DBA Data[Home] [Help]

PACKAGE: APPS.AP_WEB_POLICY_UTILS

Source


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;