View: WIP_SUB_LOC_NET_AVAIL_VIEW

Product:	WIP - Work in Process
Description:	Job/schedule material requirement supply subinventory/locator quantities
Implementation/DBA Data:	APPS.WIP_SUB_LOC_NET_AVAIL_VIEW

View Text

SELECT MSI.ORGANIZATION_ID ORGANIZATION_ID
, MSI.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MIF.ITEM_NUMBER ITEM_NUMBER
, MSI.DESCRIPTION DESCRIPTION
, WRO.SUPPLY_SUBINVENTORY SUBINVENTORY_CODE
, MIL.INVENTORY_LOCATION_ID LOCATOR_ID
, 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.QUANTITY_ISSUED + WRO.REQUIRED_QUANTITY)
, 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
, MTL_ITEM_LOCATIONS MIL
, 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 WRO.SUPPLY_SUBINVENTORY IS NOT NULL
AND WRO.ORGANIZATION_ID = MOQD.ORGANIZATION_ID(+)
AND WRO.INVENTORY_ITEM_ID = MOQD.INVENTORY_ITEM_ID(+)
AND WRO.SUPPLY_SUBINVENTORY = MOQD.SUBINVENTORY_CODE(+)
AND NVL(WRO.SUPPLY_LOCATOR_ID
, -1) = NVL(MOQD.LOCATOR_ID(+)
, -1)
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
AND MIL.INVENTORY_LOCATION_ID(+) = WRO.SUPPLY_LOCATOR_ID
AND MIL.ORGANIZATION_ID(+) = WRO.ORGANIZATION_ID GROUP BY MSI.ORGANIZATION_ID
, MSI.INVENTORY_ITEM_ID
, MIF.ITEM_NUMBER
, MSI.DESCRIPTION
, WRO.SUPPLY_SUBINVENTORY
, MIL.INVENTORY_LOCATION_ID
, EMP.EMPLOYEE_NUM
, EMP.FULL_NAME
, MSI.PLANNER_CODE
, ML.MEANING

Columns