select /*+ rule*/ c.case_cd,c.c_item_cd,e.invoice_no,e.location_cd as "loc_cd",d.status_cd,cit.com_cd,f.trans_cd,g.upd_dt as "Submitted date"
            from item_navigator_tbl o, c_item_tbl cit, case_item c, item_activity d, app_form_tbl e, item_tbl f, comm_tbl g
            where obj_cd in 
			(select /*+ rule*/com_cd from item_tbl b 
			where b.upd_dt between to_date('01/01/2007', 'MM/DD/YYYY') and SYSDATE
            and b.trans_cd in 
			(select /*+ rule*/trans_cd from trans_tbl
            where ins_dt between to_date('01/01/2007', 'MM/DD/YYYY') and SYSDATE))
            and o.obj_cd = cit.com_cd
            and o.obj_cd = g.com_cd
            and o.child_obj_cd = cit.c_item_seq_no
            and o.obj_cd = f.com_cd
            and o.child_obj_cd = f.c_item_seq_no
            and cit.c_item_cd = c.c_item_cd
            and c.c_item_cd = d.c_item_cd
            and c.req_item_cd = e.order_item_id
            and not exists (select distinct 1
                                        from item_activity ia
                                        where ia.c_item_cd = cit.c_item_cd  
                                        and status_cd in ( select 'OP' from dual union 
                                        select 'CL' from dual union
                                        select distinct c_item_status_cd from item_action_tbl ));
										

Execution Plan
----------------------------------------------------------
   0       SELECT STATEMENT Optimizer Mode=HINT: RULE
   1    0    FILTER
   2    1      TABLE ACCESS BY INDEX ROWID DFLX1.ITEM_ACTIVITY
   3    2        NESTED LOOPS
   4    3          NESTED LOOPS
   5    4            NESTED LOOPS
   6    5              NESTED LOOPS
   7    6                NESTED LOOPS
   8    7                  NESTED LOOPS
   9    8                    NESTED LOOPS
  10    9                      VIEW SYS.VW_TMD
  11   10                        SORT UNIQUE
  12   11                          TABLE ACCESS BY INDEX ROWID DFLX1.ITEM_TBL
  13   12                            NESTED LOOPS
  14   13                              TABLE ACCESS BY INDEX ROWID DFLX1.TRANS_TBL
  15   14                                INDEX RANGE SCAN DFLX1.IR2_TRANS_TBL
  16   13                              INDEX RANGE SCAN DFLX1.id_comm_item
  17    9                      TABLE ACCESS BY INDEX ROWID DFLX1.ITEM_NAVIGATATOR_TBL
  18   17                        INDEX RANGE SCAN DFLX1.XPKITEM_NAVIGATATOR_TBL
  19    8                    TABLE ACCESS BY INDEX ROWID DFLX1.COMM_TBL
  20   19                      INDEX UNIQUE SCAN DFLX1.XPKCOMM_TBL
  21    7                  TABLE ACCESS BY INDEX ROWID DFLX1.ITEM_TBL
  22   21                    INDEX UNIQUE SCAN DFLX1.XPKITEM_TBL
  23    6                INDEX RANGE SCAN DFLX1.XPKC_ITEM_TBL
  24    5              TABLE ACCESS BY INDEX ROWID DFLX1.ITEM_TBL
  25   24                INDEX UNIQUE SCAN DFLX1.XPKITEM_TBL
  26    4            TABLE ACCESS BY INDEX ROWID DFLX1.APP_FORM_TBL
  27   26              INDEX UNIQUE SCAN DFLX1.PKFAX_APP_FORM_TBL
  28    3          INDEX RANGE SCAN DFLX1.XUPD_DT
  29    1      NESTED LOOPS
  30   29        VIEW SYS.VW_TMD
  31   30          SORT UNIQUE
  32   31            UNION-ALL
  33   32              TABLE ACCESS FULL SYS.DUAL
  34   32              TABLE ACCESS FULL SYS.DUAL
  35   32              TABLE ACCESS FULL DFLX1.ITEM_ACTION_TBL
  36   29        INDEX RANGE SCAN DFLX1.XC_ITEM_ID_DTCD
  

SELECT c.Case_cd,
       c.c_Item_cd,
       e.Invoice_No,
       e.Location_cd AS "loc_cd",
       d.Status_cd,
       cIt.com_cd,
       f.tRans_cd,
       g.Upd_dt AS "Submitted date"
FROM   c_Item_Tbl cIt,
       Case_Item c,
       Item_Activity d,
       app_Form_Tbl e,
       Item_Tbl f,
       comm_Tbl g
WHERE  obj_cd IN (SELECT com_cd
                  FROM   Item_Tbl b
                  WHERE  b.Upd_dt BETWEEN To_date('01/01/2007','MM/DD/YYYY')
                                          AND SYSDATE
                         AND b.tRans_cd IN (SELECT /*+ rule*/ tRans_cd
                                            FROM   tRans_Tbl
                                            WHERE  Ins_dt BETWEEN To_date('01/01/2007','MM/DD/YYYY')
                                                                  AND SYSDATE))
       AND (f.c_Item_seq_No, f.com_cd, cIt.c_Item_seq_No, g.com_cd, cIt.com_cd) IN
           (SELECT o.Child_obj_cd, o.obj_cd , o.Child_obj_cd, o.obj_cd , o.obj_cd from Item_Navigator_Tbl o)
       AND cIt.c_Item_cd = c.c_Item_cd
       AND c.c_Item_cd = d.c_Item_cd
       AND c.req_Item_cd = e.Order_Item_Id
       AND NOT EXISTS (SELECT DISTINCT 1
                       FROM   Item_Activity ia
                       WHERE  ia.c_Item_cd = cIt.c_Item_cd
                              AND Status_cd IN (SELECT 'OP'
                                                FROM   Dual
                                                UNION 
                                                SELECT 'CL'
                                                FROM   Dual
                                                UNION 
                                                SELECT DISTINCT c_Item_Status_cd
                                                FROM   Item_Action_Tbl));