1 PACKAGE WSH_REGIONS_PKG AS
2 /* $Header: WSHRETHS.pls 120.1 2005/07/05 00:17:27 pkaliyam noship $ */
3
4 --
5 -- Package
6 -- WSH_REGIONS_PKG
7 --
8 -- Purpose
9 --
10
11 --
12 -- PACKAGE TYPES
13 --
14
15 TYPE tab_region_id is TABLE OF WSH_REGIONS.Region_Id%TYPE index by binary_integer;
16 TYPE tab_country_code is TABLE OF WSH_REGIONS.Country_Code%TYPE index by binary_integer;
17 TYPE tab_country_region_code is TABLE OF WSH_REGIONS.Country_Region_Code%TYPE index by binary_integer;
18 TYPE tab_state_code is TABLE OF WSH_REGIONS.State_Code%TYPE index by binary_integer;
19 TYPE tab_city_code is TABLE OF WSH_REGIONS.City_Code%TYPE index by binary_integer;
20 TYPE tab_port_flag is TABLE OF WSH_REGIONS.Port_Flag%TYPE index by binary_integer;
21 TYPE tab_airport_flag is TABLE OF WSH_REGIONS.Airport_Flag%TYPE index by binary_integer;
22 TYPE tab_road_terminal_flag is TABLE OF WSH_REGIONS.Road_Terminal_Flag%TYPE index by binary_integer;
23 TYPE tab_rail_terminal_flag is TABLE OF WSH_REGIONS.Rail_Terminal_Flag%TYPE index by binary_integer;
24 TYPE tab_longitude is TABLE OF WSH_REGIONS.Longitude%TYPE index by binary_integer;
25 TYPE tab_latitude is TABLE OF WSH_REGIONS.Latitude%TYPE index by binary_integer;
26 TYPE tab_timezone is TABLE OF WSH_REGIONS.Timezone%TYPE index by binary_integer;
27 TYPE tab_continent is TABLE OF WSH_REGIONS_TL.Continent%TYPE index by binary_integer;
28 TYPE tab_country is TABLE OF WSH_REGIONS_TL.Country%TYPE index by binary_integer;
29 TYPE tab_country_region is TABLE OF WSH_REGIONS_TL.Country_Region%TYPE index by binary_integer;
30 TYPE tab_state is TABLE OF WSH_REGIONS_TL.State%TYPE index by binary_integer;
31 TYPE tab_city is TABLE OF WSH_REGIONS_TL.City%TYPE index by binary_integer;
32 TYPE tab_alternate_name is TABLE OF WSH_REGIONS_TL.Alternate_Name%TYPE index by binary_integer;
33 TYPE tab_county is TABLE OF WSH_REGIONS_TL.County%TYPE index by binary_integer;
34 TYPE tab_postal_code_from is TABLE OF WSH_REGIONS_TL.Postal_Code_From%TYPE index by binary_integer;
35 TYPE tab_postal_code_to is TABLE OF WSH_REGIONS_TL.Postal_Code_To%TYPE index by binary_integer;
36 TYPE tab_language is TABLE OF WSH_REGIONS_TL.Language%TYPE index by binary_integer;
37
38 --
39 -- PUBLIC VARIABLES
40 --
41
42 --
43 --RECORD TO STORE DFF FIELD VALUES
44 --
45
46 TYPE REGION_DFF_REC IS RECORD(
47 ATTRIBUTE_CATEGORY VARCHAR2(150),
48 ATTRIBUTE1 VARCHAR2(150),
49 ATTRIBUTE2 VARCHAR2(150),
50 ATTRIBUTE3 VARCHAR2(150),
51 ATTRIBUTE4 VARCHAR2(150),
52 ATTRIBUTE5 VARCHAR2(150),
53 ATTRIBUTE6 VARCHAR2(150),
54 ATTRIBUTE7 VARCHAR2(150),
55 ATTRIBUTE8 VARCHAR2(150),
56 ATTRIBUTE9 VARCHAR2(150),
57 ATTRIBUTE10 VARCHAR2(150),
58 ATTRIBUTE11 VARCHAR2(150),
59 ATTRIBUTE12 VARCHAR2(150),
60 ATTRIBUTE13 VARCHAR2(150),
61 ATTRIBUTE14 VARCHAR2(150),
62 ATTRIBUTE15 VARCHAR2(150)
63 );
64
65 --
66 -- PUBLIC FUNCTIONS/PROCEDURES
67 --
68
69 --
70 -- Procedure: Get_Parent_Region_Info
71 --
72 -- Purpose: Retrieves all region info of the region passed in, and if it
73 -- does not exist and p_parent_insert_flag = 'Y' , inserts into the database
74 --
75 --
76
77 PROCEDURE Get_Parent_Region_Info(
78 p_parent_region_type IN NUMBER,
79 p_country_code IN VARCHAR2,
80 p_country_region_code IN VARCHAR2,
81 p_state_code IN VARCHAR2,
82 p_city_code IN VARCHAR2,
83 p_country IN VARCHAR2,
84 p_country_region IN VARCHAR2,
85 p_state IN VARCHAR2,
86 p_city IN VARCHAR2,
87 p_lang_code IN VARCHAR2,
88 p_interface_flag IN VARCHAR2,
89 p_user_id IN NUMBER,
90 p_insert_parent_flag IN VARCHAR2,
91 x_parent_region_info OUT NOCOPY wsh_regions_search_pkg.region_rec,
92 p_conc_request_flag IN VARCHAR2 DEFAULT 'N');
93
94 --
95 -- Procedure: Add_Region
96 --
97 -- Purpose: Inserts the region with appropriate data and returns the
98 -- region_id
99 --
100
101 PROCEDURE Add_Region (
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_port_flag IN VARCHAR2,
107 p_airport_flag IN VARCHAR2,
108 p_road_terminal_flag IN VARCHAR2,
109 p_rail_terminal_flag IN VARCHAR2,
110 p_longitude IN NUMBER,
111 p_latitude IN NUMBER,
112 p_timezone IN VARCHAR2,
113 p_continent IN VARCHAR2,
114 p_country IN VARCHAR2,
115 p_country_region IN VARCHAR2,
116 p_state IN VARCHAR2,
117 p_city IN VARCHAR2,
118 p_alternate_name IN VARCHAR2,
119 p_county IN VARCHAR2,
120 p_postal_code_from IN VARCHAR2,
121 p_postal_code_to IN VARCHAR2,
122 p_lang_code IN VARCHAR2,
123 p_region_type IN NUMBER,
124 p_parent_region_id IN NUMBER,
125 p_interface_flag IN VARCHAR2,
126 p_tl_only_flag IN VARCHAR2,
127 p_region_id IN NUMBER,
128 p_region_dff IN REGION_DFF_REC DEFAULT NULL,
129 x_region_id OUT NOCOPY NUMBER,
130 p_deconsol_location_id IN NUMBER DEFAULT NULL);
131
132 --
133 -- Procedure: Insert_Region
134 --
135 -- Purpose: Inserts the region with appropriate data, and recursively inserts
136 -- the parent region if it doesn't exist thru Get_Parent_Region_Id
137 --
138
139 PROCEDURE Insert_Region (
140 p_country_code IN VARCHAR2,
141 p_country_region_code IN VARCHAR2,
142 p_state_code IN VARCHAR2,
143 p_city_code IN VARCHAR2,
144 p_port_flag IN VARCHAR2,
145 p_airport_flag IN VARCHAR2,
146 p_road_terminal_flag IN VARCHAR2,
147 p_rail_terminal_flag IN VARCHAR2,
148 p_longitude IN NUMBER,
149 p_latitude IN NUMBER,
150 p_timezone IN VARCHAR2,
151 p_continent IN VARCHAR2,
152 p_country IN VARCHAR2,
153 p_country_region IN VARCHAR2,
154 p_state IN VARCHAR2,
155 p_city IN VARCHAR2,
156 p_alternate_name IN VARCHAR2,
157 p_county IN VARCHAR2,
158 p_postal_code_from IN VARCHAR2,
159 p_postal_code_to IN VARCHAR2,
160 p_lang_code IN VARCHAR2,
161 p_interface_flag IN VARCHAR2,
162 p_tl_only_flag IN VARCHAR2,
163 p_region_id IN NUMBER,
164 p_parent_region_id IN NUMBER,
165 p_user_id IN NUMBER,
166 p_insert_parent_flag IN VARCHAR2,
167 p_region_dff IN REGION_DFF_REC DEFAULT NULL,
168 x_region_id OUT NOCOPY NUMBER,
169 x_status OUT NOCOPY NUMBER,
170 x_error_msg OUT NOCOPY VARCHAR2,
171 p_deconsol_location_id IN NUMBER DEFAULT NULL,
172 p_conc_request_flag IN VARCHAR2 DEFAULT 'N');
173
174 --
175 -- Procedure: Update_Region
176 --
177 -- Purpose: Updates a region with new information if the region exists,
178 -- otherwise calls Insert_Region to insert the region.
179 --
180
181 PROCEDURE Update_Region (
182 p_insert_type IN VARCHAR2,
183 p_region_id IN NUMBER,
184 p_parent_region_id IN NUMBER,
185 p_continent IN VARCHAR2,
186 p_country IN VARCHAR2,
187 p_country_region IN VARCHAR2,
188 p_state IN VARCHAR2,
189 p_city IN VARCHAR2,
190 p_alternate_name IN VARCHAR2,
191 p_county IN VARCHAR2,
192 p_postal_code_from IN VARCHAR2,
193 p_postal_code_to IN VARCHAR2,
194 p_lang_code IN VARCHAR2,
195 p_country_code IN VARCHAR2,
196 p_country_region_code IN VARCHAR2,
197 p_state_code IN VARCHAR2,
198 p_city_code IN VARCHAR2,
199 p_port_flag IN VARCHAR2,
200 p_airport_flag IN VARCHAR2,
201 p_road_terminal_flag IN VARCHAR2,
202 p_rail_terminal_flag IN VARCHAR2,
203 p_longitude IN NUMBER,
204 p_latitude IN NUMBER,
205 p_timezone IN VARCHAR2,
206 p_interface_flag IN VARCHAR2,
207 p_user_id IN NUMBER,
208 p_insert_parent_flag IN VARCHAR2 DEFAULT 'N',
209 p_region_dff IN REGION_DFF_REC DEFAULT NULL,
210 x_region_id OUT NOCOPY NUMBER,
211 x_status OUT NOCOPY NUMBER,
212 x_error_msg OUT NOCOPY VARCHAR2,
213 p_deconsol_location_id IN NUMBER DEFAULT NULL,
214 p_conc_request_flag IN VARCHAR2 DEFAULT 'N');
215
216 --
217 -- Procedure: Delete_Region
218 --
219 -- Purpose: Deletes a region (for interface use only)
220 --
221
222 PROCEDURE Delete_Region (
223 p_region_id IN NUMBER,
224 p_lang_code IN VARCHAR2,
225 p_interface_flag IN VARCHAR2,
226 x_status OUT NOCOPY NUMBER,
227 x_error_msg OUT NOCOPY VARCHAR2);
228
229 --
230 -- Procedure: Lock_Region
231 -- Parameters: p_region_id - region_id for region to be locked
232 -- x_return_status - Status of procedure call
233 -- Description: This procedure will lock a region record. It is
234 -- specifically designed for use by the form.
235 --
236
237 PROCEDURE Lock_Region
238 (p_region_id IN NUMBER,
239 p_lang_code IN VARCHAR2,
240 p_country IN VARCHAR2,
241 p_state IN VARCHAR2,
242 p_city IN VARCHAR2,
243 p_postal_code_from IN VARCHAR2,
244 p_postal_code_to IN VARCHAR2,
245 p_country_code IN VARCHAR2,
246 p_state_code IN VARCHAR2,
247 p_city_code IN VARCHAR2,
248 p_region_dff IN REGION_DFF_REC DEFAULT NULL,
249 x_status OUT NOCOPY NUMBER,
250 p_deconsol_location_id IN NUMBER DEFAULT NULL);
251
252
253 --
254 -- Procedure: Lock_Region_Interface
255 -- Parameters: p_region_id - region_id for region to be locked
256 -- x_return_status - Status of procedure call
257 -- Description: This procedure will lock a region record. It is
258 -- specifically designed for use by the form.
259 --
260
261 PROCEDURE Lock_Region_Interface
262 (p_region_id IN NUMBER,
263 p_lang_code IN VARCHAR2,
267 p_postal_code_from IN VARCHAR2,
264 p_country IN VARCHAR2,
265 p_state IN VARCHAR2,
266 p_city IN VARCHAR2,
268 p_postal_code_to IN VARCHAR2,
269 p_country_code IN VARCHAR2,
270 p_state_code IN VARCHAR2,
271 p_city_code IN VARCHAR2,
272 x_status OUT NOCOPY NUMBER);
273
274 --
275 -- Procedure: Update_Zone (this is called from the Regions and Zones form)
276 --
277 -- Purpose: Updates or inserts a new zone
278 --
279
280 PROCEDURE Update_Zone (
281 p_insert_type IN VARCHAR2,
282 p_zone_id IN NUMBER,
283 p_zone_name IN VARCHAR2,
284 p_zone_level IN NUMBER,
285 p_lang_code IN VARCHAR2,
286 p_user_id IN NUMBER,
287 p_zone_dff IN REGION_DFF_REC DEFAULT NULL,
288 x_zone_id OUT NOCOPY NUMBER,
289 x_status OUT NOCOPY NUMBER,
290 x_error_msg OUT NOCOPY VARCHAR2,
291 p_deconsol_location_id IN NUMBER DEFAULT NULL);
292
293 --
294 -- Procedure: Update_Zone
295 --
296 -- Purpose: Updates or inserts a new zone
297 --
298
299 PROCEDURE Update_Zone (
300 p_insert_type IN VARCHAR2,
301 p_zone_id IN NUMBER,
302 p_zone_name IN VARCHAR2,
303 p_zone_level IN NUMBER,
304 p_zone_type IN NUMBER,
305 p_lang_code IN VARCHAR2,
306 p_user_id IN NUMBER,
307 p_zone_dff IN REGION_DFF_REC DEFAULT NULL,
308 x_zone_id OUT NOCOPY NUMBER,
309 x_status OUT NOCOPY NUMBER,
310 x_error_msg OUT NOCOPY VARCHAR2,
311 p_deconsol_location_id IN NUMBER DEFAULT NULL);
312
313 --
314 -- Procedure: Lock_Zone
315 -- Parameters: p_zone_id - zone_id for zone to be locked
316 -- x_return_status - Status of procedure call
317 -- Description: This procedure will lock a zone record. It is
318 -- specifically designed for use by the form.
319 --
320
321 PROCEDURE Lock_Zone
322 (p_zone_id IN NUMBER,
323 p_lang_code IN VARCHAR2,
324 p_zone_name IN VARCHAR2,
325 p_zone_level IN VARCHAR2,
326 x_status OUT NOCOPY NUMBER);
327
328 --
329 -- Procedure: Update_Zone_Region
330 --
331 -- Purpose: Updates or inserts a new zone region
332 -- Has one more parameter P_ZONE_TYPE which indicates
333 -- whether it's a normal zone(10) or a pricing zone(11)
334
338 p_zone_id IN NUMBER,
335 PROCEDURE Update_Zone_Region (
336 p_insert_type IN VARCHAR2,
337 p_zone_region_id IN NUMBER,
339 p_region_id IN NUMBER,
340 p_country IN VARCHAR2,
341 p_state IN VARCHAR2,
342 p_city IN VARCHAR2,
343 p_postal_code_from IN VARCHAR2,
344 p_postal_code_to IN VARCHAR2,
345 p_lang_code IN VARCHAR2,
346 p_country_code IN VARCHAR2,
347 p_state_code IN VARCHAR2,
348 p_city_code IN VARCHAR2,
349 p_user_id IN NUMBER,
350 p_zone_type IN VARCHAR2,
351 x_zone_region_id OUT NOCOPY NUMBER,
352 x_region_id OUT NOCOPY NUMBER,
353 x_status OUT NOCOPY NUMBER,
354 x_error_msg OUT NOCOPY VARCHAR2);
355 --
356
357 -- Procedure: Update_Zone_Region
358 --
359 -- Purpose: Updates or inserts a new zone region
360 -- Call another Update_Zone_Region with default p_zone_type='10'
361
362 PROCEDURE Update_Zone_Region (
363 p_insert_type IN VARCHAR2,
364 p_zone_region_id IN NUMBER,
365 p_zone_id IN NUMBER,
366 p_country IN VARCHAR2,
367 p_state IN VARCHAR2,
368 p_city IN VARCHAR2,
369 p_postal_code_from IN VARCHAR2,
370 p_postal_code_to IN VARCHAR2,
374 p_city_code IN VARCHAR2,
371 p_lang_code IN VARCHAR2,
372 p_country_code IN VARCHAR2,
373 p_state_code IN VARCHAR2,
375 p_user_id IN NUMBER,
376 x_zone_region_id OUT NOCOPY NUMBER,
377 x_region_id OUT NOCOPY NUMBER,
378 x_status OUT NOCOPY NUMBER,
379 x_error_msg OUT NOCOPY VARCHAR2);
380
381 --
382 -- Procedure: Lock_Zone_Region
383 -- Parameters: p_zone_region_id - zone_region_id for zone region to be locked
384 -- p_zone_id - zone id
385 -- p_region_id - zone component region id
386 -- x_return_status - Status of procedure call
387 -- Description: This procedure will lock a zone component record. It is
388 -- specifically designed for use by the form.
389 --
390
391 PROCEDURE Lock_Zone_Region
392 (p_zone_region_id IN NUMBER,
393 p_zone_id IN NUMBER,
394 p_region_id IN NUMBER,
395 x_status OUT NOCOPY NUMBER);
396
397 --
398 -- Procedure: Load_Region
399 --
400 -- Purpose: Loads the region information into interface tables
401 -- without any validation.
402 --
403
404 PROCEDURE Load_Region (
405 p_country_code IN VARCHAR2,
406 p_country_region_code IN VARCHAR2,
407 p_state_code IN VARCHAR2,
408 p_city_code IN VARCHAR2,
409 p_port_flag IN VARCHAR2,
410 p_airport_flag IN VARCHAR2,
411 p_road_terminal_flag IN VARCHAR2,
412 p_rail_terminal_flag IN VARCHAR2,
413 p_longitude IN NUMBER,
414 p_latitude IN NUMBER,
415 p_timezone IN VARCHAR2,
416 p_continent IN VARCHAR2,
417 p_country IN VARCHAR2,
418 p_country_region IN VARCHAR2,
419 p_state IN VARCHAR2,
420 p_city IN VARCHAR2,
421 p_alternate_name IN VARCHAR2,
422 p_county IN VARCHAR2,
423 p_postal_code_from IN VARCHAR2,
424 p_postal_code_to IN VARCHAR2,
425 p_lang_code IN VARCHAR2,
426 p_deconsol_location_id IN NUMBER DEFAULT NULL);
427
428 --
429 -- Procedure: Default_Regions
430 --
431 -- Purpose: Copies regions from the interface tables to
432 -- the real regions tables
433 --
434
435 PROCEDURE Default_Regions (
436 x_status OUT NOCOPY NUMBER,
437 x_regions_processed OUT NOCOPY NUMBER,
438 x_error_msg_text OUT NOCOPY VARCHAR2);
439
440 --
441 -- Procedure: Default_Regions (for concurrent program usage)
442 --
443 -- Purpose: Copies regions from the interface tables to
444 -- the real regions tables
445 --
446
447 PROCEDURE Default_Regions (
448 p_dummy1 IN VARCHAR2,
449 p_dummy2 IN VARCHAR2);
450
451 -- This method in only for the purpose of submitting a request from the form
452
453 FUNCTION Load_All_Regions RETURN NUMBER;
454
455 --
456 -- Function: getZoneRegions
457 --
458 -- Purpose: used by FTE_CAT_ZONE_LOV
459 -- to show regions that belong to a zone
460 --
461
462 FUNCTION getZoneRegions (
463 p_zoneId IN NUMBER,
464 p_lang IN VARCHAR2) return VARCHAR2;
465
466 /*----------------------------------------------------------*/
467 /* Add_Language Procedure */
468 /*--------------------------------------------------------_-*/
469 procedure ADD_LANGUAGE;
470
471 -- Following procedure are added for Regions Interface Performance
472
473 --
477 -- Same validatin are in the When-Validate-Record trigger
474 -- PROCEDURE : Validate_Region
475 --
476 -- PURPOSE : Validation regarding missing parameters or wrong format
478 -- on the Region block in WSHRGZON.fmb form
479 PROCEDURE Validate_Region (
480 p_country IN VARCHAR2,
481 p_state IN VARCHAR2,
482 p_city IN VARCHAR2,
483 p_country_code IN VARCHAR2,
484 p_state_code IN VARCHAR2,
485 p_city_code IN VARCHAR2,
486 p_postal_code_from IN VARCHAR2,
487 p_postal_code_to IN VARCHAR2,
488 x_status OUT NOCOPY NUMBER ,
489 x_error_msg OUT NOCOPY VARCHAR2 );
490
491 --
492 -- PROCEDURE : Init_Global_Table
493 --
494 -- PURPOSE : Populates the data in Global Temp tables(Wsh_Regions_Global
495 -- and Wsh_Regions_Global_Data) fetched from Wsh_Regions and
496 -- Wsh_Regions_Tl based on parameter p_populate_type.
497 --
498 PROCEDURE Init_global_table (
499 p_country IN VARCHAR2,
500 p_state IN VARCHAR2,
501 p_city IN VARCHAR2,
502 p_country_code IN VARCHAR2,
503 p_state_code IN VARCHAR2,
504 p_city_code IN VARCHAR2,
505 p_country_flag IN VARCHAR2,
509
506 p_state_flag IN VARCHAR2,
507 p_city_flag IN VARCHAR2,
508 x_return_status OUT NOCOPY VARCHAR2 );
510 --
511 -- PROCEDURE : Insert_Global_Table
512 --
513 -- PURPOSE : Inserts the data in Global Temp tables
514 -- ( Wsh_Regions_Global_Data and Wsh_Regions_Global tables )
515 PROCEDURE Insert_Global_Table (
516 p_country IN VARCHAR2,
517 p_state IN VARCHAR2,
518 p_city IN VARCHAR2,
519 p_country_code IN VARCHAR2,
520 p_state_code IN VARCHAR2,
521 p_city_code IN VARCHAR2,
522 p_region_id IN NUMBER ,
523 p_region_type IN NUMBER ,
524 p_parent_region_id IN NUMBER ,
525 p_postal_code_from IN VARCHAR2,
526 p_postal_code_to IN VARCHAR2,
527 p_tl_only_flag IN VARCHAR2,
528 p_lang_code IN VARCHAR2,
529 x_return_status OUT NOCOPY VARCHAR2 );
530
531 --
532 -- PROCEDURE : Update_Global_Table
533 --
534 -- PURPOSE : Updates the data in Global Temp tables
535 -- ( Wsh_Regions_Global_Data and Wsh_Regions_Global tables )
536 PROCEDURE Update_Global_Table (
537 p_country IN VARCHAR2,
538 p_state IN VARCHAR2,
539 p_city IN VARCHAR2,
540 p_country_code IN VARCHAR2,
541 p_state_code IN VARCHAR2,
542 p_city_code IN VARCHAR2,
543 p_region_id IN NUMBER ,
544 p_postal_code_from IN VARCHAR2,
545 p_postal_code_to IN VARCHAR2,
546 p_parent_zone_level IN NUMBER,
547 p_lang_code IN VARCHAR2,
548 x_return_status OUT NOCOPY VARCHAR2 );
549
550 END WSH_REGIONS_PKG;
551
[Home] [Help]