APPS.ISC_EDW_BOOKINGS_F_FCV
[Home] [Help]
| Product: | ONT - Order Management |
|---|---|
| Description: | |
| Implementation/DBA Data: |
APPS.ISC_EDW_BOOKINGS_F_FCV
|
SELECT TO_CHAR(L.AGREEMENT_ID) AGREEMENT_ID
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK('SOL'
, 'AGREEMENT_TYPE'
, AGR_TY.AGREEMENT_TYPE_CODE) AGREEMENT_TYPE_FK
, EDW_TRD_PARTNER_PKG.CUSTOMER_SITE_FK( H.INVOICE_TO_ORG_ID) BILL_TO_CUST_FK
, EDW_GEOGRAPHY_PKG.CUSTOMER_SITE_LOCATION_FK( H.INVOICE_TO_ORG_ID) BILL_TO_LOC_FK
, NVL(TO_CHAR(L.LINE_ID)
, 'NA_EDW')||'-'|| INST.INSTANCE_CODE BOOKINGS_PK
, EDW_BIM_CMPGNS_PKG.GET_CAMPAIGN_FK( L.MARKETING_SOURCE_CODE_ID) CAMPAIGN_ACTL_FK
, EDW_BIM_CMPGNS_PKG.GET_CAMPAIGN_FK( H.MARKETING_SOURCE_CODE_ID) CAMPAIGN_INIT_FK
, DECODE(L.CANCELLED_QUANTITY
, TO_NUMBER(NULL)
, 'NA_EDW'
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK('SOL'
, 'CANCEL_CODE'
, 'CANCELLATION')) CANCEL_REASON_FK
, DECODE(L.ITEM_TYPE_CODE
, 'CONFIG'
, 'Y'
, 'N') CONFIGURATION_ITEM_FLAG
, DECODE(H.CONVERSION_TYPE_CODE
, 'USER'
, H.CONVERSION_RATE_DATE
, DECODE(RA_ALL.TRX_DATE
, NULL
, H.BOOKED_DATE
, RA_ALL.TRX_DATE)) CONVERSION_DATE
, TO_CHAR(EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( 1
, TO_NUMBER(NULL)
, H.TRANSACTIONAL_CURR_CODE
, FSPA.SET_OF_BOOKS_ID
, DECODE(H.CONVERSION_TYPE_CODE
, 'USER'
, H.CONVERSION_RATE_DATE
, DECODE(RA_ALL.TRX_DATE
, NULL
, H.BOOKED_DATE
, RA_ALL.TRX_DATE))
, H.CONVERSION_TYPE_CODE)) CONVERSION_RATE
, H.CONVERSION_TYPE_CODE CONVERSION_TYPE
, H.TRANSACTIONAL_CURR_CODE CURRENCY_TRN_FK
, EDW_TRD_PARTNER_PKG.CUSTOMER_SITE_FK( L.SOLD_TO_ORG_ID) CUSTOMER_FK
, L.CUST_PO_NUMBER CUST_PO_NUMBER
, EDW_TIME_PKG.CAL_DAY_FK(H.BOOKED_DATE
, FSPA.SET_OF_BOOKS_ID) DATE_BOOKED_FK
, EDW_TIME_PKG.CAL_DAY_FK(L.PROMISE_DATE
, FSPA.SET_OF_BOOKS_ID) DATE_PROMISED_FK
, EDW_TIME_PKG.CAL_DAY_FK(L.REQUEST_DATE
, FSPA.SET_OF_BOOKS_ID) DATE_REQUESTED_FK
, EDW_TIME_PKG.CAL_DAY_FK(L.SCHEDULE_SHIP_DATE
, FSPA.SET_OF_BOOKS_ID) DATE_SCHEDULED_FK
, EDW_DEMAND_CLASS_PKG.GET_DEMAND_CLASS_FK( L.DEMAND_CLASS_CODE) DEMAND_CLASS_FK
, EDW_BIM_EVTOFFERS_PKG.GET_EVTOFFER_FK( L.MARKETING_SOURCE_CODE_ID) EVENT_OFFER_ACTL_FK
, EDW_BIM_EVTOFFERS_PKG.GET_EVTOFFER_FK( H.MARKETING_SOURCE_CODE_ID) EVENT_OFFER_INIT_FK
, EDW_BIM_EVTOFFERS_PKG.GET_EVTOFFER_FK( H.MARKETING_SOURCE_CODE_ID) EVENT_OFFER_REG_FK
, TO_CHAR(H.HEADER_ID) HEADER_ID
, DECODE(L.ITEM_TYPE_CODE
, 'INCLUDED'
, 'Y'
, 'N') INCLUDED_ITEM_FLAG
, SUBSTR(INST.INSTANCE_CODE
, 1
, 40) INSTANCE
, INST.INSTANCE_CODE INSTANCE_FK
, EDW_ORGANIZATION_PKG.INT_ORGANIZATION_FK( L.ORG_ID) INV_ORG_FK
, EDW_ITEMS_PKG.ITEM_ORG_FK( L.INVENTORY_ITEM_ID
, L.SHIP_FROM_ORG_ID) ITEM_ORG_FK
, L.LAST_UPDATE_DATE LAST_UPDATE_DATE
, 'NA_EDW' LINE_DETAIL_ID
, TO_CHAR(L.LINE_ID) LINE_ID
, EDW_BIM_MKTSGMTS_PKG.GET_MKTSGMT_FK( H.MARKETING_SOURCE_CODE_ID
, H.SOLD_TO_ORG_ID) MARKET_SEGMENT_FK
, EDW_BIM_MEDIA_PKG.GET_MEDIA_FK( L.MARKETING_SOURCE_CODE_ID) MEDCHN_ACTL_FK
, EDW_BIM_MEDIA_PKG.GET_MEDIA_FK( H.MARKETING_SOURCE_CODE_ID) MEDCHN_INIT_FK
, EDW_BIM_OFFERS_PKG.GET_OFFER_FK(H.HEADER_ID) OFFER_HDR_FK
, EDW_BIM_OFFERS_PKG.GET_OFFER_FK(H.HEADER_ID
, L.LINE_ID) OFFER_LINE_FK
, EDW_ORGANIZATION_PKG.OPERATING_UNIT_FK( L.ORG_ID) OPERATING_UNIT_FK
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK( 'SOL'
, 'ORDER_CATEGORY'
, UPPER(L.LINE_CATEGORY_CODE)) ORDER_CATEGORY_FK
, TO_CHAR(H.ORDER_NUMBER) ORDER_NUMBER
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK( 'SOS'
, 'ORDER_SOURCE'
, UPPER(OS.NAME)) ORDER_SOURCE_FK
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK( 'SOO'
, 'ORDER_TYPE'
, UPPER(ORDTYP.NAME)) ORDER_TYPE_FK
, TO_CHAR(L.PRICE_LIST_ID) PRICE_LIST_ID
, NVL(EDW_UTIL.GET_UOM_CONV_RATE(L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID) * L.CANCELLED_QUANTITY
, 0) QTY_CANCELLED
, NVL(EDW_UTIL.GET_UOM_CONV_RATE(L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID) * L.INVOICED_QUANTITY
, 0) QTY_INVOICED
, NVL(EDW_UTIL.GET_UOM_CONV_RATE(L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID) * L.ORDERED_QUANTITY
, 0) QTY_ORDERED
, 0 QTY_RESERVED
, NVL(EDW_UTIL.GET_UOM_CONV_RATE(L.SHIPPING_QUANTITY_UOM
, L.INVENTORY_ITEM_ID) * DECODE(L.LINE_CATEGORY_CODE
, 'RETURN'
, L.SHIPPED_QUANTITY
, 0)
, 0) QTY_RETURNED
, NVL(EDW_UTIL.GET_UOM_CONV_RATE(L.SHIPPING_QUANTITY_UOM
, L.INVENTORY_ITEM_ID) * DECODE(L.LINE_CATEGORY_CODE
, 'ORDER'
, L.SHIPPED_QUANTITY
, 0)
, 0) QTY_SHIPPED
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK( 'AR'
, 'CREDIT_MEMO_REASON'
, L.RETURN_REASON_CODE) RETURN_REASON_FK
, EDW_SALES_CHANNEL_PKG.GET_SALES_CHANNEL_FK( H.SALES_CHANNEL_CODE) SALES_CHANNEL_FK
, EDW_HR_PERSON_PKG.SALES_REP_FK(L.SALESREP_ID
, L.ORG_ID) SALES_PERSON_FK
, NVL(TO_CHAR(FSPA.SET_OF_BOOKS_ID)
, 'NA_EDW')||'-' ||SUBSTR(EDW_INSTANCE.GET_CODE
, 1
, 40) SET_OF_BOOKS_FK
, EDW_TRD_PARTNER_PKG.CUSTOMER_SITE_FK( L.SHIP_TO_ORG_ID) SHIP_TO_CUST_FK
, EDW_GEOGRAPHY_PKG.CUSTOMER_SITE_LOCATION_FK( L.SHIP_TO_ORG_ID) SHIP_TO_LOC_FK
, EDW_BIM_SRCLISTS_PKG.GET_SRCLIST_FK(H.SOLD_TO_ORG_ID) SOURCE_LIST_FK
, EDW_BIM_TRGTSGMTS_PKG.GET_TRGTSGMT_FK( L.MARKETING_SOURCE_CODE_ID
, L.SOLD_TO_ORG_ID) TARGET_SEGMENT_ACTL_FK
, EDW_BIM_TRGTSGMTS_PKG.GET_TRGTSGMT_FK( H.MARKETING_SOURCE_CODE_ID
, H.SOLD_TO_ORG_ID) TARGET_SEGMENT_INIT_FK
, EDW_PROJ_PKG.TASK_FK(L.TASK_ID
, L.PROJECT_ID) TASK_FK
, EDW_ITEMS_PKG.ITEM_ORG_FK( PL.INVENTORY_ITEM_ID
, PL.SHIP_FROM_ORG_ID) TOP_MODEL_FK
, MSI.SO_TRANSACTIONS_FLAG TRANSACTABLE_FLAG
, NVL(EDW_UTIL.GET_ITEM_COST(L.INVENTORY_ITEM_ID
, L.ORG_ID) * EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( 1
, TO_NUMBER(NULL)
, H.TRANSACTIONAL_CURR_CODE
, FSPA.SET_OF_BOOKS_ID
, H.BOOKED_DATE
, H.CONVERSION_TYPE_CODE) / EDW_UTIL.GET_UOM_CONV_RATE( L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID)
, 0) UNIT_COST_G
, NVL(EDW_UTIL.GET_ITEM_COST(L.INVENTORY_ITEM_ID
, L.ORG_ID) / EDW_UTIL.GET_UOM_CONV_RATE( L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID)
, 0) UNIT_COST_T
, NVL(EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( 1
, TO_NUMBER(NULL)
, H.TRANSACTIONAL_CURR_CODE
, FSPA.SET_OF_BOOKS_ID
, H.BOOKED_DATE
, H.CONVERSION_TYPE_CODE) * L.UNIT_LIST_PRICE / EDW_UTIL.GET_UOM_CONV_RATE( L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID)
, 0) UNIT_LIST_PRC_G
, L.UNIT_LIST_PRICE UNIT_LIST_PRC_T
, NVL(EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( 1
, TO_NUMBER(NULL)
, H.TRANSACTIONAL_CURR_CODE
, FSPA.SET_OF_BOOKS_ID
, H.BOOKED_DATE
, H.CONVERSION_TYPE_CODE) * L.UNIT_SELLING_PRICE / EDW_UTIL.GET_UOM_CONV_RATE( L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID)
, 0) UNIT_SELL_PRC_G
, NVL(L.UNIT_SELLING_PRICE / EDW_UTIL.GET_UOM_CONV_RATE( L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID)
, 0) UNIT_SELL_PRC_T
, EDW_UTIL.GET_EDW_BASE_UOM(L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID) UOM_UOM_FK
, 'NA_EDW' USER_ATTRIBUTE1
, 'NA_EDW' USER_ATTRIBUTE10
, 'NA_EDW' USER_ATTRIBUTE11
, 'NA_EDW' USER_ATTRIBUTE12
, 'NA_EDW' USER_ATTRIBUTE13
, 'NA_EDW' USER_ATTRIBUTE14
, 'NA_EDW' USER_ATTRIBUTE15
, 'NA_EDW' USER_ATTRIBUTE2
, 'NA_EDW' USER_ATTRIBUTE3
, 'NA_EDW' USER_ATTRIBUTE4
, 'NA_EDW' USER_ATTRIBUTE5
, 'NA_EDW' USER_ATTRIBUTE6
, 'NA_EDW' USER_ATTRIBUTE7
, 'NA_EDW' USER_ATTRIBUTE8
, 'NA_EDW' USER_ATTRIBUTE9
, 'NA_EDW' USER_FK1
, 'NA_EDW' USER_FK2
, 'NA_EDW' USER_FK3
, 'NA_EDW' USER_FK4
, 'NA_EDW' USER_FK5
, 0 USER_MEASURE1
, 0 USER_MEASURE2
, 0 USER_MEASURE3
, 0 USER_MEASURE4
, 0 USER_MEASURE5
FROM OE_AGREEMENTS_B AGR_TY
, OE_ORDER_SOURCES OS
, MTL_SYSTEM_ITEMS_B MSI
, OE_ORDER_LINES_ALL PL
, OE_ORDER_HEADERS_ALL H
, RA_CUSTOMER_TRX_LINES_ALL RA
, RA_CUSTOMER_TRX_ALL RA_ALL
, OE_TRANSACTION_TYPES_TL ORDTYP
, FINANCIALS_SYSTEM_PARAMS_ALL FSPA
, /*MTL_RESERVATIONS MTL
, */ EDW_LOCAL_INSTANCE INST
, OE_ORDER_LINES_ALL L
WHERE L.REFERENCE_CUSTOMER_TRX_LINE_ID = RA.CUSTOMER_TRX_LINE_ID (+)
AND RA.CUSTOMER_TRX_ID = RA_ALL.CUSTOMER_TRX_ID (+)
AND TO_CHAR(L.LINE_ID) = RA.INTERFACE_LINE_ATTRIBUTE6 (+)
AND RA.INTERFACE_LINE_CONTEXT (+) = 'ORDER ENTRY'
AND L.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID (+)
AND L.ORG_ID = MSI.ORGANIZATION_ID (+)
AND L.AGREEMENT_ID = AGR_TY.AGREEMENT_ID (+)
AND L.ORG_ID = FSPA.ORG_ID (+)
AND L.HEADER_ID = H.HEADER_ID (+)
AND NVL(L.TOP_MODEL_LINE_ID
, L.LINE_ID) = PL.LINE_ID (+)
AND L.ORG_ID = PL.ORG_ID (+)
AND L.SOURCE_DOCUMENT_TYPE_ID = OS.ORDER_SOURCE_ID (+)
AND L.LINE_TYPE_ID = ORDTYP.TRANSACTION_TYPE_ID (+)
AND USERENV('LANG') = ORDTYP.LANGUAGE (+) /*AND MTL.RESERVATION_QUANTITY IS NOT NULL
AND L.LINE_ID = MTL.DEMAND_SOURCE_LINE_ID (+)*/ UNION ALL SELECT TO_CHAR(L.AGREEMENT_ID) AGREEMENT_ID
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK('SOL'
, 'AGREEMENT_TYPE'
, AGR_TY.AGREEMENT_TYPE_CODE) AGREEMENT_TYPE_FK
, EDW_TRD_PARTNER_PKG.CUSTOMER_SITE_FK( H.INVOICE_TO_ORG_ID) BILL_TO_CUST_FK
, EDW_GEOGRAPHY_PKG.CUSTOMER_SITE_LOCATION_FK( H.INVOICE_TO_ORG_ID) BILL_TO_LOC_FK
, MTL.RESERVATION_ID||'-'||NVL(TO_CHAR(L.LINE_ID)
, 'NA_EDW')||'-'|| INST.INSTANCE_CODE BOOKINGS_PK
, EDW_BIM_CMPGNS_PKG.GET_CAMPAIGN_FK( L.MARKETING_SOURCE_CODE_ID) CAMPAIGN_ACTL_FK
, EDW_BIM_CMPGNS_PKG.GET_CAMPAIGN_FK( H.MARKETING_SOURCE_CODE_ID) CAMPAIGN_INIT_FK
, DECODE(L.CANCELLED_QUANTITY
, TO_NUMBER(NULL)
, 'NA_EDW'
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK('SOL'
, 'CANCEL_CODE'
, 'CANCELLATION')) CANCEL_REASON_FK
, DECODE(L.ITEM_TYPE_CODE
, 'CONFIG'
, 'Y'
, 'N') CONFIGURATION_ITEM_FLAG
, DECODE(H.CONVERSION_TYPE_CODE
, 'USER'
, H.CONVERSION_RATE_DATE
, DECODE(RA_ALL.TRX_DATE
, NULL
, H.BOOKED_DATE
, RA_ALL.TRX_DATE)) CONVERSION_DATE
, TO_CHAR(EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT( 1
, TO_NUMBER(NULL)
, H.TRANSACTIONAL_CURR_CODE
, FSPA.SET_OF_BOOKS_ID
, DECODE(H.CONVERSION_TYPE_CODE
, 'USER'
, H.CONVERSION_RATE_DATE
, DECODE(RA_ALL.TRX_DATE
, NULL
, H.BOOKED_DATE
, RA_ALL.TRX_DATE))
, H.CONVERSION_TYPE_CODE)) CONVERSION_RATE
, H.CONVERSION_TYPE_CODE CONVERSION_TYPE
, H.TRANSACTIONAL_CURR_CODE CURRENCY_TRN_FK
, EDW_TRD_PARTNER_PKG.CUSTOMER_SITE_FK( L.SOLD_TO_ORG_ID) CUSTOMER_FK
, L.CUST_PO_NUMBER CUST_PO_NUMBER
, EDW_TIME_PKG.CAL_DAY_FK(H.BOOKED_DATE
, FSPA.SET_OF_BOOKS_ID) DATE_BOOKED_FK
, EDW_TIME_PKG.CAL_DAY_FK(L.PROMISE_DATE
, FSPA.SET_OF_BOOKS_ID) DATE_PROMISED_FK
, EDW_TIME_PKG.CAL_DAY_FK(L.REQUEST_DATE
, FSPA.SET_OF_BOOKS_ID) DATE_REQUESTED_FK
, EDW_TIME_PKG.CAL_DAY_FK(L.SCHEDULE_SHIP_DATE
, FSPA.SET_OF_BOOKS_ID) DATE_SCHEDULED_FK
, EDW_DEMAND_CLASS_PKG.GET_DEMAND_CLASS_FK( L.DEMAND_CLASS_CODE) DEMAND_CLASS_FK
, EDW_BIM_EVTOFFERS_PKG.GET_EVTOFFER_FK( L.MARKETING_SOURCE_CODE_ID) EVENT_OFFER_ACTL_FK
, EDW_BIM_EVTOFFERS_PKG.GET_EVTOFFER_FK( H.MARKETING_SOURCE_CODE_ID) EVENT_OFFER_INIT_FK
, EDW_BIM_EVTOFFERS_PKG.GET_EVTOFFER_FK( H.MARKETING_SOURCE_CODE_ID) EVENT_OFFER_REG_FK
, TO_CHAR(H.HEADER_ID) HEADER_ID
, DECODE(L.ITEM_TYPE_CODE
, 'INCLUDED'
, 'Y'
, 'N') INCLUDED_ITEM_FLAG
, SUBSTR(INST.INSTANCE_CODE
, 1
, 40) INSTANCE
, INST.INSTANCE_CODE INSTANCE_FK
, EDW_ORGANIZATION_PKG.INT_ORGANIZATION_FK( L.ORG_ID) INV_ORG_FK
, EDW_ITEMS_PKG.ITEM_ORG_FK( L.INVENTORY_ITEM_ID
, L.SHIP_FROM_ORG_ID) ITEM_ORG_FK
, L.LAST_UPDATE_DATE LAST_UPDATE_DATE
, 'NA_EDW' LINE_DETAIL_ID
, TO_CHAR(L.LINE_ID) LINE_ID
, EDW_BIM_MKTSGMTS_PKG.GET_MKTSGMT_FK( H.MARKETING_SOURCE_CODE_ID
, H.SOLD_TO_ORG_ID) MARKET_SEGMENT_FK
, EDW_BIM_MEDIA_PKG.GET_MEDIA_FK( L.MARKETING_SOURCE_CODE_ID) MEDCHN_ACTL_FK
, EDW_BIM_MEDIA_PKG.GET_MEDIA_FK( H.MARKETING_SOURCE_CODE_ID) MEDCHN_INIT_FK
, EDW_BIM_OFFERS_PKG.GET_OFFER_FK(H.HEADER_ID) OFFER_HDR_FK
, EDW_BIM_OFFERS_PKG.GET_OFFER_FK(H.HEADER_ID
, L.LINE_ID) OFFER_LINE_FK
, EDW_ORGANIZATION_PKG.OPERATING_UNIT_FK( L.ORG_ID) OPERATING_UNIT_FK
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK( 'SOL'
, 'ORDER_CATEGORY'
, UPPER(L.LINE_CATEGORY_CODE)) ORDER_CATEGORY_FK
, TO_CHAR(H.ORDER_NUMBER) ORDER_NUMBER
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK( 'SOS'
, 'ORDER_SOURCE'
, UPPER(OS.NAME)) ORDER_SOURCE_FK
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK( 'SOO'
, 'ORDER_TYPE'
, UPPER(ORDTYP.NAME)) ORDER_TYPE_FK
, TO_CHAR(L.PRICE_LIST_ID) PRICE_LIST_ID
, 0 QTY_CANCELLED
, 0 QTY_INVOICED
, 0 QTY_ORDERED
, EDW_UTIL.GET_UOM_CONV_RATE(MTL.RESERVATION_UOM_CODE
, L.INVENTORY_ITEM_ID) * MTL.RESERVATION_QUANTITY QTY_RESERVED
, 0 QTY_RETURNED
, 0 QTY_SHIPPED
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK( 'AR'
, 'CREDIT_MEMO_REASON'
, L.RETURN_REASON_CODE) RETURN_REASON_FK
, EDW_SALES_CHANNEL_PKG.GET_SALES_CHANNEL_FK( H.SALES_CHANNEL_CODE) SALES_CHANNEL_FK
, EDW_HR_PERSON_PKG.SALES_REP_FK(L.SALESREP_ID
, L.ORG_ID) SALES_PERSON_FK
, NVL(TO_CHAR(FSPA.SET_OF_BOOKS_ID)
, 'NA_EDW')||'-' ||SUBSTR(EDW_INSTANCE.GET_CODE
, 1
, 40) SET_OF_BOOKS_FK
, EDW_TRD_PARTNER_PKG.CUSTOMER_SITE_FK( L.SHIP_TO_ORG_ID) SHIP_TO_CUST_FK
, EDW_GEOGRAPHY_PKG.CUSTOMER_SITE_LOCATION_FK( L.SHIP_TO_ORG_ID) SHIP_TO_LOC_FK
, EDW_BIM_SRCLISTS_PKG.GET_SRCLIST_FK(H.SOLD_TO_ORG_ID) SOURCE_LIST_FK
, EDW_BIM_TRGTSGMTS_PKG.GET_TRGTSGMT_FK( L.MARKETING_SOURCE_CODE_ID
, L.SOLD_TO_ORG_ID) TARGET_SEGMENT_ACTL_FK
, EDW_BIM_TRGTSGMTS_PKG.GET_TRGTSGMT_FK( H.MARKETING_SOURCE_CODE_ID
, H.SOLD_TO_ORG_ID) TARGET_SEGMENT_INIT_FK
, EDW_PROJ_PKG.TASK_FK(L.TASK_ID
, L.PROJECT_ID) TASK_FK
, EDW_ITEMS_PKG.ITEM_ORG_FK( PL.INVENTORY_ITEM_ID
, PL.SHIP_FROM_ORG_ID) TOP_MODEL_FK
, MSI.SO_TRANSACTIONS_FLAG TRANSACTABLE_FLAG
, 0 UNIT_COST_G
, 0 UNIT_COST_T
, 0 UNIT_LIST_PRC_G
, 0 UNIT_LIST_PRC_T
, 0 UNIT_SELL_PRC_G
, 0 UNIT_SELL_PRC_T
, EDW_UTIL.GET_EDW_BASE_UOM(L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID) UOM_UOM_FK
, 'NA_EDW' USER_ATTRIBUTE1
, 'NA_EDW' USER_ATTRIBUTE10
, 'NA_EDW' USER_ATTRIBUTE11
, 'NA_EDW' USER_ATTRIBUTE12
, 'NA_EDW' USER_ATTRIBUTE13
, 'NA_EDW' USER_ATTRIBUTE14
, 'NA_EDW' USER_ATTRIBUTE15
, 'NA_EDW' USER_ATTRIBUTE2
, 'NA_EDW' USER_ATTRIBUTE3
, 'NA_EDW' USER_ATTRIBUTE4
, 'NA_EDW' USER_ATTRIBUTE5
, 'NA_EDW' USER_ATTRIBUTE6
, 'NA_EDW' USER_ATTRIBUTE7
, 'NA_EDW' USER_ATTRIBUTE8
, 'NA_EDW' USER_ATTRIBUTE9
, 'NA_EDW' USER_FK1
, 'NA_EDW' USER_FK2
, 'NA_EDW' USER_FK3
, 'NA_EDW' USER_FK4
, 'NA_EDW' USER_FK5
, 0 USER_MEASURE1
, 0 USER_MEASURE2
, 0 USER_MEASURE3
, 0 USER_MEASURE4
, 0 USER_MEASURE5
FROM OE_AGREEMENTS_B AGR_TY
, OE_ORDER_SOURCES OS
, MTL_SYSTEM_ITEMS_B MSI
, OE_ORDER_LINES_ALL PL
, OE_ORDER_HEADERS_ALL H
, RA_CUSTOMER_TRX_LINES_ALL RA
, RA_CUSTOMER_TRX_ALL RA_ALL
, OE_TRANSACTION_TYPES_TL ORDTYP
, FINANCIALS_SYSTEM_PARAMS_ALL FSPA
, MTL_RESERVATIONS MTL
, EDW_LOCAL_INSTANCE INST
, OE_ORDER_LINES_ALL L
WHERE L.REFERENCE_CUSTOMER_TRX_LINE_ID = RA.CUSTOMER_TRX_LINE_ID (+)
AND RA.CUSTOMER_TRX_ID = RA_ALL.CUSTOMER_TRX_ID (+)
AND TO_CHAR(L.LINE_ID) = RA.INTERFACE_LINE_ATTRIBUTE6 (+)
AND RA.INTERFACE_LINE_CONTEXT (+) = 'ORDER ENTRY'
AND L.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID (+)
AND L.ORG_ID = MSI.ORGANIZATION_ID (+)
AND L.AGREEMENT_ID = AGR_TY.AGREEMENT_ID (+)
AND L.ORG_ID = FSPA.ORG_ID (+)
AND L.HEADER_ID = H.HEADER_ID (+)
AND NVL(L.TOP_MODEL_LINE_ID
, L.LINE_ID) = PL.LINE_ID (+)
AND L.ORG_ID = PL.ORG_ID (+)
AND L.SOURCE_DOCUMENT_TYPE_ID = OS.ORDER_SOURCE_ID (+)
AND L.LINE_TYPE_ID = ORDTYP.TRANSACTION_TYPE_ID (+)
AND USERENV('LANG') = ORDTYP.LANGUAGE (+)
AND MTL.RESERVATION_QUANTITY IS NOT NULL
AND L.LINE_ID = MTL.DEMAND_SOURCE_LINE_ID (+)