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_TOTAL_V

Source

View Text - Preformatted


SELECT moq.organization_id organization_id, mp.organization_code organization_code, moq.subinventory_code subinventory_code, moq.locator_id locator_id, mil.concatenated_segments LOCATOR, moq.inventory_item_id inventory_item_id, msiv.description item_description, msiv.concatenated_segments item, moq.revision revision, msiv.primary_uom_code uom, moq.primary_transaction_quantity on_hand, DECODE ( moq.containerized_flag,1,0, moq.primary_transaction_quantity ) unpacked, DECODE (moq.containerized_flag,1,moq.primary_transaction_quantity,0 ) packed, moq.cost_group_id cost_group_id, TO_NUMBER (NULL) lpn_id, TO_CHAR (NULL) lpn, moq.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, msi.status_id subinventory_status_id, mil.status_id locator_status_id, mln.status_id lot_status_id, TO_NUMBER (NULL) serial_status_id, DECODE (moq.containerized_flag,1, 1,2, TO_NUMBER (NULL), TO_NUMBER (NULL) )containerized_flag, TO_CHAR (NULL) status_level, moq.planning_tp_type planning_tp_type, moq.planning_organization_id planning_organization_id, moq.owning_tp_type owning_tp_type, moq.owning_organization_id owning_organization_id, msiv.lot_control_code item_lot_control, msiv.serial_number_control_code item_serial_control /* INVCONV START rnrao */ ,moq.secondary_uom_code secondary_uom_code, moq.secondary_transaction_quantity secondary_onhand, mln.grade_code, DECODE (moq.containerized_flag,1,0,moq.secondary_transaction_quantity) secondary_unpacked, DECODE (moq.containerized_flag,1,moq.secondary_transaction_quantity,0) secondary_packed /* INVCONV END rnrao */ ,moq.status_id onhand_status_id ,moq.create_transaction_id create_transaction_id FROM mtl_parameters mp, mtl_item_locations_kfv mil, mtl_secondary_inventories msi, mtl_lot_numbers mln, mtl_system_items_vl msiv, mtl_onhand_quantities_detail moq WHERE moq.organization_id = mp.organization_id AND moq.organization_id = mil.organization_id(+) AND moq.locator_id = mil.inventory_location_id(+) AND moq.organization_id = msi.organization_id AND moq.subinventory_code = msi.secondary_inventory_name AND moq.organization_id = mln.organization_id(+) AND moq.inventory_item_id = mln.inventory_item_id(+) AND moq.lot_number = mln.lot_number(+) AND moq.organization_id = msiv.organization_id AND moq.inventory_item_id = msiv.inventory_item_id

View Text - HTML Formatted

SELECT MOQ.ORGANIZATION_ID ORGANIZATION_ID
, MP.ORGANIZATION_CODE ORGANIZATION_CODE
, MOQ.SUBINVENTORY_CODE SUBINVENTORY_CODE
, MOQ.LOCATOR_ID LOCATOR_ID
, MIL.CONCATENATED_SEGMENTS LOCATOR
, MOQ.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MSIV.DESCRIPTION ITEM_DESCRIPTION
, MSIV.CONCATENATED_SEGMENTS ITEM
, MOQ.REVISION REVISION
, MSIV.PRIMARY_UOM_CODE UOM
, MOQ.PRIMARY_TRANSACTION_QUANTITY ON_HAND
, DECODE ( MOQ.CONTAINERIZED_FLAG
, 1
, 0
, MOQ.PRIMARY_TRANSACTION_QUANTITY ) UNPACKED
, DECODE (MOQ.CONTAINERIZED_FLAG
, 1
, MOQ.PRIMARY_TRANSACTION_QUANTITY
, 0 ) PACKED
, MOQ.COST_GROUP_ID COST_GROUP_ID
, TO_NUMBER (NULL) LPN_ID
, TO_CHAR (NULL) LPN
, MOQ.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
, MSI.STATUS_ID SUBINVENTORY_STATUS_ID
, MIL.STATUS_ID LOCATOR_STATUS_ID
, MLN.STATUS_ID LOT_STATUS_ID
, TO_NUMBER (NULL) SERIAL_STATUS_ID
, DECODE (MOQ.CONTAINERIZED_FLAG
, 1
, 1
, 2
, TO_NUMBER (NULL)
, TO_NUMBER (NULL) )CONTAINERIZED_FLAG
, TO_CHAR (NULL) STATUS_LEVEL
, MOQ.PLANNING_TP_TYPE PLANNING_TP_TYPE
, MOQ.PLANNING_ORGANIZATION_ID PLANNING_ORGANIZATION_ID
, MOQ.OWNING_TP_TYPE OWNING_TP_TYPE
, MOQ.OWNING_ORGANIZATION_ID OWNING_ORGANIZATION_ID
, MSIV.LOT_CONTROL_CODE ITEM_LOT_CONTROL
, MSIV.SERIAL_NUMBER_CONTROL_CODE ITEM_SERIAL_CONTROL /* INVCONV START RNRAO */
, MOQ.SECONDARY_UOM_CODE SECONDARY_UOM_CODE
, MOQ.SECONDARY_TRANSACTION_QUANTITY SECONDARY_ONHAND
, MLN.GRADE_CODE
, DECODE (MOQ.CONTAINERIZED_FLAG
, 1
, 0
, MOQ.SECONDARY_TRANSACTION_QUANTITY) SECONDARY_UNPACKED
, DECODE (MOQ.CONTAINERIZED_FLAG
, 1
, MOQ.SECONDARY_TRANSACTION_QUANTITY
, 0) SECONDARY_PACKED /* INVCONV END RNRAO */
, MOQ.STATUS_ID ONHAND_STATUS_ID
, MOQ.CREATE_TRANSACTION_ID CREATE_TRANSACTION_ID
FROM MTL_PARAMETERS MP
, MTL_ITEM_LOCATIONS_KFV MIL
, MTL_SECONDARY_INVENTORIES MSI
, MTL_LOT_NUMBERS MLN
, MTL_SYSTEM_ITEMS_VL MSIV
, MTL_ONHAND_QUANTITIES_DETAIL MOQ
WHERE MOQ.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MOQ.ORGANIZATION_ID = MIL.ORGANIZATION_ID(+)
AND MOQ.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID(+)
AND MOQ.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MOQ.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
AND MOQ.ORGANIZATION_ID = MLN.ORGANIZATION_ID(+)
AND MOQ.INVENTORY_ITEM_ID = MLN.INVENTORY_ITEM_ID(+)
AND MOQ.LOT_NUMBER = MLN.LOT_NUMBER(+)
AND MOQ.ORGANIZATION_ID = MSIV.ORGANIZATION_ID
AND MOQ.INVENTORY_ITEM_ID = MSIV.INVENTORY_ITEM_ID