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