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