View: CSP_PACKLIST_SERIAL_LOTS_V

Product:	CSP - Spares Management
Description:	Serials or lots available for receipt
Implementation/DBA Data:	APPS.CSP_PACKLIST_SERIAL_LOTS_V

View Text

SELECT CPL.PACKLIST_LINE_ID
, CPSL.QUANTITY
, CPSL.SERIAL_NUMBER
, NULL
FROM CSP_PACKLIST_LINES CPL
, CSP_PICKLIST_LINES CPPL
, CSP_PACKLIST_SERIAL_LOTS CPSL
, MTL_SERIAL_NUMBERS_TEMP MSNT
WHERE CPPL.PICKLIST_LINE_ID = CPL.PICKLIST_LINE_ID
AND CPSL.PACKLIST_LINE_ID = CPL.PACKLIST_LINE_ID
AND MSNT.TRANSACTION_TEMP_ID = CPPL.TRANSACTION_TEMP_ID
AND CPSL.SERIAL_NUMBER = MSNT.FM_SERIAL_NUMBER UNION SELECT CPL.PACKLIST_LINE_ID
, MTLT.TRANSACTION_QUANTITY
, NULL
, MTLT.LOT_NUMBER
FROM CSP_PACKLIST_LINES CPL
, CSP_PICKLIST_LINES CPPL
, CSP_PACKLIST_SERIAL_LOTS CPSL
, MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, MTL_TRANSACTION_LOTS_TEMP MTLT
WHERE CPPL.PICKLIST_LINE_ID = CPL.PICKLIST_LINE_ID
AND CPSL.PACKLIST_LINE_ID = CPL.PACKLIST_LINE_ID
AND MTLT.TRANSACTION_TEMP_ID = CPPL.TRANSACTION_TEMP_ID
AND MMTT.TRANSACTION_TEMP_ID = CPPL.TRANSACTION_TEMP_ID
AND MTLT.LOT_NUMBER = CPSL.LOT_NUMBER
AND NVL(MMTT.ITEM_SERIAL_CONTROL_CODE
, 1) IN (1
, 6) UNION SELECT CPL.PACKLIST_LINE_ID
, CPSL.QUANTITY
, CPSL.SERIAL_NUMBER
, MTLT.LOT_NUMBER
FROM CSP_PACKLIST_LINES CPL
, CSP_PICKLIST_LINES CPPL
, CSP_PACKLIST_SERIAL_LOTS CPSL
, MTL_TRANSACTION_LOTS_TEMP MTLT
, MTL_SERIAL_NUMBERS_TEMP MSNT
WHERE CPPL.PICKLIST_LINE_ID = CPL.PICKLIST_LINE_ID
AND CPSL.PACKLIST_LINE_ID = CPL.PACKLIST_LINE_ID
AND MTLT.TRANSACTION_TEMP_ID = CPPL.TRANSACTION_TEMP_ID
AND MSNT.TRANSACTION_TEMP_ID = MTLT.SERIAL_TRANSACTION_TEMP_ID
AND MTLT.LOT_NUMBER = CPSL.LOT_NUMBER
AND MSNT.FM_SERIAL_NUMBER = CPSL.SERIAL_NUMBER

Columns