Let 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%';
No comments:
Post a Comment