View: WMS_PACKING_LPN_CONT_V

Product:	WMS - Warehouse Management
Description:	View to display To LPN Content in WMS Packing Workbench Form
Implementation/DBA Data:	APPS.WMS_PACKING_LPN_CONT_V

View Text

SELECT 1 SOURCE_TYPE_ID
, WLC.PARENT_LPN_ID PARENT_LPN_ID
, TO_NUMBER (NULL) TRANSACTION_HEADER_ID
, TO_NUMBER (NULL) TRANSACTION_TEMP_ID
, 1 CONTENT_TYPE_ID
, MSIK.CONCATENATED_SEGMENTS CONTENT
, WLC.INVENTORY_ITEM_ID
, TO_NUMBER (NULL) CONTENT_LPN_ID
, WLC.REVISION REVISION
, DECODE (MSN.LPN_ID
, NULL
, WLC.QUANTITY
, 1) QUANTITY
, WLC.UOM_CODE UOM
, WLC.LOT_NUMBER LOT_NUMBER
, MSN.SERIAL_NUMBER SERIAL_NUMBER
, WLC.SECONDARY_QUANTITY
, WLC.SECONDARY_UOM_CODE
FROM WMS_LPN_CONTENTS WLC
, MTL_SYSTEM_ITEMS_KFV MSIK
, MTL_SERIAL_NUMBERS MSN
WHERE WLC.ORGANIZATION_ID = MSIK.ORGANIZATION_ID
AND WLC.INVENTORY_ITEM_ID = MSIK.INVENTORY_ITEM_ID
AND MSN.LPN_ID(+) = WLC.PARENT_LPN_ID
AND MSN.CURRENT_ORGANIZATION_ID(+) = WLC.ORGANIZATION_ID
AND MSN.INVENTORY_ITEM_ID(+) = WLC.INVENTORY_ITEM_ID
AND NVL (MSN.LOT_NUMBER(+)
, '@@@@') = NVL (WLC.LOT_NUMBER
, '@@@@') UNION ALL SELECT 1 SOURCE_TYPE_ID
, LPN.PARENT_LPN_ID PARENT_LPN_ID
, TO_NUMBER (NULL) TRANSACTION_HEADER_ID
, TO_NUMBER (NULL) TRANSACTION_TEMP_ID
, 2 CONTENT_TYPE_ID
, LPN.LICENSE_PLATE_NUMBER CONTENT
, TO_NUMBER (NULL) INVENTORY_ITEM_ID
, LPN.LPN_ID CONTENT_LPN_ID
, TO_CHAR (NULL) REVISION
, TO_NUMBER (NULL) QUANTITY
, TO_CHAR (NULL) UOM
, TO_CHAR (NULL) LOT_NUMBER
, TO_CHAR (NULL) SERIAL_NUMBER
, TO_NUMBER (NULL) SECONDARY_QUANTITY
, TO_CHAR (NULL) SECONDARY_UOM_CODE
FROM WMS_LICENSE_PLATE_NUMBERS LPN
WHERE LPN.PARENT_LPN_ID IS NOT NULL UNION ALL SELECT 2 SOURCE_TYPE_ID
, MMTT.LPN_ID PARENT_LPN_ID
, MMTT.TRANSACTION_HEADER_ID TRANSACTION_HEADER_ID
, MMTT.TRANSACTION_TEMP_ID TRANSACTION_TEMP_ID
, 1 CONTENT_TYPE_ID
, MSIK.CONCATENATED_SEGMENTS CONTENT
, MMTT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, TO_NUMBER (NULL) CONTENT_LPN_ID
, MMTT.REVISION
, DECODE (MMTT.TRANSACTION_ACTION_ID
, 51
, -1* ABS (NVL (MTLT.TRANSACTION_QUANTITY
, MMTT.TRANSACTION_QUANTITY))
, ABS (NVL (MTLT.TRANSACTION_QUANTITY
, MMTT.TRANSACTION_QUANTITY))) QUANTITY
, MMTT.TRANSACTION_UOM UOM
, MTLT.LOT_NUMBER LOT_NUMBER
, TO_CHAR (NULL) SERIAL_NUMBER
, DECODE (MMTT.TRANSACTION_ACTION_ID
, 51
, -1* ABS (NVL (MTLT.SECONDARY_QUANTITY
, MMTT.SECONDARY_TRANSACTION_QUANTITY))
, ABS(NVL (MTLT.SECONDARY_QUANTITY
, MMTT.SECONDARY_TRANSACTION_QUANTITY))) SECONDARY_QUANTITY
, MMTT.SECONDARY_UOM_CODE
FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, MTL_TRANSACTION_LOTS_TEMP MTLT
, MTL_SYSTEM_ITEMS_KFV MSIK
WHERE MTLT.TRANSACTION_TEMP_ID(+) = MMTT.TRANSACTION_TEMP_ID
AND MMTT.ORGANIZATION_ID = MSIK.ORGANIZATION_ID
AND MMTT.INVENTORY_ITEM_ID = MSIK.INVENTORY_ITEM_ID
AND MMTT.CONTENT_LPN_ID IS NULL
AND MSIK.SERIAL_NUMBER_CONTROL_CODE IN (1
, 6) UNION ALL SELECT 2 SOURCE_TYPE_ID
, MMTT.LPN_ID PARENT_LPN_ID
, MMTT.TRANSACTION_HEADER_ID TRANSACTION_HEADER_ID
, MMTT.TRANSACTION_TEMP_ID TRANSACTION_TEMP_ID
, 1 CONTENT_TYPE_ID
, MSIK.CONCATENATED_SEGMENTS CONTENT
, MMTT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, TO_NUMBER (NULL) CONTENT_LPN_ID
, MMTT.REVISION
, DECODE (MMTT.TRANSACTION_ACTION_ID
, 51
, -1
, 1) QUANTITY
, MSIK.PRIMARY_UOM_CODE UOM
, MTLT.LOT_NUMBER LOT_NUMBER
, MSN.SERIAL_NUMBER SERIAL_NUMBER
, DECODE (MMTT.TRANSACTION_ACTION_ID
, 51
, -1
, 1) SECONDARY_QUANTITY
, MSIK.SECONDARY_UOM_CODE
FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, MTL_TRANSACTION_LOTS_TEMP MTLT
, MTL_SERIAL_NUMBERS_TEMP MSNT
, MTL_SYSTEM_ITEMS_KFV MSIK
, MTL_SERIAL_NUMBERS MSN
WHERE MTLT.TRANSACTION_TEMP_ID(+) = MMTT.TRANSACTION_TEMP_ID
AND MMTT.ORGANIZATION_ID = MSIK.ORGANIZATION_ID
AND MMTT.INVENTORY_ITEM_ID = MSIK.INVENTORY_ITEM_ID
AND MMTT.CONTENT_LPN_ID IS NULL
AND MSIK.SERIAL_NUMBER_CONTROL_CODE NOT IN (1
, 6)
AND MSNT.TRANSACTION_TEMP_ID = NVL (MTLT.SERIAL_TRANSACTION_TEMP_ID
, MMTT.TRANSACTION_TEMP_ID)
AND MSN.CURRENT_ORGANIZATION_ID = MMTT.ORGANIZATION_ID
AND MSN.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MSN.SERIAL_NUMBER BETWEEN MSNT.FM_SERIAL_NUMBER
AND MSNT.TO_SERIAL_NUMBER UNION ALL SELECT 2 SOURCE_TYPE_ID
, MMTT.LPN_ID PARENT_LPN_ID
, MMTT.TRANSACTION_HEADER_ID TRANSACTION_HEADER_ID
, MMTT.TRANSACTION_TEMP_ID TRANSACTION_TEMP_ID
, 1 CONTENT_TYPE_ID
, MSIK.CONCATENATED_SEGMENTS CONTENT
, MMTT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, TO_NUMBER (NULL) CONTENT_LPN_ID
, MMTT.REVISION
, DECODE (MMTT.TRANSACTION_ACTION_ID
, 51
, -1
, 1) QUANTITY
, MSIK.PRIMARY_UOM_CODE UOM
, MTLT.LOT_NUMBER LOT_NUMBER
, MSNT.FM_SERIAL_NUMBER SERIAL_NUMBER
, DECODE (MMTT.TRANSACTION_ACTION_ID
, 51
, -1
, 1) SECONDARY_QUANTITY
, MSIK.SECONDARY_UOM_CODE
FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, MTL_TRANSACTION_LOTS_TEMP MTLT
, MTL_SERIAL_NUMBERS_TEMP MSNT
, MTL_SYSTEM_ITEMS_KFV MSIK
WHERE MTLT.TRANSACTION_TEMP_ID(+) = MMTT.TRANSACTION_TEMP_ID
AND MMTT.ORGANIZATION_ID = MSIK.ORGANIZATION_ID
AND MMTT.INVENTORY_ITEM_ID = MSIK.INVENTORY_ITEM_ID
AND MMTT.CONTENT_LPN_ID IS NULL
AND MSIK.SERIAL_NUMBER_CONTROL_CODE NOT IN (1
, 6)
AND MSNT.TRANSACTION_TEMP_ID = NVL (MTLT.SERIAL_TRANSACTION_TEMP_ID
, MMTT.TRANSACTION_TEMP_ID)
AND MSNT.FM_SERIAL_NUMBER = MSNT.TO_SERIAL_NUMBER
AND NOT EXISTS ( SELECT SERIAL_NUMBER
FROM MTL_SERIAL_NUMBERS
WHERE SERIAL_NUMBER = MSNT.FM_SERIAL_NUMBER) UNION ALL SELECT 2 SOURCE_TYPE_ID
, MMTT.LPN_ID PARENT_LPN_ID
, MMTT.TRANSACTION_HEADER_ID TRANSACTION_HEADER_ID
, MMTT.TRANSACTION_TEMP_ID TRANSACTION_TEMP_ID
, 2 CONTENT_TYPE_ID
, LPN.LICENSE_PLATE_NUMBER CONTENT
, TO_NUMBER (NULL) INVENTORY_ITEM_ID
, MMTT.CONTENT_LPN_ID
, TO_CHAR (NULL) REVISION
, DECODE (MMTT.TRANSACTION_ACTION_ID
, 51
, -1
, TO_NUMBER (NULL)) QUANTITY
, TO_CHAR (NULL) UOM
, TO_CHAR (NULL) LOT_NUMBER
, TO_CHAR (NULL)SERIAL_NUMBER
, DECODE (MMTT.TRANSACTION_ACTION_ID
, 51
, -1
, TO_NUMBER (NULL)) SECONDARY_QUANTITY
, TO_CHAR (NULL) SECONDARY_UOM_CODE
FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, WMS_LICENSE_PLATE_NUMBERS LPN
WHERE MMTT.CONTENT_LPN_ID IS NOT NULL
AND MMTT.CONTENT_LPN_ID = LPN.LPN_ID

Columns