Customer Site Information - Accounts & Sites

SELECT accounts.party_id,
       accounts.account_name,
       accounts.account_number,
       party_sites.party_site_name,
       sites.cust_acct_site_id,
       sites.cust_account_id,
       site_uses.site_use_code,
       site_uses.primary_flag,
       site_uses.site_use_id,
       site_uses.location,
       locations.ADDRESS1 || ' ' || locations.ADDRESS2 || ' ' || locations.ADDRESS3 || ' ' || locations.ADDRESS4 || ' ' || locations.CITY || ' ' || locations.POSTAL_CODE || ' ' || locations.STATE || ' ' || locations.country,
       territory.territory_short_name
FROM fusion.hz_cust_accounts accounts,
     fusion.hz_cust_acct_sites_all sites,
     fusion.hz_party_sites party_sites,
     fusion.hz_cust_site_uses_all site_uses,
     fusion.hz_locations locations,
     fusion.fnd_territories_vl territory
WHERE sites.cust_account_id = accounts.cust_account_id
  AND party_sites.party_site_id = sites.party_site_id
  AND site_uses.cust_acct_site_id = sites.cust_acct_site_id
  AND locations.location_id = party_sites.location_id
  AND site_uses.STATUS = 'A'
  AND accounts.STATUS = 'A'
  AND sites.STATUS = 'A'
  AND locations.COUNTRY = territory.territory_code
  AND EXISTS (SELECT 1
              FROM fusion.fnd_setid_assignments
              WHERE set_id = sites.set_id
                AND reference_group_name = 'HZ_CUSTOMER_ACCOUNT_SITE'
                AND determinant_type = 'BU')
  AND upper(accounts.account_name) LIKE upper('&CUSTOMER_NAME%');