[Home] [Help]
PACKAGE BODY: APPS.PAY_FI_PAYLIST_ARCHIVE
Source
1 PACKAGE BODY PAY_FI_PAYLIST_ARCHIVE AS
2 /* $Header: pyfipayla.pkb 120.2 2006/04/06 00:58:17 dragarwa noship $ */
3 g_debug BOOLEAN := hr_utility.debug_enabled;
4
5 TYPE lock_rec IS RECORD (
6 archive_assact_id NUMBER);
7
8 TYPE lock_table IS TABLE OF lock_rec
9 INDEX BY BINARY_INTEGER;
10
11 g_lock_table lock_table;
12 g_index NUMBER := -1;
13 g_index_assact NUMBER := -1;
14 g_index_bal NUMBER := -1;
15 g_package VARCHAR2 (33) := ' PAY_FI_PAYLIST_ARCHIVE.';
16 g_run_payroll_action_id NUMBER;
17 g_arc_payroll_action_id NUMBER;
18
19 -- Globals to pick up all the parameter
20 g_business_group_id NUMBER;
21 g_effective_date DATE;
22 g_trade_union_id NUMBER;
23 g_legal_employer_id NUMBER;
24 g_start_date DATE;
25 g_payroll_id NUMBER;
26 g_pay_period_id NUMBER;
27 g_pay_period VARCHAR2 (240);
28 g_payroll VARCHAR2 (240);
29 g_pay_period_end_date DATE;
30 g_legal_employer_name VARCHAR2 (240);
31
32 --End of Globals to pick up all the parameter
33 g_format_mask VARCHAR2 (50);
34 g_err_num NUMBER;
35 g_errm VARCHAR2 (150);
36 g_archive VARCHAR2 (1);
37
38 /* GET PARAMETER */
39
40 FUNCTION get_parameter (
41 p_parameter_string IN VARCHAR2,
42 p_token IN VARCHAR2,
43 p_segment_number IN NUMBER DEFAULT NULL
44 )
45 RETURN VARCHAR2
46 IS
47 l_parameter pay_payroll_actions.legislative_parameters%TYPE := NULL;
48 l_start_pos NUMBER;
49 l_delimiter VARCHAR2 (1) := ' ';
50 l_proc VARCHAR2 (40) := g_package
51 || ' get parameter ';
52 BEGIN
53 --
54 IF g_debug
55 THEN
56 hr_utility.set_location (' Entering Function GET_PARAMETER', 10);
57 END IF;
58
59 l_start_pos :=
60 INSTR ( ' '
61 || p_parameter_string, l_delimiter
62 || p_token
63 || '=');
64
65 --
66 IF l_start_pos = 0
67 THEN
68 l_delimiter := '|';
69 l_start_pos := INSTR (
70 ' '
71 || p_parameter_string,
72 l_delimiter
73 || p_token
74 || '='
75 );
76 END IF;
77
78 IF l_start_pos <> 0
79 THEN
80 l_start_pos := l_start_pos
81 + LENGTH ( p_token
82 || '=');
83 l_parameter := SUBSTR (
84 p_parameter_string,
85 l_start_pos,
86 INSTR (
87 p_parameter_string
88 || ' ',
89 l_delimiter,
90 l_start_pos
91 )
92 - l_start_pos
93 );
94
95 IF p_segment_number IS NOT NULL
96 THEN
97 l_parameter := ':'
98 || l_parameter
99 || ':';
100 l_parameter := SUBSTR (
101 l_parameter,
102 INSTR (l_parameter, ':', 1, p_segment_number)
103 + 1,
104 INSTR (
105 l_parameter,
106 ':',
107 1,
108 p_segment_number
109 + 1
110 )
111 - 1
112 - INSTR (l_parameter, ':', 1, p_segment_number)
113 );
114 END IF;
115 END IF;
116
117 --
118 RETURN l_parameter;
119
120 IF g_debug
121 THEN
122 hr_utility.set_location (' Leaving Function GET_PARAMETER', 20);
123 END IF;
124 END;
125
126 /* GET ALL PARAMETERS */
127 PROCEDURE get_all_parameters (
128 p_payroll_action_id IN NUMBER -- In parameter
129 ,
130 p_business_group_id OUT NOCOPY NUMBER,
131 p_start_date OUT NOCOPY DATE,
132 p_effective_date OUT NOCOPY DATE,
133 --p_legal_employer_id OUT NOCOPY NUMBER,
134 p_payroll_id OUT NOCOPY NUMBER,
135 p_run_payroll_action_id OUT NOCOPY NUMBER,
136 p_archive OUT NOCOPY VARCHAR2
137 )
138 IS
139 --
140 CURSOR csr_parameter_info (p_payroll_action_id NUMBER)
141 IS
142 SELECT pay_fi_archive_umfr.get_parameter (
143 legislative_parameters,
144 'ARCHIVE'
145 )
146 ARCHIVE,
147 TO_NUMBER (
148 pay_fi_archive_umfr.get_parameter (
149 legislative_parameters,
150 'PAYROLL_ID'
151 )
152 )
153 payroll_id,
154 TO_NUMBER (
155 pay_fi_archive_umfr.get_parameter (
156 legislative_parameters,
157 'PAYROLL_ACTION_ID'
158 )
159 )
160 RUN_payroll_action_id,
161 start_date, effective_date effective_date,
162 business_group_id bg_id
163 FROM pay_payroll_actions
164 WHERE payroll_action_id = p_payroll_action_id;
165
166 lr_parameter_info csr_parameter_info%ROWTYPE;
167 l_proc VARCHAR2 (240)
168 := g_package
169 || ' GET_ALL_PARAMETERS ';
170 --
171 BEGIN
172 fnd_file.put_line (
173 fnd_file.LOG,
174 'Entering Procedure GET_ALL_PARAMETER '
175 );
176 fnd_file.put_line (
177 fnd_file.LOG,
178 'Payroill Action iD '
179 || p_RUN_payroll_action_id
180 );
181 OPEN csr_parameter_info (p_payroll_action_id);
182 --FETCH csr_parameter_info into lr_parameter_info;
183 FETCH csr_parameter_info INTO p_archive,
184 p_payroll_id,
185 p_run_payroll_action_id,
186 p_start_date,
187 p_effective_date,
188 p_business_group_id;
189 CLOSE csr_parameter_info;
190 fnd_file.put_line (fnd_file.LOG, 'After csr_parameter_info in ');
191 fnd_file.put_line (fnd_file.LOG, 'archive='
192 || p_archive);
193
194 IF g_debug
195 THEN
196 hr_utility.set_location (
197 ' Leaving Procedure GET_ALL_PARAMETERS',
198 30
199 );
200 END IF;
201 END get_all_parameters;
202
203 /* RANGE CODE */
204 PROCEDURE range_code (
205 p_payroll_action_id IN NUMBER,
206 p_sql OUT NOCOPY VARCHAR2
207 )
208 IS
209 l_action_info_id NUMBER;
210 l_ovn NUMBER;
211 l_business_group_id NUMBER;
212 l_start_date VARCHAR2 (30);
213 l_end_date VARCHAR2 (30);
214 l_effective_date DATE;
215 l_consolidation_set NUMBER;
216 l_defined_balance_id NUMBER := 0;
217 l_count NUMBER := 0;
218 l_prev_prepay NUMBER := 0;
219 l_canonical_start_date DATE;
220 l_canonical_end_date DATE;
221 l_payroll_id NUMBER;
222 l_prepay_action_id NUMBER;
223 l_actid NUMBER;
224 l_assignment_id NUMBER;
225 l_trade_union_number NUMBER;
226 l_y_number VARCHAR2 (30);
227 l_local_unit_id_fetched NUMBER;
228 l_accounting_id NUMBER;
229 l_action_sequence NUMBER;
230 l_assact_id NUMBER;
231 l_pact_id NUMBER;
232 l_flag NUMBER := 0;
233 l_element_context VARCHAR2 (5);
234
235 /* CURSOR csr_legal_employer_details (
236 csr_v_legal_employer_id hr_organization_information.organization_id%TYPE
237 )
238 IS
239 SELECT hou.NAME, hoi.org_information1, hoi.org_information8
240 FROM hr_organization_information hoi, hr_organization_units hou
241 WHERE org_information_context = 'FI_LEGAL_EMPLOYER_DETAILS'
242 AND hoi.organization_id = hou.organization_id
243 AND hou.organization_id = csr_v_legal_employer_id;
244
245 lr_legal_employer_details csr_legal_employer_details%ROWTYPE;
246 */
247
248 CURSOR csr_time_period_details (
249 csr_v_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE
250 )
251 IS
252 select papf.payroll_name,ptp.end_date , ptp.period_name ,
253 ptp.regular_payment_date from pay_payroll_actions ppa,per_time_periods ptp,pay_all_payrolls_f papf
254 where ptp.time_period_id=ppa.time_period_id
255 and ppa.PAYROLL_ID=papf.PAYROLL_ID
256 and ppa.payroll_action_id=csr_v_payroll_action_id;
257
258
259 lr_time_period_details csr_time_period_details%ROWTYPE;
260 BEGIN
261 IF g_debug
262 THEN
263 hr_utility.set_location (' Entering Procedure RANGE_CODE', 40);
264 END IF;
265
266 fnd_file.put_line (fnd_file.LOG, 'Entering Procedure RANGE_CODE 7');
267 p_sql :=
268 'SELECT DISTINCT person_id
269 FROM per_people_f ppf
270 ,pay_payroll_actions ppa
271 WHERE ppa.payroll_action_id = :payroll_action_id
272 AND ppa.business_group_id = ppf.business_group_id
273 ORDER BY ppf.person_id';
274 get_all_parameters (
275 p_payroll_action_id,
276 g_business_group_id,
277 g_start_date,
278 g_effective_date,
279 g_payroll_id,
280 g_run_payroll_action_id,
281 g_archive
282 );
283
284 IF g_archive = 'Y'
285 THEN
286 -- Pick up the details belonging to Legal Employer Details
287 /* OPEN csr_legal_employer_details (g_legal_employer_id);
288 FETCH csr_legal_employer_details INTO lr_legal_employer_details;
289 CLOSE csr_legal_employer_details;*/
290 OPEN csr_time_period_details (g_run_payroll_action_id);
291 FETCH csr_time_period_details INTO lr_time_period_details;
292 CLOSE csr_time_period_details;
293 g_pay_period_end_date := lr_time_period_details.end_date;
294 pay_action_information_api.create_action_information (
295 p_action_information_id=> l_action_info_id,
296 p_action_context_id=> p_payroll_action_id,
297 p_action_context_type=> 'PA',
298 p_object_version_number=> l_ovn,
299 p_effective_date=> g_effective_date,
300 p_source_id=> NULL,
301 p_source_text=> NULL,
302 p_action_information_category=> 'EMEA REPORT DETAILS',
303 p_action_information1=> 'PYFIPAYL',
304 p_action_information2=> g_payroll_id ,
305 p_action_information3=> g_run_payroll_action_id,
306 p_action_information4=> lr_time_period_details.Payroll_name,
307 p_action_information5=> lr_time_period_details.period_name,
308 p_action_information6=> fnd_date.date_to_canonical (
309 lr_time_period_details.regular_payment_date
310 ),
311 p_action_information7=> fnd_date.date_to_canonical (
312 lr_time_period_details.end_date
313 ),
314 p_action_information8=> NULL,
315 p_action_information9=> NULL,
316 p_action_information10=> NULL,
317 p_action_information11=> NULL,
318 p_action_information12=> NULL,
319 p_action_information13=> NULL,
320 p_action_information14=> NULL,
321 p_action_information15=> NULL,
322 p_action_information16=> NULL,
323 p_action_information17=> NULL,
324 p_action_information18=> NULL,
325 p_action_information19=> NULL,
326 p_action_information20=> NULL,
327 p_action_information21=> NULL,
328 p_action_information22=> NULL,
329 p_action_information23=> NULL,
330 p_action_information24=> NULL,
331 p_action_information25=> NULL,
332 p_action_information26=> NULL,
333 p_action_information27=> NULL,
334 p_action_information28=> NULL,
335 p_action_information29=> NULL,
336 p_action_information30=> NULL
337 );
338 END IF; --archiving=yes
339
340 IF g_debug
341 THEN
342 hr_utility.set_location (' Leaving Procedure RANGE_CODE', 50);
343 END IF;
344 EXCEPTION
345 WHEN OTHERS
346 THEN
347 -- Return cursor that selects no rows
348 p_sql :=
349 'select 1 from dual where to_char(:payroll_action_id) = dummy';
350 END range_code;
351
352 /* ASSIGNMENT ACTION CODE */
353 PROCEDURE assignment_action_code (
354 p_payroll_action_id IN NUMBER,
355 p_start_person IN NUMBER,
356 p_end_person IN NUMBER,
357 p_chunk IN NUMBER
358 )
359 IS
360 CURSOR csr_prepaid_assignments (
361 p_payroll_action_id NUMBER,
362 p_start_person NUMBER,
363 p_end_person NUMBER,
364 p_run_payroll_action_id NUMBER,
365 l_bussiness_group_id NUMBER
366
367
368 -- p_chunk NUMBER
369 )
370 IS
371 SELECT act.assignment_id assignment_id,
372 act.assignment_action_id run_action_id,
373 act1.assignment_action_id prepaid_action_id
374 FROM pay_payroll_actions appa,
375 pay_payroll_actions appa2,
376 pay_assignment_actions act,
377 pay_assignment_actions act1,
378 pay_action_interlocks pai,
379 per_all_assignments_f paaf
380 WHERE appa.payroll_action_id=p_run_payroll_action_id
381 AND appa.action_type IN ('R', 'Q')
382 AND act.payroll_action_id = appa.payroll_action_id
383 AND act.source_action_id IS NULL -- Master Action
384 AND act.action_status = 'C' -- Completed
385 AND act.assignment_action_id = pai.locked_action_id
386 AND act1.assignment_action_id = pai.locking_action_id
387 AND act1.action_status = 'C' -- Completed
388 AND act1.payroll_action_id = appa2.payroll_action_id
389 AND appa2.action_type IN ('P', 'U')
390 and paaf.assignment_id=act.assignment_id
391 and paaf.person_id BETWEEN p_start_person
392 AND p_end_person ;
393
394
395 l_count NUMBER := 0;
396 l_prev_prepay NUMBER := 0;
397 l_start_date VARCHAR2 (20);
398 l_end_date VARCHAR2 (20);
399 l_canonical_start_date DATE;
400 l_canonical_end_date DATE;
401 l_payroll_id NUMBER;
402 l_consolidation_set NUMBER;
403 l_prepay_action_id NUMBER;
404 l_actid NUMBER;
405 l_assignment_id NUMBER;
406 l_action_sequence NUMBER;
407 l_assact_id NUMBER;
408 l_pact_id NUMBER;
409 l_flag NUMBER := 0;
410 l_defined_balance_id NUMBER := 0;
411 l_action_info_id NUMBER;
412 l_ovn NUMBER;
413 -- User pARAMETERS needed
414 l_business_group_id NUMBER;
415 l_effective_date DATE;
416 l_trade_union_id NUMBER;
417 l_legal_employer_id NUMBER;
418 l_local_unit_id NUMBER;
419 l_reporting_date DATE;
420 l_period VARCHAR2 (240);
421 l_period_start_date DATE;
422 l_period_end_date DATE;
423 -- End of User pARAMETERS needed
424 l_assignment NUMBER;
425 BEGIN
426 IF g_debug
427 THEN
428 hr_utility.set_location (
429 ' Entering Procedure ASSIGNMENT_ACTION_CODE',
430 60
431 );
432 END IF;
433 get_all_parameters (
434 p_payroll_action_id,
435 g_business_group_id,
436 g_start_date,
437 g_effective_date,
438 g_payroll_id,
439 g_run_payroll_action_id,
440 g_archive
441 );
442
443 fnd_file.put_line (fnd_file.LOG, ' ');
444 fnd_file.put_line (
445 fnd_file.LOG,
446 'Parameter P_Start_person '
447 || p_start_person
448 );
449 fnd_file.put_line (
450 fnd_file.LOG,
451 'Paramter P_end_personn '
452 || p_end_person
453 );
454 fnd_file.put_line (
455 fnd_file.LOG,
456 'Paramter P_end_personn '
457 || p_end_person
458 );
459 fnd_file.put_line (
460 fnd_file.LOG,
461 'Paramter P_end_personn '
462 || p_end_person
463 );
464 fnd_file.put_line (
465 fnd_file.LOG,
466 'g_run_payroll_action_id'
467 || g_run_payroll_action_id
468 );
469
470 IF g_archive = 'Y'
471 THEN
472 l_prepay_action_id := 0;
473
474 l_assignment := 0;
475 fnd_file.put_line (fnd_file.LOG, ' Before the Locking Cursor ');
476
477 -- this is for all the person's assignment actionid under the selected legal employer
478 FOR rec_prepaid_assignments IN
479 csr_prepaid_assignments (
480 p_payroll_action_id,
481 p_start_person,
482 p_end_person,
483 g_run_payroll_action_id,
484 g_business_group_id
485 )
486 LOOP
487 fnd_file.put_line (
488 fnd_file.LOG,
489 ' Inside the Csr Prepaid Cursor '
490 );
491
492
493 IF l_assignment <> rec_prepaid_assignments.assignment_id
494
495 -- IF l_prepay_action_id <>
496 -- rec_prepaid_assignments.prepaid_action_id
497 THEN
498 SELECT pay_assignment_actions_s.NEXTVAL
499 INTO l_actid
500 FROM DUAL;
501
502 --
503 g_index_assact := g_index_assact
504 + 1;
505 g_lock_table (g_index_assact).archive_assact_id := l_actid;
506 /* For Element archival */
507 -- Create the archive assignment action
508 fnd_file.put_line (
509 fnd_file.LOG,
510 'l_actid'
511 || l_actid
512 || ' rec_prepaid_assignments.assignment_id'
513 || rec_prepaid_assignments.assignment_id
514 || ' p_chunk'
515 || p_chunk
516 );
517 hr_nonrun_asact.insact (
518 l_actid,
519 rec_prepaid_assignments.assignment_id,
520 p_payroll_action_id,
521 p_chunk,
522 NULL
523 );
524 -- Create archive to prepayment assignment action interlock
525 --
526 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.prepaid_action_id);
527 END IF;
528
529 -- create archive to master assignment action interlock
530 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.run_action_id);
531 -- l_prepay_action_id := rec_prepaid_assignments.prepaid_action_id;
532
533 l_assignment := rec_prepaid_assignments.assignment_id;
534 END LOOP;
535 END IF; --ARCHIVE
536
537 fnd_file.put_line (
538 fnd_file.LOG,
539 ' After Ending Assignment Act Code the Locking Cursor '
540 );
541
542 IF g_debug
543 THEN
544 hr_utility.set_location (
545 ' Leaving Procedure ASSIGNMENT_ACTION_CODE',
546 70
547 );
548 END IF;
549 END assignment_action_code;
550
551 /* INITIALIZATION CODE */
552 PROCEDURE initialization_code (p_payroll_action_id IN NUMBER)
553 IS
554 BEGIN
555 IF g_debug
556 THEN
557 hr_utility.set_location (
558 ' Entering Procedure INITIALIZATION_CODE',
559 80
560 );
561 END IF;
562
563 fnd_file.put_line (fnd_file.LOG, 'In INIT_CODE 0');
564
565 IF g_debug
566 THEN
567 hr_utility.set_location (
568 ' Leaving Procedure INITIALIZATION_CODE',
569 90
570 );
571 END IF;
572 EXCEPTION
573 WHEN OTHERS
574 THEN
575 g_err_num := SQLCODE;
576
577 IF g_debug
578 THEN
579 hr_utility.set_location (
580 'ORA_ERR: '
581 || g_err_num
582 || 'In INITIALIZATION_CODE',
583 180
584 );
585 END IF;
586 END initialization_code;
587
588 /* ARCHIVE CODE */
589 PROCEDURE archive_code (
590 p_assignment_action_id IN NUMBER,
591 p_effective_date IN DATE
592 )
593 IS
594 /* Cursor to retrieve Payroll and Prepayment related Ids for Archival */
595
596 /* Cursor to retrieve Archive Payroll Action Id */
597
598 l_archive_payact_id NUMBER;
599 l_record_count NUMBER;
600 l_actid NUMBER;
601 l_end_date per_time_periods.end_date%TYPE;
602 l_pre_end_date per_time_periods.end_date%TYPE;
603 l_reg_payment_date per_time_periods.regular_payment_date%TYPE;
604 l_pre_reg_payment_date per_time_periods.regular_payment_date%TYPE;
605 l_date_earned pay_payroll_actions.date_earned%TYPE;
606 l_pre_date_earned pay_payroll_actions.date_earned%TYPE;
607 l_effective_date pay_payroll_actions.effective_date%TYPE;
608 l_pre_effective_date pay_payroll_actions.effective_date%TYPE;
609 l_run_payact_id NUMBER;
610 l_action_context_id NUMBER;
611 g_archive_pact NUMBER;
612 p_assactid NUMBER;
613 l_time_period_id per_time_periods.time_period_id%TYPE;
614 l_pre_time_period_id per_time_periods.time_period_id%TYPE;
615 l_start_date per_time_periods.start_date%TYPE;
616 l_pre_start_date per_time_periods.start_date%TYPE;
617 l_fnd_session NUMBER := 0;
618 l_prev_prepay NUMBER := 0;
619 l_action_info_id pay_action_information.action_information_id%TYPE;
620 l_ovn pay_action_information.object_version_number%TYPE;
621 l_flag NUMBER := 0;
622 -- The place for Variables which fetches the values to be archived
623 l_y_number VARCHAR2 (240);
624 l_y_number_spare NUMBER;
625 l_accounting_id NUMBER;
626 l_accounting_id_spare VARCHAR2 (240);
627 l_trade_union_number NUMBER;
628 l_local_unit_number NUMBER;
629 l_employee_pin VARCHAR2 (240);
630 l_employee_name VARCHAR2 (240);
631 l_membership_start_date DATE;
632 l_membership_end_date DATE;
633 l_amount_of_payment NUMBER;
634 l_reason_of_payment VARCHAR2 (240) := '00'; -- 00 => Normal Membership fee
635 l_tax_year NUMBER; -- YYYY format
636 l_union_dues NUMBER;
637 l_local_unit_id_fetched NUMBER;
638
639 CURSOR csr_person_details (
640 csr_v_business_group_id per_all_people_f.business_group_id%TYPE
641 )
642 IS
643 SELECT Distinct(pap.full_name) NAME,
644 PAA.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER,
645 -- pap.national_identifier national_identifier,
646 paa.assignment_id assignment_id
647 FROM per_all_people_f pap,
648 per_all_assignments_f paa,
649 pay_assignment_actions pasa
650 WHERE paa.person_id = pap.person_id
651 AND pasa.assignment_id = paa.assignment_id
652 AND pap.business_group_id = csr_v_business_group_id
653 -- AND g_pay_period_end_date BETWEEN pap.effective_start_date
654 -- AND pap.effective_end_date
655 -- AND g_pay_period_end_date BETWEEN paa.effective_start_date
656 -- AND paa.effective_end_date
657 AND pasa.assignment_action_id = p_assignment_action_id;
658
659 lr_person_details csr_person_details%ROWTYPE;
660 -- Cursor to pick up segment2
661
662 -- AND paa.primary_flag = 'Y';
663
664
665 l_union_per_le VARCHAR2 (100);
666
667 -- l_benefits VARCHAR2 (100);
668 l_negative_per_lu VARCHAR2 (100);
669 l_negative_per_le VARCHAR2 (100);
670 l_sign_of_payment VARCHAR2 (1);
671 -- End of Cursors
672
673 -- End of place for Cursor which fetches the values to be archived
674
675
676 BEGIN
677 IF g_debug
678 THEN
679 hr_utility.set_location (' Entering Procedure ARCHIVE_CODE', 380);
680 END IF;
681
682 IF g_archive = 'Y'
683 THEN
684
685 --
686 --
687 --
688 fnd_file.put_line (fnd_file.LOG, 'Entering ARCHIVE_CODE ');
689 OPEN csr_person_details (g_business_group_id);
690 FETCH csr_person_details INTO lr_person_details;
691 CLOSE csr_person_details;
692 -- l_employee_pin := lr_person_details.national_identifier;
693 pay_balance_pkg.set_context ('TAX_UNIT_ID', g_legal_employer_id);
694 pay_balance_pkg.set_context (
695 'DATE_EARNED',
696 fnd_date.date_to_canonical (g_pay_period_end_date)
697 );
698 pay_balance_pkg.set_context ('JURISDICTION_CODE', NULL);
699 pay_balance_pkg.set_context (
700 'ASSIGNMENT_ID',
701 lr_person_details.assignment_id
702 );
703 pay_balance_pkg.set_context ('SOURCE_ID', NULL);
704 pay_balance_pkg.set_context ('TAX_GROUP', NULL);
705 /* OPEN csr_Get_Defined_Balance_Id( 'BENEFITS_IN_KIND_PER_LE_PTD');
706 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
707 CLOSE csr_Get_Defined_Balance_Id;
708
709 l_benefits :=pay_balance_pkg.get_value(P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id, P_ASSIGNMENT_ID =>lr_person_details.assignment_id , P_VIRTUAL_DATE => g_pay_period_end_date );
710 fnd_file.put_line (
711 fnd_file.LOG,
712 ' l_benefits'||l_benefits
713
714 );
715
716 */
717
718 /* BEGIN
719 SELECT 1
720 INTO l_flag
721 FROM pay_action_information
722 WHERE action_information_category = 'EMEA REPORT INFORMATION'
723 AND action_information1 = 'PYFIUMFR'
724 AND action_information2 = 'PER'
725 AND action_context_id = p_assignment_action_id;
726 EXCEPTION
727 WHEN NO_DATA_FOUND
728 THEN*/
729
730 pay_action_information_api.create_action_information (
731 p_action_information_id=> l_action_info_id,
732 p_action_context_id=> p_assignment_action_id,
733 p_action_context_type=> 'AAP',
734 p_object_version_number=> l_ovn,
735 p_effective_date=> p_effective_date,
736 p_source_id=> NULL,
737 p_source_text=> NULL,
738 p_action_information_category=> 'EMEA REPORT INFORMATION',
739 p_action_information1=> 'PYFIPAYL',
740 p_action_information2=> 'ASG',
741 p_action_information3=> lr_person_details.NAME,
742 p_action_information4=> FND_NUMBER.NUMBER_TO_CANONICAL(get_balance_value (
743 'Salary Income',
744 p_assignment_action_id
745 )),
746 p_action_information5=> FND_NUMBER.NUMBER_TO_CANONICAL(get_balance_value (
747 'Benefits in Kind',
748 p_assignment_action_id
749 )),
750 p_action_information6=> FND_NUMBER.NUMBER_TO_CANONICAL(get_balance_value (
751 'Insurance Salary Base',
752 p_assignment_action_id
753 )),
754 p_action_information7=> FND_NUMBER.NUMBER_TO_CANONICAL(get_balance_value (
755 'Deductions Before Tax',
756 p_assignment_action_id
757 )),
758 p_action_information8=> FND_NUMBER.NUMBER_TO_CANONICAL(get_balance_value (
759 'Withholding Tax Base',
760 p_assignment_action_id
761 )+get_balance_value (
762 'Tax at Source Base',
763 p_assignment_action_id
764 ))
765 ,
766 p_action_information9=> FND_NUMBER.NUMBER_TO_CANONICAL(get_balance_value (
767 'Withholding Tax',
768 p_assignment_action_id
769 ) +get_balance_value (
770 'Tax at Source',
771 p_assignment_action_id
772 )),
773 p_action_information10=> FND_NUMBER.NUMBER_TO_CANONICAL(get_balance_value (
774 'External Expenses',
775 p_assignment_action_id
776 )),
777 p_action_information11=> FND_NUMBER.NUMBER_TO_CANONICAL(get_balance_value (
778 'Withholding Tax Base',
779 p_assignment_action_id
780 )+ get_balance_value ('Tax at Source Base', p_assignment_action_id )+
781 get_balance_value ('Deductions Before Tax', p_assignment_action_id )-
782 (get_balance_value ('Withholding Tax', p_assignment_action_id )+
783 get_balance_value ('Tax at Source', p_assignment_action_id ))-
784 get_balance_value ('Net Pay', p_assignment_action_id )),
785
786 p_action_information12=> FND_NUMBER.NUMBER_TO_CANONICAL(get_balance_value (
787 'Net Pay',
788 p_assignment_action_id
789 ) ),
790 p_action_information13=> FND_NUMBER.NUMBER_TO_CANONICAL(get_balance_value (
791 'Capital Income Base',
792 p_assignment_action_id
793 )),
794 p_action_information14=> lr_person_details.assignment_number,
795 p_action_information15=> NULL,
796 p_action_information16=> NULL,
797 p_action_information17=> NULL,
798 p_action_information18=> NULL,
799 p_action_information19=> NULL,
800 p_action_information20=> NULL,
801 p_action_information21=> NULL,
802 p_action_information22=> NULL,
803 p_action_information23=> NULL,
804 p_action_information24=> NULL,
805 p_action_information25=> NULL,
806 p_action_information26=> NULL,
807 p_action_information27=> NULL --date from srs req
808 ,
809 p_action_information28=> NULL,
810 p_action_information29=> NULL,
811 p_action_information30=> NULL
812 );
813 END IF; ---ARCHIVE=YES
814
815
816 --
817 --
818 --
819 --END LOOP;
820 IF g_debug
821 THEN
822 hr_utility.set_location (' Leaving Procedure ARCHIVE_CODE', 390);
823 END IF;
824 END archive_code;
825
826 FUNCTION get_balance_value (
827 p_balance_name IN VARCHAR2,
828 p_assignment_action_id IN NUMBER
829 )
830 RETURN NUMBER
831 IS
832 CURSOR csr_get_defined_balance_id (
833 csr_v_balance_name ff_database_items.user_name%TYPE
834 )
835 IS
836 SELECT defined_balance_id
837 FROM pay_balance_types pbt,
838 pay_balance_dimensions pbd,
839 pay_defined_balances pdb
840 WHERE pbt.balance_name = csr_v_balance_name
841 AND pbt.legislation_code = 'FI'
842 AND pbt.balance_type_id = pdb.balance_type_id
843 AND pbd.database_item_suffix = '_ASG_PTD'
844 AND pbd.legislation_code = 'FI'
845 AND pbd.balance_dimension_id = pdb.balance_dimension_id
846 and pdb.legislation_code = 'FI';
847
848 CURSOR csr_get_run_ass_action_id (
849 csr_v_ass_action_id pay_assignment_actions.assignment_action_id%TYPE
850 )
851 IS
852 SELECT paa_run.assignment_action_id
853 FROM pay_assignment_actions paa_archive, pay_assignment_actions paa_run
854 WHERE paa_run.assignment_id = paa_archive.assignment_id
855 AND paa_run.payroll_action_id = g_run_payroll_action_id
856 AND paa_archive.assignment_action_id = p_assignment_action_id;
857 /* SELECT ue.creator_id
858 FROM ff_user_entities ue, ff_database_items di
859 WHERE di.user_name = csr_v_balance_name
860 AND ue.user_entity_id = di.user_entity_id
861 AND ue.legislation_code = 'FI';
862 -- AND ue.business_group_id IS NULL temporary commented
863 -- AND ue.creator_type = 'B';
864 */
865 lr_get_defined_balance_id NUMBER;
866 lr_get_run_ass_action_id NUMBER;
867 BEGIN
868 OPEN csr_get_defined_balance_id (p_balance_name);
869 FETCH csr_get_defined_balance_id INTO lr_get_defined_balance_id;
870 CLOSE csr_get_defined_balance_id;
871
872 OPEN csr_get_run_ass_action_id (p_assignment_action_id);
873 FETCH csr_get_run_ass_action_id INTO lr_get_run_ass_action_id ;
874 CLOSE csr_get_run_ass_action_id ;
875
876 fnd_file.put_line ( fnd_file.LOG,
877 'p_balance_name'
878 || p_balance_name
879 );
880 fnd_file.put_line ( fnd_file.LOG,
881 'lr_get_defined_balance_id '
882 || lr_get_defined_balance_id
883 );
884 fnd_file.put_line ( fnd_file.LOG,
885 'p_assignment_action_id '
886 || p_assignment_action_id
887 );
888 fnd_file.put_line ( fnd_file.LOG,
889 'lr_get_run_ass_action_id'
890 || lr_get_run_ass_action_id
891 );
892
893
894 RETURN (pay_balance_pkg.get_value (
895 p_defined_balance_id=> lr_get_defined_balance_id,
896 p_assignment_action_id=> lr_get_run_ass_action_id
897 )
898 );
899 /* EXCEPTION
900 WHEN OTHERS THEN
901
902 null;*/
903 END get_balance_value;
904 END pay_fi_paylist_archive;