[Home] [Help]
PACKAGE BODY: APPS.MST_GEOCODING
Source
1 PACKAGE BODY MST_GEOCODING AS
2 /*$Header: MSTGEOCB.pls 115.9 2004/01/13 01:24:47 jnhuang noship $*/
3
4
5 Function Get_local_time(p_location_id IN number,
6 p_server_time IN date) return DATE is
7 p_api_version NUMBER;
8 p_init_msg_list VARCHAR2(80);
9 p_timezone_code VARCHAR2(80);
10 p_timezone_id NUMBER;
11 x_GMT_deviation NUMBER;
12 x_timezone_short_code VARCHAR2(30);
13 x_name VARCHAR2(240);
14 x_return_status VARCHAR2(80);
15 x_msg_count VARCHAR2(80);
16 x_msg_data VARCHAR2(2048);
17
18 l_server_timezone_id NUMBER;
19 l_server_timezone_code VARCHAR2(80);
20 l_server_timezone_short_code VARCHAR2(30);
21 l_server_gmt_offset NUMBER;
22 l_name VARCHAR2(240);
23
24 BEGIN
25
26 p_api_version := 1;
27 p_init_msg_list := NULL;
28 l_server_gmt_offset := -8;
29
30 l_server_timezone_id := to_number( FND_PROFILE.VALUE('SERVER_TIMEZONE_ID') );
31
32 BEGIN
33
34 select TIMEZONE_CODE
35 into l_server_timezone_code
36 from FND_TIMEZONES_VL
37 where UPGRADE_TZ_ID = l_server_timezone_id;
38
39 EXCEPTION
40 WHEN NO_DATA_FOUND THEN
41 return p_server_time;
42
43 END;
44
45 -- Now get the GMT offset for the server. It will be used later
46 -- to convert to local time
47
48 HZ_TIMEZONE_PUB.Get_Timezone_Short_Code(
49 p_api_version,
50 p_init_msg_list,
51 l_server_timezone_id,
52 l_server_timezone_code,
53 p_server_time,
54 l_server_gmt_offset,
55 l_server_timezone_short_code,
56 l_name,
57 x_return_status,
58 x_msg_count,
59 x_msg_data );
60
61
62 BEGIN
63
64 select FND.UPGRADE_TZ_ID, FND.TIMEZONE_CODE
65 into p_timezone_id, p_timezone_code
66 from WSH_LOCATIONS WSH, FND_TIMEZONES_VL FND
67 where WSH.TIMEZONE_CODE = FND.TIMEZONE_CODE AND
68 WSH.WSH_LOCATION_ID = p_location_id;
69
70 EXCEPTION
71 WHEN NO_DATA_FOUND THEN
72 return p_server_time;
73 END;
74
75 -- The following API call gets the GMT offset for the location
76
77 HZ_TIMEZONE_PUB.Get_Timezone_Short_Code(
78 p_api_version,
79 p_init_msg_list,
80 p_timezone_id,
81 p_timezone_code,
82 p_server_time,
83 x_GMT_deviation,
84 x_timezone_short_code,
85 x_name,
86 x_return_status,
87 x_msg_count,
88 x_msg_data );
89
90 IF (x_GMT_deviation IS NOT NULL) THEN
91 return p_server_time + (x_GMT_deviation - l_server_gmt_offset)/24.0 ;
92 ELSE
93 return p_server_time;
94 END IF;
95
96 END Get_local_time;
97
98 Function Get_server_time(p_location_id IN number,
99 p_local_time IN date) return DATE is
100 p_api_version NUMBER;
101 p_init_msg_list VARCHAR2(80);
102 p_timezone_id NUMBER;
103 p_timezone_code VARCHAR2(80);
104
105 x_GMT_deviation NUMBER;
106 x_timezone_short_code VARCHAR2(30);
107 x_name VARCHAR2(240);
108 x_return_status VARCHAR2(80);
109 x_msg_count VARCHAR2(80);
110 x_msg_data VARCHAR2(2048);
111
112 l_server_timezone_id NUMBER;
113 l_server_timezone_code VARCHAR2(80);
114 l_server_timezone_short_code VARCHAR2(30);
115 l_server_gmt_offset NUMBER;
116 l_name VARCHAR2(240);
117
118
119 BEGIN
120 p_api_version := 1;
121 p_init_msg_list := NULL;
122 l_server_gmt_offset := -8;
123
124 l_server_timezone_id := to_number( FND_PROFILE.VALUE('SERVER_TIMEZONE_ID') );
125
126 BEGIN
127
128 select TIMEZONE_CODE
129 into l_server_timezone_code
130 from FND_TIMEZONES_VL
131 where UPGRADE_TZ_ID = l_server_timezone_id;
132
133 EXCEPTION
134 WHEN NO_DATA_FOUND THEN
135 return p_local_time;
136
137 END;
138
139 -- Now get the GMT offset for the server. It will be used later
140 -- to convert to local time
141
142 HZ_TIMEZONE_PUB.Get_Timezone_Short_Code(
143 p_api_version,
144 p_init_msg_list,
145 l_server_timezone_id,
146 l_server_timezone_code,
147 p_local_time,
148 l_server_gmt_offset,
149 l_server_timezone_short_code,
150 l_name,
151 x_return_status,
152 x_msg_count,
153 x_msg_data );
154
155 BEGIN
156 select FND.UPGRADE_TZ_ID, FND.TIMEZONE_CODE
157 into p_timezone_id, p_timezone_code
158 from WSH_LOCATIONS WSH, FND_TIMEZONES_VL FND
159 where WSH.TIMEZONE_CODE = FND.TIMEZONE_CODE AND
160 WSH.WSH_LOCATION_ID = p_location_id;
161
162 EXCEPTION
163 WHEN NO_DATA_FOUND THEN
164 return p_local_time;
165
166 END;
167
168 -- The following API call gets the GMT offset for the location
169
170 HZ_TIMEZONE_PUB.Get_Timezone_Short_Code(
171 p_api_version,
172 p_init_msg_list,
173 p_timezone_id,
174 p_timezone_code,
175 p_local_time,
176 x_GMT_deviation,
177 x_timezone_short_code,
178 x_name,
179 x_return_status,
180 x_msg_count,
181 x_msg_data );
182
183 IF (x_GMT_deviation IS NOT NULL) THEN
184 return p_local_time + (l_server_gmt_offset - x_GMT_deviation)/24.0;
185 ELSE
186 return p_local_time;
187 END IF;
188
189 END Get_server_time;
190
191
192 Function Get_timezone_code(p_location_id IN number,
193 p_date IN date) return VARCHAR2 is
194 p_api_version NUMBER;
195 p_init_msg_list VARCHAR2(80);
196 p_timezone_id NUMBER;
197 p_timezone_code VARCHAR2(80);
198 x_gmt_deviation NUMBER;
199 x_timezone_short_code VARCHAR2(30);
200 x_name VARCHAR2(240);
201 x_return_status VARCHAR2(80);
202 x_msg_count VARCHAR2(80);
203 x_msg_data VARCHAR2(2048);
204
205 BEGIN
206
207 p_api_version := 1;
208 p_init_msg_list := NULL;
209 x_timezone_short_code := NULL;
210
211 -- If the date is NULL, then the time zone code should also be NULL
212
213 IF (p_date IS NULL) THEN
214 return x_timezone_short_code;
215 END IF;
216
217 -- Initialize timezone_code to server timezone code
218 -- If no information exists in WSH_LOCATIONS or HZ_TIMEZONES, we will
219 -- return the server timezone code
220
221 p_timezone_id := to_number( FND_PROFILE.VALUE('SERVER_TIMEZONE_ID') );
222
223 BEGIN
224
225 select TIMEZONE_CODE
226 into p_timezone_code
227 from FND_TIMEZONES_VL
228 where UPGRADE_TZ_ID = p_timezone_id;
229
230 EXCEPTION
231 WHEN NO_DATA_FOUND THEN
232 return x_timezone_short_code;
233
234 END;
235
236 HZ_TIMEZONE_PUB.Get_Timezone_Short_Code(
237 p_api_version,
238 p_init_msg_list,
239 p_timezone_id,
240 p_timezone_code,
241 p_date,
242 x_gmt_deviation,
243 x_timezone_short_code,
244 x_name,
245 x_return_status,
246 x_msg_count,
247 x_msg_data );
248
249
250 -- Finished getting the server time zone short code
251 -- Now get the local specific time zone short code
252
253 BEGIN
254 select FND.UPGRADE_TZ_ID, FND.TIMEZONE_CODE
255 into p_timezone_id, p_timezone_code
256 from WSH_LOCATIONS WSH, FND_TIMEZONES_VL FND
257 where WSH.TIMEZONE_CODE = FND.TIMEZONE_CODE AND
258 WSH.WSH_LOCATION_ID = p_location_id;
259
260 EXCEPTION
261 WHEN NO_DATA_FOUND THEN
262 return x_timezone_short_code;
263
264 END;
265
266 HZ_TIMEZONE_PUB.Get_Timezone_Short_Code(
267 p_api_version,
268 p_init_msg_list,
269 p_timezone_id,
270 p_timezone_code,
271 p_date,
272 x_gmt_deviation,
273 x_timezone_short_code,
274 x_name,
275 x_return_status,
276 x_msg_count,
277 x_msg_data );
278
279 return x_timezone_short_code;
280
281 END Get_timezone_code;
282
283 Function Get_server_timezone_code(p_date IN date) return VARCHAR2 is
284 p_api_version NUMBER;
285 p_init_msg_list VARCHAR2(80);
286 p_timezone_id NUMBER;
287 p_timezone_code VARCHAR2(80);
288 x_gmt_deviation NUMBER;
289 x_timezone_short_code VARCHAR2(30);
290 x_name VARCHAR2(240);
291 x_return_status VARCHAR2(80);
292 x_msg_count VARCHAR2(80);
293 x_msg_data VARCHAR2(2048);
294
295 BEGIN
296
297 p_api_version := 1;
298 p_init_msg_list := NULL;
299 x_timezone_short_code := NULL;
300
301 -- If the date is NULL, then the time zone code should also be NULL
302
303 IF (p_date IS NULL) THEN
304 return x_timezone_short_code;
305 END IF;
306
307 -- Initialize timezone_code to server timezone code
308 -- If no information exists in WSH_LOCATIONS or HZ_TIMEZONES, we will
309 -- return the server timezone code
310
311 p_timezone_id := to_number( FND_PROFILE.VALUE('SERVER_TIMEZONE_ID') );
312
313 BEGIN
314
315 select TIMEZONE_CODE
316 into p_timezone_code
317 from FND_TIMEZONES_VL
318 where UPGRADE_TZ_ID = p_timezone_id;
319
320 EXCEPTION
321 WHEN NO_DATA_FOUND THEN
322 return x_timezone_short_code;
323
324 END;
325
326 HZ_TIMEZONE_PUB.Get_Timezone_Short_Code(
327 p_api_version,
328 p_init_msg_list,
329 p_timezone_id,
330 p_timezone_code,
331 p_date,
332 x_gmt_deviation,
333 x_timezone_short_code,
334 x_name,
335 x_return_status,
336 x_msg_count,
337 x_msg_data );
338
339 return x_timezone_short_code;
340
341 END Get_server_timezone_code;
342
343
344 Procedure Get_facility_parameters(p_api_version IN NUMBER
345 , p_init_msg_list IN VARCHAR2
346 , x_pallet_load_rate OUT NOCOPY NUMBER
347 , x_pallet_unload_rate OUT NOCOPY NUMBER
348 , x_non_pallet_load_rate OUT NOCOPY NUMBER
349 , x_non_pallet_unload_rate OUT NOCOPY NUMBER
350 , x_pallet_handling_uom OUT NOCOPY VARCHAR2
351 , x_non_pallet_handling_uom OUT NOCOPY VARCHAR2
352 , x_return_status OUT NOCOPY VARCHAR2
353 , x_msg_count OUT NOCOPY VARCHAR2
354 , x_msg_data OUT NOCOPY VARCHAR2
355
356 ) is
357
358 v_statement varchar2(20000);
359 l_return_status varchar2(10);
360 p_global_user_id number;
361 p_is_spliced number;
362 p_SqlErrM varchar2(2000);
363
364 BEGIN
365
366 p_global_user_id := -9999;
367
368 x_pallet_load_rate := NULL;
369 x_pallet_unload_rate := NULL;
370 x_non_pallet_load_rate := NULL;
371 x_non_pallet_unload_rate := NULL;
372 x_pallet_handling_uom := NULL;
373 x_non_pallet_handling_uom:= NULL;
374
375
376 v_statement :=
377 'select ' ||
378 'PALLETIZED_LOADING_RATE, ' ||
379 'PALLETIZED_UNLOADING_RATE, ' ||
380 'NON_PALLETIZED_LOADING_RATE, ' ||
381 'NON_PALLETIZED_UNLOADING_RATE, ' ||
382 'PALLETIZED_WEIGHT_UOM, ' ||
383 'NON_PALLETIZED_WEIGHT_UOM ' ||
384 'from MST_PARAMETERS ' ||
385 'where USER_ID = :p_global_user_id';
386
387 select COUNT(*)
388 into p_is_spliced
389 from FND_APPLICATION
390 where APPLICATION_SHORT_NAME = 'MST';
391
392
393 IF (p_is_spliced = 1) THEN
394 EXECUTE IMMEDIATE v_statement
395 INTO x_pallet_load_rate,
396 x_pallet_unload_rate,
397 x_non_pallet_load_rate,
398 x_non_pallet_unload_rate,
399 x_pallet_handling_uom,
400 x_non_pallet_handling_uom
401 USING p_global_user_id ;
402
403 IF (x_pallet_handling_uom = '34') THEN
404 x_pallet_handling_uom := 'PALLET';
405 ELSIF (x_pallet_handling_uom = '35') THEN
406 x_pallet_handling_uom := 'CONTAINER';
407 END IF;
408
409 IF (x_non_pallet_handling_uom = '35') THEN
410 x_non_pallet_handling_uom := 'CONTAINER';
411 END IF;
412
413 END IF;
414
415
416 EXCEPTION
417 WHEN OTHERS THEN
418 p_SqlErrM := sqlerrm||' (Error in Get_facility_parameters)';
419
420 END Get_facility_parameters;
421
422 END MST_GEOCODING;