Query to get Customer Bank Account Details

Query to get Customer Bank Account Details
oracle apps ebs Customer Bank Account Details
oracle apps ebs sql for Customer Bank

SELECT cust.party_name                   customer_name,
       cust_acct.account_number,
       cust_uses.site_use_code,
       cust_loc.address1,
       cust_loc.address2,
       cust_loc.address3,
       cust_loc.address4,
       cust_loc.city,
       cust_loc.postal_code,
       bank.party_name                   bank_name,
       bank_prof.home_country,
       branch.party_name                 branch_name,
       branch_prof.bank_or_branch_number branch_number,
       account.bank_account_num,
       account.bank_account_name,
       account.IBAN,
       ixbv.EFT_SWIFT_CODE,
       ixpm.PAYMENT_METHOD_CODE,
       hou.name
  FROM hz_parties               bank,
       hz_relationships         rel,
       hz_parties               branch,
       hz_organization_profiles bank_prof,
       hz_organization_profiles branch_prof,
       iby_ext_bank_accounts    account,
       iby_account_owners       acc_owner,
       iby_external_payers_all  ext_payer,
       iby_pmt_instr_uses_all   acc_instr,
       hz_parties               cust,
       hz_cust_accounts         cust_acct,
       hz_cust_acct_sites_all   cust_site,
       hz_cust_site_uses_all    cust_uses,
       hz_locations             cust_loc,
       hz_party_sites           hps,
       iby_ext_party_pmt_mthds  ixpm,
       IBY_EXT_BANK_BRANCHES_V  ixbv,
       hr_operating_units       hou
 WHERE     1 = 1
       AND bank.party_id = rel.object_id
       AND hps.party_id = cust.PARTY_ID
       AND hps.PARTY_SITE_ID = cust_site.PARTY_SITE_ID
       AND hps.location_id = cust_loc.location_id
       AND bank.party_type = rel.object_type
       AND rel.object_table_name = 'HZ_PARTIES'
       AND rel.relationship_code = 'BRANCH_OF'
       AND rel.subject_id = branch.party_id
       AND rel.subject_type = branch.party_type
       AND rel.subject_table_name = 'HZ_PARTIES'
       AND bank.party_id = bank_prof.party_id
       AND branch.party_id = branch_prof.party_id
       AND bank.party_id = account.bank_id
       AND branch.party_id = account.branch_id
       AND account.ext_bank_account_id = acc_owner.ext_bank_account_id
       AND acc_owner.account_owner_party_id = cust.party_id
       AND account.ext_bank_account_id = acc_instr.instrument_id
       AND acc_instr.ext_pmt_party_id = ext_payer.ext_payer_id
       AND ext_payer.cust_account_id = cust_acct.cust_account_id
       AND cust_acct.cust_account_id = cust_site.cust_account_id
       AND cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
       AND cust_uses.site_use_id = ext_payer.acct_site_use_id
       AND cust.party_id = cust_acct.party_id
       AND SYSDATE BETWEEN (bank_prof.EFFECTIVE_START_DATE)
                       AND NVL (bank_prof.EFFECTIVE_END_DATE, SYSDATE + 1)
       AND SYSDATE BETWEEN (branch_prof.EFFECTIVE_START_DATE)
                       AND NVL (branch_prof.EFFECTIVE_END_DATE, SYSDATE + 1)
       AND ixpm.EXT_PMT_PARTY_ID = ext_payer.EXT_PAYER_ID
       AND ixpm.PRIMARY_FLAG = 'Y'
       AND ixpm.INACTIVE_DATE IS NULL
       AND cust_site.org_id = ORGANIZATION_ID
       AND hou.name LIKE '<Operating Unit Name>'
       AND ixbv.branch_party_id = branch.party_id
       AND ixbv.BANK_PARTY_ID = bank.party_id
       AND ixpm.PAYMENT_METHOD_CODE = 'EFT'

Related posts:

• How to Retrieve Period to Date (PTD) and Year to Date (YTD) Balances of GL Account in Oracle R12
• Sales order line status query in Oracle apps
• Query to find all Expense report entry delegations
• Query to find vacation rules in Oracle R12
• View accounting query in oracle apps R12
• Query to get AR invoice SLA and GL details
• Accounting entries not transferred to the General Ledger
• Query to get price list details in Oracle EBS
• Query to find all APIs of Oracle Apps modules
• Oracle R12 shipping status query
• R12 AR cash receipt SLA and GL details query
• Oracle apps AR Applications and SLA details
• Oracle EBS query to get AP Invoice SLA Details
• Oracle EBS query to get AR Invoice SLA Details
• Oracle AR Remit to address query
• Oracle Apps iExpense credit card details query
• Query to get customer tax registration details in R12
• Query to get Sales order and lines details in Oracle Apps
• Purchase Requisition Vertex debug xml query in Oracle EBS
• Purchase Order Vertex debug xml query in Oracle EBS
• AP Invoice Vertex debug xml query in Oracle EBS
• AR Invoice Vertex debug xml query in Oracle EBS
• Query to find AP invoice workflow item key in oracle apps
• Query to find serial number material transactions details
• Query to find period status for Inventory General Ledger Purchasing Payables Receivables Fixed Assets
• Oracle apps EBS sql to get Fixed Asset (FA) details with Net Book Value (NBV) and Depreciation
• Query to find Sales Orders that are shipped but not invoiced
• Query to get Customer Bank Account Details
• Query to get description of GL Code Combination
• Query to find Purchase Order and Invoice Details
• Query to find AP Invoices matched with receipts
• Query to find Supplier Bank Details
• Query to find Credit Memos on an AP invoice
• Query to find the AP invoices that are applied to multiple Purchase Orders
• AR Invoice grouping rule from RA_INTERFACE_LINES_ALL
• Query to find shipset and fulfilment set

Upload your own post and refer it anywhere anytime: