VIEW: APPS.MTH_SRC_WO_SO_DAT_V

Source

View Text - Preformatted

SELECT RESERVATION_PK,WORKORDER_FK,SALESORDER_NUMBER,SALESORDER_lNUMBER,CUSTOMER,REQUESTED_DATE,PROMISED_dATE,SCHEDULED_SHIP_DATE,SYSTEM_FK,QUANTITY_G,PRIMARY_UOM,PRODUCT_FK,USER_DIM1_FK, USER_DIM2_FK, USER_DIM3_FK, USER_DIM4_FK, USER_DIM5_FK, USER_ATTR1, USER_ATTR2, USER_ATTR3, USER_ATTR4, USER_ATTR5, USER_MEASURE1, USER_MEASURE2, USER_MEASURE3, USER_MEASURE4, USER_MEASURE5,SUPPLY_SOURCE_HEADER_ID,ORDERED_ITEM_ID,LAST_UPDATE_DATE,SOURCE_ORG_CODE,OP_SO_PROCESSING_FLAG FROM (SELECT (mrv.RESERVATION_ID|| '-'||fnd_profile.value('MTH_EBS_GLOBAL_NAME')||'-'||'1') RESERVATION_PK, CAST(NULL AS VARCHAR2(150)) WORKORDER_FK, moh.ORDER_NUMBER SALESORDER_NUMBER, mol.LINE_NUMBER SALESORDER_LNUMBER, hp.PARTY_NAME CUSTOMER, mol.REQUEST_DATE REQUESTED_DATE, mol.PROMISE_DATE PROMISED_DATE, mol.SCHEDULE_SHIP_DATE SCHEDULED_SHIP_DATE, fnd_profile.value('MTH_EBS_GLOBAL_NAME') SYSTEM_FK, CAST(NULL AS VARCHAR2(150)) QUANTITY_G, CAST(NULL AS VARCHAR2(150)) PRIMARY_UOM, CAST(NULL AS VARCHAR2(150)) PRODUCT_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM1_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM2_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM3_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM4_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM5_FK, CAST(NULL AS VARCHAR2(150)) USER_ATTR1, CAST(NULL AS VARCHAR2(150)) USER_ATTR2, CAST(NULL AS VARCHAR2(150)) USER_ATTR3, CAST(NULL AS VARCHAR2(150)) USER_ATTR4, CAST(NULL AS VARCHAR2(150)) USER_ATTR5, CAST(NULL AS NUMBER) USER_MEASURE1, CAST(NULL AS NUMBER) USER_MEASURE2, CAST(NULL AS NUMBER) USER_MEASURE3, CAST(NULL AS NUMBER) USER_MEASURE4, CAST(NULL AS NUMBER) USER_MEASURE5, mrv.SUPPLY_SOURCE_HEADER_ID SUPPLY_SOURCE_HEADER_ID, mol.ORDERED_ITEM_ID ORDERED_ITEM_ID, mol.LAST_UPDATE_DATE LAST_UPDATE_DATE, mtp.ORGANIZATION_CODE SOURCE_ORG_CODE, mos.PROCESSING_FLAG OP_SO_PROCESSING_FLAG FROM OE_ORDER_LINES_ALL mol, MTH_OPEN_SALES_ORDERS_F mos, MTL_RESERVATIONS mrv, OE_ORDER_HEADERS_ALL moh, HZ_PARTIES hp, HZ_CUST_ACCOUNTS hca, MTL_PARAMETERS mtp WHERE mos.EBS_HEADER_ID = mol.HEADER_ID AND mol.LINE_ID = mrv.DEMAND_SOURCE_LINE_ID AND mrv.DEMAND_SOURCE_TYPE_ID = 2 AND mrv.SUPPLY_SOURCE_TYPE_ID in (5,13) AND mrv.SUPPLY_SOURCE_HEADER_ID IS NOT NULL AND mol.HEADER_ID = moh.HEADER_ID AND hca.PARTY_ID = hp.PARTY_ID AND moh.SOLD_TO_ORG_ID = hca.CUST_ACCOUNT_ID AND mtp.ORGANIZATION_ID = mrv.ORGANIZATION_ID AND mos.RESERVATION_ID = mrv.RESERVATION_ID UNION SELECT (gsr.BATCH_RES_ID|| '-'||fnd_profile.value('MTH_EBS_GLOBAL_NAME')||'-'||'2') RESERVATION_PK, CAST(NULL AS VARCHAR2(150)) WORKORDER_FK, moh.ORDER_NUMBER SALESORDER_NUMBER, mol.LINE_NUMBER SALESORDER_LNUMBER, hp.PARTY_NAME CUSTOMER, mol.REQUEST_DATE REQUESTED_DATE, mol.PROMISE_DATE PROMISED_DATE, mol.SCHEDULE_SHIP_DATE SCHEDULED_SHIP_DATE, fnd_profile.value('MTH_EBS_GLOBAL_NAME') SYSTEM_FK, CAST(NULL AS VARCHAR2(150)) QUANTITY_G, CAST(NULL AS VARCHAR2(150)) PRIMARY_UOM, CAST(NULL AS VARCHAR2(150)) PRODUCT_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM1_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM2_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM3_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM4_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM5_FK, CAST(NULL AS VARCHAR2(150)) USER_ATTR1, CAST(NULL AS VARCHAR2(150)) USER_ATTR2, CAST(NULL AS VARCHAR2(150)) USER_ATTR3, CAST(NULL AS VARCHAR2(150)) USER_ATTR4, CAST(NULL AS VARCHAR2(150)) USER_ATTR5, CAST(NULL AS NUMBER) USER_MEASURE1, CAST(NULL AS NUMBER) USER_MEASURE2, CAST(NULL AS NUMBER) USER_MEASURE3, CAST(NULL AS NUMBER) USER_MEASURE4, CAST(NULL AS NUMBER) USER_MEASURE5, gsr.BATCH_ID SUPPLY_SOURCE_HEADER_ID, itm.ITEM_ID ORDERED_ITEM_ID, mol.LAST_UPDATE_DATE LAST_UPDATE_DATE, mtp.ORGANIZATION_CODE SOURCE_ORG_CODE, mos.PROCESSING_FLAG OP_SO_PROCESSING_FLAG FROM OE_ORDER_LINES_ALL mol, MTL_SYSTEM_ITEMS_B msi, IC_ITEM_MST itm, MTH_OPEN_SALES_ORDERS_F mos, GML_BATCH_SO_RESERVATIONS gsr, OE_ORDER_HEADERS_ALL moh, HZ_PARTIES hp, HZ_CUST_ACCOUNTS hca, MTL_PARAMETERS mtp WHERE mol.ORDERED_ITEM_ID = msi.INVENTORY_ITEM_ID AND msi.SEGMENT1 = itm.ITEM_NO AND mos.EBS_HEADER_ID = mol.HEADER_ID AND mol.LINE_ID = gsr.SO_LINE_ID AND gsr.DELETE_MARK = 0 AND mol.HEADER_ID = moh.HEADER_ID AND hca.PARTY_ID = hp.PARTY_ID AND moh.SOLD_TO_ORG_ID = hca.CUST_ACCOUNT_ID AND mtp.ORGANIZATION_ID = gsr.ORGANIZATION_ID AND mos.RESERVATION_ID = gsr.BATCH_RES_ID UNION SELECT (mos.RESERVATION_ID|| '-'||fnd_profile.value('MTH_EBS_GLOBAL_NAME')||'-'||to_char(mos.source)) RESERVATION_PK, CAST(NULL AS VARCHAR2(150)) WORKORDER_FK, moh.ORDER_NUMBER SALESORDER_NUMBER, CAST(NULL AS NUMBER) SALESORDER_LNUMBER, CAST(NULL AS VARCHAR2(150)) CUSTOMER, CAST(NULL AS DATE) REQUESTED_DATE, CAST(NULL AS DATE) PROMISED_DATE, CAST(NULL AS DATE) SCHEDULED_SHIP_DATE, fnd_profile.value('MTH_EBS_GLOBAL_NAME') SYSTEM_FK, '0' QUANTITY_G, CAST(NULL AS VARCHAR2(150)) PRIMARY_UOM, CAST(NULL AS VARCHAR2(150)) PRODUCT_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM1_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM2_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM3_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM4_FK, CAST(NULL AS VARCHAR2(150)) USER_DIM5_FK, CAST(NULL AS VARCHAR2(150)) USER_ATTR1, CAST(NULL AS VARCHAR2(150)) USER_ATTR2, CAST(NULL AS VARCHAR2(150)) USER_ATTR3, CAST(NULL AS VARCHAR2(150)) USER_ATTR4, CAST(NULL AS VARCHAR2(150)) USER_ATTR5, CAST(NULL AS NUMBER) USER_MEASURE1, CAST(NULL AS NUMBER) USER_MEASURE2, CAST(NULL AS NUMBER) USER_MEASURE3, CAST(NULL AS NUMBER) USER_MEASURE4, CAST(NULL AS NUMBER) USER_MEASURE5, mos.SUPPLY_SOURCE_HEADER_ID SUPPLY_SOURCE_HEADER_ID, CAST(NULL AS NUMBER) ORDERED_ITEM_ID, CAST(NULL AS DATE) LAST_UPDATE_DATE, CAST(NULL AS VARCHAR2(150)) SOURCE_ORG_CODE, mos.PROCESSING_FLAG OP_SO_PROCESSING_FLAG FROM (SELECT f.* FROM MTH_OPEN_SALES_ORDERS_F f WHERE NOT EXISTS (SELECT * FROM (SELECT reservation_id FROM MTL_RESERVATIONS UNION ALL SELECT batch_res_id reservation_id FROM GML_BATCH_SO_RESERVATIONS) r WHERE r.reservation_id = f.reservation_id)) mos, OE_ORDER_HEADERS_ALL moh WHERE moh.HEADER_ID = mos.EBS_HEADER_ID)

View Text - HTML Formatted

SELECT RESERVATION_PK
, WORKORDER_FK
, SALESORDER_NUMBER
, SALESORDER_LNUMBER
, CUSTOMER
, REQUESTED_DATE
, PROMISED_DATE
, SCHEDULED_SHIP_DATE
, SYSTEM_FK
, QUANTITY_G
, PRIMARY_UOM
, PRODUCT_FK
, USER_DIM1_FK
, USER_DIM2_FK
, USER_DIM3_FK
, USER_DIM4_FK
, USER_DIM5_FK
, USER_ATTR1
, USER_ATTR2
, USER_ATTR3
, USER_ATTR4
, USER_ATTR5
, USER_MEASURE1
, USER_MEASURE2
, USER_MEASURE3
, USER_MEASURE4
, USER_MEASURE5
, SUPPLY_SOURCE_HEADER_ID
, ORDERED_ITEM_ID
, LAST_UPDATE_DATE
, SOURCE_ORG_CODE
, OP_SO_PROCESSING_FLAG
FROM (SELECT (MRV.RESERVATION_ID|| '-'||FND_PROFILE.VALUE('MTH_EBS_GLOBAL_NAME')||'-'||'1') RESERVATION_PK
, CAST(NULL AS VARCHAR2(150)) WORKORDER_FK
, MOH.ORDER_NUMBER SALESORDER_NUMBER
, MOL.LINE_NUMBER SALESORDER_LNUMBER
, HP.PARTY_NAME CUSTOMER
, MOL.REQUEST_DATE REQUESTED_DATE
, MOL.PROMISE_DATE PROMISED_DATE
, MOL.SCHEDULE_SHIP_DATE SCHEDULED_SHIP_DATE
, FND_PROFILE.VALUE('MTH_EBS_GLOBAL_NAME') SYSTEM_FK
, CAST(NULL AS VARCHAR2(150)) QUANTITY_G
, CAST(NULL AS VARCHAR2(150)) PRIMARY_UOM
, CAST(NULL AS VARCHAR2(150)) PRODUCT_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM1_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM2_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM3_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM4_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM5_FK
, CAST(NULL AS VARCHAR2(150)) USER_ATTR1
, CAST(NULL AS VARCHAR2(150)) USER_ATTR2
, CAST(NULL AS VARCHAR2(150)) USER_ATTR3
, CAST(NULL AS VARCHAR2(150)) USER_ATTR4
, CAST(NULL AS VARCHAR2(150)) USER_ATTR5
, CAST(NULL AS NUMBER) USER_MEASURE1
, CAST(NULL AS NUMBER) USER_MEASURE2
, CAST(NULL AS NUMBER) USER_MEASURE3
, CAST(NULL AS NUMBER) USER_MEASURE4
, CAST(NULL AS NUMBER) USER_MEASURE5
, MRV.SUPPLY_SOURCE_HEADER_ID SUPPLY_SOURCE_HEADER_ID
, MOL.ORDERED_ITEM_ID ORDERED_ITEM_ID
, MOL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, MTP.ORGANIZATION_CODE SOURCE_ORG_CODE
, MOS.PROCESSING_FLAG OP_SO_PROCESSING_FLAG
FROM OE_ORDER_LINES_ALL MOL
, MTH_OPEN_SALES_ORDERS_F MOS
, MTL_RESERVATIONS MRV
, OE_ORDER_HEADERS_ALL MOH
, HZ_PARTIES HP
, HZ_CUST_ACCOUNTS HCA
, MTL_PARAMETERS MTP
WHERE MOS.EBS_HEADER_ID = MOL.HEADER_ID
AND MOL.LINE_ID = MRV.DEMAND_SOURCE_LINE_ID
AND MRV.DEMAND_SOURCE_TYPE_ID = 2
AND MRV.SUPPLY_SOURCE_TYPE_ID IN (5
, 13)
AND MRV.SUPPLY_SOURCE_HEADER_ID IS NOT NULL
AND MOL.HEADER_ID = MOH.HEADER_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND MOH.SOLD_TO_ORG_ID = HCA.CUST_ACCOUNT_ID
AND MTP.ORGANIZATION_ID = MRV.ORGANIZATION_ID
AND MOS.RESERVATION_ID = MRV.RESERVATION_ID UNION SELECT (GSR.BATCH_RES_ID|| '-'||FND_PROFILE.VALUE('MTH_EBS_GLOBAL_NAME')||'-'||'2') RESERVATION_PK
, CAST(NULL AS VARCHAR2(150)) WORKORDER_FK
, MOH.ORDER_NUMBER SALESORDER_NUMBER
, MOL.LINE_NUMBER SALESORDER_LNUMBER
, HP.PARTY_NAME CUSTOMER
, MOL.REQUEST_DATE REQUESTED_DATE
, MOL.PROMISE_DATE PROMISED_DATE
, MOL.SCHEDULE_SHIP_DATE SCHEDULED_SHIP_DATE
, FND_PROFILE.VALUE('MTH_EBS_GLOBAL_NAME') SYSTEM_FK
, CAST(NULL AS VARCHAR2(150)) QUANTITY_G
, CAST(NULL AS VARCHAR2(150)) PRIMARY_UOM
, CAST(NULL AS VARCHAR2(150)) PRODUCT_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM1_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM2_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM3_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM4_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM5_FK
, CAST(NULL AS VARCHAR2(150)) USER_ATTR1
, CAST(NULL AS VARCHAR2(150)) USER_ATTR2
, CAST(NULL AS VARCHAR2(150)) USER_ATTR3
, CAST(NULL AS VARCHAR2(150)) USER_ATTR4
, CAST(NULL AS VARCHAR2(150)) USER_ATTR5
, CAST(NULL AS NUMBER) USER_MEASURE1
, CAST(NULL AS NUMBER) USER_MEASURE2
, CAST(NULL AS NUMBER) USER_MEASURE3
, CAST(NULL AS NUMBER) USER_MEASURE4
, CAST(NULL AS NUMBER) USER_MEASURE5
, GSR.BATCH_ID SUPPLY_SOURCE_HEADER_ID
, ITM.ITEM_ID ORDERED_ITEM_ID
, MOL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, MTP.ORGANIZATION_CODE SOURCE_ORG_CODE
, MOS.PROCESSING_FLAG OP_SO_PROCESSING_FLAG
FROM OE_ORDER_LINES_ALL MOL
, MTL_SYSTEM_ITEMS_B MSI
, IC_ITEM_MST ITM
, MTH_OPEN_SALES_ORDERS_F MOS
, GML_BATCH_SO_RESERVATIONS GSR
, OE_ORDER_HEADERS_ALL MOH
, HZ_PARTIES HP
, HZ_CUST_ACCOUNTS HCA
, MTL_PARAMETERS MTP
WHERE MOL.ORDERED_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.SEGMENT1 = ITM.ITEM_NO
AND MOS.EBS_HEADER_ID = MOL.HEADER_ID
AND MOL.LINE_ID = GSR.SO_LINE_ID
AND GSR.DELETE_MARK = 0
AND MOL.HEADER_ID = MOH.HEADER_ID
AND HCA.PARTY_ID = HP.PARTY_ID
AND MOH.SOLD_TO_ORG_ID = HCA.CUST_ACCOUNT_ID
AND MTP.ORGANIZATION_ID = GSR.ORGANIZATION_ID
AND MOS.RESERVATION_ID = GSR.BATCH_RES_ID UNION SELECT (MOS.RESERVATION_ID|| '-'||FND_PROFILE.VALUE('MTH_EBS_GLOBAL_NAME')||'-'||TO_CHAR(MOS.SOURCE)) RESERVATION_PK
, CAST(NULL AS VARCHAR2(150)) WORKORDER_FK
, MOH.ORDER_NUMBER SALESORDER_NUMBER
, CAST(NULL AS NUMBER) SALESORDER_LNUMBER
, CAST(NULL AS VARCHAR2(150)) CUSTOMER
, CAST(NULL AS DATE) REQUESTED_DATE
, CAST(NULL AS DATE) PROMISED_DATE
, CAST(NULL AS DATE) SCHEDULED_SHIP_DATE
, FND_PROFILE.VALUE('MTH_EBS_GLOBAL_NAME') SYSTEM_FK
, '0' QUANTITY_G
, CAST(NULL AS VARCHAR2(150)) PRIMARY_UOM
, CAST(NULL AS VARCHAR2(150)) PRODUCT_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM1_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM2_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM3_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM4_FK
, CAST(NULL AS VARCHAR2(150)) USER_DIM5_FK
, CAST(NULL AS VARCHAR2(150)) USER_ATTR1
, CAST(NULL AS VARCHAR2(150)) USER_ATTR2
, CAST(NULL AS VARCHAR2(150)) USER_ATTR3
, CAST(NULL AS VARCHAR2(150)) USER_ATTR4
, CAST(NULL AS VARCHAR2(150)) USER_ATTR5
, CAST(NULL AS NUMBER) USER_MEASURE1
, CAST(NULL AS NUMBER) USER_MEASURE2
, CAST(NULL AS NUMBER) USER_MEASURE3
, CAST(NULL AS NUMBER) USER_MEASURE4
, CAST(NULL AS NUMBER) USER_MEASURE5
, MOS.SUPPLY_SOURCE_HEADER_ID SUPPLY_SOURCE_HEADER_ID
, CAST(NULL AS NUMBER) ORDERED_ITEM_ID
, CAST(NULL AS DATE) LAST_UPDATE_DATE
, CAST(NULL AS VARCHAR2(150)) SOURCE_ORG_CODE
, MOS.PROCESSING_FLAG OP_SO_PROCESSING_FLAG
FROM (SELECT F.*
FROM MTH_OPEN_SALES_ORDERS_F F
WHERE NOT EXISTS (SELECT *
FROM (SELECT RESERVATION_ID
FROM MTL_RESERVATIONS UNION ALL SELECT BATCH_RES_ID RESERVATION_ID
FROM GML_BATCH_SO_RESERVATIONS) R
WHERE R.RESERVATION_ID = F.RESERVATION_ID)) MOS
, OE_ORDER_HEADERS_ALL MOH
WHERE MOH.HEADER_ID = MOS.EBS_HEADER_ID)