[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
25-OCT-2004 schauhan 115.11 3601799 Added selection criteria for "All"
if the report is Run with print
option "Reprint All W2c".
Made changes to w2crpt_range_cursor
and w2crpt_action_creation Cursor.
05-NOV-2004 schauhan 115.12 Added 'Distinct' to the Range Cursor
w2crpt_range_cursor.
22-NOV-2004 ahanda 115.13 3601799 Fixed issue in the bug. Changed the
action creation, range and sort
procedures.
16-DEC-2004 ahanda 115.14 4039440 Changed sort code to reduce length
to get around c-code issue.
14-MAR-2005 sackumar 115.15 4222032 Change in the Range Cursor removing redundant
use of bind Variable (:payroll_action_id)
04-AUG-2005 pragupta 115.16 3679317 Change in procedure sort_action. Removed
+0 from paf and hou for removing FTS and
performance improvement
14-MAR-2006 ppanda 115.17 4583575 To reduce the shared memory per_all_assignments_f
used instead of per_assignments_f.
31-MAR-2006 sodhingr 115.18 5111088 removed the comment from sort_cursor to fix signal
11 error.
*****************************************************************************/
gv_package VARCHAR2(100);
select
pay_us_payroll_utils.get_parameter('TRANSFER_GRE',ppa.legislative_parameters),
pay_us_payroll_utils.get_parameter('PER_ID',ppa.legislative_parameters),
pay_us_payroll_utils.get_parameter('ASG_SET',ppa.legislative_parameters),
pay_us_payroll_utils.get_parameter('PRINT',ppa.legislative_parameters),
effective_date,
start_date,
business_group_id,
pay_us_payroll_utils.get_parameter('S1',ppa.legislative_parameters),
pay_us_payroll_utils.get_parameter('S2',ppa.legislative_parameters),
pay_us_payroll_utils.get_parameter('S3',ppa.legislative_parameters),
to_date(pay_us_payroll_utils.get_parameter('EFFECTIVE_DATE',
ppa.legislative_parameters)
,'dd-mon-yyyy')
from pay_payroll_actions ppa
where ppa.payroll_action_id = cp_payroll_action_id;
Purpose : This returns the select statement that is
used to created the range rows for the
W-2C PAPER.
Arguments :
Notes : Calls procedure - get_payroll_action_info
******************************************************************/
PROCEDURE w2crpt_range_cursor(
p_payroll_action_id in number
,p_sqlstr out nocopy varchar2)
IS
ld_end_date DATE;
'select distinct asg.person_id person_id
from per_all_assignments_f asg
where person_id = ' || ln_person_id ||
' and :p_payroll_action_id is not null ';
'select distinct paf.person_id
from hr_assignment_set_amendments asgset,
per_all_assignments_f paf
where assignment_set_id = ' || ln_asg_set || '
and asgset.assignment_id = paf.assignment_id
and asgset.include_or_exclude = ''I''
and :payroll_action_id is not null ';
'select distinct paa.serial_number
from pay_assignment_actions paa,
pay_payroll_actions ppa
where ppa.report_type = ''W2C_PRE_PROCESS''
and ppa.effective_date = add_months(''' || ld_start_date || ''',12) -1
and ppa.business_group_id+0 = ' || ln_business_group_id ||'
and ltrim(rtrim(
substr(ppa.legislative_parameters,
instr(ppa.legislative_parameters,''TRANSFER_GRE='')
+ length(''TRANSFER_GRE='')
,instr(ppa.legislative_parameters,'' '',2))))
= to_char(' || ln_tax_unit_id || ')
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = ''C''
and paa.tax_unit_id = ' || ln_tax_unit_id || '
and :payroll_action_id is not null
order by paa.serial_number';
CURSOR c_selected_asg_set(cp_start_person in number
,cp_end_person in number
,cp_asg_set in number) is
select distinct paf.person_id
from hr_assignment_set_amendments asgset,
per_all_assignments_f paf
where assignment_set_id = cp_asg_set
and asgset.include_or_exclude = 'I'
and paf.assignment_id = asgset.assignment_id
and paf.person_id between cp_start_person
and cp_end_person;
CURSOR c_select_all_person(cp_start_person in number,
cp_end_person in number,
cp_start_date in date,
cp_business_group_id in number,
cp_tax_unit_id in number) IS
select distinct paf.person_id
from pay_assignment_actions paa,
pay_payroll_actions ppa,
per_all_assignments_f paf
where ppa.report_type = 'W-2C PAPER'
and ppa.report_category = 'REPORT'
and ppa.report_qualifier = 'DEFAULT'
and ppa.effective_date = add_months(cp_start_date,12) -1
and ppa.business_group_id = cp_business_group_id
and ppa.legislative_parameters like '%' || cp_tax_unit_id || '%'
and paa.payroll_action_id = ppa.payroll_action_id
and paa.action_status = 'C'
and paa.tax_unit_id = cp_tax_unit_id
and paf.assignment_id = paa.assignment_id
and paf.effective_end_date =
(SELECT max(paf1.effective_end_date)
FROM per_all_assignments_f paf1
WHERE paf1.assignment_id = paf.assignment_id
AND paf1.effective_start_date <= ppa.effective_date)
and paf.person_id between cp_start_person and cp_end_person;
select ppa.report_type, paa.assignment_id,
paa.assignment_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_all_assignments_f paf
where paa.assignment_id = paf.assignment_id
and paf.person_id = cp_person_id
and paf.effective_start_date <= cp_effective_date
and paf.effective_end_date >= cp_start_date
and paa.tax_unit_id = cp_tax_unit_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date = cp_effective_date
and ppa.report_type in ('W2C_PRE_PROCESS','W-2C PAPER')
and paf.effective_end_date =
(SELECT max(paf1.effective_end_date)
FROM per_all_assignments_f paf1
WHERE paf1.assignment_id = paf.assignment_id
AND paf1.effective_start_date <= ppa.effective_date)
order by paa.assignment_action_id desc;
select ppa.report_type, paa.assignment_id,
paa.assignment_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
per_all_assignments_f paf
where paa.assignment_id = paf.assignment_id
and paf.person_id = cp_person_id
and paf.effective_start_date <= cp_effective_date
and paf.effective_end_date >= cp_start_date
and paa.tax_unit_id = cp_tax_unit_id
and paa.action_status = 'C'
and ppa.payroll_action_id = paa.payroll_action_id
and ppa.effective_date = cp_effective_date
and ppa.report_type = 'W2C_PRE_PROCESS'
and ppa.report_category = 'RT'
and ppa.report_qualifier = 'FED'
and ppa.business_group_id = cp_business_group_id
and paf.effective_end_date =
(SELECT max(paf1.effective_end_date)
FROM per_all_assignments_f paf1
WHERE paf1.assignment_id = paf.assignment_id
AND paf1.effective_start_date <= ppa.effective_date)
and exists (select 1
from pay_action_interlocks pai,
pay_assignment_actions paa_paper,
pay_payroll_Actions ppa_paper
where pai.locked_action_id = paa.assignment_action_id
and paa_paper.assignment_Action_id = pai.locking_action_id
and ppa_paper.payroll_Action_id = paa_paper.payroll_Action_id
and ppa_paper.report_type = 'W-2C PAPER'
and ppa_paper.report_category = 'REPORT'
and ppa_paper.report_qualifier = 'DEFAULT'
and ppa_paper.effective_date = cp_effective_date
and ppa_paper.business_group_id = cp_business_group_id)
order by paa.assignment_action_id desc;
select ppa.report_type,
paa.assignment_action_id,
substr(paa.serial_number, 1,15) prev_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
where pai.locking_action_id = cp_w2cpp_action
and paa.assignment_action_id = pai.locked_action_id
and ppa.payroll_action_id = paa.payroll_action_id;
select substr(full_name,1,48), employee_number
from per_all_people_f
where person_id = cp_person_id
order by effective_end_date desc;
select pay_assignment_actions_s.nextval
into ln_w2c_asg_action
from dual;
/* Insert into pay_assignment_actions. */
hr_nonrun_asact.insact(ln_w2c_asg_action
,ln_primary_assignment_id
,p_payroll_action_id
,p_chunk
,ln_tax_unit_id);
** Update the serial number column with the assignment action
** of the last two archive processes
***************************************************************/
ln_serial_number := lpad(ln_asg_act_to_lock,15,0)||
lpad(ln_second_last_arch_action,15,0);
update pay_assignment_actions aa
set aa.serial_number = ln_serial_number
where aa.assignment_action_id = ln_w2c_asg_action;
select pay_assignment_actions_s.nextval
into ln_w2c_asg_action
from dual;
/* Insert into pay_assignment_actions. */
hr_nonrun_asact.insact(ln_w2c_asg_action
,ln_primary_assignment_id
,p_payroll_action_id
,p_chunk
,ln_tax_unit_id);
** Update the serial number column with the assignment action
** of the last two archive processes
***************************************************************/
ln_serial_number := lpad(ln_asg_act_to_lock,15,0)||
lpad(ln_second_last_arch_action,15,0);
update pay_assignment_actions aa
set aa.serial_number = ln_serial_number
where aa.assignment_action_id = ln_w2c_asg_action;
open c_selected_asg_set(p_start_person_id
,p_end_person_id
,ln_asg_set);
hr_utility.trace('Opened cusor c_selected_asg_set');
fetch c_selected_asg_set into ln_set_person_id;
if c_selected_asg_set%notfound then
hr_utility.trace('No Person found for reporting in this chunk');
close c_selected_asg_set;
open c_select_all_person(p_start_person_id
,p_end_person_id
,ld_start_date
,ln_business_group_id
,ln_tax_unit_id);
hr_utility.trace('Opened cusor c_select_all_person');
fetch c_select_all_person into ln_person_id;
if c_select_all_person%notfound then
hr_utility.trace('No Person found for reporting in this chunk.');
close c_select_all_person;
'select mt.rowid
from hr_organization_units hou, hr_locations_all hl,
per_periods_of_service pps, per_all_assignments_f paf,
pay_assignment_actions mt
where mt.payroll_action_id = :p_payroll_action_id
and paf.assignment_id = mt.assignment_id -- Bug 3679317 ( +0 removed)
and paf.effective_start_date = (select max(paf2.effective_start_date)
from per_all_assignments_f paf2 where paf2.assignment_id = paf.assignment_id
and paf2.effective_start_date <= to_date(''' || to_char(ld_end_date,'dd-mon-yyyy') || ''',''dd-mon-yyyy''))
and paf.effective_end_date >= to_date('''|| to_char(ld_start_date,'dd-mon-yyyy') ||''',''dd-mon-yyyy'')
and paf.assignment_type = ''E'' and hou.organization_id = paf.organization_id
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.business_group_id = '''|| ln_business_group_id ||'''
order by decode('''||lv_sort1||''', ''Employee_Name'',
hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_LAST_NAME'')||'' ''
||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_FIRST_NAME'')||'' ''
||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1),
''SSN'',nvl(hr_us_w2_rep.get_per_item( to_number(substr(mt.serial_number,1,15)), ''A_PER_NATIONAL_IDENTIFIER''),
''Applied For''),
''Zip_Code'',hr_us_w2_rep.get_w2_postal_code( paf.person_id,to_date('''|| to_char(ld_session_date,'dd-mon-yyyy') ||''',''dd-mon-yyyy'')),
''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(substr(mt.serial_number,1,15)), ''A_PER_LAST_NAME'')||'' ''
||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_FIRST_NAME'')||'' ''
||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1)),
decode('''||lv_sort2||''', ''Employee_Name'',
hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_LAST_NAME'')||'' ''
||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,5)), ''A_PER_FIRST_NAME'')||'' ''
||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1),
''SSN'',nvl(hr_us_w2_rep.get_per_item(
to_number(substr(mt.serial_number,1,15)), ''A_PER_NATIONAL_IDENTIFIER''), ''Applied For''),
''Zip_Code'',hr_us_w2_rep.get_w2_postal_code( paf.person_id,to_date('''|| to_char(ld_session_date,'dd-mon-yyyy') ||''',''dd-mon-yyyy'')),
''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(substr(mt.serial_number,1,15)), ''A_PER_LAST_NAME'')||'' ''
||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_FIRST_NAME'')||'' ''
||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1)),
decode('''||lv_sort3||''', ''Employee_Name'', hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)),
''A_PER_LAST_NAME'')||'' ''
||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,5)), ''A_PER_FIRST_NAME'')||'' ''
||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1),
''SSN'',nvl(hr_us_w2_rep.get_per_item( to_number(substr(mt.serial_number,1,5)), ''A_PER_NATIONAL_IDENTIFIER''),
''Applied For''), ''Zip_Code'',hr_us_w2_rep.get_w2_postal_code( paf.person_id,to_date('''||to_char(ld_session_date, 'dd-mon-yyyy')||''',''dd-mon-yyyy'')),
''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(substr(mt.serial_number,1,15)), ''A_PER_LAST_NAME'')||'' ''
||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_FIRST_NAME'')||'' ''
||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,5)), ''A_PER_MIDDLE_NAMES''),1,1))';