Thursday, May 14, 2015

R12 : Query to fetch Internal Bank Account Details with Payables and Receivables Usages in Oracle Apps













SELECT xep.name                                       legal_entity_name,
       ou.name                                        "Operating Unit",
       cba.bank_account_name                          "Bank Account Name",
       cba.bank_account_num                           "Bank Account Number",
       NVL(cba.attribute1, cba.currency_code)         currency,
       cba.multi_currency_allowed_flag                "Multi Currency Flag",
       cba.account_classification                     "Account Classification",
       bb.bank_name                                   "Bank Name",
       bb.bank_branch_type                            "Bank Branch Type",
       bb.bank_branch_name                            "Bank Branch Name",
       bb.bank_branch_number                          "Bank Branch Number",
       bb.eft_swift_code                              "Swift Code",
       bau.ap_use_enable_flag                         bank_setup_ap_use_flag,
       bau.ar_use_enable_flag                         bank_setup_ar_use_flag,
       gcf.concatenated_segments
       "ASSET GL Code Combination",
       (SELECT RTRIM(XMLAGG(XMLELEMENT(e, apg.vendor_pay_group||
                     ',')) .extract('//text()'),
                       ',')
        FROM   (SELECT DISTINCT apg.vendor_pay_group,
                                bank_account_id
                FROM   ap_payment_templates apt,
                       ap_pay_group apg
                WHERE  1 = 1
                   AND apg.template_id = apt.template_id
                   AND NVL(apt.inactive_date, SYSDATE + 1) > SYSDATE) apg
        WHERE  bank_account_id = cba.bank_account_id) used_in_ap_pay_groups,
       (SELECT RTRIM(XMLAGG(XMLELEMENT(e, arm.name|| CHR(10)||
                     ',')) .extract('//text()'), ',')
        FROM   ar_receipt_method_accounts_all arma,
               ar_receipt_methods arm
        WHERE  arm.receipt_method_id = arma.receipt_method_id
           AND arma.remit_bank_acct_use_id = bau.bank_acct_use_id)
                                                      used_in_ar_recipt_mthods
FROM   ce_bank_accounts cba,
       ce_bank_acct_uses_all bau,
       cefv_bank_branches bb,
       hr_operating_units ou,
       xle_entity_profiles xep,
       gl_code_combinations_kfv gcf
WHERE  cba.bank_account_id = bau.bank_account_id
   AND cba.bank_branch_id = bb.bank_branch_id
   AND ou.organization_id = bau.org_id
   AND cba.asset_code_combination_id = gcf.code_combination_id
   AND ( cba.end_date IS NULL
          OR cba.end_date > TRUNC(SYSDATE) )
   AND ou.default_legal_context_id = xep.legal_entity_id
ORDER  BY ( cba.bank_account_num ); 

2 Responses to “R12 : Query to fetch Internal Bank Account Details with Payables and Receivables Usages in Oracle Apps”

Sateesh Kumar said...
November 3, 2015 at 2:46 AM

Hi,
I am going to work on Oracle Incentive Compensation (OIC) module. Would you please let me know the flow and integration of OIC module with other financial modules.


Sridevi Koduru said...
July 7, 2016 at 12:45 AM

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


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.