Introduction
This Post illustrates the steps required to Extract for Payments – Check and EFT in Oracle EBS R12.
Script to used for Extract for Payments – Check and EFT.
SELECT papf.employee_number employee_number
, aps.vendor_name payee_name
, ieba.bank_account_num bank_account_number
, hp_bank.party_name bank_name
, hp_bank.organization_name_phonetic bank_number
, hps_branch.party_name branch_name
, ipa.org_id org_id
, ipa.payment_date payment_date
, ieba.bank_account_type bank_account_type
, ieba.bank_id
, cba.bank_account_type int_bank_account_type
, cba.bank_account_num int_bank_account_num
, round(sum(ifdpv.payment_amount *
decode(ipa.payment_currency_code,
asp.base_currency_code,
1,
decode(aivsc.exchange_rate_type,
‘User’, –l_exchange_rate_type
ap_pmt_callout_pkg.get_user_rate(asp.base_currency_code,
ipa.payment_currency_code,
ifdpv.calling_app_doc_unique_ref1),
ap_utilities_pkg.get_exchange_rate(ipa.payment_currency_code,
asp.base_currency_code,
aivsc.exchange_rate_type,
aivsc.check_date,
‘CONFIRM’)))),0) payment_amount
, MIN(ifdpv.calling_app_doc_ref_number) reference
, MIN(ifdpv.document_description) document_description
FROM iby_pay_instructions_all ipia
, iby_payments_all ipa
, iby_external_payees_all iepa
, ap_supplier_sites_all assa
, ap_suppliers aps
, iby_ext_bank_accounts ieba
, hz_parties hp_bank
, hz_parties hps_branch
, iby_fd_docs_payable_v ifdpv
, ap_inv_selection_criteria_all aivsc
, ap_system_parameters_all asp
, ce_bank_accounts cba
, per_all_people_f papf
WHERE ipia.payment_instruction_id = p_payment_instruction_id
AND ipia.payment_instruction_id = ipa.payment_instruction_id
AND ipa.ext_payee_id = iepa.ext_payee_id
AND iepa.supplier_site_id = assa.vendor_site_id
AND assa.vendor_id = aps.vendor_id
AND ipa.external_bank_account_id = ieba.ext_bank_account_id (+)
AND ieba.bank_id = hp_bank.party_id (+)
AND ieba.branch_id = hps_branch.party_id (+)
AND ipa.payment_id = ifdpv.payment_id
AND ifdpv.calling_app_doc_unique_ref1 = aivsc.checkrun_id
AND ipa.org_id = asp.org_id
AND ipa.internal_bank_account_id = cba.bank_account_id
AND aps.employee_id = papf.person_id (+)
GROUP BY papf.employee_number
, aps.vendor_name
, ieba.bank_account_num
, hp_bank.party_name
, hp_bank.organization_name_phonetic
, hps_branch.party_name
, ipa.org_id
, ipa.payment_date
, ieba.bank_account_type
, ieba.bank_id
, cba.bank_account_type
, cba.bank_account_num;
What we expect in the script
This script helps us to comprehend how to Extract for Payments – Check and EFT. Couple of tables which is being used in the query are iby_pay_instructions_all, iby_payments_all, iby_external_payees_all, ap_supplier_sites_all, ap_suppliers, iby_ext_bank_accounts, hz_parties
, hz_parties,hps_branch, iby_fd_docs_payable_v, ap_inv_selection_criteria_all , ap_system_parameters_all, ce_bank_accounts, per_all_people_f etc.
Summary
This Post described about Extract for Payments – Check and EFT in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyenm.ebiz@gmail.com or use the comment section below to ask your questions.
