Fusion–> Query to find registered address of a Legal Entity

SELECT   xep.NAME,   — Legal Entity Name
          xjv.registration_code_le registration_code,
          xr.registration_number,
          hl.address1
          ,hl.address2
          ,hl.address3
          ,hl.address4
          ,hl.city
          ,hl.postal_code
          ,hl.state
          ,hl.province
          ,hl.country
          ,hl.county
          ,ftv.territory_short_name
          ,ftv.description
    FROM xle_registrations xr,
         xle_jurisdictions_vl xjv,
         hz_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)
ORDER BY xep.NAME
  • March 4, 2019 | 24 views
  • Comments