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; 

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

Unknown said...
July 7, 2016 at 12:51 AM

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b


Unknown said...
December 29, 2018 at 8:31 PM

Thanks and Regards. Oracle Apps R12 Training Videos at affordable cost. please check oracleappstechnical.com


Jone Martin said...
November 24, 2021 at 7:21 AM

I am implement your code in my programming. Its working good and get true result. While, I am service provider of Interior Painting Services in Knoxville TN. Our experts ready to make your house internally like palace.


omarsharif635 said...
March 24, 2022 at 9:42 AM

“The goal is to ensure that 100% jobs in government departments and different State government undertakings are crammed via Tamil adolescents,” said Finance and Human Resources Minister Palanivel Thiagarajan.

tamilnadu government jobs


adult shop said...
April 15, 2022 at 11:22 PM

Funtasia is all about unleashing the creative spark in the physical sense, stocking up on all adult toys to bring you the magic spice. Whether that means dressing in the sexiest lingerie, or playing with a world of sex toys, our wonderland caters to the very, very vivid imaginations. We love to help you express yourself and enhance your love life however you choose - so step inside and see what we’ve got waiting for you.Click Here More Details: what is a fleshlight


insuranceagency said...
May 16, 2022 at 11:24 PM

Captive Insurance Agents Portland Independent Insurance Agents


insuranceagency said...
May 16, 2022 at 11:32 PM

As an Independent Insurance Agent with Pacific Crest Services YOU will have access to direct appointments with our carrier partners. Mass Insurance Carriers gives you the POWER to be able to write more policies, to better help your clients, and to run a highly successful insurance agency! Click Here More Details: Captive Insurance Agents Portland Independent Insurance Agents


Customer care said...
May 19, 2022 at 5:06 AM


Faber Customer Care, Service Center & Showroom Near Me: FRANKE FABER INDIA Private Limited driving kitchen apparatuses producer with Italian beginning and has been a commonly recognized name for the beyond 20 years Faber customer care


Anonymous said...
May 20, 2022 at 11:56 AM

Thank you very much!


outofstatemoving said...
June 2, 2022 at 10:31 PM

When packing for a move, sooner or later you’ll get to packing a few cables here and there in the residence. In maximum cases, the ones cables can be electricity cords of digital devices and electric household home equipment but you’ll additionally get to packing connection cables of diverse peripheral devices, and extension cords as nicely. Click Here for more Details: Out of State Movers


wwwxxx said...
June 22, 2022 at 7:31 AM

✅It was awesome information providing by your content thank you for taking the time to share with us such a nice article. wwwxxx


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.