[Home] [Help]
PACKAGE BODY: APPS.PAY_US_W2C_RPT
Source
1 PACKAGE BODY pay_us_w2c_rpt AS
2 /* $Header: pyusw2cr.pkb 120.3 2006/03/31 15:57:07 sodhingr noship $ */
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_rpt
21
22 Description : This procedure is used by Employee W-2C Report
23
24 Change List
25 -----------
26 Date Name Vers Bug No Description
27 ----------- ---------- ------ ------- --------------------------
28 10-AUG-2003 asasthan 115.0 Created.
29 10-AUG-2003 irgonzal 115.1 Modified get_payroll_action_info:
30 Removed to_number function for
31 PRINT parameter.
32
33 10-AUG-2003 asasthan 115.9 Added logic for Print 'ALL'.
34 19-FEB-2003 asasthan 115.10 changed get_prev curosors
35 added date effective join
36 25-OCT-2004 schauhan 115.11 3601799 Added selection criteria for "All"
37 if the report is Run with print
38 option "Reprint All W2c".
39 Made changes to w2crpt_range_cursor
40 and w2crpt_action_creation Cursor.
41 05-NOV-2004 schauhan 115.12 Added 'Distinct' to the Range Cursor
42 w2crpt_range_cursor.
43 22-NOV-2004 ahanda 115.13 3601799 Fixed issue in the bug. Changed the
44 action creation, range and sort
45 procedures.
46 16-DEC-2004 ahanda 115.14 4039440 Changed sort code to reduce length
47 to get around c-code issue.
48 14-MAR-2005 sackumar 115.15 4222032 Change in the Range Cursor removing redundant
49 use of bind Variable (:payroll_action_id)
50 04-AUG-2005 pragupta 115.16 3679317 Change in procedure sort_action. Removed
51 +0 from paf and hou for removing FTS and
52 performance improvement
53 14-MAR-2006 ppanda 115.17 4583575 To reduce the shared memory per_all_assignments_f
54 used instead of per_assignments_f.
55 31-MAR-2006 sodhingr 115.18 5111088 removed the comment from sort_cursor to fix signal
56 11 error.
57 *****************************************************************************/
58
59 gv_package VARCHAR2(100);
60 gv_procedure_name VARCHAR2(100);
61
62 /*****************************************************************************
63 Name : get_payroll_action_info
64 Purpose : This returns the Payroll Action level
65 information for W-2C PAPER.
66 Arguments : p_payroll_action_id - Payroll_Action_id of archiver
67 p_start_date - Start date of W-2C PAPER
68 p_end_date - End date of W-2C PAPER
69 p_business_group_id - Business Group ID
70 *****************************************************************************/
71 PROCEDURE get_payroll_action_info(p_payroll_action_id in number
72 ,p_end_date out nocopy date
73 ,p_start_date out nocopy date
74 ,p_business_group_id out nocopy number
75 ,p_tax_unit_id out nocopy number
76 ,p_person_id out nocopy number
77 ,p_asg_set out nocopy number
78 ,p_print out nocopy varchar2
79 ,p_sort_option1 out nocopy varchar2
80 ,p_sort_option2 out nocopy varchar2
81 ,p_sort_option3 out nocopy varchar2
82 ,p_session_date out nocopy date
83 )
84 IS
85 cursor c_payroll_Action_info (cp_payroll_action_id in number) is
86 select
87 pay_us_payroll_utils.get_parameter('TRANSFER_GRE',ppa.legislative_parameters),
88 pay_us_payroll_utils.get_parameter('PER_ID',ppa.legislative_parameters),
89 pay_us_payroll_utils.get_parameter('ASG_SET',ppa.legislative_parameters),
90 pay_us_payroll_utils.get_parameter('PRINT',ppa.legislative_parameters),
91 effective_date,
92 start_date,
93 business_group_id,
94 pay_us_payroll_utils.get_parameter('S1',ppa.legislative_parameters),
95 pay_us_payroll_utils.get_parameter('S2',ppa.legislative_parameters),
96 pay_us_payroll_utils.get_parameter('S3',ppa.legislative_parameters),
97 to_date(pay_us_payroll_utils.get_parameter('EFFECTIVE_DATE',
98 ppa.legislative_parameters)
99 ,'dd-mon-yyyy')
100 from pay_payroll_actions ppa
101 where ppa.payroll_action_id = cp_payroll_action_id;
102
103 ld_end_date DATE;
104 ld_start_date DATE;
105 ln_business_group_id NUMBER;
106 ln_tax_unit_id NUMBER;
107 ln_person_id NUMBER;
108 ln_asg_set NUMBER;
109 lv_print VARCHAR2(60);
110 lv_sort1 VARCHAR2(60);
111 lv_sort2 VARCHAR2(60);
112 lv_sort3 VARCHAR2(60);
113 ld_session_date DATE;
114
115 BEGIN
116 hr_utility.trace('Entered get_payroll_action_info');
117 ln_tax_unit_id := 0;
118 ln_person_id := 0;
119 ln_asg_set := 0;
120 open c_payroll_action_info(p_payroll_action_id);
121 fetch c_payroll_action_info into ln_tax_unit_id,
122 ln_person_id,
123 ln_asg_set,
124 lv_print,
125 ld_end_date,
126 ld_start_date,
127 ln_business_group_id,
128 lv_sort1,
129 lv_sort2,
130 lv_sort3,
131 ld_session_date;
132 close c_payroll_action_info;
133
134 hr_utility.trace('ld_end_date = ' || to_char(ld_end_date));
135 hr_utility.trace('ld_start_date = ' || to_char(ld_start_date));
136 hr_utility.trace('ln_tax_unit_id = '|| to_char(ln_tax_unit_id));
137 hr_utility.trace('ln_person_id = ' || to_char(ln_person_id));
138 hr_utility.trace('ln_asg_set = ' || to_char(ln_asg_set));
139
140 p_end_date := ld_end_date;
141 p_start_date := ld_start_date;
142 p_business_group_id := ln_business_group_id;
143 p_tax_unit_id := ln_tax_unit_id;
144 p_person_id := ln_person_id;
145 p_asg_set := ln_asg_set;
146 p_print := lv_print;
147 p_sort_option1 := lv_sort1;
148 p_sort_option2 := lv_sort2;
149 p_sort_option3 := lv_sort3;
150 p_session_date := ld_session_date;
151
152 hr_utility.trace('Leaving get_payroll_action_info');
153
154 EXCEPTION
155 when others then
156 hr_utility.trace('Error in ' || gv_procedure_name ||
157 to_char(sqlcode) || '-' || sqlerrm);
158 raise hr_utility.hr_error;
159
160 END get_payroll_action_info;
161
162
163 /******************************************************************
164 Name : w2crpt_range_cursor
165 Purpose : This returns the select statement that is
166 used to created the range rows for the
167 W-2C PAPER.
168 Arguments :
169 Notes : Calls procedure - get_payroll_action_info
170 ******************************************************************/
171 PROCEDURE w2crpt_range_cursor(
172 p_payroll_action_id in number
173 ,p_sqlstr out nocopy varchar2)
174 IS
175 ld_end_date DATE;
176 ld_start_date DATE;
177 ln_business_group_id NUMBER;
178 ln_tax_unit_id NUMBER;
179 ln_person_id NUMBER;
180 ln_asg_set NUMBER;
181 lv_sort1 VARCHAR2(60);
182 lv_sort2 VARCHAR2(60);
183 lv_sort3 VARCHAR2(60);
184 ld_session_date DATE;
185
186 lv_sql_string VARCHAR2(32000);
187 ln_eoy_pactid number;
188 lv_print varchar2(10);
189 lv_error_mesg varchar2(100);
190 ln_agent_tax_unit_id pay_assignment_actions.tax_unit_id%type;
191 ln_year number;
192
193 BEGIN
194 hr_utility.trace('Entered w2crpt_range_cursor');
195 ln_person_id := 0;
196 ln_asg_set := 0;
197 lv_print := null;
198 hr_utility.trace('p_payroll_action_id = ' ||
199 to_char(p_payroll_action_id));
200
201 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
202 ,p_start_date => ld_start_date
203 ,p_end_date => ld_end_date
204 ,p_business_group_id => ln_business_group_id
205 ,p_tax_unit_id => ln_tax_unit_id
206 ,p_person_id => ln_person_id
207 ,p_asg_set => ln_asg_set
208 ,p_print => lv_print
209 ,p_sort_option1 => lv_sort1
210 ,p_sort_option2 => lv_sort2
211 ,p_sort_option3 => lv_sort3
212 ,p_session_date => ld_session_date);
213
214 -- Bug 3601799 - Added condition.
215 IF ln_person_id IS NOT NULL OR ln_asg_set IS NOT NULL THEN
216 ln_year := to_number(to_char(ld_end_date,'YYYY'));
217
218 hr_utility.trace('Checking for Preprocess Agent GRE setup');
219 hr_us_w2_rep.get_agent_tax_unit_id(ln_business_group_id
220 ,ln_year
221 ,ln_agent_tax_unit_id
222 ,lv_error_mesg ) ;
223
224 if lv_error_mesg is not null then
225 if substr(lv_error_mesg,1,45) is not null then
226 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
227 pay_core_utils.push_token('record_name',' ');
228 pay_core_utils.push_token('description',substr(lv_error_mesg,1,45));
229 end if;
230
231 if substr(lv_error_mesg,46,45) is not null then
232 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
233 pay_core_utils.push_token('record_name',' ');
234 pay_core_utils.push_token('description',substr(lv_error_mesg,46,45));
235 end if;
236
237 if substr(lv_error_mesg,91,45) is not null then
238 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
239 pay_core_utils.push_token('record_name',' ');
240 pay_core_utils.push_token('description',substr(lv_error_mesg,91,45));
241 end if;
242
243 if substr(lv_error_mesg,136,45) is not null then
244 pay_core_utils.push_message(801,'PAY_INVALID_ER_DATA','P');
245 pay_core_utils.push_token('record_name',' ');
246 pay_core_utils.push_token('description',substr(lv_error_mesg,136,45));
247 end if;
248
249 hr_utility.raise_error;
250
251 end if;
252
253
254 if ln_person_id is not null then
255
256 lv_sql_string :=
257 'select distinct asg.person_id person_id
258 from per_all_assignments_f asg
259 where person_id = ' || ln_person_id ||
260 ' and :p_payroll_action_id is not null ';
261
262 hr_utility.trace('Range for person_id not null');
263
264 elsif ln_asg_set is not null then
265
266 lv_sql_string :=
267 'select distinct paf.person_id
268 from hr_assignment_set_amendments asgset,
269 per_all_assignments_f paf
270 where assignment_set_id = ' || ln_asg_set || '
271 and asgset.assignment_id = paf.assignment_id
272 and asgset.include_or_exclude = ''I''
273 and :payroll_action_id is not null ';
274
275 hr_utility.trace('Range for asg_set not null');
276 end if;
277
278 -- Bug 3601799
279 -- This query string will be executed when for All parameter is passed.
280 ELSE
281 lv_sql_string :=
282 'select distinct paa.serial_number
283 from pay_assignment_actions paa,
284 pay_payroll_actions ppa
285 where ppa.report_type = ''W2C_PRE_PROCESS''
286 and ppa.effective_date = add_months(''' || ld_start_date || ''',12) -1
287 and ppa.business_group_id+0 = ' || ln_business_group_id ||'
288 and ltrim(rtrim(
289 substr(ppa.legislative_parameters,
290 instr(ppa.legislative_parameters,''TRANSFER_GRE='')
291 + length(''TRANSFER_GRE='')
292 ,instr(ppa.legislative_parameters,'' '',2))))
293 = to_char(' || ln_tax_unit_id || ')
294 and paa.payroll_action_id = ppa.payroll_action_id
295 and paa.action_status = ''C''
296 and paa.tax_unit_id = ' || ln_tax_unit_id || '
297 and :payroll_action_id is not null
298 order by paa.serial_number';
299
300 hr_utility.trace('Range for all the persons.');
301 END IF;
302
303 p_sqlstr := lv_sql_string;
304 hr_utility.trace('p_sqlstr = ' ||p_sqlstr);
305 hr_utility.trace('Leaving w2crpt_range_cursor');
306 END w2crpt_range_cursor;
307
308
309 /************************************************************
310 Name : w2crpt_action_creation
311 Purpose : This creates the assignment actions for
312 a specific chunk of people to be archived
313 by the W2C Report process.
314 Arguments :
315 Notes : Calls procedure - get_payroll_action_info
316 ************************************************************/
317 PROCEDURE w2crpt_action_creation(
318 p_payroll_action_id in number
319 ,p_start_person_id in number
320 ,p_end_person_id in number
321 ,p_chunk in number)
322
323 IS
324
325 ln_assignment_id NUMBER;
326 ln_tax_unit_id NUMBER;
327 ld_effective_date DATE ;
328 ln_asg_action_id NUMBER;
329 ln_primary_assignment_id NUMBER;
330 ln_yepp_aaid NUMBER;
331 ln_payroll_action_id NUMBER;
332 ln_w2c_asg_action NUMBER;
333 lv_year VARCHAR2(4);
334
335 ld_end_date DATE;
336 ld_start_date DATE;
337 ln_business_group_id NUMBER;
338 ln_person_id NUMBER;
339 ln_set_person_id NUMBER;
343 lv_sort2 VARCHAR2(60);
340 ln_asg_set NUMBER;
341 lv_print varchar2(10);
342 lv_sort1 VARCHAR2(60);
344 lv_sort3 VARCHAR2(60);
345 ld_session_date DATE;
346
347 lv_report_type pay_payroll_actions.report_type%TYPE ;
348 ln_asg_act_to_lock pay_assignment_actions.assignment_action_id%TYPE;
349 ln_second_last_arch_action pay_assignment_actions.assignment_action_id%TYPE;
350 ln_prev_yepp_lock_action pay_assignment_actions.assignment_action_id%TYPE;
351
352
353 lv_serial_number VARCHAR2(30);
354 lv_employee_number per_all_people_f.employee_number%type;
355 lv_message varchar2(50);
356 lv_full_name per_all_people_f.full_name%type;
357 lv_record_name varchar2(50);
358 lv_prev_report_type pay_payroll_actions.report_type%TYPE;
359 ln_prev_lock_action pay_assignment_actions.assignment_action_id%TYPE;
360 ln_prev_w2c_action_id pay_assignment_actions.assignment_action_id%TYPE;
361 ln_serial_number pay_assignment_actions.serial_number%TYPE;
362
363 CURSOR c_selected_asg_set(cp_start_person in number
364 ,cp_end_person in number
365 ,cp_asg_set in number) is
366 select distinct paf.person_id
367 from hr_assignment_set_amendments asgset,
368 per_all_assignments_f paf
369 where assignment_set_id = cp_asg_set
370 and asgset.include_or_exclude = 'I'
371 and paf.assignment_id = asgset.assignment_id
372 and paf.person_id between cp_start_person
373 and cp_end_person;
374
375 -- Bug 3601799
376 -- This Cursor is opened when report is run for All persons.
377 -- This will only happen for Re-prints
378 CURSOR c_select_all_person(cp_start_person in number,
379 cp_end_person in number,
380 cp_start_date in date,
381 cp_business_group_id in number,
382 cp_tax_unit_id in number) IS
383 select distinct paf.person_id
384 from pay_assignment_actions paa,
385 pay_payroll_actions ppa,
386 per_all_assignments_f paf
387 where ppa.report_type = 'W-2C PAPER'
388 and ppa.report_category = 'REPORT'
389 and ppa.report_qualifier = 'DEFAULT'
390 and ppa.effective_date = add_months(cp_start_date,12) -1
391 and ppa.business_group_id = cp_business_group_id
392 and ppa.legislative_parameters like '%' || cp_tax_unit_id || '%'
393 and paa.payroll_action_id = ppa.payroll_action_id
394 and paa.action_status = 'C'
395 and paa.tax_unit_id = cp_tax_unit_id
396 and paf.assignment_id = paa.assignment_id
397 and paf.effective_end_date =
398 (SELECT max(paf1.effective_end_date)
399 FROM per_all_assignments_f paf1
400 WHERE paf1.assignment_id = paf.assignment_id
401 AND paf1.effective_start_date <= ppa.effective_date)
402 and paf.person_id between cp_start_person and cp_end_person;
403
404
405 PROCEDURE action_creation (p_person_id in NUMBER)
406 IS
407
408 CURSOR get_prev_w2c_dtls (cp_person_id in number
409 ,cp_tax_unit_id in number
410 ,cp_effective_date in date
411 ,cp_start_date in date) is
412 select ppa.report_type, paa.assignment_id,
413 paa.assignment_action_id
414 from pay_payroll_actions ppa,
415 pay_assignment_actions paa,
416 per_all_assignments_f paf
417 where paa.assignment_id = paf.assignment_id
418 and paf.person_id = cp_person_id
419 and paf.effective_start_date <= cp_effective_date
420 and paf.effective_end_date >= cp_start_date
421 and paa.tax_unit_id = cp_tax_unit_id
422 and paa.action_status = 'C'
423 and ppa.payroll_action_id = paa.payroll_action_id
424 and ppa.effective_date = cp_effective_date
425 and ppa.report_type in ('W2C_PRE_PROCESS','W-2C PAPER')
426 and paf.effective_end_date =
427 (SELECT max(paf1.effective_end_date)
428 FROM per_all_assignments_f paf1
429 WHERE paf1.assignment_id = paf.assignment_id
430 AND paf1.effective_start_date <= ppa.effective_date)
431 order by paa.assignment_action_id desc;
432
433 CURSOR get_prev_w2c_reprint_dtls (cp_person_id in number
434 ,cp_tax_unit_id in number
435 ,cp_effective_date in date
436 ,cp_start_date in date
437 ,cp_business_group_id in number) is
438 select ppa.report_type, paa.assignment_id,
439 paa.assignment_action_id
440 from pay_payroll_actions ppa,
441 pay_assignment_actions paa,
442 per_all_assignments_f paf
443 where paa.assignment_id = paf.assignment_id
444 and paf.person_id = cp_person_id
445 and paf.effective_start_date <= cp_effective_date
446 and paf.effective_end_date >= cp_start_date
447 and paa.tax_unit_id = cp_tax_unit_id
451 and ppa.report_type = 'W2C_PRE_PROCESS'
448 and paa.action_status = 'C'
449 and ppa.payroll_action_id = paa.payroll_action_id
450 and ppa.effective_date = cp_effective_date
452 and ppa.report_category = 'RT'
453 and ppa.report_qualifier = 'FED'
454 and ppa.business_group_id = cp_business_group_id
455 and paf.effective_end_date =
456 (SELECT max(paf1.effective_end_date)
457 FROM per_all_assignments_f paf1
458 WHERE paf1.assignment_id = paf.assignment_id
459 AND paf1.effective_start_date <= ppa.effective_date)
460 and exists (select 1
461 from pay_action_interlocks pai,
462 pay_assignment_actions paa_paper,
463 pay_payroll_Actions ppa_paper
464 where pai.locked_action_id = paa.assignment_action_id
465 and paa_paper.assignment_Action_id = pai.locking_action_id
466 and ppa_paper.payroll_Action_id = paa_paper.payroll_Action_id
467 and ppa_paper.report_type = 'W-2C PAPER'
468 and ppa_paper.report_category = 'REPORT'
469 and ppa_paper.report_qualifier = 'DEFAULT'
470 and ppa_paper.effective_date = cp_effective_date
471 and ppa_paper.business_group_id = cp_business_group_id)
472 order by paa.assignment_action_id desc;
473
474 CURSOR get_interlocked_action(cp_w2cpp_action in number)is
475 select ppa.report_type,
476 paa.assignment_action_id,
477 substr(paa.serial_number, 1,15) prev_action_id
478 from pay_payroll_actions ppa,
479 pay_assignment_actions paa,
480 pay_action_interlocks pai
481 where pai.locking_action_id = cp_w2cpp_action
482 and paa.assignment_action_id = pai.locked_action_id
483 and ppa.payroll_action_id = paa.payroll_action_id;
484
485
486 CURSOR get_warning_dtls_for_ee(cp_person_id in number) is
487 select substr(full_name,1,48), employee_number
488 from per_all_people_f
489 where person_id = cp_person_id
490 order by effective_end_date desc;
491
492 BEGIN
493 if lv_print = 'NEW' then
494 open get_prev_w2c_dtls(p_person_id
495 ,ln_tax_unit_id
496 ,ld_end_date
497 ,ld_start_date);
498 fetch get_prev_w2c_dtls into lv_report_type
499 ,ln_primary_assignment_id
500 ,ln_asg_act_to_lock;
501 if get_prev_w2c_dtls%notfound then
502 open get_warning_dtls_for_ee(p_person_id);
503 fetch get_warning_dtls_for_ee into lv_full_name
504 ,lv_employee_number;
505 close get_warning_dtls_for_ee;
506
507 lv_record_name := 'W2C Report';
508 lv_message := 'No W2c archive actions exist for this employee';
509
510 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','P');
511 pay_core_utils.push_token('record_name',lv_record_name);
512 pay_core_utils.push_token('name_or_number',lv_full_name);
513 pay_core_utils.push_token('description',lv_message);
514 end if;
515
516 if get_prev_w2c_dtls%found then
517 if lv_report_type in ('W2C_PRE_PROCESS') then
518 /* Create an assignment action for this person */
519 select pay_assignment_actions_s.nextval
520 into ln_w2c_asg_action
521 from dual;
522 hr_utility.trace('New w2c Action = ' || ln_w2c_asg_action);
523
524 /* Insert into pay_assignment_actions. */
525 hr_nonrun_asact.insact(ln_w2c_asg_action
526 ,ln_primary_assignment_id
527 ,p_payroll_action_id
528 ,p_chunk
529 ,ln_tax_unit_id);
530
531 /**********************************************************
532 ** Get the second last archive action for this employee
533 ** The First W2C_PRE_PROCESS locks YREND
534 ** but the subsequent W2C_PRE_PROCESS will lock
535 ** the W-2C PAPER process
536 ***********************************************************/
537 open get_interlocked_action(ln_asg_act_to_lock);
538 fetch get_interlocked_action into lv_prev_report_type
539 ,ln_prev_yepp_lock_action
540 ,ln_prev_w2c_action_id;
541 if get_interlocked_action%notfound then
542 close get_interlocked_action;
543 hr_utility.raise_error;
544 end if;
545 close get_interlocked_action;
546
547 if lv_prev_report_type = 'YREND' then
548 ln_second_last_arch_action := ln_prev_yepp_lock_action;
549 elsif lv_prev_report_type = 'W-2C PAPER' then
550 ln_second_last_arch_action := ln_prev_w2c_action_id;
551 end if;
552
556 ***************************************************************/
553 /***************************************************************
554 ** Update the serial number column with the assignment action
555 ** of the last two archive processes
557 ln_serial_number := lpad(ln_asg_act_to_lock,15,0)||
558 lpad(ln_second_last_arch_action,15,0);
559
560 update pay_assignment_actions aa
561 set aa.serial_number = ln_serial_number
562 where aa.assignment_action_id = ln_w2c_asg_action;
563
564 /***************************************************************
565 ** Interlock last w2c archive action with current w2c rep action
566 ***************************************************************/
567 hr_utility.trace('Locking Action'||ln_w2c_asg_action);
568 hr_utility.trace('Locked Action' || ln_asg_act_to_lock);
569 hr_nonrun_asact.insint(ln_w2c_asg_action
570 ,ln_asg_act_to_lock);
571
572 elsif lv_report_type = 'W-2C PAPER' then
573
574 open get_warning_dtls_for_ee(p_person_id);
575 fetch get_warning_dtls_for_ee into lv_full_name
576 ,lv_employee_number;
577
578 close get_warning_dtls_for_ee;
579
580 lv_record_name := 'W2C Report';
581 lv_message := 'No new w2c archive actions exist '||
582 'for this employee';
583
584 pay_core_utils.push_message(801,'PAY_INVALID_EE_FORMAT','P');
585 pay_core_utils.push_token('record_name',lv_record_name);
586 pay_core_utils.push_token('name_or_number',lv_full_name);
587 pay_core_utils.push_token('description',lv_message);
588
589 end if; /* report type */
590
591 end if; /* employee found*/
592 close get_prev_w2c_dtls;
593
594 elsif lv_print = 'ALL' then
595
596 open get_prev_w2c_reprint_dtls(p_person_id
597 ,ln_tax_unit_id
598 ,ld_end_date
599 ,ld_start_date
600 ,ln_business_group_id);
601 lv_report_type := null;
602 ln_primary_assignment_id := 0;
603 ln_asg_act_to_lock := 0;
604
605 loop
606 fetch get_prev_w2c_reprint_dtls into lv_report_type
607 ,ln_primary_assignment_id
608 ,ln_asg_act_to_lock;
609 if get_prev_w2c_reprint_dtls%notfound then
610 exit;
611 end if;
612
613 /* Create an assignment action for this person */
614 select pay_assignment_actions_s.nextval
615 into ln_w2c_asg_action
616 from dual;
617 hr_utility.trace('New w2c Action = ' || ln_w2c_asg_action);
618
619 /* Insert into pay_assignment_actions. */
620 hr_nonrun_asact.insact(ln_w2c_asg_action
621 ,ln_primary_assignment_id
622 ,p_payroll_action_id
623 ,p_chunk
624 ,ln_tax_unit_id);
625
626 /**********************************************************
627 ** Get the second last archive action for this employee
628 ** The First W2C_PRE_PROCESS locks YREND
629 ** but the subsequent W2C_PRE_PROCESS will lock
630 ** the W-2C PAPER process
631 ***********************************************************/
632 open get_interlocked_action(ln_asg_act_to_lock);
633 fetch get_interlocked_action into lv_prev_report_type
634 ,ln_prev_yepp_lock_action
635 ,ln_prev_w2c_action_id;
636 if get_interlocked_action%notfound then
637 close get_interlocked_action;
638 hr_utility.raise_error;
639 end if;
640 close get_interlocked_action;
641
642 if lv_prev_report_type = 'YREND' then
643 ln_second_last_arch_action := ln_prev_yepp_lock_action;
644 elsif lv_prev_report_type = 'W-2C PAPER' then
645 ln_second_last_arch_action := ln_prev_w2c_action_id;
646 end if;
647
648 /***************************************************************
649 ** Update the serial number column with the assignment action
650 ** of the last two archive processes
651 ***************************************************************/
652 ln_serial_number := lpad(ln_asg_act_to_lock,15,0)||
653 lpad(ln_second_last_arch_action,15,0);
654
655 update pay_assignment_actions aa
656 set aa.serial_number = ln_serial_number
657 where aa.assignment_action_id = ln_w2c_asg_action;
658
659 end loop;
660 close get_prev_w2c_reprint_dtls;
661
662 end if; /* NEW */
663
664 END action_creation;
665
666 BEGIN
670 ln_asg_action_id := 0;
667 hr_utility.trace('Entered action_creation ');
668 ln_assignment_id := 0;
669 ln_tax_unit_id := 0;
671 ln_primary_assignment_id := 0;
672 ln_yepp_aaid := 0;
673 ln_payroll_action_id := 0;
674 ln_w2c_asg_action := 0;
675 lv_year := 0;
676
677 ln_person_id := 0 ;
678 ln_set_person_id := 0 ;
679 ln_asg_set := 0 ;
680 lv_message := null;
681
685 hr_utility.trace('p_chunk = ' || to_char(p_chunk));
682 hr_utility.trace('p_payroll_action_id = '|| to_char(p_payroll_action_id));
683 hr_utility.trace('p_start_person_id ='|| to_char(p_start_person_id));
684 hr_utility.trace('p_end_person_id = '|| to_char(p_end_person_id));
686
687 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
688 ,p_start_date => ld_start_date
689 ,p_end_date => ld_end_date
690 ,p_business_group_id => ln_business_group_id
691 ,p_tax_unit_id => ln_tax_unit_id
692 ,p_person_id => ln_person_id
693 ,p_asg_set => ln_asg_set
694 ,p_print => lv_print
695 ,p_sort_option1 => lv_sort1
696 ,p_sort_option2 => lv_sort2
697 ,p_sort_option3 => lv_sort3
698 ,p_session_date => ld_session_date);
699
700 /* PERSON ID IS NOT NULL */
701 if ln_person_id is not null then
702 action_creation(p_start_person_id);
703
704 /* ASSIGNMENT SET ID IS NOT NULL */
705 elsif ln_asg_set is not null then
706 hr_utility.trace('Entered Asg Set logic');
707 hr_utility.trace('Asg Set ='||to_char(ln_asg_set));
708 hr_utility.trace('p_start_person_id ='||to_char(p_start_person_id));
709 hr_utility.trace('End Person ='||to_char(p_end_person_id));
710
711 open c_selected_asg_set(p_start_person_id
712 ,p_end_person_id
713 ,ln_asg_set);
714 hr_utility.trace('Opened cusor c_selected_asg_set');
715 loop
716 fetch c_selected_asg_set into ln_set_person_id;
717 if c_selected_asg_set%notfound then
718 hr_utility.trace('No Person found for reporting in this chunk');
719 exit;
720 end if;
721
722 action_creation(ln_set_person_id);
723
724 end loop;
725 close c_selected_asg_set;
726
727 -- Bug 3601799 -- Added this elsif if the report is run for All.
728 /* PERSON ID and ASSIGNMENT SET ID are NULL */
729 elsif ln_person_id is null and ln_asg_set is null then
730 hr_utility.trace('Report run for All persons Logic.');
731 open c_select_all_person(p_start_person_id
732 ,p_end_person_id
733 ,ld_start_date
734 ,ln_business_group_id
735 ,ln_tax_unit_id);
736 hr_utility.trace('Opened cusor c_select_all_person');
737 loop
738 fetch c_select_all_person into ln_person_id;
739 if c_select_all_person%notfound then
740 hr_utility.trace('No Person found for reporting in this chunk.');
741 exit;
742 end if;
743
744 action_creation(ln_person_id);
745 end loop;
746 close c_select_all_person;
747 end if; /* ln_person_id */
748
749 END w2crpt_action_creation;
750
751
752 /************************************************************
753 Name : sort_action
754 Purpose : This sorts the assignment actions based on the
755 sort options given when submitting the W2C Report
756 Arguments :
757 Notes : Calls procedure - get_payroll_action_info
758 ************************************************************/
759 PROCEDURE sort_action(p_payroll_action_id in varchar2
760 ,p_sql_string in out nocopy varchar2
761 ,p_sql_length out nocopy number)
762 IS
763
764 ld_end_date DATE;
765 ld_start_date DATE;
766 ln_business_group_id NUMBER;
767 ln_person_id NUMBER;
768 ln_set_person_id NUMBER;
769 ln_asg_set NUMBER;
770 ln_tax_unit_id NUMBER;
771 lv_print VARCHAR2(10);
772 lv_sort1 VARCHAR2(60);
773 lv_sort2 VARCHAR2(60);
774 lv_sort3 VARCHAR2(60);
775 ld_session_date DATE;
776
777 BEGIN
778
779 get_payroll_action_info(p_payroll_action_id => p_payroll_action_id
780 ,p_start_date => ld_start_date
781 ,p_end_date => ld_end_date
782 ,p_business_group_id => ln_business_group_id
783 ,p_tax_unit_id => ln_tax_unit_id
784 ,p_person_id => ln_person_id
785 ,p_asg_set => ln_asg_set
786 ,p_print => lv_print
787 ,p_sort_option1 => lv_sort1
788 ,p_sort_option2 => lv_sort2
789 ,p_sort_option3 => lv_sort3
790 ,p_session_date => ld_session_date
791 );
792
793 if ld_end_date > ld_session_date then
794 ld_session_date := ld_end_date;
795 end if;
796
797 hr_utility.trace('Beginning of the sort_action cursor');
798 p_sql_string :=
799 'select mt.rowid
800 from hr_organization_units hou, hr_locations_all hl,
801 per_periods_of_service pps, per_all_assignments_f paf,
802 pay_assignment_actions mt
803 where mt.payroll_action_id = :p_payroll_action_id
804 and paf.assignment_id = mt.assignment_id -- Bug 3679317 ( +0 removed)
805 and paf.effective_start_date = (select max(paf2.effective_start_date)
809 and paf.assignment_type = ''E'' and hou.organization_id = paf.organization_id
806 from per_all_assignments_f paf2 where paf2.assignment_id = paf.assignment_id
807 and paf2.effective_start_date <= to_date(''' || to_char(ld_end_date,'dd-mon-yyyy') || ''',''dd-mon-yyyy''))
808 and paf.effective_end_date >= to_date('''|| to_char(ld_start_date,'dd-mon-yyyy') ||''',''dd-mon-yyyy'')
810 and pps.period_of_service_id = paf.period_of_service_id
811 and pps.person_id = paf.person_id and hl.location_id = paf.location_id
812 and hou.business_group_id = '''|| ln_business_group_id ||'''
813 order by decode('''||lv_sort1||''', ''Employee_Name'',
814 hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_LAST_NAME'')||'' ''
815 ||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_FIRST_NAME'')||'' ''
816 ||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1),
817 ''SSN'',nvl(hr_us_w2_rep.get_per_item( to_number(substr(mt.serial_number,1,15)), ''A_PER_NATIONAL_IDENTIFIER''),
818 ''Applied For''),
819 ''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'')),
820 ''Organization'',hou.name, ''Location'',hl.location_code,
821 ''Termination_Reason'',decode(leaving_reason,null,''ZZ'',hr_us_w2_rep.get_leav_reason(leaving_reason)),
822 hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_LAST_NAME'')||'' ''
823 ||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_FIRST_NAME'')||'' ''
824 ||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1)),
825 decode('''||lv_sort2||''', ''Employee_Name'',
826 hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_LAST_NAME'')||'' ''
827 ||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,5)), ''A_PER_FIRST_NAME'')||'' ''
828 ||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1),
829 ''SSN'',nvl(hr_us_w2_rep.get_per_item(
830 to_number(substr(mt.serial_number,1,15)), ''A_PER_NATIONAL_IDENTIFIER''), ''Applied For''),
831 ''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'')),
832 ''Organization'',hou.name, ''Location'',hl.location_code,
833 ''Termination_Reason'',decode(leaving_reason,null,''ZZ'',hr_us_w2_rep.get_leav_reason(leaving_reason)),
834 hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_LAST_NAME'')||'' ''
835 ||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_FIRST_NAME'')||'' ''
836 ||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1)),
837 decode('''||lv_sort3||''', ''Employee_Name'', hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)),
838 ''A_PER_LAST_NAME'')||'' ''
839 ||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,5)), ''A_PER_FIRST_NAME'')||'' ''
840 ||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_MIDDLE_NAMES''),1,1),
841 ''SSN'',nvl(hr_us_w2_rep.get_per_item( to_number(substr(mt.serial_number,1,5)), ''A_PER_NATIONAL_IDENTIFIER''),
842 ''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'')),
843 ''Organization'',hou.name, ''Location'',hl.location_code, ''Termination_Reason'',decode(leaving_reason,null,''ZZ'',
844 hr_us_w2_rep.get_leav_reason(leaving_reason)),
845 hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_LAST_NAME'')||'' ''
846 ||hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,15)), ''A_PER_FIRST_NAME'')||'' ''
847 ||substr(hr_us_w2_rep.get_per_item(to_number(substr(mt.serial_number,1,5)), ''A_PER_MIDDLE_NAMES''),1,1))';
848
849
850 p_sql_length := length(p_sql_string); -- return the length of the string.
851 hr_utility.trace('End of the sort_Action cursor');
852 END sort_action;
853
854 BEGIN
855 -- hr_utility.trace_on(null,'W2CRPT');
856 gv_package := 'pay_us_w2c_rpt';
857
858 END pay_us_w2c_rpt;