View: MTL_ONHAND_NEW_LPN_MWB_V

Product:	INV - Inventory
Description:	LPN content view for Material Workbench
Implementation/DBA Data:	APPS.MTL_ONHAND_NEW_LPN_MWB_V

View Text

SELECT WLN.ORGANIZATION_ID ORGANIZATION_ID
, MP.ORGANIZATION_CODE ORGANIZATION_CODE
, WLN.SUBINVENTORY_CODE SUBINVENTORY_CODE
, WLN.LOCATOR_ID LOCATOR_ID
, MIL.CONCATENATED_SEGMENTS LOCATOR
, WLC.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSIV.DESCRIPTION ITEM_DESCRIPTION
, MSIV.CONCATENATED_SEGMENTS ITEM
, WLC.REVISION REVISION
, MSIV.PRIMARY_UOM_CODE UOM
, WLC.PRIMARY_TRANSACTION_QUANTITY ON_HAND
, 0 UNPACKED
, WLC.PRIMARY_TRANSACTION_QUANTITY PACKED
, WLC.COST_GROUP_ID COST_GROUP_ID
, WLN.LPN_ID LPN_ID
, WLN.LICENSE_PLATE_NUMBER LPN
, WLN.LPN_CONTEXT LPN_CONTEXT
, WLC.LOT_NUMBER LOT_NUMBER
, MLN.EXPIRATION_DATE EXPIRATION_DATE
, TO_CHAR (NULL) SERIAL_NUMBER
, TO_CHAR (NULL) UNIT_NUMBER
, MIL.PROJECT_ID PROJECT_ID
, MIL.TASK_ID TASK_ID
, MIL.STATUS_ID LOCATOR_STATUS_ID
, TO_NUMBER (NULL) SERIAL_STATUS_ID
, WLN.OUTERMOST_LPN_ID OUTERMOST_LPN_ID
, WLN.PARENT_LPN_ID PARENT_LPN_ID
, WLC.PLANNING_TP_TYPE PLANNING_TP_TYPE
, WLC.PLANNING_ORGANIZATION_ID PLANNING_ORGANIZATION_ID
, WLC.OWNING_TP_TYPE OWNING_TP_TYPE
, WLC.OWNING_ORGANIZATION_ID OWNING_ORGANIZATION_ID
, MSIV.LOT_CONTROL_CODE ITEM_LOT_CONTROL
, MSIV.SERIAL_NUMBER_CONTROL_CODE ITEM_SERIAL_CONTROL /* INVCONV START RNRAO */
, WLC.TRANSACTION_QUANTITY SECONDARY_ONHAND
, WLC.SECONDARY_UOM_CODE
, TO_CHAR (NULL) GRADE_CODE
, DECODE (WLC.LPN_ID
, NULL
, WLC.TRANSACTION_QUANTITY
, 0) SECONDARY_UNPACKED
, DECODE (WLC.LPN_ID
, NULL
, 0
, WLC.TRANSACTION_QUANTITY) SECONDARY_PACKED /* INVCONV END RNRAO */
, WLC.STATUS_ID ONHAND_STATUS_ID
FROM MTL_ITEM_LOCATIONS_KFV MIL
, WMS_LICENSE_PLATE_NUMBERS WLN
, MTL_SYSTEM_ITEMS_VL MSIV
, MTL_ONHAND_QUANTITIES_DETAIL WLC
, MTL_PARAMETERS MP
, MTL_LOT_NUMBERS MLN
WHERE MP.ORGANIZATION_ID = WLN.ORGANIZATION_ID
AND WLN.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND WLN.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND WLN.LPN_ID = WLC.LPN_ID
AND WLC.ORGANIZATION_ID = MSIV.ORGANIZATION_ID(+)
AND WLC.INVENTORY_ITEM_ID = MSIV.INVENTORY_ITEM_ID(+)
AND WLC.ORGANIZATION_ID = MLN.ORGANIZATION_ID(+)
AND WLC.INVENTORY_ITEM_ID = MLN.INVENTORY_ITEM_ID(+)
AND WLC.LOT_NUMBER = MLN.LOT_NUMBER(+)
AND MSIV.SERIAL_NUMBER_CONTROL_CODE IN (1
, 6) UNION ALL SELECT WLN.ORGANIZATION_ID ORGANIZATION_ID
, MP.ORGANIZATION_CODE ORGANIZATION_CODE
, WLN.SUBINVENTORY_CODE SUBINVENTORY_CODE
, WLN.LOCATOR_ID LOCATOR_ID
, MIL.CONCATENATED_SEGMENTS LOCATOR
, WLC.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSIV.DESCRIPTION ITEM_DESCRIPTION
, MSIV.CONCATENATED_SEGMENTS ITEM
, WLC.REVISION REVISION
, MSIV.PRIMARY_UOM_CODE UOM
, 1 ON_HAND
, 0 UNPACKED
, 1 PACKED
, WLC.COST_GROUP_ID COST_GROUP_ID
, WLC.LPN_ID LPN_ID
, WLN.LICENSE_PLATE_NUMBER LPN
, WLN.LPN_CONTEXT LPN_CONTEXT
, WLC.LOT_NUMBER LOT_NUMBER
, MLN.EXPIRATION_DATE EXPIRATION_DATE
, WLC.SERIAL_NUMBER SERIAL_NUMBER
, WLC.END_ITEM_UNIT_NUMBER UNIT_NUMBER
, MIL.PROJECT_ID PROJECT_ID
, MIL.TASK_ID TASK_ID
, MIL.STATUS_ID LOCATOR_STATUS_ID
, WLC.STATUS_ID SERIAL_STATUS_ID
, WLN.OUTERMOST_LPN_ID OUTERMOST_LPN_ID
, WLN.PARENT_LPN_ID PARENT_LPN_ID
, WLC.PLANNING_TP_TYPE PLANNING_TP_TYPE
, WLC.PLANNING_ORGANIZATION_ID PLANNING_ORGANIZATION_ID
, WLC.OWNING_TP_TYPE OWNING_TP_TYPE
, WLC.OWNING_ORGANIZATION_ID OWNING_ORGANIZATION_ID
, MSIV.LOT_CONTROL_CODE ITEM_LOT_CONTROL
, MSIV.SERIAL_NUMBER_CONTROL_CODE ITEM_SERIAL_CONTROL /* INVCONV START RNRAO */
, TO_NUMBER (NULL) SECONDARY_ONHAND
, MSIV.SECONDARY_UOM_CODE
, TO_CHAR (NULL) GRADE_CODE
, TO_NUMBER (NULL) SECONDARY_PACKED
, TO_NUMBER (NULL) SECONDARY_UNPACKED /* INVCONV END RNRAO */
, WLC.STATUS_ID ONHAND_STATUS_ID
FROM MTL_ITEM_LOCATIONS_KFV MIL
, MTL_SERIAL_NUMBERS WLC
, WMS_LICENSE_PLATE_NUMBERS WLN
, MTL_SYSTEM_ITEMS_VL MSIV
, MTL_PARAMETERS MP
, MTL_LOT_NUMBERS MLN
WHERE MP.ORGANIZATION_ID = WLN.ORGANIZATION_ID
AND WLN.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND WLN.ORGANIZATION_ID = WLC.CURRENT_ORGANIZATION_ID
AND WLN.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND WLN.LPN_ID = WLC.LPN_ID
AND WLC.CURRENT_ORGANIZATION_ID = MSIV.ORGANIZATION_ID
AND WLC.INVENTORY_ITEM_ID = MSIV.INVENTORY_ITEM_ID
AND WLC.CURRENT_ORGANIZATION_ID = MLN.ORGANIZATION_ID(+)
AND WLC.INVENTORY_ITEM_ID = MLN.INVENTORY_ITEM_ID(+)
AND WLC.LOT_NUMBER = MLN.LOT_NUMBER(+)
AND MSIV.SERIAL_NUMBER_CONTROL_CODE NOT IN (1
, 6)

Columns