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