[Home] [Help]
PACKAGE BODY: APPS.HR_DU_DI_INSERT
Source
1 PACKAGE BODY hr_du_di_insert AS
2 /* $Header: perduext.pkb 120.1 2005/06/27 02:51:04 mroberts noship $ */
3
4
5 -- --------------------- VALIDATE_SHEET_DESCRIPTORS -------------------------
6 -- Description: This procedure checks to make sure that there are no
7 -- two descriptors with the same name
8 --
9 -- Input Parameters
10 -- p_upload_id : Identifies the upload
11 --
12 -- p_upload_header_id : Identifies the individual header
13 -- -------------------------------------------------------------------------
14 PROCEDURE VALIDATE_SHEET_DESCRIPTORS (p_upload_id IN VARCHAR2,
15 p_upload_header_id IN NUMBER)
16 IS
17
18 e_fatal_error EXCEPTION;
19 l_fatal_error_message VARCHAR2(2000);
20 l_counter_1 NUMBER;
21 l_counter_2 NUMBER;
22 l_file_name VARCHAR2(2000);
23
24 CURSOR csr_unique_desc IS
25 SELECT count (descriptor)
26 FROM hr_du_descriptors
27 WHERE upload_id = p_upload_id
28 AND upload_header_id = p_upload_header_id;
29
30 CURSOR csr_total_desc IS
31 SELECT count(distinct descriptor)
32 FROM hr_du_descriptors
33 WHERE upload_id = p_upload_id
34 AND upload_header_id = p_upload_header_id;
35
36 BEGIN
37 --
38 hr_du_utility.message('ROUT',
39 'entry:hr_du_di_insert.validate_sheet_descriptors', 5);
40 hr_du_utility.message('PARA', '(p_upload_id - ' || p_upload_id ||
41 ')(p_upload_header_id - ' || p_upload_header_id || ')' , 10);
42 --
43 OPEN csr_total_desc;
44 FETCH csr_total_desc INTO l_counter_1;
45 IF csr_total_desc%NOTFOUND THEN
46 l_file_name := hr_du_rules.RETURN_UPLOAD_HEADER_FILE(
47 p_upload_header_id);
48 l_fatal_error_message := 'Error occured trying to count all of the '
49 || 'descriptors in the file ' || l_file_name;
50 RAISE e_fatal_error;
51 END IF;
52 CLOSE csr_total_desc;
53
54 OPEN csr_unique_desc;
55 FETCH csr_unique_desc INTO l_counter_2;
56 IF csr_unique_desc%NOTFOUND THEN
57 l_file_name := hr_du_rules.RETURN_UPLOAD_HEADER_FILE(
58 p_upload_header_id);
59 l_fatal_error_message := 'Error occured trying to count all of the '
60 || 'distinct descriptors in the file ' || l_file_name;
61 RAISE e_fatal_error;
62 END IF;
63 CLOSE csr_unique_desc;
64
65 IF l_counter_1 <> l_counter_2 THEN
66 l_file_name := hr_du_rules.RETURN_UPLOAD_HEADER_FILE(p_upload_header_id);
67 l_fatal_error_message := 'Two descriptors with the same name in ' ||
68 'the file ' || l_file_name;
69 RAISE e_fatal_error;
70 END IF;
71
72 --
73 hr_du_utility.message('ROUT',
74 'exit:hr_du_di_insert.validate_sheet_descriptors', 15);
75 --
76 EXCEPTION
77 WHEN e_fatal_error THEN
78 hr_du_utility.error(SQLCODE,
79 'hr_du_di_insert.validate_sheet_descriptors',
80 l_fatal_error_message, 'R');
81 RAISE;
82 WHEN OTHERS THEN
83 hr_du_utility.error(SQLCODE,'hr_du_di_insert.validate_sheet_descriptors'
84 ,'(none)', 'R');
85 RAISE;
86 --
87 END VALIDATE_SHEET_DESCRIPTORS;
88
89
90 -- ----------------------VALIDATE_HEADER_DESCRIPTORS------------------------
91 -- Description: This procedure checks to make sure that there are no
92 -- two descriptors with the same name in the header section
93 --
94 -- Input Parameters
95 -- p_upload_id : Identifies the upload to compare the headers
96 -- -------------------------------------------------------------------------
97 PROCEDURE VALIDATE_HEADER_DESCRIPTORS (p_upload_id IN VARCHAR2)
98 IS
99
100 e_fatal_error EXCEPTION;
101 l_fatal_error_message VARCHAR2(2000);
102 l_counter_1 NUMBER;
103 l_counter_2 NUMBER;
104 l_file_name VARCHAR2(2000);
105
106 --counts all headers
107 CURSOR csr_unique_desc IS
108 SELECT count (descriptor)
109 FROM hr_du_descriptors
110 WHERE upload_id = p_upload_id
111 AND upload_header_id IS NULL;
112
113 --counts all unique headers
114 CURSOR csr_total_desc IS
115 SELECT count(distinct descriptor)
116 FROM hr_du_descriptors
117 WHERE upload_id = p_upload_id
118 AND upload_header_id IS NULL;
119
120 BEGIN
121 --
122 hr_du_utility.message('ROUT',
123 'entry:hr_du_di_insert.validate_header_descriptors', 5);
124 hr_du_utility.message('PARA',
125 '(p_upload_id - ' || p_upload_id || ')' , 10);
126
127 --
128 OPEN csr_total_desc;
129 FETCH csr_total_desc INTO l_counter_1;
130 IF csr_total_desc%NOTFOUND THEN
131 l_fatal_error_message := 'Error occured trying to count all of the '
132 || 'descriptors in the header file';
133 RAISE e_fatal_error;
134 END IF;
135 CLOSE csr_total_desc;
136
137 OPEN csr_unique_desc;
138 FETCH csr_unique_desc INTO l_counter_2;
139 IF csr_unique_desc%NOTFOUND THEN
140 l_fatal_error_message := 'Error occured trying to count all of the '
141 || 'distinct descriptors in the header file';
142 RAISE e_fatal_error;
143 END IF;
144 CLOSE csr_unique_desc;
145
146 IF l_counter_1 <> l_counter_2 THEN
147 l_fatal_error_message := 'There are descriptors with identical ' ||
148 'names on the header sheet';
149 RAISE e_fatal_error;
150 END IF;
151
152 --
153 hr_du_utility.message('ROUT',
154 'exit:hr_du_di_insert.validate_header_descriptors', 15);
155 --
156 EXCEPTION
157 WHEN e_fatal_error THEN
158 hr_du_utility.error(SQLCODE,
159 'hr_du_di_insert.validate_header_descriptors',
160 l_fatal_error_message, 'R');
161 RAISE;
162 WHEN OTHERS THEN
163 hr_du_utility.error(SQLCODE,
164 'hr_du_di_insert.validate_header_descriptors',
165 '(none)', 'R');
166 RAISE;
167 --
168 END VALIDATE_HEADER_DESCRIPTORS;
169
170 -- ------------------------- PARSE_LINE_TO_TABLE ---------------------------
171 -- Description: The procedure takes the data line then works through
172 -- stripping out the the data and placing it into the SQL array. When
173 -- all data has been removed the remainder of the array is filled with
174 -- nulls
175 --
176 -- Input Parameters
177 -- p_data_line : The data line that's been read from the flat file
178 --
179 -- p_upload_header_id : Identifies the upload header so that the file name
180 -- can be retrieved
181 --
182 -- p_line_type : Column or data line tag
183 -- -------------------------------------------------------------------------
184 PROCEDURE PARSE_LINE_TO_TABLE (p_data_line IN VARCHAR2,
185 p_upload_header_id IN NUMBER, p_line_type IN VARCHAR2)
186 IS
187
188 e_fatal_error EXCEPTION;
189 l_fatal_error_message VARCHAR2(2000);
190 l_position NUMBER;
191 l_difference NUMBER;
192 l_next NUMBER :=1;
193 l_section VARCHAR2(2000);
194 l_length NUMBER;
195 l_number_words NUMBER;
196 l_file_name VARCHAR2(200);
197
198 BEGIN
199 --
200 hr_du_utility.message('ROUT',
201 'entry:hr_du_di_insert.parse_line_to_table', 5);
202 hr_du_utility.message('PARA', '(p_data_line - ' || p_data_line ||
203 ')(p_upload_header_id - ' || p_upload_header_id ||
204 ')(p_line_type - ' || p_line_type || ')' , 10);
205 --
206
207 l_number_words := WORDS_ON_LINE(p_data_line);
208
209 FOR i IN 1..l_number_words LOOP
210 --
211 l_position := INSTRB(p_data_line, g_current_delimiter, l_next, 1);
212 IF l_position = 0 THEN
213 l_length := LENGTHB(p_data_line);
214 l_section := SUBSTRB(p_data_line, l_next, l_length);
215 l_length := LENGTHB(l_section);
216 IF l_length > 0 THEN
217 --
218 IF p_line_type = 'C' THEN
219 --loops around to check for identical column names
220 FOR j IN 1..(i - 1) LOOP
221 IF g_line_table(j) = l_section THEN
222 l_file_name :=
223 HR_DU_RULES.RETURN_UPLOAD_HEADER_FILE(p_upload_header_id);
224 l_fatal_error_message := ' Two columns have the same name ' ||
225 l_section || ' in file ' ||
226 l_file_name;
227 RAISE e_fatal_error;
228 END IF;
229 END LOOP;
230 END IF;
231 g_line_table(i) := l_section;
232 --
233 ELSE
234 g_line_table(i) := NULL;
235 END IF;
236 ELSE
237 l_difference := l_position - l_next;
238 l_section := SUBSTRB(p_data_line, l_next, l_difference);
239 l_length := LENGTHB(l_section);
240 IF l_length IS NULL THEN
241 l_section := NULL;
242 END IF;
243
244 IF p_line_type = 'C' THEN
245 --loops around to check for identical column names
246 FOR j IN 1..(i - 1) LOOP
247 IF g_line_table(j) = l_section THEN
248 l_file_name := HR_DU_RULES.RETURN_UPLOAD_HEADER_FILE
249 (p_upload_header_id);
250 l_fatal_error_message := ' Two columns have the same name ' ||
251 l_section || ' in file ' ||
252 l_file_name;
253 RAISE e_fatal_error;
254 END IF;
255 END LOOP;
256 END IF;
257
258 g_line_table(i) := l_section;
259 END IF;
260 --sets the cursor positions up for the next pass
261 l_next := l_position + 1;
262 --
263 END LOOP;
264
265 --loops through the remaining 230 files in the array and fill with null
266 FOR j IN (l_number_words + 1)..230 LOOP
267 g_line_table(j) := NULL;
268 END LOOP;
269
270 --
271 hr_du_utility.message('ROUT',
272 'exit:hr_du_di_insert.parse_line_to_table', 15);
273 --
274 EXCEPTION
275 WHEN e_fatal_error THEN
276 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.parse_line_to_table',
277 l_fatal_error_message, 'R');
278 RAISE;
279 WHEN OTHERS THEN
280 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.parse_line_to_table',
281 '(none)', 'R');
282 RAISE;
283 --
284 END PARSE_LINE_TO_TABLE;
285
286
287
288 -- ------------------------- CHECK_UNIQUE_FILES ---------------------------
289 -- Description: Checks to make sure that there are no two or more files
290 -- that have the same file name. It does this by counting the total files
291 -- in the descriptors and then the total of unique file name
292 --
293 -- Input Parameters
294 -- p_upload_id - Identifies the upload associated with the files
295 -- -------------------------------------------------------------------------
296 PROCEDURE CHECK_UNIQUE_FILES (p_upload_id IN NUMBER)
297 IS
298
299 l_counter_1 NUMBER;
300 l_counter_2 NUMBER;
301 e_fatal_error EXCEPTION;
302 l_fatal_error_message VARCHAR2(2000);
303
304 CURSOR csr_total_count IS
305 SELECT count (value)
306 FROM hr_du_descriptors
307 WHERE upload_id = p_upload_id
308 AND DESCRIPTOR_TYPE = 'F';
309
310 CURSOR csr_file_count IS
311 SELECT count(distinct value)
312 FROM hr_du_descriptors
313 WHERE upload_id = p_upload_id
314 AND DESCRIPTOR_TYPE = 'F';
315
316 BEGIN
317 --
318 hr_du_utility.message('ROUT',
319 'entry:hr_du_di_insert.check_unique_files', 5);
320 --
321
322 OPEN csr_total_count;
323 FETCH csr_total_count INTO l_counter_1;
324 IF csr_total_count%NOTFOUND THEN
325 l_fatal_error_message := 'Error occured trying to count all of the '
326 || 'files in the upload';
327 RAISE e_fatal_error;
328 END IF;
329 CLOSE csr_total_count;
330
331 OPEN csr_file_count;
332 FETCH csr_file_count INTO l_counter_2;
333 IF csr_file_count%NOTFOUND THEN
334 l_fatal_error_message := 'Error occured trying to count all of the '
335 || 'distinct files in the upload';
336 RAISE e_fatal_error;
337 END IF;
338 CLOSE csr_file_count;
339
340 IF l_counter_1 <> l_counter_2 THEN
341 l_fatal_error_message := 'There are APIs with identical file ' ||
342 'names on the header sheet';
343 RAISE e_fatal_error;
344 END IF;
345
346 --
347 hr_du_utility.message('ROUT',
348 'exit:hr_du_di_insert.check_unique_files', 15);
349 --
350 EXCEPTION
351 WHEN e_fatal_error THEN
352 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.validate_api_ids',
353 l_fatal_error_message, 'R');
354 RAISE;
355 WHEN OTHERS THEN
356 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.check_unique_files',
357 '(none)', 'R');
358 RAISE;
359 --
360 END CHECK_UNIQUE_FILES;
361
362
363 -- ------------------------- POPULATE_DYNAMIC_TABLE ------------------------
364 -- Description: This procedure is called once and simply populates the
365 -- dynamic SQL table with the appropriate hr_du_utility.local_CHR(i) values.
366 -- The Upper case are in positions 1..26 and Lower case in 27..52
367 -- -------------------------------------------------------------------------
368 PROCEDURE POPULATE_DYNAMIC_TABLE
369 IS
370
374 --
371 l_counter NUMBER := 1;
372
373 BEGIN
375 hr_du_utility.message('ROUT',
376 'entry:hr_du_di_insert.populate_dynamic_table', 5);
377 --
378
379 --Loop around the upper case letters
380 FOR i IN 65..90 LOOP
381 Char_table(l_counter) := hr_du_utility.local_CHR(i);
382 l_counter := l_counter + 1;
383 END LOOP;
384
385 --Loop around the lower case letters
386 FOR i IN 97..122 LOOP
387 Char_table(l_counter) := hr_du_utility.local_CHR(i);
388 l_counter := l_counter + 1;
389 END LOOP;
390
391 --
392 hr_du_utility.message('ROUT',
393 'exit:hr_du_di_insert.populate_dynamic_table', 15);
394 --
395 EXCEPTION
396 WHEN OTHERS THEN
397 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.populate_dynamic_table',
398 '(none)', 'R');
399 RAISE;
400 --
401 END POPULATE_DYNAMIC_TABLE;
402
403
404 -- -------------------------- VALIDATE_API_IDS ----------------------------
405 -- Description: This procedure simply finds out where the ID column is with
406 -- in the HR_DU_UPLOAD_LINES for a particular header and verfies that all
407 -- the values within that ID coulmn are unique.
408 --
409 -- Input Parameters
410 -- p_upload_header_id - Identify the upload header
411 -- ------------------------------------------------------------------------
412 PROCEDURE VALIDATE_API_IDS(p_upload_header_id IN NUMBER)
413 IS
414
415 CURSOR csr_line_id IS
416 SELECT UPLOAD_LINE_ID
417 FROM hr_du_upload_lines
418 WHERE upload_header_id = p_upload_header_id
419 AND LINE_TYPE = 'C';
420
421 CURSOR csr_API_name IS
422 SELECT upper(des.value)
423 FROM hr_du_upload_headers head,
424 hr_api_modules api,
425 hr_du_descriptors des
426 WHERE head.upload_header_id = p_upload_header_id
427 AND head.upload_header_id = des.upload_header_id
428 AND head.api_module_id = api.api_module_id
429 AND upper(api.module_name) = upper(des.value);
430
431
432 CURSOR csr_count IS
433 Select count(PVAL001)
434 FROM hr_du_upload_lines
435 WHERE upload_header_id = p_upload_header_id;
436
437 CURSOR csr_count_distinct IS
438 Select count(DISTINCT PVAL001)
439 FROM hr_du_upload_lines
440 WHERE upload_header_id = p_upload_header_id;
441
442
443 e_fatal_error EXCEPTION;
444 l_fatal_error_message VARCHAR2(2000);
445 l_line_id NUMBER;
446 l_current_pval VARCHAR2(10);
447 l_pval_field VARCHAR2(50);
448 l_spaces BOOLEAN;
449 l_count1 NUMBER;
450 l_count2 NUMBER;
451 l_dynamic_string VARCHAR2(2000);
452 l_file VARCHAR2(200);
453 l_cursor_handle INT;
454
455 BEGIN
456 --
457 hr_du_utility.message('ROUT',
458 'entry:hr_du_di_insert.validate_api_ids', 5);
459 hr_du_utility.message('PARA',
460 '(p_upload_header_id - ' || p_upload_header_id || ')' , 10);
461 --
462
463 OPEN csr_line_id;
464 FETCH csr_line_id INTO l_line_id;
465 IF csr_line_id%NOTFOUND THEN
466 l_fatal_error_message := 'No appropriate column title row exists in '
467 || 'the HR_DU_UPLOAD_LINES';
468 RAISE e_fatal_error;
469 END IF;
470 CLOSE csr_line_id;
471
472 HR_DU_DO_DATAPUMP.STORE_COLUMN_HEADINGS(l_line_id);
473
474 FOR i IN 1..230 LOOP
475 --
476 l_current_pval := LPAD(i,3,'0');
477 l_current_pval := 'PVAL' || l_current_pval;
478
479 --fetch the heading stored within the specified upload line
480 l_pval_field := HR_DU_DO_DATAPUMP.g_column_headings(i);
481
482 hr_du_dp_pc_conversion.REMOVE_SPACES (l_pval_field, l_spaces);
483
484 IF l_spaces = TRUE THEN
485 hr_du_utility.message('INFO', 'Warning : l_pval_field (with ' ||
486 'spaces removed) : ' || l_pval_field , 20);
487 END IF;
488 --
489 END LOOP;
490
491 OPEN csr_count;
492 FETCH csr_count INTO l_count1;
493 IF csr_count%NOTFOUND THEN
494 l_fatal_error_message := 'Unable to count the number of column Ids';
495 RAISE e_fatal_error;
496 END IF;
497 CLOSE csr_count;
498
499 OPEN csr_count_distinct;
500 FETCH csr_count_distinct INTO l_count2;
501 IF csr_count_distinct%NOTFOUND THEN
502 l_fatal_error_message := 'Unable to count the number of distinct ' ||
503 'column Ids';
504 RAISE e_fatal_error;
505 END IF;
506 CLOSE csr_count_distinct;
507
508 IF l_count1 <> l_count2 THEN
509 --
510 OPEN csr_API_name;
511 FETCH csr_API_name INTO l_file;
512 IF csr_API_name%NOTFOUND THEN
513 l_fatal_error_message := 'No appropriate API name exists.';
514 RAISE e_fatal_error;
515 END IF;
516 CLOSE csr_API_name;
517
518 l_fatal_error_message :='ID values are not unique with in the file '
519 || l_file;
520 RAISE e_fatal_error;
521 END IF;
522
523 --
524 hr_du_utility.message('ROUT','exit:hr_du_di_insert.validate_api_ids', 15);
525 --
526 EXCEPTION
527 WHEN e_fatal_error THEN
528 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.validate_api_ids',
529 l_fatal_error_message, 'R');
530 RAISE;
531 WHEN OTHERS THEN
532 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.validate_api_ids',
533 '(none)', 'R');
534 RAISE;
535 --
536 END VALIDATE_API_IDS;
537
538
539
540 -- ----------------------- SET_DElIMITER_STRING ----------------------------
541 -- Description: Sets the representation of the current global delimiter as
542 -- a string this allows special characters such as tabs to be visually
543 -- represented
544 -- ------------------------------------------------------------------------
545 PROCEDURE SET_DElIMITER_STRING IS
546
547 BEGIN
548
549 IF g_current_delimiter = g_tab_delimiter THEN
550 g_current_delimiter_string := '** ( tab ) **';
551 ELSIF g_current_delimiter = g_carr_delimiter THEN
552 g_current_delimiter_string := '** ( Carriage return ) **';
553 ELSIF g_current_delimiter = g_linef_delimiter THEN
554 g_current_delimiter_string := '** ( Line Feed ) **';
555 ELSE
556 g_current_delimiter_string := g_current_delimiter;
557 END IF;
558
559 EXCEPTION
560 WHEN OTHERS THEN
561 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.set_delimiter_string',
562 '(none)', 'R');
563 RAISE;
564 --
565 END SET_DElIMITER_STRING;
566
567
568 -- ------------------------- NUM_DELIMITERS ------------------------------
569 -- Description: Returns the number of delimiters within the line of text
570 --
571 -- Input Parameters
572 -- p_line - line of text passed to be checked
573 --
574 -- Output Parameters
575 -- Num_Delimiters- the number of delimiters in the line
576 --
577 -- ------------------------------------------------------------------------
578 FUNCTION NUM_DELIMITERS (p_line IN VARCHAR2)
579 RETURN NUMBER
580 IS
581
582 --the position of the delimiter in the string
583 l_position NUMBER;
584 --this is the next cursor position to search from
585 l_next NUMBER := 1;
586 --the number of delimiters encountered so far
587 l_count NUMBER := 0;
588
589 BEGIN
590
591 IF p_line IS null THEN
592 l_count := 0;
593 ELSE
594 LOOP
595 --
596 l_position := INSTRB(p_line, g_current_delimiter, l_next, 1);
597 EXIT WHEN l_position = 0;
598 l_next := l_position + 1;
599 l_count := l_count + 1;
600 --
601 END LOOP;
602 END IF;
603
604 RETURN l_count;
605
606 --error handling
607 EXCEPTION
608 WHEN OTHERS THEN
609 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.num_delimiters',
610 '(none)', 'R');
611 RAISE;
612 --
613 END NUM_DELIMITERS;
614
615
616 -- ----------------------------- WORDS_ON_LINE ---------------------------
617 -- Description: Tells you how many words are on the line, for there's
618 -- a difference to the way comma and tab file lines end.
619 --
620 -- Input Parameters
621 -- p_line - line of text passed to be checked
622 --
623 -- Output Parameters
624 -- l_number_del - Number of words
625 --
626 -- ------------------------------------------------------------------------
627 FUNCTION WORDS_ON_LINE (p_line IN VARCHAR2)
628 RETURN NUMBER
629 IS
630 l_number_del NUMBER;
631 l_section VARCHAR2(2000);
632
633 BEGIN
634
635 l_number_del := g_delimiter_count;
636 l_section := Return_Word (p_line, (l_number_del + 1));
637
638 IF l_section IS NOT NULL THEN
639 l_number_del := l_number_del + 1;
640 END IF;
641
642 RETURN l_number_del;
643
644 --error handling
645 EXCEPTION
646 WHEN OTHERS THEN
647 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.words_on_line','(none)',
648 'R');
649 RAISE;
650 END WORDS_ON_LINE;
651
652
653
654 -- --------------------------- REMOVE_GARBAGE ----------------------------
655 -- Description: Removes all the delimiters from the line of text.
656 --
657 -- Input Parameters
658 -- p_line - line of text passed to be checked
659 --
660 -- Output Parameters
661 -- l_new_data_line - The new line with out the delimiters
662 --
663 -- ------------------------------------------------------------------------
664 FUNCTION REMOVE_GARBAGE (p_line IN VARCHAR2)
665 RETURN VARCHAR2
666 IS
667
668 l_temp_line VARCHAR2(32767);
669 l_new_data_line VARCHAR2(32767);
670
671 BEGIN
672 --
673 hr_du_utility.message('ROUT','entry:hr_du_di_insert.remove_garbage', 5);
674 hr_du_utility.message('PARA', '(p_line - ' || p_line || ')' , 10);
675 --
676
677 --this loop handles removing i.e carrage returns from the line
678 IF g_delimiter_count = 0 THEN
679 l_new_data_line := p_line;
680 ELSE
681 FOR i IN 1..(g_delimiter_count + 1) LOOP
682 l_temp_line := Return_Word(p_line, i);
683 l_new_data_line := l_new_data_line || l_temp_line;
684 END LOOP;
685 END IF;
686 --
687 hr_du_utility.message('ROUT','exit:hr_du_di_insert.remove_garbage', 15);
688 hr_du_utility.message('PARA', '(l_new_data_line - ' || l_new_data_line
689 || ')' , 20);
690 --
691 RETURN l_new_data_line;
692
693 --error handling
694 EXCEPTION
695 WHEN OTHERS THEN
696 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.remove_garbage','(none)',
697 'R');
698 RAISE;
699
700 END REMOVE_GARBAGE;
701
702
703 -- ------------------------- NEXT_LINE -----------------------------------
704 -- Description: Returns the next line from the flat file and calls
705 -- remove garbage to extract unwanted data.
706 --
707 -- Input Parameters
708 -- p_filehandle - line of text passed to be checked
709 --
710 -- p_upload_header_id - when NO_DATA_FOUND then this number is used
711 -- to find out which API the problem is in.
712 --
713 -- Output Parameters
714 -- l_data_line - new line with the removed articles
715 -- passed back
716 -- ------------------------------------------------------------------------
717 FUNCTION NEXT_LINE (p_filehandle IN utl_file.file_type,
718 p_upload_header_id IN NUMBER ) RETURN VARCHAR2
719 IS
720
721 l_data_line VARCHAR2(32767);
722 l_api_name VARCHAR2(50);
723 l_string_length NUMBER;
724
725 CURSOR csr_api_name IS
726 SELECT api.module_NAME
727 FROM hr_api_modules api,
728 hr_du_upload_headers head
729 WHERE head.upload_header_id = p_upload_header_id
730 AND head.api_module_id = api.api_module_id;
731
732 BEGIN
733 --
734 hr_du_utility.message('ROUT','entry:hr_du_di_insert.next_line', 5);
735 hr_du_utility.message('PARA', '(p_filehandle - Record type )' , 10);
736 --
737 g_counter := g_counter + 1;
738 utl_file.get_line(p_filehandle,l_data_line);
739 l_string_length := LENGTH(l_data_line);
740 --
741 hr_du_utility.message('INFO','Value of the line ' || l_data_line, 15);
742 --
743
744 g_current_delimiter := g_linef_delimiter;
745 g_delimiter_count := Num_Delimiters(l_data_line);
746 IF g_delimiter_count = 1 THEN
747 l_data_line := SUBSTRB
748 (l_data_line, 1, l_string_length - g_length_linef);
749 ELSIF g_delimiter_count > 1 THEN
750 SET_DElIMITER_STRING;
751 l_data_line := Remove_Garbage(l_data_line);
752 END IF;
753
754 g_current_delimiter := g_carr_delimiter;
755 g_delimiter_count := Num_Delimiters(l_data_line);
756 IF g_delimiter_count = 1 THEN
757 l_data_line := SUBSTRB
758 (l_data_line, 1, l_string_length - g_length_carr);
759 ELSIF g_delimiter_count > 1 THEN
760 SET_DElIMITER_STRING;
761 l_data_line := Remove_Garbage(l_data_line);
762 END IF;
763
764 --general separator for the data file at the moment hard coded
765 g_current_delimiter := g_flat_file_delimiter;
766 SET_DElIMITER_STRING;
767 g_delimiter_count := Num_Delimiters(l_data_line);
768
769 --
770 hr_du_utility.message('ROUT','exit:hr_du_di_insert.next_line', 20);
771 hr_du_utility.message('PARA', '(l_data_line - ' || l_data_line || ')' ,
772 25);
773 --
774
775 RETURN l_data_line;
776
777 --error handling
778 EXCEPTION
779 WHEN NO_DATA_FOUND THEN
780 OPEN csr_api_name;
781 FETCH csr_api_name INTO l_api_name;
782 CLOSE csr_api_name;
783 hr_du_utility.error(SQLCODE, 'Problem at line ' || g_counter ||
787 WHEN OTHERS THEN
784 ' within the file relating to the API : '||
785 l_api_name,'(none)', 'R');
786 RAISE;
788 hr_du_utility.error(SQLCODE,
789 'hr_du_di_insert.next_line','(none)', 'R');
790 RAISE;
791 END NEXT_LINE;
792
793
794 -- ------------------------- RETURN_WORD ----------------------------------
795 -- Description: Returns the nth word in the line separated by delimiters
796 --
797 -- Input Parameters
798 -- p_line - line of text passed to be worked on
799 --
800 -- p_word_num - the nth word in the line that you want
801 --
802 -- Output Parameters
803 -- l_section - the word that is removed from the line
804 --
805 --
806 -- ------------------------------------------------------------------------
807 FUNCTION RETURN_WORD (p_line IN VARCHAR2, p_word_num IN NUMBER)
808 RETURN VARCHAR2
809 IS
810
811 e_fatal_error EXCEPTION;
812 l_fatal_error_message VARCHAR2(2000);
813 l_position NUMBER;
814 l_difference NUMBER;
815 l_count NUMBER :=0;
816 l_next NUMBER :=1;
817 l_previous NUMBER;
818 l_section VARCHAR2(2000);
819 l_length NUMBER;
820 l_number_del NUMBER;
821
822 BEGIN
823
824 --check to catch a line with no delimiters in
825 l_number_del := g_delimiter_count;
826 l_length := LENGTHB(p_line);
827
828
829 --catches one word on the line with no delimiters
830 IF l_number_del = 0 THEN
831 IF l_length > 0 THEN
832 l_section := p_line;
833 ELSIF p_line IS NULL THEN
834 l_section := NULL;
835 END IF;
836
837 --catches first word with no delimiter before the word
838 ELSIF p_word_num = 1 then
839 l_position := 0;
840 l_next := INSTRB(p_line, hr_du_di_insert.g_current_delimiter, 1, p_word_num);
841 l_difference := (l_next - 1) - (l_position + 1);
842 l_section := SUBSTRB(p_line, (l_position + 1) , (l_difference + 1));
843
844
845 ELSIF p_word_num >= (l_number_del + 1) THEN
846 --catches last word with no delimiter after the word
847 IF p_word_num = (l_number_del + 1) THEN
848 l_position := INSTRB(p_line, hr_du_di_insert.g_current_delimiter, 1, (p_word_num - 1));
849 l_next := l_length;
850 l_difference := l_next - (l_position + 1);
851 l_section := SUBSTRB(p_line, (l_position + 1) , (l_difference + 1));
852
853 --requested words doesn't exist
854 ELSE
855 l_fatal_error_message :='Word number requested is greater' ||
856 ' than those on the line';
857 RAISE e_fatal_error;
858 END IF;
859
860 --normal case
861 ELSE
862 l_position := INSTRB(p_line, hr_du_di_insert.g_current_delimiter, 1, (p_word_num - 1));
863 l_next := INSTRB(p_line, hr_du_di_insert.g_current_delimiter, 1, p_word_num);
864 l_difference := (l_next - 1) - (l_position + 1);
865 l_section := SUBSTRB(p_line, (l_position + 1) , (l_difference + 1));
866 END IF;
867
868
869 RETURN l_section;
870
871 --error handling
872 EXCEPTION
873 WHEN e_fatal_error THEN
874 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.return_word',
875 l_fatal_error_message, 'R');
876 RAISE;
877 WHEN OTHERS THEN
878 hr_du_utility.error(SQLCODE,
879 'hr_du_di_insert.return_word','(none)', 'R');
880 RAISE;
881 END RETURN_WORD;
882
883
884 -- ------------------------- GENERAL_EXTRACT ------------------------------
885 -- Description: This procedure updates the HR_DU_DESCRIPTORS tables
886 -- by storing the API names and locations into table
887 --
888 -- Input Parameters
889 -- p_filehandle - the file to be worked on
890 --
891 -- p_upload_id - HR_DU_UPLOAD_ID
892 --
893 -- p_upload_header_id- HR_DU_UPLOAD_HEADER_ID
894 --
895 -- p_string - The word in column one of the spread sheet tag
896 --
897 -- p_descriptor_type - Either D or F inputted into the descriptor table
898 --
899 -- ------------------------------------------------------------------------
900
901 PROCEDURE GENERAL_EXTRACT (p_filehandle IN utl_file.file_type,
902 p_upload_id IN NUMBER,
903 p_upload_header_id IN NUMBER, p_string IN VARCHAR2,
904 p_descriptor_type IN VARCHAR)
905 IS
906
907 e_fatal_error EXCEPTION;
908 l_fatal_error_message VARCHAR2(2000);
909 l_data_line VARCHAR2(2000);
910 l_Col_one VARCHAR2(2000);
911 l_Col_two VARCHAR2(2000);
912 l_file_name VARCHAR2(2000);
913
914 BEGIN
915 --
916 hr_du_utility.message('ROUT','entry:hr_du_di_insert.general_extract', 5);
917 hr_du_utility.message('PARA', '(p_filehandle - Record Type)' ||
918 '(p_upload_id - ' || p_upload_id ||
919 ')(p_upload_header_id - ' || p_upload_header_id ||
920 ')(p_string - ' || p_string ||
921 ')(p_descriptor_type - ' || p_descriptor_type || ')' , 10);
922 --
923 LOOP
924 BEGIN
925 l_data_line := next_line(p_filehandle, p_upload_header_id);
926 hr_du_utility.message('INFO','Processing Line - ' || l_data_line, 15);
927 --call return_word to get the first word which is the descriptor
928 l_Col_one := upper(Return_Word (l_data_line, 1));
929 l_Col_two := upper(Return_Word (l_data_line, 2));
930 EXIT WHEN (l_Col_one = p_string)
931 AND (l_Col_two = 'START');
932
933 --checks for syntax errors in the spreadsheet header
937 'Started. START ' || p_string ||
934 IF (l_Col_one = p_string) OR (l_Col_one = 'DATA')
935 AND (l_Col_two = 'END') OR (l_Col_two = 'START') THEN
936 l_fatal_error_message := 'Syntax error File incorrectly ' ||
938 ' must be present in the flat file ';
939 RAISE e_fatal_error;
940 END IF;
941
942 EXCEPTION
943 WHEN no_data_found THEN
944 l_fatal_error_message := 'Incorrect syntax for header' ||
945 'Error occured at row ' || g_counter;
946 RAISE e_fatal_error;
947 END;
948 END LOOP;
949 --
950 LOOP
951 BEGIN
952 l_data_line := next_line(p_filehandle, p_upload_header_id);
953 hr_du_utility.message('INFO','Processing Line - ' || l_data_line, 20);
954 IF l_data_line IS NULL THEN
955 null;
956 ELSE
957 l_Col_one := Return_Word (l_data_line, 1);
958 l_Col_two := Return_Word (l_data_line, 2);
959
960 -- statement to catch for null values in the descriptors
961 IF l_Col_two IS NULL THEN
962 IF p_upload_header_id IS NOT NULL THEN
963 l_file_name := HR_DU_RULES.RETURN_UPLOAD_HEADER_FILE
964 (p_upload_header_id);
965 l_fatal_error_message := 'A value must be supplied for the ' ||
966 l_Col_one || ' in the file ' ||
967 l_file_name;
968 RAISE e_fatal_error;
969 ELSE
970 l_fatal_error_message := 'A value must be supplied for the ' ||
971 l_Col_one || ' in the header sheet';
972 RAISE e_fatal_error;
973 END IF;
974 END IF;
975
976 EXIT WHEN (UPPER(l_Col_one) = p_string)
977 AND (UPPER(l_Col_two) = 'END');
978
979 --insert into the descriptors table the values
980 hr_du_utility.message('INFO','Insert statement', 25);
981 INSERT INTO HR_DU_DESCRIPTORS(
982 DESCRIPTOR_ID, UPLOAD_ID, UPLOAD_HEADER_ID,
983 DESCRIPTOR, VALUE, DESCRIPTOR_TYPE, LAST_UPDATE_DATE,
984 LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY,
985 CREATION_DATE)
986 VALUES(
987 HR_DU_DESCRIPTORS_S.nextval,
988 p_upload_id,
989 p_upload_header_id,
990 UPPER(l_Col_one),
991 l_Col_two,
992 p_descriptor_type,
993 sysdate,
994 1,
995 1,
996 1,
997 sysdate);
998 COMMIT;
999 --
1000 IF (UPPER(l_Col_one) = p_string OR UPPER(l_Col_one) = 'DATA' OR
1001 UPPER(l_Col_one) = 'FILES')
1002 AND (UPPER(l_Col_two) = 'START' OR UPPER(l_Col_two) = 'END' ) THEN
1003 l_fatal_error_message := 'Syntax error : Descriptor incorrectly '
1004 || 'terminated. - ' || p_string || ' END - Is not included.';
1005 RAISE e_fatal_error;
1006 END IF;
1007 END IF;
1008
1009 EXCEPTION
1010 WHEN no_data_found THEN
1011 l_fatal_error_message := 'Data Incorrectly Terminated ' ||
1012 'Error occured at row ' || g_counter;
1013 RAISE e_fatal_error;
1014 END;
1015 END LOOP;
1016 --
1017 hr_du_utility.message('ROUT','exit:hr_du_di_insert.general_extract', 30);
1018 --
1019
1020 --error handling
1021 EXCEPTION
1022 WHEN e_fatal_error THEN
1023 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.general_extract',
1024 l_fatal_error_message, 'R');
1025 RAISE;
1026 WHEN OTHERS THEN
1027 hr_du_utility.error(SQLCODE,
1028 'hr_du_di_insert.general_extract','(none)', 'R');
1029 RAISE;
1030
1031 END GENERAL_EXTRACT;
1032
1033
1034 -- ------------------------- EXTRACT_API_LOCATIONS ----------------------
1035 -- Description: Calls General_extract with three extra variables to handle
1036 -- removing the locations of the API files
1037 --
1038 -- Input Parameters
1039 -- p_filehandle - the file to be worked on
1040 --
1041 -- p_upload_id - HR_DU_UPLOAD_ID
1042 --
1043 -- ------------------------------------------------------------------------
1044 PROCEDURE EXTRACT_API_LOCATIONS (p_filehandle IN utl_file.file_type,
1045 p_upload_id IN NUMBER)
1046
1047 IS
1048 l_file_descriptor VARCHAR2(2000) :='F';
1049 l_Column_one_header VARCHAR2(2000) :='FILES';
1050 l_upload_header_id NUMBER := null;
1051
1052 BEGIN
1053 --
1054 hr_du_utility.message('ROUT','entry:hr_du_di_insert.extract_api_locations'
1055 , 5);
1056 --
1057 GENERAL_Extract (p_filehandle, p_upload_id, l_upload_header_id,
1058 l_Column_one_header, l_file_descriptor);
1059 --
1060 hr_du_utility.message('ROUT','exit:hr_du_di_insert.extract_api_locations'
1061 , 10);
1062 --
1063
1064 --error handling
1065 EXCEPTION
1066 WHEN OTHERS THEN
1067 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.extract_api_locations'
1068 ,'(none)', 'R');
1069 RAISE;
1070
1071 END EXTRACT_API_LOCATIONS;
1072
1073
1074 -- ------------------------- EXTRACT_HEADERS ------------------------------
1075 -- Description: Calls General_extract with three extra variables to deal
1076 -- with the headers of the file
1077 --
1078 -- Input Parameters
1079 -- p_filehandle - the file to be worked on
1080 --
1081 -- p_upload_id - HR_DU_UPLOAD_ID
1082 --
1083 -- ------------------------------------------------------------------------
1084 PROCEDURE EXTRACT_HEADERS (p_filehandle IN utl_file.file_type,
1085 p_upload_id IN NUMBER)
1086 IS
1087 l_file_descriptor VARCHAR2(2000) :='D';
1088 l_Column_one_header VARCHAR2(2000) :='HEADER';
1089 l_upload_header_id NUMBER := null;
1090
1091 BEGIN
1092 --
1093 hr_du_utility.message('ROUT','entry:hr_du_di_insert.extract_headers', 5);
1094 --
1095 GENERAL_Extract(p_filehandle, p_upload_id, l_upload_header_id,
1096 l_Column_one_header, l_file_descriptor);
1097 VALIDATE_HEADER_DESCRIPTORS(p_upload_id);
1098 --
1099 hr_du_utility.message('ROUT','exit:hr_du_di_insert.extract_headers', 10);
1100 --
1101
1102 --error handling
1103 EXCEPTION
1104 WHEN OTHERS THEN
1105 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.extract_headers',
1106 '(none)', 'R');
1107 RAISE;
1108
1109 END EXTRACT_HEADERS;
1110
1111
1112 -- ------------------------- EXTRACT_DESCRIPTORS -------------------------
1113 -- Description: Calls General_extract with two extra variables to handle
1114 -- the descriptor part of the input file. These are found at the top of
1115 -- each API file. HR_DU_RULES is then called to validate the header's
1116 -- set up.
1117 --
1118 -- Input Parameters
1119 -- p_filehandle - the file to be worked on
1120 --
1121 -- p_upload_id - HR_DU_UPLOAD_ID
1122 --
1123 -- p_upload_header_id - HR_DU_UPLOAD_HEADER_ID
1124 --
1125 -- ------------------------------------------------------------------------
1126 FUNCTION EXTRACT_DESCRIPTORS (p_filehandle IN utl_file.file_type,
1127 p_upload_id IN NUMBER, p_upload_header_id IN NUMBER)
1128 RETURN VARCHAR2
1129 IS
1130 l_file_descriptor VARCHAR2(2000) :='D';
1131 l_Column_one_header VARCHAR2(2000) :='DESCRIPTOR';
1132 l_reference_type VARCHAR2(100);
1133
1134 BEGIN
1135 --
1136 hr_du_utility.message('ROUT','entry:hr_du_di_insert.extract_descriptors',
1137 5);
1138 --
1139 GENERAL_Extract(p_filehandle, p_upload_id, p_upload_header_id,
1140 l_Column_one_header, l_file_descriptor);
1141 VALIDATE_SHEET_DESCRIPTORS
1142 (p_upload_id,p_upload_header_id);
1143 HR_DU_RULES.VALIDATE_USER_KEY_SETUP
1144 (p_upload_header_id, p_upload_id);
1145 l_reference_type := HR_DU_RULES.VALIDATE_REFERENCING
1146 (p_upload_header_id, p_upload_id);
1147 HR_DU_RULES.VALIDATE_STARTING_POINT
1148 (p_upload_header_id, p_upload_id);
1149 HR_DU_RULES.PROCESS_ORDER_PRESENT
1150 (p_upload_header_id);
1151 HR_DU_RULES.API_PRESENT_AND_CORRECT
1152 (p_upload_header_id, p_upload_id);
1153 --
1154 hr_du_utility.message('ROUT','exit:hr_du_di_insert.extract_descriptors',
1155 10);
1156 hr_du_utility.message('PARA', '(l_reference_type - ' || l_reference_type
1157 || ')' , 15);
1158 --
1159
1160 RETURN l_reference_type;
1161
1162 --error handling
1163 EXCEPTION
1164 WHEN OTHERS THEN
1165 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.extract_descriptors',
1166 '(none)', 'R');
1167 RAISE;
1168
1169 END EXTRACT_DESCRIPTORS;
1170
1171
1172 -- ------------------------- HANDLE_API_FILES ----------------------------
1173 -- Description: This procedure loops around the number of API files to be
1174 -- processed and imports all of the relevant data into the corresponding
1175 -- ORACLE tables.
1176 --
1177 -- Input Parameters
1178 --
1179 -- p_Location - The location of the directory where the files
1180 -- will be held
1181 --
1182 -- p_upload_id - HR_DU_UPLOAD_ID
1183 --
1184 -- ------------------------------------------------------------------------
1185 PROCEDURE HANDLE_API_FILES(p_Location IN VARCHAR2, p_upload_id IN NUMBER)
1186 IS
1187
1188 --this cursor stores the API id, name and location, from the descriptor
1189 --API table
1190 CURSOR csr_files IS
1191 SELECT des.descriptor_id, api.api_module_id, upper(api.module_NAME),
1192 des.VALUE
1193 FROM hr_api_modules api,
1194 hr_du_descriptors des
1195 WHERE upper(api.module_NAME) = upper(des.DESCRIPTOR)
1196 AND des.DESCRIPTOR_TYPE = 'F'
1197 AND des.upload_id = p_upload_id;
1198
1199
1200 --This cursor identifies the api_names that aren't spelt correctly
1201 --within the flat file.
1202 CURSOR csr_incorrect IS
1203 SELECT des.DESCRIPTOR
1204 FROM hr_du_descriptors des
1205 WHERE des.DESCRIPTOR_TYPE = 'F'
1206 AND des.upload_id = p_upload_id
1207 AND upper(des.DESCRIPTOR) NOT IN ( SELECT upper(des.DESCRIPTOR)
1208 FROM hr_api_modules api,
1209 hr_du_descriptors des
1210 WHERE upper(api.module_NAME) =
1211 upper(des.DESCRIPTOR)
1212 AND des.DESCRIPTOR_TYPE = 'F'
1213 AND des.upload_id = p_upload_id);
1214
1215
1216 e_fatal_error EXCEPTION;
1217 l_fatal_error_message VARCHAR2(2000);
1218 l_file_record csr_files%ROWTYPE;
1219 l_filehandle UTL_FILE.FILE_TYPE;
1220 l_upload_header_id NUMBER;
1221 l_reference_type VARCHAR2(10);
1222 l_descriptor VARCHAR2(50);
1223 l_table_size NUMBER := 0;
1224 l_original_upload_header_id NUMBER;
1225 l_found_value BOOLEAN;
1226 l_next_table_value NUMBER;
1227
1228
1229 BEGIN
1230 --
1231 hr_du_utility.message('ROUT','entry:hr_du_di_insert.handle_api_files', 5);
1232 hr_du_utility.message('PARA', '(p_Location - ' || p_Location ||
1233 ')(p_upload_id - ' || p_upload_id || ')' , 10);
1234 --
1235 g_header_table.delete;
1236 OPEN csr_incorrect;
1237 FETCH csr_incorrect INTO l_descriptor;
1238 IF csr_incorrect%FOUND THEN
1239 l_fatal_error_message := 'Unknown api name '|| l_descriptor ||
1240 ' on header sheet ';
1241 RAISE e_fatal_error;
1242 END IF;
1243 CLOSE csr_incorrect;
1244
1245 Update_Upload_table(p_upload_id);
1246 OPEN csr_files;
1247 LOOP
1248 FETCH csr_files INTO l_file_record;
1249 EXIT WHEN csr_files%NOTFOUND;
1250
1251 SELECT HR_DU_UPLOAD_HEADERS_S.nextval
1252 INTO l_upload_header_id
1253 FROM dual;
1254
1255
1256 hr_du_utility.message('INFO','Insert statement', 15);
1257 --Creating upload_header
1258 INSERT INTO HR_DU_UPLOAD_HEADERS(
1259 UPLOAD_HEADER_ID, UPLOAD_ID, API_MODULE_ID, STATUS,
1260 LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
1261 CREATED_BY, CREATION_DATE)
1262 VALUES(
1263 l_upload_header_id,
1264 p_upload_id,
1265 l_file_record.API_MODULE_ID,
1266 'NS',
1267 sysdate,
1268 1,
1269 1,
1270 1,
1271 sysdate);
1272 COMMIT;
1273
1274
1275 l_original_upload_header_id := l_upload_header_id;
1276 -- open the new file relating to the current API
1277 l_filehandle := Open_file (p_Location, l_file_record.VALUE);
1278 hr_du_utility.message('INFO','File Opened', 20);
1279
1280 l_reference_type := Extract_descriptors(l_filehandle, p_upload_id,
1281 l_upload_header_id);
1282 hr_du_utility.message('INFO','Extracted Descriptors;', 25);
1283
1284
1285 l_found_value := FALSE;
1286 l_table_size := g_header_table.count;
1287 hr_du_utility.message('INFO','l_table_size : ' || l_table_size , 30);
1288
1289
1290 --This statement loops around the table checking to see whether
1291 --the API_module_id has already been assigned to an upload_header
1292 --if so the header's id which has been assigned is taken.
1293 FOR i IN 1..l_table_size LOOP
1294 --
1295 IF g_header_table(i).r_api_module_id = l_file_record.API_MODULE_ID THEN
1296 l_upload_header_id := g_header_table(i).r_upload_header_id;
1297 l_found_value := TRUE;
1298
1299 --Here I'll set the API value in the descriptor table to null
1300 --so that it isn't retrieved in later searches
1301 UPDATE hr_du_descriptors
1302 SET value = NULL
1303 WHERE upload_header_id = l_original_upload_header_id
1304 AND descriptor = 'API';
1305
1306 COMMIT;
1307 EXIT;
1308 END IF;
1309 END LOOP;
1310
1311 --Adds the entry into the table for the next loop;
1312 l_next_table_value := l_table_size + 1;
1313
1314 IF l_found_value = FALSE THEN
1315 g_header_table(l_next_table_value).r_api_module_id
1316 := l_file_record.API_MODULE_ID;
1317 g_header_table(l_next_table_value).r_upload_header_id
1318 := l_upload_header_id;
1319 END IF;
1320
1321 Extract_lines(l_filehandle, p_upload_id, l_original_upload_header_id,
1322 l_reference_type, l_file_record.API_MODULE_ID,
1323 l_upload_header_id);
1324
1325 --Enter search to vaildate that no two identical API id's have
1326 --been entered in the flat file
1327 VALIDATE_API_IDS(l_upload_header_id);
1328 END LOOP;
1329 CLOSE csr_files;
1330
1331 --
1332 hr_du_utility.message('ROUT','exit:hr_du_di_insert.handle_api_files', 30);
1333 --
1334
1335 --error handling
1336 EXCEPTION
1337 WHEN e_fatal_error THEN
1338 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.handle_api_files',
1339 l_fatal_error_message, 'R');
1340 RAISE;
1341 WHEN OTHERS THEN
1342 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.handle_api_files',
1343 '(none)', 'R');
1344 RAISE;
1345
1346 END HANDLE_API_FILES;
1347
1348
1349 -- -------------------------- UPDATE_UPLOAD_TABLE -------------------------
1350 -- Description: This procedure updates the upload table from the information
1351 -- that came in through the headers in the first file.
1352 --
1353 -- Input Parameters
1354 --
1355 -- p_upload_id - HR_DU_UPLOAD_ID
1356 -- ------------------------------------------------------------------------
1357 PROCEDURE UPDATE_UPLOAD_TABLE (p_upload_id IN NUMBER)
1358 IS
1359
1360 e_fatal_error EXCEPTION;
1361 l_fatal_error_message VARCHAR2(2000);
1362 l_business_group_file VARCHAR2(2000);
1363 l_business_group_profile VARCHAR2(80);
1364 l_business_group_name VARCHAR2(80);
1365 l_business_group_id NUMBER;
1366 l_batch_name VARCHAR2(200);
1367 l_global_data VARCHAR2(2000);
1368
1369 --This cursor extracts the business group name from the upload table
1370 CURSOR csr_business_group IS
1371 SELECT VALUE
1372 FROM hr_du_descriptors
1373 WHERE DESCRIPTOR = 'BUSINESS GROUP'
1374 AND UPLOAD_ID = p_upload_id;
1375
1376 --This cursor extracts the global data flag from the upload table
1377 CURSOR csr_global_data IS
1378 SELECT VALUE
1379 FROM hr_du_descriptors
1380 WHERE DESCRIPTOR = 'GLOBAL DATA'
1381 AND UPLOAD_ID = p_upload_id;
1382
1383 --This cursor extracts the batch name from the upload table
1384 CURSOR csr_batch_name IS
1385 SELECT VALUE
1386 FROM hr_du_descriptors
1387 WHERE DESCRIPTOR = 'BATCH NAME'
1388 AND UPLOAD_ID = p_upload_id;
1389
1390 --This cursor extracts the business group name from the id value
1391 CURSOR csr_business_group_lookup IS
1392 SELECT NAME
1393 FROM per_business_groups
1394 WHERE BUSINESS_GROUP_ID = l_business_group_id;
1395
1396 BEGIN
1397 --
1398 hr_du_utility.message('ROUT',
1399 'entry:hr_du_di_insert.update_upload_table', 5);
1400 hr_du_utility.message('PARA',
1401 '(p_upload_id - ' || p_upload_id || ')' , 10);
1402 --
1403
1404 OPEN csr_batch_name;
1405 FETCH csr_batch_name INTO l_batch_name;
1406 IF csr_batch_name%NOTFOUND THEN
1407 l_fatal_error_message := 'Error BATCH NAME value not found in ' ||
1408 'header file';
1409 RAISE e_fatal_error;
1410 END IF;
1411 CLOSE csr_batch_name;
1412
1413 -- get business group name from descriptors table
1414 OPEN csr_business_group;
1415 FETCH csr_business_group INTO l_business_group_file;
1416 CLOSE csr_business_group;
1417
1418 -- get business group name from profile
1419 -- This should be set at the responsibility level if there are multiple
1420 -- business groups or at the site level for a single business group
1421 fnd_profile.get('PER_BUSINESS_GROUP_ID', l_business_group_id);
1422 OPEN csr_business_group_lookup;
1423 FETCH csr_business_group_lookup INTO l_business_group_profile;
1424 CLOSE csr_business_group_lookup;
1425
1426 -- validate business groups
1427 hr_du_utility.message('INFO','l_business_group_profile - ' ||
1428 l_business_group_profile, 15);
1429 hr_du_utility.message('INFO','l_business_group_file - ' ||
1430 l_business_group_file, 15);
1431 hr_du_rules.validate_business_group(l_business_group_profile,
1432 l_business_group_file);
1433
1434
1435 -- see if we are uploading global data
1436 OPEN csr_global_data;
1437 FETCH csr_global_data INTO l_global_data;
1438 CLOSE csr_global_data;
1439
1440
1441 IF (UPPER(l_global_data) = 'Y')
1442 OR (UPPER(l_global_data) = 'YES') THEN
1443 l_business_group_name := NULL;
1444 ELSE
1445 l_business_group_name := l_business_group_profile;
1446 END IF;
1447
1448 hr_du_utility.message('INFO','Using business group name - ' ||
1449 NVL(l_business_group_name,'NULL'), 15);
1450
1451
1452 UPDATE hr_du_uploads
1453 SET BUSINESS_GROUP_NAME = l_business_group_name
1454 WHERE UPLOAD_ID = p_upload_id;
1455
1456 -- update descriptors table to ensure that the correct value is used
1457 UPDATE hr_du_descriptors
1458 SET VALUE = l_business_group_name
1459 WHERE DESCRIPTOR = 'BUSINESS GROUP'
1460 AND UPLOAD_ID = p_upload_id;
1461
1462 -- check if a row has been updated, otherwise insert a record
1463 IF SQL%ROWCOUNT = 0 THEN
1467 LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY,
1464 INSERT INTO HR_DU_DESCRIPTORS(
1465 DESCRIPTOR_ID, UPLOAD_ID, UPLOAD_HEADER_ID,
1466 DESCRIPTOR, VALUE, DESCRIPTOR_TYPE, LAST_UPDATE_DATE,
1468 CREATION_DATE)
1469 VALUES(
1470 HR_DU_DESCRIPTORS_S.nextval,
1471 p_upload_id,
1472 null,
1473 'BUSINESS GROUP',
1474 l_business_group_name,
1475 'D',
1476 sysdate,
1477 1,
1478 1,
1479 1,
1480 sysdate);
1481 hr_du_utility.message('INFO','Row inserted into descriptors table', 15);
1482 COMMIT;
1483 ELSE
1484 hr_du_utility.message('INFO','Row updated in descriptors table', 20);
1485 END IF;
1486
1487 --
1488 hr_du_utility.message('ROUT',
1489 'exit:hr_du_di_insert.update_upload_table', 25);
1490 --
1491
1492 --error handling
1493 EXCEPTION
1494 WHEN e_fatal_error THEN
1495 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.update_upload_table',
1496 l_fatal_error_message, 'R');
1497 RAISE;
1498 WHEN OTHERS THEN
1499 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.update_upload_table',
1500 '(none)', 'R');
1501 RAISE;
1502
1503 END UPDATE_UPLOAD_TABLE;
1504
1505
1506 -- ------------------------- RETURN_FILE_NAME -----------------------------
1507 -- Description: This function takes a upload_id and returns the
1508 -- HR_DU_UPLOAD.SOURCE value
1509 --
1510 -- Input Parameters
1511 --
1512 -- p_upload_id - HR_DU_UPLOAD_ID
1513 --
1514 -- ------------------------------------------------------------------------
1515 FUNCTION RETURN_FILE_NAME(p_upload_id IN NUMBER) RETURN VARCHAR2
1516 IS
1517
1518 CURSOR csr_source IS
1519 SELECT SOURCE
1520 FROM hr_du_uploads
1521 WHERE UPLOAD_ID = p_upload_id;
1522
1523 e_fatal_error EXCEPTION;
1524 l_fatal_error_message VARCHAR2(2000);
1525 l_source_name VARCHAR2(50);
1526
1527 BEGIN
1528 --
1529 hr_du_utility.message('ROUT','entry:hr_du_di_insert.return_file_name', 5);
1530 hr_du_utility.message('PARA',
1531 '(p_upload_id - ' || p_upload_id || ')' , 10);
1532 --
1533 OPEN csr_source;
1534 FETCH csr_source INTO l_source_name;
1535 IF csr_source%NOTFOUND THEN
1536 l_fatal_error_message := 'Error File name not found in ' ||
1537 'HR_DU_UPLOAD table.';
1538 RAISE e_fatal_error;
1539 END IF;
1540 CLOSE csr_source;
1541 --
1542 hr_du_utility.message('ROUT','exit:hr_du_di_insert.return_file_name', 15);
1543 hr_du_utility.message('PARA', '(l_source_name - ' || l_source_name ||
1544 ')' , 20);
1545 --
1546 RETURN l_source_name;
1547
1548 --error handling
1549 EXCEPTION
1550 WHEN e_fatal_error THEN
1551 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.return_file_name',
1552 l_fatal_error_message, 'R');
1553 RAISE;
1554 WHEN OTHERS THEN
1555 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.return_file_name',
1556 '(none)', 'R');
1557 RAISE;
1558
1559 END RETURN_FILE_NAME;
1560
1561
1562 -- ------------------------- OPEN_FILE ------------------------------------
1563 -- Description: Opens the specified file in the named location
1564 --
1565 -- Input Parameters
1566 -- p_file_location - the file to be worked on
1567 --
1568 -- p_file_name - defines the character that separates the words
1569 --
1570 -- Output Parameters
1571 -- l_filehandle - handle to the file so it can be referenced later
1572 --
1573 -- ------------------------------------------------------------------------
1574 FUNCTION OPEN_FILE (p_file_location IN varchar2, p_file_name IN varchar2)
1575 RETURN utl_file.file_type
1576 IS
1577 --
1578 CURSOR csr_valid_profile IS
1579 SELECT value
1580 FROM v$parameter
1581 WHERE name='utl_file_dir';
1582 --
1583 l_filehandle UTL_FILE.FILE_TYPE;
1584 l_location VARCHAR2(2000);
1585 l_valid_profile VARCHAR2(2000);
1586 --
1587 BEGIN
1588 --
1589 hr_du_utility.message('ROUT','entry:hr_du_di_insert.open_file', 5);
1590 hr_du_utility.message('PARA', '(p_file_location - ' || p_file_location ||
1591 ')(p_file_name - ' || p_file_name || ')' , 10);
1592 --
1593 OPEN csr_valid_profile;
1594 FETCH csr_valid_profile INTO l_valid_profile;
1595 CLOSE csr_valid_profile;
1596 --
1597 fnd_profile.get('PER_DATA_EXCHANGE_DIR', l_location);
1598 --
1599 -- Output additional information to the log file concerning header file
1600 --
1601 hr_du_utility.message('SUMM', 'File name ->' || p_file_name , 25);
1602 hr_du_utility.message('SUMM', 'File location/HR: Data Exchange Directory profile option->' || l_location , 30);
1603 hr_du_utility.message('SUMM', 'Valid options for HR: Data Exchange Directory profile ->' || l_valid_profile , 35);
1604 --
1605 l_filehandle := utl_file.fopen(p_file_location, p_file_name, 'r', 32767);
1606 g_counter := 0;
1607 --
1608 hr_du_utility.message('ROUT','exit:hr_du_di_insert.open_file', 15);
1609 hr_du_utility.message('PARA', '(l_filehandle - File Type )' , 20);
1610 --
1611 RETURN l_filehandle;
1612 --error handling
1613 EXCEPTION
1614 WHEN OTHERS THEN
1615 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.Open_file',
1616 ' ERROR Opening - ' || p_file_name ||
1617 '. File may not exist or spelt incorrectly', 'R');
1618 RAISE;
1619
1620 END OPEN_FILE;
1621
1622
1623 -- ------------------------- EXTRACT_LINES ------------------------------
1624 -- Description: Takes the lines from the spreadsheet and inserts them in
1625 -- the HR_DU_UPLOAD_LINES and takes into account the fact that all lines
1626 -- must only have one header for one API. So if there are two headers with
1627 -- the same API then all lines are placed into the first one.
1628 --
1629 -- Input Parameters
1630 -- p_filehandle - the file to be worked on
1631 --
1632 -- p_upload_id - HR_DU_UPLOAD_ID
1633 --
1634 -- p_original_upload_header_id - The original upload_header that this
1635 -- line is connected to
1636 --
1637 -- p_reference_type - Either PC or CP
1638 --
1639 -- p_api_module_id - Identifies the API for this header
1640 --
1641 -- p_upload_header_id - The Header that the line is placed
1642 -- with so all the lines will be in a
1643 -- header with the ownership of the API
1644 -- ------------------------------------------------------------------------
1645 PROCEDURE EXTRACT_LINES(p_filehandle IN utl_file.file_type,
1646 p_upload_id IN NUMBER, p_original_upload_header_id IN NUMBER,
1647 p_reference_type IN VARCHAR2, p_api_module_id IN NUMBER,
1648 p_upload_header_id IN NUMBER)
1649 IS
1650
1651 e_fatal_error EXCEPTION;
1652 l_fatal_error_message VARCHAR2(2000);
1653 l_Col_one VARCHAR2(2000);
1654 l_Col_two VARCHAR2(2000);
1655 l_data_line VARCHAR2(32767);
1656 l_num_loop NUMBER;
1657 l_line_id NUMBER;
1658 l_line_type VARCHAR2(2) :='C';
1659 l_word VARCHAR2(2000);
1660 l_none_blank NUMBER;
1661 l_valid_column VARCHAR2(200);
1662 l_chunk_size_master NUMBER;
1663 l_chunk_size_slave NUMBER;
1664 l_temp_number NUMBER;
1665 l_header_file VARCHAR2(200);
1666
1667 BEGIN
1668 --
1669 hr_du_utility.message('ROUT','entry:hr_du_di_insert.extract_lines', 5);
1670 hr_du_utility.message('ROUT', '(p_filehandle - File Type )' ||
1671 '(p_upload_id - ' || p_upload_id ||
1672 ')(p_original_upload_header_id - ' || p_original_upload_header_id ||
1673 ')(p_reference_type - ' || p_reference_type ||
1674 ')(p_api_module_id - ' || p_api_module_id ||
1675 ')(p_upload_header_id - ' || p_upload_header_id ||
1676 ')' , 10);
1677 --
1678 l_chunk_size_master := hr_du_utility.chunk_size;
1679
1680 LOOP
1681 BEGIN
1682 l_data_line := next_line(p_filehandle, p_original_upload_header_id);
1683 hr_du_utility.message('INFO',
1684 'Data Line Header - ' || l_data_line , 15);
1685 --call return_word to get the first word which is the descriptor
1686 l_Col_one := Return_Word (l_data_line, 1);
1687 l_Col_two := Return_Word (l_data_line, 2);
1688
1689 EXIT WHEN (UPPER(l_Col_one) = 'DATA')
1690 AND (UPPER(l_Col_two) = 'START');
1691
1692 --checks for syntax errors in the spreadsheet header
1693 IF (UPPER(l_Col_one) = 'DESCRIPTOR' OR UPPER(l_Col_one) = 'FILES')
1694 AND (UPPER(l_Col_two) = 'END' OR UPPER(l_Col_two) = 'START') THEN
1695 l_fatal_error_message := 'File incorrectly started ' ||
1696 'Encountered - ' || l_Col_one || ' ' || l_Col_two || ' tag' ||
1697 ' before DATA START ' ;
1698 RAISE e_fatal_error;
1699 END IF;
1700
1701 EXCEPTION
1702 WHEN no_data_found THEN
1703 l_fatal_error_message := 'Incorrect syntax for header';
1704 RAISE e_fatal_error;
1705 END;
1706 END LOOP;
1707 --
1708 l_chunk_size_slave := l_chunk_size_master;
1709 LOOP
1710 BEGIN
1711
1712 l_data_line := next_line(p_filehandle, p_original_upload_header_id);
1713 hr_du_utility.message('INFO','Data Line Data - ' || l_data_line , 20);
1714
1715 --Add loop to check that there is valid data on the line and it
1716 --isn't just tabs and spaces.
1717
1718 --Only looking for numbers
1719 FOR i IN 1..9 LOOP
1720 l_none_blank := INSTRB(l_data_line, to_char(i));
1721 IF l_none_blank > 0 THEN
1722 EXIT;
1723 END IF;
1724 END LOOP;
1725 --Only looking for upper case letters
1726 IF (l_none_blank = 0) THEN
1727 FOR i IN 1..26 LOOP
1728 l_none_blank := INSTRB(l_data_line, Char_table(i));
1729 IF l_none_blank > 0 THEN
1730 EXIT;
1731 END IF;
1732 END LOOP;
1733 END IF;
1734
1735 --Only looking for lower case letters
1736 IF (l_none_blank = 0) THEN
1737 FOR i IN 27..52 LOOP
1738 l_none_blank := INSTRB(l_data_line, Char_table(i));
1739 IF l_none_blank > 0 THEN
1740 EXIT;
1741 END IF;
1742 END LOOP;
1743 END IF;
1744
1745 IF (l_none_blank = 0) THEN
1746 null;
1747 ELSE
1748 l_num_loop := Words_On_Line (l_data_line);
1749 l_Col_one := Return_Word (l_data_line, 1);
1750 l_Col_two := Return_Word (l_data_line, 2);
1751 EXIT WHEN (UPPER(l_Col_one) = 'DATA')
1752 AND (UPPER(l_Col_two) = 'END');
1753
1754 --checks to see if the file has been terinated properly
1755 IF (UPPER(l_Col_one) = 'DESCRIPTOR' OR UPPER(l_Col_one) = 'FILES')
1756 AND (UPPER(l_Col_two) = 'START' OR UPPER(l_Col_one) = 'FILES') THEN
1757 l_fatal_error_message := 'Syntax error File incorrectly ' ||
1758 'terminated. Cause - ' || l_Col_one || ' ' || l_Col_two || ' tag.' ||
1759 ' Sholud have encountered the - DATA END - tag.';
1760 RAISE e_fatal_error;
1761 END IF;
1762
1763 PARSE_LINE_TO_TABLE (l_data_line, p_original_upload_header_id, l_line_type);
1764
1765 --Simple check of the column heading to make sure it's valid and
1766 --check that the user has not left out the column headings
1767 IF l_line_type = 'C' THEN
1768 l_word := Return_Word (l_data_line, 2);
1769 l_valid_column := hr_du_dp_pc_conversion.
1770 GENERAL_REFERENCING_COLUMN(l_word, p_api_module_id, 'D');
1771 IF (l_word <> 'ID') AND (l_valid_column IS NULL) THEN
1772 l_fatal_error_message := l_word || ' is not a valid column ' ||
1773 ' heading';
1774 RAISE e_fatal_error;
1775 END IF;
1776
1777 --Checks to make sure that the values in PVAL001 are all Numerical.
1778 ELSE
1779 --
1780 BEGIN
1781 l_temp_number := to_number(g_line_table(1));
1782 EXCEPTION
1783 WHEN value_error THEN
1784 l_header_file := hr_du_rules.RETURN_UPLOAD_HEADER_FILE
1785 (p_original_upload_header_id);
1786 l_fatal_error_message := g_line_table(1) || ' is not a valid '
1787 || 'column id on line ' || g_counter
1788 || ' in the file ' || l_header_file ;
1789 RAISE e_fatal_error;
1790 END;
1791 END IF;
1792
1793 --Makes sure that the column names are not duplicated if there
1794 --are two upload_headers with the same API
1795 IF (p_original_upload_header_id <> p_upload_header_id) AND
1796 (l_line_type = 'C') THEN
1797 null;
1798 ELSE
1799 hr_du_utility.message('INFO','Insert Statement Start ' , 25);
1800
1801 INSERT INTO hr_du_upload_lines(
1802 UPLOAD_LINE_ID, UPLOAD_HEADER_ID, BATCH_LINE_ID,
1803 STATUS, REFERENCE_TYPE, LINE_TYPE, LAST_UPDATE_DATE,
1804 LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY,
1805 CREATION_DATE, DI_LINE_NUMBER, ORIGINAL_UPLOAD_HEADER_ID,
1806 PVAL001, PVAL002, PVAL003, PVAL004, PVAL005, PVAL006,
1807 PVAL007, PVAL008, PVAL009, PVAL010, PVAL011, PVAL012,
1808 PVAL013, PVAL014, PVAL015, PVAL016, PVAL017, PVAL018,
1809 PVAL019, PVAL020, PVAL021, PVAL022, PVAL023, PVAL024,
1810 PVAL025, PVAL026, PVAL027, PVAL028, PVAL029, PVAL030,
1811 PVAL031, PVAL032, PVAL033, PVAL034, PVAL035, PVAL036,
1812 PVAL037, PVAL038, PVAL039, PVAL040, PVAL041, PVAL042,
1813 PVAL043, PVAL044, PVAL045, PVAL046, PVAL047, PVAL048,
1814 PVAL049, PVAL050, PVAL051, PVAL052, PVAL053, PVAL054,
1815 PVAL055, PVAL056, PVAL057, PVAL058, PVAL059, PVAL060,
1816 PVAL061, PVAL062, PVAL063, PVAL064, PVAL065, PVAL066,
1817 PVAL067, PVAL068, PVAL069, PVAL070, PVAL071, PVAL072,
1818 PVAL073, PVAL074, PVAL075, PVAL076, PVAL077, PVAL078,
1819 PVAL079, PVAL080, PVAL081, PVAL082, PVAL083, PVAL084,
1820 PVAL085, PVAL086, PVAL087, PVAL088, PVAL089, PVAL090,
1821 PVAL091, PVAL092, PVAL093, PVAL094, PVAL095, PVAL096,
1825 PVAL115, PVAL116, PVAL117,PVAL118, PVAL119, PVAL120,
1822 PVAL097, PVAL098, PVAL099, PVAL100, PVAL101, PVAL102,
1823 PVAL103, PVAL104, PVAL105,PVAL106, PVAL107, PVAL108,
1824 PVAL109, PVAL110, PVAL111,PVAL112, PVAL113, PVAL114,
1826 PVAL121, PVAL122, PVAL123,PVAL124, PVAL125, PVAL126,
1827 PVAL127, PVAL128, PVAL129,PVAL130, PVAL131, PVAL132,
1828 PVAL133, PVAL134, PVAL135,PVAL136, PVAL137, PVAL138,
1829 PVAL139, PVAL140, PVAL141,PVAL142, PVAL143, PVAL144,
1830 PVAL145, PVAL146, PVAL147,PVAL148, PVAL149, PVAL150,
1831 PVAL151, PVAL152, PVAL153,PVAL154, PVAL155, PVAL156,
1832 PVAL157, PVAL158, PVAL159,PVAL160, PVAL161, PVAL162,
1833 PVAL163, PVAL164, PVAL165,PVAL166, PVAL167, PVAL168,
1834 PVAL169, PVAL170, PVAL171,PVAL172, PVAL173, PVAL174,
1835 PVAL175, PVAL176, PVAL177,PVAL178, PVAL179, PVAL180,
1836 PVAL181, PVAL182, PVAL183,PVAL184, PVAL185, PVAL186,
1837 PVAL187, PVAL188, PVAL189,PVAL190, PVAL191, PVAL192,
1838 PVAL193, PVAL194, PVAL195,PVAL196, PVAL197, PVAL198,
1839 PVAL199, PVAL200, PVAL201,PVAL202, PVAL203, PVAL204,
1840 PVAL205, PVAL206, PVAL207,PVAL208, PVAL209, PVAL210,
1841 PVAL211, PVAL212, PVAL213,PVAL214, PVAL215, PVAL216,
1842 PVAL217, PVAL218, PVAL219,PVAL220, PVAL221, PVAL222,
1843 PVAL223, PVAL224, PVAL225,PVAL226, PVAL227, PVAL228,
1844 PVAL229, PVAL230 )
1845 VALUES(
1846 HR_DU_UPLOAD_LINES_S.nextval, p_upload_header_id, null, 'NS',
1847 p_reference_type, l_line_type, sysdate,
1848 1, 1, 1, sysdate, g_counter, p_original_upload_header_id,
1849 g_line_table(1), g_line_table(2), g_line_table(3),
1850 g_line_table(4), g_line_table(5), g_line_table(6),
1851 g_line_table(7), g_line_table(8), g_line_table(9),
1852 g_line_table(10), g_line_table(11), g_line_table(12),
1853 g_line_table(13), g_line_table(14), g_line_table(15),
1854 g_line_table(16), g_line_table(17), g_line_table(18),
1855 g_line_table(19), g_line_table(20), g_line_table(21),
1856 g_line_table(22), g_line_table(23), g_line_table(24),
1857 g_line_table(25), g_line_table(26), g_line_table(27),
1858 g_line_table(28), g_line_table(29), g_line_table(30),
1859 g_line_table(31), g_line_table(32), g_line_table(33),
1860 g_line_table(34), g_line_table(35), g_line_table(36),
1861 g_line_table(37), g_line_table(38), g_line_table(39),
1862 g_line_table(40), g_line_table(41), g_line_table(42),
1863 g_line_table(43), g_line_table(44), g_line_table(45),
1864 g_line_table(46), g_line_table(47), g_line_table(48),
1865 g_line_table(49), g_line_table(50), g_line_table(51),
1866 g_line_table(52), g_line_table(53), g_line_table(54),
1867 g_line_table(55), g_line_table(56), g_line_table(57),
1868 g_line_table(58), g_line_table(59), g_line_table(60),
1869 g_line_table(61), g_line_table(62), g_line_table(63),
1870 g_line_table(64), g_line_table(65), g_line_table(66),
1871 g_line_table(67), g_line_table(68), g_line_table(69),
1872 g_line_table(70), g_line_table(71), g_line_table(72),
1873 g_line_table(73), g_line_table(74), g_line_table(75),
1874 g_line_table(76), g_line_table(77), g_line_table(78),
1875 g_line_table(79), g_line_table(80), g_line_table(81),
1876 g_line_table(82), g_line_table(83), g_line_table(84),
1877 g_line_table(85), g_line_table(86), g_line_table(87),
1878 g_line_table(88), g_line_table(89), g_line_table(90),
1879 g_line_table(91), g_line_table(92), g_line_table(93),
1880 g_line_table(94), g_line_table(95), g_line_table(96),
1881 g_line_table(97), g_line_table(98), g_line_table(99),
1882 g_line_table(100),g_line_table(101),g_line_table(102),
1883 g_line_table(103),g_line_table(104),g_line_table(105),
1884 g_line_table(106),g_line_table(107),g_line_table(108),
1885 g_line_table(109),g_line_table(110),g_line_table(111),
1886 g_line_table(112),g_line_table(113),g_line_table(114),
1887 g_line_table(115),g_line_table(116),g_line_table(117),
1888 g_line_table(118),g_line_table(119),g_line_table(120),
1889 g_line_table(121),g_line_table(122),g_line_table(123),
1890 g_line_table(124),g_line_table(125),g_line_table(126),
1891 g_line_table(127),g_line_table(128),g_line_table(129),
1892 g_line_table(130),g_line_table(131),g_line_table(132),
1893 g_line_table(133),g_line_table(134),g_line_table(135),
1894 g_line_table(136),g_line_table(137),g_line_table(138),
1895 g_line_table(139),g_line_table(140),g_line_table(141),
1896 g_line_table(142),g_line_table(143),g_line_table(144),
1897 g_line_table(145),g_line_table(146),g_line_table(147),
1898 g_line_table(148),g_line_table(149),g_line_table(150),
1899 g_line_table(151),g_line_table(152),g_line_table(153),
1900 g_line_table(154),g_line_table(155),g_line_table(156),
1901 g_line_table(157),g_line_table(158),g_line_table(159),
1902 g_line_table(160),g_line_table(161),g_line_table(162),
1903 g_line_table(163),g_line_table(164),g_line_table(165),
1904 g_line_table(166),g_line_table(167),g_line_table(168),
1905 g_line_table(169),g_line_table(170),g_line_table(171),
1906 g_line_table(172),g_line_table(173),g_line_table(174),
1907 g_line_table(175),g_line_table(176),g_line_table(177),
1908 g_line_table(178),g_line_table(179),g_line_table(180),
1909 g_line_table(181),g_line_table(182),g_line_table(183),
1910 g_line_table(184),g_line_table(185),g_line_table(186),
1911 g_line_table(187),g_line_table(188),g_line_table(189),
1912 g_line_table(190),g_line_table(191),g_line_table(192),
1913 g_line_table(193),g_line_table(194),g_line_table(195),
1914 g_line_table(196),g_line_table(197),g_line_table(198),
1915 g_line_table(199),g_line_table(200),g_line_table(201),
1916 g_line_table(202),g_line_table(203),g_line_table(204),
1917 g_line_table(205),g_line_table(206),g_line_table(207),
1918 g_line_table(208),g_line_table(209),g_line_table(210),
1919 g_line_table(211),g_line_table(212),g_line_table(213),
1920 g_line_table(214),g_line_table(215),g_line_table(216),
1921 g_line_table(217),g_line_table(218),g_line_table(219),
1922 g_line_table(220),g_line_table(221),g_line_table(222),
1923 g_line_table(223),g_line_table(224),g_line_table(225),
1924 g_line_table(226),g_line_table(227),g_line_table(228),
1925 g_line_table(229),g_line_table(230));
1926 --
1927 COMMIT;
1928 --statement to commit every <CHUNK_SIZE>
1929 IF l_chunk_size_slave = 0 THEN
1930 COMMIT;
1931 l_chunk_size_slave := l_chunk_size_master;
1932 ELSE
1933 l_chunk_size_slave := l_chunk_size_slave - 1;
1934 END IF;
1935 END IF;
1936 hr_du_utility.message('INFO','Insert Statement End ' , 30);
1937 END IF;
1938 l_line_type := 'D';
1939
1940 EXCEPTION
1941 WHEN no_data_found THEN
1942 l_fatal_error_message := 'Data Incorrectly Terminated ';
1943 RAISE e_fatal_error;
1944 END;
1945 END LOOP;
1946 --commit to make sure everything has been committed
1947 COMMIT;
1948 --
1949 hr_du_utility.message('ROUT','exit:hr_du_di_insert.extract_lines', 35);
1950 --
1951
1952 --error handling
1953 EXCEPTION
1954 WHEN e_fatal_error THEN
1955 hr_du_utility.error(SQLCODE,
1956 'hr_du_di_insert.extract_lines',l_fatal_error_message, 'R');
1957 RAISE;
1958 WHEN OTHERS THEN
1959 hr_du_utility.error(SQLCODE,
1960 'hr_du_di_insert.extract_lines','(none)', 'R');
1961 RAISE;
1962
1963 END Extract_lines;
1964
1965
1966
1967
1968 -- ------------------------- ORDERED_SEQUENCE ------------------------------
1969 -- Description: This is the main procedure that controlls the follow of both
1970 -- procedure and function calls to control the Input Porcess
1971 --
1972 -- Input Parameters
1973 -- p_upload_id - HR_DU_UPLOAD_ID to be used
1974 --
1975 -- ------------------------------------------------------------------------
1976 PROCEDURE ORDERED_SEQUENCE(p_upload_id IN NUMBER)
1977 IS
1978
1979 l_filehandle UTL_FILE.FILE_TYPE;
1980 l_data_line VARCHAR2(2000);
1981 l_number NUMBER;
1982 l_location VARCHAR2(2000);
1983 l_file_name VARCHAR2(2000);
1984
1985 BEGIN
1986 --
1987 hr_du_utility.message('ROUT','entry:hr_du_di_insert.ordered_sequence', 5);
1988 hr_du_utility.message('PARA', '(p_upload_id - ' || p_upload_id || ')',10);
1989 --
1990
1991 g_flat_file_delimiter := g_tab_delimiter;
1992 g_current_delimiter := g_flat_file_delimiter;
1993
1994 SET_DElIMITER_STRING;
1995 POPULATE_DYNAMIC_TABLE;
1996
1997 fnd_profile.get('PER_DATA_EXCHANGE_DIR', l_location);
1998 l_file_name := Return_File_Name(p_upload_id);
1999 l_filehandle :=Open_file(l_location, l_file_name);
2000 Extract_headers(l_filehandle, p_upload_id);
2001 Extract_API_locations(l_filehandle, p_upload_id);
2002
2003 --makes sure that there are not two API's with the same file
2004 Check_Unique_Files(p_upload_id);
2005
2006 Handle_API_Files (l_location, p_upload_id);
2007
2008 --
2009 hr_du_utility.message('ROUT','exit:hr_du_di_insert.ordered_sequence', 15);
2010 --
2011
2012 --error handling
2013 EXCEPTION
2014 WHEN OTHERS THEN
2015 hr_du_utility.error(SQLCODE,
2016 'hr_du_di_insert.ordered_sequence','(none)', 'R');
2017 RAISE;
2018
2019 END ORDERED_SEQUENCE;
2020
2021 -- ------------------------- VALIDATE -----------------------------------
2022 -- Description:
2023 --
2024 -- Input Parameters
2025 -- p_upload_id - The upload id to associate the procedure with
2026 -- correct table
2027 --
2028 -- ------------------------------------------------------------------------
2029 PROCEDURE VALIDATE(p_upload_id IN NUMBER) IS
2030
2031 l_temp VARCHAR2(20);
2032
2033 BEGIN
2034 --
2035 hr_du_utility.message('ROUT','entry:hr_du_di_insert.validate', 5);
2036 hr_du_utility.message('PARA', '(p_upload_id - ' || p_upload_id || ')',
2037 10);
2038 --
2039 l_temp := null;
2040 --
2041 hr_du_utility.message('ROUT','exit:hr_du_di_insert.validate', 15);
2042 --
2043
2044 --
2045 EXCEPTION
2046 WHEN OTHERS THEN
2047 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.validate',
2048 '(none)', 'R');
2049 RAISE;
2050 --
2051 END VALIDATE;
2052
2053
2054 -- ------------------------- ROLLBACK -----------------------------------
2055 -- Description: This procedure is called when an error has occured so that
2056 -- the database tables can be cleaned up to restart the input process again
2057 --
2058 -- Input Parameters
2059 -- p_upload_id - The upload id to associate the procedure with
2060 -- correct table
2061 --
2062 -- ------------------------------------------------------------------------
2063 PROCEDURE ROLLBACK(p_upload_id IN NUMBER) IS
2064
2065 BEGIN
2066 --
2067 hr_du_utility.message('ROUT','entry:hr_du_di_insert.rollback', 5);
2068 hr_du_utility.message('PARA', '(p_upload_id - ' || p_upload_id || ')',
2069 10);
2070 --
2071
2072 DELETE FROM HR_DU_DESCRIPTORS
2073 WHERE UPLOAD_ID = p_upload_id;
2074 COMMIT;
2075
2076 DELETE FROM HR_DU_UPLOAD_LINES
2077 WHERE UPLOAD_HEADER_ID IN (SELECT upload_header_id
2078 FROM hr_du_upload_headers
2079 WHERE upload_id = p_upload_id);
2080 COMMIT;
2081
2082 DELETE FROM HR_DU_UPLOAD_HEADERS
2083 WHERE UPLOAD_ID = p_upload_id;
2084 COMMIT;
2085
2086 --
2087 hr_du_utility.message('ROUT','exit:hr_du_di_insert.rollback', 15);
2088 --
2089
2090 --
2091 EXCEPTION
2092 WHEN OTHERS THEN
2093 hr_du_utility.error(SQLCODE, 'hr_du_di_insert.rollback',
2094 '(none)', 'R');
2095 RAISE;
2096 --
2097 END ROLLBACK;
2098
2099
2100 END HR_DU_DI_INSERT;