eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.WIP_CUR_SUB_LOC_NET_AVAIL_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 , 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 TRUNC(WRO.DATE_REQUIRED) <= TRUNC(SYSDATE) 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

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
, 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 TRUNC(WRO.DATE_REQUIRED) <= TRUNC(SYSDATE)
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