1 PACKAGE WSH_REGIONS_SEARCH_PKG AUTHID CURRENT_USER AS
2 /* $Header: WSHRESES.pls 120.3.12010000.3 2009/08/04 09:38:33 gbhargav ship $ */
3
4 --
5 -- Package
6 -- WSH_REGIONS_SEARCH_PKG
7 --
8 -- Purpose
9 --
10
11 --
12 -- PACKAGE TYPES
13 --
14
15 --
16 -- PUBLIC VARIABLES
17 --
18 g_mode VARCHAR2(30) := 'CREATE';
19
20 TYPE region_rec IS RECORD (
21 region_id NUMBER,
22 region_type NUMBER,
23 country VARCHAR2(80),
24 country_region VARCHAR2(60),
25 state VARCHAR2(120), --bug 8687139 length increased to 120 from 60
26 city VARCHAR2(60),
27 postal_code_from VARCHAR2(60),
28 postal_code_to VARCHAR2(60),
29 zone VARCHAR2(60),
30 zone_level NUMBER,
31 country_code VARCHAR2(10),
32 country_region_code VARCHAR2(10),
33 state_code VARCHAR2(10),
34 city_code VARCHAR2(10),
35 is_input_type VARCHAR2(1)
36 );
37
38 TYPE region_table IS TABLE OF region_rec INDEX BY BINARY_INTEGER;
39
40 TYPE region_deconsol_rec_type IS RECORD (
41 Region_id NUMBER,
42 Region_type NUMBER,
43 Deconsol_location NUMBER);
44
45 TYPE region_deconsol_tab_type IS TABLE OF region_deconsol_rec_type INDEX BY BINARY_INTEGER;
46
47 TYPE region_zone_deconsol_tab_type IS TABLE OF region_deconsol_tab_type INDEX BY BINARY_INTEGER;
48
49 TYPE loc_region_deconsol_tab_type IS TABLE OF region_deconsol_tab_type INDEX BY BINARY_INTEGER;
50
51 --
52 -- PUBLIC FUNCTIONS/PROCEDURES
53 --
54
55 --
56 -- Procedure: Get_Region_Info
57 --
58 -- Purpose: Obtains information of the region by matching all non-null
59 -- parameters that are passed in. If none, returns null in x_region_info
60 -- Has one more parameter P_SEARCH_FLAG for Postal Code Regions
61 -- 'Y' search for the exactly same region
62 -- 'N' search for the overlapping regions as well
63
67 p_state IN VARCHAR2,
64 PROCEDURE Get_Region_Info (
65 p_country IN VARCHAR2,
66 p_country_region IN VARCHAR2,
68 p_city IN VARCHAR2,
69 p_postal_code_from IN VARCHAR2,
70 p_postal_code_to IN VARCHAR2,
71 p_zone IN VARCHAR2,
72 p_lang_code IN VARCHAR2,
73 p_country_code IN VARCHAR2,
74 p_country_region_code IN VARCHAR2,
75 p_state_code IN VARCHAR2,
76 p_city_code IN VARCHAR2,
77 p_region_type IN NUMBER,
78 p_interface_flag IN VARCHAR2,
79 p_search_flag IN VARCHAR2,
80 x_region_info OUT NOCOPY region_rec);
81
82 --
83 -- Procedure: Get_Region_Info
84 --
85 -- Purpose: Obtains region_id only of the region by matching all non-null
86 -- parameters that are passed in. If none, returns -1 in x_region_id
87 -- Has one more parameter P_SEARCH_FLAG for Postal Code Regions
88 -- 'Y' search for the exactly same region
89 -- 'N' search for the overlapping regions as well
90 -- P_RECURSIVELY_FLAG will remove postal code from criteria and search
91 -- again
92
93 PROCEDURE Get_Region_Info (
94 p_country IN VARCHAR2,
95 p_country_region IN VARCHAR2,
96 p_state IN VARCHAR2,
97 p_city IN VARCHAR2,
98 p_postal_code_from IN VARCHAR2,
99 p_postal_code_to IN VARCHAR2,
100 p_zone IN VARCHAR2,
101 p_lang_code IN VARCHAR2,
102 p_country_code IN VARCHAR2,
103 p_country_region_code IN VARCHAR2,
104 p_state_code IN VARCHAR2,
105 p_city_code IN VARCHAR2,
106 p_region_type IN NUMBER,
107 p_interface_flag IN VARCHAR2,
108 p_search_flag IN VARCHAR2,
109 p_recursively_flag IN VARCHAR2,
110 x_region_id OUT NOCOPY NUMBER);
111
112
113 --
114 -- Procedure: Get_Region_Info
115 --
116 -- Purpose: Obtains information of the region by matching all non-null
117 -- parameters that are passed in. If none, returns null in x_region_info
118 -- Call another Get_Region_Info with default p_search_flag='N'
119
120 PROCEDURE Get_Region_Info (
121 p_country IN VARCHAR2,
122 p_country_region IN VARCHAR2,
123 p_state IN VARCHAR2,
124 p_city IN VARCHAR2,
125 p_postal_code_from IN VARCHAR2,
126 p_postal_code_to IN VARCHAR2,
127 p_zone IN VARCHAR2,
128 p_lang_code IN VARCHAR2,
129 p_country_code IN VARCHAR2,
130 p_country_region_code IN VARCHAR2,
131 p_state_code IN VARCHAR2,
132 p_city_code IN VARCHAR2,
133 p_region_type IN NUMBER,
134 p_interface_flag IN VARCHAR2,
135 x_region_info OUT NOCOPY region_rec);
136
137
138 PROCEDURE Get_Region_Info (
139 p_country IN VARCHAR2,
140 p_country_region IN VARCHAR2,
141 p_state IN VARCHAR2,
142 p_city IN VARCHAR2,
143 p_postal_code_from IN VARCHAR2,
144 p_postal_code_to IN VARCHAR2,
145 p_zone IN VARCHAR2,
146 p_lang_code IN VARCHAR2,
147 p_country_code IN VARCHAR2,
148 p_country_region_code IN VARCHAR2,
149 p_state_code IN VARCHAR2,
150 p_city_code IN VARCHAR2,
151 p_region_type IN NUMBER,
152 p_interface_flag IN VARCHAR2,
153 p_search_flag IN VARCHAR2 DEFAULT 'N',
154 x_regions OUT NOCOPY region_table);
155 --
156 -- Procedure: Get_Region_Id_Codes_Only
157 --
158 -- Purpose: Obtains information for the region by matching all non-null
159 -- parameters that are passed in, against the non-tl table.
160 -- If no region found, returns null in x_region_info
161 --
162
163 PROCEDURE Get_Region_Id_Codes_Only (
164 p_country_code IN VARCHAR2,
165 p_country_region_code IN VARCHAR2,
166 p_state_code IN VARCHAR2,
167 p_city_code IN VARCHAR2,
168 p_postal_code_from IN VARCHAR2,
169 p_postal_code_to IN VARCHAR2,
170 p_region_type IN NUMBER,
171 p_interface_flag IN VARCHAR2,
172 p_lang_code IN VARCHAR2,
173 x_region_id_non_tl OUT NOCOPY NUMBER,
174 x_region_id_with_tl OUT NOCOPY NUMBER);
175
176 --
177 -- Function: Match_Location_Region
178 --
179 -- Purpose: Returns region id for region found when matching location
180 -- to region
181 --
182
183 FUNCTION Match_Location_Region (
184 p_country IN VARCHAR2,
185 p_country_code IN VARCHAR2,
186 p_state IN VARCHAR2,
187 p_city IN VARCHAR2,
188 p_postal_code IN VARCHAR2,
189 p_insert_flag IN VARCHAR2) RETURN NUMBER;
190
191 --
192 -- Procedure: Process_All_Locations
193 --
194 -- Purpose: Calls Process_All_Locations with l_location_type='BOTH'
195 --
196 --
197
198 PROCEDURE Process_All_Locations (
199 p_dummy1 IN VARCHAR2,
200 p_dummy2 IN VARCHAR2,
201 p_mode IN VARCHAR2 default g_mode,
202 p_num_of_instances IN NUMBER,
203 p_insert_flag IN VARCHAR2,
204 p_start_date IN VARCHAR2,
205 p_end_date IN VARCHAR2);
206
207 --
208 -- Procedure: Process_All_Locations
209 --
210 -- Purpose: Overloaded procedure introduced as a part of ECO - 4740786.
211 -- Returns region id for region found when matching location
212 -- to region
213 --
214
215 PROCEDURE Process_All_Locations (
216 p_dummy1 IN VARCHAR2,
217 p_dummy2 IN VARCHAR2,
218 p_mode IN VARCHAR2 default g_mode,
219 p_num_of_instances IN NUMBER,
223 p_end_date IN VARCHAR2
220 p_insert_flag IN VARCHAR2,
221 p_location_type IN VARCHAR2,
222 p_start_date IN VARCHAR2,
224 );
225
226 --
227 -- Procedure: Get_Child_Requests_Status
228 --
229 -- Purpose: Obtains the completion status of all the child requests
230 -- and sets x_completion_status accordingly
231 --
232
233 PROCEDURE Get_Child_Requests_Status
234 (
235 x_completion_status OUT NOCOPY VARCHAR2
236 );
237
238 --
239 -- Procedure: Get_All_Region_Matches
240 --
241 -- Purpose: Obtains all information for a region, and its parents
242 -- and the zones it belongs to
243 --
244
245 PROCEDURE Get_All_Region_Matches (
246 p_country IN VARCHAR2,
247 p_country_region IN VARCHAR2,
248 p_state IN VARCHAR2,
249 p_city IN VARCHAR2,
250 p_postal_code_from IN VARCHAR2,
251 p_postal_code_to IN VARCHAR2,
252 p_country_code IN VARCHAR2,
253 p_country_region_code IN VARCHAR2,
254 p_state_code IN VARCHAR2,
255 p_city_code IN VARCHAR2,
256 p_lang_code IN VARCHAR2,
257 p_location_id IN NUMBER,
258 p_zone_flag IN VARCHAR2,
259 p_more_matches IN BOOLEAN DEFAULT FALSE,
260 x_status OUT NOCOPY NUMBER,
261 x_regions OUT NOCOPY region_table);
262
263 --
264 -- Procedure: Get_All_Zone_Matches
265 --
266 -- Purpose : The API derives Zones for an input Region .
267 -- A cache is used for the region to zone mapping.
268 --
269
270 PROCEDURE Get_All_Zone_Matches(
271 p_region_id IN NUMBER,
272 x_zone_tab OUT NOCOPY WSH_UTIL_CORE.ID_TAB_TYPE,
273 x_return_status OUT NOCOPY VARCHAR2);
274
275
276 --
277 -- Procedure: Get_All_RegionId_Matches
278 --
279 -- Purpose : The API derives Region id for an input location,
280 -- using table WSH_REGION_LOCATIONS
281 -- Cache is used for storing and retriving location region mappings.
282 -- when p_use_cache is FALSE
283
284 PROCEDURE Get_All_RegionId_Matches(
285 p_location_id IN NUMBER,
286 p_use_cache IN BOOLEAN DEFAULT FALSE,
287 p_lang_code IN VARCHAR2,
288 x_region_tab OUT NOCOPY WSH_UTIL_CORE.ID_TAB_TYPE,
289 x_return_status OUT NOCOPY VARCHAR2);
290
291 --***************************************************************************--
292 --========================================================================
293 -- PROCEDURE : get_all_region_deconsols
294 --
295 -- PARAMETERS: p_location_id Input delivery record
296 -- p_use_cache Whether to use cache
297 -- p_lang_code Language Code
298 -- p_zone_flag Whether to perform search at zone level as well
299 -- p_rule_to_zone_id zone id specified in the consolidation rule
300 -- p_caller Caller of API
301 -- x_region_consol_tab Table of regions containing info
302 -- about deconsol locations for given location
303 -- x_return_status Return status
304 -- COMMENT : This procedure is used to perform for following actions
305 -- Takes input location id
306 -- Finds deconsolidation location for the location as defined on Regions and
307 -- zones form
308 --========================================================================
309
310 PROCEDURE get_all_region_deconsols (
311 p_location_id IN NUMBER,
312 p_use_cache IN BOOLEAN DEFAULT FALSE,
313 p_lang_code IN VARCHAR2,
314 p_zone_flag IN BOOLEAN DEFAULT FALSE,
315 p_rule_to_zone_id IN NUMBER DEFAULT NULL,
316 p_caller IN VARCHAR2 DEFAULT NULL,
317 x_region_consol_tab OUT NOCOPY region_deconsol_Tab_Type,
318 x_return_status OUT NOCOPY VARCHAR2);
319
320 -- Following procedure are added for Regions Interface Performance
321
322 --
323 -- PROCEDURE : Check_Region_Info
324 --
325 -- PURPOSE : Checks whether region exists in Wsh_Regions_Global_Data
326 -- table based on parameters passed to it.
327 PROCEDURE Check_Region_Info (
328 p_country IN VARCHAR2,
329 p_state IN VARCHAR2,
330 p_city IN VARCHAR2,
331 p_postal_code_from IN VARCHAR2,
332 p_postal_code_to IN VARCHAR2,
333 p_region_type IN NUMBER,
334 p_search_flag IN VARCHAR2,
335 p_lang_code IN VARCHAR2,
336 x_return_status OUT NOCOPY VARCHAR2,
337 x_region_info OUT NOCOPY WSH_REGIONS_SEARCH_PKG.region_rec);
338
339 --
340 -- PROCEDURE : Check_Region_Info_Code
341 --
342 -- PURPOSE : Checks whether region exists in Wsh_Regions_Global_Data
343 -- table based on parameters passed to it.
344 PROCEDURE Check_Region_Info_Code (
345 p_country IN VARCHAR2,
346 p_state IN VARCHAR2,
347 p_city IN VARCHAR2,
348 p_country_code IN VARCHAR2,
349 p_state_code IN VARCHAR2,
350 p_city_code IN VARCHAR2,
351 p_region_type IN NUMBER,
352 p_search_flag IN VARCHAR2,
353 p_lang_code IN VARCHAR2,
354 x_return_status OUT NOCOPY VARCHAR2,
355 x_region_info OUT NOCOPY WSH_REGIONS_SEARCH_PKG.region_rec);
356
357 --
358 -- PROCEDURE : Check_Region_Id_Codes_Only
359 --
360 -- PURPOSE : Checks whether region exists in Wsh_Regions_Global_Data/
361 -- Wsh_Regions_Global table based on parameters passed to it.
362 PROCEDURE Check_Region_Id_Codes_Only (
363 p_country_code IN VARCHAR2,
364 p_state_code IN VARCHAR2,
365 p_city_code IN VARCHAR2,
366 p_postal_code_from IN VARCHAR2,
367 p_postal_code_to IN VARCHAR2,
368 p_region_type IN NUMBER,
369 p_language_code IN VARCHAR2 DEFAULT NULL,
370 x_return_status OUT NOCOPY VARCHAR2,
371 x_region_id OUT NOCOPY NUMBER );
372
373 --
374 -- PROCEDURE : Map_Location_Region_Child
375 --
376 -- PURPOSE : Child program to Location to Region Mapping Concurrent request.
377 -- Calls appropriate procedure depending upon value of mode parameter.
378
379 PROCEDURE Map_Location_Region_Child(
380 p_errbuf OUT NOCOPY VARCHAR2,
381 p_retcode OUT NOCOPY NUMBER,
382 p_mode IN VARCHAR2,
383 p_location_type IN VARCHAR2,
384 p_from_value IN NUMBER,
385 p_to_value IN NUMBER,
386 p_start_date IN VARCHAR2,
387 p_end_date IN VARCHAR2,
388 p_insert_flag IN VARCHAR2);
389
390
391 END WSH_REGIONS_SEARCH_PKG;
392
[Home] [Help]