1 package ben_distribute_rates AUTHID CURRENT_USER as
2 /* $Header: bendisrt.pkh 120.2 2010/09/14 01:22:26 stee ship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 Name
12 Convert Rates (or say Distribute rates)
13 Purpose
14 This package is mostly used to convert rates from annual to per period
15 and vice-versa. The procedures annual_to_period and period_to_annual
16 are used for this. The other procedure get_periods_between gets the
17 number of activity periods between two dates.
18
19 The procedures annual_to_period and period_to_annual are mostly used
20 to convert rate amounts. When the complete year flag is on, the start
21 date and end date are the plan year start and end date respectively.
22
23 When the complete year flag is off, then the start date is the rate
24 start date (if enrt_rt_id is supplied) or the coverage start date
25 (if the elig_per_elctbl_chc_id is supplied). The end date is still the
26 plan year end date. As the procedure is mostly used to convert rates,
27 it is advisable to use rate start date as the starting period, so in
28 other words, it is highly recommended to pass in the enrt_rt_id rather
29 than elig_per_elctbl_chc_id.
30
31 If the start date and end dates are passed in, then the dates are not
32 overridden (except in case when the complete year flag is ON).
33
34 History
35 Date Who Version What?
36 ---- --- ------- -----
37 23 Sep 98 maagrawa 115.0 Created.
38 18 Jan 99 G Perry 115.1 LED V ED
39 28 Sep 99 lmcdonal 115.2 Added Compare_Balances,
40 Prorate_min_max procedures.
41 21 Apr 00 jcarpent 115.3 Changed parms to get_periods..
42 22 Sep 00 mhoyes 115.4 - Added clear_down_cache to clear
43 function cache.
44 07 Nov 00 mhoyes 115.5 - Added set_no_cache_context.
45 03 jan 01 tilak 115.6 - getbalance function changed as global function
46 03 Nov 01 tmathers 115.7 - added decde_bits and dbdrv line.
47 21 Apr 02 ashrivas 115.8 - Added convert_rates_w for self-service
48 23 May 02 kmahendr 115.10 - Added a procedure - annual_to_period_out
49 23 May 02 115.11 No changes
50 15 Oct 02 kmahendr 115.12 Added overloaded function - get_periods_between
51 and added parameter to annual_to_period -
52 Bug#2556948
53 16-Dec-03 kmullapu 115.13 Bug 2745691.Added convert_pcr_rates_w
54 23-Jan-03 ikasire 115.15 Bug 2149438 added overloaded procedure to
55 control rounding
56 26-Jun-03 lakrish 115.16 Bug 2992321, made ann_rt_val parameters
57 as IN OUT in convert_pcr_rates_w
58 13-Oct-03 rpillay 115.17 Bug 3097501 - Externalized procedure
59 estimate_balance for COBRA. Called
60 from bencobra.pkb
61 31-oct-03 kmahendr 115.18 Bug#3231548 - added additional parameter to
62 get_periods_between
63 18-Mar-04 ikasire 115.19 Bug periodize_with_rule procedure to use
64 formula for periodization
65 26-Apr-04 kmahendr 115.20 Added parameter person_id to annual_to_period
66 function
67 21-Mar-06 vborkar 115.21 5104247 Added p_child_rt_flag parameters to
68 convert_pcr_rates_w procedure.
69 12-Sep-10 stee 115.22 Bug 10058794 - Added p_yr_start_date parameter
70 to get_periods_between.
71
72 --
73 */
74 --
75 --
76 -- This function returns the number of activity periods between
77 -- two dates. If the end date supplied is null, it is defaulted
78 -- to end of the calendar year.
79 --
80 -- Return value is number and rounded to the first decimal
81 -- e.g: 1.232 becomes 1.2; 5.47 becomes 5.5;
82 --
83 function get_periods_between(
84 p_acty_ref_perd_cd in varchar2,
85 p_start_date in date,
86 p_end_date in date default null,
87 p_payroll_id in number default null,
88 p_business_group_id in number default null,
89 p_element_type_id in number default null,
90 p_enrt_rt_id in number default null,
91 p_effective_date in date default null,
92 p_yr_start_date in date default null, -- 10058794
93 p_called_from_est in boolean default false
94 ) return number;
95
96 -- overloaded the function to calculate periods based on cheque dates
97
98 function get_periods_between(
99 p_acty_ref_perd_cd in varchar2,
100 p_start_date in date,
101 p_end_date in date default null,
102 p_payroll_id in number default null,
103 p_business_group_id in number default null,
104 p_element_type_id in number default null,
105 p_enrt_rt_id in number default null,
106 p_effective_date in date default null,
107 p_yr_start_date in date default null, -- 10058794
108 p_use_check_date in boolean
109 ) return number;
110
111 --
112 -- This function is used to convert the period amount to annual amount
113 -- The annual period is computed as the period between the start date
114 -- and end date. When the complete year flag is on, the start date and
115 -- end date are overridden by plan year start and end date respectively.
116 --
117 function period_to_annual(p_amount in number,
118 p_enrt_rt_id in number default null,
119 p_elig_per_elctbl_chc_id in number default null,
120 p_acty_ref_perd_cd in varchar2 default null,
121 p_business_group_id in number default null,
122 p_effective_date in date default null,
123 p_lf_evt_ocrd_dt in date default null,
124 p_complete_year_flag in varchar2 default 'N',
125 p_use_balance_flag in varchar2 default 'N',
126 p_start_date in date default null,
127 p_end_date in date default null,
128 p_payroll_id in number default null,
129 p_element_type_id in number default null)
130 return number;
131 --
132 -- Overloaded procedure without rounding. This can be removed once the
133 -- hard coded rounding is removed and it is better to handle in the called
134 -- procedures depending on the requirement.
135 -- This is because we don't want to round the computed values some times.
136 -- like in case of SAREC, for element entries see Bug 2149438
137 --
138 function period_to_annual(p_amount in number,
139 p_enrt_rt_id in number default null,
140 p_elig_per_elctbl_chc_id in number default null,
141 p_acty_ref_perd_cd in varchar2 default null,
142 p_business_group_id in number default null,
143 p_effective_date in date default null,
144 p_lf_evt_ocrd_dt in date default null,
145 p_complete_year_flag in varchar2 default 'N',
146 p_use_balance_flag in varchar2 default 'N',
147 p_start_date in date default null,
148 p_end_date in date default null,
149 p_payroll_id in number default null,
150 p_element_type_id in number default null,
151 p_rounding_flag in varchar2 )
152 return number;
153
154 --
155 -- This function is used to convert the annual amount to period amount
156 -- The annual period is computed as the period between the start date
157 -- and end date. When the complete year flag is on, the start date and
158 -- end date are overridden by plan year start and end date respectively.
159 --
160 function annual_to_period(p_amount in number,
161 p_enrt_rt_id in number default null,
162 p_elig_per_elctbl_chc_id in number default null,
163 p_acty_ref_perd_cd in varchar2 default null,
164 p_business_group_id in number default null,
165 p_effective_date in date default null,
166 p_lf_evt_ocrd_dt in date default null,
167 p_complete_year_flag in varchar2 default 'N',
168 p_use_balance_flag in varchar2 default 'N',
169 p_start_date in date default null,
170 p_end_date in date default null,
171 p_payroll_id in number default null,
172 p_element_type_id in number default null,
173 p_annual_target in boolean default false,
174 p_person_id in number default null)
175 return number;
176 --
177 -- Overloaded procedure without rounding. This can be removed once the
178 -- hard coded rounding is removed and it is better to handle in the called
179 -- procedures depending on the requirement.
180 -- This is because we don't want to round the computed values some times.
181 -- like in case of SAREC, for element entries see Bug 2149438
182 --
183 function annual_to_period(p_amount in number,
184 p_enrt_rt_id in number default null,
185 p_elig_per_elctbl_chc_id in number default null,
186 p_acty_ref_perd_cd in varchar2 default null,
187 p_business_group_id in number default null,
188 p_effective_date in date default null,
189 p_lf_evt_ocrd_dt in date default null,
190 p_complete_year_flag in varchar2 default 'N',
191 p_use_balance_flag in varchar2 default 'N',
192 p_start_date in date default null,
193 p_end_date in date default null,
194 p_payroll_id in number default null,
195 p_element_type_id in number default null,
196 p_annual_target in boolean default false,
197 p_rounding_flag in varchar2,
198 p_person_id in number default null)
199 return number;
200 --
201 function annual_to_period_out(p_amount in number,
202 p_enrt_rt_id in number default null,
203 p_elig_per_elctbl_chc_id in number default null,
204 p_acty_ref_perd_cd in varchar2 default null,
205 p_business_group_id in number default null,
206 p_effective_date in date default null,
207 p_lf_evt_ocrd_dt in date default null,
208 p_complete_year_flag in varchar2 default 'N',
209 p_use_balance_flag in varchar2 default 'N',
210 p_start_date in date default null,
211 p_end_date in date default null,
212 p_payroll_id in number default null,
213 p_element_type_id in number default null,
214 p_pp_in_yr_used_num out nocopy number)
215 return number;
216
217
218
219 procedure compare_balances
220 (p_person_id in number
221 ,p_effective_date in date
222 ,p_lf_evt_ocrd_dt in date default null
223 ,p_elig_per_elctbl_chc_id in number default null
224 ,p_pgm_id in number default null
225 ,p_pl_id in number default null
226 ,p_oipl_id in number default null
227 ,p_per_in_ler_id in number default null
228 ,p_business_group_id in number default null
229 ,p_acty_base_rt_id in number
230 ,p_perform_edit_flag in varchar2 default 'N'
231 ,p_entered_ann_val in number default null
232 ,p_ann_mn_val in out nocopy number
233 ,p_ann_mx_val in out nocopy number
234 ,p_ptd_balance out nocopy number
235 ,p_clm_balance out nocopy number) ;
236
237 procedure prorate_min_max
238 (p_person_id in number
239 ,p_effective_date in date
240 ,p_elig_per_elctbl_chc_id in number
241 ,p_acty_base_rt_id in number
242 ,p_rt_strt_dt in date
243 ,p_ann_mn_val in out nocopy number
244 ,p_ann_mx_val in out nocopy number ) ;
245
246
247
248 function get_balance
249 (p_enrt_rt_id in number default null,
250 p_person_id in number default null,
251 p_per_in_ler_id in number default null,
252 p_pgm_id in number default null,
253 p_pl_id in number default null,
254 p_oipl_id in number default null,
255 p_enrt_perd_id in number default null,
256 p_lee_rsn_id in number default null,
257 p_acty_base_rt_id in number default null,
258 p_payroll_id in number default null,
259 p_ptd_comp_lvl_fctr_id in number default null,
260 p_det_pl_ytd_cntrs_cd in varchar2 default null,
261 p_lf_evt_ocrd_dt in date default null,
262 p_business_group_id in number,
263 p_start_date in date,
264 p_end_date in date default null,
265 p_effective_date in date)
269 procedure clear_down_cache;
266 return number ;
267
268
270
271 procedure set_no_cache_context;
272
273 function decde_bits(p_number IN NUMBER) return NUMBER;
274
275 procedure convert_rates_w(p_person_id in number,
276 p_amount in number,
277 p_enrt_rt_id in number default null,
278 p_elig_per_elctbl_chc_id in number default null,
279 p_acty_ref_perd_cd in varchar2 default null,
280 p_cmcd_acty_ref_perd_cd in varchar2 default null,
281 p_business_group_id in number default null,
282 p_effective_date in date default null,
283 p_lf_evt_ocrd_dt in date default null,
284 p_complete_year_flag in varchar2 default 'N',
285 p_use_balance_flag in varchar2 default 'N',
286 p_start_date in date default null,
287 p_end_date in date default null,
288 p_payroll_id in number default null,
289 p_element_type_id in number default null,
290 p_convert_from_rt in varchar2,
291 p_ann_rt_val out nocopy number,
292 p_cmcd_rt_val out nocopy number,
293 p_val out nocopy number );
294
295 --
296 -- Child rate refresh when parent value is modified
297 --
298
299 procedure convert_pcr_rates_w(
300 p_person_id in number,
301 p_amount in number,
302 p_rate_index in number,
303 p_prnt_acty_base_rt_id in number,
304 p_enrt_rt_id in number default null,
305 p_enrt_rt_id2 in number default null,
306 p_enrt_rt_id3 in number default null,
307 p_enrt_rt_id4 in number default null,
308 p_elig_per_elctbl_chc_id in number default null,
309 p_acty_ref_perd_cd in varchar2 default null,
310 p_cmcd_acty_ref_perd_cd in varchar2 default null,
311 p_business_group_id in number default null,
312 p_effective_date in date default null,
313 p_lf_evt_ocrd_dt in date default null,
314 p_use_balance_flag in varchar2 default 'N',
315 p_start_date in date default null,
316 p_end_date in date default null,
317 p_payroll_id in number default null,
318 p_element_type_id in number default null,
319 p_convert_from_rt in varchar2,
320 p_ann_rt_val in out nocopy number,
321 p_cmcd_rt_val out nocopy number,
322 p_val out nocopy number,
323 p_child_rt_flag out nocopy varchar2, --5104247
324 p_ann_rt_val2 in out nocopy number,
325 p_cmcd_rt_val2 out nocopy number,
326 p_val2 out nocopy number,
327 p_child_rt_flag2 out nocopy varchar2,
328 p_ann_rt_val3 in out nocopy number,
329 p_cmcd_rt_val3 out nocopy number,
330 p_val3 out nocopy number,
331 p_child_rt_flag3 out nocopy varchar2,
332 p_ann_rt_val4 in out nocopy number,
333 p_cmcd_rt_val4 out nocopy number,
334 p_val4 out nocopy number,
335 p_child_rt_flag4 out nocopy varchar2 );
336
337
338 procedure estimate_balance
339 (p_person_id in number,
340 p_acty_base_rt_id in number,
341 p_payroll_id in number,
342 p_effective_date in date,
343 p_business_group_id in number,
344 p_date_from in date,
345 p_date_to in date,
346 p_balance out nocopy number);
347 --
348 procedure periodize_with_rule
349 (p_formula_id in number,
350 p_effective_date in date,
351 p_assignment_id in number,
352 p_convert_from_val in number,
353 p_convert_from in varchar2,
354 p_elig_per_elctbl_chc_id in number,
355 p_acty_base_rt_id in number,
356 p_business_group_id in number,
357 p_enrt_rt_id in number default null,
358 p_ann_val out nocopy number,
359 p_cmcd_val out nocopy number,
360 p_val out nocopy number );
361 --
362 end ben_distribute_rates;