1 PACKAGE OE_MSG_PUB AUTHID CURRENT_USER AS
2 /* $Header: OEXUMSGS.pls 120.7.12020000.1 2012/06/26 09:39:46 appldev ship $ */
3
4 -- Global constants used by the Get function/procedure to
5 -- determine which message to get.
6
7 G_FIRST CONSTANT NUMBER := -1 ;
8 G_NEXT CONSTANT NUMBER := -2 ;
9 G_LAST CONSTANT NUMBER := -3 ;
10 G_PREVIOUS CONSTANT NUMBER := -4 ;
11
12 -- global that holds the value of the message level profile option.
13
14 G_msg_level_threshold NUMBER := FND_API.G_MISS_NUM;
15
16 --message context record group
17 TYPE G_MSG_CONTEXT_REC_TYPE IS RECORD
18 (ENTITY_CODE VARCHAR2(30)
19 ,ENTITY_REF VARCHAR2(50)
20 ,ENTITY_ID NUMBER
21 ,HEADER_ID NUMBER
22 ,LINE_ID NUMBER
23 ,ORDER_SOURCE_ID NUMBER
24 ,ORIG_SYS_DOCUMENT_REF VARCHAR2(50)
25 ,ORIG_SYS_DOCUMENT_LINE_REF VARCHAR2(50)
26 ,ORIG_SYS_SHIPMENT_REF VARCHAR2(50)
27 ,CHANGE_SEQUENCE VARCHAR2(50)
28 ,SOURCE_DOCUMENT_TYPE_ID NUMBER
29 ,SOURCE_DOCUMENT_ID NUMBER
30 ,SOURCE_DOCUMENT_LINE_ID NUMBER
31 ,ATTRIBUTE_CODE VARCHAR2(30)
32 ,CONSTRAINT_ID NUMBER
33 ,PROCESS_ACTIVITY NUMBER
34 ,ORDER_NUMBER NUMBER
35 );
36
37 TYPE Msg_Context_Tbl_Type IS TABLE OF G_MSG_CONTEXT_REC_TYPE
38 INDEX BY BINARY_INTEGER;
39
40 G_msg_context_tbl Msg_Context_Tbl_Type;
41 G_msg_context_count NUMBER := 0;
42 G_msg_context_index NUMBER := 0;
43
44
45 -- API message record type
46 TYPE G_MSG_REC_TYPE IS RECORD
47 ( MESSAGE Varchar2(2000)
48 ,ENTITY_CODE VARCHAR2(30)
49 ,ENTITY_ID NUMBER
50 ,HEADER_ID NUMBER
51 ,LINE_ID NUMBER
52 ,ORDER_SOURCE_ID NUMBER
53 ,ORIG_SYS_DOCUMENT_REF VARCHAR2(50)
54 ,ORIG_SYS_DOCUMENT_LINE_REF VARCHAR2(50)
55 ,SOURCE_DOCUMENT_TYPE_ID NUMBER
56 ,SOURCE_DOCUMENT_ID NUMBER
57 ,SOURCE_DOCUMENT_LINE_ID NUMBER
58 ,ATTRIBUTE_CODE VARCHAR2(30)
59 ,CONSTRAINT_ID NUMBER
60 ,PROCESS_ACTIVITY NUMBER
61 ,NOTIFICATION_FLAG VARCHAR2(1)
62 ,MESSAGE_TEXT Varchar2(2000)
63 ,TYPE Varchar2(30)
64 ,ENTITY_REF VARCHAR2(50)
65 ,ORIG_SYS_SHIPMENT_REF VARCHAR2(50)
66 ,CHANGE_SEQUENCE VARCHAR2(50)
67 ,PROCESSED VARCHAR2(1)
68 ,ORG_ID NUMBER
69 );
70
71
72 -- API message table type
73 --
74 -- PL/SQL table of VARCHAR2(2000)
75 -- This is the datatype of the API message list
76
77 TYPE Msg_Tbl_Type IS TABLE OF G_MSG_REC_TYPE
78 INDEX BY BINARY_INTEGER;
79
80 -- Global message table variable.
81 -- this variable is global to the OE_MSG_PUB package only.
82 G_msg_tbl Msg_Tbl_Type;
83
84 -- Global variable holding the message count.
85 G_msg_count NUMBER := 0;
86
87 -- Index used by the Get function to keep track of the last fetched
88 -- message.
89 G_msg_index NUMBER := 0;
90
91 -- Global variable holding the process_activity values.
92 G_process_activity NUMBER := NULL;
93
94 -----------------------------------------------------------------
95 -- Procedure Initialize
96 --
97 -- Usage Used by API callers and developers to intialize the
98 -- global message table.
99 -- Desc Clears the G_msg_tbl and resets all its global
100 -- variables. Except for the message level threshold.
101 --
102
103 PROCEDURE Initialize;
104
105 -- FUNCTION Count_Msg
106 --
107 -- Usage Used by API callers and developers to find the count
108 -- of messages in the message list.
109 -- Desc Returns the value of G_msg_count
110 --
111 -- Parameters None
112 --
113 -- Return NUMBER
114
115 FUNCTION Count_Msg RETURN NUMBER;
116
117 PROCEDURE Set_Process_Activity(
118 p_process_activity IN NUMBER DEFAULT NULL);
119
120
121 PROCEDURE Set_Msg_Context (
122 p_entity_code IN VARCHAR2 DEFAULT NULL
123 ,p_entity_ref IN VARCHAR2 DEFAULT NULL
124 ,p_entity_id IN NUMBER DEFAULT NULL
125 ,p_header_id IN NUMBER DEFAULT NULL
126 ,p_line_id IN NUMBER DEFAULT NULL
127 ,p_order_source_id IN NUMBER DEFAULT NULL
128 ,p_orig_sys_document_ref IN VARCHAR2 DEFAULT NULL
129 ,p_orig_sys_document_line_ref IN VARCHAR2 DEFAULT NULL
130 ,p_orig_sys_shipment_ref IN VARCHAR2 DEFAULT NULL
131 ,p_change_sequence IN VARCHAR2 DEFAULT NULL
132 ,p_source_document_type_id IN NUMBER DEFAULT NULL
133 ,p_source_document_id IN NUMBER DEFAULT NULL
134 ,p_source_document_line_id IN NUMBER DEFAULT NULL
135 ,p_attribute_code IN VARCHAR2 DEFAULT NULL
136 ,p_constraint_id IN NUMBER DEFAULT NULL
137 -- ,p_process_activity IN NUMBER DEFAULT NULL
138 );
139
140 PROCEDURE Reset_Msg_Context(p_entity_code IN VARCHAR2);
141
142 PROCEDURE Update_Msg_Context (
143 p_entity_code IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
144 ,p_entity_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
145 ,p_header_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
146 ,p_line_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
147 ,p_order_source_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
148 ,p_orig_sys_document_ref IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
149 ,p_orig_sys_document_line_ref IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
150 ,p_orig_sys_shipment_ref IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
151 ,p_change_sequence IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
152 ,p_source_document_type_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
153 ,p_source_document_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
154 ,p_source_document_line_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
155 ,p_attribute_code IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
156 ,p_constraint_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
157 -- ,p_process_activity IN NUMBER DEFAULT FND_API.G_MISS_NUM
158 );
159
160 procedure get_msg_context(
161 p_msg_index IN NUMBER
162 ,x_entity_code OUT NOCOPY VARCHAR2
163
164 ,x_entity_ref OUT NOCOPY VARCHAR2
165
166 ,x_entity_id OUT NOCOPY NUMBER
167
168 ,x_header_id OUT NOCOPY NUMBER
169
170 ,x_line_id OUT NOCOPY NUMBER
171
172 ,x_order_source_id OUT NOCOPY NUMBER
173
174 ,x_orig_sys_document_ref OUT NOCOPY VARCHAR2
175
176 ,x_orig_sys_line_ref OUT NOCOPY VARCHAR2
177
178 ,x_orig_sys_shipment_ref OUT NOCOPY VARCHAR2
179
180 ,x_change_sequence OUT NOCOPY VARCHAR2
181
182 ,x_source_document_type_id OUT NOCOPY NUMBER
183
184 ,x_source_document_id OUT NOCOPY NUMBER
185
186 ,x_source_document_line_id OUT NOCOPY NUMBER
187
188 ,x_attribute_code OUT NOCOPY VARCHAR2
189
190 ,x_constraint_id OUT NOCOPY NUMBER
191
192 ,x_process_activity OUT NOCOPY NUMBER
193
194 ,x_notification_flag OUT NOCOPY VARCHAR2
195
196 ,x_type OUT NOCOPY VARCHAR2
197
198 );
199
200 -- PROCEDURE Count_And_Get
201 --
202 -- Usage Used by API developers to find the count of messages
203 -- in the message table. If there is only one message in
204 -- the table it retrieves this message.
205 --
206 -- Desc This procedure is a cover that calls the function
207 -- Count_Msg and if the count of messages is 1. It calls the
208 -- procedure Get. It serves as a shortcut for API
209 -- developers. to make one call instead of making a call
210 -- to count, a check, and then another call to get.
211 --
212 -- Parameters p_encoded IN VARCHAR2(1) := FND_API.G_TRUE Optional
213 -- If TRUE the message is returned in an encoded
214 -- format, else it is translated and returned.
215 -- p_count OUT NUMBER
216 -- Message count.
217 -- p_data OUT VARCHAR2(2000)
218 -- Message data.
219 --
220
221 PROCEDURE Count_And_Get
222 ( p_encoded IN VARCHAR2 := FND_API.G_TRUE ,
223 p_count OUT NOCOPY NUMBER ,
224
225 p_data OUT NOCOPY VARCHAR2
226
227 );
228
229
230 -- PROCEDURE Add
231 --
232 -- Usage Used to add messages to the global message table.
233 --
234 -- Desc Reads a message off the message dictionary stack and
235 -- writes it in an encoded format to the global PL/SQL
236 -- message table.
237 -- The message is appended at the bottom of the message
238 -- table.
239
240 PROCEDURE Add(p_context_flag IN VARCHAR2 DEFAULT 'Y');
241
242
243 -- PROCEDURE Add
244 --
245 -- Usage Used by Devlopers to add messages to Global stack from FND
246 -- stack .
247 --
248 -- Desc Accepts the message as input and writes to global_PL/SQL
249 -- message table.
250 -- The message is appended at the bottom of the message
251 -- table.
252 --
253 PROCEDURE Add_Text(p_message_text IN VARCHAR2
254 ,p_type IN VARCHAR2 DEFAULT 'ERROR'
255 ,p_context_flag IN VARCHAR2 DEFAULT 'Y');
256
257
258 -- PROCEDURE Delete_Msg
259 --
260 -- Usage Used to delete a specific message from the message
261 -- list, or clear the whole message list.
262 --
263 -- Desc If instructed to delete a specific message, the
264 -- message is removed from the message table and the
265 -- table is compressed by moving the messages coming
266 -- after the deleted messages up one entry in the message
267 -- table.
268 -- If there is no entry found the Delete procedure does
269 -- nothing, and no exception is raised.
270 -- If delete is passed no parameters it deletes the whole
271 -- message table.
272 --
273 -- Prameters p_msg_index IN NUMBER := FND_API.G_MISS_NUM Optional
274 -- holds the index of the message to be deleted.
275 --
276
277 PROCEDURE Delete_Msg
278 ( p_msg_index IN NUMBER := NULL
279 );
280
281 -- PROCEDURE Get
282 --
283 -- Usage Used to get message info from the global message table.
284 --
285 -- Desc Gets the next message from the message table.
286 -- This procedure utilizes the G_msg_index to keep track
287 -- of the last message fetched from the global table and
288 -- then fetches the next.
289 --
290 -- Parameters p_msg_index IN NUMBER := G_NEXT
291 -- Index of message to be fetched. the default is to
292 -- fetch the next message starting by the first
293 -- message. Possible values are :
294 --
295 -- G_FIRST
296 -- G_NEXT
297 -- G_LAST
298 -- G_PREVIOUS
299 -- Specific message index.
300 --
301 -- p_encoded IN VARCHAR2(1) := G_TRUE Optional
302 -- When set to TRUE retieves the message in an
303 -- encoded format. If FALSE, the function calls the
304 -- message dictionary utilities to translate the
305 -- message and do the token substitution, the message
306 -- text is then returned.
307 --
308 -- p_msg_data OUT VARCHAR2(2000)
309 -- p_msg_index_out OUT NUMBER
310
311 PROCEDURE Get
312 ( p_msg_index IN NUMBER := G_NEXT ,
313 p_encoded IN VARCHAR2 := FND_API.G_TRUE ,
314 p_data OUT NOCOPY VARCHAR2 ,
315
316 p_msg_index_out OUT NOCOPY NUMBER
317
318 );
319
320 -- FUNCTION Get
321 --
322 -- Usage Used to get message info from the message table.
323 --
324 -- Desc Gets the next message from the message table.
325 -- This procedure utilizes the G_msg_index to keep track
326 -- of the last message fetched from the table and
327 -- then fetches the next or previous message depending on
328 -- the mode the function is being called in..
329 --
330 -- Parameters p_msg_index IN NUMBER := G_NEXT
331 -- Index of message to be fetched. the default is to
332 -- fetch the next message starting by the first
333 -- message. Possible values are :
334 --
335 -- G_FIRST
336 -- G_NEXT
337 -- G_LAST
338 -- G_PREVIOUS
339 -- Specific message index.
340 --
341 -- p_encoded IN VARCHAR2(1) := FND_API.G_TRUE Optional
342 -- When set to TRUE Get retrieves the message in an
343 -- encoded format. If FALSE, the function calls the
344 -- message dictionary utilities to translate the
345 -- message and do the token substitution, the message
346 -- text is then returned.
347 --
348 -- Return VARCHAR2(2000) message data.
352 -- procedure Get that performs the exact same function as
349 -- If there are no more messages it returns NULL.
350 --
351 -- Notes The function name Get is overloaded with another
353 -- the function, the only difference is that the
354 -- procedure returns the message data as well as its
355 -- index i the message list.
356
357 FUNCTION Get
358 ( p_msg_index IN NUMBER := G_NEXT ,
359 p_encoded IN VARCHAR2 := FND_API.G_TRUE
360 )
361 RETURN VARCHAR2;
362
363 PROCEDURE Reset
364 ( p_mode IN NUMBER := G_FIRST );
365
366 -- Pre-defined API message levels
367 --
368 -- Valid values for message levels are from 1-50.
369 -- 1 being least severe and 50 highest.
370 --
371 -- The pre-defined levels correspond to standard API
372 -- return status. Debug levels are used to control the amount of
373 -- debug information a program writes to the PL/SQL message table.
374
375 G_MSG_LVL_UNEXP_ERROR CONSTANT NUMBER := 60;
376 G_MSG_LVL_ERROR CONSTANT NUMBER := 50;
377 G_MSG_LVL_SUCCESS CONSTANT NUMBER := 40;
378 G_MSG_LVL_DEBUG_HIGH CONSTANT NUMBER := 30;
379 G_MSG_LVL_DEBUG_MEDIUM CONSTANT NUMBER := 20;
380 G_MSG_LVL_DEBUG_LOW CONSTANT NUMBER := 10;
381
382 -- FUNCTION Check_Msg_Level
383 --
384 -- Usage Used by API developers to check if the level of the
385 -- message they want to write to the message table is
386 -- higher or equal to the message level threshold or not.
387 -- If the function returns TRUE the developer should go
388 -- ahead and write the message to the message table else
389 -- he/she should skip writing this message.
390 -- Desc Accepts a message level as input fetches the value of
391 -- the message threshold profile option and compares it
392 -- to the input level.
393 -- Return TRUE if the level is equal to or higher than the
394 -- threshold. Otherwise, it returns FALSE.
395 --
396
397 FUNCTION Check_Msg_Level
398 ( p_message_level IN NUMBER := G_MSG_LVL_SUCCESS
399 )
400 RETURN BOOLEAN;
401
402
403 -- PROCEDURE Build_Exc_Msg()
404 --
405 -- USAGE Used by APIs to issue a standard message when
406 -- encountering an unexpected error.
407 -- Desc The IN parameters are used as tokens to a standard
408 -- message 'FND_API_UNEXP_ERROR'.
409 -- Parameters p_pkg_name IN VARCHAR2 Optional
410 -- p_procedure_name IN VARCHAR2 Optional
411 -- p_error_text IN VARCHAR2(240) Optional
412 -- If p_error_text is missing SQLERRM is used.
413
414 PROCEDURE Build_Exc_Msg
415 ( p_pkg_name IN VARCHAR2 :=FND_API.G_MISS_CHAR ,
416 p_procedure_name IN VARCHAR2 :=FND_API.G_MISS_CHAR ,
417 p_error_text IN VARCHAR2 :=FND_API.G_MISS_CHAR
418 );
419
420
421 -- PROCEDURE Add_Exc_Msg()
422 --
423 -- USAGE Same as Build_Exc_Msg but in addition to constructing
424 -- the messages the procedure Adds it to the global
425 -- mesage table.
426
427 PROCEDURE Add_Exc_Msg
428 ( p_pkg_name IN VARCHAR2 :=FND_API.G_MISS_CHAR ,
429 p_procedure_name IN VARCHAR2 :=FND_API.G_MISS_CHAR ,
430 p_error_text IN VARCHAR2 :=FND_API.G_MISS_CHAR ,
431 p_context_flag IN VARCHAR2 DEFAULT 'Y'
432 );
433
434 -- PROCEDURE Dump_Msg and Dump_List are used for debugging purposes.
435 --
436
437 PROCEDURE Dump_Msg
438 ( p_msg_index IN NUMBER );
439
440 PROCEDURE Dump_List
441 ( p_messages IN BOOLEAN := FALSE );
442
443
444 -- PROCEDURE Save_Messages takes all the messages from the
445 -- message stack and inserts them into the OE_PROCESSING_MESSAGES table.
446
447 PROCEDURE Save_Messages(p_request_id IN NUMBER
448 ,p_message_source_code IN VARCHAR2 DEFAULT 'C');
449
450 PROCEDURE Insert_Message
451 ( p_msg_index IN NUMBER,
452 p_request_id IN NUMBER,
453 p_message_source_code IN VARCHAR2);
454
455
456 PROCEDURE Get_Msg_tbl(x_msg_tbl IN OUT NOCOPY /* file.sql.39 change */ msg_tbl_type);
457
458 PROCEDURE Populate_Msg_tbl(p_msg_tbl IN msg_tbl_type); --Added for bug 4716444
459
460 PROCEDURE Save_UI_Messages(p_request_id IN NUMBER
461 ,p_message_source_code IN VARCHAR2);
462
463 PROCEDURE Update_Notification_Flag(p_transaction_id IN NUMBER);
464
465 PROCEDURE Update_UI_Notification_Flag(p_msg_ind IN NUMBER);
466
467 FUNCTION Get_Single_message
468 (
469 x_return_status OUT NOCOPY VARCHAR2
470
471 )
472 RETURN VARCHAR2;
473
474 PROCEDURE DELETE_MESSAGE
475 (p_message_source_code IN VARCHAR2 DEFAULT NULL
476 ,p_request_id_from IN NUMBER DEFAULT NULL
477 ,p_request_id_to IN NUMBER DEFAULT NULL
478 ,p_order_number_from IN NUMBER DEFAULT NULL
479 ,p_order_number_to IN NUMBER DEFAULT NULL
480 ,p_creation_date_from IN VARCHAR2 DEFAULT NULL -- 5121760 Datatype changed from date to varchar2
481 ,p_creation_date_to IN VARCHAR2 DEFAULT NULL --5121760
482 ,p_program_id IN NUMBER DEFAULT NULL
483 ,p_process_activity_name IN VARCHAR2 DEFAULT NULL
484 ,p_order_type_id IN NUMBER DEFAULT NULL
485 ,p_attribute_code IN VARCHAR2 DEFAULT NULL
486 ,p_organization_id IN NUMBER DEFAULT NULL
487 ,p_created_by IN NUMBER DEFAULT NULL);
488
489 PROCEDURE DELETE_OI_MESSAGE
490 (p_request_id IN NUMBER DEFAULT NULL
491 ,p_order_source_id IN NUMBER DEFAULT NULL
492 ,p_orig_sys_document_ref IN VARCHAR2 DEFAULT NULL
493 ,p_change_sequence IN VARCHAR2 DEFAULT NULL
494 ,p_orig_sys_document_line_ref IN VARCHAR2 DEFAULT NULL
495 ,p_orig_sys_shipment_ref IN VARCHAR2 DEFAULT NULL
496 ,p_entity_code IN VARCHAR2 DEFAULT NULL
497 ,p_entity_ref IN VARCHAR2 DEFAULT NULL
498 ,p_org_id IN NUMBER DEFAULT NULL);
499
500 -- 4171408 - Added parameter p_type.
501 PROCEDURE Transfer_Msg_Stack
502 ( p_msg_index IN NUMBER DEFAULT NULL,
503 p_type IN VARCHAR2 DEFAULT NULL);
504
505 PROCEDURE Save_API_Messages (p_request_id IN NUMBER DEFAULT NULL
506 ,p_message_source_code IN VARCHAR2 DEFAULT 'A');
507
508 PROCEDURE Update_status_code(
509 p_request_id IN NUMBER DEFAULT NULL
510 ,p_org_id IN NUMBER DEFAULT NULL
511 ,p_entity_code IN VARCHAR2 DEFAULT NULL
512 ,p_entity_id IN NUMBER DEFAULT NULL
513 ,p_header_id IN NUMBER DEFAULT NULL
514 ,p_line_id IN NUMBER DEFAULT NULL
515 ,p_order_source_id IN NUMBER DEFAULT NULL
516 ,p_orig_sys_document_ref IN VARCHAR2 DEFAULT NULL
517 ,p_orig_sys_document_line_ref IN VARCHAR2 DEFAULT NULL
518 ,p_orig_sys_shipment_ref IN VARCHAR2 DEFAULT NULL
519 ,p_change_sequence IN VARCHAR2 DEFAULT NULL
520 ,p_source_document_type_id IN NUMBER DEFAULT NULL
521 ,p_source_document_id IN NUMBER DEFAULT NULL
522 ,p_source_document_line_id IN NUMBER DEFAULT NULL
523 ,p_attribute_code IN VARCHAR2 DEFAULT NULL
524 ,p_constraint_id IN NUMBER DEFAULT NULL
525 ,p_process_activity IN NUMBER DEFAULT NULL
526 ,p_sold_to_org_id IN NUMBER DEFAULT NULL
527 ,p_status_code IN Varchar2);
528
529 --bug 5007836, Created this overloaded API
530 FUNCTION save_messages( p_request_id IN NUMBER
531 ,p_message_source_code IN VARCHAR2 DEFAULT 'A')
532 RETURN VARCHAR2;
533
534 --Bug 8514085 Starts
535 G_msg_tbl_Copy Msg_Tbl_Type;
536 /* This global will indicate that there a timer created in forms to show multiple
537 messages in one window, instead of one after other. */
538 G_msg_timer_created BOOLEAN:=FALSE;
539 /* This global will indicate that before the multi message timer has been processed, the
540 message table has been initialzed.*/
541 G_msg_init_with_timer BOOLEAN:=FALSE;
542
543 PROCEDURE Add_Msgs_To_CopyMsgTbl;
544 PROCEDURE Add_Msgs_From_CopyMsgTbl;
545
546 --Will be used from Forms PLSQL Libraries(PLLs) to set the global G_msg_timer_created.
547 PROCEDURE Set_Msg_Timer_Created(p_msg_timer_created IN BOOLEAN);
548
549 --Will be used by Forms PLSQL Libraries(PLLs) to get the global G_Initialized_with_timer.
550 FUNCTION Get_Msg_Init_with_timer RETURN BOOLEAN;
551 --Bug 8514085 Ends
552
553 END OE_MSG_PUB ;
[Home] [Help]