Oracle Cloud P2P - Query to get Oracle Cloud (Fusion) Pr Fusion ocure 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

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