[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
sql_string := 'select R.REGION_ID,
R.REGION_TYPE,
TL.COUNTRY,
TL.COUNTRY_REGION,
TL.STATE,
TL.CITY,
TL.POSTAL_CODE_FROM,
TL.POSTAL_CODE_TO,
TL.ZONE,
R.ZONE_LEVEL,
R.COUNTRY_CODE,
R.COUNTRY_REGION_CODE,
R.STATE_CODE,
R.CITY_CODE,
''N'' ';
WSH_DEBUG_SV.logmsg(l_module_name, ' inserting into region table at '||cnt);
sql_string := 'select R.REGION_ID';
p_insert_flag IN VARCHAR2) RETURN NUMBER IS
l_region_info region_rec;
WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',P_INSERT_FLAG);
IF (p_insert_flag = 'Y') THEN
Get_Region_Info (
p_country => l_region_info.country,
p_country_region => l_region_info.country_region,
p_state => l_region_info.state,
p_city => l_region_info.city,
p_postal_code_from => l_region_info.postal_code_from,
p_postal_code_to => l_region_info.postal_code_to,
p_zone => null,
--p_lang_code => null,
p_lang_code => USERENV('LANG'),
p_country_code => l_region_info.country_code,
p_country_region_code => l_region_info.country_region_code,
p_state_code => l_region_info.state_code,
p_city_code => l_region_info.city_code,
p_region_type => l_region_type,
p_interface_flag => 'Y',
x_region_info => l_region_rec2);
IF (l_region_rec1.region_id = -1 AND p_insert_flag <> 'Y') THEN
IF l_debug_on THEN
wsh_debug_sv.logmsg(l_module_name,nvl(l_region_info.country,l_region_info.country_code)
||' '
||nvl(l_region_info.state,l_region_info.state_code)
||' '
||nvl(l_region_info.city,l_region_info.city_code)
||' '
||l_region_info.postal_code_from);
p_insert_flag IN VARCHAR2) IS
CURSOR get_region_info(l_processing_date DATE) IS
SELECT nvl(country, country_code) country,
nvl(state,state_code) state,
nvl(city,city_code) city,
postal_code_from
FROM wsh_regions_interface r, wsh_regions_tl_interface t
WHERE r.region_id = t.region_id and r.creation_date >= l_processing_date
ORDER BY country, state, city, postal_code_from;
SELECT t.territory_short_name, t.territory_code, nvl(l.state, l.province) state, l.city, l.postal_code
FROM hz_locations l, fnd_territories_tl t
WHERE t.territory_code = l.country and
t.language = userenv('LANG') and
l.last_update_date >= nvl(l_start_date, l.last_update_date) and
l.last_update_date < nvl(l_end_date, l.last_update_date+1);
IF (p_insert_flag = 'Y') THEN
IF l_debug_on THEN
wsh_debug_sv.logmsg(l_module_name,'These have been inserted in the regions interface tables');
p_insert_flag => p_insert_flag);
IF (p_insert_flag = 'Y') THEN
IF l_debug_on THEN
wsh_debug_sv.logmsg(l_module_name,'Total number of interfaced regions '||l_count);
p_insert_flag => FALSE,
x_return_status => l_return_status,
x_sqlcode => l_sqlcode,
x_sqlerr => l_sqlerr);
SELECT request_id
FROM FND_CONCURRENT_REQUESTS
WHERE parent_request_id = p_parent_request_id
AND NVL(is_sub_request, 'N') = 'Y';
p_insert_flag IN VARCHAR2,
p_start_date IN VARCHAR2,
p_end_date IN VARCHAR2) IS
l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'PROCESS_ALL_LOCATIONS';
WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',p_insert_flag);
p_insert_flag => p_insert_flag,
p_location_type => 'BOTH',
p_start_date => p_start_date,
p_end_date => p_end_date,
p_num_of_instances => p_num_of_instances
);
p_insert_flag IN VARCHAR2,
p_location_type IN VARCHAR2,
p_start_date IN VARCHAR2,
p_end_date IN VARCHAR2
) IS
--
l_debug_on BOOLEAN;
WSH_DEBUG_SV.log(l_module_name,'P_INSERT_FLAG',P_INSERT_FLAG);
p_insert_flag => p_insert_flag);
EXECUTE IMMEDIATE 'SELECT MIN(LOCATION_ID),MAX(LOCATION_ID)
FROM ( SELECT location_id, NTILE(:num_instances) OVER (ORDER BY location_id) worker
FROM HZ_LOCATIONS
WHERE last_update_date >= nvl(:start_date, last_update_date)
AND last_update_date < nvl(:end_date, last_update_date+1)
)
GROUP BY WORKER'
BULK COLLECT INTO l_worker_min_tab, l_worker_max_tab
USING l_num_of_instances, l_start_date, l_end_date;
EXECUTE IMMEDIATE 'SELECT MIN(LOCATION_ID),MAX(LOCATION_ID)
FROM ( SELECT location_id, NTILE(:num_instances) OVER (ORDER BY location_id) worker
FROM HR_LOCATIONS_ALL
WHERE last_update_date >= nvl(:start_date, last_update_date)
AND last_update_date < nvl(:end_date, last_update_date+1)
)
GROUP BY WORKER'
BULK COLLECT INTO l_worker_min_tab, l_worker_max_tab
USING l_num_of_instances, l_start_date, l_end_date;
EXECUTE IMMEDIATE 'SELECT MIN(LOCATION_ID),MAX(LOCATION_ID)
FROM ( SELECT location_id, NTILE(:num_instances) OVER (ORDER BY location_id) worker
FROM WSH_HR_LOCATIONS_V
WHERE last_update_date >= nvl(:start_date, last_update_date)
AND last_update_date < nvl(:end_date, last_update_date+1)
)
GROUP BY WORKER'
BULK COLLECT INTO l_worker_min_tab, l_worker_max_tab
USING l_num_of_instances, l_start_date, l_end_date;
argument7 => p_insert_flag);
p_insert_flag => p_insert_flag);
SELECT wr.region_id,
wr.region_type,
wrt.country,
wrt.country_region,
wrt.state,
wrt.city,
wrt.postal_code_from,
wrt.postal_code_to,
wrt.zone,
wr.zone_level,
wr.country_code,
wr.country_region_code,
wr.state_code,
wr.city_code,
decode(loc.parent_region_flag,'Y','N','N','Y')
FROM wsh_regions wr,
wsh_regions_tl wrt,
wsh_region_locations loc
WHERE wr.region_id = l_region_id AND
loc.location_id = l_location_id AND
wr.region_id = loc.region_id AND
wrt.region_id = wr.region_id AND
wrt.language = nvl(l_lang_code,wrt.language);
SELECT distinct r.region_id,
r.region_type,
null,
null,
null,
null,
null,
null,
rt.zone,
r.zone_level,
null,
null,
null,
null,
'N'
FROM wsh_regions r,
wsh_regions_tl rt,
wsh_zone_regions z
WHERE r.region_id = z.parent_region_id AND
z.region_id = l_region_id AND
r.region_type = 10 AND
rt.region_id = r.region_id AND
rt.language = nvl(l_lang_code,rt.language);
SELECT 'exists'
FROM wsh_region_locations wrl, wsh_regions_tl wrt,
wsh_regions wr
WHERE wrl.region_id is not null
AND wrl.location_id = c_location_id
AND wrl.region_id = wrt.region_id
AND wrt.language = nvl(c_lang_code,wrt.language)
AND wrt.region_id = wr.region_id
AND rownum = 1;
SELECT country, nvl(region_2, region_3), town_or_city, postal_code
FROM wsh_hr_locations_v
WHERE location_id = p_location_id;
SELECT country, nvl(state, province), city, postal_code
FROM wsh_locations
WHERE wsh_location_id = c_location_id;
SELECT wrl.region_id
BULK COLLECT INTO l_rgid_tab
FROM wsh_region_locations wrl,wsh_regions_tl wrt,wsh_regions wr
WHERE wrl.location_id = p_location_id
AND wrl.region_id is not null
AND wrl.region_id = wrt.region_id
AND wrt.language = nvl(p_lang_code,wrt.language)
AND wrt.region_id = wr.region_id
ORDER BY wrl.region_type DESC;
WSH_DEBUG_SV.logmsg(l_module_name, ' inserting into region table at '||cnt_region);
WSH_DEBUG_SV.logmsg(l_module_name, ' inserting into region table at '||cnt_region);
WSH_DEBUG_SV.logmsg(l_module_name, ' inserting into region table at '||cnt_region);
WSH_DEBUG_SV.logmsg(l_module_name, ' inserting into region table at '||cnt_region);
SELECT wrl.region_id
FROM wsh_region_locations wrl,wsh_regions_tl wrt,wsh_regions wr
WHERE wrl.location_id = l_location_id
AND wrl.region_id is not null
AND wrl.region_id = wrt.region_id
AND wrt.language = nvl(l_lang_code,wrt.language)
AND wrt.region_id = wr.region_id
ORDER BY wrl.region_type DESC;
SELECT wr.region_id
FROM wsh_zone_regions wzr,wsh_regions wr
WHERE wzr.region_id = c_region_id
AND zone_flag = 'Y'
AND wzr.parent_region_id = wr.region_id
AND wr.region_type=10;
SELECT wrl.region_id,
wr.region_type,
wr.deconsol_location_id
FROM wsh_region_locations wrl,wsh_regions_tl wrt,wsh_regions wr
WHERE wrl.location_id = c_location_id
AND wrl.region_id IS NOT NULL
AND wrl.region_id = wrt.region_id
AND wrt.language = nvl('US',wrt.language)
AND wrt.region_id = wr.region_id
--AND wr.deconsol_location_id IS NOT NULL
ORDER BY wr.region_type DESC;
SELECT wr.region_id,
--null,
wr.region_type,
wr.deconsol_location_id
FROM wsh_zone_regions wzr,wsh_regions wr
WHERE wzr.region_id = c_region_id
AND zone_flag = 'Y'
AND wzr.parent_region_id = wr.region_id
AND wr.region_type=10
AND wr.deconsol_location_id IS NOT NULL
ORDER BY wr.region_type DESC;
SELECT REGION_ID
FROM WSH_REGIONS_GLOBAL_DATA
WHERE ( city = UPPER(p_city) or p_city is null )
AND ( state = UPPER(p_state) or p_state is null )
AND country = UPPER(p_country)
AND ( p_postal_code_from is null
OR ( ( p_postal_code_from between postal_code_from and postal_code_to )
OR ( p_postal_code_to between postal_code_from and postal_code_to ) ) )
AND region_type = p_region_type
AND language = p_lang_code;
SELECT REGION_ID
FROM WSH_REGIONS_GLOBAL_DATA
WHERE ( city = UPPER(p_city) )
AND ( nvl(state, UPPER(p_state)) = UPPER(p_state) )
AND country = UPPER(p_country)
AND ( p_postal_code_from is null
OR ( ( p_postal_code_from between postal_code_from and postal_code_to )
OR ( p_postal_code_to between postal_code_from and postal_code_to ) ) )
AND region_type = p_region_type
AND language = p_lang_code;
SELECT REGION_ID
FROM WSH_REGIONS_GLOBAL_DATA
WHERE ( city = UPPER(p_city) or p_city is null )
AND ( decode(p_city, null, state, nvl(state, UPPER(p_state))) = UPPER(p_state)
OR ( p_state is null ) )
AND country = UPPER(p_country)
AND ( city_code = UPPER(p_city_code) or p_city_code is null )
AND ( decode(p_city_code, null, state_code, nvl(state_code, UPPER(p_state_code))) = UPPER(p_state_code)
OR ( p_state_code is null ) )
AND country_code = UPPER(p_country_code)
AND region_type = p_region_type
AND language = p_lang_code;
SELECT REGION_ID
FROM WSH_REGIONS_GLOBAL_DATA
WHERE ( city_code = UPPER(p_city_code) or p_city_code is null )
AND ( decode(p_city_code, null, state_code, nvl(state_code, UPPER(p_state_code))) = UPPER(p_state_code)
OR ( p_state_code is null ) )
AND country_code = UPPER(p_country_code)
AND ( p_postal_code_from is null
OR ( ( p_postal_code_from between postal_code_from and postal_code_to )
OR ( p_postal_code_to between postal_code_from and postal_code_to ) ) )
AND region_type = p_region_type
AND language = nvl(p_language_code, language);
SELECT REGION_ID
FROM WSH_REGIONS_GLOBAL
WHERE ( city_code = UPPER(p_city_code) or p_city_code is null )
AND ( decode(p_city_code, null, state_code, nvl(state_code, UPPER(p_state_code))) = UPPER(p_state_code)
OR ( p_state_code is null ) )
AND country_code = UPPER(p_country_code)
AND region_type = p_region_type;