BI Report, Business Intelligence Report,, Fusion Payment System, Oracle Fusion, Oracle Fusion Finance

How To Fetch (Query) The Payment Details In Fusion

Payment: The final leg of P2P Cycle in Oracle. Once you run the payment process Request in Fusion, depending on the criteria you have chosen Invoices will be selected for payment. Once selected, you’ll also get the option to remove the invoices you don’t want to pay. Once you confirm the final list of the invoices to be paid, and take the payment process request to the conclusion, Invoices will be paid (at least in the system. You might still need to send the checks to the vendor or EFT file to the bank)

At high level, this is how the relationship among Payment Process Request, Payment, Checks and Invoices looks like:

Oracle Fusion Payment structure

To give the further details, every payment process request can have multiple invoices paid against. So relationship between Payment Process Request and Check is 1:N.

Again, 1 Check can be used to pay multiple invoices. At the same time one invoice can be paid by multiple checks. So relationship between Check and the Invoices is M:N. Table that holds the relationship is AP_INVOICE_PAYMENTS_ALL.

By the way, Here ( India) or Here ( Other Geographies) are few UNCONVENTIONAL tricks which I have learnt to improve the performance of BI reports or SQL queries. Try them only when nothing else works.. kind of SOS...
https://www.amazon.in/gp/product/B093CC1CLD?ie=UTF8&tag=oraclebytes-21&camp=3638&linkCode=xm2&creativeASIN=B093CC1CLD

Each payment can contain multiple invoices, and same invoice can be paid in multiple checks, so relationship between Invoice and Payment is M:N.

Table NameUsage
AP_CHECKS_ALLHolds the check information
AP_INVOICE_PAYMENTS_ALLHolds the relationship between Check and the Invoice
AP_INV_SELECTION_CRITERIA_ALLTo hold the Payment Process request Details
IBY_PAYMENTS_ALLBase table for payments
Main Tables and Their Usage

Payment Detail Query: Though not really needed, yet the query to fetch main Payment details is

SELECT ipa.payment_reference_number payment_number,
Pmnt_mthd_tl.payment_method_name Payment_Method,
flv.meaning payment_status,
ipa.payments_complete_flag,
ipa.payment_amount,
ipa.payment_date,
ipa.payment_due_date,
ipa.payment_currency_code,
ipa.int_bank_name Payer_Bank_Name,
ipa.int_bank_branch_name Payer_Branch_Name,
ipa.int_bank_branch_number Payer_Branch_Number,
ipa.int_bank_account_name      Payer_Account_name,
ipa.int_bank_account_number  Payer_Bank_Account_Number,
ipa.document_category_code,
ipa.payee_name,
ipa.payee_address1,
ipa.payee_address2,
ipa.payee_city,
ipa.payee_postal_code,
ipa.payee_state,
ipa.payee_country,
ipa.payment_profile_sys_name,
ipa.payment_profile_acct_name,
ipa.payment_process_request_name,
ipa.payee_party_name,
ipa.party_site_name,
ipa.payee_address_concat,
ipa.beneficiary_name,
ipa.payer_legal_entity_name,
ipa.payer_party_number,
ipa.payee_supplier_number,
ipa.payee_supplier_site_name
FROM iby_payments_all ipa,
iby_payment_methods_tl Pmnt_mthd_tl,
fnd_lookup_values flv
WHERE 1=1
AND Pmnt_mthd_tl.payment_method_code = ipa.payment_method_code
AND Pmnt_mthd_tl.LANGUAGE = USERENV(‘LANG’)
AND flv.lookup_type = ‘IBY_PAYMENT_STATUSES’
AND flv.lookup_code = ipa.payment_status
AND flv.enabled_flag = ‘Y’

Feedback:
Hope the article helped you. If it did, please rate the post. In case it didn’t, do leave a comment to let us know what did we miss.

Reference:
support.oracle.com


How useful was this post?

Click on a star to rate it!

Average rating 5 / 5. Vote count: 5

No votes so far! Be the first to rate this post.

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?