[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_HOLIDAY_PAY_DEBT
Source
1 PACKAGE BODY pay_se_holiday_pay_debt AS
2 /* $Header: pysehpdr.pkb 120.0.12000000.1 2007/04/20 06:28:12 abhgangu noship $ */
3 g_debug BOOLEAN := hr_utility.debug_enabled;
4
5 TYPE lock_rec IS RECORD (
6 archive_assact_id NUMBER
7 );
8
9 TYPE lock_table IS TABLE OF lock_rec
10 INDEX BY BINARY_INTEGER;
11
12 g_lock_table lock_table;
13 g_index NUMBER := -1;
14 g_index_assact NUMBER := -1;
15 g_index_bal NUMBER := -1;
16 g_package VARCHAR2 (33) := 'PAY_SE_HOLIDAY_PAY_DEBT.';
17 g_payroll_action_id NUMBER;
18 g_arc_payroll_action_id NUMBER;
19 -- Globals to pick up all the parameter
20 g_business_group_id NUMBER;
21 g_effective_date DATE;
22 g_pension_provider_id NUMBER;
23 g_legal_employer_id NUMBER;
24 g_local_unit_id NUMBER;
25 g_request_for VARCHAR2 (20);
26 g_start_date DATE;
27 g_end_date DATE;
28 --End of Globals to pick up all the parameter
29 g_format_mask VARCHAR2 (50);
30 g_err_num NUMBER;
31 g_errm VARCHAR2 (150);
32
33 /* GET PARAMETER */
34 /*FUNCTION GET_PARAMETER(
35 p_parameter_string IN VARCHAR2
36 ,p_token IN VARCHAR2
37 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
38 IS
39 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
40 l_start_pos NUMBER;
41 l_delimiter VARCHAR2(1):=' ';
42 l_proc VARCHAR2(40):= g_package||' get parameter ';
43
44 BEGIN
45 --
46 IF g_debug THEN
47 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
48 END IF;
49 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
50 --
51 IF l_start_pos = 0 THEN
52 l_delimiter := '|';
53 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
54 END IF;
55 IF l_start_pos <> 0 THEN
56 l_start_pos := l_start_pos + length(p_token||'=');
57 l_parameter := substr(p_parameter_string,
58 l_start_pos,
59 instr(p_parameter_string||' ',
60 l_delimiter,l_start_pos)
61 - l_start_pos);
62 IF p_segment_number IS NOT NULL THEN
63 l_parameter := ':'||l_parameter||':';
64 l_parameter := substr(l_parameter,
65 instr(l_parameter,':',1,p_segment_number)+1,
66 instr(l_parameter,':',1,p_segment_number+1) -1
67 - instr(l_parameter,':',1,p_segment_number));
68 END IF;
69 END IF;
70 --
71 RETURN l_parameter;
72 IF g_debug THEN
73 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
74 END IF;
75 END;
76 */
77 /* GET PARAMETER */
78 FUNCTION get_parameter (
79 p_parameter_string IN VARCHAR2
80 , p_token IN VARCHAR2
81 , p_segment_number IN NUMBER DEFAULT NULL
82 )
83 RETURN VARCHAR2
84 IS
85 l_parameter pay_payroll_actions.legislative_parameters%TYPE := NULL;
86 l_start_pos NUMBER;
87 l_delimiter VARCHAR2 (1) := ' ';
88 l_proc VARCHAR2 (40) := g_package || ' get parameter ';
89 BEGIN
90 --
91 IF g_debug
92 THEN
93 hr_utility.set_location (' Entering Function GET_PARAMETER', 10);
94 END IF;
95
96 l_start_pos :=
97 INSTR (' ' || p_parameter_string, l_delimiter || p_token || '=');
98
99 --
100 IF l_start_pos = 0
101 THEN
102 l_delimiter := '|';
103 l_start_pos :=
104 INSTR (' ' || p_parameter_string, l_delimiter || p_token || '=');
105 END IF;
106
107 IF l_start_pos <> 0
108 THEN
109 l_start_pos := l_start_pos + LENGTH (p_token || '=');
110 l_parameter :=
111 SUBSTR (p_parameter_string
112 , l_start_pos
113 , INSTR (p_parameter_string || ' '
114 , l_delimiter
115 , l_start_pos
116 )
117 - (l_start_pos)
118 );
119
120 IF p_segment_number IS NOT NULL
121 THEN
122 l_parameter := ':' || l_parameter || ':';
123 l_parameter :=
124 SUBSTR (l_parameter
125 , INSTR (l_parameter, ':', 1, p_segment_number) + 1
126 , INSTR (l_parameter, ':', 1, p_segment_number + 1)
127 - 1
128 - INSTR (l_parameter, ':', 1, p_segment_number)
129 );
130 END IF;
131 END IF;
132
133 --
134 IF g_debug
135 THEN
136 hr_utility.set_location (' Leaving Function GET_PARAMETER', 20);
137 END IF;
138
139 RETURN l_parameter;
140 END;
141
142 /* GET ALL PARAMETERS */
143 PROCEDURE get_all_parameters (
144 p_payroll_action_id IN NUMBER -- In parameter
145 , p_business_group_id OUT NOCOPY NUMBER -- Core parameter
146 , p_effective_date OUT NOCOPY DATE -- Core parameter
147 , p_legal_employer_id OUT NOCOPY NUMBER -- User parameter
148 , p_request_for_all_or_not OUT NOCOPY VARCHAR2 -- User parameter
149 , p_start_date OUT NOCOPY DATE -- User parameter
150 , p_end_date OUT NOCOPY DATE -- User parameter
151 )
152 IS
153 CURSOR csr_parameter_info (p_payroll_action_id NUMBER)
154 IS
155 SELECT TO_NUMBER
156 (SUBSTR
157 (pay_se_holiday_pay_debt.get_parameter
158 (legislative_parameters
159 , 'LEGAL_EMPLOYER'
160 )
161 , 1
162 , LENGTH
163 (pay_se_holiday_pay_debt.get_parameter
164 (legislative_parameters
165 , 'LEGAL_EMPLOYER'
166 )
167 )
168 - 1
169 )
170 ) legal
171 , SUBSTR
172 (pay_se_holiday_pay_debt.get_parameter
173 (legislative_parameters
174 , 'REQUEST_FOR'
175 )
176 , 1
177 , LENGTH
178 (pay_se_holiday_pay_debt.get_parameter
179 (legislative_parameters
180 , 'REQUEST_FOR'
181 )
182 )
183 - 1
184 ) request_for
185 , (pay_se_holiday_pay_debt.get_parameter
186 (legislative_parameters
187 , 'EFFECTIVE_START_DATE'
188 )
189 ) eff_start_date
190 , (pay_se_holiday_pay_debt.get_parameter
191 (legislative_parameters
192 , 'EFFECTIVE_END_DATE'
193 )
194 ) eff_end_date
195 , effective_date effective_date, business_group_id bg_id
196 FROM pay_payroll_actions
197 WHERE payroll_action_id = p_payroll_action_id;
198
199 lr_parameter_info csr_parameter_info%ROWTYPE;
200 l_proc VARCHAR2 (240)
201 := g_package || ' GET_ALL_PARAMETERS ';
202 BEGIN
203 fnd_file.put_line (fnd_file.LOG
204 , 'Entering Procedure GET_ALL_PARAMETER '
205 );
206 fnd_file.put_line (fnd_file.LOG
207 , 'Payroill Action iD ' || p_payroll_action_id
208 );
209
210 OPEN csr_parameter_info (p_payroll_action_id);
211
212 --FETCH csr_parameter_info into lr_parameter_info;
213 FETCH csr_parameter_info
214 INTO lr_parameter_info;
215
216 CLOSE csr_parameter_info;
217
218 p_legal_employer_id := lr_parameter_info.legal;
219 fnd_file.put_line (fnd_file.LOG
220 , 'lr_parameter_info.Legal '
221 || lr_parameter_info.legal
222 );
223 p_request_for_all_or_not := lr_parameter_info.request_for;
224 fnd_file.put_line (fnd_file.LOG
225 , 'lr_parameter_info.REQUEST_FOR '
226 || lr_parameter_info.request_for
227 );
228 fnd_file.put_line (fnd_file.LOG
229 , 'lr_parameter_info.EFF_START_DATE '
230 || lr_parameter_info.eff_start_date
231 );
232 p_start_date :=
233 fnd_date.canonical_to_date (lr_parameter_info.eff_start_date);
234 fnd_file.put_line (fnd_file.LOG
235 , 'lr_parameter_info.EFF_END_DATE '
236 || lr_parameter_info.eff_end_date
237 );
238 p_end_date :=
239 fnd_date.canonical_to_date (lr_parameter_info.eff_end_date);
240 fnd_file.put_line (fnd_file.LOG
241 , 'lr_parameter_info.Effective_date '
242 || lr_parameter_info.effective_date
243 );
244 p_effective_date := lr_parameter_info.effective_date;
245 p_business_group_id := lr_parameter_info.bg_id;
246 fnd_file.put_line (fnd_file.LOG, 'After csr_parameter_info in ');
247 --fnd_file.put_line(fnd_file.log,'After p_pension_provider_id ' || p_pension_provider_id);
248 fnd_file.put_line (fnd_file.LOG
249 , 'After p_legal_employer_id in '
250 || p_legal_employer_id
251 );
252
253 --fnd_file.put_line(fnd_file.log,'After p_local_unit_id in ' || p_local_unit_id );
254 --fnd_file.put_line(fnd_file.log,'After p_archive' || p_archive );
255 IF g_debug
256 THEN
257 hr_utility.set_location (' Leaving Procedure GET_ALL_PARAMETERS'
258 , 30);
259 END IF;
260 END get_all_parameters;
261
262 /* RANGE CODE */
263 PROCEDURE range_code (
264 p_payroll_action_id IN NUMBER
265 , p_sql OUT NOCOPY VARCHAR2
266 )
267 IS
268 l_action_info_id NUMBER;
269 l_ovn NUMBER;
270 l_business_group_id NUMBER;
271 l_start_date VARCHAR2 (30);
272 l_end_date VARCHAR2 (30);
273 l_effective_date DATE;
274 l_consolidation_set NUMBER;
275 l_defined_balance_id NUMBER := 0;
276 l_count NUMBER := 0;
277 l_prev_prepay NUMBER := 0;
278 l_canonical_start_date DATE;
279 l_canonical_end_date DATE;
280 l_payroll_id NUMBER;
281 l_prepay_action_id NUMBER;
282 l_actid NUMBER;
283 l_assignment_id NUMBER;
284 l_action_sequence NUMBER;
285 l_assact_id NUMBER;
286 l_pact_id NUMBER;
287 l_flag NUMBER := 0;
288 l_element_context VARCHAR2 (5);
289
290 -- Archiving the data , as this will fire once
291 CURSOR csr_legal_employer_details (
292 csr_v_legal_employer_id hr_organization_information.organization_id%TYPE
293 )
294 IS
295 SELECT o1.NAME legal_employer_name
296 , hoi2.org_information2 org_number
297 , hoi1.organization_id legal_id
298 FROM hr_organization_units o1
299 , hr_organization_information hoi1
300 , hr_organization_information hoi2
301 WHERE o1.business_group_id = g_business_group_id
302 AND hoi1.organization_id = o1.organization_id
303 AND hoi1.organization_id =
304 NVL (csr_v_legal_employer_id, hoi1.organization_id)
305 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
306 AND hoi1.org_information_context = 'CLASS'
307 AND o1.organization_id = hoi2.organization_id
308 AND hoi2.org_information_context = 'SE_LEGAL_EMPLOYER_DETAILS';
309
310 l_legal_employer_details csr_legal_employer_details%ROWTYPE;
311
312 CURSOR csr_check_empty_le (
313 csr_v_legal_employer_id NUMBER
314 , csr_v_canonical_start_date DATE
315 , csr_v_canonical_end_date DATE
316 )
317 IS
318 SELECT '1'
319 FROM pay_payroll_actions appa
320 , pay_assignment_actions act
321 , per_all_assignments_f as1
322 , pay_payroll_actions ppa
323 WHERE ppa.payroll_action_id = p_payroll_action_id
324 AND appa.effective_date BETWEEN csr_v_canonical_start_date
325 AND csr_v_canonical_end_date
326 AND appa.action_type IN ('R', 'Q')
327 -- Payroll Run or Quickpay Run
328 AND act.payroll_action_id = appa.payroll_action_id
329 AND act.source_action_id IS NULL -- Master Action
330 AND as1.assignment_id = act.assignment_id
331 AND as1.business_group_id = g_business_group_id
332 AND act.action_status = 'C' -- Completed
333 AND act.tax_unit_id = csr_v_legal_employer_id
334 AND appa.effective_date BETWEEN as1.effective_start_date
335 AND as1.effective_end_date
336 AND ppa.effective_date BETWEEN as1.effective_start_date
337 AND as1.effective_end_date
338 ORDER BY as1.person_id, act.assignment_id;
339
340 l_le_has_employee VARCHAR2 (2);
341 -- Archiving the data , as this will fire once
342 BEGIN
343 fnd_file.put_line (fnd_file.LOG, 'In RANGE_CODE 0');
344
345 IF g_debug
346 THEN
347 hr_utility.set_location (' Entering Procedure RANGE_CODE', 40);
348 END IF;
349
350 p_sql :=
351 'SELECT DISTINCT person_id
352 FROM per_people_f ppf
353 ,pay_payroll_actions ppa
354 WHERE ppa.payroll_action_id = :payroll_action_id
355 AND ppa.business_group_id = ppf.business_group_id
356 ORDER BY ppf.person_id';
357 g_payroll_action_id := p_payroll_action_id;
358 g_business_group_id := NULL;
359 g_effective_date := NULL;
360 g_pension_provider_id := NULL;
361 g_legal_employer_id := NULL;
362 g_local_unit_id := NULL;
363 pay_se_holiday_pay_debt.get_all_parameters (p_payroll_action_id
364 , g_business_group_id
365 , g_effective_date
366 , g_legal_employer_id
367 , g_request_for
368 , g_start_date
369 , g_end_date
370 );
371 fnd_file.put_line (fnd_file.LOG
372 , 'Range Legal Emp ID ==> '
373 || g_legal_employer_id
374 );
375
376 -- *****************************************************************************
377 -- TO pick up the required details for Pension Providers
378 OPEN csr_legal_employer_details (g_legal_employer_id);
379
380 FETCH csr_legal_employer_details
381 INTO l_legal_employer_details;
382
383 CLOSE csr_legal_employer_details;
384
385 -- *****************************************************************************
386 fnd_file.put_line (fnd_file.LOG
387 , 'After CURSOR Legal Emp DETAILS ==> '
388 || g_legal_employer_id
389 );
390 -- *****************************************************************************
391 -- Insert the report Parameters
392 pay_action_information_api.create_action_information
393 (p_action_information_id => l_action_info_id
394 , p_action_context_id => p_payroll_action_id
395 , p_action_context_type => 'PA'
396 , p_object_version_number => l_ovn
397 , p_effective_date => g_effective_date
398 , p_source_id => NULL
399 , p_source_text => NULL
400 , p_action_information_category => 'EMEA REPORT DETAILS'
401 , p_action_information1 => 'PYSEHPDA'
402 , p_action_information2 => l_legal_employer_details.legal_employer_name
403 , p_action_information3 => g_legal_employer_id
404 , p_action_information4 => g_request_for
405 , p_action_information5 => fnd_date.date_to_canonical
406 (g_start_date)
407 , p_action_information6 => fnd_date.date_to_canonical
408 (g_end_date)
409 , p_action_information7 => NULL
410 , p_action_information8 => NULL
411 , p_action_information9 => NULL
412 , p_action_information10 => NULL
413 );
414 -- *****************************************************************************
415 fnd_file.put_line (fnd_file.LOG
416 , ' ================ ALL ================ '
417 );
418 --fnd_file.put_line(fnd_file.log,'PENSION provider name ==> '||lr_pension_provider_details.NAME );
419 --fnd_file.put_line(fnd_file.log,'PENSION provider ID ==> '||g_pension_provider_id);
420 fnd_file.put_line (fnd_file.LOG
421 , 'Legal Emp Name ==> '
422 || l_legal_employer_details.legal_employer_name
423 );
424 fnd_file.put_line (fnd_file.LOG
425 , 'Legal Emp ID ==> ' || g_legal_employer_id
426 );
427 fnd_file.put_line (fnd_file.LOG
428 , 'g_request_for ==> ' || g_request_for
429 );
430 --fnd_file.put_line(fnd_file.log,'Local Unit ID ==> '||g_local_unit_id);
431 --fnd_file.put_line(fnd_file.log,'acti_info_id ==> '||l_action_info_id );
432 fnd_file.put_line (fnd_file.LOG, ' ================================ ');
433
434 -- *****************************************************************************
435 IF g_request_for = 'REQUESTING_ORG'
436 THEN
437 -- Information regarding the Legal Employer
438 OPEN csr_legal_employer_details (g_legal_employer_id);
439
440 FETCH csr_legal_employer_details
441 INTO l_legal_employer_details;
442
443 CLOSE csr_legal_employer_details;
444
445 pay_action_information_api.create_action_information
446 (p_action_information_id => l_action_info_id
447 , p_action_context_id => p_payroll_action_id
448 , p_action_context_type => 'PA'
449 , p_object_version_number => l_ovn
450 , p_effective_date => g_effective_date
451 , p_source_id => NULL
452 , p_source_text => NULL
453 , p_action_information_category => 'EMEA REPORT INFORMATION'
454 , p_action_information1 => 'PYSEHPDA'
455 , p_action_information2 => 'LE'
456 , p_action_information3 => g_legal_employer_id
457 , p_action_information4 => l_legal_employer_details.legal_employer_name
458 , p_action_information5 => l_legal_employer_details.org_number
459 , p_action_information6 => NULL
460 , p_action_information7 => NULL
461 , p_action_information8 => NULL
462 , p_action_information9 => NULL
463 , p_action_information10 => NULL
464 );
465 -- *****************************************************************************
466 ELSE
467 -- *****************************************************************************
468 FOR rec_legal_employer_details IN csr_legal_employer_details (NULL)
469 LOOP
470 OPEN csr_check_empty_le (rec_legal_employer_details.legal_id
471 , g_start_date
472 , g_end_date
473 );
474
475 FETCH csr_check_empty_le
476 INTO l_le_has_employee;
477
478 CLOSE csr_check_empty_le;
479
480 IF l_le_has_employee = '1'
481 THEN
482 pay_action_information_api.create_action_information
483 (p_action_information_id => l_action_info_id
484 , p_action_context_id => p_payroll_action_id
485 , p_action_context_type => 'PA'
486 , p_object_version_number => l_ovn
487 , p_effective_date => g_effective_date
488 , p_source_id => NULL
489 , p_source_text => NULL
490 , p_action_information_category => 'EMEA REPORT INFORMATION'
491 , p_action_information1 => 'PYSEHPDA'
492 , p_action_information2 => 'LE'
493 , p_action_information3 => rec_legal_employer_details.legal_id
494 , p_action_information4 => rec_legal_employer_details.legal_employer_name
495 , p_action_information5 => rec_legal_employer_details.org_number
496 , p_action_information6 => NULL
497 , p_action_information7 => NULL
498 , p_action_information8 => NULL
499 , p_action_information9 => NULL
500 , p_action_information10 => NULL
501 );
502 END IF;
503 END LOOP;
504 END IF; -- FOR G_LEGAL_EMPLOYER
505
506 --END IF; -- G_Archive End
507 IF g_debug
508 THEN
509 hr_utility.set_location (' Leaving Procedure RANGE_CODE', 50);
510 END IF;
511 EXCEPTION
512 WHEN OTHERS
513 THEN
514 -- Return cursor that selects no rows
515 p_sql :=
516 'select 1 from dual where to_char(:payroll_action_id) = dummy';
517 END range_code;
518
519 /* ASSIGNMENT ACTION CODE */
520 PROCEDURE assignment_action_code (
521 p_payroll_action_id IN NUMBER
522 , p_start_person IN NUMBER
523 , p_end_person IN NUMBER
524 , p_chunk IN NUMBER
525 )
526 IS
527 CURSOR csr_prepaid_assignments_le (
528 p_payroll_action_id NUMBER
529 , p_start_person NUMBER
530 , p_end_person NUMBER
531 , p_legal_employer_id NUMBER
532 , l_canonical_start_date DATE
533 , l_canonical_end_date DATE
534 )
535 IS
536 /* SELECT as1.person_id person_id, act.assignment_id assignment_id
537 , act.assignment_action_id run_action_id
538 , act1.assignment_action_id prepaid_action_id
539 FROM pay_payroll_actions ppa
540 , pay_payroll_actions appa
541 , pay_payroll_actions appa2
542 , pay_assignment_actions act
543 , pay_assignment_actions act1
544 , pay_action_interlocks pai
545 , per_all_assignments_f as1
546 WHERE ppa.payroll_action_id = p_payroll_action_id
547 AND appa.effective_date BETWEEN l_canonical_start_date
548 AND l_canonical_end_date
549 AND as1.person_id BETWEEN p_start_person AND p_end_person
550 AND appa.action_type IN ('R', 'Q')
551 -- Payroll Run or Quickpay Run
552 AND act.payroll_action_id = appa.payroll_action_id
553 AND act.source_action_id IS NULL -- Master Action
554 AND as1.assignment_id = act.assignment_id
555 AND as1.business_group_id = g_business_group_id
556 AND ppa.effective_date BETWEEN as1.effective_start_date
557 AND as1.effective_end_date
558 AND act.action_status = 'C' -- Completed
559 AND act.assignment_action_id = pai.locked_action_id
560 AND act1.assignment_action_id = pai.locking_action_id
561 AND act1.action_status = 'C' -- Completed
562 AND act1.payroll_action_id = appa2.payroll_action_id
563 AND appa2.action_type IN ('P', 'U')
564 AND appa2.effective_date BETWEEN l_canonical_start_date
565 AND l_canonical_end_date
566 -- Prepayments or Quickpay Prepayments
567 AND act.tax_unit_id = act1.tax_unit_id
568 AND act.tax_unit_id = NVL (p_legal_employer_id, act.tax_unit_id)
569 ORDER BY as1.person_id, act.assignment_id;
570 */
571 SELECT as1.person_id person_id, act.assignment_id assignment_id
572 , act.assignment_action_id run_action_id
573 FROM pay_payroll_actions appa
574 , pay_assignment_actions act
575 , per_all_assignments_f as1
576 , pay_payroll_actions ppa
577 WHERE ppa.payroll_action_id = p_payroll_action_id
578 AND appa.effective_date BETWEEN l_canonical_start_date
579 AND l_canonical_end_date
580 AND as1.person_id BETWEEN p_start_person AND p_end_person
581 AND appa.action_type IN ('R', 'Q')
582 -- Payroll Run or Quickpay Run
583 AND act.payroll_action_id = appa.payroll_action_id
584 AND act.source_action_id IS NULL -- Master Action
585 AND as1.assignment_id = act.assignment_id
586 AND as1.business_group_id = g_business_group_id
587 AND act.action_status = 'C' -- Completed
588 AND act.tax_unit_id = NVL (p_legal_employer_id, act.tax_unit_id)
589 AND appa.effective_date BETWEEN as1.effective_start_date
590 AND as1.effective_end_date
591 AND ppa.effective_date BETWEEN as1.effective_start_date
592 AND as1.effective_end_date
593 ORDER BY as1.person_id, act.assignment_id;
594
595 CURSOR csr_get_defined_balance_id (
596 csr_v_balance_name ff_database_items.user_name%TYPE
597 )
598 IS
599 SELECT ue.creator_id
600 FROM ff_user_entities ue, ff_database_items di
601 WHERE di.user_name = csr_v_balance_name
602 AND ue.user_entity_id = di.user_entity_id
603 AND ue.legislation_code = 'SE'
604 AND ue.business_group_id IS NULL
605 AND ue.creator_type = 'B';
606
607 lr_get_defined_balance_id csr_get_defined_balance_id%ROWTYPE;
608 l_count NUMBER := 0;
609 l_prev_prepay NUMBER := 0;
610 l_canonical_start_date DATE;
611 l_canonical_end_date DATE;
612 l_pension_type hr_organization_information.org_information1%TYPE;
613 l_prepay_action_id NUMBER;
614 l_actid NUMBER;
615 l_assignment_id NUMBER;
616 l_action_sequence NUMBER;
617 l_assact_id NUMBER;
618 l_pact_id NUMBER;
619 l_flag NUMBER := 0;
620 l_defined_balance_id NUMBER := 0;
621 l_action_info_id NUMBER;
622 l_ovn NUMBER;
623 -- User pARAMETERS needed
624 l_business_group_id NUMBER;
625 l_effective_date DATE;
626 l_pension_provider_id NUMBER;
627 l_legal_employer_id NUMBER;
628 l_local_unit_id NUMBER;
629 l_archive VARCHAR2 (10);
630 -- End of User pARAMETERS needed
631 BEGIN
632 IF g_debug
633 THEN
634 hr_utility.set_location
635 (' Entering Procedure ASSIGNMENT_ACTION_CODE'
636 , 60
637 );
638 END IF;
639
640 fnd_file.put_line (fnd_file.LOG, ' ASSIGNMENT_ACTION_CODE ');
641 pay_se_holiday_pay_debt.get_all_parameters (p_payroll_action_id
642 , g_business_group_id
643 , g_effective_date
644 , g_legal_employer_id
645 , g_request_for
646 , g_start_date
647 , g_end_date
648 );
649 l_canonical_start_date := g_start_date;
650 l_canonical_end_date := g_end_date;
651 l_prepay_action_id := 0;
652 --fnd_file.put_line(fnd_file.log,' g_local_unit_id '|| g_local_unit_id);
653
654 --fnd_file.put_line(fnd_file.log,' INSIDE IF LOCAL UNIT NOT NULL ');
655 fnd_file.put_line (fnd_file.LOG
656 , ' p_payroll_action_id ==> ' || p_payroll_action_id
657 );
658 fnd_file.put_line (fnd_file.LOG
659 , ' g_legal_employer_id ==> ' || g_legal_employer_id
660 );
661 --fnd_file.put_line(fnd_file.log,' g_local_unit_id ==> ' || g_local_unit_id);
662 --fnd_file.put_line(fnd_file.log,' g_pension_provider_id ==> ' || g_pension_provider_id);
663 fnd_file.put_line (fnd_file.LOG
664 , ' g_effective_date ==> ' || g_effective_date
665 );
666 fnd_file.put_line (fnd_file.LOG
667 , ' l_canonical_start_date ==> '
668 || l_canonical_start_date
669 );
670 fnd_file.put_line (fnd_file.LOG
671 , ' l_canonical_end_date ==> ' || l_canonical_end_date
672 );
673
674 --fnd_file.put_line(fnd_file.log,' INSIDE ELS LOCAL UNIT NULL ');
675 l_assignment_id := 0;
676 FOR rec_prepaid_assignments IN
677 csr_prepaid_assignments_le (p_payroll_action_id
678 , p_start_person
679 , p_end_person
680 , g_legal_employer_id
681 , l_canonical_start_date
682 , l_canonical_end_date
683 )
684 LOOP
685 --fnd_file.put_line(fnd_file.log,' LE Inside the Csr Prepaid Cursor ');
686 IF l_assignment_id <> rec_prepaid_assignments.assignment_id
687 THEN
688 SELECT pay_assignment_actions_s.NEXTVAL
689 INTO l_actid
690 FROM DUAL;
691
692 -- Create the archive assignment action
693 hr_nonrun_asact.insact (l_actid
694 , rec_prepaid_assignments.assignment_id
695 , p_payroll_action_id
696 , p_chunk
697 , NULL
698 );
699 -- Create archive to prepayment assignment action interlock
700 --
701 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.prepaid_action_id);
702 END IF;
703
704 -- create archive to master assignment action interlock
705 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.run_action_id);
706 l_assignment_id := rec_prepaid_assignments.assignment_id;
707 END LOOP;
708
709 fnd_file.put_line
710 (fnd_file.LOG
711 , ' After Ending Assignment Act Code the Locking Cursor '
712 );
713
714 IF g_debug
715 THEN
716 hr_utility.set_location
717 (' Leaving Procedure ASSIGNMENT_ACTION_CODE'
718 , 70
719 );
720 END IF;
721 EXCEPTION
722 WHEN OTHERS
723 THEN
724 IF g_debug
725 THEN
726 hr_utility.set_location ('error raised assignment_action_code '
727 , 5
728 );
729 END IF;
730
731 RAISE;
732 END assignment_action_code;
733
734 /*fffffffffffffffffffffffffff*/
735
736 /* INITIALIZATION CODE */
737 PROCEDURE initialization_code (p_payroll_action_id IN NUMBER)
738 IS
739 l_action_info_id NUMBER;
740 l_ovn NUMBER;
741 l_count NUMBER := 0;
742 l_business_group_id NUMBER;
743 l_start_date VARCHAR2 (20);
744 l_end_date VARCHAR2 (20);
745 l_effective_date DATE;
746 l_payroll_id NUMBER;
747 l_consolidation_set NUMBER;
748 l_prev_prepay NUMBER := 0;
749 BEGIN
750 IF g_debug
751 THEN
752 hr_utility.set_location (' Entering Procedure INITIALIZATION_CODE'
753 , 80
754 );
755 END IF;
756
757 fnd_file.put_line (fnd_file.LOG, 'In INIT_CODE 0');
758 g_payroll_action_id := p_payroll_action_id;
759 g_business_group_id := NULL;
760 g_effective_date := NULL;
761 g_legal_employer_id := NULL;
762 pay_se_holiday_pay_debt.get_all_parameters (p_payroll_action_id
763 , g_business_group_id
764 , g_effective_date
765 , g_legal_employer_id
766 , g_request_for
767 , g_start_date
768 , g_end_date
769 );
770 fnd_file.put_line
771 (fnd_file.LOG
772 , 'In the INITIALIZATION_CODE After Initiliazing the global parameter '
773 );
774
775 IF g_debug
776 THEN
777 hr_utility.set_location (' Leaving Procedure INITIALIZATION_CODE'
778 , 90
779 );
780 END IF;
781 EXCEPTION
782 WHEN OTHERS
783 THEN
784 g_err_num := SQLCODE;
785
786 IF g_debug
787 THEN
788 hr_utility.set_location ( 'ORA_ERR: '
789 || g_err_num
790 || 'In INITIALIZATION_CODE'
791 , 180
792 );
793 END IF;
794 END initialization_code;
795
796 /* GET DEFINED BALANCE ID */
797 FUNCTION get_defined_balance_id (p_user_name IN VARCHAR2)
798 RETURN NUMBER
799 IS
800 /* Cursor to retrieve Defined Balance Id */
801 CURSOR csr_def_bal_id (p_user_name VARCHAR2)
802 IS
803 SELECT u.creator_id
804 FROM ff_user_entities u, ff_database_items d
805 WHERE d.user_name = p_user_name
806 AND u.user_entity_id = d.user_entity_id
807 AND (u.legislation_code = 'SE')
808 AND (u.business_group_id IS NULL)
809 AND u.creator_type = 'B';
810
811 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
812 BEGIN
813 IF g_debug
814 THEN
815 hr_utility.set_location
816 (' Entering Function GET_DEFINED_BALANCE_ID'
817 , 240
818 );
819 END IF;
820
821 OPEN csr_def_bal_id (p_user_name);
822
823 FETCH csr_def_bal_id
824 INTO l_defined_balance_id;
825
826 CLOSE csr_def_bal_id;
827
828 RETURN l_defined_balance_id;
829
830 IF g_debug
831 THEN
832 hr_utility.set_location (' Leaving Function GET_DEFINED_BALANCE_ID'
833 , 250
834 );
835 END IF;
836 END get_defined_balance_id;
837
838 FUNCTION get_defined_balance_value (
839 p_user_name IN VARCHAR2
840 , p_in_assignment_id IN NUMBER
841 , p_in_virtual_date IN DATE
842 , p_tax_unit_id IN NUMBER
843 , p_local_unit_id IN NUMBER
844 )
845 RETURN NUMBER
846 IS
847 /* Cursor to retrieve Defined Balance Id */
848 CURSOR csr_def_bal_id (p_user_name VARCHAR2)
849 IS
850 SELECT u.creator_id
851 FROM ff_user_entities u, ff_database_items d
852 WHERE d.user_name = p_user_name
853 AND u.user_entity_id = d.user_entity_id
854 AND (u.legislation_code = 'SE')
855 AND (u.business_group_id IS NULL)
856 AND u.creator_type = 'B';
857
858 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
859 l_return_balance_value NUMBER;
860 BEGIN
861 IF g_debug
862 THEN
863 hr_utility.set_location
864 (' Entering Function GET_DEFINED_BALANCE_VALUE'
865 , 240
866 );
867 END IF;
868
869 OPEN csr_def_bal_id (p_user_name);
870
871 FETCH csr_def_bal_id
872 INTO l_defined_balance_id;
873
874 CLOSE csr_def_bal_id;
875
876 pay_balance_pkg.set_context('TAX_UNIT_ID',p_tax_unit_id);
877 pay_balance_pkg.set_context('LOCAL_UNIT_ID',p_local_unit_id);
878
879 l_return_balance_value :=
880 TO_CHAR
881 (pay_balance_pkg.get_value
882 (p_defined_balance_id => l_defined_balance_id
883 , p_assignment_id => p_in_assignment_id
884 , p_virtual_date => p_in_virtual_date
885 )
886 , '999999999D99'
887 );
888 RETURN l_return_balance_value;
889
890 IF g_debug
891 THEN
892 hr_utility.set_location
893 (' Leaving Function GET_DEFINED_BALANCE_VALUE'
894 , 250
895 );
896 END IF;
897 END get_defined_balance_value;
898
899 /* ARCHIVE CODE */
900 PROCEDURE archive_code (
901 p_assignment_action_id IN NUMBER
902 , p_effective_date IN DATE
903 )
904 IS
905 CURSOR csr_get_defined_balance_id (
906 csr_v_balance_name ff_database_items.user_name%TYPE
907 )
908 IS
909 SELECT ue.creator_id
910 FROM ff_user_entities ue, ff_database_items di
911 WHERE di.user_name = csr_v_balance_name
912 AND ue.user_entity_id = di.user_entity_id
913 AND ue.legislation_code = 'SE'
914 AND ue.business_group_id IS NULL
915 AND ue.creator_type = 'B';
916
917 lr_get_defined_balance_id csr_get_defined_balance_id%ROWTYPE;
918 l_actid NUMBER;
919 l_end_date per_time_periods.end_date%TYPE;
920 l_date_earned pay_payroll_actions.date_earned%TYPE;
921 l_effective_date pay_payroll_actions.effective_date%TYPE;
922 l_start_date per_time_periods.start_date%TYPE;
923 l_action_info_id pay_action_information.action_information_id%TYPE;
924 l_ovn pay_action_information.object_version_number%TYPE;
925 l_flag NUMBER := 0;
926 -- The place for Variables which fetches the values to be archived
927 l_employee_name VARCHAR2 (240);
928 l_employee_number VARCHAR2 (240);
929 l_employee_code VARCHAR2 (240);
930 l_employee_pin VARCHAR2 (240);
931 l_holiday_pay_per_day NUMBER := 0;
932 l_total_paid_days NUMBER := 0;
933 l_total_paid_days_amount NUMBER := 0;
934 l_total_saved_days NUMBER := 0;
935 l_total_saved_days_amount NUMBER := 0;
936 l_total_earned_days NUMBER := 0;
937 l_total_earned_days_amount NUMBER := 0;
938 l_original_total_paid_days NUMBER := 0;
939 l_action_id VARCHAR2 (2);
940 l_local_unit_id_fetched NUMBER;
941 l_eit_local_unit NUMBER;
942 l_legal_employer_id_fetched NUMBER;
943 -- Temp needed Variables
944 l_person_id per_all_people_f.person_id%TYPE;
945 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
946
947 -- Temp needed Variables
948
949 -- End of place for Variables which fetches the values to be archived
950
951 -- The place for Cursor which fetches the values to be archived
952
953 --
954 -- Cursor to pick up
955
956 /* Cursor to retrieve Person Details */
957 CURSOR csr_get_person_details (p_asg_act_id NUMBER)
958 IS
959 SELECT pap.last_name, pap.pre_name_adjunct, pap.first_name
960 , pap.national_identifier, pap.person_id, pac.assignment_id
961 , paa.assignment_number, paa.employee_category
962 FROM pay_assignment_actions pac
963 , per_all_assignments_f paa
964 , per_all_people_f pap
965 WHERE pac.assignment_action_id = p_asg_act_id
966 AND paa.assignment_id = pac.assignment_id
967 AND paa.person_id = pap.person_id
968 AND pap.per_information_category = 'SE'
969 AND p_effective_date BETWEEN pap.effective_start_date
970 AND pap.effective_end_date
971 AND p_effective_date BETWEEN paa.effective_start_date
972 AND paa.effective_end_date;
973
974 lr_get_person_details csr_get_person_details%ROWTYPE;
975
976 -- Cursor to pick up segment2
977 CURSOR csr_get_segment2
978 IS
979 SELECT scl.segment2, scl.segment8
980 FROM per_all_assignments_f paa
981 , hr_soft_coding_keyflex scl
982 , pay_assignment_actions pasa
983 WHERE pasa.assignment_action_id = p_assignment_action_id
984 AND pasa.assignment_id = paa.assignment_id
985 AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
986 AND paa.primary_flag = 'Y'
987 AND p_effective_date BETWEEN paa.effective_start_date
988 AND paa.effective_end_date;
989
990 lr_get_segment2 csr_get_segment2%ROWTYPE;
991
992 -- Cursor to pick up LEGAL EMPLOYER
993 CURSOR csr_find_legal_employer (
994 csr_v_organization_id hr_organization_information.organization_id%TYPE
995 )
996 IS
997 SELECT hoi3.organization_id legal_id
998 FROM hr_all_organization_units o1
999 , hr_organization_information hoi1
1000 , hr_organization_information hoi2
1001 , hr_organization_information hoi3
1002 WHERE o1.business_group_id = g_business_group_id
1003 AND hoi1.organization_id = o1.organization_id
1004 AND hoi1.organization_id = csr_v_organization_id
1005 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
1006 AND hoi1.org_information_context = 'CLASS'
1007 AND o1.organization_id = hoi2.org_information1
1008 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
1009 AND hoi2.organization_id = hoi3.organization_id
1010 AND hoi3.org_information_context = 'CLASS'
1011 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER';
1012
1013 lr_find_legal_employer csr_find_legal_employer%ROWTYPE;
1014
1015 -- Cursor to pick up Local Unit Details
1016 CURSOR csr_local_unit_details (
1017 csr_v_local_unit_id hr_organization_information.organization_id%TYPE
1018 )
1019 IS
1020 SELECT o1.NAME, hoi2.org_information1
1021 FROM hr_organization_units o1
1022 , hr_organization_information hoi1
1023 , hr_organization_information hoi2
1024 WHERE o1.business_group_id = g_business_group_id
1025 AND hoi1.organization_id = o1.organization_id
1026 AND hoi1.organization_id = csr_v_local_unit_id
1027 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
1028 AND hoi1.org_information_context = 'CLASS'
1029 AND o1.organization_id = hoi2.organization_id
1030 AND hoi2.org_information_context = 'SE_LOCAL_UNIT_DETAILS';
1031
1032 lr_local_unit_details csr_local_unit_details%ROWTYPE;
1033 -- End of Cursors
1034 l_period_start_date DATE;
1035 l_period_end_date DATE;
1036 -- Cursor to pick up the Absence details
1037 --#########################################
1038
1039 -- End of place for Cursor which fetches the values to be archived
1040 BEGIN
1041 IF g_debug
1042 THEN
1043 hr_utility.set_location (' Entering Procedure ARCHIVE_CODE', 380);
1044 END IF;
1045
1046 fnd_file.put_line (fnd_file.LOG, 'Entering ARCHIVE_CODE ');
1047
1048 -- *****************************************************************************
1049 -- TO pick up the PIN
1050 OPEN csr_get_person_details (p_assignment_action_id);
1051
1052 FETCH csr_get_person_details
1053 INTO lr_get_person_details;
1054
1055 CLOSE csr_get_person_details;
1056
1057 l_employee_pin := lr_get_person_details.national_identifier;
1058
1059 IF lr_get_person_details.pre_name_adjunct IS NULL
1060 THEN
1061 l_employee_name :=
1062 lr_get_person_details.last_name
1063 || ' '
1064 || lr_get_person_details.first_name;
1065 ELSE
1066 l_employee_name :=
1067 lr_get_person_details.last_name
1068 || ' '
1069 || lr_get_person_details.pre_name_adjunct
1070 || ' '
1071 || lr_get_person_details.first_name;
1072 END IF;
1073
1074 l_employee_number := lr_get_person_details.assignment_number;
1075 l_employee_code := lr_get_person_details.employee_category;
1076 fnd_file.put_line (fnd_file.LOG
1077 , ' ==============PERSON================== '
1078 );
1079 fnd_file.put_line (fnd_file.LOG
1080 , 'l_Employee_Pin ==> ' || l_employee_pin
1081 );
1082 fnd_file.put_line (fnd_file.LOG
1083 , 'l_Employee_name ==> ' || l_employee_name
1084 );
1085 fnd_file.put_line (fnd_file.LOG, ' ================================ ');
1086
1087 -- *****************************************************************************
1088 -- TO pick up the Local Unit Sub-disbursement Number
1089 OPEN csr_get_segment2 ();
1090
1091 FETCH csr_get_segment2
1092 INTO lr_get_segment2;
1093
1094 CLOSE csr_get_segment2;
1095
1096 l_local_unit_id_fetched := lr_get_segment2.segment2;
1097
1098 OPEN csr_find_legal_employer (l_local_unit_id_fetched);
1099
1100 FETCH csr_find_legal_employer
1101 INTO lr_find_legal_employer;
1102
1103 CLOSE csr_find_legal_employer;
1104
1105 l_legal_employer_id_fetched := lr_find_legal_employer.legal_id;
1106 -- *****************************************************************************
1107 -- Pick up Person ID
1108 l_person_id := lr_get_person_details.person_id;
1109 fnd_file.put_line (fnd_file.LOG
1110 , 'l_person_id ==> ' || l_person_id
1111 );
1112 -- *****************************************************************************
1113
1114 -- *****************************************************************************
1115 -- Pick up the Balance value
1116 l_assignment_id := lr_get_person_details.assignment_id;
1117 fnd_file.put_line (fnd_file.LOG
1118 , 'l_assignment_id ==> ' || l_assignment_id
1119 );
1120
1121 -- *****************************************************************************
1122 -- Setting the context
1123 BEGIN
1124 fnd_file.put_line (fnd_file.LOG
1125 , 'l_assignment_id ==> ' || l_assignment_id
1126 );
1127 pay_balance_pkg.set_context ('ASSIGNMENT_ID', l_assignment_id);
1128 fnd_file.put_line (fnd_file.LOG
1129 , 'L_LEGAL_EMPLOYER_ID_FETCHED ==> '
1130 || l_legal_employer_id_fetched
1131 );
1132 pay_balance_pkg.set_context ('TAX_UNIT_ID'
1133 , l_legal_employer_id_fetched
1134 );
1135 fnd_file.put_line (fnd_file.LOG
1136 , 'l_local_unit_id_fetched ==> '
1137 || l_local_unit_id_fetched
1138 );
1139 pay_balance_pkg.set_context ('LOCAL_UNIT_ID'
1140 , l_local_unit_id_fetched);
1141 fnd_file.put_line (fnd_file.LOG, 'G_END_DATE ==> ' || g_end_date);
1142 pay_balance_pkg.set_context ('DATE_EARNED', g_end_date);
1143 fnd_file.put_line (fnd_file.LOG
1144 , 'l_assignment_id ==> ' || l_assignment_id
1145 );
1146 pay_balance_pkg.set_context ('JURISDICTION_CODE', NULL);
1147 pay_balance_pkg.set_context ('SOURCE_ID', NULL);
1148 pay_balance_pkg.set_context ('TAX_GROUP', NULL);
1149 END;
1150
1151 -- *****************************************************************************
1152 -- getting Balance Values
1153 l_holiday_pay_per_day :=
1154 TO_CHAR
1155 (get_defined_balance_value ('HOLIDAY_PAY_PER_DAY_ASG_LE_HY_YEAR'
1156 , l_assignment_id
1157 , g_end_date
1158 ,l_legal_employer_id_fetched
1159 ,l_local_unit_id_fetched
1160 )
1161 , '999999999D99'
1162 );
1163 /* OPEN csr_Get_Defined_Balance_Id( 'HOLIDAY_PAY_PER_DAY_ASG_LE_HY_YEAR');
1164 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1165 CLOSE csr_Get_Defined_Balance_Id;
1166
1167 fnd_file.put_line(fnd_file.log,'DEFINED_BALANCE_ID ==> ' ||lr_Get_Defined_Balance_Id.creator_id );
1168 --fnd_file.put_line(fnd_file.log,'g_effective_date ==> ' ||g_effective_date );
1169
1170 L_HOLIDAY_PAY_PER_DAY :=to_char(pay_balance_pkg.get_value
1171 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1172 P_ASSIGNMENT_ID =>l_assignment_id ,
1173 P_VIRTUAL_DATE => g_end_date ),'999999999D99') ;
1174 */
1175 /* OPEN csr_Get_Defined_Balance_Id( 'TOTAL_PAID_HOLIDAY_DAYS_TAKEN_ASG_LE_HY_YEAR');
1176 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1177 CLOSE csr_Get_Defined_Balance_Id;
1178
1179 fnd_file.put_line(fnd_file.log,'DEFINED_BALANCE_ID ==> ' ||lr_Get_Defined_Balance_Id.creator_id );
1180 --fnd_file.put_line(fnd_file.log,'g_effective_date ==> ' ||g_effective_date );
1181
1182 L_TOTAL_PAID_DAYS :=to_char(pay_balance_pkg.get_value
1183 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1184 P_ASSIGNMENT_ID =>l_assignment_id ,
1185 P_VIRTUAL_DATE => g_end_date ),'999999999D99') ;
1186 */
1187 l_total_paid_days :=
1188 TO_CHAR
1189 (get_defined_balance_value
1190 ('TOTAL_PAID_HOLIDAY_DAYS_TAKEN_ASG_LE_HY_YEAR'
1191 , l_assignment_id
1192 , g_end_date
1193 ,l_legal_employer_id_fetched
1194 ,l_local_unit_id_fetched
1195 )
1196 , '999999999D99'
1197 );
1198 /*
1199 OPEN csr_Get_Defined_Balance_Id( 'TOTAL_HOLIDAY_PAY_ASG_LE_HY_YEAR');
1200 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1201 CLOSE csr_Get_Defined_Balance_Id;
1202
1203 --fnd_file.put_line(fnd_file.log,'DEFINED_BALANCE_ID ==> ' ||lr_Get_Defined_Balance_Id.creator_id );
1204 --fnd_file.put_line(fnd_file.log,'g_effective_date ==> ' ||g_effective_date );
1205
1206 L_TOTAL_PAID_DAYS_AMOUNT :=to_char(pay_balance_pkg.get_value
1207 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1208 P_ASSIGNMENT_ID =>l_assignment_id ,
1209 P_VIRTUAL_DATE => g_end_date ),'999999999D99') ;
1210 */
1211 l_total_paid_days_amount :=
1212 TO_CHAR
1213 (get_defined_balance_value ('TOTAL_HOLIDAY_PAY_ASG_LE_HY_YEAR'
1214 , l_assignment_id
1215 , g_end_date
1216 ,l_legal_employer_id_fetched
1217 ,l_local_unit_id_fetched
1218 )
1219 , '999999999D99'
1220 );
1221 /*
1222 OPEN csr_Get_Defined_Balance_Id( 'TOTAL_SAVED_HOLIDAY_DAYS_TAKEN_ASG_LE_HY_YEAR');
1223 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1224 CLOSE csr_Get_Defined_Balance_Id;
1225
1226 --fnd_file.put_line(fnd_file.log,'DEFINED_BALANCE_ID ==> ' ||lr_Get_Defined_Balance_Id.creator_id );
1227 --fnd_file.put_line(fnd_file.log,'g_effective_date ==> ' ||g_effective_date );
1228
1229 L_TOTAL_SAVED_DAYS :=to_char(pay_balance_pkg.get_value
1230 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1231 P_ASSIGNMENT_ID =>l_assignment_id ,
1232 P_VIRTUAL_DATE => g_end_date ),'999999999D99') ;
1233 */
1234 l_total_saved_days :=
1235 TO_CHAR
1236 (get_defined_balance_value
1237 ('TOTAL_SAVED_HOLIDAY_DAYS_TAKEN_ASG_LE_HY_YEAR'
1238 , l_assignment_id
1239 , g_end_date
1240 ,l_legal_employer_id_fetched
1241 ,l_local_unit_id_fetched
1242 )
1243 , '999999999D99'
1244 );
1245 /*
1246 OPEN csr_Get_Defined_Balance_Id( 'TOTAL_SAVED_HOLIDAY_PAY_ASG_LE_HY_YEAR');
1247 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1248 CLOSE csr_Get_Defined_Balance_Id;
1249
1250 --fnd_file.put_line(fnd_file.log,'DEFINED_BALANCE_ID ==> ' ||lr_Get_Defined_Balance_Id.creator_id );
1251 --fnd_file.put_line(fnd_file.log,'g_effective_date ==> ' ||g_effective_date );
1252
1253 L_TOTAL_SAVED_DAYS_AMOUNT :=to_char(pay_balance_pkg.get_value
1254 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1255 P_ASSIGNMENT_ID =>l_assignment_id ,
1256 P_VIRTUAL_DATE => g_end_date ),'999999999D99') ;
1257 */
1258 l_total_saved_days_amount :=
1259 TO_CHAR
1260 (get_defined_balance_value
1261 ('TOTAL_SAVED_HOLIDAY_PAY_ASG_LE_HY_YEAR'
1262 , l_assignment_id
1263 , g_end_date
1264 ,l_legal_employer_id_fetched
1265 ,l_local_unit_id_fetched
1266 )
1267 , '999999999D99'
1268 );
1269 l_original_total_paid_days :=
1270 TO_CHAR
1271 (get_defined_balance_value
1272 ('TOTAL_PAID_HOLIDAY_DAYS_ASG_LE_HY_YEAR'
1273 , l_assignment_id
1274 , g_end_date
1275 ,l_legal_employer_id_fetched
1276 ,l_local_unit_id_fetched
1277 )
1278 , '999999999D99'
1279 );
1280 l_total_earned_days := l_original_total_paid_days - l_total_paid_days;
1281
1282 IF l_total_earned_days <= 0
1283 THEN
1284 l_total_earned_days := 0;
1285 END IF;
1286
1287 l_total_earned_days_amount :=
1288 l_total_earned_days * l_holiday_pay_per_day;
1289
1290 /*
1291 OPEN csr_Get_Defined_Balance_Id( 'TOTAL_PAID_HOLIDAY_DAYS_TAKEN_ASG_LE_HY_YEAR');
1292 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1293 CLOSE csr_Get_Defined_Balance_Id;
1294
1295 --fnd_file.put_line(fnd_file.log,'DEFINED_BALANCE_ID ==> ' ||lr_Get_Defined_Balance_Id.creator_id );
1296 --fnd_file.put_line(fnd_file.log,'g_effective_date ==> ' ||g_effective_date );
1297
1298 L_TOTAL_EARNED_DAYS :=to_char(pay_balance_pkg.get_value
1299 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1300 P_ASSIGNMENT_ID =>l_assignment_id ,
1301 P_VIRTUAL_DATE => g_end_date ),'999999999D99') ;
1302 */
1303 /* OPEN csr_Get_Defined_Balance_Id( 'TOTAL_PAID_HOLIDAY_DAYS_ASG_LE_HY_YEAR');
1304 FETCH csr_Get_Defined_Balance_Id INTO lr_Get_Defined_Balance_Id;
1305 CLOSE csr_Get_Defined_Balance_Id;
1306
1307 --fnd_file.put_line(fnd_file.log,'DEFINED_BALANCE_ID ==> ' ||lr_Get_Defined_Balance_Id.creator_id );
1308 --fnd_file.put_line(fnd_file.log,'g_effective_date ==> ' ||g_effective_date );
1309
1310 L_TOTAL_EARNED_DAYS_AMOUNT :=to_char(pay_balance_pkg.get_value
1311 (P_DEFINED_BALANCE_ID =>lr_Get_Defined_Balance_Id.creator_id,
1312 P_ASSIGNMENT_ID =>l_assignment_id ,
1313 P_VIRTUAL_DATE => g_end_date ),'999999999D99') ;
1314
1315 */
1316 -- End of Pickingup the Data
1317 BEGIN
1318 SELECT 1
1319 INTO l_flag
1320 FROM pay_action_information
1321 WHERE action_information_category = 'EMEA REPORT DETAILS'
1322 AND action_information1 = 'PYSEHPDA'
1323 AND action_context_id = p_assignment_action_id;
1324 EXCEPTION
1325 WHEN NO_DATA_FOUND
1326 THEN
1327 --fnd_file.put_line(fnd_file.log,'Not found In Archive record ' );
1328 fnd_file.put_line (fnd_file.LOG
1329 , 'g_payroll_action_id ' || g_payroll_action_id
1330 );
1331 pay_action_information_api.create_action_information
1332 (p_action_information_id => l_action_info_id
1333 , p_action_context_id => p_assignment_action_id
1334 , p_action_context_type => 'AAP'
1335 , p_object_version_number => l_ovn
1336 , p_effective_date => l_effective_date
1337 , p_source_id => NULL
1338 , p_source_text => NULL
1339 , p_action_information_category => 'EMEA REPORT INFORMATION'
1340 , p_action_information1 => 'PYSEHPDA'
1341 , p_action_information2 => 'PER'
1342 , p_action_information3 => g_payroll_action_id
1343 , p_action_information4 => l_employee_code
1344 , p_action_information5 => l_employee_number
1345 , p_action_information6 => l_employee_name
1346 , p_action_information7 => fnd_number.number_to_canonical
1347 (l_holiday_pay_per_day
1348 )
1349 , p_action_information8 => fnd_number.number_to_canonical
1350 (l_total_paid_days)
1351 , p_action_information9 => fnd_number.number_to_canonical
1352 (l_total_paid_days_amount
1353 )
1354 , p_action_information10 => fnd_number.number_to_canonical
1355 (l_total_saved_days)
1356 , p_action_information11 => fnd_number.number_to_canonical
1357 (l_total_saved_days_amount
1358 )
1359 , p_action_information12 => fnd_number.number_to_canonical
1360 (l_total_earned_days)
1361 , p_action_information13 => fnd_number.number_to_canonical
1362 (l_total_earned_days_amount
1363 )
1364 , p_action_information14 => l_local_unit_id_fetched
1365 , p_action_information15 => l_legal_employer_id_fetched
1366 , p_action_information16 => NULL
1367 , p_action_information17 => NULL
1368 , p_action_information18 => NULL
1369 , p_action_information19 => NULL
1370 , p_action_information20 => NULL
1371 , p_action_information21 => NULL
1372 , p_action_information22 => NULL
1373 , p_action_information23 => NULL
1374 , p_action_information24 => NULL
1375 , p_action_information25 => NULL
1376 , p_action_information26 => NULL
1377 , p_action_information27 => NULL
1378 , p_action_information28 => NULL
1379 , p_action_information29 => NULL
1380 , p_action_information30 => l_person_id
1381 , p_assignment_id => l_assignment_id
1382 );
1383 fnd_file.put_line (fnd_file.LOG
1384 , 'l_action_info_id ==> ' || l_action_info_id
1385 );
1386 fnd_file.put_line (fnd_file.LOG
1387 , 'l_action_info_id ==> ' || l_person_id
1388 );
1389 WHEN OTHERS
1390 THEN
1391 NULL;
1392 END;
1393
1394 --END IF;
1395 fnd_file.put_line (fnd_file.LOG, 'Leaving Procedure ARCHIVE_CODE');
1396
1397 IF g_debug
1398 THEN
1399 hr_utility.set_location (' Leaving Procedure ARCHIVE_CODE', 390);
1400 END IF;
1401 END archive_code;
1402
1403 --- Report XML generating code
1404 PROCEDURE writetoclob (p_xfdf_clob OUT NOCOPY CLOB)
1405 IS
1406 l_xfdf_string CLOB;
1407 l_str1 VARCHAR2 (1000);
1408 l_str2 VARCHAR2 (20);
1409 l_str3 VARCHAR2 (20);
1410 l_str4 VARCHAR2 (20);
1411 l_str5 VARCHAR2 (20);
1412 l_str6 VARCHAR2 (30);
1413 l_str7 VARCHAR2 (1000);
1414 l_str8 VARCHAR2 (240);
1415 l_str9 VARCHAR2 (240);
1416 l_str10 VARCHAR2 (20);
1417 l_str11 VARCHAR2 (20);
1418 current_index PLS_INTEGER;
1419 l_iana_charset VARCHAR2 (50);
1420 BEGIN
1421 l_iana_charset := hr_se_utility.get_iana_charset;
1422 hr_utility.set_location ('Entering WritetoCLOB ', 70);
1423 l_str1 :=
1424 '<?xml version="1.0" encoding="'
1425 || l_iana_charset
1426 || '"?> <ROOT><HPDR>';
1427 l_str2 := '<';
1428 l_str3 := '>';
1429 l_str4 := '</';
1430 l_str5 := '>';
1431 l_str6 := '</HPDR></ROOT>';
1432 l_str7 :=
1433 '<?xml version="1.0" encoding="'
1434 || l_iana_charset
1435 || '"?> <ROOT></ROOT>';
1436 l_str10 := '<HPDR>';
1437 l_str11 := '</HPDR>';
1438 DBMS_LOB.createtemporary (l_xfdf_string, FALSE, DBMS_LOB.CALL);
1439 DBMS_LOB.OPEN (l_xfdf_string, DBMS_LOB.lob_readwrite);
1440 current_index := 0;
1441
1442 IF ghpd_data.COUNT > 0
1443 THEN
1444 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str1), l_str1);
1445
1446 FOR table_counter IN ghpd_data.FIRST .. ghpd_data.LAST
1447 LOOP
1448 l_str8 := ghpd_data (table_counter).tagname;
1449 l_str9 := ghpd_data (table_counter).tagvalue;
1450
1451 IF l_str9 IN
1452 ('LEGAL_EMPLOYER'
1453 , 'LE_DETAILS'
1454 , 'EMPLOYEES'
1455 , 'PERSON'
1456 , 'LE_DETAILS_END'
1457 , 'PERSON_END'
1458 , 'EMPLOYEES_END'
1459 , 'LEGAL_EMPLOYER_END'
1460 )
1461 THEN
1462 IF l_str9 IN
1463 ('LEGAL_EMPLOYER', 'LE_DETAILS', 'EMPLOYEES', 'PERSON')
1464 THEN
1465 DBMS_LOB.writeappend (l_xfdf_string
1466 , LENGTH (l_str2)
1467 , l_str2
1468 );
1469 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1470 , l_str8);
1471 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
1472 , l_str3);
1473 ELSE
1474 DBMS_LOB.writeappend (l_xfdf_string
1475 , LENGTH (l_str4)
1476 , l_str4
1477 );
1478 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1479 , l_str8);
1480 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
1481 , l_str5);
1482 END IF;
1483 ELSE
1484 IF l_str9 IS NOT NULL
1485 THEN
1486 DBMS_LOB.writeappend (l_xfdf_string
1487 , LENGTH (l_str2)
1488 , l_str2
1489 );
1490 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1491 , l_str8);
1492 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
1493 , l_str3);
1494 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str9)
1495 , l_str9);
1496 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4)
1497 , l_str4);
1498 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1499 , l_str8);
1500 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
1501 , l_str5);
1502 ELSE
1503 DBMS_LOB.writeappend (l_xfdf_string
1504 , LENGTH (l_str2)
1505 , l_str2
1506 );
1507 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1508 , l_str8);
1509 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
1510 , l_str3);
1511 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4)
1512 , l_str4);
1513 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1514 , l_str8);
1515 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
1516 , l_str5);
1517 END IF;
1518 END IF;
1519 END LOOP;
1520
1521 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str6), l_str6);
1522 ELSE
1523 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str7), l_str7);
1524 END IF;
1525
1526 p_xfdf_clob := l_xfdf_string;
1527 hr_utility.set_location ('Leaving WritetoCLOB ', 40);
1528 EXCEPTION
1529 WHEN OTHERS
1530 THEN
1531 hr_utility.TRACE ('sqlerrm ' || SQLERRM);
1532 hr_utility.raise_error;
1533 END writetoclob;
1534
1535 PROCEDURE get_xml_for_report (
1536 p_business_group_id IN NUMBER
1537 , p_payroll_action_id IN VARCHAR2
1538 , p_template_name IN VARCHAR2
1539 , p_xml OUT NOCOPY CLOB
1540 )
1541 IS
1542 --Variables needed for the report
1543 l_counter NUMBER := 0;
1544 l_payroll_action_id pay_action_information.action_information1%TYPE;
1545
1546 --Cursors needed for report
1547 CURSOR csr_all_legal_employer (
1548 csr_v_pa_id pay_action_information.action_context_id%TYPE
1549 )
1550 IS
1551 SELECT action_information3, action_information4
1552 , action_information5
1553 FROM pay_action_information
1554 WHERE action_context_type = 'PA'
1555 AND action_context_id = csr_v_pa_id
1556 AND action_information_category = 'EMEA REPORT INFORMATION'
1557 AND action_information1 = 'PYSEHPDA'
1558 AND action_information2 = 'LE';
1559
1560 CURSOR csr_report_details (
1561 csr_v_pa_id pay_action_information.action_context_id%TYPE
1562 )
1563 IS
1564 SELECT TO_CHAR
1565 (fnd_date.canonical_to_date (action_information5)
1566 , 'YYYYMMDD'
1567 ) period_from
1568 , TO_CHAR
1569 (fnd_date.canonical_to_date (action_information6)
1570 , 'YYYYMMDD'
1571 ) period_to
1572 FROM pay_action_information
1573 WHERE action_context_type = 'PA'
1574 AND action_context_id = csr_v_pa_id
1575 AND action_information_category = 'EMEA REPORT DETAILS'
1576 AND action_information1 = 'PYSEHPDA';
1577
1578 lr_report_details csr_report_details%ROWTYPE;
1579
1580 CURSOR csr_all_employees_under_le (
1581 csr_v_pa_id pay_action_information.action_information3%TYPE
1582 , csr_v_le_id pay_action_information.action_information15%TYPE
1583 )
1584 IS
1585 SELECT *
1586 FROM pay_action_information
1587 WHERE action_context_type = 'AAP'
1588 AND action_information_category = 'EMEA REPORT INFORMATION'
1589 AND action_information1 = 'PYSEHPDA'
1590 AND action_information3 = csr_v_pa_id
1591 AND action_information2 = 'PER'
1592 AND action_information15 = csr_v_le_id
1593 ORDER BY action_information30;
1594
1595 /* End of declaration*/
1596 /* Proc to Add the tag value and Name */
1597 PROCEDURE add_tag_value (p_tag_name IN VARCHAR2, p_tag_value IN VARCHAR2)
1598 IS
1599 BEGIN
1600 ghpd_data (l_counter).tagname := p_tag_name;
1601 ghpd_data (l_counter).tagvalue := p_tag_value;
1602 l_counter := l_counter + 1;
1603 END add_tag_value;
1604 /* End of Proc to Add the tag value and Name */
1605 /* Start of GET_HPD_XML */
1606 BEGIN
1607 IF p_payroll_action_id IS NULL
1608 THEN
1609 BEGIN
1610 SELECT payroll_action_id
1611 INTO l_payroll_action_id
1612 FROM pay_payroll_actions ppa
1613 , fnd_conc_req_summary_v fcrs
1614 , fnd_conc_req_summary_v fcrs1
1615 WHERE fcrs.request_id = fnd_global.conc_request_id
1616 AND fcrs.priority_request_id = fcrs1.priority_request_id
1617 AND ppa.request_id BETWEEN fcrs1.request_id AND fcrs.request_id
1618 AND ppa.request_id = fcrs1.request_id;
1619 EXCEPTION
1620 WHEN OTHERS
1621 THEN
1622 NULL;
1623 END;
1624 ELSE
1625 l_payroll_action_id := p_payroll_action_id;
1626 fnd_file.put_line (fnd_file.LOG, 'Entered Reporting');
1627 fnd_file.put_line (fnd_file.LOG
1628 , 'p_payroll_action_id ' || p_payroll_action_id
1629 );
1630
1631 /* Structure of Xml should look like this
1632 <LE>
1633 <DETAILS>
1634 </DETAILS>
1635 <EMPLOYEES>
1636 <PERSON>
1637 </PERSON>
1638 </EMPLOYEES>
1639 </LE>
1640 */
1641 OPEN csr_report_details (l_payroll_action_id);
1642
1643 FETCH csr_report_details
1644 INTO lr_report_details;
1645
1646 CLOSE csr_report_details;
1647
1648 add_tag_value ('PERIOD_FROM', lr_report_details.period_from);
1649 add_tag_value ('PERIOD_TO', lr_report_details.period_to);
1650 fnd_file.put_line (fnd_file.LOG, 'After csr_REPORT_DETAILS ');
1651 fnd_file.put_line (fnd_file.LOG
1652 , 'PERIOD_FROM ' || lr_report_details.period_from
1653 );
1654 fnd_file.put_line (fnd_file.LOG
1655 , 'PERIOD_TO ' || lr_report_details.period_to
1656 );
1657 fnd_file.put_line (fnd_file.LOG, 'Before Csr for Legal');
1658
1659 FOR rec_all_le IN csr_all_legal_employer (l_payroll_action_id)
1660 LOOP
1661 add_tag_value ('LEGAL_EMPLOYER', 'LEGAL_EMPLOYER');
1662 add_tag_value ('LE_DETAILS', 'LE_DETAILS');
1663 add_tag_value ('LE_NAME', rec_all_le.action_information4);
1664 add_tag_value ('LE_ORG_NUM', rec_all_le.action_information5);
1665 add_tag_value ('LE_DETAILS', 'LE_DETAILS_END');
1666 add_tag_value ('EMPLOYEES', 'EMPLOYEES');
1667 fnd_file.put_line (fnd_file.LOG, '^^^^^^^^^^^^^^^^^^^^^');
1668 fnd_file.put_line (fnd_file.LOG, 'Legal Employer');
1669 fnd_file.put_line (fnd_file.LOG
1670 , 'LE ID ' || rec_all_le.action_information3
1671 );
1672 fnd_file.put_line (fnd_file.LOG
1673 , 'LE_NAME ' || rec_all_le.action_information4
1674 );
1675 fnd_file.put_line (fnd_file.LOG, '^^^^^^^^^^^^^^^^^^^^^');
1676 fnd_file.put_line (fnd_file.LOG, '^^^^^^^^^^^^^^^^^^^^^');
1677 fnd_file.put_line (fnd_file.LOG, ' Inside Person Query');
1678
1679 FOR rec_all_emp_under_le IN
1680 csr_all_employees_under_le (l_payroll_action_id
1681 , rec_all_le.action_information3
1682 )
1683 LOOP
1684 fnd_file.put_line (fnd_file.LOG
1685 , 'PERSON ID ==> '
1686 || rec_all_emp_under_le.action_information30
1687 );
1688 add_tag_value ('PERSON', 'PERSON');
1689 add_tag_value ('EMPLOYEE_CODE'
1690 , rec_all_emp_under_le.action_information4
1691 );
1692 add_tag_value ('EMPLOYEE_NUMBER'
1693 , rec_all_emp_under_le.action_information5
1694 );
1695 add_tag_value ('EMPLOYEE_NAME'
1696 , rec_all_emp_under_le.action_information6
1697 );
1698 add_tag_value
1699 ('HOLIDAY_PAY_PER_DAY'
1700 , TO_CHAR
1701 (fnd_number.canonical_to_number
1702 (rec_all_emp_under_le.action_information7)
1703 , '999999990D99'
1704 )
1705 );
1706 add_tag_value ('TOTAL_PAID_DAYS'
1707 , rec_all_emp_under_le.action_information8
1708 );
1709 add_tag_value
1710 ('TOTAL_PAID_DAYS_AMOUNT'
1711 , TO_CHAR
1712 (fnd_number.canonical_to_number
1713 (rec_all_emp_under_le.action_information9)
1714 , '999999990D99'
1715 )
1716 );
1717 add_tag_value ('TOTAL_SAVED_DAYS'
1718 , rec_all_emp_under_le.action_information10
1719 );
1720 add_tag_value
1721 ('TOTAL_SAVED_DAYS_AMOUNT'
1722 , TO_CHAR
1723 (fnd_number.canonical_to_number
1724 (rec_all_emp_under_le.action_information11)
1725 , '999999990D99'
1726 )
1727 );
1728 add_tag_value ('TOTAL_EARNED_DAYS'
1729 , rec_all_emp_under_le.action_information12
1730 );
1731 add_tag_value
1732 ('TOTAL_EARNED_DAYS_AMOUNT'
1733 , TO_CHAR
1734 (fnd_number.canonical_to_number
1735 (rec_all_emp_under_le.action_information13)
1736 , '999999990D99'
1737 )
1738 );
1739 add_tag_value ('PERSON', 'PERSON_END');
1740 END LOOP; /* For all EMPLOYEES */
1741
1742 fnd_file.put_line (fnd_file.LOG, '^^^^^^^^^^^^^^^^^^^^^');
1743 add_tag_value ('EMPLOYEES', 'EMPLOYEES_END');
1744 add_tag_value ('LEGAL_EMPLOYER', 'LEGAL_EMPLOYER_END');
1745 END LOOP; /* For all LEGAL_EMPLYER */
1746 END IF; /* for p_payroll_action_id IS NULL */
1747
1748 writetoclob (p_xml);
1749 END get_xml_for_report;
1750 END pay_se_holiday_pay_debt;