[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;
p_update_address => 'F'
);
update_geometry (l_task_rec.location_id);
update_geometry (l_task_rec.location_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
);
l_query := 'SELECT t.task_id,
t.task_number,
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,
tl.territory_short_name country,
l.country country_code,
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_sdm_ctry_profiles' || p_dataset || ' cp,
csf_spatial_ctry_mappings cm
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 cp.country_code_a3 = cm.spatial_country_code
and cm.hr_country_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(:1) AND Trunc(:2) + .99998';
SELECT t.task_id,
t.task_number,
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,
tl.territory_short_name country,
l.country country_code,
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_sdm_ctry_profiles' || p_dataset || ' cp,
csf_spatial_ctry_mappings cm
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 cp.country_code_a3 = cm.spatial_country_code
and cm.hr_country_code = l.country
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(:3) AND Trunc(:4) + .99998';
l_query := 'SELECT t.task_id,
t.task_number,
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,
tl.territory_short_name country,
l.country country_code,
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_odf_ctry_mapping coc
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 tl.territory_code = coc.country_code_2
AND t.planned_start_date BETWEEN Trunc(:1) AND Trunc(:2) + .99998';
SELECT t.task_id,
t.task_number,
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,
tl.territory_short_name country,
l.country country_code,
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_odf_ctry_mapping coc
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 tl.territory_code = coc.country_code_2
AND t.planned_start_date BETWEEN Trunc(:3) AND Trunc(:4) + .99998';
select distinct spatial_dataset from csf_spatial_ctry_mappings;
--If Single dataset, select tasks which belongs to that dataset
--If Multi dataset, select tasks which belongs to datasets listed in csf_spatial_ctry_mappings
if( l_mds_enabled = 'N' or l_mds_enabled is null or l_geo_coder_url is not null)
then
form_task_address_query('',
p_start_date,
p_end_date,
i,
x_task_rec_tbl,
task_address_cursor,
p_unstamped_only);
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,spatial_dataset
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 ('''||REPLACE(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, spatial_dataset
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';
p_update_address => 'T'
);
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 spatial_dataset from csf_spatial_ctry_mappings
WHERE spatial_country_name = ''' || upper(p_country)|| '''';
sql_stmt_str := 'SELECT PLACE_ID, COUNTRY_CODE_A3 FROM csf_sdm_ctry_profiles'||l_data_set_name||'
WHERE country_name = ''' || upper(p_country)|| '''';
query_str := 'SELECT country_name,country_code_2,area_id FROM '||owner_name||'.GC_COUNTRY_PROFILE';
select count(*) into obj_count from all_objects where owner=owner_name;
delete from csf_odf_ctry_mapping where dataset=data_set;
insert into csf_odf_ctry_mapping (user_name,country_name,country_code_2,dataset,area_id) values (owner_name,country_name,country_code_2,data_set,area_id);
put_stream (g_output, 'Inserting the country :"'||country_name||'" with UserName:'||owner_name);
put_stream (g_log, 'Inserting the country :"'||country_name||'" with UserName:'||owner_name);
select count(*) into obj_count from csf_odf_ctry_mapping where dataset=data_set;
put_stream (g_output, obj_count || 'rows has been inserted into csf_odf_ctry_mapping table');
put_stream (g_log, obj_count || 'rows has been inserted into csf_odf_ctry_mapping table');
sql_stmt_str := 'select user_name from csf_odf_ctry_mapping where country_code_2 = '''||l_ctry_code||'''';
sql_stmt_str := 'select REGION_LEVEL,MUNICIPALITY_LEVEL,SETTLEMENT_LEVEL from '||l_user_name||'.gc_country_profile where area_id = '||l_place_id||'';
sql_stmt_str := 'select user_name from csf_odf_ctry_mapping where country_name = ''' ||l_country_name|| '''';
sql_stmt_str := 'select area_id,country_code_2,lang_code_1 from '||l_user_name||'.gc_country_profile where country_name = ''' ||p_country|| '''';
sql_stmt_str := 'select spatial_dataset from csf_spatial_ctry_mappings
WHERE spatial_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';