View: MRP_ITEM_LOT_QUANTITIES_DET_V

Product:	MRP - Master Scheduling/MRP
Description:	View for expired lots in inventory
Implementation/DBA Data:	APPS.MRP_ITEM_LOT_QUANTITIES_DET_V

View Text

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

Columns