VIEW: APPS.ZX_LOC_ASSIGNMENTS_V

Source

View Text - Preformatted

SELECT distinct hzloc.location_id location_id,
         acct_site.org_id org_id,
         to_number(null) loc_id,
         hzloc.CREATED_BY,
         hzloc.CREATION_DATE,
         hzloc.LAST_UPDATE_LOGIN,
         hzloc.LAST_UPDATE_DATE,
         hzloc.LAST_UPDATED_BY,
         hzloc.REQUEST_ID,
         hzloc.PROGRAM_APPLICATION_ID,
         hzloc.PROGRAM_ID ,
         hzloc.PROGRAM_UPDATE_DATE,
         hzloc.WH_UPDATE_DATE ,
         hzloc.OBJECT_VERSION_NUMBER,
         hzloc.CREATED_BY_MODULE ,
         hzloc.APPLICATION_ID
  FROM  hz_locations hzloc, hz_cust_acct_sites_all acct_site,
        hz_party_sites p_site, hz_geo_struct_map map,
hz_geo_name_reference_log log
  WHERE acct_site.party_site_id = p_site.party_site_id
  AND   p_site.location_id = hzloc.location_id
  AND   hzloc.location_id = log.location_id
  AND   log.location_table_name = 'HZ_LOCATIONS'
  AND   log.usage_code = 'TAX'
  AND   log.map_status = 'S'
  AND   hzloc.country = map.country_code
  AND   map.loc_tbl_name = 'HZ_LOCATIONS'
 UNION
  SELECT distinct hzloc.location_id location_id,
         acct_site.org_id org_id,
         to_number(null) loc_id,
         hzloc.CREATED_BY,
         hzloc.CREATION_DATE,
         hzloc.LAST_UPDATE_LOGIN,
         hzloc.LAST_UPDATE_DATE,
         hzloc.LAST_UPDATED_BY,
         hzloc.REQUEST_ID,
         hzloc.PROGRAM_APPLICATION_ID,
         hzloc.PROGRAM_ID ,
         hzloc.PROGRAM_UPDATE_DATE,
         hzloc.WH_UPDATE_DATE ,
         hzloc.OBJECT_VERSION_NUMBER,
         hzloc.CREATED_BY_MODULE ,
         hzloc.APPLICATION_ID
  FROM  hz_locations hzloc, hz_cust_acct_sites_all acct_site,
        hz_party_sites p_site, hz_geographies g
  WHERE acct_site.party_site_id = p_site.party_site_id
  AND   p_site.location_id = hzloc.location_id
  AND   hzloc.country = g.country_code
  AND   g.geography_type = 'COUNTRY'
  AND   NOT EXISTS (SELECT '1'
                    FROM hz_geo_struct_map map
                    WHERE map.country_code = hzloc.country
                    AND   map.loc_tbl_name = 'HZ_LOCATIONS')

   

View Text - HTML Formatted

SELECT DISTINCT HZLOC.LOCATION_ID LOCATION_ID
, ACCT_SITE.ORG_ID ORG_ID
, TO_NUMBER(NULL) LOC_ID
, HZLOC.CREATED_BY
, HZLOC.CREATION_DATE
, HZLOC.LAST_UPDATE_LOGIN
, HZLOC.LAST_UPDATE_DATE
, HZLOC.LAST_UPDATED_BY
, HZLOC.REQUEST_ID
, HZLOC.PROGRAM_APPLICATION_ID
, HZLOC.PROGRAM_ID
, HZLOC.PROGRAM_UPDATE_DATE
, HZLOC.WH_UPDATE_DATE
, HZLOC.OBJECT_VERSION_NUMBER
, HZLOC.CREATED_BY_MODULE
, HZLOC.APPLICATION_ID
FROM HZ_LOCATIONS HZLOC
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
, HZ_PARTY_SITES P_SITE
, HZ_GEO_STRUCT_MAP MAP
, HZ_GEO_NAME_REFERENCE_LOG LOG
WHERE ACCT_SITE.PARTY_SITE_ID = P_SITE.PARTY_SITE_ID
AND P_SITE.LOCATION_ID = HZLOC.LOCATION_ID
AND HZLOC.LOCATION_ID = LOG.LOCATION_ID
AND LOG.LOCATION_TABLE_NAME = 'HZ_LOCATIONS'
AND LOG.USAGE_CODE = 'TAX'
AND LOG.MAP_STATUS = 'S'
AND HZLOC.COUNTRY = MAP.COUNTRY_CODE
AND MAP.LOC_TBL_NAME = 'HZ_LOCATIONS' UNION SELECT DISTINCT HZLOC.LOCATION_ID LOCATION_ID
, ACCT_SITE.ORG_ID ORG_ID
, TO_NUMBER(NULL) LOC_ID
, HZLOC.CREATED_BY
, HZLOC.CREATION_DATE
, HZLOC.LAST_UPDATE_LOGIN
, HZLOC.LAST_UPDATE_DATE
, HZLOC.LAST_UPDATED_BY
, HZLOC.REQUEST_ID
, HZLOC.PROGRAM_APPLICATION_ID
, HZLOC.PROGRAM_ID
, HZLOC.PROGRAM_UPDATE_DATE
, HZLOC.WH_UPDATE_DATE
, HZLOC.OBJECT_VERSION_NUMBER
, HZLOC.CREATED_BY_MODULE
, HZLOC.APPLICATION_ID
FROM HZ_LOCATIONS HZLOC
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
, HZ_PARTY_SITES P_SITE
, HZ_GEOGRAPHIES G
WHERE ACCT_SITE.PARTY_SITE_ID = P_SITE.PARTY_SITE_ID
AND P_SITE.LOCATION_ID = HZLOC.LOCATION_ID
AND HZLOC.COUNTRY = G.COUNTRY_CODE
AND G.GEOGRAPHY_TYPE = 'COUNTRY'
AND NOT EXISTS (SELECT '1'
FROM HZ_GEO_STRUCT_MAP MAP
WHERE MAP.COUNTRY_CODE = HZLOC.COUNTRY
AND MAP.LOC_TBL_NAME = 'HZ_LOCATIONS')