Query to Fetch AP Payment Template Details in R12 ~ Oracle Apps Knowledge Sharing

Thursday, May 14, 2015

Query to Fetch AP Payment Template Details in R12

SELECT 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
WHERE  fu.user_id = apt.last_updated_by
   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;

Do you think this Article is useful?

1 Responses to “Query to Fetch AP Payment Template Details in R12”

Anonymous said... May 20, 2022 at 11:56 AM Thank you very much!

Oracle Apps Knowledge Sharing

Thursday, May 14, 2015