[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
15-Jul-2010 emunisek 115.11 9541247 Updated report_12 to indicate the
update to Person and Location Address
Details for a change in county name
16-Jul-2010 emunisek 115.13 AGEO2010 Corrected the Data Formatting in
Report_14 to match the data of Location
being displayed with Header Record.
14-Jul-2011 emunisek 115.14 10060041 Modified function Report_12 to display
the County Update Message based on the
data found in pay_us_geo_update table
unlike the earlier hard-coded message.
18-Jul-2011 emunisek 115.15 AGEO2011 Modified the package to take the 30
characters of the city name unlike the
earlier 20 characters limit as it is
causing confusion in case of long city
names
18-Jul-2011 emunisek 115.16 AGEO2011 Modified the cursor c_cursor of procedure
report_7 so that only the Employees who
had association with the Primary City Flag
changed cities get displayed unlike the
check based on city_code earlier.
15-Jul-2012 emunisek 115.17 14314081 Added below procedures to support the
City Name changes delivered as part of
Annual Geocode 2012 Data Update.
1.report_16
2.city_name_change_report
3.formated_static_data3
4.formated_static_data4
5.formated_static_data5
6.formated_static_data6
17-Jul-2012 emunisek 115.18 14314081 Added changes to make sure that City Name
is checked while generating output to generate
correct results
18-Jul-2012 emunisek 115.19 AGEO2012 Under report_5, new_juri_code was shown as
Old Jurisdiction and old_juri_code as New
Jurisdiction. Corrected the issue
19-Jul-2012 emunisek 115.20 14314081 Added changes to check Old City Name saved in
PAY_US_GEO_UPDATE table to give correct output
report
*/
/************************************************************
** Local Package Variables
************************************************************/
gv_title VARCHAR2(100);
,p_table_updated 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);
,p_update_description in varchar2
,p_output_file_type in varchar2
,p_static_data1 out nocopy varchar2
)
IS
lv_format1 VARCHAR2(32000);
if p_update_description is not NULL then
lv_format1 := lv_format1 ||
formated_data_string (p_input_string => p_update_description
,p_output_file_type => p_output_file_type);
,p_update_description in varchar2
,p_output_file_type in varchar2
,p_static_data1 out nocopy varchar2
)
IS
lv_format1 VARCHAR2(32000);
if p_update_description is not NULL then
lv_format1 := lv_format1 ||
formated_data_string (p_input_string => p_update_description
,p_output_file_type => p_output_file_type);
,p_update_description in varchar2
,p_output_file_type in varchar2
,p_static_data1 out nocopy varchar2
)
IS
lv_format1 VARCHAR2(32000);
if p_update_description is not NULL then
lv_format1 := lv_format1 ||
formated_data_string (p_input_string => p_update_description
,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,
pay_us_emp_city_tax_rules_f pctrf
where pef.assignment_id = pgu.assignment_id
and pctrf.assignment_id = pef.assignment_id
and pctrf.effective_start_Date <= pef.effective_end_date
and pctrf.effective_end_date >= pef.effective_start_Date
and ppf.person_id = pef.person_id
and pgu.id = pps.id
and pps.patch_name = p_geocode_patch_name
and ( pgu.status = 'P'
or pef.pay_basis_id is null
or pef.payroll_id is null
or pef.location_id is null
or pef.soft_coding_keyflex_id is null
or exists (select null
from hr_soft_coding_keyflex hsck
where hsck.soft_coding_keyflex_id =
pef.soft_coding_keyflex_id
and hsck.segment1 is null ))
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,30),
pgu.old_juri_code,
substr(pmod.city_name,1,30),
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,30),
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,30),
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,30),
SUBSTR(pucn.city_name,1,30)
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
AND pay_us_geo_upd_pkg.get_city(paf.person_id, paf.location_id, ectr.state_code,
ectr.county_code,ectr.city_code,pmod.city_name,pmod.patch_name,pmod.process_type) = pmod.city_name
UNION
SELECT /*+ ORDERED
INDEX (PAY_US_MODIFIED_GEOCODES PAY_US_MODIFIED_GEOCODES_N1)
INDEX (PAY_US_EMP_CITY_TAX_RULES_F PAY_US_EMP_CITY_TAX_RULES_N3) */
SUBSTR(ppf.full_name,1,40),
SUBSTR(pmod2.city_name,1,30),
SUBSTR(pmod.city_name,1,30)
FROM pay_us_modified_geocodes pmod,
pay_us_emp_city_tax_rules_f ectr,
per_assignments_f paf,
per_people_f ppf,
pay_us_modified_geocodes pmod2
WHERE pmod.process_type = 'S'
and pmod2.process_type in ('UP','PU','P')
and pmod2.state_code = ectr.state_code
and pmod2.county_code = ectr.county_code
and pmod2.old_city_code = ectr.city_code
AND pmod.state_code = ectr.state_code
AND pmod.county_code = ectr.county_code
AND pmod.new_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
AND pmod2.patch_name = cp_geocode_patch_name
AND pay_us_geo_upd_pkg.get_city(paf.person_id, paf.location_id, ectr.state_code,
ectr.county_code,ectr.city_code,pmod.city_name,pmod.patch_name,pmod.process_type) = pmod.city_name;
select distinct state_code||'-'||county_code||'-'||old_city_code ,
state_code||'-'||county_code||'-'||new_city_code ,
substr(city_name,1,30)
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,30),
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,30),
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,30),
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');
the same tables are getting updated Bug#14314081*/
cursor c_county_name_change (cp_geocode_patch_name in varchar) is
SELECT pugp.new_juri_code,pugp.description
FROM pay_us_geo_update pugp,
pay_patch_status pps
WHERE pugp.table_name in ('PER_ADDRESSES','HR_LOCATIONS_ALL')
AND pugp.process_type = 'CN'
AND pugp.id = pps.id
AND pps.patch_name = cp_geocode_patch_name
ORDER BY pugp.description;
'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 distinct 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,30),
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' );
Changes and the details of the Address updated */
PROCEDURE report_16
(p_process_mode in varchar2
,p_geocode_patch_name in varchar2
,p_output_file_type in varchar2
)
IS
/*Cursor to get all the City Names for the current patch*/
cursor c_city_name_change (cp_geocode_patch_name in varchar) is
SELECT decode(pumg1.state_code,'70','CA','US') country,
pus.state_code,
pus.state_abbrev,
puc.county_code,
pumg1.old_city_code,
pumg1.city_name old_city_name,
pumg2.city_name new_city_name
FROM pay_us_modified_geocodes pumg1,
pay_us_modified_geocodes pumg2,
pay_us_states pus,
pay_us_counties puc
WHERE pus.state_code = puc.state_code
AND pus.state_code = pumg1.state_code
AND pus.state_code = pumg2.state_code
AND puc.county_code = pumg1.county_code
AND puc.county_code = pumg2.county_code
AND pumg1.patch_name = p_geocode_patch_name
AND pumg1.process_type = 'CY'
AND pumg1.state_code = pumg2.state_code
AND pumg1.county_code = pumg2.county_code
AND pumg1.old_city_code = pumg2.old_city_code
AND pumg1.new_city_code = pumg2.new_city_code
AND pumg1.city_name = SUBSTR(pumg2.patch_name,INSTR(pumg2.patch_name,':')+1)
AND REPLACE(pumg1.patch_name,'GEOCODE_ANNUAL_')
= REPLACE(SUBSTR(pumg2.patch_name,1,INSTR(pumg2.patch_name,':')-1),'CITY_NAME_CHANGE_')
ORDER BY country DESC,
pus.state_code,
puc.county_code,
pumg1.old_city_code;
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) ,
ppf.person_id ,
ppf.employee_number,
pgu.new_juri_code,
pumg1.city_name old_city_name,
pumg2.city_name new_city_name,
substr(pgu.description,1,instr(pgu.description,':')-1)
from pay_patch_status pps,
pay_us_geo_update pgu,
per_people_f ppf,
per_addresses pa,
pay_us_modified_geocodes pumg1,
pay_us_modified_geocodes pumg2
where pgu.process_type = 'CY'
and pgu.table_name = 'PER_ADDRESSES'
and ppf.person_id = pgu.person_id
and pa.person_id = ppf.person_id
and decode(substr(pgu.description,1,instr(pgu.description,':')-1),'Taxation Address',pa.add_information18,pa.town_or_city) =
decode(cp_process_mode,'DEBUG',pumg1.city_name,pumg2.city_name)
and pgu.process_mode = cp_process_mode
and pgu.id = pps.id
and pgu.new_juri_code = pumg1.state_code||'-'||
pumg1.county_code||'-'||
pumg1.old_city_code
and pps.patch_name = cp_geocode_patch_name
and pumg1.patch_name = cp_geocode_patch_name
and pumg1.process_type = 'CY'
and pumg1.city_name = substr(pgu.description,instr(pgu.description,':')+1)
and pumg1.state_code = pumg2.state_code
and pumg1.county_code = pumg2.county_code
and pumg1.old_city_code = pumg2.old_city_code
and pumg1.new_city_code = pumg2.new_city_code
and pumg1.city_name = SUBSTR(pumg2.patch_name,INSTR(pumg2.patch_name,':')+1)
and REPLACE(pumg1.patch_name,'GEOCODE_ANNUAL_')
= REPLACE(SUBSTR(pumg2.patch_name,1,INSTR(pumg2.patch_name,':')-1),'CITY_NAME_CHANGE_')
order by ppf.person_id;
select /*+ ORDERED
INDEX (PAY_US_GEO_UPDATE PAY_US_GEO_UPDATE_N2)
INDEX (PAY_PATCH_STATUS PAY_PATCH_STATUS_N1) */
distinct substr(hl.location_code,1,40) ,
hl.location_id ,
pgu.new_juri_code,
pumg1.city_name old_city_name,
pumg2.city_name new_city_name,
substr(pgu.description,1,instr(pgu.description,':')-1)
from pay_patch_status pps,
pay_us_geo_update pgu,
hr_locations_all hl,
pay_us_modified_geocodes pumg1,
pay_us_modified_geocodes pumg2
where pgu.process_type = 'CY'
and pgu.table_name = 'HR_LOCATIONS_ALL'
and hl.location_id = pgu.table_value_id
and pgu.process_mode = cp_process_mode
and pgu.id = pps.id
and pgu.new_juri_code = pumg1.state_code||'-'||
pumg1.county_code||'-'||
pumg1.old_city_code
and pps.patch_name = cp_geocode_patch_name
and pumg1.patch_name = cp_geocode_patch_name
and decode(substr(pgu.description,1,instr(pgu.description,':')-1),'Payroll Tax Address',hl.loc_information18,hl.town_or_city) =
decode(cp_process_mode,'DEBUG',pumg1.city_name,pumg2.city_name)
and pumg1.process_type = 'CY'
and pumg1.city_name = substr(pgu.description,instr(pgu.description,':')+1)
and pumg1.state_code = pumg2.state_code
and pumg1.county_code = pumg2.county_code
and pumg1.old_city_code = pumg2.old_city_code
and pumg1.new_city_code = pumg2.new_city_code
and pumg1.city_name = SUBSTR(pumg2.patch_name,INSTR(pumg2.patch_name,':')+1)
and REPLACE(pumg1.patch_name,'GEOCODE_ANNUAL_')
= REPLACE(SUBSTR(pumg2.patch_name,1,INSTR(pumg2.patch_name,':')-1),'CITY_NAME_CHANGE_')
order by hl.location_id;
select /*+ ORDERED
INDEX (PAY_US_GEO_UPDATE PAY_US_GEO_UPDATE_N2)
INDEX (PAY_PATCH_STATUS PAY_PATCH_STATUS_N1) */
distinct substr(hou.name,1,40) ,
hoi.organization_id ,
pgu.new_juri_code,
pumg1.city_name old_city_name,
pumg2.city_name new_city_name,
NVL(hoit.displayed_org_information_type,fdfv.descriptive_flex_context_name)||
', '||fdfc.form_left_prompt
from pay_patch_status pps,
pay_us_geo_update pgu,
hr_organization_information hoi,
hr_organization_units hou,
fnd_descr_flex_contexts_vl fdfv,
fnd_descr_flex_col_usage_vl fdfc,
hr_org_information_types hoit,
pay_us_modified_geocodes pumg1,
pay_us_modified_geocodes pumg2
where pgu.process_type = 'CY'
and pgu.table_name = 'HR_ORGANIZATION_INFORMATION'
and hoi.org_information_id = pgu.table_value_id
and hoi.organization_id = hou.organization_id
and hoi.org_information_context = substr(pgu.description,1,instr(pgu.description,':')-1)
and hoi.org_information_context = fdfv.descriptive_flex_context_code
and fdfv.descriptive_flex_context_code = fdfc.descriptive_flex_context_code
and fdfc.descriptive_flex_context_code = hoit.org_information_type(+)
and fdfc.descriptive_flexfield_name = fdfv.descriptive_flexfield_name
and fdfc.descriptive_flexfield_name = 'Org Developer DF'
and fdfc.application_column_name = substr(pgu.description,instr(pgu.description,':')+1)
and pgu.process_mode = cp_process_mode
and pgu.id = pps.id
and pgu.new_juri_code = pumg1.state_code||'-'||
pumg1.county_code||'-'||
pumg1.old_city_code
and pps.patch_name = cp_geocode_patch_name
and pumg1.patch_name = cp_geocode_patch_name
and pumg1.process_type = 'CY'
and pumg1.state_code = pumg2.state_code
and pumg1.county_code = pumg2.county_code
and pumg1.old_city_code = pumg2.old_city_code
and pumg1.new_city_code = pumg2.new_city_code
and pumg1.city_name = SUBSTR(pumg2.patch_name,INSTR(pumg2.patch_name,':')+1)
and REPLACE(pumg1.patch_name,'GEOCODE_ANNUAL_')
= REPLACE(SUBSTR(pumg2.patch_name,1,INSTR(pumg2.patch_name,':')-1),'CITY_NAME_CHANGE_')
order by hoi.organization_id;
ln_jurisdiction_code pay_us_geo_update.new_juri_code%TYPE;
ln_update_description pay_us_geo_update.description%TYPE;
'The following are the Person Addresses updated for City Name changes.'
,p_output_file_type
));
/* Start of Person Address details updated section */
if p_output_file_type ='HTML' then
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
formated_data_string (p_input_string => 'Updated Address Type'
,p_bold => 'Y'
,p_output_file_type => p_output_file_type)
;
,ln_update_description;
,ln_update_description
,p_output_file_type
,lv_data_row1);
/* End of Person Address details updated section */
fnd_file.put_line(fnd_file.output, formated_header_string(
'The following are the Location Addresses updated for City Name changes.'
,p_output_file_type
));
/* Start of Location details updated section */
if p_output_file_type ='HTML' then
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
formated_data_string (p_input_string => 'Updated Address Type'
,p_bold => 'Y'
,p_output_file_type => p_output_file_type)
;
,ln_update_description;
,ln_update_description
,p_output_file_type
,lv_data_row1);
/* End of Location details updated section */
fnd_file.put_line(fnd_file.output, formated_header_string(
'The following are the Organization Information Details updated for City Name changes.'
,p_output_file_type
));
/* Start of Organization Information updated section */
if p_output_file_type ='HTML' then
FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '');
formated_data_string (p_input_string => 'Updated Information'
,p_bold => 'Y'
,p_output_file_type => p_output_file_type)
;
,ln_update_description;
,ln_update_description
,p_output_file_type
,lv_data_row1);
/* End of Organization Information updated section */
END report_16;
p_geo_phase_id pay_us_geo_update.id%TYPE)
IS
lv_output_file_type varchar2(4);
SELECT patch_name
INTO lv_patch_name
FROM pay_patch_status
WHERE id = p_geo_phase_id;