[Home] [Help]
PACKAGE BODY: APPS.PER_NL_FDR_ARCHIVE
Source
1 PACKAGE BODY PER_NL_FDR_ARCHIVE as
2 /* $Header: penlfdra.pkb 120.2 2007/12/19 13:41:38 abhgangu noship $ */
3
4
5 /*------------------------------------------------------------------------------
6 |Name : GET_PARAMETER |
7 |Type : Function |
8 |Description : Funtion to get the parameters of the archive process |
9 -------------------------------------------------------------------------------*/
10 g_year VARCHAR2(10);
11
12 FUNCTION get_parameter (p_parameter_string in varchar2
13 ,p_token in varchar2
14 ,p_segment_number in number default null ) RETURN varchar2
15 IS
16
17 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
18 l_start_pos NUMBER;
19 l_delimiter varchar2(1):=' ';
20
21 BEGIN
22 --
23 --hr_utility.set_location('Entering get_parameter',52);
24 --
25 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
26 --
27 IF l_start_pos = 0 THEN
28 l_delimiter := '|';
29 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
30 end if;
31
32 IF l_start_pos <> 0 THEN
33 l_start_pos := l_start_pos + length(p_token||'=');
34 l_parameter := substr(p_parameter_string,
35 l_start_pos,
36 instr(p_parameter_string||' ',
37 l_delimiter,l_start_pos)
38 - l_start_pos);
39 IF p_segment_number IS NOT NULL THEN
40 l_parameter := ':'||l_parameter||':';
41 l_parameter := substr(l_parameter,
42 instr(l_parameter,':',1,p_segment_number)+1,
43 instr(l_parameter,':',1,p_segment_number+1) -1
44 - instr(l_parameter,':',1,p_segment_number));
45 END IF;
46 END IF;
47 --
48 --hr_utility.set_location('Leaving get_parameter',53);
49 --hr_utility.set_location('Entering get_parameter l_parameter--'||l_parameter||'--',54);
50 RETURN l_parameter;
51
52 END get_parameter;
53
54
55 /*-----------------------------------------------------------------------------
56 |Name : GET_ALL_PARAMETERS |
57 |Type : Procedure |
58 |Description: Procedure which returns all the parameters of the archive process|
59 -------------------------------------------------------------------------------*/
60
61
62 PROCEDURE get_all_parameters (p_payroll_action_id IN NUMBER
63 ,p_report_date OUT NOCOPY VARCHAR2
64 ,p_org_struct_id OUT NOCOPY NUMBER
65 ,p_person_id OUT NOCOPY NUMBER
66 ,p_org_id OUT NOCOPY NUMBER
67 ,p_bg_id OUT NOCOPY NUMBER) IS
68 --
69 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
70
71 SELECT PER_NL_FDR_ARCHIVE.get_parameter(legislative_parameters,'REPORT_DATE')
72 ,TO_NUMBER(PER_NL_FDR_ARCHIVE.get_parameter(legislative_parameters,'ORG_STRUCT_ID'))
73 ,TO_NUMBER(PER_NL_FDR_ARCHIVE.get_parameter(legislative_parameters,'PERSON_ID'))
74 ,TO_NUMBER(PER_NL_FDR_ARCHIVE.get_parameter(legislative_parameters,'ORG_ID'))
75 ,business_group_id
76 FROM pay_payroll_actions
77 WHERE payroll_action_id = p_payroll_action_id;
78
79 --
80 l_parameter_string PAY_PAYROLL_ACTIONS.LEGISLATIVE_PARAMETERS%TYPE;
81
82 BEGIN
83
84 --hr_utility.set_location('Entering get_all_parameters',51);
85
86 OPEN csr_parameter_info (p_payroll_action_id);
87 FETCH csr_parameter_info INTO p_report_date
88 ,p_org_struct_id
89 ,p_person_id
90 ,p_org_id
91 ,p_bg_id;
92 CLOSE csr_parameter_info;
93
94 select ppa.legislative_parameters into l_parameter_string
95 from pay_payroll_actions ppa
96 where ppa.payroll_action_id = p_payroll_action_id;
97
98
99 IF instr(l_parameter_string,'/',1,2) - instr(l_parameter_string,'/',1,1) = 3 THEN
100
101 p_report_date := substr(l_parameter_string,instr(l_parameter_string,'/',1,1)-4,10);
102
103 END IF;
104
105 --hr_utility.set_location('Leaving get_all_parameters',54);
106 END;
107
108
109
110 FUNCTION get_IANA_charset RETURN VARCHAR2 IS
111 CURSOR csr_get_iana_charset IS
112 SELECT tag
113 FROM fnd_lookup_values
114 WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
115 AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
116 INSTR(USERENV('LANGUAGE'), '.') + 1)
117 AND language = 'US';
118
119 lv_iana_charset fnd_lookup_values.tag%type;
120 BEGIN
121 OPEN csr_get_iana_charset;
122 FETCH csr_get_iana_charset INTO lv_iana_charset;
123 CLOSE csr_get_iana_charset;
124
125 --hr_utility.trace('IANA Charset = '||lv_iana_charset);
126 RETURN (lv_iana_charset);
127 END get_IANA_charset;
128
129
130
131 /*-----------------------------------------------------------------------------
132 |Name : WRITETOCLOB_RTF |
133 |Type : Procedure |
134 |Description: Procedure to write contents of XML file as CLOB |
135 -------------------------------------------------------------------------------*/
136
137
138 PROCEDURE WritetoCLOB_rtf(p_xfdf_clob out nocopy clob, p_XMLTable IN tXMLTable) IS
139
140 l_xfdf_string clob;
141 l_str0 varchar2(1000);
142 l_str1 varchar2(1000);
143 l_str2 varchar2(20);
144 l_str3 varchar2(20);
145 l_str4 varchar2(1000);
146 l_str5 varchar2(1000);
147 l_str6 varchar2(1000);
148 l_str7 varchar2(1000);
149 l_str8 varchar2(20);
150
151
152 begin
153 --fnd_file.put_line(fnd_file.log,'g_year : '||g_year);
154 hr_utility.set_location('Entered Procedure Write to clob ',100);
155 -- l_str0 := '<?xml version="1.0" encoding="UTF-8"?>';
156 -- l_str0 := '<?xml version="1.0" encoding="' || get_IANA_charset || '"?>';
157 l_str0 := '<?xml version="1.0" encoding="ISO-8859-1"?>'; -- for bug 5376513
158 IF g_year < '2008' THEN
159 l_str1 := '<Eerstedagsmelding xmlns="http://xml.belastingdienst.nl/schemas/Eerstedagsmelding/2006/02" version="1.0">' ;
160 ELSE
161 l_str1 := '<Eerstedagsmelding xmlns="http://xml.belastingdienst.nl/schemas/Eerstedagsmelding/'||g_year||'/01" version="1.1">' ;
162 END IF;
163 l_str2 := '<';
164 l_str3 := '>';
165 -- l_str4 := '<value>' ;
166 -- l_str5 := '</value> </' ;
167 l_str4 := '</Eerstedagsmelding>';
168 l_str5 := '<Eerstedagsmelding xmlns="http://xml.belastingdienst.nl/schemas/Eerstedagsmelding/2006/02" version="1.0"></Eerstedagsmelding>';
169 l_str8 := '</';
170 dbms_lob.createtemporary(l_xfdf_string,FALSE,DBMS_LOB.CALL);
171 dbms_lob.open(l_xfdf_string,dbms_lob.lob_readwrite);
172 dbms_lob.writeAppend( l_xfdf_string, length(l_str0), l_str0);
173 if p_XMLTable.count > 0 then
174 dbms_lob.writeAppend( l_xfdf_string, length(l_str1), l_str1 );
175 FOR ctr_table IN p_XMLTable.FIRST .. p_XMLTable.LAST LOOP
176 l_str6 := p_XMLTable(ctr_table).TagName;
177 l_str7 := p_XMLTable(ctr_table).TagValue;
178 if (l_str6 = 'Bericht' OR l_str6 = 'AdministratieveEenheid' OR l_str6 = 'Dienstbetrekking' OR l_str6 = 'NatuurlijkPersoon') then
179 if (l_str7 is null) then
180 dbms_lob.writeAppend( l_xfdf_string, length(l_str2), l_str2);
181 dbms_lob.writeAppend( l_xfdf_string, length(l_str6), l_str6);
182 dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3);
183 else
184 if (l_str7 = 'END') then
185 dbms_lob.writeAppend( l_xfdf_string, length(l_str8), l_str8);
186 dbms_lob.writeAppend( l_xfdf_string, length(l_str6), l_str6);
187 dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3);
188 end if;
189 end if;
190 else
191 if (l_str7 is not null) then
192 dbms_lob.writeAppend( l_xfdf_string, length(l_str2), l_str2);
193 dbms_lob.writeAppend( l_xfdf_string, length(l_str6), l_str6);
194 dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3);
195 dbms_lob.writeAppend( l_xfdf_string, length(l_str7), l_str7);
196 dbms_lob.writeAppend( l_xfdf_string, length(l_str8), l_str8);
197 dbms_lob.writeAppend( l_xfdf_string, length(l_str6), l_str6);
198 dbms_lob.writeAppend( l_xfdf_string, length(l_str3), l_str3);
199 elsif (l_str7 is null and l_str6 is not null) then
200 dbms_lob.writeAppend(l_xfdf_string, length(l_str2), l_str2);
201 dbms_lob.writeAppend(l_xfdf_string, length(l_str6), l_str6);
202 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
203 dbms_lob.writeAppend(l_xfdf_string, length(l_str8), l_str8);
204 dbms_lob.writeAppend(l_xfdf_string, length(l_str6), l_str6);
205 dbms_lob.writeAppend(l_xfdf_string, length(l_str3), l_str3);
206 else
207 null;
208 end if;
209 end if;
210 END LOOP;
211 dbms_lob.writeAppend( l_xfdf_string, length(l_str4), l_str4 );
212 else
213 dbms_lob.writeAppend( l_xfdf_string, length(l_str5), l_str5 );
214 end if;
215 DBMS_LOB.CREATETEMPORARY(p_xfdf_clob,TRUE);
216 p_xfdf_clob := l_xfdf_string;
217 hr_utility.set_location('Finished Procedure Write to CLOB ',110);
218 EXCEPTION
219 WHEN OTHERS then
220 HR_UTILITY.TRACE('sqleerm ' || SQLERRM);
221 HR_UTILITY.RAISE_ERROR;
222 END WritetoCLOB_rtf;
223
224
225
226 /*------------------------------------------------------------------------------
227 |Name : CHECK_TAX_DETAILS |
228 |Type : Function |
229 |Description : Returns 1 if the organization has tax details attached |
230 -------------------------------------------------------------------------------*/
231
232
233 FUNCTION check_tax_details (p_org_id IN NUMBER) RETURN NUMBER IS
234
235 l_return_val NUMBER := 0;
236
237 BEGIN
238
239 BEGIN
240
241 select 1 INTO l_return_val
242 from hr_organization_units hou
243 where hou.organization_id = p_org_id
244 and EXISTS (SELECT 1 FROM hr_organization_information hoi1
245
246 WHERE hoi1.org_information_context= 'NL_ORG_INFORMATION'
247 AND hoi1.organization_id=hou.organization_id
248 AND hoi1.org_information4 IS NOT NULL
249 AND hoi1.org_information3 IS NOT NULL
250
251 UNION
252
253 SELECT 1 FROM hr_organization_information hoi2
254
255 WHERE hoi2.org_information_context= 'NL_LE_TAX_DETAILS'
256 AND hoi2.organization_id=hou.organization_id
257 AND hoi2.org_information1 IS NOT NULL
258 AND hoi2.org_information2 IS NOT NULL
259
260 );
261
262 EXCEPTION
263
264 WHEN TOO_MANY_ROWS
265 THEN l_return_val := 1;
266
267 WHEN NO_DATA_FOUND
268 THEN null;
269
270 END;
271
272 return l_return_val;
273
274 END check_tax_details;
275
276
277 /*------------------------------------------------------------------------------
278 |Name : GET_REF_DATE |
279 |Type : Function |
280 |Description : Function to return the date at which the assignment record |
281 | needs to be picked for an employee. |
282 -------------------------------------------------------------------------------*/
283
284
285 FUNCTION get_ref_date (p_person_id IN NUMBER) return DATE is
286
287 l_ref_date DATE := to_date('31-12-4712','DD-MM-RRRR');
288
289 BEGIN
290
291 select ppos.date_start into l_ref_date
292 from per_periods_of_service ppos
293 where ppos.person_id = p_person_id
294 and ppos.date_start =
295 (select max(ppos1.date_start)
296 from per_periods_of_service ppos1
297 where ppos1.person_id = p_person_id);
298
299 BEGIN
300
301 select distinct pap.effective_start_date into l_ref_date
302 from per_all_people_f pap
303 where pap.person_id = p_person_id
304 and pap.effective_start_date =
305 (select max(pap1.effective_start_date)
306 from per_all_people_f pap1
307 where pap1.person_id = p_person_id
308 and pap1.current_employee_flag = 'Y')
309 and exists (select 1 from per_people_extra_info ppei
310 where ppei.person_id=p_person_id
311 and information_type='NL_FIRST_DAY_REPORT'
312 and ppei.pei_information2='Y');
313
314 EXCEPTION
315
316 WHEN NO_DATA_FOUND
317 THEN
318 null;
319 END;
320
321 return l_ref_date;
322
323 END get_ref_date;
324
325
326
327
328
329 /*------------------------------------------------------------------------------
330 |Name : EMP_CHECK |
331 |Type : Function |
332 |Description : Function required for valueset HR_NL_EMPLOYEE_FDR |
333 -------------------------------------------------------------------------------*/
334
335
336 FUNCTION emp_check (p_bg_id IN NUMBER
337 ,p_org_struct_id IN NUMBER
338 ,p_org_id IN NUMBER
339 ,p_person_id IN NUMBER
340 ,p_report_date IN DATE) return NUMBER IS
341
342 l_return_val NUMBER := 0;
343
344 BEGIN
345
346 IF p_org_id is not NULL THEN
347 BEGIN
348
349 select 1 INTO l_return_val
350 from per_all_assignments_f paa,
351 per_assignment_status_types past,
352 per_all_people_f pap,
353 per_periods_of_service ppos,
354 per_org_structure_versions posv
355
356 where posv.organization_structure_id=p_org_struct_id
357 and p_report_date between posv.date_from and nvl(posv.date_to,hr_general.end_of_time)
358 and (paa.organization_id in
359 ((SELECT pose.organization_id_child
360 FROM per_org_structure_elements pose
361 WHERE pose.org_structure_version_id = posv.org_structure_version_id
362 START with pose.organization_id_parent = p_org_id
363 CONNECT BY prior organization_id_child = organization_id_parent)
364 UNION
365 (select p_org_id from dual))
366 OR
367 nvl(paa.establishment_id,-1) = p_org_id)
368 and paa.person_id = p_person_id
369 and paa.primary_flag = 'Y'
370 and past.assignment_status_type_id = paa.assignment_status_type_id
371 and past.per_system_status = 'ACTIVE_ASSIGN'
372 and get_ref_date(pap.person_id) between paa.effective_start_date and paa.effective_end_date
373 and paa.business_group_id = p_bg_id
374 and pap.person_id = p_person_id
375 and ppos.business_group_id = pap.business_group_id
376 and pap.effective_start_date >= to_date('04-07-2006','DD-MM-RRRR')
377 and ppos.person_id = pap.person_id
378 and ppos.date_start =
379 (select max(ppos1.date_start)
380 from per_periods_of_service ppos1
381 where ppos1.person_id = ppos.person_id)
382 and ppos.date_start >= to_date('04-07-2006','DD-MM-RRRR')
383 and (exists(select 1 from per_people_extra_info ppei
384 where ppei.person_id=p_person_id
385 and information_type='NL_FIRST_DAY_REPORT'
386 and ppei.pei_information2='Y')
387 or not exists(select 1 from per_people_extra_info ppei
388 where ppei.person_id=p_person_id
389 and information_type='NL_FIRST_DAY_REPORT')
390 );
391
392 EXCEPTION
393 WHEN TOO_MANY_ROWS
394 THEN
395 l_return_val := 1;
396
397 WHEN NO_DATA_FOUND
398 THEN
399 null;
400
401 END;
402
403 ELSIF p_org_struct_id is not NULL THEN
404
405 BEGIN
406
407 select 1 INTO l_return_val
408 from per_all_assignments_f paa,
409 per_assignment_status_types past,
410 per_all_people_f pap,
411 per_periods_of_service ppos,
412 per_org_structure_versions posv
413
414 where posv.organization_structure_id=p_org_struct_id
415 and p_report_date between posv.date_from and nvl(posv.date_to,hr_general.end_of_time)
416 and paa.person_id = p_person_id
417 and paa.primary_flag = 'Y'
418 and past.assignment_status_type_id = paa.assignment_status_type_id
419 and past.per_system_status = 'ACTIVE_ASSIGN'
420 and get_ref_date(pap.person_id) between paa.effective_start_date and paa.effective_end_date
421 and paa.business_group_id = p_bg_id
422 and pap.person_id = p_person_id
423 and pap.effective_start_date >= to_date('04-07-2006','DD-MM-RRRR')
424 and ppos.business_group_id = pap.business_group_id
425 and ppos.person_id = pap.person_id
426 and ppos.date_start =
427 (select max(ppos1.date_start)
428 from per_periods_of_service ppos1
429 where ppos1.person_id = ppos.person_id)
430 and ppos.date_start >= to_date('04-07-2006','DD-MM-RRRR')
431 and (exists(select 1 from per_people_extra_info ppei
432 where ppei.person_id=p_person_id
433 and information_type='NL_FIRST_DAY_REPORT'
434 and ppei.pei_information2='Y')
435 or not exists(select 1 from per_people_extra_info ppei
436 where ppei.person_id=p_person_id
437 and information_type='NL_FIRST_DAY_REPORT')
438 )
439 and (hr_nl_org_info.get_tax_org_id(posv.org_structure_version_id,paa.organization_id) is not null
440 OR
441 per_nl_fdr_archive.org_check(pap.business_group_id, null, nvl(paa.establishment_id,-1), to_date(p_report_date,'RRRR/MM/DD')) = 1)
442 and paa.organization_id in
443 (select pose.organization_id_parent
444 from per_org_structure_elements pose
445 where posv.org_structure_version_id = pose.org_structure_version_id
446 UNION
447 select pose.organization_id_child
448 from per_org_structure_elements pose
449 where posv.org_structure_version_id = pose.org_structure_version_id);
450
451
452 EXCEPTION
453 WHEN TOO_MANY_ROWS
454 THEN
455 l_return_val := 1;
456
457 WHEN NO_DATA_FOUND
458 THEN
459 null;
460
461
462 END;
463
464 END IF;
465
466 return l_return_val;
467
468 END;
469
470
471
472 /*------------------------------------------------------------------------------
473 |Name : ORG_CHECK |
474 |Type : Function |
475 |Description : Function required for valueset HR_NL_EMPLOYER_FDR |
476 -------------------------------------------------------------------------------*/
477
478
479 FUNCTION org_check (p_bg_id IN NUMBER
480 ,p_org_struct_id IN NUMBER
481 ,p_org_id IN NUMBER
482 ,p_report_date IN DATE) return NUMBER IS
483
484 l_return_val NUMBER := 0;
485
486 BEGIN
487
488 /*hr_utility.trace_on(NULL,'NL_FDR');
489 hr_utility.set_location('Report date - '||p_report_date,1);*/
490
491
492 IF p_org_struct_id is NULL THEN
493
494 --hr_utility.set_location('hierarchy not chosen',1);
495 BEGIN
496
497 select 1 INTO l_return_val
498 from hr_organization_units hou
499 WHERE HOU.BUSINESS_GROUP_ID = p_bg_id
500 AND HOU.organization_id = p_org_id
501 AND EXISTS (SELECT 1 FROM HR_ALL_ORGANIZATION_UNITS HOU1, HR_ORGANIZATION_INFORMATION HOI1
502 WHERE HOU1.BUSINESS_GROUP_ID = p_bg_id
503 AND HOI1.ORG_INFORMATION_CONTEXT = 'NL_LE_TAX_DETAILS'
504 AND HOI1.ORG_INFORMATION1 IS NOT NULL
505 AND HOI1.ORG_INFORMATION2 IS NOT NULL
506 AND HOU1.ORGANIZATION_ID = HOI1.ORGANIZATION_ID
507 AND HOU1.ORGANIZATION_ID = HOU.ORGANIZATION_ID
508
509 UNION
510
511 SELECT 1 FROM HR_ALL_ORGANIZATION_UNITS HOU2, HR_ORGANIZATION_INFORMATION HOI2
512 WHERE HOU2.BUSINESS_GROUP_ID = p_bg_id
513 AND HOI2.ORG_INFORMATION_CONTEXT = 'NL_ORG_INFORMATION'
514 AND HOI2.ORG_INFORMATION4 IS NOT NULL
515 AND HOI2.ORG_INFORMATION3 IS NOT NULL
516 AND HOU2.ORGANIZATION_ID = HOI2.ORGANIZATION_ID
517 AND HOU2.ORGANIZATION_ID = HOU.ORGANIZATION_ID);
518
519 EXCEPTION
520
521 WHEN TOO_MANY_ROWS
522 THEN
523 l_return_val := 1;
524
525 WHEN NO_DATA_FOUND
526 THEN
527 null;
528
529 END;
530
531 ELSE
532
533 --hr_utility.set_location('hierarchy chosen',2);
534
535 BEGIN
536
537 SELECT 1 INTO l_return_val
538 FROM hr_organization_units hou
539 WHERE hou.organization_id = p_org_id
540 AND hou.business_group_id = p_bg_id
541 AND EXISTS (SELECT 1 FROM hr_organization_units hou1, hr_organization_information hoi1
542
543 WHERE hoi1.org_information_context= 'NL_ORG_INFORMATION'
544 AND hou1.business_group_id=p_bg_id
545 AND hou1.organization_id=hou.organization_id
546 AND hou1.organization_id= hoi1.organization_id
547 AND hoi1.org_information4 IS NOT NULL
548 AND hoi1.org_information3 IS NOT NULL
549 AND hou1.organization_id in
550
551 (SELECT pose.organization_id_parent
552 FROM per_org_structure_elements pose,per_org_structure_versions posv
553 WHERE posv.org_structure_version_id = pose.org_structure_version_id
554 AND posv.organization_structure_id=p_org_struct_id
555 AND p_report_date between posv.date_from and nvl(posv.date_to,hr_general.end_of_time)
556
557 UNION
558
559 SELECT pose.organization_id_child
560 FROM per_org_structure_elements pose,per_org_structure_versions posv
561 WHERE posv.org_structure_version_id = pose.org_structure_version_id
562 AND posv.organization_structure_id=p_org_struct_id
563 AND p_report_date between posv.date_from and nvl(posv.date_to,hr_general.end_of_time))
564
565 UNION
566
567 SELECT 1 FROM hr_organization_units hou2, hr_organization_information hoi2
568
569 WHERE hoi2.org_information_context= 'NL_LE_TAX_DETAILS'
570 AND hou2.business_group_id=p_bg_id
571 AND hou2.organization_id=hou.organization_id
572 AND hou2.organization_id= hoi2.organization_id
573 AND hoi2.org_information1 IS NOT NULL
574 AND hoi2.org_information2 IS NOT NULL
575 AND hou2.organization_id in
576
577 (SELECT pose.organization_id_parent
578 FROM per_org_structure_elements pose,per_org_structure_versions posv
579 WHERE posv.org_structure_version_id = pose.org_structure_version_id
580 AND posv.organization_structure_id=p_org_struct_id
581 AND p_report_date between posv.date_from and nvl(posv.date_to,hr_general.end_of_time)
582
583 UNION
584
585 SELECT pose.organization_id_child
586 FROM per_org_structure_elements pose,per_org_structure_versions posv
587 WHERE posv.org_structure_version_id = pose.org_structure_version_id
588 AND posv.organization_structure_id=p_org_struct_id
589 AND p_report_date between posv.date_from and nvl(posv.date_to,hr_general.end_of_time))
590
591 );
592
593 EXCEPTION
594
595 WHEN TOO_MANY_ROWS
596 THEN
597 l_return_val := 1;
598
599 WHEN NO_DATA_FOUND
600 THEN
601 null;
602
603 END;
604
605 END IF;
606
607 return l_return_val;
608
609 END org_check;
610
611
612
613 /*--------------------------------------------------------------------
614 |Name : RANGE_CODE |
615 |Type : Procedure |
616 |Description: This procedure returns an sql string to select a range |
617 | of assignments eligible for reporting |
618 ----------------------------------------------------------------------*/
619
620
621 PROCEDURE RANGE_CODE (pactid IN NUMBER
622 ,sqlstr OUT NOCOPY VARCHAR2) is
623
624
625 l_format VARCHAR2(40);
626
627 BEGIN
628
629
630 sqlstr := 'SELECT DISTINCT person_id
631 FROM per_all_people_f pap
632 ,pay_payroll_actions ppa
633 WHERE ppa.payroll_action_id = :payroll_action_id
634 AND ppa.business_group_id = pap.business_group_id
635 ORDER BY pap.person_id';
636
637
638
639 --hr_utility.trace_on(NULL,'NL_FDR');
640 hr_utility.set_location('Leaving Range Code',10);
641
642 EXCEPTION
643
644 WHEN OTHERS THEN
645 -- Return cursor that selects no rows
646 sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
647
648
649 END RANGE_CODE;
650
651
652
653
654 /*--------------------------------------------------------------------
655 |Name : ASSIGNMENT_ACTION_CODE |
656 |Type : Procedure |
657 |Description: This procedure further filters which assignments are |
658 | eligible for reporting |
659 ----------------------------------------------------------------------*/
660
661
662 PROCEDURE ASSIGNMENT_ACTION_CODE (p_payroll_action_id in number
663 ,p_start_person_id in number
664 ,p_end_person_id in number
665 ,p_chunk in number) IS
666
667
668 CURSOR csr_get_asg_person (p_person_id NUMBER
669 ,p_start_person_id NUMBER
670 ,p_end_person_id NUMBER
671 ,p_payroll_action_id NUMBER) is
672 select distinct paa.assignment_id assignment_id
673
674 from per_all_assignments_f paa,
675 per_assignment_status_types past,
676 pay_payroll_actions ppa,
677 per_all_people_f pap,
678 per_periods_of_service ppos
679
680 where p_person_id between p_start_person_id and p_end_person_id
681 and pap.person_id = p_person_id
682 and paa.person_id = pap.person_id
683 and paa.primary_flag = 'Y'
684 and past.assignment_status_type_id = paa.assignment_status_type_id
685 and past.per_system_status = 'ACTIVE_ASSIGN'
686 and get_ref_date(pap.person_id) between paa.effective_start_date and paa.effective_end_date
687 and pap.effective_start_date >= to_date('04-07-2006','DD-MM-RRRR')
688 and ppos.business_group_id = pap.business_group_id
689 and ppos.person_id = pap.person_id
690 and ppos.date_start =
691 (select max(ppos1.date_start)
692 from per_periods_of_service ppos1
693 where ppos1.person_id = ppos.person_id)
694 and ppos.date_start >= to_date('04-07-2006','DD-MM-RRRR')
695 and pap.business_group_id=ppa.business_group_id
696 and ppa.payroll_action_id=p_payroll_action_id
697 and (EXISTS (select 1
698 from per_people_extra_info pei
699 where pei.person_id = p_person_id
700 and pei.information_type = 'NL_FIRST_DAY_REPORT'
701 and pei.pei_information2 = 'Y')
702 or NOT EXISTS
703 (select 1
704 from per_people_extra_info pei
705 where pei.person_id = p_person_id
706 and pei.information_type = 'NL_FIRST_DAY_REPORT'));
707
708
709 CURSOR csr_get_asg_org (p_org_id NUMBER
710 ,p_org_struct_id NUMBER
711 ,p_start_person_id NUMBER
712 ,p_end_person_id NUMBER
713 ,p_payroll_action_id NUMBER
714 ,p_report_date VARCHAR2) is
715
716 select distinct paa.assignment_id assignment_id
717
718 from per_all_assignments_f paa,
719 per_assignment_status_types past,
720 per_all_people_f pap,
721 pay_payroll_actions ppa,
722 per_periods_of_service ppos,
723 per_org_structure_versions posv
724
725 where posv.organization_structure_id=p_org_struct_id
726 and to_date(p_report_date,'RRRR/MM/DD') between posv.date_from and nvl(posv.date_to,hr_general.end_of_time)
727 and (paa.organization_id in
728 ((SELECT pose.organization_id_child
729 FROM per_org_structure_elements pose
730 WHERE pose.org_structure_version_id = posv.org_structure_version_id
731 START with pose.organization_id_parent = p_org_id
732 CONNECT BY prior organization_id_child = organization_id_parent)
733 UNION
734 (select p_org_id from dual))
735 OR
736 nvl(paa.establishment_id,-1) = p_org_id
737 )
738 and paa.person_id = pap.person_id
739 and paa.primary_flag = 'Y'
740 and past.assignment_status_type_id = paa.assignment_status_type_id
741 and past.per_system_status = 'ACTIVE_ASSIGN'
742 and get_ref_date(pap.person_id) between paa.effective_start_date and paa.effective_end_date
743 and pap.person_id between p_start_person_id and p_end_person_id
744 and pap.business_group_id = ppa.business_group_id
745 and ppa.payroll_action_id = p_payroll_action_id
746 and pap.effective_start_date >= to_date('04-07-2006','DD-MM-RRRR')
747 and ppos.business_group_id = pap.business_group_id
748 and ppos.person_id = pap.person_id
749 and ppos.date_start =
750 (select max(ppos1.date_start)
751 from per_periods_of_service ppos1
752 where ppos1.person_id = ppos.person_id)
753 and ppos.date_start >= to_date('04-07-2006','DD-MM-RRRR')
754 and (EXISTS (select 1
755 from per_people_extra_info pei
756 where pei.person_id = pap.person_id
757 and pei.information_type = 'NL_FIRST_DAY_REPORT'
758 and pei.pei_information2 = 'Y')
759 or NOT EXISTS
760 (select 1
761 from per_people_extra_info pei
762 where pei.person_id = pap.person_id
763 and pei.information_type = 'NL_FIRST_DAY_REPORT'));
764
765
766
767 CURSOR csr_get_asg_hier (p_org_struct_id NUMBER
768 ,p_report_date VARCHAR2
769 ,p_start_person_id NUMBER
770 ,p_end_person_id NUMBER
771 ,p_payroll_action_id NUMBER) is
772
773 select distinct paa.assignment_id assignment_id
774
775 from per_all_assignments_f paa,
776 per_assignment_status_types past,
777 per_all_people_f pap,
778 pay_payroll_actions ppa,
779 per_periods_of_service ppos,
780 per_org_structure_versions posv
781
782 where posv.organization_structure_id=p_org_struct_id
783 and to_date(p_report_date,'RRRR/MM/DD') between posv.date_from and nvl(posv.date_to,hr_general.end_of_time)
784 and pap.person_id between p_start_person_id and p_end_person_id
785 and pap.business_group_id = ppa.business_group_id
786 and ppa.payroll_action_id = p_payroll_action_id
787 and pap.effective_start_date >= to_date('04-07-2006','DD-MM-RRRR')
788 and ppos.business_group_id = pap.business_group_id
789 and ppos.person_id = pap.person_id
790 and ppos.date_start =
791 (select max(ppos1.date_start)
792 from per_periods_of_service ppos1
793 where ppos1.person_id = ppos.person_id)
794 and ppos.date_start >= to_date('04-07-2006','DD-MM-RRRR')
795 and (EXISTS (select 1
796 from per_people_extra_info pei
797 where pei.person_id = pap.person_id
798 and pei.information_type = 'NL_FIRST_DAY_REPORT'
799 and pei.pei_information2 = 'Y')
800 or NOT EXISTS
801 (select 1
802 from per_people_extra_info pei
803 where pei.person_id = pap.person_id
804 and pei.information_type = 'NL_FIRST_DAY_REPORT'))
805 and pap.person_id = paa.person_id
806 and paa.primary_flag = 'Y'
807 and past.assignment_status_type_id = paa.assignment_status_type_id
808 and past.per_system_status = 'ACTIVE_ASSIGN'
809 and get_ref_date(pap.person_id) between paa.effective_start_date and paa.effective_end_date
810 and (hr_nl_org_info.get_tax_org_id(posv.org_structure_version_id,paa.organization_id) is not null
811 OR
812 per_nl_fdr_archive.org_check(pap.business_group_id, null, nvl(paa.establishment_id,-1), to_date(p_report_date,'RRRR/MM/DD')) = 1)
813 and paa.organization_id in
814 (select pose.organization_id_parent
815 from per_org_structure_elements pose
816 where posv.org_structure_version_id = pose.org_structure_version_id
817 UNION
818 select pose.organization_id_child
819 from per_org_structure_elements pose
820 where posv.org_structure_version_id = pose.org_structure_version_id);
821
822
823 l_report_date VARCHAR2(100);
824 l_org_struct_id NUMBER;
825 l_person_id NUMBER;
826 l_org_id NUMBER;
827 l_bg_id NUMBER;
828 l_asg_act_id NUMBER;
829
830
831 BEGIN
832
833 l_org_struct_id := NULL;
834 l_person_id := NULL;
835 l_org_id := NULL;
836
837 hr_utility.set_location('Entering assg action Code',20);
838
839 PER_NL_FDR_ARCHIVE.get_all_parameters(p_payroll_action_id, l_report_date, l_org_struct_id, l_person_id, l_org_id, l_bg_id);
840
841 --hr_utility.set_location('Parameters:- date: '||l_report_date||' hier: '||l_org_struct_id||' org: '||l_org_id,30);
842
843 IF l_person_id is not NULL THEN
844
845 --hr_utility.set_location('Person selected',40);
846
847 FOR v_csr_get_asg_person in csr_get_asg_person(l_person_id, p_start_person_id, p_end_person_id, p_payroll_action_id)
848
849 LOOP
850
851 SELECT pay_assignment_actions_s.NEXTVAL
852 INTO l_asg_act_id
853 FROM dual;
854
855 hr_nonrun_asact.insact(l_asg_act_id,v_csr_get_asg_person.assignment_id, p_payroll_action_id,p_chunk,NULL);
856
857 --hr_utility.set_location('Assignment id - '||to_char(v_csr_get_asg_person.assignment_id)||' selected',45);
858
859 END LOOP;
860
861 ELSIF l_org_id is not NULL THEN
862
863 --hr_utility.set_location('Org selected',50);
864
865 FOR v_csr_get_asg_org in csr_get_asg_org(l_org_id, l_org_struct_id, p_start_person_id, p_end_person_id, p_payroll_action_id, l_report_date)
866
867 LOOP
868
869 --hr_utility.set_location('Selecting assignment '||to_char(v_csr_get_asg_org.assignment_id),55);
870
871 SELECT pay_assignment_actions_s.NEXTVAL
872 INTO l_asg_act_id
873 FROM dual;
874
875 hr_nonrun_asact.insact(l_asg_act_id,v_csr_get_asg_org.assignment_id, p_payroll_action_id,p_chunk,NULL);
876
877 --hr_utility.set_location('Assignment id - '||to_char(v_csr_get_asg_org.assignment_id)||' selected',57);
878
879 END LOOP;
880
881
882 ELSIF l_org_struct_id is not NULL THEN
883
884 --hr_utility.set_location('Hier selected',60);
885 --hr_utility.set_location('Hier - '||to_char(l_org_struct_id)||' Pactid - '||p_payroll_action_id||' rep date - '||l_report_date||' start - '||p_start_person_id||' end - '||p_end_person_id,61);
886
887 FOR v_csr_get_asg_hier in csr_get_asg_hier(l_org_struct_id, l_report_date, p_start_person_id, p_end_person_id, p_payroll_action_id)
888
889 LOOP
890
891 --hr_utility.set_location('Assignment id - '||to_char(v_csr_get_asg_hier.assignment_id)||' selected before inserting', 62);
892
893 SELECT pay_assignment_actions_s.NEXTVAL
894 INTO l_asg_act_id
895 FROM dual;
896
897 hr_nonrun_asact.insact(l_asg_act_id,v_csr_get_asg_hier.assignment_id, p_payroll_action_id,p_chunk,NULL);
898
899 --hr_utility.set_location('Assignment id - '||to_char(v_csr_get_asg_hier.assignment_id)||' inserted', 65);
900
901 END LOOP;
902
903 END IF;
904
905
906 END ASSIGNMENT_ACTION_CODE;
907
908
909
910
911 /*-------------------------------------------------------------------------------
912 |Name : ARCHIVE_CODE |
913 |Type : Procedure |
914 |Description : Archival code |
915 -------------------------------------------------------------------------------*/
916
917
918 PROCEDURE ARCHIVE_CODE (p_assignment_action_id IN NUMBER
919 ,p_effective_date IN DATE) IS
920
921
922 CURSOR csr_get_org_info(p_assignment_action_id IN NUMBER,
923 p_org_struct_id IN NUMBER,
924 p_report_date IN VARCHAR2) is
925
926 select hou.organization_id org_id,
927 hou.name org_name,
928 hoi.org_information4 tax_reg
929
930 from pay_assignment_actions paa,
931 per_all_assignments_f pas,
932 per_assignment_status_types past,
933 hr_organization_units hou,
934 hr_organization_information hoi,
935 per_org_structure_versions posv
936
937 where posv.organization_structure_id=p_org_struct_id
938 and to_date(p_report_date,'RRRR/MM/DD') between posv.date_from and nvl(posv.date_to,hr_general.end_of_time)
939 and paa.assignment_action_id = p_assignment_action_id
940 and pas.assignment_id = paa.assignment_id
941 and past.assignment_status_type_id = pas.assignment_status_type_id
942 and past.per_system_status = 'ACTIVE_ASSIGN'
943 and get_ref_date(pas.person_id) between pas.effective_start_date and pas.effective_end_date
944 and hou.organization_id = hr_nl_org_info.get_tax_org_id(posv.org_structure_version_id, pas.organization_id)
945 and hoi.organization_id = hou.organization_id
946 and hoi.org_information_context = 'NL_ORG_INFORMATION';
947
948
949 CURSOR csr_get_leg_info(p_assignment_action_id IN NUMBER) is
950
951 select hou.organization_id org_id,
952 hou.name org_name,
953 hoi.org_information1 tax_reg
954
955 from pay_assignment_actions paa,
956 per_all_assignments_f pas,
957 per_assignment_status_types past,
958 hr_organization_units hou,
959 hr_organization_information hoi
960
961 where paa.assignment_action_id = p_assignment_action_id
962 and pas.assignment_id = paa.assignment_id
963 and past.assignment_status_type_id = pas.assignment_status_type_id
964 and past.per_system_status = 'ACTIVE_ASSIGN'
965 and get_ref_date(pas.person_id) between pas.effective_start_date and pas.effective_end_date
966 and hou.organization_id = pas.establishment_id
967 and hoi.organization_id = hou.organization_id
968 and hoi.org_information_context = 'NL_LE_TAX_DETAILS';
969
970
971
972 CURSOR csr_get_person_info(p_assignment_action_id IN NUMBER) is
973
974 select ppos.date_start hire_date,
975 pap.employee_number employee_number,
976 pap.national_identifier sofi_number,
977 pap.per_information1 init,
978 pap.pre_name_adjunct prefix,
979 pap.last_name last_name,
980 pap.full_name full_name,
981 pap.date_of_birth date_of_birth,
982 pap.person_id person_id,
983 pap.business_group_id bg_id,
984 pas.establishment_id establishment_id
985
986 from per_all_people_f pap,
987 per_all_assignments_f pas,
988 per_assignment_status_types past,
989 pay_assignment_actions paa,
990 per_periods_of_service ppos
991
992 where paa.assignment_action_id = p_assignment_action_id
993 and pas.assignment_id = paa.assignment_id
994 and pap.person_id = pas.person_id
995 and pas.primary_flag = 'Y'
996 and past.assignment_status_type_id = pas.assignment_status_type_id
997 and past.per_system_status = 'ACTIVE_ASSIGN'
998 and get_ref_date(pap.person_id) between pas.effective_start_date and pas.effective_end_date
999 and pap.effective_start_date =
1000 (select max(pap1.effective_start_date)
1001 from per_all_people_f pap1
1002 where pap.person_id = pap1.person_id)
1003 and ppos.person_id = pap.person_id
1004 and ppos.date_start =
1005 (select max(date_start)
1006 from per_periods_of_service ppos1
1007 where ppos1.person_id = pap.person_id);
1008
1009
1010 CURSOR csr_get_peit(p_person_id NUMBER) IS
1011 select ppei.person_extra_info_id person_extra_info_id,
1012 ppei.object_version_number object_version_number
1013 from per_people_extra_info ppei
1014 where ppei.person_id = p_person_id
1015 and ppei.information_type = 'NL_FIRST_DAY_REPORT'
1016 and ppei.pei_information2 = 'Y';
1017
1018 v_csr_get_peit csr_get_peit%rowtype;
1019
1020 l_report_date VARCHAR2(100);
1021 l_bg_name per_business_groups.NAME%TYPE;
1022 l_bg_id NUMBER;
1023 l_msg_id VARCHAR2(100);
1024 l_tax_reg_num VARCHAR2(100);
1025 l_hire_date DATE;
1026 l_employee_number per_all_people_f.employee_number%TYPE;
1027 l_sofi_number VARCHAR2(30) := null;
1028 l_initial VARCHAR2(150);
1029 l_prefix VARCHAR2(30);
1030 l_last_name VARCHAR2(150);
1031 l_full_name VARCHAR2(150);
1032 l_date_of_birth DATE;
1033 l_person_id NUMBER;
1034 l_establishment_id NUMBER;
1035 l_org_id NUMBER;
1036 l_org_struct_id NUMBER;
1037 l_org_name VARCHAR2(150);
1038 l_file UTL_FILE.FILE_TYPE;
1039 l_directory_path VARCHAR2(500);
1040 l_file_name VARCHAR2(50);
1041 vCtr NUMBER := 0;
1042 l_xfdf_clob CLOB;
1043 l_action_info_id NUMBER;
1044 l_ovn NUMBER;
1045 l_create_peit VARCHAR2(1);
1046 l_parameter_string pay_payroll_actions.legislative_parameters%type;
1047
1048
1049
1050 BEGIN
1051
1052 --hr_utility.trace_on(NULL,'NL_FDR');
1053 --hr_utility.set_location('Entered archive code', 90);
1054 l_create_peit := 'Y';
1055
1056 SELECT PER_NL_FDR_ARCHIVE.get_parameter(ppa.legislative_parameters,'REPORT_DATE')
1057 INTO l_report_date
1058 FROM pay_payroll_actions ppa,
1059 pay_assignment_actions paa
1060 WHERE paa.payroll_action_id = ppa.payroll_action_id
1061 AND paa.assignment_action_id = p_assignment_action_id;
1062
1063 SELECT ppa.legislative_parameters
1064 INTO l_parameter_string
1065 FROM pay_payroll_actions ppa,
1066 pay_assignment_actions paa
1067 WHERE paa.payroll_action_id = ppa.payroll_action_id
1068 AND paa.assignment_action_id = p_assignment_action_id;
1069
1070
1071 IF instr(l_parameter_string,'/',1,2) - instr(l_parameter_string,'/',1,1) = 3 THEN
1072 l_report_date := substr(l_parameter_string,instr(l_parameter_string,'/',1,1)-4,10);
1073 END IF;
1074 g_year := to_char(to_date(l_report_date,'RRRR/MM/DD'),'RRRR');
1075
1076 select TO_NUMBER(PER_NL_FDR_ARCHIVE.get_parameter(ppa.legislative_parameters,'ORG_STRUCT_ID'))
1077 INTO l_org_struct_id
1078 FROM pay_payroll_actions ppa,
1079 pay_assignment_actions paa
1080 WHERE paa.payroll_action_id = ppa.payroll_action_id
1081 AND paa.assignment_action_id = p_assignment_action_id;
1082
1083
1084 SELECT value INTO l_directory_path
1085 FROM v$parameter WHERE LOWER(name)='utl_file_dir';
1086
1087 IF INSTR(l_directory_path,',') > 0 THEN
1088 l_directory_path := SUBSTR(l_directory_path, 1, INSTR(l_directory_path,',')-1);
1089 END IF;
1090
1091
1092 OPEN csr_get_person_info(p_assignment_action_id);
1093 FETCH csr_get_person_info into l_hire_date, l_employee_number, l_sofi_number, l_initial, l_prefix, l_last_name, l_full_name, l_date_of_birth, l_person_id, l_bg_id, l_establishment_id;
1094 CLOSE csr_get_person_info;
1095
1096 --hr_utility.set_location('Processing employee number: '||l_employee_number||' with leg emp '||l_establishment_id,95);
1097
1098 IF l_establishment_id is not NULL and per_nl_fdr_archive.org_check(l_bg_id,null,l_establishment_id,to_date(l_report_date,'RRRR/MM/DD')) = 1 THEN
1099
1100 --hr_utility.set_location('Entered IF condition for legal employer',97);
1101 OPEN csr_get_leg_info(p_assignment_action_id);
1102 FETCH csr_get_leg_info INTO l_org_id, l_org_name, l_tax_reg_num;
1103 CLOSE csr_get_leg_info;
1104
1105 ELSE
1106
1107 --hr_utility.set_location('Entered else condition for HR org',99);
1108 OPEN csr_get_org_info(p_assignment_action_id, l_org_struct_id, l_report_date);
1109 FETCH csr_get_org_info INTO l_org_id, l_org_name, l_tax_reg_num;
1110 CLOSE csr_get_org_info;
1111
1112
1113 END IF;
1114
1115
1116 --hr_utility.set_location('Employee number - '||l_employee_number||' selected', 100);
1117
1118
1119 vXMLTable(vCtr).TagName := 'Bericht';
1120 vXMLTable(vCtr).TagValue := null;
1121 vCtr := vCtr + 1;
1122
1123 vXMLTable(vCtr).TagName := 'IdBer';
1124 vXMLTable(vCtr).TagValue := replace(l_last_name,'&','&'||'amp;')||'_'||l_employee_number;
1125 vCtr := vCtr + 1;
1126
1127 vXMLTable(vCtr).TagName := 'Bericht';
1128 vXMLTable(vCtr).TagValue := 'END';
1129 vCtr := vCtr + 1;
1130
1131 vXMLTable(vCtr).TagName := 'AdministratieveEenheid';
1132 vXMLTable(vCtr).TagValue := null;
1133 vCtr := vCtr + 1;
1134
1135 vXMLTable(vCtr).TagName := 'LhNr';
1136 vXMLTable(vCtr).TagValue := to_char(l_tax_reg_num);
1137 vCtr := vCtr + 1;
1138
1139 vXMLTable(vCtr).TagName := 'Dienstbetrekking';
1140 vXMLTable(vCtr).TagValue := null;
1141 vCtr := vCtr + 1;
1142
1143 vXMLTable(vCtr).TagName := 'DatAanvWz';
1144 vXMLTable(vCtr).TagValue := to_char(l_hire_date,'RRRR-MM-DD');
1145 vCtr := vCtr + 1;
1146
1147 vXMLTable(vCtr).TagName := 'NatuurlijkPersoon';
1148 vXMLTable(vCtr).TagValue := null;
1149 vCtr := vCtr + 1;
1150
1151 IF l_sofi_number is not null THEN
1152
1153 vXMLTable(vCtr).TagName := 'SofiNr';
1154 vXMLTable(vCtr).TagValue := l_sofi_number;
1155 vCtr := vCtr + 1;
1156
1157 ELSE
1158
1159 vXMLTable(vCtr).TagName := 'PersNr';
1160 vXMLTable(vCtr).TagValue := l_employee_number;
1161 vCtr := vCtr + 1;
1162
1163 END IF;
1164
1165 vXMLTable(vCtr).TagName := 'Voorl';
1166 vXMLTable(vCtr).TagValue := upper(replace(replace(l_initial,'&','&'||'amp;'),'.'));
1167 vCtr := vCtr + 1;
1168
1169 vXMLTable(vCtr).TagName := 'Voorv';
1170 vXMLTable(vCtr).TagValue := replace(l_prefix,'&','&'||'amp;');
1171 vCtr := vCtr + 1;
1172
1173 vXMLTable(vCtr).TagName := 'SignNm';
1174 vXMLTable(vCtr).TagValue := replace(l_last_name,'&','&'||'amp;');
1175 vCtr := vCtr + 1;
1176
1177 vXMLTable(vCtr).TagName := 'Gebdat';
1178 vXMLTable(vCtr).TagValue := to_char(l_date_of_birth,'RRRR-MM-DD');
1179 vCtr := vCtr + 1;
1180
1181 vXMLTable(vCtr).TagName := 'NatuurlijkPersoon';
1182 vXMLTable(vCtr).TagValue := 'END';
1183 vCtr := vCtr + 1;
1184
1185 vXMLTable(vCtr).TagName := 'Dienstbetrekking';
1186 vXMLTable(vCtr).TagValue := 'END';
1187 vCtr := vCtr + 1;
1188
1189 vXMLTable(vCtr).TagName := 'AdministratieveEenheid';
1190 vXMLTable(vCtr).TagValue := 'END';
1191 vCtr := vCtr + 1;
1192
1193 WritetoCLOB_rtf(l_xfdf_clob, vXMLTable);
1194
1195 l_file_name := l_employee_number||'_'||to_char(l_tax_reg_num)||'_'||substr(to_char(to_date(l_report_date,'RRRR/MM/DD HH24:MI:SS'),'DD-MM-RR'),1,2)||
1196 substr(to_char(to_date(l_report_date,'RRRR/MM/DD HH24:MI:SS'),'DD-MM-RR'),4,2)||substr(to_char(to_date(l_report_date,'RRRR/MM/DD HH24:MI:SS'),'DD-MM-RR'),7,2)||'.xml';
1197 l_file := utl_file.fopen(l_directory_path, l_file_name, 'W');
1198 utl_file.put_line(l_file,substr(l_xfdf_clob,1,instr(l_xfdf_clob,'<NatuurlijkPersoon>')-1));
1199 utl_file.put_line(l_file,substr(l_xfdf_clob,instr(l_xfdf_clob,'<NatuurlijkPersoon>'),length(l_xfdf_clob)-instr(l_xfdf_clob,'<NatuurlijkPersoon>')+1));
1200 utl_file.fclose(l_file);
1201
1202
1203 pay_action_information_api.create_action_information
1204 ( p_action_information_id => l_action_info_id
1205 ,p_action_context_id => p_assignment_action_id
1206 ,p_action_context_type => 'AAP'
1207 ,p_object_version_number => l_ovn
1208 ,p_effective_date => p_effective_date
1209 ,p_source_id => NULL
1210 ,p_source_text => NULL
1211 ,p_action_information_category => 'NL FDR EMPLOYEE DETAILS'
1212 ,p_action_information1 => nvl(l_employee_number,' ')
1213 ,p_action_information2 => nvl(l_full_name,' ')
1214 ,p_action_information3 => nvl(l_sofi_number,' ')
1215 ,p_action_information4 => nvl(l_org_name,' ')
1216 ,p_action_information5 => nvl(l_tax_reg_num,' ')
1217 ,p_action_information6 => nvl(fnd_date.date_to_displaydate(l_hire_date),' ')
1218 ,p_action_information7 => nvl(fnd_date.date_to_displaydate(l_date_of_birth),' '));
1219
1220
1221 FOR v_csr_get_peit IN csr_get_peit(l_person_id)
1222 LOOP
1223
1224 l_create_peit := 'N';
1225 hr_person_extra_info_api.update_person_extra_info
1226 (p_person_extra_info_id=>v_csr_get_peit.person_extra_info_id
1227 ,p_object_version_number=>v_csr_get_peit.object_version_number
1228 ,p_pei_information1=>to_char(to_date(l_report_date, 'RRRR/MM/DD HH24:MI:SS'),'RRRR/MM/DD HH24:MI:SS')
1229 ,p_pei_information2=>'N');
1230
1231 END LOOP;
1232
1233 IF l_create_peit = 'Y' THEN
1234
1235 hr_person_extra_info_api.create_person_extra_info
1236 (p_person_id=>l_person_id
1237 ,p_information_type=>'NL_FIRST_DAY_REPORT'
1238 ,p_pei_information_category=>'NL_FIRST_DAY_REPORT'
1239 ,p_pei_information1=>to_char(to_date(l_report_date, 'RRRR/MM/DD HH24:MI:SS'),'RRRR/MM/DD HH24:MI:SS')
1240 ,p_pei_information2=>'N'
1241 ,p_person_extra_info_id=>v_csr_get_peit.person_extra_info_id
1242 ,p_object_version_number=>v_csr_get_peit.object_version_number);
1243
1244 END IF;
1245
1246
1247 END ARCHIVE_CODE;
1248
1249
1250
1251 /*-------------------------------------------------------------------------------
1252 |Name : ARCHIVE_DEINIT_CODE |
1253 |Type : Procedure |
1254 |Description : Deinitialization code |
1255 -------------------------------------------------------------------------------*/
1256
1257
1258 PROCEDURE archive_deinit_code(p_actid IN NUMBER) IS
1259
1260 CURSOR csr_get_action_information IS
1261
1262 select pai.action_information_id act_id,
1263 pai.object_version_number ovn
1264
1265 from pay_action_information pai,
1266 pay_assignment_actions paa
1267
1268 where paa.payroll_action_id = p_actid
1269 and pai.action_context_id = paa.assignment_action_id
1270 and pai.action_information_category = 'NL FDR EMPLOYEE DETAILS';
1271
1272
1273 CURSOR csr_get_employer IS
1274
1275 select distinct pai.action_information4 org_name,
1276 pai.action_information5 tax_reg_num
1277
1278 from pay_action_information pai,
1279 pay_assignment_actions paa
1280
1281 where paa.payroll_action_id = p_actid
1282 and pai.action_context_id = paa.assignment_action_id
1283 and pai.action_information_category = 'NL FDR EMPLOYEE DETAILS';
1284
1285
1286 CURSOR csr_get_employee_details(p_org_name VARCHAR2) IS
1287
1288 select pai.action_information1 employee_number,
1289 pai.action_information2 full_name,
1290 pai.action_information3 sofi_number,
1291 pai.action_information6 hire_date,
1292 pai.action_information7 date_of_birth
1293
1294 from pay_action_information pai,
1295 pay_assignment_actions paa
1296
1297 where paa.payroll_action_id = p_actid
1298 and pai.action_context_id = paa.assignment_action_id
1299 and pai.action_information_category = 'NL FDR EMPLOYEE DETAILS'
1300 and pai.action_information4 = p_org_name;
1301
1302
1303 CURSOR csr_get_per_without_employer (p_org_struct_id NUMBER
1304 ,p_report_date VARCHAR2
1305 ,p_payroll_action_id NUMBER) is
1306
1307 select distinct pap.employee_number emp_no,
1308 pap.full_name name
1309
1310 from per_all_assignments_f paa,
1311 per_assignment_status_types past,
1312 per_all_people_f pap,
1313 pay_payroll_actions ppa,
1314 per_periods_of_service ppos,
1315 per_org_structure_versions posv
1316
1317 where posv.organization_structure_id=p_org_struct_id
1318 and to_date(p_report_date,'RRRR/MM/DD') between posv.date_from and nvl(posv.date_to,hr_general.end_of_time)
1319 and pap.business_group_id = ppa.business_group_id
1320 and ppa.payroll_action_id = p_payroll_action_id
1321 and pap.effective_start_date >= to_date('04-07-2006','DD-MM-RRRR')
1322 and ppos.business_group_id = pap.business_group_id
1323 and ppos.person_id = pap.person_id
1324 and ppos.date_start =
1325 (select max(ppos1.date_start)
1326 from per_periods_of_service ppos1
1327 where ppos1.person_id = ppos.person_id)
1328 and ppos.date_start >= to_date('04-07-2006','DD-MM-RRRR')
1329 and (EXISTS (select 1
1330 from per_people_extra_info pei
1331 where pei.person_id = pap.person_id
1332 and pei.information_type = 'NL_FIRST_DAY_REPORT'
1333 and pei.pei_information2 = 'Y')
1334 or NOT EXISTS
1335 (select 1
1336 from per_people_extra_info pei
1337 where pei.person_id = pap.person_id
1338 and pei.information_type = 'NL_FIRST_DAY_REPORT'))
1339 and pap.person_id = paa.person_id
1340 and paa.primary_flag = 'Y'
1341 and past.assignment_status_type_id = paa.assignment_status_type_id
1342 and past.per_system_status = 'ACTIVE_ASSIGN'
1343 and get_ref_date(pap.person_id) between paa.effective_start_date and paa.effective_end_date
1344 and hr_nl_org_info.get_tax_org_id(posv.org_structure_version_id,paa.organization_id) is null
1345 and per_nl_fdr_archive.org_check(pap.business_group_id, null, nvl(paa.establishment_id,-1), to_date(p_report_date,'RRRR/MM/DD')) = 0
1346 and paa.organization_id in
1347 (select pose.organization_id_parent
1348 from per_org_structure_elements pose
1349 where posv.org_structure_version_id = pose.org_structure_version_id
1350 UNION
1351 select pose.organization_id_child
1352 from per_org_structure_elements pose
1353 where posv.org_structure_version_id = pose.org_structure_version_id);
1354
1355
1356 l_bg_name VARCHAR2(100);
1357 l_report_date VARCHAR2(100);
1358 l_head_length NUMBER;
1359 l_org_struct_id NUMBER;
1360 l_person_id NUMBER;
1361 l_org_id NUMBER;
1362 l_bg_id NUMBER;
1363 v_csr_get_employer csr_get_employer%rowtype;
1364 v_csr_get_employee_details csr_get_employee_details%rowtype;
1365 v_csr_get_action_information csr_get_action_information%rowtype;
1366 v_csr_get_per_without_employer csr_get_per_without_employer%rowtype;
1367
1368
1369
1370 BEGIN
1371
1372 PER_NL_FDR_ARCHIVE.get_all_parameters(p_actid, l_report_date, l_org_struct_id, l_person_id, l_org_id, l_bg_id);
1373
1374 SELECT pbg.name
1375 INTO l_bg_name
1376 FROM per_business_groups pbg
1377 WHERE pbg.business_group_id = l_bg_id;
1378
1379 IF l_person_id is NULL and l_org_id is NULL and l_org_struct_id is not NULL THEN
1380
1381 FOR v_csr_get_per_without_employer IN csr_get_per_without_employer(l_org_struct_id,l_report_date, p_actid)
1382 LOOP
1383
1384 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error: Employee number - '||v_csr_get_per_without_employer.emp_no||', '||v_csr_get_per_without_employer.name||' is not attached to an employer with tax details.');
1385
1386 END LOOP;
1387
1388 END IF;
1389
1390 l_head_length := length(hr_general.decode_lookup('NL_FDR_LABELS','REP_HEAD'));
1391
1392 FND_FILE.PUT(FND_FILE.OUTPUT, lpad(hr_general.decode_lookup('NL_FDR_LABELS','REP_HEAD'),((123-l_head_length)/2)+l_head_length,' '));
1393 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1394 FND_FILE.PUT(FND_FILE.OUTPUT, lpad(rpad(' ',l_head_length+1,'-'),((123-l_head_length)/2)+l_head_length,' '));
1395 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 3);
1396 FND_FILE.PUT(FND_FILE.OUTPUT, rpad(hr_general.decode_lookup('NL_FDR_LABELS','BG_NAME'),30,' '));
1397 FND_FILE.PUT(FND_FILE.OUTPUT, ': ');
1398 FND_FILE.PUT(FND_FILE.OUTPUT, l_bg_name);
1399 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1400 FND_FILE.PUT(FND_FILE.OUTPUT, rpad(hr_general.decode_lookup('NL_FDR_LABELS','REP_DATE'),30,' '));
1401 FND_FILE.PUT(FND_FILE.OUTPUT, ': ');
1402 FND_FILE.PUT(FND_FILE.OUTPUT, fnd_date.date_to_displaydate(to_date(l_report_date, 'RRRR/MM/DD HH24:MI:SS')));
1403 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 6);
1404
1405
1406
1407 FOR v_csr_get_employer IN csr_get_employer
1408 LOOP
1409
1410 IF hr_ni_chk_pkg.chk_nat_id_format(v_csr_get_employer.tax_reg_num,'DDDDDDDDDADD') <> upper(v_csr_get_employer.tax_reg_num) OR substr(v_csr_get_employer.tax_reg_num,10,1) <> 'L' THEN
1411
1412 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error: The Tax Registration Number for '||
1413 v_csr_get_employer.org_name||' has an incorrect format. It should be nnnnnnnnnLnn. Please correct.');
1414
1415 END IF;
1416
1417
1418 FND_FILE.PUT(FND_FILE.OUTPUT, rpad(hr_general.decode_lookup('NL_FDR_LABELS','EMPLOYER'),30,' '));
1419 FND_FILE.PUT(FND_FILE.OUTPUT, ': ');
1420 FND_FILE.PUT(FND_FILE.OUTPUT, v_csr_get_employer.org_name);
1421 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1422 FND_FILE.PUT(FND_FILE.OUTPUT, rpad(hr_general.decode_lookup('NL_FDR_LABELS','TAX_REG'),30,' '));
1423 FND_FILE.PUT(FND_FILE.OUTPUT, ': ');
1424 FND_FILE.PUT(FND_FILE.OUTPUT, v_csr_get_employer.tax_reg_num);
1425 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 2);
1426
1427 FND_FILE.PUT(FND_FILE.OUTPUT, lpad(' ',123,'-'));
1428 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1429 FND_FILE.PUT(FND_FILE.OUTPUT, rpad(hr_general.decode_lookup('NL_FDR_LABELS','EMP_NO'), 21, ' '));
1430 FND_FILE.PUT(FND_FILE.OUTPUT, rpad(hr_general.decode_lookup('NL_FDR_LABELS','EMP_NAME'), 40, ' '));
1431 FND_FILE.PUT(FND_FILE.OUTPUT, rpad(hr_general.decode_lookup('NL_FDR_LABELS','SOFI_NO'), 20, ' '));
1432 --FND_FILE.PUT(FND_FILE.OUTPUT, rpad(hr_general.decode_lookup('NL_FDR_LABELS','TAX_REG'), 30, ' '));
1433 FND_FILE.PUT(FND_FILE.OUTPUT, rpad(hr_general.decode_lookup('NL_FDR_LABELS','HIRE_DATE'), 21, ' '));
1434 FND_FILE.PUT(FND_FILE.OUTPUT, rpad(hr_general.decode_lookup('NL_FDR_LABELS','BIRTH_DATE'), 21, ' '));
1435 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1436 FND_FILE.PUT(FND_FILE.OUTPUT, lpad(' ',123,'-'));
1437 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1438
1439
1440 FOR v_csr_get_employee_details IN csr_get_employee_details(v_csr_get_employer.org_name)
1441 LOOP
1442
1443 IF v_csr_get_employee_details.date_of_birth = ' ' THEN
1444
1445 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error: Employee number - '||v_csr_get_employee_details.employee_number||', '||
1446 v_csr_get_employee_details.full_name||' has no Date of Birth entered.');
1447
1448 END IF;
1449
1450 FND_FILE.PUT(FND_FILE.OUTPUT, rpad(v_csr_get_employee_details.employee_number, 21, ' '));
1451 FND_FILE.PUT(FND_FILE.OUTPUT, rpad(v_csr_get_employee_details.full_name, 40, ' '));
1452 FND_FILE.PUT(FND_FILE.OUTPUT, rpad(v_csr_get_employee_details.sofi_number, 20, ' '));
1453 --FND_FILE.PUT(FND_FILE.OUTPUT, rpad(v_csr_get_employer.tax_reg_num, 30, ' '));
1454 FND_FILE.PUT(FND_FILE.OUTPUT, rpad(v_csr_get_employee_details.hire_date, 21, ' '));
1455 FND_FILE.PUT(FND_FILE.OUTPUT, rpad(v_csr_get_employee_details.date_of_birth, 21, ' '));
1456 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 1);
1457
1458 END LOOP;
1459
1460 FND_FILE.NEW_LINE(FND_FILE.OUTPUT, 3);
1461
1462 END LOOP;
1463
1464 --Deleting the data from pay_action_information table
1465
1466 FOR v_csr_get_action_information in csr_get_action_information
1467 LOOP
1468
1469 pay_action_information_api.delete_action_information (p_action_information_id => v_csr_get_action_information.act_id
1470 ,p_object_version_number => v_csr_get_action_information.ovn);
1471
1472 END LOOP;
1473
1474 --Deleting assignment actions that have been created - for bug 5446716
1475
1476 delete from pay_assignment_actions
1477 where payroll_action_id = p_actid;
1478
1479
1480 END archive_deinit_code;
1481
1482
1483 END PER_NL_FDR_ARCHIVE;