1 PACKAGE BODY AP_WEB_POLICY_UTILS AS
2 /* $Header: apwpolub.pls 120.62.12010000.4 2008/11/11 12:40:57 sodash ship $ */
3
4 -- Max Length for Policy Schedule Name and Policy Schedule Period Name
5 C_PolicyNameMaxLength CONSTANT NUMBER := 60;
6
7 TYPE hr_assignment_rec IS RECORD (person_id per_employees_x.employee_id%type,
8 eff_start_date per_all_assignments_f.effective_start_date%type,
9 eff_end_date per_all_assignments_f.effective_end_date%type,
10 grade_id per_all_assignments_f.grade_id%type,
11 job_id per_all_assignments_f.job_id%type,
12 position_id per_all_assignments_f.position_id%type);
13
14 TYPE hr_assignment_cache_type IS TABLE OF hr_assignment_rec;
15 hr_assignment_cache hr_assignment_cache_type;
16
17
18 /*========================================================================
19 | PRIVATE FUNCTION getHrAssignmentFromDB
20 |
21 | DESCRIPTION
22 | Helper function to retrieve HR assignment info from HR tables.
23 |
24 | PARAMETERS
25 | p_person_id -- Expense Type ID associated to the expense
26 | p_date -- Assignment date.
27 | RETURNS
28 | hr_assignment_rec -- A record of the employee's assignment
29 | -- as of the given date.
30 |
31 | MODIFICATION HISTORY
32 | Date Author Description of Changes
33 | 21-Feb-2006 albowicz Created
34 |
35 *=======================================================================*/
36 FUNCTION getHrAssignmentFromDB(p_person_id IN per_employees_x.employee_id%type,
37 p_date IN DATE) RETURN hr_assignment_rec IS
38 ret_val hr_assignment_rec;
39 BEGIN
40 SELECT * INTO ret_val FROM (
41 SELECT p_person_id, effective_start_date, effective_end_date, grade_id, job_id, position_id
42 FROM per_all_assignments_f ass,
43 per_employees_x P
44 WHERE P.EMPLOYEE_ID = p_person_id
45 AND ass.person_id = P.employee_id
46 AND NOT AP_WEB_DB_HR_INT_PKG.isPersonCwk(P.employee_id)='Y'
47 AND p_date >= effective_start_date and p_date <= effective_end_date
48 AND ass.assignment_type = 'E'
49 UNION ALL
50 SELECT p_person_id, effective_start_date, effective_end_date, grade_id, job_id, position_id
51 FROM per_all_assignments_f ass,
52 per_cont_workers_current_x P
53 WHERE ass.assignment_id = P.assignment_id
54 AND P.PERSON_ID = p_person_id
55 AND p_date >= effective_start_date and p_date <= effective_end_date
56 AND ass.assignment_type = 'C')
57 WHERE ROWNUM = 1;
58
59 RETURN ret_val;
60 END getHrAssignmentFromDB;
61
62
63 /*========================================================================
64 | PRIVATE FUNCTION getHrAssignment
65 |
66 | DESCRIPTION
67 | Helper function to retrieve HR assignment.
68 |
69 | PARAMETERS
70 | p_person_id -- Expense Type ID associated to the expense
71 | p_date -- Assignment date.
72 | RETURNS
73 | hr_assignment_rec -- A record of the employee's assignment
74 | -- as of the given date.
75 |
76 | MODIFICATION HISTORY
77 | Date Author Description of Changes
78 | 21-Feb-2006 albowicz Created
79 |
80 *=======================================================================*/
81 FUNCTION getHrAssignment(p_person_id IN per_employees_x.employee_id%type,
82 p_date IN DATE) RETURN hr_assignment_rec IS
83 l_ret_val hr_assignment_rec;
84 l_temp hr_assignment_rec;
85 l_index INTEGER;
86 BEGIN
87
88 -- First look for the assignment in the session specific cache
89 IF(hr_assignment_cache IS NOT NULL) THEN
90 FOR i IN hr_assignment_cache.FIRST..hr_assignment_cache.LAST LOOP
91 l_temp := hr_assignment_cache(i);
92 IF(l_temp.person_id = p_person_id AND p_date between l_temp.eff_start_date and l_temp.eff_end_date) THEN
93 l_ret_val := l_temp;
94 exit;
95 END IF;
96 END LOOP;
97 END IF;
98
99 -- If not found, the lookup from HR tables.
100 IF(l_ret_val.person_id IS NULL) THEN
101 l_ret_val := getHrAssignmentFromDB(p_person_id,p_date);
102
103 IF(l_ret_val.person_id IS NOT NULL) THEN
104 -- Create session specific cache if necessary.
105 IF(hr_assignment_cache IS NULL) THEN
106 hr_assignment_cache := hr_assignment_cache_type(l_ret_val);
107 ELSE
108 l_index := hr_assignment_cache.LAST +1;
109 hr_assignment_cache.EXTEND;
110 hr_assignment_cache(l_index) := l_ret_val;
111 END IF;
112 END IF;
113 END IF;
114
115 RETURN l_ret_val;
116 END getHrAssignment;
117
118
119 /*========================================================================
120 | PUBLIC PROCEDURE getHrAssignment
121 |
122 | DESCRIPTION
123 | Public helper procedure to retrieve HR assignment.
124 |
125 | PARAMETERS
126 | p_person_id -- Expense Type ID associated to the expense
127 | p_date -- Assignment date.
128 | p_grade_id -- Returns grade id.
129 | p_position_id -- Returns position id.
130 | p_job_id -- Returns job id.
131 |
132 | MODIFICATION HISTORY
133 | Date Author Description of Changes
134 | 22-Feb-2006 albowicz Created
135 |
136 *=======================================================================*/
137
138 PROCEDURE getHrAssignment(p_person_id IN per_employees_x.employee_id%type,
139 p_date IN DATE,
140 p_grade_id OUT NOCOPY per_all_assignments_f.grade_id%type,
141 p_position_id OUT NOCOPY per_all_assignments_f.position_id%type,
142 p_job_id OUT NOCOPY per_all_assignments_f.job_id%type) IS
143
144 l_hr_assignment hr_assignment_rec;
145 BEGIN
146
147 l_hr_assignment := getHrAssignment(p_person_id, p_date);
148
149 IF (l_hr_assignment.person_id IS NOT NULL) THEN
150 p_grade_id := l_hr_assignment.grade_id;
151 p_position_id := l_hr_assignment.position_id;
152 p_job_id := l_hr_assignment.job_id;
153 END IF;
154
155 END getHrAssignment;
156
157
158 -- Delcare the private method upfront so that it can be used
159 -- before the definition.
160
161 PROCEDURE permutatePolicyLines(p_user_id IN NUMBER,
162 p_policy_id IN ap_pol_headers.policy_id%TYPE,
163 p_rate_type IN ap_pol_schedule_options.rate_type_code%TYPE);
164
165 PROCEDURE permutateAddonRates( p_user_id IN NUMBER,
166 p_policy_id IN ap_pol_headers.policy_id%TYPE,
167 p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE );
168
169 PROCEDURE permutateNightRates( p_user_id IN NUMBER,
170 p_policy_id IN ap_pol_headers.policy_id%TYPE,
171 p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE );
172
173 PROCEDURE permutateConusLines( p_user_id IN NUMBER,
174 p_policy_id IN ap_pol_headers.policy_id%TYPE);
175
176 FUNCTION get_hash_value(p_component1 IN VARCHAR2,
177 p_component2 IN VARCHAR2,
178 p_component3 IN VARCHAR2) RETURN NUMBER;
179
180 -- ------------------------ END PRIVATE METHOD DECLARATION ---------------------
181
182 /*========================================================================
183 | PUBLIC FUNCTION get_schedule_status
184 |
185 | DESCRIPTION
186 | This function fetches the status for a given schedule id.
187 |
188 | PSEUDO CODE/LOGIC
189 |
190 | PARAMETERS
191 | p_policy_id IN policy id
192 |
193 | MODIFICATION HISTORY
194 | Date Author Description of Changes
195 | 26-Sep-2002 V Nama Created w.r.t. bug 2480382
196 | 04-Feb-2004 V Nama Changed w.r.t. bug 2480382
197 | accounts new lines after activation
198 |
199 *=======================================================================*/
200 FUNCTION get_schedule_status(p_policy_id IN NUMBER) RETURN VARCHAR2 IS
201
202 l_meaning fnd_lookups.meaning%TYPE := '';
203 l_lookup_code fnd_lookups.lookup_code%TYPE;
204 l_sch_end_date ap_pol_headers.end_date%TYPE;
205 l_no__saved_or_duplicated NUMBER;
206 l_no__active_or_inactive NUMBER;
207 l_no__need_activation NUMBER;
208
209 BEGIN
210
211 IF p_policy_id IS NOT NULL THEN
212
213 SELECT end_date
214 INTO l_sch_end_date
215 FROM ap_pol_headers
216 WHERE policy_id = p_policy_id;
217
218
219 IF ( l_sch_end_date IS NOT NULL ) AND ( l_sch_end_date < sysdate ) THEN
220
221 l_lookup_code := 'INACTIVE';
222
223 ELSE
224
225 --get count of lines for various statuses
226 SELECT count(status)
227 INTO l_no__saved_or_duplicated
228 FROM ap_pol_lines
229 WHERE policy_id = p_policy_id
230 AND ( status = 'SAVED' OR status = 'DUPLICATED' or status ='INVALID' or status = 'NEW' or status = 'VALID');
231
232
233 SELECT count(status)
234 INTO l_no__active_or_inactive
235 FROM ap_pol_lines
236 WHERE policy_id = p_policy_id
237 AND ( status = 'ACTIVE' OR status = 'INACTIVE' );
238
239
240 --if schedule was activated earlier
241 --then atleast one line is active and/or inactive
242 --under such case all saved / duplicated lines require activation
243 --there are two source for lines requiring activation
244 --1) new and never activated lines (will have parent_line_id null)
245 --2) old, activated and edited lines (will have parent_line_id not null)
246 --however the breakup is not relevant for this api
247 IF ( l_no__active_or_inactive > 0 ) THEN
248
249 l_no__need_activation := l_no__saved_or_duplicated;
250
251 ELSE --no lines require activation
252
253 l_no__need_activation := 0;
254
255 END IF;
256
257
258
259 --if schedule was activated earlier and requires activation
260 IF ( l_no__need_activation > 0 ) THEN
261
262 l_lookup_code := 'PARTIALLY_ACTIVE';
263
264 --if schedule was activated earlier and does NOT require activation
265 ELSIF ( l_no__active_or_inactive > 0 ) THEN
266
267 l_lookup_code := 'FULLY_ACTIVE';
268
269 --default schedule was never activated
270 ELSE
271
272 l_lookup_code := 'SAVED';
273
274 END IF;
275
276
277 END IF;
278
279 l_meaning := get_lookup_meaning('OIE_POLICY_SCHEDULE_STATUS',l_lookup_code);
280
281 END IF;
282
283 return(l_meaning);
284
285 EXCEPTION
286 WHEN no_data_found THEN
287 return(null);
288 WHEN OTHERS THEN
289 raise;
290 END get_schedule_status;
291
292 /*========================================================================
293 | PUBLIC FUNCTION get_lookup_meaning
294 |
295 | DESCRIPTION
296 | This function fetches the meaning for a given lookup type and code
297 | combination. The values are cached, so the SQL is executed only
298 | once for the session.
299 |
300 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
301 | BC4J objects
302 |
303 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
304 | DBMS_UTILITY.get_hash_value
305 |
306 | PARAMETERS
307 | p_lookup_type IN lookup type
308 | p_lookup_code IN Lookup code, which is part of the lookup
309 | type in previous parameter
310 |
311 | MODIFICATION HISTORY
312 | Date Author Description of Changes
313 | 08-May-2002 J Rautiainen Created
314 |
315 *=======================================================================*/
316 FUNCTION get_lookup_meaning(p_lookup_type IN VARCHAR2,
317 p_lookup_code IN VARCHAR2) RETURN VARCHAR2 IS
318 l_meaning fnd_lookups.meaning%TYPE;
319 l_hash_value NUMBER;
320 BEGIN
321
322 IF p_lookup_code IS NOT NULL AND
323 p_lookup_type IS NOT NULL THEN
324
325 l_hash_value := DBMS_UTILITY.get_hash_value(p_lookup_type||'@*?'||p_lookup_code,
326 1000,
327 25000);
328
329 IF pg_lookups_rec.EXISTS(l_hash_value) THEN
330 l_meaning := pg_lookups_rec(l_hash_value);
331 ELSE
332
333 SELECT meaning
334 INTO l_meaning
335 FROM fnd_lookup_values_vl
336 WHERE lookup_type = p_lookup_type
337 AND lookup_code = p_lookup_code ;
338
339 pg_lookups_rec(l_hash_value) := l_meaning;
340
341 END IF;
342 END IF;
343
344 return(l_meaning);
345
346 EXCEPTION
347 WHEN no_data_found THEN
348 return(null);
349 WHEN OTHERS THEN
350 raise;
351 END get_lookup_meaning;
352
353 /*========================================================================
354 | PUBLIC FUNCTION get_lookup_description
355 |
356 | DESCRIPTION
357 | This function fetches the instruction
358 | for a given lookup type and code combination.
359 | The values are cached, so the SQL is executed only
360 | once for the session.
361 |
362 | PSEUDO CODE/LOGIC
363 |
364 | PARAMETERS
365 | p_lookup_type IN lookup type
366 | p_lookup_code IN Lookup code, which is part of the lookup
367 | type in previous parameter
368 |
369 | MODIFICATION HISTORY
370 | Date Author Description of Changes
371 | 27-Oct-2003 R Langi Created
372 |
373 *=======================================================================*/
374 FUNCTION get_lookup_description(p_lookup_type IN VARCHAR2,
375 p_lookup_code IN VARCHAR2) RETURN VARCHAR2 IS
376 l_description fnd_lookups.description%TYPE;
377 BEGIN
378
379 IF p_lookup_code IS NOT NULL AND
380 p_lookup_type IS NOT NULL THEN
381
382 SELECT description
383 INTO l_description
384 FROM fnd_lookup_values_vl
385 WHERE lookup_type = p_lookup_type
386 AND lookup_code = p_lookup_code ;
387
388 END IF;
389
390 return(l_description);
391
392 EXCEPTION
393 WHEN no_data_found THEN
394 return(null);
395 WHEN OTHERS THEN
396 raise;
397 END get_lookup_description;
398
399
400 /*========================================================================
401 | PUBLIC FUNCTION get_high_threshold
402 |
403 | DESCRIPTION
404 | This function return high threshold for a given low value.
405 |
406 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
407 |
408 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
409 |
410 | PARAMETERS
411 | p_lookup_type IN lookup type
412 |
413 | MODIFICATION HISTORY
414 | Date Author Description of Changes
415 | 10-May-2002 J Rautiainen Created
416 |
417 *=======================================================================*/
418 FUNCTION get_high_threshold(p_policy_id IN NUMBER,
419 p_lookup_type IN VARCHAR2,
420 p_low_threshold IN NUMBER) RETURN NUMBER IS
421
422 CURSOR threshold_c IS
423 SELECT threshold
424 FROM ap_pol_schedule_options
425 WHERE option_type = p_lookup_type
426 AND policy_id = p_policy_id
427 ORDER BY threshold;
428
429 previous_threshold NUMBER;
430 counter NUMBER := 0;
431 l_hash_value NUMBER;
432 l_hash_value2 NUMBER;
433 l_result NUMBER;
434
435 BEGIN
436
437 IF p_policy_id IS NOT NULL AND
438 p_lookup_type IS NOT NULL AND
439 p_low_threshold IS NOT NULL THEN
440
441 l_hash_value := get_hash_value(to_char(p_policy_id),
442 p_lookup_type,
443 to_char(p_low_threshold));
444
445 IF pg_thresholds_rec.EXISTS(l_hash_value) THEN
446 return pg_thresholds_rec(l_hash_value);
447 ELSE
448 FOR threshold_rec IN threshold_c LOOP
449 IF counter > 0 THEN
450
451 l_hash_value2 := get_hash_value(to_char(p_policy_id),
452 p_lookup_type,
453 to_char(previous_threshold));
454
455 pg_thresholds_rec(l_hash_value2) := threshold_rec.threshold;
456 END IF;
457 counter := counter + 1;
458 previous_threshold := threshold_rec.threshold;
459 END LOOP;
460
461 l_hash_value2 := get_hash_value(to_char(p_policy_id),
462 p_lookup_type,
463 to_char(previous_threshold));
464 pg_thresholds_rec(l_hash_value2) := NULL;
465
466 END IF;
467 END IF;
468
469 IF pg_thresholds_rec.EXISTS(l_hash_value) THEN
470 l_result := pg_thresholds_rec(l_hash_value);
471 return l_result;
472 ELSE
473 return to_number(l_result);
474 END IF;
475
476 EXCEPTION
477 WHEN OTHERS THEN
478 raise;
479 END get_high_threshold;
480
481 FUNCTION get_hash_value(p_component1 IN VARCHAR2,
482 p_component2 IN VARCHAR2,
483 p_component3 IN VARCHAR2) RETURN NUMBER IS
484 BEGIN
485 RETURN DBMS_UTILITY.get_hash_value(p_component1||'@*?'||p_component2||'@*?'||p_component3,
486 1000,
487 25000);
488
489 EXCEPTION
490 WHEN OTHERS THEN
491 raise;
492 END get_hash_value;
493
494 /*========================================================================
495 | PUBLIC FUNCTION get_single_org_context
496 |
497 | DESCRIPTION
498 | This function returns whether user is working in single org context.
499 |
500 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
501 | Called from BC4J on a logic deciding switcher bean behaviour
502 |
503 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
504 |
505 | RETURNS
506 | Y If user is working in single org context
507 | N If user is working in single org context
508 |
509 | PARAMETERS
510 | p_user_id IN User Id
511 |
512 | MODIFICATION HISTORY
513 | Date Author Description of Changes
514 | 13-May-2002 J Rautiainen Created
515 |
516 *=======================================================================*/
517 FUNCTION get_single_org_context(p_user_id IN NUMBER) RETURN VARCHAR2 IS
518
519 l_count NUMBER;
520 BEGIN
521 SELECT count(1)
522 INTO l_count
523 FROM AP_POL_CONTEXT
524 WHERE user_id = p_user_id;
525
526 /*-----------------------------------------------------------------*
527 | The query should never return 0, however if 0 is returned it is |
528 | considered as 'N'. This is because the switcher bean will show |
529 | enterable fields in 'Y' case. If no orgs have been defined to |
530 | the user we show an empty table for the user is not allowed to |
531 | enter context information on the fields page. |
532 *-----------------------------------------------------------------*/
533 IF l_count = 1 THEN
534 RETURN 'Y';
535 ELSE
536 RETURN 'N';
537 END IF;
538
539 END get_single_org_context;
540
541
542 /*========================================================================
543 | PUBLIC PROCEDURE initialize_user_cat_options
544 |
545 | DESCRIPTION
546 | This procedure creates category options user context.
547 |
548 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
549 | Called from BC4J
550 |
551 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
552 |
553 | PARAMETERS
554 | p_user_id IN User Id
555 | p_category_code IN Category Code
556 |
557 | MODIFICATION HISTORY
558 | Date Author Description of Changes
559 | 14-May-2002 J Rautiainen Created
560 |
561 *=======================================================================*/
562 PROCEDURE initialize_user_cat_options(p_user_id IN NUMBER,
563 p_category_code IN VARCHAR2) IS
564
565 CURSOR user_cat_options_c IS
566 SELECT pco.CATEGORY_OPTION_ID,
567 pco.CATEGORY_CODE,
568 pco.ORG_ID,
569 pc.user_id,
570 pc.selected_org_id
571 FROM AP_POL_CAT_OPTIONS_ALL pco,
572 AP_POL_CONTEXT pc
573 WHERE pco.org_id(+) = pc.selected_org_id
574 AND pco.category_code(+) = p_category_code
575 AND pc.user_id = p_user_id;
576
577 BEGIN
578 FOR user_cat_options_rec IN user_cat_options_c LOOP
579 IF user_cat_options_rec.category_option_id is null THEN
580 INSERT INTO ap_pol_cat_options_all
581 (category_option_id,
582 category_code,
583 org_id,
584 distance_uom,
585 distance_field,
586 destination_field,
587 license_plate_field,
588 attendees_field,
589 attendees_number_field,
590 end_date_field,
591 merchant_field,
592 ticket_class_field,
593 ticket_number_field,
594 location_to_field,
595 location_from_field,
596 creation_date,
597 created_by,
598 last_update_login,
599 last_update_date,
600 last_updated_by)
601 VALUES (AP_POL_CAT_OPTIONS_S.nextval,
602 p_category_code,
603 user_cat_options_rec.selected_org_id,
604 DECODE(p_category_code,
605 'MILEAGE','KM',
606 NULL), --distance_uom
607 DECODE(p_category_code,
608 'MILEAGE','TRIP_DISTANCE',
609 NULL), --distance_field,
610 DECODE(p_category_code,
611 'MILEAGE','ENABLED',
612 NULL), --destination_field,
613 DECODE(p_category_code,
614 'MILEAGE','DISABLED',
615 NULL), --license_plate_field,
616 DECODE(p_category_code,
617 'MEALS','ENABLED',
618 NULL), --attendees_field,
619 DECODE(p_category_code,
620 'MEALS','ENABLED',
621 NULL), --attendees_number_field,
622 DECODE(p_category_code,
623 'ACCOMMODATIONS','ENABLED',
624 NULL), --end_date_field,
625 DECODE(p_category_code,
626 'ACCOMMODATIONS','ENABLED',
627 'AIRFARE','ENABLED',
628 'CAR_RENTAL','ENABLED',
629 NULL), --merchant_field,
630 DECODE(p_category_code,
631 'AIRFARE','ENABLED',
632 NULL), --ticket_class_field,
633 DECODE(p_category_code,
634 'AIRFARE','ENABLED',
635 NULL), --ticket_number_field,
636 DECODE(p_category_code,
637 'AIRFARE','ENABLED',
638 NULL), --location_to_field,
639 DECODE(p_category_code,
640 'AIRFARE','ENABLED',
641 NULL), --location_from_field,
642 SYSDATE,
643 p_user_id,
644 NULL,
645 SYSDATE,
646 p_user_id);
647 END IF;
648 END LOOP;
649
650 END initialize_user_cat_options;
651
652 /*========================================================================
653 | PUBLIC FUNCTION location_translation_complete
654 |
655 | DESCRIPTION
656 | This function returns whether all locations have been translated
657 | for a given language.
658 |
659 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
660 | Called from BC4J
661 |
662 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
663 |
664 | RETURNS
665 | Y If location has been translated for the given language
666 | N If location has not been translated for the given language
667 |
668 | PARAMETERS
669 | p_language_code IN Language Code
670 |
671 | MODIFICATION HISTORY
672 | Date Author Description of Changes
673 | 21-May-2002 J Rautiainen Created
674 | 28-Oct-2002 V Nama bug 2632830
675 | If no loc defined return N
676 |
677 *=======================================================================*/
678 FUNCTION location_translation_complete(p_language_code IN VARCHAR2) RETURN VARCHAR2 IS
679
680 CURSOR locations_defined_cur IS
681 SELECT 'Y'
682 FROM dual
683 WHERE exists (select 'x' from ap_pol_locations_b);
684 l_locations_defined VARCHAR2(1);
685
686 CURSOR missing_translations_cur IS
687 SELECT count(1) missing_translation_count
688 FROM ap_pol_locations_tl
689 WHERE language = p_language_code
690 AND language <> source_lang;
691
692 missing_translations_rec missing_translations_cur%ROWTYPE;
693
694 BEGIN
695
696 --vnama bug 2632830: check if no locations have been defined
697 l_locations_defined:='N';
698
699 OPEN locations_defined_cur;
700 FETCH locations_defined_cur INTO l_locations_defined;
701 CLOSE locations_defined_cur;
702
703 IF (l_locations_defined = 'N') THEN
704 RETURN 'N';
705 END IF; --ELSE continue
706 --vnama bug 2632830
707
708
709 OPEN missing_translations_cur;
710 FETCH missing_translations_cur INTO missing_translations_rec;
711 CLOSE missing_translations_cur;
712
713 IF (missing_translations_rec.missing_translation_count = 0) THEN
714 RETURN 'Y';
715 ELSE
716 RETURN 'N';
717 END IF;
718
719 END location_translation_complete;
720
721 /*========================================================================
722 | PUBLIC FUNCTION get_employee_name
723 |
724 | DESCRIPTION
725 | This function returns the employee name for a given user.
726 |
727 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
728 | Called from BC4J, needed for the LineHistoryVO, which cannot have joins
729 | due to connect by clause.
730 |
731 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
732 |
733 | RETURNS
734 | The employee name for the given user. If employee ID is not defined for
735 | the user, then the username is returned.
736 |
737 | PARAMETERS
738 | p_user_id IN User identifier
739 |
740 | MODIFICATION HISTORY
741 | Date Author Description of Changes
742 | 25-May-2002 J Rautiainen Created
743 | 19-Aug-2004 skoukunt 3838623:replace per_workforce_x
744 | with per_people_x
745 |
746 *=======================================================================*/
747 FUNCTION get_employee_name(p_user_id IN NUMBER) RETURN VARCHAR2 IS
748
749 /* 2-Oct-2003 J Rautiainen Contingent project changes
750 * This function is used to fetch the name of a employee, regardless of
751 * the status of the employee.
752 * So in this case we need to use per_workforce_x.
753 */
754 CURSOR user_cur IS
755 select DECODE(per.PERSON_ID,
756 null, usr.USER_NAME,
757 per.full_name) employee_name
758 from fnd_user usr, per_people_x per
759 where usr.user_id = p_user_id
760 and usr.employee_id = per.person_id(+);
761
762 user_rec user_cur%ROWTYPE;
763
764 BEGIN
765 IF p_user_id is null THEN
766 return null;
767 END IF;
768
769 OPEN user_cur;
770 FETCH user_cur INTO user_rec;
771 CLOSE user_cur;
772
773 return user_rec.employee_name;
774
775 END get_employee_name;
776
777 /*========================================================================
778 | PUBLIC FUNCTION get_location
779 |
780 | DESCRIPTION
781 | This function returns location.
782 |
783 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
784 | Called from BC4J, needed for the LineHistoryVO, which cannot have joins
785 | due to connect by clause.
786 |
787 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
788 |
789 | RETURNS
790 | Location
791 |
792 | PARAMETERS
793 | p_location_id IN Location identifier
794 |
795 | MODIFICATION HISTORY
796 | Date Author Description of Changes
797 | 25-May-2002 J Rautiainen Created
798 |
799 *=======================================================================*/
800 FUNCTION get_location(p_location_id IN NUMBER) RETURN VARCHAR2 IS
801
802 CURSOR loc_cur IS
803 select location
804 from ap_pol_locations_vl
805 where location_id = p_location_id;
806
807 loc_rec loc_cur%ROWTYPE;
808
809 BEGIN
810 IF p_location_id is null THEN
811 return null;
812 END IF;
813
814 OPEN loc_cur;
815 FETCH loc_cur INTO loc_rec;
816 CLOSE loc_cur;
817
818 return loc_rec.location;
819
820 END get_location;
821
822 /*========================================================================
823 | PUBLIC FUNCTION get_currency_display
824 |
825 | DESCRIPTION
826 | This function returns currency display.
827 |
828 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
829 | Called from BC4J, needed for
830 | PolicyLinesVO/PolicyLinesAdvancedSearchCriteriaVO/CurrencyScheduleOptionsVO
831 |
832 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
833 |
834 | RETURNS
835 | Currency Name||' - '||Currency Code
836 |
837 | PARAMETERS
838 | p_currency_code IN Currency Code
839 |
840 | MODIFICATION HISTORY
841 | Date Author Description of Changes
842 | 17-June-2002 R Langi Created
843 |
844 *=======================================================================*/
845 FUNCTION get_currency_display(p_currency_code IN VARCHAR2) RETURN VARCHAR2 IS
846
847 CURSOR currency_cur IS
848 select name||' - '||currency_code currency_display
849 from fnd_currencies_vl
850 where currency_code = p_currency_code;
851
852 currency_rec currency_cur%ROWTYPE;
853
854 BEGIN
855 IF p_currency_code is null THEN
856 return null;
857 END IF;
858
859 OPEN currency_cur;
860 FETCH currency_cur INTO currency_rec;
861 CLOSE currency_cur;
862
863 return currency_rec.currency_display;
864
865 END get_currency_display;
866
867 /*========================================================================
868 | PUBLIC FUNCTION get_role
869 |
870 | DESCRIPTION
871 | This function returns role.
872 |
873 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
874 | Called from BC4J, needed for the LineHistoryVO, which cannot have joins
875 | due to connect by clause.
876 |
877 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
878 |
879 | RETURNS
880 | Role
881 |
882 | PARAMETERS
883 | p_policy_line_id IN Policy Line identifier
884 |
885 | MODIFICATION HISTORY
886 | Date Author Description of Changes
887 | 25-May-2002 J Rautiainen Created
888 |
889 *=======================================================================*/
890 FUNCTION get_role(p_policy_line_id IN NUMBER) RETURN VARCHAR2 IS
891
892 CURSOR policy_cur IS
893 select ph.role_code,
894 pl.role_id
895 from ap_pol_headers ph,
896 ap_pol_lines pl
897 where pl.policy_id = ph.policy_id
898 and pl.policy_line_id = p_policy_line_id;
899
900 policy_rec policy_cur%ROWTYPE;
901
902 BEGIN
903 IF p_policy_line_id is null THEN
904 return null;
905 END IF;
906
907 OPEN policy_cur;
908 FETCH policy_cur INTO policy_rec;
909 CLOSE policy_cur;
910
911 return get_role(policy_rec.role_code, policy_rec.role_id);
912
913 END get_role;
914
915 /*========================================================================
916 | PUBLIC FUNCTION get_role_for_so
917 |
918 | DESCRIPTION
919 | This function returns role for a schedule option.
920 |
921 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
922 | Called from BC4J, needed for RoleScheduleOptionsVO/PolicyLinesAdvancedSearchVO
923 |
924 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
925 |
926 | RETURNS
927 | Role
928 |
929 | PARAMETERS
930 | p_policy_schedule_option_id IN Policy Schedule Option identifier
931 |
932 | MODIFICATION HISTORY
933 | Date Author Description of Changes
934 | 17-June-2002 R Langi Created
935 |
936 *=======================================================================*/
937 FUNCTION get_role_for_so(p_policy_schedule_option_id IN NUMBER) RETURN VARCHAR2 IS
938
939 CURSOR policy_cur IS
940 select ph.role_code,
941 pso.role_id
942 from ap_pol_headers ph,
943 ap_pol_schedule_options pso
944 where ph.policy_id = pso.policy_id
945 and pso.schedule_option_id = p_policy_schedule_option_id;
946
947 policy_rec policy_cur%ROWTYPE;
948
949 BEGIN
950 IF p_policy_schedule_option_id is null THEN
951 return null;
952 END IF;
953
954 OPEN policy_cur;
955 FETCH policy_cur INTO policy_rec;
956 CLOSE policy_cur;
957
958 return get_role(policy_rec.role_code, policy_rec.role_id);
959
960 END get_role_for_so;
961
962 /*========================================================================
963 | PUBLIC FUNCTION get_role
964 |
965 | DESCRIPTION
966 | This function returns role.
967 |
968 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
969 | Called from local overloaded get_role function
970 |
971 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
972 |
973 | RETURNS
974 | Role
975 |
976 | PARAMETERS
977 | p_role_code IN Role Code, one of the following: GRADE, JOB_GROUP, POSITION
978 | p_role_id IN Location identifier
979 |
980 | MODIFICATION HISTORY
981 | Date Author Description of Changes
982 | 30-May-2002 J Rautiainen Created
983 |
984 *=======================================================================*/
985 FUNCTION get_role(p_role_code VARCHAR2, p_role_id IN NUMBER) RETURN VARCHAR2 IS
986
987 CURSOR job_cur IS
988 select name,
989 substrb(name,instrb(name,'.',-1)+1) parsed_name
990 from per_jobs
991 where job_id = p_role_id;
992
993 CURSOR grade_cur IS
994 select name,
995 substrb(name,instrb(name,'.',-1)+1) parsed_name
996 from per_grades
997 where grade_id = p_role_id;
998
999 CURSOR position_cur IS
1000 select name,
1001 substrb(name,instrb(name,'.',-1)+1) parsed_name
1002 from hr_all_positions_f
1003 where position_id = p_role_id;
1004
1005 job_rec job_cur%ROWTYPE;
1006 grade_rec grade_cur%ROWTYPE;
1007 position_rec position_cur%ROWTYPE;
1008
1009 BEGIN
1010
1011 IF p_role_id is null or p_role_code is null THEN
1012 return null;
1013 END IF;
1014
1015 IF p_role_id = -1 THEN
1016 return fnd_message.GET_STRING('SQLAP','OIE_ALL_OTHER');
1017 END IF;
1018
1019 IF p_role_code = 'JOB_GROUP' THEN
1020
1021 OPEN job_cur;
1022 FETCH job_cur INTO job_rec;
1023 CLOSE job_cur;
1024
1025 return job_rec.name;
1026
1027 ELSIF p_role_code = 'GRADE' THEN
1028
1029 OPEN grade_cur;
1030 FETCH grade_cur INTO grade_rec;
1031 CLOSE grade_cur;
1032
1033 return grade_rec.name;
1034
1035 ELSIF p_role_code = 'POSITION' THEN
1036
1037 OPEN position_cur;
1038 FETCH position_cur INTO position_rec;
1039 CLOSE position_cur;
1040
1041 return position_rec.name;
1042
1043 ELSE
1044 return null;
1045 END IF;
1046
1047 END get_role;
1048
1049 /*========================================================================
1050 | PUBLIC FUNCTION get_threshold
1051 |
1052 | DESCRIPTION
1053 | This function returns threshold string.
1054 |
1055 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1056 | Called from BC4J, needed for the LineHistoryVO, which cannot have joins
1057 | due to connect by clause.
1058 |
1059 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1060 |
1061 | RETURNS
1062 | Threshold
1063 |
1064 | PARAMETERS
1065 | p_range_low IN Range low threshold
1066 | p_range_high IN Range high threshold
1067 | p_category_code IN Category Code
1068 |
1069 | MODIFICATION HISTORY
1070 | Date Author Description of Changes
1071 | 25-May-2002 J Rautiainen Created
1072 |
1073 *=======================================================================*/
1074 FUNCTION get_threshold(p_range_low IN NUMBER,
1075 p_range_high IN NUMBER,
1076 p_category_code IN VARCHAR2) RETURN VARCHAR2 IS
1077 BEGIN
1078 IF p_range_low is not null
1079 AND p_range_high is not null THEN
1080
1081 /*------------------------------------------------------------+
1082 | Fetch the message "Between RANGE_LOW and RANGE_HIGH" |
1083 +------------------------------------------------------------*/
1084 FND_MESSAGE.SET_NAME ('SQLAP', 'OIE_POL_THRESHOLD_BETWEEN');
1085
1086 /*-------------------------------------------------------+
1087 | Replace tokens with the values passed in as parameter |
1088 +-------------------------------------------------------*/
1089 if (p_category_code = c_MILEAGE and p_range_low <> 0) then
1090 FND_MESSAGE.SET_TOKEN ('RANGE_LOW', p_range_low+.1);
1091 elsif (p_category_code = c_PER_DIEM and p_range_low <> 0) then
1092 -- FND_MESSAGE.SET_TOKEN ('RANGE_LOW', p_range_low+.01);
1093 FND_MESSAGE.SET_TOKEN ('RANGE_LOW',
1094 format_minutes_to_hour_minutes(p_range_low+1));
1095 else
1096 FND_MESSAGE.SET_TOKEN ('RANGE_LOW', p_range_low);
1097 end if;
1098
1099 -- FND_MESSAGE.SET_TOKEN ('RANGE_HIGH' , p_range_high);
1100 if (p_category_code = c_PER_DIEM) then
1101 FND_MESSAGE.SET_TOKEN ('RANGE_HIGH',
1102 format_minutes_to_hour_minutes(p_range_high));
1103 else
1104 FND_MESSAGE.SET_TOKEN ('RANGE_HIGH' , p_range_high);
1105 end if;
1106
1107 return FND_MESSAGE.get;
1108 ELSIF p_range_low is not null THEN
1109
1110 /*--------------------------------------------------+
1111 | Fetch the message "Greater Than RANGE_LOW" |
1112 +--------------------------------------------------*/
1113 FND_MESSAGE.SET_NAME ('SQLAP', 'OIE_POL_THRESHOLD_GREATER');
1114
1115 /*-------------------------------------------------------+
1116 | Replace tokens with the values passed in as parameter |
1117 +-------------------------------------------------------*/
1118 if (p_category_code = c_PER_DIEM) then
1119 FND_MESSAGE.SET_TOKEN ('RANGE_LOW',
1120 format_minutes_to_hour_minutes(p_range_low));
1121 else
1122 FND_MESSAGE.SET_TOKEN ('RANGE_LOW', p_range_low);
1123 end if;
1124 return FND_MESSAGE.get;
1125
1126 ELSE
1127 return null;
1128 END IF;
1129
1130 END get_threshold;
1131
1132 /*========================================================================
1133 | PUBLIC PROCEDURE initialize_user_exrate_options
1134 |
1135 | DESCRIPTION
1136 | This procedure creates exchange rate options user context.
1137 |
1138 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1139 | Called from BC4J
1140 |
1141 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1142 |
1143 | PARAMETERS
1144 | p_user_id IN User Id
1145 |
1146 | MODIFICATION HISTORY
1147 | Date Author Description of Changes
1148 | 30-May-2002 V Nama Created
1149 | 30-Jul-2002 V Nama Altered defaults to null for
1150 | exchange_rate_allowance and
1151 | overall_tolerance
1152 |
1153 *=======================================================================*/
1154 PROCEDURE initialize_user_exrate_options(p_user_id IN NUMBER) IS
1155
1156 CURSOR user_exrate_options_c IS
1157 SELECT ex.EXCHANGE_RATE_ID,
1158 ex.ORG_ID,
1159 pc.user_id,
1160 pc.selected_org_id
1161 FROM AP_POL_EXRATE_OPTIONS_ALL ex,
1162 AP_POL_CONTEXT pc
1163 WHERE ex.org_id(+) = pc.selected_org_id
1164 AND pc.user_id = p_user_id;
1165
1166 BEGIN
1167 FOR user_exrate_options_rec IN user_exrate_options_c LOOP
1168 IF user_exrate_options_rec.exchange_rate_id is null THEN
1169 INSERT INTO ap_pol_exrate_options_all
1170 (exchange_rate_id,
1171 enabled,
1172 default_exchange_rates,
1173 exchange_rate_type,
1174 exchange_rate_allowance,
1175 overall_tolerance,
1176 org_id,
1177 creation_date,
1178 created_by,
1179 last_update_login,
1180 last_update_date,
1181 last_updated_by)
1182 VALUES (AP_POL_EXRATE_OPTIONS_S.nextval,
1183 'N',
1184 'N',
1185 'Corporate',
1186 null,
1187 null,
1188 user_exrate_options_rec.selected_org_id,
1189 SYSDATE,
1190 p_user_id,
1191 NULL,
1192 SYSDATE,
1193 p_user_id);
1194 END IF;
1195 END LOOP;
1196
1197 END initialize_user_exrate_options;
1198
1199 /*========================================================================
1200 | PUBLIC FUNCTION get_context_tab_enabled
1201 |
1202 | DESCRIPTION
1203 | This function returns whether context tab should be shown or hidden.
1204 | Context tab is not showed if:
1205 | - Single org installation
1206 | - Functional security does not allow it
1207 |
1208 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1209 | Called from TabCO.java
1210 |
1211 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1212 |
1213 | RETURNS
1214 | 'Y' If policy tab should be displayed
1215 | 'N' If policy tab should be displayed
1216 |
1217 | PARAMETERS
1218 |
1219 | MODIFICATION HISTORY
1220 | Date Author Description of Changes
1221 | 10-Jun-2002 J Rautiainen Created
1222 |
1223 *=======================================================================*/
1224 FUNCTION get_context_tab_enabled RETURN VARCHAR2 IS
1225
1226 CURSOR multi_org_c IS
1227 SELECT nvl(multi_org_flag, 'N') multi_org_flag
1228 FROM fnd_product_groups;
1229
1230 multi_org_rec multi_org_c%ROWTYPE;
1231
1232 BEGIN
1233 OPEN multi_org_c;
1234 FETCH multi_org_c INTO multi_org_rec;
1235 CLOSE multi_org_c;
1236
1237 IF multi_org_rec.multi_org_flag = 'N' THEN
1238 return 'N';
1239 END IF;
1240
1241 IF not fnd_function.test('OIE_POL_ALLOW_MULTI_ORG_SETUP') THEN
1242 return 'N';
1243 END IF;
1244
1245 return 'Y';
1246
1247 END get_context_tab_enabled;
1248
1249
1250 /*========================================================================
1251 | PUBLIC FUNCTION getHighEndOfThreshold
1252 |
1253 | DESCRIPTION
1254 | This function internally calls the new function which has an additional
1255 | parameter. This function was kept for backward compatibility reasons.
1256 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1257 |
1258 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1259 |
1260 | PARAMETERS
1261 | p_policy_id IN Policy Identifier
1262 | p_threshold IN Threshold
1263 |
1264 | MODIFICATION HISTORY
1265 | Date Author Description of Changes
1266 | 16-May-2002 R Langi Created
1267 |
1268 *=======================================================================*/
1269 FUNCTION getHighEndOfThreshold(p_policy_id IN ap_pol_headers.policy_id%TYPE,
1270 p_threshold IN ap_pol_schedule_options.threshold%TYPE) RETURN ap_pol_schedule_options.threshold%TYPE IS
1271 BEGIN
1272
1273 RETURN getHighEndOfThreshold(p_policy_id, p_threshold, 'STANDARD');
1274
1275 END getHighEndOfThreshold;
1276
1277 /*========================================================================
1278 | PUBLIC FUNCTION getHighEndOfThreshold
1279 |
1280 | DESCRIPTION
1281 |
1282 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1283 |
1284 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1285 |
1286 | PARAMETERS
1287 | p_policy_id IN Policy Identifier
1288 | p_threshold IN Threshold
1289 | p_rate_type IN Rate Type (STANDARD, FIRST, LAST)
1290 |
1291 | MODIFICATION HISTORY
1292 | Date Author Description of Changes
1293 | 01-Nov-2005 krmenon Created
1294 |
1295 *=======================================================================*/
1296 FUNCTION getHighEndOfThreshold(p_policy_id IN ap_pol_headers.policy_id%TYPE,
1297 p_threshold IN ap_pol_schedule_options.threshold%TYPE,
1298 p_rate_type IN ap_pol_schedule_options.rate_type_code%TYPE) RETURN ap_pol_schedule_options.threshold%TYPE IS
1299
1300 l_high_end_of_threshold ap_pol_schedule_options.threshold%TYPE;
1301
1302 CURSOR c_threshold IS
1303 SELECT threshold
1304 FROM ap_pol_schedule_options
1305 WHERE policy_id = p_policy_id
1306 AND threshold is not null
1307 AND nvl(rate_type_code, 'STANDARD') = p_rate_type
1308 ORDER BY threshold;
1309
1310 BEGIN
1311
1312 open c_threshold;
1313 loop
1314 fetch c_threshold into l_high_end_of_threshold;
1315 exit when c_threshold%NOTFOUND;
1316
1317 if l_high_end_of_threshold > p_threshold then
1318 exit;
1319 end if;
1320
1321 end loop;
1322 close c_threshold;
1323
1324 if p_threshold = l_high_end_of_threshold then
1325 l_high_end_of_threshold := null;
1326 end if;
1327
1328 return(l_high_end_of_threshold);
1329
1330 EXCEPTION
1331 WHEN no_data_found THEN
1332 return(null);
1333 WHEN OTHERS THEN
1334 raise;
1335 END getHighEndOfThreshold;
1336
1337
1338 /*========================================================================
1339 | PUBLIC FUNCTION getPolicyCategoryCode
1340 |
1341 | DESCRIPTION
1342 | Returns the Category Code for a Policy
1343 |
1344 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1345 |
1346 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1347 |
1348 | PARAMETERS
1349 | p_policy_id IN Policy Identifier
1350 |
1351 | MODIFICATION HISTORY
1352 | Date Author Description of Changes
1353 | 16-May-2002 R Langi Created
1354 |
1355 *=======================================================================*/
1356 FUNCTION getPolicyCategoryCode(p_policy_id IN ap_pol_headers.policy_id%TYPE) RETURN ap_pol_headers.category_code%TYPE IS
1357
1358 l_category_code ap_pol_headers.category_code%TYPE;
1359
1360 BEGIN
1361
1362 select category_code
1363 into l_category_code
1364 from ap_pol_headers
1365 where policy_id = p_policy_id;
1366
1367 return l_category_code;
1368
1369 EXCEPTION
1370 WHEN no_data_found THEN
1371 return(null);
1372 WHEN OTHERS THEN
1373 raise;
1374 END getPolicyCategoryCode;
1375
1376
1377 /*========================================================================
1378 | PUBLIC FUNCTION checkRuleOption
1379 |
1380 | DESCRIPTION
1381 | Checks to see if a Rule is enabled for a Schedule and an Option defined
1382 |
1383 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1384 |
1385 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1386 | isLocationEnabled
1387 | isRoleEnabled
1388 | isCurrencyEnabled
1389 | isVehicleCategoryEnabled
1390 | isVehicleTypeEnabled
1391 | isFuelTypeEnabled
1392 | isTimeThresholdsEnabled
1393 | isDistanceThresholdsEnabled
1394 |
1395 | PARAMETERS
1396 |
1397 | RETURNS
1398 | 'Y' If a Rule is enabled for a Schedule and an Option defined
1399 | 'N' If a Rule is not enabled for a Schedule or an Option not defined
1400 |
1401 | MODIFICATION HISTORY
1402 | Date Author Description of Changes
1403 | 16-May-2002 R Langi Created
1404 |
1405 *=======================================================================*/
1406 FUNCTION checkRuleOption(p_policy_id IN ap_pol_headers.policy_id%TYPE,
1407 p_rule IN VARCHAR2) RETURN VARCHAR2 IS
1408
1409
1410 FUNCTION isLocationEnabled(p_policy_id IN ap_pol_headers.policy_id%TYPE) RETURN VARCHAR2 IS
1411
1412 l_location_flag ap_pol_headers.location_flag%TYPE;
1413 l_location_count number := 0;
1414
1415 BEGIN
1416 select location_flag
1417 into l_location_flag
1418 from ap_pol_headers
1419 where policy_id = p_policy_id;
1420
1421 select count(location_id)
1422 into l_location_count
1423 from ap_pol_schedule_options
1424 where policy_id = p_policy_id
1425 and option_type = c_LOCATION
1426 and location_id is not null;
1427
1428 if (l_location_flag = 'Y' and l_location_count > 0)
1429 then
1430 return 'Y';
1431 else
1432 return 'N';
1433 end if;
1434
1435 EXCEPTION
1436 WHEN no_data_found THEN
1437 return(null);
1438 WHEN OTHERS THEN
1439 raise;
1440 END isLocationEnabled;
1441
1442 FUNCTION isRoleEnabled(p_policy_id IN ap_pol_headers.policy_id%TYPE) RETURN VARCHAR2 IS
1443
1444 l_employee_role_flag ap_pol_headers.employee_role_flag%TYPE;
1445 l_role_count number := 0;
1446
1447 BEGIN
1448 select employee_role_flag
1449 into l_employee_role_flag
1450 from ap_pol_headers
1451 where policy_id = p_policy_id;
1452
1453 select count(role_id)
1454 into l_role_count
1455 from ap_pol_schedule_options
1456 where policy_id = p_policy_id
1457 and option_type = c_EMPLOYEE_ROLE
1458 and role_id is not null;
1459
1460 if (l_employee_role_flag = 'Y' and l_role_count > 0)
1461 then
1462 return 'Y';
1463 else
1464 return 'N';
1465 end if;
1466
1467 EXCEPTION
1468 WHEN no_data_found THEN
1469 return(null);
1470 WHEN OTHERS THEN
1471 raise;
1472 END isRoleEnabled;
1473
1474 FUNCTION isCurrencyEnabled(p_policy_id IN ap_pol_headers.policy_id%TYPE) RETURN VARCHAR2 IS
1475
1476 l_currency_preference ap_pol_headers.currency_preference%TYPE;
1477 l_currency_count number := 0;
1478
1479 BEGIN
1480 select currency_preference
1481 into l_currency_preference
1482 from ap_pol_headers
1483 where policy_id = p_policy_id;
1484
1485 select count(currency_code)
1486 into l_currency_count
1487 from ap_pol_schedule_options
1488 where policy_id = p_policy_id
1489 and option_type = c_CURRENCY
1490 and currency_code is not null;
1491
1492 if (l_currency_preference = c_MRC and l_currency_count > 0)
1493 then
1494 return 'Y';
1495 else
1496 return 'N';
1497 end if;
1498
1499 EXCEPTION
1500 WHEN no_data_found THEN
1501 return(null);
1502 WHEN OTHERS THEN
1503 raise;
1504 END isCurrencyEnabled;
1505
1506 FUNCTION isVehicleCategoryEnabled(p_policy_id IN ap_pol_headers.policy_id%TYPE) RETURN VARCHAR2 IS
1507
1508 l_vehicle_category_flag ap_pol_headers.vehicle_category_flag%TYPE;
1509 l_vehicle_category_count number := 0;
1510
1511 BEGIN
1512 select vehicle_category_flag
1513 into l_vehicle_category_flag
1514 from ap_pol_headers
1515 where policy_id = p_policy_id;
1516
1517 select count(option_code)
1518 into l_vehicle_category_count
1519 from ap_pol_schedule_options
1520 where policy_id = p_policy_id
1521 and option_type = c_VEHICLE_CATEGORY
1522 and option_code is not null;
1523
1524 if (l_vehicle_category_flag = 'Y' and l_vehicle_category_count > 0)
1525 then
1526 return 'Y';
1527 else
1528 return 'N';
1529 end if;
1530
1531 EXCEPTION
1532 WHEN no_data_found THEN
1533 return(null);
1534 WHEN OTHERS THEN
1535 raise;
1536 END isVehicleCategoryEnabled;
1537
1538 FUNCTION isVehicleTypeEnabled(p_policy_id IN ap_pol_headers.policy_id%TYPE) RETURN VARCHAR2 IS
1539
1540 l_vehicle_type_flag ap_pol_headers.vehicle_type_flag%TYPE;
1541 l_vehicle_type_count number := 0;
1542
1543 BEGIN
1544 select vehicle_type_flag
1545 into l_vehicle_type_flag
1546 from ap_pol_headers
1547 where policy_id = p_policy_id;
1548
1549 select count(option_code)
1550 into l_vehicle_type_count
1551 from ap_pol_schedule_options
1552 where policy_id = p_policy_id
1553 and option_type = c_VEHICLE_TYPE
1554 and option_code is not null;
1555
1556 if (l_vehicle_type_flag = 'Y' and l_vehicle_type_count > 0)
1557 then
1558 return 'Y';
1559 else
1560 return 'N';
1561 end if;
1562
1563 EXCEPTION
1564 WHEN no_data_found THEN
1565 return(null);
1566 WHEN OTHERS THEN
1567 raise;
1568 END isVehicleTypeEnabled;
1569
1570 FUNCTION isFuelTypeEnabled(p_policy_id IN ap_pol_headers.policy_id%TYPE) RETURN VARCHAR2 IS
1571
1572 l_fuel_type_flag ap_pol_headers.fuel_type_flag%TYPE;
1573 l_fuel_type_count number := 0;
1574
1575 BEGIN
1576 select fuel_type_flag
1577 into l_fuel_type_flag
1578 from ap_pol_headers
1579 where policy_id = p_policy_id;
1580
1581 select count(option_code)
1582 into l_fuel_type_count
1583 from ap_pol_schedule_options
1584 where policy_id = p_policy_id
1585 and option_type = c_FUEL_TYPE
1586 and option_code is not null;
1587
1588 if (l_fuel_type_flag = 'Y' and l_fuel_type_count > 0)
1589 then
1590 return 'Y';
1591 else
1592 return 'N';
1593 end if;
1594
1595 EXCEPTION
1596 WHEN no_data_found THEN
1597 return(null);
1598 WHEN OTHERS THEN
1599 raise;
1600 END isFuelTypeEnabled;
1601
1602 FUNCTION isTimeThresholdsEnabled(p_policy_id IN ap_pol_headers.policy_id%TYPE) RETURN VARCHAR2 IS
1603
1604 l_time_based_entry_flag ap_pol_headers.time_based_entry_flag%TYPE;
1605 l_thresholds_count number := 0;
1606
1607 BEGIN
1608 select nvl(time_based_entry_flag, 'N')
1609 into l_time_based_entry_flag
1610 from ap_pol_headers
1611 where policy_id = p_policy_id;
1612
1613 select count(threshold)
1614 into l_thresholds_count
1615 from ap_pol_schedule_options
1616 where policy_id = p_policy_id
1617 and (option_type = c_TIME_THRESHOLD)
1618 and threshold is not null;
1619
1620 if (l_time_based_entry_flag = 'Y' and l_thresholds_count > 0)
1621 then
1622 return 'Y';
1623 else
1624 return 'N';
1625 end if;
1626
1627 EXCEPTION
1628 WHEN no_data_found THEN
1629 return(null);
1630 WHEN OTHERS THEN
1631 raise;
1632 END isTimeThresholdsEnabled;
1633
1634 FUNCTION isDistanceThresholdsEnabled(p_policy_id IN ap_pol_headers.policy_id%TYPE) RETURN VARCHAR2 IS
1635
1636 l_distance_thresholds_flag ap_pol_headers.distance_thresholds_flag%TYPE;
1637 l_thresholds_count number := 0;
1638
1639 BEGIN
1640 select nvl2(distance_thresholds_flag, 'Y', 'N')
1641 into l_distance_thresholds_flag
1642 from ap_pol_headers
1643 where policy_id = p_policy_id;
1644
1645 select count(threshold)
1646 into l_thresholds_count
1647 from ap_pol_schedule_options
1648 where policy_id = p_policy_id
1649 and (option_type = c_DISTANCE_THRESHOLD)
1650 and threshold is not null;
1651
1652 if (l_distance_thresholds_flag = 'Y' and l_thresholds_count > 0)
1653 then
1654 return 'Y';
1655 else
1656 return 'N';
1657 end if;
1658
1659 EXCEPTION
1660 WHEN no_data_found THEN
1661 return(null);
1662 WHEN OTHERS THEN
1663 raise;
1664 END isDistanceThresholdsEnabled;
1665
1666 FUNCTION isThresholdsEnabled(p_policy_id IN ap_pol_headers.policy_id%TYPE) RETURN VARCHAR2 IS
1667
1668 l_distance_thresholds_flag ap_pol_headers.distance_thresholds_flag%TYPE;
1669 l_time_thresholds_flag ap_pol_headers.time_based_entry_flag%TYPE;
1670
1671 BEGIN
1672
1673 l_distance_thresholds_flag := isDistanceThresholdsEnabled(p_policy_id);
1674 l_time_thresholds_flag := isTimeThresholdsEnabled(p_policy_id);
1675
1676 if (l_distance_thresholds_flag IS NULL and l_time_thresholds_flag IS NULL)
1677 then
1678 return (null);
1679 elsif (l_distance_thresholds_flag = 'Y' or l_time_thresholds_flag = 'Y')
1680 then
1681 return 'Y';
1682 else
1683 return 'N';
1684 end if;
1685
1686 EXCEPTION
1687 WHEN OTHERS THEN
1688 raise;
1689 END isThresholdsEnabled;
1690
1691 FUNCTION isAddonRatesEnabled(p_policy_id IN ap_pol_headers.policy_id%TYPE) RETURN VARCHAR2 IS
1692 l_addon_mileage_rates_flag ap_pol_headers.addon_mileage_rates_flag%TYPE;
1693 l_addon_rates_count number := 0;
1694 BEGIN
1695
1696 select nvl(addon_mileage_rates_flag, 'N')
1697 into l_addon_mileage_rates_flag
1698 from ap_pol_headers
1699 where policy_id = p_policy_id;
1700
1701 select count(1)
1702 into l_addon_rates_count
1703 from ap_pol_schedule_options
1704 where policy_id = p_policy_id
1705 and (option_type = c_ADDON_RATES)
1706 and option_code is not null;
1707
1708 if (l_addon_mileage_rates_flag = 'Y' and l_addon_rates_count > 0)
1709 then
1710 return 'Y';
1711 else
1712 return 'N';
1713 end if;
1714
1715 EXCEPTION
1716 WHEN OTHERS THEN
1717 raise;
1718
1719 END isAddonRatesEnabled;
1720
1721 BEGIN
1722
1723 if (p_rule = c_LOCATION) then return isLocationEnabled(p_policy_id); end if;
1724 if (p_rule = c_EMPLOYEE_ROLE) then return isRoleEnabled(p_policy_id); end if;
1725 if (p_rule = c_CURRENCY) then return isCurrencyEnabled(p_policy_id); end if;
1726 if (p_rule = c_VEHICLE_CATEGORY) then return isVehicleCategoryEnabled(p_policy_id); end if;
1727 if (p_rule = c_VEHICLE_TYPE) then return isVehicleTypeEnabled(p_policy_id); end if;
1728 if (p_rule = c_FUEL_TYPE) then return isFuelTypeEnabled(p_policy_id); end if;
1729 if (p_rule = c_TIME_THRESHOLD) then return isTimeThresholdsEnabled(p_policy_id); end if;
1730 if (p_rule = c_DISTANCE_THRESHOLD) then return isDistanceThresholdsEnabled(p_policy_id); end if;
1731 if (p_rule = c_THRESHOLD) then return isThresholdsEnabled(p_policy_id); end if;
1732 if (p_rule = c_ADDON_RATES) then return isAddonRatesEnabled(p_policy_id); end if;
1733
1734 EXCEPTION
1735 WHEN no_data_found THEN
1736 return(null);
1737 WHEN OTHERS THEN
1738 raise;
1739 END checkRuleOption;
1740
1741
1742 /*========================================================================
1743 | PUBLIC FUNCTION getUnionStmtForRuleOption
1744 |
1745 | DESCRIPTION
1746 | If a Rule is not enabled or Schedule Option not defined for an enabled Rule
1747 | this will return a UNION null statement which is used for perumutatePolicyLines()
1748 |
1749 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1750 |
1751 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1752 |
1753 | PARAMETERS
1754 | p_policy_id IN Policy Identifier
1755 | p_rule IN Schedule Option Type
1756 |
1757 | MODIFICATION HISTORY
1758 | Date Author Description of Changes
1759 | 16-May-2002 R Langi Created
1760 |
1761 *=======================================================================*/
1762 FUNCTION getUnionStmtForRuleOption(p_policy_id IN ap_pol_headers.policy_id%TYPE,
1763 p_rule IN VARCHAR2) RETURN VARCHAR2 IS
1764
1765 l_currency_preference ap_pol_headers.currency_preference%TYPE;
1766
1767 l_src_stmt VARCHAR2(160) := 'union all select CURRENCY_CODE from ap_pol_headers where POLICY_ID = :p_policy_id';
1768
1769 l_vc_stmt VARCHAR2(80) := 'union all select to_char(null), to_char(null), to_char(null) from sys.dual';
1770 l_number_stmt VARCHAR2(80) := 'union all select to_number(null) from sys.dual';
1771 l_varchar2_stmt VARCHAR2(80) := 'union all select to_char(null) from sys.dual';
1772
1773 BEGIN
1774
1775 select currency_preference
1776 into l_currency_preference
1777 from ap_pol_headers
1778 where policy_id = p_policy_id;
1779
1780 if (checkRuleOption(p_policy_id, p_rule) = 'Y')
1781 then
1782 return '';
1783 else
1784 if (p_rule = c_CURRENCY) then
1785 if (l_currency_preference = c_SRC) then
1786 /*
1787 if Single Rate Currency there will be no records in ap_pol_schedule_options
1788 we must still permutate using ap_pol_headers.currency_code
1789 */
1790 return l_src_stmt;
1791 else
1792 /*
1793 if Location Currency Rate there will be no records in ap_pol_schedule_options
1794 if Airfare there will be no records in ap_pol_schedule_options
1795 */
1796 return l_varchar2_stmt;
1797 end if;
1798 elsif (p_rule = c_VEHICLE_CATEGORY) then
1799 /*
1800 if Vehicle Category is not selected
1801 then return 3 nulls (option_code, vehicle_type_code, fuel_type_code)
1802 */
1803 return l_vc_stmt;
1804 elsif (p_rule = c_LOCATION or p_rule = c_EMPLOYEE_ROLE or p_rule = c_THRESHOLD) then
1805 return l_number_stmt;
1806 elsif (p_rule = c_VEHICLE_TYPE or p_rule = c_FUEL_TYPE) then
1807 return l_varchar2_stmt;
1808 else
1809 return '';
1810 end if;
1811 end if;
1812
1813 EXCEPTION
1814 WHEN no_data_found THEN
1815 return(null);
1816 WHEN OTHERS THEN
1817 raise;
1818 END getUnionStmtForRuleOption;
1819
1820
1821 /*========================================================================
1822 | PRIVATE PROCEDURE checkAirfarePolicyLines
1823 |
1824 | DESCRIPTION
1825 | If Airfare Policy Lines, default TICKET_CLASS_DOMESTIC/TICKET_CLASS_INTERNATIONAL
1826 | to 'COACH'.
1827 |
1828 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1829 |
1830 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1831 |
1832 | PARAMETERS
1833 | p_policy_id IN Policy Identifier
1834 |
1835 | MODIFICATION HISTORY
1836 | Date Author Description of Changes
1837 | 24-Dec-2002 R Langi Created
1838 |
1839 *=======================================================================*/
1840 PROCEDURE checkAirfarePolicyLines(p_policy_id IN ap_pol_headers.policy_id%TYPE) IS
1841
1842 BEGIN
1843
1844 if (getPolicyCategoryCode(p_policy_id) <> 'AIRFARE')
1845 then
1846 return;
1847 else
1848 update ap_pol_lines
1849 set ticket_class_domestic = 'COACH'
1850 where policy_id = p_policy_id
1851 and ticket_class_domestic is null;
1852
1853 update ap_pol_lines
1854 set ticket_class_international = 'COACH'
1855 where policy_id = p_policy_id
1856 and ticket_class_international is null;
1857 end if;
1858
1859 EXCEPTION
1860 WHEN no_data_found THEN
1861 return;
1862 WHEN OTHERS THEN
1863 raise;
1864 END checkAirfarePolicyLines;
1865
1866
1867 /*========================================================================
1868 | PUBLIC PROCEDURE permutatePolicyLines
1869 |
1870 | DESCRIPTION
1871 | - if a Rule is not enabled or Schedule Option not defined for an enabled Rule then remove the
1872 | obsoleted Policy Line
1873 | - this will never recreate permutated lines based on existing option (rerunnable)
1874 | - if option doesn't exist then creates permutation for new option
1875 | - if option end dated then set Policy Line status to inactive
1876 |
1877 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1878 |
1879 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1880 |
1881 | PARAMETERS
1882 | p_user_id IN User Identifier
1883 | p_policy_id IN Policy Identifier
1884 |
1885 | MODIFICATION HISTORY
1886 | Date Author Description of Changes
1887 | 16-May-2002 R Langi Created
1888 |
1889 *=======================================================================*/
1890 PROCEDURE permutatePolicyLines(p_user_id IN NUMBER,
1891 p_policy_id IN ap_pol_headers.policy_id%TYPE) IS
1892
1893 l_schedule_period_id ap_pol_schedule_periods.schedule_period_id%TYPE;
1894 l_permutate_curref INTEGER;
1895 l_rows_permutated NUMBER := 0;
1896
1897 l_policy_line_count NUMBER := 0;
1898
1899 l_insert_sql_stmt VARCHAR2(4000);
1900 l_where_sql_stmt VARCHAR2(4000);
1901 l_not_exists_sql_stmt VARCHAR2(4000);
1902
1903 l_l_sql_stmt VARCHAR2(4000);
1904 l_r_sql_stmt VARCHAR2(4000);
1905 l_c_sql_stmt VARCHAR2(4000);
1906 l_vc_sql_stmt VARCHAR2(4000);
1907 l_vt_sql_stmt VARCHAR2(4000);
1908 l_ft_sql_stmt VARCHAR2(4000);
1909 l_dt_sql_stmt VARCHAR2(4000);
1910
1911 l_location_enabled VARCHAR2(80) := getUnionStmtForRuleOption(p_policy_id, c_LOCATION);
1912 l_role_enabled VARCHAR2(80) := getUnionStmtForRuleOption(p_policy_id, c_EMPLOYEE_ROLE);
1913 l_currency_enabled VARCHAR2(160) := getUnionStmtForRuleOption(p_policy_id, c_CURRENCY);
1914 l_vehicle_category_enabled VARCHAR2(80) := getUnionStmtForRuleOption(p_policy_id, c_VEHICLE_CATEGORY);
1915 l_vehicle_type_enabled VARCHAR2(80) := getUnionStmtForRuleOption(p_policy_id, c_VEHICLE_TYPE);
1916 l_fuel_type_enabled VARCHAR2(80) := getUnionStmtForRuleOption(p_policy_id, c_FUEL_TYPE);
1917 l_thresholds_enabled VARCHAR2(80) := getUnionStmtForRuleOption(p_policy_id, c_THRESHOLD);
1918 l_addon_rates_enabled VARCHAR2(1) := checkRuleOption(p_policy_id, c_ADDON_RATES);
1919 l_night_rates_enabled VARCHAR2(1) := isNightRatesEnabled(p_policy_id);
1920
1921 l_schedule_option_rec ap_pol_schedule_options%ROWTYPE;
1922 l_zero_threshold_count NUMBER;
1923 l_category_code ap_pol_headers.category_code%TYPE;
1924 l_rate_type_code ap_pol_schedule_options.rate_type_code%TYPE;
1925 l_schedule_type ap_pol_headers.schedule_type_code%TYPE;
1926 l_source ap_pol_headers.source%TYPE;
1927
1928 ---------------------------------------
1929 -- cursor for schedule periods
1930 ---------------------------------------
1931 cursor c_schedule_period_id is
1932 select schedule_period_id
1933 from ap_pol_schedule_periods
1934 where policy_id = p_policy_id;
1935
1936 ---------------------------------------------------
1937 -- cursor for first last and same day rate periods
1938 --------------------------------------------------
1939 cursor c_rate_types is
1940 select distinct rate_type_code
1941 from ap_pol_schedule_options
1942 where policy_id = p_policy_id
1943 and rate_type_code in ('FIRST_PERIOD', 'LAST_PERIOD', 'SAME_DAY')
1944 and option_type = 'TIME_THRESHOLD';
1945
1946 ---------------------------------------
1947 -- cursor for insert/select
1948 ---------------------------------------
1949 cursor l_insert_cursor is
1950 select
1951 '
1952 insert into AP_POL_LINES
1953 (
1954 POLICY_LINE_ID,
1955 POLICY_ID,
1956 SCHEDULE_PERIOD_ID,
1957 LOCATION_ID,
1958 ROLE_ID,
1959 CURRENCY_CODE,
1960 VEHICLE_CATEGORY,
1961 VEHICLE_TYPE,
1962 FUEL_TYPE,
1963 RANGE_LOW,
1964 RANGE_HIGH,
1965 RATE_TYPE_CODE,
1966 STATUS,
1967 CREATION_DATE,
1968 CREATED_BY,
1969 LAST_UPDATE_DATE,
1970 LAST_UPDATED_BY
1971 )
1972 select
1973 AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
1974 :p_policy_id AS POLICY_ID,
1975 :p_schedule_period_id AS SCHEDULE_PERIOD_ID,
1976 NEW_LOCATION_ID AS LOCATION_ID,
1977 NEW_ROLE_ID AS ROLE_ID,
1978 NEW_CURRENCY_CODE AS CURRENCY_CODE,
1979 NEW_VEHICLE_CATEGORY AS VEHICLE_CATEGORY,
1980 NEW_VEHICLE_TYPE AS VEHICLE_TYPE,
1981 NEW_FUEL_TYPE AS FUEL_TYPE,
1982 NEW_RANGE_LOW AS RANGE_LOW,
1983 NEW_RANGE_HIGH AS RANGE_HIGH,
1984 NEW_RATE_TYPE_CODE AS RATE_TYPE_CODE,
1985 ''NEW'' AS STATUS,
1986 sysdate AS CREATION_DATE,
1987 :p_user_id AS CREATED_BY,
1988 sysdate AS LAST_UPDATE_DATE,
1989 :p_user_id AS LAST_UPDATED_BY
1990 from
1991 (
1992 select distinct
1993 NEW_LOCATION_ID,
1994 NEW_ROLE_ID,
1995 NEW_CURRENCY_CODE,
1996 NEW_VEHICLE_CATEGORY,
1997 NEW_VEHICLE_TYPE,
1998 NEW_FUEL_TYPE,
1999 NEW_RANGE_LOW,
2000 NEW_RANGE_HIGH,
2001 NEW_RATE_TYPE_CODE
2002 from
2003 (
2004 select
2005 l.LOCATION_ID AS NEW_LOCATION_ID,
2006 r.ROLE_ID AS NEW_ROLE_ID,
2007 c.CURRENCY_CODE AS NEW_CURRENCY_CODE,
2008 vc.OPTION_CODE AS NEW_VEHICLE_CATEGORY,
2009 decode(vc.OPTION_CODE, null, vt.OPTION_CODE, decode(vc.VEHICLE_TYPE_CODE, ''R'', vt.OPTION_CODE, null)) AS NEW_VEHICLE_TYPE,
2010 decode(vc.OPTION_CODE, null, ft.OPTION_CODE, decode(vc.FUEL_TYPE_CODE, ''R'', ft.OPTION_CODE, null)) AS NEW_FUEL_TYPE,
2011 dt.THRESHOLD AS NEW_RANGE_LOW,
2012 ap_web_policy_UTILS.getHighEndOfThreshold(:p_policy_id, dt.THRESHOLD) AS NEW_RANGE_HIGH,
2013 :p_rate_type AS NEW_RATE_TYPE_CODE
2014 from
2015 '
2016 from sys.dual; /* l_insert_cursor */
2017
2018 ---------------------------------------
2019 -- cursor for all locations to use
2020 ---------------------------------------
2021 cursor l_l_cursor is
2022 select
2023 '
2024 (select LOCATION_ID
2025 from AP_POL_SCHEDULE_OPTIONS pso
2026 where
2027 POLICY_ID = :p_policy_id
2028 and OPTION_TYPE = :c_LOCATION
2029 and LOCATION_ID IS NOT NULL
2030 and nvl(END_DATE, SYSDATE+1) > SYSDATE
2031 '||l_location_enabled||'
2032 ) l,
2033 '
2034 from sys.dual; /* l_l_cursor */
2035
2036 ---------------------------------------
2037 -- cursor for all roles to use
2038 ---------------------------------------
2039 cursor l_r_cursor is
2040 select
2041 '
2042 (select ROLE_ID
2043 from AP_POL_SCHEDULE_OPTIONS pso
2044 where
2045 POLICY_ID = :p_policy_id
2046 and OPTION_TYPE = :c_EMPLOYEE_ROLE
2047 and ROLE_ID IS NOT NULL
2048 and nvl(END_DATE, SYSDATE+1) > SYSDATE
2049 '||l_role_enabled||'
2050 ) r,
2051 '
2052 from sys.dual; /* l_r_cursor */
2053
2054 ---------------------------------------
2055 -- cursor for all currency codes to use
2056 ---------------------------------------
2057 cursor l_c_cursor is
2058 select
2059 '
2060 (select CURRENCY_CODE
2061 from AP_POL_SCHEDULE_OPTIONS pso
2062 where
2063 POLICY_ID = :p_policy_id
2064 and OPTION_TYPE = :c_CURRENCY
2065 and CURRENCY_CODE IS NOT NULL
2066 and nvl(END_DATE, SYSDATE+1) > SYSDATE
2067 '||l_currency_enabled||'
2068 ) c,
2069 '
2070 from sys.dual; /* l_c_cursor */
2071
2072 ---------------------------------------
2073 -- cursor for all vehicle categories to use
2074 ---------------------------------------
2075 cursor l_vc_cursor is
2076 select
2077 '
2078 (select OPTION_CODE, VEHICLE_TYPE_CODE, FUEL_TYPE_CODE
2079 from AP_POL_SCHEDULE_OPTIONS pso
2080 where
2081 POLICY_ID = :p_policy_id
2082 and OPTION_TYPE = :c_VEHICLE_CATEGORY
2083 and OPTION_CODE IS NOT NULL
2084 and nvl(END_DATE, SYSDATE+1) > SYSDATE
2085 '||l_vehicle_category_enabled||'
2086 ) vc,
2087 '
2088 from sys.dual; /* l_vc_cursor */
2089
2090 ---------------------------------------
2091 -- cursor for all vehicle types to use
2092 ---------------------------------------
2093 cursor l_vt_cursor is
2094 select
2095 '
2096 (select OPTION_CODE
2097 from AP_POL_SCHEDULE_OPTIONS pso
2098 where
2099 POLICY_ID = :p_policy_id
2100 and OPTION_TYPE = :c_VEHICLE_TYPE
2101 and OPTION_CODE IS NOT NULL
2102 and nvl(END_DATE, SYSDATE+1) > SYSDATE
2103 '||l_vehicle_type_enabled||'
2104 ) vt,
2105 '
2106 from sys.dual; /* l_vt_cursor */
2107
2108 ---------------------------------------
2109 -- cursor for all fuel types to use
2110 ---------------------------------------
2111 cursor l_ft_cursor is
2112 select
2113 '
2114 (select OPTION_CODE
2115 from AP_POL_SCHEDULE_OPTIONS pso
2116 where
2117 POLICY_ID = :p_policy_id
2118 and OPTION_TYPE = :c_FUEL_TYPE
2119 and OPTION_CODE IS NOT NULL
2120 and nvl(END_DATE, SYSDATE+1) > SYSDATE
2121 '||l_fuel_type_enabled||'
2122 ) ft,
2123 '
2124 from sys.dual; /* l_ft_cursor */
2125
2126 ---------------------------------------
2127 -- cursor for all thresholds to use
2128 ---------------------------------------
2129 cursor l_dt_cursor is
2130 select
2131 '
2132 (select THRESHOLD
2133 from AP_POL_SCHEDULE_OPTIONS pso
2134 where
2135 POLICY_ID = :p_policy_id
2136 and (OPTION_TYPE = :c_DISTANCE_THRESHOLD or OPTION_TYPE = :c_TIME_THRESHOLD)
2137 and THRESHOLD IS NOT NULL
2138 and nvl(END_DATE, SYSDATE+1) > SYSDATE
2139 and nvl(rate_type_code, ''NULL'') = nvl(:p_rate_type, ''NULL'')
2140 '||l_thresholds_enabled||'
2141 ) dt
2142 '
2143 from sys.dual; /* l_dt_cursor */
2144
2145
2146 ---------------------------------------
2147 -- cursor for where rows
2148 ---------------------------------------
2149 cursor l_where_cursor is
2150 select
2151 '
2152 )
2153 where
2154 (
2155 NEW_LOCATION_ID is not null
2156 or NEW_ROLE_ID is not null
2157 or NEW_CURRENCY_CODE is not null
2158 or NEW_VEHICLE_CATEGORY is not null
2159 or NEW_VEHICLE_TYPE is not null
2160 or NEW_FUEL_TYPE is not null
2161 or NEW_RANGE_LOW is not null
2162 or NEW_RANGE_HIGH is not null
2163 )
2164 )
2165 '
2166 from sys.dual; /* l_where_cursor */
2167
2168
2169 ---------------------------------------
2170 -- cursor for adding new rules/options
2171 ---------------------------------------
2172 cursor l_not_exists_cursor is
2173 select
2174 '
2175 )
2176 where
2177 (
2178 NEW_LOCATION_ID is not null
2179 or NEW_ROLE_ID is not null
2180 or NEW_CURRENCY_CODE is not null
2181 or NEW_VEHICLE_CATEGORY is not null
2182 or NEW_VEHICLE_TYPE is not null
2183 or NEW_FUEL_TYPE is not null
2184 or NEW_RANGE_LOW is not null
2185 or NEW_RANGE_HIGH is not null
2186 )
2187 and
2188 not exists
2189 (
2190 select epl.POLICY_LINE_ID
2191 from AP_POL_LINES epl
2192 where epl.POLICY_ID = :p_policy_id
2193 and epl.SCHEDULE_PERIOD_ID = :p_schedule_period_id
2194 and nvl(epl.LOCATION_ID, :dummy_number) = nvl(NEW_LOCATION_ID, :dummy_number)
2195 and nvl(epl.ROLE_ID, :dummy_number) = nvl(NEW_ROLE_ID, :dummy_number)
2196 and
2197 (
2198 (nvl(epl.CURRENCY_CODE, :dummy_varchar2) = nvl(NEW_CURRENCY_CODE, :dummy_varchar2))
2199 or
2200 (epl.CURRENCY_CODE is not null and NEW_CURRENCY_CODE is null)
2201 )
2202 and nvl(epl.VEHICLE_CATEGORY, :dummy_varchar2) = nvl(NEW_VEHICLE_CATEGORY, :dummy_varchar2)
2203 and nvl(epl.VEHICLE_TYPE, :dummy_varchar2) = nvl(NEW_VEHICLE_TYPE, :dummy_varchar2)
2204 and nvl(epl.FUEL_TYPE, :dummy_varchar2) = nvl(NEW_FUEL_TYPE, :dummy_varchar2)
2205 and nvl(epl.RANGE_LOW, :dummy_number) = nvl(NEW_RANGE_LOW, :dummy_number)
2206 and nvl(epl.RANGE_HIGH, :dummy_number) = nvl(NEW_RANGE_HIGH, :dummy_number)
2207 and nvl(epl.RATE_TYPE_CODE, :dummy_varchar2) = nvl(NEW_RATE_TYPE_CODE, :dummy_varchar2)
2208 )
2209 )
2210 '
2211 from sys.dual; /* l_not_exists_cursor */
2212
2213
2214
2215
2216 BEGIN
2217
2218 select category_code, schedule_type_code, source
2219 into l_category_code, l_schedule_type, l_source
2220 from ap_pol_headers
2221 where policy_id = p_policy_id;
2222
2223 -- ---------------------------------------------------------------
2224 -- If this is a CONUS/OCONUS policy then call the appropriate
2225 -- procedure and return
2226 -- ---------------------------------------------------------------
2227 IF ( l_source = 'CONUS' ) THEN
2228 BEGIN
2229 permutateConusLines(p_user_id, p_policy_id);
2230 return;
2231 END;
2232 END IF;
2233 -- ----------------------------------------------------------
2234 -- Insert zero row threshold before generating permutations
2235 -- ----------------------------------------------------------
2236 IF ( l_category_code = 'PER_DIEM' ) THEN
2237 BEGIN
2238 -- Delete zero threshold rows where not needed
2239 delete from ap_pol_schedule_options
2240 where policy_id = p_policy_id
2241 and option_type = 'TIME_THRESHOLD'
2242 and threshold = 0
2243 and rate_type_code not in
2244 ( select distinct rate_type_code
2245 from ap_pol_schedule_options
2246 where policy_id = p_policy_id
2247 and rate_type_code in ('FIRST_PERIOD', 'LAST_PERIOD', 'SAME_DAY')
2248 and option_type = 'TIME_THRESHOLD'
2249 and threshold > 0 )
2250 and rate_type_code <> 'STANDARD';
2251
2252 FOR rate_type_cur in c_rate_types
2253 LOOP
2254 select count(1)
2255 into l_zero_threshold_count
2256 from ap_pol_schedule_options
2257 where policy_id = p_policy_id
2258 and rate_type_code = rate_type_cur.rate_type_code
2259 and threshold = 0;
2260
2261 IF ( l_zero_threshold_count = 0 ) THEN
2262 BEGIN
2263 SELECT ap_pol_schedule_options_s.NEXTVAL
2264 INTO l_schedule_option_rec.schedule_option_id
2265 FROM DUAL;
2266
2267 INSERT INTO ap_pol_schedule_options
2268 (
2269 policy_id,
2270 schedule_option_id,
2271 option_type,
2272 threshold,
2273 status,
2274 creation_date,
2275 created_by,
2276 last_update_date,
2277 last_updated_by,
2278 rate_type_code
2279 )
2280 VALUES
2281 (
2282 p_policy_id,
2283 l_schedule_option_rec.schedule_option_id,
2284 'TIME_THRESHOLD',
2285 0,
2286 'SAVED',
2287 sysdate,
2288 p_user_id,
2289 sysdate,
2290 p_user_id,
2291 rate_type_cur.rate_type_code
2292 );
2293
2294 /*--------- COMMENTING THIS CODE SINCE GSCC NOT FIXED FOR REC TYPE --------------
2295 l_schedule_option_rec.policy_id := p_policy_id;
2296 l_schedule_option_rec.option_type := 'TIME_THRESHOLD';
2297 l_schedule_option_rec.threshold := 0;
2298 l_schedule_option_rec.status := 'SAVED';
2299 l_schedule_option_rec.creation_date := sysdate;
2300 l_schedule_option_rec.created_by := p_user_id;
2301 l_schedule_option_rec.last_update_date := sysdate;
2302 l_schedule_option_rec.last_updated_by := p_user_id;
2303 l_schedule_option_rec.rate_type_code := rate_type_cur.rate_type_code;
2304
2305 INSERT INTO ap_pol_schedule_options values l_schedule_option_rec;
2306 ----------------------------------------------------------------------------------*/
2307 END;
2308 END IF;
2309 END LOOP;
2310
2311 -- Standard rate is a special case. Should not insert 0 record
2312 -- for midnight to midnight schedules which have first and last rates
2313 -- or for allowance schedules with time rule of start and end times
2314 select count(1)
2315 into l_zero_threshold_count
2316 from ap_pol_headers
2317 where policy_id = p_policy_id
2318 and time_based_entry_flag = 'Y'
2319 and ( day_period_code <> 'MIDNIGHT' or
2320 (nvl(rate_period_type_code, 'STANDARD') = 'STANDARD' and schedule_type_code = 'PER_DIEM') or
2321 ( schedule_type_code = 'ALLOWANCE' and allowance_time_rule_code = 'TIME_THRESHOLD' )
2322 );
2323
2324 IF ( l_zero_threshold_count = 1 ) THEN
2325 BEGIN
2326 select count(1)
2327 into l_zero_threshold_count
2328 from ap_pol_schedule_options
2329 where policy_id = p_policy_id
2330 and rate_type_code = 'STANDARD'
2331 and threshold = 0;
2332
2333 IF ( l_zero_threshold_count = 0 ) THEN
2334 BEGIN
2335 SELECT ap_pol_schedule_options_s.NEXTVAL
2336 INTO l_schedule_option_rec.schedule_option_id
2337 FROM DUAL;
2338
2339 INSERT INTO ap_pol_schedule_options
2340 (
2341 policy_id,
2342 schedule_option_id,
2343 option_type,
2344 threshold,
2345 status,
2346 creation_date,
2347 created_by,
2348 last_update_date,
2349 last_updated_by,
2350 rate_type_code
2351 )
2352 VALUES
2353 (
2354 p_policy_id,
2355 l_schedule_option_rec.schedule_option_id,
2356 'TIME_THRESHOLD',
2357 0,
2358 'SAVED',
2359 sysdate,
2360 p_user_id,
2361 sysdate,
2362 p_user_id,
2363 'STANDARD'
2364 );
2365
2366 /*--------- COMMENTING THIS CODE SINCE GSCC NOT FIXED FOR REC TYPE --------------
2367 l_schedule_option_rec.policy_id := p_policy_id;
2368 l_schedule_option_rec.option_type := 'TIME_THRESHOLD';
2369 l_schedule_option_rec.threshold := 0;
2370 l_schedule_option_rec.status := 'SAVED';
2371 l_schedule_option_rec.creation_date := sysdate;
2372 l_schedule_option_rec.created_by := p_user_id;
2373 l_schedule_option_rec.last_update_date := sysdate;
2374 l_schedule_option_rec.last_updated_by := p_user_id;
2375 l_schedule_option_rec.rate_type_code := 'STANDARD';
2376
2377 INSERT INTO ap_pol_schedule_options values l_schedule_option_rec;
2378 ---------------------------------------------------------------------------------*/
2379
2380 END;
2381 END IF;
2382
2383 END;
2384 ELSE
2385 -- ---------------------------------------------------------------------
2386 -- This means that we should not have a zero row for standard rate type
2387 -- So delete any such rows
2388 -- ---------------------------------------------------------------------
2389 delete from ap_pol_schedule_options
2390 where policy_id = p_policy_id
2391 and rate_type_code = 'STANDARD'
2392 and threshold = 0;
2393
2394 END IF;
2395
2396 l_rate_type_code := 'STANDARD';
2397 END;
2398 ELSE
2399 l_rate_type_code := null;
2400 END IF;
2401
2402
2403
2404 removeObsoletedPolicyLines(p_policy_id);
2405
2406 open l_insert_cursor;
2407 open l_where_cursor;
2408 open l_not_exists_cursor;
2409
2410 open l_l_cursor;
2411 open l_r_cursor;
2412 open l_c_cursor;
2413 open l_vc_cursor;
2414 open l_vt_cursor;
2415 open l_ft_cursor;
2416 open l_dt_cursor;
2417
2418 fetch l_insert_cursor into l_insert_sql_stmt;
2419 fetch l_where_cursor into l_where_sql_stmt;
2420 fetch l_not_exists_cursor into l_not_exists_sql_stmt;
2421
2422 fetch l_l_cursor into l_l_sql_stmt;
2423 fetch l_r_cursor into l_r_sql_stmt;
2424 fetch l_c_cursor into l_c_sql_stmt;
2425 fetch l_vc_cursor into l_vc_sql_stmt;
2426 fetch l_vt_cursor into l_vt_sql_stmt;
2427 fetch l_ft_cursor into l_ft_sql_stmt;
2428 fetch l_dt_cursor into l_dt_sql_stmt;
2429
2430 --------------
2431 -- open cursor
2432 --------------
2433 l_permutate_curref := DBMS_SQL.OPEN_CURSOR;
2434
2435 --------------
2436 -- begin loop thru all periods
2437 --------------
2438 open c_schedule_period_id;
2439 loop
2440
2441 fetch c_schedule_period_id into l_schedule_period_id;
2442 exit when c_schedule_period_id%NOTFOUND;
2443
2444 select count(policy_line_id)
2445 into l_policy_line_count
2446 from ap_pol_lines
2447 where policy_id = p_policy_id
2448 and schedule_period_id = l_schedule_period_id;
2449
2450 if (l_policy_line_count = 0) then
2451 --------------
2452 -- parse cursor
2453 --------------
2454 DBMS_SQL.PARSE(l_permutate_curref,
2455 l_insert_sql_stmt||
2456 l_l_sql_stmt||
2457 l_r_sql_stmt||
2458 l_c_sql_stmt||
2459 l_vc_sql_stmt||
2460 l_vt_sql_stmt||
2461 l_ft_sql_stmt||
2462 l_dt_sql_stmt||
2463 l_where_sql_stmt, DBMS_SQL.NATIVE);
2464 else
2465 --------------
2466 -- parse cursor
2467 --------------
2468 DBMS_SQL.PARSE(l_permutate_curref,
2469 l_insert_sql_stmt||
2470 l_l_sql_stmt||
2471 l_r_sql_stmt||
2472 l_c_sql_stmt||
2473 l_vc_sql_stmt||
2474 l_vt_sql_stmt||
2475 l_ft_sql_stmt||
2476 l_dt_sql_stmt||
2477 l_not_exists_sql_stmt, DBMS_SQL.NATIVE);
2478 --------------
2479 -- supply binds specific to this case
2480 --------------
2481 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':dummy_number', -11);
2482 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':dummy_varchar2', '-11');
2483
2484 end if;
2485
2486 --------------
2487 -- supply binds
2488 --------------
2489 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':p_policy_id', p_policy_id);
2490 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':p_schedule_period_id', l_schedule_period_id);
2491 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':p_user_id', p_user_id);
2492 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':c_LOCATION', c_LOCATION);
2493 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':c_EMPLOYEE_ROLE', c_EMPLOYEE_ROLE);
2494 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':c_CURRENCY', c_CURRENCY);
2495 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':c_VEHICLE_CATEGORY', c_VEHICLE_CATEGORY);
2496 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':c_VEHICLE_TYPE', c_VEHICLE_TYPE);
2497 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':c_FUEL_TYPE', c_FUEL_TYPE);
2498 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':c_DISTANCE_THRESHOLD', c_DISTANCE_THRESHOLD);
2499 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':c_TIME_THRESHOLD', c_TIME_THRESHOLD);
2500 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':p_rate_type', l_rate_type_code);
2501 --------------
2502 -- execute cursor
2503 --------------
2504 l_rows_permutated := DBMS_SQL.EXECUTE(l_permutate_curref);
2505
2506 end loop;
2507 close c_schedule_period_id;
2508 --------------
2509 -- end loop thru all periods
2510 --------------
2511
2512 --------------
2513 -- close cursor
2514 --------------
2515 DBMS_SQL.CLOSE_CURSOR(l_permutate_curref);
2516
2517 close l_insert_cursor;
2518 close l_where_cursor;
2519 close l_not_exists_cursor;
2520
2521 close l_l_cursor;
2522 close l_r_cursor;
2523 close l_c_cursor;
2524 close l_vc_cursor;
2525 close l_vt_cursor;
2526 close l_ft_cursor;
2527 close l_dt_cursor;
2528
2529 ----------------------------------------------------------------------
2530 -- Permutate First Period, Last Period and Same Day Rates if implemented
2531 ----------------------------------------------------------------------
2532 BEGIN
2533
2534 FOR rate_type_cur in c_rate_types
2535 LOOP
2536 permutatePolicyLines(p_user_id,
2537 p_policy_id,
2538 rate_type_cur.rate_type_code);
2539 END LOOP;
2540 END;
2541
2542 -------------------------------------------------------------------------------
2543 -- Insert addon mileage rate permutations if addon rates have been enabled
2544 -------------------------------------------------------------------------------
2545 IF ( l_addon_rates_enabled = 'Y' ) THEN
2546 permutateAddonRates(p_user_id,
2547 p_policy_id,
2548 l_schedule_period_id);
2549 END IF;
2550
2551 -------------------------------------------------------------------------------
2552 -- Insert addon mileage rate permutations if addon rates have been enabled
2553 -------------------------------------------------------------------------------
2554 IF ( l_night_rates_enabled = 'Y' ) THEN
2555 permutateNightRates(p_user_id,
2556 p_policy_id,
2557 l_schedule_period_id);
2558 END IF;
2559
2560 -- -----------------------------------------------------------------------
2561 -- If this is an allowance schedule set the calculation methods to AMOUNT
2562 -- -----------------------------------------------------------------------
2563 IF ( l_schedule_type = 'ALLOWANCE' ) THEN
2564 update ap_pol_lines
2565 set calculation_method = 'AMOUNT',
2566 accommodation_calc_method = 'AMOUNT'
2567 where policy_id = p_policy_id
2568 and nvl(calculation_method, 'X') <> 'AMOUNT';
2569 END IF;
2570
2571 updateInactivePolicyLines(p_policy_id);
2572 checkAirfarePolicyLines(p_policy_id);
2573
2574 status_saved_sched_opts(p_policy_id);
2575
2576
2577 EXCEPTION
2578 WHEN OTHERS THEN
2579 raise;
2580 END permutatePolicyLines;
2581
2582
2583 /*========================================================================
2584 | PUBLIC PROCEDURE removeObsoletedPolicyLines
2585 |
2586 | DESCRIPTION
2587 | - a policy line is obsolete if:
2588 | 1. if a rule has become disabled for an option already permutated
2589 | 2. if an option has been removed for an enabled rule
2590 | 3. if a new rule has been added then existing blank permutations becomes invalid
2591 | 4. if a new threshold has been added then existing threshold range becomes invalid
2592 |
2593 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2594 |
2595 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2596 |
2597 | PARAMETERS
2598 | p_policy_id IN Policy Identifier
2599 |
2600 | MODIFICATION HISTORY
2601 | Date Author Description of Changes
2602 | 16-May-2002 R Langi Created
2603 |
2604 *=======================================================================*/
2605 PROCEDURE removeObsoletedPolicyLines(p_policy_id IN ap_pol_headers.policy_id%TYPE) IS
2606
2607 l_obsolete_curref INTEGER;
2608 l_rows_obsoleted NUMBER := 0;
2609
2610 l_obsolete_sql_stmt VARCHAR2(4000);
2611 l_l_sql_stmt VARCHAR2(4000);
2612 l_r_sql_stmt VARCHAR2(4000);
2613 l_c_sql_stmt VARCHAR2(4000);
2614 l_vc_sql_stmt VARCHAR2(4000);
2615 l_vt_sql_stmt VARCHAR2(4000);
2616 l_ft_sql_stmt VARCHAR2(4000);
2617 l_dt_sql_stmt VARCHAR2(4000);
2618 l_amr_sql_stmt VARCHAR2(4000);
2619
2620 l_currency_preference ap_pol_headers.currency_preference%TYPE;
2621 l_currency_code ap_pol_headers.currency_code%TYPE;
2622
2623 cursor l_l_cursor is
2624 select
2625 '
2626 delete
2627 from AP_POL_LINES pl
2628 where pl.POLICY_ID = :p_policy_id
2629 and ((pl.LOCATION_ID is not null
2630 and not exists
2631 (select pso.LOCATION_ID
2632 from AP_POL_SCHEDULE_OPTIONS pso
2633 where pso.POLICY_ID = pl.POLICY_ID
2634 and pso.OPTION_TYPE = :c_LOCATION
2635 and pso.LOCATION_ID is not null
2636 and pso.LOCATION_ID = pl.LOCATION_ID
2637 )
2638 )
2639 or
2640 (pl.LOCATION_ID is null
2641 and exists
2642 (select pso.LOCATION_ID
2643 from AP_POL_SCHEDULE_OPTIONS pso
2644 where pso.POLICY_ID = pl.POLICY_ID
2645 and pso.OPTION_TYPE = :c_LOCATION
2646 and pso.LOCATION_ID is not null
2647 )
2648 )
2649 '
2650 from sys.dual; /* l_l_cursor */
2651
2652 cursor l_r_cursor is
2653 select
2654 '
2655 or
2656 (pl.ROLE_ID is not null
2657 and not exists
2658 (select pso.ROLE_ID
2659 from AP_POL_SCHEDULE_OPTIONS pso
2660 where pso.POLICY_ID = pl.POLICY_ID
2661 and pso.OPTION_TYPE = :c_EMPLOYEE_ROLE
2662 and pso.ROLE_ID is not null
2663 and pso.ROLE_ID = pl.ROLE_ID
2664 )
2665 )
2666 or
2667 (pl.ROLE_ID is null
2668 and exists
2669 (select pso.ROLE_ID
2670 from AP_POL_SCHEDULE_OPTIONS pso
2671 where pso.POLICY_ID = pl.POLICY_ID
2672 and pso.OPTION_TYPE = :c_EMPLOYEE_ROLE
2673 and pso.ROLE_ID is not null
2674 )
2675 )
2676 '
2677 from sys.dual; /* l_r_cursor */
2678
2679 cursor l_c_cursor is
2680 select
2681 '
2682 or
2683 (pl.CURRENCY_CODE is not null
2684 and
2685 (not exists
2686 (select pso.CURRENCY_CODE
2687 from AP_POL_SCHEDULE_OPTIONS pso
2688 where pso.POLICY_ID = pl.POLICY_ID
2689 and pso.OPTION_TYPE = :c_CURRENCY
2690 and pso.CURRENCY_CODE is not null
2691 and pso.CURRENCY_CODE = pl.CURRENCY_CODE
2692 )
2693 and
2694 not exists
2695 (select ph.CURRENCY_PREFERENCE
2696 from AP_POL_HEADERS ph
2697 where ph.POLICY_ID = pl.POLICY_ID
2698 and (ph.CURRENCY_PREFERENCE = :c_SRC
2699 or
2700 ph.CURRENCY_PREFERENCE = :c_LCR
2701 )
2702 )
2703 )
2704 )
2705 or
2706 (pl.CURRENCY_CODE is not null
2707 and
2708 exists
2709 (select ph.CURRENCY_PREFERENCE
2710 from AP_POL_HEADERS ph
2711 where ph.POLICY_ID = pl.POLICY_ID
2712 and ph.CURRENCY_PREFERENCE = :c_SRC
2713 )
2714 and
2715 not exists
2716 (select ph.CURRENCY_CODE
2717 from AP_POL_HEADERS ph
2718 where ph.POLICY_ID = pl.POLICY_ID
2719 and ph.CURRENCY_CODE = pl.CURRENCY_CODE
2720 )
2721 )
2722 '
2723 from sys.dual; /* l_c_cursor */
2724
2725 cursor l_vc_cursor is
2726 select
2727 '
2728 or
2729 (pl.VEHICLE_CATEGORY is not null
2730 and not exists
2731 (select pso.OPTION_CODE
2732 from AP_POL_SCHEDULE_OPTIONS pso
2733 where pso.POLICY_ID = pl.POLICY_ID
2734 and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
2735 and pso.OPTION_CODE is not null
2736 and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
2737 )
2738 )
2739 or
2740 (pl.VEHICLE_CATEGORY is null
2741 and exists
2742 (select pso.OPTION_CODE
2743 from AP_POL_SCHEDULE_OPTIONS pso
2744 where pso.POLICY_ID = pl.POLICY_ID
2745 and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
2746 and pso.OPTION_CODE is not null
2747 )
2748 )
2749 '
2750 from sys.dual; /* l_vc_cursor */
2751
2752 cursor l_vt_cursor is
2753 select
2754 '
2755 or
2756 (pl.VEHICLE_TYPE is not null
2757 and
2758 (not exists
2759 (select pso.OPTION_CODE
2760 from AP_POL_SCHEDULE_OPTIONS pso
2761 where pso.POLICY_ID = pl.POLICY_ID
2762 and pso.OPTION_TYPE = :c_VEHICLE_TYPE
2763 and pso.OPTION_CODE is not null
2764 and pso.OPTION_CODE = pl.VEHICLE_TYPE
2765 )
2766 or exists
2767 (select pso.OPTION_CODE
2768 from AP_POL_SCHEDULE_OPTIONS pso
2769 where pso.POLICY_ID = pl.POLICY_ID
2770 and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
2771 and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
2772 and pso.VEHICLE_TYPE_CODE <> ''R''
2773 ))
2774 )
2775 or
2776 (pl.VEHICLE_TYPE is null
2777 and exists
2778 (select pso.OPTION_CODE
2779 from AP_POL_SCHEDULE_OPTIONS pso
2780 where pso.POLICY_ID = pl.POLICY_ID
2781 and pso.OPTION_TYPE = :c_VEHICLE_TYPE
2782 and pso.OPTION_CODE is not null
2783 )
2784 and not exists
2785 (select pso.OPTION_CODE
2786 from AP_POL_SCHEDULE_OPTIONS pso
2787 where pso.POLICY_ID = pl.POLICY_ID
2788 and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
2789 and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
2790 and pso.VEHICLE_TYPE_CODE <> ''R''
2791 )
2792 )
2793 '
2794 from sys.dual; /* l_vt_cursor */
2795
2796 cursor l_ft_cursor is
2797 select
2798 '
2799 or
2800 (pl.FUEL_TYPE is not null
2801 and
2802 (not exists
2803 (select pso.OPTION_CODE
2804 from AP_POL_SCHEDULE_OPTIONS pso
2805 where pso.POLICY_ID = pl.POLICY_ID
2806 and pso.OPTION_TYPE = :c_FUEL_TYPE
2807 and pso.OPTION_CODE is not null
2808 and pso.OPTION_CODE = pl.FUEL_TYPE
2809 )
2810 or exists
2811 (select pso.OPTION_CODE
2812 from AP_POL_SCHEDULE_OPTIONS pso
2813 where pso.POLICY_ID = pl.POLICY_ID
2814 and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
2815 and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
2816 and pso.FUEL_TYPE_CODE <> ''R''
2817 ))
2818 )
2819 or
2820 (pl.FUEL_TYPE is null
2821 and exists
2822 (select pso.OPTION_CODE
2823 from AP_POL_SCHEDULE_OPTIONS pso
2824 where pso.POLICY_ID = pl.POLICY_ID
2825 and pso.OPTION_TYPE = :c_FUEL_TYPE
2826 and pso.OPTION_CODE is not null
2827 )
2828 and not exists
2829 (select pso.OPTION_CODE
2830 from AP_POL_SCHEDULE_OPTIONS pso
2831 where pso.POLICY_ID = pl.POLICY_ID
2832 and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
2833 and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
2834 and pso.FUEL_TYPE_CODE <> ''R''
2835 )
2836 )
2837 '
2838 from sys.dual; /* l_ft_cursor */
2839
2840 cursor l_dt_cursor is
2841 select
2842 '
2843 or
2844 (pl.RANGE_LOW is not null
2845 and not exists
2846 (select pso.THRESHOLD
2847 from AP_POL_SCHEDULE_OPTIONS pso
2848 where pso.POLICY_ID = pl.POLICY_ID
2849 and (pso.OPTION_TYPE = :c_DISTANCE_THRESHOLD or pso.OPTION_TYPE = :c_TIME_THRESHOLD)
2850 and pso.THRESHOLD is not null
2851 and pso.THRESHOLD = pl.RANGE_LOW
2852 and nvl(pso.rate_type_code, ''NULL'') = nvl(pl.rate_type_code, ''NULL'')
2853 )
2854 )
2855 or
2856 (pl.RANGE_HIGH is not null
2857 and not exists
2858 (select pso.THRESHOLD
2859 from AP_POL_SCHEDULE_OPTIONS pso
2860 where pso.POLICY_ID = pl.POLICY_ID
2861 and (pso.OPTION_TYPE = :c_DISTANCE_THRESHOLD or pso.OPTION_TYPE = :c_TIME_THRESHOLD)
2862 and pso.THRESHOLD is not null
2863 and pso.THRESHOLD = pl.RANGE_HIGH
2864 and nvl(pso.rate_type_code, ''NULL'') = nvl(pl.rate_type_code, ''NULL'')
2865 )
2866 )
2867 or
2868 (pl.RANGE_LOW is null
2869 and exists
2870 (select pso.THRESHOLD
2871 from AP_POL_SCHEDULE_OPTIONS pso
2872 where pso.POLICY_ID = pl.POLICY_ID
2873 and (pso.OPTION_TYPE = :c_DISTANCE_THRESHOLD or pso.OPTION_TYPE = :c_TIME_THRESHOLD)
2874 and pso.THRESHOLD is not null
2875 and nvl(pso.rate_type_code, ''NULL'') = nvl(pl.rate_type_code, ''NULL'')
2876 )
2877 )
2878 or
2879 (pl.RANGE_LOW is not null
2880 and
2881 nvl(pl.RANGE_HIGH, :dummy_number) <>
2882 nvl(AP_WEB_POLICY_UTILS.getHighEndOfThreshold(pl.POLICY_ID,
2883 pl.RANGE_LOW,
2884 nvl(pl.rate_type_code,''STANDARD'')), :dummy_number)
2885 )
2886 '
2887 from sys.dual; /* l_dt_cursor */
2888
2889 ---------------------------------------
2890 -- cursor for addon mileage rates
2891 -- Note: we do not need to remove any addon rate lines
2892 -- for the case of new addon rate rule since these lines
2893 -- would never have existed.
2894 ---------------------------------------
2895 cursor l_amr_cursor is
2896 select
2897 ' or
2898 (pl.ADDON_MILEAGE_RATE_CODE is not null
2899 and not exists
2900 (select pso.OPTION_CODE
2901 from AP_POL_SCHEDULE_OPTIONS pso
2902 where pso.POLICY_ID = pl.POLICY_ID
2903 and pso.OPTION_TYPE = :c_ADDON_RATES
2904 and pso.OPTION_CODE is not null
2905 and pso.OPTION_CODE = pl.ADDON_MILEAGE_RATE_CODE
2906 )
2907 )
2908
2909 )
2910 '
2911 from sys.dual; /* l_amr_cursor */
2912
2913
2914 BEGIN
2915
2916 open l_l_cursor;
2917 open l_r_cursor;
2918 open l_c_cursor;
2919 open l_vc_cursor;
2920 open l_vt_cursor;
2921 open l_ft_cursor;
2922 open l_dt_cursor;
2923 open l_amr_cursor;
2924
2925 fetch l_l_cursor into l_l_sql_stmt;
2926 fetch l_r_cursor into l_r_sql_stmt;
2927 fetch l_c_cursor into l_c_sql_stmt;
2928 fetch l_vc_cursor into l_vc_sql_stmt;
2929 fetch l_vt_cursor into l_vt_sql_stmt;
2930 fetch l_ft_cursor into l_ft_sql_stmt;
2931 fetch l_dt_cursor into l_dt_sql_stmt;
2932 fetch l_amr_cursor into l_amr_sql_stmt;
2933
2934
2935 --------------
2936 -- open cursor
2937 --------------
2938 l_obsolete_curref := DBMS_SQL.OPEN_CURSOR;
2939
2940 --------------
2941 -- parse cursor
2942 --------------
2943 DBMS_SQL.PARSE(l_obsolete_curref,
2944 l_l_sql_stmt||
2945 l_r_sql_stmt||
2946 l_c_sql_stmt||
2947 l_vc_sql_stmt||
2948 l_vt_sql_stmt||
2949 l_ft_sql_stmt||
2950 l_dt_sql_stmt||
2951 l_amr_sql_stmt,
2952 DBMS_SQL.NATIVE);
2953
2954 --------------
2955 -- supply binds
2956 --------------
2957 DBMS_SQL.BIND_VARIABLE(l_obsolete_curref, ':p_policy_id', p_policy_id);
2958 DBMS_SQL.BIND_VARIABLE(l_obsolete_curref, ':c_LOCATION', c_LOCATION);
2959 DBMS_SQL.BIND_VARIABLE(l_obsolete_curref, ':c_EMPLOYEE_ROLE', c_EMPLOYEE_ROLE);
2960 DBMS_SQL.BIND_VARIABLE(l_obsolete_curref, ':c_CURRENCY', c_CURRENCY);
2961 DBMS_SQL.BIND_VARIABLE(l_obsolete_curref, ':c_LCR', c_LCR);
2962 DBMS_SQL.BIND_VARIABLE(l_obsolete_curref, ':c_SRC', c_SRC);
2963 DBMS_SQL.BIND_VARIABLE(l_obsolete_curref, ':c_VEHICLE_CATEGORY', c_VEHICLE_CATEGORY);
2964 DBMS_SQL.BIND_VARIABLE(l_obsolete_curref, ':c_VEHICLE_TYPE', c_VEHICLE_TYPE);
2965 DBMS_SQL.BIND_VARIABLE(l_obsolete_curref, ':c_FUEL_TYPE', c_FUEL_TYPE);
2966 DBMS_SQL.BIND_VARIABLE(l_obsolete_curref, ':c_DISTANCE_THRESHOLD', c_DISTANCE_THRESHOLD);
2967 DBMS_SQL.BIND_VARIABLE(l_obsolete_curref, ':c_TIME_THRESHOLD', c_TIME_THRESHOLD);
2968 DBMS_SQL.BIND_VARIABLE(l_obsolete_curref, ':c_ADDON_RATES', c_ADDON_RATES);
2969 DBMS_SQL.BIND_VARIABLE(l_obsolete_curref, ':dummy_number', -11);
2970
2971 --------------
2972 -- execute cursor
2973 --------------
2974 l_rows_obsoleted := DBMS_SQL.EXECUTE(l_obsolete_curref);
2975
2976 --------------
2977 -- close cursor
2978 --------------
2979 DBMS_SQL.CLOSE_CURSOR(l_obsolete_curref);
2980
2981
2982 close l_l_cursor;
2983 close l_r_cursor;
2984 close l_c_cursor;
2985 close l_vc_cursor;
2986 close l_vt_cursor;
2987 close l_ft_cursor;
2988 close l_dt_cursor;
2989 close l_amr_cursor;
2990
2991 EXCEPTION
2992 WHEN OTHERS THEN
2993 raise;
2994 END removeObsoletedPolicyLines;
2995
2996
2997 /*========================================================================
2998 | PUBLIC PROCEDURE updateInactivePolicyLines
2999 |
3000 | DESCRIPTION
3001 | - if option end dated then set Policy Line status to inactive
3002 | - reactivate inactive lines if end date updated
3003 |
3004 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3005 |
3006 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
3007 |
3008 | PARAMETERS
3009 | p_policy_id IN Policy Identifier
3010 |
3011 | MODIFICATION HISTORY
3012 | Date Author Description of Changes
3013 | 16-May-2002 R Langi Created
3014 |
3015 *=======================================================================*/
3016 PROCEDURE updateInactivePolicyLines(p_policy_id IN ap_pol_headers.policy_id%TYPE) IS
3017
3018 l_inactive_curref INTEGER;
3019 l_rows_inactivated NUMBER := 0;
3020
3021 l_active_curref INTEGER;
3022 l_rows_activated NUMBER := 0;
3023
3024 l_inactive_sql_stmt VARCHAR2(4000);
3025 l_active_sql_stmt VARCHAR2(4000);
3026
3027
3028 cursor l_inactive_cursor is
3029 select
3030 '
3031 update AP_POL_LINES pl
3032 set pl.STATUS = :c_INACTIVE
3033 where pl.POLICY_ID = :p_policy_id
3034 and pl.STATUS = :c_ACTIVE
3035 and ((pl.LOCATION_ID is not null
3036 and exists
3037 (select pso.LOCATION_ID
3038 from AP_POL_SCHEDULE_OPTIONS pso
3039 where pso.POLICY_ID = pl.POLICY_ID
3040 and pso.OPTION_TYPE = :c_LOCATION
3041 and pso.LOCATION_ID is not null
3042 and pso.LOCATION_ID = pl.LOCATION_ID
3043 and nvl(pso.END_DATE, SYSDATE+1) < SYSDATE
3044 )
3045 )
3046 or
3047 (pl.ROLE_ID is not null
3048 and exists
3049 (select pso.ROLE_ID
3050 from AP_POL_SCHEDULE_OPTIONS pso
3051 where pso.POLICY_ID = pl.POLICY_ID
3052 and pso.OPTION_TYPE = :c_EMPLOYEE_ROLE
3053 and pso.ROLE_ID is not null
3054 and pso.ROLE_ID = pl.ROLE_ID
3055 and nvl(pso.END_DATE, SYSDATE+1) < SYSDATE
3056 )
3057 )
3058 or
3059 (pl.CURRENCY_CODE is not null
3060 and exists
3061 (select pso.CURRENCY_CODE
3062 from AP_POL_SCHEDULE_OPTIONS pso
3063 where pso.POLICY_ID = pl.POLICY_ID
3064 and pso.OPTION_TYPE = :c_CURRENCY
3065 and pso.CURRENCY_CODE is not null
3066 and pso.CURRENCY_CODE = pl.CURRENCY_CODE
3067 and nvl(pso.END_DATE, SYSDATE+1) < SYSDATE
3068 )
3069 )
3070 or
3071 (pl.VEHICLE_CATEGORY is not null
3072 and exists
3073 (select pso.OPTION_CODE
3074 from AP_POL_SCHEDULE_OPTIONS pso
3075 where pso.POLICY_ID = pl.POLICY_ID
3076 and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
3077 and pso.OPTION_CODE is not null
3078 and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
3079 and nvl(pso.END_DATE, SYSDATE+1) < SYSDATE
3080 )
3081 )
3082 or
3083 (pl.VEHICLE_TYPE is not null
3084 and exists
3085 (select pso.OPTION_CODE
3086 from AP_POL_SCHEDULE_OPTIONS pso
3087 where pso.POLICY_ID = pl.POLICY_ID
3088 and pso.OPTION_TYPE = :c_VEHICLE_TYPE
3089 and pso.OPTION_CODE is not null
3090 and pso.OPTION_CODE = pl.VEHICLE_TYPE
3091 and nvl(pso.END_DATE, SYSDATE+1) < SYSDATE
3092 )
3093 )
3094 or
3095 (pl.FUEL_TYPE is not null
3096 and exists
3097 (select pso.OPTION_CODE
3098 from AP_POL_SCHEDULE_OPTIONS pso
3099 where pso.POLICY_ID = pl.POLICY_ID
3100 and pso.OPTION_TYPE = :c_FUEL_TYPE
3101 and pso.OPTION_CODE is not null
3102 and pso.OPTION_CODE = pl.FUEL_TYPE
3103 and nvl(pso.END_DATE, SYSDATE+1) < SYSDATE
3104 )
3105 )
3106 or
3107 (pl.RANGE_LOW is not null
3108 and exists
3109 (select pso.THRESHOLD
3110 from AP_POL_SCHEDULE_OPTIONS pso
3111 where pso.POLICY_ID = pl.POLICY_ID
3112 and (pso.OPTION_TYPE = :c_DISTANCE_THRESHOLD or pso.OPTION_TYPE = :c_TIME_THRESHOLD)
3113 and pso.THRESHOLD is not null
3114 and pso.THRESHOLD = pl.RANGE_LOW
3115 and nvl(pso.END_DATE, SYSDATE+1) < SYSDATE
3116 )
3117 )
3118 )
3119 '
3120 from sys.dual; /* l_inactive_cursor */
3121
3122
3123 cursor l_active_cursor is
3124 select
3125 '
3126 update AP_POL_LINES pl
3127 set pl.STATUS = :c_ACTIVE
3128 where pl.POLICY_ID = :p_policy_id
3129 and pl.STATUS = :c_INACTIVE
3130 and (((pl.LOCATION_ID is not null
3131 and exists
3132 (select pso.LOCATION_ID
3133 from AP_POL_SCHEDULE_OPTIONS pso
3134 where pso.POLICY_ID = pl.POLICY_ID
3135 and pso.OPTION_TYPE = :c_LOCATION
3136 and pso.LOCATION_ID is not null
3137 and pso.LOCATION_ID = pl.LOCATION_ID
3138 and nvl(pso.END_DATE, SYSDATE+1) > SYSDATE
3139 )
3140 ) or pl.LOCATION_ID is null
3141 )
3142 and
3143 ((pl.ROLE_ID is not null
3144 and exists
3145 (select pso.ROLE_ID
3146 from AP_POL_SCHEDULE_OPTIONS pso
3147 where pso.POLICY_ID = pl.POLICY_ID
3148 and pso.OPTION_TYPE = :c_EMPLOYEE_ROLE
3149 and pso.ROLE_ID is not null
3150 and pso.ROLE_ID = pl.ROLE_ID
3151 and nvl(pso.END_DATE, SYSDATE+1) > SYSDATE
3152 )
3153 ) or pl.ROLE_ID is null
3154 )
3155 and
3156 ((pl.CURRENCY_CODE is not null
3157 and
3158 (exists
3159 (select pso.CURRENCY_CODE
3160 from AP_POL_SCHEDULE_OPTIONS pso
3161 where pso.POLICY_ID = pl.POLICY_ID
3162 and pso.OPTION_TYPE = :c_CURRENCY
3163 and pso.CURRENCY_CODE is not null
3164 and pso.CURRENCY_CODE = pl.CURRENCY_CODE
3165 and nvl(pso.END_DATE, SYSDATE+1) > SYSDATE
3166 )
3167 or exists
3168 (select ph.CURRENCY_CODE
3169 from AP_POL_HEADERS ph
3170 where ph.POLICY_ID = pl.POLICY_ID
3171 and ((ph.CURRENCY_CODE is not null and ph.CURRENCY_CODE = pl.CURRENCY_CODE)
3172 or
3173 (ph.CURRENCY_CODE is null and ph.CURRENCY_PREFERENCE <> :c_SRC)))
3174 )
3175 )
3176 or pl.CURRENCY_CODE is null
3177 )
3178 and
3179 ((pl.VEHICLE_CATEGORY is not null
3180 and exists
3181 (select pso.OPTION_CODE
3182 from AP_POL_SCHEDULE_OPTIONS pso
3183 where pso.POLICY_ID = pl.POLICY_ID
3184 and pso.OPTION_TYPE = :c_VEHICLE_CATEGORY
3185 and pso.OPTION_CODE is not null
3186 and pso.OPTION_CODE = pl.VEHICLE_CATEGORY
3187 and nvl(pso.END_DATE, SYSDATE+1) > SYSDATE
3188 )
3189 ) or pl.VEHICLE_CATEGORY is null
3190 )
3191 and
3192 ((pl.VEHICLE_TYPE is not null
3193 and exists
3194 (select pso.OPTION_CODE
3195 from AP_POL_SCHEDULE_OPTIONS pso
3196 where pso.POLICY_ID = pl.POLICY_ID
3197 and pso.OPTION_TYPE = :c_VEHICLE_TYPE
3198 and pso.OPTION_CODE is not null
3199 and pso.OPTION_CODE = pl.VEHICLE_TYPE
3200 and nvl(pso.END_DATE, SYSDATE+1) > SYSDATE
3201 ) or pl.VEHICLE_TYPE is null
3202 )
3203 )
3204 and
3205 ((pl.FUEL_TYPE is not null
3206 and exists
3207 (select pso.OPTION_CODE
3208 from AP_POL_SCHEDULE_OPTIONS pso
3209 where pso.POLICY_ID = pl.POLICY_ID
3210 and pso.OPTION_TYPE = :c_FUEL_TYPE
3211 and pso.OPTION_CODE is not null
3212 and pso.OPTION_CODE = pl.FUEL_TYPE
3213 and nvl(pso.END_DATE, SYSDATE+1) > SYSDATE
3214 )
3215 ) or pl.FUEL_TYPE is null
3216 )
3217 and
3218 ((pl.RANGE_LOW is not null
3219 and exists
3220 (select pso.THRESHOLD
3221 from AP_POL_SCHEDULE_OPTIONS pso
3222 where pso.POLICY_ID = pl.POLICY_ID
3223 and (pso.OPTION_TYPE = :c_DISTANCE_THRESHOLD or pso.OPTION_TYPE = :c_TIME_THRESHOLD)
3224 and pso.THRESHOLD is not null
3225 and pso.THRESHOLD = pl.RANGE_LOW
3226 and nvl(pso.END_DATE, SYSDATE+1) > SYSDATE
3227 )
3228 ) or pl.RANGE_LOW is null
3229 )
3230 )
3231 '
3232 from sys.dual; /* l_active_cursor */
3233
3234 BEGIN
3235
3236 --------------------------------------------------------------
3237 -- if option end dated then set Policy Line status to inactive
3238 --------------------------------------------------------------
3239 open l_inactive_cursor;
3240 fetch l_inactive_cursor into l_inactive_sql_stmt;
3241
3242 --------------
3243 -- open cursor
3244 --------------
3245 l_inactive_curref := DBMS_SQL.OPEN_CURSOR;
3246
3247 --------------
3248 -- parse cursor
3249 --------------
3250 DBMS_SQL.PARSE(l_inactive_curref,
3251 l_inactive_sql_stmt,
3252 DBMS_SQL.NATIVE);
3253
3254 --------------
3255 -- supply binds
3256 --------------
3257 DBMS_SQL.BIND_VARIABLE(l_inactive_curref, ':p_policy_id', p_policy_id);
3258 DBMS_SQL.BIND_VARIABLE(l_inactive_curref, ':c_INACTIVE', c_INACTIVE);
3259 DBMS_SQL.BIND_VARIABLE(l_inactive_curref, ':c_ACTIVE', c_ACTIVE);
3260 DBMS_SQL.BIND_VARIABLE(l_inactive_curref, ':c_LOCATION', c_LOCATION);
3261 DBMS_SQL.BIND_VARIABLE(l_inactive_curref, ':c_EMPLOYEE_ROLE', c_EMPLOYEE_ROLE);
3262 DBMS_SQL.BIND_VARIABLE(l_inactive_curref, ':c_CURRENCY', c_CURRENCY);
3263 DBMS_SQL.BIND_VARIABLE(l_inactive_curref, ':c_VEHICLE_CATEGORY', c_VEHICLE_CATEGORY);
3264 DBMS_SQL.BIND_VARIABLE(l_inactive_curref, ':c_VEHICLE_TYPE', c_VEHICLE_TYPE);
3265 DBMS_SQL.BIND_VARIABLE(l_inactive_curref, ':c_FUEL_TYPE', c_FUEL_TYPE);
3266 DBMS_SQL.BIND_VARIABLE(l_inactive_curref, ':c_DISTANCE_THRESHOLD', c_DISTANCE_THRESHOLD);
3267 DBMS_SQL.BIND_VARIABLE(l_inactive_curref, ':c_TIME_THRESHOLD', c_TIME_THRESHOLD);
3268
3269 --------------
3270 -- execute cursor
3271 --------------
3272 l_rows_inactivated := DBMS_SQL.EXECUTE(l_inactive_curref);
3273
3274 --------------
3275 -- close cursor
3276 --------------
3277 DBMS_SQL.CLOSE_CURSOR(l_inactive_curref);
3278
3279 close l_inactive_cursor;
3280
3281
3282 --------------------------------------------------------------
3283 -- reactivate inactive lines if end date updated
3284 --------------------------------------------------------------
3285 open l_active_cursor;
3286 fetch l_active_cursor into l_active_sql_stmt;
3287
3288 --------------
3289 -- open cursor
3290 --------------
3291 l_active_curref := DBMS_SQL.OPEN_CURSOR;
3292
3293 --------------
3294 -- parse cursor
3295 --------------
3296 DBMS_SQL.PARSE(l_active_curref,
3297 l_active_sql_stmt,
3298 DBMS_SQL.NATIVE);
3299
3300 --------------
3301 -- supply binds
3302 --------------
3303 DBMS_SQL.BIND_VARIABLE(l_active_curref, ':p_policy_id', p_policy_id);
3304 DBMS_SQL.BIND_VARIABLE(l_active_curref, ':c_INACTIVE', c_INACTIVE);
3305 DBMS_SQL.BIND_VARIABLE(l_active_curref, ':c_ACTIVE', c_ACTIVE);
3306 DBMS_SQL.BIND_VARIABLE(l_active_curref, ':c_LOCATION', c_LOCATION);
3307 DBMS_SQL.BIND_VARIABLE(l_active_curref, ':c_EMPLOYEE_ROLE', c_EMPLOYEE_ROLE);
3308 DBMS_SQL.BIND_VARIABLE(l_active_curref, ':c_CURRENCY', c_CURRENCY);
3309 DBMS_SQL.BIND_VARIABLE(l_active_curref, ':c_SRC', c_SRC);
3310 DBMS_SQL.BIND_VARIABLE(l_active_curref, ':c_VEHICLE_CATEGORY', c_VEHICLE_CATEGORY);
3311 DBMS_SQL.BIND_VARIABLE(l_active_curref, ':c_VEHICLE_TYPE', c_VEHICLE_TYPE);
3312 DBMS_SQL.BIND_VARIABLE(l_active_curref, ':c_FUEL_TYPE', c_FUEL_TYPE);
3313 DBMS_SQL.BIND_VARIABLE(l_active_curref, ':c_DISTANCE_THRESHOLD', c_DISTANCE_THRESHOLD);
3314 DBMS_SQL.BIND_VARIABLE(l_active_curref, ':c_TIME_THRESHOLD', c_TIME_THRESHOLD);
3315
3316 --------------
3317 -- execute cursor
3318 --------------
3319 l_rows_activated := DBMS_SQL.EXECUTE(l_active_curref);
3320
3321 --------------
3322 -- close cursor
3323 --------------
3324 DBMS_SQL.CLOSE_CURSOR(l_active_curref);
3325
3326 close l_active_cursor;
3327
3328 EXCEPTION
3329 WHEN OTHERS THEN
3330 raise;
3331 END updateInactivePolicyLines;
3332
3333
3334 /*========================================================================
3335 | PUBLIC PROCEDURE duplicatePolicyLines
3336 |
3337 | DESCRIPTION
3338 | Duplicates Policy Lines from one Policy Schedule Period to another
3339 |
3340 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3341 |
3342 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
3343 |
3344 | PARAMETERS
3345 | p_user_id IN User Identifier
3346 | p_from_policy_id IN Policy Identifier to duplicate From
3347 | p_from_schedule_period_id IN Policy Schedule Period Identifier to duplicate From
3348 | p_to_policy_id IN Policy Identifier to duplicate To
3349 | p_to_schedule_period_id IN Policy Schedule Period Identifier to duplicate To
3350 |
3351 | MODIFICATION HISTORY
3352 | Date Author Description of Changes
3353 | 16-May-2002 R Langi Created
3354 |
3355 *=======================================================================*/
3356 PROCEDURE duplicatePolicyLines(p_user_id IN NUMBER,
3357 p_from_policy_id IN ap_pol_headers.policy_id%TYPE,
3358 p_from_schedule_period_id IN ap_pol_schedule_periods.schedule_period_id%TYPE,
3359 p_to_policy_id IN ap_pol_headers.policy_id%TYPE,
3360 p_to_schedule_period_id IN ap_pol_schedule_periods.schedule_period_id%TYPE) IS
3361
3362 l_duplicate_curref INTEGER;
3363 l_rows_duplicated NUMBER := 0;
3364
3365 l_duplicate_sql_stmt VARCHAR2(4000);
3366
3367 cursor l_duplicate_cursor is
3368 select
3369 '
3370 insert into AP_POL_LINES
3371 (
3372 POLICY_LINE_ID,
3373 POLICY_ID,
3374 SCHEDULE_PERIOD_ID,
3375 LOCATION_ID,
3376 ROLE_ID,
3377 CURRENCY_CODE,
3378 MEAL_LIMIT,
3379 RATE,
3380 TOLERANCE,
3381 TICKET_CLASS_DOMESTIC,
3382 TICKET_CLASS_INTERNATIONAL,
3383 VEHICLE_CATEGORY,
3384 VEHICLE_TYPE,
3385 FUEL_TYPE,
3386 RANGE_LOW,
3387 RANGE_HIGH,
3388 CALCULATION_METHOD,
3389 MEALS_DEDUCTION,
3390 BREAKFAST_DEDUCTION,
3391 LUNCH_DEDUCTION,
3392 DINNER_DEDUCTION,
3393 ACCOMMODATION_ADJUSTMENT,
3394 ADDON_MILEAGE_RATE_CODE,
3395 RATE_PER_PASSENGER,
3396 RATE_TYPE_CODE,
3397 ONE_MEAL_DEDUCTION_AMT,
3398 TWO_MEALS_DEDUCTION_AMT,
3399 THREE_MEALS_DEDUCTION_AMT,
3400 NIGHT_RATE_TYPE_CODE,
3401 ACCOMMODATION_CALC_METHOD,
3402 START_OF_SEASON,
3403 END_OF_SEASON,
3404 MAX_LODGING_AMT,
3405 NO_GOVT_MEALS_AMT,
3406 PROP_MEALS_AMT,
3407 OFF_BASE_INC_AMT,
3408 FOOTNOTE_AMT,
3409 FOOTNOTE_RATE_AMT,
3410 MAX_PER_DIEM_AMT,
3411 EFFECTIVE_START_DATE,
3412 EFFECTIVE_END_DATE,
3413 STATUS,
3414 CREATION_DATE,
3415 CREATED_BY,
3416 LAST_UPDATE_DATE,
3417 LAST_UPDATED_BY
3418 )
3419 select
3420 AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
3421 :p_to_policy_id AS POLICY_ID,
3422 :p_to_schedule_period_id AS SCHEDULE_PERIOD_ID,
3423 LOCATION_ID,
3424 ROLE_ID,
3425 CURRENCY_CODE,
3426 MEAL_LIMIT,
3427 RATE,
3428 TOLERANCE,
3429 TICKET_CLASS_DOMESTIC,
3430 TICKET_CLASS_INTERNATIONAL,
3431 VEHICLE_CATEGORY,
3432 VEHICLE_TYPE,
3433 FUEL_TYPE,
3434 RANGE_LOW,
3435 RANGE_HIGH,
3436 CALCULATION_METHOD,
3437 MEALS_DEDUCTION,
3438 BREAKFAST_DEDUCTION,
3439 LUNCH_DEDUCTION,
3440 DINNER_DEDUCTION,
3441 ACCOMMODATION_ADJUSTMENT,
3442 ADDON_MILEAGE_RATE_CODE,
3443 RATE_PER_PASSENGER,
3444 RATE_TYPE_CODE,
3445 ONE_MEAL_DEDUCTION_AMT,
3446 TWO_MEALS_DEDUCTION_AMT,
3447 THREE_MEALS_DEDUCTION_AMT,
3448 NIGHT_RATE_TYPE_CODE,
3449 ACCOMMODATION_CALC_METHOD,
3450 START_OF_SEASON,
3451 END_OF_SEASON,
3452 MAX_LODGING_AMT,
3453 NO_GOVT_MEALS_AMT,
3454 PROP_MEALS_AMT,
3455 OFF_BASE_INC_AMT,
3456 FOOTNOTE_AMT,
3457 FOOTNOTE_RATE_AMT,
3458 MAX_PER_DIEM_AMT,
3459 EFFECTIVE_START_DATE,
3460 EFFECTIVE_END_DATE,
3461 decode(status, ''ACTIVE'', ''VALID'', ''VALID'', ''VALID'', :c_DUPLICATED) AS STATUS,
3462 sysdate AS CREATION_DATE,
3463 :p_user_id AS CREATED_BY,
3464 sysdate AS LAST_UPDATE_DATE,
3465 :p_user_id AS LAST_UPDATED_BY
3466 from
3467 AP_POL_LINES
3468 where POLICY_ID = :p_from_policy_id
3469 and SCHEDULE_PERIOD_ID = :p_from_schedule_period_id
3470 and PARENT_LINE_ID is null
3471 '
3472 from sys.dual; /* l_duplicate_cursor */
3473
3474
3475 BEGIN
3476
3477 open l_duplicate_cursor;
3478 fetch l_duplicate_cursor into l_duplicate_sql_stmt;
3479
3480 --------------
3481 -- open cursor
3482 --------------
3483 l_duplicate_curref := DBMS_SQL.OPEN_CURSOR;
3484
3485 --------------
3486 -- parse cursor
3487 --------------
3488 DBMS_SQL.PARSE(l_duplicate_curref,
3489 l_duplicate_sql_stmt,
3490 DBMS_SQL.NATIVE);
3491
3492 --------------
3493 -- supply binds
3494 --------------
3495 DBMS_SQL.BIND_VARIABLE(l_duplicate_curref, ':p_from_policy_id', p_from_policy_id);
3496 DBMS_SQL.BIND_VARIABLE(l_duplicate_curref, ':p_from_schedule_period_id', p_from_schedule_period_id);
3497 DBMS_SQL.BIND_VARIABLE(l_duplicate_curref, ':p_to_policy_id', p_to_policy_id);
3498 DBMS_SQL.BIND_VARIABLE(l_duplicate_curref, ':p_to_schedule_period_id', p_to_schedule_period_id);
3499 DBMS_SQL.BIND_VARIABLE(l_duplicate_curref, ':p_user_id', p_user_id);
3500 DBMS_SQL.BIND_VARIABLE(l_duplicate_curref, ':c_DUPLICATED', c_DUPLICATED);
3501
3502 --------------
3503 -- execute cursor
3504 --------------
3505 l_rows_duplicated := DBMS_SQL.EXECUTE(l_duplicate_curref);
3506
3507 --------------
3508 -- close cursor
3509 --------------
3510 DBMS_SQL.CLOSE_CURSOR(l_duplicate_curref);
3511
3512 close l_duplicate_cursor;
3513
3514 EXCEPTION
3515 WHEN OTHERS THEN
3516 raise;
3517 END duplicatePolicyLines;
3518
3519
3520
3521 /*========================================================================
3522 | PUBLIC PROCEDURE preservePolicyLine
3523 |
3524 | DESCRIPTION
3525 | Preserve a modified Active Policy Line
3526 |
3527 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3528 |
3529 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
3530 |
3531 | PARAMETERS
3532 | p_user_id IN User Identifier
3533 | p_policy_id IN Policy Identifier
3534 | p_schedule_period_id IN Policy Schedule Period Identifier
3535 | p_policy_line_id IN Policy Line Identifier to preserve
3536 |
3537 | MODIFICATION HISTORY
3538 | Date Author Description of Changes
3539 | 12-Aug-2002 R Langi Created
3540 |
3541 *=======================================================================*/
3542 PROCEDURE preservePolicyLine(p_user_id IN NUMBER,
3543 p_policy_id IN ap_pol_lines.policy_id%TYPE,
3544 p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE,
3545 p_policy_line_id IN ap_pol_lines.policy_line_id%TYPE) IS
3546
3547 l_preserve_count NUMBER := 0;
3548
3549 BEGIN
3550
3551 ---------------------------------------
3552 -- sanity check if modified Active policy
3553 -- line is already preserved
3554 ---------------------------------------
3555 select count(*)
3556 into l_preserve_count
3557 from AP_POL_LINES
3558 where parent_line_id = p_policy_line_id
3559 and policy_id = p_policy_id
3560 and schedule_period_id = p_schedule_period_id;
3561
3562 if (l_preserve_count > 0) then
3563 return;
3564 end if;
3565
3566 ---------------------------------------
3567 -- preserve modified Active policy line
3568 ---------------------------------------
3569 insert into AP_POL_LINES
3570 (
3571 PARENT_LINE_ID,
3572 POLICY_LINE_ID,
3573 POLICY_ID,
3574 SCHEDULE_PERIOD_ID,
3575 LOCATION_ID,
3576 ROLE_ID,
3577 CURRENCY_CODE,
3578 MEAL_LIMIT,
3579 RATE,
3580 TOLERANCE,
3581 TICKET_CLASS_DOMESTIC,
3582 TICKET_CLASS_INTERNATIONAL,
3583 VEHICLE_CATEGORY,
3584 VEHICLE_TYPE,
3585 FUEL_TYPE,
3586 RANGE_LOW,
3587 RANGE_HIGH,
3588 CALCULATION_METHOD,
3589 MEALS_DEDUCTION,
3590 BREAKFAST_DEDUCTION,
3591 LUNCH_DEDUCTION,
3592 DINNER_DEDUCTION,
3593 ACCOMMODATION_ADJUSTMENT,
3594 ADDON_MILEAGE_RATE_CODE,
3595 RATE_PER_PASSENGER,
3596 RATE_TYPE_CODE,
3597 ONE_MEAL_DEDUCTION_AMT,
3598 TWO_MEALS_DEDUCTION_AMT,
3599 THREE_MEALS_DEDUCTION_AMT,
3600 NIGHT_RATE_TYPE_CODE,
3601 ACCOMMODATION_CALC_METHOD,
3602 STATUS,
3603 CREATION_DATE,
3604 CREATED_BY,
3605 LAST_UPDATE_DATE,
3606 LAST_UPDATED_BY
3607 )
3608 select
3609 p_policy_line_id AS PARENT_LINE_ID,
3610 AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
3611 p_policy_id AS POLICY_ID,
3612 p_schedule_period_id AS SCHEDULE_PERIOD_ID,
3613 LOCATION_ID,
3614 ROLE_ID,
3615 CURRENCY_CODE,
3616 MEAL_LIMIT,
3617 RATE,
3618 TOLERANCE,
3619 TICKET_CLASS_DOMESTIC,
3620 TICKET_CLASS_INTERNATIONAL,
3621 VEHICLE_CATEGORY,
3622 VEHICLE_TYPE,
3623 FUEL_TYPE,
3624 RANGE_LOW,
3625 RANGE_HIGH,
3626 CALCULATION_METHOD,
3627 MEALS_DEDUCTION,
3628 BREAKFAST_DEDUCTION,
3629 LUNCH_DEDUCTION,
3630 DINNER_DEDUCTION,
3631 ACCOMMODATION_ADJUSTMENT,
3632 ADDON_MILEAGE_RATE_CODE,
3633 RATE_PER_PASSENGER,
3634 RATE_TYPE_CODE,
3635 ONE_MEAL_DEDUCTION_AMT,
3636 TWO_MEALS_DEDUCTION_AMT,
3637 THREE_MEALS_DEDUCTION_AMT,
3638 NIGHT_RATE_TYPE_CODE,
3639 ACCOMMODATION_CALC_METHOD,
3640 STATUS,
3641 CREATION_DATE,
3642 CREATED_BY,
3643 LAST_UPDATE_DATE,
3644 LAST_UPDATED_BY
3645 from
3646 AP_POL_LINES
3647 where POLICY_ID = p_policy_id
3648 and SCHEDULE_PERIOD_ID = p_schedule_period_id
3649 and POLICY_LINE_ID = p_policy_line_id;
3650
3651
3652 EXCEPTION
3653 WHEN OTHERS THEN
3654 raise;
3655 END preservePolicyLine;
3656
3657
3658 /*========================================================================
3659 | PUBLIC PROCEDURE archivePreservedPolicyLines
3660 |
3661 | DESCRIPTION
3662 | Archive and remove a preserved Active Policy Line after it has been reactivated
3663 |
3664 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3665 |
3666 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
3667 |
3668 | PARAMETERS
3669 | p_user_id IN User Identifier
3670 | p_policy_id IN Policy Identifier
3671 |
3672 | MODIFICATION HISTORY
3673 | Date Author Description of Changes
3674 | 12-Aug-2002 R Langi Created
3675 |
3676 *=======================================================================*/
3677 PROCEDURE archivePreservedPolicyLines(p_user_id IN NUMBER,
3678 p_policy_id IN ap_pol_lines.policy_id%TYPE) IS
3679
3680 BEGIN
3681
3682 ---------------------------------------
3683 -- archive preserved Active policy lines
3684 ---------------------------------------
3685 insert into AP_POL_LINES_HISTORY
3686 (
3687 POLICY_LINE_HISTORY_ID,
3688 POLICY_LINE_ID,
3689 SCHEDULE_PERIOD_ID,
3690 --CURRENCY_CODE, -- need to add CURRENCY_CODE to AP_POL_LINES_HISTORY because of LCR
3691 MEAL_LIMIT,
3692 RATE,
3693 TOLERANCE,
3694 TICKET_CLASS_DOMESTIC,
3695 TICKET_CLASS_INTERNATIONAL,
3696 CALCULATION_METHOD,
3697 MEALS_DEDUCTION,
3698 BREAKFAST_DEDUCTION,
3699 LUNCH_DEDUCTION,
3700 DINNER_DEDUCTION,
3701 ACCOMMODATION_ADJUSTMENT,
3702 ADDON_MILEAGE_RATE_CODE,
3703 RATE_PER_PASSENGER,
3704 RATE_TYPE_CODE,
3705 ONE_MEAL_DEDUCTION_AMT,
3706 TWO_MEALS_DEDUCTION_AMT,
3707 THREE_MEALS_DEDUCTION_AMT,
3708 NIGHT_RATE_TYPE_CODE,
3709 ACCOMMODATION_CALC_METHOD,
3710 CREATION_DATE,
3711 CREATED_BY,
3712 LAST_UPDATE_DATE,
3713 LAST_UPDATED_BY
3714 )
3715 select
3716 AP_POL_LINES_HISTORY_S.NEXTVAL AS POLICY_LINE_HISTORY_ID,
3717 PARENT_LINE_ID AS POLICY_LINE_ID,
3718 SCHEDULE_PERIOD_ID,
3719 --CURRENCY_CODE,
3720 MEAL_LIMIT,
3721 RATE,
3722 TOLERANCE,
3723 TICKET_CLASS_DOMESTIC,
3724 TICKET_CLASS_INTERNATIONAL,
3725 CALCULATION_METHOD,
3726 MEALS_DEDUCTION,
3727 BREAKFAST_DEDUCTION,
3728 LUNCH_DEDUCTION,
3729 DINNER_DEDUCTION,
3730 ACCOMMODATION_ADJUSTMENT,
3731 ADDON_MILEAGE_RATE_CODE,
3732 RATE_PER_PASSENGER,
3733 RATE_TYPE_CODE,
3734 ONE_MEAL_DEDUCTION_AMT,
3735 TWO_MEALS_DEDUCTION_AMT,
3736 THREE_MEALS_DEDUCTION_AMT,
3737 NIGHT_RATE_TYPE_CODE,
3738 ACCOMMODATION_CALC_METHOD,
3739 CREATION_DATE,
3740 CREATED_BY,
3741 LAST_UPDATE_DATE,
3742 LAST_UPDATED_BY
3743 from
3744 AP_POL_LINES
3745 where POLICY_ID = p_policy_id
3746 and PARENT_LINE_ID is not null;
3747
3748 ---------------------------------------
3749 -- remove preserved Active Policy Lines
3750 ---------------------------------------
3751 delete
3752 from
3753 AP_POL_LINES
3754 where POLICY_ID = p_policy_id
3755 and PARENT_LINE_ID is not null;
3756
3757 EXCEPTION
3758 WHEN OTHERS THEN
3759 raise;
3760 END archivePreservedPolicyLines;
3761
3762
3763 /*========================================================================
3764 | PUBLIC FUNCTION createSchedulePeriod
3765 |
3766 | DESCRIPTION
3767 | Creates a new Policy Schedule Period
3768 |
3769 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3770 |
3771 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
3772 |
3773 | PARAMETERS
3774 | p_user_id IN User Identifier
3775 | p_policy_id IN Policy Identifier
3776 | p_schedule_period_name IN Schedule Period Name
3777 | p_start_date IN Start Date
3778 | p_end_date IN End Date
3779 | p_rate_per_passenger IN Rate Per Passenger
3780 | p_min_days IN Minimum Number of Days
3781 | p_tolerance IN Tolerance
3782 | p_min_rate_per_period IN Minimum Rate per Period
3783 | p_max_breakfast_deduction IN Maximum Breakfast Deduction Allowed per Period
3784 | p_max_lunch_deduction IN Maximum Lunch Deduction Allowed per Period
3785 | p_max_dinner_deduction IN Maximum Dinner Deduction Allowed per Period
3786 | p_first_day_rate IN First day rate
3787 | p_last_day_rate IN Last day rate
3788 |
3789 | MODIFICATION HISTORY
3790 | Date Author Description of Changes
3791 | 16-May-2002 R Langi Created
3792 |
3793 *=======================================================================*/
3794 FUNCTION createSchedulePeriod(p_user_id IN NUMBER,
3795 p_policy_id IN ap_pol_schedule_periods.policy_id%TYPE,
3796 p_schedule_period_name IN ap_pol_schedule_periods.schedule_period_name%TYPE,
3797 p_start_date IN ap_pol_schedule_periods.start_date%TYPE,
3798 p_end_date IN ap_pol_schedule_periods.end_date%TYPE,
3799 p_rate_per_passenger IN ap_pol_schedule_periods.rate_per_passenger%TYPE,
3800 p_min_days IN ap_pol_schedule_periods.min_days%TYPE,
3801 p_tolerance IN ap_pol_schedule_periods.tolerance%TYPE,
3802 p_min_rate_per_period IN ap_pol_schedule_periods.min_rate_per_period%TYPE,
3803 p_max_breakfast_deduction IN ap_pol_schedule_periods.max_breakfast_deduction_amt%TYPE,
3804 p_max_lunch_deduction IN ap_pol_schedule_periods.max_lunch_deduction_amt%TYPE,
3805 p_max_dinner_deduction IN ap_pol_schedule_periods.max_dinner_deduction_amt%TYPE,
3806 p_first_day_rate IN ap_pol_schedule_periods.first_day_rate%TYPE,
3807 p_last_day_rate IN ap_pol_schedule_periods.last_day_rate%TYPE) RETURN ap_pol_schedule_periods.schedule_period_id%TYPE IS
3808
3809 l_schedule_period_id ap_pol_schedule_periods.schedule_period_id%TYPE;
3810
3811 BEGIN
3812
3813 select AP_POL_SCHEDULE_PERIODS_S.NEXTVAL
3814 into l_schedule_period_id
3815 from sys.dual;
3816
3817 insert into AP_POL_SCHEDULE_PERIODS
3818 (
3819 SCHEDULE_PERIOD_ID,
3820 SCHEDULE_PERIOD_NAME,
3821 POLICY_ID,
3822 START_DATE,
3823 END_DATE,
3824 RATE_PER_PASSENGER,
3825 MIN_DAYS,
3826 TOLERANCE,
3827 MIN_RATE_PER_PERIOD,
3828 MAX_BREAKFAST_DEDUCTION_AMT,
3829 MAX_LUNCH_DEDUCTION_AMT,
3830 MAX_DINNER_DEDUCTION_AMT,
3831 FIRST_DAY_RATE,
3832 LAST_DAY_RATE,
3833 CREATION_DATE,
3834 CREATED_BY,
3835 LAST_UPDATE_DATE,
3836 LAST_UPDATED_BY
3837 )
3838 select
3839 l_schedule_period_id AS SCHEDULE_PERIOD_ID,
3840 decode(p_schedule_period_name, null, fnd_message.GET_STRING('SQLAP','OIE_POL_PERIODS_NEW_PERIOD'), substrb(fnd_message.GET_STRING('SQLAP','OIE_POL_COPY_OF')||' '||p_schedule_period_name, 1, C_PolicyNameMaxLength)) AS SCHEDULE_PERIOD_NAME,
3841 p_policy_id AS POLICY_ID,
3842 p_start_date AS START_DATE,
3843 p_end_date AS END_DATE,
3844 p_rate_per_passenger AS RATE_PER_PASSENGER,
3845 p_min_days AS MIN_DAYS,
3846 p_tolerance AS TOLERANCE,
3847 p_min_rate_per_period as MIN_RATE_PER_PERIOD,
3848 p_max_breakfast_deduction as MAX_BREAKFAST_DEDUCTION,
3849 p_max_lunch_deduction as MAX_LUNCH_DEDUCTION,
3850 p_max_dinner_deduction as MAX_DINNER_DEDUCTION,
3851 p_first_day_rate as FIRST_DAY_RATE,
3852 p_last_day_rate as LAST_DAY_RATE,
3853 sysdate AS CREATION_DATE,
3854 p_user_id AS CREATED_BY,
3855 sysdate AS LAST_UPDATE_DATE,
3856 p_user_id AS LAST_UPDATED_BY
3857 from
3858 sys.dual;
3859
3860 return l_schedule_period_id;
3861
3862 EXCEPTION
3863 WHEN OTHERS THEN
3864 raise;
3865 END createSchedulePeriod;
3866
3867
3868 /*========================================================================
3869 | PUBLIC FUNCTION massUpdateValue
3870 |
3871 | DESCRIPTION
3872 | Using a rounding rule and percentage to update by, a value is returned
3873 |
3874 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3875 |
3876 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
3877 |
3878 | PARAMETERS
3879 | p_value IN Value to perform an update on
3880 | p_update_by IN Percentage to update the value by
3881 | p_rounding_rule IN Rounding rule to use after the value has been updated
3882 |
3883 | MODIFICATION HISTORY
3884 | Date Author Description of Changes
3885 | 16-May-2002 R Langi Created
3886 |
3887 *=======================================================================*/
3888 FUNCTION massUpdateValue(p_value IN NUMBER,
3889 p_update_by IN NUMBER,
3890 p_rounding_rule IN VARCHAR2) RETURN NUMBER IS
3891
3892 l_new_value NUMBER := 0;
3893
3894 BEGIN
3895
3896 select decode(p_rounding_rule,
3897 '1_DECIMALS', nvl(p_value, 0) + ROUND(nvl(p_value, 0) * (p_update_by/100), 1),
3898 '2_DECIMALS', nvl(p_value, 0) + ROUND(nvl(p_value, 0) * (p_update_by/100), 2),
3899 '3_DECIMALS', nvl(p_value, 0) + ROUND(nvl(p_value, 0) * (p_update_by/100), 3),
3900 nvl(p_value, 0) + ROUND(nvl(p_value, 0) * (p_update_by/100)))
3901 into l_new_value
3902 from sys.dual;
3903
3904 if (p_rounding_rule = c_NEAREST_FIVE) then
3905 if (mod(l_new_value, 5) >= 2.5) then
3906 l_new_value := l_new_value - mod(l_new_value, 5) + 5;
3907 else
3908 l_new_value := l_new_value - mod(l_new_value, 5);
3909 end if;
3910 end if;
3911 if (p_rounding_rule = c_NEAREST_TEN) then
3912 if (mod(l_new_value, 10) >= 5) then
3913 l_new_value := l_new_value - mod(l_new_value, 10) + 10;
3914 else
3915 l_new_value := l_new_value - mod(l_new_value, 10);
3916 end if;
3917 end if;
3918
3919 return l_new_value;
3920
3921 EXCEPTION
3922 WHEN no_data_found THEN
3923 return(null);
3924 WHEN OTHERS THEN
3925 raise;
3926 END massUpdateValue;
3927
3928 /*========================================================================
3929 | PUBLIC PROCEDURE duplicatePolicy
3930 |
3931 | DESCRIPTION
3932 | Duplicates a Policy Schedule (General Information/Options/Periods/Lines)
3933 |
3934 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
3935 | createSchedulePeriod
3936 | duplicatePolicyLines
3937 |
3938 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
3939 | duplicatePolicyHeader
3940 | duplicatePolicyScheduleOptions
3941 |
3942 | PARAMETERS
3943 | p_user_id IN User Identifier
3944 | p_from_policy_id IN Policy Identifier to duplicate From
3945 | p_new_policy_id IN New Policy Identifier containing the duplicated Policy
3946 |
3947 | MODIFICATION HISTORY
3948 | Date Author Description of Changes
3949 | 16-May-2002 R Langi Created
3950 | 09-Mar-2004 sasaxena Bug 2847928: clear out schedule end
3951 | date when a schedule is duplicated.
3952 |
3953 *=======================================================================*/
3954 PROCEDURE duplicatePolicy(p_user_id IN NUMBER,
3955 p_from_policy_id IN ap_pol_headers.policy_id%TYPE,
3956 p_new_policy_id OUT NOCOPY ap_pol_headers.policy_id%TYPE) IS
3957
3958 l_duplicate_header_sql_stmt VARCHAR2(4000);
3959 l_duplicate_options_sql_stmt VARCHAR2(4000);
3960
3961 l_from_policy_id ap_pol_headers.policy_id%TYPE;
3962 l_to_policy_id ap_pol_headers.policy_id%TYPE;
3963
3964 l_from_schedule_period_id ap_pol_schedule_periods.schedule_period_id%TYPE;
3965 l_to_schedule_period_id ap_pol_schedule_periods.schedule_period_id%TYPE;
3966
3967 l_schedule_period_name ap_pol_schedule_periods.schedule_period_name%TYPE;
3968 l_start_date ap_pol_schedule_periods.start_date%TYPE;
3969 l_end_date ap_pol_schedule_periods.end_date%TYPE;
3970 l_rate_per_passenger ap_pol_schedule_periods.rate_per_passenger%TYPE;
3971 l_min_days ap_pol_schedule_periods.min_days%TYPE;
3972 l_tolerance ap_pol_schedule_periods.tolerance%TYPE;
3973 l_min_rate_per_period ap_pol_schedule_periods.min_rate_per_period%TYPE;
3974 l_max_breakfast_deduction ap_pol_schedule_periods.max_breakfast_deduction_amt%TYPE;
3975 l_max_lunch_deduction ap_pol_schedule_periods.max_lunch_deduction_amt%TYPE;
3976 l_max_dinner_deduction ap_pol_schedule_periods.max_dinner_deduction_amt%TYPE;
3977 l_first_day_rate ap_pol_schedule_periods.first_day_rate%TYPE;
3978 l_last_day_rate ap_pol_schedule_periods.last_day_rate%TYPE;
3979
3980 cursor l_duplicate_periods_cursor
3981 is
3982 select
3983 SCHEDULE_PERIOD_ID,
3984 SCHEDULE_PERIOD_NAME,
3985 START_DATE,
3986 END_DATE,
3987 RATE_PER_PASSENGER,
3988 MIN_DAYS,
3989 TOLERANCE,
3990 MIN_RATE_PER_PERIOD,
3991 MAX_BREAKFAST_DEDUCTION_AMT,
3992 MAX_LUNCH_DEDUCTION_AMT,
3993 MAX_DINNER_DEDUCTION_AMT,
3994 FIRST_DAY_RATE,
3995 LAST_DAY_RATE
3996 from AP_POL_SCHEDULE_PERIODS
3997 where POLICY_ID = p_from_policy_id; /* l_duplicate_periods_cursor */
3998
3999
4000
4001 PROCEDURE duplicatePolicyHeader(p_user_id IN NUMBER,
4002 p_from_policy_id IN ap_pol_headers.policy_id%TYPE,
4003 p_to_policy_id OUT NOCOPY ap_pol_headers.policy_id%TYPE) IS
4004
4005 BEGIN
4006
4007 select AP_POL_HEADERS_S.NEXTVAL
4008 into p_to_policy_id
4009 from sys.dual;
4010
4011
4012 insert into AP_POL_HEADERS
4013 (
4014 POLICY_ID,
4015 CATEGORY_CODE,
4016 POLICY_NAME,
4017 DESCRIPTION,
4018 CURRENCY_CODE,
4019 BUSINESS_GROUP_ID,
4020 JOB_GROUP_ID,
4021 ROLE_CODE,
4022 DISTANCE_UOM,
4023 CURRENCY_PREFERENCE,
4024 ALLOW_RATE_CONVERSION_CODE,
4025 DAILY_LIMITS_CODE,
4026 START_DATE,
4027 END_DATE,
4028 DISTANCE_THRESHOLDS_FLAG,
4029 VEHICLE_CATEGORY_FLAG,
4030 VEHICLE_TYPE_FLAG,
4031 FUEL_TYPE_FLAG,
4032 PASSENGERS_FLAG,
4033 EMPLOYEE_ROLE_FLAG,
4034 TIME_BASED_ENTRY_FLAG,
4035 FREE_MEALS_FLAG,
4036 FREE_ACCOMMODATIONS_FLAG,
4037 TOLERANCE_LIMITS_FLAG,
4038 DAILY_LIMITS_FLAG,
4039 LOCATION_FLAG,
4040 TOLERANCE_LIMIT_CODE,
4041 FREE_MEALS_CODE,
4042 FREE_ACCOMMODATIONS_CODE,
4043 DAY_PERIOD_CODE,
4044 ADDON_MILEAGE_RATES_FLAG,
4045 SCHEDULE_TYPE_CODE,
4046 MIN_TRIP_DURATION,
4047 SAME_DAY_RATE_CODE,
4048 NIGHT_RATES_CODE,
4049 NIGHT_RATE_ELIGIBILITY,
4050 NIGHT_RATE_START_TIME,
4051 NIGHT_RATE_END_TIME,
4052 MULTI_DEST_RULE_CODE,
4053 MULTI_DEST_START_TIME,
4054 MULTI_DEST_END_TIME,
4055 PER_DIEM_TYPE_CODE,
4056 SOURCE,
4057 RATE_PERIOD_TYPE_CODE,
4058 MEALS_TYPE_CODE,
4059 ALLOWANCE_TIME_RULE_CODE,
4060 BREAKFAST_START_TIME,
4061 BREAKFAST_END_TIME,
4062 LUNCH_START_TIME,
4063 LUNCH_END_TIME,
4064 DINNER_START_TIME,
4065 DINNER_END_TIME,
4066 USE_MAX_DEST_RATE_FLAG,
4067 CREATION_DATE,
4068 CREATED_BY,
4069 LAST_UPDATE_LOGIN,
4070 LAST_UPDATE_DATE,
4071 LAST_UPDATED_BY
4072 )
4073 select
4074 p_to_policy_id AS POLICY_ID,
4075 CATEGORY_CODE,
4076 substrb(fnd_message.GET_STRING('SQLAP','OIE_POL_COPY_OF')||' '||POLICY_NAME, 1, C_PolicyNameMaxLength) AS POLICY_NAME,
4077 DESCRIPTION,
4078 CURRENCY_CODE,
4079 BUSINESS_GROUP_ID,
4080 JOB_GROUP_ID,
4081 ROLE_CODE,
4082 DISTANCE_UOM,
4083 CURRENCY_PREFERENCE,
4084 ALLOW_RATE_CONVERSION_CODE,
4085 DAILY_LIMITS_CODE,
4086 START_DATE,
4087 null, -- Bug 2847928
4088 DISTANCE_THRESHOLDS_FLAG,
4089 VEHICLE_CATEGORY_FLAG,
4090 VEHICLE_TYPE_FLAG,
4091 FUEL_TYPE_FLAG,
4092 PASSENGERS_FLAG,
4093 EMPLOYEE_ROLE_FLAG,
4094 TIME_BASED_ENTRY_FLAG,
4095 FREE_MEALS_FLAG,
4096 FREE_ACCOMMODATIONS_FLAG,
4097 TOLERANCE_LIMITS_FLAG,
4098 DAILY_LIMITS_FLAG,
4099 LOCATION_FLAG,
4100 TOLERANCE_LIMIT_CODE,
4101 FREE_MEALS_CODE,
4102 FREE_ACCOMMODATIONS_CODE,
4103 DAY_PERIOD_CODE,
4104 ADDON_MILEAGE_RATES_FLAG,
4105 SCHEDULE_TYPE_CODE,
4106 MIN_TRIP_DURATION,
4107 SAME_DAY_RATE_CODE,
4108 NIGHT_RATES_CODE,
4109 NIGHT_RATE_ELIGIBILITY,
4110 NIGHT_RATE_START_TIME,
4111 NIGHT_RATE_END_TIME,
4112 MULTI_DEST_RULE_CODE,
4113 MULTI_DEST_START_TIME,
4114 MULTI_DEST_END_TIME,
4115 PER_DIEM_TYPE_CODE,
4116 SOURCE,
4117 RATE_PERIOD_TYPE_CODE,
4118 MEALS_TYPE_CODE,
4119 ALLOWANCE_TIME_RULE_CODE,
4120 BREAKFAST_START_TIME,
4121 BREAKFAST_END_TIME,
4122 LUNCH_START_TIME,
4123 LUNCH_END_TIME,
4124 DINNER_START_TIME,
4125 DINNER_END_TIME,
4126 USE_MAX_DEST_RATE_FLAG,
4127 sysdate AS CREATION_DATE,
4128 p_user_id AS CREATED_BY,
4129 null AS LAST_UPDATE_LOGIN,
4130 sysdate AS LAST_UPDATE_DATE,
4131 p_user_id AS LAST_UPDATED_BY
4132 from
4133 AP_POL_HEADERS
4134 where POLICY_ID = p_from_policy_id;
4135
4136 EXCEPTION
4137 WHEN OTHERS THEN
4138 raise;
4139 END duplicatePolicyHeader;
4140
4141
4142 PROCEDURE duplicatePolicyScheduleOptions(p_user_id IN NUMBER,
4143 p_from_policy_id IN ap_pol_headers.policy_id%TYPE,
4144 p_to_policy_id IN ap_pol_headers.policy_id%TYPE) IS
4145
4146 BEGIN
4147
4148 insert into AP_POL_SCHEDULE_OPTIONS
4149 (
4150 SCHEDULE_OPTION_ID,
4151 POLICY_ID,
4152 OPTION_TYPE,
4153 OPTION_CODE,
4154 THRESHOLD,
4155 ROLE_ID,
4156 LOCATION_ID,
4157 CURRENCY_CODE,
4158 END_DATE,
4159 VEHICLE_TYPE_CODE,
4160 FUEL_TYPE_CODE,
4161 RATE_TYPE_CODE,
4162 STATUS,
4163 CREATION_DATE,
4164 CREATED_BY,
4165 LAST_UPDATE_LOGIN,
4166 LAST_UPDATE_DATE,
4167 LAST_UPDATED_BY
4168 )
4169 select
4170 AP_POL_SCHEDULE_OPTIONS_S.NEXTVAL AS SCHEDULE_OPTION_ID,
4171 p_to_policy_id AS POLICY_ID,
4172 OPTION_TYPE,
4173 OPTION_CODE,
4174 THRESHOLD,
4175 ROLE_ID,
4176 LOCATION_ID,
4177 CURRENCY_CODE,
4178 END_DATE,
4179 VEHICLE_TYPE_CODE,
4180 FUEL_TYPE_CODE,
4181 RATE_TYPE_CODE,
4182 STATUS,
4183 sysdate AS CREATION_DATE,
4184 p_user_id AS CREATED_BY,
4185 null AS LAST_UPDATE_LOGIN,
4186 sysdate AS LAST_UPDATE_DATE,
4187 p_user_id AS LAST_UPDATED_BY
4188 from
4189 AP_POL_SCHEDULE_OPTIONS
4190 where POLICY_ID = p_from_policy_id;
4191
4192 EXCEPTION
4193 WHEN OTHERS THEN
4194 raise;
4195 END duplicatePolicyScheduleOptions;
4196
4197
4198 BEGIN
4199
4200 l_from_policy_id := p_from_policy_id;
4201
4202 duplicatePolicyHeader(p_user_id => p_user_id,
4203 p_from_policy_id => l_from_policy_id,
4204 p_to_policy_id => l_to_policy_id);
4205
4206 duplicatePolicyScheduleOptions(p_user_id => p_user_id,
4207 p_from_policy_id => l_from_policy_id,
4208 p_to_policy_id => l_to_policy_id);
4209
4210
4211 open l_duplicate_periods_cursor;
4212 loop
4213 fetch l_duplicate_periods_cursor into
4214 l_from_schedule_period_id,
4215 l_schedule_period_name,
4216 l_start_date,
4217 l_end_date,
4218 l_rate_per_passenger,
4219 l_min_days,
4220 l_tolerance,
4221 l_min_rate_per_period,
4222 l_max_breakfast_deduction,
4223 l_max_lunch_deduction,
4224 l_max_dinner_deduction,
4225 l_first_day_rate,
4226 l_last_day_rate;
4227
4228 exit when l_duplicate_periods_cursor%NOTFOUND;
4229
4230 l_to_schedule_period_id := createSchedulePeriod(p_user_id => p_user_id,
4231 p_policy_id => l_to_policy_id,
4232 p_schedule_period_name => l_schedule_period_name,
4233 p_start_date => l_start_date,
4234 p_end_date => l_end_date,
4235 p_rate_per_passenger => l_rate_per_passenger,
4236 p_min_days => l_min_days,
4237 p_tolerance => l_tolerance,
4238 p_min_rate_per_period => l_min_rate_per_period,
4239 p_max_breakfast_deduction => l_max_breakfast_deduction,
4240 p_max_lunch_deduction => l_max_lunch_deduction,
4241 p_max_dinner_deduction => l_max_dinner_deduction,
4242 p_first_day_rate => l_first_day_rate,
4243 p_last_day_rate => l_last_day_rate);
4244
4245 duplicatePolicyLines(p_user_id => p_user_id,
4246 p_from_policy_id => l_from_policy_id,
4247 p_from_schedule_period_id => l_from_schedule_period_id,
4248 p_to_policy_id => l_to_policy_id,
4249 p_to_schedule_period_id => l_to_schedule_period_id);
4250
4251
4252 end loop;
4253 close l_duplicate_periods_cursor;
4254
4255 p_new_policy_id := l_to_policy_id;
4256
4257 EXCEPTION
4258 WHEN OTHERS THEN
4259 raise;
4260 END duplicatePolicy;
4261
4262
4263 /*========================================================================
4264 | PUBLIC FUNCTION active_option_exists
4265 |
4266 | DESCRIPTION
4267 | Checks whether a active schedule option exists for a option
4268 |
4269 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4270 |
4271 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4272 |
4273 | PARAMETERS
4274 | p_option_type IN Option type, required
4275 | p_option_code IN Option code, optional
4276 | p_threshold IN Threshold, optional
4277 | p_role_id IN Role Id, optional
4278 | p_location_id IN Location Id, optional
4279 | p_currency_code IN Currency Code, optional
4280 | p_end_date IN End Date, optional
4281 |
4282 | RETURNS
4283 | Y If active option exists
4284 | N If active option does not exist
4285 |
4286 | MODIFICATION HISTORY
4287 | Date Author Description of Changes
4288 | 26-Jun-2002 J Rautiainen Created
4289 |
4290 *=======================================================================*/
4291 FUNCTION active_option_exists(p_option_type IN VARCHAR2,
4292 p_option_code IN VARCHAR2,
4293 p_threshold IN NUMBER,
4294 p_role_id IN NUMBER,
4295 p_location_id IN NUMBER,
4296 p_currency_code IN VARCHAR2,
4297 p_end_date IN DATE) RETURN VARCHAR2 IS
4298 CURSOR active_cur IS
4299 select schedule_option_id
4300 from ap_pol_schedule_options
4301 where option_type = p_option_type
4302 and NVL(option_code,9.99E125) = NVL(p_option_code,9.99E125)
4303 and NVL(threshold,9.99E125) = NVL(p_threshold,9.99E125)
4304 and NVL(role_id,9.99E125) = NVL(p_role_id,9.99E125)
4305 and NVL(location_id,9.99E125) = NVL(p_location_id,9.99E125)
4306 and NVL(currency_code,chr(0)) = NVL(p_currency_code, chr(0))
4307 and NVL(end_date,TO_DATE('1','j')) >= DECODE(p_end_date,
4308 null, TO_DATE('1','j'),
4309 DECODE(end_date,
4310 null, TO_DATE('1','j'),
4311 p_end_date));
4312
4313
4314 active_rec active_cur%ROWTYPE;
4315
4316 BEGIN
4317 OPEN active_cur;
4318 FETCH active_cur INTO active_rec;
4319 IF active_cur%FOUND THEN
4320 CLOSE active_cur;
4321 RETURN 'Y';
4322 END IF;
4323
4324 CLOSE active_cur;
4325 RETURN 'N';
4326
4327 END active_option_exists;
4328
4329 /*========================================================================
4330 | PUBLIC PROCEDURE end_date_active_loc_options
4331 |
4332 | DESCRIPTION
4333 | If locations are end dated on location definitions then corresponding
4334 | active locations on schedule options should be end dated provided the
4335 | location option's end date is null or later than the defined end date.
4336 |
4337 | NOTES
4338 | Created vide bug 2560275
4339 |
4340 | MODIFICATION HISTORY
4341 | Date Author Description of Changes
4342 | 05-Feb-2004 V Nama Created
4343 |
4344 *=======================================================================*/
4345 PROCEDURE end_date_active_loc_options IS
4346
4347 CURSOR loc_def_cur IS
4348 select location_id, end_date
4349 from ap_pol_locations_vl
4350 where end_date is not null;
4351
4352 l_location_id NUMBER;
4353 l_end_date DATE;
4354 loc_def_rec loc_def_cur%ROWTYPE;
4355
4356 BEGIN
4357
4358 FOR loc_def_rec IN loc_def_cur LOOP
4359
4360 l_location_id := loc_def_rec.location_id;
4361 l_end_date := loc_def_rec.end_date;
4362
4363 IF ( l_end_date is not null )
4364 THEN
4365
4366 update ap_pol_schedule_options
4367 set end_date = l_end_date
4368 where option_type = 'LOCATION'
4369 and location_id = l_location_id
4370 and NVL(end_date,TO_DATE('1','j')) >=
4371 DECODE(l_end_date,
4372 null, TO_DATE('1','j'),
4373 DECODE(end_date,
4374 null, TO_DATE('1','j'),
4375 l_end_date));
4376 END IF;
4377 END LOOP;
4378
4379 END end_date_active_loc_options;
4380
4381 /*========================================================================
4382 | PUBLIC FUNCTION does_location_exist
4383 |
4384 | DESCRIPTION
4385 | Checks whether a locations exists
4386 |
4387 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4388 |
4389 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4390 |
4391 | PARAMETERS
4392 | NONE
4393 |
4394 | RETURNS
4395 | Y If locations exist
4396 | N If locations does not exist
4397 |
4398 | MODIFICATION HISTORY
4399 | Date Author Description of Changes
4400 | 26-Jun-2002 J Rautiainen Created
4401 |
4402 *=======================================================================*/
4403 FUNCTION does_location_exist RETURN VARCHAR2 IS
4404
4405 CURSOR location_cur IS
4406 select 1 location_count
4407 from dual
4408 where exists
4409 (select 1
4410 from ap_pol_locations_b);
4411
4412 location_rec location_cur%ROWTYPE;
4413 BEGIN
4414
4415 OPEN location_cur;
4416 FETCH location_cur INTO location_rec;
4417 CLOSE location_cur;
4418
4419 IF location_rec.location_count = 1 THEN
4420 RETURN 'Y';
4421 ELSE
4422 RETURN 'N';
4423 END IF;
4424
4425 EXCEPTION
4426 WHEN no_data_found THEN
4427 return('N');
4428 WHEN OTHERS THEN
4429 raise;
4430 END does_location_exist;
4431
4432 /*========================================================================
4433 | PUBLIC PROCEDURE status_saved_sched_opts
4434 |
4435 | DESCRIPTION
4436 | This procedure sets status of schedule options to 'SAVED' for
4437 | the given policy_id
4438 |
4439 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4440 | Called from BC4J
4441 |
4442 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4443 |
4444 | PARAMETERS
4445 | p_policy_id IN Policy Id
4446 |
4447 | MODIFICATION HISTORY
4448 | Date Author Description of Changes
4449 | 17-JUL-2002 Mohammad Shoaib Jamall Created
4450 |
4451 *=======================================================================*/
4452 PROCEDURE status_saved_sched_opts(p_policy_id IN NUMBER) IS
4453 BEGIN
4454 IF p_policy_id IS NOT NULL THEN
4455 update AP_POL_SCHEDULE_OPTIONS set STATUS = 'SAVED' where POLICY_ID = p_policy_id and nvl(STATUS, '~') <> 'ACTIVE';
4456 END IF;
4457 END status_saved_sched_opts;
4458
4459 /*========================================================================
4460 | PUBLIC PROCEDURE status_active_sched_opts
4461 |
4462 | DESCRIPTION
4463 | This procedure sets status of schedule options to 'ACTIVE' for
4464 | the given policy_id
4465 |
4466 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4467 | Called from BC4J
4468 |
4469 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4470 |
4471 | PARAMETERS
4472 | p_policy_id IN Policy Id
4473 |
4474 | MODIFICATION HISTORY
4475 | Date Author Description of Changes
4476 | 17-JUL-2002 Mohammad Shoaib Jamall Created
4477 |
4478 *=======================================================================*/
4479 PROCEDURE status_active_sched_opts(p_policy_id IN NUMBER) IS
4480 BEGIN
4481 set_status_pol_sched_opts(p_policy_id, 'ACTIVE');
4482 END status_active_sched_opts;
4483
4484 /*========================================================================
4485 | PUBLIC PROCEDURE set_status_pol_sched_opts
4486 |
4487 | DESCRIPTION
4488 | This procedure sets status of schedule options for the given policy_id
4489 |
4490 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4491 | Called from BC4J
4492 |
4493 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4494 |
4495 | PARAMETERS
4496 | p_policy_id IN Policy Id
4497 | p_status_code IN Status Code
4498 |
4499 | MODIFICATION HISTORY
4500 | Date Author Description of Changes
4501 | 17-JUL-2002 Mohammad Shoaib Jamall Created
4502 |
4503 *=======================================================================*/
4504 PROCEDURE set_status_pol_sched_opts(p_policy_id IN NUMBER,
4505 p_status_code IN VARCHAR2) IS
4506 BEGIN
4507 IF p_policy_id IS NOT NULL AND
4508 p_status_code IS NOT NULL THEN
4509 update AP_POL_SCHEDULE_OPTIONS set STATUS = p_status_code where POLICY_ID = p_policy_id;
4510 END IF;
4511 END set_status_pol_sched_opts;
4512
4513 /*========================================================================
4514 | PUBLIC FUNCTION are_exp_type_enddates_capped
4515 |
4516 | DESCRIPTION
4517 | Checks to see if end dates on expense templates are capped
4518 |
4519 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4520 |
4521 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4522 |
4523 | PARAMETERS
4524 | p_policy_id IN Policy Identifier
4525 | p_end_date IN End Date
4526 |
4527 | MODIFICATION HISTORY
4528 | Date Author Description of Changes
4529 | 31-JUL-2002 Mohammad Shoaib Jamall Created
4530 |
4531 *=======================================================================*/
4532 FUNCTION are_exp_type_enddates_capped(p_policy_id IN ap_pol_headers.policy_id%TYPE,
4533 p_end_date IN ap_pol_headers.end_date%TYPE) RETURN VARCHAR2 IS
4534 l_count_rows NUMBER := 0;
4535 l_return_val VARCHAR2(1);
4536 BEGIN
4537
4538 l_count_rows :=0;
4539 IF p_policy_id IS NOT NULL AND
4540 p_end_date IS NOT NULL THEN
4541
4542 SELECT 1 INTO l_count_rows
4543 FROM dual
4544 WHERE exists
4545 (select 1
4546 from ap_expense_report_params_all
4547 where company_policy_id = p_policy_id and nvl(end_date,p_end_date+1) > p_end_date);
4548
4549 END IF;
4550
4551 IF (l_count_rows = 1) THEN
4552 l_return_val := 'N';
4553 ELSE
4554 l_return_val := 'Y';
4555 END IF;
4556
4557 RETURN l_return_val;
4558
4559 EXCEPTION
4560 WHEN no_data_found THEN
4561 l_return_val := 'Y';
4562 RETURN l_return_val;
4563 WHEN OTHERS THEN
4564 raise;
4565 END are_exp_type_enddates_capped;
4566
4567 /*========================================================================
4568 | PUBLIC FUNCTION cap_expense_type_enddates
4569 |
4570 | DESCRIPTION
4571 | Caps end dates on expense type with p_end_date
4572 |
4573 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4574 |
4575 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4576 |
4577 | PARAMETERS
4578 | p_policy_id IN Policy Identifier
4579 | p_end_date IN End Date
4580 |
4581 | MODIFICATION HISTORY
4582 | Date Author Description of Changes
4583 | 31-JUL-2002 Mohammad Shoaib Jamall Created
4584 |
4585 *=======================================================================*/
4586 PROCEDURE cap_expense_type_enddates(p_policy_id IN ap_pol_headers.policy_id%TYPE,
4587 p_end_date IN ap_pol_headers.end_date%TYPE) IS
4588 PRAGMA AUTONOMOUS_TRANSACTION;
4589
4590 l_are_enddates_capped VARCHAR2(1);
4591 BEGIN
4592 l_are_enddates_capped := are_exp_type_enddates_capped(p_policy_id, p_end_date);
4593
4594 IF (l_are_enddates_capped = 'N') THEN
4595 UPDATE ap_expense_report_params_all SET end_date = p_end_date
4596 WHERE company_policy_id = p_policy_id and nvl(end_date,p_end_date+1) > p_end_date;
4597 commit;
4598 END IF;
4599
4600 END cap_expense_type_enddates;
4601
4602 /*========================================================================
4603 | PUBLIC PROCEDURE initialize_user_expense_options
4604 |
4605 | DESCRIPTION
4606 | This procedure creates expense options for user context.
4607 |
4608 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4609 | Called from BC4J
4610 |
4611 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4612 |
4613 | PARAMETERS
4614 | p_user_id IN User Id
4615 |
4616 | MODIFICATION HISTORY
4617 | Date Author Description of Changes
4618 | 01-Jul-2003 J Rautiainen Created
4619 |
4620 *=======================================================================*/
4621 PROCEDURE init_user_expense_options(p_user_id IN NUMBER) IS
4622
4623 CURSOR user_exp_options_c IS
4624 SELECT ep.ORG_ID,
4625 pc.user_id,
4626 pc.selected_org_id
4627 FROM AP_EXPENSE_PARAMS_ALL ep,
4628 AP_POL_CONTEXT pc
4629 WHERE ep.org_id(+) = pc.selected_org_id
4630 AND pc.user_id = p_user_id;
4631
4632 BEGIN
4633 FOR user_exp_options_rec IN user_exp_options_c LOOP
4634
4635 IF user_exp_options_rec.org_id is null THEN
4636 INSERT INTO AP_EXPENSE_PARAMS_ALL
4637 (prevent_cash_cc_age_limit,
4638 prevent_future_dated_day_limit,
4639 enforce_cc_acc_limit,
4640 enforce_cc_air_limit,
4641 enforce_cc_car_limit,
4642 enforce_cc_meal_limit,
4643 enforce_cc_misc_limit,
4644 org_id,
4645 creation_date,
4646 created_by,
4647 last_update_login,
4648 last_update_date,
4649 last_updated_by)
4650 VALUES (null,
4651 null,
4652 null,
4653 null,
4654 null,
4655 null,
4656 null,
4657 user_exp_options_rec.selected_org_id,
4658 SYSDATE,
4659 p_user_id,
4660 NULL,
4661 SYSDATE,
4662 p_user_id);
4663 END IF;
4664 END LOOP;
4665
4666 END init_user_expense_options;
4667
4668 /*========================================================================
4669 | PUBLIC FUNCTION format_minutes_to_hour_minutes
4670 |
4671 | DESCRIPTION
4672 |
4673 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4674 |
4675 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4676 |
4677 | PARAMETERS
4678 | p_lookup_type IN lookup type
4679 |
4680 | MODIFICATION HISTORY
4681 | Date Author Description of Changes
4682 | 10-May-2002 J Rautiainen Created
4683 |
4684 *=======================================================================*/
4685 FUNCTION format_minutes_to_hour_minutes(p_minutes IN NUMBER) RETURN VARCHAR2 IS
4686 BEGIN
4687 RETURN lpad(to_char(get_hours_from_threshold(p_minutes)), 2, 0)
4688 || ':' ||
4689 lpad(to_char(get_minutes_from_threshold(p_minutes)), 2, 0);
4690 EXCEPTION
4691 WHEN OTHERS THEN
4692 raise;
4693 END format_minutes_to_hour_minutes;
4694
4695 /*========================================================================
4696 | PUBLIC FUNCTION get_hours_from_threshold
4697 |
4698 | DESCRIPTION
4699 | gets hours from the threshold value stored in minutes
4700 |
4701 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4702 |
4703 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4704 |
4705 | PARAMETERS
4706 | p_lookup_type IN lookup type
4707 |
4708 | MODIFICATION HISTORY
4709 | Date Author Description of Changes
4710 | 10-May-2002 J Rautiainen Created
4711 |
4712 *=======================================================================*/
4713 FUNCTION get_hours_from_threshold(p_threshold IN NUMBER) RETURN NUMBER IS
4714 BEGIN
4715 IF p_threshold IS NULL THEN
4716 RETURN NULL;
4717 ELSE
4718 RETURN trunc(p_threshold/60);
4719 END IF;
4720 EXCEPTION
4721 WHEN OTHERS THEN
4722 raise;
4723 END get_hours_from_threshold;
4724
4725 /*========================================================================
4726 | PUBLIC FUNCTION get_minutes_threshold
4727 |
4728 | DESCRIPTION
4729 | converts threshold stored in minutes to hours:mins and returns mins
4730 |
4731 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4732 |
4733 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4734 |
4735 | PARAMETERS
4736 | p_lookup_type IN lookup type
4737 |
4738 | MODIFICATION HISTORY
4739 | Date Author Description of Changes
4740 | 10-May-2002 J Rautiainen Created
4741 |
4742 *=======================================================================*/
4743 FUNCTION get_minutes_from_threshold(p_threshold IN NUMBER) RETURN NUMBER IS
4744 BEGIN
4745 IF p_threshold IS NULL THEN
4746 RETURN NULL;
4747 ELSE
4748 RETURN mod(p_threshold,60);
4749 END IF;
4750 EXCEPTION
4751 WHEN OTHERS THEN
4752 raise;
4753 END get_minutes_from_threshold;
4754
4755
4756 /*========================================================================
4757 | PUBLIC PROCEDURE deletePolicySchedule
4758 |
4759 | DESCRIPTION
4760 | This procedure deletes a Policy Schedule
4761 |
4762 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4763 | Called from BC4J
4764 |
4765 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4766 |
4767 | PARAMETERS
4768 | p_policy_id IN Policy ID
4769 |
4770 | MODIFICATION HISTORY
4771 | Date Author Description of Changes
4772 | 22-Jul-2005 Sameer Saxena Created
4773 |
4774 *=======================================================================*/
4775 PROCEDURE deletePolicySchedule(p_policy_id IN NUMBER) IS
4776
4777 BEGIN
4778
4779 DELETE FROM AP_POL_SCHEDULE_OPTIONS WHERE POLICY_ID = p_policy_id;
4780
4781 DELETE FROM AP_POL_SCHEDULE_PERIODS WHERE POLICY_ID = p_policy_id;
4782
4783 DELETE FROM AP_POL_LINES WHERE POLICY_ID = p_policy_id;
4784
4785 EXCEPTION
4786 WHEN OTHERS THEN
4787 raise;
4788
4789 END deletePolicySchedule;
4790
4791
4792 /*========================================================================
4793 | PUBLIC PROCEDURE getPolicyLinesCount
4794 |
4795 | DESCRIPTION
4796 | This procedure returns the number of lines for a policy schedule
4797 |
4798 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4799 | Called from BC4J to prevent querying large number of rows
4800 | during initialization.
4801 |
4802 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4803 |
4804 | PARAMETERS
4805 | p_schedule_period_id IN Policy Schedule Period ID
4806 |
4807 | MODIFICATION HISTORY
4808 | Date Author Description of Changes
4809 | 18-Oct-2005 krmenon Created
4810 |
4811 *=======================================================================*/
4812 FUNCTION getPolicyLinesCount(p_schedule_period_id IN NUMBER) RETURN NUMBER IS
4813 l_count NUMBER;
4814 BEGIN
4815
4816 SELECT count(1)
4817 INTO l_count
4818 FROM ap_pol_lines
4819 WHERE schedule_period_id = p_schedule_period_id;
4820
4821 RETURN nvl(l_count, 0 );
4822
4823 EXCEPTION
4824 WHEN OTHERS THEN
4825 RETURN 0;
4826 END getPolicyLinesCount;
4827
4828 /*========================================================================
4829 | PUBLIC PROCEDURE getSingleTokenMessage
4830 |
4831 | DESCRIPTION
4832 | This function returns the fnd message which has a single token
4833 |
4834 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4835 | Called from BC4J and is used in the JRAD for setting column headers
4836 |
4837 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4838 |
4839 | PARAMETERS
4840 | p_message_name IN FND Message Name
4841 | p_token IN FND Message Token
4842 | p_token_value IN FND Message Token Value
4843 |
4844 | MODIFICATION HISTORY
4845 | Date Author Description of Changes
4846 | 18-Oct-2005 krmenon Created
4847 |
4848 *=======================================================================*/
4849 FUNCTION getSingleTokenMessage( p_message_name IN VARCHAR2,
4850 p_token IN VARCHAR2,
4851 p_token_value IN VARCHAR2 ) RETURN VARCHAR2 IS
4852 BEGIN
4853
4854 IF ( p_message_name IS NULL OR p_token IS NULL ) THEN
4855 RETURN NULL;
4856 END IF;
4857
4858 fnd_message.set_name('SQLAP', p_message_name);
4859 fnd_message.set_token( p_token, p_token_value);
4860
4861 RETURN fnd_message.get;
4862
4863 END getSingleTokenMessage;
4864
4865
4866 /*========================================================================
4867 | PUBLIC FUNCTION get_per_diem_type_meaning
4868 |
4869 | DESCRIPTION
4870 | This function fetches the meaning for a given lookup type and code
4871 | combination. The values are cached, so the SQL is executed only
4872 | once for the session.
4873 |
4874 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4875 | BC4J objects
4876 |
4877 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4878 | DBMS_UTILITY.get_hash_value
4879 |
4880 | PARAMETERS
4881 | p_source IN Source (NULL, CONUS)
4882 | p_lookup_code IN Lookup code, which is part of the lookup
4883 | type in previous parameter
4884 |
4885 | MODIFICATION HISTORY
4886 | Date Author Description of Changes
4887 | 08-May-2002 J Rautiainen Created
4888 |
4889 *=======================================================================*/
4890 FUNCTION get_per_diem_type_meaning(p_source IN VARCHAR2,
4891 p_lookup_code IN VARCHAR2) RETURN VARCHAR2 IS
4892 l_lookup_type fnd_lookups.lookup_type%TYPE;
4893 BEGIN
4894
4895 IF ( p_source = 'CONUS' ) THEN
4896 l_lookup_type := 'OIE_PER_DIEM_UPLOAD_TYPES';
4897 ELSE
4898 l_lookup_type := 'OIE_PER_DIEM_TYPES';
4899 END IF;
4900
4901 RETURN get_lookup_meaning( l_lookup_type, p_lookup_code);
4902
4903 END get_per_diem_type_meaning;
4904
4905
4906 /*========================================================================
4907 | PUBLIC PROCEDURE permutatePolicyLines
4908 |
4909 | DESCRIPTION
4910 | - if a Rule is not enabled or Schedule Option not defined for an enabled Rule then remove the
4911 | obsoleted Policy Line
4912 | - this will never recreate permutated lines based on existing option (rerunnable)
4913 | - if option doesn't exist then creates permutation for new option
4914 | - if option end dated then set Policy Line status to inactive
4915 |
4916 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
4917 |
4918 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
4919 |
4920 | PARAMETERS
4921 | p_user_id IN User Identifier
4922 | p_policy_id IN Policy Identifier
4923 | p_rate_type IN Rate Type (FIRST/LAST)
4924 |
4925 | MODIFICATION HISTORY
4926 | Date Author Description of Changes
4927 | 16-May-2002 R Langi Created
4928 |
4929 *=======================================================================*/
4930 PROCEDURE permutatePolicyLines(p_user_id IN NUMBER,
4931 p_policy_id IN ap_pol_headers.policy_id%TYPE,
4932 p_rate_type IN ap_pol_schedule_options.rate_type_code%TYPE) IS
4933
4934 l_schedule_period_id ap_pol_schedule_periods.schedule_period_id%TYPE;
4935 l_permutate_curref INTEGER;
4936 l_rows_permutated NUMBER := 0;
4937 l_policy_line_count NUMBER := 0;
4938 l_insert_sql_stmt VARCHAR2(4000);
4939 l_where_sql_stmt VARCHAR2(4000);
4940 l_not_exists_sql_stmt VARCHAR2(4000);
4941
4942 l_l_sql_stmt VARCHAR2(4000);
4943 l_r_sql_stmt VARCHAR2(4000);
4944 l_c_sql_stmt VARCHAR2(4000);
4945 l_dt_sql_stmt VARCHAR2(4000);
4946
4947
4948 l_location_enabled VARCHAR2(80) := getUnionStmtForRuleOption(p_policy_id, c_LOCATION);
4949 l_role_enabled VARCHAR2(80) := getUnionStmtForRuleOption(p_policy_id, c_EMPLOYEE_ROLE);
4950 l_currency_enabled VARCHAR2(160) := getUnionStmtForRuleOption(p_policy_id, c_CURRENCY);
4951 l_thresholds_enabled VARCHAR2(80) := getUnionStmtForRuleOption(p_policy_id, c_THRESHOLD);
4952
4953 ---------------------------------------
4954 -- cursor for schedule periods
4955 ---------------------------------------
4956 cursor c_schedule_period_id is
4957 select schedule_period_id
4958 from ap_pol_schedule_periods
4959 where policy_id = p_policy_id;
4960
4961 ---------------------------------------
4962 -- cursor for insert/select
4963 ---------------------------------------
4964 cursor l_insert_cursor is
4965 select
4966 '
4967 insert into AP_POL_LINES
4968 (
4969 POLICY_LINE_ID,
4970 POLICY_ID,
4971 SCHEDULE_PERIOD_ID,
4972 LOCATION_ID,
4973 ROLE_ID,
4974 CURRENCY_CODE,
4975 RANGE_LOW,
4976 RANGE_HIGH,
4977 RATE_TYPE_CODE,
4978 STATUS,
4979 CREATION_DATE,
4980 CREATED_BY,
4981 LAST_UPDATE_DATE,
4982 LAST_UPDATED_BY
4983 )
4984 select
4985 AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
4986 :p_policy_id AS POLICY_ID,
4987 :p_schedule_period_id AS SCHEDULE_PERIOD_ID,
4988 NEW_LOCATION_ID AS LOCATION_ID,
4989 NEW_ROLE_ID AS ROLE_ID,
4990 NEW_CURRENCY_CODE AS CURRENCY_CODE,
4991 NEW_RANGE_LOW AS RANGE_LOW,
4992 NEW_RANGE_HIGH AS RANGE_HIGH,
4993 NEW_RATE_TYPE_CODE AS RATE_TYPE_CODE,
4994 ''NEW'' AS STATUS,
4995 sysdate AS CREATION_DATE,
4996 :p_user_id AS CREATED_BY,
4997 sysdate AS LAST_UPDATE_DATE,
4998 :p_user_id AS LAST_UPDATED_BY
4999 from
5000 (
5001 select distinct
5002 NEW_LOCATION_ID,
5003 NEW_ROLE_ID,
5004 NEW_CURRENCY_CODE,
5005 NEW_RANGE_LOW,
5006 NEW_RANGE_HIGH,
5007 NEW_RATE_TYPE_CODE
5008 from
5009 (
5010 select
5011 l.LOCATION_ID AS NEW_LOCATION_ID,
5012 r.ROLE_ID AS NEW_ROLE_ID,
5013 c.CURRENCY_CODE AS NEW_CURRENCY_CODE,
5014 dt.THRESHOLD AS NEW_RANGE_LOW,
5015 ap_web_policy_UTILS.getHighEndOfThreshold(:p_policy_id, dt.THRESHOLD, :p_rate_type) AS NEW_RANGE_HIGH,
5016 :p_rate_type AS NEW_RATE_TYPE_CODE
5017 from
5018 '
5019 from sys.dual; /* l_insert_cursor */
5020
5021 ---------------------------------------
5022 -- cursor for all locations to use
5023 ---------------------------------------
5024 cursor l_l_cursor is
5025 select
5026 '
5027 (select LOCATION_ID
5028 from AP_POL_SCHEDULE_OPTIONS pso
5029 where
5030 POLICY_ID = :p_policy_id
5031 and OPTION_TYPE = :c_LOCATION
5032 and LOCATION_ID IS NOT NULL
5033 and nvl(END_DATE, SYSDATE+1) > SYSDATE
5034 '||l_location_enabled||'
5035 ) l,
5036 '
5037 from sys.dual; /* l_l_cursor */
5038
5039 ---------------------------------------
5040 -- cursor for all roles to use
5041 ---------------------------------------
5042 cursor l_r_cursor is
5043 select
5044 '
5045 (select ROLE_ID
5046 from AP_POL_SCHEDULE_OPTIONS pso
5047 where
5048 POLICY_ID = :p_policy_id
5049 and OPTION_TYPE = :c_EMPLOYEE_ROLE
5050 and ROLE_ID IS NOT NULL
5051 and nvl(END_DATE, SYSDATE+1) > SYSDATE
5052 '||l_role_enabled||'
5053 ) r,
5054 '
5055 from sys.dual; /* l_r_cursor */
5056
5057 ---------------------------------------
5058 -- cursor for all currency codes to use
5059 ---------------------------------------
5060 cursor l_c_cursor is
5061 select
5062 '
5063 (select CURRENCY_CODE
5064 from AP_POL_SCHEDULE_OPTIONS pso
5065 where
5066 POLICY_ID = :p_policy_id
5067 and OPTION_TYPE = :c_CURRENCY
5068 and CURRENCY_CODE IS NOT NULL
5069 and nvl(END_DATE, SYSDATE+1) > SYSDATE
5070 '||l_currency_enabled||'
5071 ) c,
5072 '
5073 from sys.dual; /* l_c_cursor */
5074
5075 ---------------------------------------
5076 -- cursor for all thresholds to use
5077 ---------------------------------------
5078 cursor l_dt_cursor is
5079 select
5080 '
5081 (select THRESHOLD
5082 from AP_POL_SCHEDULE_OPTIONS pso
5083 where
5084 POLICY_ID = :p_policy_id
5085 and (OPTION_TYPE = :c_DISTANCE_THRESHOLD or OPTION_TYPE = :c_TIME_THRESHOLD)
5086 and THRESHOLD IS NOT NULL
5087 and nvl(END_DATE, SYSDATE+1) > SYSDATE
5088 and nvl(rate_type_code, :p_rate_type) = :p_rate_type
5089 '||l_thresholds_enabled||'
5090 ) dt
5091 '
5092 from sys.dual; /* l_dt_cursor */
5093
5094 ---------------------------------------
5095 -- cursor for where rows
5096 ---------------------------------------
5097 cursor l_where_cursor is
5098 select
5099 '
5100 )
5101 where
5102 (
5103 NEW_LOCATION_ID is not null
5104 or NEW_ROLE_ID is not null
5105 or NEW_CURRENCY_CODE is not null
5106 or NEW_RANGE_LOW is not null
5107 or NEW_RANGE_HIGH is not null
5108 )
5109 )
5110 '
5111 from sys.dual; /* l_where_cursor */
5112
5113
5114 ---------------------------------------
5115 -- cursor for adding new rules/options
5116 ---------------------------------------
5117 cursor l_not_exists_cursor is
5118 select
5119 '
5120 )
5121 where
5122 (
5123 NEW_LOCATION_ID is not null
5124 or NEW_ROLE_ID is not null
5125 or NEW_CURRENCY_CODE is not null
5126 or NEW_RANGE_LOW is not null
5127 or NEW_RANGE_HIGH is not null
5128 )
5129 and
5130 not exists
5131 (
5132 select epl.POLICY_LINE_ID
5133 from AP_POL_LINES epl
5134 where epl.POLICY_ID = :p_policy_id
5135 and epl.SCHEDULE_PERIOD_ID = :p_schedule_period_id
5136 and nvl(epl.LOCATION_ID, :dummy_number) = nvl(NEW_LOCATION_ID, :dummy_number)
5137 and nvl(epl.ROLE_ID, :dummy_number) = nvl(NEW_ROLE_ID, :dummy_number)
5138 and
5139 (
5140 (nvl(epl.CURRENCY_CODE, :dummy_varchar2) = nvl(NEW_CURRENCY_CODE, :dummy_varchar2))
5141 or
5142 (epl.CURRENCY_CODE is not null and NEW_CURRENCY_CODE is null)
5143 )
5144 and nvl(epl.RANGE_LOW, :dummy_number) = nvl(NEW_RANGE_LOW, :dummy_number)
5145 and nvl(epl.RANGE_HIGH, :dummy_number) = nvl(NEW_RANGE_HIGH, :dummy_number)
5146 and nvl(epl.RATE_TYPE_CODE, :dummy_varchar2) = nvl(NEW_RATE_TYPE_CODE, :dummy_varchar2)
5147 )
5148 )
5149 '
5150 from sys.dual; /* l_not_exists_cursor */
5151
5152
5153 BEGIN
5154
5155 --removeObsoletedPolicyLines(p_policy_id);
5156
5157 open l_insert_cursor;
5158 open l_where_cursor;
5159 open l_not_exists_cursor;
5160
5161 open l_l_cursor;
5162 open l_r_cursor;
5163 open l_c_cursor;
5164 open l_dt_cursor;
5165
5166 fetch l_insert_cursor into l_insert_sql_stmt;
5167 fetch l_where_cursor into l_where_sql_stmt;
5168 fetch l_not_exists_cursor into l_not_exists_sql_stmt;
5169
5170 fetch l_l_cursor into l_l_sql_stmt;
5171 fetch l_r_cursor into l_r_sql_stmt;
5172 fetch l_c_cursor into l_c_sql_stmt;
5173 fetch l_dt_cursor into l_dt_sql_stmt;
5174
5175 --------------
5176 -- open cursor
5177 --------------
5178 l_permutate_curref := DBMS_SQL.OPEN_CURSOR;
5179
5180 --------------
5181 -- begin loop thru all periods
5182 --------------
5183 open c_schedule_period_id;
5184 loop
5185
5186 fetch c_schedule_period_id into l_schedule_period_id;
5187 exit when c_schedule_period_id%NOTFOUND;
5188
5189 select count(policy_line_id)
5190 into l_policy_line_count
5191 from ap_pol_lines
5192 where policy_id = p_policy_id
5193 and schedule_period_id = l_schedule_period_id;
5194
5195 if (l_policy_line_count = 0) then
5196 --------------
5197 -- parse cursor
5198 --------------
5199 DBMS_SQL.PARSE(l_permutate_curref,
5200 l_insert_sql_stmt||
5201 l_l_sql_stmt||
5202 l_r_sql_stmt||
5203 l_c_sql_stmt||
5204 l_dt_sql_stmt||
5205 l_where_sql_stmt, DBMS_SQL.NATIVE);
5206 else
5207 --------------
5208 -- parse cursor
5209 --------------
5210 DBMS_SQL.PARSE(l_permutate_curref,
5211 l_insert_sql_stmt||
5212 l_l_sql_stmt||
5213 l_r_sql_stmt||
5214 l_c_sql_stmt||
5215 l_dt_sql_stmt||
5216 l_not_exists_sql_stmt, DBMS_SQL.NATIVE);
5217 --------------
5218 -- supply binds specific to this case
5219 --------------
5220 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':dummy_number', -11);
5221 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':dummy_varchar2', '-11');
5222
5223 end if;
5224
5225 --------------
5226 -- supply binds
5227 --------------
5228 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':p_policy_id', p_policy_id);
5229 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':p_schedule_period_id', l_schedule_period_id);
5230 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':p_user_id', p_user_id);
5231 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':c_LOCATION', c_LOCATION);
5232 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':c_EMPLOYEE_ROLE', c_EMPLOYEE_ROLE);
5233 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':c_CURRENCY', c_CURRENCY);
5234 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':c_DISTANCE_THRESHOLD', c_DISTANCE_THRESHOLD);
5235 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':c_TIME_THRESHOLD', c_TIME_THRESHOLD);
5236 DBMS_SQL.BIND_VARIABLE(l_permutate_curref, ':p_rate_type', p_rate_type);
5237
5238 --------------
5239 -- execute cursor
5240 --------------
5241 l_rows_permutated := DBMS_SQL.EXECUTE(l_permutate_curref);
5242
5243 end loop;
5244 close c_schedule_period_id;
5245 --------------
5246 -- end loop thru all periods
5247 --------------
5248
5249 --------------
5250 -- close cursor
5251 --------------
5252 DBMS_SQL.CLOSE_CURSOR(l_permutate_curref);
5253
5254 close l_insert_cursor;
5255 close l_where_cursor;
5256 close l_not_exists_cursor;
5257
5258 close l_l_cursor;
5259 close l_r_cursor;
5260 close l_c_cursor;
5261 close l_dt_cursor;
5262
5263 updateInactivePolicyLines(p_policy_id);
5264 status_saved_sched_opts(p_policy_id);
5265
5266
5267 EXCEPTION
5268 WHEN OTHERS THEN
5269 raise;
5270 END permutatePolicyLines;
5271
5272 /*========================================================================
5273 | PUBLIC PROCEDURE permutateAddonRates
5274 |
5275 | DESCRIPTION
5276 | - if a Rule is not enabled or Schedule Option not defined for an enabled Rule then remove the
5277 | obsoleted Policy Line
5278 | - this will never recreate permutated lines based on existing option (rerunnable)
5279 | - if option doesn't exist then creates permutation for new option
5280 | - if option end dated then set Policy Line status to inactive
5281 |
5282 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
5283 |
5284 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
5285 |
5286 | PARAMETERS
5287 | p_user_id IN User Identifier
5288 | p_policy_id IN Policy Identifier
5289 | p_schedule_period_id IN Policy Schedule Period Id
5290 |
5291 | MODIFICATION HISTORY
5292 | Date Author Description of Changes
5293 | 10-Nov-2005 krmenon Created
5294 |
5295 *=======================================================================*/
5296 PROCEDURE permutateAddonRates( p_user_id IN NUMBER,
5297 p_policy_id IN ap_pol_headers.policy_id%TYPE,
5298 p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE ) IS
5299
5300 l_policy_line_count NUMBER;
5301
5302 BEGIN
5303
5304 BEGIN
5305 select count(policy_line_id)
5306 into l_policy_line_count
5307 from ap_pol_lines
5308 where policy_id = p_policy_id
5309 and schedule_period_id = p_schedule_period_id
5310 and addon_mileage_rate_code is not null;
5311
5312 EXCEPTION
5313 WHEN OTHERS THEN
5314 NULL;
5315 END;
5316
5317 IF (l_policy_line_count = 0) THEN
5318 -- No policy lines, so genera all permutations
5319 insert into AP_POL_LINES
5320 (
5321 POLICY_LINE_ID,
5322 POLICY_ID,
5323 SCHEDULE_PERIOD_ID,
5324 LOCATION_ID,
5325 ROLE_ID,
5326 CURRENCY_CODE,
5327 VEHICLE_CATEGORY,
5328 VEHICLE_TYPE,
5329 FUEL_TYPE,
5330 RANGE_LOW,
5331 RANGE_HIGH,
5332 ADDON_MILEAGE_RATE_CODE,
5333 STATUS,
5334 CREATION_DATE,
5335 CREATED_BY,
5336 LAST_UPDATE_DATE,
5337 LAST_UPDATED_BY
5338 )
5339 select
5340 AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
5341 apl.POLICY_ID,
5342 apl.SCHEDULE_PERIOD_ID,
5343 apl.location_id,
5344 apl.role_id,
5345 apl.currency_code,
5346 vehicle_category,
5347 vehicle_type,
5348 fuel_type,
5349 range_low,
5350 range_high,
5351 option_code as addon_mileage_rate_code,
5352 'NEW' AS STATUS,
5353 sysdate AS CREATION_DATE,
5354 apl.CREATED_BY,
5355 sysdate AS LAST_UPDATE_DATE,
5356 apl.LAST_UPDATED_BY
5357 from ap_pol_lines apl,
5358 ap_pol_schedule_options pso
5359 where apl.POLICY_ID = p_policy_id
5360 and pso.policy_id = apl.policy_id
5361 and OPTION_TYPE = 'OIE_ADDON_MILEAGE_RATES'
5362 and OPTION_CODE IS NOT NULL
5363 and nvl(END_DATE, SYSDATE+1) > SYSDATE
5364 and apl.addon_mileage_rate_code is null;
5365
5366 ELSE
5367 -- ---------------------------------------------------------
5368 -- Delete all obsolete addon mileage rate lines
5369 -- ---------------------------------------------------------
5370 delete from ap_pol_lines pl
5371 where policy_id = p_policy_id
5372 and addon_mileage_rate_code is not null
5373 and not exists
5374 (
5375 select 1
5376 from ap_pol_schedule_options pso
5377 where pso.policy_id = pl.policy_id
5378 and pso.option_code = pl.addon_mileage_rate_code
5379 );
5380 -- ---------------------------------------------------------
5381 -- Policy lines exist so make sure to only generate
5382 -- new permutations for non-existing lines.
5383 -- ---------------------------------------------------------
5384 insert into AP_POL_LINES
5385 (
5386 POLICY_LINE_ID,
5387 POLICY_ID,
5388 SCHEDULE_PERIOD_ID,
5389 LOCATION_ID,
5390 ROLE_ID,
5391 CURRENCY_CODE,
5392 VEHICLE_CATEGORY,
5393 VEHICLE_TYPE,
5394 FUEL_TYPE,
5395 RANGE_LOW,
5396 RANGE_HIGH,
5397 ADDON_MILEAGE_RATE_CODE,
5398 STATUS,
5399 CREATION_DATE,
5400 CREATED_BY,
5401 LAST_UPDATE_DATE,
5402 LAST_UPDATED_BY
5403 )
5404 select
5405 AP_POL_LINES_S.NEXTVAL AS POLICY_LINE_ID,
5406 apl.POLICY_ID,
5407 apl.SCHEDULE_PERIOD_ID,
5408 apl.location_id,
5409 apl.role_id,
5410 apl.currency_code,
5411 apl.vehicle_category,
5412 apl.vehicle_type,
5413 apl.fuel_type,
5414 apl.range_low,
5415 apl.range_high,
5416 option_code as addon_mileage_rate_code,
5417 'NEW' AS STATUS,
5418 sysdate AS CREATION_DATE,
5419 apl.CREATED_BY,
5420 sysdate AS LAST_UPDATE_DATE,
5421 apl.LAST_UPDATED_BY
5422 from ap_pol_lines apl,
5423 ap_pol_schedule_options pso
5424 where apl.POLICY_ID = p_policy_id
5425 and pso.policy_id = apl.policy_id
5426 and OPTION_TYPE = 'OIE_ADDON_MILEAGE_RATES'
5427 and OPTION_CODE IS NOT NULL
5428 and nvl(END_DATE, SYSDATE+1) > SYSDATE
5429 and apl.addon_mileage_rate_code is null
5430 and not exists
5431 ( select 1
5432 from ap_pol_lines epl
5433 where epl.POLICY_ID = apl.policy_id
5434 and epl.SCHEDULE_PERIOD_ID = apl.schedule_period_id
5435 and nvl(epl.LOCATION_ID, -1) = nvl(apl.location_id, -1)
5436 and nvl(epl.ROLE_ID, -1) = nvl(apl.role_id, -1)
5437 and ((nvl(epl.CURRENCY_CODE, 'NULL') = nvl(apl.currency_code, 'NULL'))
5438 or
5439 (epl.CURRENCY_CODE is not null and apl.currency_code is null)
5440 )
5441 and nvl(epl.VEHICLE_CATEGORY, 'NULL') = nvl(apl.vehicle_category, 'NULL')
5442 and nvl(epl.VEHICLE_TYPE, 'NULL') = nvl(apl.vehicle_type, 'NULL')
5443 and nvl(epl.FUEL_TYPE, 'NULL') = nvl(apl.fuel_type, 'NULL')
5444 and nvl(epl.RANGE_LOW, -1) = nvl(apl.range_low, -1)
5445 and nvl(epl.RANGE_HIGH, -1) = nvl(apl.range_high, -1)
5446 and epl.addon_mileage_rate_code = pso.option_code
5447 );
5448
5449
5450 END IF;
5451
5452
5453 END permutateAddonRates;
5454
5455 /*========================================================================
5456 | PUBLIC PROCEDURE permutateNightRates
5457 |
5458 | DESCRIPTION
5459 | - if a Rule is not enabled or Schedule Option not defined for an enabled Rule then remove the
5460 | obsoleted Policy Line
5461 | - this will never recreate permutated lines based on existing option (rerunnable)
5462 | - if option doesn't exist then creates permutation for new option
5463 | - if option end dated then set Policy Line status to inactive
5464 |
5465 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
5466 |
5467 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
5468 |
5469 | PARAMETERS
5470 | p_user_id IN User Identifier
5471 | p_policy_id IN Policy Identifier
5472 | p_schedule_period_id IN Policy Schedule Period Id
5473 |
5474 | MODIFICATION HISTORY
5475 | Date Author Description of Changes
5476 | 10-Nov-2005 krmenon Created
5477 |
5478 *=======================================================================*/
5479 PROCEDURE permutateNightRates( p_user_id IN NUMBER,
5480 p_policy_id IN ap_pol_headers.policy_id%TYPE,
5481 p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE ) IS
5482
5483 -- -------------------------------------
5484 -- Cursor for inserting new permutation
5485 -- -------------------------------------
5486 cursor c_insert_nighrates is
5487 select distinct
5488 apl.POLICY_ID,
5489 apl.SCHEDULE_PERIOD_ID,
5490 apl.location_id,
5491 apl.role_id,
5492 apl.currency_code,
5493 option_code as night_rate_type_code,
5494 'NEW' AS STATUS
5495 from ap_pol_lines apl,
5496 ap_pol_schedule_options pso
5497 where apl.POLICY_ID = p_policy_id
5498 and pso.policy_id(+) = apl.policy_id
5499 and OPTION_TYPE(+) = 'OIE_NIGHT_RATES'
5500 and OPTION_CODE(+) IS NOT NULL
5501 and nvl(END_DATE(+), SYSDATE+1) > SYSDATE
5502 and nvl(apl.rate_type_code, 'STANDARD') = 'STANDARD';
5503
5504 -- -------------------------------------
5505 -- Cursor for updating permutations
5506 -- -------------------------------------
5507 cursor c_update_nightrates is
5508 select
5509 distinct
5510 apl.POLICY_ID,
5511 apl.SCHEDULE_PERIOD_ID,
5512 apl.location_id,
5513 apl.role_id,
5514 apl.currency_code,
5515 option_code as night_rate_type_code,
5516 'NEW' AS STATUS
5517 from ap_pol_lines apl,
5518 ap_pol_schedule_options pso,
5519 ap_pol_lines epl
5520 where apl.POLICY_ID = p_policy_id
5521 and pso.policy_id(+) = apl.policy_id
5522 and OPTION_TYPE(+) = 'OIE_NIGHT_RATES'
5523 and OPTION_CODE(+) IS NOT NULL
5524 and nvl(END_DATE(+), SYSDATE+1) > SYSDATE
5525 and nvl(apl.rate_type_code, 'STANDARD') = 'STANDARD'
5526 and not exists
5527 ( select 1
5528 from ap_pol_lines epl
5529 where epl.POLICY_ID = apl.policy_id
5530 and epl.SCHEDULE_PERIOD_ID = apl.schedule_period_id
5531 and nvl(epl.LOCATION_ID, -1) = nvl(apl.location_id, -1)
5532 and nvl(epl.ROLE_ID, -1) = nvl(apl.role_id, -1)
5533 and ((nvl(epl.CURRENCY_CODE, 'NULL') = nvl(apl.currency_code, 'NULL'))
5534 or
5535 (epl.CURRENCY_CODE is not null and apl.currency_code is null)
5536 )
5537 and epl.rate_type_code = 'NIGHT_RATE'
5538 and ( epl.night_rate_type_code is null or
5539 ( epl.night_rate_type_code is not null and epl.night_rate_type_code = pso.option_code )
5540 )
5541 );
5542
5543 -- ------------------------------------
5544 -- Local variables
5545 -- ------------------------------------
5546 l_policy_line_count NUMBER;
5547 l_night_rates_code ap_pol_headers.night_rates_code%TYPE;
5548
5549 BEGIN
5550
5551 BEGIN
5552 select count(policy_line_id)
5553 into l_policy_line_count
5554 from ap_pol_lines
5555 where policy_id = p_policy_id
5556 and schedule_period_id = p_schedule_period_id
5557 and rate_type_code = 'NIGHT_RATE';
5558
5559 EXCEPTION
5560 WHEN OTHERS THEN
5561 NULL;
5562 END;
5563
5564 IF (l_policy_line_count = 0) THEN
5565 -- No policy lines, so generate all permutations
5566 FOR c_nightrate IN c_insert_nighrates
5567 LOOP
5568 insert into AP_POL_LINES
5569 (
5570 POLICY_LINE_ID,
5571 POLICY_ID,
5572 SCHEDULE_PERIOD_ID,
5573 LOCATION_ID,
5574 ROLE_ID,
5575 CURRENCY_CODE,
5576 RATE_TYPE_CODE,
5577 NIGHT_RATE_TYPE_CODE,
5578 STATUS,
5579 CREATION_DATE,
5580 CREATED_BY,
5581 LAST_UPDATE_DATE,
5582 LAST_UPDATED_BY
5583 )
5584 values
5585 (
5586 AP_POL_LINES_S.NEXTVAL,
5587 c_nightrate.POLICY_ID,
5588 c_nightrate.SCHEDULE_PERIOD_ID,
5589 c_nightrate.LOCATION_ID,
5590 c_nightrate.ROLE_ID,
5591 c_nightrate.CURRENCY_CODE,
5592 'NIGHT_RATE',
5593 c_nightrate.NIGHT_RATE_TYPE_CODE,
5594 c_nightrate.STATUS,
5595 sysdate,
5596 p_user_id,
5597 sysdate,
5598 p_user_id
5599 );
5600 END LOOP;
5601
5602 ELSE
5603
5604 -- ----------------------------------------------------------------
5605 -- Delete all obsolete lines based on the night rates code
5606 -- - If code is single remove rows whic have not null type code
5607 -- - If code is multiple remove rows which have null type code
5608 -- ----------------------------------------------------------------
5609 BEGIN
5610 select night_rates_code
5611 into l_night_rates_code
5612 from ap_pol_headers
5613 where policy_id = p_policy_id;
5614 EXCEPTION WHEN OTHERS THEN
5615 raise;
5616 END;
5617
5618 IF ( l_night_rates_code = 'SINGLE' ) THEN
5619 -- Delete all policy lines which have night rate type code value
5620 delete from ap_pol_lines
5621 where policy_id = p_policy_id
5622 and rate_type_code = 'NIGHT_RATE'
5623 and night_rate_type_code is not null;
5624
5625 ELSIF ( l_night_rates_code = 'MULTIPLE' ) THEN
5626 -- Delete all policy lines which have night rate type code is null
5627 /* delete from ap_pol_lines
5628 where policy_id = p_policy_id
5629 and rate_type_code = 'NIGHT_RATE'
5630 and night_rate_type_code is NULL ; */
5631
5632 -- Modified since deselecting night_rate_types retains values
5633 delete from ap_pol_lines
5634 where policy_id = p_policy_id
5635 and rate_type_code = 'NIGHT_RATE'
5636 and (night_rate_type_code is NULL
5637 or night_rate_type_code not in(select option_code
5638 from ap_pol_schedule_options
5639 where policy_id = p_policy_id));
5640 END IF;
5641
5642 -- ---------------------------------------------------------
5643 -- Policy lines exist so make sure to only generate
5644 -- new permutations for non-existing lines.
5645 -- ---------------------------------------------------------
5646 FOR c_nightrate IN c_update_nightrates
5647 LOOP
5648 insert into AP_POL_LINES
5649 (
5650 POLICY_LINE_ID,
5651 POLICY_ID,
5652 SCHEDULE_PERIOD_ID,
5653 LOCATION_ID,
5654 ROLE_ID,
5655 CURRENCY_CODE,
5656 RATE_TYPE_CODE,
5657 NIGHT_RATE_TYPE_CODE,
5658 STATUS,
5659 CREATION_DATE,
5660 CREATED_BY,
5661 LAST_UPDATE_DATE,
5662 LAST_UPDATED_BY
5663 )
5664 values
5665 (
5666 AP_POL_LINES_S.NEXTVAL,
5667 c_nightrate.POLICY_ID,
5668 c_nightrate.SCHEDULE_PERIOD_ID,
5669 c_nightrate.LOCATION_ID,
5670 c_nightrate.ROLE_ID,
5671 c_nightrate.CURRENCY_CODE,
5672 'NIGHT_RATE',
5673 c_nightrate.NIGHT_RATE_TYPE_CODE,
5674 c_nightrate.STATUS,
5675 sysdate,
5676 p_user_id,
5677 sysdate,
5678 p_user_id
5679 );
5680 END LOOP;
5681
5682 END IF;
5683
5684
5685 END permutateNightRates;
5686
5687 /*========================================================================
5688 | PUBLIC FUNCTION isNightRatesEnabled
5689 |
5690 | DESCRIPTION
5691 | Checks to see if a Rule is enabled for a Schedule and an Option defined
5692 |
5693 | CALLED FROM PROCEDURES/FUNCTIONS this package and from BC4J
5694 |
5695 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
5696 |
5697 | PARAMETERS
5698 | p_policy_id IN Policy Id
5699 | RETURNS
5700 | TRUE If a Rule is enabled for a Schedule and an Option defined
5701 | FALSE If a Rule is not enabled for a Schedule or an Option not defined
5702 |
5703 | MODIFICATION HISTORY
5704 | Date Author Description of Changes
5705 | 10-Nov-2005 krmenon Created
5706 |
5707 *=======================================================================*/
5708 FUNCTION isNightRatesEnabled(p_policy_id IN ap_pol_headers.policy_id%TYPE) RETURN VARCHAR2 IS
5709 l_night_rate_flag VARCHAR2(1) := 'N';
5710 BEGIN
5711
5712 select nvl2(night_rates_code, 'Y', 'N' )
5713 into l_night_rate_flag
5714 from ap_pol_headers
5715 where policy_id = p_policy_id;
5716
5717 return l_night_rate_flag;
5718
5719 EXCEPTION
5720 WHEN NO_DATA_FOUND THEN
5721 return l_night_rate_flag;
5722 WHEN OTHERS THEN
5723 raise;
5724
5725 END isNightRatesEnabled;
5726
5727 /*========================================================================
5728 | PUBLIC FUNCTION isFirstPeriodRatesEnabled
5729 |
5730 | DESCRIPTION
5731 | Checks to see if a Rule is enabled for a Schedule and an Option defined
5732 |
5733 | CALLED FROM PROCEDURES/FUNCTIONS this package and from BC4J
5734 |
5735 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
5736 |
5737 | PARAMETERS
5738 | p_policy_id IN Policy Id
5739 | RETURNS
5740 | TRUE If a Rule is enabled for a Schedule and an Option defined
5741 | FALSE If a Rule is not enabled for a Schedule or an Option not defined
5742 |
5743 | MODIFICATION HISTORY
5744 | Date Author Description of Changes
5745 | 10-Nov-2005 krmenon Created
5746 |
5747 *=======================================================================*/
5748 FUNCTION isFirstPeriodRatesEnabled ( p_policy_id ap_pol_headers.policy_id%TYPE ) RETURN VARCHAR2 IS
5749 l_rate_period_type_code ap_pol_headers.night_rates_code%TYPE;
5750 l_first_period_count number := 0;
5751 BEGIN
5752
5753 select nvl(rate_period_type_code, 'STANDARD')
5754 into l_rate_period_type_code
5755 from ap_pol_headers
5756 where policy_id = p_policy_id;
5757
5758 select count(1)
5759 into l_first_period_count
5760 from ap_pol_schedule_options
5761 where policy_id = p_policy_id
5762 and rate_type_code= 'FIRST_PERIOD';
5763
5764 if (l_rate_period_type_code = 'STANDARD_FIRST_LAST' and l_first_period_count > 0)
5765 then
5766 return 'Y';
5767 else
5768 return 'N';
5769 end if;
5770
5771 EXCEPTION
5772 WHEN OTHERS THEN
5773 raise;
5774
5775 END isFirstPeriodRatesEnabled;
5776
5777
5778 /*========================================================================
5779 | PUBLIC FUNCTION isLastPeriodRatesEnabled
5780 |
5781 | DESCRIPTION
5782 | Checks to see if a Rule is enabled for a Schedule and an Option defined
5783 |
5784 | CALLED FROM PROCEDURES/FUNCTIONS this package and from BC4J
5785 |
5786 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
5787 |
5788 | PARAMETERS
5789 | p_policy_id IN Policy Id
5790 | RETURNS
5791 | TRUE If a Rule is enabled for a Schedule and an Option defined
5792 | FALSE If a Rule is not enabled for a Schedule or an Option not defined
5793 |
5794 | MODIFICATION HISTORY
5795 | Date Author Description of Changes
5796 | 10-Nov-2005 krmenon Created
5797 |
5798 *=======================================================================*/
5799 FUNCTION isLastPeriodRatesEnabled ( p_policy_id ap_pol_headers.policy_id%TYPE ) RETURN VARCHAR2 IS
5800 l_rate_period_type_code ap_pol_headers.night_rates_code%TYPE;
5801 l_last_period_count number := 0;
5802 BEGIN
5803
5804 select nvl(rate_period_type_code, 'STANDARD')
5805 into l_rate_period_type_code
5806 from ap_pol_headers
5807 where policy_id = p_policy_id;
5808
5809 select count(1)
5810 into l_last_period_count
5811 from ap_pol_schedule_options
5812 where policy_id = p_policy_id
5813 and rate_type_code= 'LAST_PERIOD';
5814
5815 if (l_rate_period_type_code = 'STANDARD_FIRST_LAST' and l_last_period_count > 0)
5816 then
5817 return 'Y';
5818 else
5819 return 'N';
5820 end if;
5821
5822 EXCEPTION
5823 WHEN OTHERS THEN
5824 raise;
5825
5826 END isLastPeriodRatesEnabled;
5827
5828 /*========================================================================
5829 | PUBLIC FUNCTION isSameDayRatesEnabled
5830 |
5831 | DESCRIPTION
5832 | Checks to see if a Rule is enabled for a Schedule and an Option defined
5833 |
5834 | CALLED FROM PROCEDURES/FUNCTIONS this package and from BC4J
5835 |
5836 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
5837 |
5838 | PARAMETERS
5839 | p_policy_id IN Policy Id
5840 | RETURNS
5841 | TRUE If a Rule is enabled for a Schedule and an Option defined
5842 | FALSE If a Rule is not enabled for a Schedule or an Option not defined
5843 |
5844 | MODIFICATION HISTORY
5845 | Date Author Description of Changes
5846 | 10-Nov-2005 krmenon Created
5847 |
5848 *=======================================================================*/
5849 FUNCTION isSameDayRatesEnabled ( p_policy_id ap_pol_headers.policy_id%TYPE ) RETURN VARCHAR2 IS
5850 l_same_day_rate_code ap_pol_headers.night_rates_code%TYPE;
5851 l_same_day_count number := 0;
5852 BEGIN
5853
5854 select nvl(same_day_rate_code, 'NULL')
5855 into l_same_day_rate_code
5856 from ap_pol_headers
5857 where policy_id = p_policy_id;
5858
5859 select count(1)
5860 into l_same_day_count
5861 from ap_pol_schedule_options
5862 where policy_id = p_policy_id
5863 and rate_type_code= 'SAME_DAY';
5864
5865 if (l_same_day_rate_code = 'DEFINED' and l_same_day_count > 0)
5866 then
5867 return 'Y';
5868 else
5869 return 'N';
5870 end if;
5871
5872 EXCEPTION
5873 WHEN OTHERS THEN
5874 raise;
5875
5876 END isSameDayRatesEnabled;
5877
5878
5879 /*========================================================================
5880 | PRIVATE FUNCTION permutateConusLines
5881 |
5882 | DESCRIPTION
5883 | This procedure will permutate the conus/oconus based policies lines
5884 | in case where roles have been added as a schedule option
5885 | - Re-runnable (will not create new permuations for existing options
5886 | - Creates permutation only for new options
5887 | - If option end dated then set Policy Line status to inactive
5888 |
5889 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
5890 |
5891 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
5892 |
5893 | PARAMETERS
5894 | p_user_id IN User Identifier
5895 | p_policy_id IN Policy Identifier
5896 |
5897 | MODIFICATION HISTORY
5898 | Date Author Description of Changes
5899 | 07-Dec-2005 krmenon Created |
5900 *=======================================================================*/
5901 PROCEDURE permutateConusLines( p_user_id IN NUMBER,
5902 p_policy_id IN ap_pol_headers.policy_id%TYPE) IS
5903
5904 l_role_enabled VARCHAR2(1);
5905 BEGIN
5906
5907 -- Check if roles rule is enabled for this schedule
5908 l_role_enabled := checkRuleOption(p_policy_id, c_EMPLOYEE_ROLE);
5909
5910 -- ------------------------------------------------------
5911 -- Remove all obsolete role permutations
5912 -- ------------------------------------------------------
5913 DELETE FROM ap_pol_lines pl
5914 WHERE pl.policy_id = p_policy_id
5915 AND NVL(role_id, -1) <> -1
5916 AND NOT EXISTS
5917 ( SELECT 1
5918 FROM ap_pol_schedule_options pso
5919 WHERE pso.policy_id = pl.policy_id
5920 AND pso.option_type = 'EMPLOYEE_ROLE'
5921 AND pso.option_code = pl.role_id
5922 );
5923
5924 IF ( l_role_enabled = 'Y' ) THEN
5925 BEGIN
5926 -- ----------------------------------------------------------
5927 -- Update all lines which has a null value for the role id
5928 -- as the default All Others row.
5929 -- ----------------------------------------------------------
5930 UPDATE ap_pol_lines
5931 SET role_id = -1
5932 WHERE policy_id = p_policy_id
5933 AND role_id IS NULL;
5934
5935 -- ---------------------------------------------
5936 -- Insert new permutations
5937 -- ---------------------------------------------
5938 INSERT INTO ap_pol_lines
5939 ( POLICY_LINE_ID,
5940 POLICY_ID,
5941 SCHEDULE_PERIOD_ID,
5942 RATE_TYPE_CODE,
5943 STATUS,
5944 ROLE_ID,
5945 LOCATION_ID,
5946 CURRENCY_CODE,
5947 RATE,
5948 TOLERANCE,
5949 CALCULATION_METHOD,
5950 MEALS_DEDUCTION,
5951 BREAKFAST_DEDUCTION,
5952 LUNCH_DEDUCTION,
5953 DINNER_DEDUCTION,
5954 ONE_MEAL_DEDUCTION_AMT,
5955 TWO_MEALS_DEDUCTION_AMT,
5956 THREE_MEALS_DEDUCTION_AMT,
5957 ACCOMMODATION_ADJUSTMENT,
5958 ACCOMMODATION_CALC_METHOD,
5959 NIGHT_RATE_TYPE_CODE,
5960 START_OF_SEASON,
5961 END_OF_SEASON,
5962 MAX_LODGING_AMT,
5963 NO_GOVT_MEALS_AMT,
5964 PROP_MEALS_AMT,
5965 OFF_BASE_INC_AMT,
5966 FOOTNOTE_AMT,
5967 FOOTNOTE_RATE_AMT,
5968 MAX_PER_DIEM_AMT,
5969 EFFECTIVE_START_DATE,
5970 EFFECTIVE_END_DATE,
5971 CREATION_DATE,
5972 CREATED_BY,
5973 LAST_UPDATE_LOGIN,
5974 LAST_UPDATE_DATE,
5975 LAST_UPDATED_BY
5976 )
5977 SELECT AP_POL_LINES_S.NEXTVAL,
5978 apl.POLICY_ID,
5979 apl.SCHEDULE_PERIOD_ID,
5980 apl.RATE_TYPE_CODE,
5981 'NEW' as STATUS,
5982 pso.option_code as ROLE_ID,
5983 apl.LOCATION_ID,
5984 apl.CURRENCY_CODE,
5985 apl.RATE,
5986 apl.TOLERANCE,
5987 apl.CALCULATION_METHOD,
5988 apl.MEALS_DEDUCTION,
5989 apl.BREAKFAST_DEDUCTION,
5990 apl.LUNCH_DEDUCTION,
5991 apl.DINNER_DEDUCTION,
5992 apl.ONE_MEAL_DEDUCTION_AMT,
5993 apl.TWO_MEALS_DEDUCTION_AMT,
5994 apl.THREE_MEALS_DEDUCTION_AMT,
5995 apl.ACCOMMODATION_ADJUSTMENT,
5996 apl.ACCOMMODATION_CALC_METHOD,
5997 apl.NIGHT_RATE_TYPE_CODE,
5998 apl.START_OF_SEASON,
5999 apl.END_OF_SEASON,
6000 apl.MAX_LODGING_AMT,
6001 apl.NO_GOVT_MEALS_AMT,
6002 apl.PROP_MEALS_AMT,
6003 apl.OFF_BASE_INC_AMT,
6004 apl.FOOTNOTE_AMT,
6005 apl.FOOTNOTE_RATE_AMT,
6006 apl.MAX_PER_DIEM_AMT,
6007 apl.EFFECTIVE_START_DATE,
6008 apl.EFFECTIVE_END_DATE,
6009 sysdate as CREATION_DATE,
6010 p_user_id as CREATED_BY,
6011 p_user_id LAST_UPDATE_LOGIN,
6012 sysdate as LAST_UPDATE_DATE,
6013 p_user_id as LAST_UPDATED_BY
6014 FROM ap_pol_lines apl,
6015 ap_pol_schedule_options pso
6016 WHERE apl.policy_id = pso.policy_id
6017 AND apl.role_id = -1
6018 AND pso.option_type = 'EMPLOYEE_ROLE'
6019 AND pso.role_id IS NOT NULL
6020 AND pso.role_id <> -1
6021 AND nvl(pso.end_date, SYSDATE+1) > SYSDATE
6022 AND NOT EXISTS
6023 ( SELECT 1
6024 FROM ap_pol_lines epl
6025 WHERE epl.POLICY_ID = apl.policy_id
6026 AND epl.SCHEDULE_PERIOD_ID = apl.schedule_period_id
6027 AND nvl(epl.LOCATION_ID, -1) = nvl(apl.location_id, -1)
6028 AND nvl(epl.ROLE_ID, -1) = pso.option_code
6029 AND nvl(epl.CURRENCY_CODE, 'NULL') = nvl(apl.currency_code, 'NULL')
6030 );
6031 END;
6032 ELSE
6033 BEGIN
6034 -- ----------------------------------------------------------
6035 -- Update all lines which has a -1 for the role id to null
6036 -- since there are no roles implemented
6037 -- ----------------------------------------------------------
6038 UPDATE ap_pol_lines
6039 SET role_id = NULL
6040 WHERE policy_id = p_policy_id
6041 AND role_id IS NOT NULL;
6042
6043 END;
6044 END IF;
6045
6046 END permutateConusLines;
6047
6048
6049 /*========================================================================
6050 | PUBLIC FUNCTION isDateInSeason
6051 |
6052 | DESCRIPTION
6053 | Helper function to determine if a date is contained within a season.
6054 |
6055 | PARAMETERS
6056 | p_date -- Expense Date in question
6057 | p_start_of_season -- Season Start (mm/dd)
6058 | p_end_of_season -- Season End (mm/dd)
6059 | RETURNS
6060 | 'Y' if date within season.
6061 | 'N' otherwise.
6062 |
6063 | MODIFICATION HISTORY
6064 | Date Author Description of Changes
6065 | 21-Feb-2006 albowicz Created
6066 |
6067 *=======================================================================*/
6068 FUNCTION isDateInSeason (p_date DATE,
6069 p_start_of_season ap_pol_lines.start_of_season%TYPE,
6070 p_end_of_season ap_pol_lines.end_of_season%TYPE) RETURN VARCHAR2 IS
6071 l_start_month INTEGER;
6072 l_start_day INTEGER;
6073 l_end_month INTEGER;
6074 l_end_day INTEGER;
6075 l_start_date DATE;
6076 l_end_date DATE;
6077 BEGIN
6078
6079 IF(p_date IS NULL OR p_start_of_season IS NULL OR p_end_of_season IS NULL) THEN
6080 RETURN 'N';
6081 END IF;
6082
6083 l_start_month := substr(p_start_of_season, 1, 2) -1;
6084 l_start_day := substr(p_start_of_season, 4, 5) -1;
6085 l_end_month := substr(p_end_of_season, 1, 2) -1;
6086 l_end_day := substr(p_end_of_season, 4, 5) -1;
6087
6088 l_start_date := TRUNC(p_date, 'YEAR');
6089 l_end_date := l_start_date;
6090
6091 l_start_date := ADD_MONTHS(l_start_date, l_start_month) +l_start_day;
6092 l_end_date := ADD_MONTHS(l_end_date, l_end_month) +l_end_day;
6093
6094 -- Check if the season wraps the end of the year.
6095 IF(l_start_month > l_end_month) THEN
6096 IF(p_date >= l_start_date OR p_date <= l_end_date) THEN
6097 RETURN 'Y';
6098 END IF;
6099 ELSE
6100 IF(p_date >= l_start_date AND p_date <= l_end_date) THEN
6101 RETURN 'Y';
6102 END IF;
6103 END IF;
6104
6105 RETURN 'N';
6106
6107 EXCEPTION
6108 WHEN OTHERS THEN
6109 raise;
6110
6111 END isDateInSeason;
6112
6113 /*========================================================================
6114 | PUBLIC FUNCTION getPolicyLocationId
6115 |
6116 | DESCRIPTION
6117 | Helper function to determine the applicable policy location.
6118 |
6119 | PARAMETERS
6120 | p_expense_type_id -- Expense Type ID associated to the expense
6121 | p_expense_date -- Expense Start Date
6122 | p_location_id -- Expense Location
6123 | RETURNS
6124 | Location Id -- Location ID to use when selecting the policy line.
6125 | null -- otherwise.
6126 |
6127 | MODIFICATION HISTORY
6128 | Date Author Description of Changes
6129 | 21-Feb-2006 albowicz Created
6130 |
6131 *=======================================================================*/
6132
6133 FUNCTION getPolicyLocationId( p_expense_type_id IN NUMBER,
6134 p_expense_date IN DATE,
6135 p_location_id IN NUMBER ) RETURN NUMBER IS
6136
6137 l_location_id AP_POL_SCHEDULE_OPTIONS.location_id%type;
6138
6139 BEGIN
6140 SELECT LOCATION_ID
6141 INTO l_location_id
6142 FROM (
6143 -- This query verifies that a given location is active within a policy
6144 select location_id
6145 from AP_POL_SCHEDULE_OPTIONS opts, ap_expense_report_params_all p
6146 where p.parameter_id = p_expense_type_id
6147 AND policy_id = p.company_policy_id
6148 AND option_type = 'LOCATION'
6149 AND status = 'ACTIVE'
6150 AND (opts.end_date is null OR opts.end_date >= p_expense_date)
6151 AND LOCATION_ID = p_location_id
6152 UNION ALL
6153 select opts.location_id
6154 from AP_POL_SCHEDULE_OPTIONS opts, AP_POL_LOCATIONS_B loc1, AP_POL_LOCATIONS_B loc2, ap_expense_report_params_all p
6155 where p.parameter_id = p_expense_type_id
6156 AND policy_id = p.company_policy_id
6157 AND opts.option_type = 'LOCATION'
6158 AND opts.status = 'ACTIVE'
6159 AND (opts.end_date is null OR opts.end_date >= p_expense_date)
6160 AND loc1.location_id = opts.location_id
6161 AND loc1.location_type = 'COUNTRY'
6162 AND loc2.territory_code = loc1.territory_code
6163 AND loc2.location_type <> 'COUNTRY'
6164 AND loc2.location_id = p_location_id
6165 UNION ALL
6166 -- Will find the all other location for a given policy
6167 select loc.location_id
6168 from AP_POL_SCHEDULE_OPTIONS opts, AP_POL_LOCATIONS_B loc, ap_expense_report_params_all p
6169 where p.parameter_id = p_expense_type_id
6170 AND opts.policy_id = p.company_policy_id
6171 AND opts.option_type = 'LOCATION'
6172 AND opts.status = 'ACTIVE'
6173 AND (opts.end_date is null OR opts.end_date >= p_expense_date)
6174 AND loc.location_id = opts.location_id
6175 AND loc.undefined_location_flag = 'Y'
6176 )
6177 WHERE ROWNUM = 1;
6178
6179 return l_location_id;
6180
6181 EXCEPTION WHEN NO_DATA_FOUND THEN
6182 begin
6183 return l_location_id;
6184 end;
6185
6186 END getPolicyLocationId;
6187
6188
6189
6190 /*========================================================================
6191 | PUBLIC FUNCTION getPolicyLineId
6192 |
6193 | DESCRIPTION
6194 | Determines the applicable policy line given basic expense info.
6195 |
6196 | PARAMETERS
6197 | p_person_id -- Person id for whom the expense belongs.
6198 | p_expense_type_id -- Expense Type ID associated to the expense
6199 | p_expense_date -- Expense Start Date
6200 | p_location_id -- Expense Location
6201 | p_currency_code -- Reimbursement Currency Code
6202 | RETURNS
6203 | Policy Line Id -- if an applicable policy line can be found.
6204 | null -- otherwise.
6205 |
6206 | MODIFICATION HISTORY
6207 | Date Author Description of Changes
6208 | 21-Feb-2006 albowicz Created
6209 |
6210 *=======================================================================*/
6211 FUNCTION getPolicyLineId(p_person_id IN NUMBER,
6212 p_expense_type_id IN NUMBER,
6213 p_expense_date IN DATE,
6214 p_location_id IN NUMBER,
6215 p_currency_code IN VARCHAR2) RETURN NUMBER IS
6216
6217 l_policy_line_id AP_POL_LINES.policy_line_id%type;
6218 l_hr_assignment hr_assignment_rec;
6219 l_location_id NUMBER;
6220 BEGIN
6221
6222 l_hr_assignment := getHrAssignment(p_person_id, p_expense_date);
6223 l_location_id := getPolicyLocationId(p_expense_type_id, p_expense_date, p_location_id);
6224
6225 select l.POLICY_LINE_ID
6226 INTO l_policy_line_id
6227 from AP_POL_HEADERS h, AP_POL_LINES l, AP_POL_SCHEDULE_PERIODS sp, AP_SYSTEM_PARAMETERS sys, AP_POL_EXRATE_OPTIONS rate_opts, ap_expense_report_params_all p
6228 where p.parameter_id = p_expense_type_id
6229 AND h.policy_id = p.company_policy_id
6230 AND h.category_code <> 'MILEAGE'
6231 AND h.category_code <> 'PER_DIEM'
6232 AND p_expense_date between h.start_date and nvl(h.end_date, TO_DATE('31-12-4712', 'DD-MM-YYYY'))
6233 AND l.policy_id = h.policy_id
6234 AND l.status = 'ACTIVE'
6235 AND l.SCHEDULE_PERIOD_ID = sp.SCHEDULE_PERIOD_ID
6236 AND p_expense_date between sp.start_date and nvl(sp.end_date, TO_DATE('31-12-4712', 'DD-MM-YYYY'))
6237 AND sys.org_id = rate_opts.org_id(+)
6238 AND (nvl(h.employee_role_flag, 'N') = 'N' OR
6239 l.role_id = nvl((select ROLE_ID
6240 from AP_POL_SCHEDULE_OPTIONS
6241 where policy_id = h.policy_id
6242 AND option_type = 'EMPLOYEE_ROLE'
6243 AND status = 'ACTIVE'
6244 AND (end_date is null OR end_date >= p_expense_date)
6245 AND ROLE_ID = decode(h.role_code, 'JOB_GROUP', l_hr_assignment.job_id, 'POSITION', l_hr_assignment.position_id, 'GRADE', l_hr_assignment.grade_id, -1)), -1))
6246 AND (nvl(h.location_flag, 'N') = 'N' OR
6247 l.location_id = l_location_id)
6248 AND ( (h.category_code = 'AIRFARE') OR
6249 (l.currency_code = p_currency_code) OR
6250 (nvl(h.allow_rate_conversion_code, 'NO_CONVERSION') = 'NO_CONVERSION' AND h.currency_code = p_currency_code) OR
6251 (h.currency_preference = 'SRC' AND h.allow_rate_conversion_code = 'ALLOW_CONVERSION' AND
6252 ('Y' = GL_CURRENCY_API.rate_exists(p_currency_code, l.currency_code, p_expense_date, rate_opts.exchange_rate_type) OR
6253 ('Y' = GL_CURRENCY_API.rate_exists(p_currency_code, sys.base_currency_code, p_expense_date, rate_opts.exchange_rate_type) AND
6254 'Y' = GL_CURRENCY_API.rate_exists(sys.base_currency_code, l.currency_code, p_expense_date, rate_opts.exchange_rate_type)))
6255 ) OR
6256 (nvl(h.currency_preference, 'LCR') = 'LCR' AND ('Y' = GL_CURRENCY_API.rate_exists(p_currency_code, l.currency_code, p_expense_date, rate_opts.exchange_rate_type) OR
6257 ('Y' = GL_CURRENCY_API.rate_exists(p_currency_code, sys.base_currency_code, p_expense_date, rate_opts.exchange_rate_type) AND 'Y' = GL_CURRENCY_API.rate_exists(sys.base_currency_code, l.currency_code, p_expense_date, rate_opts.exchange_rate_type)))
6258 )
6259 )
6260 -- ACC Seasonality condition.
6261 AND (h.category_code <> 'ACCOMMODATIONS' OR l.start_of_season IS NULL OR l.end_of_season IS NULL OR
6262 'Y' = AP_WEB_POLICY_UTILS.isDateInSeason(p_expense_date, l.start_of_season, l.end_of_season))
6263 AND l.parent_line_id is null -- Bug: 6866388, Too Many rows fetched
6264 AND p_expense_date between nvl(l.effective_start_date, p_expense_date) and nvl(l.effective_end_date, p_expense_date+1); -- 6994883
6265
6266 return l_policy_line_id;
6267
6268
6269 EXCEPTION WHEN NO_DATA_FOUND THEN
6270 begin
6271 return l_policy_line_id;
6272 end;
6273
6274
6275 END getPolicyLineId;
6276
6277
6278 /*========================================================================
6279 | PUBLIC FUNCTION checkForInvalidLines
6280 |
6281 | DESCRIPTION
6282 | Public helper procedure to validate policy lines.
6283 |
6284 | PARAMETERS
6285 | p_policy_id IN Policy Id
6286 | p_schedule_id IN Policy Schedule Id.
6287 | p_rate_type IN Rate Type (STANDARD, SAME_DAY, FIRST_PERIOD,
6288 | LAST_PERIOD, NIGHT_RATE, ADDON)
6289 | p_std_invalid OUT Count of invalid standard lines
6290 | p_first_invalid OUT Count of invalid first period lines
6291 | p_last_invalid OUT Count of invalid last period lines
6292 | p_same_invalid OUT Count of invalid same day rate lines
6293 | p_night_invalid OUT Count of invalid night rate lines
6294 | p_addon_invalid OUT Count of invalid addon lines
6295 |
6296 |
6297 | MODIFICATION HISTORY
6298 | Date Author Description of Changes
6299 | 08-Jun-2006 krmenon Created
6300 |
6301 *=======================================================================*/
6302 FUNCTION checkForInvalidLines(p_policy_id IN ap_pol_lines.POLICY_ID%type,
6303 p_schedule_id IN ap_pol_lines.SCHEDULE_PERIOD_ID%type,
6304 p_std_invalid OUT NOCOPY NUMBER,
6305 p_first_invalid OUT NOCOPY NUMBER,
6306 p_last_invalid OUT NOCOPY NUMBER,
6307 p_same_invalid OUT NOCOPY NUMBER,
6308 p_night_invalid OUT NOCOPY NUMBER,
6309 p_addon_invalid OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
6310
6311 -- Cursor to check for invalid lines based on status
6312 cursor c_invalid_policy_lines is
6313 select nvl(rate_type_code, 'STANDARD') rate_type_code, count(1) as number_of_lines
6314 from ap_pol_lines
6315 where policy_id = p_policy_id
6316 and schedule_period_id = p_schedule_id
6317 and (status <> 'VALID' and status <> 'ACTIVE')
6318 and addon_mileage_rate_code is null
6319 group by rate_type_code
6320 union all
6321 select nvl(rate_type_code, 'ADDON') rate_type_code, count(1) as number_of_lines
6322 from ap_pol_lines
6323 where policy_id = p_policy_id
6324 and schedule_period_id = p_schedule_id
6325 and (status <> 'VALID' and status <> 'ACITVE')
6326 and addon_mileage_rate_code is not null
6327 group by rate_type_code;
6328
6329 l_std_invalid NUMBER;
6330 l_first_invalid NUMBER;
6331 l_last_invalid NUMBER;
6332 l_same_invalid NUMBER;
6333 l_night_invalid NUMBER;
6334 l_addon_invalid NUMBER;
6335
6336 BEGIN
6337
6338 -- Initialized
6339 p_std_invalid :=0;
6340 p_first_invalid :=0;
6341 p_last_invalid :=0;
6342 p_same_invalid :=0;
6343 p_night_invalid :=0;
6344 p_addon_invalid :=0;
6345
6346 -- Loop through the cursor
6347 FOR invalidLines in c_invalid_policy_lines
6348 LOOP
6349 IF ( invalidLines.rate_type_code = 'STANDARD' ) THEN
6350 p_std_invalid := invalidLines.number_of_lines;
6351 ELSIF ( invalidLines.rate_type_code = 'FIRST_PERIOD' ) THEN
6352 p_first_invalid := invalidLines.number_of_lines;
6353 ELSIF ( invalidLines.rate_type_code = 'LAST_PERIOD' ) THEN
6354 p_last_invalid := invalidLines.number_of_lines;
6355 ELSIF ( invalidLines.rate_type_code = 'SAME_DAY' ) THEN
6356 p_same_invalid := invalidLines.number_of_lines;
6357 ELSIF ( invalidLines.rate_type_code = 'NIGHT_RATE' ) THEN
6358 p_night_invalid := invalidLines.number_of_lines;
6359 ELSIF ( invalidLines.rate_type_code = 'ADDON' ) THEN
6360 p_addon_invalid := invalidLines.number_of_lines;
6361 END IF;
6362
6363 END LOOP;
6364
6365 IF ( (p_std_invalid + p_first_invalid + p_last_invalid +
6366 p_same_invalid + p_night_invalid + p_addon_invalid) > 0 ) THEN
6367 RETURN 'Y';
6368 ELSE
6369 RETURN 'N';
6370 END IF;
6371
6372 END checkForInvalidLines;
6373
6374 /*========================================================================
6375 | PUBLIC FUNCTION activatePolicyLines
6376 |
6377 | DESCRIPTION
6378 | Public helper procedure to activate policy lines for the case where there
6379 | are more than 300 lines.
6380 |
6381 | PARAMETERS
6382 | p_policy_id IN Policy Id
6383 | p_schedule_id IN Policy Schedule Id
6384 |
6385 |
6386 | MODIFICATION HISTORY
6387 | Date Author Description of Changes
6388 | 08-Jun-2006 krmenon Created
6389 |
6390 *=======================================================================*/
6391 PROCEDURE activatePolicyLines(p_policy_id IN ap_pol_lines.POLICY_ID%type,
6392 p_schedule_id IN ap_pol_lines.SCHEDULE_PERIOD_ID%type) IS
6393 BEGIN
6394
6395 UPDATE ap_pol_lines
6396 SET status = 'ACTIVE'
6397 WHERE policy_id = p_policy_id
6398 AND schedule_period_id = p_schedule_id
6399 AND status = 'VALID';
6400
6401 END activatePolicyLines;
6402
6403
6404 END AP_WEB_POLICY_UTILS;
[Home] [Help]