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