VIEW: APPS.CSP_PACKLIST_SERIAL_LOTS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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