Oracle Cloud P2P - Query to get Oracle Cloud (Fusion) Procure to Payment (P2P) details

SELECT

  poha.po_header_id,

  poha.segment1 AS po_num,

  haot.name AS proc_bu,

  haot.name AS req_bu,

  haot.name AS billto_bu,

  hrla1.location_name AS bill_to_loc,

  hrla.location_name AS ship_to_loc,

  ps.segment1 vendor_num,

  hp.party_name AS vendor_name,

  hps.party_site_name AS site_name,

  apt.name,

  ppnf.last_name||', '||ppnf.first_name AS buyer_name,

  poha.document_status,

  poha.currency_code,

  pola.line_num,

  pola.item_description,

  polt.line_type,

  pola.quantity AS quantity,

  DECODE (polt.line_type,'Goods', pola.unit_price,'Fixed Price Services', pola.amount) AS unit_price,

  DECODE (polt.line_type,'Goods', (pola.quantity * pola.unit_price), 'Fixed Price Services', pola.amount) AS po_lin_amount,

  pola.line_status,

  to_char(polla.need_by_date,'DD-MON-YYYY') need_by_date,

  to_char(polla.promised_date,'DD-MON-YYYY') promised_date,

  pod1.distribution_num,

  pod1.destination_subinventory,

  pod1.quantity_ordered,

  gcc.segment2 cost_center,

  gcc.segment1||'.'|| gcc.segment2||'.'|| gcc.segment3||'.'|| gcc.segment4||'.'|| gcc.segment5||'.'|| gcc.segment6||'.'|| gcc.segment7||'.'|| gcc.segment8 AS CHARGE_ACCOUNT,

  inv.invoice_num,

  to_char(inv.invoice_date,'DD-Mon-YYYY') invoice_date,

  inv.invoice_amount,

  inv.amount_paid,

  decode(ap_invoices_utility_pkg.get_approval_status(inv.invoice_id,inv.invoice_amount,inv.payment_status_flag,inv.invoice_type_lookup_code),

         'FULL'            , 'Fully Applied'

        ,'NEVER APPROVED'  , 'Never Validated'

        ,'NEEDS REAPPROVAL', 'Needs Revalidation'

        ,'CANCELLED'       , 'Cancelled'

        ,'UNPAID'          , 'Unpaid'

        ,'AVAILABLE'       , 'Available'

        ,'UNAPPROVED'      , 'Unvalidated'

        ,'APPROVED'        , 'Validated'

        ,'PERMANENT'       , 'Permanent Prepayment'

        ,NULL

  ) inv_status,

  ipa.payment_method_code,

  ipa.paper_document_number,

  ipa.payment_date,

  ipa.payment_amount payment_amount1,

  ipa.ext_bank_account_number,

  ipa.ext_branch_number,

  ipa.payment_profile_sys_name,

  ipa.payment_process_request_name

FROM

  po_headers_all poha,

  hr_organization_units_f_tl haot,

  hr_locations_all hrla,

  hr_locations_all hrla1,

  poz_suppliers ps,

  hz_parties hp,

  hz_party_sites hps,

  ap_terms_tl apt,

  per_person_names_f_v ppnf,

  poz_supplier_sites_all_m pss,

  po_lines_all pola,

  po_line_types_tl polt,

  po_line_locations_all polla,

  po_distributions_all pod1,

  gl_code_combinations gcc,

  ap_invoices_all inv,

  ap_invoice_lines_all lin,

  ap_invoice_distributions_all invd,

  ap_invoice_payments_all aipa,

  iby_docs_payable_all idpa,

  iby_payments_all ipa

WHERE 1 = 1

  AND haot.organization_id = poha.prc_bu_id

  and haot.language = 'US'

  AND hrla.location_id = poha.ship_to_location_id

  AND hrla1.location_id = poha.bill_to_location_id

  AND xep.legal_entity_id = poha.soldto_le_id

  AND ps.vendor_id = poha.vendor_id

  AND hp.party_id = hps.party_id

  AND pss.party_site_id =  hps.party_site_id

  AND pss.vendor_site_id = poha.vendor_site_id

  AND apt.term_id = poha.terms_id

  and apt.language = 'US'

  AND ppnf.person_id = poha.agent_id

  AND poha.po_header_id = pola.po_header_id

  AND pola.line_type_id = polt.line_type_id

  and polt.language = 'US'

  AND poha.po_header_id = pola.po_header_id

  AND pola.category_id = cat.category_id

  AND polla.po_header_id = poha.po_header_id

  AND polla.po_line_id  = pola.po_line_id

  AND pod1.code_combination_id = gcc.code_combination_id

  AND pod1.po_header_id = poha.po_header_id

  AND pod1.po_header_id = pola.po_header_id

  AND pod1.line_location_id = polla.line_location_id

  AND poha.po_header_id = lin.po_header_id

  AND pola.po_line_id = lin.po_line_id

  AND pod1.po_distribution_id = lin.po_distribution_id

  AND inv.invoice_id = lin.invoice_id

  AND invd.invoice_id = inv.invoice_id

  AND lin.line_number = invd.invoice_line_number

  AND inv.invoice_id = idpa.calling_app_doc_unique_ref2

  AND aipa.invoice_payment_id = idpa.calling_app_doc_unique_ref4

  AND aipa.invoice_id = idpa.calling_app_doc_unique_ref2

  AND aipa.check_id = idpa.calling_app_doc_unique_ref1

  AND idpa.formatting_payment_id = ipa.payment_id

  AND haot.name like 'US1 B%'

ORDER BY

   haot.name

  ,poha.segment1

  ,pola.line_num

  ,pod1.distribution_num