[Home] [Help]
PACKAGE BODY: APPS.PAY_CA_ARCHIVE
Source
1 package body pay_ca_archive as
2 /* $Header: pycaarch.pkb 120.8.12010000.2 2009/02/04 12:18:04 aneghosh ship $ */
3 -- /************************************************************************
4 --
5 -- Description : Package and procedure to build sql for payroll processes.
6 --
7 -- Change List
8 -- -----------
9 -- Date Name Vers Bug No Description
10 -- ----------- ---------- ----- ------- -----------------------------
11 -- 05-FEB-2009 aneghosh 115.66 8210261 Modified l_first_day_worked to
12 -- show correct hire date for a
13 -- re-hired employee. Also took
14 -- care of the fix for Bug 6396412
15 -- 31-OCT-2007 sapalani 115.65 6396412 When previous ROE exists without
16 -- LOA, the next working day after
17 -- previous ROE date is made as
18 -- first day worked for current ROE.
19 -- 16-MAR-2006 pganguly 115.64 4361007 The Box 17A will be calculated
20 -- based upon the 'ROE Vacation
21 -- Pay' balance rather than
22 -- 'Vacation Paid'.
23 -- 06-MAR-2006 pganguly 115.63 5013548 Corrected the Box 17C Balance
24 -- name to 'ROE Other Monies
25 -- Sick Leave Credit'.
26 -- 09-NOV-2005 pganguly 115.62 Added -- in the comments
27 -- section.
28 -- 07-NOV-2005 pganguly 115.61 4481028 changed cursor cur_paf/cur_gre
29 -- to use date_earned rather than
30 -- effective_date.
31 -- 30-Apr-2005 ssmukher 115.60 4510534
32 -- 17-Aug-2005 ssmukher 115.59 4510534 Modified the procedure
33 -- archive_data to include the
34 -- logic for the additional new
35 -- ROE Insurable Earning amounts
36 -- ( 28 to 53 )
37 -- 08-Aug-2005 ssmukher 115.58 4510534
38 -- 16-FEB-2005 rigarg 115.57 3919951 Modified procedure get date as
39 -- ROE Date should be LOA/Term Date
40 -- 16-FEB-2005 rigarg 115.56 3919951 Added condition in action
41 -- creation code to check if
42 -- LOA/Term exists for "ROE by
43 -- Assignment Set"
44 -- 16-FEB-2005 rigarg 115.55 3919951 Modified procedure get date to
45 -- handle the LOA and Term date
46 -- effective from next day of ROE
47 -- Date.
48 -- 06-DEC-2004 rigarg 115.54 4030558 Modified derivation of
49 -- l_start_date when asg set is
50 -- passed.
51 -- 14-OCT-2004 rigarg 115.53 3931182 Modified cursor cur_paf.
52 -- 08-OCT-2004 rigarg 115.52 3930642 Corrected Get working date call.
53 -- 07-OCT-2004 rigarg 115.51 3930642 modified call to procedure
54 -- get_ei_amount_totals
55 -- to pass first day worked and
56 -- last pay date for 15A and 15B
57 -- similar to 15C.
58 -- 07-OCT-2004 rigarg 115.50 3930642 Get Last working Date was called
59 -- being called twice in deriving
60 -- last pay date. This has been
61 -- corrected now.
62 -- 04-OCT-2004 rigarg 115.49 3923867 Changed to logic to derive 15C
63 -- amounts using first day worked
64 -- and last pay date.
65 -- 01-OCT-2004 rigarg 115.48 3923912 Removed GSCC Failure.
66 -- 01-OCT-2004 rigarg 115.47 3923912 Removed Projected End date from
67 -- the cursors.
68 -- 30-SEP-2004 rigarg 115.46 3919951 Modified cursor cur_absence
69 -- to fetch records before effective
70 -- date.
71 -- 28-SEP-2004 rigarg 115.45 3898976 Modified cursor cur_paf which
72 -- will now fetch based on previous
73 -- ROE date.
74 -- 24-SEP-2004 rigarg 115.44 3892425 Added new cursor cur_abs to find
75 -- the first working day depending
76 -- previous ROE. And corrected
77 -- derivation of previous ROE Date
78 -- in the get_balance_start_date.
79 -- 03-SEP-2004 pganguly 115.43 3824732 Fixed Bug# 3824732. Changed the
80 -- cursor cur_paf so that it uses
81 -- l_effective_date to create the
82 -- assignment actions rather than
83 -- employee's hire date.
84 -- 12-APR-2004 pganguly 115.41 3556997 Added action_type 'B' in
85 -- cur_latest_aaid.
86 -- 09-MAR-2004 pganguly 115.39 Change the cursor cur_gre to
87 -- include action_type 'I'. Also
88 -- added one parameter p_bg_id to
89 -- populate_element_table proc.
90 -- 20-JAN-2004 pganguly 115.38 3353849 Changed the sql which was
91 -- flagged in the Perf Repository.
92 -- Changed the cursor cur_retry
93 -- added pay_payroll_action,pay
94 -- assignment_actions.
95 -- 20-JAN-2004 pganguly 115.37 3353849 Changed the sql which was
96 -- flagged in the Perf Repository.
97 -- Changed the cursor cur_edor
98 -- added pay_payroll_action,pay
99 -- assignment_actions.
100 -- 16-JAN-2003 pganguly 115.36 3378568 Fixed Bug# 3378568. Removed the
101 -- 400 days check from the cursor
102 -- cur_max_date_start of function
103 -- last_period_of_service.
104 -- 22-OCT-2003 ssouresr 115.35 The function
105 -- last_period_of_service should be
106 -- called separately when the
107 -- assignment set is not null.
108 -- assignments belonging
109 -- to the assignment set were
110 -- not being archived
111 -- 03-OCT-2003 ssouresr 115.34 The archiver is modified so that
112 -- ROE is also generated for an
113 -- assignmentthat only has T4A
114 -- earnings. If both T4 and T4A
115 -- earnings exist the archiver
116 -- works as before.
117 -- 27-SEP-2003 ssouresr 115.33 The archiver is modified so that
118 -- the latest assignment is archived
119 -- for employees that have been
120 -- rehired. Also the latest
121 -- termination date is used as the
122 -- roe effective date.
123 -- 19-JUN-2003 pganguly 115.32 Changed the cursor cur_17_gres
124 -- so that it looks for T4A/RL2
125 -- GRES as well.
126 -- 06-JUN-2003 pganguly 115.31 Changed the functionality so
127 -- that ROE will be created for
128 -- employee's in T4 GREs only.
129 -- Box 17A/C earnings reported in
130 -- T4/T4A GREs for the final pay
131 -- period and the pay period after
132 -- that will be reported in the
133 -- first T4 GRE only.
134 -- 08-MAY-2003 pganguly 115.30 2923942 Changed the function get_dates.
135 -- This now returns a flag 'Y' for
136 -- terminated/loa employees. Added
137 -- a new parameter in the func:
138 -- pay_ca_roe_ei_pkg.
139 -- get_ei_amount_totals
140 -- 25-APR-2003 pganguly 115.29 For Box 17A/C changed the cond
141 -- intion from <> 0 to = 0 to
142 -- retrieve the latest asg action.
143 -- 24-MAR-2003 pganguly 115.27 Initialized l_value with 0
144 -- for each 17C Balances.
145 -- 12-MAR-2003 pganguly 115.26 2842174 In the archive_data proc, added
146 -- l_value := 0 before archiving
147 -- Box 17 Balances.
148 -- 05-MAR-2003 pganguly 115.24 Removed the to_char call from
149 -- the hr_utility.trace msg for
150 -- Box 17A l_value.
151 -- 03-MAR-2003 pganguly 115.23 2685760 Added the functionality to
152 -- archive Box 17A/C balances.
153 -- 31-DEC-2002 pganguly 115.22 2732112 Changed the cursor
154 -- cur_asg_set_person_id, added
155 -- a date join while joining
156 -- per_assignments_f. Added no
157 -- copy for GSCC.
158 -- 06-NOV-2002 ssouresr 115.21 Populated tables in archinit to
159 -- improve performance.
160 -- 04-NOV-2002 pganguly 115.20 Fixed 2375610. Changed cursor
161 -- cur_payroll_form so that it
162 -- returns roe_issuer/correspon
163 -- dence_language in the correct
164 -- sequence.
165 -- 20-MAY-2002 pganguly 115.19 Fixed 2325826.
166 -- 07-MAY-2002 pganguly 115.18 Fixed 2325826, 2322306.
167 -- 12-APR-2002 pganguly 115.17 Fixed bug# 2316949, 2311893,
168 -- 2300361, 2296898, 2260309
169 -- 05-APR-2002 pganguly 115.16 Commented out hr_utiity.raise
170 -- error in the cur_retry%NOTFound
171 -- 04-APR-2002 pganguly 115.15 Fixed bug# 2296898, 2294049,
172 -- 2046740.
173 -- 02-APR-2001 pganguly 115.11 Changed the message numbers
174 -- from 78035,78036 to 74023,74024
175 -- 20-MAR-2001 pganguly 115.10 When calling the
176 -- get_ei_amount_totals function
177 -- added 1 to previuos ROE date
178 -- so that it becomes the start
179 -- date of the next ROE.
180 -- 05-JAN-2000 pganguly 115.9 Commented hr_utility.trace_on
181 -- 27-DEC-2000 pganguly 115.8 Added a check while archiving
182 -- EI Earnings. If BOX15B is
183 -- returned then we archive 0
184 -- in all the places for Box
185 -- 15C.
186 -- 26-SEP-2000 pganguly 115.7 Uncommented the exit statement
187 -- and whenever sqlerror/oserror.
188 -- 24-MAY-2000 pganguly 115.5 Corrected the loop count in
189 -- cur_employee_info as
190 -- social_insurance_number
191 -- was archived twice.
192 -- 15-MAY-2000 pganguly 115.4 Corrected the Correspondence
193 -- Language Problem.
194 -- 15-MAY-2000 pganguly 115.3 Changed the message numbers.
195 -- 14-MAY-2000 pganguly 115.2 Added functionalities to
196 -- handle Retry and Amendment.
197 -- Also changed the ROE_PER_
198 -- NATIONAL_IDENTIFIER to
199 -- ROE_PER_SOCIAL_INSURANCE
200 -- NUMBER.
201 -- 19-APR-2000 pganguly 115.1 Fixed Multiple Assignment
202 -- , Employee Address doesn't
203 -- Exists Problem.
204 -- 14-MAR-2000 pganguly 115.0 Changes made for 11i.
205 -- 30-NOV-1999 jgoswami 110.6 Added ROE_TAX_UNIT_CITY
206 -- 30-NOV-1999 jgoswami 110.5 Added ROE_PER_CITY
207 -- 29-NOV-1999 jgoswami 110.4 Changed get_date function added
208 -- parameter p_recall_date.
209 -- Added org_information9 instead
210 -- of tax unit name only.
211 -- Currently
212 -- ROE_PER_TELEPHONE_NUMBER -- value is NULL
213 -- 29-NOV-1999 jgoswami 110.3 change date format to
214 -- DD-MON-YYYY in
215 -- ROE_EXPECTED_DATE_OF_RECALL
216 -- 23-NOV-1999 jgoswami 110.2 Added code for
217 -- Cur_business_number,
218 -- cur_payroll_form,
219 -- cur_recall in get_date function
220 -- cur_archive_info
221 -- - business_group_id
222 -- Code for ROE reason and Comment.
223 -- 04-NOV-1999 pganguly Changing the date format.
224 -- 09-AUG-1998 pganguly 110.0 Created.
225 --
226 -- ************************************************************************/
227 -- begin
228
229 procedure range_cursor(pactid in number,
230 sqlstr out nocopy varchar2) is
231 begin
232 declare
233 cursor cur_payroll_actions is
234 select
235 legislative_parameters
236 from
237 pay_payroll_actions
238 where
239 payroll_action_id = pactid;
240
241 l_legislative_parameters pay_payroll_actions.legislative_parameters%TYPE;
242 str varchar2(1000);
243 l_person_id per_people_f.person_id%TYPE;
244 l_assignment_set_id pay_payroll_actions.assignment_set_id%TYPE;
245 l_assignment_amend pay_assignment_actions.assignment_action_id%TYPE;
246
247 begin
248 -- hr_utility.trace_on(null,'ROE');
249
250 open cur_payroll_actions;
251 fetch cur_payroll_actions into
252 l_legislative_parameters;
253 close cur_payroll_actions;
254
255 hr_utility.trace('l_legislative_parameters= ' || l_legislative_parameters);
256 l_person_id :=
257 pycadar_pkg.get_parameter('PERSON_ID',l_legislative_parameters);
258 l_assignment_set_id :=
259 pycadar_pkg.get_parameter('ASSIGNMENT_SET_ID',l_legislative_parameters);
260 l_assignment_amend :=
261 pycadar_pkg.get_parameter('ASSIGNMENT_ID',l_legislative_parameters);
262
263 hr_utility.trace('PERSON_ID= ' || to_char(l_person_id));
264 hr_utility.trace('ASSIGNMENT_SET_ID= ' || to_char(l_assignment_set_id));
265 hr_utility.trace('AMEND ASSIGNMENT ACTION ID= ' || to_char(l_assignment_amend));
266
267 if l_assignment_set_id is not null then
268
269 str := 'select
270 distinct paf.person_id
271 from
272 hr_assignment_set_amendments hasa,
273 per_assignments_f paf,
274 pay_payroll_actions ppa
275 WHERE
276 hasa.assignment_set_id =
277 pycadar_pkg.get_parameter(''ASSIGNMENT_SET_ID'',ppa.legislative_parameters) and
278 hasa.include_or_exclude = ''I'' and
279 hasa.assignment_id = paf.assignment_id and
280 ppa.payroll_action_id = :pactid
281 ORDER BY paf.person_id';
282
283 hr_utility.trace('Assignment set id is not null');
284
285 else
286
287 -- For one person only. The person_id will be stored
288 -- in the legislative parameter.
289 -- The first 10 characters of legislative_parameters
290 -- has 'PERSON_ID= '
291
292 str := 'select
293 fnd_number.canonical_to_number(substr(legislative_parameters,11,(decode(instr(legislative_parameters,'' ''),0,10,instr(legislative_parameters,'' '')-11))))
294 from pay_payroll_actions ppa
295 where ppa.payroll_action_id=:pactid';
296
297 end if;
298
299 sqlstr := str;
300
301 end;
302
303 end range_cursor;
304
305 function get_user_entity(p_user_name in varchar2) return number is
306 begin
307 declare
308
309 cursor cur_database_items is
310 select fdi.user_entity_id
311 from ff_database_items fdi
312 where fdi.user_name = p_user_name;
313
314 l_user_entity_id ff_database_items.user_entity_id%TYPE;
315
316 begin
317
318 open cur_database_items;
319
320 fetch cur_database_items
321 into l_user_entity_id;
322
323 if cur_database_items%notfound then
324
325 close cur_database_items ;
326 l_user_entity_id := -1;
327
328 else
329
330 close cur_database_items ;
331
332 end if;
333
334 return l_user_entity_id;
335 end;
336
337 end get_user_entity;
338
339 function get_working_date(p_business_group_id number,
340 p_asg_id number,
341 p_current_date date,
342 p_next_or_prev varchar2) return date is
343 begin
344
345 declare
346
347 cursor cur_paf is
348 select
349 puc.user_column_name
350 from
351 per_assignments_f paf,
352 hr_soft_coding_keyflex hsck,
353 pay_user_columns puc
354 where
355 paf.assignment_id = p_asg_id and
356 paf.business_group_id = p_business_group_id and
357 paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id and
358 p_current_date between
359 paf.effective_start_date and
360 paf.effective_end_date and
361 hsck.segment4 = puc.user_column_id;
362
363 l_working_date date;
364 l_user_column_name pay_user_columns.user_column_name%TYPE;
365 c_ws_tab_name VARCHAR2(80) := 'COMPANY WORK SCHEDULES';
366 l_hour number := 0;
367 v_curr_day varchar2(5);
368
369 cursor cur_hr_org_work is
370 select
371 hoi.org_information2
372 from
373 hr_organization_units hou,
374 hr_organization_information hoi
375 where
376 hou.business_group_id = p_business_group_id and
377 hou.organization_id = hoi.organization_id and
378 hoi.org_information_context = 'Canadian Work Schedule';
379
380 begin
381
382 open cur_paf;
383 fetch
384 cur_paf
385 into
386 l_user_column_name;
387
388 if cur_paf%NOTFOUND then
389
390 close cur_paf;
391
392 open cur_hr_org_work;
393 fetch
394 cur_hr_org_work
395 into
396 l_user_column_name;
397
398 if cur_hr_org_work%NOTFOUND OR
399 l_user_column_name IS NULL then
400
401 close cur_hr_org_work;
402
403 if p_next_or_prev = 'N' then
404 return p_current_date + 1;
405 else
406 return p_current_date - 1;
407 end if;
408
409 else
410
411 close cur_hr_org_work;
412
413 end if;
414
415 else
416
417 close cur_paf;
418
419 end if;
420
421 if p_next_or_prev = 'N' then
422 l_working_date := p_current_date + 1;
423 else
424 l_working_date := p_current_date - 1;
425 end if;
426
427 for i in 1..7 loop
428
429 v_curr_day := to_char(l_working_date,'DY');
430
431 l_hour := fnd_number.canonical_to_number(
432 hruserdt.get_table_value(p_business_group_id,
433 c_ws_tab_name,
434 l_user_column_name,
435 v_curr_day));
436
437 hr_utility.trace('l_hour = ' || to_char(l_hour));
438
439 if l_hour <> 0 then
440 exit;
441 else
442 if p_next_or_prev = 'N' then
443 l_working_date := l_working_date + 1;
444 else
445 l_working_date := l_working_date - 1;
446 end if;
447 end if;
448
449 end loop;
450
451 hr_utility.trace('l working date = ' || to_char(l_working_date));
452 return l_working_date;
453
454 end;
455
456 end; -- get_working_date
457
458 function get_defined_balance_id(p_balance_name in varchar2,
459 p_dimension_name in varchar2,
460 p_business_group_id in number)
461 return number is
462 begin
463
464 declare
465
466 cursor cur_get_def_bal_id is
467 select
468 pdb.defined_balance_id
469 from
470 pay_defined_balances pdb,
471 pay_balance_types pbt,
472 pay_balance_dimensions pbd1
473 where
474 pbt.balance_name = p_balance_name and
475 pbt.business_group_id is null and
476 pbt.legislation_code = 'CA' and
477 pbt.balance_type_id = pdb.balance_type_id and
478 pdb.balance_dimension_id = pbd1.balance_dimension_id and
479 pbd1.dimension_name = p_dimension_name and
480 pbd1.business_group_id is null and
481 pbd1.legislation_code = 'CA';
482
483 l_def_balance_id pay_defined_balances.defined_balance_id%TYPE;
484
485 begin
486
487 hr_utility.trace('Function get_defined_balance_id starts here !');
488
489 open cur_get_def_bal_id;
490 fetch cur_get_def_bal_id
491 into l_def_balance_id;
492 if cur_get_def_bal_id%NOTFOUND then
493 close cur_get_def_bal_id;
494 hr_utility.trace('get_defined_balance_id: Defined balance not found!');
495 return -1;
496 else
497 close cur_get_def_bal_id;
498 hr_utility.trace('get_defined_balance_id: Defined balance found = ' ||
499 to_char(l_def_balance_id));
500 return l_def_balance_id;
501 end if;
502
503 end;
504
505 end get_defined_balance_id;
506
507 function balance_feed_exists(p_balance_name in varchar2,
508 p_business_group_id in number)
509 return BOOLEAN is
510 begin
511
512 declare
513
514 CURSOR cur_bal_feed_exists IS
515 SELECT
516 'X'
517 FROM
518 pay_balance_feeds_f pbf,
519 pay_balance_types pbt,
520 pay_input_values_f piv,
521 pay_element_types_f pet
522 WHERE
523 pbt.balance_name = p_balance_name and
524 pbt.business_group_id is NULL and
525 pbf.balance_type_id = pbt.balance_type_id and
526 pbf.input_value_id = piv.input_value_id and
527 piv.element_type_id = pet.element_type_id;
528 --pbt.balance_type_id = pet.element_information10;
529
530 dummy varchar2(1);
531
532 begin
533
534 hr_utility.trace('Function balance_feed_exists');
535 hr_utility.trace('balance_feed_exists p_balance_name = ' || p_balance_name);
536
537 OPEN cur_bal_feed_exists;
538 FETCH cur_bal_feed_exists
539 INTO dummy;
540 if cur_bal_feed_exists%FOUND then
541 close cur_bal_feed_exists;
542 hr_utility.trace('balance_feed_exists for = ' || p_balance_name);
543 return TRUE;
544 else
545 close cur_bal_feed_exists;
546 hr_utility.trace('Balance Feed doesn''t exist for = ' || p_balance_name);
547 return FALSE;
548 end if;
549
550 end;
551
552 end balance_feed_exists; -- balance_feed_exists
553
554
555 function get_date(p_person_id in number,
556 p_asg_id in number,
557 p_business_group_id in number,
558 p_effective_date in date,
559 p_recall_date out nocopy date,
560 p_roe_reason out nocopy varchar2,
561 p_roe_comment out nocopy varchar2,
562 p_term_or_abs_flag out nocopy varchar2,
563 p_term_or_abs out nocopy varchar2
564 ) return date is
565 begin
566
567 declare
568
569 cursor cur_terminate is
570 select pps.actual_termination_date termination_date,
571 NULL recall_date,
572 pps.pds_information1 roe_reason,
573 pps.pds_information2 roe_comment,
574 pps.date_start
575 from per_periods_of_service pps
576 where pps.person_id=p_person_id
577 and pps.business_group_id = p_business_group_id
578 and p_effective_date - nvl(pps.actual_termination_date,p_effective_date) <= 31
579 and pps.date_start <= p_effective_date
580 and pps.actual_termination_date <= p_effective_date
581 order by pps.date_start desc;
582
583 cursor cur_absence(cp_effective_date date) is
584 select paav.date_start date_start,
585 paav.date_end recall_date,
586 paav.abs_information1 roe_reason,
587 paav.abs_information2 roe_comment
588 from per_absence_attendances_v paav
589 where paav.person_id=p_person_id
590 and paav.business_group_id = p_business_group_id
591 and paav.date_start <= cp_effective_date
592 and p_effective_date - paav.date_start <= 31;
593
594 l_termination_date date;
595 l_absence_date date;
596 l_recall_date date;
597 l_date_start date;
598 l_roe_reason varchar2(150);
599 l_roe_comment varchar2(150);
600 l_effective_date date;
601
602 begin
603 hr_utility.trace('before terminate'||to_char(p_effective_date));
604 open cur_terminate;
605
606 fetch cur_terminate
607 into l_termination_date,
608 l_recall_date,
609 l_roe_reason,
610 l_roe_comment,
611 l_date_start;
612
613 if l_termination_date is null or
614 cur_terminate%notfound then
615
616 close cur_terminate;
617
618 hr_utility.trace('Cur terminate not found');
619
620 l_effective_date := get_working_date(p_business_group_id,
621 p_asg_id,
622 p_effective_date,
623 'N');
624
625 open cur_absence(l_effective_date);
626
627 fetch cur_absence
628 into l_absence_date,
629 l_recall_date,
630 l_roe_reason,
631 l_roe_comment;
632
633 if cur_absence%notfound
634 or l_absence_date is null then
635
636 hr_utility.trace('Cur absence not found');
637 close cur_absence;
638 p_recall_date := l_recall_date;
639 p_roe_reason := l_roe_reason;
640 p_roe_comment := l_roe_comment;
641 p_term_or_abs_flag := 'N';
642 p_term_or_abs := NULL;
643 return p_effective_date;
644
645 else
646
647 hr_utility.trace('Cur absence found');
648 close cur_absence;
649 p_recall_date := l_recall_date;
650 p_roe_reason := l_roe_reason;
651 p_roe_comment := l_roe_comment;
652 p_term_or_abs_flag := 'Y';
653 p_term_or_abs := 'A';
654 hr_utility.trace('l_absence_date: '||to_char(l_absence_date));
655
656 return l_absence_date;
657
658 end if;
659
660 else
661
662 p_recall_date := l_recall_date;
663 p_roe_reason := l_roe_reason;
664 p_roe_comment := l_roe_comment;
665 p_term_or_abs_flag := 'Y';
666 p_term_or_abs := 'T';
667 hr_utility.trace('.....in terminate...else..');
668 close cur_terminate;
669 return l_termination_date;
670
671 end if;
672
673 end;
674
675 end get_date;
676
677 function get_balance_start_date(p_person_id in number,
678 p_effective_date in date)
679 return date is
680 begin
681
682 declare
683
684 cursor cur_aaid is
685 select paa.assignment_action_id
686 from pay_assignment_actions paa,
687 pay_payroll_actions ppa,
688 per_assignments_f paf
689 where paf.person_id = p_person_id
690 and paf.assignment_id = paa.assignment_id
691 and paa.payroll_action_id = ppa.payroll_action_id
692 and ppa.action_type = 'X'
693 and ppa.action_status = 'C'
694 and ppa.effective_date < p_effective_date
695 and ppa.report_type = 'ROE'
696 order by ppa.effective_date desc;
697
698 cursor cur_dates (b_assignment_action_id number) is
699 select fnd_date.canonical_to_date(fai.value)
700 from ff_archive_items fai,
701 ff_database_items fdi
702 where fai.user_entity_id = fdi.user_entity_id
703 and fdi.user_name = 'ROE_DATE'
704 and fai.context1 = to_char(b_assignment_action_id);
705
706 l_ass_act_id number(15);
707 l_start_date date;
708
709 begin
710
711 open cur_aaid;
712 fetch cur_aaid
713 into l_ass_act_id;
714 close cur_aaid;
715
716 open cur_dates(l_ass_act_id);
717 fetch cur_dates
718 into l_start_date;
719
720 if cur_dates%notfound then
721 close cur_dates;
722 return null;
723 else
724 close cur_dates;
725 return l_start_date;
726 end if;
727 end;
728
729 end get_balance_start_date;
730
731 procedure populate_box17c_bal_table is
732 begin
733
734 declare
735
736 begin
737
738 pay_ca_archive.box17c_bal_table(1).code := 'A';
739 pay_ca_archive.box17c_bal_table(1).balance_name
740 := 'ROE Other Monies Anniversary Payout';
741
742 pay_ca_archive.box17c_bal_table(2).code := 'B';
743 pay_ca_archive.box17c_bal_table(2).balance_name
744 := 'ROE Other Monies Bonus';
745
746 pay_ca_archive.box17c_bal_table(3).code := 'E';
747 pay_ca_archive.box17c_bal_table(3).balance_name
748 := 'ROE Other Monies Severance Pay';
749
750 pay_ca_archive.box17c_bal_table(4).code := 'G';
751 pay_ca_archive.box17c_bal_table(4).balance_name
752 := 'ROE Other Monies Gratuities';
753
754 pay_ca_archive.box17c_bal_table(5).code := 'H';
755 pay_ca_archive.box17c_bal_table(5).balance_name
756 := 'ROE Other Monies Honorariums';
757
758 pay_ca_archive.box17c_bal_table(6).code := 'I';
759 pay_ca_archive.box17c_bal_table(6).balance_name
760 := 'ROE Other Monies Sick Leave Credit';
761
762 pay_ca_archive.box17c_bal_table(7).code := 'N';
763 pay_ca_archive.box17c_bal_table(7).balance_name
764 := 'ROE Other Monies Pensions';
765
766 pay_ca_archive.box17c_bal_table(8).code := 'O';
767 pay_ca_archive.box17c_bal_table(8).balance_name
768 := 'ROE Other Monies Other';
769
770 pay_ca_archive.box17c_bal_table(9).code := 'R';
771 pay_ca_archive.box17c_bal_table(9).balance_name
772 := 'ROE Other Monies Retirement Leave Credits';
773
774 pay_ca_archive.box17c_bal_table(10).code := 'S';
775 pay_ca_archive.box17c_bal_table(10).balance_name
776 := 'ROE Other Monies Settlement or Labour Arb Award';
777
778 pay_ca_archive.box17c_bal_table(11).code := 'U';
779 pay_ca_archive.box17c_bal_table(11).balance_name
780 := 'ROE Other Monies Supplementary Unemployment Benefits';
781
782 pay_ca_archive.box17c_bal_table(12).code := 'Y';
783 pay_ca_archive.box17c_bal_table(12).balance_name
784 := 'ROE Other Monies Pay in Lieu of Notice';
785
786 end;
787
788 end; -- populate_box17c_bal_table;
789
790 procedure archinit(p_payroll_action_id in number) is
791 begin
792
793 DECLARE
794
795 CURSOR cur_bg_id IS
796 SELECT
797 business_group_id
798 FROM
799 pay_payroll_actions
800 WHERE
801 payroll_action_id = p_payroll_action_id;
802
803 l_bg_id per_business_groups.business_group_id%TYPE;
804
805 BEGIN
806
807 OPEN cur_bg_id;
808 FETCH cur_bg_id
809 INTO l_bg_id;
810 CLOSE cur_bg_id;
811
812 hr_utility.trace('Archive initialization');
813 pay_ca_roe_ei_pkg.populate_element_table(l_bg_id);
814 populate_box17c_bal_table;
815
816 END;
817
818 end archinit;
819
820 function archive_value(p_assactid in number,
821 p_user_name in varchar2) return varchar2 is
822
823 begin
824
825 declare
826
827 cursor cur_archive_value is
828 select fai.value
829 from ff_archive_items fai,
830 ff_database_items fdi
831 where fdi.user_name = p_user_name
832 and fdi.user_entity_id = fai.user_entity_id
833 and fai.context1 = p_assactid;
834
835 l_value ff_archive_items.value%type;
836
837 begin
838
839 open cur_archive_value;
840
841 fetch cur_archive_value
842 into l_value;
843
844 close cur_archive_value;
845
846 return l_value;
847
848 end;
849
850 end;
851
852 procedure action_creation(pactid in number,
853 stperson in number,
854 endperson in number,
855 chunk in number) is
856 begin
857 declare
858
859 cursor cur_payroll_actions is
860 select
861 legislative_parameters,
862 effective_date,
863 business_group_id
864 from
865 pay_payroll_actions
866 where
867 payroll_action_id = pactid;
868
869 l_start_date DATE;
870
871 l_legislative_parameters pay_payroll_actions.legislative_parameters%TYPE;
872 str varchar2(1000);
873 l_person_id per_people_f.person_id%type;
874 l_assignment_set_id pay_payroll_actions.assignment_set_id%type;
875 l_effective_date pay_payroll_actions.effective_date%type;
876 l_effective_date1 pay_payroll_actions.effective_date%type;
877 l_business_group_id pay_payroll_actions.business_group_id%type;
878 l_assignment_amend pay_assignment_actions.assignment_action_id%TYPE;
879
880 cursor cur_asg_set_person_id is
881 select
882 distinct paf.person_id person_id
883 from
884 hr_assignment_set_amendments hasa,
885 per_assignments_f paf
886 WHERE
887 hasa.assignment_set_id = l_assignment_set_id and
888 hasa.include_or_exclude = 'I' and
889 hasa.assignment_id = paf.assignment_id and
890 least(l_effective_date,paf.effective_end_date) between
891 paf.effective_start_date and
892 paf.effective_end_date;
893
894 cursor cur_paf(b_person_id per_people_f.person_id%type,
895 b_start_date DATE,
896 b_end_date DATE)
897 is select
898 paf.assignment_id assignment_id,
899 paf.payroll_id payroll_id
900 from
901 per_assignments_f paf
902 where
903 paf.person_id = b_person_id
904 and paf.person_id >= stperson
905 and paf.person_id <= endperson
906 and paf.assignment_type in ('E','C')
907 and (paf.effective_end_date >= b_end_date
908 or trunc(paf.effective_end_date) = hr_general.END_OF_TIME
909 )
910 and paf.effective_start_date <= b_start_date
911 group by
912 paf.assignment_id,
913 paf.payroll_id;
914
915 cursor cur_gre(p_assignment_id per_assignments_f.assignment_id%type,
916 p_payroll_id per_assignments_f.payroll_id%type,
917 p_effective_date date) is
918 select
919 distinct paa.tax_unit_id gre_id,
920 'T4' gre_type
921 from
922 pay_assignment_actions paa,
923 pay_payroll_actions ppa,
924 hr_organization_information hoi
925 where
926 paa.assignment_id = p_assignment_id and
927 ppa.payroll_action_id = paa.payroll_action_id and
928 ppa.payroll_id = p_payroll_id and
929 ppa.action_type in ( 'R','B','F','R','Q','I') and
930 ppa.action_status = 'C' and
931 p_effective_date - 400 <= ppa.date_earned and
932 ppa.date_earned <= p_effective_date and
933 hoi.organization_id = paa.tax_unit_id and
934 hoi.org_information_context = 'Canada Employer Identification' and
935 hoi.org_information5 = 'T4/RL1'
936 union all
937 select
938 distinct paa.tax_unit_id gre_id,
939 'T4A' gre_type
940 from
941 pay_assignment_actions paa,
942 pay_payroll_actions ppa,
943 hr_organization_information hoi
944 where
945 paa.assignment_id = p_assignment_id and
946 ppa.payroll_action_id = paa.payroll_action_id and
947 ppa.payroll_id = p_payroll_id and
948 ppa.action_type in ( 'R','B','F','R','Q') and
949 ppa.action_status = 'C' and
950 p_effective_date - 400 <= ppa.date_earned and
951 ppa.date_earned <= p_effective_date and
952 hoi.organization_id = paa.tax_unit_id and
953 hoi.org_information_context = 'Canada Employer Identification' and
954 hoi.org_information5 in ('T4A/RL1','T4A/RL2') and
955 not exists
956 (select 1
957 from
958 pay_assignment_actions paa_t4,
959 pay_payroll_actions ppa_t4,
960 hr_organization_information hoi_t4
961 where
962 paa_t4.assignment_id = p_assignment_id and
963 ppa_t4.payroll_action_id = paa_t4.payroll_action_id and
964 ppa_t4.payroll_id = p_payroll_id and
965 ppa_t4.action_type in ( 'R','B','F','R','Q') and
966 ppa_t4.action_status = 'C' and
967 p_effective_date - 400 <= ppa_t4.date_earned and
968 ppa_t4.date_earned <= p_effective_date and
969 hoi_t4.organization_id = paa_t4.tax_unit_id and
970 hoi_t4.org_information_context = 'Canada Employer Identification' and
971 hoi_t4.org_information5 = 'T4/RL1');
972
973 cursor cur_asg_action_id is
974 select
975 pay_assignment_actions_s.nextval
976 from
977 dual;
978
979 cursor cur_prd_end_date(p_payroll_id number,
980 p_date date) is
981 select
982 ptp.end_date
983 from
984 per_time_periods ptp
985 where
986 ptp.payroll_id = p_payroll_id and
987 p_date between
988 ptp.start_date and ptp.end_date;
989
990 l_lockingactid number;
991 l_assignment_id number;
992 l_tax_unit_id number;
993 l_value ff_archive_items.value%type;
994 l_user_entity_id ff_user_entities.user_entity_id%type;
995 l_archive_item_id ff_archive_items.archive_item_id%type;
996 l_object_version_number number(9);
997 l_some_warning boolean;
998 l_prev_roe_date date;
999 l_roe_date date;
1000 total_no_fields number;
1001 l_recall_date date;
1002 l_roe_reason varchar2(150);
1003 l_roe_comment varchar2(150);
1004 l_end_date date;
1005 l_date_start date;
1006
1007 TYPE tab_char240 is table of varchar2(240)
1008 index by binary_integer;
1009 TYPE tab_num9 is table of number(9)
1010 index by binary_integer;
1011
1012 l_user_entity_amend tab_num9;
1013 l_value_amend tab_char240;
1014 l_term_or_abs_flag varchar2(1);
1015 l_term_or_abs varchar2(1);
1016 l_first_t4_gre varchar2(1);
1017 multiple_gre boolean := FALSE;
1018
1019 cursor cur_employee_hire_date is
1020 select max(service.date_start) hire_date
1021 from per_periods_of_service service,
1022 per_assignments_f asg
1023 -- where asg.assignment_id = l_assignment_id
1024 where asg.person_id = l_person_id
1025 and l_effective_date BETWEEN
1026 asg.effective_start_date
1027 AND asg.effective_end_date
1028 and asg.person_id = service.person_id(+)
1029 and service.date_start <= l_effective_date;
1030
1031 cursor cur_abs ( b_person_id number,
1032 b_date_start date) is
1033 select abs.date_end date_end
1034 from per_absence_attendances abs
1035 where abs.person_id = b_person_id
1036 and abs.date_start = b_date_start;
1037
1038 /* FUNCTION last_period_of_service (p_effective_date date,
1039 p_person_id number,
1040 p_business_group_id number)
1041 RETURN DATE IS
1042 BEGIN
1043 DECLARE
1044
1045 l_date_start date;
1046
1047 CURSOR cur_max_date_start IS
1048 SELECT max(date_start)
1049 FROM per_periods_of_service
1050 WHERE person_id = p_person_id
1051 AND business_group_id = p_business_group_id
1052 AND date_start <= p_effective_date;
1053 --AND date_start >= p_effective_date - 400;
1054
1055 BEGIN
1056
1057 OPEN cur_max_date_start;
1058 FETCH cur_max_date_start INTO l_date_start;
1059 CLOSE cur_max_date_start;
1060
1061 RETURN l_date_start;
1062 END;
1063
1064 END; */
1065
1066 FUNCTION check_retry_amend(p_person_id number,
1067 p_assignment_id number,
1068 p_payroll_id number,
1069 p_gre_id number,
1070 p_roe_date date) RETURN BOOLEAN IS
1071 begin
1072
1073 declare
1074
1075 v_assignment_id number;
1076 v_payroll_id number;
1077 v_gre_id number;
1078 v_roe_date number;
1079
1080 cursor cur_retry is
1081 select
1082 paa.assignment_action_id locked_action_id
1083 from
1084 pay_payroll_actions ppa,
1085 pay_assignment_actions paa,
1086 ff_archive_items fai1,
1087 ff_archive_items fai2
1088 where
1089 ppa.report_type = 'ROE' and
1090 ppa.report_category = 'ROEC' and
1091 ppa.report_qualifier = 'ROEQ' and
1092 ppa.payroll_action_id = paa.payroll_action_id and
1093 paa.tax_unit_id = p_gre_id and
1094 paa.assignment_id = p_assignment_id and
1095 paa.assignment_action_id = fai1.context1 and
1096 fai1.user_entity_id = v_roe_date and
1097 fnd_date.canonical_to_date(fai1.value) =
1098 fnd_date.canonical_to_date(to_char(p_roe_date,'yyyy/mm/dd hh24:mi:ss')) and
1099 fai1.context1 = fai2.context1 and
1100 fai2.user_entity_id = v_payroll_id and
1101 fai2.value = to_char(p_payroll_id);
1102
1103 l_context1 number;
1104 dummy varchar2(1);
1105
1106
1107 cursor cur_amend is
1108 select
1109 'x'
1110 from
1111 pay_action_interlocks
1112 where
1113 locked_action_id = l_context1;
1114
1115 cursor cur_ppf is
1116 select
1117 full_name
1118 from
1119 per_people_f ppf
1120 where
1121 ppf.person_id = p_person_id and
1122 p_roe_date between ppf.effective_start_date and
1123 ppf.effective_end_date;
1124
1125 l_full_name per_people_f.full_name%TYPE;
1126
1127 begin
1128
1129 v_assignment_id := get_user_entity('ROE_ASSIGNMENT_ID');
1130 v_payroll_id := get_user_entity('ROE_PAYROLL_ID');
1131 v_gre_id := get_user_entity('ROE_GRE_ID');
1132 v_roe_date := get_user_entity('ROE_DATE');
1133
1134 open cur_ppf;
1135 fetch cur_ppf into l_full_name;
1136 close cur_ppf;
1137
1138 hr_utility.set_location('check_retry_amend' , 5);
1139 open cur_retry;
1140 fetch cur_retry into l_context1;
1141
1142 if (cur_retry%FOUND) then
1143
1144 hr_utility.set_location('check_retry_amend' , 7);
1145 hr_utility.trace('l_context1 = '|| to_char(l_context1));
1146 close cur_retry;
1147 open cur_amend;
1148 fetch cur_amend into dummy;
1149 if cur_amend%FOUND then
1150
1151 -- Record has already been locked by Mag Process.
1152 -- So it is an amend issue.
1153
1154 close cur_amend;
1155
1156 hr_utility.set_location('pay_ca_archive.cur_amend', 10);
1157
1158 hr_utility.set_message(801,'PAY_74024_ROE_AMEND_RECORD');
1159 hr_utility.set_message_token('PERSON',l_full_name);
1160 --hr_utility.raise_error;
1161 RETURN FALSE;
1162 else
1163 close cur_amend;
1164
1165 -- Record not found, so it is a
1166 -- Retry Issue
1167
1168 hr_utility.set_location('pay_ca_archive.cur_amend', 20);
1169 hr_utility.set_message(801,'PAY_74023_ROE_RETRY_RECORD');
1170 hr_utility.set_message_token('PERSON',l_full_name);
1171 --hr_utility.raise_error;
1172 RETURN FALSE;
1173
1174 end if;
1175
1176 else
1177
1178 close cur_retry;
1179 hr_utility.set_location('pay_ca_archive.cur_retry', 10);
1180 RETURN TRUE;
1181
1182 end if;
1183
1184 end;
1185
1186 end; -- End check_retry_amend
1187
1188 begin
1189
1190 hr_utility.set_location('Package pay_ca_archive...action creation',1);
1191
1192 open cur_payroll_actions;
1193 fetch cur_payroll_actions
1194 into
1195 l_legislative_parameters,
1196 l_effective_date,
1197 l_business_group_id;
1198 close cur_payroll_actions;
1199
1200 l_person_id :=
1201 pycadar_pkg.get_parameter('PERSON_ID',l_legislative_parameters);
1202 l_assignment_set_id :=
1203 pycadar_pkg.get_parameter('ASSIGNMENT_SET_ID',l_legislative_parameters);
1204 l_assignment_amend :=
1205 pycadar_pkg.get_parameter('ASSIGNMENT_ID',l_legislative_parameters);
1206 l_end_date := l_effective_date;
1207
1208 hr_utility.trace('l_legislative_parameters '|| l_legislative_parameters);
1209 hr_utility.trace('Person ID = '|| to_char(l_person_id));
1210 hr_utility.trace('ASSIGNMENT_SET_ID= ' || to_char(l_assignment_set_id));
1211 hr_utility.trace('AMEND ASSIGNMENT ACTION ID= ' ||
1212 to_char(l_assignment_amend));
1213
1214 if l_assignment_set_id is null then
1215
1216 -- Find the latest hire date of employee
1217 -- so that assignments that existed previous
1218 -- to this date are not archived
1219
1220 /* l_date_start :=
1221 last_period_of_service (l_effective_date,
1222 l_person_id,
1223 l_business_group_id);
1224
1225 hr_utility.trace('l_date_start = ' ||to_char(l_date_start)); */
1226
1227 -- If assignment_set_id is null then the
1228 -- archiver is running for one person, the id of
1229 -- the person is stored in legislative_parameters.
1230
1231 -- l_effective_date is stored in l_end_date before
1232 -- get_date is called as l_effective_date may be changed
1233 -- to termination_date or absence_date after calling
1234 -- get_date function.
1235
1236 l_effective_date := get_date(l_person_id,
1237 l_assignment_id,
1238 l_business_group_id,
1239 l_effective_date,
1240 l_recall_date,
1241 l_roe_reason,
1242 l_roe_comment,
1243 l_term_or_abs_flag,
1244 l_term_or_abs);
1245 hr_utility.trace('l_effective_date: '||to_char(l_effective_date));
1246
1247 l_roe_date := l_effective_date;
1248
1249 l_prev_roe_date := get_balance_start_date(l_person_id,
1250 l_effective_date);
1251
1252
1253 if l_assignment_amend is not null then
1254
1255 open cur_asg_action_id;
1256 fetch cur_asg_action_id into l_lockingactid;
1257 if cur_asg_action_id%NOTFOUND then
1258 close cur_asg_action_id;
1259 hr_utility.trace('Locking action id not found');
1260 else
1261 close cur_asg_action_id;
1262 hr_utility.trace('Locking action id found');
1263 end if;
1264
1265 l_user_entity_amend(1) := get_user_entity('ROE_ASSIGNMENT_ID');
1266 l_value_amend(1) := archive_value(l_assignment_amend,'ROE_ASSIGNMENT_ID');
1267
1268 l_user_entity_amend(2) := get_user_entity('ROE_PAYROLL_ID');
1269 l_value_amend(2) := archive_value(l_assignment_amend,'ROE_PAYROLL_ID');
1270
1271 l_user_entity_amend(3) := get_user_entity('ROE_GRE_ID');
1272 l_value_amend(3) := archive_value(l_assignment_amend,'ROE_GRE_ID');
1273
1274 l_user_entity_amend(4) := get_user_entity('PREV_ROE_DATE');
1275 l_value_amend(4) := archive_value(l_assignment_amend,'PREV_ROE_DATE');
1276
1277 l_user_entity_amend(5) := get_user_entity('ROE_DATE');
1278 l_value_amend(5) := archive_value(l_assignment_amend,'ROE_DATE');
1279
1280 hr_utility.trace('l_value_amend(1)'||l_value_amend(1));
1281 hr_utility.trace('l_value_amend(2)'||l_value_amend(2));
1282 hr_utility.trace('l_value_amend(3)'||l_value_amend(3));
1283
1284 hr_nonrun_asact.insact(l_lockingactid,
1285 l_value_amend(1),
1286 pactid,
1287 chunk,
1288 l_value_amend(3)
1289 );
1290
1291 total_no_fields := 5;
1292
1293 for j in 1..total_no_fields loop
1294
1295 ff_archive_api.create_archive_item(
1296 p_archive_item_id => l_archive_item_id,
1297 p_user_entity_id => l_user_entity_amend(j),
1298 p_archive_value => l_value_amend(j),
1299 p_archive_type => 'AAC',
1300 p_action_id => l_lockingactid,
1301 p_legislation_code => 'CA',
1302 p_object_version_number => l_object_version_number,
1303 p_some_warning => l_some_warning);
1304
1305 end loop; -- tot_no_fields
1306
1307 else
1308
1309 l_start_date := get_balance_start_date(l_person_id, l_effective_date);
1310
1311 IF l_start_date is NOT NULL THEN
1312 open cur_abs(l_person_id, l_start_date);
1313 fetch cur_abs into l_start_date;
1314 close cur_abs;
1315 if l_start_date is not null then
1316 l_start_date := get_working_date(l_business_group_id,
1317 l_assignment_id,
1318 l_start_date,
1319 'N');
1320 else
1321 open cur_employee_hire_date;
1322 fetch cur_employee_hire_date
1323 into l_start_date;
1324 close cur_employee_hire_date;
1325 end if;
1326 l_start_date := GREATEST(l_start_date, l_effective_date - 400);
1327 ELSE
1328 l_start_date := l_effective_date - 400;
1329 END IF;
1330
1331 hr_utility.trace('l_person_id = '|| l_person_id);
1332 hr_utility.trace('l_start_date = '|| l_start_date);
1333 hr_utility.trace('l_effective_date = '|| l_effective_date);
1334
1335 for i in cur_paf(l_person_id, l_effective_date, l_start_date) loop
1336
1337 hr_utility.trace('I.assignment ID = '|| to_char(i.assignment_id));
1338 hr_utility.trace('I.payroll ID = '|| to_char(i.payroll_id));
1339 hr_utility.set_location('Get the locking action id', 1);
1340
1341 open cur_prd_end_date(i.payroll_id,
1342 l_roe_date);
1343 fetch cur_prd_end_date
1344 into l_effective_date1;
1345 close cur_prd_end_date;
1346
1347 hr_utility.trace('l_effective_date1 = '|| to_char(l_effective_date1));
1348
1349 l_first_t4_gre := 'Y';
1350
1351 for k in cur_gre(i.assignment_id,i.payroll_id,l_effective_date1) loop
1352
1353 hr_utility.trace('k.GRE ID = '|| to_char(k.gre_id));
1354 hr_utility.trace('k.GRE TYPE = '|| k.gre_type);
1355
1356 if (k.gre_type = 'T4' and l_first_t4_gre = 'Y') then
1357 multiple_gre := TRUE;
1358 l_first_t4_gre := 'N';
1359 else
1360 multiple_gre := FALSE;
1361 end if;
1362
1363 open cur_asg_action_id;
1364 fetch cur_asg_action_id into l_lockingactid;
1365 if cur_asg_action_id%NOTFOUND then
1366 close cur_asg_action_id;
1367 hr_utility.trace('Locking action id not found');
1368 else
1369 close cur_asg_action_id;
1370 hr_utility.trace('Locking action id found');
1371 end if;
1372
1373 hr_nonrun_asact.insact(l_lockingactid,i.assignment_id,
1374 pactid,chunk,k.gre_id);
1375
1376 IF multiple_gre THEN
1377 update pay_assignment_actions
1378 set
1379 serial_number = 'Y'
1380 where
1381 assignment_action_id = l_lockingactid;
1382 END IF;
1383
1384 -- This portion of the code checks for Record already
1385 -- Exists or not. If Exists then we need to error out
1386 -- the assignment.
1387 --
1388 -- If Record already exists and isn't locked by Mag
1389 -- Process then we need to pass a error message saying
1390 -- the retry process should be tried for this assignment.
1391 --
1392 -- If it is locked by Mag process then we error out saying
1393 -- user need to amend the assignment.
1394
1395 hr_utility.trace('Date: '||to_char(l_roe_date));
1396
1397 if check_retry_amend(l_person_id,
1398 i.assignment_id,
1399 i.payroll_id,
1400 k.gre_id,
1401 l_roe_date) then
1402
1403 -- The GRE, payroll,assignment_id will be archived
1404 -- in the action creation level. The archive_type
1405 -- flag in the pay_report_format_items will have ACC
1406 -- The start_date and end_date is also archived
1407 -- in the assignment_action creation level
1408
1409 total_no_fields := 5;
1410
1411 hr_utility.trace('GRE ID = '|| to_char(k.gre_id));
1412
1413 for j in 1..total_no_fields loop
1414
1415 if j = 1 then
1416 l_value := i.assignment_id;
1417 l_user_entity_id := get_user_entity('ROE_ASSIGNMENT_ID');
1418 elsif j = 2 then
1419 l_value := i.payroll_id;
1420 l_user_entity_id := get_user_entity('ROE_PAYROLL_ID');
1421 elsif j = 3 then
1422 l_value := k.gre_id;
1423 l_user_entity_id := get_user_entity('ROE_GRE_ID');
1424 elsif j = 4 then
1425 l_value := to_char(l_prev_roe_date,'YYYY/MM/DD HH24:MI:SS');
1426 l_user_entity_id := get_user_entity('PREV_ROE_DATE');
1427 elsif j = 5 then
1428 l_value := to_char(l_roe_date,'YYYY/MM/DD HH24:MI:SS');
1429 l_user_entity_id := get_user_entity('ROE_DATE');
1430 end if;
1431
1432 ff_archive_api.create_archive_item(
1433 p_archive_item_id => l_archive_item_id,
1434 p_user_entity_id => l_user_entity_id,
1435 p_archive_value => l_value,
1436 p_archive_type => 'AAC',
1437 p_action_id => l_lockingactid,
1438 p_legislation_code => 'CA',
1439 p_object_version_number => l_object_version_number,
1440 p_some_warning => l_some_warning);
1441
1442 end loop; -- tot_no_fields
1443
1444 end if; -- check_retry_amend
1445
1446 end loop; -- cur_gre
1447
1448 end loop; -- cur_paf
1449
1450 end if; -- End if (l_assignment_amend)
1451
1452 else
1453
1454 for p_id in cur_asg_set_person_id loop
1455
1456 l_person_id := p_id.person_id;
1457
1458 -- Find the latest hire date of employee
1459 -- so that assignments that existed previous
1460 -- to this date are not archived
1461
1462 /* l_date_start :=
1463 last_period_of_service (l_end_date,
1464 l_person_id,
1465 l_business_group_id);
1466
1467 hr_utility.trace('l_date_start = ' ||to_char(l_date_start)); */
1468
1469 l_effective_date := get_date(l_person_id,
1470 l_assignment_id,
1471 l_business_group_id,
1472 l_end_date,
1473 l_recall_date,
1474 l_roe_reason,
1475 l_roe_comment,
1476 l_term_or_abs_flag,
1477 l_term_or_abs);
1478 hr_utility.trace('l_effective_date: '||to_char(l_effective_date));
1479 hr_utility.trace('l_end_date: '||to_char(l_end_date));
1480
1481 l_roe_date := l_effective_date;
1482
1483 l_prev_roe_date := get_balance_start_date(l_person_id,
1484 l_effective_date);
1485
1486
1487 l_start_date := l_prev_roe_date;
1488
1489
1490 IF l_start_date is NOT NULL THEN
1491 open cur_abs(l_person_id, l_start_date);
1492 fetch cur_abs into l_start_date;
1493 close cur_abs;
1494 if l_start_date is not null then
1495 l_start_date := get_working_date(l_business_group_id,
1496 l_assignment_id,
1497 l_start_date,
1498 'N');
1499 else
1500 open cur_employee_hire_date;
1501 fetch cur_employee_hire_date
1502 into l_start_date;
1503 close cur_employee_hire_date;
1504 end if;
1505 l_start_date := GREATEST(l_start_date, l_effective_date - 400);
1506 ELSE
1507 l_start_date := l_effective_date - 400;
1508 END IF;
1509
1510 hr_utility.trace('l_person_id = '|| l_person_id);
1511 hr_utility.trace('l_start_date = '|| l_start_date);
1512 hr_utility.trace('l_effective_date = '|| l_effective_date);
1513
1514 if l_term_or_abs in ('A','T') then
1515 for i in cur_paf(l_person_id, l_effective_date, l_start_date) loop
1516
1517 hr_utility.trace('I.assignment ID = '|| to_char(i.assignment_id));
1518 hr_utility.trace('I.payroll ID = '|| to_char(i.payroll_id));
1519 hr_utility.set_location('Get the locking action id', 1);
1520
1521 open cur_prd_end_date(i.payroll_id,
1522 l_roe_date);
1523 fetch cur_prd_end_date
1524 into l_effective_date1;
1525 close cur_prd_end_date;
1526
1527 hr_utility.trace('l_effective_date1 = '|| to_char(l_effective_date1));
1528
1529 l_first_t4_gre := 'Y';
1530
1531 for k in cur_gre(i.assignment_id,i.payroll_id,l_effective_date1) loop
1532
1533 hr_utility.trace('k.GRE ID = '|| to_char(k.gre_id));
1534 hr_utility.trace('k.GRE TYPE = '|| k.gre_type);
1535
1536 if (k.gre_type = 'T4' and l_first_t4_gre = 'Y') then
1537 multiple_gre := TRUE;
1538 l_first_t4_gre := 'N';
1539 else
1540 multiple_gre := FALSE;
1541 end if;
1542
1543 open cur_asg_action_id;
1544 fetch cur_asg_action_id into l_lockingactid;
1545 if cur_asg_action_id%NOTFOUND then
1546 close cur_asg_action_id;
1547 hr_utility.trace('Locking action id not found');
1548 else
1549 close cur_asg_action_id;
1550 hr_utility.trace('Locking action id found');
1551 end if;
1552
1553
1554 hr_nonrun_asact.insact(l_lockingactid,i.assignment_id,
1555 pactid,chunk,k.gre_id);
1556
1557 IF multiple_gre THEN
1558 update pay_assignment_actions
1559 set
1560 serial_number = 'Y'
1561 where
1562 assignment_action_id = l_lockingactid;
1563 END IF;
1564
1565 -- This portion of the code checks for Record already
1566 -- Exists or not. If Exists then we need to error out
1567 -- the assignment.
1568 --
1569 -- If Record already exists and isn't locked by Mag
1570 -- Process then we need to pass a error message saying
1571 -- the retry process should be tried for this assignment.
1572 --
1573 -- If it is locked by Mag process then we error out saying
1574 -- user need to amend the assignment.
1575
1576 hr_utility.trace('Date: '||to_char(l_roe_date));
1577
1578 if check_retry_amend(l_person_id,
1579 i.assignment_id,
1580 i.payroll_id,
1581 k.gre_id,
1582 l_roe_date) then
1583
1584 -- The GRE, payroll,assignment_id will be archived
1585 -- in the action creation level. The archive_type
1586 -- flag in the pay_report_format_items will have ACC
1587 -- The start_date and end_date is also archived
1588 -- in the assignment_action creation level
1589
1590 total_no_fields := 5;
1591
1592 hr_utility.trace('GRE ID = '|| to_char(k.gre_id));
1593
1594 for j in 1..total_no_fields loop
1595
1596 if j = 1 then
1597 l_value := i.assignment_id;
1598 l_user_entity_id := get_user_entity('ROE_ASSIGNMENT_ID');
1599 elsif j = 2 then
1600 l_value := i.payroll_id;
1601 l_user_entity_id := get_user_entity('ROE_PAYROLL_ID');
1602 elsif j = 3 then
1603 l_value := k.gre_id;
1604 l_user_entity_id := get_user_entity('ROE_GRE_ID');
1605 elsif j = 4 then
1606 l_value := to_char(l_prev_roe_date,'YYYY/MM/DD HH24:MI:SS');
1607 l_user_entity_id := get_user_entity('PREV_ROE_DATE');
1608 elsif j = 5 then
1609 l_value := to_char(l_roe_date,'YYYY/MM/DD HH24:MI:SS');
1610 l_user_entity_id := get_user_entity('ROE_DATE');
1611 end if;
1612
1613 ff_archive_api.create_archive_item(
1614 p_archive_item_id => l_archive_item_id,
1615 p_user_entity_id => l_user_entity_id,
1616 p_archive_value => l_value,
1617 p_archive_type => 'AAC',
1618 p_action_id => l_lockingactid,
1619 p_legislation_code => 'CA',
1620 p_object_version_number => l_object_version_number,
1621 p_some_warning => l_some_warning);
1622
1623 end loop; -- tot_no_fields
1624
1625 end if; -- check_retry_amend
1626
1627 end loop; -- cur_gre
1628
1629 end loop; -- cur_paf
1630 end if;
1631
1632 end loop; -- cur_asg_set_person_id
1633
1634 hr_utility.trace('Action Creation: assignment set is passed');
1635
1636 end if; -- End if (Assignment set)
1637 end;
1638
1639 hr_utility.trace_off;
1640
1641 end action_creation;
1642
1643
1644 function func_expected_date_of_return (p_asg_id number,
1645 p_payroll_id number,
1646 p_gre_id number) return date is
1647 begin
1648 declare
1649
1650 l_edor_uid ff_user_entities.user_entity_id%TYPE;
1651 l_payroll_uid ff_user_entities.user_entity_id%TYPE;
1652
1653 cursor cur_edor is
1654 select
1655 fai2.value
1656 from
1657 pay_payroll_actions ppa,
1658 pay_assignment_actions paa,
1659 ff_archive_items fai1,
1660 ff_archive_items fai2
1661 where
1662 ppa.report_type = 'ROE' and
1663 ppa.report_category = 'ROEC' and
1664 ppa.report_qualifier = 'ROEQ' and
1665 ppa.payroll_action_id = paa.payroll_action_id and
1666 paa.tax_unit_id = p_gre_id and
1667 paa.assignment_id = p_asg_id and
1668 paa.assignment_action_id = fai1.context1 and
1669 fai1.user_entity_id = l_payroll_uid and
1670 fai1.value = to_char(p_payroll_id) and
1671 fai1.context1 = fai2.context1 and
1672 fai2.user_entity_id = l_edor_uid;
1673
1674
1675 l_value ff_archive_items.value%TYPE;
1676 l_temp_date date;
1677
1678 begin
1679
1680 hr_utility.trace('func_expected_date_of_return');
1681
1682 l_edor_uid := get_user_entity('ROE_EXPECTED_DATE_OF_RECALL');
1683 l_payroll_uid := get_user_entity('ROE_PAYROLL_ID');
1684
1685 open cur_edor;
1686 fetch cur_edor
1687 into l_value;
1688 close cur_edor;
1689
1690 hr_utility.trace('func_expected_date_of_return l_value = ' || l_value);
1691 l_temp_date := fnd_date.canonical_to_date(l_value);
1692
1693 return l_temp_date;
1694
1695 end;
1696 end; -- func_expected_date_of_return
1697
1698 procedure archive_data(p_assactid in number,
1699 p_effective_date in date) is
1700 begin
1701 declare
1702
1703 TYPE tab_varchar2 IS TABLE OF VARCHAR2(200)
1704 INDEX BY BINARY_INTEGER;
1705
1706 TYPE tab_number IS TABLE OF NUMBER
1707 INDEX BY BINARY_INTEGER;
1708
1709 tab_user_entity_name tab_varchar2;
1710
1711 cursor cur_abs ( b_person_id number,
1712 b_date_start date) is
1713 select abs.date_end date_end
1714 from per_absence_attendances abs
1715 where abs.person_id = b_person_id
1716 and abs.date_start = b_date_start;
1717
1718 cursor cur_archive_info is
1719 select
1720 paa.assignment_id,
1721 paa.tax_unit_id,
1722 ppa.effective_date,
1723 ppa.business_group_id,
1724 ppa.payroll_id,
1725 legislative_parameters,
1726 NVL(paa.serial_number,'N')
1727 from
1728 pay_payroll_actions ppa,
1729 pay_assignment_actions paa
1730 where
1731 paa.assignment_action_id = p_assactid and
1732 paa.payroll_action_id = ppa.payroll_action_id;
1733
1734 cursor cur_prd_end_date(p_payroll_id number,
1735 p_date date) is
1736 select
1737 ptp.end_date
1738 from
1739 per_time_periods ptp
1740 where
1741 ptp.payroll_id = p_payroll_id and
1742 p_date between
1743 ptp.start_date and ptp.end_date;
1744
1745 l_assignment_id pay_assignment_actions.assignment_id%type;
1746 l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
1747 l_effective_date pay_payroll_actions.effective_date%type;
1748 l_business_group_id pay_payroll_actions.business_group_id%type;
1749 total_no_fields number;
1750 l_payroll_id pay_payroll_actions.payroll_id%type;
1751 l_person_id per_people_f.person_id%TYPE;
1752 l_assignment_amend pay_assignment_actions.assignment_action_id%TYPE;
1753
1754
1755 cursor cur_person_id(p_assignment_action_id number) is
1756 select
1757 paf.person_id person_id
1758 from
1759 pay_assignment_actions paa,
1760 per_assignments_f paf
1761 where
1762 paa.assignment_action_id = p_assignment_action_id and
1763 paa.assignment_id = paf.assignment_id and
1764 l_effective_date between paf.effective_start_date and
1765 paf.effective_end_date;
1766
1767 cursor cur_pai is
1768 select
1769 pai.locking_action_id
1770 from
1771 pay_action_interlocks pai
1772 where
1773 pai.locked_action_id = l_assignment_amend;
1774
1775 l_locking_action_id pay_action_interlocks.locking_action_id%TYPE;
1776
1777 /* original
1778 cursor cur_employer_info(l_tax_unit_id number) is
1779 select hctu.name name,
1780 hctu.address_line_1 address_line_1,
1781 hctu.address_line_2 address_line_2,
1782 hctu.address_line_3 address_line_3,
1783 hctu.province province,
1784 hctu.country country,
1785 hctu.postal_code postal_code
1786 from hr_ca_tax_units_v hctu
1787 where hctu.tax_unit_id=l_tax_unit_id;
1788 */
1789
1790 cursor cur_employer_info(l_tax_unit_id number) is
1791 select nvl(hoi.org_information9,hctu.name) name,
1792 hctu.address_line_1 address_line_1,
1793 hctu.address_line_2 address_line_2,
1794 hctu.address_line_3 address_line_3,
1795 hctu.town_or_city city,
1796 hctu.province province,
1797 hctu.country country,
1798 hctu.postal_code postal_code,
1799 hctu.telephone_number_1 telephone
1800 from hr_ca_tax_units_v hctu,
1801 hr_organization_information hoi
1802 where hctu.tax_unit_id=l_tax_unit_id
1803 and hoi.organization_id = l_tax_unit_id
1804 and hoi.org_information_context = 'Canada Employer Identification';
1805
1806
1807 cursor cur_employee_info is
1808 select people.first_name first_name,
1809 people.last_name last_name,
1810 people.national_identifier social_insurance_number,
1811 people.middle_names middle_names,
1812 decode (people.correspondence_language, 'FRC','F','E') correspondence_language
1813 from
1814 per_all_people_f people
1815 , per_person_types ptype
1816 , per_phones phone
1817 , fnd_sessions ses
1818 , hr_lookups a
1819 , hr_lookups c
1820 , hr_lookups d
1821 , hr_lookups e
1822 , hr_lookups f
1823 , hr_lookups g
1824 , hr_lookups h
1825 , hr_lookups i
1826 , per_all_assignments_f ASSIGN
1827 where l_effective_date BETWEEN
1828 ASSIGN.effective_start_date
1829 AND ASSIGN.effective_end_date
1830 and ASSIGN.assignment_id = l_assignment_id
1831 and PEOPLE.person_id = ASSIGN.person_id
1832 and l_effective_date BETWEEN
1833 PEOPLE.effective_start_date
1834 AND PEOPLE.effective_end_date
1835 and PTYPE.person_type_id = PEOPLE.person_type_id
1836 and PHONE.parent_id (+) = PEOPLE.person_id
1837 AND PHONE.parent_table (+)= 'PER_ALL_PEOPLE_F'
1838 and PHONE.phone_type (+)= 'W1'
1839 AND l_effective_date
1840 BETWEEN NVL(PHONE.date_from(+),l_effective_date)
1841 AND NVL(PHONE.date_to(+),l_effective_date)
1842 and a.lookup_type = 'YES_NO'
1843 and a.lookup_code = nvl(PEOPLE.current_applicant_flag,'N')
1844 and a.application_id = 800
1845 and c.lookup_type = 'YES_NO'
1846 and c.lookup_code = nvl(PEOPLE.current_employee_flag,'N')
1847 and c.application_id = 800
1848 and d.lookup_type = 'YES_NO'
1849 and d.lookup_code = nvl(PEOPLE.registered_disabled_flag,'N')
1850 and d.application_id = 800
1851 and e.lookup_type (+)= 'HOME_OFFICE'
1852 and e.lookup_code (+)= PEOPLE.expense_check_send_to_address
1853 and e.application_id (+)= 800
1854 and f.lookup_type (+)= 'MAR_STATUS'
1855 and f.lookup_code (+)= PEOPLE.marital_status
1856 and f.application_id (+)= 800
1857 and g.lookup_type (+)= 'NATIONALITY'
1858 and g.lookup_code (+)= PEOPLE.nationality
1859 and g.application_id (+)= 800
1860 and h.lookup_type (+)= 'SEX'
1861 and h.lookup_code (+)= PEOPLE.sex
1862 and h.application_id (+)= 800
1863 and i.lookup_type (+)= 'TITLE'
1864 and i.lookup_code (+)= PEOPLE.title
1865 and i.application_id (+)= 800
1866 and SES.session_id = USERENV('SESSIONID');
1867
1868 cursor cur_employee_address_info is
1869 select addr.address_line1 address_line_1,
1870 addr.address_line2 address_line_2,
1871 addr.address_line3 address_line_3,
1872 addr.town_or_city city,
1873 addr.region_1 province,
1874 addr.country country,
1875 addr.postal_code postal_code,
1876 addr.telephone_number_1 telephone_number
1877 from per_all_assignments_f assign,
1878 per_addresses addr
1879 where assign.assignment_id = l_assignment_id
1880 and l_effective_date BETWEEN
1881 assign.effective_start_date
1882 AND assign.effective_end_date
1883 and assign.person_id = addr.person_id(+)
1884 and addr.primary_flag(+) = 'Y'
1885 and l_effective_date
1886 BETWEEN nvl(ADDR.date_from,l_effective_date)
1887 AND nvl(ADDR.date_to,l_effective_date);
1888
1889 cursor cur_employee_hire_date is
1890 select max(service.date_start) hire_date
1891 from per_periods_of_service service,
1892 per_assignments_f asg
1893 where asg.assignment_id = l_assignment_id
1894 and l_effective_date BETWEEN
1895 asg.effective_start_date
1896 AND asg.effective_end_date
1897 and asg.person_id = service.person_id(+)
1898 and service.date_start <= l_effective_date;
1899
1900 -- The assignment_number will be displayed
1901 -- as Employer's payroll reference number in ROE.
1902
1903 cursor cur_asg_number is
1904 select paf.assignment_number asg_number
1905 from per_assignments_f paf
1906 where l_effective_date between paf.effective_start_date
1907 AND paf.effective_end_date
1908 and paf.assignment_id = l_assignment_id;
1909
1910 --
1911 -- Revenue Canada Business Number
1912 --
1913 --
1914 cursor cur_business_number(l_tax_unit_id number ) is
1915 select hoi.org_information1 business_number
1916 from hr_organization_information hoi
1917 where l_tax_unit_id = hoi.organization_id
1918 and ltrim(rtrim(hoi.org_information_context)) =
1919 'Canada Employer Identification';
1920
1921 --
1922 -- ROE specific information in the payroll form
1923 --
1924
1925 cursor cur_payroll_form(p_pay_period_end_date date) is
1926 select people1.full_name contact_person,
1927 ppf.prl_information2 contact_phone_number,
1928 people2.full_name roe_issuer,
1929 ppf.prl_information4 correspondence_language,
1930 people1.first_name contact_first_name,
1931 people1.middle_names contact_middle_names,
1932 people1.last_name contact_last_name
1933 from pay_payrolls_f ppf,
1934 per_people_f people1,
1935 per_people_f people2
1936 where ppf.payroll_id = l_payroll_id
1937 and ppf.prl_information_category = 'CA'
1938 and ppf.prl_information1 = people1.person_id(+)
1939 and ppf.prl_information3 = people2.person_id(+)
1940 and p_pay_period_end_date BETWEEN nvl(people1.effective_start_date,
1941 p_pay_period_end_date)
1942 AND nvl(people1.effective_end_date,p_pay_period_end_date)
1943 and p_pay_period_end_date BETWEEN nvl(people2.effective_start_date,
1944 p_pay_period_end_date)
1945 AND nvl(people2.effective_end_date,p_pay_period_end_date)
1946 and p_pay_period_end_date BETWEEN nvl(ppf.effective_start_date,
1947 p_pay_period_end_date)
1948 AND nvl(ppf.effective_end_date,p_pay_period_end_date);
1949 --
1950 -- Assignment Job
1951 --
1952
1953 cursor cur_asg_job is
1954 select
1955 job.name name
1956 from
1957 per_all_assignments_f assign
1958 , per_grades grade
1959 , per_jobs job
1960 , per_assignment_status_types ast
1961 , pay_all_payrolls_f payroll
1962 , per_time_periods timep
1963 , hr_locations loc
1964 , hr_all_organization_units org
1965 , pay_people_groups grp
1966 , per_all_vacancies vac
1967 , per_all_people_f people1
1968 , per_all_people_f people2
1969 , per_all_positions pos1
1970 , per_all_positions pos2
1971 , per_all_positions pos3
1972 , hr_lookups hr1
1973 , hr_lookups hr2
1974 , hr_lookups hr3
1975 , hr_lookups hr4
1976 , hr_lookups hr5
1977 , hr_lookups hr6
1978 , hr_lookups hr7
1979 , fnd_lookups fnd1
1980 , fnd_lookups fnd2
1981 where
1982 l_effective_date BETWEEN assign.effective_start_date
1983 AND assign.effective_end_date
1984 and assign.assignment_id = l_assignment_id
1985 and grade.grade_id (+)= assign.grade_id
1986 and job.job_id (+)= assign.job_id
1987 and ast.assignment_status_type_id = assign.assignment_status_type_id
1988 and payroll.payroll_id (+)= assign.payroll_id
1989 and l_effective_date between
1990 nvl (payroll.effective_start_date,l_effective_date)
1991 and nvl (payroll.effective_end_date,l_effective_date)
1992 and timep.payroll_id (+)= assign.payroll_id
1993 and l_effective_date between nvl (timep.start_date(+), l_effective_date)
1994 and nvl (timep.end_date(+), l_effective_date)
1995 and loc.location_id (+)= assign.location_id
1996 and org.organization_id = assign.organization_id
1997 and grp.people_group_id (+)= assign.people_group_id
1998 and vac.vacancy_id (+)= assign.vacancy_id
1999 and hr1.lookup_code = assign.assignment_type
2000 and hr1.lookup_type = 'EMP_APL'
2001 and hr2.lookup_code (+)= assign.probation_unit
2002 and hr2.lookup_type (+)= 'UNITS'
2003 and hr3.lookup_code (+)= assign.frequency
2004 and hr3.lookup_type (+)= 'FREQUENCY'
2005 and fnd1.lookup_code = assign.primary_flag
2006 and fnd1.lookup_type = 'YES_NO'
2007 and fnd2.lookup_code (+)= assign.manager_flag
2008 and fnd2.lookup_type (+)= 'YES_NO'
2009 and people1.person_id (+)= assign.recruiter_id
2010 and people2.person_id (+)= assign.supervisor_id
2011 and pos1.position_id (+)= assign.position_id
2012 and hr4.lookup_code (+)= pos1.frequency
2013 and hr4.lookup_type (+)= 'FREQUENCY'
2014 and hr5.lookup_code (+)= assign.employment_category
2015 and hr5.lookup_type (+)= 'EMP_CAT'
2016 and hr6.lookup_code (+)= assign.perf_review_period_frequency
2017 and hr6.lookup_type (+)= 'FREQUENCY'
2018 and hr7.lookup_code (+)= assign.sal_review_period_frequency
2019 and hr7.lookup_type (+)= 'FREQUENCY'
2020 and pos2.position_id (+)= pos1.successor_position_id
2021 and pos3.position_id (+)= pos1.relief_position_id;
2022
2023 --
2024 -- Final pay period ending date
2025 --
2026
2027 cursor cur_final_pay_period_date(p_pay_period_end_date date) is
2028 select min(ptp.start_date) start_date,
2029 max(ptp.end_date) end_date
2030 from pay_payroll_actions ppa,
2031 pay_assignment_actions paa,
2032 per_time_periods ptp,
2033 per_time_period_types tptype
2034 where paa.assignment_id = l_assignment_id
2035 and paa.tax_unit_id = l_tax_unit_id
2036 and paa.payroll_action_id = ppa.payroll_action_id
2037 and ppa.payroll_id = l_payroll_id
2038 and ppa.action_type in ('R','Q')
2039 and ppa.date_earned <= p_pay_period_end_date
2040 and ppa.payroll_id = ptp.payroll_id
2041 and p_pay_period_end_date BETWEEN ptp.start_date
2042 AND ptp.end_date
2043 and ptp.period_type = tptype.period_type;
2044
2045
2046 cursor cur_last_pay_date is
2047 select max(ppa.date_earned) last_day_paid
2048 from pay_payroll_actions ppa,
2049 pay_assignment_actions paa
2050 where paa.assignment_id = l_assignment_id
2051 and paa.payroll_action_id = ppa.payroll_action_id
2052 and ppa.action_type in ('R','Q')
2053 and paa.action_status = 'C'
2054 and ppa.date_earned <= l_effective_date
2055 and ppa.payroll_id = l_payroll_id
2056 and paa.tax_unit_id = l_tax_unit_id;
2057
2058
2059 l_value ff_archive_items.value%type;
2060 l_user_entity_id ff_user_entities.user_entity_id%type;
2061 l_archive_item_id ff_archive_items.archive_item_id%type;
2062 l_object_version_number number(9);
2063 l_some_warning boolean;
2064
2065 l_prev_roe_date date;
2066 l_roe_date date;
2067 tab_period_total pay_ca_roe_ei_pkg.t_large_number_table;
2068 l_total_insurable number;
2069 ret varchar2(10);
2070 l_total_type varchar2(15);
2071 l_no_of_periods number;
2072 l_period_type varchar2(20);
2073 l_recall_date date;
2074 l_recall_date1 date;
2075 l_roe_reason varchar2(150);
2076 l_roe_comment varchar2(150);
2077 l_last_day_paid date;
2078 l_last_day_paid1 date;
2079 l_last_day_paid2 date;
2080 l_legislative_parameters pay_payroll_actions.legislative_parameters%TYPE; l_assignment_set_id hr_assignment_sets.assignment_set_id%TYPE;
2081
2082 CURSOR cur_asg_set_name IS
2083 SELECT
2084 assignment_set_name
2085 FROM
2086 hr_assignment_sets
2087 WHERE assignment_set_id = l_assignment_set_id
2088 AND business_group_id = l_business_group_id;
2089
2090 l_asg_set_name hr_assignment_sets.assignment_set_name%TYPE;
2091 l_first_day_worked date;
2092 l_final_pay_period_end_date date;
2093 l_final_pay_period_start_date date;
2094 l_roe_contact_person per_people_f.full_name%TYPE;
2095 l_roe_contact_first_name per_people_f.first_name%TYPE;
2096 l_roe_contact_middle_names per_people_f.middle_names%TYPE;
2097 l_roe_contact_last_name per_people_f.last_name%TYPE;
2098 l_roe_contact_phone_number pay_payrolls_f.prl_information2%TYPE;
2099 l_roe_issuer pay_payrolls_f.prl_information4%TYPE;
2100 l_roe_correspondence_language per_people_f.full_name%TYPE;
2101 l_pay_period_end_date date;
2102 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
2103 l_latest_aaid pay_assignment_actions.assignment_action_id%TYPE
2104 := 0;
2105 l_latest_aaid_after_term pay_assignment_actions.assignment_action_id%TYPE
2106 := 0;
2107 l_period_end_date_after_term date;
2108 l_period_start_date_after_term date;
2109
2110 cursor cur_latest_aaid(l_pay_period_start_date date,
2111 l_pay_period_end_date date,
2112 p_tax_unit_id number) is
2113 select
2114 max(paa.assignment_action_id)
2115 from
2116 pay_assignment_actions paa,
2117 pay_payroll_actions ppa,
2118 per_assignments_f paf
2119 where
2120 paa.assignment_id = l_assignment_id and
2121 paa.tax_unit_id = p_tax_unit_id and
2122 paa.payroll_action_id = ppa.payroll_action_id and
2123 ppa.action_type in ('R','Q','V','B','F') and
2124 ppa.action_status = 'C' and
2125 ppa.date_earned between
2126 l_pay_period_start_date and
2127 l_pay_period_end_date and
2128 paa.assignment_id = paf.assignment_id and
2129 l_pay_period_end_date between paf.effective_start_date and
2130 paf.effective_end_date and
2131 paf.payroll_id = l_payroll_id;
2132
2133 l_temp_value1 number := 0;
2134 l_temp_code1 varchar2(1);
2135 l_temp_value2 number := 0;
2136 l_temp_code2 varchar2(1);
2137 l_temp_value3 number := 0;
2138 l_temp_code3 varchar2(1);
2139 l_term_or_abs_flag varchar2(1);
2140 l_term_or_abs varchar2(1);
2141 l_tax_group_id hr_organization_information.org_information4%TYPE;
2142 l_t4a_gre varchar2(1);
2143
2144 cursor cur_tax_group is
2145 select
2146 org_information4
2147 from
2148 hr_organization_information
2149 where
2150 organization_id = l_tax_unit_id and
2151 org_information_context = 'Canada Employer Identification';
2152
2153 cursor cur_17_gres(p_tax_group_id varchar2) is
2154 select
2155 organization_id tax_unit_id
2156 from
2157 hr_organization_information
2158 where
2159 org_information4 = p_tax_group_id and
2160 org_information_context = 'Canada Employer Identification' and
2161 org_information5 in ('T4A/RL1','T4A/RL2') and
2162 l_t4a_gre = 'Y'
2163 union
2164 select
2165 l_tax_unit_id tax_unit_id
2166 from
2167 dual;
2168
2169 l_serial_number pay_assignment_actions.serial_number%TYPE;
2170
2171 begin
2172
2173 hr_utility.trace('Archive data');
2174
2175 open cur_archive_info;
2176 fetch cur_archive_info
2177 into l_assignment_id,
2178 l_tax_unit_id,
2179 l_effective_date,
2180 l_business_group_id,
2181 l_payroll_id,
2182 l_legislative_parameters,
2183 l_t4a_gre;
2184 close cur_archive_info;
2185
2186 l_person_id :=
2187 pycadar_pkg.get_parameter('PERSON_ID',l_legislative_parameters);
2188 l_assignment_set_id :=
2189 pycadar_pkg.get_parameter('ASSIGNMENT_SET_ID',l_legislative_parameters);
2190 l_assignment_amend :=
2191 pycadar_pkg.get_parameter('ASSIGNMENT_ID',l_legislative_parameters);
2192
2193 if l_assignment_set_id is not null then
2194
2195 open cur_person_id(p_assactid);
2196 fetch
2197 cur_person_id
2198 into
2199 l_person_id;
2200 close cur_person_id;
2201
2202 open cur_asg_set_name;
2203 fetch cur_asg_set_name
2204 into l_asg_set_name;
2205 close cur_asg_set_name;
2206
2207 end if;
2208
2209 l_prev_roe_date :=
2210 fnd_date.canonical_to_date(archive_value(p_assactid,'PREV_ROE_DATE'));
2211 l_roe_date :=
2212 fnd_date.canonical_to_date(archive_value(p_assactid,'ROE_DATE'));
2213 l_assignment_id := archive_value(p_assactid,'ROE_ASSIGNMENT_ID');
2214 l_tax_unit_id := archive_value(p_assactid,'ROE_GRE_ID');
2215 l_payroll_id := archive_value(p_assactid,'ROE_PAYROLL_ID');
2216
2217
2218 l_effective_date := l_roe_date;
2219
2220 -- The get_date function is called to check whether the
2221 -- employee is terminated or not. l_recall_date will be
2222 -- null if the employee is terminated.
2223
2224 l_last_day_paid := get_date(l_person_id,
2225 l_assignment_id,
2226 l_business_group_id,
2227 l_effective_date,
2228 l_recall_date,
2229 l_roe_reason,
2230 l_roe_comment,
2231 l_term_or_abs_flag,
2232 l_term_or_abs);
2233
2234 -- If the ROE Type is LOA, the last day paid
2235 -- is one day prior to LOA Start Date.
2236 IF l_term_or_abs = 'A' then
2237 l_last_day_paid := get_working_date(l_business_group_id,
2238 l_assignment_id,
2239 l_last_day_paid,
2240 'P');
2241 END IF;
2242
2243 open cur_prd_end_date(l_payroll_id,
2244 l_last_day_paid);
2245 fetch cur_prd_end_date
2246 into l_pay_period_end_date;
2247
2248 if cur_prd_end_date%NOTFOUND then
2249 l_pay_period_end_date := l_effective_date;
2250 end if;
2251
2252 close cur_prd_end_date;
2253
2254 -- if the check_retry_amend has returned false in action_creation
2255 -- the following local variables will have NULL so we just raise
2256 -- an error so the assignent action has error status, the error
2257 -- log will be found in the log/request file.
2258
2259 if ((l_roe_date IS NOT NULL) AND
2260 (l_assignment_id is not null) AND
2261 (l_tax_unit_id is not null) AND
2262 (l_payroll_id is not null) ) then
2263
2264 total_no_fields := 5;
2265
2266 for cur_rec in cur_employee_info loop
2267
2268 hr_utility.trace('cur_rec.first_name = '|| cur_rec.first_name);
2269 hr_utility.trace('cur_rec.middle_name = '|| cur_rec.middle_names);
2270 hr_utility.trace('cur_rec.last_name = '|| cur_rec.last_name);
2271
2272 for cur_field in 1..total_no_fields loop
2273
2274 if cur_field = 1 then
2275
2276 l_value := cur_rec.first_name;
2277 l_user_entity_id := get_user_entity('ROE_PER_FIRST_NAME');
2278
2279 elsif cur_field = 2 then
2280
2281 l_value := cur_rec.last_name;
2282 l_user_entity_id := get_user_entity('ROE_PER_LAST_NAME');
2283
2284 elsif cur_field = 3 then
2285
2286 l_value := cur_rec.social_insurance_number;
2287 l_user_entity_id := get_user_entity('ROE_PER_SOCIAL_INSURANCE_NUMBER');
2288
2289 elsif cur_field = 4 then
2290
2291 l_value := cur_rec.middle_names;
2292 l_user_entity_id := get_user_entity('ROE_PER_MIDDLE_NAME');
2293
2294 elsif cur_field = 5 then
2295
2296 l_value := cur_rec.correspondence_language;
2297 l_user_entity_id := get_user_entity('ROE_EMPLOYEE_CORRESPONDENCE_LANGUAGE');
2298
2299 end if;
2300
2301 hr_utility.trace('user entity id = '|| to_char(l_user_entity_id));
2302 hr_utility.trace('l value = '|| l_value);
2303
2304 ff_archive_api.create_archive_item(
2305 p_archive_item_id => l_archive_item_id,
2306 p_user_entity_id => l_user_entity_id,
2307 p_archive_value => l_value,
2308 p_archive_type => 'AAP',
2309 p_action_id => p_assactid,
2310 p_legislation_code => 'CA',
2311 p_object_version_number => l_object_version_number,
2312 p_some_warning => l_some_warning);
2313
2314 end loop; -- total_no_fields
2315
2316 end loop; -- cur_employee_info
2317
2318 for i in cur_employer_info(l_tax_unit_id) loop
2319
2320 for j in 1..9 loop
2321
2322 if j = 1 then
2323
2324 l_value := i.name;
2325 l_user_entity_id := get_user_entity('ROE_TAX_UNIT_NAME');
2326
2327 elsif j = 2 then
2328
2329 l_value := i.address_line_1;
2330 l_user_entity_id := get_user_entity('ROE_TAX_UNIT_ADDRESS_LINE_1');
2331
2332 elsif j = 3 then
2333
2334 l_value := i.address_line_2;
2335 l_user_entity_id := get_user_entity('ROE_TAX_UNIT_ADDRESS_LINE_2');
2336
2337 elsif j = 4 then
2338
2339 l_value := i.address_line_3;
2340 l_user_entity_id := get_user_entity('ROE_TAX_UNIT_ADDRESS_LINE_3');
2341
2342 elsif j = 5 then
2343
2344 l_value := i.province;
2345 l_user_entity_id := get_user_entity('ROE_TAX_UNIT_PROVINCE');
2346
2347 elsif j = 6 then
2348
2349 l_value := i.country;
2350 l_user_entity_id := get_user_entity('ROE_TAX_UNIT_COUNTRY');
2351
2352
2353 elsif j = 7 then
2354
2355 l_value := i.postal_code;
2356 l_user_entity_id := get_user_entity('ROE_TAX_UNIT_POSTAL_CODE');
2357
2358 elsif j = 8 then
2359
2360 l_value := i.city;
2361 l_user_entity_id := get_user_entity('ROE_TAX_UNIT_CITY');
2362
2363 elsif j = 9 then
2364
2365 l_value := i.telephone;
2366 l_user_entity_id := get_user_entity('ROE_TAX_UNIT_PHONE_NUMBER');
2367
2368 end if;
2369
2370 hr_utility.trace('user entity id = '|| to_char(l_user_entity_id));
2371 hr_utility.trace('l value = '|| l_value);
2372
2373 ff_archive_api.create_archive_item(
2374 p_archive_item_id => l_archive_item_id,
2375 p_user_entity_id => l_user_entity_id,
2376 p_archive_value => l_value,
2377 p_archive_type => 'AAP',
2378 p_action_id => p_assactid,
2379 p_legislation_code => 'CA',
2380 p_object_version_number => l_object_version_number,
2381 p_some_warning => l_some_warning);
2382
2383 end loop;
2384
2385 end loop;
2386
2387
2388 total_no_fields := 8;
2389
2390 for cur_rec in cur_employee_address_info loop
2391
2392 for cur_field in 1..total_no_fields loop
2393
2394 if cur_field = 1 then
2395
2396 l_value := cur_rec.address_line_1;
2397 l_user_entity_id := get_user_entity('ROE_PER_ADDRESS_LINE_1');
2398
2399 elsif cur_field = 2 then
2400
2401 l_value := cur_rec.address_line_2;
2402 l_user_entity_id := get_user_entity('ROE_PER_ADDRESS_LINE_2');
2403
2404 elsif cur_field = 3 then
2405
2406 l_value := cur_rec.address_line_3;
2407 l_user_entity_id := get_user_entity('ROE_PER_ADDRESS_LINE_3');
2408
2409 elsif cur_field = 4 then
2410
2411 l_value := cur_rec.province;
2412 l_user_entity_id := get_user_entity('ROE_PER_PROVINCE');
2413
2414 elsif cur_field = 5 then
2415
2416 l_value := cur_rec.country;
2417 l_user_entity_id := get_user_entity('ROE_PER_COUNTRY');
2418
2419
2420 elsif cur_field = 6 then
2421
2422 l_value := cur_rec.postal_code;
2423 l_user_entity_id := get_user_entity('ROE_PER_POSTAL_CODE');
2424
2425 elsif cur_field = 7 then
2426
2427 -- l_value := cur_rec.telephone_number;
2428 -- as per discussed with lucy and lewis putting null value
2429 l_value := null;
2430 l_user_entity_id := get_user_entity('ROE_PER_TELEPHONE_NUMBER');
2431
2432 elsif cur_field = 8 then
2433
2434 l_value := cur_rec.city;
2435 l_user_entity_id := get_user_entity('ROE_PER_CITY');
2436
2437 end if;
2438
2439 hr_utility.trace('per user entity id = '|| to_char(l_user_entity_id));
2440 hr_utility.trace('per l value = '|| l_value);
2441
2442 ff_archive_api.create_archive_item(
2443 p_archive_item_id => l_archive_item_id,
2444 p_user_entity_id => l_user_entity_id,
2445 p_archive_value => l_value,
2446 p_archive_type => 'AAP',
2447 p_action_id => p_assactid,
2448 p_legislation_code => 'CA',
2449 p_object_version_number => l_object_version_number,
2450 p_some_warning => l_some_warning);
2451
2452 end loop; --total_no_fields
2453
2454 end loop; -- cur_employee_address_info
2455
2456 -- This loop will archive records which
2457 -- are supposed to have null values and
2458 -- will be populated by the archive view
2459 -- form. The total number of items that
2460 -- will be archived 11
2461
2462 total_no_fields := 11;
2463
2464 l_value := null;
2465
2466 tab_user_entity_name(1) := 'ROE_BOX_17B_DATE1';
2467 tab_user_entity_name(2) := 'ROE_BOX_17B_AMOUNT1';
2468 tab_user_entity_name(3) := 'ROE_BOX_17B_DATE2';
2469 tab_user_entity_name(4) := 'ROE_BOX_17B_AMOUNT2';
2470 tab_user_entity_name(5) := 'ROE_BOX_17B_DATE3';
2471 tab_user_entity_name(6) := 'ROE_BOX_17B_AMOUNT3';
2472 tab_user_entity_name(7) := 'ROE_BOX_19_PAYMENT_START_DATE';
2473 tab_user_entity_name(8) := 'ROE_BOX_19_PAYMENT_AMOUNT';
2474 tab_user_entity_name(9) := 'ROE_BOX_19_DAY_WEEK';
2475 tab_user_entity_name(10) := 'ROE_MANUAL';
2476 tab_user_entity_name(11) := 'ROE_INCLUDE_EXCLUDE';
2477
2478 for cur_rec in 1..total_no_fields loop
2479
2480 l_user_entity_id := get_user_entity(tab_user_entity_name(cur_rec));
2481
2482 hr_utility.trace('user entity id = '|| to_char(l_user_entity_id));
2483 hr_utility.trace('l value = '|| l_value);
2484
2485 ff_archive_api.create_archive_item(
2486 p_archive_item_id => l_archive_item_id,
2487 p_user_entity_id => l_user_entity_id,
2488 p_archive_value => l_value,
2489 p_archive_type => 'AAP',
2490 p_action_id => p_assactid,
2491 p_legislation_code => 'CA',
2492 p_object_version_number => l_object_version_number,
2493 p_some_warning => l_some_warning);
2494
2495 end loop; -- total_no_fields
2496
2497 open cur_asg_number;
2498 fetch cur_asg_number
2499 into l_value;
2500
2501 if cur_asg_number%NOTFOUND then
2502 l_value := NULL;
2503 end if;
2504
2505 close cur_asg_number;
2506
2507 l_user_entity_id := get_user_entity('ROE_ASG_NUMBER');
2508
2509 hr_utility.trace('ROE_ASG_NUBER id = '|| to_char(l_user_entity_id));
2510 hr_utility.trace('ROE_ASG_NUBER value = '|| l_value);
2511
2512 ff_archive_api.create_archive_item(
2513 p_archive_item_id => l_archive_item_id,
2514 p_user_entity_id => l_user_entity_id,
2515 p_archive_value => l_value,
2516 p_archive_type => 'AAP',
2517 p_action_id => p_assactid,
2518 p_legislation_code => 'CA',
2519 p_object_version_number => l_object_version_number,
2520 p_some_warning => l_some_warning);
2521
2522
2523 for cur_rec in cur_business_number(l_tax_unit_id) loop
2524
2525 l_value := cur_rec.business_number;
2526 l_user_entity_id := get_user_entity('ROE_CANADA_EMPLOYER_IDENTIFICATION_ORG_BUSINESS_NUMBER');
2527
2528 hr_utility.trace('user entity id = '|| to_char(l_user_entity_id));
2529 hr_utility.trace('l value = '|| l_value);
2530
2531 ff_archive_api.create_archive_item(
2532 p_archive_item_id => l_archive_item_id,
2533 p_user_entity_id => l_user_entity_id,
2534 p_archive_value => l_value,
2535 p_archive_type => 'AAP',
2536 p_action_id => p_assactid,
2537 p_legislation_code => 'CA',
2538 p_object_version_number => l_object_version_number,
2539 p_some_warning => l_some_warning);
2540
2541 end loop; -- cur_business_number
2542
2543
2544 total_no_fields := 7;
2545
2546 open cur_payroll_form(l_pay_period_end_date);
2547 fetch cur_payroll_form
2548 into l_roe_contact_person,
2549 l_roe_contact_phone_number,
2550 l_roe_issuer,
2551 l_roe_correspondence_language,
2552 l_roe_contact_first_name,
2553 l_roe_contact_middle_names,
2554 l_roe_contact_last_name;
2555
2556 if cur_payroll_form%NOTFOUND then
2557 l_roe_contact_person := NULL;
2558 l_roe_contact_phone_number := NULL;
2559 l_roe_issuer := NULL;
2560 l_roe_correspondence_language := NULL;
2561 l_roe_contact_first_name := NULL;
2562 l_roe_contact_middle_names := NULL;
2563 l_roe_contact_last_name := NULL;
2564 end if;
2565
2566 close cur_payroll_form;
2567
2568 for cur_field in 1..total_no_fields loop
2569
2570 if cur_field = 1 then
2571
2572 l_value := l_roe_contact_person;
2573 l_user_entity_id := get_user_entity('ROE_CONTACT_PERSON');
2574
2575 elsif cur_field = 2 then
2576
2577 l_value := l_roe_contact_phone_number;
2578 l_user_entity_id := get_user_entity('ROE_CONTACT_PHONE_NUMBER');
2579
2580 elsif cur_field = 3 then
2581
2582 l_value := l_roe_issuer;
2583 l_user_entity_id := get_user_entity('ROE_ISSUER');
2584
2585 elsif cur_field = 4 then
2586
2587 l_value := l_roe_correspondence_language;
2588 l_user_entity_id := get_user_entity('ROE_PER_CORRESPONDENCE_LANGUAGE');
2589
2590 elsif cur_field = 5 then
2591
2592 l_value := l_roe_contact_first_name;
2593 l_user_entity_id := get_user_entity('ROE_CONTACT_PER_FIRST_NAME');
2594
2595 elsif cur_field = 6 then
2596
2597 l_value := l_roe_contact_middle_names;
2598 l_user_entity_id := get_user_entity('ROE_CONTACT_PER_MIDDLE_NAMES');
2599
2600 elsif cur_field = 7 then
2601
2602 l_value := l_roe_contact_last_name;
2603 l_user_entity_id := get_user_entity('ROE_CONTACT_PER_LAST_NAME');
2604
2605 end if;
2606
2607 hr_utility.trace('user entity id = '|| to_char(l_user_entity_id));
2608 hr_utility.trace('l value = '|| l_value);
2609
2610 ff_archive_api.create_archive_item(
2611 p_archive_item_id => l_archive_item_id,
2612 p_user_entity_id => l_user_entity_id,
2613 p_archive_value => l_value,
2614 p_archive_type => 'AAP',
2615 p_action_id => p_assactid,
2616 p_legislation_code => 'CA',
2617 p_object_version_number => l_object_version_number,
2618 p_some_warning => l_some_warning);
2619
2620 end loop; -- total_no_fields
2621
2622
2623 for cur_rec in cur_asg_job loop
2624
2625 l_value := cur_rec.name;
2626 l_user_entity_id := get_user_entity('ROE_ASG_JOB');
2627
2628 hr_utility.trace('user entity id = '|| to_char(l_user_entity_id));
2629 hr_utility.trace('l value = '|| l_value);
2630
2631 ff_archive_api.create_archive_item(
2632 p_archive_item_id => l_archive_item_id,
2633 p_user_entity_id => l_user_entity_id,
2634 p_archive_value => l_value,
2635 p_archive_type => 'AAP',
2636 p_action_id => p_assactid,
2637 p_legislation_code => 'CA',
2638 p_object_version_number => l_object_version_number,
2639 p_some_warning => l_some_warning);
2640
2641 end loop;
2642
2643 open cur_final_pay_period_date(l_pay_period_end_date);
2644 fetch cur_final_pay_period_date
2645 into l_final_pay_period_start_date,
2646 l_final_pay_period_end_date;
2647
2648 if cur_final_pay_period_date%NOTFOUND then
2649 l_final_pay_period_start_date := NULL;
2650 l_final_pay_period_end_date := NULL;
2651 else
2652 l_value := to_char(l_final_pay_period_end_date,'YYYY/MM/DD HH24:MI:SS');
2653 end if;
2654
2655 close cur_final_pay_period_date;
2656
2657 l_user_entity_id := get_user_entity('ROE_FINAL_PAY_PERIOD_ENDING_DATE');
2658
2659 hr_utility.trace('ROE_FINAL_PAY_PERIOD_ENDING_DATE entity id = '
2660 || to_char(l_user_entity_id));
2661 hr_utility.trace('ROE_FINAL_PAY_PERIOD_ENDING_DATE value = '|| l_value);
2662 hr_utility.trace('l_final_pay_period_start_date = ' ||
2663 to_char(l_final_pay_period_start_date));
2664
2665 ff_archive_api.create_archive_item(
2666 p_archive_item_id => l_archive_item_id,
2667 p_user_entity_id => l_user_entity_id,
2668 p_archive_value => l_value,
2669 p_archive_type => 'AAP',
2670 p_action_id => p_assactid,
2671 p_legislation_code => 'CA',
2672 p_object_version_number => l_object_version_number,
2673 p_some_warning => l_some_warning);
2674
2675
2676 -- The get_date function is called to check whether the
2677 -- employee is terminated or not. l_recall_date will be
2678 -- null if the employee is terminated.
2679
2680 l_last_day_paid := get_date(l_person_id,
2681 l_assignment_id,
2682 l_business_group_id,
2683 l_effective_date,
2684 l_recall_date,
2685 l_roe_reason,
2686 l_roe_comment,
2687 l_term_or_abs_flag,
2688 l_term_or_abs);
2689
2690 -- if the employee has got a prev ROE and
2691 -- has come back from LOA then the start date would be
2692 -- Next working day after the employee has returned otherwise
2693 -- the hire date will be the start date.
2694
2695 if l_prev_roe_date is not null then
2696
2697 hr_utility.trace('l_prev_roe_date is not null ' ||
2698 to_char(l_prev_roe_date,'dd-mon-yy'));
2699
2700 -- if the prev ROE was used for LOA then the expected date
2701 -- of recall archived on the prev run would be the first day
2702 -- worked for this run.
2703 -- If the prev ROE was issued b'coz the employee was
2704 -- terminated then the latest hire date would be first day
2705 -- worked.
2706
2707 -- Commented for bug 3892425
2708 -- l_first_day_worked := func_expected_date_of_return(l_assignment_id,
2709 -- l_payroll_id,
2710 -- l_tax_unit_id);
2711
2712 -- Fix for Bug 3892425:
2713 -- To derive the first day worked after an employee returns from LOA
2714 -- fetch the absense end date
2715 -- for the person whose LOA start date is same as previous ROE date.
2716
2717 open cur_abs(l_person_id, l_prev_roe_date);
2718 fetch cur_abs into l_first_day_worked;
2719 close cur_abs;
2720
2721 if l_first_day_worked is not null then
2722
2723 l_first_day_worked := get_working_date(l_business_group_id,
2724 l_assignment_id,
2725 l_first_day_worked,
2726 'N');
2727 l_value := to_char(l_first_day_worked,'YYYY/MM/DD HH24:MI:SS');
2728
2729 else
2730
2731 /* --Fix for 6396412 (sapalani)
2732
2733 open cur_employee_hire_date;
2734 fetch cur_employee_hire_date
2735 into l_first_day_worked;
2736 close cur_employee_hire_date;
2737
2738 l_first_day_worked := get_working_date(l_business_group_id,
2739 l_assignment_id,
2740 l_prev_roe_date,
2741 'N');
2742 l_value := to_char(l_first_day_worked,'YYYY/MM/DD HH24:MI:SS');
2743
2744 -- End 6396412
2745 */
2746 --Fix for 8210261 (aneghosh)
2747 open cur_employee_hire_date;
2748 fetch cur_employee_hire_date
2749 into l_first_day_worked;
2750 close cur_employee_hire_date;
2751 if l_first_day_worked < get_working_date(l_business_group_id,
2752 l_assignment_id,
2753 l_prev_roe_date,
2754 'N')
2755 then
2756 l_first_day_worked := get_working_date(l_business_group_id,
2757 l_assignment_id,
2758 l_prev_roe_date,
2759 'N');
2760 end if;
2761 --End 8210261
2762 l_value := to_char(l_first_day_worked,'YYYY/MM/DD HH24:MI:SS');
2763 end if;
2764
2765 l_user_entity_id := get_user_entity('ROE_EMP_PER_HIRE_DATE');
2766
2767 hr_utility.trace('ROE_EMP_PER_HIRE_DATE = '|| to_char(l_user_entity_id));
2768 hr_utility.trace('First day worked = '|| l_value);
2769
2770 ff_archive_api.create_archive_item(
2771 p_archive_item_id => l_archive_item_id,
2772 p_user_entity_id => l_user_entity_id,
2773 p_archive_value => l_value,
2774 p_archive_type => 'AAP',
2775 p_action_id => p_assactid,
2776 p_legislation_code => 'CA',
2777 p_object_version_number => l_object_version_number,
2778 p_some_warning => l_some_warning);
2779
2780 else
2781
2782 hr_utility.trace('first day worked in hire date');
2783
2784 for cur_rec in cur_employee_hire_date loop
2785
2786 l_value := to_char(cur_rec.hire_date,'YYYY/MM/DD HH24:MI:SS');
2787 l_user_entity_id := get_user_entity('ROE_EMP_PER_HIRE_DATE');
2788
2789 hr_utility.trace('ROE_EMP_PER_HIRE_DATE = '|| to_char(l_user_entity_id));
2790 hr_utility.trace('First day worked = '|| l_value);
2791
2792 ff_archive_api.create_archive_item(
2793 p_archive_item_id => l_archive_item_id,
2794 p_user_entity_id => l_user_entity_id,
2795 p_archive_value => l_value,
2796 p_archive_type => 'AAP',
2797 p_action_id => p_assactid,
2798 p_legislation_code => 'CA',
2799 p_object_version_number => l_object_version_number,
2800 p_some_warning => l_some_warning);
2801
2802 end loop; -- cur_employee_hire_date
2803
2804 end if;
2805
2806 -- If the person has been terminated then the last date paid
2807 -- is actual termination date but if the employee has gone for
2808 -- LOA then the last date paid becomes one day before/ prev
2809 -- working date the actual LOA start date. l_recall_date
2810 -- will be null if the employee is terminated.
2811
2812 if l_term_or_abs = 'T' then
2813
2814 l_value := to_char(l_effective_date,'YYYY/MM/DD HH24:MI:SS');
2815 l_user_entity_id := get_user_entity('ROE_PAY_EARNED_END_DATE');
2816
2817 hr_utility.trace('ROE_PAY_EARNED_END_DATE id = '|| to_char(l_user_entity_id));
2818 hr_utility.trace('ROE_PAY_EARNED_END_DATE value = '|| l_value);
2819
2820 ff_archive_api.create_archive_item(
2821 p_archive_item_id => l_archive_item_id,
2822 p_user_entity_id => l_user_entity_id,
2823 p_archive_value => l_value,
2824 p_archive_type => 'AAP',
2825 p_action_id => p_assactid,
2826 p_legislation_code => 'CA',
2827 p_object_version_number => l_object_version_number,
2828 p_some_warning => l_some_warning);
2829
2830 else
2831
2832 -- If the employee has returned from the LOA and the return
2833 -- date is less than the final_pay_period_end_date then the
2834 -- last day paid is final_pay_period_ending_date else it
2835 -- is the day b4 LOA start date.
2836
2837 if l_recall_date <= l_final_pay_period_end_date then
2838
2839 hr_utility.trace('recall date is less than final period ending date');
2840 l_value := to_char(l_final_pay_period_end_date,'YYYY/MM/DD HH24:MI:SS');
2841
2842 else
2843
2844 hr_utility.trace('recall date is greater than final period ending date');
2845 l_last_day_paid1 := get_working_date(l_business_group_id,
2846 l_assignment_id,
2847 l_last_day_paid,
2848 'P');
2849
2850 l_value := to_char(l_last_day_paid1,'YYYY/MM/DD HH24:MI:SS');
2851
2852 end if;
2853
2854 l_user_entity_id := get_user_entity('ROE_PAY_EARNED_END_DATE');
2855
2856 hr_utility.trace('ROE_PAY_EARNED_END_DATE id = '||
2857 to_char(l_user_entity_id));
2858 hr_utility.trace('ROE_PAY_EARNED_END_DATE = '|| l_value);
2859
2860 ff_archive_api.create_archive_item(
2861 p_archive_item_id => l_archive_item_id,
2862 p_user_entity_id => l_user_entity_id,
2863 p_archive_value => l_value,
2864 p_archive_type => 'AAP',
2865 p_action_id => p_assactid,
2866 p_legislation_code => 'CA',
2867 p_object_version_number => l_object_version_number,
2868 p_some_warning => l_some_warning);
2869
2870 end if;
2871
2872 --
2873 -- ROE reason and Comment
2874 --
2875
2876 l_effective_date := get_date(l_person_id,
2877 l_assignment_id,
2878 l_business_group_id,
2879 l_effective_date,
2880 l_recall_date,
2881 l_roe_reason,
2882 l_roe_comment,
2883 l_term_or_abs_flag,
2884 l_term_or_abs);
2885
2886 for cur_field in 1..4 loop
2887
2888 if cur_field = 1 then
2889
2890 l_value := l_roe_reason;
2891 l_user_entity_id := get_user_entity('ROE_REASON');
2892
2893 elsif cur_field = 2 then
2894
2895 l_value := l_roe_comment;
2896 l_user_entity_id := get_user_entity('ROE_COMMENTS');
2897
2898 elsif cur_field = 3 then
2899
2900 if l_recall_date is not null then
2901
2902 l_recall_date1 := get_working_date(l_business_group_id,
2903 l_assignment_id,
2904 l_recall_date,
2905 'N');
2906 else
2907
2908 l_recall_date1 := l_recall_date;
2909
2910 end if;
2911
2912 hr_utility.trace('l_recall_date ' ||
2913 to_char(l_recall_date1,'dd-mon-yy'));
2914 l_value := to_char(l_recall_date1,'YYYY/MM/DD HH24:MI:SS');
2915 l_user_entity_id := get_user_entity('ROE_EXPECTED_DATE_OF_RECALL');
2916
2917 elsif cur_field = 4 then
2918
2919 if l_recall_date is not null then
2920 l_value := 'Y';
2921 else
2922 if (l_roe_reason = 'E' or
2923 l_roe_reason = 'G' or
2924 l_roe_reason = 'M')then
2925 l_value := 'N';
2926 elsif (l_roe_reason = 'A' or
2927 l_roe_reason = 'B' or
2928 l_roe_reason = 'C' or
2929 l_roe_reason = 'D' or
2930 l_roe_reason = 'F' or
2931 l_roe_reason = 'H' or
2932 l_roe_reason = 'J' or
2933 l_roe_reason = 'K' or
2934 l_roe_reason = 'N' or
2935 l_roe_reason = 'P') then
2936 l_value := 'U';
2937 elsif l_roe_reason is null then
2938 l_value := null;
2939 end if;
2940 end if;
2941
2942 l_user_entity_id := get_user_entity('ROE_UNKNOWN_NOT_RETURNING');
2943
2944 end if;
2945
2946 hr_utility.trace('user entity id = '|| to_char(l_user_entity_id));
2947 hr_utility.trace('l value = '|| l_value);
2948
2949 hr_utility.trace('l person_id = '|| to_char(l_person_id));
2950 hr_utility.trace('l bg_id = '|| to_char(l_business_group_id));
2951 hr_utility.trace('l effective date = '|| to_char(l_effective_date));
2952 hr_utility.trace('l recall date = '|| to_char(l_recall_date));
2953 hr_utility.trace('l reason = '|| l_roe_reason);
2954 hr_utility.trace('l comment = '|| l_roe_comment);
2955
2956
2957 ff_archive_api.create_archive_item(
2958 p_archive_item_id => l_archive_item_id,
2959 p_user_entity_id => l_user_entity_id,
2960 p_archive_value => l_value,
2961 p_archive_type => 'AAP',
2962 p_action_id => p_assactid,
2963 p_legislation_code => 'CA',
2964 p_object_version_number => l_object_version_number,
2965 p_some_warning => l_some_warning);
2966
2967 end loop;
2968
2969
2970 -- Archiving the Box 17A ROE Vacation Pay Balance
2971
2972 open cur_tax_group;
2973 fetch cur_tax_group
2974 into l_tax_group_id;
2975 close cur_tax_group;
2976
2977 hr_utility.trace('l_tax_group_id = ' || l_tax_group_id);
2978 hr_utility.trace('l_t4a_gre = ' || l_t4a_gre);
2979
2980 l_value := '0';
2981
2982 l_user_entity_id := get_user_entity('ROE_BOX_17A');
2983
2984 if balance_feed_exists('ROE Vacation Pay',l_business_group_id) then
2985
2986 hr_utility.trace('Archive Data: Balance Feed Exists for ROE Vacation Pay');
2987
2988 l_defined_balance_id := get_defined_balance_id(
2989 'ROE Vacation Pay',
2990 'Assignment within Government Reporting Entity Period to Date',
2991 l_business_group_id);
2992
2993 hr_utility.trace('l_defined_balance_id = ' || to_char(l_defined_balance_id));
2994 for gres in cur_17_gres(l_tax_group_id) loop
2995
2996 hr_utility.trace('gres.tax_unit_id = ' || to_char(gres.tax_unit_id));
2997 hr_utility.trace('17A l_final_pay_period_start_date = ' || to_char(l_final_pay_period_start_date));
2998 hr_utility.trace('17A l_final_pay_period_end_date = ' || to_char(l_final_pay_period_end_date));
2999
3000 pay_balance_pkg.set_context('TAX_UNIT_ID',gres.tax_unit_id);
3001
3002 open cur_latest_aaid(l_final_pay_period_start_date,
3003 l_final_pay_period_end_date,
3004 gres.tax_unit_id);
3005 fetch cur_latest_aaid
3006 into l_latest_aaid;
3007 if cur_latest_aaid%NOTFOUND then
3008 close cur_latest_aaid;
3009 else
3010 close cur_latest_aaid;
3011 end if;
3012
3013 hr_utility.trace('l_latest_aaid = ' || to_char(l_latest_aaid));
3014
3015 if l_latest_aaid is not null then
3016 l_value := l_value + NVL(pay_balance_pkg.get_value(l_defined_balance_id,
3017 l_latest_aaid),0);
3018 end if;
3019
3020 hr_utility.trace('Vacation Paid l_value = ' || l_value);
3021 hr_utility.trace('l_final_pay_period_end_date = ' ||
3022 to_char(l_final_pay_period_end_date));
3023
3024 open cur_final_pay_period_date(l_final_pay_period_end_date + 1);
3025 fetch cur_final_pay_period_date
3026 into l_period_start_date_after_term,
3027 l_period_end_date_after_term;
3028
3029 if cur_final_pay_period_date%NOTFOUND then
3030 l_period_start_date_after_term := NULL;
3031 l_period_end_date_after_term := NULL;
3032 end if;
3033
3034 close cur_final_pay_period_date;
3035
3036 open cur_latest_aaid(l_period_start_date_after_term,
3037 l_period_end_date_after_term,
3038 gres.tax_unit_id);
3039 fetch cur_latest_aaid
3040 into l_latest_aaid_after_term;
3041 if cur_latest_aaid%NOTFOUND then
3042 close cur_latest_aaid;
3043 else
3044 close cur_latest_aaid;
3045 end if;
3046
3047 hr_utility.trace('Box 17A l_latest_aaid_after_term = ' ||
3048 to_char(l_latest_aaid_after_term));
3049
3050 if l_latest_aaid_after_term is not null then
3051 l_value := l_value + NVL(pay_balance_pkg.get_value(l_defined_balance_id,
3052 l_latest_aaid_after_term),0);
3053 end if;
3054
3055 hr_utility.trace('ROE_BOX_17A = ' || l_value);
3056
3057 end loop; -- cur_17_gres
3058
3059 end if; -- End if balance_feed_exists
3060
3061 ff_archive_api.create_archive_item(
3062 p_archive_item_id => l_archive_item_id,
3063 p_user_entity_id => l_user_entity_id,
3064 p_archive_value => l_value,
3065 p_archive_type => 'AAP',
3066 p_action_id => p_assactid,
3067 p_legislation_code => 'CA',
3068 p_object_version_number => l_object_version_number,
3069 p_some_warning => l_some_warning);
3070
3071 -- End of archiving Box 17A Balance
3072
3073 -- Start of archiving ROE Box 17C, Only the First three
3074 -- balances with highest value with their codes with be
3075 -- archived.
3076
3077
3078 -- l_latest_aaid will have the latest assignment_action_id
3079 -- if box 17A is populated otherwise it will have 0 as this
3080 -- is initialized with 0.
3081
3082 hr_utility.trace('l_final_pay_period_start_date = ' ||
3083 to_char(l_final_pay_period_start_date));
3084
3085
3086 hr_utility.trace('l_tax_unit_id = ' || to_char(l_tax_unit_id));
3087
3088 for tot_no_bal in 1..12 loop
3089
3090 l_value := '0';
3091 if balance_feed_exists(
3092 pay_ca_archive.box17c_bal_table(tot_no_bal).balance_name,
3093 l_business_group_id) then
3094
3095 l_defined_balance_id := get_defined_balance_id(
3096 pay_ca_archive.box17c_bal_table(tot_no_bal).balance_name,
3097 'Assignment within Government Reporting Entity Period to Date',
3098 l_business_group_id);
3099
3100 hr_utility.trace('Box 17C l_defined_balance_id = ' ||
3101 to_char(l_defined_balance_id));
3102
3103 for gres in cur_17_gres(l_tax_group_id) loop
3104
3105 pay_balance_pkg.set_context('TAX_UNIT_ID',gres.tax_unit_id);
3106
3107 open cur_latest_aaid(l_final_pay_period_start_date,
3108 l_final_pay_period_end_date,
3109 gres.tax_unit_id);
3110 fetch cur_latest_aaid
3111 into l_latest_aaid;
3112 if cur_latest_aaid%NOTFOUND then
3113 close cur_latest_aaid;
3114 else
3115 close cur_latest_aaid;
3116 end if;
3117
3118 hr_utility.trace('l_latest_aaid = ' || to_char(l_latest_aaid));
3119
3120 if l_latest_aaid is not null then
3121 l_value := l_value +
3122 NVL(pay_balance_pkg.get_value(l_defined_balance_id,
3123 l_latest_aaid),0);
3124 end if;
3125
3126 hr_utility.trace(
3127 pay_ca_archive.box17c_bal_table(tot_no_bal).balance_name || ' = '
3128 || l_value);
3129
3130 hr_utility.trace('Box 17C l_final_pay_period_end_date = ' ||
3131 to_char(l_final_pay_period_end_date));
3132
3133 open cur_final_pay_period_date(l_final_pay_period_end_date + 1);
3134 fetch cur_final_pay_period_date
3135 into l_period_start_date_after_term,
3136 l_period_end_date_after_term;
3137
3138 if cur_final_pay_period_date%NOTFOUND then
3139 l_period_start_date_after_term := NULL;
3140 l_period_end_date_after_term := NULL;
3141 end if;
3142
3143 close cur_final_pay_period_date;
3144
3145 hr_utility.trace('Box 17C l_period_end_date_after_term = ' ||
3146 to_char(l_period_end_date_after_term));
3147
3148 open cur_latest_aaid(l_period_start_date_after_term,
3149 l_period_end_date_after_term,
3150 gres.tax_unit_id);
3151 fetch cur_latest_aaid
3152 into l_latest_aaid_after_term;
3153 if cur_latest_aaid%NOTFOUND then
3154 close cur_latest_aaid;
3155 else
3156 close cur_latest_aaid;
3157 end if;
3158
3159 hr_utility.trace('Box 17C l_latest_aaid_after_term = ' ||
3160 to_char(l_latest_aaid_after_term));
3161
3162 if l_latest_aaid_after_term is not null then
3163 l_value := l_value +
3164 NVL(pay_balance_pkg.get_value(l_defined_balance_id,
3165 l_latest_aaid_after_term),0);
3166 end if;
3167
3168 hr_utility.trace('Box 17C l_value = ' || l_value);
3169
3170 end loop; -- cur_17_gres
3171
3172 -- We will archive only the first three highest Balances
3173
3174 if to_number(l_value) > 0 then
3175
3176 if to_number(l_value) >= l_temp_value1 then
3177 l_temp_value3 := l_temp_value2;
3178 l_temp_code3 := l_temp_code2;
3179 l_temp_value2 := l_temp_value1;
3180 l_temp_code2 := l_temp_code1;
3181 l_temp_value1 := to_number(l_value);
3182 l_temp_code1 := pay_ca_archive.box17c_bal_table(tot_no_bal).code;
3183 else
3184 if to_number(l_value) >= l_temp_value2 then
3185 l_temp_value3 := l_temp_value2;
3186 l_temp_code3 := l_temp_code2;
3187 l_temp_value2 := to_number(l_value);
3188 l_temp_code2 := pay_ca_archive.box17c_bal_table(tot_no_bal).code;
3189 else
3190 l_temp_value3 := to_number(l_value);
3191 l_temp_code3 := pay_ca_archive.box17c_bal_table(tot_no_bal).code;
3192 end if;
3193 end if;
3194
3195 end if; -- End if l_value > 0
3196
3197 end if; -- End if Balance Feed Exists;
3198
3199 end loop; -- End loop Balances
3200
3201
3202 hr_utility.trace('l_temp_code1 = ' || l_temp_code1);
3203 hr_utility.trace('l_temp_value1 = ' || to_char(l_temp_value1));
3204 hr_utility.trace('l_temp_code2 = ' || l_temp_code2);
3205 hr_utility.trace('l_temp_value2 = ' || to_char(l_temp_value2));
3206 hr_utility.trace('l_temp_code3 = ' || l_temp_code3);
3207 hr_utility.trace('l_temp_value3 = ' || to_char(l_temp_value3));
3208
3209 tab_user_entity_name(1) := 'ROE_BOX_17C_DESC1';
3210 tab_user_entity_name(2) := 'ROE_BOX_17C_AMOUNT1';
3211 tab_user_entity_name(3) := 'ROE_BOX_17C_DESC2';
3212 tab_user_entity_name(4) := 'ROE_BOX_17C_AMOUNT2';
3213 tab_user_entity_name(5) := 'ROE_BOX_17C_DESC3';
3214 tab_user_entity_name(6) := 'ROE_BOX_17C_AMOUNT3';
3215
3216 for tot_box_17c in 1..6 loop
3217
3218 l_user_entity_id := get_user_entity(tab_user_entity_name(tot_box_17c));
3219
3220 if tot_box_17c = 1 then
3221 l_value := l_temp_code1;
3222 elsif tot_box_17c = 2 then
3223 l_value := to_char(l_temp_value1);
3224 elsif tot_box_17c = 3 then
3225 l_value := l_temp_code2;
3226 elsif tot_box_17c = 4 then
3227 l_value := to_char(l_temp_value2);
3228 elsif tot_box_17c = 5 then
3229 l_value := l_temp_code3;
3230 elsif tot_box_17c = 6 then
3231 l_value := to_char(l_temp_value3);
3232 end if;
3233
3234 ff_archive_api.create_archive_item(
3235 p_archive_item_id => l_archive_item_id,
3236 p_user_entity_id => l_user_entity_id,
3237 p_archive_value => l_value,
3238 p_archive_type => 'AAP',
3239 p_action_id => p_assactid,
3240 p_legislation_code => 'CA',
3241 p_object_version_number => l_object_version_number,
3242 p_some_warning => l_some_warning);
3243
3244 end loop;
3245
3246 -- End of archiving Box 17C Balance
3247
3248 --
3249 -- Get the EI hours and EI earnings
3250 --
3251
3252 -- EI Hours - Begin
3253
3254 l_total_type := 'EI Hours';
3255
3256 ret := pay_ca_roe_ei_pkg.get_ei_amount_totals
3257 (
3258 p_total_type => l_total_type,
3259 p_assignment_id => l_assignment_id,
3260 p_gre => l_tax_unit_id,
3261 p_payroll_id => l_payroll_id,
3262 p_start_date => NVL(l_first_day_worked, l_prev_roe_date + 1),
3263 p_end_date => NVL(l_final_pay_period_end_date, l_roe_date),
3264 p_total_insurable => l_total_insurable,
3265 p_no_of_periods => l_no_of_periods,
3266 p_period_total => tab_period_total,
3267 p_period_type => l_period_type,
3268 p_term_or_abs_flag => l_term_or_abs_flag
3269 );
3270
3271 l_value := l_total_insurable;
3272 l_user_entity_id := get_user_entity('ROE_TOTAL_INSURABLE_HOURS');
3273
3274 hr_utility.trace('user entity id = '|| to_char(l_user_entity_id));
3275 hr_utility.trace('l value = '|| l_value);
3276
3277 ff_archive_api.create_archive_item(
3278 p_archive_item_id => l_archive_item_id,
3279 p_user_entity_id => l_user_entity_id,
3280 p_archive_value => l_value,
3281 p_archive_type => 'AAP',
3282 p_action_id => p_assactid,
3283 p_legislation_code => 'CA',
3284 p_object_version_number => l_object_version_number,
3285 p_some_warning => l_some_warning);
3286
3287
3288 -- EI Hours - End
3289
3290 hr_utility.trace('End of archiving EI Hours');
3291
3292 -- EI Earnings - Begin
3293
3294 l_total_type := 'EI Earnings';
3295
3296 ret := pay_ca_roe_ei_pkg.get_ei_amount_totals
3297 (
3298 p_total_type => l_total_type,
3299 p_assignment_id => l_assignment_id,
3300 p_gre => l_tax_unit_id,
3301 p_payroll_id => l_payroll_id,
3302 p_start_date => NVL(l_first_day_worked, l_prev_roe_date + 1),
3303 p_end_date => NVL(l_final_pay_period_end_date, l_roe_date),
3304 p_total_insurable => l_total_insurable,
3305 p_no_of_periods => l_no_of_periods,
3306 p_period_total => tab_period_total,
3307 p_period_type => l_period_type,
3308 p_term_or_abs_flag => l_term_or_abs_flag
3309 );
3310
3311 hr_utility.trace('ret = ' || ret);
3312
3313 tab_user_entity_name(1) := 'ROE_INSURABLE_EARNING_1';
3314 tab_user_entity_name(2) := 'ROE_INSURABLE_EARNING_2';
3315 tab_user_entity_name(3) := 'ROE_INSURABLE_EARNING_3';
3316 tab_user_entity_name(4) := 'ROE_INSURABLE_EARNING_4';
3317 tab_user_entity_name(5) := 'ROE_INSURABLE_EARNING_5';
3318 tab_user_entity_name(6) := 'ROE_INSURABLE_EARNING_6';
3319 tab_user_entity_name(7) := 'ROE_INSURABLE_EARNING_7';
3320 tab_user_entity_name(8) := 'ROE_INSURABLE_EARNING_8';
3321 tab_user_entity_name(9) := 'ROE_INSURABLE_EARNING_9';
3322 tab_user_entity_name(10) := 'ROE_INSURABLE_EARNING_10';
3323 tab_user_entity_name(11) := 'ROE_INSURABLE_EARNING_11';
3324 tab_user_entity_name(12) := 'ROE_INSURABLE_EARNING_12';
3325 tab_user_entity_name(13) := 'ROE_INSURABLE_EARNING_13';
3326 tab_user_entity_name(14) := 'ROE_INSURABLE_EARNING_14';
3327 tab_user_entity_name(15) := 'ROE_INSURABLE_EARNING_15';
3328 tab_user_entity_name(16) := 'ROE_INSURABLE_EARNING_16';
3329 tab_user_entity_name(17) := 'ROE_INSURABLE_EARNING_17';
3330 tab_user_entity_name(18) := 'ROE_INSURABLE_EARNING_18';
3331 tab_user_entity_name(19) := 'ROE_INSURABLE_EARNING_19';
3332 tab_user_entity_name(20) := 'ROE_INSURABLE_EARNING_20';
3333 tab_user_entity_name(21) := 'ROE_INSURABLE_EARNING_21';
3334 tab_user_entity_name(22) := 'ROE_INSURABLE_EARNING_22';
3335 tab_user_entity_name(23) := 'ROE_INSURABLE_EARNING_23';
3336 tab_user_entity_name(24) := 'ROE_INSURABLE_EARNING_24';
3337 tab_user_entity_name(25) := 'ROE_INSURABLE_EARNING_25';
3338 tab_user_entity_name(26) := 'ROE_INSURABLE_EARNING_26';
3339 tab_user_entity_name(27) := 'ROE_INSURABLE_EARNING_27';
3340 /* Added by ssmukher for bug 4510534 */
3341 tab_user_entity_name(28) := 'ROE_INSURABLE_EARNING_28';
3342 tab_user_entity_name(29) := 'ROE_INSURABLE_EARNING_29';
3343 tab_user_entity_name(30) := 'ROE_INSURABLE_EARNING_30';
3344 tab_user_entity_name(31) := 'ROE_INSURABLE_EARNING_31';
3345 tab_user_entity_name(32) := 'ROE_INSURABLE_EARNING_32';
3346 tab_user_entity_name(33) := 'ROE_INSURABLE_EARNING_33';
3347 tab_user_entity_name(34) := 'ROE_INSURABLE_EARNING_34';
3348 tab_user_entity_name(35) := 'ROE_INSURABLE_EARNING_35';
3349 tab_user_entity_name(36) := 'ROE_INSURABLE_EARNING_36';
3350 tab_user_entity_name(37) := 'ROE_INSURABLE_EARNING_37';
3351 tab_user_entity_name(38) := 'ROE_INSURABLE_EARNING_38';
3352 tab_user_entity_name(39) := 'ROE_INSURABLE_EARNING_39';
3353 tab_user_entity_name(40) := 'ROE_INSURABLE_EARNING_40';
3354 tab_user_entity_name(41) := 'ROE_INSURABLE_EARNING_41';
3355 tab_user_entity_name(42) := 'ROE_INSURABLE_EARNING_42';
3356 tab_user_entity_name(43) := 'ROE_INSURABLE_EARNING_43';
3357 tab_user_entity_name(44) := 'ROE_INSURABLE_EARNING_44';
3358 tab_user_entity_name(45) := 'ROE_INSURABLE_EARNING_45';
3359 tab_user_entity_name(46) := 'ROE_INSURABLE_EARNING_46';
3360 tab_user_entity_name(47) := 'ROE_INSURABLE_EARNING_47';
3361 tab_user_entity_name(48) := 'ROE_INSURABLE_EARNING_48';
3362 tab_user_entity_name(49) := 'ROE_INSURABLE_EARNING_49';
3363 tab_user_entity_name(50) := 'ROE_INSURABLE_EARNING_50';
3364 tab_user_entity_name(51) := 'ROE_INSURABLE_EARNING_51';
3365 tab_user_entity_name(52) := 'ROE_INSURABLE_EARNING_52';
3366 tab_user_entity_name(53) := 'ROE_INSURABLE_EARNING_53';
3367 tab_user_entity_name(54) := 'ROE_TOTAL_INSURABLE_EARNINGS';
3368 tab_user_entity_name(55) := 'ROE_PAY_PERIOD_TYPE';
3369
3370 for cur_field in 1..55 loop
3371
3372 if cur_field = 55 then
3373
3374 l_value := l_period_type;
3375 l_user_entity_id := get_user_entity(tab_user_entity_name(cur_field));
3376
3377 elsif cur_field = 54 then
3378
3379 l_value := l_total_insurable;
3380 l_user_entity_id := get_user_entity(tab_user_entity_name(cur_field));
3381
3382 else
3383
3384 /* Commented by ssmukher for Bug 4510534
3385 if ret = 'BOX15B' then
3386 hr_utility.trace('15B');
3387 l_value := '0';
3388 else
3389 hr_utility.trace('BOX15C');
3390 l_value := tab_period_total(cur_field);
3391 end if;
3392 */
3393 l_value := tab_period_total(cur_field);
3394 l_user_entity_id := get_user_entity(tab_user_entity_name(cur_field));
3395
3396 end if;
3397
3398 hr_utility.trace('user entity id = '|| to_char(l_user_entity_id));
3399 hr_utility.trace('l value = '|| l_value);
3400
3401 ff_archive_api.create_archive_item(
3402 p_archive_item_id => l_archive_item_id,
3403 p_user_entity_id => l_user_entity_id,
3404 p_archive_value => l_value,
3405 p_archive_type => 'AAP',
3406 p_action_id => p_assactid,
3407 p_legislation_code => 'CA',
3408 p_object_version_number => l_object_version_number,
3409 p_some_warning => l_some_warning);
3410
3411 end loop;
3412
3413 hr_utility.trace('End of archiving EI Earnings');
3414
3415 -- As all the archiving is done now we lock
3416 -- the mag assignment_action_id of the l_assignment_amend
3417 -- with the new assignment-action_id
3418
3419 if l_assignment_amend is not null then
3420
3421 open cur_pai;
3422 fetch cur_pai into l_locking_action_id;
3423 close cur_pai;
3424
3425 hr_nonrun_asact.insint(p_assactid,l_locking_action_id);
3426
3427 end if;
3428
3429 -- If the assignment set is passed we need to delete the
3430 -- assignment_id from the assignment set/and the assignment
3431 -- set it self where there is not record left in hr_assignment
3432 -- _set_amendments.
3433
3434 IF l_assignment_set_id IS NOT NULL THEN
3435
3436 pay_ca_archive.delete_asg_set_records(l_asg_set_name,
3437 l_assignment_id,
3438 l_business_group_id);
3439 ELSE
3440
3441 -- If it's a Record of Employment for a sigle employee then
3442 -- we need to delete the assignment from the assignment sets.
3443 -- from both the assignment sets.
3444
3445 pay_ca_archive.delete_asg_set_records('LOA_ASG_SET',
3446 l_assignment_id,
3447 l_business_group_id);
3448
3449 pay_ca_archive.delete_asg_set_records('TERMINATION_ASG_SET',
3450 l_assignment_id,
3451 l_business_group_id);
3452
3453 END IF;
3454
3455 ELSE
3456
3457 hr_utility.raise_error;
3458
3459 END IF; -- check_retry_amend has failed or not.
3460
3461 end;
3462
3463 end archive_data;
3464
3465 function asg_set_exists (p_asg_set in varchar2,
3466 p_business_group_id in number) return NUMBER IS
3467 --
3468 cursor c_set_check is
3469 SELECT assignment_set_id
3470 FROM hr_assignment_sets
3471 WHERE UPPER(assignment_set_name) = UPPER(p_asg_set)
3472 AND business_group_id = p_business_group_id;
3473 --
3474 l_assignment_set_id hr_assignment_sets.assignment_set_id%TYPE;
3475 --
3476 begin
3477 --
3478 OPEN c_set_check;
3479 FETCH c_set_check
3480 INTO l_assignment_set_id;
3481
3482 IF c_set_check%FOUND then
3483 CLOSE c_set_check;
3484 RETURN l_assignment_set_id;
3485 ELSE
3486 CLOSE c_set_check;
3487 RETURN -1;
3488 END IF;
3489 --
3490 end asg_set_exists;
3491 --
3492 procedure create_asg_set_records(p_assignment_set_name in varchar2,
3493 p_assignment_id in number,
3494 p_business_group_id in number) IS
3495 begin
3496
3497 declare
3498
3499 cursor c_sequence is
3500 SELECT hr_assignment_sets_s.nextval
3501 FROM dual;
3502
3503 l_assignment_set_id hr_assignment_sets.assignment_set_id%TYPE;
3504
3505 CURSOR c_already_in_set is
3506 SELECT 'X'
3507 FROM hr_assignment_set_amendments
3508 WHERE assignment_id = p_assignment_id
3509 AND assignment_set_id = l_assignment_set_id
3510 AND include_or_exclude = 'I';
3511
3512 l_dummy VARCHAR2(1);
3513 l_rowid VARCHAR2(30);
3514
3515 --
3516 begin
3517
3518 -- If Assignment set already exists then we need to add the assigment
3519 -- into the assigment set, ELSE we need to create the assignment set
3520 -- and then add the assignment to it.
3521
3522 l_assignment_set_id := asg_set_exists(p_assignment_set_name,
3523 p_business_group_id);
3524
3525 hr_utility.trace('l_assignment_set_id = ' || to_char(l_assignment_set_id));
3526
3527 IF l_assignment_set_id <> -1 THEN
3528
3529 OPEN c_already_in_set;
3530 FETCH c_already_in_set
3531 INTO l_dummy;
3532 IF c_already_in_set%NOTFOUND THEN
3533
3534 CLOSE c_already_in_set;
3535
3536 hr_utility.trace('Assignment id: ' || to_char(p_assignment_id) ||
3537 ' does not exist in the assignment set');
3538
3539 hr_assignment_set_amds_pkg.insert_row(
3540 p_rowid => l_rowid
3541 ,p_assignment_id => p_assignment_id
3542 ,p_assignment_set_id => l_assignment_set_id
3543 ,p_include_or_exclude => 'I');
3544 ELSE
3545 hr_utility.trace('Assignment id: ' || to_char(p_assignment_id) ||
3546 ' already exists in the assignment set');
3547 CLOSE c_already_in_set;
3548
3549 END IF;
3550
3551 ELSE -- Assignment set doesn't exists so we need to create the assignment
3552 -- set as well.
3553
3554 OPEN c_sequence;
3555 FETCH c_sequence into l_assignment_set_id;
3556 CLOSE c_sequence;
3557
3558 hr_assignment_sets_pkg.insert_row(
3559 p_rowid => l_rowid,
3560 p_assignment_set_id => l_assignment_set_id,
3561 p_business_group_id => p_business_group_id,
3562 p_payroll_id => '',
3563 p_assignment_set_name => p_assignment_set_name,
3564 p_formula_id => null);
3565
3566 hr_assignment_set_amds_pkg.insert_row(
3567 p_rowid => l_rowid
3568 ,p_assignment_id => p_assignment_id
3569 ,p_assignment_set_id => l_assignment_set_id
3570 ,p_include_or_exclude => 'I');
3571
3572 END IF;
3573
3574 end;
3575 --
3576 end create_asg_set_records;
3577 --
3578 procedure delete_asg_set_records(p_assignment_set_name in VARCHAR2,
3579 p_assignment_id in NUMBER,
3580 p_business_group_id NUMBER) IS
3581 --
3582 begin
3583
3584 declare
3585
3586 CURSOR cur_asg_set_id IS
3587 SELECT assignment_set_id
3588 FROM hr_assignment_sets
3589 WHERE UPPER(assignment_set_name) = UPPER(p_assignment_set_name)
3590 AND business_group_id = p_business_group_id;
3591
3592 l_assignment_set_id hr_assignment_sets.assignment_set_id%TYPE;
3593
3594 CURSOR c_already_in_set IS
3595 SELECT 'X'
3596 FROM hr_assignment_set_amendments
3597 WHERE assignment_id = p_assignment_id
3598 AND assignment_set_id = l_assignment_set_id
3599 AND include_or_exclude = 'I';
3600
3601 CURSOR cur_last_row IS
3602 SELECT 'X'
3603 FROM hr_assignment_set_amendments
3604 WHERE assignment_set_id = l_assignment_set_id;
3605
3606 --
3607 l_dummy VARCHAR2(1);
3608 --
3609 begin
3610 --
3611 hr_utility.trace(' Begin pay_ca_archive.delete_asg_set_records');
3612
3613 OPEN cur_asg_set_id;
3614 FETCH cur_asg_set_id
3615 INTO l_assignment_set_id;
3616
3617 IF cur_asg_set_id%FOUND then
3618
3619 hr_utility.trace(' In delete_asg_set_records, cur_asg_set_id found !');
3620 hr_utility.trace(' In delete_asg_set_records, l_assignment_set_id = '
3621 || to_char(l_assignment_set_id));
3622
3623 CLOSE cur_asg_set_id;
3624 OPEN c_already_in_set;
3625 FETCH c_already_in_set
3626 INTO l_dummy;
3627
3628 IF c_already_in_set%FOUND THEN
3629
3630 hr_utility.trace(' In delete_asg_set_records, c_already_in_set found !');
3631 CLOSE c_already_in_set;
3632
3633 DELETE FROM
3634 hr_assignment_set_amendments
3635 WHERE
3636 assignment_set_id = l_assignment_set_id and
3637 assignment_id = p_assignment_id;
3638
3639 -- If this is the last row in hr_assignment_set_amendments
3640 -- then we need to delete the assignment set as well from
3641 -- hr_assignment_sets.
3642
3643 OPEN cur_last_row;
3644 FETCH cur_last_row
3645 INTO l_dummy;
3646
3647 IF cur_last_row%ROWCOUNT = 0 then
3648
3649 hr_utility.trace('In delete_asg_set_records cur_last_row = ' ||
3650 to_char(cur_last_row%ROWCOUNT));
3651
3652 CLOSE cur_last_row;
3653
3654 DELETE FROM hr_assignment_sets
3655 where assignment_set_id = l_assignment_set_id;
3656
3657 ELSE
3658
3659 close cur_last_row;
3660
3661 END IF;
3662
3663 ELSE
3664
3665 close c_already_in_set;
3666
3667 END IF;
3668
3669 ELSE
3670
3671 CLOSE cur_asg_set_id;
3672
3673 END IF;
3674
3675 END;
3676 --
3677 end delete_asg_set_records;
3678 --
3679 end pay_ca_archive;