[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Removed a)'order by' and 'for update'
clauses, b) calls to hr_us_w2_rep
functions (added queries to retrieve
the values).
Modified RANGE_CURSOR and removed
calls to get_parameter function.
115.6 10-AUG-01 irgonzal Modified action_creation cursor and
removed reference to
hr_us_w2_rep.get_w2_arch_bal function.
115.7 30-AUG-01 irgonzal Modified range_cursor and added
condition that includes :payroll_action_id
parameter.
Remove identation in SORT cursor.
Replaced 'YEAR' by 'Year'.
115.9 31-AUG-01 ssarma added to_char to tax_unit_id join to
fic1.context
115.10 09-SEP-01 kthirmiy added ppa.payroll_action_id in the action_creation
procedure while selecting the l_eoy_payroll_action_id
Also changed to ppa.effective_date=ppa1.effective_date
instead of
ppa.effective_date = to_date('31-DEC-'||
hr_us_w2_mt.get_parameter
('Year',ppa1.legislative_parameters), 'DD-MON-YYYY')
115.11 11-DEC-01 meshah changed the assignment_action cursor for
performance reason. There was a dramatic performance
gain at inhouse. No each selection criteria are
a seperate cursor.
115.14 12-DEC-01 rsirigir GSCC COMPLIANCE CHECK, added
REM checkfile:~PROD:~PATH:~FILE
changed date format from
select to_date('31-DEC-'||to_char(l_year),
'DD-MON-YYYY') to
select to_date('31-DEC-'||to_char(l_year),
'DD/MM/YYYY')
changed date format from
where to_date('31-DEC-'||to_char(l_year),'DD-MON-YYYY')
> l_dt to
where to_date('31-DEC-'||to_char(l_year),'DD/MM/YYYY')
> l_dt
115.15 10-Jan-02 kthirmiy For TAR 1874418.995 to improve performance changed in
sort_action function . Removed the tables
pay_payroll_actions ppa_arch and
pay_assignment_actions and to go directly to
pay_assignment_actions mt table.
115.16 18-JAN-02 meshah changed the sort cursor again. Need to fetch
zip code for the live address.
115.20 12-FEB-02 meshah changed the action_creation cursor. Now seperate
procedures are called for Employee and Employer
W2. This is because state paramter is required
for Employer W2 and optional for Employee W2.
115.21 19-Aug-02 fusman Added Puerto Rico W-2 report type.
115.22 10-SEP-02 kthirmiy Added hr_us_w2_rep.get_agent_tax_unit_id
for Agent GRE setup validation check
in the range_cursor
115.23 11-SEP-02 kthirmiy changed ppa1.report_type instead of ppa.report_type
changed update of mt.assignment_action_id instead of
paf.assignment_id in sort_action
115.24 12-Sep-02 fusman Bug:2565342
Changed the ssn datatype from number to varchar2.
115.25 17-SEP-02 kthirmiy Removed Pre-Process Check - Agent GRE setup
for Bug 2573499
115.26 31-JUL-03 meshah 2576942 modified cursors c_actions_with_location,
c_actions_with_org and c_actions_with_state.
A new cursor c_state_ueid has been created to
fetch the user_entity_id only once.
Same cursors have been modified for ee and er.
115.26 08-AUG-03 meshah 3052020 passing report_type as a parameter to
action_creation_for_ee. We do not print paper
W2 for employee who have opted not to receive a
paper W2.
115.28 29-SEP-03 meshah backed out the call to
pay_us_employee_payslip_web.
115.29 03-OCT-03 meshah changed the c_actions_no_selection cursor for
ee and er for performance reason.
115.30 20-JUL-2004 asasthan NO CODE CHANGES Only comments have been added
BUG: 3343607, 3624090
Changes for action_creation
with state and org was done
by meshah earlier.
Action Creation with SSN
seems to be taking optimal
path.
Sort Action: put on hold
after discussing with meshah.
115.31 30-JUL-2004 asasthan 3343607 cursor c_actions_with_ssn is not
used at all. Removing the cursor
from the code for EE W2 Report.
115.32 03-RAUG2004 asasthan 3343607 cursor c_actions_with_ssn is not
used for ER W2 Report. Removing
cursor and commented out code.
115.34 06-AUG-2004 rsethupa 3052020 Changes for optionally printing W2
115.35 19-AUG-2004 meshah there was a to_char on serial_number
when comparing with person_id.
this will cause the package to be
invalid on 8.1.7.4x DB. Changed to
to_number.
115.36 01-SEP-2004 asasthan 3052020 Employer W2 should print
irrespective of Self-Service
Preferences set for W2.
115.37 14-MAR-2005 sackumar 4222032 Change in the Range Cursor removing redundant
use of bind Variable (:payroll_action_id)
115.40 24-AUG-2005 pragupta 4152323 Range Person ID functionality enhancement:
The cursors for action_creation_for_ee and
action_creation_for_er have been replaced by
ref cursors. The aim is to improve the
performance of the cursor queries.
115.41 07-SEP-2005 ynegoro 2538173 Support new parameter, locality
115.42 12-SEP-2005 sodhingr 3688789 Added W2_XML report format for action
creation
115.43 21-SEP-2005 ahanda Changed action creation to support
locality
115.44 22-SEP-2005 ahanda Changed select stmt for locality param.
115.55 26-OCT-2005 kvsankar 4645408 Added the check for the User Entity
'A_CITY_WK_WITHHELD_PER_JD_GRE_YTD'
as employees who have both Wages and Taxes
withheld should only be reported for the
specified locality
115.46 04-JAN-2006 pragupta 4886044 Added the check for the User Entity
'A_COUNTY_WITHHELD_PER_JD_GRE_YTD'
and 'A_SCHOOL_WITHHELD_PER_JD_GRE_YTD'
as employees who have both Wages and Taxes
withheld should only be reported for the
specified locality
115.47 24-JAN-2006 asasthan 4951715 Removed suppression of index
on per_assignments_f in sort cursor
115.48 10-AUG-2006 sodhingr 5169849 Changed action_creation for EE report to
print the W-2 for terminated EE only
115.49 29-AUG-2006 saurgupt 5169849 Changed the function action_creation_term_ee. Removed
condition which checks that the actual_termination_date should
be between eoy_start_date and session_date.
115.50 07-SEP-2006 jdevasah 5513289 Commented the cursor c_actions_with_person of
action_creation_for_ee procedure. This cursor is
no longer required since this is replaced by a dymanic
cursor.
115.51 20-02-2008 svannian 6809739 action creation cursor of ER will pick up employees
when either sit wages or sit tax is greater than zero
********************************************************************************/
----------------------------------- range_cursor -------------------------------
procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
l_eoy_payroll_action_id pay_payroll_actions.payroll_action_id%type;
select ppa.payroll_action_id
, ppa.business_group_id
, to_number(hr_us_w2_mt.get_parameter('Year',ppa1.legislative_parameters))
, ppa1.report_type
into l_eoy_payroll_action_id
,l_business_group_id
,l_year
,l_report_type
from pay_payroll_actions ppa, /* EOY payroll action id */
pay_payroll_actions ppa1 /* PYUGEN payroll action id */
where ppa1.payroll_action_id = pactid
and ppa.effective_date = ppa1.effective_date
and ppa.report_type = 'YREND'
and hr_us_w2_mt.get_parameter
('GRE_ID',ppa1.legislative_parameters) =
hr_us_w2_mt.get_parameter
('TRANSFER_GRE',ppa.legislative_parameters);
'SELECT
to_number(paa_arch.serial_number)
FROM
PAY_ASSIGNMENT_ACTIONS paa_arch
WHERE paa_arch.payroll_action_id = ' || l_eoy_payroll_action_id ||
' AND :payroll_action_id is not null
AND paa_arch.action_status = ''C''
order by to_number(paa_arch.serial_number) ';
FUNCTION action_creation_term_ee (p_select IN varchar2,
p_where IN varchar2,
p_eoy_start_date IN date,
p_session_date IN date)
RETURN VARCHAR2 IS
c_select varchar2(32767);
c_select := p_select || ',per_periods_of_service PDS ';
c_complete_sql := c_select|| c_where;
/* when person is selected */
-- Bug# 5513289 : This cursor is not needed. A dynamic cursor created to replace
-- this to fix this bug.
/* CURSOR c_actions_with_person is
SELECT paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date
FROM per_assignments_f paf,
pay_assignment_actions paa_arch
WHERE paa_arch.payroll_action_id = p_eoy_payroll_action_id
AND paa_arch.action_status = 'C'
AND paf.PERSON_ID = p_per_id
AND paa_arch.assignment_id = paf.assignment_id
AND paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= p_session_date)
AND paf.effective_end_date >= p_eoy_start_date
AND paf.assignment_type = 'E'
AND paf.person_id between stperson and endperson;
select context_id from ff_contexts
where context_name = p_context_name;
select user_entity_id
from ff_user_entities
where user_entity_name = p_user_entity_name
and legislation_code = 'US';
c_actions_no_selection RefCurType;
c_actions_no_selection_sql varchar2(10000);
select effective_date,
report_type,
report_qualifier,
report_category
into l_effective_date,
l_report_type,
l_report_qualifier,
l_report_category
from pay_payroll_actions
where payroll_action_id = pactid;
select report_format
into l_report_format
from pay_report_format_mappings_f
where report_type = l_report_type
and report_qualifier = l_report_qualifier
and report_category = l_report_category
and l_effective_date between
effective_start_date and effective_end_date;
/* when no selection is entered */
if((p_loc_id is null ) and
(p_org_id is null ) and
(p_per_id is null ) and
(p_ssn is null ) and
(p_state_code is null ) and
(p_asg_set_id is null )) then
hr_utility.set_location(l_procedure_name, 5);
c_actions_no_selection_sql :=
'SELECT paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date
FROM per_assignments_f paf,
pay_assignment_actions paa_arch,
pay_population_ranges ppr ';
(select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_session_date || ''')
AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
AND paf.assignment_type = ''E''
and paf.primary_flag = ''Y''
AND ppr.payroll_action_id = ' || pactid || '
AND ppr.chunk_number = ' || chunk || '
AND paf.person_id = ppr.person_id
and paf.person_id = to_number(paa_arch.serial_number)';
/* c_actions_no_selection_sql := c_actions_no_selection_sql ||
',per_periods_of_service PDS ';
c_actions_no_selection_sql := action_creation_term_ee (c_actions_no_selection_sql,
c_actions_where,
p_eoy_start_date,
p_session_date);
c_actions_no_selection_sql := c_actions_no_selection_sql || c_actions_where;
hr_utility.trace(' c_actions_no_selection_sql' ||c_actions_no_selection_sql);
c_actions_no_selection_sql :=
'SELECT paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date
FROM per_assignments_f paf,
pay_assignment_actions paa_arch ';
(select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_session_date || ''')
AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
AND paf.assignment_type = ''E''
and paf.primary_flag = ''Y''
AND paf.person_id between ' || stperson || ' and ' || endperson || '
and paf.person_id = to_number(paa_arch.serial_number) ';
c_actions_no_selection_sql := action_creation_term_ee (c_actions_no_selection_sql,
c_actions_where,
p_eoy_start_date,
p_session_date);
c_actions_no_selection_sql := c_actions_no_selection_sql || c_actions_where;
hr_utility.trace(' c_actions_no_selection_sql' ||c_actions_no_selection_sql);
OPEN c_actions_no_selection FOR c_actions_no_selection_sql;
fetch c_actions_no_selection into lockedactid,assignid,greid,l_effective_end_date;
if c_actions_no_selection%found then
num := num + 1;
hr_utility.trace('In the c_actions_no_selection%found in action cursor');
hr_utility.trace('In the c_actions_no_selection%notfound in action cursor');
hr_utility.trace('Before inserting the action record');
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions
set serial_number = lockedactid
where assignment_action_id = lockingactid;
close c_actions_no_selection;
'SELECT paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date
FROM per_periods_of_service pps,
per_assignments_f paf,
pay_assignment_actions paa_arch,
pay_population_ranges ppr ';
(select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_session_date || ''')
AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
AND paf.assignment_type = ''E''
AND pps.period_of_service_id = paf.period_of_service_id
AND ppr.payroll_action_id = ' || pactid || '
AND ppr.chunk_number = ' || chunk || '
AND paf.person_id = ppr.person_id ' ;
'SELECT paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date
FROM per_periods_of_service pps,
per_assignments_f paf,
pay_assignment_actions paa_arch ';
(select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_session_date || ''' )
AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
AND paf.assignment_type = ''E''
AND pps.period_of_service_id = paf.period_of_service_id
AND paf.person_id between ' || stperson || ' and ' || endperson || '';
hr_utility.trace('Before inserting the action record');
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions
set serial_number = lockedactid
where assignment_action_id = lockingactid;
'SELECT paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date
FROM per_periods_of_service pps,
per_assignments_f paf,
pay_assignment_actions paa_arch,
pay_population_ranges ppr ';
(select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_session_date || ''')
AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
AND paf.assignment_type = ''E''
AND pps.period_of_service_id = paf.period_of_service_id
AND ppr.payroll_action_id = ' || pactid || '
AND ppr.chunk_number = ' || chunk || '
AND paf.person_id = ppr.person_id ';
'SELECT paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date
FROM per_periods_of_service pps,
per_assignments_f paf,
pay_assignment_actions paa_arch ';
(select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_session_date || ''')
AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
AND paf.assignment_type = ''E''
AND pps.period_of_service_id = paf.period_of_service_id
AND paf.person_id between ' || stperson || ' and ' || endperson ||'';
hr_utility.trace('Before inserting the action record');
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions
set serial_number = lockedactid
where assignment_action_id = lockingactid;
of terminated employees alone are selected */
c_actions_with_person_sql := 'SELECT paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date
FROM per_assignments_f paf,
pay_assignment_actions paa_arch';
AND paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' ||p_session_date ||''')
AND paf.effective_end_date >='''|| p_eoy_start_date || '''
AND paf.assignment_type = ''E''
AND paf.person_id between ' || stperson || ' and ' || endperson ||' ';
hr_utility.trace('Before inserting the action record');
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions
set serial_number = lockedactid
where assignment_action_id = lockingactid;
'SELECT paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date
FROM per_assignments_f paf,
pay_assignment_actions paa_arch,
pay_population_ranges ppr ';
(select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_session_date || ''')
AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
AND paf.assignment_type = ''E''
AND ppr.payroll_action_id = ' || pactid || '
AND ppr.chunk_number = ' || chunk || '
AND paf.person_id = ppr.person_id ';
'SELECT paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date
FROM per_assignments_f paf,
pay_assignment_actions paa_arch ';
(select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_session_date || ''')
AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
AND paf.assignment_type = ''E''
AND paf.person_id between ' || stperson || ' and ' || endperson;
' AND exists ( select 1 from dual
where 1 =
(select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
from ff_archive_items fai,
ff_archive_item_contexts fic1,
ff_archive_item_contexts fic2
where fai.context1 = paa_arch.assignment_action_id
and fai.user_entity_id in (' || l_subj_whable || ',
' || l_subj_nwhable || ')
and fai.archive_item_id = fic1.archive_item_id
and fic1.context_id = ' || l_tuid_context || '
and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
and fai.archive_item_id = fic2.archive_item_id
and fic2.context_id = ' || l_juri_context || '
and substr(ltrim(rtrim(fic2.context)),1,2) = ' || p_state_code || ' ))';
' AND exists ( select 1 from dual
where 1 =
(select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
from ff_archive_items fai,
ff_archive_item_contexts fic1,
ff_archive_item_contexts fic2
where fai.context1 = paa_arch.assignment_action_id
and fai.user_entity_id in (' || l_county_wheld || ')
and fai.archive_item_id = fic1.archive_item_id
and fic1.context_id = ' || l_tuid_context || '
and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
and fai.archive_item_id = fic2.archive_item_id
and fic2.context_id = ' || l_juri_context || '
and substr(ltrim(rtrim(fic2.context)),1,6) = substr(''' || p_locality_code || ''',1,6) ))';
' AND exists ( select 1 from dual
where 1 =
(select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
from ff_archive_items fai,
ff_archive_item_contexts fic1,
ff_archive_item_contexts fic2
where fai.context1 = paa_arch.assignment_action_id
and fai.user_entity_id in (' || l_subj_whable || ',
' || l_subj_nwhable || ')
and fai.archive_item_id = fic1.archive_item_id
and fic1.context_id = ' || l_tuid_context || '
and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
and fai.archive_item_id = fic2.archive_item_id
and fic2.context_id = ' || l_juri_context || '
and substr(ltrim(rtrim(fic2.context)),1,6) = substr(''' || p_locality_code || ''',1,6) ))';
' AND exists ( select 1 from dual
where 1 =
(select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
from ff_archive_items fai,
ff_archive_item_contexts fic1,
ff_archive_item_contexts fic2
where fai.context1 = paa_arch.assignment_action_id
and fai.user_entity_id in (' || l_city_wk_whld || ')
and fai.archive_item_id = fic1.archive_item_id
and fic1.context_id = ' || l_tuid_context || '
and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
and fai.archive_item_id = fic2.archive_item_id
and fic2.context_id = ' || l_juri_context || '
and substr(ltrim(rtrim(fic2.context)),1,11) = ''' || p_locality_code || ''' ))';
' AND exists ( select 1 from dual
where 1 =
(select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
from ff_archive_items fai,
ff_archive_item_contexts fic1,
ff_archive_item_contexts fic2
where fai.context1 = paa_arch.assignment_action_id
and fai.user_entity_id in (' || l_subj_whable || ',
' || l_subj_nwhable || ')
and fai.archive_item_id = fic1.archive_item_id
and fic1.context_id = ' || l_tuid_context || '
and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
and fai.archive_item_id = fic2.archive_item_id
and fic2.context_id = ' || l_juri_context || '
and substr(ltrim(rtrim(fic2.context)),1,11) = ''' || p_locality_code || ''' ))';
' AND exists ( select 1 from dual
where 1 =
(select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
from ff_archive_items fai,
ff_archive_item_contexts fic1,
ff_archive_item_contexts fic2
where fai.context1 = paa_arch.assignment_action_id
and fai.user_entity_id in (' || l_school_wheld || ')
and fai.archive_item_id = fic1.archive_item_id
and fic1.context_id = ' || l_tuid_context || '
and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
and fai.archive_item_id = fic2.archive_item_id
and fic2.context_id = ' || l_juri_context || '
and substr(ltrim(rtrim(fic2.context)),1,8) = ''' || p_locality_code || '''))';
' AND exists ( select 1 from dual
where 1 =
(select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
from ff_archive_items fai,
ff_archive_item_contexts fic1,
ff_archive_item_contexts fic2
where fai.context1 = paa_arch.assignment_action_id
and fai.user_entity_id in (' || l_subj_whable || ',
' || l_subj_nwhable || ')
and fai.archive_item_id = fic1.archive_item_id
and fic1.context_id = ' || l_tuid_context || '
and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
and fai.archive_item_id = fic2.archive_item_id
and fic2.context_id = ' || l_juri_context || '
and substr(ltrim(rtrim(fic2.context)),1,8) = ''' || p_locality_code || '''))';
hr_utility.trace('Before inserting the action record');
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions
set serial_number = lockedactid
where assignment_action_id = lockingactid;
'SELECT paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date
FROM per_assignments_f paf,
pay_assignment_actions paa_arch,
pay_population_ranges ppr ';
AND paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_session_date || ''')
AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
AND paf.assignment_type = ''E''
AND ppr.payroll_action_id = ' || pactid || '
AND ppr.chunk_number = ' || chunk || '
AND paf.person_id = ppr.person_id
AND exists ( select 1 /* Selected Assignment Set */
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = ' || p_asg_set_id || '
and hasa.assignment_id = paa_arch.assignment_id
and upper(hasa.include_or_exclude) = ''I'') ';
'SELECT paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date
FROM per_assignments_f paf,
pay_assignment_actions paa_arch ';
AND paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_session_date || ''')
AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
AND paf.assignment_type = ''E''
AND paf.person_id between ' || stperson || ' and ' || endperson || '
AND exists ( select 1 /* Selected Assignment Set */
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = ' || p_asg_set_id || '
and hasa.assignment_id = paa_arch.assignment_id
and upper(hasa.include_or_exclude) = ''I'') ';
hr_utility.trace('Before inserting the action record');
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions
set serial_number = lockedactid
where assignment_action_id = lockingactid;
select context_id from ff_contexts
where context_name = p_context_name;
select user_entity_id
from ff_user_entities
where user_entity_name = p_user_entity_name
and legislation_code = 'US';
/* when person is selected */
CURSOR c_actions_with_person is
SELECT paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date
FROM per_assignments_f paf,
pay_assignment_actions paa_arch
WHERE paa_arch.payroll_action_id = p_eoy_payroll_action_id
AND paa_arch.action_status = 'C'
AND paf.PERSON_ID = p_per_id
AND paa_arch.assignment_id = paf.assignment_id
AND paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= p_session_date)
AND paf.effective_end_date >= p_eoy_start_date
AND paf.assignment_type = 'E'
AND paf.person_id between stperson and endperson
AND exists ( select 1 from dual
where 1 =
(select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
from ff_archive_items fai,
ff_archive_item_contexts fic1,
ff_archive_item_contexts fic2
where fai.context1 = paa_arch.assignment_action_id
and fai.user_entity_id in
( l_sit_subj_whable,
l_sit_subj_nwhable,
l_sit_withheld) /* 6809739 */
and fai.archive_item_id = fic1.archive_item_id
and fic1.context_id = l_tuid_context
and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
and fai.archive_item_id = fic2.archive_item_id
and fic2.context_id = l_juri_context
and substr(ltrim(rtrim(fic2.context)),1,2) = p_state_code )) ;
c_actions_no_selection RefCurType;
c_actions_no_selection_sql varchar2(10000);
select effective_date,
report_type,
report_qualifier,
report_category
into l_effective_date,
l_report_type,
l_report_qualifier,
l_report_category
from pay_payroll_actions
where payroll_action_id = pactid;
select report_format
into l_report_format
from pay_report_format_mappings_f
where report_type = l_report_type
and report_qualifier = l_report_qualifier
and report_category = l_report_category
and l_effective_date between
effective_start_date and effective_end_date;
/* when no selection is entered */
if((p_loc_id is null ) and
(p_org_id is null ) and
(p_per_id is null ) and
(p_ssn is null ) and
(p_asg_set_id is null )) then
if l_range_person_on = TRUE Then
hr_utility.trace('Range Person ID Functionality is enabled') ;
c_actions_no_selection_sql :=
'SELECT paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date
FROM per_assignments_f paf,
pay_assignment_actions paa_arch,
pay_population_ranges ppr
WHERE paa_arch.action_status = ''C''
AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
AND paa_arch.assignment_id = paf.assignment_id
AND paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_session_date || ''')
AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
AND paf.assignment_type = ''E''
and paf.primary_flag = ''Y''
--AND paf.person_id between stperson and endperson
and paf.person_id = to_number(paa_arch.serial_number)
AND ppr.payroll_action_id = ' || pactid || '
AND ppr.chunk_number = ' || chunk || '
AND paf.person_id = ppr.person_id
AND exists ( select 1 from dual
where 1 =
(select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
from ff_archive_items fai,
ff_archive_item_contexts fic1,
ff_archive_item_contexts fic2
where fai.context1 = paa_arch.assignment_action_id
and fai.user_entity_id in
( ' || l_sit_subj_whable || ',
' || l_sit_subj_nwhable || ',
' || l_sit_withheld || ') /* 6809739 */
and fai.archive_item_id = fic1.archive_item_id
and fic1.context_id = ' || l_tuid_context || '
and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
and fai.archive_item_id = fic2.archive_item_id
and fic2.context_id = ' || l_juri_context || '
and substr(ltrim(rtrim(fic2.context)),1,2) = ' || p_state_code || ' )) ';
c_actions_no_selection_sql :=
'SELECT paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date
FROM per_assignments_f paf,
pay_assignment_actions paa_arch
WHERE paa_arch.action_status = ''C''
AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
AND paa_arch.assignment_id = paf.assignment_id
AND paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_session_date || ''')
AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
AND paf.assignment_type = ''E''
and paf.primary_flag = ''Y''
AND paf.person_id between ' || stperson || ' and ' || endperson || '
and paf.person_id = to_number(paa_arch.serial_number)
AND exists ( select 1 from dual
where 1 =
(select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
from ff_archive_items fai,
ff_archive_item_contexts fic1,
ff_archive_item_contexts fic2
where fai.context1 = paa_arch.assignment_action_id
and fai.user_entity_id in
( ' || l_sit_subj_whable || ',
' || l_sit_subj_nwhable || ',
' || l_sit_withheld || ') /* 6809739 */
and fai.archive_item_id = fic1.archive_item_id
and fic1.context_id = ' || l_tuid_context || '
and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
and fai.archive_item_id = fic2.archive_item_id
and fic2.context_id = ' || l_juri_context || '
and substr(ltrim(rtrim(fic2.context)),1,2) = ' || p_state_code || ' )) ';
OPEN c_actions_no_selection for c_actions_no_selection_sql;
fetch c_actions_no_selection into lockedactid,assignid,greid,l_effective_end_date;
if c_actions_no_selection%found then
num := num + 1;
hr_utility.trace('In the c_actions_no_selection%found in action cursor');
hr_utility.trace('In the c_actions_no_selection%notfound in action cursor');
hr_utility.trace('Before inserting the action record');
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions
set serial_number = lockedactid
where assignment_action_id = lockingactid;
close c_actions_no_selection;
'SELECT paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date
FROM per_periods_of_service pps,
per_assignments_f paf,
pay_assignment_actions paa_arch,
pay_population_ranges ppr
/* disabling the index for performance reason */
WHERE paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
AND paa_arch.action_status = ''C''
AND nvl(final_process_date,''' || p_session_date || ''')
between paf.effective_start_date
and paf.effective_end_date
AND paf.location_id = ' || p_loc_id || '
AND paa_arch.assignment_id = paf.assignment_id
AND paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_session_date || ''')
AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
AND paf.assignment_type = ''E''
AND pps.period_of_service_id = paf.period_of_service_id
--AND paf.person_id between stperson and endperson
AND ppr.payroll_action_id = ' || pactid || '
AND ppr.chunk_number = ' || chunk || '
AND paf.person_id = ppr.person_id
AND exists ( select 1 from dual
where 1 =
(select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
from ff_archive_items fai,
ff_archive_item_contexts fic1,
ff_archive_item_contexts fic2
where fai.context1 = paa_arch.assignment_action_id
and fai.user_entity_id in
( ' || l_sit_subj_whable || ',
' || l_sit_subj_nwhable || ',
' || l_sit_withheld || ') /* 6809739 */
and fai.archive_item_id = fic1.archive_item_id
and fic1.context_id = ' || l_tuid_context || '
and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
and fai.archive_item_id = fic2.archive_item_id
and fic2.context_id = ' || l_juri_context || '
and substr(ltrim(rtrim(fic2.context)),1,2) = ' || p_state_code || ' ))';
'SELECT paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date
FROM per_periods_of_service pps,
per_assignments_f paf,
pay_assignment_actions paa_arch
/* disabling the index for performance reason */
WHERE paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
AND paa_arch.action_status = ''C''
AND nvl(final_process_date,''' || p_session_date || ''')
between paf.effective_start_date
and paf.effective_end_date
AND paf.location_id = ' || p_loc_id || '
AND paa_arch.assignment_id = paf.assignment_id
AND paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_session_date || ''')
AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
AND paf.assignment_type = ''E''
AND pps.period_of_service_id = paf.period_of_service_id
AND paf.person_id between ' || stperson || ' and ' || endperson || '
AND exists ( select 1 from dual
where 1 =
(select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
from ff_archive_items fai,
ff_archive_item_contexts fic1,
ff_archive_item_contexts fic2
where fai.context1 = paa_arch.assignment_action_id
and fai.user_entity_id in
( ' || l_sit_subj_whable || ',
' || l_sit_subj_nwhable || ',
' || l_sit_withheld || ') /* 6809739 */
and fai.archive_item_id = fic1.archive_item_id
and fic1.context_id = ' || l_tuid_context || '
and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
and fai.archive_item_id = fic2.archive_item_id
and fic2.context_id = ' || l_juri_context || '
and substr(ltrim(rtrim(fic2.context)),1,2) = ' || p_state_code || ' ))';
hr_utility.trace('Before inserting the action record');
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions
set serial_number = lockedactid
where assignment_action_id = lockingactid;
'SELECT paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date
FROM per_periods_of_service pps,
per_assignments_f paf,
pay_assignment_actions paa_arch,
pay_population_ranges ppr
/* disabling the index for performance reason */
WHERE paa_arch.payroll_action_id +0 = ' || p_eoy_payroll_action_id || '
AND paa_arch.action_status = ''C''
AND nvl(final_process_date,''' || p_session_date || ''')
between paf.effective_start_date
and paf.effective_end_date
AND paf.organization_id = ' || p_org_id || '
AND paa_arch.assignment_id = paf.assignment_id
AND paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_session_date || ''')
AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
AND paf.assignment_type = ''E''
AND pps.period_of_service_id = paf.period_of_service_id
--AND paf.person_id between stperson and endperson
AND ppr.payroll_action_id = ' || pactid || '
AND ppr.chunk_number = ' || chunk || '
AND paf.person_id = ppr.person_id
AND exists ( select 1 from dual
where 1 =
(select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
from ff_archive_items fai,
ff_archive_item_contexts fic1,
ff_archive_item_contexts fic2
where fai.context1 = paa_arch.assignment_action_id
and fai.user_entity_id in
( ' || l_sit_subj_whable || ',
' || l_sit_subj_nwhable || ',
' || l_sit_withheld || ') /* 6809739 */
and fai.archive_item_id = fic1.archive_item_id
and fic1.context_id = ' || l_tuid_context || '
and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
and fai.archive_item_id = fic2.archive_item_id
and fic2.context_id = ' || l_juri_context || '
and substr(ltrim(rtrim(fic2.context)),1,2) = ' || p_state_code || '))';
'SELECT paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date
FROM per_periods_of_service pps,
per_assignments_f paf,
pay_assignment_actions paa_arch
/* disabling the index for performance reason */
WHERE paa_arch.payroll_action_id +0 = ' || p_eoy_payroll_action_id || '
AND paa_arch.action_status = ''C''
AND nvl(final_process_date,''' || p_session_date || ''')
between paf.effective_start_date
and paf.effective_end_date
AND paf.organization_id = ' || p_org_id || '
AND paa_arch.assignment_id = paf.assignment_id
AND paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_session_date || ''')
AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
AND paf.assignment_type = ''E''
AND pps.period_of_service_id = paf.period_of_service_id
AND paf.person_id between ' || stperson || ' and ' || endperson || '
AND exists ( select 1 from dual
where 1 =
(select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
from ff_archive_items fai,
ff_archive_item_contexts fic1,
ff_archive_item_contexts fic2
where fai.context1 = paa_arch.assignment_action_id
and fai.user_entity_id in
( ' || l_sit_subj_whable || ',
' || l_sit_subj_nwhable || ',
' || l_sit_withheld || ') /* 6809739 */
and fai.archive_item_id = fic1.archive_item_id
and fic1.context_id = ' || l_tuid_context || '
and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
and fai.archive_item_id = fic2.archive_item_id
and fic2.context_id = ' || l_juri_context || '
and substr(ltrim(rtrim(fic2.context)),1,2) = ' || p_state_code || '))';
hr_utility.trace('Before inserting the action record');
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions
set serial_number = lockedactid
where assignment_action_id = lockingactid;
hr_utility.trace('Before inserting the action record');
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions
set serial_number = lockedactid
where assignment_action_id = lockingactid;
'SELECT paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date
FROM per_assignments_f paf,
pay_assignment_actions paa_arch,
pay_population_ranges ppr
WHERE paa_arch.action_status = ''C''
AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
AND paa_arch.assignment_id = paf.assignment_id
AND paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_session_date || ''')
AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
AND paf.assignment_type = ''E''
--AND paf.person_id between stperson and endperson
AND ppr.payroll_action_id = ' || pactid || '
AND ppr.chunk_number = ' || chunk || '
AND paf.person_id = ppr.person_id
AND exists ( select 1 /* Selected Assignment Set */
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = ' || p_asg_set_id || '
and hasa.assignment_id = paa_arch.assignment_id
and upper(hasa.include_or_exclude) = ''I'')
AND exists ( select 1 from dual
where 1 =
(select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
from ff_archive_items fai,
ff_archive_item_contexts fic1,
ff_archive_item_contexts fic2
where fai.context1 = paa_arch.assignment_action_id
and fai.user_entity_id in
( ' || l_sit_subj_whable || ',
' || l_sit_subj_nwhable || ',
' || l_sit_withheld || ') /* 6809739 */
and fai.archive_item_id = fic1.archive_item_id
and fic1.context_id = ' || l_tuid_context || '
and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
and fai.archive_item_id = fic2.archive_item_id
and fic2.context_id = ' || l_juri_context || '
and substr(ltrim(rtrim(fic2.context)),1,2) = ' || p_state_code || ' ))';
'SELECT paa_arch.assignment_action_id,
paa_arch.assignment_id,
paa_arch.tax_unit_id,
paf.effective_end_date
FROM per_assignments_f paf,
pay_assignment_actions paa_arch
WHERE paa_arch.action_status = ''C''
AND paa_arch.payroll_action_id + 0 = ' || p_eoy_payroll_action_id || '
AND paa_arch.assignment_id = paf.assignment_id
AND paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2
where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= ''' || p_session_date || ''')
AND paf.effective_end_date >= ''' || p_eoy_start_date || '''
AND paf.assignment_type = ''E''
AND paf.person_id between ' || stperson || ' and ' || endperson || '
AND exists ( select 1 /* Selected Assignment Set */
from hr_assignment_set_amendments hasa
where hasa.assignment_set_id = ' || p_asg_set_id || '
and hasa.assignment_id = paa_arch.assignment_id
and upper(hasa.include_or_exclude) = ''I'')
AND exists ( select 1 from dual
where 1 =
(select decode(sign(nvl(sum(to_number(fai.value)),0)),-1,1,0,0,1)
from ff_archive_items fai,
ff_archive_item_contexts fic1,
ff_archive_item_contexts fic2
where fai.context1 = paa_arch.assignment_action_id
and fai.user_entity_id in
( ' || l_sit_subj_whable || ',
' || l_sit_subj_nwhable || ',
' || l_sit_withheld || ') /* 6809739 */
and fai.archive_item_id = fic1.archive_item_id
and fic1.context_id = ' || l_tuid_context || '
and ltrim(rtrim(fic1.context)) = to_char(paa_arch.tax_unit_id)
and fai.archive_item_id = fic2.archive_item_id
and fic2.context_id = ' || l_juri_context || '
and substr(ltrim(rtrim(fic2.context)),1,2) = ' || p_state_code || ' ))';
hr_utility.trace('Before inserting the action record');
select pay_assignment_actions_s.nextval
into lockingactid
from dual;
update pay_assignment_actions
set serial_number = lockedactid
where assignment_action_id = lockingactid;
select to_number(hr_us_w2_mt.get_parameter('Year',ppa1.legislative_parameters)),
to_number(hr_us_w2_mt.get_parameter('GRE_ID',ppa1.legislative_parameters)),
to_number(hr_us_w2_mt.get_parameter('ORG_ID',ppa1.legislative_parameters)),
to_number(hr_us_w2_mt.get_parameter('LOC_ID',ppa1.legislative_parameters)),
to_number(hr_us_w2_mt.get_parameter('PER_ID',ppa1.legislative_parameters)),
hr_us_w2_mt.get_parameter('SSN',ppa1.legislative_parameters),
hr_us_w2_mt.get_parameter('STATE',ppa1.legislative_parameters),
to_number(hr_us_w2_mt.get_parameter('ASG_SET',ppa1.legislative_parameters)),
ppa.effective_date,
ppa.payroll_action_id,
ppa.start_date,
ppa1.report_type
--,ppa1.legislative_parameters
,hr_us_w2_mt.get_parameter('LOCALITY',ppa1.legislative_parameters)
,hr_us_w2_mt.get_parameter('PRINT_TERM',ppa1.legislative_parameters)
into l_year,
l_gre_id,
l_org_id,
l_loc_id,
l_per_id,
l_ssn,
l_state_code,
l_asg_set_id,
l_session_date,
l_eoy_payroll_action_id,
l_eoy_start_date,
l_report_type
,l_locality_code -- BUG2538173
,l_print_term
from pay_payroll_actions ppa, /* EOY payroll action id */
pay_payroll_actions ppa1 /* PYUGEN payroll action id */
where ppa1.payroll_action_id = pactid
and ppa.effective_date = ppa1.effective_date
and ppa.report_type = 'YREND'
and hr_us_w2_mt.get_parameter
('GRE_ID',ppa1.legislative_parameters) =
hr_us_w2_mt.get_parameter
('TRANSFER_GRE',ppa.legislative_parameters);
selection citeria only only value can be entered so in case l_ssn is not
null then it is safe to assume l_per_id is null */
if l_ssn is not null then
l_per_id := l_ssn;
select hr_us_w2_mt.get_parameter('Year',ppa.legislative_parameters),
hr_us_w2_mt.get_parameter('GRE_ID',ppa.legislative_parameters),
hr_us_w2_mt.get_parameter('ORG_ID',ppa.legislative_parameters),
hr_us_w2_mt.get_parameter('LOC_ID',ppa.legislative_parameters),
hr_us_w2_mt.get_parameter('PER_ID',ppa.legislative_parameters),
hr_us_w2_mt.get_parameter('SSN',ppa.legislative_parameters),
hr_us_w2_mt.get_parameter('STATE',ppa.legislative_parameters),
hr_us_w2_mt.get_parameter('S1',ppa.legislative_parameters),
hr_us_w2_mt.get_parameter('S2',ppa.legislative_parameters),
hr_us_w2_mt.get_parameter('S3',ppa.legislative_parameters),
to_date(hr_us_w2_mt.get_parameter('EFFECTIVE_DATE',ppa.legislative_parameters),'YYYY/MM/DD'),
ppa.effective_date,
ppa.business_group_id
into l_year,
l_gre_id,
l_org_id,
l_loc_id,
l_per_id,
l_ssn,
l_state_code,
l_sort1 ,
l_sort2,
l_sort3,
l_dt, --session_date
l_year_end,
l_bg_id
from pay_payroll_actions ppa
where ppa.payroll_action_id = payactid;
select to_date('31-DEC-'||to_char(l_year),'DD/MM/YYYY')
into l_dt
from dual
where to_date('31-DEC-'||to_char(l_year),'DD/MM/YYYY') > l_dt;
'select mt.rowid
from hr_organization_units hou, hr_locations_all hl, per_periods_of_service pps, per_assignments_f paf,
pay_assignment_actions mt where mt.payroll_action_id = :pactid and
paf.assignment_id = mt.assignment_id and paf.effective_start_date = (select max(paf2.effective_start_date)
from per_assignments_f paf2 where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= to_date(''31-DEC-''||'''||l_year||''',''DD/MM/YYYY''))
and paf.effective_end_date >= to_date(''01-JAN-''||'''||l_year||''',''DD/MM/YYYY'')
and paf.assignment_type = ''E'' and pps.period_of_service_id = paf.period_of_service_id
and pps.person_id = paf.person_id and hl.location_id = paf.location_id
and hou.organization_id = paf.organization_id and hou.business_group_id + 0 = '''||l_bg_id||'''
order by decode('''||l_sort1||''', ''Employee_Name'',
hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_LAST_NAME'')||'' ''
||hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_FIRST_NAME'')||'' ''
||substr(hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_MIDDLE_NAMES''),1,1),
''SSN'',nvl(hr_us_w2_rep.get_per_item( to_number(mt.serial_number), ''A_PER_NATIONAL_IDENTIFIER''), ''Applied For''),
''Zip_Code'',hr_us_w2_rep.get_w2_postal_code( paf.person_id,'''||l_dt||'''),
''Organization'',hou.name, ''Location'',hl.location_code,
''Termination_Reason'',decode(leaving_reason,null,''ZZ'',hr_us_w2_rep.get_leav_reason(leaving_reason)),
hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_LAST_NAME'')||'' ''
||hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_FIRST_NAME'')||'' ''
||substr(hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_MIDDLE_NAMES''),1,1)),
decode('''||l_sort2||''', ''Employee_Name'',
hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_LAST_NAME'')||'' ''
||hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_FIRST_NAME'')||'' ''
||substr(hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_MIDDLE_NAMES''),1,1),
''SSN'',nvl(hr_us_w2_rep.get_per_item(
to_number(mt.serial_number), ''A_PER_NATIONAL_IDENTIFIER''), ''Applied For''),
''Zip_Code'',hr_us_w2_rep.get_w2_postal_code( paf.person_id,'''||l_dt||'''),
''Organization'',hou.name, ''Location'',hl.location_code,
''Termination_Reason'',decode(leaving_reason,null,''ZZ'',hr_us_w2_rep.get_leav_reason(leaving_reason)),
hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_LAST_NAME'')||'' ''
||hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_FIRST_NAME'')||'' ''
||substr(hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_MIDDLE_NAMES''),1,1)),
decode('''||l_sort3||''', ''Employee_Name'', hr_us_w2_rep.get_per_item(to_number(mt.serial_number),
''A_PER_LAST_NAME'')||'' ''||hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_FIRST_NAME'')||'' ''
||substr(hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_MIDDLE_NAMES''),1,1),
''SSN'',nvl(hr_us_w2_rep.get_per_item( to_number(mt.serial_number), ''A_PER_NATIONAL_IDENTIFIER''),
''Applied For''), ''Zip_Code'',hr_us_w2_rep.get_w2_postal_code( paf.person_id,'''||l_dt||'''),
''Organization'',hou.name, ''Location'',hl.location_code, ''Termination_Reason'',decode(leaving_reason,null,''ZZ'',hr_us_w2_rep.get_leav_reason(leaving_reason)),
hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_LAST_NAME'')||'' ''
||hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_FIRST_NAME'')||'' ''
||substr(hr_us_w2_rep.get_per_item(to_number(mt.serial_number), ''A_PER_MIDDLE_NAMES''),1,1))
for update of mt.assignment_action_id' ;