[Home] [Help]
View: ZX_LOC_ASSIGNMENTS_V
View Text
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')