1 package ben_derive_part_and_rate_facts AUTHID CURRENT_USER as
2 /* $Header: bendrpar.pkh 120.0.12000000.1 2007/01/19 15:55:20 appldev noship $ */
3 --------------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10
11 Name
12 Derive Participation and Rate Factors
13 Purpose
14 This package is used to derive the participation and rate factor
15 information for a particular person for a particular program, plan
16 or option.
17 History
18 Date Who Version What?
19 ---- --- ------- -----
20 14 Dec 97 G Perry 110.0 Created.
21 15 Dec 97 G Perry 110.1 Changed type of g_min_max_breach
22 to a boolean so that we handle
23 the breach logic differently.
24 Made all functions public and
25 made caching utility public.
26 04 Jan 98 G Perry 110.2 Added exception g_record_error.
27 Changed public interfaces to
28 remove ler_id and oipl_id as
29 these are not needed.
30 05 Jan 98 G Perry 110.3 removed g_record_error
31 exception as we now use the
32 generic exception raised by
33 the benmngle process.
34 13 Jan 98 G Perry 110.4 Added extra parameter to
35 cache function.
36 24 Jan 98 G Perry 110.5 Now caching next years popl
37 start date.
38 06 Mar 98 G Perry 110.6 Added caching structures to
39 store los and age details. This
40 makes the run more performant.
41 08 Apr 98 G Perry 110.7 Added get_calculated_age, added
42 all FF stuff. Made code more
43 reusable.
44 20 Apr 98 G Perry 110.8 Now caching comb_age and
45 comb_los.
46 11 Apr 98 G Perry 110.9 Added cache structures so we
47 only create temporal life
48 events once.
49 13 Jun 98 G Perry 110.10 Made clear_down_cache public.
50 26 Aug 98 G Perry 115.4 Added cmbn_age_n_los_val into
51 g_cache_details structure.
52 28 Aug 98 G Perry 115.5 Removed cache where we store
53 latest temporal life event date
54 information.
55 23 Nov 98 G Perry 115.6 Added rate information into
56 cache. Added support for rates
57 as well as factors.
58 18 Jan 99 G Perry 115.8 LED V ED
59 08 Feb 99 G Perry 115.9 Added in ptnl_ler_trtmt_cd in
60 all procedures.
61 17 Feb 99 G Perry 115.10 Added in once_r_cntug_cd and
62 elig_flag into cache structure.
63 21 Apr 99 G Perry 115.11 Added new procedure and function
64 set_potential_ler_id
65 get_potential_ler_id
66 05 May 99 G Perry 115.12 Added support for PTIP, PLIP.
67 15 Nov 99 S Tee 115.13 Added determine_cobra_eligibility.
68 05-Feb-00 maagrawa 115.14 Data type of prtn_ovrid_thru_dt
69 changed to date (Bug 1169243)
70 26-Feb-00 mhoyes 115.15 - Added p_comp_obj_tree_row parameter
71 to all routines which use
72 ben_env_object comp object values.
73 28-Feb-00 stee 115.16 - Added p_cbr_tmprl_evt_flag
74 parameter.
75 05-Mar-00 stee 115.17 - Added ptip_id to
76 determine_cobra_eligibility.
77 05-Mar-00 tmathers 115.18 - readded missing comment end
78 24-Mar-00 gperry 115.19 Changed g_cache_structure for
79 factor values to number else
80 it truncs values implicitly due
81 to number(15) type.
82 31-Mar-00 gperry 115.20 Added oiplip support.
83 26-Jun-00 stee 115.21 Added p_derivable_factors to
84 derive_rate_and_factors
85 procedure. Removed
86 p_cbr_tmprl_evt_flag.
87 27-Jun-00 stee 115.21 Added p_derivable_factors to
88 derive_rate_and_factors
89 procedure. Removed
90 p_cbr_tmprl_evt_flag.
91 27-Jun-00 mhoyes 115.22 - Added p_business_group_id to
92 cache_data_structures.
93 - Extended g_cache_structure for
94 the elig per caches.
95 30-Jun-00 mhoyes 115.23 - Added context parameters.
96 13-Jul-00 mhoyes 115.24 - Added NOCOPYs.
97 03-Aug-00 mhoyes 115.25 - Removed get_temporal_ler_id.
98 - Commented out hr_utility statements
99 were causing 1000000 executions in
100 temporal mode for 25 people.
101 07-Jan-01 mhoyes 115.26 - Made comp_calculation public.
102 06-Apr-01 mhoyes 115.27 - Added p_calculate_only_mode for EFC.
103 30-jan-02 tjesumic 115.28 - set_taxunit_context procedure added
104 to set the tax_unit_context before calling
105 per_balance_pkg.set_value, bug: 2180602
106 01-feb-02 tjesumic 115.29 dbdrv fixed
107 01-feb-02 tjesumic 115.30 dbdrv fixed
108 01-jul-03 pabodla 115.31 Grade/Step Added global variables
109 to support grade/step life event
110 triggering.
111 19-Aug-03 mmudigon 115.32 gscc fix
112 11-Nov-03 ikasire 115.33 Added g_no_ptnl_ler_id for getting
113 not to trigger potentials as part of
114 U,W,M,P,I,A Modes BUG 3243960
115 --------------------------------------------------------------------------------
116 */
117 --
118 type g_cache_structure is record
119 (los_val number
120 ,age_val number
121 ,comp_ref_amt number
122 ,hrs_wkd_val number
123 ,pct_fl_tm_val number
124 ,cmbn_age_n_los_val number
125 ,age_uom varchar2(30)
126 ,los_uom varchar2(30)
127 ,comp_ref_uom varchar2(30)
128 ,hrs_wkd_bndry_perd_cd varchar2(30)
129 ,frz_los_flag varchar2(30)
130 ,frz_age_flag varchar2(30)
131 ,frz_hrs_wkd_flag varchar2(30)
132 ,frz_cmp_lvl_flag varchar2(30)
133 ,frz_pct_fl_tm_flag varchar2(30)
134 ,frz_comb_age_and_los_flag varchar2(30)
135 ,rt_los_val number
136 ,rt_age_val number
137 ,rt_comp_ref_amt number
138 ,rt_hrs_wkd_val number
139 ,rt_pct_fl_tm_val number
140 ,rt_cmbn_age_n_los_val number
141 ,rt_age_uom varchar2(30)
142 ,rt_los_uom varchar2(30)
143 ,rt_comp_ref_uom varchar2(30)
144 ,rt_hrs_wkd_bndry_perd_cd varchar2(30)
145 ,rt_frz_los_flag varchar2(30)
146 ,rt_frz_age_flag varchar2(30)
147 ,rt_frz_hrs_wkd_flag varchar2(30)
148 ,rt_frz_cmp_lvl_flag varchar2(30)
149 ,rt_frz_pct_fl_tm_flag varchar2(30)
150 ,rt_frz_comb_age_and_los_flag varchar2(30)
151 ,ovrid_svc_dt date
152 ,prtn_ovridn_flag varchar2(30)
153 ,prtn_ovridn_thru_dt date
154 ,comb_age number
155 ,comb_los number
156 ,comb_rt_age number
157 ,comb_rt_los number
158 ,once_r_cntug_cd varchar2(30)
159 ,elig_flag varchar2(30)
160 ,pgm_id number
161 ,ptip_id number
162 ,pl_id number
163 ,plip_id number
164 ,opt_id number
165 ,prtn_strt_dt date
166 ,prtn_end_dt date
167 ,elig_per_opt_id number
168 ,object_version_number number
169 ,elig_per_id number
170 ,per_in_ler_id number
171 ,pep_prtn_strt_dt date
172 ,pep_prtn_end_dt date
173 );
174 --
175 -- Variable which represents the cache data structure
176 --
177 g_cache_details g_cache_structure;
178 --
179 -- GRADE/STEP :variables added for Grade/step.
180 g_prev_pgm_id NUMBER;
181 g_pgm_typ_cd varchar2(30);
182 g_gsp_ler_id NUMBER;
183 g_gsp_ler_name varchar2(600);
184 g_temp_ler_id NUMBER;
185 --Added to restrict triggering of potentials for Unrestricted LER
186 g_no_ptnl_ler_id NUMBER;
187 --
188 FUNCTION comp_calculation
189 (p_comp_obj_tree_row IN ben_manage_life_events.g_cache_proc_objects_rec
190 ,p_empasg_row IN per_all_assignments_f%ROWTYPE
191 ,p_benasg_row IN per_all_assignments_f%ROWTYPE
192 ,p_curroiplip_row IN ben_cobj_cache.g_oiplip_inst_row
193 ,p_rec IN ben_derive_part_and_rate_cache.g_cache_clf_rec_obj
194 ,p_person_id IN NUMBER
195 ,p_business_group_id IN NUMBER
196 ,p_pgm_id IN NUMBER
197 ,p_pl_id IN NUMBER
198 ,p_oipl_id IN NUMBER
199 ,p_oiplip_id IN NUMBER
200 ,p_plip_id IN NUMBER
201 ,p_ptip_id IN NUMBER
202 ,p_effective_date IN DATE
203 ,p_lf_evt_ocrd_dt IN DATE
204 ) RETURN NUMBER;
205 --
206 PROCEDURE derive_rates_and_factors
207 (p_calculate_only_mode in boolean default false
208 ,p_comp_obj_tree_row IN OUT NOCOPY ben_manage_life_events.g_cache_proc_objects_rec
209 --
210 -- Context info
211 --
212 ,p_per_row IN OUT NOCOPY per_all_people_f%ROWTYPE
213 ,p_empasg_row IN OUT NOCOPY per_all_assignments_f%ROWTYPE
214 ,p_benasg_row IN OUT NOCOPY per_all_assignments_f%ROWTYPE
215 ,p_pil_row IN OUT NOCOPY ben_per_in_ler%ROWTYPE
216 --
217 ,p_mode IN VARCHAR2 DEFAULT NULL
218 --
219 ,p_effective_date IN DATE
220 ,p_lf_evt_ocrd_dt IN DATE
221 ,p_person_id IN NUMBER
222 ,p_business_group_id IN NUMBER
223 ,p_pgm_id IN NUMBER DEFAULT NULL
224 ,p_pl_id IN NUMBER DEFAULT NULL
225 ,p_oipl_id IN NUMBER DEFAULT NULL
226 ,p_plip_id IN NUMBER DEFAULT NULL
227 ,p_ptip_id IN NUMBER DEFAULT NULL
228 ,p_ptnl_ler_trtmt_cd IN VARCHAR2 DEFAULT NULL
229 ,p_derivable_factors IN VARCHAR2 DEFAULT 'ASC'
230 ,p_comp_rec IN OUT NOCOPY g_cache_structure
231 ,p_oiplip_rec IN OUT NOCOPY g_cache_structure
232 );
233 -----------------------------------------------------------------------
234 procedure cache_data_structures
235 (p_comp_obj_tree_row in out NOCOPY ben_manage_life_events.g_cache_proc_objects_rec
236 ,p_empasg_row in out NOCOPY per_all_assignments_f%rowtype
237 ,p_benasg_row in out NOCOPY per_all_assignments_f%rowtype
238 ,p_pil_row in out NOCOPY ben_per_in_ler%rowtype
239 ,p_business_group_id in number
240 ,p_effective_date in date
241 ,p_person_id in number
242 ,p_pgm_id in number
243 ,p_pl_id in number
244 ,p_oipl_id in number
245 ,p_plip_id in number
246 ,p_ptip_id in number
247 ,p_comp_rec in out NOCOPY g_cache_structure
248 ,p_oiplip_rec in out NOCOPY g_cache_structure
249 );
250 -----------------------------------------------------------------------
251 procedure determine_cobra_eligibility
252 (p_calculate_only_mode in boolean default false
253 ,p_person_id in number
254 ,p_business_group_id in number
255 ,p_pgm_id in number
256 ,p_ptip_id in number default null
257 ,p_ptnl_ler_trtmt_cd in varchar2
258 ,p_effective_date in date
259 ,p_lf_evt_ocrd_dt in date
260 ,p_derivable_factors in varchar2
261 );
262 -----------------------------------------------------------------------
263 procedure clear_down_cache;
264 -----------------------------------------------------------------------
265 procedure set_taxunit_context
266 (p_person_id in number
267 ,p_business_group_id in number
268 ,p_effective_date in date
269 );
270 -----------------------------------------------------------------------
271 function get_latest_paa_id
272 (p_person_id in number
273 ,p_business_group_id in number
274 ,p_effective_date in date
275 ) return number;
276 -----------------------------------------------------------------------
277 end ben_derive_part_and_rate_facts;
[Home] [Help]