[Home] [Help]
PACKAGE BODY: APPS.PER_NL_WAZO_ARCHIVE
Source
1 PACKAGE BODY PER_NL_WAZO_ARCHIVE as
2 /* $Header: penlwazo.pkb 120.9.12020000.1 2012/07/17 08:56:34 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
230 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
231 --
232 IF l_start_pos = 0 THEN
233 l_delimiter := '|';
234 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
235 END IF;
236
237 IF l_start_pos <> 0 THEN
238 l_start_pos := l_start_pos + length(p_token||'=');
239 l_parameter := substr(p_parameter_string,
240 l_start_pos,
241 instr(p_parameter_string||' ',
242 l_delimiter,l_start_pos)
243 - l_start_pos);
244 IF p_segment_number IS NOT NULL THEN
245 l_parameter := ':'||l_parameter||':';
246 l_parameter := substr(l_parameter,
247 instr(l_parameter,':',1,p_segment_number)+1,
248 instr(l_parameter,':',1,p_segment_number+1) -1
249 - instr(l_parameter,':',1,p_segment_number));
250 END IF;
251 END IF;
252 RETURN l_parameter;
253 END get_parameter;
254 /**************************************************************************/
255
256
257
258
259
260 /*------------------------------------------------------------------------------
261 |Name : GET_ALL_PARAMETERS |
262 |Type : Procedure |
263 |Description: Procedure which returns all the parameters of the archive process|
264 -------------------------------------------------------------------------------*/
265
266
267 PROCEDURE get_all_parameters (p_payroll_action_id IN NUMBER
268 ,p_org_struct_id OUT NOCOPY NUMBER
269 ,p_person_id OUT NOCOPY NUMBER
270 ,p_org_id OUT NOCOPY NUMBER
271 ,p_bg_id OUT NOCOPY NUMBER
272 ,p_start_date OUT NOCOPY DATE
273 ,p_end_date OUT NOCOPY DATE
274 ,p_type OUT NOCOPY VARCHAR) IS
275
276 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
277 SELECT to_number(get_parameter(legislative_parameters,'ORG_STRUCT_ID')) org_struct_id
278 ,to_number(get_parameter(legislative_parameters,'PERSON_ID')) person_id
279 ,to_number(get_parameter(legislative_parameters,'ORG_ID')) org_id
280 ,business_group_id bg_id
281 ,start_date start_date
282 ,effective_date end_date
283 ,get_parameter(legislative_parameters,'TYPE') report_type
284 FROM pay_payroll_actions
285 WHERE payroll_action_id = p_payroll_action_id;
286
287 l_param csr_parameter_info%ROWTYPE;
288
289 BEGIN
290 OPEN csr_parameter_info (p_payroll_action_id);
291 FETCH csr_parameter_info INTO l_param;
292 CLOSE csr_parameter_info;
293
294 p_org_struct_id := l_param.org_struct_id;
295 p_person_id := l_param.person_id;
296 p_org_id := l_param.org_id;
297 p_bg_id := l_param.bg_id;
298 p_type := l_param.report_type;
299 p_start_date := l_param.start_date;
300 p_end_date := l_param.end_date;
301
302 EXCEPTION
303 WHEN others THEN
304 hr_utility.trace('Exception in get_all_parameters SQLERRM: '||SQLERRM);
305 END get_all_parameters;
306 /**************************************************************************/
307
308
309
310
311
312 /*------------------------------------------------------------------------------
313 |Name : EMP_CHECK |
314 |Type : Function |
315 |Description : Function required for valueset HR_NL_EMPLOYEE_WAZO |
316 -------------------------------------------------------------------------------*/
317 FUNCTION emp_check (p_bg_id IN NUMBER
318 ,p_org_struct_id IN NUMBER
319 ,p_org_id IN NUMBER
320 ,p_person_id IN NUMBER
321 ,p_start_date IN DATE
322 ,p_end_date IN DATE) RETURN NUMBER IS
323
324 CURSOR csr_org_check(c_bg_id IN NUMBER
325 ,c_org_struct_id IN NUMBER
326 ,c_org_id IN NUMBER
327 ,c_person_id IN NUMBER
328 ,c_start_date IN DATE
329 ,c_end_date IN DATE) IS
330 SELECT 1
331 FROM per_all_assignments_f paa
332 ,per_assignment_status_types past
333 ,per_all_people_f pap
334 ,per_org_structure_versions posv
335 ,per_absence_attendances pab
336 ,per_absence_attendance_types paat
337 WHERE posv.organization_structure_id = c_org_struct_id
338 AND posv.date_from <= c_start_date
339 AND nvl (posv.date_to
340 ,hr_general.end_of_time) >= c_end_date
341 AND (
342 paa.organization_id IN
343 (
344 (
345 SELECT pose.organization_id_child
346 FROM per_org_structure_elements pose
347 WHERE pose.org_structure_version_id = posv.org_structure_version_id
348 START WITH pose.organization_id_parent = c_org_id
349 CONNECT BY PRIOR organization_id_child = organization_id_parent
350 )
351 UNION
352 (
353 SELECT c_org_id
354 FROM dual
355 )
356 )
357 OR nvl (paa.establishment_id,- 1) = c_org_id
358 )
359 AND paa.person_id = c_person_id
360 AND paat.absence_category = 'WAZO'
361 AND pab.abs_information_category = 'NL_WAZO'
362 AND pab.business_group_id = pap.business_group_id
363 AND pab.date_start BETWEEN c_start_date
364 AND c_end_date
365 AND pab.absence_attendance_type_id = paat.absence_attendance_type_id
366 AND pab.business_group_id = paat.business_group_id
367 AND paa.business_group_id = c_bg_id
368 AND pap.person_id = c_person_id
369 AND pap.business_group_id = paa.business_group_id;
370 /**************************************************************************/
371 CURSOR csr_org_struct_check(c_bg_id IN NUMBER
372 ,c_org_struct_id IN NUMBER
373 ,c_org_id IN NUMBER
374 ,c_person_id IN NUMBER
375 ,c_start_date IN DATE
376 ,c_end_date IN DATE) IS
377 SELECT 1
378 FROM per_all_assignments_f paa
379 ,per_all_people_f pap
380 ,per_org_structure_versions posv
381 ,per_absence_attendances pab
382 ,per_absence_attendance_types paat
383 WHERE posv.organization_structure_id = c_org_struct_id
384 AND posv.date_from <= c_start_date
385 AND nvl (posv.date_to
386 ,hr_general.end_of_time) >= c_end_date
387 AND paa.person_id = c_person_id
388 AND paa.business_group_id = c_bg_id
389 AND pap.person_id = paa.person_id
390 AND pap.business_group_id = paa.business_group_id
391 AND pab.business_group_id = pap.business_group_id
392 AND pab.person_id = pap.person_id
393 AND pab.date_start BETWEEN c_start_date
394 AND c_end_date
395 AND pab.absence_attendance_type_id = paat.absence_attendance_type_id
396 AND pab.business_group_id = paat.business_group_id
397 AND paat.absence_category = 'WAZO'
398 AND pab.abs_information_category = 'NL_WAZO'
399 AND (
400 hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
401 ,paa.organization_id) IS NOT NULL
402 OR per_nl_wazo_archive.org_check (pap.business_group_id
403 ,NULL
404 ,nvl (paa.establishment_id,- 1)
405 ,c_start_date
406 ,c_end_date) = 1
407 )
408 AND paa.organization_id IN
409 (
410 SELECT pose.organization_id_parent
411 FROM per_org_structure_elements pose
412 WHERE posv.org_structure_version_id = pose.org_structure_version_id
413 UNION
414 SELECT pose.organization_id_child
415 FROM per_org_structure_elements pose
416 WHERE posv.org_structure_version_id = pose.org_structure_version_id
417 );
418 /**************************************************************************/
419 l_return_val NUMBER := 0;
420 l_start_date DATE;
421 l_end_date DATE;
422
423 BEGIN
424 hr_utility.trace('+====emp_check============================================+');
425 IF p_org_id is not NULL THEN
426 BEGIN
427 OPEN csr_org_check(p_bg_id
428 ,p_org_struct_id
429 ,p_org_id
430 ,p_person_id
431 ,p_start_date
432 ,p_end_date );
433 FETCH csr_org_check INTO l_return_val;
434 CLOSE csr_org_check;
435
436 EXCEPTION
437 WHEN TOO_MANY_ROWS THEN
438 l_return_val := 1;
439 WHEN NO_DATA_FOUND THEN
440 null;
441 WHEN OTHERS THEN
442 hr_utility.trace('Exception in emp_check org. SQLERRM : '||SQLERRM);
443 END;
444 hr_utility.trace('l_return_val : '||l_return_val);
445 ELSIF p_org_struct_id is not NULL THEN
446 BEGIN
447 OPEN csr_org_struct_check(p_bg_id
448 ,p_org_struct_id
449 ,p_org_id
450 ,p_person_id
451 ,p_start_date
452 ,p_end_date );
453 FETCH csr_org_struct_check INTO l_return_val;
454 CLOSE csr_org_struct_check;
455 EXCEPTION
456 WHEN TOO_MANY_ROWS THEN
457 l_return_val := 1;
458 WHEN NO_DATA_FOUND THEN
459 NULL;
460 WHEN OTHERS THEN
461 hr_utility.trace('Exception in emp_check org_struct. SQLERRM : '||SQLERRM);
462 END;
463 hr_utility.trace('l_return_val : '||l_return_val);
464 END IF;
465 hr_utility.trace('X====emp_check============================================X');
466 RETURN l_return_val;
467 EXCEPTION
468 WHEN OTHERS THEN
469 hr_utility.trace('Exception in emp_check SQL-ERRM : '||SQLERRM);
470 END emp_check;
471 /**************************************************************************/
472
473
474
475
476
477 /*------------------------------------------------------------------------------
478 |Name : ORG_CHECK |
479 |Type : Function |
480 |Description : Function required for valueset HR_NL_EMPLOYER_DSR |
481 -------------------------------------------------------------------------------*/
482 FUNCTION org_check (p_bg_id IN NUMBER
483 ,p_org_struct_id IN NUMBER
484 ,p_org_id IN NUMBER
485 ,p_start_date IN DATE
486 ,p_end_date IN DATE)
487 RETURN NUMBER IS
488
489 CURSOR csr_org_struct_check (c_bg_id IN NUMBER
490 ,c_org_id IN NUMBER) IS
491
492 SELECT 1
493 FROM hr_organization_units hou
494 WHERE hou.business_group_id = c_bg_id
495 AND hou.organization_id = c_org_id
496 AND EXISTS
497 (
498 SELECT 1
499 FROM hr_all_organization_units hou1
500 ,hr_organization_information hoi1
501 WHERE hou1.business_group_id = c_bg_id
502 AND hoi1.org_information_context = 'NL_LE_TAX_DETAILS'
503 AND hoi1.org_information1 IS NOT NULL
504 AND hoi1.org_information2 IS NOT NULL
505 AND hou1.organization_id = hoi1.organization_id
506 AND hou1.organization_id = hou.organization_id
507 UNION
508 SELECT 1
509 FROM hr_all_organization_units hou2
510 ,hr_organization_information hoi2
511 WHERE hou2.business_group_id = c_bg_id
512 AND hoi2.org_information_context = 'NL_ORG_INFORMATION'
513 AND hoi2.org_information4 IS NOT NULL
514 AND hoi2.org_information3 IS NOT NULL
515 AND hou2.organization_id = hoi2.organization_id
516 AND hou2.organization_id = hou.organization_id
517 );
518 /**************************************************************************/
519 CURSOR csr_org_check (c_bg_id IN NUMBER
520 ,c_org_id IN NUMBER
521 ,c_org_struct_id IN NUMBER
522 ,c_start_date IN DATE
523 ,c_end_date IN DATE) IS
524
525 SELECT 1
526 FROM hr_organization_units hou
527 WHERE hou.organization_id = c_org_id
528 AND hou.business_group_id = c_bg_id
529 AND EXISTS
530 (
531 SELECT 1
532 FROM hr_organization_units hou1
533 ,hr_organization_information hoi1
534 WHERE hoi1.org_information_context = 'NL_ORG_INFORMATION'
535 AND hou1.business_group_id = c_bg_id
536 AND hou1.organization_id = hou.organization_id
537 AND hou1.organization_id = hoi1.organization_id
538 AND hoi1.org_information4 IS NOT NULL
539 AND hoi1.org_information3 IS NOT NULL
540 AND hou1.organization_id IN
541 (
542 SELECT pose.organization_id_parent
543 FROM per_org_structure_elements pose
544 ,per_org_structure_versions posv
545 WHERE posv.org_structure_version_id = pose.org_structure_version_id
546 AND posv.organization_structure_id = c_org_struct_id
547 AND posv.date_from <= c_start_date
548 AND nvl (posv.date_to
549 ,hr_general.end_of_time) >= c_end_date
550 UNION
551 SELECT pose.organization_id_child
552 FROM per_org_structure_elements pose
553 ,per_org_structure_versions posv
554 WHERE posv.org_structure_version_id = pose.org_structure_version_id
555 AND posv.organization_structure_id = c_org_struct_id
556 AND posv.date_from <= c_start_date
557 AND nvl (posv.date_to
558 ,hr_general.end_of_time) >= c_end_date
559 )
560 UNION
561 SELECT 1
562 FROM hr_organization_units hou2
563 ,hr_organization_information hoi2
564 WHERE hoi2.org_information_context = 'NL_LE_TAX_DETAILS'
565 AND hou2.business_group_id = c_bg_id
566 AND hou2.organization_id = hou.organization_id
567 AND hou2.organization_id = hoi2.organization_id
568 AND hoi2.org_information1 IS NOT NULL
569 AND hoi2.org_information2 IS NOT NULL
570 AND hou2.organization_id IN
571 (
572 SELECT pose.organization_id_parent
573 FROM per_org_structure_elements pose
574 ,per_org_structure_versions posv
575 WHERE posv.org_structure_version_id = pose.org_structure_version_id
576 AND posv.organization_structure_id = c_org_struct_id
577 AND posv.date_from <= c_start_date
578 AND nvl (posv.date_to
579 ,hr_general.end_of_time) >= c_end_date
580 UNION
581 SELECT pose.organization_id_child
582 FROM per_org_structure_elements pose
583 ,per_org_structure_versions posv
584 WHERE posv.org_structure_version_id = pose.org_structure_version_id
585 AND posv.organization_structure_id = c_org_struct_id
586 AND posv.date_from <= c_start_date
587 AND nvl (posv.date_to
588 ,hr_general.end_of_time) >= c_end_date
589 )
590 );
591 /**************************************************************************/
592
593 l_return_val NUMBER := 0;
594 l_start_date DATE;
595 l_end_date DATE;
596
597 BEGIN
598 --hr_utility.trace('+====org_check=============================================+');
599 IF p_org_struct_id is NULL THEN
600 BEGIN
601 OPEN csr_org_struct_check (p_bg_id,p_org_id);
602 FETCH csr_org_struct_check INTO l_return_val;
603 CLOSE csr_org_struct_check;
604
605 EXCEPTION
606 WHEN TOO_MANY_ROWS THEN
607 l_return_val := 1;
608 WHEN NO_DATA_FOUND THEN
609 NULL;
610 WHEN OTHERS THEN
611 hr_utility.trace('Exception in org_check org_struct. SQLERRM : '||SQLERRM);
612 END;
613 hr_utility.trace('l_return_val : '||l_return_val);
614 ELSE
615 BEGIN
616 OPEN csr_org_check (p_bg_id,p_org_id,p_org_struct_id,p_start_date,p_end_date);
617 FETCH csr_org_check INTO l_return_val;
618 CLOSE csr_org_check;
619
620 EXCEPTION
621 WHEN TOO_MANY_ROWS THEN
622 l_return_val := 1;
623 WHEN NO_DATA_FOUND THEN
624 NULL;
625 WHEN OTHERS THEN
626 hr_utility.trace('Exception in org_check org_struct1. SQLERRM : '||SQLERRM);
627 END;
628 hr_utility.trace('l_return_val : '||l_return_val);
629 END IF;
630 RETURN l_return_val;
631 --hr_utility.trace('X====org_check=============================================X');
632 EXCEPTION
633 WHEN OTHERS THEN
634 hr_utility.trace('Exception in org_check SQL-ERRM : '||SQLERRM);
635 END org_check;
636 /**************************************************************************/
637
638 /*--------------------------------------------------------------------
639 |Name : RANGE_CODE |
640 |Type : Procedure |
641 |Description: This procedure returns an sql string to select a range |
642 | of assignments eligible for reporting |
643 ----------------------------------------------------------------------*/
644
645
646 PROCEDURE range_code (pactid IN NUMBER
647 ,sqlstr OUT NOCOPY VARCHAR2) is
648 BEGIN
649 hr_utility.trace('+====range_code============================================+');
650 sqlstr := 'SELECT DISTINCT person_id
651 FROM per_all_people_f pap
652 ,pay_payroll_actions ppa
653 WHERE ppa.payroll_action_id = :payroll_action_id
654 AND ppa.business_group_id = pap.business_group_id
655 ORDER BY pap.person_id';
656 hr_utility.trace('X====range_code============================================X');
657 EXCEPTION
658 WHEN OTHERS THEN
659 -- Return cursor that selects no rows
660 hr_utility.trace('Exception in range_code SQL-ERRM : '||SQLERRM);
661 sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
662 END range_code;
663
664 /**************************************************************************/
665
666 PROCEDURE initialization_code(p_action_context_id IN NUMBER) IS
667
668 CURSOR c_usr_tab_col IS
669 SELECT put.user_table_id
670 ,puc.user_column_id
671 FROM pay_user_tables put
672 ,pay_user_columns puc
673 WHERE put.user_table_id = puc.user_table_id
674 AND put.legislation_code = puc.legislation_code
675 AND put.user_table_name = 'NL_DIACRITICAL_MARKS'
676 AND put.legislation_code = 'NL';
677 /**************************************************************************/
678 CURSOR c_src_dest_str(p_user_column_id IN NUMBER
679 , p_user_table_id IN NUMBER
680 , p_business_group_id IN NUMBER
681 , p_start_date IN DATE) IS
682 SELECT DISTINCT
683 UPPER (purf.row_low_range_or_name) Source
684 ,UPPER (pucif.value) Target
685 FROM pay_user_column_instances_f pucif
686 ,pay_user_rows_f purf
687 WHERE pucif.user_column_id = p_user_column_id
688 AND purf.user_table_id = p_user_table_id
689 AND pucif.user_row_id = purf.user_row_id
690 AND pucif.business_group_id = purf.business_group_id
691 AND pucif.business_group_id = p_business_group_id
692 AND p_start_date BETWEEN pucif.EFFECTIVE_START_DATE AND pucif.EFFECTIVE_END_DATE
693 AND p_start_date BETWEEN purf.EFFECTIVE_START_DATE AND purf.EFFECTIVE_END_DATE;
694 /**************************************************************************/
695 CURSOR c_bg_stdate(p_payroll_action_id IN NUMBER) IS
696 SELECT business_group_id bg_id
697 ,start_date start_date
698 FROM pay_payroll_actions
699 WHERE payroll_action_id = p_payroll_action_id;
700 /**************************************************************************/
701 l_user_table_id NUMBER;
702 l_user_column_id NUMBER;
703 l_bg_id NUMBER;
704 l_start_date DATE;
705
706 BEGIN
707 hr_utility.trace('+====initialization_code===================================+');
708 IF Instr ('a', COMPOSE('a'|| UNISTR('\0301')) ) = 0 THEN
709 OPEN c_bg_stdate(p_action_context_id);
710 FETCH c_bg_stdate INTO l_bg_id,l_start_date;
711 CLOSE c_bg_stdate;
712
713 OPEN c_usr_tab_col;
714 FETCH c_usr_tab_col INTO l_user_table_id, l_user_column_id;
715 CLOSE c_usr_tab_col;
716
717 FOR I in c_src_dest_str(l_user_column_id, l_user_table_id, l_bg_id, l_start_date)
718 LOOP
719 g_SOURCE := g_SOURCE||upper(I.Source)||lower(I.Source);
720 g_TARGET := g_TARGET||upper(I.Target)||lower(I.Target);
721 END LOOP;
722
723 hr_utility.trace('g_SOURCE : '||g_SOURCE);
724 hr_utility.trace('g_TARGET : '||g_TARGET);
725 END IF;
726 hr_utility.trace('X====initialization_code===================================X');
727 EXCEPTION
728 WHEN OTHERS THEN
729 hr_utility.trace('Exception in init_code SQL-ERRM : '||SQLERRM);
730 END initialization_code;
731 /**************************************************************************/
732 /*--------------------------------------------------------------------
733 |Name : ASSIGNMENT_ACTION_CODE |
734 |Type : Procedure |
735 |Description: This procedure further filters which assignments are |
736 | eligible for reporting |
737 ----------------------------------------------------------------------*/
738
739
740 PROCEDURE assignment_action_code (p_payroll_action_id IN NUMBER
741 ,p_start_person_id IN NUMBER
742 ,p_end_person_id IN NUMBER
743 ,p_chunk IN NUMBER) IS
744
745 CURSOR csr_get_asg_person (p_person_id IN NUMBER
746 ,p_start_person_id IN NUMBER
747 ,p_end_person_id IN NUMBER
748 ,p_payroll_action_id IN NUMBER
749 ,p_start_date IN DATE
750 ,p_end_date IN DATE) is
751 SELECT DISTINCT
752 paa.assignment_id assignment_id
753 FROM per_absence_attendance_types paat
754 ,per_absence_attendances pab
755 ,pay_payroll_actions ppa
756 ,per_all_assignments_f paa
757 ,per_all_people_f pap
758 WHERE paat.absence_category = 'WAZO'
759 AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
760 AND paat.business_group_id = pab.business_group_id
761 AND pab.date_start BETWEEN p_start_date
762 AND p_end_date
763 AND pab.business_group_id = pap.business_group_id
764 AND pap.person_id = p_person_id
765 AND p_person_id BETWEEN p_start_person_id
766 AND p_end_person_id
767 AND pap.person_id = paa.person_id
768 AND pab.person_id = pap.person_id
769 AND paa.business_group_id = pap.business_group_id
770 AND paa.assignment_id = pab.abs_information1
771 AND pab.abs_information_category = 'NL_WAZO'
772 AND pap.business_group_id = ppa.business_group_id
773 AND ppa.payroll_action_id = p_payroll_action_id;
774 /**************************************************************************/
775
776 CURSOR csr_get_asg_org (p_org_id IN NUMBER
777 ,p_org_struct_id IN NUMBER
778 ,p_start_person_id IN NUMBER
779 ,p_end_person_id IN NUMBER
780 ,p_payroll_action_id IN NUMBER
781 ,p_start_date IN DATE
782 ,p_end_date IN DATE) IS
783 SELECT DISTINCT
784 paa.assignment_id assignment_id
785 FROM per_absence_attendance_types paat
786 ,per_absence_attendances pab
787 ,pay_payroll_actions ppa
788 ,per_org_structure_versions posv
789 ,per_all_assignments_f paa
790 ,per_all_people_f pap
791 WHERE posv.organization_structure_id = p_org_struct_id
792 AND posv.date_from <= p_end_date
793 AND nvl (posv.date_to,hr_general.end_of_time) >= p_start_date
794 AND (
795 paa.organization_id IN
796 (
797 (
798 SELECT pose.organization_id_child
799 FROM per_org_structure_elements pose
800 WHERE pose.org_structure_version_id = posv.org_structure_version_id
801 START WITH pose.organization_id_parent = p_org_id
802 CONNECT BY PRIOR organization_id_child = organization_id_parent
803 )
804 UNION
805 (
806 SELECT p_org_id
807 FROM dual
808 )
809 )
810 OR nvl (paa.establishment_id,- 1) = p_org_id
811 )
812 AND pap.person_id = paa.person_id
813 AND paa.person_id BETWEEN p_start_person_id
814 AND p_end_person_id
815 AND paa.business_group_id = pap.business_group_id
816 AND pab.person_id = pap.person_id
817 AND pab.date_start BETWEEN p_start_date
818 AND p_end_date
819 AND paat.absence_category = 'WAZO'
820 AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
821 AND paat.business_group_id = pab.business_group_id
822 AND pab.business_group_id = pap.business_group_id
823 AND paa.assignment_id = pab.abs_information1
824 AND pab.abs_information_category = 'NL_WAZO'
825 AND pap.business_group_id = ppa.business_group_id
826 AND ppa.payroll_action_id = p_payroll_action_id;
827 /**************************************************************************/
828
829 CURSOR csr_get_asg_hier (p_org_struct_id IN NUMBER
830 ,p_start_person_id IN NUMBER
831 ,p_end_person_id IN NUMBER
832 ,p_payroll_action_id IN NUMBER
833 ,p_start_date IN DATE
834 ,p_end_date IN DATE) IS
835
836 SELECT DISTINCT
837 paa.assignment_id assignment_id
838 FROM per_absence_attendance_types paat
839 ,per_absence_attendances pab
840 ,per_all_assignments_f paa
841 ,per_all_people_f pap
842 ,pay_payroll_actions ppa
843 ,per_org_structure_versions posv
844 WHERE posv.organization_structure_id = p_org_struct_id
845 AND pap.person_id BETWEEN p_start_person_id
846 AND p_end_person_id
847 AND pap.business_group_id = ppa.business_group_id
848 AND ppa.payroll_action_id = p_payroll_action_id
849 AND paat.absence_category = 'WAZO'
850 AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
851 AND paat.business_group_id = pab.business_group_id
852 AND pab.date_start BETWEEN p_start_date
853 AND p_end_date
854 AND paa.assignment_id = pab.abs_information1
855 AND pab.abs_information_category = 'NL_WAZO'
856 AND pab.business_group_id = pap.business_group_id
857 AND pab.person_id = pap.person_id
858 AND pap.person_id = paa.person_id
859 AND (
860 hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
861 ,paa.organization_id) IS NOT NULL
862 OR per_nl_wazo_archive.org_check (pap.business_group_id
863 ,NULL
864 ,nvl (paa.establishment_id,- 1)
865 ,p_start_date
866 ,p_end_date) = 1
867 )
868 AND paa.organization_id IN
869 (
870 SELECT pose.organization_id_parent
871 FROM per_org_structure_elements pose
872 WHERE posv.org_structure_version_id = pose.org_structure_version_id
873 UNION
874 SELECT pose.organization_id_child
875 FROM per_org_structure_elements pose
876 WHERE posv.org_structure_version_id = pose.org_structure_version_id
877 );
878 /**************************************************************************/
879 --Withdrawal and Regeneration
880 CURSOR csr_get_asg_person_archive (p_person_id IN NUMBER
881 ,p_start_person_id IN NUMBER
882 ,p_end_person_id IN NUMBER
883 ,p_payroll_action_id IN NUMBER
884 ,p_start_date IN DATE
885 ,p_end_date IN DATE
886 ,p_type IN VARCHAR2) IS
887 SELECT DISTINCT
888 paa.assignment_id assignment_id
889 FROM pay_action_information pai
890 ,pay_payroll_actions ppa
891 ,per_all_assignments_f paa
892 ,per_all_people_f pap
893 WHERE pai.action_context_type = 'AAP'
894 AND pai.action_information_category = 'NL_WAZO_ABS_INFO'
895 AND pai.action_information4 = 'WAZO'
896 AND pai.action_information29 = 'NL_WAZO'
897 AND (
898 (
899 pai.action_information30 = 'N'
900 AND p_type = 'W'
901 )
902 OR (
903 p_type = 'R'
904 )
905 )
906 AND (
907 fnd_date.canonical_to_date(pai.action_information2) BETWEEN p_start_date
908 AND p_end_date
909 )
910 AND paa.assignment_id = pai.assignment_id
911 AND pap.person_id = p_person_id
912 AND p_person_id BETWEEN p_start_person_id
913 AND p_end_person_id
914 AND pap.person_id = paa.person_id
915 AND paa.business_group_id = pap.business_group_id
916 AND pap.business_group_id = ppa.business_group_id
917 AND ppa.payroll_action_id = p_payroll_action_id;
918
919 /**************************************************************************/
920 CURSOR csr_get_asg_org_archive (p_org_id IN NUMBER
921 ,p_org_struct_id IN NUMBER
922 ,p_start_person_id IN NUMBER
923 ,p_end_person_id IN NUMBER
924 ,p_payroll_action_id IN NUMBER
925 ,p_start_date IN DATE
926 ,p_end_date IN DATE
927 ,p_type IN VARCHAR2) IS
928 SELECT DISTINCT
929 paa.assignment_id assignment_id
930 FROM pay_action_information pai
931 ,pay_payroll_actions ppa
932 ,per_org_structure_versions posv
933 ,per_all_assignments_f paa
934 ,per_all_people_f pap
935 WHERE posv.organization_structure_id = p_org_struct_id
936 AND posv.date_from <= p_end_date
937 AND nvl (posv.date_to
938 ,hr_general.end_of_time) >= p_start_date
939 AND (
940 paa.organization_id IN
941 (
942 (
943 SELECT pose.organization_id_child
944 FROM per_org_structure_elements pose
945 WHERE pose.org_structure_version_id = posv.org_structure_version_id
946 START WITH pose.organization_id_parent = p_org_id
947 CONNECT BY PRIOR organization_id_child = organization_id_parent
948 )
949 UNION
950 (
951 SELECT p_org_id
952 FROM dual
953 )
954 )
955 OR nvl (paa.establishment_id,- 1) = p_org_id
956 )
957 AND pap.person_id = paa.person_id
958 AND paa.person_id BETWEEN p_start_person_id
959 AND p_end_person_id
960 AND paa.business_group_id = pap.business_group_id
961 AND pai.action_context_type = 'AAP'
962 AND pai.action_information_category = 'NL_WAZO_ABS_INFO'
963 AND pai.action_information4 = 'WAZO'
964 AND pai.action_information29 = 'NL_WAZO'
965 AND (
966 (
967 pai.action_information30 = 'N'
968 AND p_type = 'W'
969 )
970 OR (
971 p_type = 'R'
972 )
973 )
974 AND (
975 fnd_date.canonical_to_date(pai.action_information2) BETWEEN p_start_date
976 AND p_end_date
977 )
978 AND paa.assignment_id = pai.assignment_id
979 AND pap.business_group_id = ppa.business_group_id
980 AND ppa.payroll_action_id = p_payroll_action_id;
981 /**************************************************************************/
982 CURSOR csr_get_asg_hier_archive (p_org_struct_id IN NUMBER
983 ,p_start_person_id IN NUMBER
984 ,p_end_person_id IN NUMBER
985 ,p_payroll_action_id IN NUMBER
986 ,p_start_date IN DATE
987 ,p_end_date IN DATE
988 ,p_type IN VARCHAR2) IS
989 SELECT DISTINCT
990 paa.assignment_id assignment_id
991 FROM pay_action_information pai
992 ,per_all_assignments_f paa
993 ,per_all_people_f pap
994 ,pay_payroll_actions ppa
995 ,per_org_structure_versions posv
996 WHERE posv.organization_structure_id = p_org_struct_id
997 AND pap.person_id BETWEEN p_start_person_id
998 AND p_end_person_id
999 AND pap.business_group_id = ppa.business_group_id
1000 AND ppa.payroll_action_id = p_payroll_action_id
1001 AND pai.action_context_type = 'AAP'
1002 AND pai.action_information_category = 'NL_WAZO_ABS_INFO'
1003 AND pai.action_information4 = 'WAZO'
1004 AND pai.action_information29 = 'NL_WAZO'
1005 AND (
1006 (
1007 pai.action_information30 = 'N'
1008 AND p_type = 'W'
1009 )
1010 OR (
1011 p_type = 'R'
1012 )
1013 )
1014 AND (
1015 fnd_date.canonical_to_date(pai.action_information2) BETWEEN p_start_date
1016 AND p_end_date
1017 )
1018 AND paa.assignment_id = pai.assignment_id
1019 AND ppa.payroll_action_id = p_payroll_action_id
1020 AND pap.business_group_id = ppa.business_group_id
1021 AND pap.person_id = paa.person_id
1022 AND (
1023 hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
1024 ,paa.organization_id) IS NOT NULL
1025 OR per_nl_wazo_archive.org_check (pap.business_group_id
1026 ,NULL
1027 ,nvl (paa.establishment_id,- 1)
1028 ,p_start_date
1029 ,p_end_date) = 1
1030 )
1031 AND paa.organization_id IN
1032 (
1033 SELECT pose.organization_id_parent
1034 FROM per_org_structure_elements pose
1035 WHERE posv.org_structure_version_id = pose.org_structure_version_id
1036 UNION
1037 SELECT pose.organization_id_child
1038 FROM per_org_structure_elements pose
1039 WHERE posv.org_structure_version_id = pose.org_structure_version_id
1040 );
1041 /**************************************************************************/
1042
1043 l_org_struct_id NUMBER :=NULL;
1044 l_person_id NUMBER :=NULL;
1045 l_org_id NUMBER :=NULL;
1046 l_bg_id NUMBER;
1047 l_asg_act_id NUMBER;
1048 l_start_date DATE;
1049 l_end_date DATE;
1050 l_type VARCHAR2(1) :=NULL;
1051 /**************************************************************************/
1052 PROCEDURE ins_assg_act (p_assignid IN NUMBER
1053 ,p_pactid IN NUMBER
1054 ,p_chunk IN NUMBER)
1055 IS
1056 l_asg_act_id NUMBER;
1057 BEGIN
1058 SELECT pay_assignment_actions_s.NEXTVAL INTO l_asg_act_id FROM dual;
1059 hr_nonrun_asact.insact(
1060 lockingactid => l_asg_act_id
1061 ,assignid => p_assignid
1062 ,pactid => p_pactid
1063 ,chunk => p_chunk);
1064 EXCEPTION
1065 WHEN others THEN
1066 hr_utility.trace('Exception in ins_assg_act SQL-ERRM : '||SQLERRM);
1067 END ins_assg_act;
1068 /**************************************************************************/
1069
1070
1071 BEGIN
1072 hr_utility.trace('+====assignment_action_code================================+');
1073 hr_utility.trace('Payroll_action_id/Chunk: '||p_payroll_action_id||' / '||p_chunk );
1074 hr_utility.trace('Start/End Person ID : '||p_start_person_id||' / '||p_end_person_id );
1075
1076 get_all_parameters (
1077 p_payroll_action_id => p_payroll_action_id
1078 ,p_org_struct_id => l_org_struct_id
1079 ,p_person_id => l_person_id
1080 ,p_org_id => l_org_id
1081 ,p_bg_id => l_bg_id
1082 ,p_start_date => l_start_date
1083 ,p_end_date => l_end_date
1084 ,p_type => l_type);
1085
1086
1087 IF l_person_id is not NULL THEN
1088 hr_utility.trace(l_type||' Person selected ' ||l_person_id||' '||p_start_person_id||' '||p_end_person_id);
1089 IF l_type = 'I' THEN
1090 FOR v_csr_get_asg_person IN
1091 csr_get_asg_person(l_person_id, p_start_person_id, p_end_person_id
1092 , p_payroll_action_id, l_start_date, l_end_date)
1093 LOOP
1094 ins_assg_act(v_csr_get_asg_person.assignment_id,p_payroll_action_id,p_chunk);
1095 END LOOP;
1096 ELSIF l_type IN ('W','R') THEN
1097 FOR v_csr_get_asg_person IN
1098 csr_get_asg_person_archive(l_person_id, p_start_person_id, p_end_person_id
1099 , p_payroll_action_id, l_start_date, l_end_date,l_type)
1100 LOOP
1101 ins_assg_act(v_csr_get_asg_person.assignment_id,p_payroll_action_id,p_chunk);
1102 END LOOP;
1103 END IF;
1104 -----------------------------------------------
1105 ELSIF l_org_id is not NULL THEN
1106 hr_utility.trace(l_type||' Org selected ' ||l_org_id);
1107 IF l_type = 'I' THEN
1108 FOR v_csr_get_asg_org IN
1109 csr_get_asg_org(l_org_id, l_org_struct_id
1110 , p_start_person_id, p_end_person_id, p_payroll_action_id
1111 , l_start_date, l_end_date)
1112 LOOP
1113 ins_assg_act(v_csr_get_asg_org.assignment_id,p_payroll_action_id,p_chunk);
1114 END LOOP;
1115 ELSIF l_type IN ('W','R') THEN
1116 FOR v_csr_get_asg_org IN
1117 csr_get_asg_org_archive(l_org_id, l_org_struct_id
1118 , p_start_person_id, p_end_person_id, p_payroll_action_id
1119 , l_start_date, l_end_date,l_type)
1120 LOOP
1121 ins_assg_act(v_csr_get_asg_org.assignment_id,p_payroll_action_id,p_chunk);
1122 END LOOP;
1123 END IF;
1124 -----------------------------------------------
1125 ELSIF l_org_struct_id is not NULL THEN
1126 hr_utility.trace(l_type||' Hierarchy selected ' ||l_org_struct_id);
1127 IF l_type = 'I' THEN
1128 FOR v_csr_get_asg_hier IN
1129 csr_get_asg_hier(l_org_struct_id, p_start_person_id, p_end_person_id
1130 , p_payroll_action_id, l_start_date, l_end_date)
1131 LOOP
1132 ins_assg_act(v_csr_get_asg_hier.assignment_id,p_payroll_action_id,p_chunk);
1133 END LOOP;
1134 ELSIF l_type IN ('W','R') THEN
1135 FOR v_csr_get_asg_hier IN
1136 csr_get_asg_hier_archive(l_org_struct_id, p_start_person_id, p_end_person_id
1137 , p_payroll_action_id, l_start_date, l_end_date,l_type)
1138 LOOP
1139 ins_assg_act(v_csr_get_asg_hier.assignment_id,p_payroll_action_id,p_chunk);
1140 END LOOP;
1141 END IF;
1142 END IF;
1143 hr_utility.trace('X====assignment_action_code================================X');
1144 EXCEPTION
1145 WHEN others THEN
1146 hr_utility.trace('Exception in assignment_action_code SQL-ERRM : '||SQLERRM);
1147 END assignment_action_code;
1148
1149
1150
1151
1152 /*-------------------------------------------------------------------------------
1153 |Name : ARCHIVE_CODE |
1154 |Type : Procedure |
1155 |Description : Archival code |
1156 -------------------------------------------------------------------------------*/
1157
1158
1159 PROCEDURE archive_code (p_assignment_action_id IN NUMBER
1160 ,p_effective_date IN DATE) IS
1161
1162 /**************************************************************************/
1163 CURSOR csr_get_org_info(p_assignment_id IN NUMBER
1164 , p_org_struct_id IN NUMBER
1165 , p_abs_start_date IN DATE) IS
1166 SELECT hou.organization_id org_id
1167 ,hou.name org_name
1168 ,hoi.org_information4 tax_reg
1169 FROM per_all_assignments_f pas
1170 ,hr_organization_units hou
1171 ,hr_organization_information hoi
1172 ,per_org_structure_versions posv
1173 WHERE posv.organization_structure_id = p_org_struct_id
1174 AND p_abs_start_date BETWEEN posv.date_from
1175 AND nvl (posv.date_to
1176 ,hr_general.end_of_time)
1177 AND pas.assignment_id = p_assignment_id
1178 AND hou.organization_id = hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
1179 ,pas.organization_id)
1180 AND hoi.organization_id = hou.organization_id
1181 AND hoi.org_information_context = 'NL_ORG_INFORMATION';
1182 /**************************************************************************/
1183 CURSOR csr_get_leg_info(p_assignment_id IN NUMBER) IS
1184 SELECT hou.organization_id org_id
1185 ,hou.name org_name
1186 ,hoi.org_information1 tax_reg
1187 FROM per_all_assignments_f pas
1188 ,hr_organization_units hou
1189 ,hr_organization_information hoi
1190 WHERE pas.assignment_id = p_assignment_id
1191 AND hou.organization_id = pas.establishment_id
1192 AND hoi.organization_id = hou.organization_id
1193 AND hoi.org_information_context = 'NL_LE_TAX_DETAILS';
1194 /**************************************************************************/
1195 CURSOR csr_get_abs_details (p_assignment_action_id IN NUMBER
1196 , p_start_date IN DATE
1197 , p_end_date IN DATE) IS
1198 SELECT paa.assignment_id assignment_id
1199 ,pas.person_id person_id
1200 ,pab.absence_attendance_type_id abs_att_type_id
1201 ,pab.business_group_id busi_group_id
1202 ,pab.absence_attendance_id abs_att_id
1203 ,pab.date_end end_date
1204 ,pab.date_start start_date
1205 ,pab.abs_information1 ass_id
1206 ,pab.abs_information2 sick_ref
1207 ,decode (pab.abs_information3,'Y','1','N','2') code_pymt
1208 ,pab.abs_information4 reason
1209 ,decode (pab.abs_information5,'Y','1','N','2') spl_sit
1210 ,to_char (fnd_date.canonical_to_date(pab.abs_information6),'YYYYMMDD') est_dob
1211 ,to_char (pab.date_start,'YYYYMMDD') wazo_st_dt
1212 FROM per_absence_attendance_types paat
1213 ,per_absence_attendances pab
1214 ,pay_assignment_actions paa
1215 ,per_all_assignments_f pas
1216 WHERE paat.absence_category = 'WAZO'
1217 AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
1218 AND paat.business_group_id = pab.business_group_id
1219 AND pab.date_start BETWEEN p_start_date
1220 AND p_end_date
1221 AND pab.date_start BETWEEN pas.effective_start_date
1222 AND pas.effective_end_date
1223 AND pab.business_group_id = pas.business_group_id
1224 AND paa.assignment_action_id = p_assignment_action_id
1225 AND pas.assignment_id = paa.assignment_id
1226 AND pab.abs_information1 = pas.assignment_id
1227 AND pab.person_id = pas.person_id
1228 AND pab.abs_information_category = 'NL_WAZO';
1229 /**************************************************************************/
1230
1231 CURSOR csr_already_exists(p_assignment_id IN NUMBER, p_abs_start_date IN DATE) IS
1232 SELECT 'Y'
1233 FROM pay_action_information pai
1234 WHERE pai.action_context_type = 'AAP'
1235 AND pai.action_information_category = 'NL_WAZO_ABS_INFO'
1236 AND pai.ASSIGNMENT_ID = p_assignment_id
1237 AND fnd_date.canonical_to_date(pai.action_information2) = p_abs_start_date
1238 AND pai.action_information30 = 'N';
1239 /**************************************************************************/
1240
1241 CURSOR get_archived_absence(p_assignment_id IN NUMBER
1242 , p_start_date IN DATE
1243 , p_end_date IN DATE
1244 , p_type IN VARCHAR2) IS
1245 SELECT pai.action_information1 abs_att_id
1246 ,pai.action_information2 st_dt
1247 ,pai.action_information3 end_dt
1248 ,pai.action_context_id org_asg_act_id
1249 ,pai.action_information_id act_info_id
1250 FROM pay_action_information pai
1251 WHERE pai.assignment_id = p_assignment_id
1252 AND pai.action_context_type = 'AAP'
1253 AND pai.action_information_category = 'NL_WAZO_ABS_INFO'
1254 AND pai.action_information29 = 'NL_WAZO'
1255 AND fnd_date.canonical_to_date (pai.action_information2) BETWEEN p_start_date
1256 AND p_end_date
1257 AND (
1258 (
1259 pai.action_information30 = 'N'
1260 AND p_type = 'W'
1261 )
1262 OR (
1263 p_type = 'R'
1264 )
1265 );
1266 /**************************************************************************/
1267 /* sender and contact data */
1268 CURSOR csr_get_org_contact_info(p_assignment_id IN NUMBER
1269 , p_start_date IN DATE) IS
1270 SELECT decode (hoi.org_information1,'Y','1','N','2') test_msg
1271 ,hoi.org_information2 sector
1272 ,lpad(hoi.org_information3, 2, 0) risk_group
1273 ,hoi.org_information4 role_sender
1274 ,hoi.org_information5 tax_no_sender
1275 ,hoi.org_information6 contact_per_or_dept
1276 ,hoi.org_information7 est_name
1277 ,hoi.org_information8 gender_contact
1278 ,hoi.org_information9 ph_no_contact
1279 FROM per_all_assignments_f pas
1280 ,hr_organization_units hou
1281 ,hr_organization_information hoi
1282 WHERE pas.assignment_id = p_assignment_id
1283 AND p_start_date BETWEEN pas.effective_start_date
1284 AND pas.effective_end_date
1285 AND hou.organization_id = nvl (pas.establishment_id
1286 ,pas.organization_id)
1287 AND hoi.organization_id = hou.organization_id
1288 AND hoi.org_information_context = 'NL_ORG_DSR_INFO';
1289 /**************************************************************************/
1290 /* employer payroll data */
1291 CURSOR csr_get_employer_bank_info (p_assignment_id IN NUMBER
1292 , p_start_date IN DATE) IS
1293 SELECT upper(pea.segment2) acc_no
1294 ,pea.segment10 iban
1295 FROM per_all_assignments_f pas
1296 ,pay_payrolls_f pp
1297 ,pay_org_payment_methods_f popm
1298 ,pay_external_accounts pea
1299 WHERE pas.assignment_id = p_assignment_id
1300 AND pas.payroll_id = pp.payroll_id
1301 AND p_start_date BETWEEN pp.effective_start_date
1302 AND pp.effective_end_date
1303 AND pp.default_payment_method_id = popm.org_payment_method_id
1304 AND p_start_date BETWEEN popm.effective_start_date
1305 AND popm.effective_end_date
1306 AND popm.external_account_id = pea.external_account_id
1307 AND popm.business_group_id = pas.business_group_id;
1308 /**************************************************************************/
1309 /* employee data */
1310 CURSOR csr_get_person_info(p_assignment_id IN NUMBER
1311 , p_start_date IN DATE
1312 , p_end_date IN DATE) IS
1313 SELECT pap.national_identifier sofi_number
1314 ,to_char (pap.date_of_birth,'YYYYMMDD') date_of_birth
1315 ,decode (pap.sex,'M','1','F','2',NULL,'9') gender
1316 ,REPLACE(REPLACE(pap.per_information1, '.', ''), ' ', '') init
1317 ,pap.pre_name_adjunct prefix
1318 ,pap.last_name last_name
1319 ,pap.employee_number employee_number
1320 ,pap.person_id person_id
1321 ,pap.business_group_id bg_id
1322 ,pas.establishment_id establishment_id
1323 FROM per_all_people_f pap
1324 ,per_all_assignments_f pas
1325 WHERE pas.assignment_id = p_assignment_id
1326 AND p_start_date BETWEEN pas.effective_start_date
1327 AND pas.effective_end_date
1328 AND pap.person_id = pas.person_id
1329 AND p_start_date BETWEEN pap.effective_start_date
1330 AND pap.effective_end_date;
1331 /**************************************************************************/
1332 /* Employee payroll data */
1333 CURSOR csr_get_employee_bank_info (p_assignment_id IN NUMBER
1334 , p_start_date IN DATE) IS
1335 SELECT upper(pea.segment2) acc_no
1336 ,pea.segment9 bic
1337 ,pea.segment10 iban
1338 FROM pay_personal_payment_methods_f pppm
1339 ,pay_external_accounts pea
1340 WHERE pppm.assignment_id = p_assignment_id
1341 AND p_start_date BETWEEN pppm.effective_start_date
1342 AND pppm.effective_end_date
1343 AND pppm.external_account_id = pea.external_account_id
1344 ORDER BY pppm.priority;
1345 /**************************************************************************/
1346 /* Assignment details */
1347 CURSOR csr_get_assignment_details (p_assignment_id IN NUMBER
1348 , p_start_date IN DATE) IS
1349 SELECT decode (hsck.segment4, 'Y', '1', 'N', '2') wage_tax_discount
1350 ,hsck.segment11 wage_tax_table
1351 ,pas.assignment_sequence asg_seq
1352 FROM per_all_assignments_f pas
1353 ,hr_soft_coding_keyflex hsck
1354 WHERE pas.assignment_id = p_assignment_id
1355 AND p_start_date BETWEEN pas.effective_start_date
1356 AND pas.effective_end_date
1357 AND hsck.soft_coding_keyflex_id(+) = pas.soft_coding_keyflex_id;
1358 /**************************************************************************/
1359 /* labor relation info */
1360 CURSOR csr_get_lbr_info (p_assignment_id IN NUMBER
1361 , p_start_date IN DATE) IS
1362 SELECT to_char(fnd_date.canonical_to_date (paei.aei_information1),'YYYYMMDD') start_date
1363 ,to_char(fnd_date.canonical_to_date (paei.aei_information2),'YYYYMMDD') end_date
1364 ,paei.aei_information3 code_kind
1365 FROM per_assignment_extra_info paei
1366 WHERE paei.assignment_id = p_assignment_id
1367 AND paei.aei_information_category = 'NL_LBR'
1368 AND p_start_date BETWEEN fnd_date.canonical_to_date (paei.aei_information1)
1369 AND nvl (fnd_date.canonical_to_date (paei.aei_information2)
1370 ,hr_general.end_of_time);
1371 /**************************************************************************/
1372 /* per_phones */
1373 CURSOR csr_get_phone_no(p_assignment_id IN NUMBER
1374 , p_start_date IN DATE
1375 , p_type IN VARCHAR2) IS
1376 SELECT pp.phone_number phone_no
1377 FROM per_all_assignments_f pas
1378 ,per_phones pp
1379 WHERE pas.assignment_id = p_assignment_id
1380 AND p_start_date BETWEEN pas.effective_start_date
1381 AND pas.effective_end_date
1382 AND pas.person_id = pp.parent_id
1383 AND pp.parent_table = 'PER_ALL_PEOPLE_F'
1384 AND pp.phone_type = p_type
1385 AND p_start_date BETWEEN pp.date_from
1386 AND nvl (pp.date_to
1387 ,hr_general.end_of_time);
1388 /**************************************************************************/
1389 /* Sick Addresses */
1390 CURSOR csr_get_sick_addr(p_assignment_id IN NUMBER
1391 , p_start_date IN DATE) IS
1392 SELECT to_char (pad.date_from,'YYYYMMDD') start_date
1393 ,to_char (pad.date_to,'YYYYMMDD') end_date
1394 ,pad.style style
1395 ,pad.postal_code postal
1396 ,pad.town_or_city town
1397 ,pad.country country
1398 ,pad.address_line1 address1
1399 ,pad.address_line2 address2
1400 ,pad.telephone_number_1 tel1
1401 ,pad.telephone_number_2 tel2
1402 ,pad.region_1 reg1
1403 ,pad.region_2 reg2
1404 ,pad.add_information13 info13
1405 ,pad.add_information14 info14
1406 ,pad.add_information15 info15
1407 ,pad.add_information16 info16
1408 FROM per_all_assignments_f pas
1409 ,per_addresses pad
1410 WHERE pas.assignment_id = p_assignment_id
1411 AND p_start_date BETWEEN pas.effective_start_date
1412 AND pas.effective_end_date
1413 AND pas.person_id = pad.person_id
1414 AND pas.business_group_id = pad.business_group_id
1415 AND pad.address_type = 'NL_SICK'
1416 AND pad.date_from =
1417 (
1418 SELECT max (pad1.date_from)
1419 FROM per_addresses pad1
1420 WHERE pad1.person_id = pad.person_id
1421 AND (
1422 p_start_date BETWEEN pad1.date_from
1423 AND nvl (pad1.date_to
1424 ,hr_general.end_of_time)
1425 OR pad1.date_from > p_start_date
1426 )
1427 );
1428 /**************************************************************************/
1429 /* Person Address */
1430 CURSOR csr_get_person_addr(p_assignment_id IN NUMBER
1431 , p_start_date IN DATE) IS
1432 SELECT to_char (pad.date_from,'YYYYMMDD') start_date
1433 ,to_char (pad.date_to,'YYYYMMDD') end_date
1434 ,pad.style style
1435 ,pad.postal_code postal
1436 ,pad.town_or_city town
1437 ,pad.country country
1438 ,pad.address_line1 address1
1439 ,pad.address_line2 address2
1440 ,pad.telephone_number_1 tel1
1441 ,pad.telephone_number_2 tel2
1442 ,pad.region_1 reg1
1443 ,pad.region_2 reg2
1444 ,pad.add_information13 info13
1445 ,pad.add_information14 info14
1446 ,pad.add_information15 info15
1447 ,pad.add_information16 info16
1448 FROM per_all_assignments_f pas
1449 ,per_addresses pad
1450 WHERE pas.assignment_id = p_assignment_id
1451 AND p_start_date BETWEEN pas.effective_start_date
1452 AND pas.effective_end_date
1453 AND pad.person_id = pas.person_id
1454 AND p_start_date BETWEEN pad.date_from
1455 AND nvl (pad.date_to
1456 ,hr_general.end_of_time);
1457 /**************************************************************************/
1458 /*NL_DS_SICK_INFO - Contact Details*/
1459 CURSOR csr_get_sickness_contact (p_assignment_id IN NUMBER
1460 , p_start_date IN DATE) IS
1461 SELECT pei_information21 contact_name
1462 ,pei_information22 est_name
1463 ,pei_information23 gender
1464 ,pei_information24 contact_ph_no
1465 FROM per_all_assignments_f pas
1466 ,per_people_extra_info pei
1467 WHERE pas.assignment_id = p_assignment_id
1468 AND p_start_date BETWEEN pas.effective_start_date
1469 AND pas.effective_end_date
1470 AND pei.person_id = pas.person_id
1471 AND pei.information_type = 'NL_DS_SICK_INFO';
1472 /**************************************************************************/
1473 CURSOR c_get_pact_chunk(c_assignment_action_id IN NUMBER ) IS
1474 SELECT ppa.payroll_action_id
1475 ,paa.chunk_number
1476 FROM pay_payroll_actions ppa
1477 ,pay_assignment_actions paa
1478 WHERE paa.payroll_action_id = ppa.payroll_action_id
1479 AND paa.assignment_action_id = c_assignment_action_id;
1480 /**************************************************************************/
1481 CURSOR c_get_assid_chunk(c_assignment_action_id IN NUMBER ) IS
1482 SELECT paa.assignment_id
1483 ,paa.chunk_number
1484 FROM pay_assignment_actions paa
1485 ,pay_payroll_actions ppa
1486 WHERE paa.payroll_action_id = ppa.payroll_action_id
1487 AND paa.assignment_action_id = c_assignment_action_id;
1488 /**************************************************************************/
1489 CURSOR csr_get_assignment_dates(p_assignment_id IN NUMBER) IS
1490 SELECT to_char(min (pas.effective_start_date),'YYYYMMDD') ass_start_date
1491 ,to_char(decode (max (pas.effective_end_date)
1492 ,hr_general.end_of_time
1493 ,to_date(NULL),max (pas.effective_end_date)),'YYYYMMDD') ass_end_date
1494 FROM per_all_assignments_f pas
1495 WHERE pas.assignment_id = p_assignment_id;
1496 /**************************************************************************/
1497 CURSOR csr_numiv_override(p_assignment_id IN NUMBER) IS
1498 SELECT paei.aei_information1 numiv_override
1499 FROM per_assignment_extra_info paei
1500 WHERE paei.assignment_id = p_assignment_id
1501 AND paei.aei_information_category = 'NL_NUMIV_OVERRIDE';
1502 /**************************************************************************/
1503
1504 l_org_struct_id NUMBER :=NULL;
1505 l_person_id NUMBER :=NULL;
1506 l_org_id NUMBER :=NULL;
1507 l_bg_id NUMBER;
1508 l_child_aa_id NUMBER;
1509 l_start_date DATE :=NULL;
1510 l_end_date DATE :=NULL;
1511 l_type VARCHAR2(1) :=NULL;
1512 l_abs_attendence_id NUMBER;
1513 l_payroll_action_id NUMBER;
1514 l_est_id NUMBER :=NULL;
1515 l_tax_reg_num VARCHAR2(100);
1516 l_org_name VARCHAR2(150);
1517 l_abs_start_date DATE :=NULL;
1518 l_abs_end_date DATE :=NULL;
1519 l_assignment_id NUMBER :=NULL;
1520 l_sick_ph_no per_phones.phone_number%TYPE;
1521 l_for_sick_ph_no per_phones.phone_number%TYPE;
1522 l_sick_mobile_no per_phones.phone_number%TYPE;
1523 l_job_name per_jobs.name%TYPE;
1524 l_fz_code per_assignment_extra_info.aei_information3%TYPE;
1525 l_action_info_id NUMBER;
1526 l_ovn NUMBER;
1527 l_exists VARCHAR2(1);
1528 l_chunk_number NUMBER;
1529 l_person_addr csr_get_person_addr%ROWTYPE;
1530 l_sick_addr csr_get_sick_addr%ROWTYPE;
1531 l_sickness_contact csr_get_sickness_contact%ROWTYPE;
1532 l_assignment_details csr_get_assignment_details%ROWTYPE;
1533 l_person_info csr_get_person_info%ROWTYPE;
1534 l_org_contact_info csr_get_org_contact_info%ROWTYPE;
1535 l_employer_bank csr_get_employer_bank_info%ROWTYPE;
1536 l_employee_bank csr_get_employee_bank_info%ROWTYPE;
1537 l_lbr_info csr_get_lbr_info%ROWTYPE;
1538 l_assignment_dates csr_get_assignment_dates%ROWTYPE;
1539 l_numiv_override per_assignment_extra_info.aei_information1%TYPE;
1540
1541 /**************************************************************************/
1542 BEGIN
1543 hr_utility.trace('+====archive_code==========================================+');
1544 hr_utility.trace('p_assignment_action_id : '||p_assignment_action_id);
1545 hr_utility.trace('p_effective_date : '||p_effective_date);
1546
1547 OPEN c_get_pact_chunk (p_assignment_action_id);
1548 FETCH c_get_pact_chunk INTO l_payroll_action_id, l_chunk_number;
1549 CLOSE c_get_pact_chunk;
1550
1551 hr_utility.trace('l_payroll_action_id : '||l_payroll_action_id);
1552 hr_utility.trace('l_chunk_number : '||l_chunk_number);
1553
1554 get_all_parameters (
1555 p_payroll_action_id => l_payroll_action_id
1556 ,p_org_struct_id => l_org_struct_id
1557 ,p_person_id => l_person_id
1558 ,p_org_id => l_org_id
1559 ,p_bg_id => l_bg_id
1560 ,p_start_date => l_start_date
1561 ,p_end_date => l_end_date
1562 ,p_type => l_type);
1563
1564 IF l_type = 'I' THEN
1565 FOR v_csr_get_abs_details IN csr_get_abs_details(p_assignment_action_id, l_start_date, l_end_date)
1566 LOOP
1567 l_exists := 'N';
1568 l_assignment_id := v_csr_get_abs_details.assignment_id;
1569 l_abs_attendence_id := v_csr_get_abs_details.abs_att_id;
1570 l_abs_start_date := v_csr_get_abs_details.start_date;
1571 l_abs_end_date := v_csr_get_abs_details.end_date;
1572 hr_utility.trace('Assignment/Attendence : ' || l_assignment_id||'/'||l_abs_attendence_id);
1573 hr_utility.trace('Absence Start/End Date : ' || to_char(l_abs_start_date,'DDMonYYYY') || ' / ' ||
1574 to_char(l_abs_end_date,'DDMonYYYY'));
1575
1576 OPEN csr_already_exists (l_assignment_id,l_abs_start_date);
1577 FETCH csr_already_exists INTO l_exists;
1578 CLOSE csr_already_exists;
1579 -- hr_utility.trace('l_exists : '||l_exists);
1580 IF l_exists = 'Y' THEN
1581 fnd_file.put_line(fnd_file.log,'WAZO Report already sent. Hence skipped.');
1582 ELSIF l_exists = 'N' THEN
1583
1584 SELECT pay_assignment_actions_s.NEXTVAL INTO l_child_aa_id FROM dual;
1585
1586 hr_nonrun_asact.insact(
1587 lockingactid => l_child_aa_id
1588 , assignid => l_assignment_id
1589 , pactid => l_payroll_action_id
1590 , chunk => l_chunk_number
1591 , status => 'C'
1592 , source_act => p_assignment_action_id);
1593
1594 OPEN csr_get_sickness_contact(l_assignment_id, l_abs_start_date );
1595 FETCH csr_get_sickness_contact INTO l_sickness_contact;
1596 CLOSE csr_get_sickness_contact;
1597
1598 OPEN csr_get_person_info(l_assignment_id, l_abs_start_date, l_abs_end_date);
1599 FETCH csr_get_person_info into l_person_info;
1600 CLOSE csr_get_person_info;
1601
1602 OPEN csr_get_org_contact_info(l_assignment_id, l_abs_start_date );
1603 FETCH csr_get_org_contact_info into l_org_contact_info;
1604 CLOSE csr_get_org_contact_info;
1605
1606 OPEN csr_get_employer_bank_info(l_assignment_id, l_abs_start_date );
1607 FETCH csr_get_employer_bank_info into l_employer_bank;
1608 CLOSE csr_get_employer_bank_info;
1609
1610 /* Archiving absence details */
1611 pay_action_information_api.create_action_information
1612 (
1613 p_action_information_id => l_action_info_id
1614 , p_action_context_id => l_child_aa_id
1615 , p_action_context_type => 'AAP'
1616 , p_object_version_number => l_ovn
1617 , p_assignment_id => l_assignment_id
1618 , p_effective_date => p_effective_date
1619 , p_source_id => NULL
1620 , p_source_text => NULL
1621 , p_action_information_category => 'NL_WAZO_ABS_INFO'
1622 , p_action_information1 => l_abs_attendence_id
1623 , p_action_information2 => fnd_date.date_to_canonical(l_abs_start_date)
1624 , p_action_information3 => fnd_date.date_to_canonical(l_abs_end_date)
1625 , p_action_information4 => 'WAZO'
1626 , p_action_information5 => v_csr_get_abs_details.person_id
1627 , p_action_information6 => v_csr_get_abs_details.sick_ref
1628 , p_action_information7 => v_csr_get_abs_details.code_pymt
1629 , p_action_information8 => v_csr_get_abs_details.reason
1630 , p_action_information9 => v_csr_get_abs_details.wazo_st_dt
1631 , p_action_information10 => l_sickness_contact.contact_name
1632 , p_action_information11 => l_sickness_contact.est_name
1633 , p_action_information12 => l_sickness_contact.gender
1634 , p_action_information13 => l_sickness_contact.contact_ph_no
1635 , p_action_information14 => l_employer_bank.acc_no
1636 -- , p_action_information15 => v_csr_get_abs_details.busi_group_id
1637 -- , p_action_information16 => v_csr_get_abs_details.abs_att_type_id
1638 , p_action_information17 => l_employer_bank.iban
1639 , p_action_information18 => v_csr_get_abs_details.spl_sit
1640 , p_action_information19 => v_csr_get_abs_details.est_dob
1641 , p_action_information29 => 'NL_WAZO'
1642 , p_action_information30 => 'N'
1643 );
1644
1645
1646
1647
1648 l_est_id := l_person_info.establishment_id;
1649
1650 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
1651 OPEN csr_get_leg_info(l_assignment_id);
1652 FETCH csr_get_leg_info INTO l_org_id, l_org_name, l_tax_reg_num;
1653 CLOSE csr_get_leg_info;
1654 ELSE
1655 OPEN csr_get_org_info(l_assignment_id, l_org_struct_id, l_abs_start_date);
1656 FETCH csr_get_org_info INTO l_org_id, l_org_name, l_tax_reg_num;
1657 CLOSE csr_get_org_info;
1658 END IF;
1659
1660
1661 OPEN csr_get_employee_bank_info(l_assignment_id, l_abs_start_date);
1662 FETCH csr_get_employee_bank_info INTO l_employee_bank;
1663 CLOSE csr_get_employee_bank_info;
1664
1665 OPEN csr_get_assignment_details(l_assignment_id, l_abs_start_date);
1666 FETCH csr_get_assignment_details INTO l_assignment_details;
1667 close csr_get_assignment_details;
1668
1669 OPEN csr_get_lbr_info(l_assignment_id, l_abs_start_date);
1670 FETCH csr_get_lbr_info INTO l_lbr_info;
1671 CLOSE csr_get_lbr_info;
1672
1673 OPEN csr_get_assignment_dates(l_assignment_id);
1674 FETCH csr_get_assignment_dates into l_assignment_dates;
1675 CLOSE csr_get_assignment_dates;
1676
1677 OPEN csr_numiv_override(l_assignment_id);
1678 FETCH csr_numiv_override INTO l_numiv_override;
1679 CLOSE csr_numiv_override;
1680
1681 pay_action_information_api.create_action_information
1682 (
1683 p_action_information_id => l_action_info_id
1684 , p_action_context_id => l_child_aa_id
1685 , p_action_context_type => 'AAP'
1686 , p_object_version_number => l_ovn
1687 , p_assignment_id => l_assignment_id
1688 , p_effective_date => p_effective_date
1689 , p_source_id => NULL
1690 , p_source_text => NULL
1691 , p_action_information_category => 'NL_WAZO_EMP_INFO'
1692 , p_action_information1 => l_abs_attendence_id
1693 , p_action_information2 => l_org_contact_info.test_msg
1694 , p_action_information3 => l_org_contact_info.tax_no_sender
1695 , p_action_information4 => l_org_contact_info.role_sender
1696 , p_action_information5 => l_org_contact_info.contact_per_or_dept
1697 , p_action_information6 => l_org_contact_info.ph_no_contact
1698 , p_action_information7 => l_org_contact_info.risk_group
1699 , p_action_information8 => l_org_contact_info.sector
1700 , p_action_information9 => l_tax_reg_num
1701 -- , p_action_information10 => l_employer_bank.acc_no
1702 , p_action_information11 => l_person_info.sofi_number
1703 , p_action_information12 => l_person_info.date_of_birth
1704 , p_action_information13 => l_person_info.gender
1705 , p_action_information14 => l_person_info.init
1706 , p_action_information15 => l_person_info.prefix
1707 , p_action_information16 => l_person_info.last_name
1708 , p_action_information17 => l_employee_bank.acc_no
1709 , p_action_information18 => l_employee_bank.bic
1710 , p_action_information19 => l_employee_bank.iban
1711 , p_action_information20 => l_assignment_details.wage_tax_discount
1712 , p_action_information21 => nvl(l_numiv_override,l_assignment_details.asg_seq)
1713 , p_action_information22 => l_assignment_details.wage_tax_table
1714 , p_action_information23 => l_lbr_info.end_date
1715 , p_action_information24 => l_person_info.employee_number
1716 , p_action_information25 => l_org_contact_info.est_name
1717 , p_action_information26 => l_org_contact_info.gender_contact
1718 , p_action_information27 => l_assignment_dates.ass_start_date
1719 , p_action_information28 => l_assignment_dates.ass_end_date
1720 );
1721
1722 OPEN csr_get_phone_no(l_assignment_id, l_abs_start_date, 'NL_LS');
1723 FETCH csr_get_phone_no into l_sick_ph_no;
1724 CLOSE csr_get_phone_no;
1725
1726 OPEN csr_get_phone_no(l_assignment_id, l_abs_start_date, 'NL_FS');
1727 FETCH csr_get_phone_no into l_for_sick_ph_no;
1728 CLOSE csr_get_phone_no;
1729
1730 OPEN csr_get_phone_no(l_assignment_id, l_abs_start_date, 'NL_SM');
1731 FETCH csr_get_phone_no into l_sick_mobile_no;
1732 CLOSE csr_get_phone_no;
1733
1734 OPEN csr_get_sick_addr(l_assignment_id, l_abs_start_date);
1735 FETCH csr_get_sick_addr into l_sick_addr;
1736 CLOSE csr_get_sick_addr;
1737
1738
1739 hr_utility.trace('Sickness Address Style : ' || l_sick_addr.style);
1740 IF l_sick_addr.style IS NOT NULL THEN
1741 /* Archiving sickness address */
1742 pay_action_information_api.create_action_information
1743 ( p_action_information_id => l_action_info_id
1744 , p_action_context_id => l_child_aa_id
1745 , p_action_context_type => 'AAP'
1746 , p_object_version_number => l_ovn
1747 , p_assignment_id => l_assignment_id
1748 , p_effective_date => p_effective_date
1749 , p_source_id => NULL
1750 , p_source_text => NULL
1751 , p_action_information_category => 'NL_WAZO_ADDRESS'
1752 , p_action_information1 => l_abs_attendence_id
1753 , p_action_information2 => l_sick_addr.style
1754 , p_action_information3 => l_sick_addr.start_date
1755 , p_action_information4 => l_sick_addr.end_date
1756 , p_action_information5 => REPLACE(l_sick_addr.postal,' ','')
1757 , p_action_information6 => l_sick_addr.town
1758 , p_action_information7 => l_sick_addr.country -- only for NL_GLB
1759 , p_action_information8 => l_sick_addr.tel1
1760 , p_action_information9 => l_sick_addr.tel2 -- for NL mobile, for NL_GLB forino
1761 , p_action_information10 => l_sick_addr.reg1 -- for NL st name, for NL_GLB region
1762 , p_action_information11 => l_sick_addr.reg2 -- Only NL_GLB loc des
1763 , p_action_information12 => l_sick_addr.info13 -- NL hse no
1764 , p_action_information13 => l_sick_addr.info14 -- NL hse no add,
1765 , p_action_information14 => l_sick_addr.info15 -- NL hse boat NL_GLB ISO country
1766 , p_action_information15 => l_sick_addr.info16 -- NL caravan
1767 , p_action_information16 => l_sick_ph_no
1768 , p_action_information17 => l_for_sick_ph_no
1769 , p_action_information18 => l_sick_mobile_no
1770 , p_action_information19 => l_sick_addr.address1 -- NL_GLB street
1771 , p_action_information20 => l_sick_addr.address2 -- NL_GLB hse no
1772 );
1773
1774 ELSE
1775 OPEN csr_get_person_addr(l_assignment_id, l_abs_start_date);
1776 FETCH csr_get_person_addr INTO l_person_addr;
1777 CLOSE csr_get_person_addr;
1778 IF l_person_addr.style IS NOT NULL THEN
1779 hr_utility.trace('Person Address Style : ' || l_person_addr.style);
1780
1781 /* Archieving sickness address */
1782 pay_action_information_api.create_action_information
1783 (
1784 p_action_information_id => l_action_info_id
1785 , p_action_context_id => l_child_aa_id
1786 , p_action_context_type => 'AAP'
1787 , p_object_version_number => l_ovn
1788 , p_assignment_id => l_assignment_id
1789 , p_effective_date => p_effective_date
1790 , p_source_id => NULL
1791 , p_source_text => NULL
1792 , p_action_information_category => 'NL_WAZO_ADDRESS'
1793 , p_action_information1 => l_abs_attendence_id
1794 , p_action_information2 => l_person_addr.style
1795 , p_action_information3 => l_person_addr.start_date
1796 , p_action_information4 => l_person_addr.end_date
1797 , p_action_information5 => REPLACE(l_person_addr.postal,' ','')
1798 , p_action_information6 => l_person_addr.town
1799 , p_action_information7 => l_person_addr.country -- only for NL_GLB
1800 , p_action_information8 => l_person_addr.tel1
1801 , p_action_information9 => l_person_addr.tel2 -- for NL mobile, for NL_GLB forino
1802 , p_action_information10 => l_person_addr.reg1 -- for NL st name, for NL_GLB St name
1803 , p_action_information11 => l_person_addr.reg2 -- Only NL_GLB loc des
1804 , p_action_information12 => l_person_addr.info13 -- NL hse no
1805 , p_action_information13 => l_person_addr.info14 -- NL hse no add
1806 , p_action_information14 => l_person_addr.info15 -- NL hse boat NL_GLB ISO country
1807 , p_action_information15 => l_person_addr.info16 -- NL caravan
1808 , p_action_information16 => l_sick_ph_no
1809 , p_action_information17 => l_for_sick_ph_no
1810 , p_action_information18 => l_sick_mobile_no
1811 , p_action_information19 => l_person_addr.address1 -- NL_GLB street
1812 , p_action_information20 => l_person_addr.address2 -- NL_GLB hse no
1813 );
1814 END IF;
1815 END IF;
1816 END IF;
1817 END LOOP;
1818 ELSIF l_type IN ('W','R') THEN
1819 hr_utility.trace(l_type||' Assignment Action ID : '||p_assignment_action_id );
1820 OPEN c_get_assid_chunk (p_assignment_action_id);
1821 FETCH c_get_assid_chunk INTO l_assignment_id, l_chunk_number;
1822 CLOSE c_get_assid_chunk;
1823 hr_utility.trace('Assignment ID/Chunk : '||l_assignment_id||' / '||l_chunk_number);
1824
1825 FOR r_get_archived_absence IN get_archived_absence(l_assignment_id, l_start_date, l_end_date, l_type)
1826 LOOP
1827
1828 hr_utility.trace('Absence Attendence ID : ' || r_get_archived_absence.abs_att_id);
1829
1830 hr_utility.trace('Absence Start/End Date : ' ||
1831 to_char(fnd_date.canonical_to_date(r_get_archived_absence.st_dt),'DDMonYYYY') || ' / ' ||
1832 to_char(fnd_date.canonical_to_date(r_get_archived_absence.end_dt),'DDMonYYYY'));
1833 hr_utility.trace('Org Asg Action ID : ' || r_get_archived_absence.org_asg_act_id);
1834
1835 hr_nonrun_asact.insint(lockingactid => p_assignment_action_id
1836 , lockedactid => r_get_archived_absence.org_asg_act_id);
1837 IF l_type = 'W' THEN
1838 -- Update previous archive incase of withdrawal.
1839 pay_action_information_api.update_action_information
1840 (p_action_information_id => r_get_archived_absence.act_info_id
1841 , p_object_version_number => l_ovn
1842 , p_action_information30 => 'Y'
1843 );
1844 END IF;
1845 END LOOP;
1846 END IF;
1847
1848 hr_utility.trace('X====archive_code==========================================X');
1849 EXCEPTION
1850 WHEN TOO_MANY_ROWS THEN
1851 hr_utility.trace('Too Many Rows Exception in archive_code');
1852
1853 WHEN NO_DATA_FOUND THEN
1854 hr_utility.trace('No Data Found Exception in archive_code');
1855
1856 WHEN others THEN
1857 hr_utility.trace('Exception in archive_code SQL-ERRM : '||SQLERRM);
1858
1859 END archive_code;
1860 /**************************************************************************/
1861
1862
1863
1864 /*-------------------------------------------------------------------------------
1865 |Name : DEINITIALIZATION_CODE |
1866 |Type : Procedure |
1867 |Description : Deinitialization code |
1868 -------------------------------------------------------------------------------*/
1869
1870
1871 PROCEDURE deinitialization_code(p_actid IN NUMBER) IS
1872 BEGIN
1873 hr_utility.trace('+====deinitialization_code====X '|| p_actid);
1874 END deinitialization_code;
1875
1876
1877
1878
1879
1880 /**************************************************************************/
1881 PROCEDURE gen_header_xml
1882 IS
1883 l_string VARCHAR2(32767) := NULL;
1884 l_string_value VARCHAR2(1000) := NULL;
1885 l_clob pay_file_details.file_fragment%TYPE;
1886 l_blob pay_file_details.blob_file_fragment%TYPE;
1887 l_payroll_action_id NUMBER;
1888 l_org_struct_id NUMBER := NULL;
1889 l_person_id NUMBER := NULL;
1890 l_org_id NUMBER := NULL;
1891 l_bg_id NUMBER;
1892 l_start_date DATE := NULL;
1893 l_end_date DATE := NULL;
1894 l_type VARCHAR2(1) := NULL;
1895 l_lang VARCHAR2(2) := userenv ('lang');
1896
1897 CURSOR c_get_hierarchy(p_org_struct_id IN NUMBER) IS
1898 SELECT name
1899 FROM per_organization_structures
1900 WHERE organization_structure_id = p_org_struct_id;
1901
1902
1903 CURSOR c_get_employer(p_org_id IN NUMBER) IS
1904 SELECT name
1905 FROM hr_organization_units
1906 WHERE organization_id = p_org_id;
1907
1908 CURSOR c_get_employee(p_person_id IN NUMBER) IS
1909 SELECT full_name
1910 FROM per_all_people_f
1911 WHERE person_id = p_person_id;
1912
1913 BEGIN
1914 hr_utility.trace('+====gen_header_xml========================================+');
1915 l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
1916 hr_utility.trace('l_payroll_action_id : '||l_payroll_action_id);
1917 l_string := l_string || '<ROOT>'||EOL;
1918
1919 get_all_parameters (
1920 p_payroll_action_id => l_payroll_action_id
1921 ,p_org_struct_id => l_org_struct_id
1922 ,p_person_id => l_person_id
1923 ,p_org_id => l_org_id
1924 ,p_bg_id => l_bg_id
1925 ,p_start_date => l_start_date
1926 ,p_end_date => l_end_date
1927 ,p_type => l_type);
1928
1929 l_string_value := get_look_up_value('HR_NL_DS_REPORT_TYPE',l_type,l_lang);
1930 l_string := l_string || '<P_REPORT_TYPE>'||clean_XML(l_string_value)||'</P_REPORT_TYPE>'||EOL;
1931
1932 l_string_value := fnd_date.date_to_chardate(l_start_date);
1933 l_string := l_string || '<P_DATE_FROM>'||clean_XML(l_string_value)||'</P_DATE_FROM>'||EOL;
1934
1935 l_string_value := fnd_date.date_to_chardate(l_end_date);
1936 l_string := l_string || '<P_DATE_TO>'||clean_XML(l_string_value)||'</P_DATE_TO>'||EOL;
1937
1938 IF l_org_struct_id IS NOT NULL THEN
1939 OPEN c_get_hierarchy(l_org_struct_id);
1940 FETCH c_get_hierarchy INTO l_string_value;
1941 CLOSE c_get_hierarchy;
1942 l_string := l_string || '<P_HIERARCHY>'||clean_XML(l_string_value)||'</P_HIERARCHY>'||EOL;
1943 END IF;
1944
1945 IF l_org_id IS NOT NULL THEN
1946 OPEN c_get_employer(l_org_id);
1947 FETCH c_get_employer INTO l_string_value;
1948 CLOSE c_get_employer;
1949 l_string := l_string || '<P_EMPLOYER>'||clean_XML(l_string_value)||'</P_EMPLOYER>'||EOL;
1950 END IF;
1951
1952 IF l_person_id IS NOT NULL THEN
1953 OPEN c_get_employee(l_person_id);
1954 FETCH c_get_employee INTO l_string_value;
1955 CLOSE c_get_employee;
1956 l_string := l_string || '<P_EMPLOYEE>'||clean_XML(l_string_value)||'</P_EMPLOYEE>'||EOL;
1957 END IF;
1958
1959 l_clob := l_clob||l_string;
1960 IF l_clob IS NOT NULL THEN
1961 l_blob := c2b(l_clob);
1962 pay_core_files.write_to_magtape_lob(l_blob);
1963 END IF;
1964 hr_utility.trace('X====gen_header_xml========================================X');
1965 EXCEPTION
1966 WHEN others THEN
1967 hr_utility.trace('Exception in gen_header_xml SQL-ERRM : '||SQLERRM);
1968 END gen_header_xml;
1969
1970
1971 /**************************************************************************/
1972 PROCEDURE gen_footer_xml
1973 IS
1974 l_buf VARCHAR2(32767);
1975 BEGIN
1976 hr_utility.trace('+====gen_footer_xml========================================+');
1977 l_buf := l_buf || '</ROOT>'||EOL ;
1978 pay_core_files.write_to_magtape_lob(l_buf);
1979 hr_utility.trace('X====gen_footer_xml========================================X');
1980 EXCEPTION
1981 WHEN others THEN
1982 hr_utility.trace('Exception in gen_footer_xml SQL-ERRM : '||SQLERRM);
1983 END gen_footer_xml;
1984 /**************************************************************************/
1985
1986
1987 PROCEDURE gen_body_xml_main(p_type varchar2, p_id number) IS
1988
1989
1990 CURSOR cur_abs_info_details (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
1991 SELECT pai.action_information1 abs_att_id
1992 ,pai.action_information2 start_date
1993 ,pai.action_information3 end_date
1994 ,pai.action_information4 abs_cat
1995 ,pai.action_information5 person_id
1996 ,pai.action_information6 sick_ref
1997 ,pai.action_information7 code_pymt
1998 ,pai.action_information8 wazo_reason
1999 ,pai.action_information9 wazo_st_dt
2000 ,pai.action_information10 contact_name
2001 ,pai.action_information11 est_name
2002 ,pai.action_information12 gender
2003 ,pai.action_information13 contact_ph_no
2004 ,pai.action_information14 employer_acc_no
2005 ,pai.action_information17 employer_iban
2006 ,pai.action_information18 spl_sit
2007 ,pai.action_information19 est_dob
2008 FROM pay_action_information pai
2009 WHERE pai.action_context_id = c_assignment_action_id
2010 AND pai.action_information_category = 'NL_WAZO_ABS_INFO'
2011 AND action_context_type = 'AAP';
2012 /**************************************************************************/
2013 CURSOR cur_emp_details (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
2014 SELECT action_information2 test_msg
2015 ,action_information3 tax_no_sender
2016 ,action_information4 role_sender
2017 ,action_information5 contact_per_or_dept
2018 ,action_information6 ph_no_contact
2019 ,action_information7 risk_group
2020 ,action_information8 sector
2021 ,action_information9 tax_reg_num
2022 -- ,action_information10 employer_acc_no
2023 ,action_information11 sofi_number
2024 ,action_information12 date_of_birth
2025 ,action_information13 gender
2026 ,action_information14 init
2027 ,action_information15 prefix
2028 ,action_information16 last_name
2029 ,action_information17 emp_acc_no
2030 ,action_information18 emp_bic
2031 ,action_information19 emp_iban
2032 ,action_information20 wage_tax_discount
2033 ,action_information21 asg_seq
2034 ,action_information22 wage_tax_table
2035 ,action_information23 end_date
2036 ,action_information24 emp_num
2037 ,action_information25 est_name
2038 ,action_information26 gender_contact
2039 ,action_information27 ass_start_date
2040 ,action_information28 ass_end_date
2041 FROM pay_action_information pai
2042 WHERE pai.action_context_id = c_assignment_action_id
2043 AND pai.action_information_category = 'NL_WAZO_EMP_INFO'
2044 AND action_context_type = 'AAP';
2045 /**************************************************************************/
2046 CURSOR cur_addr_details (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
2047 SELECT action_information2 style
2048 ,action_information3 start_date
2049 ,action_information4 end_date
2050 ,action_information5 postal
2051 ,action_information6 town
2052 ,action_information7 country
2053 ,action_information8 tel1
2054 ,action_information9 tel2
2055 ,action_information10 reg1
2056 ,action_information11 reg2
2057 ,action_information12 info13
2058 ,action_information13 info14
2059 ,action_information14 info15
2060 ,action_information15 info16
2061 ,action_information16 sick_ph_no
2062 ,action_information17 for_sick_ph_no
2063 ,action_information18 sick_mobile_no
2064 ,action_information19 address1
2065 ,action_information20 address2
2066 FROM pay_action_information pai
2067 WHERE pai.action_context_id = c_assignment_action_id
2068 AND pai.action_information_category = 'NL_WAZO_ADDRESS'
2069 AND action_context_type = 'AAP';
2070 /**************************************************************************/
2071 CURSOR cur_addl_sick_info(p_abs_attendance_id IN NUMBER) IS
2072 SELECT fdst.short_text text
2073 FROM fnd_attached_documents fad
2074 ,fnd_documents fd
2075 ,fnd_document_categories fdc
2076 ,fnd_document_datatypes fdd
2077 ,fnd_documents_short_text fdst
2078 WHERE fad.entity_name = 'PER_ABSENCE_ATTENDANCES'
2079 AND fad.pk1_value = to_char (p_abs_attendance_id)
2080 AND fd.document_id = fad.document_id
2081 AND fdd.datatype_id = fd.datatype_id
2082 AND fdc.category_id = fd.category_id
2083 AND fdc.name = 'HR_COMMENT'
2084 AND fdd.name = 'SHORT_TEXT'
2085 AND fdd.language = 'US'
2086 AND fdst.media_id = fd.media_id;
2087 /**************************************************************************/
2088 CURSOR cur_withdrawl_flag(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) is
2089 SELECT pai.action_information30 flag
2090 FROM pay_action_information pai
2091 WHERE pai.action_context_id = c_assignment_action_id
2092 AND pai.action_information_category = 'NL_WAZO_ABS_INFO'
2093 AND action_context_type = 'AAP';
2094 /**************************************************************************/
2095 CURSOR cur_app_version IS
2096 SELECT release_name
2097 FROM fnd_product_groups;
2098 /**************************************************************************/
2099 --Variables--
2100
2101 l_clob PAY_FILE_DETAILS.FILE_FRAGMENT%TYPE;
2102 l_blob PAY_FILE_DETAILS.BLOB_FILE_FRAGMENT%TYPE;
2103 l_file UTL_FILE.FILE_TYPE;
2104 l_directory_path VARCHAR2(500);
2105 l_file_name VARCHAR2(50);
2106 x NUMBER := 0;
2107 r NUMBER := 0;
2108 l_type VARCHAR2(1);
2109 l_lang VARCHAR2(2) := userenv ('lang');
2110 l_add_info cur_addl_sick_info%ROWTYPE;
2111 l_abs_info cur_abs_info_details%ROWTYPE;
2112 l_emp_details cur_emp_details%ROWTYPE;
2113 l_addr_details cur_addr_details%ROWTYPE;
2114 l_get_app_ver cur_app_version%ROWTYPE;
2115 l_withdrawl_flag cur_withdrawl_flag%ROWTYPE;
2116 xXMLTable tXMLTable;
2117 rXMLTable tXMLTable;
2118
2119
2120 PROCEDURE Tag (xCtr IN OUT NOCOPY NUMBER
2121 ,rCtr IN OUT NOCOPY NUMBER
2122 ,pTagName IN VARCHAR2
2123 ,pTagValue IN VARCHAR2
2124 ,pDestination IN VARCHAR2)
2125 IS
2126 BEGIN
2127 IF pTagValue IS NOT NULL THEN
2128 IF pDestination IN ('X','B') THEN
2129 xXMLTable(xCtr).TagName := pTagName;
2130 xXMLTable(xCtr).TagValue := pTagValue;
2131
2132 IF pTagName = 'UwvZwWazoMelding' AND pTagValue = '_START_' THEN
2133 xXMLTable(xCtr).TagName := xXMLTable(xCtr).TagName||EOL||
2134 'xsi:schemaLocation="http://schemas.uwv.nl/UwvML/Berichten/UwvZwWazoMelding-v0207 UwvZwWazoMelding-v0207-b01.xsd"'||EOL||
2135 'xmlns:str="http://schemas.uwv.nl/UwvML/Berichten/UwvZwWazoMelding-v0207"'||EOL||
2136 'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"';
2137 END IF;
2138
2139 xCtr := xCtr + 1;
2140 END IF;
2141 IF pDestination IN ('R','B') THEN
2142 rXMLTable(rCtr).TagName := pTagName;
2143 rXMLTable(rCtr).TagValue := pTagValue;
2144 rCtr := rCtr + 1;
2145 END IF;
2146 END IF;
2147 END Tag;
2148
2149
2150 BEGIN
2151 hr_utility.trace('+====gen_body_xml_main=====================================+');
2152
2153 OPEN cur_abs_info_details(p_id);
2154 FETCH cur_abs_info_details INTO l_abs_info;
2155 CLOSE cur_abs_info_details;
2156
2157 OPEN cur_emp_details(p_id);
2158 FETCH cur_emp_details INTO l_emp_details;
2159 CLOSE cur_emp_details;
2160
2161 OPEN cur_addr_details(p_id);
2162 FETCH cur_addr_details INTO l_addr_details;
2163 CLOSE cur_addr_details;
2164
2165 IF l_abs_info.abs_att_id IS NOT NULL THEN
2166 OPEN cur_addl_sick_info(l_abs_info.abs_att_id);
2167 FETCH cur_addl_sick_info into l_add_info;
2168 CLOSE cur_addl_sick_info;
2169 END IF;
2170
2171 OPEN cur_app_version;
2172 FETCH cur_app_version into l_get_app_ver;
2173 CLOSE cur_app_version;
2174
2175 IF p_type = 'R' THEN
2176 OPEN cur_withdrawl_flag(p_id);
2177 FETCH cur_withdrawl_flag into l_withdrawl_flag;
2178 CLOSE cur_withdrawl_flag;
2179 END IF;
2180 hr_utility.trace('Fetched all data. XML Generation starts');
2181
2182 Tag(x,r,'UwvZwWazoMelding','_START_','B'); -- wazo report*
2183 Tag(x,r,'IndTestbericht',l_emp_details.test_msg,'X'); -- test msg
2184 Tag(x,r,'KetenPartij','_START_','B'); -- + sender data*
2185 Tag(x,r,'Loonheffingennr',l_emp_details.tax_no_sender,'X'); -- tax reg no sender*
2186 Tag(x,r,'CdRolKetenpartij',l_emp_details.role_sender,'X'); -- role sender*
2187 Tag(x,r,'NaamSoftwarePakket','Oracle HCM','X'); -- appln name
2188 Tag(x,r,'VersieSoftwarePakket',l_get_app_ver.release_name,'X'); -- ver no
2189 IF l_emp_details.contact_per_or_dept IS NOT NULL
2190 OR l_emp_details.ph_no_contact IS NOT NULL THEN
2191 Tag(x,r,'ContactGegevens','_START_','B'); -- + contact data
2192 Tag(x,r,'NaamContactpersoonAfd',l_emp_details.contact_per_or_dept,'B'); -- person or dept
2193 Tag(x,r,'TelefoonnrContactpersoonAfd',l_emp_details.ph_no_contact,'B'); -- ph no
2194 Tag(x,r,'ContactGegevens','_END_','B'); -- - contact data
2195 END IF;
2196 Tag(x,r,'KetenPartij','_END_','B'); -- - sender data*
2197 Tag(x,r,'AdministratieveEenheid','_START_','B'); -- + employer*
2198 Tag(x,r,'Loonheffingennr',l_emp_details.tax_reg_num,'X'); -- tax no employer*
2199 Tag(x,r,'SectorRisicogroep','_START_','X'); -- + risk sector grp*
2200 Tag(x,r,'CdRisicoPremiegroep',l_emp_details.risk_group,'X'); -- risk grp*
2201 Tag(x,r,'CdSectorOsv',l_emp_details.sector,'X'); -- sector *
2202 Tag(x,r,'SectorRisicogroep','_END_','X'); -- - risk sector grp*
2203 IF l_abs_info.employer_acc_no IS NOT NULL
2204 OR l_abs_info.employer_iban IS NOT NULL THEN
2205 Tag(x,r,'Bankrekening','_START_','X'); -- + employer bank
2206 Tag(x,r,'Bankrekeningnr',l_abs_info.employer_acc_no,'X'); -- acc no
2207 Tag(x,r,'Iban',l_abs_info.employer_iban,'X'); -- iban
2208 Tag(x,r,'Bankrekening','_END_','X'); -- - employer bank
2209 END IF;
2210 Tag(x,r,'NatuurlijkPersoon','_START_','B'); -- + employee data*
2211 Tag(x,r,'Burgerservicenr',l_emp_details.sofi_number,'B'); -- sofino*
2212 Tag(x,r,'Geboortedat',l_emp_details.date_of_birth,'X'); -- dob*
2213 Tag(x,r,'Geboortedat',fnd_date.date_to_chardate(
2214 to_date(l_emp_details.date_of_birth,'YYYYMMDD')),'R');
2215 Tag(x,r,'Geslacht',l_emp_details.gender,'X'); -- gender
2216 Tag(x,r,'Geslacht',get_look_up_value('HR_NL_DS_GENDER',
2217 l_emp_details.gender,l_lang),'R');
2218 Tag(x,r,'Voorletters',l_emp_details.init,'B'); -- initials
2219 Tag(x,r,'Voorvoegsel',l_emp_details.prefix,'B'); -- prefix
2220 Tag(x,r,'SignificantDeelVanDeAchternaam',l_emp_details.last_name,'B'); -- last name*
2221 Tag(x,r,'Telefoonnr',NVL(l_addr_details.sick_ph_no, l_addr_details.tel1),'B');-- sick tel no
2222 Tag(x,r,'TelefoonnrMobiel',NVL(l_addr_details.sick_mobile_no
2223 , l_addr_details.tel2),'B'); -- mobile no sick
2224 Tag(x,r,'StdTelefoonnrBuitenland',NVL(l_addr_details.for_sick_ph_no
2225 , l_addr_details.tel2),'B'); -- foriegn ph no
2226 IF l_emp_details.emp_acc_no IS NOT NULL
2227 OR substr(NVL(l_emp_details.emp_iban,'00'),1,2) = 'NL' THEN
2228 Tag(x,r,'BankrekeningNederland','_START_','B'); -- + Employee Bank
2229 Tag(x,r,'Bankrekeningnr',l_emp_details.emp_acc_no,'B'); -- acc no
2230 IF substr(NVL(l_emp_details.emp_iban,'00'),1,2) = 'NL' THEN
2231 Tag(x,r,'Iban',l_emp_details.emp_iban,'B'); -- iban
2232 END IF;
2233 Tag(x,r,'BankrekeningNederland','_END_','B'); -- - Employee Bank
2234 ELSIF (l_emp_details.emp_iban IS NOT NULL AND substr(l_emp_details.emp_iban,1,2) <> 'NL')
2235 OR l_emp_details.emp_bic IS NOT NULL THEN
2236 Tag(x,r,'BankrekeningBuitenland','_START_','B'); -- + Employee Foreign Bank
2237 Tag(x,r,'Iban',l_emp_details.emp_iban,'B'); -- iban
2238 Tag(x,r,'Bic',get_look_up_value('HR_NL_BIC_CODES',
2239 l_emp_details.emp_bic,l_lang),'B'); -- bic
2240 Tag(x,r,'BankrekeningBuitenland','_END_','B'); -- - Employee Foreign Bank
2241 END IF;
2242 IF l_addr_details.start_date IS NOT NULL
2243 OR l_addr_details.end_date IS NOT NULL
2244 OR l_addr_details.reg1 IS NOT NULL
2245 OR l_addr_details.info13 IS NOT NULL
2246 OR l_addr_details.info14 IS NOT NULL
2247 OR l_addr_details.postal IS NOT NULL
2248 OR l_addr_details.town IS NOT NULL
2249 OR l_addr_details.info15 IS NOT NULL
2250 OR l_addr_details.info16 IS NOT NULL
2251 OR l_addr_details.address1 IS NOT NULL
2252 OR l_addr_details.address2 IS NOT NULL
2253 OR l_addr_details.reg1 IS NOT NULL
2254 OR l_addr_details.reg2 IS NOT NULL
2255 OR l_addr_details.country IS NOT NULL THEN
2256 Tag(x,r,'Adres','_START_','B'); -- + address
2257 IF l_addr_details.style = 'NL' THEN
2258 Tag(x,r,'StraatadresNederland','_START_','B'); -- + NL address
2259 Tag(x,r,'Straatnaam',l_addr_details.reg1,'B'); -- street name
2260 Tag(x,r,'Huisnr',l_addr_details.info13,'B'); -- house number
2261 Tag(x,r,'Huisnrtoevoeging',l_addr_details.info14,'B'); -- house number addition
2262 Tag(x,r,'Postcd',l_addr_details.postal,'B'); -- postal code
2263 Tag(x,r,'Woonplaatsnaam',l_addr_details.town,'B'); -- city
2264 Tag(x,r,'Woonbootverwijzing',REPLACE(REPLACE(l_addr_details.info15,'Y','AB')
2265 ,'N',''),'X'); -- houseboat
2266 Tag(x,r,'Woonbootverwijzing',get_look_up_value('HR_NL_YES_NO',
2267 l_addr_details.info15,l_lang),'R');
2268 Tag(x,r,'Woonwagenverwijzing',REPLACE(REPLACE(l_addr_details.info16,'Y','WW')
2269 ,'N',''),'X'); -- caravan
2270 Tag(x,r,'Woonwagenverwijzing',get_look_up_value('HR_NL_YES_NO',
2271 l_addr_details.info16,l_lang),'R');
2272 Tag(x,r,'StraatadresNederland','_END_','B'); -- - NL address
2273 ELSIF l_addr_details.style = 'NL_GLB' THEN
2274 Tag(x,r,'StraatadresBuitenland','_START_','B'); -- + foriegn address
2275 Tag(x,r,'StraatnaamBuitenland',l_addr_details.info13,'B'); -- street name
2276 Tag(x,r,'HuisnrBuitenland',l_addr_details.info14,'B'); -- house number
2277 Tag(x,r,'PostcdBuitenland',l_addr_details.postal,'B'); -- postal code
2278 Tag(x,r,'WoonplaatsnaamBuitenland',l_addr_details.town,'B'); -- city
2279 Tag(x,r,'RegionaamBuitenland',l_addr_details.reg1,'B'); -- region name
2280 Tag(x,r,'LocatieomsBuitenland',l_addr_details.info15,'B'); -- location desr
2281 Tag(x,r,'LandencdIso',l_addr_details.info16,'B'); -- iso country code
2282 Tag(x,r,'Landsnaam',l_addr_details.country,'B'); -- country name
2283 Tag(x,r,'StraatadresBuitenland','_END_','B'); -- - foriegn address
2284 END IF;
2285 Tag(x,r,'Adres','_END_','B'); -- - address
2286 END IF;
2287 Tag(x,r,'Arbeidsverhouding','_START_','B'); -- + assignment details*
2288 Tag(x,r,'IndLoonheffingskorting',l_emp_details.wage_tax_discount,'X'); -- wage tax disc
2289 Tag(x,r,'IndLoonheffingskorting',get_look_up_value('HR_NL_YES_NO',
2290 REPLACE(REPLACE(l_emp_details.wage_tax_discount,'1','Y'),'2','N'),l_lang),'R');
2291 Tag(x,r,'NrInkomstenverhouding',l_emp_details.asg_seq,'B'); -- NumIV override/asg seq
2292 Tag(x,r,'CdLoonbelastingtabel',l_emp_details.wage_tax_table,'B'); -- wage tax table
2293 Tag(x,r,'DatE',nvl(l_emp_details.end_date,l_emp_details.ass_end_date),'X'); -- end date income relation
2294 Tag(x,r,'DatE',fnd_date.date_to_chardate(to_date(nvl(l_emp_details.end_date
2295 ,l_emp_details.ass_end_date), 'YYYYMMDD')),'R');
2296 Tag(x,r,'MeldingWazo','_START_','B'); -- + wazo details
2297 IF (p_type = 'R' AND l_withdrawl_flag.flag = 'Y') OR p_type = 'W' THEN
2298 Tag(x,r,'IndVerzoekTotIntrekken','1','X'); -- withdrawal code
2299 Tag(x,r,'IndVerzoekTotIntrekken',get_look_up_value('HR_NL_YES_NO','Y',l_lang),'R');
2300 END IF;
2301 Tag(x,r,'ReferentieMelding',nvl(l_abs_info.sick_ref,l_emp_details.emp_num),'B'); -- sickness reference
2302 Tag(x,r,'DatTijdAanbiedenKetenpartij',to_char(SYSDATE, 'YYYYMMDDHH24MISS'),'X'); -- time stamp
2303 Tag(x,r,'DatTijdAanbiedenKetenpartij',fnd_date.date_to_charDT(sysdate),'R');
2304 Tag(x,r,'ToelMelding',l_add_info.text,'B'); -- additional details
2305 Tag(x,r,'IndDirecteUitkering',l_abs_info.code_pymt,'X'); -- code payment
2306 Tag(x,r,'IndDirecteUitkering',get_look_up_value('HR_NL_YES_NO',
2307 REPLACE(REPLACE(l_abs_info.code_pymt,'1','Y'),'2','N'),l_lang),'R');
2308 Tag(x,r,'CdRedenAanvraagWazo',l_abs_info.wazo_reason,'X'); -- reason for wazo report
2309 Tag(x,r,'CdRedenAanvraagWazo',get_look_up_value('HR_NL_DS_WAZO_REASONS',
2310 l_abs_info.wazo_reason,l_lang),'R');
2311 Tag(x,r,'IndSpecialeSituatie',l_abs_info.spl_sit,'X'); -- special situation
2312 Tag(x,r,'IndSpecialeSituatie',get_look_up_value('HR_NL_YES_NO',
2313 REPLACE(REPLACE(l_abs_info.spl_sit,'1','Y'),'2','N'),l_lang),'R');
2314 Tag(x,r,'DatGewensteAanvangWazo',l_abs_info.wazo_st_dt,'X'); -- WAZO date
2315 Tag(x,r,'DatGewensteAanvangWazo',fnd_date.date_to_chardate(
2316 to_date(l_abs_info.wazo_st_dt,'YYYYMMDD')),'R');
2317 IF l_abs_info.wazo_reason='01' THEN
2318 Tag(x,r,'DatVermoedelijkeBevalling',l_abs_info.est_dob,'X'); -- Estimated DOB
2319 Tag(x,r,'DatVermoedelijkeBevalling',fnd_date.date_to_chardate(
2320 to_date(l_abs_info.est_dob,'YYYYMMDD')),'R');
2321 END IF;
2322 IF l_abs_info.contact_name IS NOT NULL
2323 OR l_abs_info.est_name IS NOT NULL
2324 OR l_abs_info.gender IS NOT NULL
2325 OR l_abs_info.contact_ph_no IS NOT NULL THEN
2326 Tag(x,r,'ContactGegevens','_START_','B'); -- + contact details -sickness
2327 Tag(x,r,'NaamContactpersoonAfd',l_abs_info.contact_name,'B'); -- name
2328 Tag(x,r,'OmschrijvingLokaleVestiging',l_abs_info.est_name,'B'); -- Establishment
2329 Tag(x,r,'Geslacht',l_abs_info.gender,'X'); -- gender
2330 Tag(x,r,'Geslacht',get_look_up_value('HR_NL_DS_GENDER',
2331 l_abs_info.gender,l_lang),'R');
2332 Tag(x,r,'TelefoonnrContactpersoonAfd',l_abs_info.contact_ph_no,'B'); -- telephone
2333 Tag(x,r,'ContactGegevens','_END_','B'); -- - contact details
2334 ELSIF l_emp_details.contact_per_or_dept IS NOT NULL
2335 OR l_emp_details.ph_no_contact IS NOT NULL
2336 OR l_emp_details.est_name IS NOT NULL
2337 OR l_emp_details.gender_contact IS NOT NULL
2338 THEN
2339 Tag(x,r,'ContactGegevens','_START_','B'); -- + contact details -organization
2340 Tag(x,r,'NaamContactpersoonAfd',l_emp_details.contact_per_or_dept,'B'); -- name
2341 Tag(x,r,'OmschrijvingLokaleVestiging',l_emp_details.est_name,'B'); -- Establishment
2342 Tag(x,r,'Geslacht',l_emp_details.gender_contact,'X'); -- gender
2343 Tag(x,r,'Geslacht',get_look_up_value('HR_NL_DS_GENDER',
2344 l_emp_details.gender_contact,l_lang),'R');
2345 Tag(x,r,'TelefoonnrContactpersoonAfd',l_emp_details.ph_no_contact,'B'); -- telephone
2346 Tag(x,r,'ContactGegevens','_END_','B'); -- - contact details
2347 END IF;
2348 Tag(x,r,'MeldingWazo','_END_','B'); -- - wazo details
2349 Tag(x,r,'Arbeidsverhouding','_END_','B'); -- - assignment details*
2350 Tag(x,r,'NatuurlijkPersoon','_END_','B'); -- - employee data*
2351 Tag(x,r,'AdministratieveEenheid','_END_','B'); -- - employer*
2352 Tag(x,r,'UwvZwWazoMelding','_END_','B'); -- wazo report*
2353
2354 l_file_name := l_emp_details.emp_num||'_'||l_abs_info.wazo_st_dt||'_WAZO';
2355 IF p_type in ('W','R') THEN
2356 l_file_name := l_file_name||'_'||p_type;
2357 END IF;
2358 l_file_name := l_file_name||'.xml';
2359
2360 write_file(xXMLTable, l_file_name);
2361
2362 write_report(rXMLTable);
2363
2364 hr_utility.trace('X====gen_body_xml_main=====================================X');
2365 EXCEPTION
2366 WHEN others THEN
2367 hr_utility.trace('Exception in gen_body_xml_main SQL-ERRM :'||SQLERRM);
2368 END gen_body_xml_main;
2369
2370 /**************************************************************************/
2371
2372 PROCEDURE gen_body_xml
2373 IS
2374
2375 l_payroll_action_id NUMBER;
2376 l_asg_action_id NUMBER;
2377 l_org_struct_id NUMBER := NULL;
2378 l_person_id NUMBER := NULL;
2379 l_org_id NUMBER := NULL;
2380 l_bg_id NUMBER;
2381 l_start_date DATE := NULL;
2382 l_end_date DATE := NULL;
2383 l_type VARCHAR2(1) := NULL;
2384 /**************************************************************************/
2385 CURSOR cur_get_child_act_id (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
2386 SELECT assignment_action_id ass_act_id
2387 FROM pay_assignment_actions
2388 WHERE source_action_id = c_assignment_action_id;
2389 /**************************************************************************/
2390 CURSOR get_parent_asg_ids(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
2391 SELECT locked_action_id parent_id
2392 FROM pay_action_interlocks
2393 WHERE locking_action_id = c_assignment_action_id;
2394 /**************************************************************************/
2395 CURSOR get_pact_id(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
2396 SELECT ppa.payroll_action_id
2397 FROM pay_payroll_actions ppa
2398 ,pay_assignment_actions paa
2399 WHERE paa.payroll_action_id = ppa.payroll_action_id
2400 AND paa.assignment_action_id = c_assignment_action_id;
2401 /**************************************************************************/
2402
2403 BEGIN
2404 hr_utility.trace('+====gen_body_xml==========================================+');
2405
2406 l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
2407 hr_utility.trace('TRANSFER_PAYROLL_ACTION_ID = '||l_payroll_action_id);
2408
2409 l_asg_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
2410 hr_utility.trace('TRANSFER_ACT_ID = '||l_asg_action_id);
2411
2412 OPEN get_pact_id(l_asg_action_id);
2413 FETCH get_pact_id INTO l_payroll_action_id;
2414 CLOSE get_pact_id;
2415
2416 hr_utility.trace('l_payroll_action_id = '||l_payroll_action_id);
2417 get_all_parameters (
2418 p_payroll_action_id => l_payroll_action_id
2419 ,p_org_struct_id => l_org_struct_id
2420 ,p_person_id => l_person_id
2421 ,p_org_id => l_org_id
2422 ,p_bg_id => l_bg_id
2423 ,p_start_date => l_start_date
2424 ,p_end_date => l_end_date
2425 ,p_type => l_type);
2426
2427 IF l_type = 'I' THEN
2428 hr_utility.trace('Initial Report');
2429 FOR v_child_act_id IN cur_get_child_act_id(l_asg_action_id)
2430 LOOP
2431 hr_utility.trace('Child Assignment Action ID :'||v_child_act_id.ass_act_id);
2432 gen_body_xml_main(l_type, v_child_act_id.ass_act_id);
2433 END LOOP;
2434 ELSIF l_type IN ('W','R') THEN
2435 hr_utility.trace('Withdrawal/Regeneration Report');
2436 FOR v_parent_asg_ids in get_parent_asg_ids(l_asg_action_id)
2437 LOOP
2438 hr_utility.trace('Parent Assignment Action ID :'||v_parent_asg_ids.parent_id);
2439 gen_body_xml_main(l_type, v_parent_asg_ids.parent_id);
2440 END LOOP;
2441 END IF;
2442 hr_utility.trace('X====gen_body_xml==========================================X');
2443 EXCEPTION
2444 WHEN others THEN
2445 hr_utility.trace('Exception in gen_body_xml SQL-ERRM :'||SQLERRM);
2446 END gen_body_xml;
2447
2448 END PER_NL_WAZO_ARCHIVE;
2449