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