1 package ben_provider_pools as
2 /* $Header: benpstcr.pkh 120.0.12010000.1 2008/07/29 12:29:09 appldev ship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1998 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 Name
12 Benefit provider pools
13 Purpose
14 This package is used to create benefit provided ledger entries as well
15 as flex credit enrolment results.
16 History
17 Date Who Version What?
18 ---- --- ------- -------
19 07 Jun 98 jcarpent 110.0 Created
20 10 Nov 98 jcarpent 115.5 Added create_rollover_enrt
21 27 May 99 maagrawa 115.6 Added new procedures for flex
22 credits re-computation when
23 choices may not be there.
24 21 Oct 99 lmcdonal 115.7 Added per-in-ler to create_flex.
25 12 Nov 99 lmcdonal 115.8 Added enrt_mthd_cd to create_debit_ledger_entry,
26 recompute_flex_credits
27 25 Jan 00 maagrawa 115.9 Added parameter p_per_in_ler_id to procedures
28 create_credit_ledger_entry,
29 create_debit_ledger_entry,
30 cleanup_invalid_ledger_entries,
31 create_flex_credit_enrolment, total_pools,
32 create_rollover_enrollment.
33 28 Sep 00 stee 115.10 Added p_net_credit_val to
34 create_flex_credit_enrolment.
35 12 Oct 00 maagrawa 115.11 Added p_old_rlovr_amt to
36 create_rollover_enrollment.
37 03 Jul 01 tmathers 115.13 fixed 9i compliance issues.
38 10 Jul 01 tmathers 115.14 Converted compute_excess, create_credit_ledger_entry and
39 create_debit_ledger_entry for EFC.
40 25-Jan-02 pbodla 115.15 Bug 2185478 Added procedure to validate the
41 rollover value entered by the user on flex
42 enrollment form and show proper message immediately
43 28-Jan-02 pbodla 115.16 Added dbdrv line.
44 08-Aug-02 kmahendr 115.17 Bug#2382651- added additional parameter to total_pools and
45 create_flex_credit_enrollment procedures.
46 30-Dec-02 mmudigon 115.19 NOCOPY
47 */
48 --------------------------------------------------------------------------------
49 g_credit_pool_result_id number:=null;
50 g_credit_pool_person_id number:=null;
51 --
52 -- Bug 2185478 : Globals used for validating the rollover amounts
53 --
54 g_balance number;
55 g_mx_dstrbl_pct_num number;
56 g_mx_rlovr_val number;
57 g_mx_elcn_val number;
58 g_mn_dstrbl_pct_num number;
59 g_mn_rlovr_val number;
60 g_mn_elcn_val number;
61 g_mx_rlovr_rl_val number;
62 --
63 procedure validate_rollover_val
64 (p_calculate_only_mode in boolean default false
65 ,p_bnft_prvdr_pool_id in number
66 ,p_person_id in number
67 ,p_per_in_ler_id in number
68 ,p_acty_base_rt_id in number default null
69 ,p_enrt_mthd_cd in varchar2
70 ,p_effective_date in date
71 ,p_datetrack_mode in varchar2
72 ,p_business_group_id in number
73 ,p_pct_rndg_cd in varchar2
74 ,p_pct_rndg_rl in number
75 ,p_dflt_excs_trtmt_cd in varchar2
76 ,p_new_rollover_val in number
77 ,p_rollover_val out nocopy number
78 );
79 --
80 procedure accumulate_pools(
81 p_validate in boolean default false,
82 p_person_id in number,
83 p_elig_per_elctbl_chc_id in number,
84 p_enrt_mthd_cd in varchar2,
85 p_effective_date in date,
86 p_business_group_id in number
87 );
88
89 procedure accumulate_pools_for_choice(
90 p_validate in boolean default false,
91 p_person_id in number,
92 p_epe_rec in ben_epe_shd.g_rec_type,
93 p_enrt_mthd_cd in varchar2,
94 p_effective_date in date
95 );
96
97 function person_enrolled_in_choice(
98 p_person_id number,
99 p_epe_rec ben_epe_shd.g_rec_type,
100 p_old_result_id number default hr_api.g_number,
101 p_effective_date date) return boolean;
102
103 procedure create_credit_ledger_entry(
104 p_validate in boolean default false,
105 p_person_id in number,
106 p_elig_per_elctbl_chc_id in number,
107 p_per_in_ler_id in number,
108 p_business_group_id in number,
109 p_bnft_prvdr_pool_id in number,
110 p_enrt_mthd_cd in varchar2,
111 p_effective_date in date
112 );
113
114 procedure create_credit_ledger_entry
115 (p_validate in boolean default null
116 ,p_calculate_only_mode in boolean default false
117 ,p_person_id in number
118 ,p_epe_rec in ben_epe_shd.g_rec_type
119 ,p_enrt_mthd_cd in varchar2
120 ,p_effective_date in date
121 --
122 ,p_bnft_prvdd_ldgr_id out nocopy number
123 ,p_bpl_prvdd_val out nocopy number
124 );
125 procedure create_debit_ledger_entry
126 (p_validate in boolean default false
127 ,p_calculate_only_mode in boolean default false
128 ,p_person_id in number
129 ,p_per_in_ler_id in number
130 ,p_elig_per_elctbl_chc_id in number
131 ,p_prtt_enrt_rslt_id in number
132 ,p_decr_bnft_prvdr_pool_id in number
133 ,p_acty_base_rt_id in number
134 ,p_prtt_rt_val_id in number
135 ,p_enrt_mthd_cd in varchar2
136 ,p_val in number
137 ,p_bnft_prvdd_ldgr_id in out nocopy number
138 ,p_business_group_id in number
139 ,p_effective_date in date
140 --
141 ,p_bpl_used_val out nocopy number
142 );
143
144 procedure create_debit_ledger_entry
145 (p_validate in boolean default false
146 ,p_calculate_only_mode in boolean default false
147 ,p_person_id in number
148 ,p_enrt_mthd_cd in varchar2
149 ,p_epe_rec in ben_epe_shd.g_rec_type
150 ,p_enrt_rt_rec in ben_ecr_shd.g_rec_type
151 ,p_bnft_prvdd_ldgr_id in out nocopy number
152 ,p_business_group_id in number
153 ,p_effective_date in date
154 --
155 ,p_bpl_used_val out nocopy number
156 );
157
158 procedure cleanup_invalid_ledger_entries( -- so few args because uses global table
159 p_validate in boolean default false,
160 p_person_id in number,
161 p_prtt_enrt_rslt_id in number,
162 p_effective_date in date,
163 p_business_group_id in number
164 );
165
166 procedure cleanup_invalid_ledger_entries( -- so few args because uses global table
167 p_validate in boolean default false,
168 p_person_id in number,
169 p_per_in_ler_id in number,
170 p_effective_date in date,
171 p_business_group_id in number
172 );
173
174 procedure create_flex_credit_enrolment(
175 p_validate in boolean default false,
176 p_person_id in number,
177 p_enrt_mthd_cd in varchar2,
178 p_business_group_id in number,
179 p_effective_date in date,
180 p_prtt_enrt_rslt_id out nocopy number,
181 p_prtt_rt_val_id out nocopy number,
182 p_per_in_ler_id in number,
183 p_rt_val in number,
184 p_net_credit_val in number default null,
185 p_pgm_id in number default null
186 );
187
188 procedure total_pools(
189 p_validate in boolean default false,
190 p_prtt_enrt_rslt_id in out nocopy number,
191 p_prtt_rt_val_id in out nocopy number,
192 p_acty_ref_perd_cd out nocopy varchar2,
193 p_acty_base_rt_id out nocopy number,
194 p_rt_strt_dt out nocopy date,
195 p_rt_val out nocopy number,
196 p_element_type_id out nocopy number,
197 p_person_id in number,
198 p_per_in_ler_id in number,
199 p_enrt_mthd_cd in varchar2,
200 p_effective_date in date,
201 p_business_group_id in number,
202 p_pgm_id in number default null
203 );
204
205 procedure remove_bnft_prvdd_ldgr(
206 p_prtt_enrt_rslt_id in number,
207 p_effective_date in date,
208 p_business_group_id in number,
209 p_validate in boolean,
210 p_datetrack_mode in varchar2
211 );
212 procedure create_rollover_enrollment(
213 p_bnft_prvdr_pool_id in number,
214 p_person_id in number,
215 p_per_in_ler_id in number,
216 p_effective_date in date,
217 p_datetrack_mode in varchar2,
218 p_acty_base_rt_id in number,
219 p_rlovr_amt in number,
220 p_old_rlovr_amt in number,
221 p_business_group_id in number,
222 p_enrt_mthd_cd in varchar2
223 );
224 --
225 procedure update_rate(p_prtt_rt_val_id in out nocopy number,
226 p_val in number,
227 p_prtt_enrt_rslt_id in number,
228 p_ended_per_in_ler_id in number,
229 p_effective_date in date,
230 p_business_group_id in number);
231 --
232 procedure compute_excess
233 (p_calculate_only_mode in boolean default false
234 ,p_bnft_prvdr_pool_id in number
235 ,p_flex_rslt_id in number
236 ,p_person_id in number
237 ,p_per_in_ler_id in number
238 ,p_enrt_mthd_cd in varchar2
239 ,p_effective_date in date
240 ,p_business_group_id in number
241 --
242 ,p_frftd_val out nocopy number
243 ,p_def_exc_amount out nocopy number
244 ,p_bpl_cash_recd_val out nocopy number
245 );
246 --
247 procedure recompute_flex_credits
248 (p_person_id in number
249 ,p_enrt_mthd_cd in varchar2
250 ,p_prtt_enrt_rslt_id in number
251 ,p_per_in_ler_id in number
252 ,p_pgm_id in number
253 ,p_business_group_id in number
254 ,p_effective_date in date
255 );
256 --
257 end ben_provider_pools;