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:
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...
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 Name | Usage |
| AP_CHECKS_ALL | Holds the check information |
| AP_INVOICE_PAYMENTS_ALL | Holds the relationship between Check and the Invoice |
| AP_INV_SELECTION_CRITERIA_ALL | To hold the Payment Process request Details |
| IBY_PAYMENTS_ALL | Base table for payments |
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
