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_LPN_CONTENTS_V

Source

View Text - Preformatted


SELECT wlc.PARENT_LPN_ID ,wln.LICENSE_PLATE_NUMBER /* Parent License Plate Number */ ,to_number(null) /* LPN_ID is null from wms_lpn_contents */ ,to_char(null) /* LICENSE_PLATE_NUMBER is null since lpn_id is null */ , wln.OUTERMOST_LPN_ID ,wln1.LICENSE_PLATE_NUMBER /*Outermost License Plate Number */ ,wlc.ORGANIZATION_ID ,wlc.INVENTORY_ITEM_ID ,decode(wlc.INVENTORY_ITEM_ID,NULL,3,1) ,wln.LPN_CONTEXT ,wlc.ITEM_DESCRIPTION ,wlc.REVISION ,wlc.LOT_NUMBER ,wlc.SERIAL_NUMBER ,wlc.UOM_CODE ,wlc.QUANTITY , wlc.SOURCE_TYPE_ID , wlc.SOURCE_HEADER_ID , wlc.SOURCE_LINE_ID , wlc.SOURCE_LINE_DETAIL_ID , wlc.SOURCE_NAME , wln.subinventory_code , wln.locator_id from wms_lpn_contents wlc ,wms_license_plate_numbers wln ,wms_license_plate_numbers wln1 WHERE wln.lpn_id = wlc.parent_lpn_id and wln.outermost_lpn_id = wln1.lpn_id and wlc.serial_number is null and nvl(wlc.serial_summary_entry, 2) = 2 /*Don't pick up the serial summary entries */ and wlc.lot_number is null UNION ALL /*select all container contents -lot controlled from wms_lpn_contents entity */ SELECT wlc.PARENT_LPN_ID ,wln.LICENSE_PLATE_NUMBER /* Parent License Plate Number */ ,to_number(null) LPN_ID /* LPN_ID is null from wms_lpn_contents */ ,to_char(null) /* LICENSE_PLATE_NUMBER is null since lpn_id is null */ , wln.OUTERMOST_LPN_ID ,wln1.LICENSE_PLATE_NUMBER /*Outermost License Plate Number */ ,wlc.ORGANIZATION_ID ,wlc.INVENTORY_ITEM_ID ,decode(wlc.INVENTORY_ITEM_ID,NULL,3,1) ,wln.LPN_CONTEXT ,wlc.ITEM_DESCRIPTION ,wlc.REVISION ,null LOT_NUMBER ,null SERIAL_NUMBER ,wlc.UOM_CODE ,sum(wlc.QUANTITY) QUANTITY , wlc.SOURCE_TYPE_ID , wlc.SOURCE_HEADER_ID , wlc.SOURCE_LINE_ID , wlc.SOURCE_LINE_DETAIL_ID , wlc.SOURCE_NAME , wln.subinventory_code , wln.locator_id from wms_lpn_contents wlc ,wms_license_plate_numbers wln ,wms_license_plate_numbers wln1 where wln.lpn_id = wlc.parent_lpn_id and wln.outermost_lpn_id = wln1.lpn_id and wlc.lot_number is not null and nvl(wlc.serial_summary_entry, 2) = 2 /* Don't pick up the serial summary entries */ group by wlc.PARENT_LPN_ID ,wln.LICENSE_PLATE_NUMBER ,to_number(null) ,to_char(null) , wln.OUTERMOST_LPN_ID ,wln1.LICENSE_PLATE_NUMBER ,wlc.ORGANIZATION_ID ,wlc.INVENTORY_ITEM_ID ,decode(wlc.INVENTORY_ITEM_ID,NULL,3,1) ,wln.LPN_CONTEXT ,wlc.ITEM_DESCRIPTION ,wlc.REVISION ,null ,null ,wlc.UOM_CODE , wlc.SOURCE_TYPE_ID , wlc.SOURCE_HEADER_ID , wlc.SOURCE_LINE_ID , wlc.SOURCE_LINE_DETAIL_ID , wlc.SOURCE_NAME , wln.subinventory_code , wln.locator_id UNION ALL /*select all container contents -serialized from mtl_serial_numbers instead wms_lpn_contents entity */ SELECT wlc.LPN_ID ,wln.LICENSE_PLATE_NUMBER /* Parent License Plate Number */ ,to_number(null) LPN_ID /* LPN_ID is null from wms_lpn_contents */ ,to_char(null) /* LICENSE_PLATE_NUMBER is null since lpn_id is null */ , wln.OUTERMOST_LPN_ID ,wln1.LICENSE_PLATE_NUMBER /*Outermost License Plate Number */ ,wlc.current_ORGANIZATION_ID ,wlc.INVENTORY_ITEM_ID ,decode(wlc.INVENTORY_ITEM_ID,NULL,3,1) ,wln.LPN_CONTEXT ,to_char(null) /* description */ ,wlc.REVISION ,null LOT_NUMBER /* wlc.LOT_NUMBER */ ,null SERIAL_NUMBER /* wlc.SERIAL_NUMBER */ ,to_char(null) /* uom code is null for serialized item */ ,count(*) QUANTITY , wlc.LAST_TXN_SOURCE_TYPE_ID SOURCE_TYPE_ID , wlc.LAST_TXN_SOURCE_ID SOURCE_HEADER_ID , to_number(null) SOURCE_LINE_ID , to_number(null) SOURCE_LINE_DETAIL_ID , wlc.LAST_TXN_SOURCE_NAME SOURCE_NAME , wln.subinventory_code , wln.locator_id from mtl_serial_numbers wlc ,wms_license_plate_numbers wln ,wms_license_plate_numbers wln1 where wln.lpn_id = wlc.lpn_id and wln1.lpn_id = wln.outermost_lpn_id group by wlc.LPN_ID ,wln.LICENSE_PLATE_NUMBER ,to_number(null) ,to_char(null) , wln.OUTERMOST_LPN_ID ,wln1.LICENSE_PLATE_NUMBER ,wlc.CURRENT_ORGANIZATION_ID ,wlc.INVENTORY_ITEM_ID ,decode(wlc.INVENTORY_ITEM_ID,NULL,3,1) ,wln.LPN_CONTEXT ,to_char(null) ,wlc.REVISION ,null ,null ,to_char(null) , wlc.LAST_TXN_SOURCE_TYPE_ID , wlc.LAST_TXN_SOURCE_ID , to_number(null) , to_number(null) , wlc.LAST_TXN_SOURCE_NAME , wln.subinventory_code , wln.locator_id UNION ALL /* All container content items where the content itself is a container */ SELECT wln.PARENT_LPN_ID ,wln1.LICENSE_PLATE_NUMBER /* Parent License Plate Number */ ,wln.LPN_ID ,wln.LICENSE_PLATE_NUMBER /* Child License Plate Number */ , wln.OUTERMOST_LPN_ID ,wln2.LICENSE_PLATE_NUMBER /*Outermost License Plate Number */ ,wln.ORGANIZATION_ID ,wln.INVENTORY_ITEM_ID ,2 ,wln.LPN_CONTEXT ,to_char(null) /* description */ ,wln.REVISION ,wln.LOT_NUMBER ,wln.SERIAL_NUMBER ,to_char(null) /* uom code */ ,1 /* quantity */ , wln.SOURCE_TYPE_ID , wln.SOURCE_HEADER_ID , wln.SOURCE_LINE_ID , wln.SOURCE_LINE_DETAIL_ID , wln.SOURCE_NAME , wln.subinventory_code , wln.locator_id from wms_license_plate_numbers wln ,wms_license_plate_numbers wln1 ,wms_license_plate_numbers wln2 where wln.PARENT_LPN_ID = wln1.LPN_ID and wln.outermost_lpn_id = wln2.LPN_ID and wln.PARENT_LPN_ID is not null

View Text - HTML Formatted

SELECT WLC.PARENT_LPN_ID
, WLN.LICENSE_PLATE_NUMBER /* PARENT LICENSE PLATE NUMBER */
, TO_NUMBER(NULL) /* LPN_ID IS NULL
FROM WMS_LPN_CONTENTS */
, TO_CHAR(NULL) /* LICENSE_PLATE_NUMBER IS NULL SINCE LPN_ID IS NULL */
, WLN.OUTERMOST_LPN_ID
, WLN1.LICENSE_PLATE_NUMBER /*OUTERMOST LICENSE PLATE NUMBER */
, WLC.ORGANIZATION_ID
, WLC.INVENTORY_ITEM_ID
, DECODE(WLC.INVENTORY_ITEM_ID
, NULL
, 3
, 1)
, WLN.LPN_CONTEXT
, WLC.ITEM_DESCRIPTION
, WLC.REVISION
, WLC.LOT_NUMBER
, WLC.SERIAL_NUMBER
, WLC.UOM_CODE
, WLC.QUANTITY
, WLC.SOURCE_TYPE_ID
, WLC.SOURCE_HEADER_ID
, WLC.SOURCE_LINE_ID
, WLC.SOURCE_LINE_DETAIL_ID
, WLC.SOURCE_NAME
, WLN.SUBINVENTORY_CODE
, WLN.LOCATOR_ID
FROM WMS_LPN_CONTENTS WLC
, WMS_LICENSE_PLATE_NUMBERS WLN
, WMS_LICENSE_PLATE_NUMBERS WLN1
WHERE WLN.LPN_ID = WLC.PARENT_LPN_ID
AND WLN.OUTERMOST_LPN_ID = WLN1.LPN_ID
AND WLC.SERIAL_NUMBER IS NULL
AND NVL(WLC.SERIAL_SUMMARY_ENTRY
, 2) = 2 /*DON'T PICK UP THE SERIAL SUMMARY ENTRIES */
AND WLC.LOT_NUMBER IS NULL UNION ALL /*SELECT ALL CONTAINER CONTENTS -LOT CONTROLLED
FROM WMS_LPN_CONTENTS ENTITY */ SELECT WLC.PARENT_LPN_ID
, WLN.LICENSE_PLATE_NUMBER /* PARENT LICENSE PLATE NUMBER */
, TO_NUMBER(NULL) LPN_ID /* LPN_ID IS NULL
FROM WMS_LPN_CONTENTS */
, TO_CHAR(NULL) /* LICENSE_PLATE_NUMBER IS NULL SINCE LPN_ID IS NULL */
, WLN.OUTERMOST_LPN_ID
, WLN1.LICENSE_PLATE_NUMBER /*OUTERMOST LICENSE PLATE NUMBER */
, WLC.ORGANIZATION_ID
, WLC.INVENTORY_ITEM_ID
, DECODE(WLC.INVENTORY_ITEM_ID
, NULL
, 3
, 1)
, WLN.LPN_CONTEXT
, WLC.ITEM_DESCRIPTION
, WLC.REVISION
, NULL LOT_NUMBER
, NULL SERIAL_NUMBER
, WLC.UOM_CODE
, SUM(WLC.QUANTITY) QUANTITY
, WLC.SOURCE_TYPE_ID
, WLC.SOURCE_HEADER_ID
, WLC.SOURCE_LINE_ID
, WLC.SOURCE_LINE_DETAIL_ID
, WLC.SOURCE_NAME
, WLN.SUBINVENTORY_CODE
, WLN.LOCATOR_ID
FROM WMS_LPN_CONTENTS WLC
, WMS_LICENSE_PLATE_NUMBERS WLN
, WMS_LICENSE_PLATE_NUMBERS WLN1
WHERE WLN.LPN_ID = WLC.PARENT_LPN_ID
AND WLN.OUTERMOST_LPN_ID = WLN1.LPN_ID
AND WLC.LOT_NUMBER IS NOT NULL
AND NVL(WLC.SERIAL_SUMMARY_ENTRY
, 2) = 2 /* DON'T PICK UP THE SERIAL SUMMARY ENTRIES */ GROUP BY WLC.PARENT_LPN_ID
, WLN.LICENSE_PLATE_NUMBER
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, WLN.OUTERMOST_LPN_ID
, WLN1.LICENSE_PLATE_NUMBER
, WLC.ORGANIZATION_ID
, WLC.INVENTORY_ITEM_ID
, DECODE(WLC.INVENTORY_ITEM_ID
, NULL
, 3
, 1)
, WLN.LPN_CONTEXT
, WLC.ITEM_DESCRIPTION
, WLC.REVISION
, NULL
, NULL
, WLC.UOM_CODE
, WLC.SOURCE_TYPE_ID
, WLC.SOURCE_HEADER_ID
, WLC.SOURCE_LINE_ID
, WLC.SOURCE_LINE_DETAIL_ID
, WLC.SOURCE_NAME
, WLN.SUBINVENTORY_CODE
, WLN.LOCATOR_ID UNION ALL /*SELECT ALL CONTAINER CONTENTS -SERIALIZED
FROM MTL_SERIAL_NUMBERS INSTEAD WMS_LPN_CONTENTS ENTITY */ SELECT WLC.LPN_ID
, WLN.LICENSE_PLATE_NUMBER /* PARENT LICENSE PLATE NUMBER */
, TO_NUMBER(NULL) LPN_ID /* LPN_ID IS NULL
FROM WMS_LPN_CONTENTS */
, TO_CHAR(NULL) /* LICENSE_PLATE_NUMBER IS NULL SINCE LPN_ID IS NULL */
, WLN.OUTERMOST_LPN_ID
, WLN1.LICENSE_PLATE_NUMBER /*OUTERMOST LICENSE PLATE NUMBER */
, WLC.CURRENT_ORGANIZATION_ID
, WLC.INVENTORY_ITEM_ID
, DECODE(WLC.INVENTORY_ITEM_ID
, NULL
, 3
, 1)
, WLN.LPN_CONTEXT
, TO_CHAR(NULL) /* DESCRIPTION */
, WLC.REVISION
, NULL LOT_NUMBER /* WLC.LOT_NUMBER */
, NULL SERIAL_NUMBER /* WLC.SERIAL_NUMBER */
, TO_CHAR(NULL) /* UOM CODE IS NULL FOR SERIALIZED ITEM */
, COUNT(*) QUANTITY
, WLC.LAST_TXN_SOURCE_TYPE_ID SOURCE_TYPE_ID
, WLC.LAST_TXN_SOURCE_ID SOURCE_HEADER_ID
, TO_NUMBER(NULL) SOURCE_LINE_ID
, TO_NUMBER(NULL) SOURCE_LINE_DETAIL_ID
, WLC.LAST_TXN_SOURCE_NAME SOURCE_NAME
, WLN.SUBINVENTORY_CODE
, WLN.LOCATOR_ID
FROM MTL_SERIAL_NUMBERS WLC
, WMS_LICENSE_PLATE_NUMBERS WLN
, WMS_LICENSE_PLATE_NUMBERS WLN1
WHERE WLN.LPN_ID = WLC.LPN_ID
AND WLN1.LPN_ID = WLN.OUTERMOST_LPN_ID GROUP BY WLC.LPN_ID
, WLN.LICENSE_PLATE_NUMBER
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, WLN.OUTERMOST_LPN_ID
, WLN1.LICENSE_PLATE_NUMBER
, WLC.CURRENT_ORGANIZATION_ID
, WLC.INVENTORY_ITEM_ID
, DECODE(WLC.INVENTORY_ITEM_ID
, NULL
, 3
, 1)
, WLN.LPN_CONTEXT
, TO_CHAR(NULL)
, WLC.REVISION
, NULL
, NULL
, TO_CHAR(NULL)
, WLC.LAST_TXN_SOURCE_TYPE_ID
, WLC.LAST_TXN_SOURCE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, WLC.LAST_TXN_SOURCE_NAME
, WLN.SUBINVENTORY_CODE
, WLN.LOCATOR_ID UNION ALL /* ALL CONTAINER CONTENT ITEMS
WHERE THE CONTENT ITSELF IS A CONTAINER */ SELECT WLN.PARENT_LPN_ID
, WLN1.LICENSE_PLATE_NUMBER /* PARENT LICENSE PLATE NUMBER */
, WLN.LPN_ID
, WLN.LICENSE_PLATE_NUMBER /* CHILD LICENSE PLATE NUMBER */
, WLN.OUTERMOST_LPN_ID
, WLN2.LICENSE_PLATE_NUMBER /*OUTERMOST LICENSE PLATE NUMBER */
, WLN.ORGANIZATION_ID
, WLN.INVENTORY_ITEM_ID
, 2
, WLN.LPN_CONTEXT
, TO_CHAR(NULL) /* DESCRIPTION */
, WLN.REVISION
, WLN.LOT_NUMBER
, WLN.SERIAL_NUMBER
, TO_CHAR(NULL) /* UOM CODE */
, 1 /* QUANTITY */
, WLN.SOURCE_TYPE_ID
, WLN.SOURCE_HEADER_ID
, WLN.SOURCE_LINE_ID
, WLN.SOURCE_LINE_DETAIL_ID
, WLN.SOURCE_NAME
, WLN.SUBINVENTORY_CODE
, WLN.LOCATOR_ID
FROM WMS_LICENSE_PLATE_NUMBERS WLN
, WMS_LICENSE_PLATE_NUMBERS WLN1
, WMS_LICENSE_PLATE_NUMBERS WLN2
WHERE WLN.PARENT_LPN_ID = WLN1.LPN_ID
AND WLN.OUTERMOST_LPN_ID = WLN2.LPN_ID
AND WLN.PARENT_LPN_ID IS NOT NULL