In this post you’ll see PO total amount displays based on currency rate, this query is calculating the PO amount on line level, this query will help you to get PO amount if it is created in different currency in Oracle Fusion Cloud application.
Query to get PO amount with currency rate in Oracle Fusion
Select SUM(CASE WHEN pla1.purchase_basis = ‘SERVICES’ THEN
CASE WHEN pha1.currency_code = ‘AED’ THEN
NVL(pla1.amount,0)
ELSE (pla1.amount * pha1.rate)
END
ELSE CASE WHEN pha1.currency_code = ‘AED’ THEN
(nvl(pla1.quantity, 1) * pla1.unit_price)
ELSE
(nvl(pla1.quantity, 1) * pla1.unit_price)* pha1.rate
END
END)
FROM po_lines_all pla1,
po_headers_all pha1,
poz_suppliers_v sup1
WHERE pla1.po_header_id = pha1.po_header_id
AND pha1.prc_bu_id = org.organization_id
AND pha1.agent_id = papf.person_id
AND sup1.vendor_id = pha1.vendor_id
AND NVL(sup1.vendor_type_lookup_code,’X’) = NVL(sup.vendor_type_lookup_code,’X’)
and pla1.po_header_id = pha.po_header_id
AND pha1.document_status IN (‘CLOSED’,’OPEN’,’CLOSED FOR INVOICING’,’FINALLY CLOSED’,’CLOSED FOR RECEIVING’)
AND pha1.type_lookup_code = ‘STANDARD’