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