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:
Post a Comment