ECC Order Management, Orders

select
x.*
from
(
  SELECT * FROM (
                                            SELECT  /*+ push_pred(oel) */ oel.order_number,
                                                    hdfv.* ,
                                                    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, 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 "ROW_ID" "'ONTH_ROW_ID'","CONTEXT_VALUE" "'ONTH_CONTEXT_VALUE'","ADDITIONAL_COMMENT" "'ONTH_ADDITIONAL_COMMENT'","CONCATENATED_SEGMENTS" "'ONTH_CONCATENATED_SEGMENTS'" from ( select ROWID "ROW_ID",CONTEXT "CONTEXT_VALUE",ATTRIBUTE1 "ADDITIONAL_COMMENT",ATTRIBUTE1 "CONCATENATED_SEGMENTS" from OE_ORDER_HEADERS_ALL ) ) hdfv, oe_order_headers_all h 
                                                  WHERE
                                                    1=1
                                                     and h.rowid = hdfv."'ONTH_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"))
) x
where
2=2