1 PACKAGE BODY pay_us_w2c_arch AS
2 /* $Header: pyusw2cp.pkb 120.1.12010000.2 2008/08/06 08:40:18 ubhat ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_w2c_arch
21
22 Description : This procedure is used by W-2C Pre-Process
23 to archive data for W-2C Corrections Reporting.
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ------ ------- --------------------------
29 02-AUG-2002 asasthan 115.0 Created.
30 07-FEB-2003 asasthan 115.3 Closed the prev_w2 cursor.
31 07-FEB-2003 asasthan 115.6 added distinct person_id for
32 asg_set
33 11-FEB-2003 asasthan 115.9 Removed ref to hr_us_w2_mt
34 18-FEB-2003 asasthan 115.10 replaced ln_person_id with
35 p_person_id within action_creation
36 19-FEB-2003 asasthan 115.11 changed get_prev curosor
37 added date effective join
38 19-FEB-2003 asasthan 115.12 added SSN to log message
39 27-FEB-2003 asasthan 115.13 changed message text - doc review
40 15-SEP-2003 rsethupa 115.14 2819817 created cursor to pick up the
41 latest processed primary assignment
42 in the w2c_action_creation
43 procedure.
44 12-NOV-2004 meshah 115.15 4009534 increased the length of variable
45 lv_message to 150.
46 15-NOV-2004 meshah 115.16 fixed gscc error File_Sql_6
47 29-MAR-2005 sackumar 115.17 4222032 Change in the Range Cursor removing redundant
48 use of bind Variable (:payroll_action_id)
49 02-AUG-2005 rsethupa 115.18 4349941 Added end_date check to cursor
50 c_selected_asg_set in w2c_action_creation
51 procedure for Perf Improvement
52 31-MAR-2008 asgugupt 115.19 6909112 Set ln_primary_assignment_id properly
53 for secondary assignment in
54 procedure w2c_action_creation
55 *****************************************************************************/
56
57 gv_package VARCHAR2(100) := 'pay_us_w2c_arch';
58 gv_procedure_name VARCHAR2(100);
59
60
61
62 /*****************************************************************************
63 Name : get_eoy_action_info
64 Purpose : This returns the Payroll Action level
65 information for YREND Archiver.
66 Arguments : p_payroll_action_id - Payroll_Action_id of EOY
67 p_w2c_date - End date of W2C Pre Process
68 ******************************************************************************/
69 PROCEDURE get_eoy_action_info(p_w2c_eff_date in date
70 ,p_w2c_tax_unit_id in number
71 ,p_eoy_pactid out nocopy number
72 )
73 IS
74
75 CURSOR get_eoy_info(cp_w2c_eff_date in date
76 ,cp_w2c_tax_unit_id in number) is
77 select ppa.payroll_action_id
78 from pay_payroll_actions ppa
79 where ppa.report_type = 'YREND'
80 and ppa.effective_date = cp_w2c_eff_date
81 and pay_us_w2c_arch.get_parameter('TRANSFER_GRE',ppa.legislative_parameters)
82 = cp_w2c_tax_unit_id;
83
84 ln_eoy_pactid number :=0;
85
86 BEGIN
87
88 hr_utility.trace('Entered get_eoy_action_info');
89
90 open get_eoy_info(p_w2c_eff_date
91 ,p_w2c_tax_unit_id);
92
93 hr_utility.trace('Opened get_eoy_info');
94
95 fetch get_eoy_info into ln_eoy_pactid;
96
97 hr_utility.trace('Fetched get_eoy_info ');
98
99 close get_eoy_info;
100
101 hr_utility.trace('Closed get_eoy_info ');
102
103 p_eoy_pactid := ln_eoy_pactid;
104
105 hr_utility.trace('ln_eoy_pactid = ' ||
106 to_char(ln_eoy_pactid));
107 hr_utility.trace('Leaving get_eoy_action_info');
108
109 EXCEPTION
110 when others then
111 hr_utility.trace('Error in ' || gv_procedure_name ||
112 to_char(sqlcode) || '-' || sqlerrm);
113 raise hr_utility.hr_error;
114
115 END get_eoy_action_info;
116
117 /*****************************************************************************
118 Name : get_payroll_action_info
119 Purpose : This returns the Payroll Action level
120 information for W-2C Archiver.
121 Arguments : p_payroll_action_id - Payroll_Action_id of archiver
122 p_start_date - Start date of Archiver
123 p_end_date - End date of Archiver
124 p_business_group_id - Business Group ID
125 ******************************************************************************/
126 PROCEDURE get_payroll_action_info(p_payroll_action_id in number
127 ,p_end_date out nocopy date
128 ,p_start_date out nocopy date
129 ,p_business_group_id out nocopy number
130 ,p_tax_unit_id out nocopy number
131 ,p_person_id out nocopy number
132 ,p_asg_set out nocopy number
133 )
134 IS
135 cursor c_payroll_Action_info (cp_payroll_action_id in number) is
136 select to_number(pay_us_w2c_arch.get_parameter(
137 'TRANSFER_GRE',ppa.legislative_parameters)),
138 to_number(pay_us_w2c_arch.get_parameter(
139 'PER_ID',ppa.legislative_parameters)),
140 to_number(pay_us_w2c_arch.get_parameter(
141 'SSN',ppa.legislative_parameters)),
142 to_number(pay_us_w2c_arch.get_parameter(
143 'ASG_SET',ppa.legislative_parameters)),
144 to_char(effective_date,'YYYY') ,
145 effective_date,
146 start_date,
147 business_group_id
148 from pay_payroll_actions ppa
149 where ppa.payroll_action_id = cp_payroll_action_id;
150
151 ld_end_date DATE;
152 ld_start_date DATE;
153 ln_business_group_id NUMBER;
154 ln_tax_unit_id NUMBER := 0;
155 ln_person_id NUMBER := 0;
156 ln_asg_set NUMBER := 0;
157 lv_ssn per_people_f.national_identifier%TYPE;
158 lv_year VARCHAR2(4) := 0;
159
160 BEGIN
161 hr_utility.trace('Entered get_payroll_action_info');
162 open c_payroll_action_info(p_payroll_action_id);
163 fetch c_payroll_action_info into ln_tax_unit_id,
164 ln_person_id,
165 lv_ssn,
166 ln_asg_set,
167 lv_year,
168 ld_end_date,
169 ld_start_date,
170 ln_business_group_id;
171 close c_payroll_action_info;
172
173 hr_utility.trace('ld_end_date = ' || to_char(ld_end_date));
174 hr_utility.trace('ld_start_date = ' || to_char(ld_start_date));
175 hr_utility.trace('ln_tax_unit_id = '|| to_char(ln_tax_unit_id));
176 hr_utility.trace('ln_person_id = ' || to_char(ln_person_id));
177 hr_utility.trace('lv_ssn = ' || lv_ssn);
178 hr_utility.trace('ln_asg_set = ' || to_char(ln_asg_set));
179 hr_utility.trace('lv_year = ' || lv_year);
180
181 p_end_date := ld_end_date;
182 p_start_date := ld_start_date;
183 p_business_group_id := ln_business_group_id;
184 p_tax_unit_id := ln_tax_unit_id;
185 p_person_id := ln_person_id;
186 p_asg_set := ln_asg_set;
187
188 hr_utility.trace('Leaving get_payroll_action_info');
189
190 EXCEPTION
191 when others then
192 hr_utility.trace('Error in ' || gv_procedure_name ||
193 to_char(sqlcode) || '-' || sqlerrm);
194 raise hr_utility.hr_error;
195
196 END get_payroll_action_info;
197
198
199
200 /******************************************************************
201 Name : w2c_range_cursor
202 Purpose : This returns the select statement that is
203 used to created the range rows for the
204 W-2C Archiver.
205 Arguments :
206 Notes : Calls procedure - get_payroll_action_info
207 ******************************************************************/
208 PROCEDURE w2c_range_cursor(
209 p_payroll_action_id in number
210 ,p_sqlstr out nocopy varchar2)
211 IS
212
213 ld_end_date DATE;
214 ld_start_date DATE;
215 ln_business_group_id NUMBER;
216 ln_tax_unit_id NUMBER;
217 ln_person_id NUMBER := 0;
218 lv_ssn per_people_f.national_identifier%TYPE ;
219 ln_asg_set NUMBER := 0;
220 lv_year VARCHAR2(4) := 0;
221 lv_sql_string VARCHAR2(32000);
222 ln_eoy_pactid number;
223
224 BEGIN
225 hr_utility.trace('Entered w2c_range_cursor');
226 hr_utility.trace('p_payroll_action_id = ' ||
227 to_char(p_payroll_action_id));
228
229 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
230 ,p_start_date => ld_start_date
231 ,p_end_date => ld_end_date
232 ,p_business_group_id => ln_business_group_id
233 ,p_tax_unit_id => ln_tax_unit_id
234 ,p_person_id => ln_person_id
235 ,p_asg_set => ln_asg_set);
236
237 if ln_person_id is not null then
238
239 lv_sql_string :=
240 'select distinct asg.person_id person_id
241 from per_all_assignments_f asg
242 where person_id = ' || to_char(ln_person_id) ||
243 ' and :p_payroll_action_id is not null';
244
245 hr_utility.trace('Range for person_id not null');
246
247 elsif ln_asg_set is not null then
248
249 lv_sql_string :=
250 'select distinct paf.person_id
251 from hr_assignment_set_amendments asgset,
252 per_all_assignments_f paf
253 where assignment_set_id = ''' || ln_asg_set || '''
254 and asgset.assignment_id = paf.assignment_id
255 and asgset.include_or_exclude = ''I''
256 and :payroll_action_id is not null ';
257
258 hr_utility.trace('Range for asg_set not null');
259 end if;
260
261 p_sqlstr := lv_sql_string;
262 hr_utility.trace('p_sqlstr = ' ||p_sqlstr);
263
264
265
266 hr_utility.trace('Leaving w2c_range_cursor');
267 END w2c_range_cursor;
268
269
270 /************************************************************
271 Name : w2c_action_creation
272 Purpose : This creates the assignment actions for
273 a specific chunk of people to be archived
274 by the W2C Archiver process.
275 Arguments :
276 Notes : Calls procedure - get_payroll_action_info
277 ************************************************************/
278
279 PROCEDURE w2c_action_creation(
280 p_payroll_action_id in number
281 ,p_start_person_id in number
282 ,p_end_person_id in number
283 ,p_chunk in number)
284
285 IS
286
287 ln_assignment_id NUMBER := 0;
288 ln_tax_unit_id NUMBER := 0;
289 ld_effective_date DATE ;
290 ln_asg_action_id NUMBER := 0;
291 ln_primary_assignment_id NUMBER := 0;
292 ln_yepp_aaid NUMBER := 0;
293 ln_payroll_action_id NUMBER := 0;
294 ln_w2c_asg_action NUMBER := 0;
295 lv_year VARCHAR2(4) := 0;
296
297 ld_end_date DATE;
298 ld_start_date DATE;
299 ln_business_group_id NUMBER;
300 ln_person_id NUMBER := 0 ;
301 ln_person_id_sel NUMBER := 0 ;
302 lv_ssn per_people_f.national_identifier%TYPE ;
303 ln_asg_set NUMBER := 0 ;
304 ln_prev_asg_action_id NUMBER := 0;
305 ln_prev_assignment_id NUMBER := 0;
306 ln_prev_tax_unit_id NUMBER := 0;
307 ld_prev_effective_date DATE ;
308 lv_report_type pay_payroll_actions.report_type%TYPE ;
309 ln_asg_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
310
311
312 lv_serial_number VARCHAR2(30);
313 ln_eoy_pactid number:= 0;
314 lv_national_identifier per_all_people_f.national_identifier%type;
315 lv_message varchar2(150):= null;
316 lv_full_name per_all_people_f.full_name%type;
317 lv_name varchar2(50);
318 lv_record_name varchar2(50);
319 --bug 6909112 starts here
320 l_tmp_asg_id per_all_assignments_f.assignment_id%TYPE;
321 --bug 6909112 ends here
322
323
324 CURSOR c_selected_asg_set(cp_start_person in number
325 ,cp_end_person in number
326 ,cp_asg_set in number
327 ,cp_eoy_pactid in number
328 ,cp_year_start_date in date) is
329 select distinct paf.person_id
330 from hr_assignment_set_amendments asgset,
331 per_all_assignments_f paf
332 where assignment_set_id = cp_asg_set
333 and asgset.include_or_exclude = 'I'
334 and paf.assignment_id = asgset.assignment_id
335 --and exists (select 1 from pay_assignment_actions paa
336 -- where paa.assignment_id = asgset.assignment_id
337 -- and paa.payroll_action_id = cp_eoy_pactid)
338 and paf.person_id between cp_start_person
339 and cp_end_person
340 and paf.effective_end_date >= cp_year_start_date; /* Bug 4349941 */
341
342 PROCEDURE action_creation (p_person_id in NUMBER)
343 IS
344
345
346 CURSOR get_prev_w2c_dtls (cp_person_id in number
347 ,cp_tax_unit_id in number
348 ,cp_effective_date in date
349 ,cp_start_date in date) is
350 select ppa.report_type, paa.assignment_id,
351 paa.assignment_action_id
352 from pay_payroll_actions ppa,
353 pay_assignment_actions paa,
354 per_assignments_f paf
355 where paa.assignment_id = paf.assignment_id
356 and paf.person_id = cp_person_id
357 and paf.effective_start_date <= cp_effective_date
358 and paf.effective_end_date >= cp_start_date
359 and paa.tax_unit_id = cp_tax_unit_id
360 and paa.action_status = 'C'
361 and ppa.payroll_action_id = paa.payroll_action_id
362 and ppa.effective_date = cp_effective_date
363 and ppa.report_type in ('YREND', 'W2C_PRE_PROCESS', 'W-2C PAPER')
364 and paf.effective_end_date =
365 (SELECT max(paf1.effective_end_date)
366 FROM per_assignments_f paf1
367 WHERE paf1.assignment_id = paf.assignment_id
368 AND paf1.effective_start_date <= ppa.effective_date
369 )
370 order by paa.assignment_action_id desc;
371
372
373 CURSOR get_warning_dtls_for_ee(cp_person_id in number) is
374 select substr(full_name,1,31), substr(national_identifier,1,11)
375 from per_all_people_f
376 where person_id = cp_person_id
377 ORDER BY effective_end_date desc;
378
379
380 /* Cursor to pick up latest processed primary assignment for
381 rehired employee*/
382
383 CURSOR get_rehired_emp (cp_person_id in number,cp_tax_unit_id in number
384 ,cp_start_date in date, cp_end_date in date) is
385 SELECT max(paa.assignment_id)
386 FROM
387 pay_assignment_actions paa,
388 per_assignments_f paf,
389 pay_payroll_actions ppa,
390 pay_action_classifications pac
391 WHERE paf.person_id = cp_person_id
392 AND paa.assignment_id = paf.assignment_id
393 and paf.primary_flag = 'Y'
394 AND paa.tax_unit_id = cp_tax_unit_id
395 and ppa.action_status ='C'
396 and paa.action_status = 'C'
397 AND paa.payroll_action_id = ppa.payroll_action_id
398 AND ppa.action_type = pac.action_type
399 AND pac.classification_name = 'SEQUENCED'
400 AND ppa.effective_date BETWEEN paf.effective_start_date
401 AND paf.effective_end_date
402 AND ppa.effective_date BETWEEN cp_start_date and
403 cp_end_date;
404
405
406 BEGIN
407
408 /*need to determine if the selected person has
409 any unprinted W2Cs. In this case we would not
410 create an action for him. Messages should be pushed in the
411 logs saying why his action was not created.
412
413 Actions will be created if
414
415 #1 there is a YEPP action not followed by a
416 W2C_PRE_PROCESS action
417
418 #2 there is a W2C_PRE_PROCESS action for this person
419 which is followed by a W2C report process */
420
421 open get_prev_w2c_dtls(p_person_id
422 ,ln_tax_unit_id
423 ,ld_end_date
424 ,ld_start_date);
425 fetch get_prev_w2c_dtls into lv_report_type
426 ,ln_primary_assignment_id
427 ,ln_asg_act_to_lock;
428 if get_prev_w2c_dtls%notfound then
429
430 open get_warning_dtls_for_ee(p_person_id);
431 fetch get_warning_dtls_for_ee into lv_full_name
432 ,lv_national_identifier;
433 hr_utility.trace('lv_full_name ='||lv_full_name);
434 hr_utility.trace('lv_national_identifier ='||lv_national_identifier);
435 lv_name := lv_full_name || ', SSN '||lv_national_identifier;
436
437
438 close get_warning_dtls_for_ee;
439
440 lv_record_name := 'W-2c Pre-Process';
441 lv_message := 'The Year End Pre-Process was not run for this employee';
442
443 /* push message into pay_message_lines */
444 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','P');
445 pay_core_utils.push_token('record_name',lv_record_name);
446 pay_core_utils.push_token('name_or_number',lv_name);
447 pay_core_utils.push_token('description',lv_message);
448
449 end if;
450
451
452 if get_prev_w2c_dtls%found then
453 if lv_report_type in ('YREND', 'W-2C PAPER') then
454
455 /* Create an assignment action for this person */
456 select pay_assignment_actions_s.nextval
457 into ln_w2c_asg_action
458 from dual;
459 hr_utility.trace('New w2c Action = ' ||
460 to_char(ln_w2c_asg_action));
461
462 /* Insert into pay_assignment_actions. */
463 hr_utility.trace('creating asg action');
464
465 /*Bug No. 2819817- pick up latest processed primary assignment
466 In case a terminated employee is rehired and paid, the
467 latest processed assignment is picked. */
468 --bug 6909112 starts here
469 l_tmp_asg_id:=ln_primary_assignment_id;
470 --bug 6909112 ends here
471 open get_rehired_emp(p_person_id
472 ,ln_tax_unit_id
473 ,ld_start_date
474 ,ld_end_date);
475 fetch get_rehired_emp into ln_primary_assignment_id;
476 --bug 6909112 starts here
477 if ln_primary_assignment_id is null then
478 ln_primary_assignment_id:=l_tmp_asg_id;
479 end if;
480 --bug 6909112 ends here
481 close get_rehired_emp;
482 hr_nonrun_asact.insact(ln_w2c_asg_action
483 ,ln_primary_assignment_id
484 ,p_payroll_action_id
485 ,p_chunk
486 ,ln_tax_unit_id);
487
488 /* Update the serial number column with the person id
489 so that the W2C report will not have
490 to do an additional checking against the assignment
491 table */
492
493 hr_utility.trace('updating asg action');
494
495 update pay_assignment_actions aa
496 set aa.serial_number = p_person_id
497 where aa.assignment_action_id = ln_w2c_asg_action;
498
499 /* Interlock the yepp/last w2c report
500 action with current w2c action */
501
502 hr_utility.trace('Locking Action = ' || ln_w2c_asg_action);
503 hr_utility.trace('Locked Action = ' || ln_asg_act_to_lock);
504 hr_nonrun_asact.insint(ln_w2c_asg_action
505 ,ln_asg_act_to_lock);
506
507 elsif lv_report_type = 'W2C_PRE_PROCESS' then
508
509
510 open get_warning_dtls_for_ee(p_person_id);
511 fetch get_warning_dtls_for_ee into lv_full_name
512 ,lv_national_identifier;
513 hr_utility.trace('lv_full_name ='||lv_full_name);
514 hr_utility.trace('lv_national_identifier ='||lv_national_identifier);
515 lv_name := lv_full_name || ', SSN '||lv_national_identifier;
516
517 close get_warning_dtls_for_ee;
518
519 lv_record_name := 'W-2c Pre-Process';
520 lv_message := 'An unprinted W-2c exists';
521
522 /* push message into pay_message_lines */
523 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','P');
524 pay_core_utils.push_token('record_name',lv_record_name);
525 pay_core_utils.push_token('name_or_number',lv_name);
526 pay_core_utils.push_token('description',lv_message);
527 end if; /* report type */
528
529 end if; /* employee found*/
530
531 close get_prev_w2c_dtls;
532
533 END action_creation;
534
535 BEGIN
536 hr_utility.trace('Entered action_creation ');
537 hr_utility.trace('p_payroll_action_id = '|| to_char(p_payroll_action_id));
538 hr_utility.trace('p_start_person_id ='|| to_char(p_start_person_id));
539 hr_utility.trace('p_end_person_id = '|| to_char(p_end_person_id));
540 hr_utility.trace('p_chunk = ' || to_char(p_chunk));
541
542 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
543 ,p_start_date => ld_start_date
544 ,p_end_date => ld_end_date
545 ,p_business_group_id => ln_business_group_id
546 ,p_tax_unit_id => ln_tax_unit_id
547 ,p_person_id => ln_person_id
548 ,p_asg_set => ln_asg_set);
549
550 get_eoy_action_info(ld_end_date
551 ,ln_tax_unit_id
552 ,ln_eoy_pactid);
553
554 /* PERSON ID IS NOT NULL */
555
556 if ln_person_id is not null then
557
558 action_creation(p_start_person_id);
559
560 elsif ln_asg_set is not null then
561
562 hr_utility.trace('Entered Asg Set logic');
563 hr_utility.trace('Asg Set ='||to_char(ln_asg_set));
564
565 ln_person_id_sel := 0;
566
567 hr_utility.trace('p_start_person_id ='||to_char(p_start_person_id));
568 hr_utility.trace('End Person ='||to_char(p_end_person_id));
569 hr_utility.trace('Asg Set ='||to_char(ln_asg_set));
570 hr_utility.trace('EOY Pactid ='||to_char(ln_eoy_pactid));
571
572 open c_selected_asg_set (p_start_person_id
573 ,p_end_person_id
574 ,ln_asg_set
575 ,ln_eoy_pactid
576 ,ld_start_date) ;
577 hr_utility.trace('Opened cusor c_selected_asg_set');
578 loop
579 fetch c_selected_asg_set into ln_person_id_sel ;
580 if c_selected_asg_set%notfound then
581 hr_utility.trace('No Person found for reporting in this chunk');
582 exit;
583 end if;
584
585 hr_utility.trace('ln_person_id after c_selected_asg_set = '||to_char(ln_person_id));
586
587 action_creation(ln_person_id_sel);
588
589 end loop;
590
591 close c_selected_asg_set;
592
593 end if; /* ln_person_id or lv_ssn is not null */
594
595 END w2c_action_creation;
596
597 FUNCTION get_parameter(name in varchar2,
598 parameter_list varchar2) return varchar2
599 is
600 start_ptr number;
601 end_ptr number;
602 token_val pay_payroll_actions.legislative_parameters%type;
603 par_value pay_payroll_actions.legislative_parameters%type;
604 begin
605
606 token_val := name||'=';
607
608 start_ptr := instr(parameter_list, token_val) + length(token_val);
609 end_ptr := instr(parameter_list, ' ',start_ptr);
610
611
612 /* if there is no spaces use then length of the string */
613 if end_ptr = 0 then
614 end_ptr := length(parameter_list)+1;
615 end if;
616
617 /* Did we find the token */
618 if instr(parameter_list, token_val) = 0 then
619 par_value := NULL;
620 else
621 par_value := substr(parameter_list, start_ptr, end_ptr - start_ptr);
622 end if;
623
624 return par_value;
625
626 end get_parameter;
627
628 --Begin
629 --hr_utility.trace_on(null,'W2C');
630
631
632 end pay_us_w2c_arch;
[Home] [Help]