[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT city_name psdcode_change,
state_code,
county_code,
old_city_code
FROM pay_us_modified_geocodes pumg
WHERE process_type = 'PA'
AND NOT EXISTS
(SELECT NULL
FROM pay_us_geo_update pugu,
pay_patch_status pps
WHERE pugu.process_type = 'PA'
AND pugu.process_mode = 'UPGRADE'
AND pugu.process_date = pps.applied_date
AND pugu.old_juri_code = substr(pumg.city_name,1,6)
AND pugu.new_juri_code = substr(pumg.city_name,8,6)
AND pps.id = pugu.id
AND pugu.assignment_id = -1
AND pps.applied_date >= TO_DATE('01-01-2012','DD-MM-YYYY')
AND substr(pps.patch_name,1,10) = 'PSDUPGRADE'
AND pugu.status = 'C'
AND pps.status = 'C');
SELECT nvl(MAX(id),0)
INTO l_prev_upgrade_id
FROM pay_patch_status
WHERE patch_name LIKE 'PSDUPGRADE%'
AND description = 'UPGRADE'
AND status = 'C';
SELECT id,phase,applied_date
INTO l_id,l_phase,l_date
FROM pay_patch_status
WHERE patch_name LIKE '%PSDUPGRADE%'
AND description = 'UPGRADE'
AND id > l_prev_upgrade_id
AND nvl(status,'P') <> 'C';
UPDATE pay_patch_status
SET status = 'P'
WHERE id = l_id;
UPDATE pay_us_geo_update
SET status = 'C'
WHERE id = l_id
AND process_type = 'PA'
AND process_mode = l_mode
AND process_date = l_date
AND status = 'P';
UPDATE pay_us_geo_update
SET status = NULL
WHERE id = l_id
AND process_type = 'PA'
AND process_mode = l_mode
AND process_date = l_date
AND status = 'E';
SELECT MAX(pps.id)
INTO l_generate_id
FROM pay_patch_status pps
WHERE pps.patch_name LIKE 'PSDUPGRADE%'
AND pps.description = 'GENERATE'
AND pps.id > l_prev_upgrade_id
AND status = 'C';
SELECT applied_date
INTO l_generate_applied_date
FROM pay_patch_status pps
WHERE id = l_generate_id;
SELECT pay_patch_status_s.NEXTVAL,
'PSDUPGRADE_'||to_char(sysdate,'DDMONYYYYHHMISS'),
TRUNC(sysdate),
'P'
INTO l_id,l_patch_name,l_date,l_status
FROM DUAL;
INSERT INTO pay_patch_status
(id,patch_number,patch_name,phase,status,applied_date,legislation_code,description)
VALUES
(l_id,'1111111',l_patch_name,'UPGRADE',l_status,l_date,'US',l_mode);
UPDATE /*+PARALLEL*/ pay_us_geo_update
SET id = l_id,
process_date = l_date,
process_mode = l_mode,
status = NULL
WHERE id = l_generate_id
AND process_date = l_generate_applied_date
AND process_type = 'PA'
AND process_mode = 'GENERATE'
AND assignment_id <> -1;
SELECT id,phase,applied_date
INTO l_id,l_phase,l_date
FROM pay_patch_status
WHERE patch_name LIKE '%PSDUPGRADE%'
AND description = 'UPGRADE'
AND id > l_prev_upgrade_id
AND nvl(status,'P') <> 'C';
/*Delete Data from earlier GENERATE run before starting the current one*/
hr_utility.trace('Delete any Data generated earlier before the current one');
DELETE FROM pay_us_geo_update
WHERE process_type = 'PA'
AND process_mode = 'GENERATE'
AND id IN
(SELECT id
FROM pay_patch_status
WHERE patch_name LIKE 'PSDUPGRADE%'
AND legislation_code = 'US'
AND description = 'GENERATE'
AND id > l_prev_upgrade_id);
/*Update the Status of Previous GENERATE pay_patch_status entry to D indicating
deletion of the Generated Data.*/
UPDATE pay_patch_status
SET status = 'D',
phase = NULL
WHERE patch_name LIKE 'PSDUPGRADE%'
AND legislation_code = 'US'
AND id > l_prev_upgrade_id
AND description = 'GENERATE';
SELECT pay_patch_status_s.NEXTVAL,
'PSDUPGRADE_'||to_char(sysdate,'DDMONYYYYHHMISS'),
TRUNC(sysdate),
'P'
INTO l_id,l_patch_name,l_date,l_status
FROM DUAL;
INSERT INTO pay_patch_status
(id,patch_number,patch_name,phase,status,applied_date,legislation_code,description)
VALUES
(l_id,'1111111',l_patch_name,'VALIDATE',l_status,l_date,'US',l_mode);
SELECT pay_element_sets_s.nextval
INTO l_element_set_id
FROM DUAL;
INSERT INTO pay_element_sets
(element_set_id,
legislation_code,
element_set_name,
element_set_type)
VALUES
(l_element_set_id,
'US',
'US_JSD_ELEMENTS_'||p_pay_patch_status_id,
'D');
INSERT INTO pay_element_type_rules
(element_type_id,
element_set_id,
include_or_exclude)
SELECT DISTINCT pet.element_type_id,
l_element_set_id,
'I'
FROM pay_element_types_f pet,
pay_input_values_f piv,
per_business_groups pbg
WHERE NVL(pet.legislation_code,'US') = 'US'
AND NVL(pet.business_group_id,pbg.business_group_id) = pbg.business_group_id
AND pbg.legislation_code = 'US'
AND NVL(pbg.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'))
>= TO_DATE('01-01-2012','DD-MM-YYYY')
AND piv.element_type_id = pet.element_type_id
AND piv.name = 'Jurisdiction';
SELECT pay_us_geo_upd_pkg.get_parameter('MODE',ppa.legislative_parameters)
INTO l_mode
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_payroll_action_id;
SELECT assignment_action_id
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE paa.assignment_id = p_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type in ('Q','B','R','I','V')
AND ppa.effective_date >= to_date('01-01-2012','DD-MM-YYYY')
AND paa.action_status = 'C';
SELECT assignment_action_id
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE paa.assignment_id = p_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type in ('X')
AND ppa.report_type = 'XFR_INTERFACE'
AND ppa.effective_date >= to_date('01-01-2012','DD-MM-YYYY');
SELECT assignment_action_id
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE paa.assignment_id = p_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type in ('X')
AND ppa.report_type = 'PSD_MAG_XML'
AND ppa.effective_date >= to_date('01-01-2012','DD-MM-YYYY');
SELECT fai.archive_item_id
FROM ff_archive_items fai
WHERE fai.context1=l_assignment_action_id;
SELECT substr(pumg.city_name,1,6),
substr(pumg.city_name,8,6),
substr(pumg.city_name,15,5),
state_code||'-'||county_code||'-'||old_city_code
FROM pay_us_modified_geocodes pumg
WHERE pumg.process_type = 'PA'
AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
pumg.county_code,
pumg.old_city_code,
pumg.city_name)='Y'
ORDER BY pumg.state_code,pumg.county_code,pumg.old_city_code;
SELECT substr(pumg1.city_name,1,6),
substr(pumg1.city_name,8,6)
FROM pay_us_modified_geocodes pumg1,
pay_us_modified_geocodes pumg2
WHERE pumg1.process_type = 'PA'
AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg1.state_code,
pumg1.county_code,
pumg1.old_city_code,
pumg1.city_name)='Y'
AND pumg2.process_type = 'PA'
AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg2.state_code,
pumg2.county_code,
pumg2.old_city_code,
pumg2.city_name)='Y'
AND pumg1.rowid <> pumg2.rowid
AND substr(pumg1.city_name,1,6) = substr(pumg2.city_name,8,6)
AND substr(pumg2.city_name,1,6) = substr(pumg1.city_name,8,6)
ORDER BY 1,2;
SELECT person_id
INTO l_person_id
FROM per_all_assignments_f
WHERE assignment_id = p_assignment_id
AND rownum = 1;
INSERT INTO pay_us_geo_update
(id,
assignment_id,
person_id,
table_name,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
description)
SELECT DISTINCT p_id
,p_assignment_id
,l_person_id
,'PAY_US_ASG_REPORTING'
,puar.jurisdiction_code
,replace(puar.jurisdiction_code,substr(pumg.city_name,1,6),substr(pumg.city_name,8,6))
,pumg.process_type
,p_date
,p_mode
,substr(pumg.city_name,8,6)
FROM pay_us_asg_reporting puar,
pay_us_modified_geocodes pumg
WHERE puar.assignment_id = p_assignment_id
AND length(puar.jurisdiction_code) = 16
AND substr(puar.jurisdiction_code,1,2) = '39'
AND (substr(puar.jurisdiction_code,4,6) = substr(pumg.city_name,1,6) or
substr(puar.jurisdiction_code,11,6) = substr(pumg.city_name,1,6))
AND pumg.process_type = 'PA'
AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
pumg.county_code,
pumg.old_city_code,
pumg.city_name)='Y';
INSERT INTO pay_us_geo_update
(id,
assignment_id,
person_id,
table_value_id,
table_name,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
description)
SELECT DISTINCT p_id
,p_assignment_id
,l_person_id
,peev.element_entry_value_id
,'PAY_ELEMENT_ENTRY_VALUES_F'
,peev.screen_entry_value
,replace(peev.screen_entry_value,substr(pumg.city_name,1,6),substr(pumg.city_name,8,6))
,pumg.process_type
,p_date
,p_mode
,substr(pumg.city_name,8,6)
FROM pay_element_entries_f pee,
pay_element_entry_values_f peev,
pay_input_values_f piv,
pay_element_type_rules petr,
pay_us_modified_geocodes pumg
WHERE pee.assignment_id = p_assignment_id
AND pee.element_type_id = petr.element_type_id
AND peev.element_entry_id = pee.element_entry_id
AND petr.element_set_id = p_element_set_id
AND pee.element_type_id = piv.element_type_id
AND piv.name = 'Jurisdiction'
AND pee.effective_end_date >= to_date('01-01-2012','DD-MM-YYYY')
AND peev.effective_end_date >= to_date('01-01-2012','DD-MM-YYYY')
AND piv.effective_end_date >= to_date('01-01-2012','DD-MM-YYYY')
AND length(peev.screen_entry_value) = 16
AND substr(peev.screen_entry_value,1,2) = '39'
AND (substr(peev.screen_entry_value,4,6) = substr(pumg.city_name,1,6) or
substr(peev.screen_entry_value,11,6) = substr(pumg.city_name,1,6))
AND pumg.process_type = 'PA'
AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
pumg.county_code,
pumg.old_city_code,
pumg.city_name)='Y';
INSERT INTO pay_us_geo_update
(id,
assignment_id,
person_id,
table_value_id,
table_name,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
description)
SELECT DISTINCT p_id
,p_assignment_id
,l_person_id
,prr.run_result_id
,'PAY_RUN_RESULTS'
,prr.jurisdiction_code
,replace(prr.jurisdiction_code,substr(pumg.city_name,1,6),substr(pumg.city_name,8,6))
,pumg.process_type
,p_date
,p_mode
,substr(pumg.city_name,8,6)
FROM pay_run_results prr,
pay_us_modified_geocodes pumg,
pay_element_types_f pet,
pay_input_values_f piv
WHERE prr.assignment_action_id = tab_assignment_action_id(i)
AND length(prr.jurisdiction_code) = 16
AND substr(prr.jurisdiction_code,1,2) = '39'
AND (substr(prr.jurisdiction_code,4,6) = substr(pumg.city_name,1,6) or
substr(prr.jurisdiction_code,11,6) = substr(pumg.city_name,1,6))
AND pumg.process_type = 'PA'
AND pet.element_type_id = piv.element_type_id
AND piv.name = 'Jurisdiction'
AND pet.element_type_id = prr.element_type_id
AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
pumg.county_code,
pumg.old_city_code,
pumg.city_name)='Y';
INSERT INTO pay_us_geo_update
(id,
assignment_id,
person_id,
table_value_id,
table_name,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
description)
SELECT DISTINCT p_id
,p_assignment_id
,l_person_id
,prr.run_result_id
,'PAY_RUN_RESULT_VALUES'
,prv.result_value
,replace(prv.result_value,substr(pumg.city_name,1,6),substr(pumg.city_name,8,6))
,pumg.process_type
,p_date
,p_mode
,substr(pumg.city_name,8,6)
FROM pay_run_results prr,
pay_run_result_values prv,
pay_us_modified_geocodes pumg,
pay_element_types_f pet,
pay_input_values_f piv,
pay_element_type_rules petr
WHERE prr.assignment_action_id = tab_assignment_action_id(i)
AND prr.run_result_id = prv.run_result_id
AND length(prv.result_value) = 16
AND substr(prv.result_value,1,2) = '39'
AND (substr(prv.result_value,4,6) = substr(pumg.city_name,1,6) or
substr(prv.result_value,11,6) = substr(pumg.city_name,1,6))
AND pumg.process_type = 'PA'
AND pet.element_type_id = petr.element_type_id
AND petr.element_set_id = p_element_set_id
AND pet.element_type_id = prr.element_type_id
AND pet.legislation_code = 'US'
AND pet.element_type_id = piv.element_type_id
AND prv.input_value_id = piv.input_value_id
AND piv.name = 'Jurisdiction'
AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
pumg.county_code,
pumg.old_city_code,
pumg.city_name)='Y';
INSERT INTO pay_us_geo_update
(id,
assignment_id,
person_id,
table_value_id,
table_name,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
description)
SELECT DISTINCT p_id
,p_assignment_id
,l_person_id
,pai.action_information_id
,'PAY_ACTION_INFORMATION'
,pai.jurisdiction_code
,replace(pai.jurisdiction_code,substr(pumg.city_name,1,6),substr(pumg.city_name,8,6))
,pumg.process_type
,p_date
,p_mode
,substr(pumg.city_name,8,6)
FROM pay_action_information pai,
pay_us_modified_geocodes pumg
WHERE pai.action_context_id = tab_assignment_action_id(i)
AND pai.action_context_type = 'AAP'
AND pai.action_information_category = 'US PSD'
AND length(pai.jurisdiction_code) = 16
AND substr(pai.jurisdiction_code,1,2) = '39'
AND (substr(pai.jurisdiction_code,4,6) = substr(pumg.city_name,1,6) or
substr(pai.jurisdiction_code,11,6) = substr(pumg.city_name,1,6))
AND pumg.process_type = 'PA'
AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
pumg.county_code,
pumg.old_city_code,
pumg.city_name)='Y';
INSERT INTO pay_us_geo_update
(id,
assignment_id,
person_id,
table_value_id,
table_name,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
description)
SELECT DISTINCT p_id
,p_assignment_id
,l_person_id
,faic.archive_item_id
,'FF_ARCHIVE_ITEM_CONTEXTS'
,faic.context
,replace(faic.context,substr(pumg.city_name,1,6),substr(pumg.city_name,8,6))
,pumg.process_type
,p_date
,p_mode
,substr(pumg.city_name,8,6)
FROM ff_archive_item_contexts faic,
pay_us_modified_geocodes pumg
WHERE faic.archive_item_id = l_archive_item_id
AND length(faic.context) = 16
AND substr(faic.context,1,2) = '39'
AND (substr(faic.context,4,6) = substr(pumg.city_name,1,6) or
substr(faic.context,11,6) = substr(pumg.city_name,1,6))
AND pumg.process_type = 'PA'
AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
pumg.county_code,
pumg.old_city_code,
pumg.city_name)='Y';
do any update. So need to delete that assignment updates for that PSD Update. We are doing this
check here instead of in the beginning because we do not want to miss any other PSD Code updates
for this assignment*/
DELETE FROM pay_us_geo_update pugu
WHERE pugu.process_type = 'PA'
AND pugu.process_mode = p_mode
AND pugu.process_date = p_date
AND pugu.id = p_id
AND pugu.assignment_id = p_assignment_id
AND EXISTS
(SELECT NULL
FROM pay_us_emp_city_tax_rules_f pect,
pay_us_city_school_dsts pcsd,
pay_us_modified_geocodes pumg
WHERE pect.assignment_id = p_assignment_id
AND pect.jurisdiction_code = pumg.state_code||'-'||pumg.county_code||'-'||pumg.old_city_code
AND pugu.description = substr(pumg.city_name,8,6)
AND nvl(pect.school_district_code,substr(pumg.city_name,15,5)) <> substr(pumg.city_name,15,5)
AND pumg.process_type = 'PA'
AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
pumg.county_code,
pumg.old_city_code,
pumg.city_name)='Y'
AND pcsd.state_code = pumg.state_code
AND pcsd.county_code = pumg.county_code
AND pcsd.city_code = pumg.old_city_code
AND pcsd.school_dst_code <> substr(pumg.city_name,15,5));
UPDATE pay_us_geo_update
SET new_juri_code = replace(new_juri_code,l_old_psd_code,l_new_psd_code)
WHERE process_type = 'PA'
AND process_mode = p_mode
AND process_date = p_date
AND id = p_id
AND assignment_id = p_assignment_id
AND description <> l_old_psd_code
AND instr(old_juri_code,l_old_psd_code) > 0;
UPDATE pay_us_geo_update
SET new_juri_code = substr(old_juri_code,1,3)||substr(old_juri_code,11,6)||'-'||substr(old_juri_code,4,6)
WHERE process_type = 'PA'
AND process_mode = p_mode
AND process_date = p_date
AND id = p_id
AND assignment_id = p_assignment_id
AND instr(old_juri_code,l_old_psd_code) > 0
AND instr(old_juri_code,l_new_psd_code) > 0
AND (description = l_old_psd_code OR
description = l_new_psd_code);
SELECT DISTINCT
pugu.table_name,
pugu.table_value_id,
pugu.old_juri_code,
pugu.new_juri_code,
decode(pugu.table_name,
'PAY_US_ASG_REPORTING',1,
'PAY_ACTION_INFORMATION',2,
'PAY_ELEMENT_ENTRY_VALUES_F',3,
'PAY_RUN_RESULTS',4,
'PAY_RUN_RESULT_VALUES',5,
'FF_ARCHVIE_ITEM_CONTEXTS', 6) table_order -- Added for bug 14213838.
FROM pay_us_geo_update pugu
WHERE pugu.id = p_id
AND pugu.process_type = 'PA'
AND pugu.process_mode = p_mode
AND pugu.process_date = p_date
AND pugu.assignment_id = p_assignment_id
AND nvl(pugu.status,'P') <> 'C'
ORDER BY table_order;
SELECT COUNT(0)
FROM (SELECT DISTINCT assignment_id
FROM pay_us_geo_update pugu
WHERE pugu.id = p_id
AND pugu.process_type = 'PA'
AND pugu.process_mode = p_mode
AND pugu.process_date = p_date
AND pugu.assignment_id > 0
AND NVL(pugu.status,'NULL') = DECODE(p_status,'ALL',NVL(pugu.status,'NULL'),p_status));
UPDATE pay_us_asg_reporting
SET jurisdiction_code= l_new_jurisdiction_code
WHERE assignment_id = p_assignment_id
AND jurisdiction_code = l_old_jurisdiction_code;
UPDATE pay_element_entry_values_f
SET screen_entry_value = l_new_jurisdiction_code
WHERE element_entry_value_id = l_table_value_id
AND screen_entry_value = l_old_jurisdiction_code;
UPDATE pay_run_results
SET jurisdiction_code = l_new_jurisdiction_code
WHERE run_result_id = l_table_value_id
AND jurisdiction_code = l_old_jurisdiction_code;
UPDATE pay_run_result_values
SET result_value = l_new_jurisdiction_code
WHERE run_result_id = l_table_value_id
AND result_value = l_old_jurisdiction_code;
UPDATE pay_action_information
SET jurisdiction_code = l_new_jurisdiction_code
WHERE action_information_id = l_table_value_id
AND action_context_type = 'AAP'
AND action_information_category = 'US PSD'
AND jurisdiction_code = l_old_jurisdiction_code;
UPDATE ff_archive_item_contexts
SET context = l_new_jurisdiction_code
WHERE archive_item_id = l_table_value_id
AND context = l_old_jurisdiction_code;
to retain single record. Below delete statement achieves this.*/
DELETE FROM pay_us_asg_reporting
WHERE ROWID IN (
SELECT a.ROWID
FROM pay_us_asg_reporting a,
pay_us_asg_reporting b
WHERE a.assignment_id = p_assignment_id
AND a.assignment_id = b.assignment_id
AND a.tax_unit_id=b.tax_unit_id
AND a.jurisdiction_code=b.jurisdiction_code
AND a.rowid > b.rowid);
/*As we are done with the processing of an assignment, we will update the status to P*/
UPDATE pay_us_geo_update
SET status = 'P'
WHERE id = p_id
AND process_mode = p_mode
AND process_date = p_date
AND process_type = 'PA'
AND assignment_id = p_assignment_id;
UPDATE pay_us_geo_update
SET status = 'E'
WHERE id = p_id
AND process_mode = p_mode
AND process_date = p_date
AND process_type = 'PA'
AND assignment_id = p_assignment_id;
SELECT pay_us_geo_upd_pkg.get_parameter('MODE',ppa.legislative_parameters)
INTO l_mode
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = pactid;
sqlstr := ' SELECT DISTINCT paf.person_id
FROM per_all_assignments_f paf,
pay_us_emp_city_tax_rules_f pect,
pay_us_modified_geocodes pumg
WHERE pumg.process_type = ''PA''
AND pect.assignment_id = paf.assignment_id
AND pect.state_code = pumg.state_code
AND pect.county_code = pumg.county_code
AND pect.city_code = pumg.old_city_code
AND :pactid IS NOT NULL
AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
pumg.county_code,
pumg.old_city_code,
pumg.city_name) = ''Y''
ORDER BY paf.person_id';
/*Under UPGRADE Mode, we already have Assignment changes present in pay_us_geo_update
table. So we will use the pay_us_geo_update table as source for Range Cursor*/
SELECT id,applied_date
INTO l_id,l_date
FROM pay_patch_status
WHERE patch_name LIKE 'PSDUPGRADE%'
AND legislation_code = 'US'
AND status = 'P'
AND description = l_mode;
sqlstr := ' SELECT DISTINCT pugu.person_id
FROM pay_us_geo_update pugu
WHERE pugu.id = '||l_id||
' AND pugu.process_type = ''PA'' '||
'AND pugu.process_mode = '''||l_mode||''' '||
'AND pugu.process_date = TO_DATE('''||TO_CHAR(l_date,'DD-MM-YYYY')||''',''DD-MM-YYYY'')'||
' AND nvl(pugu.status,''P'') <> ''C''
AND :pactid IS NOT NULL
AND pugu.assignment_id > 0
ORDER BY pugu.person_id';
sqlstr := ' SELECT distinct paf.person_id
FROM per_all_assignments_f paf
WHERE 1=2
AND :pactid IS NOT NULL';
SELECT pay_us_geo_upd_pkg.get_parameter('MODE',ppa.legislative_parameters)
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_payroll_action_id;
SELECT DISTINCT ectr.assignment_id
FROM per_all_assignments_f paf,
pay_us_emp_city_tax_rules_f ectr,
pay_us_modified_geocodes pumg
WHERE pumg.state_code = ectr.state_code
AND pumg.county_code = ectr.county_code
AND pumg.old_city_code = ectr.city_code
AND pumg.process_type = 'PA'
AND ectr.assignment_id = paf.assignment_id
AND paf.person_id BETWEEN p_stperson AND p_endperson
AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
pumg.county_code,
pumg.old_city_code,
pumg.city_name)='Y';
p_id pay_us_geo_update.id%TYPE,
p_date DATE,
p_mode pay_us_geo_update.process_mode%TYPE
) IS
SELECT DISTINCT pugu.assignment_id
FROM pay_us_geo_update pugu,
per_all_assignments_f paf
WHERE pugu.id = p_id
AND pugu.process_type = 'PA'
AND pugu.process_mode = p_mode
AND pugu.process_date = p_date
AND nvl(pugu.status,'P') <> 'C'
AND paf.assignment_id = pugu.assignment_id
AND paf.assignment_id > 0
AND paf.person_id BETWEEN p_stperson AND p_endperson
ORDER BY pugu.assignment_id;
SELECT pay_assignment_actions_s.nextval
INTO l_locking_action_id
FROM DUAL;
SELECT id,applied_date
INTO l_id,l_date
FROM pay_patch_status
WHERE patch_name LIKE 'PSDUPGRADE%'
AND legislation_code = 'US'
AND status = 'P'
AND description = l_mode;
SELECT pay_assignment_actions_s.nextval
INTO l_locking_action_id
FROM DUAL;
SELECT paa.payroll_action_id,
paa.object_id,
paa.object_type
FROM pay_temp_object_actions paa
WHERE paa.object_action_id = p_assignment_action_id;
SELECT pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_payroll_action_id;
SELECT id,applied_date
INTO l_id,l_date
FROM pay_patch_status
WHERE patch_name LIKE 'PSDUPGRADE%'
AND legislation_code = 'US'
AND status = 'P'
AND description = l_mode;
SELECT element_set_id
INTO l_element_set_id
FROM pay_element_sets
WHERE element_set_name = 'US_JSD_ELEMENTS_'||l_id
AND legislation_code = 'US';
SELECT DISTINCT pbg.business_group_id,
pbg.name,
pbv.balance_validation_id
FROM per_business_groups pbg,
per_all_assignments_f paf,
pay_us_geo_update pugu,
pay_balance_types pbt,
pay_defined_balances pdb,
pay_balance_validation pbv
WHERE pugu.id = p_id
AND pugu.process_type = 'PA'
AND pugu.process_mode = p_mode
AND pugu.process_date = p_date
AND decode(p_mode,'GENERATE','C',pugu.status) = 'C'
AND pbg.business_group_id = paf.business_group_id
AND pugu.assignment_id = paf.assignment_id
AND pbg.legislation_code = 'US'
AND pbt.balance_name in
('City PSD Subj Whable',
'City PSD Withheld',
'City RS PSD Subj Whable',
'City RS PSD Withheld',
'City WK PSD Subj Whable',
'City WK PSD Withheld',
'PSD Subj Whable',
'PSD Withheld',
'School PSD Withheld',
'School RS PSD Withheld',
'School WK PSD Withheld')
AND pbt.balance_type_id = pdb.balance_type_id
AND pdb.save_run_balance = 'Y'
AND pbt.legislation_code = 'US'
AND pdb.defined_balance_id = pbv.defined_balance_id
AND pbv.business_group_id = pbg.business_group_id
ORDER BY pbg.business_group_id;
SELECT pbt.balance_name,pbd.dimension_name
FROM pay_balance_types pbt,
pay_balance_dimensions pbd,
pay_defined_balances pdb
WHERE pbt.balance_name in
('City PSD Subj Whable',
'City PSD Withheld',
'City RS PSD Subj Whable',
'City RS PSD Withheld',
'City WK PSD Subj Whable',
'City WK PSD Withheld',
'PSD Subj Whable',
'PSD Withheld',
'School PSD Withheld',
'School RS PSD Withheld',
'School WK PSD Withheld')
AND pbt.balance_type_id = pdb.balance_type_id
AND pdb.save_run_balance = 'Y'
AND pbt.legislation_code = 'US'
AND pdb.balance_dimension_id = pbd.balance_dimension_id
ORDER BY pbt.balance_name,pbd.dimension_name;
SELECT id,applied_date
INTO l_id,l_date
FROM pay_patch_status
WHERE patch_name LIKE 'PSDUPGRADE%'
AND legislation_code = 'US'
AND status = 'P'
AND description = p_mode;
UPDATE pay_balance_validation
SET run_balance_status = 'I'
WHERE balance_validation_id = l_balance_validation_id;
SELECT COUNT(0)
FROM (SELECT DISTINCT assignment_id
FROM pay_us_geo_update pugu
WHERE pugu.id = p_id
AND pugu.process_type = 'PA'
AND pugu.process_mode = p_mode
AND pugu.process_date = p_date
AND pugu.assignment_id > 0
AND NVL(pugu.status,'NULL') = DECODE(p_status,'ALL',NVL(pugu.status,'NULL'),p_status));
SELECT pay_us_geo_upd_pkg.get_parameter('MODE',PPa.legislative_parameters)
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_payroll_action_id;
SELECT DISTINCT
ppa.payroll_action_id
FROM pay_payroll_actions ppa
WHERE ppa.report_type='PSD_MAG_XML';
SELECT DISTINCT
pugu.table_name,
pugu.table_value_id,
pugu.old_juri_code,
pugu.new_juri_code
FROM pay_us_geo_update pugu
WHERE pugu.table_name = 'HR_ORGANIZATION_INFORMATION'
AND pugu.process_type = 'PA'
AND pugu.process_mode = p_mode
AND pugu.process_date = p_date
AND nvl(pugu.status,'P') <> 'C';
SELECT DISTINCT
pugu.table_name,
pugu.table_value_id,
pugu.old_juri_code,
pugu.new_juri_code
FROM pay_us_geo_update pugu
WHERE pugu.table_name = 'FF_ARCHIVE_ITEMS'
AND pugu.process_type = 'PA'
AND pugu.process_mode = p_mode
AND pugu.process_date = p_date
AND nvl(pugu.status,'P') <> 'C';
SELECT id,applied_date
INTO l_id,l_date
FROM pay_patch_status
WHERE patch_name LIKE 'PSDUPGRADE%'
AND legislation_code = 'US'
AND status = 'P'
AND description = l_mode;
SELECT user_entity_id
INTO l_er_psd_entity_id
FROM ff_user_entities
WHERE user_entity_name='A_PSD_REPORTING_RULES_ORG_EMPLOYER_PSD_CODE';
INSERT INTO pay_us_geo_update
(id,
assignment_id,
table_value_id,
table_name,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
description)
SELECT DISTINCT l_id
,l_psd_pact_id
,fai.archive_item_id
,'FF_ARCHIVE_ITEMS'
,fai.value
,replace(fai.value,substr(pumg.city_name,1,6),substr(pumg.city_name,8,6))
,pumg.process_type
,l_date
,l_mode
,substr(pumg.city_name,8,6)
FROM ff_archive_items fai,
pay_us_modified_geocodes pumg
WHERE fai.context1 = l_psd_pact_id
AND fai.user_entity_id = l_er_psd_entity_id
AND fai.value= substr(pumg.city_name,1,6)
AND pumg.process_type = 'PA'
AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
pumg.county_code,
pumg.old_city_code,
pumg.city_name)='Y';
INSERT INTO pay_us_geo_update
(id,
assignment_id,
table_value_id,
table_name,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
description)
SELECT DISTINCT l_id
,hou.organization_id
,hoi.org_information_id
,'HR_ORGANIZATION_INFORMATION'
,hoi.org_information10
,replace(hoi.org_information10,substr(pumg.city_name,1,6),substr(pumg.city_name,8,6))
,pumg.process_type
,l_date
,l_mode
,substr(pumg.city_name,8,6)
FROM hr_organization_information hoi,
hr_organization_units hou,
pay_us_modified_geocodes pumg
WHERE hoi.org_information_context='PSD Reporting Rules'
AND substr(hoi.org_information10,1,6) = substr(pumg.city_name,1,6)
AND pumg.process_type = 'PA'
AND hou.organization_id =hoi.organization_id
AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
pumg.county_code,
pumg.old_city_code,
pumg.city_name)='Y';
DELETE FROM pay_element_type_rules
WHERE element_set_id IN
(SELECT element_set_id
FROM pay_element_sets
WHERE element_set_name = 'US_JSD_ELEMENTS_'||l_id);
DELETE FROM pay_element_sets
WHERE legislation_code = 'US'
AND element_set_name = 'US_JSD_ELEMENTS_'||l_id;
UPDATE pay_patch_status
SET phase = NULL,
status = 'C'
WHERE legislation_code = 'US'
AND patch_name like 'PSDUPGRADE%'
AND status = 'P';
UPDATE hr_organization_information
SET org_information10 = l_new_jurisdiction_code
WHERE org_information_id = l_table_value_id
AND org_information10 = l_old_jurisdiction_code;
UPDATE pay_us_geo_update
SET status = 'P'
WHERE id = l_id
AND process_mode = l_mode
AND process_date = l_date
AND process_type = 'PA'
AND table_value_id = l_table_value_id;
UPDATE ff_archive_items
SET value = l_new_jurisdiction_code
WHERE archive_item_id = l_table_value_id
AND value = l_old_jurisdiction_code;
UPDATE pay_us_geo_update
SET status = 'P'
WHERE id = l_id
AND process_mode = l_mode
AND process_date = l_date
AND process_type = 'PA'
AND table_value_id = l_table_value_id;
UPDATE pay_us_geo_update
SET status = 'C'
WHERE id = l_id
AND process_type = 'PA'
AND process_mode = l_mode
AND process_date = l_date
AND status = 'P';
UPDATE pay_us_geo_update
SET status = 'E'
WHERE id = l_id
AND process_type = 'PA'
AND process_mode = l_mode
AND process_date = l_date
AND assignment_id IN
(SELECT object_id
FROM pay_temp_object_actions
WHERE payroll_action_id = p_payroll_action_id
AND action_status = 'E');
INSERT INTO pay_us_geo_update
(id,
assignment_id,
old_juri_code,
new_juri_code,
process_type,
process_date,
process_mode,
status)
SELECT l_id,
-1,
substr(pumg.city_name,1,6),
substr(pumg.city_name,8,6),
'PA',
l_date,
l_mode,
'C'
FROM pay_us_modified_geocodes pumg
WHERE pumg.process_type = 'PA'
AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
pumg.county_code,
pumg.old_city_code,
pumg.city_name)='Y';
UPDATE pay_patch_status
SET phase = NULL,
status = 'C'
WHERE legislation_code = 'US'
AND patch_name like 'PSDUPGRADE%'
AND status = 'P'
AND description = l_mode;
UPDATE pay_patch_status
SET phase = NULL,
status = 'E'
WHERE legislation_code = 'US'
AND patch_name like 'PSDUPGRADE%'
AND status = 'P'
AND description = l_mode;
SELECT substr(pumg.city_name,1,6),
substr(pumg.city_name,8,6),
substr(pumg.city_name,15,5),
state_code||'-'||county_code||'-'||old_city_code
FROM pay_us_modified_geocodes pumg
WHERE pumg.process_type = 'PA'
AND pay_us_psd_upgrade_pkg.upgrade_needed(pumg.state_code,
pumg.county_code,
pumg.old_city_code,
pumg.city_name)='Y'
ORDER BY state_code,county_code,old_city_code;
CURSOR get_emp_update_details(p_id NUMBER,p_mode VARCHAR2,p_date DATE) IS
SELECT DISTINCT
substr(ppf.full_name,1,100),
pugu.assignment_id,
pugu.old_juri_code,
pugu.new_juri_code,
pugu.table_name
FROM pay_us_geo_update pugu,
per_all_people_f ppf
WHERE pugu.id = p_id
AND pugu.process_type = 'PA'
AND pugu.process_mode = p_mode
AND pugu.process_date = p_date
AND pugu.person_id = ppf.person_id
AND NVL(pugu.status,'NULL') = decode(p_mode,'GENERATE','NULL','UPGRADE','C')
AND NOT EXISTS (
SELECT NULL
FROM per_all_people_f ppf1
WHERE ppf1.person_id =ppf.person_id
AND ppf1.effective_end_date > ppf.effective_end_date
)
GROUP BY ppf.full_name,pugu.assignment_id,pugu.old_juri_code,
pugu.new_juri_code,pugu.table_name
ORDER BY pugu.assignment_id,pugu.old_juri_code,pugu.new_juri_code;
CURSOR get_gre_update_details(p_id NUMBER,p_mode VARCHAR2,p_date DATE) IS
SELECT DISTINCT
substr(hou.name,1,100),
hou.organization_id,
substr(pugu.old_juri_code,1,6),
substr(pugu.new_juri_code,1,6),
pugu.table_name
FROM pay_us_geo_update pugu,
hr_organization_information hoi,
hr_organization_units hou
WHERE pugu.id = p_id
AND pugu.process_type = 'PA'
AND pugu.process_mode = p_mode
AND pugu.process_date = p_date
AND pugu.table_value_id=hoi.org_information_id
AND hoi.organization_id=hou.organization_id
AND NVL(pugu.status,'NULL') = decode(p_mode,'GENERATE','NULL','UPGRADE','C')
GROUP BY hou.name,hou.organization_id,pugu.old_juri_code,
pugu.new_juri_code,pugu.table_name
ORDER BY 2, 3,4;
SELECT DISTINCT
substr(ppf.full_name,1,100),
pugu.assignment_id
FROM pay_us_geo_update pugu,
per_all_people_f ppf
WHERE pugu.id = p_id
AND pugu.process_type = 'PA'
AND pugu.process_mode = p_mode
AND pugu.process_date = p_date
AND pugu.person_id = ppf.person_id
AND NVL(pugu.status,'X') ='E'
AND NOT EXISTS (
SELECT NULL
FROM per_all_people_f ppf1
WHERE ppf1.person_id =ppf.person_id
AND ppf1.effective_end_date > ppf.effective_end_date
)
GROUP BY ppf.full_name,pugu.assignment_id
ORDER BY pugu.assignment_id;
SELECT DISTINCT
substr(hou.name,1,100),
hou.organization_id
FROM pay_us_geo_update pugu,
hr_organization_information hoi,
hr_organization_units hou
WHERE pugu.id = p_id
AND pugu.process_type = 'PA'
AND pugu.process_mode = p_mode
AND pugu.process_date = p_date
AND pugu.table_value_id=hoi.org_information_id
AND hoi.organization_id=hou.organization_id
AND NVL(pugu.status,'X') ='E'
GROUP BY hou.name,hou.organization_id
ORDER BY hou.organization_id;
SELECT COUNT(0)
FROM (SELECT DISTINCT assignment_id
FROM pay_us_geo_update pugu
WHERE pugu.id = p_id
AND pugu.process_type = 'PA'
AND pugu.process_mode = p_mode
AND pugu.process_date = p_date
AND pugu.assignment_id > 0
AND pugu.table_name not in ('HR_ORGANIZATION_INFORMATION', 'FF_ARCHIVE_ITEMS')
AND NVL(pugu.status,'NULL') = DECODE(p_status,'ALL',NVL(pugu.status,'NULL'),p_status));
SELECT COUNT(0)
FROM (SELECT DISTINCT assignment_id
FROM pay_us_geo_update pugu
WHERE pugu.id = p_id
AND pugu.process_type = 'PA'
AND pugu.process_mode = p_mode
AND pugu.process_date = p_date
AND pugu.assignment_id > 0
AND pugu.table_name ='HR_ORGANIZATION_INFORMATION'
AND NVL(pugu.status,'NULL') = DECODE(p_status,'ALL',NVL(pugu.status,'NULL'),p_status));
SELECT COUNT(0)
FROM (SELECT DISTINCT table_value_id
FROM pay_us_geo_update pugu
WHERE pugu.id = p_id
AND pugu.process_type = 'PA'
AND pugu.process_mode = p_mode
AND pugu.process_date = p_date
AND pugu.assignment_id > 0
AND pugu.table_name ='FF_ARCHIVE_ITEMS'
AND NVL(pugu.status,'NULL') = DECODE(p_status,'ALL',NVL(pugu.status,'NULL'),p_status));
SELECT id,applied_date
INTO l_id,l_date
FROM pay_patch_status
WHERE patch_name LIKE 'PSDUPGRADE%'
AND legislation_code = 'US'
AND status = 'P'
AND description = p_mode;
/* Start of Employee Update related details printing*/
IF p_mode = 'GENERATE' THEN
fnd_file.put_line(fnd_file.output,'List down employees for whom Jurisdiction code and relevant tables are to be updated
');
fnd_file.put_line(fnd_file.output,'List down employees for whom Jurisdiction code and relevant tables were updated
');
fnd_file.put_line(fnd_file.output,'Update Details
');
fnd_file.put_line(fnd_file.output,'Employee Details Jurisdiction Details Tables Updated ');
Line: 2292
OPEN get_emp_update_details(l_id,p_mode,l_date);
Line: 2293
FETCH get_emp_update_details INTO l_full_name,l_emp_assignment_id,l_old_jurisdiction_code,l_new_jurisdiction_code,l_table_name;
Line: 2306
WHILE (get_emp_update_details%FOUND)
LOOP
IF (l_print_full_name = l_full_name) AND (l_print_emp_assignment_id = l_emp_assignment_id)
AND (l_print_old_juri_code = l_old_jurisdiction_code) AND (l_print_new_juri_code = l_new_jurisdiction_code) THEN
/*Same employee record for another table. Update the corresponing table flag.*/
IF l_table_name = 'PAY_US_ASG_REPORTING' THEN
l_pay_us_asg_reporting := 'Yes';
Line: 2390
FETCH get_emp_update_details INTO l_full_name,l_emp_assignment_id,l_old_jurisdiction_code,l_new_jurisdiction_code,l_table_name;
Line: 2392
IF (get_emp_update_details%NOTFOUND) THEN
/*No more records exist. Print the last employee details*/
fnd_file.put_line(fnd_file.output,''||l_print_full_name||' '||l_print_emp_assignment_id||' '||l_print_old_juri_code||' '||l_print_new_juri_code||' ');
Line: 2403
CLOSE get_emp_update_details;
Line: 2407
/* End of Employee Update related details printing*/
-- Added for bug 14213838.
/* Start of GRE Update related details printing*/
IF p_mode = 'GENERATE' THEN
fnd_file.put_line(fnd_file.output,'List down GREs for whom PSD Code and relevant tables are to be updated
');
Line: 2415
fnd_file.put_line(fnd_file.output,'List down GREs for whom PSD Code and relevant tables were updated
');
Line: 2424
fnd_file.put_line(fnd_file.output,'Update Details
');
Line: 2426
fnd_file.put_line(fnd_file.output,'Governement Reporting Entity Details PSD Code Details Tables Updated ');
Line: 2433
OPEN get_gre_update_details(l_id,p_mode,l_date);
Line: 2434
FETCH get_gre_update_details INTO l_gre_name, l_gre_id, l_old_jurisdiction_code,l_new_jurisdiction_code,l_table_name;
Line: 2442
WHILE (get_gre_update_details%FOUND)
LOOP
IF (l_print_gre_name = l_gre_name) AND (l_print_gre_id = l_gre_id)
AND (l_print_old_juri_code = l_old_jurisdiction_code) AND (l_print_new_juri_code = l_new_jurisdiction_code) THEN
/*Same GRE record for another table. Update the corresponing table flag.*/
IF l_table_name = 'HR_ORGANIZATION_INFORMATION' THEN
l_hr_organization_information := 'Yes';
Line: 2477
FETCH get_gre_update_details INTO l_gre_name,l_gre_id,l_old_jurisdiction_code,l_new_jurisdiction_code,l_table_name;
Line: 2479
IF (get_gre_update_details%NOTFOUND) THEN
/*No more records exist. Print the last GRE details*/
fnd_file.put_line(fnd_file.output,''||l_print_gre_name||' '||l_print_gre_id||' '||l_print_old_juri_code||' '||l_print_new_juri_code||' ');
Line: 2489
CLOSE get_gre_update_details;
Line: 2493
/* End of GRE Information Update related details printing*/
IF p_mode = 'UPGRADE' AND l_upgrade_needed = 'Y' THEN
/* Start of Errored Employees printing section */
OPEN get_emp_in_error(l_id,p_mode,l_date);
Line: 2648
fnd_file.put_line(fnd_file.output,'Employer PSD Codes related data in FF_ARCHIVE_ITEMS table has been successfully updated
');
Line: 2655
fnd_file.put_line(fnd_file.output,'Employer PSD Codes related data in FF_ARCHIVE_ITEMS table not updated successfully
');