[Home] [Help]
PACKAGE BODY: APPS.AP_WEB_UPLOAD_PDM_PKG
Source
1 PACKAGE BODY AP_WEB_UPLOAD_PDM_PKG AS
2 /* $Header: apwupdmb.pls 120.12.12000000.2 2007/02/12 23:23:23 skoukunt ship $ */
3
4
5 PROCEDURE put_line(p_buff IN VARCHAR2) IS
6 BEGIN
7 fnd_file.put_line(fnd_file.log, p_buff);
8 END put_line;
9
10
11 ------------------------------------------------------------------------
12 FUNCTION Concat(p_str1 IN VARCHAR2,
13 p_str2 IN VARCHAR2) RETURN VARCHAR2 IS
14 ------------------------------------------------------------------------
15 BEGIN
16 if (p_str1 is null) then
17 return p_str2;
18 elsif (p_str2 is null) then
19 return p_str1;
20 else
21 return p_str1||', '||p_str2;
22 end if;
23 return null;
24 END Concat;
25
26
27 PROCEDURE CleanRatesInterface(p_ratetype IN VARCHAR2) IS
28 BEGIN
29 --
30 put_line('Clean up oie_pol_rates_interface');
31 --
32 delete
33 from oie_pol_rates_interface
34 where
35 (
36 (p_ratetype = 'CONUS' and country = 'UNITED STATES' and state_province not in ('HAWAII', 'ALASKA'))
37 or
38 (p_ratetype = 'OCONUS' and (country <> 'UNITED STATES' or state_province in ('HAWAII', 'ALASKA')))
39 );
40
41 EXCEPTION
42 when no_data_found then
43 return;
44 when others then
45 APP_EXCEPTION.RAISE_EXCEPTION;
46
47 END CleanRatesInterface;
48
49
50 ------------------------------------------------------------------------
51 PROCEDURE AddToZeroRates(p_city_locality IN VARCHAR2,
52 p_county IN VARCHAR2,
53 p_state_province IN VARCHAR2,
54 p_country IN VARCHAR2) IS
55 ------------------------------------------------------------------------
56
57 i number;
58 already_added boolean := false;
59 location varchar2(240);
60
61 BEGIN
62 location := Concat(Concat(Concat(p_city_locality, p_county), p_state_province), p_country);
63 for i in 1..g_num_locs_zero_rates
64 loop
65 if (g_zero_rates(i) = location) then
66 already_added := true;
67 exit;
68 end if;
69 end loop;
70
71 if (not already_added) then
72 g_num_locs_zero_rates := g_num_locs_zero_rates + 1;
73 g_zero_rates.extend(1);
74 g_zero_rates(g_num_locs_zero_rates) := location;
75 end if;
76 END AddToZeroRates;
77
78
79 ------------------------------------------------------------------------
80 PROCEDURE AddToInvalidLocs(p_city_locality IN VARCHAR2,
81 p_county IN VARCHAR2,
82 p_state_province IN VARCHAR2,
83 p_country IN VARCHAR2) IS
84 ------------------------------------------------------------------------
85 i number;
86 already_added boolean := false;
87 location varchar2(240);
88
89 BEGIN
90 location := Concat(Concat(Concat(p_city_locality, p_county), p_state_province), p_country);
91 for i in 1..g_num_locs_invalid
92 loop
93 if (g_invalid_locs(i) = location) then
94 already_added := true;
95 exit;
96 end if;
97 end loop;
98
99 if (not already_added) then
100 g_num_locs_invalid := g_num_locs_invalid + 1;
101 g_invalid_locs.extend(1);
102 g_invalid_locs(g_num_locs_invalid) := location;
103 end if;
104 END AddToInvalidLocs;
105
106
107 ------------------------------------------------------------------------
108 FUNCTION MyReplace(p_string IN VARCHAR2) RETURN VARCHAR2 IS
109 ------------------------------------------------------------------------
110 BEGIN
111 if (p_string is not null) then
112 return replace(replace(replace(replace(replace(replace(replace(p_string,', THE','%'),' REPUBLIC OF','%'),' ISLANDS','%'),' ISLAND','%'),', ','%'),'-','%'),'.','%');
113 end if;
114 return null;
115 END MyReplace;
116
117 ------------------------------------------------------------------------
118 FUNCTION MySoundex(p_string IN VARCHAR2) RETURN VARCHAR2 IS
119 ------------------------------------------------------------------------
120 BEGIN
121 if (p_string is not null) then
122 return soundex(myreplace(p_string));
123 end if;
124 return null;
125 END MySoundex;
126
127
128 ------------------------------------------------------------------------
129 FUNCTION GetTerritory(p_country IN VARCHAR2) RETURN VARCHAR2 IS
130 ------------------------------------------------------------------------
131
132 -------------------
133 -- exact match cursor
134 -------------------
135 CURSOR exact_cur IS
136 select territory_code
137 from fnd_territories_vl
138 where upper(territory_short_name) = upper(p_country)
139 and rownum = 1;
140
141 exact_rec exact_cur%ROWTYPE;
142
143 -------------------
144 -- fuzzy match cursor
145 -------------------
146 CURSOR fuzzy_count_cur IS
147 select count(1) fuzzy_count
148 from fnd_territories_vl
149 where
150 (
151 upper(territory_short_name) like '%'||upper(p_country)||'%'
152 or
153 upper(description) like '%'||upper(p_country)||'%'
154 );
155
156 fuzzy_count_rec fuzzy_count_cur%ROWTYPE;
157 l_fuzzy_count NUMBER := 0;
158
159 CURSOR fuzzy_cur IS
160 select territory_code
161 from fnd_territories_vl
162 where
163 (
164 upper(territory_short_name) like '%'||upper(p_country)||'%'
165 or
166 upper(description) like '%'||upper(p_country)||'%'
167 )
168 and rownum = 1;
169
170 fuzzy_rec fuzzy_cur%ROWTYPE;
171
172 -------------------
173 -- extreme fuzzy match cursor
174 -------------------
175 CURSOR extreme_fuzzy_count_cur IS
176 select count(1) extreme_fuzzy_count
177 from fnd_territories_vl
178 where
179 (
180 upper(territory_short_name) like '%'||ap_web_upload_pdm_pkg.myreplace(upper(p_country))||'%'
181 or
182 upper(description) like '%'||ap_web_upload_pdm_pkg.myreplace(upper(p_country))||'%'
183 );
184
185 extreme_fuzzy_count_rec extreme_fuzzy_count_cur%ROWTYPE;
186 l_extreme_fuzzy_count NUMBER := 0;
187
188 CURSOR extreme_fuzzy_cur IS
189 select territory_code
190 from fnd_territories_vl
191 where
192 (
193 upper(territory_short_name) like '%'||ap_web_upload_pdm_pkg.myreplace(upper(p_country))||'%'
194 or
195 upper(description) like '%'||ap_web_upload_pdm_pkg.myreplace(upper(p_country))||'%'
196 )
197 and rownum = 1;
198
199 extreme_fuzzy_rec extreme_fuzzy_cur%ROWTYPE;
200
201 -------------------
202 -- soundex match cursor
203 -------------------
204 CURSOR soundex_count_cur IS
205 select count(1) soundex_count
206 from fnd_territories_vl
207 where
208 (
209 ap_web_upload_pdm_pkg.mysoundex(upper(territory_short_name)) = ap_web_upload_pdm_pkg.mysoundex(upper(p_country))
210 or
211 ap_web_upload_pdm_pkg.mysoundex(upper(description)) = ap_web_upload_pdm_pkg.mysoundex(upper(p_country))
212 );
213
214 soundex_count_rec soundex_count_cur%ROWTYPE;
215 l_soundex_count NUMBER := 0;
216
217 CURSOR soundex_cur IS
218 select territory_code
219 from fnd_territories_vl
220 where
221 (
222 ap_web_upload_pdm_pkg.mysoundex(territory_short_name) = ap_web_upload_pdm_pkg.mysoundex(p_country)
223 or
224 ap_web_upload_pdm_pkg.mysoundex(description) = ap_web_upload_pdm_pkg.mysoundex(p_country)
225 )
226 and rownum = 1;
227
228 soundex_rec soundex_cur%ROWTYPE;
229
230
231 BEGIN
232
233 OPEN exact_cur;
234 FETCH exact_cur INTO exact_rec;
235 CLOSE exact_cur;
236
237 if (exact_rec.territory_code is not null) then
238
239 -- exact match
240 return exact_rec.territory_code;
241
242 else
243
244 OPEN fuzzy_count_cur;
245 FETCH fuzzy_count_cur INTO fuzzy_count_rec;
246 CLOSE fuzzy_count_cur;
247
248 if (fuzzy_count_rec.fuzzy_count = 1) then
249
250 -- fuzzy match found 1
251 OPEN fuzzy_cur;
252 FETCH fuzzy_cur INTO fuzzy_rec;
253 CLOSE fuzzy_cur;
254
255 return fuzzy_rec.territory_code;
256
257 else
258
259 OPEN extreme_fuzzy_count_cur;
260 FETCH extreme_fuzzy_count_cur INTO extreme_fuzzy_count_rec;
261 CLOSE extreme_fuzzy_count_cur;
262
263 if (extreme_fuzzy_count_rec.extreme_fuzzy_count = 1) then
264
265 -- extreme fuzzy match found 1
266 OPEN extreme_fuzzy_cur;
267 FETCH extreme_fuzzy_cur INTO extreme_fuzzy_rec;
268 CLOSE extreme_fuzzy_cur;
269
270 return extreme_fuzzy_rec.territory_code;
271
272 else
273
274 OPEN soundex_count_cur;
275 FETCH soundex_count_cur INTO soundex_count_rec;
276 CLOSE soundex_count_cur;
277
278 if (soundex_count_rec.soundex_count = 1) then
279
280 -- soundex match found 1
281 OPEN soundex_cur;
282 FETCH soundex_cur INTO soundex_rec;
283 CLOSE soundex_cur;
284
285 return soundex_rec.territory_code;
286
287 end if; /* soundex_count_rec.soundex_count = 1 */
288
289 end if; /* extreme_fuzzy_count_rec.extreme_fuzzy_count = 1 */
290
291 end if; /* fuzzy_count_rec.fuzzy_count = 1 */
292
293 end if; /* exact_rec.territory_code is not null */
294
295 return null;
296
297 EXCEPTION
298 when others then
299 APP_EXCEPTION.RAISE_EXCEPTION;
300
301 END GetTerritory;
302
303
304 ------------------------------------------------------------------------
305 FUNCTION CreateLocation(p_location IN VARCHAR2,
306 p_location_type IN VARCHAR2,
307 p_city_locality_id IN NUMBER,
308 p_county_id IN NUMBER,
309 p_state_province_id IN NUMBER,
310 p_country IN VARCHAR2,
311 p_territory_code IN VARCHAR2,
312 p_undefined_location IN VARCHAR2) RETURN NUMBER IS
313 ------------------------------------------------------------------------
314
315 l_location_id NUMBER;
316 l_description VARCHAR2(240);
317
318 BEGIN
319
320 if (p_location_type = 'CITY') then
321 if (p_territory_code = 'US') then
322 /* CONUS - City/Locality||', '||County||', '||State/Province */
323 l_description := Concat(p_location, AP_WEB_POLICY_UTILS.get_location(p_county_id));
324 l_description := Concat(l_description, AP_WEB_POLICY_UTILS.get_location(p_state_province_id));
325 elsif (p_undefined_location = 'N') then
326 /* OCONUS - City/Locality||', '||Country */
327 l_description := Concat(p_location, p_country);
328 else
329 l_description := p_location;
330 end if;
331 else
332 l_description := p_location;
333 end if;
334
335 /*
336 put_line('CreateLocation : ');
337 put_line('=> Location : '||initcap(p_location));
338 put_line('=> Location Type : '||p_location_type);
339 put_line('=> Description : '||l_description);
340 put_line('=> City Locality Id : '||p_city_locality_id);
341 put_line('=> County Id : '||p_county_id);
342 put_line('=> State Province Id : '||p_state_province_id);
343 put_line('=> Country : '||p_country);
344 put_line('=> Territory Code : '||p_territory_code);
345 put_line('=> Undefined Location : '||p_undefined_location);
346 */
347
348 select ap_pol_locations_s.nextval
349 into l_location_id
350 from dual;
351
352 insert into AP_POL_LOCATIONS_B (
353 LOCATION_ID,
354 TERRITORY_CODE,
355 UNDEFINED_LOCATION_FLAG,
356 END_DATE,
357 STATUS,
358 CREATION_DATE,
359 CREATED_BY,
360 LAST_UPDATE_DATE,
361 LAST_UPDATED_BY,
362 LAST_UPDATE_LOGIN,
363 LOCATION_TYPE,
364 CITY_LOCALITY_ID,
365 COUNTY_ID,
366 STATE_PROVINCE_ID,
367 COUNTRY
368 ) values (
369 --LOCATION_ID,
370 l_location_id,
371 --TERRITORY_CODE,
372 p_territory_code,
373 --UNDEFINED_LOCATION_FLAG,
374 nvl(p_undefined_location, 'N'),
375 --END_DATE,
376 null,
377 --STATUS,
378 decode(p_territory_code, null, decode(p_undefined_location, 'Y', 'ACTIVE', 'INVALID'), 'ACTIVE'),
379 --CREATION_DATE,
380 sysdate,
381 --CREATED_BY,
382 fnd_global.user_id,
383 --LAST_UPDATE_DATE,
384 sysdate,
385 --LAST_UPDATED_BY,
386 fnd_global.user_id,
387 --LAST_UPDATE_LOGIN,
388 fnd_global.login_id,
389 --LOCATION_TYPE,
390 p_location_type,
391 --CITY_LOCALITY_ID,
392 p_city_locality_id,
393 --COUNTY_ID,
394 p_county_id,
395 --STATE_PROVINCE_ID,
396 p_state_province_id,
397 --COUNTRY
398 initcap(p_country)
399 );
400
401 insert into AP_POL_LOCATIONS_TL (
402 LOCATION_ID,
403 LOCATION,
404 DESCRIPTION,
405 CREATION_DATE,
406 CREATED_BY,
407 LAST_UPDATE_LOGIN,
408 LAST_UPDATE_DATE,
409 LAST_UPDATED_BY,
410 LANGUAGE,
411 SOURCE_LANG
412 ) select
413 --LOCATION_ID,
414 l_location_id,
415 --LOCATION,
416 initcap(p_location),
417 --DESCRIPTION,
418 initcap(l_description),
419 --CREATION_DATE,
420 sysdate,
421 --CREATED_BY,
422 fnd_global.user_id,
423 --LAST_UPDATE_LOGIN,
424 fnd_global.login_id,
425 --LAST_UPDATE_DATE,
426 sysdate,
427 --LAST_UPDATED_BY,
428 fnd_global.user_id,
429 --LANGUAGE,
430 L.LANGUAGE_CODE,
431 --SOURCE_LANG
432 NVL(userenv('LANG'),BASE.LANGUAGE_CODE)
433 from FND_LANGUAGES L,
434 FND_LANGUAGES BASE
435 where L.INSTALLED_FLAG in ('I', 'B')
436 AND BASE.INSTALLED_FLAG = 'B'
437 and not exists
438 (select NULL
439 from AP_POL_LOCATIONS_TL T
440 where T.LOCATION_ID = l_location_id
441 and T.LANGUAGE = L.LANGUAGE_CODE);
442
443 if (p_location_type = 'CITY') then
444 put_line('Created Location Id: '||l_location_id);
445 g_num_locs_created := g_num_locs_created + 1;
446 end if;
447
448 return l_location_id;
449
450 EXCEPTION
451 when others then
452 fnd_message.set_name('SQLAP', 'OIE_CREATE_LOCATION_ERROR');
453 fnd_message.set_token('LOCATION', p_location);
454 fnd_message.set_token('TYPE', p_location_type);
455 APP_EXCEPTION.RAISE_EXCEPTION;
456
457 END CreateLocation;
458
459
460 ------------------------------------------------------------------------
461 FUNCTION GetLocation(p_location IN VARCHAR2,
462 p_location_type IN VARCHAR2,
463 p_city_locality_id IN NUMBER,
464 p_county_id IN NUMBER,
465 p_state_province_id IN NUMBER,
466 p_country IN VARCHAR2,
467 p_territory_code IN VARCHAR2) RETURN NUMBER IS
468 ------------------------------------------------------------------------
469
470 CURSOR loc_cur IS
471 select location_id
472 from ap_pol_locations_vl
473 where upper(location) = upper(decode(p_location_type, 'CITY', location, p_location))
474 and location_type = p_location_type
475 and nvl(city_locality_id, -1) = nvl(p_city_locality_id, -1)
476 and nvl(county_id, -1) = nvl(p_county_id, -1)
477 and nvl(state_province_id, -1) = nvl(p_state_province_id, -1)
478 and nvl(upper(country), -1) = nvl(upper(p_country), -1)
479 and nvl(territory_code, -1) = nvl(p_territory_code, -1)
480 and rownum = 1;
481
482 loc_rec loc_cur%ROWTYPE;
483
484 BEGIN
485
486 OPEN loc_cur;
487 FETCH loc_cur INTO loc_rec;
488 CLOSE loc_cur;
489
490 if (loc_rec.location_id is not null) then
491 return loc_rec.location_id;
492 else
493 return CreateLocation(p_location => p_location,
494 p_location_type => p_location_type,
495 p_city_locality_id => p_city_locality_id,
496 p_county_id => p_county_id,
497 p_state_province_id => p_state_province_id,
498 p_country => p_country,
499 p_territory_code => p_territory_code,
500 p_undefined_location => 'N');
501 end if;
502
503 return null;
504
505 EXCEPTION
506 when others then
507 fnd_message.set_name('SQLAP', 'OIE_LOCATION_NOT_FOUND');
508 fnd_message.set_token('LOCATION', p_location);
509 fnd_message.set_token('TYPE', p_location_type);
510 APP_EXCEPTION.RAISE_EXCEPTION;
511
512 END GetLocation;
513
514
515 ------------------------------------------------------------------------
516 FUNCTION GetUndefinedCONUS RETURN NUMBER IS
517 ------------------------------------------------------------------------
518
519 l_city_locality_id NUMBER;
520
521 CURSOR undefined_conus_cur IS
522 select location_id
523 from ap_pol_locations_vl
524 where city_locality_id = l_city_locality_id
525 and territory_code = 'US'
526 and rownum = 1;
527
528 undefined_conus_rec undefined_conus_cur%ROWTYPE;
529
530 BEGIN
531
532 l_city_locality_id := GetLocation(p_location => 'All Other United States',
533 p_location_type => 'CITY_LOCALITY',
534 p_city_locality_id => null,
535 p_county_id => null,
536 p_state_province_id => null,
537 p_country => 'UNITED STATES',
538 p_territory_code => 'US');
539
540 OPEN undefined_conus_cur;
541 FETCH undefined_conus_cur INTO undefined_conus_rec;
542 CLOSE undefined_conus_cur;
543
544 if (undefined_conus_rec.location_id is not null) then
545 return undefined_conus_rec.location_id;
546 else
547 --FND_MESSAGE.SET_NAME('SQLAP', 'OIE_ALL_OTHER_US');
548 --return CreateLocation(p_location => FND_MESSAGE.GET,
549 return CreateLocation(p_location => 'All Other United States',
550 p_location_type => 'CITY',
551 p_city_locality_id => l_city_locality_id,
552 p_county_id => null,
553 p_state_province_id => null,
554 p_country => 'UNITED STATES',
555 p_territory_code => 'US',
556 p_undefined_location => 'N');
557 end if;
558
559 return null;
560
561 EXCEPTION
562 when others then
563 fnd_message.set_name('SQLAP', 'OIE_UNDEFINED_CONUS_NOT_FOUND');
564 APP_EXCEPTION.RAISE_EXCEPTION;
565
566 END GetUndefinedCONUS;
567
568
569 ------------------------------------------------------------------------
570 FUNCTION GetUndefinedLocation RETURN NUMBER IS
571 ------------------------------------------------------------------------
572
573 CURSOR undefined_cur IS
574 select location_id
575 from ap_pol_locations_vl
576 where undefined_location_flag = 'Y'
577 and territory_code is null
578 and rownum = 1;
579
580 undefined_rec undefined_cur%ROWTYPE;
581
582 BEGIN
583
584 OPEN undefined_cur;
585 FETCH undefined_cur INTO undefined_rec;
586 CLOSE undefined_cur;
587
588 if (undefined_rec.location_id is not null) then
589 return undefined_rec.location_id;
590 else
591 FND_MESSAGE.SET_NAME('SQLAP', 'OIE_ALL_OTHER');
592 return CreateLocation(p_location => FND_MESSAGE.GET,
593 p_location_type => 'CITY',
594 p_city_locality_id => null,
595 p_county_id => null,
596 p_state_province_id => null,
597 p_country => null,
598 p_territory_code => null,
599 p_undefined_location => 'Y');
600 end if;
601
602 return null;
603
604 EXCEPTION
605 when others then
606 fnd_message.set_name('SQLAP', 'OIE_UNDEFINED_LOCATION_NOT_FOUND');
607 APP_EXCEPTION.RAISE_EXCEPTION;
608
609 END GetUndefinedLocation;
610
611
612 ------------------------------------------------------------------------
613 FUNCTION get_location_status(p_location_id IN NUMBER) RETURN VARCHAR2 IS
614 ------------------------------------------------------------------------
615
616 CURSOR loc_status_cur IS
617 select status
618 from ap_pol_locations_vl
619 where location_id = p_location_id;
620
621 loc_status_rec loc_status_cur%ROWTYPE;
622
623 BEGIN
624 IF p_location_id is null THEN
625 return null;
626 END IF;
627
628 OPEN loc_status_cur;
629 FETCH loc_status_cur INTO loc_status_rec;
630 CLOSE loc_status_cur;
631
632 return loc_status_rec.status;
633
634 END get_location_status;
635
636
637 ------------------------------------------------------------------------
638 FUNCTION GetCityLocation(p_city_locality IN VARCHAR2,
639 p_county IN VARCHAR2,
640 p_state_province IN VARCHAR2,
641 p_country IN VARCHAR2) RETURN NUMBER IS
642 ------------------------------------------------------------------------
643
644 l_territory_code VARCHAR2(30);
645 --l_country_id NUMBER;
646 l_state_province_id NUMBER;
647 l_county_id NUMBER;
648 l_city_locality_id NUMBER;
649
650 BEGIN
651
652 /*
653 put_line('GetCityLocation : ');
654 put_line('=> City Locality : '||p_city_locality);
655 put_line('=> County : '||p_county);
656 put_line('=> State Province : '||p_state_province);
657 put_line('=> Country : '||p_country);
658 put_line('=> Territory Code : '||l_territory_code);
659 */
660
661 if (p_city_locality = 'ALL PLACES NOT LISTED') then
662 if (p_state_province like '%CONUS%') then
663 return GetUndefinedCONUS;
664 else
665 return GetUndefinedLocation;
666 end if;
667 end if;
668
669 l_territory_code := GetTerritory(p_country => p_country);
670 /*
671 if (l_territory_code is null) then
672 -- cannot create a location without an associated fnd_territory!
673 return null;
674 end if;
675 */
676
677 /* No need to create Country Locations
678 if (p_country is not null) then
679 l_country_id := GetLocation(p_location => p_country,
680 p_location_type => 'COUNTRY',
681 p_city_locality_id => l_city_locality_id,
682 p_county_id => l_county_id,
683 p_state_province_id => l_state_province_id,
684 p_country => p_country,
685 p_territory_code => l_territory_code);
686 end if;
687 */
688
689 if (p_state_province is not null) then
690 l_state_province_id := GetLocation(p_location => p_state_province,
691 p_location_type => 'STATE_PROVINCE',
692 p_city_locality_id => l_city_locality_id,
693 p_county_id => l_county_id,
694 p_state_province_id => l_state_province_id,
695 p_country => p_country,
696 p_territory_code => l_territory_code);
697 end if;
698
699 if (p_county is not null) then
700 l_county_id := GetLocation(p_location => p_county,
701 p_location_type => 'COUNTY',
702 p_city_locality_id => l_city_locality_id,
703 p_county_id => l_county_id,
704 p_state_province_id => l_state_province_id,
705 p_country => p_country,
706 p_territory_code => l_territory_code);
707 end if;
708
709 if (p_city_locality is not null) then
710 l_city_locality_id := GetLocation(p_location => p_city_locality,
711 p_location_type => 'CITY_LOCALITY',
712 p_city_locality_id => l_city_locality_id,
713 p_county_id => l_county_id,
714 p_state_province_id => l_state_province_id,
715 p_country => p_country,
716 p_territory_code => l_territory_code);
717 end if;
718
719
720 return GetLocation(p_location => p_city_locality,
721 p_location_type => 'CITY',
722 p_city_locality_id => l_city_locality_id,
723 p_county_id => l_county_id,
724 p_state_province_id => l_state_province_id,
725 p_country => p_country,
726 p_territory_code => l_territory_code);
727
728
729 EXCEPTION
730 when others then
731 raise;
732
733 END GetCityLocation;
734
735
736
737 ------------------------------------------------------------------------
738 FUNCTION CreatePolicy(p_expense_category IN VARCHAR2,
739 p_policy_name IN VARCHAR2 DEFAULT NULL,
740 p_policy_start_date IN DATE DEFAULT NULL,
741 p_per_diem_type_code IN VARCHAR2 DEFAULT NULL,
742 p_meals_rate IN VARCHAR2 DEFAULT NULL,
743 p_free_meals_ded IN VARCHAR2 DEFAULT NULL,
744 p_use_free_acc_add IN VARCHAR2 DEFAULT NULL,
745 p_use_free_acc_ded IN VARCHAR2 DEFAULT NULL) RETURN NUMBER IS
746 ------------------------------------------------------------------------
747
748 l_policy_id NUMBER(15);
749
750 BEGIN
751
752 select AP_POL_HEADERS_S.NEXTVAL
753 into l_policy_id
754 from dual;
755
756 insert into AP_POL_HEADERS
757 (
758 POLICY_ID,
759 CATEGORY_CODE,
760 SCHEDULE_TYPE_CODE,
761 SOURCE,
762 POLICY_NAME,
763 DESCRIPTION,
764 CURRENCY_CODE,
765 CURRENCY_PREFERENCE,
766 ALLOW_RATE_CONVERSION_CODE,
767 START_DATE,
768 LOCATION_FLAG,
769 PER_DIEM_TYPE_CODE,
770 MEALS_TYPE_CODE,
771 FREE_MEALS_FLAG,
772 FREE_MEALS_CODE,
773 FREE_ACCOMMODATIONS_FLAG,
774 FREE_ACCOMMODATIONS_CODE,
775 NIGHT_RATES_CODE,
776 CREATION_DATE,
777 CREATED_BY,
778 LAST_UPDATE_LOGIN,
779 LAST_UPDATE_DATE,
780 LAST_UPDATED_BY
781 )
782 values
783 (
784 --POLICY_ID,
785 l_policy_id,
786 --CATEGORY_CODE,
787 p_expense_category,
788 --SCHEDULE_TYPE_CODE,
789 p_expense_category,
790 --SOURCE,
791 'CONUS',
792 --POLICY_NAME,
793 p_policy_name,
794 --DESCRIPTION,
795 p_policy_name,
796 --CURRENCY_CODE,
797 'USD',
798 --CURRENCY_PREFERENCE,
799 'SRC',
800 --ALLOW_RATE_CONVERSION_CODE,
801 'ALLOW_CONVERSION',
802 --START_DATE,
803 p_policy_start_date,
804 --LOCATION_FLAG,
805 'Y',
806 --PER_DIEM_TYPE_CODE,
807 p_per_diem_type_code,
808 --MEALS_TYPE_CODE,
809 p_meals_rate,
810 --FREE_MEALS_FLAG,
811 decode(p_free_meals_ded, 'SINGLE', 'Y', 'SPECIFIC', 'Y', null),
812 --FREE_MEALS_CODE,
813 p_free_meals_ded,
814 --FREE_ACCOMMODATIONS_FLAG,
815 decode(p_use_free_acc_add, 'Y', 'Y', decode(p_use_free_acc_ded, 'Y', 'Y', null)),
816 --FREE_ACCOMMODATIONS_CODE,
817 decode(p_use_free_acc_add, 'Y', 'ADD', decode(p_use_free_acc_ded, 'Y', 'DEDUCT', null)),
818 --NIGHT_RATES_CODE,
819 decode(p_use_free_acc_add, 'Y', 'SINGLE', null),
820 --CREATION_DATE,
821 SYSDATE,
822 --CREATED_BY,
823 fnd_global.user_id,
824 --LAST_UPDATE_LOGIN,
825 fnd_global.login_id,
826 --LAST_UPDATE_DATE,
827 SYSDATE,
828 --LAST_UPDATED_BY
829 fnd_global.user_id
830 );
831
832 return l_policy_id;
833
834 EXCEPTION
835 WHEN OTHERS THEN
836 raise;
837
838 END CreatePolicy;
839
840
841 ------------------------------------------------------------------------
842 FUNCTION CreateScheduleOption(p_policy_id IN NUMBER,
843 p_location_id IN NUMBER) RETURN NUMBER IS
844 ------------------------------------------------------------------------
845
846 l_schedule_option_id NUMBER(15);
847
848 BEGIN
849
850 select AP_POL_SCHEDULE_OPTIONS_S.NEXTVAL
851 into l_schedule_option_id
852 from dual;
853
854 insert into AP_POL_SCHEDULE_OPTIONS
855 (
856 SCHEDULE_OPTION_ID,
857 POLICY_ID,
858 OPTION_TYPE,
859 LOCATION_ID,
860 STATUS,
861 CREATION_DATE,
862 CREATED_BY,
863 LAST_UPDATE_LOGIN,
864 LAST_UPDATE_DATE,
865 LAST_UPDATED_BY
866 )
867 values
868 (
869 --SCHEDULE_OPTION_ID,
870 l_schedule_option_id,
871 --POLICY_ID,
872 p_policy_id,
873 --OPTION_TYPE,
874 'LOCATION',
875 --LOCATION_ID,
876 p_location_id,
877 --STATUS,
878 'SAVED',
879 --CREATION_DATE,
880 SYSDATE,
881 --CREATED_BY,
882 fnd_global.user_id,
883 --LAST_UPDATE_LOGIN,
884 fnd_global.login_id,
885 --LAST_UPDATE_DATE,
886 SYSDATE,
887 --LAST_UPDATED_BY
888 fnd_global.user_id
889 );
890
891 return l_schedule_option_id;
892
893 EXCEPTION
894 WHEN OTHERS THEN
895 raise;
896
897 END CreateScheduleOption;
898
899
900 ------------------------------------------------------------------------
901 FUNCTION CreateSchedulePeriod(p_policy_id IN NUMBER,
902 p_period_name IN VARCHAR2,
903 p_period_start_date IN DATE) RETURN NUMBER IS
904 ------------------------------------------------------------------------
905
906 l_schedule_period_id NUMBER(15);
907
908 BEGIN
909
910 select AP_POL_SCHEDULE_PERIODS_S.NEXTVAL
911 into l_schedule_period_id
912 from dual;
913
914 insert into AP_POL_SCHEDULE_PERIODS
915 (
916 SCHEDULE_PERIOD_ID,
917 SCHEDULE_PERIOD_NAME,
918 POLICY_ID,
919 START_DATE,
920 CREATION_DATE,
921 CREATED_BY,
922 LAST_UPDATE_LOGIN,
923 LAST_UPDATE_DATE,
924 LAST_UPDATED_BY
925 )
926 values
927 (
928 --SCHEDULE_PERIOD_ID,
929 l_schedule_period_id,
930 --SCHEDULE_PERIOD_NAME,
931 p_period_name,
932 --POLICY_ID,
933 p_policy_id,
934 --START_DATE,
935 p_period_start_date,
936 --CREATION_DATE,
937 SYSDATE,
938 --CREATED_BY,
939 fnd_global.user_id,
940 --LAST_UPDATE_LOGIN,
941 fnd_global.login_id,
942 --LAST_UPDATE_DATE,
943 SYSDATE,
944 --LAST_UPDATED_BY
945 fnd_global.user_id
946 );
947
948 return l_schedule_period_id;
949
950 EXCEPTION
951 WHEN OTHERS THEN
952 raise;
953
954 END CreateSchedulePeriod;
955
956
957 ------------------------------------------------------------------------
958 FUNCTION CreatePolicyLine(p_policy_id IN NUMBER,
959 p_schedule_period_id IN NUMBER,
960 p_role_id IN NUMBER,
961 p_location_id IN NUMBER,
962 p_rate IN NUMBER,
963 p_calc_method IN VARCHAR2,
964 p_single_deduction IN NUMBER,
965 p_breakfast_deduction IN NUMBER,
966 p_lunch_deduction IN NUMBER,
967 p_dinner_deduction IN NUMBER,
968 p_start_of_season IN VARCHAR2,
969 p_end_of_season IN VARCHAR2,
970 p_max_lodging_amt IN NUMBER,
971 p_no_govt_meals_amt IN NUMBER,
972 p_prop_meals_amt IN NUMBER,
973 p_off_base_inc_amt IN NUMBER,
974 p_footnote_amt IN NUMBER,
975 p_footnote_rate_amt IN NUMBER,
976 p_max_per_diem_amt IN NUMBER,
977 p_effective_start_date IN DATE,
978 p_effective_end_date IN DATE,
979 p_use_free_acc_add IN VARCHAR2 DEFAULT NULL,
980 p_use_free_acc_ded IN VARCHAR2 DEFAULT NULL) RETURN NUMBER IS
981 ------------------------------------------------------------------------
982
983 l_policy_line_id NUMBER(15);
984
985 BEGIN
986
987 select AP_POL_LINES_S.NEXTVAL
988 into l_policy_line_id
989 from dual;
990
991
992 insert into AP_POL_LINES
993 (
994 POLICY_LINE_ID,
995 POLICY_ID,
996 SCHEDULE_PERIOD_ID,
997 ROLE_ID,
998 LOCATION_ID,
999 CURRENCY_CODE,
1000 RATE,
1001 RATE_TYPE_CODE,
1002 CALCULATION_METHOD,
1003 ACCOMMODATION_CALC_METHOD,
1004 ACCOMMODATION_ADJUSTMENT,
1005 MEALS_DEDUCTION,
1006 BREAKFAST_DEDUCTION,
1007 LUNCH_DEDUCTION,
1008 DINNER_DEDUCTION,
1009 STATUS,
1010 START_OF_SEASON,
1011 END_OF_SEASON,
1012 MAX_LODGING_AMT,
1013 NO_GOVT_MEALS_AMT,
1014 PROP_MEALS_AMT,
1015 OFF_BASE_INC_AMT,
1016 FOOTNOTE_AMT,
1017 FOOTNOTE_RATE_AMT,
1018 MAX_PER_DIEM_AMT,
1019 EFFECTIVE_START_DATE,
1020 EFFECTIVE_END_DATE,
1021 CREATION_DATE,
1022 CREATED_BY,
1023 LAST_UPDATE_LOGIN,
1024 LAST_UPDATE_DATE,
1025 LAST_UPDATED_BY
1026 )
1027 values
1028 (
1029 --POLICY_LINE_ID,
1030 l_policy_line_id,
1031 --POLICY_ID,
1032 p_policy_id,
1033 --SCHEDULE_PERIOD_ID,
1034 p_schedule_period_id,
1035 --ROLE_ID,
1036 p_role_id,
1037 --LOCATION_ID,
1038 p_location_id,
1039 --CURRENCY_CODE,
1040 'USD',
1041 --RATE,
1042 p_rate,
1043 --RATE_TYPE_CODE,
1044 'STANDARD',
1045 --CALCULATION_METHOD,
1046 p_calc_method,
1047 --ACCOMMODATION_CALC_METHOD,
1048 decode(p_use_free_acc_add, 'Y', 'AMOUNT', decode(p_use_free_acc_ded, 'Y', 'AMOUNT', null)),
1049 --ACCOMMODATION_ADJUSTMENT,
1050 decode(p_use_free_acc_add, 'Y', p_max_lodging_amt, decode(p_use_free_acc_ded, 'Y', p_max_lodging_amt, null)),
1051 --MEALS_DEDUCTION,
1052 p_single_deduction,
1053 --BREAKFAST_DEDUCTION,
1054 p_breakfast_deduction,
1055 --LUNCH_DEDUCTION,
1056 p_lunch_deduction,
1057 --DINNER_DEDUCTION,
1058 p_dinner_deduction,
1059 --STATUS,
1060 'NEW',
1061 --START_OF_SEASON,
1062 p_start_of_season,
1063 --END_OF_SEASON,
1064 p_end_of_season,
1065 --MAX_LODGING_AMT,
1066 p_max_lodging_amt,
1067 --NO_GOVT_MEALS_AMT,
1068 p_no_govt_meals_amt,
1069 --PROP_MEALS_AMT,
1070 p_prop_meals_amt,
1071 --OFF_BASE_INC_AMT,
1072 p_off_base_inc_amt,
1073 --FOOTNOTE_AMT,
1074 p_footnote_amt,
1075 --FOOTNOTE_RATE_AMT,
1076 p_footnote_rate_amt,
1077 --MAX_PER_DIEM_AMT,
1078 p_max_per_diem_amt,
1079 --EFFECTIVE_START_DATE,
1080 p_effective_start_date,
1081 --EFFECTIVE_END_DATE,
1082 p_effective_end_date,
1083 --CREATION_DATE,
1084 SYSDATE,
1085 --CREATED_BY,
1086 fnd_global.user_id,
1087 --LAST_UPDATE_LOGIN,
1088 fnd_global.login_id,
1089 --LAST_UPDATE_DATE,
1090 SYSDATE,
1091 --LAST_UPDATED_BY
1092 fnd_global.user_id
1093 );
1094
1095 return l_policy_line_id;
1096
1097 EXCEPTION
1098 WHEN OTHERS THEN
1099 raise;
1100
1101 END CreatePolicyLine;
1102
1103
1104 ------------------------------------------------------------------------
1105 FUNCTION CreateNightRateLine(p_policy_id IN NUMBER,
1106 p_schedule_period_id IN NUMBER,
1107 p_role_id IN NUMBER,
1108 p_location_id IN NUMBER,
1109 p_rate IN NUMBER,
1110 p_single_deduction IN NUMBER,
1111 p_breakfast_deduction IN NUMBER,
1112 p_lunch_deduction IN NUMBER,
1113 p_dinner_deduction IN NUMBER,
1114 p_start_of_season IN VARCHAR2,
1115 p_end_of_season IN VARCHAR2,
1116 p_max_lodging_amt IN NUMBER,
1117 p_no_govt_meals_amt IN NUMBER,
1118 p_prop_meals_amt IN NUMBER,
1119 p_off_base_inc_amt IN NUMBER,
1120 p_footnote_amt IN NUMBER,
1121 p_footnote_rate_amt IN NUMBER,
1122 p_max_per_diem_amt IN NUMBER,
1123 p_effective_start_date IN DATE,
1124 p_effective_end_date IN DATE) RETURN NUMBER IS
1125 ------------------------------------------------------------------------
1126
1127 l_night_rate_line_id NUMBER(15);
1128
1129 BEGIN
1130
1131 select AP_POL_LINES_S.NEXTVAL
1132 into l_night_rate_line_id
1133 from dual;
1134
1135
1136 insert into AP_POL_LINES
1137 (
1138 POLICY_LINE_ID,
1139 POLICY_ID,
1140 SCHEDULE_PERIOD_ID,
1141 ROLE_ID,
1142 LOCATION_ID,
1143 CURRENCY_CODE,
1144 RATE,
1145 RATE_TYPE_CODE,
1146 CALCULATION_METHOD,
1147 ACCOMMODATION_CALC_METHOD,
1148 ACCOMMODATION_ADJUSTMENT,
1149 MEALS_DEDUCTION,
1150 BREAKFAST_DEDUCTION,
1151 LUNCH_DEDUCTION,
1152 DINNER_DEDUCTION,
1153 STATUS,
1154 START_OF_SEASON,
1155 END_OF_SEASON,
1156 MAX_LODGING_AMT,
1157 NO_GOVT_MEALS_AMT,
1158 PROP_MEALS_AMT,
1159 OFF_BASE_INC_AMT,
1160 FOOTNOTE_AMT,
1161 FOOTNOTE_RATE_AMT,
1162 MAX_PER_DIEM_AMT,
1163 EFFECTIVE_START_DATE,
1164 EFFECTIVE_END_DATE,
1165 CREATION_DATE,
1166 CREATED_BY,
1167 LAST_UPDATE_LOGIN,
1168 LAST_UPDATE_DATE,
1169 LAST_UPDATED_BY
1170 )
1171 values
1172 (
1173 --POLICY_LINE_ID,
1174 l_night_rate_line_id,
1175 --POLICY_ID,
1176 p_policy_id,
1177 --SCHEDULE_PERIOD_ID,
1178 p_schedule_period_id,
1179 --ROLE_ID,
1180 p_role_id,
1181 --LOCATION_ID,
1182 p_location_id,
1183 --CURRENCY_CODE,
1184 'USD',
1185 --RATE,
1186 p_rate,
1187 --RATE_TYPE_CODE,
1188 'NIGHT_RATE',
1189 --CALCULATION_METHOD,
1190 'AMOUNT',
1191 --ACCOMMODATION_CALC_METHOD,
1192 'AMOUNT',
1193 --ACCOMMODATION_ADJUSTMENT,
1194 p_max_lodging_amt,
1195 --MEALS_DEDUCTION,
1196 p_single_deduction,
1197 --BREAKFAST_DEDUCTION,
1198 p_breakfast_deduction,
1199 --LUNCH_DEDUCTION,
1200 p_lunch_deduction,
1201 --DINNER_DEDUCTION,
1202 p_dinner_deduction,
1203 --STATUS,
1204 'NEW',
1205 --START_OF_SEASON,
1206 p_start_of_season,
1207 --END_OF_SEASON,
1208 p_end_of_season,
1209 --MAX_LODGING_AMT,
1210 p_max_lodging_amt,
1211 --NO_GOVT_MEALS_AMT,
1212 p_no_govt_meals_amt,
1213 --PROP_MEALS_AMT,
1214 p_prop_meals_amt,
1215 --OFF_BASE_INC_AMT,
1216 p_off_base_inc_amt,
1217 --FOOTNOTE_AMT,
1218 p_footnote_amt,
1219 --FOOTNOTE_RATE_AMT,
1220 p_footnote_rate_amt,
1221 --MAX_PER_DIEM_AMT,
1222 p_max_per_diem_amt,
1223 --EFFECTIVE_START_DATE,
1224 p_effective_start_date,
1225 --EFFECTIVE_END_DATE,
1226 p_effective_end_date,
1227 --CREATION_DATE,
1228 SYSDATE,
1229 --CREATED_BY,
1230 fnd_global.user_id,
1231 --LAST_UPDATE_LOGIN,
1232 fnd_global.login_id,
1233 --LAST_UPDATE_DATE,
1234 SYSDATE,
1235 --LAST_UPDATED_BY
1236 fnd_global.user_id
1237 );
1238
1239 return l_night_rate_line_id;
1240
1241 EXCEPTION
1242 WHEN OTHERS THEN
1243 raise;
1244
1245 END CreateNightRateLine;
1246
1247
1248 ------------------------------------------------------------------------
1249 FUNCTION GetPolicy(p_policy_id IN NUMBER) RETURN NUMBER IS
1250 ------------------------------------------------------------------------
1251
1252 cursor policy_cur is
1253 select policy_id
1254 from ap_pol_headers
1255 where policy_id = p_policy_id
1256 and rownum = 1;
1257
1258 policy_rec policy_cur%ROWTYPE;
1259
1260 BEGIN
1261
1262 OPEN policy_cur;
1263 FETCH policy_cur INTO policy_rec;
1264 CLOSE policy_cur;
1265 return policy_rec.policy_id;
1266
1267 EXCEPTION
1268 WHEN OTHERS THEN
1269 raise;
1270
1271 END GetPolicy;
1272
1273
1274 ------------------------------------------------------------------------
1275 FUNCTION GetScheduleOption(p_policy_id IN NUMBER,
1276 p_location_id IN NUMBER) RETURN NUMBER IS
1277 ------------------------------------------------------------------------
1278
1279 cursor schedule_option_cur is
1280 select schedule_option_id
1281 from ap_pol_schedule_options
1282 where policy_id = p_policy_id
1283 and option_type = 'LOCATION'
1284 and location_id = p_location_id
1285 and rownum = 1;
1286
1287 schedule_option_rec schedule_option_cur%ROWTYPE;
1288
1289 BEGIN
1290
1291 OPEN schedule_option_cur;
1292 FETCH schedule_option_cur INTO schedule_option_rec;
1293 CLOSE schedule_option_cur;
1294 return schedule_option_rec.schedule_option_id;
1295
1296 EXCEPTION
1297 WHEN OTHERS THEN
1298 raise;
1299
1300 END GetScheduleOption;
1301
1302
1303 ------------------------------------------------------------------------
1304 FUNCTION GetSchedulePeriod(p_policy_id IN NUMBER) RETURN NUMBER IS
1305 ------------------------------------------------------------------------
1306
1307 cursor schedule_period_cur is
1308 select schedule_period_id
1309 from ap_pol_schedule_periods
1310 where policy_id = p_policy_id
1311 and rownum = 1;
1312
1313 schedule_period_rec schedule_period_cur%ROWTYPE;
1314
1315 BEGIN
1316
1317 OPEN schedule_period_cur;
1318 FETCH schedule_period_cur INTO schedule_period_rec;
1319 CLOSE schedule_period_cur;
1320 return schedule_period_rec.schedule_period_id;
1321
1322 EXCEPTION
1323 WHEN OTHERS THEN
1324 raise;
1325
1326 END GetSchedulePeriod;
1327
1328
1329 ------------------------------------------------------------------------
1330 FUNCTION GetPolicyLine(p_policy_id IN NUMBER,
1331 p_schedule_period_id IN NUMBER,
1332 p_role_id IN NUMBER,
1333 p_location_id IN NUMBER,
1334 p_start_of_season IN VARCHAR2,
1335 p_end_of_season IN VARCHAR2,
1336 p_effective_start_date IN DATE) RETURN NUMBER IS
1337 ------------------------------------------------------------------------
1338
1339 cursor policy_line_cur is
1340 select policy_line_id
1341 from ap_pol_lines
1342 where policy_id = p_policy_id
1343 and schedule_period_id = p_schedule_period_id
1344 and nvl(role_id, -1) = nvl(p_role_id, -1)
1345 and location_id = p_location_id
1346 and nvl(start_of_season, -1) = nvl(p_start_of_season, -1)
1347 and nvl(end_of_season, -1) = nvl(p_end_of_season, -1)
1348 and effective_start_date = p_effective_start_date
1349 and rownum = 1;
1350
1351 policy_line_rec policy_line_cur%ROWTYPE;
1352
1353 BEGIN
1354
1355 OPEN policy_line_cur;
1356 FETCH policy_line_cur INTO policy_line_rec;
1357 CLOSE policy_line_cur;
1358 return policy_line_rec.policy_line_id;
1359
1360 EXCEPTION
1361 WHEN OTHERS THEN
1362 raise;
1363
1364 END GetPolicyLine;
1365
1366
1367 ------------------------------------------------------------------------
1368 FUNCTION CheckPolicyExists(p_expense_category IN VARCHAR2,
1369 p_policy_name IN VARCHAR2) RETURN VARCHAR2 IS
1370 ------------------------------------------------------------------------
1371
1372 CURSOR policy_cur IS
1373 select policy_id
1374 from ap_pol_headers
1375 where category_code = p_expense_category
1376 and upper(policy_name) = upper(p_policy_name)
1377 and rownum = 1;
1378
1379 policy_rec policy_cur%ROWTYPE;
1380
1381 BEGIN
1382
1383 OPEN policy_cur;
1384 FETCH policy_cur INTO policy_rec;
1385 CLOSE policy_cur;
1386
1387 if (policy_rec.policy_id is not null) then
1388
1389 -- policy already exists
1390 return 'Y';
1391
1392 end if;
1393
1394 return 'N';
1395
1396 EXCEPTION
1397 when others then
1398 APP_EXCEPTION.RAISE_EXCEPTION;
1399
1400 END CheckPolicyExists;
1401
1402
1403 ------------------------------------------------------------------------
1404 FUNCTION CheckPeriodExists(p_policy_id IN VARCHAR2,
1405 p_period_name IN VARCHAR2,
1406 p_period_start_date IN DATE) RETURN VARCHAR2 IS
1407 ------------------------------------------------------------------------
1408
1409 CURSOR period_cur IS
1410 select schedule_period_id
1411 from ap_pol_schedule_periods
1412 where policy_id = p_policy_id
1413 and upper(schedule_period_name) = upper(p_period_name)
1414 and start_date = p_period_start_date
1415 and rownum = 1;
1416
1417 period_rec period_cur%ROWTYPE;
1418
1419 BEGIN
1420
1421 OPEN period_cur;
1422 FETCH period_cur INTO period_rec;
1423 CLOSE period_cur;
1424
1425 if (period_rec.schedule_period_id is not null) then
1426
1427 -- period already exists
1428 return 'Y';
1429
1430 end if;
1431
1432 return 'N';
1433
1434 EXCEPTION
1435 when others then
1436 APP_EXCEPTION.RAISE_EXCEPTION;
1437
1438 END CheckPeriodExists;
1439
1440
1441 ------------------------------------------------------------------------
1442 FUNCTION GetLatestPeriodStartDate(p_policy_id IN VARCHAR2) RETURN DATE IS
1443 ------------------------------------------------------------------------
1444
1445 CURSOR latest_period_cur IS
1446 select max(start_date) as start_date
1447 from ap_pol_schedule_periods
1448 where policy_id = p_policy_id
1449 and rownum = 1;
1450
1451 latest_period_rec latest_period_cur%ROWTYPE;
1452
1453 BEGIN
1454
1455 OPEN latest_period_cur;
1456 FETCH latest_period_cur INTO latest_period_rec;
1457 CLOSE latest_period_cur;
1458
1459 return latest_period_rec.start_date;
1460
1461 EXCEPTION
1462 when others then
1463 APP_EXCEPTION.RAISE_EXCEPTION;
1464
1465 END GetLatestPeriodStartDate;
1466
1467
1468 ------------------------------------------------------------------------
1469 FUNCTION GetPerDiemTypeCode(p_policy_id IN VARCHAR2) RETURN VARCHAR2 IS
1470 ------------------------------------------------------------------------
1471
1472 CURSOR per_diem_type_code_cur IS
1473 select per_diem_type_code
1474 from ap_pol_headers
1475 where policy_id = p_policy_id
1476 and rownum = 1;
1477
1478 per_diem_type_code_rec per_diem_type_code_cur%ROWTYPE;
1479
1480 BEGIN
1481
1482 OPEN per_diem_type_code_cur;
1483 FETCH per_diem_type_code_cur INTO per_diem_type_code_rec;
1484 CLOSE per_diem_type_code_cur;
1485
1486 return per_diem_type_code_rec.per_diem_type_code;
1487
1488 EXCEPTION
1489 when others then
1490 APP_EXCEPTION.RAISE_EXCEPTION;
1491
1492 END GetPerDiemTypeCode;
1493
1494
1495 ------------------------------------------------------------------------
1496 FUNCTION GetMealsTypeCode(p_policy_id IN VARCHAR2) RETURN VARCHAR2 IS
1497 ------------------------------------------------------------------------
1498
1499 CURSOR meals_type_code_cur IS
1500 select meals_type_code
1501 from ap_pol_headers
1502 where policy_id = p_policy_id
1503 and rownum = 1;
1504
1505 meals_type_code_rec meals_type_code_cur%ROWTYPE;
1506
1507 BEGIN
1508
1509 OPEN meals_type_code_cur;
1510 FETCH meals_type_code_cur INTO meals_type_code_rec;
1511 CLOSE meals_type_code_cur;
1512
1513 return meals_type_code_rec.meals_type_code;
1514
1515 EXCEPTION
1516 when others then
1517 APP_EXCEPTION.RAISE_EXCEPTION;
1518
1519 END GetMealsTypeCode;
1520
1521
1522 ------------------------------------------------------------------------
1523 FUNCTION GetPerDiemTypeCode(p_rate_incl_meals IN VARCHAR2,
1524 p_rate_incl_inc IN VARCHAR2,
1525 p_rate_incl_acc IN VARCHAR2,
1526 p_meals_rate IN VARCHAR2) RETURN VARCHAR2 IS
1527 ------------------------------------------------------------------------
1528
1529 l_per_diem_type_code VARCHAR2(15) := '';
1530
1531 BEGIN
1532
1533 if ('Y' = p_rate_incl_meals) then
1534 if ('LOCAL' = p_meals_rate) then
1535 l_per_diem_type_code := 'M';
1536 elsif ('PROPORTIONAL' = p_meals_rate) then
1537 l_per_diem_type_code := 'P';
1538 end if;
1539 end if;
1540
1541 if ('Y' = p_rate_incl_acc) then
1542 l_per_diem_type_code := l_per_diem_type_code || 'A';
1543 end if;
1544
1545 if ('Y' = p_rate_incl_inc) then
1546 l_per_diem_type_code := l_per_diem_type_code || 'I';
1547 end if;
1548
1549 return l_per_diem_type_code;
1550
1551 EXCEPTION
1552 when others then
1553 APP_EXCEPTION.RAISE_EXCEPTION;
1554
1555 END GetPerDiemTypeCode;
1556
1557
1558 ------------------------------------------------------------------------
1559 FUNCTION GetRateIncludesMeals(p_per_diem_type_code IN VARCHAR2) RETURN VARCHAR2 IS
1560 ------------------------------------------------------------------------
1561
1562 BEGIN
1563
1564 if (instrb(p_per_diem_type_code, 'M', 1) > 0) then
1565 return 'Y';
1566 end if;
1567
1568 if (instrb(p_per_diem_type_code, 'P', 1) > 0) then
1569 return 'Y';
1570 end if;
1571
1572 return 'N';
1573
1574 EXCEPTION
1575 when no_data_found then
1576 return 'N';
1577 when others then
1578 APP_EXCEPTION.RAISE_EXCEPTION;
1579
1580 END GetRateIncludesMeals;
1581
1582
1583 ------------------------------------------------------------------------
1584 FUNCTION GetRateIncludesIncidentals(p_per_diem_type_code IN VARCHAR2) RETURN VARCHAR2 IS
1585 ------------------------------------------------------------------------
1586
1587 BEGIN
1588
1589 if (instrb(p_per_diem_type_code, 'I', 1) > 0) then
1590 return 'Y';
1591 end if;
1592
1593 return 'N';
1594
1595 EXCEPTION
1596 when no_data_found then
1597 return 'N';
1598 when others then
1599 APP_EXCEPTION.RAISE_EXCEPTION;
1600
1601 END GetRateIncludesIncidentals;
1602
1603
1604 ------------------------------------------------------------------------
1605 FUNCTION GetRateIncludesAccommodations(p_per_diem_type_code IN VARCHAR2) RETURN VARCHAR2 IS
1606 ------------------------------------------------------------------------
1607
1608 BEGIN
1609
1610 if (instrb(p_per_diem_type_code, 'A', 1) > 0) then
1611 return 'Y';
1612 end if;
1613
1614 return 'N';
1615
1616 EXCEPTION
1617 when no_data_found then
1618 return 'N';
1619 when others then
1620 APP_EXCEPTION.RAISE_EXCEPTION;
1621
1622 END GetRateIncludesAccommodations;
1623
1624
1625 ------------------------------------------------------------------------
1626 FUNCTION CalculateRate(p_expense_category IN VARCHAR2,
1627 p_rate_incl_meals IN VARCHAR2,
1628 p_rate_incl_inc IN VARCHAR2,
1629 p_rate_incl_acc IN VARCHAR2,
1630 p_meals_rate IN VARCHAR2,
1631 p_no_govt_meals_amt IN NUMBER,
1632 p_prop_meals_amt IN NUMBER,
1633 p_max_lodging_amt IN NUMBER,
1634 p_max_per_diem_amt IN NUMBER) RETURN NUMBER IS
1635 ------------------------------------------------------------------------
1636
1637 l_rate NUMBER := 0;
1638
1639 BEGIN
1640
1641 if ('PER_DIEM' = p_expense_category) then
1642
1643 if ('Y' = p_rate_incl_meals) then
1644 if ('LOCAL' = p_meals_rate) then
1645 l_rate := p_no_govt_meals_amt;
1646 elsif ('PROPORTIONAL' = p_meals_rate) then
1647 l_rate := p_prop_meals_amt;
1648 end if;
1649 end if;
1650
1651 if ('Y' = p_rate_incl_acc) then
1652 l_rate := l_rate + p_max_lodging_amt;
1653 end if;
1654
1655 if ('Y' = p_rate_incl_inc) then
1656 l_rate := l_rate + p_max_per_diem_amt - p_no_govt_meals_amt - p_max_lodging_amt;
1657 end if;
1658
1659 elsif ('MEALS' = p_expense_category) then
1660
1661 if ('LOCAL' = p_meals_rate) then
1662 l_rate := p_no_govt_meals_amt;
1663 elsif ('PROPORTIONAL' = p_meals_rate) then
1664 l_rate := p_prop_meals_amt;
1665 end if;
1666
1667 elsif ('ACCOMMODATIONS' = p_expense_category) then
1668
1669 l_rate := p_max_lodging_amt;
1670
1671 end if;
1672
1673 return l_rate;
1674
1675 EXCEPTION
1676 when others then
1677 APP_EXCEPTION.RAISE_EXCEPTION;
1678
1679 END CalculateRate;
1680
1681
1682 ------------------------------------------------------------------------
1683 PROCEDURE CreateSchedule(errbuf OUT NOCOPY VARCHAR2,
1684 retcode OUT NOCOPY NUMBER,
1685 p_ratetype IN VARCHAR2,
1686 p_expense_category IN VARCHAR2,
1687 p_policy_name IN VARCHAR2,
1688 p_policy_start_date IN DATE,
1689 p_period_name IN VARCHAR2,
1690 p_period_start_date IN DATE,
1691 p_rate_incl_meals IN VARCHAR2,
1692 p_rate_incl_inc IN VARCHAR2,
1693 p_rate_incl_acc IN VARCHAR2,
1694 p_meals_rate IN VARCHAR2,
1695 p_free_meals_ded IN VARCHAR2,
1696 p_use_free_acc_add IN VARCHAR2,
1697 p_use_free_acc_ded IN VARCHAR2,
1698 p_calc_method IN VARCHAR2,
1699 p_single_deduction IN NUMBER,
1700 p_breakfast_deduction IN NUMBER,
1701 p_lunch_deduction IN NUMBER,
1702 p_dinner_deduction IN NUMBER) IS
1703 ------------------------------------------------------------------------
1704
1705 l_debug_info VARCHAR2(200);
1706
1707 l_location_id NUMBER(15);
1708 l_location_status VARCHAR2(30);
1709 l_policy_id NUMBER(15);
1710 l_schedule_option_id NUMBER(15);
1711 l_schedule_period_id NUMBER(15);
1712 l_policy_line_id NUMBER(15);
1713 l_night_rate_line_id NUMBER(15);
1714 l_night_start_of_season VARCHAR2(5);
1715 l_night_end_of_season VARCHAR2(5);
1716
1717 l_per_diem_type_code VARCHAR2(30);
1718 l_rate NUMBER;
1719
1720 -------------------------------
1721 -- cursor to check if rates exist
1722 -------------------------------
1723 cursor rates_exist_cur is
1724 select 'Y' rates_exist
1725 from oie_pol_rates_interface
1726 where
1727 (
1728 (p_ratetype = 'CONUS' and country = 'UNITED STATES' and state_province not in ('HAWAII', 'ALASKA'))
1729 or
1730 (p_ratetype = 'OCONUS' and (country <> 'UNITED STATES' or state_province in ('HAWAII', 'ALASKA')))
1731 )
1732 and rownum = 1;
1733
1734 rates_exist_rec rates_exist_cur%ROWTYPE;
1735
1736 -------------------------------
1737 -- cursor for rates
1738 -------------------------------
1739 cursor rates_cur is
1740 select city_locality,
1741 county,
1742 state_province,
1743 country,
1744 start_of_season,
1745 end_of_season,
1746 max_lodging_amt,
1747 no_govt_meals_amt,
1748 prop_meals_amt,
1749 off_base_inc_amt,
1750 footnote_amt,
1751 footnote_rate_amt,
1752 max_per_diem_amt,
1753 effective_date,
1754 '' as effective_end_date
1755 from oie_pol_rates_interface
1756 where
1757 (
1758 (p_ratetype = 'CONUS' and country = 'UNITED STATES' and state_province not in ('HAWAII', 'ALASKA'))
1759 or
1760 (p_ratetype = 'OCONUS' and (country <> 'UNITED STATES' or state_province in ('HAWAII', 'ALASKA')))
1761 )
1762 order by country, state_province, county, city_locality, to_date(effective_date, 'MM/DD/RRRR') desc, start_of_season desc;
1763
1764 rates_rec rates_cur%ROWTYPE;
1765
1766 last_location_id NUMBER(15);
1767 last_rates_rec rates_cur%ROWTYPE;
1768
1769 -------------------------------
1770 -- cursor for gap rates
1771 -------------------------------
1772 cursor rates_gap_cur is
1773 select l1.location_id, min(l1.effective_start_date) as effective_start_date,
1774 '01/01' as start_of_season, '12/31' as end_of_season
1775 from ap_pol_lines l1
1776 where l1.policy_id = l_policy_id
1777 and not exists
1778 (select 'Y'
1779 from ap_pol_lines l2
1780 where l2.policy_id = l1.policy_id
1781 and l2.location_id = l1.location_id
1782 and l2.effective_start_date = p_period_start_date
1783 and rownum = 1
1784 ) group by l1.location_id;
1785
1786 rates_gap_rec rates_gap_cur%ROWTYPE;
1787
1788 l_eliminate_seasonality boolean := false;
1789
1790 l_undefined_conus_location_id NUMBER := GetUndefinedCONUS;
1791 l_undefined_location_id NUMBER := GetUndefinedLocation;
1792
1793 -------------------------------
1794 -- cursor for undefined conus rate
1795 -------------------------------
1796 cursor undefined_conus_rate_cur is
1797 select rate, '01/01' as start_of_season, '12/31' as end_of_season
1798 from ap_pol_lines
1799 where policy_id = l_policy_id
1800 and location_id = l_undefined_conus_location_id;
1801
1802 undefined_conus_rate_rec undefined_conus_rate_cur%ROWTYPE;
1803
1804 -------------------------------
1805 -- cursor for undefined location rate
1806 -------------------------------
1807 cursor undefined_loc_rate_cur is
1808 select rate, '01/01' as start_of_season, '12/31' as end_of_season
1809 from ap_pol_lines
1810 where policy_id = l_policy_id
1811 and location_id = l_undefined_location_id;
1812
1813 undefined_loc_rate_rec undefined_loc_rate_cur%ROWTYPE;
1814
1815 BEGIN
1816
1817 OPEN rates_exist_cur;
1818 FETCH rates_exist_cur INTO rates_exist_rec;
1819 CLOSE rates_exist_cur;
1820
1821 if (rates_exist_rec.rates_exist is null) then
1822 --
1823 put_line('No rates to process!');
1824 --
1825 return;
1826 end if;
1827
1828 --
1829 put_line('------------------------------------------------------------');
1830 --
1831 if ('PER_DIEM' = p_expense_category) then
1832 --
1833 put_line('GetPerDiemTypeCode');
1834 --
1835 l_per_diem_type_code := GetPerDiemTypeCode(p_rate_incl_meals,
1836 p_rate_incl_inc,
1837 p_rate_incl_acc,
1838 p_meals_rate);
1839 --
1840 put_line('Per Diem Type Code: '||l_per_diem_type_code);
1841 --
1842 end if;
1843
1844 --
1845 put_line('Create Policy');
1846 --
1847 l_policy_id := CreatePolicy(p_expense_category => p_expense_category,
1848 p_policy_name => p_policy_name,
1849 p_policy_start_date => p_policy_start_date,
1850 p_per_diem_type_code => l_per_diem_type_code,
1851 p_meals_rate => p_meals_rate,
1852 p_free_meals_ded => p_free_meals_ded,
1853 p_use_free_acc_add => p_use_free_acc_add,
1854 p_use_free_acc_ded => p_use_free_acc_ded);
1855 --
1856 put_line('Created Policy Id: '||l_policy_id);
1857 --
1858
1859 --
1860 put_line('Create Schedule Period');
1861 --
1862 l_schedule_period_id := CreateSchedulePeriod(p_policy_id => l_policy_id,
1863 p_period_name => p_period_name,
1864 p_period_start_date => p_period_start_date);
1865 --
1866 put_line('Created Schedule Period Id: '||l_schedule_period_id);
1867 --
1868
1869 /* Eliminate seasonality when schedule
1870 - contains only meals or meals and incidental rates
1871 - doesn't contain Accommodations
1872 */
1873 if ('MEALS' = p_expense_category or
1874 ('PER_DIEM' = p_expense_category and not (instrb(l_per_diem_type_code,'A') > 0))
1875 ) then
1876
1877 --
1878 put_line('Eliminating seasonality');
1879 --
1880 l_eliminate_seasonality := true;
1881
1882 end if; /* Schedule contains only meals or meals and incidental rates */
1883
1884
1885 OPEN rates_cur;
1886 loop
1887
1888 FETCH rates_cur INTO rates_rec;
1889 EXIT WHEN rates_cur%NOTFOUND;
1890
1891 g_num_recs_processed := g_num_recs_processed + 1;
1892
1893 --
1894 put_line('------------------------------------------------------------');
1895 --
1896 l_location_id := GetCityLocation(p_city_locality => rates_rec.city_locality,
1897 p_county => rates_rec.county,
1898 p_state_province => rates_rec.state_province,
1899 p_country => rates_rec.country);
1900
1901 l_location_status := get_location_status(p_location_id => l_location_id);
1902
1903 --
1904 put_line('Location Id : '||l_location_id);
1905 put_line('Location Status : '||l_location_status);
1906
1907 put_line('City Locality : '||rates_rec.city_locality);
1908 put_line('County : '||rates_rec.county);
1909 put_line('State Province : '||rates_rec.state_province);
1910 put_line('Country : '||rates_rec.country);
1911 put_line('Start of Season : '||rates_rec.start_of_season);
1912 put_line('End of Season : '||rates_rec.end_of_season);
1913 put_line('Max Lodging Amt : '||rates_rec.max_lodging_amt);
1914 put_line('No Govt Meals Amt : '||rates_rec.no_govt_meals_amt);
1915 put_line('Prop Meals Amt : '||rates_rec.prop_meals_amt);
1916 put_line('Off Base Inc Amt : '||rates_rec.off_base_inc_amt);
1917 put_line('Footnote Amt : '||rates_rec.footnote_amt);
1918 put_line('Footnote Rate Amt : '||rates_rec.footnote_rate_amt);
1919 put_line('Max Per Diem Amt : '||rates_rec.max_per_diem_amt);
1920 put_line('Effective Date : '||rates_rec.effective_date);
1921 --
1922
1923
1924 if (nvl(rates_rec.max_lodging_amt, 0) = 0 and
1925 nvl(rates_rec.no_govt_meals_amt, 0) = 0 and
1926 nvl(rates_rec.prop_meals_amt, 0) = 0 and
1927 nvl(rates_rec.max_per_diem_amt, 0) = 0) then
1928
1929 put_line('Note: this is a zero rate location');
1930 AddToZeroRates(rates_rec.city_locality, rates_rec.county, rates_rec.state_province, rates_rec.country);
1931
1932 end if;
1933
1934 if (l_location_id is not null and l_location_status <> 'INVALID') then
1935
1936 if (p_period_start_date <= to_date(rates_rec.effective_date, 'MM/DD/RRRR')) then
1937
1938 --
1939 put_line('Leaving Effective Start Date as is');
1940 --
1941 null;
1942
1943 elsif (p_period_start_date > to_date(rates_rec.effective_date, 'MM/DD/RRRR')) then
1944
1945 --
1946 put_line('Setting Effective Start Date to Period Start Date');
1947 --
1948 rates_rec.effective_date := to_char(p_period_start_date, 'MM/DD/RRRR');
1949
1950 end if; /* */
1951
1952 if (last_location_id = l_location_id) then
1953
1954 if (last_rates_rec.effective_date <> rates_rec.effective_date) then
1955
1956 --
1957 put_line('Setting Effective End Date to Last Effective Start Date - 1');
1958 --
1959 rates_rec.effective_end_date := to_char(to_date(last_rates_rec.effective_date, 'MM/DD/RRRR') - 1, 'MM/DD/RRRR');
1960
1961 else
1962
1963 --
1964 put_line('Setting Effective End Date to Last Effective End Date');
1965 --
1966 rates_rec.effective_end_date := last_rates_rec.effective_end_date;
1967
1968 end if;
1969
1970 end if; /* (last_location_id = l_location_id) */
1971
1972
1973 l_schedule_option_id := GetScheduleOption(p_policy_id => l_policy_id,
1974 p_location_id => l_location_id);
1975
1976 if (l_schedule_option_id is null) then
1977
1978 --
1979 put_line('Create Schedule Option');
1980 --
1981 l_schedule_option_id := CreateScheduleOption(p_policy_id => l_policy_id,
1982 p_location_id => l_location_id);
1983
1984 else
1985
1986 --
1987 put_line('Schedule Option exists');
1988 --
1989 null;
1990
1991 end if; /* l_schedule_option_id is null */
1992
1993 --
1994 put_line('Schedule Option: '||l_schedule_option_id);
1995 --
1996
1997 l_night_start_of_season := rates_rec.start_of_season;
1998 l_night_end_of_season := rates_rec.end_of_season;
1999 if (l_eliminate_seasonality) then
2000 rates_rec.start_of_season := null;
2001 rates_rec.end_of_season := null;
2002 end if;
2003
2004 l_rate := CalculateRate(p_expense_category => p_expense_category,
2005 p_rate_incl_meals => p_rate_incl_meals,
2006 p_rate_incl_inc => p_rate_incl_inc,
2007 p_rate_incl_acc => p_rate_incl_acc,
2008 p_meals_rate => p_meals_rate,
2009 p_no_govt_meals_amt => rates_rec.no_govt_meals_amt,
2010 p_prop_meals_amt => rates_rec.prop_meals_amt,
2011 p_max_lodging_amt => rates_rec.max_lodging_amt,
2012 p_max_per_diem_amt => rates_rec.max_per_diem_amt);
2013
2014
2015 l_policy_line_id := GetPolicyLine(p_policy_id => l_policy_id,
2016 p_schedule_period_id => l_schedule_period_id,
2017 p_role_id => null,
2018 p_location_id => l_location_id,
2019 p_start_of_season => rates_rec.start_of_season,
2020 p_end_of_season => rates_rec.end_of_season,
2021 p_effective_start_date => to_date(rates_rec.effective_date, 'MM/DD/RRRR'));
2022
2023
2024 if (l_policy_line_id is null) then
2025
2026 --
2027 put_line('Create Standard Rate');
2028 --
2029
2030 l_policy_line_id := CreatePolicyLine(p_policy_id => l_policy_id,
2031 p_schedule_period_id => l_schedule_period_id,
2032 p_role_id => null,
2033 p_location_id => l_location_id,
2034 p_rate => l_rate,
2035 p_calc_method => p_calc_method,
2036 p_single_deduction => p_single_deduction,
2037 p_breakfast_deduction => p_breakfast_deduction,
2038 p_lunch_deduction => p_lunch_deduction,
2039 p_dinner_deduction => p_dinner_deduction,
2040 p_start_of_season => rates_rec.start_of_season,
2041 p_end_of_season => rates_rec.end_of_season,
2042 p_max_lodging_amt => rates_rec.max_lodging_amt,
2043 p_no_govt_meals_amt => rates_rec.no_govt_meals_amt,
2044 p_prop_meals_amt => rates_rec.prop_meals_amt,
2045 p_off_base_inc_amt => rates_rec.off_base_inc_amt,
2046 p_footnote_amt => rates_rec.footnote_amt,
2047 p_footnote_rate_amt => rates_rec.footnote_rate_amt,
2048 p_max_per_diem_amt => rates_rec.max_per_diem_amt,
2049 p_effective_start_date => to_date(rates_rec.effective_date, 'MM/DD/RRRR'),
2050 p_effective_end_date => to_date(rates_rec.effective_end_date, 'MM/DD/RRRR'),
2051 p_use_free_acc_add => p_use_free_acc_add,
2052 p_use_free_acc_ded => p_use_free_acc_ded);
2053
2054 --
2055 put_line('Created Standard Rate Id: '||l_policy_line_id||' with rate: '||l_rate);
2056 --
2057 g_num_std_rates_created := g_num_std_rates_created + 1;
2058
2059 if (nvl(p_use_free_acc_add, 'N') = 'Y') then
2060
2061 l_night_rate_line_id := CreateNightRateLine(p_policy_id => l_policy_id,
2062 p_schedule_period_id => l_schedule_period_id,
2063 p_role_id => null,
2064 p_location_id => l_location_id,
2065 p_rate => rates_rec.max_lodging_amt,
2066 p_single_deduction => p_single_deduction,
2067 p_breakfast_deduction => p_breakfast_deduction,
2068 p_lunch_deduction => p_lunch_deduction,
2069 p_dinner_deduction => p_dinner_deduction,
2070 p_start_of_season => l_night_start_of_season,
2071 p_end_of_season => l_night_end_of_season,
2072 p_max_lodging_amt => rates_rec.max_lodging_amt,
2073 p_no_govt_meals_amt => rates_rec.no_govt_meals_amt,
2074 p_prop_meals_amt => rates_rec.prop_meals_amt,
2075 p_off_base_inc_amt => rates_rec.off_base_inc_amt,
2076 p_footnote_amt => rates_rec.footnote_amt,
2077 p_footnote_rate_amt => rates_rec.footnote_rate_amt,
2078 p_max_per_diem_amt => rates_rec.max_per_diem_amt,
2079 p_effective_start_date => to_date(rates_rec.effective_date, 'MM/DD/RRRR'),
2080 p_effective_end_date => to_date(rates_rec.effective_end_date, 'MM/DD/RRRR'));
2081
2082 --
2083 put_line('Created Night Rate Id: '||l_night_rate_line_id||' with rate: '||l_rate);
2084 --
2085 g_num_night_rates_created := g_num_night_rates_created + 1;
2086
2087 end if; /* nvl(p_use_free_acc_add, 'N') = 'Y' */
2088
2089 else
2090
2091 --
2092 put_line('Standard Rate Id exists: '||l_policy_line_id);
2093 --
2094
2095 end if; /* l_policy_line_id is null */
2096
2097
2098 last_location_id := l_location_id;
2099 last_rates_rec.no_govt_meals_amt := rates_rec.no_govt_meals_amt;
2100 last_rates_rec.prop_meals_amt := rates_rec.prop_meals_amt;
2101 last_rates_rec.off_base_inc_amt := rates_rec.off_base_inc_amt;
2102 last_rates_rec.max_per_diem_amt := rates_rec.max_per_diem_amt;
2103 last_rates_rec.effective_date := rates_rec.effective_date;
2104 last_rates_rec.effective_end_date := rates_rec.effective_end_date;
2105
2106
2107 else
2108
2109 --
2110 put_line('Cannot create Standard Rate for Invalid Location Id: '||l_location_id);
2111 --
2112 AddToInvalidLocs(rates_rec.city_locality, rates_rec.county, rates_rec.state_province, rates_rec.country);
2113
2114
2115 end if; /* l_location_id is not null and l_location_status <> 'INVALID' */
2116
2117 end loop;
2118 CLOSE rates_cur;
2119
2120
2121 if (p_ratetype = 'CONUS') then
2122
2123 --
2124 put_line('------------------------------------------------------------');
2125 --
2126 --
2127 put_line('Checking for Undefined CONUS Standard Rate');
2128 --
2129
2130 open undefined_conus_rate_cur;
2131
2132 fetch undefined_conus_rate_cur into undefined_conus_rate_rec;
2133
2134 if (undefined_conus_rate_cur%notfound) then
2135
2136 undefined_conus_rate_rec.rate := null;
2137 if (l_eliminate_seasonality) then
2138 undefined_conus_rate_rec.start_of_season := null;
2139 undefined_conus_rate_rec.end_of_season := null;
2140 end if;
2141
2142 l_rate := undefined_conus_rate_rec.rate;
2143
2144 l_schedule_option_id := GetScheduleOption(p_policy_id => l_policy_id,
2145 p_location_id => l_undefined_conus_location_id);
2146
2147 if (l_schedule_option_id is null) then
2148
2149 --
2150 put_line('Create Undefined CONUS Schedule Option');
2151 --
2152 l_schedule_option_id := CreateScheduleOption(p_policy_id => l_policy_id,
2153 p_location_id => l_undefined_conus_location_id);
2154
2155 else
2156
2157 --
2158 put_line('Undefined CONUS Schedule Option exists');
2159 --
2160 null;
2161
2162 end if; /* l_schedule_option_id is null */
2163
2164 --
2165 put_line('Create Undefined CONUS Standard Rate');
2166 --
2167
2168 l_policy_line_id := CreatePolicyLine(p_policy_id => l_policy_id,
2169 p_schedule_period_id => l_schedule_period_id,
2170 p_role_id => null,
2171 p_location_id => l_undefined_conus_location_id,
2172 p_rate => l_rate,
2173 p_calc_method => null,
2174 p_single_deduction => null,
2175 p_breakfast_deduction => null,
2176 p_lunch_deduction => null,
2177 p_dinner_deduction => null,
2178 p_start_of_season => undefined_conus_rate_rec.start_of_season,
2179 p_end_of_season => undefined_conus_rate_rec.end_of_season,
2180 p_max_lodging_amt => null,
2181 p_no_govt_meals_amt => null,
2182 p_prop_meals_amt => null,
2183 p_off_base_inc_amt => null,
2184 p_footnote_amt => null,
2185 p_footnote_rate_amt => null,
2186 p_max_per_diem_amt => null,
2187 p_effective_start_date => p_period_start_date,
2188 p_effective_end_date => null,
2189 p_use_free_acc_add => p_use_free_acc_add,
2190 p_use_free_acc_ded => p_use_free_acc_ded);
2191
2192 --
2193 put_line('Created Undefined CONUS Standard Rate Id: '||l_policy_line_id||' with rate: '||l_rate);
2194 --
2195 g_num_std_rates_created := g_num_std_rates_created + 1;
2196
2197 if (nvl(p_use_free_acc_add, 'N') = 'Y') then
2198
2199 l_night_rate_line_id := CreateNightRateLine(p_policy_id => l_policy_id,
2200 p_schedule_period_id => l_schedule_period_id,
2201 p_role_id => null,
2202 p_location_id => l_undefined_conus_location_id,
2203 p_rate => null,
2204 p_single_deduction => null,
2205 p_breakfast_deduction => null,
2206 p_lunch_deduction => null,
2207 p_dinner_deduction => null,
2208 p_start_of_season => '01/01',
2209 p_end_of_season => '12/31',
2210 p_max_lodging_amt => null,
2211 p_no_govt_meals_amt => null,
2212 p_prop_meals_amt => null,
2213 p_off_base_inc_amt => null,
2214 p_footnote_amt => null,
2215 p_footnote_rate_amt => null,
2216 p_max_per_diem_amt => null,
2217 p_effective_start_date => p_period_start_date,
2218 p_effective_end_date => null);
2219
2220 --
2221 put_line('Created Undefined CONUS Night Rate Id: '||l_night_rate_line_id||' with rate: '||l_rate);
2222 --
2223 g_num_night_rates_created := g_num_night_rates_created + 1;
2224
2225 end if; /* nvl(p_use_free_acc_add, 'N') = 'Y' */
2226
2227 else
2228 l_rate := undefined_conus_rate_rec.rate;
2229 --
2230 put_line('Got Undefined CONUS Standard Rate with rate: '||l_rate);
2231 --
2232 end if;
2233
2234 close undefined_conus_rate_cur;
2235
2236 end if;
2237
2238 --
2239 put_line('------------------------------------------------------------');
2240 --
2241 --
2242 put_line('Always check for Undefined Location Standard Rate');
2243 --
2244
2245 open undefined_loc_rate_cur;
2246
2247 fetch undefined_loc_rate_cur into undefined_loc_rate_rec;
2248
2249 if (undefined_loc_rate_cur%notfound) then
2250
2251 undefined_loc_rate_rec.rate := null;
2252 if (l_eliminate_seasonality) then
2253 undefined_loc_rate_rec.start_of_season := null;
2254 undefined_loc_rate_rec.end_of_season := null;
2255 end if;
2256
2257 l_rate := undefined_loc_rate_rec.rate;
2258
2259 l_schedule_option_id := GetScheduleOption(p_policy_id => l_policy_id,
2260 p_location_id => l_undefined_location_id);
2261
2262 if (l_schedule_option_id is null) then
2263
2264 --
2265 put_line('Create Undefined Location Schedule Option');
2266 --
2267 l_schedule_option_id := CreateScheduleOption(p_policy_id => l_policy_id,
2268 p_location_id => l_undefined_location_id);
2269
2270 else
2271
2272 --
2273 put_line('Undefined Location Schedule Option exists');
2274 --
2275 null;
2276
2277 end if; /* l_schedule_option_id is null */
2278
2279 --
2280 put_line('Create Undefined Location Standard Rate');
2281 --
2282
2283 l_policy_line_id := CreatePolicyLine(p_policy_id => l_policy_id,
2284 p_schedule_period_id => l_schedule_period_id,
2285 p_role_id => null,
2286 p_location_id => l_undefined_location_id,
2287 p_rate => l_rate,
2288 p_calc_method => null,
2289 p_single_deduction => null,
2290 p_breakfast_deduction => null,
2291 p_lunch_deduction => null,
2292 p_dinner_deduction => null,
2293 p_start_of_season => undefined_loc_rate_rec.start_of_season,
2294 p_end_of_season => undefined_loc_rate_rec.end_of_season,
2295 p_max_lodging_amt => null,
2296 p_no_govt_meals_amt => null,
2297 p_prop_meals_amt => null,
2298 p_off_base_inc_amt => null,
2299 p_footnote_amt => null,
2300 p_footnote_rate_amt => null,
2301 p_max_per_diem_amt => null,
2302 p_effective_start_date => p_period_start_date,
2303 p_effective_end_date => null,
2304 p_use_free_acc_add => p_use_free_acc_add,
2305 p_use_free_acc_ded => p_use_free_acc_ded);
2306
2307 --
2308 put_line('Created Undefined Location Standard Rate Id: '||l_policy_line_id||' with rate: '||l_rate);
2309 --
2310 g_num_std_rates_created := g_num_std_rates_created + 1;
2311
2312 if (nvl(p_use_free_acc_add, 'N') = 'Y') then
2313
2314 l_night_rate_line_id := CreateNightRateLine(p_policy_id => l_policy_id,
2315 p_schedule_period_id => l_schedule_period_id,
2316 p_role_id => null,
2317 p_location_id => l_undefined_location_id,
2318 p_rate => null,
2319 p_single_deduction => null,
2320 p_breakfast_deduction => null,
2321 p_lunch_deduction => null,
2322 p_dinner_deduction => null,
2323 p_start_of_season => '01/01',
2324 p_end_of_season => '12/31',
2325 p_max_lodging_amt => null,
2326 p_no_govt_meals_amt => null,
2327 p_prop_meals_amt => null,
2328 p_off_base_inc_amt => null,
2329 p_footnote_amt => null,
2330 p_footnote_rate_amt => null,
2331 p_max_per_diem_amt => null,
2332 p_effective_start_date => p_period_start_date,
2333 p_effective_end_date => null);
2334
2335 --
2336 put_line('Created Undefined Location Night Rate Id: '||l_night_rate_line_id||' with rate: '||l_rate);
2337 --
2338 g_num_night_rates_created := g_num_night_rates_created + 1;
2339
2340 end if; /* nvl(p_use_free_acc_add, 'N') = 'Y' */
2341
2342 else
2343 l_rate := undefined_loc_rate_rec.rate;
2344 --
2345 put_line('Got Undefined Location Standard Rate with rate: '||l_rate);
2346 --
2347 end if;
2348
2349 close undefined_loc_rate_cur;
2350
2351
2352 --
2353 put_line('------------------------------------------------------------');
2354 --
2355 --
2356 put_line('Always check for Gap Standard Rate');
2357 --
2358 OPEN rates_gap_cur;
2359 loop
2360
2361 FETCH rates_gap_cur INTO rates_gap_rec;
2362 EXIT WHEN rates_gap_cur%NOTFOUND;
2363
2364 if (l_eliminate_seasonality) then
2365 rates_gap_rec.start_of_season := null;
2366 rates_gap_rec.end_of_season := null;
2367 end if;
2368
2369 --
2370 put_line('Create Gap Standard Rate');
2371 --
2372
2373 l_policy_line_id := CreatePolicyLine(p_policy_id => l_policy_id,
2374 p_schedule_period_id => l_schedule_period_id,
2375 p_role_id => null,
2376 p_location_id => rates_gap_rec.location_id,
2377 p_rate => l_rate,
2378 p_calc_method => null,
2379 p_single_deduction => null,
2380 p_breakfast_deduction => null,
2381 p_lunch_deduction => null,
2382 p_dinner_deduction => null,
2383 p_start_of_season => rates_gap_rec.start_of_season,
2384 p_end_of_season => rates_gap_rec.end_of_season,
2385 p_max_lodging_amt => null,
2386 p_no_govt_meals_amt => null,
2387 p_prop_meals_amt => null,
2388 p_off_base_inc_amt => null,
2389 p_footnote_amt => null,
2390 p_footnote_rate_amt => null,
2391 p_max_per_diem_amt => null,
2392 p_effective_start_date => p_period_start_date,
2393 p_effective_end_date => rates_gap_rec.effective_start_date - 1,
2394 p_use_free_acc_add => p_use_free_acc_add,
2395 p_use_free_acc_ded => p_use_free_acc_ded);
2396
2397 --
2398 put_line('Created Gap Standard Rate Id: '||l_policy_line_id||' with rate: '||l_rate);
2399 --
2400 g_num_std_rates_created := g_num_std_rates_created + 1;
2401
2402 if (nvl(p_use_free_acc_add, 'N') = 'Y') then
2403
2404 l_night_rate_line_id := CreateNightRateLine(p_policy_id => l_policy_id,
2405 p_schedule_period_id => l_schedule_period_id,
2406 p_role_id => null,
2407 p_location_id => rates_gap_rec.location_id,
2408 p_rate => null,
2409 p_single_deduction => null,
2410 p_breakfast_deduction => null,
2411 p_lunch_deduction => null,
2412 p_dinner_deduction => null,
2413 p_start_of_season => '01/01',
2414 p_end_of_season => '12/31',
2415 p_max_lodging_amt => null,
2416 p_no_govt_meals_amt => null,
2417 p_prop_meals_amt => null,
2418 p_off_base_inc_amt => null,
2419 p_footnote_amt => null,
2420 p_footnote_rate_amt => null,
2421 p_max_per_diem_amt => null,
2422 p_effective_start_date => p_period_start_date,
2423 p_effective_end_date => rates_gap_rec.effective_start_date - 1);
2424
2425 --
2426 put_line('Created Gap Night Rate Id: '||l_night_rate_line_id||' with rate: '||l_rate);
2427 --
2428 g_num_night_rates_created := g_num_night_rates_created + 1;
2429
2430 end if; /* nvl(p_use_free_acc_add, 'N') = 'Y' */
2431
2432 end loop;
2433 CLOSE rates_gap_cur;
2434
2435
2436 EXCEPTION
2437 when others then
2438 raise;
2439
2440
2441 END CreateSchedule;
2442
2443
2444 PROCEDURE UpdateEndDates(
2445 p_policy_id IN NUMBER,
2446 p_schedule_period_id IN NUMBER) IS
2447
2448 BEGIN
2449
2450 update ap_pol_lines apl1
2451 set apl1.effective_end_date =
2452 (
2453 select min(apl2.effective_start_date)-1
2454 from ap_pol_lines apl2
2455 where apl2.policy_id = apl1.policy_id
2456 and apl2.schedule_period_id = apl1.schedule_period_id
2457 and apl2.policy_line_id <> apl1.policy_line_id
2458 and apl2.location_id = apl1.location_id
2459 and apl2.effective_start_date > apl1.effective_start_date
2460 )
2461 where policy_id = p_policy_id
2462 and schedule_period_id = p_schedule_period_id
2463 and apl1.effective_end_date is null
2464 and
2465 exists
2466 (select 'Y'
2467 from ap_pol_lines apl3
2468 where apl3.policy_id = apl1.policy_id
2469 and apl3.schedule_period_id = apl1.schedule_period_id
2470 and apl3.policy_line_id <> apl1.policy_line_id
2471 and apl3.location_id = apl1.location_id
2472 and apl3.effective_start_date > apl1.effective_start_date);
2473
2474
2475 g_num_std_rates_updated := sql%rowcount;
2476
2477
2478 EXCEPTION
2479 when others then
2480 raise;
2481
2482 END UpdateEndDates;
2483
2484
2485 ------------------------------------------------------------------------
2486 PROCEDURE UpdateSchedule(errbuf OUT NOCOPY VARCHAR2,
2487 retcode OUT NOCOPY NUMBER,
2488 p_ratetype IN VARCHAR2,
2489 p_expense_category IN VARCHAR2,
2490 p_policy_id IN NUMBER,
2491 p_period_type IN VARCHAR2,
2492 p_period_id IN VARCHAR2,
2493 p_period_name IN VARCHAR2,
2494 p_period_start_date IN DATE,
2495 p_rate_incl_meals IN VARCHAR2,
2496 p_rate_incl_inc IN VARCHAR2,
2497 p_rate_incl_acc IN VARCHAR2,
2498 p_meals_rate IN VARCHAR2,
2499 p_free_meals_ded IN VARCHAR2,
2500 p_use_free_acc_add IN VARCHAR2,
2501 p_use_free_acc_ded IN VARCHAR2,
2502 p_calc_method IN VARCHAR2,
2503 p_single_deduction IN NUMBER,
2504 p_breakfast_deduction IN NUMBER,
2505 p_lunch_deduction IN NUMBER,
2506 p_dinner_deduction IN NUMBER) IS
2507 ------------------------------------------------------------------------
2508
2509 l_debug_info VARCHAR2(200);
2510
2511 l_role_id NUMBER(15);
2512 l_location_id NUMBER(15);
2513 l_location_status VARCHAR2(30);
2514 l_schedule_option_id NUMBER(15);
2515 l_schedule_period_id NUMBER(15);
2516 l_policy_line_id NUMBER(15);
2517 l_night_rate_line_id NUMBER(15);
2518 l_night_start_of_season VARCHAR2(5);
2519 l_night_end_of_season VARCHAR2(5);
2520
2521 l_per_diem_type_code VARCHAR2(30);
2522 l_rate NUMBER;
2523
2524 -------------------------------
2525 -- cursor roles
2526 -------------------------------
2527 cursor roles_cur is
2528 select role_id
2529 from ap_pol_schedule_options
2530 where policy_id = p_policy_id
2531 and option_type = AP_WEB_POLICY_UTILS.c_EMPLOYEE_ROLE
2532 and role_id is not null;
2533
2534 roles_rec roles_cur%ROWTYPE;
2535 one_role_processed boolean;
2536
2537 -------------------------------
2538 -- cursor to check if rates exist
2539 -------------------------------
2540 cursor rates_exist_cur is
2541 select 'Y' rates_exist
2542 from oie_pol_rates_interface
2543 where
2544 (
2545 (p_ratetype = 'CONUS' and country = 'UNITED STATES' and state_province not in ('HAWAII', 'ALASKA'))
2546 or
2547 (p_ratetype = 'OCONUS' and (country <> 'UNITED STATES' or state_province in ('HAWAII', 'ALASKA')))
2548 )
2549 and rownum = 1;
2550
2551 rates_exist_rec rates_exist_cur%ROWTYPE;
2552
2553 -------------------------------
2554 -- cursor for rates
2555 -------------------------------
2556 cursor rates_cur is
2557 select city_locality,
2558 county,
2559 state_province,
2560 country,
2561 start_of_season,
2562 end_of_season,
2563 max_lodging_amt,
2564 no_govt_meals_amt,
2565 prop_meals_amt,
2566 off_base_inc_amt,
2567 footnote_amt,
2568 footnote_rate_amt,
2569 max_per_diem_amt,
2570 effective_date,
2571 '' as effective_end_date
2572 from oie_pol_rates_interface
2573 where
2574 (
2575 (p_ratetype = 'CONUS' and country = 'UNITED STATES' and state_province not in ('HAWAII', 'ALASKA'))
2576 or
2577 (p_ratetype = 'OCONUS' and (country <> 'UNITED STATES' or state_province in ('HAWAII', 'ALASKA')))
2578 )
2579 order by country, state_province, county, city_locality, to_date(effective_date, 'MM/DD/RRRR') desc, start_of_season desc;
2580
2581 rates_rec rates_cur%ROWTYPE;
2582
2583 last_location_id NUMBER(15);
2584 last_rates_rec rates_cur%ROWTYPE;
2585
2586 -------------------------------
2587 -- cursor for gap rates
2588 -------------------------------
2589 cursor rates_gap_cur is
2590 select l1.role_id, l1.location_id, min(l1.effective_start_date) as effective_start_date,
2591 '01/01' as start_of_season, '12/31' as end_of_season
2592 from ap_pol_lines l1
2593 where l1.policy_id = p_policy_id
2594 and not exists
2595 (select 'Y'
2596 from ap_pol_lines l2
2597 where l2.policy_id = l1.policy_id
2598 and nvl(l2.role_id, -1) = nvl(l1.role_id, -1)
2599 and l2.location_id = l1.location_id
2600 and l2.effective_start_date = p_period_start_date
2601 and rownum = 1
2602 ) group by l1.role_id, l1.location_id;
2603
2604 rates_gap_rec rates_gap_cur%ROWTYPE;
2605
2606 l_eliminate_seasonality boolean := false;
2607
2608 l_undefined_conus_location_id NUMBER := GetUndefinedCONUS;
2609 l_undefined_location_id NUMBER := GetUndefinedLocation;
2610
2611 -------------------------------
2612 -- cursor for undefined conus rate
2613 -------------------------------
2614 cursor undefined_conus_rate_cur is
2615 select rate, '01/01' as start_of_season, '12/31' as end_of_season
2616 from ap_pol_lines
2617 where policy_id = p_policy_id
2618 and nvl(role_id, -1) = nvl(l_role_id, -1)
2619 and location_id = l_undefined_conus_location_id;
2620
2621 undefined_conus_rate_rec undefined_conus_rate_cur%ROWTYPE;
2622
2623 -------------------------------
2624 -- cursor for undefined location rate
2625 -------------------------------
2626 cursor undefined_loc_rate_cur is
2627 select rate, '01/01' as start_of_season, '12/31' as end_of_season
2628 from ap_pol_lines
2629 where policy_id = p_policy_id
2630 and nvl(role_id, -1) = nvl(l_role_id, -1)
2631 and location_id = l_undefined_location_id;
2632
2633 undefined_loc_rate_rec undefined_loc_rate_cur%ROWTYPE;
2634
2635
2636 BEGIN
2637
2638 OPEN rates_exist_cur;
2639 FETCH rates_exist_cur INTO rates_exist_rec;
2640 CLOSE rates_exist_cur;
2641
2642 if (rates_exist_rec.rates_exist is null) then
2643 --
2644 put_line('No rates to process!');
2645 --
2646 return;
2647 end if;
2648
2649 --
2650 put_line('------------------------------------------------------------');
2651 --
2652 if ('PER_DIEM' = p_expense_category) then
2653 --
2654 put_line('GetPerDiemTypeCode');
2655 --
2656 l_per_diem_type_code := GetPerDiemTypeCode(p_policy_id);
2657 --
2658 put_line('Per Diem Type Code: '||l_per_diem_type_code);
2659 --
2660 end if;
2661
2662 if ('NEW' = p_period_type) then
2663
2664 --
2665 put_line('End date current open Schedule Period');
2666 --
2667 update ap_pol_schedule_periods
2668 set end_date = p_period_start_date - 1
2669 where policy_id = p_policy_id
2670 and end_date is null;
2671
2672 --
2673 put_line('Create Schedule Period');
2674 --
2675 l_schedule_period_id := CreateSchedulePeriod(p_policy_id => p_policy_id,
2676 p_period_name => p_period_name,
2677 p_period_start_date => p_period_start_date);
2678 --
2679 put_line('Created Schedule Period Id: '||l_schedule_period_id);
2680 --
2681
2682 else
2683
2684 l_schedule_period_id := p_period_id;
2685 --
2686 put_line('Update Schedule Period: '||l_schedule_period_id);
2687 --
2688
2689 end if; /* 'NEW' = p_period_type */
2690
2691
2692 /* Eliminate seasonality when schedule
2693 - contains only meals or meals and incidental rates
2694 - doesn't contain Accommodations
2695 */
2696 if ('MEALS' = p_expense_category or
2697 ('PER_DIEM' = p_expense_category and not (instrb(l_per_diem_type_code,'A') > 0))
2698 ) then
2699
2700 --
2701 put_line('Eliminating seasonality');
2702 --
2703 l_eliminate_seasonality := true;
2704
2705 end if; /* Schedule contains only meals or meals and incidental rates */
2706
2707
2708 OPEN rates_cur;
2709 loop
2710
2711 FETCH rates_cur INTO rates_rec;
2712 EXIT WHEN rates_cur%NOTFOUND;
2713
2714 g_num_recs_processed := g_num_recs_processed + 1;
2715
2716 --
2717 put_line('------------------------------------------------------------');
2718 --
2719 l_location_id := GetCityLocation(p_city_locality => rates_rec.city_locality,
2720 p_county => rates_rec.county,
2721 p_state_province => rates_rec.state_province,
2722 p_country => rates_rec.country);
2723
2724 l_location_status := get_location_status(p_location_id => l_location_id);
2725
2726 --
2727 put_line('Location Id : '||l_location_id);
2728 put_line('Location Status : '||l_location_status);
2729
2730 put_line('City Locality : '||rates_rec.city_locality);
2731 put_line('County : '||rates_rec.county);
2732 put_line('State Province : '||rates_rec.state_province);
2733 put_line('Country : '||rates_rec.country);
2734 put_line('Start of Season : '||rates_rec.start_of_season);
2735 put_line('End of Season : '||rates_rec.end_of_season);
2736 put_line('Max Lodging Amt : '||rates_rec.max_lodging_amt);
2737 put_line('No Govt Meals Amt : '||rates_rec.no_govt_meals_amt);
2738 put_line('Prop Meals Amt : '||rates_rec.prop_meals_amt);
2739 put_line('Off Base Inc Amt : '||rates_rec.off_base_inc_amt);
2740 put_line('Footnote Amt : '||rates_rec.footnote_amt);
2741 put_line('Footnote Rate Amt : '||rates_rec.footnote_rate_amt);
2742 put_line('Max Per Diem Amt : '||rates_rec.max_per_diem_amt);
2743 put_line('Effective Date : '||rates_rec.effective_date);
2744 --
2745
2746
2747 if (nvl(rates_rec.max_lodging_amt, 0) = 0 and
2748 nvl(rates_rec.no_govt_meals_amt, 0) = 0 and
2749 nvl(rates_rec.prop_meals_amt, 0) = 0 and
2750 nvl(rates_rec.max_per_diem_amt, 0) = 0) then
2751
2752 put_line('Note: this is a zero rate location');
2753 AddToZeroRates(rates_rec.city_locality, rates_rec.county, rates_rec.state_province, rates_rec.country);
2754
2755 end if;
2756
2757 if (l_location_id is not null and l_location_status <> 'INVALID') then
2758
2759 if (p_period_start_date <= to_date(rates_rec.effective_date, 'MM/DD/RRRR')) then
2760
2761 --
2762 put_line('Leaving Effective Start Date as is');
2763 --
2764 null;
2765
2766 elsif (p_period_start_date > to_date(rates_rec.effective_date, 'MM/DD/RRRR')) then
2767
2768 --
2769 put_line('Setting Effective Start Date to Period Start Date');
2770 --
2771 rates_rec.effective_date := to_char(p_period_start_date, 'MM/DD/RRRR');
2772
2773 end if; /* */
2774
2775 if (last_location_id = l_location_id) then
2776
2777 if (last_rates_rec.effective_date <> rates_rec.effective_date) then
2778
2779 --
2780 put_line('Setting Effective End Date to Last Effective Start Date - 1');
2781 --
2782 rates_rec.effective_end_date := to_char(to_date(last_rates_rec.effective_date, 'MM/DD/RRRR') - 1, 'MM/DD/RRRR');
2783
2784 else
2785
2786 --
2787 put_line('Setting Effective End Date to Last Effective End Date');
2788 --
2789 rates_rec.effective_end_date := last_rates_rec.effective_end_date;
2790
2791 end if;
2792
2793 end if; /* (last_location_id = l_location_id) */
2794
2795 l_schedule_option_id := GetScheduleOption(p_policy_id => p_policy_id,
2796 p_location_id => l_location_id);
2797
2798 if (l_schedule_option_id is null) then
2799
2800 --
2801 put_line('Create Schedule Option');
2802 --
2803 l_schedule_option_id := CreateScheduleOption(p_policy_id => p_policy_id,
2804 p_location_id => l_location_id);
2805
2806 else
2807
2808 --
2809 put_line('Schedule Option exists');
2810 --
2811 null;
2812
2813 end if; /* l_schedule_option_id is null */
2814
2815 --
2816 put_line('Schedule Option: '||l_schedule_option_id);
2817 --
2818
2819 l_night_start_of_season := rates_rec.start_of_season;
2820 l_night_end_of_season := rates_rec.end_of_season;
2821 if (l_eliminate_seasonality) then
2822 rates_rec.start_of_season := null;
2823 rates_rec.end_of_season := null;
2824 end if;
2825
2826 l_rate := CalculateRate(p_expense_category => p_expense_category,
2827 p_rate_incl_meals => p_rate_incl_meals,
2828 p_rate_incl_inc => p_rate_incl_inc,
2829 p_rate_incl_acc => p_rate_incl_acc,
2830 p_meals_rate => p_meals_rate,
2831 p_no_govt_meals_amt => rates_rec.no_govt_meals_amt,
2832 p_prop_meals_amt => rates_rec.prop_meals_amt,
2833 p_max_lodging_amt => rates_rec.max_lodging_amt,
2834 p_max_per_diem_amt => rates_rec.max_per_diem_amt);
2835
2836
2837 --
2838 put_line('Process Roles');
2839 --
2840 one_role_processed := false;
2841 OPEN roles_cur;
2842 loop
2843
2844 FETCH roles_cur INTO roles_rec;
2845 if (roles_cur%NOTFOUND) then
2846 if (one_role_processed) then
2847 exit;
2848 else
2849 l_role_id := null;
2850 end if;
2851 else
2852 l_role_id := roles_rec.role_id;
2853 end if;
2854 one_role_processed := true;
2855 --
2856 put_line('Role Id: '||l_role_id);
2857 --
2858
2859
2860 l_policy_line_id := GetPolicyLine(p_policy_id => p_policy_id,
2861 p_schedule_period_id => l_schedule_period_id,
2862 p_role_id => l_role_id,
2863 p_location_id => l_location_id,
2864 p_start_of_season => rates_rec.start_of_season,
2865 p_end_of_season => rates_rec.end_of_season,
2866 p_effective_start_date => to_date(rates_rec.effective_date, 'MM/DD/RRRR'));
2867
2868
2869 if (l_policy_line_id is null) then
2870
2871 --
2872 put_line('Create Standard Rate');
2873 --
2874
2875 l_policy_line_id := CreatePolicyLine(p_policy_id => p_policy_id,
2876 p_schedule_period_id => l_schedule_period_id,
2877 p_role_id => l_role_id,
2878 p_location_id => l_location_id,
2879 p_rate => l_rate,
2880 p_calc_method => p_calc_method,
2881 p_single_deduction => p_single_deduction,
2882 p_breakfast_deduction => p_breakfast_deduction,
2883 p_lunch_deduction => p_lunch_deduction,
2884 p_dinner_deduction => p_dinner_deduction,
2885 p_start_of_season => rates_rec.start_of_season,
2886 p_end_of_season => rates_rec.end_of_season,
2887 p_max_lodging_amt => rates_rec.max_lodging_amt,
2888 p_no_govt_meals_amt => rates_rec.no_govt_meals_amt,
2889 p_prop_meals_amt => rates_rec.prop_meals_amt,
2890 p_off_base_inc_amt => rates_rec.off_base_inc_amt,
2891 p_footnote_amt => rates_rec.footnote_amt,
2892 p_footnote_rate_amt => rates_rec.footnote_rate_amt,
2893 p_max_per_diem_amt => rates_rec.max_per_diem_amt,
2894 p_effective_start_date => to_date(rates_rec.effective_date, 'MM/DD/RRRR'),
2895 p_effective_end_date => to_date(rates_rec.effective_end_date, 'MM/DD/RRRR'),
2896 p_use_free_acc_add => p_use_free_acc_add,
2897 p_use_free_acc_ded => p_use_free_acc_ded);
2898
2899 --
2900 put_line('Created Standard Rate Id: '||l_policy_line_id||' with rate: '||l_rate);
2901 --
2902 g_num_std_rates_created := g_num_std_rates_created + 1;
2903
2904 if (nvl(p_use_free_acc_add, 'N') = 'Y') then
2905
2906 l_night_rate_line_id := CreateNightRateLine(p_policy_id => p_policy_id,
2907 p_schedule_period_id => l_schedule_period_id,
2908 p_role_id => l_role_id,
2909 p_location_id => l_location_id,
2910 p_rate => rates_rec.max_lodging_amt,
2911 p_single_deduction => p_single_deduction,
2912 p_breakfast_deduction => p_breakfast_deduction,
2913 p_lunch_deduction => p_lunch_deduction,
2914 p_dinner_deduction => p_dinner_deduction,
2915 p_start_of_season => l_night_start_of_season,
2916 p_end_of_season => l_night_end_of_season,
2917 p_max_lodging_amt => rates_rec.max_lodging_amt,
2918 p_no_govt_meals_amt => rates_rec.no_govt_meals_amt,
2919 p_prop_meals_amt => rates_rec.prop_meals_amt,
2920 p_off_base_inc_amt => rates_rec.off_base_inc_amt,
2921 p_footnote_amt => rates_rec.footnote_amt,
2922 p_footnote_rate_amt => rates_rec.footnote_rate_amt,
2923 p_max_per_diem_amt => rates_rec.max_per_diem_amt,
2924 p_effective_start_date => to_date(rates_rec.effective_date, 'MM/DD/RRRR'),
2925 p_effective_end_date => to_date(rates_rec.effective_end_date, 'MM/DD/RRRR'));
2926
2927 --
2928 put_line('Created Night Rate Id: '||l_night_rate_line_id||' with rate: '||l_rate);
2929 --
2930 g_num_night_rates_created := g_num_night_rates_created + 1;
2931
2932 end if; /* nvl(p_use_free_acc_add, 'N') = 'Y' */
2933
2934 else
2935
2936 --
2937 put_line('Standard Rate Id exists: '||l_policy_line_id);
2938 --
2939
2940 end if; /* l_policy_line_id is null */
2941
2942 end loop;
2943 CLOSE roles_cur;
2944
2945 last_location_id := l_location_id;
2946 last_rates_rec.no_govt_meals_amt := rates_rec.no_govt_meals_amt;
2947 last_rates_rec.prop_meals_amt := rates_rec.prop_meals_amt;
2948 last_rates_rec.off_base_inc_amt := rates_rec.off_base_inc_amt;
2949 last_rates_rec.max_per_diem_amt := rates_rec.max_per_diem_amt;
2950 last_rates_rec.effective_date := rates_rec.effective_date;
2951 last_rates_rec.effective_end_date := rates_rec.effective_end_date;
2952
2953
2954 else
2955
2956 --
2957 put_line('Cannot create Standard Rate for Invalid Location Id: '||l_location_id);
2958 --
2959 AddToInvalidLocs(rates_rec.city_locality, rates_rec.county, rates_rec.state_province, rates_rec.country);
2960
2961 end if; /* l_location_id is not null and l_location_status <> 'INVALID' */
2962
2963 end loop;
2964 CLOSE rates_cur;
2965
2966
2967
2968
2969 if (p_ratetype = 'CONUS') then
2970
2971 --
2972 put_line('------------------------------------------------------------');
2973 --
2974 --
2975 put_line('Checking for Undefined CONUS Standard Rate');
2976 --
2977
2978 --
2979 put_line('Process Roles');
2980 --
2981 one_role_processed := false;
2982 OPEN roles_cur;
2983 loop
2984
2985 FETCH roles_cur INTO roles_rec;
2986 if (roles_cur%NOTFOUND) then
2987 if (one_role_processed) then
2988 exit;
2989 else
2990 l_role_id := null;
2991 end if;
2992 else
2993 l_role_id := roles_rec.role_id;
2994 end if;
2995 one_role_processed := true;
2996 --
2997 put_line('Role Id: '||l_role_id);
2998 --
2999
3000 open undefined_conus_rate_cur;
3001
3002 fetch undefined_conus_rate_cur into undefined_conus_rate_rec;
3003
3004 if (undefined_conus_rate_cur%notfound) then
3005
3006 undefined_conus_rate_rec.rate := null;
3007 if (l_eliminate_seasonality) then
3008 undefined_conus_rate_rec.start_of_season := null;
3009 undefined_conus_rate_rec.end_of_season := null;
3010 end if;
3011
3012 l_rate := undefined_conus_rate_rec.rate;
3013
3014 l_schedule_option_id := GetScheduleOption(p_policy_id => p_policy_id,
3015 p_location_id => l_undefined_conus_location_id);
3016
3017 if (l_schedule_option_id is null) then
3018
3019 --
3020 put_line('Create Undefined CONUS Schedule Option');
3021 --
3022 l_schedule_option_id := CreateScheduleOption(p_policy_id => p_policy_id,
3023 p_location_id => l_undefined_conus_location_id);
3024
3025 else
3026
3027 --
3028 put_line('Undefined CONUS Schedule Option exists');
3029 --
3030 null;
3031
3032 end if; /* l_schedule_option_id is null */
3033
3034 --
3035 put_line('Create Undefined CONUS Standard Rate');
3036 --
3037
3038 l_policy_line_id := CreatePolicyLine(p_policy_id => p_policy_id,
3039 p_schedule_period_id => l_schedule_period_id,
3040 p_role_id => l_role_id,
3041 p_location_id => l_undefined_conus_location_id,
3042 p_rate => l_rate,
3043 p_calc_method => null,
3044 p_single_deduction => null,
3045 p_breakfast_deduction => null,
3046 p_lunch_deduction => null,
3047 p_dinner_deduction => null,
3048 p_start_of_season => undefined_conus_rate_rec.start_of_season,
3049 p_end_of_season => undefined_conus_rate_rec.end_of_season,
3050 p_max_lodging_amt => null,
3051 p_no_govt_meals_amt => null,
3052 p_prop_meals_amt => null,
3053 p_off_base_inc_amt => null,
3054 p_footnote_amt => null,
3055 p_footnote_rate_amt => null,
3056 p_max_per_diem_amt => null,
3057 p_effective_start_date => p_period_start_date,
3058 p_effective_end_date => null,
3059 p_use_free_acc_add => p_use_free_acc_add,
3060 p_use_free_acc_ded => p_use_free_acc_ded);
3061
3062 --
3063 put_line('Created Undefined CONUS Standard Rate Id: '||l_policy_line_id||' with rate: '||l_rate);
3064 --
3065 g_num_std_rates_created := g_num_std_rates_created + 1;
3066
3067 if (nvl(p_use_free_acc_add, 'N') = 'Y') then
3068
3069 l_night_rate_line_id := CreateNightRateLine(p_policy_id => p_policy_id,
3070 p_schedule_period_id => l_schedule_period_id,
3071 p_role_id => l_role_id,
3072 p_location_id => l_undefined_conus_location_id,
3073 p_rate => null,
3074 p_single_deduction => null,
3075 p_breakfast_deduction => null,
3076 p_lunch_deduction => null,
3077 p_dinner_deduction => null,
3078 p_start_of_season => '01/01',
3079 p_end_of_season => '12/31',
3080 p_max_lodging_amt => null,
3081 p_no_govt_meals_amt => null,
3082 p_prop_meals_amt => null,
3083 p_off_base_inc_amt => null,
3084 p_footnote_amt => null,
3085 p_footnote_rate_amt => null,
3086 p_max_per_diem_amt => null,
3087 p_effective_start_date => p_period_start_date,
3088 p_effective_end_date => null);
3089
3090 --
3091 put_line('Created Undefined CONUS Night Rate Id: '||l_night_rate_line_id||' with rate: '||l_rate);
3092 --
3093 g_num_night_rates_created := g_num_night_rates_created + 1;
3094
3095 end if; /* nvl(p_use_free_acc_add, 'N') = 'Y' */
3096
3097 else
3098 l_rate := undefined_conus_rate_rec.rate;
3099 --
3100 put_line('Got Undefined CONUS Standard Rate with rate: '||l_rate);
3101 --
3102 end if; /* undefined_conus_rate_cur%notfound */
3103
3104 close undefined_conus_rate_cur;
3105
3106
3107 end loop;
3108 CLOSE roles_cur;
3109
3110 end if; /* p_ratetype = 'CONUS' */
3111
3112
3113 --
3114 put_line('------------------------------------------------------------');
3115 --
3116 --
3117 put_line('Always check for Undefined Location Standard Rate');
3118 --
3119
3120 --
3121 put_line('Process Roles');
3122 --
3123 one_role_processed := false;
3124 OPEN roles_cur;
3125 loop
3126
3127 FETCH roles_cur INTO roles_rec;
3128 if (roles_cur%NOTFOUND) then
3129 if (one_role_processed) then
3130 exit;
3131 else
3132 l_role_id := null;
3133 end if;
3134 else
3135 l_role_id := roles_rec.role_id;
3136 end if;
3137 one_role_processed := true;
3138 --
3139 put_line('Role Id: '||l_role_id);
3140 --
3141
3142 open undefined_loc_rate_cur;
3143
3144 fetch undefined_loc_rate_cur into undefined_loc_rate_rec;
3145
3146 if (undefined_loc_rate_cur%notfound) then
3147
3148 undefined_loc_rate_rec.rate := null;
3149 if (l_eliminate_seasonality) then
3150 undefined_loc_rate_rec.start_of_season := null;
3151 undefined_loc_rate_rec.end_of_season := null;
3152 end if;
3153
3154 l_rate := undefined_loc_rate_rec.rate;
3155
3156 l_schedule_option_id := GetScheduleOption(p_policy_id => p_policy_id,
3157 p_location_id => l_undefined_location_id);
3158
3159 if (l_schedule_option_id is null) then
3160
3161 --
3162 put_line('Create Undefined Location Schedule Option');
3163 --
3164 l_schedule_option_id := CreateScheduleOption(p_policy_id => p_policy_id,
3165 p_location_id => l_undefined_location_id);
3166
3167 else
3168
3169 --
3170 put_line('Undefined Location Schedule Option exists');
3171 --
3172 null;
3173
3174 end if; /* l_schedule_option_id is null */
3175
3176 --
3177 put_line('Create Undefined Location Standard Rate');
3178 --
3179
3180 l_policy_line_id := CreatePolicyLine(p_policy_id => p_policy_id,
3181 p_schedule_period_id => l_schedule_period_id,
3182 p_role_id => l_role_id,
3183 p_location_id => l_undefined_location_id,
3184 p_rate => l_rate,
3185 p_calc_method => null,
3186 p_single_deduction => null,
3187 p_breakfast_deduction => null,
3188 p_lunch_deduction => null,
3189 p_dinner_deduction => null,
3190 p_start_of_season => undefined_loc_rate_rec.start_of_season,
3191 p_end_of_season => undefined_loc_rate_rec.end_of_season,
3192 p_max_lodging_amt => null,
3193 p_no_govt_meals_amt => null,
3194 p_prop_meals_amt => null,
3195 p_off_base_inc_amt => null,
3196 p_footnote_amt => null,
3197 p_footnote_rate_amt => null,
3198 p_max_per_diem_amt => null,
3199 p_effective_start_date => p_period_start_date,
3200 p_effective_end_date => null,
3201 p_use_free_acc_add => p_use_free_acc_add,
3202 p_use_free_acc_ded => p_use_free_acc_ded);
3203
3204 --
3205 put_line('Created Undefined Location Standard Rate Id: '||l_policy_line_id||' with rate: '||l_rate);
3206 --
3207 g_num_std_rates_created := g_num_std_rates_created + 1;
3208
3209 if (nvl(p_use_free_acc_add, 'N') = 'Y') then
3210
3211 l_night_rate_line_id := CreateNightRateLine(p_policy_id => p_policy_id,
3212 p_schedule_period_id => l_schedule_period_id,
3213 p_role_id => l_role_id,
3214 p_location_id => l_undefined_location_id,
3215 p_rate => null,
3216 p_single_deduction => null,
3217 p_breakfast_deduction => null,
3218 p_lunch_deduction => null,
3219 p_dinner_deduction => null,
3220 p_start_of_season => '01/01',
3221 p_end_of_season => '12/31',
3222 p_max_lodging_amt => null,
3223 p_no_govt_meals_amt => null,
3224 p_prop_meals_amt => null,
3225 p_off_base_inc_amt => null,
3226 p_footnote_amt => null,
3227 p_footnote_rate_amt => null,
3228 p_max_per_diem_amt => null,
3229 p_effective_start_date => p_period_start_date,
3230 p_effective_end_date => null);
3231
3232 --
3233 put_line('Created Undefined Location Night Rate Id: '||l_night_rate_line_id||' with rate: '||l_rate);
3234 --
3235 g_num_night_rates_created := g_num_night_rates_created + 1;
3236
3237 end if; /* nvl(p_use_free_acc_add, 'N') = 'Y' */
3238
3239 else
3240 l_rate := undefined_loc_rate_rec.rate;
3241 --
3242 put_line('Got Undefined Location Standard Rate with rate: '||l_rate);
3243 --
3244 end if; /* undefined_loc_rate_cur%notfound */
3245
3246 close undefined_loc_rate_cur;
3247
3248
3249 end loop;
3250 CLOSE roles_cur;
3251
3252
3253 --
3254 put_line('------------------------------------------------------------');
3255 --
3256 --
3257 put_line('Always check for Gap Standard Rate');
3258 --
3259 --
3260 put_line('Process Roles');
3261 --
3262 one_role_processed := false;
3263 OPEN roles_cur;
3264 loop
3265
3266 FETCH roles_cur INTO roles_rec;
3267 if (roles_cur%NOTFOUND) then
3268 if (one_role_processed) then
3269 exit;
3270 else
3271 l_role_id := null;
3272 end if;
3273 else
3274 l_role_id := roles_rec.role_id;
3275 end if;
3276 one_role_processed := true;
3277 --
3278 put_line('Role Id: '||l_role_id);
3279 --
3280
3281 OPEN rates_gap_cur;
3282 loop
3283
3284 FETCH rates_gap_cur INTO rates_gap_rec;
3285 EXIT WHEN rates_gap_cur%NOTFOUND;
3286
3287 if (l_eliminate_seasonality) then
3288 rates_gap_rec.start_of_season := null;
3289 rates_gap_rec.end_of_season := null;
3290 end if;
3291
3292 --
3293 put_line('Create Gap Standard Rate');
3294 --
3295
3296 l_policy_line_id := CreatePolicyLine(p_policy_id => p_policy_id,
3297 p_schedule_period_id => l_schedule_period_id,
3298 p_role_id => rates_gap_rec.role_id,
3299 p_location_id => rates_gap_rec.location_id,
3300 p_rate => l_rate,
3301 p_calc_method => null,
3302 p_single_deduction => null,
3303 p_breakfast_deduction => null,
3304 p_lunch_deduction => null,
3305 p_dinner_deduction => null,
3306 p_start_of_season => rates_gap_rec.start_of_season,
3307 p_end_of_season => rates_gap_rec.end_of_season,
3308 p_max_lodging_amt => null,
3309 p_no_govt_meals_amt => null,
3310 p_prop_meals_amt => null,
3311 p_off_base_inc_amt => null,
3312 p_footnote_amt => null,
3313 p_footnote_rate_amt => null,
3314 p_max_per_diem_amt => null,
3315 p_effective_start_date => p_period_start_date,
3316 p_effective_end_date => rates_gap_rec.effective_start_date - 1,
3317 p_use_free_acc_add => p_use_free_acc_add,
3318 p_use_free_acc_ded => p_use_free_acc_ded);
3319
3320 --
3321 put_line('Created Gap Standard Rate Id: '||l_policy_line_id||' with rate: '||l_rate);
3322 --
3323 g_num_std_rates_created := g_num_std_rates_created + 1;
3324
3325 if (nvl(p_use_free_acc_add, 'N') = 'Y') then
3326
3327 l_night_rate_line_id := CreateNightRateLine(p_policy_id => p_policy_id,
3328 p_schedule_period_id => l_schedule_period_id,
3329 p_role_id => rates_gap_rec.role_id,
3330 p_location_id => rates_gap_rec.location_id,
3331 p_rate => null,
3332 p_single_deduction => null,
3333 p_breakfast_deduction => null,
3334 p_lunch_deduction => null,
3335 p_dinner_deduction => null,
3336 p_start_of_season => '01/01',
3337 p_end_of_season => '12/31',
3338 p_max_lodging_amt => null,
3339 p_no_govt_meals_amt => null,
3340 p_prop_meals_amt => null,
3341 p_off_base_inc_amt => null,
3342 p_footnote_amt => null,
3343 p_footnote_rate_amt => null,
3344 p_max_per_diem_amt => null,
3345 p_effective_start_date => p_period_start_date,
3346 p_effective_end_date => rates_gap_rec.effective_start_date - 1);
3347
3348 --
3349 put_line('Created Gap Night Rate Id: '||l_night_rate_line_id||' with rate: '||l_rate);
3350 --
3351 g_num_night_rates_created := g_num_night_rates_created + 1;
3352
3353 end if; /* nvl(p_use_free_acc_add, 'N') = 'Y' */
3354
3355 end loop;
3356 CLOSE rates_gap_cur;
3357
3358
3359 end loop;
3360 CLOSE roles_cur;
3361
3362 --
3363 put_line('------------------------------------------------------------');
3364 --
3365 --
3366 put_line('Update End Dates');
3367 --
3368 UpdateEndDates(p_policy_id => p_policy_id,
3369 p_schedule_period_id => l_schedule_period_id);
3370
3371
3372 EXCEPTION
3373 when others then
3374 raise;
3375
3376
3377 END UpdateSchedule;
3378
3379
3380 ------------------------------------------------------------------------
3381 PROCEDURE UploadRates(errbuf OUT NOCOPY VARCHAR2,
3382 retcode OUT NOCOPY NUMBER,
3383 p_ratetype IN VARCHAR2,
3384 p_action IN VARCHAR2,
3385 p_source IN VARCHAR2,
3386 p_datafile IN VARCHAR2,
3387 p_expense_category IN VARCHAR2,
3388 p_policy_id IN NUMBER,
3389 p_policy_name IN VARCHAR2,
3390 p_policy_start_date IN VARCHAR2,
3391 p_period_type IN VARCHAR2,
3392 p_period_id IN NUMBER,
3393 p_period_name IN VARCHAR2,
3394 p_period_start_date IN VARCHAR2,
3395 p_rate_incl_meals IN VARCHAR2,
3396 p_rate_incl_inc IN VARCHAR2,
3397 p_rate_incl_acc IN VARCHAR2,
3398 p_meals_rate IN VARCHAR2,
3399 p_free_meals_ded IN VARCHAR2,
3400 p_use_free_acc_add IN VARCHAR2,
3401 p_use_free_acc_ded IN VARCHAR2,
3402 p_calc_method IN VARCHAR2,
3403 p_single_deduction IN NUMBER,
3404 p_breakfast_deduction IN NUMBER,
3405 p_lunch_deduction IN NUMBER,
3406 p_dinner_deduction IN NUMBER) IS
3407 ------------------------------------------------------------------------
3408
3409 l_debug_info VARCHAR2(200);
3410 l_request_id NUMBER;
3411 l_request_status VARCHAR2(30);
3412 l_policy_start_date DATE;
3413 l_period_start_date DATE;
3414 i NUMBER;
3415
3416 BEGIN
3417
3418 --g_debug_switch := p_debug_switch;
3419 g_debug_switch := 'Y';
3420 g_last_updated_by := to_number(FND_GLOBAL.USER_ID);
3421 g_last_update_login := to_number(FND_GLOBAL.LOGIN_ID);
3422 g_invalid_locs := Invalid_Locs('');
3423 g_zero_rates := Zero_Rates('');
3424
3425
3426 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
3427
3428 IF g_debug_switch = 'Y' THEN
3429 --
3430 put_line('------------------------------------------------------------');
3431 put_line('-- P A R A M E T E R S --');
3432 put_line('------------------------------------------------------------');
3433 --
3434 put_line('Debug = ' || g_debug_switch);
3435 put_line('Last Updated By = ' || g_last_updated_by);
3436 put_line('Last Update Login = ' || g_last_update_login);
3437 put_line('Request Id = ' || l_request_id);
3438 --
3439 put_line('Rate Type = ' || p_ratetype);
3440 put_line('Action = ' || p_action);
3441 put_line('Source = ' || p_source);
3442 put_line('Data File = ' || p_datafile);
3443 --
3444 put_line('Expense Category = ' || p_expense_category);
3445 put_line('Policy Id = ' || p_policy_id);
3446 put_line('Policy Name = ' || p_policy_name);
3447 l_policy_start_date := fnd_date.canonical_to_date(p_policy_start_date);
3448 put_line('Policy Start Date = ' || l_policy_start_date);
3449 put_line('Period Type = ' || p_period_type);
3450 put_line('Period Id = ' || p_period_id);
3451 put_line('Period Name = ' || p_period_name);
3452 if ('CREATE' = p_action) then
3453 l_period_start_date := l_policy_start_date;
3454 else
3455 l_period_start_date := fnd_date.canonical_to_date(p_period_start_date);
3456 end if;
3457 put_line('Period Start Date = ' || l_period_start_date);
3458 --
3459 put_line('Rate Includes Meals = ' || p_rate_incl_meals);
3460 put_line('Rate Includes Incidentals = ' || p_rate_incl_inc);
3461 put_line('Rate Includes Accommodations = ' || p_rate_incl_acc);
3462 put_line('Meals Rate = ' || p_meals_rate);
3463 put_line('Free Meals Deduction = ' || p_free_meals_ded);
3464 put_line('Use Free Accommodations Addition = ' || p_use_free_acc_add);
3465 put_line('Use Free Accommodations Deduction = ' || p_use_free_acc_ded);
3466 put_line('Calculation Method = ' || p_calc_method);
3467 put_line('Single Deduction = ' || p_single_deduction);
3468 put_line('Breakfast Deduction = ' || p_breakfast_deduction);
3469 put_line('Lunch Deduction = ' || p_lunch_deduction);
3470 put_line('Dinner Deduction = ' || p_dinner_deduction);
3471 --
3472 END IF;
3473
3474 put_line('------------------------------------------------------------');
3475 put_line('-- B E G I N --');
3476 put_line('------------------------------------------------------------');
3477 CleanRatesInterface(p_ratetype => p_ratetype);
3478
3479 if ('CONUS' = p_ratetype and
3480 'FILE' = p_source and p_datafile is not null) then
3481 UploadCONUS(errbuf, retcode, p_datafile, l_request_status);
3482 elsif ('OCONUS' = p_ratetype and
3483 'FILE' = p_source and p_datafile is not null) then
3484 UploadOCONUS(errbuf, retcode, p_datafile, l_request_status);
3485 end if;
3486
3487 if ('UPLOAD' = p_action) then
3488
3489 null;
3490
3491 elsif ('CREATE' = p_action) then
3492
3493 if (nvl(retcode, 0) <> 2 and nvl(l_request_status, 'SUCCESS') = 'SUCCESS') then
3494 CreateSchedule(errbuf,
3495 retcode,
3496 p_ratetype,
3497 p_expense_category,
3498 p_policy_name,
3499 l_policy_start_date,
3500 p_period_name,
3501 l_period_start_date,
3502 p_rate_incl_meals,
3503 p_rate_incl_inc,
3504 p_rate_incl_acc,
3505 p_meals_rate,
3506 p_free_meals_ded,
3507 p_use_free_acc_add,
3508 p_use_free_acc_ded,
3509 p_calc_method,
3510 p_single_deduction,
3511 p_breakfast_deduction,
3512 p_lunch_deduction,
3513 p_dinner_deduction);
3514 end if;
3515
3516 elsif ('UPDATE' = p_action) then
3517
3518 if (nvl(retcode, 0) <> 2 and nvl(l_request_status, 'SUCCESS') = 'SUCCESS') then
3519 UpdateSchedule(errbuf,
3520 retcode,
3521 p_ratetype,
3522 p_expense_category,
3523 p_policy_id,
3524 p_period_type,
3525 p_period_id,
3526 p_period_name,
3527 l_period_start_date,
3528 p_rate_incl_meals,
3529 p_rate_incl_inc,
3530 p_rate_incl_acc,
3531 p_meals_rate,
3532 p_free_meals_ded,
3533 p_use_free_acc_add,
3534 p_use_free_acc_ded,
3535 p_calc_method,
3536 p_single_deduction,
3537 p_breakfast_deduction,
3538 p_lunch_deduction,
3539 p_dinner_deduction);
3540
3541 end if;
3542
3543 end if;
3544
3545 CleanRatesInterface(p_ratetype => p_ratetype);
3546 --
3547 put_line('------------------------------------------------------------');
3548 put_line('-- E N D --');
3549 put_line('------------------------------------------------------------');
3550 put_line('------------------------------------------------------------');
3551 put_line('-- U P L O A D S U M M A R Y --');
3552 put_line('------------------------------------------------------------');
3553 put_line('Total number of records in the file = ' || g_num_recs_processed);
3554 put_line('Total number of Locations created = ' || g_num_locs_created );
3555 put_line('Total number of Standard Rates created = ' || g_num_std_rates_created);
3556 put_line('Total number of Standard Rates updated = ' || g_num_std_rates_updated);
3557 put_line('Total number of Night Rates created = ' || g_num_night_rates_created);
3558 --
3559 if (g_num_locs_invalid > 0) then
3560 put_line('------------------------------------------------------------');
3561 put_line('-- I N V A L I D L O C A T I O N S --');
3562 put_line('------------------------------------------------------------');
3563 put_line('Total number of Invalid Locations = ' || g_num_locs_invalid);
3564 for i in 1..g_num_locs_invalid
3565 loop
3566 put_line(g_invalid_locs(i));
3567 end loop;
3568 end if;
3569 --
3570 if (g_num_locs_zero_rates > 0) then
3571 put_line('------------------------------------------------------------');
3572 put_line('-- Z E R O R A T E L O C A T I O N S --');
3573 put_line('------------------------------------------------------------');
3574 put_line('Total number of Zero Rate Locations = ' || g_num_locs_zero_rates);
3575 for i in 1..g_num_locs_zero_rates
3576 loop
3577 put_line(g_zero_rates(i));
3578 end loop;
3579 end if;
3580
3581
3582 EXCEPTION
3583 WHEN OTHERS THEN
3584 put_line(sqlerrm);
3585 rollback;
3586 raise;
3587
3588 END UploadRates;
3589
3590
3591 PROCEDURE UploadCONUS(errbuf out nocopy varchar2,
3592 retcode out nocopy number,
3593 p_datafile in varchar2,
3594 p_request_status out nocopy varchar2) IS
3595
3596
3597 l_request_id number;
3598 l_result boolean;
3599 l_phase varchar2(240);
3600 l_status varchar2(240);
3601 l_dev_phase varchar2(240);
3602 l_dev_status varchar2(240);
3603 l_message varchar2(240);
3604
3605 BEGIN
3606
3607 --
3608 put_line('Validating CONUS format');
3609 --
3610 ValidateCONUS(errbuf, retcode, p_datafile);
3611 --
3612 put_line('errbuf = '||errbuf);
3613 put_line('retcode = '||retcode);
3614 --
3615
3616 if (nvl(retcode, 0) <> 2) then
3617 --
3618 put_line('Submitting request to load CONUS');
3619 --
3620 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
3621 'SQLAP',
3622 'APXCONUS',
3623 '',
3624 '',
3625 false,
3626 p_datafile);
3627 commit;
3628
3629 --
3630 put_line('Request Id to load CONUS: '||l_request_id);
3631 --
3632
3633 if (l_request_id = 0) then
3634 errbuf := fnd_message.get;
3635 retcode := 2;
3636
3637 else
3638
3639 loop
3640
3641 --
3642 put_line('Going to sleep for 30 sec... : '||to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3643 --
3644 dbms_lock.sleep(30.01);
3645 --
3646 put_line('Awake... : '||to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3647 --
3648
3649 l_result := fnd_concurrent.get_request_status(
3650 REQUEST_ID => l_request_id,
3651 PHASE => l_phase,
3652 STATUS => l_status,
3653 DEV_PHASE => l_dev_phase,
3654 DEV_STATUS => l_dev_status,
3655 MESSAGE => l_message);
3656
3657 if (l_result) then
3658
3659 if (l_dev_phase = 'COMPLETE') then
3660
3661 if (l_dev_status in ('NORMAL','WARNING')) then
3662
3663 p_request_status := 'SUCCESS';
3664
3665 else
3666
3667 p_request_status := 'FAILED';
3668
3669 end if; /* l_dev_status = 'NORMAL' or l_dev_status = 'WARNING' */
3670
3671 --
3672 put_line('Load request status: '||p_request_status);
3673 --
3674 exit;
3675
3676 end if; /* l_dev_phase = 'COMPLETE' */
3677
3678 end if; /* result */
3679
3680 end loop;
3681
3682 /*
3683 else
3684 put_line('Wait for Request to load CONUS: '||l_request_id);
3685 if (FND_CONCURRENT.WAIT_FOR_REQUEST(
3686 request_id => l_request_id,
3687 interval => 30,
3688 max_wait => 144000,
3689 phase => l_phase,
3690 status => l_status,
3691 dev_phase => l_dev_phase,
3692 dev_status => l_dev_status,
3693 message => l_message)) then
3694 null;
3695 end if;
3696
3697 put_line('l_phase: '||l_phase);
3698 put_line('l_status: '||l_status);
3699 put_line('l_dev_phase: '||l_dev_phase);
3700 put_line('l_dev_status: '||l_dev_status);
3701 put_line('l_message: '||l_message);
3702 */
3703
3704 end if; /* l_request_id = 0 */
3705
3706 end if; /* nvl(retcode, 0) <> 2 */
3707
3708 EXCEPTION
3709 WHEN OTHERS THEN
3710 errbuf := fnd_message.get;
3711 retcode := 2;
3712 END UploadCONUS;
3713
3714
3715 PROCEDURE UploadOCONUS(errbuf out nocopy varchar2,
3716 retcode out nocopy number,
3717 p_datafile in varchar2,
3718 p_request_status out nocopy varchar2) IS
3719
3720
3721 l_request_id number;
3722 l_result boolean;
3723 l_phase varchar2(240);
3724 l_status varchar2(240);
3725 l_dev_phase varchar2(240);
3726 l_dev_status varchar2(240);
3727 l_message varchar2(240);
3728
3729 BEGIN
3730
3731 --
3732 put_line('Validating OCONUS format');
3733 --
3734 ValidateOCONUS(errbuf, retcode, p_datafile);
3735 --
3736 put_line('errbuf = '||errbuf);
3737 put_line('retcode = '||retcode);
3738
3739 if (nvl(retcode, 0) <> 2) then
3740 --
3741 put_line('Submitting request to load OCONUS');
3742 --
3743 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
3744 'SQLAP',
3745 'APXOCONUS',
3746 '',
3747 '',
3748 false,
3749 p_datafile);
3750 commit;
3751
3752 --
3753 put_line('Request Id to load OCONUS: '||l_request_id);
3754 --
3755
3756 if (l_request_id = 0) then
3757 errbuf := fnd_message.get;
3758 retcode := 2;
3759
3760 else
3761
3762 loop
3763
3764 --
3765 put_line('Going to sleep for 30 sec... : '||to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3766 --
3767 dbms_lock.sleep(30.01);
3768 --
3769 put_line('Awake... : '||to_char(sysdate, 'MM/DD/YYYY HH:MI:SS'));
3770 --
3771
3772 l_result := fnd_concurrent.get_request_status(
3773 REQUEST_ID => l_request_id,
3774 PHASE => l_phase,
3775 STATUS => l_status,
3776 DEV_PHASE => l_dev_phase,
3777 DEV_STATUS => l_dev_status,
3778 MESSAGE => l_message);
3779
3780 if (l_result) then
3781
3782 if (l_dev_phase = 'COMPLETE') then
3783
3784 if (l_dev_status in ('NORMAL','WARNING')) then
3785
3786 p_request_status := 'SUCCESS';
3787
3788 else
3789
3790 p_request_status := 'FAILED';
3791
3792 end if; /* l_dev_status = 'NORMAL' or l_dev_status = 'WARNING' */
3793
3794 --
3795 put_line('Load request status: '||p_request_status);
3796 --
3797 exit;
3798
3799 end if; /* l_dev_phase = 'COMPLETE' */
3800
3801 end if; /* result */
3802
3803 end loop;
3804
3805 /*
3806 else
3807 put_line('Wait for Request to load OCONUS: '||l_request_id);
3808 if (FND_CONCURRENT.WAIT_FOR_REQUEST(
3809 request_id => l_request_id,
3810 interval => 30,
3811 max_wait => 144000,
3812 phase => l_phase,
3813 status => l_status,
3814 dev_phase => l_dev_phase,
3815 dev_status => l_dev_status,
3816 message => l_message)) then
3817 null;
3818 end if;
3819
3820 put_line('l_phase: '||l_phase);
3821 put_line('l_status: '||l_status);
3822 put_line('l_dev_phase: '||l_dev_phase);
3823 put_line('l_dev_status: '||l_dev_status);
3824 put_line('l_message: '||l_message);
3825 */
3826
3827 end if; /* l_request_id = 0 */
3828
3829 end if; /* nvl(retcode, 0) <> 2 */
3830
3831 EXCEPTION
3832 WHEN OTHERS THEN
3833 errbuf := fnd_message.get;
3834 retcode := 2;
3835 END UploadOCONUS;
3836
3837
3838 PROCEDURE ValidateCONUS(errbuf out nocopy varchar2,
3839 retcode out nocopy number,
3840 p_datafile in varchar2) IS
3841
3842
3843
3844 BEGIN
3845
3846 ValidateFileFormat(errbuf, retcode, 'CONUS', p_datafile);
3847
3848 END ValidateCONUS;
3849
3850
3851 PROCEDURE ValidateOCONUS(errbuf out nocopy varchar2,
3852 retcode out nocopy number,
3853 p_datafile in varchar2) IS
3854
3855
3856
3857 BEGIN
3858
3859 ValidateFileFormat(errbuf, retcode, 'OCONUS', p_datafile);
3860
3861 END ValidateOCONUS;
3862
3863
3864
3865 PROCEDURE ValidateFileFormat(errbuf out nocopy varchar2,
3866 retcode out nocopy number,
3867 p_ratetype in varchar2,
3868 p_datafile in varchar2) IS
3869 /*
3870 PROCEDURE ValidateFileFormat(p_ratetype in varchar2,
3871 p_datafile in varchar2) IS
3872 */
3873
3874
3875 l_datafilepath varchar2(240);
3876 l_datafile varchar2(240);
3877 l_datafileptr utl_file.file_type;
3878
3879 l_ntdir number;
3880 l_unixdir number;
3881
3882 l_line varchar2(1000);
3883 l_numrecs number;
3884
3885 l_end_delim number;
3886 l_after_delim number;
3887
3888 l_invalid_format exception;
3889 l_invalid_rate_type exception;
3890
3891 BEGIN
3892
3893 --
3894 -- Parse the datafile for the path and filename
3895 --
3896 put_line('Parsing Data File: '|| p_datafile);
3897 --
3898 l_ntdir := instrb(p_datafile, '\', -1);
3899 l_unixdir := instrb(p_datafile, '/', -1);
3900 if (l_ntdir > 0) then
3901 l_datafilepath := substrb(p_datafile, 0, l_ntdir-1);
3902 l_datafile := substrb(p_datafile, l_ntdir+1);
3903 elsif (l_unixdir > 0) then
3904 l_datafilepath := substrb(p_datafile, 0, l_unixdir-1);
3905 l_datafile := substrb(p_datafile, l_unixdir+1);
3906 else
3907 l_datafilepath := '';
3908 l_datafile := p_datafile;
3909 end if;
3910 --
3911 put_line('NT Directory position: '|| to_char(l_ntdir));
3912 put_line('Unix Directory position: '|| to_char(l_unixdir));
3913 put_line('Data File Path: '|| l_datafilepath);
3914 put_line('Data File Name: '|| l_datafile);
3915 put_line('Rate Type: '|| p_ratetype);
3916 --
3917
3918 --
3919 -- Open the datafile for read
3920 --
3921 put_line('Opening Data File: '|| p_datafile);
3922 --
3923 l_datafileptr := utl_file.fopen(l_datafilepath, l_datafile, 'r');
3924
3925 l_numrecs := 0;
3926
3927 if (p_ratetype = 'CONUS') then
3928 --
3929 -- Check CONUS file format has 10 fields
3930 --
3931 loop
3932 begin
3933 utl_file.get_line(l_datafileptr, l_line);
3934 l_numrecs := l_numrecs + 1;
3935 l_end_delim := instrb(l_line, ';', 1, 10);
3936 l_after_delim := instrb(l_line, ';', 1, 11);
3937 if (l_end_delim <> 0 and
3938 l_after_delim = 0) then
3939 null;
3940 else
3941 raise l_invalid_format;
3942 end if;
3943 exception
3944 when no_data_found then
3945 exit;
3946 when others then
3947 raise l_invalid_format;
3948 end;
3949 end loop;
3950 elsif (p_ratetype = 'OCONUS') then
3951 --
3952 -- Check OCONUS file format has 12 fields
3953 --
3954 loop
3955 begin
3956 utl_file.get_line(l_datafileptr, l_line);
3957 l_numrecs := l_numrecs + 1;
3958 l_end_delim := instrb(l_line, ';', 1, 12);
3959 l_after_delim := instrb(l_line, ';', 1, 13);
3960 if (l_end_delim <> 0 and
3961 l_after_delim = 0) then
3962 null;
3963 else
3964 raise l_invalid_format;
3965 end if;
3966 exception
3967 when no_data_found then
3968 exit;
3969 when others then
3970 raise l_invalid_format;
3971 end;
3972 end loop;
3973 else
3974 --
3975 -- Invalid rate type
3976 --
3977 raise l_invalid_rate_type;
3978 end if;
3979 --
3980 put_line('Total number of valid '||p_ratetype||' records: '|| to_char(l_numrecs));
3981 --
3982
3983 --
3984 -- Close the datafile
3985 --
3986 put_line('Closing Data File: '|| p_datafile);
3987 --
3988 utl_file.fclose(l_datafileptr);
3989
3990 EXCEPTION
3991 WHEN l_invalid_format THEN
3992 --utl_file.fclose_all;
3993 utl_file.fclose(l_datafileptr);
3994 fnd_message.set_name('SQLAP', 'OIE_APWUPDM_INVALID_FORMAT');
3995 errbuf := fnd_message.get;
3996 retcode := 2;
3997 WHEN l_invalid_rate_type THEN
3998 --utl_file.fclose_all;
3999 utl_file.fclose(l_datafileptr);
4000 fnd_message.set_name('SQLAP', 'OIE_APWUPDM_INVALID_RATE_TYPE');
4001 errbuf := fnd_message.get;
4002 retcode := 2;
4003 WHEN OTHERS THEN
4004 --utl_file.fclose_all;
4005 utl_file.fclose(l_datafileptr);
4006 fnd_message.set_name('AK', 'AK_INVALID_FILE_OPERATION');
4007 fnd_message.set_token('PATH', l_datafilepath);
4008 fnd_message.set_token('FILE', l_datafile);
4009 errbuf := fnd_message.get;
4010 retcode := 2;
4011
4012 END ValidateFileFormat;
4013
4014
4015 END AP_WEB_UPLOAD_PDM_PKG;