[Home] [Help]
PACKAGE BODY: APPS.HR_H2PI_UPLOAD
Source
1 PACKAGE BODY hr_h2pi_upload AS
2 /* $Header: hrh2piul.pkb 120.0 2005/05/31 00:41:59 appldev noship $*/
3
4 g_package VARCHAR2(33) := ' hr_h2pi_upload.';
5 --
6 -- --------------------------------------------------------------------------------
7 -- Description: Procedure to upload data into a H2PI data tables using
8 -- XML to SQL Utility (XSU).
9 -- --------------------------------------------------------------------------------
10 --
11 procedure insert_xml_into_table (p_table_name in varchar2,
12 p_locator in clob) is
13
14 l_saveCtx DBMS_XMLSave.CtxType;
15 l_rows number(15);
16 l_length number(15);
17 l_proc varchar2(73) := g_package || 'insert_xml_into_table';
18
19 BEGIN
20 hr_utility.set_location('Entering:'|| l_proc, 10);
21 if dbms_lob.getlength(p_locator) > length('</' || p_table_name|| '>') + length('<?xml version = ''1.0''?') + 10 then
22 l_saveCtx := DBMS_XMLSave.newContext(p_table_name);
23 DBMS_XMLSave.setDateFormat(l_saveCtx,null);
24 l_rows := DBMS_XMLSave.insertXML(l_saveCtx,p_locator);
25 DBMS_XMLSave.closeContext(l_saveCtx);
26 end if;
27 --
28 hr_utility.set_location('Leaving:'|| l_proc, 20);
29 END insert_xml_into_table;
30
31 function get_clob_locator (p_table_name in varchar2) return clob is
32
33 TYPE l_clob_rec_type is RECORD
34 (table_name varchar2(30),
35 xmldoc clob);
36
37 l_clob_rec l_clob_rec_type;
38 l_xmldoc_loc clob;
39 l_proc varchar2(73) := g_package || 'get_clob_locator';
40
41 BEGIN
42 hr_utility.set_location('Entering:'|| l_proc, 10);
43
44 l_clob_rec.table_name := p_table_name;
45 l_clob_rec.xmldoc := null;
46 l_xmldoc_loc := l_clob_rec.xmldoc;
47 hr_utility.set_location('Leaving:'|| l_proc, 20);
48 return l_xmldoc_loc;
49
50 EXCEPTION
51 when no_data_found then
52 null;
53 when others then
54 null;
55 END;
56
57 /*
58 FUNCTION get_from_business_group_id RETURN NUMBER IS
59
60 g_package VARCHAR2(33) := ' hr_h2pi_bg_upload.';
61 l_from_business_group_id NUMBER(15);
62 l_proc VARCHAR2(72) := g_package||'get_from_business_group_id';
63
64 BEGIN
65 hr_utility.set_location('Entering:'|| l_proc, 10);
66 l_from_business_group_id := hr_h2pi_map.get_from_id
67 (p_table_name => 'HR_ALL_ORGANIZATION_UNITS',
68 p_to_id => hr_h2pi_upload.g_to_business_group_id);
69 IF l_from_business_group_id = -1 THEN
70 hr_utility.set_location(l_proc, 20);
71 hr_h2pi_error.data_error
72 (p_from_id => hr_h2pi_upload.g_to_business_group_id,
73 p_table_name => 'HR_H2PI_BG_AND_GRE',
74 p_message_level => 'FATAL',
75 p_message_name => 'HR_289241_MAPPING_ID_MISSING');
76 END IF;
77
78 hr_utility.set_location('Leaving:'|| l_proc, 30);
79 RETURN l_from_business_group_id;
80 END;
81 */
82
83 FUNCTION get_from_client_id RETURN NUMBER IS
84
85 g_package VARCHAR2(33) := ' hr_h2pi_upload.';
86 l_from_client_id VARCHAR2(60);
87 l_proc VARCHAR2(72) := g_package||'get_from_client_id';
88
89 BEGIN
90 hr_utility.set_location('Entering:'|| l_proc, 10);
91 -- special case of getting from client id
92 l_from_client_id := hr_h2pi_map.get_from_id
93 (p_table_name => 'CLIENT_ID',
94 p_to_id => hr_h2pi_upload.g_to_business_group_id);
95 IF l_from_client_id = -1 THEN
96 hr_utility.set_location(l_proc, 20);
97 hr_h2pi_error.data_error
98 (p_from_id => hr_h2pi_upload.g_to_business_group_id,
99 p_table_name => 'HR_H2PI_BG_AND_GRE',
100 p_message_level => 'FATAL',
101 p_message_name => 'HR_289241_MAPPING_ID_MISSING');
102 END IF;
103
104 hr_utility.set_location('Leaving:'|| l_proc, 30);
105 RETURN l_from_client_id;
106 END;
107
108
109 procedure upload (p_errbuf OUT NOCOPY VARCHAR2,
110 p_retcode OUT NOCOPY NUMBER,
111 p_file_name IN VARCHAR2) IS
112
113 l_fp UTL_FILE.file_type;
114 l_line varchar2(32767);
115 l_text varchar2(32767);
116
117 l_xmldoc clob;
118 l_rows number(15);
119 l_file_name varchar2(30):= 'h2i_upload';
120 l_dest_clob_loc clob;
121 l_proc varchar2(72) := g_package||'upload';
122
123 e_in_upload EXCEPTION ;
124 PRAGMA Exception_Init(e_in_upload, -20001);
125 l_message varchar2(240);
126 --
127
128 BEGIN
129 hr_utility.set_location('Entering:'|| l_proc, 10);
130
131 -- check for previous incomplete uploads
132 /*
133 BEGIN
134 if check_incomplete_upload then
135 RAISE e_in_upload;
136 end if;
137 EXCEPTION
138 when e_in_upload then
139 fnd_message.set_name('PER','HR_289277_INCOMPLETE_UPLOAD');
140 --l_message := fnd_message.get_string('PER','HR_289277_INCOMPLETE_UPLOAD');
141 --fnd_file.put_line(FND_FILE.LOG,l_message);
142 fnd_message.raise_error;
143 END;
144 */
145
146 l_fp := UTL_FILE.fopen(FND_PROFILE.VALUE('PER_H2PI_DATA_UPLOAD_DIRECTORY'),p_file_name,'r');
147
148 --
149 -- FOR HR_H2PI_EMPLOYEES table.
150 --
151 l_dest_clob_loc := get_clob_locator('HR_H2PI_EMPLOYEES');
152 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
153 --
154 hr_utility.set_location(l_proc, 20);
155 <<hr_h2pi_employees>>
156 loop
157 utl_file.get_line(l_fp,l_line);
158 l_text := l_line;
159 if l_text is not null then
160 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
161 end if;
162 if (l_text = '</HR_H2PI_EMPLOYEES>')
163 OR (l_text = '<HR_H2PI_EMPLOYEES/>') then
164 exit ;
165 end if;
166 end loop hr_h2pi_employees;
167 --
168 insert_xml_into_table('HR_H2PI_EMPLOYEES',l_dest_clob_loc);
169 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
170 --
171
172 --
173 -- FOR HR_H2PI_ADDRESSES table.
174 --
175 l_dest_clob_loc := get_clob_locator('HR_H2PI_ADDRESSES');
176 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
177 --
178 hr_utility.set_location('Entering:'|| l_proc, 30);
179 <<hr_h2pi_addresses>>
180 loop
181 utl_file.get_line(l_fp,l_line);
182 l_text := l_line;
183 if l_text is not null then
184 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
185 end if;
186 if (l_text = '</HR_H2PI_ADDRESSES>')
187 OR (l_text = '<HR_H2PI_ADDRESSES/>') then
188 exit ;
189 end if;
190 end loop hr_h2pi_addresses;
191 --
192 insert_xml_into_table('HR_H2PI_ADDRESSES',l_dest_clob_loc);
193 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
194 --
195
196 --
197 -- FOR HR_H2PI_LOCATIONS table.
198 --
199 l_dest_clob_loc := get_clob_locator('HR_H2PI_LOCATIONS');
200 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
201 --
202 hr_utility.set_location('Entering:'|| l_proc, 40);
203 <<hr_h2pi_locations>>
204 loop
205 utl_file.get_line(l_fp,l_line);
206 l_text := l_line;
207 if l_text is not null then
208 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
209 end if;
210 if (l_text = '</HR_H2PI_LOCATIONS>')
211 OR (l_text = '<HR_H2PI_LOCATIONS/>') then
212 exit ;
213 end if;
214 end loop hr_h2pi_addresses;
215 --
216 insert_xml_into_table('HR_H2PI_LOCATIONS',l_dest_clob_loc);
217 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
218 --
219
220 --
221 -- FOR HR_H2PI_ASSIGNMENTS table.
222 --
223 l_dest_clob_loc := get_clob_locator('HR_H2PI_ASSIGNMENTS');
224 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
225 --
226 hr_utility.set_location('Entering:'|| l_proc, 50);
227 <<hr_h2pi_assignments>>
228 loop
229 utl_file.get_line(l_fp,l_line);
230 l_text := l_line;
231 if l_text is not null then
232 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
233 end if;
234 if (l_text = '</HR_H2PI_ASSIGNMENTS>')
235 OR (l_text = '<HR_H2PI_ASSIGNMENTS/>') then
236 exit ;
237 end if;
238 end loop hr_h2pi_addresses;
239 --
240 insert_xml_into_table('HR_H2PI_ASSIGNMENTS',l_dest_clob_loc);
241 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
242
243 --
244 -- FOR HR_H2PI_PAY_BASES table.
245 --
246 l_dest_clob_loc := get_clob_locator('HR_H2PI_PAY_BASES');
247 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
248 --
249 hr_utility.set_location('Entering:'|| l_proc, 60);
250 <<hr_h2pi_pay_bases>>
251 loop
252 utl_file.get_line(l_fp,l_line);
253 l_text := l_line;
254 if l_text is not null then
255 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
256 end if;
257 if (l_text = '</HR_H2PI_PAY_BASES>')
258 OR (l_text = '<HR_H2PI_PAY_BASES/>') then
259 exit ;
260 end if;
261 end loop hr_h2pi_pay_bases;
262 --
263 insert_xml_into_table('HR_H2PI_PAY_BASES',l_dest_clob_loc);
264 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
265
266 --
267 -- FOR HR_H2PI_HR_ORGANIZATIONS table.
268 --
269 l_dest_clob_loc := get_clob_locator('HR_H2PI_HR_ORGANIZATIONS');
270 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
271 --
272 hr_utility.set_location('Entering:'|| l_proc, 70);
273 <<hr_h2pi_HR_organizations>>
274 loop
275 utl_file.get_line(l_fp,l_line);
276 l_text := l_line;
277 if l_text is not null then
278 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
279 end if;
280 if l_text = '</HR_H2PI_HR_ORGANIZATIONS>' then exit ; end if;
281 if (l_text = '</HR_H2PI_HR_ORGANIZATIONS>')
282 OR (l_text = '<HR_H2PI_HR_ORGANIZATIONS/>') then
283 exit ;
284 end if;
285 end loop hr_h2pi_hr_organizations;
286 --
287 insert_xml_into_table('HR_H2PI_HR_ORGANIZATIONS',l_dest_clob_loc);
288 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
289
290
291 --
292 -- FOR HR_H2PI_PAYROLLS table.
293 --
294 l_dest_clob_loc := get_clob_locator('HR_H2PI_PAYROLLS');
295 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
296 --
297 hr_utility.set_location('Entering:'|| l_proc, 80);
298 <<hr_h2pi_payrolls>>
299 loop
300 utl_file.get_line(l_fp,l_line);
301 l_text := l_line;
302 if l_text is not null then
303 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
304 end if;
305 if (l_text = '</HR_H2PI_PAYROLLS>')
306 OR (l_text = '<HR_H2PI_PAYROLLS/>') then
307 exit ;
308 end if;
309 end loop hr_h2pi_payrolls;
310 --
311 insert_xml_into_table('HR_H2PI_PAYROLLS',l_dest_clob_loc);
312 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
313
314
315 --
316 -- FOR HR_H2PI_ELEMENT_TYPES table.
317 --
318 l_dest_clob_loc := get_clob_locator('HR_H2PI_ELEMENT_TYPES');
319 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
320 --
321 hr_utility.set_location('Entering:'|| l_proc, 90);
322 <<hr_h2pi_element_types>>
323 loop
324 utl_file.get_line(l_fp,l_line);
325 l_text := l_line;
326 if l_text is not null then
327 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
328 end if;
329 if (l_text = '</HR_H2PI_ELEMENT_TYPES>')
330 OR (l_text = '<HR_H2PI_ELEMENT_TYPES/>') then
331 exit ;
332 end if;
333 end loop hr_h2pi_element_types;
334 --
335 insert_xml_into_table('HR_H2PI_ELEMENT_TYPES',l_dest_clob_loc);
336 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
337 --
338
339 --
340 -- FOR HR_H2PI_INPUT_VALUES table.
341 --
342 l_dest_clob_loc := get_clob_locator('HR_H2PI_INPUT_VALUES');
343 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
344 --
345 hr_utility.set_location('Entering:'|| l_proc, 100);
346 <<hr_h2pi_input_values>>
347 loop
348 utl_file.get_line(l_fp,l_line);
349 l_text := l_line;
350 if l_text is not null then
351 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
352 end if;
353 if (l_text = '</HR_H2PI_INPUT_VALUES>')
354 OR (l_text = '<HR_H2PI_INPUT_VALUES/>') then
355 exit ;
356 end if;
357 end loop hr_h2pi_input_values;
358 --
359 insert_xml_into_table('HR_H2PI_INPUT_VALUES',l_dest_clob_loc);
360 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
361 --
362
363 --
364 -- FOR HR_H2PI_ELEMENT_LINKS table.
365 --
366 l_dest_clob_loc := get_clob_locator('HR_H2PI_ELEMENT_LINKS');
367 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
368 hr_utility.set_location('Entering:'|| l_proc,110);
369 --
370 <<hr_h2pi_element_links>>
371 loop
372 utl_file.get_line(l_fp,l_line);
373 l_text := l_line;
374 if l_text is not null then
375 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
376 end if;
377 if (l_text = '</HR_H2PI_ELEMENT_LINKS>')
378 OR (l_text = '<HR_H2PI_ELEMENT_LINKS/>') then
379 exit ;
380 end if;
381 end loop hr_h2pi_element_links;
382 --
383 insert_xml_into_table('HR_H2PI_ELEMENT_LINKS',l_dest_clob_loc);
384 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
385 --
386
387 --
388 -- FOR HR_H2PI_BG_AND_GRE table.
389 --
390 l_dest_clob_loc := get_clob_locator('HR_H2PI_BG_AND_GRE');
391 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
392 --
393 hr_utility.set_location('Entering:'|| l_proc, 120);
394 <<hr_h2pi_bg_and_gre>>
395 loop
396 utl_file.get_line(l_fp,l_line);
397 l_text := l_line;
398 if l_text is not null then
399 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
400 end if;
401 if (l_text = '</HR_H2PI_BG_AND_GRE>')
402 OR (l_text = '<HR_H2PI_BG_AND_GRE/>') then
403 exit ;
404 end if;
405 end loop hr_h2pi_bg_and_gre;
406 --
407 insert_xml_into_table('HR_H2PI_BG_AND_GRE',l_dest_clob_loc);
408 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
409 --
410
411 --
412 --
416 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
413 -- FOR HR_H2PI_ORG_PAYMENT_METHODS table.
414 --
415 l_dest_clob_loc := get_clob_locator('HR_H2PI_ORG_PAYMENT_METHODS');
417 --
418 hr_utility.set_location('Entering:'|| l_proc, 130);
419 <<hr_h2pi_org_payment_methods>>
420 loop
421 utl_file.get_line(l_fp,l_line);
422 l_text := l_line;
423 if l_text is not null then
424 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
425 end if;
426 if (l_text = '</HR_H2PI_ORG_PAYMENT_METHODS>')
427 OR (l_text = '<HR_H2PI_ORG_PAYMENT_METHODS/>') then
428 exit ;
429 end if;
430 end loop hr_h2pi_org_payment_methods;
431 --
432 insert_xml_into_table('HR_H2PI_ORG_PAYMENT_METHODS',l_dest_clob_loc);
433 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
434 --
435
436 --
437 -- FOR HR_H2PI_PATCH_STATUS table.
438 --
439 l_dest_clob_loc := get_clob_locator('HR_H2PI_PATCH_STATUS');
440 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
441 --
442 hr_utility.set_location('Entering:'|| l_proc, 140);
443 <<hr_h2pi_patch_status>>
444 loop
445 utl_file.get_line(l_fp,l_line);
446 l_text := l_line;
447 if l_text is not null then
448 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
449 end if;
450 if (l_text = '</HR_H2PI_PATCH_STATUS>')
451 OR (l_text = '<HR_H2PI_PATCH_STATUS/>') then
452 exit ;
453 end if;
454 end loop hr_h2pi_patch_status;
455 --
456 insert_xml_into_table('HR_H2PI_PATCH_STATUS',l_dest_clob_loc);
457 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
458 --
459
460 --
461 -- FOR HR_H2PI_FEDERAL_TAX_RULES table.
462 --
463 l_dest_clob_loc := get_clob_locator('HR_H2PI_FEDERAL_TAX_RULES');
464 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
465 --
466 hr_utility.set_location('Entering:'|| l_proc, 150);
467 <<hr_h2pi_federal_tax_rules>>
468 loop
469 utl_file.get_line(l_fp,l_line);
470 l_text := l_line;
471 if l_text is not null then
472 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
473 end if;
474 if (l_text = '</HR_H2PI_FEDERAL_TAX_RULES>')
475 OR (l_text = '<HR_H2PI_FEDERAL_TAX_RULES/>') then
476 exit ;
477 end if;
478 end loop hr_h2pi_federal_tax_rules;
479 --
480 insert_xml_into_table('HR_H2PI_FEDERAL_TAX_RULES',l_dest_clob_loc);
481 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
482 --
483
484 --
485 -- FOR HR_H2PI_STATE_TAX_RULES table.
486 --
487 l_dest_clob_loc := get_clob_locator('HR_H2PI_STATE_TAX_RULES');
488 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
489 --
490 hr_utility.set_location('Entering:'|| l_proc, 160);
491 <<hr_h2pi_state_tax_rules>>
492 loop
493 utl_file.get_line(l_fp,l_line);
494 l_text := l_line;
495 if l_text is not null then
496 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
497 end if;
498 if (l_text = '</HR_H2PI_STATE_TAX_RULES>')
499 OR (l_text = '<HR_H2PI_STATE_TAX_RULES/>') then
500 exit ;
501 end if;
502 end loop hr_h2pi_state_tax_rules;
503 --
504 insert_xml_into_table('HR_H2PI_STATE_TAX_RULES',l_dest_clob_loc);
505 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
506 --
507
508 --
509 -- FOR HR_H2PI_COUNTY_TAX_RULES table.
510 --
511 l_dest_clob_loc := get_clob_locator('HR_H2PI_COUNTY_TAX_RULES');
512 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
513 --
514 hr_utility.set_location('Entering:'|| l_proc, 170);
515 <<hr_h2pi_county_tax_rules>>
516 loop
517 utl_file.get_line(l_fp,l_line);
518 l_text := l_line;
519 if l_text is not null then
520 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
521 end if;
522 if (l_text = '</HR_H2PI_COUNTY_TAX_RULES>')
523 OR (l_text = '<HR_H2PI_COUNTY_TAX_RULES/>') then
524 exit ;
525 end if;
526 end loop hr_h2pi_county_tax_rules;
527 --
528 insert_xml_into_table('HR_H2PI_COUNTY_TAX_RULES',l_dest_clob_loc);
529 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
530 --
531
532 --
533 -- FOR HR_H2PI_CITY_TAX_RULES table.
534 --
535 l_dest_clob_loc := get_clob_locator('HR_H2PI_CITY_TAX_RULES');
536 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
537 --
538 hr_utility.set_location('Entering:'|| l_proc, 180);
539 <<hr_h2pi_city_tax_rules>>
540 loop
541 utl_file.get_line(l_fp,l_line);
542 l_text := l_line;
543 if l_text is not null then
544 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
545 end if;
546 if (l_text = '</HR_H2PI_CITY_TAX_RULES>')
550 end loop hr_h2pi_city_tax_rules;
547 OR (l_text = '<HR_H2PI_CITY_TAX_RULES/>') then
548 exit ;
549 end if;
551 --
552 insert_xml_into_table('HR_H2PI_CITY_TAX_RULES',l_dest_clob_loc);
553 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
554 --
555
556 --
557 -- FOR HR_H2PI_ORGANIZATION_CLASS table.
558 --
559 l_dest_clob_loc := get_clob_locator('HR_H2PI_ORGANIZATION_CLASS');
560 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
561 --
562 hr_utility.set_location('Entering:'|| l_proc, 190);
563 <<hr_h2pi_organization_class>>
564 loop
565 utl_file.get_line(l_fp,l_line);
566 l_text := l_line;
567 if l_text is not null then
568 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
569 end if;
570 if (l_text = '</HR_H2PI_ORGANIZATION_CLASS>')
571 OR (l_text = '<HR_H2PI_ORGANIZATION_CLASS/>') then
572 exit ;
573 end if;
574 end loop hr_h2pi_organization_class;
575 --
576 insert_xml_into_table('HR_H2PI_ORGANIZATION_CLASS',l_dest_clob_loc);
577 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
578 --
579
580 --
581 -- FOR HR_H2PI_PERIODS_OF_SERVICE table.
582 --
583 l_dest_clob_loc := get_clob_locator('HR_H2PI_PERIODS_OF_SERVICE');
584 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
585 --
586 hr_utility.set_location('Entering:'|| l_proc, 200);
587 <<hr_h2pi_periods_of_service>>
588 loop
589 utl_file.get_line(l_fp,l_line);
590 l_text := l_line;
591 if l_text is not null then
592 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
593 end if;
594 if (l_text = '</HR_H2PI_PERIODS_OF_SERVICE>')
595 OR (l_text = '<HR_H2PI_PERIODS_OF_SERVICE/>') then
596 exit ;
597 end if;
598 end loop hr_h2pi_periods_of_service;
599 --
600 insert_xml_into_table('HR_H2PI_PERIODS_OF_SERVICE',l_dest_clob_loc);
601 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
602 --
603 --
604 -- FOR HR_H2PI_SALARIES table.
605 --
606 l_dest_clob_loc := get_clob_locator('HR_H2PI_SALARIES');
607 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
608 --
609 hr_utility.set_location('Entering:'|| l_proc, 210);
610 <<hr_h2pi_salaries>>
611 loop
612 utl_file.get_line(l_fp,l_line);
613 l_text := l_line;
614 if l_text is not null then
615 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
616 end if;
617 if (l_text = '</HR_H2PI_SALARIES>')
618 OR (l_text = '<HR_H2PI_SALARIES/>') then
619 exit ;
620 end if;
621 end loop hr_h2pi_salaries;
622 --
623 insert_xml_into_table('HR_H2PI_SALARIES',l_dest_clob_loc);
624 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
625 --
626
627 --
628 -- FOR HR_H2PI_ORGANIZATION_INFO table.
629 --
630 l_dest_clob_loc := get_clob_locator('HR_H2PI_ORGANIZATION_INFO');
631 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
632 --
633 hr_utility.set_location('Entering:'|| l_proc, 220);
634 <<hr_h2pi_organization_info>>
635 loop
636 utl_file.get_line(l_fp,l_line);
637 l_text := l_line;
638 if l_text is not null then
639 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
640 end if;
641 if (l_text = '</HR_H2PI_ORGANIZATION_INFO>')
642 OR (l_text = '<HR_H2PI_ORGANIZATION_INFO/>') then
643 exit ;
644 end if;
645 end loop hr_h2pi_organization_info;
646 --
647 insert_xml_into_table('HR_H2PI_ORGANIZATION_INFO',l_dest_clob_loc);
648 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
649 --
650
651 --
652 -- FOR HR_H2PI_COST_ALLOCATIONS table.
653 --
654 l_dest_clob_loc := get_clob_locator('HR_H2PI_COST_ALLOCATIONS');
655 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
656 --
657 hr_utility.set_location('Entering:'|| l_proc, 230);
658 <<hr_h2pi_cost_allocations>>
659 loop
660 utl_file.get_line(l_fp,l_line);
661 l_text := l_line;
662 if l_text is not null then
663 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
664 end if;
665 if (l_text = '</HR_H2PI_COST_ALLOCATIONS>')
666 OR (l_text = '<HR_H2PI_COST_ALLOCATIONS/>') then
667 exit ;
668 end if;
669 end loop hr_h2pi_cost_allocations;
670 --
671 insert_xml_into_table('HR_H2PI_COST_ALLOCATIONS',l_dest_clob_loc);
672 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
673 --
674
675 --
676 -- FOR HR_H2PI_PAYMENT_METHODS table.
677 --
678 l_dest_clob_loc := get_clob_locator('HR_H2PI_PAYMENT_METHODS');
679 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
680 --
681 hr_utility.set_location('Entering:'|| l_proc, 240);
682 <<hr_h2pi_payment_methods>>
686 if l_text is not null then
683 loop
684 utl_file.get_line(l_fp,l_line);
685 l_text := l_line;
687 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
688 end if;
689 if (l_text = '</HR_H2PI_PAYMENT_METHODS>')
690 OR (l_text = '<HR_H2PI_PAYMENT_METHODS/>') then
691 exit ;
692 end if;
693 end loop hr_h2pi_payment_methods;
694 --
695 insert_xml_into_table('HR_H2PI_PAYMENT_METHODS',l_dest_clob_loc);
696 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
697 --
698
699 --
700 -- FOR HR_H2PI_ELEMENT_NAMES table.
701 --
702 l_dest_clob_loc := get_clob_locator('HR_H2PI_ELEMENT_NAMES');
703 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
704 --
705 hr_utility.set_location('Entering:'|| l_proc, 250);
706 <<hr_h2pi_element_names>>
707 loop
708 utl_file.get_line(l_fp,l_line);
709 l_text := l_line;
710 if l_text is not null then
711 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
712 end if;
713 if (l_text = '</HR_H2PI_ELEMENT_NAMES>')
714 OR (l_text = '<HR_H2PI_ELEMENT_NAMES/>') then
715 exit ;
716 end if;
717 end loop hr_h2pi_element_names;
718 --
719 insert_xml_into_table('HR_H2PI_ELEMENT_NAMES',l_dest_clob_loc);
720 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
721 --
722
723 --
724 -- FOR HR_H2PI_ELEMENT_ENTRIES table.
725 --
726 l_dest_clob_loc := get_clob_locator('HR_H2PI_ELEMENT_ENTRIES');
727 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
728 --
729 hr_utility.set_location('Entering:'|| l_proc, 260);
730 <<hr_h2pi_element_entries>>
731 loop
732 utl_file.get_line(l_fp,l_line);
733 l_text := l_line;
734 if l_text is not null then
735 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
736 end if;
737 if l_text = '</HR_H2PI_ELEMENT_ENTRIES>' then exit ; end if;
738 if (l_text = '</HR_H2PI_ELEMENT_ENTRIES>')
739 OR (l_text = '<HR_H2PI_ELEMENT_ENTRIES/>') then
740 exit ;
741 end if;
742 end loop hr_h2pi_element_entries;
743 --
744 insert_xml_into_table('HR_H2PI_ELEMENT_ENTRIES',l_dest_clob_loc);
745 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
746 --
747
748 --
749 -- FOR HR_H2PI_ELEMENT_ENTRY_VALUES table.
750 --
751 l_dest_clob_loc := get_clob_locator('HR_H2PI_ELEMENT_ENTRY_VALUES');
752 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
753 --
754 <<hr_h2pi_element_entry_values>>
755 hr_utility.set_location('Entering:'|| l_proc, 270);
756 loop
757 utl_file.get_line(l_fp,l_line);
758 l_text := l_line;
759 if l_text is not null then
760 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
761 end if;
762 if l_text = '</HR_H2PI_ELEMENT_ENTRY_VALUES>' then exit ; end if;
763 if (l_text = '</HR_H2PI_ELEMENT_ENTRY_VALUES>')
764 OR (l_text = '<HR_H2PI_ELEMENT_ENTRY_VALUES/>') then
765 exit ;
766 end if;
767 end loop hr_h2pi_element_entry_values;
768 --
769 insert_xml_into_table('HR_H2PI_ELEMENT_ENTRY_VALUES',l_dest_clob_loc);
770 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
771 --
772
773
774 --
775 -- FOR HR_H2PI_US_MODIFIED_GEOCODES table.
776 --
777 l_dest_clob_loc := get_clob_locator('HR_H2PI_US_MODIFIED_GEOCODES');
778 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
779 --
780 <<hr_h2pi_us_modified_geocodes>>
781 hr_utility.set_location('Entering:'|| l_proc, 280);
782 loop
783 utl_file.get_line(l_fp,l_line);
784 l_text := l_line;
785 if l_text is not null then
786 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
787 end if;
788 if l_text = '</HR_H2PI_US_MODIFIED_GEOCODES>' then exit ; end if;
789 if (l_text = '</HR_H2PI_US_MODIFIED_GEOCODES>')
790 OR (l_text = '<HR_H2PI_US_MODIFIED_GEOCODES/>') then
791 exit ;
792 end if;
793 end loop HR_H2PI_US_MODIFIED_GEOCODES;
794 --
795 insert_xml_into_table('HR_H2PI_US_MODIFIED_GEOCODES',l_dest_clob_loc);
796 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
797 --
798
799 --
800 -- FOR HR_H2PI_US_CITY_NAMES table.
801 --
802 l_dest_clob_loc := get_clob_locator('HR_H2PI_US_CITY_NAMES');
803 dbms_lob.createtemporary(l_dest_clob_loc,TRUE);
804 --
805 <<HR_H2PI_US_CITY_NAMES>>
806 hr_utility.set_location('Entering:'|| l_proc, 290);
807 loop
808 utl_file.get_line(l_fp,l_line);
809 l_text := l_line;
810 if l_text is not null then
811 dbms_lob.writeappend(l_dest_clob_loc,length(l_text),l_text);
812 end if;
813 if l_text = '</HR_H2PI_US_CITY_NAMES>' then exit ; end if;
814 if (l_text = '</HR_H2PI_US_CITY_NAMES>')
815 OR (l_text = '<HR_H2PI_US_CITY_NAMES/>') then
819 --
816 exit ;
817 end if;
818 end loop HR_H2PI_US_CITY_NAMES;
820 insert_xml_into_table('HR_H2PI_US_CITY_NAMES',l_dest_clob_loc);
821 DBMS_LOB.FREETEMPORARY(l_dest_clob_loc);
822 --
823 utl_file.fclose(l_fp);
824
825 --commit;
826 hr_utility.set_location('Leaving:'|| l_proc, 300);
827
828 EXCEPTION
829 WHEN UTL_FILE.INVALID_PATH THEN
830 fnd_message.set_name('PER','HR_52089_NOT_OPEN_FILE');
831 fnd_message.set_token('FILENAME',p_file_name);
832 fnd_message.raise_error;
833 WHEN UTL_FILE.INVALID_OPERATION THEN
834 fnd_message.set_name('PER','HR_52089_NOT_OPEN_FILE');
835 fnd_message.set_token('FILENAME',p_file_name);
836 fnd_message.raise_error;
837
838 END upload;
839
840 END hr_h2pi_upload ;