[Home] [Help]
PACKAGE BODY: APPS.PER_NL_LTSR_ARCHIVE
Source
1 PACKAGE BODY PER_NL_LTSR_ARCHIVE as
2 /* $Header: penlltsr.pkb 120.4.12020000.1 2012/07/17 08:54: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
265 PROCEDURE get_all_parameters (p_payroll_action_id IN NUMBER
266 ,p_org_struct_id OUT NOCOPY NUMBER
267 ,p_person_id OUT NOCOPY NUMBER
268 ,p_org_id OUT NOCOPY NUMBER
269 ,p_bg_id OUT NOCOPY NUMBER
270 ,p_start_date OUT NOCOPY DATE
271 ,p_end_date OUT NOCOPY DATE
272 ,p_type OUT NOCOPY VARCHAR) IS
273
274 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
275 SELECT TO_NUMBER(get_parameter(legislative_parameters,'ORG_STRUCT_ID')) org_struct_id
276 ,TO_NUMBER(get_parameter(legislative_parameters,'PERSON_ID')) person_id
277 ,TO_NUMBER(get_parameter(legislative_parameters,'ORG_ID')) org_id
278 ,business_group_id bg_id
279 ,start_date start_date
280 ,effective_date end_date
281 ,get_parameter(legislative_parameters,'TYPE') report_type
282 FROM pay_payroll_actions
283 WHERE payroll_action_id = p_payroll_action_id;
284
285 l_param csr_parameter_info%ROWTYPE;
286
287 BEGIN
288 OPEN csr_parameter_info (p_payroll_action_id);
289 FETCH csr_parameter_info INTO l_param;
290 CLOSE csr_parameter_info;
291
292 p_org_struct_id := l_param.org_struct_id;
293 p_person_id := l_param.person_id;
294 p_org_id := l_param.org_id;
295 p_bg_id := l_param.bg_id;
296 p_type := l_param.report_type;
297 p_start_date := l_param.start_date;
298 p_end_date := l_param.end_date;
299
300 EXCEPTION
301 WHEN others THEN
302 hr_utility.trace('Exception in get_all_parameters SQLERRM: '||SQLERRM);
303 END get_all_parameters;
304 /**************************************************************************/
305
306
307 /*------------------------------------------------------------------------------
308 |Name : EMP_CHECK |
309 |Type : Function |
310 |Description : Function required for valueset HR_NL_EMPLOYEE_DSR |
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_start 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_start 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_ltsr_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 IF p_org_id is not NULL THEN
424 BEGIN
425 OPEN csr_org_check(p_bg_id
426 ,p_org_struct_id
427 ,p_org_id
428 ,p_person_id
429 ,p_start_date
430 ,p_end_date );
431 FETCH csr_org_check INTO l_return_val;
432 CLOSE csr_org_check;
433
434 EXCEPTION
435 WHEN TOO_MANY_ROWS THEN
436 l_return_val := 1;
437 WHEN NO_DATA_FOUND THEN
438 null;
439 WHEN OTHERS THEN
440 hr_utility.trace('Exception in emp_check org. SQLERRM : '||SQLERRM);
441 END;
442 hr_utility.trace('l_return_val : '||l_return_val);
443 ELSIF p_org_struct_id is not NULL THEN
444 BEGIN
445 OPEN csr_org_struct_check(p_bg_id
446 ,p_org_struct_id
447 ,p_org_id
448 ,p_person_id
449 ,p_start_date
450 ,p_end_date );
451 FETCH csr_org_struct_check INTO l_return_val;
452 CLOSE csr_org_struct_check;
453 EXCEPTION
454 WHEN TOO_MANY_ROWS THEN
455 l_return_val := 1;
456 WHEN NO_DATA_FOUND THEN
457 NULL;
458 WHEN OTHERS THEN
459 hr_utility.trace('Exception in emp_check org_struct. SQLERRM : '||SQLERRM);
460 END;
461 hr_utility.trace('l_return_val : '||l_return_val);
462 END IF;
463 hr_utility.trace('X====emp_check============================================X');
464 RETURN l_return_val;
465 EXCEPTION
466 WHEN OTHERS THEN
467 hr_utility.trace('Exception in emp_check SQL-ERRM : '||SQLERRM);
468 END emp_check;
469 /**************************************************************************/
470
471
472
473
474
475 /*------------------------------------------------------------------------------
476 |Name : ORG_CHECK |
477 |Type : Function |
478 |Description : Function required for valueset HR_NL_EMPLOYER_DSR |
479 -------------------------------------------------------------------------------*/
480 FUNCTION org_check (p_bg_id IN NUMBER
481 ,p_org_struct_id IN NUMBER
482 ,p_org_id IN NUMBER
483 ,p_start_date IN DATE
484 ,p_end_date IN DATE)
485 RETURN NUMBER IS
486
487 CURSOR csr_org_struct_check (c_bg_id IN NUMBER
488 ,c_org_id IN NUMBER) IS
489
490 SELECT 1
491 FROM hr_organization_units hou
492 WHERE hou.business_group_id = c_bg_id
493 AND hou.organization_id = c_org_id
494 AND EXISTS
495 (
496 SELECT 1
497 FROM hr_all_organization_units hou1
498 ,hr_organization_information hoi1
499 WHERE hou1.business_group_id = c_bg_id
500 AND hoi1.org_information_context = 'NL_LE_TAX_DETAILS'
501 AND hoi1.org_information1 IS NOT NULL
502 AND hoi1.org_information2 IS NOT NULL
503 AND hou1.organization_id = hoi1.organization_id
504 AND hou1.organization_id = hou.organization_id
505 UNION
506 SELECT 1
507 FROM hr_all_organization_units hou2
508 ,hr_organization_information hoi2
509 WHERE hou2.business_group_id = c_bg_id
510 AND hoi2.org_information_context = 'NL_ORG_INFORMATION'
511 AND hoi2.org_information4 IS NOT NULL
512 AND hoi2.org_information3 IS NOT NULL
513 AND hou2.organization_id = hoi2.organization_id
514 AND hou2.organization_id = hou.organization_id
515 );
516 /**************************************************************************/
517 CURSOR csr_org_check (c_bg_id IN NUMBER
518 ,c_org_id IN NUMBER
519 ,c_org_struct_id IN NUMBER
520 ,c_start_date IN DATE
521 ,c_end_date IN DATE) IS
522
523 SELECT 1
524 FROM hr_organization_units hou
525 WHERE hou.organization_id = c_org_id
526 AND hou.business_group_id = c_bg_id
527 AND EXISTS
528 (
529 SELECT 1
530 FROM hr_organization_units hou1
531 ,hr_organization_information hoi1
532 WHERE hoi1.org_information_context = 'NL_ORG_INFORMATION'
533 AND hou1.business_group_id = c_bg_id
534 AND hou1.organization_id = hou.organization_id
535 AND hou1.organization_id = hoi1.organization_id
536 AND hoi1.org_information4 IS NOT NULL
537 AND hoi1.org_information3 IS NOT NULL
538 AND hou1.organization_id IN
539 (
540 SELECT pose.organization_id_parent
541 FROM per_org_structure_elements pose
542 ,per_org_structure_versions posv
543 WHERE posv.org_structure_version_id = pose.org_structure_version_id
544 AND posv.organization_structure_id = c_org_struct_id
545 AND posv.date_from <= c_start_date
546 AND nvl (posv.date_to
547 ,hr_general.end_of_time) >= c_end_date
548 UNION
549 SELECT pose.organization_id_child
550 FROM per_org_structure_elements pose
551 ,per_org_structure_versions posv
552 WHERE posv.org_structure_version_id = pose.org_structure_version_id
553 AND posv.organization_structure_id = c_org_struct_id
554 AND posv.date_from <= c_start_date
555 AND nvl (posv.date_to
556 ,hr_general.end_of_time) >= c_end_date
557 )
558 UNION
559 SELECT 1
560 FROM hr_organization_units hou2
561 ,hr_organization_information hoi2
562 WHERE hoi2.org_information_context = 'NL_LE_TAX_DETAILS'
563 AND hou2.business_group_id = c_bg_id
564 AND hou2.organization_id = hou.organization_id
565 AND hou2.organization_id = hoi2.organization_id
566 AND hoi2.org_information1 IS NOT NULL
567 AND hoi2.org_information2 IS NOT NULL
568 AND hou2.organization_id IN
569 (
570 SELECT pose.organization_id_parent
571 FROM per_org_structure_elements pose
572 ,per_org_structure_versions posv
573 WHERE posv.org_structure_version_id = pose.org_structure_version_id
574 AND posv.organization_structure_id = c_org_struct_id
575 AND posv.date_from <= c_start_date
576 AND nvl (posv.date_to
577 ,hr_general.end_of_time) >= c_end_date
578 UNION
579 SELECT pose.organization_id_child
580 FROM per_org_structure_elements pose
581 ,per_org_structure_versions posv
582 WHERE posv.org_structure_version_id = pose.org_structure_version_id
583 AND posv.organization_structure_id = c_org_struct_id
584 AND posv.date_from <= c_start_date
585 AND nvl (posv.date_to
586 ,hr_general.end_of_time) >= c_end_date
587 )
588 );
589 /**************************************************************************/
590
591 l_return_val NUMBER := 0;
592 l_start_date DATE;
593 l_end_date DATE;
594
595 BEGIN
596 --hr_utility.trace('+====org_check=============================================+');
597 IF p_org_struct_id is NULL THEN
598 BEGIN
599 OPEN csr_org_struct_check (p_bg_id,p_org_id);
600 FETCH csr_org_struct_check INTO l_return_val;
601 CLOSE csr_org_struct_check;
602
603 EXCEPTION
604 WHEN TOO_MANY_ROWS THEN
605 l_return_val := 1;
606 WHEN NO_DATA_FOUND THEN
607 NULL;
608 WHEN OTHERS THEN
609 hr_utility.trace('Exception in org_check org_struct. SQLERRM : '||SQLERRM);
610 END;
611 hr_utility.trace('l_return_val : '||l_return_val);
612 ELSE
613 BEGIN
614 OPEN csr_org_check (p_bg_id,p_org_id,p_org_struct_id,p_start_date,p_end_date);
615 FETCH csr_org_check INTO l_return_val;
616 CLOSE csr_org_check;
617
618 EXCEPTION
619 WHEN TOO_MANY_ROWS THEN
620 l_return_val := 1;
621 WHEN NO_DATA_FOUND THEN
622 NULL;
623 WHEN OTHERS THEN
624 hr_utility.trace('Exception in org_check org_struct1. SQLERRM : '||SQLERRM);
625 END;
626 hr_utility.trace('l_return_val : '||l_return_val);
627 END IF;
628 RETURN l_return_val;
629 --hr_utility.trace('X====org_check=============================================X');
630 EXCEPTION
631 WHEN OTHERS THEN
632 hr_utility.trace('Exception in org_check SQL-ERRM : '||SQLERRM);
633 END org_check;
634 /**************************************************************************/
635
636 /*--------------------------------------------------------------------
637 |Name : RANGE_CODE |
638 |Type : Procedure |
639 |Description: This procedure returns an sql string to select a range |
640 | of assignments eligible for reporting |
641 ----------------------------------------------------------------------*/
642
643
644 PROCEDURE range_code (pactid IN NUMBER
645 ,sqlstr OUT NOCOPY VARCHAR2) is
646 BEGIN
647 hr_utility.trace('+====range_code============================================+');
648 sqlstr := 'SELECT DISTINCT person_id
649 FROM per_all_people_f pap
650 ,pay_payroll_actions ppa
651 WHERE ppa.payroll_action_id = :payroll_action_id
652 AND ppa.business_group_id = pap.business_group_id
653 ORDER BY pap.person_id';
654 hr_utility.trace('X====range_code============================================X');
655 EXCEPTION
656 WHEN OTHERS THEN
657 -- Return cursor that selects no rows
658 hr_utility.trace('Exception in range_code SQL-ERRM : '||SQLERRM);
659 sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
660 END range_code;
661 /**************************************************************************/
662
663 PROCEDURE initialization_code(p_action_context_id IN NUMBER) IS
664
665 CURSOR c_usr_tab_col IS
666 SELECT put.user_table_id
667 ,puc.user_column_id
668 FROM pay_user_tables put
669 ,pay_user_columns puc
670 WHERE put.user_table_id = puc.user_table_id
671 AND put.legislation_code = puc.legislation_code
672 AND put.user_table_name = 'NL_DIACRITICAL_MARKS'
673 AND put.legislation_code = 'NL';
674 /**************************************************************************/
675 CURSOR c_src_dest_str(p_user_column_id IN NUMBER
676 , p_user_table_id IN NUMBER
677 , p_business_group_id IN NUMBER
678 , p_start_date IN DATE) IS
679 SELECT DISTINCT
680 UPPER (purf.row_low_range_or_name) Source
681 ,UPPER (pucif.value) Target
682 FROM pay_user_column_instances_f pucif
683 ,pay_user_rows_f purf
684 WHERE pucif.user_column_id = p_user_column_id
685 AND purf.user_table_id = p_user_table_id
686 AND pucif.user_row_id = purf.user_row_id
687 AND pucif.business_group_id = purf.business_group_id
688 AND pucif.business_group_id = p_business_group_id
689 AND p_start_date BETWEEN pucif.EFFECTIVE_START_DATE AND pucif.EFFECTIVE_END_DATE
690 AND p_start_date BETWEEN purf.EFFECTIVE_START_DATE AND purf.EFFECTIVE_END_DATE;
691 /**************************************************************************/
692 CURSOR c_bg_stdate(p_payroll_action_id IN NUMBER) IS
693 SELECT business_group_id bg_id
694 ,start_date start_date
695 FROM pay_payroll_actions
696 WHERE payroll_action_id = p_payroll_action_id;
697 /**************************************************************************/
698 l_user_table_id NUMBER;
699 l_user_column_id NUMBER;
700 l_bg_id NUMBER;
701 l_start_date DATE;
702
703 BEGIN
704 hr_utility.trace('+====initialization_code===================================+');
705 IF Instr ('a', COMPOSE('a'|| UNISTR('\0301')) ) = 0 THEN
706 OPEN c_bg_stdate(p_action_context_id);
707 FETCH c_bg_stdate INTO l_bg_id,l_start_date;
708 CLOSE c_bg_stdate;
709
710 OPEN c_usr_tab_col;
711 FETCH c_usr_tab_col INTO l_user_table_id, l_user_column_id;
712 CLOSE c_usr_tab_col;
713
714 FOR I in c_src_dest_str(l_user_column_id, l_user_table_id, l_bg_id, l_start_date)
715 LOOP
716 g_SOURCE := g_SOURCE||upper(I.Source)||lower(I.Source);
717 g_TARGET := g_TARGET||upper(I.Target)||lower(I.Target);
718 END LOOP;
719
720 hr_utility.trace('g_SOURCE : '||g_SOURCE);
721 hr_utility.trace('g_TARGET : '||g_TARGET);
722 END IF;
723 hr_utility.trace('X====initialization_code===================================X');
724 EXCEPTION
725 WHEN OTHERS THEN
726 hr_utility.trace('Exception in init_code SQL-ERRM : '||SQLERRM);
727 END initialization_code;
728 /**************************************************************************/
729
730 /*--------------------------------------------------------------------
731 |Name : ASSIGNMENT_ACTION_CODE |
732 |Type : Procedure |
733 |Description: This procedure further filters which assignments are |
734 | eligible for reporting |
735 ----------------------------------------------------------------------*/
736
737 PROCEDURE assignment_action_code (p_payroll_action_id IN NUMBER
738 ,p_start_person_id IN NUMBER
739 ,p_end_person_id IN NUMBER
740 ,p_chunk IN NUMBER) IS
741
742 CURSOR csr_get_asg_person (p_person_id IN NUMBER
743 ,p_start_person_id IN NUMBER
744 ,p_end_person_id IN NUMBER
745 ,p_payroll_action_id IN NUMBER
746 ,p_start_date IN DATE
747 ,p_end_date IN DATE) is
748 SELECT DISTINCT
749 paa.assignment_id assignment_id
750 FROM per_absence_attendance_types paat
751 ,per_absence_attendances pab
752 ,pay_payroll_actions ppa
753 ,per_all_assignments_f paa
754 ,per_all_people_f pap
755 WHERE paat.absence_category = 'S'
756 AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
757 AND paat.business_group_id = pab.business_group_id
758 AND pab.date_start BETWEEN p_start_date
759 AND p_end_date
760 AND pab.business_group_id = pap.business_group_id
761 AND pap.person_id = p_person_id
762 AND p_person_id BETWEEN p_start_person_id
763 AND p_end_person_id
764 AND pap.person_id = paa.person_id
765 AND pab.person_id = pap.person_id
766 AND paa.business_group_id = pap.business_group_id
767 AND paa.assignment_id = pab.abs_information5
768 AND pab.abs_information_category = 'NL_S'
769 AND pab.abs_information4 = 'L'
770 AND pap.business_group_id = ppa.business_group_id
771 AND ppa.payroll_action_id = p_payroll_action_id;
772 /**************************************************************************/
773
774 CURSOR csr_get_asg_org (p_org_id IN NUMBER
775 ,p_org_struct_id IN NUMBER
776 ,p_start_person_id IN NUMBER
777 ,p_end_person_id IN NUMBER
778 ,p_payroll_action_id IN NUMBER
779 ,p_start_date IN DATE
780 ,p_end_date IN DATE) IS
781 SELECT DISTINCT
782 paa.assignment_id assignment_id
783 FROM per_absence_attendance_types paat
784 ,per_absence_attendances pab
785 ,pay_payroll_actions ppa
786 ,per_org_structure_versions posv
787 ,per_all_assignments_f paa
788 ,per_all_people_f pap
789 WHERE posv.organization_structure_id = p_org_struct_id
790 AND posv.date_from <= p_end_date
791 AND nvl (posv.date_to,hr_general.end_of_time) >= p_start_date
792 AND (
793 paa.organization_id IN
794 (
795 (
796 SELECT pose.organization_id_child
797 FROM per_org_structure_elements pose
798 WHERE pose.org_structure_version_id = posv.org_structure_version_id
799 START WITH pose.organization_id_parent = p_org_id
800 CONNECT BY PRIOR organization_id_child = organization_id_parent
801 )
802 UNION
803 (
804 SELECT p_org_id
805 FROM dual
806 )
807 )
808 OR nvl (paa.establishment_id,- 1) = p_org_id
809 )
810 AND pap.person_id = paa.person_id
811 AND paa.person_id BETWEEN p_start_person_id
812 AND p_end_person_id
813 AND paa.business_group_id = pap.business_group_id
814 AND pab.person_id = pap.person_id
815 AND pab.date_start BETWEEN p_start_date
816 AND p_end_date
817 AND paat.absence_category = 'S'
818 AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
819 AND paat.business_group_id = pab.business_group_id
820 AND pab.business_group_id = pap.business_group_id
821 AND paa.assignment_id = pab.abs_information5
822 AND pab.abs_information_category = 'NL_S'
823 AND pab.abs_information4 = 'L'
824 AND pap.business_group_id = ppa.business_group_id
825 AND ppa.payroll_action_id = p_payroll_action_id;
826 /**************************************************************************/
827
828 CURSOR csr_get_asg_hier (p_org_struct_id IN NUMBER
829 ,p_start_person_id IN NUMBER
830 ,p_end_person_id IN NUMBER
831 ,p_payroll_action_id IN NUMBER
832 ,p_start_date IN DATE
833 ,p_end_date IN DATE) IS
834
835 SELECT DISTINCT
836 paa.assignment_id assignment_id
837 FROM per_absence_attendance_types paat
838 ,per_absence_attendances pab
839 ,per_all_assignments_f paa
840 ,per_all_people_f pap
841 ,pay_payroll_actions ppa
842 ,per_org_structure_versions posv
843 WHERE posv.organization_structure_id = p_org_struct_id
844 AND pap.person_id BETWEEN p_start_person_id
845 AND p_end_person_id
846 AND pap.business_group_id = ppa.business_group_id
847 AND ppa.payroll_action_id = p_payroll_action_id
848 AND paat.absence_category = 'S'
849 AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
850 AND paat.business_group_id = pab.business_group_id
851 AND pab.date_start BETWEEN p_start_date
852 AND p_end_date
853 AND paa.assignment_id = pab.abs_information5
854 AND pab.abs_information_category = 'NL_S'
855 AND pab.abs_information4 = 'L'
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_ltsr_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_LTSR_ABS_INFO'
895 AND pai.action_information4 = 'LTSR'
896 AND pai.action_information29 = 'NL_LTSR'
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_LTSR_ABS_INFO'
963 AND pai.action_information4 = 'LTSR'
964 AND pai.action_information29 = 'NL_LTSR'
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_LTSR_ABS_INFO'
1003 AND pai.action_information4 = 'LTSR'
1004 AND pai.action_information29 = 'NL_LTSR'
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_ltsr_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_start_date DATE;
1048 l_end_date DATE;
1049 l_type VARCHAR2(1) :=NULL;
1050
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 BEGIN
1071 hr_utility.trace('+====assignment_action_code================================+');
1072 hr_utility.trace('Payroll_action_id/Chunk: '||p_payroll_action_id||' / '||p_chunk );
1073 hr_utility.trace('Start/End Person ID : '||p_start_person_id||' / '||p_end_person_id );
1074
1075 get_all_parameters (
1076 p_payroll_action_id => p_payroll_action_id
1077 ,p_org_struct_id => l_org_struct_id
1078 ,p_person_id => l_person_id
1079 ,p_org_id => l_org_id
1080 ,p_bg_id => l_bg_id
1081 ,p_start_date => l_start_date
1082 ,p_end_date => l_end_date
1083 ,p_type => l_type);
1084
1085
1086 IF l_person_id is not NULL THEN
1087 hr_utility.trace(l_type||' Person selected ' ||l_person_id||' '||p_start_person_id||' '||p_end_person_id);
1088 IF l_type = 'I' THEN
1089 FOR v_csr_get_asg_person IN
1090 csr_get_asg_person(l_person_id, p_start_person_id, p_end_person_id
1091 , p_payroll_action_id, l_start_date, l_end_date)
1092 LOOP
1093 ins_assg_act(v_csr_get_asg_person.assignment_id,p_payroll_action_id,p_chunk);
1094 END LOOP;
1095 ELSIF l_type IN ('W','R') THEN
1096 FOR v_csr_get_asg_person IN
1097 csr_get_asg_person_archive(l_person_id, p_start_person_id, p_end_person_id
1098 , p_payroll_action_id, l_start_date, l_end_date,l_type)
1099 LOOP
1100 ins_assg_act(v_csr_get_asg_person.assignment_id,p_payroll_action_id,p_chunk);
1101 END LOOP;
1102 END IF;
1103 -----------------------------------------------
1104 ELSIF l_org_id is not NULL THEN
1105 hr_utility.trace(l_type||' Org selected ' ||l_org_id);
1106 IF l_type = 'I' THEN
1107 FOR v_csr_get_asg_org IN
1108 csr_get_asg_org(l_org_id, l_org_struct_id
1109 , p_start_person_id, p_end_person_id, p_payroll_action_id
1110 , l_start_date, l_end_date)
1111 LOOP
1112 ins_assg_act(v_csr_get_asg_org.assignment_id,p_payroll_action_id,p_chunk);
1113 END LOOP;
1114 ELSIF l_type IN ('W','R') THEN
1115 FOR v_csr_get_asg_org IN
1116 csr_get_asg_org_archive(l_org_id, l_org_struct_id
1117 , p_start_person_id, p_end_person_id, p_payroll_action_id
1118 , l_start_date, l_end_date,l_type)
1119 LOOP
1120 ins_assg_act(v_csr_get_asg_org.assignment_id,p_payroll_action_id,p_chunk);
1121 END LOOP;
1122 END IF;
1123 -----------------------------------------------
1124 ELSIF l_org_struct_id is not NULL THEN
1125 hr_utility.trace(l_type||' Hierarchy selected ' ||l_org_struct_id);
1126 IF l_type = 'I' THEN
1127 FOR v_csr_get_asg_hier IN
1128 csr_get_asg_hier(l_org_struct_id, p_start_person_id, p_end_person_id
1129 , p_payroll_action_id, l_start_date, l_end_date)
1130 LOOP
1131 ins_assg_act(v_csr_get_asg_hier.assignment_id,p_payroll_action_id,p_chunk);
1132 END LOOP;
1133 ELSIF l_type IN ('W','R') THEN
1134 FOR v_csr_get_asg_hier IN
1135 csr_get_asg_hier_archive(l_org_struct_id, p_start_person_id, p_end_person_id
1136 , p_payroll_action_id, l_start_date, l_end_date,l_type)
1137 LOOP
1138 ins_assg_act(v_csr_get_asg_hier.assignment_id,p_payroll_action_id,p_chunk);
1139 END LOOP;
1140 END IF;
1141 END IF;
1142 hr_utility.trace('X====assignment_action_code================================X');
1143 EXCEPTION
1144 WHEN others THEN
1145 hr_utility.trace('Exception in assignment_action_code SQL-ERRM : '||SQLERRM);
1146 END assignment_action_code;
1147
1148
1149 /*-------------------------------------------------------------------------------
1150 |Name : archive_code |
1151 |Type : Procedure |
1152 |Description : Archival code |
1153 -------------------------------------------------------------------------------*/
1154
1155
1156 PROCEDURE archive_code (p_assignment_action_id IN NUMBER
1157 ,p_effective_date IN DATE) IS
1158
1159 /**************************************************************************/
1160 CURSOR csr_get_org_info(p_assignment_id IN NUMBER
1161 , p_org_struct_id IN NUMBER
1162 , p_abs_start_date IN DATE) IS
1163 SELECT hou.organization_id org_id
1164 ,hou.name org_name
1165 ,hoi.org_information4 tax_reg
1166 FROM per_all_assignments_f pas
1167 ,hr_organization_units hou
1168 ,hr_organization_information hoi
1169 ,per_org_structure_versions posv
1170 WHERE posv.organization_structure_id = p_org_struct_id
1171 AND p_abs_start_date BETWEEN posv.date_from
1172 AND nvl (posv.date_to
1173 ,hr_general.end_of_time)
1174 AND pas.assignment_id = p_assignment_id
1175 AND hou.organization_id = hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
1176 ,pas.organization_id)
1177 AND hoi.organization_id = hou.organization_id
1178 AND hoi.org_information_context = 'NL_ORG_INFORMATION';
1179 /**************************************************************************/
1180 CURSOR csr_get_leg_info(p_assignment_id IN NUMBER) IS
1181 SELECT hou.organization_id org_id
1182 ,hou.name org_name
1183 ,hoi.org_information1 tax_reg
1184 FROM per_all_assignments_f pas
1185 ,hr_organization_units hou
1186 ,hr_organization_information hoi
1187 WHERE pas.assignment_id = p_assignment_id
1188 AND hou.organization_id = pas.establishment_id
1189 AND hoi.organization_id = hou.organization_id
1190 AND hoi.org_information_context = 'NL_LE_TAX_DETAILS';
1191 /**************************************************************************/
1192 CURSOR csr_get_abs_details (p_assignment_action_id IN NUMBER
1193 , p_start_date IN DATE
1194 , p_end_date IN DATE) IS
1195 SELECT paa.assignment_id assignment_id
1196 , pas.person_id person_id
1197 , pab.absence_attendance_id abs_att_id
1198 , pab.date_start start_date
1199 , pab.date_end end_date
1200 , paat.absence_category abs_cat
1201 , pab.abs_information1 ass_id
1202 , pab.abs_information3 sick_ref
1203 , to_char (pab.date_start, 'YYYYMMDD') lts_st_dt
1204 , to_char (pab.date_end, 'YYYYMMDD') lts_end_dt
1205 , pab.abs_information4 name_contact
1206 , pab.abs_information5 est_name
1207 , pab.abs_information6 gender
1208 , pab.abs_information7 tel_no
1209 , pab.abs_information_category info_cat
1210 FROM per_absence_attendance_types paat
1211 , per_absence_attendances pab
1212 , pay_assignment_actions paa
1213 , per_all_assignments_f pas
1214 WHERE paat.absence_category = 'S'
1215 AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
1216 AND paat.business_group_id = pab.business_group_id
1217 AND pab.date_start BETWEEN p_start_date
1218 AND p_end_date
1219 AND pab.date_start BETWEEN pas.effective_start_date
1220 AND pas.effective_end_date
1221 AND pab.business_group_id = pas.business_group_id
1222 AND paa.assignment_action_id = p_assignment_action_id
1223 AND pas.assignment_id = paa.assignment_id
1224 AND pab.abs_information5 = pas.assignment_id
1225 AND pab.person_id = pas.person_id
1226 AND pab.abs_information_category = 'NL_S'
1227 AND pab.abs_information4 = 'L';
1228
1229
1230 /**************************************************************************/
1231
1232 CURSOR csr_already_exists(p_assignment_id IN NUMBER, p_abs_start_date IN DATE) IS
1233 SELECT 'Y'
1234 FROM pay_action_information pai
1235 WHERE pai.action_context_type = 'AAP'
1236 AND pai.action_information_category = 'NL_LTSR_ABS_INFO'
1237 AND pai.ASSIGNMENT_ID = p_assignment_id
1238 AND fnd_date.canonical_to_date(pai.action_information2) = p_abs_start_date
1239 AND pai.action_information30 = 'N';
1240 /**************************************************************************/
1241 /* sender and contact data */
1242 CURSOR csr_get_org_contact_info(p_assignment_id IN NUMBER
1243 , p_start_date IN DATE) IS
1244 SELECT decode (hoi.org_information1,'Y','1','N','2') test_msg
1245 ,hoi.org_information2 sector
1246 ,lpad(hoi.org_information3, 2, 0) risk_group
1247 ,hoi.org_information4 role_sender
1248 ,hoi.org_information5 tax_no_sender
1249 ,hoi.org_information6 contact_per_or_dept
1250 ,hoi.org_information7 est_name
1251 ,hoi.org_information8 gender_contact
1252 ,hoi.org_information9 ph_no_contact
1253 FROM per_all_assignments_f pas
1254 ,hr_organization_units hou
1255 ,hr_organization_information hoi
1256 WHERE pas.assignment_id = p_assignment_id
1257 AND p_start_date BETWEEN pas.effective_start_date
1258 AND pas.effective_end_date
1259 AND hou.organization_id = nvl (pas.establishment_id
1260 ,pas.organization_id)
1261 AND hoi.organization_id = hou.organization_id
1262 AND hoi.org_information_context = 'NL_ORG_DSR_INFO';
1263 /**************************************************************************/
1264 /* employee data */
1265 CURSOR csr_get_person_info(p_assignment_id IN NUMBER
1266 , p_start_date IN DATE
1267 , p_end_date IN DATE) IS
1268 SELECT pap.national_identifier sofi_number
1269 ,to_char (pap.date_of_birth,'YYYYMMDD') date_of_birth
1270 ,decode (pap.sex,'M','1','F','2',NULL,'9') gender
1271 ,REPLACE(REPLACE(pap.per_information1, '.', ''), ' ', '') init
1272 ,pap.pre_name_adjunct prefix
1273 ,pap.last_name last_name
1274 ,pap.employee_number employee_number
1275 ,pap.person_id person_id
1276 ,pap.business_group_id bg_id
1277 ,pas.establishment_id establishment_id
1278 ,pas.assignment_sequence asg_seq
1279 FROM per_all_people_f pap
1280 ,per_all_assignments_f pas
1281 WHERE pas.assignment_id = p_assignment_id
1282 AND p_start_date BETWEEN pas.effective_start_date
1283 AND pas.effective_end_date
1284 AND pap.person_id = pas.person_id
1285 AND p_start_date BETWEEN pap.effective_start_date
1286 AND pap.effective_end_date;
1287 /**************************************************************************/
1288
1289 CURSOR get_archived_absence(p_assignment_id IN NUMBER
1290 , p_start_date IN DATE
1291 , p_end_date IN DATE
1292 , p_type IN VARCHAR2) IS
1293 SELECT pai.action_information1 abs_att_id
1294 ,pai.action_information2 st_dt
1295 ,pai.action_information3 end_dt
1296 ,pai.action_context_id org_asg_act_id
1297 ,pai.action_information_id act_info_id
1298 FROM pay_action_information pai
1299 WHERE pai.assignment_id = p_assignment_id
1300 AND pai.action_context_type = 'AAP'
1301 AND pai.action_information_category = 'NL_LTSR_ABS_INFO'
1302 AND pai.action_information29 = 'NL_LTSR'
1303 AND fnd_date.canonical_to_date (pai.action_information2) BETWEEN p_start_date
1304 AND p_end_date
1305 AND (
1306 (
1307 pai.action_information30 = 'N'
1308 AND p_type = 'W'
1309 )
1310 OR (
1311 p_type = 'R'
1312 )
1313 );
1314 /**************************************************************************/
1315 CURSOR csr_get_sickness_contact (p_assignment_id IN NUMBER
1316 , p_start_date IN DATE) IS
1317 SELECT pei_information21 contact_name
1318 ,pei_information22 est_name
1319 ,pei_information23 gender
1320 ,pei_information24 contact_ph_no
1321 -- ,pei_information3 l29bzw
1322 FROM per_all_assignments_f pas
1323 ,per_people_extra_info pei
1324 WHERE pas.assignment_id = p_assignment_id
1325 AND p_start_date BETWEEN pas.effective_start_date
1326 AND pas.effective_end_date
1327 AND pei.person_id = pas.person_id
1328 AND pei.information_type = 'NL_DS_SICK_INFO';
1329 /**************************************************************************/
1330 CURSOR c_get_pact_chunk(c_assignment_action_id IN NUMBER ) IS
1331 SELECT ppa.payroll_action_id
1332 ,paa.chunk_number
1333 FROM pay_payroll_actions ppa
1334 ,pay_assignment_actions paa
1335 WHERE paa.payroll_action_id = ppa.payroll_action_id
1336 AND paa.assignment_action_id = c_assignment_action_id;
1337 /**************************************************************************/
1338 CURSOR c_get_assid_chunk(c_assignment_action_id IN NUMBER ) IS
1339 SELECT paa.assignment_id
1340 ,paa.chunk_number
1341 FROM pay_assignment_actions paa
1342 ,pay_payroll_actions ppa
1343 WHERE paa.payroll_action_id = ppa.payroll_action_id
1344 AND paa.assignment_action_id = c_assignment_action_id;
1345 /**************************************************************************/
1346 CURSOR csr_numiv_override(p_assignment_id IN NUMBER) IS
1347 SELECT paei.aei_information1 numiv_override
1348 FROM per_assignment_extra_info paei
1349 WHERE paei.assignment_id = p_assignment_id
1350 AND paei.aei_information_category = 'NL_NUMIV_OVERRIDE';
1351 /**************************************************************************/
1352
1353 l_org_struct_id NUMBER :=NULL;
1354 l_person_id NUMBER :=NULL;
1355 l_org_id NUMBER :=NULL;
1356 l_bg_id NUMBER;
1357 l_child_aa_id NUMBER;
1358 l_start_date DATE :=NULL;
1359 l_end_date DATE :=NULL;
1360 l_type VARCHAR2(1) :=NULL;
1361 l_abs_attendence_id NUMBER;
1362 l_payroll_action_id NUMBER;
1363 l_est_id NUMBER :=NULL;
1364 l_tax_reg_num VARCHAR2(100);
1365 l_org_name VARCHAR2(150);
1366 l_abs_start_date DATE :=NULL;
1367 l_abs_end_date DATE :=NULL;
1368 l_assignment_id NUMBER :=NULL;
1369 l_action_info_id NUMBER;
1370 l_ovn NUMBER;
1371 l_exists VARCHAR2(1);
1372 l_chunk_number NUMBER;
1373 l_person_info csr_get_person_info%ROWTYPE;
1374 l_org_contact_info csr_get_org_contact_info%ROWTYPE;
1375 l_sickness_contact csr_get_sickness_contact%ROWTYPE;
1376 l_numiv_override per_assignment_extra_info.aei_information1%TYPE;
1377
1378
1379 BEGIN
1380
1381 hr_utility.trace('+====archive_code==========================================+');
1382 hr_utility.trace('p_assignment_action_id : '||p_assignment_action_id);
1383 hr_utility.trace('p_effective_date : '||p_effective_date);
1384
1385 OPEN c_get_pact_chunk (p_assignment_action_id);
1386 FETCH c_get_pact_chunk INTO l_payroll_action_id, l_chunk_number;
1387 CLOSE c_get_pact_chunk;
1388
1389 hr_utility.trace('l_payroll_action_id : '||l_payroll_action_id);
1390 hr_utility.trace('l_chunk_number : '||l_chunk_number);
1391
1392 get_all_parameters (
1393 p_payroll_action_id => l_payroll_action_id
1394 ,p_org_struct_id => l_org_struct_id
1395 ,p_person_id => l_person_id
1396 ,p_org_id => l_org_id
1397 ,p_bg_id => l_bg_id
1398 ,p_start_date => l_start_date
1399 ,p_end_date => l_end_date
1400 ,p_type => l_type);
1401
1402 IF l_type = 'I' THEN
1403 FOR v_csr_get_abs_details IN csr_get_abs_details(p_assignment_action_id, l_start_date, l_end_date)
1404 LOOP
1405 l_exists := 'N';
1406 l_assignment_id := v_csr_get_abs_details.assignment_id;
1407 l_abs_attendence_id := v_csr_get_abs_details.abs_att_id;
1408 l_abs_start_date := v_csr_get_abs_details.start_date;
1409 l_abs_end_date := v_csr_get_abs_details.end_date;
1410 hr_utility.trace('Assignment/Attendence : ' || l_assignment_id||'/'||l_abs_attendence_id);
1411 hr_utility.trace('Absence Start/End Date : ' || to_char(l_abs_start_date,'DDMonYYYY') || ' / ' ||
1412 to_char(l_abs_end_date,'DDMonYYYY'));
1413
1414 OPEN csr_already_exists (l_assignment_id,l_abs_start_date);
1415 FETCH csr_already_exists INTO l_exists;
1416 CLOSE csr_already_exists;
1417 -- hr_utility.trace('l_exists : '||l_exists);
1418 IF l_exists = 'Y' THEN
1419 fnd_file.put_line(fnd_file.log,'Long Term Sickness Report already sent. Hence skipped.');
1420 ELSIF l_exists = 'N' THEN
1421
1422 SELECT pay_assignment_actions_s.NEXTVAL INTO l_child_aa_id FROM dual;
1423
1424 hr_nonrun_asact.insact(
1425 lockingactid => l_child_aa_id
1426 , assignid => l_assignment_id
1427 , pactid => l_payroll_action_id
1428 , chunk => l_chunk_number
1429 , status => 'C'
1430 , source_act => p_assignment_action_id);
1431
1432 OPEN csr_get_org_contact_info(l_assignment_id, l_abs_start_date );
1433 FETCH csr_get_org_contact_info into l_org_contact_info;
1434 CLOSE csr_get_org_contact_info;
1435
1436
1437 OPEN csr_get_sickness_contact(l_assignment_id, l_abs_start_date );
1438 FETCH csr_get_sickness_contact INTO l_sickness_contact;
1439 CLOSE csr_get_sickness_contact;
1440
1441 /* Archiving absence details */
1442 pay_action_information_api.create_action_information
1443 (
1444 p_action_information_id => l_action_info_id
1445 , p_action_context_id => l_child_aa_id
1446 , p_action_context_type => 'AAP'
1447 , p_object_version_number => l_ovn
1448 , p_assignment_id => l_assignment_id
1449 , p_effective_date => p_effective_date
1450 , p_source_id => NULL
1451 , p_source_text => NULL
1452 , p_action_information_category => 'NL_LTSR_ABS_INFO'
1453 , p_action_information1 => l_abs_attendence_id
1454 , p_action_information2 => fnd_date.date_to_canonical(l_abs_start_date)
1455 , p_action_information3 => fnd_date.date_to_canonical(l_abs_end_date)
1456 , p_action_information4 => 'LTSR'
1457 , p_action_information5 => v_csr_get_abs_details.person_id
1458 , p_action_information6 => v_csr_get_abs_details.ass_id
1459 , p_action_information7 => v_csr_get_abs_details.sick_ref
1460 , p_action_information8 => v_csr_get_abs_details.lts_st_dt
1461 , p_action_information9 => v_csr_get_abs_details.lts_end_dt
1462 , p_action_information10 => l_sickness_contact.contact_name
1463 , p_action_information11 => l_sickness_contact.est_name
1464 , p_action_information12 => l_sickness_contact.gender
1465 , p_action_information13 => l_sickness_contact.contact_ph_no
1466 , p_action_information29 => 'NL_LTSR'
1467 , p_action_information30 => 'N'
1468 );
1469
1470 OPEN csr_get_person_info(l_assignment_id, l_abs_start_date, l_abs_end_date);
1471 FETCH csr_get_person_info into l_person_info;
1472 CLOSE csr_get_person_info;
1473
1474 l_est_id := l_person_info.establishment_id;
1475 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
1476 OPEN csr_get_leg_info(l_assignment_id);
1477 FETCH csr_get_leg_info INTO l_org_id, l_org_name, l_tax_reg_num;
1478 CLOSE csr_get_leg_info;
1479 ELSE
1480 OPEN csr_get_org_info(l_assignment_id, l_org_struct_id, l_abs_start_date);
1481 FETCH csr_get_org_info INTO l_org_id, l_org_name, l_tax_reg_num;
1482 CLOSE csr_get_org_info;
1483 END IF;
1484
1485 OPEN csr_numiv_override(l_assignment_id);
1486 FETCH csr_numiv_override INTO l_numiv_override;
1487 CLOSE csr_numiv_override;
1488
1489 /* Archiving Employee and employer info */
1490 pay_action_information_api.create_action_information
1491 (
1492 p_action_information_id => l_action_info_id
1493 , p_action_context_id => l_child_aa_id
1494 , p_action_context_type => 'AAP'
1495 , p_object_version_number => l_ovn
1496 , p_assignment_id => l_assignment_id
1497 , p_effective_date => p_effective_date
1498 , p_source_id => NULL
1499 , p_source_text => NULL
1500 , p_action_information_category => 'NL_LTSR_EMP_INFO'
1501 , p_action_information1 => l_abs_attendence_id
1502 , p_action_information2 => l_org_contact_info.test_msg
1503 , p_action_information3 => l_org_contact_info.tax_no_sender
1504 , p_action_information4 => l_org_contact_info.role_sender
1505 , p_action_information5 => l_org_contact_info.contact_per_or_dept
1506 , p_action_information6 => l_org_contact_info.ph_no_contact
1507 , p_action_information7 => l_org_contact_info.risk_group
1508 , p_action_information8 => l_org_contact_info.sector
1509 , p_action_information9 => l_tax_reg_num
1510 , p_action_information11 => l_person_info.sofi_number
1511 , p_action_information12 => l_person_info.date_of_birth
1512 , p_action_information13 => l_person_info.gender
1513 , p_action_information14 => l_person_info.init
1514 , p_action_information15 => l_person_info.prefix
1515 , p_action_information16 => l_person_info.last_name
1516 , p_action_information21 => nvl(l_numiv_override,l_person_info.asg_seq)
1517 , p_action_information22 => l_person_info.employee_number
1518 , p_action_information23 => l_org_contact_info.est_name
1519 , p_action_information24 => l_org_contact_info.gender_contact
1520 );
1521
1522 END IF;
1523 END LOOP;
1524
1525 ELSIF l_type IN ('W','R') THEN
1526 hr_utility.trace(l_type||' Assignment Action ID : '||p_assignment_action_id );
1527 OPEN c_get_assid_chunk (p_assignment_action_id);
1528 FETCH c_get_assid_chunk INTO l_assignment_id, l_chunk_number;
1529 CLOSE c_get_assid_chunk;
1530 hr_utility.trace('Assignment ID/Chunk : '||l_assignment_id||' / '||l_chunk_number);
1531
1532 FOR r_get_archived_absence IN get_archived_absence(l_assignment_id, l_start_date, l_end_date, l_type)
1533 LOOP
1534
1535 hr_utility.trace('Absence Attendence ID : ' || r_get_archived_absence.abs_att_id);
1536
1537 hr_utility.trace('Absence Start/End Date : ' ||
1538 to_char(fnd_date.canonical_to_date(r_get_archived_absence.st_dt),'DDMonYYYY') || ' / ' ||
1539 to_char(fnd_date.canonical_to_date(r_get_archived_absence.end_dt),'DDMonYYYY'));
1540 hr_utility.trace('Org Asg Action ID : ' || r_get_archived_absence.org_asg_act_id);
1541
1542 hr_nonrun_asact.insint(lockingactid => p_assignment_action_id
1543 , lockedactid => r_get_archived_absence.org_asg_act_id);
1544 IF l_type = 'W' THEN
1545 -- Update previous archive incase of withdrawal.
1546 pay_action_information_api.update_action_information
1547 (p_action_information_id => r_get_archived_absence.act_info_id
1548 , p_object_version_number => l_ovn
1549 , p_action_information30 => 'Y'
1550 );
1551 END IF;
1552 END LOOP;
1553 END IF;
1554
1555 hr_utility.trace('X====archive_code==========================================X');
1556 EXCEPTION
1557 WHEN TOO_MANY_ROWS THEN
1558 hr_utility.trace('Too Many Rows Exception in archive_code');
1559
1560 WHEN NO_DATA_FOUND THEN
1561 hr_utility.trace('No Data Found Exception in archive_code');
1562
1563 WHEN others THEN
1564 hr_utility.trace('Exception in archive_code SQL-ERRM : '||SQLERRM);
1565
1566 END archive_code;
1567 /**************************************************************************/
1568
1569 /*-------------------------------------------------------------------------------
1570 |Name : DEINITIALIZATION_CODE |
1571 |Type : Procedure |
1572 |Description : Deinitialization code |
1573 -------------------------------------------------------------------------------*/
1574
1575 PROCEDURE deinitialization_code(p_actid IN NUMBER) IS
1576
1577
1578 BEGIN
1579 hr_utility.trace('+====deinitialization_code====X '|| p_actid);
1580 END deinitialization_code;
1581
1582
1583
1584
1585
1586 /**************************************************************************/
1587 PROCEDURE gen_header_xml
1588 IS
1589 l_string VARCHAR2(32767) := NULL;
1590 l_string_value VARCHAR2(1000) := NULL;
1591 l_clob pay_file_details.file_fragment%TYPE;
1592 l_blob pay_file_details.blob_file_fragment%TYPE;
1593 l_payroll_action_id NUMBER;
1594 l_org_struct_id NUMBER := NULL;
1595 l_person_id NUMBER := NULL;
1596 l_org_id NUMBER := NULL;
1597 l_bg_id NUMBER;
1598 l_start_date DATE := NULL;
1599 l_end_date DATE := NULL;
1600 l_type VARCHAR2(1) := NULL;
1601 l_lang VARCHAR2(2) := userenv('lang');
1602
1603 CURSOR c_get_hierarchy(p_org_struct_id IN NUMBER) IS
1604 SELECT name
1605 FROM per_organization_structures
1606 WHERE organization_structure_id = p_org_struct_id;
1607
1608 CURSOR c_get_employer(p_org_id IN NUMBER) IS
1609 SELECT name
1610 FROM hr_organization_units
1611 WHERE organization_id = p_org_id;
1612
1613 CURSOR c_get_employee(p_person_id IN NUMBER) IS
1614 SELECT full_name
1615 FROM per_all_people_f
1616 WHERE person_id = p_person_id;
1617
1618 BEGIN
1619 hr_utility.trace('+====gen_header_xml========================================+');
1620 l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
1621 hr_utility.trace('l_payroll_action_id : '||l_payroll_action_id);
1622 l_string := l_string || '<ROOT>'||EOL;
1623
1624 get_all_parameters (
1625 p_payroll_action_id => l_payroll_action_id
1626 ,p_org_struct_id => l_org_struct_id
1627 ,p_person_id => l_person_id
1628 ,p_org_id => l_org_id
1629 ,p_bg_id => l_bg_id
1630 ,p_start_date => l_start_date
1631 ,p_end_date => l_end_date
1632 ,p_type => l_type);
1633
1634 l_string_value := get_look_up_value('HR_NL_DS_REPORT_TYPE',l_type,l_lang);
1635 l_string := l_string || '<P_REPORT_TYPE>'||clean_XML(l_string_value)||'</P_REPORT_TYPE>'||EOL;
1636
1637 l_string_value := fnd_date.date_to_chardate(l_start_date);
1638 l_string := l_string || '<P_DATE_FROM>'||clean_XML(l_string_value)||'</P_DATE_FROM>'||EOL;
1639
1640 l_string_value := fnd_date.date_to_chardate(l_end_date);
1641 l_string := l_string || '<P_DATE_TO>'||clean_XML(l_string_value)||'</P_DATE_TO>'||EOL;
1642
1643 IF l_org_struct_id IS NOT NULL THEN
1644 OPEN c_get_hierarchy(l_org_struct_id);
1645 FETCH c_get_hierarchy INTO l_string_value;
1646 CLOSE c_get_hierarchy;
1647 l_string := l_string || '<P_HIERARCHY>'||clean_XML(l_string_value)||'</P_HIERARCHY>'||EOL;
1648 END IF;
1649
1650 IF l_org_id IS NOT NULL THEN
1651 OPEN c_get_employer(l_org_id);
1652 FETCH c_get_employer INTO l_string_value;
1653 CLOSE c_get_employer;
1654 l_string := l_string || '<P_EMPLOYER>'||clean_XML(l_string_value)||'</P_EMPLOYER>'||EOL;
1655 END IF;
1656
1657 IF l_person_id IS NOT NULL THEN
1658 OPEN c_get_employee(l_person_id);
1659 FETCH c_get_employee INTO l_string_value;
1660 CLOSE c_get_employee;
1661 l_string := l_string || '<P_EMPLOYEE>'||clean_XML(l_string_value)||'</P_EMPLOYEE>'||EOL;
1662 END IF;
1663
1664 l_clob := l_clob||l_string;
1665 IF l_clob IS NOT NULL THEN
1666 l_blob := c2b(l_clob);
1667 pay_core_files.write_to_magtape_lob(l_blob);
1668 END IF;
1669 hr_utility.trace('X====gen_header_xml========================================X');
1670 EXCEPTION
1671 WHEN others THEN
1672 hr_utility.trace('Exception in gen_header_xml SQL-ERRM : '||SQLERRM);
1673 END gen_header_xml;
1674
1675
1676 /**************************************************************************/
1677 PROCEDURE gen_footer_xml
1678 IS
1679 l_buf VARCHAR2(32767);
1680 BEGIN
1681 hr_utility.trace('+====gen_footer_xml========================================+');
1682 l_buf := l_buf || '</ROOT>'||EOL ;
1683 pay_core_files.write_to_magtape_lob(l_buf);
1684 hr_utility.trace('X====gen_footer_xml========================================X');
1685 EXCEPTION
1686 WHEN others THEN
1687 hr_utility.trace('Exception in gen_footer_xml SQL-ERRM : '||SQLERRM);
1688 END gen_footer_xml;
1689 /**************************************************************************/
1690 PROCEDURE gen_body_xml_main(p_type varchar2, p_id number) IS
1691
1692
1693 CURSOR cur_abs_info_details (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
1694 SELECT pai.action_information1 abs_att_id
1695 ,pai.action_information2 start_date
1696 ,pai.action_information3 end_date
1697 ,pai.action_information4 abs_cat
1698 ,pai.action_information5 person_id
1699 ,pai.action_information6 ass_id
1700 ,pai.action_information7 sick_ref
1701 ,pai.action_information8 lts_st_dt
1702 ,pai.action_information9 lts_end_dt
1703 ,pai.action_information10 contact_name
1704 ,pai.action_information11 est_name
1705 ,pai.action_information12 contact_gender
1706 ,pai.action_information13 contact_ph
1707 FROM pay_action_information pai
1708 WHERE pai.action_context_id = c_assignment_action_id
1709 AND pai.action_information_category = 'NL_LTSR_ABS_INFO'
1710 AND action_context_type = 'AAP';
1711 /**************************************************************************/
1712 CURSOR cur_emp_details (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
1713 SELECT action_information2 test_msg
1714 ,action_information3 tax_no_sender
1715 ,action_information4 role_sender
1716 ,action_information5 contact_per_or_dept
1717 ,action_information6 ph_no_contact
1718 ,action_information7 risk_group
1719 ,action_information8 sector
1720 ,action_information9 tax_reg_num
1721 ,action_information11 sofi_number
1722 ,action_information12 date_of_birth
1723 ,action_information13 gender
1724 ,action_information14 init
1725 ,action_information15 prefix
1726 ,action_information16 last_name
1727 ,action_information21 asg_seq
1728 ,action_information22 emp_num
1729 ,action_information23 est_name
1730 ,action_information24 gender_contact
1731 FROM pay_action_information pai
1732 WHERE pai.action_context_id = c_assignment_action_id
1733 AND pai.action_information_category = 'NL_LTSR_EMP_INFO'
1734 AND action_context_type = 'AAP';
1735
1736 /**************************************************************************/
1737 CURSOR cur_withdrawl_flag(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) is
1738 SELECT pai.action_information30 flag
1739 FROM pay_action_information pai
1740 WHERE pai.action_context_id = c_assignment_action_id
1741 AND pai.action_information_category = 'NL_LTSR_ABS_INFO'
1742 AND action_context_type = 'AAP';
1743 /**************************************************************************/
1744 CURSOR cur_app_version IS
1745 SELECT release_name
1746 FROM fnd_product_groups;
1747 /**************************************************************************/
1748 --Variables--
1749
1750 l_clob PAY_FILE_DETAILS.FILE_FRAGMENT%TYPE;
1751 l_blob PAY_FILE_DETAILS.BLOB_FILE_FRAGMENT%TYPE;
1752 l_file UTL_FILE.FILE_TYPE;
1753 l_directory_path VARCHAR2(500);
1754 l_file_name VARCHAR2(50);
1755 x NUMBER := 0;
1756 r NUMBER := 0;
1757 l_type VARCHAR2(1);
1758 l_lang VARCHAR2(2) := userenv('lang');
1759 l_abs_info cur_abs_info_details%ROWTYPE;
1760 l_emp_details cur_emp_details%ROWTYPE;
1761 l_get_app_ver cur_app_version%ROWTYPE;
1762 l_withdrawl_flag cur_withdrawl_flag%ROWTYPE;
1763 xXMLTable tXMLTable;
1764 rXMLTable tXMLTable;
1765
1766
1767 PROCEDURE Tag (xCtr IN OUT NOCOPY NUMBER
1768 ,rCtr IN OUT NOCOPY NUMBER
1769 ,pTagName IN VARCHAR2
1770 ,pTagValue IN VARCHAR2
1771 ,pDestination IN VARCHAR2)
1772 IS
1773 BEGIN
1774 IF pTagValue IS NOT NULL THEN
1775 IF pDestination IN ('X','B') THEN --XML or Both
1776 xXMLTable(xCtr).TagName := pTagName;
1777 xXMLTable(xCtr).TagValue := pTagValue;
1778
1779 IF pTagName = 'UwvZwMlaMelding' AND pTagValue = '_START_' THEN
1780 xXMLTable(xCtr).TagName := xXMLTable(xCtr).TagName||EOL||
1781 'xsi:schemaLocation="http://schemas.uwv.nl/UwvML/Berichten/UwvZwMlaMelding-v0207 UwvZwMlaMelding-v0207-b01.xsd"'||EOL||
1782 'xmlns:str="http://schemas.uwv.nl/UwvML/Berichten/UwvZwMlaMelding-v0207"'||EOL||
1783 'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"';
1784 END IF;
1785
1786 xCtr := xCtr + 1;
1787 END IF;
1788 IF pDestination IN ('R','B') THEN --Report or Both
1789 rXMLTable(rCtr).TagName := pTagName;
1790 rXMLTable(rCtr).TagValue := pTagValue;
1791 rCtr := rCtr + 1;
1792 END IF;
1793 END IF;
1794 END Tag;
1795
1796
1797 BEGIN
1798 hr_utility.trace('+====gen_body_xml_main=====================================+');
1799
1800 OPEN cur_abs_info_details(p_id);
1801 FETCH cur_abs_info_details INTO l_abs_info;
1802 CLOSE cur_abs_info_details;
1803
1804 OPEN cur_emp_details(p_id);
1805 FETCH cur_emp_details INTO l_emp_details;
1806 CLOSE cur_emp_details;
1807
1808 OPEN cur_app_version;
1809 FETCH cur_app_version into l_get_app_ver;
1810 CLOSE cur_app_version;
1811
1812 IF p_type = 'R' THEN
1813 OPEN cur_withdrawl_flag(p_id);
1814 FETCH cur_withdrawl_flag into l_withdrawl_flag;
1815 CLOSE cur_withdrawl_flag;
1816 END IF;
1817 hr_utility.trace('Fetched all data. XML Generation starts');
1818 Tag(x,r,'UwvZwMlaMelding','_START_','B'); -- LT sickness report*
1819 Tag(x,r,'IndTestbericht',l_emp_details.test_msg,'X'); -- test msg
1820 Tag(x,r,'KetenPartij','_START_','B'); -- + sender data*
1821 Tag(x,r,'Loonheffingennr',l_emp_details.tax_no_sender,'X'); -- tax reg no sender*
1822 Tag(x,r,'CdRolKetenpartij',l_emp_details.role_sender,'X'); -- role sender*
1823 Tag(x,r,'NaamSoftwarePakket','Oracle HCM','X'); -- appln name
1824 Tag(x,r,'VersieSoftwarePakket',l_get_app_ver.release_name,'X'); -- ver no
1825 IF l_emp_details.contact_per_or_dept IS NOT NULL
1826 OR l_emp_details.ph_no_contact IS NOT NULL THEN
1827 Tag(x,r,'ContactGegevens','_START_','B'); -- + contact data
1828 Tag(x,r,'NaamContactpersoonAfd',l_emp_details.contact_per_or_dept,'B'); -- person or dept
1829 Tag(x,r,'TelefoonnrContactpersoonAfd',l_emp_details.ph_no_contact,'B'); -- ph no
1830 Tag(x,r,'ContactGegevens','_END_','B'); -- - contact data
1831 END IF;
1832 Tag(x,r,'KetenPartij','_END_','B'); -- - sender data*
1833 Tag(x,r,'AdministratieveEenheid','_START_','B'); -- + employer*
1834 Tag(x,r,'Loonheffingennr',l_emp_details.tax_reg_num,'X'); -- tax no employer*
1835 Tag(x,r,'SectorRisicogroep','_START_','X'); -- + risk sector grp*
1836 Tag(x,r,'CdRisicoPremiegroep',l_emp_details.risk_group,'X'); -- risk grp*
1837 Tag(x,r,'CdSectorOsv',l_emp_details.sector,'X'); -- sector *
1838 Tag(x,r,'SectorRisicogroep','_END_','X'); -- - risk sector grp*
1839 Tag(x,r,'NatuurlijkPersoon','_START_','B'); -- + employee data*
1840 Tag(x,r,'Burgerservicenr',l_emp_details.sofi_number,'B'); -- sofino*
1841 Tag(x,r,'Geboortedat',l_emp_details.date_of_birth,'X'); -- dob*
1842 Tag(x,r,'Geboortedat',fnd_date.date_to_chardate(
1843 to_date(l_emp_details.date_of_birth,'YYYYMMDD')),'R');
1844 Tag(x,r,'Geslacht',l_emp_details.gender,'X'); -- gender
1845 Tag(x,r,'Geslacht',get_look_up_value('HR_NL_DS_GENDER',
1846 l_emp_details.gender,l_lang),'R');
1847 Tag(x,r,'Voorletters',l_emp_details.init,'B'); -- initials
1848 Tag(x,r,'Voorvoegsel',l_emp_details.prefix,'B'); -- prefix
1849 Tag(x,r,'SignificantDeelVanDeAchternaam',l_emp_details.last_name,'B'); -- last name*
1850 Tag(x,r,'Arbeidsverhouding','_START_','B'); -- + assignment details*
1851 Tag(x,r,'NrInkomstenverhouding',l_emp_details.asg_seq,'B'); -- NumIV override/asg seq
1852 Tag(x,r,'MeldingLangdurigAo','_START_','B'); -- + LT sickness details
1853 IF (p_type = 'R' AND l_withdrawl_flag.flag = 'Y') OR p_type = 'W' THEN
1854 Tag(x,r,'IndVerzoekTotIntrekken','1','X'); -- withdrawal code
1855 Tag(x,r,'IndVerzoekTotIntrekken',get_look_up_value('HR_NL_YES_NO','Y',l_lang),'R');
1856 END IF;
1857 Tag(x,r,'AoPeriode','_START_','B'); -- + period
1858 Tag(x,r,'DatBAo',l_abs_info.lts_st_dt,'X'); -- start day
1859 Tag(x,r,'DatBAo',fnd_date.date_to_chardate(to_date(
1860 l_abs_info.lts_st_dt,'YYYYMMDD')),'R');
1861 Tag(x,r,'DatEAo',l_abs_info.lts_end_dt,'X'); -- end day
1862 Tag(x,r,'DatEAo',fnd_date.date_to_chardate(to_date(
1863 l_abs_info.lts_end_dt,'YYYYMMDD')),'R');
1864 Tag(x,r,'AoPeriode','_END_','B'); -- - period
1865 IF l_abs_info.contact_name IS NOT NULL
1866 OR l_abs_info.est_name IS NOT NULL
1867 OR l_abs_info.contact_gender IS NOT NULL
1868 OR l_abs_info.contact_ph IS NOT NULL THEN
1869 Tag(x,r,'ContactGegevens','_START_','B'); -- + contact details
1870 Tag(x,r,'NaamContactpersoonAfd',l_abs_info.contact_name,'B'); -- name
1871 Tag(x,r,'OmschrijvingLokaleVestiging',l_abs_info.est_name,'B'); -- Establishment
1872 Tag(x,r,'Geslacht',l_abs_info.contact_gender,'X'); -- gender
1873 Tag(x,r,'Geslacht',get_look_up_value('HR_NL_DS_GENDER',
1874 l_abs_info.contact_gender,l_lang),'R');
1875 Tag(x,r,'TelefoonnrContactpersoonAfd',l_abs_info.contact_ph,'B'); -- telephone
1876 Tag(x,r,'ContactGegevens','_END_','B'); -- - contact details
1877 ELSIF l_emp_details.contact_per_or_dept IS NOT NULL
1878 OR l_emp_details.ph_no_contact IS NOT NULL
1879 OR l_emp_details.est_name IS NOT NULL
1880 OR l_emp_details.gender_contact IS NOT NULL
1881 THEN
1882 Tag(x,r,'ContactGegevens','_START_','B'); -- + contact details
1883 Tag(x,r,'NaamContactpersoonAfd',l_emp_details.contact_per_or_dept,'B'); -- name
1884 Tag(x,r,'OmschrijvingLokaleVestiging',l_emp_details.est_name,'B'); -- Establishment
1885 Tag(x,r,'Geslacht',l_emp_details.gender_contact,'X'); -- gender
1886 Tag(x,r,'Geslacht',get_look_up_value('HR_NL_DS_GENDER',
1887 l_emp_details.gender_contact,l_lang),'R');
1888 Tag(x,r,'TelefoonnrContactpersoonAfd',l_emp_details.ph_no_contact,'B'); -- telephone
1889 Tag(x,r,'ContactGegevens','_END_','B'); -- - contact details
1890 END IF;
1891 Tag(x,r,'MeldingLangdurigAo','_END_','B'); -- - LT sickness details
1892 Tag(x,r,'Arbeidsverhouding','_END_','B'); -- - assignment details*
1893 Tag(x,r,'NatuurlijkPersoon','_END_','B'); -- - employee data*
1894 Tag(x,r,'AdministratieveEenheid','_END_','B'); -- - employer*
1895 Tag(x,r,'UwvZwMlaMelding','_END_','B'); -- LT sickness report*
1896
1897 l_file_name := l_emp_details.emp_num||'_'||l_abs_info.lts_st_dt||'_LTSR';
1898 IF p_type in ('W','R') THEN
1899 l_file_name := l_file_name||'_'||p_type;
1900 END IF;
1901 l_file_name := l_file_name||'.xml';
1902
1903 write_file(xXMLTable, l_file_name);
1904
1905 write_report(rXMLTable);
1906
1907 hr_utility.trace('X====gen_body_xml_main=====================================X');
1908 EXCEPTION
1909 WHEN others THEN
1910 hr_utility.trace('Exception in gen_body_xml_main SQL-ERRM :'||SQLERRM);
1911 END gen_body_xml_main;
1912
1913 /**************************************************************************/
1914
1915 PROCEDURE gen_body_xml
1916 IS
1917
1918 l_payroll_action_id NUMBER;
1919 l_asg_action_id NUMBER;
1920 l_org_struct_id NUMBER := NULL;
1921 l_person_id NUMBER := NULL;
1922 l_org_id NUMBER := NULL;
1923 l_bg_id NUMBER;
1924 l_start_date DATE := NULL;
1925 l_end_date DATE := NULL;
1926 l_type VARCHAR2(1) := NULL;
1927 /**************************************************************************/
1928 CURSOR cur_get_child_act_id (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
1929 SELECT assignment_action_id ass_act_id
1930 FROM pay_assignment_actions
1931 WHERE source_action_id = c_assignment_action_id;
1932 /**************************************************************************/
1933 CURSOR get_parent_asg_ids(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
1934 SELECT locked_action_id parent_id
1935 FROM pay_action_interlocks
1936 WHERE locking_action_id = c_assignment_action_id;
1937 /**************************************************************************/
1938 CURSOR get_pact_id(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
1939 SELECT ppa.payroll_action_id
1940 FROM pay_payroll_actions ppa
1941 ,pay_assignment_actions paa
1942 WHERE paa.payroll_action_id = ppa.payroll_action_id
1943 AND paa.assignment_action_id = c_assignment_action_id;
1944 /**************************************************************************/
1945
1946 BEGIN
1947 hr_utility.trace('+====gen_body_xml==========================================+');
1948
1949 l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
1950 hr_utility.trace('TRANSFER_PAYROLL_ACTION_ID = '||l_payroll_action_id);
1951
1952 l_asg_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
1953 hr_utility.trace('TRANSFER_ACT_ID = '||l_asg_action_id);
1954
1955 OPEN get_pact_id(l_asg_action_id);
1956 FETCH get_pact_id INTO l_payroll_action_id;
1957 CLOSE get_pact_id;
1958
1959 hr_utility.trace('l_payroll_action_id = '||l_payroll_action_id);
1960 get_all_parameters (
1961 p_payroll_action_id => l_payroll_action_id
1962 ,p_org_struct_id => l_org_struct_id
1963 ,p_person_id => l_person_id
1964 ,p_org_id => l_org_id
1965 ,p_bg_id => l_bg_id
1966 ,p_start_date => l_start_date
1967 ,p_end_date => l_end_date
1968 ,p_type => l_type);
1969
1970 IF l_type = 'I' THEN
1971 hr_utility.trace('Initial Report');
1972 FOR v_child_act_id IN cur_get_child_act_id(l_asg_action_id)
1973 LOOP
1974 hr_utility.trace('Child Assignment Action ID :'||v_child_act_id.ass_act_id);
1975 gen_body_xml_main(l_type, v_child_act_id.ass_act_id);
1976 END LOOP;
1977 ELSIF l_type IN ('W','R') THEN
1978 hr_utility.trace('Withdrawal/Regeneration Report');
1979 FOR v_parent_asg_ids in get_parent_asg_ids(l_asg_action_id)
1980 LOOP
1981 hr_utility.trace('Parent Assignment Action ID :'||v_parent_asg_ids.parent_id);
1982 gen_body_xml_main(l_type, v_parent_asg_ids.parent_id);
1983 END LOOP;
1984 END IF;
1985 hr_utility.trace('X====gen_body_xml==========================================X');
1986 EXCEPTION
1987 WHEN others THEN
1988 hr_utility.trace('Exception in gen_body_xml SQL-ERRM :'||SQLERRM);
1989 END gen_body_xml;
1990
1991 END PER_NL_LTSR_ARCHIVE;
1992