ECC Order Management, Order Lines

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