[Home] [Help]
PACKAGE BODY: APPS.PAY_NL_ABP_REPORT
Source
1 PACKAGE BODY PAY_NL_ABP_REPORT as
2 /* $Header: pynlabprep.pkb 120.0.12020000.15 2013/03/27 13:54:13 mkuppuch noship $ */
3
4 EOL VARCHAR2(5) := FND_GLOBAL.NEWLINE();
5 g_SOURCE VARCHAR2(4000);
6 g_TARGET VARCHAR2(4000);
7
8 FUNCTION get_lookup_value(p_lookup_type IN VARCHAR2
9 ,p_lookup_code IN VARCHAR2
10 ,p_language IN VARCHAR2 DEFAULT 'US')
11 RETURN VARCHAR2 IS
12
13 CURSOR c_get_lookup_value (c_lookup_type IN VARCHAR2
14 ,c_lookup_code IN VARCHAR2
15 ,c_language IN VARCHAR2) IS
16 SELECT meaning
17 FROM fnd_lookup_values
18 WHERE lookup_type = c_lookup_type
19 AND language = c_language
20 AND lookup_code = c_lookup_code;
21
22 BEGIN
23 FOR r_get_lookup_value in c_get_lookup_value(p_lookup_type,p_lookup_code,p_language)
24 LOOP
25 RETURN r_get_lookup_value.meaning;
26 END LOOP;
27 RETURN NULL;
28
29 EXCEPTION
30 WHEN TOO_MANY_ROWS THEN
31 RETURN NULL;
32 WHEN NO_DATA_FOUND THEN
33 RETURN NULL;
34 WHEN OTHERS THEN
35 hr_utility.trace('Exception in get_lookup_value SQL-ERRM : '||SQLERRM);
36
37 END get_lookup_value;
38 -------------------------------------------------------------------------------
39 -- get_IANA_charset
40 -------------------------------------------------------------------------------
41 FUNCTION get_IANA_charset RETURN VARCHAR2 IS
42 CURSOR csr_get_iana_charset IS
43 SELECT tag
44 FROM fnd_lookup_values
45 WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
46 AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
47 INSTR(USERENV('LANGUAGE'), '.') + 1)
48 AND language = 'US';
49
50 lv_iana_charset fnd_lookup_values.tag%type;
51 BEGIN
52 OPEN csr_get_iana_charset;
53 FETCH csr_get_iana_charset INTO lv_iana_charset;
54 CLOSE csr_get_iana_charset;
55
56 hr_utility.trace('IANA Charset = '||lv_iana_charset);
57 RETURN (LV_IANA_CHARSET);
58 END get_IANA_charset;
59
60 -- ==============================================================================
61 -- Function to typecast CLOB to BLOB (binary conversion)
62 -- ==============================================================================
63
64 FUNCTION c2b( c IN CLOB ) RETURN BLOB
65 IS
66 pos PLS_INTEGER := 1;
67 buffer RAW(32767);
68 res BLOB;
69 lob_len PLS_INTEGER := dbms_lob.getlength(c);
70 BEGIN
71 -- hr_utility.trace('+====c2b===================================================+');
72 dbms_lob.createtemporary(res,TRUE);
73 dbms_lob.open(res,dbms_lob.lob_readwrite);
74 LOOP
75 buffer := utl_raw.cast_to_raw( dbms_lob.substr( c, 16000, pos ) );
76 IF utl_raw.length( buffer ) > 0 THEN
77 dbms_lob.writeappend(res,utl_raw.length(buffer),buffer);
78 END IF;
79 pos := pos + 16000;
80 EXIT WHEN pos > lob_len;
81 END LOOP;
82 -- hr_utility.trace('X====c2b===================================================X');
83 RETURN res;
84 EXCEPTION
85 WHEN others THEN
86 hr_utility.trace('Exception in c2b SQL-ERRM : '||SQLERRM);
87 END c2b;
88
89 -- ==============================================================================
90 -- Function to replace unallowed characters in XML Values
91 -- ==============================================================================
92 FUNCTION clean_XML(P_STRING IN VARCHAR2) RETURN VARCHAR2 AS
93 l_string varchar2(1000);
94 BEGIN
95 l_string := p_string;
96 l_string := REPLACE(l_string, '&', '&'||'amp;');
97 l_string := REPLACE(l_string, '<', '&'||'lt;'); --#60
98 l_string := REPLACE(l_string, '>', '&'||'gt;'); --#62
99 l_string := REPLACE(l_string, '''','&'||'apos;');
100 l_string := REPLACE(l_string, '"', '&'||'quot;');
101
102 --Diacritical marks handling--
103 IF g_SOURCE IS NOT NULL THEN
104 l_string:= translate(l_string,g_SOURCE,g_TARGET);
105 END IF;
106 RETURN l_string;
107 EXCEPTION
108 WHEN others THEN
109 hr_utility.trace('Exception in clean_XML SQL-ERRM :'||SQLERRM);
110 RETURN l_string;
111 END clean_XML;
112
113 -- ==============================================================================
114 -- Function to create header xmls for PDF Report
115 -- ==============================================================================
116 FUNCTION report_header_xml(p_payroll_action_id IN NUMBER)
117 RETURN VARCHAR2 IS
118 l_string VARCHAR2(32767) := NULL;
119 l_string_value VARCHAR2(1000) := NULL;
120 l_payroll_action_id NUMBER;
121 l_org_struct_id NUMBER :=NULL;
122 l_person_id NUMBER :=NULL;
123 l_org_id NUMBER :=NULL;
124 l_bg_id NUMBER;
125 l_asg_act_id NUMBER;
126 l_start_date DATE;
127 l_end_date DATE;
128 l_report_type VARCHAR2(30);
129 l_seq_num NUMBER;
130 l_lang VARCHAR2(2) := userenv ('lang');
131
132 CURSOR csr_get_hierarchy(p_org_struct_id IN NUMBER) IS
133 SELECT name
134 FROM per_organization_structures
135 WHERE organization_structure_id = p_org_struct_id;
136
137 CURSOR csr_get_employer(p_org_id IN NUMBER) IS
138 SELECT name
139 FROM hr_organization_units
140 WHERE organization_id = p_org_id;
141
142 BEGIN
143 hr_utility.trace('+====report_header_xml========================================+');
144
145 l_string := l_string || '<ROOT>'||EOL;
146
147 pay_nl_abp_archive.get_all_parameters (
148 p_payroll_action_id => p_payroll_action_id
149 ,p_org_struct_id => l_org_struct_id
150 ,p_org_id => l_org_id
151 ,p_report_type => l_report_type
152 ,p_seq_num => l_seq_num
153 ,p_bg_id => l_bg_id
154 ,p_start_date => l_start_date
155 ,p_end_date => l_end_date);
156
157 l_string_value := get_lookup_value('NL_ABP_REPORT_TYPE',l_report_type,l_lang);
158
159 l_string := l_string || '<P_REPORT_TYPE>'||clean_XML(l_string_value)||'</P_REPORT_TYPE>'||EOL;
160
161 l_string_value := fnd_date.date_to_chardate(l_start_date);
162 l_string := l_string || '<P_DATE_FROM>'||clean_XML(l_string_value)||'</P_DATE_FROM>'||EOL;
163
164 l_string_value := fnd_date.date_to_chardate(l_end_date);
165 l_string := l_string || '<P_DATE_TO>'||clean_XML(l_string_value)||'</P_DATE_TO>'||EOL;
166
167 IF l_org_struct_id IS NOT NULL THEN
168 OPEN csr_get_hierarchy(l_org_struct_id);
169 FETCH csr_get_hierarchy INTO l_string_value;
170 CLOSE csr_get_hierarchy;
171 l_string := l_string || '<P_HIERARCHY>'||clean_XML(l_string_value)||'</P_HIERARCHY>'||EOL;
172 END IF;
173
174 IF l_org_id IS NOT NULL THEN
175 OPEN csr_get_employer(l_org_id);
176 FETCH csr_get_employer INTO l_string_value;
177 CLOSE csr_get_employer;
178 l_string := l_string || '<P_EMPLOYER>'||clean_XML(l_string_value)||'</P_EMPLOYER>'||EOL;
179 END IF;
180
181 hr_utility.trace('X====report_header_xml========================================X');
182
183 RETURN l_string;
184
185 EXCEPTION
186 WHEN others THEN
187 hr_utility.trace('Exception in report_header_xml SQL-ERRM : '||SQLERRM);
188 END report_header_xml;
189
190 -- ==============================================================================
191 -- Procedure to append the details to main report
192 -- ==============================================================================
193 PROCEDURE write_to_clob(p_xmltable IN txmltable
194 ,p_clob OUT NOCOPY CLOB
195 ,p_abp_output_type IN VARCHAR2
196 ,p_payroll_action_id IN NUMBER) IS
197
198 CURSOR csr_get_employer(p_org_id IN NUMBER) IS
199 SELECT name
200 FROM hr_organization_units
201 WHERE organization_id = p_org_id;
202
203 l_xml_element VARCHAR2(32767);
204 l_str VARCHAR2(80);
205 l_string VARCHAR2(32767) := NULL;
206 l_string1 VARCHAR2(80) := NULL;
207 BEGIN
208
209 fnd_file.put_line(fnd_file.log,'+====write_to_clob==========================================+');
210
211 l_str := '<?xml version="1.0" encoding="' || get_IANA_charset || '"?>'||EOL;
212 dbms_lob.createtemporary(p_clob, FALSE, DBMS_LOB.CALL);
213 dbms_lob.open(p_clob, DBMS_LOB.LOB_READWRITE);
214
215 IF p_abp_output_type IN ('NLABP_PDF','NLABP_CSV') THEN
216 l_string := report_header_xml(p_payroll_action_id);
217 dbms_lob.writeAppend(p_clob, length(l_string), l_string);
218 END IF;
219
220 IF p_XMLTable.COUNT > 0 THEN
221 FOR table_counter IN p_XMLTable.FIRST .. p_XMLTable.LAST
222 LOOP
223 IF p_XMLTable(table_counter).tagvalue = '_START_' THEN
224 l_xml_element := '<' || p_XMLTable(table_counter).tagname || '>'||EOL;
225 ELSIF p_XMLTable(table_counter).tagvalue = '_END_' THEN
226 l_xml_element := '</' || p_XMLTable(table_counter).tagname || '>'||EOL;
227 ELSE
228 l_xml_element := '<' || p_XMLTable(table_counter).tagname ||
229 '>'|| clean_XML(p_XMLTable(table_counter).tagvalue) ||
230 '</'||p_XMLTable(table_counter).tagname || '>'||EOL;
231 END IF;
232 dbms_lob.writeappend(p_clob, length(l_xml_element), l_xml_element);
233 END LOOP;
234 ELSE
235 dbms_lob.writeAppend(p_clob, length(l_str), l_str );
236 END IF;
237
238 IF p_abp_output_type IN ('NLABP_PDF','NLABP_CSV') THEN
239 l_string1 := '</ROOT>'||EOL ;
240 dbms_lob.writeAppend(p_clob, length(l_string1), l_string1);
241 END IF;
242
243 fnd_file.put_line(fnd_file.log,'X====WRITE_to_clob==========================================X');
244
245 EXCEPTION
246 WHEN others THEN
247 fnd_file.put_line(fnd_file.log,'Exception in write_to_clob SQL-ERRM : '||SQLERRM);
248 hr_utility.raise_error;
249 END write_to_clob;
250
251
252 PROCEDURE Tag (xCtr IN OUT NOCOPY NUMBER
253 ,pTagName IN VARCHAR2
254 ,pTagValue IN VARCHAR2)
255 IS
256 BEGIN
257 IF pTagValue IS NOT NULL THEN
258 xXMLTable(xCtr).TagName := pTagName;
259 xXMLTable(xCtr).TagValue := pTagValue;
260 IF pTagValue = '_START_NS_' THEN
261 xXMLTable(xCtr).TagName := xXMLTable(xCtr).TagName||EOL||'xmlns="http://sbc.apg.nl/gegevensintakeEnControle/levering/"';
262 xXMLTable(xCtr).TagValue := '_START_';
263 END IF;
264 xCtr := xCtr + 1;
265 END IF;
266 END Tag;
267
268 -- =============================================================================
269 -- gen_body_xml_main - XML generation at assignment level
270 -- =============================================================================
271 PROCEDURE gen_body_xml_main(p_asg_action_id IN NUMBER
272 ,p_abp_output_type IN VARCHAR2
273 ,x IN OUT NOCOPY NUMBER) IS
274
275 CURSOR csr_asg_details(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
276 SELECT pai.action_information1 emp_num
277 ,pai.action_information2 asg_num
278 ,to_char(fnd_date.canonical_to_date(pai.action_information3),'RRRR-MM-DD') asg_start_date
279 ,to_char(fnd_date.canonical_to_date(pai.action_information4),'RRRR-MM-DD') asg_end_date
280 ,pai.action_information5 term_reason
281 ,decode (pai.action_information5
282 ,'R', 'A'
283 ,'A', 'R'
284 ,'O', 'I'
285 ,pai.action_information5) term_reason_pdf
286 ,pai.action_information6 kind_emp
287 ,pai.assignment_id assignment_id
288 FROM pay_action_information pai
289 WHERE pai.action_context_id = c_assignment_action_id
290 AND pai.action_information_category = 'NL_ABP_ASG_INFO'
291 AND pai.action_context_type = 'AAP';
292 --=============================================================================
293 CURSOR csr_emp_details (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
294 SELECT pai.action_information2 employee_number
295 ,pai.action_information3 sofi_number
296 ,pai.action_information4 last_name
297 ,pai.action_information5 init
298 ,pai.action_information6 prefix
299 ,to_char(fnd_date.canonical_to_date(pai.action_information7),'RRRR-MM-DD') date_of_birth
300 ,pai.action_information8 nationality
301 ,pai.action_information9 gender
302 ,to_char(fnd_date.canonical_to_date(pai.action_information10),'RRRR-MM-DD') date_of_death
303 ,pai.action_information11 p_last_name
304 ,pai.action_information12 p_prefix
305 ,pai.action_information13 p_init
306 ,pai.action_information14 street
307 ,pai.action_information15 house_number
308 ,pai.action_information16 house_number_addition
309 ,pai.action_information17 location
310 ,pai.action_information18 postal_code
311 ,pai.action_information19 city
312 ,pai.action_information20 region
313 ,pai.action_information21 iso
314 ,pai.action_information22 style
315 ,pai.action_information23 marital_status
316 ,to_char(fnd_date.canonical_to_date(pai.action_information24),'RRRR-MM-DD') marital_status_sd
317 ,pai.action_information25 indv_exe
318 ,to_char(fnd_date.canonical_to_date(pai.action_information26),'RRRR-MM-DD') start_date
319 ,to_char(fnd_date.canonical_to_date(pai.action_information27),'RRRR-MM-DD') end_date
320 ,pai.action_information28 person_data_flag
321 FROM pay_action_information pai
322 WHERE pai.action_context_id = c_assignment_action_id
323 AND pai.action_information_category = 'NL_ABP_EMP_INFO'
324 AND pai.action_context_type = 'AAP';
325 --=============================================================================
326 CURSOR csr_pay_period_details(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
327 SELECT to_char(fnd_date.canonical_to_date(pai.action_information1),'RRRR-MM-DD') start_date
328 ,to_char(fnd_date.canonical_to_date(pai.action_information2),'RRRR-MM-DD') end_date
329 ,pai.action_information3 kop
330 ,pai.action_information4 income_code
331 ,fnd_number.canonical_to_number(pai.action_information5) ptp
332 ,pai.action_information6 code_inc
333 FROM pay_action_information pai
334 WHERE pai.action_context_id = c_assignment_action_id
335 AND pai.action_information_category = 'NL_ABP_PAY_PERIOD_INFO'
336 AND action_context_type = 'AAP'
337 ORDER BY start_date;
338 --=============================================================================
339 CURSOR csr_pension_details(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE,c_effective_date VARCHAR2) IS
340 SELECT pai.action_information1 pension_type
341 ,pai.action_information2 pension_type_variant
342 ,to_char(fnd_date.canonical_to_date(pai.action_information3),'RRRR-MM-DD') pension_type_sdate
343 ,to_char(fnd_date.canonical_to_date(pai.action_information4),'RRRR-MM-DD') pension_type_info_sdate
344 ,pai.action_information5 wage_type
345 ,fnd_number.canonical_to_number(pai.action_information6) abp_pension_base
346 ,fnd_number.canonical_to_number(pai.action_information7) contr_base
347 ,fnd_number.canonical_to_number(pai.action_information8) contr_amt
348 ,fnd_number.canonical_to_number(pai.action_information9) vop
349 ,to_char(fnd_date.canonical_to_date(pai.action_information10),'RRRR-MM-DD') pension_type_info_sdate_multi --Bug 16301893
350 ,to_char(fnd_date.canonical_to_date(pai.action_information11),'RRRR-MM-DD') pension_type_edate
351 FROM pay_action_information pai
352 WHERE pai.action_context_id = c_assignment_action_id
353 AND pai.action_information_category = 'NL_ABP_PENSION_INFO'
354 AND action_context_type = 'AAP'
355 AND to_char(fnd_date.canonical_to_date(pai.action_information4),'RRRR-MM-DD') <= c_effective_date; --Bug 16301893
356 --=============================================================================
357 CURSOR csr_get_pact_id(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
358 SELECT ppa.payroll_action_id
359 FROM pay_payroll_actions ppa
360 ,pay_assignment_actions paa
361 WHERE paa.payroll_action_id = ppa.payroll_action_id
362 AND paa.assignment_action_id = c_assignment_action_id;
363 --=============================================================================
364 CURSOR csr_get_nationality(c_code VARCHAR2) IS
365 SELECT shared_type_name
366 FROM per_shared_types
367 WHERE lookup_type = 'NL_NATIONALITY'
368 AND information1 = c_code
369 ORDER BY 1;
370 --=============================================================================
371 CURSOR csr_get_assignment_total(c_action_context_id pay_action_information.action_context_id%TYPE) IS
372 SELECT pension_type
373 , pension_type_variant
374 , abp_pension_base
375 , sum (contr_base) contr_base
376 , sum (contr_amt) contr_amt
377 FROM (
378 SELECT pai.action_information1 pension_type
379 , pai.action_information2 pension_type_variant
380 , pai.action_information4 pension_type_info_sdate
381 , nvl (fnd_number.canonical_to_number (pai.action_information6), 0) abp_pension_base
382 , avg (nvl (fnd_number.canonical_to_number (pai.action_information7), 0)) contr_base
383 , avg (nvl (fnd_number.canonical_to_number (pai.action_information8), 0)) contr_amt
384 FROM pay_action_information pai
385 WHERE pai.action_context_id = c_action_context_id
386 AND pai.action_information_category = 'NL_ABP_PENSION_INFO'
387 AND action_context_type = 'AAP'
388 GROUP BY pai.action_information1
389 , pai.action_information2
390 , pai.action_information4
391 , pai.action_information6
392 )
393 GROUP BY pension_type
394 , pension_type_variant
395 , abp_pension_base;
396 --=============================================================================
397 --Variables--
398 TYPE tPayPeriodTable IS TABLE OF csr_pay_period_details%ROWTYPE INDEX BY BINARY_INTEGER;
399 l_nationality per_shared_types.shared_type_name%TYPE;
400 l_lang VARCHAR2(2) := userenv ('lang');
401 l_emp_details csr_emp_details%ROWTYPE;
402 l_asg_details csr_asg_details%ROWTYPE;
403 l_pay_count NUMBER :=0;
404 report_data tPayPeriodTable;
405 l_payroll_action_id NUMBER;
406 l_pension_type_info_sdate pay_action_information.action_information4%TYPE; --Bug 16301893
407
408 BEGIN
409 fnd_file.put_line(fnd_file.log,'+====gen_body_xml_main=====================================+');
410 fnd_file.put_line(fnd_file.log,'Assignment Action Id :'||p_asg_action_id);
411
412 OPEN csr_get_pact_id(p_asg_action_id);
413 FETCH csr_get_pact_id INTO l_payroll_action_id;
414 CLOSE csr_get_pact_id;
415
416 OPEN csr_asg_details(p_asg_action_id);
417 FETCH csr_asg_details INTO l_asg_details;
418 CLOSE csr_asg_details;
419
420 OPEN csr_emp_details(p_asg_action_id);
421 FETCH csr_emp_details INTO l_emp_details;
422 CLOSE csr_emp_details;
423
424 OPEN csr_get_nationality(l_emp_details.nationality);
425 FETCH csr_get_nationality INTO l_nationality;
426 CLOSE csr_get_nationality;
427
428 FOR v_csr_pay_period_details IN csr_pay_period_details(p_asg_action_id) LOOP
429 l_pay_count := l_pay_count + 1;
430 report_data(l_pay_count).start_date := v_csr_pay_period_details.start_date;
431 report_data(l_pay_count).end_date := v_csr_pay_period_details.end_date;
432 report_data(l_pay_count).kop := v_csr_pay_period_details.kop;
433 report_data(l_pay_count).income_code := v_csr_pay_period_details.income_code;
434 report_data(l_pay_count).ptp := v_csr_pay_period_details.ptp;
435 report_data(l_pay_count).code_inc := v_csr_pay_period_details.code_inc;
436 fnd_file.put_line(fnd_file.log,'Start Date/End Date/KOP/Income Code/PTP/Code_Inc : ' ||report_data(l_pay_count).start_date
437 ||' '||report_data(l_pay_count).end_date
438 ||' '||report_data(l_pay_count).kop
439 ||' '||report_data(l_pay_count).income_code
440 ||' '||report_data(l_pay_count).ptp
441 ||' '||report_data(l_pay_count).code_inc);
442 END LOOP;
443
444 fnd_file.put_line(fnd_file.log,'Fetched all data. XML Generation starts');
445 fnd_file.put_line(fnd_file.log,'Assignment_Id : '||l_asg_details.assignment_id);
446
447 fnd_file.put_line(fnd_file.log,'Asg Details');
448
449 Tag(x,'inkomstenverhouding','_START_'); -- + assignment details*
450 Tag(x,'personeelsnummer',l_asg_details.emp_num); -- employee no.*
451 Tag(x,'nummer',l_asg_details.asg_num); -- asg no.*
452 IF p_abp_output_type = 'NLABP_XML' THEN
453 Tag(x,'datumAanvang',l_asg_details.asg_start_date); -- asg start date*
454 Tag(x,'datumEinde',l_asg_details.asg_end_date); -- asg end date
455 Tag(x,'redenEinde',l_asg_details.term_reason); -- end of participation reason
456 Tag(x,'aard',l_asg_details.kind_emp); -- kind of emp*
457 ELSIF p_abp_output_type = 'NLABP_PDF' THEN
458 Tag(x,'datumAanvangA',fnd_date.date_to_chardate(
459 to_date(l_asg_details.asg_start_date,'YYYY-MM-DD')));
460 Tag(x,'datumEindeA',fnd_date.date_to_chardate(
461 to_date(l_asg_details.asg_end_date,'YYYY-MM-DD')));
462 Tag(x,'redenEinde',get_lookup_value('PQP_NL_OVERRIDE_REASON',l_asg_details.term_reason_pdf,l_lang));
463 END IF;
464
465 fnd_file.put_line(fnd_file.log,'Pay period Details');
466
467 IF l_pay_count > 0 THEN
468 FOR i IN report_data.FIRST..report_data.LAST LOOP
469 Tag(x,'inkomstenperiode','_START_'); -- + pay period details*
470 IF p_abp_output_type = 'NLABP_XML' THEN
471 Tag(x,'datumAanvang',report_data(i).start_date); -- start date pay period*
472 Tag(x,'datumEinde',report_data(i).end_date); -- end date pay period
473 Tag(x,'verbijzonderingInkomstenverhouding',report_data(i).kop); -- kind of part*
474 Tag(x,'soortInkomstenverhouding',report_data(i).income_code); -- income code
475 Tag(x,'deeltijdfactor',to_char(report_data(i).ptp,'FM0.0000')); -- parttime factor*
476 Tag(x,'codeMeetelling',report_data(i).code_inc); -- code included*
477 ELSIF p_abp_output_type = 'NLABP_PDF' THEN
478 Tag(x,'datumAanvang',fnd_date.date_to_chardate(
479 to_date(report_data(i).start_date,'YYYY-MM-DD')));
480 Tag(x,'datumEinde',fnd_date.date_to_chardate(
481 to_date(report_data(i).end_date,'YYYY-MM-DD')));
482 Tag(x,'verbijzonderingInkomstenverhouding',get_lookup_value('PQP_NL_KIND_OF_PARTICIPATION'
483 ,report_data(i).kop,l_lang));
484 Tag(x,'deeltijdfactor',to_char(report_data(i).ptp*100,'FM990D00'));
485 Tag(x,'codeMeetelling',get_lookup_value('HR_NL_YES_NO',
486 REPLACE(REPLACE(report_data(i).code_inc,'ja','Y'),'nee','N'),l_lang));
487 ELSIF p_abp_output_type = 'NLABP_CSV' THEN
488 Tag(x,'personeelsnummer',l_asg_details.emp_num);
489 Tag(x,'nummer',l_asg_details.asg_num);
490 Tag(x,'significantDeelAchternaam',REPLACE(l_emp_details.last_name,',',' '));
491 Tag(x,'datum', to_char(to_date(report_data(i).start_date,'RRRR-MM-DD'),'MM YYYY'));
492 Tag(x,'deeltijdfactor',to_char(report_data(i).ptp*100,'FM990.00'));
493 END IF;
494
495 fnd_file.put_line(fnd_file.log,'Pension Type Details');
496 IF report_data(i).ptp <> 0 THEN
497 FOR v_csr_pension_details IN csr_pension_details(p_asg_action_id,report_data(i).start_date) LOOP
498
499 --Begin Bug 16301893
500 IF l_pay_count > 1 Then
501 l_pension_type_info_sdate := v_csr_pension_details.pension_type_info_sdate_multi;
502 ELSE
503 l_pension_type_info_sdate := v_csr_pension_details.pension_type_info_sdate;
504 END IF;
505 --End Bug 16301893
506
507 IF (l_pay_count =1) OR (l_pay_count > 1 AND report_data(i).start_date = l_pension_type_info_sdate) THEN -- for multiple pay period --Bug 16301893
508 IF p_abp_output_type = 'NLABP_CSV' THEN
509 Tag(x,'productLoon',to_char(v_csr_pension_details.abp_pension_base,'FM9999999990.00'));
510 IF v_csr_pension_details.pension_type = 'OPNP' AND v_csr_pension_details.pension_type_variant IS NULL THEN
511 Tag(x,'opnp',to_char(v_csr_pension_details.contr_base,'FM9999999990.00'));
512 ELSIF v_csr_pension_details.pension_type = 'AAOP' AND v_csr_pension_details.pension_type_variant IS NULL THEN
513 Tag(x,'Aaop',to_char(v_csr_pension_details.contr_base,'FM9999999990.00'));
514 ELSIF v_csr_pension_details.pension_type = 'OPNP-I' AND v_csr_pension_details.pension_type_variant = 'A' THEN
515 Tag(x,'opnp65',to_char(v_csr_pension_details.contr_amt,'FM9999999990.00'));
516 ELSIF v_csr_pension_details.pension_type = 'OPNP-I' AND v_csr_pension_details.pension_type_variant = 'B' THEN
517 Tag(x,'opnp25',to_char(v_csr_pension_details.contr_amt,'FM9999999990.00'));
518 ELSIF v_csr_pension_details.pension_type = 'OPNP-I' AND v_csr_pension_details.pension_type_variant = 'C' THEN
519 Tag(x,'opnp50',to_char(v_csr_pension_details.contr_amt,'FM9999999990.00'));
520 ELSIF v_csr_pension_details.pension_type = 'VPL' AND v_csr_pension_details.pension_type_variant IS NULL THEN
521 Tag(x,'vpl',to_char(v_csr_pension_details.contr_amt,'FM9999999990.00'));
522 ELSIF v_csr_pension_details.pension_type = 'PPP-I' AND v_csr_pension_details.pension_type_variant IS NULL THEN
523 Tag(x,'Partner',to_char(v_csr_pension_details.contr_amt,'FM9999999990.00'));
524 END IF;
525 ELSE
526 Tag(x,'pensioenVerzekeringverhouding','_START_'); -- + pension type details
527 Tag(x,'productkeuze',v_csr_pension_details.pension_type); -- pension type*
528 Tag(x,'productvariant',v_csr_pension_details.pension_type_variant); -- pension type variant
529 Tag(x,'datumAanvang',v_csr_pension_details.pension_type_sdate); -- start date pension type*
530 Tag(x,'datumEinde',v_csr_pension_details.pension_type_edate); -- end date pension type
531 Tag(x,'pensioenVerzekeringPeriode','_START_'); -- + pension type pay period details*
532 Tag(x,'datumAanvang',l_pension_type_info_sdate); -- start date pension type info* --Bug 16301893
533 Tag(x,'soortLoonGrondslag',v_csr_pension_details.wage_type); -- type of wage
534 IF p_abp_output_type = 'NLABP_XML' THEN
535 Tag(x,'productLoon',to_char(v_csr_pension_details.abp_pension_base,'FM9999999990.00')); -- ABP pension base
536 Tag(x,'premiegrondslag',to_char(v_csr_pension_details.contr_base,'FM9999999990.00')); -- contribution base
537 Tag(x,'premie',to_char(v_csr_pension_details.contr_amt,'FM9999999990.00')); -- contribution amt
538 Tag(x,'meetellingswaarde',to_char(v_csr_pension_details.vop,'FM000.00')); -- eligibility %
539 ELSIF p_abp_output_type = 'NLABP_PDF' THEN
540 Tag(x,'productLoon',to_char(v_csr_pension_details.abp_pension_base,'FM9G999G999G990D00'));
541 Tag(x,'premiegrondslag',to_char(v_csr_pension_details.contr_base,'FM9G999G999G990D00'));
542 Tag(x,'premie',to_char(v_csr_pension_details.contr_amt,'FM9G999G999G990D00'));
543 Tag(x,'meetellingswaarde',to_char(v_csr_pension_details.vop,'FM000D00'));
544 END IF;
545 Tag(x,'pensioenVerzekeringPeriode','_END_'); -- - pension type pay period details*
546 Tag(x,'pensioenVerzekeringverhouding','_END_'); -- - pension type details
547
548
549 fnd_file.put_line(fnd_file.log,'Employer Total Details');
550 fnd_file.put_line(fnd_file.log,'Pension Base'||v_csr_pension_details.abp_pension_base);
551 fnd_file.put_line(fnd_file.log,'Contr Base '||v_csr_pension_details.contr_base);
552 fnd_file.put_line(fnd_file.log,'Contr Amt '||v_csr_pension_details.contr_amt);
553
554
555 END IF;
556 END IF; --for multiple pay period
557 END LOOP; --pension details
558 END IF; --ptp
559 Tag(x,'inkomstenperiode','_END_'); -- - pay period details*
560 END LOOP; --report_data
561
562 FOR v_csr_get_assignment_total IN csr_get_assignment_total(p_asg_action_id) LOOP
563 FOR k IN 1..7 LOOP
564 IF tpension(k) = v_csr_get_assignment_total.pension_type AND v_csr_get_assignment_total.pension_type_variant IS NULL THEN
565 fnd_file.put_line(fnd_file.log,'Pension Type '||tPension(k));
566 g_er_amount(k).pension_base := nvl(g_er_amount(k).pension_base,0) + nvl(v_csr_get_assignment_total.abp_pension_base,0);
567 g_er_amount(k).contr_base := nvl(g_er_amount(k).contr_base,0) + nvl(v_csr_get_assignment_total.contr_base,0);
568 g_er_amount(k).contr_amt := nvl(g_er_amount(k).contr_amt,0) + nvl(v_csr_get_assignment_total.contr_amt,0);
569 g_er_amount(k).flag := 1; --flag to indicate which pension types are to be reported
570 ELSE
571 IF substr(tpension(k),8,1) = v_csr_get_assignment_total.pension_type_variant THEN
572 fnd_file.put_line(fnd_file.log,'Pension Type '||tPension(k));
573 fnd_file.put_line(fnd_file.log,'Pension Type Variant '||substr(tPension(k),8,1));
574 g_er_amount(k).pension_base := nvl(g_er_amount(k).pension_base,0) + nvl(v_csr_get_assignment_total.abp_pension_base,0);
575 g_er_amount(k).contr_base := nvl(g_er_amount(k).contr_base,0) + nvl(v_csr_get_assignment_total.contr_base,0);
576 g_er_amount(k).contr_amt := nvl(g_er_amount(k).contr_amt,0) + nvl(v_csr_get_assignment_total.contr_amt,0);
577 g_er_amount(k).flag := 1; --flag to indicate which pension types are to be reported
578 END IF; --pension type variant
579 END IF; --pension type
580 END LOOP;
581 END LOOP;
582
583 END IF; --l_pay_count
584
585
586 IF l_emp_details.person_data_flag = 'Y' AND p_abp_output_type <> 'NLABP_CSV' THEN
587 fnd_file.put_line(fnd_file.log,'Person Details');
588 Tag(x,'natuurlijkPersoon','_START_'); -- + person details*
589 Tag(x,'personeelsnummer',l_emp_details.employee_number); -- employee no.*
590 Tag(x,'bsn',l_emp_details.sofi_number); -- BSN/Sofi no.
591 Tag(x,'significantDeelAchternaam',l_emp_details.last_name); -- emp last name*
592 Tag(x,'voorletters',l_emp_details.init); -- initials*
593 Tag(x,'voorvoegsels',l_emp_details.prefix); -- prefix
594 IF p_abp_output_type = 'NLABP_XML' THEN
595 Tag(x,'geboortedatum',l_emp_details.date_of_birth); -- dob*
596 Tag(x,'nationaliteit',l_emp_details.nationality); -- nationality*
597 Tag(x,'geslacht',l_emp_details.gender); -- gender*
598 Tag(x,'datumOverlijden',l_emp_details.date_of_death); -- date of decease
599 Tag(x,'codeBurgerlijkeStaat',l_emp_details.marital_status); -- marital status
600 Tag(x,'datumIngangBurgerlijkeStaat',l_emp_details.marital_status_sd); -- start date marital status
601 ELSIF p_abp_output_type = 'NLABP_PDF' THEN
602 Tag(x,'geboortedatum',fnd_date.date_to_chardate(
603 to_date(l_emp_details.date_of_birth,'YYYY-MM-DD')));
604 Tag(x,'nationaliteit',l_nationality);
605 Tag(x,'geslacht',get_lookup_value('SEX',REPLACE(REPLACE(l_emp_details.gender
606 ,'1','M'),'2','F'),l_lang));
607 Tag(x,'datumOverlijden',fnd_date.date_to_chardate(
608 to_date(l_emp_details.date_of_death,'YYYY-MM-DD')));
609 END IF;
610 Tag(x,'naamPartner',l_emp_details.p_last_name); -- last name partner
611 Tag(x,'voorlettersPartner',l_emp_details.p_init); -- initials partner*
612 Tag(x,'voorvoegselsPartner',l_emp_details.p_prefix); -- prefix partner
613 IF p_abp_output_type = 'NLABP_XML' THEN
614 Tag(x,'individueleOntheffing',l_emp_details.indv_exe); -- individual exemption*
615 ELSIF p_abp_output_type = 'NLABP_PDF' THEN
616 Tag(x,'individueleOntheffing',get_lookup_value('HR_NL_YES_NO',
617 REPLACE(nvl(l_emp_details.indv_exe,'N'),'G','Y'),l_lang));
618 END IF;
619
620 fnd_file.put_line(fnd_file.log,'Address Details');
621
622 IF l_emp_details.style = 'NL' THEN
623 Tag(x,'adresBinnenland','_START_'); -- + dutch address details
624 Tag(x,'straatnaam',l_emp_details.street); -- street name*
625 Tag(x,'huisnummer',l_emp_details.house_number); -- house no.*
626 Tag(x,'huisnummerToevoeging',l_emp_details.house_number_addition); -- house no. add
627 Tag(x,'locatieomschrijving',l_emp_details.location); -- location desc
628 Tag(x,'postcode',l_emp_details.postal_code); -- postal code*
629 Tag(x,'woonplaats',l_emp_details.city); -- city*
630 Tag(x,'adresBinnenland','_END_'); -- - dutch address details
631 ELSIF l_emp_details.style = 'NL_GLB' THEN
632 Tag(x,'adresBuitenland','_START_'); -- + foreign address details
633 Tag(x,'straatnaam',l_emp_details.street); -- street name*
634 Tag(x,'huisnummer',l_emp_details.house_number); -- house no.*
635 Tag(x,'locatieomschrijving',l_emp_details.location); -- location desc
636 Tag(x,'postcode',l_emp_details.postal_code); -- postal code
637 Tag(x,'woonplaats',l_emp_details.city); -- city*
638 Tag(x,'regioNaam',l_emp_details.region); -- region
639 Tag(x,'ISOLandcode',l_emp_details.iso); -- ISO country code*
640 Tag(x,'adresBuitenland','_END_'); -- - foreign address details
641 END IF;
642 Tag(x,'natuurlijkPersoon','_END_'); -- - person details*
643 END IF;
644
645 Tag(x,'inkomstenverhouding','_END_'); -- - assignment details*
646
647 fnd_file.put_line(fnd_file.log,'X====gen_body_xml_main=====================================X');
648 EXCEPTION
649 WHEN others THEN
650 fnd_file.put_line(fnd_file.log,'Exception in gen_body_xml_main SQL-ERRM :'||SQLERRM);
651 END gen_body_xml_main;
652
653
654 -- =============================================================================
655 -- generate - XML generation at File level
656 -- =============================================================================
657 PROCEDURE generate( p_action_context_id NUMBER
658 ,p_abp_output_type VARCHAR2
659 ,p_template_name VARCHAR2
660 ,p_xml OUT NOCOPY CLOB)
661 IS
662
663 CURSOR csr_get_file_details(c_payroll_action_id IN NUMBER)
664 IS
665 SELECT action_information1 reg_num_sender
666 ,to_char(fnd_date.canonical_to_date(action_information2),'YYYYMM') period
667 ,action_information3 seq_num
668 ,action_information4 version
669 ,action_information5 creation_date
670 ,action_information6 period_type
671 ,action_information7 prod_test
672 ,action_information8 name_sender
673 ,action_information9 release
674 ,to_char(fnd_date.canonical_to_date(action_information10),'MM YYYY') ref_file
675 FROM pay_action_information pai
676 WHERE pai.action_context_id = c_payroll_action_id
677 AND pai.action_information_category = 'NL_ABP_SENDER_INFO'
678 AND pai.action_context_type = 'PA';
679 --=============================================================================
680 CURSOR csr_get_employer_det(c_payroll_action_id IN NUMBER)
681 IS
682 SELECT action_information1 er_num
683 ,action_information2 freq
684 ,to_char(fnd_date.canonical_to_date(action_information3),'RRRR-MM-DD') start_date
685 ,to_char(fnd_date.canonical_to_date(action_information4),'RRRR-MM-DD') end_date
686 ,action_information5 ref_er
687 ,action_information6 org_id
688 FROM pay_action_information pai
689 WHERE pai.action_context_id = c_payroll_action_id
690 AND pai.action_information_category = 'NL_ABP_EMPLOYER_INFO'
691 AND ACTION_CONTEXT_TYPE = 'PA';
692 --=============================================================================
693 CURSOR c_usr_tab_col IS
694 SELECT put.user_table_id
695 ,puc.user_column_id
696 FROM pay_user_tables put
697 ,pay_user_columns puc
698 WHERE put.user_table_id = puc.user_table_id
699 AND put.legislation_code = puc.legislation_code
700 AND put.user_table_name = 'NL_DIACRITICAL_MARKS'
701 AND put.legislation_code = 'NL';
702 --=============================================================================
703 CURSOR c_src_dest_str(p_user_column_id IN NUMBER
704 , p_user_table_id IN NUMBER
705 , p_business_group_id IN NUMBER
706 , p_start_date IN DATE) IS
707 SELECT DISTINCT
708 UPPER (purf.row_low_range_or_name) Source
709 ,UPPER (pucif.value) Target
710 FROM pay_user_column_instances_f pucif
711 ,pay_user_rows_f purf
712 WHERE pucif.user_column_id = p_user_column_id
713 AND purf.user_table_id = p_user_table_id
714 AND pucif.user_row_id = purf.user_row_id
715 AND pucif.business_group_id = purf.business_group_id
716 AND pucif.business_group_id = p_business_group_id
717 AND p_start_date BETWEEN pucif.EFFECTIVE_START_DATE AND pucif.EFFECTIVE_END_DATE
718 AND p_start_date BETWEEN purf.EFFECTIVE_START_DATE AND purf.EFFECTIVE_END_DATE;
719 --=============================================================================
720 CURSOR c_bg_stdate(p_payroll_action_id IN NUMBER) IS
721 SELECT business_group_id bg_id
722 ,start_date start_date
723 FROM pay_payroll_actions
724 WHERE payroll_action_id = p_payroll_action_id;
725 --=============================================================================
726
727 x NUMBER := 0;
728 l_file_details csr_get_file_details%ROWTYPE;
729 l_employer_det csr_get_employer_det%ROWTYPE;
730 l_action_info_id NUMBER;
731 l_ovn NUMBER;
732 l_xml CLOB;
733 l_lang VARCHAR2(2) := userenv ('lang');
734 l_ctxt NUMBER;
735 l_user_table_id NUMBER;
736 l_user_column_id NUMBER;
737 l_bg_id NUMBER;
738 l_start_date DATE;
739 -- =============================================================================
740 -- gen_body_xml - Procedure for XML generation at Employer level
741 -- =============================================================================
742 PROCEDURE gen_body_xml(x IN OUT NOCOPY NUMBER)
743 IS
744
745 CURSOR csr_get_child_act_id (c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE )IS
746 SELECT assignment_action_id ass_act_id
747 FROM pay_assignment_actions
748 WHERE source_action_id = c_assignment_action_id;
749 --=============================================================================
750 CURSOR csr_get_asg_action_id(c_payroll_action_id IN NUMBER
751 ,c_employer_id IN NUMBER) IS
752 SELECT assignment_action_id
753 FROM pay_assignment_actions
754 WHERE payroll_action_id = c_payroll_action_id
755 AND tax_unit_id = c_employer_id;
756 --=============================================================================
757 CURSOR csr_chk_asg_ctxt(c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE) IS
758 SELECT 1
759 FROM pay_action_information pai
760 WHERE pai.action_context_id = c_assignment_action_id
761 AND pai.action_information_category = 'NL_ABP_ASG_INFO'
762 AND pai.action_context_type = 'AAP';
763 --=============================================================================
764 CURSOR csr_asg_withdrawal(c_payroll_action_id pay_assignment_actions.payroll_action_id%TYPE) IS
765 SELECT pai.action_information1 emp_num
766 , pai.action_information2 asg_num
767 , to_char (fnd_date.canonical_to_date (pai.action_information3), 'RRRR-MM-DD') start_date
768 , pai.action_information4 sofi_num
769 , pai.action_information5 code_withdrawal
770 FROM pay_action_information pai
771 WHERE pai.action_context_id IN
772 (
773 SELECT assignment_action_id
774 FROM pay_assignment_actions
775 WHERE payroll_action_id = c_payroll_action_id
776 )
777 AND pai.action_context_type = 'AAP'
778 AND pai.action_information_category IN ('NL_ABP_COMP_WITHDRAWAL_INFO', 'NL_ABP_WITHDRAWAL_INFO')
779 ORDER BY emp_num,asg_num,sofi_num,start_date;
780 --=============================================================================
781 l_asg_withdrawal csr_asg_withdrawal%ROWTYPE;
782
783 BEGIN
784 fnd_file.put_line(fnd_file.log,'+====gen_body_xml==========================================+');
785
786 fnd_file.put_line(fnd_file.log,'Payroll Action Id = '||p_action_context_id);
787
788 IF INSTR ('a', COMPOSE('a'|| UNISTR('\0301')) ) = 0 THEN
789 OPEN c_bg_stdate(p_action_context_id);
790 FETCH c_bg_stdate INTO l_bg_id,l_start_date;
791 CLOSE c_bg_stdate;
792
793 OPEN c_usr_tab_col;
794 FETCH c_usr_tab_col INTO l_user_table_id, l_user_column_id;
795 CLOSE c_usr_tab_col;
796
797 FOR I in c_src_dest_str(l_user_column_id, l_user_table_id, l_bg_id, l_start_date)
798 LOOP
799 g_SOURCE := g_SOURCE||upper(I.Source)||lower(I.Source);
800 g_TARGET := g_TARGET||upper(I.Target)||lower(I.Target);
801 END LOOP;
802
803 fnd_file.put_line(fnd_file.log,'g_SOURCE : '||g_SOURCE);
804 fnd_file.put_line(fnd_file.log,'g_TARGET : '||g_TARGET);
805 END IF;
806
807 --init
808 FOR i in 1..7 LOOP
809 g_er_amount(i).pension_base:=0;
810 g_er_amount(i).contr_base:=0;
811 g_er_amount(i).contr_amt:=0;
812 END LOOP;
813 --init
814
815 OPEN csr_get_employer_det(p_action_context_id);
816 FETCH csr_get_employer_det INTO l_employer_det;
817 CLOSE csr_get_employer_det;
818
819 Tag(x,'administratieveEenheid','_START_'); -- + employer details*
820 -- Generate xml for Employer details begin
821 Tag(x,'aansluitnummer',l_employer_det.er_num); -- ABP reg. no. employer*
822 Tag(x,'nummerOpdrachtgever','1001'); -- Number ABP*
823 IF p_abp_output_type = 'NLABP_XML' THEN
824 Tag(x,'periodiciteit',l_employer_det.freq); -- type of period*
825 Tag(x,'datumAanvangTijdvak',l_employer_det.start_date); -- start date period*
826 Tag(x,'datumEindeTijdvak',l_employer_det.end_date); -- end date period*
827 ELSIF p_abp_output_type = 'NLABP_PDF' THEN
828 Tag(x,'periodiciteit',get_lookup_value('NL_WR_PERIOD_TYPE',
829 REPLACE(REPLACE(l_employer_det.freq,'MND','MONTH'),
830 'VWK','LMONTH'),l_lang));
831 Tag(x,'datumAanvangTijdvak',fnd_date.date_to_chardate(
832 to_date(l_employer_det.start_date,'YYYY-MM-DD')));
833 Tag(x,'datumEindeTijdvak',fnd_date.date_to_chardate(
834 to_date(l_employer_det.end_date,'YYYY-MM-DD')));
835 END IF;
836 Tag(x,'referentieAansluiting',l_employer_det.ref_er); -- ref employer
837 -- Generate xml for Employer details end
838
839 fnd_file.put_line(fnd_file.log,'Employer Id '||l_employer_det.org_id);
840
841 FOR v_asg_action_id IN csr_get_asg_action_id(p_action_context_id,l_employer_det.org_id) LOOP
842 FOR v_child_act_id IN csr_get_child_act_id(v_asg_action_id.assignment_action_id)
843 LOOP
844 fnd_file.put_line(fnd_file.log,'Asg Act Id: '||v_child_act_id.ass_act_id);
845 --Find if assignment context is archived. If yes, generate xml for all the contexts
846 OPEN csr_chk_asg_ctxt(v_child_act_id.ass_act_id);
847 FETCH csr_chk_asg_ctxt INTO l_ctxt;
848 IF csr_chk_asg_ctxt%FOUND THEN
849 fnd_file.put_line(fnd_file.log,'Asg Context Found: ');
850 CLOSE csr_chk_asg_ctxt;
851 gen_body_xml_main(v_child_act_id.ass_act_id,p_abp_output_type,x);
852 ELSE
853 CLOSE csr_chk_asg_ctxt;
854 END IF;
855 END LOOP;
856 END LOOP;
857
858 fnd_file.put_line(fnd_file.log,'Withdrawal Details');
859 fnd_file.put_line(fnd_file.log,'p_action_context_id: '||p_action_context_id);
860 OPEN csr_asg_withdrawal(p_action_context_id);
861 FETCH csr_asg_withdrawal INTO l_asg_withdrawal;
862 IF csr_asg_withdrawal%FOUND THEN
863 CLOSE csr_asg_withdrawal;
864 FOR v_csr_asg_withdrawal IN csr_asg_withdrawal(p_action_context_id) LOOP
865 Tag(x,'inkomstenperiodeIntrekking','_START_'); -- + withdrawal pay period details*
866 Tag(x,'personeelsnummer',v_csr_asg_withdrawal.emp_num); -- emp no.*
867 Tag(x,'nummer',v_csr_asg_withdrawal.asg_num); -- asg no.*
868 IF p_abp_output_type = 'NLABP_XML' THEN
869 Tag(x,'datumAanvangInkomstenperiode',v_csr_asg_withdrawal.start_date); -- start date pay period*
870 Tag(x,'bsn',v_csr_asg_withdrawal.sofi_num); -- BSN/Sofi no.*
871 Tag(x,'indicatieIntrekkingIkv',v_csr_asg_withdrawal.code_withdrawal); -- code withdrawal asg
872 ELSIF p_abp_output_type = 'NLABP_PDF' THEN
873 Tag(x,'datumAanvangInkomstenperiode',fnd_date.date_to_chardate(
874 to_date(v_csr_asg_withdrawal.start_date,'YYYY-MM-DD')));
875 Tag(x,'bsn',v_csr_asg_withdrawal.sofi_num);
876 Tag(x,'indicatieIntrekkingIkv',get_lookup_value('HR_NL_YES_NO',
877 REPLACE(REPLACE(v_csr_asg_withdrawal.code_withdrawal
878 ,'ja','Y'),'nee','N'),l_lang));
879 END IF;
880 Tag(x,'inkomstenperiodeIntrekking','_END_'); -- - withdrawal pay period details*
881 END LOOP;
882 ELSE
883 CLOSE csr_asg_withdrawal;
884 END IF;
885
886 FOR l_er_total in 1..7 LOOP
887 IF g_er_amount(l_er_total).flag = 1 THEN
888 -- Generate xml for Employer Totals begin
889 Tag(x,'collectieveAangiftePensioenVerzekeringAansluiting','_START_'); -- + totals per indv employer details*
890 Tag(x,'productkeuze',substr(tPension(l_er_total),1,6)); -- pension type*
891 Tag(x,'productvariant',substr(tPension(l_er_total),8,1)); -- pension type variant*
892 IF g_er_amount(l_er_total).pension_base <> 0 THEN
893 IF p_abp_output_type = 'NLABP_PDF' THEN
894 Tag(x,'productLoon',to_char(round(g_er_amount(l_er_total).pension_base,2),'FM9G999G999G990D00'));
895 ELSE
896 Tag(x,'productLoon',to_char(round(g_er_amount(l_er_total).pension_base,2),'fm9999999990.00')); -- ABP pension base*
897 END IF;
898 END IF;
899 IF g_er_amount(l_er_total).contr_base <> 0 THEN
900 IF p_abp_output_type = 'NLABP_PDF' THEN
901 Tag(x,'premiegrondslag',to_char(round(g_er_amount(l_er_total).contr_base,2),'FM9G999G999G990D00'));
902 ELSE
903 Tag(x,'premiegrondslag',to_char(round(g_er_amount(l_er_total).contr_base,2),'fm9999999990.00')); -- contribution base*
904 END IF;
905 END IF;
906 IF g_er_amount(l_er_total).contr_amt <> 0 THEN
907 IF p_abp_output_type = 'NLABP_PDF' THEN
908 Tag(x,'premie',to_char(round(g_er_amount(l_er_total).contr_amt,2),'FM9G999G999G990D00'));
909 ELSE
910 Tag(x,'premie',to_char(round(g_er_amount(l_er_total).contr_amt,2),'fm9999999990.00')); -- contribution amt
911 END IF;
912 END IF;
913 Tag(x,'collectieveAangiftePensioenVerzekeringAansluiting','_END_'); -- - totals per indv employer details*
914 -- Generate xml for Employer Totals end
915
916 -- Store data for Totals per file
917 g_tot_amount(l_er_total).pension_base := g_tot_amount(l_er_total).pension_base + g_er_amount(l_er_total).pension_base;
918 g_tot_amount(l_er_total).contr_base := g_tot_amount(l_er_total).contr_base + g_er_amount(l_er_total).contr_base;
919 g_tot_amount(l_er_total).contr_amt := g_tot_amount(l_er_total).contr_amt + g_er_amount(l_er_total).contr_amt;
920 g_tot_amount(l_er_total).flag := 1; --flag to indicate which pension types are to be reported
921 END IF;
922 END LOOP;
923
924 Tag(x,'administratieveEenheid','_END_'); -- - employer details*
925
926 fnd_file.put_line(fnd_file.log,'X====gen_body_xml==========================================X');
927 EXCEPTION
928 WHEN OTHERS THEN
929 fnd_file.put_line(fnd_file.log,'Exception in gen_body_xml SQL-ERRM :'||SQLERRM);
930
931 END gen_body_xml;
932
933 BEGIN
934 fnd_file.put_line(fnd_file.log,'+====Generate====================+'|| p_action_context_id);
935
936 --init
937 xXMLTable.delete;
938
939 FOR i in 1..7 LOOP
940 g_tot_amount(i).pension_base:=0;
941 g_tot_amount(i).contr_base:=0;
942 g_tot_amount(i).contr_amt:=0;
943 END LOOP;
944
945 tPension(1):='OPNP';
946 tPension(2):='AAOP';
947 tPension(3):='OPNP-I-A';
948 tPension(4):='OPNP-I-B';
949 tPension(5):='OPNP-I-C';
950 tPension(6):='PPP-I';
951 tPension(7):='VPL';
952 --init
953
954 OPEN csr_get_file_details(p_action_context_id);
955 FETCH csr_get_file_details INTO l_file_details;
956 CLOSE csr_get_file_details;
957
958
959 fnd_file.put_line(fnd_file.log,'Sender Details ');
960
961 IF p_abp_output_type = 'NLABP_PDF' THEN
962 Tag(x,'leveringAPG','_START_');
963 END IF;
964
965 -- Generate xml for File and Sender details begin
966 IF p_abp_output_type = 'NLABP_XML' THEN
967 Tag(x,'leveringAPG','_START_NS_'); -- + ABP*
968 Tag(x,'nummerLeverancier',l_file_details.reg_num_sender); -- ABP reg. no. sender*
969 Tag(x,'periode',l_file_details.period); -- reporting period*
970 Tag(x,'volgnummer',l_file_details.seq_num); -- seq. no. period*
971 Tag(x,'versieBericht',l_file_details.version); -- file version*
972 Tag(x,'datumtijdLeveringaanmaak',l_file_details.creation_date); -- date+time of creation*
973 Tag(x,'soort',l_file_details.period_type); -- period or correction*
974 Tag(x,'type',l_file_details.prod_test); -- production or test file*
975 Tag(x,'naamLeverancier',l_file_details.name_sender); -- name sender
976 Tag(x,'gebruiktSoftwarepakket','Oracle Payroll'); -- used software
977 Tag(x,'versieSoftwarepakket',l_file_details.release); -- ver no
978 Tag(x,'referentieLevering',l_file_details.ref_file); -- ref file
979 END IF;
980 -- Generate xml for File and Sender details end
981
982 -- General xml for Employer and Employee details begin
983 gen_body_xml(x);
984 -- General xml for Employer and Employee details end
985
986 fnd_file.put_line(fnd_file.log,'Totals per file Details ');
987 -- Generate xml for Totals per file begin
988 FOR l_total in 1..7 LOOP
989 IF g_er_amount(l_total).flag = 1 THEN
990 Tag(x,'collectievePensioenVerzekeringsgegevensLevering','_START_'); -- + totals per file details*
991 Tag(x,'productkeuze',substr(tPension(l_total),1,6)); -- pension type*
992 Tag(x,'productvariant',substr(tPension(l_total),8,1)); -- pension type variant*
993 IF g_tot_amount(l_total).pension_base <> 0 THEN
994 IF p_abp_output_type = 'NLABP_PDF' THEN
995 Tag(x,'productLoon',to_char(round(g_tot_amount(l_total).pension_base,2),'FM9G999G999G990D00'));
996 ELSE
997 Tag(x,'productLoon',to_char(round(g_tot_amount(l_total).pension_base,2),'fm9999999990.00')); -- ABP pension base*
998 END IF;
999 END IF;
1000 IF g_tot_amount(l_total).contr_base <> 0 THEN
1001 IF p_abp_output_type = 'NLABP_PDF' THEN
1002 Tag(x,'premiegrondslag',to_char(round(g_tot_amount(l_total).contr_base,2),'FM9G999G999G990D00'));
1003 ELSE
1004 Tag(x,'premiegrondslag',to_char(round(g_tot_amount(l_total).contr_base,2),'fm9999999990.00')); -- contribution base*
1005 END IF;
1006 END IF;
1007 IF g_tot_amount(l_total).contr_amt <> 0 THEN
1008 IF p_abp_output_type = 'NLABP_PDF' THEN
1009 Tag(x,'premie',to_char(round(g_tot_amount(l_total).contr_amt,2),'FM9G999G999G990D00'));
1010 ELSE
1011 Tag(x,'premie',to_char(round(g_tot_amount(l_total).contr_amt,2),'fm9999999990.00')); -- contribution amt
1012 END IF;
1013 END IF;
1014 Tag(x,'collectievePensioenVerzekeringsgegevensLevering','_END_'); -- - totals per file details*
1015 END IF;
1016 END LOOP;
1017 -- Generate xml for Totals per file end
1018
1019 Tag(x,'leveringAPG','_END_'); -- - ABP*
1020
1021 write_to_clob(p_xmltable => xXMLtable
1022 ,p_clob => l_xml
1023 ,p_abp_output_type => p_abp_output_type
1024 ,p_payroll_action_id => p_action_context_id);
1025
1026 p_xml := l_xml;
1027 dbms_lob.freeTemporary(l_xml);
1028
1029 fnd_file.put_line(fnd_file.log,'X====Generate====================X '|| p_action_context_id);
1030
1031 /* 14832638 - Set CP Output type to XML for XML report */
1032 IF p_abp_output_type='NLABP_XML' THEN
1033 UPDATE fnd_concurrent_requests
1034 SET output_file_type = 'XML'
1035 WHERE request_id = fnd_global.conc_request_id;
1036 END IF;
1037
1038 EXCEPTION
1039 WHEN OTHERS THEN
1040 hr_utility.trace('Exception in GENERATE : '||SQLERRM);
1041 raise;
1042 END GENERATE;
1043
1044 END PAY_NL_ABP_REPORT;
1045