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.MTL_ONHAND_LPN_MWB_V

Source

View Text - Preformatted


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 ,wlc.uom_code uom ,wlc.quantity on_hand ,0 unpacked ,wlc.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 ,wlc.txn_error_flag txn_error_flag ,wln.outermost_lpn_id outermost_lpn_id ,wln.parent_lpn_id parent_lpn_id ,2 planning_tp_type ,wln.organization_id planning_organization_id ,2 owning_tp_type ,wln.organization_id owning_organization_id ,msiv.lot_control_code item_lot_control ,msiv.serial_number_control_code item_serial_control ,wlc.secondary_quantity secondary_onhand ,msiv.secondary_uom_code secondary_uom_code ,TO_CHAR (NULL) grade_code ,0 secondary_unpacked ,wlc.secondary_quantity secondary_packed FROM mtl_item_locations_kfv mil ,wms_lpn_contents 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.locator_id = mil.inventory_location_id(+) AND wln.lpn_id = wlc.parent_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 DECODE (wln.lpn_context, 10, 2, NVL (wlc.serial_summary_entry, 2)) = 2 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 ,wlc.lpn_txn_error_flag txn_error_flag ,wln.outermost_lpn_id outermost_lpn_id ,wln.parent_lpn_id parent_lpn_id ,2 planning_tp_type ,wln.organization_id planning_organization_id ,2 owning_tp_type ,wln.organization_id owning_organization_id ,msiv.lot_control_code item_lot_control ,msiv.serial_number_control_code item_serial_control ,1 secondary_onhand ,msiv.secondary_uom_code secondary_uom_code ,TO_CHAR (NULL) grade_code ,TO_NUMBER (NULL) secondary_unpacked ,TO_NUMBER (NULL) secondary_packed 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(+)

View Text - HTML Formatted

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
, WLC.UOM_CODE UOM
, WLC.QUANTITY ON_HAND
, 0 UNPACKED
, WLC.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
, WLC.TXN_ERROR_FLAG TXN_ERROR_FLAG
, WLN.OUTERMOST_LPN_ID OUTERMOST_LPN_ID
, WLN.PARENT_LPN_ID PARENT_LPN_ID
, 2 PLANNING_TP_TYPE
, WLN.ORGANIZATION_ID PLANNING_ORGANIZATION_ID
, 2 OWNING_TP_TYPE
, WLN.ORGANIZATION_ID OWNING_ORGANIZATION_ID
, MSIV.LOT_CONTROL_CODE ITEM_LOT_CONTROL
, MSIV.SERIAL_NUMBER_CONTROL_CODE ITEM_SERIAL_CONTROL
, WLC.SECONDARY_QUANTITY SECONDARY_ONHAND
, MSIV.SECONDARY_UOM_CODE SECONDARY_UOM_CODE
, TO_CHAR (NULL) GRADE_CODE
, 0 SECONDARY_UNPACKED
, WLC.SECONDARY_QUANTITY SECONDARY_PACKED
FROM MTL_ITEM_LOCATIONS_KFV MIL
, WMS_LPN_CONTENTS 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.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND WLN.LPN_ID = WLC.PARENT_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 DECODE (WLN.LPN_CONTEXT
, 10
, 2
, NVL (WLC.SERIAL_SUMMARY_ENTRY
, 2)) = 2 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
, WLC.LPN_TXN_ERROR_FLAG TXN_ERROR_FLAG
, WLN.OUTERMOST_LPN_ID OUTERMOST_LPN_ID
, WLN.PARENT_LPN_ID PARENT_LPN_ID
, 2 PLANNING_TP_TYPE
, WLN.ORGANIZATION_ID PLANNING_ORGANIZATION_ID
, 2 OWNING_TP_TYPE
, WLN.ORGANIZATION_ID OWNING_ORGANIZATION_ID
, MSIV.LOT_CONTROL_CODE ITEM_LOT_CONTROL
, MSIV.SERIAL_NUMBER_CONTROL_CODE ITEM_SERIAL_CONTROL
, 1 SECONDARY_ONHAND
, MSIV.SECONDARY_UOM_CODE SECONDARY_UOM_CODE
, TO_CHAR (NULL) GRADE_CODE
, TO_NUMBER (NULL) SECONDARY_UNPACKED
, TO_NUMBER (NULL) SECONDARY_PACKED
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(+)