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_NEW_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 ,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)

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
, 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)