select 'ProductMaster' producttype,e.schemecd,e.SCHEMESHORTDESC as SCHEMEDESC,to_char(e.schemefrdate,'dd/MM/yyyy')as schemefrdate, to_char(e.schemetodate,'dd/MM/yyyy') as schemetodate,e.mkid ,e.countrycd,e.statecd,e.districtcd,f.schemetype, a.productcd,a.packsizecd,a.packtypecd,a.sortorder1, a.countrycd,a.statecd,a.districtcd,a.p_uniqueid,a.mrp, (a.productcd ||'-'||b.countryname||'-'||c.statename||'-'||d.districtname|| ' -pkt.prc:'||a.pktprice ||' -mrp:'||a.mrp) as pcode 
from  wb.wbproductdetails a 
left join wb.wbcountry b on b.countrycd=a.countrycd  
left join wb.wbstate c on c.countrycd=a.countrycd and  c.countrycd=b.countrycd and c.statecd=a.statecd 
left join sam.samdistrict d on d.countrycd=a.countrycd and  d.countrycd=b.countrycd and  d.countrycd=c.countrycd and d.statecd=a.statecd and  d.statecd=c.statecd and  d.districtcd=a.districtcd 
left join sam.aspschemedetvw e on e.compcode = a.compcode and e.productcd = a.productcd and e.countrycd in(1,999) and e.statecd in(3,999) and e.districtcd in(14,999) 
left join sam.aspschemedet f on f.compcode = e.compcode and f.schemecd = e.schemecd and f.compcode = a.compcode and f.productcd = a.productcd  
where  
a.compcode=3 and  
a.countrycd in(1,999) and 
a.statecd in(3,999) and 
a.districtcd in(14,999) and 
'31-December-2010' between a.startdate and a.discontinuedate and 
e.partycode in('A0101A0199','999') and 
e.userid<>'0' and  
e.schemefrdate<=to_date('31-December-2010','dd/mm/yyyy') and e.schemetodate>=to_date('31-December-2010','dd/mm/yyyy')

SELECT 'ProductMaster'                       producttype,
       e.schemecd,
       e.schemeshortdesc                     AS schemedesc,
       To_char(e.schemefrdate, 'dd/MM/yyyy') AS schemefrdate,
       To_char(e.schemetodate, 'dd/MM/yyyy') AS schemetodate,
       e.mkid,
       e.countrycd,
       e.statecd,
       e.districtcd,
       f.schemetype,
       a.productcd,
       a.packsizecd,
       a.packtypecd,
       a.sortorder1,
       a.countrycd,
       a.statecd,
       a.districtcd,
       a.p_uniqueid,
       a.mrp,
       ( a.productcd
         ||'-'
         ||b.countryname
         ||'-'
         ||c.statename
         ||'-'
         ||d.districtname
         || ' -pkt.prc:'
         ||a.pktprice
         ||' -mrp:'
         ||a.mrp )                           AS pcode
FROM   wb.wbproductdetails a
       left join wb.wbcountry b
         ON b.countrycd = a.countrycd
       left join wb.wbstate c
         ON c.countrycd = a.countrycd
            AND c.countrycd = b.countrycd
            AND c.statecd = a.statecd
       left join sam.samdistrict d
         ON d.countrycd = a.countrycd
            AND d.countrycd = b.countrycd
            AND d.countrycd = c.countrycd
            AND d.statecd = a.statecd
            AND d.statecd = c.statecd
            AND d.districtcd = a.districtcd
       left join sam.aspschemedetvw e
         ON e.compcode = a.compcode
            AND e.productcd = a.productcd
            AND e.countrycd IN( 1, 999 )
            AND e.statecd IN( 3, 999 )
            AND e.districtcd IN( 14, 999 )
       left join sam.aspschemedet f
         ON f.compcode = e.compcode
            AND f.schemecd = e.schemecd
            AND f.compcode = a.compcode
            AND f.productcd = a.productcd
WHERE  a.compcode = 3
       AND a.countrycd IN( 1, 999 )
       AND a.statecd IN( 3, 999 )
       AND a.districtcd IN( 14, 999 )
       AND '31-December-2010' BETWEEN a.startdate AND a.discontinuedate
       AND e.partycode IN( 'A0101A0199', '999' )
       AND e.userid <> '0'
       AND e.schemefrdate <= To_date('31-December-2010', 'dd/mm/yyyy')
       AND e.schemetodate >= To_date('31-December-2010', 'dd/mm/yyyy')  

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
| Id  | Operation                 |  Name              | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                    |     9 |  3843 |   120 |
|*  1 |  HASH JOIN OUTER          |                    |     9 |  3843 |   120 |
|   2 |   VIEW                    |                    |     9 |  3528 |   109 |
|   3 |    NESTED LOOPS OUTER     |                    |     9 |  3276 |   109 |
|   4 |     NESTED LOOPS OUTER    |                    |     9 |  1917 |    19 |
|   5 |      VIEW                 |                    |     9 |  1809 |    19 |
|   6 |       NESTED LOOPS OUTER  |                    |     9 |  1296 |    19 |
|   7 |        VIEW               |                    |     9 |  1224 |    19 |
|   8 |         NESTED LOOPS OUTER|                    |     9 |   522 |    19 |
|*  9 |          TABLE ACCESS FULL| WBPRODUCTDETAILS   |     9 |   486 |    19 |
|* 10 |          INDEX UNIQUE SCAN| PK_COUNTRY         |     1 |     4 |       |
|* 11 |        INDEX UNIQUE SCAN  | PK_STATE           |     1 |     8 |       |
|* 12 |      INDEX UNIQUE SCAN    | PK_DISTRICT        |     1 |    12 |       |
|  13 |     VIEW                  |                    |     1 |   151 |       |
|  14 |      NESTED LOOPS         |                    |     1 |   120 |    10 |
|* 15 |       HASH JOIN           |                    |     1 |    89 |    10 |
|* 16 |        TABLE ACCESS FULL  | ASPSCHMMARKET      |     1 |    41 |     4 |
|* 17 |        TABLE ACCESS FULL  | ASPSCHEMEMASTER    |    15 |   720 |     5 |
|* 18 |       INDEX UNIQUE SCAN   | PK_ASPSCHMPRODDET  |     1 |    31 |       |
|  19 |   TABLE ACCESS FULL       | ASPSCHEMEDET       |  5766 |   197K|    10 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("F"."PRODUCTCD"(+)="from$_subquery$_009"."PRODUCTCD_5" AND "F".
              "COMPCODE"(+)="from$_subquery$_009"."COMPCODE_0" AND "F"."SCHEM
              ECD"(+)="from$_subquery$_009"."SCHEMECD_43" AND "F"."COMPCODE"(
              +)="from$_subquery$_009"."COMPCODE_42"
   9 - filter("A"."COMPCODE"=3 AND "A"."STARTDATE"<='31-December-2010' AND "A
              "."DISCONTINUEDATE">='31-December-2010' AND ("A"."COUNTRYCD"=1
              OR "A"."COUNTRYCD"=999) AND ("A"."STATECD"=3 OR "A"."STATECD"=9
              99) AND ("A"."DISTRICTCD"=14 OR "A"."DISTRICTCD"=999))
  10 - access("B"."COUNTRYCD"(+)="A"."COUNTRYCD")
  11 - access("C"."COUNTRYCD"(+)="from$_subquery$_003"."COUNTRYCD_2" AND "C".
              "STATECD"(+)="from$_subquery$_003"."STATECD_3"
       filter("C"."COUNTRYCD"(+)="from$_subquery$_003"."COUNTRYCD_23")
  12 - access("D"."COUNTRYCD"(+)="from$_subquery$_005"."COUNTRYCD_2" AND "D".
              "STATECD"(+)="from$_subquery$_005"."STATECD_3" AND "D"."DISTRIC
              TCD"(+)="from$_subquery$_005"."DISTRICTCD_4"
       filter("D"."STATECD"(+)="from$_subquery$_005"."STATECD_29" AND "D"."CO
              UNTRYCD"(+)="from$_subquery$_005"."COUNTRYCD_28" AND "D"."COUNT
              RYCD"(+)="from$_subquery$_005"."COUNTRYCD_23")
  15 - access("A"."COMPCODE"="B"."COMPCODE" AND "A"."SCHEMECD"="B"."SCHEMECD")
  16 - filter(("B"."COUNTRYCD"=1 OR "B"."COUNTRYCD"=999) AND ("B"."STATECD"=3
               OR "B"."STATECD"=999) AND ("B"."DISTRICTCD"=14 OR "B"."DISTRIC
              TCD"=999) AND "B"."SCHEMEFRDATE"<=TO_DATE('31-December-2010','d
              d/mm/yyyy') AND "B"."SCHEMETODATE">=TO_DATE('31-December-2010',
              'dd/mm/yyyy') AND ("B"."PARTYCODE"='999' OR "B"."PARTYCODE"='A0
              101A0199'))
  17 - filter("A"."COMPCODE"="from$_subquery$_005"."COMPCODE_0" AND "A"."USER
              ID"<>'0' AND "A"."USERID"<>'0')
  18 - access("A"."COMPCODE"="C"."COMPCODE" AND "A"."SCHEMECD"="C"."SCHEMECD"
               AND "C"."PRODUCTCD"="from$_subquery$_005"."PRODUCTCD_5"

Note: cpu costing is off

60 rows selected.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                     |   992 |   328K|   114   (3)|
|*  1 |  HASH JOIN RIGHT OUTER            |                     |   992 |   328K|   114   (3)|
|*  2 |   TABLE ACCESS FULL               | ASPSCHEMEDET        |  5744 |   196K|    23   (0)|
|   3 |   VIEW                            |                     |   992 |   294K|    90   (3)|
|   4 |    NESTED LOOPS OUTER             |                     |   992 |   246K|    90   (3)|
|   5 |     NESTED LOOPS                  |                     |   992 |   234K|    90   (3)|
|*  6 |      HASH JOIN                    |                     | 15534 |  3200K|    89   (2)|
|   7 |       NESTED LOOPS                |                     |       |       |            |
|   8 |        NESTED LOOPS               |                     |    11 |   979 |    20   (0)|
|*  9 |         TABLE ACCESS FULL         | ASPSCHMMARKET       |    11 |   451 |     9   (0)|
|* 10 |         INDEX UNIQUE SCAN         | PK_ASPSCHMCD_SCHMCD |     1 |       |     0   (0)|
|* 11 |        TABLE ACCESS BY INDEX ROWID| ASPSCHEMEMASTER     |     1 |    48 |     1   (0)|
|  12 |       VIEW                        |                     |  1454 |   173K|    68   (0)|
|  13 |        NESTED LOOPS OUTER         |                     |  1454 |   147K|    68   (0)|
|  14 |         VIEW                      |                     |  1454 |   136K|    68   (0)|
|  15 |          NESTED LOOPS OUTER       |                     |  1454 | 84332 |    68   (0)|
|* 16 |           TABLE ACCESS FULL       | WBPRODUCTDETAILS    |  1454 | 78516 |    68   (0)|
|* 17 |           INDEX UNIQUE SCAN       | PK_COUNTRY          |     1 |     4 |     0   (0)|
|* 18 |         INDEX UNIQUE SCAN         | PK_STATE            |     1 |     8 |     0   (0)|
|* 19 |      INDEX UNIQUE SCAN            | PK_ASPSCHMPRODDET   |     1 |    31 |     0   (0)|
|* 20 |     INDEX UNIQUE SCAN             | PK_DISTRICT         |     1 |    12 |     0   (0)|
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("F"."PRODUCTCD"(+)="A"."PRODUCTCD" AND "F"."COMPCODE"(+)="A"."COMPCODE"
              AND "F"."SCHEMECD"(+)="E"."SCHEMECD" AND "F"."COMPCODE"(+)="E"."COMPCODE")
   2 - filter("F"."COMPCODE"(+)=3)
   6 - access("A"."COMPCODE"="from$_subquery$_005"."COMPCODE")
   9 - filter(("B"."STATECD"=3 OR "B"."STATECD"=999) AND ("B"."PARTYCODE"='999' OR
              "B"."PARTYCODE"='A0101A0199') AND ("B"."DISTRICTCD"=14 OR "B"."DISTRICTCD"=999) AND
              "B"."SCHEMETODATE">=TO_DATE('31-December-2010','dd/mm/yyyy') AND ("B"."COUNTRYCD"=1
              OR "B"."COUNTRYCD"=999) AND "B"."SCHEMEFRDATE"<=TO_DATE('31-December-2010','dd/mm/yyyy
              ') AND "B"."COMPCODE"=3)
  10 - access("A"."COMPCODE"=3 AND "A"."SCHEMECD"="B"."SCHEMECD")
  11 - filter("A"."USERID"<>'0')
  16 - filter("A"."COMPCODE"=3 AND ("A"."STATECD"=3 OR "A"."STATECD"=999) AND
              ("A"."COUNTRYCD"=1 OR "A"."COUNTRYCD"=999) AND ("A"."DISTRICTCD"=14 OR
              "A"."DISTRICTCD"=999) AND "A"."STARTDATE"<='31-December-2010' AND
              "A"."DISCONTINUEDATE">='31-December-2010')
  17 - access("B"."COUNTRYCD"(+)="A"."COUNTRYCD")
       filter("B"."COUNTRYCD"(+)=1 OR "B"."COUNTRYCD"(+)=999)
  18 - access("C"."COUNTRYCD"(+)="A"."COUNTRYCD" AND "C"."STATECD"(+)="A"."STATECD")
       filter(("C"."STATECD"(+)=3 OR "C"."STATECD"(+)=999) AND ("C"."COUNTRYCD"(+)=1
              OR "C"."COUNTRYCD"(+)=999) AND "C"."COUNTRYCD"(+)="B"."COUNTRYCD")
  19 - access("C"."COMPCODE"=3 AND "A"."SCHEMECD"="C"."SCHEMECD" AND
              "C"."PRODUCTCD"="from$_subquery$_005"."PRODUCTCD")
  20 - access("D"."COUNTRYCD"(+)="A"."COUNTRYCD" AND "D"."STATECD"(+)="A"."STATECD"
              AND "D"."DISTRICTCD"(+)="A"."DISTRICTCD")
       filter(("D"."STATECD"(+)=3 OR "D"."STATECD"(+)=999) AND
              ("D"."DISTRICTCD"(+)=14 OR "D"."DISTRICTCD"(+)=999) AND ("D"."COUNTRYCD"(+)=1 OR
              "D"."COUNTRYCD"(+)=999) AND "D"."STATECD"(+)="C"."STATECD" AND
              "D"."COUNTRYCD"(+)="C"."COUNTRYCD" AND "D"."COUNTRYCD"(+)="B"."COUNTRYCD")

Note
-----
   - 'PLAN_TABLE' is old version

62 rows selected.

SQL> execute DBMS_STATS.DELETE_SCHEMA_STATS (OWNNAME => 'SAM');

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.gather_schema_stats(OWNNAME => 'SAM', OPTIONS => 'GATHER AUTO');

PL/SQL procedure successfully completed.

SQL>  execute DBMS_STATS.DELETE_SCHEMA_STATS (OWNNAME => 'WB');

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.gather_schema_stats(OWNNAME => 'WB', OPTIONS => 'GATHER AUTO');

PL/SQL procedure successfully completed.

CREATE TABLE TEMPA
(
  P_UNIQUEID       NUMBER(8),
  COMPCODE         NUMBER(4),
  COUNTRYCD        NUMBER(4),
  STATECD          NUMBER(4),
  DISTRICTCD       NUMBER(4),
  PRODUCTCD        VARCHAR2(50 BYTE),
  STARTDATE        DATE,
  DISCONTINUEDATE  DATE
)
LOGGING 
NOCACHE
NOPARALLEL
NOMONITORING;


CREATE TABLE TEMPB
(
  COUNTRYCD  NUMBER(4)
)
LOGGING 
NOCACHE
NOPARALLEL
NOMONITORING;


CREATE TABLE TEMPC
(
  COUNTRYCD  NUMBER(4),
  STATECD    NUMBER(4)
)
LOGGING 
NOCACHE
NOPARALLEL
NOMONITORING;


CREATE TABLE TEMPD
(
  COUNTRYCD   NUMBER(4),
  STATECD     NUMBER(4),
  DISTRICTCD  NUMBER(4)
)
LOGGING 
NOCACHE
NOPARALLEL
NOMONITORING;


CREATE TABLE TEMPE
(
  COMPCODE         NUMBER(4)                    NOT NULL,
  PRODUCTCD        VARCHAR2(50 BYTE)            NOT NULL,
  COUNTRYCD        NUMBER(4),
  STATECD          NUMBER(4),
  DISTRICTCD       NUMBER(4),
  SCHEMECD         NUMBER(8)                    NOT NULL,
  PARTYCODE        VARCHAR2(10 BYTE),
  USERID           VARCHAR2(30 BYTE)            NOT NULL,
  SCHEMEFRDATE     DATE                         NOT NULL,
  SCHEMETODATE     DATE                         NOT NULL,
  SCHEMESHORTDESC  VARCHAR2(30 BYTE)            NOT NULL
)
LOGGING 
NOCACHE
NOPARALLEL
NOMONITORING;


CREATE TABLE TEMPF
(
  COMPCODE    NUMBER(4)                         NOT NULL,
  PRODUCTCD   VARCHAR2(50 BYTE)                 NOT NULL,
  SCHEMETYPE  VARCHAR2(5 BYTE)                  NOT NULL,
  SCHEMECD    NUMBER(8)                         NOT NULL
)
LOGGING 
NOCACHE
NOPARALLEL
NOMONITORING;



SET DEFINE OFF;
Insert into TEMPA
   (P_UNIQUEID, COMPCODE, COUNTRYCD, STATECD, DISTRICTCD, 
    PRODUCTCD, STARTDATE, DISCONTINUEDATE)
 Values
   (5233, 3, 1, 3, 999, 
    'WB-BROKENS-500 GMS-POUCH', TO_DATE('10/11/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/9999 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TEMPA
   (P_UNIQUEID, COMPCODE, COUNTRYCD, STATECD, DISTRICTCD, 
    PRODUCTCD, STARTDATE, DISCONTINUEDATE)
 Values
   (5234, 3, 1, 3, 999, 
    'WB-BROKENS-1 KGS-POUCH', TO_DATE('10/11/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/31/9999 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

SET DEFINE OFF;
Insert into TEMPB
   (COUNTRYCD)
 Values
   (1);
Insert into TEMPB
   (COUNTRYCD)
 Values
   (999);
COMMIT;

SET DEFINE OFF;
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (1, 3);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (1, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (2, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (3, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (4, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (5, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (6, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (7, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (8, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (9, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (10, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (11, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (12, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (13, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (14, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (999, 999);
COMMIT;

SET DEFINE OFF;
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (1, 14, 22);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (1, 14, 23);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (1, 14, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (1, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (2, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (3, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (4, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (5, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (6, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (7, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (8, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (9, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (10, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (11, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (12, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (13, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (14, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (15, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (16, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (17, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (999, 999, 999);
COMMIT;

SET DEFINE OFF;
Insert into TEMPE
   (COMPCODE, PRODUCTCD, COUNTRYCD, STATECD, DISTRICTCD, 
    SCHEMECD, PARTYCODE, USERID, SCHEMEFRDATE, SCHEMETODATE, 
    SCHEMESHORTDESC)
 Values
   (3, 'WB-BROKENS-500 GMS-POUCH', 1, 14, 999, 
    1554, '999', 'chintan.patel', TO_DATE('12/25/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/07/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    '50gmWBMasalaOnWBLeaf500gm');
Insert into TEMPE
   (COMPCODE, PRODUCTCD, COUNTRYCD, STATECD, DISTRICTCD, 
    SCHEMECD, PARTYCODE, USERID, SCHEMEFRDATE, SCHEMETODATE, 
    SCHEMESHORTDESC)
 Values
   (3, 'WB-BROKENS-500 GMS-POUCH', 1, 3, 999, 
    1554, '999', 'chintan.patel', TO_DATE('12/25/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/07/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
    '50gmWBMasalaOnWBLeaf500gm');
COMMIT;

SET DEFINE OFF;
Insert into TEMPF
   (COMPCODE, PRODUCTCD, SCHEMETYPE, SCHEMECD)
 Values
   (3, 'WB-BROKENS-500 GMS-POUCH', 'DIFWP', 1554);
COMMIT;

SQL> select a.p_uniqueid,e.schemeshortdesc
  2  from  tempa a 
  3  left join tempb b on b.countrycd=a.countrycd  
  4  left join tempc c on c.countrycd=a.countrycd and  c.countrycd=b.countrycd and c.statecd=a.statecd 
  5  left join tempd d on d.countrycd=a.countrycd and  d.countrycd=b.countrycd and  d.countrycd=c.countrycd and d.statecd=a.statecd and  d.statecd=c.statecd and  d.districtcd=a.districtcd
  6  left join tempe e on e.compcode = a.compcode and e.productcd = a.productcd and e.countrycd in(1,999) and e.statecd in(3,999) and e.districtcd in(14,999) 
  7  left join tempf f on f.compcode = e.compcode and f.schemecd = e.schemecd and f.compcode = a.compcode and f.productcd = a.productcd  
  8  where  
  9  a.compcode=3 and  
 10  a.countrycd in(1,999) and 
 11  a.statecd in(3,999) and 
 12  a.districtcd in(14,999) and 
 13  '31-December-2010' between a.startdate and a.discontinuedate and 
 14  e.partycode in('A0101A0199','999') and 
 15  e.userid<>'0' and  
 16  e.schemefrdate<=to_date('31-December-2010','dd/mm/yyyy') and e.schemetodate>=to_date('31-December-2010','dd/mm/yyyy');

P_UNIQUEID SCHEMESHORTDESC
---------- ------------------------------
      5233 50gmWBMasalaOnWBLeaf500gm
      5234

SQL> select a.p_uniqueid,e.schemeshortdesc
  2  from  tempa a 
  3  left join tempb b on b.countrycd=a.countrycd  
  4  left join tempc c on c.countrycd=a.countrycd and  c.countrycd=b.countrycd and c.statecd=a.statecd 
  5  left join tempd d on d.countrycd=a.countrycd and  d.countrycd=b.countrycd and  d.countrycd=c.countrycd and d.statecd=a.statecd and  d.statecd=c.statecd and  d.districtcd=a.districtcd
  6  left join tempe e on e.compcode = a.compcode and e.productcd = a.productcd and e.countrycd in(1,999) and e.statecd in(3,999) and e.districtcd in(14,999) 
  7  left join tempf f on f.compcode = e.compcode and f.schemecd = e.schemecd and f.compcode = a.compcode and f.productcd = a.productcd  
  8  where  
  9  a.compcode=3 and  
 10  a.countrycd in(1,999) and 
 11  a.statecd in(3,999) and 
 12  a.districtcd in(14,999) and 
 13  '31-December-2010' between a.startdate and a.discontinuedate and 
 14  e.partycode in('A0101A0199','999') and 
 15  e.userid<>'0' and  
 16  e.schemefrdate<=to_date('31-December-2010','dd/mm/yyyy') and e.schemetodate>=to_date('31-December-2010','dd/mm/yyyy');

P_UNIQUEID SCHEMESHORTDESC
---------- ------------------------------
      5233 50gmWBMasalaOnWBLeaf500gm

'31-December-2010'
to_date('31-December-2010','dd/mm/yyyy')

select a.p_uniqueid, e.schemeshortdesc
  from  tempa a 
  left join tempe e on e.compcode = a.compcode and e.productcd = a.productcd
    and e.countrycd in(1,999) and e.statecd in(3,999) and e.districtcd in(14,999) 
    and e.partycode in('A0101A0199','999') and e.userid<>'0'
    and e.schemefrdate<=to_date('31/12/2010','dd/mm/yyyy')
    and e.schemetodate>=to_date('31/12/2010','dd/mm/yyyy')
where  
  a.compcode=3 and  
  a.countrycd in(1,999) and 
  a.statecd in(3,999) and 
  a.districtcd in(14,999) and 
  to_date( '31/12/2010','dd/mm/yyyy') between a.startdate and a.discontinuedate;

SQL> select a.p_uniqueid,e.schemeshortdesc
  2  from  tempa a 
  3  left join tempe e on e.compcode = a.compcode and e.productcd = a.productcd and e.countrycd in(1,999) and e.statecd in(3,999) and e.districtcd in(14,999)
  4  where  
  5  a.compcode=3 and  
  6  a.countrycd in(1,999) and 
  7  a.statecd in(3,999) and 
  8  a.districtcd in(14,999) and 
  9  '31-December-2010' between a.startdate and a.discontinuedate and 
 10  e.partycode in('A0101A0199','999') and 
 11  e.userid<>'0' and  
 12  e.schemefrdate<=to_date('31-December-2010','dd/mm/yyyy') and e.schemetodate>=to_date('31-December-2010','dd/mm/yyyy');

P_UNIQUEID SCHEMESHORTDESC
---------- ------------------------------
      5233 50gmWBMasalaOnWBLeaf500gm
      5234

SQL> select a.p_uniqueid,e.schemeshortdesc
  2  from  tempa a 
  3  left join tempe e on e.compcode = a.compcode and e.productcd = a.productcd and e.countrycd in(1,999) and e.statecd in(3,999) and e.districtcd in(14,999) 
  4  where  
  5  a.compcode=3 and  
  6  a.countrycd in(1,999) and 
  7  a.statecd in(3,999) and 
  8  a.districtcd in(14,999) and 
  9  '31-December-2010' between a.startdate and a.discontinuedate and 
 10  e.partycode in('A0101A0199','999') and 
 11  e.userid<>'0' and  
 12  e.schemefrdate<=to_date('31-December-2010','dd/mm/yyyy') and e.schemetodate>=to_date('31-December-2010','dd/mm/yyyy');

P_UNIQUEID SCHEMESHORTDESC
---------- ------------------------------
      5233 50gmWBMasalaOnWBLeaf500gm