Thursday, 9 July 2026

CURRENT ON-hand inventory quantities details in Oracle Fusion

How would you write an sql query TO retrieve CURRENT ON-hand inventory quantities BY organization AND item in fusion inventory?

==============================================================

SELECT   iop.organization_code                 AS inv_org_code,
         esi.item_number                       AS item_number,
         esi.description                       AS item_description,
         Sum(ioq.primary_transaction_quantity) AS total_onhand_quantity,
         esi.primary_uom_code                  AS primary_uom
FROM     inv_onhand_quantities_detail ioq,
         egp_system_items_vl esi,
         inv_org_parameters iop
WHERE    1=1
AND      ioq.inventory_item_id = esi.inventory_item_id
AND      ioq.organization_id = esi.organization_id
AND      ioq.organization_id = iop.organization_id
         /* Supports single or multiple selected org codes */
AND      (
                  iop.organization_code IN (:p_inv_org_code)
         OR       :p_inv_org_code IS NULL)
GROUP BY iop.organization_code,
         esi.item_number,
         esi.description,
         esi.primary_uom_code
HAVING   Sum(ioq.primary_transaction_quantity) > 0
ORDER BY iop.organization_code,
         esi.item_number
         --------------------------------------
         lov invcodelov

SELECT   organization_code AS d,
         organization_code AS r
FROM     inv_org_parameters
ORDER BY 1

---------------------

Result


No comments: