Query to get all AP Payments related to a PPR:-
SELECT c.*FROM ap_checks_all c
WHERE c.payment_id IN (SELECT ip.payment_id
FROM iby_payments_all ip
WHERE payment_service_request_id IN
(SELECT
payment_service_request_id
FROM
iby_pay_service_requests
WHERE
calling_app_id = 200
AND
call_app_pay_service_req_code =
'&checkrun_name')
);
SQL Query to get Payment Process Request (PPR) Templates Used to Create a Payment Process Request (PPR) in oracle EBS R12 :-
SELECT AISCA.checkrun_name PPR_Name,
apt.template_id,apt.template_name,
ipmv.payment_method_name pmt_method_name,
cba.bank_account_name bank_acct_name,
ipp.payment_profile_name pmt_profile_name,
cpd.payment_document_name pmt_doc_name,
apt.pay_group_option pay_grp_optn,
(SELECT Rtrim(Xmlagg(XMLELEMENT(e, apg.vendor_pay_group||
',')) .extract('//text()'),
',')
FROM ap_pay_group apg
WHERE apg.template_id = apt.template_id) enabled_pay_groups,
apt.ou_group_option ou_grp_optn,
(SELECT Rtrim(Xmlagg(XMLELEMENT(e, hou.short_code||
',')) .extract('//text()'),
',')
FROM ap_ou_group,
hr_operating_units hou
WHERE org_id = hou.organization_id
AND template_id = apt.template_id) enabled_ous,
apt.currency_group_option curr_grp_optn,
(SELECT Rtrim(Xmlagg(XMLELEMENT(e, acg.currency_code||
',')) .extract('//text()'), ',')
FROM ap_currency_group acg
WHERE acg.template_id = apt.template_id) enabled_currencies,
apt.description ppp_description,
apt.inactive_date,
apt.addl_pay_thru_days,
apt.addl_pay_from_days,
apt.low_payment_priority,
apt.hi_payment_priority,
apt.vendor_id,
apt.pay_only_when_due_flag,
apt.vendor_type_lookup_code vdr_type_lcode,
apt.bank_account_id,
apt.payment_profile_id,
apt.zero_inv_allowed_flag,
apt.payment_method_code,
apt.inv_exchange_rate_type,
apt.payment_date_option,
apt.addl_payment_days,
apt.payment_exchange_rate_type,
apt.zero_amounts_allowed,
apt.payables_review_settings,
apt.calc_awt_int_flag,
apt.payments_review_settings,
apt.document_rejection_level_code doc_reject_lvl,
apt.create_instrs_flag,
apt.payment_rejection_level_code pmt_reject_lvl,
apt.payment_document_id,
plc.displayed_field supplier_type,
pv.vendor_name payee,
alc1.displayed_field template_type_name,
gdct.user_conversion_type user_rate_type,
fu.user_name
FROM ap_payment_templates apt,
po_lookup_codes plc,
iby_payment_methods_vl ipmv,
iby_payment_profiles ipp,
ce_bank_accounts cba,
ap_lookup_codes alc1,
gl_daily_conversion_types gdct,
po_vendors pv,
fnd_user fu,
ce_payment_documents cpd,
ap_inv_selection_criteria_all AISCA
WHERE 1 = 1
AND fu.user_id = apt.last_updated_by
AND AISCA.checkrun_name = '&checkrun_name'
AND AISCA.template_id = apt.template_id
AND plc.lookup_code(+) = apt.vendor_type_lookup_code
AND plc.lookup_type(+) = 'VENDOR TYPE'
AND cba.bank_account_id(+) = apt.bank_account_id
AND ipmv.payment_method_code(+) = apt.payment_method_code
AND alc1.lookup_type(+) = 'PAYMENT_TEMPLATE_TYPE'
AND alc1.lookup_code(+) = apt.template_type
AND gdct.conversion_type(+) = apt.payment_exchange_rate_type
AND ipp.payment_profile_id(+) = apt.payment_profile_id
AND pv.party_id(+) = apt.party_id
AND apt.payment_document_id = cpd.payment_document_id(+)
ORDER BY apt.template_name,
ipmv.payment_method_name;
