1 package body ben_use_cvg_rt_date as
2 /* $Header: benuscrd.pkb 120.2.12010000.2 2008/09/30 06:22:22 krupani ship $ */
3 --
4 /*
5 +========================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +========================================================================+
10 */
11 /*
12 Name
13 Manage Life Events
14 Purpose
15 This package is used to check FONM validation
16 benuscrd process.
17 History
18 Date Who Version What?
19 ---- --- ------- -----
20 07 Apr 2004 tjesumic 115.0 Created.
21 22 Apr 2004 pbodla 115.1 Added procedure fonm_clear_down_cache
22 12 May 2004 pbodla 115.2 Some more clear cache routines added.
23 13 aug 2004 tjesumic 115.3 fonm dates are intialised
24 18 Aug 2004 tjesumic 115.4 ptip_id parameter added
25 20 Aug 2004 tjesumic 115.5 set/get_fonm added , used in BENEDSGB.pld
26 17 Nov 2005 mmudigon 115.6 Bug 4731069. Added rtp ch to
27 fonm_clear_down_cache
28 17 Mar 2006 stee 115.7 Bug 5095721 - hints for performance
29 improvement.
30 30-Sep-2008 krupani 115.8 Bug 7374364 - Created
31 procedure clear_fonm_globals
32 */
33 --------------------------------------------------------------------------------
34 --
35 g_package varchar2(80) := 'ben_use_cvg_rt_date';
36 g_debug boolean := hr_utility.debug_enabled;
37 --
38 -- to to get whether coverage fonm calc is define or not
39 procedure get_csd_rsd_Status( p_pgm_id in number default null
40 ,p_ptip_id in number default null
41 ,p_plip_id in number default null
42 ,p_pl_id in number default null
43 ,p_effective_date in date default null
44 ,p_status out nocopy varchar2
45 ) is
46
47 l_package varchar2(80) := g_package||'.get_csd_rsd_Status';
48 l_dummy varchar2(1);
49
50
51 cursor c_pgm is
52 select /*+ first_rows(1) */ 'x' -- 5095721
53 from ben_plip_f plip
54 where plip.pgm_id = p_pgm_id
55 and plip.USE_CSD_RSD_PRCCNG_CD is not null
56 and plip.plip_stat_cd = 'A'
57 and p_effective_date between plip.effective_start_date
58 and plip.effective_end_date
59 and rownum = 1 -- 5095721
60 ;
61
62 cursor c_ptip is
63 select /*+ first_rows(1) */ 'x' -- 5095721
64 from ben_plip_f plip, ben_ptip_f ptip, ben_pl_f pl
65 where ptip.ptip_id = p_ptip_id
66 and ptip.pgm_id = plip.pgm_id
67 and plip.pl_id = pl.pl_id
68 and pl.pl_typ_id = ptip.pl_typ_id
69 and plip.USE_CSD_RSD_PRCCNG_CD is not null
70 and plip.plip_stat_cd = 'A'
71 and ptip.ptip_stat_cd = 'A'
72 and p_effective_date between ptip.effective_start_date
73 and ptip.effective_end_date
74 and p_effective_date between pl.effective_start_date
75 and pl.effective_end_date
76 and p_effective_date between plip.effective_start_date
77 and plip.effective_end_date
78 and rownum = 1 -- 5095721
79 ;
80
81 cursor c_plip is
82 select 'x'
83 from ben_plip_f plip
84 where plip.plip_id = p_plip_id
85 and plip.USE_CSD_RSD_PRCCNG_CD is not null
86 and plip.plip_stat_cd = 'A'
87 and p_effective_date between plip.effective_start_date
88 and plip.effective_end_date
89 and rownum = 1; -- 5095721
90
91
92 cursor c_pl is
93 select 'x'
94 from ben_pl_f pl
95 where pl.pl_id = p_pl_id
96 and pl.USE_CSD_RSD_PRCCNG_CD is not null
97 and pl.pl_stat_cd = 'A'
98 and p_effective_date between pl.effective_start_date
99 and pl.effective_end_date
100 and rownum = 1; -- 5095721
101
102 Begin
103 g_debug := hr_utility.debug_enabled;
104 if g_debug then
105 hr_utility.set_location ('Entering '||l_package,10);
106 hr_utility.set_location ('p_plip_id '||p_plip_id,10);
107 hr_utility.set_location ('p_pl_id '||p_pl_id,10);
108 hr_utility.set_location ('p_pgm_id '||p_pgm_id,10);
109
110 end if;
111 -- initalise the global
112 ben_manage_life_events.g_fonm_cvg_strt_dt := null ;
113 ben_manage_life_events.g_fonm_rt_strt_dt := null ;
114 --
115 p_status := 'N' ;
116 if p_plip_id is not null then
117 open c_plip ;
118 fetch c_plip into l_dummy ;
119 if c_plip%found then
120 p_status := 'Y' ;
121 end if ;
122 close c_plip ;
123
124 elsif p_ptip_id is not null then
125 open c_ptip ;
126 fetch c_ptip into l_dummy ;
127 if c_ptip%found then
128 p_status := 'Y' ;
129 end if ;
130 close c_ptip ;
131
132
133 elsif p_pgm_id is not null then
134 open c_pgm ;
135 fetch c_pgm into l_dummy ;
136 if c_pgm%found then
137 p_status := 'Y' ;
138 end if ;
139 close c_pgm ;
140 elsif p_pl_id is not null then
141 open c_pl ;
142 fetch c_pl into l_dummy ;
143 if c_pl%found then
144 p_status := 'Y' ;
145 end if ;
146 close c_pl ;
147 end if ;
148
149 if g_debug then
150
151 hr_utility.set_location ('status '||p_status,10);
152 hr_utility.set_location ('Leaving '||l_package,10);
153 end if;
154
155 End ;
156 --
157 -- This procedure should only be called in fonm mode when the dates are
158 -- changed between the processing of comp objects.
159 --
160 procedure fonm_clear_down_cache is
161 --
162 --
163 begin
164 --
165 ben_person_object.clear_down_cache;
166 ben_evaluate_rate_profiles.init_globals;
167 ben_rt_prfl_cache.clear_down_cache;
168 ben_elp_cache.clear_down_cache;
169 ben_cep_cache.clear_down_cache;
170 ben_elig_rl_cache.clear_down_cache;
171 ben_rtp_cache.clear_down_cache;
172 --
173 -- 9999 look at the some of the cache clears from list below.
174 -- DO NOT UNCOMMENT , ANY SPECIFIC CLEAR CACHE NEEDED MOVE IT OUT OF
175 -- COMMENT.
176 --
177 /*
178 ben_person_object.defrag_caches;
179 ben_epe_cache.init_context_cobj_pileperow;
180 ben_epe_cache.init_context_pileperow ;
181 ben_epe_cache.clear_down_cache;
182 ben_cobj_cache.clear_down_cache;
183 ben_comp_object.clear_down_cache;
184 ben_elig_object.clear_down_cache;
185 ben_seeddata_object.clear_down_cache;
186 ben_manage_life_events.g_cache_person_prtn.delete;
187 ben_derive_part_and_rate_cache.clear_down_cache;
188 ben_derive_prt_and_rate_cache1.clear_down_cache;
189 ben_derive_part_and_rate_facts.clear_down_cache;
190 ben_derive_part_and_rate_cvg.clear_down_cache;
191 ben_derive_part_and_rate_prem.clear_down_cache;
192 ben_cel_cache.clear_down_cache;
193 ben_org_object.clear_down_cache;
194 --
195 ben_life_object.clear_down_cache;
196 ben_location_object.clear_down_cache;
197 ben_org_object.clear_down_cache;
198 --
199 ben_letrg_cache.clear_down_cache;
200 ben_cagrelp_cache.clear_down_cache;
201
202 -- from clear_enroll_caches
203 ben_batch_dt_api.clear_down_cache;
204 ben_element_entry.clear_down_cache;
205
206 ben_cop_cache.clear_down_cache;
207 ben_pep_cache.clear_down_cache;
208 ben_distribute_rates.clear_down_cache;
209 ben_pil_cache.clear_down_cache;
210 ben_pln_cache.clear_down_cache;
211 ben_saz_cache.clear_down_cache;
212 ben_seeddata_object.clear_down_cache;
213 benutils.clear_down_cache;
214 ben_pil_object.clear_down_cache;
215 ben_rt_asnt_cache.clear_down_cache;
216
217 */
218 --
219 end fonm_clear_down_cache;
220 --
221
222 procedure get_fonm (p_fonm out nocopy varchar2 ,
223 p_fonm_cvg_strt_dt out nocopy date ,
224 p_fonm_rt_strt_dt out nocopy date
225 ) is
226
227 l_proc varchar2(400) := g_package||'.get_fonm' ;
228 begin
229
230 if g_debug then
231 hr_utility.set_location ('Entering '||l_proc,10);
232 end if;
233
234 p_fonm := ben_manage_life_events.fonm ;
235 p_fonm_cvg_strt_dt := ben_manage_life_events.g_fonm_cvg_strt_dt ;
236 p_fonm_rt_strt_dt := ben_manage_life_events.g_fonm_rt_strt_dt ;
237
238
239 if g_debug then
240 hr_utility.set_location ('Leaving '||l_proc,10);
241 end if;
242
243 end get_fonm ;
244
245 procedure set_fonm (p_fonm in varchar2 ,
246 p_fonm_cvg_strt_dt in date default null ,
247 p_fonm_rt_strt_dt in date default null
248 ) is
249
250 l_proc varchar2(400) := g_package||'.set_fonm' ;
251 begin
252
253 if g_debug then
254 hr_utility.set_location ('Entering '||l_proc,10);
255 end if;
256
257 ben_manage_life_events.fonm := p_fonm ;
258 if p_fonm = 'N' then
259 ben_manage_life_events.g_fonm_cvg_strt_dt := null ;
260 ben_manage_life_events.g_fonm_rt_strt_dt := null ;
261 else
262 if p_fonm_cvg_strt_dt is not null then
263 ben_manage_life_events.g_fonm_cvg_strt_dt := p_fonm_cvg_strt_dt ;
264 end if ;
265
266 if p_fonm_rt_strt_dt is not null then
267 ben_manage_life_events.g_fonm_rt_strt_dt := p_fonm_rt_strt_dt ;
268 end if ;
269
270 end if ;
271
272
273 if g_debug then
274 hr_utility.set_location ('Leaving '||l_proc,10);
275 end if;
276 end set_fonm ;
277
278 procedure clear_fonm_globals is
279 --
280 l_proc varchar2(400) := g_package||'.clear_fonm_globals';
281
282 begin
283 --
284 hr_utility.set_location ('Entering '||l_proc,10);
285
286 ben_manage_life_events.fonm := null ;
287 ben_manage_life_events.g_fonm_cvg_strt_dt := null ;
288 ben_manage_life_events.g_fonm_rt_strt_dt := null;
289
290 hr_utility.set_location ('Leaving '||l_proc,10);
291 --
292 end clear_fonm_globals;
293
294 end ben_use_cvg_rt_date;
[Home] [Help]