[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_geometry (p_location_id IN NUMBER)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
UPDATE hz_locations
SET geometry = NULL
WHERE location_id = p_location_id;
SELECT NAME
FROM fnd_timezones_vl
WHERE timezone_code = p_timezone_id;
update_geometry (l_task_rec.location_id);
update_geometry (l_task_rec.location_id);
SELECT t.task_id,
t.task_number,
t.address_id,
l.location_id,
l.address_style,
l.address1,
l.address2,
l.address3,
l.address4,
l.city,
l.postal_code,
l.county,
l.state,
l.province,
l.country country_code,
tl.territory_short_name country,
l.timezone_id
FROM jtf_tasks_b t,
jtf_task_types_b tt,
hz_locations l,
fnd_territories_tl tl,
jtf_task_statuses_vl jts
WHERE tt.task_type_id = t.task_type_id
AND t.location_id is not null
AND l.location_id = t.location_id
AND tl.territory_code = l.country
AND tl.language = 'US'
AND t.source_object_type_code = 'SR'
AND t.deleted_flag <> 'Y'
AND tt.rule = 'DISPATCH'
AND t.task_status_id = jts.task_status_id
AND NVL (jts.schedulable_flag, 'N') = 'Y'
AND t.planned_start_date BETWEEN Trunc(p_start_date) AND Trunc(p_end_date) + .99998
UNION
SELECT t.task_id,
t.task_number,
t.address_id,
l.location_id,
l.address_style,
l.address1,
l.address2,
l.address3,
l.address4,
l.city,
l.postal_code,
l.county,
l.state,
l.province,
l.country country_code,
tl.territory_short_name country,
l.timezone_id
FROM jtf_tasks_b t,
jtf_task_types_b tt,
hz_party_sites hps,
hz_locations l,
fnd_territories_tl tl,
jtf_task_statuses_vl jts
WHERE tt.task_type_id = t.task_type_id
AND t.address_id is not null
AND t.address_id = hps.party_site_id
AND l.location_id = hps.location_id
AND tl.territory_code = l.country
AND tl.language = 'US'
AND t.source_object_type_code = 'SR'
AND t.deleted_flag <> 'Y'
AND tt.rule = 'DISPATCH'
AND t.task_status_id = jts.task_status_id
AND NVL (jts.schedulable_flag, 'N') = 'Y'
AND t.planned_start_date BETWEEN Trunc(p_start_date) AND Trunc(p_end_date) + .99998;
SELECT t.task_id,
t.task_number,
t.address_id,
l.location_id,
l.address_style,
l.address1,
l.address2,
l.address3,
l.address4,
l.city,
l.postal_code,
l.county,
l.state,
l.province,
l.country country_code,
tl.territory_short_name country,
l.timezone_id
FROM jtf_tasks_b t,
jtf_task_types_b tt,
hz_locations l,
fnd_territories_tl tl,
jtf_task_statuses_vl jts,
CSF_SPATIAL_CTRY_MAPPINGS sp
WHERE tt.task_type_id = t.task_type_id
AND t.location_id is not null
AND l.location_id = t.location_id
AND tl.territory_code = l.country
AND l.country = sp.hr_country_code
AND sp.spatial_dataset = fnd_profile.value('CSF_SPATIAL_DATASET_NAME')
AND tl.language = 'US'
AND t.source_object_type_code = 'SR'
AND t.deleted_flag <> 'Y'
AND tt.rule = 'DISPATCH'
AND t.task_status_id = jts.task_status_id
AND NVL (jts.schedulable_flag, 'N') = 'Y'
AND t.planned_start_date BETWEEN Trunc(p_start_date) AND Trunc(p_end_date) + .99998
UNION
SELECT t.task_id,
t.task_number,
t.address_id,
l.location_id,
l.address_style,
l.address1,
l.address2,
l.address3,
l.address4,
l.city,
l.postal_code,
l.county,
l.state,
l.province,
l.country country_code,
tl.territory_short_name country,
l.timezone_id
FROM jtf_tasks_b t,
jtf_task_types_b tt,
hz_party_sites hps,
hz_locations l,
fnd_territories_tl tl,
jtf_task_statuses_vl jts,
CSF_SPATIAL_CTRY_MAPPINGS sp
WHERE tt.task_type_id = t.task_type_id
AND t.address_id is not null
AND t.address_id = hps.party_site_id
AND l.location_id = hps.location_id
AND tl.territory_code = l.country
AND l.country = sp.hr_country_code
AND sp.spatial_dataset = fnd_profile.value('CSF_SPATIAL_DATASET_NAME')
AND tl.language = 'US'
AND t.source_object_type_code = 'SR'
AND t.deleted_flag <> 'Y'
AND tt.rule = 'DISPATCH'
AND t.task_status_id = jts.task_status_id
AND NVL (jts.schedulable_flag, 'N') = 'Y'
AND t.planned_start_date BETWEEN Trunc(p_start_date) AND Trunc(p_end_date) + .99998;
SELECT geometry
FROM hz_locations
WHERE location_id = l_location_id;
SELECT NAME
FROM csf_lf_places p, csf_lf_names n, csf_lf_place_names pn
WHERE n.name_id = pn.name_id
AND pn.place_id = p.place_id
AND NAME = l_place_name
AND p.place_parent_level = l_parent_level;
SELECT SPATIAL_COUNTRY_NAME
FROM CSF_SPATIAL_CTRY_MAPPINGS
WHERE HR_COUNTRY_NAME = p_task_rec.country;
l_uk_city :=' SELECT cln.NAME
FROM csf_lf_names'||l_dataset_profile_value||' cln,
csf_lf_place_names'||l_dataset_profile_value||' clpn,
csf_lf_places'||l_dataset_profile_value||' clp
WHERE cln.name_id = clpn.name_id
AND clpn.place_id = clp.place_id
AND clp.place_parent_level NOT IN (-1, 0)
AND UPPER (cln.NAME) = UPPER ('''||p_task_rec.city||''')';
l_place_country :=' SELECT n.NAME
FROM csf_lf_places'||l_dataset_profile_value||' p,
csf_lf_place_names'||l_dataset_profile_value||' pn,
csf_lf_names'||l_dataset_profile_value||' n
WHERE n.NAME = UPPER ('''||l_country||''')
AND pn.name_id = n.name_id
AND p.place_id = pn.place_id
AND p.place_parent_level = -1';
l_place_state :=' SELECT n.NAME
FROM csf_lf_names'||l_dataset_profile_value||' n,
csf_lf_place_names'||l_dataset_profile_value||' pn,
(SELECT place_id
FROM csf_lf_places'||l_dataset_profile_value||'
CONNECT BY PRIOR place_id = parent_place_id
START WITH place_id IN (
SELECT pn.place_id
FROM csf_lf_names'||l_dataset_profile_value||' n,
csf_lf_place_names'||l_dataset_profile_value||' pn
WHERE n.NAME = UPPER ('''|| l_country||''')
AND pn.name_id = n.name_id)) p
WHERE pn.place_id = p.place_id
AND n.name_id = pn.name_id
AND n.NAME = UPPER ('''||p_task_rec.state||''')';
l_place_city :=' SELECT n.NAME
FROM csf_lf_names'||l_dataset_profile_value||' n,
csf_lf_place_names'||l_dataset_profile_value||' pn,
(SELECT place_id
FROM csf_lf_places'||l_dataset_profile_value||'
WHERE place_parent_level IN (1, 8)
CONNECT BY PRIOR place_id = parent_place_id
START WITH place_id IN (
SELECT pn.place_id
FROM csf_lf_names'||l_dataset_profile_value||' n,
csf_lf_place_names'||l_dataset_profile_value||' pn
WHERE n.NAME = UPPER ('''||l_place||''')
AND pn.name_id = n.name_id)) p
WHERE pn.place_id = p.place_id
AND n.name_id = pn.name_id
AND n.NAME = UPPER ('''||p_task_rec.city||''')';
l_place_zip := 'SELECT pc.postal_code
FROM csf_lf_postcodes'||l_dataset_profile_value||' pc,
csf_lf_place_postcs'||l_dataset_profile_value||' ppc,
csf_lf_place_names'||l_dataset_profile_value||' pn,
csf_lf_names'||l_dataset_profile_value||' n
WHERE pc.postal_code_id = ppc.postal_code_id
AND ppc.place_id = pn.place_id
AND pn.name_id = n.name_id
AND pc.postal_code = '''||p_task_rec.postal_code||'''
AND EXISTS (
SELECT 1
FROM csf_lf_places'||l_dataset_profile_value||'
WHERE place_id IN (
SELECT clpn.place_id
FROM csf_lf_names'||l_dataset_profile_value||' cln,
csf_lf_place_names'||l_dataset_profile_value||' clpn
WHERE cln.NAME = '''||l_place||'''
AND cln.name_id = clpn.name_id)
CONNECT BY place_id = PRIOR parent_place_id
START WITH place_id = pn.place_id)';
SELECT task_id
FROM csf_ext_locations
WHERE task_id = l_task_id;
csf_locations_pkg.insert_row_ext
(p_csf_ext_location_id => l_location_id,
p_last_update_date => SYSDATE,
p_last_updated_by => NVL (fnd_global.user_id,
-1
),
p_creation_date => SYSDATE,
p_created_by => NVL (fnd_global.user_id,
-1
),
p_last_update_login => NVL
(fnd_global.conc_login_id,
-1
),
p_request_id => NVL
(fnd_global.conc_request_id,
-1
),
p_program_application_id => NVL
(fnd_global.prog_appl_id,
-1
),
p_program_id => NVL
(fnd_global.conc_program_id,
-1
),
p_program_update_date => SYSDATE,
p_task_id => p_task_rec.task_id,
p_location_id => p_task_rec.location_id,
p_validated_flag => p_task_rec.validated_flag,
p_override_flag => p_task_rec.override_flag,
p_log_detail_short => l_error,
p_log_detail_long => l_error_detail
);
csf_locations_pkg.update_row_ext
(p_csf_ext_location_id => p_task_rec.location_id,
p_last_update_date => SYSDATE,
p_last_updated_by => NVL (fnd_global.user_id,
-1
),
p_last_update_login => NVL
(fnd_global.conc_login_id,
-1
),
p_request_id => NVL
(fnd_global.conc_request_id,
-1
),
p_program_application_id => NVL
(fnd_global.prog_appl_id,
-1
),
p_program_id => NVL
(fnd_global.conc_program_id,
-1
),
p_program_update_date => SYSDATE,
p_location_id => p_task_rec.location_id,
p_validated_flag => p_task_rec.validated_flag,
p_override_flag => p_task_rec.override_flag,
p_log_detail_short => l_error,
p_log_detail_long => l_error_detail
);
SELECT NAME
FROM csf_lf_places p, csf_lf_names n, csf_lf_place_names pn
WHERE n.name_id = pn.name_id
AND pn.place_id = p.place_id
AND NAME = l_place_name
AND p.place_parent_level = l_parent_level;
SELECT SPATIAL_COUNTRY_NAME
FROM CSF_SPATIAL_CTRY_MAPPINGS
WHERE HR_COUNTRY_NAME = upper(p_country);
l_uk_city :=' SELECT cln.NAME
FROM csf_lf_names'||l_dataset_profile_value||' cln,
csf_lf_place_names'||l_dataset_profile_value||' clpn,
csf_lf_places'||l_dataset_profile_value||' clp
WHERE cln.name_id = clpn.name_id
AND clpn.place_id = clp.place_id
AND clp.place_parent_level NOT IN (-1, 0)
AND UPPER (cln.NAME) = UPPER ('''||p_city||''')';
l_place_country :=' SELECT n.NAME
FROM csf_lf_places'||l_dataset_profile_value||' p,
csf_lf_place_names'||l_dataset_profile_value||' pn,
csf_lf_names'||l_dataset_profile_value||' n
WHERE n.NAME = UPPER ('''||l_country||''')
AND pn.name_id = n.name_id
AND p.place_id = pn.place_id
AND p.place_parent_level = -1';
l_place_state :=' SELECT n.NAME
FROM csf_lf_names'||l_dataset_profile_value||' n,
csf_lf_place_names'||l_dataset_profile_value||' pn,
(SELECT place_id
FROM csf_lf_places'||l_dataset_profile_value||'
CONNECT BY PRIOR place_id = parent_place_id
START WITH place_id IN (
SELECT pn.place_id
FROM csf_lf_names'||l_dataset_profile_value||' n,
csf_lf_place_names'||l_dataset_profile_value||' pn
WHERE n.NAME = UPPER ('''|| l_country||''')
AND pn.name_id = n.name_id)) p
WHERE pn.place_id = p.place_id
AND n.name_id = pn.name_id
AND n.NAME = UPPER ('''||p_state||''')';
l_place_city := ' SELECT n.NAME
FROM csf_lf_names'||l_dataset_profile_value||' n,
csf_lf_place_names'||l_dataset_profile_value||' pn,
(SELECT place_id
FROM csf_lf_places'||l_dataset_profile_value||'
WHERE place_parent_level IN (1, 8)
CONNECT BY PRIOR place_id = parent_place_id
START WITH place_id IN (
SELECT pn.place_id
FROM csf_lf_names'||l_dataset_profile_value||' n,
csf_lf_place_names'||l_dataset_profile_value||' pn
WHERE n.NAME = UPPER ('''||l_place||''')
AND pn.name_id = n.name_id)) p
WHERE pn.place_id = p.place_id
AND n.name_id = pn.name_id
AND n.NAME = UPPER ('''||p_city||''')';
l_place_zip := 'SELECT pc.postal_code
FROM csf_lf_postcodes'||l_dataset_profile_value||' pc,
csf_lf_place_postcs'||l_dataset_profile_value||' ppc,
csf_lf_place_names'||l_dataset_profile_value||' pn,
csf_lf_names'||l_dataset_profile_value||' n
WHERE pc.postal_code_id = ppc.postal_code_id
AND ppc.place_id = pn.place_id
AND pn.name_id = n.name_id
AND pc.postal_code = '''||p_postal_code||'''
AND EXISTS (
SELECT 1
FROM csf_lf_places'||l_dataset_profile_value||'
WHERE place_id IN (
SELECT clpn.place_id
FROM csf_lf_names'||l_dataset_profile_value||' cln,
csf_lf_place_names'||l_dataset_profile_value||' clpn
WHERE cln.NAME = UPPER('''||l_place||''')
AND cln.name_id = clpn.name_id)
CONNECT BY place_id = PRIOR parent_place_id
START WITH place_id = pn.place_id)';
SELECT NAME
FROM hz_timezones_vl
WHERE timezone_id = p_timezone_id;
SELECT ftt.territory_code country_code
FROM fnd_territories_tl ftt
WHERE UPPER (ftt.territory_short_name) = UPPER (p_country)
AND ftt.language = 'US';
SELECT task_id
FROM csf_validate_tasks_v
WHERE task_id = l_task_id AND validated_flag = 'N';
PROCEDURE update_task_address (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
p_location_id IN hz_locations.location_id%TYPE,
p_address1 IN hz_locations.address1%TYPE,
p_address2 IN hz_locations.address2%TYPE,
p_address3 IN hz_locations.address3%TYPE,
p_address4 IN hz_locations.address4%TYPE,
p_city IN hz_locations.city%TYPE,
p_postal_code IN hz_locations.postal_code%TYPE,
p_state IN hz_locations.state%TYPE,
p_province IN hz_locations.province%TYPE,
p_county IN hz_locations.county%TYPE,
p_country IN hz_locations.country%TYPE,
p_validated_flag IN csf_ext_locations.validated_flag%TYPE,
p_override_flag IN csf_ext_locations.override_flag%TYPE,
p_timezone_id IN hz_locations.timezone_id%TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_TASK_ADDRESS';
SELECT ftt.territory_code country_code
FROM fnd_territories_tl ftt
WHERE UPPER (ftt.territory_short_name) = UPPER (p_country)
AND ftt.language = 'US';
csf_locations_pkg.update_row_hz
(p_last_update_date => SYSDATE,
p_last_updated_by => NVL (fnd_global.user_id,
-1
),
p_last_update_login => NVL
(fnd_global.conc_login_id,
-1
),
p_request_id => NVL
(fnd_global.conc_request_id,
-1
),
p_program_application_id => NVL
(fnd_global.prog_appl_id,
-1
),
p_program_id => NVL
(fnd_global.conc_program_id,
-1
),
p_program_update_date => SYSDATE,
p_address1 => p_address1,
p_address2 => p_address2,
p_address3 => p_address3,
p_address4 => p_address4,
p_city => p_city,
p_postal_code => p_postal_code,
p_county => p_county,
p_state => p_state,
p_province => p_province,
p_country => l_country_code,
p_validated_flag => p_validated_flag,
p_location_id => p_location_id,
p_timezone_id => p_timezone_id
);
csf_locations_pkg.update_row_ext
(p_csf_ext_location_id => p_location_id,
p_last_update_date => SYSDATE,
p_last_updated_by => NVL (fnd_global.user_id,
-1
),
p_last_update_login => NVL
(fnd_global.conc_login_id,
-1
),
p_request_id => NVL
(fnd_global.conc_request_id,
-1
),
p_program_application_id => NVL
(fnd_global.prog_appl_id,
-1
),
p_program_id => NVL
(fnd_global.conc_program_id,
-1
),
p_program_update_date => SYSDATE,
p_location_id => p_location_id,
p_validated_flag => p_validated_flag,
p_override_flag => p_override_flag,
p_log_detail_short => l_short_msg,
p_log_detail_long => l_long_msg
);
END update_task_address;
SELECT task_id
FROM csf_ext_locations
WHERE location_id = l_location_id
AND validated_flag = 'N'
AND override_flag = 'N';
csf_locations_pkg.update_row_ext
(p_csf_ext_location_id => p_task_rec.location_id,
p_last_update_date => SYSDATE,
p_last_updated_by => NVL (fnd_global.user_id,
-1
),
p_last_update_login => NVL
(fnd_global.conc_login_id,
-1
),
p_request_id => NVL
(fnd_global.conc_request_id,
-1
),
p_program_application_id => NVL
(fnd_global.prog_appl_id,
-1
),
p_program_id => NVL
(fnd_global.conc_program_id,
-1
),
p_program_update_date => SYSDATE,
p_location_id => p_task_rec.location_id,
p_validated_flag => p_task_rec.validated_flag,
p_override_flag => p_task_rec.override_flag,
p_log_detail_short => NULL,
p_log_detail_long => NULL
);
sql_stmt_str := 'SELECT PLACE_ID, COUNTRY_CODE_A3 FROM csf_sdm_ctry_profiles'||l_data_set_name||'
WHERE country_name = ''' || upper(p_country)|| '''';
sql_stmt_str := 'SELECT DISTINCT place_id
FROM csf_lf_places'||l_data_set_name||
' WHERE place_parent_level = -1 START WITH place_id IN
(SELECT pn.place_id
FROM csf_lf_names'||l_data_set_name|| ' n,
csf_lf_place_names'||l_data_set_name||' pn
WHERE n.name_id = pn.name_id
AND n.name = ''' || upper(p_city)|| ''' )
CONNECT BY PRIOR parent_place_id = place_id';