Inventory Valuation Report

SELECT 
  i.ITEM_NUMBER,
  i.DESCRIPTION AS ITEM_DESCRIPTION,
  i.PRIMARY_UOM_CODE,
  ic.ITEM_CATALOG_GROUP,
  o.ORGANIZATION_CODE,
  o.ORGANIZATION_NAME,
  t.SUBINVENTORY_CODE,
  l.LOCATOR_SEGMENTS,
  t.ON_HAND_QUANTITY,
  t.AVAILABLE_QUANTITY,
  t.RESERVED_QUANTITY,
  c.ITEM_COST AS UNIT_COST,
  c.COST_TYPE,
  (t.ON_HAND_QUANTITY * c.ITEM_COST) AS TOTAL_VALUE,
  i.INVENTORY_ITEM_STATUS_CODE,
  i.PLANNING_MAKE_BUY_CODE,
  i.ABC_CLASS_CODE,
  i.CREATED_BY,
  i.CREATION_DATE,
  i.LAST_UPDATE_DATE,
  s.DESCRIPTION AS SUBINV_DESCRIPTION,
  s.ASSET_INVENTORY AS ASSET_SUBINV_FLAG
FROM MTL_ONHAND_QUANTITIES_DETAIL t
JOIN MTL_SYSTEM_ITEMS_B i ON t.INVENTORY_ITEM_ID = i.INVENTORY_ITEM_ID 
  AND t.ORGANIZATION_ID = i.ORGANIZATION_ID
JOIN MTL_PARAMETERS o ON t.ORGANIZATION_ID = o.ORGANIZATION_ID
LEFT JOIN MTL_ITEM_CATEGORIES ic ON i.INVENTORY_ITEM_ID = ic.INVENTORY_ITEM_ID 
  AND i.ORGANIZATION_ID = ic.ORGANIZATION_ID
LEFT JOIN CST_ITEM_COSTS c ON i.INVENTORY_ITEM_ID = c.INVENTORY_ITEM_ID 
  AND i.ORGANIZATION_ID = c.ORGANIZATION_ID
  AND c.COST_TYPE_ID = 1
LEFT JOIN MTL_ITEM_LOCATIONS l ON t.LOCATOR_ID = l.INVENTORY_LOCATION_ID
LEFT JOIN MTL_SECONDARY_INVENTORIES s ON t.SUBINVENTORY_CODE = s.SECONDARY_INVENTORY_NAME 
  AND t.ORGANIZATION_ID = s.ORGANIZATION_ID
WHERE t.ON_HAND_QUANTITY > 0
  AND (:organization_id IS NULL OR t.ORGANIZATION_ID = :organization_id)
  AND (:item_number IS NULL OR i.ITEM_NUMBER LIKE :item_number || '%')
  AND (:abc_class IS NULL OR i.ABC_CLASS_CODE = :abc_class)
  AND (:subinventory IS NULL OR t.SUBINVENTORY_CODE = :subinventory)
  AND i.INVENTORY_ITEM_STATUS_CODE = 'Active'
ORDER BY o.ORGANIZATION_CODE, i.ITEM_NUMBER, t.SUBINVENTORY_CODE