VIEW: APPS.MRP_ORG_QUANTITY_DET_V

Source

View Text - Preformatted

SELECT ITEMS.INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID , MIL.PROJECT_ID , MIL.TASK_ID , SUB.SUB_INVENTORY_CODE , ITEMS.COMPILE_DESIGNATOR , MPO.ORGANIZATION_ID , SUB.NETTING_TYPE , NVL(SUM( DECODE(PARAM.DEFAULT_STATUS_ID ,NULL , (MOQ.PRIMARY_TRANSACTION_QUANTITY * DECODE((SUB.NETTING_TYPE + NVL(MLN.AVAILABILITY_TYPE,1) + NVL(MIL.AVAILABILITY_TYPE,1)), 3, 1, 0)), (MOQ.PRIMARY_TRANSACTION_QUANTITY * DECODE((SUB.NETTING_TYPE + NVL(MMS.AVAILABILITY_TYPE,1)), 2,1, 0)) ) ), 0), NVL(SUM( DECODE(PARAM.DEFAULT_STATUS_ID ,NULL , (MOQ.PRIMARY_TRANSACTION_QUANTITY * DECODE((SUB.NETTING_TYPE + NVL(MLN.AVAILABILITY_TYPE,1) + NVL(MIL.AVAILABILITY_TYPE,1)), 3, 0, 1)), (MOQ.PRIMARY_TRANSACTION_QUANTITY * DECODE((SUB.NETTING_TYPE + NVL(MMS.AVAILABILITY_TYPE,1)), 2,0, 1)) ) ), 0), NULL FROM MTL_PARAMETERS PARAM, MRP_SUB_INVENTORIES SUB, MTL_ITEM_LOCATIONS MIL, MTL_ONHAND_QUANTITIES_DETAIL MOQ, MRP_SYSTEM_ITEMS ITEMS, MRP_PLAN_ORGANIZATIONS_V MPO, MTL_LOT_NUMBERS MLN, MTL_MATERIAL_STATUSES MMS WHERE (MOQ.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+) AND MOQ.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)) AND MLN.LOT_NUMBER(+) = MOQ.LOT_NUMBER AND MLN.ORGANIZATION_ID(+) = MOQ.ORGANIZATION_ID AND MLN.INVENTORY_ITEM_ID(+) = MOQ.INVENTORY_ITEM_ID AND SUB.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID AND SUB.COMPILE_DESIGNATOR = ITEMS.COMPILE_DESIGNATOR AND SUB.SUB_INVENTORY_CODE = MOQ.SUBINVENTORY_CODE AND MOQ.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID AND MOQ.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND ITEMS.ORGANIZATION_ID = MPO.PLANNED_ORGANIZATION AND ITEMS.COMPILE_DESIGNATOR = MPO.COMPILE_DESIGNATOR AND NVL(MLN.EXPIRATION_DATE, TRUNC(SYSDATE)) >= TRUNC(SYSDATE) AND NVL( ITEMS.EFFECTIVITY_CONTROL, 1) = 1 AND PARAM.ORGANIZATION_ID = MOQ.ORGANIZATION_ID AND MOQ.STATUS_ID = MMS.STATUS_ID(+) GROUP BY ITEMS.INVENTORY_ITEM_ID, ITEMS.ORGANIZATION_ID, MIL.PROJECT_ID, MIL.TASK_ID, SUB.SUB_INVENTORY_CODE, ITEMS.COMPILE_DESIGNATOR, MPO.ORGANIZATION_ID, SUB.NETTING_TYPE, NULL UNION ALL SELECT ITEMS.INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID , MIL.PROJECT_ID , MIL.TASK_ID , SUB.SUB_INVENTORY_CODE , ITEMS.COMPILE_DESIGNATOR , MPO.ORGANIZATION_ID , SUB.NETTING_TYPE , NVL(SUM(DECODE((SUB.NETTING_TYPE + NVL(MIL.AVAILABILITY_TYPE, 1) + NVL(MLN.AVAILABILITY_TYPE, 1)),3, 1, 0)), 0), NVL(SUM(DECODE((SUB.NETTING_TYPE + NVL(MIL.AVAILABILITY_TYPE, 1) + NVL(MLN.AVAILABILITY_TYPE, 1)),3, 0, 1)), 0), MSN.END_ITEM_UNIT_NUMBER FROM MRP_SUB_INVENTORIES SUB , MTL_ITEM_LOCATIONS MIL , MTL_SERIAL_NUMBERS MSN , MRP_SYSTEM_ITEMS ITEMS , MRP_PLAN_ORGANIZATIONS_V MPO, MTL_LOT_NUMBERS MLN WHERE MSN.CURRENT_ORGANIZATION_ID = MIL.ORGANIZATION_ID(+) AND MSN.CURRENT_LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+) AND MSN.LOT_NUMBER = MLN.LOT_NUMBER(+) AND MSN.CURRENT_ORGANIZATION_ID = MLN.ORGANIZATION_ID(+) AND MSN.INVENTORY_ITEM_ID = MLN.INVENTORY_ITEM_ID(+) AND NVL(MLN.EXPIRATION_DATE, TRUNC(SYSDATE)) >= TRUNC(SYSDATE) AND MSN.CURRENT_STATUS IN (3,5) AND SUB.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID AND SUB.COMPILE_DESIGNATOR = ITEMS.COMPILE_DESIGNATOR AND SUB.SUB_INVENTORY_CODE = MSN.CURRENT_SUBINVENTORY_CODE AND MSN.CURRENT_STATUS IN (3, 5) AND MSN.CURRENT_ORGANIZATION_ID = ITEMS.ORGANIZATION_ID AND MSN.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND ITEMS.ORGANIZATION_ID = MPO.PLANNED_ORGANIZATION AND ITEMS.COMPILE_DESIGNATOR = MPO.COMPILE_DESIGNATOR AND NVL( ITEMS.EFFECTIVITY_CONTROL, 1) = 2 GROUP BY ITEMS.INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID , MIL.PROJECT_ID , MIL.TASK_ID , SUB.SUB_INVENTORY_CODE , ITEMS.COMPILE_DESIGNATOR , MPO.ORGANIZATION_ID , SUB.NETTING_TYPE , MSN.END_ITEM_UNIT_NUMBER

View Text - HTML Formatted

SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, MIL.PROJECT_ID
, MIL.TASK_ID
, SUB.SUB_INVENTORY_CODE
, ITEMS.COMPILE_DESIGNATOR
, MPO.ORGANIZATION_ID
, SUB.NETTING_TYPE
, NVL(SUM( DECODE(PARAM.DEFAULT_STATUS_ID
, NULL
, (MOQ.PRIMARY_TRANSACTION_QUANTITY * DECODE((SUB.NETTING_TYPE + NVL(MLN.AVAILABILITY_TYPE
, 1) + NVL(MIL.AVAILABILITY_TYPE
, 1))
, 3
, 1
, 0))
, (MOQ.PRIMARY_TRANSACTION_QUANTITY * DECODE((SUB.NETTING_TYPE + NVL(MMS.AVAILABILITY_TYPE
, 1))
, 2
, 1
, 0)) ) )
, 0)
, NVL(SUM( DECODE(PARAM.DEFAULT_STATUS_ID
, NULL
, (MOQ.PRIMARY_TRANSACTION_QUANTITY * DECODE((SUB.NETTING_TYPE + NVL(MLN.AVAILABILITY_TYPE
, 1) + NVL(MIL.AVAILABILITY_TYPE
, 1))
, 3
, 0
, 1))
, (MOQ.PRIMARY_TRANSACTION_QUANTITY * DECODE((SUB.NETTING_TYPE + NVL(MMS.AVAILABILITY_TYPE
, 1))
, 2
, 0
, 1)) ) )
, 0)
, NULL
FROM MTL_PARAMETERS PARAM
, MRP_SUB_INVENTORIES SUB
, MTL_ITEM_LOCATIONS MIL
, MTL_ONHAND_QUANTITIES_DETAIL MOQ
, MRP_SYSTEM_ITEMS ITEMS
, MRP_PLAN_ORGANIZATIONS_V MPO
, MTL_LOT_NUMBERS MLN
, MTL_MATERIAL_STATUSES MMS
WHERE (MOQ.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MOQ.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+))
AND MLN.LOT_NUMBER(+) = MOQ.LOT_NUMBER
AND MLN.ORGANIZATION_ID(+) = MOQ.ORGANIZATION_ID
AND MLN.INVENTORY_ITEM_ID(+) = MOQ.INVENTORY_ITEM_ID
AND SUB.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND SUB.COMPILE_DESIGNATOR = ITEMS.COMPILE_DESIGNATOR
AND SUB.SUB_INVENTORY_CODE = MOQ.SUBINVENTORY_CODE
AND MOQ.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND MOQ.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = MPO.PLANNED_ORGANIZATION
AND ITEMS.COMPILE_DESIGNATOR = MPO.COMPILE_DESIGNATOR
AND NVL(MLN.EXPIRATION_DATE
, TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND NVL( ITEMS.EFFECTIVITY_CONTROL
, 1) = 1
AND PARAM.ORGANIZATION_ID = MOQ.ORGANIZATION_ID
AND MOQ.STATUS_ID = MMS.STATUS_ID(+) GROUP BY ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, MIL.PROJECT_ID
, MIL.TASK_ID
, SUB.SUB_INVENTORY_CODE
, ITEMS.COMPILE_DESIGNATOR
, MPO.ORGANIZATION_ID
, SUB.NETTING_TYPE
, NULL UNION ALL SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, MIL.PROJECT_ID
, MIL.TASK_ID
, SUB.SUB_INVENTORY_CODE
, ITEMS.COMPILE_DESIGNATOR
, MPO.ORGANIZATION_ID
, SUB.NETTING_TYPE
, NVL(SUM(DECODE((SUB.NETTING_TYPE + NVL(MIL.AVAILABILITY_TYPE
, 1) + NVL(MLN.AVAILABILITY_TYPE
, 1))
, 3
, 1
, 0))
, 0)
, NVL(SUM(DECODE((SUB.NETTING_TYPE + NVL(MIL.AVAILABILITY_TYPE
, 1) + NVL(MLN.AVAILABILITY_TYPE
, 1))
, 3
, 0
, 1))
, 0)
, MSN.END_ITEM_UNIT_NUMBER
FROM MRP_SUB_INVENTORIES SUB
, MTL_ITEM_LOCATIONS MIL
, MTL_SERIAL_NUMBERS MSN
, MRP_SYSTEM_ITEMS ITEMS
, MRP_PLAN_ORGANIZATIONS_V MPO
, MTL_LOT_NUMBERS MLN
WHERE MSN.CURRENT_ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MSN.CURRENT_LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND MSN.LOT_NUMBER = MLN.LOT_NUMBER(+)
AND MSN.CURRENT_ORGANIZATION_ID = MLN.ORGANIZATION_ID(+)
AND MSN.INVENTORY_ITEM_ID = MLN.INVENTORY_ITEM_ID(+)
AND NVL(MLN.EXPIRATION_DATE
, TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND MSN.CURRENT_STATUS IN (3
, 5)
AND SUB.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND SUB.COMPILE_DESIGNATOR = ITEMS.COMPILE_DESIGNATOR
AND SUB.SUB_INVENTORY_CODE = MSN.CURRENT_SUBINVENTORY_CODE
AND MSN.CURRENT_STATUS IN (3
, 5)
AND MSN.CURRENT_ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND MSN.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = MPO.PLANNED_ORGANIZATION
AND ITEMS.COMPILE_DESIGNATOR = MPO.COMPILE_DESIGNATOR
AND NVL( ITEMS.EFFECTIVITY_CONTROL
, 1) = 2 GROUP BY ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, MIL.PROJECT_ID
, MIL.TASK_ID
, SUB.SUB_INVENTORY_CODE
, ITEMS.COMPILE_DESIGNATOR
, MPO.ORGANIZATION_ID
, SUB.NETTING_TYPE
, MSN.END_ITEM_UNIT_NUMBER