[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 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 pay_assignment_actions_s.nextval
into lockingactid
from dual;
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)
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);
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)
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);
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 ;
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 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');
/* 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;
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' );