[Home] [Help]
PACKAGE BODY: APPS.PER_NL_LTSRR_ARCHIVE
Source
1 PACKAGE BODY PER_NL_LTSRR_ARCHIVE as
2 /* $Header: penlltsrr.pkb 120.5.12020000.1 2012/07/17 08:55:43 asudhaka noship $ */
3
4 EOL VARCHAR2(5) := fnd_global.newline();
5 g_SOURCE VARCHAR2(4000);
6 g_TARGET VARCHAR2(4000);
7
8 /**************************************************************************/
9 -- typecasts CLOB to BLOB (binary conversion)
10
11 FUNCTION c2b( c IN CLOB ) RETURN BLOB
12 IS
13 pos PLS_INTEGER := 1;
14 buffer RAW(32767);
15 res BLOB;
16 lob_len PLS_INTEGER := dbms_lob.getlength(c);
17 BEGIN
18 -- hr_utility.trace('+====c2b===================================================+');
19 dbms_lob.createtemporary(res,TRUE);
20 dbms_lob.open(res,dbms_lob.lob_readwrite);
21 LOOP
22 buffer := utl_raw.cast_to_raw( dbms_lob.substr( c, 16000, pos ) );
23 IF utl_raw.length( buffer ) > 0 THEN
24 dbms_lob.writeappend(res,utl_raw.length(buffer),buffer);
25 END IF;
26 pos := pos + 16000;
27 EXIT WHEN pos > lob_len;
28 END LOOP;
29 -- hr_utility.trace('X====c2b===================================================X');
30 RETURN res;
31 EXCEPTION
32 WHEN others THEN
33 hr_utility.trace('Exception in c2b SQL-ERRM : '||SQLERRM);
34 END c2b;
35 /**************************************************************************/
36
37 /*------------------------------------------------------------------------------
38 |Name : clean_XML |
39 |Type : Procedure |
40 |Description: Procedure to replace unallowed characters in XML Values |
41 -------------------------------------------------------------------------------*/
42
43
44 FUNCTION clean_XML(P_STRING IN VARCHAR2) RETURN VARCHAR2 AS
45 l_string varchar2(1000);
46 BEGIN
47 l_string := p_string;
48 l_string := REPLACE(l_string, '&', '&'||'amp;');
49 l_string := REPLACE(l_string, '<', '&'||'lt;'); --#60
50 l_string := REPLACE(l_string, '>', '&'||'gt;'); --#62
51 l_string := REPLACE(l_string, '''','&'||'apos;');
52 l_string := REPLACE(l_string, '"', '&'||'quot;');
53
54 --Diacritical marks handling--
55 IF g_SOURCE IS NOT NULL THEN
56 l_string:= translate(l_string,g_SOURCE,g_TARGET);
57 END IF;
58 RETURN l_string;
59 EXCEPTION
60 WHEN others THEN
61 hr_utility.trace('Exception in clean_XML SQL-ERRM :'||SQLERRM);
62 RETURN l_string;
63 END clean_XML;
64 /**************************************************************************/
65
66
67
68
69
70 /*-----------------------------------------------------------------------------
71 |Name : write_file |
72 |Type : Procedure |
73 |Description: Procedure to create an XML file output in utl_file_dir |
74 -------------------------------------------------------------------------------*/
75
76 PROCEDURE write_file(p_XMLTable IN tXMLTable
77 ,p_file_name IN VARCHAR) IS
78
79 l_str1 VARCHAR2(80) ;
80 l_xml_element VARCHAR2(800);
81 l_file UTL_FILE.FILE_TYPE;
82 l_directory_path VARCHAR2(500);
83 l_file_name VARCHAR2(50);
84
85 CURSOR cur_get_directory_path IS
86 SELECT value
87 FROM v$parameter
88 WHERE lower (name) = 'utl_file_dir';
89
90 BEGIN
91 hr_utility.trace('+====write_file============================================+');
92 OPEN cur_get_directory_path;
93 FETCH cur_get_directory_path INTO l_directory_path;
94 CLOSE cur_get_directory_path;
95
96 IF INSTR(l_directory_path,',') > 0 THEN
97 l_directory_path := SUBSTR(l_directory_path, 1, INSTR(l_directory_path,',')-1);
98 END IF;
99
100 l_file := utl_file.fopen(l_directory_path, p_file_name, 'W');
101 l_str1 := '<?xml version="1.0" encoding="ISO-8859-1"?>'||EOL;
102 utl_file.put_line(l_file, l_str1);
103
104 IF p_XMLTable.COUNT > 0 THEN
105
106 FOR table_counter IN p_XMLTable.FIRST .. p_XMLTable.LAST
107 LOOP
108 IF p_XMLTable(table_counter).tagvalue = '_START_' THEN
109 l_xml_element := '<' || p_XMLTable(table_counter).tagname || '>';
110 ELSIF p_XMLTable(table_counter).tagvalue = '_END_' THEN
111 l_xml_element := '</' || p_XMLTable(table_counter).tagname || '>';
112 ELSE
113 l_xml_element := '<' || p_XMLTable(table_counter).tagname ||
114 '>'|| clean_XML(p_XMLTable(table_counter).tagvalue) ||
115 '</'||p_XMLTable(table_counter).tagname || '>';
116 END IF;
117 -- IF p_XMLTable(table_counter).tagvalue IS NOT NULL THEN
118 utl_file.put_line(l_file,l_xml_element);
119 -- END IF;
120 END LOOP;
121 END IF;
122 utl_file.fclose(l_file);
123 hr_utility.trace('Written file : '||l_directory_path||'/'||p_file_name);
124 hr_utility.trace('x====write_file============================================x');
125 EXCEPTION
126 WHEN others THEN
127 hr_utility.trace('Exception in write_file SQL-ERRM : '||SQLERRM);
128 hr_utility.raise_error;
129 END write_file;
130 /**************************************************************************/
131
132
133
134
135
136 /*-----------------------------------------------------------------------------
137 |Name : write_report |
138 |Type : Procedure |
139 |Description: Procedure to append the assignment details to main report XML |
140 -------------------------------------------------------------------------------*/
141
142 PROCEDURE write_report(p_XMLTable IN tXMLTable) IS
143 l_xml_element VARCHAR2(800);
144 l_clob CLOB;
145 BEGIN
146 hr_utility.trace('+====write_report==========================================+');
147 dbms_lob.createtemporary(l_clob, FALSE, DBMS_LOB.CALL);
148 dbms_lob.open(l_clob, DBMS_LOB.LOB_READWRITE);
149
150 IF p_XMLTable.COUNT > 0 THEN
151
152 FOR table_counter IN p_XMLTable.FIRST .. p_XMLTable.LAST
153 LOOP
154 IF p_XMLTable(table_counter).tagvalue = '_START_' THEN
155 l_xml_element := '<' || p_XMLTable(table_counter).tagname || '>'||EOL;
156 ELSIF p_XMLTable(table_counter).tagvalue = '_END_' THEN
157 l_xml_element := '</' || p_XMLTable(table_counter).tagname || '>'||EOL;
158 ELSE
159 l_xml_element := '<' || p_XMLTable(table_counter).tagname ||
160 '>'|| clean_XML(p_XMLTable(table_counter).tagvalue) ||
161 '</'||p_XMLTable(table_counter).tagname || '>'||EOL;
162 END IF;
163 -- IF p_XMLTable(table_counter).tagvalue IS NOT NULL THEN
164 dbms_lob.writeappend(l_clob, LENGTH(l_xml_element), l_xml_element);
165 -- END IF;
166 END LOOP;
167 END IF;
168 IF l_clob IS NOT NULL THEN
169 pay_core_files.write_to_magtape_lob(c2b(l_clob));
170 END IF;
171 hr_utility.trace('x====write_report==========================================x');
172 EXCEPTION
173 WHEN others THEN
174 hr_utility.trace('Exception in write_report SQL-ERRM : '||SQLERRM);
175 hr_utility.raise_error;
176 END write_report;
177 /**************************************************************************/
178
179 FUNCTION get_look_up_value(p_lookup_type IN VARCHAR2
180 ,p_lookup_code IN VARCHAR2
181 ,p_language IN VARCHAR2 DEFAULT 'US')
182 RETURN VARCHAR2 IS
183
184 CURSOR c_get_look_up_value (c_lookup_type IN VARCHAR2
185 ,c_lookup_code IN VARCHAR2
186 ,c_language IN VARCHAR2) IS
187 SELECT meaning
188 FROM fnd_lookup_values
189 WHERE lookup_type = c_lookup_type
190 AND language = c_language
191 AND lookup_code = c_lookup_code;
192
193 BEGIN
194 FOR r_get_look_up_value in c_get_look_up_value(p_lookup_type,p_lookup_code,p_language)
195 LOOP
196 RETURN r_get_look_up_value.meaning;
197 END LOOP;
198 RETURN NULL;
199 EXCEPTION
200 WHEN TOO_MANY_ROWS THEN
201 RETURN NULL;
202 WHEN NO_DATA_FOUND THEN
203 RETURN NULL;
204 WHEN others THEN
205 hr_utility.trace('Exception in get_look_up_value SQL-ERRM : '||SQLERRM);
206
207 END get_look_up_value;
208 /**************************************************************************/
209
210
211
212 /*------------------------------------------------------------------------------
213 |Name : GET_PARAMETER |
214 |Type : Function |
215 |Description : Funtion to get the parameters of the archive process |
216 -------------------------------------------------------------------------------*/
217
218 FUNCTION get_parameter (p_parameter_string IN VARCHAR2
219 ,p_token IN VARCHAR2
220 ,p_segment_number IN NUMBER DEFAULT NULL )
221 RETURN VARCHAR2 IS
222
223 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
224 l_start_pos NUMBER;
225 l_delimiter varchar2(1):=' ';
226
227 BEGIN
228 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
229 --
230 IF l_start_pos = 0 THEN
231 l_delimiter := '|';
232 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
233 end if;
234
235 IF l_start_pos <> 0 THEN
236 l_start_pos := l_start_pos + length(p_token||'=');
237 l_parameter := substr(p_parameter_string,
238 l_start_pos,
239 instr(p_parameter_string||' ',
240 l_delimiter,l_start_pos)
241 - l_start_pos);
242 IF p_segment_number IS NOT NULL THEN
243 l_parameter := ':'||l_parameter||':';
244 l_parameter := substr(l_parameter,
245 instr(l_parameter,':',1,p_segment_number)+1,
246 instr(l_parameter,':',1,p_segment_number+1) -1
247 - instr(l_parameter,':',1,p_segment_number));
248 END IF;
249 END IF;
250 RETURN l_parameter;
251 END get_parameter;
252 /**************************************************************************/
253
254
255
256
257
258 /*------------------------------------------------------------------------------
259 |Name : GET_ALL_PARAMETERS |
260 |Type : Procedure |
261 |Description: Procedure which returns all the parameters of the archive process|
262 -------------------------------------------------------------------------------*/
263
264 PROCEDURE get_all_parameters (p_payroll_action_id IN NUMBER
265 ,p_org_struct_id OUT NOCOPY NUMBER
266 ,p_person_id OUT NOCOPY NUMBER
267 ,p_org_id OUT NOCOPY NUMBER
268 ,p_bg_id OUT NOCOPY NUMBER
269 ,p_start_date OUT NOCOPY DATE
270 ,p_end_date OUT NOCOPY DATE
271 ,p_type OUT NOCOPY VARCHAR) IS
272
273 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
274 SELECT TO_NUMBER(get_parameter(legislative_parameters,'ORG_STRUCT_ID')) org_struct_id
275 ,TO_NUMBER(get_parameter(legislative_parameters,'PERSON_ID')) person_id
276 ,TO_NUMBER(get_parameter(legislative_parameters,'ORG_ID')) org_id
277 ,business_group_id bg_id
278 ,start_date start_date
279 ,effective_date end_date
280 ,get_parameter(legislative_parameters,'TYPE') report_type
281 FROM pay_payroll_actions
282 WHERE payroll_action_id = p_payroll_action_id;
283
284 l_param csr_parameter_info%ROWTYPE;
285
286 BEGIN
287 OPEN csr_parameter_info (p_payroll_action_id);
288 FETCH csr_parameter_info INTO l_param;
289 CLOSE csr_parameter_info;
290
291 p_org_struct_id := l_param.org_struct_id;
292 p_person_id := l_param.person_id;
293 p_org_id := l_param.org_id;
294 p_bg_id := l_param.bg_id;
295 p_type := l_param.report_type;
296 p_start_date := l_param.start_date;
297 p_end_date := l_param.end_date;
298
299 EXCEPTION
300 WHEN others THEN
301 hr_utility.trace('Exception in get_all_parameters SQLERRM: '||SQLERRM);
302 END get_all_parameters;
303 /**************************************************************************/
304
305
306
307 /*------------------------------------------------------------------------------
308 |Name : EMP_CHECK |
309 |Type : Function |
310 |Description : Function required for valueset HR_NL_EMPLOYEE_LTSRR |
311 -------------------------------------------------------------------------------*/
312
313 FUNCTION emp_check (p_bg_id IN NUMBER
314 ,p_org_struct_id IN NUMBER
315 ,p_org_id IN NUMBER
316 ,p_person_id IN NUMBER
317 ,p_start_date IN DATE
318 ,p_end_date IN DATE) RETURN NUMBER IS
319
320 CURSOR csr_org_check(c_bg_id IN NUMBER
321 ,c_org_struct_id IN NUMBER
322 ,c_org_id IN NUMBER
323 ,c_person_id IN NUMBER
324 ,c_start_date IN DATE
325 ,c_end_date IN DATE) IS
326 SELECT 1
327 FROM per_all_assignments_f paa
328 ,per_assignment_status_types past
329 ,per_all_people_f pap
330 ,per_org_structure_versions posv
331 ,per_absence_attendances pab
332 ,per_absence_attendance_types paat
333 WHERE posv.organization_structure_id = c_org_struct_id
334 AND posv.date_from <= c_start_date
335 AND nvl (posv.date_to
336 ,hr_general.end_of_time) >= c_end_date
337 AND (
338 paa.organization_id IN
339 (
340 (
341 SELECT pose.organization_id_child
342 FROM per_org_structure_elements pose
343 WHERE pose.org_structure_version_id = posv.org_structure_version_id
344 START WITH pose.organization_id_parent = c_org_id
345 CONNECT BY PRIOR organization_id_child = organization_id_parent
346 )
347 UNION
348 (
349 SELECT c_org_id
350 FROM dual
351 )
352 )
353 OR nvl (paa.establishment_id,- 1) = c_org_id
354 )
355 AND paa.person_id = c_person_id
356 AND paat.absence_category = 'S'
357 AND pab.abs_information_category = 'NL_S'
358 AND pab.abs_information4 = 'L'
359 AND pab.business_group_id = pap.business_group_id
360 AND (pab.date_end + 1) BETWEEN c_start_date
361 AND c_end_date
362 AND pab.absence_attendance_type_id = paat.absence_attendance_type_id
363 AND pab.business_group_id = paat.business_group_id
364 AND paa.business_group_id = c_bg_id
365 AND pap.person_id = c_person_id
366 AND pap.business_group_id = paa.business_group_id;
367 /**************************************************************************/
368 CURSOR csr_org_struct_check(c_bg_id IN NUMBER
369 ,c_org_struct_id IN NUMBER
370 ,c_org_id IN NUMBER
371 ,c_person_id IN NUMBER
372 ,c_start_date IN DATE
373 ,c_end_date IN DATE) IS
374 SELECT 1
375 FROM per_all_assignments_f paa
376 ,per_all_people_f pap
377 ,per_org_structure_versions posv
378 ,per_absence_attendances pab
379 ,per_absence_attendance_types paat
380 WHERE posv.organization_structure_id = c_org_struct_id
381 AND posv.date_from <= c_start_date
382 AND nvl (posv.date_to
383 ,hr_general.end_of_time) >= c_end_date
384 AND paa.person_id = c_person_id
385 AND paa.business_group_id = c_bg_id
386 AND pap.person_id = paa.person_id
387 AND pap.business_group_id = paa.business_group_id
388 AND pab.business_group_id = pap.business_group_id
389 AND pab.person_id = pap.person_id
390 AND (pab.DATE_END + 1) BETWEEN c_start_date
391 AND c_end_date
392 AND pab.absence_attendance_type_id = paat.absence_attendance_type_id
393 AND pab.business_group_id = paat.business_group_id
394 AND paat.absence_category = 'S'
395 AND pab.abs_information_category = 'NL_S'
396 AND pab.abs_information4 = 'L'
397 AND (
398 hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
399 ,paa.organization_id) IS NOT NULL
400 OR per_nl_ltsrr_archive.org_check (pap.business_group_id
401 ,NULL
402 ,nvl (paa.establishment_id,- 1)
403 ,c_start_date
404 ,c_end_date) = 1
405 )
406 AND paa.organization_id IN
407 (
408 SELECT pose.organization_id_parent
409 FROM per_org_structure_elements pose
410 WHERE posv.org_structure_version_id = pose.org_structure_version_id
411 UNION
412 SELECT pose.organization_id_child
413 FROM per_org_structure_elements pose
414 WHERE posv.org_structure_version_id = pose.org_structure_version_id
415 );
416 /**************************************************************************/
417 l_return_val NUMBER := 0;
418 l_start_date DATE;
419 l_end_date DATE;
420
421 BEGIN
422 hr_utility.trace('+====emp_check============================================+');
423
424 IF p_org_id is not NULL THEN
425 BEGIN
426 OPEN csr_org_check(p_bg_id
427 ,p_org_struct_id
428 ,p_org_id
429 ,p_person_id
430 ,p_start_date
431 ,p_end_date );
432 FETCH csr_org_check INTO l_return_val;
433 CLOSE csr_org_check;
434
435 EXCEPTION
436 WHEN TOO_MANY_ROWS THEN
437 l_return_val := 1;
438 WHEN NO_DATA_FOUND THEN
439 null;
440 WHEN OTHERS THEN
441 hr_utility.trace('Exception in emp_check org. SQLERRM : '||SQLERRM);
442 END;
443 hr_utility.trace('l_return_val : '||l_return_val);
444 ELSIF p_org_struct_id is not NULL THEN
445 BEGIN
446 OPEN csr_org_struct_check(p_bg_id
447 ,p_org_struct_id
448 ,p_org_id
449 ,p_person_id
450 ,p_start_date
451 ,p_end_date );
452 FETCH csr_org_struct_check INTO l_return_val;
453 CLOSE csr_org_struct_check;
454 EXCEPTION
455 WHEN TOO_MANY_ROWS THEN
456 l_return_val := 1;
457 WHEN NO_DATA_FOUND THEN
458 NULL;
459 WHEN OTHERS THEN
460 hr_utility.trace('Exception in emp_check org_struct. SQLERRM : '||SQLERRM);
461 END;
462 hr_utility.trace('l_return_val : '||l_return_val);
463 END IF;
464 hr_utility.trace('X====emp_check============================================X');
465 RETURN l_return_val;
466 EXCEPTION
467 WHEN OTHERS THEN
468 hr_utility.trace('Exception in emp_check SQL-ERRM : '||SQLERRM);
469 END emp_check;
470 /**************************************************************************/
471
472
473
474
475
476 /*------------------------------------------------------------------------------
477 |Name : ORG_CHECK |
478 |Type : Function |
479 |Description : Function required for valueset HR_NL_EMPLOYER_LTSRR |
480 -------------------------------------------------------------------------------*/
481 FUNCTION org_check (p_bg_id IN NUMBER
482 ,p_org_struct_id IN NUMBER
483 ,p_org_id IN NUMBER
484 ,p_start_date IN DATE
485 ,p_end_date IN DATE)
486 RETURN NUMBER IS
487
488 CURSOR csr_org_struct_check (c_bg_id IN NUMBER
489 ,c_org_id IN NUMBER) IS
490
491 SELECT 1
492 FROM hr_organization_units hou
493 WHERE hou.business_group_id = c_bg_id
494 AND hou.organization_id = c_org_id
495 AND EXISTS
496 (
497 SELECT 1
498 FROM hr_all_organization_units hou1
499 ,hr_organization_information hoi1
500 WHERE hou1.business_group_id = c_bg_id
501 AND hoi1.org_information_context = 'NL_LE_TAX_DETAILS'
502 AND hoi1.org_information1 IS NOT NULL
503 AND hoi1.org_information2 IS NOT NULL
504 AND hou1.organization_id = hoi1.organization_id
505 AND hou1.organization_id = hou.organization_id
506 UNION
507 SELECT 1
508 FROM hr_all_organization_units hou2
509 ,hr_organization_information hoi2
510 WHERE hou2.business_group_id = c_bg_id
511 AND hoi2.org_information_context = 'NL_ORG_INFORMATION'
512 AND hoi2.org_information4 IS NOT NULL
513 AND hoi2.org_information3 IS NOT NULL
514 AND hou2.organization_id = hoi2.organization_id
515 AND hou2.organization_id = hou.organization_id
516 );
517 /**************************************************************************/
518 CURSOR csr_org_check (c_bg_id IN NUMBER
519 ,c_org_id IN NUMBER
520 ,c_org_struct_id IN NUMBER
521 ,c_start_date IN DATE
522 ,c_end_date IN DATE) IS
523
524 SELECT 1
525 FROM hr_organization_units hou
526 WHERE hou.organization_id = c_org_id
527 AND hou.business_group_id = c_bg_id
528 AND EXISTS
529 (
530 SELECT 1
531 FROM hr_organization_units hou1
532 ,hr_organization_information hoi1
533 WHERE hoi1.org_information_context = 'NL_ORG_INFORMATION'
534 AND hou1.business_group_id = c_bg_id
535 AND hou1.organization_id = hou.organization_id
536 AND hou1.organization_id = hoi1.organization_id
537 AND hoi1.org_information4 IS NOT NULL
538 AND hoi1.org_information3 IS NOT NULL
539 AND hou1.organization_id IN
540 (
541 SELECT pose.organization_id_parent
542 FROM per_org_structure_elements pose
543 ,per_org_structure_versions posv
544 WHERE posv.org_structure_version_id = pose.org_structure_version_id
545 AND posv.organization_structure_id = c_org_struct_id
546 AND posv.date_from <= c_start_date
547 AND nvl (posv.date_to
548 ,hr_general.end_of_time) >= c_end_date
549 UNION
550 SELECT pose.organization_id_child
551 FROM per_org_structure_elements pose
552 ,per_org_structure_versions posv
553 WHERE posv.org_structure_version_id = pose.org_structure_version_id
554 AND posv.organization_structure_id = c_org_struct_id
555 AND posv.date_from <= c_start_date
556 AND nvl (posv.date_to
557 ,hr_general.end_of_time) >= c_end_date
558 )
559 UNION
560 SELECT 1
561 FROM hr_organization_units hou2
562 ,hr_organization_information hoi2
563 WHERE hoi2.org_information_context = 'NL_LE_TAX_DETAILS'
564 AND hou2.business_group_id = c_bg_id
565 AND hou2.organization_id = hou.organization_id
566 AND hou2.organization_id = hoi2.organization_id
567 AND hoi2.org_information1 IS NOT NULL
568 AND hoi2.org_information2 IS NOT NULL
569 AND hou2.organization_id IN
570 (
571 SELECT pose.organization_id_parent
572 FROM per_org_structure_elements pose
573 ,per_org_structure_versions posv
574 WHERE posv.org_structure_version_id = pose.org_structure_version_id
575 AND posv.organization_structure_id = c_org_struct_id
576 AND posv.date_from <= c_start_date
577 AND nvl (posv.date_to
578 ,hr_general.end_of_time) >= c_end_date
579 UNION
580 SELECT pose.organization_id_child
581 FROM per_org_structure_elements pose
582 ,per_org_structure_versions posv
583 WHERE posv.org_structure_version_id = pose.org_structure_version_id
584 AND posv.organization_structure_id = c_org_struct_id
585 AND posv.date_from <= c_start_date
586 AND nvl (posv.date_to
587 ,hr_general.end_of_time) >= c_end_date
588 )
589 );
590 /**************************************************************************/
591
592 l_return_val NUMBER := 0;
593 l_start_date DATE;
594 l_end_date DATE;
595
596 BEGIN
597 --hr_utility.trace('+====org_check=============================================+');
598 IF p_org_struct_id is NULL THEN
599 BEGIN
600 OPEN csr_org_struct_check (p_bg_id,p_org_id);
601 FETCH csr_org_struct_check INTO l_return_val;
602 CLOSE csr_org_struct_check;
603
604 EXCEPTION
605 WHEN TOO_MANY_ROWS THEN
606 l_return_val := 1;
607 WHEN NO_DATA_FOUND THEN
608 NULL;
609 WHEN OTHERS THEN
610 hr_utility.trace('Exception in org_check org_struct. SQLERRM : '||SQLERRM);
611 END;
612 hr_utility.trace('l_return_val : '||l_return_val);
613 ELSE
614 BEGIN
615 OPEN csr_org_check (p_bg_id,p_org_id,p_org_struct_id,p_start_date,p_end_date);
616 FETCH csr_org_check INTO l_return_val;
617 CLOSE csr_org_check;
618
619 EXCEPTION
620 WHEN TOO_MANY_ROWS THEN
621 l_return_val := 1;
622 WHEN NO_DATA_FOUND THEN
623 NULL;
624 WHEN OTHERS THEN
625 hr_utility.trace('Exception in org_check org_struct1. SQLERRM : '||SQLERRM);
626 END;
627 hr_utility.trace('l_return_val : '||l_return_val);
628 END IF;
629 RETURN l_return_val;
630 --hr_utility.trace('X====org_check=============================================X');
631 EXCEPTION
632 WHEN OTHERS THEN
633 hr_utility.trace('Exception in org_check SQL-ERRM : '||SQLERRM);
634 END org_check;
635 /**************************************************************************/
636
637 /*--------------------------------------------------------------------
638 |Name : RANGE_CODE |
639 |Type : Procedure |
640 |Description: This procedure returns an sql string to select a range |
641 | of assignments eligible for reporting |
642 ----------------------------------------------------------------------*/
643
644
645 PROCEDURE range_code (pactid IN NUMBER
646 ,sqlstr OUT NOCOPY VARCHAR2) is
647 BEGIN
648 hr_utility.trace('+====range_code============================================+');
649 sqlstr := 'SELECT DISTINCT person_id
650 FROM per_all_people_f pap
651 ,pay_payroll_actions ppa
652 WHERE ppa.payroll_action_id = :payroll_action_id
653 AND ppa.business_group_id = pap.business_group_id
654 ORDER BY pap.person_id';
655 hr_utility.trace('X====range_code============================================X');
656 EXCEPTION
657 WHEN OTHERS THEN
658 -- Return cursor that selects no rows
659 hr_utility.trace('Exception in range_code SQL-ERRM : '||SQLERRM);
660 sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
661 END range_code;
662
663 /**************************************************************************/
664
665 PROCEDURE initialization_code(p_action_context_id IN NUMBER) IS
666
667 CURSOR c_usr_tab_col IS
668 SELECT put.user_table_id
669 ,puc.user_column_id
670 FROM pay_user_tables put
671 ,pay_user_columns puc
672 WHERE put.user_table_id = puc.user_table_id
673 AND put.legislation_code = puc.legislation_code
674 AND put.user_table_name = 'NL_DIACRITICAL_MARKS'
675 AND put.legislation_code = 'NL';
676 /**************************************************************************/
677 CURSOR c_src_dest_str(p_user_column_id IN NUMBER
678 , p_user_table_id IN NUMBER
679 , p_business_group_id IN NUMBER
680 , p_start_date IN DATE) IS
681 SELECT DISTINCT
682 upper (purf.row_low_range_or_name) source
683 ,upper (pucif.value) target
684 FROM pay_user_column_instances_f pucif
685 ,pay_user_rows_f purf
686 WHERE pucif.user_column_id = p_user_column_id
687 AND purf.user_table_id = p_user_table_id
688 AND pucif.user_row_id = purf.user_row_id
689 AND pucif.business_group_id = purf.business_group_id
690 AND pucif.business_group_id = p_business_group_id
691 AND p_start_date BETWEEN pucif.effective_start_date
692 AND pucif.effective_end_date
693 AND p_start_date BETWEEN purf.effective_start_date
694 AND purf.effective_end_date;
695 /**************************************************************************/
696 CURSOR c_bg_stdate(p_payroll_action_id IN NUMBER) IS
697 SELECT business_group_id bg_id
698 ,start_date start_date
699 FROM pay_payroll_actions
700 WHERE payroll_action_id = p_payroll_action_id;
701 /**************************************************************************/
702 l_user_table_id NUMBER;
703 l_user_column_id NUMBER;
704 l_bg_id NUMBER;
705 l_start_date DATE;
706
707 BEGIN
708 hr_utility.trace('+====initialization_code===================================+');
709 IF Instr ('a', COMPOSE('a'|| UNISTR('\0301')) ) = 0 THEN
710 OPEN c_bg_stdate(p_action_context_id);
711 FETCH c_bg_stdate INTO l_bg_id,l_start_date;
712 CLOSE c_bg_stdate;
713
714 OPEN c_usr_tab_col;
715 FETCH c_usr_tab_col INTO l_user_table_id, l_user_column_id;
716 CLOSE c_usr_tab_col;
717
718 FOR I in c_src_dest_str(l_user_column_id, l_user_table_id, l_bg_id, l_start_date)
719 LOOP
720 g_SOURCE := g_SOURCE||upper(I.Source)||lower(I.Source);
721 g_TARGET := g_TARGET||upper(I.Target)||lower(I.Target);
722 END LOOP;
723
724 hr_utility.trace('g_SOURCE :'||g_SOURCE);
725 hr_utility.trace('g_TARGET :'||g_TARGET);
726 END IF;
727 hr_utility.trace('X====initialization_code===================================X');
728 EXCEPTION
729 WHEN OTHERS THEN
730 hr_utility.trace('Exception in init_code SQL-ERRM : '||SQLERRM);
731 END initialization_code;
732 /**************************************************************************/
733 /*--------------------------------------------------------------------
734 |Name : ASSIGNMENT_ACTION_CODE |
735 |Type : Procedure |
736 |Description: This procedure further filters which assignments are |
737 | eligible for reporting |
738 ----------------------------------------------------------------------*/
739
740
741 PROCEDURE assignment_action_code (p_payroll_action_id IN NUMBER
742 ,p_start_person_id IN NUMBER
743 ,p_end_person_id IN NUMBER
744 ,p_chunk IN NUMBER) IS
745
746 CURSOR csr_get_asg_person (p_person_id IN NUMBER
747 ,p_start_person_id IN NUMBER
748 ,p_end_person_id IN NUMBER
749 ,p_payroll_action_id IN NUMBER
750 ,p_start_date IN DATE
751 ,p_end_date IN DATE) IS
752 SELECT DISTINCT
753 paa.assignment_id assignment_id
754 , pab.absence_attendance_id abs_att_id
755 FROM per_absence_attendance_types paat
756 , per_absence_attendances pab
757 , pay_payroll_actions ppa
758 , per_all_assignments_f paa
759 , per_all_people_f pap
760 WHERE paat.absence_category = 'S'
761 AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
762 AND paat.business_group_id = pab.business_group_id
763 AND (pab.date_end + 1) BETWEEN p_start_date
764 AND p_end_date
765 AND pab.business_group_id = pap.business_group_id
766 AND pap.person_id = p_person_id
767 AND p_person_id BETWEEN p_start_person_id
768 AND p_end_person_id
769 AND pap.person_id = paa.person_id
770 AND pab.person_id = pap.person_id
771 AND paa.business_group_id = pap.business_group_id
772 AND paa.assignment_id = pab.abs_information5
773 AND pab.abs_information_category = 'NL_S'
774 AND pab.abs_information4 = 'L'
775 AND pap.business_group_id = ppa.business_group_id
776 AND ppa.payroll_action_id = p_payroll_action_id;
777 /**************************************************************************/
778
779 CURSOR csr_get_asg_org (p_org_id IN NUMBER
780 ,p_org_struct_id IN NUMBER
781 ,p_start_person_id IN NUMBER
782 ,p_end_person_id IN NUMBER
783 ,p_payroll_action_id IN NUMBER
784 ,p_start_date IN DATE
785 ,p_end_date IN DATE) IS
786 SELECT DISTINCT
787 paa.assignment_id assignment_id
788 , pab.absence_attendance_id abs_att_id
789 FROM per_absence_attendance_types paat
790 , per_absence_attendances pab
791 , pay_payroll_actions ppa
792 , per_org_structure_versions posv
793 , per_all_assignments_f paa
794 , per_all_people_f pap
795 WHERE posv.organization_structure_id = p_org_struct_id
796 AND posv.date_from <= p_end_date
797 AND nvl (posv.date_to, hr_general.end_of_time) >= p_start_date
798 AND (
799 paa.organization_id IN
800 (
801 (
802 SELECT pose.organization_id_child
803 FROM per_org_structure_elements pose
804 WHERE pose.org_structure_version_id = posv.org_structure_version_id
805 START WITH pose.organization_id_parent = p_org_id
806 CONNECT BY PRIOR organization_id_child = organization_id_parent
807 )
808 UNION
809 (
810 SELECT p_org_id
811 FROM dual
812 )
813 )
814 OR nvl (paa.establishment_id, - 1) = p_org_id
815 )
816 AND pap.person_id = paa.person_id
817 AND paa.person_id BETWEEN p_start_person_id
818 AND p_end_person_id
819 AND paa.business_group_id = pap.business_group_id
820 AND pab.person_id = pap.person_id
821 AND (pab.date_end + 1) BETWEEN p_start_date
822 AND p_end_date
823 AND paat.absence_category = 'S'
824 AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
825 AND paat.business_group_id = pab.business_group_id
826 AND pab.business_group_id = pap.business_group_id
827 AND paa.assignment_id = pab.abs_information5
828 AND pab.abs_information_category = 'NL_S'
829 AND pab.abs_information4 = 'L'
830 AND pap.business_group_id = ppa.business_group_id
831 AND ppa.payroll_action_id = p_payroll_action_id;
832 /**************************************************************************/
833
834 CURSOR csr_get_asg_hier (p_org_struct_id IN NUMBER
835 ,p_start_person_id IN NUMBER
836 ,p_end_person_id IN NUMBER
837 ,p_payroll_action_id IN NUMBER
838 ,p_start_date IN DATE
839 ,p_end_date IN DATE) IS
840
841 SELECT DISTINCT
842 paa.assignment_id assignment_id
843 , pab.absence_attendance_id abs_att_id
844 FROM per_absence_attendance_types paat
845 , per_absence_attendances pab
846 , per_all_assignments_f paa
847 , per_all_people_f pap
848 , pay_payroll_actions ppa
849 , per_org_structure_versions posv
850 WHERE posv.organization_structure_id = p_org_struct_id
851 AND pap.person_id BETWEEN p_start_person_id
852 AND p_end_person_id
853 AND pap.business_group_id = ppa.business_group_id
854 AND ppa.payroll_action_id = p_payroll_action_id
855 AND paat.absence_category = 'S'
856 AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
857 AND paat.business_group_id = pab.business_group_id
858 AND (pab.date_end + 1) BETWEEN p_start_date
859 AND p_end_date
860 AND paa.assignment_id = pab.abs_information5
861 AND pab.abs_information_category = 'NL_S'
862 AND pab.abs_information4 = 'L'
863 AND pab.business_group_id = pap.business_group_id
864 AND pab.person_id = pap.person_id
865 AND pap.person_id = paa.person_id
866 AND (
867 hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
868 ,paa.organization_id) IS NOT NULL
869 OR per_nl_ltsrr_archive.org_check (pap.business_group_id
870 ,NULL
871 ,nvl (paa.establishment_id, - 1)
872 ,p_start_date
873 ,p_end_date) = 1
874 )
875 AND paa.organization_id IN
876 (
877 SELECT pose.organization_id_parent
878 FROM per_org_structure_elements pose
879 WHERE posv.org_structure_version_id = pose.org_structure_version_id
880 UNION
881 SELECT pose.organization_id_child
882 FROM per_org_structure_elements pose
883 WHERE posv.org_structure_version_id = pose.org_structure_version_id
884 );
885 /**************************************************************************/
886 --Withdrawal and Regeneration
887
888 CURSOR csr_get_asg_person_archive (p_person_id IN NUMBER
889 ,p_start_person_id IN NUMBER
890 ,p_end_person_id IN NUMBER
891 ,p_payroll_action_id IN NUMBER
892 ,p_start_date IN DATE
893 ,p_end_date IN DATE
894 ,p_type IN VARCHAR2) IS
895 SELECT DISTINCT
896 paa.assignment_id assignment_id
897 FROM pay_action_information pai
898 , pay_payroll_actions ppa
899 , per_all_assignments_f paa
900 , per_all_people_f pap
901 WHERE pai.action_context_type = 'AAP'
902 AND pai.action_information_category = 'NL_LTSRR_ABS_INFO'
903 AND pai.action_information4 = 'LTSRR'
904 AND pai.action_information29 = 'NL_LTSRR'
905 AND (
906 (
907 pai.action_information30 = 'N'
908 AND p_type = 'W'
909 )
910 OR (
911 p_type = 'R'
912 )
913 )
914 AND (
915 p_start_date <= fnd_date.canonical_to_date (pai.action_information2)
916 AND p_end_date >= fnd_date.canonical_to_date (pai.action_information3)
917 )
918 AND paa.assignment_id = pai.assignment_id
919 AND pap.person_id = p_person_id
920 AND p_person_id BETWEEN p_start_person_id
921 AND p_end_person_id
922 AND pap.person_id = paa.person_id
923 AND paa.business_group_id = pap.business_group_id
924 AND pap.business_group_id = ppa.business_group_id
925 AND ppa.payroll_action_id = p_payroll_action_id;
926
927 /**************************************************************************/
928 CURSOR csr_get_asg_org_archive (p_org_id IN NUMBER
929 ,p_org_struct_id IN NUMBER
930 ,p_start_person_id IN NUMBER
931 ,p_end_person_id IN NUMBER
932 ,p_payroll_action_id IN NUMBER
933 ,p_start_date IN DATE
934 ,p_end_date IN DATE
935 ,p_type IN VARCHAR2) IS
936 SELECT DISTINCT
937 paa.assignment_id assignment_id
938 FROM pay_action_information pai
939 , pay_payroll_actions ppa
940 , per_org_structure_versions posv
941 , per_all_assignments_f paa
942 , per_all_people_f pap
943 WHERE posv.organization_structure_id = p_org_struct_id
944 AND posv.date_from <= p_end_date
945 AND nvl (posv.date_to
946 ,hr_general.end_of_time) >= p_start_date
947 AND (
948 paa.organization_id IN
949 (
950 (
951 SELECT pose.organization_id_child
952 FROM per_org_structure_elements pose
953 WHERE pose.org_structure_version_id = posv.org_structure_version_id
954 START WITH pose.organization_id_parent = p_org_id
955 CONNECT BY PRIOR organization_id_child = organization_id_parent
956 )
957 UNION
958 (
959 SELECT p_org_id
960 FROM dual
961 )
962 )
963 OR nvl (paa.establishment_id, - 1) = p_org_id
964 )
965 AND pap.person_id = paa.person_id
966 AND paa.person_id BETWEEN p_start_person_id
967 AND p_end_person_id
968 AND paa.business_group_id = pap.business_group_id
969 AND pai.action_context_type = 'AAP'
970 AND pai.action_information_category = 'NL_LTSRR_ABS_INFO'
971 AND pai.action_information4 = 'LTSRR'
972 AND pai.action_information29 = 'NL_LTSRR'
973 AND (
974 (
975 pai.action_information30 = 'N'
976 AND p_type = 'W'
977 )
978 OR (
979 p_type = 'R'
980 )
981 )
982 AND (
983 p_start_date <= fnd_date.canonical_to_date (pai.action_information2)
984 AND p_end_date >= fnd_date.canonical_to_date (pai.action_information3)
985 )
986 AND paa.assignment_id = pai.assignment_id
987 AND pap.business_group_id = ppa.business_group_id
988 AND ppa.payroll_action_id = p_payroll_action_id;
989 /**************************************************************************/
990 CURSOR csr_get_asg_hier_archive (p_org_struct_id IN NUMBER
991 ,p_start_person_id IN NUMBER
992 ,p_end_person_id IN NUMBER
993 ,p_payroll_action_id IN NUMBER
994 ,p_start_date IN DATE
995 ,p_end_date IN DATE
996 ,p_type IN VARCHAR2) IS
997 SELECT DISTINCT
998 paa.assignment_id assignment_id
999 FROM pay_action_information pai
1000 , per_all_assignments_f paa
1001 , per_all_people_f pap
1002 , pay_payroll_actions ppa
1003 , per_org_structure_versions posv
1004 WHERE posv.organization_structure_id = p_org_struct_id
1005 AND pap.person_id BETWEEN p_start_person_id
1006 AND p_end_person_id
1007 AND pap.business_group_id = ppa.business_group_id
1008 AND ppa.payroll_action_id = p_payroll_action_id
1009 AND pai.action_context_type = 'AAP'
1010 AND pai.action_information_category = 'NL_LTSRR_ABS_INFO'
1011 AND pai.action_information4 = 'LTSRR'
1012 AND pai.action_information29 = 'NL_LTSRR'
1013 AND (
1014 (
1015 pai.action_information30 = 'N'
1016 AND p_type = 'W'
1017 )
1018 OR (
1019 p_type = 'R'
1020 )
1021 )
1022 AND (
1023 p_start_date <= fnd_date.canonical_to_date (pai.action_information2)
1024 AND p_end_date >= fnd_date.canonical_to_date (pai.action_information3)
1025 )
1026 AND paa.assignment_id = pai.assignment_id
1027 AND pap.person_id = paa.person_id
1028 AND (
1029 hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
1030 ,paa.organization_id) IS NOT NULL
1031 OR per_nl_dsr_archive.org_check (pap.business_group_id
1032 ,NULL
1033 ,nvl (paa.establishment_id,- 1)
1034 ,p_start_date
1035 , p_end_date) = 1
1036 )
1037 AND paa.organization_id IN
1038 (
1039 SELECT pose.organization_id_parent
1040 FROM per_org_structure_elements pose
1041 WHERE posv.org_structure_version_id = pose.org_structure_version_id
1042 UNION
1043 SELECT pose.organization_id_child
1044 FROM per_org_structure_elements pose
1045 WHERE posv.org_structure_version_id = pose.org_structure_version_id
1046 );
1047 /**************************************************************************/
1048
1049 l_org_struct_id NUMBER := NULL;
1050 l_person_id NUMBER := NULL;
1051 l_org_id NUMBER := NULL;
1052 l_bg_id NUMBER;
1053 l_start_date DATE;
1054 l_end_date DATE;
1055 l_type VARCHAR2(1) := NULL;
1056
1057 /**************************************************************************/
1058 PROCEDURE ins_assg_act (p_assignid IN NUMBER
1059 ,p_pactid IN NUMBER
1060 ,p_chunk IN NUMBER)
1061 IS
1062 l_asg_act_id NUMBER;
1063 BEGIN
1064 SELECT pay_assignment_actions_s.NEXTVAL INTO l_asg_act_id FROM dual;
1065 hr_nonrun_asact.insact(
1066 lockingactid => l_asg_act_id
1067 ,assignid => p_assignid
1068 ,pactid => p_pactid
1069 ,chunk => p_chunk);
1070 EXCEPTION
1071 WHEN others THEN
1072 hr_utility.trace('Exception in ins_assg_act SQL-ERRM : '||SQLERRM);
1073 END ins_assg_act;
1074 /**************************************************************************/
1075
1076 BEGIN
1077
1078 hr_utility.trace('+====assignment_action_code================================+');
1079 hr_utility.trace('Payroll_action_id/Chunk: '||p_payroll_action_id||' / '||p_chunk );
1080 hr_utility.trace('Start/End Person ID : '||p_start_person_id||' / '||p_end_person_id );
1081
1082 get_all_parameters (
1083 p_payroll_action_id => p_payroll_action_id
1084 ,p_org_struct_id => l_org_struct_id
1085 ,p_person_id => l_person_id
1086 ,p_org_id => l_org_id
1087 ,p_bg_id => l_bg_id
1088 ,p_start_date => l_start_date
1089 ,p_end_date => l_end_date
1090 ,p_type => l_type);
1091
1092 IF l_person_id is not NULL THEN
1093 hr_utility.trace(l_type||' Person selected ' ||l_person_id||' '||p_start_person_id||' '||p_end_person_id);
1094 IF l_type = 'I' THEN
1095 FOR v_csr_get_asg_person IN
1096 csr_get_asg_person(l_person_id, p_start_person_id, p_end_person_id
1097 , p_payroll_action_id, l_start_date, l_end_date)
1098 LOOP
1099 ins_assg_act(v_csr_get_asg_person.assignment_id,p_payroll_action_id,p_chunk);
1100 END LOOP;
1101 ELSIF l_type IN ('W','R') THEN
1102 FOR v_csr_get_asg_person IN
1103 csr_get_asg_person_archive(l_person_id, p_start_person_id, p_end_person_id
1104 , p_payroll_action_id, l_start_date, l_end_date,l_type)
1105 LOOP
1106 ins_assg_act(v_csr_get_asg_person.assignment_id,p_payroll_action_id,p_chunk);
1107 END LOOP;
1108 END IF;
1109 -----------------------------------------------
1110 ELSIF l_org_id is not NULL THEN
1111 hr_utility.trace(l_type||' Org selected ' ||l_org_id);
1112 IF l_type = 'I' THEN
1113 FOR v_csr_get_asg_org IN
1114 csr_get_asg_org(l_org_id, l_org_struct_id
1115 , p_start_person_id, p_end_person_id, p_payroll_action_id
1116 , l_start_date, l_end_date)
1117 LOOP
1118 ins_assg_act(v_csr_get_asg_org.assignment_id,p_payroll_action_id,p_chunk);
1119 END LOOP;
1120 ELSIF l_type IN ('W','R') THEN
1121 FOR v_csr_get_asg_org IN
1122 csr_get_asg_org_archive(l_org_id, l_org_struct_id
1123 , p_start_person_id, p_end_person_id, p_payroll_action_id
1124 , l_start_date, l_end_date,l_type)
1125 LOOP
1126 ins_assg_act(v_csr_get_asg_org.assignment_id,p_payroll_action_id,p_chunk);
1127 END LOOP;
1128 END IF;
1129 -----------------------------------------------
1130 ELSIF l_org_struct_id is not NULL THEN
1131 hr_utility.trace(l_type||' Hierarchy selected ' ||l_org_struct_id);
1132 IF l_type = 'I' THEN
1133 FOR v_csr_get_asg_hier IN
1134 csr_get_asg_hier(l_org_struct_id, p_start_person_id, p_end_person_id
1135 , p_payroll_action_id, l_start_date, l_end_date)
1136 LOOP
1137 ins_assg_act(v_csr_get_asg_hier.assignment_id,p_payroll_action_id,p_chunk);
1138 END LOOP;
1139 ELSIF l_type IN ('W','R') THEN
1140 FOR v_csr_get_asg_hier IN
1141 csr_get_asg_hier_archive(l_org_struct_id, p_start_person_id, p_end_person_id
1142 , p_payroll_action_id, l_start_date, l_end_date,l_type)
1143 LOOP
1144 ins_assg_act(v_csr_get_asg_hier.assignment_id,p_payroll_action_id,p_chunk);
1145 END LOOP;
1146 END IF;
1147 END IF;
1148
1149
1150 hr_utility.trace('X====assignment_action_code================================X');
1151 EXCEPTION
1152 WHEN others THEN
1153 hr_utility.trace('Exception in assignment_action_code SQL-ERRM : '||SQLERRM);
1154 END assignment_action_code;
1155 /**************************************************************************/
1156
1157 /*-------------------------------------------------------------------------------
1158 |Name : archive_code |
1159 |Type : Procedure |
1160 |Description : Archival code |
1161 -------------------------------------------------------------------------------*/
1162
1163
1164 PROCEDURE archive_code (p_assignment_action_id IN NUMBER
1165 ,p_effective_date IN DATE) IS
1166
1167
1168 CURSOR csr_get_org_info(p_assignment_id IN NUMBER
1169 ,p_org_struct_id IN NUMBER
1170 ,p_abs_start_date IN DATE) IS
1171 SELECT hou.organization_id org_id
1172 , hou.name org_name
1173 , hoi.org_information4 tax_reg
1174 FROM per_all_assignments_f pas
1175 , hr_organization_units hou
1176 , hr_organization_information hoi
1177 , per_org_structure_versions posv
1178 WHERE posv.organization_structure_id = p_org_struct_id
1179 AND p_abs_start_date BETWEEN posv.date_from
1180 AND nvl (posv.date_to
1181 ,hr_general.end_of_time)
1182 AND pas.assignment_id = p_assignment_id
1183 AND hou.organization_id = hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
1184 ,pas.organization_id)
1185 AND hoi.organization_id = hou.organization_id
1186 AND hoi.org_information_context = 'NL_ORG_INFORMATION';
1187 /**************************************************************************/
1188 CURSOR csr_get_leg_info(p_assignment_id IN NUMBER) IS
1189 SELECT hou.organization_id org_id
1190 , hou.name org_name
1191 , hoi.org_information1 tax_reg
1192 FROM per_all_assignments_f pas
1193 , hr_organization_units hou
1194 , hr_organization_information hoi
1195 WHERE pas.assignment_id = p_assignment_id
1196 AND hou.organization_id = pas.establishment_id
1197 AND hoi.organization_id = hou.organization_id
1198 AND hoi.org_information_context = 'NL_LE_TAX_DETAILS';
1199 /**************************************************************************/
1200
1201 /* abs details */
1202 CURSOR csr_get_abs_details (p_assignment_action_id IN NUMBER
1203 ,p_start_date IN DATE
1204 ,p_end_date IN DATE) IS
1205
1206 SELECT paa.assignment_id assignment_id
1207 , pas.person_id person_id
1208 , pab.absence_attendance_id abs_att_id
1209 , pab.date_start start_date
1210 , pab.date_end end_date
1211 , paat.absence_category abs_cat
1212 , pab.abs_information5 ass_id
1213 , pab.abs_information6 sick_ref
1214 , to_char (pab.date_start, 'YYYYMMDD') lts_st_dt
1215 , to_char ((pab.date_end + 1), 'YYYYMMDD') lts_rec_dt
1216 , pab.abs_information_category info_cat
1217 FROM per_absence_attendance_types paat
1218 , per_absence_attendances pab
1219 , pay_assignment_actions paa
1220 , per_all_assignments_f pas
1221 WHERE paat.absence_category = 'S'
1222 AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
1223 AND paat.business_group_id = pab.business_group_id
1224 AND (pab.date_end + 1) BETWEEN p_start_date
1225 AND p_end_date
1226 AND (pab.date_end + 1) BETWEEN pas.effective_start_date
1227 AND pas.effective_end_date
1228 AND pab.business_group_id = pas.business_group_id
1229 AND paa.assignment_action_id = p_assignment_action_id
1230 AND pas.assignment_id = paa.assignment_id
1231 AND pab.abs_information5 = pas.assignment_id
1232 AND pab.person_id = pas.person_id
1233 AND pab.abs_information_category = 'NL_S'
1234 AND pab.abs_information4 = 'L';
1235 /**************************************************************************/
1236
1237 /* sender and contact data */
1238 CURSOR csr_get_org_contact_info(p_assignment_id IN NUMBER
1239 , p_start_date IN DATE) IS
1240 SELECT decode (hoi.org_information1,'Y','1','N','2') test_msg
1241 ,hoi.org_information2 sector
1242 ,lpad(hoi.org_information3, 2, 0) risk_group
1243 ,hoi.org_information4 role_sender
1244 ,hoi.org_information5 tax_no_sender
1245 ,hoi.org_information6 contact_per_or_dept
1246 ,hoi.org_information7 est_name
1247 ,hoi.org_information8 gender_contact
1248 ,hoi.org_information9 ph_no_contact
1249 FROM per_all_assignments_f pas
1250 ,hr_organization_units hou
1251 ,hr_organization_information hoi
1252 WHERE pas.assignment_id = p_assignment_id
1253 AND p_start_date BETWEEN pas.effective_start_date
1254 AND pas.effective_end_date
1255 AND hou.organization_id = nvl (pas.establishment_id
1256 ,pas.organization_id)
1257 AND hoi.organization_id = hou.organization_id
1258 AND hoi.org_information_context = 'NL_ORG_DSR_INFO';
1259 /**************************************************************************/
1260
1261 /* employee data */
1262 CURSOR csr_get_person_info(p_assignment_id IN NUMBER
1263 , p_start_date IN DATE
1264 , p_end_date IN DATE) IS
1265 SELECT pap.national_identifier sofi_number
1266 ,to_char (pap.date_of_birth
1267 ,'YYYYMMDD') date_of_birth
1268 ,decode (pap.sex,'M','1','F','2',NULL,'9') gender
1269 ,REPLACE(REPLACE(pap.per_information1, '.', ''), ' ', '') init
1270 ,pap.pre_name_adjunct prefix
1271 ,pap.last_name last_name
1272 ,pap.employee_number employee_number
1273 ,pap.person_id person_id
1274 ,pap.business_group_id bg_id
1275 ,pas.establishment_id establishment_id
1276 ,pas.assignment_sequence asg_seq
1277 FROM per_all_people_f pap
1278 ,per_all_assignments_f pas
1279 WHERE pas.assignment_id = p_assignment_id
1280 AND p_start_date BETWEEN pas.effective_start_date
1281 AND pas.effective_end_date
1282 AND pap.person_id = pas.person_id
1283 AND p_start_date BETWEEN pap.effective_start_date
1284 AND pap.effective_end_date;
1285 /**************************************************************************/
1286
1287
1288 /*NL_DS_SICK_INFO - Contact Details*/
1289 CURSOR csr_get_sickness_contact (p_assignment_id IN NUMBER
1290 , p_start_date IN DATE) IS
1291 SELECT pei_information21 contact_name
1292 ,pei_information22 est_name
1293 ,pei_information23 gender
1294 ,pei_information24 contact_ph_no
1295 FROM per_all_assignments_f pas
1296 ,per_people_extra_info pei
1297 WHERE pas.assignment_id = p_assignment_id
1298 AND p_start_date BETWEEN pas.effective_start_date
1299 AND pas.effective_end_date
1300 AND pei.person_id = pas.person_id
1301 AND pei.information_type = 'NL_DS_SICK_INFO';
1302
1303 /**************************************************************************/
1304
1305 /* labor relation info */
1306 CURSOR csr_get_lbr_info (p_assignment_id IN NUMBER
1307 , p_start_date IN DATE) IS
1308 SELECT to_char(fnd_date.canonical_to_date (paei.aei_information1),'YYYYMMDD') start_date
1309 ,to_char(fnd_date.canonical_to_date (paei.aei_information2),'YYYYMMDD') end_date
1310 ,paei.aei_information3 code_kind
1311 FROM per_assignment_extra_info paei
1312 WHERE paei.assignment_id = p_assignment_id
1313 AND paei.aei_information_category = 'NL_LBR'
1314 AND p_start_date BETWEEN fnd_date.canonical_to_date (paei.aei_information1)
1315 AND nvl (fnd_date.canonical_to_date (paei.aei_information2)
1316 ,hr_general.end_of_time);
1317 /**************************************************************************/
1318
1319 CURSOR csr_already_exists(p_abs_att_id NUMBER) IS
1320
1321 SELECT 'Y'
1322 FROM pay_action_information pai
1323 WHERE pai.action_context_type = 'AAP'
1324 AND pai.action_information_category = 'NL_LTSRR_ABS_INFO'
1325 AND pai.action_information1 = p_abs_att_id
1326 AND pai.action_information30 = 'N';
1327 /**************************************************************************/
1328
1329 CURSOR csr_chk_LTSR_exists (p_assignment_id IN NUMBER
1330 ,p_abs_start_date IN DATE) IS
1331
1332 SELECT 'Y'
1333 FROM pay_action_information pai
1334 WHERE pai.assignment_id = p_assignment_id
1335 AND pai.action_information_category = 'NL_LTSR_ABS_INFO'
1336 AND pai.action_information30 = 'N'
1337 AND fnd_date.canonical_to_date (pai.action_information2) = p_abs_start_date;
1338
1339 /**************************************************************************/
1340
1341 CURSOR get_archived_absence(p_assignment_id IN NUMBER
1342 , p_start_date IN DATE
1343 , p_end_date IN DATE
1344 , p_type IN VARCHAR2) IS
1345
1346 SELECT pai.action_information1 abs_att_id
1347 , pai.action_information2 st_dt
1348 , pai.action_information3 end_dt
1349 , pai.action_context_id org_asg_act_id
1350 , pai.action_information_id act_info_id
1351 FROM pay_action_information pai
1352 WHERE pai.assignment_id = p_assignment_id
1353 AND pai.action_context_type = 'AAP'
1354 AND pai.action_information_category = 'NL_LTSRR_ABS_INFO'
1355 AND pai.action_information29 = 'NL_LTSRR'
1356 AND (
1357 (
1358 pai.action_information30 = 'N'
1359 AND p_type = 'W'
1360 )
1361 OR (
1362 p_type = 'R'
1363 )
1364 )
1365 AND (
1366 p_start_date <= fnd_date.canonical_to_date (pai.action_information2)
1367 AND p_end_date >= fnd_date.canonical_to_date (pai.action_information3)
1368 );
1369 /**************************************************************************/
1370 CURSOR c_get_pact_chunk(c_assignment_action_id IN NUMBER ) IS
1371 SELECT ppa.payroll_action_id
1372 ,paa.chunk_number
1373 FROM pay_payroll_actions ppa
1374 ,pay_assignment_actions paa
1375 WHERE paa.payroll_action_id = ppa.payroll_action_id
1376 AND paa.assignment_action_id = c_assignment_action_id;
1377 /**************************************************************************/
1378 CURSOR c_get_assid_chunk(c_assignment_action_id IN NUMBER ) IS
1379 SELECT paa.assignment_id
1380 , paa.chunk_number
1381 FROM pay_assignment_actions paa
1382 , pay_payroll_actions ppa
1383 WHERE paa.payroll_action_id = ppa.payroll_action_id
1384 AND paa.assignment_action_id = c_assignment_action_id;
1385 /**************************************************************************/
1386 CURSOR csr_get_assignment_dates(p_assignment_id IN NUMBER) IS
1387 SELECT to_char(min (pas.effective_start_date),'YYYYMMDD') ass_start_date
1388 ,to_char(decode (max (pas.effective_end_date)
1389 ,hr_general.end_of_time
1390 ,to_date(NULL),max (pas.effective_end_date)),'YYYYMMDD') ass_end_date
1391 FROM per_all_assignments_f pas
1392 WHERE pas.assignment_id = p_assignment_id;
1393 /**************************************************************************/
1394 CURSOR csr_numiv_override(p_assignment_id IN NUMBER) IS
1395 SELECT paei.aei_information1 numiv_override
1396 FROM per_assignment_extra_info paei
1397 WHERE paei.assignment_id = p_assignment_id
1398 AND paei.aei_information_category = 'NL_NUMIV_OVERRIDE';
1399 /**************************************************************************/
1400
1401 l_org_struct_id NUMBER := NULL;
1402 l_person_id NUMBER := NULL;
1403 l_org_id NUMBER := NULL;
1404 l_bg_id NUMBER;
1405 l_child_aa_id NUMBER;
1406 l_start_date DATE := NULL;
1407 l_end_date DATE := NULL;
1408 l_type VARCHAR2(1) := NULL;
1409 l_abs_attendence_id NUMBER;
1410 l_payroll_action_id NUMBER;
1411 l_est_id NUMBER := NULL;
1412 l_tax_reg_num VARCHAR2(100);
1413 l_org_name VARCHAR2(150);
1414 l_abs_start_date DATE := NULL;
1415 l_abs_end_date DATE := NULL;
1416 l_assignment_id NUMBER := NULL;
1417 l_action_info_id NUMBER;
1418 l_ovn NUMBER;
1419 l_exists VARCHAR2(1);
1420 l_LTSR_exists VARCHAR2(1);
1421 l_chunk_number NUMBER;
1422 l_person_info csr_get_person_info%ROWTYPE;
1423 l_org_contact_info csr_get_org_contact_info%ROWTYPE;
1424 l_sickness_contact csr_get_sickness_contact%ROWTYPE;
1425 l_lbr_info csr_get_lbr_info%ROWTYPE;
1426 l_assignment_dates csr_get_assignment_dates%ROWTYPE;
1427 l_numiv_override per_assignment_extra_info.aei_information1%TYPE;
1428
1429
1430 /**************************************************************************/
1431 BEGIN
1432 hr_utility.trace('+====archive_code==========================================+');
1433 hr_utility.trace('p_assignment_action_id : '||p_assignment_action_id);
1434 hr_utility.trace('p_effective_date : '||p_effective_date);
1435
1436 OPEN c_get_pact_chunk (p_assignment_action_id);
1437 FETCH c_get_pact_chunk INTO l_payroll_action_id, l_chunk_number;
1438 CLOSE c_get_pact_chunk;
1439
1440 hr_utility.trace('l_payroll_action_id : '||l_payroll_action_id);
1441 hr_utility.trace('l_chunk_number : '||l_chunk_number);
1442
1443 get_all_parameters (
1444 p_payroll_action_id => l_payroll_action_id
1445 ,p_org_struct_id => l_org_struct_id
1446 ,p_person_id => l_person_id
1447 ,p_org_id => l_org_id
1448 ,p_bg_id => l_bg_id
1449 ,p_start_date => l_start_date
1450 ,p_end_date => l_end_date
1451 ,p_type => l_type);
1452
1453 IF l_type = 'I' THEN
1454 FOR v_csr_get_abs_details IN csr_get_abs_details(p_assignment_action_id, l_start_date, l_end_date)
1455 LOOP
1456 l_exists := 'N';
1457 l_LTSR_exists := 'N';
1458 l_assignment_id := v_csr_get_abs_details.assignment_id;
1459 l_abs_attendence_id := v_csr_get_abs_details.abs_att_id;
1460 l_abs_start_date := v_csr_get_abs_details.start_date;
1461 l_abs_end_date := v_csr_get_abs_details.end_date;
1462 hr_utility.trace('Assignment/Attendence : ' || l_assignment_id||'/'||l_abs_attendence_id);
1463 hr_utility.trace('Absence Start/End Date : ' || to_char(l_abs_start_date,'DDMonYYYY') || ' / ' ||
1464 to_char(l_abs_end_date,'DDMonYYYY'));
1465 OPEN csr_already_exists (l_abs_attendence_id);
1466 FETCH csr_already_exists INTO l_exists;
1467 CLOSE csr_already_exists;
1468
1469 OPEN csr_chk_LTSR_exists(l_assignment_id,l_abs_start_date);
1470 FETCH csr_chk_LTSR_exists INTO l_LTSR_exists;
1471 CLOSE csr_chk_LTSR_exists;
1472
1473 -- hr_utility.trace('LTSRR exists: '||l_exists||' LTSR exists : '||l_LTSR_exists);
1474
1475 IF l_exists = 'Y' THEN
1476 fnd_file.put_line(fnd_file.log,'Long Term Sickness Recovery Report already sent. Hence skipped.');
1477 ELSIF l_exists = 'N' THEN
1478 IF l_LTSR_exists = 'N' THEN
1479 fnd_file.put_line(fnd_file.log,'Long Term Sickness Report not sent. Hence skipped.');
1480 ELSIF l_LTSR_exists = 'Y' THEN
1481
1482 SELECT pay_assignment_actions_s.NEXTVAL INTO l_child_aa_id FROM dual;
1483 hr_nonrun_asact.insact(
1484 lockingactid => l_child_aa_id
1485 , assignid => l_assignment_id
1486 , pactid => l_payroll_action_id
1487 , chunk => l_chunk_number
1488 , status => 'C'
1489 , source_act => p_assignment_action_id);
1490
1491 OPEN csr_get_sickness_contact(l_assignment_id, l_abs_start_date );
1492 FETCH csr_get_sickness_contact INTO l_sickness_contact;
1493 CLOSE csr_get_sickness_contact;
1494
1495 OPEN csr_get_org_contact_info(l_assignment_id, l_abs_start_date );
1496 FETCH csr_get_org_contact_info into l_org_contact_info;
1497 CLOSE csr_get_org_contact_info;
1498
1499 OPEN csr_get_person_info(l_assignment_id, l_abs_start_date, l_abs_end_date);
1500 FETCH csr_get_person_info into l_person_info;
1501 CLOSE csr_get_person_info;
1502
1503 /* Archiving absence details */
1504 pay_action_information_api.create_action_information
1505 (
1506 p_action_information_id => l_action_info_id
1507 , p_action_context_id => l_child_aa_id
1508 , p_action_context_type => 'AAP'
1509 , p_object_version_number => l_ovn
1510 , p_assignment_id => l_assignment_id
1511 , p_effective_date => p_effective_date
1512 , p_source_id => NULL
1513 , p_source_text => NULL
1514 , p_action_information_category => 'NL_LTSRR_ABS_INFO'
1515 , p_action_information1 => l_abs_attendence_id
1516 , p_action_information2 => fnd_date.date_to_canonical(l_abs_end_date + 1)
1517 , p_action_information3 => fnd_date.date_to_canonical(l_abs_start_date)
1518 , p_action_information4 => 'LTSRR'
1519 , p_action_information5 => v_csr_get_abs_details.person_id
1520 , p_action_information6 => v_csr_get_abs_details.ass_id
1521 , p_action_information7 => v_csr_get_abs_details.sick_ref
1522 , p_action_information8 => v_csr_get_abs_details.lts_st_dt
1523 , p_action_information9 => v_csr_get_abs_details.lts_rec_dt
1524 , p_action_information10 => l_sickness_contact.contact_name
1525 , p_action_information11 => l_sickness_contact.est_name
1526 , p_action_information12 => l_sickness_contact.gender
1527 , p_action_information13 => l_sickness_contact.contact_ph_no
1528 , p_action_information29 => 'NL_LTSRR'
1529 , p_action_information30 => 'N'
1530 );
1531
1532 OPEN csr_get_lbr_info(l_assignment_id, l_abs_start_date);
1533 FETCH csr_get_lbr_info INTO l_lbr_info;
1534 CLOSE csr_get_lbr_info;
1535
1536 OPEN csr_get_assignment_dates(l_assignment_id);
1537 FETCH csr_get_assignment_dates into l_assignment_dates;
1538 CLOSE csr_get_assignment_dates;
1539
1540 l_est_id := l_person_info.establishment_id;
1541 IF l_est_id IS NOT NULL
1542 AND per_nl_ltsrr_archive.org_check(l_bg_id
1543 ,NULL
1544 ,l_est_id
1545 ,l_abs_start_date
1546 ,l_abs_end_date) = 1 THEN
1547 OPEN csr_get_leg_info(l_assignment_id);
1548 FETCH csr_get_leg_info INTO l_org_id, l_org_name, l_tax_reg_num;
1549 CLOSE csr_get_leg_info;
1550 ELSE
1551 OPEN csr_get_org_info(l_assignment_id, l_org_struct_id, l_abs_start_date);
1552 FETCH csr_get_org_info INTO l_org_id, l_org_name, l_tax_reg_num;
1553 CLOSE csr_get_org_info;
1554 END IF;
1555
1556 OPEN csr_numiv_override(l_assignment_id);
1557 FETCH csr_numiv_override INTO l_numiv_override;
1558 CLOSE csr_numiv_override;
1559
1560 /* Archiving Employee and employer info */
1561 pay_action_information_api.create_action_information
1562 ( p_action_information_id => l_action_info_id
1563 , p_action_context_id => l_child_aa_id
1564 , p_action_context_type => 'AAP'
1565 , p_object_version_number => l_ovn
1566 , p_assignment_id => l_assignment_id
1567 , p_effective_date => p_effective_date
1568 , p_source_id => NULL
1569 , p_source_text => NULL
1570 , p_action_information_category => 'NL_LTSRR_EMP_INFO'
1571 , p_action_information1 => l_abs_attendence_id
1572 , p_action_information2 => l_org_contact_info.test_msg
1573 , p_action_information3 => l_org_contact_info.tax_no_sender
1574 , p_action_information4 => l_org_contact_info.role_sender
1575 , p_action_information5 => l_org_contact_info.contact_per_or_dept
1576 , p_action_information6 => l_org_contact_info.ph_no_contact
1577 , p_action_information7 => l_org_contact_info.risk_group
1578 , p_action_information8 => l_org_contact_info.sector
1579 , p_action_information9 => l_tax_reg_num
1580 , p_action_information11 => l_person_info.sofi_number
1581 , p_action_information12 => l_person_info.date_of_birth
1582 , p_action_information13 => l_person_info.gender
1583 , p_action_information14 => l_person_info.init
1584 , p_action_information15 => l_person_info.prefix
1585 , p_action_information16 => l_person_info.last_name
1586 , p_action_information17 => l_lbr_info.start_date
1587 , p_action_information18 => l_lbr_info.end_date
1588 , p_action_information21 => nvl(l_numiv_override,l_person_info.asg_seq)
1589 , p_action_information22 => l_person_info.employee_number
1590 , p_action_information23 => l_org_contact_info.est_name
1591 , p_action_information24 => l_org_contact_info.gender_contact
1592 , p_action_information25 => l_assignment_dates.ass_start_date
1593 , p_action_information26 => l_assignment_dates.ass_end_date
1594 );
1595
1596 END IF;
1597 END IF;
1598 END LOOP;
1599
1600 ELSIF l_type IN ('W','R') THEN
1601 hr_utility.trace(l_type||' Assignment Action ID : '||p_assignment_action_id );
1602 OPEN c_get_assid_chunk (p_assignment_action_id);
1603 FETCH c_get_assid_chunk INTO l_assignment_id, l_chunk_number;
1604 CLOSE c_get_assid_chunk;
1605 hr_utility.trace('Assignment ID/Chunk : '||l_assignment_id||' / '||l_chunk_number);
1606
1607 FOR r_get_archived_absence IN get_archived_absence(l_assignment_id, l_start_date, l_end_date, l_type)
1608 LOOP
1609
1610 hr_utility.trace('Absence Attendence ID : ' || r_get_archived_absence.abs_att_id);
1611
1612 hr_utility.trace('Absence Start/End Date : ' ||
1613 to_char(fnd_date.canonical_to_date(r_get_archived_absence.st_dt),'DDMonYYYY') || ' / ' ||
1614 to_char(fnd_date.canonical_to_date(r_get_archived_absence.end_dt),'DDMonYYYY'));
1615 hr_utility.trace('Org Asg Action ID : ' || r_get_archived_absence.org_asg_act_id);
1616
1617 hr_nonrun_asact.insint(lockingactid => p_assignment_action_id
1618 , lockedactid => r_get_archived_absence.org_asg_act_id);
1619 IF l_type = 'W' THEN
1620 -- Update previous archive incase of withdrawal.
1621 pay_action_information_api.update_action_information
1622 (p_action_information_id => r_get_archived_absence.act_info_id
1623 , p_object_version_number => l_ovn
1624 , p_action_information30 => 'Y'
1625 );
1626 END IF;
1627 END LOOP;
1628 END IF;
1629
1630 hr_utility.trace('X====archive_code==========================================X');
1631 EXCEPTION
1632 WHEN TOO_MANY_ROWS THEN
1633 hr_utility.trace('Too Many Rows Exception in archive_code');
1634
1635 WHEN NO_DATA_FOUND THEN
1636 hr_utility.trace('No Data Found Exception in archive_code');
1637
1638 WHEN others THEN
1639 hr_utility.trace('Exception in archive_code SQL-ERRM : '||SQLERRM);
1640
1641 END archive_code;
1642 /**************************************************************************/
1643
1644 /*-------------------------------------------------------------------------------
1645 |Name : DEINITIALIZATION_CODE |
1646 |Type : Procedure |
1647 |Description : Deinitialization code |
1648 -------------------------------------------------------------------------------*/
1649
1650
1651 PROCEDURE deinitialization_code(p_actid IN NUMBER) IS
1652 BEGIN
1653 hr_utility.trace('+====deinitialization_code====X '|| p_actid);
1654 END deinitialization_code;
1655 /**************************************************************************/
1656
1657 PROCEDURE gen_header_xml
1658 IS
1659 l_string VARCHAR2(32767) := NULL;
1660 l_string_value VARCHAR2(1000) := NULL;
1661 l_clob pay_file_details.file_fragment%TYPE;
1662 l_blob pay_file_details.blob_file_fragment%TYPE;
1663 l_payroll_action_id NUMBER;
1664 l_org_struct_id NUMBER := NULL;
1665 l_person_id NUMBER := NULL;
1666 l_org_id NUMBER := NULL;
1667 l_bg_id NUMBER;
1668 l_start_date DATE := NULL;
1669 l_end_date DATE := NULL;
1670 l_type VARCHAR2(1) := NULL;
1671 l_lang VARCHAR2(2) := userenv('lang');
1672
1673 CURSOR c_get_hierarchy(p_org_struct_id IN NUMBER) IS
1674 SELECT name
1675 FROM per_organization_structures
1676 WHERE organization_structure_id = p_org_struct_id;
1677
1678 CURSOR c_get_employer(p_org_id IN NUMBER) IS
1679 SELECT name
1680 FROM hr_organization_units
1681 WHERE organization_id = p_org_id;
1682
1683 CURSOR c_get_employee(p_person_id IN NUMBER) IS
1684 SELECT full_name
1685 FROM per_all_people_f
1686 WHERE person_id = p_person_id;
1687
1688 BEGIN
1689 hr_utility.trace('+====gen_header_xml========================================+');
1690 l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
1691 hr_utility.trace('l_payroll_action_id : '||l_payroll_action_id);
1692 l_string := l_string || '<ROOT>'||EOL;
1693
1694 get_all_parameters (
1695 p_payroll_action_id => l_payroll_action_id
1696 ,p_org_struct_id => l_org_struct_id
1697 ,p_person_id => l_person_id
1698 ,p_org_id => l_org_id
1699 ,p_bg_id => l_bg_id
1700 ,p_start_date => l_start_date
1701 ,p_end_date => l_end_date
1702 ,p_type => l_type);
1703
1704 l_string_value := get_look_up_value('HR_NL_DS_REPORT_TYPE',l_type,l_lang);
1705 l_string := l_string || '<P_REPORT_TYPE>'||clean_XML(l_string_value)||'</P_REPORT_TYPE>'||EOL;
1706
1707 l_string_value := fnd_date.date_to_chardate(l_start_date);
1708 l_string := l_string || '<P_DATE_FROM>'||clean_XML(l_string_value)||'</P_DATE_FROM>'||EOL;
1709
1710 l_string_value := fnd_date.date_to_chardate(l_end_date);
1711 l_string := l_string || '<P_DATE_TO>'||clean_XML(l_string_value)||'</P_DATE_TO>'||EOL;
1712
1713 IF l_org_struct_id IS NOT NULL THEN
1714 OPEN c_get_hierarchy(l_org_struct_id);
1715 FETCH c_get_hierarchy INTO l_string_value;
1716 CLOSE c_get_hierarchy;
1717 l_string := l_string || '<P_HIERARCHY>'||clean_XML(l_string_value)||'</P_HIERARCHY>'||EOL;
1718 END IF;
1719
1720 IF l_org_id IS NOT NULL THEN
1721 OPEN c_get_employer(l_org_id);
1722 FETCH c_get_employer INTO l_string_value;
1723 CLOSE c_get_employer;
1724 l_string := l_string || '<P_EMPLOYER>'||clean_XML(l_string_value)||'</P_EMPLOYER>'||EOL;
1725 END IF;
1726
1727 IF l_person_id IS NOT NULL THEN
1728 OPEN c_get_employee(l_person_id);
1729 FETCH c_get_employee INTO l_string_value;
1730 CLOSE c_get_employee;
1731 l_string := l_string || '<P_EMPLOYEE>'||clean_XML(l_string_value)||'</P_EMPLOYEE>'||EOL;
1732 END IF;
1733
1734 l_clob := l_clob||l_string;
1735 IF l_clob IS NOT NULL THEN
1736 l_blob := c2b(l_clob);
1737 pay_core_files.write_to_magtape_lob(l_blob);
1738 END IF;
1739 hr_utility.trace('X====gen_header_xml========================================X');
1740 EXCEPTION
1741 WHEN others THEN
1742 hr_utility.trace('Exception in gen_header_xml SQL-ERRM : '||SQLERRM);
1743 END gen_header_xml;
1744 /**************************************************************************/
1745 PROCEDURE gen_footer_xml
1746 IS
1747 l_buf VARCHAR2(32767);
1748 BEGIN
1749 hr_utility.trace('+====gen_footer_xml========================================+');
1750 l_buf := l_buf || '</ROOT>'||EOL ;
1751 pay_core_files.write_to_magtape_lob(l_buf);
1752 hr_utility.trace('X====gen_footer_xml========================================X');
1753 EXCEPTION
1754 WHEN others THEN
1755 hr_utility.trace('Exception in gen_footer_xml SQL-ERRM : '||SQLERRM);
1756 END gen_footer_xml;
1757 /**************************************************************************/
1758
1759
1760 PROCEDURE gen_body_xml_main(p_type varchar2, p_id number) IS
1761
1762 CURSOR cur_abs_info_details (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
1763 SELECT pai.action_information1 abs_att_id
1764 ,pai.action_information2 start_date
1765 ,pai.action_information3 end_date
1766 ,pai.action_information4 abs_cat
1767 ,pai.action_information5 person_id
1768 ,pai.action_information6 ass_id
1769 ,pai.action_information7 sick_ref
1770 ,pai.action_information8 lts_st_dt
1771 ,pai.action_information9 lts_rec_dt
1772 ,pai.action_information10 contact_name
1773 ,pai.action_information11 est_name
1774 ,pai.action_information12 contact_gender
1775 ,pai.action_information13 contact_ph
1776 FROM pay_action_information pai
1777 WHERE pai.action_context_id = c_assignment_action_id
1778 AND pai.action_information_category = 'NL_LTSRR_ABS_INFO'
1779 AND action_context_type = 'AAP';
1780 /**************************************************************************/
1781 CURSOR cur_emp_details (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
1782 SELECT action_information2 test_msg
1783 ,action_information3 tax_no_sender
1784 ,action_information4 role_sender
1785 ,action_information5 contact_per_or_dept
1786 ,action_information6 ph_no_contact
1787 ,action_information7 risk_group
1788 ,action_information8 sector
1789 ,action_information9 tax_reg_num
1790 ,action_information10 employer_acc_no
1791 ,action_information11 sofi_number
1792 ,action_information12 date_of_birth
1793 ,action_information13 gender
1794 ,action_information14 init
1795 ,action_information15 prefix
1796 ,action_information16 last_name
1797 ,action_information17 lbr_st_date
1798 ,action_information18 lbr_end_date
1799 ,action_information21 asg_seq
1800 ,action_information22 emp_num
1801 ,action_information23 est_name
1802 ,action_information24 gender_contact
1803 ,action_information25 ass_start_date
1804 ,action_information26 ass_end_date
1805 FROM pay_action_information pai
1806 WHERE pai.action_context_id = c_assignment_action_id
1807 AND pai.action_information_category = 'NL_LTSRR_EMP_INFO'
1808 AND action_context_type = 'AAP';
1809 /**************************************************************************/
1810 CURSOR cur_withdrawl_flag(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
1811 SELECT pai.action_information30 flag
1812 FROM pay_action_information pai
1813 WHERE pai.action_context_id = c_assignment_action_id
1814 AND pai.action_information_category = 'NL_LTSRR_ABS_INFO'
1815 AND action_context_type = 'AAP';
1816 /**************************************************************************/
1817 CURSOR cur_app_version IS
1818 SELECT release_name
1819 FROM fnd_product_groups;
1820 /**************************************************************************/
1821 --Variables--
1822 l_clob PAY_FILE_DETAILS.FILE_FRAGMENT%TYPE;
1823 l_blob PAY_FILE_DETAILS.BLOB_FILE_FRAGMENT%TYPE;
1824 l_file UTL_FILE.FILE_TYPE;
1825 l_directory_path VARCHAR2(500);
1826 l_file_name VARCHAR2(50);
1827 x NUMBER := 0;
1828 r NUMBER := 0;
1829 l_abs_info cur_abs_info_details%ROWTYPE;
1830 l_emp_details cur_emp_details%ROWTYPE;
1831 l_get_app_ver cur_app_version%ROWTYPE;
1832 l_withdrawl_flag cur_withdrawl_flag%ROWTYPE;
1833 l_type VARCHAR2(1);
1834 l_lang VARCHAR2(2) := userenv('lang');
1835 xXMLTable tXMLTable;
1836 rXMLTable tXMLTable;
1837
1838 PROCEDURE Tag (xCtr IN OUT NOCOPY NUMBER
1839 ,rCtr IN OUT NOCOPY NUMBER
1840 ,pTagName IN VARCHAR2
1841 ,pTagValue IN VARCHAR2
1842 ,pDestination IN VARCHAR2)
1843 IS
1844 BEGIN
1845 IF pTagValue IS NOT NULL THEN
1846 IF pDestination IN ('X','B') THEN --XML or Both
1847 xXMLTable(xCtr).TagName := pTagName;
1848 xXMLTable(xCtr).TagValue := pTagValue;
1849
1850 IF pTagName = 'UwvZwMlaHersteldMelding' AND pTagValue = '_START_' THEN
1851 xXMLTable(xCtr).TagName := xXMLTable(xCtr).TagName||EOL||
1852 'xsi:schemaLocation="http://schemas.uwv.nl/UwvML/Berichten/UwvZwMlaHersteldMelding-v0207 UwvZwMlaHersteldMelding-v0207-b01.xsd"'||EOL||
1853 'xmlns:str="http://schemas.uwv.nl/UwvML/Berichten/UwvZwMlaHersteldMelding-v0207"'||EOL||
1854 'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"';
1855 END IF;
1856
1857 xCtr := xCtr + 1;
1858 END IF;
1859 IF pDestination IN ('R','B') THEN --Report or Both
1860 rXMLTable(rCtr).TagName := pTagName;
1861 rXMLTable(rCtr).TagValue := pTagValue;
1862 rCtr := rCtr + 1;
1863 END IF;
1864 END IF;
1865 END Tag;
1866
1867
1868 BEGIN
1869 hr_utility.trace('+====gen_body_xml_main=====================================+');
1870
1871 OPEN cur_abs_info_details(p_id);
1872 FETCH cur_abs_info_details INTO l_abs_info;
1873 CLOSE cur_abs_info_details;
1874
1875 OPEN cur_emp_details(p_id);
1876 FETCH cur_emp_details INTO l_emp_details;
1877 CLOSE cur_emp_details;
1878
1879 OPEN cur_app_version;
1880 FETCH cur_app_version into l_get_app_ver;
1881 CLOSE cur_app_version;
1882
1883 IF p_type = 'R' THEN
1884 OPEN cur_withdrawl_flag(p_id);
1885 FETCH cur_withdrawl_flag into l_withdrawl_flag;
1886 CLOSE cur_withdrawl_flag;
1887 END IF;
1888 hr_utility.trace('Fetched all data. XML Generation starts');
1889 Tag(x,r,'UwvZwMlaHersteldMelding','_START_','B'); -- LT sickness recovery report*
1890 Tag(x,r,'IndTestbericht',l_emp_details.test_msg,'X'); -- test msg
1891 Tag(x,r,'KetenPartij','_START_','B'); -- + sender data*
1892 Tag(x,r,'Loonheffingennr',l_emp_details.tax_no_sender,'X'); -- tax reg no sender*
1893 Tag(x,r,'CdRolKetenpartij',l_emp_details.role_sender,'X'); -- role sender*
1894 Tag(x,r,'NaamSoftwarePakket','Oracle HCM','X'); -- appln name
1895 Tag(x,r,'VersieSoftwarePakket',l_get_app_ver.release_name,'X'); -- ver no
1896 IF l_emp_details.contact_per_or_dept IS NOT NULL
1897 OR l_emp_details.ph_no_contact IS NOT NULL THEN
1898 Tag(x,r,'ContactGegevens','_START_','B'); -- + contact data
1899 Tag(x,r,'NaamContactpersoonAfd',l_emp_details.contact_per_or_dept,'B'); -- person or dept
1900 Tag(x,r,'TelefoonnrContactpersoonAfd',l_emp_details.ph_no_contact,'B'); -- ph no
1901 Tag(x,r,'ContactGegevens','_END_','B'); -- - contact data
1902 END IF;
1903 Tag(x,r,'KetenPartij','_END_','B'); -- - sender data*
1904 Tag(x,r,'AdministratieveEenheid','_START_','B'); -- + employer*
1905 Tag(x,r,'Loonheffingennr',l_emp_details.tax_reg_num,'X'); -- tax no employer*
1906 Tag(x,r,'SectorRisicogroep','_START_','X'); -- + risk sector grp*
1907 Tag(x,r,'CdRisicoPremiegroep',l_emp_details.risk_group,'X'); -- risk grp*
1908 Tag(x,r,'CdSectorOsv',l_emp_details.sector,'X'); -- sector *
1909 Tag(x,r,'SectorRisicogroep','_END_','X'); -- - risk sector grp*
1910 Tag(x,r,'NatuurlijkPersoon','_START_','B'); -- + employee data*
1911 Tag(x,r,'Burgerservicenr',l_emp_details.sofi_number,'B'); -- sofino*
1912 Tag(x,r,'Geboortedat',l_emp_details.date_of_birth,'X'); -- dob*
1913 Tag(x,r,'Geboortedat',fnd_date.date_to_chardate(
1914 to_date(l_emp_details.date_of_birth,'YYYYMMDD')),'R');
1915 Tag(x,r,'Geslacht',l_emp_details.gender,'X'); -- gender
1916 Tag(x,r,'Geslacht',get_look_up_value('HR_NL_DS_GENDER',
1917 l_emp_details.gender,l_lang),'R');
1918 Tag(x,r,'Voorletters',l_emp_details.init,'B'); -- initials
1919 Tag(x,r,'Voorvoegsel',l_emp_details.prefix,'B'); -- prefix
1920 Tag(x,r,'SignificantDeelVanDeAchternaam',l_emp_details.last_name,'B'); -- last name*
1921 Tag(x,r,'Arbeidsverhouding','_START_','B'); -- + assignment details*
1922 Tag(x,r,'NrInkomstenverhouding',l_emp_details.asg_seq,'B'); -- NumIV override/asg seq
1923 IF l_emp_details.lbr_st_date IS NOT NULL
1924 OR l_emp_details.lbr_end_date IS NOT NULL
1925 THEN
1926 Tag(x,r,'DatB',l_emp_details.lbr_st_date,'X'); -- start dt income rel
1927 Tag(x,r,'DatE',l_emp_details.lbr_end_date,'X'); -- end dt income rel
1928 Tag(x,r,'DatB',fnd_date.date_to_chardate(
1929 to_date(l_emp_details.lbr_st_date,'YYYYMMDD')),'R');
1930 Tag(x,r,'DatE',fnd_date.date_to_chardate(
1931 to_date(l_emp_details.lbr_end_date,'YYYYMMDD')),'R');
1932 ELSE
1933 Tag(x,r,'DatB',l_emp_details.ass_start_date,'X'); -- start dt assignment
1934 Tag(x,r,'DatE',l_emp_details.ass_end_date,'X'); -- end dt assignment
1935 Tag(x,r,'DatB',fnd_date.date_to_chardate(
1936 to_date(l_emp_details.ass_start_date,'YYYYMMDD')),'R');
1937 Tag(x,r,'DatE',fnd_date.date_to_chardate(
1938 to_date(l_emp_details.ass_end_date,'YYYYMMDD')),'R');
1939 END IF;
1940 Tag(x,r,'MeldingHersteldLangdurigAO','_START_','B'); -- + LT sickness recovery details
1941 IF (p_type = 'R' AND l_withdrawl_flag.flag = 'Y') OR p_type = 'W' THEN
1942 Tag(x,r,'IndVerzoekTotIntrekken','1','X'); -- withdrawal code
1943 Tag(x,r,'IndVerzoekTotIntrekken',get_look_up_value('HR_NL_YES_NO','Y',l_lang),'R');
1944 END IF;
1945 Tag(x,r,'ReferentieMelding',nvl(l_abs_info.sick_ref,l_emp_details.emp_num),'B'); -- sickness reference
1946 Tag(x,r,'DatHersteld',l_abs_info.lts_rec_dt,'X'); -- first day of full recovery
1947 Tag(x,r,'DatHersteld',fnd_date.date_to_chardate(
1948 to_date(l_abs_info.lts_rec_dt,'YYYYMMDD')),'R');
1949 Tag(x,r,'DatEersteAoDag',l_abs_info.lts_st_dt,'X'); -- start date sickness
1950 Tag(x,r,'DatEersteAoDag',fnd_date.date_to_chardate(
1951 to_date(l_abs_info.lts_st_dt,'YYYYMMDD')),'R');
1952 IF l_abs_info.contact_name IS NOT NULL
1953 OR l_abs_info.est_name IS NOT NULL
1954 OR l_abs_info.contact_gender IS NOT NULL
1955 OR l_abs_info.contact_ph IS NOT NULL THEN
1956 Tag(x,r,'ContactGegevens','_START_','B'); -- + contact details - sickness
1957 Tag(x,r,'NaamContactpersoonAfd',l_abs_info.contact_name,'B'); -- name
1958 Tag(x,r,'OmschrijvingLokaleVestiging',l_abs_info.est_name,'B'); -- Establishment
1959 Tag(x,r,'Geslacht',l_abs_info.contact_gender,'X'); -- gender
1960 Tag(x,r,'Geslacht',get_look_up_value('HR_NL_DS_GENDER',
1961 l_abs_info.contact_gender,l_lang),'R');
1962 Tag(x,r,'TelefoonnrContactpersoonAfd',l_abs_info.contact_ph,'B'); -- telephone
1963 Tag(x,r,'ContactGegevens','_END_','B'); -- - contact details
1964 ELSIF l_emp_details.contact_per_or_dept IS NOT NULL
1965 OR l_emp_details.ph_no_contact IS NOT NULL
1966 OR l_emp_details.est_name IS NOT NULL
1967 OR l_emp_details.gender_contact IS NOT NULL
1968 THEN
1969 Tag(x,r,'ContactGegevens','_START_','B'); -- + contact details - organization
1970 Tag(x,r,'NaamContactpersoonAfd',l_emp_details.contact_per_or_dept,'B'); -- name
1971 Tag(x,r,'OmschrijvingLokaleVestiging',l_emp_details.est_name,'B'); -- Establishment
1972 Tag(x,r,'Geslacht',l_emp_details.gender_contact,'X'); -- gender
1973 Tag(x,r,'Geslacht',get_look_up_value('HR_NL_DS_GENDER',
1974 l_emp_details.gender_contact,l_lang),'R');
1975 Tag(x,r,'TelefoonnrContactpersoonAfd',l_emp_details.ph_no_contact,'B'); -- telephone
1976 Tag(x,r,'ContactGegevens','_END_','B'); -- - contact details
1977 END IF;
1978 Tag(x,r,'MeldingHersteldLangdurigAO','_END_','B'); -- - LT sickness recovry details
1979 Tag(x,r,'Arbeidsverhouding','_END_','B'); -- - assignment details*
1980 Tag(x,r,'NatuurlijkPersoon','_END_','B'); -- - employee data*
1981 Tag(x,r,'AdministratieveEenheid','_END_','B'); -- - employer*
1982 Tag(x,r,'UwvZwMlaHersteldMelding','_END_','B'); -- LT sickness recovery report*
1983
1984 l_file_name := l_emp_details.emp_num||'_'||l_abs_info.lts_rec_dt||'_LTSRR';
1985 IF p_type in ('W','R') THEN
1986 l_file_name := l_file_name||'_'||p_type;
1987 END IF;
1988 l_file_name := l_file_name||'.xml';
1989
1990 write_file(xXMLTable, l_file_name);
1991
1992 write_report(rXMLTable);
1993
1994 hr_utility.trace('X====gen_body_xml_main=====================================X');
1995 EXCEPTION
1996 WHEN others THEN
1997 hr_utility.trace('Exception in gen_body_xml_main SQL-ERRM :'||SQLERRM);
1998 END gen_body_xml_main;
1999
2000 /**************************************************************************/
2001
2002 PROCEDURE gen_body_xml
2003 IS
2004
2005 l_payroll_action_id NUMBER;
2006 l_asg_action_id NUMBER;
2007 l_org_struct_id NUMBER := NULL;
2008 l_person_id NUMBER := NULL;
2009 l_org_id NUMBER := NULL;
2010 l_bg_id NUMBER;
2011 l_start_date DATE := NULL;
2012 l_end_date DATE := NULL;
2013 l_type VARCHAR2(1) := NULL;
2014 /**************************************************************************/
2015 CURSOR cur_get_child_act_id (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
2016 SELECT assignment_action_id ass_act_id
2017 FROM pay_assignment_actions
2018 WHERE source_action_id = c_assignment_action_id;
2019 /**************************************************************************/
2020 CURSOR get_parent_asg_ids(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
2021 SELECT locked_action_id parent_id
2022 FROM pay_action_interlocks
2023 WHERE locking_action_id = c_assignment_action_id;
2024 /**************************************************************************/
2025 CURSOR get_pact_id(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
2026 SELECT ppa.payroll_action_id
2027 FROM pay_payroll_actions ppa
2028 ,pay_assignment_actions paa
2029 WHERE paa.payroll_action_id = ppa.payroll_action_id
2030 AND paa.assignment_action_id = c_assignment_action_id;
2031 /**************************************************************************/
2032
2033 BEGIN
2034 hr_utility.trace('+====gen_body_xml==========================================+');
2035
2036 l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
2037 hr_utility.trace('TRANSFER_PAYROLL_ACTION_ID = '||l_payroll_action_id);
2038
2039 l_asg_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
2040 hr_utility.trace('TRANSFER_ACT_ID = '||l_asg_action_id);
2041
2042 OPEN get_pact_id(l_asg_action_id);
2043 FETCH get_pact_id INTO l_payroll_action_id;
2044 CLOSE get_pact_id;
2045
2046 hr_utility.trace('l_payroll_action_id = '||l_payroll_action_id);
2047 get_all_parameters (
2048 p_payroll_action_id => l_payroll_action_id
2049 ,p_org_struct_id => l_org_struct_id
2050 ,p_person_id => l_person_id
2051 ,p_org_id => l_org_id
2052 ,p_bg_id => l_bg_id
2053 ,p_start_date => l_start_date
2054 ,p_end_date => l_end_date
2055 ,p_type => l_type);
2056
2057 IF l_type = 'I' THEN
2058 hr_utility.trace('Initial Report');
2059 FOR v_child_act_id IN cur_get_child_act_id(l_asg_action_id)
2060 LOOP
2061 hr_utility.trace('Child Assignment Action ID :'||v_child_act_id.ass_act_id);
2062 gen_body_xml_main(l_type, v_child_act_id.ass_act_id);
2063 END LOOP;
2064 ELSIF l_type IN ('W','R') THEN
2065 hr_utility.trace('Withdrawal/Regeneration Report');
2066 FOR v_parent_asg_ids in get_parent_asg_ids(l_asg_action_id)
2067 LOOP
2068 hr_utility.trace('Parent Assignment Action ID :'||v_parent_asg_ids.parent_id);
2069 gen_body_xml_main(l_type, v_parent_asg_ids.parent_id);
2070 END LOOP;
2071 END IF;
2072 hr_utility.trace('X====gen_body_xml==========================================X');
2073 EXCEPTION
2074 WHEN others THEN
2075 hr_utility.trace('Exception in gen_body_xml SQL-ERRM :'||SQLERRM);
2076 END gen_body_xml;
2077
2078 END PER_NL_LTSRR_ARCHIVE;
2079