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