1 package ben_distribute_rates as
2 /* $Header: bendisrt.pkh 120.1.12010000.1 2008/07/29 12:10:00 appldev 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 --
70 */
71 --
72 --
73 -- This function returns the number of activity periods between
74 -- two dates. If the end date supplied is null, it is defaulted
75 -- to end of the calendar year.
76 --
77 -- Return value is number and rounded to the first decimal
78 -- e.g: 1.232 becomes 1.2; 5.47 becomes 5.5;
79 --
80 function get_periods_between(
81 p_acty_ref_perd_cd in varchar2,
82 p_start_date in date,
83 p_end_date in date default null,
84 p_payroll_id in number default null,
85 p_business_group_id in number default null,
86 p_element_type_id in number default null,
87 p_enrt_rt_id in number default null,
88 p_effective_date in date default null,
89 p_called_from_est in boolean default false
90 ) return number;
91
92 -- overloaded the function to calculate periods based on cheque dates
93
94 function get_periods_between(
95 p_acty_ref_perd_cd in varchar2,
96 p_start_date in date,
97 p_end_date in date default null,
98 p_payroll_id in number default null,
99 p_business_group_id in number default null,
100 p_element_type_id in number default null,
101 p_enrt_rt_id in number default null,
102 p_effective_date in date default null,
103 p_use_check_date in boolean
104 ) return number;
105
106 --
107 -- This function is used to convert the period amount to annual amount
108 -- The annual period is computed as the period between the start date
109 -- and end date. When the complete year flag is on, the start date and
110 -- end date are overridden by plan year start and end date respectively.
111 --
112 function period_to_annual(p_amount in number,
113 p_enrt_rt_id in number default null,
114 p_elig_per_elctbl_chc_id in number default null,
115 p_acty_ref_perd_cd in varchar2 default null,
116 p_business_group_id in number default null,
117 p_effective_date in date default null,
118 p_lf_evt_ocrd_dt in date default null,
119 p_complete_year_flag in varchar2 default 'N',
120 p_use_balance_flag in varchar2 default 'N',
121 p_start_date in date default null,
122 p_end_date in date default null,
123 p_payroll_id in number default null,
124 p_element_type_id in number default null)
125 return number;
126 --
127 -- Overloaded procedure without rounding. This can be removed once the
128 -- hard coded rounding is removed and it is better to handle in the called
129 -- procedures depending on the requirement.
130 -- This is because we don't want to round the computed values some times.
131 -- like in case of SAREC, for element entries see Bug 2149438
132 --
133 function period_to_annual(p_amount in number,
134 p_enrt_rt_id in number default null,
135 p_elig_per_elctbl_chc_id in number default null,
136 p_acty_ref_perd_cd in varchar2 default null,
137 p_business_group_id in number default null,
138 p_effective_date in date default null,
139 p_lf_evt_ocrd_dt in date default null,
140 p_complete_year_flag in varchar2 default 'N',
141 p_use_balance_flag in varchar2 default 'N',
142 p_start_date in date default null,
143 p_end_date in date default null,
144 p_payroll_id in number default null,
145 p_element_type_id in number default null,
146 p_rounding_flag in varchar2 )
147 return number;
148
149 --
150 -- This function is used to convert the annual amount to period amount
151 -- The annual period is computed as the period between the start date
152 -- and end date. When the complete year flag is on, the start date and
153 -- end date are overridden by plan year start and end date respectively.
154 --
155 function annual_to_period(p_amount in number,
156 p_enrt_rt_id in number default null,
157 p_elig_per_elctbl_chc_id in number default null,
158 p_acty_ref_perd_cd in varchar2 default null,
159 p_business_group_id in number default null,
160 p_effective_date in date default null,
161 p_lf_evt_ocrd_dt in date default null,
162 p_complete_year_flag in varchar2 default 'N',
163 p_use_balance_flag in varchar2 default 'N',
164 p_start_date in date default null,
165 p_end_date in date default null,
166 p_payroll_id in number default null,
167 p_element_type_id in number default null,
168 p_annual_target in boolean default false,
169 p_person_id in number default null)
170 return number;
171 --
172 -- Overloaded procedure without rounding. This can be removed once the
173 -- hard coded rounding is removed and it is better to handle in the called
174 -- procedures depending on the requirement.
175 -- This is because we don't want to round the computed values some times.
176 -- like in case of SAREC, for element entries see Bug 2149438
177 --
178 function annual_to_period(p_amount in number,
179 p_enrt_rt_id in number default null,
180 p_elig_per_elctbl_chc_id in number default null,
181 p_acty_ref_perd_cd in varchar2 default null,
182 p_business_group_id in number default null,
183 p_effective_date in date default null,
184 p_lf_evt_ocrd_dt in date default null,
185 p_complete_year_flag in varchar2 default 'N',
186 p_use_balance_flag in varchar2 default 'N',
187 p_start_date in date default null,
188 p_end_date in date default null,
189 p_payroll_id in number default null,
190 p_element_type_id in number default null,
191 p_annual_target in boolean default false,
192 p_rounding_flag in varchar2,
193 p_person_id in number default null)
194 return number;
195 --
196 function annual_to_period_out(p_amount in number,
197 p_enrt_rt_id in number default null,
198 p_elig_per_elctbl_chc_id in number default null,
199 p_acty_ref_perd_cd in varchar2 default null,
200 p_business_group_id in number default null,
201 p_effective_date in date default null,
202 p_lf_evt_ocrd_dt in date default null,
203 p_complete_year_flag in varchar2 default 'N',
204 p_use_balance_flag in varchar2 default 'N',
205 p_start_date in date default null,
206 p_end_date in date default null,
207 p_payroll_id in number default null,
208 p_element_type_id in number default null,
209 p_pp_in_yr_used_num out nocopy number)
210 return number;
211
212
213
214 procedure compare_balances
215 (p_person_id in number
216 ,p_effective_date in date
217 ,p_lf_evt_ocrd_dt in date default null
218 ,p_elig_per_elctbl_chc_id in number default null
219 ,p_pgm_id in number default null
220 ,p_pl_id in number default null
221 ,p_oipl_id in number default null
222 ,p_per_in_ler_id in number default null
223 ,p_business_group_id in number default null
224 ,p_acty_base_rt_id in number
225 ,p_perform_edit_flag in varchar2 default 'N'
226 ,p_entered_ann_val in number default null
227 ,p_ann_mn_val in out nocopy number
228 ,p_ann_mx_val in out nocopy number
229 ,p_ptd_balance out nocopy number
230 ,p_clm_balance out nocopy number) ;
231
232 procedure prorate_min_max
233 (p_person_id in number
234 ,p_effective_date in date
235 ,p_elig_per_elctbl_chc_id in number
236 ,p_acty_base_rt_id in number
237 ,p_rt_strt_dt in date
238 ,p_ann_mn_val in out nocopy number
239 ,p_ann_mx_val in out nocopy number ) ;
240
241
242
243 function get_balance
244 (p_enrt_rt_id in number default null,
245 p_person_id in number default null,
246 p_per_in_ler_id in number default null,
247 p_pgm_id in number default null,
248 p_pl_id in number default null,
249 p_oipl_id in number default null,
250 p_enrt_perd_id in number default null,
251 p_lee_rsn_id in number default null,
255 p_det_pl_ytd_cntrs_cd in varchar2 default null,
252 p_acty_base_rt_id in number default null,
253 p_payroll_id in number default null,
254 p_ptd_comp_lvl_fctr_id in number default null,
256 p_lf_evt_ocrd_dt in date default null,
257 p_business_group_id in number,
258 p_start_date in date,
259 p_end_date in date default null,
260 p_effective_date in date)
261 return number ;
262
263
264 procedure clear_down_cache;
265
266 procedure set_no_cache_context;
267
268 function decde_bits(p_number IN NUMBER) return NUMBER;
269
270 procedure convert_rates_w(p_person_id in number,
271 p_amount in number,
272 p_enrt_rt_id in number default null,
273 p_elig_per_elctbl_chc_id in number default null,
274 p_acty_ref_perd_cd in varchar2 default null,
275 p_cmcd_acty_ref_perd_cd in varchar2 default null,
276 p_business_group_id in number default null,
277 p_effective_date in date default null,
278 p_lf_evt_ocrd_dt in date default null,
279 p_complete_year_flag in varchar2 default 'N',
280 p_use_balance_flag in varchar2 default 'N',
281 p_start_date in date default null,
282 p_end_date in date default null,
283 p_payroll_id in number default null,
284 p_element_type_id in number default null,
285 p_convert_from_rt in varchar2,
286 p_ann_rt_val out nocopy number,
287 p_cmcd_rt_val out nocopy number,
288 p_val out nocopy number );
289
290 --
291 -- Child rate refresh when parent value is modified
292 --
293
294 procedure convert_pcr_rates_w(
295 p_person_id in number,
296 p_amount in number,
297 p_rate_index in number,
298 p_prnt_acty_base_rt_id in number,
299 p_enrt_rt_id in number default null,
300 p_enrt_rt_id2 in number default null,
301 p_enrt_rt_id3 in number default null,
302 p_enrt_rt_id4 in number default null,
303 p_elig_per_elctbl_chc_id in number default null,
304 p_acty_ref_perd_cd in varchar2 default null,
305 p_cmcd_acty_ref_perd_cd in varchar2 default null,
306 p_business_group_id in number default null,
307 p_effective_date in date default null,
308 p_lf_evt_ocrd_dt in date default null,
309 p_use_balance_flag in varchar2 default 'N',
310 p_start_date in date default null,
311 p_end_date in date default null,
312 p_payroll_id in number default null,
313 p_element_type_id in number default null,
314 p_convert_from_rt in varchar2,
315 p_ann_rt_val in out nocopy number,
316 p_cmcd_rt_val out nocopy number,
317 p_val out nocopy number,
318 p_child_rt_flag out nocopy varchar2, --5104247
319 p_ann_rt_val2 in out nocopy number,
320 p_cmcd_rt_val2 out nocopy number,
321 p_val2 out nocopy number,
322 p_child_rt_flag2 out nocopy varchar2,
323 p_ann_rt_val3 in out nocopy number,
324 p_cmcd_rt_val3 out nocopy number,
325 p_val3 out nocopy number,
326 p_child_rt_flag3 out nocopy varchar2,
327 p_ann_rt_val4 in out nocopy number,
328 p_cmcd_rt_val4 out nocopy number,
329 p_val4 out nocopy number,
330 p_child_rt_flag4 out nocopy varchar2 );
331
332
333 procedure estimate_balance
334 (p_person_id in number,
335 p_acty_base_rt_id in number,
336 p_payroll_id in number,
337 p_effective_date in date,
338 p_business_group_id in number,
339 p_date_from in date,
340 p_date_to in date,
341 p_balance out nocopy number);
342 --
343 procedure periodize_with_rule
344 (p_formula_id in number,
345 p_effective_date in date,
346 p_assignment_id in number,
347 p_convert_from_val in number,
348 p_convert_from in varchar2,
349 p_elig_per_elctbl_chc_id in number,
350 p_acty_base_rt_id in number,
351 p_business_group_id in number,
352 p_enrt_rt_id in number default null,
353 p_ann_val out nocopy number,
354 p_cmcd_val out nocopy number,
355 p_val out nocopy number );
356 --
357 end ben_distribute_rates;
[Home] [Help]