eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
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.8.12010000.4 2008/09/30 06:48:07 rsahai ship $ */
3 --
4 TYPE char_tab IS TABLE OF pay_action_information.action_information1%type INDEX BY BINARY_INTEGER;
5 g_xml_nwr   char_tab;
6 --
7 g_action_ctx_id     NUMBER;
8 sqlstr              DBMS_SQL.VARCHAR2S;
9 l_cntr_sql          NUMBER;
10 g_year              VARCHAR2(10);
11 g_report_type       VARCHAR2(20);
12 EOL			  VARCHAR2(5)   := fnd_global.local_chr(10);	--7283669
13 --
14 -------------------------------------------------------------------------------
15 -- get_IANA_charset
16 -------------------------------------------------------------------------------
17 FUNCTION get_IANA_charset RETURN VARCHAR2 IS
18     CURSOR csr_get_iana_charset IS
19         SELECT tag
20           FROM fnd_lookup_values
21          WHERE lookup_type = 'FND_ISO_CHARACTER_SET_MAP'
22            AND lookup_code = SUBSTR(USERENV('LANGUAGE'),
23                                     INSTR(USERENV('LANGUAGE'), '.') + 1)
24            AND language = 'US';
25 
26     lv_iana_charset fnd_lookup_values.tag%type;
27 BEGIN
28     OPEN csr_get_iana_charset;
29         FETCH csr_get_iana_charset INTO lv_iana_charset;
30     CLOSE csr_get_iana_charset;
31 
32     hr_utility.trace('IANA Charset = '||lv_iana_charset);
33     RETURN (lv_iana_charset);
34 END get_IANA_charset;
35 --
36 -------------------------------------------------------------------------------
37 -- TO_UTF8
38 --------------------------------------------------------------------------------
39 FUNCTION TO_UTF8(str in varchar2 )RETURN VARCHAR2 AS
40    db_charset varchar2(120);
41 BEGIN
42     SELECT value
43     INTO db_charset
44     FROM nls_database_parameters
45     WHERE parameter = 'NLS_CHARACTERSET';
46     RETURN CONVERT(str,'UTF8',db_charset);
47 END;
48 -------------------------------------------------------------------------------
49 -- WRITETOCLOB
50 --------------------------------------------------------------------------------
51 PROCEDURE WritetoCLOB (p_xfdf_string out nocopy clob) IS
52      l_str VARCHAR2(240);
53      l_str1 VARCHAR2(4000);
54      l_concat_str VARCHAR2(32000);
55      l_len    NUMBER;
56  BEGIN
57      --l_str := '<?xml version="1.0" encoding="UTF-8"?> <Loonaangifte></Loonaangifte>';
58      l_str := '<?xml version="1.0" encoding="' || get_IANA_charset || '"?> <Loonaangifte></Loonaangifte>';
59      dbms_lob.createtemporary(p_xfdf_string,FALSE,DBMS_LOB.CALL);
60      dbms_lob.open(p_xfdf_string,dbms_lob.lob_readwrite);
61      IF g_xml_nwr.count > 0 THEN
62        l_concat_str := '';
63        FOR ctr_table IN g_xml_nwr.FIRST .. g_xml_nwr.LAST LOOP
64            l_concat_str := l_concat_str || g_xml_nwr(ctr_table);
65            l_len := length(l_concat_str);
66            IF  l_len  > 28000 then
67            dbms_lob.writeAppend( p_xfdf_string, l_len, l_concat_str);
68            l_concat_str := '';
69            END IF;
70        END LOOP;
71        IF length(l_concat_str) > 0 THEN
72          dbms_lob.writeAppend( p_xfdf_string, LENGTH(l_concat_str), l_concat_str);
73        END IF;
74      ELSE
75          --l_str1 := CONVERT(l_str,'UTF8');
76          dbms_lob.writeAppend( p_xfdf_string, LENGTH(l_str), l_str );
77      END IF;
78  END WritetoCLOB;
79 -------------------------------------------------------------------------------
80 -- YES_NO
81 --------------------------------------------------------------------------------
82 FUNCTION yes_no(p_yn VARCHAR2) RETURN VARCHAR2 IS
83 BEGIN
84 	IF 	p_yn = 'J' THEN
85 	    RETURN  'Y';
86 	ELSE
87 	    RETURN p_yn;
88 	END IF;
89 END yes_no;
90 --------------------------------------------------------------------------------
91 --    Name        : GET_TAG_DESCRIPTION
92 --    Description : This Function returns the Tag Description when the tag name
93 --                  is provided   .
94 --------------------------------------------------------------------------------
95 FUNCTION get_tag_description(p_tag    VARCHAR2)
96 RETURN VARCHAR2
97 IS
98 CURSOR csr_get_tag_descr(l_tag VARCHAR2)
99 IS
100 SELECT  meaning
101        ,nvl(start_date_active,fnd_date.canonical_to_date('0001/01/01 00:00:00'))  active_date
102 FROM   hr_lookups
103 WHERE lookup_type = 'NL_FORM_LABELS'
104   --AND lookup_code like l_tag||'%'
105   AND (lookup_code like l_tag||'%'||g_year OR lookup_code = l_tag)
106   AND nvl(to_char(start_date_active,'RRRR'),'0001') <= g_year
107   AND nvl(to_char(end_date_active,'RRRR'),'4712') >= g_year
108   AND enabled_flag = 'Y'
109 --ORDER BY 2 desc;
110   ORDER BY active_date desc, lookup_code desc ;
111 
112 l_temp_data csr_get_tag_descr%ROWTYPE;
113 l_description   VARCHAR2(500);
114 
115 BEGIN
116   IF p_tag IS NOT NULL THEN
117     OPEN csr_get_tag_descr(p_tag);
118     FETCH csr_get_tag_descr INTO l_temp_data;
119     IF csr_get_tag_descr%NOTFOUND THEN
120       l_description := NULL;
121     ELSE
122       l_description := l_temp_data.meaning;
123     END IF;
124     CLOSE csr_get_tag_descr;
125   ELSE
126     l_description := NULL;
127   END IF;
128   RETURN l_description;
129 END get_tag_description;
130 --------------------------------------------------------------------------------
131 --    Name        : GET_TAG_NAME
132 --    Description : This Function returns the Tag Name when the context
133 --                  and column are specified.
134 --------------------------------------------------------------------------------
135 FUNCTION get_tag_name (p_context_code         VARCHAR2
136                       ,p_node                 VARCHAR2) RETURN VARCHAR2 AS
137     --
138     CURSOR csr_get_tag_name IS
139     SELECT TRANSLATE ((description), ' /','__') tag_name
140     FROM  fnd_descr_flex_col_usage_tl
141     WHERE application_id                = 801
142     AND   source_lang = 'US'
143     AND   descriptive_flexfield_name    = 'Action Information DF'
144     AND   descriptive_flex_context_code = p_context_code
145     AND   application_column_name       = UPPER(p_node);
146     --
147     CURSOR csr_inv_seg_check(l_tag VARCHAR2) IS
148     SELECT 'N'
149     FROM hr_lookups
150     WHERE lookup_type = 'PAY_NL_INVALID_WR_TAGS'
151     AND lookup_code   = upper(l_tag)
152     AND description   =  l_tag
153     AND enabled_flag  = 'Y'
154     AND (to_char(start_date_active,'RRRR') <= g_year
155          AND nvl(to_char(end_date_active,'RRRR'),'4712') >= g_year);
156 
157   l_tag_name  fnd_descr_flex_col_usage_tl.description%TYPE;
158   l_display   VARCHAR2(1);
159     --
160 BEGIN
161     --
162    IF (p_context_code <> p_node) THEN
163     OPEN csr_get_tag_name;
164         FETCH csr_get_tag_name INTO l_tag_name;
165     CLOSE csr_get_tag_name;
166    ELSE
167      l_tag_name := p_node;
168    END IF;
169     --
170      IF g_year >= '2008' THEN
171       OPEN csr_inv_seg_check(l_tag_name);
172       FETCH csr_inv_seg_check INTO l_display;
173       IF csr_inv_seg_check%FOUND THEN
174         l_tag_name := NULL;
175       END IF;
176       CLOSE csr_inv_seg_check;
177     END IF;
178 
179    RETURN l_tag_name;
180     --
181 END get_tag_name;
182 --
183 --------------------------------------------------------------------------------
184 --    Name        : LOAD_XML_INTERNAL
185 --    Description : This procedure loads the global XML cache.
186 --------------------------------------------------------------------------------
187 PROCEDURE load_xml_internal (P_NODE_TYPE         VARCHAR2
188                             ,P_NODE              VARCHAR2
189                             ,P_DATA              VARCHAR2) AS
190     l_proc_name VARCHAR2(100);
191     l_data      pay_action_information.action_information1%TYPE;
192     --
193 BEGIN
194     --
195     IF p_node_type = 'CS' THEN
196         g_xml_nwr (g_xml_nwr.count() + 1) := '<'||p_node||'>'||EOL;
197     ELSIF p_node_type = 'CE' THEN
198         g_xml_nwr (g_xml_nwr.count() + 1) := '</'||p_node||'>'||EOL;
199     ELSIF p_node_type = 'D' AND p_data IS NOT NULL THEN
200         /* Handle special charaters in data */
201         l_data := REPLACE (p_data, '&', '&');
202         l_data := REPLACE (l_data, '>', '>');
203         l_data := REPLACE (l_data, '<', '<');
204         l_data := REPLACE (l_data, '''', ''');
205         l_data := REPLACE (l_data, '"', '"');
206         g_xml_nwr (g_xml_nwr.count() + 1) := '<'||p_node||'>'||l_data||'</'||p_node||'>'||EOL;
207     ELSIF p_node_type = 'CD' AND p_data IS NOT NULL THEN
208         l_data := p_data;
209         g_xml_nwr (g_xml_nwr.count() + 1) := '<'||p_node||'><![CDATA['||l_data||']]></'||p_node||'>'||EOL;
210     END IF;
211     --
212     END load_xml_internal;
213 --
214 --------------------------------------------------------------------------------
215 --    Name        : LOAD_XML
216 --    Description : This procedure loads the global XML cache.
217 --------------------------------------------------------------------------------
218 --
219 PROCEDURE load_xml (p_node_type     VARCHAR2
220                    ,p_context_code  VARCHAR2
221                    ,p_node          VARCHAR2
222                    ,p_data          VARCHAR2) AS
223     --
224     CURSOR csr_get_tag_name IS
225     SELECT TRANSLATE ((description), ' /','__') tag_name
226     FROM  fnd_descr_flex_col_usage_tl
227     WHERE application_id                = 801
228     AND   source_lang = 'US'
229     AND   descriptive_flexfield_name    = 'Action Information DF'
230     AND   descriptive_flex_context_code = p_context_code
231     AND   application_column_name       = UPPER (p_node);
232 
233     CURSOR csr_inv_seg_check(l_tag VARCHAR2) IS
234     SELECT 'N'
235     FROM hr_lookups
236     WHERE lookup_type = 'PAY_NL_INVALID_WR_TAGS'
237     AND lookup_code   = upper(l_tag)
238     AND description   =  l_tag
239     AND enabled_flag  = 'Y'
240     AND (to_char(start_date_active,'RRRR') <= g_year
241          AND nvl(to_char(end_date_active,'RRRR'),'4712') >= g_year);
242     --
243 
244     l_proc_name VARCHAR2(100);
245     l_tag_name  VARCHAR2(500);
246     l_data      pay_action_information.action_information1%TYPE;
247     l_display   VARCHAR2(1);
248 
249     --
250 BEGIN
251     --
252     l_display := 'Y';
253     IF p_node_type in ('D','CD') THEN
254         --
255         -- Fetch segment names
256         --
257         OPEN csr_get_tag_name;
258             FETCH csr_get_tag_name INTO l_tag_name;
259         CLOSE csr_get_tag_name;
260     END IF;
261     --
262     IF g_xml_nwr.count() <> 0 AND UPPER(p_node) NOT LIKE 'XAPI%' THEN
263         l_tag_name := nvl(l_tag_name, TRANSLATE(p_node, ' /', '__'));
264     ELSE
265         l_tag_name := p_node;
266     END IF;
267     --
268     IF g_year >= '2008'  THEN
269       OPEN csr_inv_seg_check(l_tag_name);
270       FETCH csr_inv_seg_check INTO l_display;
271       IF csr_inv_seg_check%NOTFOUND THEN
272         l_display := 'Y';
273       ELSE
274         l_display := 'N';
275       END IF;
276       CLOSE csr_inv_seg_check;
277     END IF;
278     IF l_display = 'Y' THEN
279        l_data :=  p_data;
280        IF p_node = 'Description' THEN     /***** for Description .. the tag name is fed through the p_data *****/
281          l_data := get_tag_description(UPPER(p_data));
282        END IF;
283       IF g_report_type <> 'NLNWR_XML' AND p_context_code = 'NL_WR_INCOME_PERIOD' AND UPPER(l_tag_name) = 'CDAGH' THEN
284       /** to avoid conflict between CdAGH in XML and CdAGH in the PDF output ***/
285         l_data := NULL;
286       END IF;
287       load_xml_internal (p_node_type, l_tag_name, l_data);
288     END IF;
289     --
290 END load_xml;
291 --
292 
293 
294 --------------------------------------------------------------------------------
295 --    Name        : FLEX_SEG_ENABLED
296 --    Description : This function returns TRUE if an application column is
297 --                  registered with given context of Action Information DF.
298 --                  Otherwise, it returns false.
299 --------------------------------------------------------------------------------
300 --
301 FUNCTION flex_seg_enabled(p_context_code              VARCHAR2
302                          ,p_application_column_name   VARCHAR2) RETURN BOOLEAN AS
303     --
304     CURSOR csr_seg_enabled IS
305     SELECT 'Y'
306     FROM fnd_descr_flex_col_usage_vl
307     WHERE application_id                 = 801
308     AND descriptive_flexfield_name  LIKE 'Action Information DF'
309     AND descriptive_flex_context_code    =  p_context_code
310     AND application_column_name       LIKE  p_application_column_name
311     AND enabled_flag                     =  'Y';
312     --
313     l_exists    varchar2(1);
314     --
315 BEGIN
316     --
317     OPEN csr_seg_enabled;
318         FETCH csr_seg_enabled INTO l_exists;
319     CLOSE csr_seg_enabled;
320     --
321     IF l_exists = 'Y' THEN
322         RETURN (TRUE);
323     ELSE
327 END flex_seg_enabled;
324         RETURN (FALSE);
325     END IF;
326     --
328 --
329 --------------------------------------------------------------------------------
330 --    Name        : BUILD_SQL
331 --    Description : This procedure builds dynamic SQL string.
332 --------------------------------------------------------------------------------
333 --
334 PROCEDURE build_sql(p_sqlstr_tab    IN OUT NOCOPY DBMS_SQL.VARCHAR2S
335                    ,p_cntr          IN OUT NOCOPY NUMBER
336                    ,p_string        VARCHAR2) AS
337     --
338     l_proc_name varchar2(100);
339     --
340 BEGIN
341     p_sqlstr_tab(p_cntr) := p_string;
342     p_cntr               := p_cntr + 1;
343 END;
344 --
345 --
346 --------------------------------------------------------------------------------
347 --    Name        : GET_LE_NAME
348 --    Description : This function returns the REPORTING NAME of the
349 --                  Legal Employer
350 --------------------------------------------------------------------------------
351 --
352 FUNCTION get_le_name(p_payroll_action_id      NUMBER) RETURN VARCHAR2 AS
353     --
354     CURSOR csr_get_name(p_organization_id NUMBER) IS
355     SELECT hoi1.org_information14
356     FROM hr_organization_units hou
357         ,hr_organization_information hoi
358         ,hr_organization_information hoi1
359     WHERE hou.organization_id = hoi.organization_id
360     AND	  hou.organization_id = hoi1.organization_id(+)
361     AND	  hou.organization_id = p_organization_id
362     AND	  hoi.org_information_context = 'CLASS'
363     AND	  hoi1.org_information_context(+) = 'NL_ORG_INFORMATION';
364     --
365     l_name              hr_organization_information.org_information14%TYPE;
366     l_legal_emplr_id    NUMBER(15);
367     --
368 BEGIN
369     --
370     l_legal_emplr_id := pay_nl_wage_report_pkg.get_parameters(p_payroll_action_id, 'Legal_Employer');
371     --
372     OPEN csr_get_name(l_legal_emplr_id);
373     FETCH csr_get_name INTO l_name;
374     CLOSE csr_get_name;
375     --
376     RETURN l_name;
377     --
378 END get_le_name;
379 --
380 --------------------------------------------------------------------------------
381 --    Name        : GENERATE_COLLECTIVE_REPORT
382 --    Description : This Procedure is used to generate the XML part for
383 --                  the tags in the Collective Report Part.
384 --------------------------------------------------------------------------------
385 --
386 PROCEDURE generate_collective_report( p_act_context_id       NUMBER
387                                      ,p_type                 VARCHAR2
388                                      ,p_start_date           VARCHAR2
389                                      ,p_end_date             VARCHAR2
390                                      ,p_in_not_in            VARCHAR2
391                                      ,p_report_type          VARCHAR2) AS
392 --
393 BEGIN
394 --
395    build_sql(sqlstr, l_cntr_sql, 'DECLARE l_col_tag fnd_descr_flex_col_usage_vl.description%TYPE; BEGIN ');
396 
397     --
398     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 ');
399     --
400     build_sql(sqlstr, l_cntr_sql, 'IF csr_collective_info_rec.action_information2 '|| p_in_not_in ||'  (''IngBijdrZvw'',''AGHKort'',''TotTeBet'') THEN ');
401     --
402     IF p_report_type = 'NLNWR_XML' THEN
403         build_sql(sqlstr, l_cntr_sql,
404         '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)));');
405     ELSE
406         --
407         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); ');
408           build_sql(sqlstr, l_cntr_sql, 'IF l_col_tag IS NOT NULL THEN ');
409            build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''CRRecords'', NULL);');
410            build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Tag'', csr_collective_info_rec.action_information2);');
411             --build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'', csr_collective_info_rec.action_information5);');
412               build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'', csr_collective_info_rec.action_information2);');
413             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)));');
414           --
415            build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''CRRecords'', NULL);');
416            build_sql(sqlstr, l_cntr_sql, 'END IF; ');
417         END IF;
418     --
419     build_sql(sqlstr, l_cntr_sql, 'END IF;');
420     --
421     build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
422   build_sql(sqlstr, l_cntr_sql, 'END ;');
423 
424 --
425 END generate_collective_report;
426 --
427 --------------------------------------------------------------------------------
428 --    Name        : GENERATE_IP_SRG_NR
429 --    Description : This Procedure is used to generate the XML part for
430 --                 (Income Period - Sector Rsik Group - Nominative Report).
431 --                  Instead of repeating it thrice, we call this Proc Thrice
432 --------------------------------------------------------------------------------
436                              ,p_report_type          VARCHAR2) AS
433 --
434 PROCEDURE generate_ip_srg_nr( p_action_context_id    NUMBER
435                              ,p_type                 VARCHAR2
437 --
438 BEGIN
439 --
440     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 ');
441     --
442     -- Income Period <Inkomstenperiode>
443     build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''Inkomstenperiode'', NULL);');
444         --
445         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''));');
446         --
447           FOR cntr in 6..30 LOOP
448             IF flex_seg_enabled ('NL_WR_INCOME_PERIOD', 'ACTION_INFORMATION'||cntr) THEN
449                 IF cntr = 13 THEN
450                   IF g_year >= '2008' THEN
451                     build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec.action_information13 IS NULL THEN ');
452                       build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_INCOME_PERIOD'', ''ACTION_INFORMATION13'', NULL);');
453                     build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information13 = ''0'' THEN ');
454                       build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_INCOME_PERIOD'', ''ACTION_INFORMATION13'', ''0'');');
455                     build_sql(sqlstr, l_cntr_sql, 'ELSE ');
456                       build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', ''NL_WR_INCOME_PERIOD'', ''ACTION_INFORMATION13'', ''5'');');
457                     build_sql(sqlstr, l_cntr_sql, 'END IF; ');
458                   ELSE
459                     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);');
460                   END IF;
461                 ELSE
462                   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 ||');');
463                 END IF;
464             END IF;
465           END LOOP;
466         IF p_report_type <> 'NLNWR_XML' THEN
467         --
468         --build_sql(sqlstr, l_cntr_sql, 'Declare l_code_contract VARCHAR2(50); Begin ');
469         --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; ');
470         --build_sql(sqlstr, l_cntr_sql, 'End; ');
471 
472          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));');
473          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));');
474          build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CdInvlVplM'', hr_general.decode_lookup(''NL_INFLUENCE_CODE'', csr_income_info_rec.action_information8));');
475          --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));');
476          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));');
477          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));');
478          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)));');
479          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));');
480          IF g_year < '2008' THEN
481            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''));');
482          ELSE
483            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''));');
484          END IF;
485          IF g_year >= '2008' THEN
486           build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec.action_information13 IS NULL THEN ');
487            build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''CdAGHM'', NULL); ');
488           build_sql(sqlstr, l_cntr_sql, 'ELSIF csr_income_info_rec.action_information13 = ''0'' THEN ');
489            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'')); ');
490           build_sql(sqlstr, l_cntr_sql, 'ELSE ');
491            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'')); ');
492           build_sql(sqlstr, l_cntr_sql, 'END IF; ');
493          ELSE
494            build_sql(sqlstr, l_cntr_sql,
495            '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));');
499          --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));');
496          END IF;
497          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)));');
498          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));');
500          IF g_year = '2006' THEN
501             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''));');
502          ELSE
503             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''));');
504          END IF;
505          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)));');
506          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)));');
507          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)));');
508          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));');
509          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)));');
510          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)));');
511          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)));');
512          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)));');
513          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)));');
514          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));');
515          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)));');
516         END IF;
517         --
518     -- Income Period </Inkomstenperiode>
519     build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''Inkomstenperiode'', NULL);');
520     --
521     build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
522     --
523     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 ');
524     --
525     -- Sector Risk Group <SectorRisicogroep>
526     build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''SectorRisicogroep'', NULL);');
527         --
528              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''));');
529              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''));');
530         FOR cntr in 7..8 LOOP
531             IF flex_seg_enabled ('NL_WR_SWMF_SECTOR_RISK_GROUP', 'ACTION_INFORMATION'||cntr) THEN
532              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 ||');');
533             END IF;
534         END LOOP;
535         --
536     -- Sector Risk Group </SectorRisicogroep>
537     build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''SectorRisicogroep'', NULL);');
538     --
539     build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
540     --
541     build_sql(sqlstr, l_cntr_sql, 'DECLARE l_tag fnd_descr_flex_col_usage_vl.description%TYPE; BEGIN ');
542     --
543     -- Nominative Report <NominatieveAangifte>
544     --build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NominatieveAangifte'', NULL);');
545     --
546     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 ');
547     --
548     -- Nominative Report <NominatieveAangifte>
549     IF g_year = '2006' OR p_report_type <> 'NLNWR_XML' THEN
550         build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NominatieveAangifte'', NULL);');
551     ELSE
552         IF g_year >= '2008' THEN
556         END IF;
553           build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''Werknemersgegevens'', NULL);');
554         ELSE
555           build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''Werknemergegevens'', NULL);');
557     END IF;
558         --
559         FOR cntr in 5..14 LOOP
560             IF flex_seg_enabled ('NL_WR_NOMINATIVE_REPORT', 'ACTION_INFORMATION'||cntr) THEN
561                 --
562                 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec.action_information' || cntr ||' IS NOT NULL THEN ');
563                 --
564                 IF p_report_type = 'NLNWR_XML' THEN
565                      --
566                      build_sql(sqlstr, l_cntr_sql,
567 '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''));');
568                      --
569                 ELSE
570 
571                     --
572                     build_sql(sqlstr, l_cntr_sql, 'l_tag :=  pay_nl_nwr_xml_extract_pkg.get_tag_name(''NL_WR_NOMINATIVE_REPORT'',''ACTION_INFORMATION'|| cntr ||''');');
573                     --
574                       build_sql(sqlstr, l_cntr_sql, 'IF l_tag IS NOT NULL THEN ');
575                       build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NRRecords'', NULL);');
576                         build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Tag'', l_tag );');
577                          build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'', l_tag); ');
578                         build_sql(sqlstr, l_cntr_sql,
579                         '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''));');
580                       build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NRRecords'', NULL);');
581                       build_sql(sqlstr, l_cntr_sql, 'END IF; ');
582                     --
583 
584                 END IF;
585                 --
586              build_sql(sqlstr, l_cntr_sql, 'END IF; ');
587              --
588             END IF;
589         END LOOP;
590         --
591         -- Added for the New Context NL_WR_NOMINATIVE_REPORT_ADD
592         --
593         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 ');
594         --
595         FOR cntr1 in 10..10 LOOP
596             IF flex_seg_enabled ('NL_WR_NOMINATIVE_REPORT_ADD', 'ACTION_INFORMATION'||cntr1) THEN
597                 --
598                 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec1.action_information' || cntr1 ||' IS NOT NULL THEN ');
599                 --
600                 IF p_report_type = 'NLNWR_XML' THEN
601                      --
602                      build_sql(sqlstr, l_cntr_sql,
603     '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''));');
604                      --
605                 ELSE
606                     --
607                     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 ||''');');
608                     --
609                        build_sql(sqlstr, l_cntr_sql, 'IF l_tag IS NOT NULL THEN ');
610                         build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NRRecords'', NULL);');
611                          build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Tag'', l_tag );');
612                          build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'', l_tag); ');
613                         build_sql(sqlstr, l_cntr_sql,
614                         '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''));');
615                     build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NRRecords'', NULL);');
616                     build_sql(sqlstr, l_cntr_sql, 'END IF; ');
617                     END IF;
618                 --
619              build_sql(sqlstr, l_cntr_sql, 'END IF; ');
620              --
621             END IF;
622         END LOOP;
623         build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
624         --
625         --
626         FOR cntr in 15..22 LOOP
627             IF flex_seg_enabled ('NL_WR_NOMINATIVE_REPORT', 'ACTION_INFORMATION'||cntr) THEN
628                 --
629                 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec.action_information' || cntr ||' IS NOT NULL THEN ');
630                 --
631                 IF p_report_type = 'NLNWR_XML' THEN
632                      --
633                      build_sql(sqlstr, l_cntr_sql,
634 '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''));');
635                      --
636                 ELSE
637                      --
638                     build_sql(sqlstr, l_cntr_sql, 'l_tag :=  pay_nl_nwr_xml_extract_pkg.get_tag_name(''NL_WR_NOMINATIVE_REPORT'',''ACTION_INFORMATION'|| cntr ||''');');
642                         build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Tag'', l_tag );');
639                     --
640                       build_sql(sqlstr, l_cntr_sql, 'IF l_tag IS NOT NULL THEN ');
641                        build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NRRecords'', NULL);');
643                         IF cntr in (16,17) AND g_year = '2006' THEN
644                             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'''||'));');
645                         ELSE
646                          build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'',l_tag); ');
647                         END IF;
648                         build_sql(sqlstr, l_cntr_sql,
649                         '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''));');
650                      build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NRRecords'', NULL);');
651                      build_sql(sqlstr, l_cntr_sql, 'END IF; ');
652                     --
653                     END IF;
654                 --
655              build_sql(sqlstr, l_cntr_sql, 'END IF; ');
656              --
657             END IF;
658         END LOOP;
659         --
660         -- Added for the New Context NL_WR_NOMINATIVE_REPORT_ADD
661         --
662         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 ');
663         --
664         FOR cntr1 in 5..9 LOOP
665             IF flex_seg_enabled ('NL_WR_NOMINATIVE_REPORT_ADD', 'ACTION_INFORMATION'||cntr1) THEN
666                 --
667                 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec1.action_information' || cntr1 ||' IS NOT NULL THEN ');
668                 --
669                 IF p_report_type = 'NLNWR_XML' THEN
670                      --
671                      build_sql(sqlstr, l_cntr_sql,
672     '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''));');
673                      --
674                 ELSE
675                     --
676                     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 ||''');');
677                     --
678                        build_sql(sqlstr, l_cntr_sql, 'IF l_tag IS NOT NULL THEN ');
679                         build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NRRecords'', NULL);');
680                         build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Tag'', l_tag );');
681                          build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'', l_tag); ');
682                         build_sql(sqlstr, l_cntr_sql,
683                         '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''));');
684                        --
685                     build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NRRecords'', NULL);');
686                      build_sql(sqlstr, l_cntr_sql, 'END IF; ');
687                  END IF;
688                 --
689              build_sql(sqlstr, l_cntr_sql, 'END IF; ');
690              --
691             END IF;
692         END LOOP;
693         build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
694         --
695         --
696         FOR cntr in 23..24 LOOP
697             IF flex_seg_enabled ('NL_WR_NOMINATIVE_REPORT', 'ACTION_INFORMATION'||cntr) THEN
698                 --
699                 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec.action_information' || cntr ||' IS NOT NULL THEN ');
700                 --
701                 IF p_report_type = 'NLNWR_XML' THEN
702                      --
703                      build_sql(sqlstr, l_cntr_sql,
704 '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''));');
705                      --
706                 ELSE
707                     --
708                     build_sql(sqlstr, l_cntr_sql, 'l_tag :=  pay_nl_nwr_xml_extract_pkg.get_tag_name(''NL_WR_NOMINATIVE_REPORT'',''ACTION_INFORMATION'|| cntr ||''');');
709                     --
710                       build_sql(sqlstr, l_cntr_sql, 'IF l_tag IS NOT NULL THEN ');
711                         build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NRRecords'', NULL);');
712                         build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Tag'', l_tag );');
713                          build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'', l_tag); ');
714                         build_sql(sqlstr, l_cntr_sql,
715                         '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''));');
716                        --
720                 --
717                     build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NRRecords'', NULL);');
718                     build_sql(sqlstr, l_cntr_sql, 'END IF; ');
719                  END IF;
721              build_sql(sqlstr, l_cntr_sql, 'END IF; ');
722              --
723             END IF;
724         END LOOP;
725         --
726         FOR cntr in 25..26 LOOP
727             IF flex_seg_enabled ('NL_WR_NOMINATIVE_REPORT', 'ACTION_INFORMATION'||cntr) THEN
728                 --
729                 IF p_report_type = 'NLNWR_XML' THEN
730                     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 ||');');
731                 ELSE
732                      --
733                     build_sql(sqlstr, l_cntr_sql, 'l_tag :=  pay_nl_nwr_xml_extract_pkg.get_tag_name(''NL_WR_NOMINATIVE_REPORT'',''ACTION_INFORMATION'|| cntr ||''');');
734                     --
735                       build_sql(sqlstr, l_cntr_sql, 'IF l_tag IS NOT NULL THEN ');
736                         build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NRRecords'', NULL);');
737                         build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Tag'', l_tag);');
738                          build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'', l_tag); ');
739                         build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Amount'', csr_income_info_rec.action_information' || cntr ||');');
740                        --
741                     build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NRRecords'', NULL);');
742                     build_sql(sqlstr, l_cntr_sql, 'END IF; ');
743                END IF;
744                 --
745             END IF;
746         END LOOP;
747         --
748         FOR cntr in 27..30 LOOP
749             IF flex_seg_enabled ('NL_WR_NOMINATIVE_REPORT', 'ACTION_INFORMATION'||cntr) THEN
750                 --
751                 build_sql(sqlstr, l_cntr_sql, 'IF csr_income_info_rec.action_information' || cntr ||' IS NOT NULL THEN ');
752                 --
753                 IF p_report_type = 'NLNWR_XML' THEN
754                      --
755                      build_sql(sqlstr, l_cntr_sql,
756 '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''));');
757                      --
758                 ELSE
759                     --
760                     build_sql(sqlstr, l_cntr_sql, 'l_tag :=  pay_nl_nwr_xml_extract_pkg.get_tag_name(''NL_WR_NOMINATIVE_REPORT'',''ACTION_INFORMATION'|| cntr ||''');');
761                     --
762                       build_sql(sqlstr, l_cntr_sql, 'IF l_tag IS NOT NULL THEN ');
763                         build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NRRecords'', NULL);');
764                         build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Tag'', l_tag );');
765                          build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''Description'', l_tag); ');
766                         build_sql(sqlstr, l_cntr_sql,
767                         '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''));');
768                        --
769                     build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NRRecords'', NULL);');
770                 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
771                    END IF;
772                 --
773                 build_sql(sqlstr, l_cntr_sql, 'END IF; ');
774                 --
775             END IF;
776         END LOOP;
777         --
778     -- Nominative Report </NominatieveAangifte>
779     IF g_year = '2006' OR p_report_type <> 'NLNWR_XML' THEN
780         build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NominatieveAangifte'', NULL);');
781     ELSE
782         IF g_year >= '2008' THEN
783           build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''Werknemersgegevens'', NULL);');
784         ELSE
785           build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''Werknemergegevens'', NULL);');
786         END IF;
787     END IF;
788     --
789     build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
790     --
791     -- Nominative Report </NominatieveAangifte>
792     --build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NominatieveAangifte'', NULL);');
793     --
794     build_sql(sqlstr, l_cntr_sql, 'END ;');
795     --
796 END generate_ip_srg_nr;
797 --
798 --------------------------------------------------------------------------------
799 --    Name        : GENERATE_PERSON
800 --    Description : This Procedure is used to generate the XML part for
801 --                 (Person - Address/Foreign Address).
802 --------------------------------------------------------------------------------
803 --
804 PROCEDURE generate_person( p_action_context_id    NUMBER
805                           ,p_type                 VARCHAR2
806                           ,p_report_type          VARCHAR2) AS
807 --
808 BEGIN
809 --
810     -- Person <NatuurlijkPersoon>
814         --
811     build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''NatuurlijkPersoon'', NULL);');
812         --
813              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);');
815         FOR cntr in 9..11 LOOP
816             IF flex_seg_enabled ('NL_WR_EMPLOYMENT_INFO', 'ACTION_INFORMATION'||cntr) THEN
817              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 ||');');
818             END IF;
819         END LOOP;
820         --
821              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''));');
822         --
823         FOR cntr in 13..14 LOOP
824             IF flex_seg_enabled ('NL_WR_EMPLOYMENT_INFO', 'ACTION_INFORMATION'||cntr) THEN
825              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 ||');');
826             END IF;
827         END LOOP;
828         --
829         IF p_report_type IN ('NLNWR_IER','NLNWR_IERC','NLNWR_WER','NLNWR_COER','NLNWR_COMPLETE') THEN
830         --
831         build_sql(sqlstr, l_cntr_sql, 'Declare l_gender VARCHAR2(10); Begin ');
832         build_sql(sqlstr, l_cntr_sql, ' l_gender := NULL ; ');
833         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;');
834              --
835              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'')));');
836              build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''D'', NULL, ''GeslM'', hr_general.decode_lookup(''SEX'',l_gender));');
837              -- This is for PersNr (Person Number) - only for Audit Reports
838              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);');
839              --
840         build_sql(sqlstr, l_cntr_sql, 'END ;');
841         END IF;
842         --
843     build_sql(sqlstr, l_cntr_sql, 'FOR csr_address_info_rec IN pay_nl_nwr_xml_extract_pkg.csr_address_info
844                                     ('||p_action_context_id||',''ADDRESS DETAILS'','''||p_type||''',csr_employment_info_rec.action_information_id,csr_employment_info_rec.assignment_id) LOOP ');
845     --
846     build_sql(sqlstr, l_cntr_sql, 'IF csr_address_info_rec.action_information14 = ''EMPLOYEE'' THEN ');
847     -- Address <AdresBinnenland>
848     build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''AdresBinnenland'', NULL);');
849     --
850         --
851          build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''Str'', csr_address_info_rec.action_information9);');
852          build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''HuisNr'', csr_address_info_rec.action_information5);');
853          build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''HuisNrToev'', csr_address_info_rec.action_information6);');
854          build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''LocOms'', csr_address_info_rec.action_information11);');
855          build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''Pc'', csr_address_info_rec.action_information12);');
856          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));');
857         --
858     -- Address </AdresBinnenland>
859     build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''AdresBinnenland'', NULL);');
860     --
861     build_sql(sqlstr, l_cntr_sql, 'END IF ; ');
862     --
863     build_sql(sqlstr, l_cntr_sql, 'IF csr_address_info_rec.action_information14 = ''EMPLOYEE FOREIGN'' THEN ');
864     -- Foreign Address <AdresBuitenland>
865     build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''AdresBuitenland'', NULL);');
866     --
867          --
868          build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''Str'', csr_address_info_rec.action_information5);');
869          build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''HuisNr'', csr_address_info_rec.action_information6);');
870          build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''LocOms'', csr_address_info_rec.action_information7);');
871          build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''Pc'', csr_address_info_rec.action_information12);');
872          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));');
873          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));');
874          build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CD'', NULL, ''LandCd'', csr_address_info_rec.action_information13);');
875         --
876     -- Foreign Address </AdresBuitenland>
877     build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''AdresBuitenland'', NULL);');
878     --
882     -- Person </NatuurlijkPersoon>
879     build_sql(sqlstr, l_cntr_sql, 'END IF ; ');
880     --
881     build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
883     build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''NatuurlijkPersoon'', NULL);');
884 --
885 END generate_person;
886 --
887 --------------------------------------------------------------------------------
888 --  Name        : GENERATE
889 --  Description : This procedure interprets archived information and prints it
890 --                out to an XML file.
891 --------------------------------------------------------------------------------
892 --
893 PROCEDURE generate( p_action_context_id         NUMBER
894                    ,p_nwr_report_type           VARCHAR2
895                    --,p_xdo_output_type           VARCHAR2
896                    ,p_assignment_set_id         NUMBER
897                    ,p_sort_order                VARCHAR2
898                    ,p_template_name             VARCHAR2
899                    ,p_xml                       OUT NOCOPY CLOB) IS
900     --
901     CURSOR csr_inc_exc(p_assignment_set_id NUMBER) IS
902     SELECT include_or_exclude
903     FROM   hr_assignment_set_amendments hasa
904     WHERE  hasa.assignment_set_id = p_assignment_set_id;
905     --
906     CURSOR get_year(c_payroll_action_id NUMBER) IS
907     SELECT TO_CHAR(effective_date,'RRRR')
908     FROM   pay_payroll_actions
909     WHERE  payroll_action_id = c_payroll_action_id;
910     --
911     l_proc_name      VARCHAR2(100);
912     l_xml            CLOB;
913     csr              NUMBER;
914     ret              NUMBER;
915     l_inc_exc        VARCHAR2(1) DEFAULT 'X'; -- 'X' as some dummy value
916     l_payroll_type   VARCHAR2(40) DEFAULT NULL;
917     --
918 BEGIN
919     --
920     --hr_utility.trace_on(NULL,'NL_NWR');
921     hr_utility.trace('p_nwr_report_type :'||p_nwr_report_type);
922     hr_utility.trace('p_action_context_id :'||p_action_context_id);
923     hr_utility.trace('p_template_name :'||p_template_name);
924     hr_utility.trace('p_assignment_set_id :'||p_assignment_set_id);
925     hr_utility.trace('p_sort_order :'||p_sort_order);
926     --
927     l_cntr_sql      := 1;
928     g_action_ctx_id := p_action_context_id;
929     --
930     IF p_assignment_set_id IS NOT NULL THEN
931         OPEN csr_inc_exc(p_assignment_set_id);
932         FETCH csr_inc_exc INTO l_inc_exc;
933         CLOSE csr_inc_exc;
934     END IF;
935     --
936     OPEN  get_year(p_action_context_id);
937     FETCH get_year INTO g_year;
938     CLOSE get_year;
939     --
940     build_sql(sqlstr, l_cntr_sql, 'DECLARE  BEGIN ');
941     --g_xml_nwr (g_xml_nwr.count() + 1) := '<?xml version="1.0" encoding="UTF-8"?>';
942     g_xml_nwr (g_xml_nwr.count() + 1) := '<?xml version="1.0" encoding="' || get_IANA_charset ||'"?>';
943     -- START <Loonaangifte>
944     --# 0
945     l_payroll_type := TO_CHAR(pay_nl_wage_report_pkg.get_parameters(p_action_context_id,'Payroll_Type'));
946 
947     IF p_nwr_report_type = 'NLNWR_XML' THEN
948        -- l_payroll_type := TO_CHAR(pay_nl_wage_report_pkg.get_parameters(p_action_context_id,'Payroll_Type'));
949 
950         IF l_payroll_type = 'YEARLY' and g_year >= '2007' THEN  -- Enh# 6968464
951               g_xml_nwr (g_xml_nwr.count() + 1) := '<Jaarloonopgaaf xmlns="http://xml.belastingdienst.nl/schemas/Jaarloonopgaaf/'||g_year||'/01" version="1.2">'||EOL ;
952         ELSIF l_payroll_type = 'YEARLY' THEN
953               g_xml_nwr (g_xml_nwr.count() + 1) := '<Jaarloonopgaaf xmlns="http://xml.belastingdienst.nl/schemas/Loonaangifte/2006/01" version="4.0">'||EOL ;
954         ELSIF g_year = '2006' THEN
955               g_xml_nwr (g_xml_nwr.count() + 1) := '<Loonaangifte xmlns="http://xml.belastingdienst.nl/schemas/Loonaangifte/2006/01" version="4.0">'||EOL ;
956         ELSE
957             g_xml_nwr (g_xml_nwr.count() + 1) := '<Loonaangifte xmlns="http://xml.belastingdienst.nl/schemas/Loonaangifte/'||g_year||'/01" version="1.0">'||EOL ;
958             --g_xml_nwr (g_xml_nwr.count() + 1) := '<version>1.0</version>' ;
959         END IF;
960     ELSE
961         g_xml_nwr (g_xml_nwr.count() + 1) := '<Loonaangifte>'||EOL;
962     END IF;
963     --# 0
964     --
965     -- Starting the Main Loop .. All the Other Loops come under this Loop only.
966     --
967     g_report_type := p_nwr_report_type;
968     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 ');
969     --# 1
970     IF p_nwr_report_type = 'NLNWR_XML' THEN
971     --#
972         -- MESSAGE <Bericht>
973         build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''Bericht'', NULL);');
974             --
975                  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);');
976             --
977                  build_sql(sqlstr, l_cntr_sql,
978 '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''));');
979             --
980             FOR cntr in 4..7 LOOP
981                 IF flex_seg_enabled ('NL_WR_EMPLOYER_INFO', 'ACTION_INFORMATION'||cntr) THEN
982                  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 ||');');
983                 END IF;
984             END LOOP;
985             --
989     END IF;
986         build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''Bericht'', NULL);');
987         -- MESSAGE </Bericht>
988     --# 1
990     --#
991     -- Administrative Unit <AdministratieveEenheid>
992     build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''AdministratieveEenheid'', NULL);');
993     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);');
994     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);');
995     -- Added to accomodate the changes in the collective and complete reports
996     IF p_nwr_report_type IN ('NLNWR_CR','NLNWR_COMPLETE') THEN
997         -- MESSAGE <Bericht>
998              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);');
999              --
1000              build_sql(sqlstr, l_cntr_sql,
1001 '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''));');
1002             --
1003             FOR cntr in 4..7 LOOP
1004                 IF flex_seg_enabled ('NL_WR_EMPLOYER_INFO', 'ACTION_INFORMATION'||cntr) THEN
1005                  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 ||');');
1006                 END IF;
1007             END LOOP;
1008             --
1009         -- MESSAGE </Bericht>
1010     END IF;
1011     --
1012     --# 2
1013     IF p_nwr_report_type IN ('NLNWR_XML','NLNWR_CR','NLNWR_IER','NLNWR_COMPLETE') THEN
1014     --#
1015         IF l_payroll_type = 'YEARLY' THEN
1016             -- Period Report <TijdvakCorrectie>
1017             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''TijdvakCorrectie'', NULL);');
1018         ELSE
1019             -- Period Report <TijdvakAangifte>
1020             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''TijdvakAangifte'', NULL);');
1021         END IF;
1022         --
1023         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''));');
1024         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''));');
1025         --
1026         IF l_payroll_type <> 'YEARLY' THEN
1027             -- Complete Report <VolledigeAangifte>
1028             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''VolledigeAangifte'', NULL);');
1029         END IF;
1030         --# 2.1
1031         IF p_nwr_report_type IN ('NLNWR_XML','NLNWR_CR','NLNWR_COMPLETE') THEN
1032         --#
1033             -- Collective Report <CollectieveAangifte>
1034             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''CollectieveAangifte'', NULL);');
1035             --
1036             generate_collective_report( p_act_context_id       => p_action_context_id
1037                                        ,p_type                 => 'COMPLETE'
1038                                        ,p_start_date           => 'csr_message_info_rec.action_information10'
1039                                        ,p_end_date             => 'csr_message_info_rec.action_information11'
1040                                        ,p_in_not_in            => 'NOT IN'
1041                                        ,p_report_type          => p_nwr_report_type);
1042             --
1043             build_sql(sqlstr, l_cntr_sql,
1044 '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 ');
1045             -- Specification waiting money fund contribution <Wgf>
1046             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''Wgf'', NULL);');
1047                 --
1048                 FOR cntr in 7..8 LOOP
1049                     IF flex_seg_enabled ('NL_WR_SWMF_SECTOR_RISK_GROUP', 'ACTION_INFORMATION'||cntr) THEN
1050                      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 ||');');
1051                     END IF;
1052                 END LOOP;
1053                 --
1054                 build_sql(sqlstr, l_cntr_sql,
1055                 '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)));');
1056                 build_sql(sqlstr, l_cntr_sql,
1057                 '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)));');
1058             --
1059             -- Specification waiting money fund contribution </Wgf>
1060             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''Wgf'', NULL);');
1061             --
1062             build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
1063             --
1064             generate_collective_report( p_act_context_id       => p_action_context_id
1065                                        ,p_type                 => 'COMPLETE'
1069                                        ,p_report_type          => p_nwr_report_type);
1066                                        ,p_start_date           => 'csr_message_info_rec.action_information10'
1067                                        ,p_end_date             => 'csr_message_info_rec.action_information11'
1068                                        ,p_in_not_in            => 'IN'
1070             --
1071             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 ');
1072             --
1073             -- Correction balances prev. period <SaldoCorrectiesVoorgaandTijdvak>
1074             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''SaldoCorrectiesVoorgaandTijdvak'', NULL);');
1075             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''));');
1076             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''));');
1077             build_sql(sqlstr, l_cntr_sql,
1078             '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)));');
1079             --
1080             -- Correction balances prev. period </SaldoCorrectiesVoorgaandTijdvak>
1081             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''SaldoCorrectiesVoorgaandTijdvak'', NULL);');
1082             build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
1083             --
1084             -- General Total
1085             build_sql(sqlstr, l_cntr_sql,
1086 '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 ');
1087             --
1088             build_sql(sqlstr, l_cntr_sql,
1089             '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)));');
1090             --
1091             build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
1092             --
1093             -- Collective Report </CollectieveAangifte>
1094             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''CollectieveAangifte'', NULL);');
1095             --
1096         --# 2.1
1097         END IF;
1098         --#
1099         --# 2.2
1100         IF p_nwr_report_type IN ('NLNWR_XML','NLNWR_IER','NLNWR_COMPLETE') THEN
1101         --#
1102             build_sql(sqlstr, l_cntr_sql,
1103 'FOR csr_employment_info_rec IN pay_nl_nwr_xml_extract_pkg.csr_employment_info ('||
1104 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 ');
1105             --
1106             IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'I' THEN
1107             --
1108                 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 ');
1109                 build_sql(sqlstr, l_cntr_sql, 'IF csr_assignment_filter_rec.INCLUDE_OR_EXCLUDE = ''I'' THEN ');
1110             END IF;
1111             --
1112             IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'E' THEN
1113             --
1114                 build_sql(sqlstr, l_cntr_sql, 'DECLARE l_flag VARCHAR2(1) := ''Y'';  BEGIN ');
1115                 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 ');
1116                 build_sql(sqlstr, l_cntr_sql, 'IF csr_assignment_filter_rec.INCLUDE_OR_EXCLUDE = ''E'' THEN  l_flag := ''N''; END IF; END LOOP; ');
1117                 build_sql(sqlstr, l_cntr_sql, 'IF l_flag = ''Y'' THEN ');
1118             END IF;
1119             --
1120             -- Initial Employment Relation <InkomstenverhoudingInitieel>
1121             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''InkomstenverhoudingInitieel'', NULL);');
1122                 --
1123                 IF p_nwr_report_type <> 'NLNWR_XML' THEN
1124                     --
1125                     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);');
1126                     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);');
1127                     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''));');
1128                     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''));');
1129                     --
1130                 END IF;
1131                 --
1132                      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);');
1133                      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);');
1134                      build_sql(sqlstr, l_cntr_sql,
1138                 --
1135                      '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''));');
1136                      build_sql(sqlstr, l_cntr_sql,
1137                      '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''));');
1139                 FOR cntr in REVERSE 5..6 LOOP
1140                     IF flex_seg_enabled ('NL_WR_EMPLOYMENT_INFO', 'ACTION_INFORMATION'||cntr) THEN
1141                      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 ||');');
1142                     END IF;
1143                 END LOOP;
1144                 --
1145             --
1146             generate_person(p_action_context_id,'INITIAL',p_nwr_report_type);
1147             --
1148             generate_ip_srg_nr(p_action_context_id,'INITIAL',p_nwr_report_type);
1149             --
1150             -- Initial Employment Relation </InkomstenverhoudingInitieel>
1151             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''InkomstenverhoudingInitieel'', NULL);');
1152             --
1153             IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'I' THEN
1154                 build_sql(sqlstr, l_cntr_sql, 'END IF; END LOOP; ');
1155             END IF;
1156             --
1157             IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'E' THEN
1158                 build_sql(sqlstr, l_cntr_sql, ' END IF; END; ');
1159             END IF;
1160             --
1161             build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
1162             --
1163         --# 2.2
1164         END IF;
1165         --#
1166         IF l_payroll_type <> 'YEARLY' THEN
1167             -- Complete Report </VolledigeAangifte>
1168             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''VolledigeAangifte'', NULL);');
1169         END IF;
1170         IF l_payroll_type = 'YEARLY' THEN
1171             -- Period Report <TijdvakCorrectie>
1172             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''TijdvakCorrectie'', NULL);');
1173         ELSE
1174             -- Period Report <TijdvakAangifte>
1175             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''TijdvakAangifte'', NULL);');
1176         END IF;
1177         --
1178     --# 2
1179     END IF;
1180     --#
1181     --# 3
1182     IF p_nwr_report_type IN ('NLNWR_XML','NLNWR_CRC','NLNWR_IERC','NLNWR_WER','NLNWR_COER','NLNWR_COMPLETE') THEN
1183     --#
1184         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 ');
1185         --
1186         -- Correction Report <TijdvakCorrectie>
1187         build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''TijdvakCorrectie'', NULL);');
1188         --
1189         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''));');
1190         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''));');
1191         --
1192         IF p_nwr_report_type IN ('NLNWR_CRC') THEN
1193             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);');
1194             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);');
1195         END IF;
1196         --
1197         --# 3.1
1198         IF p_nwr_report_type IN ('NLNWR_XML','NLNWR_CRC','NLNWR_COMPLETE') THEN
1199         --#
1200             -- Collective Report <CollectieveAangifte>
1201             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''CollectieveAangifte'', NULL);');
1202             --
1203             generate_collective_report( p_act_context_id       => p_action_context_id
1204                                        ,p_type                 => 'CORRECTION'
1205                                        ,p_start_date           => 'csr_correction_info_rec.start_date'
1206                                        ,p_end_date             => 'csr_correction_info_rec.end_date'
1207                                        ,p_in_not_in            => 'NOT IN'
1208                                        ,p_report_type          => p_nwr_report_type);
1209             --
1210             build_sql(sqlstr, l_cntr_sql,
1211 '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 ');
1212             -- Specification waiting money fund contribution <Wgf>
1213             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''Wgf'', NULL);');
1214                 --
1215                 FOR cntr in 7..8 LOOP
1216                     IF flex_seg_enabled ('NL_WR_SWMF_SECTOR_RISK_GROUP', 'ACTION_INFORMATION'||cntr) THEN
1217                      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 ||');');
1218                     END IF;
1219                 END LOOP;
1223                 '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)));');
1220                 build_sql(sqlstr, l_cntr_sql,
1221                 '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)));');
1222                 build_sql(sqlstr, l_cntr_sql,
1224                 --
1225             -- Specification waiting money fund contribution </Wgf>
1226             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''Wgf'', NULL);');
1227             --
1228             build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
1229             --
1230             --
1231             generate_collective_report( p_act_context_id       => p_action_context_id
1232                                        ,p_type                 => 'CORRECTION'
1233                                        ,p_start_date           => 'csr_correction_info_rec.start_date'
1234                                        ,p_end_date             => 'csr_correction_info_rec.end_date'
1235                                        ,p_in_not_in            => 'IN'
1236                                        ,p_report_type          => p_nwr_report_type);
1237             --
1238             -- Collective Report </CollectieveAangifte>
1239             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''CollectieveAangifte'', NULL);');
1240             --
1241         --# 3.1
1242         END IF;
1243         --#
1244         --# 3.2
1245         IF p_nwr_report_type IN ('NLNWR_XML','NLNWR_IERC','NLNWR_COMPLETE') THEN
1246         --#
1247             --
1248             build_sql(sqlstr, l_cntr_sql,
1249 'FOR csr_employment_info_rec IN pay_nl_nwr_xml_extract_pkg.csr_employment_info ('||
1250 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 ');
1251             --
1252             IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'I' THEN
1253             --
1254                 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 ');
1255                 build_sql(sqlstr, l_cntr_sql, 'IF csr_assignment_filter_rec.INCLUDE_OR_EXCLUDE = ''I'' THEN ');
1256             END IF;
1257             --
1258             IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'E' THEN
1259             --
1260                 build_sql(sqlstr, l_cntr_sql, 'DECLARE l_flag VARCHAR2(1) := ''Y'';  BEGIN ');
1261                 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 ');
1262                 build_sql(sqlstr, l_cntr_sql, 'IF csr_assignment_filter_rec.INCLUDE_OR_EXCLUDE = ''E'' THEN  l_flag := ''N''; END IF; END LOOP; ');
1263                 build_sql(sqlstr, l_cntr_sql, 'IF l_flag = ''Y'' THEN ');
1264             END IF;
1265             --
1266             -- Initial Employment Relation <InkomstenverhoudingInitieel>
1267             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''InkomstenverhoudingInitieel'', NULL);');
1268                 --
1269                 IF p_nwr_report_type <> 'NLNWR_XML' THEN
1270                     --
1271                     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);');
1272                     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);');
1273                     --
1274                     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''));');
1275                     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''));');
1276                     --
1277                 END IF;
1278                 --
1279                      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);');
1280                      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);');
1281                      build_sql(sqlstr, l_cntr_sql,
1282                      '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''));');
1283                      build_sql(sqlstr, l_cntr_sql,
1284                      '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''));');
1285                 --
1286                 FOR cntr in REVERSE 5..6 LOOP
1287                     IF flex_seg_enabled ('NL_WR_EMPLOYMENT_INFO', 'ACTION_INFORMATION'||cntr) THEN
1288                      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 ||');');
1289                     END IF;
1293             generate_person(p_action_context_id,'CORRECTION',p_nwr_report_type);
1290                 END LOOP;
1291                 --
1292             --
1294             --
1295             generate_ip_srg_nr(p_action_context_id,'CORRECTION',p_nwr_report_type);
1296             --
1297             -- Initial Employment Relation </InkomstenverhoudingInitieel>
1298             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''InkomstenverhoudingInitieel'', NULL);');
1299             --
1300             IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'I' THEN
1301                 build_sql(sqlstr, l_cntr_sql, 'END IF; END LOOP; ');
1302             END IF;
1303             --
1304             IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'E' THEN
1305                 build_sql(sqlstr, l_cntr_sql, ' END IF; END; ');
1306             END IF;
1307             --
1308             build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
1309             --
1310         --# 3.2
1311         END IF;
1312         --#
1313         --# 3.3
1314         IF p_nwr_report_type IN ('NLNWR_XML','NLNWR_WER','NLNWR_COMPLETE') THEN
1315         --#
1316             --
1317             build_sql(sqlstr, l_cntr_sql,
1318 'FOR csr_employment_info_rec IN pay_nl_nwr_xml_extract_pkg.csr_employment_info ('||
1319 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 ');
1320             --
1321             IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'I' THEN
1322             --
1323                 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 ');
1324                 build_sql(sqlstr, l_cntr_sql, 'IF csr_assignment_filter_rec.INCLUDE_OR_EXCLUDE = ''I'' THEN ');
1325             END IF;
1326             --
1327             IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'E' THEN
1328             --
1329                 build_sql(sqlstr, l_cntr_sql, 'DECLARE l_flag VARCHAR2(1) := ''Y'';  BEGIN ');
1330                 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 ');
1331                 build_sql(sqlstr, l_cntr_sql, 'IF csr_assignment_filter_rec.INCLUDE_OR_EXCLUDE = ''E'' THEN  l_flag := ''N''; END IF; END LOOP; ');
1332                 build_sql(sqlstr, l_cntr_sql, 'IF l_flag = ''Y'' THEN ');
1333             END IF;
1334             --
1335             -- Withdrawal Employment Relation <InkomstenverhoudingIntrekking>
1336             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''InkomstenverhoudingIntrekking'', NULL);');
1337                 --
1338                 IF p_nwr_report_type <> 'NLNWR_XML' THEN
1339                     --
1340                     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);');
1341                     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);');
1342                     --
1343                     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''));');
1344                     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''));');
1345                     --
1346                     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);');
1347                     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);');
1348                     build_sql(sqlstr, l_cntr_sql,
1349                     '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''));');
1350                     build_sql(sqlstr, l_cntr_sql,
1351                     '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''));');
1352                     --
1353                     FOR cntr in 5..6 LOOP
1354                         IF flex_seg_enabled ('NL_WR_EMPLOYMENT_INFO', 'ACTION_INFORMATION'||cntr) THEN
1355                          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 ||');');
1356                         END IF;
1357                     END LOOP;
1358                     --
1359                     generate_person(p_action_context_id,'WITHDRAWAL',p_nwr_report_type);
1360                     --
1361                 END IF;
1362                 --
1363                 IF p_nwr_report_type = 'NLNWR_XML' THEN
1364                     --
1365                     IF g_year = '2006' THEN
1369                     ELSE
1366                         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);');
1367                         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);');
1368                         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);');
1370                         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);');
1371                         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);');
1372                         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);');
1373                         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);');
1374                     END IF;
1375                     --
1376                 END IF;
1377             --
1378             -- Withdrawal Employment Relation </InkomstenverhoudingIntrekking>
1379             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''InkomstenverhoudingIntrekking'', NULL);');
1380             --
1381             IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'I' THEN
1382                 build_sql(sqlstr, l_cntr_sql, 'END IF; END LOOP; ');
1383             END IF;
1384             --
1385             IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'E' THEN
1386                 build_sql(sqlstr, l_cntr_sql, ' END IF; END; ');
1387             END IF;
1388             --
1389             build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
1390             --
1391         --# 3.3
1392         END IF;
1393         --#
1394         --# 3.4
1395         IF p_nwr_report_type IN ('NLNWR_XML','NLNWR_COER','NLNWR_COMPLETE') THEN
1396         --#
1397             build_sql(sqlstr, l_cntr_sql,
1398 'FOR csr_employment_info_rec IN pay_nl_nwr_xml_extract_pkg.csr_employment_info ('||
1399 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 ');
1400             --
1401             IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'I' THEN
1402             --
1403                 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 ');
1404                 build_sql(sqlstr, l_cntr_sql, 'IF csr_assignment_filter_rec.INCLUDE_OR_EXCLUDE = ''I'' THEN ');
1405             END IF;
1406             --
1407             IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'E' THEN
1408             --
1409                 build_sql(sqlstr, l_cntr_sql, 'DECLARE l_flag VARCHAR2(1) := ''Y'';  BEGIN ');
1410                 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 ');
1411                 build_sql(sqlstr, l_cntr_sql, 'IF csr_assignment_filter_rec.INCLUDE_OR_EXCLUDE = ''E'' THEN  l_flag := ''N''; END IF; END LOOP; ');
1412                 build_sql(sqlstr, l_cntr_sql, 'IF l_flag = ''Y'' THEN ');
1413             END IF;
1414             --
1415             -- Correction Employment relation <InkomstenverhoudingCorrectie>
1416             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CS'', NULL, ''InkomstenverhoudingCorrectie'', NULL);');
1417                 --
1418                 IF p_nwr_report_type <> 'NLNWR_XML' THEN
1419                     --
1420                     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);');
1421                     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);');
1422                     --
1423                     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''));');
1424                     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''));');
1425                     --
1426                 END IF;
1427                 --
1428                      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);');
1429                      build_sql(sqlstr, l_cntr_sql,
1433                      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);');
1430                      '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''));');
1431                      build_sql(sqlstr, l_cntr_sql,
1432                      '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''));');
1434                      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);');
1435                      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);');
1436             --
1437             IF p_nwr_report_type <> 'NLNWR_XML' THEN
1438                 generate_person(p_action_context_id,'CORRECT',p_nwr_report_type);
1439             END IF;
1440             --
1441             generate_ip_srg_nr(p_action_context_id,'CORRECT',p_nwr_report_type);
1442             --
1443             -- Correction Employment relation </InkomstenverhoudingCorrectie>
1444             build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''InkomstenverhoudingCorrectie'', NULL);');
1445             --
1446             IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'I' THEN
1447                 build_sql(sqlstr, l_cntr_sql, 'END IF; END LOOP; ');
1448             END IF;
1449             --
1450             IF p_nwr_report_type <> 'NLNWR_XML' AND p_assignment_set_id IS NOT NULL AND l_inc_exc = 'E' THEN
1451                 build_sql(sqlstr, l_cntr_sql, ' END IF; END; ');
1452             END IF;
1453             --
1454             build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
1455             --
1456         --# 3.4
1457         END IF;
1458         --#
1459         -- Correction Report </TijdvakCorrectie>
1460         build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''TijdvakCorrectie'', NULL);');
1461         --
1462         build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
1463         --
1464     --# 3
1465     END IF;
1466     --#
1467     -- Administrative Unit </AdministratieveEenheid>
1468     build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''AdministratieveEenheid'', NULL);');
1469     --
1470     -- End of the Main Loop
1471     --
1472     build_sql(sqlstr, l_cntr_sql, 'END LOOP;');
1473     -- END </Loonaangifte>
1474     IF l_payroll_type = 'YEARLY' AND p_nwr_report_type = 'NLNWR_XML' THEN
1475       build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''Jaarloonopgaaf'', NULL);');
1476     ELSE
1477       build_sql(sqlstr, l_cntr_sql, 'pay_nl_nwr_xml_extract_pkg.load_xml(''CE'', NULL, ''Loonaangifte'', NULL);');
1478     END IF;
1479     build_sql(sqlstr, l_cntr_sql, 'END;');
1480     --
1481     csr := dbms_sql.open_cursor;
1482     dbms_sql.parse (csr,
1483                     sqlstr,
1484                     sqlstr.first(),
1485                     sqlstr.last(),
1486                     false,
1487                     dbms_sql.v7);
1488     ret := dbms_sql.execute(csr);
1489     dbms_sql.close_cursor(csr);
1490     --
1491     WritetoCLOB(p_xfdf_string => l_xml);
1492     --
1493     p_xml := l_xml;
1494     --
1495     dbms_lob.freeTemporary(l_xml);
1496     --
1497     g_xml_nwr.delete();
1498     --
1499 END generate;
1500 --
1501 END pay_nl_nwr_xml_extract_pkg;