[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_ALECTA
Source
1 PACKAGE BODY pay_se_alecta AS
2 /* $Header: pysealer.pkb 120.0.12010000.2 2008/11/11 09:28:36 rmurahar ship $ */
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 (100) := 'PAY_SE_ALECTA.';
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_year NUMBER;
27 g_month VARCHAR2 (4);
28 g_sent_from VARCHAR2 (240);
29 g_sent_to VARCHAR2 (240);
30 g_production VARCHAR2 (240);
31 --End of Globals to pick up all the parameter
32 g_format_mask VARCHAR2 (50);
33 g_err_num NUMBER;
34 g_errm VARCHAR2 (150);
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 || ' get parameter ';
48 BEGIN
49 --
50 IF g_debug
51 THEN
52 hr_utility.set_location (' Entering Function GET_PARAMETER', 10);
53 END IF;
54
55 l_start_pos :=
56 INSTR (' ' || p_parameter_string, l_delimiter || p_token || '=');
57
58 --
59 IF l_start_pos = 0
60 THEN
61 l_delimiter := '|';
62 l_start_pos :=
63 INSTR (' ' || p_parameter_string, l_delimiter || p_token || '=');
64 END IF;
65
66 -- DBMS_OUTPUT.put_line (' ' || p_parameter_string);
67 -- DBMS_OUTPUT.put_line (l_delimiter || p_token || '=');
68 -- DBMS_OUTPUT.put_line (l_start_pos);
69
70 IF l_start_pos <> 0
71 THEN
72 l_start_pos := l_start_pos + LENGTH (p_token || '=');
73 -- DBMS_OUTPUT.put_line (l_start_pos);
74 l_parameter :=
75 SUBSTR (p_parameter_string
76 ,l_start_pos
77 , INSTR (p_parameter_string || ' ', ',', l_start_pos)
78 - (l_start_pos)
79 );
80 -- DBMS_OUTPUT.put_line (l_parameter);
81
82 IF p_segment_number IS NOT NULL
83 THEN
84 l_parameter := ':' || l_parameter || ':';
85 l_parameter :=
86 SUBSTR (l_parameter
87 , INSTR (l_parameter, ':', 1, p_segment_number) + 1
88 , INSTR (l_parameter, ':', 1, p_segment_number + 1)
89 - 1
90 - INSTR (l_parameter, ':', 1, p_segment_number)
91 );
92 END IF;
93 END IF;
94
95 --
96 IF g_debug
97 THEN
98 hr_utility.set_location (' Leaving Function GET_PARAMETER', 20);
99 END IF;
100
101 RETURN l_parameter;
102 END;
103
104 /* GET ALL PARAMETERS */
105 PROCEDURE get_all_parameters (
106 p_payroll_action_id IN NUMBER -- In parameter
107 ,p_business_group_id OUT NOCOPY NUMBER -- Core parameter
108 ,p_effective_date OUT NOCOPY DATE -- Core parameter
109 ,p_legal_employer_id OUT NOCOPY NUMBER -- User parameter
110 ,p_request_for_all_or_not OUT NOCOPY VARCHAR2 -- User parameter
111 ,p_year OUT NOCOPY NUMBER -- User parameter
112 ,p_month OUT NOCOPY VARCHAR2 -- User parameter
113 ,p_sent_from OUT NOCOPY VARCHAR2
114 ,p_sent_to OUT NOCOPY VARCHAR2
115 ,p_production OUT NOCOPY VARCHAR2
116 )
117 IS
118 CURSOR csr_parameter_info (p_payroll_action_id NUMBER)
119 IS
120 SELECT TO_NUMBER
121 (pay_se_alecta.get_parameter (legislative_parameters
122 ,'LEGAL_EMPLOYER'
123 )
124 ) legal
125 , (pay_se_alecta.get_parameter (legislative_parameters
126 ,'REQUEST_FOR'
127 )
128 ) request_for
129 , (pay_se_alecta.get_parameter (legislative_parameters, 'YEAR')
130 ) report_year
131 , (pay_se_alecta.get_parameter (legislative_parameters
132 ,'MONTH')
133 ) report_month
134 , (pay_se_alecta.get_parameter (legislative_parameters
135 ,'SENT_FROM'
136 )
137 ) sent_from
138 , (pay_se_alecta.get_parameter (legislative_parameters
139 ,'SENT_TO'
140 )
141 ) sent_to
142 , (pay_se_alecta.get_parameter (legislative_parameters
143 ,'PRODUCTION'
144 )
145 ) production
146 ,effective_date effective_date
147 ,business_group_id bg_id
148 FROM pay_payroll_actions
149 WHERE payroll_action_id = p_payroll_action_id;
150
151 lr_parameter_info csr_parameter_info%ROWTYPE;
152 l_proc VARCHAR2 (240)
153 := g_package || ' GET_ALL_PARAMETERS ';
154 BEGIN
155 /* fnd_file.put_line (fnd_file.LOG,
156 'Entering Procedure GET_ALL_PARAMETER '
157 );
158 fnd_file.put_line (fnd_file.LOG,
159 'Payroill Action iD ' || p_payroll_action_id
160 );
161 */
162 OPEN csr_parameter_info (p_payroll_action_id);
163
164 --FETCH csr_parameter_info into lr_parameter_info;
165 FETCH csr_parameter_info
166 INTO lr_parameter_info;
167
168 CLOSE csr_parameter_info;
169
170 p_legal_employer_id := lr_parameter_info.legal;
171 p_request_for_all_or_not := lr_parameter_info.request_for;
172 p_year := TO_NUMBER (lr_parameter_info.report_year);
173 p_month := (lr_parameter_info.report_month);
174 p_sent_from := (lr_parameter_info.sent_from);
175 p_sent_to := (lr_parameter_info.sent_to);
176 p_production := (lr_parameter_info.production);
177 p_effective_date := lr_parameter_info.effective_date;
178 p_business_group_id := lr_parameter_info.bg_id;
179 /*
180 fnd_file.put_line (fnd_file.LOG,
181 'lr_parameter_info.Legal '
182 || lr_parameter_info.legal
183 );
184 fnd_file.put_line (fnd_file.LOG,
185 'lr_parameter_info.REQUEST_FOR '
186 || lr_parameter_info.request_for
187 );
188 fnd_file.put_line (fnd_file.LOG,
189 'lr_parameter_info.YEAR ' || lr_parameter_info.REPORT_YEAR
190 );
191
192 fnd_file.put_line (fnd_file.LOG,
193 'lr_parameter_info.MONTH '
194 || lr_parameter_info.REPORT_MONTH
195 );
196
197 fnd_file.put_line (fnd_file.LOG,
198 'lr_parameter_info.Effective_date '
199 || lr_parameter_info.effective_date
200 );
201
202 fnd_file.put_line (fnd_file.LOG, 'After csr_parameter_info in ');
203 fnd_file.put_line (fnd_file.LOG,
204 'After p_legal_employer_id in '
205 || p_legal_employer_id
206 );
207 */
208 END get_all_parameters;
209
210 /* RANGE CODE */
211 PROCEDURE range_code (
212 p_payroll_action_id IN NUMBER
213 ,p_sql OUT NOCOPY VARCHAR2
214 )
215 IS
216 l_action_info_id NUMBER;
217 l_ovn NUMBER;
218 l_business_group_id NUMBER;
219 l_start_date VARCHAR2 (30);
220 l_end_date VARCHAR2 (30);
221 l_effective_date DATE;
222 l_consolidation_set NUMBER;
223 l_defined_balance_id NUMBER := 0;
224 l_count NUMBER := 0;
225 l_prev_prepay NUMBER := 0;
226 l_canonical_start_date DATE;
227 l_canonical_end_date DATE;
228 l_payroll_id NUMBER;
229 l_prepay_action_id NUMBER;
230 l_actid NUMBER;
231 l_assignment_id NUMBER;
232 l_action_sequence NUMBER;
233 l_assact_id NUMBER;
234 l_pact_id NUMBER;
235 l_flag NUMBER := 0;
236 l_element_context VARCHAR2 (5);
237
238 -- Archiving the data , as this will fire once
239 CURSOR csr_legal_employer_details (
240 csr_v_legal_employer_id hr_organization_information.organization_id%TYPE
241 )
242 IS
243 SELECT o1.NAME legal_employer_name
244 ,hoi2.org_information2 org_number
245 ,hoi1.organization_id legal_id
246 FROM hr_organization_units o1
247 ,hr_organization_information hoi1
248 ,hr_organization_information hoi2
249 WHERE o1.business_group_id = g_business_group_id
250 AND hoi1.organization_id = o1.organization_id
251 AND hoi1.organization_id =
252 NVL (csr_v_legal_employer_id, hoi1.organization_id)
253 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
254 AND hoi1.org_information_context = 'CLASS'
255 AND o1.organization_id = hoi2.organization_id
256 AND hoi2.org_information_context = 'SE_LEGAL_EMPLOYER_DETAILS';
257
258 l_legal_employer_details csr_legal_employer_details%ROWTYPE;
259
260 CURSOR csr_check_empty_le (
261 csr_v_legal_employer_id NUMBER
262 ,csr_v_canonical_start_date DATE
263 ,csr_v_canonical_end_date DATE
264 )
265 IS
266 SELECT '1'
267 FROM pay_payroll_actions appa
268 ,pay_assignment_actions act
269 ,per_all_assignments_f as1
270 ,pay_payroll_actions ppa
271 WHERE ppa.payroll_action_id = p_payroll_action_id
272 AND appa.effective_date BETWEEN csr_v_canonical_start_date
273 AND csr_v_canonical_end_date
274 AND appa.action_type IN ('R', 'Q')
275 -- Payroll Run or Quickpay Run
276 AND act.payroll_action_id = appa.payroll_action_id
277 AND act.source_action_id IS NULL -- Master Action
278 AND as1.assignment_id = act.assignment_id
279 AND as1.business_group_id = g_business_group_id
280 AND act.action_status = 'C' -- Completed
281 AND act.tax_unit_id = csr_v_legal_employer_id
282 AND appa.effective_date BETWEEN as1.effective_start_date
283 AND as1.effective_end_date
284 AND ppa.effective_date BETWEEN as1.effective_start_date
285 AND as1.effective_end_date
286 ORDER BY as1.person_id
287 ,act.assignment_id;
288
289 l_le_has_employee VARCHAR2 (2);
290 -- Archiving the data , as this will fire once
291 BEGIN
292
293 p_sql :=
294 'SELECT DISTINCT person_id
295 FROM per_people_f ppf
296 ,pay_payroll_actions ppa
297 WHERE ppa.payroll_action_id = :payroll_action_id
298 AND ppa.business_group_id = ppf.business_group_id
299 ORDER BY ppf.person_id';
300 g_payroll_action_id := p_payroll_action_id;
301 g_business_group_id := NULL;
302 g_effective_date := NULL;
303 g_pension_provider_id := NULL;
304 g_legal_employer_id := NULL;
305 g_local_unit_id := NULL;
306 pay_se_alecta.get_all_parameters (p_payroll_action_id
307 ,g_business_group_id
308 ,g_effective_date
309 ,g_legal_employer_id
310 ,g_request_for
311 ,g_year
312 ,g_month
313 ,g_sent_from
314 ,g_sent_to
315 ,g_production
316 );
317
318
319 -- *****************************************************************************
320 -- TO pick up the required details for Pension Providers
321 OPEN csr_legal_employer_details (g_legal_employer_id);
322
323 FETCH csr_legal_employer_details
324 INTO l_legal_employer_details;
325
326 CLOSE csr_legal_employer_details;
327
328 -- *****************************************************************************
329
330 -- *****************************************************************************
331 -- Insert the report Parameters
332 pay_action_information_api.create_action_information
333 (p_action_information_id => l_action_info_id
334 ,p_action_context_id => p_payroll_action_id
335 ,p_action_context_type => 'PA'
336 ,p_object_version_number => l_ovn
337 ,p_effective_date => g_effective_date
338 ,p_source_id => NULL
339 ,p_source_text => NULL
340 ,p_action_information_category => 'EMEA REPORT DETAILS'
341 ,p_action_information1 => 'PYSEALEA'
342 ,p_action_information2 => l_legal_employer_details.legal_employer_name
343 ,p_action_information3 => g_legal_employer_id
344 ,p_action_information4 => g_request_for
345 ,p_action_information5 => (g_year)
346 ,p_action_information6 => (g_month)
347 ,p_action_information7 => g_sent_from
348 ,p_action_information8 => g_sent_to
349 ,p_action_information9 => g_production
350 ,p_action_information10 => NULL
351 );
352 -- *****************************************************************************
353
354 --fnd_file.put_line(fnd_file.log,'PENSION provider name ==> '||lr_pension_provider_details.NAME );
355 --fnd_file.put_line(fnd_file.log,'PENSION provider ID ==> '||g_pension_provider_id);
356
357 --fnd_file.put_line(fnd_file.log,'Local Unit ID ==> '||g_local_unit_id);
358 --fnd_file.put_line(fnd_file.log,'acti_info_id ==> '||l_action_info_id );
359
360
361 -- *****************************************************************************
362 /* IF g_request_for = 'REQUESTING_ORG'
363 THEN
364 -- Information regarding the Legal Employer
365 OPEN csr_legal_employer_details (g_legal_employer_id);
366
367 FETCH csr_legal_employer_details
368 INTO l_legal_employer_details;
369
370 CLOSE csr_legal_employer_details;
371
372 pay_action_information_api.create_action_information
373 (p_action_information_id => l_action_info_id,
374 p_action_context_id => p_payroll_action_id,
375 p_action_context_type => 'PA',
376 p_object_version_number => l_ovn,
377 p_effective_date => g_effective_date,
378 p_source_id => NULL,
379 p_source_text => NULL,
380 p_action_information_category => 'EMEA REPORT INFORMATION',
381 p_action_information1 => 'PYSEALEA',
382 p_action_information2 => 'LE',
383 p_action_information3 => g_legal_employer_id,
384 p_action_information4 => l_legal_employer_details.legal_employer_name,
385 p_action_information5 => l_legal_employer_details.org_number,
386 p_action_information6 => NULL,
387 p_action_information7 => NULL,
388 p_action_information8 => NULL,
389 p_action_information9 => NULL,
390 p_action_information10 => NULL
391 );
392 -- *****************************************************************************
393 ELSE
394 -- *****************************************************************************
395 FOR rec_legal_employer_details IN csr_legal_employer_details (NULL)
396 LOOP
397 OPEN csr_check_empty_le (rec_legal_employer_details.legal_id,
398 g_start_date,
399 g_end_date
400 );
401
402 FETCH csr_check_empty_le
403 INTO l_le_has_employee;
404
405 CLOSE csr_check_empty_le;
406
407 IF l_le_has_employee = '1'
408 THEN
409 pay_action_information_api.create_action_information
410 (p_action_information_id => l_action_info_id,
411 p_action_context_id => p_payroll_action_id,
412 p_action_context_type => 'PA',
413 p_object_version_number => l_ovn,
414 p_effective_date => g_effective_date,
415 p_source_id => NULL,
416 p_source_text => NULL,
417 p_action_information_category => 'EMEA REPORT INFORMATION',
418 p_action_information1 => 'PYSEALEA',
419 p_action_information2 => 'LE',
420 p_action_information3 => rec_legal_employer_details.legal_id,
421 p_action_information4 => rec_legal_employer_details.legal_employer_name,
422 p_action_information5 => rec_legal_employer_details.org_number,
423 p_action_information6 => NULL,
424 p_action_information7 => NULL,
425 p_action_information8 => NULL,
426 p_action_information9 => NULL,
427 p_action_information10 => NULL
428 );
429 END IF;
430 END LOOP;
431 END IF; -- FOR G_LEGAL_EMPLOYER
432 */
433 --END IF; -- G_Archive End
434 IF g_debug
435 THEN
436 hr_utility.set_location (' Leaving Procedure RANGE_CODE', 50);
437 END IF;
438 EXCEPTION
439 WHEN OTHERS
440 THEN
441 -- Return cursor that selects no rows
442 p_sql :=
443 'select 1 from dual where to_char(:payroll_action_id) = dummy';
444 END range_code;
445
446 /* ASSIGNMENT ACTION CODE */
447 PROCEDURE assignment_action_code (
448 p_payroll_action_id IN NUMBER
449 ,p_start_person IN NUMBER
450 ,p_end_person IN NUMBER
451 ,p_chunk IN NUMBER
452 )
453 IS
454 CURSOR csr_prepaid_assignments_le (
455 p_payroll_action_id NUMBER
456 ,p_start_person NUMBER
457 ,p_end_person NUMBER
458 ,p_legal_employer_id NUMBER
459 ,l_canonical_start_date DATE
460 ,l_canonical_end_date DATE
461 )
462 IS
463 SELECT as1.person_id person_id
464 ,act.assignment_id assignment_id
465 ,act.assignment_action_id run_action_id
466 FROM pay_payroll_actions appa
467 ,pay_assignment_actions act
468 ,per_all_assignments_f as1
469 ,pay_payroll_actions ppa
470 WHERE ppa.payroll_action_id = p_payroll_action_id
471 AND appa.effective_date BETWEEN l_canonical_start_date
472 AND l_canonical_end_date
473 AND as1.person_id BETWEEN p_start_person AND p_end_person
474 AND appa.action_type IN ('R', 'Q')
475 -- Payroll Run or Quickpay Run
476 AND act.payroll_action_id = appa.payroll_action_id
477 AND act.source_action_id IS NULL -- Master Action
478 AND as1.assignment_id = act.assignment_id
479 AND as1.business_group_id = g_business_group_id
480 AND act.action_status = 'C' -- Completed
481 AND act.tax_unit_id = NVL (p_legal_employer_id, act.tax_unit_id)
482 AND appa.effective_date BETWEEN as1.effective_start_date
483 AND as1.effective_end_date
484 -- AND ppa.effective_date BETWEEN as1.effective_start_date AND as1.effective_end_date
485 ORDER BY as1.person_id
486 ,act.assignment_id;
487
488 CURSOR csr_get_defined_balance_id (
489 csr_v_balance_name ff_database_items.user_name%TYPE
490 )
491 IS
492 SELECT ue.creator_id
493 FROM ff_user_entities ue
494 ,ff_database_items di
495 WHERE di.user_name = csr_v_balance_name
496 AND ue.user_entity_id = di.user_entity_id
497 AND ue.legislation_code = 'SE'
498 AND ue.business_group_id IS NULL
499 AND ue.creator_type = 'B';
500
501 lr_get_defined_balance_id csr_get_defined_balance_id%ROWTYPE;
502 l_count NUMBER := 0;
503 l_prev_prepay NUMBER := 0;
504 l_canonical_start_date DATE;
505 l_canonical_end_date DATE;
506 l_pension_type hr_organization_information.org_information1%TYPE;
507 l_prepay_action_id NUMBER;
508 l_actid NUMBER;
509 l_assignment_id NUMBER;
510 l_action_sequence NUMBER;
511 l_assact_id NUMBER;
512 l_pact_id NUMBER;
513 l_flag NUMBER := 0;
514 l_defined_balance_id NUMBER := 0;
515 l_action_info_id NUMBER;
516 l_ovn NUMBER;
517 -- User pARAMETERS needed
518 l_business_group_id NUMBER;
519 l_effective_date DATE;
520 l_pension_provider_id NUMBER;
521 l_legal_employer_id NUMBER;
522 l_local_unit_id NUMBER;
523 l_archive VARCHAR2 (10);
524 -- End of User pARAMETERS needed
525 BEGIN
526 -- fnd_file.put_line (fnd_file.LOG, ' ASSIGNMENT_ACTION_CODE ');
527 pay_se_alecta.get_all_parameters (p_payroll_action_id
528 ,g_business_group_id
529 ,g_effective_date
530 ,g_legal_employer_id
531 ,g_request_for
532 ,g_year
533 ,g_month
534 ,g_sent_from
535 ,g_sent_to
536 ,g_production
537 );
538 --fnd_file.put_line(fnd_file.log,' g_year '|| g_year);
539 --fnd_file.put_line(fnd_file.log,' g_month '|| g_month);
540 l_canonical_start_date :=
541 fnd_date.string_to_date (('01-' || TRUNC (g_month) || '-' || g_year)
542 ,'DD-MM-YYYY'
543 );
544 l_canonical_end_date := LAST_DAY (l_canonical_start_date);
545 l_prepay_action_id := 0;
546 --fnd_file.put_line(fnd_file.log,' g_local_unit_id '|| g_local_unit_id);
547
548 --fnd_file.put_line(fnd_file.log,' INSIDE IF LOCAL UNIT NOT NULL ');
549 /*
550 fnd_file.put_line (fnd_file.LOG,
551 ' p_payroll_action_id ==> ' || p_payroll_action_id
552 );
553 fnd_file.put_line (fnd_file.LOG,
554 ' g_legal_employer_id ==> ' || g_legal_employer_id
555 );
556 fnd_file.put_line (fnd_file.LOG,
557 ' g_effective_date ==> ' || g_effective_date
558 );
559 fnd_file.put_line (fnd_file.LOG,
560 ' l_canonical_start_date ==> '
561 || l_canonical_start_date
562 );
563 fnd_file.put_line (fnd_file.LOG,
564 ' l_canonical_end_date ==> ' || l_canonical_end_date
565 );
566 */
567 --fnd_file.put_line(fnd_file.log,' INSIDE ELS LOCAL UNIT NULL ');
568 l_assignment_id := 0;
569
570 FOR rec_prepaid_assignments IN
571 csr_prepaid_assignments_le (p_payroll_action_id
572 ,p_start_person
573 ,p_end_person
574 ,g_legal_employer_id
575 ,l_canonical_start_date
576 ,l_canonical_end_date
577 )
578 LOOP
579 IF l_assignment_id <> rec_prepaid_assignments.assignment_id
580 THEN
581 SELECT pay_assignment_actions_s.NEXTVAL
582 INTO l_actid
583 FROM DUAL;
584
585 -- Create the archive assignment action
586 hr_nonrun_asact.insact (l_actid
587 ,rec_prepaid_assignments.assignment_id
588 ,p_payroll_action_id
589 ,p_chunk
590 ,NULL
591 );
592 -- Create archive to prepayment assignment action interlock
593 --
594 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.prepaid_action_id);
595 END IF;
596
597 -- create archive to master assignment action interlock
598 --hr_nonrun_asact.insint(l_actid,rec_prepaid_assignments.run_action_id);
599 l_assignment_id := rec_prepaid_assignments.assignment_id;
600 END LOOP;
601 /*
602 fnd_file.put_line
603 (fnd_file.LOG,
604 ' After Ending Assignment Act Code the Locking Cursor '
605 );
606 */
607 EXCEPTION
608 WHEN OTHERS
609 THEN
610 IF g_debug
611 THEN
612 hr_utility.set_location ('error raised assignment_action_code '
613 ,5
614 );
615 END IF;
616
617 RAISE;
618 END assignment_action_code;
619
620 /*fffffffffffffffffffffffffff*/
621
622 /* INITIALIZATION CODE */
623 PROCEDURE initialization_code (p_payroll_action_id IN NUMBER)
624 IS
625 l_action_info_id NUMBER;
626 l_ovn NUMBER;
627 l_count NUMBER := 0;
628 l_business_group_id NUMBER;
629 l_start_date VARCHAR2 (20);
630 l_end_date VARCHAR2 (20);
631 l_effective_date DATE;
632 l_payroll_id NUMBER;
633 l_consolidation_set NUMBER;
634 l_prev_prepay NUMBER := 0;
635
636 CURSOR csr_get_all_legal_employer_id
637 IS
638 SELECT o.organization_id
639 FROM hr_all_organization_units o
640 ,hr_organization_information hoi1
641 WHERE o.business_group_id = g_business_group_id
642 AND hoi1.organization_id = o.organization_id
643 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
644 AND hoi1.org_information_context = 'CLASS'
645 /* AND o.organization_id =
646 DECODE (g_request_for
647 ,'ALL_ORG', o.organization_id
648 ,g_legal_employer_id
649 ) */
650 ;
651
652 CURSOR csr_get_all_fields
653 IS
654 SELECT h.lookup_code
655 FROM hr_lookups h
656 WHERE h.lookup_type = 'HR_SE_ALECTA_FIELDS'
657 AND h.enabled_flag = 'Y'
658 ORDER BY h.meaning;
659
660 CURSOR csr_get_all_events (csr_v_fields VARCHAR2)
661 IS
662 SELECT h.lookup_code
663 FROM hr_lookups h
664 WHERE h.lookup_type = 'HR_SE_EVENT'
665 AND h.enabled_flag = 'Y'
666 AND ( ( SUBSTR (csr_v_fields, 1, 2) = 'ET'
667 AND (lookup_code <> 'FK')
668 )
669 OR ( SUBSTR (csr_v_fields, 1, 2) = 'MS'
670 AND lookup_code IN ('IN', 'FK', 'LO')
671 )
672 OR ( SUBSTR (csr_v_fields, 1, 2) = 'TR'
673 AND lookup_code IN ('AV2', 'AV3', 'AV4')
674 )
675 OR ( SUBSTR (csr_v_fields, 1, 2) = 'PL'
676 AND (lookup_code = 'AV2')
677 )
678 )
679 ORDER BY h.meaning;
680
681 CURSOR csr_get_le_event_info (
682 csr_v_le NUMBER
683 ,csr_v_name VARCHAR2
684 ,csr_v_event VARCHAR2
685 )
686 IS
687 SELECT o.organization_id
688 ,hoi3.org_information1
689 ,hoi3.org_information2
690 ,hoi3.org_information3
691 ,hoi3.org_information4
692 ,hoi3.org_information5
693 ,hoi3.org_information6
694 ,hoi3.org_information7
695 ,hoi3.org_information8
696 FROM hr_all_organization_units o
697 ,hr_organization_information hoi1
698 ,hr_organization_information hoi3
699 WHERE o.business_group_id = g_business_group_id
700 AND hoi1.organization_id = o.organization_id
701 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
702 AND hoi1.org_information_context = 'CLASS'
703 AND o.organization_id = csr_v_le
704 AND o.organization_id = hoi3.organization_id
705 AND hoi3.org_information_context = 'SE_ALECTA_MAPPING'
706 AND hoi3.org_information1 = csr_v_name
707 AND hoi3.org_information2 = csr_v_event;
708
709 lr_get_le_event_info csr_get_le_event_info%ROWTYPE;
710 l_temp_counter VARCHAR2 (200);
711 l_temp_field VARCHAR2 (200);
712 l_temp_event VARCHAR2 (200);
713 BEGIN
714 IF g_debug
715 THEN
716 hr_utility.set_location (' Entering Procedure INITIALIZATION_CODE'
717 ,80
718 );
719 END IF;
720
721
722 g_payroll_action_id := p_payroll_action_id;
723 g_business_group_id := NULL;
724 g_effective_date := NULL;
725 g_legal_employer_id := NULL;
726 pay_se_alecta.get_all_parameters (p_payroll_action_id
727 ,g_business_group_id
728 ,g_effective_date
729 ,g_legal_employer_id
730 ,g_request_for
731 ,g_year
732 ,g_month
733 ,g_sent_from
734 ,g_sent_to
735 ,g_production
736 );
737
738 g_start_date :=
739 fnd_date.string_to_date (('01-' || TRUNC (g_month) || '-' || g_year)
740 ,'DD-MM-YYYY'
741 );
742 g_end_date := LAST_DAY (g_start_date);
743
744 l_count := 1;
745
746 FOR row_legal_emp_id IN csr_get_all_legal_employer_id
747 LOOP
748
749 record_legal_employer (row_legal_emp_id.organization_id).organization_id :=
750 row_legal_emp_id.organization_id;
751
752 FOR row_get_all_fields IN csr_get_all_fields
753 LOOP
754 record_legal_employer (row_legal_emp_id.organization_id).field_code
755 (row_get_all_fields.lookup_code).disp_name :=
756 row_get_all_fields.lookup_code;
757 lr_get_le_event_info := NULL;
758
759 FOR row_get_all_events IN
760 csr_get_all_events (row_get_all_fields.lookup_code)
761 LOOP
762 record_legal_employer (row_legal_emp_id.organization_id).field_code
763 (row_get_all_fields.lookup_code).events_row
764 (row_get_all_events.lookup_code).event_code :=
765 row_get_all_events.lookup_code;
766
767 OPEN csr_get_le_event_info (row_legal_emp_id.organization_id
768 ,row_get_all_fields.lookup_code
769 ,row_get_all_events.lookup_code
770 );
771
772 FETCH csr_get_le_event_info
773 INTO lr_get_le_event_info;
774
775 CLOSE csr_get_le_event_info;
776
777 record_legal_employer (row_legal_emp_id.organization_id).field_code
778 (row_get_all_fields.lookup_code).events_row
779 (row_get_all_events.lookup_code).bal_ele :=
780 lr_get_le_event_info.org_information3;
781 record_legal_employer (row_legal_emp_id.organization_id).field_code
782 (row_get_all_fields.lookup_code).events_row
783 (row_get_all_events.lookup_code).balance_type_id :=
784 lr_get_le_event_info.org_information8;
785 record_legal_employer (row_legal_emp_id.organization_id).field_code
786 (row_get_all_fields.lookup_code).events_row
787 (row_get_all_events.lookup_code).element_type_id :=
788 lr_get_le_event_info.org_information5;
789 record_legal_employer (row_legal_emp_id.organization_id).field_code
790 (row_get_all_fields.lookup_code).events_row
791 (row_get_all_events.lookup_code).input_value_id :=
792 lr_get_le_event_info.org_information6;
793 lr_get_le_event_info:=NULL;
794 END LOOP;
795 END LOOP;
796 END LOOP;
797 /*
798 l_temp_counter := RECORD_LEGAL_EMPLOYER.FIRST;
799
800 WHILE l_temp_counter IS NOT NULL
801 LOOP
802 logger ('EACH LE' ,RECORD_LEGAL_EMPLOYER (l_temp_counter).organization_id );
803 l_temp_field := RECORD_LEGAL_EMPLOYER(l_temp_counter).FIELD_CODE.FIRST;
804 while l_temp_field IS NOT NULL
805 Loop
806 logger ('FIELD ' ,RECORD_LEGAL_EMPLOYER(l_temp_counter).
807 FIELD_CODE(l_temp_field).DISP_NAME );
808
809 l_temp_event :=RECORD_LEGAL_EMPLOYER(l_temp_counter).
810 FIELD_CODE(l_temp_field).
811 EVENTS_ROW.FIRST;
812 while l_temp_event IS NOT NULL
813 LOOP
814 logger (' EVENT' ,RECORD_LEGAL_EMPLOYER(l_temp_counter).
815 FIELD_CODE(l_temp_field).
816 EVENTS_ROW(l_temp_event).EVENT_CODE );
817 l_temp_event :=RECORD_LEGAL_EMPLOYER(l_temp_counter).FIELD_CODE(l_temp_field).
818 EVENTS_ROW.NEXT(l_temp_event);
819 END LOOP;
820 l_temp_field :=RECORD_LEGAL_EMPLOYER(l_temp_counter).FIELD_CODE.NEXT(l_temp_field);
821 END LOOP;
822 l_temp_counter := RECORD_LEGAL_EMPLOYER.NEXT (l_temp_counter);
823 END LOOP;
824 */
825 EXCEPTION
826 WHEN OTHERS
827 THEN
828 g_err_num := SQLCODE;
829
830 IF g_debug
831 THEN
832 hr_utility.set_location ( 'ORA_ERR: '
833 || g_err_num
834 || 'In INITIALIZATION_CODE'
835 ,180
836 );
837 END IF;
838 END initialization_code;
839
840 /* GET DEFINED BALANCE ID */
841 FUNCTION get_defined_balance_id (p_user_name IN VARCHAR2)
842 RETURN NUMBER
843 IS
844 /* Cursor to retrieve Defined Balance Id */
845 CURSOR csr_def_bal_id (p_user_name VARCHAR2)
846 IS
847 SELECT u.creator_id
848 FROM ff_user_entities u
849 ,ff_database_items d
850 WHERE d.user_name = p_user_name
851 AND u.user_entity_id = d.user_entity_id
852 AND (u.legislation_code = 'SE')
853 AND (u.business_group_id IS NULL)
854 AND u.creator_type = 'B';
855
856 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
857 BEGIN
858 IF g_debug
859 THEN
860 hr_utility.set_location
861 (' Entering Function GET_DEFINED_BALANCE_ID'
862 ,240
863 );
864 END IF;
865
866 OPEN csr_def_bal_id (p_user_name);
867
868 FETCH csr_def_bal_id
869 INTO l_defined_balance_id;
870
871 CLOSE csr_def_bal_id;
872
873 RETURN l_defined_balance_id;
874
875 IF g_debug
876 THEN
877 hr_utility.set_location (' Leaving Function GET_DEFINED_BALANCE_ID'
878 ,250
879 );
880 END IF;
881 END get_defined_balance_id;
882
883 FUNCTION get_defined_balance_value (
884 p_balance_type_id IN NUMBER
885 ,p_dimension IN VARCHAR2
886 ,p_in_assignment_id IN NUMBER
887 ,p_in_virtual_date IN DATE
888 )
889 RETURN NUMBER
890 IS
891 CURSOR csr_defined_balance_id
892 IS
893 SELECT db.defined_balance_id
894 FROM pay_defined_balances db
895 ,pay_balance_dimensions bd
896 WHERE db.balance_type_id = p_balance_type_id
897 AND db.balance_dimension_id = bd.balance_dimension_id
898 AND bd.database_item_suffix = p_dimension
899 AND bd.legislation_code = 'SE';
900
901 l_defined_balance_id ff_user_entities.user_entity_id%TYPE;
902 l_return_balance_value NUMBER;
903 BEGIN
904 IF p_balance_type_id IS NOT NULL
905 THEN
906 -- pay_balance_pkg.set_context ('TAX_UNIT_ID', p_tax_unit_id);
907 -- pay_balance_pkg.set_context ('LOCAL_UNIT_ID', p_local_unit_id);
908 OPEN csr_defined_balance_id;
909
910 FETCH csr_defined_balance_id
911 INTO l_defined_balance_id;
912
913 CLOSE csr_defined_balance_id;
914
915 l_return_balance_value :=
916 TO_CHAR
917 (pay_balance_pkg.get_value
918 (p_defined_balance_id => l_defined_balance_id
919 ,p_assignment_id => p_in_assignment_id
920 ,p_virtual_date => p_in_virtual_date
921 )
922 ,'999999999D99'
923 );
924 ELSE
925 l_return_balance_value := 0;
926 END IF;
927
928 RETURN l_return_balance_value;
929 END get_defined_balance_value;
930
931 /* ARCHIVE CODE */
932 PROCEDURE archive_code (
933 p_assignment_action_id IN NUMBER
934 ,p_effective_date IN DATE
935 )
936 IS
937 CURSOR csr_get_defined_balance_id (
938 csr_v_balance_name ff_database_items.user_name%TYPE
939 )
940 IS
941 SELECT ue.creator_id
942 FROM ff_user_entities ue
943 ,ff_database_items di
944 WHERE di.user_name = csr_v_balance_name
945 AND ue.user_entity_id = di.user_entity_id
946 AND ue.legislation_code = 'SE'
947 AND ue.business_group_id IS NULL
948 AND ue.creator_type = 'B';
949
950 lr_get_defined_balance_id csr_get_defined_balance_id%ROWTYPE;
951 l_actid NUMBER;
952 l_end_date per_time_periods.end_date%TYPE;
953 l_date_earned pay_payroll_actions.date_earned%TYPE;
954 l_effective_date pay_payroll_actions.effective_date%TYPE;
955 l_start_date per_time_periods.start_date%TYPE;
956 l_action_info_id pay_action_information.action_information_id%TYPE;
957 l_ovn pay_action_information.object_version_number%TYPE;
958 l_flag NUMBER := 0;
959 -- The place for Variables which fetches the values to be archived
960 l_employee_number VARCHAR2 (240);
961 l_employee_code VARCHAR2 (240);
962 l_new_entry VARCHAR2 (240);
963 l_moving_company VARCHAR2 (240);
964 l_new_salary VARCHAR2 (240);
965 l_withdrawal VARCHAR2 (240);
966 l_organization_number VARCHAR2 (240);
967 l_cost_centre VARCHAR2 (240);
968 l_agreement_plan_id VARCHAR2 (240);
969 l_employee_pin VARCHAR2 (240);
970 l_time_for_event_in DATE;
971 l_time_for_event_fk DATE;
972 l_time_for_event_lo DATE;
973 l_time_for_event_av DATE;
974 l_last_name VARCHAR2 (240);
975 l_first_name VARCHAR2 (240);
976 l_before_after VARCHAR2 (240);
977 l_monthly_salary_in NUMBER := 0;
978 l_monthly_salary_fk NUMBER := 0;
979 l_monthly_salary_lo NUMBER := 0;
980 l_fully_capable_of_work VARCHAR2 (20);
981 l_inability_to_work VARCHAR2 (20);
982 l_prev_organization_number VARCHAR2 (100);
983 l_curr_organization_number VARCHAR2 (100);
984 l_prev_cost_center VARCHAR2 (240);
985 l_curr_cost_center VARCHAR2 (240);
986 l_reason_for_termination VARCHAR2 (240);
987 l_yearly_salary_in NUMBER := 0;
988 l_yearly_salary_fk NUMBER := 0;
989 l_yearly_salary_lo NUMBER := 0;
990 l_salary_cut VARCHAR2 (240);
991 l_start_parental_leave DATE;
992 l_action_id VARCHAR2 (2);
993 l_local_unit_id_fetched NUMBER;
994 l_eit_local_unit NUMBER;
995 l_legal_employer_id_fetched NUMBER;
996 -- Temp needed Variables
997 l_person_id per_all_people_f.person_id%TYPE;
998 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
999
1000 -- Temp needed Variables
1001
1002 -- End of place for Variables which fetches the values to be archived
1003
1004 -- The place for Cursor which fetches the values to be archived
1005
1006 --
1007 -- Cursor to pick up
1008
1009 /* Cursor to retrieve Person Details */
1010 CURSOR csr_get_assignment_id (p_asg_act_id NUMBER)
1011 IS
1012 SELECT pac.assignment_id
1013 FROM pay_assignment_actions pac
1014 WHERE pac.assignment_action_id = p_asg_act_id;
1015
1016 CURSOR csr_get_person_details (p_asg_act_id NUMBER)
1017 IS
1018 SELECT pap.last_name
1019 ,pap.pre_name_adjunct
1020 ,pap.first_name
1021 ,pap.national_identifier
1022 ,pap.person_id
1023 ,pac.assignment_id
1024 ,paa.assignment_number
1025 ,paa.employee_category
1026 ,paa.effective_start_date
1027 FROM pay_assignment_actions pac
1028 ,per_all_assignments_f paa
1029 ,per_all_people_f pap
1030 WHERE pac.assignment_action_id = p_asg_act_id
1031 AND paa.assignment_id = pac.assignment_id
1032 AND paa.person_id = pap.person_id
1033 AND pap.per_information_category = 'SE'
1034 AND p_effective_date BETWEEN pap.effective_start_date
1035 AND pap.effective_end_date
1036 AND p_effective_date BETWEEN paa.effective_start_date
1037 AND paa.effective_end_date;
1038
1039 lr_get_person_details csr_get_person_details%ROWTYPE;
1040
1041 -- Cursor to pick up segment2
1042 CURSOR csr_get_segment2
1043 IS
1044 SELECT scl.segment2
1045 ,scl.segment8
1046 FROM per_all_assignments_f paa
1047 ,hr_soft_coding_keyflex scl
1048 ,pay_assignment_actions pasa
1049 WHERE pasa.assignment_action_id = p_assignment_action_id
1050 AND pasa.assignment_id = paa.assignment_id
1051 AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
1052 -- AND paa.primary_flag = 'Y'
1053 AND p_effective_date BETWEEN paa.effective_start_date
1054 AND paa.effective_end_date;
1055
1056 lr_get_segment2 csr_get_segment2%ROWTYPE;
1057
1058 -- Cursor to pick up LEGAL EMPLOYER
1059 CURSOR csr_find_legal_employer (
1060 csr_v_organization_id hr_organization_information.organization_id%TYPE
1061 )
1062 IS
1063 SELECT hoi3.organization_id legal_id
1064 FROM hr_all_organization_units o1
1065 ,hr_organization_information hoi1
1066 ,hr_organization_information hoi2
1067 ,hr_organization_information hoi3
1068 WHERE o1.business_group_id = g_business_group_id
1069 AND hoi1.organization_id = o1.organization_id
1070 AND hoi1.organization_id = csr_v_organization_id
1071 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
1072 AND hoi1.org_information_context = 'CLASS'
1073 AND o1.organization_id = hoi2.org_information1
1074 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
1075 AND hoi2.organization_id = hoi3.organization_id
1076 AND hoi3.org_information_context = 'CLASS'
1077 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER';
1078
1079 lr_find_legal_employer csr_find_legal_employer%ROWTYPE;
1080
1081 -- Cursor to pick up Local Unit Details
1082 CURSOR csr_local_unit_details (
1083 csr_v_local_unit_id hr_organization_information.organization_id%TYPE
1084 )
1085 IS
1086 SELECT o1.NAME
1087 ,hoi2.org_information1
1088 FROM hr_organization_units o1
1089 ,hr_organization_information hoi1
1090 ,hr_organization_information hoi2
1091 WHERE o1.business_group_id = g_business_group_id
1092 AND hoi1.organization_id = o1.organization_id
1093 AND hoi1.organization_id = csr_v_local_unit_id
1094 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
1095 AND hoi1.org_information_context = 'CLASS'
1096 AND o1.organization_id = hoi2.organization_id
1097 AND hoi2.org_information_context = 'SE_LOCAL_UNIT_DETAILS';
1098
1099 lr_local_unit_details csr_local_unit_details%ROWTYPE;
1100
1101 CURSOR csr_new_joinee (csr_v_assignment_id NUMBER)
1102 IS
1103 SELECT COUNT ('1') "RECORD_FOUND"
1104 FROM per_all_assignments_f
1105 WHERE assignment_id = csr_v_assignment_id
1106 AND business_group_id = g_business_group_id
1107 AND effective_start_date < g_start_date;
1108
1109 lr_new_joinee csr_new_joinee%ROWTYPE;
1110
1111 CURSOR csr_agreement_plan (csr_v_assignment_id NUMBER)
1112 IS
1113 SELECT hr_general.decode_lookup ('SE_AGREEMENT_PLAN'
1114 ,aei_information1
1115 ) "AGREEMENT"
1116 FROM per_assignment_extra_info
1117 WHERE assignment_id = csr_v_assignment_id
1118 AND information_type = 'SE_ALECTA_DETAILS';
1119
1120 CURSOR csr_get_joining_date (csr_v_assignment_id NUMBER)
1121 IS
1122 SELECT effective_start_date
1123 ,effective_end_date
1124 FROM per_all_assignments_f
1125 WHERE assignment_id = csr_v_assignment_id
1126 AND business_group_id = g_business_group_id
1127 AND effective_start_date >= g_start_date
1128 AND effective_start_date <= g_end_date
1129 --AND rownum =1
1130 ORDER BY effective_start_date ASC;
1131
1132 lr_get_joining_date csr_get_joining_date%ROWTYPE;
1133
1134 CURSOR csr_get_person_id (csr_v_assignment_id NUMBER)
1135 IS
1136 SELECT paaf.person_id
1137 FROM per_all_assignments_f paaf
1138 WHERE paaf.assignment_id = csr_v_assignment_id
1139 AND paaf.effective_start_date <= g_end_date;
1140
1141 lr_get_person_id csr_get_person_id%ROWTYPE;
1142
1143 CURSOR csr_get_assignments (csr_v_assignment_id NUMBER)
1144 IS
1145 SELECT GREATEST (effective_start_date, g_start_date)
1146 effective_start_date
1147 ,LEAST (effective_end_date, g_end_date) effective_end_date
1148 FROM per_all_assignments_f
1149 WHERE assignment_id = csr_v_assignment_id
1150 AND business_group_id = g_business_group_id
1151 AND effective_start_date BETWEEN g_start_date AND g_end_date
1152 ORDER BY effective_end_date DESC;
1153
1154 lr_get_assignments csr_get_assignments%ROWTYPE;
1155 -- End of Cursors
1156 l_current_local_unit NUMBER;
1157 l_previous_local_unit NUMBER;
1158 l_current_location_id NUMBER;
1159 l_previous_location_id NUMBER;
1160 l_current_legal_employer NUMBER;
1161 l_previous_legal_employer NUMBER;
1162 l_joining_date DATE;
1163 l_period_start_date DATE;
1164 l_period_end_date DATE;
1165 -- Cursor to pick up the Absence details
1166 --#########################################
1167
1168 -- End of place for Cursor which fetches the values to be archived
1169 BEGIN
1170
1171
1172
1173 -- *****************************************************************************
1174 -- TO pick up Assignmnet ID
1175 OPEN csr_get_assignment_id (p_assignment_action_id);
1176
1177 FETCH csr_get_assignment_id
1178 INTO l_assignment_id;
1179
1180 CLOSE csr_get_assignment_id;
1181
1182
1183
1184 OPEN csr_get_person_id (l_assignment_id);
1185
1186 FETCH csr_get_person_id
1187 INTO lr_get_person_id;
1188
1189 CLOSE csr_get_person_id;
1190
1191
1192 l_person_id := lr_get_person_id.person_id;
1193
1194 OPEN csr_agreement_plan (l_assignment_id);
1195
1196 FETCH csr_agreement_plan
1197 INTO l_agreement_plan_id;
1198
1199 CLOSE csr_agreement_plan;
1200
1201
1202
1203 OPEN csr_new_joinee (l_assignment_id);
1204
1205 FETCH csr_new_joinee
1206 INTO lr_new_joinee;
1207
1208 CLOSE csr_new_joinee;
1209
1210 IF lr_new_joinee.record_found <= 0
1211 THEN
1212 l_new_entry := 'IN';
1213 ELSE
1214 l_new_entry := NULL;
1215 END IF;
1216
1217
1218
1219 IF l_new_entry = 'IN'
1220 THEN
1221
1222
1223 OPEN csr_get_joining_date (l_assignment_id);
1224
1225 FETCH csr_get_joining_date
1226 INTO lr_get_joining_date;
1227
1228 CLOSE csr_get_joining_date;
1229
1230 l_joining_date := lr_get_joining_date.effective_start_date;
1231 get_assignment_lvl_info (l_assignment_id
1232 ,l_joining_date
1233 ,l_organization_number
1234 ,l_cost_centre
1235 );
1236 get_person_lvl_info (l_assignment_id
1237 ,l_joining_date
1238 ,l_employee_pin
1239 ,l_first_name
1240 ,l_last_name
1241 ,l_before_after
1242 );
1243
1244 get_in_time_of_event (l_assignment_id
1245 ,l_joining_date
1246 ,l_time_for_event_in
1247 );
1248
1249 get_salary (l_assignment_id
1250 ,l_joining_date
1251 ,l_before_after
1252 ,l_new_entry
1253 ,l_monthly_salary_in
1254 ,l_yearly_salary_in
1255 );
1256
1257 get_absence_lvl_info (l_assignment_id
1258 ,l_joining_date
1259 ,l_fully_capable_of_work
1260 ,l_inability_to_work
1261 );
1262
1263
1264
1265 END IF;
1266
1267 /* **********************************************************************************/
1268 IF l_new_entry IS NOT NULL
1269 THEN
1270 pay_action_information_api.create_action_information
1271 (p_action_information_id => l_action_info_id
1272 ,p_action_context_id => p_assignment_action_id
1273 ,p_action_context_type => 'AAP'
1274 ,p_object_version_number => l_ovn
1275 ,p_effective_date => l_effective_date
1276 ,p_source_id => NULL
1277 ,p_source_text => NULL
1278 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1279 ,p_action_information1 => 'PYSEALEA'
1280 ,p_action_information2 => 'PER'
1281 ,p_action_information3 => g_payroll_action_id
1282 ,p_action_information4 => l_new_entry
1283 ,p_action_information5 => l_organization_number
1284 ,p_action_information6 => l_cost_centre
1285 ,p_action_information7 => l_agreement_plan_id
1286 ,p_action_information8 => l_employee_pin
1287 ,p_action_information9 => fnd_date.date_to_canonical
1288 (l_time_for_event_in)
1289 ,p_action_information10 => l_last_name
1290 ,p_action_information11 => l_first_name
1291 ,p_action_information12 => l_before_after
1292 ,p_action_information13 => fnd_number.number_to_canonical
1293 (l_yearly_salary_in)
1294 ,p_action_information14 => fnd_number.number_to_canonical
1295 (l_monthly_salary_in)
1296 ,p_action_information15 => l_fully_capable_of_work
1297 ,p_action_information16 => /*NVL */(l_inability_to_work)
1298 /*,'0'
1299 ) */
1300 ,p_action_information17 => NULL
1301 ,p_action_information18 => NULL
1302 ,p_action_information19 => NULL
1303 ,p_action_information20 => NULL
1304 ,p_action_information21 => NULL
1305 ,p_action_information22 => NULL
1306 ,p_action_information23 => NULL
1307 ,p_action_information24 => NULL
1308 ,p_action_information25 => NULL
1309 ,p_action_information26 => NULL
1310 ,p_action_information27 => NULL
1311 ,p_action_information28 => NULL
1312 ,p_action_information29 => NULL
1313 ,p_action_information30 => l_person_id
1314 ,p_assignment_id => l_assignment_id
1315 );
1316
1317 END IF;
1318
1319 /* *****************************************************************************/
1320 get_salary_change_or_not (l_assignment_id
1321 ,l_new_salary
1322 ,l_time_for_event_lo
1323 );
1324
1325 IF l_new_salary IS NOT NULL
1326 AND l_new_salary = 'LO'
1327 AND l_new_entry IS NULL
1328 THEN
1329
1330 get_assignment_lvl_info (l_assignment_id
1331 ,g_end_date
1332 ,l_organization_number
1333 ,l_cost_centre
1334 );
1335
1336 get_person_lvl_info (l_assignment_id
1337 ,g_end_date
1338 ,l_employee_pin
1339 ,l_first_name
1340 ,l_last_name
1341 ,l_before_after
1342 );
1343
1344 get_salary (l_assignment_id
1345 ,g_end_date
1346 ,l_before_after
1347 ,l_new_salary
1348 ,l_monthly_salary_lo
1349 ,l_yearly_salary_lo
1350 );
1351
1352 IF l_before_after = 'BEFORE'
1353 THEN
1354 get_salary_cut (l_assignment_id, g_end_date, l_salary_cut);
1355 ELSE
1356 l_salary_cut := NULL;
1357 END IF;
1358
1359
1360 get_absence_lvl_info (l_assignment_id
1361 ,g_end_date
1362 ,l_fully_capable_of_work
1363 ,l_inability_to_work
1364 );
1365
1366
1367 END IF;
1368
1369 /* **********************************************************************************/
1370 IF l_new_salary IS NOT NULL
1371 THEN
1372 pay_action_information_api.create_action_information
1373 (p_action_information_id => l_action_info_id
1374 ,p_action_context_id => p_assignment_action_id
1375 ,p_action_context_type => 'AAP'
1376 ,p_object_version_number => l_ovn
1377 ,p_effective_date => l_effective_date
1378 ,p_source_id => NULL
1379 ,p_source_text => NULL
1380 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1381 ,p_action_information1 => 'PYSEALEA'
1382 ,p_action_information2 => 'PER'
1383 ,p_action_information3 => g_payroll_action_id
1384 ,p_action_information4 => l_new_salary
1385 ,p_action_information5 => l_organization_number
1386 ,p_action_information6 => l_cost_centre
1387 ,p_action_information7 => l_employee_pin
1388 ,p_action_information8 => fnd_date.date_to_canonical
1389 (l_time_for_event_lo)
1390 ,p_action_information9 => l_before_after
1391 ,p_action_information10 => fnd_number.number_to_canonical
1392 (l_yearly_salary_lo)
1393 ,p_action_information11 => fnd_number.number_to_canonical
1394 (l_monthly_salary_lo)
1395 ,p_action_information12 => l_salary_cut
1396 ,p_action_information13 => /*NVL*/ (l_inability_to_work)
1397 /*,'0'
1398 ) */
1399 ,p_action_information14 => NULL
1400 ,p_action_information15 => NULL
1401 ,p_action_information16 => NULL
1402 ,p_action_information17 => NULL
1403 ,p_action_information18 => NULL
1404 ,p_action_information19 => NULL
1405 ,p_action_information20 => NULL
1406 ,p_action_information21 => NULL
1407 ,p_action_information22 => NULL
1408 ,p_action_information23 => NULL
1409 ,p_action_information24 => NULL
1410 ,p_action_information25 => NULL
1411 ,p_action_information26 => NULL
1412 ,p_action_information27 => NULL
1413 ,p_action_information28 => NULL
1414 ,p_action_information29 => NULL
1415 ,p_action_information30 => l_person_id
1416 ,p_assignment_id => l_assignment_id
1417 );
1418
1419 END IF;
1420
1421 /* *****************************************************************************/
1422
1423 FOR row_get_assignments IN csr_get_assignments (l_assignment_id)
1424 LOOP
1425
1426 l_current_local_unit := NULL;
1427 l_current_legal_employer := NULL;
1428 l_current_location_id := NULL;
1429 l_previous_local_unit := NULL;
1430 l_previous_legal_employer := NULL;
1431 l_previous_location_id := NULL;
1432 get_org_lvl_info (l_assignment_id
1433 ,row_get_assignments.effective_start_date
1434 ,l_current_local_unit
1435 ,l_current_legal_employer
1436 ,l_current_location_id
1437 );
1438 get_org_lvl_info (l_assignment_id
1439 , (row_get_assignments.effective_start_date) - 1
1440 ,l_previous_local_unit
1441 ,l_previous_legal_employer
1442 ,l_previous_location_id
1443 );
1444
1445 l_moving_company := NULL;
1446
1447 IF l_current_legal_employer <> l_previous_legal_employer
1448 THEN
1449 l_moving_company := 'FK';
1450 l_time_for_event_fk := row_get_assignments.effective_start_date;
1451 get_assignment_lvl_info (l_assignment_id
1452 ,row_get_assignments.effective_start_date
1453 ,l_curr_organization_number
1454 ,l_curr_cost_center
1455 );
1456 get_assignment_lvl_info
1457 (l_assignment_id
1458 , (row_get_assignments.effective_start_date
1459 )
1460 - 1
1461 ,l_prev_organization_number
1462 ,l_prev_cost_center
1463 );
1464 get_person_lvl_info (l_assignment_id
1465 ,row_get_assignments.effective_start_date
1466 ,l_employee_pin
1467 ,l_first_name
1468 ,l_last_name
1469 ,l_before_after
1470 );
1471 get_salary (l_assignment_id
1472 ,g_end_date
1473 ,l_before_after
1474 ,l_moving_company
1475 ,l_monthly_salary_fk
1476 ,l_yearly_salary_fk
1477 );
1478 get_absence_lvl_info (l_assignment_id
1479 ,g_end_date
1480 ,l_fully_capable_of_work
1481 ,l_inability_to_work
1482 );
1483 /* **********************************************************************************/
1484 pay_action_information_api.create_action_information
1485 (p_action_information_id => l_action_info_id
1486 ,p_action_context_id => p_assignment_action_id
1487 ,p_action_context_type => 'AAP'
1488 ,p_object_version_number => l_ovn
1489 ,p_effective_date => l_effective_date
1490 ,p_source_id => NULL
1491 ,p_source_text => NULL
1492 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1493 ,p_action_information1 => 'PYSEALEA'
1494 ,p_action_information2 => 'PER'
1495 ,p_action_information3 => g_payroll_action_id
1496 ,p_action_information4 => l_moving_company
1497 ,p_action_information5 => l_curr_organization_number
1498 ,p_action_information6 => l_curr_cost_center
1499 ,p_action_information7 => l_agreement_plan_id
1500 ,p_action_information8 => l_employee_pin
1501 ,p_action_information9 => fnd_date.date_to_canonical
1502 (l_time_for_event_fk)
1503 ,p_action_information10 => l_before_after
1504 ,p_action_information11 => fnd_number.number_to_canonical
1505 (l_yearly_salary_fk)
1506 ,p_action_information12 => fnd_number.number_to_canonical
1507 (l_monthly_salary_fk)
1508 ,p_action_information13 => l_fully_capable_of_work
1509 ,p_action_information14 =>/* NVL*/
1510 (l_inability_to_work)
1511 /*,'0'
1512 ) */
1513 ,p_action_information15 => l_prev_organization_number
1514 ,p_action_information16 => l_prev_cost_center
1515 ,p_action_information17 => NULL
1516 ,p_action_information18 => NULL
1517 ,p_action_information19 => NULL
1518 ,p_action_information20 => NULL
1519 ,p_action_information21 => NULL
1520 ,p_action_information22 => NULL
1521 ,p_action_information23 => NULL
1522 ,p_action_information24 => NULL
1523 ,p_action_information25 => NULL
1524 ,p_action_information26 => NULL
1525 ,p_action_information27 => NULL
1526 ,p_action_information28 => NULL
1527 ,p_action_information29 => NULL
1528 ,p_action_information30 => l_person_id
1529 ,p_assignment_id => l_assignment_id
1530 );
1531 /* *****************************************************************************/
1532 END IF;
1533 END LOOP;
1534
1535 l_time_for_event_av := NULL;
1536 get_end_employment_or_not (l_assignment_id
1537 ,l_withdrawal
1538 ,l_time_for_event_av
1539 ,l_reason_for_termination
1540 ,l_effective_date
1541 );
1542 l_start_parental_leave := NULL;
1543
1544
1545 IF l_time_for_event_av IS NULL AND l_reason_for_termination IS NULL
1546 THEN
1547 get_termination_or_not (l_assignment_id
1548 ,'AV2'
1549 ,l_withdrawal
1550 ,l_time_for_event_av
1551 ,l_reason_for_termination
1552 ,l_effective_date
1553 ,l_start_parental_leave
1554 );
1555 END IF;
1556
1557
1558 IF l_time_for_event_av IS NULL AND l_reason_for_termination IS NULL
1559 THEN
1560 get_termination_or_not (l_assignment_id
1561 ,'AV3'
1562 ,l_withdrawal
1563 ,l_time_for_event_av
1564 ,l_reason_for_termination
1565 ,l_effective_date
1566 ,l_start_parental_leave
1567 );
1568 END IF;
1569
1570
1571 IF l_time_for_event_av IS NULL AND l_reason_for_termination IS NULL
1572 THEN
1573 get_termination_or_not (l_assignment_id
1574 ,'AV4'
1575 ,l_withdrawal
1576 ,l_time_for_event_av
1577 ,l_reason_for_termination
1578 ,l_effective_date
1579 ,l_start_parental_leave
1580 );
1581 END IF;
1582
1583
1584
1585 IF l_time_for_event_av IS NOT NULL
1586 OR l_reason_for_termination IS NOT NULL
1587 THEN
1588 get_assignment_lvl_info (l_assignment_id
1589 ,l_effective_date
1590 ,l_organization_number
1591 ,l_cost_centre
1592 );
1593 get_person_lvl_info (l_assignment_id
1594 ,l_effective_date
1595 ,l_employee_pin
1596 ,l_first_name
1597 ,l_last_name
1598 ,l_before_after
1599 );
1600 pay_action_information_api.create_action_information
1601 (p_action_information_id => l_action_info_id
1602 ,p_action_context_id => p_assignment_action_id
1603 ,p_action_context_type => 'AAP'
1604 ,p_object_version_number => l_ovn
1605 ,p_effective_date => l_effective_date
1606 ,p_source_id => NULL
1607 ,p_source_text => NULL
1608 ,p_action_information_category => 'EMEA REPORT INFORMATION'
1609 ,p_action_information1 => 'PYSEALEA'
1610 ,p_action_information2 => 'PER'
1611 ,p_action_information3 => g_payroll_action_id
1612 ,p_action_information4 => l_withdrawal
1613 ,p_action_information5 => l_organization_number
1614 ,p_action_information6 => l_cost_centre
1615 ,p_action_information7 => l_employee_pin
1616 ,p_action_information8 => fnd_date.date_to_canonical
1617 (l_time_for_event_av)
1618 ,p_action_information9 => l_reason_for_termination
1619 ,p_action_information10 => fnd_date.date_to_canonical
1620 (l_start_parental_leave)
1621 ,p_action_information11 => NULL
1622 ,p_action_information12 => NULL
1623 ,p_action_information13 => NULL
1624 ,p_action_information14 => NULL
1625 ,p_action_information15 => NULL
1626 ,p_action_information16 => NULL
1627 ,p_action_information17 => NULL
1628 ,p_action_information18 => NULL
1629 ,p_action_information19 => NULL
1630 ,p_action_information20 => NULL
1631 ,p_action_information21 => NULL
1632 ,p_action_information22 => NULL
1633 ,p_action_information23 => NULL
1634 ,p_action_information24 => NULL
1635 ,p_action_information25 => NULL
1636 ,p_action_information26 => NULL
1637 ,p_action_information27 => NULL
1638 ,p_action_information28 => NULL
1639 ,p_action_information29 => NULL
1640 ,p_action_information30 => l_person_id
1641 ,p_assignment_id => l_assignment_id
1642 );
1643 END IF;
1644
1645 -- End of Pickingup the Data
1646 END archive_code;
1647
1648 --- Report XML generating code
1649 PROCEDURE writetoclob (p_xfdf_clob OUT NOCOPY CLOB)
1650 IS
1651 l_xfdf_string CLOB;
1652 l_str1 VARCHAR2 (4000);
1653 l_str_1 VARCHAR2 (4000);
1654 l_str_2 VARCHAR2 (4000);
1655 l_str_3 VARCHAR2 (4000);
1656 l_str2 VARCHAR2 (200);
1657 l_str3 VARCHAR2 (200);
1658 l_str4 VARCHAR2 (200);
1659 l_str5 VARCHAR2 (200);
1660 l_str6 VARCHAR2 (500);
1661 l_str7 VARCHAR2 (1000);
1662 l_str8 VARCHAR2 (240);
1663 l_str9 VARCHAR2 (240);
1664 l_str10 VARCHAR2 (200);
1665 l_str11 VARCHAR2 (200);
1666 current_index PLS_INTEGER;
1667 l_iana_charset VARCHAR2 (150);
1668 BEGIN
1669
1670 l_iana_charset := hr_se_utility.get_iana_charset;
1671 -- logger('CLOB l_iana_charset +== > ',l_iana_charset);
1672 l_str1 :=
1673 '<?xml version="1.0" encoding="'
1674 || l_iana_charset
1675 || '"?>'
1676 || '<granssnitt:GrunduppgifterITP ';
1677 l_str_1 :='version="2.0.0.0" xmlns:granssnitt="http://collectum.se/granssnitt/grunduppgifterITP/2.0" xmlns:arkitekturella="http://collectum.se/arkitekturella/2.0" ';
1678 l_str_2 :='xmlns:avanmalan="http://collectum.se/paket/pa/avanmalan/2.0" xmlns:flyttAnstalldaInomKoncern="http://collectum.se/paket/pa/flyttAnstalldaInomKoncern/2.0" ';
1679 l_str_3 :=' xmlns:loneandring="http://collectum.se/paket/pa/loneandring/2.0" xmlns:nyanmalan="http://collectum.se/paket/pa/nyanmalan/2.0" xmlns:typer="http://collectum.se/typer/2.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" >';
1680 l_str2 := '<';
1681 l_str3 := '>';
1682 l_str4 := '</';
1683 l_str5 := '>';
1684 l_str6 := '</granssnitt:GrunduppgifterITP>';
1685 l_str7 :=
1686 '<?xml version="1.0" encoding="'
1687 || l_iana_charset
1688 || '"?> <ROOT></ROOT>';
1689 l_str10 := NULL;
1690 l_str11 := '</>';
1691 DBMS_LOB.createtemporary (l_xfdf_string, FALSE, DBMS_LOB.CALL);
1692 DBMS_LOB.OPEN (l_xfdf_string, DBMS_LOB.lob_readwrite);
1693 current_index := 0;
1694
1695 --logger('CLOB CLOB +== > ','In CLOB');
1696 --logger('str1111 +== > ',l_str1);
1697 --logger('str1111 +== > ',l_str_1);
1698 IF ghpd_data.COUNT > 0
1699 THEN
1700 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str1), l_str1);
1701 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str_1), l_str_1);
1702 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str_2), l_str_2);
1703 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str_3), l_str_3);
1704 FOR table_counter IN ghpd_data.FIRST .. ghpd_data.LAST
1705 LOOP
1706 l_str8 := ghpd_data (table_counter).tagname;
1707 l_str9 := ghpd_data (table_counter).tagvalue;
1708 l_str10 := ghpd_data (table_counter).eventnumber;
1709
1710 IF l_str9 IN
1711 ('granssnitt:Header'
1712 ,'granssnitt:Header_END'
1713 ,'granssnitt:AvanmalanHandelse'
1714 ,'granssnitt:AvanmalanHandelse_END'
1715 ,'arkitekturella:Timestamp'
1716 ,'arkitekturella:Timestamp_END'
1717 ,'granssnitt:Avanmalan'
1718 ,'granssnitt:Avanmalan_END'
1719 ,'avanmalan:Tidsstampel'
1720 ,'avanmalan:Tidsstampel_END'
1721 ,'granssnitt:FlyttAnstalldaInomKoncernHandelse'
1722 ,'granssnitt:FlyttAnstalldaInomKoncernHandelse_END'
1723 ,'granssnitt:FlyttAnstalldaInomKoncern'
1724 ,'granssnitt:FlyttAnstalldaInomKoncern_END'
1725 ,'flyttAnstalldaInomKoncern:Tidsstampel'
1726 ,'flyttAnstalldaInomKoncern:Tidsstampel_END'
1727 ,'granssnitt:LoneandringHandelse'
1728 ,'granssnitt:LoneandringHandelse_END'
1729 ,'granssnitt:Loneandring'
1730 ,'granssnitt:Loneandring_END'
1731 ,'loneandring:Tidsstampel'
1732 ,'loneandring:Tidsstampel_END'
1733 ,'granssnitt:NyanmalanHandelse'
1734 ,'granssnitt:NyanmalanHandelse_END'
1735 ,'granssnitt:Nyanmalan'
1736 ,'granssnitt:Nyanmalan_END'
1737 ,'nyanmalan:Tidsstampel'
1738 ,'nyanmalan:Tidsstampel_END'
1739 )
1740 THEN
1741 IF l_str9 IN
1742 ('granssnitt:Header'
1743 ,'granssnitt:AvanmalanHandelse'
1744 ,'arkitekturella:Timestamp'
1745 ,'granssnitt:Avanmalan'
1746 ,'avanmalan:Tidsstampel'
1747 ,'granssnitt:FlyttAnstalldaInomKoncernHandelse'
1748 ,'granssnitt:FlyttAnstalldaInomKoncern'
1749 ,'flyttAnstalldaInomKoncern:Tidsstampel'
1750 ,'granssnitt:LoneandringHandelse'
1751 ,'granssnitt:Loneandring'
1752 ,'loneandring:Tidsstampel'
1753 ,'granssnitt:NyanmalanHandelse'
1754 ,'granssnitt:Nyanmalan'
1755 ,'nyanmalan:Tidsstampel'
1756 )
1757 THEN
1758 IF l_str9 IN
1759 ('granssnitt:AvanmalanHandelse'
1760 ,'granssnitt:FlyttAnstalldaInomKoncernHandelse'
1761 ,'granssnitt:LoneandringHandelse'
1762 ,'granssnitt:NyanmalanHandelse'
1763 )
1764 THEN
1765 DBMS_LOB.writeappend (l_xfdf_string
1766 ,LENGTH (l_str2)
1767 ,l_str2
1768 );
1769 DBMS_LOB.writeappend (l_xfdf_string
1770 ,LENGTH (l_str8)
1771 ,l_str8
1772 );
1773 --logger('Adding +== > ','Attribute');
1774 --logger('Adding l_str10 +== > ', l_str10);
1775 -- Add attribute column
1776 DBMS_LOB.writeappend (l_xfdf_string
1777 ,LENGTH (' Handelsenummer="')
1778 ,' Handelsenummer="'
1779 );
1780 DBMS_LOB.writeappend (l_xfdf_string
1781 ,LENGTH (l_str10)
1782 ,l_str10
1783 );
1784 DBMS_LOB.writeappend (l_xfdf_string, LENGTH ('"'), '"');
1785 -- END of attribute addition
1786 DBMS_LOB.writeappend (l_xfdf_string
1787 ,LENGTH (l_str3)
1788 ,l_str3
1789 );
1790 ELSE
1791 DBMS_LOB.writeappend (l_xfdf_string
1792 ,LENGTH (l_str2)
1793 ,l_str2
1794 );
1795 DBMS_LOB.writeappend (l_xfdf_string
1796 ,LENGTH (l_str8)
1797 ,l_str8
1798 );
1799 DBMS_LOB.writeappend (l_xfdf_string
1800 ,LENGTH (l_str3)
1801 ,l_str3
1802 );
1803 END IF;
1804 ELSE
1805 DBMS_LOB.writeappend (l_xfdf_string
1806 ,LENGTH (l_str4)
1807 ,l_str4
1808 );
1809 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1810 ,l_str8);
1811 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
1812 ,l_str5);
1813 END IF;
1814 ELSE
1815 IF l_str9 IS NOT NULL
1816 THEN
1817 DBMS_LOB.writeappend (l_xfdf_string
1818 ,LENGTH (l_str2)
1819 ,l_str2
1820 );
1821 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1822 ,l_str8);
1823 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
1824 ,l_str3);
1825 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str9)
1826 ,l_str9);
1827 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4)
1828 ,l_str4);
1829 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1830 ,l_str8);
1831 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
1832 ,l_str5);
1833 ELSE
1834 DBMS_LOB.writeappend (l_xfdf_string
1835 ,LENGTH (l_str2)
1836 ,l_str2
1837 );
1838 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1839 ,l_str8);
1840 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str3)
1841 ,l_str3);
1842 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str4)
1843 ,l_str4);
1844 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str8)
1845 ,l_str8);
1846 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str5)
1847 ,l_str5);
1848 END IF;
1849 END IF;
1850 END LOOP;
1851
1852 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str6), l_str6);
1853 ELSE
1854 DBMS_LOB.writeappend (l_xfdf_string, LENGTH (l_str7), l_str7);
1855 END IF;
1856
1857 p_xfdf_clob := l_xfdf_string;
1858 hr_utility.set_location ('Leaving WritetoCLOB ', 40);
1859 EXCEPTION
1860 WHEN OTHERS
1861 THEN
1862 hr_utility.TRACE ('sqlerrm ' || SQLERRM);
1863 hr_utility.raise_error;
1864 END writetoclob;
1865
1866 PROCEDURE get_xml_for_report (
1867 p_business_group_id IN NUMBER
1868 ,p_payroll_action_id IN VARCHAR2
1869 ,p_template_name IN VARCHAR2
1870 ,p_xml OUT NOCOPY CLOB
1871 )
1872 IS
1873 --Variables needed for the report
1874 l_counter NUMBER := 0;
1875 l_payroll_action_id pay_action_information.action_information1%TYPE;
1876
1877 --Cursors needed for report
1878 CURSOR csr_report_details (
1879 csr_v_pa_id pay_action_information.action_context_id%TYPE
1880 )
1881 IS
1882 SELECT *
1883 FROM pay_action_information
1884 WHERE action_context_type = 'PA'
1885 AND action_context_id = csr_v_pa_id
1886 AND action_information_category = 'EMEA REPORT DETAILS'
1887 AND action_information1 = 'PYSEALEA';
1888
1889 lr_report_details csr_report_details%ROWTYPE;
1890
1891 CURSOR csr_all_employees_for_event (
1892 csr_v_pa_id pay_action_information.action_information3%TYPE
1893 ,csr_v_event pay_action_information.action_information4%TYPE
1894 )
1895 IS
1896 SELECT *
1897 FROM pay_action_information
1898 WHERE action_context_type = 'AAP'
1899 AND action_information_category = 'EMEA REPORT INFORMATION'
1900 AND action_information1 = 'PYSEALEA'
1901 AND action_information3 = csr_v_pa_id
1902 AND action_information2 = 'PER'
1903 AND action_information4 = csr_v_event
1904 ORDER BY action_information30;
1905
1906 CURSOR csr_count_employees_for_event (
1907 csr_v_pa_id pay_action_information.action_information3%TYPE
1908 ,csr_v_event pay_action_information.action_information4%TYPE
1909 )
1910 IS
1911 SELECT COUNT ('1')
1912 FROM pay_action_information
1913 WHERE action_context_type = 'AAP'
1914 AND action_information_category = 'EMEA REPORT INFORMATION'
1915 AND action_information1 = 'PYSEALEA'
1916 AND action_information3 = csr_v_pa_id
1917 AND action_information2 = 'PER'
1918 AND action_information4 = csr_v_event;
1919
1920 l_count_employees_for_event NUMBER;
1921
1922 CURSOR csr_all_employees_under_le (
1923 csr_v_pa_id pay_action_information.action_information3%TYPE
1924 ,csr_v_le_id pay_action_information.action_information15%TYPE
1925 )
1926 IS
1927 SELECT *
1928 FROM pay_action_information
1929 WHERE action_context_type = 'AAP'
1930 AND action_information_category = 'EMEA REPORT INFORMATION'
1931 AND action_information1 = 'PYSEALEA'
1932 AND action_information3 = csr_v_pa_id
1933 AND action_information2 = 'PER'
1934 AND action_information15 = csr_v_le_id
1935 ORDER BY action_information30;
1936
1937 /* End of declaration*/
1938 /* Proc to Add the tag value and Name */
1939 PROCEDURE add_tag_value (
1940 p_tag_name IN VARCHAR2
1941 ,p_tag_value IN VARCHAR2
1942 ,p_eventnumber IN NUMBER DEFAULT NULL
1943 )
1944 IS
1945 BEGIN
1946 ghpd_data (l_counter).tagname := p_tag_name;
1947 ghpd_data (l_counter).tagvalue := p_tag_value;
1948 ghpd_data (l_counter).eventnumber := p_eventnumber;
1949 l_counter := l_counter + 1;
1950 END add_tag_value;
1951 /* End of Proc to Add the tag value and Name */
1952 /* Start of GET_HPD_XML */
1953 BEGIN
1954 IF p_payroll_action_id IS NULL
1955 THEN
1956 BEGIN
1957 SELECT payroll_action_id
1958 INTO l_payroll_action_id
1959 FROM pay_payroll_actions ppa
1960 ,fnd_conc_req_summary_v fcrs
1961 ,fnd_conc_req_summary_v fcrs1
1962 WHERE fcrs.request_id = fnd_global.conc_request_id
1963 AND fcrs.priority_request_id = fcrs1.priority_request_id
1964 AND ppa.request_id BETWEEN fcrs1.request_id AND fcrs.request_id
1965 AND ppa.request_id = fcrs1.request_id;
1966 EXCEPTION
1967 WHEN OTHERS
1968 THEN
1969 NULL;
1970 END;
1971 ELSE
1972 l_payroll_action_id := p_payroll_action_id;
1973
1974
1975 --logger('p_payroll_action_id +== > ',p_payroll_action_id);
1976 OPEN csr_report_details (l_payroll_action_id);
1977
1978 FETCH csr_report_details
1979 INTO lr_report_details;
1980
1981 CLOSE csr_report_details;
1982
1983
1984 -- Header ---
1985 add_tag_value ('granssnitt:Header', 'granssnitt:Header');
1986 add_tag_value ('arkitekturella:SkickatFran'
1987 ,lr_report_details.action_information7
1988 );
1989 add_tag_value ('arkitekturella:SkickatTill'
1990 ,lr_report_details.action_information8
1991 );
1992 add_tag_value ('arkitekturella:Timestamp'
1993 ,'arkitekturella:Timestamp');
1994 add_tag_value ('typer:Datetime'
1995 ,REPLACE (TO_CHAR (SYSDATE, 'YYYY-MM-DD HH:MM:SS')
1996 ,' '
1997 ,'T'
1998 )
1999 );
2000 add_tag_value ('typer:Fractions', '000000');
2001 add_tag_value ('arkitekturella:Timestamp'
2002 ,'arkitekturella:Timestamp_END'
2003 );
2004 add_tag_value ('arkitekturella:Sekvensnummer', '1');
2005
2006 IF lr_report_details.action_information9 = 'Y'
2007 THEN
2008 add_tag_value ('arkitekturella:Produktion', 'true' /*'Yes'*/);
2009 ELSE
2010 add_tag_value ('arkitekturella:Produktion', 'false' /*'No'*/);
2011 END IF;
2012
2013 add_tag_value ('granssnitt:Header', 'granssnitt:Header_END');
2014
2015 -- Withdrawal ---
2016 /*add_tag_value ('granssnitt:User', ' ');
2017 add_tag_value ('granssnitt:Filnamn', ' ');*/
2018 l_count_employees_for_event := 1;
2019
2020 /*l_count_employees_for_event := NULL;
2021 OPEN csr_count_employees_for_event (l_payroll_action_id,'AV');
2022 FETCH csr_count_employees_for_event INTO l_count_employees_for_event;
2023 CLOSE csr_count_employees_for_event;
2024 IF l_count_employees_for_event > 0
2025 THEN
2026 */
2027 FOR row_all_employees_for_event IN
2028 csr_all_employees_for_event (l_payroll_action_id, 'AV')
2029 LOOP
2030 add_tag_value ('granssnitt:User', ' ');
2031 add_tag_value ('granssnitt:Organisationsnummer','16'||row_all_employees_for_event.action_information5);
2032 add_tag_value ('granssnitt:Filnamn', ' ');
2033
2034 add_tag_value ('granssnitt:AvanmalanHandelse'
2035 ,'granssnitt:AvanmalanHandelse'
2036 ,l_count_employees_for_event
2037 );
2038 l_count_employees_for_event := l_count_employees_for_event + 1;
2039 add_tag_value ('granssnitt:Avanmalan', 'granssnitt:Avanmalan');
2040 add_tag_value ('avanmalan:Organisationsnummer'
2041 ,'16'||row_all_employees_for_event.action_information5
2042 );
2043 add_tag_value ('avanmalan:KostnadsstalleId'
2044 ,row_all_employees_for_event.action_information6
2045 );
2046 add_tag_value ('avanmalan:Personnummer'
2047 ,row_all_employees_for_event.action_information7
2048 );
2049 --logger('Before Change of Company +== > ',row_all_employees_for_event.action_information8);
2050 add_tag_value
2051 ('avanmalan:Handelsetidpunkt'
2052 ,TO_CHAR
2053 (fnd_date.canonical_to_date
2054 (row_all_employees_for_event.action_information8)
2055 ,'YYYY-MM-DD'
2056 )
2057 );
2058 --logger('Before Change of Company +== > ',row_all_employees_for_event.action_information8);
2059 add_tag_value ('avanmalan:Avgangsorsak'
2060 ,row_all_employees_for_event.action_information9
2061 );
2062
2063 IF row_all_employees_for_event.action_information10 IS NOT NULL
2064 THEN
2065 add_tag_value
2066 ('avanmalan:DatumForForalderledighet'
2067 ,TO_CHAR
2068 (fnd_date.canonical_to_date
2069 (row_all_employees_for_event.action_information10)
2070 ,'YYYY-MM-DD'
2071 )
2072 );
2073 END IF;
2074
2075 add_tag_value ('avanmalan:Tidsstampel', 'avanmalan:Tidsstampel');
2076 add_tag_value ('typer:Datetime'
2077 ,REPLACE (TO_CHAR (SYSDATE, 'YYYY-MM-DD HH:MM:SS')
2078 ,' '
2079 ,'T'
2080 )
2081 );
2082 add_tag_value ('typer:Fractions', '000000');
2083 add_tag_value ('avanmalan:Tidsstampel'
2084 ,'avanmalan:Tidsstampel_END'
2085 );
2086 add_tag_value ('granssnitt:Avanmalan', 'granssnitt:Avanmalan_END');
2087 add_tag_value ('granssnitt:AvanmalanHandelse'
2088 ,'granssnitt:AvanmalanHandelse_END'
2089 );
2090 END LOOP;
2091
2092 --END IF;
2093
2094
2095 --Change of company
2096 /*
2097 l_count_employees_for_event := NULL;
2098 OPEN csr_count_employees_for_event (l_payroll_action_id,'FK');
2099 FETCH csr_count_employees_for_event INTO l_count_employees_for_event;
2100 CLOSE csr_count_employees_for_event;
2101 IF l_count_employees_for_event > 0
2102 THEN
2103 */
2104 FOR row_all_employees_for_event IN
2105 csr_all_employees_for_event (l_payroll_action_id, 'FK')
2106 LOOP
2107 add_tag_value ('granssnitt:User', ' ');
2108 add_tag_value ('granssnitt:Organisationsnummer','16'||row_all_employees_for_event.action_information5);
2109 add_tag_value ('granssnitt:Filnamn', ' ');
2110
2111 add_tag_value ('granssnitt:FlyttAnstalldaInomKoncernHandelse'
2112 ,'granssnitt:FlyttAnstalldaInomKoncernHandelse'
2113 ,l_count_employees_for_event
2114 );
2115 l_count_employees_for_event := l_count_employees_for_event + 1;
2116 add_tag_value ('granssnitt:FlyttAnstalldaInomKoncern'
2117 ,'granssnitt:FlyttAnstalldaInomKoncern'
2118 );
2119 add_tag_value ('flyttAnstalldaInomKoncern:Organisationsnummer'
2120 ,'16'||row_all_employees_for_event.action_information5
2121 );
2122 add_tag_value ('flyttAnstalldaInomKoncern:KostnadsstalleId'
2123 ,row_all_employees_for_event.action_information6
2124 );
2125 add_tag_value ('flyttAnstalldaInomKoncern:Avtalsplanid'
2126 ,row_all_employees_for_event.action_information7
2127 );
2128 add_tag_value ('flyttAnstalldaInomKoncern:Personnummer'
2129 ,row_all_employees_for_event.action_information8
2130 );
2131 add_tag_value
2132 ('flyttAnstalldaInomKoncern:Handelsetidpunkt'
2133 ,TO_CHAR
2134 (fnd_date.canonical_to_date
2135 (row_all_employees_for_event.action_information9)
2136 ,'YYYY-MM-DD'
2137 )
2138 );
2139
2140 -- Add conditional for yearly or monthly salary.
2141 /*IF row_all_employees_for_event.action_information10 = 'AFTER'
2142 THEN*/
2143 add_tag_value
2144 ('flyttAnstalldaInomKoncern:Manadslon'
2145 ,fnd_number.canonical_to_number
2146 (row_all_employees_for_event.action_information12)
2147 );
2148 /*ELSE*/
2149 add_tag_value
2150 ('flyttAnstalldaInomKoncern:Arslon'
2151 ,fnd_number.canonical_to_number
2152 (row_all_employees_for_event.action_information11)
2153 );
2154 /*END IF; */
2155
2156 add_tag_value ('flyttAnstalldaInomKoncern:FulltArbetsfor'
2157 ,row_all_employees_for_event.action_information13
2158 );
2159 /* add_tag_value ('flyttAnstalldaInomKoncern:GradAvArbetsoformaga'
2160 ,row_all_employees_for_event.action_information14
2161 );*/
2162 add_tag_value
2163 ('flyttAnstalldaInomKoncern:OrganisationsnummerFran'
2164 ,'16'||row_all_employees_for_event.action_information15
2165 );
2166 add_tag_value ('flyttAnstalldaInomKoncern:KostnadsstalleIdFran'
2167 ,row_all_employees_for_event.action_information16
2168 );
2169 add_tag_value ('flyttAnstalldaInomKoncern:Tidsstampel'
2170 ,'flyttAnstalldaInomKoncern:Tidsstampel'
2171 );
2172 add_tag_value ('typer:Datetime'
2173 ,REPLACE (TO_CHAR (SYSDATE, 'YYYY-MM-DD HH:MM:SS')
2174 ,' '
2175 ,'T'
2176 )
2177 );
2178 add_tag_value ('typer:Fractions', '000000');
2179 add_tag_value ('flyttAnstalldaInomKoncern:Tidsstampel'
2180 ,'flyttAnstalldaInomKoncern:Tidsstampel_END'
2181 );
2182 add_tag_value ('granssnitt:FlyttAnstalldaInomKoncern'
2183 ,'granssnitt:FlyttAnstalldaInomKoncern_END'
2184 );
2185 add_tag_value ('granssnitt:FlyttAnstalldaInomKoncernHandelse'
2186 ,'granssnitt:FlyttAnstalldaInomKoncernHandelse_END'
2187 );
2188 END LOOP;
2189
2190 --END IF;
2191 -- Salary Change
2192
2193
2194 /*
2195 l_count_employees_for_event := NULL;
2196 OPEN csr_count_employees_for_event (l_payroll_action_id,'LO');
2197 FETCH csr_count_employees_for_event INTO l_count_employees_for_event;
2198 CLOSE csr_count_employees_for_event;
2199 IF l_count_employees_for_event > 0
2200 THEN
2201 */
2202 FOR row_all_employees_for_event IN
2203 csr_all_employees_for_event (l_payroll_action_id, 'LO')
2204 LOOP
2205 add_tag_value ('granssnitt:User', ' ');
2206 add_tag_value ('granssnitt:Organisationsnummer','16'||row_all_employees_for_event.action_information5);
2207 add_tag_value ('granssnitt:Filnamn', ' ');
2208
2209 add_tag_value ('granssnitt:LoneandringHandelse'
2210 ,'granssnitt:LoneandringHandelse'
2211 ,l_count_employees_for_event
2212 );
2213 l_count_employees_for_event := l_count_employees_for_event + 1;
2214 add_tag_value ('granssnitt:Loneandring', 'granssnitt:Loneandring');
2215 add_tag_value ('loneandring:Organisationsnummer'
2216 ,'16'||row_all_employees_for_event.action_information5
2217 );
2218 add_tag_value ('loneandring:KostnadsstalleId'
2219 ,row_all_employees_for_event.action_information6
2220 );
2221 add_tag_value ('loneandring:Personnummer'
2222 ,row_all_employees_for_event.action_information7
2223 );
2224 add_tag_value
2225 ('loneandring:Handelsetidpunkt'
2226 ,TO_CHAR
2227 (fnd_date.canonical_to_date
2228 (row_all_employees_for_event.action_information8)
2229 ,'YYYY-MM-DD'
2230 )
2231 );
2232
2233 -- add conditional tags
2234 add_tag_value
2235 ('loneandring:Manadslon'
2236 ,row_all_employees_for_event.action_information11
2237 );
2238
2239 add_tag_value
2240 ('loneandring:Arslon'
2241 ,row_all_employees_for_event.action_information10
2242 );
2243
2244 add_tag_value ('loneandring:GradAvArbetsoformaga'
2245 ,row_all_employees_for_event.action_information13
2246 );
2247
2248 IF row_all_employees_for_event.action_information9 = 'AFTER'
2249 THEN
2250 /* add_tag_value
2251 ('loneandring:Manadslon'
2252 ,row_all_employees_for_event.action_information11
2253 );*/
2254 /*add_tag_value ('loneandring:GradAvArbetsoformaga'
2255 ,row_all_employees_for_event.action_information13
2256 );*/
2257 null;
2258 ELSE
2259 /*add_tag_value
2260 ('loneandring:Arslon'
2261 ,row_all_employees_for_event.action_information10
2262 );*/
2263 add_tag_value ('loneandring:Lonesankning'
2264 ,row_all_employees_for_event.action_information12
2265 );
2266 END IF;
2267
2268 add_tag_value ('loneandring:Tidsstampel'
2269 ,'loneandring:Tidsstampel'
2270 );
2271 add_tag_value ('typer:Datetime'
2272 ,REPLACE (TO_CHAR (SYSDATE, 'YYYY-MM-DD HH:MM:SS')
2273 ,' '
2274 ,'T'
2275 )
2276 );
2277 add_tag_value ('typer:Fractions', '000000');
2278 add_tag_value ('loneandring:Tidsstampel'
2279 ,'loneandring:Tidsstampel_END'
2280 );
2281 add_tag_value ('granssnitt:Loneandring'
2282 ,'granssnitt:Loneandring_END'
2283 );
2284 add_tag_value ('granssnitt:LoneandringHandelse'
2285 ,'granssnitt:LoneandringHandelse_END'
2286 );
2287 END LOOP;
2288
2289 --END IF;
2290
2291
2292 -- New Entry or New Joinee
2293 /*
2294 l_count_employees_for_event := NULL;
2295 OPEN csr_count_employees_for_event (l_payroll_action_id,'IN');
2296 FETCH csr_count_employees_for_event INTO l_count_employees_for_event;
2297 CLOSE csr_count_employees_for_event;
2298 IF l_count_employees_for_event > 0
2299 THEN
2300 */
2301 FOR row_all_employees_for_event IN
2302 csr_all_employees_for_event (l_payroll_action_id, 'IN')
2303 LOOP
2304 add_tag_value ('granssnitt:User', ' ');
2305 add_tag_value ('granssnitt:Organisationsnummer','16'||row_all_employees_for_event.action_information5);
2306 add_tag_value ('granssnitt:Filnamn', ' ');
2307
2308 add_tag_value ('granssnitt:NyanmalanHandelse'
2309 ,'granssnitt:NyanmalanHandelse'
2310 ,l_count_employees_for_event
2311 );
2312 l_count_employees_for_event := l_count_employees_for_event + 1;
2313 add_tag_value ('granssnitt:Nyanmalan', 'granssnitt:Nyanmalan');
2314 add_tag_value ('nyanmalan:Organisationsnummer'
2315 ,'16'||row_all_employees_for_event.action_information5
2316 );
2317 add_tag_value ('nyanmalan:KostnadsstalleId'
2318 ,row_all_employees_for_event.action_information6
2319 );
2320 add_tag_value ('nyanmalan:Avtalsplanid'
2321 ,row_all_employees_for_event.action_information7
2322 );
2323 add_tag_value ('nyanmalan:Personnummer'
2324 ,row_all_employees_for_event.action_information8
2325 );
2326 add_tag_value
2327 ('nyanmalan:Handelsetidpunkt'
2328 ,TO_CHAR
2329 (fnd_date.canonical_to_date
2330 (row_all_employees_for_event.action_information9)
2331 ,'YYYY-MM-DD'
2332 )
2333 );
2334 add_tag_value ('nyanmalan:Efternamn'
2335 ,row_all_employees_for_event.action_information10
2336 );
2337 add_tag_value ('nyanmalan:Fornamn'
2338 ,row_all_employees_for_event.action_information11
2339 );
2340
2341 -- add conditional tags
2342 /* IF row_all_employees_for_event.action_information12 = 'AFTER'
2343 THEN*/ /* Bug No. 6141681 */
2344 add_tag_value
2345 ('nyanmalan:Manadslon'
2346 ,row_all_employees_for_event.action_information14
2347 );
2348 /*ELSE */
2349 add_tag_value
2350 ('nyanmalan:Arslon'
2351 ,row_all_employees_for_event.action_information13
2352 );
2353 /*END IF;*/
2354
2355 add_tag_value ('nyanmalan:FulltArbetsfor'
2356 ,row_all_employees_for_event.action_information15
2357 );
2358 add_tag_value ('nyanmalan:GradAvArbetsoformaga'
2359 ,row_all_employees_for_event.action_information16
2360 );
2361 add_tag_value ('nyanmalan:Tidsstampel', 'nyanmalan:Tidsstampel');
2362 add_tag_value ('typer:Datetime'
2363 ,REPLACE (TO_CHAR (SYSDATE, 'YYYY-MM-DD HH:MM:SS')
2364 ,' '
2365 ,'T'
2366 )
2367 );
2368 add_tag_value ('typer:Fractions', '000000');
2369 add_tag_value ('nyanmalan:Tidsstampel'
2370 ,'nyanmalan:Tidsstampel_END'
2371 );
2372 add_tag_value ('granssnitt:Nyanmalan', 'granssnitt:Nyanmalan_END');
2373 add_tag_value ('granssnitt:NyanmalanHandelse'
2374 ,'granssnitt:NyanmalanHandelse_END'
2375 );
2376 END LOOP;
2377 --END IF;
2378 /* add_tag_value ('PERIOD_FROM', lr_report_details.period_from);
2379 add_tag_value ('PERIOD_TO', lr_report_details.period_to);
2380 fnd_file.put_line (fnd_file.LOG, 'After csr_REPORT_DETAILS ');
2381 fnd_file.put_line (fnd_file.LOG,
2382 'PERIOD_FROM ' || lr_report_details.period_from
2383 );
2384 fnd_file.put_line (fnd_file.LOG,
2385 'PERIOD_TO ' || lr_report_details.period_to
2386 );
2387 fnd_file.put_line (fnd_file.LOG, 'Before Csr for Legal');
2388
2389 FOR rec_all_le IN csr_all_legal_employer (l_payroll_action_id)
2390 LOOP
2391 add_tag_value ('LEGAL_EMPLOYER', 'LEGAL_EMPLOYER');
2392 add_tag_value ('LE_DETAILS', 'LE_DETAILS');
2393 add_tag_value ('LE_NAME', rec_all_le.action_information4);
2394 add_tag_value ('LE_ORG_NUM', rec_all_le.action_information5);
2395 add_tag_value ('LE_DETAILS', 'LE_DETAILS_END');
2396 add_tag_value ('EMPLOYEES', 'EMPLOYEES');
2397 fnd_file.put_line (fnd_file.LOG, '^^^^^^^^^^^^^^^^^^^^^');
2398 fnd_file.put_line (fnd_file.LOG, 'Legal Employer');
2399 fnd_file.put_line (fnd_file.LOG,
2400 'LE ID ' || rec_all_le.action_information3
2401 );
2402 fnd_file.put_line (fnd_file.LOG,
2403 'LE_NAME ' || rec_all_le.action_information4
2404 );
2405 fnd_file.put_line (fnd_file.LOG, '^^^^^^^^^^^^^^^^^^^^^');
2406 fnd_file.put_line (fnd_file.LOG, '^^^^^^^^^^^^^^^^^^^^^');
2407 fnd_file.put_line (fnd_file.LOG, ' Inside Person Query');
2408
2409 FOR rec_all_emp_under_le IN
2410 csr_all_employees_under_le (l_payroll_action_id,
2411 rec_all_le.action_information3
2412 )
2413 LOOP
2414 fnd_file.put_line (fnd_file.LOG,
2415 'PERSON ID ==> '
2416 || rec_all_emp_under_le.action_information30
2417 );
2418 add_tag_value ('PERSON', 'PERSON');
2419 add_tag_value ('EMPLOYEE_CODE',
2420 rec_all_emp_under_le.action_information4
2421 );
2422 add_tag_value ('EMPLOYEE_NUMBER',
2423 rec_all_emp_under_le.action_information5
2424 );
2425 add_tag_value ('EMPLOYEE_NAME',
2426 rec_all_emp_under_le.action_information6
2427 );
2428 add_tag_value
2429 ('HOLIDAY_PAY_PER_DAY',
2430 TO_CHAR
2431 (fnd_number.canonical_to_number
2432 (rec_all_emp_under_le.action_information7),
2433 '999999990D99'
2434 )
2435 );
2436 add_tag_value ('TOTAL_PAID_DAYS',
2437 rec_all_emp_under_le.action_information8
2438 );
2439 add_tag_value
2440 ('TOTAL_PAID_DAYS_AMOUNT',
2441 TO_CHAR
2442 (fnd_number.canonical_to_number
2443 (rec_all_emp_under_le.action_information9),
2444 '999999990D99'
2445 )
2446 );
2447 add_tag_value ('TOTAL_SAVED_DAYS',
2448 rec_all_emp_under_le.action_information10
2449 );
2450 add_tag_value
2451 ('TOTAL_SAVED_DAYS_AMOUNT',
2452 TO_CHAR
2453 (fnd_number.canonical_to_number
2454 (rec_all_emp_under_le.action_information11),
2455 '999999990D99'
2456 )
2457 );
2458 add_tag_value ('TOTAL_EARNED_DAYS',
2459 rec_all_emp_under_le.action_information12
2460 );
2461 add_tag_value
2462 ('TOTAL_EARNED_DAYS_AMOUNT',
2463 TO_CHAR
2464 (fnd_number.canonical_to_number
2465 (rec_all_emp_under_le.action_information13),
2466 '999999990D99'
2467 )
2468 );
2469 add_tag_value ('PERSON', 'PERSON_END');
2470 END LOOP; -- For all EMPLOYEES
2471
2472 fnd_file.put_line (fnd_file.LOG, '^^^^^^^^^^^^^^^^^^^^^');
2473 add_tag_value ('EMPLOYEES', 'EMPLOYEES_END');
2474 add_tag_value ('LEGAL_EMPLOYER', 'LEGAL_EMPLOYER_END');
2475 END LOOP; -- For all LEGAL_EMPLYER
2476 */
2477 END IF; -- for p_payroll_action_id IS NULL
2478
2479 writetoclob (p_xml);
2480
2481 -- INSERT INTO clob_table VALUES (p_xml );
2482
2483 -- COMMIT;
2484 END get_xml_for_report;
2485
2486 -- *****************************************************************************
2487 /* Proc to Add the tag value and Name */
2488 PROCEDURE logger (p_display IN VARCHAR2, p_value IN VARCHAR2)
2489 IS
2490 BEGIN
2491 fnd_file.put_line (fnd_file.LOG
2492 , p_display || ' ==> ' || p_value
2493 );
2494 END logger;
2495
2496 PROCEDURE get_assignment_lvl_info (
2497 p_assignment_id IN NUMBER
2498 ,p_effective_date IN DATE
2499 ,p_organization_number OUT NOCOPY VARCHAR2
2500 ,p_cost_centre OUT NOCOPY VARCHAR2
2501 )
2502 IS
2503 CURSOR csr_get_details
2504 IS
2505 SELECT scl.segment2
2506 ,paa.location_id
2507 FROM per_all_assignments_f paa
2508 ,hr_soft_coding_keyflex scl
2509 WHERE paa.assignment_id = p_assignment_id
2510 AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
2511 -- AND paa.primary_flag = 'Y'
2512 AND p_effective_date BETWEEN paa.effective_start_date
2513 AND paa.effective_end_date;
2514
2515 lr_get_details csr_get_details%ROWTYPE;
2516
2517 -- Cursor to pick up LEGAL EMPLOYER
2518 CURSOR csr_find_legal_employer (
2519 csr_v_organization_id hr_organization_information.organization_id%TYPE
2520 )
2521 IS
2522 SELECT hoi3.organization_id legal_id
2523 FROM hr_all_organization_units o1
2524 ,hr_organization_information hoi1
2525 ,hr_organization_information hoi2
2526 ,hr_organization_information hoi3
2527 WHERE o1.business_group_id = g_business_group_id
2528 AND hoi1.organization_id = o1.organization_id
2529 AND hoi1.organization_id = csr_v_organization_id
2530 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
2531 AND hoi1.org_information_context = 'CLASS'
2532 AND o1.organization_id = hoi2.org_information1
2533 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
2534 AND hoi2.organization_id = hoi3.organization_id
2535 AND hoi3.org_information_context = 'CLASS'
2536 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER';
2537
2538 lr_find_legal_employer csr_find_legal_employer%ROWTYPE;
2539
2540 CURSOR csr_legal_employer_details (
2541 csr_v_legal_employer_id hr_organization_information.organization_id%TYPE
2542 )
2543 IS
2544 SELECT o1.NAME legal_employer_name
2545 ,hoi2.org_information2 org_number
2546 ,hoi1.organization_id legal_id
2547 FROM hr_organization_units o1
2548 ,hr_organization_information hoi1
2549 ,hr_organization_information hoi2
2550 WHERE o1.business_group_id = g_business_group_id
2551 AND hoi1.organization_id = o1.organization_id
2552 AND hoi1.organization_id = csr_v_legal_employer_id
2553 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
2554 AND hoi1.org_information_context = 'CLASS'
2555 AND o1.organization_id = hoi2.organization_id
2556 AND hoi2.org_information_context = 'SE_LEGAL_EMPLOYER_DETAILS';
2557
2558 lr_legal_employer_details csr_legal_employer_details%ROWTYPE;
2559
2560 CURSOR csr_cost_center_details (csr_v_location_id NUMBER)
2561 IS
2562 SELECT hr_general.decode_lookup ('SE_ALECTA_COST_CENTER'
2563 ,lei_information1
2564 ) "COST_CENTER"
2565 FROM hr_location_extra_info
2566 WHERE location_id = csr_v_location_id
2567 AND information_type = 'SE_ALECTA_DETAILS';
2568 BEGIN
2569 OPEN csr_get_details;
2570
2571 FETCH csr_get_details
2572 INTO lr_get_details;
2573
2574 CLOSE csr_get_details;
2575
2576 --logger('LOCAL UNIT ==> ',lr_get_details.segment2);
2577 OPEN csr_find_legal_employer (lr_get_details.segment2);
2578
2579 FETCH csr_find_legal_employer
2580 INTO lr_find_legal_employer;
2581
2582 CLOSE csr_find_legal_employer;
2583
2584 --logger('LEGAL EMPLOYER ==> ',lr_find_legal_employer.legal_id);
2585 OPEN csr_legal_employer_details (lr_find_legal_employer.legal_id);
2586
2587 FETCH csr_legal_employer_details
2588 INTO lr_legal_employer_details;
2589
2590 CLOSE csr_legal_employer_details;
2591
2592 p_organization_number := lr_legal_employer_details.org_number;
2593
2594 --logger('p_organization_number ==> ',p_organization_number);
2595 OPEN csr_cost_center_details (lr_get_details.location_id);
2596
2597 FETCH csr_cost_center_details
2598 INTO p_cost_centre;
2599 CLOSE csr_cost_center_details;
2600 p_cost_centre:=nvl(p_cost_centre,'000');
2601 --logger('p_cost_centre ==> ',p_cost_centre);
2602 END get_assignment_lvl_info;
2603
2604 PROCEDURE get_person_lvl_info (
2605 p_assignment_id IN NUMBER
2606 ,p_effective_date IN DATE
2607 ,p_pin OUT NOCOPY VARCHAR2
2608 ,p_first_name OUT NOCOPY VARCHAR2
2609 ,p_last_name OUT NOCOPY VARCHAR2
2610 ,p_born_1979 OUT NOCOPY VARCHAR2
2611 )
2612 IS
2613 CURSOR csr_get_details
2614 IS
2615 SELECT pap.last_name
2616 ,pap.first_name
2617 ,pap.national_identifier
2618 ,pap.person_id
2619 ,paa.assignment_id
2620 ,paa.assignment_number
2621 ,paa.effective_start_date
2622 ,pap.date_of_birth
2623 FROM per_all_assignments_f paa
2624 ,per_all_people_f pap
2625 WHERE paa.assignment_id = p_assignment_id
2626 AND paa.person_id = pap.person_id
2627 AND pap.per_information_category = 'SE'
2628 AND p_effective_date BETWEEN pap.effective_start_date
2629 AND pap.effective_end_date
2630 AND p_effective_date BETWEEN paa.effective_start_date
2631 AND paa.effective_end_date;
2632
2633 lr_get_details csr_get_details%ROWTYPE;
2634 BEGIN
2635 OPEN csr_get_details;
2636
2637 FETCH csr_get_details
2638 INTO lr_get_details;
2639
2640 CLOSE csr_get_details;
2641
2642 p_pin := substr(TO_CHAR(lr_get_details.date_of_birth,'yyyy'),1,2)||replace(lr_get_details.national_identifier,'-','');
2643 p_first_name := lr_get_details.first_name;
2644 p_last_name := lr_get_details.last_name;
2645
2646 IF TO_CHAR (lr_get_details.date_of_birth, 'YYYY') >= 1979
2647 THEN
2648 p_born_1979 := 'AFTER';
2649 ELSE
2650 p_born_1979 := 'BEFORE';
2651 END IF;
2652 --logger('p_PIN ==> ',p_PIN);
2653 --logger('p_FIRST_NAME ==> ',p_FIRST_NAME);
2654 --logger('p_LAST_NAME ==> ',p_LAST_NAME);
2655 --logger('p_born_1979 ==> ',p_born_1979);
2656 END get_person_lvl_info;
2657
2658 PROCEDURE get_in_time_of_event (
2659 p_assignment_id IN NUMBER
2660 ,p_effective_date IN DATE
2661 ,p_event_date OUT NOCOPY DATE
2662 )
2663 IS
2664 CURSOR csr_get_details
2665 IS
2666 SELECT MONTHS_BETWEEN (g_start_date, pap.date_of_birth) / 12 "YEAR"
2667 ,pap.date_of_birth
2668 FROM per_all_assignments_f paa
2669 ,per_all_people_f pap
2670 WHERE paa.assignment_id = p_assignment_id
2671 AND paa.person_id = pap.person_id
2672 AND pap.per_information_category = 'SE'
2673 AND p_effective_date BETWEEN pap.effective_start_date
2674 AND pap.effective_end_date
2675 AND p_effective_date BETWEEN paa.effective_start_date
2676 AND paa.effective_end_date;
2677
2678 lr_get_details csr_get_details%ROWTYPE;
2679 BEGIN
2680 OPEN csr_get_details;
2681
2682 FETCH csr_get_details
2683 INTO lr_get_details;
2684
2685 CLOSE csr_get_details;
2686
2687 IF lr_get_details.YEAR >= 18
2688 THEN
2689 p_event_date := p_effective_date;
2690 ELSE
2691 p_event_date := ADD_MONTHS (lr_get_details.date_of_birth, 18 * 12);
2692 END IF;
2693 END get_in_time_of_event;
2694
2695 PROCEDURE get_absence_lvl_info (
2696 p_assignment_id IN NUMBER
2697 ,p_effective_date IN DATE
2698 ,p_fully_capable OUT NOCOPY VARCHAR2
2699 ,p_inability_to_work OUT NOCOPY VARCHAR2
2700 )
2701 IS
2702 CURSOR csr_get_details
2703 IS
2704 SELECT paa.person_id
2705 FROM per_all_assignments_f paa
2706 WHERE paa.assignment_id = p_assignment_id
2707 AND p_effective_date BETWEEN paa.effective_start_date
2708 AND paa.effective_end_date;
2709
2710 lr_get_details csr_get_details%ROWTYPE;
2711
2712 CURSOR csr_get_abs_details (csr_v_person_id NUMBER)
2713 IS
2714 SELECT paat.absence_category
2715 ,paa.date_start
2716 ,paa.date_end
2717 ,paa.abs_information3
2718 FROM per_absence_attendances paa
2719 ,per_absence_attendance_types paat
2720 WHERE paa.person_id = csr_v_person_id
2721 AND paa.absence_attendance_type_id =
2722 paat.absence_attendance_type_id
2723 AND paat.absence_category = 'S'
2724 AND paa.date_start >= g_start_date
2725 AND paa.date_start <= g_end_date
2726 ORDER BY paa.date_start desc;
2727
2728 lr_get_abs_details csr_get_abs_details%ROWTYPE;
2729 BEGIN
2730 OPEN csr_get_details;
2731
2732 FETCH csr_get_details
2733 INTO lr_get_details;
2734
2735 CLOSE csr_get_details;
2736
2737 OPEN csr_get_abs_details (lr_get_details.person_id);
2738
2739 FETCH csr_get_abs_details
2740 INTO lr_get_abs_details;
2741
2742 CLOSE csr_get_abs_details;
2743
2744 IF lr_get_abs_details.abs_information3 IS NOT NULL
2745 AND lr_get_abs_details.abs_information3 <> 100
2746 THEN
2747 p_fully_capable := /*'No';*/ 'false';
2748 p_inability_to_work :=
2749 NVL (lr_get_abs_details.abs_information3, '0');
2750 ELSE
2751 p_fully_capable := /*'Yes';*/ 'true';
2752 /* p_inability_to_work := '0';*/
2753 END IF;
2754
2755 END;
2756
2757 PROCEDURE get_org_lvl_info (
2758 p_assignment_id IN NUMBER
2759 ,p_effective_date IN DATE
2760 ,p_local_unit_id OUT NOCOPY NUMBER
2761 ,p_legal_employer_id OUT NOCOPY NUMBER
2762 ,p_location_id OUT NOCOPY NUMBER
2763 )
2764 IS
2765 CURSOR csr_get_details
2766 IS
2767 SELECT scl.segment2
2768 ,paa.location_id
2769 FROM per_all_assignments_f paa
2770 ,hr_soft_coding_keyflex scl
2771 WHERE paa.assignment_id = p_assignment_id
2772 AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
2773 AND p_effective_date BETWEEN paa.effective_start_date
2774 AND paa.effective_end_date;
2775
2776 lr_get_details csr_get_details%ROWTYPE;
2777
2778 -- Cursor to pick up LEGAL EMPLOYER
2779 CURSOR csr_find_legal_employer (
2780 csr_v_organization_id hr_organization_information.organization_id%TYPE
2781 )
2782 IS
2783 SELECT hoi3.organization_id legal_id
2784 FROM hr_all_organization_units o1
2785 ,hr_organization_information hoi1
2786 ,hr_organization_information hoi2
2787 ,hr_organization_information hoi3
2788 WHERE o1.business_group_id = g_business_group_id
2789 AND hoi1.organization_id = o1.organization_id
2790 AND hoi1.organization_id = csr_v_organization_id
2791 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
2792 AND hoi1.org_information_context = 'CLASS'
2793 AND o1.organization_id = hoi2.org_information1
2794 AND hoi2.org_information_context = 'SE_LOCAL_UNITS'
2795 AND hoi2.organization_id = hoi3.organization_id
2796 AND hoi3.org_information_context = 'CLASS'
2797 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER';
2798
2799 lr_find_legal_employer csr_find_legal_employer%ROWTYPE;
2800 BEGIN
2801 OPEN csr_get_details;
2802
2803 FETCH csr_get_details
2804 INTO lr_get_details;
2805
2806 CLOSE csr_get_details;
2807
2808 p_local_unit_id := lr_get_details.segment2;
2809 p_location_id := lr_get_details.location_id;
2810
2811
2812 OPEN csr_find_legal_employer (lr_get_details.segment2);
2813
2814 FETCH csr_find_legal_employer
2815 INTO lr_find_legal_employer;
2816
2817 CLOSE csr_find_legal_employer;
2818
2819 p_legal_employer_id := lr_find_legal_employer.legal_id;
2820
2821 END;
2822
2823 PROCEDURE get_salary (
2824 p_assignment_id IN NUMBER
2825 ,p_effective_date IN DATE
2826 ,p_before_after IN VARCHAR2
2827 ,p_event IN VARCHAR2
2828 ,p_monthly_salary OUT NOCOPY NUMBER
2829 ,p_yearly_salary OUT NOCOPY NUMBER
2830 )
2831 IS
2832 l_local_unit_id NUMBER;
2833 l_legal_employer_id NUMBER;
2834 l_location_id NUMBER;
2835 l_balance_type_id NUMBER;
2836 l_defined_balance_id NUMBER;
2837
2838 CURSOR csr_defined_balance_id (
2839 csr_v_balance_type_id NUMBER
2840 ,csr_v_dimesion VARCHAR2
2841 )
2842 IS
2843 SELECT db.defined_balance_id
2844 FROM pay_defined_balances db
2845 ,pay_balance_dimensions bd
2846 WHERE db.balance_type_id = csr_v_balance_type_id
2847 AND db.balance_dimension_id = bd.balance_dimension_id
2848 AND bd.database_item_suffix = csr_v_dimesion
2849 AND bd.legislation_code = 'SE';
2850 BEGIN
2851 l_legal_employer_id := NULL;
2852 l_local_unit_id := NULL;
2853 l_location_id := NULL;
2854 get_org_lvl_info (p_assignment_id
2855 ,p_effective_date
2856 ,l_local_unit_id
2857 ,l_legal_employer_id
2858 ,l_location_id
2859 );
2860
2861
2862 IF p_before_after = 'AFTER'
2863 THEN
2864 l_balance_type_id :=
2865 record_legal_employer (l_legal_employer_id).field_code ('MS1').events_row
2866 (p_event).balance_type_id;
2867
2868 /* OPEN csr_defined_balance_id(l_balance_type_id,'_ASG_MONTH');
2869 FETCH csr_defined_balance_id INTO l_defined_balance_id;
2870 CLOSE csr_defined_balance_id;*/
2871 p_monthly_salary :=ceil(
2872 get_defined_balance_value (l_balance_type_id
2873 ,'_ASG_MONTH'
2874 ,p_assignment_id
2875 ,g_end_date
2876 ));
2877
2878 p_yearly_salary := 0;
2879 ELSE
2880 l_balance_type_id :=
2881 record_legal_employer (l_legal_employer_id).field_code ('MS2').events_row
2882 (p_event).balance_type_id;
2883
2884 /*
2885 OPEN csr_defined_balance_id(l_balance_type_id,'_ASG_YTD');
2886 FETCH csr_defined_balance_id INTO l_defined_balance_id;
2887 CLOSE csr_defined_balance_id;
2888 */
2889 p_yearly_salary :=ceil(
2890 get_defined_balance_value (l_balance_type_id
2891 ,'_ASG_YTD'
2892 ,p_assignment_id
2893 ,ADD_MONTHS ( TRUNC (g_end_date
2894 ,'YYYY')
2895 - 1
2896 ,12
2897 )
2898 ));
2899
2900 p_monthly_salary := 0;
2901 END IF;
2902
2903 IF p_monthly_salary IS NULL THEN
2904 p_monthly_salary := 0;
2905 END IF;
2906
2907 END get_salary;
2908
2909 PROCEDURE get_salary_change_or_not (
2910 p_assignment_id IN NUMBER
2911 ,p_new_salary OUT NOCOPY VARCHAR2
2912 ,p_event_time OUT NOCOPY DATE
2913 )
2914 IS
2915 l_local_unit_id NUMBER;
2916 l_legal_employer_id NUMBER;
2917 l_location_id NUMBER;
2918 l_element_type_id NUMBER;
2919
2920 CURSOR csr_get_element (csr_v_element_type_id NUMBER)
2921 IS
2922 SELECT effective_start_date
2923 FROM pay_element_entries_f
2924 WHERE assignment_id = p_assignment_id
2925 AND element_type_id = csr_v_element_type_id
2926 AND effective_start_date BETWEEN g_start_date AND g_end_date;
2927
2928 lr_get_element csr_get_element%ROWTYPE;
2929 BEGIN
2930 l_legal_employer_id := NULL;
2931 l_local_unit_id := NULL;
2932 l_location_id := NULL;
2933 get_org_lvl_info (p_assignment_id
2934 ,g_end_date
2935 ,l_local_unit_id
2936 ,l_legal_employer_id
2937 ,l_location_id
2938 );
2939 l_element_type_id :=
2940 record_legal_employer (l_legal_employer_id).field_code ('ET').events_row
2941 ('LO').element_type_id;
2942
2943 lr_get_element := NULL;
2944
2945 OPEN csr_get_element (l_element_type_id);
2946
2947 FETCH csr_get_element
2948 INTO lr_get_element;
2949
2950 CLOSE csr_get_element;
2951
2952 IF lr_get_element.effective_start_date IS NULL
2953 THEN
2954 p_new_salary := NULL;
2955 p_event_time := NULL;
2956 ELSE
2957 p_new_salary := 'LO';
2958 p_event_time := lr_get_element.effective_start_date;
2959 END IF;
2960
2961
2962 END get_salary_change_or_not;
2963
2964 PROCEDURE get_salary_cut (
2965 p_assignment_id IN NUMBER
2966 ,p_effective_date IN DATE
2967 ,p_salary_cut OUT NOCOPY VARCHAR2
2968 )
2969 IS
2970 l_balance_type_id NUMBER;
2971 l_current_yearly_salary NUMBER;
2972 l_previous_yearly_salary NUMBER;
2973 l_local_unit_id NUMBER;
2974 l_legal_employer_id NUMBER;
2975 l_location_id NUMBER;
2976 l_min_assg_start_date DATE;
2977 CURSOR csr_min_assignment (csr_v_assignment_id NUMBER )
2978 IS
2979 SELECT min(effective_start_date)
2980 FROM per_all_assignments_f
2981 WHERE assignment_id=csr_v_assignment_id;
2982 BEGIN
2983
2984 get_org_lvl_info (p_assignment_id
2985 ,g_end_date
2986 ,l_local_unit_id
2987 ,l_legal_employer_id
2988 ,l_location_id
2989 );
2990 OPEN csr_min_assignment(p_assignment_id);
2991 FETCH csr_min_assignment INTO l_min_assg_start_date;
2992 CLOSE csr_min_assignment;
2993
2994 l_balance_type_id :=
2995 record_legal_employer (l_legal_employer_id).field_code ('MS2').events_row
2996 ('LO').balance_type_id;
2997
2998 l_current_yearly_salary :=ceil(
2999 get_defined_balance_value (l_balance_type_id
3000 ,'_ASG_YTD'
3001 ,p_assignment_id
3002 ,ADD_MONTHS (TRUNC (g_end_date, 'YYYY') - 1
3003 ,12
3004 )
3005 ));
3006
3007 IF l_min_assg_start_date > ( TRUNC (g_end_date, 'YYYY') - 1) THEN
3008 l_previous_yearly_salary:=0;
3009 ELSE
3010 l_previous_yearly_salary :=ceil(
3011 get_defined_balance_value (l_balance_type_id
3012 ,'_ASG_YTD'
3013 ,p_assignment_id
3014 , TRUNC (g_end_date, 'YYYY') - 1
3015 ));
3016 END IF;
3017
3018
3019 IF l_previous_yearly_salary > l_current_yearly_salary
3020 THEN
3021 p_salary_cut := 'Ja';
3022 ELSE
3023 p_salary_cut := NULL;
3024 END IF;
3025 END;
3026
3027 PROCEDURE get_end_employment_or_not (
3028 p_assignment_id IN NUMBER
3029 ,p_withdrawl OUT NOCOPY VARCHAR2
3030 ,p_event_time OUT NOCOPY DATE
3031 ,p_reason OUT NOCOPY VARCHAR2
3032 ,p_effective_date OUT NOCOPY DATE
3033 )
3034 IS
3035 l_local_unit_id NUMBER;
3036 l_legal_employer_id NUMBER;
3037 l_location_id NUMBER;
3038 l_element_type_id NUMBER;
3039
3040 CURSOR csr_get_assignments (csr_v_assignment_id NUMBER)
3041 IS
3042 SELECT scl.segment2
3043 ,scl.segment5
3044 ,scl.segment6
3045 ,scl.segment7
3046 ,GREATEST (effective_start_date, g_start_date)
3047 "EFF_START_DATE"
3048 ,LEAST (effective_end_date, g_end_date) "EFF_END_DATE"
3049 FROM per_all_assignments_f paa
3050 ,hr_soft_coding_keyflex scl
3051 WHERE paa.assignment_id = p_assignment_id
3052 AND paa.business_group_id = g_business_group_id
3053 AND scl.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
3054 AND paa.effective_start_date BETWEEN g_start_date AND g_end_date
3055 ORDER BY paa.effective_end_date DESC;
3056
3057 lr_get_assignments csr_get_assignments%ROWTYPE;
3058 BEGIN
3059 OPEN csr_get_assignments (p_assignment_id);
3060
3061 FETCH csr_get_assignments
3062 INTO lr_get_assignments;
3063
3064 CLOSE csr_get_assignments;
3065
3066 p_effective_date := lr_get_assignments.eff_start_date;
3067
3068
3069 IF lr_get_assignments.segment5 IS NOT NULL
3070 AND lr_get_assignments.segment6 IS NOT NULL
3071 THEN
3072 p_withdrawl := 'AV';
3073 p_event_time :=
3074 fnd_date.canonical_to_date (lr_get_assignments.segment6);
3075 p_reason := '1';
3076 ELSE
3077 p_withdrawl := NULL;
3078 p_event_time := NULL;
3079 p_reason := NULL;
3080 END IF;
3081
3082
3083 END;
3084
3085 PROCEDURE get_termination_or_not (
3086 p_assignment_id IN NUMBER
3087 ,p_field_code IN VARCHAR2
3088 ,p_withdrawl OUT NOCOPY VARCHAR2
3089 ,p_event_time OUT NOCOPY DATE
3090 ,p_reason OUT NOCOPY VARCHAR2
3091 ,p_effective_date OUT NOCOPY DATE
3092 ,p_parental_start_date OUT NOCOPY DATE
3093 )
3094 IS
3095 l_local_unit_id NUMBER;
3096 l_legal_employer_id NUMBER;
3097 l_location_id NUMBER;
3098 l_element_type_id NUMBER;
3099 l_input_value_id NUMBER;
3100
3101 CURSOR csr_get_element (
3102 csr_v_element_type_id NUMBER
3103 ,csr_v_input_value_id NUMBER
3104 )
3105 IS
3106 SELECT peef.effective_start_date
3107 ,peevf.screen_entry_value
3108 FROM pay_element_entries_f peef
3109 ,pay_element_entry_values_f peevf
3110 WHERE peef.assignment_id = p_assignment_id
3111 AND peef.element_type_id = csr_v_element_type_id
3112 AND peef.effective_start_date BETWEEN g_start_date AND g_end_date
3113 AND peevf.effective_start_date BETWEEN g_start_date AND g_end_date
3114 AND peef.element_entry_id=peevf.element_entry_id
3115 AND peevf.input_value_id = csr_v_input_value_id;
3116
3117 lr_get_element csr_get_element%ROWTYPE;
3118 BEGIN
3119 l_legal_employer_id := NULL;
3120 l_local_unit_id := NULL;
3121 l_location_id := NULL;
3122 get_org_lvl_info (p_assignment_id
3123 ,g_end_date
3124 ,l_local_unit_id
3125 ,l_legal_employer_id
3126 ,l_location_id
3127 );
3128
3129 l_element_type_id := NULL;
3130 l_input_value_id := NULL;
3131 l_element_type_id :=
3132 record_legal_employer (l_legal_employer_id).field_code ('TR').events_row
3133 (p_field_code).element_type_id;
3134
3135 l_input_value_id :=
3136 record_legal_employer (l_legal_employer_id).field_code ('TR').events_row
3137 (p_field_code).input_value_id;
3138
3139 lr_get_element := NULL;
3140
3141 OPEN csr_get_element (l_element_type_id, l_input_value_id);
3142
3143 FETCH csr_get_element
3144 INTO lr_get_element;
3145 p_effective_date := lr_get_element.effective_start_date;
3146 CLOSE csr_get_element;
3147
3148
3149 p_effective_date := lr_get_element.effective_start_date;
3150
3151
3152 IF lr_get_element.screen_entry_value IS NULL
3153 THEN
3154 p_withdrawl := NULL;
3155 p_event_time := NULL;
3156 p_reason := NULL;
3157 ELSE
3158 p_withdrawl := 'AV';
3159 p_reason := lr_get_element.screen_entry_value;
3160 l_element_type_id := NULL;
3161 l_input_value_id := NULL;
3162 l_element_type_id :=
3163 record_legal_employer (l_legal_employer_id).field_code ('ET').events_row
3164 (p_field_code).element_type_id;
3165
3166 l_input_value_id :=
3167 record_legal_employer (l_legal_employer_id).field_code ('ET').events_row
3168 (p_field_code).input_value_id;
3169
3170 lr_get_element := NULL;
3171
3172 OPEN csr_get_element (l_element_type_id, l_input_value_id);
3173
3174 FETCH csr_get_element
3175 INTO lr_get_element;
3176
3177 CLOSE csr_get_element;
3178
3179
3180 p_event_time :=
3181 fnd_date.canonical_to_date (lr_get_element.screen_entry_value);
3182 END IF;
3183
3184 IF p_field_code = 'AV2'
3185 THEN
3186 l_element_type_id := NULL;
3187 l_input_value_id := NULL;
3188 p_parental_start_date := NULL;
3189 l_element_type_id :=
3190 record_legal_employer (l_legal_employer_id).field_code ('PL').events_row
3191 (p_field_code).element_type_id;
3192
3193 l_input_value_id :=
3194 record_legal_employer (l_legal_employer_id).field_code ('PL').events_row
3195 (p_field_code).input_value_id;
3196
3197 lr_get_element := NULL;
3198
3199 OPEN csr_get_element (l_element_type_id, l_input_value_id);
3200
3201 FETCH csr_get_element
3202 INTO lr_get_element;
3203
3204 CLOSE csr_get_element;
3205
3206
3207 p_parental_start_date :=
3208 fnd_date.canonical_to_date (lr_get_element.screen_entry_value);
3209 ELSE
3210 p_parental_start_date := NULL;
3211 END IF;
3212
3213
3214 END;
3215 END pay_se_alecta;