1 package body ben_use_cvg_rt_date as
2 /* $Header: benuscrd.pkb 120.2 2006/03/17 11:47:30 stee noship $ */
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 */
31 --------------------------------------------------------------------------------
32 --
33 g_package varchar2(80) := 'ben_use_cvg_rt_date';
34 g_debug boolean := hr_utility.debug_enabled;
35 --
36 -- to to get whether coverage fonm calc is define or not
37 procedure get_csd_rsd_Status( p_pgm_id in number default null
38 ,p_ptip_id in number default null
39 ,p_plip_id in number default null
40 ,p_pl_id in number default null
41 ,p_effective_date in date default null
42 ,p_status out nocopy varchar2
43 ) is
44
45 l_package varchar2(80) := g_package||'.get_csd_rsd_Status';
46 l_dummy varchar2(1);
47
48
49 cursor c_pgm is
50 select /*+ first_rows(1) */ 'x' -- 5095721
51 from ben_plip_f plip
52 where plip.pgm_id = p_pgm_id
53 and plip.USE_CSD_RSD_PRCCNG_CD is not null
54 and plip.plip_stat_cd = 'A'
55 and p_effective_date between plip.effective_start_date
56 and plip.effective_end_date
57 and rownum = 1 -- 5095721
58 ;
59
60 cursor c_ptip is
61 select /*+ first_rows(1) */ 'x' -- 5095721
62 from ben_plip_f plip, ben_ptip_f ptip, ben_pl_f pl
63 where ptip.ptip_id = p_ptip_id
64 and ptip.pgm_id = plip.pgm_id
65 and plip.pl_id = pl.pl_id
66 and pl.pl_typ_id = ptip.pl_typ_id
67 and plip.USE_CSD_RSD_PRCCNG_CD is not null
68 and plip.plip_stat_cd = 'A'
69 and ptip.ptip_stat_cd = 'A'
70 and p_effective_date between ptip.effective_start_date
71 and ptip.effective_end_date
72 and p_effective_date between pl.effective_start_date
73 and pl.effective_end_date
74 and p_effective_date between plip.effective_start_date
75 and plip.effective_end_date
76 and rownum = 1 -- 5095721
77 ;
78
79 cursor c_plip is
80 select 'x'
81 from ben_plip_f plip
82 where plip.plip_id = p_plip_id
83 and plip.USE_CSD_RSD_PRCCNG_CD is not null
84 and plip.plip_stat_cd = 'A'
85 and p_effective_date between plip.effective_start_date
86 and plip.effective_end_date
87 and rownum = 1; -- 5095721
88
89
90 cursor c_pl is
91 select 'x'
92 from ben_pl_f pl
93 where pl.pl_id = p_pl_id
94 and pl.USE_CSD_RSD_PRCCNG_CD is not null
95 and pl.pl_stat_cd = 'A'
96 and p_effective_date between pl.effective_start_date
97 and pl.effective_end_date
98 and rownum = 1; -- 5095721
99
100 Begin
101 g_debug := hr_utility.debug_enabled;
102 if g_debug then
103 hr_utility.set_location ('Entering '||l_package,10);
104 hr_utility.set_location ('p_plip_id '||p_plip_id,10);
105 hr_utility.set_location ('p_pl_id '||p_pl_id,10);
106 hr_utility.set_location ('p_pgm_id '||p_pgm_id,10);
107
108 end if;
109 -- initalise the global
110 ben_manage_life_events.g_fonm_cvg_strt_dt := null ;
111 ben_manage_life_events.g_fonm_rt_strt_dt := null ;
112 --
113 p_status := 'N' ;
114 if p_plip_id is not null then
115 open c_plip ;
116 fetch c_plip into l_dummy ;
117 if c_plip%found then
118 p_status := 'Y' ;
119 end if ;
120 close c_plip ;
121
122 elsif p_ptip_id is not null then
123 open c_ptip ;
124 fetch c_ptip into l_dummy ;
125 if c_ptip%found then
126 p_status := 'Y' ;
127 end if ;
128 close c_ptip ;
129
130
131 elsif p_pgm_id is not null then
132 open c_pgm ;
133 fetch c_pgm into l_dummy ;
134 if c_pgm%found then
135 p_status := 'Y' ;
136 end if ;
137 close c_pgm ;
138 elsif p_pl_id is not null then
139 open c_pl ;
140 fetch c_pl into l_dummy ;
141 if c_pl%found then
142 p_status := 'Y' ;
143 end if ;
144 close c_pl ;
145 end if ;
146
147 if g_debug then
148
149 hr_utility.set_location ('status '||p_status,10);
150 hr_utility.set_location ('Leaving '||l_package,10);
151 end if;
152
153 End ;
154 --
155 -- This procedure should only be called in fonm mode when the dates are
156 -- changed between the processing of comp objects.
157 --
158 procedure fonm_clear_down_cache is
159 --
160 --
161 begin
162 --
163 ben_person_object.clear_down_cache;
164 ben_evaluate_rate_profiles.init_globals;
165 ben_rt_prfl_cache.clear_down_cache;
166 ben_elp_cache.clear_down_cache;
167 ben_cep_cache.clear_down_cache;
168 ben_elig_rl_cache.clear_down_cache;
169 ben_rtp_cache.clear_down_cache;
170 --
171 -- 9999 look at the some of the cache clears from list below.
172 -- DO NOT UNCOMMENT , ANY SPECIFIC CLEAR CACHE NEEDED MOVE IT OUT OF
173 -- COMMENT.
174 --
175 /*
176 ben_person_object.defrag_caches;
177 ben_epe_cache.init_context_cobj_pileperow;
178 ben_epe_cache.init_context_pileperow ;
179 ben_epe_cache.clear_down_cache;
180 ben_cobj_cache.clear_down_cache;
181 ben_comp_object.clear_down_cache;
182 ben_elig_object.clear_down_cache;
183 ben_seeddata_object.clear_down_cache;
184 ben_manage_life_events.g_cache_person_prtn.delete;
185 ben_derive_part_and_rate_cache.clear_down_cache;
186 ben_derive_prt_and_rate_cache1.clear_down_cache;
187 ben_derive_part_and_rate_facts.clear_down_cache;
188 ben_derive_part_and_rate_cvg.clear_down_cache;
189 ben_derive_part_and_rate_prem.clear_down_cache;
190 ben_cel_cache.clear_down_cache;
191 ben_org_object.clear_down_cache;
192 --
193 ben_life_object.clear_down_cache;
194 ben_location_object.clear_down_cache;
195 ben_org_object.clear_down_cache;
196 --
197 ben_letrg_cache.clear_down_cache;
198 ben_cagrelp_cache.clear_down_cache;
199
200 -- from clear_enroll_caches
201 ben_batch_dt_api.clear_down_cache;
202 ben_element_entry.clear_down_cache;
203
204 ben_cop_cache.clear_down_cache;
205 ben_pep_cache.clear_down_cache;
206 ben_distribute_rates.clear_down_cache;
207 ben_pil_cache.clear_down_cache;
208 ben_pln_cache.clear_down_cache;
209 ben_saz_cache.clear_down_cache;
210 ben_seeddata_object.clear_down_cache;
211 benutils.clear_down_cache;
212 ben_pil_object.clear_down_cache;
213 ben_rt_asnt_cache.clear_down_cache;
214
215 */
216 --
217 end fonm_clear_down_cache;
218 --
219
220 procedure get_fonm (p_fonm out nocopy varchar2 ,
221 p_fonm_cvg_strt_dt out nocopy date ,
222 p_fonm_rt_strt_dt out nocopy date
223 ) is
224
225 l_proc varchar2(400) := g_package||'.get_fonm' ;
226 begin
227
228 if g_debug then
229 hr_utility.set_location ('Entering '||l_proc,10);
230 end if;
231
232 p_fonm := ben_manage_life_events.fonm ;
233 p_fonm_cvg_strt_dt := ben_manage_life_events.g_fonm_cvg_strt_dt ;
234 p_fonm_rt_strt_dt := ben_manage_life_events.g_fonm_rt_strt_dt ;
235
236
237 if g_debug then
238 hr_utility.set_location ('Leaving '||l_proc,10);
239 end if;
240
241 end get_fonm ;
242
243 procedure set_fonm (p_fonm in varchar2 ,
244 p_fonm_cvg_strt_dt in date default null ,
245 p_fonm_rt_strt_dt in date default null
246 ) is
247
248 l_proc varchar2(400) := g_package||'.set_fonm' ;
249 begin
250
251 if g_debug then
252 hr_utility.set_location ('Entering '||l_proc,10);
253 end if;
254
255 ben_manage_life_events.fonm := p_fonm ;
256 if p_fonm = 'N' then
257 ben_manage_life_events.g_fonm_cvg_strt_dt := null ;
258 ben_manage_life_events.g_fonm_rt_strt_dt := null ;
259 else
260 if p_fonm_cvg_strt_dt is not null then
261 ben_manage_life_events.g_fonm_cvg_strt_dt := p_fonm_cvg_strt_dt ;
262 end if ;
263
264 if p_fonm_rt_strt_dt is not null then
265 ben_manage_life_events.g_fonm_rt_strt_dt := p_fonm_rt_strt_dt ;
266 end if ;
267
268 end if ;
269
270
271 if g_debug then
272 hr_utility.set_location ('Leaving '||l_proc,10);
273 end if;
274 end set_fonm ;
275
276
277 end ben_use_cvg_rt_date;
[Home] [Help]