ECC Order Management, Order Lines (1)

select
x.*
from
(
SELECT * FROM (SELECT
                                                    ECC_SPEC_ID,
                                                    dfv.* ,
                                                    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.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,
                                                    oel.COMPONENT_NUMBER,
                                                    oel.SERVICE_NUMBER,
                                                    oel.SHIPMENT_NUMBER,
                                                    oel.OPTION_NUMBER,
                                                    oel.SIMPLE_LINE_NUMBER,
                                                    oel.SOURCE_DOCUMENT_TYPE_ID,
                                                    oel.ORIG_SYS_DOCUMENT_REF,
                                                    oel.SOURCE_DOCUMENT_ID,
                                                    oel.ORIG_SYS_LINE_REF,
                                                    oel.SOURCE_DOCUMENT_LINE_ID,
                                                    oel.ORDER_SOURCE_ID,
                                                    oel.DROP_SHIP_SOURCE_ID,
                                                    oel.REQUISITION_HEADER_ID,
                                                    oel.REQUISITION_LINE_ID,
                                                    oel.PO_HEADER_ID,
                                                    oel.PO_LINE_ID,
                                                    oel.LINE_LOCATION_ID,
                                                    oel.PO_RELEASE_ID, oel.LINE_SECONDARY_QTY, oel.LINE_SECONDARY_UOM, oel.LINE_QUANTITY_2, oel.SHIP_FROM_ORG, oel.BACKORDERED_FLAG,
                                                    ENABLE_TRF_DEMAND_FLAG
                                                  FROM
                                                    OE_ECC_GLOBAL_TEMP oel
                                                    , (select "ROW_ID" "'ONTL_ROW_ID'","CONTEXT_VALUE" "'ONTL_CONTEXT_VALUE'",to_char("LATE_ORDER_PENALTY") "'ONTL_LATE_ORDER_PENALTY'",fnd_ecc_dff_util.get_vset_values('OE_LINE_ATTRIBUTES','DATE_VALUE',"DATE_VALUE", NULL ,NULL,660) "'ONTL_DATE_VALUE'","TEXT" "'ONTL_TEXT'",fnd_ecc_dff_util.get_vset_values('OE_LINE_ATTRIBUTES','SUBJECTIVE_VALUE',"SUBJECTIVE_VALUE", NULL ,NULL,660) "'ONTL_SUBJECTIVE_VALUE'","NUMERIC_VALUE" "'ONTL_NUMERIC_VALUE'",fnd_ecc_dff_util.get_vset_values('OE_LINE_ATTRIBUTES','ACTIVITY_CRITERIA',"ACTIVITY_CRITERIA", NULL ,NULL,660) "'ONTL_ACTIVITY_CRITERIA'","ACTIVITY_VALUE" "'ONTL_ACTIVITY_VALUE'",fnd_ecc_dff_util.get_vset_values('OE_LINE_ATTRIBUTES','TYPE_OF_MATERIAL',"TYPE_OF_MATERIAL", NULL ,NULL,660) "'ONTL_TYPE_OF_MATERIAL'","CONCATENATED_SEGMENTS" "'ONTL_CONCATENATED_SEGMENTS'","DATE_VALUE" "'ONTL_DATE_VALUE_EDOC'","SUBJECTIVE_VALUE" "'ONTL_SUBJECTIVE_VALUE_EDOC'","ACTIVITY_CRITERIA" "'ONTL_ACTIVITY_CRITERIA_EDOC'","TYPE_OF_MATERIAL" "'ONTL_TYPE_OF_MATERIAL_EDOC'" from ( select ROWID "ROW_ID",CONTEXT "CONTEXT_VALUE",ATTRIBUTE1 "LATE_ORDER_PENALTY",ATTRIBUTE2 "DATE_VALUE",ATTRIBUTE3 "TEXT",ATTRIBUTE4 "SUBJECTIVE_VALUE",ATTRIBUTE5 "NUMERIC_VALUE",ATTRIBUTE6 "ACTIVITY_CRITERIA",ATTRIBUTE7 "ACTIVITY_VALUE",ATTRIBUTE8 "TYPE_OF_MATERIAL",ATTRIBUTE1||'.'||ATTRIBUTE2||'.'||ATTRIBUTE3||'.'||ATTRIBUTE4||'.'||ATTRIBUTE5||'.'||ATTRIBUTE6||'.'||ATTRIBUTE7||'.'||ATTRIBUTE8 "CONCATENATED_SEGMENTS" from OE_ORDER_LINES_ALL ) ) dfv, oe_order_lines_all ol 
                                                    , (select "ROW_ID" "'ONTH_ROW_ID'","CONTEXT_VALUE" "'ONTH_CONTEXT_VALUE'","ADDITIONAL_COMMENT" "'ONTH_ADDITIONAL_COMMENT'",to_char("SPECIAL_PRICE") "'ONTH_SPECIAL_PRICE'",to_char("LUMPSUM_ON_NET") "'ONTH_LUMPSUM_ON_NET'",to_char("ADDITIONAL_DISCOUNT_ON_NET___") "'ONTH_ADDITIONAL_DISCOUNT_ON_NET___'","CONCATENATED_SEGMENTS" "'ONTH_CONCATENATED_SEGMENTS'" from ( select ROWID "ROW_ID",CONTEXT "CONTEXT_VALUE",ATTRIBUTE1 "ADDITIONAL_COMMENT",ATTRIBUTE2 "SPECIAL_PRICE",ATTRIBUTE3 "LUMPSUM_ON_NET",ATTRIBUTE4 "ADDITIONAL_DISCOUNT_ON_NET___",ATTRIBUTE1||'.'||ATTRIBUTE2||'.'||ATTRIBUTE3||'.'||ATTRIBUTE4 "CONCATENATED_SEGMENTS" from OE_ORDER_HEADERS_ALL ) ) hdfv, oe_order_headers_all h 
                                                  WHERE
                                                    1 = 1
                                                     and  ol.rowid=dfv."'ONTL_ROW_ID'"(+) and oel.line_id=ol.line_id 
                                                     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,
                                                    max(SALESPERSON) as SALESPERSON
                                                    for language_code in ('US' "US"))
) x
where
2=2