[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
PARTY_TYPE_CODE,
upper(PARTY_SOURCE_TABLE) PARTY_SOURCE_TABLE,
upper(PARTY_SOURCE_COLUMN)PARTY_SOURCE_COLUMN,
APPLICABLE_TO_EVNT_CLS_FLAG,
PARTY_SITE_TYPE,
upper(LOCATION_SOURCE_TABLE) LOCATION_SOURCE_TABLE,
upper(LOCATION_SOURCE_COLUMN) LOCATION_SOURCE_COLUMN
FROM
ZX_PARTY_TYPES;
SELECT 'TRUE'
INTO l_hr_location_exists
FROM hr_locations_all
WHERE location_id = p_location_id;
SELECT 'TRUE'
INTO l_hz_location_exists
FROM hz_locations
WHERE location_id = p_location_id;
SELECT 'TRUE'
INTO l_hz_location_exists
FROM hz_locations
WHERE location_id = p_location_id;
SELECT 'TRUE'
INTO l_hr_location_exists
FROM hr_locations_all
WHERE location_id = p_location_id;
SELECT country
INTO l_loc_country_code
FROM hr_locations_all
WHERE location_id = p_location_id;
SELECT country
INTO l_loc_country_code
FROM hz_locations
WHERE location_id = p_location_id;
SELECT country
INTO l_loc_country_code
FROM hz_locations
WHERE location_id = p_location_id;
SELECT country
INTO l_loc_country_code
FROM hr_locations_all
WHERE location_id = p_location_id;
SELECT tax_jurisdiction_id,
tax_jurisdiction_code,
tax_regime_code,
tax,
precedence_level
FROM zx_jurisdictions_b
WHERE effective_from <= c_date
AND (effective_to >= c_date or effective_to is null)
AND tax = c_tax
AND tax_regime_code = c_tax_regime_code
AND zone_geography_id = c_geography_id
AND (((inner_city_jurisdiction_flag = c_inner_city_jurisdiction_flag) OR
(inner_city_jurisdiction_flag IS NULL AND
c_inner_city_jurisdiction_flag IS NULL)) OR
(inner_city_jurisdiction_flag is null and
c_inner_city_jurisdiction_flag is not null) OR
(inner_city_jurisdiction_flag is not null and c_inner_city_jurisdiction_flag is null));
SELECT geography_type,
geography_use,
limited_by_geography_id
FROM hz_geography_types_b
WHERE geography_type = c_geography_type;
SELECT country_code
INTO l_zone_type_country_code
FROM hz_geographies
WHERE geography_id = l_limited_by_geography_id;
SELECT 1 into l_count_geo
FROM hz_geo_name_references
WHERE location_id = p_location_id
AND rownum = 1;
l_jurisdiction_rec_tmp_tbl.delete;
SELECT country
INTO l_loc_country_code
FROM hz_locations
WHERE location_id = p_location_id;
SELECT country
INTO l_loc_country_code
FROM hr_locations_all
WHERE location_id = p_location_id;
SELECT geography_type, geography_use, geography_type_num
BULK COLLECT INTO l_geography_type, l_geography_use, l_geography_type_num
FROM
(SELECT gt.geography_type geography_type, gt.geography_use geography_use, 1 geography_type_num
FROM hz_geography_types_b gt
WHERE l_tax_rec.zone_geography_type = gt.geography_type
UNION
SELECT gt.geography_type geography_type, gt.geography_use geography_use, 2 geography_type_num
FROM hz_geography_types_b gt
WHERE l_tax_rec.override_geography_type = gt.geography_type
UNION
SELECT rt.object_type geography_type,
gt.geography_use geography_use,
2+rownum geography_type_num
FROM hz_relationship_types rt,
hz_geography_types_b gt
WHERE l_tax_rec.override_geography_type = rt.subject_type
AND rt.object_type = gt.geography_type)
ORDER BY 3 desc;
INSERT INTO zx_jurisdictions_gt
(TAX_JURISDICTION_ID,
TAX_JURISDICTION_CODE,
TAX_REGIME_CODE,
TAX,
PRECEDENCE_LEVEL)
SELECT
l_jurisdiction_rec_tmp_tbl(n).tax_jurisdiction_id,
l_jurisdiction_rec_tmp_tbl(n).tax_jurisdiction_code,
l_jurisdiction_rec_tmp_tbl(n).tax_regime_code,
l_jurisdiction_rec_tmp_tbl(n).tax,
l_jurisdiction_rec_tmp_tbl(n).precedence_level
FROM dual
WHERE NOT EXISTS (SELECT '1'
FROM zx_jurisdictions_gt
WHERE tax_jurisdiction_id = l_jurisdiction_rec_tmp_tbl(n).tax_jurisdiction_id);
l_log_msg := 'R: Inserted into zx_jurisdictions_gt table)' ;
SELECT count(*)
INTO l_count
FROM hz_geography_types_b
WHERE geography_type = p_geography_type
AND geography_use = 'MASTER_REF';
SELECT gnr.geography_id, geo.geography_code, geo.geography_name, geo.geography_use
INTO x_geography_id, x_geography_code, x_geography_name, l_geography_use
FROM hz_geo_name_references gnr, hz_geographies geo
WHERE gnr.location_table_name = x_location_table_name
AND gnr.location_id = p_location_id
AND gnr.geography_type = p_geography_type
AND geo.geography_id = gnr.geography_id;
SELECT geography_id, geography_code, geography_name
INTO x_geography_id, x_geography_code, x_geography_name
FROM hz_geographies
WHERE geography_code = l_country_code
AND geography_type = 'COUNTRY';
SELECT gnr.geography_id, gnr.geography_type, g.geography_name
BULK COLLECT INTO l_geography_id, l_geography_type, l_geography_name
FROM hz_geo_name_references gnr, hz_geographies g
WHERE location_table_name = x_loc_tbl
AND location_id = location_id_tbl(i)
AND gnr.geography_id = g.geography_id
ORDER BY gnr.geography_id;