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