[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_UITE_ARCH_PKG
Source
1 PACKAGE BODY pay_jp_uite_arch_pkg AS
2 -- $Header: pyjpuiar.pkb 120.0.12010000.13 2010/06/02 19:15:05 mpothala noship $
3 -- *************************************************************************
4 -- * Copyright (c) Oracle Corporation Japan,2009 Product Development.
5 -- * All rights reserved
6 -- *************************************************************************
7 -- *
8 -- * PROGRAM NAME
9 -- * pyjpuiar.pkb
10 -- *
11 -- * DESCRIPTION
12 -- * This script creates the package body of pay_jp_uite_arch_pkg
13 -- *
14 -- * USAGE
15 -- * To install sqlplus <apps_user>/<apps_pwd> @pyjpuiar.pkb
16 -- * To Execute sqlplus <apps_user>/<apps_pwd> EXEC pay_jp_uite_arch_pkg.<procedure name>
17 -- *
18 -- * PROGRAM LIST
19 -- * ==========
20 -- * NAME DESCRIPTION
21 -- * ----------------- --------------------------------------------------
22 -- * RANGE_CODE
23 -- * INITIALIZATION_CODE
24 -- * ASSIGNMENT_ACTION_CODE
25 -- * ARCHIVE_CODE
26 -- *
27 -- * DEPENDENCIES
28 -- * None
29 -- *
30 -- * CALLED BY
31 -- * Concurrent Program
32 -- *
33 -- * LAST UPDATE DATE 08-Feb-2010
34 -- * Date the program has been modified for the last time
35 -- *
36 -- * HISTORY
37 -- * =======
38 -- *
39 -- * VERSION DATE AUTHOR(S) DESCRIPTION
40 -- * ------- ----------- -----------------------------------------------------------
41 -- * 120.0.12010000.1 08-Mar-2010 RDARASI/MPOTHALA Creation
42 -- * 120.0.12010000.2 06-Apr-2010 MPOTHALA Fixed patch review comments
43 -- * 120.0.12010000.3 12-Apr-2010 MPOTHALA Fixed patch review comments
44 -- * 120.0.12010000.4 16-Apr-2010 MPOTHALA Fixed for bug #9596298
45 -- * 120.0.12010000.5 16-Apr-2010 MPOTHALA Fixed for bug #9648082,9648137,9652235,9655892,9652251
46 -- * 120.0.12010000.6 06-May-2010 MPOTHALA Fixed for bug #9648082,9653516,9702153
47 -- * 120.0.12010000.7 06-May-2010 MPOTHALA Fixed for bug #9648082,9653516,9702153
48 -- * 120.0.12010000.8 06-May-2010 MPOTHALA Fixed for bug #9648082,9653516,9702153
49 -- * 120.0.12010000.9 21-May-2010 MPOTHALA Fixed for bug #9728577,9732294
50 -- * 120.0.12010000.10 26-May-2010 MPOTHALA Fixed for bug #9728577,9732294
51 -- * 120.0.12010000.11 26-May-2010 MPOTHALA Fixed for bug #9732572
52 -- * 120.0.12010000.12 02-Jun-2010 MPOTHALA Fixed for bug #9732572
53 -- * 120.0.12010000.13 02-Jun-2010 MPOTHALA Fixed for bug #9732572
54 -- *********************************************************************************
55
56 --Declaration of constant global variables
57
58 gc_package CONSTANT VARCHAR2(60) := 'pay_jp_uite_arch_pkg.';
59 gc_sal_ele_set VARCHAR2(20) := 'SAL';
60 gc_spb_ele_set VARCHAR2(20) := 'SPB';
61 gc_legislation_code per_business_groups.legislation_code%TYPE;
62 gc_date_earned CONSTANT VARCHAR2(30) := 'DATE_EARNED';
63 gc_date_paid CONSTANT VARCHAR2(30) := 'DATE_PAID';
64 gn_max_period CONSTANT NUMBER := 48;
65 -- Declaration of global variables
66 gn_arc_payroll_action_id pay_payroll_actions.payroll_action_id%type;
67 gn_business_group_id hr_all_organization_units.organization_id%TYPE;
68 gn_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
69 gb_debug BOOLEAN;
70 gd_end_date DATE;
71 gd_start_date DATE;
72 gc_exception EXCEPTION;
73 gc_santei_base VARCHAR2(20) DEFAULT gc_date_earned;
74 gn_output_period NUMBER DEFAULT 12;
75 gn_sal_ele_set_id pay_element_sets.element_set_id%TYPE;
76 gn_spb_ele_set_id pay_element_sets.element_set_id%TYPE;
77 --
78 TYPE gt_wage_info IS RECORD (payment_date DATE
79 ,insured_start_date DATE
80 ,insured_end_date DATE
81 ,insured_days NUMBER
82 ,period_start_date DATE
83 ,period_end_date DATE
84 ,base_days NUMBER
85 ,wage_amount_a NUMBER
86 ,wage_amount_b NUMBER
87 ,remarks VARCHAR2(60)
88 ,exclude_period VARCHAR2(10)
89 ,line_number NUMBER
90 );
91 --
92 TYPE gt_insert_wage_info IS TABLE OF gt_wage_info INDEX BY BINARY_INTEGER;
93 --
94 FUNCTION get_life_ins_org_id(p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
95 ,p_effective_date IN DATE)
96 --************************************************************************
97 -- FUNCTION
98 -- get_life_ins_org_id
99 --
100 -- DESCRIPTION
101 -- To Retrive life insurance organization Id
102 --
103 -- ACCESS
104 -- PRIVATE
105 --
106 -- PREREQUISITES
107 -- None
108 --
109 -- CALLED BY
110 -- archive_code
111 --************************************************************************
112 RETURN NUMBER
113 IS
114 --
115 lc_procedure VARCHAR2(200);
116 ln_life_ins_org_id NUMBER;
117 --
118 BEGIN
119 --
120 gb_debug := hr_utility.debug_enabled;
121 --
122 IF gb_debug THEN
123 lc_procedure := gc_package||'get_life_ins_org';
124 hr_utility.set_location('Entering '||lc_procedure,1);
125 END IF;
126 --
127 ln_life_ins_org_id := pay_jp_balance_pkg.get_entry_value_number(p_element_name => 'COM_LI_INFO'
128 ,p_input_value_name => 'EI_LOCATION'
129 ,p_assignment_id => p_assignment_id
130 ,p_effective_date => p_effective_date
131 );
132
133 --
134 IF gb_debug THEN
135 hr_utility.set_location('Leaving '||lc_procedure,1000);
136 END IF;
137 --
138 RETURN ln_life_ins_org_id;
139 --
140 EXCEPTION
141 WHEN NO_DATA_FOUND THEN
142 IF gb_debug THEN
143 hr_utility.set_location('No Data Found Exception in get_life_ins_org_id',10);
144 END IF;
145 RETURN NULL;
146 --
147 WHEN OTHERS THEN
148 hr_utility.set_location('Error in '||lc_procedure,999999);
149 RAISE;
150 RETURN NULL;
151 END get_life_ins_org_id;
152 --
153 FUNCTION get_ei_type(p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
154 ,p_effective_date IN DATE)
155 --************************************************************************
156 -- FUNCTION
157 -- get_ei_type
158 --
159 -- DESCRIPTION
160 -- To Retrive Employee Insurance Type
161 --
162 -- ACCESS
163 -- PRIVATE
164 --
165 -- PREREQUISITES
166 -- None
167 --
168 -- CALLED BY
169 -- archive_code
170 --************************************************************************
171 RETURN VARCHAR2
172 IS
173 --
174 lc_procedure VARCHAR2(200);
175 lc_ei_type VARCHAR2(60);
176 --
177 BEGIN
178 --
179 gb_debug := hr_utility.debug_enabled;
180 --
181 IF gb_debug THEN
182 lc_procedure := gc_package||'get_ei_type';
183 hr_utility.set_location('Entering '||lc_procedure,1);
184 END IF;
185 --
186 lc_ei_type := pay_jp_balance_pkg.get_entry_value_char(p_element_name => 'COM_LI_INFO'
187 ,p_input_value_name => 'EI_TYPE'
188 ,p_assignment_id => p_assignment_id
189 ,p_effective_date => p_effective_date
190 );
191
192 --
193 IF gb_debug THEN
194 hr_utility.set_location('Leaving '||lc_procedure,1000);
195 END IF;
196 --
197 RETURN lc_ei_type;
198 --
199 EXCEPTION
200 WHEN NO_DATA_FOUND THEN
201 IF gb_debug THEN
202 hr_utility.set_location('No Data Found Exception in get_ei_type',10);
203 END IF;
204 RETURN NULL;
205 --
206 WHEN OTHERS THEN
207 hr_utility.set_location('Error in '||lc_procedure,999999);
208 RAISE;
209 RETURN NULL;
210 END get_ei_type;
211 --
212 FUNCTION get_term_rpt_flag(p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
213 ,p_effective_date IN DATE)
214 --************************************************************************
215 -- FUNCTION
216 -- get_ei_type
217 --
218 -- DESCRIPTION
219 -- To Retrive Employee Insurance Type
220 --
221 -- ACCESS
222 -- PRIVATE
223 --
224 -- PREREQUISITES
225 -- None
226 --
227 -- CALLED BY
228 -- archive_code
229 --************************************************************************
230 RETURN VARCHAR2
231 IS
232 --
233 lc_procedure VARCHAR2(200);
234 lc_term_rpt_flag VARCHAR2(60);
235 --
236 BEGIN
237 --
238 gb_debug := hr_utility.debug_enabled;
239 --
240 IF gb_debug THEN
241 lc_procedure := gc_package||'get_term_rpt_flag';
242 hr_utility.set_location('Entering '||lc_procedure,1);
243 END IF;
244 --
245 lc_term_rpt_flag := pay_jp_balance_pkg.get_entry_value_char(p_element_name => 'COM_EI_QUALIFY_INFO'
246 ,p_input_value_name => 'TRM_REPORT_OUTPUT_FLAG'
247 ,p_assignment_id => p_assignment_id
248 ,p_effective_date => p_effective_date
249 );
250
251 --
252 IF gb_debug THEN
253 hr_utility.set_location('Leaving '||lc_procedure,1000);
254 END IF;
255 --
256 RETURN lc_term_rpt_flag;
257 --
258 EXCEPTION
259 WHEN NO_DATA_FOUND THEN
260 IF gb_debug THEN
261 hr_utility.set_location('No Data Found Exception in get_term_rpt_flag',10);
262 END IF;
263 RETURN NULL;
264 --
265 WHEN OTHERS THEN
266 hr_utility.set_location('Error in '||lc_procedure,999999);
267 RAISE;
268 RETURN NULL;
269 END get_term_rpt_flag;
270 --
271 FUNCTION get_ei_qualify_date(p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
272 ,p_effective_date IN DATE)
273 --************************************************************************
274 -- FUNCTION
275 -- get_ei_type
276 --
277 -- DESCRIPTION
278 -- To Retrive Employee Insurance qulified date
279 --
280 -- ACCESS
281 -- PRIVATE
282 --
283 -- PREREQUISITES
284 -- None
285 --
286 -- CALLED BY
287 -- archive_code
288 --************************************************************************
289 RETURN DATE
290 IS
291 --
292 lc_procedure VARCHAR2(200);
293 ld_qualify_date DATE;
294 --
295 BEGIN
296 --
297 gb_debug := hr_utility.debug_enabled;
298 --
299 IF gb_debug THEN
300 lc_procedure := gc_package||'get_ei_quality_date';
301 hr_utility.set_location('Entering '||lc_procedure,1);
302 END IF;
303 --
304 ld_qualify_date := pay_jp_balance_pkg.get_entry_value_date(p_element_name => 'COM_EI_QUALIFY_INFO'
305 ,p_input_value_name => 'QUALIFY_DATE'
306 ,p_assignment_id => p_assignment_id
307 ,p_effective_date => p_effective_date
308 );
309
310 --
311 IF gb_debug THEN
312 hr_utility.set_location('Leaving '||lc_procedure,1000);
313 END IF;
314 --
315 RETURN ld_qualify_date;
316 --
317 EXCEPTION
318 WHEN NO_DATA_FOUND THEN
319 IF gb_debug THEN
320 hr_utility.set_location('No Data Found Exception in get_ei_quality_date',10);
321 END IF;
322 RETURN NULL;
323 --
324 WHEN OTHERS THEN
325 hr_utility.set_location('Error in '||lc_procedure,999999);
326 RAISE;
327 RETURN NULL;
328 END get_ei_qualify_date;
329 --
330 FUNCTION get_ei_dis_qual_date(p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
331 ,p_effective_date IN DATE)
332 --************************************************************************
333 -- FUNCTION
334 -- get_ei_type
335 --
336 -- DESCRIPTION
337 -- To Retrive Employee Insurance disqualified date
338 --
339 -- ACCESS
340 -- PRIVATE
341 --
342 -- PREREQUISITES
343 -- None
344 --
345 -- CALLED BY
346 -- archive_code
347 --************************************************************************
348 RETURN DATE
349 IS
350 --
351 lc_procedure VARCHAR2(200);
352 ld_qualify_date DATE;
353 --
354 BEGIN
355 --
356 gb_debug := hr_utility.debug_enabled;
357 --
358 IF gb_debug THEN
359 lc_procedure := gc_package||'get_ei_dis_qual_date';
360 hr_utility.set_location('Entering '||lc_procedure,1);
361 END IF;
362 --
363 ld_qualify_date := pay_jp_balance_pkg.get_entry_value_date(p_element_name => 'COM_EI_QUALIFY_INFO'
364 ,p_input_value_name => 'DISQUALIFY_DATE'
365 ,p_assignment_id => p_assignment_id
366 ,p_effective_date => p_effective_date
367 );
368
369 --
370 IF gb_debug THEN
371 hr_utility.set_location('Leaving '||lc_procedure,1000);
372 END IF;
373 --
374 RETURN ld_qualify_date;
375 --
376 EXCEPTION
377
378 WHEN NO_DATA_FOUND THEN
379 IF gb_debug THEN
380 hr_utility.set_location('No Data Found Exception in get_ei_dis_qual_date',10);
381 END IF;
382 RETURN NULL;
383 --
384 WHEN OTHERS THEN
385 hr_utility.set_location('Error in '||lc_procedure,999999);
386 RAISE;
387 RETURN NULL;
388 END get_ei_dis_qual_date;
389 --
390 FUNCTION get_ui_num(p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
391 ,p_effective_date IN DATE)
392 --************************************************************************
393 -- FUNCTION
394 -- get_ui_num
395 --
396 -- DESCRIPTION
397 -- To Retrive life insurance organization Id
398 --
399 -- ACCESS
400 -- PRIVATE
401 --
402 -- PREREQUISITES
403 -- None
404 --
405 -- CALLED BY
406 -- archive_code
407 --************************************************************************
408 RETURN VARCHAR2
409 IS
410 --
411 lc_procedure VARCHAR2(200);
412 lc_ui_num VARCHAR2(20);
413 --
414 BEGIN
415 --
416 gb_debug := hr_utility.debug_enabled;
417 --
418 IF gb_debug THEN
419 lc_procedure := gc_package||'get_ui_num';
420 hr_utility.set_location('Entering '||lc_procedure,1);
421 END IF;
422 --
423 lc_ui_num := pay_jp_balance_pkg.get_entry_value_char(p_element_name => 'COM_LI_INFO'
424 ,p_input_value_name => 'EI_NUM'
425 ,p_assignment_id => p_assignment_id
426 ,p_effective_date => p_effective_date
427 );
428
429 --
430 IF gb_debug THEN
431 hr_utility.set_location('Leaving '||lc_procedure,1000);
432 END IF;
433 --
434 RETURN lc_ui_num;
435 --
436 EXCEPTION
437 WHEN NO_DATA_FOUND THEN
438 IF gb_debug THEN
439 hr_utility.set_location('No Data Found Exception in get_ui_num',10);
440 END IF;
441 RETURN NULL;
442 --
443 WHEN OTHERS THEN
444 hr_utility.set_location('Error in '||lc_procedure,999999);
445 RAISE;
446 RETURN NULL;
447 END get_ui_num;
448 --
449 FUNCTION get_element_set_id(p_element_set_name IN VARCHAR2
450 ,p_business_group_id IN NUMBER
451 ,p_legislation_code IN VARCHAR2)
452 --***********************************************************************
453 -- FUNCTION
454 -- get_element_set_id
455 --
456 -- DESCRIPTION
457 -- To Retrive life element_set_id
458 --
459 -- ACCESS
460 -- PRIVATE
461 --
462 -- PREREQUISITES
463 -- None
464 --
465 -- CALLED BY
466 -- archive_code
467 --************************************************************************
468 RETURN NUMBER
469 IS
470 --
471 CURSOR csr_ele_set
472 IS
473 SELECT MIN(pes.element_set_id)
474 FROM pay_element_sets PES
475 WHERE PES.element_set_name = p_element_set_name
476 AND NVL(PES.business_group_id,p_business_group_id) = p_business_group_id
477 AND NVL(PES.legislation_code,p_legislation_code) = p_legislation_code;
478 --
479 ln_element_set_id NUMBER;
480 lc_procedure VARCHAR2(200);
481 --
482 BEGIN
483 --
484 --
485 gb_debug := hr_utility.debug_enabled;
486 --
487 IF gb_debug THEN
488 lc_procedure := gc_package||'RANGE_CODE';
489 hr_utility.set_location('Entering '||lc_procedure,1);
490 END IF;
491 --
492 OPEN csr_ele_set;
493 FETCH csr_ele_set into ln_element_set_id;
494 CLOSE csr_ele_set;
495 --
496 RETURN ln_element_set_id;
497 --
498 END get_element_set_id;
499 --
500 FUNCTION get_insert_action_info( p_insert_wage_info IN gt_insert_wage_info)
501 --************************************************************************
502 -- FUNCTION
503 -- get_insert_action_info
504 --
505 -- DESCRIPTION
506 -- Removes redundant insurance period if employee payroll has been in the
507 -- middle of the month and payroll has been run more than once in a month
508 -- ACCESS
509 -- PRIVATE
510 --
511 -- PREREQUISITES
512 -- None
513 --
514 -- CALLED BY
515 -- proc_sal_arch
516 --************************************************************************
517 RETURN gt_insert_wage_info
518 AS
519 --
520 lt_res_tb gt_insert_wage_info;
521 lc_procedure VARCHAR2(200);
522 ln_count NUMBER;
523 ld_insured_start_date per_time_periods.start_date%TYPE;
524 ld_insured_end_date per_time_periods.end_date%TYPE;
525 lc_duplicate_flag VARCHAR2(1):='N';
526 ln_insured_days NUMBER;
527 ln_ins_rows NUMBER:=0;
528 ln_row_count NUMBER:=0;
529 lc_final_flag VARCHAR2(1) DEFAULT 'N';
530 ld_final_insured_date DATE;
531 --
532 BEGIN
533 --
534 gb_debug := hr_utility.debug_enabled;
535 --
536 IF gb_debug THEN
537 lc_procedure := gc_package||'get_insert_action_info';
538 hr_utility.set_location('Entering '||lc_procedure,1);
539 END IF;
540 --
541 ln_count := p_insert_wage_info.count;
542 --
543 IF gb_debug THEN
544 hr_utility.set_location('p_insert_wage_info count = '||ln_count,1);
545 END IF;
546 --
547 IF ln_count=1 THEN
548 --
549 lt_res_tb := p_insert_wage_info;
550 --
551 ELSIF ln_count > 1 THEN
552 --
553 FOR i in p_insert_wage_info.first..p_insert_wage_info.last
554 --
555 LOOP
556 --
557 EXIT WHEN (lc_final_flag = 'Y');
558 --
559 IF i > 0 THEN
560 --
561 --Checking whether insured periods are same when payroll has been changed
562 --
563 IF ( (TRUNC(p_insert_wage_info(i).insured_start_date) = TRUNC(p_insert_wage_info(i-1).insured_start_date))
564 AND (TRUNC(p_insert_wage_info(i).insured_end_date) = TRUNC(p_insert_wage_info(i-1).insured_end_date)) ) THEN
565 --
566 IF ( i < p_insert_wage_info.last) THEN
567 --
568 ld_insured_start_date := p_insert_wage_info(i+1).insured_start_date;
569 ld_insured_end_date := p_insert_wage_info(i+1).insured_end_date;
570 ln_insured_days := p_insert_wage_info(i+1).insured_days;
571 lc_duplicate_flag := 'Y';
572 --
573 ELSE
574 --
575 ld_insured_start_date := NULL;
576 ld_insured_end_date := NULL;
577 ln_insured_days := NULL;
578 --
579 END IF;
580 --
581 ELSE
582 --
583 IF (lc_duplicate_flag = 'Y') THEN
584 --
585 IF (i < p_insert_wage_info.last) THEN
586 --
587 ld_insured_start_date := p_insert_wage_info(i+1).insured_start_date;
588 ld_insured_end_date := p_insert_wage_info(i+1).insured_end_date;
589 ln_insured_days := p_insert_wage_info(i+1).insured_days;
590 --
591 ELSE
592 --
593 ld_insured_start_date := NULL;
594 ld_insured_end_date := NULL;
595 ln_insured_days := NULL;
596 --
597 END IF;
598 --
599 ELSE
600 --
601 ld_insured_start_date := p_insert_wage_info(i).insured_start_date;
602 ld_insured_end_date := p_insert_wage_info(i).insured_end_date;
603 ln_insured_days := p_insert_wage_info(i).insured_days;
604 --
605 END IF;
606 --
607 END IF;
608 --
609 ELSE
610 --
611 ld_insured_start_date := p_insert_wage_info(i).insured_start_date;
612 ld_insured_end_date := p_insert_wage_info(i).insured_end_date;
613 ln_insured_days := p_insert_wage_info(i).insured_days;
614 --
615 END IF;
616 --
617 IF ( (NVL(ln_insured_days,0) >= 11) AND (p_insert_wage_info(i).exclude_period = 'N') ) THEN
618 --
619 ln_ins_rows := ln_ins_rows +1;
620 --
621 END IF;
622 --
623 IF p_insert_wage_info(i).exclude_period = 'N' THEN
624 --
625 ln_row_count := ln_row_count + 1;
626 --
627 END IF;
628 --
629 IF ((ln_ins_rows = gn_output_period) OR (ln_row_count = 24)) THEN
630 --
631 ld_final_insured_date := ld_insured_start_date;
632
633 --
634 END IF;
635 --
636 IF TRUNC(ld_final_insured_date) BETWEEN p_insert_wage_info(i).period_start_date AND p_insert_wage_info(i).period_end_date THEN
637 --
638 lc_final_flag := 'Y';
639 --
640 END IF;
641 --
642 IF ((ln_ins_rows > gn_output_period) OR (ln_row_count > 24)) THEN
643 --
644 ld_insured_start_date := NULL;
645 ld_insured_end_date := NULL;
646 ln_insured_days := NULL;
647 --
648 END IF;
649 --
650 lt_res_tb(i).payment_date := p_insert_wage_info(i).payment_date; -- Payment Date
651 lt_res_tb(i).insured_start_date := ld_insured_start_date; -- Insured Period Start Date
652 lt_res_tb(i).insured_end_date := ld_insured_end_date; -- Insured Period End Date
653 lt_res_tb(i).insured_days := ln_insured_days ; -- Insured Period Base Days
654 lt_res_tb(i).period_start_date := p_insert_wage_info(i).period_start_date; -- Pay Period Start Date
655 lt_res_tb(i).period_end_date := p_insert_wage_info(i).period_end_date; -- Pay Period End Date
656 lt_res_tb(i).base_days := p_insert_wage_info(i).base_days; -- Pay Period Base Days
657 lt_res_tb(i).wage_amount_a := p_insert_wage_info(i).wage_amount_a; -- Wage Amount A
658 lt_res_tb(i).wage_amount_b := p_insert_wage_info(i).wage_amount_b; -- Wage Amount B
659 lt_res_tb(i).remarks := p_insert_wage_info(i).remarks; -- Remarks
660 lt_res_tb(i).exclude_period := p_insert_wage_info(i).exclude_period; -- Exclude Period
661 lt_res_tb(i).line_number := p_insert_wage_info(i).line_number; -- Line Number
662 --
663 ld_insured_start_date := NULL;
664 ld_insured_end_date := NULL;
665 ln_insured_days := NULL;
666 --
667 END LOOP;
668 END IF;
669 --
670 RETURN lt_res_tb;
671 --
672 IF gb_debug THEN
673 hr_utility.set_location('Leaving '||lc_procedure,1);
674 END IF;
675 --
676 EXCEPTION
677 WHEN NO_DATA_FOUND THEN
678 IF gb_debug THEN
679 hr_utility.set_location('No Data Found Exception in ' ||lc_procedure,10);
680 END IF;
681 RETURN lt_res_tb;
682 END get_insert_action_info;
683 --
684
685 PROCEDURE proc_insert_row( p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
686 ,p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE
687 ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
688 ,p_effective_date IN pay_payroll_actions.effective_date%TYPE
689 ,p_termination_date IN per_periods_of_service.actual_termination_date%TYPE
690 ,p_payroll_id IN pay_payrolls_f.payroll_id%TYPE
691 ,p_hire_date IN per_periods_of_service.date_start%TYPE
692 ,p_last_std_process_date IN per_periods_of_service.last_standard_process_date%TYPE
693 ,p_line_number OUT NOCOPY NUMBER)
694 IS
695 --***************************************************************************
696 -- PROCEDURE
697 -- proc_insert_row
698 --
699 -- DESCRIPTION
700 -- This procedure is used to process non payroll data
701 --
702 -- ACCESS
703 -- PUBLIC
704 --
705 -- PARAMETERS
706 --==========
707 -- NAME TYPE DESCRIPTION
708 ------------------- -------- ---------------------------------------
709 -- p_payroll_action_id IN This parameter passes Payroll Action Id
710 -- p_assignment_id IN This parameter passes Assignment Id
711 -- p_effective_date IN This Parameter Passes Effective Date
712 -- p_termination_date IN This Parameter passes the Termination Date
713 -- p_payroll_id IN This Parameter passes the Payroll Id
714 -- p_hire_date IN This parameter passes the hire date
715 -- p_last_std_process_date IN This parameter passes the last standard process date
716 -- p_ins_start_date OUT Passes back Insurance start date
717 -- PREREQUISITES
718 -- None
719 --
720 -- CALLED BY
721 -- None
722 --***********************************************************************
723 lc_procedure VARCHAR2(200);
724 --
725 CURSOR lcu_period_for_no_assact
726 IS
727 SELECT PTP.start_date,
728 PTP.end_date
729 FROM per_time_periods PTP
730 WHERE PTP.payroll_id = p_payroll_id
731 AND p_termination_date BETWEEN PTP.start_date AND PTP.end_date
732 ORDER BY PTP.start_date DESC;
733 --
734 ln_diff_mth NUMBER;
735 ld_effective_date pay_payroll_actions.effective_date%TYPE;
736 ld_date_earned pay_payroll_actions.date_earned%TYPE;
737 ld_period_start_date per_time_periods.start_date%TYPE;
738 ld_period_end_date per_time_periods.end_date%TYPE;
739 ld_insured_start_date per_time_periods.start_date%TYPE;
740 ld_insured_end_date per_time_periods.end_date%TYPE;
741 ln_line_number NUMBER:=0;
742 ln_action_info_id pay_action_information.action_information_id%TYPE;
743 ln_obj_version_num pay_action_information.object_version_number%TYPE;
744 --
745 lr_lcu_period_for_no_assact lcu_period_for_no_assact%rowtype;
746 --
747 BEGIN
748 --
749 gb_debug := hr_utility.debug_enabled;
750 --
751 IF gb_debug THEN
752 lc_procedure := gc_package||'proc_insert_row';
753 hr_utility.set_location('Entering '||lc_procedure,1);
754 END IF;
755 --
756 OPEN lcu_period_for_no_assact;
757 --
758 FETCH lcu_period_for_no_assact INTO lr_lcu_period_for_no_assact;
759 --
760 ln_diff_mth := (TO_NUMBER(TO_CHAR(lr_lcu_period_for_no_assact.start_date,'YYYY'))
761 - TO_NUMBER(TO_CHAR(p_termination_date,'YYYY'))) * 12
762 + (TO_NUMBER(TO_CHAR(lr_lcu_period_for_no_assact.start_date,'MM'))
763 - TO_NUMBER(TO_CHAR(p_termination_date,'MM'))); --#Bug 9653516
764 --
765 hr_utility.set_location('ln_diff_mth '||lc_procedure,20);
766 --
767 -- Wage Payment Days --
768 --
769 IF TO_CHAR(lr_lcu_period_for_no_assact.start_date,'MM') = TO_CHAR(lr_lcu_period_for_no_assact.end_date,'MM') THEN
770 --
771 ld_insured_start_date := add_months(p_termination_date + 1,ln_diff_mth -1); --#Bug 9653516
772 ld_insured_end_date := add_months(ld_insured_start_date,1) - 1; --#Bug 9653516
773 --
774 ELSE
775 --
776 IF TO_CHAR(lr_lcu_period_for_no_assact.start_date,'YYYY') = TO_CHAR(lr_lcu_period_for_no_assact.end_date,'YYYY') THEN --#Bug 9732294
777 --
778 ld_insured_start_date := add_months(p_termination_date + 1,ln_diff_mth); --#Bug 9702153
779 ld_insured_end_date := add_months(ld_insured_start_date,1) - 1; --#Bug 9702153
780 --
781 ELSE
782 --
783 ld_insured_start_date := add_months(p_termination_date + 1,ln_diff_mth -1); --#Bug 9732294
784 ld_insured_end_date := add_months(ld_insured_start_date,1) - 1; --#Bug 9732294
785 --
786 END IF;
787 --
788 END IF;
789 --
790 ln_line_number := ln_line_number + 1;
791 --
792 IF TRUNC(p_hire_date) > TRUNC(ld_insured_start_date) THEN
793 --
794 ld_insured_start_date := p_hire_date;
795 --
796 END IF;
797 --
798 IF TRUNC(p_hire_date) > TRUNC(ld_insured_end_date) THEN
799 --
800 ld_insured_end_date:= p_hire_date;
801 --
802 END IF;
803 --
804 IF TRUNC(p_hire_date) > TRUNC(lr_lcu_period_for_no_assact.start_date) THEN
805 --
806 ld_period_start_date := p_hire_date;
807 --
808 ELSE
809 --
810 ld_period_start_date := lr_lcu_period_for_no_assact.start_date;
811 --
812 END IF;
813 --
814 IF TRUNC(p_termination_date) BETWEEN lr_lcu_period_for_no_assact.start_date AND lr_lcu_period_for_no_assact.end_date THEN
815 --
816 ld_period_end_date := TRUNC(p_termination_date);
817 --
818 ELSE
819 --
820 ld_period_end_date := lr_lcu_period_for_no_assact.end_date;
821 --
822 END IF;
823 --
824 IF gb_debug THEN
825 hr_utility.set_location('Insured Period Start Date '||ld_insured_start_date,12);
826 hr_utility.set_location('Insured Period End Date '||ld_insured_end_date,13);
827 hr_utility.set_location('Pay Period Start Date '||ld_period_start_date,15);
828 hr_utility.set_location('Pay Period End Date '||ld_period_end_date,16);
829 END IF;
830 --
831 pay_action_information_api.create_action_information
832 (p_action_information_id => ln_action_info_id
833 , p_action_context_id => p_assignment_action_id
834 , p_action_context_type => 'AAP'
835 , p_object_version_number => ln_obj_version_num
836 , p_effective_date => p_effective_date
837 , p_assignment_id => p_assignment_id
838 , p_source_id => NULL
839 , p_source_text => NULL
840 , p_action_information_category => 'JP_UITE_SAL'
841 , p_action_information1 => fnd_date.date_to_canonical(ld_period_end_date) -- Payment Date
842 , p_action_information2 => fnd_date.date_to_canonical(ld_insured_start_date) -- Insured Period Start Date
843 , p_action_information3 => fnd_date.date_to_canonical(ld_insured_end_date) -- Insured Period End Date
844 , p_action_information4 => NULL -- Wage Payment Base Days
845 , p_action_information5 => fnd_date.date_to_canonical(ld_period_start_date) -- Pay Period Start Date
846 , p_action_information6 => fnd_date.date_to_canonical(ld_period_end_date) -- Pay Period End Date
847 , p_action_information7 => NULL -- Base Days
848 , p_action_information8 => NULL -- Wage Amount A
849 , p_action_information9 => NULL -- Wage Amount B
850 , p_action_information10 => NULL -- Total Amount of Salary
851 , p_action_information11 => NULL -- Remarks
852 , p_action_information12 => 'N' -- Exclude Period
853 , p_action_information13 => fnd_number.number_to_canonical(ln_line_number) -- Line Number
854 );
855 --
856 CLOSE lcu_period_for_no_assact;
857 --
858 p_line_number := ln_line_number;
859 --
860 IF gb_debug THEN
861 hr_utility.set_location('Leaving '||lc_procedure,1);
862 END IF;
863 --
864 EXCEPTION
865 --
866 WHEN gc_exception THEN
867 IF gb_debug THEN
868 hr_utility.set_location('Error in '||lc_procedure,999999);
869 END IF;
870 RAISE;
871 --
872 WHEN OTHERS THEN
873 RAISE gc_exception;
874 --
875 END proc_insert_row;
876 --
877 PROCEDURE proc_sal_arch( p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
878 ,p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE
879 ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
880 ,p_effective_date IN pay_payroll_actions.effective_date%TYPE
881 ,p_termination_date IN per_periods_of_service.actual_termination_date%TYPE
882 ,p_payroll_id IN pay_payrolls_f.payroll_id%TYPE
883 ,p_hire_date IN per_periods_of_service.date_start%TYPE
884 ,p_last_std_process_date IN per_periods_of_service.last_standard_process_date%TYPE
885 ,p_ins_start_date OUT NOCOPY per_time_periods.start_date%TYPE)
886 --***************************************************************************
887 -- PROCEDURE
888 -- proc_sal_arch
889 --
890 -- DESCRIPTION
891 -- This procedure is used to process salary archive
892 --
893 -- ACCESS
894 -- PUBLIC
895 --
896 -- PARAMETERS
897 --==========
898 -- NAME TYPE DESCRIPTION
899 ------------------- -------- ---------------------------------------
900 -- p_payroll_action_id IN This parameter passes Payroll Action Id
901 -- p_assignment_id IN This parameter passes Assignment Idter passes the Termination Date
902 -- p_payroll_id IN This Parameter passes the Payroll Id
903 -- p_hire_date IN This parameter passes the hire date
904 -- p_last_std_process_date IN This parameter passes the last standard process date
905 -- p_ins_start_date OUT Passes back Insurance start date
906 -- PREREQUISITES
907 -- None
908 --
909 -- CALLED BY
910 -- archive_code
911 --***********************************************************************
912 IS
913 --
914 CURSOR lcu_assct
915 IS
916 SELECT PAA.assignment_action_id,
917 PPA.effective_date,
918 PPA.date_earned,
919 PTP.start_date,
920 PTP.end_date,
921 PAF.payroll_id,
922 PAF.effective_start_date,
923 PAF.effective_end_date
924 FROM pay_assignment_actions PAA
925 ,pay_payroll_actions PPA
926 ,per_time_periods PTP
927 ,per_assignments_f PAF
928 WHERE PAA.assignment_id = p_assignment_id
929 AND PAF.assignment_id = PAA.assignment_id
930 AND PAA.action_status = 'C'
931 AND PPA.payroll_action_id = PAA.payroll_action_id
932 AND PPA.effective_date BETWEEN add_months(p_termination_date +1,gn_max_period * -1)
933 AND PPA.effective_date
934 AND TRUNC(PTP.start_date) <= TRUNC(p_termination_date)
935 AND PPA.element_set_id = gn_sal_ele_set_id
936 AND PPA.action_type in ('R','Q','G','L')
937 AND NOT EXISTS(
938 SELECT null
939 FROM pay_action_interlocks PAI,
940 pay_assignment_actions PAAI,
941 pay_payroll_actions PPAI
942 WHERE PAI.locked_action_id = PAA.assignment_action_id
943 AND PAAI.assignment_action_id = PAI.locking_action_id
944 AND PPAI.payroll_action_id = PAAI.payroll_action_id
945 AND PPAI.action_type = 'V')
946 AND PTP.payroll_id = PPA.payroll_id
947 AND PPA.date_earned BETWEEN PTP.start_date AND PTP.end_date
948 AND PPA.date_earned BETWEEN PAF.effective_start_date AND PAF.effective_end_date
949 ORDER BY PAA.assignment_action_id DESC;
950 --
951 CURSOR lcu_assct_effective
952 IS
953 SELECT PAA.assignment_action_id,
954 PPA.effective_date,
955 PPA.date_earned,
956 PTP.start_date,
957 PTP.end_date,
958 PAF.payroll_id,
959 PAF.effective_start_date,
960 PAF.effective_end_date
961 FROM pay_assignment_actions PAA
962 ,pay_payroll_actions PPA
963 ,per_time_periods PTP
964 ,per_assignments_f PAF
965 WHERE PAA.assignment_id = p_assignment_id
966 AND PAF.assignment_id = PAA.assignment_id
967 AND PAA.action_status = 'C'
968 AND PPA.payroll_action_id = PAA.payroll_action_id
969 AND PPA.effective_date BETWEEN add_months(p_termination_date +1,gn_max_period * -1)
970 AND PPA.effective_date
971 AND TRUNC(PTP.start_date) <= TRUNC(p_termination_date)
972 AND PPA.element_set_id = gn_sal_ele_set_id
973 AND PPA.action_type in ('R','Q','G','L')
974 AND NOT EXISTS(
975 SELECT null
976 FROM pay_action_interlocks PAI,
977 pay_assignment_actions PAAI,
978 pay_payroll_actions PPAI
979 WHERE PAI.locked_action_id = PAA.assignment_action_id
980 AND PAAI.assignment_action_id = PAI.locking_action_id
981 AND PPAI.payroll_action_id = PAAI.payroll_action_id
982 AND PPAI.action_type = 'V')
983 AND PTP.payroll_id = PPA.payroll_id
984 AND PPA.effective_date BETWEEN PTP.start_date AND PTP.end_date
985 AND PPA.effective_date BETWEEN PAF.effective_start_date AND PAF.effective_end_date
986 ORDER BY PAA.assignment_action_id DESC; -- Bug 9693280
987 --
988 CURSOR lcu_period_for_no_assact
989 IS
990 SELECT PTP.start_date,
991 PTP.end_date
992 FROM per_time_periods PTP
993 WHERE PTP.payroll_id = p_payroll_id
994 AND PTP.start_date BETWEEN add_months(p_termination_date +1,gn_max_period * -1)
995 AND NVL(p_last_std_process_date,PTP.start_date)
996 AND PTP.start_date <= p_termination_date
997 ORDER BY PTP.start_date DESC;
998 --
999 CURSOR lcu_get_bal_id(p_balance_name pay_balance_types.balance_name%TYPE
1000 ,p_database_item_suffix pay_balance_dimensions.database_item_suffix%TYPE)
1001 IS
1002 SELECT PDB.defined_balance_id
1003 ,PBT.balance_type_id
1004 FROM pay_balance_types PBT
1005 ,pay_balance_dimensions PBD
1006 ,pay_defined_balances PDB
1007 WHERE PBT.balance_name = p_balance_name
1008 AND PBD.database_item_suffix = p_database_item_suffix
1009 AND PBT.balance_type_id = PDB.balance_type_id
1010 AND PBD.balance_dimension_id = PDB.balance_dimension_id;
1011 --
1012 CURSOR lcu_balance_asg_run(p_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE
1013 ,p_balance_type_id pay_balance_types.balance_type_id%TYPE)
1014 IS
1015 SELECT 'Y'
1016 FROM pay_assignment_actions ASSACT,
1017 pay_payroll_actions PACT,
1018 pay_balance_feeds_f FEED,
1019 pay_run_results RR,
1020 pay_run_result_values TARGET
1021 WHERE ASSACT.assignment_action_id = p_assignment_action_id
1022 AND PACT.payroll_action_id = ASSACT.payroll_action_id
1023 AND RR.assignment_action_id = ASSACT.assignment_action_id
1024 AND RR.status in ('P','PA')
1025 AND PACT.action_type in ('R','Q','G','L')
1026 AND TARGET.run_result_id = RR.run_result_id
1027 AND FEED.input_value_id = TARGET.input_value_id
1028 AND FEED.balance_type_id = p_balance_type_id
1029 AND PACT.effective_date BETWEEN FEED.effective_start_date AND FEED.effective_end_date;
1030 --
1031 CURSOR lcu_balance_asg_prev_run(p_balance_type_id pay_balance_types.balance_type_id%TYPE
1032 ,p_date_earned pay_payroll_actions.date_earned%TYPE
1033 )
1034 IS
1035 SELECT ASSACT.assignment_action_id
1036 FROM pay_assignment_actions ASSACT,
1037 pay_payroll_actions PACT,
1038 pay_balance_feeds_f FEED,
1039 pay_run_results RR,
1040 pay_run_result_values TARGET
1041 WHERE PACT.payroll_action_id = ASSACT.payroll_action_id
1042 AND RR.assignment_action_id = ASSACT.assignment_action_id
1043 AND RR.status in ('P','PA')
1044 AND PACT.action_type in ('R','Q','G','L')
1045 AND TARGET.run_result_id = RR.run_result_id
1046 AND FEED.input_value_id = TARGET.input_value_id
1047 AND FEED.balance_type_id = p_balance_type_id
1048 AND PACT.element_set_id = gn_sal_ele_set_id
1049 AND PACT.date_earned BETWEEN FEED.effective_start_date AND FEED.effective_end_date
1050 AND PACT.date_earned BETWEEN (p_date_earned+1) AND add_months(p_date_earned,1)
1051 AND ASSACT.assignment_id= p_assignment_id; -- #Bug 9732572
1052 --
1053 TYPE t_assact_rec is record(
1054 assignment_action_id number,
1055 effective_date date,
1056 date_earned date,
1057 period_start_date date,
1058 period_end_date date,
1059 payroll_id number,
1060 payroll_change_st_dt date,
1061 payroll_change_end_dt date
1062 );
1063 --
1064 TYPE t_assact_tbl IS TABLE OF t_assact_rec INDEX BY BINARY_INTEGER;
1065 --
1066 lt_assact_tbl t_assact_tbl;
1067 --
1068 lc_procedure VARCHAR2(200);
1069 lc_exclude_period VARCHAR2(10) DEFAULT 'N';
1070 lc_remarks VARCHAR2(60);
1071 i NUMBER := 0;
1072 j NUMBER;
1073 --
1074 ln_action_info_id pay_action_information.action_information_id%TYPE;
1075 ln_obj_version_num pay_action_information.object_version_number%TYPE;
1076 ln_line_number NUMBER:=0;
1077 ln_diff_mth NUMBER;
1078 ln_assact_tbl_cnt NUMBER := 0;
1079 ln_wage_pay_days NUMBER;
1080 ln_base_days NUMBER;
1081 ln_wage_amount_a NUMBER;
1082 ln_wage_amount_b NUMBER;
1083 ln_total_wage NUMBER;
1084 ln_bpd_balance_id pay_defined_balances.defined_balance_id%TYPE;
1085 ln_sal_a_bal_id pay_defined_balances.defined_balance_id%TYPE;
1086 ln_sal_b_bal_id pay_defined_balances.defined_balance_id%TYPE;
1087 ln_sal_a_prev_bal_id pay_defined_balances.defined_balance_id%TYPE;
1088 ln_sal_b_prev_bal_id pay_defined_balances.defined_balance_id%TYPE;
1089 ln_bpd_baltyp_id pay_balance_types.balance_type_id%TYPE;
1090 ln_sal_a_baltyp_id pay_balance_types.balance_type_id%TYPE;
1091 ln_sal_b_baltyp_id pay_balance_types.balance_type_id%TYPE;
1092 ln_sal_a_prev_baltyp_id pay_balance_types.balance_type_id%TYPE;
1093 ln_sal_b_prev_baltyp_id pay_balance_types.balance_type_id%TYPE;
1094 ln_wage_dis_count NUMBER:=0; -- Count excluding payment days less than 11 Days
1095 ln_line_count NUMBER;
1096 ln_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
1097 ln_prev_ass_a_act_id pay_assignment_actions.assignment_action_id%TYPE;
1098 ln_prev_ass_b_act_id pay_assignment_actions.assignment_action_id%TYPE;
1099 --
1100 ln_sal_action_id pay_assignment_actions.assignment_action_id%TYPE;
1101 ld_effective_date pay_payroll_actions.effective_date%TYPE;
1102 ld_date_earned pay_payroll_actions.date_earned%TYPE;
1103 ld_period_start_date per_time_periods.start_date%TYPE;
1104 ld_period_end_date per_time_periods.end_date%TYPE;
1105 ld_insured_start_date per_time_periods.start_date%TYPE;
1106 ld_insured_end_date per_time_periods.end_date%TYPE;
1107 ld_prev_ins_end_date per_time_periods.end_date%TYPE;
1108 lc_wage_a_flag VARCHAR2(10) DEFAULT 'N';
1109 lc_wage_b_flag VARCHAR2(10) DEFAULT 'N';
1110 lc_sal_a_bal_flag VARCHAR2(1) DEFAULT 'N';
1111 lc_sal_b_bal_flag VARCHAR2(1) DEFAULT 'N';
1112 --
1113 lt_insert_wage_info gt_insert_wage_info;
1114 lt_insert_action_info gt_insert_wage_info;
1115 --
1116 lr_lcu_period_for_no_assact lcu_period_for_no_assact%rowtype;
1117 --
1118 BEGIN
1119 --
1120 gb_debug := hr_utility.debug_enabled;
1121 --
1122 IF gb_debug THEN
1123 lc_procedure := gc_package||'proc_sal_arch';
1124 hr_utility.set_location('Entering '||lc_procedure,1);
1125 END IF;
1126 --
1127 -- Fetching balnce id for Wage Payment days
1128 --
1129 OPEN lcu_get_bal_id(p_balance_name => 'B_SAL_TRM_REPORT_WAGE_PAY_BASE_DAYS'
1130 ,p_database_item_suffix => '_ASG_RUN');
1131 FETCH lcu_get_bal_id INTO ln_bpd_balance_id
1132 ,ln_bpd_baltyp_id;
1133 CLOSE lcu_get_bal_id;
1134 --
1135 -- Fetching balnce id for salary A
1136 --
1137 OPEN lcu_get_bal_id(p_balance_name => 'B_SAL_TRM_REPORT_WAGE_A'
1138 ,p_database_item_suffix => '_ASG_PTD');
1139 FETCH lcu_get_bal_id INTO ln_sal_a_bal_id
1140 ,ln_sal_a_baltyp_id;
1141 CLOSE lcu_get_bal_id;
1142 --
1143 -- Fetching balnce id for salary B
1144 --
1145 OPEN lcu_get_bal_id(p_balance_name => 'B_SAL_TRM_REPORT_WAGE_B'
1146 ,p_database_item_suffix => '_ASG_PTD');
1147 FETCH lcu_get_bal_id INTO ln_sal_b_bal_id
1148 ,ln_sal_b_baltyp_id;
1149 CLOSE lcu_get_bal_id;
1150 --
1151 -- Fetching balnce id for previous month salary A
1152 --
1153 OPEN lcu_get_bal_id(p_balance_name => 'B_SAL_TRM_REPORT_WAGE_A_PREV_MTH'
1154 ,p_database_item_suffix => '_ASG_PTD');
1155 FETCH lcu_get_bal_id INTO ln_sal_a_prev_bal_id
1156 ,ln_sal_a_prev_baltyp_id;
1157 CLOSE lcu_get_bal_id;
1158 --
1159 -- Fetching balnce id for previous month salary B
1160 --
1161 OPEN lcu_get_bal_id(p_balance_name => 'B_SAL_TRM_REPORT_WAGE_B_PREV_MTH'
1162 ,p_database_item_suffix => '_ASG_PTD');
1163 FETCH lcu_get_bal_id INTO ln_sal_b_prev_bal_id
1164 ,ln_sal_b_prev_baltyp_id;
1165 CLOSE lcu_get_bal_id;
1166 --
1167 lt_assact_tbl.DELETE;
1168 --
1169 IF (gc_santei_base = gc_date_earned) THEN
1170 --
1171 OPEN lcu_assct;
1172 --
1173 LOOP
1174 --
1175 FETCH lcu_assct INTO lt_assact_tbl(ln_assact_tbl_cnt);
1176 EXIT WHEN lcu_assct%NOTFOUND;
1177 --
1178 ln_assact_tbl_cnt := ln_assact_tbl_cnt + 1;
1179 --
1180 END LOOP;
1181 --
1182 CLOSE lcu_assct;
1183 --
1184 ELSE
1185 --
1186 OPEN lcu_assct_effective;
1187 --
1188 LOOP
1189 --
1190 FETCH lcu_assct_effective INTO lt_assact_tbl(ln_assact_tbl_cnt);
1191 EXIT WHEN lcu_assct_effective%NOTFOUND;
1192 --
1193 ln_assact_tbl_cnt := ln_assact_tbl_cnt + 1;
1194 --
1195 END LOOP;
1196 --
1197 CLOSE lcu_assct_effective;
1198 --
1199 END IF;
1200 --
1201 IF lt_assact_tbl.COUNT > 0 THEN
1202 --
1203 <<assact_loop>>
1204 FOR assact_cnt IN lt_assact_tbl.FIRST..lt_assact_tbl.LAST LOOP
1205 --
1206 ln_line_number := ln_line_number + 1;
1207 --
1208 -- Fetch difference between Payperiod months and Termination date to calculate insurance period --
1209 --
1210 IF ln_line_number = 1 AND TRUNC(p_termination_date) NOT BETWEEN lt_assact_tbl(assact_cnt).period_start_date AND lt_assact_tbl(assact_cnt).period_end_date THEN
1211 --
1212 proc_insert_row( p_assignment_action_id => p_assignment_action_id
1213 ,p_payroll_action_id => p_payroll_action_id
1214 ,p_assignment_id => p_assignment_id
1215 ,p_effective_date => p_effective_date
1216 ,p_termination_date => p_termination_date
1217 ,p_payroll_id => p_payroll_id
1218 ,p_hire_date => p_hire_date
1219 ,p_last_std_process_date => p_last_std_process_date
1220 ,p_line_number => ln_line_count);
1221 ln_line_number := ln_line_number + NVL(ln_line_count,0);
1222 --
1223 END IF;
1224 --
1225 -- Period Start Date and Period End date
1226 --
1227 IF TRUNC(p_hire_date) > TRUNC(lt_assact_tbl(assact_cnt).period_start_date) THEN
1228 --
1229 ld_period_start_date := p_hire_date;
1230 --
1231 ELSE
1232 --
1233 IF assact_cnt < lt_assact_tbl.LAST THEN
1234 --
1235 IF (lt_assact_tbl(assact_cnt).payroll_id <> lt_assact_tbl(assact_cnt+1).payroll_id) THEN
1236 --
1237 IF lt_assact_tbl(assact_cnt).payroll_change_st_dt > lt_assact_tbl(assact_cnt).period_start_date THEN
1238 --
1239 ld_period_start_date := lt_assact_tbl(assact_cnt).payroll_change_st_dt;
1240 --
1241 ELSE
1242 --
1243 ld_period_start_date := lt_assact_tbl(assact_cnt).period_start_date;
1244 --
1245 END IF;
1246 --
1247 ELSE
1248 --
1249 ld_period_start_date := lt_assact_tbl(assact_cnt).period_start_date;
1250 --
1251 END IF;
1252 --
1253 ELSE
1254 --
1255 ld_period_start_date := lt_assact_tbl(assact_cnt).period_start_date;
1256 --
1257 END IF;
1258 --
1259 END IF;
1260 --
1261 IF TRUNC(p_termination_date) BETWEEN lt_assact_tbl(assact_cnt).period_start_date AND lt_assact_tbl(assact_cnt).period_end_date THEN
1262 --
1263 ld_period_end_date := TRUNC(p_termination_date);
1264 --
1265 ELSE
1266 --
1267 IF assact_cnt > lt_assact_tbl.FIRST THEN
1268 --
1269 IF (lt_assact_tbl(assact_cnt).payroll_id <> lt_assact_tbl(assact_cnt-1).payroll_id) THEN
1270 --
1271 IF lt_assact_tbl(assact_cnt).payroll_change_end_dt < lt_assact_tbl(assact_cnt).period_end_date THEN
1272 --
1273 ld_period_end_date := lt_assact_tbl(assact_cnt).payroll_change_end_dt;
1274 --
1275 ELSE
1276 --
1277 ld_period_end_date := lt_assact_tbl(assact_cnt).period_end_date;
1278 --
1279 END IF;
1280 --
1281 ELSE
1282 --
1283 ld_period_end_date := lt_assact_tbl(assact_cnt).period_end_date;
1284 --
1285 END IF;
1286 --
1287 ELSE
1288 --
1289 ld_period_end_date := lt_assact_tbl(assact_cnt).period_end_date;
1290 --
1291 END IF;
1292 --
1293 END IF;
1294 --
1295 -- Checking Maximum period of 4 years or no of display periods greater than Santei base period
1296 --
1297 EXIT WHEN ((TRUNC(MONTHS_BETWEEN(p_termination_date,ld_period_start_date)/12)>=4 ));
1298 --
1299 ln_assignment_action_id := lt_assact_tbl(assact_cnt).assignment_action_id;
1300 ln_base_days := pay_jp_balance_pkg.get_entry_value_char('COM_TRM_REPORT_WAGE_PAY_INFO','PAY_PERIOD_BASE_DAYS',p_assignment_id,lt_assact_tbl(assact_cnt).date_earned);
1301 --
1302 IF ln_base_days IS NULL THEN
1303 --
1304 IF ln_assignment_action_id IS NOT NULL THEN --Bug 9693280
1305 --
1306 ln_base_days := pay_jp_balance_pkg.get_balance_value(ln_bpd_balance_id,ln_assignment_action_id);
1307 --
1308 END IF;
1309 --
1310 IF ( NVL(ln_base_days,0) = 0)THEN -- #Bug No 9652251
1311 --
1312 ln_base_days := ROUND(ld_period_end_date - ld_period_start_date)+1; -- #Bug No 9648082
1313 --
1314 END IF;
1315 --
1316 END IF;
1317 --
1318 -- Insured Days
1319 --
1320 ln_diff_mth := (TO_NUMBER(TO_CHAR(ld_period_start_date,'YYYY'))
1321 - TO_NUMBER(TO_CHAR(p_termination_date,'YYYY'))) * 12
1322 + (TO_NUMBER(TO_CHAR(ld_period_start_date,'MM'))
1323 - TO_NUMBER(TO_CHAR(p_termination_date,'MM'))); --#Bug 9653516
1324 --
1325 hr_utility.set_location('ln_diff_mth = '||ln_diff_mth,20);
1326 --
1327 -- Wage Payment Days --
1328 --
1329 IF TO_CHAR(ld_period_start_date,'MM') = TO_CHAR(ld_period_end_date,'MM') THEN
1330 --
1331 ld_insured_start_date := add_months(p_termination_date + 1,ln_diff_mth -1); --#Bug 9653516
1332 ld_insured_end_date := add_months(ld_insured_start_date,1) - 1; --#Bug 9653516
1333 --
1334 ELSE
1335 --
1336 IF TO_CHAR(ld_period_start_date,'YYYY') = TO_CHAR(ld_period_end_date,'YYYY') THEN --#Bug 9732294
1337 --
1338 ld_insured_start_date := add_months(p_termination_date + 1,ln_diff_mth); --#Bug 9702153
1339 ld_insured_end_date := add_months(ld_insured_start_date,1) - 1; --#Bug 9702153
1340 --
1341 ELSE
1342 --
1343 ld_insured_start_date := add_months(p_termination_date + 1,ln_diff_mth -1); --#Bug 9732294
1344 ld_insured_end_date := add_months(ld_insured_start_date,1) - 1; --#Bug 9732294
1345 --
1346 END IF;
1347 END IF;
1348 --
1349 ln_wage_pay_days := pay_jp_balance_pkg.get_entry_value_char('COM_TRM_REPORT_WAGE_PAY_INFO','EE_PERIOD_BASE_DAYS',p_assignment_id,ld_insured_end_date);
1350 --
1351 -- Fecthing Start and End insured/pay periods
1352 --
1353 IF TRUNC(p_hire_date) > TRUNC(ld_insured_start_date) THEN
1354 --
1355 ld_insured_start_date := p_hire_date;
1356 --
1357 END IF;
1358 --
1359 IF TRUNC(p_hire_date) > TRUNC(ld_insured_end_date) THEN
1360 --
1361 ld_insured_end_date:= p_hire_date;
1362 --
1363 END IF;
1364
1365 --
1366 IF ln_wage_pay_days IS NULL THEN
1367 --
1368 IF ( TRUNC(ld_insured_start_date) = TRUNC(ld_period_start_date) AND
1369 TRUNC(ld_insured_end_date) = TRUNC(ld_period_end_date)) THEN --#Bug 9652235
1370 --
1371 ln_wage_pay_days:= ln_base_days; --#Bug 9648082
1372 --
1373 ELSE
1374 --
1375 ln_wage_pay_days := ROUND(ld_insured_end_date - ld_insured_start_date)+1; -- #Bug No 9648082
1376 --
1377 END IF;
1378 --
1379 END IF;
1380 --
1381 -- checking payrun results for balance B_SAL_TRM_REPORT_WAGE_A
1382 --
1383 IF ln_assignment_action_id IS NOT NULL THEN --Bug 9693280
1384 --
1385 OPEN lcu_balance_asg_run(p_assignment_action_id => ln_assignment_action_id
1386 ,p_balance_type_id => ln_sal_a_baltyp_id);
1387 FETCH lcu_balance_asg_run INTO lc_sal_a_bal_flag ;
1388 CLOSE lcu_balance_asg_run;
1389 --
1390 -- checking payrun results for balance B_SAL_TRM_REPORT_WAGE_B
1391 --
1392 OPEN lcu_balance_asg_run(p_assignment_action_id => ln_assignment_action_id
1393 ,p_balance_type_id => ln_sal_b_baltyp_id);
1394 FETCH lcu_balance_asg_run INTO lc_sal_b_bal_flag ;
1395 CLOSE lcu_balance_asg_run;
1396 --
1397 -- checking payrun results for balance B_SAL_TRM_REPORT_WAGE_A_PREV_MTH
1398 --
1399 OPEN lcu_balance_asg_prev_run(p_balance_type_id => ln_sal_a_prev_baltyp_id
1400 ,p_date_earned => lt_assact_tbl(assact_cnt).date_earned
1401 );
1402 FETCH lcu_balance_asg_prev_run INTO ln_prev_ass_a_act_id;
1403 CLOSE lcu_balance_asg_prev_run;
1404 --
1405 -- checking payrun results for balance B_SAL_TRM_REPORT_WAGE_B_PREV_MTH
1406 --
1407 OPEN lcu_balance_asg_prev_run(p_balance_type_id => ln_sal_b_prev_baltyp_id
1408 ,p_date_earned => lt_assact_tbl(assact_cnt).date_earned
1409 );
1410 FETCH lcu_balance_asg_prev_run INTO ln_prev_ass_b_act_id;
1411 CLOSE lcu_balance_asg_prev_run;
1412 --
1413 -- Derving Wage Amount A
1414 --
1415 ln_wage_amount_a := pay_jp_balance_pkg.get_entry_value_char('COM_TRM_REPORT_WAGE_PAY_INFO','WAGE_A',p_assignment_id,lt_assact_tbl(assact_cnt).date_earned);
1416 --
1417 IF ln_wage_amount_a IS NULL THEN
1418 --
1419 IF lc_sal_a_bal_flag = 'Y' THEN
1420 --
1421 IF ln_prev_ass_a_act_id IS NOT NULL THEN
1422 --
1423 ln_wage_amount_a := pay_jp_balance_pkg.get_balance_value(ln_sal_a_bal_id,ln_assignment_action_id)
1424 + pay_jp_balance_pkg.get_balance_value(ln_sal_a_prev_bal_id,ln_prev_ass_a_act_id);
1425 ELSE
1426 --
1427 ln_wage_amount_a := pay_jp_balance_pkg.get_balance_value(ln_sal_a_bal_id,ln_assignment_action_id);
1428 --
1429 END IF;
1430 --
1431 ELSE
1432 --
1433 IF ln_prev_ass_a_act_id IS NOT NULL THEN
1434 --
1435 ln_wage_amount_a := pay_jp_balance_pkg.get_balance_value(ln_sal_a_prev_bal_id,ln_prev_ass_a_act_id);
1436 --
1437 END IF;
1438 --
1439 END IF;
1440 --
1441 --
1442 END IF;
1443 -- Derving Wage Amount B
1444 --
1445 ln_wage_amount_b := pay_jp_balance_pkg.get_entry_value_char('COM_TRM_REPORT_WAGE_PAY_INFO','WAGE_B',p_assignment_id,lt_assact_tbl(assact_cnt).date_earned);
1446 --
1447 IF ln_wage_amount_b IS NULL THEN
1448 --
1449 IF lc_sal_b_bal_flag = 'Y' THEN
1450 --
1451 IF ln_prev_ass_b_act_id IS NOT NULL THEN
1452 --
1453 ln_wage_amount_b := pay_jp_balance_pkg.get_balance_value(ln_sal_b_bal_id,ln_assignment_action_id)
1454 + pay_jp_balance_pkg.get_balance_value(ln_sal_b_prev_bal_id,ln_prev_ass_b_act_id );
1455 --
1456 ELSE
1457 --
1458 ln_wage_amount_b := pay_jp_balance_pkg.get_balance_value(ln_sal_b_bal_id,ln_assignment_action_id);
1459 --
1460 END IF;
1461 --
1462 ELSE
1463 --
1464 IF ln_prev_ass_b_act_id IS NOT NULL THEN
1465 --
1466 ln_wage_amount_b := pay_jp_balance_pkg.get_balance_value(ln_sal_b_prev_bal_id,ln_prev_ass_b_act_id );
1467 --
1468 END IF;
1469 --
1470 END IF;
1471 --
1472 END IF;
1473 --
1474 END IF; --Bug 9693280
1475 --
1476 IF gb_debug THEN
1477 hr_utility.set_location('ln_wage_amount_a = '||ln_wage_amount_a,11);
1478 hr_utility.set_location('ln_wage_amount_b = '||ln_wage_amount_b,12);
1479 hr_utility.set_location('ln_assignment_action_id = '||ln_assignment_action_id,13);
1480 END IF;
1481 --
1482 IF (ln_wage_amount_a IS NOT NULL) THEN -- #Bug9692693
1483 --
1484 lc_wage_a_flag := 'Y';
1485 --
1486 END IF;
1487 --
1488 IF (ln_wage_amount_b IS NOT NULL) THEN -- #Bug9692693
1489 --
1490 lc_wage_b_flag := 'Y';
1491 --
1492 END IF;
1493 --
1494 lc_exclude_period := pay_jp_balance_pkg.get_entry_value_char('COM_TRM_REPORT_WAGE_PAY_INFO','EXCLUDE_PERIOD',p_assignment_id,lt_assact_tbl(assact_cnt).date_earned);
1495 lc_remarks := pay_jp_balance_pkg.get_entry_value_char('COM_TRM_REPORT_WAGE_PAY_INFO','RMKS',p_assignment_id,lt_assact_tbl(assact_cnt).date_earned);
1496 --
1497 IF gb_debug THEN
1498 hr_utility.set_location('Payment Date '||lt_assact_tbl(assact_cnt).effective_date,11);
1499 hr_utility.set_location('Insured Period Start Date '||ld_insured_start_date,12);
1500 hr_utility.set_location('Insured Period End Date '||ld_insured_end_date,13);
1501 hr_utility.set_location('Insured Period Base Days '||ln_wage_pay_days,14);
1502 hr_utility.set_location('Pay Period Start Date '||ld_period_start_date,15);
1503 hr_utility.set_location('Pay Period End Date '||ld_period_end_date,16);
1504 hr_utility.set_location('Pay Period Base Days '||ln_base_days,17);
1505 hr_utility.set_location('Wage Amount A '||ln_wage_amount_a,18);
1506 hr_utility.set_location('Wage Amount B '||ln_wage_amount_b,19);
1507 hr_utility.set_location('Wage Amount Total '||ln_total_wage,20);
1508 hr_utility.set_location('Remarks '||lc_remarks,21);
1509 hr_utility.set_location('Exclude Period '||lc_exclude_period,22);
1510 hr_utility.set_location('ln_line_number '||ln_line_number ,23);
1511 END IF;
1512 --
1513
1514 lt_insert_wage_info(i).payment_date := lt_assact_tbl(assact_cnt).effective_date; -- Payment Date
1515 lt_insert_wage_info(i).insured_start_date := ld_insured_start_date; -- Insured Period Start Date
1516 lt_insert_wage_info(i).insured_end_date := ld_insured_end_date; -- Insured Period End Date
1517 lt_insert_wage_info(i).insured_days := ln_wage_pay_days; -- Insured Period Base Days
1518 lt_insert_wage_info(i).period_start_date := ld_period_start_date; -- Pay Period Start Date
1519 lt_insert_wage_info(i).period_end_date := ld_period_end_date; -- Pay Period End Date
1520 lt_insert_wage_info(i).base_days := ln_base_days; -- Pay Period Base Days
1521 lt_insert_wage_info(i).wage_amount_a := ln_wage_amount_a; -- Wage Amount A
1522 lt_insert_wage_info(i).wage_amount_b := ln_wage_amount_b; -- Wage Amount B
1523 lt_insert_wage_info(i).remarks := lc_remarks; -- Remarks
1524 lt_insert_wage_info(i).exclude_period := NVL(lc_exclude_period,'N'); -- Exclude Period
1525 lt_insert_wage_info(i).line_number := ln_line_number; -- Line Number
1526 --
1527 -- initialize local arguments
1528 i := i+1;
1529 lc_sal_a_bal_flag := 'N';
1530 lc_sal_b_bal_flag := 'N';
1531 ln_prev_ass_a_act_id := NULL;
1532 ln_prev_ass_a_act_id := NULL;
1533 ln_wage_amount_a := NULL;
1534 ln_wage_amount_b := NULL;
1535 ln_assignment_action_id := NULL;
1536 --
1537 END LOOP;
1538 --
1539 -- Inserting into Pay action Information
1540 -- #Bug9692693 Start
1541 --
1542 lt_insert_action_info := get_insert_action_info(p_insert_wage_info => lt_insert_wage_info);
1543 j := lt_insert_action_info.first;
1544 --
1545 WHILE j IS NOT NULL LOOP
1546 IF gb_debug THEN
1547 --
1548 hr_utility.set_location('Inserting Data into Pay action Information ',30);
1549 --
1550 END IF;
1551 --
1552 -- Summing total if wage_amount_a and wage_amount_b not null during any month
1553 --
1554 IF (lc_wage_a_flag = 'Y' AND lc_wage_b_flag = 'Y') THEN
1555 --
1556 IF (lt_insert_action_info(j).wage_amount_a IS NOT NULL OR lt_insert_action_info(j).wage_amount_b IS NOT NULL) THEN
1557 --
1558 ln_total_wage := NVL(lt_insert_action_info(j).wage_amount_a,0) + NVL(lt_insert_action_info(j).wage_amount_b,0);
1559 --
1560 END IF;
1561 --
1562 END IF;
1563 --
1564 pay_action_information_api.create_action_information
1565 ( p_action_information_id => ln_action_info_id
1566 , p_action_context_id => p_assignment_action_id
1567 , p_action_context_type => 'AAP'
1568 , p_object_version_number => ln_obj_version_num
1569 , p_effective_date => p_effective_date
1570 , p_assignment_id => p_assignment_id
1571 , p_source_id => NULL
1572 , p_source_text => NULL
1573 , p_action_information_category => 'JP_UITE_SAL'
1574 , p_action_information1 => fnd_date.date_to_canonical(lt_insert_action_info(j).payment_date) -- Payment Date
1575 , p_action_information2 => fnd_date.date_to_canonical(lt_insert_action_info(j).insured_start_date) -- Insured Period Start Date
1576 , p_action_information3 => fnd_date.date_to_canonical(lt_insert_action_info(j).insured_end_date) -- Insured Period End Date
1577 , p_action_information4 => fnd_number.number_to_canonical(lt_insert_action_info(j).insured_days) -- Insured Period Base Days
1578 , p_action_information5 => fnd_date.date_to_canonical(lt_insert_action_info(j).period_start_date) -- Pay Period Start Date
1579 , p_action_information6 => fnd_date.date_to_canonical(lt_insert_action_info(j).period_end_date) -- Pay Period End Date
1580 , p_action_information7 => fnd_number.number_to_canonical(lt_insert_action_info(j).base_days) -- Pay Period Base Days
1581 , p_action_information8 => fnd_number.number_to_canonical(lt_insert_action_info(j).wage_amount_a) -- Wage Amount A
1582 , p_action_information9 => fnd_number.number_to_canonical(lt_insert_action_info(j).wage_amount_b) -- Wage Amount B
1583 , p_action_information10 => fnd_number.number_to_canonical(ln_total_wage) -- Wage Amount Total
1584 , p_action_information11 => lt_insert_action_info(j).remarks -- Remarks
1585 , p_action_information12 => lt_insert_action_info(j).exclude_period -- Exclude Period
1586 , p_action_information13 => fnd_number.number_to_canonical(lt_insert_action_info(j).line_number) -- Line Number
1587 );
1588 --
1589 j := lt_insert_action_info.next(j);
1590 ln_action_info_id := NULL;
1591 ln_obj_version_num := NULL;
1592 ln_total_wage := NULL;
1593 --
1594 END LOOP;
1595
1596 -- #Bug9692693 End
1597 ELSE
1598 --
1599 -- show first line even if there is no payroll action for the employee
1600 --
1601 OPEN lcu_period_for_no_assact;
1602 --
1603 LOOP
1604 --
1605 FETCH lcu_period_for_no_assact INTO lr_lcu_period_for_no_assact;
1606 EXIT WHEN (lcu_period_for_no_assact%NOTFOUND OR ln_line_number >=gn_output_period OR p_hire_date > lr_lcu_period_for_no_assact.start_date);
1607 --
1608 ln_line_number := ln_line_number + 1;
1609 --
1610 --
1611 ln_diff_mth := (TO_NUMBER(TO_CHAR(lr_lcu_period_for_no_assact.start_date,'YYYY'))
1612 - TO_NUMBER(TO_CHAR(p_termination_date,'YYYY'))) * 12
1613 + (TO_NUMBER(TO_CHAR(lr_lcu_period_for_no_assact.start_date,'MM'))
1614 - TO_NUMBER(TO_CHAR(p_termination_date,'MM'))); --#Bug 9653516
1615
1616 --
1617 -- Wage Payment Days --
1618 --
1619 IF TO_CHAR(lr_lcu_period_for_no_assact.start_date,'MM') = TO_CHAR(lr_lcu_period_for_no_assact.end_date,'MM') THEN
1620 --
1621 ld_insured_start_date := add_months(p_termination_date + 1,ln_diff_mth -1); --#Bug 9653516
1622 ld_insured_end_date := add_months(ld_insured_start_date,1) - 1; --#Bug 9653516
1623 --
1624 ELSE
1625 --
1626 IF TO_CHAR(lr_lcu_period_for_no_assact.start_date,'YYYY') = TO_CHAR(lr_lcu_period_for_no_assact.end_date,'YYYY') THEN --#Bug 9732294
1627 --
1628 ld_insured_start_date := add_months(p_termination_date + 1,ln_diff_mth); --#Bug 9702153
1629 ld_insured_end_date := add_months(ld_insured_start_date,1) - 1; --#Bug 9702153
1630 --
1631 ELSE
1632 --
1633 ld_insured_start_date := add_months(p_termination_date + 1,ln_diff_mth -1); --#Bug 9732294
1634 ld_insured_end_date := add_months(ld_insured_start_date,1) - 1; --#Bug 9732294
1635 --
1636 END IF;
1637
1638 --
1639 END IF;
1640 --
1641 IF TRUNC(p_hire_date) > TRUNC(ld_insured_start_date) THEN
1642 --
1643 ld_insured_start_date := p_hire_date;
1644 --
1645 END IF;
1646 --
1647 IF TRUNC(p_hire_date) > TRUNC( ld_insured_end_date) THEN
1648 --
1649 ld_insured_end_date:= p_hire_date;
1650 --
1651 END IF;
1652 --
1653 IF TRUNC(p_hire_date) > TRUNC(lr_lcu_period_for_no_assact.start_date) THEN
1654 --
1655 ld_period_start_date := p_hire_date;
1656 --
1657 ELSE
1658 --
1659 ld_period_start_date := lr_lcu_period_for_no_assact.start_date;
1660 --
1661 END IF;
1662 --
1663 IF TRUNC(p_termination_date) BETWEEN lr_lcu_period_for_no_assact.start_date AND lr_lcu_period_for_no_assact.end_date THEN
1664 --
1665 ld_period_end_date := TRUNC(p_termination_date);
1666 --
1667 ELSE
1668 --
1669 ld_period_end_date := lr_lcu_period_for_no_assact.end_date;
1670 --
1671 END IF;
1672 --
1673 IF gb_debug THEN
1674 --
1675 hr_utility.set_location('ln_diff_mth '||lc_procedure,20);
1676 hr_utility.set_location('Insured Period Start Date '||ld_insured_start_date,12);
1677 hr_utility.set_location('Insured Period End Date '||ld_insured_end_date,13);
1678 hr_utility.set_location('Pay Period Start Date '||ld_period_start_date,15);
1679 hr_utility.set_location('Pay Period End Date '||ld_period_end_date,16);
1680 hr_utility.set_location('Remarks '||lc_remarks,21);
1681 hr_utility.set_location('Exclude Period '||lc_exclude_period,22);
1682 hr_utility.set_location('ln_line_number '||ln_line_number ,23);
1683 END IF;
1684 --
1685 pay_action_information_api.create_action_information
1686 (p_action_information_id => ln_action_info_id
1687 , p_action_context_id => p_assignment_action_id
1688 , p_action_context_type => 'AAP'
1689 , p_object_version_number => ln_obj_version_num
1690 , p_effective_date => p_effective_date
1691 , p_assignment_id => p_assignment_id
1692 , p_source_id => NULL
1693 , p_source_text => NULL
1694 , p_action_information_category => 'JP_UITE_SAL'
1695 , p_action_information1 => fnd_date.date_to_canonical(ld_period_end_date) -- Payment Date
1696 , p_action_information2 => fnd_date.date_to_canonical(ld_insured_start_date) -- Insured Period Start Date
1697 , p_action_information3 => fnd_date.date_to_canonical(ld_insured_end_date) -- Insured Period End Date
1698 , p_action_information4 => NULL -- Wage Payment Base Days
1699 , p_action_information5 => fnd_date.date_to_canonical(ld_period_start_date) -- Pay Period Start Date
1700 , p_action_information6 => fnd_date.date_to_canonical(ld_period_end_date) -- Pay Period End Date
1701 , p_action_information7 => NULL -- Base Days
1702 , p_action_information8 => NULL -- Wage Amount A
1703 , p_action_information9 => NULL -- Wage Amount B
1704 , p_action_information10 => NULL -- Total Amount of Salary
1705 , p_action_information11 => lc_remarks -- Remarks
1706 , p_action_information12 => NVL(lc_exclude_period,'N') -- Exclude Period
1707 , p_action_information13 => fnd_number.number_to_canonical(ln_line_number) -- Line Number
1708 );
1709 --
1710 END LOOP;
1711 CLOSE lcu_period_for_no_assact;
1712 --
1713 p_ins_start_date := ld_insured_start_date;
1714 --
1715 END IF;
1716 --
1717 IF gb_debug THEN
1718 hr_utility.set_location('Leaving '||lc_procedure,1);
1719 END IF;
1720 --
1721 EXCEPTION
1722 --
1723 WHEN gc_exception THEN
1724 IF gb_debug THEN
1725 hr_utility.set_location('Error in '||lc_procedure,999999);
1726 END IF;
1727 RAISE;
1728 WHEN OTHERS THEN
1729 RAISE gc_exception;
1730 --
1731 END proc_sal_arch;
1732 --
1733
1734 PROCEDURE proc_spb_arch(p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
1735 ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
1736 ,p_effective_date IN pay_payroll_actions.effective_date%TYPE
1737 ,p_period_start_date IN per_time_periods.start_date%TYPE
1738 ,p_period_end_date IN per_time_periods.end_date%TYPE
1739 ,p_payroll_id IN NUMBER)
1740 --***************************************************************************
1741 -- PROCEDURE
1742 -- proc_spb_arch
1743 --
1744 -- DESCRIPTION
1745 -- This procedure is used to process special bonus archive
1746 --
1747 -- ACCESS
1748 -- PUBLIC
1749 --
1750 -- PARAMETERS
1751 --==========
1752 -- NAME TYPE DESCRIPTION
1753 ------------------- -------- ---------------------------------------
1754 -- p_payroll_action_id IN This parameter passes Payroll Action Id
1755 -- p_assignment_id IN This parameter passes Assignment Id
1756 -- p_effective_date IN This Parameter Passes Effective Date
1757 -- p_termination_date IN This Paramter Passes the Termination Date
1758 -- p_payroll_id IN This Paramter Passes the Payroll Id
1759 -- PREREQUISITES
1760 -- None
1761 --
1762 -- CALLED BY
1763 -- None
1764 --***********************************************************************
1765 IS
1766 --
1767 TYPE t_spb_assact_rec is record(
1768 assignment_action_id number,
1769 effective_date date,
1770 date_earned date);
1771 --
1772 TYPE t_spb_assact_tbl IS TABLE OF t_spb_assact_rec INDEX BY BINARY_INTEGER;
1773 --
1774 CURSOR lcu_spb_assact
1775 IS
1776 SELECT paa.assignment_action_id,
1777 ppa.effective_date,
1778 ppa.date_earned
1779 FROM pay_assignment_actions paa,
1780 pay_payroll_actions ppa
1781 WHERE paa.assignment_id = p_assignment_id
1782 AND paa.action_status = 'C'
1783 AND ppa.payroll_action_id = paa.payroll_action_id
1784 AND ppa.effective_date
1785 BETWEEN p_period_start_date and p_period_end_date
1786 AND ppa.element_set_id = gn_spb_ele_set_id
1787 AND ppa.action_type in ('R','Q','G','L')
1788 AND NOT EXISTS(
1789 SELECT null
1790 FROM pay_action_interlocks pai,
1791 pay_assignment_actions paa2,
1792 pay_payroll_actions ppa2
1793 WHERE pai.locked_action_id = paa.assignment_action_id
1794 AND paa2.assignment_action_id = pai.locking_action_id
1795 AND ppa2.payroll_action_id = paa2.payroll_action_id
1796 AND ppa2.action_type = 'V')
1797 ORDER BY paa.action_sequence;
1798 --
1799 CURSOR lcu_get_bal_id(p_balance_name pay_balance_types.balance_name%TYPE
1800 ,p_database_item_suffix pay_balance_dimensions.database_item_suffix%TYPE)
1801 IS
1802 SELECT PDB.defined_balance_id
1803 FROM pay_balance_types PBT
1804 ,pay_balance_dimensions PBD
1805 ,pay_defined_balances PDB
1806 WHERE PBT.balance_name = p_balance_name
1807 AND PBD.database_item_suffix = p_database_item_suffix
1808 AND PBT.balance_type_id = PDB.balance_type_id
1809 AND PBD.balance_dimension_id = PDB.balance_dimension_id;
1810 --
1811 lc_procedure VARCHAR2(200);
1812 --
1813 ln_action_info_id pay_action_information.action_information_id%TYPE;
1814 ln_obj_version_num pay_action_information.object_version_number%TYPE;
1815 ln_spb_earnings NUMBER;
1816 ln_spb_bal_id pay_defined_balances.defined_balance_id%TYPE;
1817 ln_spb_assact_tbl_cnt NUMBER := 0;
1818 --
1819 lt_spb_assact_tbl t_spb_assact_tbl;
1820 --
1821 BEGIN
1822 --
1823 gb_debug := hr_utility.debug_enabled;
1824 --
1825 IF gb_debug THEN
1826 lc_procedure := gc_package||'proc_spb_arch';
1827 hr_utility.set_location('Entering '||lc_procedure,1);
1828 END IF;
1829 --
1830 lt_spb_assact_tbl.delete;
1831 --
1832 -- Fetching balnce id for salary A
1833 --
1834 OPEN lcu_get_bal_id(p_balance_name => 'B_SPB_ERN_SUBJ_EI'
1835 ,p_database_item_suffix => '_ASG_RUN');
1836 FETCH lcu_get_bal_id INTO ln_spb_bal_id;
1837 CLOSE lcu_get_bal_id;
1838 --
1839 -- opening cursor to fetch details into table type
1840 --
1841 OPEN lcu_spb_assact;
1842 --
1843 LOOP
1844 --
1845 FETCH lcu_spb_assact INTO lt_spb_assact_tbl(ln_spb_assact_tbl_cnt);
1846 EXIT WHEN lcu_spb_assact%NOTFOUND;
1847 --
1848 ln_spb_assact_tbl_cnt := ln_spb_assact_tbl_cnt + 1;
1849 --
1850 END LOOP;
1851 CLOSE lcu_spb_assact;
1852 --
1853 IF lt_spb_assact_tbl.count > 0 THEN
1854 --
1855 <<spb_assact_loop>>
1856 FOR spb_assact_cnt in lt_spb_assact_tbl.first..lt_spb_assact_tbl.last LOOP
1857 --
1858 ln_spb_earnings := pay_jp_balance_pkg.get_balance_value(ln_spb_bal_id,lt_spb_assact_tbl(spb_assact_cnt).assignment_action_id);
1859 --
1860 --
1861 pay_action_information_api.create_action_information
1862 ( p_action_information_id => ln_action_info_id
1863 , p_action_context_id => p_assignment_action_id
1864 , p_action_context_type => 'AAP'
1865 , p_object_version_number => ln_obj_version_num
1866 , p_effective_date => p_effective_date
1867 , p_assignment_id => p_assignment_id
1868 , p_source_id => NULL
1869 , p_source_text => NULL
1870 , p_action_information_category => 'JP_UITE_SPB'
1871 , p_action_information1 => fnd_number.number_to_canonical(lt_spb_assact_tbl(spb_assact_cnt).assignment_action_id) -- Assignment Action ID
1872 , p_action_information2 => fnd_date.date_to_canonical(lt_spb_assact_tbl(spb_assact_cnt).effective_date) -- Effective Date
1873 , p_action_information3 => fnd_number.number_to_canonical(ln_spb_earnings) -- Total Earnings Subject to EI
1874 );
1875 --
1876 END LOOP;
1877 --
1878 END IF;
1879 --
1880 IF gb_debug THEN
1881 hr_utility.set_location('leaving '||lc_procedure,1);
1882 END IF;
1883
1884 --
1885 END proc_spb_arch;
1886 --
1887 PROCEDURE proc_term_arch( p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE
1888 ,p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE
1889 ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
1890 ,p_effective_date IN pay_payroll_actions.effective_date%TYPE
1891 ,p_termination_date IN per_periods_of_service.actual_termination_date%TYPE
1892 )
1893 --***************************************************************************
1894 -- PROCEDURE
1895 -- proc_sal_arch
1896 --
1897 -- DESCRIPTION
1898 -- This procedure is used to process salary archive
1899 --
1900 -- ACCESS
1901 -- PUBLIC
1902 --
1903 -- PARAMETERS
1904 --==========
1905 -- NAME TYPE DESCRIPTION
1906 ------------------- -------- ---------------------------------------
1907 -- p_payroll_action_id IN This parameter passes Payroll Action Id
1908 -- p_assignment_id IN This parameter passes Assignment Id
1909 -- p_effective_date IN This Parameter Passes Effective Date
1910 -- p_termination_date IN This Paramter Passes the Termination Date
1911 -- PREREQUISITES
1912 -- None
1913 --
1914 -- CALLED BY
1915 -- None
1916 --***********************************************************************
1917 IS
1918 --
1919 ln_action_info_id pay_action_information.action_information_id%TYPE;
1920 ln_obj_version_num pay_action_information.object_version_number%TYPE;
1921 ln_term_action_info_id pay_action_information.action_information_id%TYPE;
1922 ln_term_obj_version_num pay_action_information.object_version_number%TYPE;
1923 --
1924 lc_wage_note pay_action_information.action_information1%TYPE;
1925 lc_wage_note2 pay_action_information.action_information1%TYPE;
1926 lc_wage_note3 pay_action_information.action_information1%TYPE;
1927 lc_wage_note4 pay_action_information.action_information2%TYPE;
1928 lc_wage_note5 pay_action_information.action_information2%TYPE;
1929 lc_wage_instr1 pay_action_information.action_information1%TYPE;
1930 lc_wage_instr2 pay_action_information.action_information2%TYPE;
1931 lc_term_reason pay_action_information.action_information1%TYPE;
1932 lc_reason_detail pay_action_information.action_information2%TYPE;
1933 lc_reason_detail2 pay_action_information.action_information2%TYPE;
1934 lc_reason_detail3 pay_action_information.action_information2%TYPE;
1935 lc_reason_detail4 pay_action_information.action_information3%TYPE;
1936 lc_reason_detail5 pay_action_information.action_information3%TYPE;
1937 lc_concrete_cir1 pay_action_information.action_information2%TYPE;
1938 lc_concrete_cir2 pay_action_information.action_information3%TYPE;
1939 --
1940 lc_procedure VARCHAR2(200);
1941 --
1942 BEGIN
1943 --
1944 gb_debug := hr_utility.debug_enabled;
1945 --
1946 IF gb_debug THEN
1947 lc_procedure := gc_package||'proc_term_arch';
1948 hr_utility.set_location('Entering '||lc_procedure,1);
1949 END IF;
1950 --
1951 --Wage Instructions
1952 --
1953 lc_wage_note := pay_jp_balance_pkg.get_entry_value_char('COM_TRM_REPORT_INFO','WAGE_SP_DESC',p_assignment_id,p_termination_date);
1954 lc_wage_note2 := pay_jp_balance_pkg.get_entry_value_char('COM_TRM_REPORT_INFO','WAGE_SP_DESC2',p_assignment_id,p_termination_date);
1955 lc_wage_note3 := pay_jp_balance_pkg.get_entry_value_char('COM_TRM_REPORT_INFO','WAGE_SP_DESC3',p_assignment_id,p_termination_date);
1956 lc_wage_note4 := pay_jp_balance_pkg.get_entry_value_char('COM_TRM_REPORT_INFO','WAGE_SP_DESC4',p_assignment_id,p_termination_date);
1957 lc_wage_note5 := pay_jp_balance_pkg.get_entry_value_char('COM_TRM_REPORT_INFO','WAGE_SP_DESC5',p_assignment_id,p_termination_date);
1958 --
1959 lc_wage_instr1 := lc_wage_note || lc_wage_note2 ||lc_wage_note3;
1960 --
1961 lc_wage_instr2 := lc_wage_note4 ||lc_wage_note5;
1962 --
1963 -- Termination Details
1964 --
1965 lc_term_reason := pay_jp_balance_pkg.get_entry_value_char('COM_TRM_REPORT_REASON_INFO','TRM_REASON',p_assignment_id,p_termination_date);
1966 lc_reason_detail := pay_jp_balance_pkg.get_entry_value_char('COM_TRM_REPORT_REASON_INFO','DETAIL',p_assignment_id,p_termination_date);
1967 lc_reason_detail2 := pay_jp_balance_pkg.get_entry_value_char('COM_TRM_REPORT_REASON_INFO','DETAIL2',p_assignment_id,p_termination_date);
1968 lc_reason_detail3 := pay_jp_balance_pkg.get_entry_value_char('COM_TRM_REPORT_REASON_INFO','DETAIL3',p_assignment_id,p_termination_date);
1969 lc_reason_detail4 := pay_jp_balance_pkg.get_entry_value_char('COM_TRM_REPORT_REASON_INFO','DETAIL4',p_assignment_id,p_termination_date);
1970 lc_reason_detail5 := pay_jp_balance_pkg.get_entry_value_char('COM_TRM_REPORT_REASON_INFO','DETAIL5',p_assignment_id,p_termination_date);
1971 --
1972 lc_concrete_cir1 := lc_reason_detail||lc_reason_detail2||lc_reason_detail3;
1973 lc_concrete_cir2 := lc_reason_detail4||lc_reason_detail5;
1974 --
1975 --
1976 --WAGE NOTE DETAILS ------------
1977 --
1978 pay_action_information_api.create_action_information
1979 ( p_action_information_id => ln_action_info_id
1980 , p_action_context_id => p_assignment_action_id
1981 , p_action_context_type => 'AAP'
1982 , p_object_version_number => ln_obj_version_num
1983 , p_effective_date => p_effective_date
1984 , p_assignment_id => p_assignment_id
1985 , p_source_id => NULL
1986 , p_source_text => NULL
1987 , p_action_information_category => 'JP_UITE_INSTR'
1988 , p_action_information1 => lc_wage_instr1 -- Wage Special Instruction 1
1989 , p_action_information2 => lc_wage_instr2 -- Wage Special Instruction 2
1990 );
1991 --
1992 --TERMINATION DETAILS ------------
1993 --
1994 pay_action_information_api.create_action_information
1995 ( p_action_information_id => ln_term_action_info_id
1996 , p_action_context_id => p_assignment_action_id
1997 , p_action_context_type => 'AAP'
1998 , p_object_version_number => ln_term_obj_version_num
1999 , p_effective_date => p_effective_date
2000 , p_assignment_id => p_assignment_id
2001 , p_source_id => NULL
2002 , p_source_text => NULL
2003 , p_action_information_category => 'JP_UITE_TERM'
2004 , p_action_information1 => lc_term_reason -- Separation Reason
2005 , p_action_information2 => lc_concrete_cir1 -- Concrete Circumstance
2006 , p_action_information3 => lc_concrete_cir2 -- Concrete Circumstance 2
2007 );
2008 --
2009 IF gb_debug THEN
2010 --
2011 hr_utility.set_location('Leaving '||lc_procedure,1);
2012 --
2013 END IF;
2014 --
2015 EXCEPTION
2016 WHEN gc_exception THEN
2017 IF gb_debug THEN
2018 hr_utility.set_location('Error in '||lc_procedure,999999);
2019 END IF;
2020 RAISE;
2021 WHEN OTHERS THEN
2022 RAISE gc_exception;
2023 --
2024 END proc_term_arch;
2025 --
2026 PROCEDURE RANGE_CODE ( p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE
2027 ,p_sql OUT NOCOPY VARCHAR2
2028 )
2029 --***************************************************************************
2030 -- PROCEDURE
2031 -- RANGE_CODE
2032 --
2033 -- DESCRIPTION
2034 -- This procedure returns a sql string to select a range
2035 -- of assignments eligible for archival
2036 --
2037 -- ACCESS
2038 -- PUBLIC
2039 --
2040 -- PARAMETERS
2041 -- ==========
2042 -- NAME TYPE DESCRIPTION
2043 ------------------- -------- ---------------------------------------
2044 -- p_payroll_action_id IN This parameter passes Payroll Action Id.
2045 -- p_sql OUT This parameter retunrs SQL Query.
2046 --
2047 -- PREREQUISITES
2048 -- None
2049 --
2050 -- CALLED BY
2051 -- None
2052 --*************************************************************************
2053 IS
2054
2055 lc_procedure VARCHAR2(200);
2056
2057 BEGIN
2058 --
2059 gb_debug := hr_utility.debug_enabled;
2060 --
2061 IF gb_debug THEN
2062 lc_procedure := gc_package||'RANGE_CODE';
2063 hr_utility.set_location('Entering '||lc_procedure,1);
2064 END IF ;
2065 -------------------------------------------------------------------------
2066 -- sql string to SELECT a range of assignments eligible for archival.
2067 -------------------------------------------------------------------------
2068 p_sql := ' SELECT distinct p.person_id' ||
2069 ' FROM per_people_f p,' ||
2070 ' pay_payroll_actions pa' ||
2071 ' WHERE pa.payroll_action_id = :payroll_action_id' ||
2072 ' AND p.business_group_id = pa.business_group_id' ||
2073 ' ORDER BY p.person_id';
2074 --
2075 IF gb_debug THEN
2076 hr_utility.set_location('Leaving '||lc_procedure,1000);
2077 END IF;
2078 --
2079 IF gb_debug THEN
2080 hr_utility.set_location(lc_procedure,10);
2081 END IF;
2082 --
2083 END RANGE_CODE;
2084 --
2085 PROCEDURE initialize ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE )
2086 --*************************************************************************
2087 -- PROCEDURE
2088 -- initialize
2089 --
2090 -- DESCRIPTION
2091 -- This procedure is used to set global contexts
2092 --
2093 -- ACCESS
2094 -- PUBLIC
2095 --
2096 -- PARAMETERS
2097 -- ==========
2098 -- NAME TYPE DESCRIPTION
2099 -- ----------------- -------- ---------------------------------------
2100 -- p_payroll_action_id IN This parameter passes Payroll Action Id
2101 --
2102 -- PREREQUISITES
2103 -- None
2104 --
2105 -- CALLED BY
2106 -- INITIALIZATION_CODE
2107 --*************************************************************************
2108 IS
2109 --
2110 CURSOR lcr_params(p_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE)
2111 --*************************************************************************
2112 --
2113 -- CURSOR lcr_params
2114 --
2115 -- DESCRIPTION
2116 -- Fetches User Parameters from legislative_paramters column.
2117 --
2118 -- PARAMETERS
2119 -- ==========
2120 -- NAME TYPE DESCRIPTION
2121 ------------------- -------- ---------------------------------------------
2122 -- p_payroll_action_id IN This parameter passes the Payroll Action Id.
2123 --
2124 -- PREREQUISITES
2125 -- None
2126 --
2127 -- CALLED BY
2128 -- initialize procedure
2129 --
2130 --**********************************************************************
2131 IS
2132 SELECT pay_core_utils.get_parameter('BG',legislative_parameters)
2133 ,pay_core_utils.get_parameter('ASSETID',legislative_parameters)
2134 ,TO_DATE(pay_core_utils.get_parameter('TEDF',legislative_parameters),'YYYY/MM/DD')
2135 ,TO_DATE(pay_core_utils.get_parameter('TEDT',legislative_parameters),'YYYY/MM/DD')
2136 ,pay_core_utils.get_parameter('LIO',legislative_parameters)
2137 ,TO_DATE(pay_core_utils.get_parameter('EDATE',legislative_parameters),'YYYY/MM/DD')
2138 FROM pay_payroll_actions PPA
2139 WHERE PPA.payroll_action_id = p_payroll_action_id;
2140
2141 --
2142 --*************************************************************************
2143 --
2144 -- CURSOR lcu_ei_org_info
2145 --
2146 -- DESCRIPTION
2147 -- Fetches Separation Certificate Santei Base, Separation Certificate Output Period at the org EI level
2148 --
2149 -- PARAMETERS
2150 -- ==========
2151 -- NAME TYPE DESCRIPTION
2152 ------------------- -------- ---------------------------------------------
2153 --
2154 -- PREREQUISITES
2155 -- None
2156 --
2157 -- CALLED BY
2158 -- initialize procedure
2159 --
2160 --**********************************************************************
2161 CURSOR lcu_ei_org_info(p_organization_id hr_organization_information.organization_id%TYPE)
2162 IS
2163 SELECT NVL(org_information15,gc_date_earned)
2164 ,NVL(org_information16,12)
2165 FROM hr_organization_information HOI
2166 WHERE HOI.org_information_context= 'JP_LI_UNION_INFO'
2167 AND organization_id= p_organization_id;
2168 -- Local Variables
2169 lc_procedure VARCHAR2(200);
2170 --
2171 BEGIN
2172 --
2173 gb_debug :=hr_utility.debug_enabled ;
2174 lc_procedure := gc_package||'initialize';
2175 --
2176 IF gb_debug THEN
2177 hr_utility.set_location('Entering '||lc_procedure,1);
2178 END IF;
2179 -------------------------------------------------------------------------
2180 -- initialization_code to set the global tables for EIT
2181 -- that will be used by each thread in multi-threading.
2182 -------------------------------------------------------------------------
2183 gn_arc_payroll_action_id := p_payroll_action_id;
2184 -------------------------------------------------------------------------
2185 -- Fetch the parameters passed by user into global variable.
2186 -------------------------------------------------------------------------
2187 OPEN lcr_params(p_payroll_action_id);
2188 FETCH lcr_params
2189 INTO gr_parameters.business_group_id
2190 ,gr_parameters.assignment_set_id
2191 ,gr_parameters.termination_date_from
2192 ,gr_parameters.termination_date_to
2193 ,gr_parameters.labor_insorg_id
2194 ,gr_parameters.effective_date;
2195 CLOSE lcr_params;
2196 --
2197 IF gb_debug THEN
2198 hr_utility.set_location('p_payroll_action_id......... = ' || p_payroll_action_id,30);
2199 hr_utility.set_location('gr_parameters.business_group_id.......= ' || gr_parameters.business_group_id,30);
2200 hr_utility.set_location('gr_parameters.assignment_set_id.......= ' || gr_parameters.assignment_set_id,30);
2201 hr_utility.set_location('gr_parameters.termination_date_from...= ' || gr_parameters.termination_date_from,30);
2202 hr_utility.set_location('gr_parameters.termination_date_to.....= ' || gr_parameters.termination_date_to,30);
2203 hr_utility.set_location('gr_parameters.labor_insorg_id..= ' || gr_parameters.labor_insorg_id,30);
2204 hr_utility.set_location('gr_parameters.effective_date....... = ' || gr_parameters.effective_date,30);
2205 END IF;
2206 --
2207 gn_business_group_id := gr_parameters.business_group_id ;
2208 gn_payroll_action_id := p_payroll_action_id;
2209 gc_legislation_code := pay_jp_balance_pkg.get_legislation_code(gr_parameters.business_group_id);
2210 gn_sal_ele_set_id := get_element_set_id(gc_sal_ele_set,gr_parameters.business_group_id,gc_legislation_code);
2211 gn_spb_ele_set_id := get_element_set_id(gc_spb_ele_set,gr_parameters.business_group_id,gc_legislation_code);
2212 --
2213 -------------------------------------------------------------------------
2214 -- Fetch the Organization information into global type
2215 -------------------------------------------------------------------------
2216 OPEN lcu_ei_org_info(gr_parameters.labor_insorg_id);
2217 FETCH lcu_ei_org_info
2218 INTO gc_santei_base
2219 ,gn_output_period;
2220 CLOSE lcu_ei_org_info;
2221 --
2222 IF gb_debug THEN
2223 hr_utility.set_location('Separation Certificate Santei Base.......= ' || gc_santei_base,30);
2224 hr_utility.set_location('Separation Certificate Output Period.......= ' ||gn_output_period,30);
2225 hr_utility.set_location('gn_sal_ele_set_id .........= ' ||gn_sal_ele_set_id,30);
2226 hr_utility.set_location('gn_spb_ele_set_id .........= ' ||gn_spb_ele_set_id,30);
2227 hr_utility.set_location('Leaving '||lc_procedure,1000);
2228 END IF;
2229 --
2230 EXCEPTION
2231 WHEN OTHERS THEN
2232 hr_utility.set_location('Error in '||lc_procedure,999999);
2233 RAISE;
2234 END initialize;
2235 --
2236 PROCEDURE INITIALIZATION_CODE ( p_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE )
2237 --***************************************************************************
2238 -- PROCEDURE
2239 -- INITIALIZATION_CODE
2240 --
2241 -- DESCRIPTION
2242 -- This procedure is used to set global contexts
2243 --
2244 -- ACCESS
2245 -- PUBLIC
2246 --
2247 -- PARAMETERS
2248 --==========
2249 -- NAME TYPE DESCRIPTION
2250 ------------------- -------- ---------------------------------------
2251 -- p_payroll_action_id IN This parameter passes Payroll Action Id
2252 --
2253 -- PREREQUISITES
2254 -- None
2255 --
2256 -- CALLED BY
2257 -- None
2258 --***********************************************************************
2259 IS
2260 -- Local Variables
2261 lc_procedure VARCHAR2(200);
2262 --
2263 BEGIN
2264 --
2265 gb_debug :=hr_utility.debug_enabled ;
2266 --
2267 IF gb_debug THEN
2268 lc_procedure := gc_package||'INITIALIZATION_CODE';
2269 hr_utility.set_location('Entering '||lc_procedure,1);
2270 END IF;
2271 -----------------------------------------------------------
2272 -- initialization_code to set the global tables for EIT
2273 -- that will be used by each thread in multi-threading.
2274 -----------------------------------------------------------
2275 gn_arc_payroll_action_id := p_payroll_action_id;
2276 -----------------------------------------------------------
2277 -- Fetch the parameters passed by user into global variable
2278 -- initialize procedure
2279 -----------------------------------------------------------
2280 initialize(p_payroll_action_id);
2281 --
2282 IF gb_debug THEN
2283 hr_utility.set_location('Leaving '||lc_procedure,1000);
2284 END IF;
2285 --
2286 EXCEPTION
2287 WHEN OTHERS THEN
2288 hr_utility.set_location('Error in '||lc_procedure,999999);
2289 RAISE;
2290 END INITIALIZATION_CODE;
2291 --Function pay_yea_balance_result_value
2292 --
2293 FUNCTION range_person_on
2294 --************************************************************************
2295 -- FUNCTION
2296 -- range_person_on
2297 --
2298 -- DESCRIPTION
2299 -- Checks if RANGE_PERSON_ID is enabled for
2300 -- Archive process.
2301 --
2302 -- ACCESS
2303 -- PRIVATE
2304 --
2305 -- PREREQUISITES
2306 -- None
2307 --
2308 -- CALLED BY
2309 -- assignment_action_code
2310 --************************************************************************
2311 RETURN BOOLEAN
2312 IS
2313 --
2314 CURSOR lcu_action_parameter
2315 IS
2316 SELECT parameter_value
2317 FROM pay_action_parameters
2318 WHERE parameter_name = 'RANGE_PERSON_ID';
2319 --
2320 lb_return BOOLEAN;
2321 lc_action_param_val VARCHAR2(30);
2322 --
2323 BEGIN
2324 --
2325 gb_debug := hr_utility.debug_enabled;
2326 --
2327 IF gb_debug THEN
2328 hr_utility.set_location('Entering range_person_on',10);
2329 END IF;
2330 --
2331 OPEN lcu_action_parameter;
2332 FETCH lcu_action_parameter INTO lc_action_param_val;
2333 CLOSE lcu_action_parameter;
2334 --
2335 IF lc_action_param_val = 'Y' THEN
2336 lb_return := TRUE;
2337 IF gb_debug THEN
2338 hr_utility.set_location('Range Person = True',10);
2339 END IF;
2340 ELSE
2341 lb_return := FALSE;
2342 END IF;
2343 --
2344 IF gb_debug THEN
2345 hr_utility.set_location('Leaving range_person_on',10);
2346 END IF;
2347 RETURN lb_return;
2348 --
2349 EXCEPTION WHEN NO_DATA_FOUND THEN
2350 IF gb_debug THEN
2351 hr_utility.set_location('No Data Found Exception in range_person_on',10);
2352 END IF;
2353 lb_return := FALSE;
2354 RETURN lb_return;
2355 END range_person_on;
2356 --
2357 PROCEDURE assignment_action_code ( p_payroll_action_id IN pay_payroll_actions.payroll_action_id%type
2358 ,p_start_person IN per_all_people_f.person_id%type
2359 ,p_end_person IN per_all_people_f.person_id%type
2360 ,p_chunk IN NUMBER
2361 )
2362 --************************************************************************
2363 -- PROCEDURE
2364 -- assignment_action_code
2365 --
2366 -- DESCRIPTION
2367 -- This procedure further restricts the assignment_id's returned by range_code
2368 -- This procedure gets the parameters given by user and restricts
2369 -- the assignments to be archived
2370 -- it then calls hr_nonrun.insact to create an assignment action id
2371 -- it then archives Payroll Run assignment action id details
2372 -- for each assignment.
2373 -- There are different cursors for choosing the assignment ids.
2374 -- Depending on the parameters passed,the appropriate cursor is used.
2375 --
2376 -- ACCESS
2377 -- PUBLIC
2378 --
2379 -- PARAMETERS
2380 -- ==========
2381 -- NAME TYPE DESCRIPTION
2382 -- ----------------- -------- ---------------------------------------
2383 -- p_payroll_action_id IN This parameter passes Payroll Action Id
2384 -- p_start_person IN This parameter passes Start Person Id
2385 -- p_end_person IN This parameter passes End Person Id
2386 -- p_chunk IN This parameter passes Chunk Number
2387 --
2388 -- PREREQUISITES
2389 -- None
2390 --
2391 -- CALLED BY
2392 -- PYUGEN process
2393 --************************************************************************
2394 IS
2395 --
2396 CURSOR lcu_emp_assignment_det_r ( p_business_group_id per_assignments_f.business_group_id%TYPE
2397 ,p_organization_id per_assignments_f.organization_id%TYPE
2398 ,p_effective_date DATE
2399 ,p_start_date DATE
2400 ,p_end_date DATE
2401 )
2402 IS
2403 SELECT PAF.assignment_id
2404 ,PPS.actual_termination_date
2405 ,PPS.projected_termination_date
2406 FROM per_people_f PPF
2407 ,per_assignments_f PAF
2408 ,per_periods_of_service PPS
2409 ,pay_population_ranges PPR
2410 ,pay_payroll_actions PPA
2411 WHERE PPF.person_id = PAF.person_id
2412 AND PPF.person_id = PPS.person_id
2413 AND PPA.payroll_action_id = p_payroll_action_id
2414 AND PPA.payroll_action_id = PPR.payroll_action_id
2415 AND PPR.chunk_number = p_chunk
2416 AND PPR.person_id = PPF.person_id
2417 AND PAF.business_group_id = p_business_group_id
2418 AND PPA.business_group_id = PAF.business_group_id
2419 AND PPS.period_of_service_id = PAF.period_of_service_id
2420 AND TRUNC(NVL(PPS.actual_termination_date,PPS.projected_termination_date)) BETWEEN PPF.effective_start_date AND PPF.effective_end_date
2421 AND TRUNC(NVL(PPS.actual_termination_date,PPS.projected_termination_date)) BETWEEN PAF.effective_start_date AND PAF.effective_end_date
2422 AND TRUNC(NVL(PPS.actual_termination_date,PPS.projected_termination_date)) BETWEEN p_start_date AND p_end_date
2423 AND NVL(get_life_ins_org_id(PAF.assignment_id,NVL(PPS.actual_termination_date,PPS.projected_termination_date)),-999) = p_organization_id
2424 AND NVL(get_ei_type(PAF.assignment_id,NVL(PPS.actual_termination_date,PPS.projected_termination_date)),-999) IN ('EE','EE_AGED','EX','EX_AGED')
2425 AND NVL(PPS.actual_termination_date,PPS.projected_termination_date) BETWEEN get_ei_qualify_date(PAF.assignment_id,NVL(PPS.actual_termination_date,PPS.projected_termination_date))
2426 AND NVL(get_ei_dis_qual_date(PAF.assignment_id,NVL(PPS.actual_termination_date,PPS.projected_termination_date))-1,TO_DATE('31/12/4712','dd/mm/yyyy'))
2427 AND get_term_rpt_flag(PAF.assignment_id,NVL(PPS.actual_termination_date,PPS.projected_termination_date))= 'Y'
2428 ORDER BY PAF.assignment_id;
2429 --
2430 CURSOR lcu_emp_assignment_det ( p_start_person_id per_all_people_f.person_id%TYPE
2431 ,p_end_person_id per_all_people_f.person_id%TYPE
2432 ,p_business_group_id per_assignments_f.business_group_id%TYPE
2433 ,p_organization_id per_assignments_f.organization_id%TYPE
2434 ,p_effective_date DATE
2435 ,p_start_date DATE
2436 ,p_end_date DATE
2437 )
2438 IS
2439 SELECT PAF.assignment_id
2440 ,PPS.actual_termination_date
2441 ,PPS.projected_termination_date
2442 FROM per_people_f PPF
2443 ,per_assignments_f PAF
2444 ,per_periods_of_service PPS
2445 WHERE PPF.person_id = PAF.person_id
2446 AND PPF.person_id = PPS.person_id
2447 AND PAF.business_group_id = p_business_group_id
2448 AND PPF.person_id BETWEEN p_start_person_id
2449 AND p_end_person_id
2450 AND PPS.period_of_service_id = PAF.period_of_service_id
2451 AND TRUNC(NVL(PPS.actual_termination_date,PPS.projected_termination_date)) BETWEEN PPF.effective_start_date AND PPF.effective_end_date
2452 AND TRUNC(NVL(PPS.actual_termination_date,PPS.projected_termination_date)) BETWEEN PAF.effective_start_date AND PAF.effective_end_date
2453 AND TRUNC(NVL(PPS.actual_termination_date,PPS.projected_termination_date)) BETWEEN p_start_date AND p_end_date
2454 AND NVL(get_life_ins_org_id(PAF.assignment_id,NVL(PPS.actual_termination_date,PPS.projected_termination_date)),-999) = p_organization_id
2455 AND NVL(get_ei_type(PAF.assignment_id,NVL(PPS.actual_termination_date,PPS.projected_termination_date)),-999) IN ('EE','EE_AGED','EX','EX_AGED')
2456 AND NVL(PPS.actual_termination_date,PPS.projected_termination_date)BETWEEN get_ei_qualify_date(PAF.assignment_id,NVL(PPS.actual_termination_date,PPS.projected_termination_date))
2457 AND NVL(get_ei_dis_qual_date(PAF.assignment_id,NVL(PPS.actual_termination_date,PPS.projected_termination_date))-1,TO_DATE('31/12/4712','dd/mm/yyyy'))
2458 AND get_term_rpt_flag(PAF.assignment_id,NVL(PPS.actual_termination_date,PPS.projected_termination_date))= 'Y'
2459 ORDER BY PAF.assignment_id;
2460 --
2461 CURSOR lcu_next_action_id
2462 IS
2463 SELECT pay_assignment_actions_s.NEXTVAL
2464 FROM dual;
2465 --
2466 -- Local Variables
2467 lt_org_id per_jp_report_common_pkg.gt_org_tbl;
2468 lc_procedure VARCHAR2(200);
2469 lc_include_flag VARCHAR2(1);
2470 ld_start_date DATE;
2471 ln_next_assignment_action_id NUMBER;
2472 --
2473 BEGIN
2474 --
2475 gb_debug := hr_utility.debug_enabled ;
2476 --
2477 IF gb_debug THEN
2478 lc_procedure := gc_package||'assignment_action_code';
2479 hr_utility.set_location('Entering ' || lc_procedure,20);
2480 hr_utility.set_location('Entering assignment_action_code',20);
2481 hr_utility.set_location('Person Range '||p_start_person||' - '||p_end_person,20);
2482 hr_utility.set_location('p_payroll_action_id - '||p_payroll_action_id,20);
2483 hr_utility.set_location('p_chunk - '||p_chunk,20);
2484 END IF;
2485 --
2486 -- initialization_code to to set the global tables for EIT
2487 -- that will be used by each thread in multi-threading.
2488 --
2489 initialize(p_payroll_action_id);
2490 --
2491
2492
2493 IF range_person_on THEN
2494 --
2495 IF gb_debug THEN
2496 hr_utility.set_location('Inside Range person if condition',20);
2497 END IF;
2498 -- -- Assignment Action for Current and Terminated Employees
2499 FOR lr_emp_assignment_det IN lcu_emp_assignment_det_r(gr_parameters.business_group_id
2500 ,gr_parameters.labor_insorg_id
2501 ,gr_parameters.effective_date
2502 ,gr_parameters.termination_date_from
2503 ,gr_parameters.termination_date_to
2504 )
2505 LOOP
2506 IF NVL(gr_parameters.assignment_set_id,0) = 0 THEN
2507 OPEN lcu_next_action_id;
2508 FETCH lcu_next_action_id INTO ln_next_assignment_action_id;
2509 CLOSE lcu_next_action_id;
2510 --
2511 IF gb_debug THEN
2512 hr_utility.set_location('p_payroll_action_id......... = '||p_payroll_action_id,20);
2513 hr_utility.set_location('l_next_assignment_action_id. = '||ln_next_assignment_action_id,20);
2514 hr_utility.set_location('lr_emp_assignment_det.assignment_id.= '||lr_emp_assignment_det.assignment_id,20);
2515 END IF;
2516 --
2517 -- Create the archive assignment actions
2518 hr_nonrun_asact.insact(ln_next_assignment_action_id
2519 ,lr_emp_assignment_det.assignment_id
2520 ,p_payroll_action_id
2521 ,p_chunk
2522 );
2523 ELSE
2524 lc_include_flag := hr_jp_ast_utility_pkg.assignment_set_validate(p_assignment_set_id => gr_parameters.assignment_set_id
2525 ,p_assignment_id => lr_emp_assignment_det.assignment_id
2526 ,p_effective_date => NVL(lr_emp_assignment_det.actual_termination_date,lr_emp_assignment_det.projected_termination_date)
2527 ,p_populate_fs_flag => 'Y'
2528 );
2529 IF lc_include_flag = 'Y' THEN
2530 OPEN lcu_next_action_id;
2531 FETCH lcu_next_action_id INTO ln_next_assignment_action_id;
2532 CLOSE lcu_next_action_id;
2533 --
2534 IF gb_debug THEN
2535 hr_utility.set_location('p_payroll_action_id......... = '||p_payroll_action_id,20);
2536 hr_utility.set_location('l_next_assignment_action_id. = '||ln_next_assignment_action_id,20);
2537 hr_utility.set_location('lr_emp_assignment_det.assignment_id.= '||lr_emp_assignment_det.assignment_id,20);
2538 END IF;
2539 --
2540 -- Create the archive assignment actions
2541 hr_nonrun_asact.insact(ln_next_assignment_action_id
2542 ,lr_emp_assignment_det.assignment_id
2543 ,p_payroll_action_id
2544 ,p_chunk
2545 );
2546 END IF;
2547 END IF;
2548 END LOOP; -- End loop for assignment details cursor
2549 ELSE -- Range person is not on
2550 IF gb_debug THEN
2551 hr_utility.set_location('Range person returns false',20);
2552 END IF;
2553 -- -- Assignment Action for Current and Terminated Employees
2554 FOR lr_emp_assignment_det IN lcu_emp_assignment_det(p_start_person
2555 ,p_end_person
2556 ,gr_parameters.business_group_id
2557 ,gr_parameters.labor_insorg_id
2558 ,gr_parameters.effective_date
2559 ,gr_parameters.termination_date_from
2560 ,gr_parameters.termination_date_to
2561 )
2562 LOOP
2563 IF NVL(gr_parameters.assignment_set_id,0) = 0 THEN
2564 OPEN lcu_next_action_id;
2565 FETCH lcu_next_action_id INTO ln_next_assignment_action_id;
2566 CLOSE lcu_next_action_id;
2567 --
2568 IF gb_debug THEN
2569 hr_utility.set_location('p_payroll_action_id......... = '||p_payroll_action_id,20);
2570 hr_utility.set_location('l_next_assignment_action_id. = '||ln_next_assignment_action_id,20);
2571 hr_utility.set_location('lr_emp_assignment_det.assignment_id.= '||lr_emp_assignment_det.assignment_id,20);
2572 END IF;
2573 --
2574 -- Create the archive assignment actions
2575 hr_nonrun_asact.insact(ln_next_assignment_action_id
2576 ,lr_emp_assignment_det.assignment_id
2577 ,p_payroll_action_id
2578 ,p_chunk
2579 );
2580 ELSE
2581 lc_include_flag := hr_jp_ast_utility_pkg.assignment_set_validate(p_assignment_set_id => gr_parameters.assignment_set_id
2582 ,p_assignment_id => lr_emp_assignment_det.assignment_id
2583 ,p_effective_date => NVL(lr_emp_assignment_det.actual_termination_date,lr_emp_assignment_det.projected_termination_date)
2584 ,p_populate_fs_flag => 'Y'
2585 );
2586 IF lc_include_flag = 'Y' THEN
2587 OPEN lcu_next_action_id;
2588 FETCH lcu_next_action_id INTO ln_next_assignment_action_id;
2589 CLOSE lcu_next_action_id;
2590 --
2591 IF gb_debug THEN
2592 hr_utility.set_location('p_payroll_action_id......... = '||p_payroll_action_id,20);
2593 hr_utility.set_location('l_next_assignment_action_id. = '||ln_next_assignment_action_id,20);
2594 hr_utility.set_location('lr_emp_assignment_det.assignment_id.= '||lr_emp_assignment_det.assignment_id,20);
2595 END IF;
2596 --
2597 -- Create the archive assignment actions
2598 hr_nonrun_asact.insact(ln_next_assignment_action_id
2599 ,lr_emp_assignment_det.assignment_id
2600 ,p_payroll_action_id
2601 ,p_chunk
2602 );
2603 END IF;
2604 END IF;
2605 END LOOP; -- End loop for assignment details cursor
2606 END IF; -- End If for range_person_on
2607 --
2608 IF gb_debug THEN
2609 hr_utility.set_location('Leaving '||lc_procedure,1);
2610 END IF;
2611 --
2612 EXCEPTION
2613 WHEN gc_exception THEN
2614 IF gb_debug THEN
2615 hr_utility.set_location('Error in '||lc_procedure,999999);
2616 END IF;
2617 RAISE;
2618 WHEN OTHERS THEN
2619 RAISE gc_exception;
2620 END assignment_action_code;
2621 --
2622 --
2623 PROCEDURE ARCHIVE_CODE ( p_assignment_action_id IN pay_assignment_actions.assignment_action_id%type
2624 , p_effective_date IN pay_payroll_actions.effective_date%type
2625 )
2626 --************************************************************************
2627 -- PROCEDURE
2628 -- ARCHIVE_CODE
2629 --
2630 -- DESCRIPTION
2631 -- If employee details not previously archived,proc archives employee
2632 -- details in pay_Action_information with context 'JP_UITE_EMP'
2633 --
2634 -- ACCESS
2635 -- PUBLIC
2636 --
2637 -- PARAMETERS
2638 -- ==========
2639 -- NAME TYPE DESCRIPTION
2640 -- ----------------- -------- ---------------------------------------
2641 -- p_assignment_action_id IN This parameter passes Assignment Action Id
2642 -- p_effective_date IN This parameter passes Effective Date
2643 --
2644 -- PREREQUISITES
2645 -- None
2646 --
2647 -- CALLED BY
2648 -- None
2649 --************************************************************************
2650 IS
2651 --
2652 CURSOR lcu_get_assignment_id ( p_assignment_action_id pay_assignment_actions.assignment_action_id%type )
2653 IS
2654 SELECT assignment_id
2655 FROM pay_assignment_actions
2656 WHERE assignment_action_id = p_assignment_action_id;
2657 --
2658 CURSOR lcu_employee_details ( p_assignment_id NUMBER
2659 , p_effective_date DATE
2660 )
2661 IS
2662 SELECT PPF.employee_number EMPLOYEE_NUMBER
2663 , get_ui_num(p_assignment_id,NVL(PPOS.actual_termination_date,PPOS.projected_termination_date)) UI_REGISTERED_NUMBER
2664 , PPF.last_name LAST_NAME_KANA
2665 , PPF.first_name FIRST_NAME_KANA
2666 , PPF.per_information18 LAST_NAME
2667 , PPF.per_information19 FIRST_NAME
2668 , NVL(PPOS.actual_termination_date,PPOS.projected_termination_date) TERMINATION_DATE
2669 , PAD.postal_code EMP_ZIP_CODE
2670 , PAD.address_line1 ADDRESS_LINE1
2671 , PAD.address_line2 ADDRESS_LINE2
2672 , PAD.address_line3 ADDRESS_LINE3
2673 , PAD.telephone_number_1 PHONE_NUM
2674 , PAF.assignment_id ASSIGNMENT_ID
2675 , PAF.payroll_id PAYROLL_ID
2676 , PPOS.date_start HIRE_DATE
2677 , PPOS.last_standard_process_date LAST_STD_PROCESS_DATE
2678 FROM per_people_f PPF
2679 , per_assignments_f PAF
2680 , per_addresses PAD
2681 , per_periods_of_service PPOS
2682 WHERE PAF.person_id = PPF.person_id
2683 AND PAD.person_id(+) = PPF.person_id
2684 AND PAD.address_type(+) = 'JP_C'
2685 AND TRUNC(NVL(PPOS.actual_termination_date,PPOS.projected_termination_date)) BETWEEN NVL(PAD.date_from,NVL(PPOS.actual_termination_date,PPOS.projected_termination_date))
2686 AND NVL(PAD.date_to,TO_DATE('31/12/4712','dd/mm/yyyy')) --#Bug9648137
2687 AND PPF.person_id = PPOS.person_id
2688 AND TRUNC(NVL(PPOS.actual_termination_date,PPOS.projected_termination_date)) BETWEEN PPF.effective_start_date
2689 AND PPF.effective_end_date
2690 AND TRUNC(NVL(PPOS.actual_termination_date,PPOS.projected_termination_date)) BETWEEN PAF.effective_start_date
2691 AND PAF.effective_end_date
2692 AND PAF.assignment_id = p_assignment_id
2693 AND PPOS.period_of_service_id = NVL(PAF.period_of_service_id,PPOS.period_of_service_id)
2694 ORDER BY PPF.effective_start_date;
2695 --
2696 -- Local Variables
2697 ln_action_info_id pay_action_information.action_information_id%TYPE;
2698 ln_obj_version_num pay_action_information.object_version_number%TYPE;
2699 ln_assignment_id per_all_assignments_f.assignment_id%TYPE;
2700 lc_procedure VARCHAR2(200);
2701 ld_ins_start_date per_time_periods.start_date%TYPE;
2702 --
2703 BEGIN
2704 --
2705 gb_debug := hr_utility.debug_enabled ;
2706 -- initialization_code to set the global tables for EIT
2707 -- that will be used by each thread in multi-threading.
2708 --
2709 initialize(gn_payroll_action_id);
2710 --
2711 IF gb_debug THEN
2712 lc_procedure := gc_package||'archive_code';
2713 hr_utility.set_location('Entering '||lc_procedure,1);
2714 hr_utility.set_location('p_assignment_action_id......= '|| p_assignment_action_id,10);
2715 hr_utility.set_location('p_effective_date............= '|| TO_CHAR(p_effective_date,'DD-MON-YYYY'),10);
2716 END IF;
2717 --
2718 -- Fetch the assignment id
2719 OPEN lcu_get_assignment_id(p_assignment_action_id);
2720 FETCH lcu_get_assignment_id INTO ln_assignment_id;
2721 CLOSE lcu_get_assignment_id;
2722 --
2723 IF gb_debug THEN
2724 hr_utility.set_location('Opening Employee Details cursor for ARCHIVE',30);
2725 hr_utility.set_location('Archiving EMPLOYEE DETAILS',30);
2726 END IF;
2727 --
2728 FOR lr_employee_details IN lcu_employee_details(p_assignment_id => ln_assignment_id
2729 ,p_effective_date => gr_parameters.effective_date)
2730 LOOP
2731 --
2732 -- EMPLOYEE DETAILS ----------
2733 pay_action_information_api.create_action_information
2734 ( p_action_information_id => ln_action_info_id
2735 , p_action_context_id => p_assignment_action_id
2736 , p_action_context_type => 'AAP'
2737 , p_object_version_number => ln_obj_version_num
2738 , p_effective_date => p_effective_date
2739 , p_assignment_id => lr_employee_details.assignment_id
2740 , p_source_id => NULL
2741 , p_source_text => NULL
2742 , p_action_information_category => 'JP_UITE_EMP'
2743 , p_action_information1 => lr_employee_details.EMPLOYEE_NUMBER
2744 , p_action_information2 => lr_employee_details.UI_REGISTERED_NUMBER
2745 , p_action_information3 => lr_employee_details.LAST_NAME_KANA
2746 , p_action_information4 => lr_employee_details.FIRST_NAME_KANA
2747 , p_action_information5 => lr_employee_details.LAST_NAME
2748 , p_action_information6 => lr_employee_details.FIRST_NAME
2749 , p_action_information7 => fnd_date.date_to_canonical(lr_employee_details.TERMINATION_DATE)
2750 , p_action_information8 => lr_employee_details.EMP_ZIP_CODE
2751 , p_action_information9 => lr_employee_details.ADDRESS_LINE1
2752 , p_action_information10 => lr_employee_details.ADDRESS_LINE2
2753 , p_action_information11 => lr_employee_details.ADDRESS_LINE3
2754 , p_action_information12 => lr_employee_details.PHONE_NUM
2755 );
2756 -- SALARY DETAILS----------
2757 proc_sal_arch( p_assignment_action_id => p_assignment_action_id
2758 ,p_payroll_action_id => gn_payroll_action_id
2759 ,p_assignment_id => lr_employee_details.assignment_id
2760 ,p_effective_date => p_effective_date
2761 ,p_termination_date => lr_employee_details.TERMINATION_DATE
2762 ,p_payroll_id => lr_employee_details.payroll_id
2763 ,p_hire_date => lr_employee_details.hire_date
2764 ,p_last_std_process_date => lr_employee_details.last_std_process_date
2765 ,p_ins_start_date => ld_ins_start_date );
2766
2767
2768 -- SPECIAL BONUS DETAILS -------
2769 proc_spb_arch (p_assignment_action_id => p_assignment_action_id
2770 ,p_assignment_id => lr_employee_details.assignment_id
2771 ,p_effective_date => p_effective_date
2772 ,p_period_start_date => ld_ins_start_date
2773 ,p_period_end_date => lr_employee_details.TERMINATION_DATE
2774 ,p_payroll_id => lr_employee_details.payroll_id
2775 );
2776 --
2777 -- Temination details and Wage Instructions ----------
2778 --
2779 proc_term_arch( p_assignment_action_id => p_assignment_action_id
2780 ,p_payroll_action_id => gn_payroll_action_id
2781 ,p_assignment_id => lr_employee_details.assignment_id
2782 ,p_effective_date => p_effective_date
2783 ,p_termination_date => lr_employee_details.TERMINATION_DATE
2784 );
2785
2786 --
2787 END LOOP; -- End LOOP for Employee Details
2788 --
2789 IF gb_debug THEN
2790 hr_utility.set_location('Leaving '||lc_procedure,1);
2791 END IF;
2792 --
2793 EXCEPTION
2794 WHEN gc_exception THEN
2795 IF gb_debug THEN
2796 hr_utility.set_location('Error in '||lc_procedure,999999);
2797 END IF;
2798 RAISE;
2799 WHEN OTHERS THEN
2800 RAISE gc_exception;
2801 END ARCHIVE_CODE;
2802
2803 PROCEDURE deinitialize_code(p_payroll_action_id IN NUMBER)
2804 --************************************************************************
2805 -- PROCEDURE
2806 -- deinitialize_code
2807 --
2808 -- DESCRIPTION
2809 -- This package is used to remove temporary action codes
2810 --
2811 -- ACCESS
2812 -- PUBLIC
2813 --
2814 -- PARAMETERS
2815 -- ==========
2816 -- NAME TYPE DESCRIPTION
2817 -- ----------------- -------- ---------------------------------------
2818 -- p_payroll_action_id IN This parameter passes Assignment Action Id
2819 --
2820 -- PREREQUISITES
2821 -- None
2822 --
2823 -- CALLED BY
2824 -- None
2825 --************************************************************************/
2826
2827 IS
2828 --
2829 CURSOR lcu_office_details
2830 IS
2831 SELECT HOI.org_information1 LOCATION_NUMBER
2832 ,HOI.org_information2 BUSINESS_ADDRESS1
2833 ,HOI.org_information3 BUSINESS_ADDRESS2
2834 ,HOI.org_information4 BUSINESS_ADDRESS3
2835 ,HOI.org_information5 EMPLOYER_ADDRESS1
2836 ,HOI.org_information6 EMPLOYER_ADDRESS2
2837 ,HOI.org_information7 EMPLOYER_ADDRESS3
2838 ,HOI.org_information8 LOCATION_NAME
2839 ,HOI.org_information9 EMPLOYER_NAME
2840 ,HOI.org_information10 EMPLOYER_FULL_NAME
2841 ,HOI.org_information11 COMPANY_PHONE
2842 FROM hr_organization_information HOI
2843 WHERE HOI.org_information_context = 'JP_LI_UNION_INFO'
2844 AND HOI.organization_id = gr_parameters.labor_insorg_id;
2845 --
2846 lc_proc CONSTANT VARCHAR2(61) := gc_package || 'deinitialise_code';
2847 ln_action_info_id pay_action_information.action_information_id%TYPE;
2848 ln_obj_version_num pay_action_information.object_version_number%TYPE;
2849
2850 --
2851 BEGIN
2852 gb_debug := hr_utility.debug_enabled ;
2853 --
2854 IF gb_debug THEN
2855 hr_utility.set_location('Entering: ' || lc_proc, 10);
2856 END IF;
2857 --
2858 -- Office Details
2859 --
2860 FOR lr_office_details IN lcu_office_details
2861 --
2862 LOOP
2863 --
2864 pay_action_information_api.create_action_information
2865 ( p_action_information_id => ln_action_info_id
2866 , p_action_context_id => p_payroll_action_id
2867 , p_action_context_type => 'PA'
2868 , p_object_version_number => ln_obj_version_num
2869 , p_effective_date => gr_parameters.effective_date
2870 , p_source_id => NULL
2871 , p_source_text => NULL
2872 , p_action_information_category => 'JP_UITE_OFFICE'
2873 , p_action_information1 => lr_office_details.LOCATION_NUMBER
2874 , p_action_information2 => lr_office_details.LOCATION_NAME
2875 , p_action_information3 => lr_office_details.BUSINESS_ADDRESS1
2876 , p_action_information4 => lr_office_details.BUSINESS_ADDRESS2
2877 , p_action_information5 => lr_office_details.BUSINESS_ADDRESS3
2878 , p_action_information6 => lr_office_details.COMPANY_PHONE
2879 , p_action_information7 => lr_office_details.EMPLOYER_FULL_NAME
2880 , p_action_information8 => lr_office_details.EMPLOYER_ADDRESS1
2881 , p_action_information9 => lr_office_details.EMPLOYER_ADDRESS2
2882 , p_action_information10 => lr_office_details.EMPLOYER_ADDRESS3
2883 , p_action_information11 => lr_office_details.EMPLOYER_NAME
2884 );
2885 --
2886 END LOOP;
2887 --
2888 IF gb_debug THEN
2889 hr_utility.set_location('Leaving: ' || lc_proc, 10);
2890 END IF;
2891 --
2892 END deinitialize_code;
2893 --
2894 END pay_jp_uite_arch_pkg;