VIEW: APPS.WMS_LPN_CONTENTS_LOT_V

Source

View Text - Preformatted

SELECT wlc.PARENT_LPN_ID ,wlp.LICENSE_PLATE_NUMBER PARENT_LICENSE_PLATE_NUMBER ,wlp.outermost_lpn_id ,wlp1.LICENSE_PLATE_NUMBER /* outermost lpn */ ,wlc.ORGANIZATION_ID ,wlc.INVENTORY_ITEM_ID ,msi.PADDED_CONCATENATED_SEGMENTS INVENTORY_ITEM ,msi.DESCRIPTION ITEM_DESCRIPTION ,msi.PRIMARY_UOM_CODE ,wlc.REVISION ,wlc.LOT_NUMBER ,wlp.SUBINVENTORY_CODE ,wlp.LOCATOR_ID ,wlc.COST_GROUP_ID ,SUM(wlc.QUANTITY) QUANTITY ,mln.EXPIRATION_DATE ,msi.LOT_CONTROL_CODE ITEM_LOT_CONTROL ,msi.LOCATION_CONTROL_CODE ITEM_LOCATOR_CONTROL ,msi.SERIAL_NUMBER_CONTROL_CODE ITEM_SERIAL_CONTROL FROM WMS_LPN_CONTENTS wlc ,WMS_LICENSE_PLATE_NUMBERS wlp ,WMS_LICENSE_PLATE_NUMBERS wlp1 ,MTL_SYSTEM_ITEMS_KFV msi ,MTL_LOT_NUMBERS mln WHERE wlc.INVENTORY_ITEM_ID = msi.INVENTORY_ITEM_ID AND wlc.ORGANIZATION_ID = msi.ORGANIZATION_ID AND wlc.LOT_NUMBER = mln.LOT_NUMBER AND wlc.INVENTORY_ITEM_ID = mln.INVENTORY_ITEM_ID AND wlc.ORGANIZATION_ID = mln.ORGANIZATION_ID AND wlc.PARENT_LPN_ID = wlp.LPN_ID AND wlp.outermost_LPN_ID = wlp1.LPN_ID GROUP BY wlc.PARENT_LPN_ID ,wlp.LICENSE_PLATE_NUMBER ,wlp.outermost_lpn_id ,wlp1.LICENSE_PLATE_NUMBER ,wlc.ORGANIZATION_ID ,wlc.INVENTORY_ITEM_ID ,wlc.REVISION ,wlc.LOT_NUMBER ,wlp.SUBINVENTORY_CODE ,wlp.LOCATOR_ID ,wlc.COST_GROUP_ID ,msi.PADDED_CONCATENATED_SEGMENTS ,msi.DESCRIPTION ,msi.PRIMARY_UOM_CODE ,mln.EXPIRATION_DATE ,msi.LOT_CONTROL_CODE ,msi.LOCATION_CONTROL_CODE ,msi.SERIAL_NUMBER_CONTROL_CODE

View Text - HTML Formatted

SELECT WLC.PARENT_LPN_ID
, WLP.LICENSE_PLATE_NUMBER PARENT_LICENSE_PLATE_NUMBER
, WLP.OUTERMOST_LPN_ID
, WLP1.LICENSE_PLATE_NUMBER /* OUTERMOST LPN */
, WLC.ORGANIZATION_ID
, WLC.INVENTORY_ITEM_ID
, MSI.PADDED_CONCATENATED_SEGMENTS INVENTORY_ITEM
, MSI.DESCRIPTION ITEM_DESCRIPTION
, MSI.PRIMARY_UOM_CODE
, WLC.REVISION
, WLC.LOT_NUMBER
, WLP.SUBINVENTORY_CODE
, WLP.LOCATOR_ID
, WLC.COST_GROUP_ID
, SUM(WLC.QUANTITY) QUANTITY
, MLN.EXPIRATION_DATE
, MSI.LOT_CONTROL_CODE ITEM_LOT_CONTROL
, MSI.LOCATION_CONTROL_CODE ITEM_LOCATOR_CONTROL
, MSI.SERIAL_NUMBER_CONTROL_CODE ITEM_SERIAL_CONTROL
FROM WMS_LPN_CONTENTS WLC
, WMS_LICENSE_PLATE_NUMBERS WLP
, WMS_LICENSE_PLATE_NUMBERS WLP1
, MTL_SYSTEM_ITEMS_KFV MSI
, MTL_LOT_NUMBERS MLN
WHERE WLC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND WLC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND WLC.LOT_NUMBER = MLN.LOT_NUMBER
AND WLC.INVENTORY_ITEM_ID = MLN.INVENTORY_ITEM_ID
AND WLC.ORGANIZATION_ID = MLN.ORGANIZATION_ID
AND WLC.PARENT_LPN_ID = WLP.LPN_ID
AND WLP.OUTERMOST_LPN_ID = WLP1.LPN_ID GROUP BY WLC.PARENT_LPN_ID
, WLP.LICENSE_PLATE_NUMBER
, WLP.OUTERMOST_LPN_ID
, WLP1.LICENSE_PLATE_NUMBER
, WLC.ORGANIZATION_ID
, WLC.INVENTORY_ITEM_ID
, WLC.REVISION
, WLC.LOT_NUMBER
, WLP.SUBINVENTORY_CODE
, WLP.LOCATOR_ID
, WLC.COST_GROUP_ID
, MSI.PADDED_CONCATENATED_SEGMENTS
, MSI.DESCRIPTION
, MSI.PRIMARY_UOM_CODE
, MLN.EXPIRATION_DATE
, MSI.LOT_CONTROL_CODE
, MSI.LOCATION_CONTROL_CODE
, MSI.SERIAL_NUMBER_CONTROL_CODE