ECC Order Management, Orders, SQL1

Description
Columns: Ord Num, Customer, Customer Number, Ship To Location, Order Date, Order Alert Count, Request Date, Order Alert Flag, Ecc Spec Id, Header Id ...
Imported from ECC
  SELECT * FROM (
                                            SELECT  /*+ push_pred(oel) */ oel.order_number,oel.customer,oel.customer_number,oel.ship_to_location, oel.order_date,
                                                    oel.status, oel.order_alert_count,oel.request_date,oel.order_type,
                                                    oel.order_alert_flag,oel.ecc_spec_id,oel.header_id,oel.header_hold_name,oel.customer_po,
                                                    oel.org_id,oel.order_currency,oel.order_total,oel.language_code,oel.csr_user_name, hdfv.*,oel.ORDER_CREATION_DATE,
                                                    oel.ORDER_BOOKED_DATE
 
                                            FROM
                                            (
                                                SELECT /*+ leading(oegt) cardinality(oegt 10) */ oegt.order_number,
                                                    oegt.customer,
                                                    oegt.customer_number,
                                                    ship_su.LOCATION ship_to_location,
                                                    oegt.order_date,
                                                    oegt.status,
                                                    SUM (oegt.alert_count) + MAX(nvl(oegt.hdr_alert_count,0)) order_alert_count,
                                                    DECODE (SUM (oegt.alert_count) + MAX(nvl(oegt.hdr_alert_count,0)),0,'ecc_blank','ecc_warning') order_alert_flag,
                                                    oegt.header_id ecc_spec_id,
                                                    oegt.header_id,
                                                    oegt.header_hold_name,
                                                    oh.cust_po_number customer_po,
                                                    oh.request_date ,
                                                    oegt.order_type,
                                                    oegt.org_id,
                                                    oegt.func_currency order_currency,
                                                    oegt.order_total order_total,
                                                    oh.csr_user_id,oegt.csr_user_name,
                                                    oegt.ORDER_BOOKED_DATE ORDER_BOOKED_DATE,
                                                    oegt.ORDER_CREATION_DATE ORDER_CREATION_DATE,
                                                    oegt.language_code language_code
                                                FROM
                                                    oe_ecc_global_temp oegt,
                                                    oe_order_headers_all oh,
                                                    hz_cust_site_uses_all ship_su
                                                WHERE
                                                    oh.header_id    =oegt.header_id AND
                                                    oh.ship_to_org_id = ship_su.site_use_id(+) AND
                                                    oegt.org_id IS NOT NULL
                                                GROUP BY
                                                    oegt.header_id,
                                                    oegt.order_number,
                                                    oegt.customer,
                                                    oegt.customer_number,
                                                    oegt.order_date,
                                                    oegt.status,
                                                    ship_su.LOCATION,
                                                    oegt.header_hold_name,
                                                    oh.cust_po_number,
                                                    oh.request_date ,
                                                    oegt.hdr_alert_count,
                                                    oegt.order_type,
                                                    oegt.org_id,
                                                    oegt.func_currency,
                                                    oegt.order_total,
                                                    oh.csr_user_id,oegt.csr_user_name,
                                                    oegt.ORDER_BOOKED_DATE ,
                                                    oegt.ORDER_CREATION_DATE ,
                                                    oegt.language_code) oel,
                                                    (select 'ONT_H_HDF_ROW_ID','ONT_H_HDF_CONTEXT_VALUE','ONT_H_HDF_CONCATENATED_SEGMENTS' from dual where 1=2  union select ROWIDTOCHAR(ROW_ID),CONTEXT_VALUE,CONCATENATED_SEGMENTS from OE_ORDER_HEADERS_ALL_DFV) hdfv,
                                                     oe_order_headers_all h
                                                  WHERE
                                                    h.rowid = hdfv."'ONT_H_HDF_ROW_ID'"(+) and
                                                    h.header_id = oel.header_id
                                            )
                                            PIVOT (
                                                max(STATUS) as STATUS,
                                                max(ORDER_CURRENCY) as ORDER_CURRENCY,
                                                max(ORDER_TYPE) as ORDER_TYPE
                                                for language_code in ('US' "US"))