[Home] [Help]
PACKAGE BODY: APPS.PAY_NL_NWR_XML_EXTRACT_PKG
Source
1 PACKAGE BODY pay_nl_nwr_xml_extract_pkg as
2 /* $Header: pynlwrep.pkb 120.26.12020000.6 2013/01/11 09:32:01 mkuppuch ship $ */
3 --
4
5 TYPE char_tab IS TABLE OF pay_action_information.action_information1%type INDEX BY BINARY_INTEGER;
6 g_xml_nwr char_tab;
7 --
8 g_action_ctx_id NUMBER;
9 sqlstr DBMS_SQL.VARCHAR2S;
10 l_cntr_sql NUMBER;
11 g_year VARCHAR2(10);
12 g_report_type VARCHAR2(20);
13 g_payroll_type VARCHAR2(40) DEFAULT NULL; --8552196
14 EOL VARCHAR2(5) := fnd_global.local_chr(10); --7283669
15 g_SOURCE VARCHAR2(4000); --11900414
16 g_TARGET VARCHAR2(4000); --11900414
17 --
18 -------------------------------------------------------------------------------
19 -- get_IANA_charset
20 -------------------------------------------------------------------------------
21 FUNCTION get_IANA_charset RETURN VARCHAR2 IS
22 CURSOR csr_get_iana_charset IS
23 SELECT tag
24 FROM fnd_lookup_values
25 WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
26 AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
27 INSTR(USERENV('LANGUAGE'), '.') + 1)
28 AND language = 'US';
29
30 lv_iana_charset fnd_lookup_values.tag%type;
31 BEGIN
32 OPEN csr_get_iana_charset;
33 FETCH csr_get_iana_charset INTO lv_iana_charset;
34 CLOSE csr_get_iana_charset;
35
36 hr_utility.trace('IANA Charset = '||lv_iana_charset);
37 RETURN (lv_iana_charset);
38 END get_IANA_charset;
39 --
40 -------------------------------------------------------------------------------
41 -- TO_UTF8
42 --------------------------------------------------------------------------------
43 FUNCTION TO_UTF8(str in varchar2 )RETURN VARCHAR2 AS
44 db_charset varchar2(120);
45 BEGIN
46 SELECT value
47 INTO db_charset
48 FROM nls_database_parameters
49 WHERE parameter = 'NLS_CHARACTERSET';
50 RETURN CONVERT(str,'UTF8',db_charset);
51 END;
52 -------------------------------------------------------------------------------
53 -- WRITETOCLOB
54 --------------------------------------------------------------------------------
55 PROCEDURE WritetoCLOB (p_xfdf_string out nocopy clob) IS
56 l_str VARCHAR2(240);
57 l_str1 VARCHAR2(4000);
58 l_concat_str VARCHAR2(32000);
59 l_len NUMBER;
60 BEGIN
61 --l_str := '<?xml version="1.0" encoding="UTF-8"?> <Loonaangifte></Loonaangifte>';
62 l_str := '<?xml version="1.0" encoding="' || get_IANA_charset || '"?> <Loonaangifte></Loonaangifte>';
63 dbms_lob.createtemporary(p_xfdf_string,FALSE,DBMS_LOB.CALL);
64 dbms_lob.open(p_xfdf_string,dbms_lob.lob_readwrite);
65 IF g_xml_nwr.count > 0 THEN
66 l_concat_str := '';
67 FOR ctr_table IN g_xml_nwr.FIRST .. g_xml_nwr.LAST LOOP
68 l_concat_str := l_concat_str || g_xml_nwr(ctr_table);
69 l_len := length(l_concat_str);
70 IF l_len > 28000 then
71 dbms_lob.writeAppend( p_xfdf_string, l_len, l_concat_str);
72 l_concat_str := '';
73 END IF;
74 END LOOP;
75 IF length(l_concat_str) > 0 THEN
76 dbms_lob.writeAppend( p_xfdf_string, LENGTH(l_concat_str), l_concat_str);
77 END IF;
78 ELSE
79 --l_str1 := CONVERT(l_str,'UTF8');
80 dbms_lob.writeAppend( p_xfdf_string, LENGTH(l_str), l_str );
81 END IF;
82 END WritetoCLOB;
83 -------------------------------------------------------------------------------
84 -- YES_NO
85 --------------------------------------------------------------------------------
86 FUNCTION yes_no(p_yn VARCHAR2) RETURN VARCHAR2 IS
87 BEGIN
88 IF p_yn = 'J' THEN
89 RETURN 'Y';
90 ELSE
91 RETURN p_yn;
92 END IF;
93 END yes_no;
94 --------------------------------------------------------------------------------
95 -- Name : GET_TAG_DESCRIPTION
96 -- Description : This Function returns the Tag Description when the tag name
100 RETURN VARCHAR2
97 -- is provided .
98 --------------------------------------------------------------------------------
99 FUNCTION get_tag_description(p_tag VARCHAR2)
101 IS
102 CURSOR csr_get_tag_descr(l_tag VARCHAR2)
103 IS
104 SELECT meaning
105 ,nvl(start_date_active,fnd_date.canonical_to_date('0001/01/01 00:00:00')) active_date
106 FROM hr_lookups
107 WHERE lookup_type = 'NL_FORM_LABELS'
108 --AND lookup_code like l_tag||'%'
109 AND (lookup_code like l_tag||'%'||g_year OR lookup_code = l_tag)
110 AND nvl(to_char(start_date_active,'RRRR'),'0001') <= g_year
111 AND nvl(to_char(end_date_active,'RRRR'),'4712') >= g_year
112 AND enabled_flag = 'Y'
113 --ORDER BY 2 desc;
114 ORDER BY active_date desc, lookup_code desc ;
115
116 l_temp_data csr_get_tag_descr%ROWTYPE;
117 l_description VARCHAR2(500);
118 l_tag pay_action_information.action_information1%TYPE;
119
120 BEGIN
121 IF p_tag IS NOT NULL THEN
122 IF g_year >= '2013' AND p_tag='WGHZVW' Then
123 l_tag := 'TOTWGHZVW';
124 ELSE
125 l_tag := p_tag;
126 End IF;
127 OPEN csr_get_tag_descr(l_tag);
128 FETCH csr_get_tag_descr INTO l_temp_data;
129 IF csr_get_tag_descr%NOTFOUND THEN
130 l_description := NULL;
131 ELSE
132 l_description := l_temp_data.meaning;
133 END IF;
134 CLOSE csr_get_tag_descr;
135 ELSE
136 l_description := NULL;
137 END IF;
138 RETURN l_description;
139 END get_tag_description;
140 --------------------------------------------------------------------------------
141 -- Name : GET_TAG_NAME
142 -- Description : This Function returns the Tag Name when the context
143 -- and column are specified.
144 --------------------------------------------------------------------------------
145 FUNCTION get_tag_name (p_context_code VARCHAR2
146 ,p_node VARCHAR2) RETURN VARCHAR2 AS
147 --
148 CURSOR csr_get_tag_name IS
149 SELECT TRANSLATE ((description), ' /','__') tag_name
150 FROM fnd_descr_flex_col_usage_tl
151 WHERE application_id = 801
152 AND source_lang = 'US'
153 AND descriptive_flexfield_name = 'Action Information DF'
154 AND descriptive_flex_context_code = p_context_code
155 AND application_column_name = UPPER(p_node);
156 --
157 CURSOR csr_inv_seg_check(l_tag VARCHAR2) IS
158 SELECT 'N'
159 FROM hr_lookups
160 WHERE lookup_type = 'PAY_NL_INVALID_WR_TAGS'
161 AND lookup_code = upper(l_tag)
162 AND description = l_tag
163 AND enabled_flag = 'Y'
164 AND (g_year BETWEEN to_char(start_date_active,'RRRR')
165 AND nvl(to_char(end_date_active,'RRRR'),'4712'));
166
167 CURSOR csr_replace_tag(l_tag VARCHAR2) IS
168 SELECT description
169 FROM hr_lookups
170 WHERE lookup_type = 'PAY_NL_REPLACEMENT_WR_TAGS'
171 AND lookup_code = upper (l_tag)
172 AND enabled_flag = 'Y'
173 AND (g_year BETWEEN to_char(start_date_active,'RRRR')
174 AND nvl(to_char(end_date_active,'RRRR'),'4712'));
175
176 l_tag_name fnd_descr_flex_col_usage_tl.description%TYPE;
177 l_display VARCHAR2(1);
178 l_replace hr_lookups.description%TYPE;
179 --
180 BEGIN
181 --
182 IF (p_context_code <> p_node) THEN
183 OPEN csr_get_tag_name;
184 FETCH csr_get_tag_name INTO l_tag_name;
185 CLOSE csr_get_tag_name;
186 ELSE
187 l_tag_name := p_node;
188 END IF;
189 --
190 IF g_year >= '2008' THEN
191 OPEN csr_inv_seg_check(l_tag_name);
192 FETCH csr_inv_seg_check INTO l_display;
193 IF csr_inv_seg_check%FOUND THEN
194 l_tag_name := NULL;
195 END IF;
196 CLOSE csr_inv_seg_check;
197 END IF;
198
199 IF g_year >= '2012' AND p_context_code IN ('NL_WR_NOMINATIVE_REPORT_ADD' ,'NL_WR_NOMINATIVE_REPORT') THEN
200 OPEN csr_replace_tag(l_tag_name);
201 FETCH csr_replace_tag INTO l_replace;
202 IF csr_replace_tag%FOUND THEN
203 l_tag_name := l_replace;
204 END IF;
205 CLOSE csr_replace_tag;
206 END IF;
207
208 RETURN l_tag_name;
209 --
210 END get_tag_name;
211 --
212 --------------------------------------------------------------------------------
213 -- Name : LOAD_XML_INTERNAL
214 -- Description : This procedure loads the global XML cache.
215 --------------------------------------------------------------------------------
216 PROCEDURE load_xml_internal (P_NODE_TYPE VARCHAR2
217 ,P_NODE VARCHAR2
218 ,P_DATA VARCHAR2) AS
219 l_proc_name VARCHAR2(100);
220 l_data pay_action_information.action_information1%TYPE;
221 --
222 BEGIN
223 --
224 IF p_node_type = 'CS' THEN
225 g_xml_nwr (g_xml_nwr.count() + 1) := '<'||p_node||'>'||EOL;
226 ELSIF p_node_type = 'CE' THEN
227 g_xml_nwr (g_xml_nwr.count() + 1) := '</'||p_node||'>'||EOL;
228 ELSIF p_node_type = 'D' AND p_data IS NOT NULL THEN
229 /* Handle special charaters in data */
230 l_data := REPLACE (p_data, '&', '&');
231 l_data := REPLACE (l_data, '>', '>');
232 l_data := REPLACE (l_data, '<', '<');
233 l_data := REPLACE (l_data, '''', ''');
234 l_data := REPLACE (l_data, '"', '"');
235 g_xml_nwr (g_xml_nwr.count() + 1) := '<'||p_node||'>'||l_data||'</'||p_node||'>'||EOL;
239 IF g_SOURCE IS NOT NULL THEN
236 ELSIF p_node_type = 'CD' AND p_data IS NOT NULL THEN
237 l_data := p_data;
238 --11900414 Starts
240 l_data:= translate(l_data,g_SOURCE,g_TARGET);
241 END IF;
242 --11900414 Ends
243 g_xml_nwr (g_xml_nwr.count() + 1) := '<'||p_node||'><![CDATA['||l_data||']]></'||p_node||'>'||EOL;
244 END IF;
245 --
246 END load_xml_internal;
247 --
248 --------------------------------------------------------------------------------
249 -- Name : LOAD_XML
250 -- Description : This procedure loads the global XML cache.
251 --------------------------------------------------------------------------------
252 --
253 PROCEDURE load_xml (p_node_type VARCHAR2
254 ,p_context_code VARCHAR2
255 ,p_node VARCHAR2
256 ,p_data VARCHAR2) AS
257 --
258 CURSOR csr_get_tag_name IS
259 SELECT TRANSLATE ((description), ' /','__') tag_name
260 FROM fnd_descr_flex_col_usage_tl
261 WHERE application_id = 801
262 AND source_lang = 'US'
263 AND descriptive_flexfield_name = 'Action Information DF'
264 AND descriptive_flex_context_code = p_context_code
265 AND application_column_name = UPPER (p_node);
266
267 CURSOR csr_inv_seg_check(l_tag VARCHAR2) IS
268 SELECT 'N'
269 FROM hr_lookups
270 WHERE lookup_type = 'PAY_NL_INVALID_WR_TAGS'
271 AND lookup_code = upper(l_tag)
272 AND description = l_tag
273 AND enabled_flag = 'Y'
274 AND (g_year BETWEEN to_char(start_date_active,'RRRR')
275 AND nvl(to_char(end_date_active,'RRRR'),'4712'));
276
277 CURSOR csr_replace_tag(l_tag VARCHAR2) IS
278 SELECT description
279 FROM hr_lookups
280 WHERE lookup_type = 'PAY_NL_REPLACEMENT_WR_TAGS'
281 AND lookup_code = upper(l_tag)
282 AND enabled_flag = 'Y'
283 AND (g_year BETWEEN to_char(start_date_active,'RRRR')
284 AND nvl(to_char(end_date_active,'RRRR'),'4712'));
285 --
286
287 l_proc_name VARCHAR2(100);
288 l_tag_name VARCHAR2(500);
289 l_data pay_action_information.action_information1%TYPE;
290 l_display VARCHAR2(1);
291 l_replace hr_lookups.description%TYPE;
292
293 --
294 BEGIN
295 --
296 l_display := 'Y';
297 IF p_node_type in ('D','CD') THEN
298 --
299 -- Fetch segment names
300 --
301 OPEN csr_get_tag_name;
302 FETCH csr_get_tag_name INTO l_tag_name;
303 CLOSE csr_get_tag_name;
304 END IF;
305 --
306 IF g_xml_nwr.count() <> 0 AND UPPER(p_node) NOT LIKE 'XAPI%' THEN
307 l_tag_name := nvl(l_tag_name, TRANSLATE(p_node, ' /', '__'));
308 ELSE
309 l_tag_name := p_node;
310 END IF;
311 --
312 IF g_year >= '2008' THEN
313 OPEN csr_inv_seg_check(l_tag_name);
314 FETCH csr_inv_seg_check INTO l_display;
315 IF csr_inv_seg_check%NOTFOUND THEN
316 l_display := 'Y';
317 ELSE
318 l_display := 'N';
319 END IF;
320 CLOSE csr_inv_seg_check;
321 END IF;
322
323 IF g_year >= '2012' AND p_context_code IN ('NL_WR_NOMINATIVE_REPORT_ADD' ,'NL_WR_NOMINATIVE_REPORT') THEN
324 OPEN csr_replace_tag(l_tag_name);
325 FETCH csr_replace_tag INTO l_replace;
326 IF csr_replace_tag%FOUND THEN
327 l_tag_name := l_replace;
328 END IF;
329 CLOSE csr_replace_tag;
330 END IF;
331
332 IF l_display = 'Y' THEN
333 l_data := p_data;
334 IF p_node = 'Description' THEN /***** for Description .. the tag name is fed through the p_data *****/
335 l_data := get_tag_description(UPPER(p_data));
336 END IF;
337 IF g_report_type <> 'NLNWR_XML' AND p_context_code = 'NL_WR_INCOME_PERIOD' AND UPPER(l_tag_name) = 'CDAGH' THEN
338 /** to avoid conflict between CdAGH in XML and CdAGH in the PDF output ***/
339 l_data := NULL;
340 END IF;
341 load_xml_internal (p_node_type, l_tag_name, l_data);
342 END IF;
343 --
344 END load_xml;
345 --
346
347
348 --------------------------------------------------------------------------------
349 -- Name : FLEX_SEG_ENABLED
350 -- Description : This function returns TRUE if an application column is
351 -- registered with given context of Action Information DF.
352 -- Otherwise, it returns false.
353 --------------------------------------------------------------------------------
354 --
355 FUNCTION flex_seg_enabled(p_context_code VARCHAR2
356 ,p_application_column_name VARCHAR2) RETURN BOOLEAN AS
357 --
358 CURSOR csr_seg_enabled IS
359 SELECT 'Y'
360 FROM fnd_descr_flex_col_usage_vl
361 WHERE application_id = 801
362 AND descriptive_flexfield_name LIKE 'Action Information DF'
363 AND descriptive_flex_context_code = p_context_code
364 AND application_column_name LIKE p_application_column_name
365 AND enabled_flag = 'Y';
366 --
367 l_exists varchar2(1);
368 --
369 BEGIN
370 --
371 OPEN csr_seg_enabled;
372 FETCH csr_seg_enabled INTO l_exists;
373 CLOSE csr_seg_enabled;
374 --
375 IF l_exists = 'Y' THEN
376 RETURN (TRUE);
377 ELSE
378 RETURN (FALSE);
379 END IF;
380 --
384 -- Name : BUILD_SQL
381 END flex_seg_enabled;
382 --
383 --------------------------------------------------------------------------------
385 -- Description : This procedure builds dynamic SQL string.
386 --------------------------------------------------------------------------------
387 --
388 PROCEDURE build_sql(p_sqlstr_tab IN OUT NOCOPY DBMS_SQL.VARCHAR2S
389 ,p_cntr IN OUT NOCOPY NUMBER
390 ,p_string VARCHAR2) AS
391 --
392 l_proc_name varchar2(100);
393 --
394 BEGIN
395 p_sqlstr_tab(p_cntr) := p_string;
396 p_cntr := p_cntr + 1;
397 END;
398 --
399 --
400 --------------------------------------------------------------------------------
401 -- Name : GET_LE_NAME
402 -- Description : This function returns the REPORTING NAME of the
403 -- Legal Employer
404 --------------------------------------------------------------------------------
405 --
406 FUNCTION get_le_name(p_payroll_action_id NUMBER) RETURN VARCHAR2 AS
407 --
408 CURSOR csr_get_name(p_organization_id NUMBER) IS
409 SELECT hoi1.org_information14
410 FROM hr_organization_units hou
411 ,hr_organization_information hoi
412 ,hr_organization_information hoi1
413 WHERE hou.organization_id = hoi.organization_id
414 AND hou.organization_id = hoi1.organization_id(+)
415 AND hou.organization_id = p_organization_id
416 AND hoi.org_information_context = 'CLASS'
417 AND hoi1.org_information_context(+) = 'NL_ORG_INFORMATION';
418 --
419 l_name hr_organization_information.org_information14%TYPE;
420 l_legal_emplr_id NUMBER(15);
421 --
422 BEGIN
423 --
424 l_legal_emplr_id := pay_nl_wage_report_pkg.get_parameters(p_payroll_action_id, 'Legal_Employer');
425 --
426 OPEN csr_get_name(l_legal_emplr_id);
427 FETCH csr_get_name INTO l_name;
428 CLOSE csr_get_name;
429 --
430 RETURN l_name;
431 --
432 END get_le_name;
433 --
434 --------------------------------------------------------------------------------
435 -- Name : GENERATE_COLLECTIVE_REPORT
436 -- Description : This Procedure is used to generate the XML part for
437 -- the tags in the Collective Report Part.
438 --------------------------------------------------------------------------------
439 --
440 PROCEDURE generate_collective_report( p_act_context_id NUMBER
441 ,p_type VARCHAR2
442 ,p_start_date VARCHAR2
443 ,p_end_date VARCHAR2
444 ,p_in_not_in VARCHAR2
445 ,p_report_type VARCHAR2) AS
446 --
447 BEGIN
448 --
449 build_sql(sqlstr, l_cntr_sql, 'DECLARE l_col_tag fnd_descr_flex_col_usage_vl.description%TYPE; BEGIN ');
450
451 --
452 build_sql(sqlstr, l_cntr_sql, 'FOR csr_collective_info_rec IN pay_nl_nwr_xml_extract_pkg.csr_collective_info ('||p_act_context_id||',''NL_WR_COLLECTIVE_REPORT'','''|| p_type ||''','||p_start_date||','||p_end_date||') LOOP ');
453 --
454 --LC 2010 -- begin
455 IF g_year >= '2010' THEN
456 IF g_year >= '2013' THEN
457 build_sql(sqlstr, l_cntr_sql, 'IF csr_collective_info_rec.action_information2 '|| p_in_not_in ||' (''IngBijdrZvw'',''TotWghZvw'',''PkAgh'',''PkNwArbvOudWn'',''TotTeBet'') THEN ');
458 ELSE
459 build_sql(sqlstr, l_cntr_sql, 'IF csr_collective_info_rec.action_information2 '|| p_in_not_in ||' (''IngBijdrZvw'',''PkAgh'',''PkNwArbvOudWn'',''PkInDnstOudWn'',''TotTeBet'') THEN ');
460 END IF;
461 ELSE
462 build_sql(sqlstr, l_cntr_sql, 'IF csr_collective_info_rec.action_information2 '|| p_in_not_in ||' (''IngBijdrZvw'',''AGHKort'',''TotTeBet'') THEN ');
463 END IF;
464 --LC 2010 -- end
465 --
466 IF p_report_type = 'NLNWR_XML' THEN
467 build_sql(sqlstr, l_cntr_sql,
468 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, csr_collective_info_rec.action_information2, to_char(fnd_number.canonical_to_number(csr_collective_info_rec.action_information6)));');
469 ELSE
470 --
471 build_sql(sqlstr, l_cntr_sql, 'l_col_tag := pay_nl_nwr_xml_extract_pkg.get_tag_name(csr_collective_info_rec.action_information2,csr_collective_info_rec.action_information2); ');
472 build_sql(sqlstr, l_cntr_sql, 'IF l_col_tag IS NOT NULL THEN ');
473 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''CRRecords'', NULL);');
474 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Tag'', csr_collective_info_rec.action_information2);');
475 --build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'', csr_collective_info_rec.action_information5);');
476 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'', csr_collective_info_rec.action_information2);');
477 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Amount'', to_char(fnd_number.canonical_to_number(csr_collective_info_rec.action_information6)));');
478 --
479 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''CRRecords'', NULL);');
480 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
481 END IF;
482 --
483 build_sql(sqlstr, l_cntr_sql, 'END IF;');
484 --
485 build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
486 build_sql(sqlstr, l_cntr_sql, 'END ;');
487
488 --
489 END generate_collective_report;
490 --
491 --------------------------------------------------------------------------------
495 -- Instead of repeating it thrice, we call this Proc Thrice
492 -- Name : GENERATE_IP_SRG_NR
493 -- Description : This Procedure is used to generate the XML part for
494 -- (Income Period - Sector Rsik Group - Nominative Report).
496 --------------------------------------------------------------------------------
497 --
498 PROCEDURE generate_ip_srg_nr( p_action_context_id NUMBER
499 ,p_type VARCHAR2
500 ,p_report_type VARCHAR2) AS
501 --
502 BEGIN
503 --
504 build_sql(sqlstr, l_cntr_sql, 'FOR csr_income_info_rec IN pay_nl_nwr_xml_extract_pkg.csr_income_info ('||p_action_context_id||',''NL_WR_INCOME_PERIOD'','''||p_type||''',csr_employment_info_rec.action_information_id) LOOP ');
505 --
506 -- Income Period <Inkomstenperiode>
507 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''Inkomstenperiode'', NULL);');
508 --
509 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_INCOME_PERIOD'', ''ACTION_INFORMATION5'', TO_CHAR(fnd_date.canonical_to_date(csr_income_info_rec.action_information5),''YYYY-MM-DD''));');
510 --
511 FOR cntr in 6..19 LOOP --LC 2010 -- Split 6..30 to 6..19 20..27
512 IF flex_seg_enabled ('NL_WR_INCOME_PERIOD', 'ACTION_INFORMATION'||cntr) THEN
513 IF cntr = 13 THEN
514 IF g_year >= '2010' THEN
515 --LC 2010--begin
516 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec.action_information13 = ''1'' OR ');
517 build_sql(sqlstr, l_cntr_sql, 'csr_income_info_rec.action_information13 = ''2'' OR ');
518 build_sql(sqlstr, l_cntr_sql, 'csr_income_info_rec.action_information13 = ''3'' OR ');
519 build_sql(sqlstr, l_cntr_sql, 'csr_income_info_rec.action_information13 = ''4'' THEN ');
520 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndPkAgh'', ''J'');');
521
522 IF g_year < '2013' THEN
523 --LC 2013--begin
524 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information13 = ''6'' THEN ');
525 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndPkNwArbvOudWn'', ''J'');');
526
527 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information13 = ''7'' THEN ');
528 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndPkInDnstOudWn'', ''J'');');
529
530 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information13 = ''8'' THEN ');
531 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndPkAgh'', ''J'');');
532 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndPkNwArbvOudWn'', ''J'');');
533
534 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information13 = ''9'' THEN ');
535 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndPkAgh'', ''J'');');
536 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndPkInDnstOudWn'', ''J'');');
537 ELSE
538 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information13 in (''6'',''8'') THEN ');
539 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndPkNwArbvOudWn'', ''J'');');
540 END IF;
541
542 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
543 --LC 2010--end
544 ELSIF g_year >= '2008' THEN
545 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec.action_information13 IS NULL THEN ');
546 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_INCOME_PERIOD'', ''ACTION_INFORMATION13'', NULL);');
547 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information13 = ''0'' THEN ');
548 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_INCOME_PERIOD'', ''ACTION_INFORMATION13'', ''0'');');
549
550 --abraghun--7668628--LC 2009 changes Begin
551 IF g_year >= '2009' THEN
552
553 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information13 = ''1'' OR ');
554 build_sql(sqlstr, l_cntr_sql, 'csr_income_info_rec.action_information13 = ''2'' OR ');
555 build_sql(sqlstr, l_cntr_sql, 'csr_income_info_rec.action_information13 = ''3'' OR ');
556 build_sql(sqlstr, l_cntr_sql, 'csr_income_info_rec.action_information13 = ''4'' THEN ');
557 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_INCOME_PERIOD'', ''ACTION_INFORMATION13'', ''5'');');
558 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information13 = ''6'' OR ');
559 build_sql(sqlstr, l_cntr_sql, 'csr_income_info_rec.action_information13 = ''7'' OR ');
560 build_sql(sqlstr, l_cntr_sql, 'csr_income_info_rec.action_information13 = ''8'' OR ');
561 build_sql(sqlstr, l_cntr_sql, 'csr_income_info_rec.action_information13 = ''9'' THEN ');
562 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_INCOME_PERIOD'', ''ACTION_INFORMATION13'', csr_income_info_rec.action_information13);');
563
564 ELSE
565 build_sql(sqlstr, l_cntr_sql, 'ELSE ');
566 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_INCOME_PERIOD'', ''ACTION_INFORMATION13'', ''5'');');
567 END IF;
568 --abraghun--7668628--LC 2009 changes End
572 END IF;
569 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
570 ELSE
571 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_INCOME_PERIOD'', ''ACTION_INFORMATION13'', csr_income_info_rec.action_information13);');
573 ELSE
574 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_INCOME_PERIOD'', ''ACTION_INFORMATION'|| cntr ||''', csr_income_info_rec.action_information' || cntr ||');');
575 END IF;
576 END IF;
577 END LOOP;
578 -- LC 2010 -- New Tags IndPma, IndWgldOudRegl begin
579 IF g_year >= '2010' THEN
580 FOR cntr in 28..29 LOOP --LC 2010 -- Split 6..30 to 6..19 28..29 20..27
581 IF flex_seg_enabled ('NL_WR_INCOME_PERIOD', 'ACTION_INFORMATION'||cntr) THEN
582 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_INCOME_PERIOD'', ''ACTION_INFORMATION'|| cntr ||''', csr_income_info_rec.action_information' || cntr ||');');
583 END IF;
584 END LOOP;
585 END IF;
586
587 IF flex_seg_enabled ('NL_WR_INCOME_PERIOD', 'ACTION_INFORMATION20') THEN
588 IF g_year >= '2013' THEN
589 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec.action_information20 = ''C'' THEN ');
590 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_INCOME_PERIOD'', ''ACTION_INFORMATION20'', ''K'');');
591 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information20 = ''D'' THEN ');
592 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_INCOME_PERIOD'', ''ACTION_INFORMATION20'', ''L'');');
593 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information20 = ''E'' THEN ');
594 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_INCOME_PERIOD'', ''ACTION_INFORMATION20'', ''M'');');
595 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information20 = ''F'' THEN ');
596 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_INCOME_PERIOD'', ''ACTION_INFORMATION20'', ''N'');');
597 build_sql(sqlstr, l_cntr_sql, 'ELSE ');
598 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_INCOME_PERIOD'', ''ACTION_INFORMATION20'', csr_income_info_rec.action_information20);');
599 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
600 ELSE
601 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_INCOME_PERIOD'', ''ACTION_INFORMATION20'', csr_income_info_rec.action_information20);');
602 END IF;
603 END IF;
604 -- LC 2010 -- New Tags IndPma, IndWgldOudRegl end
605 FOR cntr in 21..26 LOOP --LC 2010 -- Split 6..30 to 6..19 28..29 20..27
606 IF flex_seg_enabled ('NL_WR_INCOME_PERIOD', 'ACTION_INFORMATION'||cntr) THEN
607 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_INCOME_PERIOD'', ''ACTION_INFORMATION'|| cntr ||''', csr_income_info_rec.action_information' || cntr ||');');
608 END IF;
609 END LOOP;
610 --LC 2013 --Begin
611 IF g_year >= '2013' THEN
612 IF flex_seg_enabled ('NL_WR_INCOME_PERIOD', 'ACTION_INFORMATION30') THEN
613 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_INCOME_PERIOD'', ''ACTION_INFORMATION'|| '30' ||''', csr_income_info_rec.action_information' || '30' ||');');
614 END IF;
615 END IF;
616
617 IF flex_seg_enabled ('NL_WR_INCOME_PERIOD', 'ACTION_INFORMATION27') THEN
618 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_INCOME_PERIOD'', ''ACTION_INFORMATION'|| '27' ||''', csr_income_info_rec.action_information' || '27' ||');');
619 END IF;
620 --LC 2013 --End
621
622 IF p_report_type <> 'NLNWR_XML' THEN
623 --
624 --build_sql(sqlstr, l_cntr_sql, 'Declare l_code_contract VARCHAR2(50); Begin ');
625 --build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec.action_information9 = ''O'' THEN l_code_contract := ''Permanent'' ; ELSIF csr_income_info_rec.action_information9 = ''B'' THEN l_code_contract := ''Fixed Term''; END IF; ');
626 --build_sql(sqlstr, l_cntr_sql, 'End; ');
627
628 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''SrtIVM'', hr_general.decode_lookup(''NL_INCOME_CODE'', csr_income_info_rec.action_information6));');
629 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CdAardM'', hr_general.decode_lookup(''NL_LABOR_RELATION_CODE'', csr_income_info_rec.action_information7));');
630 --LC 2010-- begin
631 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec.action_information8 IN (''AF'',''BF'',''CF'',''DF'',''EF'') THEN ');
632 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CdInvlVplM'',
633 hr_general.decode_lookup(''NL_INFLUENCE_CODE'', SUBSTR(csr_income_info_rec.action_information8,1,1))||'' / ''||
634 hr_general.decode_lookup(''NL_INFLUENCE_CODE'', ''F''));');
635 build_sql(sqlstr, l_cntr_sql, 'ELSE pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CdInvlVplM'', hr_general.decode_lookup(''NL_INFLUENCE_CODE'', csr_income_info_rec.action_information8));');
636 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
637 --LC 2010-- end
638
639 --build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CdBepTdM'', hr_general.decode_lookup(''NL_EMPLOYMENT_CATG'', csr_income_info_rec.action_information9));');
640 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CdBepTdM'', hr_general.decode_lookup(NVL(csr_message_info_rec.action_information12,''NL_EMPLOYMENT_CATG''), csr_income_info_rec.action_information9));');
644 --
641 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''FsIndFZM'', hr_general.decode_lookup(''NL_TEMP_LABOR_CODE'', csr_income_info_rec.action_information10));');
642 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndRglmArbM'', hr_general.decode_lookup(''YES_NO'',pay_nl_nwr_xml_extract_pkg.yes_no(csr_income_info_rec.action_information11)));');
643 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CAOM'', hr_general.decode_lookup(''NL_COLLECTIVE_AGREEMENT'', csr_income_info_rec.action_information12));');
645 IF g_year >= '2010' THEN
646 --LC 2010--begin
647 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec.action_information13 = ''1'' OR ');
648 build_sql(sqlstr, l_cntr_sql, 'csr_income_info_rec.action_information13 = ''2'' OR ');
649 build_sql(sqlstr, l_cntr_sql, 'csr_income_info_rec.action_information13 = ''3'' OR ');
650 build_sql(sqlstr, l_cntr_sql, 'csr_income_info_rec.action_information13 = ''4'' THEN ');
651 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndPkAghM'', hr_general.decode_lookup(''YES_NO'',''Y''));');
652
653 IF g_year < '2013' THEN
654 --LC 2013--begin
655 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information13 = ''6'' THEN ');
656 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndPkNwArbvOudWnM'', hr_general.decode_lookup(''YES_NO'',''Y''));');
657
658 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information13 = ''7'' THEN ');
659 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndPkInDnstOudWnM'', hr_general.decode_lookup(''YES_NO'',''Y''));');
660
661
662 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information13 = ''8'' THEN ');
663 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndPkAghM'', hr_general.decode_lookup(''YES_NO'',''Y''));');
664 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndPkNwArbvOudWnM'', hr_general.decode_lookup(''YES_NO'',''Y''));');
665
666 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information13 = ''9'' THEN ');
667 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndPkAghM'', hr_general.decode_lookup(''YES_NO'',''Y''));');
668 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndPkInDnstOudWnM'', hr_general.decode_lookup(''YES_NO'',''Y''));');
669 ELSE
670 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information13 in (''6'',''8'') THEN ');
671 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndPkNwArbvOudWnM'', hr_general.decode_lookup(''YES_NO'',''Y''));');
672 END IF;
673 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
674 --LC 2010--end
675 ELSE
676 IF g_year < '2008' THEN
677 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CdAGH'', hr_general.decode_lookup(''NL_FORM_LABELS'',''CDAGH''));');
678 ELSE
679 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CdAGH'', hr_general.decode_lookup(''NL_FORM_LABELS'',''CDAGH_2008''));');
680 END IF;
681 IF g_year >= '2008' THEN
682 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec.action_information13 IS NULL THEN ');
683 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CdAGHM'', NULL); ');
684 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information13 = ''0'' THEN ');
685 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CdAGHM'',csr_income_info_rec.action_information13||'' ''|| hr_general.decode_lookup(''NL_FORM_LABELS'',''LC0_CDAGH'')); ');
686 --abraghun--7668628--LC 2009 changes Begin
687 IF g_year >= '2009' THEN
688 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information13 = ''1'' OR ');
689 build_sql(sqlstr, l_cntr_sql, 'csr_income_info_rec.action_information13 = ''2'' OR ');
690 build_sql(sqlstr, l_cntr_sql, 'csr_income_info_rec.action_information13 = ''3'' OR ');
691 build_sql(sqlstr, l_cntr_sql, 'csr_income_info_rec.action_information13 = ''4'' THEN ');
692 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CdAGHM'',''5 ''|| hr_general.decode_lookup(''NL_FORM_LABELS'',''LC5_CDAGH'')); ');
693
694 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information13 = ''6'' THEN ');
695 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CdAGHM'',''6 ''|| hr_general.decode_lookup(''NL_FORM_LABELS'',''LC6_CDAGH'')); ');
696
697 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information13 = ''7'' THEN ');
698 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CdAGHM'',''7 ''|| hr_general.decode_lookup(''NL_FORM_LABELS'',''LC7_CDAGH'')); ');
699
700 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information13 = ''8'' THEN ');
701 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CdAGHM'',''8 ''|| hr_general.decode_lookup(''NL_FORM_LABELS'',''LC8_CDAGH'')); ');
702
703 build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information13 = ''9'' THEN ');
704 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CdAGHM'',''9 ''|| hr_general.decode_lookup(''NL_FORM_LABELS'',''LC9_CDAGH'')); ');
705
706 ELSE
707 build_sql(sqlstr, l_cntr_sql, 'ELSE ');
708 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CdAGHM'',''5 ''|| hr_general.decode_lookup(''NL_FORM_LABELS'',''LC5_CDAGH'')); ');
709 END IF;
713 build_sql(sqlstr, l_cntr_sql,
710 --abraghun--7668628--LC 2009 changes End
711 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
712 ELSE
714 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CdAGHM'', csr_income_info_rec.action_information13||'' ''||hr_general.decode_lookup(''NL_LABOUR_HANDICAP_DISC_TYPE'',csr_income_info_rec.action_information13));');
715 END IF;
716 END IF; --LC 2010--
717 --
718 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndLhKortM'', hr_general.decode_lookup(''YES_NO'',pay_nl_nwr_xml_extract_pkg.yes_no(csr_income_info_rec.action_information14)));');
719 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CdRdnGnBijtM'', hr_general.decode_lookup(''NL_COMPANY_CAR_USAGE_CODE'', csr_income_info_rec.action_information15));');
720 --build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''LbTabM'', hr_general.decode_lookup(''NL_TAX_CODE'', csr_income_info_rec.action_information16));');
721 IF g_year = '2006' OR g_year >'2012' THEN
722 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndWAOD'', hr_general.decode_lookup(''NL_FORM_LABELS'',''INDWAO_2006''));');
723 ELSE
724 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndWAOD'', hr_general.decode_lookup(''NL_FORM_LABELS'',''INDWAO''));');
725 END IF;
726 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndWAOM'', hr_general.decode_lookup(''YES_NO'',pay_nl_nwr_xml_extract_pkg.yes_no(csr_income_info_rec.action_information17)));');
727 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndWWM'', hr_general.decode_lookup(''YES_NO'',pay_nl_nwr_xml_extract_pkg.yes_no(csr_income_info_rec.action_information18)));');
728 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndZWM'', hr_general.decode_lookup(''YES_NO'',pay_nl_nwr_xml_extract_pkg.yes_no(csr_income_info_rec.action_information19)));');
729 --LC 2010--begin New Tags from 2010.
730 IF g_year >= '2010' THEN
731 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndPmaM'', hr_general.decode_lookup(''YES_NO'',pay_nl_nwr_xml_extract_pkg.yes_no(csr_income_info_rec.action_information28)));');
732 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndWgldOudReglM'', hr_general.decode_lookup(''YES_NO'',pay_nl_nwr_xml_extract_pkg.yes_no(csr_income_info_rec.action_information29)));');
733 END IF;
734 --LC 2010--end
735
736 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CdZvwM'', hr_general.decode_lookup(''NL_ZVW_INSURED'', csr_income_info_rec.action_information20));');
737 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndVakBnM'', hr_general.decode_lookup(''YES_NO'',pay_nl_nwr_xml_extract_pkg.yes_no(csr_income_info_rec.action_information21)));');
738 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndSA71M'', hr_general.decode_lookup(''YES_NO'',pay_nl_nwr_xml_extract_pkg.yes_no(csr_income_info_rec.action_information22)));');
739 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndSA72M'', hr_general.decode_lookup(''YES_NO'',pay_nl_nwr_xml_extract_pkg.yes_no(csr_income_info_rec.action_information23)));');
740 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndSA43M'', hr_general.decode_lookup(''YES_NO'',pay_nl_nwr_xml_extract_pkg.yes_no(csr_income_info_rec.action_information24)));');
741 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndSA03M'', hr_general.decode_lookup(''YES_NO'',pay_nl_nwr_xml_extract_pkg.yes_no(csr_income_info_rec.action_information25)));');
742 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CdIncInkVermM'', hr_general.decode_lookup(''NL_INCOME_DECREASE_CODE'', csr_income_info_rec.action_information26));');
743 --LC 2013 --Begin
744 IF g_year >= '2013' THEN
745 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndTijdHkM'', hr_general.decode_lookup(''YES_NO'',pay_nl_nwr_xml_extract_pkg.yes_no(csr_income_info_rec.action_information30)));');
746 END IF;
747 --LC 2013 --End
748 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''IndAanvUitkM'', hr_general.decode_lookup(''YES_NO'',pay_nl_nwr_xml_extract_pkg.yes_no(csr_income_info_rec.action_information27)));');
749 END IF;
750 --
751 -- Income Period </Inkomstenperiode>
752 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''Inkomstenperiode'', NULL);');
753 --
754 build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
755 --
756 build_sql(sqlstr, l_cntr_sql, 'FOR csr_income_info_rec IN pay_nl_nwr_xml_extract_pkg.csr_income_info ('||p_action_context_id||',''NL_WR_SWMF_SECTOR_RISK_GROUP'',''SECTOR_RISK_GROUP'',csr_employment_info_rec.action_information_id) LOOP ');
757 --
758 -- Sector Risk Group <SectorRisicogroep>
759 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''SectorRisicogroep'', NULL);');
760 --
761 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_SWMF_SECTOR_RISK_GROUP'', ''ACTION_INFORMATION5'', TO_CHAR(fnd_date.canonical_to_date(csr_income_info_rec.action_information5),''YYYY-MM-DD''));');
762 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_SWMF_SECTOR_RISK_GROUP'', ''ACTION_INFORMATION6'', TO_CHAR(fnd_date.canonical_to_date(csr_income_info_rec.action_information6),''YYYY-MM-DD''));');
763 FOR cntr in 7..8 LOOP
764 IF flex_seg_enabled ('NL_WR_SWMF_SECTOR_RISK_GROUP', 'ACTION_INFORMATION'||cntr) THEN
765 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_SWMF_SECTOR_RISK_GROUP'', ''ACTION_INFORMATION'|| cntr ||''', csr_income_info_rec.action_information' || cntr ||');');
766 END IF;
767 END LOOP;
768 --
772 build_sql(sqlstr, l_cntr_sql, 'FOR csr_income_info_rec1 IN pay_nl_nwr_xml_extract_pkg.csr_income_info1 ('||p_action_context_id||',''NL_WR_NOMINATIVE_REPORT_ADD'','''||p_type||''',csr_employment_info_rec.action_information_id) LOOP ');
769 --LC 2010-- begin
770 --
771 IF g_year >= '2010' THEN
773 --
774 FOR cntr1 in 15..15 LOOP --LC 2010 -- New Tag PrLnPrSectFnds
775 IF flex_seg_enabled ('NL_WR_NOMINATIVE_REPORT_ADD', 'ACTION_INFORMATION'||cntr1) THEN
776 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec1.action_information' || cntr1 ||' IS NOT NULL THEN ');
777 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_NOMINATIVE_REPORT_ADD'',
778 ''ACTION_INFORMATION'|| cntr1 ||''', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec1.action_information' || cntr1 ||'),''FM999999999999999999990.00''));');
779 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
780 END IF;
781 END LOOP;
782 build_sql(sqlstr, l_cntr_sql, 'END LOOP; ');
783
784 build_sql(sqlstr, l_cntr_sql, 'FOR csr_income_info_rec2 IN pay_nl_nwr_xml_extract_pkg.csr_income_info1 ('||p_action_context_id||',''NL_WR_NOMINATIVE_REPORT'','''||p_type||''',csr_employment_info_rec.action_information_id) LOOP ');
785 FOR cntr2 in 19..19 LOOP --From 2010, Tag moved from Employee Data Part (PrWgf).
786 IF flex_seg_enabled ('NL_WR_NOMINATIVE_REPORT', 'ACTION_INFORMATION'||cntr2) THEN
787 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec2.action_information' || cntr2 ||' IS NOT NULL THEN ');
788 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_NOMINATIVE_REPORT'', ''ACTION_INFORMATION'|| cntr2 ||''',
789 TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec2.action_information' || cntr2 ||'),''FM999999999999999999990.00''));');
790 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
791 END IF;
792 END LOOP;
793 build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
794 END IF;
795 --
796 --LC 2010-- end
797 -- Sector Risk Group </SectorRisicogroep>
798 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''SectorRisicogroep'', NULL);');
799 --
800 build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
801 --
802 build_sql(sqlstr, l_cntr_sql, 'DECLARE l_tag fnd_descr_flex_col_usage_vl.description%TYPE; BEGIN ');
803 --
804 -- Nominative Report <NominatieveAangifte>
805 --build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NominatieveAangifte'', NULL);');
806 --
807 build_sql(sqlstr, l_cntr_sql, 'FOR csr_income_info_rec IN pay_nl_nwr_xml_extract_pkg.csr_income_info ('||p_action_context_id||',''NL_WR_NOMINATIVE_REPORT'','''||p_type||''',csr_employment_info_rec.action_information_id) LOOP ');
808 --
809 -- Nominative Report <NominatieveAangifte>
810 IF g_year = '2006' OR p_report_type <> 'NLNWR_XML' THEN
811 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NominatieveAangifte'', NULL);');
812 ELSE
813 IF g_year >= '2008'
814 AND g_payroll_type <> 'YEARLY' --8552196
815 THEN
816 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''Werknemersgegevens'', NULL);');
817 ELSE
818 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''Werknemergegevens'', NULL);');
819 END IF;
820 END IF;
821 --
822 FOR cntr in 5..6 LOOP --LC 2010-- Changed from 5..14 to 5..6
823 IF flex_seg_enabled ('NL_WR_NOMINATIVE_REPORT', 'ACTION_INFORMATION'||cntr) THEN
824 --
825 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec.action_information' || cntr ||' IS NOT NULL THEN ');
826 --
827 IF p_report_type = 'NLNWR_XML' THEN
828 --
829 build_sql(sqlstr, l_cntr_sql,
830 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_NOMINATIVE_REPORT'', ''ACTION_INFORMATION'|| cntr ||''', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec.action_information' || cntr ||'),''FM999999999999999999990.00''));');
831 --
832 ELSE
833
834 --
835 build_sql(sqlstr, l_cntr_sql, 'l_tag := pay_nl_nwr_xml_extract_pkg.get_tag_name(''NL_WR_NOMINATIVE_REPORT'',''ACTION_INFORMATION'|| cntr ||''');');
836 --
837 build_sql(sqlstr, l_cntr_sql, 'IF l_tag IS NOT NULL THEN ');
838 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NRRecords'', NULL);');
839 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Tag'', l_tag );');
840 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'', l_tag); ');
841 build_sql(sqlstr, l_cntr_sql,
842 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Amount'', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec.action_information' || cntr ||'),''FM999999999999999999990.00''));');
843 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NRRecords'', NULL);');
844 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
845 --
846
847 END IF;
848 --
849 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
850 --
851 END IF;
852 END LOOP;
853 --
854 IF g_year >= '2010' THEN
855 --LC 2010-- begin
856 --New Tags introduced in 2010 PrLnWao(12), PrLnWaoWga(13), PrLnWwAwf(14),PrLnUfo(16)
857 --
858 -- Added for the New Context NL_WR_NOMINATIVE_REPORT_ADD
859 --
863 --
860 --LC 2013 --BEGIN
861 --Tags PrLnWao(12), PrLnWaoWga(13), PrLnWwAwf(14) are obsolete as of 2013
862 build_sql(sqlstr, l_cntr_sql, 'FOR csr_income_info_rec1 IN pay_nl_nwr_xml_extract_pkg.csr_income_info1 ('||p_action_context_id||',''NL_WR_NOMINATIVE_REPORT_ADD'','''||p_type||''',csr_employment_info_rec.action_information_id) LOOP ');
864 IF g_year < '2013'THEN
865 FOR cntr1 in 12..14 LOOP
866 IF flex_seg_enabled ('NL_WR_NOMINATIVE_REPORT_ADD', 'ACTION_INFORMATION'||cntr1) THEN
867 --
868 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec1.action_information' || cntr1 ||' IS NOT NULL THEN ');
869 --
870 IF p_report_type = 'NLNWR_XML' THEN
871 --
872 build_sql(sqlstr, l_cntr_sql,
873 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_NOMINATIVE_REPORT_ADD'', ''ACTION_INFORMATION'|| cntr1 ||''', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec1.action_information' || cntr1 ||'),''FM999999999999999999990.00''));');
874 --
875 ELSE
876 --
877 build_sql(sqlstr, l_cntr_sql, 'l_tag := pay_nl_nwr_xml_extract_pkg.get_tag_name(''NL_WR_NOMINATIVE_REPORT_ADD'',''ACTION_INFORMATION'|| cntr1 ||''');');
878 --
879 build_sql(sqlstr, l_cntr_sql, 'IF l_tag IS NOT NULL THEN ');
880 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NRRecords'', NULL);');
881 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Tag'', l_tag );');
882 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'', l_tag); ');
883 build_sql(sqlstr, l_cntr_sql,
884 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Amount'', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec1.action_information' || cntr1 ||'),''FM999999999999999999990.00''));');
885 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NRRecords'', NULL);');
886 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
887 END IF;
888 --
889 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
890 --
891 END IF;
892 END LOOP;
893 END IF;
894
895 FOR cntr1 in 16..16 LOOP --PrLnUfo(16)
896 IF flex_seg_enabled ('NL_WR_NOMINATIVE_REPORT_ADD', 'ACTION_INFORMATION'||cntr1) THEN
897 --
898 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec1.action_information' || cntr1 ||' IS NOT NULL THEN ');
899 --
900 IF p_report_type = 'NLNWR_XML' THEN
901 --
902 build_sql(sqlstr, l_cntr_sql,
903 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_NOMINATIVE_REPORT_ADD'', ''ACTION_INFORMATION'|| cntr1 ||''', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec1.action_information' || cntr1 ||'),''FM999999999999999999990.00''));');
904 --
905 ELSE
906 --
907 build_sql(sqlstr, l_cntr_sql, 'l_tag := pay_nl_nwr_xml_extract_pkg.get_tag_name(''NL_WR_NOMINATIVE_REPORT_ADD'',''ACTION_INFORMATION'|| cntr1 ||''');');
908 --
909 build_sql(sqlstr, l_cntr_sql, 'IF l_tag IS NOT NULL THEN ');
910 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NRRecords'', NULL);');
911 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Tag'', l_tag );');
912 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'', l_tag); ');
913 build_sql(sqlstr, l_cntr_sql,
914 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Amount'', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec1.action_information' || cntr1 ||'),''FM999999999999999999990.00''));');
915 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NRRecords'', NULL);');
916 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
917 END IF;
918 --
919 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
920 --
921 END IF;
922 END LOOP;
923 build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
924 --
925 --LC 2010-- end
926 END IF;
927 --
928 FOR cntr in 7..14 LOOP --LC 2010-- Splitted from 5..14 to 5..6 and 7..14
929 IF flex_seg_enabled ('NL_WR_NOMINATIVE_REPORT', 'ACTION_INFORMATION'||cntr) THEN
930 --
931 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec.action_information' || cntr ||' IS NOT NULL THEN ');
932 --
933 IF p_report_type = 'NLNWR_XML' THEN
934 --
935 build_sql(sqlstr, l_cntr_sql,
936 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_NOMINATIVE_REPORT'', ''ACTION_INFORMATION'|| cntr ||''', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec.action_information' || cntr ||'),''FM999999999999999999990.00''));');
937 --
938 ELSE
939
940 --
941 build_sql(sqlstr, l_cntr_sql, 'l_tag := pay_nl_nwr_xml_extract_pkg.get_tag_name(''NL_WR_NOMINATIVE_REPORT'',''ACTION_INFORMATION'|| cntr ||''');');
942 --
943 build_sql(sqlstr, l_cntr_sql, 'IF l_tag IS NOT NULL THEN ');
944 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NRRecords'', NULL);');
948 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Amount'', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec.action_information' || cntr ||'),''FM999999999999999999990.00''));');
945 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Tag'', l_tag );');
946 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'', l_tag); ');
947 build_sql(sqlstr, l_cntr_sql,
949 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NRRecords'', NULL);');
950 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
951 --
952
953 END IF;
954 --
955 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
956 --
957 END IF;
958 END LOOP;
959
960 --
961 -- Added for the New Context NL_WR_NOMINATIVE_REPORT_ADD
962 --
963 build_sql(sqlstr, l_cntr_sql, 'FOR csr_income_info_rec1 IN pay_nl_nwr_xml_extract_pkg.csr_income_info1 ('||p_action_context_id||',''NL_WR_NOMINATIVE_REPORT_ADD'','''||p_type||''',csr_employment_info_rec.action_information_id) LOOP ');
964 --
965 FOR cntr1 in 10..10 LOOP
966 IF flex_seg_enabled ('NL_WR_NOMINATIVE_REPORT_ADD', 'ACTION_INFORMATION'||cntr1) THEN
967 --
968 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec1.action_information' || cntr1 ||' IS NOT NULL THEN ');
969 --
970 IF p_report_type = 'NLNWR_XML' THEN
971 --
972 build_sql(sqlstr, l_cntr_sql,
973 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_NOMINATIVE_REPORT_ADD'', ''ACTION_INFORMATION'|| cntr1 ||''', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec1.action_information' || cntr1 ||'),''FM999999999999999999990.00''));');
974 --
975 ELSE
976 --
977 build_sql(sqlstr, l_cntr_sql, 'l_tag := pay_nl_nwr_xml_extract_pkg.get_tag_name(''NL_WR_NOMINATIVE_REPORT_ADD'',''ACTION_INFORMATION'|| cntr1 ||''');');
978 --
979 build_sql(sqlstr, l_cntr_sql, 'IF l_tag IS NOT NULL THEN ');
980 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NRRecords'', NULL);');
981 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Tag'', l_tag );');
982 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'', l_tag); ');
983 build_sql(sqlstr, l_cntr_sql,
984 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Amount'', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec1.action_information' || cntr1 ||'),''FM999999999999999999990.00''));');
985 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NRRecords'', NULL);');
986 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
987 END IF;
988 --
989 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
990 --
991 END IF;
992 END LOOP;
993 build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
994 --
995 --
996 FOR cntr in 15..18 LOOP --LC 2010 -- Changed 15..22 to 15..18 , 19..19, 20..22
997 IF flex_seg_enabled ('NL_WR_NOMINATIVE_REPORT', 'ACTION_INFORMATION'||cntr) THEN
998 --
999 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec.action_information' || cntr ||' IS NOT NULL THEN ');
1000 --
1001 IF p_report_type = 'NLNWR_XML' THEN
1002 --
1003 build_sql(sqlstr, l_cntr_sql,
1004 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_NOMINATIVE_REPORT'', ''ACTION_INFORMATION'|| cntr ||''', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec.action_information' || cntr ||'),''FM999999999999999999990.00''));');
1005 --
1006 ELSE
1007 --
1008 build_sql(sqlstr, l_cntr_sql, 'l_tag := pay_nl_nwr_xml_extract_pkg.get_tag_name(''NL_WR_NOMINATIVE_REPORT'',''ACTION_INFORMATION'|| cntr ||''');');
1009 --
1010 build_sql(sqlstr, l_cntr_sql, 'IF l_tag IS NOT NULL THEN ');
1011 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NRRecords'', NULL);');
1012 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Tag'', l_tag );');
1013 IF cntr in (16,17) AND g_year = '2006' THEN
1014 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'', hr_general.decode_lookup(''NL_FORM_LABELS'',UPPER(l_tag)'||'||''_2006'''||'));');
1015 ELSE
1016 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'',l_tag); ');
1017 END IF;
1018 build_sql(sqlstr, l_cntr_sql,
1019 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Amount'', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec.action_information' || cntr ||'),''FM999999999999999999990.00''));');
1020 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NRRecords'', NULL);');
1021 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
1022 --
1023 END IF;
1024 --
1025 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
1026 --
1027 END IF;
1028 END LOOP;
1029 --
1033 --
1030 IF g_year < '2010' THEN --From 2010, Tag moved to Sect. Risk. Group section.
1031 FOR cntr in 19..19 LOOP --LC 2010 -- Changed 15..22 to 15..18 , 19..19, 20..22
1032 IF flex_seg_enabled ('NL_WR_NOMINATIVE_REPORT', 'ACTION_INFORMATION'||cntr) THEN
1034 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec.action_information' || cntr ||' IS NOT NULL THEN ');
1035 --
1036 IF p_report_type = 'NLNWR_XML' THEN
1037 --
1038 build_sql(sqlstr, l_cntr_sql,
1039 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_NOMINATIVE_REPORT'', ''ACTION_INFORMATION'|| cntr ||''', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec.action_information' || cntr ||'),''FM999999999999999999990.00''));');
1040 --
1041 ELSE
1042 --
1043 build_sql(sqlstr, l_cntr_sql, 'l_tag := pay_nl_nwr_xml_extract_pkg.get_tag_name(''NL_WR_NOMINATIVE_REPORT'',''ACTION_INFORMATION'|| cntr ||''');');
1044 --
1045 build_sql(sqlstr, l_cntr_sql, 'IF l_tag IS NOT NULL THEN ');
1046 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NRRecords'', NULL);');
1047 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Tag'', l_tag );');
1048 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'',l_tag); ');
1049 build_sql(sqlstr, l_cntr_sql,
1050 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Amount'', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec.action_information' || cntr ||'),''FM999999999999999999990.00''));');
1051 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NRRecords'', NULL);');
1052 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
1053 --
1054 END IF;
1055 --
1056 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
1057 --
1058 END IF;
1059 END LOOP;
1060 END IF;
1061 --
1062 FOR cntr in 20..22 LOOP --LC 2010 -- Changed 15..22 to 15..18 , 19..19, 20..22
1063 --LC 2013 -- Tag VergZvw is obsolete as of 2013
1064 IF flex_seg_enabled ('NL_WR_NOMINATIVE_REPORT', 'ACTION_INFORMATION'||cntr) THEN
1065 --
1066 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec.action_information' || cntr ||' IS NOT NULL THEN ');
1067 --
1068 IF p_report_type = 'NLNWR_XML' THEN
1069 --
1070 build_sql(sqlstr, l_cntr_sql,
1071 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_NOMINATIVE_REPORT'', ''ACTION_INFORMATION'|| cntr ||''', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec.action_information' || cntr ||'),''FM999999999999999999990.00''));');
1072 --
1073 ELSE
1074 --
1075 build_sql(sqlstr, l_cntr_sql, 'l_tag := pay_nl_nwr_xml_extract_pkg.get_tag_name(''NL_WR_NOMINATIVE_REPORT'',''ACTION_INFORMATION'|| cntr ||''');');
1076 --
1077 build_sql(sqlstr, l_cntr_sql, 'IF l_tag IS NOT NULL THEN ');
1078 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NRRecords'', NULL);');
1079 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Tag'', l_tag );');
1080 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'',l_tag); ');
1081 build_sql(sqlstr, l_cntr_sql,
1082 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Amount'', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec.action_information' || cntr ||'),''FM999999999999999999990.00''));');
1083 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NRRecords'', NULL);');
1084 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
1085 --
1086 END IF;
1087 --
1088 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
1089 --
1090 END IF;
1091 END LOOP;
1092
1093 --
1094 -- Added for the New Context NL_WR_NOMINATIVE_REPORT_ADD
1095 --
1096 build_sql(sqlstr, l_cntr_sql, 'FOR csr_income_info_rec1 IN pay_nl_nwr_xml_extract_pkg.csr_income_info1 ('||p_action_context_id||',''NL_WR_NOMINATIVE_REPORT_ADD'','''||p_type||''',csr_employment_info_rec.action_information_id) LOOP ');
1097 --
1098 FOR cntr1 in 5..9 LOOP
1099 IF flex_seg_enabled ('NL_WR_NOMINATIVE_REPORT_ADD', 'ACTION_INFORMATION'||cntr1) THEN
1100 --
1101 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec1.action_information' || cntr1 ||' IS NOT NULL THEN ');
1102 --
1103 IF p_report_type = 'NLNWR_XML' THEN
1104 --
1105 build_sql(sqlstr, l_cntr_sql,
1106 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_NOMINATIVE_REPORT_ADD'', ''ACTION_INFORMATION'|| cntr1 ||''', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec1.action_information' || cntr1 ||'),''FM999999999999999999990.00''));');
1107 --
1108 ELSE
1109 --
1110 build_sql(sqlstr, l_cntr_sql, 'l_tag := pay_nl_nwr_xml_extract_pkg.get_tag_name(''NL_WR_NOMINATIVE_REPORT_ADD'',''ACTION_INFORMATION'|| cntr1 ||''');');
1111 --
1112 build_sql(sqlstr, l_cntr_sql, 'IF l_tag IS NOT NULL THEN ');
1113 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NRRecords'', NULL);');
1117 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Amount'', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec1.action_information' || cntr1 ||'),''FM999999999999999999990.00''));');
1114 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Tag'', l_tag );');
1115 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'', l_tag); ');
1116 build_sql(sqlstr, l_cntr_sql,
1118 --
1119 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NRRecords'', NULL);');
1120 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
1121 END IF;
1122 --
1123 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
1124 --
1125 END IF;
1126 END LOOP;
1127 build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
1128 --
1129 --
1130 FOR cntr in 23..24 LOOP
1131 IF flex_seg_enabled ('NL_WR_NOMINATIVE_REPORT', 'ACTION_INFORMATION'||cntr) THEN
1132 --
1133 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec.action_information' || cntr ||' IS NOT NULL THEN ');
1134 --
1135 IF p_report_type = 'NLNWR_XML' THEN
1136 --
1137 build_sql(sqlstr, l_cntr_sql,
1138 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_NOMINATIVE_REPORT'', ''ACTION_INFORMATION'|| cntr ||''', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec.action_information' || cntr ||'),''FM999999999999999999990.00''));');
1139 --
1140 ELSE
1141 --
1142 build_sql(sqlstr, l_cntr_sql, 'l_tag := pay_nl_nwr_xml_extract_pkg.get_tag_name(''NL_WR_NOMINATIVE_REPORT'',''ACTION_INFORMATION'|| cntr ||''');');
1143 --
1144 build_sql(sqlstr, l_cntr_sql, 'IF l_tag IS NOT NULL THEN ');
1145 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NRRecords'', NULL);');
1146 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Tag'', l_tag );');
1147 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'', l_tag); ');
1148 build_sql(sqlstr, l_cntr_sql,
1149 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Amount'', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec.action_information' || cntr ||'),''FM999999999999999999990.00''));');
1150 --
1151 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NRRecords'', NULL);');
1152 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
1153 END IF;
1154 --
1155 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
1156 --
1157 END IF;
1158 END LOOP;
1159 --
1160 FOR cntr in 25..26 LOOP
1161 IF flex_seg_enabled ('NL_WR_NOMINATIVE_REPORT', 'ACTION_INFORMATION'||cntr) THEN
1162 --
1163 IF p_report_type = 'NLNWR_XML' THEN
1164 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_NOMINATIVE_REPORT'', ''ACTION_INFORMATION'|| cntr ||''', csr_income_info_rec.action_information' || cntr ||');');
1165 ELSE
1166 --
1167 build_sql(sqlstr, l_cntr_sql, 'l_tag := pay_nl_nwr_xml_extract_pkg.get_tag_name(''NL_WR_NOMINATIVE_REPORT'',''ACTION_INFORMATION'|| cntr ||''');');
1168 --
1169 build_sql(sqlstr, l_cntr_sql, 'IF l_tag IS NOT NULL THEN ');
1170 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NRRecords'', NULL);');
1171 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Tag'', l_tag);');
1172 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'', l_tag); ');
1173 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Amount'', csr_income_info_rec.action_information' || cntr ||');');
1174 --
1175 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NRRecords'', NULL);');
1176 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
1177 END IF;
1178 --
1179 END IF;
1180 END LOOP;
1181 --
1182 FOR cntr in 27..30 LOOP
1183 IF flex_seg_enabled ('NL_WR_NOMINATIVE_REPORT', 'ACTION_INFORMATION'||cntr) THEN
1184 --
1185 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec.action_information' || cntr ||' IS NOT NULL THEN ');
1186 --
1187 IF p_report_type = 'NLNWR_XML' THEN
1188 --
1189 build_sql(sqlstr, l_cntr_sql,
1190 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_NOMINATIVE_REPORT'', ''ACTION_INFORMATION'|| cntr ||''', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec.action_information' || cntr ||'),''FM999999999999999999990.00''));');
1191 --
1192 ELSE
1193 --
1194 build_sql(sqlstr, l_cntr_sql, 'l_tag := pay_nl_nwr_xml_extract_pkg.get_tag_name(''NL_WR_NOMINATIVE_REPORT'',''ACTION_INFORMATION'|| cntr ||''');');
1195 --
1196 build_sql(sqlstr, l_cntr_sql, 'IF l_tag IS NOT NULL THEN ');
1197 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NRRecords'', NULL);');
1198 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Tag'', l_tag );');
1202 --
1199 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'', l_tag); ');
1200 build_sql(sqlstr, l_cntr_sql,
1201 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Amount'', TO_CHAR(fnd_number.canonical_to_number(csr_income_info_rec.action_information' || cntr ||'),''FM999999999999999999990.00''));');
1203 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NRRecords'', NULL);');
1204 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
1205 END IF;
1206 --
1207 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
1208 --
1209 END IF;
1210 END LOOP;
1211 --
1212 -- Nominative Report </NominatieveAangifte>
1213 IF g_year = '2006' OR p_report_type <> 'NLNWR_XML' THEN
1214 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NominatieveAangifte'', NULL);');
1215 ELSE
1216 IF g_year >= '2008'
1217 AND g_payroll_type <> 'YEARLY' --8552196
1218 THEN
1219 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''Werknemersgegevens'', NULL);');
1220 ELSE
1221 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''Werknemergegevens'', NULL);');
1222 END IF;
1223 END IF;
1224 --
1225 build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
1226 --
1227 -- Nominative Report </NominatieveAangifte>
1228 --build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NominatieveAangifte'', NULL);');
1229 --
1230 build_sql(sqlstr, l_cntr_sql, 'END ;');
1231 --
1232 END generate_ip_srg_nr;
1233 --
1234 --------------------------------------------------------------------------------
1235 -- Name : GENERATE_PERSON
1236 -- Description : This Procedure is used to generate the XML part for
1237 -- (Person - Address/Foreign Address).
1238 --------------------------------------------------------------------------------
1239 --
1240 PROCEDURE generate_person( p_action_context_id NUMBER
1241 ,p_type VARCHAR2
1242 ,p_report_type VARCHAR2) AS
1243 --
1244 BEGIN
1245 --
1246 -- Person <NatuurlijkPersoon>
1247 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NatuurlijkPersoon'', NULL);');
1248 --
1249 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION8'', csr_employment_info_rec.action_information8);');
1250 --
1251 FOR cntr in 9..11 LOOP
1252 IF flex_seg_enabled ('NL_WR_EMPLOYMENT_INFO', 'ACTION_INFORMATION'||cntr) THEN
1253 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION'|| cntr ||''', csr_employment_info_rec.action_information' || cntr ||');');
1254 END IF;
1255 END LOOP;
1256 --
1257 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION12'', TO_CHAR(fnd_date.canonical_to_date(csr_employment_info_rec.action_information12),''YYYY-MM-DD''));');
1258 --
1259 FOR cntr in 13..14 LOOP
1260 IF flex_seg_enabled ('NL_WR_EMPLOYMENT_INFO', 'ACTION_INFORMATION'||cntr) THEN
1261 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION'|| cntr ||''', csr_employment_info_rec.action_information' || cntr ||');');
1262 END IF;
1263 END LOOP;
1264 --
1265 IF p_report_type IN ('NLNWR_IER','NLNWR_IERC','NLNWR_WER','NLNWR_COER','NLNWR_COMPLETE') THEN
1266 --
1267 build_sql(sqlstr, l_cntr_sql, 'Declare l_gender VARCHAR2(10); Begin ');
1268 build_sql(sqlstr, l_cntr_sql, ' l_gender := NULL ; ');
1269 build_sql(sqlstr, l_cntr_sql, 'IF csr_employment_info_rec.action_information14 = 1 THEN l_gender := ''M'' ; ELSIF csr_employment_info_rec.action_information14 = 2 THEN l_gender := ''F'' ; END IF;');
1270 --
1271 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''NatM'', hr_general.decode_lookup(''NL_NATIONALITY'',LPAD(csr_employment_info_rec.action_information13,4,''0'')));');
1272 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''GeslM'', hr_general.decode_lookup(''SEX'',l_gender));');
1273 -- This is for PersNr (Person Number) - only for Audit Reports
1274 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION5'', csr_employment_info_rec.action_information5);');
1275 --
1276 build_sql(sqlstr, l_cntr_sql, 'END ;');
1277 END IF;
1278 --
1279 build_sql(sqlstr, l_cntr_sql, 'FOR csr_address_info_rec IN pay_nl_nwr_xml_extract_pkg.csr_address_info
1280 ('||p_action_context_id||',''ADDRESS DETAILS'','''||p_type||''',csr_employment_info_rec.action_information_id,csr_employment_info_rec.assignment_id) LOOP ');
1281 --
1282 build_sql(sqlstr, l_cntr_sql, 'IF csr_address_info_rec.action_information14 = ''EMPLOYEE'' THEN ');
1283 -- Address <AdresBinnenland>
1284 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''AdresBinnenland'', NULL);');
1285 --
1286 --
1287 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''Str'', csr_address_info_rec.action_information9);');
1288 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''HuisNr'', csr_address_info_rec.action_information5);');
1292 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''Woonpl'', SUBSTR(hr_general.decode_lookup(''HR_NL_CITY'',csr_address_info_rec.action_information8),1,24));');
1289 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''HuisNrToev'', csr_address_info_rec.action_information6);');
1290 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''LocOms'', csr_address_info_rec.action_information11);');
1291 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''Pc'', csr_address_info_rec.action_information12);');
1293 --
1294 -- Address </AdresBinnenland>
1295 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''AdresBinnenland'', NULL);');
1296 --
1297 build_sql(sqlstr, l_cntr_sql, 'END IF ; ');
1298 --
1299 build_sql(sqlstr, l_cntr_sql, 'IF csr_address_info_rec.action_information14 = ''EMPLOYEE FOREIGN'' THEN ');
1300 -- Foreign Address <AdresBuitenland>
1301 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''AdresBuitenland'', NULL);');
1302 --
1303 --
1304 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''Str'', csr_address_info_rec.action_information5);');
1305 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''HuisNr'', csr_address_info_rec.action_information6);');
1306 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''LocOms'', csr_address_info_rec.action_information7);');
1307 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''Pc'', csr_address_info_rec.action_information12);');
1308 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''Woonpl'', SUBSTR(csr_address_info_rec.action_information8,1,24));');
1309 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''Reg'', SUBSTR(hr_general.decode_lookup(''NL_REGION'',csr_address_info_rec.action_information9),1,24));');
1310 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''LandCd'', csr_address_info_rec.action_information13);');
1311 --
1312 -- Foreign Address </AdresBuitenland>
1313 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''AdresBuitenland'', NULL);');
1314 --
1315 build_sql(sqlstr, l_cntr_sql, 'END IF ; ');
1316 --
1317 build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
1318 -- Person </NatuurlijkPersoon>
1319 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NatuurlijkPersoon'', NULL);');
1320 --
1321 END generate_person;
1322 --
1323 --------------------------------------------------------------------------------
1324 -- Name : GENERATE
1325 -- Description : This procedure interprets archived information and prints it
1326 -- out to an XML file.
1327 --------------------------------------------------------------------------------
1328 --
1329 PROCEDURE generate( p_action_context_id NUMBER
1330 ,p_nwr_report_type VARCHAR2
1331 --,p_xdo_output_type VARCHAR2
1332 ,p_assignment_set_id NUMBER
1333 ,p_sort_order VARCHAR2
1334 ,p_template_name VARCHAR2
1335 ,p_xml OUT NOCOPY CLOB) IS
1336 --
1337 CURSOR csr_inc_exc(p_assignment_set_id NUMBER) IS
1338 SELECT include_or_exclude
1339 FROM hr_assignment_set_amendments hasa
1340 WHERE hasa.assignment_set_id = p_assignment_set_id;
1341 --
1342 CURSOR get_year(c_payroll_action_id NUMBER) IS
1343 SELECT TO_CHAR(effective_date,'RRRR')
1344 FROM pay_payroll_actions
1345 WHERE payroll_action_id = c_payroll_action_id;
1346 --
1347 --11900414 Starts
1348 CURSOR c_usr_tab_col IS
1349 SELECT put.user_table_id
1350 ,puc.user_column_id
1351 FROM pay_user_tables put
1352 ,pay_user_columns puc
1353 WHERE put.user_table_id = puc.user_table_id
1354 AND put.legislation_code = puc.legislation_code
1355 AND put.user_table_name = 'NL_DIACRITICAL_MARKS'
1356 AND put.legislation_code = 'NL';
1357
1358 CURSOR c_src_dest_str(p_user_column_id IN NUMBER,
1359 p_user_table_id IN NUMBER,
1360 p_business_group_id IN NUMBER,
1361 p_start_date IN DATE) IS
1362 SELECT DISTINCT
1363 UPPER (purf.row_low_range_or_name) Source
1364 ,UPPER (pucif.value) Target
1365 FROM pay_user_column_instances_f pucif
1366 ,pay_user_rows_f purf
1367 WHERE pucif.user_column_id = p_user_column_id
1368 AND purf.user_table_id = p_user_table_id
1369 AND pucif.user_row_id = purf.user_row_id
1370 AND pucif.business_group_id = purf.business_group_id
1371 AND pucif.business_group_id = p_business_group_id
1372 AND p_start_date BETWEEN pucif.EFFECTIVE_START_DATE AND pucif.EFFECTIVE_END_DATE
1373 AND p_start_date BETWEEN purf.EFFECTIVE_START_DATE AND purf.EFFECTIVE_END_DATE;
1374
1375 CURSOR c_bg_stdate(p_payroll_action_id IN NUMBER) IS
1376 SELECT business_group_id
1377 ,start_date
1378 FROM pay_payroll_actions
1379 WHERE payroll_action_id = p_payroll_action_id;
1380
1381 l_user_table_id NUMBER;
1382 l_user_column_id NUMBER;
1383 l_bg_id NUMBER;
1384 l_start_date DATE;
1385 --11900414 Ends
1386
1387 l_proc_name VARCHAR2(100);
1388 l_xml CLOB;
1389 csr NUMBER;
1390 ret NUMBER;
1391 l_inc_exc VARCHAR2(1) DEFAULT 'X'; -- 'X' as some dummy value
1395 l_ER_ONLY VARCHAR2(10); --10100628
1392 l_payroll_type VARCHAR2(40) DEFAULT NULL;
1393 --
1394 l_prev_corr_year VARCHAR2(100); --10100628
1396 BEGIN
1397 --
1398 --hr_utility.trace_on(NULL,'NL_NWR');
1399 hr_utility.trace('p_nwr_report_type :'||p_nwr_report_type);
1400 hr_utility.trace('p_action_context_id :'||p_action_context_id);
1401 hr_utility.trace('p_template_name :'||p_template_name);
1402 hr_utility.trace('p_assignment_set_id :'||p_assignment_set_id);
1403 hr_utility.trace('p_sort_order :'||p_sort_order);
1404 --
1405 l_cntr_sql := 1;
1406 g_action_ctx_id := p_action_context_id;
1407 --
1408 IF p_assignment_set_id IS NOT NULL THEN
1409 OPEN csr_inc_exc(p_assignment_set_id);
1410 FETCH csr_inc_exc INTO l_inc_exc;
1411 CLOSE csr_inc_exc;
1412 END IF;
1413 --
1414 --10100628 l_prev_corr_year := NVL(pay_nl_wage_report_pkg.get_parameters(p_action_context_id, 'CORR_YR'),-9999); --10100628
1415 l_prev_corr_year := NVL(pay_nl_wage_report_pkg.get_parameters(p_action_context_id, 'CORR_YR'),'N'); --10100628
1416 l_ER_ONLY := NVL(pay_nl_wage_report_pkg.get_parameters(p_action_context_id, 'ER_ONLY'),'N');
1417
1418 OPEN get_year(p_action_context_id);
1419 FETCH get_year INTO g_year;
1420 CLOSE get_year;
1421
1422 --10100628
1423 IF l_prev_corr_year = 'Y' THEN
1424 l_prev_corr_year := g_year;
1425 ELSIF l_prev_corr_year = 'N' THEN
1426 l_prev_corr_year := '-9999';
1427 END IF;
1428 --10100628
1429
1430 --10100628
1431 /*
1432 IF l_prev_corr_year <> '-9999' THEN
1433 g_year := l_prev_corr_year;
1434 END IF;
1435 */
1436 --10100628
1437 --11900414 Starts
1438 IF Instr ('a', COMPOSE('a'|| UNISTR('\0301')) ) = 0 THEN
1439
1440 OPEN c_bg_stdate(p_action_context_id);
1441 FETCH c_bg_stdate INTO l_bg_id,l_start_date;
1442 CLOSE c_bg_stdate;
1443
1444 OPEN C_USR_TAB_COL;
1445 FETCH C_USR_TAB_COL INTO l_user_table_id, l_user_column_id;
1446 CLOSE C_USR_TAB_COL;
1447
1448 FOR I in C_SRC_DEST_STR(l_user_column_id, l_user_table_id, l_bg_id, l_start_date)
1449 LOOP
1450 g_SOURCE := g_SOURCE||upper(I.Source)||lower(I.Source);
1451 g_TARGET := g_TARGET||upper(I.Target)||lower(I.Target);
1452 END LOOP;
1453
1454 hr_utility.trace('g_SOURCE :'||g_SOURCE);
1455 hr_utility.trace('g_TARGET :'||g_TARGET);
1456
1457 END IF;
1458 --11900414 Ends
1459
1460 --
1461 build_sql(sqlstr, l_cntr_sql, 'DECLARE BEGIN ');
1462 --g_xml_nwr (g_xml_nwr.count() + 1) := '<?xml version="1.0" encoding="UTF-8"?>';
1463 g_xml_nwr (g_xml_nwr.count() + 1) := '<?xml version="1.0" encoding="' || get_IANA_charset ||'"?>';
1464 -- START <Loonaangifte>
1465 --# 0
1466 l_payroll_type := TO_CHAR(pay_nl_wage_report_pkg.get_parameters(p_action_context_id,'Payroll_Type'));
1467 g_payroll_type := l_payroll_type; --8552196
1468 IF p_nwr_report_type = 'NLNWR_XML' THEN
1469 -- l_payroll_type := TO_CHAR(pay_nl_wage_report_pkg.get_parameters(p_action_context_id,'Payroll_Type'));
1470 IF l_payroll_type = 'YEARLY' and g_year >= '2008' THEN -- Bug# 8459982
1471 g_xml_nwr (g_xml_nwr.count() + 1) := '<Jaarloonopgaaf xmlns="http://xml.belastingdienst.nl/schemas/Jaarloonopgaaf/'||g_year||'/01" version="1.1">'||EOL ;
1472 ELSIF l_payroll_type = 'YEARLY' and g_year = '2007' THEN -- Enh# 6968464
1473 g_xml_nwr (g_xml_nwr.count() + 1) := '<Jaarloonopgaaf xmlns="http://xml.belastingdienst.nl/schemas/Jaarloonopgaaf/'||g_year||'/01" version="1.2">'||EOL ;
1474 ELSIF l_payroll_type = 'YEARLY' THEN
1475 g_xml_nwr (g_xml_nwr.count() + 1) := '<Jaarloonopgaaf xmlns="http://xml.belastingdienst.nl/schemas/Loonaangifte/2006/01" version="4.0">'||EOL ;
1476 ELSIF g_year = '2006' THEN
1477 g_xml_nwr (g_xml_nwr.count() + 1) := '<Loonaangifte xmlns="http://xml.belastingdienst.nl/schemas/Loonaangifte/2006/01" version="4.0">'||EOL ;
1478 ELSIF g_year >= '2012' THEN
1479 g_xml_nwr (g_xml_nwr.count() + 1) := '<Loonaangifte xmlns="http://xml.belastingdienst.nl/schemas/Loonaangifte/'||g_year||'/01" version="2.0">'||EOL ; -- Enh# 13641307 / 13643663
1480 ELSE
1481 g_xml_nwr (g_xml_nwr.count() + 1) := '<Loonaangifte xmlns="http://xml.belastingdienst.nl/schemas/Loonaangifte/'||g_year||'/01" version="1.0">'||EOL ;
1482 --g_xml_nwr (g_xml_nwr.count() + 1) := '<version>1.0</version>' ;
1483 END IF;
1484 ELSE
1485 g_xml_nwr (g_xml_nwr.count() + 1) := '<Loonaangifte>'||EOL;
1486 g_xml_nwr (g_xml_nwr.count() + 1) := '<Year>'||g_year||'</Year>'||EOL; --LC2010-- Added for RTF Templates
1487 END IF;
1488 --# 0
1489 --
1490 -- Starting the Main Loop .. All the Other Loops come under this Loop only.
1491 --
1492 g_report_type := p_nwr_report_type;
1493 build_sql(sqlstr, l_cntr_sql, 'FOR csr_message_info_rec IN pay_nl_nwr_xml_extract_pkg.csr_message_info ('||p_action_context_id||',''NL_WR_EMPLOYER_INFO'') LOOP ');
1494 --# 1
1495 IF p_nwr_report_type = 'NLNWR_XML' THEN
1496 --#
1497 -- MESSAGE <Bericht>
1498 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''Bericht'', NULL);');
1499 --
1500 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION2'', csr_message_info_rec.action_information2);');
1501 --
1502 build_sql(sqlstr, l_cntr_sql,
1503 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION3'', REPLACE(TO_CHAR(fnd_date.canonical_to_date(csr_message_info_rec.action_information3),''YYYY-MM-DD HH24:MI:SS''),'' '',''T''));');
1504 --
1508 END IF;
1505 FOR cntr in 4..6 LOOP
1506 IF flex_seg_enabled ('NL_WR_EMPLOYER_INFO', 'ACTION_INFORMATION'||cntr) THEN
1507 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION'|| cntr ||''', csr_message_info_rec.action_information' || cntr ||');');
1509 END LOOP;
1510 --
1511 --LC2010--
1512 IF g_year < '2010' THEN
1513 IF flex_seg_enabled ('NL_WR_EMPLOYER_INFO', 'ACTION_INFORMATION7') THEN
1514 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION7'', csr_message_info_rec.action_information7);');
1515 END IF;
1516 ELSE
1517 FOR cntr in 13..14 LOOP
1518 IF flex_seg_enabled ('NL_WR_EMPLOYER_INFO', 'ACTION_INFORMATION'||cntr) THEN
1519 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION'|| cntr ||''', csr_message_info_rec.action_information' || cntr ||');');
1520 END IF;
1521 END LOOP;
1522 END IF;
1523 --LC2010--
1524
1525 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''Bericht'', NULL);');
1526 -- MESSAGE </Bericht>
1527 --# 1
1528 END IF;
1529 --#
1530 -- Administrative Unit <AdministratieveEenheid>
1531 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''AdministratieveEenheid'', NULL);');
1532 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION8'', csr_message_info_rec.action_information8);');
1533 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION9'', csr_message_info_rec.action_information9);');
1534 -- Added to accomodate the changes in the collective and complete reports
1535 IF p_nwr_report_type IN ('NLNWR_CR','NLNWR_COMPLETE') THEN
1536 -- MESSAGE <Bericht>
1537 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION2'', csr_message_info_rec.action_information2);');
1538 --
1539 build_sql(sqlstr, l_cntr_sql,
1540 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION3'', REPLACE(TO_CHAR(fnd_date.canonical_to_date(csr_message_info_rec.action_information3),''YYYY-MM-DD HH24:MI:SS''),'' '',''T''));');
1541 --
1542 FOR cntr in 4..6 LOOP
1543 IF flex_seg_enabled ('NL_WR_EMPLOYER_INFO', 'ACTION_INFORMATION'||cntr) THEN
1544 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION'|| cntr ||''', csr_message_info_rec.action_information' || cntr ||');');
1545 END IF;
1546 END LOOP;
1547 --LC2010--
1548 IF g_year < '2010' THEN
1549 IF flex_seg_enabled ('NL_WR_EMPLOYER_INFO', 'ACTION_INFORMATION7') THEN
1550 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION7'', csr_message_info_rec.action_information7);');
1551 END IF;
1552 ELSE
1553 FOR cntr in 13..14 LOOP
1554 IF flex_seg_enabled ('NL_WR_EMPLOYER_INFO', 'ACTION_INFORMATION'||cntr) THEN
1555 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION'|| cntr ||''', csr_message_info_rec.action_information' || cntr ||');');
1556 END IF;
1557 END LOOP;
1558 END IF;
1559 --LC2010--
1560 --
1561 -- MESSAGE </Bericht>
1562 END IF;
1563 --
1564
1565
1566 --IF l_prev_corr_year = '-9999' THEN --10100628 -13106837
1567 IF (l_prev_corr_year = '-9999' OR l_prev_corr_year <> '-9999' ) THEN --13106837 --13844512
1568 --# 2
1569
1570 IF p_nwr_report_type IN ('NLNWR_XML','NLNWR_CR','NLNWR_IER','NLNWR_COMPLETE') OR (p_nwr_report_type IN ('NLNWR_CRC') AND l_prev_corr_year <> '-9999' ) THEN --13106837
1571 --#
1572
1573 IF l_payroll_type = 'YEARLY' OR l_prev_corr_year <> '-9999' THEN --13106837 --13844512
1574 -- Period Report <TijdvakCorrectie>
1575
1576 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''TijdvakCorrectie'', NULL);');
1577 ELSE
1578 -- Period Report <TijdvakAangifte>
1579
1580 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''TijdvakAangifte'', NULL);');
1581 END IF;
1582 --
1583 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION10'', TO_CHAR(fnd_date.canonical_to_date(csr_message_info_rec.action_information10),''YYYY-MM-DD''));');
1584 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION11'', TO_CHAR(fnd_date.canonical_to_date(csr_message_info_rec.action_information11),''YYYY-MM-DD''));');
1585 --
1586 IF l_payroll_type <> 'YEARLY' AND l_prev_corr_year = '-9999' THEN --13106837
1587 -- Complete Report <VolledigeAangifte>
1588
1589 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''VolledigeAangifte'', NULL);');
1590 END IF;
1591 --# 2.1
1592 IF p_nwr_report_type IN ('NLNWR_XML','NLNWR_CR','NLNWR_COMPLETE') OR (p_nwr_report_type IN ('NLNWR_CRC') AND l_prev_corr_year <> '-9999' ) THEN ---13106837 --13844512
1593 --#
1594 -- Collective Report <CollectieveAangifte>
1598 generate_collective_report( p_act_context_id => p_action_context_id
1595 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''CollectieveAangifte'', NULL);');
1596 --
1597 IF l_prev_corr_year = '-9999' OR (l_prev_corr_year <> '-9999' AND l_ER_ONLY = 'N') THEN --13844512
1599 ,p_type => 'COMPLETE'
1600 ,p_start_date => 'csr_message_info_rec.action_information10'
1601 ,p_end_date => 'csr_message_info_rec.action_information11'
1602 ,p_in_not_in => 'NOT IN'
1603 ,p_report_type => p_nwr_report_type);
1604 END IF;
1605 --
1606
1607 --13106837
1608 IF l_prev_corr_year <> '-9999' THEN --13844512
1609 generate_collective_report( p_act_context_id => p_action_context_id
1610 ,p_type => 'CORRECTION'
1611 ,p_start_date => 'csr_message_info_rec.action_information10'
1612 ,p_end_date => 'csr_message_info_rec.action_information11'
1613 ,p_in_not_in => 'NOT IN'
1614 ,p_report_type => p_nwr_report_type);
1615 END IF;
1616 --13106837
1617
1618 build_sql(sqlstr, l_cntr_sql,
1619 'FOR csr_swmf_info_rec IN pay_nl_nwr_xml_extract_pkg.csr_swmf_info ('||p_action_context_id||',''NL_WR_SWMF_SECTOR_RISK_GROUP'',''SWMF'',csr_message_info_rec.action_information10,csr_message_info_rec.action_information11) LOOP ');
1620 -- Specification waiting money fund contribution <Wgf>
1621 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''Wgf'', NULL);');
1622 --
1623 --LC2010--begin - From 2010, Sect Tag Deleted. Only RisGrp
1624 IF g_year < '2010' THEN
1625 FOR cntr in 7..8 LOOP
1626 IF flex_seg_enabled ('NL_WR_SWMF_SECTOR_RISK_GROUP', 'ACTION_INFORMATION'||cntr) THEN
1627 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_SWMF_SECTOR_RISK_GROUP'', ''ACTION_INFORMATION'|| cntr ||''', csr_swmf_info_rec.action_information' || cntr ||');');
1628 END IF;
1629 END LOOP;
1630 ELSE
1631 IF flex_seg_enabled ('NL_WR_SWMF_SECTOR_RISK_GROUP', 'ACTION_INFORMATION8') THEN
1632 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_SWMF_SECTOR_RISK_GROUP'', ''ACTION_INFORMATION8'', csr_swmf_info_rec.action_information8);');
1633 END IF;
1634 END IF;
1635 --LC2010--End
1636 --
1637 build_sql(sqlstr, l_cntr_sql,
1638 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_SWMF_SECTOR_RISK_GROUP'', ''ACTION_INFORMATION9'', to_char(fnd_number.canonical_to_number(csr_swmf_info_rec.action_information9)));');
1639 build_sql(sqlstr, l_cntr_sql,
1640 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_SWMF_SECTOR_RISK_GROUP'', ''ACTION_INFORMATION10'', to_char(fnd_number.canonical_to_number(csr_swmf_info_rec.action_information10)));');
1641 --
1642 -- Specification waiting money fund contribution </Wgf>
1643 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''Wgf'', NULL);');
1644 --
1645 build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
1646 --
1647 IF l_prev_corr_year = '-9999' OR (l_prev_corr_year <> '-9999' AND l_ER_ONLY = 'N') THEN --13844512
1648 generate_collective_report( p_act_context_id => p_action_context_id
1649 ,p_type => 'COMPLETE'
1650 ,p_start_date => 'csr_message_info_rec.action_information10'
1651 ,p_end_date => 'csr_message_info_rec.action_information11'
1652 ,p_in_not_in => 'IN'
1653 ,p_report_type => p_nwr_report_type);
1654 END IF;
1655 --
1656
1657 --13106837
1658 IF l_prev_corr_year <> '-9999' THEN --13844512
1659 generate_collective_report( p_act_context_id => p_action_context_id
1660 ,p_type => 'CORRECTION'
1661 ,p_start_date => 'csr_message_info_rec.action_information10'
1662 ,p_end_date => 'csr_message_info_rec.action_information11'
1663 ,p_in_not_in => 'IN'
1664 ,p_report_type => p_nwr_report_type);
1665 END IF;
1666 --13106837
1667 IF l_prev_corr_year = '-9999' THEN --13844512
1668 build_sql(sqlstr, l_cntr_sql, 'FOR csr_corr_balance_info_rec IN pay_nl_nwr_xml_extract_pkg.csr_corr_balance_info ('||p_action_context_id||',''NL_WR_COLLECTIVE_REPORT'',''CORR_BALANCE'') LOOP ');
1669 --
1670 -- Correction balances prev. period <SaldoCorrectiesVoorgaandTijdvak>
1671 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''SaldoCorrectiesVoorgaandTijdvak'', NULL);');
1672 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''DatAanvTv'', TO_CHAR(fnd_date.canonical_to_date(csr_corr_balance_info_rec.action_information3),''YYYY-MM-DD''));');
1676 --
1673 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''DatEindTv'', TO_CHAR(fnd_date.canonical_to_date(csr_corr_balance_info_rec.action_information4),''YYYY-MM-DD''));');
1674 build_sql(sqlstr, l_cntr_sql,
1675 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, csr_corr_balance_info_rec.action_information2, to_char(fnd_number.canonical_to_number(csr_corr_balance_info_rec.action_information6)));');
1677 -- Correction balances prev. period </SaldoCorrectiesVoorgaandTijdvak>
1678 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''SaldoCorrectiesVoorgaandTijdvak'', NULL);');
1679 build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
1680 --
1681 END IF; --13844512
1682 -- General Total
1683 build_sql(sqlstr, l_cntr_sql,
1684 'FOR csr_collective_info_rec IN pay_nl_nwr_xml_extract_pkg.csr_collective_info ('||p_action_context_id||',''NL_WR_COLLECTIVE_REPORT'',''TOTAL'',csr_message_info_rec.action_information10,csr_message_info_rec.action_information11) LOOP ');
1685 --
1686 build_sql(sqlstr, l_cntr_sql,
1687 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, csr_collective_info_rec.action_information2, to_char(fnd_number.canonical_to_number(csr_collective_info_rec.action_information6)));');
1688 --
1689 build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
1690 --
1691 -- Collective Report </CollectieveAangifte>
1692 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''CollectieveAangifte'', NULL);');
1693 --
1694 --# 2.1
1695 END IF;
1696 --#
1697 --# 2.2
1698 IF l_prev_corr_year = '-9999' OR (l_prev_corr_year <> '-9999' AND l_ER_ONLY = 'N') THEN --13844512
1699 IF p_nwr_report_type IN ('NLNWR_XML','NLNWR_IER','NLNWR_COMPLETE') OR (p_nwr_report_type IN ('NLNWR_CRC') AND (l_prev_corr_year <> '-9999' AND l_ER_ONLY = 'N')) THEN
1700 --#
1701 build_sql(sqlstr, l_cntr_sql,
1702 'FOR csr_employment_info_rec IN pay_nl_nwr_xml_extract_pkg.csr_employment_info ('||
1703 p_action_context_id||',''NL_WR_EMPLOYMENT_INFO'',''INITIAL'',csr_message_info_rec.action_information10,csr_message_info_rec.action_information11,'''|| p_sort_order ||''') LOOP ');
1704 --
1705 IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'I' THEN
1706 --
1707 build_sql(sqlstr, l_cntr_sql, 'FOR csr_assignment_filter_rec IN pay_nl_nwr_xml_extract_pkg.csr_assignment_filter ('||p_assignment_set_id||',csr_employment_info_rec.assignment_id) LOOP ');
1708 build_sql(sqlstr, l_cntr_sql, 'IF csr_assignment_filter_rec.INCLUDE_OR_EXCLUDE = ''I'' THEN ');
1709 END IF;
1710 --
1711 IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'E' THEN
1712 --
1713 build_sql(sqlstr, l_cntr_sql, 'DECLARE l_flag VARCHAR2(1) := ''Y''; BEGIN ');
1714 build_sql(sqlstr, l_cntr_sql, 'FOR csr_assignment_filter_rec IN pay_nl_nwr_xml_extract_pkg.csr_assignment_filter ('||p_assignment_set_id||',csr_employment_info_rec.assignment_id) LOOP ');
1715 build_sql(sqlstr, l_cntr_sql, 'IF csr_assignment_filter_rec.INCLUDE_OR_EXCLUDE = ''E'' THEN l_flag := ''N''; END IF; END LOOP; ');
1716 build_sql(sqlstr, l_cntr_sql, 'IF l_flag = ''Y'' THEN ');
1717 END IF;
1718 --
1719 -- Initial Employment Relation <InkomstenverhoudingInitieel>
1720 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''InkomstenverhoudingInitieel'', NULL);');
1721 --
1722 IF p_nwr_report_type <> 'NLNWR_XML' THEN
1723 --
1724 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION8'', csr_message_info_rec.action_information8);');
1725 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION9'', csr_message_info_rec.action_information9);');
1726 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION10'', TO_CHAR(fnd_date.canonical_to_date(csr_message_info_rec.action_information10),''YYYY-MM-DD''));');
1727 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION11'', TO_CHAR(fnd_date.canonical_to_date(csr_message_info_rec.action_information11),''YYYY-MM-DD''));');
1728 --
1729 END IF;
1730 --
1731 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION18'', csr_employment_info_rec.action_information18);');
1732 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION4'', csr_employment_info_rec.action_information4);');
1733 build_sql(sqlstr, l_cntr_sql,
1734 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION15'', TO_CHAR(fnd_date.canonical_to_date(csr_employment_info_rec.action_information15),''YYYY-MM-DD''));');
1735 build_sql(sqlstr, l_cntr_sql,
1736 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION16'', TO_CHAR(fnd_date.canonical_to_date(csr_employment_info_rec.action_information16),''YYYY-MM-DD''));');
1737 --
1738 FOR cntr in REVERSE 5..6 LOOP
1739 IF flex_seg_enabled ('NL_WR_EMPLOYMENT_INFO', 'ACTION_INFORMATION'||cntr) THEN
1743 --
1740 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION'|| cntr ||''', csr_employment_info_rec.action_information' || cntr ||');');
1741 END IF;
1742 END LOOP;
1744 --
1745 generate_person(p_action_context_id,'INITIAL',p_nwr_report_type);
1746 --
1747 generate_ip_srg_nr(p_action_context_id,'INITIAL',p_nwr_report_type);
1748 --
1749 -- Initial Employment Relation </InkomstenverhoudingInitieel>
1750 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''InkomstenverhoudingInitieel'', NULL);');
1751 --
1752 IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'I' THEN
1753 build_sql(sqlstr, l_cntr_sql, 'END IF; END LOOP; ');
1754 END IF;
1755 --
1756 IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'E' THEN
1757 build_sql(sqlstr, l_cntr_sql, ' END IF; END; ');
1758 END IF;
1759 --
1760 build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
1761 --
1762 --# 2.2
1763 END IF;
1764 END IF; --13844512
1765 --#
1766
1767
1768 IF l_payroll_type <> 'YEARLY' AND l_prev_corr_year = '-9999' THEN --13106837
1769
1770 -- Complete Report </VolledigeAangifte>
1771 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''VolledigeAangifte'', NULL);');
1772 END IF;
1773 IF l_payroll_type = 'YEARLY' OR l_prev_corr_year <> '-9999' THEN --13106837 --13844512
1774 -- Period Report <TijdvakCorrectie>
1775
1776 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''TijdvakCorrectie'', NULL);');
1777 ELSE
1778 -- Period Report <TijdvakAangifte>
1779
1780 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''TijdvakAangifte'', NULL);');
1781 END IF;
1782 --
1783 --# 2
1784 END IF;
1785 --#
1786 END IF; --10100628
1787
1788 IF l_prev_corr_year = '-9999' THEN --13106837
1789 --# 3
1790 IF p_nwr_report_type IN ('NLNWR_XML','NLNWR_CRC','NLNWR_IERC','NLNWR_WER','NLNWR_COER','NLNWR_COMPLETE') THEN
1791 --#
1792 build_sql(sqlstr, l_cntr_sql, 'FOR csr_correction_info_rec IN pay_nl_nwr_xml_extract_pkg.csr_correction_info ('||p_action_context_id||',''NL_WR_EMPLOYMENT_INFO'',''INITIAL'') LOOP ');
1793 --
1794 -- Correction Report <TijdvakCorrectie>
1795 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''TijdvakCorrectie'', NULL);');
1796 --
1797 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''DatAanvTv'', TO_CHAR(fnd_date.canonical_to_date(csr_correction_info_rec.start_date),''YYYY-MM-DD''));');
1798 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''DatEindTv'', TO_CHAR(fnd_date.canonical_to_date(csr_correction_info_rec.end_date),''YYYY-MM-DD''));');
1799 --
1800 IF p_nwr_report_type IN ('NLNWR_CRC') THEN
1801 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION8'', csr_message_info_rec.action_information8);');
1802 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION9'', csr_message_info_rec.action_information9);');
1803 END IF;
1804 --
1805
1806 --# 3.1
1807 IF p_nwr_report_type IN ('NLNWR_XML','NLNWR_CRC','NLNWR_COMPLETE') THEN
1808 --#
1809 -- Collective Report <CollectieveAangifte>
1810 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''CollectieveAangifte'', NULL);');
1811 --
1812 generate_collective_report( p_act_context_id => p_action_context_id
1813 ,p_type => 'CORRECTION'
1814 ,p_start_date => 'csr_correction_info_rec.start_date'
1815 ,p_end_date => 'csr_correction_info_rec.end_date'
1816 ,p_in_not_in => 'NOT IN'
1817 ,p_report_type => p_nwr_report_type);
1818 --
1819 build_sql(sqlstr, l_cntr_sql,
1820 'FOR csr_swmf_info_rec IN pay_nl_nwr_xml_extract_pkg.csr_swmf_info ('||p_action_context_id||',''NL_WR_SWMF_SECTOR_RISK_GROUP'',''SWMF'',csr_correction_info_rec.start_date,csr_correction_info_rec.end_date) LOOP ');
1821 -- Specification waiting money fund contribution <Wgf>
1822 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''Wgf'', NULL);');
1823 --
1824 /* FOR cntr in 7..8 LOOP
1825 IF flex_seg_enabled ('NL_WR_SWMF_SECTOR_RISK_GROUP', 'ACTION_INFORMATION'||cntr) THEN
1826 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_SWMF_SECTOR_RISK_GROUP'', ''ACTION_INFORMATION'|| cntr ||''', csr_swmf_info_rec.action_information' || cntr ||');');
1827 END IF;
1828 END LOOP;
1829 */
1830 --LC2010--begin - From 2010, Sect Tag Deleted. Only RisGrp 9691085
1831 IF g_year < '2010' THEN
1832 FOR cntr in 7..8 LOOP
1833 IF flex_seg_enabled ('NL_WR_SWMF_SECTOR_RISK_GROUP', 'ACTION_INFORMATION'||cntr) THEN
1837 ELSE
1834 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_SWMF_SECTOR_RISK_GROUP'', ''ACTION_INFORMATION'|| cntr ||''', csr_swmf_info_rec.action_information' || cntr ||');');
1835 END IF;
1836 END LOOP;
1838 IF flex_seg_enabled ('NL_WR_SWMF_SECTOR_RISK_GROUP', 'ACTION_INFORMATION8') THEN
1839 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_SWMF_SECTOR_RISK_GROUP'', ''ACTION_INFORMATION8'', csr_swmf_info_rec.action_information8);');
1840 END IF;
1841 END IF;
1842 --LC2010--End -- 9691085
1843
1844 build_sql(sqlstr, l_cntr_sql,
1845 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_SWMF_SECTOR_RISK_GROUP'', ''ACTION_INFORMATION9'', to_char(fnd_number.canonical_to_number(csr_swmf_info_rec.action_information9)));');
1846 build_sql(sqlstr, l_cntr_sql,
1847 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_SWMF_SECTOR_RISK_GROUP'', ''ACTION_INFORMATION10'', to_char(fnd_number.canonical_to_number(csr_swmf_info_rec.action_information10)));');
1848 --
1849 -- Specification waiting money fund contribution </Wgf>
1850 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''Wgf'', NULL);');
1851 --
1852 build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
1853 --
1854 --
1855 generate_collective_report( p_act_context_id => p_action_context_id
1856 ,p_type => 'CORRECTION'
1857 ,p_start_date => 'csr_correction_info_rec.start_date'
1858 ,p_end_date => 'csr_correction_info_rec.end_date'
1859 ,p_in_not_in => 'IN'
1860 ,p_report_type => p_nwr_report_type);
1861 --
1862 -- Collective Report </CollectieveAangifte>
1863 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''CollectieveAangifte'', NULL);');
1864 --
1865 --# 3.1
1866 END IF;
1867 --#
1868
1869 IF l_prev_corr_year = '-9999' THEN --10100628
1870 --# 3.2
1871 IF p_nwr_report_type IN ('NLNWR_XML','NLNWR_IERC','NLNWR_COMPLETE') THEN
1872 --#
1873 --
1874
1875 build_sql(sqlstr, l_cntr_sql,
1876 'FOR csr_employment_info_rec IN pay_nl_nwr_xml_extract_pkg.csr_employment_info ('||
1877 p_action_context_id||',''NL_WR_EMPLOYMENT_INFO'',''CORRECTION'',csr_correction_info_rec.start_date,csr_correction_info_rec.end_date,'''|| p_sort_order ||''') LOOP ');
1878 --
1879 IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'I' THEN
1880 --
1881 build_sql(sqlstr, l_cntr_sql, 'FOR csr_assignment_filter_rec IN pay_nl_nwr_xml_extract_pkg.csr_assignment_filter ('||p_assignment_set_id||',csr_employment_info_rec.assignment_id) LOOP ');
1882 build_sql(sqlstr, l_cntr_sql, 'IF csr_assignment_filter_rec.INCLUDE_OR_EXCLUDE = ''I'' THEN ');
1883 END IF;
1884 --
1885 IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'E' THEN
1886 --
1887 build_sql(sqlstr, l_cntr_sql, 'DECLARE l_flag VARCHAR2(1) := ''Y''; BEGIN ');
1888 build_sql(sqlstr, l_cntr_sql, 'FOR csr_assignment_filter_rec IN pay_nl_nwr_xml_extract_pkg.csr_assignment_filter ('||p_assignment_set_id||',csr_employment_info_rec.assignment_id) LOOP ');
1889 build_sql(sqlstr, l_cntr_sql, 'IF csr_assignment_filter_rec.INCLUDE_OR_EXCLUDE = ''E'' THEN l_flag := ''N''; END IF; END LOOP; ');
1890 build_sql(sqlstr, l_cntr_sql, 'IF l_flag = ''Y'' THEN ');
1891 END IF;
1892 --
1893 -- Initial Employment Relation <InkomstenverhoudingInitieel>
1894 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''InkomstenverhoudingInitieel'', NULL);');
1895 --
1896 IF p_nwr_report_type <> 'NLNWR_XML' THEN
1897 --
1898 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION8'', csr_message_info_rec.action_information8);');
1899 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION9'', csr_message_info_rec.action_information9);');
1900 --
1901 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''DatAanvTv'', TO_CHAR(fnd_date.canonical_to_date(csr_correction_info_rec.start_date),''YYYY-MM-DD''));');
1902 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''DatEindTv'', TO_CHAR(fnd_date.canonical_to_date(csr_correction_info_rec.end_date),''YYYY-MM-DD''));');
1903 --
1904 END IF;
1905 --
1906 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION18'', csr_employment_info_rec.action_information18);');
1907 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION4'', csr_employment_info_rec.action_information4);');
1908 build_sql(sqlstr, l_cntr_sql,
1909 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION15'', TO_CHAR(fnd_date.canonical_to_date(csr_employment_info_rec.action_information15),''YYYY-MM-DD''));');
1910 build_sql(sqlstr, l_cntr_sql,
1914 IF flex_seg_enabled ('NL_WR_EMPLOYMENT_INFO', 'ACTION_INFORMATION'||cntr) THEN
1911 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION16'', TO_CHAR(fnd_date.canonical_to_date(csr_employment_info_rec.action_information16),''YYYY-MM-DD''));');
1912 --
1913 FOR cntr in REVERSE 5..6 LOOP
1915 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION'|| cntr ||''', csr_employment_info_rec.action_information' || cntr ||');');
1916 END IF;
1917 END LOOP;
1918 --
1919 --
1920 generate_person(p_action_context_id,'CORRECTION',p_nwr_report_type);
1921 --
1922 generate_ip_srg_nr(p_action_context_id,'CORRECTION',p_nwr_report_type);
1923 --
1924 -- Initial Employment Relation </InkomstenverhoudingInitieel>
1925 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''InkomstenverhoudingInitieel'', NULL);');
1926 --
1927 IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'I' THEN
1928 build_sql(sqlstr, l_cntr_sql, 'END IF; END LOOP; ');
1929 END IF;
1930 --
1931 IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'E' THEN
1932 build_sql(sqlstr, l_cntr_sql, ' END IF; END; ');
1933 END IF;
1934 --
1935 build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
1936 --
1937 --# 3.2
1938 END IF;
1939 --#
1940 --# 3.3
1941 IF p_nwr_report_type IN ('NLNWR_XML','NLNWR_WER','NLNWR_COMPLETE') THEN
1942 --#
1943 --
1944
1945 build_sql(sqlstr, l_cntr_sql,
1946 'FOR csr_employment_info_rec IN pay_nl_nwr_xml_extract_pkg.csr_employment_info ('||
1947 p_action_context_id||',''NL_WR_EMPLOYMENT_INFO'',''WITHDRAWAL'',csr_correction_info_rec.start_date,csr_correction_info_rec.end_date,'''|| p_sort_order ||''') LOOP ');
1948 --
1949 IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'I' THEN
1950 --
1951 build_sql(sqlstr, l_cntr_sql, 'FOR csr_assignment_filter_rec IN pay_nl_nwr_xml_extract_pkg.csr_assignment_filter ('||p_assignment_set_id||',csr_employment_info_rec.assignment_id) LOOP ');
1952 build_sql(sqlstr, l_cntr_sql, 'IF csr_assignment_filter_rec.INCLUDE_OR_EXCLUDE = ''I'' THEN ');
1953 END IF;
1954 --
1955 IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'E' THEN
1956 --
1957 build_sql(sqlstr, l_cntr_sql, 'DECLARE l_flag VARCHAR2(1) := ''Y''; BEGIN ');
1958 build_sql(sqlstr, l_cntr_sql, 'FOR csr_assignment_filter_rec IN pay_nl_nwr_xml_extract_pkg.csr_assignment_filter ('||p_assignment_set_id||',csr_employment_info_rec.assignment_id) LOOP ');
1959 build_sql(sqlstr, l_cntr_sql, 'IF csr_assignment_filter_rec.INCLUDE_OR_EXCLUDE = ''E'' THEN l_flag := ''N''; END IF; END LOOP; ');
1960 build_sql(sqlstr, l_cntr_sql, 'IF l_flag = ''Y'' THEN ');
1961 END IF;
1962 --
1963 -- Withdrawal Employment Relation <InkomstenverhoudingIntrekking>
1964 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''InkomstenverhoudingIntrekking'', NULL);');
1965 --
1966 IF p_nwr_report_type <> 'NLNWR_XML' THEN
1967 --
1968 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION8'', csr_message_info_rec.action_information8);');
1969 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION9'', csr_message_info_rec.action_information9);');
1970 --
1971 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''DatAanvTv'', TO_CHAR(fnd_date.canonical_to_date(csr_correction_info_rec.start_date),''YYYY-MM-DD''));');
1972 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''DatEindTv'', TO_CHAR(fnd_date.canonical_to_date(csr_correction_info_rec.end_date),''YYYY-MM-DD''));');
1973 --
1974 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION18'', csr_employment_info_rec.action_information18);');
1975 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION4'', csr_employment_info_rec.action_information4);');
1976 build_sql(sqlstr, l_cntr_sql,
1977 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION15'', TO_CHAR(fnd_date.canonical_to_date(csr_employment_info_rec.action_information15),''YYYY-MM-DD''));');
1978 build_sql(sqlstr, l_cntr_sql,
1979 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION16'', TO_CHAR(fnd_date.canonical_to_date(csr_employment_info_rec.action_information16),''YYYY-MM-DD''));');
1980 --
1981 FOR cntr in 5..6 LOOP
1982 IF flex_seg_enabled ('NL_WR_EMPLOYMENT_INFO', 'ACTION_INFORMATION'||cntr) THEN
1983 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION'|| cntr ||''', csr_employment_info_rec.action_information' || cntr ||');');
1984 END IF;
1985 END LOOP;
1986 --
1987 generate_person(p_action_context_id,'WITHDRAWAL',p_nwr_report_type);
1988 --
1989 END IF;
1993
1990 --
1991 IF p_nwr_report_type = 'NLNWR_XML' THEN
1992 --
1994 IF g_year = '2006' THEN
1995 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION4'', csr_employment_info_rec.action_information4);');
1996 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION5'', csr_employment_info_rec.action_information5);');
1997 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION8'', csr_employment_info_rec.action_information8);');
1998 ELSE
1999 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION18'', csr_employment_info_rec.action_information18);');
2000 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION4'', csr_employment_info_rec.action_information4);');
2001 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION8'', csr_employment_info_rec.action_information8);');
2002 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION5'', csr_employment_info_rec.action_information5);');
2003 END IF;
2004 --
2005 END IF;
2006 --
2007 -- Withdrawal Employment Relation </InkomstenverhoudingIntrekking>
2008 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''InkomstenverhoudingIntrekking'', NULL);');
2009 --
2010 IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'I' THEN
2011 build_sql(sqlstr, l_cntr_sql, 'END IF; END LOOP; ');
2012 END IF;
2013 --
2014 IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'E' THEN
2015 build_sql(sqlstr, l_cntr_sql, ' END IF; END; ');
2016 END IF;
2017 --
2018 build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
2019 --
2020 --# 3.3
2021 END IF;
2022 --#
2023 END IF; --10100628
2024
2025 --IF (l_prev_corr_year = '-9999' OR (l_prev_corr_year <> '-9999' AND l_ER_ONLY = 'N' ) ) THEN --10100628 --13106837
2026 --# 3.4
2027 IF p_nwr_report_type IN ('NLNWR_XML','NLNWR_COER','NLNWR_COMPLETE') THEN
2028 --#
2029
2030 build_sql(sqlstr, l_cntr_sql,
2031 'FOR csr_employment_info_rec IN pay_nl_nwr_xml_extract_pkg.csr_employment_info ('||
2032 p_action_context_id||',''NL_WR_EMPLOYMENT_INFO'',''CORRECT'',csr_correction_info_rec.start_date,csr_correction_info_rec.end_date,'''|| p_sort_order ||''') LOOP ');
2033 --
2034 IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'I' THEN
2035 --
2036 build_sql(sqlstr, l_cntr_sql, 'FOR csr_assignment_filter_rec IN pay_nl_nwr_xml_extract_pkg.csr_assignment_filter ('||p_assignment_set_id||',csr_employment_info_rec.assignment_id) LOOP ');
2037 build_sql(sqlstr, l_cntr_sql, 'IF csr_assignment_filter_rec.INCLUDE_OR_EXCLUDE = ''I'' THEN ');
2038 END IF;
2039 --
2040 IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'E' THEN
2041 --
2042 build_sql(sqlstr, l_cntr_sql, 'DECLARE l_flag VARCHAR2(1) := ''Y''; BEGIN ');
2043 build_sql(sqlstr, l_cntr_sql, 'FOR csr_assignment_filter_rec IN pay_nl_nwr_xml_extract_pkg.csr_assignment_filter ('||p_assignment_set_id||',csr_employment_info_rec.assignment_id) LOOP ');
2044 build_sql(sqlstr, l_cntr_sql, 'IF csr_assignment_filter_rec.INCLUDE_OR_EXCLUDE = ''E'' THEN l_flag := ''N''; END IF; END LOOP; ');
2045 build_sql(sqlstr, l_cntr_sql, 'IF l_flag = ''Y'' THEN ');
2046 END IF;
2047 --
2048 -- Correction Employment relation <InkomstenverhoudingCorrectie>
2049 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''InkomstenverhoudingCorrectie'', NULL);');
2050 --
2051 IF p_nwr_report_type <> 'NLNWR_XML' THEN
2052 --
2053 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION8'', csr_message_info_rec.action_information8);');
2054 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', ''NL_WR_EMPLOYER_INFO'', ''ACTION_INFORMATION9'', csr_message_info_rec.action_information9);');
2055 --
2056 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''DatAanvTv'', TO_CHAR(fnd_date.canonical_to_date(csr_correction_info_rec.start_date),''YYYY-MM-DD''));');
2057 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''DatEindTv'', TO_CHAR(fnd_date.canonical_to_date(csr_correction_info_rec.end_date),''YYYY-MM-DD''));');
2058 --
2059 END IF;
2060 --
2061 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION4'', csr_employment_info_rec.action_information4);');
2062 build_sql(sqlstr, l_cntr_sql,
2063 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION15'', TO_CHAR(fnd_date.canonical_to_date(csr_employment_info_rec.action_information15),''YYYY-MM-DD''));');
2064 build_sql(sqlstr, l_cntr_sql,
2065 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION16'', TO_CHAR(fnd_date.canonical_to_date(csr_employment_info_rec.action_information16),''YYYY-MM-DD''));');
2066 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION6'', csr_employment_info_rec.action_information6);');
2067 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION5'', csr_employment_info_rec.action_information5);');
2068 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_EMPLOYMENT_INFO'', ''ACTION_INFORMATION8'', csr_employment_info_rec.action_information8);');
2069 --
2070 IF p_nwr_report_type <> 'NLNWR_XML' THEN
2071 generate_person(p_action_context_id,'CORRECT',p_nwr_report_type);
2072 END IF;
2073 --
2074 generate_ip_srg_nr(p_action_context_id,'CORRECT',p_nwr_report_type);
2075 --
2076 -- Correction Employment relation </InkomstenverhoudingCorrectie>
2077 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''InkomstenverhoudingCorrectie'', NULL);');
2078 --
2079 IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'I' THEN
2080 build_sql(sqlstr, l_cntr_sql, 'END IF; END LOOP; ');
2081 END IF;
2082 --
2083 IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'E' THEN
2084 build_sql(sqlstr, l_cntr_sql, ' END IF; END; ');
2085 END IF;
2086 --
2087 build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
2088 --
2089 --# 3.4
2090 END IF;
2091
2092 --END IF; --10100628 --13106837
2093
2094 --#
2095 -- Correction Report </TijdvakCorrectie>
2096 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''TijdvakCorrectie'', NULL);');
2097 --
2098 build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
2099 --
2100 --# 3
2101 END IF;
2102 --#
2103 END IF; --13106837
2104 -- Administrative Unit </AdministratieveEenheid>
2105 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''AdministratieveEenheid'', NULL);');
2106 --
2107 -- End of the Main Loop
2108 --
2109 build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
2110 -- END </Loonaangifte>
2111 IF l_payroll_type = 'YEARLY' AND p_nwr_report_type = 'NLNWR_XML' THEN
2112 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''Jaarloonopgaaf'', NULL);');
2113 ELSE
2114 build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''Loonaangifte'', NULL);');
2115 END IF;
2116 build_sql(sqlstr, l_cntr_sql, 'END;');
2117 --
2118 csr := dbms_sql.open_cursor;
2119 dbms_sql.parse (csr,
2120 sqlstr,
2121 sqlstr.first(),
2122 sqlstr.last(),
2123 false,
2124 dbms_sql.v7);
2125 ret := dbms_sql.execute(csr);
2126 dbms_sql.close_cursor(csr);
2127 --
2128 WritetoCLOB(p_xfdf_string => l_xml);
2129 --
2130 p_xml := l_xml;
2131 --
2132 dbms_lob.freeTemporary(l_xml);
2133 --
2134 g_xml_nwr.delete();
2135 --
2136 END generate;
2137 --
2138 END pay_nl_nwr_xml_extract_pkg;