1 PACKAGE AP_WEB_POLICY_UTILS AUTHID CURRENT_USER AS
2 /* $Header: apwpolus.pls 120.20.12020000.3 2013/02/22 10:28:40 ramnagar ship $ */
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 TYPE t_locations_table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
68 pg_locations_rec t_locations_table;
69
70 --Bug16383630
71 TYPE t_locations_desc_table IS TABLE OF VARCHAR2(240) INDEX BY BINARY_INTEGER;
72 pg_locations_desc_rec t_locations_desc_table;
73
74 TYPE t_currency_table IS TABLE OF VARCHAR2(80) INDEX BY VARCHAR2(80);
75 pg_currency_rec t_currency_table;
76
77 TYPE t_category_table IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
78 pg_category_rec t_category_table;
79
80 Type t_role_table IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
81 pg_role_rec t_role_table;
82
83 /*========================================================================
84 | PUBLIC FUNCTION get_schedule_status
85 |
86 | DESCRIPTION
87 | This function fetches the status for a given schedule id.
88 |
89 | PSEUDO CODE/LOGIC
90 |
91 | PARAMETERS
92 | p_policy_id IN policy id
93 |
94 | MODIFICATION HISTORY
95 | Date Author Description of Changes
96 | 26-Sep-2002 V Nama Created
97 |
98 *=======================================================================*/
99 FUNCTION get_schedule_status(p_policy_id IN NUMBER) RETURN VARCHAR2;
100
101 /*========================================================================
102 | PUBLIC FUNCTION get_lookup_meaning
103 |
104 | DESCRIPTION
105 | This function fetches the meaning for a given lookup type and code
106 | combination. The values are cached, so the SQL is executed only
107 | once for the session.
108 |
109 | PSEUDO CODE/LOGIC
110 |
111 | PARAMETERS
112 | p_lookup_type IN lookup type
113 | p_lookup_code IN Lookup code, which is part of the lookup
114 | type in previous parameter
115 |
116 | MODIFICATION HISTORY
117 | Date Author Description of Changes
118 | 08-May-2002 J Rautiainen Created
119 |
120 *=======================================================================*/
121 FUNCTION get_lookup_meaning(p_lookup_type IN VARCHAR2,
122 p_lookup_code IN VARCHAR2) RETURN VARCHAR2;
123
124
125 /*========================================================================
126 | PUBLIC FUNCTION get_lookup_description
127 |
128 | DESCRIPTION
129 | This function fetches the instruction
130 | for a given lookup type and code combination.
131 | The values are cached, so the SQL is executed only
132 | once for the session.
133 |
134 | PSEUDO CODE/LOGIC
135 |
136 | PARAMETERS
137 | p_lookup_type IN lookup type
138 | p_lookup_code IN Lookup code, which is part of the lookup
139 | type in previous parameter
140 |
141 | MODIFICATION HISTORY
142 | Date Author Description of Changes
143 | 27-Oct-2003 R Langi Created
144 |
145 *=======================================================================*/
146 FUNCTION get_lookup_description(p_lookup_type IN VARCHAR2,
147 p_lookup_code IN VARCHAR2) RETURN VARCHAR2;
148
149
150 /*========================================================================
151 | PUBLIC FUNCTION get_high_threshold
152 |
153 | DESCRIPTION
154 | This function return high threshold for a given low value.
155 |
156 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
157 |
158 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
159 |
160 | PARAMETERS
161 | p_lookup_type IN lookup type
162 |
163 | MODIFICATION HISTORY
164 | Date Author Description of Changes
165 | 10-May-2002 J Rautiainen Created
166 |
167 *=======================================================================*/
168 FUNCTION get_high_threshold(p_policy_id IN NUMBER,
169 p_lookup_type IN VARCHAR2,
170 p_low_threshold IN NUMBER) RETURN NUMBER;
171
172 /*========================================================================
173 | PUBLIC FUNCTION get_single_org_context
174 |
175 | DESCRIPTION
176 | This function returns whether user is working in single org context.
177 |
178 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
179 | Called from BC4J on a logic deciding switcher bean behaviour
180 |
181 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
182 |
183 | RETURNS
184 | Y If user is working in single org context
185 | N If user is working in single org context
186 |
187 | PARAMETERS
188 | p_user_id IN User Id
189 |
190 | MODIFICATION HISTORY
191 | Date Author Description of Changes
192 | 13-May-2002 J Rautiainen Created
193 |
194 *=======================================================================*/
195 FUNCTION get_single_org_context(p_user_id IN NUMBER) RETURN VARCHAR2;
196
197
198 /*========================================================================
199 | PUBLIC PROCEDURE initialize_user_cat_options
200 |
201 | DESCRIPTION
202 | This procedure creates category options user context.
203 |
204 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
205 | Called from BC4J
206 |
207 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
208 |
209 | PARAMETERS
210 | p_user_id IN User Id
211 | p_category_code IN Category Code
212 |
213 | MODIFICATION HISTORY
214 | Date Author Description of Changes
215 | 14-May-2002 J Rautiainen Created
216 |
217 *=======================================================================*/
218 PROCEDURE initialize_user_cat_options(p_user_id IN NUMBER,
219 p_category_code IN VARCHAR2);
220
221 /*========================================================================
222 | PUBLIC FUNCTION location_translation_complete
223 |
224 | DESCRIPTION
225 | This function returns whether all locations have been translated
226 | for a given language.
227 |
228 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
229 | Called from BC4J
230 |
231 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
232 |
233 | RETURNS
234 | Y If location has been translated for the given language
235 | N If location has not been translated for the given language
236 |
237 | PARAMETERS
238 | p_language_code IN Language Code
239 |
240 | MODIFICATION HISTORY
241 | Date Author Description of Changes
242 | 21-May-2002 J Rautiainen Created
243 |
244 *=======================================================================*/
245 FUNCTION location_translation_complete(p_language_code IN VARCHAR2) RETURN VARCHAR2;
246
247 /*========================================================================
248 | PUBLIC FUNCTION get_employee_name
249 |
250 | DESCRIPTION
251 | This function returns the employee name for a given user.
252 |
253 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
254 | Called from BC4J, needed for the LineHistoryVO, which cannot have joins
255 | due to connect by clause.
256 |
257 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
258 |
259 | RETURNS
260 | The employee name for the given user. If employee ID is not defined for
261 | the user, then the username is returned.
262 |
263 | PARAMETERS
264 | p_user_id IN User identifier
265 |
266 | MODIFICATION HISTORY
267 | Date Author Description of Changes
268 | 25-May-2002 J Rautiainen Created
269 |
270 *=======================================================================*/
271 FUNCTION get_employee_name(p_user_id IN NUMBER) RETURN VARCHAR2;
272
273 /*========================================================================
274 | PUBLIC FUNCTION get_location
275 |
276 | DESCRIPTION
277 | This function returns location.
278 |
279 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
280 | Called from BC4J, needed for the LineHistoryVO, which cannot have joins
281 | due to connect by clause.
282 |
283 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
284 |
285 | RETURNS
286 | Location
287 |
288 | PARAMETERS
289 | p_location_id IN Location identifier
290 |
291 | MODIFICATION HISTORY
292 | Date Author Description of Changes
293 | 25-May-2002 J Rautiainen Created
294 |
295 *=======================================================================*/
296 FUNCTION get_location(p_location_id IN NUMBER) RETURN VARCHAR2;
297
298 /*========================================================================
299 | PUBLIC FUNCTION get_currency_display
300 |
301 | DESCRIPTION
302 | This function returns currency display.
303 |
304 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
305 | Called from BC4J, needed for
306 | PolicyLinesVO/PolicyLinesAdvancedSearchCriteriaVO/CurrencyScheduleOptionsVO
307 |
308 |
309 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
310 |
311 | RETURNS
312 | Currency Name||' - '||Currency Code
313 |
314 | PARAMETERS
315 | p_currency_code IN Currency Code
316 |
317 | MODIFICATION HISTORY
318 | Date Author Description of Changes
319 | 17-June-2002 R Langi Created
320 |
321 *=======================================================================*/
322 FUNCTION get_currency_display(p_currency_code IN VARCHAR2) RETURN VARCHAR2;
323
324 /*========================================================================
325 | PUBLIC FUNCTION get_role
326 |
327 | DESCRIPTION
328 | This function returns role.
329 |
330 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
331 | Called from BC4J, needed for the LineHistoryVO, which cannot have joins
332 | due to connect by clause.
333 |
334 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
335 |
336 | RETURNS
337 | Role
338 |
339 | PARAMETERS
340 | p_policy_line_id IN Policy Line identifier
341 |
342 | MODIFICATION HISTORY
343 | Date Author Description of Changes
344 | 25-May-2002 J Rautiainen Created
345 |
346 *=======================================================================*/
347 FUNCTION get_role(p_policy_line_id IN NUMBER) RETURN VARCHAR2;
348
349 /*========================================================================
350 | PUBLIC FUNCTION get_role_for_so
351 |
352 | DESCRIPTION
353 | This function returns role for a schedule option.
354 |
355 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
356 | Called from BC4J, needed for RoleScheduleOptionsVO/PolicyLinesAdvancedSearchVO
357 |
358 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
359 |
360 | RETURNS
361 | Role
362 |
363 | PARAMETERS
364 | p_policy_schedule_option_id IN Policy Schedule Option identifier
365 |
366 | MODIFICATION HISTORY
367 | Date Author Description of Changes
368 | 17-June-2002 R Langi Created
369 |
370 *=======================================================================*/
371 FUNCTION get_role_for_so(p_policy_schedule_option_id IN NUMBER) RETURN VARCHAR2;
372
373 /*========================================================================
374 | PUBLIC FUNCTION get_role
375 |
376 | DESCRIPTION
377 | This function returns role.
378 |
379 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
380 | Called from local overloaded get_role function
381 |
382 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
383 |
384 | RETURNS
385 | Role
386 |
387 | PARAMETERS
388 | p_role_code IN Role Code, one of the following: GRADE, JOB_GROUP, POSITION
389 | p_role_id IN Location identifier
390 |
391 | MODIFICATION HISTORY
392 | Date Author Description of Changes
393 | 30-May-2002 J Rautiainen Created
394 |
395 *=======================================================================*/
396 FUNCTION get_role(p_role_code VARCHAR2, p_role_id IN NUMBER) RETURN VARCHAR2;
397
398 /*========================================================================
399 | PUBLIC FUNCTION get_threshold
400 |
401 | DESCRIPTION
402 | This function returns threshold string.
403 |
404 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
405 | Called from BC4J, needed for the LineHistoryVO, which cannot have joins
406 | due to connect by clause.
407 |
408 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
409 |
410 | RETURNS
411 | Threshold
412 |
413 | PARAMETERS
414 | p_range_low IN Range low threshold
415 | p_range_high IN Range high threshold
416 | p_category_code IN Cagetory Code
417 |
418 | MODIFICATION HISTORY
419 | Date Author Description of Changes
420 | 25-May-2002 J Rautiainen Created
421 |
422 *=======================================================================*/
423 FUNCTION get_threshold(p_range_low IN NUMBER,
424 p_range_high IN NUMBER,
425 p_category_code IN VARCHAR2) RETURN VARCHAR2;
426
427 /*========================================================================
428 | PUBLIC PROCEDURE initialize_user_exrate_options
429 |
430 | DESCRIPTION
431 | This procedure creates category options user context.
432 |
433 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
434 | Called from BC4J
435 |
436 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
437 |
438 | PARAMETERS
439 | p_user_id IN User Id
440 | p_category_code IN Category Code
441 |
442 | MODIFICATION HISTORY
443 | Date Author Description of Changes
444 | 30-May-2002 V Nama Created
445 |
446 *=======================================================================*/
447 PROCEDURE initialize_user_exrate_options(p_user_id IN NUMBER);
448
449 /*========================================================================
450 | PUBLIC FUNCTION get_context_tab_enabled
451 |
452 | DESCRIPTION
453 | This function returns whether context tab should be shown or hidden.
454 | Context tab is not showed if:
455 | - Single org installation
456 | - Functional security does not allow it
457 |
458 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
459 | Called from TabCO.java
460 |
461 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
462 |
463 | RETURNS
464 | 'Y' If policy tab should be displayed
465 | 'N' If policy tab should be displayed
466 |
467 | PARAMETERS
468 |
469 | MODIFICATION HISTORY
470 | Date Author Description of Changes
471 | 10-Jun-2002 J Rautiainen Created
472 |
473 *=======================================================================*/
474 FUNCTION get_context_tab_enabled RETURN VARCHAR2;
475
476
477 /*========================================================================
478 | PUBLIC FUNCTION getHighEndOfThreshold
479 |
480 | DESCRIPTION
481 |
482 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
483 |
484 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
485 |
486 | PARAMETERS
487 | p_policy_id IN Policy Identifier
488 | p_threshold IN Threshold
489 |
490 | MODIFICATION HISTORY
491 | Date Author Description of Changes
492 | 16-May-2002 R Langi Created
493 |
494 *=======================================================================*/
495 FUNCTION getHighEndOfThreshold(p_policy_id IN ap_pol_headers.policy_id%TYPE,
496 p_threshold IN ap_pol_schedule_options.threshold%TYPE) RETURN ap_pol_schedule_options.threshold%TYPE;
497
498 /*========================================================================
499 | PUBLIC FUNCTION getHighEndOfThreshold
500 |
501 | DESCRIPTION
502 |
503 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
504 |
505 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
506 |
507 | PARAMETERS
508 | p_policy_id IN Policy Identifier
509 | p_threshold IN Threshold
510 | p_rate_type IN Rate Type (STANDARD, FIRST, LAST)
511 |
512 | MODIFICATION HISTORY
513 | Date Author Description of Changes
514 | 01-Nov-2005 krmenon Created
515 |
516 *=======================================================================*/
517 FUNCTION getHighEndOfThreshold(p_policy_id IN ap_pol_headers.policy_id%TYPE,
518 p_threshold IN ap_pol_schedule_options.threshold%TYPE,
519 p_rate_type IN ap_pol_schedule_options.rate_type_code%TYPE) RETURN ap_pol_schedule_options.threshold%TYPE;
520
521 /*========================================================================
522 | PUBLIC FUNCTION getPolicyCategoryCode
523 |
524 | DESCRIPTION
525 | Returns the Category Code for a Policy
526 |
527 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
528 |
529 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
530 |
531 | PARAMETERS
532 | p_policy_id IN Policy Identifier
533 |
534 | MODIFICATION HISTORY
535 | Date Author Description of Changes
536 | 16-May-2002 R Langi Created
537 |
538 *=======================================================================*/
539 FUNCTION getPolicyCategoryCode(p_policy_id IN ap_pol_headers.policy_id%TYPE) RETURN ap_pol_headers.category_code%TYPE;
540
541 /*========================================================================
542 | PUBLIC FUNCTION checkRuleOption
543 |
544 | DESCRIPTION
545 | Checks to see if a Rule is enabled for a Schedule and an Option defined
546 |
547 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
548 |
549 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
550 | isLocationEnabled
551 | isRoleEnabled
552 | isCurrencyEnabled
553 | isVehicleCategoryEnabled
554 | isVehicleTypeEnabled
555 | isFuelTypeEnabled
556 | isThresholdsEnabled
557 |
558 | PARAMETERS
559 |
560 | RETURNS
561 | 'Y' If a Rule is enabled for a Schedule and an Option defined
562 | 'N' If a Rule is not enabled for a Schedule or an Option not defined
563 |
564 | MODIFICATION HISTORY
565 | Date Author Description of Changes
566 | 16-May-2002 R Langi Created
567 |
568 *=======================================================================*/
569 FUNCTION checkRuleOption(p_policy_id IN ap_pol_headers.policy_id%TYPE,
570 p_rule IN VARCHAR2) RETURN VARCHAR2;
571
572 /*========================================================================
573 | PUBLIC FUNCTION getUnionStmtForRuleOption
574 |
575 | DESCRIPTION
576 | If a Rule is not enabled or Schedule Option not defined for an enabled Rule
577 | this will return a UNION null statement which is used for perumutatePolicyLines()
578 |
579 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
580 |
581 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
582 |
583 | PARAMETERS
584 | p_policy_id IN Policy Identifier
585 | p_rule IN Schedule Option Type
586 |
587 | MODIFICATION HISTORY
588 | Date Author Description of Changes
589 | 16-May-2002 R Langi Created
590 |
591 *=======================================================================*/
592 FUNCTION getUnionStmtForRuleOption(p_policy_id IN ap_pol_headers.policy_id%TYPE,
593 p_rule IN VARCHAR2) RETURN VARCHAR2;
594
595 /*========================================================================
596 | PUBLIC PROCEDURE permutatePolicyLines
597 |
598 | DESCRIPTION
599 | - if a Rule is not enabled or Schedule Option not defined for an enabled Rule then remove the
600 | obsoleted Policy Line
601 | - this will never recreate permutated lines based on existing option (rerunnable)
602 | - if option doesn't exist then creates permutation for new option
603 | - if option end dated then set Policy Line status to inactive
604 |
605 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
606 |
607 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
608 |
609 | PARAMETERS
610 | p_user_id IN User Identifier
611 | p_policy_id IN Policy Identifier
612 |
613 | MODIFICATION HISTORY
614 | Date Author Description of Changes
615 | 16-May-2002 R Langi Created
616 |
617 *=======================================================================*/
618 PROCEDURE permutatePolicyLines(p_user_id IN NUMBER,
619 p_policy_id IN ap_pol_headers.policy_id%TYPE);
620
621 /*========================================================================
622 | PUBLIC PROCEDURE removeObsoletedPolicyLines
623 |
624 | DESCRIPTION
625 | - if a Rule is not enabled or Schedule Option not defined for an enabled Rule then remove the
626 | obsoleted Policy Line
627 |
628 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
629 |
630 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
631 |
632 | PARAMETERS
633 | p_policy_id IN Policy Identifier
634 |
635 | MODIFICATION HISTORY
636 | Date Author Description of Changes
637 | 16-May-2002 R Langi Created
638 |
639 *=======================================================================*/
640 PROCEDURE removeObsoletedPolicyLines(p_policy_id IN ap_pol_headers.policy_id%TYPE);
641
642 /*========================================================================
643 | PUBLIC PROCEDURE updateInactivePolicyLines
644 |
645 | DESCRIPTION
646 | - if option end dated then set Policy Line status to inactive
647 |
648 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
649 |
650 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
651 |
652 | PARAMETERS
653 | p_policy_id IN Policy Identifier
654 |
655 | MODIFICATION HISTORY
656 | Date Author Description of Changes
657 | 16-May-2002 R Langi Created
658 |
659 *=======================================================================*/
660 PROCEDURE updateInactivePolicyLines(p_policy_id IN ap_pol_headers.policy_id%TYPE);
661
662 /*========================================================================
663 | PUBLIC PROCEDURE duplicatePolicyLines
664 |
665 | DESCRIPTION
666 | Duplicates Policy Lines from one Policy Schedule Period to another
667 |
668 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
669 |
670 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
671 |
672 | PARAMETERS
673 | p_user_id IN User Identifier
674 | p_from_policy_id IN Policy Identifier to duplicate From
675 | p_from_schedule_period_id IN Policy Schedule Period Identifier to duplicate From
676 | p_to_policy_id IN Policy Identifier to duplicate To
677 | p_to_schedule_period_id IN Policy Schedule Period Identifier to duplicate To
678 |
679 | MODIFICATION HISTORY
680 | Date Author Description of Changes
681 | 16-May-2002 R Langi Created
682 |
683 *=======================================================================*/
684 PROCEDURE duplicatePolicyLines(p_user_id IN NUMBER,
685 p_from_policy_id IN ap_pol_headers.policy_id%TYPE,
686 p_from_schedule_period_id IN ap_pol_schedule_periods.schedule_period_id%TYPE,
687 p_to_policy_id IN ap_pol_headers.policy_id%TYPE,
688 p_to_schedule_period_id IN ap_pol_schedule_periods.schedule_period_id%TYPE);
689
690 /*========================================================================
691 | PUBLIC PROCEDURE preservePolicyLine
692 |
693 | DESCRIPTION
694 | Preserve a modified Active Policy Line
695 |
696 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
697 |
698 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
699 |
700 | PARAMETERS
701 | p_user_id IN User Identifier
702 | p_policy_id IN Policy Identifier
703 | p_schedule_period_id IN Policy Schedule Period Identifier
704 | p_policy_line_id IN Policy Line Identifier to preserve
705 |
706 | MODIFICATION HISTORY
707 | Date Author Description of Changes
708 | 12-Aug-2002 R Langi Created
709 |
710 *=======================================================================*/
711 PROCEDURE preservePolicyLine(p_user_id IN NUMBER,
712 p_policy_id IN ap_pol_lines.policy_id%TYPE,
713 p_schedule_period_id IN ap_pol_lines.schedule_period_id%TYPE,
714 p_policy_line_id IN ap_pol_lines.policy_line_id%TYPE);
715
716 /*========================================================================
717 | PUBLIC PROCEDURE archivePreservedPolicyLines
718 |
719 | DESCRIPTION
720 | Archive and remove a preserved Active Policy Line after it has been reactivated
721 |
722 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
723 |
724 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
725 |
726 | PARAMETERS
727 | p_user_id IN User Identifier
728 | p_policy_id IN Policy Identifier
729 |
730 | MODIFICATION HISTORY
731 | Date Author Description of Changes
732 | 12-Aug-2002 R Langi Created
733 |
734 *=======================================================================*/
735 PROCEDURE archivePreservedPolicyLines(p_user_id IN NUMBER,
736 p_policy_id IN ap_pol_lines.policy_id%TYPE);
737
738 /*========================================================================
739 | PUBLIC FUNCTION createSchedulePeriod
740 |
741 | DESCRIPTION
742 | Creates a new Policy Schedule Period
743 |
744 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
745 |
746 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
747 |
748 | PARAMETERS
749 | p_user_id IN User Identifier
750 | p_policy_id IN Policy Identifier
751 | p_schedule_period_name IN Schedule Period Name
752 | p_start_date IN Start Date
753 | p_end_date IN End Date
754 | p_rate_per_passenger IN Rate Per Passenger
755 | p_min_days IN Minimum Number of Days
756 | p_tolerance IN Tolerance
757 | p_min_rate_per_period IN Minimum Rate per Period
758 | p_max_breakfast_deduction IN Maximum Breakfast Deduction Allowed per Period
759 | p_max_lunch_deduction IN Maximum Lunch Deduction Allowed per Period
760 | p_max_dinner_deduction IN Maximum Dinner Deduction Allowed per Period
761 | p_first_day_rate IN First day rate
762 | p_last_day_rate IN Last day rate
763 |
764 | MODIFICATION HISTORY
765 | Date Author Description of Changes
766 | 16-May-2002 R Langi Created
767 |
768 *=======================================================================*/
769 FUNCTION createSchedulePeriod(p_user_id IN NUMBER,
770 p_policy_id IN ap_pol_schedule_periods.policy_id%TYPE,
771 p_schedule_period_name IN ap_pol_schedule_periods.schedule_period_name%TYPE,
772 p_start_date IN ap_pol_schedule_periods.start_date%TYPE,
773 p_end_date IN ap_pol_schedule_periods.end_date%TYPE DEFAULT NULL,
774 p_rate_per_passenger IN ap_pol_schedule_periods.rate_per_passenger%TYPE,
775 p_min_days IN ap_pol_schedule_periods.min_days%TYPE,
776 p_tolerance IN ap_pol_schedule_periods.tolerance%TYPE,
777 p_min_rate_per_period IN ap_pol_schedule_periods.min_rate_per_period%TYPE,
778 p_max_breakfast_deduction IN ap_pol_schedule_periods.max_breakfast_deduction_amt%TYPE,
779 p_max_lunch_deduction IN ap_pol_schedule_periods.max_lunch_deduction_amt%TYPE,
780 p_max_dinner_deduction IN ap_pol_schedule_periods.max_dinner_deduction_amt%TYPE,
781 p_first_day_rate IN ap_pol_schedule_periods.first_day_rate%TYPE,
782 p_last_day_rate IN ap_pol_schedule_periods.last_day_rate%TYPE,
783 p_reimbursement_percentage In ap_pol_schedule_periods.reimbursement_percentage%TYPE) RETURN ap_pol_schedule_periods.schedule_period_id%TYPE;
784
785 /*========================================================================
786 | PUBLIC FUNCTION massUpdateValue
787 |
788 | DESCRIPTION
789 | Using a rounding rule and percentage to update by, a value is returned
790 |
791 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
792 |
793 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
794 |
795 | PARAMETERS
796 | p_value IN Value to perform an update on
797 | p_update_by IN Percentage to update the value by
798 | p_rounding_rule IN Rounding rule to use after the value has been updated
799 |
800 | MODIFICATION HISTORY
801 | Date Author Description of Changes
802 | 16-May-2002 R Langi Created
803 |
804 *=======================================================================*/
805 FUNCTION massUpdateValue(p_value IN NUMBER,
806 p_update_by IN NUMBER,
807 p_rounding_rule IN VARCHAR2) RETURN NUMBER;
808
809 /*========================================================================
810 | PUBLIC PROCEDURE duplicatePolicy
811 |
812 | DESCRIPTION
813 | Duplicates a Policy Schedule (General Information/Options/Periods/Lines)
814 |
815 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
816 | createSchedulePeriod
817 | duplicatePolicyLines
818 |
819 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
820 | duplicatePolicyHeader
821 | duplicatePolicyScheduleOptions
822 |
823 | PARAMETERS
824 | p_user_id IN User Identifier
825 | p_from_policy_id IN Policy Identifier to duplicate From
826 | p_new_policy_id IN New Policy Identifier containing the duplicated Policy
827 |
828 | MODIFICATION HISTORY
829 | Date Author Description of Changes
830 | 16-May-2002 R Langi Created
831 |
832 *=======================================================================*/
833 PROCEDURE duplicatePolicy(p_user_id IN NUMBER,
834 p_from_policy_id IN ap_pol_headers.policy_id%TYPE,
835 p_new_policy_id OUT NOCOPY ap_pol_headers.policy_id%TYPE);
836
837 /*========================================================================
838 | PUBLIC FUNCTION active_option_exists
839 |
840 | DESCRIPTION
841 | Checks whether a active schedule option exists for a option
842 |
843 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
844 |
845 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
846 |
847 | PARAMETERS
848 | p_option_type IN Option type, required
849 | p_option_code IN Option code, optional
850 | p_threshold IN Threshold, optional
851 | p_role_id IN Role Id, optional
852 | p_location_id IN Location Id, optional
853 | p_currency_code IN Currency Code, optional
854 | p_end_date IN End Date, optional
855 |
856 | RETURNS
857 | Y If active option exists
858 | N If active option does not exist
859 |
860 | MODIFICATION HISTORY
861 | Date Author Description of Changes
862 | 26-Jun-2002 J Rautiainen Created
863 |
864 *=======================================================================*/
865 FUNCTION active_option_exists(p_option_type IN VARCHAR2,
866 p_option_code IN VARCHAR2,
867 p_threshold IN NUMBER,
868 p_role_id IN NUMBER,
869 p_location_id IN NUMBER,
870 p_currency_code IN VARCHAR2,
871 p_end_date IN DATE) RETURN VARCHAR2;
872
873
874 /*========================================================================
875 | PUBLIC PROCEDURE end_date_active_loc_options
876 |
877 | DESCRIPTION
878 | If locations are end dated on location definitions then corresponding
879 | active locations on schedule options should be end dated provided the
880 | location option's end date is null or later than the defined end date.
881 |
882 | NOTES
883 | Created vide bug 2560275
884 |
885 | MODIFICATION HISTORY
886 | Date Author Description of Changes
887 | 05-Feb-2004 V Nama Created
888 |
889 *=======================================================================*/
890 PROCEDURE end_date_active_loc_options;
891
892 /*========================================================================
893 | PUBLIC FUNCTION does_location_exist
894 |
895 | DESCRIPTION
896 | Checks whether a locations exists
897 |
898 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
899 |
900 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
901 |
902 | PARAMETERS
903 | NONE
904 |
905 | RETURNS
906 | Y If locations exist
907 | N If locations does not exist
908 |
909 | MODIFICATION HISTORY
910 | Date Author Description of Changes
911 | 26-Jun-2002 J Rautiainen Created
912 |
913 *=======================================================================*/
914 FUNCTION does_location_exist RETURN VARCHAR2;
915
916 /*========================================================================
917 | PUBLIC PROCEDURE status_saved_sched_opts
918 |
919 | DESCRIPTION
920 | This procedure sets status of schedule options to 'SAVED' for
921 | the given policy_id
922 |
923 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
924 | Called from BC4J
925 |
926 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
927 |
928 | PARAMETERS
929 | p_policy_id IN Policy Id
930 |
931 | MODIFICATION HISTORY
932 | Date Author Description of Changes
933 | 17-JUL-2002 Mohammad Shoaib Jamall Created
934 |
935 *=======================================================================*/
936 PROCEDURE status_saved_sched_opts(p_policy_id IN NUMBER);
937
938 /*========================================================================
939 | PUBLIC PROCEDURE status_active_sched_opts
940 |
941 | DESCRIPTION
942 | This procedure sets status of schedule options to 'ACTIVE' for
943 | the given policy_id
944 |
945 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
946 | Called from BC4J
947 |
948 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
949 |
950 | PARAMETERS
951 | p_policy_id IN Policy Id
952 |
953 | MODIFICATION HISTORY
954 | Date Author Description of Changes
955 | 17-JUL-2002 Mohammad Shoaib Jamall Created
956 |
957 *=======================================================================*/
958 PROCEDURE status_active_sched_opts(p_policy_id IN NUMBER);
959
960 /*========================================================================
961 | PUBLIC PROCEDURE set_status_pol_sched_opts
962 |
963 | DESCRIPTION
964 | This procedure sets status of schedule options for the given policy_id
965 |
966 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
967 | Called from BC4J
968 |
969 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
970 |
971 | PARAMETERS
972 | p_policy_id IN Policy Id
973 | p_status_code IN Status Code
974 |
975 | MODIFICATION HISTORY
976 | Date Author Description of Changes
977 | 17-JUL-2002 Mohammad Shoaib Jamall Created
978 |
979 *=======================================================================*/
980 PROCEDURE set_status_pol_sched_opts(p_policy_id IN NUMBER,
981 p_status_code IN VARCHAR2);
982
983 /*========================================================================
984 | PUBLIC FUNCTION are_exp_type_enddates_capped
985 |
986 | DESCRIPTION
987 | Checks to see if end dates on expense templates are capped
988 |
989 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
990 |
991 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
992 |
993 | PARAMETERS
994 | p_policy_id IN Policy Identifier
995 | p_end_date IN End Date
996 |
997 | MODIFICATION HISTORY
998 | Date Author Description of Changes
999 | 31-JUL-2002 Mohammad Shoaib Jamall Created
1000 |
1001 *=======================================================================*/
1002 FUNCTION are_exp_type_enddates_capped(p_policy_id IN ap_pol_headers.policy_id%TYPE,
1003 p_end_date IN ap_pol_headers.end_date%TYPE DEFAULT NULL) RETURN VARCHAR2;
1004
1005 /*========================================================================
1006 | PUBLIC FUNCTION cap_expense_type_enddates
1007 |
1008 | DESCRIPTION
1009 | Caps end dates on expense type with p_end_date
1010 |
1011 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1012 |
1013 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1014 |
1015 | PARAMETERS
1016 | p_policy_id IN Policy Identifier
1017 | p_end_date IN End Date
1018 |
1019 | MODIFICATION HISTORY
1020 | Date Author Description of Changes
1021 | 31-JUL-2002 Mohammad Shoaib Jamall Created
1022 |
1023 *=======================================================================*/
1024 PROCEDURE cap_expense_type_enddates(p_policy_id IN ap_pol_headers.policy_id%TYPE,
1025 p_end_date IN ap_pol_headers.end_date%TYPE DEFAULT NULL);
1026
1027
1028 /*========================================================================
1029 | PUBLIC PROCEDURE initialize_user_expense_options
1030 |
1031 | DESCRIPTION
1032 | This procedure creates expense options for user context.
1033 |
1034 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1035 | Called from BC4J
1036 |
1037 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1038 |
1039 | PARAMETERS
1040 | p_user_id IN User Id
1041 |
1042 | MODIFICATION HISTORY
1043 | Date Author Description of Changes
1044 | 01-Jul-2003 J Rautiainen Created
1045 |
1046 *=======================================================================*/
1047 PROCEDURE init_user_expense_options(p_user_id IN NUMBER);
1048
1049 /*========================================================================
1050 | PUBLIC FUNCTION format_minutes_to_hour_minutes
1051 |
1052 | DESCRIPTION
1053 |
1054 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1055 |
1056 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1057 |
1058 | PARAMETERS
1059 | p_lookup_type IN lookup type
1060 |
1061 | MODIFICATION HISTORY
1062 | Date Author Description of Changes
1063 | 10-May-2002 J Rautiainen Created
1064 |
1065 *=======================================================================*/
1066 FUNCTION format_minutes_to_hour_minutes(p_minutes IN NUMBER) RETURN VARCHAR2;
1067
1068 /*========================================================================
1069 | PUBLIC FUNCTION get_hours_from_threshold
1070 |
1071 | DESCRIPTION
1072 | gets hours from the threshold value stored in minutes
1073 |
1074 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1075 |
1076 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1077 |
1078 | PARAMETERS
1079 | p_lookup_type IN lookup type
1080 |
1081 | MODIFICATION HISTORY
1082 | Date Author Description of Changes
1083 | 10-May-2002 J Rautiainen Created
1084 |
1085 *=======================================================================*/
1086 FUNCTION get_hours_from_threshold(p_threshold IN NUMBER) RETURN NUMBER;
1087
1088 /*========================================================================
1089 | PUBLIC FUNCTION get_minutes_threshold
1090 |
1091 | DESCRIPTION
1092 | converts threshold stored in minutes to hours:mins and returns mins
1093 |
1094 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1095 |
1096 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1097 |
1098 | PARAMETERS
1099 | p_lookup_type IN lookup type
1100 |
1101 | MODIFICATION HISTORY
1102 | Date Author Description of Changes
1103 | 10-May-2002 J Rautiainen Created
1104 |
1105 *=======================================================================*/
1106 FUNCTION get_minutes_from_threshold(p_threshold IN NUMBER) RETURN NUMBER;
1107
1108 /*========================================================================
1109 | PUBLIC PROCEDURE deletePolicySchedule
1110 |
1111 | DESCRIPTION
1112 | This procedure deletes a Policy Schedule
1113 |
1114 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1115 | Called from BC4J
1116 |
1117 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1118 |
1119 | PARAMETERS
1120 | p_policy_id IN Policy ID
1121 |
1122 | MODIFICATION HISTORY
1123 | Date Author Description of Changes
1124 | 22-Jul-2005 Sameer Saxena Created
1125 |
1126 *=======================================================================*/
1127 PROCEDURE deletePolicySchedule(p_policy_id IN NUMBER);
1128
1129 /*========================================================================
1130 | PUBLIC PROCEDURE getPolicyLinesCount
1131 |
1132 | DESCRIPTION
1133 | This procedure returns the number of lines for a policy schedule
1134 |
1135 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1136 | Called from BC4J to prevent querying large number of rows
1137 | during initialization.
1138 |
1139 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1140 |
1141 | PARAMETERS
1142 | p_schedule_period_id IN Policy Schedule Period ID
1143 |
1144 | MODIFICATION HISTORY
1145 | Date Author Description of Changes
1146 | 18-Oct-2005 krmenon Created
1147 |
1148 *=======================================================================*/
1149 FUNCTION getPolicyLinesCount(p_schedule_period_id IN NUMBER) RETURN NUMBER;
1150
1151 /*========================================================================
1152 | PUBLIC PROCEDURE getSingleTokenMessage
1153 |
1154 | DESCRIPTION
1155 | This function returns the fnd message which has a single token
1156 |
1157 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1158 | Called from BC4J and is used in the JRAD for setting column headers
1159 |
1160 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1161 |
1162 | PARAMETERS
1163 | p_message_name IN FND Message Name
1164 | p_token IN FND Message Token
1165 | p_token_value IN FND Message Token Value
1166 |
1167 | MODIFICATION HISTORY
1168 | Date Author Description of Changes
1169 | 18-Oct-2005 krmenon Created
1170 |
1171 *=======================================================================*/
1172 FUNCTION getSingleTokenMessage( p_message_name IN VARCHAR2,
1173 p_token IN VARCHAR2,
1174 p_token_value IN VARCHAR2 ) RETURN VARCHAR2;
1175
1176
1177 /*========================================================================
1178 | PUBLIC FUNCTION get_per_diem_type_meaning
1179 |
1180 | DESCRIPTION
1181 | This function fetches the meaning for a given lookup type and code
1182 | combination. The values are cached, so the SQL is executed only
1183 | once for the session.
1184 |
1185 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1186 | BC4J objects
1187 |
1188 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1189 | DBMS_UTILITY.get_hash_value
1190 |
1191 | PARAMETERS
1192 | p_source IN Source (NULL, CONUS)
1193 | p_lookup_code IN Lookup code, which is part of the lookup
1194 | type in previous parameter
1195 |
1196 | MODIFICATION HISTORY
1197 | Date Author Description of Changes
1198 | 08-May-2002 J Rautiainen Created
1199 |
1200 *=======================================================================*/
1201 FUNCTION get_per_diem_type_meaning(p_source IN VARCHAR2,
1202 p_lookup_code IN VARCHAR2) RETURN VARCHAR2;
1203
1204 /*========================================================================
1205 | PUBLIC FUNCTION isFirstPeriodRatesEnabled
1206 |
1207 | DESCRIPTION
1208 | Checks to see if a Rule is enabled for a Schedule and an Option defined
1209 |
1210 | CALLED FROM PROCEDURES/FUNCTIONS this package and from BC4J
1211 |
1212 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1213 |
1214 | PARAMETERS
1215 | p_policy_id IN Policy Id
1216 | RETURNS
1217 | TRUE If a Rule is enabled for a Schedule and an Option defined
1218 | FALSE If a Rule is not enabled for a Schedule or an Option not defined
1219 |
1220 | MODIFICATION HISTORY
1221 | Date Author Description of Changes
1222 | 10-Nov-2005 krmenon Created
1223 |
1224 *=======================================================================*/
1225 FUNCTION isFirstPeriodRatesEnabled ( p_policy_id ap_pol_headers.policy_id%TYPE ) RETURN VARCHAR2;
1226
1227 /*========================================================================
1228 | PUBLIC FUNCTION isLastPeriodRatesEnabled
1229 |
1230 | DESCRIPTION
1231 | Checks to see if a Rule is enabled for a Schedule and an Option defined
1232 |
1233 | CALLED FROM PROCEDURES/FUNCTIONS this package and from BC4J
1234 |
1235 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1236 |
1237 | PARAMETERS
1238 | p_policy_id IN Policy Id
1239 | RETURNS
1240 | TRUE If a Rule is enabled for a Schedule and an Option defined
1241 | FALSE If a Rule is not enabled for a Schedule or an Option not defined
1242 |
1243 | MODIFICATION HISTORY
1244 | Date Author Description of Changes
1245 | 10-Nov-2005 krmenon Created
1246 |
1247 *=======================================================================*/
1248 FUNCTION isLastPeriodRatesEnabled ( p_policy_id ap_pol_headers.policy_id%TYPE ) RETURN VARCHAR2;
1249
1250 /*========================================================================
1251 | PUBLIC FUNCTION isSameDayRatesEnabled
1252 |
1253 | DESCRIPTION
1254 | Checks to see if a Rule is enabled for a Schedule and an Option defined
1255 |
1256 | CALLED FROM PROCEDURES/FUNCTIONS this package and from BC4J
1257 |
1258 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1259 |
1260 | PARAMETERS
1261 | p_policy_id IN Policy Id
1262 | RETURNS
1263 | TRUE If a Rule is enabled for a Schedule and an Option defined
1264 | FALSE If a Rule is not enabled for a Schedule or an Option not defined
1265 |
1266 | MODIFICATION HISTORY
1267 | Date Author Description of Changes
1268 | 10-Nov-2005 krmenon Created
1269 |
1270 *=======================================================================*/
1271 FUNCTION isSameDayRatesEnabled ( p_policy_id ap_pol_headers.policy_id%TYPE ) RETURN VARCHAR2;
1272
1273 /*========================================================================
1274 | PUBLIC FUNCTION isNightRatesEnabled
1275 |
1276 | DESCRIPTION
1277 | Checks to see if a Rule is enabled for a Schedule and an Option defined
1278 |
1279 | CALLED FROM PROCEDURES/FUNCTIONS this package and from BC4J
1280 |
1281 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1282 |
1283 | PARAMETERS
1284 | p_policy_id IN Policy Id
1285 | RETURNS
1286 | TRUE If a Rule is enabled for a Schedule and an Option defined
1287 | FALSE If a Rule is not enabled for a Schedule or an Option not defined
1288 |
1289 | MODIFICATION HISTORY
1290 | Date Author Description of Changes
1291 | 10-Nov-2005 krmenon Created
1292 |
1293 *=======================================================================*/
1294 FUNCTION isNightRatesEnabled(p_policy_id IN ap_pol_headers.policy_id%TYPE) RETURN VARCHAR2;
1295
1296 /*========================================================================
1297 | PUBLIC FUNCTION isDateInSeason
1298 |
1299 | DESCRIPTION
1300 | Helper function to determine if a date is contained within a season.
1301 |
1302 | PARAMETERS
1303 | p_date -- Expense Date in question
1304 | p_start_of_season -- Season Start (mm/dd)
1305 | p_end_of_season -- Season End (mm/dd)
1306 | RETURNS
1307 | 'Y' if date within season.
1308 | 'N' otherwise.
1309 |
1310 | MODIFICATION HISTORY
1311 | Date Author Description of Changes
1312 | 21-Feb-2006 albowicz Created
1313 |
1314 *=======================================================================*/
1315 FUNCTION isDateInSeason (p_date IN DATE,
1316 p_start_of_season IN ap_pol_lines.start_of_season%TYPE,
1317 p_end_of_season IN ap_pol_lines.end_of_season%TYPE) RETURN VARCHAR2;
1318
1319 /*========================================================================
1320 | PUBLIC FUNCTION getPolicyLineId
1321 |
1322 | DESCRIPTION
1323 | Determines the applicable policy line given basic expense info.
1324 |
1325 | PARAMETERS
1326 | p_person_id -- Person id for whom the expense belongs.
1327 | p_expense_type_id -- Expense Type ID associated to the expense
1328 | p_expense_date -- Expense Start Date
1329 | p_location_id -- Expense Location
1330 | p_currency_code -- Reimbursement Currency Code
1331 | RETURNS
1332 | Policy Line Id -- if an applicable policy line can be found.
1333 | null -- otherwise.
1334 |
1335 | MODIFICATION HISTORY
1336 | Date Author Description of Changes
1337 | 21-Feb-2006 albowicz Created
1338 |
1339 *=======================================================================*/
1340 FUNCTION getPolicyLineId(p_person_id IN NUMBER,
1341 p_expense_type_id IN NUMBER,
1342 p_expense_date IN DATE,
1343 p_location_id IN NUMBER,
1344 p_currency_code IN VARCHAR2 ) RETURN NUMBER;
1345
1346 /*========================================================================
1347 | PUBLIC PROCEDURE getHrAssignment
1348 |
1349 | DESCRIPTION
1350 | Public helper procedure to retrieve HR assignment.
1351 |
1352 | PARAMETERS
1353 | p_person_id -- Expense Type ID associated to the expense
1354 | p_date -- Assignment date.
1355 | p_grade_id -- Returns grade id.
1356 | p_position_id -- Returns position id.
1357 | p_job_id -- Returns job id.
1358 |
1359 | MODIFICATION HISTORY
1360 | Date Author Description of Changes
1361 | 22-Feb-2006 albowicz Created
1362 |
1363 *=======================================================================*/
1364
1365 PROCEDURE getHrAssignment(p_person_id IN per_employees_x.employee_id%type,
1366 p_date IN DATE,
1367 p_grade_id OUT NOCOPY per_all_assignments_f.grade_id%type,
1368 p_position_id OUT NOCOPY per_all_assignments_f.position_id%type,
1369 p_job_id OUT NOCOPY per_all_assignments_f.job_id%type);
1370
1371
1372 /*========================================================================
1373 | PUBLIC FUNCTION checkForInvalidLines
1374 |
1375 | DESCRIPTION
1376 | Public helper procedure to validate policy lines.
1377 |
1378 | PARAMETERS
1379 | p_policy_id IN Policy Id
1380 | p_schedule_id IN Policy Schedule Id.
1381 | p_rate_type IN Rate Type (STANDARD, SAME_DAY, FIRST_PERIOD,
1382 | LAST_PERIOD, NIGHT_RATE, ADDON)
1383 | p_std_invalid OUT Count of invalid standard lines
1384 | p_first_invalid OUT Count of invalid first period lines
1385 | p_last_invalid OUT Count of invalid last period lines
1386 | p_same_invalid OUT Count of invalid same day rate lines
1387 | p_night_invalid OUT Count of invalid night rate lines
1388 | p_addon_invalid OUT Count of invalid addon lines
1389 |
1390 |
1391 | MODIFICATION HISTORY
1392 | Date Author Description of Changes
1393 | 08-Jun-2006 krmenon Created
1394 |
1395 *=======================================================================*/
1396 FUNCTION checkForInvalidLines(p_policy_id IN ap_pol_lines.POLICY_ID%type,
1397 p_schedule_id IN ap_pol_lines.SCHEDULE_PERIOD_ID%type,
1398 p_std_invalid OUT NOCOPY NUMBER,
1399 p_first_invalid OUT NOCOPY NUMBER,
1400 p_last_invalid OUT NOCOPY NUMBER,
1401 p_same_invalid OUT NOCOPY NUMBER,
1402 p_night_invalid OUT NOCOPY NUMBER,
1403 p_addon_invalid OUT NOCOPY NUMBER) RETURN VARCHAR2;
1404 /*========================================================================
1405 | PUBLIC FUNCTION activatePolicyLines
1406 |
1407 | DESCRIPTION
1408 | Public helper procedure to activate policy lines for the case where there
1409 | are more than 300 lines.
1410 |
1411 | PARAMETERS
1412 | p_policy_id IN Policy Id
1413 | p_schedule_id IN Policy Schedule Id
1414 |
1415 |
1416 | MODIFICATION HISTORY
1417 | Date Author Description of Changes
1418 | 08-Jun-2006 krmenon Created
1419 |
1420 *=======================================================================*/
1421 PROCEDURE activatePolicyLines(p_policy_id IN ap_pol_lines.POLICY_ID%type,
1422 p_schedule_id IN ap_pol_lines.SCHEDULE_PERIOD_ID%type);
1423
1424 /*========================================================================
1425 | PUBLIC FUNCTION get_dup_rule_assignment_exists
1426 |
1427 | DESCRIPTION
1428 | This function checks whether assignments exist for a given duplicate detection rule.
1429 |
1430 | RETURNS
1431 | Y / N depending whether assignment exists for the rule
1432 |
1433 | PARAMETERS
1434 | p_rule_id IN Rule Id
1435 |
1436 | MODIFICATION HISTORY
1437 | Date Author Description of Changes
1438 | 15-Feb-2010 Dharma Theja Reddy S Created
1439 |
1440 *=======================================================================*/
1441 FUNCTION get_dup_rule_assignment_exists(p_rule_id IN NUMBER) RETURN VARCHAR2;
1442
1443 /*========================================================================
1444 | PUBLIC FUNCTION get_dup_rs_assignment_exists
1445 |
1446 | DESCRIPTION
1447 | This function checks whether assignments exist for a given duplicate detection rule set.
1448 |
1449 | RETURNS
1450 | Y / N depending whether assignment exists for the rule set
1451 |
1452 | PARAMETERS
1453 | p_rule_set_id IN Rule Set Id
1454 |
1455 | MODIFICATION HISTORY
1456 | Date Author Description of Changes
1457 | 17-Feb-2010 Dharma Theja Reddy S Created
1458 |
1459 *=======================================================================*/
1460 FUNCTION get_dup_rs_assignment_exists(p_rule_set_id IN NUMBER) RETURN VARCHAR2;
1461
1462 /*========================================================================
1463 | PUBLIC FUNCTION get_dup_detect_rule_name
1464 |
1465 | DESCRIPTION
1466 | This function returns duplicate detection rule name using rule id.
1467 |
1468 | RETURNS
1469 | Duplicate detection rule name.
1470 |
1471 | PARAMETERS
1472 | p_rule_id IN Rule Id
1473 |
1474 | MODIFICATION HISTORY
1475 | Date Author Description of Changes
1476 | 23-Feb-2010 Dharma Theja Reddy S Created
1477 |
1478 *=======================================================================*/
1479 FUNCTION get_dup_detect_rule_name(p_rule_id IN NUMBER) RETURN VARCHAR2;
1480
1481 /*========================================================================
1482 | PUBLIC FUNCTION get_dup_detect_rs_name
1483 |
1484 | DESCRIPTION
1485 | This function returns duplicate detection rule set name using rule set id.
1486 |
1487 | RETURNS
1488 | Duplicate Detection Rule Set name
1489 |
1490 | PARAMETERS
1491 | p_rule_set_id IN Rule Set Id
1492 |
1493 | MODIFICATION HISTORY
1494 | Date Author Description of Changes
1495 | 23-Feb-2010 Dharma Theja Reddy S Created
1496 |
1497 *=======================================================================*/
1498 FUNCTION get_dup_detect_rs_name(p_rule_set_id IN NUMBER) RETURN VARCHAR2;
1499
1500 /*========================================================================
1501 | PUBLIC FUNCTION validate_dup_detect_rule_name
1502 |
1503 | DESCRIPTION
1504 | This function validates the duplicate detection rule name.
1505 |
1506 | RETURNS
1507 | Y / N depending whether the rule name already exists
1508 |
1509 | PARAMETERS
1510 | p_rule_name IN Rule Name
1511 | p_rule_id IN Rule Id
1512 |
1513 | MODIFICATION HISTORY
1514 | Date Author Description of Changes
1515 | 23-Feb-2010 Dharma Theja Reddy S Created
1516 |
1517 *=======================================================================*/
1518 FUNCTION validate_dup_detect_rule_name(p_rule_name IN VARCHAR2, p_rule_id IN VARCHAR2) RETURN VARCHAR2;
1519
1520 /*========================================================================
1521 | PUBLIC FUNCTION validate_dup_detect_rs_name
1522 |
1523 | DESCRIPTION
1524 | This function validates the duplicate detection rule set name.
1525 |
1526 | RETURNS
1527 | Y / N depending whether the rule set name already exists
1528 |
1529 | PARAMETERS
1530 | p_rule_set_name IN Rule Set Name
1531 | p_rule_set_id IN Rule Set Id
1532 |
1533 | MODIFICATION HISTORY
1534 | Date Author Description of Changes
1535 | 23-Feb-2010 Dharma Theja Reddy S Created
1536 |
1537 *=======================================================================*/
1538 FUNCTION validate_dup_detect_rs_name(p_rule_set_name IN VARCHAR2, p_rule_set_id IN VARCHAR2) RETURN VARCHAR2;
1539
1540 /*========================================================================
1541 | PUBLIC FUNCTION getDuplicateDetectionRule
1542 |
1543 | DESCRIPTION
1544 | This function returns the duplicate detection rule id.
1545 |
1546 | RETURNS
1547 | Duplicate detection rule id
1548 |
1549 | PARAMETERS
1550 | p_org_id IN Org Id
1551 | p_category_code IN Category Code
1552 | p_start_date IN Expense Start Date
1553 |
1554 | MODIFICATION HISTORY
1555 | Date Author Description of Changes
1556 | 23-Feb-2010 Dharma Theja Reddy S Created
1557 |
1558 *=======================================================================*/
1559 FUNCTION getDuplicateDetectionRule(p_org_id IN ap_expense_report_lines_all.ORG_ID%TYPE,
1560 p_category_code IN ap_expense_report_lines_all.CATEGORY_CODE%TYPE,
1561 p_start_date IN ap_expense_report_lines_all.START_EXPENSE_DATE%TYPE) RETURN NUMBER;
1562
1563 /*========================================================================
1564 | PUBLIC FUNCTION isDupDetectExists
1565 |
1566 | DESCRIPTION
1567 | This function validates whether the duplicate detection violation exists
1568 | for the expense line.
1569 |
1570 | RETURNS
1571 | Duplicate Detection Action
1572 |
1573 | PARAMETERS
1574 | p_report_header_id IN Report Header Id
1575 | p_dist_line_number IN Distribution Line Number
1576 | p_org_id IN Org Id
1577 | p_category_code IN Category Code
1578 | p_start_date IN Expense Start Date
1579 |
1580 | MODIFICATION HISTORY
1581 | Date Author Description of Changes
1582 | 23-Feb-2010 Dharma Theja Reddy S Created
1583 |
1584 *=======================================================================*/
1585 FUNCTION isDupDetectExists(p_report_header_id ap_expense_report_lines_all.REPORT_HEADER_ID%TYPE,
1586 p_dist_line_number ap_expense_report_lines_all.DISTRIBUTION_LINE_NUMBER%TYPE,
1587 p_org_id IN ap_expense_report_lines_all.ORG_ID%TYPE,
1588 p_category_code IN ap_expense_report_lines_all.CATEGORY_CODE%TYPE,
1589 p_start_date IN ap_expense_report_lines_all.START_EXPENSE_DATE%TYPE) RETURN VARCHAR2;
1590
1591 /*========================================================================
1592 | PUBLIC FUNCTION getDistLineNumber
1593 |
1594 | DESCRIPTION
1595 | This function gets the distribution line number.
1596 |
1597 | RETURNS
1598 | Distribution Line Number
1599 |
1600 | PARAMETERS
1601 | p_report_header_id IN Report Header Id
1602 | p_dist_line_number IN Distribution Line Number
1603 |
1604 | MODIFICATION HISTORY
1605 | Date Author Description of Changes
1606 | 23-Feb-2010 Dharma Theja Reddy S Created
1607 |
1608 *=======================================================================*/
1609 FUNCTION getDistLineNumber(p_report_header_id IN ap_expense_report_lines_all.REPORT_HEADER_ID%TYPE,
1610 p_dist_line_number IN ap_expense_report_lines_all.DISTRIBUTION_LINE_NUMBER%TYPE) RETURN VARCHAR2;
1611
1612 /*========================================================================
1613 | PUBLIC PROCEDURE removeDupViolations
1614 |
1615 | DESCRIPTION
1616 | This procedure removes the duplicate detection violations for the expense line.
1617 |
1618 | PARAMETERS
1619 | p_report_header_id IN Report Header Id
1620 | p_dist_line_number IN Distribution Line Number
1621 |
1622 | MODIFICATION HISTORY
1623 | Date Author Description of Changes
1624 | 23-Feb-2010 Dharma Theja Reddy S Created
1625 |
1626 *=======================================================================*/
1627 PROCEDURE removeDupViolations(p_report_header_id IN ap_expense_report_lines_all.REPORT_HEADER_ID%TYPE,
1628 p_dist_line_number IN ap_expense_report_lines_all.DISTRIBUTION_LINE_NUMBER%TYPE);
1629
1630 /*========================================================================
1631 | PUBLIC PROCEDURE getDistNumber
1632 |
1633 | DESCRIPTION
1634 | This procedure gets the distribution line number depending on the expense category.
1635 |
1636 | PARAMETERS
1637 | p_report_line_id IN Report Line Id
1638 | p_category OUT Expense Category
1639 | p_dist_num OUT Distribution Number
1640 |
1641 | MODIFICATION HISTORY
1642 | Date Author Description of Changes
1643 | 23-Feb-2010 Dharma Theja Reddy S Created
1644 |
1645 *=======================================================================*/
1646 PROCEDURE getDistNumber(p_report_line_id IN ap_pol_violations_all.DUP_REPORT_LINE_ID%TYPE,
1647 p_category OUT NOCOPY VARCHAR2,
1648 p_dist_num OUT NOCOPY VARCHAR2);
1649
1650 /*========================================================================
1651 | PUBLIC FUNCTION getMaxDistLineNumber
1652 |
1653 | DESCRIPTION
1654 | This function returns the maximum distribution line number for that expense line.
1655 |
1656 | RETURNS
1657 | Maximum Distribution Line Number
1658 |
1659 | PARAMETERS
1660 | p_report_header_id IN Report Header Id
1661 | p_dist_line_number IN Distribution Line Number
1662 |
1663 | MODIFICATION HISTORY
1664 | Date Author Description of Changes
1665 | 23-Feb-2010 Dharma Theja Reddy S Created
1666 |
1667 *=======================================================================*/
1668 FUNCTION getMaxDistLineNumber(p_report_header_id IN ap_pol_violations_all.REPORT_HEADER_ID%TYPE,
1669 p_dist_line_number IN ap_pol_violations_all.DISTRIBUTION_LINE_NUMBER%TYPE) RETURN NUMBER;
1670
1671 /*========================================================================
1672 | PUBLIC PROCEDURE performDuplicateDetection
1673 |
1674 | DESCRIPTION
1675 | This procedure performs the duplicate detection for the expense line and
1676 | inserts the violations on to the table ap_pol_violations_all.
1677 |
1678 | MODIFICATION HISTORY
1679 | Date Author Description of Changes
1680 | 23-Feb-2010 Dharma Theja Reddy S Created
1681 |
1682 *=======================================================================*/
1683 PROCEDURE performDuplicateDetection(p_employee_id IN VARCHAR2,
1684 p_report_header_id IN VARCHAR2,
1685 p_distribution_line_number IN VARCHAR2,
1686 p_org_id IN VARCHAR2,
1687 p_start_date IN DATE,
1688 p_end_date IN DATE,
1689 p_receipt_currency_code IN VARCHAR2,
1690 p_daily_amount IN NUMBER,
1691 p_receipt_currency_amount IN NUMBER,
1692 p_web_parameter_id IN VARCHAR2,
1693 p_merchant_name IN VARCHAR2,
1694 p_daily_distance IN NUMBER,
1695 p_distance_unit_code IN VARCHAR2,
1696 p_destination_from IN VARCHAR2,
1697 p_destination_to IN VARCHAR2,
1698 p_trip_distance IN NUMBER,
1699 p_license_plate_number IN VARCHAR2,
1700 p_attendes IN VARCHAR2,
1701 p_number_attendes IN NUMBER,
1702 p_ticket_class_code IN VARCHAR2,
1703 p_ticket_number IN VARCHAR2,
1704 p_itemization_parent_id IN NUMBER,
1705 p_category_code IN VARCHAR2,
1706 p_report_line_id IN NUMBER,
1707 p_max_violation_number IN OUT NOCOPY NUMBER,
1708 p_dup_detect_action OUT NOCOPY VARCHAR2,
1709 p_created_by IN NUMBER,
1710 p_creation_date IN DATE,
1711 p_last_updated_by IN NUMBER,
1712 p_last_update_login IN NUMBER,
1713 p_last_update_date IN DATE);
1714
1715 /*========================================================================
1716 | PUBLIC FUNCTION massUpdatePolicyLines
1717 |
1718 | DESCRIPTION
1719 | Public helper procedure to Mass Update policy lines for the case where there
1720 | are more than 200 lines.
1721 |
1722 | PARAMETERS
1723 | l_mass_update_type
1724 | l_rate
1725 | l_meal_limit
1726 | l_calculation_method
1727 | l_accommodation_calc_method
1728 | l_breakfast_deduction
1729 | l_lunch_deduction
1730 | l_dinner_deduction
1731 | l_accommodation_adjustment
1732 | l_meals_deduction
1733 | l_tolerance
1734 | l_rate_per_passenger
1735 | l_one_meal_deduction_amt
1736 | l_two_meals_deduction_amt
1737 | l_three_meals_deduction_amt
1738 | l_rounding_rule
1739 | l_where_clause
1740 |
1741 |
1742 | MODIFICATION HISTORY
1743 | Date Author Description of Changes
1744 | 06-MAR-2009 meesubra Created
1745 |
1746 *=======================================================================*/
1747
1748 PROCEDURE massUpdatePolicyLines(l_mass_update_type IN VARCHAR2,
1749 l_rate IN VARCHAR2,
1750 l_meal_limit IN VARCHAR2,
1751 l_calculation_method IN VARCHAR2,
1752 l_accommodation_calc_method IN VARCHAR2,
1753 l_breakfast_deduction IN VARCHAR2,
1754 l_lunch_deduction IN VARCHAR2,
1755 l_dinner_deduction IN VARCHAR2,
1756 l_accommodation_adjustment IN VARCHAR2,
1757 l_meals_deduction IN VARCHAR2,
1758 l_tolerance IN VARCHAR2,
1759 l_reimbursement_percentage IN VARCHAR2,
1760 l_rate_per_passenger IN VARCHAR2,
1761 l_one_meal_deduction_amt IN VARCHAR2,
1762 l_two_meals_deduction_amt IN VARCHAR2,
1763 l_three_meals_deduction_amt IN VARCHAR2,
1764 l_rounding_rule IN VARCHAR2,
1765 l_where_clause IN VARCHAR2,
1766 l_max_receipt_amt IN VARCHAR2);
1767
1768 Function Get_Role(P_Policy_Id In Number,
1769 P_Policy_Line_Id In Number,
1770 p_role_id IN NUMBER) RETURN VARCHAR2;
1771
1772 /*========================================================================
1773 | PUBLIC FUNCTION get_location_desc
1774 |
1775 | DESCRIPTION
1776 | This function returns location description.
1777 |
1778 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1779 | Called from BC4J, needed for the LineHistoryVO, PolicyLinesVO
1780 | which cannot have joins due to connect by clause.
1781 |
1782 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1783 |
1784 | RETURNS
1785 | Location Description
1786 |
1787 | PARAMETERS
1788 | p_location_id IN Location identifier
1789 |
1790 | MODIFICATION HISTORY
1791 | Date Author Description of Changes
1792 | 25-Aug-2011 meesubra Created
1793 |
1794 *=======================================================================*/
1795 Function Get_Location_Desc(P_Location_Id In Number) Return Varchar2;
1796
1797
1798 END AP_WEB_POLICY_UTILS;