eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.WMS_PACKING_LPN_CONT_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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