VIEW: APPS.WIP_NET_AVAILABLE_VIEW

Source

View Text - Preformatted

SELECT MSI.ORGANIZATION_ID ORGANIZATION_ID , MSI.INVENTORY_ITEM_ID INVENTORY_ITEM_ID , MIF.ITEM_NUMBER ITEM_NUMBER , MSI.DESCRIPTION DESCRIPTION , EMP.EMPLOYEE_NUM BUYER_EMPLOYEE_NUM , EMP.FULL_NAME BUYER_NAME , MSI.PLANNER_CODE PLANNER_CODE , ML.MEANING PLANNING_MAKE_BUY_CODE , NVL(COUNT(DISTINCT WRO.ROWID),0) NO_OF_OPEN_REQUIREMENTS , NVL(SUM(WRO.REQUIRED_QUANTITY),0)/DECODE(COUNT(DISTINCT MOQD.ROWID), 0,1,COUNT(DISTINCT MOQD.ROWID)) REQUIRED_QUANTITY , NVL(SUM(WRO.QUANTITY_ISSUED),0)/DECODE(COUNT(DISTINCT MOQD.ROWID),0, 1,COUNT(DISTINCT MOQD.ROWID)) QUANTITY_ISSUED , NVL(SUM(WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED),0) /DECODE(COUNT(DISTINCT MOQD.ROWID),0,1,COUNT(DISTINCT MOQD.ROWID)) QUANTITY_OPEN , NVL(SUM(DECODE(MSUB.AVAILABILITY_TYPE,1,MOQD.PRIMARY_TRANSACTION_QUANTITY,0) ),0)/DECODE(COUNT(DISTINCT WRO.ROWID),0,1,COUNT(DISTINCT WRO.ROWID) ) NETTABLE_QUANTITY_ON_HAND , NVL(SUM(DECODE(MSUB.AVAILABILITY_TYPE,2,MOQD.PRIMARY_TRANSACTION_QUANTITY,0) ),0)/DECODE(COUNT(DISTINCT WRO.ROWID),0,1,COUNT(DISTINCT WRO.ROWID) ) NONNETTABLE_QUANTITY_ON_HAND , NVL(SUM(MOQD.PRIMARY_TRANSACTION_QUANTITY),0)/DECODE(COUNT(DISTINCT WRO.ROWID),0,1,COUNT(DISTINCT WRO.ROWID)) TOTAL_QUANTITY_ON_HAND , NVL(SUM(DECODE(MSUB.AVAILABILITY_TYPE,1,MOQD.PRIMARY_TRANSACTION_QUANTITY,0) ),0)/DECODE(COUNT(DISTINCT WRO.ROWID),0,1,COUNT(DISTINCT WRO.ROWID) ) + NVL((SUM(WRO.QUANTITY_ISSUED)-SUM(WRO.REQUIRED_QUANTITY)),0) /DECODE(COUNT(DISTINCT MOQD.ROWID),0,1,COUNT(DISTINCT MOQD.ROWID)) NET_AVAILABLE_QUANTITY FROM MFG_LOOKUPS ML, MTL_ONHAND_QUANTITIES_DETAIL MOQD, MTL_SECONDARY_INVENTORIES MSUB, WIP_REQUIREMENT_OPERATIONS WRO, MTL_EMPLOYEES_VIEW EMP, MTL_ITEM_FLEXFIELDS MIF, MTL_SYSTEM_ITEMS_B MSI WHERE WRO.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND WRO.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND MIF.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND MIF.ITEM_ID = MSI.INVENTORY_ITEM_ID AND ABS(WRO.REQUIRED_QUANTITY) > ABS(WRO.QUANTITY_ISSUED) AND MSI.ORGANIZATION_ID = MOQD.ORGANIZATION_ID(+) AND MSI.INVENTORY_ITEM_ID = MOQD.INVENTORY_ITEM_ID(+) AND ((EXISTS (SELECT 'X' FROM WIP_DISCRETE_JOBS DJ WHERE DJ.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID AND DJ.ORGANIZATION_ID = WRO.ORGANIZATION_ID AND DJ.STATUS_TYPE IN (1,3,4,6))) OR (EXISTS (SELECT 'X' FROM WIP_REPETITIVE_SCHEDULES WRS WHERE WRS.ORGANIZATION_ID = WRO.ORGANIZATION_ID AND WRS.REPETITIVE_SCHEDULE_ID = WRO.REPETITIVE_SCHEDULE_ID AND WRS.STATUS_TYPE IN (1,3,4,6)))) AND EMP.EMPLOYEE_ID(+) = MSI.BUYER_ID AND EMP.ORGANIZATION_ID(+) = MSI.ORGANIZATION_ID AND ML.LOOKUP_CODE(+)= MSI.PLANNING_MAKE_BUY_CODE AND ML.LOOKUP_TYPE(+) = 'MTL_PLANNING_MAKE_BUY' AND MSUB.SECONDARY_INVENTORY_NAME(+) = MOQD.SUBINVENTORY_CODE AND MSUB.ORGANIZATION_ID(+) = MOQD.ORGANIZATION_ID GROUP BY MSI.ORGANIZATION_ID , MSI.INVENTORY_ITEM_ID , MIF.ITEM_NUMBER , MSI.DESCRIPTION , EMP.EMPLOYEE_NUM , EMP.FULL_NAME , MSI.PLANNER_CODE , ML.MEANING

View Text - HTML Formatted

SELECT MSI.ORGANIZATION_ID ORGANIZATION_ID
, MSI.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MIF.ITEM_NUMBER ITEM_NUMBER
, MSI.DESCRIPTION DESCRIPTION
, EMP.EMPLOYEE_NUM BUYER_EMPLOYEE_NUM
, EMP.FULL_NAME BUYER_NAME
, MSI.PLANNER_CODE PLANNER_CODE
, ML.MEANING PLANNING_MAKE_BUY_CODE
, NVL(COUNT(DISTINCT WRO.ROWID)
, 0) NO_OF_OPEN_REQUIREMENTS
, NVL(SUM(WRO.REQUIRED_QUANTITY)
, 0)/DECODE(COUNT(DISTINCT MOQD.ROWID)
, 0
, 1
, COUNT(DISTINCT MOQD.ROWID)) REQUIRED_QUANTITY
, NVL(SUM(WRO.QUANTITY_ISSUED)
, 0)/DECODE(COUNT(DISTINCT MOQD.ROWID)
, 0
, 1
, COUNT(DISTINCT MOQD.ROWID)) QUANTITY_ISSUED
, NVL(SUM(WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED)
, 0) /DECODE(COUNT(DISTINCT MOQD.ROWID)
, 0
, 1
, COUNT(DISTINCT MOQD.ROWID)) QUANTITY_OPEN
, NVL(SUM(DECODE(MSUB.AVAILABILITY_TYPE
, 1
, MOQD.PRIMARY_TRANSACTION_QUANTITY
, 0) )
, 0)/DECODE(COUNT(DISTINCT WRO.ROWID)
, 0
, 1
, COUNT(DISTINCT WRO.ROWID) ) NETTABLE_QUANTITY_ON_HAND
, NVL(SUM(DECODE(MSUB.AVAILABILITY_TYPE
, 2
, MOQD.PRIMARY_TRANSACTION_QUANTITY
, 0) )
, 0)/DECODE(COUNT(DISTINCT WRO.ROWID)
, 0
, 1
, COUNT(DISTINCT WRO.ROWID) ) NONNETTABLE_QUANTITY_ON_HAND
, NVL(SUM(MOQD.PRIMARY_TRANSACTION_QUANTITY)
, 0)/DECODE(COUNT(DISTINCT WRO.ROWID)
, 0
, 1
, COUNT(DISTINCT WRO.ROWID)) TOTAL_QUANTITY_ON_HAND
, NVL(SUM(DECODE(MSUB.AVAILABILITY_TYPE
, 1
, MOQD.PRIMARY_TRANSACTION_QUANTITY
, 0) )
, 0)/DECODE(COUNT(DISTINCT WRO.ROWID)
, 0
, 1
, COUNT(DISTINCT WRO.ROWID) ) + NVL((SUM(WRO.QUANTITY_ISSUED)-SUM(WRO.REQUIRED_QUANTITY))
, 0) /DECODE(COUNT(DISTINCT MOQD.ROWID)
, 0
, 1
, COUNT(DISTINCT MOQD.ROWID)) NET_AVAILABLE_QUANTITY
FROM MFG_LOOKUPS ML
, MTL_ONHAND_QUANTITIES_DETAIL MOQD
, MTL_SECONDARY_INVENTORIES MSUB
, WIP_REQUIREMENT_OPERATIONS WRO
, MTL_EMPLOYEES_VIEW EMP
, MTL_ITEM_FLEXFIELDS MIF
, MTL_SYSTEM_ITEMS_B MSI
WHERE WRO.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND WRO.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIF.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIF.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND ABS(WRO.REQUIRED_QUANTITY) > ABS(WRO.QUANTITY_ISSUED)
AND MSI.ORGANIZATION_ID = MOQD.ORGANIZATION_ID(+)
AND MSI.INVENTORY_ITEM_ID = MOQD.INVENTORY_ITEM_ID(+)
AND ((EXISTS (SELECT 'X'
FROM WIP_DISCRETE_JOBS DJ
WHERE DJ.WIP_ENTITY_ID = WRO.WIP_ENTITY_ID
AND DJ.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND DJ.STATUS_TYPE IN (1
, 3
, 4
, 6))) OR (EXISTS (SELECT 'X'
FROM WIP_REPETITIVE_SCHEDULES WRS
WHERE WRS.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND WRS.REPETITIVE_SCHEDULE_ID = WRO.REPETITIVE_SCHEDULE_ID
AND WRS.STATUS_TYPE IN (1
, 3
, 4
, 6))))
AND EMP.EMPLOYEE_ID(+) = MSI.BUYER_ID
AND EMP.ORGANIZATION_ID(+) = MSI.ORGANIZATION_ID
AND ML.LOOKUP_CODE(+)= MSI.PLANNING_MAKE_BUY_CODE
AND ML.LOOKUP_TYPE(+) = 'MTL_PLANNING_MAKE_BUY'
AND MSUB.SECONDARY_INVENTORY_NAME(+) = MOQD.SUBINVENTORY_CODE
AND MSUB.ORGANIZATION_ID(+) = MOQD.ORGANIZATION_ID GROUP BY MSI.ORGANIZATION_ID
, MSI.INVENTORY_ITEM_ID
, MIF.ITEM_NUMBER
, MSI.DESCRIPTION
, EMP.EMPLOYEE_NUM
, EMP.FULL_NAME
, MSI.PLANNER_CODE
, ML.MEANING