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; 

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!


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.