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%';
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
1 Responses to “Customer and their Bank Details in Oracle Apps R12”
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