View: WIP_JOB_LOT_COMPOSITION_V

Product:	WIP - Work in Process
Description:	Discrete job lot composition
Implementation/DBA Data:	APPS.WIP_JOB_LOT_COMPOSITION_V

View Text

SELECT DISTINCT DECODE( WDJ.WIP_ENTITY_ID
, NULL
, -999
, WDJ.WIP_ENTITY_ID)
, WDJ.START_QUANTITY
, WDJ.QUANTITY_COMPLETED
, WE.WIP_ENTITY_NAME
, MTLN.ORGANIZATION_ID
, MTLN.INVENTORY_ITEM_ID
, MSIK.CONCATENATED_SEGMENTS
, MSIK.DESCRIPTION
, MTLN.LOT_NUMBER
, MLN.EXPIRATION_DATE
, (SUM(MTLN.PRIMARY_QUANTITY) * -1) TOTAL_LOT_QUANTITY
, MTLN.TRANSACTION_SOURCE_TYPE_ID
, MTLN.TRANSACTION_SOURCE_ID
FROM WIP_ENTITIES WE
, WIP_DISCRETE_JOBS WDJ
, MTL_LOT_NUMBERS MLN
, MTL_SYSTEM_ITEMS_KFV MSIK
, MTL_TRANSACTION_LOT_NUMBERS MTLN
WHERE MTLN.INVENTORY_ITEM_ID = MSIK.INVENTORY_ITEM_ID
AND MTLN.ORGANIZATION_ID = MSIK.ORGANIZATION_ID
AND MTLN.INVENTORY_ITEM_ID = MLN.INVENTORY_ITEM_ID
AND MTLN.ORGANIZATION_ID = MLN.ORGANIZATION_ID
AND MTLN.LOT_NUMBER = MLN.LOT_NUMBER
AND WDJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID(+)
AND WDJ.ORGANIZATION_ID = WE.ORGANIZATION_ID(+)
AND MTLN.TRANSACTION_SOURCE_TYPE_ID = 5
AND WDJ.ORGANIZATION_ID (+) = MTLN.ORGANIZATION_ID
AND WDJ.LOT_NUMBER (+) = MTLN.LOT_NUMBER
AND WDJ.PRIMARY_ITEM_ID (+) = MTLN.INVENTORY_ITEM_ID
AND NVL(WDJ.WIP_ENTITY_ID
, -999) != MTLN.TRANSACTION_SOURCE_ID GROUP BY WDJ.WIP_ENTITY_ID
, WDJ.START_QUANTITY
, WDJ.QUANTITY_COMPLETED
, WE.WIP_ENTITY_NAME
, MTLN.INVENTORY_ITEM_ID
, MSIK.CONCATENATED_SEGMENTS
, MSIK.DESCRIPTION
, MTLN.LOT_NUMBER
, MLN.EXPIRATION_DATE
, MTLN.TRANSACTION_SOURCE_ID
, MTLN.TRANSACTION_SOURCE_TYPE_ID
, MTLN.ORGANIZATION_ID

Columns