VIEW: APPS.MRP_ITEM_LOT_QUANTITIES_DET_V

Source

View Text - Preformatted

SELECT ITEMS.INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID , ORGS.ORGANIZATION_ID , SUM(MOQ.PRIMARY_TRANSACTION_QUANTITY) , MOQ.SUBINVENTORY_CODE , MOQ.REVISION , MOQ.LOCATOR_ID , MOQ.LOT_NUMBER , LOTS.EXPIRATION_DATE , MIL.PROJECT_ID , MIL.TASK_ID , SUB.NETTING_TYPE , ITEMS.COMPILE_DESIGNATOR , NULL FROM MTL_ITEM_LOCATIONS MIL, MTL_LOT_NUMBERS LOTS, MTL_ONHAND_QUANTITIES_DETAIL MOQ, MRP_SUB_INVENTORIES SUB, MRP_SYSTEM_ITEMS ITEMS, MRP_PLAN_ORGANIZATIONS_V ORGS WHERE (MOQ.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+) AND MOQ.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)) AND MOQ.INVENTORY_ITEM_ID = LOTS.INVENTORY_ITEM_ID AND MOQ.ORGANIZATION_ID = LOTS.ORGANIZATION_ID AND MOQ.LOT_NUMBER = LOTS.LOT_NUMBER AND MOQ.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND MOQ.ORGANIZATION_ID = SUB.ORGANIZATION_ID AND MOQ.SUBINVENTORY_CODE = SUB.SUB_INVENTORY_CODE AND NVL( ITEMS.EFFECTiVITY_CONTROL, 1) = 1 AND ITEMS.LOT_CONTROL_CODE = 2 AND ITEMS.ORGANIZATION_ID = ORGS.PLANNED_ORGANIZATION AND ITEMS.COMPILE_DESIGNATOR = ORGS.COMPILE_DESIGNATOR AND SUB.ORGANIZATION_ID = ORGS.PLANNED_ORGANIZATION AND SUB.COMPILE_DESIGNATOR = ORGS.COMPILE_DESIGNATOR GROUP BY ITEMS.INVENTORY_ITEM_ID, ITEMS.ORGANIZATION_ID, MOQ.SUBINVENTORY_CODE, MOQ.REVISION, MOQ.LOCATOR_ID, MOQ.LOT_NUMBER, LOTS.EXPIRATION_DATE, MIL.PROJECT_ID, MIL.TASK_ID, SUB.NETTING_TYPE, ORGS.ORGANIZATION_ID, ITEMS.COMPILE_DESIGNATOR UNION ALL SELECT ITEMS.INVENTORY_ITEM_ID, ITEMS.ORGANIZATION_ID, ORGS.ORGANIZATION_ID, NVL(SUM(DECODE(SUB.NETTING_TYPE, 1, 1, 0)), 0), MSN.CURRENT_SUBINVENTORY_CODE, MSN.REVISION, MSN.CURRENT_LOCATOR_ID, MSN.LOT_NUMBER, LOTS.EXPIRATION_DATE, MIL.PROJECT_ID, MIL.TASK_ID, SUB.NETTING_TYPE, ITEMS.COMPILE_DESIGNATOR, MSN.END_ITEM_UNIT_NUMBER FROM MTL_ITEM_LOCATIONS MIL, MTL_LOT_NUMBERS LOTS, MTL_SERIAL_NUMBERS MSN, MRP_SUB_INVENTORIES SUB, MRP_SYSTEM_ITEMS ITEMS, MRP_PLAN_ORGANIZATIONS_V ORGS WHERE MSN.CURRENT_ORGANIZATION_ID = MIL.ORGANIZATION_ID(+) AND MSN.CURRENT_LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+) AND MSN.CURRENT_STATUS in (3,5) AND MSN.INVENTORY_ITEM_ID = LOTS.INVENTORY_ITEM_ID AND MSN.CURRENT_ORGANIZATION_ID = LOTS.ORGANIZATION_ID AND MSN.LOT_NUMBER = LOTS.LOT_NUMBER AND MSN.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID AND MSN.CURRENT_ORGANIZATION_ID = SUB.ORGANIZATION_ID AND MSN.CURRENT_SUBINVENTORY_CODE = SUB.SUB_INVENTORY_CODE AND NVL( ITEMS.EFFECTIVITY_CONTROL, 1) = 2 AND ITEMS.LOT_CONTROL_CODE = 2 AND ITEMS.ORGANIZATION_ID = ORGS.PLANNED_ORGANIZATION AND ITEMS.COMPILE_DESIGNATOR = ORGS.COMPILE_DESIGNATOR AND SUB.ORGANIZATION_ID = ORGS.PLANNED_ORGANIZATION AND SUB.COMPILE_DESIGNATOR = ORGS.COMPILE_DESIGNATOR GROUP BY ITEMS.INVENTORY_ITEM_ID , ITEMS.ORGANIZATION_ID , MSN.CURRENT_SUBINVENTORY_CODE , MSN.REVISION , MSN.CURRENT_LOCATOR_ID , MSN.LOT_NUMBER , LOTS.EXPIRATION_DATE , MIL.PROJECT_ID , MIL.TASK_ID , ORGS.ORGANIZATION_ID , SUB.NETTING_TYPE , ITEMS.COMPILE_DESIGNATOR , MSN.END_ITEM_UNIT_NUMBER

View Text - HTML Formatted

SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ORGS.ORGANIZATION_ID
, SUM(MOQ.PRIMARY_TRANSACTION_QUANTITY)
, MOQ.SUBINVENTORY_CODE
, MOQ.REVISION
, MOQ.LOCATOR_ID
, MOQ.LOT_NUMBER
, LOTS.EXPIRATION_DATE
, MIL.PROJECT_ID
, MIL.TASK_ID
, SUB.NETTING_TYPE
, ITEMS.COMPILE_DESIGNATOR
, NULL
FROM MTL_ITEM_LOCATIONS MIL
, MTL_LOT_NUMBERS LOTS
, MTL_ONHAND_QUANTITIES_DETAIL MOQ
, MRP_SUB_INVENTORIES SUB
, MRP_SYSTEM_ITEMS ITEMS
, MRP_PLAN_ORGANIZATIONS_V ORGS
WHERE (MOQ.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MOQ.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+))
AND MOQ.INVENTORY_ITEM_ID = LOTS.INVENTORY_ITEM_ID
AND MOQ.ORGANIZATION_ID = LOTS.ORGANIZATION_ID
AND MOQ.LOT_NUMBER = LOTS.LOT_NUMBER
AND MOQ.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND MOQ.ORGANIZATION_ID = SUB.ORGANIZATION_ID
AND MOQ.SUBINVENTORY_CODE = SUB.SUB_INVENTORY_CODE
AND NVL( ITEMS.EFFECTIVITY_CONTROL
, 1) = 1
AND ITEMS.LOT_CONTROL_CODE = 2
AND ITEMS.ORGANIZATION_ID = ORGS.PLANNED_ORGANIZATION
AND ITEMS.COMPILE_DESIGNATOR = ORGS.COMPILE_DESIGNATOR
AND SUB.ORGANIZATION_ID = ORGS.PLANNED_ORGANIZATION
AND SUB.COMPILE_DESIGNATOR = ORGS.COMPILE_DESIGNATOR GROUP BY ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, MOQ.SUBINVENTORY_CODE
, MOQ.REVISION
, MOQ.LOCATOR_ID
, MOQ.LOT_NUMBER
, LOTS.EXPIRATION_DATE
, MIL.PROJECT_ID
, MIL.TASK_ID
, SUB.NETTING_TYPE
, ORGS.ORGANIZATION_ID
, ITEMS.COMPILE_DESIGNATOR UNION ALL SELECT ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, ORGS.ORGANIZATION_ID
, NVL(SUM(DECODE(SUB.NETTING_TYPE
, 1
, 1
, 0))
, 0)
, MSN.CURRENT_SUBINVENTORY_CODE
, MSN.REVISION
, MSN.CURRENT_LOCATOR_ID
, MSN.LOT_NUMBER
, LOTS.EXPIRATION_DATE
, MIL.PROJECT_ID
, MIL.TASK_ID
, SUB.NETTING_TYPE
, ITEMS.COMPILE_DESIGNATOR
, MSN.END_ITEM_UNIT_NUMBER
FROM MTL_ITEM_LOCATIONS MIL
, MTL_LOT_NUMBERS LOTS
, MTL_SERIAL_NUMBERS MSN
, MRP_SUB_INVENTORIES SUB
, MRP_SYSTEM_ITEMS ITEMS
, MRP_PLAN_ORGANIZATIONS_V ORGS
WHERE MSN.CURRENT_ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MSN.CURRENT_LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND MSN.CURRENT_STATUS IN (3
, 5)
AND MSN.INVENTORY_ITEM_ID = LOTS.INVENTORY_ITEM_ID
AND MSN.CURRENT_ORGANIZATION_ID = LOTS.ORGANIZATION_ID
AND MSN.LOT_NUMBER = LOTS.LOT_NUMBER
AND MSN.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID
AND MSN.CURRENT_ORGANIZATION_ID = SUB.ORGANIZATION_ID
AND MSN.CURRENT_SUBINVENTORY_CODE = SUB.SUB_INVENTORY_CODE
AND NVL( ITEMS.EFFECTIVITY_CONTROL
, 1) = 2
AND ITEMS.LOT_CONTROL_CODE = 2
AND ITEMS.ORGANIZATION_ID = ORGS.PLANNED_ORGANIZATION
AND ITEMS.COMPILE_DESIGNATOR = ORGS.COMPILE_DESIGNATOR
AND SUB.ORGANIZATION_ID = ORGS.PLANNED_ORGANIZATION
AND SUB.COMPILE_DESIGNATOR = ORGS.COMPILE_DESIGNATOR GROUP BY ITEMS.INVENTORY_ITEM_ID
, ITEMS.ORGANIZATION_ID
, MSN.CURRENT_SUBINVENTORY_CODE
, MSN.REVISION
, MSN.CURRENT_LOCATOR_ID
, MSN.LOT_NUMBER
, LOTS.EXPIRATION_DATE
, MIL.PROJECT_ID
, MIL.TASK_ID
, ORGS.ORGANIZATION_ID
, SUB.NETTING_TYPE
, ITEMS.COMPILE_DESIGNATOR
, MSN.END_ITEM_UNIT_NUMBER