Payment Process Request in Oracle fusion or E business suit is created to gather all the applicable invoices in P2P Cycle in order to pay the suppliers. ( Come on, they would want their money once they have delivered you the item or the services). Here is the query you can use to get the details:
SELECT aisca.CHECKRUN_ID,
aisca.checkrun_name,
tmplt.template_name ,
aisca.check_date,
aisca.period_name,
aisca.pay_thru_date,
Pmnt_mthd_tl.payment_method_name Payment_Method,
hp.party_name supplier_name,
bank_detail.party_name bank_name,
bank_brach.party_name bank_branch_name,
bank_acct.bank_account_name,
bank_acct.bank_account_num,
bank_acct.masked_account_num,
bank_acct.account_holder_name,
pmnt_doc.payment_document_name,
Pmnt_Prof.payment_profile_name Payment_Profile,
aisca.status
FROM ap_inv_selection_criteria_all aisca,
poz_suppliers supp,
hz_parties hp,
ap_payment_templates tmplt,
ce_payment_documents pmnt_doc,
IBY_ACCT_PMT_PROFILES_TL Pmnt_Prof,
ce_bank_accounts bank_Acct,
hz_parties bank_detail,
hz_parties bank_brach,
iby_payment_methods_tl Pmnt_mthd_tl
WHERE 1=1
AND supp.vendor_id(+) = aisca.vendor_id
and hp.party_id(+) = supp.party_id
AND tmplt.template_id(+) = aisca.template_id
AND pmnt_doc.payment_document_id(+) = aisca.payment_document_id
AND Pmnt_Prof.PAYMENT_PROFILE_ID(+) = aisca.PAYMENT_PROFILE_ID
AND Pmnt_prof.language = USERENV(‘LANG’)
AND bank_Acct.bank_account_id(+) = aisca.bank_account_id
AND Bank_Detail.party_id(+) = bank_Acct.BANK_ID
AND Bank_Brach.party_id(+) = bank_Acct.Bank_branch_id
AND Pmnt_mthd_tl.payment_method_code = aisca.payment_method_code
AND Pmnt_mthd_tl.LANGUAGE = USERENV(‘LANG’)
Order by aisca.creation_date desc
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...
Brief Details:
Once you decide to make a payment in Fusion, you’ll create a payment process Request which is nothing but the checkrun_name in the above query. This request may contain supplier name, Payment Template, Payment Method Code etc. These all the parameters will determine the invoices to be picked and the account to be used for the payment.
Main Tables:
| Table Name | Usage |
| ap_inv_selection_criteria_all | Base table of Payment Process Request |
| poz_suppliers | Base table of the suppliers |
| ap_payment_templates | Payment Template Table |
| ce_payment_documents | Payment Document table like Check, electronic etc |
| IBY_ACCT_PMT_PROFILES_B | Payment Profile Table |
| ce_bank_accounts | Bank Account Base Table |
Related Posts:
Invoices Table in Oracle Fusion – AP_INVOICES_ALL
How To Fetch (Query) The Payment Details In Fusion
P2P Cycle in Oracle Fusion
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
