View: WMS_LPN_CONTENTS_DETAIL_V

Product:	WMS - Warehouse Management
Description:	View of container contents ( includes detail lot and serial controlled items)
Implementation/DBA Data:	APPS.WMS_LPN_CONTENTS_DETAIL_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 */
, 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(+)

Columns