[Home] [Help]
PACKAGE BODY: APPS.PER_NL_DSR_ARCHIVE
Source
1 PACKAGE BODY PER_NL_DSR_ARCHIVE as
2 /* $Header: penldsr.pkb 120.9.12020000.1 2012/07/17 08:51:12 asudhaka noship $ */
3
4
5 EOL VARCHAR2(5) := fnd_global.newline();
6 g_SOURCE VARCHAR2(4000);
7 g_TARGET VARCHAR2(4000);
8
9 /**************************************************************************/
10 -- typecasts CLOB to BLOB (binary conversion)
11
12 FUNCTION c2b( c IN CLOB ) RETURN BLOB
13 IS
14 pos PLS_INTEGER := 1;
15 buffer RAW(32767);
16 res BLOB;
17 lob_len PLS_INTEGER := dbms_lob.getlength(c);
18 BEGIN
19 -- hr_utility.trace('+====c2b===================================================+');
20 dbms_lob.createtemporary(res,TRUE);
21 dbms_lob.open(res,dbms_lob.lob_readwrite);
22 LOOP
23 buffer := utl_raw.cast_to_raw( dbms_lob.substr( c, 16000, pos ) );
24 IF utl_raw.length( buffer ) > 0 THEN
25 dbms_lob.writeappend(res,utl_raw.length(buffer),buffer);
26 END IF;
27 pos := pos + 16000;
28 EXIT WHEN pos > lob_len;
29 END LOOP;
30 -- hr_utility.trace('X====c2b===================================================X');
31 RETURN res;
32 EXCEPTION
33 WHEN others THEN
34 hr_utility.trace('Exception in c2b SQL-ERRM : '||SQLERRM);
35 END c2b;
36 /**************************************************************************/
37
38 /*------------------------------------------------------------------------------
39 |Name : clean_XML |
40 |Type : Procedure |
41 |Description: Procedure to replace unallowed characters in XML Values |
42 -------------------------------------------------------------------------------*/
43
44
45 FUNCTION clean_XML(P_STRING IN VARCHAR2) RETURN VARCHAR2 AS
46 l_string varchar2(1000);
47 BEGIN
48 l_string := p_string;
49 l_string := REPLACE(l_string, '&', '&'||'amp;');
50 l_string := REPLACE(l_string, '<', '&'||'lt;'); --#60
51 l_string := REPLACE(l_string, '>', '&'||'gt;'); --#62
52 l_string := REPLACE(l_string, '''','&'||'apos;');
53 l_string := REPLACE(l_string, '"', '&'||'quot;');
54
55 --Diacritical marks handling--
56 IF g_SOURCE IS NOT NULL THEN
57 l_string:= translate(l_string,g_SOURCE,g_TARGET);
58 END IF;
59 RETURN l_string;
60 EXCEPTION
61 WHEN others THEN
62 hr_utility.trace('Exception in clean_XML SQL-ERRM :'||SQLERRM);
63 RETURN l_string;
64 END clean_XML;
65 /**************************************************************************/
66
67
68
69
70
71 /*-----------------------------------------------------------------------------
72 |Name : write_file |
73 |Type : Procedure |
74 |Description: Procedure to create an XML file output in utl_file_dir |
75 -------------------------------------------------------------------------------*/
76
77 PROCEDURE write_file(p_XMLTable IN tXMLTable
78 ,p_file_name IN VARCHAR) IS
79
80 l_str1 VARCHAR2(80) ;
81 l_xml_element VARCHAR2(800);
82 l_file UTL_FILE.FILE_TYPE;
83 l_directory_path VARCHAR2(500);
84 l_file_name VARCHAR2(50);
85
86 CURSOR cur_get_directory_path IS
87 SELECT value
88 FROM v$parameter
89 WHERE lower (name) = 'utl_file_dir';
90
91 BEGIN
92 hr_utility.trace('+====write_file============================================+');
93 OPEN cur_get_directory_path;
94 FETCH cur_get_directory_path INTO l_directory_path;
95 CLOSE cur_get_directory_path;
96
97 IF INSTR(l_directory_path,',') > 0 THEN
98 l_directory_path := SUBSTR(l_directory_path, 1, INSTR(l_directory_path,',')-1);
99 END IF;
100
101 l_file := utl_file.fopen(l_directory_path, p_file_name, 'W');
102 l_str1 := '<?xml version="1.0" encoding="ISO-8859-1"?>'||EOL;
103 utl_file.put_line(l_file, l_str1);
104
105 IF p_XMLTable.COUNT > 0 THEN
106
107 FOR table_counter IN p_XMLTable.FIRST .. p_XMLTable.LAST
108 LOOP
109 IF p_XMLTable(table_counter).tagvalue = '_START_' THEN
110 l_xml_element := '<' || p_XMLTable(table_counter).tagname || '>';
111 ELSIF p_XMLTable(table_counter).tagvalue = '_END_' THEN
112 l_xml_element := '</' || p_XMLTable(table_counter).tagname || '>';
113 ELSE
114 l_xml_element := '<' || p_XMLTable(table_counter).tagname ||
115 '>'|| clean_XML(p_XMLTable(table_counter).tagvalue) ||
116 '</'||p_XMLTable(table_counter).tagname || '>';
117 END IF;
118 -- IF p_XMLTable(table_counter).tagvalue IS NOT NULL THEN
119 utl_file.put_line(l_file,l_xml_element);
120 -- END IF;
121 END LOOP;
122 END IF;
123 utl_file.fclose(l_file);
124 hr_utility.trace('Written file : '||l_directory_path||'/'||p_file_name);
125 hr_utility.trace('x====write_file============================================x');
126 EXCEPTION
127 WHEN others THEN
128 hr_utility.trace('Exception in write_file SQL-ERRM : '||SQLERRM);
129 hr_utility.raise_error;
130 END write_file;
131 /**************************************************************************/
132
133
134
135
136
137 /*-----------------------------------------------------------------------------
138 |Name : write_report |
139 |Type : Procedure |
140 |Description: Procedure to append the assignment details to main report XML |
141 -------------------------------------------------------------------------------*/
142
143 PROCEDURE write_report(p_XMLTable IN tXMLTable) IS
144 l_xml_element VARCHAR2(800);
145 l_clob CLOB;
146 BEGIN
147 hr_utility.trace('+====write_report==========================================+');
148 dbms_lob.createtemporary(l_clob, FALSE, DBMS_LOB.CALL);
149 dbms_lob.open(l_clob, DBMS_LOB.LOB_READWRITE);
150
151 IF p_XMLTable.COUNT > 0 THEN
152
153 FOR table_counter IN p_XMLTable.FIRST .. p_XMLTable.LAST
154 LOOP
155 IF p_XMLTable(table_counter).tagvalue = '_START_' THEN
156 l_xml_element := '<' || p_XMLTable(table_counter).tagname || '>'||EOL;
157 ELSIF p_XMLTable(table_counter).tagvalue = '_END_' THEN
158 l_xml_element := '</' || p_XMLTable(table_counter).tagname || '>'||EOL;
159 ELSE
160 l_xml_element := '<' || p_XMLTable(table_counter).tagname ||
161 '>'|| clean_XML(p_XMLTable(table_counter).tagvalue) ||
162 '</'||p_XMLTable(table_counter).tagname || '>'||EOL;
163 END IF;
164 -- IF p_XMLTable(table_counter).tagvalue IS NOT NULL THEN
165 dbms_lob.writeappend(l_clob, LENGTH(l_xml_element), l_xml_element);
166 -- END IF;
167 END LOOP;
168 END IF;
169 IF l_clob IS NOT NULL THEN
170 pay_core_files.write_to_magtape_lob(c2b(l_clob));
171 END IF;
172 hr_utility.trace('x====write_report==========================================x');
173 EXCEPTION
174 WHEN others THEN
175 hr_utility.trace('Exception in write_report SQL-ERRM : '||SQLERRM);
176 hr_utility.raise_error;
177 END write_report;
178 /**************************************************************************/
179
180 FUNCTION get_look_up_value(p_lookup_type IN VARCHAR2
181 ,p_lookup_code IN VARCHAR2
182 ,p_language IN VARCHAR2 DEFAULT 'US')
183 RETURN VARCHAR2 IS
184
185 CURSOR c_get_look_up_value (c_lookup_type IN VARCHAR2
186 ,c_lookup_code IN VARCHAR2
187 ,c_language IN VARCHAR2) IS
188 SELECT meaning
189 FROM fnd_lookup_values
190 WHERE lookup_type = c_lookup_type
191 AND language = c_language
192 AND lookup_code = c_lookup_code;
193
194 BEGIN
195 FOR r_get_look_up_value in c_get_look_up_value(p_lookup_type,p_lookup_code,p_language)
196 LOOP
197 RETURN r_get_look_up_value.meaning;
198 END LOOP;
199 RETURN NULL;
200 EXCEPTION
201 WHEN TOO_MANY_ROWS THEN
202 RETURN NULL;
203 WHEN NO_DATA_FOUND THEN
204 RETURN NULL;
205 WHEN others THEN
206 hr_utility.trace('Exception in get_look_up_value SQL-ERRM : '||SQLERRM);
207
208 END get_look_up_value;
209 /**************************************************************************/
210
211
212
213 /*------------------------------------------------------------------------------
214 |Name : GET_PARAMETER |
215 |Type : Function |
216 |Description : Funtion to get the parameters of the archive process |
217 -------------------------------------------------------------------------------*/
218
219 FUNCTION get_parameter (p_parameter_string IN VARCHAR2
220 ,p_token IN VARCHAR2
221 ,p_segment_number IN NUMBER DEFAULT NULL )
222 RETURN VARCHAR2 IS
223
224 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
225 l_start_pos NUMBER;
226 l_delimiter varchar2(1):=' ';
227
228 BEGIN
229 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
230 --
231 IF l_start_pos = 0 THEN
232 l_delimiter := '|';
233 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
234 end if;
235
236 IF l_start_pos <> 0 THEN
237 l_start_pos := l_start_pos + length(p_token||'=');
238 l_parameter := substr(p_parameter_string,
239 l_start_pos,
240 instr(p_parameter_string||' ',
241 l_delimiter,l_start_pos)
242 - l_start_pos);
243 IF p_segment_number IS NOT NULL THEN
244 l_parameter := ':'||l_parameter||':';
245 l_parameter := substr(l_parameter,
246 instr(l_parameter,':',1,p_segment_number)+1,
247 instr(l_parameter,':',1,p_segment_number+1) -1
248 - instr(l_parameter,':',1,p_segment_number));
249 END IF;
250 END IF;
251 RETURN l_parameter;
252 END get_parameter;
253 /**************************************************************************/
254
255
256
257
258
259 /*------------------------------------------------------------------------------
260 |Name : GET_ALL_PARAMETERS |
261 |Type : Procedure |
262 |Description: Procedure which returns all the parameters of the archive process|
263 -------------------------------------------------------------------------------*/
264
265
266 PROCEDURE get_all_parameters (p_payroll_action_id IN NUMBER
267 ,p_org_struct_id OUT NOCOPY NUMBER
268 ,p_person_id OUT NOCOPY NUMBER
269 ,p_org_id OUT NOCOPY NUMBER
270 ,p_bg_id OUT NOCOPY NUMBER
271 ,p_start_date OUT NOCOPY DATE
272 ,p_end_date OUT NOCOPY DATE
273 ,p_type OUT NOCOPY VARCHAR) IS
274
275 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
276 SELECT to_number(get_parameter(legislative_parameters,'ORG_STRUCT_ID')) org_struct_id
277 ,to_number(get_parameter(legislative_parameters,'PERSON_ID')) person_id
278 ,to_number(get_parameter(legislative_parameters,'ORG_ID')) org_id
279 ,business_group_id bg_id
280 ,start_date start_date
281 ,effective_date end_date
282 ,get_parameter(legislative_parameters,'TYPE') report_type
283 FROM pay_payroll_actions
284 WHERE payroll_action_id = p_payroll_action_id;
285
286 l_param csr_parameter_info%ROWTYPE;
287
288 BEGIN
289 OPEN csr_parameter_info (p_payroll_action_id);
290 FETCH csr_parameter_info INTO l_param;
291 CLOSE csr_parameter_info;
292
293 p_org_struct_id := l_param.org_struct_id;
294 p_person_id := l_param.person_id;
295 p_org_id := l_param.org_id;
296 p_bg_id := l_param.bg_id;
297 p_type := l_param.report_type;
298 p_start_date := l_param.start_date;
299 p_end_date := l_param.end_date;
300
301 EXCEPTION
302 WHEN others THEN
303 hr_utility.trace('Exception in get_all_parameters SQLERRM: '||SQLERRM);
304 END get_all_parameters;
305 /**************************************************************************/
306
307
308
309
310 /*------------------------------------------------------------------------------
311 |Name : GET_PERSON_DETAILS |
312 |Type : Function |
313 |Description : Function returning Person EIT details to Absence EIT |
314 -------------------------------------------------------------------------------*/
315
316 FUNCTION get_person_details (p_id IN NUMBER
317 ,p_effective_date IN DATE
318 ,p_info IN VARCHAR2
319 ,p_language IN VARCHAR2 DEFAULT userenv('lang')) RETURN VARCHAR2 AS
320
321 CURSOR c_get_person_eit_details(c_person_id IN NUMBER
322 , c_effective_date IN DATE) IS
323 SELECT pei_information3 standby_emp
324 ,pei_information4 art_4_5
325 ,pei_information5 art_29
326 ,pei_information6 sickness_reference
327 ,pei_information7 payment_to_employee
328 ,pei_information8 workday_saturday
329 ,pei_information9 workday_sunday
330 ,pei_information10 first_day_si
331 ,pei_information11 daily_si
332 ,pei_information12 accident
333 ,pei_information13 work_hours
334 ,pei_information14 partial_recovery_date
335 ,pei_information15 termination_notice_date
336 ,pei_information16 immediate_termination
337 ,pei_information17 period_lt_3_months
338 ,pei_information18 first_call
339 ,pei_information19 exceeds_allowed_period
340 ,pei_information20 temporary_contracts
341 FROM per_people_extra_info
342 WHERE information_type = 'NL_DS_SICK_INFO'
343 AND person_id = c_person_id
344 AND c_effective_date BETWEEN fnd_date.canonical_to_date (pei_information1)
345 AND nvl (fnd_date.canonical_to_date (pei_information2),hr_general.end_of_time);
346 /**************************************************************************/
347 CURSOR cur_get_tml_info(c_assignment_id IN NUMBER
348 , c_effective_date IN DATE) IS
349 SELECT aei.aei_information3 temp_worker
350 FROM per_assignment_extra_info aei
351 WHERE aei.assignment_id = c_assignment_id
352 AND aei_information_category = 'NL_TML'
353 AND c_effective_date BETWEEN fnd_date.canonical_to_date (aei_information1)
354 AND nvl (fnd_date.canonical_to_date (aei_information2)
355 ,hr_general.end_of_time);
356 /**************************************************************************/
357 CURSOR cur_get_person_id(c_assignment_id IN NUMBER) IS
358 SELECT pas.person_id
359 FROM per_all_assignments_f pas
360 WHERE pas.assignment_id = c_assignment_id;
361 /**************************************************************************/
362
363 l_tml_info cur_get_tml_info%ROWTYPE;
364 l_person_id NUMBER := 0;
365 BEGIN
366 IF lower(p_info) = 'sickness_reason' THEN
367 OPEN cur_get_tml_info(p_id, p_effective_date); --assignment_id passed
368 FETCH cur_get_tml_info INTO l_tml_info;
369 CLOSE cur_get_tml_info;
370 IF NVL(l_tml_info.temp_worker,'') = '17' THEN
371 RETURN get_look_up_value('HR_NL_DS_REASON','01',p_language);
372 ELSE
373 OPEN cur_get_person_id(p_id); --assignment_id passed
374 FETCH cur_get_person_id INTO l_person_id;
375 CLOSE cur_get_person_id;
376
377 FOR r_get_person_eit_details in c_get_person_eit_details(l_person_id,p_effective_date)
378 LOOP
379 IF NVL(r_get_person_eit_details.art_29,' ') IN
380 ('01', '02', '03', '04', '05', '06', '07', '08', '09','10', '11', '99') THEN
381 RETURN get_look_up_value('HR_NL_DS_REASON','04',p_language);
382 ELSIF NVL(r_get_person_eit_details.art_4_5,' ') = 'Y' THEN
383 RETURN get_look_up_value('HR_NL_DS_REASON','05',p_language);
384 ELSIF NVL(r_get_person_eit_details.standby_emp,' ') = 'Y' THEN
385 RETURN get_look_up_value('HR_NL_DS_REASON','02',p_language);
386 ELSE
387 RETURN NULL;
388 END IF;
389 END LOOP;
390 END IF;
391
392 ELSE
393 FOR r_get_person_eit_details in c_get_person_eit_details(p_id,p_effective_date)
394 LOOP
395 CASE lower(p_info)
396 WHEN 'sickness_reference' THEN
397 RETURN r_get_person_eit_details.sickness_reference;
398 WHEN 'payment_to_employee' THEN
399 RETURN get_look_up_value('HR_NL_YES_NO',r_get_person_eit_details.payment_to_employee,p_language);
400 WHEN 'workday_saturday' THEN
401 RETURN get_look_up_value('HR_NL_YES_NO',r_get_person_eit_details.workday_saturday,p_language);
402 WHEN 'workday_sunday' THEN
403 RETURN get_look_up_value('HR_NL_YES_NO',r_get_person_eit_details.workday_sunday,p_language);
404 WHEN 'first_day_si' THEN
405 RETURN fnd_number.canonical_to_number(r_get_person_eit_details.first_day_si);
406 WHEN 'daily_si' THEN
407 RETURN fnd_number.canonical_to_number(r_get_person_eit_details.daily_si);
408 WHEN 'accident' THEN
409 RETURN get_look_up_value('HR_NL_DS_ACCIDENT',r_get_person_eit_details.accident,p_language);
410 WHEN 'work_hours' THEN
411 RETURN fnd_number.canonical_to_number(r_get_person_eit_details.work_hours);
412 WHEN 'partial_recovery_date' THEN
413 RETURN fnd_date.canonical_to_date (r_get_person_eit_details.partial_recovery_date);
414 WHEN 'termination_notice_date' THEN
415 RETURN fnd_date.canonical_to_date (r_get_person_eit_details.termination_notice_date);
416 WHEN 'immediate_termination' THEN
417 RETURN get_look_up_value('HR_NL_YES_NO',r_get_person_eit_details.immediate_termination,p_language);
418 WHEN 'period_lt_3_months' THEN
419 RETURN get_look_up_value('HR_NL_YES_NO',r_get_person_eit_details.period_lt_3_months,p_language);
420 WHEN 'first_call' THEN
421 RETURN fnd_number.canonical_to_number(r_get_person_eit_details.first_call);
422 WHEN 'exceeds_allowed_period' THEN
423 RETURN get_look_up_value('HR_NL_YES_NO',r_get_person_eit_details.exceeds_allowed_period,p_language);
424 WHEN 'temporary_contracts' THEN
425 RETURN fnd_number.canonical_to_number(r_get_person_eit_details.temporary_contracts);
426 ELSE
427 RETURN NULL;
428 END CASE;
429 END LOOP;
430 END IF;
431 RETURN NULL;
432 EXCEPTION
433 WHEN TOO_MANY_ROWS THEN
434 RETURN NULL;
435 WHEN NO_DATA_FOUND THEN
436 RETURN NULL;
437 END get_person_details;
438 /**************************************************************************/
439
440
441 /*------------------------------------------------------------------------------
442 |Name : EMP_CHECK |
443 |Type : Function |
444 |Description : Function required for valueset HR_NL_EMPLOYEE_DSR |
445 -------------------------------------------------------------------------------*/
446
447 FUNCTION emp_check (p_bg_id IN NUMBER
448 ,p_org_struct_id IN NUMBER
449 ,p_org_id IN NUMBER
450 ,p_person_id IN NUMBER
451 ,p_start_date IN DATE
452 ,p_end_date IN DATE) RETURN NUMBER IS
453
454 CURSOR csr_org_check(c_bg_id IN NUMBER
455 ,c_org_struct_id IN NUMBER
456 ,c_org_id IN NUMBER
457 ,c_person_id IN NUMBER
458 ,c_start_date IN DATE
459 ,c_end_date IN DATE) IS
460 SELECT 1
461 FROM per_all_assignments_f paa
462 ,per_assignment_status_types past
463 ,per_all_people_f pap
464 ,per_org_structure_versions posv
465 ,per_absence_attendances pab
466 ,per_absence_attendance_types paat
467 WHERE posv.organization_structure_id = c_org_struct_id
468 AND posv.date_from <= c_start_date
469 AND nvl (posv.date_to
470 ,hr_general.end_of_time) >= c_end_date
471 AND (
472 paa.organization_id IN
473 (
474 (
475 SELECT pose.organization_id_child
476 FROM per_org_structure_elements pose
477 WHERE pose.org_structure_version_id = posv.org_structure_version_id
478 START WITH pose.organization_id_parent = c_org_id
479 CONNECT BY PRIOR organization_id_child = organization_id_parent
480 )
481 UNION
482 (
483 SELECT c_org_id
484 FROM dual
485 )
486 )
487 OR nvl (paa.establishment_id,- 1) = c_org_id
488 )
489 AND paa.person_id = c_person_id
490 AND paat.absence_category = 'S'
491 AND pab.abs_information_category = 'NL_S'
492 AND pab.abs_information4 = 'S'
493 AND pab.business_group_id = pap.business_group_id
494 AND pab.date_start BETWEEN c_start_date
495 AND c_end_date
496 AND pab.absence_attendance_type_id = paat.absence_attendance_type_id
497 AND pab.business_group_id = paat.business_group_id
498 AND paa.business_group_id = c_bg_id
499 AND pap.person_id = c_person_id
500 AND pap.business_group_id = paa.business_group_id;
501 /**************************************************************************/
502 CURSOR csr_org_struct_check(c_bg_id IN NUMBER
503 ,c_org_struct_id IN NUMBER
504 ,c_org_id IN NUMBER
505 ,c_person_id IN NUMBER
506 ,c_start_date IN DATE
507 ,c_end_date IN DATE) IS
508 SELECT 1
509 FROM per_all_assignments_f paa
510 ,per_all_people_f pap
511 ,per_org_structure_versions posv
512 ,per_absence_attendances pab
513 ,per_absence_attendance_types paat
514 WHERE posv.organization_structure_id = c_org_struct_id
515 AND posv.date_from <= c_start_date
516 AND nvl (posv.date_to
517 ,hr_general.end_of_time) >= c_end_date
518 AND paa.person_id = c_person_id
519 AND paa.business_group_id = c_bg_id
520 AND pap.person_id = paa.person_id
521 AND pap.business_group_id = paa.business_group_id
522 AND pab.business_group_id = pap.business_group_id
523 AND pab.person_id = pap.person_id
524 AND pab.date_start BETWEEN c_start_date
525 AND c_end_date
526 AND pab.absence_attendance_type_id = paat.absence_attendance_type_id
527 AND pab.business_group_id = paat.business_group_id
528 AND paat.absence_category = 'S'
529 AND pab.abs_information_category = 'NL_S'
530 AND pab.abs_information4 = 'S'
531 AND (
532 hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
533 ,paa.organization_id) IS NOT NULL
534 OR per_nl_dsr_archive.org_check (pap.business_group_id
535 ,NULL
536 ,nvl (paa.establishment_id,- 1)
537 ,c_start_date
538 ,c_end_date) = 1
539 )
540 AND paa.organization_id IN
541 (
542 SELECT pose.organization_id_parent
543 FROM per_org_structure_elements pose
544 WHERE posv.org_structure_version_id = pose.org_structure_version_id
545 UNION
546 SELECT pose.organization_id_child
547 FROM per_org_structure_elements pose
548 WHERE posv.org_structure_version_id = pose.org_structure_version_id
549 );
550 /**************************************************************************/
551 l_return_val NUMBER := 0;
552 l_start_date DATE;
553 l_end_date DATE;
554
555 BEGIN
556 hr_utility.trace('+====emp_check============================================+');
557 IF p_org_id is not NULL THEN
558 BEGIN
559 OPEN csr_org_check(p_bg_id
560 ,p_org_struct_id
561 ,p_org_id
562 ,p_person_id
563 ,p_start_date
564 ,p_end_date );
565 FETCH csr_org_check INTO l_return_val;
566 CLOSE csr_org_check;
567
568 EXCEPTION
569 WHEN TOO_MANY_ROWS THEN
570 l_return_val := 1;
571 WHEN NO_DATA_FOUND THEN
572 null;
573 WHEN OTHERS THEN
574 hr_utility.trace('Exception in emp_check org. SQLERRM : '||SQLERRM);
575 END;
576 hr_utility.trace('l_return_val : '||l_return_val);
577 ELSIF p_org_struct_id is not NULL THEN
578 BEGIN
579 OPEN csr_org_struct_check(p_bg_id
580 ,p_org_struct_id
581 ,p_org_id
582 ,p_person_id
583 ,p_start_date
584 ,p_end_date );
585 FETCH csr_org_struct_check INTO l_return_val;
586 CLOSE csr_org_struct_check;
587 EXCEPTION
588 WHEN TOO_MANY_ROWS THEN
589 l_return_val := 1;
590 WHEN NO_DATA_FOUND THEN
591 NULL;
592 WHEN OTHERS THEN
593 hr_utility.trace('Exception in emp_check org_struct. SQLERRM : '||SQLERRM);
594 END;
595 hr_utility.trace('l_return_val : '||l_return_val);
596 END IF;
597 hr_utility.trace('X====emp_check============================================X');
598 RETURN l_return_val;
599 EXCEPTION
600 WHEN OTHERS THEN
601 hr_utility.trace('Exception in emp_check SQL-ERRM : '||SQLERRM);
602 END emp_check;
603 /**************************************************************************/
604
605
606
607
608
609 /*------------------------------------------------------------------------------
610 |Name : ORG_CHECK |
611 |Type : Function |
612 |Description : Function required for valueset HR_NL_EMPLOYER_DSR |
613 -------------------------------------------------------------------------------*/
614 FUNCTION org_check (p_bg_id IN NUMBER
615 ,p_org_struct_id IN NUMBER
616 ,p_org_id IN NUMBER
617 ,p_start_date IN DATE
618 ,p_end_date IN DATE)
619 RETURN NUMBER IS
620
621 CURSOR csr_org_struct_check (c_bg_id IN NUMBER
622 ,c_org_id IN NUMBER) IS
623
624 SELECT 1
625 FROM hr_organization_units hou
626 WHERE hou.business_group_id = c_bg_id
627 AND hou.organization_id = c_org_id
628 AND EXISTS
629 (
630 SELECT 1
631 FROM hr_all_organization_units hou1
632 ,hr_organization_information hoi1
633 WHERE hou1.business_group_id = c_bg_id
634 AND hoi1.org_information_context = 'NL_LE_TAX_DETAILS'
635 AND hoi1.org_information1 IS NOT NULL
636 AND hoi1.org_information2 IS NOT NULL
637 AND hou1.organization_id = hoi1.organization_id
638 AND hou1.organization_id = hou.organization_id
639 UNION
640 SELECT 1
641 FROM hr_all_organization_units hou2
642 ,hr_organization_information hoi2
643 WHERE hou2.business_group_id = c_bg_id
644 AND hoi2.org_information_context = 'NL_ORG_INFORMATION'
645 AND hoi2.org_information4 IS NOT NULL
646 AND hoi2.org_information3 IS NOT NULL
647 AND hou2.organization_id = hoi2.organization_id
648 AND hou2.organization_id = hou.organization_id
649 );
650 /**************************************************************************/
651 CURSOR csr_org_check (c_bg_id IN NUMBER
652 ,c_org_id IN NUMBER
653 ,c_org_struct_id IN NUMBER
654 ,c_start_date IN DATE
655 ,c_end_date IN DATE) IS
656
657 SELECT 1
658 FROM hr_organization_units hou
659 WHERE hou.organization_id = c_org_id
660 AND hou.business_group_id = c_bg_id
661 AND EXISTS
662 (
663 SELECT 1
664 FROM hr_organization_units hou1
665 ,hr_organization_information hoi1
666 WHERE hoi1.org_information_context = 'NL_ORG_INFORMATION'
667 AND hou1.business_group_id = c_bg_id
668 AND hou1.organization_id = hou.organization_id
669 AND hou1.organization_id = hoi1.organization_id
670 AND hoi1.org_information4 IS NOT NULL
671 AND hoi1.org_information3 IS NOT NULL
672 AND hou1.organization_id IN
673 (
674 SELECT pose.organization_id_parent
675 FROM per_org_structure_elements pose
676 ,per_org_structure_versions posv
677 WHERE posv.org_structure_version_id = pose.org_structure_version_id
678 AND posv.organization_structure_id = c_org_struct_id
679 AND posv.date_from <= c_start_date
680 AND nvl (posv.date_to
681 ,hr_general.end_of_time) >= c_end_date
682 UNION
683 SELECT pose.organization_id_child
684 FROM per_org_structure_elements pose
685 ,per_org_structure_versions posv
686 WHERE posv.org_structure_version_id = pose.org_structure_version_id
687 AND posv.organization_structure_id = c_org_struct_id
688 AND posv.date_from <= c_start_date
689 AND nvl (posv.date_to
690 ,hr_general.end_of_time) >= c_end_date
691 )
692 UNION
693 SELECT 1
694 FROM hr_organization_units hou2
695 ,hr_organization_information hoi2
696 WHERE hoi2.org_information_context = 'NL_LE_TAX_DETAILS'
697 AND hou2.business_group_id = c_bg_id
698 AND hou2.organization_id = hou.organization_id
699 AND hou2.organization_id = hoi2.organization_id
700 AND hoi2.org_information1 IS NOT NULL
701 AND hoi2.org_information2 IS NOT NULL
702 AND hou2.organization_id IN
703 (
704 SELECT pose.organization_id_parent
705 FROM per_org_structure_elements pose
706 ,per_org_structure_versions posv
707 WHERE posv.org_structure_version_id = pose.org_structure_version_id
708 AND posv.organization_structure_id = c_org_struct_id
709 AND posv.date_from <= c_start_date
710 AND nvl (posv.date_to
711 ,hr_general.end_of_time) >= c_end_date
712 UNION
713 SELECT pose.organization_id_child
714 FROM per_org_structure_elements pose
715 ,per_org_structure_versions posv
716 WHERE posv.org_structure_version_id = pose.org_structure_version_id
717 AND posv.organization_structure_id = c_org_struct_id
718 AND posv.date_from <= c_start_date
719 AND nvl (posv.date_to
720 ,hr_general.end_of_time) >= c_end_date
721 )
722 );
723 /**************************************************************************/
724
725 l_return_val NUMBER := 0;
726 l_start_date DATE;
727 l_end_date DATE;
728
729 BEGIN
730 --hr_utility.trace('+====org_check=============================================+');
731 IF p_org_struct_id is NULL THEN
732 BEGIN
733 OPEN csr_org_struct_check (p_bg_id,p_org_id);
734 FETCH csr_org_struct_check INTO l_return_val;
735 CLOSE csr_org_struct_check;
736
737 EXCEPTION
738 WHEN TOO_MANY_ROWS THEN
739 l_return_val := 1;
740 WHEN NO_DATA_FOUND THEN
741 NULL;
742 WHEN OTHERS THEN
743 hr_utility.trace('Exception in org_check org_struct. SQLERRM : '||SQLERRM);
744 END;
745 hr_utility.trace('l_return_val : '||l_return_val);
746 ELSE
747 BEGIN
748 OPEN csr_org_check (p_bg_id,p_org_id,p_org_struct_id,p_start_date,p_end_date);
749 FETCH csr_org_check INTO l_return_val;
750 CLOSE csr_org_check;
751
752 EXCEPTION
753 WHEN TOO_MANY_ROWS THEN
754 l_return_val := 1;
755 WHEN NO_DATA_FOUND THEN
756 NULL;
757 WHEN OTHERS THEN
758 hr_utility.trace('Exception in org_check org_struct1. SQLERRM : '||SQLERRM);
759 END;
760 hr_utility.trace('l_return_val : '||l_return_val);
761 END IF;
762 RETURN l_return_val;
763 --hr_utility.trace('X====org_check=============================================X');
764 EXCEPTION
765 WHEN OTHERS THEN
766 hr_utility.trace('Exception in org_check SQL-ERRM : '||SQLERRM);
767 END org_check;
768 /**************************************************************************/
769
770 /*--------------------------------------------------------------------
771 |Name : RANGE_CODE |
772 |Type : Procedure |
773 |Description: This procedure returns an sql string to select a range |
774 | of assignments eligible for reporting |
775 ----------------------------------------------------------------------*/
776
777
778 PROCEDURE range_code (pactid IN NUMBER
779 ,sqlstr OUT NOCOPY VARCHAR2) is
780 BEGIN
781 hr_utility.trace('+====range_code============================================+');
782 sqlstr := 'SELECT DISTINCT person_id
783 FROM per_all_people_f pap
784 ,pay_payroll_actions ppa
785 WHERE ppa.payroll_action_id = :payroll_action_id
786 AND ppa.business_group_id = pap.business_group_id
787 ORDER BY pap.person_id';
788 hr_utility.trace('X====range_code============================================X');
789 EXCEPTION
790 WHEN OTHERS THEN
791 -- Return cursor that selects no rows
792 hr_utility.trace('Exception in range_code SQL-ERRM : '||SQLERRM);
793 sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
794 END range_code;
795
796 /**************************************************************************/
797
798 PROCEDURE initialization_code(p_action_context_id IN NUMBER) IS
799
800 CURSOR c_usr_tab_col IS
801 SELECT put.user_table_id
802 ,puc.user_column_id
803 FROM pay_user_tables put
804 ,pay_user_columns puc
805 WHERE put.user_table_id = puc.user_table_id
806 AND put.legislation_code = puc.legislation_code
807 AND put.user_table_name = 'NL_DIACRITICAL_MARKS'
808 AND put.legislation_code = 'NL';
809 /**************************************************************************/
810 CURSOR c_src_dest_str(p_user_column_id IN NUMBER
811 , p_user_table_id IN NUMBER
812 , p_business_group_id IN NUMBER
813 , p_start_date IN DATE) IS
814 SELECT DISTINCT
815 UPPER (purf.row_low_range_or_name) Source
816 ,UPPER (pucif.value) Target
817 FROM pay_user_column_instances_f pucif
818 ,pay_user_rows_f purf
819 WHERE pucif.user_column_id = p_user_column_id
820 AND purf.user_table_id = p_user_table_id
821 AND pucif.user_row_id = purf.user_row_id
822 AND pucif.business_group_id = purf.business_group_id
823 AND pucif.business_group_id = p_business_group_id
824 AND p_start_date BETWEEN pucif.EFFECTIVE_START_DATE AND pucif.EFFECTIVE_END_DATE
825 AND p_start_date BETWEEN purf.EFFECTIVE_START_DATE AND purf.EFFECTIVE_END_DATE;
826 /**************************************************************************/
827 CURSOR c_bg_stdate(p_payroll_action_id IN NUMBER) IS
828 SELECT business_group_id bg_id
829 ,start_date start_date
830 FROM pay_payroll_actions
831 WHERE payroll_action_id = p_payroll_action_id;
832 /**************************************************************************/
833 l_user_table_id NUMBER;
834 l_user_column_id NUMBER;
835 l_bg_id NUMBER;
836 l_start_date DATE;
837
838 BEGIN
839 hr_utility.trace('+====initialization_code===================================+');
840 IF INSTR ('a', COMPOSE('a'|| UNISTR('\0301')) ) = 0 THEN
841 OPEN c_bg_stdate(p_action_context_id);
842 FETCH c_bg_stdate INTO l_bg_id,l_start_date;
843 CLOSE c_bg_stdate;
844
845 OPEN c_usr_tab_col;
846 FETCH c_usr_tab_col INTO l_user_table_id, l_user_column_id;
847 CLOSE c_usr_tab_col;
848
849 FOR I in c_src_dest_str(l_user_column_id, l_user_table_id, l_bg_id, l_start_date)
850 LOOP
851 g_SOURCE := g_SOURCE||upper(I.Source)||lower(I.Source);
852 g_TARGET := g_TARGET||upper(I.Target)||lower(I.Target);
853 END LOOP;
854
855 hr_utility.trace('g_SOURCE : '||g_SOURCE);
856 hr_utility.trace('g_TARGET : '||g_TARGET);
857 END IF;
858 hr_utility.trace('X====initialization_code===================================X');
859 EXCEPTION
860 WHEN OTHERS THEN
861 hr_utility.trace('Exception in init_code SQL-ERRM : '||SQLERRM);
862 END initialization_code;
863 /**************************************************************************/
864 /*--------------------------------------------------------------------
865 |Name : ASSIGNMENT_ACTION_CODE |
866 |Type : Procedure |
867 |Description: This procedure further filters which assignments are |
868 | eligible for reporting |
869 ----------------------------------------------------------------------*/
870
871
872 PROCEDURE assignment_action_code (p_payroll_action_id IN NUMBER
873 ,p_start_person_id IN NUMBER
874 ,p_end_person_id IN NUMBER
875 ,p_chunk IN NUMBER) IS
876
877 CURSOR csr_get_asg_person (p_person_id IN NUMBER
878 ,p_start_person_id IN NUMBER
879 ,p_end_person_id IN NUMBER
880 ,p_payroll_action_id IN NUMBER
881 ,p_start_date IN DATE
882 ,p_end_date IN DATE) is
883 SELECT DISTINCT
884 paa.assignment_id assignment_id
885 FROM per_absence_attendance_types paat
886 ,per_absence_attendances pab
887 ,pay_payroll_actions ppa
888 ,per_all_assignments_f paa
889 ,per_all_people_f pap
890 WHERE paat.absence_category = 'S'
891 AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
892 AND paat.business_group_id = pab.business_group_id
893 AND pab.date_start BETWEEN p_start_date
894 AND p_end_date
895 AND pab.business_group_id = pap.business_group_id
896 AND pap.person_id = p_person_id
897 AND p_person_id BETWEEN p_start_person_id
898 AND p_end_person_id
899 AND pap.person_id = paa.person_id
900 AND pab.person_id = pap.person_id
901 AND paa.business_group_id = pap.business_group_id
902 AND paa.assignment_id = pab.abs_information5
903 AND pab.abs_information_category = 'NL_S'
904 AND pab.abs_information4 = 'S'
905 AND pap.business_group_id = ppa.business_group_id
906 AND ppa.payroll_action_id = p_payroll_action_id;
907 /**************************************************************************/
908
909 CURSOR csr_get_asg_org (p_org_id IN NUMBER
910 ,p_org_struct_id IN NUMBER
911 ,p_start_person_id IN NUMBER
912 ,p_end_person_id IN NUMBER
913 ,p_payroll_action_id IN NUMBER
914 ,p_start_date IN DATE
915 ,p_end_date IN DATE) IS
916 SELECT DISTINCT
917 paa.assignment_id assignment_id
918 FROM per_absence_attendance_types paat
919 ,per_absence_attendances pab
920 ,pay_payroll_actions ppa
921 ,per_org_structure_versions posv
922 ,per_all_assignments_f paa
923 ,per_all_people_f pap
924 WHERE posv.organization_structure_id = p_org_struct_id
925 AND posv.date_from <= p_end_date
926 AND nvl (posv.date_to,hr_general.end_of_time) >= p_start_date
927 AND (
928 paa.organization_id IN
929 (
930 (
931 SELECT pose.organization_id_child
932 FROM per_org_structure_elements pose
933 WHERE pose.org_structure_version_id = posv.org_structure_version_id
934 START WITH pose.organization_id_parent = p_org_id
935 CONNECT BY PRIOR organization_id_child = organization_id_parent
936 )
937 UNION
938 (
939 SELECT p_org_id
940 FROM dual
941 )
942 )
943 OR nvl (paa.establishment_id,- 1) = p_org_id
944 )
945 AND pap.person_id = paa.person_id
946 AND paa.person_id BETWEEN p_start_person_id
947 AND p_end_person_id
948 AND paa.business_group_id = pap.business_group_id
949 AND pab.person_id = pap.person_id
950 AND pab.date_start BETWEEN p_start_date
951 AND p_end_date
952 AND paat.absence_category = 'S'
953 AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
954 AND paat.business_group_id = pab.business_group_id
955 AND pab.business_group_id = pap.business_group_id
956 AND paa.assignment_id = pab.abs_information5
957 AND pab.abs_information_category = 'NL_S'
958 AND pab.abs_information4 = 'S'
959 AND pap.business_group_id = ppa.business_group_id
960 AND ppa.payroll_action_id = p_payroll_action_id;
961 /**************************************************************************/
962
963 CURSOR csr_get_asg_hier (p_org_struct_id IN NUMBER
964 ,p_start_person_id IN NUMBER
965 ,p_end_person_id IN NUMBER
966 ,p_payroll_action_id IN NUMBER
967 ,p_start_date IN DATE
968 ,p_end_date IN DATE) IS
969
970 SELECT DISTINCT
971 paa.assignment_id assignment_id
972 FROM per_absence_attendance_types paat
973 ,per_absence_attendances pab
974 ,per_all_assignments_f paa
975 ,per_all_people_f pap
976 ,pay_payroll_actions ppa
977 ,per_org_structure_versions posv
978 WHERE posv.organization_structure_id = p_org_struct_id
979 AND pap.person_id BETWEEN p_start_person_id
980 AND p_end_person_id
981 AND pap.business_group_id = ppa.business_group_id
982 AND ppa.payroll_action_id = p_payroll_action_id
983 AND paat.absence_category = 'S'
984 AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
985 AND paat.business_group_id = pab.business_group_id
986 AND pab.date_start BETWEEN p_start_date
987 AND p_end_date
988 AND paa.assignment_id = pab.abs_information5
989 AND pab.abs_information_category = 'NL_S'
990 AND pab.abs_information4 = 'S'
991 AND pab.business_group_id = pap.business_group_id
992 AND pab.person_id = pap.person_id
993 AND pap.person_id = paa.person_id
994 AND (
995 hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
996 ,paa.organization_id) IS NOT NULL
997 OR per_nl_dsr_archive.org_check (pap.business_group_id
998 ,NULL
999 ,nvl (paa.establishment_id,- 1)
1000 ,p_start_date
1001 ,p_end_date) = 1
1002 )
1003 AND paa.organization_id IN
1004 (
1005 SELECT pose.organization_id_parent
1006 FROM per_org_structure_elements pose
1007 WHERE posv.org_structure_version_id = pose.org_structure_version_id
1008 UNION
1009 SELECT pose.organization_id_child
1010 FROM per_org_structure_elements pose
1011 WHERE posv.org_structure_version_id = pose.org_structure_version_id
1012 );
1013 /**************************************************************************/
1014 --Withdrawal and Regeneration
1015 CURSOR csr_get_asg_person_archive (p_person_id IN NUMBER
1016 ,p_start_person_id IN NUMBER
1017 ,p_end_person_id IN NUMBER
1018 ,p_payroll_action_id IN NUMBER
1019 ,p_start_date IN DATE
1020 ,p_end_date IN DATE
1021 ,p_type IN VARCHAR2) IS
1022 SELECT DISTINCT
1023 paa.assignment_id assignment_id
1024 FROM pay_action_information pai
1025 ,pay_payroll_actions ppa
1026 ,per_all_assignments_f paa
1027 ,per_all_people_f pap
1028 WHERE pai.action_context_type = 'AAP'
1029 AND pai.action_information_category = 'NL_DSR_ABS_INFO'
1030 AND pai.action_information4 = 'SR'
1031 AND pai.action_information29 = 'NL_SR'
1032 AND (
1033 (
1034 pai.action_information30 = 'N'
1035 AND p_type = 'W'
1036 )
1037 OR (
1038 p_type = 'R'
1039 )
1040 )
1041 AND (
1042 fnd_date.canonical_to_date(pai.action_information2) BETWEEN p_start_date
1043 AND p_end_date
1044 )
1045 AND paa.assignment_id = pai.assignment_id
1046 AND pap.person_id = p_person_id
1047 AND p_person_id BETWEEN p_start_person_id
1048 AND p_end_person_id
1049 AND pap.person_id = paa.person_id
1050 AND paa.business_group_id = pap.business_group_id
1051 AND pap.business_group_id = ppa.business_group_id
1052 AND ppa.payroll_action_id = p_payroll_action_id;
1053
1054 /**************************************************************************/
1055 CURSOR csr_get_asg_org_archive (p_org_id IN NUMBER
1056 ,p_org_struct_id IN NUMBER
1057 ,p_start_person_id IN NUMBER
1058 ,p_end_person_id IN NUMBER
1059 ,p_payroll_action_id IN NUMBER
1060 ,p_start_date IN DATE
1061 ,p_end_date IN DATE
1062 ,p_type IN VARCHAR2) IS
1063 SELECT DISTINCT
1064 paa.assignment_id assignment_id
1065 FROM pay_action_information pai
1066 ,pay_payroll_actions ppa
1067 ,per_org_structure_versions posv
1068 ,per_all_assignments_f paa
1069 ,per_all_people_f pap
1070 WHERE posv.organization_structure_id = p_org_struct_id
1071 AND posv.date_from <= p_end_date
1072 AND nvl (posv.date_to
1073 ,hr_general.end_of_time) >= p_start_date
1074 AND (
1075 paa.organization_id IN
1076 (
1077 (
1078 SELECT pose.organization_id_child
1079 FROM per_org_structure_elements pose
1080 WHERE pose.org_structure_version_id = posv.org_structure_version_id
1081 START WITH pose.organization_id_parent = p_org_id
1082 CONNECT BY PRIOR organization_id_child = organization_id_parent
1083 )
1084 UNION
1085 (
1086 SELECT p_org_id
1087 FROM dual
1088 )
1089 )
1090 OR nvl (paa.establishment_id,- 1) = p_org_id
1091 )
1092 AND pap.person_id = paa.person_id
1093 AND paa.person_id BETWEEN p_start_person_id
1094 AND p_end_person_id
1095 AND paa.business_group_id = pap.business_group_id
1096 AND pai.action_context_type = 'AAP'
1097 AND pai.action_information_category = 'NL_DSR_ABS_INFO'
1098 AND pai.action_information4 = 'SR'
1099 AND pai.action_information29 = 'NL_SR'
1100 AND (
1101 (
1102 pai.action_information30 = 'N'
1103 AND p_type = 'W'
1104 )
1105 OR (
1106 p_type = 'R'
1107 )
1108 )
1109 AND (
1110 fnd_date.canonical_to_date(pai.action_information2) BETWEEN p_start_date
1111 AND p_end_date
1112 )
1113 AND paa.assignment_id = pai.assignment_id
1114 AND pap.business_group_id = ppa.business_group_id
1115 AND ppa.payroll_action_id = p_payroll_action_id;
1116 /**************************************************************************/
1117 CURSOR csr_get_asg_hier_archive (p_org_struct_id IN NUMBER
1118 ,p_start_person_id IN NUMBER
1119 ,p_end_person_id IN NUMBER
1120 ,p_payroll_action_id IN NUMBER
1121 ,p_start_date IN DATE
1122 ,p_end_date IN DATE
1123 ,p_type IN VARCHAR2) IS
1124 SELECT DISTINCT
1125 paa.assignment_id assignment_id
1126 FROM pay_action_information pai
1127 ,per_all_assignments_f paa
1128 ,per_all_people_f pap
1129 ,pay_payroll_actions ppa
1130 ,per_org_structure_versions posv
1131 WHERE posv.organization_structure_id = p_org_struct_id
1132 AND pap.person_id BETWEEN p_start_person_id
1133 AND p_end_person_id
1134 AND pap.business_group_id = ppa.business_group_id
1135 AND ppa.payroll_action_id = p_payroll_action_id
1136 AND pai.action_context_type = 'AAP'
1137 AND pai.action_information_category = 'NL_DSR_ABS_INFO'
1138 AND pai.action_information4 = 'SR'
1139 AND pai.action_information29 = 'NL_SR'
1140 AND (
1141 (
1142 pai.action_information30 = 'N'
1143 AND p_type = 'W'
1144 )
1145 OR (
1146 p_type = 'R'
1147 )
1148 )
1149 AND (
1150 fnd_date.canonical_to_date(pai.action_information2) BETWEEN p_start_date
1151 AND p_end_date
1152 )
1153 AND paa.assignment_id = pai.assignment_id
1154 AND ppa.payroll_action_id = p_payroll_action_id
1155 AND pap.business_group_id = ppa.business_group_id
1156 AND pap.person_id = paa.person_id
1157 AND (
1158 hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
1159 ,paa.organization_id) IS NOT NULL
1160 OR per_nl_dsr_archive.org_check (pap.business_group_id
1161 ,NULL
1162 ,nvl (paa.establishment_id,- 1)
1163 ,p_start_date
1164 ,p_end_date) = 1
1165 )
1166 AND paa.organization_id IN
1167 (
1168 SELECT pose.organization_id_parent
1169 FROM per_org_structure_elements pose
1170 WHERE posv.org_structure_version_id = pose.org_structure_version_id
1171 UNION
1172 SELECT pose.organization_id_child
1173 FROM per_org_structure_elements pose
1174 WHERE posv.org_structure_version_id = pose.org_structure_version_id
1175 );
1176 /**************************************************************************/
1177
1178 l_org_struct_id NUMBER :=NULL;
1179 l_person_id NUMBER :=NULL;
1180 l_org_id NUMBER :=NULL;
1181 l_bg_id NUMBER;
1182 l_asg_act_id NUMBER;
1183 l_start_date DATE;
1184 l_end_date DATE;
1185 l_type VARCHAR2(1) :=NULL;
1186
1187 /**************************************************************************/
1188 PROCEDURE ins_assg_act (p_assignid IN NUMBER
1189 ,p_pactid IN NUMBER
1190 ,p_chunk IN NUMBER)
1191 IS
1192 l_asg_act_id NUMBER;
1193 BEGIN
1194 SELECT pay_assignment_actions_s.NEXTVAL INTO l_asg_act_id FROM dual;
1195 hr_nonrun_asact.insact(
1196 lockingactid => l_asg_act_id
1197 ,assignid => p_assignid
1198 ,pactid => p_pactid
1199 ,chunk => p_chunk);
1200 EXCEPTION
1201 WHEN others THEN
1202 hr_utility.trace('Exception in ins_assg_act SQL-ERRM : '||SQLERRM);
1203 END ins_assg_act;
1204 /**************************************************************************/
1205
1206
1207 BEGIN
1208 hr_utility.trace('+====assignment_action_code================================+');
1209 hr_utility.trace('Payroll_action_id/Chunk: '||p_payroll_action_id||' / '||p_chunk );
1210 hr_utility.trace('Start/End Person ID : '||p_start_person_id||' / '||p_end_person_id );
1211
1212 get_all_parameters (
1213 p_payroll_action_id => p_payroll_action_id
1214 ,p_org_struct_id => l_org_struct_id
1215 ,p_person_id => l_person_id
1216 ,p_org_id => l_org_id
1217 ,p_bg_id => l_bg_id
1218 ,p_start_date => l_start_date
1219 ,p_end_date => l_end_date
1220 ,p_type => l_type);
1221
1222
1223 IF l_person_id is not NULL THEN
1224 hr_utility.trace(l_type||' Person selected ' ||l_person_id||' '||p_start_person_id||' '||p_end_person_id);
1225 IF l_type = 'I' THEN
1226 FOR v_csr_get_asg_person IN
1227 csr_get_asg_person(l_person_id, p_start_person_id, p_end_person_id
1228 , p_payroll_action_id, l_start_date, l_end_date)
1229 LOOP
1230 ins_assg_act(v_csr_get_asg_person.assignment_id,p_payroll_action_id,p_chunk);
1231 END LOOP;
1232 ELSIF l_type IN ('W','R') THEN
1233 FOR v_csr_get_asg_person IN
1234 csr_get_asg_person_archive(l_person_id, p_start_person_id, p_end_person_id
1235 , p_payroll_action_id, l_start_date, l_end_date,l_type)
1236 LOOP
1237 ins_assg_act(v_csr_get_asg_person.assignment_id,p_payroll_action_id,p_chunk);
1238 END LOOP;
1239 END IF;
1240 -----------------------------------------------
1241 ELSIF l_org_id is not NULL THEN
1242 hr_utility.trace(l_type||' Org selected ' ||l_org_id);
1243 IF l_type = 'I' THEN
1244 FOR v_csr_get_asg_org IN
1245 csr_get_asg_org(l_org_id, l_org_struct_id
1246 , p_start_person_id, p_end_person_id, p_payroll_action_id
1247 , l_start_date, l_end_date)
1248 LOOP
1249 ins_assg_act(v_csr_get_asg_org.assignment_id,p_payroll_action_id,p_chunk);
1250 END LOOP;
1251 ELSIF l_type IN ('W','R') THEN
1252 FOR v_csr_get_asg_org IN
1253 csr_get_asg_org_archive(l_org_id, l_org_struct_id
1254 , p_start_person_id, p_end_person_id, p_payroll_action_id
1255 , l_start_date, l_end_date,l_type)
1256 LOOP
1257 ins_assg_act(v_csr_get_asg_org.assignment_id,p_payroll_action_id,p_chunk);
1258 END LOOP;
1259 END IF;
1260 -----------------------------------------------
1261 ELSIF l_org_struct_id is not NULL THEN
1262 hr_utility.trace(l_type||' Hierarchy selected ' ||l_org_struct_id);
1263 IF l_type = 'I' THEN
1264 FOR v_csr_get_asg_hier IN
1265 csr_get_asg_hier(l_org_struct_id, p_start_person_id, p_end_person_id
1266 , p_payroll_action_id, l_start_date, l_end_date)
1267 LOOP
1268 ins_assg_act(v_csr_get_asg_hier.assignment_id,p_payroll_action_id,p_chunk);
1269 END LOOP;
1270 ELSIF l_type IN ('W','R') THEN
1271 FOR v_csr_get_asg_hier IN
1272 csr_get_asg_hier_archive(l_org_struct_id, p_start_person_id, p_end_person_id
1273 , p_payroll_action_id, l_start_date, l_end_date,l_type)
1274 LOOP
1275 ins_assg_act(v_csr_get_asg_hier.assignment_id,p_payroll_action_id,p_chunk);
1276 END LOOP;
1277 END IF;
1278 END IF;
1279 hr_utility.trace('X====assignment_action_code================================X');
1280 EXCEPTION
1281 WHEN others THEN
1282 hr_utility.trace('Exception in assignment_action_code SQL-ERRM : '||SQLERRM);
1283 END assignment_action_code;
1284 /**************************************************************************/
1285
1286 /*-------------------------------------------------------------------------------
1287 |Name : archive_code |
1288 |Type : Procedure |
1289 |Description : Archival code |
1290 -------------------------------------------------------------------------------*/
1291
1292
1293 PROCEDURE archive_code (p_assignment_action_id IN NUMBER
1294 ,p_effective_date IN DATE) IS
1295
1296 /**************************************************************************/
1297 CURSOR csr_get_org_info(p_assignment_id IN NUMBER
1298 , p_org_struct_id IN NUMBER
1299 , p_abs_start_date IN DATE) IS
1300 SELECT hou.organization_id org_id
1301 ,hou.name org_name
1302 ,hoi.org_information4 tax_reg
1303 FROM per_all_assignments_f pas
1304 ,hr_organization_units hou
1305 ,hr_organization_information hoi
1306 ,per_org_structure_versions posv
1307 WHERE posv.organization_structure_id = p_org_struct_id
1308 AND p_abs_start_date BETWEEN posv.date_from
1309 AND nvl (posv.date_to
1310 ,hr_general.end_of_time)
1311 AND pas.assignment_id = p_assignment_id
1312 AND hou.organization_id = hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
1313 ,pas.organization_id)
1314 AND hoi.organization_id = hou.organization_id
1315 AND hoi.org_information_context = 'NL_ORG_INFORMATION';
1316 /**************************************************************************/
1317 CURSOR csr_get_leg_info(p_assignment_id IN NUMBER) IS
1318 SELECT hou.organization_id org_id
1319 ,hou.name org_name
1320 ,hoi.org_information1 tax_reg
1321 FROM per_all_assignments_f pas
1322 ,hr_organization_units hou
1323 ,hr_organization_information hoi
1324 WHERE pas.assignment_id = p_assignment_id
1325 AND hou.organization_id = pas.establishment_id
1326 AND hoi.organization_id = hou.organization_id
1327 AND hoi.org_information_context = 'NL_LE_TAX_DETAILS';
1328 /**************************************************************************/
1329 CURSOR csr_get_abs_details (p_assignment_action_id IN NUMBER
1330 , p_start_date IN DATE
1331 , p_end_date IN DATE) IS
1332 SELECT paa.assignment_id assignment_id
1333 ,pas.person_id person_id
1334 ,pab.absence_attendance_type_id abs_att_type_id
1335 ,pab.business_group_id busi_group_id
1336 ,pab.absence_attendance_id abs_att_id
1337 ,pab.date_end end_date
1338 ,nvl(fnd_date.canonical_to_date(pab.abs_information9),pab.date_start) start_date
1339 ,pab.abs_information4 report_type
1340 ,pab.abs_information5 ass_id
1341 ,pab.abs_information6 sick_ref
1342 ,to_char (pab.date_notification,'YYYYMMDD') noti_date
1343 ,to_char (nvl(fnd_date.canonical_to_date(pab.abs_information9),pab.date_start),'YYYYMMDD') dsr_st_dt
1344 ,decode (pab.abs_information7,'Y','1','N','2') code_pymt
1345 ,pab.abs_information8 reason
1346 ,to_char (pab.abs_information9,'YYYYMMDD') overall_start_date
1347 ,decode (pab.abs_information10,'Y','1','N','2') work_sat
1348 ,decode (pab.abs_information11,'Y','1','N','2') work_sun
1349 ,pab.abs_information12 si_first
1350 ,pab.abs_information13 daily_si
1351 ,pab.abs_information14 amt_wsw
1352 ,pab.abs_information15 acc_reason
1353 ,pab.abs_information16 reason_late
1354 ,pab.abs_information17 avg_hr
1355 ,pab.abs_information18 partial_rec
1356 ,to_char (fnd_date.canonical_to_date (pab.abs_information19)
1357 ,'YYYYMMDD') dt_ter_notice
1358 ,pab.abs_information20 term_init
1359 ,decode (pab.abs_information21,'Y','1','N','2') imme_term
1360 ,pab.abs_information22 no_of_calls
1361 ,decode (pab.abs_information23,'Y','1','N','2') period_less_three
1362 ,pab.abs_information24 no_of_first_call
1363 ,decode (pab.abs_information25,'Y','1','N','2') work_long_all
1364 ,pab.abs_information26 temp_cont
1365 FROM per_absence_attendance_types paat
1366 ,per_absence_attendances pab
1367 ,pay_assignment_actions paa
1368 ,per_all_assignments_f pas
1369 WHERE paat.absence_category = 'S'
1370 AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
1371 AND paat.business_group_id = pab.business_group_id
1372 AND pab.date_start BETWEEN p_start_date
1373 AND p_end_date
1374 AND pab.date_start BETWEEN pas.effective_start_date
1375 AND pas.effective_end_date
1376 AND pab.business_group_id = pas.business_group_id
1377 AND paa.assignment_action_id = p_assignment_action_id
1378 AND pas.assignment_id = paa.assignment_id
1379 AND pab.abs_information5 = pas.assignment_id
1380 AND pab.person_id = pas.person_id
1381 AND pab.abs_information_category = 'NL_S'
1382 AND pab.abs_information4 = 'S';
1383 /**************************************************************************/
1384
1385 CURSOR csr_already_exists(p_assignment_id IN NUMBER, p_abs_start_date IN DATE) IS
1386 SELECT 'Y'
1387 FROM pay_action_information pai
1388 WHERE pai.action_context_type = 'AAP'
1389 AND pai.action_information_category = 'NL_DSR_ABS_INFO'
1390 AND pai.ASSIGNMENT_ID = p_assignment_id
1391 AND fnd_date.canonical_to_date(pai.action_information2) = p_abs_start_date
1392 AND pai.action_information30 = 'N';
1393 /**************************************************************************/
1394
1395 CURSOR get_archived_absence(p_assignment_id IN NUMBER
1396 , p_start_date IN DATE
1397 , p_end_date IN DATE
1398 , p_type IN VARCHAR2) IS
1399 SELECT pai.action_information1 abs_att_id
1400 ,pai.action_information2 st_dt
1401 ,pai.action_information3 end_dt
1402 ,pai.action_context_id org_asg_act_id
1403 ,pai.action_information_id act_info_id
1404 FROM pay_action_information pai
1405 WHERE pai.assignment_id = p_assignment_id
1406 AND pai.action_context_type = 'AAP'
1407 AND pai.action_information_category = 'NL_DSR_ABS_INFO'
1408 AND pai.action_information29 = 'NL_SR'
1409 AND fnd_date.canonical_to_date (pai.action_information2) BETWEEN p_start_date
1410 AND p_end_date
1411 AND (
1412 (
1413 pai.action_information30 = 'N'
1414 AND p_type = 'W'
1415 )
1416 OR (
1417 p_type = 'R'
1418 )
1419 );
1420 /**************************************************************************/
1421
1422 /* sender and contact data */
1423 CURSOR csr_get_org_contact_info(p_assignment_id IN NUMBER
1424 , p_start_date IN DATE) IS
1425 SELECT decode (hoi.org_information1,'Y','1','N','2') test_msg
1426 ,hoi.org_information2 sector
1427 ,lpad(hoi.org_information3, 2, 0) risk_group
1428 ,hoi.org_information4 role_sender
1429 ,hoi.org_information5 tax_no_sender
1430 ,hoi.org_information6 contact_per_or_dept
1431 ,hoi.org_information7 est_name
1432 ,hoi.org_information8 gender_contact
1433 ,hoi.org_information9 ph_no_contact
1434 FROM per_all_assignments_f pas
1435 ,hr_organization_units hou
1436 ,hr_organization_information hoi
1437 WHERE pas.assignment_id = p_assignment_id
1438 AND p_start_date BETWEEN pas.effective_start_date
1439 AND pas.effective_end_date
1440 AND hou.organization_id = nvl (pas.establishment_id
1441 ,pas.organization_id)
1442 AND hoi.organization_id = hou.organization_id
1443 AND hoi.org_information_context = 'NL_ORG_DSR_INFO';
1444 /**************************************************************************/
1445 /* employer payroll data */
1446 CURSOR csr_get_employer_bank_info (p_assignment_id IN NUMBER
1447 , p_start_date IN DATE) IS
1448 SELECT upper(pea.segment2) acc_no
1449 ,pea.segment10 iban
1450 FROM per_all_assignments_f pas
1451 ,pay_payrolls_f pp
1452 ,pay_org_payment_methods_f popm
1453 ,pay_external_accounts pea
1454 WHERE pas.assignment_id = p_assignment_id
1455 AND pas.payroll_id = pp.payroll_id
1456 AND p_start_date BETWEEN pp.effective_start_date
1457 AND pp.effective_end_date
1458 AND pp.default_payment_method_id = popm.org_payment_method_id
1459 AND p_start_date BETWEEN popm.effective_start_date
1460 AND popm.effective_end_date
1461 AND popm.external_account_id = pea.external_account_id
1462 AND popm.business_group_id = pas.business_group_id;
1463 /**************************************************************************/
1464 /* employee data */
1465 CURSOR csr_get_person_info(p_assignment_id IN NUMBER
1466 , p_start_date IN DATE
1467 , p_end_date IN DATE) IS
1468 SELECT pap.national_identifier sofi_number
1469 ,to_char (pap.date_of_birth,'YYYYMMDD') date_of_birth
1470 ,decode (pap.sex,'M','1','F','2',NULL,'9') gender
1471 ,REPLACE(REPLACE(pap.per_information1, '.', ''), ' ', '') init
1472 ,pap.pre_name_adjunct prefix
1473 ,pap.last_name last_name
1474 ,pap.employee_number employee_number
1475 ,pap.person_id person_id
1476 ,pap.business_group_id bg_id
1477 ,pas.establishment_id establishment_id
1478 FROM per_all_people_f pap
1479 ,per_all_assignments_f pas
1480 WHERE pas.assignment_id = p_assignment_id
1481 AND p_start_date BETWEEN pas.effective_start_date
1482 AND pas.effective_end_date
1483 AND pap.person_id = pas.person_id
1484 AND p_start_date BETWEEN pap.effective_start_date
1485 AND pap.effective_end_date;
1486 /**************************************************************************/
1487 /* Job details */
1488 CURSOR csr_get_job(p_assignment_id IN NUMBER
1489 , p_start_date IN DATE ) IS
1490 SELECT pj.name
1491 FROM per_all_assignments_f pas
1492 ,per_jobs pj
1493 WHERE pas.assignment_id = p_assignment_id
1494 AND p_start_date BETWEEN pas.effective_start_date
1495 AND pas.effective_end_date
1496 AND pas.job_id = pj.job_id
1497 AND pas.business_group_id = pj.business_group_id;
1498 /**************************************************************************/
1499 /* Employee payroll data */
1500 CURSOR csr_get_employee_bank_info (p_assignment_id IN NUMBER
1501 , p_start_date IN DATE) IS
1502 SELECT upper(pea.segment2) acc_no
1503 ,pea.segment9 bic
1504 ,pea.segment10 iban
1505 FROM pay_personal_payment_methods_f pppm
1506 ,pay_external_accounts pea
1507 WHERE pppm.assignment_id = p_assignment_id
1508 AND p_start_date BETWEEN pppm.effective_start_date
1509 AND pppm.effective_end_date
1510 AND pppm.external_account_id = pea.external_account_id
1511 ORDER BY pppm.priority;
1512 /**************************************************************************/
1513 /* CAO data */
1514 CURSOR csr_get_cao_info(p_assignment_id IN NUMBER
1515 , p_start_date IN DATE) IS
1516 SELECT pca.cag_information1 cao_code
1517 ,pca.cag_information2 no_of_waiting
1518 ,pca.cag_information3 percent_pay
1519 ,pca.cag_information4 contract_type
1520 ,decode(pca.cag_information5, 'Y', '1', 'N', '2') temp_worker
1521 ,decode(pca.cag_information6, 'Y', '1', 'N', '2') temp_to_contr
1522 ,decode(pca.cag_information7, 'Y', '1', 'N', '2') termination
1523 ,decode(pca.cag_information8, 'Y', '1', 'N', '2') appr_temp_worker
1524 FROM per_all_assignments_f pas
1525 ,per_collective_agreements pca
1526 WHERE pas.assignment_id = p_assignment_id
1527 AND p_start_date BETWEEN pas.effective_start_date
1528 AND pas.effective_end_date
1529 AND pca.collective_agreement_id = pas.collective_agreement_id
1530 AND pca.cag_information_category = 'NL';
1531 /**************************************************************************/
1532 /* Assignment details */
1533 CURSOR csr_get_assignment_details (p_assignment_id IN NUMBER
1534 , p_start_date IN DATE) IS
1535 SELECT decode (hsck.segment4, 'Y', '1', 'N', '2') wage_tax_discount
1536 ,hsck.segment11 wage_tax_table
1537 ,pas.assignment_sequence asg_seq
1538 ,pas.employment_category employment_cat
1539 ,pas.employee_category employee_cat
1540 FROM per_all_assignments_f pas
1541 ,hr_soft_coding_keyflex hsck
1542 WHERE pas.assignment_id = p_assignment_id
1543 AND p_start_date BETWEEN pas.effective_start_date
1544 AND pas.effective_end_date
1545 AND hsck.soft_coding_keyflex_id(+) = pas.soft_coding_keyflex_id;
1546 /**************************************************************************/
1547 /* code fz*/
1548 CURSOR csr_get_fzcode (p_assignment_id IN NUMBER
1549 , p_start_date IN DATE) IS
1550 SELECT paei.aei_information3 fzcode
1551 FROM per_assignment_extra_info paei
1552 WHERE paei.assignment_id = p_assignment_id
1553 AND paei.aei_information_category = 'NL_TML'
1554 AND p_start_date BETWEEN fnd_date.canonical_to_date (paei.aei_information1)
1555 AND nvl (fnd_date.canonical_to_date (paei.aei_information2)
1556 ,hr_general.end_of_time);
1557 /**************************************************************************/
1558 /* labor relation info */
1559 CURSOR csr_get_lbr_info (p_assignment_id IN NUMBER
1560 , p_start_date IN DATE) IS
1561 SELECT to_char(fnd_date.canonical_to_date (paei.aei_information1),'YYYYMMDD') start_date
1562 ,to_char(fnd_date.canonical_to_date (paei.aei_information2),'YYYYMMDD') end_date
1563 ,paei.aei_information3 code_kind
1564 FROM per_assignment_extra_info paei
1565 WHERE paei.assignment_id = p_assignment_id
1566 AND paei.aei_information_category = 'NL_LBR'
1567 AND p_start_date BETWEEN fnd_date.canonical_to_date (paei.aei_information1)
1568 AND nvl (fnd_date.canonical_to_date (paei.aei_information2)
1569 ,hr_general.end_of_time);
1570 /**************************************************************************/
1571 /* per_phones */
1572 CURSOR csr_get_phone_no(p_assignment_id IN NUMBER
1573 , p_start_date IN DATE
1574 , p_type IN VARCHAR2) IS
1575 SELECT pp.phone_number phone_no
1576 FROM per_all_assignments_f pas
1577 ,per_phones pp
1578 WHERE pas.assignment_id = p_assignment_id
1579 AND p_start_date BETWEEN pas.effective_start_date
1580 AND pas.effective_end_date
1581 AND pas.person_id = pp.parent_id
1582 AND pp.parent_table = 'PER_ALL_PEOPLE_F'
1583 AND pp.phone_type = p_type
1584 AND p_start_date BETWEEN pp.date_from
1585 AND nvl (pp.date_to
1586 ,hr_general.end_of_time);
1587 /**************************************************************************/
1588 /* Sick Addresses */
1589 CURSOR csr_get_sick_addr(p_assignment_id IN NUMBER
1590 , p_start_date IN DATE) IS
1591 SELECT to_char (pad.date_from,'YYYYMMDD') start_date
1592 ,to_char (pad.date_to,'YYYYMMDD') end_date
1593 ,pad.style style
1594 ,pad.postal_code postal
1595 ,pad.town_or_city town
1596 ,pad.country country
1597 ,pad.address_line1 address1
1598 ,pad.address_line2 address2
1599 ,pad.telephone_number_1 tel1
1600 ,pad.telephone_number_2 tel2
1601 ,pad.region_1 reg1
1602 ,pad.region_2 reg2
1603 ,pad.add_information13 info13
1604 ,pad.add_information14 info14
1605 ,pad.add_information15 info15
1606 ,pad.add_information16 info16
1607 FROM per_all_assignments_f pas
1608 ,per_addresses pad
1609 WHERE pas.assignment_id = p_assignment_id
1610 AND p_start_date BETWEEN pas.effective_start_date
1611 AND pas.effective_end_date
1612 AND pas.person_id = pad.person_id
1613 AND pas.business_group_id = pad.business_group_id
1614 AND pad.address_type = 'NL_SICK'
1615 AND pad.date_from =
1616 (
1617 SELECT max (pad1.date_from)
1618 FROM per_addresses pad1
1619 WHERE pad1.person_id = pad.person_id
1620 AND (
1621 p_start_date BETWEEN pad1.date_from
1622 AND nvl (pad1.date_to
1623 ,hr_general.end_of_time)
1624 OR pad1.date_from > p_start_date
1625 )
1626 );
1627 /**************************************************************************/
1628 /* Person Address */
1629 CURSOR csr_get_person_addr(p_assignment_id IN NUMBER
1630 , p_start_date IN DATE) IS
1631 SELECT to_char (pad.date_from,'YYYYMMDD') start_date
1632 ,to_char (pad.date_to,'YYYYMMDD') end_date
1633 ,pad.style style
1634 ,pad.postal_code postal
1635 ,pad.town_or_city town
1636 ,pad.country country
1637 ,pad.address_line1 address1
1638 ,pad.address_line2 address2
1639 ,pad.telephone_number_1 tel1
1640 ,pad.telephone_number_2 tel2
1641 ,pad.region_1 reg1
1642 ,pad.region_2 reg2
1643 ,pad.add_information13 info13
1644 ,pad.add_information14 info14
1645 ,pad.add_information15 info15
1646 ,pad.add_information16 info16
1647 FROM per_all_assignments_f pas
1648 ,per_addresses pad
1649 WHERE pas.assignment_id = p_assignment_id
1650 AND p_start_date BETWEEN pas.effective_start_date
1651 AND pas.effective_end_date
1652 AND pad.person_id = pas.person_id
1653 AND p_start_date BETWEEN pad.date_from
1654 AND nvl (pad.date_to
1655 ,hr_general.end_of_time);
1656 /**************************************************************************/
1657 /*NL_DS_SICK_INFO - Contact Details*/
1658 CURSOR csr_get_sickness_contact (p_assignment_id IN NUMBER
1659 , p_start_date IN DATE) IS
1660 SELECT pei_information21 contact_name
1661 ,pei_information22 est_name
1662 ,pei_information23 gender
1663 ,pei_information24 contact_ph_no
1664 ,pei_information3 l29bzw
1665 FROM per_all_assignments_f pas
1666 ,per_people_extra_info pei
1667 WHERE pas.assignment_id = p_assignment_id
1668 AND p_start_date BETWEEN pas.effective_start_date
1669 AND pas.effective_end_date
1670 AND pei.person_id = pas.person_id
1671 AND pei.information_type = 'NL_DS_SICK_INFO';
1672 /**************************************************************************/
1673 CURSOR csr_get_shared_types(c_code IN VARCHAR2
1674 ,c_business_gr_id IN NUMBER
1675 ,c_lookup IN VARCHAR2) IS
1676 SELECT system_type_cd
1677 FROM per_shared_types
1678 WHERE lookup_type = c_lookup
1679 AND information1 = c_code
1680 AND (
1681 business_group_id = c_business_gr_id
1682 OR business_group_id IS NULL
1683 )
1684 ORDER BY 1;
1685 /**************************************************************************/
1686 /* connected absences*/
1687 CURSOR c_get_connected_absences(c_current_absence IN NUMBER) IS
1688 SELECT absence_attendance_id
1689 ,date_start
1690 ,date_end
1691 FROM (
1692 SELECT absence_attendance_id
1693 ,nvl(fnd_date.canonical_to_date(abs_information9),date_start) date_start
1694 ,date_end
1695 FROM per_absence_attendances
1696 START WITH absence_attendance_id = c_current_absence
1697 CONNECT BY PRIOR abs_information27 = absence_attendance_id
1698 )
1699 WHERE absence_attendance_id <> c_current_absence
1700 AND date_start IS NOT NULL
1701 AND date_end IS NOT NULL
1702 AND date_end <
1703 (
1704 SELECT nvl(fnd_date.canonical_to_date(abs_information9),date_start) date_start
1705 FROM per_absence_attendances
1706 WHERE absence_attendance_id = c_current_absence
1707 );
1708 /**************************************************************************/
1709 CURSOR c_get_pact_chunk(c_assignment_action_id IN NUMBER ) IS
1710 SELECT ppa.payroll_action_id
1711 ,paa.chunk_number
1712 FROM pay_payroll_actions ppa
1713 ,pay_assignment_actions paa
1714 WHERE paa.payroll_action_id = ppa.payroll_action_id
1715 AND paa.assignment_action_id = c_assignment_action_id;
1716 /**************************************************************************/
1717 CURSOR c_get_assid_chunk(c_assignment_action_id IN NUMBER ) IS
1718 SELECT paa.assignment_id
1719 ,paa.chunk_number
1720 FROM pay_assignment_actions paa
1721 ,pay_payroll_actions ppa
1722 WHERE paa.payroll_action_id = ppa.payroll_action_id
1723 AND paa.assignment_action_id = c_assignment_action_id;
1724 /**************************************************************************/
1725 CURSOR csr_get_assignment_dates(p_assignment_id IN NUMBER) IS
1726 SELECT to_char(min (pas.effective_start_date),'YYYYMMDD') ass_start_date
1727 ,to_char(decode (max (pas.effective_end_date)
1728 ,hr_general.end_of_time
1729 ,to_date(NULL),max (pas.effective_end_date)),'YYYYMMDD') ass_end_date
1730 FROM per_all_assignments_f pas
1731 WHERE pas.assignment_id = p_assignment_id;
1732 /**************************************************************************/
1733 CURSOR csr_numiv_override(p_assignment_id IN NUMBER) IS
1734 SELECT paei.aei_information1 numiv_override
1735 FROM per_assignment_extra_info paei
1736 WHERE paei.assignment_id = p_assignment_id
1737 AND paei.aei_information_category = 'NL_NUMIV_OVERRIDE';
1738 /**************************************************************************/
1739
1740 TYPE tDateTable IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
1741 connected_absences tDateTable;
1742 connected_absence_count NUMBER :=0;
1743 table_index NUMBER :=0;
1744 l_org_struct_id NUMBER :=NULL;
1745 l_person_id NUMBER :=NULL;
1746 l_org_id NUMBER :=NULL;
1747 l_bg_id NUMBER;
1748 l_child_aa_id NUMBER;
1749 l_start_date DATE :=NULL;
1750 l_end_date DATE :=NULL;
1751 l_type VARCHAR2(1) :=NULL;
1752 l_abs_attendence_id NUMBER;
1753 l_payroll_action_id NUMBER;
1754 l_est_id NUMBER :=NULL;
1755 l_tax_reg_num VARCHAR2(100);
1756 l_org_name VARCHAR2(150);
1757 l_abs_start_date DATE :=NULL;
1758 l_abs_end_date DATE :=NULL;
1759 l_assignment_id NUMBER :=NULL;
1760 l_sick_ph_no per_phones.phone_number%TYPE;
1761 l_for_sick_ph_no per_phones.phone_number%TYPE;
1762 l_sick_mobile_no per_phones.phone_number%TYPE;
1763 l_job_name per_jobs.name%TYPE;
1764 l_fz_code per_assignment_extra_info.aei_information3%TYPE;
1765 l_contract per_shared_types.system_type_cd%TYPE;
1766 l_action_info_id NUMBER;
1767 l_ovn NUMBER;
1768 l_exists VARCHAR2(1);
1769 l_chunk_number NUMBER;
1770 l_person_addr csr_get_person_addr%ROWTYPE;
1771 l_sick_addr csr_get_sick_addr%ROWTYPE;
1772 l_sickness_contact csr_get_sickness_contact%ROWTYPE;
1773 l_assignment_details csr_get_assignment_details%ROWTYPE;
1774 l_cao_info csr_get_cao_info%ROWTYPE;
1775 l_person_info csr_get_person_info%ROWTYPE;
1776 l_org_contact_info csr_get_org_contact_info%ROWTYPE;
1777 l_employer_bank csr_get_employer_bank_info%ROWTYPE;
1778 l_employee_bank csr_get_employee_bank_info%ROWTYPE;
1779 l_lbr_info csr_get_lbr_info%ROWTYPE;
1780 l_assignment_dates csr_get_assignment_dates%ROWTYPE;
1781 l_numiv_override per_assignment_extra_info.aei_information1%TYPE;
1782
1783 /**************************************************************************/
1784 BEGIN
1785 hr_utility.trace('+====archive_code==========================================+');
1786 hr_utility.trace('p_assignment_action_id : '||p_assignment_action_id);
1787 hr_utility.trace('p_effective_date : '||p_effective_date);
1788
1789 OPEN c_get_pact_chunk (p_assignment_action_id);
1790 FETCH c_get_pact_chunk INTO l_payroll_action_id, l_chunk_number;
1791 CLOSE c_get_pact_chunk;
1792
1793 hr_utility.trace('l_payroll_action_id : '||l_payroll_action_id);
1794 hr_utility.trace('l_chunk_number : '||l_chunk_number);
1795
1796 get_all_parameters (
1797 p_payroll_action_id => l_payroll_action_id
1798 ,p_org_struct_id => l_org_struct_id
1799 ,p_person_id => l_person_id
1800 ,p_org_id => l_org_id
1801 ,p_bg_id => l_bg_id
1802 ,p_start_date => l_start_date
1803 ,p_end_date => l_end_date
1804 ,p_type => l_type);
1805
1806 IF l_type = 'I' THEN
1807 FOR v_csr_get_abs_details IN csr_get_abs_details(p_assignment_action_id, l_start_date, l_end_date)
1808 LOOP
1809 connected_absences.DELETE;
1810 connected_absence_count :=0;
1811 table_index :=0;
1812 l_exists := 'N';
1813 l_assignment_id := v_csr_get_abs_details.assignment_id;
1814 l_abs_attendence_id := v_csr_get_abs_details.abs_att_id;
1815 l_abs_start_date := v_csr_get_abs_details.start_date;
1816 l_abs_end_date := v_csr_get_abs_details.end_date;
1817 hr_utility.trace('Assignment/Attendence : ' || l_assignment_id||'/'||l_abs_attendence_id);
1818 hr_utility.trace('Absence Start/End Date : ' || to_char(l_abs_start_date,'DDMonYYYY') || ' / ' ||
1819 to_char(l_abs_end_date,'DDMonYYYY'));
1820
1821 OPEN csr_already_exists (l_assignment_id,l_abs_start_date);
1822 FETCH csr_already_exists INTO l_exists;
1823 CLOSE csr_already_exists;
1824
1825 -- hr_utility.trace('SR Exists? : '||l_exists);
1826
1827 IF l_exists = 'Y' THEN
1828 fnd_file.put_line(fnd_file.log,'Sickness Report already sent. Hence skipped.');
1829 ELSIF l_exists = 'N' THEN
1830
1831 SELECT pay_assignment_actions_s.NEXTVAL INTO l_child_aa_id FROM dual;
1832
1833 hr_nonrun_asact.insact(
1834 lockingactid => l_child_aa_id
1835 , assignid => l_assignment_id
1836 , pactid => l_payroll_action_id
1837 , chunk => l_chunk_number
1838 , status => 'C'
1839 , source_act => p_assignment_action_id);
1840
1841 OPEN csr_get_org_contact_info(l_assignment_id, l_abs_start_date );
1842 FETCH csr_get_org_contact_info into l_org_contact_info;
1843 CLOSE csr_get_org_contact_info;
1844
1845 OPEN csr_get_employer_bank_info(l_assignment_id, l_abs_start_date );
1846 FETCH csr_get_employer_bank_info into l_employer_bank;
1847 CLOSE csr_get_employer_bank_info;
1848
1849 OPEN csr_get_person_info(l_assignment_id, l_abs_start_date, l_abs_end_date);
1850 FETCH csr_get_person_info into l_person_info;
1851 CLOSE csr_get_person_info;
1852
1853 l_est_id := l_person_info.establishment_id;
1854
1855 IF l_est_id IS NOT NULL AND org_check(l_bg_id,NULL,l_est_id, l_abs_start_date, l_abs_end_date) = 1 THEN
1856 OPEN csr_get_leg_info(l_assignment_id);
1857 FETCH csr_get_leg_info INTO l_org_id, l_org_name, l_tax_reg_num;
1858 CLOSE csr_get_leg_info;
1859 ELSE
1860 OPEN csr_get_org_info(l_assignment_id, l_org_struct_id, l_abs_start_date);
1861 FETCH csr_get_org_info INTO l_org_id, l_org_name, l_tax_reg_num;
1862 CLOSE csr_get_org_info;
1863 END IF;
1864
1865 /* Archiving absence details */
1866 pay_action_information_api.create_action_information
1867 (
1868 p_action_information_id => l_action_info_id
1869 , p_action_context_id => l_child_aa_id
1870 , p_action_context_type => 'AAP'
1871 , p_object_version_number => l_ovn
1872 , p_assignment_id => l_assignment_id
1873 , p_effective_date => p_effective_date
1874 , p_source_id => NULL
1875 , p_source_text => NULL
1876 , p_action_information_category => 'NL_DSR_ABS_INFO'
1877 , p_action_information1 => l_abs_attendence_id
1878 , p_action_information2 => fnd_date.date_to_canonical(l_abs_start_date)
1879 , p_action_information3 => fnd_date.date_to_canonical(l_abs_end_date)
1880 , p_action_information4 => 'SR'
1881 , p_action_information5 => v_csr_get_abs_details.person_id
1882 , p_action_information6 => l_org_contact_info.test_msg
1883 , p_action_information7 => l_org_contact_info.tax_no_sender
1884 , p_action_information8 => l_org_contact_info.role_sender
1885 , p_action_information9 => l_org_contact_info.contact_per_or_dept
1886 , p_action_information10 => l_org_contact_info.ph_no_contact
1887 , p_action_information11 => l_org_contact_info.risk_group
1888 , p_action_information12 => l_org_contact_info.sector
1889 , p_action_information13 => l_tax_reg_num
1890 , p_action_information14 => l_employer_bank.acc_no
1891 , p_action_information15 => v_csr_get_abs_details.busi_group_id
1892 , p_action_information16 => v_csr_get_abs_details.abs_att_type_id
1893 , p_action_information17 => l_org_contact_info.est_name
1894 , p_action_information18 => l_org_contact_info.gender_contact
1895 , p_action_information19 => l_employer_bank.iban
1896 , p_action_information29 => 'NL_SR'
1897 , p_action_information30 => 'N'
1898 );
1899
1900 OPEN csr_get_sickness_contact(l_assignment_id, l_abs_start_date );
1901 FETCH csr_get_sickness_contact INTO l_sickness_contact;
1902 CLOSE csr_get_sickness_contact;
1903
1904 FOR r_get_connected_absences IN c_get_connected_absences(l_abs_attendence_id) LOOP
1905 connected_absence_count := connected_absence_count+1;
1906 table_index := table_index+1;
1907 connected_absences(table_index) := to_char(r_get_connected_absences.date_start,'YYYYMMDD');
1908 table_index := table_index+1;
1909 connected_absences(table_index) := to_char(r_get_connected_absences.date_end,'YYYYMMDD');
1910 END LOOP;
1911 FOR i in table_index+1 .. 28 LOOP
1912 connected_absences(i):=''; -- dummy initialize upto 28 segments
1913 END LOOP;
1914
1915 IF connected_absence_count>0 THEN
1916 -- Archiving connected absence details
1917 pay_action_information_api.create_action_information
1918 (
1919 p_action_information_id => l_action_info_id
1920 , p_action_context_id => l_child_aa_id
1921 , p_action_context_type => 'AAP'
1922 , p_object_version_number => l_ovn
1923 , p_assignment_id => l_assignment_id
1924 , p_effective_date => p_effective_date
1925 , p_source_id => NULL
1926 , p_source_text => NULL
1927 , p_action_information_category => 'NL_DSR_CONN_ABS_INFO'
1928 , p_action_information1 => l_abs_attendence_id
1929 , p_action_information2 => connected_absence_count
1930 , p_action_information3 => connected_absences(1)
1931 , p_action_information4 => connected_absences(2)
1932 , p_action_information5 => connected_absences(3)
1933 , p_action_information6 => connected_absences(4)
1934 , p_action_information7 => connected_absences(5)
1935 , p_action_information8 => connected_absences(6)
1936 , p_action_information9 => connected_absences(7)
1937 , p_action_information10 => connected_absences(8)
1938 , p_action_information11 => connected_absences(9)
1939 , p_action_information12 => connected_absences(10)
1940 , p_action_information13 => connected_absences(11)
1941 , p_action_information14 => connected_absences(12)
1942 , p_action_information15 => connected_absences(13)
1943 , p_action_information16 => connected_absences(14)
1944 , p_action_information17 => connected_absences(15)
1945 , p_action_information18 => connected_absences(16)
1946 , p_action_information19 => connected_absences(17)
1947 , p_action_information20 => connected_absences(18)
1948 , p_action_information21 => connected_absences(19)
1949 , p_action_information22 => connected_absences(20)
1950 , p_action_information23 => connected_absences(21)
1951 , p_action_information24 => connected_absences(22)
1952 , p_action_information25 => connected_absences(23)
1953 , p_action_information26 => connected_absences(24)
1954 , p_action_information27 => connected_absences(25)
1955 , p_action_information28 => connected_absences(26)
1956 , p_action_information29 => connected_absences(27)
1957 , p_action_information30 => connected_absences(28)
1958 );
1959 END IF;
1960
1961
1962
1963
1964 pay_action_information_api.create_action_information
1965 (
1966 p_action_information_id => l_action_info_id
1967 , p_action_context_id => l_child_aa_id
1968 , p_action_context_type => 'AAP'
1969 , p_object_version_number => l_ovn
1970 , p_assignment_id => l_assignment_id
1971 , p_effective_date => p_effective_date
1972 , p_source_id => NULL
1973 , p_source_text => NULL
1974 , p_action_information_category => 'NL_DSR_SICK_INFO'
1975 , p_action_information1 => l_abs_attendence_id
1976 , p_action_information2 => v_csr_get_abs_details.sick_ref
1977 , p_action_information3 => v_csr_get_abs_details.noti_date
1978 , p_action_information4 => v_csr_get_abs_details.dsr_st_dt
1979 , p_action_information5 => v_csr_get_abs_details.code_pymt
1980 , p_action_information6 => v_csr_get_abs_details.reason
1981 , p_action_information7 => v_csr_get_abs_details.work_sat
1982 , p_action_information8 => v_csr_get_abs_details.work_sun
1983 , p_action_information9 => v_csr_get_abs_details.si_first
1984 , p_action_information10 => v_csr_get_abs_details.daily_si
1985 , p_action_information11 => v_csr_get_abs_details.amt_wsw
1986 , p_action_information12 => v_csr_get_abs_details.acc_reason
1987 , p_action_information13 => v_csr_get_abs_details.reason_late
1988 , p_action_information14 => v_csr_get_abs_details.avg_hr
1989 , p_action_information15 => v_csr_get_abs_details.partial_rec
1990 , p_action_information16 => v_csr_get_abs_details.dt_ter_notice
1991 , p_action_information17 => v_csr_get_abs_details.term_init
1992 , p_action_information18 => v_csr_get_abs_details.imme_term
1993 , p_action_information19 => v_csr_get_abs_details.no_of_calls
1994 , p_action_information20 => v_csr_get_abs_details.period_less_three
1995 , p_action_information21 => v_csr_get_abs_details.no_of_first_call
1996 , p_action_information22 => v_csr_get_abs_details.work_long_all
1997 , p_action_information23 => v_csr_get_abs_details.temp_cont
1998 , p_action_information24 => l_sickness_contact.contact_name
1999 , p_action_information25 => l_sickness_contact.est_name
2000 , p_action_information26 => l_sickness_contact.gender
2001 , p_action_information27 => l_sickness_contact.contact_ph_no
2002 , p_action_information28 => connected_absence_count
2003 , p_action_information30 => l_sickness_contact.l29bzw
2004 );
2005
2006 OPEN csr_get_fzcode(l_assignment_id, l_abs_start_date);
2007 FETCH csr_get_fzcode INTO l_fz_code;
2008 CLOSE csr_get_fzcode;
2009
2010 OPEN csr_get_employee_bank_info(l_assignment_id, l_abs_start_date);
2011 FETCH csr_get_employee_bank_info INTO l_employee_bank;
2012 CLOSE csr_get_employee_bank_info;
2013
2014 OPEN csr_get_assignment_details(l_assignment_id, l_abs_start_date);
2015 FETCH csr_get_assignment_details INTO l_assignment_details;
2016 close csr_get_assignment_details;
2017
2018 OPEN csr_numiv_override(l_assignment_id);
2019 FETCH csr_numiv_override INTO l_numiv_override;
2020 CLOSE csr_numiv_override;
2021
2022 OPEN csr_get_assignment_dates(l_assignment_id);
2023 FETCH csr_get_assignment_dates into l_assignment_dates;
2024 CLOSE csr_get_assignment_dates;
2025
2026 OPEN csr_get_lbr_info(l_assignment_id, l_abs_start_date);
2027 FETCH csr_get_lbr_info INTO l_lbr_info;
2028 CLOSE csr_get_lbr_info;
2029
2030 OPEN csr_get_cao_info(l_assignment_id, l_abs_start_date);
2031 FETCH csr_get_cao_info INTO l_cao_info;
2032 CLOSE csr_get_cao_info;
2033
2034 IF l_cao_info.contract_type = 'NL_EMPLOYMENT_CATG' THEN
2035 OPEN csr_get_shared_types(l_assignment_details.employment_cat,l_bg_id ,l_cao_info.contract_type );
2036 FETCH csr_get_shared_types INTO l_contract;
2037 CLOSE csr_get_shared_types;
2038 ELSIF l_cao_info.contract_type = 'NL_EMPLOYEE_CATG' THEN
2039 OPEN csr_get_shared_types(l_assignment_details.employee_cat,l_bg_id ,l_cao_info.contract_type);
2040 FETCH csr_get_shared_types INTO l_contract;
2041 CLOSE csr_get_shared_types;
2042 END IF;
2043
2044 OPEN csr_get_job(l_assignment_id, l_abs_start_date);
2045 FETCH csr_get_job INTO l_job_name;
2046 CLOSE csr_get_job;
2047
2048 pay_action_information_api.create_action_information
2049 (
2050 p_action_information_id => l_action_info_id
2051 , p_action_context_id => l_child_aa_id
2052 , p_action_context_type => 'AAP'
2053 , p_object_version_number => l_ovn
2054 , p_assignment_id => l_assignment_id
2055 , p_effective_date => p_effective_date
2056 , p_source_id => NULL
2057 , p_source_text => NULL
2058 , p_action_information_category => 'NL_DSR_EMP_INFO'
2059 , p_action_information1 => l_abs_attendence_id
2060 , p_action_information2 => l_person_info.sofi_number
2061 , p_action_information3 => l_person_info.date_of_birth
2062 , p_action_information4 => l_person_info.gender
2063 , p_action_information5 => l_person_info.init
2064 , p_action_information6 => l_person_info.prefix
2065 , p_action_information7 => l_person_info.last_name
2066 , p_action_information8 => l_employee_bank.acc_no
2067 , p_action_information9 => l_employee_bank.bic
2068 , p_action_information10 => l_employee_bank.iban
2069 , p_action_information11 => l_assignment_details.wage_tax_discount
2070 , p_action_information12 => nvl(l_numiv_override,l_assignment_details.asg_seq)
2071 , p_action_information13 => l_job_name
2072 , p_action_information14 => l_fz_code
2073 , p_action_information15 => l_assignment_details.wage_tax_table
2074 , p_action_information16 => l_cao_info.no_of_waiting
2075 , p_action_information17 => l_cao_info.percent_pay
2076 , p_action_information18 => l_lbr_info.code_kind
2077 , p_action_information19 => l_lbr_info.start_date
2078 , p_action_information20 => l_lbr_info.end_date
2079 , p_action_information21 => l_contract
2080 , p_action_information22 => l_cao_info.cao_code
2081 , p_action_information23 => l_cao_info.temp_worker
2082 , p_action_information24 => l_cao_info.temp_to_contr
2083 , p_action_information25 => l_cao_info.termination
2084 , p_action_information26 => l_cao_info.appr_temp_worker
2085 , p_action_information27 => l_person_info.employee_number
2086 , p_action_information28 => l_assignment_dates.ass_start_date
2087 , p_action_information29 => l_assignment_dates.ass_end_date
2088 );
2089
2090 OPEN csr_get_phone_no(l_assignment_id, l_abs_start_date, 'NL_LS');
2091 FETCH csr_get_phone_no into l_sick_ph_no;
2092 CLOSE csr_get_phone_no;
2093
2094 OPEN csr_get_phone_no(l_assignment_id, l_abs_start_date, 'NL_FS');
2095 FETCH csr_get_phone_no into l_for_sick_ph_no;
2096 CLOSE csr_get_phone_no;
2097
2098 OPEN csr_get_phone_no(l_assignment_id, l_abs_start_date, 'NL_SM');
2099 FETCH csr_get_phone_no into l_sick_mobile_no;
2100 CLOSE csr_get_phone_no;
2101
2102 OPEN csr_get_sick_addr(l_assignment_id, l_abs_start_date);
2103 FETCH csr_get_sick_addr into l_sick_addr;
2104 CLOSE csr_get_sick_addr;
2105
2106
2107 hr_utility.trace('Sickness Address Style : ' || l_sick_addr.style );
2108 IF l_sick_addr.style IS NOT NULL THEN
2109 /* Archiving sickness address */
2110 pay_action_information_api.create_action_information
2111 ( p_action_information_id => l_action_info_id
2112 , p_action_context_id => l_child_aa_id
2113 , p_action_context_type => 'AAP'
2114 , p_object_version_number => l_ovn
2115 , p_assignment_id => l_assignment_id
2116 , p_effective_date => p_effective_date
2117 , p_source_id => NULL
2118 , p_source_text => NULL
2119 , p_action_information_category => 'NL_DSR_ADDRESS'
2120 , p_action_information1 => l_abs_attendence_id
2121 , p_action_information2 => l_sick_addr.style
2122 , p_action_information3 => l_sick_addr.start_date
2123 , p_action_information4 => l_sick_addr.end_date
2124 , p_action_information5 => REPLACE(l_sick_addr.postal,' ','')
2125 , p_action_information6 => l_sick_addr.town
2126 , p_action_information7 => l_sick_addr.country -- only for NL_GLB
2127 , p_action_information8 => l_sick_addr.tel1
2128 , p_action_information9 => l_sick_addr.tel2 -- for NL mobile, for NL_GLB forino
2129 , p_action_information10 => l_sick_addr.reg1 -- for NL st name, for NL_GLB region
2130 , p_action_information11 => l_sick_addr.reg2 -- Only NL_GLB loc des
2131 , p_action_information12 => l_sick_addr.info13 -- NL hse no
2132 , p_action_information13 => l_sick_addr.info14 -- NL hse no add,
2133 , p_action_information14 => l_sick_addr.info15 -- NL hse boat NL_GLB ISO country
2134 , p_action_information15 => l_sick_addr.info16 -- NL caravan
2135 , p_action_information16 => l_sick_ph_no
2136 , p_action_information17 => l_for_sick_ph_no
2137 , p_action_information18 => l_sick_mobile_no
2138 , p_action_information19 => l_sick_addr.address1 -- NL_GLB street
2139 , p_action_information20 => l_sick_addr.address2 -- NL_GLB hse no
2140 );
2141
2142 ELSE
2143 OPEN csr_get_person_addr(l_assignment_id, l_abs_start_date);
2144 FETCH csr_get_person_addr INTO l_person_addr;
2145 CLOSE csr_get_person_addr;
2146 hr_utility.trace('Person Address Style : ' || l_person_addr.style);
2147 IF l_person_addr.style IS NOT NULL THEN
2148
2149 /* Archiving sickness address */
2150 pay_action_information_api.create_action_information
2151 (
2152 p_action_information_id => l_action_info_id
2153 , p_action_context_id => l_child_aa_id
2154 , p_action_context_type => 'AAP'
2155 , p_object_version_number => l_ovn
2156 , p_assignment_id => l_assignment_id
2157 , p_effective_date => p_effective_date
2158 , p_source_id => NULL
2159 , p_source_text => NULL
2160 , p_action_information_category => 'NL_DSR_ADDRESS'
2161 , p_action_information1 => l_abs_attendence_id
2162 , p_action_information2 => l_person_addr.style
2163 , p_action_information3 => l_person_addr.start_date
2164 , p_action_information4 => l_person_addr.end_date
2165 , p_action_information5 => REPLACE(l_person_addr.postal,' ','')
2166 , p_action_information6 => l_person_addr.town
2167 , p_action_information7 => l_person_addr.country -- only for NL_GLB
2168 , p_action_information8 => l_person_addr.tel1
2169 , p_action_information9 => l_person_addr.tel2 -- for NL mobile, for NL_GLB forino
2170 , p_action_information10 => l_person_addr.reg1 -- for NL st name, for NL_GLB St name
2171 , p_action_information11 => l_person_addr.reg2 -- Only NL_GLB loc des
2172 , p_action_information12 => l_person_addr.info13 -- NL hse no
2173 , p_action_information13 => l_person_addr.info14 -- NL hse no add
2174 , p_action_information14 => l_person_addr.info15 -- NL hse boat NL_GLB ISO country
2175 , p_action_information15 => l_person_addr.info16 -- NL caravan
2176 , p_action_information16 => l_sick_ph_no
2177 , p_action_information17 => l_for_sick_ph_no
2178 , p_action_information18 => l_sick_mobile_no
2179 , p_action_information19 => l_person_addr.address1 -- NL_GLB street
2180 , p_action_information20 => l_person_addr.address2 -- NL_GLB hse no
2181 );
2182 END IF;
2183 END IF;
2184 END IF;
2185 END LOOP;
2186 ELSIF l_type IN ('W','R') THEN
2187 hr_utility.trace(l_type||' Assignment Action ID : '||p_assignment_action_id );
2188 OPEN c_get_assid_chunk (p_assignment_action_id);
2189 FETCH c_get_assid_chunk INTO l_assignment_id, l_chunk_number;
2190 CLOSE c_get_assid_chunk;
2191 hr_utility.trace('Assignment ID/Chunk : '||l_assignment_id||' / '||l_chunk_number);
2192
2193 FOR r_get_archived_absence IN get_archived_absence(l_assignment_id, l_start_date, l_end_date, l_type)
2194 LOOP
2195
2196 hr_utility.trace('Absence Attendence ID : ' || r_get_archived_absence.abs_att_id);
2197
2198 hr_utility.trace('Absence Start/End Date : ' ||
2199 to_char(fnd_date.canonical_to_date(r_get_archived_absence.st_dt),'DDMonYYYY') || ' / ' ||
2200 to_char(fnd_date.canonical_to_date(r_get_archived_absence.end_dt),'DDMonYYYY'));
2201 hr_utility.trace('Org Asg Action ID : ' || r_get_archived_absence.org_asg_act_id);
2202
2203 hr_nonrun_asact.insint(lockingactid => p_assignment_action_id
2204 , lockedactid => r_get_archived_absence.org_asg_act_id);
2205 IF l_type = 'W' THEN
2206 -- Update previous archive incase of withdrawal.
2207 pay_action_information_api.update_action_information
2208 (p_action_information_id => r_get_archived_absence.act_info_id
2209 , p_object_version_number => l_ovn
2210 , p_action_information30 => 'Y'
2211 );
2212 END IF;
2213 END LOOP;
2214 END IF;
2215
2216 hr_utility.trace('X====archive_code==========================================X');
2217 EXCEPTION
2218 WHEN TOO_MANY_ROWS THEN
2219 hr_utility.trace('Too Many Rows Exception in archive_code');
2220
2221 WHEN NO_DATA_FOUND THEN
2222 hr_utility.trace('No Data Found Exception in archive_code');
2223
2224 WHEN others THEN
2225 hr_utility.trace('Exception in archive_code SQL-ERRM : '||SQLERRM);
2226
2227 END archive_code;
2228 /**************************************************************************/
2229
2230
2231 /*-------------------------------------------------------------------------------
2232 |Name : DEINITIALIZATION_CODE |
2233 |Type : Procedure |
2234 |Description : Deinitialization code |
2235 -------------------------------------------------------------------------------*/
2236
2237
2238 PROCEDURE deinitialization_code(p_actid IN NUMBER) IS
2239 BEGIN
2240 hr_utility.trace('+====deinitialization_code====X '|| p_actid);
2241 END deinitialization_code;
2242
2243
2244
2245
2246
2247 /**************************************************************************/
2248 PROCEDURE gen_header_xml
2249 IS
2250 l_string VARCHAR2(32767) := NULL;
2251 l_string_value VARCHAR2(1000) := NULL;
2252 l_clob pay_file_details.file_fragment%TYPE;
2253 l_blob pay_file_details.blob_file_fragment%TYPE;
2254 l_payroll_action_id NUMBER;
2255 l_org_struct_id NUMBER := NULL;
2256 l_person_id NUMBER := NULL;
2257 l_org_id NUMBER := NULL;
2258 l_bg_id NUMBER;
2259 l_start_date DATE := NULL;
2260 l_end_date DATE := NULL;
2261 l_type VARCHAR2(1) := NULL;
2262 l_lang VARCHAR2(2) := userenv ('lang');
2263
2264 CURSOR c_get_hierarchy(p_org_struct_id IN NUMBER) IS
2265 SELECT name
2266 FROM per_organization_structures
2267 WHERE organization_structure_id = p_org_struct_id;
2268
2269 CURSOR c_get_employer(p_org_id IN NUMBER) IS
2270 SELECT name
2271 FROM hr_organization_units
2272 WHERE organization_id = p_org_id;
2273
2274 CURSOR c_get_employee(p_person_id IN NUMBER) IS
2275 SELECT full_name
2276 FROM per_all_people_f
2277 WHERE person_id = p_person_id;
2278
2279 BEGIN
2280 hr_utility.trace('+====gen_header_xml========================================+');
2281 l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
2282 hr_utility.trace('l_payroll_action_id : '||l_payroll_action_id);
2283 l_string := l_string || '<ROOT>'||EOL;
2284
2285 get_all_parameters (
2286 p_payroll_action_id => l_payroll_action_id
2287 ,p_org_struct_id => l_org_struct_id
2288 ,p_person_id => l_person_id
2289 ,p_org_id => l_org_id
2290 ,p_bg_id => l_bg_id
2291 ,p_start_date => l_start_date
2292 ,p_end_date => l_end_date
2293 ,p_type => l_type);
2294
2295 l_string_value := get_look_up_value('HR_NL_DS_REPORT_TYPE',l_type,l_lang);
2296 l_string := l_string || '<P_REPORT_TYPE>'||clean_XML(l_string_value)||'</P_REPORT_TYPE>'||EOL;
2297
2298 l_string_value := fnd_date.date_to_chardate(l_start_date);
2299 l_string := l_string || '<P_DATE_FROM>'||clean_XML(l_string_value)||'</P_DATE_FROM>'||EOL;
2300
2301 l_string_value := fnd_date.date_to_chardate(l_end_date);
2302 l_string := l_string || '<P_DATE_TO>'||clean_XML(l_string_value)||'</P_DATE_TO>'||EOL;
2303
2304 IF l_org_struct_id IS NOT NULL THEN
2305 OPEN c_get_hierarchy(l_org_struct_id);
2306 FETCH c_get_hierarchy INTO l_string_value;
2307 CLOSE c_get_hierarchy;
2308 l_string := l_string || '<P_HIERARCHY>'||clean_XML(l_string_value)||'</P_HIERARCHY>'||EOL;
2309 END IF;
2310
2311 IF l_org_id IS NOT NULL THEN
2312 OPEN c_get_employer(l_org_id);
2313 FETCH c_get_employer INTO l_string_value;
2314 CLOSE c_get_employer;
2315 l_string := l_string || '<P_EMPLOYER>'||clean_XML(l_string_value)||'</P_EMPLOYER>'||EOL;
2316 END IF;
2317
2318 IF l_person_id IS NOT NULL THEN
2319 OPEN c_get_employee(l_person_id);
2320 FETCH c_get_employee INTO l_string_value;
2321 CLOSE c_get_employee;
2322 l_string := l_string || '<P_EMPLOYEE>'||clean_XML(l_string_value)||'</P_EMPLOYEE>'||EOL;
2323 END IF;
2324
2325 l_clob := l_clob||l_string;
2326 IF l_clob IS NOT NULL THEN
2327 l_blob := c2b(l_clob);
2328 pay_core_files.write_to_magtape_lob(l_blob);
2329 END IF;
2330 hr_utility.trace('X====gen_header_xml========================================X');
2331 EXCEPTION
2332 WHEN others THEN
2333 hr_utility.trace('Exception in gen_header_xml SQL-ERRM : '||SQLERRM);
2334 END gen_header_xml;
2335
2336
2337 /**************************************************************************/
2338 PROCEDURE gen_footer_xml
2339 IS
2340 l_buf VARCHAR2(32767);
2341 BEGIN
2342 hr_utility.trace('+====gen_footer_xml========================================+');
2343 l_buf := l_buf || '</ROOT>'||EOL ;
2344 pay_core_files.write_to_magtape_lob(l_buf);
2345 hr_utility.trace('X====gen_footer_xml========================================X');
2346 EXCEPTION
2347 WHEN others THEN
2348 hr_utility.trace('Exception in gen_footer_xml SQL-ERRM : '||SQLERRM);
2349 END gen_footer_xml;
2350 /**************************************************************************/
2351
2352
2353 PROCEDURE gen_body_xml_main(p_type varchar2, p_id number) IS
2354
2355
2356 CURSOR cur_conn_abs_info_details (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
2357 SELECT pai.action_information2 conn_count
2358 ,pai.action_information3 start1
2359 ,pai.action_information4 end1
2360 ,pai.action_information5 start2
2361 ,pai.action_information6 end2
2362 ,pai.action_information7 start3
2363 ,pai.action_information8 end3
2364 ,pai.action_information9 start4
2365 ,pai.action_information10 end4
2366 ,pai.action_information11 start5
2367 ,pai.action_information12 end5
2368 ,pai.action_information13 start6
2369 ,pai.action_information14 end6
2370 ,pai.action_information15 start7
2371 ,pai.action_information16 end7
2372 ,pai.action_information17 start8
2373 ,pai.action_information18 end8
2374 ,pai.action_information19 start9
2375 ,pai.action_information20 end9
2376 ,pai.action_information21 start10
2377 ,pai.action_information22 end10
2378 ,pai.action_information23 start11
2379 ,pai.action_information24 end11
2380 ,pai.action_information25 start12
2381 ,pai.action_information26 end12
2382 ,pai.action_information27 start13
2383 ,pai.action_information28 end13
2384 ,pai.action_information29 start14
2385 ,pai.action_information30 end14
2386 FROM pay_action_information pai
2387 WHERE pai.action_context_id = c_assignment_action_id
2388 AND pai.action_information_category = 'NL_DSR_CONN_ABS_INFO'
2389 AND action_context_type = 'AAP';
2390
2391 /**************************************************************************/
2392 CURSOR cur_abs_info_details (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
2393 SELECT pai.action_information1 abs_att_id
2394 ,pai.action_information2 start_date
2395 ,pai.action_information3 end_date
2396 ,pai.action_information4 abs_cat
2397 ,pai.action_information5 person_id
2398 ,pai.action_information6 test_msg
2399 ,pai.action_information7 tax_no_sender
2400 ,pai.action_information8 role_sender
2401 ,pai.action_information9 contact_per_or_dept
2402 ,pai.action_information10 ph_no_contact
2403 ,pai.action_information11 risk_group
2404 ,pai.action_information12 sector
2405 ,pai.action_information13 tax_reg_num
2406 ,pai.action_information14 employer_acc_no
2407 ,pai.action_information17 est_name
2408 ,pai.action_information18 gender
2409 ,pai.action_information19 employer_iban
2410 FROM pay_action_information pai
2411 WHERE pai.action_context_id=c_assignment_action_id
2412 AND pai.ACTION_INFORMATION_CATEGORY = 'NL_DSR_ABS_INFO'
2413 AND action_context_type = 'AAP';
2414 /**************************************************************************/
2415 CURSOR cur_sick_info_details (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
2416 SELECT pai.action_information2 sick_ref
2417 ,pai.action_information3 noti_date
2418 ,pai.action_information4 dsr_st_dt
2419 ,pai.action_information5 code_pymt
2420 ,pai.action_information6 reason
2421 ,pai.action_information7 work_sat
2422 ,pai.action_information8 work_sun
2423 ,pai.action_information9 si_first
2424 ,pai.action_information10 daily_si
2425 ,pai.action_information11 amt_wsw
2426 ,pai.action_information12 acc_reason
2427 ,pai.action_information13 reason_late
2428 ,pai.action_information14 avg_hr
2429 ,pai.action_information15 partial_rec
2430 ,pai.action_information16 dt_ter_notice
2431 ,pai.action_information17 term_init
2432 ,pai.action_information18 imme_term
2433 ,pai.action_information19 no_of_calls
2434 ,pai.action_information20 period_less_three
2435 ,pai.action_information21 no_of_first_call
2436 ,pai.action_information22 work_long_all
2437 ,pai.action_information23 temp_cont
2438 ,pai.action_information24 contact_name
2439 ,pai.action_information25 est_name
2440 ,pai.action_information26 gender
2441 ,pai.action_information27 contact_ph_no
2442 ,pai.action_information28 conn_absence_count
2443 ,pai.action_information30 l_29bzw
2444 FROM pay_action_information pai
2445 WHERE pai.action_context_id = c_assignment_action_id
2446 AND pai.action_information_category = 'NL_DSR_SICK_INFO'
2447 AND action_context_type = 'AAP';
2448 /**************************************************************************/
2449 CURSOR cur_emp_details (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
2450 SELECT action_information2 sofi_number
2451 ,action_information3 date_of_birth
2452 ,action_information4 gender
2453 ,action_information5 init
2454 ,action_information6 prefix
2455 ,action_information7 last_name
2456 ,action_information8 acc_no
2457 ,action_information9 bic
2458 ,action_information10 iban
2459 ,action_information11 wage_tax_discount
2460 ,action_information12 asg_seq
2461 ,action_information13 job_name
2462 ,action_information14 fz_code
2463 ,action_information15 wage_tax_table
2464 ,action_information16 no_of_waiting
2465 ,action_information17 percent_pay
2466 ,action_information18 code_kind
2467 ,action_information19 start_date
2468 ,action_information20 end_date
2469 ,action_information21 contract
2470 ,action_information22 cao_code
2471 ,action_information23 temp_worker
2472 ,action_information24 temp_to_contr
2473 ,action_information25 termination
2474 ,action_information26 appr_temp_worker
2475 ,action_information27 emp_num
2476 ,action_information28 ass_start_date
2477 ,action_information29 ass_end_date
2478 FROM pay_action_information pai
2479 WHERE pai.action_context_id = c_assignment_action_id
2480 AND pai.action_information_category = 'NL_DSR_EMP_INFO'
2481 AND action_context_type = 'AAP';
2482 /**************************************************************************/
2483 CURSOR cur_addr_details (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
2484 SELECT action_information2 style
2485 ,action_information3 start_date
2486 ,action_information4 end_date
2487 ,action_information5 postal
2488 ,action_information6 town
2489 ,action_information7 country
2490 ,action_information8 tel1
2491 ,action_information9 tel2
2492 ,action_information10 reg1
2493 ,action_information11 reg2
2494 ,action_information12 info13
2495 ,action_information13 info14
2496 ,action_information14 info15
2497 ,action_information15 info16
2498 ,action_information16 sick_ph_no
2499 ,action_information17 for_sick_ph_no
2500 ,action_information18 sick_mobile_no
2501 ,action_information19 address1
2502 ,action_information20 address2
2503 FROM pay_action_information pai
2504 WHERE pai.action_context_id = c_assignment_action_id
2505 AND pai.action_information_category = 'NL_DSR_ADDRESS'
2506 AND action_context_type = 'AAP';
2507 /**************************************************************************/
2508 CURSOR cur_addl_sick_info(p_abs_attendance_id IN NUMBER) IS
2509 SELECT fdst.short_text text
2510 FROM fnd_attached_documents fad
2511 ,fnd_documents fd
2512 ,fnd_document_categories fdc
2513 ,fnd_document_datatypes fdd
2514 ,fnd_documents_short_text fdst
2515 WHERE fad.entity_name = 'PER_ABSENCE_ATTENDANCES'
2516 AND fad.pk1_value = to_char(p_abs_attendance_id)
2517 AND fd.document_id = fad.document_id
2518 AND fdd.datatype_id = fd.datatype_id
2519 AND fdc.category_id = fd.category_id
2520 AND fdc.name = 'HR_COMMENT'
2521 AND fdd.name = 'SHORT_TEXT'
2522 AND fdd.language = 'US'
2523 AND fdst.media_id = fd.media_id;
2524 /**************************************************************************/
2525 CURSOR cur_withdrawl_flag(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
2526 SELECT pai.action_information30 flag
2527 FROM pay_action_information pai
2528 WHERE pai.action_context_id = c_assignment_action_id
2529 AND pai.action_information_category = 'NL_DSR_ABS_INFO'
2530 AND action_context_type = 'AAP';
2531 /**************************************************************************/
2532 CURSOR cur_app_version IS
2533 SELECT release_name
2534 FROM fnd_product_groups;
2535 /**************************************************************************/
2536 --Variables--
2537 l_clob PAY_FILE_DETAILS.FILE_FRAGMENT%TYPE;
2538 l_blob PAY_FILE_DETAILS.BLOB_FILE_FRAGMENT%TYPE;
2539 l_file UTL_FILE.FILE_TYPE;
2540 l_directory_path VARCHAR2(500);
2541 l_file_name VARCHAR2(50);
2542 x NUMBER := 0;
2543 r NUMBER := 0;
2544 l_add_info cur_addl_sick_info%ROWTYPE;
2545 l_abs_info cur_abs_info_details%ROWTYPE;
2546 l_sick_info cur_sick_info_details%ROWTYPE;
2547 l_emp_details cur_emp_details%ROWTYPE;
2548 l_addr_details cur_addr_details%ROWTYPE;
2549 l_get_app_ver cur_app_version%ROWTYPE;
2550 l_withdrawl_flag cur_withdrawl_flag%ROWTYPE;
2551 l_type VARCHAR2(1);
2552 l_lang VARCHAR2(2) := userenv ('lang');
2553 xXMLTable tXMLTable;
2554 rXMLTable tXMLTable;
2555
2556 TYPE tDateTable IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
2557 start_date tDateTable;
2558 end_date tDateTable;
2559 connected_absence_count NUMBER :=0;
2560
2561 PROCEDURE Tag (xCtr IN OUT NOCOPY NUMBER
2562 ,rCtr IN OUT NOCOPY NUMBER
2563 ,pTagName IN VARCHAR2
2564 ,pTagValue IN VARCHAR2
2565 ,pDestination IN VARCHAR2)
2566 IS
2567 BEGIN
2568 IF pTagValue IS NOT NULL THEN
2569 IF pDestination IN ('X','B') THEN
2570 xXMLTable(xCtr).TagName := pTagName;
2571 xXMLTable(xCtr).TagValue := pTagValue;
2572
2573 IF pTagName = 'UwvZwZiekteMelding' AND pTagValue = '_START_' THEN
2574 xXMLTable(xCtr).TagName := xXMLTable(xCtr).TagName||EOL||
2575 'xsi:schemaLocation="http://schemas.uwv.nl/UwvML/Berichten/UwvZwZiekteMelding-v0215 UwvZwZiekteMelding-v0215-b01.xsd"'||EOL||
2576 'xmlns:str="http://schemas.uwv.nl/UwvML/Berichten/UwvZwZiekteMelding-v0215"'||EOL||
2577 'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"';
2578 END IF;
2579
2580 xCtr := xCtr + 1;
2581 END IF;
2582 IF pDestination IN ('R','B') THEN
2583 rXMLTable(rCtr).TagName := pTagName;
2584 rXMLTable(rCtr).TagValue := pTagValue;
2585 rCtr := rCtr + 1;
2586 END IF;
2587 END IF;
2588 END Tag;
2589
2590
2591 BEGIN
2592 hr_utility.trace('+====gen_body_xml_main=====================================+');
2593
2594 OPEN cur_abs_info_details(p_id);
2595 FETCH cur_abs_info_details INTO l_abs_info;
2596 CLOSE cur_abs_info_details;
2597
2598 OPEN cur_sick_info_details(p_id);
2599 FETCH cur_sick_info_details INTO l_sick_info;
2600 CLOSE cur_sick_info_details;
2601
2602 OPEN cur_emp_details(p_id);
2603 FETCH cur_emp_details INTO l_emp_details;
2604 CLOSE cur_emp_details;
2605
2606 OPEN cur_addr_details(p_id);
2607 FETCH cur_addr_details INTO l_addr_details;
2608 CLOSE cur_addr_details;
2609
2610 IF l_abs_info.abs_att_id IS NOT NULL THEN
2611 OPEN cur_addl_sick_info(l_abs_info.abs_att_id);
2612 FETCH cur_addl_sick_info into l_add_info;
2613 CLOSE cur_addl_sick_info;
2614 END IF;
2615
2616 OPEN cur_app_version;
2617 FETCH cur_app_version into l_get_app_ver;
2618 CLOSE cur_app_version;
2619
2620 IF p_type = 'R' THEN
2621 OPEN cur_withdrawl_flag(p_id);
2622 FETCH cur_withdrawl_flag into l_withdrawl_flag;
2623 CLOSE cur_withdrawl_flag;
2624 END IF;
2625
2626 IF NVL(l_sick_info.conn_absence_count,0)>0 THEN --Only if Count of connected absences >0
2627 OPEN cur_conn_abs_info_details(p_id);
2628 FETCH cur_conn_abs_info_details into
2629 connected_absence_count,
2630 start_date(1), end_date(1),
2631 start_date(2), end_date(2),
2632 start_date(3), end_date(3),
2633 start_date(4), end_date(4),
2634 start_date(5), end_date(5),
2635 start_date(6), end_date(6),
2636 start_date(7), end_date(7),
2637 start_date(8), end_date(8),
2638 start_date(9), end_date(9),
2639 start_date(10), end_date(10),
2640 start_date(11), end_date(11),
2641 start_date(12), end_date(12),
2642 start_date(13), end_date(13),
2643 start_date(14), end_date(14);
2644 CLOSE cur_conn_abs_info_details;
2645 END IF;
2646 hr_utility.trace('Fetched all data. XML Generation starts');
2647
2648 Tag(x,r,'UwvZwZiekteMelding','_START_','B'); -- sickness report*
2649 Tag(x,r,'IndTestbericht',l_abs_info.test_msg,'X'); -- test msg
2650 Tag(x,r,'KetenPartij','_START_','B'); -- + sender data*
2651 Tag(x,r,'Loonheffingennr',l_abs_info.tax_no_sender,'X'); -- tax reg no sender*
2652 Tag(x,r,'CdRolKetenpartij',l_abs_info.role_sender,'X'); -- role sender*
2653 Tag(x,r,'NaamSoftwarePakket','Oracle HCM','X'); -- appln name
2654 Tag(x,r,'VersieSoftwarePakket',l_get_app_ver.release_name,'X'); -- ver no
2655 IF l_abs_info.contact_per_or_dept IS NOT NULL
2656 OR l_abs_info.ph_no_contact IS NOT NULL THEN
2657 Tag(x,r,'ContactGegevens','_START_','B'); -- + contact data
2658 Tag(x,r,'NaamContactpersoonAfd',l_abs_info.contact_per_or_dept,'B'); -- person or dept
2659 Tag(x,r,'TelefoonnrContactpersoonAfd',l_abs_info.ph_no_contact,'B'); -- ph no
2660 Tag(x,r,'ContactGegevens','_END_','B'); -- - contact data
2661 END IF;
2662 Tag(x,r,'KetenPartij','_END_','B'); -- - sender data*
2663 Tag(x,r,'AdministratieveEenheid','_START_','B'); -- + employer*
2664 Tag(x,r,'Loonheffingennr',l_abs_info.tax_reg_num,'X'); -- tax no employer*
2665 Tag(x,r,'SectorRisicogroep','_START_','X'); -- + risk sector grp*
2666 Tag(x,r,'CdRisicoPremiegroep',l_abs_info.risk_group,'X'); -- risk grp*
2667 Tag(x,r,'CdSectorOsv',l_abs_info.sector,'X'); -- sector *
2668 Tag(x,r,'SectorRisicogroep','_END_','X'); -- - risk sector grp*
2669 IF l_abs_info.employer_acc_no IS NOT NULL
2670 OR l_abs_info.employer_iban IS NOT NULL
2671 THEN
2672 Tag(x,r,'Bankrekening','_START_','X'); -- + employer bank
2673 Tag(x,r,'Bankrekeningnr',l_abs_info.employer_acc_no,'X'); -- acc no
2674 Tag(x,r,'Iban',l_abs_info.employer_iban,'X'); -- iban no
2675 Tag(x,r,'Bankrekening','_END_','X'); -- - employer bank
2676 END IF;
2677 Tag(x,r,'NatuurlijkPersoon','_START_','B'); -- + employee data*
2678 Tag(x,r,'Burgerservicenr',l_emp_details.sofi_number,'B'); -- sofino*
2679 Tag(x,r,'Geboortedat',l_emp_details.date_of_birth,'X'); -- dob*
2680 Tag(x,r,'Geboortedat',fnd_date.date_to_chardate(
2681 to_date(l_emp_details.date_of_birth,'YYYYMMDD')),'R');
2682 Tag(x,r,'Geslacht',l_emp_details.gender,'X'); -- gender
2683 Tag(x,r,'Geslacht',get_look_up_value('HR_NL_DS_GENDER',
2684 l_emp_details.gender,l_lang),'R');
2685 Tag(x,r,'Voorletters',l_emp_details.init,'B'); -- initials
2686 Tag(x,r,'Voorvoegsel',l_emp_details.prefix,'B'); -- prefix
2687 Tag(x,r,'SignificantDeelVanDeAchternaam',l_emp_details.last_name,'B'); -- last name*
2688 IF NVL(l_addr_details.sick_ph_no, l_addr_details.tel1) IS NOT NULL
2689 OR NVL(l_addr_details.sick_mobile_no , l_addr_details.tel2) IS NOT NULL
2690 OR NVL(l_addr_details.for_sick_ph_no , l_addr_details.tel2) IS NOT NULL
2691 OR l_addr_details.start_date IS NOT NULL
2692 OR l_addr_details.end_date IS NOT NULL
2693 OR l_addr_details.reg1 IS NOT NULL
2694 OR l_addr_details.info13 IS NOT NULL
2695 OR l_addr_details.info14 IS NOT NULL
2696 OR l_addr_details.postal IS NOT NULL
2697 OR l_addr_details.town IS NOT NULL
2698 OR l_addr_details.info15 IS NOT NULL
2699 OR l_addr_details.info16 IS NOT NULL
2700 OR l_addr_details.address1 IS NOT NULL
2701 OR l_addr_details.address2 IS NOT NULL
2702 OR l_addr_details.reg1 IS NOT NULL
2703 OR l_addr_details.reg2 IS NOT NULL
2704 OR l_addr_details.country IS NOT NULL
2705 THEN
2706
2707 Tag(x,r,'VerblijfadresTijdensZiekte','_START_','B'); -- + sickness address
2708 Tag(x,r,'Telefoonnr',NVL(l_addr_details.sick_ph_no
2709 , l_addr_details.tel1),'B'); -- sick tel no
2710 Tag(x,r,'TelefoonnrMobiel',NVL(l_addr_details.sick_mobile_no
2711 , l_addr_details.tel2),'B'); -- mobile no sick
2712 Tag(x,r,'TelefoonnrBuitenland',NVL(l_addr_details.for_sick_ph_no
2713 , l_addr_details.tel2),'B'); -- foriegn ph no
2714 IF l_addr_details.start_date IS NOT NULL
2715 OR l_addr_details.end_date IS NOT NULL
2716 OR l_addr_details.reg1 IS NOT NULL
2717 OR l_addr_details.info13 IS NOT NULL
2718 OR l_addr_details.info14 IS NOT NULL
2719 OR l_addr_details.postal IS NOT NULL
2720 OR l_addr_details.town IS NOT NULL
2721 OR l_addr_details.info15 IS NOT NULL
2722 OR l_addr_details.info16 IS NOT NULL
2723 OR l_addr_details.address1 IS NOT NULL
2724 OR l_addr_details.address2 IS NOT NULL
2725 OR l_addr_details.reg1 IS NOT NULL
2726 OR l_addr_details.reg2 IS NOT NULL
2727 OR l_addr_details.country IS NOT NULL
2728 THEN
2729 Tag(x,r,'Adres','_START_','B'); -- + address
2730 Tag(x,r,'DatB',l_addr_details.start_date,'X'); -- start dt addr
2731 Tag(x,r,'DatB',fnd_date.date_to_chardate(
2732 to_date(l_addr_details.start_date,'YYYYMMDD')),'R');
2733 Tag(x,r,'DatE',l_addr_details.end_date,'X'); -- end dt addr
2734 Tag(x,r,'DatE',fnd_date.date_to_chardate(
2735 to_date(l_addr_details.end_date,'YYYYMMDD')),'R');
2736 IF l_addr_details.style = 'NL' THEN
2737 Tag(x,r,'StraatadresNederland','_START_','B'); -- + NL address
2738 Tag(x,r,'Straatnaam',l_addr_details.reg1,'B'); -- street name
2739 Tag(x,r,'Huisnr',l_addr_details.info13,'B'); -- house number
2740 Tag(x,r,'Huisnrtoevoeging',l_addr_details.info14,'B'); -- house number addition
2741 Tag(x,r,'Postcd',l_addr_details.postal,'B'); -- postal code
2742 Tag(x,r,'Woonplaatsnaam',l_addr_details.town,'B'); -- city
2743 Tag(x,r,'Woonbootverwijzing',REPLACE(REPLACE(l_addr_details.info15
2744 ,'Y','AB'),'N',''),'X'); -- houseboat
2745 Tag(x,r,'Woonbootverwijzing',get_look_up_value('HR_NL_YES_NO',
2746 l_addr_details.info15,l_lang),'R');
2747 Tag(x,r,'Woonwagenverwijzing',REPLACE(REPLACE(l_addr_details.info16
2748 ,'Y','WW'),'N',''),'X'); -- caravan
2749 Tag(x,r,'Woonwagenverwijzing',get_look_up_value('HR_NL_YES_NO',
2750 l_addr_details.info16,l_lang),'R');
2751 Tag(x,r,'StraatadresNederland','_END_','B'); -- - NL address
2752 ELSIF l_addr_details.style = 'NL_GLB' THEN
2753 Tag(x,r,'StraatadresBuitenland','_START_','B'); -- + foriegn address
2754 Tag(x,r,'StraatnaamBuitenland',l_addr_details.info13,'B'); -- street name
2755 Tag(x,r,'HuisnrBuitenland',l_addr_details.info14,'B'); -- house number
2756 Tag(x,r,'PostcdBuitenland',l_addr_details.postal,'B'); -- postal code
2757 Tag(x,r,'WoonplaatsnaamBuitenland',l_addr_details.town,'B'); -- city
2758 Tag(x,r,'RegionaamBuitenland',l_addr_details.reg1,'B'); -- region name
2759 Tag(x,r,'LocatieomsBuitenland',l_addr_details.info15,'B'); -- location desr
2760 Tag(x,r,'LandencdIso',l_addr_details.info16,'B'); -- iso country code
2761 Tag(x,r,'Landsnaam',l_addr_details.country,'B'); -- country name
2762 Tag(x,r,'StraatadresBuitenland','_END_','B'); -- - foriegn address
2763 END IF;
2764 Tag(x,r,'Adres','_END_','B'); -- - address
2765 END IF;
2766 Tag(x,r,'VerblijfadresTijdensZiekte','_END_','B'); -- - sickness address
2767 END IF;
2768 IF l_emp_details.acc_no IS NOT NULL
2769 OR substr(NVL(l_emp_details.iban,'00'),1,2) = 'NL' THEN
2770 Tag(x,r,'BankrekeningNederland','_START_','B'); -- + Employee Bank
2771 Tag(x,r,'Bankrekeningnr',l_emp_details.acc_no,'B'); -- acc no
2772 IF substr(NVL(l_emp_details.iban,'00'),1,2) = 'NL' THEN
2773 Tag(x,r,'Iban',l_emp_details.iban,'B'); -- iban
2774 END IF;
2775 Tag(x,r,'BankrekeningNederland','_END_','B'); -- - Employee Bank
2776 ELSIF (l_emp_details.iban IS NOT NULL AND substr(l_emp_details.iban,1,2) <> 'NL')
2777 OR l_emp_details.bic IS NOT NULL THEN
2778 Tag(x,r,'BankrekeningBuitenland','_START_','B'); -- + Employee Foreign Bank
2779 Tag(x,r,'Iban',l_emp_details.iban,'B'); -- iban
2780 Tag(x,r,'Bic',get_look_up_value('HR_NL_BIC_CODES',
2781 l_emp_details.bic,l_lang),'B'); -- bic
2782 Tag(x,r,'BankrekeningBuitenland','_END_','B'); -- - Employee Foreign Bank
2783 END IF;
2784 Tag(x,r,'Arbeidsverhouding','_START_','B'); -- + assignment details*
2785 Tag(x,r,'IndLoonheffingskorting',l_emp_details.wage_tax_discount,'X'); -- wage tax disc
2786 Tag(x,r,'IndLoonheffingskorting',get_look_up_value('HR_NL_YES_NO',
2787 REPLACE(REPLACE(l_emp_details.wage_tax_discount,'1','Y'),'2','N'),l_lang),'R');
2788 Tag(x,r,'NrInkomstenverhouding',l_emp_details.asg_seq,'B'); -- NumIV override/asg seq
2789 Tag(x,r,'NaamBeroepOngecodeerd',l_emp_details.job_name,'B'); -- job name
2790 Tag(x,r,'CdFaseIndelingFZ',l_emp_details.fz_code,'X'); -- FZ code
2791 Tag(x,r,'CdFaseIndelingFZ',get_look_up_value('NL_TEMP_LABOR_CODE',
2792 l_emp_details.fz_code,l_lang),'R');
2793 Tag(x,r,'CdLoonbelastingtabel',l_emp_details.wage_tax_table,'B'); -- wage tax table
2794 Tag(x,r,'AantLoonwachtdagen',l_emp_details.no_of_waiting,'B'); -- no of waiting days
2795 Tag(x,r,'PercLoondoorbetTijdensAo',l_emp_details.percent_pay,'B'); -- percentage payment
2796 Tag(x,r,'CdAardArbeidsverhouding',l_emp_details.code_kind,'X'); -- code kind of labour relation
2797 Tag(x,r,'CdAardArbeidsverhouding',get_look_up_value('NL_LABOR_RELATION_CODE',
2798 l_emp_details.code_kind,l_lang),'R');
2799 IF l_emp_details.start_date IS NOT NULL
2800 OR l_emp_details.end_date IS NOT NULL
2801 THEN
2802 Tag(x,r,'DatB',l_emp_details.start_date,'X'); -- start date income relation
2803 Tag(x,r,'DatB',fnd_date.date_to_chardate(to_date(l_emp_details.start_date,
2804 'YYYYMMDD')),'R');
2805 Tag(x,r,'DatE',l_emp_details.end_date,'X'); -- end date income relation
2806 Tag(x,r,'DatE',fnd_date.date_to_chardate(to_date(l_emp_details.end_date,
2807 'YYYYMMDD')),'R');
2808 ELSE
2809 Tag(x,r,'DatB',l_emp_details.ass_start_date,'X'); -- start date assignment
2810 Tag(x,r,'DatB',fnd_date.date_to_chardate(to_date(l_emp_details.ass_start_date,
2811 'YYYYMMDD')),'R');
2812 Tag(x,r,'DatE',l_emp_details.ass_end_date,'X'); -- end date assignment
2813 Tag(x,r,'DatE',fnd_date.date_to_chardate(to_date(l_emp_details.ass_end_date,
2814 'YYYYMMDD')),'R');
2815 END IF;
2816 Tag(x,r,'OproepInvalEnZiekUitDienstArbeidsverhouding','_START_','B'); -- + temp work or end empl during sickness
2817 Tag(x,r,'CdContractOnbepaaldeTijd',l_emp_details.contract,'X'); -- code contract
2818 Tag(x,r,'CdContractOnbepaaldeTijd',get_look_up_value('NL_EMPLOYEE_CATG'
2819 ,l_emp_details.contract,l_lang),'R');
2820 Tag(x,r,'CdCao',l_emp_details.cao_code,'X'); -- cao code
2821 Tag(x,r,'CdCao',get_look_up_value('NL_COLLECTIVE_AGREEMENT'
2822 ,l_emp_details.cao_code,l_lang),'R');
2823 Tag(x,r,'IndCaoBepalingOproeparbeid',l_emp_details.temp_worker,'X'); -- temp workers
2824 Tag(x,r,'IndCaoBepalingOproeparbeid',get_look_up_value('HR_NL_YES_NO',
2825 REPLACE(REPLACE(l_emp_details.temp_worker,'1','Y'),'2','N'),l_lang),'R');
2826 Tag(x,r,'IndCaoBepTijdelijkNaarVastDvb',l_emp_details.temp_to_contr,'X'); -- temp to fixed contract
2827 Tag(x,r,'IndCaoBepTijdelijkNaarVastDvb',get_look_up_value('HR_NL_YES_NO',
2828 REPLACE(REPLACE(l_emp_details.temp_to_contr,'1','Y'),'2','N'),l_lang),'R');
2829 Tag(x,r,'IndVoorafOpzeggenAok',l_emp_details.termination,'X'); -- term contract
2830 Tag(x,r,'IndVoorafOpzeggenAok',get_look_up_value('HR_NL_YES_NO',
2831 REPLACE(REPLACE(l_emp_details.termination,'1','Y'),'2','N'),l_lang),'R');
2832 Tag(x,r,'IndVerschijningsplichtOproepkr',l_emp_details.appr_temp_worker,'X'); -- appearence temp workers
2833 Tag(x,r,'IndVerschijningsplichtOproepkr',get_look_up_value('HR_NL_YES_NO',
2834 REPLACE(REPLACE(l_emp_details.appr_temp_worker,'1','Y'),'2','N'),l_lang),'R');
2835 Tag(x,r,'OproepInvalEnZiekUitDienstArbeidsverhouding','_END_','B'); -- - temp work or end empl during sickness
2836 Tag(x,r,'MeldingZiekte','_START_','B'); -- + sickness details
2837 IF (p_type = 'R' AND l_withdrawl_flag.flag = 'Y') OR p_type = 'W' THEN
2838 Tag(x,r,'IndVerzoekTotIntrekken','1','X'); -- withdrawal code
2839 Tag(x,r,'IndVerzoekTotIntrekken',get_look_up_value('HR_NL_YES_NO',
2840 'Y',l_lang),'R');
2841 END IF;
2842 Tag(x,r,'ReferentieMelding',NVL(l_sick_info.sick_ref,l_emp_details.emp_num),'B'); -- sickness reference
2843 Tag(x,r,'DatTijdAanbiedenKetenpartij',to_char(SYSDATE, 'YYYYMMDDHH24MISS'),'X');-- time stamp
2844 Tag(x,r,'DatTijdAanbiedenKetenpartij',fnd_date.date_to_charDT(sysdate),'R');
2845 Tag(x,r,'DatOntvangstMeldingWerkgever',l_sick_info.noti_date,'X'); -- date of reporting
2846 Tag(x,r,'DatOntvangstMeldingWerkgever',fnd_date.date_to_chardate(
2847 to_date(l_sick_info.noti_date,'YYYYMMDD')),'R');
2848 Tag(x,r,'DatEersteAoDag',l_sick_info.dsr_st_dt,'X'); -- start date sickness
2849 Tag(x,r,'DatEersteAoDag',fnd_date.date_to_chardate(
2850 to_date(l_sick_info.dsr_st_dt,'YYYYMMDD')),'R');
2851 Tag(x,r,'ToelMelding',l_add_info.text,'B'); -- additional information
2852 Tag(x,r,'IndDirecteUitkering',l_sick_info.code_pymt,'X'); -- code payment to employee
2853 Tag(x,r,'IndDirecteUitkering',get_look_up_value('HR_NL_YES_NO',
2854 REPLACE(REPLACE(l_sick_info.code_pymt,'1','Y'),'2','N'),l_lang),'R');
2855 Tag(x,r,'CdRedenAangifteAo',l_sick_info.reason,'X'); -- list reason sickness report
2856 Tag(x,r,'CdRedenAangifteAo',get_look_up_value('HR_NL_DS_REASON',
2857 l_sick_info.reason,l_lang),'R');
2858 Tag(x,r,'IndWerkdagOpZaterdag',l_sick_info.work_sat,'X'); -- workday saturday
2859 Tag(x,r,'IndWerkdagOpZaterdag',get_look_up_value('HR_NL_YES_NO',
2860 REPLACE(REPLACE(l_sick_info.work_sat,'1','Y'),'2','N'),l_lang),'R');
2861 Tag(x,r,'IndWerkdagOpZondag',l_sick_info.work_sun,'X'); -- workday sunday
2862 Tag(x,r,'IndWerkdagOpZondag',get_look_up_value('HR_NL_YES_NO',
2863 REPLACE(REPLACE(l_sick_info.work_sun,'1','Y'),'2','N'),l_lang),'R');
2864 Tag(x,r,'BedrSvLoonGedeeltelijkWerkEersteAoDag',l_sick_info.si_first,'B'); -- SI wage first day on partial sick
2865 Tag(x,r,'BedrSvLoonPerDagGedeeltelijkWerkenTijdensAo',l_sick_info.daily_si,'B');-- Daily SI wage
2866 Tag(x,r,'BedrWswSubsidiesExclWgpremies',l_sick_info.amt_wsw,'B'); -- WSW subsidy amt
2867 Tag(x,r,'CdRedenRegres',l_sick_info.acc_reason,'X'); -- list accident
2868 Tag(x,r,'CdRedenRegres',get_look_up_value('HR_NL_DS_ACCIDENT',
2869 l_sick_info.acc_reason,l_lang),'R');
2870 Tag(x,r,'OmsRedenTeLateAanvraagUitkering',l_sick_info.reason_late,'B'); -- reason for late paymt
2871 Tag(x,r,'GemiddeldWerktijdPerWeek',l_sick_info.avg_hr,'B'); -- avg working hours per week
2872 Tag(x,r,'CdRedenToepassingArtikel29B',l_sick_info.l_29bzw,'X'); -- list art 129 BZW
2873 Tag(x,r,'CdRedenToepassingArtikel29B',get_look_up_value('HR_NL_DS_29BZW',
2874 l_sick_info.l_29bzw,l_lang),'R');
2875 IF l_sick_info.dt_ter_notice IS NOT NULL
2876 OR l_sick_info.term_init IS NOT NULL
2877 OR l_sick_info.imme_term IS NOT NULL
2878 OR l_sick_info.no_of_calls IS NOT NULL
2879 OR l_sick_info.period_less_three IS NOT NULL
2880 OR l_sick_info.no_of_first_call IS NOT NULL
2881 OR l_sick_info.work_long_all IS NOT NULL
2882 OR l_sick_info.temp_cont IS NOT NULL
2883 THEN
2884 Tag(x,r,'OproepInvalEnZiekUitDienstMelding','_START_','B'); -- + temp work or end empl
2885 Tag(x,r,'DatOpzeggenArbeidsovereenkomst',l_sick_info.dt_ter_notice,'X'); -- dt of notice of termination
2886 Tag(x,r,'DatOpzeggenArbeidsovereenkomst',fnd_date.date_to_chardate(
2887 to_date(l_sick_info.dt_ter_notice,'YYYYMMDD')),'R');
2888 Tag(x,r,'CdInitiatiefnemerOntslag',l_sick_info.term_init,'X'); -- code term initiative
2889 Tag(x,r,'CdInitiatiefnemerOntslag',get_look_up_value('HR_NL_DS_TERM_INIT',
2890 l_sick_info.term_init,l_lang),'R');
2891 Tag(x,r,'IndOnslagOpStaandeVoet',l_sick_info.imme_term,'X'); -- immediate termination
2892 Tag(x,r,'IndOnslagOpStaandeVoet',get_look_up_value('HR_NL_YES_NO',
2893 REPLACE(REPLACE(l_sick_info.imme_term,'1','Y'),'2','N'),l_lang),'R');
2894 Tag(x,r,'AantKerenOpgeroepenVoorArbeid',l_sick_info.no_of_calls,'B'); -- no of calls for work
2895 Tag(x,r,'IndOndAokVoldoetAanToegestanePer',l_sick_info.period_less_three,'X');-- periods less than three months
2896 Tag(x,r,'IndOndAokVoldoetAanToegestanePer',get_look_up_value('HR_NL_YES_NO',
2897 REPLACE(REPLACE(l_sick_info.period_less_three,'1','Y'),'2','N'),l_lang),'R');
2898 Tag(x,r,'NrEersteAokVanOpeenvolgendeReeks',l_sick_info.no_of_first_call,'B'); -- no of first call for work
2899 Tag(x,r,'IndDuurAokLangerDanToegestanePer',l_sick_info.work_long_all,'X'); -- code period work longer than allowed
2900 Tag(x,r,'IndDuurAokLangerDanToegestanePer',get_look_up_value('HR_NL_YES_NO',
2901 REPLACE(REPLACE(l_sick_info.work_long_all,'1','Y'),'2','N'),l_lang),'R');
2902 Tag(x,r,'AantArbeidsovereenkomstenBepaaldeTijd',l_sick_info.temp_cont,'B'); -- pno of temp contracts
2903 Tag(x,r,'OproepInvalEnZiekUitDienstMelding','_END_','B'); -- - temp work or end empl
2904 END IF;
2905 IF connected_absence_count>0 THEN -- Currently connected absences is limited to 14 absences
2906 FOR i in 1..LEAST(connected_absence_count,14) LOOP
2907 Tag(x,r,'AoPeriode','_START_','B'); -- + connected abs
2908 Tag(x,r,'DatBAo',start_date(i),'X'); -- first day
2909 Tag(x,r,'DatBAo',fnd_date.date_to_chardate(to_date(
2910 start_date(i),'YYYYMMDD')),'R');
2911 Tag(x,r,'DatEAo',end_date(i),'X'); -- last day
2912 Tag(x,r,'DatEAo',fnd_date.date_to_chardate(to_date(
2913 end_date(i),'YYYYMMDD')),'R');
2914 Tag(x,r,'AoPeriode','_END_','B'); -- - connected abs
2915 END LOOP;
2916 END IF;
2917 IF l_sick_info.contact_name IS NOT NULL
2918 OR l_sick_info.est_name IS NOT NULL
2919 OR l_sick_info.gender IS NOT NULL
2920 OR l_sick_info.contact_ph_no IS NOT NULL
2921 THEN
2922 Tag(x,r,'ContactGegevens','_START_','B'); -- + contact details - sickness
2923 Tag(x,r,'NaamContactpersoonAfd',l_sick_info.contact_name,'B'); -- name
2924 Tag(x,r,'OmschrijvingLokaleVestiging',l_sick_info.est_name,'B'); -- Establishment
2925 Tag(x,r,'Geslacht',l_sick_info.gender,'X'); -- gender
2926 Tag(x,r,'Geslacht',get_look_up_value('HR_NL_DS_GENDER',
2927 l_sick_info.gender,l_lang),'R');
2928 Tag(x,r,'TelefoonnrContactpersoonAfd',l_sick_info.contact_ph_no,'B'); -- Telephone
2929 Tag(x,r,'ContactGegevens','_END_','B');
2930 ELSIF l_abs_info.contact_per_or_dept IS NOT NULL
2931 OR l_abs_info.ph_no_contact IS NOT NULL
2932 OR l_abs_info.est_name IS NOT NULL
2933 OR l_abs_info.gender IS NOT NULL
2934 THEN
2935 Tag(x,r,'ContactGegevens','_START_','B'); -- + contact details - org
2936 Tag(x,r,'NaamContactpersoonAfd',l_abs_info.contact_per_or_dept,'B'); -- name
2937 Tag(x,r,'OmschrijvingLokaleVestiging',l_abs_info.est_name,'B'); -- Establishment
2938 Tag(x,r,'Geslacht',l_abs_info.gender,'X'); -- gender
2939 Tag(x,r,'Geslacht',get_look_up_value('HR_NL_DS_GENDER',
2940 l_abs_info.gender,l_lang),'R');
2941 Tag(x,r,'TelefoonnrContactpersoonAfd',l_abs_info.ph_no_contact,'B'); -- Telephone
2942 Tag(x,r,'ContactGegevens','_END_','B'); -- - contact details
2943 END IF;
2944 Tag(x,r,'MeldingZiekte','_END_','B'); -- - sickness details
2945 Tag(x,r,'Arbeidsverhouding','_END_','B'); -- - assignment details*
2946 Tag(x,r,'NatuurlijkPersoon','_END_','B'); -- - employee data*
2947 Tag(x,r,'AdministratieveEenheid','_END_','B'); -- - employer*
2948 Tag(x,r,'UwvZwZiekteMelding','_END_','B'); -- sickness report*
2949
2950 l_file_name := l_emp_details.emp_num||'_'||l_sick_info.dsr_st_dt||'_DSR';
2951 IF p_type in ('W','R') THEN
2952 l_file_name := l_file_name||'_'||p_type;
2953 END IF;
2954 l_file_name := l_file_name||'.xml';
2955
2956 write_file(xXMLTable, l_file_name);
2957
2958 write_report(rXMLTable);
2959
2960 hr_utility.trace('X====gen_body_xml_main=====================================X');
2961 EXCEPTION
2962 WHEN others THEN
2963 hr_utility.trace('Exception in gen_body_xml_main SQL-ERRM :'||SQLERRM);
2964 END gen_body_xml_main;
2965
2966 /**************************************************************************/
2967
2968 PROCEDURE gen_body_xml
2969 IS
2970
2971 l_payroll_action_id NUMBER;
2972 l_asg_action_id NUMBER;
2973 l_org_struct_id NUMBER := NULL;
2974 l_person_id NUMBER := NULL;
2975 l_org_id NUMBER := NULL;
2976 l_bg_id NUMBER;
2977 l_start_date DATE := NULL;
2978 l_end_date DATE := NULL;
2979 l_type VARCHAR2(1) := NULL;
2980 /**************************************************************************/
2981 CURSOR cur_get_child_act_id (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
2982 SELECT assignment_action_id ass_act_id
2983 FROM pay_assignment_actions
2984 WHERE source_action_id = c_assignment_action_id;
2985 /**************************************************************************/
2986 CURSOR get_parent_asg_ids(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
2987 SELECT locked_action_id parent_id
2988 FROM pay_action_interlocks
2989 WHERE locking_action_id = c_assignment_action_id;
2990 /**************************************************************************/
2991 CURSOR get_pact_id(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
2992 SELECT ppa.payroll_action_id
2993 FROM pay_payroll_actions ppa
2994 ,pay_assignment_actions paa
2995 WHERE paa.payroll_action_id = ppa.payroll_action_id
2996 AND paa.assignment_action_id = c_assignment_action_id;
2997 /**************************************************************************/
2998
2999 BEGIN
3000 hr_utility.trace('+====gen_body_xml==========================================+');
3001
3002 l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
3003 hr_utility.trace('TRANSFER_PAYROLL_ACTION_ID = '||l_payroll_action_id);
3004
3005 l_asg_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
3006 hr_utility.trace('TRANSFER_ACT_ID = '||l_asg_action_id);
3007
3008 OPEN get_pact_id(l_asg_action_id);
3009 FETCH get_pact_id INTO l_payroll_action_id;
3010 CLOSE get_pact_id;
3011
3012 hr_utility.trace('l_payroll_action_id = '||l_payroll_action_id);
3013 get_all_parameters (
3014 p_payroll_action_id => l_payroll_action_id
3015 ,p_org_struct_id => l_org_struct_id
3016 ,p_person_id => l_person_id
3017 ,p_org_id => l_org_id
3018 ,p_bg_id => l_bg_id
3019 ,p_start_date => l_start_date
3020 ,p_end_date => l_end_date
3021 ,p_type => l_type);
3022
3023 IF l_type = 'I' THEN
3024 hr_utility.trace('Initial Report');
3025 FOR v_child_act_id IN cur_get_child_act_id(l_asg_action_id)
3026 LOOP
3027 hr_utility.trace('Child Assignment Action ID :'||v_child_act_id.ass_act_id);
3028 gen_body_xml_main(l_type, v_child_act_id.ass_act_id);
3029 END LOOP;
3030 ELSIF l_type IN ('W','R') THEN
3031 hr_utility.trace('Withdrawal/Regeneration Report');
3032 FOR v_parent_asg_ids in get_parent_asg_ids(l_asg_action_id)
3033 LOOP
3034 hr_utility.trace('Parent Assignment Action ID :'||v_parent_asg_ids.parent_id);
3035 gen_body_xml_main(l_type, v_parent_asg_ids.parent_id);
3036 END LOOP;
3037 END IF;
3038 hr_utility.trace('X====gen_body_xml==========================================X');
3039 EXCEPTION
3040 WHEN others THEN
3041 hr_utility.trace('Exception in gen_body_xml SQL-ERRM :'||SQLERRM);
3042 END gen_body_xml;
3043
3044 END PER_NL_DSR_ARCHIVE;
3045