ECC Order Management, Orders

Description
Categories: Enterprise Command Center
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
Run ECC Order Management, Orders and other Oracle EBS reports with Blitz Report™ on our demo environment
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 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
Blitz Report™