1 PACKAGE BODY WSH_UTIL AS
2 /* $Header: WSHUUTLB.pls 115.5 99/08/18 12:37:18 porting ship $ */
3
4
5 --
6 -- Package Variables
7 --
8 initialized VARCHAR2(1) := 'N';
9 log_buffer logTabTyp;
10 log_char_buffer logCharTyp;
11 current_line NUMBER := 1;
12 request_id NUMBER := -1;
13 debug_flag VARCHAR2(1) := 'N';
14 debug_level NUMBER := 0;
15 flush VARCHAR2(1) := 'N';
16 source VARCHAR2(1) := 'u';
17 log_file_handle UTL_FILE.FILE_TYPE;
18 log_file_name VARCHAR2(20);
19 file_location VARCHAR2(20) := '/home/rshivram/temp';
20 last_flush_record NUMBER := 1;
21
22 --
23 -- PACKAGE CONSTANTS
24 --
25
26 SUCCESS CONSTANT NUMBER := 0;
27 FAILURE CONSTANT NUMBER := -1;
28 MAX_LINES CONSTANT NUMBER := 1000;
29 MAX_LENGTH CONSTANT NUMBER := 2000;
30
31 --
32 -- This function initializes the package. Should only be called if
33 -- you care about the flush variable which is set to 'N' by default.
34 -- Arguments:
35 -- p_flush -> 'Y' or 'N' depending on whether the line is to be
36 -- written to the file immediately or not.
37 --
38
39 FUNCTION Init (
40 p_flush IN VARCHAR2 DEFAULT 'N'
41 ) RETURN NUMBER IS
42
43 BEGIN
44 flush := 'N';
45 initialized := 'Y';
46 RETURN SUCCESS;
47
48 EXCEPTION
49 WHEN OTHERS THEN
50 RETURN FAILURE;
51 END Init;
52
53 --
54 -- This function creates a new log file and opens it for writing log
55 -- information
56 --
57
58 FUNCTION Open_File RETURN NUMBER IS
59 today VARCHAR2(8);
60 BEGIN
61 IF wshsrc = 'u' THEN
62 SELECT to_char(SYSDATE,'DDHHMISS')
63 INTO today
64 FROM dual;
65 log_file_name := 'l' || today || '.req';
66 ELSE
67 log_file_name := 'l' || to_char(request_id) || '.req';
68 END IF;
69 log_file_handle := UTL_FILE.FOPEN(file_location, log_file_name, 'w');
70 END Open_File;
71
72 --
73 -- This function returns 'u' if called from form etc or 'c' for
74 -- concurrent program
75 --
76
77 FUNCTION Wshsrc RETURN VARCHAR2 IS
78 ret_code NUMBER;
79 BEGIN
80 RETURN source;
81 END Wshsrc;
82
83 --
84 -- This function writes a line of text to the log file always
85 -- Arguments:
86 -- p_text -> text to be logged, must be less that 80 chars per line
87 -- otherwise, is truncated
88 -- p_token -> if you want a token string to be attched to the line
89 -- for retrieveing specific lines in the log file
90 --
91
92 PROCEDURE Write_Line (
93 p_text IN VARCHAR2,
94 p_token IN VARCHAR2
95 ) IS
96 ret_code NUMBER;
97 BEGIN
98
99 IF current_line >= MAX_LINES THEN
100 RETURN;
101 END IF;
102 IF p_token IS NOT NULL THEN
103 log_buffer(current_line).token := SUBSTR(p_token,1,80);
104 END IF;
105 log_buffer(current_line).text_line := p_text;
106 current_line := current_line + 1;
107
108 EXCEPTION
109 WHEN OTHERS THEN
110 Default_Handler('WSH_UTIL.Write_Line','Error in Write_Line');
111 END Write_Line;
112
113 --
114 -- This function writes a line of text to the log file (less than
115 -- MAX_LENGTH characters), if debug level set to p_debug_level or lower
116 -- (and debug flag is 'Y').
117 -- It uses Write_Line, there fore it splices the text into lines
118 -- of characters at most 80 characters long.
119 --
120 -- If p_debug_level is 0 then always write the line.
121 -- Otherwise, similar to Write_Line.
122 --
123
124 PROCEDURE Write_Log (
125 p_text IN VARCHAR2,
126 p_debug_level IN NUMBER DEFAULT 0,
127 p_token IN VARCHAR2 DEFAULT ''
128 ) IS
129 ret_code NUMBER;
130 p_insert_text VARCHAR2(80);
131 p_remainder_text VARCHAR2(2000);
132 i BINARY_INTEGER;
133 BEGIN
134 i := 0;
135 IF LENGTH(p_text) > MAX_LENGTH THEN
136 p_remainder_text := SUBSTR(p_text,1,MAX_LENGTH);
137 ELSE
138 p_remainder_text := p_text;
139 END IF;
140
141 LOOP
142 p_insert_text := SUBSTR(p_remainder_text,1,80);
143 p_remainder_text := SUBSTR(p_remainder_text,81);
144
145 IF p_debug_level = 0 THEN
146 Write_Line(p_insert_text, p_token);
147 ELSIF debug_flag = 'Y' AND p_debug_level >= debug_level THEN -- Bug 930902: Should be >= instead of <=
148 Write_Line(p_insert_text, p_token);
149 END IF;
150
151 EXIT WHEN p_remainder_text IS NULL;
152 i := i + 1;
153 EXIT WHEN i = 25;
154 END LOOP;
155
156 END Write_Log;
157
158 --
159 -- This function will fetch the table which has all the lines
160 -- added into the log file
161 -- Arguments:
162 -- p_log -> log structure (cannot be used from forms, currently)
163 --
164
165 PROCEDURE Get_Log (
166 p_log OUT logCharTyp
167 ) IS
168 i NUMBER;
169 BEGIN
170 IF log_buffer.COUNT > 0 THEN
171 FOR i IN 1..log_buffer.COUNT LOOP
172 log_char_buffer(i) := log_buffer(i).text_line;
173 END LOOP;
174 END IF;
175 p_log := log_char_buffer;
176
177 EXCEPTION
178 WHEN OTHERS THEN
179 Default_Handler('WSH_UTIL.Get_Log','Error in Get_Log');
180 END Get_Log;
181
182 --
183 -- This function gets the number of lines entered as log information
184 --
185
186 FUNCTION Get_Size RETURN NUMBER IS
187 BEGIN
188 RETURN log_buffer.COUNT;
189
190 EXCEPTION
191 WHEN OTHERS THEN
192 Default_Handler('WSH_UTIL.Get_Size','Error in Get_Size');
193 RETURN FAILURE;
194 END Get_Size;
195
196 --
197 -- This procedure gets a specific line form the log file specified by
198 -- the line number
199 -- Arguments:
200 -- p_line -> Line number in the log file, if you know it
201 --
202
203 PROCEDURE Get_Line (
204 p_line IN NUMBER,
205 p_text OUT VARCHAR2,
206 p_status OUT NUMBER
207 ) IS
208 BEGIN
209 IF (p_line > 0) AND (p_line < current_line) THEN
210 p_text := log_buffer(p_line).text_line;
211 p_status := SUCCESS;
212 ELSE
213 p_text := 'WSH_UTIL: Invalid Index ' || to_char(p_line);
214 p_status := FAILURE;
215 END IF;
216
217 EXCEPTION
218 WHEN OTHERS THEN
219 Default_Handler('WSH_UTIL.Get_Line','Error in Get_Line');
220 p_status := FAILURE;
221 END Get_Line;
222
223 --
224 -- This procedure gets a specific line form the log file specified by
225 -- the token
226 -- Arguments:
227 -- p_token -> Token associated with the line in the Log file
228 --
229
230 PROCEDURE Get_Line (
231 p_token IN VARCHAR2,
232 p_text OUT VARCHAR2,
233 p_status OUT NUMBER
234 ) IS
235 found NUMBER := 0;
236 i NUMBER;
237 BEGIN
238 FOR i IN 1..log_buffer.COUNT LOOP
239 IF log_buffer(i).token = p_token THEN
240 found := 1;
241 p_text := log_buffer(i).text_line;
242 p_status := SUCCESS;
243 END IF;
244 END LOOP;
245 IF found = 0 THEN
246 p_text := 'WSH_UTIL: Token (' || p_token || ') not found';
247 p_status := FAILURE;
248 END IF;
249
250 EXCEPTION
251 WHEN OTHERS THEN
252 Default_Handler('WSH_UTIL.Get_Line','Error in Get_Line');
253 p_status := FAILURE;
254 END Get_Line;
255
256 --
257 -- This function will clear the logged information for the session
258 --
259
260 FUNCTION Clear_Log RETURN NUMBER IS
261 BEGIN
262 log_buffer.DELETE(1,log_buffer.COUNT);
263 log_char_buffer.DELETE(1,log_char_buffer.COUNT);
264 current_line := 1;
265 RETURN SUCCESS;
266
267 EXCEPTION
268 WHEN OTHERS THEN
269 Default_Handler('WSH_UTIL.Clear_Log','Error in Clear_Log');
270 RETURN FAILURE;
271 END Clear_Log;
272
273 --
274 -- This function will flush the log information to a log file in the
275 -- server denoted by l<request_id>.log or l<time>.log. Your DBA can
276 -- assist you with the location of the log file.
277 --
278
279 FUNCTION Flush_Log (
280 p_close IN VARCHAR2
281 ) RETURN NUMBER IS
282 buffer VARCHAR2(80);
283 ret_code NUMBER;
284 i NUMBER;
285 BEGIN
286 IF flush = 'N' THEN
287 IF log_file_name IS NULL THEN
288 ret_code := Open_File;
289 IF ret_code = FAILURE THEN
290 RETURN FAILURE;
291 END IF;
292 END IF;
293 FOR i IN current_line..log_buffer.COUNT LOOP
294 buffer := log_buffer(i).text_line;
295 UTL_FILE.PUT_LINE(log_file_handle, buffer);
296 END LOOP;
297 last_flush_record := log_buffer.COUNT - 1;
298 IF p_close = 'Y' THEN
299 UTL_FILE.FCLOSE(log_file_handle);
300 END IF;
301 END IF;
302 RETURN SUCCESS;
303
304 EXCEPTION
305 WHEN OTHERS THEN
306 Default_Handler('WSH_UTIL.Flush_Log','Error in Flush_Log');
307 RETURN FAILURE;
308 END Flush_Log;
309
310 --
311 -- This is the generic server side exception handler for shipping.
312 -- It should be called in the WHEN OTHERS clause.
313 -- Arguments:
314 -- function_name -> package_name.proc/func_name
315 -- msg_txt -> useful debugging text for exception
316 --
317
318 PROCEDURE Default_Handler(
319 function_name IN VARCHAR2,
320 msg_txt IN VARCHAR2 DEFAULT ''
321 ) IS
322 theMessage VARCHAR2(2000);
323 tempMessage VARCHAR2(2000);
324 firstBlock VARCHAR2(2000);
325 nextBlock VARCHAR2(2000);
326 i BINARY_INTEGER;
327 BEGIN
328 i := 0;
329 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
330 FND_MESSAGE.Set_Token('PACKAGE',function_name);
331 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
332 FND_MESSAGE.Set_Token('ORA_TEXT',msg_txt);
333 IF Wshsrc = 'u' THEN
334 APP_EXCEPTION.Raise_Exception;
335 ELSE
336 theMessage := FND_MESSAGE.Get;
337 tempMessage := theMessage;
338
339 LOOP
340 firstBlock := SUBSTR(tempMessage,1,80);
341 nextBlock := SUBSTR(tempMessage,81);
342 log_buffer(current_line).text_line := firstBlock;
343 current_line := current_line + 1;
344 tempMessage := nextBlock;
345 EXIT WHEN tempMessage IS NULL;
346 i := i + 1;
347 EXIT WHEN i = 100;
348 END LOOP;
349
350 END IF;
351 END Default_Handler;
352
353
354 --
355 -- Internal access to variables for debugging purposes
356 --
357
358 FUNCTION Get_Var (
359 var_name IN VARCHAR2
360 ) RETURN VARCHAR2 IS
361 BEGIN
362 IF var_name = 'initialized' THEN RETURN initialized;
363 ELSIF var_name = 'current_line' THEN RETURN to_char(current_line);
364 ELSIF var_name = 'request_id' THEN RETURN to_char(request_id);
365 ELSIF var_name = 'debug_flag' THEN RETURN debug_flag;
366 ELSIF var_name = 'debug_level' THEN RETURN to_char(debug_level);
367 ELSIF var_name = 'flush' THEN RETURN flush;
368 ELSIF var_name = 'source' THEN RETURN source;
369 ELSE RETURN 'BAD TOKEN';
370 END IF;
371 END Get_Var;
372
373 PROCEDURE Set_Var (
374 var_name IN VARCHAR2,
375 var_val IN VARCHAR2
376 ) IS
377 BEGIN
378 IF var_name = 'initialized' THEN initialized := var_val;
379 ELSIF var_name = 'current_line' THEN current_line := to_number(var_val);
380 ELSIF var_name = 'request_id' THEN request_id := to_number(var_val);
381 ELSIF var_name = 'debug_flag' THEN debug_flag := var_val;
382 ELSIF var_name = 'debug_level' THEN debug_level := to_number(var_val);
383 ELSIF var_name = 'flush' THEN flush := var_val;
384 ELSIF var_name = 'source' THEN source := var_val;
385 END IF;
386 END Set_Var;
387
388
389 FUNCTION update_locator_flex( organization_id IN NUMBER,
390 locator_id IN NUMBER,
391 subinventory IN VARCHAR2)
392 RETURN BOOLEAN IS
393 CURSOR c1 ( x_org_id NUMBER, x_loc_id NUMBER, x_subinv VARCHAR2) IS
394 SELECT 'Exist'
395 FROM mtl_item_locations
396 WHERE organization_id = x_org_id
397 AND inventory_location_id = x_loc_id
398 AND subinventory_code IS NOT NULL
399 AND subinventory_code <> x_subinv;
400 temp VARCHAR2(10);
401 x_org_id NUMBER;
402 x_loc_id NUMBER;
403 BEGIN
404
405 OPEN c1( organization_id, locator_id, subinventory);
406 FETCH c1 INTO temp;
407
408 IF ( c1%FOUND) THEN
409 IF (c1%ISOPEN) THEN
410 CLOSE c1;
411 END IF;
412
413 RETURN FALSE;
414 END IF;
415
416 IF (c1%ISOPEN) THEN
417 CLOSE c1;
418 END IF;
419
420 x_org_id := organization_id;
421 x_loc_id := locator_id;
422
423 UPDATE mtl_item_locations a
424 SET a.subinventory_code = subinventory
425 WHERE a.organization_id = x_org_id
426 AND a.inventory_location_id = x_loc_id;
427
428 RETURN TRUE;
429
430 EXCEPTION
431 WHEN others THEN
432 Default_Handler('WSH_UTIL.update_locator_flex',SQLERRM);
433 END update_locator_flex;
434
435 FUNCTION sc_online( departure_id IN NUMBER,
436 delivery_id IN NUMBER,
437 so_reservations IN VARCHAR2) RETURN BOOLEAN IS
438 x_status VARCHAR2(30);
439 x_return_msg VARCHAR2(128);
440 x_return_val NUMBER;
441 x_check_failure VARCHAR2(50);
442 x_dummy VARCHAR2(50);
443 BEGIN
444 -- Call the TM to run Update Shipping Program
445
446 IF (delivery_id IS NULL and departure_id IS NULL)
447 OR
448 (delivery_id = 0 AND departure_id = 0 ) THEN
449 FND_MESSAGE.Set_Name('OE','WSH_UTL_INVALID_PARA');
450 return FALSE;
451 END IF;
452
453 x_return_val:= FND_TRANSACTION.Synchronous(1000,
454 x_status,
455 x_return_msg,
456 'OE',
457 'OEBSCO',
458 TO_CHAR(delivery_id),
459 TO_CHAR(departure_id),
460 so_reservations,
461 FND_PROFILE.VALUE('USER_ID'),
462 FND_PROFILE.VALUE('LOGIN_ID'),
463 TO_CHAR(0),
464 NULL);
465
466 IF (x_return_val = 2) THEN
467 FND_MESSAGE.Set_Name('OE','SHP_ONLINE_NO_MANAGER');
468 return FALSE;
469 ELSIF (x_return_val <> 0) THEN
470 FND_MESSAGE.Set_Name('OE','SHP_AOL_ONLINE_FAILED');
471 FND_MESSAGE.Set_Token('PROGRAM','Update Shipping Information');
472 return FALSE;
473 ELSE
474 IF (x_return_msg = 'FAILURE') THEN
475 x_return_val := FND_TRANSACTION.get_values(x_check_failure,
476 x_dummy,
477 x_dummy,
478 x_dummy,
479 x_dummy,
480 x_dummy,
481 x_dummy,
482 x_dummy,
483 x_dummy,
484 x_dummy,
485 x_dummy,
486 x_dummy,
487 x_dummy,
488 x_dummy,
489 x_dummy,
490 x_dummy,
491 x_dummy,
492 x_dummy,
493 x_dummy,
494 x_dummy);
495 IF (x_check_failure = 'SUCCESS') THEN
496 FND_MESSAGE.Set_Name('OE','WSH_INVENTORY_INTERFACE_FAILED');
497 RETURN FALSE;
498 ELSE
499 FND_MESSAGE.Set_Name('OE','WSH_UPDATE_SHIPPING_FAILED');
500 RETURN FALSE;
501 END IF;
502 ELSE
503 -- Transaction manager went through and successfully closed the pick slip
504 RETURN TRUE;
505 END IF;
506 END IF;
507 EXCEPTION
508
509 WHEN OTHERS THEN
513 FND_MESSAGE.Set_Token('ORA_TEXT','Unexpected exception');
510 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
511 FND_MESSAGE.Set_Token('PACKAGE','WSH_UTIL.sc_online');
512 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
514 RETURN FALSE;
515
516 END sc_online;
517
518 BEGIN
519
520 IF initialized = 'N' THEN
521 request_id := to_number(FND_PROFILE.VALUE('CONC_REQUEST_ID'));
522 IF request_id IS NULL THEN
523 source := 'u';
524 request_id := -1;
525 ELSE
526 source := 'c';
527 END IF;
528 debug_flag := FND_PROFILE.VALUE('SO_DEBUG');
529 IF debug_flag = 'Y' THEN
530 debug_level := to_number(FND_PROFILE.VALUE('OE_DEBUG_LEVEL'));
531 END IF;
532 initialized := 'Y';
533 END IF;
534
535 EXCEPTION
536 WHEN OTHERS THEN
537 FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
538 FND_MESSAGE.Set_Token('PACKAGE','WSH_UTIL');
539 FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
540 FND_MESSAGE.Set_Token('ORA_TEXT','Failure initializing package');
541 APP_EXCEPTION.Raise_Exception;
542
543 END WSH_UTIL;
[Home] [Help]