[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
11-SEP-2002 jgoswami 115.6 Changed sort cursor , changed for update
clause.
17-SEP-2002 jgoswami 115.7 Changed action cursor , removed for update
clause.
01-09-20032 asasthan 115.8 Fixes for terminated employee
Changed sort_action, removed join with paf
so that terminated ees get picked and
removed for update of clause.
01-09-20032 asasthan 115.9 Nocopy changes made
20-JAN-2003 jgoswami 115.10 Changed the action_creation cursor to
check for Reduced Subject (A_WAGES) >0 from
Gross (A_W2_GROSS_1099R) >0
22-JAN-2003 jgoswami 115.11 Commented out the code which locks the Year
End Pre-Process when a 1099r Paper
assignment action are created.
11-SEP-2003 jgoswami 115.12 Changed date format in sort cursor as the
EFFECTIVE_DATE value in the legislative parameter
is changed form DD-MON-YYYY to YYYY/MM/DD.
16-JAN-2003 jgoswami 115.14 Changed the action_creation cursor to
check for Gross (A_W2_GROSS_1099R) >0 from
Reduced Subject (A_WAGES) >0.Fix bug 3381162
14-MAR-2005 sackumar 115.15 4222032 Change in the Range Cursor removing
redundant use of bind Variable (:pactid)
14-MAR-2006 jgoswami 115.16 Changed the action_creation procedure for
performance, split c_action cursor to
multiple cursors and added range person
functionality. Multiple cursors created are
c_actions_with_location,
c_actions_with_org, c_actions_with_state,
c_actions_with_person,
c_actions_with_assign_sql
based on the SRS parameters.
24-MAR-2006 jgoswami 115.17 fix gscc errors
01-SEP-2006 saurgupt 115.18 Bug 3913757 : Modified the order by clause in sort_action.
21-SEP-2006 jgoswami 115.19 fix sort cursor exceed length issue
21-SEP-2006 jgoswami 115.20 fix gscc errors
09-NOV-2006 alikhar 115.21 Modified for 1099R PDF. (Bug 5440136)
24-NOV-2006 alikhar 115.22 Added tag PAYER_ADDR_CT_ST_ZP for 1099R PDF
22-DEC-2006 alikhar 115.23 Added tag PRINT_INSTRUCTION for 1099R PDF (5717266)
26-DEC-2006 alikhar 115.24 Fixed GSCC warnings.
15-JUN-2007 vaprakas 115.25 5979491 Corrected the difference between paper
and pdf report
07-SEP-2007 vaprakas 115.26 Modified changes for bug fix 5979491
21-SEP-2007 vaprakas 115.27 Modified code to display the DESIG. ROTH CONTRIB
*/
/******************************************************************
** private package global declarations
******************************************************************/
g_package VARCHAR2(50) := 'pay_1099r_pkg.';
select pay_1099R_pkg.get_parameter('YEAR',ppa.legislative_parameters),
pay_1099R_pkg.get_parameter('TAX_ID',ppa.legislative_parameters),
pay_1099R_pkg.get_parameter('ASSIGN_SET',ppa.legislative_parameters)
into ln_year,
ln_gre_id,
ln_assign_set
from pay_payroll_actions ppa
where ppa.payroll_action_id = pactid;
'SELECT distinct to_number(paa_arch.serial_number)
FROM PAY_ASSIGNMENT_ACTIONS paa_arch,
PAY_PAYROLL_ACTIONS ppa_arch
WHERE :pactid is not null
AND ppa_arch.report_type = ''YREND''
AND to_char(ppa_arch.effective_date,''YYYY'')= '''||ln_year||'''
AND pay_yrend_reports_pkg.get_parameter(''TRANSFER_GRE'',
ppa_arch.legislative_parameters)= '''||ln_gre_id||'''
AND ppa_arch.action_status = ''C''
AND ppa_arch.payroll_action_id = paa_arch.payroll_action_id
order by to_number(paa_arch.serial_number) ';
select to_number(pay_1099R_pkg.get_parameter('YEAR',ppa1.legislative_parameters)),
to_number(pay_1099R_pkg.get_parameter('TAX_ID',ppa1.legislative_parameters)),
to_number(pay_1099R_pkg.get_parameter('ORG_ID',ppa1.legislative_parameters)),
to_number(pay_1099R_pkg.get_parameter('LOC_ID',ppa1.legislative_parameters)),
to_number(pay_1099R_pkg.get_parameter('PER_ID',ppa1.legislative_parameters)),
pay_1099R_pkg.get_parameter('SSN',ppa1.legislative_parameters),
pay_1099R_pkg.get_parameter('ST_COD',ppa1.legislative_parameters),
to_number(pay_1099R_pkg.get_parameter('ASSIGN_SET',ppa1.legislative_parameters)),
ppa.effective_date,
ppa.payroll_action_id,
ppa.start_date,
ppa1.effective_date,
ppa1.report_type,
ppa1.report_qualifier,
ppa1.report_category
from pay_payroll_actions ppa, /* EOY payroll action id */
pay_payroll_actions ppa1 /* PYUGEN payroll action id */
where ppa1.payroll_action_id = cp_pactid
and ppa.effective_date = ppa1.effective_date
and ppa.report_type = 'YREND'
and pay_1099R_pkg.get_parameter
('TAX_ID',ppa1.legislative_parameters) =
pay_1099R_pkg.get_parameter
('TRANSFER_GRE',ppa.legislative_parameters);
select pay_1099R_pkg.get_parameter('YEAR',ppa.legislative_parameters),
pay_1099R_pkg.get_parameter('TAX_ID',ppa.legislative_parameters),
pay_1099R_pkg.get_parameter('ORG_ID',ppa.legislative_parameters),
pay_1099R_pkg.get_parameter('LOC_ID',ppa.legislative_parameters),
pay_1099R_pkg.get_parameter('PER_ID',ppa.legislative_parameters),
pay_1099R_pkg.get_parameter('SSN',ppa.legislative_parameters),
pay_1099R_pkg.get_parameter('ST_COD',ppa.legislative_parameters),
pay_1099R_pkg.get_parameter('ASSIGN_SET',ppa.legislative_parameters),
effective_date,
report_type,
report_qualifier,
report_category
from pay_payroll_actions ppa
where ppa.payroll_action_id = cp_pactid;
/* when person or ssn 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 = l_eoy_payroll_action_id
AND paa_arch.action_status = 'C'
AND paf.PERSON_ID = l_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 <= l_session_date)
AND paf.effective_end_date >= l_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 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((l_loc_id is null ) and
(l_org_id is null ) and
(l_per_id is null ) and
(l_ssn is null ) and
(l_state_code is null ) and
(l_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
WHERE paa_arch.action_status = ''C''
AND paa_arch.payroll_action_id +0= ' || l_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 <= ''' || l_session_date || ''')
AND paf.effective_end_date >= ''' || l_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 :=
'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= ' || l_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 <= ''' || l_session_date || ''')
AND paf.effective_end_date >= ''' || l_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)';
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 = ' || l_eoy_payroll_action_id || '
AND paa_arch.action_status = ''C''
AND paa_arch.assignment_id = paf.assignment_id
AND nvl(pps.final_process_date,''' || l_session_date || ''')
between paf.effective_start_date
and paf.effective_end_date
AND paf.location_id = ' || l_loc_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 <= ''' || l_session_date || ''')
AND paf.effective_end_date >= ''' || l_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
/* disabling the index for performance reason */
WHERE paa_arch.payroll_action_id + 0 = ' || l_eoy_payroll_action_id || '
AND paa_arch.action_status = ''C''
AND paa_arch.assignment_id = paf.assignment_id
AND nvl(pps.final_process_date,''' || l_session_date || ''')
between paf.effective_start_date
and paf.effective_end_date
AND paf.location_id = ' || l_loc_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 <= ''' || l_session_date || ''' )
AND paf.effective_end_date >= ''' || l_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
/* disabling the index for performance reason */
WHERE paa_arch.payroll_action_id +0 = ' || l_eoy_payroll_action_id || '
AND paa_arch.action_status = ''C''
AND nvl(pps.final_process_date,''' || l_session_date || ''')
between paf.effective_start_date
and paf.effective_end_date
AND paf.organization_id = ' || l_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 <= ''' || l_session_date || ''')
AND paf.effective_end_date >= ''' || l_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
/* disabling the index for performance reason */
WHERE paa_arch.payroll_action_id +0 = ' || l_eoy_payroll_action_id || '
AND paa_arch.action_status = ''C''
AND nvl(pps.final_process_date,''' || l_session_date || ''')
between paf.effective_start_date
and paf.effective_end_date
AND paf.organization_id = ' || l_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 <= ''' || l_session_date || ''')
AND paf.effective_end_date >= ''' || l_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 person_id into l_per_id
from per_people_f ppf
where national_identifier = l_ssn
and l_effective_date between effective_start_date
and effective_end_date;
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.payroll_action_id +0 = ' || l_eoy_payroll_action_id || '
AND paa_arch.action_status = ''C''
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 <= ''' || l_session_date || ''')
AND paf.effective_end_date >= ''' || l_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
WHERE paa_arch.payroll_action_id +0 = ' || l_eoy_payroll_action_id || '
AND paa_arch.action_status = ''C''
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 <= ''' || l_session_date || ''')
AND paf.effective_end_date >= ''' || l_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) = ' || l_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_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 = ' || l_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 <= ''' || l_session_date || ''')
AND paf.effective_end_date >= ''' || l_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 = ' || l_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
WHERE paa_arch.action_status = ''C''
AND paa_arch.payroll_action_id + 0 = ' || l_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 <= ''' || l_session_date || ''')
AND paf.effective_end_date >= ''' || l_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 = ' || l_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 to_char(ppa.effective_date, 'DD-MON-YYYY'),
to_char(fnd_date.canonical_to_date(pay_1099R_pkg.get_parameter('EFFECTIVE_DATE',
ppa.legislative_parameters)),'DD-MON-YYYY')
into l_effective_date, l_session_date
from pay_payroll_actions ppa
where payroll_action_id = payactid;
'select paa1.rowid
/* we need the row id of the assignment actions
that are created by PYUGEN */
from pay_assignment_actions paa,
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
and paa.assignment_action_id = paa1.serial_number
order by
decode(pay_1099R_pkg.get_parameter(''SORT_1'',ppa1.legislative_parameters),
''Employee_Name'',
hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_LAST_NAME'') ||
hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_FIRST_NAME'') ||
decode(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES'' ),
null, null,
substr(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES''),1,1)),
''Social_Security_Number'',
nvl(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_NATIONAL_IDENTIFIER''),''Applied For''),
''Zip_Code'',
hr_us_w2_rep.get_w2_postal_code(to_number(paa.serial_number),to_date('''||l_effective_date||''',''DD-MM-YYYY'')),
hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_LAST_NAME'') ||
hr_us_w2_rep.get_per_item( paa.assignment_action_id,''A_PER_FIRST_NAME'') ||
decode(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES''),
null, null,
substr(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES''),1,1))),
decode(pay_1099R_pkg.get_parameter(''SORT_2'',ppa1.legislative_parameters),
''Employee_Name'',
hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_LAST_NAME'') ||
hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_FIRST_NAME'') ||
decode(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES'' ),
null, null,
substr(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES''),1,1)),
''Social_Security_Number'',
nvl(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_NATIONAL_IDENTIFIER''), ''Applied For''),
''Zip_Code'',
hr_us_w2_rep.get_w2_postal_code( to_number(paa.serial_number),
to_date('''||l_effective_date||''',''DD-MM-YYYY'')),
hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_LAST_NAME'') ||
hr_us_w2_rep.get_per_item( paa.assignment_action_id,''A_PER_FIRST_NAME'') ||
decode(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES''),
null, null,
substr(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES''),1,1))),
decode(pay_1099R_pkg.get_parameter(''SORT_3'',ppa1.legislative_parameters),
''Employee_Name'',
hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_LAST_NAME'') ||
hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_FIRST_NAME'') ||
decode(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES'' ),
null, null,
substr(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES''),1,1)),
''Social_Security_Number'',
nvl(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_NATIONAL_IDENTIFIER''), ''Applied For''),
''Zip_Code'',
hr_us_w2_rep.get_w2_postal_code( to_number(paa.serial_number),
to_date('''||l_effective_date||''',''DD-MM-YYYY'')),
hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_LAST_NAME'') ||
hr_us_w2_rep.get_per_item( paa.assignment_action_id,''A_PER_FIRST_NAME'') ||
decode(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES''),
null, null,
substr(hr_us_w2_rep.get_per_item(paa.assignment_action_id,''A_PER_MIDDLE_NAMES''),1,1)))';
SELECT to_number(serial_number)
FROM pay_assignment_actions
WHERE assignment_action_id = p_fed_aaid;
SELECT tax_unit_id st_tax_unit_id,
assignment_id st_assign_id,
decode(state_abbrev, 'NJ', state_abbrev||nvl(replace(replace(state_ein,'-'),'/'),'NO STATE EIN'), state_abbrev||' '||nvl(state_ein,'NO STATE EIN')) state_ein,
w2_box_17 sit_subject,
w2_box_18 sit_withheld
FROM
pay_us_w2_state_v pws
WHERE state_abbrev NOT IN ( 'AK','FL', 'NH','NV','SD','TN','TX','WA','WY')
AND (w2_box_17 <> 0 OR w2_box_18 <> 0)
AND assignment_action_id = p_asg_actid
AND payroll_action_id = pactid
ORDER BY state_abbrev;
SELECT locality_name,
assignment_id lit_assign_id,
tax_unit_id lit_tax_unit_id,
w2_box_20 lit_subject,
w2_box_21 lit_withheld
FROM
pay_us_w2_locality_v
WHERE
w2_box_21 <> 0
AND assignment_action_id = p_asg_actid
AND payroll_action_id = pactid;
SELECT
to_number(pay_1099R_pkg.get_parameter('YREND_PACTID',ppa.legislative_parameters)) yrend_pactid,
to_number(paa.serial_number) fed_aaid,
v1099r.year year,
v1099r.gross_1099r gross_1099r,
v1099r.wages_tips_compensation fit_subject,
v1099r.taxable_amt_1099r taxable_amt_1099r,
v1099r.fed_it_withheld fit_withheld,
v1099r.ssn ssn,
v1099r.first_name ||' '||v1099r.middle_name||' ' ||v1099r.pre_name_adjunt ||' '||v1099r.last_name employee_name,
v1099r.federal_ein federal_ein,
v1099r.tax_unit_name tax_unit_name,
rpad(substr(hr_us_w2_rep.get_tax_unit_addr_line(paa.tax_unit_id,'ADDR1'),1,30),31,' ')
||decode( hr_us_w2_rep.get_tax_unit_addr_line(paa.tax_unit_id,'ADDR2'),null,null,
rpad(substr( hr_us_w2_rep.get_tax_unit_addr_line(paa.tax_unit_id,'ADDR2') ,1,30),31,' '))
||decode( hr_us_w2_rep.get_tax_unit_addr_line(paa.tax_unit_id,'ADDR3') ,null,null,
rpad(substr( hr_us_w2_rep.get_tax_unit_addr_line(paa.tax_unit_id,'ADDR3') ,1,30),31,' ')) tax_unit_address,
substr( hr_us_w2_rep.get_tax_unit_addr_line(paa.tax_unit_id,'CITY') ,1,29)||', '||
hr_us_w2_rep.get_tax_unit_addr_line(paa.tax_unit_id,'STATE') ||' '||
hr_us_w2_rep.get_tax_unit_addr_line(paa.tax_unit_id,'ZIP') ct_st_zp,
decode(v1099r.taxable_amount_unknown,'Y','X',null) taxable_amt_unknown,
decode(v1099r.total_distributions,'Y','X',null) total_distributions,
v1099r.employee_distribution_percent ee_dstr_pr,
v1099r.total_distribution_percent tot_dstr_pr,
v1099r.capital_gain,
v1099r.ee_contributions_or_premiums ee_cont_prem,
v1099r.unrealized_net_er_security un_net_er,
v1099r.other_ee_annuity_contract_amt ee_anuity,
v1099r.total_ee_contributions tot_ee_contr,
nvl(hr_us_w2_rep.get_per_item(v1099r.assignment_action_id, 'A_DISTRIBUTION_CODE_FOR_1099R'),'7') ee_distribution_code,
v1099r.defferal_year defferal_year
from
PAY_ASSIGNMENT_ACTIONS PAA, --PYUGEN
PAY_PAYROLL_ACTIONS PPA, --PYUGEN
PAY_US_WAGES_1099r_v v1099r
WHERE
paa.assignment_action_id = p_asg_actid
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.serial_number = v1099r.assignment_action_id;
SELECT ppa.legislative_parameters,
fnd_date.canonical_to_date(pay_1099r_pkg.get_parameter('EFFECTIVE_DATE',ppa.legislative_parameters)),
pay_1099r_pkg.get_parameter('PRINT_INSTRUCTION',ppa.legislative_parameters)
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE paa.assignment_action_id = asg_actid
and ppa.payroll_action_id = paa.payroll_action_id;