ECC Order Management, Orders

Description
Categories: Enterprise Command Center
Columns: Order Number, Customer, Customer Number, Ship To Location, Order Date, Order Alert Count, Request Date, Order Alert Flag, Ecc Spec Id, Header Id ...
Imported from Enterprise Command Center
Dataset Key: ont-headers
Query Procedure: OE_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: oe_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
select
x.*
from
(
  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, 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 OE_ORDER_HEADERS_ALL_DFV ) 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
Parameter Name SQL text Validation
Operating Unit
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV