[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,p_table_updated in varchar2
,p_error_description in varchar2
,p_output_file_type in varchar2
,p_static_data1 out nocopy varchar2
)
IS
lv_format1 VARCHAR2(32000);
if p_table_updated is not NULL then
lv_format1 := lv_format1 ||
formated_data_string (p_input_string => p_table_updated
,p_output_file_type => p_output_file_type);
select distinct substr(ppf.full_name,1,40) ,
pef.assignment_id ,
substr(pef.assignment_number,1,17) ,
substr(pgu.description,1,65)
from per_people_f ppf,
per_assignments_f pef,
pay_us_geo_update pgu,
pay_patch_status pps
where pef.assignment_id = pgu.assignment_id
and ppf.person_id = pef.person_id
and pgu.id = pps.id
and pps.patch_name = p_geocode_patch_name
and pgu.status = 'P'
and pgu.process_mode = p_process_mode;
'THIS IS A LIST OF ASSIGNMENT INFORMATION FOR THE GEOCODE UPDATE QUARTERLY PATCH'
,p_output_file_type
));
select distinct substr(ppf.full_name,1,40) ,
pef.assignment_id ,
substr(pef.assignment_number,1,17)
from per_people_f ppf,
per_assignments_f pef,
pay_us_geo_update pgu,
pay_patch_status pps
where pgu.process_type = 'PERCENTAGE_OVER_100'
and pef.assignment_id = pgu.assignment_id
and ppf.person_id = pef.person_id
and pgu.process_mode = p_process_mode
and pgu.id = pps.id
and pps.patch_name = p_geocode_patch_name;
select distinct substr(ppf.full_name,1,40) ,
pef.assignment_id ,
substr(pef.assignment_number,1,17)
from per_people_f ppf,
per_assignments_f pef,
pay_us_geo_update pgu,
pay_patch_status pps
where pgu.process_type = 'MISSING_COUNTY_RECORDS'
and pef.assignment_id = pgu.assignment_id
and ppf.person_id = pef.person_id
and pgu.process_mode = cp_process_mode
and pgu.id = pps.id
and pps.patch_name = cp_geocode_patch_name;
select /*+ index(pmod PAY_US_MODIFIED_GEOCODES_PK)*/ -- Bug 3350007
distinct substr(ppf.full_name,1,40) ,
pef.assignment_id ,
substr(pef.assignment_number,1,17),
pgu.new_juri_code ,
substr(pusc.city_name,1,20),
pgu.old_juri_code,
substr(pmod.city_name,1,20),
substr(pgu.table_name ,1,20)
from pay_us_modified_geocodes pmod,
pay_us_city_names pusc ,
pay_patch_status pps,
per_people_f ppf,
per_assignments_f pef,
pay_us_geo_update pgu
where pgu.process_type = 'PU'
and pef.assignment_id = pgu.assignment_id
and ppf.person_id = pef.person_id
and pgu.table_name is not null
and substr(new_juri_code,1,2) = pmod.state_code
and substr(new_juri_code,4,3) = pmod.county_code
and substr(new_juri_code,8,4) = pmod.new_city_code
and substr(old_juri_code,8,4) = pmod.old_city_code
and pmod.process_type = 'PU'
and pusc.city_code = substr(new_juri_code,8,4)
and pusc.county_code = substr(new_juri_code,4,3)
and pusc.state_code = substr(new_juri_code,1,2)
and pusc.primary_flag = 'Y'
and pgu.process_mode = cp_process_mode
and pgu.id = pps.id
and pps.patch_name = cp_geocode_patch_name;
'Employees who have records updated in the following tables because'
,p_output_file_type
));
formated_data_string (p_input_string => 'Table Updated'
,p_bold => 'Y'
,p_output_file_type => p_output_file_type)
;
select distinct substr(ppf.full_name,1,40) ,
pef.assignment_id "Assignment Id" ,
substr(pef.assignment_number,1,17),
substr(pusc.city_name,1,20),
old_juri_code "Old JD",
new_juri_code "New JD",
substr(table_name,1,20)
from per_people_f ppf,
per_assignments_f pef,
pay_us_geo_update pgu,
pay_us_city_names pusc,
pay_patch_status pps
where pgu.process_type = 'UP'
and pgu.table_name is not null
and pef.assignment_id = pgu.assignment_id
and ppf.person_id = pef.person_id
and pusc.city_code = substr(new_juri_code,8,4)
and pusc.county_code = substr(new_juri_code,4,3)
and pusc.state_code = substr(new_juri_code,1,2)
and pusc.primary_flag = 'Y'
and pgu.process_mode = p_process_mode
and pgu.id = pps.id
and pps.patch_name = p_geocode_patch_name;
'Employees whose records have been updated in the following tables'
,p_output_file_type
));
formated_data_string (p_input_string => 'Table Updated'
,p_bold => 'Y'
,p_output_file_type => p_output_file_type)
;
select distinct substr(ppf.full_name,1,40) ,
pef.assignment_id "Assignment Id" ,
substr(pef.assignment_number,1,17),
substr(pusc.city_name,1,20),
substr(puscn.county_name,1,20),
substr(pust.state_abbrev,1,2),
old_juri_code ,
new_juri_code
from per_people_f ppf,
per_assignments_f pef,
pay_us_geo_update pgu,
pay_us_city_names pusc,
pay_patch_status pps ,
pay_us_states pust,
pay_us_counties puscn
where pgu.process_type = 'US'
and pgu.status = 'A'
and pgu.table_name is null
and pef.assignment_id = pgu.assignment_id
and ppf.person_id = pef.person_id
and pusc.city_code = substr(new_juri_code,8,4)
and pusc.county_code = substr(new_juri_code,4,3)
and pusc.state_code = substr(new_juri_code,1,2)
and puscn.county_code = pusc.county_code
and puscn.state_code = pusc.state_code
and pust.state_code = pusc.state_code
and pusc.primary_flag = 'Y'
and pgu.process_mode = cp_process_mode
and pgu.id = pps.id
and pps.patch_name = cp_geocode_patch_name;
SELECT /*+ ORDERED
INDEX (PAY_US_MODIFIED_GEOCODES PAY_US_MODIFIED_GEOCODES_N1)
INDEX (PAY_US_CITY_NAMES PAY_US_CITY_NAMES_FK1)
INDEX (PAY_US_EMP_CITY_TAX_RULES_F PAY_US_EMP_CITY_TAX_RULES_N3) */
SUBSTR(ppf.full_name,1,40),
SUBSTR(pmod.city_name,1,20),
SUBSTR(pucn.city_name,1,20)
FROM pay_us_modified_geocodes pmod,
pay_us_city_names pucn,
pay_us_emp_city_tax_rules_f ectr,
per_assignments_f paf,
per_people_f ppf
WHERE pmod.process_type = 'P'
AND pmod.state_code = pucn.state_code
AND pmod.county_code = pucn.county_code
AND pmod.new_city_code = pucn.city_code
AND pucn.primary_flag = 'Y'
AND pmod.state_code = ectr.state_code
AND pmod.county_code = ectr.county_code
AND pmod.old_city_code = ectr.city_code
AND ectr.assignment_id = paf.assignment_id
AND paf.person_id = ppf.person_id
AND pmod.patch_name = cp_geocode_patch_name
union all
SELECT /*+ ORDERED
INDEX (PAY_US_MODIFIED_GEOCODES PAY_US_MODIFIED_GEOCODES_N1)
INDEX (PAY_US_CITY_NAMES PAY_US_CITY_NAMES_FK1)
INDEX (PAY_US_EMP_CITY_TAX_RULES_F PAY_US_EMP_CITY_TAX_RULES_N3) */
SUBSTR(ppf.full_name,1,40),
SUBSTR(pucn.city_name,1,20),
SUBSTR(pmod.city_name,1,20)
FROM pay_us_modified_geocodes pmod,
pay_us_city_names pucn,
pay_us_emp_city_tax_rules_f ectr,
per_assignments_f paf,
per_people_f ppf,
hr_s_us_city_names pmod2
WHERE pmod.process_type = 'S'
AND pmod.state_code = pucn.state_code
AND pmod.county_code = pucn.county_code
AND pmod.new_city_code = pucn.city_code
AND pucn.city_name = pmod2.city_name
and pmod2.state_code = ectr.state_code
and pmod2.county_code = ectr.county_code
and pmod2.city_code = ectr.city_code
and pmod2.primary_flag = 'Y'
and pmod2.city_name = pmod.city_name
AND pmod.state_code = ectr.state_code
AND pmod.county_code = ectr.county_code
AND pmod.old_city_code = ectr.city_code
AND ectr.assignment_id = paf.assignment_id
AND paf.person_id = ppf.person_id
AND pmod.patch_name = cp_geocode_patch_name;
select state_code||'-'||county_code||'-'||old_city_code ,
state_code||'-'||county_code||'-'||new_city_code ,
substr(city_name,1,20)
from pay_us_modified_geocodes
where old_city_code like 'U%';
select /*+ ORDERED
INDEX (PAY_US_GEO_UPDATE PAY_US_GEO_UPDATE_N2)
INDEX (PAY_PATCH_STATUS PAY_PATCH_STATUS_N1) */
distinct substr(ppf.full_name,1,40) ,
pef.assignment_id ,
substr(pef.assignment_number,1,17),
substr(pusc.city_name,1,20),
pgu.new_juri_code
from pay_patch_status pps,
pay_us_geo_update pgu,
pay_us_city_names pusc,
per_assignments_f pef,
per_people_f ppf
where pgu.process_type = 'NEW_CITY_RECORDS'
and pef.assignment_id = pgu.assignment_id
and ppf.person_id = pef.person_id
and pusc.city_code = substr(new_juri_code,8,4)
and pusc.county_code = substr(new_juri_code,4,3)
and pusc.state_code = substr(new_juri_code,1,2)
and pgu.process_mode = cp_process_mode
and pusc.primary_flag = 'Y'
and pgu.id = pps.id
and pps.patch_name = cp_geocode_patch_name;
select /*+ ORDERED
INDEX (PAY_US_GEO_UPDATE PAY_US_GEO_UPDATE_N2 )
INDEX (PAY_PATCH_STATUS PAY_PATCH_STATUS_N1) */
distinct substr(ppf.full_name,1,40) ,
pef.assignment_id ,
substr(pef.assignment_number,1,17),
substr(pusc.city_name,1,20),
pgu.new_juri_code
from pay_patch_status pps,
pay_us_geo_update pgu,
pay_us_city_names pusc,
per_assignments_f pef,
per_people_f ppf
where pgu.process_type = 'NEW_VERTEX_RECORDS'
and pef.assignment_id = pgu.assignment_id
and ppf.person_id = pef.person_id
and pusc.city_code = substr(new_juri_code,8,4)
and pusc.county_code = substr(new_juri_code,4,3)
and pusc.state_code = substr(new_juri_code,1,2)
and pusc.primary_flag = 'Y'
and pgu.process_mode = cp_process_mode
and pgu.id = pps.id
and pps.patch_name = cp_geocode_patch_name;
select /*+ ORDERED
INDEX (PAY_US_GEO_UPDATE PAY_US_GEO_UPDATE_N2 )
INDEX (PAY_PATCH_STATUS PAY_PATCH_STATUS_N1) */
distinct old_juri_code ,
new_juri_code
from pay_patch_status pps,
pay_us_geo_update pgu
where pgu.process_type = 'TAX_RULES_CHANGE'
and pgu.process_mode = cp_process_mode
and pgu.id = pps.id
and pps.patch_name = cp_geocode_patch_name;
SELECT /*+ ORDERED
INDEX (PAY_US_MODIFIED_GEOCODES PAY_US_MODIFIED_GEOCODES_N1) */
distinct
substr(ppf.full_name,1,40),
paf.assignment_id ,
substr(paf.assignment_number,1,17),
substr(pmod.city_name,1,20),
ectr.jurisdiction_code,
substr(puc1.county_name,1,20),
pmod.state_code||'-'||pmod.new_county_code||'-'||pmod.new_city_code "New JD",
substr(puc2.county_name,1,20)
FROM pay_us_modified_geocodes pmod,
pay_us_emp_city_tax_rules_f ectr,
per_assignments_f paf,
per_people_f ppf,
pay_us_counties puc1,
pay_us_counties puc2
WHERE ppf.person_id = paf.person_id
AND pmod.state_code = ectr.state_code
AND pmod.state_code = puc1.state_code
AND pmod.state_code = puc2.state_code
AND pmod.county_code = puc1.county_code
AND pmod.new_county_code = puc2.county_code
AND pmod.county_code = ectr.county_code
AND pmod.new_county_code is not null
AND pmod.old_city_code = ectr.city_code
AND ectr.assignment_id = paf.assignment_id
AND pmod.patch_name = cp_geocode_patch_name
and pmod.process_type in ('P', 'PC', 'PU', 'S', 'SU', 'UP', 'US');
'changing their county codes. You must update the jurisdiction information'
,p_output_file_type
));
'The following tables were updated and now have the following row counts:'
,p_output_file_type
));
select count(*)
into ln_row_count
from pay_us_states;
select count(*)
into ln_row_count
from pay_us_counties;
select count(*)
into ln_row_count
from pay_us_city_geocodes;
select count(*)
into ln_row_count
from pay_us_city_names;
select count(*)
into ln_row_count
from pay_us_zip_codes;
select count(*)
into ln_row_count
from pay_us_modified_geocodes;
select hla.location_code "Work Location",
hla.description "Location Description",
hla.address_line_1 "Address",
hla.town_or_city "City Name",
hla.region_1 "County",
hla.region_2 "State",
hla.postal_code "Zipcode"
from hr_locations_all hla
where hla.location_id in
(select distinct location_id
from per_assignments_f paf,
pay_us_emp_city_tax_rules_f pctr
where ( ( pctr.STATE_CODE = '26'
and pctr.county_code = '510'
and pctr.city_code = '1270')
or ( pctr.state_code = '21'
and pctr.county_code = '510'
and pctr.city_code = '0040')
)
and pctr.assignment_id = paf.assignment_id )
and postal_code in
( '63142',
'63148',
'63149',
'63152',
'63153',
'63154',
'63159',
'63161',
'63162',
'63165',
'63168',
'63170',
'63172',
'63173',
'63174',
'63175',
'63176',
'63181',
'63183',
'63184',
'63185',
'63186',
'63187',
'63189',
'63191',
'63192',
'63193',
'63194',
'21232',
'21238',
'21242',
'21243',
'21245',
'21246',
'21247',
'21248',
'21249',
'21253',
'21254',
'21255',
'21256',
'21257',
'21258',
'21259',
'21260',
'21261',
'21262',
'21266',
'21267',
'21269',
'21271',
'21272',
'21276',
'21277',
'21291',
'21292',
'21293',
'21294',
'21295',
'21296',
'21299' );
SELECT distinct substr(ppf.full_name,1,40),
substr(addr.address_line1 ,1,30),
substr(addr.town_or_city,1,20),
substr(addr.region_1 ,1,20),
substr(addr.region_2 ,1,5),
substr(addr.postal_code ,1,10)
from per_addresses addr,
per_all_people_f ppf
where addr.person_id = ppf.person_id
and ppf.person_id in
(select distinct person_id
from per_assignments_f paf,
pay_us_emp_city_tax_rules_f pctr
where ( ( pctr.STATE_CODE = '26'
and pctr.county_code = '510'
and pctr.city_code = '1270')
or ( pctr.state_code = '21'
and pctr.county_code = '510'
and pctr.city_code = '0040')
)
and pctr.assignment_id = paf.assignment_id )
and addr.postal_code in
( '63142',
'63148',
'63149',
'63152',
'63153',
'63154',
'63159',
'63161',
'63162',
'63165',
'63168',
'63170',
'63172',
'63173',
'63174',
'63175',
'63176',
'63181',
'63183',
'63184',
'63185',
'63186',
'63187',
'63189',
'63191',
'63192',
'63193',
'63194',
'21232',
'21238',
'21242',
'21243',
'21245',
'21246',
'21247',
'21248',
'21249',
'21253',
'21254',
'21255',
'21256',
'21257',
'21258',
'21259',
'21260',
'21261',
'21262',
'21266',
'21267',
'21269',
'21271',
'21272',
'21276',
'21277',
'21291',
'21292',
'21293',
'21294',
'21295',
'21296',
'21299' );