[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_TAX_TABLE_UPLOAD
Source
1 PACKAGE BODY pay_se_tax_table_upload
2 /* $Header: pysettup.pkb 120.4 2007/02/15 18:00:39 vetsrini noship $ */
3 AS
4 g_package CONSTANT VARCHAR2 (33) := 'PAY_SE_TAX_TABLE_UPLOAD';
5 -- Global constants
6 g_warning CONSTANT NUMBER := 1;
7 g_error CONSTANT NUMBER := 2;
8 -- Exceptions
9 e_fatal_error EXCEPTION;
10 e_record_too_long EXCEPTION;
11 e_diff_record_frequency EXCEPTION;
12 e_diff_record_freq_limit EXCEPTION;
13 e_empty_line EXCEPTION;
14 e_same_date EXCEPTION;
15 e_future_rec_exists EXCEPTION;
16 c_end_of_time CONSTANT DATE := TO_DATE ('12/31/4712', 'MM/DD/YYYY');
17 start_date DATE;
18 end_date DATE;
19
20 PROCEDURE main (
21 errbuf OUT NOCOPY VARCHAR2
22 ,retcode OUT NOCOPY NUMBER
23 ,p_data_file_name IN VARCHAR2
24 ,p_tax_table_type IN VARCHAR2
25 ,p_effective_start_date IN VARCHAR2
26 ,p_business_group IN NUMBER
27 )
28 IS
29 CURSOR csr_legislation_code
30 IS
31 SELECT legislation_code
32 FROM per_business_groups
33 WHERE business_group_id = p_business_group;
34
35 l_proc CONSTANT VARCHAR2 (72) := g_package || '.MAIN';
36 l_errbuf VARCHAR2 (1000);
37 l_retcode NUMBER;
38 lr_legislation_code csr_legislation_code%ROWTYPE;
39 legislation_code per_business_groups.legislation_code%TYPE;
40 BEGIN
41 l_errbuf := ' ';
42 l_retcode := 0;
43
44 OPEN csr_legislation_code;
45
46 FETCH csr_legislation_code
47 INTO lr_legislation_code;
48
49 CLOSE csr_legislation_code;
50
51 legislation_code := lr_legislation_code.legislation_code;
52 hr_utility.set_location ('Entering:' || l_proc, 10);
53 hr_utility.set_location ('p_business_group' || p_business_group, 15);
54 hr_utility.set_location ('Legislation = ' || legislation_code, 20);
55 hr_utility.set_location ( 'Effective Start Date = '
56 || p_effective_start_date
57 ,21
58 );
59 hr_utility.set_location ('c_end_of_time = ' || c_end_of_time, 22);
60
61 -- Check for Sweden Localization.
62 IF legislation_code = 'SE'
63 THEN
64 PURGE (l_errbuf, l_retcode, NULL, NULL, NULL);
65 -- Date Validation Check
66 -- Call for the SQL Loader Concurrent Request
67 upload_tax_to_temp_table (l_errbuf, l_retcode, p_data_file_name,p_tax_table_type);
68 errbuf := l_errbuf;
69 retcode := l_retcode;
70 --If anything happens inside the previous call,,
71 -- dont proceed further
72 hr_utility.set_location ('l_retcode:' || l_retcode, 40);
73
74 IF l_retcode IS NULL OR l_retcode = 1
75 THEN
76 -- Call to Load data procedure
77 upload_tax_to_main_table
78 (l_errbuf
79 ,l_retcode
80 ,p_legislation_code => legislation_code
81 ,p_effective_start_date => TRUNC
82 (TO_DATE
83 (p_effective_start_date
84 ,'YYYY/MM/DD HH24:MI:SS'
85 )
86 )
87 ,p_business_group => p_business_group
88 ,p_tax_table_type => p_tax_table_type
89 );
90 errbuf := l_errbuf;
91 retcode := l_retcode;
92
93 -- Emptying the Temp tables
94 IF l_retcode IS NULL OR l_retcode = 1
95 THEN
96 PURGE (l_errbuf, l_retcode, NULL, NULL, NULL);
97 END IF;
98 END IF;
99
100 COMMIT;
101 END IF;
102
103 hr_utility.set_location ('Leaving:' || l_proc, 40);
104 END main;
105
106 /* */
107
108 ---------------------------------------------------------------------------------
109 PROCEDURE PURGE (
110 errbuf OUT NOCOPY VARCHAR2
111 ,retcode OUT NOCOPY NUMBER
112 ,p_business_group IN NUMBER
113 ,p_effective_start_date IN VARCHAR2
114 ,p_effective_end_date IN VARCHAR2
115 )
116 IS
117 CURSOR csr_legislation_code
118 IS
119 SELECT legislation_code
120 FROM per_business_groups
121 WHERE business_group_id = p_business_group;
122
123 CURSOR csr_range_table_id (
124 p_legislation_code per_business_groups.legislation_code%TYPE
125 )
126 IS
127 SELECT range_table_id
128 ,object_version_number
129 FROM pay_range_tables_f
130 WHERE legislation_code = p_legislation_code
131 --AND BUSINESS_GROUP_ID = p_business_group
132 AND effective_start_date >=
133 TO_DATE (p_effective_start_date, 'YYYY/MM/DD HH24:MI:SS')
134 AND effective_end_date <=
135 TO_DATE (p_effective_end_date, 'YYYY/MM/DD HH24:MI:SS');
136
137 CURSOR csr_range_id (p_range_table_id NUMBER)
138 IS
139 SELECT range_id
140 ,object_version_number
141 FROM pay_ranges_f
142 WHERE range_table_id = p_range_table_id
143 AND effective_start_date >=
144 TO_DATE (p_effective_start_date, 'YYYY/MM/DD HH24:MI:SS')
145 AND effective_end_date <=
146 TO_DATE (p_effective_end_date, 'YYYY/MM/DD HH24:MI:SS');
147
148 lr_legislation_code csr_legislation_code%ROWTYPE;
149 legislation_code per_business_groups.legislation_code%TYPE;
150 BEGIN
151 --hr_utility.trace_on(null,'tax');
152 hr_utility.set_location ('Entering: Purge ', 10);
153 hr_utility.set_location ('p_business_group ' || p_business_group, 10);
154 hr_utility.set_location ( 'p_effective_start_date'
155 || p_effective_start_date
156 ,10
157 );
158 hr_utility.set_location ('p_effective_end_date' || p_effective_end_date
159 ,10
160 );
161
162 IF p_effective_start_date IS NULL AND p_effective_end_date IS NULL
163 THEN
164 DELETE FROM pay_range_temp;
165 ELSE
166 OPEN csr_legislation_code;
167
168 FETCH csr_legislation_code
169 INTO lr_legislation_code;
170
171 CLOSE csr_legislation_code;
172
173 legislation_code := lr_legislation_code.legislation_code;
174 hr_utility.set_location ('Legislation_Code' || legislation_code, 10);
175
176 FOR MASTER IN csr_range_table_id (legislation_code)
177 LOOP
178 FOR CHILD IN csr_range_id (MASTER.range_table_id)
179 LOOP
180 pay_range_api.delete_range (CHILD.range_id
181 ,CHILD.object_version_number
182 );
183 END LOOP;
184
185 pay_range_table_api.delete_range_table
186 (MASTER.range_table_id
187 ,MASTER.object_version_number
188 );
189 END LOOP;
190 END IF;
191
192 COMMIT;
193 END PURGE;
194
195 ---------------------------------------------------------------------------------
196 PROCEDURE check_date (
197 p_effective_start_date IN VARCHAR2
198 ,p_effective_end_date IN VARCHAR2
199 ,p_message_name IN VARCHAR2
200 )
201 IS
202 BEGIN
203 IF TO_DATE (p_effective_start_date, 'YYYY/MM/DD HH24:MI:SS') >
204 TO_DATE (p_effective_end_date, 'YYYY/MM/DD HH24:MI:SS')
205 THEN
206 fnd_message.set_name ('PAY', p_message_name);
207 fnd_message.raise_error;
208 END IF;
209 END check_date;
210
211 ---------------------------------------------------------------------------
212
213 /* */
214 PROCEDURE upload_tax_to_main_table (
215 errbuf OUT NOCOPY VARCHAR2
216 ,retcode OUT NOCOPY NUMBER
217 ,p_legislation_code IN VARCHAR2
218 ,p_effective_start_date IN DATE
219 ,p_business_group IN NUMBER
220 ,p_tax_table_type IN VARCHAR2
221 )
222 IS
223 l_proc CONSTANT VARCHAR2 (72)
224 := g_package || '.Upload_Tax_To_Main_Table';
225 -- Automatic Sequence created by API
226 l_pay_f_range_table_id pay_range_tables_f.range_table_id%TYPE;
227 -- Values from flat file to be uploaded to Temp Tables
228 l_range_table_num pay_range_tables_f.range_table_number%TYPE;
229 l_period_frequency pay_range_tables_f.period_frequency%TYPE;
230 l_row_value_uom pay_range_tables_f.row_value_uom%TYPE;
231 l_low_band pay_ranges_f.low_band%TYPE;
232 l_high_band pay_ranges_f.high_band%TYPE;
233 l_amount1 pay_ranges_f.amount1%TYPE;
234 l_amount2 pay_ranges_f.amount2%TYPE;
235 l_amount3 pay_ranges_f.amount3%TYPE;
236 l_amount4 pay_ranges_f.amount4%TYPE;
237 l_amount5 pay_ranges_f.amount5%TYPE;
238 l_amount6 pay_ranges_f.amount6%TYPE;
239 l_amount7 pay_ranges_f.amount7%TYPE;
240 l_amount8 pay_ranges_f.amount8%TYPE;
241 l_dummy_range_table_id pay_range_tables_f.range_table_id%TYPE;
242 l_object_version_number pay_range_tables_f.object_version_number%TYPE;
243 l_dummy pay_range_tables_f.object_version_number%TYPE;
244 l_csr_range_table_id pay_range_tables_f.range_table_id%TYPE;
245
246 CURSOR csr_data_exists_on_same_date
247 IS
248 SELECT 'Y'
249 FROM pay_range_tables_f
250 WHERE legislation_code = p_legislation_code
251 AND period_frequency = p_tax_table_type
252 --AND BUSINESS_GROUP_ID = p_business_group
253 AND effective_start_date = p_effective_start_date;
254
255 CURSOR csr_data_exists_on_future_date
256 IS
257 SELECT 'Y'
258 ,effective_start_date
259 ,effective_end_date
260 FROM pay_range_tables_f
261 WHERE legislation_code = p_legislation_code
262 AND period_frequency = p_tax_table_type
263 --AND BUSINESS_GROUP_ID = p_business_group
264 AND effective_start_date > p_effective_start_date;
265
266 CURSOR csr_master_end_date (
267 l_range_table_number NUMBER
268 ,l_period_frequency NUMBER
269 ,l_row_value_uom VARCHAR2
270 )
271 IS
272 SELECT range_table_id
273 ,object_version_number
274 FROM pay_range_tables_f
275 WHERE legislation_code = p_legislation_code
276 --AND BUSINESS_GROUP_ID = p_business_group
277 AND effective_start_date < p_effective_start_date
278 AND range_table_number = l_range_table_number
279 AND row_value_uom = l_row_value_uom
280 AND period_frequency = l_period_frequency
281 AND effective_end_date = c_end_of_time;
282
283 CURSOR csr_child_end_date (l_range_table_id NUMBER)
284 IS
285 SELECT range_id
286 FROM pay_ranges_f
287 WHERE range_table_id = l_range_table_id
288 AND effective_start_date < p_effective_start_date
289 AND effective_end_date <> c_end_of_time;
290
291 CURSOR csr_distinct_range_values
292 IS
293 SELECT DISTINCT range_table_number
294 ,period_frequency
295 ,row_value_uom
296 FROM pay_range_temp;
297
298 CURSOR csr_range_band_val_frm_tmp_tab (
299 l_range_table_num NUMBER
300 ,l_period_frequency VARCHAR2
301 ,l_row_value_uom VARCHAR2
302 )
303 IS
304 SELECT low_band
305 ,high_band
306 ,amount1
307 ,amount2
308 ,amount3
309 ,amount4
310 ,amount5
311 ,amount6
312 ,amount7
313 ,amount8
314 FROM pay_range_temp
315 WHERE range_table_number = l_range_table_num
316 AND period_frequency = l_period_frequency
317 AND row_value_uom = l_row_value_uom;
318
319 CURSOR csr_range_values_from_main_tab
320 IS
321 SELECT range_table_id
322 ,range_table_number
323 ,period_frequency
324 ,row_value_uom
325 FROM pay_range_tables_f
326 WHERE legislation_code = p_legislation_code
327 --AND BUSINESS_GROUP_ID = p_business_group
328 AND effective_start_date = p_effective_start_date
329 AND effective_end_date = c_end_of_time;
330
331 l_check VARCHAR2 (20);
332 lr_csr_master_end_date csr_master_end_date%ROWTYPE;
333 BEGIN
334 l_check := ' ';
335 hr_utility.set_location ('UPLOAD PROCESS', 10);
336 l_object_version_number := 1;
337
338 -- Check for the Data if the effective date is same as available in db ,,
339 -- Plz say no and clear temp table and error out
340 OPEN csr_data_exists_on_same_date;
341
342 FETCH csr_data_exists_on_same_date
343 INTO l_check;
344
345 CLOSE csr_data_exists_on_same_date;
346
347 IF l_check = 'Y'
348 THEN
349 RAISE e_same_date;
350 END IF;
351
352 --Resetting it to null
353 l_check := ' ';
354
355 OPEN csr_data_exists_on_future_date;
356
357 FETCH csr_data_exists_on_future_date
358 INTO l_check
359 ,start_date
360 ,end_date;
361
362 CLOSE csr_data_exists_on_future_date;
363
364 IF l_check = 'Y'
365 THEN
366 RAISE e_future_rec_exists;
367 END IF;
368
369 --Resetting it to null
370 l_check := ' ';
371 -- TEMP TABLE
372 l_csr_range_table_id := -99;
373
374 -- *****************************************************************************************
375 FOR uni_master IN csr_distinct_range_values
376 LOOP
377 OPEN csr_master_end_date (uni_master.range_table_number
378 ,uni_master.period_frequency
379 ,uni_master.row_value_uom
380 );
381
382 FETCH csr_master_end_date
383 INTO l_csr_range_table_id
384 ,l_dummy;
385
386 CLOSE csr_master_end_date;
387
388 IF l_csr_range_table_id <> -99
389 THEN
390 -- It found the master id is already present which has to be end-dated.
391 hr_utility.set_location ('PROCESS', 10);
392 pay_range_table_api.update_range_table
393 (p_range_table_id => l_csr_range_table_id
394 ,p_effective_end_date => p_effective_start_date
395 - 1
396 ,p_object_version_number => l_dummy
397 );
398 -- End dated the master record , now itself end date the child records
399 end_date_child (l_csr_range_table_id, p_effective_start_date);
400 l_csr_range_table_id := -99;
401 END IF;
402
403 pay_range_table_api.create_range_table
404 (p_range_table_id => l_dummy_range_table_id
405 ,p_effective_start_date => p_effective_start_date
406 ,p_effective_end_date => c_end_of_time
407 ,p_range_table_number => uni_master.range_table_number
408 ,p_period_frequency => uni_master.period_frequency
409 ,p_row_value_uom => uni_master.row_value_uom
410 ,p_legislation_code => p_legislation_code
411 ,p_business_group_id => NULL
412 --p_business_group
413 , p_object_version_number => l_object_version_number
414 );
415 END LOOP;
416
417 -- *****************************************************************************************
418
419 -- Open Master parent table and fetch the range_table_num , Period_frequency and row value num
420 -- pick up values from temp table for this record and insert that in to Main child table
421 FOR MASTER IN csr_range_values_from_main_tab
422 LOOP
423 -- For each record in the pay_range_tables_f
424 -- pick up all record one by one from pay_ranges_temp
425 -- and insert into pay_ranges_f table
426 FOR CHILD IN
427 csr_range_band_val_frm_tmp_tab (MASTER.range_table_number
428 ,MASTER.period_frequency
429 ,MASTER.row_value_uom
430 )
431 LOOP
432 pay_range_api.create_range
433 (p_range_table_id => MASTER.range_table_id
434 ,p_low_band => CHILD.low_band
435 ,p_high_band => CHILD.high_band
436 ,p_amount1 => CHILD.amount1
437 ,p_amount2 => CHILD.amount2
438 ,p_amount3 => CHILD.amount3
439 ,p_amount4 => CHILD.amount4
440 ,p_amount5 => CHILD.amount5
441 ,p_amount6 => CHILD.amount6
442 ,p_amount7 => CHILD.amount7
443 ,p_amount8 => CHILD.amount8
444 ,p_effective_start_date => p_effective_start_date
445 ,p_effective_end_date => c_end_of_time
446 ,p_object_version_number => l_object_version_number
447 ,p_range_id => l_dummy_range_table_id
448 );
449 END LOOP;
450 END LOOP;
451 EXCEPTION
452 -- *************************************
453 WHEN e_same_date
454 -- Data already availabe on same date
455 THEN
456 hr_utility.set_location (l_proc, 270);
457 -- Set retcode to 2, indicating an ERROR to the ConcMgr
458 retcode := g_error;
459 -- Set the application error
460 hr_utility.set_message (801, 'HR_377224_SE_DATE_INVALID');
461 -- Return the message to the ConcMgr (This msg will appear in the log file)
462 errbuf := hr_utility.GET_MESSAGE;
463 -- *************************************
464
465 -- *************************************
466 WHEN e_future_rec_exists
467 -- Data already availabe on same date
468 THEN
469 hr_utility.set_location (l_proc, 270);
470 -- Set retcode to 2, indicating an ERROR to the ConcMgr
471 retcode := g_error;
472 -- Set the application error
473 hr_utility.set_message (801, 'HR_377226_SE_FUTURE_DATA_EXIST');
474 hr_utility.set_message_token (801
475 ,'START_DATE'
476 ,TO_DATE (start_date, 'DD/MM/YYYY')
477 );
478 hr_utility.set_message_token (801
479 ,'END_DATE'
480 ,TO_DATE (end_date, 'DD/MM/YYYY')
481 );
482 -- Return the message to the ConcMgr (This msg will appear in the log file)
483 errbuf := hr_utility.GET_MESSAGE;
484 -- *************************************
485 END upload_tax_to_main_table;
486
487 PROCEDURE end_date_child (
488 p_range_table_id IN NUMBER
489 ,p_effective_start_date IN DATE
490 )
491 IS
492 CURSOR csr_child_end_date
493 IS
494 SELECT range_id
495 ,object_version_number
496 FROM pay_ranges_f
497 WHERE range_table_id = p_range_table_id
498 AND effective_start_date < p_effective_start_date
499 AND effective_end_date = c_end_of_time;
500
501 l_range_id NUMBER;
502 l_object_version_number pay_range_tables_f.object_version_number%TYPE;
503 BEGIN
504 OPEN csr_child_end_date;
505
506 LOOP
507 EXIT WHEN csr_child_end_date%NOTFOUND;
508
509 FETCH csr_child_end_date
510 INTO l_range_id
511 ,l_object_version_number;
512
513 pay_range_api.update_range
514 (p_range_table_id => p_range_table_id
515 ,p_effective_end_date => p_effective_start_date
516 - 1
517 ,p_object_version_number => l_object_version_number
518 ,p_range_id => l_range_id
519 );
520 END LOOP;
521
522 CLOSE csr_child_end_date;
523 END end_date_child;
524
525 -- *****************************************************************************************
526 /*
527 PROCEDURE NAME : Upload_Tax_To_Temp_Table
528 PARAMATERS : p_data_file_name -- Name of the file to be read.
529
530 PURPOSE : To Open the file Specified from the particular Dir
531 Pass it to SPLIT_LINE Procedure
532
533 ERRORS HANDLED : Raise ERROR if No directory specified
534 Raise Error for all invalid file level operations
535 Like
536 invalid operation
537 internal error
538 invalid mode
539 invalid path
540 invalid filehandle
541 read error
542 */
543 PROCEDURE upload_tax_to_temp_table (
544 errbuf OUT NOCOPY VARCHAR2
545 ,retcode OUT NOCOPY NUMBER
546 ,p_data_file_name IN VARCHAR2
547 ,p_tax_table_type IN VARCHAR2
548 )
549 IS
550 -- Procedure name
551 l_proc CONSTANT VARCHAR2 (72)
552 := g_package || '.Upload_Tax_To_Temp_Table';
553 -- Constants
554 c_read_file CONSTANT VARCHAR2 (1) := 'r';
555 c_max_linesize CONSTANT NUMBER := 4000;
556 c_commit_point CONSTANT NUMBER := 1000;
557 c_data_exchange_dir CONSTANT VARCHAR2 (30) := 'PER_DATA_EXCHANGE_DIR';
558 -- File Handling variables
559 l_file_type UTL_FILE.file_type;
560 l_filename VARCHAR2 (240);
561 l_location VARCHAR2 (4000);
562 l_line_read VARCHAR2 (4000) := NULL;
563 -- Batch Variables
564 l_batch_seq NUMBER := 0;
565 l_batch_id NUMBER;
566 -- variables which represents columns in PAY_RANGE_TEMP table.
567 l_range_table_number pay_range_tables_f.range_table_number%TYPE;
568 l_period_frequency pay_range_tables_f.period_frequency%TYPE;
569 l_row_value_uom pay_range_tables_f.row_value_uom%TYPE;
570 l_low_band pay_ranges_f.low_band%TYPE;
571 l_high_band pay_ranges_f.high_band%TYPE;
572 l_amount1 pay_ranges_f.amount1%TYPE;
573 l_amount2 pay_ranges_f.amount2%TYPE;
574 l_amount3 pay_ranges_f.amount3%TYPE;
575 l_amount4 pay_ranges_f.amount4%TYPE;
576 l_amount5 pay_ranges_f.amount5%TYPE;
577 l_amount6 pay_ranges_f.amount6%TYPE;
578 l_amount7 pay_ranges_f.amount7%TYPE;
579 l_amount8 pay_ranges_f.amount8%TYPE;
580 -- Local Variables
581 l_diff_record_type NUMBER := 0;
582
583 BEGIN
584 hr_utility.set_location ('Entering:' || l_proc, 10);
585 hr_utility.set_location ('p_data_file_name ' || p_data_file_name, 1);
586 l_filename := p_data_file_name;
587 fnd_profile.get (c_data_exchange_dir, l_location);
588 hr_utility.set_location ('Directory = ' || l_location, 30);
589
590 IF l_location IS NULL
591 THEN
592 hr_utility.set_location ('Raising I/O error = ' || l_location, 35);
593 -- error : I/O directory not defined
594 RAISE e_fatal_error;
595 END IF;
596
597 -- Open flat file
598 l_file_type :=
599 UTL_FILE.fopen (l_location, l_filename, c_read_file, c_max_linesize);
600
601 -- Loop over the file, reading in each line.
602 -- GET_LINE will raise NO_DATA_FOUND when it r4eaches EOF
603 -- so we use that as the exit condition for the loop
604 <<read_lines_in_file>>
605 LOOP
606 BEGIN
607 UTL_FILE.get_line (l_file_type, l_line_read);
608 l_batch_seq := l_batch_seq + 1;
609 hr_utility.set_location (' line read: ' || l_line_read, 60);
610 -- Calling the procedure tyo split the line into variables
611 split_line (p_line => l_line_read
612 ,p_range_table_number => l_range_table_number
613 ,p_row_value_uom => l_row_value_uom
614 ,p_period_frequency => l_period_frequency
615 ,p_low_band => l_low_band
616 ,p_high_band => l_high_band
617 ,p_amount1 => l_amount1
618 ,p_amount2 => l_amount2
619 ,p_amount3 => l_amount3
620 ,p_amount4 => l_amount4
621 ,p_amount5 => l_amount5
622 ,p_amount6 => l_amount6
623 ,p_amount7 => l_amount7
624 ,p_amount8 => l_amount8
625 );
626 -- Warning record is of different frequency Type
627 IF (trim(l_period_frequency) = trim(p_tax_table_type))
628 THEN
629 INSERT INTO pay_range_temp
630 (range_id
631 ,range_table_number
632 ,row_value_uom
633 ,period_frequency
634 ,earnings_type
635 ,low_band
636 ,high_band
637 ,amount1
638 ,amount2
639 ,amount3
640 ,amount4
641 ,amount5
642 ,amount6
643 ,amount7
644 ,amount8
645 )
646 VALUES (pay_ranges_f_s.NEXTVAL
647 ,l_range_table_number
648 ,l_row_value_uom
649 ,l_period_frequency
650 ,NULL
651 ,l_low_band
652 ,l_high_band
653 ,l_amount1
654 ,l_amount2
655 ,l_amount3
656 ,l_amount4
657 ,l_amount5
658 ,l_amount6
659 ,l_amount7
660 ,l_amount8
661 );
662 else
663 hr_utility.set_location (' Record is of different type', 110);
664 l_diff_record_type := l_diff_record_type +1;
665 IF l_diff_record_type < 100
666 THEN
667 RAISE e_diff_record_frequency;
668 ELSE
669 RAISE e_diff_record_freq_limit;
670 END IF;
671 END IF;
672
673
674 -- commit the records uppon reaching the commit point
675 IF MOD (l_batch_seq, c_commit_point) = 0
676 THEN
677 COMMIT;
678 END IF;
679 EXCEPTION
680 WHEN NO_DATA_FOUND
681 THEN
682 EXIT;
683 -- *************************************
684 -- When the Record is of differnt frequency type .
685 WHEN e_diff_record_frequency
686 --Record is of different frequency
687 THEN
688 -- Set retcode to 1, indicating a WARNING to the ConcMgr
689 retcode := g_warning;
690 -- Set the application error
691 hr_utility.set_message (801, 'HR_377252_SE_REC_DIFF_FREQ');
692 hr_utility.set_message_token (801, 'LINE_NO', l_batch_seq);
693 hr_utility.set_message_token (801, 'LINE', l_line_read);
694 hr_utility.set_location (l_proc, 260);
695 -- Write the message to log file, do not raise an application error but continue
696 -- (with next line)
697 fnd_file.put_line (fnd_file.LOG, hr_utility.GET_MESSAGE);
698
699 -- *************************************
700 -- When 100 Records of different frequency type has been found.
701 WHEN e_diff_record_freq_limit
702 --Record is of different frequency
703 THEN
704 hr_utility.set_location (l_proc, 270);
705 -- Set retcode to 2, indicating an ERROR to the ConcMgr
706 retcode := g_error;
707 -- Set the application error
708 hr_utility.set_message (801, 'HR_377253_SE_REC_FREQ_LIMIT');
709 -- Return the message to the ConcMgr (This msg will appear in the log file)
710 errbuf := hr_utility.GET_MESSAGE;
711
712 -- *************************************
713 -- When the Record in the file is larger than specified size.
714 WHEN e_record_too_long
715 --Record is too long
716 THEN
717 -- Set retcode to 1, indicating a WARNING to the ConcMgr
718 retcode := g_warning;
719 -- Set the application error
720 hr_utility.set_message (801, 'HR_377215_SE_RECORD_TOO_LONG');
721 hr_utility.set_message_token (801, 'LINE_NO', l_batch_seq);
722 hr_utility.set_message_token (801, 'LINE', l_line_read);
723 hr_utility.set_location (l_proc, 260);
724 -- Write the message to log file, do not raise an application error but continue
725 -- (with next line)
726 fnd_file.put_line (fnd_file.LOG, hr_utility.GET_MESSAGE);
727 -- *************************************
728 WHEN e_empty_line
729 THEN
730 -- Set retcode to 1, indicating a WARNING to the ConcMgr
731 retcode := g_warning;
732 -- Set the application error
733 hr_utility.set_message (800, 'HR_377222_SE_EMPTY_LINE');
734 hr_utility.set_message_token (800, 'LINE_NO', l_batch_seq);
735 -- Write the message to log file, do not raise an application error but continue
736 -- (with next line)
737 fnd_file.put_line (fnd_file.LOG, hr_utility.GET_MESSAGE);
738 END; -- file reading Begin
739 END LOOP read_lines_in_file;
740
741 -- Commit the outstanding records
742 COMMIT;
743 UTL_FILE.fclose (l_file_type);
744 hr_utility.set_location ('Leaving:' || l_proc, 260);
745 EXCEPTION
746 -- When file location is not proper
747 -- ***********************************************
748 WHEN e_fatal_error
749 -- No directory specified
750 THEN
751 -- Close the file in case of error
752 IF UTL_FILE.is_open (l_file_type)
753 THEN
754 UTL_FILE.fclose (l_file_type);
755 END IF;
756
757 hr_utility.set_location (l_proc, 270);
758 -- Set retcode to 2, indicating an ERROR to the ConcMgr
759 retcode := g_error;
760 -- Set the application error
761 hr_utility.set_message (801, 'HR_SE_DATA_EXCHANGE_DIR_MIS');
762 -- Return the message to the ConcMgr (This msg will appear in the log file)
763 errbuf := hr_utility.GET_MESSAGE;
764 -- ***********************************************
765 WHEN UTL_FILE.invalid_operation
766 -- File could not be opened as requested, perhaps because of operating system permissions
767 -- Also raised when attempting a write operation on a file opened for read, or a read operation
768 -- on a file opened for write.
769 THEN
770 IF UTL_FILE.is_open (l_file_type)
771 THEN
772 UTL_FILE.fclose (l_file_type);
773 END IF;
774
775 hr_utility.set_location (l_proc, 280);
776 retcode := g_error;
777 errbuf :=
778 'Reading File ('
779 || l_location
780 || ' -> '
781 || l_filename
782 || ') - Invalid Operation.';
783 -- ***********************************************
784 WHEN UTL_FILE.internal_error
785 -- Unspecified internal error
786 THEN
787 IF UTL_FILE.is_open (l_file_type)
788 THEN
789 UTL_FILE.fclose (l_file_type);
790 END IF;
791
792 hr_utility.set_location (l_proc, 290);
793 retcode := g_error;
794 errbuf :=
795 'Reading File ('
796 || l_location
797 || ' -> '
798 || l_filename
799 || ') - Internal Error.';
800 -- ***********************************************
801 WHEN UTL_FILE.invalid_mode
802 -- Invalid string specified for file mode
803 THEN
804 IF UTL_FILE.is_open (l_file_type)
805 THEN
806 UTL_FILE.fclose (l_file_type);
807 END IF;
808
809 hr_utility.set_location (l_proc, 300);
810 retcode := g_error;
811 errbuf :=
812 'Reading File ('
813 || l_location
814 || ' -> '
815 || l_filename
816 || ') - Invalid Mode.';
817 -- ***********************************************
818 WHEN UTL_FILE.invalid_path
819 -- Directory or filename is invalid or not accessible
820 THEN
821 IF UTL_FILE.is_open (l_file_type)
822 THEN
823 UTL_FILE.fclose (l_file_type);
824 END IF;
825
826 retcode := g_error;
827 errbuf :=
828 'Reading File ('
829 || l_location
830 || ' -> '
831 || l_filename
832 || ') - Invalid Path or Filename.';
833 hr_utility.set_location (l_proc, 310);
834 -- ***********************************************
835 WHEN UTL_FILE.invalid_filehandle
836 -- File type does not specify an open file
837 THEN
838 IF UTL_FILE.is_open (l_file_type)
839 THEN
840 UTL_FILE.fclose (l_file_type);
841 END IF;
842
843 hr_utility.set_location (l_proc, 320);
844 retcode := g_error;
845 errbuf :=
846 'Reading File ('
847 || l_location
848 || ' -> '
849 || l_filename
850 || ') - Invalid File Type.';
851 WHEN UTL_FILE.read_error
852 -- ***********************************************
853
854 -- Operating system error occurred during a read operation
855 THEN
856 IF UTL_FILE.is_open (l_file_type)
857 THEN
858 UTL_FILE.fclose (l_file_type);
859 END IF;
860
861 hr_utility.set_location (l_proc, 330);
862 retcode := g_error;
863 errbuf :=
864 'Reading File ('
865 || l_location
866 || ' -> '
867 || l_filename
868 || ') - Read Error.';
869 -- ***********************************************
870 END upload_tax_to_temp_table;
871
872 /*
873
874 PROCEDURE NAME : split_line
875 PARAMATERS :
876 p_line a line read from file
877 Out variables
878 split the values in the line pass it to
879 specific out parameter.
880 PURPOSE : To split up the line read return it to specific columns.
881 ERRORS HANDLED :
882 e_record_too_long When Record is Too Long.
883
884 */
885 PROCEDURE split_line (
886 p_line IN VARCHAR2
887 ,p_range_table_number OUT NOCOPY pay_range_tables_f.range_table_number%TYPE
888 ,p_row_value_uom OUT NOCOPY pay_range_tables_f.row_value_uom%TYPE
889 ,p_period_frequency OUT NOCOPY pay_range_tables_f.period_frequency%TYPE
890 ,p_low_band OUT NOCOPY pay_ranges_f.low_band%TYPE
891 ,p_high_band OUT NOCOPY pay_ranges_f.high_band%TYPE
892 ,p_amount1 OUT NOCOPY pay_ranges_f.amount1%TYPE
893 ,p_amount2 OUT NOCOPY pay_ranges_f.amount2%TYPE
894 ,p_amount3 OUT NOCOPY pay_ranges_f.amount3%TYPE
895 ,p_amount4 OUT NOCOPY pay_ranges_f.amount4%TYPE
896 ,p_amount5 OUT NOCOPY pay_ranges_f.amount5%TYPE
897 ,p_amount6 OUT NOCOPY pay_ranges_f.amount6%TYPE
898 ,p_amount7 OUT NOCOPY pay_ranges_f.amount7%TYPE
899 ,p_amount8 OUT NOCOPY pay_ranges_f.amount8%TYPE
900 )
901 IS
902 -- Procedure name
903 l_proc CONSTANT VARCHAR2 (72) := g_package || '.split_line';
904 l_record_length NUMBER := 45; --33;
905 BEGIN
906 hr_utility.set_location ('Entering:' || l_proc, 70);
907 --Set record length
908 l_record_length := 45; --33;
909
910 IF p_line IS NULL
911 THEN
912 /* If the line is empty Raise an Warning saying the line is empty */
913 RAISE e_empty_line;
914 ELSE
915 -- Error in record if it is too long according to given format
916 IF (LENGTH (p_line) > l_record_length)
917 THEN
918 hr_utility.set_location (' Record too long', 110);
919 RAISE e_record_too_long;
920 END IF;
921
922 p_period_frequency := SUBSTR (p_line, 1, 2);
923 p_row_value_uom := SUBSTR (p_line, 3, 1);
924 p_range_table_number := SUBSTR (p_line, 4, 2);
925 p_low_band := TRIM (SUBSTR (p_line, 6, 7));
926 p_high_band := TRIM (SUBSTR (p_line, 13, 7));
927 p_amount1 := TRIM (SUBSTR (p_line, 20, 5));
928 p_amount2 := TRIM (SUBSTR (p_line, 25, 5));
929 p_amount3 := TRIM (SUBSTR (p_line, 30, 5));
930 p_amount4 := TRIM (SUBSTR (p_line, 35, 5));
931 p_amount5 := TRIM (SUBSTR (p_line, 40, 5));
932 -- ************************* NOT REQUIRED FOR NOW *********************
933 -- BUT ADDING IT FOR FUTURE USE
934 p_amount6 := NULL;
935 p_amount7 := NULL;
936 p_amount8 := NULL;
937 -- ************************* NOT REQUIRED FOR NOW *********************
938 hr_utility.set_location ( ' p_RANGE_TABLE_NUMBER'
939 || p_range_table_number
940 ,110
941 );
942 hr_utility.set_location (' p_ROW_VALUE_UOM ' || p_row_value_uom, 110);
943 hr_utility.set_location (' p_PERIOD_FREQUENCY ' || p_period_frequency
944 ,110
945 );
946 hr_utility.set_location (' p_LOW_BAND ' || p_low_band, 110);
947 hr_utility.set_location (' p_HIGH_BAND ' || p_high_band, 110);
948 hr_utility.set_location (' p_AMOUNT1 ' || p_amount1, 110);
949 hr_utility.set_location (' p_AMOUNT2 ' || p_amount2, 110);
950 hr_utility.set_location (' p_AMOUNT3 ' || p_amount3, 110);
951 hr_utility.set_location (' p_AMOUNT4 ' || p_amount4, 110);
952 hr_utility.set_location (' p_AMOUNT5 ' || p_amount5, 110);
953 hr_utility.set_location (' p_AMOUNT6 ' || p_amount6, 110);
954 hr_utility.set_location (' p_AMOUNT7 ' || p_amount7, 110);
955 hr_utility.set_location (' p_AMOUNT8 ' || p_amount8, 110);
956 END IF;
957
958 hr_utility.set_location ('Leaving:' || l_proc, 120);
959 END;
960 END pay_se_tax_table_upload;