Below SQL query gets the Customer information from TCA (Trading Community Architecture)
tables.
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 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 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 = 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_uses.location = cust_loc.location_id AND cust.party_id = cust_acct.party_id;
tables.
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 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 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 = 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_uses.location = cust_loc.location_id AND cust.party_id = cust_acct.party_id;
