[Home] [Help]
PACKAGE BODY: APPS.BEN_AGF_CACHE
Source
1 package body ben_agf_cache as
2 /* $Header: benagfch.pkb 115.4 2002/12/23 12:36:02 nhunur ship $ */
3 -- ---------------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 History
12 Date Who Version What?
13 -- -- --- ------- -----
14 30 Oct 98 mhoyes 115.0 created
15 09 Mar 99 G Perry 115.2 IS to AS.
16 04 Mar 99 mhoyes 115.3 Implemented Hashing.
17 */
18 -- ------------------------------------------------------------------------------
19 --
20 g_package varchar2(50) := 'ben_agf_cache.';
21 --
22 -- Declare globals
23 --
24 -- age factor
25 --
26 g_cache_agf_lookup ben_cache.g_cache_lookup_table;
27 g_cache_agf_inst ben_agf_cache.g_cache_agf_instor;
28 --
29 procedure agf_writecache
30 (p_effective_date in date
31 --
32 ,p_refresh_cache in boolean default FALSE
33 )
34 is
35 --
36 l_proc varchar2(72) := g_package||'agf_writecache';
37 --
38 l_torrwnum binary_integer;
39 --
40 l_prev_hv number;
41 l_hv number;
42 l_not_hash_found boolean;
43 --
44 cursor c_agf_look is
45 select agf.age_fctr_id, agf.business_group_id
46 from ben_age_fctr agf
47 where exists
48 (select null
49 from ben_age_fctr agf
50 where
51 agf.age_fctr_id = agf.age_fctr_id)
52 order by agf.age_fctr_id;
53 --
54 cursor c_agf_inst is
55 select agf.age_fctr_id,
56 agf.mx_age_num,
57 agf.mn_age_num,
58 agf.no_mn_age_flag,
59 agf.no_mx_age_flag
60 from ben_age_fctr agf
61 order by agf.age_fctr_id
62 , agf.age_fctr_id;
63 --
64 begin
65 --
66 for objlook in c_agf_look loop
67 --
68 l_hv := ben_hash_utility.get_hashed_index(objlook.age_fctr_id);
69 --
70 -- Check if hashed value is already allocated
71 --
72 if g_cache_agf_lookup.exists(l_hv) then
73 --
74 l_not_hash_found := false;
75 --
76 -- Loop until un-allocated has value is derived
77 --
78 while not l_not_hash_found loop
79 --
80 l_hv := ben_hash_utility.get_next_hash_index(p_hash_index => l_hv);
81 --
82 -- Check if the hash index exists, if not we can use it
83 --
84 if not g_cache_agf_lookup.exists(l_hv) then
85 --
86 -- Lets store the hash value in the index
87 --
88 l_not_hash_found := true;
89 exit;
90 --
91 else
92 --
93 l_not_hash_found := false;
94 --
95 end if;
96 --
97 end loop;
98 --
99 end if;
100 --
101 g_cache_agf_lookup(l_hv).id := objlook.age_fctr_id;
102 g_cache_agf_lookup(l_hv).fk_id := objlook.business_group_id;
103 --
104 end loop;
105 --
106 l_torrwnum := 0;
107 l_prev_hv := -1;
108 --
109 for objinst in c_agf_inst loop
110 --
111 l_hv := ben_hash_utility.get_hashed_index(objinst.age_fctr_id);
112 --
113 -- Check if hashed value is already allocated
114 --
115 if g_cache_agf_inst.exists(l_hv) then
116 --
117 l_not_hash_found := false;
118 --
119 -- Loop until un-allocated has value is derived
120 --
121 while not l_not_hash_found loop
122 --
123 l_hv := ben_hash_utility.get_next_hash_index(p_hash_index => l_hv);
124 --
125 -- Check if the hash index exists, if not we can use it
126 --
127 if not g_cache_agf_inst.exists(l_hv) then
128 --
129 -- Lets store the hash value in the index
130 --
131 l_not_hash_found := true;
132 exit;
133 --
134 else
135 --
136 l_not_hash_found := false;
137 --
138 end if;
139 --
140 end loop;
141 --
142 end if;
143 --
144 --
145 -- Check for first row
146 --
147 if l_prev_hv = -1 then
148 --
149 g_cache_agf_lookup(l_hv).starttorele_num := l_torrwnum;
150 --
151 elsif l_hv <> l_prev_hv then
152 --
153 g_cache_agf_lookup(l_prev_hv).endtorele_num := l_torrwnum-1;
154 g_cache_agf_lookup(l_hv).starttorele_num := l_torrwnum;
155 --
156 end if;
157 --
158 -- Populate the cache instance details
159 --
160 g_cache_agf_inst(l_torrwnum).age_fctr_id := objinst.age_fctr_id;
161 g_cache_agf_inst(l_torrwnum).mx_age_num := objinst.mx_age_num;
162 g_cache_agf_inst(l_torrwnum).mn_age_num := objinst.mn_age_num;
163 g_cache_agf_inst(l_torrwnum).no_mn_age_flag := objinst.no_mn_age_flag;
164 g_cache_agf_inst(l_torrwnum).no_mn_age_flag := objinst.no_mn_age_flag;
165 --
166 l_torrwnum := l_torrwnum+1;
167 l_prev_hv := l_hv;
168 --
169 end loop;
170 --
171 g_cache_agf_lookup(l_prev_hv).endtorele_num := l_torrwnum-1;
172 --
173 end agf_writecache;
174 --
175 procedure agf_getcacdets
176 (p_effective_date in date
177 ,p_business_group_id in number
178 ,p_age_fctr_id in number
179 --
180 ,p_refresh_cache in boolean default FALSE
181 --
182 ,p_inst_set out nocopy ben_agf_cache.g_cache_agf_instor
183 ,p_inst_count out nocopy number
184 )
185 is
186 --
187 l_proc varchar2(72) := g_package||'agf_getcacdets';
188 --
189 l_torrwnum binary_integer;
190 l_insttorrw_num binary_integer;
191 --
192 l_index binary_integer;
193 l_hv binary_integer;
194 l_not_hash_found boolean;
195 --
196 begin
197 --
198 -- Flush the cache
199 --
200 if p_refresh_cache then
201 --
202 g_cache_agf_lookup.delete;
203 g_cache_agf_inst.delete;
204 --
205 end if;
206 --
207 -- Populate the global cache
208 --
209 if g_cache_agf_lookup.count = 0
210 then
211 --
212 -- Build the cache
213 --
214 ben_agf_cache.agf_writecache
215 (p_effective_date => p_effective_date
216 --
217 ,p_refresh_cache => p_refresh_cache
218 );
219 --
220 end if;
221 --
222 l_hv := ben_hash_utility.get_hashed_index(p_id => p_age_fctr_id);
223 --
224 -- Check if hashed value is already allocated
225 --
226 if g_cache_agf_lookup.exists(l_hv) then
227 --
228 -- If it does exist make sure its the right one
229 --
230 if g_cache_agf_lookup(l_hv).id <> p_age_fctr_id then
231 --
232 l_not_hash_found := false;
233 --
234 -- Loop until un-allocated has value is derived
235 --
236 while not l_not_hash_found loop
237 --
238 l_hv := ben_hash_utility.get_next_hash_index(p_hash_index => l_hv);
239 --
240 -- Check if the hash index exists, if not we can use it
241 --
242 if not g_cache_agf_lookup.exists(l_hv) then
243 --
244 -- Lets store the hash value in the index
245 --
246 l_not_hash_found := true;
247 exit;
248 --
249 else
250 --
251 l_not_hash_found := false;
252 --
253 end if;
254 --
255 end loop;
256 --
257 end if;
258 --
259 end if;
260 --
261 --
262 -- Get the instance details
263 --
264 l_torrwnum := 0;
265 --
266 for l_insttorrw_num in g_cache_agf_lookup(l_hv).starttorele_num ..
267 g_cache_agf_lookup(l_hv).endtorele_num loop
268 --
269 p_inst_set(l_torrwnum) := g_cache_agf_inst(l_insttorrw_num);
270 l_torrwnum := l_torrwnum+1;
271 --
272 end loop;
273 --
274 p_inst_count := l_torrwnum;
275 --
276 exception
277 when no_data_found then
278 --
279 p_inst_count := 0;
280 --
281 end agf_getcacdets;
282 --
283 end ben_agf_cache;