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

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 );

Do you think this Article is useful?

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

Unknown 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.
Unknown 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
Unknown said... December 29, 2018 at 7:59 PM Thanks and Regards. Oracle Apps R12 Training Videos at affordable cost. please check oracleappstechnical.com

Oracle Apps Knowledge Sharing

Thursday, May 14, 2015