Thursday, December 23, 2010

Customer and their Bank Details in Oracle Apps R12

Lst us go step by step and finally we can see the consolidated Query. All these queries were tested in R12.1.1 instance.

Query to Fetch Customer Data:
SELECT
      cust.party_name customer_name
    , cust_acct.cust_account_id
    , 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
FROM
      hz_parties             cust
    , hz_cust_accounts       cust_acct
    , hz_cust_acct_sites_all cust_site
    , hz_party_sites         party_site
    , hz_cust_site_uses_all  cust_uses
    , hz_locations           cust_loc
WHERE cust.party_id               = cust_acct.party_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_site.party_site_id     = party_site.party_site_id
AND   party_site.location_id      = cust_loc.location_id
AND   cust.party_name             like '%&party_name%';

Query to Find the Bank Account id based on Customer Info
SELECT
    account.ext_bank_account_id -- Link to Bank and Branch Information
   ,acc_instr.instrument_id
   ,acc_instr.ext_pmt_party_id
   ,ext_payer.ext_payer_id
   ,ext_payer.cust_account_id  -- Link to Cust Account Info
   ,ext_payer.acct_site_use_id
FROM
    iby_ext_bank_accounts   account
  , iby_pmt_instr_uses_all  acc_instr
  , iby_external_payers_all ext_payer 
WHERE 1= 1
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   = '&id_from_previus_query';

Query to Find the Bank And Branch Information based on previous Query
SELECT
     cust.party_name                   customer_Party_name
    ,cust.party_id                     customer_party_id
    ,bank.party_name                   bank_name
    ,bank_prof.home_country   
    ,account.bank_account_num
    ,account.bank_account_name
    ,branch.party_name                 branch_name
    ,branch_prof.bank_or_branch_number branch_number
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
   ,hz_parties                cust
WHERE 1=1
AND bank.party_id                    = rel.object_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      = '&ext_bank_accout_id_frm_previous_query';

Consolidated Query to fetch Customer info, Customer Site info, Bank Info and Bank Branch Info:
SELECT
     cust.party_name customer_name
   , cust_acct.account_number
   , cust_acct.cust_account_id
   , 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
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_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_party_sites           party_site
   , hz_locations             cust_loc
WHERE 1=1
AND bank.party_id                    = rel.object_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 cust_acct.cust_account_id        = cust_site.cust_account_id
AND cust_site.cust_acct_site_id      = cust_uses.cust_acct_site_id
AND party_site.party_id              = cust.party_id
AND party_site.party_site_id         = cust_site.party_site_id
AND party_site.location_id           = cust_loc.location_id
AND cust.party_id                    = cust_acct.party_id
AND bank.party_id                    = account.bank_id
AND branch.party_id                  = account.branch_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_uses.site_use_id            = ext_payer.acct_site_use_id
AND cust.party_name             like '%$Party_name%';

1 Responses to “Customer and their Bank Details in Oracle Apps R12”

Anonymous said...
October 10, 2012 at 8:44 AM

SQL returns 2 rows when inserting party_name from hz_parties.

Is it possible that 2 banks accounts were created for this customer?


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.