View: MRP_TEMP_QUANTITY_V

Product:	MRP - Master Scheduling/MRP
Description:	On hand information view
Implementation/DBA Data:	APPS.MRP_TEMP_QUANTITY_V

View Text

SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, TEMP.PROJECT_ID
, TEMP.TASK_ID
, SUB.SUB_INVENTORY_CODE
, ITEMS.COMPILE_DESIGNATOR
, ORG.ORGANIZATION_ID
, SUB.NETTING_TYPE
, NVL(SUM(TEMP.PRIMARY_QUANTITY * DECODE(SUB.NETTING_TYPE
, 1
, 1
, 0))
, 0)
, NVL(SUM(TEMP.PRIMARY_QUANTITY * DECODE(SUB.NETTING_TYPE
, 1
, 0
, 1))
, 0)
, NULL
FROM MRP_SUB_INVENTORIES SUB
, MTL_MATERIAL_TRANSACTIONS_TEMP TEMP
, MRP_SYSTEM_ITEMS ITEMS
, MRP_PLAN_ORGANIZATIONS_V ORG
WHERE SUB.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND SUB.COMPILE_DESIGNATOR = ITEMS.COMPILE_DESIGNATOR
AND SUB.SUB_INVENTORY_CODE = TEMP.SUBINVENTORY_CODE
AND TEMP.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND TEMP.POSTING_FLAG = 'Y'
AND TEMP.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = ORG.PLANNED_ORGANIZATION
AND ITEMS.COMPILE_DESIGNATOR = ORG.COMPILE_DESIGNATOR
AND NVL(ITEMS.EFFECTIVITY_CONTROL
, 1) = 1 GROUP BY ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, TEMP.PROJECT_ID
, TEMP.TASK_ID
, SUB.SUB_INVENTORY_CODE
, ITEMS.COMPILE_DESIGNATOR
, ORG.ORGANIZATION_ID
, SUB.NETTING_TYPE UNION ALL SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, TEMP.PROJECT_ID
, TEMP.TASK_ID
, SUB.SUB_INVENTORY_CODE
, ITEMS.COMPILE_DESIGNATOR
, ORG.ORGANIZATION_ID
, SUB.NETTING_TYPE
, NVL(SUM(TEMP.PRIMARY_QUANTITY * DECODE(SUB.NETTING_TYPE
, 1
, 1
, 0))
, 0)
, NVL(SUM(TEMP.PRIMARY_QUANTITY * DECODE(SUB.NETTING_TYPE
, 1
, 0
, 1))
, 0)
, DECODE(TEMP.TRANSACTION_SOURCE_TYPE_ID
, 1
, PJM_UNIT_EFF.RCV_UNIT_NUMBER(TEMP.RCV_TRANSACTION_ID)
, 5
, PJM_UNIT_EFF.WIP_UNIT_NUMBER(TEMP.TRANSACTION_SOURCE_ID
, TEMP.ORGANIZATION_ID))
FROM MRP_SUB_INVENTORIES SUB
, MTL_MATERIAL_TRANSACTIONS_TEMP TEMP
, MRP_SYSTEM_ITEMS ITEMS
, MRP_PLAN_ORGANIZATIONS_V ORG
WHERE SUB.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND SUB.COMPILE_DESIGNATOR = ITEMS.COMPILE_DESIGNATOR
AND SUB.SUB_INVENTORY_CODE = TEMP.SUBINVENTORY_CODE
AND TEMP.POSTING_FLAG = 'Y'
AND TEMP.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = ORG.PLANNED_ORGANIZATION
AND ITEMS.COMPILE_DESIGNATOR = ORG.COMPILE_DESIGNATOR
AND NVL(ITEMS.EFFECTIVITY_CONTROL
, 1) = 2
AND (TEMP.TRANSACTION_ACTION_ID = 1
AND TEMP.TRANSACTION_SOURCE_TYPE_ID IN (1
, 5)) GROUP BY ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, TEMP.PROJECT_ID
, TEMP.TASK_ID
, SUB.SUB_INVENTORY_CODE
, ITEMS.COMPILE_DESIGNATOR
, ORG.ORGANIZATION_ID
, SUB.NETTING_TYPE
, DECODE(TEMP.TRANSACTION_SOURCE_TYPE_ID
, 1
, PJM_UNIT_EFF.RCV_UNIT_NUMBER(TEMP.RCV_TRANSACTION_ID)
, 5
, PJM_UNIT_EFF.WIP_UNIT_NUMBER(TEMP.TRANSACTION_SOURCE_ID
, TEMP.ORGANIZATION_ID)) UNION ALL SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, TEMP.PROJECT_ID
, TEMP.TASK_ID
, SUB.SUB_INVENTORY_CODE
, ITEMS.COMPILE_DESIGNATOR
, ORG.ORGANIZATION_ID
, SUB.NETTING_TYPE
, NVL(SUM(TEMP.PRIMARY_QUANTITY * DECODE(SUB.NETTING_TYPE
, 1
, 1
, 0))
, 0)
, NVL(SUM(TEMP.PRIMARY_QUANTITY * DECODE(SUB.NETTING_TYPE
, 1
, 0
, 1))
, 0)
, MSN.END_ITEM_UNIT_NUMBER
FROM MRP_SUB_INVENTORIES SUB
, MTL_MATERIAL_TRANSACTIONS_TEMP TEMP
, MTL_SERIAL_NUMBERS_TEMP SERTEMP
, MTL_SERIAL_NUMBERS MSN
, MRP_SYSTEM_ITEMS ITEMS
, MRP_PLAN_ORGANIZATIONS_V ORG
WHERE SUB.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND SUB.COMPILE_DESIGNATOR = ITEMS.COMPILE_DESIGNATOR
AND SUB.SUB_INVENTORY_CODE = TEMP.SUBINVENTORY_CODE
AND TEMP.ORGANIZATION_ID = ITEMS.ORGANIZATION_ID
AND TEMP.POSTING_FLAG = 'Y'
AND TEMP.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = ORG.PLANNED_ORGANIZATION
AND ITEMS.COMPILE_DESIGNATOR = ORG.COMPILE_DESIGNATOR
AND NVL(ITEMS.EFFECTIVITY_CONTROL
, 1) = 2
AND SERTEMP.TRANSACTION_TEMP_ID = TEMP.TRANSACTION_TEMP_ID
AND MSN.SERIAL_NUMBER = SERTEMP.FM_SERIAL_NUMBER
AND MSN.INVENTORY_ITEM_ID = TEMP.INVENTORY_ITEM_ID
AND NOT(TEMP.TRANSACTION_ACTION_ID = 1
AND TEMP.TRANSACTION_SOURCE_TYPE_ID IN (1
, 5)) GROUP BY ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, TEMP.PROJECT_ID
, TEMP.TASK_ID
, SUB.SUB_INVENTORY_CODE
, ITEMS.COMPILE_DESIGNATOR
, ORG.ORGANIZATION_ID
, SUB.NETTING_TYPE
, MSN.END_ITEM_UNIT_NUMBER

Columns