-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                                |     1 |   224 |   793K  (1)| 02:38:43 |
|   1 |  NESTED LOOPS                          |                                |     1 |     9 |     0   (0)| 00:00:01 |
|*  2 |   FIXED TABLE FULL                     | X$KCCDI                        |     1 |     9 |     0   (0)| 00:00:01 |
|   3 |   FIXED TABLE FULL                     | X$KCCDI2                       |     1 |       |     0   (0)| 00:00:01 |
|   4 |  SORT ORDER BY                         |                                |     1 |   224 |   793K  (1)| 02:38:43 |
|   5 |   NESTED LOOPS                         |                                |       |       |            |          |
|   6 |    NESTED LOOPS                        |                                |     1 |   224 |   793K  (1)| 02:38:43 |
|   7 |     NESTED LOOPS                       |                                |     1 |   208 |   793K  (1)| 02:38:43 |
|   8 |      NESTED LOOPS                      |                                |    19 |  3800 |   793K  (1)| 02:38:43 |
|*  9 |       HASH JOIN                        |                                |   216 | 40392 |   793K  (1)| 02:38:38 |
|  10 |        VIEW                            | index$_join$_008               |   102K|   700K|  2445   (2)| 00:00:30 |
|* 11 |         HASH JOIN                      |                                |       |       |            |          |
|* 12 |          INDEX FAST FULL SCAN          | XXOM_3LP_SYM_ORA_ORDER_HDR_N3  |   102K|   700K|   696   (2)| 00:00:09 |
|* 13 |          INDEX FAST FULL SCAN          | XXOM_3LP_SYM_ORA_ORDER_HDR_N2  |   102K|   700K|   958   (2)| 00:00:12 |
|  14 |        MERGE JOIN CARTESIAN            |                                |  2245K|   385M|   790K  (1)| 02:38:08 |
|  15 |         NESTED LOOPS                   |                                |   513 | 83619 |   778K  (1)| 02:35:44 |
|* 16 |          HASH JOIN                     |                                |  1188 |   180K|   778K  (1)| 02:35:43 |
|* 17 |           VIEW                         | index$_join$_012               |    69 |   552 |    15   (7)| 00:00:01 |
|* 18 |            HASH JOIN                   |                                |       |       |            |          |
|  19 |             INDEX FAST FULL SCAN       | HR_ORGANIZATION_UNITS_FK1      |    69 |   552 |     9   (0)| 00:00:01 |
|  20 |             INDEX FAST FULL SCAN       | HR_ORGANIZATION_UNITS_PK       |    69 |   552 |     9   (0)| 00:00:01 |
|  21 |           NESTED LOOPS                 |                                |  2878 |   415K|   778K  (1)| 02:35:43 |
|* 22 |            HASH JOIN                   |                                |   101K|  6906K| 17596   (1)| 00:03:32 |
|  23 |             NESTED LOOPS               |                                |  1784 | 62440 |   133   (4)| 00:00:02 |
|* 24 |              TABLE ACCESS FULL         | OE_TRANSACTION_TYPES_TL        |  1784 | 55304 |   132   (3)| 00:00:02 |
|* 25 |              INDEX UNIQUE SCAN         | OE_TRANSACTION_TYPES_ALL_U1    |     1 |     4 |     0   (0)| 00:00:01 |
|* 26 |             TABLE ACCESS BY INDEX ROWID| OE_ORDER_HEADERS_ALL           |   130K|  4462K| 17461   (1)| 00:03:30 |
|* 27 |              INDEX RANGE SCAN          | OE_ORDER_HEADERS_N8            |   174K|       |   400   (2)| 00:00:05 |
|* 28 |            TABLE ACCESS BY INDEX ROWID | OE_ORDER_LINES_ALL             |     1 |    78 |    11   (0)| 00:00:01 |
|* 29 |             INDEX RANGE SCAN           | OE_ORDER_LINES_ALL_X17         |    21 |       |     3   (0)| 00:00:01 |
|* 30 |          INDEX UNIQUE SCAN             | HR_ALL_ORGANIZATION_UNTS_TL_PK |     1 |     7 |     0   (0)| 00:00:01 |
|  31 |         BUFFER SORT                    |                                |  4376 | 74392 |   790K  (1)| 02:38:08 |
|  32 |          TABLE ACCESS FULL             | XXONT_SOM_SCHEDULER            |  4376 | 74392 |    23   (0)| 00:00:01 |
|* 33 |       TABLE ACCESS BY INDEX ROWID      | XXOM_3LP_SYM_ORA_ORDER_LINES   |     1 |    13 |   793K  (1)| 02:38:43 |
|  34 |        BITMAP CONVERSION TO ROWIDS     |                                |       |       |            |          |
|* 35 |         BITMAP INDEX RANGE SCAN        | XXOM_3LP_SYM_ORA_ORDER_N11     |       |       |            |          |
|* 36 |      TABLE ACCESS BY INDEX ROWID       | MTL_PARAMETERS                 |     1 |     8 |     1   (0)| 00:00:01 |
|* 37 |       INDEX UNIQUE SCAN                | MTL_PARAMETERS_U1              |     1 |       |     0   (0)| 00:00:01 |
|* 38 |     INDEX UNIQUE SCAN                  | OE_ORDER_SOURCES_U1            |     1 |       |     0   (0)| 00:00:01 |
|* 39 |    TABLE ACCESS BY INDEX ROWID         | OE_ORDER_SOURCES               |     1 |    16 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------

/* Formatted on 2014/03/10 08:20 (Formatter Plus v4.8.8) */
SELECT   /*+ ordered */                                            --PRN 13909
         oh.demand_class_code, TO_CHAR (order_number, '99999999')
       , TO_CHAR (ol.line_number, '9999'), ol.ordered_item
       , mp.organization_code
       , 
--NVL(frs.email, 'linda.bauer@emerson.com')   sc_email,
         NVL (NVL (oss.primary_email_address, oss.alternate_email_address)
            , 'linda.bauer@emerson.com'
             ) sc_email
       , TO_CHAR (ol.promise_date, 'DD-Mon-YYYY') promise_dt
       , TO_CHAR (ol.schedule_ship_date, 'DD-Mon-YYYY') sched_dt
       , (SELECT d.NAME
            FROM v$database d) db_name
    FROM apps.oe_transaction_types_tl ottt
       , apps.oe_transaction_types_all otta
       , apps.oe_order_headers_all oh
       , apps.oe_order_lines_all ol
       , apps.hr_organization_units hou
       , apps.xxont_som_scheduler oss
       , apps.xxom_3lp_sym_ora_order_hdr xxooh
       , apps.xxom_3lp_sym_ora_order_lines xxool
       ,                               --Added by OCD Project  on 7th aug 2012
         apps.mtl_parameters mp
       , apps.oe_order_sources oos
   WHERE oh.header_id = ol.header_id
     AND ol.ship_from_org_id = hou.organization_id
     AND hou.organization_id = mp.organization_id
     AND oh.header_id = xxooh.header_id
     AND xxooh.order_admin = oss.subscriber_id
     AND xxool.line_id = ol.line_id    --Added by OCD Project  on 7th aug 2012
     AND ol.org_id = xxool.org_id      --Added by OCD Project  on 7th aug 2012
--AND oss.subscriber_type = 'BUSINESS_ADMIN' Commented for SI
     AND oh.order_type_id = otta.transaction_type_id
     AND otta.transaction_type_id = ottt.transaction_type_id
     AND ottt.LANGUAGE = USERENV ('LANG')
-- AND ol.link_to_line_id  IS NULL   commented by OCD Project  on 7th aug 2012
     AND (   (    xxool.assembly_type IS NOT NULL
                                       --Added by OCD Project  on 7th aug 2012
              AND ol.link_to_line_id = ol.top_model_line_id
             )
          OR (    (   xxool.assembly_type IS NULL
                   OR xxool.assembly_type NOT IN ('I', 'R')
                  )
              AND ol.link_to_line_id IS NULL
             )
         )                             --Added by OCD Project  on 7th aug 2012
     AND ol.ordered_quantity <> 0
     AND oh.open_flag = 'Y'
     AND ol.open_flag = 'Y'
     AND oh.flow_status_code <> 'CLOSED'
     AND oos.order_source_id = oh.order_source_id
     AND oos.NAME <> 'Internal'
     AND (    ottt.NAME NOT LIKE '%QUOTE%'
          AND ottt.NAME NOT LIKE '%DUMMY%'
          AND ottt.NAME NOT LIKE '%RMA%'
          AND ottt.NAME NOT LIKE '%BILLING ONLY%'
          AND ottt.NAME NOT LIKE '%CREDIT ONLY%'
         )
     AND ol.ordered_item NOT LIKE 'MISC%'
     AND TRUNC (ol.schedule_ship_date) = TRUNC (ol.promise_date)
     AND mp.organization_code = 'RTF'
ORDER BY 1, 2, 3