Query to find legal entity,registration and address details

Query to find legal entity details

SELECT xep.name,                                          -- Legal Entity Name
       xjv.registration_code_le registration_code,        -- Registration Code
       xr.registration_number,                          -- Registration Number
          DECODE (hl.address_line_1, NULL, '', hl.address_line_1 || ', ')
       || DECODE (hl.address_line_2, NULL, '', hl.address_line_2 || ', ')
       || DECODE (hl.address_line_3, NULL, '', hl.address_line_3 || ', ')
       || DECODE (hl.town_or_city, NULL, '', hl.town_or_city || ', ')
       || DECODE (hl.region_2, NULL, '', hl.region_2 || ', ')
       || DECODE (hl.postal_code, NULL, '', hl.postal_code || ', ')
       || DECODE (ftv.territory_short_name,
                  NULL, '',
                  ftv.territory_short_name)
          Address --Address
  FROM xle_registrations xr,
       xle_jurisdictions_vl xjv,
       hr_locations hl,
       fnd_territories_vl ftv,
       xle_entity_profiles xep
 WHERE     1 = 1
       AND xep.legal_entity_id = :legal_entity_id
       AND xr.source_table = 'XLE_ENTITY_PROFILES'
       AND xr.source_id = xep.legal_entity_id
       AND xjv.jurisdiction_id = xr.jurisdiction_id
       AND hl.location_id = xr.location_id
       AND ftv.territory_code = hl.country
       AND SYSDATE BETWEEN NVL (xr.effective_from, SYSDATE - 1)
                       AND NVL (xr.EFFECTIVE_TO, SYSDATE + 1);