View: WMS_LPN_CONTENTS_V

Product:	WMS - Warehouse Management
Description:	Summary view of container contents ( includes summary of lot and serialized controlled items )
Implementation/DBA Data:	APPS.WMS_LPN_CONTENTS_V

View Text

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

Columns