VIEW: APPS.ZX_LOC_ASSIGNMENTS_V

Source

View Text - Preformatted

SELECT DISTINCT hzloc.location_id location_id,
         acct_site.org_id AS 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
 WHERE acct_site.party_site_id = p_site.party_site_id
   AND p_site.location_id = hzloc.location_id
   AND DECODE ( NVL( (SELECT 1
                        FROM hz_geo_struct_map map
                       WHERE map.country_code = hzloc.country
                         AND map.loc_tbl_name = 'HZ_LOCATIONS'
                         AND rownum = 1
                      ), 0),
              1,
                 NVL( (SELECT 2
                         FROM hz_geo_name_reference_log log1
                        WHERE 2 = 2
                          AND log1.location_id = hzloc.location_id
                          AND log1.location_table_name = 'HZ_LOCATIONS'
                          AND log1.usage_code = 'TAX'
                          AND log1.map_status = 'S'
                          AND ROWNUM = 1
                       ), 0),
                  NVL( ( SELECT 2
                           FROM hz_geographies g
                          WHERE 3 = 3
                            AND g.country_code = hzloc.country
                            AND g.geography_type = 'COUNTRY'
                            AND ROWNUM = 1
                        ), 0)
             ) = 2

   

View Text - HTML Formatted

SELECT DISTINCT HZLOC.LOCATION_ID LOCATION_ID
, ACCT_SITE.ORG_ID AS 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
WHERE ACCT_SITE.PARTY_SITE_ID = P_SITE.PARTY_SITE_ID
AND P_SITE.LOCATION_ID = HZLOC.LOCATION_ID
AND DECODE ( NVL( (SELECT 1
FROM HZ_GEO_STRUCT_MAP MAP
WHERE MAP.COUNTRY_CODE = HZLOC.COUNTRY
AND MAP.LOC_TBL_NAME = 'HZ_LOCATIONS'
AND ROWNUM = 1 )
, 0)
, 1
, NVL( (SELECT 2
FROM HZ_GEO_NAME_REFERENCE_LOG LOG1
WHERE 2 = 2
AND LOG1.LOCATION_ID = HZLOC.LOCATION_ID
AND LOG1.LOCATION_TABLE_NAME = 'HZ_LOCATIONS'
AND LOG1.USAGE_CODE = 'TAX'
AND LOG1.MAP_STATUS = 'S'
AND ROWNUM = 1 )
, 0)
, NVL( ( SELECT 2
FROM HZ_GEOGRAPHIES G
WHERE 3 = 3
AND G.COUNTRY_CODE = HZLOC.COUNTRY
AND G.GEOGRAPHY_TYPE = 'COUNTRY'
AND ROWNUM = 1 )
, 0) ) = 2