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_DETAIL_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 */ ,wln.organization_id ,wln.subinventory_code ,wln.locator_id ,decode(wlc.INVENTORY_ITEM_ID,NULL,3,1) CONTENT_TYPE ,wlc.INVENTORY_ITEM_ID ,msi.CONCATENATED_SEGMENTS INVENTORY_ITEM ,nvl(wlc.ITEM_DESCRIPTION,msi.description) ITEM_DESCRIPTION ,wlc.REVISION ,wlc.LOT_NUMBER ,wlc.SERIAL_NUMBER ,to_char(null) UNIT_NUMBER ,to_number(null) serial_status_id ,wln.status_id lpn_status_id ,wln.lpn_context ,wlc.COST_GROUP_ID ,cg.cost_group cost_group_name ,msi.primary_uom_code ,wlc.UOM_CODE ,wlc.QUANTITY ,wlc.LAST_UPDATE_DATE ,wlc.LAST_UPDATED_BY , wlc.CREATION_DATE , wlc.CREATED_BY , wlc.LAST_UPDATE_LOGIN , wlc.REQUEST_ID , wlc.PROGRAM_APPLICATION_ID , wlc.PROGRAM_ID , wlc.PROGRAM_UPDATE_DATE , wlc.ATTRIBUTE_CATEGORY , wlc.ATTRIBUTE1 , wlc.ATTRIBUTE2 , wlc.ATTRIBUTE3 , wlc.ATTRIBUTE4 , wlc.ATTRIBUTE5 , wlc.ATTRIBUTE6 , wlc.ATTRIBUTE7 , wlc.ATTRIBUTE8 , wlc.ATTRIBUTE9 , wlc.ATTRIBUTE10 , wlc.ATTRIBUTE11 , wlc.ATTRIBUTE12 , wlc.ATTRIBUTE13 , wlc.ATTRIBUTE14 , wlc.ATTRIBUTE15 , wlc.SOURCE_TYPE_ID , wlc.SOURCE_HEADER_ID , wlc.SOURCE_LINE_ID , wlc.SOURCE_LINE_DETAIL_ID , wlc.SOURCE_NAME , wlc.txn_error_flag txn_error_flag from wms_lpn_contents wlc ,wms_license_plate_numbers wln ,wms_license_plate_numbers wln1 ,mtl_system_items_kfv msi ,cst_cost_groups cg WHERE wln.lpn_id = wlc.parent_lpn_id and wln1.lpn_id = wln.outermost_lpn_id and wlc.organization_id = msi.organization_id(+) and wlc.inventory_item_id = msi.inventory_item_id(+) and wlc.cost_group_id = cg.cost_group_id(+) and nvl(wlc.serial_summary_entry, 2) = 2 /* Don't pick up the serial summary entries */ UNION ALL /* select all the contents are the container itself */ /* content type 2 means is 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.subinventory_code ,wln.locator_id ,2 CONTENT_TYPE ,wln.INVENTORY_ITEM_ID ,msi.CONCATENATED_SEGMENTS INVENTORY_ITEM ,msi.description /* container item description */ ,wln.REVISION ,wln.LOT_NUMBER ,wln.SERIAL_NUMBER ,to_char(null) UNIT_NUMBER ,to_number(null) serial_status_id ,wln.status_id lpn_status_id ,wln.lpn_context ,wln. cost_group_id ,cg.cost_group cost_group_name ,msi.primary_uom_code ,msi.PRIMARY_UOM_CODE /* uom code */ ,1 /* quantity */ ,wln.LAST_UPDATE_DATE ,wln.LAST_UPDATED_BY ,wln.CREATION_DATE ,wln.CREATED_BY ,wln.LAST_UPDATE_LOGIN ,wln.REQUEST_ID ,wln.PROGRAM_APPLICATION_ID ,wln.PROGRAM_ID ,wln.PROGRAM_UPDATE_DATE ,wln.ATTRIBUTE_CATEGORY ,wln.ATTRIBUTE1 ,wln.ATTRIBUTE2 ,wln.ATTRIBUTE3 ,wln.ATTRIBUTE4 ,wln.ATTRIBUTE5 ,wln.ATTRIBUTE6 ,wln.ATTRIBUTE7 ,wln.ATTRIBUTE8 ,wln.ATTRIBUTE9 ,wln.ATTRIBUTE10 ,wln.ATTRIBUTE11 ,wln.ATTRIBUTE12 ,wln.ATTRIBUTE13 ,wln.ATTRIBUTE14 ,wln.ATTRIBUTE15 , wln.SOURCE_TYPE_ID , wln.SOURCE_HEADER_ID , wln.SOURCE_LINE_ID , wln.SOURCE_LINE_DETAIL_ID , wln.SOURCE_NAME , '' txn_error_flag from wms_license_plate_numbers wln ,wms_license_plate_numbers wln1 ,wms_license_plate_numbers wln2 ,mtl_system_items_kfv msi ,cst_cost_groups cg where wln.PARENT_LPN_ID = wln1.LPN_ID and wln.outermost_lpn_id = wln2.lpn_id and wln.organization_id = msi.organization_id(+) and wln.inventory_item_id = msi.inventory_item_id(+) and wln.cost_group_id = cg.cost_group_id(+) UNION ALL /* selecting all serialized contents from mtl_serial_numbers instead of wms_lpn_contents */ SELECT wlc.LPN_ID PARENT_LPN_ID ,wln.LICENSE_PLATE_NUMBER /* Parent License Plate Number */ ,to_number(null) /* NULL due to contents are serialized item */ ,to_char(null) /* NULL due to contents are serialized item */ ,wln.outermost_lpn_id ,wln1.LICENSE_PLATE_NUMBER /* Outermost License Plate Number */ ,wln.organization_id ,wln.subinventory_code ,wln.locator_id ,1 CONTENT_TYPE /* always serialized item */ ,wlc.INVENTORY_ITEM_ID ,msi.CONCATENATED_SEGMENTS INVENTORY_ITEM ,msi.description ITEM_DESCRIPTION ,wlc.REVISION ,wlc.LOT_NUMBER ,wlc.SERIAL_NUMBER ,wlc.end_item_unit_number UNIT_NUMBER ,wlc.status_id serial_status_id ,wln.status_id lpn_status_id ,wln.lpn_context ,wlc.COST_GROUP_ID ,cg.cost_group cost_group_name ,msi.primary_uom_code ,msi.primary_uom_code UOM_CODE ,1 QUANTITY ,wlc.LAST_UPDATE_DATE ,wlc.LAST_UPDATED_BY , wlc.CREATION_DATE , wlc.CREATED_BY , wlc.LAST_UPDATE_LOGIN , wlc.REQUEST_ID , wlc.PROGRAM_APPLICATION_ID , wlc.PROGRAM_ID , wlc.PROGRAM_UPDATE_DATE , wlc.ATTRIBUTE_CATEGORY , wlc.ATTRIBUTE1 , wlc.ATTRIBUTE2 , wlc.ATTRIBUTE3 , wlc.ATTRIBUTE4 , wlc.ATTRIBUTE5 , wlc.ATTRIBUTE6 , wlc.ATTRIBUTE7 , wlc.ATTRIBUTE8 , wlc.ATTRIBUTE9 , wlc.ATTRIBUTE10 , wlc.ATTRIBUTE11 , wlc.ATTRIBUTE12 , wlc.ATTRIBUTE13 , wlc.ATTRIBUTE14 , wlc.ATTRIBUTE15 , 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 ,wlc.lpn_txn_error_flag txn_error_flag from mtl_serial_numbers wlc ,wms_license_plate_numbers wln ,wms_license_plate_numbers wln1 ,mtl_system_items_kfv msi ,cst_cost_groups cg where wln.lpn_id = wlc.lpn_id and wln1.lpn_id = wln.outermost_lpn_id and wlc.current_organization_id = msi.organization_id(+) and wlc.inventory_item_id = msi.inventory_item_id(+) and wlc.cost_group_id = cg.cost_group_id(+)

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 */
, WLN.ORGANIZATION_ID
, WLN.SUBINVENTORY_CODE
, WLN.LOCATOR_ID
, DECODE(WLC.INVENTORY_ITEM_ID
, NULL
, 3
, 1) CONTENT_TYPE
, WLC.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS INVENTORY_ITEM
, NVL(WLC.ITEM_DESCRIPTION
, MSI.DESCRIPTION) ITEM_DESCRIPTION
, WLC.REVISION
, WLC.LOT_NUMBER
, WLC.SERIAL_NUMBER
, TO_CHAR(NULL) UNIT_NUMBER
, TO_NUMBER(NULL) SERIAL_STATUS_ID
, WLN.STATUS_ID LPN_STATUS_ID
, WLN.LPN_CONTEXT
, WLC.COST_GROUP_ID
, CG.COST_GROUP COST_GROUP_NAME
, MSI.PRIMARY_UOM_CODE
, WLC.UOM_CODE
, WLC.QUANTITY
, WLC.LAST_UPDATE_DATE
, WLC.LAST_UPDATED_BY
, WLC.CREATION_DATE
, WLC.CREATED_BY
, WLC.LAST_UPDATE_LOGIN
, WLC.REQUEST_ID
, WLC.PROGRAM_APPLICATION_ID
, WLC.PROGRAM_ID
, WLC.PROGRAM_UPDATE_DATE
, WLC.ATTRIBUTE_CATEGORY
, WLC.ATTRIBUTE1
, WLC.ATTRIBUTE2
, WLC.ATTRIBUTE3
, WLC.ATTRIBUTE4
, WLC.ATTRIBUTE5
, WLC.ATTRIBUTE6
, WLC.ATTRIBUTE7
, WLC.ATTRIBUTE8
, WLC.ATTRIBUTE9
, WLC.ATTRIBUTE10
, WLC.ATTRIBUTE11
, WLC.ATTRIBUTE12
, WLC.ATTRIBUTE13
, WLC.ATTRIBUTE14
, WLC.ATTRIBUTE15
, WLC.SOURCE_TYPE_ID
, WLC.SOURCE_HEADER_ID
, WLC.SOURCE_LINE_ID
, WLC.SOURCE_LINE_DETAIL_ID
, WLC.SOURCE_NAME
, WLC.TXN_ERROR_FLAG TXN_ERROR_FLAG
FROM WMS_LPN_CONTENTS WLC
, WMS_LICENSE_PLATE_NUMBERS WLN
, WMS_LICENSE_PLATE_NUMBERS WLN1
, MTL_SYSTEM_ITEMS_KFV MSI
, CST_COST_GROUPS CG
WHERE WLN.LPN_ID = WLC.PARENT_LPN_ID
AND WLN1.LPN_ID = WLN.OUTERMOST_LPN_ID
AND WLC.ORGANIZATION_ID = MSI.ORGANIZATION_ID(+)
AND WLC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND WLC.COST_GROUP_ID = CG.COST_GROUP_ID(+)
AND NVL(WLC.SERIAL_SUMMARY_ENTRY
, 2) = 2 /* DON'T PICK UP THE SERIAL SUMMARY ENTRIES */ UNION ALL /* SELECT ALL THE CONTENTS ARE THE CONTAINER ITSELF */ /* CONTENT TYPE 2 MEANS IS 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.SUBINVENTORY_CODE
, WLN.LOCATOR_ID
, 2 CONTENT_TYPE
, WLN.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS INVENTORY_ITEM
, MSI.DESCRIPTION /* CONTAINER ITEM DESCRIPTION */
, WLN.REVISION
, WLN.LOT_NUMBER
, WLN.SERIAL_NUMBER
, TO_CHAR(NULL) UNIT_NUMBER
, TO_NUMBER(NULL) SERIAL_STATUS_ID
, WLN.STATUS_ID LPN_STATUS_ID
, WLN.LPN_CONTEXT
, WLN. COST_GROUP_ID
, CG.COST_GROUP COST_GROUP_NAME
, MSI.PRIMARY_UOM_CODE
, MSI.PRIMARY_UOM_CODE /* UOM CODE */
, 1 /* QUANTITY */
, WLN.LAST_UPDATE_DATE
, WLN.LAST_UPDATED_BY
, WLN.CREATION_DATE
, WLN.CREATED_BY
, WLN.LAST_UPDATE_LOGIN
, WLN.REQUEST_ID
, WLN.PROGRAM_APPLICATION_ID
, WLN.PROGRAM_ID
, WLN.PROGRAM_UPDATE_DATE
, WLN.ATTRIBUTE_CATEGORY
, WLN.ATTRIBUTE1
, WLN.ATTRIBUTE2
, WLN.ATTRIBUTE3
, WLN.ATTRIBUTE4
, WLN.ATTRIBUTE5
, WLN.ATTRIBUTE6
, WLN.ATTRIBUTE7
, WLN.ATTRIBUTE8
, WLN.ATTRIBUTE9
, WLN.ATTRIBUTE10
, WLN.ATTRIBUTE11
, WLN.ATTRIBUTE12
, WLN.ATTRIBUTE13
, WLN.ATTRIBUTE14
, WLN.ATTRIBUTE15
, WLN.SOURCE_TYPE_ID
, WLN.SOURCE_HEADER_ID
, WLN.SOURCE_LINE_ID
, WLN.SOURCE_LINE_DETAIL_ID
, WLN.SOURCE_NAME
, '' TXN_ERROR_FLAG
FROM WMS_LICENSE_PLATE_NUMBERS WLN
, WMS_LICENSE_PLATE_NUMBERS WLN1
, WMS_LICENSE_PLATE_NUMBERS WLN2
, MTL_SYSTEM_ITEMS_KFV MSI
, CST_COST_GROUPS CG
WHERE WLN.PARENT_LPN_ID = WLN1.LPN_ID
AND WLN.OUTERMOST_LPN_ID = WLN2.LPN_ID
AND WLN.ORGANIZATION_ID = MSI.ORGANIZATION_ID(+)
AND WLN.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND WLN.COST_GROUP_ID = CG.COST_GROUP_ID(+) UNION ALL /* SELECTING ALL SERIALIZED CONTENTS
FROM MTL_SERIAL_NUMBERS INSTEAD OF WMS_LPN_CONTENTS */ SELECT WLC.LPN_ID PARENT_LPN_ID
, WLN.LICENSE_PLATE_NUMBER /* PARENT LICENSE PLATE NUMBER */
, TO_NUMBER(NULL) /* NULL DUE TO CONTENTS ARE SERIALIZED ITEM */
, TO_CHAR(NULL) /* NULL DUE TO CONTENTS ARE SERIALIZED ITEM */
, WLN.OUTERMOST_LPN_ID
, WLN1.LICENSE_PLATE_NUMBER /* OUTERMOST LICENSE PLATE NUMBER */
, WLN.ORGANIZATION_ID
, WLN.SUBINVENTORY_CODE
, WLN.LOCATOR_ID
, 1 CONTENT_TYPE /* ALWAYS SERIALIZED ITEM */
, WLC.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS INVENTORY_ITEM
, MSI.DESCRIPTION ITEM_DESCRIPTION
, WLC.REVISION
, WLC.LOT_NUMBER
, WLC.SERIAL_NUMBER
, WLC.END_ITEM_UNIT_NUMBER UNIT_NUMBER
, WLC.STATUS_ID SERIAL_STATUS_ID
, WLN.STATUS_ID LPN_STATUS_ID
, WLN.LPN_CONTEXT
, WLC.COST_GROUP_ID
, CG.COST_GROUP COST_GROUP_NAME
, MSI.PRIMARY_UOM_CODE
, MSI.PRIMARY_UOM_CODE UOM_CODE
, 1 QUANTITY
, WLC.LAST_UPDATE_DATE
, WLC.LAST_UPDATED_BY
, WLC.CREATION_DATE
, WLC.CREATED_BY
, WLC.LAST_UPDATE_LOGIN
, WLC.REQUEST_ID
, WLC.PROGRAM_APPLICATION_ID
, WLC.PROGRAM_ID
, WLC.PROGRAM_UPDATE_DATE
, WLC.ATTRIBUTE_CATEGORY
, WLC.ATTRIBUTE1
, WLC.ATTRIBUTE2
, WLC.ATTRIBUTE3
, WLC.ATTRIBUTE4
, WLC.ATTRIBUTE5
, WLC.ATTRIBUTE6
, WLC.ATTRIBUTE7
, WLC.ATTRIBUTE8
, WLC.ATTRIBUTE9
, WLC.ATTRIBUTE10
, WLC.ATTRIBUTE11
, WLC.ATTRIBUTE12
, WLC.ATTRIBUTE13
, WLC.ATTRIBUTE14
, WLC.ATTRIBUTE15
, 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
, WLC.LPN_TXN_ERROR_FLAG TXN_ERROR_FLAG
FROM MTL_SERIAL_NUMBERS WLC
, WMS_LICENSE_PLATE_NUMBERS WLN
, WMS_LICENSE_PLATE_NUMBERS WLN1
, MTL_SYSTEM_ITEMS_KFV MSI
, CST_COST_GROUPS CG
WHERE WLN.LPN_ID = WLC.LPN_ID
AND WLN1.LPN_ID = WLN.OUTERMOST_LPN_ID
AND WLC.CURRENT_ORGANIZATION_ID = MSI.ORGANIZATION_ID(+)
AND WLC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND WLC.COST_GROUP_ID = CG.COST_GROUP_ID(+)