1 package ben_manage_cwb_life_events as
2 /* $Header: bencwbcm.pkh 120.6.12000000.1 2007/01/19 15:22:40 appldev noship $ */
3 /*
4 +==============================================================================+
5 | Copyright (c) 1997 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +==============================================================================+
9
10 Name
11 Manage Life Events
12 Purpose
13 This package is used to handle cwb pre-benmngle process and post
14 benmngle cwb relevent actions like populating the cross business group
15 and other hierarchy data.
16 History
17 Date Who Version What?
18 ---- --- ------- -----
19 19 Dec 03 pbodla/
20 Indrasen 115.0 Created.
21 22 Dec 03 Indrase 115.1 Added new procedure and
22 globals.
23 05 Jan 05 Pbodla 115.2 Added CWB table record structures
24 07 Jan 03 pbodla 115.5 Added code to populate cwb_rates
25 09 Jan 03 pbodla 115.6 Added code for auto budget issue
26 17 Jan 03 pbodla 115.8 Added code to populate missing pils
27 ikasire Added p_group_pl_id to get_group_plan_info
28 05 Feb 04 ikasire 115.9 Added online call- new procedure
29 global_online_process_w
30 25 Feb 04 pbodla 115.10 Added params p_no_person_rates,
31 p_no_person_groups for the
32 populate_cwb_rates procedure.
33 26 Feb 04 ikasire 115.11 rebuild_heirarchy externalised
34 21 Jul 04 pbodla 115.12 Added logic for single person run.
35 Procedures modified are :
36 popu_cwb_tables, popu_cwb_group_pil_data
37 21 Jul 04 pbodla 115.12 Added p_use_eff_dt_flag to
38 global_online_process_w, if front end
39 takes the decision to take full control
40 to clone data then this flag is passed
41 as Y.
42 08 oct 04 pbodla 115.14 Added extra parameters to
43 global_online_process_w, to handle
44 backout.
45 01 nov 04 pbodla 115.15 Added global g_options_exists
46 Added procedure
47 sum_oipl_rates_and_upd_pl_rate
48 Bug : 3968065
49 01 nov 04 pbodla 115.16
50 06-Dec-04 bmanyam 115.17 Bug: 3979082. Added hrchy_ame_trn_cd and hrchy_rl
51 to g_cache_group_plan_type
52 14-Dec-04 pbodla 115.18 bug 4040013 - g_options_exists is defaulted to null.
53 21-Feb-05 pbodla 115.19 bug 4198404 - Added record structure
54 g_error_log_rec_type to log proper
55 error messages.
56 03-Jan-06 nhunur 115.20 cwb - changes for person type param.
57 08-Feb-06 abparekh 115.21 Bug 4875181 - Added p_run_rollup_only to global_process
58 08-Feb-06 mmudigon 115.22 CWB Multi currency support. Added
59 procs() exec_element_det_rl and
60 get_abr_ele_inp
61 determine_curr_code
62 08-Feb-06 pbodla/stee 115.22 CWB Multi currency support. Added
63 determine_curr_code
64 16-Mar-06 nhunur 115.23 Made exec_element_det_rl public.
65 24-Mar-06 maagrawa 115.24 GSCC nocopy error fixed.
66 22-May-06 pbodla 115.25 Bug 5232223 - Added code to handle the trk inelig flag
67 If trk inelig flag is set to N at group plan level
68 then do not create cwb per in ler and all associated data.
69 */
70 --------------------------------------------------------------------------------
71 --
72 --
73 g_trk_inelig_flag varchar2(1);
74 type g_cwb_processes_table is table of number index by binary_integer;
75 g_cwb_processes_rec g_cwb_processes_table;
76 g_num_cwb_processes number := 0;
77 type g_cache_group_plan_type is record
78 (group_pl_id ben_pl_f.pl_id%type,
79 group_lf_evt_ocrd_dt date,
80 group_ler_id ben_ler_f.ler_id%type,
81 group_business_group_id number,
82 hrchy_to_use_cd varchar2(30),
83 pos_structure_version_id number,
84 group_per_in_ler_id number,
85 access_cd varchar2(30),
86 plans_wthn_group_pl number,
87 end_dt date,
88 auto_distr_flag varchar2(30),
89 ws_upd_strt_dt date,
90 ws_upd_end_dt date,
91 uses_bdgt_flag varchar2(30),
92 hrchy_ame_trn_cd varchar2(30),
93 hrchy_rl number,
94 -- Bug 5232223
95 trk_inelig_per_flag varchar2(30));
96 --
97 g_cwb_person_groups_rec ben_cwb_person_groups%ROWTYPE;
98 g_cwb_person_rates_rec ben_cwb_person_rates%ROWTYPE;
99 g_cwb_person_groups_rec_temp ben_cwb_person_groups%ROWTYPE;
100 g_cwb_person_rates_rec_temp ben_cwb_person_rates%ROWTYPE;
101 g_options_exists boolean;
102 --
103 g_cache_group_plan_rec g_cache_group_plan_type;
104 --
105 type g_cache_cpg_rec_table is table of ben_cwb_person_groups%rowtype index
106 by binary_integer;
107 --
108 g_cache_cpg_rec g_cache_cpg_rec_table;
109 --
110 -- Structure for Error logging .
111 --
112 type g_error_log_rec_type is record
113 (group_pl_id ben_pl_f.pl_id%type,
114 group_lf_evt_ocrd_dt date,
115 person_id number,
116 assignment_id number,
117 mgr_person_id number,
118 group_per_in_ler_id number,
119 step_number number,
120 calling_proc varchar2(200));
121 --
122 g_error_log_rec g_error_log_rec_type;
123 -----------------------------------------------------------------------
124 procedure popu_group_pil_heir(p_group_pl_id in number,
125 p_group_lf_evt_ocrd_dt in date,
126 p_group_business_group_id in number,
127 p_group_ler_id in number);
128
129 --
130 procedure get_group_plan_info(p_pl_id in number,
131 p_lf_evt_ocrd_dt in date,
132 p_business_group_id in number default null,
133 -- 9999IK Not required if we only run for group pl
134 p_group_pl_id in number default null
135 );
136 --
137 procedure popu_cwb_tables(
138 p_group_per_in_ler_id in number,
139 p_group_pl_id in number,
140 p_group_lf_evt_ocrd_dt in date,
141 p_group_business_group_id in number,
142 p_group_ler_id in number,
143 p_use_eff_dt_flag in varchar2 default 'N',
144 p_effective_date in date default null);
145 --
146 procedure get_cwb_manager_and_assignment
147 (p_person_id in number,
148 p_hrchy_to_use_cd in varchar2,
149 p_pos_structure_version_id in number,
150 p_effective_date in date,
151 p_manager_id out nocopy number,
152 p_assignment_id out nocopy number );
153 -----------------------------------------------------------------------
154 procedure global_process
155 (errbuf out nocopy varchar2
156 ,retcode out nocopy number
157 ,p_benefit_action_id in number default null
158 ,p_effective_date in varchar2
159 ,p_mode in varchar2 default 'W'
160 ,p_derivable_factors in varchar2 default 'ASC'
161 ,p_validate in varchar2 default 'N'
162 ,p_person_id in number default null
163 ,p_pgm_id in number default null
164 ,p_business_group_id in number
165 ,p_pl_id in number default null
166 ,p_popl_enrt_typ_cycl_id in number default null
167 ,p_lf_evt_ocrd_dt in varchar2 default null
168 ,p_person_type_id in number default null
169 ,p_no_programs in varchar2 default 'N'
170 ,p_no_plans in varchar2 default 'N'
171 ,p_comp_selection_rule_id in number default null
172 ,p_person_selection_rule_id in number default null
173 ,p_ler_id in number default null
174 ,p_organization_id in number default null
175 ,p_benfts_grp_id in number default null
176 ,p_location_id in number default null
177 ,p_pstl_zip_rng_id in number default null
178 ,p_rptg_grp_id in number default null
179 ,p_pl_typ_id in number default null
180 ,p_opt_id in number default null
181 ,p_eligy_prfl_id in number default null
182 ,p_vrbl_rt_prfl_id in number default null
183 ,p_legal_entity_id in number default null
184 ,p_payroll_id in number default null
185 ,p_commit_data in varchar2 default 'Y'
186 ,p_audit_log_flag in varchar2 default 'N'
187 ,p_lmt_prpnip_by_org_flag in varchar2 default 'N'
188 ,p_cbr_tmprl_evt_flag in varchar2 default 'N'
189 ,p_trace_plans_flag in varchar2 default 'N'
190 ,p_online_call_flag in varchar2 default 'N'
191 ,p_clone_all_data_flag in varchar2 default 'N'
192 ,p_cwb_person_type in varchar2 default NULL
193 ,p_run_rollup_only in varchar2 default 'N' /* Bug 4875181 */
194 );
195 --
196 -----------------------------------------------------------------------
197 procedure populate_cwb_rates(
198 --
199 -- Columns needed for ben_cwb_person_rates
200 --
201 p_person_id in number
202 ,p_assignment_id in number default null
203 ,p_organization_id in number default null
204 ,p_pl_id in number
205 ,p_oipl_id in number default null
206 ,p_opt_id in number default null
207 ,p_ler_id in number default null
208 ,p_business_group_id in number default null
209 ,p_acty_base_rt_id in number default null
210 ,p_elig_flag in varchar2 default 'Y'
211 ,p_inelig_rsn_cd in varchar2 default null
212 --
213 -- Columns needed by BEN_CWB_PERSON_GROUPS
214 --
215 ,p_due_dt in date default null
216 ,p_access_cd in varchar2 default null
217 ,p_elig_per_elctbl_chc_id in number default null
218 ,p_no_person_rates in varchar2 default null
219 ,p_no_person_groups in varchar2 default null
220 ,p_currency_det_cd in varchar2 default null
221 ,p_element_det_rl in number default null
222 ,p_base_element_type_id in number default null
223 );
224 -----------------------------------------------------------------------
225 procedure popu_cwb_group_pil_data (
226 p_group_per_in_ler_id in number,
227 p_group_pl_id in number,
228 p_group_lf_evt_ocrd_dt in date,
229 p_group_business_group_id in number,
230 p_group_ler_id in number,
231 p_use_eff_dt_flag in varchar2 default 'N',
232 p_effective_date in date default null);
233 ----------------------------------------------------------------------
234 procedure global_online_process_w
235 (
236 p_effective_date in date
237 ,p_validate in varchar2 default 'N'
238 ,p_person_id in number default null
239 ,p_business_group_id in number
240 ,p_pl_id in number default null
241 ,p_lf_evt_ocrd_dt in date default null
242 ,p_clone_all_data_flag in varchar2 default 'N'
243 ,p_backout_and_process_flag in varchar2 default 'N'
244 );
245 ----------------------------------------------------------------------
246 procedure rebuild_heirarchy
247 (p_group_per_in_ler_id in number ) ;
248 ----------------------------------------------------------------------
249 ----------------------------------------------------------------------
250 procedure sum_oipl_rates_and_upd_pl_rate (
251 p_pl_id in number,
252 p_group_pl_id in number,
253 p_lf_evt_ocrd_dt in date,
254 p_person_id in number,
255 p_assignment_id in number
256 );
257 ----------------------------------------------------------------------
258 procedure auto_allocate_budgets (
259 p_pl_id in number default null,
260 p_group_pl_id in number,
261 p_lf_evt_ocrd_dt in date,
262 p_person_id in number default null,
263 p_assignment_id in number default null
264 );
265 ----------------------------------------------------------------------
266 procedure determine_curr_code
267 (p_element_det_rl number default null,
268 p_acty_base_rt_id number default null,
269 p_currency_det_cd varchar2 default null,
270 p_base_element_type_id number default null,
271 p_effective_date date,
272 p_assignment_id number default null,
273 p_organization_id number default null,
274 p_business_group_id number default null,
275 p_pl_id number default null,
276 p_opt_id number default null,
277 p_ler_id number default null,
278 p_element_type_id out nocopy number,
279 p_input_value_id out nocopy number,
280 p_currency_cd out nocopy varchar2);
281 ----------------------------------------------------------------------
282 procedure get_abr_ele_inp
283 (p_person_id number,
284 p_acty_base_rt_id number,
285 p_effective_date date,
286 p_element_type_id_in number default null,
287 p_input_value_id_in number default null,
288 p_pl_id number default null,
289 p_element_type_id_out out nocopy number,
290 p_input_value_id_out out nocopy number );
291 ----------------------------------------------------------------------
292 procedure exec_element_det_rl
293 (p_element_det_rl number default null,
294 p_acty_base_rt_id number default null,
295 p_effective_date date,
296 p_assignment_id number default null,
297 p_organization_id number default null,
298 p_business_group_id number default null,
299 p_pl_id number default null,
300 p_ler_id number default null,
301 p_element_type_id out nocopy number,
302 p_input_value_id out nocopy number,
303 p_currency_cd out nocopy varchar2);
304 ----------------------------------------------------------------------
305 end ben_manage_cwb_life_events;
[Home] [Help]