[Home] [Help]
PACKAGE BODY: APPS.PAY_SA_ARCHIVE
Source
1 PACKAGE BODY PAY_SA_ARCHIVE AS
2 /* $Header: pysaparc.pkb 120.6.12010000.3 2008/08/06 08:17:01 ubhat ship $ */
3
4 g_debug boolean := hr_utility.debug_enabled;
5
6 TYPE element_rec IS RECORD (
7 classification_name VARCHAR2(60)
8 ,element_name VARCHAR2(60)
9 ,element_type_id NUMBER
10 ,input_value_id NUMBER
11 ,element_type VARCHAR2(1)
12 ,uom VARCHAR2(1)
13 ,archive_flag VARCHAR2(1));
14
15 TYPE balance_rec IS RECORD (
16 balance_name VARCHAR2(60),
17 defined_balance_id NUMBER,
18 balance_type_id NUMBER);
19
20 TYPE lock_rec IS RECORD (
21 archive_assact_id NUMBER);
22
23
24 TYPE element_table IS TABLE OF element_rec INDEX BY BINARY_INTEGER;
25 TYPE balance_table IS TABLE OF balance_rec INDEX BY BINARY_INTEGER;
26 TYPE lock_table IS TABLE OF lock_rec INDEX BY BINARY_INTEGER;
27
28 g_element_table element_table;
29 g_user_balance_table balance_table;
30 g_lock_table lock_table;
31
32 g_index NUMBER := -1;
33 g_index_assact NUMBER := -1;
34 g_index_bal NUMBER := -1;
35
36 g_package VARCHAR2(33) := ' PAY_SA_ARCHIVE.';
37 g_payroll_action_id NUMBER;
38 g_arc_payroll_action_id NUMBER;
39 g_business_group_id NUMBER;
40 g_format_mask VARCHAR2(50);
41
42 g_err_num NUMBER;
43 g_errm VARCHAR2(150);
44
45
46 /* GET PARAMETER */
47 FUNCTION GET_PARAMETER(
48 p_parameter_string IN VARCHAR2
49 ,p_token IN VARCHAR2
50 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
51 IS
52
53 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
54 l_start_pos NUMBER;
55 l_delimiter VARCHAR2(1):=' ';
56 l_proc VARCHAR2(40):= g_package||' get parameter ';
57
58 BEGIN
59 --
60
61 IF g_debug THEN
62 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
63 END IF;
64
65 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
66 --
67 IF l_start_pos = 0 THEN
68 l_delimiter := '|';
69 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
70 END IF;
71
72 IF l_start_pos <> 0 THEN
73 l_start_pos := l_start_pos + length(p_token||'=');
74 l_parameter := substr(p_parameter_string,
75 l_start_pos,
76 instr(p_parameter_string||' ',
77 l_delimiter,l_start_pos)
78 - l_start_pos);
79 IF p_segment_number IS NOT NULL THEN
80 l_parameter := ':'||l_parameter||':';
81 l_parameter := substr(l_parameter,
82 instr(l_parameter,':',1,p_segment_number)+1,
83 instr(l_parameter,':',1,p_segment_number+1) -1
84 - instr(l_parameter,':',1,p_segment_number));
85 END IF;
86 END IF;
87 --
88 RETURN l_parameter;
89
90 IF g_debug THEN
91 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
92 END IF;
93
94 END;
95
96 /* GET ALL PARAMETERS */
97 PROCEDURE GET_ALL_PARAMETERS(
98 p_payroll_action_id IN NUMBER
99 ,p_business_group_id OUT NOCOPY NUMBER
100 ,p_start_date OUT NOCOPY VARCHAR2
101 ,p_end_date OUT NOCOPY VARCHAR2
102 ,p_effective_date OUT NOCOPY DATE
103 ,p_payroll_id OUT NOCOPY VARCHAR2
104 ,p_consolidation_set OUT NOCOPY VARCHAR2) IS
105 --
106 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
107
108 SELECT PAY_SA_ARCHIVE.GET_PARAMETER(legislative_parameters,'PAYROLL_ID')
109 ,PAY_SA_ARCHIVE.GET_PARAMETER(legislative_parameters,'CONSOLIDATION_SET_ID')
110 ,PAY_SA_ARCHIVE.GET_PARAMETER(legislative_parameters,'START_DATE')
111 ,PAY_SA_ARCHIVE.GET_PARAMETER(legislative_parameters,'END_DATE')
112 ,effective_date
113 ,business_group_id
114 FROM pay_payroll_actions
115 WHERE payroll_action_id = p_payroll_action_id;
116 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
117 --
118 BEGIN
119 OPEN csr_parameter_info (p_payroll_action_id);
120 FETCH csr_parameter_info INTO p_payroll_id
121 ,p_consolidation_set
122 ,p_start_date
123 ,p_end_date
124 ,p_effective_date
125 ,p_business_group_id;
126 CLOSE csr_parameter_info;
127 --
128
129 IF g_debug THEN
130 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
131 END IF;
132 END GET_ALL_PARAMETERS;
133
134
135
136 /* RANGE CODE */
137 PROCEDURE RANGE_CODE (p_payroll_action_id IN NUMBER
138 ,p_sql OUT NOCOPY VARCHAR2)
139 IS
140
141 CURSOR csr_get_message(p_bus_grp_id NUMBER) IS
142 SELECT org_information6 message
143 FROM hr_organization_information
144 WHERE organization_id = p_bus_grp_id
145 AND org_information_context = 'Business Group:Payslip Info'
146 AND org_information1 = 'MESG';
147
148 -----------------------------------------------------------------
149 -- BALANCES
150 -----------------------------------------------------------------
151 /* Cursor to retrieve Other Balances Information */
152 CURSOR csr_get_balance(p_bus_grp_id NUMBER) IS
153 SELECT org_information1 balance_type_id
154 ,org_information2 balance_dim_id
155 ,org_information3 narrative
156 FROM hr_organization_information
157 WHERE organization_id = p_bus_grp_id
158 AND org_information_context = 'SA_PAYSLIP_BALANCES';
159
160
161 /* Cursor to fetch defined balance id */
162 CURSOR csr_def_balance(bal_type_id NUMBER, bal_dim_id NUMBER) IS
163 SELECT defined_balance_id
164 FROM pay_defined_balances
165 WHERE balance_type_id = bal_type_id
166 AND balance_dimension_id = bal_dim_id;
167
168 -----------------------------------------------------
169 --ELEMENTS
170 ----------------------------------------------------
171
172 /* Cursor to retrieve Time Period Information */
173 CURSOR csr_time_periods(p_run_payact_id NUMBER
174 ,p_payroll_id NUMBER) IS
175 SELECT ptp.end_date end_date,
176 ptp.start_date start_date,
177 ptp.period_name period_name,
178 ppf.payroll_name payroll_name
179 FROM per_time_periods ptp
180 ,pay_payroll_actions ppa
181 ,pay_payrolls_f ppf
182 WHERE ptp.payroll_id = ppa.payroll_id
183 AND ppa.payroll_action_id = p_run_payact_id
184 AND ppa.payroll_id = ppf.payroll_id
185 AND ppf.payroll_id = NVL(p_payroll_id , ppf.payroll_id)
186 AND ppa.date_earned BETWEEN ptp.start_date
187 AND ptp.end_date
188 AND ppa.date_earned BETWEEN ppf.effective_start_date
189 AND ppf.effective_end_date;
190
191 --------------------------------------------------------------
192 -- Additional Element
193 --------------------------------------------------------------
194 /* Cursor to retrieve Additional Element Information */
195 CURSOR csr_get_element(p_bus_grp_id NUMBER, p_date_earned DATE) IS
196 SELECT hoi.org_information1 element_type_id
197 ,hoi.org_information2 input_value_id
198 ,hoi.org_information3 element_narrative
199 ,pec.classification_name
200 ,piv.uom
201 FROM hr_organization_information hoi
202 ,pay_element_classifications pec
203 ,pay_element_types_f pet
204 ,pay_input_values_f piv
205 WHERE hoi.organization_id = p_bus_grp_id
206 AND hoi.org_information_context = 'SA_PAYSLIP_ELEMENTS'
207 AND hoi.org_information1 = pet.element_type_id
208 AND pec.classification_id = pet.classification_id
209 AND piv.input_value_id = hoi.org_information2
210 AND p_date_earned BETWEEN piv.effective_start_date
211 AND piv.effective_end_date;
212
213
214
215
216 rec_time_periods csr_time_periods%ROWTYPE;
217
218 rec_get_balance csr_get_balance%ROWTYPE;
219 rec_get_message csr_get_message%ROWTYPE;
220 rec_get_element csr_get_element%ROWTYPE;
221 l_action_info_id NUMBER;
222 l_ovn NUMBER;
223 l_business_group_id NUMBER;
224 l_start_date VARCHAR2(30);
225 l_end_date VARCHAR2(30);
226 l_effective_date DATE;
227 l_consolidation_set NUMBER;
228 l_defined_balance_id NUMBER := 0;
229 l_count NUMBER := 0;
230 l_prev_prepay NUMBER := 0;
231 l_canonical_start_date DATE;
232 l_canonical_end_date DATE;
233 l_payroll_id NUMBER;
234 l_prepay_action_id NUMBER;
235 l_actid NUMBER;
236 l_assignment_id NUMBER;
237 l_action_sequence NUMBER;
238 l_assact_id NUMBER;
239 l_pact_id NUMBER;
240 l_flag NUMBER := 0;
241 l_element_context VARCHAR2(5);
242
243 BEGIN
244
245 IF g_debug THEN
246 hr_utility.set_location(' Entering Procedure RANGE_CODE',40);
247 END IF;
248
249 PAY_SA_ARCHIVE.GET_ALL_PARAMETERS(p_payroll_action_id
250 ,l_business_group_id
251 ,l_start_date
252 ,l_end_date
253 ,l_effective_date
254 ,l_payroll_id
255 ,l_consolidation_set);
256
257
258 l_canonical_start_date := TO_DATE(l_start_date,'YYYY/MM/DD');
259 l_canonical_end_date := TO_DATE(l_end_date,'YYYY/MM/DD');
260
261 OPEN csr_get_message(l_business_group_id);
262 LOOP
263 FETCH csr_get_message INTO rec_get_message;
264 EXIT WHEN csr_get_message%NOTFOUND;
265
266 pay_action_information_api.create_action_information (
267 p_action_information_id => l_action_info_id
268 ,p_action_context_id => p_payroll_action_id
269 ,p_action_context_type => 'PA'
270 ,p_object_version_number => l_ovn
271 ,p_effective_date => l_effective_date
272 ,p_source_id => NULL
273 ,p_source_text => NULL
274 ,p_action_information_category => 'EMPLOYEE OTHER INFORMATION'
275 ,p_action_information1 => l_business_group_id
276 ,p_action_information2 => 'MESG' -- Message Context
277 ,p_action_information3 => NULL
278 ,p_action_information4 => NULL
279 ,p_action_information5 => NULL
280 ,p_action_information6 => rec_get_message.message);
281 END LOOP;
282 CLOSE csr_get_message;
283
284
285
286
287
288 -------------------------------------------------------------------------------------
289 -- Initialize Balance Definitions
290 -------------------------------------------------------------------------------------
291
292 OPEN csr_get_balance(l_business_group_id);
293 LOOP
294 FETCH csr_get_balance INTO rec_get_balance;
295 EXIT WHEN csr_get_balance%NOTFOUND;
296
297 OPEN csr_def_balance(rec_get_balance.balance_type_id,rec_get_balance.balance_dim_id);
298 FETCH csr_def_balance INTO l_defined_balance_id;
299 CLOSE csr_def_balance;
300
301 BEGIN
302
303
304 SELECT 1 INTO l_flag
305 FROM pay_action_information
306 WHERE action_information_category = 'EMEA BALANCE DEFINITION'
307 AND action_context_id = p_payroll_action_id
308 AND action_information2 = l_defined_balance_id
309 AND action_information6 = 'OBAL'
310 AND action_information4 = rec_get_balance.narrative;
311
312 EXCEPTION WHEN NO_DATA_FOUND THEN
313 pay_action_information_api.create_action_information (
314 p_action_information_id => l_action_info_id
315 ,p_action_context_id => p_payroll_action_id
316 ,p_action_context_type => 'PA'
317 ,p_object_version_number => l_ovn
318 ,p_effective_date => l_effective_date
319 ,p_source_id => NULL
320 ,p_source_text => NULL
321 ,p_action_information_category => 'EMEA BALANCE DEFINITION'
322 ,p_action_information1 => NULL
323 ,p_action_information2 => l_defined_balance_id
324 ,p_action_information4 => rec_get_balance.narrative
325 ,p_action_information6 => 'OBAL');
326 WHEN OTHERS THEN
327 NULL;
328 END;
329
330 END LOOP;
331 CLOSE csr_get_balance;
332
333 -----------------------------------------------------------------------------
334 --Initialize Element Definitions
335 -----------------------------------------------------------------------------
336 g_business_group_id := l_business_group_id;
337
338
339 ARCHIVE_ELEMENT_INFO(p_payroll_action_id => p_payroll_action_id
340 ,p_effective_date => l_effective_date
341 ,p_date_earned => l_canonical_end_date
342 ,p_pre_payact_id => NULL);
343
344
345 -----------------------------------------------------------------------------
346 --Archive Additional Element Definitions
347 -----------------------------------------------------------------------------
348
349 l_element_context := 'F';
350
351 OPEN csr_get_element(l_business_group_id,l_canonical_end_date);
352 LOOP
353 FETCH csr_get_element INTO rec_get_element;
354 EXIT WHEN csr_get_element%NOTFOUND;
355
356
357
358 BEGIN
359
360 SELECT 1 INTO l_flag
361 FROM pay_action_information
362 WHERE action_context_id = p_payroll_action_id
363 AND action_information_category = 'EMEA ELEMENT DEFINITION'
364 AND action_information2 = rec_get_element.element_type_id
365 AND action_information3 = rec_get_element.input_value_id
366 AND action_information5 = l_element_context;
367
368
369 EXCEPTION WHEN NO_DATA_FOUND THEN
370
371 pay_action_information_api.create_action_information (
372 p_action_information_id => l_action_info_id
373 ,p_action_context_id => p_payroll_action_id
374 ,p_action_context_type => 'PA'
375 ,p_object_version_number => l_ovn
376 ,p_effective_date => l_effective_date
377 ,p_source_id => NULL
378 ,p_source_text => NULL
379 ,p_action_information_category => 'EMEA ELEMENT DEFINITION'
380 ,p_action_information1 => NULL
381 ,p_action_information2 => rec_get_element.element_type_id
382 ,p_action_information3 => rec_get_element.input_value_id
383 ,p_action_information4 => rec_get_element.element_narrative
384 ,p_action_information5 => l_element_context
385 ,p_action_information6 => rec_get_element.uom
386 ,p_action_information7 => l_element_context);
387 WHEN OTHERS THEN
388 NULL;
389 END;
390 END LOOP;
391 CLOSE csr_get_element;
392
393 p_sql := 'SELECT DISTINCT person_id
394 FROM per_people_f ppf
395 ,pay_payroll_actions ppa
396 WHERE ppa.payroll_action_id = :payroll_action_id
397 AND ppa.business_group_id = ppf.business_group_id
398 ORDER BY ppf.person_id';
399
400 IF g_debug THEN
401 hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
402 END IF;
403
404 EXCEPTION
405 WHEN OTHERS THEN
406 -- Return cursor that selects no rows
407 p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
408
409 END RANGE_CODE;
410
411
415 ,p_start_person IN NUMBER
412 /* ASSIGNMENT ACTION CODE */
413 PROCEDURE ASSIGNMENT_ACTION_CODE
414 (p_payroll_action_id IN NUMBER
416 ,p_end_person IN NUMBER
417 ,p_chunk IN NUMBER)
418 IS
419
420 CURSOR csr_prepaid_assignments(p_payroll_action_id NUMBER,
421 p_start_person NUMBER,
422 p_end_person NUMBER,
423 p_payroll_id NUMBER,
424 p_consolidation_id NUMBER,
425 l_canonical_start_date DATE,
426 l_canonical_end_date DATE)
427 IS
428 SELECT act.assignment_id assignment_id,
429 act.assignment_action_id run_action_id,
430 act1.assignment_action_id prepaid_action_id
431 FROM pay_payroll_actions ppa,
432 pay_payroll_actions appa,
433 pay_payroll_actions appa2,
434 pay_assignment_actions act,
435 pay_assignment_actions act1,
436 pay_action_interlocks pai,
437 per_all_assignments_f as1
438 WHERE ppa.payroll_action_id = p_payroll_action_id
439 AND appa.consolidation_set_id = p_consolidation_id
440 AND appa.effective_date BETWEEN l_canonical_start_date
441 AND l_canonical_end_date
442 AND as1.person_id BETWEEN p_start_person
443 AND p_end_person
444 AND appa.action_type IN ('R','Q')
445 -- Payroll Run or Quickpay Run
446 AND act.payroll_action_id = appa.payroll_action_id
447 AND act.source_action_id IS NULL -- Master Action
448 AND as1.assignment_id = act.assignment_id
449 AND ppa.effective_date BETWEEN as1.effective_start_date
450 AND as1.effective_end_date
451 AND act.action_status = 'C' -- Completed
452 AND act.assignment_action_id = pai.locked_action_id
453 AND act1.assignment_action_id = pai.locking_action_id
454 AND act1.action_status = 'C' -- Completed
455 AND act1.payroll_action_id = appa2.payroll_action_id
456 AND appa2.action_type IN ('P','U')
457 AND appa2.effective_date BETWEEN l_canonical_start_date
458 AND l_canonical_end_date
459 -- Prepayments or Quickpay Prepayments
460 AND (as1.payroll_id = p_payroll_id OR p_payroll_id IS NULL)
461 AND NOT EXISTS (SELECT /*+ ORDERED */ NULL
462 FROM pay_action_interlocks pai1,
463 pay_assignment_actions act2,
464 pay_payroll_actions appa3
465 WHERE pai1.locked_action_id = act.assignment_action_id
466 AND act2.assignment_action_id= pai1.locking_action_id
467 AND act2.payroll_action_id = appa3.payroll_action_id
468 AND appa3.action_type = 'X'
469 AND appa3.action_status = 'C'
470 AND appa3.report_type = 'SA_ARCHIVE')
471 AND NOT EXISTS ( SELECT /*+ ORDERED */ NULL
472 FROM pay_action_interlocks pai1,
473 pay_assignment_actions act2,
474 pay_payroll_actions appa3
475 WHERE pai1.locked_action_id = act.assignment_action_id
476 AND act2.assignment_action_id= pai1.locking_action_id
477 AND act2.payroll_action_id = appa3.payroll_action_id
478 AND appa3.action_type = 'V'
479 AND appa3.action_status = 'C')
480 ORDER BY act.assignment_id;
481
482
483
484
485 l_count NUMBER := 0;
486 l_prev_prepay NUMBER := 0;
487 l_business_group_id NUMBER;
488 l_start_date VARCHAR2(20);
489 l_end_date VARCHAR2(20);
490 l_canonical_start_date DATE;
491 l_canonical_end_date DATE;
492 l_effective_date DATE;
493 l_payroll_id NUMBER;
494 l_consolidation_set NUMBER;
495 l_prepay_action_id NUMBER;
496 l_actid NUMBER;
497 l_assignment_id NUMBER;
498 l_action_sequence NUMBER;
499 l_assact_id NUMBER;
500 l_pact_id NUMBER;
501 l_flag NUMBER := 0;
502 l_defined_balance_id NUMBER :=0;
503 l_action_info_id NUMBER;
504 l_ovn NUMBER;
505
506
507 BEGIN
508
509 IF g_debug THEN
510 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',60);
511 END IF;
512
513 PAY_SA_ARCHIVE.GET_ALL_PARAMETERS(p_payroll_action_id
514 ,l_business_group_id
515 ,l_start_date
516 ,l_end_date
517 ,l_effective_date
518 ,l_payroll_id
519 ,l_consolidation_set);
520
521
522 l_canonical_start_date := TO_DATE(l_start_date,'YYYY/MM/DD');
523 l_canonical_end_date := TO_DATE(l_end_date,'YYYY/MM/DD');
524
525 l_prepay_action_id := 0;
526
527 FOR rec_prepaid_assignments IN csr_prepaid_assignments(p_payroll_action_id
528 ,p_start_person
529 ,p_end_person
530 ,l_payroll_id
531 ,l_consolidation_set
532 ,l_canonical_start_date
533 ,l_canonical_end_date) LOOP
534
535 IF l_prepay_action_id <> rec_prepaid_assignments.prepaid_action_id THEN
536 SELECT pay_assignment_actions_s.NEXTVAL
537 INTO l_actid
541 g_lock_table(g_index_assact).archive_assact_id := l_actid; /* For Element archival */
538 FROM dual;
539 --
540 g_index_assact := g_index_assact + 1;
542
543
544
545 -- Create the archive assignment action
546 hr_nonrun_asact.insact(l_actid
547 ,rec_prepaid_assignments.assignment_id
548 ,p_payroll_action_id
549 ,p_chunk
550 ,NULL);
551
552 -- Create archive to prepayment assignment action interlock
553 --
554 hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.prepaid_action_id);
555
556
557 END IF;
558 -- create archive to master assignment action interlock
559
560 hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.run_action_id);
561 l_prepay_action_id := rec_prepaid_assignments.prepaid_action_id;
562
563 END LOOP;
564
565
566
567
568
569 IF g_debug THEN
570 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',70);
571 END IF;
572 END ASSIGNMENT_ACTION_CODE;
573
574
575 /* INITIALIZATION CODE */
576 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
577 IS
578
579 CURSOR csr_prepay_id IS
580 SELECT distinct prepay_payact.payroll_action_id prepay_payact_id
581 ,run_payact.date_earned date_earned
582 FROM pay_action_interlocks archive_intlck
583 ,pay_assignment_actions prepay_assact
584 ,pay_payroll_actions prepay_payact
585 ,pay_action_interlocks prepay_intlck
586 ,pay_assignment_actions run_assact
587 ,pay_payroll_actions run_payact
588 ,pay_assignment_actions archive_assact
589 WHERE archive_intlck.locking_action_id = archive_assact.assignment_action_id
590 and archive_assact.payroll_action_id = p_payroll_action_id
591 AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
592 AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
593 AND prepay_payact.action_type IN ('U','P')
594 AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
595 AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
596 AND run_payact.payroll_action_id = run_assact.payroll_action_id
597 AND run_payact.action_type IN ('Q', 'R')
598 ORDER BY prepay_payact.payroll_action_id;
599
600 /* Cursor to retrieve Run Assignment Action Ids */
601 CURSOR csr_runact_id IS
602 SELECT distinct prepay_payact.payroll_action_id prepay_payact_id
603 ,run_payact.date_earned date_earned
604 ,run_payact.payroll_action_id run_payact_id
605 FROM pay_action_interlocks archive_intlck
606 ,pay_assignment_actions prepay_assact
607 ,pay_payroll_actions prepay_payact
608 ,pay_action_interlocks prepay_intlck
609 ,pay_assignment_actions run_assact
610 ,pay_payroll_actions run_payact
611 ,pay_assignment_actions archive_assact
612 WHERE archive_intlck.locking_action_id = archive_assact.assignment_action_id
613 and archive_assact.payroll_action_id = p_payroll_action_id
614 AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
615 AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
616 AND prepay_payact.action_type IN ('U','P')
617 AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
618 AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
619 AND run_payact.payroll_action_id = run_assact.payroll_action_id
620 AND run_payact.action_type IN ('Q', 'R')
621 ORDER BY prepay_payact.payroll_action_id;
622
623 rec_prepay_id csr_prepay_id%ROWTYPE;
624 rec_runact_id csr_runact_id%ROWTYPE;
625
626 l_action_info_id NUMBER;
627 l_ovn NUMBER;
628 l_count NUMBER := 0;
629 l_business_group_id NUMBER;
630 l_start_date VARCHAR2(20);
631 l_end_date VARCHAR2(20);
632 l_effective_date DATE;
633 l_payroll_id NUMBER;
634 l_consolidation_set NUMBER;
635 l_prev_prepay NUMBER := 0;
636
637
638 BEGIN
639
640 IF g_debug THEN
641 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
642 END IF;
643
644 /*fnd_file.put_line(fnd_file.log,'In INIT_CODE 0');*/
645
646 GET_ALL_PARAMETERS(p_payroll_action_id
647 ,l_business_group_id
648 ,l_start_date
649 ,l_end_date
650 ,l_effective_date
651 ,l_payroll_id
652 ,l_consolidation_set);
653
654 g_arc_payroll_action_id := p_payroll_action_id;
655
656 g_business_group_id := l_business_group_id;
657
658
659
660 /* Archive Element Details */
661
662 OPEN csr_prepay_id;
663 LOOP
664 FETCH csr_prepay_id INTO rec_prepay_id;
665 EXIT WHEN csr_prepay_id%NOTFOUND;
666
667 ---------------------------------------------------------
668 --Initialize Global tables once every prepayment payroll
669 --action id and once every thread
670 ---------------------------------------------------------
671
672 IF (rec_prepay_id.prepay_payact_id <> l_prev_prepay) THEN
673
674 ARCHIVE_ADD_ELEMENT(p_archive_assact_id => NULL,
678 p_date_earned => rec_prepay_id.date_earned,
675 p_assignment_action_id => NULL,
676 p_assignment_id => NULL,
677 p_payroll_action_id => p_payroll_action_id,
679 p_effective_date => l_effective_date,
680 p_pre_payact_id => rec_prepay_id.prepay_payact_id,
681 p_archive_flag => 'N');
682
683 END IF;
684
685 l_prev_prepay := rec_prepay_id.prepay_payact_id;
686
687 END LOOP;
688 CLOSE csr_prepay_id;
689
690
691 /* Initialize Global tables for Balances */
692 ARCHIVE_OTH_BALANCE(p_archive_assact_id => NULL,
693 p_assignment_action_id => NULL,
694 p_assignment_id => NULL,
695 p_payroll_action_id => p_payroll_action_id,
696 p_record_count => NULL,
697 p_pre_payact_id => NULL, --rec_prepay_id.prepay_payact_id,
698 p_effective_date => l_effective_date,
699 p_date_earned => NULL,
700 p_archive_flag => 'N');
701
702 IF g_debug THEN
703 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
704 END IF;
705
706 EXCEPTION WHEN OTHERS THEN
707 g_err_num := SQLCODE;
708
709 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In INITIALIZATION_CODE');*/
710 IF g_debug THEN
711 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In INITIALIZATION_CODE',180);
712 END IF;
713
714 END INITIALIZATION_CODE;
715
716 PROCEDURE SETUP_ELEMENT_DEFINITIONS( p_classification_name IN VARCHAR2
717 ,p_element_name IN VARCHAR2
718 ,p_element_type_id IN NUMBER
719 ,p_input_value_id IN NUMBER
720 ,p_element_type IN VARCHAR2
721 ,p_uom IN VARCHAR2
722 ,p_archive_flag IN VARCHAR2)
723 IS
724
725 BEGIN
726
727 IF g_debug THEN
728 hr_utility.set_location(' Entering Procedure SETUP_ELEMENT_DEFINITIONS',100);
729 END IF;
730
731
732 g_index := g_index + 1;
733
734 /* Initialize global tables that hold Additional Element details */
735 g_element_table(g_index).classification_name := p_classification_name;
736 g_element_table(g_index).element_name := p_element_name;
737 g_element_table(g_index).element_type := p_element_type;
738 g_element_table(g_index).element_type_id := p_element_type_id;
739 g_element_table(g_index).input_value_id := p_input_value_id;
740 g_element_table(g_index).uom := p_uom;
741 g_element_table(g_index).archive_flag := p_archive_flag;
742
743 IF g_debug THEN
744 hr_utility.set_location(' Leaving Procedure SETUP_ELEMENT_DEFINITIONS',110);
745 END IF;
746
747 END SETUP_ELEMENT_DEFINITIONS;
748
749 PROCEDURE SETUP_BALANCE_DEFINITIONS(p_balance_name IN VARCHAR2
750 ,p_defined_balance_id IN NUMBER
751 ,p_balance_type_id IN NUMBER)
752
753 IS
754 BEGIN
755
756 IF g_debug THEN
757 hr_utility.set_location(' Entering Procedure SETUP_BALANCE_DEFINITIONS',120);
758 END IF;
759
760 g_index_bal := g_index_bal + 1;
761 /* Initialize global tables that hold Other Balances details */
762 g_user_balance_table(g_index_bal).balance_name := p_balance_name;
763 g_user_balance_table(g_index_bal).defined_balance_id := p_defined_balance_id;
764 g_user_balance_table(g_index_bal).balance_type_id := p_balance_type_id;
765
766 IF g_debug THEN
767 hr_utility.set_location(' Leaving Procedure SETUP_BALANCE_DEFINITIONS',130);
768 END IF;
769
770 END SETUP_BALANCE_DEFINITIONS;
771
772
773 /* GET COUNTRY NAME FROM CODE */
774 FUNCTION GET_COUNTRY_NAME(p_territory_code VARCHAR2)
775 RETURN VARCHAR2
776 IS
777
778 CURSOR csr_get_territory_name(p_territory_code VARCHAR2) Is
779 SELECT territory_short_name
780 FROM fnd_territories_vl
781 WHERE territory_code = p_territory_code;
782
783 l_country fnd_territories_vl.territory_short_name%TYPE;
784 BEGIN
785
786 IF g_debug THEN
787 hr_utility.set_location(' Entering Function GET_COUNTRY_NAME',140);
788 END IF;
789
790 OPEN csr_get_territory_name(p_territory_code);
791 FETCH csr_get_territory_name into l_country;
792 CLOSE csr_get_territory_name;
793
794 RETURN l_country;
795
796 IF g_debug THEN
797 hr_utility.set_location(' Leaving Function GET_COUNTRY_NAME',150);
798 END IF;
799
800 END GET_COUNTRY_NAME;
801
802 /* EMPLOYEE DETAILS REGION */
803 PROCEDURE ARCHIVE_EMPLOYEE_DETAILS (p_archive_assact_id IN NUMBER
804 ,p_assignment_id IN NUMBER
805 ,p_assignment_action_id IN NUMBER
806 ,p_payroll_action_id IN NUMBER
807 ,p_time_period_id IN NUMBER
808 ,p_date_earned IN DATE
809 ,p_pay_date_earned IN DATE
810 ,p_effective_date IN DATE) IS
811
812 /* Cursor to retrieve person details about Employee */
816 ppf.national_identifier ni_number,
813 CURSOR csr_person_details(p_assignment_id NUMBER) IS
814 SELECT ppf.person_id person_id,
815 ppf.full_name full_name,
817 ppf.nationality nationality,
818 pps.date_start start_date,
819 ppf.employee_number emp_num,
820 ppf.first_name first_name,
821 ppf.last_name last_name,
822 ppf.per_information1 father_name,
823 ppf.per_information2 g_father_name,
824 ppf.title title,
825 paf.location_id loc_id,
826 paf.organization_id org_id,
827 paf.job_id job_id,
828 paf.position_id pos_id,
829 paf.grade_id grade_id,
830 paf.business_group_id bus_grp_id
831 FROM per_assignments_f paf,
832 per_all_people_f ppf,
833 per_periods_of_service pps
834 WHERE paf.person_id = ppf.person_id
835 AND paf.assignment_id = p_assignment_id
836 AND pps.person_id = ppf.person_id
837 AND p_date_earned BETWEEN paf.effective_start_date
838 AND paf.effective_end_date
839 AND p_date_earned BETWEEN ppf.effective_start_date
840 AND ppf.effective_end_date;
841
842
843 /* Cursor to retrieve primary address of Employee */
844 CURSOR csr_primary_address(p_person_id NUMBER) IS
845 SELECT pa.person_id person_id,
846 pa.style style,
847 pa.address_type ad_type,
848 pa.country country,
849 pa.region_1 R1,
850 pa.region_2 R2,
851 pa.region_3 R3,
852 pa.town_or_city city,
853 pa.address_line1 AL1,
854 pa.address_line2 AL2,
855 pa.address_line3 AL3,
856 pa.postal_code postal_code
857 FROM per_addresses pa
858 WHERE pa.primary_flag = 'Y'
859 AND pa.person_id = p_person_id
860 AND p_effective_date BETWEEN pa.date_from
861 AND NVL(pa.date_to,to_date('31-12-4712','DD-MM-YYYY'));
862
863 /* Cursor to retrieve Employer's Address */
864 CURSOR csr_employer_address(p_assignment_id NUMBER) IS
865 SELECT paa.person_id person_id
866 ,hscl.segment1 org_id
867 ,hla.style style
868 ,hla.country country
869 ,hla.region_1 R1
870 ,hla.region_2 R2
871 ,hla.region_3 R3
872 ,hla.town_or_city city
873 ,hla.address_line_1 AL1
874 ,hla.address_line_2 AL2
875 ,hla.address_line_3 AL3
876 ,hla.postal_code postal_code
877 FROM hr_locations_all hla
878 ,hr_organization_units hou
879 ,per_all_assignments_f paa
880 ,hr_soft_coding_keyflex hscl
881 WHERE hscl.segment1 = hou.organization_id
882 AND hscl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
883 AND hou.location_id = hla.location_id
884 AND paa.assignment_id = p_assignment_id
885 AND paa.business_group_id = hou.business_group_id
886 AND p_date_earned BETWEEN paa.effective_start_date
887 AND paa.effective_end_date;
888
889 CURSOR csr_organization_address(p_organization_id NUMBER) IS
890 SELECT hla.style style
891 ,hla.address_line_1 AL1
892 ,hla.address_line_2 AL2
893 ,hla.address_line_3 AL3
894 ,hla.town_or_city city
895 ,hla.region_1 R1
896 ,hla.region_2 R2
897 ,hla.region_3 R3
898 ,hla.country country
899 ,hla.postal_code postal_code
900 FROM hr_locations_all hla,
901 hr_organization_units hoa
902 WHERE hla.location_id = hoa.location_id
903 AND hoa.organization_id = p_organization_id
904 AND p_effective_date BETWEEN hoa.date_from
905 AND NVL(hoa.date_to,to_date('31-12-4712','DD-MM-YYYY'));
906
907
908 /* Cursor to retrieve top most organization in the hierarchy */
909 CURSOR csr_top_org(p_org_structure_version_id NUMBER) IS
910 SELECT DISTINCT pose.organization_id_parent
911 FROM per_org_structure_elements pose
912 WHERE pose.org_structure_version_id = p_org_structure_version_id
913 AND pose.organization_id_parent NOT IN (SELECT pose1.organization_id_child
914 FROM per_org_structure_elements pose1
915 WHERE pose1.org_structure_version_id = p_org_structure_version_id);
916
917 /* Cursor to retrieve Business Group Id */
918 CURSOR csr_bus_grp_id(p_organization_id NUMBER) IS
919 SELECT business_group_id
920 FROM hr_organization_units
921 WHERE organization_id = p_organization_id;
922
923 /* Cursor to retrieve Currency */
924 CURSOR csr_currency(p_bg_id NUMBER) IS
925 SELECT org_information10
926 FROM hr_organization_information
927 WHERE organization_id = p_bg_id
928 AND org_information_context = 'Business Group Information';
929
930 /* Cursor to retrieve Organization Structure Version Id */
931 CURSOR csr_org_structure(p_bg_id NUMBER) IS
932 SELECT org_information1
933 FROM hr_organization_information
934 WHERE organization_id = p_bg_id
935 AND org_information_context = 'SA_HR_BG_INFO';
936
937 /* Cursor to retrieve Grade of Employee */
938 CURSOR csr_grade(p_grade_id NUMBER) IS
939 SELECT pg.name
940 FROM per_grades pg
941 WHERE pg.grade_id = p_grade_id;
942
946 FROM per_all_positions pap
943 /* Cursor to retrieve Position of Employee */
944 CURSOR csr_position(p_position_id NUMBER) IS
945 SELECT pap.name
947 WHERE pap.position_id = p_position_id;
948
949 /* Cursor to retrieve Cost Center */
950 CURSOR csr_cost_center(p_assignment_id NUMBER) IS
951 SELECT concatenated_segments
952 FROM pay_cost_allocations_v
953 WHERE assignment_id=p_assignment_id
954 AND p_date_earned BETWEEN effective_start_date
955 AND effective_end_date;
956
957 /* Cursor to pick up Payroll Location */
958 CURSOR csr_pay_location(p_location_id NUMBER) IS
959 SELECT location_code location
960 FROM hr_locations_all
961 WHERE location_id = p_location_id;
962
963 /* Cursor to fetch lower limit of gosi base*/
964 CURSOR get_lower_base IS
965 SELECT global_value
966 FROM ff_globals_f
967 WHERE global_name = 'SA_GOSI_BASE_LOWER_LIMIT'
968 AND legislation_code = 'SA'
969 AND business_group_id IS NULL
970 AND p_pay_date_earned BETWEEN effective_start_date
971 AND effective_end_date;
972
973 /* Cursor to fetch upper limit of gosi base*/
974 CURSOR get_upper_base IS
975 SELECT global_value
976 FROM ff_globals_f
977 WHERE global_name = 'SA_GOSI_BASE_UPPER_LIMIT'
978 AND legislation_code = 'SA'
979 AND business_group_id IS NULL
980 AND p_pay_date_earned BETWEEN effective_start_date
981 AND effective_end_date;
982
983 rec_person_details csr_person_details%ROWTYPE;
984 rec_primary_address csr_primary_address%ROWTYPE;
985 rec_employer_address csr_employer_address%ROWTYPE;
986 rec_org_address csr_organization_address%ROWTYPE;
987 l_nationality per_all_people_f.nationality%TYPE;
988 l_position per_all_positions.name%TYPE;
989 l_grade per_grades.name%TYPE;
990 l_currency hr_organization_information.org_information10%TYPE;
991 l_organization hr_organization_units.name%TYPE;
992 l_pay_location hr_locations_all.address_line_1%TYPE;
993 l_city VARCHAR2(80);
994 l_country VARCHAR2(80);
995 l_emp_city VARCHAR2(80);
996 l_emp_country VARCHAR2(80);
997 l_org_city VARCHAR2(80);
998 l_org_country VARCHAR2(80);
999 l_action_info_id NUMBER;
1000 l_ovn NUMBER;
1001 l_person_id NUMBER;
1002 l_bg_id NUMBER;
1003 l_employer_name hr_organization_units.name%TYPE;
1004 l_org_struct_ver_id hr_organization_information.org_information1%TYPE;
1005 l_top_org_id per_org_structure_elements.organization_id_parent%TYPE;
1006 l_cost_center pay_cost_allocations_v.concatenated_segments%TYPE;
1007 l_defined_balance_id NUMBER;
1008 l_balance_value NUMBER;
1009 l_formatted_value VARCHAR2(50) := NULL;
1010 l_org_exists NUMBER :=0;
1011 l_lower_base NUMBER :=0;
1012 l_upper_base NUMBER :=0;
1013
1014
1015 BEGIN
1016
1017 IF g_debug THEN
1018 hr_utility.set_location(' Entering Procedure ARCHIVE_EMPLOYEE_DETAILS',160);
1019 END IF;
1020 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS');*/
1021
1022 /* PERSON AND ADDRESS DETAILS */
1023 OPEN csr_person_details(p_assignment_id);
1024 FETCH csr_person_details INTO rec_person_details;
1025 CLOSE csr_person_details;
1026
1027 OPEN csr_primary_address(rec_person_details.person_id);
1028 FETCH csr_primary_address INTO rec_primary_address;
1029 CLOSE csr_primary_address;
1030
1031 OPEN csr_employer_address(p_assignment_id);
1032 FETCH csr_employer_address INTO rec_employer_address;
1033 CLOSE csr_employer_address;
1034
1035 OPEN csr_organization_address(rec_person_details.org_id);
1036 FETCH csr_organization_address INTO rec_org_address;
1037 CLOSE csr_organization_address;
1038
1039 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 2');*/
1040
1041 /* GRADE AND POSITION */
1042 /* Changed IF condition construct to fix Bug 3583862 */
1043 IF(rec_person_details.pos_id IS NOT NULL) THEN
1044 OPEN csr_position(rec_person_details.pos_id);
1045 FETCH csr_position INTO l_position;
1046 CLOSE csr_position;
1047 END IF;
1048
1049 IF(rec_person_details.grade_id IS NOT NULL) THEN
1050 OPEN csr_grade(rec_person_details.grade_id);
1051 FETCH csr_grade INTO l_grade;
1052 CLOSE csr_grade;
1053
1054 END IF;
1055
1056 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 3');*/
1057
1058 /* CURRENCY */
1059 OPEN csr_bus_grp_id(rec_person_details.org_id);
1060 FETCH csr_bus_grp_id INTO l_bg_id;
1061 CLOSE csr_bus_grp_id;
1062
1063 OPEN csr_currency(l_bg_id);
1064 FETCH csr_currency INTO l_currency;
1065 CLOSE csr_currency;
1066
1067 g_format_mask := FND_CURRENCY.GET_FORMAT_MASK(l_currency,40);
1068
1069 /* COST CENTER */
1070 OPEN csr_cost_center(p_assignment_id);
1071 FETCH csr_cost_center INTO l_cost_center;
1072 CLOSE csr_cost_center;
1073
1074 /*NATIONALITY*/
1075 l_nationality := hr_general.decode_lookup('NATIONALITY',rec_person_details.nationality);
1079 l_org_struct_ver_id := NULL;
1076
1077 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 4');*/
1078
1080 OPEN csr_org_structure(l_bg_id);
1081 FETCH csr_org_structure INTO l_org_struct_ver_id;
1082 CLOSE csr_org_structure;
1083
1084 l_top_org_id := NULL;
1085 OPEN csr_top_org(l_org_struct_ver_id);
1086 FETCH csr_top_org INTO l_top_org_id;
1087 CLOSE csr_top_org;
1088
1089 IF(rec_person_details.loc_id IS NOT NULL) THEN
1090 l_pay_location := NULL;
1091 OPEN csr_pay_location(rec_person_details.loc_id);
1092 FETCH csr_pay_location INTO l_pay_location;
1093 CLOSE csr_pay_location;
1094
1095 ELSE l_pay_location := NULL;
1096 END IF;
1097
1098
1099 SELECT name INTO l_organization
1100 FROM hr_organization_units
1101 WHERE organization_id = rec_person_details.org_id;
1102
1103 SELECT name INTO l_employer_name
1104 FROM hr_organization_units
1105 WHERE organization_id = rec_employer_address.org_id;
1106
1107 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 5');*/
1108 l_city := null;
1109 IF rec_primary_address.style = 'SA' THEN
1110 l_city:= hr_general.decode_lookup('SA_CITY',rec_primary_address.city);
1111 ELSE
1112 l_city:= rec_primary_address.city;
1113 END IF;
1114
1115 l_country:=PAY_SA_ARCHIVE.get_country_name(rec_primary_address.country);
1116 l_emp_city := null;
1117 IF rec_employer_address.style = 'SA' THEN
1118 l_emp_city:= hr_general.decode_lookup('SA_CITY',rec_employer_address.city);
1119 ELSE
1120 l_emp_city:= rec_employer_address.city;
1121 END IF;
1122
1123 l_emp_country:=PAY_SA_ARCHIVE.get_country_name(rec_employer_address.country);
1124 l_org_city := null;
1125 IF rec_org_address.style = 'SA' THEN
1126 --l_org_city:= hr_general.decode_lookup('SA_CITY',rec_employer_address.city);
1127 l_org_city:= hr_general.decode_lookup('SA_CITY',rec_org_address.city);
1128 ELSE
1129 --l_org_city:= rec_employer_address.city;
1130 l_org_city:= rec_org_address.city;
1131 END IF;
1132
1133 l_org_country:=PAY_SA_ARCHIVE.get_country_name(rec_org_address.country);
1134
1135
1136
1137 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 6');*/
1138
1139 /* Get Value for GOSI Reference Salary*/
1140
1141 OPEN get_lower_base;
1142 FETCH get_lower_base INTO l_lower_base;
1143 CLOSE get_lower_base;
1144
1145 OPEN get_upper_base;
1146 FETCH get_upper_base INTO l_upper_base;
1147 CLOSE get_upper_base;
1148
1149 l_defined_balance_id := PAY_SA_ARCHIVE.GET_DEFINED_BALANCE_ID('GOSI_REFERENCE_EARNINGS_ASG_YTD');
1150 l_balance_value := pay_balance_pkg.get_value(l_defined_balance_id, p_assignment_action_id);
1151
1152 --Check for lower and upper limits of the reference salary
1153
1154
1155 IF(l_balance_value > to_number(l_upper_base)) THEN
1156 l_balance_value := to_number(l_upper_base);
1157 ELSIF(l_balance_value < to_number(l_lower_base)) THEN
1158 l_balance_value := to_number(l_lower_base);
1159 END IF;
1160
1161 l_formatted_value := to_char(l_balance_value, g_format_mask);
1162
1163 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS gosi reference');*/
1164
1165 /* INSERT PERSON DETAILS */
1166 pay_action_information_api.create_action_information (
1167 p_action_information_id => l_action_info_id
1168 ,p_action_context_id => p_archive_assact_id
1169 ,p_action_context_type => 'AAP'
1170 ,p_object_version_number => l_ovn
1171 ,p_effective_date => p_effective_date
1172 ,p_source_id => NULL
1173 ,p_source_text => NULL
1174 ,p_action_information_category => 'EMPLOYEE DETAILS'
1175 ,p_action_information1 => rec_person_details.full_name
1176 ,p_action_information2 => rec_person_details.org_id
1177 ,p_action_information4 => rec_person_details.ni_number
1178 ,p_action_information7 => l_grade
1179 ,p_action_information10 => rec_person_details.emp_num
1180 ,p_action_information12 => fnd_date.date_to_canonical(rec_person_details.start_date)
1181 ,p_action_information15 => l_organization
1182 ,p_action_information16 => p_time_period_id
1183 ,p_action_information19 => l_position
1184 ,p_action_information30 => l_pay_location
1185 ,p_assignment_id => p_assignment_id);
1186
1187
1188 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 7');*/
1189 /* ADDITIONAL EMPLOYEE DETAILS */
1190 pay_action_information_api.create_action_information (
1191 p_action_information_id => l_action_info_id
1192 ,p_action_context_id => p_archive_assact_id
1193 ,p_action_context_type => 'AAP'
1194 ,p_object_version_number => l_ovn
1195 ,p_effective_date => p_effective_date
1196 ,p_source_id => NULL
1197 ,p_source_text => NULL
1198 ,p_action_information_category => 'ADDL EMPLOYEE DETAILS'
1199 ,p_action_information4 => l_nationality
1200 ,p_action_information5 => l_cost_center
1201 ,p_action_information6 => l_top_org_id
1202 ,p_action_information7 => l_currency
1203 ,p_action_information8 => rec_employer_address.AL1
1204 ,p_action_information9 => rec_person_details.title -- for payroll register
1205 ,p_action_information10 => rec_person_details.first_name -- for payroll register
1206 ,p_action_information11 => rec_person_details.last_name -- for payroll register
1207 ,p_action_information12 => rec_person_details.father_name -- for payroll register
1208 ,p_action_information13 => l_formatted_value -- GOSI Reference Salary
1209 ,p_action_information14 => l_employer_name
1210 ,p_assignment_id => p_assignment_id);
1211 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 8');*/
1212
1213 /* INSERT ADDRESS DETAILS */
1214 IF rec_primary_address.AL1 IS NOT NULL THEN /* CHECK IF EMPLOYEE HAS BEEN GIVEN A PRIMARY ADDRESS */
1215 pay_action_information_api.create_action_information (
1216 p_action_information_id => l_action_info_id
1217 ,p_action_context_id => p_archive_assact_id
1218 ,p_action_context_type => 'AAP'
1219 ,p_object_version_number => l_ovn
1220 ,p_effective_date => p_effective_date
1221 ,p_source_id => NULL
1222 ,p_source_text => NULL
1223 ,p_action_information_category => 'ADDRESS DETAILS'
1224 ,p_action_information1 => rec_primary_address.person_id
1225 ,p_action_information5 => rec_primary_address.AL1
1226 ,p_action_information6 => rec_primary_address.AL2
1227 ,p_action_information7 => NULL
1228 ,p_action_information8 => l_city
1229 ,p_action_information9 => rec_primary_address.R1
1230 ,p_action_information10 => rec_primary_address.R2
1231 ,p_action_information11 => rec_primary_address.R3
1232 ,p_action_information12 => rec_primary_address.postal_code
1233 ,p_action_information13 => l_country
1234 ,p_action_information14 => 'Employee Address'
1235 ,p_assignment_id => p_assignment_id);
1236 ELSE
1237 /* INSERT EMPLOYER ADDRESS AS EMPLOYEE'S PRIMARY ADDRESS */
1238 pay_action_information_api.create_action_information (
1239 p_action_information_id => l_action_info_id
1240 ,p_action_context_id => p_archive_assact_id
1241 ,p_action_context_type => 'AAP'
1242 ,p_object_version_number => l_ovn
1243 ,p_effective_date => p_effective_date
1244 ,p_source_id => NULL
1245 ,p_source_text => NULL
1246 ,p_action_information_category => 'ADDRESS DETAILS'
1247 ,p_action_information1 => rec_employer_address.person_id
1248 ,p_action_information5 => NULL --rec_employer_address.AL1
1249 ,p_action_information6 => NULL --rec_employer_address.AL2
1250 ,p_action_information7 => NULL --rec_employer_address.AL3
1251 ,p_action_information8 => NULL --l_emp_city
1252 ,p_action_information9 => NULL --rec_employer_address.R1
1253 ,p_action_information10 => NULL --rec_employer_address.R2
1254 ,p_action_information11 => NULL --rec_employer_address.R3
1255 ,p_action_information12 => NULL --rec_employer_address.postal_code
1256 ,p_action_information13 => NULL --l_emp_country
1257 ,p_action_information14 => 'Employee Address'
1258 ,p_assignment_id => p_assignment_id);
1259 END IF;
1260
1261 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 9');*/
1262 /* INSERT EMPLOYER'S ADDRESS (ORGANIZATION ADDRESS)*/
1263
1264
1265 BEGIN
1266 l_org_exists := 0;
1267 SELECT 1
1268 INTO l_org_exists
1269 FROM pay_action_information
1270 WHERE action_context_id = p_payroll_action_id
1271 AND action_information1 = rec_person_details.org_id
1272 AND effective_date = p_effective_date
1273 AND action_information_category = 'ADDRESS DETAILS';
1274
1275 EXCEPTION
1276 WHEN NO_DATA_FOUND THEN
1277 pay_action_information_api.create_action_information (
1278 p_action_information_id => l_action_info_id
1279 ,p_action_context_id => p_payroll_action_id
1280 ,p_action_context_type => 'PA'
1281 ,p_object_version_number => l_ovn
1282 ,p_effective_date => p_effective_date
1283 ,p_source_id => NULL
1284 ,p_source_text => NULL
1285 ,p_action_information_category => 'ADDRESS DETAILS'
1286 ,p_action_information1 => rec_person_details.org_id
1287 ,p_action_information5 => rec_org_address.AL1
1288 ,p_action_information6 => rec_org_address.AL2
1289 ,p_action_information7 => rec_org_address.AL3
1290 ,p_action_information8 => l_org_city
1291 ,p_action_information9 => rec_org_address.R1
1292 ,p_action_information10 => rec_org_address.R2
1293 ,p_action_information11 => rec_org_address.R3
1294 ,p_action_information12 => rec_org_address.postal_code
1295 ,p_action_information13 => l_org_country
1296 ,p_action_information14 => 'Employer Address');
1297
1298 WHEN OTHERS THEN
1299 NULL;
1300 END;
1301
1302
1303
1304
1305 /*fnd_file.put_line(fnd_file.log,'Entering Procedure ARCHIVE_EMPLOYEE_DETAILS 10');*/
1306 --
1307
1308 IF g_debug THEN
1309 hr_utility.set_location(' Leaving Procedure ARCHIVE_EMPLOYEE_DETAILS',170);
1310 END IF;
1311 --
1312 EXCEPTION WHEN OTHERS THEN
1313 g_err_num := SQLCODE;
1314
1315 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_EMPLOYEE_DETAILS');*/
1316 IF g_debug THEN
1317 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_EMPLOYEE_DETAILS',180);
1318 END IF;
1319
1320 END ARCHIVE_EMPLOYEE_DETAILS;
1321
1322 /* EARNINGS REGION, DEDUCTIONS REGION */
1323 PROCEDURE ARCHIVE_ELEMENT_INFO(p_payroll_action_id IN NUMBER
1324 ,p_effective_date IN DATE
1325 ,p_date_earned IN DATE
1326 ,p_pre_payact_id IN NUMBER)
1327 IS
1328
1329 /* Cursor to retrieve Earnings Element Information */
1330 CURSOR csr_ear_element_info IS
1331 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
1332 ,et.element_type_id element_type_id
1333 ,iv.input_value_id input_value_id
1334 ,iv.uom uom
1335 FROM pay_element_types_f et
1336 , pay_element_types_f_tl pettl
1337 , pay_input_values_f iv
1338 , pay_element_classifications classification
1339 WHERE et.element_type_id = iv.element_type_id
1340 AND et.element_type_id = pettl.element_type_id
1341 AND pettl.language = USERENV('LANG')
1342 AND iv.name = 'Pay Value'
1343 AND classification.classification_id = et.classification_id
1344 AND classification.classification_name IN ('Absence'
1345 ,'Direct Payment'
1346 ,'Earnings'
1347 ,'Supplemental Earnings')
1348 AND p_date_earned BETWEEN et.effective_start_date
1349 AND et.effective_end_date
1350 AND p_date_earned BETWEEN iv.effective_start_date
1351 AND iv.effective_end_date
1352 AND ((et.business_group_id IS NULL AND et.legislation_code = 'SA')
1353 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
1354
1355
1356 /* Cursor to retrieve Deduction Element Information */
1357 CURSOR csr_ded_element_info IS
1358 SELECT nvl(pettl.reporting_name,pettl.element_name) rep_name
1359 ,et.element_type_id element_type_id
1360 ,iv.input_value_id input_value_id
1361 ,iv.uom uom
1362 FROM pay_element_types_f et
1363 , pay_element_types_f_tl pettl
1364 , pay_input_values_f iv
1365 , pay_element_classifications classification
1366 WHERE et.element_type_id = iv.element_type_id
1367 AND et.element_type_id = pettl.element_type_id
1368 AND pettl.language = USERENV('LANG')
1369 AND iv.name = 'Pay Value'
1370 AND classification.classification_id = et.classification_id
1371 AND classification.classification_name IN ('Involuntary Deductions'
1372 ,'Pre-Statutory Deductions'
1373 ,'Statutory Deductions'
1374 ,'Voluntary Deductions')
1375 AND nvl(et.reporting_name,et.element_name) <> 'GOSI'
1376 AND p_date_earned BETWEEN et.effective_start_date
1377 AND et.effective_end_date
1378 AND p_date_earned BETWEEN iv.effective_start_date
1379 AND iv.effective_end_date
1380 AND ((et.business_group_id IS NULL AND et.legislation_code = 'SA')
1381 OR (et.business_group_id = g_business_group_id AND et.legislation_code IS NULL));
1382
1383 l_action_info_id NUMBER;
1384 l_ovn NUMBER;
1385 l_flag NUMBER := 0;
1386
1387 BEGIN
1388
1389 IF g_debug THEN
1390 hr_utility.set_location(' Entering Procedure ARCHIVE_ELEMENT_INFO',210);
1391 END IF;
1392
1393 /* EARNINGS ELEMENT */
1394
1395 FOR rec_earnings IN csr_ear_element_info LOOP
1396
1397 BEGIN
1398
1399 SELECT 1 INTO l_flag
1400 FROM pay_action_information
1401 WHERE action_context_id = p_payroll_action_id
1402 AND action_information_category = 'EMEA ELEMENT DEFINITION'
1403 AND action_information2 = rec_earnings.element_type_id
1404 AND action_information3 = rec_earnings.input_value_id
1405 AND action_information5 = 'E';
1406
1407 EXCEPTION WHEN NO_DATA_FOUND THEN
1408
1409
1410 pay_action_information_api.create_action_information (
1411 p_action_information_id => l_action_info_id
1412 ,p_action_context_id => p_payroll_action_id
1413 ,p_action_context_type => 'PA'
1414 ,p_object_version_number => l_ovn
1415 ,p_effective_date => p_effective_date
1416 ,p_source_id => NULL
1417 ,p_source_text => NULL
1418 ,p_action_information_category => 'EMEA ELEMENT DEFINITION'
1419 ,p_action_information1 => p_pre_payact_id
1420 ,p_action_information2 => rec_earnings.element_type_id
1421 ,p_action_information3 => rec_earnings.input_value_id
1422 ,p_action_information4 => rec_earnings.rep_name
1423 ,p_action_information5 => 'E'
1424 ,p_action_information6 => rec_earnings.uom
1425 ,p_action_information7 => 'E'); --Earnings Element Context
1426
1427 WHEN OTHERS THEN
1428 NULL;
1429
1430 END;
1431 END LOOP;
1432
1433 /* DEDUCTION ELEMENT */
1434
1435 FOR rec_deduction IN csr_ded_element_info LOOP
1436
1437 BEGIN
1438
1439 SELECT 1 INTO l_flag
1440 FROM pay_action_information
1441 WHERE action_context_id = p_payroll_action_id
1442 AND action_information_category = 'EMEA ELEMENT DEFINITION'
1443 AND action_information2 = rec_deduction.element_type_id
1444 AND action_information3 = rec_deduction.input_value_id
1445 AND action_information5 = 'D';
1446
1447 EXCEPTION WHEN NO_DATA_FOUND THEN
1448 pay_action_information_api.create_action_information (
1449 p_action_information_id => l_action_info_id
1450 ,p_action_context_id => p_payroll_action_id
1451 ,p_action_context_type => 'PA'
1452 ,p_object_version_number => l_ovn
1453 ,p_effective_date => p_effective_date
1454 ,p_source_id => NULL
1455 ,p_source_text => NULL
1456 ,p_action_information_category => 'EMEA ELEMENT DEFINITION'
1457 ,p_action_information1 => p_pre_payact_id
1458 ,p_action_information2 => rec_deduction.element_type_id
1459 ,p_action_information3 => rec_deduction.input_value_id
1460 ,p_action_information4 => rec_deduction.rep_name
1461 ,p_action_information5 => 'D'
1462 ,p_action_information6 => rec_deduction.uom
1463 ,p_action_information7 => 'D'); --Deduction Element Context
1464 /*WHEN OTHERS THEN
1465 NULL;*/
1466 END;
1467 END LOOP;
1468
1469
1470 IF g_debug THEN
1471 hr_utility.set_location(' Leaving Procedure ARCHIVE_ELEMENT_INFO',220);
1472 END IF;
1473
1474 EXCEPTION WHEN OTHERS THEN
1475 g_err_num := SQLCODE;
1476
1477 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_ELEMENT_INFO');*/
1478 IF g_debug THEN
1479 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'ARCHIVE_ELEMENT_INFO',230);
1480 END IF;
1481
1482
1483 END ARCHIVE_ELEMENT_INFO;
1484
1485 /* GET DEFINED BALANCE ID */
1486 FUNCTION GET_DEFINED_BALANCE_ID(p_user_name IN VARCHAR2) RETURN NUMBER
1487 IS
1488 /* Cursor to retrieve Defined Balance Id */
1489 CURSOR csr_def_bal_id(p_user_name VARCHAR2) IS
1490 SELECT u.creator_id
1491 FROM ff_user_entities u,
1492 ff_database_items d
1493 WHERE d.user_name = p_user_name
1494 AND u.user_entity_id = d.user_entity_id
1495 AND (u.legislation_code = 'SA' )
1496 AND (u.business_group_id IS NULL )
1497 AND u.creator_type = 'B';
1498
1499 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
1500
1501 BEGIN
1502 IF g_debug THEN
1503 hr_utility.set_location(' Entering Function GET_DEFINED_BALANCE_ID',240);
1504 END IF;
1505
1506 OPEN csr_def_bal_id(p_user_name);
1507 FETCH csr_def_bal_id INTO l_defined_balance_id;
1508 CLOSE csr_def_bal_id;
1509 RETURN l_defined_balance_id;
1510
1511 IF g_debug THEN
1512 hr_utility.set_location(' Leaving Function GET_DEFINED_BALANCE_ID',250);
1513 END IF;
1514
1515 END GET_DEFINED_BALANCE_ID;
1516
1517 /* PAYMENT INFORMATION REGION */
1518 PROCEDURE ARCHIVE_PAYMENT_INFO(p_archive_assact_id IN NUMBER,
1519 p_prepay_assact_id IN NUMBER,
1520 p_assignment_id IN NUMBER,
1521 p_date_earned IN DATE,
1522 p_effective_date IN DATE)
1523 IS
1524
1525 /* Cursor to fetch ppm and opm ids to check which payment method to archive */
1526 CURSOR csr_chk(p_prepay_assact_id NUMBER) IS
1527 SELECT personal_payment_method_id ppm_id,
1528 org_payment_method_id opm_id
1529 FROM pay_pre_payments
1530 WHERE assignment_action_id = p_prepay_assact_id;
1531
1532 /* Cursor to check if bank details are attached with ppm */
1533 CURSOR csr_chk_bank(p_ppm_id NUMBER) IS
1534 SELECT ppm.external_account_id
1535 FROM pay_personal_payment_methods_f ppm
1536 WHERE ppm.personal_payment_method_id = p_ppm_id
1537 AND p_date_earned BETWEEN ppm.effective_start_date
1538 AND ppm.effective_end_date;
1539
1540
1541 /* Cursor to retrieve Organization Payment Method Information */
1542 CURSOR csr_get_org_pay(p_prepay_assact_id NUMBER, opm_id NUMBER) IS
1543 SELECT pop.org_payment_method_id opm_id,
1544 pop.org_payment_method_name,
1545 ppttl.payment_type_name pay_type,
1546 ppp.value value
1547 FROM pay_org_payment_methods_f pop,
1548 pay_assignment_actions paa,
1549 pay_payment_types ppt,
1550 pay_payment_types_tl ppttl,
1551 pay_pre_payments ppp
1552 WHERE paa.assignment_action_id = p_prepay_assact_id
1553 AND ppt.payment_type_id = pop.payment_type_id
1554 AND ppt.payment_type_id = ppttl.payment_type_id
1555 AND ppttl.language = userenv('LANG')
1556 AND ppp.org_payment_method_id = pop.org_payment_method_id
1557 AND pop.org_payment_method_id = opm_id
1558 AND ppp.assignment_action_id = paa.assignment_action_id
1559 AND p_date_earned BETWEEN pop.effective_start_date
1560 AND pop.effective_end_date;
1561
1562 /* Cursor to retrieve Personal Payment Method Info*/
1563 CURSOR csr_get_pers_pay(p_prepay_assact_id NUMBER, ppm_id NUMBER) IS
1564 SELECT pea.segment1 name_id,
1565 pea.segment2 branch,
1566 pea.segment4 acct_num,
1567 ppm.org_payment_method_id opm_id,
1568 pop.external_account_id,
1569 pop.org_payment_method_name,
1570 ppm.personal_payment_method_id ppm_id,
1571 ppttl.payment_type_name pay_type,
1572 ppp.value value
1573 FROM pay_external_accounts pea,
1574 pay_org_payment_methods_f pop,
1575 pay_personal_payment_methods_f ppm,
1576 pay_assignment_actions paa,
1577 pay_payment_types ppt,
1578 pay_payment_types_tl ppttl,
1579 pay_pre_payments ppp
1580 WHERE pea.id_flex_num=20
1581 AND pea.external_account_id = NVL(ppm.external_account_id,pop.external_account_id)
1582 AND paa.assignment_action_id = p_prepay_assact_id
1583 AND paa.assignment_id = ppm.assignment_id
1584 AND ppm.org_payment_method_id = pop.org_payment_method_id
1585 AND ppm.personal_payment_method_id = ppm_id
1586 AND ppt.payment_type_id = pop.payment_type_id
1587 AND ppt.payment_type_id = ppttl.payment_type_id
1588 AND ppttl.language = userenv('LANG')
1589 AND ppp.assignment_action_id = paa.assignment_action_id
1590 AND ppp.personal_payment_method_id = ppm.personal_payment_method_id
1591 AND p_date_earned BETWEEN pop.effective_start_date
1592 AND pop.effective_end_date
1593 AND p_date_earned BETWEEN ppm.effective_start_date
1594 AND ppm.effective_end_date;
1595
1596
1597 l_bank_name VARCHAR2(50);
1598 l_action_info_id NUMBER;
1599 l_ovn NUMBER;
1600 l_org NUMBER;
1601 l_pers VARCHAR2(40) := NULL;
1602 l_ext_acct NUMBER;
1603 rec_chk csr_chk%ROWTYPE;
1604 l_pay_value VARCHAR2(50) := NULL;
1605
1606 BEGIN
1607
1608 IF g_debug THEN
1609 hr_utility.set_location(' Entering Procedure ARCHIVE_PAYMENT_INFO',260);
1610 END IF;
1611 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 1');*/
1612
1613
1614 OPEN csr_chk(p_prepay_assact_id);
1615 LOOP
1616 FETCH csr_chk INTO rec_chk;
1617 EXIT WHEN csr_chk%NOTFOUND;
1618
1619
1620 IF rec_chk.ppm_id IS NOT NULL THEN
1621
1622 FOR rec_pers_pay IN csr_get_pers_pay(p_prepay_assact_id,rec_chk.ppm_id) LOOP
1623
1624
1625 OPEN csr_chk_bank(rec_chk.ppm_id);
1626 FETCH csr_chk_bank INTO l_ext_acct;
1627 CLOSE csr_chk_bank;
1628
1629 l_pay_value := to_char (rec_pers_pay.value,g_format_mask);
1630
1631 IF (l_ext_acct IS NOT NULL) THEN
1632 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 2');*/
1633
1634 l_bank_name := hr_general.decode_lookup('SA_BANKS',rec_pers_pay.name_id);
1635
1636 pay_action_information_api.create_action_information (
1637 p_action_information_id => l_action_info_id
1638 ,p_action_context_id => p_archive_assact_id
1639 ,p_action_context_type => 'AAP'
1640 ,p_object_version_number => l_ovn
1641 ,p_effective_date => p_effective_date
1642 ,p_source_id => NULL
1643 ,p_source_text => NULL
1644 ,p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION'
1645 ,p_action_information1 => NULL
1646 ,p_action_information2 => rec_pers_pay.ppm_id
1647 ,p_action_information5 => l_bank_name
1648 ,p_action_information6 => rec_pers_pay.branch
1649 ,p_action_information7 => NULL
1650 ,p_action_information8 => rec_pers_pay.acct_num
1651 ,p_action_information9 => NULL
1652 ,p_action_information10 => NULL
1653 ,p_action_information11 => NULL
1654 ,p_action_information12 => NULL
1655 ,p_action_information13 => NULL
1656 ,p_action_information14 => NULL
1657 ,p_action_information15 => NULL
1658 ,p_action_information16 => fnd_number.number_to_canonical(rec_pers_pay.value) --l_pay_value
1659 ,p_action_information17 => NULL
1660 ,p_action_information18 => rec_pers_pay.pay_type
1661 ,p_assignment_id => p_assignment_id);
1662
1663 ELSE
1664
1665 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 3');*/
1666 pay_action_information_api.create_action_information (
1667 p_action_information_id => l_action_info_id
1668 ,p_action_context_id => p_archive_assact_id
1669 ,p_action_context_type => 'AAP'
1670 ,p_object_version_number => l_ovn
1671 ,p_effective_date => p_effective_date
1672 ,p_source_id => NULL
1673 ,p_source_text => NULL
1674 ,p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION'
1675 ,p_action_information1 => rec_pers_pay.opm_id
1676 ,p_action_information2 => rec_pers_pay.ppm_id
1677 ,p_action_information5 => NULL
1678 ,p_action_information6 => NULL
1679 ,p_action_information7 => NULL
1680 ,p_action_information8 => NULL
1681 ,p_action_information9 => NULL
1682 ,p_action_information10 => NULL
1683 ,p_action_information11 => NULL
1684 ,p_action_information12 => NULL
1685 ,p_action_information13 => NULL
1686 ,p_action_information14 => NULL
1687 ,p_action_information15 => NULL
1688 ,p_action_information16 => fnd_number.number_to_canonical(rec_pers_pay.value) --l_pay_value
1689 ,p_action_information17 => NULL
1690 ,p_action_information18 => rec_pers_pay.pay_type
1691 ,p_assignment_id => p_assignment_id);
1692
1693 END IF;
1694
1695 END LOOP;
1696 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 4');*/
1697
1698
1699 END IF;
1700
1701 IF (rec_chk.opm_id IS NOT NULL AND rec_chk.ppm_id IS NULL) THEN
1702
1703 /*fnd_file.put_line(fnd_file.log,'In ARCHIVE_PAYMENT_INFO 5');*/
1704
1705 FOR rec_org_pay IN csr_get_org_pay(p_prepay_assact_id,rec_chk.opm_id) LOOP
1706
1707 l_pay_value := to_char (rec_org_pay.value,g_format_mask);
1708
1709 pay_action_information_api.create_action_information (
1710 p_action_information_id => l_action_info_id
1711 ,p_action_context_id => p_archive_assact_id
1712 ,p_action_context_type => 'AAP'
1713 ,p_object_version_number => l_ovn
1714 ,p_effective_date => p_effective_date
1715 ,p_source_id => NULL
1716 ,p_source_text => NULL
1717 ,p_action_information_category => 'EMPLOYEE NET PAY DISTRIBUTION'
1718 ,p_action_information1 => rec_org_pay.opm_id
1719 ,p_action_information2 => NULL
1720 ,p_action_information5 => NULL
1721 ,p_action_information6 => NULL
1722 ,p_action_information7 => NULL
1723 ,p_action_information8 => NULL
1724 ,p_action_information9 => NULL
1725 ,p_action_information10 => NULL
1726 ,p_action_information11 => NULL
1727 ,p_action_information12 => NULL
1728 ,p_action_information13 => NULL
1729 ,p_action_information14 => NULL
1730 ,p_action_information15 => NULL
1731 ,p_action_information16 => fnd_number.number_to_canonical(rec_org_pay.value) --l_pay_value
1732 ,p_action_information17 => NULL
1733 ,p_action_information18 => rec_org_pay.pay_type
1734 ,p_assignment_id => p_assignment_id);
1735
1736 END LOOP;
1737 END IF;
1738
1739 END LOOP;
1740 CLOSE csr_chk;
1741
1742 IF g_debug THEN
1743 hr_utility.set_location(' Leaving Procedure ARCHIVE_PAYMENT_INFO',270);
1744 END IF;
1745
1746 EXCEPTION WHEN OTHERS THEN
1747 g_err_num := SQLCODE;
1748 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_PAYMENT_INFO');*/
1749 IF g_debug THEN
1750 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_PAYMENT_INFO',280);
1751 END IF;
1752
1753 END ARCHIVE_PAYMENT_INFO;
1754
1755 /* ACCRUALS REGION */
1756 PROCEDURE ARCHIVE_ACCRUAL_PLAN ( p_assignment_id IN NUMBER
1757 ,p_date_earned IN DATE
1758 ,p_effective_date IN DATE
1759 ,p_archive_assact_id IN NUMBER
1760 ,p_run_assignment_action_id IN NUMBER
1761 ,p_period_end_date IN DATE
1762 ,p_period_start_date IN DATE
1763 )
1764 IS
1765 --
1766 -- Cursor to get the Leave Balance Details .
1767 CURSOR csr_leave_balance
1768 IS
1769 --
1770 SELECT pap.accrual_plan_name
1771 ,hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category) accrual_catgry
1772 ,pap.accrual_units_of_measure
1773 ,ppa.payroll_id
1774 ,pap.business_group_id
1775 ,pap.accrual_plan_id
1776 FROM pay_accrual_plans pap
1777 ,pay_element_types_f pet
1778 ,pay_element_links_f pel
1779 ,pay_element_entries_f pee
1780 ,pay_assignment_actions paa
1781 ,pay_payroll_actions ppa
1782 WHERE pet.element_type_id = pap.accrual_plan_element_type_id
1783 AND pel.element_type_id = pet.element_type_id
1784 AND pee.element_link_id = pel.element_link_id
1785 AND paa.assignment_id = pee.assignment_id
1786 AND ppa.payroll_action_id = paa.payroll_action_id
1787 AND ppa.action_type IN ('R','Q')
1788 AND ppa.action_status = 'C'
1789 AND ppa.date_earned BETWEEN pet.effective_start_date
1790 AND pet.effective_end_date
1791 AND ppa.date_earned BETWEEN pel.effective_start_date
1792 AND pel.effective_end_date
1793 AND ppa.date_earned BETWEEN pee.effective_start_date
1794 AND pee.effective_end_date
1795 AND paa.assignment_id = p_assignment_id
1796 AND paa.assignment_action_id = p_run_assignment_action_id;
1797 --
1798
1799 l_action_info_id NUMBER;
1800 l_accrual_plan_id pay_accrual_plans.accrual_plan_id%type;
1801 l_accrual_plan_name pay_accrual_plans.accrual_plan_name%type;
1802 l_accrual_category pay_accrual_plans.accrual_category%type;
1803 l_accrual_uom pay_accrual_plans.accrual_units_of_measure%type;
1804 l_payroll_id pay_all_payrolls_f.payroll_id%type;
1805 l_business_group_id NUMBER;
1806 l_effective_date DATE;
1807 l_annual_leave_balance NUMBER;
1808 l_ovn NUMBER;
1809 l_leave_taken NUMBER;
1810 l_start_date DATE;
1811 l_end_date DATE;
1812 l_accrual_end_date DATE;
1813 l_accrual NUMBER;
1814 l_total_leave_taken NUMBER;
1815 l_procedure VARCHAR2(100) := g_package || '.archive_accrual_details';
1816 --
1817 BEGIN
1818 --
1819
1820 IF g_debug THEN
1821 hr_utility.set_location(' Entering Procedure ARCHIVE_ACCRUAL_PLAN',290);
1822 END IF;
1823
1824 -- Bug 6915966
1825 -- Fetch and archive the accruals cursor in loop
1826 FOR c_leave_bal In csr_leave_balance
1827 Loop
1828 l_accrual_plan_name := c_leave_bal.accrual_plan_name ;
1829 l_accrual_category := c_leave_bal.accrual_catgry ;
1830 l_accrual_uom := c_leave_bal.accrual_units_of_measure ;
1831 l_payroll_id := c_leave_bal.payroll_id ;
1832 l_business_group_id := c_leave_bal.business_group_id ;
1833 l_accrual_plan_id := c_leave_bal.accrual_plan_id ;
1834
1835 IF csr_leave_balance%FOUND THEN
1836 --
1837 -- Call to get annual leave balance
1838 per_accrual_calc_functions.get_net_accrual
1839 (
1840 p_assignment_id => p_assignment_id -- number in
1841 ,p_plan_id => l_accrual_plan_id -- number in
1842 ,p_payroll_id => l_payroll_id -- number in
1843 ,p_business_group_id => l_business_group_id -- number in
1844 ,p_calculation_date => p_date_earned -- date in
1845 ,p_start_date => l_start_date -- date out
1846 ,p_end_date => l_end_date -- date out
1847 ,p_accrual_end_date => l_accrual_end_date -- date out
1848 ,p_accrual => l_accrual -- number out
1849 ,p_net_entitlement => l_annual_leave_balance -- number out
1850 );
1851
1852
1853 IF l_annual_leave_balance IS NULL THEN
1854 --
1855 l_annual_leave_balance := 0;
1856 --
1857 END IF;
1858
1859 l_leave_taken := per_accrual_calc_functions.get_absence
1860 (
1861 p_assignment_id
1862 ,l_accrual_plan_id
1863 ,p_period_end_date
1864 ,p_period_start_date
1865 );
1866 l_ovn :=1;
1867
1868 IF l_accrual_plan_name IS NOT NULL THEN
1869 --
1870 pay_action_information_api.create_action_information (
1871 p_action_information_id => l_action_info_id
1872 ,p_action_context_id => p_archive_assact_id
1873 ,p_action_context_type => 'AAP'
1874 ,p_object_version_number => l_ovn
1875 ,p_effective_date => p_effective_date
1876 ,p_source_id => NULL
1877 ,p_source_text => NULL
1878 ,p_action_information_category => 'EMPLOYEE ACCRUALS'
1879 ,p_action_information4 => l_accrual_plan_name
1880 ,p_action_information5 => fnd_number.number_to_canonical(l_leave_taken)
1881 ,p_action_information6 => fnd_number.number_to_canonical(l_annual_leave_balance)
1882 ,p_assignment_id => p_assignment_id);
1883
1884 --
1885 END IF;
1886 --
1887 END IF;
1888 --
1889 END LOOP ;
1890
1891 IF g_debug THEN
1892 hr_utility.set_location(' Leaving Procedure ARCHIVE_ACCRUAL_PLAN',300);
1893 END IF;
1894 --
1895 EXCEPTION
1896 WHEN OTHERS THEN
1897 IF csr_leave_balance%ISOPEN THEN
1898 --
1899 CLOSE csr_leave_balance;
1900 --
1901 END IF;
1902 --
1903 g_err_num := SQLCODE;
1904
1905 --fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_ACCRUAL_PLAN');
1906 IF g_debug THEN
1907 hr_utility.set_location('ORA_ERR: ' || g_err_num || ' In ARCHIVE_ACCRUAL_PLAN',310);
1908 END IF;
1909 RAISE;
1910
1911 END ARCHIVE_ACCRUAL_PLAN;
1912
1913 /* ADDITIONAL ELEMENTS REGION */
1914 PROCEDURE ARCHIVE_ADD_ELEMENT(p_archive_assact_id IN NUMBER,
1915 p_assignment_action_id IN NUMBER,
1916 p_assignment_id IN NUMBER,
1917 p_payroll_action_id IN NUMBER,
1918 p_date_earned IN DATE,
1919 p_effective_date IN DATE,
1920 p_pre_payact_id IN NUMBER,
1921 p_archive_flag IN VARCHAR2) IS
1922
1923 /* Cursor to retrieve Additional Element Information */
1924 CURSOR csr_get_element(p_bus_grp_id NUMBER) IS
1925 SELECT hoi.org_information1 element_type_id
1926 ,hoi.org_information2 input_value_id
1927 ,hoi.org_information3 element_narrative
1928 ,pec.classification_name
1929 ,piv.uom
1930 FROM hr_organization_information hoi
1931 ,pay_element_classifications pec
1932 ,pay_element_types_f pet
1933 ,pay_input_values_f piv
1934 WHERE hoi.organization_id = p_bus_grp_id
1935 AND hoi.org_information_context = 'SA_PAYSLIP_ELEMENTS'
1936 AND hoi.org_information1 = pet.element_type_id
1937 AND pec.classification_id = pet.classification_id
1938 AND piv.input_value_id = hoi.org_information2
1939 AND p_date_earned BETWEEN piv.effective_start_date
1940 AND piv.effective_end_date;
1941
1942 /* Cursor to retrieve run result value of Additional Elements */
1943 CURSOR csr_result_value(p_iv_id NUMBER
1944 ,p_ele_type_id NUMBER
1945 ,p_assignment_action_id NUMBER) IS
1946 SELECT rrv.result_value
1947 FROM pay_run_result_values rrv
1948 ,pay_run_results rr
1949 ,pay_assignment_actions paa
1950 ,pay_payroll_actions ppa
1951 WHERE rrv.input_value_id = p_iv_id
1952 AND rr.element_type_id = p_ele_type_id
1953 AND rr.run_result_id = rrv.run_result_id
1954 AND rr.assignment_action_id = paa.assignment_action_id
1955 AND paa.assignment_action_id = p_assignment_action_id
1956 AND ppa.payroll_action_id = paa.payroll_action_id
1957 AND ppa.action_type IN ('Q','R')
1958 AND rrv.result_value IS NOT NULL;
1959
1960 rec_get_element csr_get_element%ROWTYPE;
1961 l_result_value pay_run_result_values.result_value%TYPE := 0;
1962 l_action_info_id NUMBER;
1963 l_ovn NUMBER;
1964 l_element_context VARCHAR2(10);
1965 l_index NUMBER := 0;
1966 l_formatted_value VARCHAR2(50) := NULL;
1967 l_flag NUMBER := 0;
1968
1969 BEGIN
1970
1971 IF g_debug THEN
1972 hr_utility.set_location(' Entering Procedure ARCHIVE_ADD_ELEMENT',320);
1973 END IF;
1974
1975
1976 IF p_archive_flag = 'N' THEN
1977
1978 ---------------------------------------------------
1979 --Check if global table has already been populated
1980 ---------------------------------------------------
1981 IF g_element_table.count = 0 THEN
1982
1983 OPEN csr_get_element(g_business_group_id);
1984
1985 LOOP
1986 FETCH csr_get_element INTO rec_get_element;
1987 EXIT WHEN csr_get_element%NOTFOUND;
1988
1989 l_element_context := 'F'; --Additional Element Context
1990
1991
1992 SETUP_ELEMENT_DEFINITIONS(p_classification_name => rec_get_element.classification_name
1993 ,p_element_name => rec_get_element.element_narrative
1994 ,p_element_type_id => rec_get_element.element_type_id
1995 ,p_input_value_id => rec_get_element.input_value_id
1996 ,p_element_type => l_element_context
1997 ,p_uom => rec_get_element.uom
1998 ,p_archive_flag => p_archive_flag);
1999
2000 END LOOP;
2001 CLOSE csr_get_element;
2002 END IF;
2003
2004
2005 ELSIF p_archive_flag = 'Y' AND g_element_table.count > 0 THEN
2006
2007 FOR l_index IN g_element_table.first.. g_element_table.last LOOP
2008 l_result_value := NULL;
2009
2010 BEGIN
2011
2012 OPEN csr_result_value(g_element_table(l_index).input_value_id
2013 ,g_element_table(l_index).element_type_id
2014 ,p_assignment_action_id);
2015 LOOP
2016 FETCH csr_result_value INTO l_result_value;
2017 EXIT WHEN csr_result_value%NOTFOUND;
2018 IF l_result_value is not null THEN
2019
2020 pay_action_information_api.create_action_information (
2021 p_action_information_id => l_action_info_id
2022 ,p_action_context_id => p_archive_assact_id
2023 ,p_action_context_type => 'AAP'
2024 ,p_object_version_number => l_ovn
2025 ,p_effective_date => p_effective_date
2026 ,p_source_id => NULL
2027 ,p_source_text => NULL
2028 ,p_action_information_category => 'EMEA ELEMENT INFO'
2029 ,p_action_information1 => g_element_table(l_index).element_type_id
2030 ,p_action_information2 => g_element_table(l_index).input_value_id
2031 ,p_action_information3 => g_element_table(l_index).element_type
2032 ,p_action_information4 => l_result_value --l_formatted_value
2033 ,p_action_information9 => 'Additional Element'
2034 ,p_assignment_id => p_assignment_id);
2035 END IF;
2036 END LOOP;
2037 CLOSE csr_result_value;
2038
2039 /*IF l_result_value is not null THEN
2040
2041 pay_action_information_api.create_action_information (
2042 p_action_information_id => l_action_info_id
2043 ,p_action_context_id => p_archive_assact_id
2044 ,p_action_context_type => 'AAP'
2045 ,p_object_version_number => l_ovn
2046 ,p_effective_date => p_effective_date
2047 ,p_source_id => NULL
2048 ,p_source_text => NULL
2049 ,p_action_information_category => 'EMEA ELEMENT INFO'
2050 ,p_action_information1 => g_element_table(l_index).element_type_id
2051 ,p_action_information2 => g_element_table(l_index).input_value_id
2052 ,p_action_information3 => g_element_table(l_index).element_type
2053 ,p_action_information4 => l_result_value --l_formatted_value
2054 ,p_action_information9 => 'Additional Element'
2055 ,p_assignment_id => p_assignment_id);
2056 END IF;*/
2057
2058
2059 EXCEPTION WHEN OTHERS THEN
2060 g_err_num := SQLCODE;
2061 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_ADD_ELEMENT');*/
2062 IF g_debug THEN
2063 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_ADD_ELEMENT',330);
2064 END IF;
2065
2066 END;
2067
2068 END LOOP;
2069
2070 END IF;
2071
2072 IF g_debug THEN
2073 hr_utility.set_location(' Leaving Procedure ARCHIVE_ADD_ELEMENT',340);
2074 END IF;
2075
2076 END ARCHIVE_ADD_ELEMENT;
2077
2078 /* OTHER BALANCES REGION */
2079 PROCEDURE ARCHIVE_OTH_BALANCE (p_archive_assact_id IN NUMBER,
2080 p_assignment_action_id IN NUMBER,
2081 p_assignment_id IN NUMBER,
2082 p_payroll_action_id IN NUMBER,
2083 p_record_count IN NUMBER,
2084 p_pre_payact_id IN NUMBER,
2085 p_effective_date IN DATE,
2086 p_date_earned IN DATE,
2087 p_archive_flag IN VARCHAR2) IS
2088
2089 /* Cursor to retrieve Other Balances Information */
2090 CURSOR csr_get_balance(p_bus_grp_id NUMBER) IS
2091 SELECT org_information1 balance_type_id
2092 ,org_information2 balance_dim_id
2093 ,org_information3 narrative
2094 FROM hr_organization_information
2095 WHERE organization_id = p_bus_grp_id
2096 AND org_information_context = 'SA_PAYSLIP_BALANCES';
2097
2098 /* Cursor to retrieve Tax Unit Id for setting context */
2099 CURSOR csr_tax_unit (p_run_assact_id NUMBER) IS
2100 SELECT paa.tax_unit_id
2101 FROM pay_assignment_actions paa
2102 WHERE paa.assignment_action_id = p_run_assact_id;
2103
2104 /* Cursor to fetch defined balance id */
2105 CURSOR csr_def_balance(bal_type_id NUMBER, bal_dim_id NUMBER) IS
2106 SELECT defined_balance_id
2107 FROM pay_defined_balances
2108 WHERE balance_type_id = bal_type_id
2109 AND balance_dimension_id = bal_dim_id;
2110
2111 rec_get_balance csr_get_balance%ROWTYPE;
2112 l_balance_value NUMBER := 0;
2113 l_action_info_id NUMBER;
2114 l_ovn NUMBER;
2115 l_index NUMBER;
2116 l_tu_id NUMBER;
2117 l_defined_balance_id NUMBER:=0;
2118 l_formatted_value VARCHAR2(50) := NULL;
2119 l_flag NUMBER := 0;
2120
2121 BEGIN
2122
2123 IF g_debug THEN
2124 hr_utility.set_location(' Entering Procedure ARCHIVE_OTH_BALANCE',350);
2125 END IF;
2126
2127 /*fnd_file.put_line(fnd_file.log,'Entering In ARCHIVE_OTH_BALANCE global');*/
2128
2129
2130
2131 IF p_archive_flag = 'N' THEN
2132
2133 ---------------------------------------------------
2134 --Check if global table has already been populated
2135 ---------------------------------------------------
2136 IF g_user_balance_table.count = 0 THEN
2137
2138 OPEN csr_get_balance(g_business_group_id);
2139
2140 LOOP
2141 FETCH csr_get_balance INTO rec_get_balance;
2142 EXIT WHEN csr_get_balance%NOTFOUND;
2143
2144 OPEN csr_def_balance(rec_get_balance.balance_type_id,rec_get_balance.balance_dim_id);
2145 FETCH csr_def_balance INTO l_defined_balance_id;
2146 CLOSE csr_def_balance;
2147
2148 PAY_SA_ARCHIVE.SETUP_BALANCE_DEFINITIONS(p_balance_name => rec_get_balance.narrative
2149 ,p_defined_balance_id => l_defined_balance_id
2150 ,p_balance_type_id => rec_get_balance.balance_type_id);
2151 END LOOP;
2152 CLOSE csr_get_balance;
2153 END IF;
2154
2155 ELSIF p_archive_flag = 'Y' THEN
2156
2157 OPEN csr_tax_unit(p_assignment_action_id);
2158 FETCH csr_tax_unit INTO l_tu_id;
2159 CLOSE csr_tax_unit;
2160
2161 PAY_BALANCE_PKG.SET_CONTEXT('TAX_UNIT_ID',l_tu_id);
2162 PAY_BALANCE_PKG.SET_CONTEXT('DATE_EARNED',fnd_date.date_to_canonical(p_date_earned));
2163
2164 IF g_user_balance_table.count > 0 THEN
2165
2166 FOR l_index IN g_user_balance_table.first.. g_user_balance_table.last LOOP
2167
2168 l_balance_value := pay_balance_pkg.get_value(g_user_balance_table(l_index).defined_balance_id,p_assignment_action_id);
2169
2170
2171 IF l_balance_value > 0 THEN
2172
2173 pay_action_information_api.create_action_information (
2174 p_action_information_id => l_action_info_id
2175 ,p_action_context_id => p_archive_assact_id
2176 ,p_action_context_type => 'AAP'
2177 ,p_object_version_number => l_ovn
2178 ,p_effective_date => p_effective_date
2179 ,p_source_id => NULL
2180 ,p_source_text => NULL
2181 ,p_action_information_category => 'EMEA BALANCES'
2182 ,p_action_information1 => g_user_balance_table(l_index).defined_balance_id
2183 ,p_action_information2 => 'OBAL' --Other Balances Context
2184 ,p_action_information4 => fnd_number.number_to_canonical(l_balance_value) --l_formatted_value
2185 ,p_action_information5 => NULL
2186 ,p_action_information6 => 'Other Balances'
2187 ,p_assignment_id => p_assignment_id);
2188
2189 END IF;
2190
2191 END LOOP;
2192
2193
2194 END IF; /* For table count check */
2195
2196
2197 END IF;
2198
2199
2200 EXCEPTION WHEN OTHERS THEN
2201 g_err_num := SQLCODE;
2202
2203 /*fnd_file.put_line(fnd_file.log,'ORA_ERR: ' || g_err_num || 'In ARCHIVE_OTH_BALANCE'||SQLERRM);*/
2204 IF g_debug THEN
2205 hr_utility.set_location('ORA_ERR: ' || g_err_num || 'In ARCHIVE_OTH_BALANCE',360);
2206 END IF;
2207
2208 END ARCHIVE_OTH_BALANCE;
2209
2210 /* ARCHIVE CODE */
2211 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
2212 ,p_effective_date IN DATE)
2213 IS
2214
2215 /* Cursor to retrieve Payroll and Prepayment related Ids for Archival */
2216 CURSOR csr_archive_ids (p_locking_action_id NUMBER) IS
2217 SELECT prepay_assact.assignment_action_id prepay_assact_id
2218 ,prepay_assact.assignment_id prepay_assgt_id
2219 ,prepay_payact.payroll_action_id prepay_payact_id
2220 ,prepay_payact.effective_date prepay_effective_date
2221 ,run_assact.assignment_id run_assgt_id
2222 ,run_assact.assignment_action_id run_assact_id
2223 ,run_payact.payroll_action_id run_payact_id
2224 ,run_payact.payroll_id payroll_id
2225 FROM pay_action_interlocks archive_intlck
2226 ,pay_assignment_actions prepay_assact
2227 ,pay_payroll_actions prepay_payact
2228 ,pay_action_interlocks prepay_intlck
2229 ,pay_assignment_actions run_assact
2230 ,pay_payroll_actions run_payact
2231 WHERE archive_intlck.locking_action_id = p_locking_action_id
2232 AND prepay_assact.assignment_action_id = archive_intlck.locked_action_id
2233 AND prepay_payact.payroll_action_id = prepay_assact.payroll_action_id
2234 AND prepay_payact.action_type IN ('U','P')
2235 AND prepay_intlck.locking_action_id = prepay_assact.assignment_action_id
2236 AND run_assact.assignment_action_id = prepay_intlck.locked_action_id
2237 AND run_payact.payroll_action_id = run_assact.payroll_action_id
2238 AND run_payact.action_type IN ('Q', 'R')
2239 ORDER BY prepay_intlck.locking_action_id,prepay_intlck.locked_action_id desc;
2240
2241 /* Cursor to retrieve time period information */
2242 CURSOR csr_period_end_date(p_assact_id NUMBER,p_pay_act_id NUMBER) IS
2243 SELECT ptp.end_date end_date,
2244 ptp.regular_payment_date regular_payment_date,
2245 ptp.time_period_id time_period_id,
2246 ppa.date_earned date_earned,
2247 ppa.effective_date effective_date,
2248 ptp.start_date start_date
2249 FROM per_time_periods ptp
2250 ,pay_payroll_actions ppa
2251 ,pay_assignment_actions paa
2252 WHERE ptp.payroll_id =ppa.payroll_id
2253 AND ppa.payroll_action_id =paa.payroll_action_id
2254 AND paa.assignment_action_id =p_assact_id
2255 AND ppa.payroll_action_id =p_pay_act_id
2256 AND ppa.date_earned BETWEEN ptp.start_date
2257 AND ptp.end_date;
2258
2259 /* Cursor to retrieve Archive Payroll Action Id */
2260 CURSOR csr_archive_payact(p_assignment_action_id NUMBER) IS
2261 SELECT payroll_action_id
2262 FROM pay_assignment_actions
2263 WHERE assignment_Action_id = p_assignment_action_id;
2264
2265
2266 l_archive_payact_id NUMBER;
2267 l_record_count NUMBER;
2268 l_actid NUMBER;
2269 l_end_date per_time_periods.end_date%TYPE;
2270 l_pre_end_date per_time_periods.end_date%TYPE;
2271 l_reg_payment_date per_time_periods.regular_payment_date%TYPE;
2272 l_pre_reg_payment_date per_time_periods.regular_payment_date%TYPE;
2273 l_date_earned pay_payroll_actions.date_earned%TYPE;
2274 l_pre_date_earned pay_payroll_actions.date_earned%TYPE;
2275 l_effective_date pay_payroll_actions.effective_date%TYPE;
2276 l_pre_effective_date pay_payroll_actions.effective_date%TYPE;
2277 l_run_payact_id NUMBER;
2278 l_action_context_id NUMBER;
2279 g_archive_pact NUMBER;
2280 p_assactid NUMBER;
2281 l_time_period_id per_time_periods.time_period_id%TYPE;
2282 l_pre_time_period_id per_time_periods.time_period_id%TYPE;
2283 l_start_date per_time_periods.start_date%TYPE;
2284 l_pre_start_date per_time_periods.start_date%TYPE;
2285 l_fnd_session NUMBER := 0;
2286 l_prev_prepay NUMBER := 0;
2287
2288 BEGIN
2289
2290 IF g_debug THEN
2291 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',380);
2292 END IF;
2293
2294
2295 OPEN csr_archive_payact(p_assignment_action_id);
2296 FETCH csr_archive_payact INTO l_archive_payact_id;
2297 CLOSE csr_archive_payact;
2298
2299 l_record_count := 0;
2300 FOR rec_archive_ids IN csr_archive_ids(p_assignment_action_id) LOOP
2301 OPEN csr_period_end_date(rec_archive_ids.run_assact_id,rec_archive_ids.run_payact_id);
2302 FETCH csr_period_end_date INTO l_end_date,l_reg_payment_date,l_time_period_id,l_date_earned,l_effective_date,l_start_date;
2303 CLOSE csr_period_end_date;
2304
2305 OPEN csr_period_end_date(rec_archive_ids.prepay_assact_id,rec_archive_ids.prepay_payact_id);
2306 FETCH csr_period_end_date INTO l_pre_end_date,l_pre_reg_payment_date,l_pre_time_period_id,l_pre_date_earned,l_pre_effective_date,l_pre_start_date;
2307 CLOSE csr_period_end_date;
2308
2309 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_EMPLOYEE_DETAILS');*/
2310
2311 -------------------------------------------------------------
2312 --Archive EMPLOYEE_DETAILS, PAYMENT_INFO and BALANCES
2313 --for every prepayment assignment action id
2314 -------------------------------------------------------------
2315
2316 IF (rec_archive_ids.prepay_assact_id <> l_prev_prepay) THEN
2317
2318 ARCHIVE_EMPLOYEE_DETAILS(p_archive_assact_id => p_assignment_action_id
2319 ,p_assignment_id => rec_archive_ids.run_assgt_id
2320 ,p_assignment_action_id => rec_archive_ids.run_assact_id
2321 ,p_payroll_action_id => l_archive_payact_id
2322 ,p_time_period_id => l_time_period_id
2323 ,p_date_earned => l_date_earned --l_pre_date_earned
2324 ,p_pay_date_earned => l_date_earned
2325 ,p_effective_date => p_effective_date);
2326
2327
2328 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_PAYMENT_INFO');*/
2329 ARCHIVE_PAYMENT_INFO(p_archive_assact_id => p_assignment_action_id,
2330 p_prepay_assact_id => rec_archive_ids.prepay_assact_id,
2331 p_assignment_id => rec_archive_ids.prepay_assgt_id,
2332 p_date_earned => l_date_earned, --l_pre_date_earned,
2333 p_effective_date => p_effective_date);
2334
2335 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_OTH_BALANCE');*/
2336 ARCHIVE_OTH_BALANCE(p_archive_assact_id => p_assignment_action_id,
2337 p_assignment_action_id => rec_archive_ids.run_assact_id,
2338 p_assignment_id => rec_archive_ids.run_assgt_id,
2339 p_payroll_action_id => l_archive_payact_id,
2340 p_record_count => l_record_count,
2341 p_pre_payact_id => rec_archive_ids.prepay_payact_id,
2342 p_effective_date => p_effective_date,
2343 p_date_earned => l_date_earned,
2344 p_archive_flag => 'Y');
2345
2346 l_prev_prepay := rec_archive_ids.prepay_assact_id;
2347
2348 END IF;
2349
2350 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_ACCRUAL_PLAN');*/
2351 ARCHIVE_ACCRUAL_PLAN (p_assignment_id => rec_archive_ids.run_assgt_id,
2352 p_date_earned => l_date_earned,
2353 p_effective_date => p_effective_date,
2354 p_archive_assact_id => p_assignment_action_id,
2355 p_run_assignment_action_id => rec_archive_ids.run_assact_id,
2356 p_period_end_date => l_end_date,
2357 p_period_start_date => l_start_date);
2358
2359 /*fnd_file.put_line(fnd_file.log,'ARCHIVE_ADD_ELEMENT');*/
2360 ARCHIVE_ADD_ELEMENT(p_archive_assact_id => p_assignment_action_id,
2361 p_assignment_action_id => rec_archive_ids.run_assact_id,
2362 p_assignment_id => rec_archive_ids.run_assgt_id,
2363 p_payroll_action_id => l_archive_payact_id,
2364 p_date_earned => l_date_earned,
2365 p_effective_date => p_effective_date,
2366 p_pre_payact_id => rec_archive_ids.prepay_payact_id,
2367 p_archive_flag => 'Y');
2368
2369
2370
2371 /*fnd_file.put_line(fnd_file.log,'Assact id: '|| p_assignment_action_id);*/
2372
2373 l_record_count := l_record_count + 1;
2374 END LOOP;
2375
2376 IF g_debug THEN
2377 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',390);
2378 END IF;
2379
2380 END ARCHIVE_CODE;
2381 END PAY_SA_ARCHIVE;