[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select city_name
from pay_us_modified_geocodes
where patch_name = p_patch_name
and process_type = 'CN'
and state_code = p_state_code
and county_code = p_county_code;
select town_or_city
from per_addresses pa,
pay_us_states pus,
pay_us_counties puc
where pus.state_abbrev = pa.region_2
and pus.state_code = puc.state_code
and nvl(p_county_name,puc.county_name) = pa.region_1
and pa.person_id = p_person_id
and pus.state_code = p_state_code
and puc.county_code = p_county_code
union
select pa.add_information18
from per_addresses pa,
pay_us_states pus,
pay_us_counties puc
where pus.state_abbrev = pa.add_information17
and pus.state_code = puc.state_code
and nvl(p_county_name,puc.county_name) = pa.add_information19
and pa.person_id = p_person_id
and pus.state_code = p_state_code
and puc.county_code = p_county_code
union
select hl.town_or_city
from hr_locations_all hl,
pay_us_states pus,
pay_us_counties puc
where pus.state_abbrev = hl.region_2
and pus.state_code = puc.state_code
and nvl(p_county_name,puc.county_name) = hl.region_1
and hl.location_id = p_location_id
and pus.state_code = p_state_code
and puc.county_code = p_county_code
union
select loc_information18
from hr_locations_all hl,
pay_us_states pus,
pay_us_counties puc
where pus.state_abbrev = hl.loc_information17
and pus.state_code = puc.state_code
and nvl(p_county_name,puc.county_name) = hl.loc_information19
and pus.state_code = p_state_code
and puc.county_code = p_county_code
and hl.location_id = p_location_id;
select pucn.city_name
from pay_us_city_names pucn
where pucn.state_code = p_state_code
and pucn.county_code = p_county_code
and pucn.city_code = p_city_code
and pucn.city_name = p_emp_city_name
and not exists
(select null
from pay_us_modified_geocodes
where patch_name = p_patch_name
and new_city_code = p_city_code
and old_city_code <> p_city_code)
union
select pucn.city_name
from pay_us_city_names pucn,
pay_us_modified_geocodes pumg
where pucn.state_code = p_state_code
and pucn.county_code = p_county_code
and pucn.city_name = p_emp_city_name
and pumg.state_code = pucn.state_code
and pumg.county_code = pucn.county_code
and pumg.old_city_code = p_city_code
and pumg.new_city_code <> p_city_code
and pumg.patch_name = p_patch_name;
select ppa.legislative_parameters,
pay_us_geo_upd_pkg.get_parameter('PATCH_NAME',PPa.legislative_parameters)
into leg_param,
ln_upgrade_patch
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
sqlstr := ' select distinct paf.person_id
from pay_us_modified_geocodes mg,
pay_us_emp_city_tax_rules_f tr,
per_all_assignments_f paf,
pay_us_states pus
where mg.patch_name = '''||ln_upgrade_patch||'''
and mg.state_code = pus.state_code
and mg.state_code = tr.state_code
and mg.county_code = tr.county_code
and mg.old_city_code = tr.city_code
and tr.assignment_id = paf.assignment_id
and :pactid is not null
order by paf.person_id';
select ppa.legislative_parameters,
pay_us_geo_upd_pkg.get_parameter('PATCH_NAME',PPa.legislative_parameters),
pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
SELECT distinct ectr.assignment_id
FROM per_all_assignments_f paf,
pay_us_emp_city_tax_rules_f ectr,
pay_us_modified_geocodes pmod
WHERE pmod.state_code = ectr.state_code
AND pmod.county_code = ectr.county_code
AND pmod.new_county_code is null
AND pmod.old_city_code = ectr.city_code
AND pmod.process_type in ('UP','US','PU','D','SU')
AND pmod.patch_name = l_patch_name
AND ectr.assignment_id = paf.assignment_id
AND paf.person_id between stperson and endperson
AND get_city(paf.person_id, paf.location_id, ectr.state_code,
ectr.county_code,ectr.city_code,pmod.city_name,l_patch_name,pmod.process_type) = pmod.city_name
AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.assignment_id = ectr.assignment_id
and pugu.new_juri_code = pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code
and pugu.old_juri_code = ectr.jurisdiction_code
and pugu.table_value_id is null
and pugu.table_name is null
and pugu.process_type = pmod.process_type
and pugu.process_mode = l_mode
and pugu.id = l_geo_phase_id)
UNION ALL
SELECT distinct pac.assignment_id
FROM per_all_assignments_f paf,
pay_action_contexts pac,
pay_us_modified_geocodes pmod
WHERE pmod.state_code = 'CA'
AND pmod.county_code = pac.context_value
AND pac.context_id in (select context_id
from ff_contexts
where context_name = 'JURISDICTION_CODE')
AND pmod.patch_name = l_patch_name
AND pac.assignment_id = paf.assignment_id
AND paf.person_id between stperson and endperson ;
select ppa.payroll_action_id
from per_business_groups pbg, pay_payroll_actions ppa
Where ppa.action_type in ('R', 'Q', 'I', 'B', 'V')
and ppa.effective_date >= p_balance_load_date
and pbg.business_group_id = ppa.business_group_id
and pbg.legislation_code in ( 'US', 'CA');
select ID
from pay_patch_status
where patch_name = p_patch_name
and status in ('P','E');
select phase, status from pay_patch_status
where patch_name like p_patch_name || '%'
and legislation_code = 'US';
If both conditions above are true, there is a geocode update
underway and a row for this process needs to be added to the
pay_patch_status table.
*/
hr_utility.trace('inserting into pay_patch_status ');
insert into pay_patch_status
(ID,
PATCH_NUMBER,
PATCH_NAME,
PHASE,
PROCESS_TYPE,
APPLIED_DATE,
STATUS,
DESCRIPTION,
UPDATE_DATE,
LEGISLATION_CODE,
APPLICATION_RELEASE,
PREREQ_PATCH_NAME)
values
(PAY_PATCH_STATUS_S.nextval,
'1111111',
l_patch_name, --p_patch_name,
'START',
null,
sysdate,
'P',
'CURRENT GEOCODE PATCH', -- lv_patch_desc,
null,
'US',
'115',
'Q2' );
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
-- insert the action record.
hr_nonrun_asact.insact(lockingactid => lockingactid,
Object_Id => l_assignment_id,
pactid => pactid,
chunk => chunk,
object_type => 'ASG');
hr_utility.trace('before update_taxability_rules value of l_geo_phase_Id is '|| to_char(l_geo_phase_Id));
select min(balance_load_date)
into l_balance_load_date
from pay_balance_validation;
select max(chunk_number)
into lv_no_of_chunks
from pay_population_ranges
where payroll_action_id = pactid;
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
select decode (mod(lv_count,lv_no_of_chunks),0,lv_no_of_chunks,mod(lv_count,lv_no_of_chunks))
into lv_curr_chunk
from dual;
pay_us_geo_upd_pkg.update_county_name(l_geo_phase_id,l_mode,l_patch_name,'INTERNAL'); --l_patch_name);
pay_us_geo_upd_pkg.update_city_name(l_geo_phase_id,l_mode,l_patch_name,'INTERNAL'); --l_patch_name);
pay_us_geo_upd_pkg.update_taxability_rules(l_geo_phase_id,l_mode,l_patch_name); --l_patch_name);
pay_us_geo_upd_pkg.update_org_info(l_geo_phase_id,l_mode,l_patch_name); --l_patch_name);
pay_us_geo_upd_pkg.update_ca_emp_info(l_geo_phase_id,l_mode,l_patch_name);
sqlstr := 'select paa1.rowid
from pay_assignment_actions paa1, -- PYUGEN assignment action
pay_payroll_actions ppa1 -- PYUGEN payroll action id
where ppa1.payroll_action_id = :pactid
and paa1.payroll_action_id = ppa1.payroll_action_id
order by paa1.assignment_action_id
for update of paa1.assignment_id';
select ptoa.payroll_action_id,
ptoa.object_id,
ptoa.object_type
from PAY_TEMP_OBJECT_ACTIONS ptoa
where ptoa.object_action_id = cp_assignment_action;
select ppa.legislative_parameters,
pay_us_geo_upd_pkg.get_parameter('PATCH_NAME',PPa.legislative_parameters),
pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
select ID
from pay_patch_status
where patch_name = p_patch_name
and status in ('P','E');
hr_utility.set_location ('pay_us_geo_update.action_code', 1);
select patch_name, patch_number
from pay_patch_status
where description = 'CURRENT GEOCODE PATCH';
select id from pay_patch_status
where patch_name = p_patch_name
and patch_number = p_patch_number
and legislation_code = 'US';
select 'x' from dual
where exists(select 'x' from pay_us_geo_update
where id = p_patch_id
and status = p_patch_status
and rownum < 2);
select ppa.legislative_parameters,
pay_us_geo_upd_pkg.get_parameter('PATCH_NAME',PPa.legislative_parameters),
pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
update pay_patch_status
set status = 'E'
where id = ln_patch_id;
update pay_patch_status
set status = 'C',
phase = null,
process_type = null,
description = null
where id = ln_patch_id;
insert into PAY_US_GEO_UPDATE (ID,
ASSIGNMENT_ID,
PERSON_ID,
TABLE_NAME,
TABLE_VALUE_ID,
OLD_JURI_CODE,
NEW_JURI_CODE,
PROCESS_TYPE,
PROCESS_DATE,
PROCESS_MODE,
STATUS,
DESCRIPTION)
VALUES(g_geo_phase_id,
p_assign_id,
p_person_id,
p_location,
p_id,
p_old_juri_code,
p_new_juri_code,
p_proc_type,
sysdate,
'UPGRADE',
p_status,
p_description);
insert into PAY_US_GEO_UPDATE (ID,
ASSIGNMENT_ID,
PERSON_ID,
TABLE_NAME,
TABLE_VALUE_ID,
OLD_JURI_CODE,
NEW_JURI_CODE,
PROCESS_TYPE,
PROCESS_DATE,
PROCESS_MODE,
STATUS,
DESCRIPTION)
VALUES(g_geo_phase_id,
p_assign_id,
p_person_id,
p_location,
p_id,
p_old_juri_code,
p_new_juri_code,
p_proc_type,
sysdate,
g_mode,
p_status,
p_description);
SELECT distinct paei.aei_information2, paei.aei_information13,
paei.assignment_id,
pmod.state_code||'-'||pmod.county_code||'-'
||pmod.new_city_code jd_code,
paf.person_id
FROM per_assignments_f paf,
pay_us_modified_geocodes pmod,
per_assignment_extra_info paei
WHERE paei.information_type = 'LOCALITY'
AND substr(paei.aei_information2,8,4) <> '0000'
AND pmod.city_name = paei.aei_information13
AND pmod.state_code = substr(paei.aei_information2,1,2)
AND pmod.county_code = substr(paei.aei_information2,4,3)
AND pmod.old_city_code = substr(paei.aei_information2,8,4)
AND pmod.process_type in ('UP','US','PU','D','SU','RP','RS')
AND paf.assignment_id = paei.assignment_id;
SELECT distinct ectr.jurisdiction_code, ectr.assignment_id,
pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code jd_code,
paf.person_id, pmod.new_city_code, pmod.process_type, ectr.emp_city_tax_rule_id
FROM per_all_assignments_f paf,
pay_us_emp_city_tax_rules_f ectr,
pay_us_modified_geocodes pmod
WHERE pmod.state_code = ectr.state_code
AND pmod.county_code = ectr.county_code
AND pmod.new_county_code is null
AND pmod.old_city_code = ectr.city_code
AND pmod.process_type in ('UP','US','PU','D','SU')
AND pmod.patch_name = p_patch_name
AND ectr.assignment_id = paf.assignment_id
AND pmod.city_name = nvl(p_city_name, pmod.city_name)
AND paf.assignment_id between P_ASSIGN_START and P_ASSIGN_END
AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.assignment_id = ectr.assignment_id
and pugu.new_juri_code = pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code
and pugu.old_juri_code = ectr.jurisdiction_code
and pugu.table_value_id is null
and pugu.table_name is null
and pugu.process_type = pmod.process_type
and pugu.process_mode = g_mode
and pugu.id = g_geo_phase_id
and ((p_api_mode = 'Y' and pugu.status = 'C') or
(p_api_mode = 'N' and pugu.status in ('A','C'))))
UNION ALL
SELECT distinct pac.context_value, pac.assignment_id,
pmod.new_county_code jd_code,
paf.person_id, pmod.new_city_code, pmod.process_type,
pac.context_id
FROM per_all_assignments_f paf,
pay_action_contexts pac,
pay_us_modified_geocodes pmod
WHERE pmod.state_code = 'CA'
AND pmod.county_code = pac.context_value
AND pac.context_id in (select context_id
from ff_contexts
where context_name = 'JURISDICTION_CODE')
AND pmod.patch_name = p_patch_name
AND pac.assignment_id = paf.assignment_id
AND paf.assignment_id between P_ASSIGN_START and P_ASSIGN_END ;
lv_update_prr varchar2(1);
SELECT distinct 'Y'
FROM pay_us_emp_city_tax_rules_f puecf
WHERE puecf.jurisdiction_code = p_old_juri_code
AND puecf.assignment_id = p_assign_id
AND puecf.emp_city_tax_rule_id = p_city_tax_record_id
AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.assignment_id = p_assign_id
and pugu.table_value_id = puecf.emp_city_tax_rule_id
and pugu.old_juri_code = p_old_juri_code
and pugu.table_name = 'PAY_US_EMP_CITY_TAX_RULES_F'
and pugu.process_mode = g_mode
and pugu.process_type = g_process_type
and pugu.id = g_geo_phase_id);
SELECT /*+ ORDERED */ distinct pev.screen_entry_value, pev.element_entry_id,
pev.input_value_id
FROM pay_element_entries_f pee,
pay_element_entry_values_f pev,
pay_input_values_f piv
WHERE pee.assignment_id = assign_id
AND pee.element_entry_id = pev.element_entry_id
AND pev.screen_entry_value = geocode
AND pev.input_value_id = piv.input_value_id
AND piv.name = 'Jurisdiction'
-- AND piv.legislation_code = 'US'
AND IS_US_OR_CA_LEGISLATION(piv.input_value_id) = piv.input_value_id
AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.assignment_id = assign_id
and pugu.table_value_id = pev.element_entry_id
and pugu.old_juri_code = geocode
and pugu.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
and pugu.process_mode = g_mode
and pugu.process_type = g_process_type
and pugu.id = g_geo_phase_id);
SELECT assignment_action_id
FROM pay_assignment_actions
WHERE assignment_id = assign_id;
SELECT distinct prr.run_result_id,
prr.assignment_action_id, prr.jurisdiction_code
FROM pay_run_results prr
WHERE prr.assignment_action_id = assign_action_id
AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.assignment_id = assign_id
and pugu.table_value_id = prr.run_result_id
and pugu.old_juri_code = prr.jurisdiction_code
and pugu.table_name = 'PAY_RUN_RESULTS'
and pugu.process_mode = g_mode
and pugu.process_type = g_process_type
and pugu.id = g_geo_phase_id);
SELECT pac.context_id,
pac.assignment_action_id
FROM pay_action_contexts pac,
pay_assignment_actions paa
WHERE paa.assignment_id = assign_id
AND pac.assignment_id = paa.assignment_id -- Bug# 3679984 added this to where clause
AND paa.assignment_action_id = pac.assignment_action_id
AND pac.context_id = context_id ;
SELECT distinct paa.assignment_action_id,
faic.context old_juri_code, faic.archive_item_id, ffc.context_id
FROM ff_archive_items fai,
ff_archive_item_contexts faic,
pay_assignment_actions paa,
pay_payroll_actions ppa,
ff_contexts ffc
WHERE ppa.report_type = 'YREND'
AND ppa.report_category = 'RT'
AND ppa.report_qualifier = 'FED'
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.assignment_id = assign_id
AND fai.context1 = paa.assignment_action_id
AND fai.archive_item_id = faic.archive_item_id
AND faic.context = geocode
AND ffc.context_id = faic.context_id
AND ffc.context_name = 'JURISDICTION_CODE'
AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.assignment_id = assign_id
and pugu.table_value_id = faic.archive_item_id
and pugu.old_juri_code = faic.context
and pugu.table_name = 'FF_ARCHIVE_ITEM_CONTEXTS'
and pugu.process_mode = g_mode
and pugu.process_type = g_process_type
and pugu.id = g_geo_phase_id);
SELECT distinct paa.assignment_action_id,
faic.context old_juri_code,
faic.archive_item_id,
ffc.context_id
FROM ff_archive_items fai,
ff_archive_item_contexts faic,
pay_assignment_actions paa,
pay_payroll_actions ppa,
ff_contexts ffc
WHERE ppa.report_type in ('T4', 'T4A', 'RL1', 'RL2', 'YREND')
and ppa.report_category in ('RT', 'CAEOYRL1', 'CAEOYRL2', 'CAEOY', 'CAEOY')
and report_qualifier in ('FED','CAEOYRL1', 'CAEOYRL2', 'CAEOY', 'CAEOY')
and ppa.payroll_action_id = paa.payroll_action_id
and paa.assignment_id = assign_id
and fai.context1 = paa.assignment_action_id
and fai.archive_item_id = faic.archive_item_id
and faic.context = geocode
and ffc.context_id = faic.context_id
and ffc.context_name = 'JURISDICTION_CODE'
and not exists (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.assignment_id = assign_id
and pugu.table_value_id = faic.archive_item_id
and pugu.old_juri_code = faic.context
and pugu.table_name = 'FF_ARCHIVE_ITEM_CONTEXTS'
and pugu.process_mode = g_mode
and pugu.process_type = g_process_type
and pugu.id = g_geo_phase_id);
SELECT distinct pbcv.context_id, pbcv.value, pbcv.latest_balance_id,
plb.assignment_action_id
FROM pay_assignment_actions paa,
pay_balance_context_values pbcv,
pay_person_latest_balances plb,
ff_contexts fcon
WHERE paa.assignment_id = assign_id
AND paa.assignment_action_id = plb.assignment_action_id
AND plb.person_id = personid
AND pbcv.latest_balance_id = plb.latest_balance_id
AND pbcv.value = geocode
AND fcon.context_id = pbcv.context_id
AND fcon.context_name = 'JURISDICTION_CODE'
AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.assignment_id = assign_id
and pugu.table_value_id = plb.latest_balance_id
and pugu.old_juri_code = geocode
and pugu.table_name = 'PAY_BALANCE_CONTEXT_VALUES'
and pugu.process_mode = g_mode
and pugu.process_type = g_process_type
and pugu.id = g_geo_phase_id);
SELECT distinct pbcv.context_id, pbcv.value, pbcv.latest_balance_id,
plb.assignment_action_id
FROM ff_contexts fcon,
pay_balance_context_values pbcv,
pay_assignment_latest_balances plb
WHERE plb.assignment_id = assign_id
AND pbcv.latest_balance_id = plb.latest_balance_id
AND pbcv.value = geocode
AND fcon.context_id = pbcv.context_id
AND fcon.context_name = 'JURISDICTION_CODE'
AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.assignment_id = assign_id
and pugu.table_value_id = plb.latest_balance_id
and pugu.old_juri_code = geocode
and pugu.table_name = 'PAY_BALANCE_CONTEXT_VALUES'
and pugu.process_mode = g_mode
and pugu.process_type = g_process_type
and pugu.id = g_geo_phase_id);
SELECT distinct pbcv.context_id, pbcv.value, pbcv.latest_balance_id,
plb.assignment_action_id
FROM ff_contexts fcon,
pay_balance_context_values pbcv,
pay_latest_balances plb
WHERE plb.assignment_id = assign_id
AND pbcv.latest_balance_id = plb.latest_balance_id
AND pbcv.value = geocode
AND fcon.context_id = pbcv.context_id
AND fcon.context_name = 'JURISDICTION_CODE'
AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.assignment_id = assign_id
and pugu.table_value_id = plb.latest_balance_id
and pugu.old_juri_code = geocode
and pugu.table_name = 'PAY_BALANCE_CONTEXT_VALUES'
and pugu.process_mode = g_mode
and pugu.process_type = g_process_type
and pugu.id = g_geo_phase_id);
SELECT 'Y' from PAY_US_GEO_UPDATE pugu
WHERE pugu.assignment_id = p_assign_id
AND pugu.process_mode = g_mode
AND pugu.id = g_geo_phase_id
AND pugu.table_name is null
AND pugu.status = 'P'
AND pugu.new_juri_code = p_new_juri_code
AND pugu.old_juri_code = p_old_juri_code;
IS select distinct 'Y'
from pay_us_geo_update pugu
where pugu.process_type in ('SU','US')
and pugu.table_name is null
and pugu.process_mode = g_mode
and pugu.id = g_geo_phase_id
and pugu.assignment_id = p_assign_id
and pugu.table_name is null
and pugu.table_value_id is null
and pugu.old_juri_code = p_old_juri_code
and pugu.new_juri_code = p_new_juri_code
and NOT EXISTS (select 'Y' from pay_us_modified_geocodes pmod
where pmod.state_code = substr(pugu.new_juri_code,1,2)
and pmod.county_code = substr(pugu.new_juri_code,4,3)
and pmod.old_city_code = substr(pugu.old_juri_code,8)
and pmod.new_city_code = substr(pugu.new_juri_code,8)
and pmod.process_type not in ('SU','US')
and pmod.patch_name = p_patch_name);
select piv.input_value_id
from pay_input_values_f piv
where piv.name in ('Jurisdiction', 'jd_rs', 'jd_wk')
and ( (piv.legislation_code in( 'US', 'CA')
)
OR (piv.legislation_code is null
and piv.business_group_id is not null
and exists (select 'Y'
from hr_organization_information hoi
where hoi.organization_id = piv.business_group_id
and hoi.org_information_context = 'Business Group Information'
and hoi.org_information9 in ('US','CA')
)
)
)
)
Loop
l_counter := l_counter+1;
IS select 'Y'
FROM pay_balance_batch_lines
WHERE jurisdiction_code = p_old_juri_code
AND assignment_id = p_assign_id
AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.assignment_id = p_assign_id
and pugu.old_juri_code = p_old_juri_code
and pugu.new_juri_code = p_new_juri_code
and pugu.table_name = 'PAY_BALANCE_BATCH_LINES'
and pugu.process_mode = g_mode
and pugu.process_type = g_process_type
and pugu.id = g_geo_phase_id);
hr_utility.trace('Before update of balance_batch_lines for assignment id: '||to_char(p_assign_id));
UPDATE pay_balance_batch_lines
SET jurisdiction_code = p_new_juri_code
WHERE jurisdiction_code = p_old_juri_code
AND assignment_id = p_assign_id;
IS select 'Y'
FROM pay_run_balances
WHERE jurisdiction_code = p_old_juri_code
AND assignment_id = p_assign_id
AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.assignment_id = p_assign_id
and pugu.old_juri_code = p_old_juri_code
and pugu.new_juri_code = p_new_juri_code
and pugu.table_name = 'PAY_RUN_BALANCES'
and pugu.process_mode = g_mode
and pugu.process_type = g_process_type
and pugu.id = g_geo_phase_id);
hr_utility.trace('Before update of pay_run_balances for assignment id: '||to_char(p_assign_id));
UPDATE pay_run_balances
SET jurisdiction_code = p_new_juri_code,
jurisdiction_comp3 = p_new_city_code
WHERE assignment_id = p_assign_id
AND jurisdiction_code = p_old_juri_code ;
SELECT count(*)
INTO l_run_balance_count
FROM pay_run_balances prb,
pay_assignment_actions paa
WHERE paa.assignment_id = p_assign_id
AND paa.assignment_action_id = prb.assignment_action_id
AND paa.assignment_id = prb.assignment_id
AND prb.jurisdiction_code = p_old_juri_code
AND rownum = 1;
hr_utility.trace('Before update of city tax records for assignment id: '||to_char(p_assign_id));
UPDATE pay_us_emp_city_tax_rules_f
SET jurisdiction_code = p_new_juri_code,
city_code = p_new_city_code
WHERE jurisdiction_code = p_old_juri_code
AND assignment_id = p_assign_id
AND emp_city_tax_rule_id = p_city_tax_record_id
AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.assignment_id = p_assign_id
and pugu.table_value_id = p_city_tax_record_id
and pugu.old_juri_code = p_old_juri_code
and pugu.table_name = 'PAY_US_EMP_CITY_TAX_RULES_F'
and pugu.process_mode = g_mode
and pugu.process_type = g_process_type
and pugu.id = g_geo_phase_id);
select distinct pect1.assignment_id, pect1.jurisdiction_code
from pay_us_emp_city_tax_rules_f pect1,
pay_us_emp_city_tax_rules_f pect2
where pect1.assignment_id = pect2.assignment_id
and pect1.jurisdiction_code = pect2.jurisdiction_code
and pect1.emp_city_tax_rule_id < pect2.emp_city_tax_rule_id
and pect1.assignment_id between P_ASSIGN_START and P_ASSIGN_END ;
/* select distinct pect1.assignment_id, pect1.jurisdiction_code
from pay_us_emp_city_tax_rules_f pect1
where pect1.assignment_id between P_ASSIGN_START and P_ASSIGN_END
and pect1.emp_city_tax_rule_id <
(select pect2.emp_city_tax_rule_id
from pay_us_emp_city_tax_rules_f pect2
where pect1.assignment_id = pect2.assignment_id
and pect1.jurisdiction_code = pect2.jurisdiction_code
);
DELETE FROM pay_us_emp_city_tax_rules_f pecto
WHERE pecto.rowid < (SELECT max(pecti.rowid)
FROM pay_us_emp_city_tax_rules_f pecti
WHERE pecti.assignment_id = pecto.assignment_id
AND pecti.assignment_id = dup_rec.assignment_id
AND pecti.jurisdiction_code = pecto.jurisdiction_code
AND pecti.jurisdiction_code = dup_rec.jurisdiction_code
AND pecti.emp_city_tax_rule_id <> pecto.emp_city_tax_rule_id)
AND pecto.assignment_id = dup_rec.assignment_id;
IS select distinct 'Y'
FROM pay_run_result_values prv
WHERE prv.result_value = p_new_juri_code
AND prv.run_result_id = p_run_result_id
AND EXISTS (SELECT 0
FROM pay_input_values_f piv
WHERE piv.input_value_id = prv.input_value_id
AND (piv.name = 'Jurisdiction' OR
piv.name = 'jd_rs' OR
piv.name = 'jd_wk')
-- AND piv.legislation_code = 'US')
AND IS_US_OR_CA_LEGISLATION(piv.input_value_id) = piv.input_value_id )
AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.assignment_id = p_assign_id
and pugu.table_value_id = prv.run_result_id
and pugu.old_juri_code = p_old_juri_code
and pugu.table_name = 'PAY_RUN_RESULT_VALUES'
and pugu.process_mode = g_mode
and pugu.process_type = g_process_type
and pugu.id = g_geo_phase_id);
IS select distinct 'Y'
FROM pay_run_results prr
WHERE prr.jurisdiction_code = p_new_juri_code
AND prr.run_result_id = p_run_result_id
AND prr.assignment_action_id = p_assign_act_id
AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.assignment_id = p_assign_id
and pugu.table_value_id = prr.run_result_id
and pugu.old_juri_code = p_old_juri_code
and pugu.table_name = 'PAY_RUN_RESULTS'
and pugu.process_mode = g_mode
and pugu.process_type = g_process_type
and pugu.id = g_geo_phase_id);
hr_utility.trace('Before update of run result values for assignment_action_id: '||to_char(p_assign_act_id));
UPDATE pay_run_result_values prv
SET prv.result_value = p_new_juri_code
WHERE prv.run_result_id = p_run_result_id
AND prv.result_value = p_old_juri_code
AND EXISTS (SELECT 0
FROM pay_input_values_f piv
WHERE piv.input_value_id = prv.input_value_id
AND (piv.name = 'Jurisdiction' OR
piv.name = 'jd_rs' OR
piv.name = 'jd_wk')
-- AND piv.legislation_code = 'US');
hr_utility.trace('After update of run result values for assignment_action_id: '||to_char(p_assign_act_id));
hr_utility.trace('Before update of run results for assignment_action_id: '||to_char(p_assign_act_id));
UPDATE pay_run_results
SET jurisdiction_code = p_new_juri_code
WHERE jurisdiction_code = p_old_juri_code
AND run_result_id = p_run_result_id
AND assignment_action_id = p_assign_act_id;
hr_utility.trace('After update of run results for assignment_action_id: '||to_char(p_assign_act_id));
IS select 'Y'
FROM pay_action_contexts
WHERE assignment_action_id = p_assign_act_id
AND assignment_id = p_assign_id
AND context_id = p_context_id
AND context_value = p_old_juri_code ;
hr_utility.trace('Before update of pay_action_contexts for assignment id: '||to_char(p_assign_id));
UPDATE pay_action_contexts
SET context_value = p_new_juri_code
WHERE context_value = p_old_juri_code
AND assignment_action_id = p_assign_act_id
AND context_id = p_context_id ;
hr_utility.trace('Before update of archive item contexts for assignment_id: '||to_char(p_assign_id));
UPDATE ff_archive_item_contexts ffaic
SET ffaic.context = p_new_juri_code
WHERE ffaic.context = p_old_juri_code
AND ffaic.context_id = p_context_id
AND ffaic.archive_item_id = p_archive_item_id;
hr_utility.trace('After update of archive item contexts for assignment_id: '||to_char(p_assign_id));
hr_utility.trace('Before update of element entry values for assignment_id: '||to_char(p_assign_id));
UPDATE pay_element_entry_values_f
SET screen_entry_value = p_new_juri_code
WHERE screen_entry_value = p_old_juri_code
AND input_value_id+0 = p_input_value_id
AND element_entry_id = p_ele_ent_id;
hr_utility.trace('After update of element entry values for assignment_id: '||to_char(p_assign_id));
hr_utility.trace('Before update of latest balances context for assignment_action_id: '||to_char(p_assign_act_id));
UPDATE pay_balance_context_values
SET value = p_new_juri_code
WHERE value = p_old_juri_code
AND context_id = p_context_id
AND latest_balance_id = p_lat_bal_id;
hr_utility.trace('After update of latest balances context for assignment_action_id: '||to_char(p_assign_act_id));
select pev.screen_entry_value sev, pev.element_entry_id eei
from pay_element_entry_values_f pev,
pay_input_values_f piv,
pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pee
where pee.assignment_id = p_assignment
and pel.element_link_id = pee.element_link_id
and pet.element_type_id = pel.element_type_id
and pet.element_name = 'VERTEX'
and pev.element_entry_id = pee.element_entry_id
and pev.screen_entry_value is not null
and piv.input_value_id = pev.input_value_id+0
and piv.element_type_id = pet.element_type_id
and piv.name = 'Jurisdiction'
and piv.legislation_code = 'US'
and pet.legislation_code = 'US'
order by 1,2;
select /*Bug 7592909*/distinct pev.screen_entry_value , pev.effective_start_date,
pev.effective_end_date
from pay_element_entry_values_f pev,
pay_input_values_f piv,
pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pef
where pef.element_entry_id = p_element_entry_id
and pel.element_link_id = pef.element_link_id
and pet.element_type_id = pel.element_type_id
and pet.element_name = 'VERTEX'
and pev.element_entry_id = pef.element_entry_id
and pev.screen_entry_value is not null
and piv.input_value_id = pev.input_value_id+0
and piv.element_type_id = pet.element_type_id
and piv.name = 'Percentage'
and piv.legislation_code = 'US'
and pet.legislation_code = 'US';
/* get the percentages for the record to be deleted */
open csr_get_percentage(j.eei);
update pay_element_entry_values_f pev
set pev.screen_entry_value = pev.screen_entry_value + l_percent
where pev.element_entry_id = l_prev_eleid
and pev.screen_entry_value is not null
and pev.input_value_id = (select distinct piv.input_value_id
from pay_input_values_f piv,
pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pef
where pef.element_entry_id =
l_prev_eleid
and pel.element_link_id =
pef.element_link_id
and pet.element_type_id =
pel.element_type_id
and pet.element_name = 'VERTEX'
and piv.element_type_id =
pet.element_type_id
and piv.name = 'Percentage'
and piv.legislation_code = 'US'
and pet.legislation_code = 'US')
/*Bug 7592909*/
and pev.effective_start_date=l_effective_start_date
and pev.effective_end_date=l_effective_end_date;
/* Now delete the current entry */
delete pay_element_entries_f
where element_entry_id = j.eei
and assignment_id = p_assignment_id;
PROCEDURE insert_ele_entries
(p_proc_type in varchar2,
p_assign_id in number,
p_person_id in number,
p_new_juri_code in varchar2,
p_old_juri_code in varchar2)
IS
-- Finds out if County Tax Record exists for this ASSIGNMENT_ID
cursor c_county_rec (p_assignment_id in number,
p_state_code in varchar2,
p_county_code in varchar2) is
select business_group_id
from pay_us_emp_county_tax_rules_f pecot
where pecot.assignment_id = p_assignment_id
and pecot.state_code = p_state_code
and pecot.county_code = p_county_code;
select business_group_id
from pay_us_emp_city_tax_rules_f pect
where pect.assignment_id = p_assignment_id
and pect.state_code = p_state_code
and pect.county_code = p_county_code
and pect.city_code = p_city_code;
select min(peft.effective_start_date),
max(peft.effective_end_date),
peft.business_group_id
from pay_us_emp_city_tax_rules_f peft
where peft.assignment_id = p_assignment_id
group by peft.business_group_id;
lc_insert_rec varchar2(1);
hr_utility.trace('Entering pay_us_geo_upd_pkg.insert_ele_entries');
lc_insert_rec := 'N';
hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',1);
hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',2);
hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',3);
hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',4);
hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',5);
hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',6);
lc_insert_rec := 'Y';
hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',7);
if lc_insert_rec = 'Y' then
hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',8);
hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',9);
hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',10);
hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',11);
pay_us_emp_dt_tax_rules.insert_def_city_rec(p_assign_id,
ld_eff_start_date,
ld_eff_end_date,
ln_state_code,
ln_county_code,
ln_city_code,
ln_business_group_id,
0);
hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',12);
hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',12);
p_mode => 'INSERT');
hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',13);
hr_utility.set_location('pay_us_geo_upd_pkg.insert_ele_entries',14);
p_proc_type => 'ELE_ENTRY_INSERT_NOT_REQD',
p_person_id => p_person_id,
p_assign_id => p_assign_id,
p_old_juri_code => null,
p_new_juri_code => p_new_juri_code,
p_location => 'PAY_ELEMENT_ENTRIES_F',
p_id => null);
END insert_ele_entries;
SELECT pus.state_code,
pus.state_name,
pusf.effective_start_date,
pusf.effective_end_date
FROM pay_us_states pus,
pay_us_emp_state_tax_rules_f pusf
WHERE pusf.assignment_id = p_assign_id
AND pusf.state_code = pus.state_abbrev
AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.table_name = 'PAY_ELEMENT_ENTRY_VALUES_F'
and pugu.process_type = 'PERCENTAGE_OVER_100'
and pugu.assignment_id = p_assign_id
and pugu.process_mode = g_mode
and pugu.process_type = g_process_type
and pugu.id = g_geo_phase_id);
SELECT sum(nvl(to_number(pev2.screen_entry_value),0))
FROM pay_input_values_f piv2,
pay_element_entry_values_f pev2,
pay_input_values_f piv1,
pay_element_entry_values_f pev1,
pay_element_types_f pet,
pay_element_links_f pel,
pay_element_entries_f pef
WHERE pef.assignment_id = p_assign_id
AND pef.creator_type = 'UT'
AND pef.element_link_id = pel.element_link_id
AND pel.element_type_id = pet.element_type_id
AND pet.element_name = 'VERTEX'
AND (
(start_date >= pef.effective_start_date AND
end_date <= pef.effective_end_date)
OR (start_date = pef.effective_end_date)
OR (end_date = pef.effective_start_date)
)
AND (pef.element_entry_id = pev1.element_entry_id
AND pef.effective_start_date = pev1.effective_start_date
AND pef.effective_end_date = pev1.effective_end_date
AND state_code = substr(pev1.screen_entry_value,1,2)
AND pev1.input_value_id = piv1.input_value_id
AND piv1.name = 'Jurisdiction'
AND piv1.legislation_code = 'US')
AND (pev2.element_entry_id = pev1.element_entry_id
AND pev2.effective_start_date = pev1.effective_start_date
AND pev2.effective_end_date = pev1.effective_end_date
AND pev2.screen_entry_value is not null
AND piv2.input_value_id = pev2.input_value_id
AND piv2.name = 'Percentage'
AND piv2.legislation_code = 'US');
SELECT ppf.person_id
INTO l_person_id
FROM per_all_people_f ppf,
per_all_assignments_f paf
WHERE ppf.person_id = paf.person_id
AND paf.assignment_id = p_assign_id
AND ppf.effective_start_date = (SELECT max(ppf2.effective_start_date)
FROM per_all_people_f ppf2
WHERE ppf2.person_id = ppf.person_id);
SELECT count(*)
INTO table_exist
FROM cat
WHERE table_name = tab_name;
but do not create another row in the pay_us_geo_update table */
ELSE
hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geocodes',40);
l_proc_stage := 'INSERT_ELEMENT_ENTRIES';
insert_ele_entries (
p_proc_type => main_proc_type,
p_assign_id => main_assign_id,
p_person_id => main_person_id,
p_new_juri_code => main_new_juri_code,
p_old_juri_code => main_old_juri_code);
SAVEPOINT GEO_UPDATE_SAVEPOINT;
l_text := 'UPDATE '||tab_name||
' SET jurisdiction_code = '''||main_new_juri_code||
''' WHERE assignment_id = '''||to_char(main_assign_id)||
''' AND jurisdiction_code = '''||main_old_juri_code||
'''';
SELECT 'Y'
INTO lv_update_prr
FROM dual
WHERE EXISTS (SELECT 0
FROM pay_us_city_tax_info_f
WHERE jurisdiction_code = main_old_juri_code)
OR EXISTS (SELECT 0
FROM pay_us_city_tax_info_f
WHERE jurisdiction_code = main_new_juri_code)
OR length(main_old_juri_code) = 2 ;
lv_update_prr := 'N';
IF lv_update_prr = 'Y' THEN
-- Bug 3319878 -- Opening cursor
OPEN paa_cur(main_assign_id);
UPDATE PAY_US_GEO_UPDATE
SET status = 'A', description = null
WHERE assignment_id = main_assign_id
AND old_juri_code = main_old_juri_code
AND new_juri_code = main_new_juri_code
AND table_name is null
AND table_value_id is null
AND status = 'P'
AND process_type = main_proc_type;
UPDATE PAY_US_GEO_UPDATE
SET status = 'C', description = null
WHERE assignment_id = main_assign_id
AND old_juri_code = main_old_juri_code
AND new_juri_code = main_new_juri_code
AND table_name is null
AND table_value_id is null
AND status in ('P','A')
AND process_type = main_proc_type;
rollback to GEO_UPDATE_SAVEPOINT;
UPDATE PAY_US_GEO_UPDATE
SET description = l_error_text
WHERE assignment_id = main_assign_id
AND old_juri_code = main_old_juri_code
AND new_juri_code = main_new_juri_code
AND table_name is null
AND table_value_id is null
AND old_juri_code = main_old_juri_code
AND new_juri_code = main_new_juri_code
AND status = 'P'
AND process_type = main_proc_type;
PROCEDURE update_taxability_rules(P_GEO_PHASE_ID IN NUMBER,
P_MODE IN VARCHAR2,
P_PATCH_NAME IN VARCHAR2)
IS
--Retrieve all changed geocodes on pay_taxability_rules table.
--Bug 3319878 -- Changed the cursor query to reduce cost.
--Bug 5042715 -- Added hints to reduce cost.
CURSOR ptax_cur IS
SELECT /*+index( pmod PAY_US_MODIFIED_GEOCODES_N2 ,
ptax PAY_TAXABILITY_RULES_UK)
use_nl(pmod ptax)*/
distinct ptax.jurisdiction_code
FROM pay_us_modified_geocodes pmod,
pay_taxability_rules ptax
WHERE ptax.jurisdiction_code = pmod.state_code||'-000-'||pmod.old_city_code
AND pmod.process_type in ('UP','RP')
AND pmod.patch_name = p_patch_name
AND substr(ptax.jurisdiction_code,8,4) <> '0000'
AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.table_name = 'PAY_TAXABILITY_RULES'
and pugu.new_juri_code = ptax.jurisdiction_code
and pugu.process_mode = g_mode
and pugu.process_type = g_process_type
and pugu.id = g_geo_phase_id
and rownum <2);
SELECT distinct ptax.jurisdiction_code
FROM pay_us_modified_geocodes pmod,
pay_taxability_rules ptax
WHERE pmod.state_code = 'CA'
AND ptax.jurisdiction_code = pmod.county_code || '000-0000'
AND pmod.patch_name = p_patch_name
AND ptax.legislation_code = 'CA' ;
hr_utility.trace('Entering pay_us_geo_upd_pkg.update_taxability_rules');
hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',1);
SELECT pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code,
process_type
INTO jd_code, l_proc_type
FROM pay_us_modified_geocodes pmod
WHERE pmod.state_code = substr(ptax_rec.jurisdiction_code,1,2)
AND pmod.old_city_code = substr(ptax_rec.jurisdiction_code,8,4)
AND pmod.process_type in ('UP','RP')
AND pmod.patch_name = p_patch_name
--city taxability rules don't carry a county-code so we have to pull the first
-- row in the case of a city that spans a county.
and rownum = 1;
hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',2);
select count(*) into l_count
from pay_taxability_rules ptax
where ptax.jurisdiction_code = substr(jd_code,1,2)||'-000-'||substr(jd_code,8,4);
UPDATE pay_taxability_rules ptax
SET ptax.jurisdiction_code = substr(jd_code,1,2)||'-000-'||
substr(jd_code,8,4)
WHERE ptax.jurisdiction_code = ptax_rec.jurisdiction_code;
hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',3);
hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',4);
hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',15);
SELECT pmod.new_county_code,
process_type
INTO jd_code, l_proc_type
FROM pay_us_modified_geocodes pmod
WHERE pmod.state_code = 'CA'
AND pmod.county_code = substr(ptax_ca_rec.jurisdiction_code,1,2)
AND pmod.patch_name = p_patch_name;
UPDATE pay_taxability_rules ptax
SET ptax.jurisdiction_code = jd_code||'-000-'||'0000'
WHERE ptax.jurisdiction_code = ptax_ca_rec.jurisdiction_code;
hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',20);
hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',25);
hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',5);
hr_utility.set_location('pay_us_geo_upd_pkg.update_taxability_rules',6);
fnd_file.put_line(fnd_file.log, 'Exception update_taxability_rules' );
END update_taxability_rules;
changes update does not happen in a given year. In order to overcome this limitation
if there are no assignments, we will be calling update_county_name procedure
from Pay_us_geocode_engine_pkg.geocode_upgrade procedure
Parameter P_CALL is used to differentiate the call from pay_us_geo_upd_pkg.action_creation
(Pass 'INTERNAL' to P_CALL) or pay_us_geocode_engine_pkg.geocode_upgrade (Pass 'EXTERNAL'
to P_CALL)*/
PROCEDURE update_county_name(P_GEO_PHASE_ID IN NUMBER,
P_MODE IN VARCHAR2,
P_PATCH_NAME IN VARCHAR2,
P_CALL IN VARCHAR2)
IS
l_error_message_text varchar2(240);
SELECT decode(pumg.state_code,'70','CA','US') country,
pumg.state_code,
pus.state_abbrev,
puc.county_code,
pumg.city_name old_county_name,
puc.county_name new_county_name
FROM pay_us_modified_geocodes pumg,
pay_us_states pus,
pay_us_counties puc
WHERE pus.state_code = puc.state_code
AND pus.state_code = pumg.state_code
AND puc.county_code = pumg.county_code
AND pumg.patch_name = P_PATCH_NAME
AND pumg.process_type = 'CN'
ORDER BY pus.state_code,puc.county_code;
hr_utility.trace('Entering pay_us_geo_upd_pkg.update_county_name');
hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',1);
UPDATE per_addresses
SET region_1 = l_county_name_change.new_county_name
WHERE region_1 = l_county_name_change.old_county_name
AND region_2 = l_county_name_change.state_abbrev
AND country = l_county_name_change.country;
UPDATE per_addresses
SET add_information19 = l_county_name_change.new_county_name
WHERE add_information19 = l_county_name_change.old_county_name
AND add_information17 = l_county_name_change.state_abbrev;
hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',2);
SELECT count(*) INTO l_count
FROM per_addresses
WHERE region_1 = l_county_name_change.old_county_name
AND region_2 = l_county_name_change.state_abbrev
AND country = l_county_name_change.country;
SELECT count(*) INTO l_override_count
FROM per_addresses
WHERE add_information19 = l_county_name_change.old_county_name
AND add_information17 = l_county_name_change.state_abbrev;
hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',3);
hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',4);
l_county_name_change.new_county_name||'. Corresponding Person Address Records Updated.';
UPDATE hr_locations_all
SET region_1 = l_county_name_change.new_county_name
WHERE region_1 = l_county_name_change.old_county_name
AND region_2 = l_county_name_change.state_abbrev
AND country = l_county_name_change.country;
UPDATE hr_locations_all
SET loc_information19 = l_county_name_change.new_county_name
WHERE loc_information19 = l_county_name_change.old_county_name
AND loc_information17 = l_county_name_change.state_abbrev;
hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',5);
SELECT count(*) INTO l_count
FROM hr_locations_all
WHERE region_1 = l_county_name_change.old_county_name
AND region_2 = l_county_name_change.state_abbrev
AND country = l_county_name_change.country;
SELECT count(*) INTO l_override_count
FROM hr_locations_all
WHERE LOC_INFORMATION19 = l_county_name_change.old_county_name
AND LOC_INFORMATION17 = l_county_name_change.state_abbrev;
hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',6);
hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',7);
l_county_name_change.new_county_name||'. Corresponding Location Address Records Updated.';
hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',8);
hr_utility.set_location('pay_us_geo_upd_pkg.update_county_name',11);
fnd_file.put_line(fnd_file.log, 'Exception update_county_name' );
END update_county_name;
the City Name we delivered earlier, it is necessary to update the City Name
details stored in other tables.
For each of the City Name that got modified, an entry will be created in table
PAY_US_MODIFIED_GEOCODES with process_type as 'CY'. It has Old City Name stored
in city_name field. The Jurisdiction Code details corresponding to the City
Name is also stored in PAY_US_MODIFIED_GEOCODES table.
Since for a given Jurisdiction Code we can have multiple cities (i.e Primary
city and Secondary Cities), it is necessary to have a mechanism to distinguish
for which City Name entry the name actually got modified.
For this purpose, another entry will be created in PAY_US_MODIFIED_GEOCODES
table with process_type as 'NC'. It has the New City corresponding to the
Old City that got updated. In case if there are multiple city name changes for
a same city code, in order to make it easy to identify which city name got
changed to which city name, the old city name will be saved in the field
PATCH_NAME. This PATCH_NAME will have the entry CITY_NAME_CHANGE_XXXX followed
by Colon (:) followed by the Old City Name.
So if it is necessary to identify what all City names got modified below Query
can be used to identify the list.
SELECT pumg1.patch_name,
pumg1.city_name old_city_name,
pumg2.city_name new_city_name
FROM pay_us_modified_geocodes pumg1,
pay_us_modified_geocodes pumg2
WHERE pumg1.process_type = 'CY'
AND pumg2.process_type = 'NC'
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 pumg1.patch_name
This procedure is called from pay_us_geo_upd_pkg.action_creation. For an Year, if there
are no assignments impacted by the city_name changes delivered the Submission of
Geocode Upgrade Concurrent Program will be skipped. So the intended City Name
changes update does not happen in a given year. In order to overcome this limitation
if there are no assignments, we will be calling update_city_name procedure
from Pay_us_geocode_engine_pkg.geocode_upgrade procedure
Parameter P_CALL is used to differentiate the call from pay_us_geo_upd_pkg.action_creation
(Pass 'INTERNAL' to P_CALL) or pay_us_geocode_engine_pkg.geocode_upgrade (Pass 'EXTERNAL'
to P_CALL)*/
PROCEDURE update_city_name(P_GEO_PHASE_ID IN NUMBER,
P_MODE IN VARCHAR2,
P_PATCH_NAME IN VARCHAR2,
P_CALL IN VARCHAR2)
IS
l_error_message_text varchar2(240);
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_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 decode(state_code,'70',county_abbrev,county_name)
FROM pay_us_counties
WHERE state_code = p_state_code
AND county_code = p_county_code;
SELECT city_name county_name
FROM pay_us_modified_geocodes
WHERE process_type = 'CN'
AND state_code = p_state_code
AND county_code = p_county_code
ORDER BY patch_name;
l_jurisdiction_code pay_us_geo_update.old_juri_code%TYPE;
hr_utility.trace('Entering pay_us_geo_upd_pkg.update_city_name');
hr_utility.set_location('pay_us_geo_upd_pkg.update_city_name',1);
INSERT INTO pay_us_geo_update
(
id,
assignment_id,
person_id,
table_name,
table_value_id,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
status,
description
)
SELECT DISTINCT
p_geo_phase_id,
NULL,
pa.person_id,
'PER_ADDRESSES',
pa.address_id,
l_jurisdiction_code,
l_jurisdiction_code,
'CY',
sysdate,
p_mode,
NULL,
'Address'||':'||l_city_name_change.old_city_name
FROM per_addresses pa
WHERE town_or_city = l_city_name_change.old_city_name
AND region_1 = l_county_name
AND NVL(region_2,l_city_name_change.state_abbrev) =
DECODE(country,'US',l_city_name_change.state_abbrev,NVL(region_2,l_city_name_change.state_abbrev))
AND country = l_city_name_change.country;
UPDATE per_addresses
SET town_or_city = l_city_name_change.new_city_name,
derived_locale = replace(derived_locale,
l_city_name_change.old_city_name,
l_city_name_change.new_city_name)
WHERE town_or_city = l_city_name_change.old_city_name
AND region_1 = l_county_name
AND NVL(region_2,l_city_name_change.state_abbrev) =
DECODE(country,'US',l_city_name_change.state_abbrev,NVL(region_2,l_city_name_change.state_abbrev))
AND country = l_city_name_change.country;
INSERT INTO pay_us_geo_update
(
id,
assignment_id,
person_id,
table_name,
table_value_id,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
status,
description
)
SELECT DISTINCT
p_geo_phase_id,
NULL,
pa.person_id,
'PER_ADDRESSES',
pa.address_id,
l_jurisdiction_code,
l_jurisdiction_code,
'CY',
sysdate,
p_mode,
NULL,
'Taxation Address'||':'||l_city_name_change.old_city_name
FROM per_addresses pa
WHERE add_information18 = l_city_name_change.old_city_name
AND add_information19 = l_county_name
AND add_information17 = l_city_name_change.state_abbrev;
UPDATE per_addresses
SET add_information18 = l_city_name_change.new_city_name
WHERE add_information18 = l_city_name_change.old_city_name
AND add_information19 = l_county_name
AND add_information17 = l_city_name_change.state_abbrev;
hr_utility.set_location('pay_us_geo_upd_pkg.update_city_name',2);
INSERT INTO pay_us_geo_update
(
id,
assignment_id,
person_id,
table_name,
table_value_id,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
status,
description
)
SELECT DISTINCT
p_geo_phase_id,
NULL,
NULL,
'HR_LOCATIONS_ALL',
hl.location_id,
l_jurisdiction_code,
l_jurisdiction_code,
'CY',
sysdate,
p_mode,
NULL,
'Address'||':'||l_city_name_change.old_city_name
FROM hr_locations_all hl
WHERE town_or_city = l_city_name_change.old_city_name
AND region_1 = l_county_name
AND NVL(region_2,l_city_name_change.state_abbrev) =
DECODE(country,'US',l_city_name_change.state_abbrev,NVL(region_2,l_city_name_change.state_abbrev))
AND country = l_city_name_change.country;
UPDATE hr_locations_all
SET town_or_city = l_city_name_change.new_city_name,
derived_locale = replace(derived_locale,
l_city_name_change.old_city_name,
l_city_name_change.new_city_name)
WHERE town_or_city = l_city_name_change.old_city_name
AND region_1 = l_county_name
AND NVL(region_2,l_city_name_change.state_abbrev) =
DECODE(country,'US',l_city_name_change.state_abbrev,NVL(region_2,l_city_name_change.state_abbrev))
AND country = l_city_name_change.country;
INSERT INTO pay_us_geo_update
(
id,
assignment_id,
person_id,
table_name,
table_value_id,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
status,
description
)
SELECT DISTINCT
p_geo_phase_id,
NULL,
NULL,
'HR_LOCATIONS_ALL',
hl.location_id,
l_jurisdiction_code,
l_jurisdiction_code,
'CY',
sysdate,
p_mode,
NULL,
'Payroll Tax Address'||':'||l_city_name_change.old_city_name
FROM hr_locations_all hl
WHERE loc_information18 = l_city_name_change.old_city_name
AND loc_information19 = l_county_name
AND loc_information17 = l_city_name_change.state_abbrev;
UPDATE hr_locations_all
SET loc_information18 = l_city_name_change.new_city_name
WHERE loc_information18 = l_city_name_change.old_city_name
AND loc_information19 = l_county_name
AND loc_information17 = l_city_name_change.state_abbrev;
INSERT INTO pay_us_geo_update
(
id,
assignment_id,
person_id,
table_name,
table_value_id,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
status,
description
)
SELECT DISTINCT
p_geo_phase_id,
NULL,
NULL,
'HR_ORGANIZATION_INFORMATION',
hoi.org_information_id,
l_jurisdiction_code,
l_jurisdiction_code,
'CY',
sysdate,
p_mode,
NULL,
'EEO_REPORT'||':'||'ORG_INFORMATION7'
FROM hr_organization_information hoi
WHERE org_information7 = l_city_name_change.old_city_name
AND org_information8 = l_city_name_change.state_abbrev
AND org_information_context = 'EEO_REPORT';
UPDATE hr_organization_information
SET org_information7 = l_city_name_change.new_city_name
WHERE org_information7 = l_city_name_change.old_city_name
AND org_information8 = l_city_name_change.state_abbrev
AND org_information_context = 'EEO_REPORT';
hr_utility.set_location('pay_us_geo_upd_pkg.update_city_name',3);
INSERT INTO pay_us_geo_update
(
id,
assignment_id,
person_id,
table_name,
table_value_id,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
status,
description
)
SELECT DISTINCT
p_geo_phase_id,
NULL,
pa.person_id,
'PER_ADDRESSES',
pa.address_id,
l_jurisdiction_code,
l_jurisdiction_code,
'CY',
sysdate,
p_mode,
NULL,
'Address'||':'||l_city_name_change.old_city_name
FROM per_addresses pa
WHERE town_or_city = l_city_name_change.old_city_name
AND region_1 = l_county_name
AND NVL(region_2,l_city_name_change.state_abbrev) =
DECODE(country,'US',l_city_name_change.state_abbrev,NVL(region_2,l_city_name_change.state_abbrev))
AND country = l_city_name_change.country;
INSERT INTO pay_us_geo_update
(
id,
assignment_id,
person_id,
table_name,
table_value_id,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
status,
description
)
SELECT DISTINCT
p_geo_phase_id,
NULL,
pa.person_id,
'PER_ADDRESSES',
pa.address_id,
l_jurisdiction_code,
l_jurisdiction_code,
'CY',
sysdate,
p_mode,
NULL,
'Taxation Address'||':'||l_city_name_change.old_city_name
FROM per_addresses pa
WHERE add_information18 = l_city_name_change.old_city_name
AND add_information19 = l_county_name
AND add_information17 = l_city_name_change.state_abbrev;
INSERT INTO pay_us_geo_update
(
id,
assignment_id,
person_id,
table_name,
table_value_id,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
status,
description
)
SELECT DISTINCT
p_geo_phase_id,
NULL,
NULL,
'HR_LOCATIONS_ALL',
hl.location_id,
l_jurisdiction_code,
l_jurisdiction_code,
'CY',
sysdate,
p_mode,
NULL,
'Address'||':'||l_city_name_change.old_city_name
FROM hr_locations_all hl
WHERE town_or_city = l_city_name_change.old_city_name
AND region_1 = l_county_name
AND NVL(region_2,l_city_name_change.state_abbrev) =
DECODE(country,'US',l_city_name_change.state_abbrev,NVL(region_2,l_city_name_change.state_abbrev))
AND country = l_city_name_change.country;
INSERT INTO pay_us_geo_update
(
id,
assignment_id,
person_id,
table_name,
table_value_id,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
status,
description
)
SELECT DISTINCT
p_geo_phase_id,
NULL,
NULL,
'HR_LOCATIONS_ALL',
hl.location_id,
l_jurisdiction_code,
l_jurisdiction_code,
'CY',
sysdate,
p_mode,
NULL,
'Payroll Tax Address'||':'||l_city_name_change.old_city_name
FROM hr_locations_all hl
WHERE loc_information18 = l_city_name_change.old_city_name
AND loc_information19 = l_county_name
AND loc_information17 = l_city_name_change.state_abbrev;
hr_utility.set_location('pay_us_geo_upd_pkg.update_city_name',4);
INSERT INTO pay_us_geo_update
(
id,
assignment_id,
person_id,
table_name,
table_value_id,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
status,
description
)
SELECT DISTINCT
p_geo_phase_id,
NULL,
pa.person_id,
'PER_ADDRESSES',
pa.address_id,
l_jurisdiction_code,
l_jurisdiction_code,
'CY',
sysdate,
p_mode,
NULL,
'Address'||':'||l_city_name_change.old_city_name
FROM per_addresses pa
WHERE town_or_city = l_city_name_change.old_city_name
AND region_1 = l_county_name
AND NVL(region_2,l_city_name_change.state_abbrev) =
DECODE(country,'US',l_city_name_change.state_abbrev,NVL(region_2,l_city_name_change.state_abbrev))
AND country = l_city_name_change.country;
INSERT INTO pay_us_geo_update
(
id,
assignment_id,
person_id,
table_name,
table_value_id,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
status,
description
)
SELECT DISTINCT
p_geo_phase_id,
NULL,
pa.person_id,
'PER_ADDRESSES',
pa.address_id,
l_jurisdiction_code,
l_jurisdiction_code,
'CY',
sysdate,
p_mode,
NULL,
'Taxation Address'||':'||l_city_name_change.old_city_name
FROM per_addresses pa
WHERE add_information18 = l_city_name_change.old_city_name
AND add_information19 = l_county_name
AND add_information17 = l_city_name_change.state_abbrev;
INSERT INTO pay_us_geo_update
(
id,
assignment_id,
person_id,
table_name,
table_value_id,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
status,
description
)
SELECT DISTINCT
p_geo_phase_id,
NULL,
NULL,
'HR_LOCATIONS_ALL',
hl.location_id,
l_jurisdiction_code,
l_jurisdiction_code,
'CY',
sysdate,
p_mode,
NULL,
'Address'||':'||l_city_name_change.old_city_name
FROM hr_locations_all hl
WHERE town_or_city = l_city_name_change.old_city_name
AND region_1 = l_county_name
AND NVL(region_2,l_city_name_change.state_abbrev) =
DECODE(country,'US',l_city_name_change.state_abbrev,NVL(region_2,l_city_name_change.state_abbrev))
AND country = l_city_name_change.country;
INSERT INTO pay_us_geo_update
(
id,
assignment_id,
person_id,
table_name,
table_value_id,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
status,
description
)
SELECT DISTINCT
p_geo_phase_id,
NULL,
NULL,
'HR_LOCATIONS_ALL',
hl.location_id,
l_jurisdiction_code,
l_jurisdiction_code,
'CY',
sysdate,
p_mode,
NULL,
'Payroll Tax Address'||':'||l_city_name_change.old_city_name
FROM hr_locations_all hl
WHERE loc_information18 = l_city_name_change.old_city_name
AND loc_information19 = l_county_name
AND loc_information17 = l_city_name_change.state_abbrev;
INSERT INTO pay_us_geo_update
(
id,
assignment_id,
person_id,
table_name,
table_value_id,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
status,
description
)
SELECT DISTINCT
p_geo_phase_id,
NULL,
NULL,
'HR_ORGANIZATION_INFORMATION',
hoi.org_information_id,
l_jurisdiction_code,
l_jurisdiction_code,
'CY',
sysdate,
p_mode,
NULL,
'EEO_REPORT'||':'||'ORG_INFORMATION7'
FROM hr_organization_information hoi
WHERE org_information7 = l_city_name_change.old_city_name
AND org_information8 = l_city_name_change.state_abbrev
AND org_information_context = 'EEO_REPORT';
hr_utility.set_location('pay_us_geo_upd_pkg.update_city_name',5);
hr_utility.set_location('pay_us_geo_upd_pkg.update_city_name',6);
hr_utility.set_location('pay_us_geo_upd_pkg.update_city_name',99);
fnd_file.put_line(fnd_file.log, 'Exception update_city_name' );
END update_city_name;
PROCEDURE update_org_info(P_GEO_PHASE_ID IN NUMBER,
P_MODE IN VARCHAR2,
P_PATCH_NAME IN VARCHAR2)
IS
--Retrieve all changed geocodes in the hr_organization_information table
CURSOR org_info_cur IS
SELECT distinct org_information1
FROM pay_us_modified_geocodes pmod,
hr_organization_information hoi
WHERE pmod.state_code = substr(hoi.org_information1,1,2)
AND pmod.county_code = substr(hoi.org_information1,4,3)
AND pmod.old_city_code = substr(hoi.org_information1,8,4)
AND pmod.process_type in ('UP','PU','RP')
AND pmod.patch_name = p_patch_name
AND hoi.org_information_context = 'Local Tax Rules'
AND NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.table_name = 'HR_ORGANIZATION_INFORMATION'
and pugu.new_juri_code = hoi.org_information1
and pugu.process_mode = g_mode
and pugu.process_type = g_process_type
and pugu.id = g_geo_phase_id);
SELECT distinct hoi.org_information1, hoi.org_information_id
FROM pay_us_modified_geocodes pmod,
hr_organization_information hoi
WHERE pmod.state_code = 'CA'
AND pmod.county_code = substr(hoi.org_information1,1,2)
AND pmod.patch_name = p_patch_name
AND hoi.org_information_context in
(
'Prov Reporting Est',
'Provincial Information',
'Provincial Reporting Info.',
'Provincial Employment Standard',
'Workers Comp Info.'
) ;
hr_utility.trace('Entering pay_us_geo_upd_pkg.update_org_info');
hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',1);
SELECT pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code,
process_type
INTO new_geocode, l_proc_type
FROM pay_us_modified_geocodes pmod
WHERE pmod.state_code = substr(org_info_rec.org_information1,1,2)
AND pmod.county_code = substr(org_info_rec.org_information1,4,3)
AND pmod.old_city_code = substr(org_info_rec.org_information1,8,4)
AND pmod.process_type in ('UP','PU','RP','U')
AND pmod.patch_name = p_patch_name;
hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',2);
UPDATE hr_organization_information
SET org_information1 = new_geocode
WHERE org_information1 = org_info_rec.org_information1
AND org_information_context = 'Local Tax Rules';
hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',3);
hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',4);
hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',5);
hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',15);
SELECT pmod.new_county_code,
process_type
INTO new_geocode, l_proc_type
FROM pay_us_modified_geocodes pmod
WHERE pmod.state_code = 'CA'
AND pmod.county_code = substr(org_info_ca_rec.org_information1,1,2)
AND pmod.patch_name = p_patch_name;
UPDATE hr_organization_information
SET org_information1 = new_geocode
WHERE org_information1 = org_info_ca_rec.org_information1
AND org_information_id = org_info_ca_rec.org_information_id
AND org_information_context in
(
'Prov Reporting Est',
'Provincial Information',
'Provincial Reporting Info.',
'Provincial Employment Standard',
'Workers Comp Info.'
) ;
hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',15);
hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',20);
hr_utility.set_location('pay_us_geo_upd_pkg.update_org_info',6);
fnd_file.put_line(fnd_file.log, 'Exception update_org_info' );
END update_org_info;
SELECT 'Y'
FROM dual
WHERE exists (SELECT /*+index(pugu PAY_US_GEO_UPDATE_N2) */ 'Y'
FROM PAY_US_GEO_UPDATE pugu
WHERE pugu.id = p_geo_phase_id
AND pugu.process_mode = p_mode
AND pugu.table_name is null
AND pugu.table_value_id is null
AND pugu.status <> 'C'
AND rownum < 2 );
SELECT ID
FROM pay_patch_status
WHERE patch_name = p_patch_name;
IF chk_last_api%NOTFOUND THEN /* Everything is complete we can update pay_patch_status to complete */
hr_utility.set_location('pay_us_geo_upd_pkg.upgrade_geo_api',50);
UPDATE pay_patch_status
SET status = 'C', phase = null
WHERE id = l_id;
PROCEDURE update_ca_emp_info (P_GEO_PHASE_ID IN NUMBER,
P_MODE IN VARCHAR2,
P_PATCH_NAME IN VARCHAR2)
IS
CURSOR canada_emp_fed_tax_cur IS
SELECT distinct cafed.employment_province, cafed.assignment_id
FROM pay_ca_emp_fed_tax_info_f cafed,
pay_us_modified_geocodes pmod
WHERE pmod.state_code = 'CA'
AND pmod.county_code = cafed.employment_province
AND pmod.patch_name = p_patch_name;
SELECT distinct caprov.province_code, caprov.assignment_id
FROM pay_ca_emp_prov_tax_info_f caprov,
pay_us_modified_geocodes pmod
WHERE pmod.state_code = 'CA'
AND pmod.county_code = caprov.province_code
AND pmod.patch_name = p_patch_name;
SELECT distinct caleg.jurisdiction_code
FROM pay_ca_legislation_info caleg,
pay_us_modified_geocodes pmod
WHERE pmod.state_code = 'CA'
AND pmod.county_code = caleg.jurisdiction_code
AND pmod.patch_name = p_patch_name ;
hr_utility.trace('Entering pay_us_geo_upd_pkg.update_ca_emp_info');
hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',1);
SELECT pmod.new_county_code,
pmod.process_type
INTO new_geocode, l_proc_type
FROM pay_us_modified_geocodes pmod
WHERE pmod.state_code = 'CA'
AND pmod.county_code = canada_emp_fed_rec.employment_province
AND pmod.patch_name = p_patch_name;
UPDATE pay_ca_emp_fed_tax_info_f
SET employment_province = new_geocode
WHERE employment_province = canada_emp_fed_rec.employment_province
AND assignment_id = canada_emp_fed_rec.assignment_id ;
hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',2);
hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',3);
hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',4);
SELECT pmod.new_county_code,
pmod.process_type
INTO new_geocode1, l_proc_type
FROM pay_us_modified_geocodes pmod
WHERE pmod.state_code = 'CA'
AND pmod.county_code = canada_emp_prov_rec.province_code
AND pmod.patch_name = p_patch_name;
UPDATE pay_ca_emp_prov_tax_info_f
SET province_code = new_geocode1
WHERE province_code = canada_emp_prov_rec.province_code
AND assignment_id = canada_emp_prov_rec.assignment_id ;
hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',5);
hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',6);
hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',7);
SELECT pmod.new_county_code,
pmod.process_type
INTO new_geocode2, l_proc_type
FROM pay_us_modified_geocodes pmod
WHERE pmod.state_code = 'CA'
AND pmod.county_code = canada_leg_info_rec.jurisdiction_code
AND pmod.patch_name = p_patch_name;
UPDATE pay_ca_legislation_info
SET jurisdiction_code = new_geocode2
WHERE jurisdiction_code = canada_leg_info_rec.jurisdiction_code ;
hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',8);
hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',9);
hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',10);
hr_utility.set_location('pay_us_geo_upd_pkg.update_ca_emp_info',11);
fnd_file.put_line(fnd_file.log, 'Exception update_ca_emp_info' );
END update_ca_emp_info ;
select
prb.run_balance_id, prb.jurisdiction_code, prb.jurisdiction_comp3
from pay_run_balances prb, pay_us_modified_geocodes pmod
Where prb.payroll_action_id = c_payroll_action_id
--between p_start_payroll_action and p_end_payroll_action
and prb.assignment_id is null
and pmod.state_code = substr(prb.jurisdiction_code,1,2)
and pmod.county_code = substr(prb.jurisdiction_code,4,3)
and pmod.old_city_code = substr(prb.jurisdiction_code,8,4)
and pmod.process_type in ('PU', 'UP')
and pmod.patch_name = p_patch_name;
/* and NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.process_type = g_process_type
and pugu.process_mode = g_mode
and pugu.assignment_id is null
and pugu.old_juri_code = prb.jurisdiction_code
and pugu.person_id = prb.run_balance_id
and pugu.table_name = 'PAY_RUN_BALANCES'
and pugu.id = g_geo_phase_id);*/
/* select /*+ ORDERED
index(pmod PAY_US_MODIFIED_GEOCODES_N1)
USE_NL(prb pdb pbd pmod) */
/* prb.run_balance_id,
prb.jurisdiction_code,
prb.jurisdiction_comp3
from pay_run_balances prb,
pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_us_modified_geocodes pmod
Where prb.payroll_action_id = c_payroll_action_id
--between p_start_payroll_action and p_end_payroll_action
and prb.assignment_id is null
and prb.defined_balance_id = pdb.defined_balance_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.dimension_level = 'GRP'
and pdb.legislation_code = 'US'
and pbd.database_item_suffix like '%JD%'
and pmod.state_code = substr(prb.jurisdiction_code,1,2)
and pmod.county_code = substr(prb.jurisdiction_code,4,3)
and pmod.old_city_code = substr(prb.jurisdiction_code,8,4)
and pmod.patch_name = p_patch_name
and NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.process_type = g_process_type
and pugu.process_mode = g_mode
and pugu.assignment_id is null
and pugu.old_juri_code = pmod.state_code || '-' || pmod.county_code || '-' || pmod.old_city_code --prb.jurisdiction_code
and pugu.person_id = prb.payroll_action_id
and pugu.table_name = 'PAY_RUN_BALANCES'
and pugu.id = g_geo_phase_id);
select
prb.run_balance_id, prb.jurisdiction_code, prb.jurisdiction_comp3
from pay_run_balances prb, pay_us_modified_geocodes pmod
Where prb.payroll_action_id = c_payroll_action_id
--between p_start_payroll_action and p_end_payroll_action
and prb.assignment_id is null
and pmod.state_code = 'CA'
and pmod.county_code = substr(prb.jurisdiction_code,1,2)
and pmod.process_type in ('PU', 'UP')
and pmod.patch_name = p_patch_name;
and NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.old_juri_code = prb.jurisdiction_code
and pugu.assignment_id is null
and pugu.person_id = prb.run_balance_id
and pugu.table_name = 'PAY_RUN_BALANCES'
and pugu.process_mode = g_mode
and pugu.process_type = g_process_type
and pugu.id = g_geo_phase_id); */
/*select /*+ ORDERED
index(pmod PAY_US_MODIFIED_GEOCODES_N1)
USE_NL(prb pdb pbd pmod) */
/* prb.run_balance_id,
prb.jurisdiction_code,
prb.jurisdiction_comp3
from pay_run_balances prb,
pay_defined_balances pdb,
pay_balance_dimensions pbd,
pay_us_modified_geocodes pmod
Where prb.payroll_action_id = c_payroll_action_id
--between p_start_payroll_action and p_end_payroll_action
and prb.assignment_id is null
and prb.defined_balance_id = pdb.defined_balance_id
and pdb.balance_dimension_id = pbd.balance_dimension_id
and pbd.dimension_level = 'GRP'
and pdb.legislation_code = 'CA'
and pbd.database_item_suffix like '%JD%'
and pmod.state_code = 'CA'
and pmod.county_code = substr(prb.jurisdiction_code,1,2)
and pmod.patch_name = p_patch_name
and NOT EXISTS (select 'Y' from PAY_US_GEO_UPDATE pugu
where pugu.old_juri_code = prb.jurisdiction_code
and pugu.assignment_id is null
and pugu.person_id = prb.payroll_action_id
and pugu.table_name = 'PAY_RUN_BALANCES'
and pugu.process_mode = g_mode
and pugu.process_type = g_process_type
and pugu.id = g_geo_phase_id); */
select pbg.legislation_code,
ppa.payroll_action_id
from per_business_groups pbg, pay_payroll_actions ppa
Where ppa.payroll_action_id between c_start_pactid and c_end_pactid
and pbg.business_group_id = ppa.business_group_id;
l_row_updated varchar2(1);
l_row_updated := 'N';
select 'Y'
into l_row_updated
from PAY_US_GEO_UPDATE pugu
where pugu.old_juri_code = group_level_bal_us_rec.jurisdiction_code
and pugu.assignment_id is null
and pugu.person_id = group_level_bal_us_rec.run_balance_id
and pugu.table_name = 'PAY_RUN_BALANCES'
and pugu.process_mode = g_mode
and pugu.process_type = g_process_type
and pugu.id = g_geo_phase_id;
SELECT pmod.state_code||'-'||pmod.county_code||'-'||pmod.new_city_code,
process_type, pmod.new_city_code
INTO l_geocode, l_proc_type, l_new_city_code
FROM pay_us_modified_geocodes pmod
WHERE pmod.state_code = substr(group_level_bal_us_rec.jurisdiction_code,1,2)
AND pmod.county_code = substr(group_level_bal_us_rec.jurisdiction_code,4,3)
AND pmod.old_city_code = substr(group_level_bal_us_rec.jurisdiction_code,8,4)
AND pmod.process_type in ('UP','PU','RP','U','US','D','SU')
AND pmod.patch_name = p_patch_name;
UPDATE pay_run_balances
SET jurisdiction_code = l_geocode,
jurisdiction_comp3 = l_new_city_code
WHERE payroll_action_id = group_level_bal_us_rec.run_balance_id
-- AND jurisdiction_comp3 = group_level_bal_us_rec.jurisdiction_comp3
AND jurisdiction_code = group_level_bal_us_rec.jurisdiction_code;
l_row_updated := 'N';
select 'Y'
into l_row_updated
from PAY_US_GEO_UPDATE pugu
where pugu.old_juri_code = group_level_bal_ca_rec.jurisdiction_code
and pugu.assignment_id is null
and pugu.person_id = group_level_bal_ca_rec.run_balance_id
and pugu.table_name = 'PAY_RUN_BALANCES'
and pugu.process_mode = g_mode
and pugu.process_type = g_process_type
and pugu.id = g_geo_phase_id;
SELECT pmod.new_county_code, pmod.process_type
INTO l_geocode, l_proc_type
FROM pay_us_modified_geocodes pmod
WHERE pmod.state_code = 'CA'
-- AND pmod.county_code = group_level_bal_ca_rec.jurisdiction_code
AND pmod.county_code = substr(group_level_bal_ca_rec.jurisdiction_code,1,2)
AND pmod.patch_name = p_patch_name;
UPDATE pay_run_balances
SET jurisdiction_code = l_geocode
WHERE payroll_action_id = group_level_bal_ca_rec.run_balance_id
-- AND jurisdiction_comp3 = group_level_bal_ca_rec.jurisdiction_comp3
-- AND jurisdiction_code = group_level_bal_ca_rec.jurisdiction_code
AND substr(jurisdiction_code,1,2) =
substr(group_level_bal_ca_rec.jurisdiction_code,1,2) ;
fnd_file.put_line(fnd_file.log, 'Exception update_ca_emp_info' );