[Home] [Help]
PACKAGE BODY: APPS.BEN_SAZ_CACHE
Source
1 package body ben_saz_cache as
2 /* $Header: bensazch.pkb 120.0.12010000.3 2009/10/16 03:49:24 krupani ship $ */
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 17-Sep-00 mhoyes Created.
15 115.2 11-dec-2002 hmani NoCopy changes
16 115.3 29-Jan-2009 krupani Bug 7718194 - zip code range (g_sazrzr_zippzrid_va) should be
17 re-populated when service area (g_sazrzr_svcpzrid_va) is re-populated
18 115.4 16-Oct-2009 krupani Bug 9021884 - modified the fix of 7718194 in order not to cause the
19 performance issue. Reverted the fix of 7718194
20 -----------------------------------------------------------------------------
21 */
22 --
23 -- Globals.
24 --
25 g_package varchar2(50) := 'ben_saz_cache.';
26 --
27 type g_current_row is record
28 (zip_code varchar2(30)
29 ,svc_area_id number
30 ,effective_date date
31 );
32 --
33 g_sazrzr_current g_current_row;
34 g_sazrzr_svcpzrid_va benutils.g_number_table := benutils.g_number_table();
35 g_sazrzr_zippzrid_va benutils.g_number_table := benutils.g_number_table();
36 --
37 procedure SAZRZR_Exists
38 (p_svc_area_id in number
39 ,p_zip_code in varchar2
40 ,p_eff_date in date
41 --
42 ,p_exists out nocopy boolean
43 )
44 is
45 --
46 l_proc varchar2(72) := 'SAZRZR_Exists';
47 l_effective_date date; -- Bug 9021884
48 --
49 type l_hash_table_row is record
50 (id number
51 );
52 --
53 type l_hash_table_tbl is table of l_hash_table_row index by binary_integer;
54 --
55 l_hash_table_tor l_hash_table_tbl;
56 --
57 l_hv pls_integer;
58 --
59
60 cursor c_saz
61 (c_svc_area_id number
62 ,c_eff_date date
63 )
64 is
65 select saz.pstl_zip_rng_id
66 from ben_svc_area_pstl_zip_rng_f saz
67 where saz.SVC_AREA_ID = c_svc_area_id
68 and c_eff_date
69 between saz.effective_start_date and saz.effective_end_date;
70 --
71 cursor c_rzr
72 (c_zip_code VARCHAR2
73 ,c_eff_date date
74 )
75 is
76 select rzr.pstl_zip_rng_id
77 from ben_pstl_zip_rng_f rzr
78 where
79 length(c_zip_code) >= length(rzr.from_value)
80 and
81 (substr(c_zip_code,1,length(rzr.from_value))
82 between rzr.from_value and rzr.to_value
83 )
84 and c_eff_date
85 between rzr.effective_start_date and rzr.effective_end_date;
86 --
87 begin
88 --
89 -- When the zip code is null then no ranges match
90 --
91 -- Bug 9021884: Storing the date in a temp varialbe
92 l_effective_date := g_sazrzr_current.effective_date;
93
94 if p_zip_code is null
95 then
96 --
97 p_exists := false;
98 return;
99 --
100 end if;
101 --
102 -- Check if cached postal code ranges are for currently
103 -- cached service area. Or the effective date has changed.
104 --
105 if nvl(g_sazrzr_current.svc_area_id,-999999) <> p_svc_area_id
106 or nvl(g_sazrzr_current.effective_date,hr_api.g_sot) <> p_eff_date
107 then
108 --
109 g_sazrzr_svcpzrid_va.delete;
110 --
111 open c_saz
112 (c_svc_area_id => p_svc_area_id
113 ,c_eff_date => p_eff_date
114 );
115 fetch c_saz BULK COLLECT INTO g_sazrzr_svcpzrid_va;
116 close c_saz;
117 --
118 g_sazrzr_current.svc_area_id := p_svc_area_id;
119 g_sazrzr_current.effective_date := p_eff_date;
120 --
121 end if;
122 --
123 -- Check if cached service area postal code ranges
124 -- exist
125 --
126 if g_sazrzr_svcpzrid_va.count = 0
127 then
128 --
129 p_exists := false;
130 return;
131 --
132 --
133 -- Check if cached postal code ranges are for the current
134 -- zip code
135 --
136 /* Bug 9021884: replaced g_sazrzr_current.effective_date by l_effective_date below*/
137
138 elsif nvl(g_sazrzr_current.zip_code,'00000') <> p_zip_code
139 or nvl(l_effective_date,hr_api.g_sot) <> p_eff_date
140 then
141 --
142 g_sazrzr_zippzrid_va.delete;
143 --
144 open c_rzr
145 (c_zip_code => p_zip_code
146 ,c_eff_date => p_eff_date
147 );
148 fetch c_rzr BULK COLLECT INTO g_sazrzr_zippzrid_va;
149 close c_rzr;
150 --
151 g_sazrzr_current.zip_code := p_zip_code;
152 g_sazrzr_current.effective_date := p_eff_date;
153 --
154 end if;
155 --
156 -- Check if cached zip code postal code ranges exist
157 --
158 if g_sazrzr_zippzrid_va.count = 0
159 then
160 --
161 p_exists := false;
162 return;
163 --
164 end if;
165 --
166 -- Populate zip values in hash table
167 --
168 for i in g_sazrzr_zippzrid_va.first..g_sazrzr_zippzrid_va.last
169 loop
170 --
171 l_hv := mod(g_sazrzr_zippzrid_va(i),ben_hash_utility.get_hash_key);
172 l_hash_table_tor(l_hv).id := g_sazrzr_zippzrid_va(i);
173 --
174 end loop;
175 --
176 -- Check for service area clashes
177 --
178 for i in g_sazrzr_svcpzrid_va.first..g_sazrzr_svcpzrid_va.last
179 loop
180 --
181 l_hv := mod(g_sazrzr_svcpzrid_va(i),ben_hash_utility.get_hash_key);
182 --
183 if l_hash_table_tor.exists(l_hv)
184 then
185 --
186 p_exists := true;
187 return;
188 --
189 end if;
190 --
191 end loop;
192 --
193 p_exists := false;
194 --
195 end SAZRZR_Exists;
196 --
197 procedure clear_down_cache
198 is
199
200 l_sazrzr_reset g_current_row;
201
202 begin
203 --
204 g_sazrzr_svcpzrid_va.delete;
205 g_sazrzr_zippzrid_va.delete;
206 g_sazrzr_current := l_sazrzr_reset;
207 --
208 end clear_down_cache;
209 --
210 end ben_saz_cache;