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