[Home] [Help]
PACKAGE BODY: APPS.BEN_EXT_XML_WRITE
Source
4 /*
1 package body BEN_EXT_XML_WRITE as
2 /* $Header: benxxmlw.pkb 120.6 2008/03/14 04:58:56 vkodedal noship $ */
3 --------------------------------------------------------------------------------
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 Name:
11 Extract XML Write Process.
12 Purpose:
13 This process reads records from the ben_ext_rslt_dtl table and writes them
14 to a xml output file.
15 History:
16 Date Who Version What?
17 ---- --- ------- -----
18 17 Apr 2003 tjesumic 115.0 Created Write Xml File
19 17 Apr 2003 tjesumic 115.0 NOCOPY added
20 22 Apr 2003 tjesumic 115.2 maxline is fixed
21 22 Apr 2003 tjesumic 115.2 character set is changed to utf-8
22 13 May 2003 tjesumic 115.4 XML user defined tag added
23 14 May 2003 tjesumic 115.4 new attribute ext_rec_person id added ,
24 the record is relatively refered in xsl
25 14 may 2003 tjesumic 115.6 xsl and xsd files created on the user defined name
26 16 may 2003 tjesumic 115.7 nonamespace used for schema
27 28 may 2003 tjesumic 115.8 for default tags lookup value are used
28 29 may 2003 tjesumic 115.9 check_sql error is fixed by spiliting
29 29 may 2003 tjesumic 115.9 check_sql error is fixed by spiliting
30 29 may 2003 tjesumic 115.11 complex type 'All' changed to 'choice'
31 08 Aug 2003 tjesumic 115.12 Element Level Hide Flag is not displayed
32 25 Aug 2004 tjesumic 115.13 xdo integeration
33 08 Nov 2004 tjesumic 115.14 ext_xml_encode added
34 15 dec 2004 tjesumic 115.15 ext_rcd_in_file_id adeded to validation
35 15 Jan 2005 tjesumic 115.16 Hard coding charactset changed by
36 getting the value from profile ICX_CLIENT_IANA_ENCODING
37 XML grouped by the record low level for
38 PDF format
39 27-Jan-2005 tjesumic 115.17 when there is header and trailer pdf format fails
40 01-Feb-2005 tjesumic 115.18 300 elements allowed in a record
41 04-Mar-2005 tjesumic 115.19 XDO is called for all the type except 'X'ml
42 22-Mar-2005 tjesumic 115.20 sub header and sub trailer added for xml creation
43 22-Mar-2005 tjesumic 115.22 version 115.20 brought forward
44 08-Jun-2005 tjesumic 115.23 pennserver extract enhancment
45 30-Nov-2005 tjesumic 115.24 fnd_concurrent_request table updted with output file
46 06-Dec-2005 tjesumic 115.25 cm_display
47 09-Dec-2005 tjesumic 115.26 new parameter p_source added
48 14-MAr-2007 tjesumic 115.27 xml tag added for xdo ouputs
49 26-Feb-2008 vkodedal 115.28 c_per_info modified #6838751
50 */
51 -----------------------------------------------------------------------------------
52 --
53 g_package varchar2(30) := ' ben_ext_xml_write.';
54 --
55 g_file_tag ben_ext_dfn.xml_tag_name%Type ;
56 g_dfn_tag ben_ext_dfn.xml_tag_name%Type ;
57 g_rcd_tag_tbl BEN_EXT_XML_WRITE.g_table ;
58 g_elmt_tag_tbl BEN_EXT_XML_WRITE.g_table ;
59 g_low_lvl_tbl BEN_EXT_XML_WRITE.g_table ;
60 g_iana_char_set varchar2(500) ;
61 g_prev_grop_val ben_ext_rslt_dtl.group_val_01%type ;
62 --
63 -----------------------------------------------------------------------------
64
65
66
67 function determine_sub_low_lvl (p_prev_lvl varchar2 ,
68 p_lvl varchar2 ,
69 p_group varchar2 default null ) return varchar2 is
70
71 l_ret_val varchar2(10) := 'N' ;
72 l_proc varchar2(72) := g_package||'determine_sub_low_lvl';
73 begin
74
75 hr_Utility.set_location('Entering'||l_proc, 5);
76 if p_prev_lvl = 'P' and p_lvl in ('CO','G','E','Y','R','F','ED','B','D','A','PR','T','TS','WG','WR') then --- person
77 l_ret_val := 'Y' ;
78 elsif p_prev_lvl in ( 'E') and p_lvl in ('B','D','A','PR') then -- Enrollment
79 l_ret_val := 'Y' ;
80 elsif p_prev_lvl in ( 'G') and p_lvl in ('ED') then -- eligible
81 l_ret_val := 'Y' ;
82 elsif p_prev_lvl in ( 'T') and p_lvl in ('TS') then -- timecard
83 l_ret_val := 'Y' ;
84 elsif p_prev_lvl = 'OR' and p_lvl in ( 'P','CO','G','E','Y','R','F','ED','B','D','A','PR','T','TS','WG','WR', 'PO')
85 and nvl(p_group, '-1') <> ' ' and nvl(p_group,'-1') = nvl(g_prev_grop_val,'-1')
86 then --- organization subheader
87 l_ret_val := 'Y' ;
91 l_ret_val := 'Y' ;
88 elsif p_prev_lvl in('PO','JB','LO','PY') and p_lvl in ('P','CO','G','E','Y','R','F','ED','B','D','A','PR','T','TS','WG','WR')
89 and nvl(p_group , '-1') <> ' ' and nvl(p_group,'-1') = nvl(g_prev_grop_val,'-1')
90 then --- position/job/location/payroll subheader
92 end if ;
93 -- for first level
94 --if p_prev_lvl is null and p_lvl = 'P' then
95 -- l_ret_val := 'Y' ;
96 --elsif (p_prev_lvl is null and p_lvl = 'N' ) then
97 -- l_ret_val := 'Y' ;
98 --elsif p_prev_lvl is null and p_lvl is not null then
99 if p_prev_lvl is null and p_lvl is not null then
100 l_ret_val := 'Y' ;
101 end if ;
102
103 g_prev_grop_val := p_group ;
104
105 hr_utility.set_location('p_prev_lvl '|| p_prev_lvl , 15);
106 hr_utility.set_location('p_lvl '|| p_lvl , 15);
107 hr_utility.set_location('Exiting '|| l_ret_val ||l_proc, 15);
108 return l_ret_val ;
109
110 end determine_sub_low_lvl;
111
112
113 function get_low_lvl_name ( p_lvl varchar2 ) return varchar2 is
114
115 l_ret_val varchar2(250) ;
116 l_proc varchar2(72) := g_package||' get_low_lvl_name';
117
118 cursor c is
119 select meaning
120 from hr_lookups
121 where lookup_type = 'BEN_EXT_LVL'
122 and lookup_code = p_lvl ;
123
124 begin
125
126 hr_Utility.set_location('Entering'||l_proc, 5);
127 open c ;
128 fetch c into l_ret_val ;
129 close c ;
130 if l_ret_val is not null then
131 l_ret_val := translate(l_ret_val ,' !@#$%^&*()-+={}|[]\";:/.,<>?~`','_');
132 end if ;
133
134 hr_utility.set_location('Exiting '|| l_ret_val ||l_proc, 15);
135 return (l_ret_val ) ;
136
137 end get_low_lvl_name;
138
139
140 procedure add_delete_sub_level(p_action varchar2 ,
141 p_low_lvl_cd varchar2) is
142 l_proc varchar2(72) := g_package||' add_delete_sub_level';
143
144 l_last number := g_low_lvl_tbl.last ;
145 begin
146 hr_Utility.set_location('Entering'||l_proc, 5);
147 hr_Utility.set_location(p_action ||' / '||p_low_lvl_cd , 5);
148
149
150 hr_Utility.set_location('p_action'||p_action, 5);
151 hr_Utility.set_location('l_last '||l_last, 5);
152
153 if p_action = 'ADD' then
154 g_low_lvl_tbl(( nvl(l_last,0) + 1)) := p_low_lvl_cd ;
155 end if ;
156
157 if p_action = 'DELETE' then
158
159 for j in REVERSE 1 .. l_last Loop
160 if g_low_lvl_tbl(j) = p_low_lvl_cd then
161 hr_Utility.set_location('delete '||j, 5);
162 g_low_lvl_tbl.delete(j) ;
163 exit ;
164 end if ;
165 end loop ;
166
167 end if ;
168 hr_utility.set_location('Exiting ' ||l_proc, 15);
169
170 end add_delete_sub_level ;
171
172
173
174
175 Function ext_xml_encode(p_text varchar2 ) return varchar2 is
176
177 l_text varchar2(4000) ;
178 begin
179 l_text:= (replace(
180 replace(
181 replace(
182 replace(p_text, '&' , '&' || 'amp;' ),
183 '"', '&' || 'quot;'),
184 '<', '&' ||'lt;' ),
185 '>', '&' ||'gt;' ));
186 Return l_text ;
187
188 end ext_xml_encode ;
189
190
191
192 Procedure Load_tags
193 (p_tag_table in out nocopy BEN_EXT_XML_WRITE.g_table ,
194 p_tag in varchar2
195 ) is
196
197 l_proc varchar2(72) := g_package||'Load_tags';
198 l_found varchar2(1) := 'N' ;
199 l_last number := nvl(p_tag_table.last,0) ;
200 begin
201
202 --hr_Utility.set_location('Entering'||l_proc, 5);
203 if p_tag_table.first is not null then
204 for i in 1 .. l_last loop
205 if p_tag_table(i) = p_tag then
206 l_found := 'Y' ;
207 exit ;
208 end if ;
209 end loop ;
210 end if ;
211 if l_found = 'N' then
212 p_tag_table(( nvl(l_last,0) + 1)) := p_tag ;
213 end if ;
214
215
216 --hr_utility.set_location('Exiting '||p_tag_table.last ||l_proc, 15);
217 End Load_tags;
218
219
220 Procedure write_style_sheet
221 (p_drctry_name in varchar2,
222 p_file_name in varchar2,
223 p_ext_rslt_id in number,
224 p_ext_dfn_tag in varchar2,
225 p_ext_file_tag in varchar2) is
226
227 file_handle utl_file.file_type;
228 l_var varchar2(4000) ;
229 l_proc varchar2(72) := g_package||'write_style_sheet';
230 l_max_ext_line_size Number := 32767 ;
231 l_output_name ben_ext_rslt.output_name%type ;
232
233 Begin
234 --
235 hr_Utility.set_location('Entering'||l_proc, 5);
236 --l_output_name := 'benxxssh.xsl' ;
237 l_output_name := nvl(p_file_name, 'benxxssh') || '.xsl' ;
238 --
239
240 hr_Utility.set_location('out put '||l_output_name|| ' / '|| p_drctry_name, 5);
241 file_handle := utl_file.fopen (p_drctry_name,l_output_name,'w' , l_max_ext_line_size );
242 hr_Utility.set_location(' after header ', 5);
243 --- Write the xml header
244 utl_file.put_line(file_handle, '<?xml version="1.0" encoding="'||g_iana_char_set||'"?>');
245 utl_file.put_line(file_handle, '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">');
246 utl_file.put_line(file_handle, '<xsl:template match="/">');
247 utl_file.put_line(file_handle, '<html> <body>');
248
249 hr_Utility.set_location(' after header ', 5);
250 l_var := ' <xsl:for-each select="/'||'*'||'/'||p_ext_file_tag||'">
251 <h2>
255 Date : <xsl:value-of select="@effective_date"/> Start Date : <xsl:value-of select="@start_time"/>
252 <xsl:value-of select="@ext_dfn_name"/>
253 </h2>
254 <h5>
256 End Date : <xsl:value-of select="@end_time"/>
257 </h5>
258 <xsl:for-each select="' || 'oabext_person_record_set' ||'">
259 <table border="1"> ' ;
260 utl_file.put_line(file_handle, l_var);
261 -- this is record level , there can be multiple rec with diff tag so relative path is used
262 l_var := '
263 <xsl:for-each select="' || 'child::*' ||'">
264 <xsl:sort select="@ext_rec_person"/>
265 <!-- This diplay the record header for each recird -->
266 <tr> <th>
267 <xsl:value-of select="@ext_rec_name" />
268 </th> </tr>' ;
269 utl_file.put_line(file_handle, l_var);
270 l_var := ' <xsl:for-each select="oabext_record_set">
271 <xsl:if test="@record_number=1"> ';
272 utl_file.put_line(file_handle, l_var);
273
274 for j in g_elmt_tag_tbl.first .. g_elmt_tag_tbl.last loop
275 l_var := '
276 <xsl:for-each select="' || g_elmt_tag_tbl(j) ||'">
277 <th> <xsl:value-of select="@ext_elmt_name"/> </th>
278 </xsl:for-each> ';
279 utl_file.put_line(file_handle, l_var);
280 end loop ;
281 l_var := '
282 </xsl:if>
283 </xsl:for-each>
284 <!-- Listing Element Value -->
285 <xsl:for-each select="oabext_record_set">
286 <tr>';
287
288 utl_file.put_line(file_handle, l_var);
289 for j in g_elmt_tag_tbl.first .. g_elmt_tag_tbl.last loop
290 l_var := '
291 <xsl:for-each select="'||g_elmt_tag_tbl(j)||'">
292 <td> <xsl:value-of select="."/> </td>
293 </xsl:for-each> ' ;
294 utl_file.put_line(file_handle, l_var);
295 end loop ;
296 l_var := '
297 </tr>
298 </xsl:for-each>
299 </xsl:for-each> ' ;
300 utl_file.put_line(file_handle, l_var);
301 l_var := '</table>
302 </xsl:for-each>
303 </xsl:for-each> ' ;
304
305 hr_Utility.set_location('out put '||l_var, 5);
306 utl_file.put_line(file_handle,l_var );
307 ---
308 utl_file.put_line(file_handle, '</body> </html>');
309 utl_file.put_line(file_handle, '</xsl:template>');
310 utl_file.put_line(file_handle, '</xsl:stylesheet>');
311 ---
312 utl_file.fclose(file_handle);
313
314 -- write to logfile the record count
315 fnd_message.set_name('BEN','BEN_91878_EXT_TTL_RCRDS');
316 fnd_file.put_line(fnd_file.log,fnd_message.get || ' ' || p_drctry_name||'/'||l_output_name ) ;
317 hr_utility.set_location('Exiting'||l_proc, 15);
318
319 END write_style_sheet;
320
321
322
323 Procedure write_schema
324 (p_drctry_name in varchar2,
325 p_file_name in varchar2,
326 p_ext_rslt_id in number,
327 p_ext_dfn_tag in varchar2,
328 p_ext_file_tag in varchar2) is
329
330 file_handle utl_file.file_type;
331 l_var varchar2(4000) ;
332 l_proc varchar2(72) := g_package||'write_schema';
333 l_max_ext_line_size Number := 32767 ;
334 l_output_name ben_ext_rslt.output_name%type ;
335
336 Begin
337 --
338 hr_Utility.set_location('Entering'||l_proc, 5);
339
340 l_output_name := nvl(p_file_name,'benxxsch' ) || '.xsd' ;
341 --l_output_name := 'benxxsch.xsd' ;
342
343 --
344 file_handle := utl_file.fopen (p_drctry_name,l_output_name,'w' , l_max_ext_line_size );
345 --- Write the xml header
346 utl_file.put_line(file_handle, '<?xml version="1.0" encoding="'||g_iana_char_set||'"?>');
347 utl_file.put_line(file_handle, '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">');
348 -- utl_file.put_line(file_handle, ' targetNamespace="http://www.oracle.com/xml/OAB/ext"');
349 -- utl_file.put_line(file_handle, ' xmlns="http://www.oracle.com/xml/OAB/ext">');
350 ----- Element Type
351 for i in g_elmt_tag_tbl.first .. g_elmt_tag_tbl.last loop
352 l_var := '
353 <xs:element name="'||g_elmt_tag_tbl(i)||'">
354 <xs:complexType>
355 <xs:simpleContent>
356 <xs:extension base="xs:string">
357 <xs:attribute name="ext_elmt_name" type="xs:string" use="required"/>
358 <xs:attribute name="ext_elmt_seq" use="required"/>
359 <xs:attribute name="ext_elmt_type" type="xs:string" use="required"/>
360 <xs:attribute name="ext_elmt_length_" type="xs:string" />
361 <xs:attribute name="ext_elmt_format" type="xs:string" />
362 <xs:attribute name="ext_elmt_just" type="xs:string"/>
363 </xs:extension>
364 </xs:simpleContent>
365 </xs:complexType>
366 </xs:element> ' ;
367 utl_file.put_line(file_handle,l_var );
368 end loop ;
369
370 ---- Creating for the record set
371 l_var :='
372 <xs:element name="oabext_record_set">
373 <xs:complexType>
374 <xs:choice maxOccurs="unbounded"> ' ;
375 utl_file.put_line(file_handle,l_var );
376 for i in g_elmt_tag_tbl.first .. g_elmt_tag_tbl.last loop
380 l_var := '
377 l_var := ' <xs:element ref="'||g_elmt_tag_tbl(i)||'" minOccurs="0"/>' ;
378 utl_file.put_line(file_handle,l_var );
379 end loop ;
381 </xs:choice>
382 <xs:attribute name="record_number" type="xs:string" use="required"/>
383 </xs:complexType>
384 </xs:element>' ;
385 utl_file.put_line(file_handle,l_var );
386
387
388 --- Creating for the records
389 for i in g_rcd_tag_tbl.first .. g_rcd_tag_tbl.last loop
390 l_var := '
391 <xs:element name="'||g_rcd_tag_tbl(i)||'">
392 <xs:complexType>
393 <xs:sequence>
394 <xs:element ref="oabext_record_set" maxOccurs="unbounded"/>
395 </xs:sequence>
396 <xs:attribute name="ext_rec_name" type="xs:string" use="required"/>
397 <xs:attribute name="ext_rec_seq" type="xs:byte" use="required"/>
398 <xs:attribute name="ext_rec_type" type="xs:string" use="required"/>
399 <xs:attribute name="ext_rec_person" type="xs:string"/>
400 </xs:complexType>
401 </xs:element> ' ;
402 utl_file.put_line(file_handle,l_var );
403 end loop ;
404
405
406 --- File type
407 l_var := '
408 <xs:element name="'||'oabext_person_record_set'||'">
409 <xs:complexType>
410 <xs:choice maxOccurs="unbounded"> ';
411 utl_file.put_line(file_handle,l_var );
412 for i in g_rcd_tag_tbl.first .. g_rcd_tag_tbl.last loop
413 l_var := ' <xs:element ref="'||g_rcd_tag_tbl(i)||'"/> ' ;
414 utl_file.put_line(file_handle,l_var );
415 end loop;
416 l_var := '
417 </xs:choice>
418 <xs:attribute name="person_name" type="xs:string"/>
419 <xs:attribute name="employee_number" type="xs:string"/>
420 </xs:complexType>
421 </xs:element> ' ;
422
423 utl_file.put_line(file_handle, l_var);
424
425
426
427
428
429
430 --- File type
431 l_var := '
432 <xs:element name="'||p_ext_file_tag||'">
433 <xs:complexType>
434 <xs:choice maxOccurs="unbounded"> ';
435 utl_file.put_line(file_handle,l_var );
436 l_var := ' <xs:element ref="'||'oabext_person_record_set'||'"/> ' ;
437 utl_file.put_line(file_handle,l_var );
438 l_var := '
439 </xs:choice>
440 <xs:attribute name="ext_dfn_name" type="xs:string" use="required"/>
441 <xs:attribute name="ext_type" type="xs:string" use="required"/>
442 <xs:attribute name="effective_date" type="xs:string" use="required"/>
443 <xs:attribute name="start_time" type="xs:string" use="required"/>
444 <xs:attribute name="end_time" type="xs:string" use="required"/>
445 </xs:complexType>
446 </xs:element> ' ;
447
448 utl_file.put_line(file_handle, l_var);
449 l_var := '
450 <xs:element name="'||p_ext_dfn_tag||'">
451 <xs:complexType>
452 <xs:sequence>
453 <xs:element ref="'||p_ext_file_tag||'"/>
454 </xs:sequence>
455 </xs:complexType>
456 </xs:element>
457 </xs:schema>' ;
458
459 --- Closing Root
460 utl_file.put_line(file_handle,l_var );
461 --- Closing the file
462 utl_file.fclose(file_handle);
463
464 -- write to logfile the record count
465 fnd_message.set_name('BEN','BEN_91878_EXT_TTL_RCRDS');
466 fnd_file.put_line(fnd_file.log,fnd_message.get || ' ' || p_drctry_name||'/'||l_output_name ) ;
467 hr_utility.set_location('Exiting'||l_proc, 15);
468
469
470
471
472 END write_schema;
473
474
475
476
477
478 Procedure write_xdo_pdf
479 (p_drctry_name in varchar2,
480 p_pdf_output_name in varchar2,
481 p_input_name in varchar2,
482 p_template_id in number,
483 p_output_type in varchar2
484 ) is
485
486 l_proc varchar2(72) := g_package||'write_xdo_pdf';
487
488
489 l_pdf_output_name ben_ext_rslt.output_name%type ;
490 l_input_name ben_ext_rslt.output_name%type ;
491 l_extn_name varchar2(50) ;
492 l_template_cd xdo_templates_b.template_code%type ;
493 l_application_id number ;
494 l_APPLICATION_SHORT_NAME xdo_templates_b.APPLICATION_SHORT_NAME%type ;
495 l_request_id number ;
496
497
498 cursor c_xdo (p_template_id number) is
499 select template_code ,
500 application_id,
501 APPLICATION_SHORT_NAME
502 from xdo_templates_b xdo
503 where template_id = p_template_id ;
504
505 Begin
506 --
507 hr_Utility.set_location('Entering'||l_proc, 5);
508
509
510
511 open c_xdo(p_template_id) ;
512 fetch c_xdo into l_template_cd,
513 l_application_id,
514 l_APPLICATION_SHORT_NAME ;
515 close c_xdo ;
516
517 if l_template_cd is not null then
518 if substr(p_drctry_name, -1) = '/' then
519 l_input_name := p_drctry_name || p_input_name ;
520 l_pdf_output_name := p_drctry_name || p_pdf_output_name ;
521 else
522 --- check it is window kind of path or unix
523 if instr(p_drctry_name , '\') > 0 then
524 l_input_name := p_drctry_name ||'\'||p_input_name ;
525 l_pdf_output_name := p_drctry_name ||'\'||p_pdf_output_name ;
529 end if ;
526 else
527 l_input_name := p_drctry_name ||'/'||p_input_name ;
528 l_pdf_output_name := p_drctry_name ||'/'||p_pdf_output_name ;
530 end if ;
531
532
533 -- call concurrent manager to execute the pdf
534 l_request_id := fnd_request.submit_request
535 (application => 'BEN'
536 ,program => 'BENXDXML'
537 ,description => NULL
538 ,sub_request => FALSE
539 ,argument1 => l_application_id
540 ,argument2 => l_template_cd
541 ,argument3 => l_input_name
542 ,argument4 => l_pdf_output_name
543 ,argument5 => p_output_type
544 );
545
546 end if ;
547
548 hr_utility.set_location('Exiting'||l_proc, 15);
549
550 END write_xdo_pdf;
551
552
553
554
555
556
557
558 procedure load_arrays
559 (p_ext_rcd_id in number,
560 p_val_01 in varchar2,
561 p_val_02 in varchar2,
562 p_val_03 in varchar2,
563 p_val_04 in varchar2,
564 p_val_05 in varchar2,
565 p_val_06 in varchar2,
566 p_val_07 in varchar2,
567 p_val_08 in varchar2,
568 p_val_09 in varchar2,
569 p_val_10 in varchar2,
570 p_val_11 in varchar2,
571 p_val_12 in varchar2,
572 p_val_13 in varchar2,
573 p_val_14 in varchar2,
574 p_val_15 in varchar2,
575 p_val_16 in varchar2,
576 p_val_17 in varchar2,
577 p_val_18 in varchar2,
578 p_val_19 in varchar2,
579 p_val_20 in varchar2,
580 p_val_21 in varchar2,
581 p_val_22 in varchar2,
582 p_val_23 in varchar2,
583 p_val_24 in varchar2,
584 p_val_25 in varchar2,
585 p_val_26 in varchar2,
586 p_val_27 in varchar2,
587 p_val_28 in varchar2,
588 p_val_29 in varchar2,
589 p_val_30 in varchar2,
590 p_val_31 in varchar2,
591 p_val_32 in varchar2,
592 p_val_33 in varchar2,
593 p_val_34 in varchar2,
594 p_val_35 in varchar2,
595 p_val_36 in varchar2,
596 p_val_37 in varchar2,
597 p_val_38 in varchar2,
598 p_val_39 in varchar2,
599 p_val_40 in varchar2,
600 p_val_41 in varchar2,
601 p_val_42 in varchar2,
602 p_val_43 in varchar2,
603 p_val_44 in varchar2,
604 p_val_45 in varchar2,
605 p_val_46 in varchar2,
606 p_val_47 in varchar2,
607 p_val_48 in varchar2,
608 p_val_49 in varchar2,
609 p_val_50 in varchar2,
610 p_val_51 in varchar2,
611 p_val_52 in varchar2,
612 p_val_53 in varchar2,
613 p_val_54 in varchar2,
614 p_val_55 in varchar2,
615 p_val_56 in varchar2,
616 p_val_57 in varchar2,
617 p_val_58 in varchar2,
618 p_val_59 in varchar2,
619 p_val_60 in varchar2,
620 p_val_61 in varchar2,
621 p_val_62 in varchar2,
622 p_val_63 in varchar2,
623 p_val_64 in varchar2,
624 p_val_65 in varchar2,
625 p_val_66 in varchar2,
626 p_val_67 in varchar2,
627 p_val_68 in varchar2,
628 p_val_69 in varchar2,
629 p_val_70 in varchar2,
630 p_val_71 in varchar2,
631 p_val_72 in varchar2,
632 p_val_73 in varchar2,
633 p_val_74 in varchar2,
634 p_val_75 in varchar2,
635 p_val_76 in varchar2,
636 p_val_77 in varchar2,
637 p_val_78 in varchar2,
638 p_val_79 in varchar2,
639 p_val_80 in varchar2,
640 p_val_81 in varchar2,
641 p_val_82 in varchar2,
642 p_val_83 in varchar2,
643 p_val_84 in varchar2,
644 p_val_85 in varchar2,
645 p_val_86 in varchar2,
646 p_val_87 in varchar2,
647 p_val_88 in varchar2,
648 p_val_89 in varchar2,
649 p_val_90 in varchar2,
650 p_val_91 in varchar2,
651 p_val_92 in varchar2,
652 p_val_93 in varchar2,
653 p_val_94 in varchar2,
654 p_val_95 in varchar2,
655 p_val_96 in varchar2,
656 p_val_97 in varchar2,
657 p_val_98 in varchar2,
658 p_val_99 in varchar2,
659 p_val_100 in varchar2,
660 p_val_101 in varchar2,
661 p_val_102 in varchar2,
662 p_val_103 in varchar2,
663 p_val_104 in varchar2,
664 p_val_105 in varchar2,
665 p_val_106 in varchar2,
666 p_val_107 in varchar2,
667 p_val_108 in varchar2,
668 p_val_109 in varchar2,
669 p_val_110 in varchar2,
670 p_val_111 in varchar2,
671 p_val_112 in varchar2,
672 p_val_113 in varchar2,
673 p_val_114 in varchar2,
674 p_val_115 in varchar2,
675 p_val_116 in varchar2,
676 p_val_117 in varchar2,
677 p_val_118 in varchar2,
678 p_val_119 in varchar2,
679 p_val_120 in varchar2,
680 p_val_121 in varchar2,
681 p_val_122 in varchar2,
682 p_val_123 in varchar2,
683 p_val_124 in varchar2,
684 p_val_125 in varchar2,
685 p_val_126 in varchar2,
686 p_val_127 in varchar2,
687 p_val_128 in varchar2,
688 p_val_129 in varchar2,
689 p_val_130 in varchar2,
690 p_val_131 in varchar2,
691 p_val_132 in varchar2,
692 p_val_133 in varchar2,
693 p_val_134 in varchar2,
694 p_val_135 in varchar2,
698 p_val_139 in varchar2,
695 p_val_136 in varchar2,
696 p_val_137 in varchar2,
697 p_val_138 in varchar2,
699 p_val_140 in varchar2,
700 p_val_141 in varchar2,
701 p_val_142 in varchar2,
702 p_val_143 in varchar2,
703 p_val_144 in varchar2,
704 p_val_145 in varchar2,
705 p_val_146 in varchar2,
706 p_val_147 in varchar2,
707 p_val_148 in varchar2,
708 p_val_149 in varchar2,
709 p_val_150 in varchar2,
710 p_val_151 in varchar2,
711 p_val_152 in varchar2,
712 p_val_153 in varchar2,
713 p_val_154 in varchar2,
714 p_val_155 in varchar2,
715 p_val_156 in varchar2,
716 p_val_157 in varchar2,
717 p_val_158 in varchar2,
718 p_val_159 in varchar2,
719 p_val_160 in varchar2,
720 p_val_161 in varchar2,
721 p_val_162 in varchar2,
722 p_val_163 in varchar2,
723 p_val_164 in varchar2,
724 p_val_165 in varchar2,
725 p_val_166 in varchar2,
726 p_val_167 in varchar2,
727 p_val_168 in varchar2,
728 p_val_169 in varchar2,
729 p_val_170 in varchar2,
730 p_val_171 in varchar2,
731 p_val_172 in varchar2,
732 p_val_173 in varchar2,
733 p_val_174 in varchar2,
734 p_val_175 in varchar2,
735 p_val_176 in varchar2,
736 p_val_177 in varchar2,
737 p_val_178 in varchar2,
738 p_val_179 in varchar2,
739 p_val_180 in varchar2,
740 p_val_181 in varchar2,
741 p_val_182 in varchar2,
742 p_val_183 in varchar2,
743 p_val_184 in varchar2,
744 p_val_185 in varchar2,
745 p_val_186 in varchar2,
746 p_val_187 in varchar2,
747 p_val_188 in varchar2,
748 p_val_189 in varchar2,
749 p_val_190 in varchar2,
750 p_val_191 in varchar2,
751 p_val_192 in varchar2,
752 p_val_193 in varchar2,
753 p_val_194 in varchar2,
754 p_val_195 in varchar2,
755 p_val_196 in varchar2,
756 p_val_197 in varchar2,
757 p_val_198 in varchar2,
758 p_val_199 in varchar2,
759 p_val_200 in varchar2,
760 p_val_201 in varchar2,
761 p_val_202 in varchar2,
762 p_val_203 in varchar2,
763 p_val_204 in varchar2,
764 p_val_205 in varchar2,
765 p_val_206 in varchar2,
766 p_val_207 in varchar2,
767 p_val_208 in varchar2,
768 p_val_209 in varchar2,
769 p_val_210 in varchar2,
770 p_val_211 in varchar2,
771 p_val_212 in varchar2,
772 p_val_213 in varchar2,
773 p_val_214 in varchar2,
774 p_val_215 in varchar2,
775 p_val_216 in varchar2,
776 p_val_217 in varchar2,
777 p_val_218 in varchar2,
778 p_val_219 in varchar2,
779 p_val_220 in varchar2,
780 p_val_221 in varchar2,
781 p_val_222 in varchar2,
782 p_val_223 in varchar2,
783 p_val_224 in varchar2,
784 p_val_225 in varchar2,
785 p_val_226 in varchar2,
786 p_val_227 in varchar2,
787 p_val_228 in varchar2,
788 p_val_229 in varchar2,
789 p_val_230 in varchar2,
790 p_val_231 in varchar2,
791 p_val_232 in varchar2,
792 p_val_233 in varchar2,
793 p_val_234 in varchar2,
794 p_val_235 in varchar2,
795 p_val_236 in varchar2,
796 p_val_237 in varchar2,
797 p_val_238 in varchar2,
798 p_val_239 in varchar2,
799 p_val_240 in varchar2,
800 p_val_241 in varchar2,
801 p_val_242 in varchar2,
802 p_val_243 in varchar2,
803 p_val_244 in varchar2,
804 p_val_245 in varchar2,
805 p_val_246 in varchar2,
806 p_val_247 in varchar2,
807 p_val_248 in varchar2,
808 p_val_249 in varchar2,
809 p_val_250 in varchar2,
810 p_val_251 in varchar2,
811 p_val_252 in varchar2,
812 p_val_253 in varchar2,
813 p_val_254 in varchar2,
814 p_val_255 in varchar2,
815 p_val_256 in varchar2,
816 p_val_257 in varchar2,
817 p_val_258 in varchar2,
818 p_val_259 in varchar2,
819 p_val_260 in varchar2,
820 p_val_261 in varchar2,
821 p_val_262 in varchar2,
822 p_val_263 in varchar2,
823 p_val_264 in varchar2,
824 p_val_265 in varchar2,
825 p_val_266 in varchar2,
826 p_val_267 in varchar2,
827 p_val_268 in varchar2,
828 p_val_269 in varchar2,
829 p_val_270 in varchar2,
830 p_val_271 in varchar2,
831 p_val_272 in varchar2,
832 p_val_273 in varchar2,
833 p_val_274 in varchar2,
834 p_val_275 in varchar2,
835 p_val_276 in varchar2,
836 p_val_277 in varchar2,
837 p_val_278 in varchar2,
838 p_val_279 in varchar2,
839 p_val_280 in varchar2,
840 p_val_281 in varchar2,
841 p_val_282 in varchar2,
842 p_val_283 in varchar2,
843 p_val_284 in varchar2,
844 p_val_285 in varchar2,
845 p_val_286 in varchar2,
846 p_val_287 in varchar2,
847 p_val_288 in varchar2,
848 p_val_289 in varchar2,
849 p_val_290 in varchar2,
850 p_val_291 in varchar2,
851 p_val_292 in varchar2,
852 p_val_293 in varchar2,
853 p_val_294 in varchar2,
854 p_val_295 in varchar2,
855 p_val_296 in varchar2,
856 p_val_297 in varchar2,
857 p_val_298 in varchar2,
858 p_val_299 in varchar2,
859 p_val_300 in varchar2,
863 --
860 p_seq_num in number) is
861 --
862 l_proc varchar2(72) := g_package||'load_arrays';
864 begin
865 --
866 hr_utility.set_location('Entering'||l_proc, 5);
867 --
868 ben_ext_write.g_val(01) := ext_xml_encode( p_val_01) ;
869 ben_ext_write.g_val(02) := ext_xml_encode( p_val_02) ;
870 ben_ext_write.g_val(03) := ext_xml_encode( p_val_03) ;
871 ben_ext_write.g_val(04) := ext_xml_encode( p_val_04) ;
872 ben_ext_write.g_val(05) := ext_xml_encode( p_val_05) ;
873 ben_ext_write.g_val(06) := ext_xml_encode( p_val_06) ;
874 ben_ext_write.g_val(07) := ext_xml_encode( p_val_07) ;
875 ben_ext_write.g_val(08) := ext_xml_encode( p_val_08) ;
876 ben_ext_write.g_val(09) := ext_xml_encode( p_val_09) ;
877 ben_ext_write.g_val(10) := ext_xml_encode( p_val_10) ;
878 ben_ext_write.g_val(11) := ext_xml_encode( p_val_11) ;
879 ben_ext_write.g_val(12) := ext_xml_encode( p_val_12) ;
880 ben_ext_write.g_val(13) := ext_xml_encode( p_val_13) ;
881 ben_ext_write.g_val(14) := ext_xml_encode( p_val_14) ;
882 ben_ext_write.g_val(15) := ext_xml_encode( p_val_15) ;
883 ben_ext_write.g_val(16) := ext_xml_encode( p_val_16) ;
884 ben_ext_write.g_val(17) := ext_xml_encode( p_val_17) ;
885 ben_ext_write.g_val(18) := ext_xml_encode( p_val_18) ;
886 ben_ext_write.g_val(19) := ext_xml_encode( p_val_19) ;
887 ben_ext_write.g_val(20) := ext_xml_encode( p_val_20) ;
888 ben_ext_write.g_val(21) := ext_xml_encode( p_val_21) ;
889 ben_ext_write.g_val(22) := ext_xml_encode( p_val_22) ;
890 ben_ext_write.g_val(23) := ext_xml_encode( p_val_23) ;
891 ben_ext_write.g_val(24) := ext_xml_encode( p_val_24) ;
892 ben_ext_write.g_val(25) := ext_xml_encode( p_val_25) ;
893 ben_ext_write.g_val(26) := ext_xml_encode( p_val_26) ;
894 ben_ext_write.g_val(27) := ext_xml_encode( p_val_27) ;
895 ben_ext_write.g_val(28) := ext_xml_encode( p_val_28) ;
896 ben_ext_write.g_val(29) := ext_xml_encode( p_val_29) ;
897 ben_ext_write.g_val(30) := ext_xml_encode( p_val_30) ;
898 ben_ext_write.g_val(31) := ext_xml_encode( p_val_31) ;
899 ben_ext_write.g_val(32) := ext_xml_encode( p_val_32) ;
900 ben_ext_write.g_val(33) := ext_xml_encode( p_val_33) ;
901 ben_ext_write.g_val(34) := ext_xml_encode( p_val_34) ;
902 ben_ext_write.g_val(35) := ext_xml_encode( p_val_35) ;
903 ben_ext_write.g_val(36) := ext_xml_encode( p_val_36) ;
904 ben_ext_write.g_val(37) := ext_xml_encode( p_val_37) ;
905 ben_ext_write.g_val(38) := ext_xml_encode( p_val_38) ;
906 ben_ext_write.g_val(39) := ext_xml_encode( p_val_39) ;
907 ben_ext_write.g_val(40) := ext_xml_encode( p_val_40) ;
908 ben_ext_write.g_val(41) := ext_xml_encode( p_val_41) ;
909 ben_ext_write.g_val(42) := ext_xml_encode( p_val_42) ;
910 ben_ext_write.g_val(43) := ext_xml_encode( p_val_43) ;
911 ben_ext_write.g_val(44) := ext_xml_encode( p_val_44) ;
912 ben_ext_write.g_val(45) := ext_xml_encode( p_val_45) ;
913 ben_ext_write.g_val(46) := ext_xml_encode( p_val_46) ;
914 ben_ext_write.g_val(47) := ext_xml_encode( p_val_47) ;
915 ben_ext_write.g_val(48) := ext_xml_encode( p_val_48) ;
916 ben_ext_write.g_val(49) := ext_xml_encode( p_val_49) ;
917 ben_ext_write.g_val(50) := ext_xml_encode( p_val_50) ;
918 ben_ext_write.g_val(51) := ext_xml_encode( p_val_51) ;
919 ben_ext_write.g_val(52) := ext_xml_encode( p_val_52) ;
920 ben_ext_write.g_val(53) := ext_xml_encode( p_val_53) ;
921 ben_ext_write.g_val(54) := ext_xml_encode( p_val_54) ;
922 ben_ext_write.g_val(55) := ext_xml_encode( p_val_55) ;
923 ben_ext_write.g_val(56) := ext_xml_encode( p_val_56) ;
924 ben_ext_write.g_val(57) := ext_xml_encode( p_val_57) ;
925 ben_ext_write.g_val(58) := ext_xml_encode( p_val_58) ;
926 ben_ext_write.g_val(59) := ext_xml_encode( p_val_59) ;
927 ben_ext_write.g_val(60) := ext_xml_encode( p_val_60) ;
928 ben_ext_write.g_val(61) := ext_xml_encode( p_val_61) ;
929 ben_ext_write.g_val(62) := ext_xml_encode( p_val_62) ;
930 ben_ext_write.g_val(63) := ext_xml_encode( p_val_63) ;
931 ben_ext_write.g_val(64) := ext_xml_encode( p_val_64) ;
932 ben_ext_write.g_val(65) := ext_xml_encode( p_val_65) ;
933 ben_ext_write.g_val(66) := ext_xml_encode( p_val_66) ;
934 ben_ext_write.g_val(67) := ext_xml_encode( p_val_67) ;
935 ben_ext_write.g_val(68) := ext_xml_encode( p_val_68) ;
936 ben_ext_write.g_val(69) := ext_xml_encode( p_val_69) ;
937 ben_ext_write.g_val(70) := ext_xml_encode( p_val_70) ;
938 ben_ext_write.g_val(71) := ext_xml_encode( p_val_71) ;
939 ben_ext_write.g_val(72) := ext_xml_encode( p_val_72) ;
940 ben_ext_write.g_val(73) := ext_xml_encode( p_val_73) ;
941 ben_ext_write.g_val(74) := ext_xml_encode( p_val_74) ;
942 ben_ext_write.g_val(75) := ext_xml_encode( p_val_75) ;
943 ben_ext_write.g_val(76) := ext_xml_encode( p_val_76 );
944 ben_ext_write.g_val(77) := ext_xml_encode( p_val_77 );
945 ben_ext_write.g_val(78) := ext_xml_encode( p_val_78 );
946 ben_ext_write.g_val(79) := ext_xml_encode( p_val_79 );
947 ben_ext_write.g_val(80) := ext_xml_encode( p_val_80 );
948 ben_ext_write.g_val(81) := ext_xml_encode( p_val_81 );
949 ben_ext_write.g_val(82) := ext_xml_encode( p_val_82 );
950 ben_ext_write.g_val(83) := ext_xml_encode( p_val_83 );
951 ben_ext_write.g_val(84) := ext_xml_encode( p_val_84 );
952 ben_ext_write.g_val(85) := ext_xml_encode( p_val_85 );
953 ben_ext_write.g_val(86) := ext_xml_encode( p_val_86 );
954 ben_ext_write.g_val(87) := ext_xml_encode( p_val_87 );
955 ben_ext_write.g_val(88) := ext_xml_encode( p_val_88 );
956 ben_ext_write.g_val(89) := ext_xml_encode( p_val_89 );
957 ben_ext_write.g_val(90) := ext_xml_encode( p_val_90 );
958 ben_ext_write.g_val(91) := ext_xml_encode( p_val_91 );
959 ben_ext_write.g_val(92) := ext_xml_encode( p_val_92 );
960 ben_ext_write.g_val(93) := ext_xml_encode( p_val_93 );
961 ben_ext_write.g_val(94) := ext_xml_encode( p_val_94 );
962 ben_ext_write.g_val(95) := ext_xml_encode( p_val_95 );
963 ben_ext_write.g_val(96) := ext_xml_encode( p_val_96 );
964 ben_ext_write.g_val(97) := ext_xml_encode( p_val_97 );
968 ben_ext_write.g_val(101) := ext_xml_encode( p_val_101 );
965 ben_ext_write.g_val(98) := ext_xml_encode( p_val_98 );
966 ben_ext_write.g_val(99) := ext_xml_encode( p_val_99 );
967 ben_ext_write.g_val(100) := ext_xml_encode( p_val_100 );
969 ben_ext_write.g_val(102) := ext_xml_encode( p_val_102 );
970 ben_ext_write.g_val(103) := ext_xml_encode( p_val_103 );
971 ben_ext_write.g_val(104) := ext_xml_encode( p_val_104 );
972 ben_ext_write.g_val(105) := ext_xml_encode( p_val_105 );
973 ben_ext_write.g_val(106) := ext_xml_encode( p_val_106 );
974 ben_ext_write.g_val(107) := ext_xml_encode( p_val_107 );
975 ben_ext_write.g_val(108) := ext_xml_encode( p_val_108 );
976 ben_ext_write.g_val(109) := ext_xml_encode( p_val_109 );
977 ben_ext_write.g_val(110) := ext_xml_encode( p_val_110 );
978 ben_ext_write.g_val(111) := ext_xml_encode( p_val_111 );
979 ben_ext_write.g_val(112) := ext_xml_encode( p_val_112 );
980 ben_ext_write.g_val(113) := ext_xml_encode( p_val_113 );
981 ben_ext_write.g_val(114) := ext_xml_encode( p_val_114 );
982 ben_ext_write.g_val(115) := ext_xml_encode( p_val_115 );
983 ben_ext_write.g_val(116) := ext_xml_encode( p_val_116 );
984 ben_ext_write.g_val(117) := ext_xml_encode( p_val_117 );
985 ben_ext_write.g_val(118) := ext_xml_encode( p_val_118 );
986 ben_ext_write.g_val(119) := ext_xml_encode( p_val_119 );
987 ben_ext_write.g_val(120) := ext_xml_encode( p_val_120 );
991 ben_ext_write.g_val(124) := ext_xml_encode( p_val_124 );
988 ben_ext_write.g_val(121) := ext_xml_encode( p_val_121 );
989 ben_ext_write.g_val(122) := ext_xml_encode( p_val_122 );
990 ben_ext_write.g_val(123) := ext_xml_encode( p_val_123 );
992 ben_ext_write.g_val(125) := ext_xml_encode( p_val_125 );
993 ben_ext_write.g_val(126) := ext_xml_encode( p_val_126 );
994 ben_ext_write.g_val(127) := ext_xml_encode( p_val_127 );
995 ben_ext_write.g_val(128) := ext_xml_encode( p_val_128 );
996 ben_ext_write.g_val(129) := ext_xml_encode( p_val_129 );
997 ben_ext_write.g_val(130) := ext_xml_encode( p_val_130 );
998 ben_ext_write.g_val(131) := ext_xml_encode( p_val_131 );
999 ben_ext_write.g_val(132) := ext_xml_encode( p_val_132 );
1000 ben_ext_write.g_val(133) := ext_xml_encode( p_val_133 );
1001 ben_ext_write.g_val(134) := ext_xml_encode( p_val_134 );
1002 ben_ext_write.g_val(135) := ext_xml_encode( p_val_135 );
1003 ben_ext_write.g_val(136) := ext_xml_encode( p_val_136 );
1004 ben_ext_write.g_val(137) := ext_xml_encode( p_val_137 );
1005 ben_ext_write.g_val(138) := ext_xml_encode( p_val_138 );
1006 ben_ext_write.g_val(139) := ext_xml_encode( p_val_139 );
1007 ben_ext_write.g_val(140) := ext_xml_encode( p_val_140 );
1008 ben_ext_write.g_val(141) := ext_xml_encode( p_val_141 );
1009 ben_ext_write.g_val(142) := ext_xml_encode( p_val_142 );
1010 ben_ext_write.g_val(143) := ext_xml_encode( p_val_143 );
1011 ben_ext_write.g_val(144) := ext_xml_encode( p_val_144 );
1012 ben_ext_write.g_val(145) := ext_xml_encode( p_val_145 );
1013 ben_ext_write.g_val(146) := ext_xml_encode( p_val_146 );
1014 ben_ext_write.g_val(147) := ext_xml_encode( p_val_147 );
1015 ben_ext_write.g_val(148) := ext_xml_encode( p_val_148 );
1016 ben_ext_write.g_val(149) := ext_xml_encode( p_val_149 );
1017 ben_ext_write.g_val(150) := ext_xml_encode( p_val_150 );
1018 ben_ext_write.g_val(151) := ext_xml_encode( p_val_151 );
1019 ben_ext_write.g_val(152) := ext_xml_encode( p_val_152 );
1020 ben_ext_write.g_val(153) := ext_xml_encode( p_val_153 );
1021 ben_ext_write.g_val(154) := ext_xml_encode( p_val_154 );
1022 ben_ext_write.g_val(155) := ext_xml_encode( p_val_155 );
1023 ben_ext_write.g_val(156) := ext_xml_encode( p_val_156 );
1024 ben_ext_write.g_val(157) := ext_xml_encode( p_val_157 );
1025 ben_ext_write.g_val(158) := ext_xml_encode( p_val_158 );
1026 ben_ext_write.g_val(159) := ext_xml_encode( p_val_159 );
1027 ben_ext_write.g_val(160) := ext_xml_encode( p_val_160 );
1028 ben_ext_write.g_val(161) := ext_xml_encode( p_val_161 );
1029 ben_ext_write.g_val(162) := ext_xml_encode( p_val_162 );
1030 ben_ext_write.g_val(163) := ext_xml_encode( p_val_163 );
1031 ben_ext_write.g_val(164) := ext_xml_encode( p_val_164 );
1032 ben_ext_write.g_val(165) := ext_xml_encode( p_val_165 );
1033 ben_ext_write.g_val(166) := ext_xml_encode( p_val_166 );
1034 ben_ext_write.g_val(167) := ext_xml_encode( p_val_167 );
1035 ben_ext_write.g_val(168) := ext_xml_encode( p_val_168 );
1036 ben_ext_write.g_val(169) := ext_xml_encode( p_val_169 );
1037 ben_ext_write.g_val(170) := ext_xml_encode( p_val_170 );
1038 ben_ext_write.g_val(171) := ext_xml_encode( p_val_171 );
1039 ben_ext_write.g_val(172) := ext_xml_encode( p_val_172 );
1040 ben_ext_write.g_val(173) := ext_xml_encode( p_val_173 );
1041 ben_ext_write.g_val(174) := ext_xml_encode( p_val_174 );
1042 ben_ext_write.g_val(175) := ext_xml_encode( p_val_175 );
1043 ben_ext_write.g_val(176) := ext_xml_encode( p_val_176 );
1044 ben_ext_write.g_val(177) := ext_xml_encode( p_val_177 );
1045 ben_ext_write.g_val(178) := ext_xml_encode( p_val_178 );
1049 ben_ext_write.g_val(182) := ext_xml_encode( p_val_182 );
1046 ben_ext_write.g_val(179) := ext_xml_encode( p_val_179 );
1047 ben_ext_write.g_val(180) := ext_xml_encode( p_val_180 );
1048 ben_ext_write.g_val(181) := ext_xml_encode( p_val_181 );
1050 ben_ext_write.g_val(183) := ext_xml_encode( p_val_183 );
1051 ben_ext_write.g_val(184) := ext_xml_encode( p_val_184 );
1052 ben_ext_write.g_val(185) := ext_xml_encode( p_val_185 );
1053 ben_ext_write.g_val(186) := ext_xml_encode( p_val_186 );
1054 ben_ext_write.g_val(187) := ext_xml_encode( p_val_187 );
1055 ben_ext_write.g_val(188) := ext_xml_encode( p_val_188 );
1056 ben_ext_write.g_val(189) := ext_xml_encode( p_val_189 );
1057 ben_ext_write.g_val(190) := ext_xml_encode( p_val_190 );
1058 ben_ext_write.g_val(191) := ext_xml_encode( p_val_191 );
1059 ben_ext_write.g_val(192) := ext_xml_encode( p_val_192 );
1060 ben_ext_write.g_val(193) := ext_xml_encode( p_val_193 );
1061 ben_ext_write.g_val(194) := ext_xml_encode( p_val_194 );
1062 ben_ext_write.g_val(195) := ext_xml_encode( p_val_195 );
1063 ben_ext_write.g_val(196) := ext_xml_encode( p_val_196 );
1064 ben_ext_write.g_val(197) := ext_xml_encode( p_val_197 );
1065 ben_ext_write.g_val(198) := ext_xml_encode( p_val_198 );
1066 ben_ext_write.g_val(199) := ext_xml_encode( p_val_199 );
1067 ben_ext_write.g_val(200) := ext_xml_encode( p_val_200 );
1068 ben_ext_write.g_val(201) := ext_xml_encode( p_val_201 );
1069 ben_ext_write.g_val(202) := ext_xml_encode( p_val_202 );
1070 ben_ext_write.g_val(203) := ext_xml_encode( p_val_203 );
1071 ben_ext_write.g_val(204) := ext_xml_encode( p_val_204 );
1072 ben_ext_write.g_val(205) := ext_xml_encode( p_val_205 );
1073 ben_ext_write.g_val(206) := ext_xml_encode( p_val_206 );
1074 ben_ext_write.g_val(207) := ext_xml_encode( p_val_207 );
1075 ben_ext_write.g_val(208) := ext_xml_encode( p_val_208 );
1076 ben_ext_write.g_val(209) := ext_xml_encode( p_val_209 );
1077 ben_ext_write.g_val(210) := ext_xml_encode( p_val_210 );
1078 ben_ext_write.g_val(211) := ext_xml_encode( p_val_211 );
1079 ben_ext_write.g_val(212) := ext_xml_encode( p_val_212 );
1080 ben_ext_write.g_val(213) := ext_xml_encode( p_val_213 );
1081 ben_ext_write.g_val(214) := ext_xml_encode( p_val_214 );
1082 ben_ext_write.g_val(215) := ext_xml_encode( p_val_215 );
1083 ben_ext_write.g_val(216) := ext_xml_encode( p_val_216 );
1084 ben_ext_write.g_val(217) := ext_xml_encode( p_val_217 );
1085 ben_ext_write.g_val(218) := ext_xml_encode( p_val_218 );
1086 ben_ext_write.g_val(219) := ext_xml_encode( p_val_219 );
1087 ben_ext_write.g_val(220) := ext_xml_encode( p_val_220 );
1088 ben_ext_write.g_val(221) := ext_xml_encode( p_val_221 );
1089 ben_ext_write.g_val(222) := ext_xml_encode( p_val_222 );
1090 ben_ext_write.g_val(223) := ext_xml_encode( p_val_223 );
1091 ben_ext_write.g_val(224) := ext_xml_encode( p_val_224 );
1092 ben_ext_write.g_val(225) := ext_xml_encode( p_val_225 );
1093 ben_ext_write.g_val(226) := ext_xml_encode( p_val_226 );
1094 ben_ext_write.g_val(227) := ext_xml_encode( p_val_227 );
1095 ben_ext_write.g_val(228) := ext_xml_encode( p_val_228 );
1096 ben_ext_write.g_val(229) := ext_xml_encode( p_val_229 );
1097 ben_ext_write.g_val(230) := ext_xml_encode( p_val_230 );
1098 ben_ext_write.g_val(231) := ext_xml_encode( p_val_231 );
1099 ben_ext_write.g_val(232) := ext_xml_encode( p_val_232 );
1100 ben_ext_write.g_val(233) := ext_xml_encode( p_val_233 );
1101 ben_ext_write.g_val(234) := ext_xml_encode( p_val_234 );
1102 ben_ext_write.g_val(235) := ext_xml_encode( p_val_235 );
1106 ben_ext_write.g_val(239) := ext_xml_encode( p_val_239 );
1103 ben_ext_write.g_val(236) := ext_xml_encode( p_val_236 );
1104 ben_ext_write.g_val(237) := ext_xml_encode( p_val_237 );
1105 ben_ext_write.g_val(238) := ext_xml_encode( p_val_238 );
1107 ben_ext_write.g_val(240) := ext_xml_encode( p_val_240 );
1108 ben_ext_write.g_val(241) := ext_xml_encode( p_val_241 );
1109 ben_ext_write.g_val(242) := ext_xml_encode( p_val_242 );
1110 ben_ext_write.g_val(243) := ext_xml_encode( p_val_243 );
1111 ben_ext_write.g_val(244) := ext_xml_encode( p_val_244 );
1112 ben_ext_write.g_val(245) := ext_xml_encode( p_val_245 );
1113 ben_ext_write.g_val(246) := ext_xml_encode( p_val_246 );
1114 ben_ext_write.g_val(247) := ext_xml_encode( p_val_247 );
1115 ben_ext_write.g_val(248) := ext_xml_encode( p_val_248 );
1116 ben_ext_write.g_val(249) := ext_xml_encode( p_val_249 );
1117 ben_ext_write.g_val(250) := ext_xml_encode( p_val_250 );
1118 ben_ext_write.g_val(251) := ext_xml_encode( p_val_251 );
1119 ben_ext_write.g_val(252) := ext_xml_encode( p_val_252 );
1120 ben_ext_write.g_val(253) := ext_xml_encode( p_val_253 );
1121 ben_ext_write.g_val(254) := ext_xml_encode( p_val_254 );
1122 ben_ext_write.g_val(255) := ext_xml_encode( p_val_255 );
1123 ben_ext_write.g_val(256) := ext_xml_encode( p_val_256 );
1124 ben_ext_write.g_val(257) := ext_xml_encode( p_val_257 );
1125 ben_ext_write.g_val(258) := ext_xml_encode( p_val_258 );
1129 ben_ext_write.g_val(262) := ext_xml_encode( p_val_262 );
1126 ben_ext_write.g_val(259) := ext_xml_encode( p_val_259 );
1127 ben_ext_write.g_val(260) := ext_xml_encode( p_val_260 );
1128 ben_ext_write.g_val(261) := ext_xml_encode( p_val_261 );
1130 ben_ext_write.g_val(263) := ext_xml_encode( p_val_263 );
1131 ben_ext_write.g_val(264) := ext_xml_encode( p_val_264 );
1132 ben_ext_write.g_val(265) := ext_xml_encode( p_val_265 );
1133 ben_ext_write.g_val(266) := ext_xml_encode( p_val_266 );
1134 ben_ext_write.g_val(267) := ext_xml_encode( p_val_267 );
1135 ben_ext_write.g_val(268) := ext_xml_encode( p_val_268 );
1136 ben_ext_write.g_val(269) := ext_xml_encode( p_val_269 );
1137 ben_ext_write.g_val(270) := ext_xml_encode( p_val_270 );
1138 ben_ext_write.g_val(271) := ext_xml_encode( p_val_271 );
1139 ben_ext_write.g_val(272) := ext_xml_encode( p_val_272 );
1140 ben_ext_write.g_val(273) := ext_xml_encode( p_val_273 );
1141 ben_ext_write.g_val(274) := ext_xml_encode( p_val_274 );
1142 ben_ext_write.g_val(275) := ext_xml_encode( p_val_275 );
1143 ben_ext_write.g_val(276) := ext_xml_encode( p_val_276 );
1144 ben_ext_write.g_val(277) := ext_xml_encode( p_val_277 );
1145 ben_ext_write.g_val(278) := ext_xml_encode( p_val_278 );
1146 ben_ext_write.g_val(279) := ext_xml_encode( p_val_279 );
1147 ben_ext_write.g_val(280) := ext_xml_encode( p_val_280 );
1148 ben_ext_write.g_val(281) := ext_xml_encode( p_val_281 );
1149 ben_ext_write.g_val(282) := ext_xml_encode( p_val_282 );
1150 ben_ext_write.g_val(283) := ext_xml_encode( p_val_283 );
1151 ben_ext_write.g_val(284) := ext_xml_encode( p_val_284 );
1152 ben_ext_write.g_val(285) := ext_xml_encode( p_val_285 );
1153 ben_ext_write.g_val(286) := ext_xml_encode( p_val_286 );
1154 ben_ext_write.g_val(287) := ext_xml_encode( p_val_287 );
1155 ben_ext_write.g_val(288) := ext_xml_encode( p_val_288 );
1156 ben_ext_write.g_val(289) := ext_xml_encode( p_val_289 );
1157 ben_ext_write.g_val(290) := ext_xml_encode( p_val_290 );
1158 ben_ext_write.g_val(291) := ext_xml_encode( p_val_291 );
1159 ben_ext_write.g_val(292) := ext_xml_encode( p_val_292 );
1160 ben_ext_write.g_val(293) := ext_xml_encode( p_val_293 );
1161 ben_ext_write.g_val(294) := ext_xml_encode( p_val_294 );
1162 ben_ext_write.g_val(295) := ext_xml_encode( p_val_295 );
1163 ben_ext_write.g_val(296) := ext_xml_encode( p_val_296 );
1164 ben_ext_write.g_val(297) := ext_xml_encode( p_val_297 );
1165 ben_ext_write.g_val(298) := ext_xml_encode( p_val_298 );
1166 ben_ext_write.g_val(299) := ext_xml_encode( p_val_299 );
1167 ben_ext_write.g_val(300) := ext_xml_encode( p_val_300 );
1168
1169
1170 --
1171 hr_utility.set_location('Exiting'||l_proc, 15);
1172 --
1173 --
1174 end load_arrays;
1175 -----------------------------------------------------------------------------
1176
1177 Procedure MAIN
1178 (p_output_name in varchar2,
1179 p_drctry_name in varchar2,
1180 p_ext_rslt_id in number,
1181 p_output_type in varchar2,
1182 p_xdo_template_id in number ,
1183 p_cm_display_flag in varchar2 default null,
1184 p_rec_count in out NOCOPY number ,
1185 p_source in varchar2 default 'BENXWRIT' ) is
1186
1187 --
1188 --
1189 file_handle utl_file.file_type;
1190 --
1191 cursor c_xrd is
1192 select xrd.ext_rcd_id,
1193 xrd.person_id,
1194 xrd.val_01,
1195 xrd.val_02,
1196 xrd.val_03,
1197 xrd.val_04,
1198 xrd.val_05,
1199 xrd.val_06,
1200 xrd.val_07,
1201 xrd.val_08,
1202 xrd.val_09,
1203 xrd.val_10,
1204 xrd.val_11,
1205 xrd.val_12,
1206 xrd.val_13,
1207 xrd.val_14,
1208 xrd.val_15,
1209 xrd.val_16,
1210 xrd.val_17,
1211 xrd.val_18,
1212 xrd.val_19,
1213 xrd.val_20,
1214 xrd.val_21,
1215 xrd.val_22,
1216 xrd.val_23,
1217 xrd.val_24,
1218 xrd.val_25,
1219 xrd.val_26,
1220 xrd.val_27,
1221 xrd.val_28,
1222 xrd.val_29,
1223 xrd.val_30,
1224 xrd.val_31,
1225 xrd.val_32,
1226 xrd.val_33,
1227 xrd.val_34,
1228 xrd.val_35,
1229 xrd.val_36,
1230 xrd.val_37,
1231 xrd.val_38,
1232 xrd.val_39,
1233 xrd.val_40,
1234 xrd.val_41,
1235 xrd.val_42,
1236 xrd.val_43,
1237 xrd.val_44,
1238 xrd.val_45,
1239 xrd.val_46,
1240 xrd.val_47,
1241 xrd.val_48,
1242 xrd.val_49,
1243 xrd.val_50,
1244 xrd.val_51,
1245 xrd.val_52,
1246 xrd.val_53,
1247 xrd.val_54,
1248 xrd.val_55,
1249 xrd.val_56,
1250 xrd.val_57,
1251 xrd.val_58,
1252 xrd.val_59,
1253 xrd.val_60,
1254 xrd.val_61,
1255 xrd.val_62,
1256 xrd.val_63,
1257 xrd.val_64,
1258 xrd.val_65,
1259 xrd.val_66,
1260 xrd.val_67,
1261 xrd.val_68,
1262 xrd.val_69,
1263 xrd.val_70,
1264 xrd.val_71,
1265 xrd.val_72,
1266 xrd.val_73,
1267 xrd.val_74,
1271 xrd.val_78,
1268 xrd.val_75,
1269 xrd.val_76,
1270 xrd.val_77,
1272 xrd.val_79,
1273 xrd.val_80,
1274 xrd.val_81,
1275 xrd.val_82,
1276 xrd.val_83,
1277 xrd.val_84,
1278 xrd.val_85,
1279 xrd.val_86,
1280 xrd.val_87,
1281 xrd.val_88,
1282 xrd.val_89,
1283 xrd.val_90,
1284 xrd.val_91,
1285 xrd.val_92,
1286 xrd.val_93,
1287 xrd.val_94,
1288 xrd.val_95,
1289 xrd.val_96,
1290 xrd.val_97,
1291 xrd.val_98,
1292 xrd.val_99,
1293 xrd.val_100,
1294 xrd.val_101,
1295 xrd.val_102,
1296 xrd.val_103,
1297 xrd.val_104,
1298 xrd.val_105,
1299 xrd.val_106,
1300 xrd.val_107,
1301 xrd.val_108,
1302 xrd.val_109,
1303 xrd.val_110,
1304 xrd.val_111,
1305 xrd.val_112,
1306 xrd.val_113,
1307 xrd.val_114,
1308 xrd.val_115,
1309 xrd.val_116,
1310 xrd.val_117,
1311 xrd.val_118,
1312 xrd.val_119,
1313 xrd.val_120,
1314 xrd.val_121,
1315 xrd.val_122,
1316 xrd.val_123,
1317 xrd.val_124,
1318 xrd.val_125,
1319 xrd.val_126,
1320 xrd.val_127,
1321 xrd.val_128,
1322 xrd.val_129,
1323 xrd.val_130,
1324 xrd.val_131,
1325 xrd.val_132,
1326 xrd.val_133,
1327 xrd.val_134,
1328 xrd.val_135,
1329 xrd.val_136,
1330 xrd.val_137,
1331 xrd.val_138,
1332 xrd.val_139,
1333 xrd.val_140,
1334 xrd.val_141,
1335 xrd.val_142,
1336 xrd.val_143,
1337 xrd.val_144,
1338 xrd.val_145,
1339 xrd.val_146,
1340 xrd.val_147,
1341 xrd.val_148,
1342 xrd.val_149,
1343 xrd.val_150,
1344 xrd.val_151,
1345 xrd.val_152,
1346 xrd.val_153,
1347 xrd.val_154,
1348 xrd.val_155,
1349 xrd.val_156,
1350 xrd.val_157,
1351 xrd.val_158,
1352 xrd.val_159,
1353 xrd.val_160,
1354 xrd.val_161,
1355 xrd.val_162,
1356 xrd.val_163,
1357 xrd.val_164,
1358 xrd.val_165,
1359 xrd.val_166,
1360 xrd.val_167,
1361 xrd.val_168,
1362 xrd.val_169,
1363 xrd.val_170,
1364 xrd.val_171,
1365 xrd.val_172,
1366 xrd.val_173,
1367 xrd.val_174,
1368 xrd.val_175,
1369 xrd.val_176,
1370 xrd.val_177,
1371 xrd.val_178,
1372 xrd.val_179,
1373 xrd.val_180,
1374 xrd.val_181,
1375 xrd.val_182,
1376 xrd.val_183,
1377 xrd.val_184,
1378 xrd.val_185,
1379 xrd.val_186,
1380 xrd.val_187,
1381 xrd.val_188,
1382 xrd.val_189,
1383 xrd.val_190,
1384 xrd.val_191,
1385 xrd.val_192,
1386 xrd.val_193,
1387 xrd.val_194,
1388 xrd.val_195,
1389 xrd.val_196,
1390 xrd.val_197,
1391 xrd.val_198,
1392 xrd.val_199,
1393 xrd.val_200,
1394 xrd.val_201,
1395 xrd.val_202,
1396 xrd.val_203,
1397 xrd.val_204,
1398 xrd.val_205,
1399 xrd.val_206,
1400 xrd.val_207,
1401 xrd.val_208,
1402 xrd.val_209,
1403 xrd.val_210,
1404 xrd.val_211,
1405 xrd.val_212,
1406 xrd.val_213,
1407 xrd.val_214,
1408 xrd.val_215,
1409 xrd.val_216,
1410 xrd.val_217,
1411 xrd.val_218,
1412 xrd.val_219,
1413 xrd.val_220,
1414 xrd.val_221,
1415 xrd.val_222,
1416 xrd.val_223,
1417 xrd.val_224,
1418 xrd.val_225,
1419 xrd.val_226,
1420 xrd.val_227,
1421 xrd.val_228,
1422 xrd.val_229,
1423 xrd.val_230,
1424 xrd.val_231,
1425 xrd.val_232,
1426 xrd.val_233,
1427 xrd.val_234,
1428 xrd.val_235,
1429 xrd.val_236,
1430 xrd.val_237,
1431 xrd.val_238,
1432 xrd.val_239,
1433 xrd.val_240,
1434 xrd.val_241,
1435 xrd.val_242,
1439 xrd.val_246,
1436 xrd.val_243,
1437 xrd.val_244,
1438 xrd.val_245,
1440 xrd.val_247,
1441 xrd.val_248,
1442 xrd.val_249,
1443 xrd.val_250,
1444 xrd.val_251,
1445 xrd.val_252,
1446 xrd.val_253,
1447 xrd.val_254,
1448 xrd.val_255,
1449 xrd.val_256,
1450 xrd.val_257,
1451 xrd.val_258,
1452 xrd.val_259,
1453 xrd.val_260,
1454 xrd.val_261,
1455 xrd.val_262,
1456 xrd.val_263,
1457 xrd.val_264,
1458 xrd.val_265,
1459 xrd.val_266,
1460 xrd.val_267,
1461 xrd.val_268,
1462 xrd.val_269,
1463 xrd.val_270,
1464 xrd.val_271,
1465 xrd.val_272,
1466 xrd.val_273,
1467 xrd.val_274,
1468 xrd.val_275,
1469 xrd.val_276,
1470 xrd.val_277,
1471 xrd.val_278,
1472 xrd.val_279,
1473 xrd.val_280,
1474 xrd.val_281,
1475 xrd.val_282,
1476 xrd.val_283,
1477 xrd.val_284,
1478 xrd.val_285,
1479 xrd.val_286,
1480 xrd.val_287,
1481 xrd.val_288,
1482 xrd.val_289,
1483 xrd.val_290,
1484 xrd.val_291,
1485 xrd.val_292,
1486 xrd.val_293,
1487 xrd.val_294,
1488 xrd.val_295,
1489 xrd.val_296,
1490 xrd.val_297,
1491 xrd.val_298,
1492 xrd.val_299,
1493 xrd.val_300,
1494 xrf.seq_num,
1495 xrd.group_val_01
1496 from ben_ext_rslt_dtl xrd,
1497 ben_ext_rslt xrs,
1498 ben_ext_dfn xdf,
1499 ben_ext_rcd_in_file xrf
1500 where xrd.ext_rslt_id = p_ext_rslt_id
1504 and xrd.ext_rcd_id = xrf.ext_rcd_id
1501 and xrd.ext_rslt_id = xrs.ext_rslt_id
1502 and xrs.ext_dfn_id = xdf.ext_dfn_id
1503 and xdf.ext_file_id = xrf.ext_file_id
1505 and (xrd.ext_rcd_in_file_id is null
1506 or xrd.ext_rcd_in_file_id = xrf.ext_rcd_in_file_id ) -- or condition taken care of previous results
1507 and xrf.hide_flag = 'N'
1508 order by xrd.group_val_01,
1509 xrd.group_val_02,
1510 xrd.prmy_sort_val,
1511 xrd.scnd_sort_val,
1512 xrd.thrd_sort_val,
1513 xrf.seq_num ; -- this is addedd ther are many time header may not sorted in order
1514
1515 cursor c_ext_file is
1516 select xdf.ext_file_id,
1520 xdf.cm_display_flag,
1517 xdf.name,
1518 xdf.xml_tag_name,
1519 xdf.data_typ_cd ,
1521 xrs.eff_dt,
1522 xrs.run_strt_dt,
1523 xrs.run_end_dt,
1524 xfi.xml_tag_name file_xml_tag_name ,
1525 nvl( xrs.xdo_template_id, xdf.xdo_template_id ) xdo_template_id
1526 from ben_Ext_dfn xdf,
1527 ben_Ext_rslt xrs,
1528 ben_ext_file xfi
1529 where xrs.ext_rslt_id = p_ext_rslt_id
1530 and xrs.ext_dfn_id = xdf.ext_dfn_id
1531 and xdf.ext_file_id = xfi.ext_file_id ;
1532
1533 l_ext_file c_ext_file%rowtype ;
1534
1535 cursor c_ext_rcd ( c_ext_file_id number ,
1536 c_ext_rcd_id number ) is
1537 select rcd.name ,
1538 rcd.xml_tag_name ,
1539 erf.seq_num,
1540 rcd.rcd_type_cd,
1541 erf.hide_flag ,
1542 rcd.LOW_LVL_CD
1543 from ben_ext_rcd_in_file erf ,
1544 ben_ext_rcd rcd
1545 where erf.ext_file_id = c_ext_file_id
1546 and erf.ext_rcd_id = c_ext_rcd_id
1547 and rcd.ext_rcd_id = erf.ext_rcd_id
1548 order by erf.seq_num ;
1549 l_ext_rcd c_ext_rcd%rowtype ;
1550
1551 cursor c_elmt (c_ext_rcd_id number) is
1552 select elm.name,
1553 elm.xml_tag_name,
1554 rcd.seq_num,
1555 elm.data_elmt_typ_cd,
1556 elm.max_length_num,
1557 elm.frmt_mask_cd,
1558 elm.just_cd
1559 from ben_ext_data_elmt_in_rcd rcd ,
1560 ben_ext_data_elmt elm
1561 where rcd.ext_rcd_id = c_ext_rcd_id
1562 and elm.ext_data_elmt_id = rcd.ext_data_elmt_id
1563 and nvl(rcd.hide_flag,'N') = 'N'
1564 order by rcd.seq_num;
1565
1566 cursor c_defa_tags is
1567 select lookup_code ,
1568 meaning
1569 from hr_lookups
1570 where lookup_type = 'BEN_EXT_XML_TAGS' ;
1571
1572
1573
1574 l_elm_xml_tag_name ben_ext_data_elmt.xml_tag_name%type ;
1575 l_elm_name ben_ext_data_elmt.name%type ;
1576 l_elmt_seq_num ben_ext_data_elmt_in_rcd.seq_num%type ;
1577 l_elm_typ_cd ben_ext_data_elmt.data_elmt_typ_cd%type ;
1578 l_elm_max_length ben_ext_data_elmt.max_length_num%type ;
1579 l_elm_frmt_mask ben_ext_data_elmt.frmt_mask_cd%type ;
1580 l_elm_just_cd ben_ext_data_elmt.just_cd%type ;
1581 l_elm_val ben_ext_rslt_dtl.val_01%type ;
1582 l_rec_num number := 0 ;
1583 l_output_name ben_ext_rslt.output_name%type ;
1584 l_file_name ben_ext_rslt.output_name%type ;
1585 l_var varchar2(4000) ;
1586 l_new_rec number := -1 ;
1587 l_elmt_defa_tag ben_ext_data_elmt.xml_tag_name%type ;
1588 l_rcd_defa_tag ben_ext_data_elmt.xml_tag_name%type ;
1589 l_rcdset_defa_tag ben_ext_data_elmt.xml_tag_name%type ;
1590 l_file_defa_tag ben_ext_data_elmt.xml_tag_name%type ;
1591 l_dfn_defa_tag ben_ext_data_elmt.xml_tag_name%type ;
1592 l_xdo_template_id number ;
1593 l_cm_display_flag varchar2(30) ;
1594 --
1595 l_old_person_id number := -1 ;
1596
1597 cursor c_per_info (p_person_id number, p_effective_date date) is
1598 select full_name,
1599 employee_number
1600 from per_all_people_f
1601 where person_id = p_person_id
1602 and p_effective_date between EFFECTIVE_START_DATE and
1603 nvl(EFFECTIVE_END_DATE,trunc(sysdate));
1604 l_name per_all_people_f.full_name%type ;
1605 l_employee_number per_all_people_f.employee_number%type ;
1606
1607 --
1608 l_proc varchar2(72) := g_package||'main';
1609 l_max_ext_line_size Number := 32767 ;
1610 -- pdf cahnges
1611 l_pdf_output_name ben_ext_rslt.output_name%type ;
1612 l_extn_name varchar2(50) ;
1613 l_prv_low_lvl_cd varchar2(5) ;
1614 l_low_lvl_name varchar2(250) ;
1615 l_low_lvl_cd varchar2(250) ;
1616 l_Low_lvl_grouping varchar2(1) := 'N' ;
1617 l_prev_levl_found varchar2(1) := 'N' ;
1618
1619
1620 l_directory_name varchar2(2000) ;
1621
1622 cursor c_fnd_dir is
1623 select outfile_name
1624 from fnd_concurrent_requests
1625 where request_id = fnd_global.conc_request_id ;
1626 begin
1627 --
1628 hr_Utility.set_location('Entering'||l_proc, 5);
1629
1630 fnd_profile.get( NAME => 'ICX_CLIENT_IANA_ENCODING'
1631 ,VAL => g_iana_char_set );
1632 if g_iana_char_set is null then
1633 g_iana_char_set := 'UTF-8' ;
1634 end if ;
1635
1636 hr_Utility.set_location('iana character '|| l_output_name, 5);
1637
1638 --- Process for xml_file l_xml_result
1639 l_output_name := rtrim(p_output_name);
1640
1641 --- to create extract schema and stylesheet n output file name
1642
1643 l_file_name := l_output_name ;
1644 if instr( l_output_name,'.',-1) > 1 then
1645 l_file_name := substr(l_output_name,1,instr( l_output_name,'.',-1)-1) ;
1646 end if ;
1647
1648
1649 -- we can not create same file for xml and pdf
1650 -- so pdf created in given name and pdf changed by adding xml to the end
1651 -- of the file extension is xml then add .xml to then , else
1652 -- create the file name with xml extension
1653 if p_output_type <> 'X' then
1654 l_pdf_output_name := l_output_name ;
1655 l_extn_name := substr(l_pdf_output_name,instr( l_pdf_output_name,'.',-1)+1) ;
1656 if upper(l_extn_name) = 'XML' then
1657 l_output_name := l_output_name || '.xml' ;
1658 else
1662 end if ;
1659 l_output_name := l_file_name || '.xml' ;
1660 end if ;
1661 l_Low_lvl_grouping := 'Y' ; -- for pdf output subgrouping enabled
1663
1664
1665 --- intialise the default tag variable and validate variable
1666 --- if the lookup variable is null the system does not throw errror
1667 --- because the variable may not be used if the tags are fefined in extract
1668 for i in c_defa_tags
1669 Loop
1673 if i.lookup_code = 'EXT_FILE' then
1670 if i.lookup_code = 'EXT_DFN' then
1671 l_dfn_defa_tag := i.meaning ;
1672 end if ;
1674 l_file_defa_tag := i.meaning ;
1675 end if ;
1676 if i.lookup_code = 'EXT_RCD' then
1677 l_rcd_defa_tag := i.meaning ;
1678 end if ;
1679 if i.lookup_code = 'EXT_RCD_SET' then
1680 l_rcdset_defa_tag := i.meaning ;
1681 end if ;
1682 if i.lookup_code = 'EXT_ELMT' then
1683 l_elmt_defa_tag := i.meaning ;
1684 end if ;
1685
1686 end loop ;
1687 --- if the default element variable are not null
1688 --- validate the tags
1689 if l_dfn_defa_tag is not null then
1690 ben_xel_bus.chk_xml_name_format
1691 ( p_xml_tag_name => l_dfn_defa_tag
1692 ) ;
1693 end if ;
1694
1695 if l_file_defa_tag is not null then
1696 ben_xel_bus.chk_xml_name_format
1697 ( p_xml_tag_name => l_file_defa_tag
1698 ) ;
1699 end if ;
1700
1701 if l_rcd_defa_tag is not null then
1702 ben_xel_bus.chk_xml_name_format
1703 ( p_xml_tag_name => l_rcd_defa_tag
1704 ) ;
1705 end if ;
1706
1707 if l_rcdset_defa_tag is not null then
1708 ben_xel_bus.chk_xml_name_format
1709 ( p_xml_tag_name => l_rcdset_defa_tag
1710 ) ;
1711 end if ;
1712
1713 if l_elmt_defa_tag is not null then
1714 ben_xel_bus.chk_xml_name_format
1715 ( p_xml_tag_name => l_elmt_defa_tag
1716 ) ;
1717 end if ;
1718
1719
1720 --- Extract File/Definition Information
1721 open c_ext_file ;
1722 fetch c_ext_file into l_ext_file ;
1723 close c_ext_file ;
1724
1725
1726 ---
1727 l_xdo_template_id := nvl(p_xdo_template_id , l_ext_file.xdo_template_id ) ;
1728 l_cm_display_flag := nvl(p_cm_display_flag , l_ext_file.cm_display_flag ) ;
1729 --l_cm_display_flag := p_cm_display_flag ;
1730
1731 l_directory_name := p_drctry_name ;
1732 if p_cm_display_flag = 'Y' then
1736 l_directory_name := rtrim(l_directory_name,'o'||fnd_global.conc_request_id||'.out') ;
1733 open c_fnd_dir ;
1734 fetch c_fnd_dir into l_directory_name ;
1735 close c_fnd_dir ;
1737 l_directory_name := substr( l_directory_name, 1, length( l_directory_name)-1) ;
1738 l_file_name := 'o'||fnd_global.conc_request_id ;
1739 l_output_name := 'o'||fnd_global.conc_request_id||'.out' ;
1740 end if ;
1741
1742
1743 if l_cm_display_flag <> 'Y' then
1744 file_handle := utl_file.fopen (l_directory_name ,l_output_name,'w' , l_max_ext_line_size );
1745 end if ;
1746
1747 g_dfn_tag := nvl(l_ext_file.xml_tag_name,l_dfn_defa_tag) ;
1748 g_file_tag := nvl(l_ext_file.file_xml_tag_name,l_file_defa_tag ) ;
1749 -- when the output type is pdf dont write the header
1750 if p_output_type <> 'X' then
1751
1752 l_var := '<?xml version="1.0" encoding="'||g_iana_char_set||'"?>';
1753 l_var := l_var || '<'||nvl(l_ext_file.xml_tag_name,l_dfn_defa_tag )||'>' ;
1754 if l_cm_display_flag = 'Y' then
1755 fnd_file.put_line(fnd_file.OUTPUT, l_var);
1756 else
1757 utl_file.put_line(file_handle, l_var);
1758 end if ;
1759 else
1760 --- Write the xml header
1761 if l_cm_display_flag = 'Y' then
1762 fnd_file.put_line(fnd_file.OUTPUT, '<?xml version="1.0" encoding="'||g_iana_char_set||'"?>');
1763 else
1764 utl_file.put_line(file_handle, '<?xml version="1.0" encoding="'||g_iana_char_set||'"?>');
1765 end if ;
1766
1767 --utl_file.put_line(file_handle, '<?xml version="1.0" encoding="'||g_iana_char_set||'"?>');
1768 -- Write the refference to style sheet, assume the style sheet in current directory
1769 if l_cm_display_flag = 'Y' then
1770 null ;
1771 --fnd_file.put_line(fnd_file.OUTPUT, '<?xml-stylesheet type="text/xsl" href="'||nvl(l_file_name,'benxxssh')||'.xsl"?>');
1772 else
1773
1774 utl_file.put_line(file_handle, '<?xml-stylesheet type="text/xsl" href="'||nvl(l_file_name,'benxxssh')||'.xsl"?>');
1775 end if ;
1776
1777 -- utl_file.put_line(file_handle, '<?xml-stylesheet type="text/xsl" href="'||nvl(l_file_name,'benxxssh')||'.xsl"?>');
1778
1779 -- write the xml root element with reference to Schemea , the assumption is
1780 -- again the schema doc is avaialble in currect dir
1781
1782 --l_var:='<ext:'||nvl(l_ext_file.xml_tag_name,l_dfn_defa_tag )||' xmlns:ext="http://www.oracle.com/xml/OAB/ext"' ;
1783 --l_var:='<'||nvl(l_ext_file.xml_tag_name,l_dfn_defa_tag )||' xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" ' ;
1784 --utl_file.put_line(file_handle, l_var);
1785
1786 --if l_cm_display_flag = 'Y' then
1787 -- fnd_file.put_line(fnd_file.OUTPUT, l_var);
1788 --else
1789 -- utl_file.put_line(file_handle, l_var);
1790 -- end if ;
1791
1792 ---utl_file.put_line(file_handle, ' xmlns="http://www.oracle.com/xml/OAB/ext" ');
1793 --- utl_file.put_line(file_handle, ' xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" ');
1794 l_var:='<'||nvl(l_ext_file.xml_tag_name,l_dfn_defa_tag )||' xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" ' ;
1795 if l_cm_display_flag = 'Y' then
1796 --fnd_file.put_line(fnd_file.OUTPUT, l_var);
1797 --fnd_file.put_line(fnd_file.OUTPUT, ' xsi:noNamespaceSchemaLocation="'|| nvl(l_file_name,'benxxsch')||'.xsd">');
1798 null ;
1799 else
1800 utl_file.put_line(file_handle, l_var);
1801 utl_file.put_line(file_handle, ' xsi:noNamespaceSchemaLocation="'|| nvl(l_file_name,'benxxsch')||'.xsd">');
1802 end if ;
1803
1804 end if ;
1805
1806 -- write file/definition information
1807 l_var := '<' ||nvl(l_ext_file.file_xml_tag_name,l_file_defa_tag) || ' ext_dfn_name="'||l_ext_file.name || '" '||
1808 'ext_type="' || l_ext_file.data_typ_cd || '" '||
1809 'effective_date="'||l_ext_file.eff_dt|| '" '||
1810 'start_time="' || l_ext_file.run_strt_dt || '" '||
1811 'end_time="' || l_ext_file.run_end_dt || '" > ' ;
1812
1813 --utl_file.put_line(file_handle, l_var );
1814
1815 if l_cm_display_flag = 'Y' then
1816 fnd_file.put_line(fnd_file.OUTPUT, l_var);
1817 else
1818 utl_file.put_line(file_handle, l_var);
1819 end if ;
1820
1821 p_rec_count := 0 ;
1822 ---- Loop the the record
1823 for l_xrd in c_xrd loop
1824
1825 -----
1826 load_arrays
1827 (l_xrd.ext_rcd_id,
1828 l_xrd.val_01,
1832 l_xrd.val_05,
1829 l_xrd.val_02,
1830 l_xrd.val_03,
1831 l_xrd.val_04,
1833 l_xrd.val_06,
1834 l_xrd.val_07,
1835 l_xrd.val_08,
1836 l_xrd.val_09,
1837 l_xrd.val_10,
1838 l_xrd.val_11,
1839 l_xrd.val_12,
1840 l_xrd.val_13,
1841 l_xrd.val_14,
1842 l_xrd.val_15,
1843 l_xrd.val_16,
1844 l_xrd.val_17,
1845 l_xrd.val_18,
1846 l_xrd.val_19,
1847 l_xrd.val_20,
1848 l_xrd.val_21,
1849 l_xrd.val_22,
1850 l_xrd.val_23,
1851 l_xrd.val_24,
1852 l_xrd.val_25,
1853 l_xrd.val_26,
1854 l_xrd.val_27,
1855 l_xrd.val_28,
1856 l_xrd.val_29,
1857 l_xrd.val_30,
1858 l_xrd.val_31,
1859 l_xrd.val_32,
1860 l_xrd.val_33,
1861 l_xrd.val_34,
1862 l_xrd.val_35,
1863 l_xrd.val_36,
1864 l_xrd.val_37,
1865 l_xrd.val_38,
1866 l_xrd.val_39,
1867 l_xrd.val_40,
1868 l_xrd.val_41,
1869 l_xrd.val_42,
1870 l_xrd.val_43,
1871 l_xrd.val_44,
1872 l_xrd.val_45,
1873 l_xrd.val_46,
1874 l_xrd.val_47,
1875 l_xrd.val_48,
1876 l_xrd.val_49,
1877 l_xrd.val_50,
1878 l_xrd.val_51,
1879 l_xrd.val_52,
1880 l_xrd.val_53,
1881 l_xrd.val_54,
1882 l_xrd.val_55,
1883 l_xrd.val_56,
1884 l_xrd.val_57,
1885 l_xrd.val_58,
1886 l_xrd.val_59,
1887 l_xrd.val_60,
1888 l_xrd.val_61,
1889 l_xrd.val_62,
1890 l_xrd.val_63,
1891 l_xrd.val_64,
1892 l_xrd.val_65,
1893 l_xrd.val_66,
1894 l_xrd.val_67,
1895 l_xrd.val_68,
1896 l_xrd.val_69,
1897 l_xrd.val_70,
1898 l_xrd.val_71,
1899 l_xrd.val_72,
1900 l_xrd.val_73,
1901 l_xrd.val_74,
1902 l_xrd.val_75,
1903 l_xrd.val_76,
1904 l_xrd.val_77,
1905 l_xrd.val_78,
1906 l_xrd.val_79,
1907 l_xrd.val_80,
1908 l_xrd.val_81,
1912 l_xrd.val_85,
1909 l_xrd.val_82,
1910 l_xrd.val_83,
1911 l_xrd.val_84,
1913 l_xrd.val_86,
1914 l_xrd.val_87,
1915 l_xrd.val_88,
1916 l_xrd.val_89,
1917 l_xrd.val_90,
1918 l_xrd.val_91,
1919 l_xrd.val_92,
1920 l_xrd.val_93,
1921 l_xrd.val_94,
1922 l_xrd.val_95,
1923 l_xrd.val_96,
1924 l_xrd.val_97,
1925 l_xrd.val_98,
1926 l_xrd.val_99,
1927 l_xrd.val_100,
1928 l_xrd.val_101,
1929 l_xrd.val_102,
1930 l_xrd.val_103,
1931 l_xrd.val_104,
1932 l_xrd.val_105,
1933 l_xrd.val_106,
1934 l_xrd.val_107,
1935 l_xrd.val_108,
1936 l_xrd.val_109,
1937 l_xrd.val_110,
1938 l_xrd.val_111,
1939 l_xrd.val_112,
1940 l_xrd.val_113,
1941 l_xrd.val_114,
1942 l_xrd.val_115,
1943 l_xrd.val_116,
1944 l_xrd.val_117,
1945 l_xrd.val_118,
1946 l_xrd.val_119,
1947 l_xrd.val_120,
1948 l_xrd.val_121,
1949 l_xrd.val_122,
1950 l_xrd.val_123,
1951 l_xrd.val_124,
1952 l_xrd.val_125,
1953 l_xrd.val_126,
1954 l_xrd.val_127,
1955 l_xrd.val_128,
1956 l_xrd.val_129,
1957 l_xrd.val_130,
1958 l_xrd.val_131,
1959 l_xrd.val_132,
1960 l_xrd.val_133,
1961 l_xrd.val_134,
1962 l_xrd.val_135,
1963 l_xrd.val_136,
1964 l_xrd.val_137,
1965 l_xrd.val_138,
1966 l_xrd.val_139,
1967 l_xrd.val_140,
1968 l_xrd.val_141,
1969 l_xrd.val_142,
1970 l_xrd.val_143,
1971 l_xrd.val_144,
1972 l_xrd.val_145,
1973 l_xrd.val_146,
1974 l_xrd.val_147,
1975 l_xrd.val_148,
1976 l_xrd.val_149,
1977 l_xrd.val_150,
1978 l_xrd.val_151,
1979 l_xrd.val_152,
1980 l_xrd.val_153,
1981 l_xrd.val_154,
1982 l_xrd.val_155,
1983 l_xrd.val_156,
1984 l_xrd.val_157,
1985 l_xrd.val_158,
1986 l_xrd.val_159,
1987 l_xrd.val_160,
1988 l_xrd.val_161,
1989 l_xrd.val_162,
1990 l_xrd.val_163,
1991 l_xrd.val_164,
1992 l_xrd.val_165,
1993 l_xrd.val_166,
1994 l_xrd.val_167,
1995 l_xrd.val_168,
1996 l_xrd.val_169,
1997 l_xrd.val_170,
1998 l_xrd.val_171,
1999 l_xrd.val_172,
2000 l_xrd.val_173,
2001 l_xrd.val_174,
2002 l_xrd.val_175,
2003 l_xrd.val_176,
2004 l_xrd.val_177,
2005 l_xrd.val_178,
2006 l_xrd.val_179,
2007 l_xrd.val_180,
2008 l_xrd.val_181,
2009 l_xrd.val_182,
2010 l_xrd.val_183,
2011 l_xrd.val_184,
2012 l_xrd.val_185,
2013 l_xrd.val_186,
2014 l_xrd.val_187,
2015 l_xrd.val_188,
2016 l_xrd.val_189,
2017 l_xrd.val_190,
2018 l_xrd.val_191,
2019 l_xrd.val_192,
2020 l_xrd.val_193,
2021 l_xrd.val_194,
2022 l_xrd.val_195,
2023 l_xrd.val_196,
2024 l_xrd.val_197,
2025 l_xrd.val_198,
2026 l_xrd.val_199,
2027 l_xrd.val_200,
2028 l_xrd.val_201,
2029 l_xrd.val_202,
2030 l_xrd.val_203,
2031 l_xrd.val_204,
2032 l_xrd.val_205,
2033 l_xrd.val_206,
2034 l_xrd.val_207,
2035 l_xrd.val_208,
2036 l_xrd.val_209,
2037 l_xrd.val_210,
2038 l_xrd.val_211,
2039 l_xrd.val_212,
2040 l_xrd.val_213,
2041 l_xrd.val_214,
2042 l_xrd.val_215,
2043 l_xrd.val_216,
2044 l_xrd.val_217,
2045 l_xrd.val_218,
2046 l_xrd.val_219,
2047 l_xrd.val_220,
2048 l_xrd.val_221,
2049 l_xrd.val_222,
2050 l_xrd.val_223,
2051 l_xrd.val_224,
2052 l_xrd.val_225,
2053 l_xrd.val_226,
2054 l_xrd.val_227,
2055 l_xrd.val_228,
2056 l_xrd.val_229,
2057 l_xrd.val_230,
2058 l_xrd.val_231,
2059 l_xrd.val_232,
2060 l_xrd.val_233,
2061 l_xrd.val_234,
2062 l_xrd.val_235,
2063 l_xrd.val_236,
2064 l_xrd.val_237,
2065 l_xrd.val_238,
2066 l_xrd.val_239,
2067 l_xrd.val_240,
2068 l_xrd.val_241,
2069 l_xrd.val_242,
2070 l_xrd.val_243,
2071 l_xrd.val_244,
2072 l_xrd.val_245,
2073 l_xrd.val_246,
2074 l_xrd.val_247,
2075 l_xrd.val_248,
2076 l_xrd.val_249,
2077 l_xrd.val_250,
2078 l_xrd.val_251,
2079 l_xrd.val_252,
2080 l_xrd.val_253,
2081 l_xrd.val_254,
2082 l_xrd.val_255,
2083 l_xrd.val_256,
2084 l_xrd.val_257,
2085 l_xrd.val_258,
2086 l_xrd.val_259,
2087 l_xrd.val_260,
2088 l_xrd.val_261,
2089 l_xrd.val_262,
2090 l_xrd.val_263,
2091 l_xrd.val_264,
2095 l_xrd.val_268,
2092 l_xrd.val_265,
2093 l_xrd.val_266,
2094 l_xrd.val_267,
2096 l_xrd.val_269,
2097 l_xrd.val_270,
2098 l_xrd.val_271,
2099 l_xrd.val_272,
2100 l_xrd.val_273,
2101 l_xrd.val_274,
2102 l_xrd.val_275,
2103 l_xrd.val_276,
2104 l_xrd.val_277,
2105 l_xrd.val_278,
2106 l_xrd.val_279,
2107 l_xrd.val_280,
2108 l_xrd.val_281,
2109 l_xrd.val_282,
2110 l_xrd.val_283,
2111 l_xrd.val_284,
2112 l_xrd.val_285,
2113 l_xrd.val_286,
2114 l_xrd.val_287,
2115 l_xrd.val_288,
2116 l_xrd.val_289,
2117 l_xrd.val_290,
2118 l_xrd.val_291,
2119 l_xrd.val_292,
2120 l_xrd.val_293,
2121 l_xrd.val_294,
2122 l_xrd.val_295,
2123 l_xrd.val_296,
2124 l_xrd.val_297,
2125 l_xrd.val_298,
2126 l_xrd.val_299,
2127 l_xrd.val_300,
2128 l_xrd.seq_num);
2129 --
2130
2131
2132 -- if person cahnged then close the record set too
2133 if l_old_person_id <> l_xrd.person_id then
2134 if l_new_rec <> -1 then
2135 l_new_rec := -2 ;
2136 end if ;
2137 end if ;
2138
2139
2140
2141
2142 p_rec_count := p_rec_count + 1 ;
2143
2144 -- for low level grouping every record start with record element not record set element
2145 if l_Low_lvl_grouping = 'N' then
2146 if l_new_rec <> l_xrd.ext_rcd_id then
2147 -- closing the element for previous record
2148 if l_new_rec <> -1 then
2149 hr_utility.set_location(' close record ' || l_ext_rcd.xml_tag_name , 99 );
2150
2151 if l_cm_display_flag = 'Y' then
2152 fnd_file.put_line(fnd_file.OUTPUT, '</'||nvl(l_ext_rcd.xml_tag_name,l_rcd_defa_tag)||'>');
2153 else
2154 utl_file.put_line(file_handle, '</'||nvl(l_ext_rcd.xml_tag_name,l_rcd_defa_tag)||'>');
2155 end if ;
2156
2157 --utl_file.put_line(file_handle, '</'||nvl(l_ext_rcd.xml_tag_name,l_rcd_defa_tag)||'>');
2158 ---- whether close the lowlevel code
2159 end if ;
2160 end if ;
2161 end if ;
2162 -- person level loop shoyld be closed after the record level loop
2163 if l_old_person_id <> l_xrd.person_id then
2164 if l_old_person_id <> -1 then
2165 --- close the lowlevel code
2166
2167 if l_Low_lvl_grouping = 'Y' then
2168
2169 l_prev_levl_found := 'N' ;
2170 for j in REVERSE 1 .. g_low_lvl_tbl.count loop
2171 --- whne the person level complete exist
2172 --- or reach the sub group
2173 if l_prv_low_lvl_cd = 'P' and g_low_lvl_tbl(j) <> 'P'
2174 or ( g_low_lvl_tbl(j) in ('PO','OR','PY','JB','LO') and
2175 nvl(l_xrd.group_val_01 ,'-1') = nvl(g_prev_grop_val,'-1') and
2176 l_xrd.group_val_01 <> ' ' )
2177 then
2178 hr_utility.set_location(' new person ' || g_low_lvl_tbl(j) , 99 );
2179 l_prv_low_lvl_cd := g_low_lvl_tbl(j) ;
2180 l_prev_levl_found := 'Y' ;
2181 exit ;
2182 end if ;
2183 l_prv_low_lvl_cd := g_low_lvl_tbl(j) ;
2184 l_low_lvl_name := get_low_lvl_name(l_prv_low_lvl_cd) ;
2185 hr_utility.set_location(' close level ' || l_low_lvl_name , 99 );
2186 l_var:= '</'||l_low_lvl_name || '> ' ;
2187 --utl_file.put_line(file_handle, l_var );
2188 if l_cm_display_flag = 'Y' then
2189 fnd_file.put_line(fnd_file.OUTPUT, l_var);
2190 else
2191 utl_file.put_line(file_handle, l_var);
2192 end if ;
2193 add_delete_sub_level('DELETE' , l_prv_low_lvl_cd ) ;
2194 end loop ;
2195 --- if previous level si 'p' assume the loop completed
2196 --- so clear the variables
2197 if l_prev_levl_found = 'N' then
2198 g_low_lvl_tbl.delete ;
2199 l_prv_low_lvl_cd := null ;
2200 end if ;
2201 else
2202 -- l_Low_lvl_grouping
2203 --utl_file.put_line(file_handle, '</'||'oabext_person_record_set' ||'>');
2204 if l_cm_display_flag = 'Y' then
2205 fnd_file.put_line(fnd_file.OUTPUT, '</'||'oabext_person_record_set' ||'>' );
2206 else
2207 utl_file.put_line(file_handle, '</'||'oabext_person_record_set' ||'>' );
2208 end if ;
2209 end if ;
2210 end if ;
2211 end if ;
2212
2213
2214
2215 -- person level starts before the record level
2216 -- person level loop
2217 if l_old_person_id <> l_xrd.person_id then
2218 --get the person name
2219 open c_per_info(l_xrd.person_id,l_ext_file.eff_dt) ;
2220 fetch c_per_info into l_name, l_employee_number ;
2224 -- '" employee_number="'|| l_employee_number||'">');
2221 close c_per_info ;
2222 if l_Low_lvl_grouping = 'N' then
2223 --utl_file.put_line(file_handle, '<'||'oabext_person_record_set'||' person_name="'||ext_xml_encode(l_name)||
2225 if l_cm_display_flag = 'Y' then
2226 fnd_file.put_line(fnd_file.OUTPUT, '<'||'oabext_person_record_set'||' person_name="'||ext_xml_encode(l_name)||
2227 '" employee_number="'|| l_employee_number||'">');
2228 else
2229 utl_file.put_line(file_handle, '<'||'oabext_person_record_set'||' person_name="'||ext_xml_encode(l_name)||
2230 '" employee_number="'|| l_employee_number||'">');
2231 end if ;
2232
2233 end if ;
2234 end if ;
2235 l_old_person_id := l_xrd.person_id ;
2236
2237
2238
2239 -- start the record level after the person level
2240 if l_new_rec <> l_xrd.ext_rcd_id then
2241 --- dont change the record for people change
2242 if l_new_rec <> -2 then
2243 l_rec_num := 0 ;
2244 end if ;
2245
2246 hr_utility.set_location(' new record ' || l_xrd.ext_rcd_id , 99 );
2247 l_new_rec := l_xrd.ext_rcd_id ;
2248
2249 --- Get the Extract Record Information
2250 open c_ext_rcd( l_ext_file.ext_file_id ,
2251 l_xrd.ext_rcd_id );
2252 fetch c_ext_rcd into l_ext_rcd ;
2253 close c_ext_rcd ;
2254
2255
2256 --- Skip the record which has hide attribute
2257 if nvl(l_ext_rcd.hide_flag,'N') <> 'Y' then
2258 if l_Low_lvl_grouping = 'Y' then
2259 --- decode to start the lowlevel
2260 -- when the previous level is not the same
2261 if nvl(l_prv_low_lvl_cd,'-1') <> l_ext_rcd.low_lvl_cd then
2262 l_low_lvl_name := get_low_lvl_name(l_ext_rcd.low_lvl_cd) ;
2263 -- current level is sub group of previous levle
2264 if ( determine_sub_low_lvl (l_prv_low_lvl_cd,l_ext_rcd.low_lvl_cd, l_xrd.group_val_01)) = 'Y' then
2265
2266 -- for every person or first record get the person infromation as attribute
2267 if l_prv_low_lvl_cd is null then
2268 l_var := '<'||l_low_lvl_name || ' Low_Level_Code="' ||l_ext_rcd.low_lvl_cd||
2269 '" person_name="'||ext_xml_encode(l_name)||
2270 '" employee_number="'|| l_employee_number|| '"> ' ;
2271 else
2272 l_low_lvl_name := get_low_lvl_name(l_ext_rcd.low_lvl_cd) ;
2273 l_var := '<'||l_low_lvl_name || ' Low_Level_Code="' ||l_ext_rcd.low_lvl_cd || '"> ' ;
2274 end if ;
2275
2276 --utl_file.put_line(file_handle, l_var );
2277 if l_cm_display_flag = 'Y' then
2278 fnd_file.put_line(fnd_file.OUTPUT, l_var);
2279 else
2280 utl_file.put_line(file_handle, l_var);
2281 end if ;
2282
2283 add_delete_sub_level('ADD' , l_ext_rcd.low_lvl_cd ) ;
2284 l_prv_low_lvl_cd := l_ext_rcd.low_lvl_cd ;
2285
2286 else
2287 -- when the level not the same and not subgroup
2288 -- close the element till it find the same or subgroup
2289 -- close the previous level
2290 hr_utility.set_location('closing previous level' || l_prv_low_lvl_cd , 99 );
2291 l_prev_levl_found := 'N' ;
2292 for j in REVERSE 1 .. g_low_lvl_tbl.count loop
2293
2294 l_prv_low_lvl_cd := g_low_lvl_tbl(j) ;
2295 hr_utility.set_location(' prv '||l_prv_low_lvl_cd|| ' curr '|| l_ext_rcd.low_lvl_cd , 99 ) ;
2296 if l_prv_low_lvl_cd = l_ext_rcd.low_lvl_cd and
2297 nvl(l_xrd.group_val_01,'-1') = nvl(g_prev_grop_val,'-1') then
2298 -- close the previous element and open a new element
2299 l_low_lvl_name := get_low_lvl_name(l_ext_rcd.low_lvl_cd) ;
2300 l_var:= '</'||l_low_lvl_name || '> ' ;
2301 --utl_file.put_line(file_handle, l_var );
2302 if l_cm_display_flag = 'Y' then
2303 fnd_file.put_line(fnd_file.OUTPUT, l_var);
2304 else
2305 utl_file.put_line(file_handle, l_var);
2306 end if ;
2307 l_low_lvl_name := get_low_lvl_name(l_ext_rcd.low_lvl_cd) ;
2308 l_var := '<'||l_low_lvl_name || ' Low_Level_Code="'|| l_ext_rcd.low_lvl_cd || '"> ' ;
2309 --utl_file.put_line(file_handle, l_var );
2310 if l_cm_display_flag = 'Y' then
2311 fnd_file.put_line(fnd_file.OUTPUT, l_var);
2312 else
2313 utl_file.put_line(file_handle, l_var);
2314 end if ;
2315
2316 l_prev_levl_found := 'Y' ;
2317 exit ;
2318
2319 elsif (determine_sub_low_lvl(l_prv_low_lvl_cd,l_ext_rcd.low_lvl_cd,l_xrd.group_val_01)) ='Y' then
2323 if l_cm_display_flag = 'Y' then
2320 l_low_lvl_name := get_low_lvl_name(l_ext_rcd.low_lvl_cd) ;
2321 l_var := '<'||l_low_lvl_name || ' Low_Level_Code="'|| l_ext_rcd.low_lvl_cd || '"> ' ;
2322 --utl_file.put_line(file_handle, l_var );
2324 fnd_file.put_line(fnd_file.OUTPUT, l_var);
2325 else
2326 utl_file.put_line(file_handle, l_var);
2327 end if ;
2328 add_delete_sub_level('ADD' , l_ext_rcd.low_lvl_cd ) ;
2329 l_prv_low_lvl_cd := l_ext_rcd.low_lvl_cd ;
2330 l_prev_levl_found := 'Y' ;
2331 exit ;
2332 else
2333 add_delete_sub_level('DELETE' , l_prv_low_lvl_cd ) ;
2334 l_low_lvl_name := get_low_lvl_name(l_prv_low_lvl_cd) ;
2335 hr_utility.set_location( ' closingi prv levl ' || l_low_lvl_name , 99 ) ;
2336 l_var:= '</'||l_low_lvl_name || '> ' ;
2337 --utl_file.put_line(file_handle, l_var );
2338 if l_cm_display_flag = 'Y' then
2339 fnd_file.put_line(fnd_file.OUTPUT, l_var);
2340 else
2341 utl_file.put_line(file_handle, l_var);
2342 end if ;
2343
2344 end if ;
2345 end loop ;
2346 --- if the previous level not found then it is the new level
2347 if l_prev_levl_found = 'N' then
2348 l_low_lvl_name := get_low_lvl_name(l_ext_rcd.low_lvl_cd) ;
2349 l_var := '<'||l_low_lvl_name || ' Low_Level_Code="'|| l_ext_rcd.low_lvl_cd || '"> ' ;
2350 --utl_file.put_line(file_handle, l_var );
2351 if l_cm_display_flag = 'Y' then
2352 fnd_file.put_line(fnd_file.OUTPUT, l_var);
2353 else
2354 utl_file.put_line(file_handle, l_var);
2355 end if ;
2356 add_delete_sub_level('ADD' , l_ext_rcd.low_lvl_cd ) ;
2357 l_prv_low_lvl_cd := l_ext_rcd.low_lvl_cd ;
2358 end if ;
2359
2360
2361 end if ;
2362 end if ;
2363 -- l_Low_lvl_grouping
2364 else
2365 ---
2366
2367 l_var:= '<'||nvl(l_ext_rcd.xml_tag_name,l_rcd_defa_tag)||' ext_rec_name="'||l_ext_rcd.name || '" '||
2368 'ext_rec_seq="' || l_ext_rcd.seq_num || '" '||
2369 'ext_rec_type="'||l_ext_rcd.rcd_type_cd || '" '||
2370 'ext_rec_person="'||l_xrd.person_id || '" > ' ;
2371 --utl_file.put_line(file_handle, l_var );
2372 if l_cm_display_flag = 'Y' then
2373 fnd_file.put_line(fnd_file.OUTPUT, l_var);
2374 else
2375 utl_file.put_line(file_handle, l_var);
2376 end if ;
2377 load_tags(p_tag_table => g_rcd_tag_tbl ,
2378 p_tag => nvl(l_ext_rcd.xml_tag_name,l_rcd_defa_tag)
2379 ) ;
2380 end if ;
2381 end if ;
2382 end if ;
2383
2384 --- extract the detail only when the hide flag is 'N'
2385 if nvl(l_ext_rcd.hide_flag,'N') <> 'Y' then
2386 ---- required columns information taken from Redord layout definition
2387 l_rec_num := l_rec_num + 1 ;
2388 -- for pdf there is no need for subgrouping under record
2389 if l_Low_lvl_grouping = 'Y' then
2390 l_var:= '<'||nvl(l_ext_rcd.xml_tag_name,l_rcd_defa_tag)|| '>' ;
2391 else
2392 l_var:= '<oabext_record_set record_number=" ' || l_rec_num ||'">' ;
2393 end if ;
2394 --utl_file.put_line(file_handle, l_var);
2395 if l_cm_display_flag = 'Y' then
2396 fnd_file.put_line(fnd_file.OUTPUT, l_var);
2397 else
2398 utl_file.put_line(file_handle, l_var);
2399 end if ;
2400 --
2401
2402 open c_elmt (l_xrd.ext_rcd_id) ;
2403 loop
2404 fetch c_elmt into l_elm_name ,
2405 l_elm_xml_tag_name,
2406 l_elmt_seq_num ,
2407 l_elm_typ_cd ,
2408 l_elm_max_length,
2409 l_elm_frmt_mask ,
2410 l_elm_just_cd ;
2411 exit when c_elmt%notfound ;
2412
2413 l_elm_val := ben_ext_write.g_val(l_elmt_seq_num) ;
2414
2415
2416 l_var := '<'||nvl(l_elm_xml_tag_name,l_elmt_defa_tag) ;
2417 -- dont get the attrib for PDF output , confuse the user to define the template
2418 if l_Low_lvl_grouping = 'N' then
2419
2420 l_var := l_var ||' ext_elmt_name="' || l_elm_name || '" '||
2421 'ext_elmt_seq="' || l_elmt_seq_num || '" '||
2422 'ext_elmt_type="' || l_elm_typ_cd || '" '||
2423 'ext_elmt_length_="'|| l_elm_max_length || '" '||
2424 'ext_elmt_format="' || l_elm_frmt_mask || '" '||
2425 'ext_elmt_just="' || l_elm_just_cd ||'" ' ;
2426 end if ;
2427
2431 --utl_file.put_line(file_handle, l_var );
2428 l_var := l_var|| '>'||l_elm_val||'</'||nvl(l_elm_xml_tag_name,l_elmt_defa_tag)||'>';
2429 --
2430
2432 if l_cm_display_flag = 'Y' then
2433 fnd_file.put_line(fnd_file.OUTPUT, l_var);
2434 else
2435 utl_file.put_line(file_handle, l_var);
2436 end if ;
2437
2438 Load_tags(p_tag_table => g_elmt_tag_tbl ,
2439 p_tag => nvl(l_elm_xml_tag_name,l_elmt_defa_tag)
2440 ) ;
2441
2442 end loop ;
2443 close c_elmt ;
2444
2445 if l_Low_lvl_grouping = 'Y' then
2446 l_var:= '</'||nvl(l_ext_rcd.xml_tag_name,l_rcd_defa_tag)||'>' ;
2447 else
2448 l_var:= '</oabext_record_set>' ;
2449 end if ;
2450
2451 --utl_file.put_line(file_handle,l_var );
2452 if l_cm_display_flag = 'Y' then
2453 fnd_file.put_line(fnd_file.OUTPUT, l_var);
2454 else
2455 utl_file.put_line(file_handle, l_var);
2456 end if ;
2457 end if ;
2458
2459 end loop ;
2460 --- Close the elements
2461 hr_utility.set_location ( ' out of loop ' , 99 ) ;
2462
2463 if l_Low_lvl_grouping = 'Y' then
2464 for j in REVERSE 1 .. g_low_lvl_tbl.count loop
2465 l_prv_low_lvl_cd := g_low_lvl_tbl(j) ;
2466 l_low_lvl_name := get_low_lvl_name(l_prv_low_lvl_cd) ;
2467 l_var:= '</'||l_low_lvl_name || '> ' ;
2468 --utl_file.put_line(file_handle, l_var );
2469 if l_cm_display_flag = 'Y' then
2470 fnd_file.put_line(fnd_file.OUTPUT, l_var);
2471 else
2472 utl_file.put_line(file_handle, l_var);
2473 end if ;
2474 end loop ;
2475 g_low_lvl_tbl.delete ;
2476 else
2477 --utl_file.put_line(file_handle, '</'||nvl(l_ext_rcd.xml_tag_name,l_rcd_defa_tag)||'>');
2478 if l_cm_display_flag = 'Y' then
2479 fnd_file.put_line(fnd_file.OUTPUT, '</'||nvl(l_ext_rcd.xml_tag_name,l_rcd_defa_tag)||'>');
2480 fnd_file.put_line(fnd_file.OUTPUT, '</'||'oabext_person_record_set' ||'>' );
2481 else
2482 utl_file.put_line(file_handle, '</'||nvl(l_ext_rcd.xml_tag_name,l_rcd_defa_tag)||'>');
2483 utl_file.put_line(file_handle, '</'||'oabext_person_record_set' ||'>');
2484 end if ;
2485 -- l_Low_lvl_grouping
2486 --utl_file.put_line(file_handle, '</'||'oabext_person_record_set' ||'>');
2487 end if ;
2488
2489 --utl_file.put_line(file_handle,l_var );
2490 if l_cm_display_flag = 'Y' then
2491 fnd_file.put_line(fnd_file.OUTPUT, '</'||nvl(l_ext_file.file_xml_tag_name,l_file_defa_tag)||'>' );
2492 --fnd_file.put_line(fnd_file.OUTPUT, '</ext:'|| nvl(l_ext_file.xml_tag_name,l_dfn_defa_tag)||'>');
2493 if p_output_type <> 'X' then
2494 fnd_file.put_line(fnd_file.OUTPUT, '</'|| nvl(l_ext_file.xml_tag_name,l_dfn_defa_tag)||'>' );
2495 end if ;
2496 else
2497 utl_file.put_line(file_handle, '</'||nvl(l_ext_file.file_xml_tag_name,l_file_defa_tag)||'>');
2498 --utl_file.put_line(file_handle, '</ext:'|| nvl(l_ext_file.xml_tag_name,l_dfn_defa_tag)||'>');
2499 utl_file.put_line(file_handle, '</'|| nvl(l_ext_file.xml_tag_name,l_dfn_defa_tag)||'>');
2500 utl_file.fclose(file_handle);
2501 end if;
2502
2503
2504 if p_output_type <> 'X' and l_cm_display_flag <> 'Y' then --- pdf
2505
2506
2507
2508
2509
2510 hr_utility.set_location( ' calling xdo ', 99 );
2511 write_xdo_pdf
2512 (p_drctry_name => l_directory_name ,
2513 p_pdf_output_name => l_pdf_output_name,
2514 p_input_name => l_output_name,
2515 p_template_id => l_xdo_template_id,
2516 p_output_type => p_output_type
2517 --p_cm_display_flag => l_cm_display_flag
2518 );
2519 else
2520
2521 ---- Calling the function write the schema file in the current directory
2522 if l_cm_display_flag <> 'Y' then
2523 write_schema
2524 (p_drctry_name => l_directory_name,
2525 p_file_name => l_file_name,
2526 p_ext_rslt_id => p_ext_rslt_id,
2527 p_ext_dfn_tag => nvl(l_ext_file.xml_tag_name,l_dfn_defa_tag),
2528 p_ext_file_tag => nvl(l_ext_file.file_xml_tag_name,l_file_defa_tag)) ;
2529
2530
2531 write_style_sheet
2532 (p_drctry_name => l_directory_name,
2533 p_file_name => l_file_name,
2534 p_ext_rslt_id => p_ext_rslt_id,
2535 p_ext_dfn_tag => nvl(l_ext_file.xml_tag_name,l_dfn_defa_tag),
2536 p_ext_file_tag => nvl(l_ext_file.file_xml_tag_name,l_file_defa_tag)) ;
2537 end if ;
2538 end if ;
2539
2540 /*
2541 if p_cm_display_flag = 'Y' then
2542 update fnd_concurrent_requests
2543 set output_file_type = 'XML'
2544 where request_id = fnd_global.conc_request_id ;
2545 commit ;
2546 end if ;
2547 */
2548
2549
2550 hr_utility.set_location('Exiting'||l_proc, 15);
2551 --
2552 --
2553 EXCEPTION
2554 --
2555 WHEN utl_file.invalid_path then
2556 fnd_message.set_name('BEN', 'BEN_92254_UTL_INVLD_PATH');
2557 fnd_file.put_line(fnd_file.log, fnd_message.get);
2558 fnd_message.raise_error;
2559 --
2560 WHEN utl_file.invalid_mode then
2564 --
2561 fnd_message.set_name('BEN', 'BEN_92249_UTL_INVLD_MODE');
2562 fnd_file.put_line(fnd_file.log, fnd_message.get);
2563 fnd_message.raise_error;
2565 WHEN utl_file.invalid_filehandle then
2566 fnd_message.set_name('BEN', 'BEN_92250_UTL_INVLD_FILEHANDLE');
2567 fnd_file.put_line(fnd_file.log, fnd_message.get);
2568 fnd_message.raise_error;
2569 --
2570 WHEN utl_file.invalid_operation then
2571 fnd_message.set_name('BEN', 'BEN_92251_UTL_INVLD_OPER');
2572 fnd_file.put_line(fnd_file.log, fnd_message.get);
2573 fnd_message.raise_error;
2574 --
2575 WHEN utl_file.read_error then
2576 fnd_message.set_name('BEN', 'BEN_92252_UTL_READ_ERROR');
2577 fnd_file.put_line(fnd_file.log, fnd_message.get);
2578 fnd_message.raise_error;
2579 --
2580
2581 WHEN others THEN
2582 hr_utility.set_location( 'other exception raised ' , 99 ) ;
2583 fnd_message.set_name('PER','FFU10_GENERAL_ORACLE_ERROR');
2584 fnd_message.set_token('2',substr(sqlerrm,1,200));
2585 fnd_file.put_line(fnd_file.log, fnd_message.get);
2586 fnd_message.raise_error;
2587 --
2588 END ;
2589 --
2590 END;