1 PACKAGE AP_WEB_POLICY_UTILS AS
2 /* $Header: apwpolus.pls 120.16 2006/09/20 21:06:48 dtong noship $ */
3 /*=======================================================================+
4 | Declare PUBLIC Data Types and Variables
5 +=======================================================================*/
6
7
8 /*=======================================================================+
9 | List of possible AP_POL_HEADERS.CATEGORY_CODE (OIE_EXPENSE_CATEGORY)
10 +=======================================================================*/
11 c_ACCOMMODATIONS CONSTANT VARCHAR2(20) := 'ACCOMMODATIONS';
12 c_AIRFARE CONSTANT VARCHAR2(20) := 'AIRFARE';
13 c_CAR_RENTAL CONSTANT VARCHAR2(20) := 'CAR_RENTAL';
14 c_MEALS CONSTANT VARCHAR2(20) := 'MEALS';
15 c_MILEAGE CONSTANT VARCHAR2(20) := 'MILEAGE';
16 c_MISC CONSTANT VARCHAR2(20) := 'MISC';
17 c_PER_DIEM CONSTANT VARCHAR2(20) := 'PER_DIEM';
18 /*=======================================================================+
19 | List of possible AP_POL_LINES.STATUS (OIE_POLICY_LINE_STATUS)
20 +=======================================================================*/
21 c_SAVED CONSTANT VARCHAR2(20) := 'SAVED';
22 c_DUPLICATED CONSTANT VARCHAR2(20) := 'DUPLICATED';
23 c_ACTIVE CONSTANT VARCHAR2(20) := 'ACTIVE';
24 c_INACTIVE CONSTANT VARCHAR2(20) := 'INACTIVE';
25 c_ARCHIVED CONSTANT VARCHAR2(20) := 'ARCHIVED';
26 /*=======================================================================+
27 | List of possible AP_POL_SCHEDULE_OPTIONS.OPTION_TYPE
28 +=======================================================================*/
29 c_LOCATION CONSTANT VARCHAR2(20) := 'LOCATION';
30 c_EMPLOYEE_ROLE CONSTANT VARCHAR2(20) := 'EMPLOYEE_ROLE';
31 c_CURRENCY CONSTANT VARCHAR2(20) := 'CURRENCY';
32 c_VEHICLE_CATEGORY CONSTANT VARCHAR2(20) := 'OIE_VEHICLE_CATEGORY';
33 c_VEHICLE_TYPE CONSTANT VARCHAR2(20) := 'OIE_VEHICLE_TYPE';
34 c_FUEL_TYPE CONSTANT VARCHAR2(20) := 'OIE_FUEL_TYPE';
35 c_DISTANCE_THRESHOLD CONSTANT VARCHAR2(20) := 'DISTANCE_THRESHOLD';
36 c_TIME_THRESHOLD CONSTANT VARCHAR2(20) := 'TIME_THRESHOLD';
37 c_ADDON_RATES CONSTANT VARCHAR2(30) := 'OIE_ADDON_MILEAGE_RATES';
38 c_NIGHT_RATES CONSTANT VARCHAR2(30) := 'OIE_NIGHT_RATES';
39 /*=======================================================================+
40 | c_THRESHOLD means (OPTION_TYPE = c_DISTANCE_THRESHOLD or OPTION_TYPE = c_TIME_THRESHOLD)
41 | c_THRESHOLD is used only in this package and is not stored in db
42 | as a valid AP_POL_SCHEDULE_OPTIONS.OPTION_TYPE
43 +=======================================================================*/
44 c_THRESHOLD CONSTANT VARCHAR2(20) := 'THRESHOLD';
45 /*=======================================================================+
46 | List of possible AP_POL_HEADERS.CURRENCY_PREFERENCE (OIE_POL_CUR_RULES)
47 +=======================================================================*/
48 c_LCR CONSTANT VARCHAR2(20) := 'LCR';
49 c_MRC CONSTANT VARCHAR2(20) := 'MRC';
50 c_SRC CONSTANT VARCHAR2(20) := 'SRC';
51 /*=======================================================================+
52 | List of possible OIE_ROUNDING_RULE
53 +=======================================================================*/
54 c_WHOLE_NUMBER CONSTANT VARCHAR2(20) := 'WHOLE_NUMBER';
55 c_NEAREST_FIVE CONSTANT VARCHAR2(20) := 'NEAREST_FIVE';
56 c_NEAREST_TEN CONSTANT VARCHAR2(20) := 'NEAREST_TEN';
57 c_1_DECIMALS CONSTANT VARCHAR2(20) := '1_DECIMALS';
58 c_2_DECIMALS CONSTANT VARCHAR2(20) := '2_DECIMALS';
59 c_3_DECIMALS CONSTANT VARCHAR2(20) := '3_DECIMALS';
60
61 TYPE t_lookups_table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
62 pg_lookups_rec t_lookups_table;
63
64 TYPE t_thresholds_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
65 pg_thresholds_rec t_thresholds_table;
66
67 /*========================================================================
68 | PUBLIC FUNCTION get_schedule_status
69 |
70 | DESCRIPTION
71 | This function fetches the status for a given schedule id.
72 |
73 | PSEUDO CODE/LOGIC
74 |
75 | PARAMETERS
76 | p_policy_id IN policy id
77 |
78 | MODIFICATION HISTORY
79 | Date Author Description of Changes
80 | 26-Sep-2002 V Nama Created
81 |
82 *=======================================================================*/
83 FUNCTION get_schedule_status(p_policy_id IN NUMBER) RETURN VARCHAR2;
84
85 /*========================================================================
86 | PUBLIC FUNCTION get_lookup_meaning
87 |
88 | DESCRIPTION
89 | This function fetches the meaning for a given lookup type and code
90 | combination. The values are cached, so the SQL is executed only
91 | once for the session.
92 |
93 | PSEUDO CODE/LOGIC
94 |
95 | PARAMETERS
96 | p_lookup_type IN lookup type
97 | p_lookup_code IN Lookup code, which is part of the lookup
98 | type in previous parameter
99 |
100 | MODIFICATION HISTORY
101 | Date Author Description of Changes
102 | 08-May-2002 J Rautiainen Created
103 |
104 *=======================================================================*/
105 FUNCTION get_lookup_meaning(p_lookup_type IN VARCHAR2,
106 p_lookup_code IN VARCHAR2) RETURN VARCHAR2;
107
108
109 /*========================================================================
110 | PUBLIC FUNCTION get_lookup_description
111 |
112 | DESCRIPTION
113 | This function fetches the instruction
114 | for a given lookup type and code combination.
115 | The values are cached, so the SQL is executed only
116 | once for the session.
117 |
118 | PSEUDO CODE/LOGIC
119 |
120 | PARAMETERS
121 | p_lookup_type IN lookup type
122 | p_lookup_code IN Lookup code, which is part of the lookup
123 | type in previous parameter
124 |
125 | MODIFICATION HISTORY
126 | Date Author Description of Changes
127 | 27-Oct-2003 R Langi Created
128 |
129 *=======================================================================*/
130 FUNCTION get_lookup_description(p_lookup_type IN VARCHAR2,
131 p_lookup_code IN VARCHAR2) RETURN VARCHAR2;
132
133
134 /*========================================================================
135 | PUBLIC FUNCTION get_high_threshold
136 |
137 | DESCRIPTION
138 | This function return high threshold for a given low value.
139 |
140 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
141 |
142 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
143 |
144 | PARAMETERS
145 | p_lookup_type IN lookup type
146 |
147 | MODIFICATION HISTORY
148 | Date Author Description of Changes
149 | 10-May-2002 J Rautiainen Created
150 |
151 *=======================================================================*/
152 FUNCTION get_high_threshold(p_policy_id IN NUMBER,
153 p_lookup_type IN VARCHAR2,
154 p_low_threshold IN NUMBER) RETURN NUMBER;
155
156 /*========================================================================
157 | PUBLIC FUNCTION get_single_org_context
158 |
159 | DESCRIPTION
160 | This function returns whether user is working in single org context.
161 |
162 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
163 | Called from BC4J on a logic deciding switcher bean behaviour
164 |
165 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
166 |
167 | RETURNS
168 | Y If user is working in single org context
169 | N If user is working in single org context
170 |
171 | PARAMETERS
172 | p_user_id IN User Id
173 |
174 | MODIFICATION HISTORY
175 | Date Author Description of Changes
176 | 13-May-2002 J Rautiainen Created
177 |
178 *=======================================================================*/
179 FUNCTION get_single_org_context(p_user_id IN NUMBER) RETURN VARCHAR2;
180
181
182 /*========================================================================
183 | PUBLIC PROCEDURE initialize_user_cat_options
184 |
185 | DESCRIPTION
186 | This procedure creates category options user context.
187 |
188 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
189 | Called from BC4J
190 |
191 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
192 |
193 | PARAMETERS
194 | p_user_id IN User Id
195 | p_category_code IN Category Code
196 |
197 | MODIFICATION HISTORY
198 | Date Author Description of Changes
199 | 14-May-2002 J Rautiainen Created
200 |
201 *=======================================================================*/
202 PROCEDURE initialize_user_cat_options(p_user_id IN NUMBER,
203 p_category_code IN VARCHAR2);
204
205 /*========================================================================
206 | PUBLIC FUNCTION location_translation_complete
207 |
208 | DESCRIPTION
209 | This function returns whether all locations have been translated
210 | for a given language.
211 |
212 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
213 | Called from BC4J
214 |
215 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
216 |
217 | RETURNS
218 | Y If location has been translated for the given language
219 | N If location has not been translated for the given language
220 |
221 | PARAMETERS
222 | p_language_code IN Language Code
223 |
224 | MODIFICATION HISTORY
225 | Date Author Description of Changes
226 | 21-May-2002 J Rautiainen Created
227 |
228 *=======================================================================*/
229 FUNCTION location_translation_complete(p_language_code IN VARCHAR2) RETURN VARCHAR2;
230
231 /*========================================================================
232 | PUBLIC FUNCTION get_employee_name
233 |
234 | DESCRIPTION
235 | This function returns the employee name for a given user.
236 |
237 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
238 | Called from BC4J, needed for the LineHistoryVO, which cannot have joins
239 | due to connect by clause.
240 |
241 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
242 |
243 | RETURNS
244 | The employee name for the given user. If employee ID is not defined for
245 | the user, then the username is returned.
246 |
247 | PARAMETERS
248 | p_user_id IN User identifier
249 |
250 | MODIFICATION HISTORY
251 | Date Author Description of Changes
252 | 25-May-2002 J Rautiainen Created
253 |
254 *=======================================================================*/
255 FUNCTION get_employee_name(p_user_id IN NUMBER) RETURN VARCHAR2;
256
257 /*========================================================================
258 | PUBLIC FUNCTION get_location
259 |
260 | DESCRIPTION
261 | This function returns location.
262 |
263 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
264 | Called from BC4J, needed for the LineHistoryVO, which cannot have joins
265 | due to connect by clause.
266 |
267 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
268 |
269 | RETURNS
270 | Location
271 |
272 | PARAMETERS
273 | p_location_id IN Location identifier
274 |
275 | MODIFICATION HISTORY
276 | Date Author Description of Changes
277 | 25-May-2002 J Rautiainen Created
278 |
279 *=======================================================================*/
280 FUNCTION get_location(p_location_id IN NUMBER) RETURN VARCHAR2;
281
282 /*========================================================================
283 | PUBLIC FUNCTION get_currency_display
284 |
285 | DESCRIPTION
286 | This function returns currency display.
287 |
288 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
289 | Called from BC4J, needed for
290 | PolicyLinesVO/PolicyLinesAdvancedSearchCriteriaVO/CurrencyScheduleOptionsVO
291 |
292 |
293 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
294 |
295 | RETURNS
296 | Currency Name||' - '||Currency Code
297 |
298 | PARAMETERS
299 | p_currency_code IN Currency Code
300 |
301 | MODIFICATION HISTORY
302 | Date Author Description of Changes
303 | 17-June-2002 R Langi Created
304 |
305 *=======================================================================*/
306 FUNCTION get_currency_display(p_currency_code IN VARCHAR2) RETURN VARCHAR2;
307
308 /*========================================================================
309 | PUBLIC FUNCTION get_role
310 |
311 | DESCRIPTION
312 | This function returns role.
313 |
314 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
315 | Called from BC4J, needed for the LineHistoryVO, which cannot have joins
316 | due to connect by clause.
317 |
318 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
319 |
320 | RETURNS
321 | Role
322 |
323 | PARAMETERS
324 | p_policy_line_id IN Policy Line identifier
325 |
326 | MODIFICATION HISTORY
327 | Date Author Description of Changes
328 | 25-May-2002 J Rautiainen Created
329 |
330 *=======================================================================*/
331 FUNCTION get_role(p_policy_line_id IN NUMBER) RETURN VARCHAR2;
332
333 /*========================================================================
334 | PUBLIC FUNCTION get_role_for_so
335 |
336 | DESCRIPTION
337 | This function returns role for a schedule option.
338 |
339 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
340 | Called from BC4J, needed for RoleScheduleOptionsVO/PolicyLinesAdvancedSearchVO
341 |
342 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
343 |
344 | RETURNS
345 | Role
346 |
347 | PARAMETERS
348 | p_policy_schedule_option_id IN Policy Schedule Option identifier
349 |
350 | MODIFICATION HISTORY
351 | Date Author Description of Changes
352 | 17-June-2002 R Langi Created
353 |
354 *=======================================================================*/
355 FUNCTION get_role_for_so(p_policy_schedule_option_id IN NUMBER) RETURN VARCHAR2;
356
357 /*========================================================================
358 | PUBLIC FUNCTION get_role
359 |
360 | DESCRIPTION
361 | This function returns role.
362 |
363 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
364 | Called from local overloaded get_role function
365 |
366 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
367 |
368 | RETURNS
369 | Role
370 |
371 | PARAMETERS
372 | p_role_code IN Role Code, one of the following: GRADE, JOB_GROUP, POSITION
373 | p_role_id IN Location identifier
374 |
375 | MODIFICATION HISTORY
376 | Date Author Description of Changes
377 | 30-May-2002 J Rautiainen Created
378 |
379 *=======================================================================*/
380 FUNCTION get_role(p_role_code VARCHAR2, p_role_id IN NUMBER) RETURN VARCHAR2;
381
382 /*========================================================================
383 | PUBLIC FUNCTION get_threshold
384 |
385 | DESCRIPTION
386 | This function returns threshold string.
387 |
388 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
389 | Called from BC4J, needed for the LineHistoryVO, which cannot have joins
390 | due to connect by clause.
391 |
392 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
393 |
394 | RETURNS
395 | Threshold
396 |
397 | PARAMETERS
398 | p_range_low IN Range low threshold
399 | p_range_high IN Range high threshold
400 | p_category_code IN Cagetory Code
401 |
402 | MODIFICATION HISTORY
403 | Date Author Description of Changes
404 | 25-May-2002 J Rautiainen Created
405 |
406 *=======================================================================*/
407 FUNCTION get_threshold(p_range_low IN NUMBER,
408 p_range_high IN NUMBER,
409 p_category_code IN VARCHAR2) RETURN VARCHAR2;
410
411 /*========================================================================
412 | PUBLIC PROCEDURE initialize_user_exrate_options
413 |
414 | DESCRIPTION
415 | This procedure creates category options user context.
416 |
417 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
418 | Called from BC4J
419 |
420 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
421 |
422 | PARAMETERS
423 | p_user_id IN User Id
424 | p_category_code IN Category Code
425 |
426 | MODIFICATION HISTORY
427 | Date Author Description of Changes
428 | 30-May-2002 V Nama Created
429 |
430 *=======================================================================*/
431 PROCEDURE initialize_user_exrate_options(p_user_id IN NUMBER);
432
433 /*========================================================================
434 | PUBLIC FUNCTION get_context_tab_enabled
435 |
436 | DESCRIPTION
437 | This function returns whether context tab should be shown or hidden.
438 | Context tab is not showed if:
439 | - Single org installation
440 | - Functional security does not allow it
441 |
442 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
443 | Called from TabCO.java
444 |
445 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
446 |
447 | RETURNS
448 | 'Y' If policy tab should be displayed
449 | 'N' If policy tab should be displayed
450 |
451 | PARAMETERS
452 |
453 | MODIFICATION HISTORY
454 | Date Author Description of Changes
455 | 10-Jun-2002 J Rautiainen Created
456 |
457 *=======================================================================*/
458 FUNCTION get_context_tab_enabled RETURN VARCHAR2;
459
460
461 /*========================================================================
462 | PUBLIC FUNCTION getHighEndOfThreshold
463 |
464 | DESCRIPTION
465 |
466 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
467 |
468 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
469 |
470 | PARAMETERS
471 | p_policy_id IN Policy Identifier
472 | p_threshold IN Threshold
473 |
474 | MODIFICATION HISTORY
475 | Date Author Description of Changes
476 | 16-May-2002 R Langi Created
477 |
478 *=======================================================================*/
479 FUNCTION getHighEndOfThreshold(p_policy_id IN ap_pol_headers.policy_id%TYPE,
480 p_threshold IN ap_pol_schedule_options.threshold%TYPE) RETURN ap_pol_schedule_options.threshold%TYPE;
481
482 /*========================================================================
483 | PUBLIC FUNCTION getHighEndOfThreshold
484 |
485 | DESCRIPTION
486 |
487 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
488 |
489 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
490 |
491 | PARAMETERS
492 | p_policy_id IN Policy Identifier
493 | p_threshold IN Threshold
494 | p_rate_type IN Rate Type (STANDARD, FIRST, LAST)
495 |
496 | MODIFICATION HISTORY
497 | Date Author Description of Changes
498 | 01-Nov-2005 krmenon Created
499 |
500 *=======================================================================*/
501 FUNCTION getHighEndOfThreshold(p_policy_id IN ap_pol_headers.policy_id%TYPE,
502 p_threshold IN ap_pol_schedule_options.threshold%TYPE,
503 p_rate_type IN ap_pol_schedule_options.rate_type_code%TYPE) RETURN ap_pol_schedule_options.threshold%TYPE;
504
505 /*========================================================================
506 | PUBLIC FUNCTION getPolicyCategoryCode
507 |
508 | DESCRIPTION
509 | Returns the Category Code for a Policy
510 |
511 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
512 |
513 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
514 |
515 | PARAMETERS
516 | p_policy_id IN Policy Identifier
517 |
518 | MODIFICATION HISTORY
519 | Date Author Description of Changes
520 | 16-May-2002 R Langi Created
521 |
522 *=======================================================================*/
523 FUNCTION getPolicyCategoryCode(p_policy_id IN ap_pol_headers.policy_id%TYPE) RETURN ap_pol_headers.category_code%TYPE;
524
525 /*========================================================================
526 | PUBLIC FUNCTION checkRuleOption
527 |
528 | DESCRIPTION
529 | Checks to see if a Rule is enabled for a Schedule and an Option defined
530 |
531 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
532 |
533 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
534 | isLocationEnabled
535 | isRoleEnabled
536 | isCurrencyEnabled
537 | isVehicleCategoryEnabled
538 | isVehicleTypeEnabled
539 | isFuelTypeEnabled
540 | isThresholdsEnabled
541 |
542 | PARAMETERS
543 |
544 | RETURNS
545 | 'Y' If a Rule is enabled for a Schedule and an Option defined
546 | 'N' If a Rule is not enabled for a Schedule or an Option not defined
547 |
548 | MODIFICATION HISTORY
549 | Date Author Description of Changes
550 | 16-May-2002 R Langi Created
551 |
552 *=======================================================================*/
553 FUNCTION checkRuleOption(p_policy_id IN ap_pol_headers.policy_id%TYPE,
554 p_rule IN VARCHAR2) RETURN VARCHAR2;
555
556 /*========================================================================
557 | PUBLIC FUNCTION getUnionStmtForRuleOption
558 |
559 | DESCRIPTION
560 | If a Rule is not enabled or Schedule Option not defined for an enabled Rule
561 | this will return a UNION null statement which is used for perumutatePolicyLines()
562 |
563 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
564 |
565 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
566 |
567 | PARAMETERS
568 | p_policy_id IN Policy Identifier
569 | p_rule IN Schedule Option Type
570 |
571 | MODIFICATION HISTORY
572 | Date Author Description of Changes
573 | 16-May-2002 R Langi Created
574 |
575 *=======================================================================*/
576 FUNCTION getUnionStmtForRuleOption(p_policy_id IN ap_pol_headers.policy_id%TYPE,
577 p_rule IN VARCHAR2) RETURN VARCHAR2;
578
579 /*========================================================================
580 | PUBLIC PROCEDURE permutatePolicyLines
581 |
582 | DESCRIPTION
583 | - if a Rule is not enabled or Schedule Option not defined for an enabled Rule then remove the
584 | obsoleted Policy Line
585 | - this will never recreate permutated lines based on existing option (rerunnable)
586 | - if option doesn't exist then creates permutation for new option
587 | - if option end dated then set Policy Line status to inactive
588 |
589 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
590 |
591 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
592 |
593 | PARAMETERS
594 | p_user_id IN User Identifier
595 | p_policy_id IN Policy Identifier
596 |
597 | MODIFICATION HISTORY
598 | Date Author Description of Changes
599 | 16-May-2002 R Langi Created
600 |
601 *=======================================================================*/
602 PROCEDURE permutatePolicyLines(p_user_id IN NUMBER,
603 p_policy_id IN ap_pol_headers.policy_id%TYPE);
604
605 /*========================================================================
606 | PUBLIC PROCEDURE removeObsoletedPolicyLines
607 |
608 | DESCRIPTION
609 | - if a Rule is not enabled or Schedule Option not defined for an enabled Rule then remove the
610 | obsoleted Policy Line
611 |
612 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
613 |
614 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
615 |
616 | PARAMETERS
617 | p_policy_id IN Policy Identifier
618 |
619 | MODIFICATION HISTORY
620 | Date Author Description of Changes
621 | 16-May-2002 R Langi Created
622 |
623 *=======================================================================*/
624 PROCEDURE removeObsoletedPolicyLines(p_policy_id IN ap_pol_headers.policy_id%TYPE);
625
626 /*========================================================================
627 | PUBLIC PROCEDURE updateInactivePolicyLines
628 |
629 | DESCRIPTION
630 | - if option end dated then set Policy Line status to inactive
631 |
632 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
633 |
634 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
635 |
636 | PARAMETERS
637 | p_policy_id IN Policy Identifier
638 |
639 | MODIFICATION HISTORY
640 | Date Author Description of Changes
641 | 16-May-2002 R Langi Created
642 |
643 *=======================================================================*/
644 PROCEDURE updateInactivePolicyLines(p_policy_id IN ap_pol_headers.policy_id%TYPE);
645
646 /*========================================================================
647 | PUBLIC PROCEDURE duplicatePolicyLines
648 |
649 | DESCRIPTION
650 | Duplicates Policy Lines from one Policy Schedule Period to another
651 |
652 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
653 |
654 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
655 |
656 | PARAMETERS
657 | p_user_id IN User Identifier
658 | p_from_policy_id IN Policy Identifier to duplicate From
659 | p_from_schedule_period_id IN Policy Schedule Period Identifier to duplicate From
660 | p_to_policy_id IN Policy Identifier to duplicate To
661 | p_to_schedule_period_id IN Policy Schedule Period Identifier to duplicate To
662 |
663 | MODIFICATION HISTORY
664 | Date Author Description of Changes
665 | 16-May-2002 R Langi Created
666 |
667 *=======================================================================*/
668 PROCEDURE duplicatePolicyLines(p_user_id IN NUMBER,
669 p_from_policy_id IN ap_pol_headers.policy_id%TYPE,
670 p_from_schedule_period_id IN ap_pol_schedule_periods.schedule_period_id%TYPE,
671 p_to_policy_id IN ap_pol_headers.policy_id%TYPE,
672 p_to_schedule_period_id IN ap_pol_schedule_periods.schedule_period_id%TYPE);
673
674 /*========================================================================
675 | PUBLIC PROCEDURE preservePolicyLine
676 |
677 | DESCRIPTION
678 | Preserve a modified Active Policy Line
679 |
680 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
681 |
682 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
683 |
684 | PARAMETERS
685 | p_user_id IN User Identifier
686 | p_policy_id IN Policy Identifier
687 | p_schedule_period_id IN Policy Schedule Period Identifier
688 | p_policy_line_id IN Policy Line Identifier to preserve
689 |
690 | MODIFICATION HISTORY
691 | Date Author Description of Changes
692 | 12-Aug-2002 R Langi Created
693 |
694 *=======================================================================*/
695 PROCEDURE preservePolicyLine(p_user_id IN NUMBER,
696 p_policy_id IN ap_pol_lines.policy_id%TYPE,
697 p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE,
698 p_policy_line_id IN ap_pol_lines.policy_line_id%TYPE);
699
700 /*========================================================================
701 | PUBLIC PROCEDURE archivePreservedPolicyLines
702 |
703 | DESCRIPTION
704 | Archive and remove a preserved Active Policy Line after it has been reactivated
705 |
706 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
707 |
708 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
709 |
710 | PARAMETERS
711 | p_user_id IN User Identifier
712 | p_policy_id IN Policy Identifier
713 |
714 | MODIFICATION HISTORY
715 | Date Author Description of Changes
716 | 12-Aug-2002 R Langi Created
717 |
718 *=======================================================================*/
719 PROCEDURE archivePreservedPolicyLines(p_user_id IN NUMBER,
720 p_policy_id IN ap_pol_lines.policy_id%TYPE);
721
722 /*========================================================================
723 | PUBLIC FUNCTION createSchedulePeriod
724 |
725 | DESCRIPTION
726 | Creates a new Policy Schedule Period
727 |
728 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
729 |
730 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
731 |
732 | PARAMETERS
733 | p_user_id IN User Identifier
734 | p_policy_id IN Policy Identifier
735 | p_schedule_period_name IN Schedule Period Name
736 | p_start_date IN Start Date
737 | p_end_date IN End Date
738 | p_rate_per_passenger IN Rate Per Passenger
739 | p_min_days IN Minimum Number of Days
740 | p_tolerance IN Tolerance
741 | p_min_rate_per_period IN Minimum Rate per Period
742 | p_max_breakfast_deduction IN Maximum Breakfast Deduction Allowed per Period
743 | p_max_lunch_deduction IN Maximum Lunch Deduction Allowed per Period
744 | p_max_dinner_deduction IN Maximum Dinner Deduction Allowed per Period
745 | p_first_day_rate IN First day rate
746 | p_last_day_rate IN Last day rate
747 |
748 | MODIFICATION HISTORY
749 | Date Author Description of Changes
750 | 16-May-2002 R Langi Created
751 |
752 *=======================================================================*/
753 FUNCTION createSchedulePeriod(p_user_id IN NUMBER,
754 p_policy_id IN ap_pol_schedule_periods.policy_id%TYPE,
755 p_schedule_period_name IN ap_pol_schedule_periods.schedule_period_name%TYPE,
756 p_start_date IN ap_pol_schedule_periods.start_date%TYPE,
757 p_end_date IN ap_pol_schedule_periods.end_date%TYPE DEFAULT NULL,
758 p_rate_per_passenger IN ap_pol_schedule_periods.rate_per_passenger%TYPE,
759 p_min_days IN ap_pol_schedule_periods.min_days%TYPE,
760 p_tolerance IN ap_pol_schedule_periods.tolerance%TYPE,
761 p_min_rate_per_period IN ap_pol_schedule_periods.min_rate_per_period%TYPE,
762 p_max_breakfast_deduction IN ap_pol_schedule_periods.max_breakfast_deduction_amt%TYPE,
763 p_max_lunch_deduction IN ap_pol_schedule_periods.max_lunch_deduction_amt%TYPE,
764 p_max_dinner_deduction IN ap_pol_schedule_periods.max_dinner_deduction_amt%TYPE,
765 p_first_day_rate IN ap_pol_schedule_periods.first_day_rate%TYPE,
766 p_last_day_rate IN ap_pol_schedule_periods.last_day_rate%TYPE) RETURN ap_pol_schedule_periods.schedule_period_id%TYPE;
767
768 /*========================================================================
769 | PUBLIC FUNCTION massUpdateValue
770 |
771 | DESCRIPTION
772 | Using a rounding rule and percentage to update by, a value is returned
773 |
774 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
775 |
776 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
777 |
778 | PARAMETERS
779 | p_value IN Value to perform an update on
780 | p_update_by IN Percentage to update the value by
781 | p_rounding_rule IN Rounding rule to use after the value has been updated
782 |
783 | MODIFICATION HISTORY
784 | Date Author Description of Changes
785 | 16-May-2002 R Langi Created
786 |
787 *=======================================================================*/
788 FUNCTION massUpdateValue(p_value IN NUMBER,
789 p_update_by IN NUMBER,
790 p_rounding_rule IN VARCHAR2) RETURN NUMBER;
791
792 /*========================================================================
793 | PUBLIC PROCEDURE duplicatePolicy
794 |
795 | DESCRIPTION
796 | Duplicates a Policy Schedule (General Information/Options/Periods/Lines)
797 |
798 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
799 | createSchedulePeriod
800 | duplicatePolicyLines
801 |
802 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
803 | duplicatePolicyHeader
804 | duplicatePolicyScheduleOptions
805 |
806 | PARAMETERS
807 | p_user_id IN User Identifier
808 | p_from_policy_id IN Policy Identifier to duplicate From
809 | p_new_policy_id IN New Policy Identifier containing the duplicated Policy
810 |
811 | MODIFICATION HISTORY
812 | Date Author Description of Changes
813 | 16-May-2002 R Langi Created
814 |
815 *=======================================================================*/
816 PROCEDURE duplicatePolicy(p_user_id IN NUMBER,
817 p_from_policy_id IN ap_pol_headers.policy_id%TYPE,
818 p_new_policy_id OUT NOCOPY ap_pol_headers.policy_id%TYPE);
819
820 /*========================================================================
821 | PUBLIC FUNCTION active_option_exists
822 |
823 | DESCRIPTION
824 | Checks whether a active schedule option exists for a option
825 |
826 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
827 |
828 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
829 |
830 | PARAMETERS
831 | p_option_type IN Option type, required
832 | p_option_code IN Option code, optional
833 | p_threshold IN Threshold, optional
834 | p_role_id IN Role Id, optional
835 | p_location_id IN Location Id, optional
836 | p_currency_code IN Currency Code, optional
837 | p_end_date IN End Date, optional
838 |
839 | RETURNS
840 | Y If active option exists
841 | N If active option does not exist
842 |
843 | MODIFICATION HISTORY
844 | Date Author Description of Changes
845 | 26-Jun-2002 J Rautiainen Created
846 |
847 *=======================================================================*/
848 FUNCTION active_option_exists(p_option_type IN VARCHAR2,
849 p_option_code IN VARCHAR2,
850 p_threshold IN NUMBER,
851 p_role_id IN NUMBER,
852 p_location_id IN NUMBER,
853 p_currency_code IN VARCHAR2,
854 p_end_date IN DATE) RETURN VARCHAR2;
855
856
857 /*========================================================================
858 | PUBLIC PROCEDURE end_date_active_loc_options
859 |
860 | DESCRIPTION
861 | If locations are end dated on location definitions then corresponding
862 | active locations on schedule options should be end dated provided the
863 | location option's end date is null or later than the defined end date.
864 |
865 | NOTES
866 | Created vide bug 2560275
867 |
868 | MODIFICATION HISTORY
869 | Date Author Description of Changes
870 | 05-Feb-2004 V Nama Created
871 |
872 *=======================================================================*/
873 PROCEDURE end_date_active_loc_options;
874
875 /*========================================================================
876 | PUBLIC FUNCTION does_location_exist
877 |
878 | DESCRIPTION
879 | Checks whether a locations exists
880 |
881 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
882 |
883 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
884 |
885 | PARAMETERS
886 | NONE
887 |
888 | RETURNS
889 | Y If locations exist
890 | N If locations does not exist
891 |
892 | MODIFICATION HISTORY
893 | Date Author Description of Changes
894 | 26-Jun-2002 J Rautiainen Created
895 |
896 *=======================================================================*/
897 FUNCTION does_location_exist RETURN VARCHAR2;
898
899 /*========================================================================
900 | PUBLIC PROCEDURE status_saved_sched_opts
901 |
902 | DESCRIPTION
903 | This procedure sets status of schedule options to 'SAVED' for
904 | the given policy_id
905 |
906 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
907 | Called from BC4J
908 |
909 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
910 |
911 | PARAMETERS
912 | p_policy_id IN Policy Id
913 |
914 | MODIFICATION HISTORY
915 | Date Author Description of Changes
916 | 17-JUL-2002 Mohammad Shoaib Jamall Created
917 |
918 *=======================================================================*/
919 PROCEDURE status_saved_sched_opts(p_policy_id IN NUMBER);
920
921 /*========================================================================
922 | PUBLIC PROCEDURE status_active_sched_opts
923 |
924 | DESCRIPTION
925 | This procedure sets status of schedule options to 'ACTIVE' for
926 | the given policy_id
927 |
928 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
929 | Called from BC4J
930 |
931 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
932 |
933 | PARAMETERS
934 | p_policy_id IN Policy Id
935 |
936 | MODIFICATION HISTORY
937 | Date Author Description of Changes
938 | 17-JUL-2002 Mohammad Shoaib Jamall Created
939 |
940 *=======================================================================*/
941 PROCEDURE status_active_sched_opts(p_policy_id IN NUMBER);
942
943 /*========================================================================
944 | PUBLIC PROCEDURE set_status_pol_sched_opts
945 |
946 | DESCRIPTION
947 | This procedure sets status of schedule options for the given policy_id
948 |
949 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
950 | Called from BC4J
951 |
952 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
953 |
954 | PARAMETERS
955 | p_policy_id IN Policy Id
956 | p_status_code IN Status Code
957 |
958 | MODIFICATION HISTORY
959 | Date Author Description of Changes
960 | 17-JUL-2002 Mohammad Shoaib Jamall Created
961 |
962 *=======================================================================*/
963 PROCEDURE set_status_pol_sched_opts(p_policy_id IN NUMBER,
964 p_status_code IN VARCHAR2);
965
966 /*========================================================================
967 | PUBLIC FUNCTION are_exp_type_enddates_capped
968 |
969 | DESCRIPTION
970 | Checks to see if end dates on expense templates are capped
971 |
972 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
973 |
974 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
975 |
976 | PARAMETERS
977 | p_policy_id IN Policy Identifier
978 | p_end_date IN End Date
979 |
980 | MODIFICATION HISTORY
981 | Date Author Description of Changes
982 | 31-JUL-2002 Mohammad Shoaib Jamall Created
983 |
984 *=======================================================================*/
985 FUNCTION are_exp_type_enddates_capped(p_policy_id IN ap_pol_headers.policy_id%TYPE,
986 p_end_date IN ap_pol_headers.end_date%TYPE DEFAULT NULL) RETURN VARCHAR2;
987
988 /*========================================================================
989 | PUBLIC FUNCTION cap_expense_type_enddates
990 |
991 | DESCRIPTION
992 | Caps end dates on expense type with p_end_date
993 |
994 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
995 |
996 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
997 |
998 | PARAMETERS
999 | p_policy_id IN Policy Identifier
1000 | p_end_date IN End Date
1001 |
1002 | MODIFICATION HISTORY
1003 | Date Author Description of Changes
1004 | 31-JUL-2002 Mohammad Shoaib Jamall Created
1005 |
1006 *=======================================================================*/
1007 PROCEDURE cap_expense_type_enddates(p_policy_id IN ap_pol_headers.policy_id%TYPE,
1008 p_end_date IN ap_pol_headers.end_date%TYPE DEFAULT NULL);
1009
1010
1011 /*========================================================================
1012 | PUBLIC PROCEDURE initialize_user_expense_options
1013 |
1014 | DESCRIPTION
1015 | This procedure creates expense options for user context.
1016 |
1017 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1018 | Called from BC4J
1019 |
1020 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1021 |
1022 | PARAMETERS
1023 | p_user_id IN User Id
1024 |
1025 | MODIFICATION HISTORY
1026 | Date Author Description of Changes
1027 | 01-Jul-2003 J Rautiainen Created
1028 |
1029 *=======================================================================*/
1030 PROCEDURE init_user_expense_options(p_user_id IN NUMBER);
1031
1032 /*========================================================================
1033 | PUBLIC FUNCTION format_minutes_to_hour_minutes
1034 |
1035 | DESCRIPTION
1036 |
1037 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1038 |
1039 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1040 |
1041 | PARAMETERS
1042 | p_lookup_type IN lookup type
1043 |
1044 | MODIFICATION HISTORY
1045 | Date Author Description of Changes
1046 | 10-May-2002 J Rautiainen Created
1047 |
1048 *=======================================================================*/
1049 FUNCTION format_minutes_to_hour_minutes(p_minutes IN NUMBER) RETURN VARCHAR2;
1050
1051 /*========================================================================
1052 | PUBLIC FUNCTION get_hours_from_threshold
1053 |
1054 | DESCRIPTION
1055 | gets hours from the threshold value stored in minutes
1056 |
1057 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1058 |
1059 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1060 |
1061 | PARAMETERS
1062 | p_lookup_type IN lookup type
1063 |
1064 | MODIFICATION HISTORY
1065 | Date Author Description of Changes
1066 | 10-May-2002 J Rautiainen Created
1067 |
1068 *=======================================================================*/
1069 FUNCTION get_hours_from_threshold(p_threshold IN NUMBER) RETURN NUMBER;
1070
1071 /*========================================================================
1072 | PUBLIC FUNCTION get_minutes_threshold
1073 |
1074 | DESCRIPTION
1075 | converts threshold stored in minutes to hours:mins and returns mins
1076 |
1077 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1078 |
1079 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1080 |
1081 | PARAMETERS
1082 | p_lookup_type IN lookup type
1083 |
1084 | MODIFICATION HISTORY
1085 | Date Author Description of Changes
1086 | 10-May-2002 J Rautiainen Created
1087 |
1088 *=======================================================================*/
1089 FUNCTION get_minutes_from_threshold(p_threshold IN NUMBER) RETURN NUMBER;
1090
1091 /*========================================================================
1092 | PUBLIC PROCEDURE deletePolicySchedule
1093 |
1094 | DESCRIPTION
1095 | This procedure deletes a Policy Schedule
1096 |
1097 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1098 | Called from BC4J
1099 |
1100 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1101 |
1102 | PARAMETERS
1103 | p_policy_id IN Policy ID
1104 |
1105 | MODIFICATION HISTORY
1106 | Date Author Description of Changes
1107 | 22-Jul-2005 Sameer Saxena Created
1108 |
1109 *=======================================================================*/
1110 PROCEDURE deletePolicySchedule(p_policy_id IN NUMBER);
1111
1112 /*========================================================================
1113 | PUBLIC PROCEDURE getPolicyLinesCount
1114 |
1115 | DESCRIPTION
1116 | This procedure returns the number of lines for a policy schedule
1117 |
1118 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1119 | Called from BC4J to prevent querying large number of rows
1120 | during initialization.
1121 |
1122 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1123 |
1124 | PARAMETERS
1125 | p_schedule_period_id IN Policy Schedule Period ID
1126 |
1127 | MODIFICATION HISTORY
1128 | Date Author Description of Changes
1129 | 18-Oct-2005 krmenon Created
1130 |
1131 *=======================================================================*/
1132 FUNCTION getPolicyLinesCount(p_schedule_period_id IN NUMBER) RETURN NUMBER;
1133
1134 /*========================================================================
1135 | PUBLIC PROCEDURE getSingleTokenMessage
1136 |
1137 | DESCRIPTION
1138 | This function returns the fnd message which has a single token
1139 |
1140 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1141 | Called from BC4J and is used in the JRAD for setting column headers
1142 |
1143 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1144 |
1145 | PARAMETERS
1146 | p_message_name IN FND Message Name
1147 | p_token IN FND Message Token
1148 | p_token_value IN FND Message Token Value
1149 |
1150 | MODIFICATION HISTORY
1151 | Date Author Description of Changes
1152 | 18-Oct-2005 krmenon Created
1153 |
1154 *=======================================================================*/
1155 FUNCTION getSingleTokenMessage( p_message_name IN VARCHAR2,
1156 p_token IN VARCHAR2,
1157 p_token_value IN VARCHAR2 ) RETURN VARCHAR2;
1158
1159
1160 /*========================================================================
1161 | PUBLIC FUNCTION get_per_diem_type_meaning
1162 |
1163 | DESCRIPTION
1164 | This function fetches the meaning for a given lookup type and code
1165 | combination. The values are cached, so the SQL is executed only
1166 | once for the session.
1167 |
1168 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1169 | BC4J objects
1170 |
1171 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1172 | DBMS_UTILITY.get_hash_value
1173 |
1174 | PARAMETERS
1175 | p_source IN Source (NULL, CONUS)
1176 | p_lookup_code IN Lookup code, which is part of the lookup
1177 | type in previous parameter
1178 |
1179 | MODIFICATION HISTORY
1180 | Date Author Description of Changes
1181 | 08-May-2002 J Rautiainen Created
1182 |
1183 *=======================================================================*/
1184 FUNCTION get_per_diem_type_meaning(p_source IN VARCHAR2,
1185 p_lookup_code IN VARCHAR2) RETURN VARCHAR2;
1186
1187 /*========================================================================
1188 | PUBLIC FUNCTION isFirstPeriodRatesEnabled
1189 |
1190 | DESCRIPTION
1191 | Checks to see if a Rule is enabled for a Schedule and an Option defined
1192 |
1193 | CALLED FROM PROCEDURES/FUNCTIONS this package and from BC4J
1194 |
1195 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1196 |
1197 | PARAMETERS
1198 | p_policy_id IN Policy Id
1199 | RETURNS
1200 | TRUE If a Rule is enabled for a Schedule and an Option defined
1201 | FALSE If a Rule is not enabled for a Schedule or an Option not defined
1202 |
1203 | MODIFICATION HISTORY
1204 | Date Author Description of Changes
1205 | 10-Nov-2005 krmenon Created
1206 |
1207 *=======================================================================*/
1208 FUNCTION isFirstPeriodRatesEnabled ( p_policy_id ap_pol_headers.policy_id%TYPE ) RETURN VARCHAR2;
1209
1210 /*========================================================================
1211 | PUBLIC FUNCTION isLastPeriodRatesEnabled
1212 |
1213 | DESCRIPTION
1214 | Checks to see if a Rule is enabled for a Schedule and an Option defined
1215 |
1216 | CALLED FROM PROCEDURES/FUNCTIONS this package and from BC4J
1217 |
1218 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1219 |
1220 | PARAMETERS
1221 | p_policy_id IN Policy Id
1222 | RETURNS
1223 | TRUE If a Rule is enabled for a Schedule and an Option defined
1224 | FALSE If a Rule is not enabled for a Schedule or an Option not defined
1225 |
1226 | MODIFICATION HISTORY
1227 | Date Author Description of Changes
1228 | 10-Nov-2005 krmenon Created
1229 |
1230 *=======================================================================*/
1231 FUNCTION isLastPeriodRatesEnabled ( p_policy_id ap_pol_headers.policy_id%TYPE ) RETURN VARCHAR2;
1232
1233 /*========================================================================
1234 | PUBLIC FUNCTION isSameDayRatesEnabled
1235 |
1236 | DESCRIPTION
1237 | Checks to see if a Rule is enabled for a Schedule and an Option defined
1238 |
1239 | CALLED FROM PROCEDURES/FUNCTIONS this package and from BC4J
1240 |
1241 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1242 |
1243 | PARAMETERS
1244 | p_policy_id IN Policy Id
1245 | RETURNS
1246 | TRUE If a Rule is enabled for a Schedule and an Option defined
1247 | FALSE If a Rule is not enabled for a Schedule or an Option not defined
1248 |
1249 | MODIFICATION HISTORY
1250 | Date Author Description of Changes
1251 | 10-Nov-2005 krmenon Created
1252 |
1253 *=======================================================================*/
1254 FUNCTION isSameDayRatesEnabled ( p_policy_id ap_pol_headers.policy_id%TYPE ) RETURN VARCHAR2;
1255
1256 /*========================================================================
1257 | PUBLIC FUNCTION isNightRatesEnabled
1258 |
1259 | DESCRIPTION
1260 | Checks to see if a Rule is enabled for a Schedule and an Option defined
1261 |
1262 | CALLED FROM PROCEDURES/FUNCTIONS this package and from BC4J
1263 |
1264 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1265 |
1266 | PARAMETERS
1267 | p_policy_id IN Policy Id
1268 | RETURNS
1269 | TRUE If a Rule is enabled for a Schedule and an Option defined
1270 | FALSE If a Rule is not enabled for a Schedule or an Option not defined
1271 |
1272 | MODIFICATION HISTORY
1273 | Date Author Description of Changes
1274 | 10-Nov-2005 krmenon Created
1275 |
1276 *=======================================================================*/
1277 FUNCTION isNightRatesEnabled(p_policy_id IN ap_pol_headers.policy_id%TYPE) RETURN VARCHAR2;
1278
1279 /*========================================================================
1280 | PUBLIC FUNCTION isDateInSeason
1281 |
1282 | DESCRIPTION
1283 | Helper function to determine if a date is contained within a season.
1284 |
1285 | PARAMETERS
1286 | p_date -- Expense Date in question
1287 | p_start_of_season -- Season Start (mm/dd)
1288 | p_end_of_season -- Season End (mm/dd)
1289 | RETURNS
1290 | 'Y' if date within season.
1291 | 'N' otherwise.
1292 |
1293 | MODIFICATION HISTORY
1294 | Date Author Description of Changes
1295 | 21-Feb-2006 albowicz Created
1296 |
1297 *=======================================================================*/
1298 FUNCTION isDateInSeason (p_date IN DATE,
1299 p_start_of_season IN ap_pol_lines.start_of_season%TYPE,
1300 p_end_of_season IN ap_pol_lines.end_of_season%TYPE) RETURN VARCHAR2;
1301
1302 /*========================================================================
1303 | PUBLIC FUNCTION getPolicyLineId
1304 |
1305 | DESCRIPTION
1306 | Determines the applicable policy line given basic expense info.
1307 |
1308 | PARAMETERS
1309 | p_person_id -- Person id for whom the expense belongs.
1310 | p_expense_type_id -- Expense Type ID associated to the expense
1311 | p_expense_date -- Expense Start Date
1312 | p_location_id -- Expense Location
1313 | p_currency_code -- Reimbursement Currency Code
1314 | RETURNS
1315 | Policy Line Id -- if an applicable policy line can be found.
1316 | null -- otherwise.
1317 |
1318 | MODIFICATION HISTORY
1319 | Date Author Description of Changes
1320 | 21-Feb-2006 albowicz Created
1321 |
1322 *=======================================================================*/
1323 FUNCTION getPolicyLineId(p_person_id IN NUMBER,
1324 p_expense_type_id IN NUMBER,
1325 p_expense_date IN DATE,
1326 p_location_id IN NUMBER,
1327 p_currency_code IN VARCHAR2 ) RETURN NUMBER;
1328
1329 /*========================================================================
1330 | PUBLIC PROCEDURE getHrAssignment
1331 |
1332 | DESCRIPTION
1333 | Public helper procedure to retrieve HR assignment.
1334 |
1335 | PARAMETERS
1336 | p_person_id -- Expense Type ID associated to the expense
1337 | p_date -- Assignment date.
1338 | p_grade_id -- Returns grade id.
1339 | p_position_id -- Returns position id.
1340 | p_job_id -- Returns job id.
1341 |
1342 | MODIFICATION HISTORY
1343 | Date Author Description of Changes
1344 | 22-Feb-2006 albowicz Created
1345 |
1346 *=======================================================================*/
1347
1348 PROCEDURE getHrAssignment(p_person_id IN per_employees_x.employee_id%type,
1349 p_date IN DATE,
1350 p_grade_id OUT NOCOPY per_all_assignments_f.grade_id%type,
1351 p_position_id OUT NOCOPY per_all_assignments_f.position_id%type,
1352 p_job_id OUT NOCOPY per_all_assignments_f.job_id%type);
1353
1354
1355 /*========================================================================
1356 | PUBLIC FUNCTION checkForInvalidLines
1357 |
1358 | DESCRIPTION
1359 | Public helper procedure to validate policy lines.
1360 |
1361 | PARAMETERS
1362 | p_policy_id IN Policy Id
1363 | p_schedule_id IN Policy Schedule Id.
1364 | p_rate_type IN Rate Type (STANDARD, SAME_DAY, FIRST_PERIOD,
1365 | LAST_PERIOD, NIGHT_RATE, ADDON)
1366 | p_std_invalid OUT Count of invalid standard lines
1367 | p_first_invalid OUT Count of invalid first period lines
1368 | p_last_invalid OUT Count of invalid last period lines
1369 | p_same_invalid OUT Count of invalid same day rate lines
1370 | p_night_invalid OUT Count of invalid night rate lines
1371 | p_addon_invalid OUT Count of invalid addon lines
1372 |
1373 |
1374 | MODIFICATION HISTORY
1375 | Date Author Description of Changes
1376 | 08-Jun-2006 krmenon Created
1377 |
1378 *=======================================================================*/
1379 FUNCTION checkForInvalidLines(p_policy_id IN ap_pol_lines.POLICY_ID%type,
1380 p_schedule_id IN ap_pol_lines.SCHEDULE_PERIOD_ID%type,
1381 p_std_invalid OUT NOCOPY NUMBER,
1382 p_first_invalid OUT NOCOPY NUMBER,
1383 p_last_invalid OUT NOCOPY NUMBER,
1384 p_same_invalid OUT NOCOPY NUMBER,
1385 p_night_invalid OUT NOCOPY NUMBER,
1386 p_addon_invalid OUT NOCOPY NUMBER) RETURN VARCHAR2;
1387 /*========================================================================
1388 | PUBLIC FUNCTION activatePolicyLines
1389 |
1390 | DESCRIPTION
1391 | Public helper procedure to activate policy lines for the case where there
1392 | are more than 300 lines.
1393 |
1394 | PARAMETERS
1395 | p_policy_id IN Policy Id
1396 | p_schedule_id IN Policy Schedule Id
1397 |
1398 |
1399 | MODIFICATION HISTORY
1400 | Date Author Description of Changes
1401 | 08-Jun-2006 krmenon Created
1402 |
1403 *=======================================================================*/
1404 PROCEDURE activatePolicyLines(p_policy_id IN ap_pol_lines.POLICY_ID%type,
1405 p_schedule_id IN ap_pol_lines.SCHEDULE_PERIOD_ID%type);
1406
1407
1408 END AP_WEB_POLICY_UTILS;
[Home] [Help]