ECC Order Management, Order Lines

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, 'Onth Row Id', 'Onth Context Value', 'Onth Additional Comment', 'Onth Concatenated Segments', Ecc Last Update Date, Order Number, Line Number, Line Id, Header Id ...
Imported from Enterprise Command Center
Dataset Key: ont-lines
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
                                                    ECC_SPEC_ID,
 
                                                    hdfv.* ,
                                                    oel.ECC_LAST_UPDATE_DATE,oel.ORDER_NUMBER,oel.LINE_NUMBER,oel.LINE_ID,oel.HEADER_ID,oel.PRODUCT,oel.LINE_QUANTITY,
                                                    oel.UNIT_SELLING_PRICE,oel.ORDER_UOM,oel.PRODUCT_DESCRIPTION,oel.LINE_TYPE,oel.PRICE_LIST,oel.PRICE_LIST_ID,oel.ORDER_DATE,
                                                    oel.SHIP_FROM_ORG_ID,oel.WAREHOUSE,oel.SOURCE_TYPE,oel.SHIPMENT_METHOD,oel.CARRIER,oel.SHIPMENT_PRIORITY,oel.SHIPPING_INSTRUCTIONS,
                                                    oel.PACKING_INSTRUCTIONS,oel.FREIGHT_TERMS,oel.SALESPERSON,oel.CUSTOMER,oel.CUSTOMER_NUMBER,oel.SHIP_TO_CUSTOMER_NAME,
                                                    oel.SHIP_TO_CUSTOMER_NUMBER,oel.SHIP_TO_CONTACT,oel.SHIP_TO_CONTACT_ID,oel.SHIP_TO_ADDRESS,oel.SHIP_TO_ORG_ID,
                                                    oel.BILL_TO_CUSTOMER_NAME,oel.BILL_TO_CUSTOMER_NUMBER,oel.BILL_TO_CONTACT,oel.BILL_TO_CONTACT_ID,oel.BILL_TO_ADDRESS,
                                                    oel.BILL_TO_ORG_ID,oel.EXPECTED_DELAY,oel.REQUEST_DATE,oel.REQUEST_DATE_TYPE,oel.SCHEDULE_SHIP_DATE,oel.ACTUAL_SHIPMENT_DATE,
                                                    oel.SCHEDULE_ARRIVAL_DATE,oel.ACTUAL_ARRIVAL_DATE,oel.LATEST_ACCEPTABLE_DATE,oel.FULFILLMENT_DATE,oel.FLOW_STATUS_CODE,
                                                    oel.LINE_STATUS,oel.ITEM_TYPE_CODE,oel.ORG_ID,oel.OPERATING_UNIT,oel.LINE_CATEGORY_CODE,oel.FULFILLED_FLAG,oel.LINE_TRAN_AMOUNT,
                                                    oel.OPEN_FLAG,oel.TXN_CURRENCY,oel.TXN_CURRENCY_CODE,oel.FUNC_CURRENCY_CODE,oel.FUNC_CURRENCY,oel.LINE_AMOUNT,oel.SHIP_TO_SITE,
                                                    oel.SHIP_TO_LOCATION,oel.SHIP_TO_STATE,oel.SHIP_TO_COUNTRY,oel.BILL_TO_SITE,oel.BILL_TO_CITY,oel.BILL_TO_STATE,oel.BILL_TO_COUNTRY,
                                                    oel.LINE_LAST_UPDATE_DATE,oel.PRIMARY_UOM,oel.INVENTORY_ITEM_ID,oel.SALES_CHANNEL,oel.ORDER_TYPE,oel.STATUS,oel.SHIPPED_QUANTITY,
                                                    oel.FULFILLED_QUANTITY,oel.INVOICED_QUANTITY,oel.SCHEDULE_STATUS,oel.PROMISE_DATE,oel.CUSTOMER_PO,oel.PAYMENT_TERM,oel.AGREEMENT_NAME,
                                                    oel.SALES_AGREEMENT_NUMBER,oel.SALES_AGREEMENT_LINE_NUMBER,oel.SUBINVENTORY,oel.PICK_STATUS,oel.TRANSACTIONAL_ORDER_TOTAL,
                                                    oel.ORDER_TOTAL,oel.RESERVED_QUANTITY,oel.PARTY_ID,oel.PREDICTED_RETURN_REASON_CODE,oel.ANOMALIES,oel.LINE_HOLD_NAME,
                                                    oel.HEADER_HOLD_NAME,oel.TOTAL_HOLD_NAME,oel.ALERT_FLAG,oel.ALERT_COUNT,oel.ALERT_TEXT,oel.GRAPH_ALERT_NAME,oel.DELAY_FLAG,
                                                    oel.DELAY_COUNT,oel.CONVERSION_RATE,oel.CONVERSION_TYPE_CODE,oel.RESERVED_QTY,oel.BOOKED_FLAG,oel.ORDER_ALERT_COUNT,oel.ALERT_TYPE,
                                                    oel.HDR_ALERT_COUNT,oel.LANGUAGE_CODE,oel.ALERT_TYPE_CODE,oel.TABLE_ALERT_FLAG,oel.HDR_LAST_UPDATE_DATE,oel.ON_ALERT,oel.IS_OPEN,
                                                    oel.IS_BOOKED,oel.LINE_QUANTITY_1,oel.SHIPPED_QUANTITY_1,oel.CURRENCY,oel.CSR_USER_NAME,oel.ORDER_BOOKED_DATE,oel.ORDER_CREATION_DATE,
                                                    oel.SHIP_SET_NAME,oel.ARRIVAL_SET_NAME
                                                  FROM
                                                    OE_ECC_GLOBAL_TEMP 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(freight_terms) as freight_terms,
                                                    max(product_description) as product_description,
                                                    max(price_list) as price_list,
                                                    max(line_type) as line_type,
                                                    max(payment_term) as payment_term,
                                                    max(ship_to_contact) as ship_to_contact,
                                                    max(bill_to_contact) as bill_to_contact,
                                                    max(line_status) as line_status,
                                                    max(operating_unit) as operating_unit,
                                                    max(sales_channel) as sales_channel,
                                                    max(SHIPMENT_METHOD) as SHIPMENT_METHOD,
                                                    max(on_alert) as on_alert,
                                                    max(is_open) as is_open,
                                                    max(is_booked) as is_booked,
                                                    max(order_type) as order_type,
                                                    max(agreement_name) as agreement_name,
                                                    max(SOURCE_TYPE) as SOURCE_TYPE,
                                                    max(TXN_CURRENCY) as TXN_CURRENCY,
                                                    max(func_currency) as func_currency,
                                                    max(BILL_TO_COUNTRY) as BILL_TO_COUNTRY,
                                                    max(ship_to_country) as ship_to_country,
                                                    max(STATUS) as STATUS,
                                                    max(GRAPH_ALERT_NAME) as GRAPH_ALERT_NAME,
                                                    max(alert_text) as alert_text ,
                                                    max(warehouse) as warehouse,
                                                    max(request_date_type) as REQUEST_DATE_TYPE,
                                                    max(alert_type) as alert_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