[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE(count(0),0,'N','Y')
INTO l_upgrade_needed
FROM pay_us_geo_update pugu,
pay_patch_status pps
WHERE pps.patch_name = 'USER_CITY_UPGRADE'
AND pps.phase = 'TO_BE_UPDATED'
AND pps.process_type = 'USER_CITY_UPGRADE'
AND pps.legislation_code = 'US'
AND pps.id = pugu.id
AND pugu.process_date = pps.applied_date
AND pugu.process_type = pps.process_type
AND pugu.process_mode = pps.phase;
SELECT pay_us_geo_upd_pkg.get_parameter('MODE',ppa.legislative_parameters)
INTO l_mode
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = pactid;
SELECT pay_patch_status_s.NEXTVAL
INTO l_id FROM dual;
INSERT INTO pay_patch_status
(id,
patch_number,
patch_name,
phase,
process_type,
applied_date,
status,
description,
legislation_code)
VALUES(l_id,
'1111111',
l_patch_name,
'UPDATED',
'USER_CITY_UPGRADE',
l_applied_date,
'P',
l_mode,
'US');
INSERT INTO pay_us_modified_geocodes
(city_name,
state_code,
county_code,
new_city_code,
old_city_code,
primary_flag,
process_type,
patch_name,
new_county_code)
SELECT substr(pugu.description,3),
substr(pugu.old_juri_code,1,2),
substr(pugu.old_juri_code,4,3),
substr(pugu.new_juri_code,8,4),
substr(pugu.old_juri_code,8,4),
'N',
'D',
l_patch_name,
NULL
FROM pay_us_geo_update pugu,
pay_patch_status pps
WHERE pps.patch_name = 'USER_CITY_UPGRADE'
AND pps.phase = 'TO_BE_UPDATED'
AND pps.process_type = 'USER_CITY_UPGRADE'
AND pps.legislation_code = 'US'
AND pps.id = pugu.id
AND pugu.process_date = pps.applied_date
AND pugu.process_type = pps.process_type
AND pugu.process_mode = pps.phase
AND NOT EXISTS
(SELECT NULL
FROM pay_us_modified_geocodes pumg
WHERE pumg.city_name = substr(pugu.description,3)
AND pumg.state_code = substr(pugu.old_juri_code,1,2)
AND pumg.county_code = substr(pugu.old_juri_code,4,3)
AND pumg.new_city_code = substr(pugu.new_juri_code,8,4)
AND pumg.old_city_code = substr(pugu.old_juri_code,8,4)
AND pumg.process_type = 'D'
AND pumg.patch_name = l_patch_name);
sqlstr := ' SELECT DISTINCT paf.person_id
FROM per_all_assignments_f paf,
pay_us_emp_city_tax_rules_f pect,
pay_us_geo_update pugu,
pay_patch_status pps
WHERE pect.assignment_id = paf.assignment_id
AND substr(pugu.old_juri_code,1,2) = pect.state_code
AND substr(pugu.old_juri_code,4,3) = pect.county_code
AND substr(pugu.old_juri_code,8,4) = pect.city_code
AND :pactid IS NOT NULL
AND pugu.id = pps.id
AND pugu.process_date = pps.applied_date
AND pugu.process_type = pps.process_type
AND pugu.process_mode = pps.phase
AND pps.patch_name = ''USER_CITY_UPGRADE''
AND pps.phase = ''TO_BE_UPDATED''
AND pps.legislation_code = ''US''
AND pps.process_type = ''USER_CITY_UPGRADE''
ORDER BY paf.person_id';
sqlstr := ' SELECT distinct paf.person_id
FROM per_all_assignments_f paf
WHERE 1=2
AND :pactid IS NOT NULL';
SELECT DISTINCT ectr.assignment_id
FROM per_all_assignments_f paf,
pay_us_emp_city_tax_rules_f ectr,
pay_us_geo_update pugu,
pay_patch_status pps
WHERE pps.patch_name = 'USER_CITY_UPGRADE'
AND pps.phase = 'TO_BE_UPDATED'
AND pps.process_type = 'USER_CITY_UPGRADE'
AND pps.legislation_code = 'US'
AND pps.id = pugu.id
AND pugu.process_date = pps.applied_date
AND pugu.process_type = pps.process_type
AND pugu.process_mode = pps.phase
AND ectr.state_code = substr(pugu.old_juri_code,1,2)
AND ectr.county_code = substr(pugu.old_juri_code,4,3)
AND ectr.city_code = substr(pugu.old_juri_code,8,4)
AND ectr.assignment_id = paf.assignment_id
AND paf.person_id BETWEEN p_stperson AND p_endperson
ORDER BY ectr.assignment_id;
SELECT pay_assignment_actions_s.nextval
INTO l_assignment_action_id
FROM DUAL;
SELECT paa.payroll_action_id,
paa.object_id
FROM pay_temp_object_actions paa
WHERE paa.object_action_id = p_assignment_action_id;
SELECT pps.id,pps.patch_name
FROM pay_patch_status pps
WHERE pps.patch_name LIKE 'USER_CITY_UPGRADE%'
AND pps.legislation_code = 'US'
AND pps.status = 'P'
AND pps.description = p_mode
AND NOT EXISTS
(SELECT NULL
FROM pay_patch_status pps1
WHERE pps1.id > pps.id
AND pps1.patch_name LIKE 'USER_CITY_UPGRADE%'
AND pps.legislation_code = 'US')
ORDER BY pps.id DESC;
SELECT pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_payroll_action_id;
SELECT COUNT(0)
FROM (SELECT DISTINCT assignment_id
FROM pay_us_geo_update pugu
WHERE pugu.id = p_id
AND pugu.process_type = p_process_type
AND pugu.process_mode = p_mode
AND pugu.table_name is null
AND pugu.table_value_id is null
AND DECODE(pugu.status,'C','C','E')
= DECODE(p_status,'ALL',DECODE(pugu.status,'C','C','E'),p_status));
SELECT pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_payroll_action_id;
CURSOR get_user_city_update_details IS
SELECT pugu.old_juri_code,
pugu.new_juri_code,
substr(pugu.description,3) city_name,
substr(pugu.description,1,1) primary_flag
FROM pay_us_geo_update pugu,
pay_patch_status pps
WHERE pps.patch_name = 'USER_CITY_UPGRADE'
AND pps.phase = 'TO_BE_UPDATED'
AND pps.process_type = 'USER_CITY_UPGRADE'
AND pps.legislation_code = 'US'
AND pps.id = pugu.id
AND pugu.process_date = pps.applied_date
AND pugu.process_type = pps.process_type
AND pugu.process_mode = pps.phase;
l_process_type pay_us_geo_update.process_type%TYPE;
l_assignments_to_be_updated NUMBER;
l_old_juri_code pay_us_geo_update.old_juri_code%TYPE;
l_new_juri_code pay_us_geo_update.new_juri_code%TYPE;
SELECT DECODE(count(0),0,'N','Y')
INTO l_upgrade_needed
FROM pay_us_geo_update pugu,
pay_patch_status pps
WHERE pps.patch_name = 'USER_CITY_UPGRADE'
AND pps.phase = 'TO_BE_UPDATED'
AND pps.process_type = 'USER_CITY_UPGRADE'
AND pps.legislation_code = 'US'
AND pps.id = pugu.id
AND pugu.process_date = pps.applied_date
AND pugu.process_type = pps.process_type
AND pugu.process_mode = pps.phase;
SELECT id,applied_date
INTO l_id,l_applied_date
FROM pay_patch_status pps
WHERE pps.patch_name LIKE 'USER_CITY_UPGRADE%'
AND pps.legislation_code = 'US'
AND pps.status = 'P'
AND pps.description = l_mode
AND NOT EXISTS
(SELECT NULL
FROM pay_patch_status pps1
WHERE pps1.id > pps.id
AND pps1.patch_name LIKE 'USER_CITY_UPGRADE%'
AND pps1.legislation_code = 'US');
UPDATE pay_patch_status
SET status = 'C'
WHERE legislation_code = 'US'
AND id = l_id
AND status = 'P'
AND description = l_mode;
SELECT count(DISTINCT paf.assignment_id)
INTO l_assignments_to_be_updated
FROM per_all_assignments_f paf,
pay_us_emp_city_tax_rules_f pect,
pay_us_geo_update pugu,
pay_patch_status pps
WHERE pect.assignment_id = paf.assignment_id
AND substr(pugu.old_juri_code,1,2) = pect.state_code
AND substr(pugu.old_juri_code,4,3) = pect.county_code
AND substr(pugu.old_juri_code,8,4) = pect.city_code
AND pugu.id = pps.id
AND pugu.process_date = pps.applied_date
AND pugu.process_type = pps.process_type
AND pugu.process_mode = pps.phase
AND pps.patch_name = 'USER_CITY_UPGRADE'
AND pps.phase = 'TO_BE_UPDATED'
AND pps.legislation_code = 'US'
AND pps.process_type = 'USER_CITY_UPGRADE';
AND l_assignments_to_be_updated = 0
AND l_error_assignments = 0 THEN
OPEN get_user_city_update_details;
FETCH get_user_city_update_details
INTO l_old_juri_code,l_new_juri_code,l_city_name,l_primary_flag;
WHILE (get_user_city_update_details%FOUND)
LOOP
/*Update the City Code and Primary Flag as shipped by Oracle*/
UPDATE pay_us_city_names
SET city_code = substr(l_new_juri_code,8,4),
primary_flag = l_primary_flag
WHERE state_code = substr(l_old_juri_code,1,2)
AND county_code = substr(l_old_juri_code,4,3)
AND city_code = substr(l_old_juri_code,8,4)
AND city_name = l_city_name;
DELETE FROM pay_us_zip_codes puzc
WHERE state_code = substr(l_old_juri_code,1,2)
AND county_code = substr(l_old_juri_code,4,3)
AND city_code = substr(l_old_juri_code,8,4)
AND EXISTS
( SELECT NULL
FROM pay_us_zip_codes puzc1
WHERE puzc1.state_code = puzc.state_code
AND puzc1.county_code = puzc.county_code
AND puzc1.city_code = substr(l_new_juri_code,8,4)
AND puzc1.zip_start <= puzc.zip_start
AND puzc1.zip_end >= puzc.zip_end);
UPDATE pay_us_zip_codes
SET city_code = substr(l_new_juri_code,8,4)
WHERE state_code = substr(l_old_juri_code,1,2)
AND county_code = substr(l_old_juri_code,4,3)
AND city_code = substr(l_old_juri_code,8,4);
/*Below delete statement was executed earlier. But need to execute
this again as User might have created a Zip Code which overlaps
the Oracle delivered Zip Codes.
For Example, User maintained 20000-20050. Oracle delivered 20000-20010.
Earlier delete statement was used to delete records from User Maintained
Zip Codes. 20000-20050 will not be deleted. Now as the zip codes are
merged, the below statement will remove the 20000-20010*/
DELETE FROM pay_us_zip_codes puzc
WHERE state_code = substr(l_new_juri_code,1,2)
AND county_code = substr(l_new_juri_code,4,3)
AND city_code = substr(l_new_juri_code,8,4)
AND EXISTS
( SELECT NULL
FROM pay_us_zip_codes puzc1
WHERE puzc1.state_code = puzc.state_code
AND puzc1.county_code = puzc.county_code
AND puzc1.city_code = puzc.city_code
AND puzc1.zip_start <= puzc.zip_start
AND puzc1.zip_end >= puzc.zip_end
AND puzc1.rowid <> puzc.rowid);
UPDATE pay_us_zip_codes puzc
SET zip_start = (SELECT LPAD(TO_CHAR(MAX(puzc2.zip_end) + 1),5,'0')
FROM pay_us_zip_codes puzc2
WHERE puzc2.state_code = puzc.state_code
AND puzc2.county_code = puzc.county_code
AND puzc2.city_code = puzc.city_code
AND puzc2.zip_start < puzc.zip_start
AND puzc2.zip_end < puzc.zip_end
AND puzc2.zip_end >= puzc.zip_start)
WHERE state_code = substr(l_new_juri_code,1,2)
AND county_code = substr(l_new_juri_code,4,3)
AND city_code = substr(l_new_juri_code,8,4)
AND EXISTS
( SELECT NULL
FROM pay_us_zip_codes puzc1
WHERE puzc1.state_code = puzc.state_code
AND puzc1.county_code = puzc.county_code
AND puzc1.city_code = puzc.city_code
AND puzc1.zip_start < puzc.zip_start
AND puzc1.zip_end < puzc.zip_end
AND puzc1.zip_end >= puzc.zip_start);
FETCH get_user_city_update_details
INTO l_old_juri_code,l_new_juri_code,l_city_name,l_primary_flag;
CLOSE get_user_city_update_details;
UPDATE pay_us_geo_update pugu
SET process_mode = 'UPDATED'
WHERE EXISTS
(SELECT NULL
FROM pay_patch_status pps
WHERE pps.patch_name = 'USER_CITY_UPGRADE'
AND pps.phase = 'TO_BE_UPDATED'
AND pps.process_type = 'USER_CITY_UPGRADE'
AND pps.legislation_code = 'US'
AND pps.id = pugu.id
AND pugu.process_date = pps.applied_date
AND pugu.process_type = pps.process_type
AND pugu.process_mode = pps.phase);
UPDATE pay_patch_status pps
SET phase = 'UPDATED'
WHERE pps.patch_name = 'USER_CITY_UPGRADE'
AND pps.phase = 'TO_BE_UPDATED'
AND pps.process_type = 'USER_CITY_UPGRADE'
AND pps.legislation_code = 'US';
UPDATE pay_patch_status
SET status = 'C'
WHERE legislation_code = 'US'
AND id = l_id
AND status = 'P'
AND description = l_mode;
UPDATE pay_patch_status
SET status = 'E'
WHERE legislation_code = 'US'
AND id = l_id
AND status = 'P'
AND description = l_mode;
CURSOR get_user_city_update_details IS
SELECT pus.state_name,
pus.state_abbrev,
puc.county_name,
substr(pugu.description,3) city_name,
pugu.old_juri_code,
pugu.new_juri_code
FROM pay_us_geo_update pugu,
pay_patch_status pps,
pay_us_states pus,
pay_us_counties puc
WHERE pps.patch_name = 'USER_CITY_UPGRADE'
AND pps.phase = 'TO_BE_UPDATED'
AND pps.process_type = 'USER_CITY_UPGRADE'
AND pps.legislation_code = 'US'
AND pps.id = pugu.id
AND pugu.process_date = pps.applied_date
AND pugu.process_type = pps.process_type
AND pugu.process_mode = pps.phase
AND pus.state_code = substr(pugu.old_juri_code,1,2)
AND pus.state_code = puc.state_code
AND puc.county_code = substr(pugu.old_juri_code,4,3)
ORDER BY pugu.old_juri_code;
SELECT pugu.person_id,
pugu.assignment_id,
pugu.old_juri_code,
pugu.new_juri_code,
COUNT(DISTINCT pugu.table_name)
FROM pay_us_geo_update pugu
WHERE pugu.id = p_id
AND pugu.process_type = 'D'
AND pugu.process_mode = p_mode
AND pugu.table_name IS NOT NULL
AND EXISTS
(SELECT NULL
FROM pay_us_geo_update pugu1
WHERE pugu1.id = p_id
AND pugu1.process_type = 'D'
AND pugu1.process_mode = p_mode
AND pugu1.person_id = pugu.person_id
AND pugu1.assignment_id = pugu.assignment_id
AND pugu1.table_name IS NULL
AND pugu1.table_value_id IS NULL
AND NVL(pugu1.status,'X') = 'C')
GROUP BY pugu.person_id,pugu.assignment_id,
pugu.old_juri_code,pugu.new_juri_code
HAVING COUNT(*) > 0
ORDER BY pugu.person_id,pugu.assignment_id,
pugu.old_juri_code,pugu.new_juri_code;
SELECT substr(ppf.full_name,1,100)
FROM per_all_people_f ppf
WHERE ppf.person_id = p_person_id
AND NOT EXISTS
(
SELECT NULL
FROM per_all_people_f ppf1
WHERE ppf1.person_id =ppf.person_id
AND ppf1.effective_end_date > ppf.effective_end_date
);
CURSOR get_updated_tables (p_id NUMBER,
p_mode VARCHAR2,
p_person_id NUMBER,
p_assignment_id NUMBER,
p_old_juri_code VARCHAR2,
p_new_juri_code VARCHAR2) IS
SELECT DISTINCT pugu.table_name
FROM pay_us_geo_update pugu
WHERE pugu.id = p_id
AND pugu.process_type = 'D'
AND pugu.process_mode = p_mode
AND pugu.person_id = p_person_id
AND pugu.assignment_id = p_assignment_id
AND pugu.table_name IS NOT NULL
AND pugu.old_juri_code = p_old_juri_code
AND pugu.new_juri_code = p_new_juri_code
ORDER BY pugu.table_name;
SELECT DISTINCT
substr(ppf.full_name,1,100),
pugu.assignment_id
FROM pay_us_geo_update pugu,
per_all_people_f ppf
WHERE pugu.id = p_id
AND pugu.process_type = 'D'
AND pugu.process_mode = p_mode
AND pugu.person_id = ppf.person_id
AND pugu.table_name IS NULL
AND pugu.table_value_id IS NULL
AND NVL(pugu.status,'E') <>'C'
AND NOT EXISTS (
SELECT NULL
FROM per_all_people_f ppf1
WHERE ppf1.person_id =ppf.person_id
AND ppf1.effective_end_date > ppf.effective_end_date
)
GROUP BY ppf.full_name,pugu.assignment_id
ORDER BY pugu.assignment_id;
SELECT COUNT(0)
FROM (SELECT DISTINCT assignment_id
FROM pay_us_geo_update pugu
WHERE pugu.id = p_id
AND pugu.process_type = p_process_type
AND pugu.process_mode = p_mode
AND pugu.table_name is null
AND pugu.table_value_id is null
AND DECODE(pugu.status,'C','C','E')
= DECODE(p_status,'ALL',DECODE(pugu.status,'C','C','E'),p_status));
l_old_juri_code pay_us_geo_update.old_juri_code%TYPE;
l_new_juri_code pay_us_geo_update.new_juri_code%TYPE;
l_assignment_id pay_us_geo_update.assignment_id%TYPE;
l_person_id pay_us_geo_update.person_id%TYPE;
l_table_name pay_us_geo_update.table_name%TYPE;
SELECT id,applied_date
INTO l_id,l_applied_date
FROM pay_patch_status pps
WHERE pps.patch_name LIKE 'USER_CITY_UPGRADE%'
AND pps.legislation_code = 'US'
AND pps.status = 'P'
AND pps.description = p_mode
AND NOT EXISTS
(SELECT NULL
FROM pay_patch_status pps1
WHERE pps1.id > pps.id
AND pps1.patch_name LIKE 'USER_CITY_UPGRADE%'
AND pps1.legislation_code = 'US');
/* Start of Employee Update related details printing*/
fnd_file.put_line(fnd_file.output,' Process Date : '||to_char(l_applied_date,'DD-MON-YYYY HH:MM:SS')||'
');
OPEN get_user_city_update_details;
FETCH get_user_city_update_details INTO
l_state_name,l_state_abbrev,l_county_name,
l_city_name,l_old_juri_code,l_new_juri_code;
WHILE (get_user_city_update_details%FOUND)
LOOP
fnd_file.put_line(fnd_file.output,''||l_state_name||' '||l_state_abbrev||
' '||l_county_name||' '||l_city_name||
' '||l_old_juri_code||' '||l_new_juri_code||'
FETCH get_user_city_update_details INTO
l_state_name,l_state_abbrev,l_county_name,
l_city_name,l_old_juri_code,l_new_juri_code;
CLOSE get_user_city_update_details;
fnd_file.put_line(fnd_file.output,'List down employees for whom Jurisdiction code and relevant tables are to be updated
');
fnd_file.put_line(fnd_file.output,'List down employees for whom Jurisdiction code and relevant tables were updated
');
fnd_file.put_line(fnd_file.output,'Update Details
');
fnd_file.put_line(fnd_file.output,'Employee Details Jurisdiction Details Tables Updated ');
Line: 864
OPEN get_updated_tables(l_id,p_mode,l_person_id,
l_assignment_id,l_old_juri_code,l_new_juri_code);
Line: 866
FETCH get_updated_tables INTO l_table_name;
Line: 873
WHILE (get_updated_tables%FOUND)
LOOP
fnd_file.put_line(fnd_file.output,''||l_table_name||'
FETCH get_updated_tables INTO l_table_name;
END LOOP; /* get_updated_tables LOOP */
CLOSE get_updated_tables;