VIEW: APPS.MRP_WEB_PLANNED_SUPPLY_V

Source

View Text - Preformatted

SELECT REC.TRANSACTION_ID , MSI.INVENTORY_ITEM_ID , MSI.ORGANIZATION_ID , MSI.ORGANIZATION_CODE , PLANS.ORGANIZATION_ID , PLANS.COMPILE_DESIGNATOR , REC.NEW_SCHEDULE_DATE , DECODE(REC.ORDER_TYPE, 1, IPO.PO_NUMBER, NULL) , REC.DISPOSITION_STATUS_TYPE , REC.ORDER_TYPE , L1.MEANING , REC.VENDOR_ID , REC.NEW_ORDER_QUANTITY , REC.NEW_ORDER_PLACEMENT_DATE , REC.NEW_DOCK_DATE , FLEX.ITEM_NUMBER , REC.FIRM_PLANNED_TYPE , L3.MEANING , L2.MEANING , REC.SOURCE_VENDOR_ID , VEN.VENDOR_NAME , REC.SOURCE_VENDOR_SITE_ID , SITE.VENDOR_SITE_CODE , NVL(REC.PROJECT_ID, -1) , NVL(REC.TASK_ID, -1) , MRP_GET_PROJECT.PROJECT(REC.PROJECT_ID) , MRP_GET_PROJECT.TASK(REC.TASK_ID) , NVL(REC.PLANNING_GROUP,'-') , 2 , 1 , REC.PLANNING_GROUP FROM PO_VENDOR_SITES_ALL SITE, PO_VENDORS VEN, MRP_ITEM_PURCHASE_ORDERS IPO, MFG_LOOKUPS L2, MFG_LOOKUPS L3, MFG_LOOKUPS L1, MRP_RECOMMENDATIONS REC, MTL_ITEM_FLEXFIELDS FLEX, MRP_SYSTEM_ITEMS MSI , MRP_PLAN_ORGANIZATIONS MPO, MRP_PLANS PLANS WHERE VEN.VENDOR_ID(+) = REC.SOURCE_VENDOR_ID AND SITE.VENDOR_SITE_ID(+) = REC.SOURCE_VENDOR_SITE_ID AND IPO.TRANSACTION_ID(+) = REC.DISPOSITION_ID AND IPO.COMPILE_DESIGNATOR(+) = REC.COMPILE_DESIGNATOR AND IPO.ORGANIZATION_ID(+) = REC.ORGANIZATION_ID AND IPO.INVENTORY_ITEM_ID(+) = REC.INVENTORY_ITEM_ID AND REC.COMPILE_DESIGNATOR = MSI.COMPILE_DESIGNATOR AND REC.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND FLEX.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND FLEX.ITEM_ID = MSI.INVENTORY_ITEM_ID AND REC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND L3.LOOKUP_TYPE = 'SYS_YES_NO' AND L3.LOOKUP_CODE = REC.FIRM_PLANNED_TYPE AND L2.LOOKUP_TYPE (+) = 'MRP_ACTION_TYPE' AND L2.LOOKUP_CODE (+) = DECODE(REC.DISPOSITION_STATUS_TYPE, 2, 6, DECODE(SIGN(REC.NEW_SCHEDULE_DATE - REC.OLD_SCHEDULE_DATE), 1, 5, -1, 4, 0, NULL)) AND L1.LOOKUP_TYPE = 'MRP_ORDER_TYPE' AND L1.LOOKUP_CODE = REC.ORDER_TYPE AND REC.ORDER_TYPE != 4 AND MSI.ORGANIZATION_ID = NVL(MPO.PLANNED_ORGANIZATION, PLANS.ORGANIZATION_ID) AND MSI.COMPILE_DESIGNATOR = PLANS.COMPILE_DESIGNATOR AND MPO.PLAN_LEVEL (+) = 2 AND MPO.COMPILE_DESIGNATOR (+) = PLANS.COMPILE_DESIGNATOR AND MPO.ORGANIZATION_ID (+) = PLANS.ORGANIZATION_ID AND PLANS.PLAN_COMPLETION_DATE IS NOT NULL

View Text - HTML Formatted

SELECT REC.TRANSACTION_ID
, MSI.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID
, MSI.ORGANIZATION_CODE
, PLANS.ORGANIZATION_ID
, PLANS.COMPILE_DESIGNATOR
, REC.NEW_SCHEDULE_DATE
, DECODE(REC.ORDER_TYPE
, 1
, IPO.PO_NUMBER
, NULL)
, REC.DISPOSITION_STATUS_TYPE
, REC.ORDER_TYPE
, L1.MEANING
, REC.VENDOR_ID
, REC.NEW_ORDER_QUANTITY
, REC.NEW_ORDER_PLACEMENT_DATE
, REC.NEW_DOCK_DATE
, FLEX.ITEM_NUMBER
, REC.FIRM_PLANNED_TYPE
, L3.MEANING
, L2.MEANING
, REC.SOURCE_VENDOR_ID
, VEN.VENDOR_NAME
, REC.SOURCE_VENDOR_SITE_ID
, SITE.VENDOR_SITE_CODE
, NVL(REC.PROJECT_ID
, -1)
, NVL(REC.TASK_ID
, -1)
, MRP_GET_PROJECT.PROJECT(REC.PROJECT_ID)
, MRP_GET_PROJECT.TASK(REC.TASK_ID)
, NVL(REC.PLANNING_GROUP
, '-')
, 2
, 1
, REC.PLANNING_GROUP
FROM PO_VENDOR_SITES_ALL SITE
, PO_VENDORS VEN
, MRP_ITEM_PURCHASE_ORDERS IPO
, MFG_LOOKUPS L2
, MFG_LOOKUPS L3
, MFG_LOOKUPS L1
, MRP_RECOMMENDATIONS REC
, MTL_ITEM_FLEXFIELDS FLEX
, MRP_SYSTEM_ITEMS MSI
, MRP_PLAN_ORGANIZATIONS MPO
, MRP_PLANS PLANS
WHERE VEN.VENDOR_ID(+) = REC.SOURCE_VENDOR_ID
AND SITE.VENDOR_SITE_ID(+) = REC.SOURCE_VENDOR_SITE_ID
AND IPO.TRANSACTION_ID(+) = REC.DISPOSITION_ID
AND IPO.COMPILE_DESIGNATOR(+) = REC.COMPILE_DESIGNATOR
AND IPO.ORGANIZATION_ID(+) = REC.ORGANIZATION_ID
AND IPO.INVENTORY_ITEM_ID(+) = REC.INVENTORY_ITEM_ID
AND REC.COMPILE_DESIGNATOR = MSI.COMPILE_DESIGNATOR
AND REC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND FLEX.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND FLEX.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND REC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND L3.LOOKUP_TYPE = 'SYS_YES_NO'
AND L3.LOOKUP_CODE = REC.FIRM_PLANNED_TYPE
AND L2.LOOKUP_TYPE (+) = 'MRP_ACTION_TYPE'
AND L2.LOOKUP_CODE (+) = DECODE(REC.DISPOSITION_STATUS_TYPE
, 2
, 6
, DECODE(SIGN(REC.NEW_SCHEDULE_DATE - REC.OLD_SCHEDULE_DATE)
, 1
, 5
, -1
, 4
, 0
, NULL))
AND L1.LOOKUP_TYPE = 'MRP_ORDER_TYPE'
AND L1.LOOKUP_CODE = REC.ORDER_TYPE
AND REC.ORDER_TYPE != 4
AND MSI.ORGANIZATION_ID = NVL(MPO.PLANNED_ORGANIZATION
, PLANS.ORGANIZATION_ID)
AND MSI.COMPILE_DESIGNATOR = PLANS.COMPILE_DESIGNATOR
AND MPO.PLAN_LEVEL (+) = 2
AND MPO.COMPILE_DESIGNATOR (+) = PLANS.COMPILE_DESIGNATOR
AND MPO.ORGANIZATION_ID (+) = PLANS.ORGANIZATION_ID
AND PLANS.PLAN_COMPLETION_DATE IS NOT NULL