Query:
SELECT POH.segment1 "PO Number",
POH.type_lookup_code "Document Type Code",
POS.style_name "Style Display Name",
PRC_BU.bu_name "Procurement BU",
REQ_BU.bu_name "Requistioning BU",
LE.name "Sold-To Legal Entity",
BILL_BU.bu_name "Bill-To BU",
(SELECT PPF.full_name
FROM per_person_names_f PPF
WHERE POH.agent_id = PPF.person_id
AND PPF.name_type = 'GLOBAL'
AND Trunc(SYSDATE) BETWEEN
Nvl(PPF.effective_start_date, SYSDATE - 1)
AND
Nvl(PPF.effective_end_date, SYSDATE + 1))
Buyer,
POH.currency_code,
POH.rate,
POH.rate_type,
To_char(POH.rate_date, 'YYYY/MM/DD') "Rate Date",
POH.comments,
HRLB.location_name "Bill-To Location",
HRLS.location_name "Ship-To Location",
SUP.vendor_name "Supplier",
SUP.segment1 "Supplier Number",
SITE.vendor_site_code "Supplier Site",
CONT.party_name "Supplier Contact",
POH.vendor_order_num "Vendor Order Num",
POH.fob_lookup_code "FOB",
POH.freight_terms_lookup_code "Freight Terms",
POH.pay_on_code "Pay On Code",
TERM.name "Payment Terms",
POH.acceptance_required_flag "Required Acknowledgment",
POH.acceptance_within_days "Acknowledge Within (Days)",
POH.supplier_notif_method "Communication Method",
POH.email_address "To Email",
POH.confirming_order_flag "Confirming order",
POH.note_to_receiver "Note to Receiver",
POH.note_to_vendor "Note to Supplier",
POH.default_taxation_country
"Default Taxation Country Code",
POH.tax_document_subtype "Tax Document Subtype Code",
Nvl((SELECT PEA.email_address
FROM per_email_addresses PEA
WHERE 1 = 1
AND POH.agent_id = PEA.person_id
AND PEA.email_type = 'W1'
AND SYSDATE BETWEEN PEA.date_from AND
Nvl(PEA.date_to, SYSDATE)), (
SELECT PEA.email_address
FROM per_email_addresses PEA
WHERE 1 = 1
AND POH.agent_id = PEA.person_id
AND PEA.email_type = 'H1'
AND SYSDATE BETWEEN PEA.date_from AND Nvl(PEA.date_to, SYSDATE)))
"Buyer E-mail",
POH.mode_of_transport "Mode of Transport",
POH.service_level "Service Level",
POH.first_pty_reg_id
"First Party Tax Registration Number",
POH.third_pty_reg_id
"Third Party Tax Registration Number",
POH.buyer_managed_transport_flag
"Buyer Managed Transportation",
POH.master_contract_id "Master Contract ID",
POH.cc_email_address "Cc Email",
POH.bcc_email_address "Bcc Email",
POH.document_status "Document Status",
POH.po_header_id,
POH.created_by "Created BY",
To_char(POH.creation_date, 'YYYY/MM/DD') "Creation Date",
POH.last_updated_by,
To_char(POH.last_update_date, 'YYYY/MM/DD') "Last Update Date",
POH.object_version_number
FROM po_headers_all POH,
hz_parties PARTY,
ap_terms_tl TERM,
poz_suppliers_v SUP,
poz_supplier_sites_v SITE,
poz_supplier_contacts_v CONT,
hr_locations_all HRLS,
hr_locations_all HRLB,
xle_entity_profiles LE,
fun_all_business_units_v BILL_BU,
fun_all_business_units_v PRC_BU,
fun_all_business_units_v REQ_BU,
po_doc_style_headers POS
WHERE 1 = 1
AND POH.carrier_id = PARTY.party_id (+)
AND POS.style_id = POH.style_id
AND POH.vendor_id = SUP.vendor_id
AND POH.vendor_site_id = SITE.vendor_site_id
AND SUP.vendor_id = SITE.vendor_id
AND POH.vendor_contact_id = CONT.vendor_contact_id(+)
AND POH.prc_bu_id = PRC_BU.bu_id(+)
AND POH.req_bu_id = REQ_BU.bu_id(+)
AND POH.billto_bu_id = BILL_BU.bu_id(+)
AND POH.ship_to_location_id = HRLS.location_id(+)
AND POH.bill_to_location_id = HRLB.location_id(+)
AND POH.soldto_le_id = LE.legal_entity_id(+)
AND POH.terms_id = TERM.term_id(+)
AND TERM.LANGUAGE(+) = 'US'
AND POH.type_lookup_code = 'STANDARD'
AND POH.segment1 = 'EMS-PO117' -- Input PO Number
How to Run the SQL in Oracle Fusion Cloud Applications (Analytics)?
Tools → Reports and Analytics → Browse Catalog → Create → Data Model
Data → View
Verify the SQL Output with UI Data!
