[Home] [Help]
PACKAGE BODY: APPS.BEN_LETRG_CACHE
Source
1 package body ben_letrg_cache as
2 /* $Header: beltrgch.pkb 120.0 2005/05/28 03:39:10 appldev noship $ */
3 --
4 /*
5 +==============================================================================+
6 | Copyright (c) 1997 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 --
11 History
12 Version Date Author Comments
13 --------- --------- ---------- --------------------------------------------
14 115.0 14-Sep-00 mhoyes Created.
15 115.1 01-May-01 mhoyes Added PRV and PEN caches.
16 115.2 22-May-01 mhoyes Further tuned triggers.
17 115.3 21-Jan-03 hmani Modified for Triggering LE for Multiple table
18 Enhancement.
19 115.4 21-Feb-05 tjesumic typ_cd added and checklist validated .
20 -----------------------------------------------------------------------------
21 */
22 --
23 -- Globals.
24 --
25 g_package varchar2(50) := 'ben_letrg_cache.';
26 --
27 g_hash_key number := ben_hash_utility.get_hash_key;
28 g_hash_jump number := ben_hash_utility.get_hash_jump;
29 --
30 -- 0 - Always refresh
31 -- 1 - Initialise cache
32 -- 2 - Cache hit
33 --
34 g_egdlertrg_instance g_egdlertrg_inst_tbl;
35 g_egdlertrg_cached pls_integer := 0;
36 --
37 g_prvlertrg_instance g_egdlertrg_inst_tbl;
38 g_prvlertrg_cached pls_integer := 0;
39 --
40 g_penlertrg_instance g_egdlertrg_inst_tbl;
41 g_penlertrg_cached pls_integer := 0;
42 --
43
44
45 CURSOR c_chkpslexists
46 (c_business_group_id NUMBER
47 ,c_effective_date DATE
48 ,c_source_table varchar2
49 )
50 IS
51 select 1
52 from ben_per_info_chg_cs_ler_f psl
53 where psl.business_group_id = c_business_group_id
54 and c_effective_date
55 between psl.effective_start_date and psl.effective_end_date
56 and psl.source_table = c_source_table;
57 --
58 CURSOR c_chkrpcexists
59 (c_business_group_id NUMBER
60 ,c_effective_date DATE
61 ,c_source_table varchar2
62 )
63 IS
64 select 1
65 from ben_rltd_per_chg_cs_ler_f rpc
66 where rpc.business_group_id = c_business_group_id
67 and c_effective_date
68 between rpc.effective_start_date and rpc.effective_end_date
69 and rpc.source_table = c_source_table;
70 --
71 CURSOR c_lerdets
72 (c_business_group_id NUMBER
73 ,c_effective_date DATE
74 ,c_source_table varchar2
75 ,c_status varchar2
76 )
77 IS
78 select ler.ler_id
79 , ler.typ_cd
80 , ler.ocrd_dt_det_cd
81 from ben_ler_f ler
82 where ler.business_group_id = c_business_group_id
83 and c_effective_date between ler.effective_start_date
84 and ler.effective_end_date
85 and ( c_status = 'I' or ler.typ_cd in ('COMP','GSP','ABS','CHECKLIST') )
86 and ((exists
87 (select 1
88 from ben_per_info_chg_cs_ler_f psl
89 , ben_ler_per_info_cs_ler_f lpl
90 where source_table = c_source_table
91 and psl.per_info_chg_cs_ler_id = lpl.per_info_chg_cs_ler_id
92 and lpl.business_group_id = psl.business_group_id
93 and lpl.business_group_id = ler.business_group_id
94 and c_effective_date between psl.effective_start_date
95 and psl.effective_end_date
96 and lpl.ler_id = ler.ler_id)
97 )
98 OR (exists
99 (select 1
100 from ben_rltd_per_chg_cs_ler_f rpc
101 , ben_ler_rltd_per_cs_ler_f lrp
102 where source_table = c_source_table
103 and lrp.business_group_id = rpc.business_group_id
104 and lrp.business_group_id = ler.business_group_id
105 and c_effective_date between rpc.effective_start_date
106 and rpc.effective_end_date
107 and rpc.rltd_per_chg_cs_ler_id = lrp.rltd_per_chg_cs_ler_id
108 and lrp.ler_id = ler.ler_id)
109 ))
110 order by ler.ler_id;
111 --
112 procedure write_egdlertrg_cache
113 (p_business_group_id in number
114 ,p_effective_date in date
115 )
116 is
117 --
118 TYPE g_number_table_type IS TABLE OF NUMBER
119 INDEX BY BINARY_INTEGER;
120 --
121 TYPE g_v230_table_type IS TABLE OF varchar2(30)
122 INDEX BY BINARY_INTEGER;
123 --
124 l_ler_id_tab g_number_table_type;
125 l_ler_typ_cd_tab g_v230_table_type;
126 l_ocrd_dt_det_cd_tab g_v230_table_type;
127 --
128 l_hv pls_integer;
129 l_not_hash_found boolean;
130 --
131 l_ele_num pls_integer;
132
133 --
134 l_bool BOOLEAN;
135 l_status VARCHAR2(1);
136 l_industry VARCHAR2(1);
137 --
138 --
139 CURSOR c_instance
140 (c_business_group_id NUMBER
141 ,c_effective_date DATE
142 )
143 IS
144 SELECT ler.ler_id,
145 ler.typ_cd,
146 ler.ocrd_dt_det_cd
147 FROM ben_ler_f ler
148 WHERE ler.business_group_id = c_business_group_id
149 AND c_effective_date BETWEEN ler.effective_start_date
150 AND ler.effective_end_date
151 AND ( l_status = 'I' or ler.typ_cd in ('COMP','GSP','ABS','CHECKLIST') )
152 AND (
153 (
154 EXISTS
155 (SELECT 1
156 FROM ben_per_info_chg_cs_ler_f psl,
157 ben_ler_per_info_cs_ler_f lpl
158 WHERE psl.source_table = 'BEN_ELIG_DPNT'
159 AND psl.per_info_chg_cs_ler_id =
160 lpl.per_info_chg_cs_ler_id
161 AND c_effective_date BETWEEN psl.effective_start_date
162 AND psl.effective_end_date
163 AND lpl.ler_id = ler.ler_id
164 AND c_effective_date BETWEEN lpl.effective_start_date
165 AND lpl.effective_end_date)
166 )
167 OR (
168 EXISTS
169 (SELECT 1
170 FROM ben_ler_rltd_per_cs_ler_f lrp,
171 ben_rltd_per_chg_cs_ler_f rpc
172 WHERE rpc.source_table = 'BEN_ELIG_DPNT'
173 AND c_effective_date BETWEEN rpc.effective_start_date
174 AND rpc.effective_end_date
175 AND rpc.rltd_per_chg_cs_ler_id =
176 lrp.rltd_per_chg_cs_ler_id
177 AND lrp.ler_id = ler.ler_id
178 AND c_effective_date BETWEEN lrp.effective_start_date
179 AND lrp.effective_end_date)
180 ));
181 --
182 begin
183 --
184 --
185 -- Check if definitions exist for the source table and BGP ID combination
186 --
187 l_ele_num := 0;
188 l_bool :=fnd_installation.get(appl_id => 805
189 ,dep_appl_id =>805
190 ,status => l_status
191 ,industry => l_industry);
192
193 for row in c_instance
194 (c_business_group_id => p_business_group_id
195 ,c_effective_date => p_effective_date
196 )
197 loop
198 g_egdlertrg_instance(l_ele_num).ler_id := row.ler_id;
199 g_egdlertrg_instance(l_ele_num).typ_cd := row.typ_cd;
200 g_egdlertrg_instance(l_ele_num).ocrd_dt_det_cd := row.ocrd_dt_det_cd;
201 l_ele_num := l_ele_num+1;
202 end loop;
203 --
204 end write_egdlertrg_cache;
205 --
206 procedure get_egdlertrg_dets
207 (p_business_group_id in number
208 ,p_effective_date in date
209 ,p_inst_set in out NOCOPY g_egdlertrg_inst_tbl
210 )
211 is
212 --
213 l_proc varchar2(72) := 'get_egdlertrg_dets';
214 --
215 begin
216 --
217 -- check comp object type
218 --
219 if g_egdlertrg_cached < 2
220 then
221 --
222 -- Write the cache
223 --
224 write_egdlertrg_cache
225 (p_business_group_id => p_business_group_id
226 ,p_effective_date => p_effective_date
227 );
228 --
229 if g_egdlertrg_cached = 1
230 then
231 --
232 g_egdlertrg_cached := 2;
233 --
234 end if;
235 --
236 end if;
237 --
238 p_inst_set := g_egdlertrg_instance;
239 --
240 end get_egdlertrg_dets;
241 --
242 procedure write_prvlertrg_cache
243 (p_business_group_id in number
244 ,p_effective_date in date
245 )
246 is
247 --
248 l_ler_id_tab benutils.g_number_table := benutils.g_number_table();
249 l_ocrd_dt_det_cd_tab benutils.g_v2_30_table := benutils.g_v2_30_table();
250 l_ler_typ_cd_tab benutils.g_v2_30_table := benutils.g_v2_30_table();
251 --
252 l_ele_num pls_integer;
253 --
254 l_dummy_number pls_integer;
255 l_found boolean;
256 l_source_table varchar2(100);
257 --
258 l_bool BOOLEAN;
259 l_status VARCHAR2(1);
260 l_industry VARCHAR2(1);
261 begin
262 --
263 --
264 l_found := FALSE;
265 l_source_table := 'BEN_PRTT_RT_VAL';
266 --
267 open c_chkpslexists
268 (c_business_group_id => p_business_group_id
269 ,c_effective_date => p_effective_date
270 ,c_source_table => l_source_table
271 );
272 fetch c_chkpslexists into l_dummy_number;
273 if c_chkpslexists%notfound then
274 --
275 l_found := FALSE;
276 --
277 open c_chkrpcexists
278 (c_business_group_id => p_business_group_id
279 ,c_effective_date => p_effective_date
280 ,c_source_table => l_source_table
281 );
282 fetch c_chkrpcexists into l_dummy_number;
283 if c_chkrpcexists%notfound then
284 --
285 l_found := FALSE;
286 --
287 else
288 --
289 l_found := TRUE;
290 --
291 end if;
292 close c_chkrpcexists;
293 --
294 else
295 --
296 l_found := TRUE;
297 --
298 end if;
299 close c_chkpslexists;
300 --
301 if l_found then
302 --
303 l_ele_num := 0;
304 l_bool :=fnd_installation.get(appl_id => 805
305 ,dep_appl_id =>805
306 ,status => l_status
307 ,industry => l_industry);
308
309 for row in c_lerdets
310 (c_business_group_id => p_business_group_id
311 ,c_effective_date => p_effective_date
312 ,c_source_table => l_source_table
313 ,c_status => l_status
314 )
315 loop
316 g_prvlertrg_instance(l_ele_num).ler_id := row.ler_id;
317 g_prvlertrg_instance(l_ele_num).typ_cd := row.typ_cd;
318 g_prvlertrg_instance(l_ele_num).ocrd_dt_det_cd := row.ocrd_dt_det_cd;
319 l_ele_num := l_ele_num+1;
320 end loop;
321 --
322 else
323 --
324 g_prvlertrg_instance.delete;
325 --
326 end if;
327 --
328 end write_prvlertrg_cache;
329 --
330 procedure get_prvlertrg_dets
331 (p_business_group_id in number
332 ,p_effective_date in date
333 ,p_inst_set in out NOCOPY g_egdlertrg_inst_tbl
334 )
335 is
336 --
337 l_proc varchar2(72) := 'get_prvlertrg_dets';
338 --
339 begin
340 --
341 -- check comp object type
342 --
343 if g_prvlertrg_cached < 2
344 then
345 --
346 -- Write the cache
347 --
348 write_prvlertrg_cache
349 (p_business_group_id => p_business_group_id
350 ,p_effective_date => p_effective_date
351 );
352 --
353 if g_prvlertrg_cached = 1
354 then
355 --
356 g_prvlertrg_cached := 2;
357 --
358 end if;
359 --
360 end if;
361 --
362 p_inst_set := g_prvlertrg_instance;
363 --
364 end get_prvlertrg_dets;
365 --
366 procedure write_penlertrg_cache
367 (p_business_group_id in number
368 ,p_effective_date in date
369 )
370 is
371 --
372 l_ler_id_tab benutils.g_number_table := benutils.g_number_table();
373 l_ocrd_dt_det_cd_tab benutils.g_v2_30_table := benutils.g_v2_30_table();
374 l_ler_typ_cd_tab benutils.g_v2_30_table := benutils.g_v2_30_table();
375 --
376 l_ele_num pls_integer;
377 --
378 l_dummy_number pls_integer;
379 l_found boolean;
380 l_source_table varchar2(100);
381 --
382 l_bool BOOLEAN;
383 l_status VARCHAR2(1);
384 l_industry VARCHAR2(1);
385 --
386
387 begin
388 --
389 --
390 l_found := FALSE;
391 l_source_table := 'BEN_PRTT_ENRT_RSLT_F';
392 --
393 open c_chkpslexists
394 (c_business_group_id => p_business_group_id
395 ,c_effective_date => p_effective_date
396 ,c_source_table => l_source_table
397 );
398 fetch c_chkpslexists into l_dummy_number;
399 if c_chkpslexists%notfound then
400 --
401 l_found := FALSE;
402 --
403 open c_chkrpcexists
404 (c_business_group_id => p_business_group_id
405 ,c_effective_date => p_effective_date
406 ,c_source_table => l_source_table
407 );
408 fetch c_chkrpcexists into l_dummy_number;
409 if c_chkrpcexists%notfound then
410 --
411 l_found := FALSE;
412 --
413 else
414 --
415 l_found := TRUE;
416 --
417 end if;
418 close c_chkrpcexists;
419 --
420 else
421 --
422 l_found := TRUE;
423 --
424 end if;
425 close c_chkpslexists;
426 --
427 if l_found then
428 --
429 l_ele_num := 0;
430 l_bool :=fnd_installation.get(appl_id => 805
431 ,dep_appl_id =>805
432 ,status => l_status
433 ,industry => l_industry);
434
435 for row in c_lerdets
436 (c_business_group_id => p_business_group_id
437 ,c_effective_date => p_effective_date
438 ,c_source_table => l_source_table
439 ,c_status => l_status
440 )
441 loop
442 g_penlertrg_instance(l_ele_num).ler_id := row.ler_id;
443 g_penlertrg_instance(l_ele_num).typ_cd := row.typ_cd;
444 g_penlertrg_instance(l_ele_num).ocrd_dt_det_cd := row.ocrd_dt_det_cd;
445 l_ele_num := l_ele_num+1;
446 end loop;
447 --
448 else
449 --
450 g_penlertrg_instance.delete;
451 --
452 end if;
453 --
454 end write_penlertrg_cache;
455 --
456 procedure get_penlertrg_dets
457 (p_business_group_id in number
458 ,p_effective_date in date
459 ,p_inst_set in out NOCOPY g_egdlertrg_inst_tbl
460 )
461 is
462 --
463 l_proc varchar2(72) := 'get_penlertrg_dets';
464 --
465 begin
466 --
467 -- check comp object type
468 --
469 if g_penlertrg_cached < 2
470 then
471 --
472 -- Write the cache
473 --
474 write_penlertrg_cache
475 (p_business_group_id => p_business_group_id
476 ,p_effective_date => p_effective_date
477 );
478 --
479 if g_penlertrg_cached = 1
480 then
481 --
482 g_penlertrg_cached := 2;
483 --
484 end if;
485 --
486 end if;
487 --
488 p_inst_set := g_penlertrg_instance;
489 --
490 end get_penlertrg_dets;
491 --
492 ------------------------------------------------------------------------
493 -- DELETE ALL CACHED DATA
494 ------------------------------------------------------------------------
495 procedure clear_down_cache
496 is
497
498 l_ler_init g_egdlertrg_inst_tbl;
499
500 begin
501 --
502 g_egdlertrg_instance.delete;
503 g_egdlertrg_cached := 1;
504 --
505 g_prvlertrg_instance.delete;
506 g_prvlertrg_cached := 1;
507 --
508 g_penlertrg_instance.delete;
509 g_penlertrg_cached := 1;
510 --
511 end clear_down_cache;
512 --
513 procedure set_no_cache
514 is
515
516 l_ler_init g_egdlertrg_inst_tbl;
517
518 begin
519 --
520 g_egdlertrg_instance.delete;
521 g_egdlertrg_cached := 0;
522 --
523 g_prvlertrg_instance.delete;
524 g_prvlertrg_cached := 0;
525 --
526 g_penlertrg_instance.delete;
527 g_penlertrg_cached := 0;
528 --
529 end set_no_cache;
530 --
531 end ben_letrg_cache;