1 PACKAGE BODY ARP_MESSAGE as
2 /* $Header: ARHAMSGB.pls 120.6 2006/05/24 07:23:48 vsegu noship $ */
3 --
4 -- PURPOSE
5 -- Allow messages (both for reporting and debugging) to be written to
6 -- database table or to memory by PL/SQL programs executed on the server.
7 -- When program control is returned to the client, messages can be
8 -- retrieved and used in a report or a log file, etc.
9 --
10 --
11 -- HISTORY
12 --
13 -- 14-JAN-94 R Lee Created.
14 -- 23-FEB-94 R Lee Modified so that messages can be ouputed
15 -- to table AS_CONC_REQUEST_MESSAGES.
16 -- 27-FEB-94 R Lee Renamed Put_DB_Error to Set_Error
17 -- 28-FEB-94 R Lee Modified Initialize. The output buffers
18 -- (db tables or memory) of normal messages
19 -- and debugging messages can now be controlled
20 -- independently.
21 -- 17-MAR-94 R Lee Added Set_Line to replace Put_Line.
22 -- 31-MAR-94 R Lee Changed references to AS_REPORT_ENTRIES to
23 -- AS_CONC_REQUEST_MESSAGES.
24 -- 31-MAR-94 W Smith AR copies this package for its own use.
25 --
26 -- 10-FEB-97 V Ahluwalia When others then exception is raised into the
27 -- outer block in insert into
28 -- ar_conc_request_messages, as previous call
29 -- caused looping when table extents are full
30 -- 29-MAR-01 J Huang Bug 1706869: Modified procedure Insert_Row to
31 -- make it as AUTONOMOUS_TRANSACTION.
32 -- 02-14-02 Added one more procedure to get last few messages
33 --
34 -- 02-22-2002 Jyoti Pandey Moved all the logic from arplbmsg.sql
35 -- at version 115.5 for new GSCC standards
36 -- 12-04-02 Jyoti Pandey Added NOCOPY to Get procedure for GSCC warnings
37 -- 23-NOV-04 S V Sowjanya Bug 3871056: Added global varible
38 -- G_Account_Merge_Logging, Assigned value to this
39 -- variable in initialize procedure.
40 -- Added if condition in procedure Set_line to log
41 -- messages only if the profile HZ_ACCOUNT_MERGE_LOGGING
42 -- is set.
43 -- 27-APR-06 S V Sowjanya Bug 5010855. Replaced reference to arp_standard with
44 -- HZ_UTILITY_V2PUB
45 -- 24-APR-06 S V Sowjanya Bug 5239180. Replaced MM with MI in date conversion
46 /*------------------------------ DATA TYPES ---------------------------------*/
47 TYPE MESSAGE_TABLE_TYPE IS TABLE of
48 VARCHAR2(255)
49 INDEX BY BINARY_INTEGER;
50
51 TYPE CODE_TABLE_TYPE IS TABLE of
52 VARCHAR2(12)
53 INDEX BY BINARY_INTEGER;
54
55 TYPE DATE_TABLE_TYPE IS TABLE of
56 VARCHAR2(30)
57 INDEX BY BINARY_INTEGER;
58
59
60 /*---------------------------- PRIVATE VARIABLES ----------------------------*/
61
62 G_TMessage_Buffer MESSAGE_TABLE_TYPE; -- Message Stack
63 G_TEmpty_Msg_Buffer MESSAGE_TABLE_TYPE; -- Empty Stack used for
64 -- clearing memory
65 G_TMessage_Type CODE_TABLE_TYPE; -- Message Type Stack in sync.
66 -- with Message Stack
67 G_TEmpty_Type CODE_TABLE_TYPE; -- Emtpy Type Stack
68
69 G_TDate_Buffer DATE_TABLE_TYPE; -- Creation Date Stack
70 G_TEmpty_Date DATE_TABLE_TYPE; -- Empty Date Stack
71
72 G_Msg_Count NUMBER := 0; -- Num of Messages on stack
73 G_Msg_Ptr NUMBER := 1; -- Points to next Message
74 -- on stack to retreive.
75 G_Output_Code VARCHAR2(5) := 'STACK'; -- Determines whether messages
76 -- will be stored in a table
77 -- or in memory.
78 G_Debug_Flag VARCHAR2(1) := 'N'; -- Flag determines whether
79 -- debug mode is on or off.
80 G_Debug_Output_Code VARCHAR2(5) := 'STACK'; -- Determines how debugging
81 -- messages will be stored.
82 G_Ins_Message_Flag VARCHAR2(1) := 'N'; -- Flag to indicate whether
83 -- the current message needs
84 -- to be inserted into
85 -- AR_CONC_REQUEST_MESSAGES
86
87 G_User_Id NUMBER := FND_PROFILE.Value('USER_ID');
88 G_Conc_Request_Id NUMBER;
89 G_Account_Merge_Logging VARCHAR2(1);
90
91 /*--------------------------- PRIVATE ROUTINES ------------------------------*/
92
93 PROCEDURE Insert_Row(X_Message_Text VARCHAR2,
94 X_Message_Type VARCHAR2,
95 X_Error_Number NUMBER) IS
96 BEGIN
97 INSERT INTO ar_conc_request_messages(conc_request_message_id,
98 creation_date,
99 created_by,
100 request_id,
101 type,
102 error_number,
103 text)
104 VALUES(ar_conc_request_messages_s.nextval,
105 SYSDATE,
106 G_User_Id,
107 G_Conc_Request_Id,
108 X_Message_Type,
109 X_Error_Number,
110 X_Message_Text);
111 EXCEPTION
112 When OTHERS then
113 -- Set_Error('ARP_MESSAGE.Insert_Message');
114 RAISE ;
115 END;
116
117
118 PROCEDURE Insert_Message(Message_Text VARCHAR2,
119 Message_Type VARCHAR2,
120 Error_Number NUMBER) IS
121 BEGIN
122 if ( (G_Output_Code = 'TABLE')
123 and (G_Ins_Message_Flag = 'Y')) then
124 Insert_Row(G_TMessage_Buffer(G_Msg_Count),
125 G_TMessage_Type(G_Msg_Count), NULL);
126 G_Ins_Message_Flag := 'N';
127 end if;
128 Insert_Row(Message_Text, Message_Type, Error_Number);
129 END;
130
131 --
132 -- NAME
133 -- Set_Line
134 --
135 -- Writes message either to the message stack or AR_CONC_REQUEST_MESSAGES
136 --
137 --
138 PROCEDURE Set_Line(Message_Text IN VARCHAR2,
139 Message_Type IN VARCHAR2,
140 Error_Number IN NUMBER) IS
141 BEGIN
142
143 if ( ( (Message_Type = 'DEBUG')
144 and (G_Debug_Output_Code = 'TABLE'))
145 or ( (G_Output_Code = 'TABLE')
146 and (Message_Type <> 'DEBUG'))) then
147 Insert_Message(Message_Text, Message_Type, Error_Number);
148 else
149 G_Msg_Count := G_Msg_Count + 1;
150 G_TMessage_Buffer(G_Msg_Count) := substrb(Message_Text,1,255);
151 G_TMessage_Type(G_Msg_Count) := Message_Type;
152 G_TDate_Buffer(G_Msg_Count) := TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'); --bug 5239180 replaced MM with MI
153 end if;
154
155 END;
156
157
158 -- NAME
159 -- Decode_Message
160 --
161 -- PURPOSE
162 -- Parse an encoded VARCHAR2 string that stores the information
163 -- necessary to a call the client Message Dictionary API, and
164 -- returns the translated message.
165 --
166 -- The encoded message will be of the following format.
167 --
168 -- NOTES
169 -- o An arbitrary number of tokens are supported (unless variable
170 -- Temp_Message is exceeded).
171 -- o Token values may contain spaces and quotes.
172 -- o One or more spaces is used to delimit components of the
173 -- encoded message.
174 --
175
176 FUNCTION Decode_Message(Encoded_Message VARCHAR2) Return VARCHAR2 IS
177 Temp_Message VARCHAR2(500);
178 Appl_Short_Name VARCHAR2(3);
179 Translate BOOLEAN;
180 Temp_Buf VARCHAR2(30);
181 Token_Value VARCHAR2(50);
182 Translate_Arg VARCHAR2(10);
183 Pos1 NUMBER;
184 Pos2 NUMBER;
185 BEGIN
186
187 Temp_Message := Ltrim(Encoded_Message);
188 --
189 -- Extract the Application Short Name and Message Name
190 --
191 Pos1 := instrb(Temp_Message, ' ', 1);
192 Appl_Short_Name := substrb(Temp_Message, 1, Pos1 - 1);
193 Temp_Message := Ltrim(substrb(Temp_Message, Pos1 + 1));
194 Pos1 := instrb(Temp_Message, ' ', 1);
195 --
196 -- Store the Message name in variable Temp_Buf
197 --
198 if (Pos1 = 0) then
199 Temp_Buf := Temp_Message;
200 Temp_Message := NULL;
201 else
202 Temp_Buf := substrb(Temp_Message, 1, Pos1 - 1);
203 Temp_Message := Ltrim(substrb(Temp_Message, Pos1 + 1));
204 end if;
205 --
206 -- Set the Message Name
207 --
208
209 FND_MESSAGE.Set_Name(Appl_Short_Name, Temp_Buf);
210
211 --
212 -- Extract the token information if necessary.
213 --
214 if (Temp_Message is not NULL) then
215 LOOP
216 --
217 -- Store the token name in Temp_Buf
218 --
219 Pos1 := instrb(Temp_Message, ' ', 1);
220 Temp_Buf := substrb(Temp_Message, 1, Pos1 - 1);
221 --
222 -- Locate the Token Value Delimiters and extract the token value.
223 --
224 Pos1 := instrb(Temp_Message, '\"', 1);
225 Pos2 := instrb(Temp_Message, '\"', Pos1 + 2, 1);
226 Token_Value := substrb(Temp_Message, Pos1 + 2, Pos2 - Pos1 - 2);
227 Temp_Message := Ltrim(substrb(Temp_Message, Pos2 + 2));
228 Pos1 := instrb(Temp_Message, ' ', 1);
229 --
230 -- Pos1 will equal 0 when Temp_Message is NULL which means that
231 -- there are no more tokens to process.
232 --
233 if (Pos1 <> 0) then
234 Translate_Arg := Upper(substrb(Temp_Message, 1, Pos1 - 1));
235 Temp_Message := Ltrim(substrb(Temp_Message, Pos1 + 1));
236 else
237 Translate_Arg := Upper(Temp_Message);
238 Temp_Message := NULL;
239 end if;
240 if (Translate_Arg = 'TRUE') then
241 Translate := True;
242 elsif (Translate_Arg = 'FALSE') then
243 Translate := False;
244 end if;
245
246 FND_MESSAGE.Set_Token(Temp_Buf, Token_Value, Translate);
247
248 Exit when (Temp_Message is NULL);
249 end LOOP;
250 end if;
251
252 Temp_Message := FND_MESSAGE.Get;
253
254 Return(Temp_Message);
255
256 RETURN NULL;
257 EXCEPTION
258 When OTHERS then
259 null;
260
261 RETURN NULL;
262
263 END;
264
265
266 /*---------------------------- PUBLIC ROUTINES ------------------------------*/
267 --
268 -- NAME
269 -- Initialize
270 --
271 -- PURPOSE
272 -- Controls whether output is stored in a DB table or in memory via the
273 -- message stack and sets the REQUEST_ID of the concurrent program if
274 -- applicabale.
275 --
276 -- USAGE
277 -- When Output_Code = 'TABLE' messages will be inserted into
278 -- AR_CONC_REQUEST_MESSAGES.
279 --
280 -- When Output_Code = 'STACK' messages will be written to memory on
281 -- the message stack.
282 --
283 -- Same logic applies to Debug_Output_Code
284 --
285 -- When Debug_Flag = 'Y', debug mode is turned on.
286 --
287 -- HISTORY
288 -- 14-FEB-94 R Lee Created.
289 -- 28-FEB-94 R Lee Added argument Debug_Output_Code
290 -- 31-MAR-94 R Lee Added argument Debug_Flag
291 --
292 PROCEDURE Initialize(Output_Code VARCHAR2,
293 Conc_Request_Id NUMBER,
294 Debug_Flag VARCHAR2 ,
295 Debug_Output_Code VARCHAR2 ) IS
296 BEGIN
297 G_Conc_Request_Id := Conc_Request_Id;
298 G_User_Id := HZ_UTILITY_V2PUB.user_id; --arp_standard.profile.user_id;
299 G_Account_Merge_Logging := fnd_profile.value('HZ_ACCOUNT_MERGE_LOGGING');
300 G_Debug_Flag := Debug_Flag;
301 if (Upper(Debug_Output_Code) IN ('STACK', 'TABLE')) then
302 G_Debug_Output_Code := Debug_Output_Code;
303 else
304 G_Debug_Output_Code := 'STACK';
305 end if;
306 if (Upper(Output_Code) IN ('STACK', 'TABLE')) then
307 G_Output_Code := Output_Code;
308 else
309 --
310 -- Hardcoded message for the moment
311 --
312 Set_Line('Invalid Argument to Set_Output. ' ||
313 'Messages will be written to memory by default.');
314 G_Output_Code := 'STACK';
315 end if;
316 END;
317
318
319 --
320 -- NAME
321 -- Flush
322 --
323 -- PURPOSE
324 -- Ensures that all messages on the message stack that need to be
325 -- inserted into AR_CONC_REQUEST_MESSAGES are inserted.
326 --
327 --
328 PROCEDURE Flush IS
329 BEGIN
330 if ( (G_Output_Code = 'TABLE')
331 and (G_Ins_Message_Flag = 'Y')) then
332 Insert_Row(G_TMessage_Buffer(G_Msg_Count),
333 G_TMessage_Type(G_Msg_Count),
334 NULL);
335 G_Ins_Message_Flag := 'N';
336 end if;
337 END;
338
339
340 --
341 -- NAME
342 -- Put_Line
343 --
344 -- NOTES
345 -- Temporary function for backwards compatibility.
346 -- Use Set_Error instead.
347 --
348 PROCEDURE Put_Line(Message_Text IN VARCHAR2) IS
349 BEGIN
350 Set_Line(Message_Text, 'NO_TRANSLATE', NULL);
351 END;
352
353
354 --
355 -- NAME
356 -- Set_Line
357 --
358 -- PURPOSE
359 -- Puts a char string to Message Stack
360 --
361 PROCEDURE Set_Line(Message_Text IN VARCHAR2) IS
362 BEGIN
363 IF NVL(G_Account_Merge_Logging,'N') = 'Y' THEN
364 Set_Line(Message_Text, 'NO_TRANSLATE', NULL);
365 END IF;
366 END;
367
368
369 --
370 -- NAME
371 -- Set_Name
372 --
373 -- PURPOSE
374 -- Puts an "encoded" message name on the Message Stack
375 --
376 PROCEDURE Set_Name(Appl_Short_Name IN VARCHAR2,
377 Message_Name IN VARCHAR2) IS
378 BEGIN
379 if ( (G_Output_Code = 'TABLE')
380 and (G_Ins_Message_Flag = 'Y')) then
381 -- if (G_Ins_Message_Flag = 'Y') then
382 Insert_Row(G_TMessage_Buffer(G_Msg_Count),
383 G_TMessage_Type(G_Msg_Count),
384 NULL);
385 end if;
386 G_Msg_Count := G_Msg_Count + 1;
387 G_TMessage_Buffer(G_Msg_Count) := Appl_Short_Name || ' ' || Message_Name;
388 G_TMessage_Type(G_Msg_Count) := 'TRANSLATE';
389 G_TDate_Buffer(G_Msg_Count) := TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS'); --bug 5239180 replaced MM with MI
390 G_Ins_Message_Flag := 'Y';
391 END;
392
393
394 --
395 -- NAME
396 -- DEBUG
397 --
398 -- PURPOSE
399 -- Writes a debugging message to the Message Stack only if
400 -- the profile option value for AR_DEBUG = 'Y'.
401
402 PROCEDURE Debug(Message_Text IN VARCHAR2) IS
403 BEGIN
404 if (G_Debug_Flag = 'Y') then
405 Set_Line(Message_Text, 'DEBUG', NULL);
406 end if;
407 END;
408
409
410 --
411 -- NAME
412 -- Set_Token
413 --
414 -- PURPOSE
415 -- Add Token Information to the current message on the stack.
416 -- The current message must be of type 'TRANSLATE' for this
417 -- to work properly when the message is translated on the client,
418 -- although no serious errors will occur.
419 --
420 PROCEDURE Set_Token(Token_Name IN VARCHAR2,
421 Token_Value IN VARCHAR2,
422 Translate IN BOOLEAN ) IS
423 Trans_Label VARCHAR2(5);
424 BEGIN
425 if (Translate) then
426 Trans_Label := 'TRUE';
427 else
428 Trans_Label := 'FALSE';
429 end if;
430 G_TMessage_Buffer(G_Msg_Count)
431 := G_TMessage_Buffer(G_Msg_Count) || ' ' ||
432 Token_Name || ' \"' ||
433 Token_Value || '\" ' || Trans_Label;
434 END;
435
436 PROCEDURE Get(Message_Buf OUT NOCOPY VARCHAR2,
437 Message_Type OUT NOCOPY VARCHAR2,
438 Status OUT NOCOPY NUMBER) IS
439 BEGIN
440 if ( (G_Msg_Ptr > G_Msg_Count)
441 or (G_Msg_Count = 0)) then
442 Status := 0;
443 Clear;
444 else
445 Message_Buf := G_TMessage_Buffer(G_Msg_Ptr);
446 Message_Type := G_TMessage_Type(G_Msg_Ptr);
447 Status := LengthB(G_TMessage_Buffer(G_Msg_Ptr));
448 G_Msg_Ptr := G_Msg_Ptr + 1;
449 end if;
450 END;
451
452 PROCEDURE Get(Message_Buf OUT NOCOPY VARCHAR2,
453 Message_Type OUT NOCOPY VARCHAR2,
454 Creation_Date OUT NOCOPY VARCHAR2,
455 Status OUT NOCOPY NUMBER) IS
456 BEGIN
457 if ( (G_Msg_Ptr > G_Msg_Count)
458 or (G_Msg_Count = 0)) then
459 Status := 0;
460 Clear;
461 else
462 Message_Buf := G_TMessage_Buffer(G_Msg_Ptr);
463 Message_Type := G_TMessage_Type(G_Msg_Ptr);
464 Creation_Date := G_TDate_Buffer(G_Msg_Ptr);
465 Status := LengthB(G_TMessage_Buffer(G_Msg_Ptr));
466 G_Msg_Ptr := G_Msg_Ptr + 1;
467 end if;
468 END;
469
470 --
471 -- NAME
472 -- Clear
473 --
474 -- PURPOSE
475 -- Frees memory used the the Message Stack and resets the
476 -- the Message Stack counter and pointer variables.
477 --
478 PROCEDURE Clear IS
479 BEGIN
480 G_TMessage_Buffer.Delete;
481 G_TMessage_Type.Delete;
482 G_TDate_Buffer.Delete;
483
484 -- G_TMessage_Buffer := G_TEmpty_Msg_Buffer;
485 -- G_TMessage_Type := G_TEmpty_Type;
486 -- G_TDate_Buffer := G_TEmpty_Date;
487 G_Msg_Count := 0;
488 G_Msg_Ptr := 1;
489 END;
490
491
492 --
493 -- Temporary function for backwards compatibility.
494 -- Use Set_Error instead.
495 --
496 PROCEDURE Put_DB_Error(Routine IN VARCHAR2 ,
497 Context IN VARCHAR2 ) IS
498 BEGIN
499 Set_Error(Routine, Context);
500 END;
501
502 PROCEDURE Set_Error(Routine IN VARCHAR2 ,
503 Context IN VARCHAR2 ) IS
504 Delimiter1 VARCHAR2(3);
505 Delimiter2 VARCHAR2(3);
506 BEGIN
507 if (Routine is not NULL) then
508 Delimiter1 := ' : ';
509 end if;
510 if (Context is not NULL) then
511 Delimiter2 := ' : ';
512 end if;
513 Set_Line(Routine||Delimiter1||Context||Delimiter2||SQLERRM);
514 END;
515
516 FUNCTION Message_Count Return NUMBER IS
517 BEGIN
518 Return(G_Msg_Count);
519 END;
520
521 PROCEDURE Purge_Messages(X_Request_Id IN NUMBER) IS
522 BEGIN
523 DELETE from ar_conc_request_messages
524 WHERE request_id = X_Request_Id;
525 END;
526
527 FUNCTION Last_Message_Sequence Return NUMBER IS
528 CURSOR C_Last_Seq IS
529 SELECT max(conc_request_message_id)
530 FROM ar_conc_request_messages
531 WHERE request_id = G_Conc_Request_Id;
532
533 Last_Msg_Seq NUMBER;
534
535 BEGIN
536 OPEN C_Last_Seq;
537 FETCH C_Last_Seq INTO Last_Msg_Seq;
538 CLOSE C_Last_Seq;
539 return(Last_Msg_Seq);
540
541 EXCEPTION
542 When OTHERS then
543 ARP_MESSAGE.Set_Error('Last_Message_Sequence');
544 Raise;
545 END;
546
547 FUNCTION Get_Last_Few_Messages(num IN NUMBER)
548 RETURN VARCHAR2 IS
549
550 buf VARCHAR2(2000);
551 l_message_type VARCHAR2(12);
552 l_count number;
553
554 BEGIN
555 if ( (G_Msg_Ptr > G_Msg_Count)
556 or (G_Msg_Count = 0)) then
557 Clear;
558 else
559
560 if G_Msg_Count < num then
561 l_count := G_Msg_Count;
562 else
563 l_count := num;
564 end if;
565
566 for I in 1..l_count LOOP
567 l_message_type := G_TMessage_Type(G_Msg_Count-l_count+i);
568
569 if l_message_type = 'TRANSLATE' then
570 buf := buf ||fnd_global.local_chr(10)||decode_message(G_TMessage_Buffer( (G_Msg_Count-l_count+i)) );
571 else
572 buf := buf ||fnd_global.local_chr(10)||G_TMessage_Buffer( (G_Msg_Count-l_count+i));
573 end if;
574
575 END LOOP;
576 end if;
577
578 RETURN BUF;
579
580 EXCEPTION
581 WHEN NO_DATA_FOUND THEN
582 NULL;
583 END;
584
585
586 END ARP_MESSAGE;
[Home] [Help]