[Home] [Help]
Skip to content
PACKAGE BODY: APPS.PER_NL_DSRR_ARCHIVE
Source
1 PACKAGE BODY PER_NL_DSRR_ARCHIVE as
2 /* $Header: penldsrr.pkb 120.5.12020000.1 2012/07/17 08:52:51 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_DSRR |
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 = 'S'
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 = 'S'
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_dsr_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_DSRR |
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 AND pucif.EFFECTIVE_END_DATE
692 AND p_start_date BETWEEN purf.EFFECTIVE_START_DATE AND purf.EFFECTIVE_END_DATE;
693 /**************************************************************************/
694 CURSOR c_bg_stdate(p_payroll_action_id IN NUMBER) IS
695 SELECT business_group_id bg_id
696 ,start_date start_date
697 FROM pay_payroll_actions
698 WHERE payroll_action_id = p_payroll_action_id;
699 /**************************************************************************/
700 l_user_table_id NUMBER;
701 l_user_column_id NUMBER;
702 l_bg_id NUMBER;
703 l_start_date DATE;
704
705 BEGIN
706 hr_utility.trace('+====initialization_code===================================+');
707 IF Instr ('a', COMPOSE('a'|| UNISTR('\0301')) ) = 0 THEN
708 OPEN c_bg_stdate(p_action_context_id);
709 FETCH c_bg_stdate INTO l_bg_id,l_start_date;
710 CLOSE c_bg_stdate;
711
712 OPEN c_usr_tab_col;
713 FETCH c_usr_tab_col INTO l_user_table_id, l_user_column_id;
714 CLOSE c_usr_tab_col;
715
716 FOR I in c_src_dest_str(l_user_column_id, l_user_table_id, l_bg_id, l_start_date)
717 LOOP
718 g_SOURCE := g_SOURCE||upper(I.Source)||lower(I.Source);
719 g_TARGET := g_TARGET||upper(I.Target)||lower(I.Target);
720 END LOOP;
721
722 hr_utility.trace('g_SOURCE :'||g_SOURCE);
723 hr_utility.trace('g_TARGET :'||g_TARGET);
724 END IF;
725 hr_utility.trace('X====initialization_code===================================X');
726 EXCEPTION
727 WHEN OTHERS THEN
728 hr_utility.trace('Exception in init_code SQL-ERRM : '||SQLERRM);
729 END initialization_code;
730 /**************************************************************************/
731 /*--------------------------------------------------------------------
732 |Name : ASSIGNMENT_ACTION_CODE |
733 |Type : Procedure |
734 |Description: This procedure further filters which assignments are |
735 | eligible for reporting |
736 ----------------------------------------------------------------------*/
737
738
739 PROCEDURE assignment_action_code (p_payroll_action_id IN NUMBER
740 ,p_start_person_id IN NUMBER
741 ,p_end_person_id IN NUMBER
742 ,p_chunk IN NUMBER) IS
743
744 CURSOR csr_get_asg_person (p_person_id IN NUMBER
745 ,p_start_person_id IN NUMBER
746 ,p_end_person_id IN NUMBER
747 ,p_payroll_action_id IN NUMBER
748 ,p_start_date IN DATE
749 ,p_end_date IN DATE) IS
750 SELECT DISTINCT
751 paa.assignment_id assignment_id
752 , pab.absence_attendance_id abs_att_id
753 FROM per_absence_attendance_types paat
754 , per_absence_attendances pab
755 , pay_payroll_actions ppa
756 , per_all_assignments_f paa
757 , per_all_people_f pap
758 WHERE paat.absence_category = 'S'
759 AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
760 AND paat.business_group_id = pab.business_group_id
761 AND (pab.date_end + 1) BETWEEN p_start_date
762 AND p_end_date
763 AND pab.business_group_id = pap.business_group_id
764 AND pap.person_id = p_person_id
765 AND p_person_id BETWEEN p_start_person_id
766 AND p_end_person_id
767 AND pap.person_id = paa.person_id
768 AND pab.person_id = pap.person_id
769 AND paa.business_group_id = pap.business_group_id
770 AND paa.assignment_id = pab.abs_information5
771 AND pab.abs_information_category = 'NL_S'
772 AND pab.abs_information4 = 'S'
773 AND pap.business_group_id = ppa.business_group_id
774 AND ppa.payroll_action_id = p_payroll_action_id;
775 /**************************************************************************/
776
777 CURSOR csr_get_asg_org (p_org_id IN NUMBER
778 ,p_org_struct_id IN NUMBER
779 ,p_start_person_id IN NUMBER
780 ,p_end_person_id IN NUMBER
781 ,p_payroll_action_id IN NUMBER
782 ,p_start_date IN DATE
783 ,p_end_date IN DATE) IS
784 SELECT DISTINCT
785 paa.assignment_id assignment_id
786 , pab.absence_attendance_id abs_att_id
787 FROM per_absence_attendance_types paat
788 , per_absence_attendances pab
789 , pay_payroll_actions ppa
790 , per_org_structure_versions posv
791 , per_all_assignments_f paa
792 , per_all_people_f pap
793 WHERE posv.organization_structure_id = p_org_struct_id
794 AND posv.date_from <= p_end_date
795 AND nvl (posv.date_to, hr_general.end_of_time) >= p_start_date
796 AND (
797 paa.organization_id IN
798 (
799 (
800 SELECT pose.organization_id_child
801 FROM per_org_structure_elements pose
802 WHERE pose.org_structure_version_id = posv.org_structure_version_id
803 START WITH pose.organization_id_parent = p_org_id
804 CONNECT BY PRIOR organization_id_child = organization_id_parent
805 )
806 UNION
807 (
808 SELECT p_org_id
809 FROM dual
810 )
811 )
812 OR nvl (paa.establishment_id, - 1) = p_org_id
813 )
814 AND pap.person_id = paa.person_id
815 AND paa.person_id BETWEEN p_start_person_id
816 AND p_end_person_id
817 AND paa.business_group_id = pap.business_group_id
818 AND pab.person_id = pap.person_id
819 AND (pab.date_end + 1) BETWEEN p_start_date
820 AND p_end_date
821 AND paat.absence_category = 'S'
822 AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
823 AND paat.business_group_id = pab.business_group_id
824 AND pab.business_group_id = pap.business_group_id
825 AND paa.assignment_id = pab.abs_information5
826 AND pab.abs_information_category = 'NL_S'
827 AND pab.abs_information4 = 'S'
828 AND pap.business_group_id = ppa.business_group_id
829 AND ppa.payroll_action_id = p_payroll_action_id;
830 /**************************************************************************/
831
832 CURSOR csr_get_asg_hier (p_org_struct_id IN NUMBER
833 ,p_start_person_id IN NUMBER
834 ,p_end_person_id IN NUMBER
835 ,p_payroll_action_id IN NUMBER
836 ,p_start_date IN DATE
837 ,p_end_date IN DATE) IS
838
839 SELECT DISTINCT
840 paa.assignment_id assignment_id
841 , pab.absence_attendance_id abs_att_id
842 FROM per_absence_attendance_types paat
843 , per_absence_attendances pab
844 , per_all_assignments_f paa
845 , per_all_people_f pap
846 , pay_payroll_actions ppa
847 , per_org_structure_versions posv
848 WHERE posv.organization_structure_id = p_org_struct_id
849 AND pap.person_id BETWEEN p_start_person_id
850 AND p_end_person_id
851 AND pap.business_group_id = ppa.business_group_id
852 AND ppa.payroll_action_id = p_payroll_action_id
853 AND paat.absence_category = 'S'
854 AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
855 AND paat.business_group_id = pab.business_group_id
856 AND (pab.date_end + 1) BETWEEN p_start_date
857 AND p_end_date
858 AND paa.assignment_id = pab.abs_information5
859 AND pab.abs_information_category = 'NL_S'
860 AND pab.abs_information4 = 'S'
861 AND pab.business_group_id = pap.business_group_id
862 AND pab.person_id = pap.person_id
863 AND pap.person_id = paa.person_id
864 AND (
865 hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
866 ,paa.organization_id) IS NOT NULL
867 OR per_nl_dsrr_archive.org_check (pap.business_group_id
868 ,NULL
869 ,nvl (paa.establishment_id, - 1)
870 ,p_start_date
871 ,p_end_date) = 1
872 )
873 AND paa.organization_id IN
874 (
875 SELECT pose.organization_id_parent
876 FROM per_org_structure_elements pose
877 WHERE posv.org_structure_version_id = pose.org_structure_version_id
878 UNION
879 SELECT pose.organization_id_child
880 FROM per_org_structure_elements pose
881 WHERE posv.org_structure_version_id = pose.org_structure_version_id
882 );
883 /**************************************************************************/
884 --Withdrawal and Regeneration
885 CURSOR csr_get_asg_person_archive (p_person_id IN NUMBER
886 ,p_start_person_id IN NUMBER
887 ,p_end_person_id IN NUMBER
888 ,p_payroll_action_id IN NUMBER
889 ,p_start_date IN DATE
890 ,p_end_date IN DATE
891 ,p_type IN VARCHAR2) IS
892 SELECT DISTINCT
893 paa.assignment_id assignment_id
894 FROM pay_action_information pai
895 , pay_payroll_actions ppa
896 , per_all_assignments_f paa
897 , per_all_people_f pap
898 WHERE pai.action_context_type = 'AAP'
899 AND pai.action_information_category = 'NL_DSRR_ABS_INFO'
900 AND pai.action_information4 = 'SRR'
901 AND pai.action_information29 = 'NL_SRR'
902 AND (
903 (
904 pai.action_information30 = 'N'
905 AND p_type = 'W'
906 )
907 OR (
908 p_type = 'R'
909 )
910 )
911 AND (
912 p_start_date <= fnd_date.canonical_to_date (pai.action_information2)
913 AND p_end_date >= fnd_date.canonical_to_date (pai.action_information3)
914 )
915 AND paa.assignment_id = pai.assignment_id
916 AND pap.person_id = p_person_id
917 AND p_person_id BETWEEN p_start_person_id
918 AND p_end_person_id
919 AND pap.person_id = paa.person_id
920 AND paa.business_group_id = pap.business_group_id
921 AND pap.business_group_id = ppa.business_group_id
922 AND ppa.payroll_action_id = p_payroll_action_id;
923
924 /**************************************************************************/
925 CURSOR csr_get_asg_org_archive (p_org_id IN NUMBER
926 ,p_org_struct_id IN NUMBER
927 ,p_start_person_id IN NUMBER
928 ,p_end_person_id IN NUMBER
929 ,p_payroll_action_id IN NUMBER
930 ,p_start_date IN DATE
931 ,p_end_date IN DATE
932 ,p_type IN VARCHAR2) IS
933 SELECT DISTINCT
934 paa.assignment_id assignment_id
935 FROM pay_action_information pai
936 , pay_payroll_actions ppa
937 , per_org_structure_versions posv
938 , per_all_assignments_f paa
939 , per_all_people_f pap
940 WHERE posv.organization_structure_id = p_org_struct_id
941 AND posv.date_from <= p_end_date
942 AND nvl (posv.date_to
943 ,hr_general.end_of_time) >= p_start_date
944 AND (
945 paa.organization_id IN
946 (
947 (
948 SELECT pose.organization_id_child
949 FROM per_org_structure_elements pose
950 WHERE pose.org_structure_version_id = posv.org_structure_version_id
951 START WITH pose.organization_id_parent = p_org_id
952 CONNECT BY PRIOR organization_id_child = organization_id_parent
953 )
954 UNION
955 (
956 SELECT p_org_id
957 FROM dual
958 )
959 )
960 OR nvl (paa.establishment_id, - 1) = p_org_id
961 )
962 AND pap.person_id = paa.person_id
963 AND paa.person_id BETWEEN p_start_person_id
964 AND p_end_person_id
965 AND paa.business_group_id = pap.business_group_id
966 AND pai.action_context_type = 'AAP'
967 AND pai.action_information_category = 'NL_DSRR_ABS_INFO'
968 AND pai.action_information4 = 'SRR'
969 AND pai.action_information29 = 'NL_SRR'
970 AND (
971 (
972 pai.action_information30 = 'N'
973 AND p_type = 'W'
974 )
975 OR (
976 p_type = 'R'
977 )
978 )
979 AND (
980 p_start_date <= fnd_date.canonical_to_date (pai.action_information2)
981 AND p_end_date >= fnd_date.canonical_to_date (pai.action_information3)
982 )
983 AND paa.assignment_id = pai.assignment_id
984 AND pap.business_group_id = ppa.business_group_id
985 AND ppa.payroll_action_id = p_payroll_action_id;
986 /**************************************************************************/
987 CURSOR csr_get_asg_hier_archive (p_org_struct_id IN NUMBER
988 ,p_start_person_id IN NUMBER
989 ,p_end_person_id IN NUMBER
990 ,p_payroll_action_id IN NUMBER
991 ,p_start_date IN DATE
992 ,p_end_date IN DATE
993 ,p_type IN VARCHAR2) IS
994 SELECT DISTINCT
995 paa.assignment_id assignment_id
996 FROM pay_action_information pai
997 , per_all_assignments_f paa
998 , per_all_people_f pap
999 , pay_payroll_actions ppa
1000 , per_org_structure_versions posv
1001 WHERE posv.organization_structure_id = p_org_struct_id
1002 AND pap.person_id BETWEEN p_start_person_id
1003 AND p_end_person_id
1004 AND pap.business_group_id = ppa.business_group_id
1005 AND ppa.payroll_action_id = p_payroll_action_id
1006 AND pai.action_context_type = 'AAP'
1007 AND pai.action_information_category = 'NL_DSRR_ABS_INFO'
1008 AND pai.action_information4 = 'SRR'
1009 AND pai.action_information29 = 'NL_SRR'
1010 AND (
1011 (
1012 pai.action_information30 = 'N'
1013 AND p_type = 'W'
1014 )
1015 OR (
1016 p_type = 'R'
1017 )
1018 )
1019 AND (
1020 p_start_date <= fnd_date.canonical_to_date (pai.action_information2)
1021 AND p_end_date >= fnd_date.canonical_to_date (pai.action_information3)
1022 )
1023 AND paa.assignment_id = pai.assignment_id
1024 AND pap.person_id = paa.person_id
1025 AND (
1026 hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
1027 ,paa.organization_id) IS NOT NULL
1028 OR per_nl_dsr_archive.org_check (pap.business_group_id
1029 ,NULL
1030 ,nvl (paa.establishment_id,- 1)
1031 ,p_start_date
1032 ,p_end_date) = 1
1033 )
1034 AND paa.organization_id IN
1035 (
1036 SELECT pose.organization_id_parent
1037 FROM per_org_structure_elements pose
1038 WHERE posv.org_structure_version_id = pose.org_structure_version_id
1039 UNION
1040 SELECT pose.organization_id_child
1041 FROM per_org_structure_elements pose
1042 WHERE posv.org_structure_version_id = pose.org_structure_version_id
1043 );
1044 /**************************************************************************/
1045
1046 l_org_struct_id NUMBER := NULL;
1047 l_person_id NUMBER := NULL;
1048 l_org_id NUMBER := NULL;
1049 l_bg_id NUMBER;
1050 l_start_date DATE;
1051 l_end_date DATE;
1052 l_type VARCHAR2(1) := NULL;
1053
1054 /**************************************************************************/
1055 PROCEDURE ins_assg_act (p_assignid IN NUMBER
1056 ,p_pactid IN NUMBER
1057 ,p_chunk IN NUMBER)
1058 IS
1059 l_asg_act_id NUMBER;
1060 BEGIN
1061 SELECT pay_assignment_actions_s.NEXTVAL INTO l_asg_act_id FROM dual;
1062 hr_nonrun_asact.insact(
1063 lockingactid => l_asg_act_id
1064 ,assignid => p_assignid
1065 ,pactid => p_pactid
1066 ,chunk => p_chunk);
1067 EXCEPTION
1068 WHEN others THEN
1069 hr_utility.trace('Exception in ins_assg_act SQL-ERRM : '||SQLERRM);
1070 END ins_assg_act;
1071 /**************************************************************************/
1072
1073 BEGIN
1074
1075 hr_utility.trace('+====assignment_action_code================================+');
1076 hr_utility.trace('Payroll_action_id/Chunk: '||p_payroll_action_id||' / '||p_chunk );
1077 hr_utility.trace('Start/End Person ID : '||p_start_person_id||' / '||p_end_person_id );
1078
1079 get_all_parameters (
1080 p_payroll_action_id => p_payroll_action_id
1081 ,p_org_struct_id => l_org_struct_id
1082 ,p_person_id => l_person_id
1083 ,p_org_id => l_org_id
1084 ,p_bg_id => l_bg_id
1085 ,p_start_date => l_start_date
1086 ,p_end_date => l_end_date
1087 ,p_type => l_type);
1088
1089 IF l_person_id is not NULL THEN
1090 hr_utility.trace(l_type||' Person selected ' ||l_person_id||' '||p_start_person_id||' '||p_end_person_id);
1091 IF l_type = 'I' THEN
1092 FOR v_csr_get_asg_person IN
1093 csr_get_asg_person(l_person_id, p_start_person_id, p_end_person_id
1094 , p_payroll_action_id, l_start_date, l_end_date)
1095 LOOP
1096 ins_assg_act(v_csr_get_asg_person.assignment_id,p_payroll_action_id,p_chunk);
1097 END LOOP;
1098 ELSIF l_type IN ('W','R') THEN
1099 FOR v_csr_get_asg_person IN
1100 csr_get_asg_person_archive(l_person_id, p_start_person_id, p_end_person_id
1101 , p_payroll_action_id, l_start_date, l_end_date,l_type)
1102 LOOP
1103 ins_assg_act(v_csr_get_asg_person.assignment_id,p_payroll_action_id,p_chunk);
1104 END LOOP;
1105 END IF;
1106 -----------------------------------------------
1107 ELSIF l_org_id is not NULL THEN
1108 hr_utility.trace(l_type||' Org selected ' ||l_org_id);
1109 IF l_type = 'I' THEN
1110 FOR v_csr_get_asg_org IN
1111 csr_get_asg_org(l_org_id, l_org_struct_id
1112 , p_start_person_id, p_end_person_id, p_payroll_action_id
1113 , l_start_date, l_end_date)
1114 LOOP
1115 ins_assg_act(v_csr_get_asg_org.assignment_id,p_payroll_action_id,p_chunk);
1116 END LOOP;
1117 ELSIF l_type IN ('W','R') THEN
1118 FOR v_csr_get_asg_org IN
1119 csr_get_asg_org_archive(l_org_id, l_org_struct_id
1120 , p_start_person_id, p_end_person_id, p_payroll_action_id
1121 , l_start_date, l_end_date,l_type)
1122 LOOP
1123 ins_assg_act(v_csr_get_asg_org.assignment_id,p_payroll_action_id,p_chunk);
1124 END LOOP;
1125 END IF;
1126 -----------------------------------------------
1127 ELSIF l_org_struct_id is not NULL THEN
1128 hr_utility.trace(l_type||' Hierarchy selected ' ||l_org_struct_id);
1129 IF l_type = 'I' THEN
1130 FOR v_csr_get_asg_hier IN
1131 csr_get_asg_hier(l_org_struct_id, p_start_person_id, p_end_person_id
1132 , p_payroll_action_id, l_start_date, l_end_date)
1133 LOOP
1134 ins_assg_act(v_csr_get_asg_hier.assignment_id,p_payroll_action_id,p_chunk);
1135 END LOOP;
1136 ELSIF l_type IN ('W','R') THEN
1137 FOR v_csr_get_asg_hier IN
1138 csr_get_asg_hier_archive(l_org_struct_id, p_start_person_id, p_end_person_id
1139 , p_payroll_action_id, l_start_date, l_end_date,l_type)
1140 LOOP
1141 ins_assg_act(v_csr_get_asg_hier.assignment_id,p_payroll_action_id,p_chunk);
1142 END LOOP;
1143 END IF;
1144 END IF;
1145
1146
1147 hr_utility.trace('X====assignment_action_code================================X');
1148 EXCEPTION
1149 WHEN others THEN
1150 hr_utility.trace('Exception in assignment_action_code SQL-ERRM : '||SQLERRM);
1151 END assignment_action_code;
1152 /**************************************************************************/
1153
1154 /*-------------------------------------------------------------------------------
1155 |Name : archive_code |
1156 |Type : Procedure |
1157 |Description : Archival code |
1158 -------------------------------------------------------------------------------*/
1159
1160
1161 PROCEDURE archive_code (p_assignment_action_id IN NUMBER
1162 ,p_effective_date IN DATE) IS
1163
1164
1165 CURSOR csr_get_org_info(p_assignment_id IN NUMBER
1166 ,p_org_struct_id IN NUMBER
1167 ,p_abs_start_date IN DATE) IS
1168 SELECT hou.organization_id org_id
1169 ,hou.name org_name
1170 ,hoi.org_information4 tax_reg
1171 FROM per_all_assignments_f pas
1172 ,hr_organization_units hou
1173 ,hr_organization_information hoi
1174 ,per_org_structure_versions posv
1175 WHERE posv.organization_structure_id = p_org_struct_id
1176 AND p_abs_start_date BETWEEN posv.date_from
1177 AND nvl (posv.date_to
1178 ,hr_general.end_of_time)
1179 AND pas.assignment_id = p_assignment_id
1180 AND hou.organization_id = hr_nl_org_info.get_tax_org_id (posv.org_structure_version_id
1181 ,pas.organization_id)
1182 AND hoi.organization_id = hou.organization_id
1183 AND hoi.org_information_context = 'NL_ORG_INFORMATION';
1184 /**************************************************************************/
1185 CURSOR csr_get_leg_info(p_assignment_id IN NUMBER) IS
1186 SELECT hou.organization_id org_id
1187 ,hou.name org_name
1188 ,hoi.org_information1 tax_reg
1189 FROM per_all_assignments_f pas
1190 ,hr_organization_units hou
1191 ,hr_organization_information hoi
1192 WHERE pas.assignment_id = p_assignment_id
1193 AND hou.organization_id = pas.establishment_id
1194 AND hoi.organization_id = hou.organization_id
1195 AND hoi.org_information_context = 'NL_LE_TAX_DETAILS';
1196 /**************************************************************************/
1197
1198 CURSOR csr_get_abs_details (p_assignment_action_id IN NUMBER
1199 ,p_start_date IN DATE
1200 ,p_end_date IN DATE) IS
1201 SELECT paa.assignment_id assignment_id
1202 ,pas.person_id person_id
1203 ,pab.absence_attendance_id abs_att_id
1204 ,pab.date_end end_date
1205 ,nvl (fnd_date.canonical_to_date (pab.abs_information9)
1206 ,pab.date_start) start_date
1207 ,paat.absence_category abs_cat
1208 ,pab.abs_information_category info_cat
1209 ,pab.abs_information4 report_type
1210 ,pab.abs_information5 ass_id
1211 ,pab.abs_information6 sick_ref
1212 ,to_char (pab.date_notification
1213 ,'YYYYMMDD') noti_date
1214 ,to_char (nvl (fnd_date.canonical_to_date (pab.abs_information9)
1215 ,pab.date_start)
1216 ,'YYYYMMDD') st_dt
1217 ,to_char ((pab.date_end + 1)
1218 ,'YYYYMMDD') full_rec_dt
1219 ,to_char (fnd_date.canonical_to_date (pab.abs_information18)
1220 ,'YYYYMMDD') partial_rec_dt
1221 ,pab.abs_information13 daily_si
1222 FROM per_absence_attendance_types paat
1223 ,per_absence_attendances pab
1224 ,pay_assignment_actions paa
1225 ,per_all_assignments_f pas
1226 WHERE paat.absence_category = 'S'
1227 AND paat.absence_attendance_type_id = pab.absence_attendance_type_id
1228 AND paat.business_group_id = pab.business_group_id
1229 AND (pab.date_end + 1) BETWEEN p_start_date
1230 AND p_end_date
1231 AND (pab.date_end + 1) BETWEEN pas.effective_start_date
1232 AND pas.effective_end_date
1233 AND pab.business_group_id = pas.business_group_id
1234 AND paa.assignment_action_id = p_assignment_action_id
1235 AND pas.assignment_id = paa.assignment_id
1236 AND pab.abs_information5 = pas.assignment_id
1237 AND pab.person_id = pas.person_id
1238 AND pab.abs_information_category = 'NL_S'
1239 AND pab.abs_information4 = 'S';
1240
1241 /**************************************************************************/
1242
1243 CURSOR csr_chk_SR_exists (p_assignment_id IN NUMBER
1244 ,p_abs_start_date IN DATE) IS
1245 SELECT 'Y'
1246 FROM pay_action_information pai
1247 WHERE pai.assignment_id = p_assignment_id
1248 AND pai.action_information_category = 'NL_DSR_ABS_INFO'
1249 AND pai.action_information30 = 'N'
1250 AND fnd_date.canonical_to_date (pai.action_information2) = p_abs_start_date;
1251 /**************************************************************************/
1252
1253 /* sender and contact data */
1254 CURSOR csr_get_org_contact_info(p_assignment_id IN NUMBER
1255 , p_start_date IN DATE) IS
1256 SELECT decode (hoi.org_information1,'Y','1','N','2') test_msg
1257 ,hoi.org_information2 sector
1258 ,lpad(hoi.org_information3, 2, 0) risk_group
1259 ,hoi.org_information4 role_sender
1260 ,hoi.org_information5 tax_no_sender
1261 ,hoi.org_information6 contact_per_or_dept
1262 ,hoi.org_information7 est_name
1263 ,hoi.org_information8 gender_contact
1264 ,hoi.org_information9 ph_no_contact
1265 FROM per_all_assignments_f pas
1266 ,hr_organization_units hou
1267 ,hr_organization_information hoi
1268 WHERE pas.assignment_id = p_assignment_id
1269 AND p_start_date BETWEEN pas.effective_start_date
1270 AND pas.effective_end_date
1271 AND hou.organization_id = nvl (pas.establishment_id
1272 ,pas.organization_id)
1273 AND hoi.organization_id = hou.organization_id
1274 AND hoi.org_information_context = 'NL_ORG_DSR_INFO';
1275 /**************************************************************************/
1276
1277 /* employee data */
1278 CURSOR csr_get_person_info(p_assignment_id IN NUMBER
1279 , p_start_date IN DATE
1280 , p_end_date IN DATE) IS
1281 SELECT pap.national_identifier sofi_number
1282 ,to_char (pap.date_of_birth
1283 ,'YYYYMMDD') date_of_birth
1284 ,decode (pap.sex,'M','1','F','2',NULL,'9') gender
1285 ,REPLACE(REPLACE(pap.per_information1, '.', ''), ' ', '') init
1286 ,pap.pre_name_adjunct prefix
1287 ,pap.last_name last_name
1288 ,pap.employee_number employee_number
1289 ,pap.person_id person_id
1290 ,pap.business_group_id bg_id
1291 ,pas.establishment_id establishment_id
1292 ,pas.assignment_sequence asg_seq
1293 FROM per_all_people_f pap
1294 ,per_all_assignments_f pas
1295 WHERE pas.assignment_id = p_assignment_id
1296 AND p_start_date BETWEEN pas.effective_start_date
1297 AND pas.effective_end_date
1298 AND pap.person_id = pas.person_id
1299 AND p_start_date BETWEEN pap.effective_start_date
1300 AND pap.effective_end_date;
1301 /**************************************************************************/
1302
1303 /*NL_DS_SICK_INFO - Contact Details*/
1304 CURSOR csr_get_sickness_contact (p_assignment_id IN NUMBER
1305 , p_start_date IN DATE) IS
1306 SELECT pei_information21 contact_name
1307 ,pei_information22 est_name
1308 ,pei_information23 gender
1309 ,pei_information24 contact_ph_no
1310 FROM per_all_assignments_f pas
1311 ,per_people_extra_info pei
1312 WHERE pas.assignment_id = p_assignment_id
1313 AND p_start_date BETWEEN pas.effective_start_date
1314 AND pas.effective_end_date
1315 AND pei.person_id = pas.person_id
1316 AND pei.information_type = 'NL_DS_SICK_INFO';
1317
1318 /**************************************************************************/
1319
1320 CURSOR csr_already_exists(p_abs_att_id NUMBER) IS
1321
1322 SELECT 'Y'
1323 FROM pay_action_information pai
1324 WHERE pai.action_context_type = 'AAP'
1325 AND pai.action_information_category = 'NL_DSRR_ABS_INFO'
1326 AND pai.action_information1 = p_abs_att_id
1327 AND pai.action_information30 = 'N';
1328 /**************************************************************************/
1329
1330 CURSOR get_archived_absence(p_assignment_id IN NUMBER
1331 , p_start_date IN DATE
1332 , p_end_date IN DATE
1333 , p_type IN VARCHAR2) IS
1334
1335 SELECT pai.action_information1 abs_att_id
1336 , pai.action_information2 st_dt
1337 , pai.action_information3 end_dt
1338 , pai.action_context_id org_asg_act_id
1339 , pai.action_information_id act_info_id
1340 FROM pay_action_information pai
1341 WHERE pai.assignment_id = p_assignment_id
1342 AND pai.action_context_type = 'AAP'
1343 AND pai.action_information_category = 'NL_DSRR_ABS_INFO'
1344 AND pai.action_information29 = 'NL_SRR'
1345 AND (
1346 (
1347 pai.action_information30 = 'N'
1348 AND p_type = 'W'
1349 )
1350 OR (
1351 p_type = 'R'
1352 )
1353 )
1354 AND (
1355 p_start_date <= fnd_date.canonical_to_date (pai.action_information2)
1356 AND p_end_date >= fnd_date.canonical_to_date (pai.action_information3)
1357 ); --abraghun - to check this date condition.
1358 /**************************************************************************/
1359 CURSOR c_get_pact_chunk(c_assignment_action_id IN NUMBER ) IS
1360 SELECT ppa.payroll_action_id
1361 ,paa.chunk_number
1362 FROM pay_payroll_actions ppa
1363 ,pay_assignment_actions paa
1364 WHERE paa.payroll_action_id = ppa.payroll_action_id
1365 AND paa.assignment_action_id = c_assignment_action_id;
1366 /**************************************************************************/
1367 CURSOR c_get_assid_chunk(c_assignment_action_id IN NUMBER ) IS
1368 SELECT paa.assignment_id
1369 , paa.chunk_number
1370 FROM pay_assignment_actions paa
1371 , pay_payroll_actions ppa
1372 WHERE paa.payroll_action_id = ppa.payroll_action_id
1373 AND paa.assignment_action_id = c_assignment_action_id;
1374 /**************************************************************************/
1375 CURSOR csr_numiv_override(p_assignment_id IN NUMBER) IS
1376 SELECT paei.aei_information1 numiv_override
1377 FROM per_assignment_extra_info paei
1378 WHERE paei.assignment_id = p_assignment_id
1379 AND paei.aei_information_category = 'NL_NUMIV_OVERRIDE';
1380 /**************************************************************************/
1381
1382 l_org_struct_id NUMBER := NULL;
1383 l_person_id NUMBER := NULL;
1384 l_org_id NUMBER := NULL;
1385 l_bg_id NUMBER;
1386 l_child_aa_id NUMBER;
1387 l_start_date DATE := NULL;
1388 l_end_date DATE := NULL;
1389 l_type VARCHAR2(1) := NULL;
1390 l_abs_attendence_id NUMBER;
1391 l_payroll_action_id NUMBER;
1392 l_est_id NUMBER := NULL;
1393 l_tax_reg_num VARCHAR2(100);
1394 l_org_name VARCHAR2(150);
1395 l_abs_start_date DATE := NULL;
1396 l_abs_end_date DATE := NULL;
1397 l_assignment_id NUMBER := NULL;
1398 l_action_info_id NUMBER;
1399 l_ovn NUMBER;
1400 l_exists VARCHAR2(1);
1401 l_SR_exists VARCHAR2(1);
1402 l_chunk_number NUMBER;
1403
1404 l_sickness_contact csr_get_sickness_contact%ROWTYPE;
1405 l_person_info csr_get_person_info%ROWTYPE;
1406 l_org_contact_info csr_get_org_contact_info%ROWTYPE;
1407 l_numiv_override per_assignment_extra_info.aei_information1%TYPE;
1408
1409
1410
1411
1412 /**************************************************************************/
1413 BEGIN
1414 hr_utility.trace('+====archive_code==========================================+');
1415 hr_utility.trace('p_assignment_action_id : '||p_assignment_action_id);
1416 hr_utility.trace('p_effective_date : '||p_effective_date);
1417
1418 OPEN c_get_pact_chunk (p_assignment_action_id);
1419 FETCH c_get_pact_chunk INTO l_payroll_action_id, l_chunk_number;
1420 CLOSE c_get_pact_chunk;
1421
1422 hr_utility.trace('l_payroll_action_id : '||l_payroll_action_id);
1423 hr_utility.trace('l_chunk_number : '||l_chunk_number);
1424
1425 get_all_parameters (
1426 p_payroll_action_id => l_payroll_action_id
1427 ,p_org_struct_id => l_org_struct_id
1428 ,p_person_id => l_person_id
1429 ,p_org_id => l_org_id
1430 ,p_bg_id => l_bg_id
1431 ,p_start_date => l_start_date
1432 ,p_end_date => l_end_date
1433 ,p_type => l_type);
1434
1435 IF l_type = 'I' THEN
1436 FOR v_csr_get_abs_details IN csr_get_abs_details(p_assignment_action_id, l_start_date, l_end_date)
1437 LOOP
1438 l_exists := 'N';
1439 l_SR_exists := 'N';
1440 l_assignment_id := v_csr_get_abs_details.assignment_id;
1441 l_abs_attendence_id := v_csr_get_abs_details.abs_att_id;
1442 l_abs_start_date := v_csr_get_abs_details.start_date;
1443 l_abs_end_date := v_csr_get_abs_details.end_date;
1444 hr_utility.trace('Assignment/Attendence : ' || l_assignment_id||'/'||l_abs_attendence_id);
1445 hr_utility.trace('Absence Start/End Date : ' || to_char(l_abs_start_date,'DDMonYYYY') || ' / ' ||
1446 to_char(l_abs_end_date,'DDMonYYYY'));
1447 OPEN csr_already_exists (l_abs_attendence_id);
1448 FETCH csr_already_exists INTO l_exists;
1449 CLOSE csr_already_exists;
1450
1451 OPEN csr_chk_SR_exists(l_assignment_id,l_abs_start_date);
1452 FETCH csr_chk_SR_exists INTO l_SR_exists;
1453 CLOSE csr_chk_SR_exists;
1454
1455 -- hr_utility.trace('SRR exists: '||l_exists||' SR exists : '||l_SR_exists);
1456 IF l_exists = 'Y' THEN
1457 fnd_file.put_line(fnd_file.log,'Sickness Recovery Report already sent. Hence skipped.');
1458 ELSIF l_exists = 'N' THEN
1459 IF l_SR_exists = 'N' THEN
1460 fnd_file.put_line(fnd_file.log,'Sickness Report not sent. Hence skipped.');
1461 ELSIF l_SR_exists = 'Y' THEN
1462
1463 SELECT pay_assignment_actions_s.NEXTVAL INTO l_child_aa_id FROM dual;
1464 hr_nonrun_asact.insact(
1465 lockingactid => l_child_aa_id
1466 , assignid => l_assignment_id
1467 , pactid => l_payroll_action_id
1468 , chunk => l_chunk_number
1469 , status => 'C'
1470 , source_act => p_assignment_action_id);
1471
1472 OPEN csr_get_sickness_contact(l_assignment_id, l_abs_start_date );
1473 FETCH csr_get_sickness_contact INTO l_sickness_contact;
1474 CLOSE csr_get_sickness_contact;
1475
1476 OPEN csr_get_person_info(l_assignment_id, l_abs_start_date, l_abs_end_date);
1477 FETCH csr_get_person_info into l_person_info;
1478 CLOSE csr_get_person_info;
1479
1480 OPEN csr_get_org_contact_info(l_assignment_id, l_abs_start_date );
1481 FETCH csr_get_org_contact_info into l_org_contact_info;
1482 CLOSE csr_get_org_contact_info;
1483
1484 /* Archiving absence details */
1485 pay_action_information_api.create_action_information
1486 (
1487 p_action_information_id => l_action_info_id
1488 , p_action_context_id => l_child_aa_id
1489 , p_action_context_type => 'AAP'
1490 , p_object_version_number => l_ovn
1491 , p_assignment_id => l_assignment_id
1492 , p_effective_date => p_effective_date
1493 , p_source_id => NULL
1494 , p_source_text => NULL
1495 , p_action_information_category => 'NL_DSRR_ABS_INFO'
1496 , p_action_information1 => l_abs_attendence_id
1497 , p_action_information2 => fnd_date.date_to_canonical(l_abs_end_date + 1)
1498 , p_action_information3 => fnd_date.date_to_canonical(l_abs_start_date)
1499 , p_action_information4 => 'SRR'
1500 , p_action_information5 => v_csr_get_abs_details.person_id
1501 , p_action_information6 => v_csr_get_abs_details.ass_id
1502 , p_action_information7 => v_csr_get_abs_details.sick_ref
1503 , p_action_information8 => v_csr_get_abs_details.noti_date
1504 , p_action_information9 => v_csr_get_abs_details.st_dt
1505 , p_action_information10 => v_csr_get_abs_details.full_rec_dt
1506 , p_action_information11 => v_csr_get_abs_details.partial_rec_dt
1507 , p_action_information12 => v_csr_get_abs_details.daily_si
1508 , p_action_information13 => l_sickness_contact.contact_name
1509 , p_action_information14 => l_sickness_contact.est_name
1510 , p_action_information15 => l_sickness_contact.gender
1511 , p_action_information16 => l_sickness_contact.contact_ph_no
1512 , p_action_information29 => 'NL_SRR'
1513 , p_action_information30 => 'N'
1514 );
1515
1516 l_est_id := l_person_info.establishment_id;
1517
1518 IF l_est_id IS NOT NULL
1519 AND per_nl_dsrr_archive.org_check(l_bg_id
1520 ,NULL
1521 ,l_est_id
1522 ,l_abs_start_date
1523 ,l_abs_end_date) = 1 THEN
1524 OPEN csr_get_leg_info(l_assignment_id);
1525 FETCH csr_get_leg_info INTO l_org_id, l_org_name, l_tax_reg_num;
1526 CLOSE csr_get_leg_info;
1527 ELSE
1528 OPEN csr_get_org_info(l_assignment_id, l_org_struct_id, l_abs_start_date);
1529 FETCH csr_get_org_info INTO l_org_id, l_org_name, l_tax_reg_num;
1530 CLOSE csr_get_org_info;
1531 END IF;
1532
1533 OPEN csr_numiv_override(l_assignment_id);
1534 FETCH csr_numiv_override INTO l_numiv_override;
1535 CLOSE csr_numiv_override;
1536
1537 /* Archiving Employee and employer info */
1538 pay_action_information_api.create_action_information
1539 ( p_action_information_id => l_action_info_id
1540 , p_action_context_id => l_child_aa_id
1541 , p_action_context_type => 'AAP'
1542 , p_object_version_number => l_ovn
1543 , p_assignment_id => l_assignment_id
1544 , p_effective_date => p_effective_date
1545 , p_source_id => NULL
1546 , p_source_text => NULL
1547 , p_action_information_category => 'NL_DSRR_EMP_INFO'
1548 , p_action_information1 => l_abs_attendence_id
1549 , p_action_information2 => l_org_contact_info.test_msg
1550 , p_action_information3 => l_org_contact_info.tax_no_sender
1551 , p_action_information4 => l_org_contact_info.role_sender
1552 , p_action_information5 => l_org_contact_info.contact_per_or_dept
1553 , p_action_information6 => l_org_contact_info.ph_no_contact
1554 , p_action_information7 => l_org_contact_info.risk_group
1555 , p_action_information8 => l_org_contact_info.sector
1556 , p_action_information9 => l_tax_reg_num
1557 , p_action_information11 => l_person_info.sofi_number
1558 , p_action_information12 => l_person_info.date_of_birth
1559 , p_action_information13 => l_person_info.gender
1560 , p_action_information14 => l_person_info.init
1561 , p_action_information15 => l_person_info.prefix
1562 , p_action_information16 => l_person_info.last_name
1563 , p_action_information21 => nvl(l_numiv_override,l_person_info.asg_seq)
1564 , p_action_information22 => l_person_info.employee_number
1565 , p_action_information23 => l_org_contact_info.est_name
1566 , p_action_information24 => l_org_contact_info.gender_contact
1567 );
1568 END IF;
1569 END IF;
1570 END LOOP;
1571
1572 ELSIF l_type IN ('W','R') THEN
1573 hr_utility.trace(l_type||' Assignment Action ID : '||p_assignment_action_id );
1574 OPEN c_get_assid_chunk (p_assignment_action_id);
1575 FETCH c_get_assid_chunk INTO l_assignment_id, l_chunk_number;
1576 CLOSE c_get_assid_chunk;
1577 hr_utility.trace('Assignment ID/Chunk : '||l_assignment_id||' / '||l_chunk_number);
1578
1579 FOR r_get_archived_absence IN get_archived_absence(l_assignment_id, l_start_date, l_end_date, l_type)
1580 LOOP
1581
1582 hr_utility.trace('Absence Attendence ID : ' || r_get_archived_absence.abs_att_id);
1583
1584 hr_utility.trace('Absence Start/End Date : ' ||
1585 to_char(fnd_date.canonical_to_date(r_get_archived_absence.st_dt),'DDMonYYYY') || ' / ' ||
1586 to_char(fnd_date.canonical_to_date(r_get_archived_absence.end_dt),'DDMonYYYY'));
1587 hr_utility.trace('Org Asg Action ID : ' || r_get_archived_absence.org_asg_act_id);
1588
1589 hr_nonrun_asact.insint(lockingactid => p_assignment_action_id
1590 , lockedactid => r_get_archived_absence.org_asg_act_id);
1591 IF l_type = 'W' THEN
1592 -- Update previous archive incase of withdrawal.
1593 pay_action_information_api.update_action_information
1594 (p_action_information_id => r_get_archived_absence.act_info_id
1595 , p_object_version_number => l_ovn
1596 , p_action_information30 => 'Y'
1597 );
1598 END IF;
1599 END LOOP;
1600 END IF;
1601
1602 hr_utility.trace('X====archive_code==========================================X');
1603 EXCEPTION
1604 WHEN TOO_MANY_ROWS THEN
1605 hr_utility.trace('Too Many Rows Exception in archive_code');
1606
1607 WHEN NO_DATA_FOUND THEN
1608 hr_utility.trace('No Data Found Exception in archive_code');
1609
1610 WHEN others THEN
1611 hr_utility.trace('Exception in archive_code SQL-ERRM : '||SQLERRM);
1612
1613 END archive_code;
1614 /**************************************************************************/
1615
1616
1617 /*-------------------------------------------------------------------------------
1618 |Name : DEINITIALIZATION_CODE |
1619 |Type : Procedure |
1620 |Description : Deinitialization code |
1621 -------------------------------------------------------------------------------*/
1622
1623
1624 PROCEDURE deinitialization_code(p_actid IN NUMBER) IS
1625 BEGIN
1626 hr_utility.trace('+====deinitialization_code====X '|| p_actid);
1627 END deinitialization_code;
1628
1629
1630
1631
1632
1633 /**************************************************************************/
1634 PROCEDURE gen_header_xml
1635 IS
1636 l_string VARCHAR2(32767) := NULL;
1637 l_string_value VARCHAR2(1000) := NULL;
1638 l_clob pay_file_details.file_fragment%TYPE;
1639 l_blob pay_file_details.blob_file_fragment%TYPE;
1640 l_payroll_action_id NUMBER;
1641 l_org_struct_id NUMBER := NULL;
1642 l_person_id NUMBER := NULL;
1643 l_org_id NUMBER := NULL;
1644 l_bg_id NUMBER;
1645 l_start_date DATE := NULL;
1646 l_end_date DATE := NULL;
1647 l_type VARCHAR2(1) := NULL;
1648 l_lang VARCHAR2(2) := userenv ('lang');
1649
1650 CURSOR c_get_hierarchy(p_org_struct_id IN NUMBER) IS
1651 SELECT name
1652 FROM per_organization_structures
1653 WHERE organization_structure_id = p_org_struct_id;
1654
1655 CURSOR c_get_employer(p_org_id IN NUMBER) IS
1656 SELECT name
1657 FROM hr_organization_units
1658 WHERE organization_id = p_org_id;
1659
1660 CURSOR c_get_employee(p_person_id IN NUMBER) IS
1661 SELECT full_name
1662 FROM per_all_people_f
1663 WHERE person_id = p_person_id;
1664
1665 BEGIN
1666 hr_utility.trace('+====gen_header_xml========================================+');
1667 l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
1668 hr_utility.trace('l_payroll_action_id : '||l_payroll_action_id);
1669 l_string := l_string || '<ROOT>'||EOL;
1670
1671 get_all_parameters (
1672 p_payroll_action_id => l_payroll_action_id
1673 ,p_org_struct_id => l_org_struct_id
1674 ,p_person_id => l_person_id
1675 ,p_org_id => l_org_id
1676 ,p_bg_id => l_bg_id
1677 ,p_start_date => l_start_date
1678 ,p_end_date => l_end_date
1679 ,p_type => l_type);
1680
1681 l_string_value := get_look_up_value('HR_NL_DS_REPORT_TYPE',l_type,l_lang);
1682 l_string := l_string || '<P_REPORT_TYPE>'||clean_XML(l_string_value)||'</P_REPORT_TYPE>'||EOL;
1683
1684 l_string_value := fnd_date.date_to_chardate(l_start_date);
1685 l_string := l_string || '<P_DATE_FROM>'||clean_XML(l_string_value)||'</P_DATE_FROM>'||EOL;
1686
1687 l_string_value := fnd_date.date_to_chardate(l_end_date);
1688 l_string := l_string || '<P_DATE_TO>'||clean_XML(l_string_value)||'</P_DATE_TO>'||EOL;
1689
1690 IF l_org_struct_id IS NOT NULL THEN
1691 OPEN c_get_hierarchy(l_org_struct_id);
1692 FETCH c_get_hierarchy INTO l_string_value;
1693 CLOSE c_get_hierarchy;
1694 l_string := l_string || '<P_HIERARCHY>'||clean_XML(l_string_value)||'</P_HIERARCHY>'||EOL;
1695 END IF;
1696
1697 IF l_org_id IS NOT NULL THEN
1698 OPEN c_get_employer(l_org_id);
1699 FETCH c_get_employer INTO l_string_value;
1700 CLOSE c_get_employer;
1701 l_string := l_string || '<P_EMPLOYER>'||clean_XML(l_string_value)||'</P_EMPLOYER>'||EOL;
1702 END IF;
1703
1704 IF l_person_id IS NOT NULL THEN
1705 OPEN c_get_employee(l_person_id);
1706 FETCH c_get_employee INTO l_string_value;
1707 CLOSE c_get_employee;
1708 l_string := l_string || '<P_EMPLOYEE>'||clean_XML(l_string_value)||'</P_EMPLOYEE>'||EOL;
1709 END IF;
1710
1711 l_clob := l_clob||l_string;
1712 IF l_clob IS NOT NULL THEN
1713 l_blob := c2b(l_clob);
1714 pay_core_files.write_to_magtape_lob(l_blob);
1715 END IF;
1716 hr_utility.trace('X====gen_header_xml========================================X');
1717 EXCEPTION
1718 WHEN others THEN
1719 hr_utility.trace('Exception in gen_header_xml SQL-ERRM : '||SQLERRM);
1720 END gen_header_xml;
1721 /**************************************************************************/
1722 PROCEDURE gen_footer_xml
1723 IS
1724 l_buf VARCHAR2(32767);
1725 BEGIN
1726 hr_utility.trace('+====gen_footer_xml========================================+');
1727 l_buf := l_buf || '</ROOT>'||EOL ;
1728 pay_core_files.write_to_magtape_lob(l_buf);
1729 hr_utility.trace('X====gen_footer_xml========================================X');
1730 EXCEPTION
1731 WHEN others THEN
1732 hr_utility.trace('Exception in gen_footer_xml SQL-ERRM : '||SQLERRM);
1733 END gen_footer_xml;
1734 /**************************************************************************/
1735
1736 PROCEDURE gen_body_xml_main(p_type varchar2, p_id number) IS
1737
1738 CURSOR cur_abs_info_details (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
1739 SELECT pai.action_information1 abs_att_id
1740 ,pai.action_information2 rec_dt
1741 ,pai.action_information3 st_dt
1742 ,pai.action_information4 abs_cat
1743 ,pai.action_information5 person_id
1744 ,pai.action_information6 ass_id
1745 ,pai.action_information7 sick_ref
1746 ,pai.action_information8 noti_date
1747 ,pai.action_information9 lt_st_dt
1748 ,pai.action_information10 full_rec_dt
1749 ,pai.action_information11 partial_rec_dt
1750 ,pai.action_information12 daily_si
1751 ,pai.action_information13 contact_name
1752 ,pai.action_information14 est_name
1753 ,pai.action_information15 gender
1754 ,pai.action_information16 contact_ph_no
1755 FROM pay_action_information pai
1756 WHERE pai.action_context_id = c_assignment_action_id
1757 AND pai.action_information_category = 'NL_DSRR_ABS_INFO'
1758 AND action_context_type = 'AAP';
1759 /**************************************************************************/
1760 CURSOR cur_emp_details (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
1761 SELECT action_information2 test_msg
1762 ,action_information3 tax_no_sender
1763 ,action_information4 role_sender
1764 ,action_information5 contact_per_or_dept
1765 ,action_information6 ph_no_contact
1766 ,action_information7 risk_group
1767 ,action_information8 sector
1768 ,action_information9 tax_reg_num
1769 ,action_information11 sofi_number
1770 ,action_information12 date_of_birth
1771 ,action_information13 gender
1772 ,action_information14 init
1773 ,action_information15 prefix
1774 ,action_information16 last_name
1775 ,action_information21 asg_seq
1776 ,action_information22 emp_num
1777 ,action_information23 est_name
1778 ,action_information24 gender_contact
1779 FROM pay_action_information pai
1780 WHERE pai.action_context_id = c_assignment_action_id
1781 AND pai.action_information_category = 'NL_DSRR_EMP_INFO'
1782 AND action_context_type = 'AAP';
1783 /**************************************************************************/
1784 CURSOR cur_withdrawl_flag(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
1785 SELECT pai.action_information30 flag
1786 FROM pay_action_information pai
1787 WHERE pai.action_context_id = c_assignment_action_id
1788 AND pai.action_information_category = 'NL_DSR_ABS_INFO'
1789 AND action_context_type = 'AAP';
1790 /**************************************************************************/
1791 CURSOR cur_app_version IS
1792 SELECT release_name
1793 FROM fnd_product_groups;
1794 /**************************************************************************/
1795 --Variables--
1796 l_clob PAY_FILE_DETAILS.FILE_FRAGMENT%TYPE;
1797 l_blob PAY_FILE_DETAILS.BLOB_FILE_FRAGMENT%TYPE;
1798 l_file UTL_FILE.FILE_TYPE;
1799 l_directory_path VARCHAR2(500);
1800 l_file_name VARCHAR2(50);
1801 x NUMBER := 0;
1802 r NUMBER := 0;
1803 l_abs_info cur_abs_info_details%ROWTYPE;
1804 l_emp_details cur_emp_details%ROWTYPE;
1805 l_get_app_ver cur_app_version%ROWTYPE;
1806 l_withdrawl_flag cur_withdrawl_flag%ROWTYPE;
1807 l_type VARCHAR2(1);
1808 l_lang VARCHAR2(2) := userenv ('lang');
1809 xXMLTable tXMLTable;
1810 rXMLTable tXMLTable;
1811
1812 PROCEDURE Tag (xCtr IN OUT NOCOPY NUMBER
1813 ,rCtr IN OUT NOCOPY NUMBER
1814 ,pTagName IN VARCHAR2
1815 ,pTagValue IN VARCHAR2
1816 ,pDestination IN VARCHAR2)
1817 IS
1818 BEGIN
1819 IF pTagValue IS NOT NULL THEN
1820 IF pDestination IN ('X','B') THEN
1821 xXMLTable(xCtr).TagName := pTagName;
1822 xXMLTable(xCtr).TagValue := pTagValue;
1823
1824 IF pTagName = 'UwvZwWerkhervattingMelding' AND pTagValue = '_START_' THEN
1825 xXMLTable(xCtr).TagName := xXMLTable(xCtr).TagName||EOL||
1826 'xsi:schemaLocation="http://schemas.uwv.nl/UwvML/Berichten/UwvZwWerkhervattingMelding-v0207 UwvZwWerkhervattingMelding-v0207-b02.xsd"'||EOL||
1827 'xmlns:str="http://schemas.uwv.nl/UwvML/Berichten/UwvZwWerkhervattingMelding-v0207"'||EOL||
1828 'xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"';
1829 END IF;
1830
1831 xCtr := xCtr + 1;
1832 END IF;
1833 IF pDestination IN ('R','B') THEN
1834 rXMLTable(rCtr).TagName := pTagName;
1835 rXMLTable(rCtr).TagValue := pTagValue;
1836 rCtr := rCtr + 1;
1837 END IF;
1838 END IF;
1839 END Tag;
1840
1841
1842 BEGIN
1843 hr_utility.trace('+====gen_body_xml_main=====================================+');
1844
1845 OPEN cur_abs_info_details(p_id);
1846 FETCH cur_abs_info_details INTO l_abs_info;
1847 CLOSE cur_abs_info_details;
1848
1849 OPEN cur_emp_details(p_id);
1850 FETCH cur_emp_details INTO l_emp_details;
1851 CLOSE cur_emp_details;
1852
1853 OPEN cur_app_version;
1854 FETCH cur_app_version into l_get_app_ver;
1855 CLOSE cur_app_version;
1856
1857 IF p_type = 'R' THEN
1858 OPEN cur_withdrawl_flag(p_id);
1859 FETCH cur_withdrawl_flag into l_withdrawl_flag;
1860 CLOSE cur_withdrawl_flag;
1861 END IF;
1862 hr_utility.trace('Fetched all data. XML Generation starts');
1863
1864 Tag(x,r,'UwvZwWerkhervattingMelding','_START_','B'); -- sickness recovery report*
1865 Tag(x,r,'IndTestbericht',l_emp_details.test_msg,'X'); -- test msg
1866 Tag(x,r,'KetenPartij','_START_','B'); -- + sender data*
1867 Tag(x,r,'Loonheffingennr',l_emp_details.tax_no_sender,'X'); -- tax reg no sender*
1868 Tag(x,r,'CdRolKetenpartij',l_emp_details.role_sender,'X'); -- role sender*
1869 Tag(x,r,'NaamSoftwarePakket','Oracle HCM','X'); -- appln name
1870 Tag(x,r,'VersieSoftwarePakket',l_get_app_ver.release_name,'X'); -- ver no
1871 IF l_emp_details.contact_per_or_dept IS NOT NULL
1872 OR l_emp_details.ph_no_contact IS NOT NULL THEN
1873 Tag(x,r,'ContactGegevens','_START_','B'); -- + contact data
1874 Tag(x,r,'NaamContactpersoonAfd',l_emp_details.contact_per_or_dept,'B'); -- person or dept
1875 Tag(x,r,'TelefoonnrContactpersoonAfd',l_emp_details.ph_no_contact,'B'); -- ph no
1876 Tag(x,r,'ContactGegevens','_END_','B'); -- - contact data
1877 END IF;
1878 Tag(x,r,'KetenPartij','_END_','B'); -- - sender data*
1879 Tag(x,r,'AdministratieveEenheid','_START_','B'); -- + employer*
1880 Tag(x,r,'Loonheffingennr',l_emp_details.tax_reg_num,'X'); -- tax no employer*
1881 Tag(x,r,'SectorRisicogroep','_START_','X'); -- + risk sector grp*
1882 Tag(x,r,'CdRisicoPremiegroep',l_emp_details.risk_group,'X'); -- risk grp*
1883 Tag(x,r,'CdSectorOsv',l_emp_details.sector,'X'); -- sector *
1884 Tag(x,r,'SectorRisicogroep','_END_','X'); -- - risk sector grp*
1885 Tag(x,r,'NatuurlijkPersoon','_START_','B'); -- + employee data*
1886 Tag(x,r,'Burgerservicenr',l_emp_details.sofi_number,'B'); -- sofino*
1887 Tag(x,r,'Geboortedat',l_emp_details.date_of_birth,'X'); -- dob*
1888 Tag(x,r,'Geboortedat',fnd_date.date_to_chardate(
1889 to_date(l_emp_details.date_of_birth,'YYYYMMDD')),'R');
1890 Tag(x,r,'Geslacht',l_emp_details.gender,'X'); -- gender
1891 Tag(x,r,'Geslacht',get_look_up_value('HR_NL_DS_GENDER',
1892 l_emp_details.gender,l_lang),'R');
1893 Tag(x,r,'Voorletters',l_emp_details.init,'B'); -- initials
1894 Tag(x,r,'Voorvoegsel',l_emp_details.prefix,'B'); -- prefix
1895 Tag(x,r,'SignificantDeelVanDeAchternaam',l_emp_details.last_name,'B'); -- last name*
1896 Tag(x,r,'Arbeidsverhouding','_START_','B'); -- + assignment details*
1897 Tag(x,r,'NrInkomstenverhouding',l_emp_details.asg_seq,'B'); -- NumIV override/asg seq
1898 Tag(x,r,'MeldingWerkhervatting','_START_','B'); -- + sickness recovery details
1899 IF (p_type = 'R' AND l_withdrawl_flag.flag = 'Y') OR p_type = 'W' THEN
1900 Tag(x,r,'IndVerzoekTotIntrekken','1','X'); -- withdrawal code
1901 Tag(x,r,'IndVerzoekTotIntrekken',get_look_up_value('HR_NL_YES_NO','Y',l_lang),'R');
1902 END IF;
1903 Tag(x,r,'ReferentieMelding',NVL(l_abs_info.sick_ref,l_emp_details.emp_num),'B'); -- sickness reference
1904 Tag(x,r,'DatTijdAanbiedenKetenpartij',to_char(SYSDATE, 'YYYYMMDDHH24MISS'),'X');-- time stamp
1905 Tag(x,r,'DatTijdAanbiedenKetenpartij',fnd_date.date_to_charDT(sysdate),'R');
1906 Tag(x,r,'DatOntvangstMeldingWerkgever',l_abs_info.noti_date,'X'); -- date of reporting
1907 Tag(x,r,'DatOntvangstMeldingWerkgever',fnd_date.date_to_chardate(
1908 to_date(l_abs_info.noti_date,'YYYYMMDD')),'R');
1909 Tag(x,r,'DatEersteAoDag',l_abs_info.lt_st_dt,'X'); -- start date sickness
1910 Tag(x,r,'DatEersteAoDag',fnd_date.date_to_chardate(
1911 to_date(l_abs_info.lt_st_dt,'YYYYMMDD')),'R');
1912 IF l_abs_info.partial_rec_dt IS NULL THEN
1913 Tag(x,r,'WerkhervattingVolledig','_START_','B'); -- + full recovery details
1914 Tag(x,r,'DatHersteld',l_abs_info.full_rec_dt,'X'); -- full recovery date
1915 Tag(x,r,'DatHersteld',fnd_date.date_to_chardate(
1916 to_date(l_abs_info.full_rec_dt,'YYYYMMDD')),'R');
1917 Tag(x,r,'WerkhervattingVolledig','_END_','B'); -- - full recovery details
1918 ELSE
1919 Tag(x,r,'WerkhervattingGedeeltelijk','_START_','B'); -- + partial recovery details
1920 Tag(x,r,'DatWerkhervatting',l_abs_info.partial_rec_dt,'X'); -- partial recovery date
1921 Tag(x,r,'DatWerkhervatting',fnd_date.date_to_chardate(
1922 to_date(l_abs_info.partial_rec_dt,'YYYYMMDD')),'R');
1923 Tag(x,r,'BedrSvLoonGedeeltelijkWerkTijdensAo',l_abs_info.daily_si,'B'); -- Daily SI wage
1924 Tag(x,r,'WerkhervattingGedeeltelijk','_END_','B'); -- - partial recovery details
1925 END IF;
1926 IF l_abs_info.contact_name IS NOT NULL
1927 OR l_abs_info.est_name IS NOT NULL
1928 OR l_abs_info.gender IS NOT NULL
1929 OR l_abs_info.contact_ph_no IS NOT NULL THEN
1930 Tag(x,r,'ContactGegevens','_START_','B'); -- + contact details
1931 Tag(x,r,'NaamContactpersoonAfd',l_abs_info.contact_name,'B'); -- name
1932 Tag(x,r,'OmschrijvingLokaleVestiging',l_abs_info.est_name,'B'); -- Establishment
1933 Tag(x,r,'Geslacht',l_abs_info.gender,'X'); -- gender
1934 Tag(x,r,'Geslacht',get_look_up_value('HR_NL_DS_GENDER',
1935 l_abs_info.gender,l_lang),'R');
1936 Tag(x,r,'TelefoonnrContactpersoonAfd',l_abs_info.contact_ph_no,'B'); -- telephone
1937 Tag(x,r,'ContactGegevens','_END_','B'); -- - contact details
1938 ELSIF l_emp_details.contact_per_or_dept IS NOT NULL
1939 OR l_emp_details.ph_no_contact IS NOT NULL
1940 OR l_emp_details.est_name IS NOT NULL
1941 OR l_emp_details.gender IS NOT NULL
1942 THEN
1943 Tag(x,r,'ContactGegevens','_START_','B'); -- + contact details
1944 Tag(x,r,'NaamContactpersoonAfd',l_emp_details.contact_per_or_dept,'B'); -- name
1945 Tag(x,r,'OmschrijvingLokaleVestiging',l_emp_details.est_name,'B'); -- Establishment
1946 Tag(x,r,'Geslacht',l_emp_details.gender_contact,'X'); -- gender
1947 Tag(x,r,'Geslacht',get_look_up_value('HR_NL_DS_GENDER',
1948 l_emp_details.gender_contact,l_lang),'R');
1949 Tag(x,r,'TelefoonnrContactpersoonAfd',l_emp_details.ph_no_contact,'B'); -- telephone
1950 Tag(x,r,'ContactGegevens','_END_','B'); -- - contact details
1951 END IF;
1952 Tag(x,r,'MeldingWerkhervatting','_END_','B'); -- - sickness recovry details
1953 Tag(x,r,'Arbeidsverhouding','_END_','B'); -- - assignment details*
1954 Tag(x,r,'NatuurlijkPersoon','_END_','B'); -- - employee data*
1955 Tag(x,r,'AdministratieveEenheid','_END_','B'); -- - employer*
1956 Tag(x,r,'UwvZwWerkhervattingMelding','_END_','B'); -- sickness recovery report*
1957
1958 l_file_name := l_emp_details.emp_num||'_'||l_abs_info.full_rec_dt||'_DSRR';
1959 IF p_type in ('W','R') THEN
1960 l_file_name := l_file_name||'_'||p_type;
1961 END IF;
1962 l_file_name := l_file_name||'.xml';
1963
1964 write_file(xXMLTable, l_file_name);
1965
1966 write_report(rXMLTable);
1967
1968 hr_utility.trace('X====gen_body_xml_main=====================================X');
1969 EXCEPTION
1970 WHEN others THEN
1971 hr_utility.trace('Exception in gen_body_xml_main SQL-ERRM :'||SQLERRM);
1972 END gen_body_xml_main;
1973
1974 /**************************************************************************/
1975
1976 PROCEDURE gen_body_xml
1977 IS
1978
1979 l_payroll_action_id NUMBER;
1980 l_asg_action_id NUMBER;
1981 l_org_struct_id NUMBER := NULL;
1982 l_person_id NUMBER := NULL;
1983 l_org_id NUMBER := NULL;
1984 l_bg_id NUMBER;
1985 l_start_date DATE := NULL;
1986 l_end_date DATE := NULL;
1987 l_type VARCHAR2(1) := NULL;
1988 /**************************************************************************/
1989 CURSOR cur_get_child_act_id (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
1990 SELECT assignment_action_id ass_act_id
1991 FROM pay_assignment_actions
1992 WHERE source_action_id = c_assignment_action_id;
1993 /**************************************************************************/
1994 CURSOR get_parent_asg_ids(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
1995 SELECT locked_action_id parent_id
1996 FROM pay_action_interlocks
1997 WHERE locking_action_id = c_assignment_action_id;
1998 /**************************************************************************/
1999 CURSOR get_pact_id(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
2000 SELECT ppa.payroll_action_id
2001 FROM pay_payroll_actions ppa
2002 ,pay_assignment_actions paa
2003 WHERE paa.payroll_action_id = ppa.payroll_action_id
2004 AND paa.assignment_action_id = c_assignment_action_id;
2005 /**************************************************************************/
2006
2007 BEGIN
2008 hr_utility.trace('+====gen_body_xml==========================================+');
2009
2010 l_payroll_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID');
2011 hr_utility.trace('TRANSFER_PAYROLL_ACTION_ID = '||l_payroll_action_id);
2012
2013 l_asg_action_id := pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID');
2014 hr_utility.trace('TRANSFER_ACT_ID = '||l_asg_action_id);
2015
2016 OPEN get_pact_id(l_asg_action_id);
2017 FETCH get_pact_id INTO l_payroll_action_id;
2018 CLOSE get_pact_id;
2019
2020 hr_utility.trace('l_payroll_action_id = '||l_payroll_action_id);
2021 get_all_parameters (
2022 p_payroll_action_id => l_payroll_action_id
2023 ,p_org_struct_id => l_org_struct_id
2024 ,p_person_id => l_person_id
2025 ,p_org_id => l_org_id
2026 ,p_bg_id => l_bg_id
2027 ,p_start_date => l_start_date
2028 ,p_end_date => l_end_date
2029 ,p_type => l_type);
2030
2031 IF l_type = 'I' THEN
2032 hr_utility.trace('Initial Report');
2033 FOR v_child_act_id IN cur_get_child_act_id(l_asg_action_id)
2034 LOOP
2035 hr_utility.trace('Child Assignment Action ID :'||v_child_act_id.ass_act_id);
2036 gen_body_xml_main(l_type, v_child_act_id.ass_act_id);
2037 END LOOP;
2038 ELSIF l_type IN ('W','R') THEN
2039 hr_utility.trace('Withdrawal/Regeneration Report');
2040 FOR v_parent_asg_ids in get_parent_asg_ids(l_asg_action_id)
2041 LOOP
2042 hr_utility.trace('Parent Assignment Action ID :'||v_parent_asg_ids.parent_id);
2043 gen_body_xml_main(l_type, v_parent_asg_ids.parent_id);
2044 END LOOP;
2045 END IF;
2046 hr_utility.trace('X====gen_body_xml==========================================X');
2047 EXCEPTION
2048 WHEN others THEN
2049 hr_utility.trace('Exception in gen_body_xml SQL-ERRM :'||SQLERRM);
2050 END gen_body_xml;
2051
2052 END PER_NL_DSRR_ARCHIVE;
2053