[Home] [Help]
PACKAGE BODY: APPS.PAY_FI_ARCHIVE_UMFR
Source
1 PACKAGE BODY PAY_FI_ARCHIVE_UMFR AS
2 /* $Header: pyfiumfa.pkb 120.3 2012/01/19 09:21:20 rpahune ship $ */
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_ARCHIVE_UMFR.';
16 g_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_local_unit_id NUMBER;
25 g_reporting_date DATE;
26 g_period VARCHAR2 (240);
27 g_period_start_date DATE;
28 g_period_end_date DATE;
29
30 --End of Globals to pick up all the parameter
31 g_format_mask VARCHAR2 (50);
32 g_err_num NUMBER;
33 g_errm VARCHAR2 (150);
34 g_archive VARCHAR2 (1);
35
36 /* GET PARAMETER */
37 FUNCTION get_parameter (
38 p_parameter_string IN VARCHAR2,
39 p_token IN VARCHAR2,
40 p_segment_number IN NUMBER DEFAULT NULL
41 )
42 RETURN VARCHAR2
43 IS
44 l_parameter pay_payroll_actions.legislative_parameters%TYPE := NULL;
45 l_start_pos NUMBER;
46 l_delimiter VARCHAR2 (1) := ' ';
47 l_proc VARCHAR2 (40) := g_package
48 || ' get parameter ';
49 BEGIN
50 --
51 IF g_debug
52 THEN
53 hr_utility.set_location (' Entering Function GET_PARAMETER', 10);
54 END IF;
55
56 l_start_pos :=
57 INSTR ( ' '
58 || p_parameter_string, l_delimiter
59 || p_token
60 || '=');
61
62 --
63 IF l_start_pos = 0
64 THEN
65 l_delimiter := '|';
66 l_start_pos := INSTR (
67 ' '
68 || p_parameter_string,
69 l_delimiter
70 || p_token
71 || '='
72 );
73 END IF;
74
75 IF l_start_pos <> 0
76 THEN
77 l_start_pos := l_start_pos
78 + LENGTH ( p_token
79 || '=');
80 l_parameter := SUBSTR (
81 p_parameter_string,
82 l_start_pos,
83 INSTR (
84 p_parameter_string
85 || ' ',
86 l_delimiter,
87 l_start_pos
88 )
89 - l_start_pos
90 );
91
92 IF p_segment_number IS NOT NULL
93 THEN
94 l_parameter := ':'
95 || l_parameter
96 || ':';
97 l_parameter := SUBSTR (
98 l_parameter,
99 INSTR (l_parameter, ':', 1, p_segment_number)
100 + 1,
101 INSTR (
102 l_parameter,
103 ':',
104 1,
105 p_segment_number
106 + 1
107 )
108 - 1
109 - INSTR (l_parameter, ':', 1, p_segment_number)
110 );
111 END IF;
112 END IF;
113
114 --
115 RETURN l_parameter;
116
117 IF g_debug
118 THEN
119 hr_utility.set_location (' Leaving Function GET_PARAMETER', 20);
120 END IF;
121 END;
122
123 /* GET ALL PARAMETERS */
124 PROCEDURE get_all_parameters (
125 p_payroll_action_id IN NUMBER -- In parameter
126 ,
127 p_business_group_id OUT NOCOPY NUMBER -- Core parameter
128 ,
129 p_effective_date OUT NOCOPY DATE -- Core parameter
130 ,
131 p_trade_union_id OUT NOCOPY NUMBER -- User parameter
132 ,
133 p_legal_employer_id OUT NOCOPY NUMBER -- User parameter
134 ,
135 p_local_unit_id OUT NOCOPY NUMBER -- User parameter
136 ,
137
138
139 p_period OUT NOCOPY VARCHAR2, -- User parameter,
140
141 p_period_end_date OUT NOCOPY DATE,
142 p_archive OUT NOCOPY VARCHAR2
143 )
144 IS
145 --
146 CURSOR csr_parameter_info (p_payroll_action_id NUMBER)
147 IS
148 SELECT pay_fi_archive_umfr.get_parameter (
149 legislative_parameters,
150 'ARCHIVE'
151 ),
152 TO_NUMBER (
153 pay_fi_archive_umfr.get_parameter (
154 legislative_parameters,
155 'TRADE_UNION_ID'
156 )
157 )
158 trade,
159 TO_NUMBER (
160 pay_fi_archive_umfr.get_parameter (
161 legislative_parameters,
162 'LEGAL_EMPLOYER_ID'
163 )
164 )
165 legal,
166 TO_NUMBER (
167 pay_fi_archive_umfr.get_parameter (
168 legislative_parameters,
169 'LOCAL_UNIT_ID'
170 )
171 )
172 LOCAL,
173 pay_fi_archive_umfr.get_parameter (
174 legislative_parameters,
175 'PERIOD'
176 )
177 period,
178 fnd_date.canonical_to_date (
179 pay_fi_archive_umfr.get_parameter (
180 legislative_parameters,
181 'PERIOD_END_DATE'
182 )
183 )
184 period_end_date,
185 effective_date effective_date, business_group_id bg_id
186 FROM pay_payroll_actions
187 WHERE payroll_action_id = p_payroll_action_id;
188
189 lr_parameter_info csr_parameter_info%ROWTYPE;
190 l_proc VARCHAR2 (240)
191 := g_package
192 || ' GET_ALL_PARAMETERS ';
193 --
194 BEGIN
195 OPEN csr_parameter_info (p_payroll_action_id);
196 --FETCH csr_parameter_info into lr_parameter_info;
197 FETCH csr_parameter_info INTO p_archive,
198 p_trade_union_id,
199 p_legal_employer_id,
200 p_local_unit_id,
201 p_period,
202 p_period_end_date,
203 p_effective_date,
204 p_business_group_id;
205 CLOSE csr_parameter_info;
206
207
208 IF g_debug
209 THEN
210 hr_utility.set_location (
211 ' Leaving Procedure GET_ALL_PARAMETERS',
212 30
213 );
214 END IF;
215 END get_all_parameters;
216
217 /* RANGE CODE */
218 PROCEDURE range_code (
219 p_payroll_action_id IN NUMBER,
220 p_sql OUT NOCOPY VARCHAR2
221 )
222 IS
223 l_action_info_id NUMBER;
224 l_ovn NUMBER;
225 l_business_group_id NUMBER;
226 l_start_date VARCHAR2 (30);
227 l_end_date VARCHAR2 (30);
228 l_effective_date DATE;
229 l_consolidation_set NUMBER;
230 l_defined_balance_id NUMBER := 0;
231 l_count NUMBER := 0;
232 l_prev_prepay NUMBER := 0;
233 l_canonical_start_date DATE;
234 l_canonical_end_date DATE;
235 l_payroll_id NUMBER;
236 l_prepay_action_id NUMBER;
237 l_actid NUMBER;
238 l_assignment_id NUMBER;
239 l_trade_union_number NUMBER;
240 l_y_number VARCHAR2 (30);
241 l_local_unit_id_fetched NUMBER;
242 l_accounting_id NUMBER;
243 l_action_sequence NUMBER;
244 l_assact_id NUMBER;
245 l_pact_id NUMBER;
246 l_flag NUMBER := 0;
247 l_element_context VARCHAR2 (5);
248
249 CURSOR csr_trade_union_details (
250 csr_v_trade_union_id hr_organization_information.organization_id%TYPE
251 )
252 IS
253 SELECT hou.NAME, hoi.org_information1, hoi.org_information5
254 FROM hr_organization_information hoi, hr_organization_units hou
255 WHERE org_information_context = 'FI_TRADE_UNION_DETAILS'
256 AND hou.organization_id = csr_v_trade_union_id
257 AND hoi.organization_id = hou.organization_id;
258
259 lr_trade_union_details csr_trade_union_details%ROWTYPE;
260
261 CURSOR csr_legal_employer_details (
262 csr_v_legal_employer_id hr_organization_information.organization_id%TYPE
263 )
264 IS
265 SELECT hou.NAME, hoi.org_information1, hoi.org_information8
266 FROM hr_organization_information hoi, hr_organization_units hou
267 WHERE org_information_context = 'FI_LEGAL_EMPLOYER_DETAILS'
268 AND hoi.organization_id = hou.organization_id
269 AND hou.organization_id = csr_v_legal_employer_id;
270
271 lr_legal_employer_details csr_legal_employer_details%ROWTYPE;
272
273 CURSOR csr_all_local_unit_details (
274 csr_v_legal_employer_id hr_organization_information.organization_id%TYPE
275 )
276 IS
277 SELECT hoi_le.org_information1 local_unit_id,
278 hou_lu.NAME local_unit_name,
279 hoi_lu.org_information1 y_spare_number,
280 hoi_lu.org_information2 local_unit_number
281 FROM hr_organization_units hou_le,
282 hr_organization_information hoi_le,
283 hr_organization_units hou_lu,
284 hr_organization_information hoi_lu
285 WHERE hoi_le.organization_id = hou_le.organization_id
286 AND hou_le.organization_id = csr_v_legal_employer_id
287 AND hoi_le.org_information_context = 'FI_LOCAL_UNITS'
288 AND hou_lu.organization_id = hoi_le.org_information1
289 AND hou_lu.organization_id = hoi_lu.organization_id
290 AND hoi_lu.org_information_context = 'FI_LOCAL_UNIT_DETAILS';
291
292 lr_all_local_unit_details csr_all_local_unit_details%ROWTYPE;
293
294 CURSOR csr_local_unit_details (
295 csr_v_local_unit_id hr_organization_information.organization_id%TYPE
296 )
297 IS
298 SELECT hou.NAME, hoi.org_information1 y_spare_number,
299 hoi.org_information2 local_unit_number
300 FROM hr_organization_information hoi, hr_organization_units hou
301 WHERE org_information_context = 'FI_LOCAL_UNIT_DETAILS'
302 AND hoi.organization_id = hou.organization_id
303 AND hou.organization_id = csr_v_local_unit_id;
304
305 lr_local_unit_details csr_local_unit_details%ROWTYPE;
306 BEGIN
307 IF g_debug
308 THEN
309 hr_utility.set_location (' Entering Procedure RANGE_CODE', 40);
310 END IF;
311
312 fnd_file.put_line (fnd_file.LOG, 'Entering Procedure RANGE_CODE 7');
313 p_sql :=
314 'SELECT DISTINCT person_id
315 FROM per_people_f ppf
316 ,pay_payroll_actions ppa
317 WHERE ppa.payroll_action_id = :payroll_action_id
318 AND ppa.business_group_id = ppf.business_group_id
319 ORDER BY ppf.person_id';
320 pay_fi_archive_umfr.get_all_parameters (
321 p_payroll_action_id,
322 g_business_group_id,
323 g_effective_date,
324 g_trade_union_id,
325 g_legal_employer_id,
326 g_local_unit_id,
327 g_period,
328 g_period_end_date,
329 g_archive
330 );
331
332 IF g_archive = 'Y'
333 THEN
334 OPEN csr_trade_union_details (g_trade_union_id);
335 FETCH csr_trade_union_details INTO lr_trade_union_details;
336 CLOSE csr_trade_union_details;
337 l_trade_union_number := lr_trade_union_details.org_information1;
338 l_accounting_id := lr_trade_union_details.org_information5;
339 -- Pick up the details belonging to Legal Employer Details
340
341 OPEN csr_legal_employer_details (g_legal_employer_id);
342 fnd_file.put_line (fnd_file.LOG, '1');
343 FETCH csr_legal_employer_details INTO lr_legal_employer_details;
344 fnd_file.put_line (fnd_file.LOG, '2');
345 CLOSE csr_legal_employer_details;
346 fnd_file.put_line (fnd_file.LOG, '3');
347 l_y_number := lr_legal_employer_details.org_information1;
348
349 IF g_local_unit_id IS NOT NULL
350 THEN
351 OPEN csr_local_unit_details (g_local_unit_id);
352 FETCH csr_local_unit_details INTO lr_local_unit_details;
353 CLOSE csr_local_unit_details;
354 pay_action_information_api.create_action_information (
355 p_action_information_id=> l_action_info_id,
356 p_action_context_id=> p_payroll_action_id,
357 p_action_context_type=> 'PA',
358 p_object_version_number=> l_ovn,
359 p_effective_date=> g_effective_date,
360 p_source_id=> NULL,
361 p_source_text=> NULL,
362 p_action_information_category=> 'EMEA REPORT INFORMATION',
363 p_action_information1=> 'PYFIUMFR',
364 p_action_information2=> 'LU',
365 p_action_information3=> g_local_unit_id,
366 p_action_information4=> lr_local_unit_details.NAME,
367 p_action_information5=> lr_local_unit_details.y_spare_number,
368 p_action_information6=> lr_local_unit_details.local_unit_number,
369 p_action_information7=> NULL,
370 p_action_information8=> NULL,
371 p_action_information9=> NULL,
372 p_action_information10=> NULL,
373 p_action_information11=> NULL,
374 p_action_information12=> NULL,
375 p_action_information13=> NULL,
376 p_action_information14=> NULL,
377 p_action_information15=> NULL,
378 p_action_information16=> NULL,
379 p_action_information17=> NULL,
380 p_action_information18=> NULL,
381 p_action_information19=> NULL,
382 p_action_information20=> NULL,
383 p_action_information21=> NULL,
384 p_action_information22=> NULL,
385 p_action_information23=> NULL,
386 p_action_information24=> NULL,
387 p_action_information25=> NULL,
388 p_action_information26=> NULL,
389 p_action_information27=> NULL --date from srs req
390 ,
391 p_action_information28=> NULL,
392 p_action_information29=> NULL,
393 p_action_information30=> NULL
394 );
395 ELSE
396 FOR lr_all_local_unit_details IN
397 csr_all_local_unit_details (g_legal_employer_id)
398 LOOP
399 pay_action_information_api.create_action_information (
400 p_action_information_id=> l_action_info_id,
401 p_action_context_id=> p_payroll_action_id,
402 p_action_context_type=> 'PA',
403 p_object_version_number=> l_ovn,
404 p_effective_date=> g_effective_date,
405 p_source_id=> NULL,
406 p_source_text=> NULL,
407 p_action_information_category=> 'EMEA REPORT INFORMATION',
408 p_action_information1=> 'PYFIUMFR',
409 p_action_information2=> 'LU',
410 p_action_information3=> lr_all_local_unit_details.local_unit_id,
411 p_action_information4=> lr_all_local_unit_details.local_unit_name,
412 p_action_information5=> lr_all_local_unit_details.y_spare_number,
413 p_action_information6=> lr_all_local_unit_details.local_unit_number,
414 p_action_information7=> NULL,
415 p_action_information8=> NULL,
416 p_action_information9=> NULL,
417 p_action_information10=> NULL,
418 p_action_information11=> NULL,
419 p_action_information12=> NULL,
420 p_action_information13=> NULL,
421 p_action_information14=> NULL,
422 p_action_information15=> NULL,
423 p_action_information16=> NULL,
424 p_action_information17=> NULL,
425 p_action_information18=> NULL,
426 p_action_information19=> NULL,
427 p_action_information20=> NULL,
428 p_action_information21=> NULL,
429 p_action_information22=> NULL,
430 p_action_information23=> NULL,
431 p_action_information24=> NULL,
432 p_action_information25=> NULL,
433 p_action_information26=> NULL,
434 p_action_information27=> NULL --date from srs req
435 ,
436 p_action_information28=> NULL,
437 p_action_information29=> NULL,
438 p_action_information30=> NULL
439 );
440 END LOOP;
441 END IF;
442
443 pay_action_information_api.create_action_information (
444 p_action_information_id=> l_action_info_id,
445 p_action_context_id=> p_payroll_action_id,
446 p_action_context_type=> 'PA',
447 p_object_version_number=> l_ovn,
448 p_effective_date=> g_effective_date,
449 p_source_id=> NULL,
450 p_source_text=> NULL,
451 p_action_information_category=> 'EMEA REPORT DETAILS',
452 p_action_information1=> 'PYFIUMFR',
453 p_action_information2=> lr_trade_union_details.NAME,
454 p_action_information3=> g_trade_union_id,
455 p_action_information4=> lr_legal_employer_details.NAME,
456 p_action_information5=> g_legal_employer_id,
457 p_action_information6=> lr_local_unit_details.NAME,
458 p_action_information7=> g_local_unit_id,
459 p_action_information8=> g_period,
460 p_action_information9=> fnd_date.date_to_canonical (
461 g_period_end_date
462 ),
463 p_action_information10=> NULL,
464 p_action_information11=> NULL,
465 p_action_information12=> NULL,
466 p_action_information13=> NULL,
467 p_action_information14=> NULL,
468 p_action_information15=> NULL,
469 p_action_information16=> NULL,
470 p_action_information17=> NULL,
471 p_action_information18=> NULL,
472 p_action_information19=> NULL,
473 p_action_information20=> NULL,
474 p_action_information21=> NULL,
475 p_action_information22=> NULL,
476 p_action_information23=> NULL,
477 p_action_information24=> NULL,
478 p_action_information25=> NULL,
479 p_action_information26=> NULL,
480 p_action_information27=> NULL,
481 p_action_information28=> NULL,
482 p_action_information29=> NULL,
483 p_action_information30=> NULL
484 );
485 pay_action_information_api.create_action_information (
486 p_action_information_id=> l_action_info_id,
487 p_action_context_id=> p_payroll_action_id,
488 p_action_context_type=> 'PA',
489 p_object_version_number=> l_ovn,
490 p_effective_date=> g_effective_date,
491 p_source_id=> NULL,
492 p_source_text=> NULL,
493 p_action_information_category=> 'EMEA REPORT INFORMATION',
494 p_action_information1=> 'PYFIUMFR',
495 p_action_information2=> 'LE',
496 p_action_information3=> g_legal_employer_id,
497 p_action_information4=> lr_legal_employer_details.NAME,
498 p_action_information5=> l_y_number,
499 p_action_information6=> NULL,
500 p_action_information7=> NULL,
501 p_action_information8=> NULL,
502 p_action_information9=> NULL,
503 p_action_information10=> NULL,
504 p_action_information11=> NULL,
505 p_action_information12=> NULL,
506 p_action_information13=> NULL,
507 p_action_information14=> NULL,
508 p_action_information15=> NULL,
509 p_action_information16=> NULL,
510 p_action_information17=> NULL,
511 p_action_information18=> NULL,
512 p_action_information19=> NULL,
513 p_action_information20=> NULL,
514 p_action_information21=> NULL,
515 p_action_information22=> NULL,
516 p_action_information23=> NULL,
517 p_action_information24=> NULL,
518 p_action_information25=> NULL,
519 p_action_information26=> NULL,
520 p_action_information27=> NULL,
521 p_action_information28=> NULL,
522 p_action_information29=> NULL,
523 p_action_information30=> NULL
524 );
525 pay_action_information_api.create_action_information (
526 p_action_information_id=> l_action_info_id,
527 p_action_context_id=> p_payroll_action_id,
528 p_action_context_type=> 'PA',
529 p_object_version_number=> l_ovn,
530 p_effective_date=> g_effective_date,
531 p_source_id=> NULL,
532 p_source_text=> NULL,
533 p_action_information_category=> 'EMEA REPORT INFORMATION',
534 p_action_information1=> 'PYFIUMFR',
535 p_action_information2=> 'TU',
536 p_action_information3=> g_trade_union_id,
537 p_action_information4=> lr_trade_union_details.NAME,
538 p_action_information5=> l_trade_union_number,
539 p_action_information6=> l_accounting_id,
540 p_action_information7=> NULL,
541 p_action_information8=> NULL,
542 p_action_information9=> NULL,
543 p_action_information10=> NULL,
544 p_action_information11=> NULL,
545 p_action_information12=> NULL,
546 p_action_information13=> NULL,
547 p_action_information14=> NULL,
548 p_action_information15=> NULL,
549 p_action_information16=> NULL,
550 p_action_information17=> NULL,
551 p_action_information18=> NULL,
552 p_action_information19=> NULL,
553 p_action_information20=> NULL,
554 p_action_information21=> NULL,
555 p_action_information22=> NULL,
556 p_action_information23=> NULL,
557 p_action_information24=> NULL,
558 p_action_information25=> NULL,
559 p_action_information26=> NULL,
560 p_action_information27=> NULL,
561 p_action_information28=> NULL,
562 p_action_information29=> NULL,
563 p_action_information30=> NULL
564 );
565 END IF; --archiving=yes
566
567 IF g_debug
568 THEN
569 hr_utility.set_location (' Leaving Procedure RANGE_CODE', 50);
570 END IF;
571 EXCEPTION
572 WHEN OTHERS
573 THEN
574 -- Return cursor that selects no rows
575 p_sql :=
576 'select 1 from dual where to_char(:payroll_action_id) = dummy';
577 END range_code;
578
579 /* ASSIGNMENT ACTION CODE */
580 PROCEDURE assignment_action_code (
581 p_payroll_action_id IN NUMBER,
582 p_start_person IN NUMBER,
583 p_end_person IN NUMBER,
584 p_chunk IN NUMBER
585 )
586 IS
587 CURSOR csr_prepaid_assignments (
588 p_payroll_action_id NUMBER,
589 p_start_person NUMBER,
590 p_end_person NUMBER,
591 p_legal_employer_id NUMBER,
592 p_local_unit_id NUMBER,
593 p_trade_union_id NUMBER,
594 l_period_start_date DATE,
595 l_period_end_date DATE,
596 l_bussiness_group_id NUMBER,
597 p_chunk NUMBER
598 )
599 IS
600
601 SELECT act.assignment_id assignment_id,
602 act.assignment_action_id run_action_id,
603 act1.assignment_action_id prepaid_action_id
604 FROM pay_payroll_actions ppa
605 ,pay_payroll_actions appa
606 ,pay_payroll_actions appa2
607 ,pay_assignment_actions act
608 ,pay_assignment_actions act1
609 ,pay_action_interlocks pai
610 ,per_all_assignments_f as1
611 ,hr_soft_coding_keyflex hsck
612 ,pay_run_result_values TARGET
613 ,pay_run_results RR
614 ,pay_element_entries_f PEEF
615 ,pay_element_types_f PETF
616 , pay_input_values_f PIV
617 , per_all_people_f pap
618 WHERE ppa.payroll_action_id = p_payroll_action_id
619 AND appa.effective_date BETWEEN l_period_start_date
620 AND l_period_end_date
621 AND as1.person_id BETWEEN p_start_person
622 AND p_end_person
623 AND appa.action_type IN ('R','Q')
624 -- Payroll Run or Quickpay Run
625 AND act.payroll_action_id = appa.payroll_action_id
626 AND act.source_action_id IS NULL -- Master Action
627 AND as1.assignment_id = act.assignment_id
628 AND as1.person_id = pap.person_id
629 AND pap.per_information9 =
630 TO_CHAR (p_trade_union_id)
631 AND ppa.effective_date BETWEEN as1.effective_start_date
632 AND as1.effective_end_date
633 AND act.action_status IN ('C','S') -- 10229501
634 AND act.assignment_action_id = pai.locked_action_id
635 AND act1.assignment_action_id = pai.locking_action_id
636 AND act1.action_status IN ('C','S') -- 10229501
637 AND act1.payroll_action_id = appa2.payroll_action_id
638 AND appa2.action_type IN ('P','U')
639 AND appa2.effective_date BETWEEN l_period_start_date
640 AND l_period_end_date
641 -- Prepayments or Quickpay Prepayments
642 AND ( p_local_unit_id IS NULL
643 OR ( p_local_unit_id IS NOT NULL
644 AND hsck.segment2 = TO_CHAR (p_local_unit_id)
645 )
646 )
647 AND hsck.SOFT_CODING_KEYFLEX_ID=as1.SOFT_CODING_KEYFLEX_ID
648 -- AND hsck.segment2 = p_local_unit_id
649 AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
650 AND act.TAX_UNIT_ID = p_legal_employer_id
651 and TARGET.run_result_id = RR.run_result_id
652 AND (( RR.assignment_action_id
653 in ( Select act2.assignment_action_id
654 from pay_assignment_actions act2
655 Where act2.source_action_id=act.assignment_action_id
656 AND act2.action_status = 'C' -- Completed
657 AND act2.payroll_action_id = act.payroll_action_id))
658 or
659 (RR.assignment_action_id=act.assignment_action_id))
660 and RR.status in ('P','PA')
661 and PEEF.element_entry_id = RR.element_entry_id
662 and PEEF.element_type_id = RR.element_type_id
663 and PEEF.element_type_id = PETF.element_type_id
664 and PETF.legislation_code ='FI'
665 and PETF.element_name = 'Trade Union Membership Fees'
666 and PIV.element_type_id = PETF.element_type_id
667 and PIV.input_value_id = TARGET.input_value_id
668 and PIV.name='Third Party Payee'
669 and TARGET.result_value = to_char(p_trade_union_id)
670 and act.assignment_id IN
671 (SELECT MIN(act.assignment_id)
672 FROM pay_payroll_actions ppa
673 ,pay_payroll_actions appa
674 ,pay_payroll_actions appa2
675 ,pay_assignment_actions act
676 ,pay_assignment_actions act1
677 ,pay_action_interlocks pai
678 ,per_all_assignments_f as1
679 ,hr_soft_coding_keyflex hsck
680 ,pay_run_result_values TARGET
681 ,pay_run_results RR
682 ,pay_element_entries_f PEEF
683 ,pay_element_types_f PETF
684 , pay_input_values_f PIV
685 , per_all_people_f pap
686 WHERE ppa.payroll_action_id = p_payroll_action_id
687 AND appa.effective_date BETWEEN l_period_start_date
688 AND l_period_end_date
689 AND as1.person_id BETWEEN p_start_person
690 AND p_end_person
691 AND appa.action_type IN ('R','Q')
692 -- Payroll Run or Quickpay Run
693 AND act.payroll_action_id = appa.payroll_action_id
694 AND act.source_action_id IS NULL -- Master Action
695 AND as1.assignment_id = act.assignment_id
696 AND as1.person_id = pap.person_id
697 AND pap.per_information9 =
698 TO_CHAR (p_trade_union_id)
699 AND ppa.effective_date BETWEEN as1.effective_start_date
700 AND as1.effective_end_date
701 AND act.action_status IN ('C','S') -- 10229501
702 AND act.assignment_action_id = pai.locked_action_id
703 AND act1.assignment_action_id = pai.locking_action_id
704 AND act1.action_status IN ('C','S') -- 10229501
705 AND act1.payroll_action_id = appa2.payroll_action_id
706 AND appa2.action_type IN ('P','U')
707 AND appa2.effective_date BETWEEN l_period_start_date
708 AND l_period_end_date
709 -- Prepayments or Quickpay Prepayments
710 AND hsck.SOFT_CODING_KEYFLEX_ID=as1.SOFT_CODING_KEYFLEX_ID
711 AND ( p_local_unit_id IS NULL
712 OR ( p_local_unit_id IS NOT NULL
713 AND hsck.segment2 = TO_CHAR (p_local_unit_id)
714 )
715 )
716 -- AND hsck.segment2 = p_local_unit_id
717 AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
718 AND act.TAX_UNIT_ID = p_legal_employer_id
719 and TARGET.run_result_id = RR.run_result_id
720 AND (( RR.assignment_action_id
721 in ( Select act2.assignment_action_id
722 from pay_assignment_actions act2
723 Where act2.source_action_id=act.assignment_action_id
724 AND act2.action_status = 'C' -- Completed
725 AND act2.payroll_action_id = act.payroll_action_id))
726 or
727 (RR.assignment_action_id=act.assignment_action_id))
728 and RR.status in ('P','PA')
729 and PEEF.element_entry_id = RR.element_entry_id
730 and PEEF.element_type_id = RR.element_type_id
731 and PEEF.element_type_id = PETF.element_type_id
732 and PETF.legislation_code ='FI'
733 and PETF.element_name = 'Trade Union Membership Fees'
734 and PIV.element_type_id = PETF.element_type_id
735 and PIV.input_value_id = TARGET.input_value_id
736 and PIV.name='Third Party Payee'
737 and TARGET.result_value = to_char(p_trade_union_id)
738 GROUP BY as1.person_id
739 )
740 and (act.assignment_id ,act.assignment_action_id ) IN
741 (SELECT act.assignment_id , max(act.assignment_action_id )
742 FROM pay_payroll_actions ppa
743 ,pay_payroll_actions appa
744 ,pay_payroll_actions appa2
745 ,pay_assignment_actions act
746 ,pay_assignment_actions act1
747 ,pay_action_interlocks pai
748 ,per_all_assignments_f as1
749 ,hr_soft_coding_keyflex hsck
750 ,pay_run_result_values TARGET
751 ,pay_run_results RR
752 ,pay_element_entries_f PEEF
753 ,pay_element_types_f PETF
754 , pay_input_values_f PIV
755 , per_all_people_f pap
756 WHERE ppa.payroll_action_id = p_payroll_action_id
757 AND appa.effective_date BETWEEN l_period_start_date
758 AND l_period_end_date
759 AND as1.person_id BETWEEN p_start_person
760 AND p_end_person
761 AND appa.action_type IN ('R','Q')
762 -- Payroll Run or Quickpay Run
763 AND act.payroll_action_id = appa.payroll_action_id
764 AND act.source_action_id IS NULL -- Master Action
765 AND as1.assignment_id = act.assignment_id
766 AND as1.person_id = pap.person_id
767 AND pap.per_information9 =
768 TO_CHAR (p_trade_union_id)
769 AND ppa.effective_date BETWEEN as1.effective_start_date
770 AND as1.effective_end_date
771 AND act.action_status IN ('C','S') -- 10229501
772 AND act.assignment_action_id = pai.locked_action_id
773 AND act1.assignment_action_id = pai.locking_action_id
774 AND act1.action_status IN ('C','S') -- 10229501
775 AND act1.payroll_action_id = appa2.payroll_action_id
776 AND appa2.action_type IN ('P','U')
777 AND appa2.effective_date BETWEEN l_period_start_date
778 AND l_period_end_date
779 AND hsck.SOFT_CODING_KEYFLEX_ID=as1.SOFT_CODING_KEYFLEX_ID
780 AND ( p_local_unit_id IS NULL
781 OR ( p_local_unit_id IS NOT NULL
782 AND hsck.segment2 = TO_CHAR (p_local_unit_id)
783 )
784 )
785 AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
786 AND act.TAX_UNIT_ID = p_legal_employer_id
787 and TARGET.run_result_id = RR.run_result_id
788 AND (( RR.assignment_action_id
789 in ( Select act2.assignment_action_id
790 from pay_assignment_actions act2
791 Where act2.source_action_id=act.assignment_action_id
792 AND act2.action_status = 'C' -- Completed
793 AND act2.payroll_action_id = act.payroll_action_id))
794 or
795 (RR.assignment_action_id=act.assignment_action_id))
796 and RR.status in ('P','PA')
797 and PEEF.element_entry_id = RR.element_entry_id
798 and PEEF.element_type_id = RR.element_type_id
799 and PEEF.element_type_id = PETF.element_type_id
800 and PETF.legislation_code ='FI'
801 and PETF.element_name = 'Trade Union Membership Fees'
802 and PIV.element_type_id = PETF.element_type_id
803 and PIV.input_value_id = TARGET.input_value_id
804 and PIV.name='Third Party Payee'
805 and TARGET.result_value = to_char(p_trade_union_id)
806 GROUP BY act.assignment_id
807 )
808 ORDER BY act.assignment_id;
809
810
811
812
813
814
815 l_count NUMBER := 0;
816 l_prev_prepay NUMBER := 0;
817 l_start_date VARCHAR2 (20);
818 l_end_date VARCHAR2 (20);
819 l_canonical_start_date DATE;
820 l_canonical_end_date DATE;
821 l_payroll_id NUMBER;
822 l_consolidation_set NUMBER;
823 l_prepay_action_id NUMBER;
824 l_actid NUMBER;
825 l_assignment_id NUMBER;
826 l_action_sequence NUMBER;
827 l_assact_id NUMBER;
828 l_pact_id NUMBER;
829 l_flag NUMBER := 0;
830 l_defined_balance_id NUMBER := 0;
831 l_action_info_id NUMBER;
832 l_ovn NUMBER;
833 -- User pARAMETERS needed
834 l_business_group_id NUMBER;
835 l_effective_date DATE;
836 l_trade_union_id NUMBER;
837 l_legal_employer_id NUMBER;
838 l_local_unit_id NUMBER;
839 l_reporting_date DATE;
840 l_period VARCHAR2 (240);
841 l_period_start_date DATE;
842 l_period_end_date DATE;
843 -- End of User pARAMETERS needed
844 l_assignment number; --
845 BEGIN
846 IF g_debug
847 THEN
848 hr_utility.set_location (
849 ' Entering Procedure ASSIGNMENT_ACTION_CODE',
850 60
851 );
852 END IF;
853
854 pay_fi_archive_umfr.get_all_parameters (
855 p_payroll_action_id,
856 g_business_group_id,
857 g_effective_date,
858 g_trade_union_id,
859 g_legal_employer_id,
860 g_local_unit_id,
861 g_period,
862 g_period_end_date,
863 g_archive
864 );
865
866 fnd_file.put_line ( fnd_file.LOG, g_legal_employer_id ||'g_legal_employer_id');
867 fnd_file.put_line ( fnd_file.LOG, p_start_person ||'p_start_person');
868 fnd_file.put_line ( fnd_file.LOG, p_end_person ||'p_end_person');
869 fnd_file.put_line ( fnd_file.LOG, g_local_unit_id ||'g_local_unit_id');
870 fnd_file.put_line ( fnd_file.LOG, g_trade_union_id ||'g_trade_union_id');
871 fnd_file.put_line ( fnd_file.LOG, g_period_start_date ||'g_period_start_date');
872 fnd_file.put_line ( fnd_file.LOG, g_period_start_date ||'g_period_start_date');
873 fnd_file.put_line ( fnd_file.LOG, g_period_start_date ||'g_period_start_date');
874
875 IF g_archive = 'Y'
876 THEN
877 l_prepay_action_id := 0;
878 l_assignment:=0;
879 fnd_file.put_line (fnd_file.LOG, ' Before the Locking Cursor ');
880
881 SELECT DECODE (
882 g_period,
883 'MONTH', TRUNC (g_period_end_date, 'MM'),
884 'BIMONTH', TRUNC (
885 ADD_MONTHS (
886 g_period_end_date,
887 MOD (
888 TO_NUMBER (
889 TO_CHAR (g_period_end_date, 'MM')
890 ),
891 2
892 )
893 - 1
894 ),
895 'MM'
896 ),
897 'BIWEEK', g_period_end_date - 14,
898 'QUARTER', TRUNC (g_period_end_date, 'Q')
899 )
900 INTO g_period_start_date
901 FROM DUAL;
902
903 fnd_file.put_line (
904 fnd_file.LOG,
905 'G_PERIOD_start_DATE '
906 || g_period_start_date
907 );
908
909 -- this is for all the person's assignment actionid under the selected legal employer
910 FOR rec_prepaid_assignments IN
911 csr_prepaid_assignments (
912 p_payroll_action_id,
913 p_start_person,
914 p_end_person,
915 g_legal_employer_id,
916 g_local_unit_id,
917 g_trade_union_id,
918 g_period_start_date,
919 g_period_end_date,
920 g_business_group_id,
921 p_chunk
922 )
923 LOOP
924
925 -- IF l_prepay_action_id <>
926 -- rec_prepaid_assignments.prepaid_action_id --pp
927 if l_assignment <> rec_prepaid_assignments.assignment_id then
928 -- THEN
929 SELECT pay_assignment_actions_s.NEXTVAL
930 INTO l_actid
931 FROM DUAL;
932
933 --
934 g_index_assact := g_index_assact
935 + 1;
936 g_lock_table (g_index_assact).archive_assact_id := l_actid;
937
938 -- Create the archive assignment action
939 fnd_file.put_line (
940 fnd_file.LOG,
941 'l_actid'
942 || l_actid
943 || ' rec_prepaid_assignments.assignment_id'
944 || rec_prepaid_assignments.assignment_id
945 || ' p_chunk'
946 || p_chunk
947 );
948 hr_nonrun_asact.insact (
949 l_actid,
950 rec_prepaid_assignments.assignment_id,
951 p_payroll_action_id,
952 p_chunk,
953 NULL
954 );
955 -- Create archive to prepayment assignment action interlock
956 --
957 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.prepaid_action_id);
958 END IF;
959
960 -- create archive to master assignment action interlock
961 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.run_action_id);
962 -- l_prepay_action_id := rec_prepaid_assignments.prepaid_action_id; ---pp
963 l_assignment:= rec_prepaid_assignments.assignment_id;
964 END LOOP;
965 END IF; --ARCHIVE
966
967 fnd_file.put_line (
968 fnd_file.LOG,
969 ' After Ending Assignment Act Code the Locking Cursor '
970 );
971
972 IF g_debug
973 THEN
974 hr_utility.set_location (
975 ' Leaving Procedure ASSIGNMENT_ACTION_CODE',
976 70
977 );
978 END IF;
979 END assignment_action_code;
980
981
982 PROCEDURE initialization_code (p_payroll_action_id IN NUMBER)
983 IS
984 CURSOR csr_prepay_id
985 IS
986 SELECT DISTINCT prepay_payact.payroll_action_id prepay_payact_id,
987 run_payact.date_earned date_earned
988 FROM pay_action_interlocks archive_intlck,
989 pay_assignment_actions prepay_assact,
990 pay_payroll_actions prepay_payact,
991 pay_action_interlocks prepay_intlck,
992 pay_assignment_actions run_assact,
993 pay_payroll_actions run_payact,
994 pay_assignment_actions archive_assact
995 WHERE archive_intlck.locking_action_id =
996 archive_assact.assignment_action_id
997 AND archive_assact.payroll_action_id =
998 p_payroll_action_id
999 AND prepay_assact.assignment_action_id =
1000 archive_intlck.locked_action_id
1001 AND prepay_payact.payroll_action_id =
1002 prepay_assact.payroll_action_id
1003 AND prepay_payact.action_type IN ('U', 'P')
1004 AND prepay_intlck.locking_action_id =
1005 prepay_assact.assignment_action_id
1006 AND run_assact.assignment_action_id =
1007 prepay_intlck.locked_action_id
1008 AND run_payact.payroll_action_id =
1009 run_assact.payroll_action_id
1010 AND run_payact.action_type IN ('Q', 'R')
1011 ORDER BY prepay_payact.payroll_action_id;
1012
1013
1014 CURSOR csr_runact_id
1015 IS
1016 SELECT DISTINCT prepay_payact.payroll_action_id prepay_payact_id,
1017 run_payact.date_earned date_earned,
1018 run_payact.payroll_action_id run_payact_id
1019 FROM pay_action_interlocks archive_intlck,
1020 pay_assignment_actions prepay_assact,
1021 pay_payroll_actions prepay_payact,
1022 pay_action_interlocks prepay_intlck,
1023 pay_assignment_actions run_assact,
1024 pay_payroll_actions run_payact,
1025 pay_assignment_actions archive_assact
1026 WHERE archive_intlck.locking_action_id =
1027 archive_assact.assignment_action_id
1028 AND archive_assact.payroll_action_id =
1029 p_payroll_action_id
1030 AND prepay_assact.assignment_action_id =
1031 archive_intlck.locked_action_id
1032 AND prepay_payact.payroll_action_id =
1033 prepay_assact.payroll_action_id
1034 AND prepay_payact.action_type IN ('U', 'P')
1035 AND prepay_intlck.locking_action_id =
1036 prepay_assact.assignment_action_id
1037 AND run_assact.assignment_action_id =
1038 prepay_intlck.locked_action_id
1039 AND run_payact.payroll_action_id =
1040 run_assact.payroll_action_id
1041 AND run_payact.action_type IN ('Q', 'R')
1042 ORDER BY prepay_payact.payroll_action_id;
1043
1044 rec_prepay_id csr_prepay_id%ROWTYPE;
1045 rec_runact_id csr_runact_id%ROWTYPE;
1046 l_action_info_id NUMBER;
1047 l_ovn NUMBER;
1048 l_count NUMBER := 0;
1049 l_business_group_id NUMBER;
1050 l_start_date VARCHAR2 (20);
1051 l_end_date VARCHAR2 (20);
1052 l_effective_date DATE;
1053 l_payroll_id NUMBER;
1054 l_consolidation_set NUMBER;
1055 l_prev_prepay NUMBER := 0;
1056 BEGIN
1057 IF g_debug
1058 THEN
1059 hr_utility.set_location (
1060 ' Entering Procedure INITIALIZATION_CODE',
1061 80
1062 );
1063 END IF;
1064
1065 fnd_file.put_line (fnd_file.LOG, 'In INIT_CODE 0');
1066
1067
1068 IF g_debug
1069 THEN
1070 hr_utility.set_location (
1071 ' Leaving Procedure INITIALIZATION_CODE',
1072 90
1073 );
1074 END IF;
1075 EXCEPTION
1076 WHEN OTHERS
1077 THEN
1078 g_err_num := SQLCODE;
1079
1080 IF g_debug
1081 THEN
1082 hr_utility.set_location (
1083 'ORA_ERR: '
1084 || g_err_num
1085 || 'In INITIALIZATION_CODE',
1086 180
1087 );
1088 END IF;
1089 END initialization_code;
1090
1091 /* GET COUNTRY NAME FROM CODE */
1092 FUNCTION get_country_name (p_territory_code VARCHAR2)
1093 RETURN VARCHAR2
1094 IS
1095 CURSOR csr_get_territory_name (p_territory_code VARCHAR2)
1096 IS
1097 SELECT territory_short_name
1098 FROM fnd_territories_vl
1099 WHERE territory_code = p_territory_code;
1100
1101 l_country fnd_territories_vl.territory_short_name%TYPE;
1102 BEGIN
1103 IF g_debug
1104 THEN
1105 hr_utility.set_location (' Entering Function GET_COUNTRY_NAME', 140);
1106 END IF;
1107
1108 OPEN csr_get_territory_name (p_territory_code);
1109 FETCH csr_get_territory_name INTO l_country;
1110 CLOSE csr_get_territory_name;
1111 RETURN l_country;
1112
1113 IF g_debug
1114 THEN
1115 hr_utility.set_location (' Leaving Function GET_COUNTRY_NAME', 150);
1116 END IF;
1117 END get_country_name;
1118
1119 /* GET DEFINED BALANCE ID */
1120 FUNCTION get_defined_balance_id (p_user_name IN VARCHAR2)
1121 RETURN NUMBER
1122 IS
1123 /* Cursor to retrieve Defined Balance Id */
1124 CURSOR csr_def_bal_id (p_user_name VARCHAR2)
1125 IS
1126 SELECT u.creator_id
1127 FROM ff_user_entities u, ff_database_items d
1128 WHERE d.user_name = p_user_name
1129 AND u.user_entity_id = d.user_entity_id
1130 AND (u.legislation_code = 'FI')
1131 AND (u.business_group_id IS NULL)
1132 AND u.creator_type = 'B';
1133
1134 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
1135 BEGIN
1136 IF g_debug
1137 THEN
1138 hr_utility.set_location (
1139 ' Entering Function GET_DEFINED_BALANCE_ID',
1140 240
1141 );
1142 END IF;
1143
1144 OPEN csr_def_bal_id (p_user_name);
1145 FETCH csr_def_bal_id INTO l_defined_balance_id;
1146 CLOSE csr_def_bal_id;
1147 RETURN l_defined_balance_id;
1148
1149 IF g_debug
1150 THEN
1151 hr_utility.set_location (
1152 ' Leaving Function GET_DEFINED_BALANCE_ID',
1153 250
1154 );
1155 END IF;
1156 END get_defined_balance_id;
1157
1158 /* ARCHIVE CODE */
1159 PROCEDURE archive_code (
1160 p_assignment_action_id IN NUMBER,
1161 p_effective_date IN DATE
1162 )
1163 IS
1164 /* Cursor to retrieve Payroll and Prepayment related Ids for Archival */
1165 CURSOR csr_archive_ids (p_locking_action_id NUMBER)
1166 IS
1167 SELECT prepay_assact.assignment_action_id prepay_assact_id,
1168 prepay_assact.assignment_id prepay_assgt_id,
1169 prepay_payact.payroll_action_id prepay_payact_id,
1170 prepay_payact.effective_date prepay_effective_date,
1171 run_assact.assignment_id run_assgt_id,
1172 run_assact.assignment_action_id run_assact_id,
1173 run_payact.payroll_action_id run_payact_id,
1174 run_payact.payroll_id payroll_id
1175 FROM pay_action_interlocks archive_intlck,
1176 pay_assignment_actions prepay_assact,
1177 pay_payroll_actions prepay_payact,
1178 pay_action_interlocks prepay_intlck,
1179 pay_assignment_actions run_assact,
1180 pay_payroll_actions run_payact
1181 WHERE archive_intlck.locking_action_id = p_locking_action_id
1182 AND prepay_assact.assignment_action_id =
1183 archive_intlck.locked_action_id
1184 AND prepay_payact.payroll_action_id =
1185 prepay_assact.payroll_action_id
1186 AND prepay_payact.action_type IN ('U', 'P')
1187 AND prepay_intlck.locking_action_id =
1188 prepay_assact.assignment_action_id
1189 AND run_assact.assignment_action_id =
1190 prepay_intlck.locked_action_id
1191 AND run_payact.payroll_action_id = run_assact.payroll_action_id
1192 AND run_payact.action_type IN ('Q', 'R')
1193 ORDER BY prepay_intlck.locking_action_id,
1194 prepay_intlck.locked_action_id DESC;
1195
1196 /* Cursor to retrieve time period information */
1197 CURSOR csr_period_end_date (p_assact_id NUMBER, p_pay_act_id NUMBER)
1198 IS
1199 SELECT ptp.end_date end_date,
1200 ptp.regular_payment_date regular_payment_date,
1201 ptp.time_period_id time_period_id,
1202 ppa.date_earned date_earned,
1203 ppa.effective_date effective_date, ptp.start_date start_date
1204 FROM per_time_periods ptp,
1205 pay_payroll_actions ppa,
1206 pay_assignment_actions paa
1207 WHERE ptp.payroll_id = ppa.payroll_id
1208 AND ppa.payroll_action_id = paa.payroll_action_id
1209 AND paa.assignment_action_id = p_assact_id
1210 AND ppa.payroll_action_id = p_pay_act_id
1211 AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date;
1212
1213 /* Cursor to retrieve Archive Payroll Action Id */
1214 CURSOR csr_archive_payact (p_assignment_action_id NUMBER)
1215 IS
1216 SELECT payroll_action_id
1217 FROM pay_assignment_actions
1218 WHERE assignment_action_id = p_assignment_action_id;
1219
1220 l_archive_payact_id NUMBER;
1221 l_record_count NUMBER;
1222 l_actid NUMBER;
1223 l_end_date per_time_periods.end_date%TYPE;
1224 l_pre_end_date per_time_periods.end_date%TYPE;
1225 l_reg_payment_date per_time_periods.regular_payment_date%TYPE;
1226 l_pre_reg_payment_date per_time_periods.regular_payment_date%TYPE;
1227 l_date_earned pay_payroll_actions.date_earned%TYPE;
1228 l_pre_date_earned pay_payroll_actions.date_earned%TYPE;
1229 l_effective_date pay_payroll_actions.effective_date%TYPE;
1230 l_pre_effective_date pay_payroll_actions.effective_date%TYPE;
1231 l_run_payact_id NUMBER;
1232 l_action_context_id NUMBER;
1233 g_archive_pact NUMBER;
1234 p_assactid NUMBER;
1235 l_time_period_id per_time_periods.time_period_id%TYPE;
1236 l_pre_time_period_id per_time_periods.time_period_id%TYPE;
1237 l_start_date per_time_periods.start_date%TYPE;
1238 l_pre_start_date per_time_periods.start_date%TYPE;
1239 l_fnd_session NUMBER := 0;
1240 l_prev_prepay NUMBER := 0;
1241 l_action_info_id pay_action_information.action_information_id%TYPE;
1242 l_ovn pay_action_information.object_version_number%TYPE;
1243 l_flag NUMBER := 0;
1244 -- The place for Variables which fetches the values to be archived
1245 l_y_number VARCHAR2 (240);
1246 l_y_number_spare NUMBER;
1247 l_accounting_id NUMBER;
1248 l_accounting_id_spare VARCHAR2 (240);
1249 l_trade_union_number NUMBER;
1250 l_local_unit_number NUMBER;
1251 l_employee_pin VARCHAR2 (240);
1252 l_employee_name VARCHAR2 (240);
1253 l_membership_start_date DATE;
1254 l_membership_end_date DATE;
1255
1256 l_amount_of_payment NUMBER;
1257 l_reason_of_payment VARCHAR2 (240) := '00'; -- 00 => Normal Membership fee
1258 l_tax_year NUMBER; -- YYYY format
1259 l_union_dues NUMBER;
1260 l_local_unit_id_fetched NUMBER;
1261
1262
1263 -- End of place for Variables which fetches the values to be archived
1264 -- The place for Cursor which fetches the values to be archived
1265
1266 -- This cursor fetches Trade Union Details
1267
1268
1269 CURSOR csr_person_details (
1270 csr_v_business_group_id per_all_people_f.business_group_id%TYPE,
1271 csr_v_local_unit_id hr_organization_information.organization_id%TYPE
1272 )
1273 IS
1274 SELECT pap.LAST_NAME || ' ' || pap.FIRST_NAME NAME, pap.national_identifier,
1275 paa.assignment_id assignment_id,
1276 pap.per_information18 membership_start_date,
1277 pap.per_information19 membership_end_date
1278 FROM per_all_people_f pap,
1279 per_all_assignments_f paa,
1280 hr_soft_coding_keyflex scl,
1281 pay_assignment_actions pasa
1282 WHERE paa.person_id = pap.person_id
1283 AND pasa.assignment_id = paa.assignment_id
1284 AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
1285 AND pap.effective_start_date <= g_period_end_date
1286 AND pap.effective_end_date >= g_period_start_date
1287 AND paa.effective_start_date <= g_period_end_date
1288 AND paa.effective_end_date >= g_period_start_date
1289 AND pap.business_group_id = csr_v_business_group_id
1290 AND scl.segment2 = csr_v_local_unit_id
1291 AND pasa.assignment_action_id = p_assignment_action_id;
1292
1293
1294 -- AND paa.primary_flag = 'Y'
1295 --
1296
1297
1298 --GROUP BY pap.person_id ;
1299
1300 lr_person_details csr_person_details%ROWTYPE;
1301
1302 CURSOR csr_get_defined_balance_id (
1303 csr_v_balance_name ff_database_items.user_name%TYPE
1304 )
1305 IS
1306 SELECT ue.creator_id
1307 FROM ff_user_entities ue, ff_database_items di
1308 WHERE di.user_name = csr_v_balance_name
1309 AND ue.user_entity_id = di.user_entity_id
1310 AND ue.legislation_code = 'FI'
1311 AND ue.business_group_id IS NULL
1312 AND ue.creator_type = 'B';
1313
1314 lr_get_defined_balance_id csr_get_defined_balance_id%ROWTYPE;
1315
1316 -- Cursor to pick up segment2
1317 CURSOR csr_get_segment2
1318 IS
1319 SELECT scl.segment2
1320 FROM per_all_assignments_f paa,
1321 hr_soft_coding_keyflex scl,
1322 pay_assignment_actions pasa
1323 WHERE pasa.assignment_action_id = p_assignment_action_id
1324 AND pasa.assignment_id = paa.assignment_id
1325 AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id;
1326
1327
1328 -- AND paa.primary_flag = 'Y';
1329
1330 lr_get_segment2 csr_get_segment2%ROWTYPE;
1331
1332 l_union_per_le VARCHAR2 (100);
1333 l_union_per_lu VARCHAR2 (100);
1334 l_negative_per_lu VARCHAR2 (100);
1335 l_negative_per_le VARCHAR2 (100);
1336 l_Sign_of_payment VARCHAR2(1);
1337 -- End of Cursors
1338
1339 -- End of place for Cursor which fetches the values to be archived
1340
1341 BEGIN
1342 IF g_debug
1343 THEN
1344 hr_utility.set_location (' Entering Procedure ARCHIVE_CODE', 380);
1345 END IF;
1346
1347 IF g_archive = 'Y'
1348 THEN
1349
1350 --
1351 --
1352 --
1353 fnd_file.put_line (fnd_file.LOG, 'Entering ARCHIVE_CODE ');
1354
1355 --Insert your logic to select the data for report over here.
1356
1357 --Pick up the details belonging to Trade Union
1358
1359
1360 -- If the g_local_unit_id is null then
1361 -- from assignment action id find the assignmnet id then segment2 where the local unit is is stored
1362 -- from there pick up the local unit details from the organization table
1363 OPEN csr_get_segment2 ();
1364 FETCH csr_get_segment2 INTO lr_get_segment2;
1365 CLOSE csr_get_segment2;
1366 l_local_unit_id_fetched := lr_get_segment2.segment2;
1367 fnd_file.put_line (
1368 fnd_file.LOG,
1369 ' After the Legal g_local_unit_id '
1370 || g_local_unit_id
1371 );
1372 fnd_file.put_line (
1373 fnd_file.LOG,
1374 ' l_Y_number_spare '
1375 || l_y_number_spare
1376 );
1377 fnd_file.put_line (
1378 fnd_file.LOG,
1379 ' l_Local_unit_number '
1380 || l_local_unit_number
1381 );
1382 hr_utility.TRACE ('After Local Unit');
1383 hr_utility.TRACE ('Before Person Record');
1384 fnd_file.put_line (
1385 fnd_file.LOG,
1386 ' g_business_group_id '
1387 || g_business_group_id
1388 );
1389 fnd_file.put_line (
1390 fnd_file.LOG,
1391 ' l_local_unit_id_fetched '
1392 || l_local_unit_id_fetched
1393 );
1394 fnd_file.put_line (
1395 fnd_file.LOG,
1396 ' p_assignment_action_id '
1397 || p_assignment_action_id
1398 );
1399 fnd_file.put_line (
1400 fnd_file.LOG,
1401 ' p_effective_date '
1402 || p_effective_date
1403 );
1404 OPEN csr_person_details (
1405 g_business_group_id,
1406 l_local_unit_id_fetched
1407 );
1408 FETCH csr_person_details INTO lr_person_details;
1409 CLOSE csr_person_details;
1410 l_employee_name := lr_person_details.NAME;
1411 l_employee_pin := lr_person_details.national_identifier;
1412 pay_balance_pkg.set_context('TAX_UNIT_ID',g_legal_employer_id);
1413 pay_balance_pkg.set_context('LOCAL_UNIT_ID',l_local_unit_id_fetched);
1414 pay_balance_pkg.set_context('DATE_EARNED',fnd_date.date_to_canonical(g_period_end_date));
1415 pay_balance_pkg.set_context('JURISDICTION_CODE',NULL);
1416 pay_balance_pkg.set_context('SOURCE_ID',NULL);
1417 pay_balance_pkg.set_context('TAX_GROUP',NULL);
1418 pay_balance_pkg.set_context('ORGANIZATION_ID',g_trade_union_id);
1419 pay_balance_pkg.set_context('ASSIGNMENT_ID',lr_person_details.assignment_id);
1420
1421 if g_period='MONTH' THEN
1422
1423
1424 OPEN csr_Get_Defined_Balance_Id( 'CUMULATIVE_TRADE_UNION_MEMBERSHIP_FEES_PER_UNION_LU_MONTH');
1425 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1426 CLOSE csr_Get_Defined_Balance_Id;
1427
1428 l_union_per_lu :=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_period_end_date );
1429 OPEN csr_Get_Defined_Balance_Id( 'UNION_DUES_NEGATIVE_PAYMENT_PER_UNION_LU_MONTH');
1430 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1431 CLOSE csr_Get_Defined_Balance_Id;
1432
1433 l_negative_per_lu :=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_period_end_date );
1434
1435 elsif g_period='BIMONTH' THEN
1436 OPEN csr_Get_Defined_Balance_Id( 'CUMULATIVE_TRADE_UNION_MEMBERSHIP_FEES_PER_UNION_LU_BIMONTH');
1437 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1438 CLOSE csr_Get_Defined_Balance_Id;
1439
1440 l_union_per_lu :=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_period_end_date );
1441 OPEN csr_Get_Defined_Balance_Id( 'UNION_DUES_NEGATIVE_PAYMENT_PER_UNION_LU_BIMONTH');
1442 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1443 CLOSE csr_Get_Defined_Balance_Id;
1444
1445 l_negative_per_lu :=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_period_end_date );
1446
1447
1448 elsif g_period='BIWEEK' THEN
1449 OPEN csr_Get_Defined_Balance_Id( 'CUMULATIVE_TRADE_UNION_MEMBERSHIP_FEES_PER_UNION_LU_BIWEEK');
1450 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1451 CLOSE csr_Get_Defined_Balance_Id;
1452
1453 l_union_per_lu :=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_period_end_date );
1454 OPEN csr_Get_Defined_Balance_Id( 'UNION_DUES_NEGATIVE_PAYMENT_PER_UNION_LU_BIWEEK');
1455 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1456 CLOSE csr_Get_Defined_Balance_Id;
1457
1458 l_negative_per_lu :=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_period_end_date );
1459
1460
1461 elsif g_period='QUARTER' THEN
1462 OPEN csr_Get_Defined_Balance_Id( 'CUMULATIVE_TRADE_UNION_MEMBERSHIP_FEES_PER_UNION_LU_QUARTER');
1463 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1464 CLOSE csr_Get_Defined_Balance_Id;
1465
1466 l_union_per_lu :=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_period_end_date );
1467 OPEN csr_Get_Defined_Balance_Id( 'UNION_DUES_NEGATIVE_PAYMENT_PER_UNION_LU_QUARTER');
1468 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1469 CLOSE csr_Get_Defined_Balance_Id;
1470
1471 l_negative_per_lu :=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_period_end_date );
1472
1473
1474 END IF;
1475
1476
1477 -- Pick up the defined balance id belonging to CUMULATIVE_TRADE_UNION_MEMBERSHIP_FEES_PER_PTD
1478 -- End of Pickingup the Data
1479 -- l_Union_Dues := pay_balance_pkg.get_value(lr_Get_Defined_Balance_Id.creator_id, lr_Person_Details.assignment_id,p_effective_date);
1480
1481 IF l_negative_per_lu > 0
1482 THEN
1483 l_Sign_of_payment := '-';
1484 ELSE
1485 l_Sign_of_payment :='+';
1486 END IF;
1487
1488
1489 BEGIN
1490 SELECT 1
1491 INTO l_flag
1492 FROM pay_action_information
1493 WHERE action_information_category = 'EMEA REPORT INFORMATION'
1494 AND action_information1 = 'PYFIUMFR'
1495 AND action_information2 = 'PER'
1496 AND action_context_id = p_assignment_action_id;
1497 EXCEPTION
1498 WHEN NO_DATA_FOUND
1499 THEN
1500 pay_action_information_api.create_action_information (
1501 p_action_information_id=> l_action_info_id,
1502 p_action_context_id=> p_assignment_action_id,
1503 p_action_context_type=> 'AAP',
1504 p_object_version_number=> l_ovn,
1505 p_effective_date=> l_effective_date,
1506 p_source_id=> NULL,
1507 p_source_text=> NULL,
1508 p_action_information_category=> 'EMEA REPORT INFORMATION',
1509 p_action_information1=> 'PYFIUMFR',
1510 p_action_information2=> 'PER',
1511 p_action_information3=> l_local_unit_id_fetched,
1512 p_action_information4=> lr_person_details.national_identifier,
1513 p_action_information5=> lr_person_details.NAME,
1514 p_action_information6=> (lr_person_details.membership_start_date),
1515 p_action_information7=> (lr_person_details.membership_end_date),
1516 p_action_information8=> fnd_number.number_to_canonical(l_union_per_lu),
1517 p_action_information9=> l_Sign_of_payment,
1518 p_action_information10=> NULL,
1519 p_action_information11=> NULL,
1520 p_action_information12=> NULL,
1521 p_action_information13=> NULL,
1522 p_action_information14=> NULL,
1523 p_action_information15=> NULL,
1524 p_action_information16=> NULL,
1525 p_action_information17=> NULL,
1526 p_action_information18=> NULL,
1527 p_action_information19=> NULL,
1528 p_action_information20=> NULL,
1529 p_action_information21=> NULL,
1530 p_action_information22=> NULL,
1531 p_action_information23=> NULL,
1532 p_action_information24=> NULL,
1533 p_action_information25=> NULL,
1534 p_action_information26=> NULL,
1535 p_action_information27=> NULL --date from srs req
1536 ,
1537 p_action_information28=> NULL,
1538 p_action_information29=> NULL,
1539 p_action_information30=> NULL
1540 );
1541 WHEN OTHERS
1542 THEN
1543 NULL;
1544 END;
1545 END IF; ---ARCHIVE=YES
1546
1547
1548 --
1549 --
1550 --
1551 --END LOOP;
1552 IF g_debug
1553 THEN
1554 hr_utility.set_location (' Leaving Procedure ARCHIVE_CODE', 390);
1555 END IF;
1556 END archive_code;
1557 END pay_fi_archive_umfr;