/*
SQL Script for getting PO Details in Oracle Fusion
*/
SELECT
hou_prc.bu_name "Procurement BU",
hp.party_name "Supplier Name",
ss.vendor_site_code "Supplier Site",
hp_con.person_first_name || ' ' || hp_con.person_last_name "Supplier Contact",
hl_bill.location_code "Bill-to-Location",
hl_ship.location_code "Ship-to-Location",
poh.creation_date "Creation_date",
ppnf.full_name "Buyer",
poh.document_status "Status",
poh.segment1 "PO Number",
pol.line_num "Line Number",
pol.item_description "Item Description",
pol.quantity "Qty",
pol.uom_code "UOM",
pol.unit_price "Unit Price",
/* Added Line Amount derived column */
(pol.quantity * pol.unit_price) "Line Amount",
/* Concatenated Charge Account */
gcc.segment1 || '-' || gcc.segment2 || '-' || gcc.segment3 || '-' || gcc.segment4 "Charge Account",
pod.distribution_num "Distribution Number"
FROM
po_headers_all poh,
po_lines_all pol,
po_line_locations_all pll,
po_distributions_all pod,
poz_suppliers sup,
hz_parties hp,
hz_parties hp_con,
poz_supplier_sites_v ss,
fun_all_business_units_v hou_prc,
per_person_names_f ppnf,
gl_code_combinations gcc,
hr_locations_all hl_ship,
hr_locations_all hl_bill,
poz_supplier_contacts psc
WHERE 1=1
/* Core PO Table Joins */
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = pll.po_line_id
AND pll.line_location_id = pod.line_location_id
/* Supplier and Site Joins */
AND poh.vendor_id = sup.vendor_id
AND sup.party_id = hp.party_id
AND poh.vendor_site_id = ss.vendor_site_id
/* Contact Joins with Outer Join for stability */
AND poh.vendor_contact_id = psc.vendor_contact_id(+)
AND psc.per_party_id = hp_con.party_id(+)
/* Organization and Buyer Filter */
AND poh.prc_bu_id = hou_prc.bu_id
AND poh.agent_id = ppnf.person_id
AND ppnf.name_type = 'GLOBAL'
AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
/* Accounting and Location Outer Joins */
AND pod.code_combination_id = gcc.code_combination_id
AND pll.ship_to_location_id = hl_ship.location_id(+)
AND poh.bill_to_location_id = hl_bill.location_id(+)
/* Parameters for BIP Data Model */
AND (poh.segment1 = :P_PO_NUM OR :P_PO_NUM IS NULL)
AND TRUNC(poh.creation_date) BETWEEN :P_FROM_DATE AND :P_TO_DATE
ORDER BY
poh.segment1 DESC,
pol.line_num ASC
LOV Script
select poh.segment1
from
po_headers_all poh
Result:
