[Home] [Help]
PACKAGE BODY: APPS.BEN_CEP_CACHE
Source
1 package body ben_cep_cache as
2 /* $Header: bencepch.pkb 120.1 2006/01/12 21:01:52 mhoyes noship $ */
3 --
4 procedure clear_down_cache
5 is
6 --
7 l_copcep_odlookup ben_cache.g_cache_lookup_table;
8 l_copcep_odinst g_cobcep_odcache := g_cobcep_odcache();
9 --
10 begin
11 --
12 -- On demand cache structures
13 --
14 g_copcep_odlookup := l_copcep_odlookup;
15 g_copcep_odinst := l_copcep_odinst;
16 g_copcep_odcached := 0;
17 g_copcep_nxelenum := null;
18 --
19 end clear_down_cache;
20 --
21 procedure cobcep_odgetdets
22 (p_effective_date in date
23 ,p_pgm_id in number
24 ,p_pl_id in number
25 ,p_oipl_id in number
26 ,p_plip_id in number
27 ,p_ptip_id in number
28 -- Grade/Step
29 ,p_vrbl_rt_prfl_id in number
30 --
31 ,p_inst_set in out nocopy g_cobcep_odcache
32 )
33 is
34 --
35 l_inst_set g_cobcep_odcache := g_cobcep_odcache();
36 --
37 l_hv pls_integer;
38 l_hash_found boolean;
39 l_insttorrw_num pls_integer;
40 l_torrwnum pls_integer;
41 --
42 l_clash_count pls_integer;
43 --
44 begin
45 --
46 if g_copcep_odcached = 0
47 then
48 --
49 -- Build the cache
50 --
51 clear_down_cache;
52 --
53 g_copcep_odcached := 1;
54 --
55 end if;
56 -- hr_utility.set_location(' Derive hv '||l_proc,10);
57 --
58 -- Get the instance details
59 --
60 -- Grade/Step
61 l_hv := mod(nvl(p_pgm_id,1)+nvl(p_ptip_id,2)+nvl(p_plip_id,3)
62 +nvl(p_pl_id,4)+nvl(p_oipl_id,5)
63 +nvl(p_vrbl_rt_prfl_id,6)
64 ,g_hash_key);
65 --
66 -- Check if hashed value is already allocated
67 --
68 l_hash_found := false;
69 --
70 if g_copcep_odlookup.exists(l_hv) then
71 --
72 if nvl(g_copcep_odlookup(l_hv).id,-1) = nvl(p_pgm_id,-1)
73 and nvl(g_copcep_odlookup(l_hv).fk_id,-1) = nvl(p_ptip_id,-1)
74 and nvl(g_copcep_odlookup(l_hv).fk1_id,-1) = nvl(p_plip_id,-1)
75 and nvl(g_copcep_odlookup(l_hv).fk2_id,-1) = nvl(p_pl_id,-1)
76 and nvl(g_copcep_odlookup(l_hv).fk3_id,-1) = nvl(p_oipl_id,-1)
77 and nvl(g_copcep_odlookup(l_hv).fk4_id,-1) = nvl(p_vrbl_rt_prfl_id,-1)
78 then
79 --
80 null;
81 --
82 else
83 --
84 l_hash_found := false;
85 --
86 -- Loop until un-allocated has value is derived
87 --
88 l_clash_count := 0;
89 --
90 while not l_hash_found loop
91 --
92 l_hv := l_hv+g_hash_jump;
93 /*
94 l_hv := ben_hash_utility.get_next_hash_index(p_hash_index => l_hv);
95 */
96 --
97 if g_copcep_odlookup.exists(l_hv) then
98 --
99 -- Check if the hash index exists, and compare the values
100 --
101 if nvl(g_copcep_odlookup(l_hv).id,-1) = nvl(p_pgm_id,-1)
102 and nvl(g_copcep_odlookup(l_hv).fk_id,-1) = nvl(p_ptip_id,-1)
103 and nvl(g_copcep_odlookup(l_hv).fk1_id,-1) = nvl(p_plip_id,-1)
104 and nvl(g_copcep_odlookup(l_hv).fk2_id,-1) = nvl(p_pl_id,-1)
105 and nvl(g_copcep_odlookup(l_hv).fk3_id,-1) = nvl(p_oipl_id,-1)
106 -- Grade/Step
107 and nvl(g_copcep_odlookup(l_hv).fk4_id,-1) = nvl(p_vrbl_rt_prfl_id,-1)
108 then
109 --
110 l_hash_found := true;
111 exit;
112 --
113 else
114 --
115 l_clash_count := l_clash_count+1;
116 l_hash_found := false;
117 --
118 end if;
119 --
120 -- Check for high clash counts and defrag
121 --
122 if l_clash_count > 50
123 then
124 --
125 l_hv := null;
126 clear_down_cache;
127 exit;
128 --
129 end if;
130 --
131 else
132 --
133 l_hv := null;
134 exit;
135 --
136 end if;
137 --
138 end loop;
139 --
140 end if;
141 --
142 else
143 --
144 l_hv := null;
145 --
146 end if;
147
148 if l_hv is null
149 then
150 --
151 ben_cep_cache1.write_cobcep_odcache
152 (p_effective_date => p_effective_date
153 ,p_pgm_id => p_pgm_id
154 ,p_pl_id => p_pl_id
155 ,p_oipl_id => p_oipl_id
156 ,p_plip_id => p_plip_id
157 ,p_ptip_id => p_ptip_id
158 -- Grade/Step
159 ,p_vrbl_rt_prfl_id => p_vrbl_rt_prfl_id
160 --
161 ,p_hv => l_hv
162 );
163 --
164 end if;
165 -- hr_utility.set_location(' Got hv '||l_proc,10);
166 --
167 if l_hv is not null then
168 --
169 l_torrwnum := 1;
170 --
171 -- hr_utility.set_location(' Get loop '||l_proc,10);
172 for l_insttorrw_num in g_copcep_odlookup(l_hv).starttorele_num ..
173 g_copcep_odlookup(l_hv).endtorele_num
174 loop
175 --
176 l_inst_set.extend(1);
177 l_inst_set(l_torrwnum) := g_copcep_odinst(l_insttorrw_num);
178 l_torrwnum := l_torrwnum+1;
179 --
180 end loop;
181 --
182 -- hr_utility.set_location(' Dn Get loop '||l_proc,10);
183 --
184 end if;
185 --
186 p_inst_set := l_inst_set;
187 --
188 -- hr_utility.set_location(' Leaving '||l_proc,10);
189 exception
190 --
191 when no_data_found then
192 --
193 p_inst_set := l_inst_set;
194 --
195 end cobcep_odgetdets;
196 --
197 end ben_cep_cache;