Query to select all Banks and their respective Branches

SELECT
   bank.party_name AS bank,
   branch.party_name AS branch,
   ftt.description AS branch_country
FROM
   hz_relationships hr,
   hz_parties bank,
   hz_parties branch,
   fnd_territories_tl ftt
WHERE
   1 = 1
   AND hr.relationship_code = 'HAS_BRANCH'
   AND hr.object_id = branch.party_id
   AND hr.subject_id = bank.party_id
   AND ftt.territory_code = branch.home_country
ORDER BY
   bank.party_name,
   branch.party_name

Comments