1 PACKAGE BODY OE_MSG_PUB AS
2 /* $Header: OEXUMSGB.pls 120.13.12010000.2 2008/08/04 15:03:57 amallik ship $ */
3
4 -- Constants used as tokens for unexpected error messages.
5
6 G_PKG_NAME CONSTANT VARCHAR2(15):= 'OE_MSG_PUB';
7 G_HEADER_ID NUMBER;
8 G_ORDER_NUMBER NUMBER;
9
10 -- Procedure Initialize
11 --
12 -- Usage Used by API callers and developers to intialize the
13 -- global message table.
14 -- Desc Clears the G_msg_tbl and resets all its global
15 -- variables. Except for the message level threshold.
16 --
17
18 PROCEDURE Initialize
19 IS
20 --
21 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
22 --
23 BEGIN
24
25 FND_MSG_PUB.Initialize;
26
27 G_msg_tbl.DELETE;
28 G_msg_count := 0;
29 G_msg_index := 0;
30 G_msg_context_tbl.DELETE;
31 G_msg_context_count := 0;
32 G_Msg_Context_index := 0;
33
34 IF l_debug_level > 0 THEN
35 oe_debug_pub.add( 'LEAVING OE_MSG_PUB.INITIALIZE' , 1 ) ;
36 END IF;
37 EXCEPTION
38 WHEN OTHERS THEN
39 IF l_debug_level > 0 THEN
40 oe_debug_pub.add('Error in procedure Initialize ' || sqlerrm);
41 END IF;
42 END;
43
44 PROCEDURE Set_Process_Activity(
45 p_process_activity IN NUMBER DEFAULT NULL)
46 IS
47 --
48 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
49 --
50 BEGIN
51
52 G_process_activity := p_process_activity;
53 EXCEPTION
54 WHEN OTHERS THEN
55 IF l_debug_level > 0 THEN
56 oe_debug_pub.add('Error in procedure Set_Process_Activity ' || sqlerrm);
57 END IF;
58 END Set_Process_Activity;
59
60 PROCEDURE Set_Msg_Context (
61 p_entity_code IN VARCHAR2 DEFAULT NULL
62 ,p_entity_ref IN VARCHAR2 DEFAULT NULL
63 ,p_entity_id IN NUMBER DEFAULT NULL
64 ,p_header_id IN NUMBER DEFAULT NULL
65 ,p_line_id IN NUMBER DEFAULT NULL
66 ,p_order_source_id IN NUMBER DEFAULT NULL
67 ,p_orig_sys_document_ref IN VARCHAR2 DEFAULT NULL
68 ,p_orig_sys_document_line_ref IN VARCHAR2 DEFAULT NULL
69 ,p_orig_sys_shipment_ref IN VARCHAR2 DEFAULT NULL
70 ,p_change_sequence IN VARCHAR2 DEFAULT NULL
71 ,p_source_document_type_id IN NUMBER DEFAULT NULL
72 ,p_source_document_id IN NUMBER DEFAULT NULL
73 ,p_source_document_line_id IN NUMBER DEFAULT NULL
74 ,p_attribute_code IN VARCHAR2 DEFAULT NULL
75 ,p_constraint_id IN NUMBER DEFAULT NULL
76 -- ,p_process_activity IN NUMBER DEFAULT NULL
77 )
78 IS
79 --
80 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
81 --
82 BEGIN
83 -- Increment message context count
84 G_msg_context_count := G_msg_context_count + 1;
85
86 /* IF statements added for 2244395 */
87
88 -- Write message context.
89
90 IF p_entity_code = FND_API.G_MISS_CHAR THEN
91 G_msg_context_tbl(G_msg_context_count).ENTITY_CODE := NULL;
92 ELSE
93 G_msg_context_tbl(G_msg_context_count).ENTITY_CODE := p_entity_code;
94 END IF;
95
96 IF p_entity_id = FND_API.G_MISS_NUM THEN
97 G_msg_context_tbl(G_msg_context_count).ENTITY_ID := NULL;
98 ELSE
99 G_msg_context_tbl(G_msg_context_count).ENTITY_ID := p_entity_id;
100 END IF;
101
102 IF p_entity_ref = FND_API.G_MISS_CHAR THEN
103 G_msg_context_tbl(G_msg_context_count).ENTITY_REF := NULL;
104 ELSE
105 G_msg_context_tbl(G_msg_context_count).ENTITY_REF := p_entity_ref;
106 END IF;
107
108 IF p_header_id = FND_API.G_MISS_NUM THEN
109 G_msg_context_tbl(G_msg_context_count).HEADER_ID := NULL;
110 ELSE
111 G_msg_context_tbl(G_msg_context_count).HEADER_ID := p_header_id;
112 END IF;
113
114 IF p_line_id = FND_API.G_MISS_NUM THEN
115 G_msg_context_tbl(G_msg_context_count).LINE_ID := NULL;
116 ELSE
117
118 IF p_line_id is not null
119 AND (p_header_id is null
120 OR p_header_id = FND_API.G_MISS_NUM) THEN
121
122
123 BEGIN
124 SELECT header_id
125 INTO G_msg_context_tbl(G_msg_context_count).header_id
126 FROM oe_order_lines_all
127 WHERE line_id = p_line_id;
128
129
130 EXCEPTION
131 WHEN OTHERS THEN
132 NULL;
133 END;
134 END IF;
135 G_msg_context_tbl(G_msg_context_count).LINE_ID := p_line_id;
136 END IF;
137
138 IF p_order_source_id = FND_API.G_MISS_NUM THEN
139 G_msg_context_tbl(G_msg_context_count).ORDER_SOURCE_ID := NULL;
140 ELSE
141 G_msg_context_tbl(G_msg_context_count).ORDER_SOURCE_ID := p_order_source_id;
142 END IF;
143
144 IF p_orig_sys_document_ref = FND_API.G_MISS_CHAR THEN
145 G_msg_context_tbl(G_msg_context_count).ORIG_SYS_DOCUMENT_REF := NULL;
146 ELSE
147 G_msg_context_tbl(G_msg_context_count).ORIG_SYS_DOCUMENT_REF := p_orig_sys_document_ref;
148 END IF;
149
150 IF p_orig_sys_document_line_ref = FND_API.G_MISS_CHAR THEN
151 G_msg_context_tbl(G_msg_context_count).ORIG_SYS_DOCUMENT_LINE_REF := NULL;
152 ELSE
153 G_msg_context_tbl(G_msg_context_count).ORIG_SYS_DOCUMENT_LINE_REF := p_orig_sys_document_line_ref;
154 END IF;
155
156 IF p_orig_sys_shipment_ref = FND_API.G_MISS_CHAR THEN
157 G_msg_context_tbl(G_msg_context_count).ORIG_SYS_SHIPMENT_REF := NULL;
158 ELSE
159 G_msg_context_tbl(G_msg_context_count).ORIG_SYS_SHIPMENT_REF := p_orig_sys_shipment_ref;
160 END IF;
161
162 IF p_change_sequence = FND_API.G_MISS_CHAR THEN
163 G_msg_context_tbl(G_msg_context_count).CHANGE_SEQUENCE := NULL;
164 ELSE
165 G_msg_context_tbl(G_msg_context_count).CHANGE_SEQUENCE := p_change_sequence;
166 END IF;
167
168 IF p_source_document_type_id = FND_API.G_MISS_NUM THEN
169 G_msg_context_tbl(G_msg_context_count).SOURCE_DOCUMENT_TYPE_ID := NULL;
170 ELSE
171 G_msg_context_tbl(G_msg_context_count).SOURCE_DOCUMENT_TYPE_ID := p_source_document_type_id;
172 END IF;
173
174 IF p_source_document_id = FND_API.G_MISS_NUM THEN
175 G_msg_context_tbl(G_msg_context_count).SOURCE_DOCUMENT_ID := NULL;
176 ELSE
177 G_msg_context_tbl(G_msg_context_count).SOURCE_DOCUMENT_ID := p_source_document_id;
178 END IF;
179
180 IF p_source_document_line_id = FND_API.G_MISS_NUM THEN
181 G_msg_context_tbl(G_msg_context_count).SOURCE_DOCUMENT_LINE_ID := NULL;
182 ELSE
183 G_msg_context_tbl(G_msg_context_count).SOURCE_DOCUMENT_LINE_ID := p_source_document_line_id;
184 END IF;
185
186 IF p_attribute_code = FND_API.G_MISS_CHAR THEN
187 G_msg_context_tbl(G_msg_context_count).ATTRIBUTE_CODE := NULL;
188 ELSE
189 G_msg_context_tbl(G_msg_context_count).ATTRIBUTE_CODE := p_attribute_code;
190 END IF;
191
192 IF p_constraint_id = FND_API.G_MISS_NUM THEN
193 G_msg_context_tbl(G_msg_context_count).CONSTRAINT_ID := NULL;
194 ELSE
195 G_msg_context_tbl(G_msg_context_count).CONSTRAINT_ID := p_constraint_id;
196 END IF;
197
198 -- G_msg_context_tbl(G_msg_context_count).PROCESS_ACTIVITY := p_process_activity;
199 G_msg_context_tbl(G_msg_context_count).PROCESS_ACTIVITY := G_process_activity;
200 EXCEPTION
201 WHEN OTHERS THEN
202 IF l_debug_level > 0 THEN
203 oe_debug_pub.add('Error in procedure Set_Msg_Context ' || sqlerrm);
204 END IF;
205 END;
206
207 PROCEDURE Update_Msg_Context (
208 p_entity_code IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
209 ,p_entity_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
210 ,p_header_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
211 ,p_line_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
212 ,p_order_source_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
213 ,p_orig_sys_document_ref IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
214 ,p_orig_sys_document_line_ref IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
215 ,p_orig_sys_shipment_ref IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
216 ,p_change_sequence IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
217 ,p_source_document_type_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
218 ,p_source_document_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
219 ,p_source_document_line_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
220 ,p_attribute_code IN VARCHAR2 DEFAULT FND_API.G_MISS_CHAR
221 ,p_constraint_id IN NUMBER DEFAULT FND_API.G_MISS_NUM
222 -- ,p_process_activity IN NUMBER DEFAULT FND_API.G_MISS_NUM
223 ) IS
224 --
225 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
226 --
227 Begin
228
229 -- if G_msg_context_tbl(G_msg_context_count).ENTITY_CODE = p_entity_code then
230 if p_entity_id <> FND_API.G_MISS_NUM then
231 G_msg_context_tbl(G_msg_context_count).ENTITY_ID := p_entity_id;
232 end if;
233 if p_header_id <> FND_API.G_MISS_NUM then
234 G_msg_context_tbl(G_msg_context_count).HEADER_ID := p_header_id;
235 end if;
236 if p_line_id <> FND_API.G_MISS_NUM then
237 G_msg_context_tbl(G_msg_context_count).LINE_ID := p_line_id;
238 end if;
239 if p_order_source_id <> FND_API.G_MISS_NUM then
240 G_msg_context_tbl(G_msg_context_count).ORDER_SOURCE_ID := p_order_source_id;
241 end if;
242 if p_orig_sys_document_ref <> FND_API.G_MISS_CHAR then
243 G_msg_context_tbl(G_msg_context_count).ORIG_SYS_DOCUMENT_REF := p_orig_sys_document_ref;
244 end if;
245 if p_orig_sys_document_line_ref <> FND_API.G_MISS_CHAR then
246 G_msg_context_tbl(G_msg_context_count).ORIG_SYS_DOCUMENT_LINE_REF := p_orig_sys_document_line_ref;
247 end if;
248 if p_orig_sys_shipment_ref <> FND_API.G_MISS_CHAR then
249 G_msg_context_tbl(G_msg_context_count).ORIG_SYS_SHIPMENT_REF := p_orig_sys_shipment_ref;
250 end if;
251 if p_change_sequence <> FND_API.G_MISS_CHAR then
252 G_msg_context_tbl(G_msg_context_count).CHANGE_SEQUENCE := p_change_sequence;
253 end if;
254 if p_source_document_type_id <> FND_API.G_MISS_NUM then
255 G_msg_context_tbl(G_msg_context_count).SOURCE_DOCUMENT_TYPE_ID := p_source_document_type_id;
256 end if;
257 if p_source_document_id <> FND_API.G_MISS_NUM then
258 G_msg_context_tbl(G_msg_context_count).SOURCE_DOCUMENT_ID := p_source_document_id;
259 end if;
260 if p_source_document_line_id <> FND_API.G_MISS_NUM then
261 G_msg_context_tbl(G_msg_context_count).SOURCE_DOCUMENT_LINE_ID := p_source_document_line_id;
262 end if;
263 if p_attribute_code <> FND_API.G_MISS_CHAR then
264 G_msg_context_tbl(G_msg_context_count).ATTRIBUTE_CODE := p_attribute_code;
265 end if;
266 if p_constraint_id <> FND_API.G_MISS_NUM then
267 G_msg_context_tbl(G_msg_context_count).CONSTRAINT_ID := p_constraint_id;
268 end if;
269 /* if p_process_activity <> FND_API.G_MISS_NUM then
270 G_msg_context_tbl(G_msg_context_count).PROCESS_ACTIVITY := p_process_activity;
271 end if;*/
272 /*
273 G_msg_context_tbl(G_msg_context_count).ENTITY_ID := p_entity_id;
274 G_msg_context_tbl(G_msg_context_count).HEADER_ID := p_header_id;
275 G_msg_context_tbl(G_msg_context_count).LINE_ID := p_line_Id;
276 G_msg_context_tbl(G_msg_context_count).ORIG_SYS_DOCUMENT_REF := p_orig_sys_document_ref;
277 G_msg_context_tbl(G_msg_context_count).ORIG_SYS_DOCUMENT_LINE_REF := p_orig_sys_document_line_ref;
278 G_msg_context_tbl(G_msg_context_count).SOURCE_DOCUMENT_ID := p_source_document_id;
279 G_msg_context_tbl(G_msg_context_count).SOURCE_DOCUMENT_LINE_ID := p_source_document_line_id;
280 G_msg_context_tbl(G_msg_context_count).ATTRIBUTE_CODE := p_attribute_code;
281 G_msg_context_tbl(G_msg_context_count).CONSTRAINT_ID := p_constraint_id;
282 */
283 -- end if;
284 EXCEPTION
285 WHEN OTHERS THEN
286 IF l_debug_level > 0 THEN
287 oe_debug_pub.add('Error in procedure Update_Msg_Context ' || sqlerrm);
288 END IF;
289 End Update_Msg_Context;
290
291 PROCEDURE Reset_Msg_Context (p_entity_code IN VARCHAR2)
292 IS
293 --
294 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
295 --
296 BEGIN
297 if G_msg_context_count > 0 then
298 if G_msg_context_tbl.EXISTS(G_msg_context_count) AND
299 G_msg_context_tbl(G_msg_context_count).ENTITY_CODE = p_entity_code then
300 G_msg_context_tbl.delete(G_msg_context_count) ;
301 G_msg_context_count := G_msg_context_count - 1;
302 end if;
303 end if;
304 EXCEPTION
305 WHEN OTHERS THEN
306 IF l_debug_level > 0 THEN
307 oe_debug_pub.add('Error in procedure Reset_Msg_Context ' || sqlerrm);
308 END IF;
309 END Reset_Msg_Context;
310
311
312
313 -- FUNCTION Count_Msg
314 --
315 -- Usage Used by API callers and developers to find the count
316 -- of messages in the message list.
317 -- Desc Returns the value of G_msg_count
318 --
319 -- Parameters None
320 --
321 -- Return NUMBER
322
323 FUNCTION Count_Msg RETURN NUMBER
324 IS
325 --
326 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
327 --
328 BEGIN
329
330 RETURN G_msg_Count;
331 EXCEPTION
332 WHEN OTHERS THEN
333 IF l_debug_level > 0 THEN
334 oe_debug_pub.add('Error in Function Count_msg ' || sqlerrm);
335 END IF;
336 END Count_Msg;
337
338 -- PROCEDURE Count_And_Get
339 --
340
341 PROCEDURE Count_And_Get
342 ( p_encoded IN VARCHAR2 := FND_API.G_TRUE ,
343 p_count OUT NOCOPY NUMBER ,
344
345 p_data OUT NOCOPY VARCHAR2
346
347 )
348 IS
349 l_msg_count NUMBER;
350 --
351 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
352 --
353 BEGIN
354
355 l_msg_count := Count_Msg;
356
357 IF l_msg_count = 1 THEN
358
359 p_data := Get ( p_msg_index => G_FIRST ,
360 p_encoded => p_encoded );
361
362 Reset;
363
364 END IF;
365
366 p_count := l_msg_count ;
367
368 IF l_debug_level > 0 THEN
369 oe_debug_pub.add( 'LEAVING OE_MSG_PUB.COUNT_AND_GET '|| L_MSG_COUNT , 3 ) ;
370 END IF;
371 EXCEPTION
372 WHEN OTHERS THEN
373 IF l_debug_level > 0 THEN
374 oe_debug_pub.add('Error in Function Count_And_Get ' || sqlerrm);
375 END IF;
376 END Count_And_Get;
377
378 -- PROCEDURE Add
379 --
380 -- Usage Used to add messages to the global message table.
381 --
382 -- Desc Reads a message off the message dictionary stack and
383 -- writes it in an encoded format to the global PL/SQL
384 -- message table.
385 -- The message is appended at the bottom of the message
386 -- table.
387 --
388
389 PROCEDURE Add(p_context_flag IN VARCHAR2 DEFAULT 'Y')
390 IS
391 l_type VARCHAR2(30);
392 l_app_id VARCHAR2(30);
393 l_message_name VARCHAR2(30);
394 --
395 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
396 --
397 BEGIN
398
399 -- Increment message count
400 G_msg_count := G_msg_count + 1;
401
402 -- Write message.
403 G_msg_tbl(G_msg_count).Message := FND_MESSAGE.GET_ENCODED;
404
405 IF p_context_flag = 'Y' AND G_msg_context_count <> 0 then
406
407 BEGIN
408
409 fnd_message.parse_encoded(G_msg_tbl(G_msg_count).Message,
410 l_app_id,
411 l_message_name);
412
413
414 Select type
415 Into l_type
416 from fnd_new_messages a,
417 fnd_application b
418 where a.application_id = b.application_id
419 and a.language_code = USERENV('LANG')
420 and a.message_name = l_message_name
421 and b.application_short_name = l_app_id;
422
423 EXCEPTION
424
425 WHEN OTHERS THEN
426
427 l_type := 'ERROR';
428
429 END;
430
431 G_msg_tbl(G_msg_count).ENTITY_CODE := G_msg_context_tbl(G_msg_context_count).ENTITY_CODE;
432 G_msg_tbl(G_msg_count).ENTITY_ID := G_msg_context_tbl(G_msg_context_count).ENTITY_ID;
433 G_msg_tbl(G_msg_count).HEADER_ID := G_msg_context_tbl(G_msg_context_count).HEADER_ID;
434 G_msg_tbl(G_msg_count).LINE_ID := G_msg_context_tbl(G_msg_context_count).LINE_ID;
435 G_msg_tbl(G_msg_count).ORDER_SOURCE_ID := G_msg_context_tbl(G_msg_context_count).ORDER_SOURCE_ID;
436 G_msg_tbl(G_msg_count).ORIG_SYS_DOCUMENT_REF := G_msg_context_tbl(G_msg_context_count).ORIG_SYS_DOCUMENT_REF;
437 G_msg_tbl(G_msg_count).ORIG_SYS_DOCUMENT_LINE_REF := G_msg_context_tbl(G_msg_context_count).ORIG_SYS_DOCUMENT_LINE_REF;
438 G_msg_tbl(G_msg_count).SOURCE_DOCUMENT_TYPE_ID := G_msg_context_tbl(G_msg_context_count).SOURCE_DOCUMENT_TYPE_ID;
439 G_msg_tbl(G_msg_count).SOURCE_DOCUMENT_ID := G_msg_context_tbl(G_msg_context_count).SOURCE_DOCUMENT_ID;
440 G_msg_tbl(G_msg_count).SOURCE_DOCUMENT_LINE_ID := G_msg_context_tbl(G_msg_context_count).SOURCE_DOCUMENT_LINE_ID;
441 G_msg_tbl(G_msg_count).ATTRIBUTE_CODE := G_msg_context_tbl(G_msg_context_count).ATTRIBUTE_CODE;
442 G_msg_tbl(G_msg_count).CONSTRAINT_ID := G_msg_context_tbl(G_msg_context_count).CONSTRAINT_ID;
443 G_msg_tbl(G_msg_count).PROCESS_ACTIVITY := G_msg_context_tbl(G_msg_context_count).PROCESS_ACTIVITY;
444 G_msg_tbl(G_msg_count).TYPE := l_type;
445 G_msg_tbl(G_msg_count).ORG_ID := MO_GLOBAL.Get_Current_Org_Id;
446
447 ELSE
448 G_msg_tbl(G_msg_count).ENTITY_CODE := NULL;
449 G_msg_tbl(G_msg_count).ENTITY_ID := NULL;
450 G_msg_tbl(G_msg_count).HEADER_ID := NULL;
451 G_msg_tbl(G_msg_count).LINE_ID := NULL;
452 G_msg_tbl(G_msg_count).ORDER_SOURCE_ID := NULL;
453 G_msg_tbl(G_msg_count).ORIG_SYS_DOCUMENT_REF := NULL;
454 G_msg_tbl(G_msg_count).ORIG_SYS_DOCUMENT_LINE_REF := NULL;
455 G_msg_tbl(G_msg_count).SOURCE_DOCUMENT_TYPE_ID := NULL;
456 G_msg_tbl(G_msg_count).SOURCE_DOCUMENT_ID := NULL;
457 G_msg_tbl(G_msg_count).SOURCE_DOCUMENT_LINE_ID := NULL;
458 G_msg_tbl(G_msg_count).ATTRIBUTE_CODE := NULL;
459 G_msg_tbl(G_msg_count).CONSTRAINT_ID := NULL;
460 G_msg_tbl(G_msg_count).PROCESS_ACTIVITY := NULL;
461 G_msg_tbl(G_msg_count).TYPE := NULL;
462 G_msg_tbl(G_msg_count).ORG_ID := NULL;
463 END IF;
464
465 IF l_debug_level > 0 THEN
466 oe_debug_pub.add( 'LEAVING OE_MSG_PUB.ADD' , 3 ) ;
467 END IF;
468 EXCEPTION
469 WHEN OTHERS THEN
470 IF l_debug_level > 0 THEN
471 oe_debug_pub.add('Error in Procedure Add ' || sqlerrm);
472 END IF;
473 END Add;
474
475 -- PROCEDURE Add_text
476 --
477 -- Usage Used by Devlopers to add messages to Global stack from FND
478 -- stack .
479 --
480 -- Desc Accepts the message as input and writes to global_PL/SQL
481 -- message table.
482 -- The message is appended at the bottom of the message
483 -- table.
484 --
485
486 PROCEDURE Add_Text(p_message_text IN VARCHAR2
487 ,p_type IN VARCHAR2 DEFAULT 'ERROR'
488 ,p_context_flag IN VARCHAR2 DEFAULT 'Y')
489 IS
490 --
491 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
492 --
493 BEGIN
494
495 -- Increment message count
496 G_msg_count := G_msg_count + 1;
497
498 -- Write message.
499 G_msg_tbl(G_msg_count).Message_text := p_message_text;
500
501 IF p_context_flag = 'Y' AND G_msg_context_count <> 0 then
502 G_msg_tbl(G_msg_count).ENTITY_CODE := G_msg_context_tbl(G_msg_context_count).ENTITY_CODE;
503 G_msg_tbl(G_msg_count).ENTITY_ID := G_msg_context_tbl(G_msg_context_count).ENTITY_ID;
504 G_msg_tbl(G_msg_count).HEADER_ID := G_msg_context_tbl(G_msg_context_count).HEADER_ID;
505 G_msg_tbl(G_msg_count).LINE_ID := G_msg_context_tbl(G_msg_context_count).LINE_ID;
506 G_msg_tbl(G_msg_count).ORDER_SOURCE_ID := G_msg_context_tbl(G_msg_context_count).ORDER_SOURCE_ID;
507 G_msg_tbl(G_msg_count).ORIG_SYS_DOCUMENT_REF := G_msg_context_tbl(G_msg_context_count).ORIG_SYS_DOCUMENT_REF;
508 G_msg_tbl(G_msg_count).ORIG_SYS_DOCUMENT_LINE_REF := G_msg_context_tbl(G_msg_context_count).ORIG_SYS_DOCUMENT_LINE_REF;
509 G_msg_tbl(G_msg_count).SOURCE_DOCUMENT_TYPE_ID := G_msg_context_tbl(G_msg_context_count).SOURCE_DOCUMENT_TYPE_ID;
510 G_msg_tbl(G_msg_count).SOURCE_DOCUMENT_ID := G_msg_context_tbl(G_msg_context_count).SOURCE_DOCUMENT_ID;
511 G_msg_tbl(G_msg_count).SOURCE_DOCUMENT_LINE_ID := G_msg_context_tbl(G_msg_context_count).SOURCE_DOCUMENT_LINE_ID;
512 G_msg_tbl(G_msg_count).ATTRIBUTE_CODE := G_msg_context_tbl(G_msg_context_count).ATTRIBUTE_CODE;
513 G_msg_tbl(G_msg_count).CONSTRAINT_ID := G_msg_context_tbl(G_msg_context_count).CONSTRAINT_ID;
514 G_msg_tbl(G_msg_count).PROCESS_ACTIVITY := G_msg_context_tbl(G_msg_context_count).PROCESS_ACTIVITY;
515 G_msg_tbl(G_msg_count).TYPE := p_type;
516 G_msg_tbl(G_msg_count).ORG_ID := MO_GLOBAL.Get_Current_Org_Id;
517
518 ELSE
519 G_msg_tbl(G_msg_count).ENTITY_CODE := NULL;
520 G_msg_tbl(G_msg_count).ENTITY_ID := NULL;
521 G_msg_tbl(G_msg_count).HEADER_ID := NULL;
522 G_msg_tbl(G_msg_count).LINE_ID := NULL;
523 G_msg_tbl(G_msg_count).ORDER_SOURCE_ID := NULL;
524 G_msg_tbl(G_msg_count).ORIG_SYS_DOCUMENT_REF := NULL;
525 G_msg_tbl(G_msg_count).ORIG_SYS_DOCUMENT_LINE_REF := NULL;
526 G_msg_tbl(G_msg_count).SOURCE_DOCUMENT_TYPE_ID := NULL;
527 G_msg_tbl(G_msg_count).SOURCE_DOCUMENT_ID := NULL;
528 G_msg_tbl(G_msg_count).SOURCE_DOCUMENT_LINE_ID := NULL;
529 G_msg_tbl(G_msg_count).ATTRIBUTE_CODE := NULL;
530 G_msg_tbl(G_msg_count).CONSTRAINT_ID := NULL;
531 G_msg_tbl(G_msg_count).PROCESS_ACTIVITY := NULL;
532 G_msg_tbl(G_msg_count).TYPE := NULL;
533 G_msg_tbl(G_msg_count).ORG_ID := NULL;
534 END IF;
535
536 IF l_debug_level > 0 THEN
537 oe_debug_pub.add( 'LEAVING OE_MSG_PUB.ADD_TEXT' , 3 ) ;
538 END IF;
539 EXCEPTION
540 WHEN OTHERS THEN
541 IF l_debug_level > 0 THEN
542 oe_debug_pub.add('Error in Procedure Add_text ' || sqlerrm);
543 END IF;
544 END Add_Text;
545
546 --
547 -- Usage Used to delete a specific message from the message
548 -- list, or clear the whole message list.
549 --
550 -- Desc If instructed to delete a specific message, the
551 -- message is removed from the message table and the
552 -- table is compressed by moving the messages coming
553 -- after the deleted messages up one entry in the message
554 -- table.
555 -- If there is no entry found the Delete procedure does
556 -- nothing, and no exception is raised.
557 -- If delete is passed no parameters it deletes the whole
558 -- message table.
559 --
560 -- Prameters p_msg_index IN NUMBER := FND_API.G_MISS_NUM Optional
561 -- holds the index of the message to be deleted.
562 --
563
564 PROCEDURE Delete_Msg
565 ( p_msg_index IN NUMBER := NULL
566 )
567 IS
568 l_msg_index NUMBER;
569 --
570 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
571 --
572 BEGIN
573
574 IF p_msg_index IS NULL THEN
575
576 -- Delete the whole table.
577
578 G_msg_tbl.DELETE;
579 G_msg_count := 0;
580 G_msg_index := 0;
581
582 ELSE
583
584 -- Check if entry exists
585
586 IF G_msg_tbl.EXISTS(p_msg_index) THEN
587
588 IF p_msg_index <= G_msg_count THEN
589
590 -- Move all messages up 1 entry.
591
592 FOR I IN p_msg_index..G_msg_count-1 LOOP
593
594 G_msg_tbl( I ).MESSAGE := G_msg_tbl( I + 1 ).MESSAGE;
595 G_msg_tbl( I ).MESSAGE := G_msg_tbl( I + 1 ).MESSAGE_TEXT;
596 G_msg_tbl( I ).ENTITY_CODE := G_msg_tbl( I + 1 ).ENTITY_CODE;
597 G_msg_tbl( I ).ENTITY_ID := G_msg_tbl( I + 1 ).ENTITY_ID;
598 G_msg_tbl( I ).HEADER_ID := G_msg_tbl( I + 1 ).HEADER_ID;
599 G_msg_tbl( I ).LINE_ID := G_msg_tbl( I + 1 ).LINE_ID;
600 G_msg_tbl( I ).ORDER_SOURCE_ID := G_msg_tbl( I + 1 ).ORDER_SOURCE_ID;
601 G_msg_tbl( I ).ORIG_SYS_DOCUMENT_REF := G_msg_tbl( I + 1 ).ORIG_SYS_DOCUMENT_REF;
602 G_msg_tbl( I ).ORIG_SYS_DOCUMENT_LINE_REF := G_msg_tbl( I + 1 ).ORIG_SYS_DOCUMENT_LINE_REF;
603 G_msg_tbl( I ).SOURCE_DOCUMENT_TYPE_ID := G_msg_tbl( I + 1 ).SOURCE_DOCUMENT_TYPE_ID;
604 G_msg_tbl( I ).SOURCE_DOCUMENT_ID := G_msg_tbl( I + 1 ).SOURCE_DOCUMENT_ID;
605 G_msg_tbl( I ).SOURCE_DOCUMENT_LINE_ID := G_msg_tbl( I + 1 ).SOURCE_DOCUMENT_LINE_ID;
606 G_msg_tbl( I ).ATTRIBUTE_CODE := G_msg_tbl( I + 1 ).ATTRIBUTE_CODE;
607 G_msg_tbl( I ).CONSTRAINT_ID := G_msg_tbl( I + 1 ).CONSTRAINT_ID;
608 G_msg_tbl( I ).PROCESS_ACTIVITY := G_msg_tbl( I + 1 ).PROCESS_ACTIVITY;
609 G_msg_tbl( I ).NOTIFICATION_FLAG := G_msg_tbl( I + 1 ).NOTIFICATION_FLAG;
610 G_msg_tbl( I ).TYPE := G_msg_tbl( I + 1 ).TYPE;
611 G_msg_tbl( I ).PROCESSED := G_msg_tbl( I + 1 ).PROCESSED;
612
613 END LOOP;
614
615 -- Delete the last message table entry.
616
617 G_msg_tbl.DELETE(G_msg_count) ;
618 G_msg_count := G_msg_count - 1 ;
619
620 END IF;
621
622 END IF;
623
624 END IF;
625 EXCEPTION
626 WHEN OTHERS THEN
627 IF l_debug_level > 0 THEN
628 oe_debug_pub.add('Error in Delete_msg ' || sqlerrm);
629 END IF;
630 END Delete_Msg;
631
632 procedure get_msg_context(
633 p_msg_index IN NUMBER
634 ,x_entity_code OUT NOCOPY VARCHAR2
635
636 ,x_entity_ref OUT NOCOPY VARCHAR2
637
638 ,x_entity_id OUT NOCOPY NUMBER
639
640 ,x_header_id OUT NOCOPY NUMBER
641
642 ,x_line_id OUT NOCOPY NUMBER
643
644 ,x_order_source_id OUT NOCOPY NUMBER
645
646 ,x_orig_sys_document_ref OUT NOCOPY VARCHAR2
647
648 ,x_orig_sys_line_ref OUT NOCOPY VARCHAR2
649
650 ,x_orig_sys_shipment_ref OUT NOCOPY VARCHAR2
651
652 ,x_change_sequence OUT NOCOPY VARCHAR2
653
654 ,x_source_document_type_id OUT NOCOPY NUMBER
655
656 ,x_source_document_id OUT NOCOPY NUMBER
657
658 ,x_source_document_line_id OUT NOCOPY NUMBER
659
660 ,x_attribute_code OUT NOCOPY VARCHAR2
661
662 ,x_constraint_id OUT NOCOPY NUMBER
663
664 ,x_process_activity OUT NOCOPY NUMBER
665
666 ,x_notification_flag OUT NOCOPY VARCHAR2
667
668 ,x_type OUT NOCOPY VARCHAR2
669
670 ) IS
671 --
672 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
673 --
674 Begin
675 x_entity_code := G_msg_tbl(p_msg_index).ENTITY_CODE;
676 x_entity_ref := G_msg_tbl(p_msg_index).ENTITY_REF;
677 x_entity_id := G_msg_tbl(p_msg_index).ENTITY_ID;
678 x_header_id := G_msg_tbl(p_msg_index).HEADER_ID;
679 x_line_id := G_msg_tbl(p_msg_index).LINE_ID;
680 x_order_source_id := G_msg_tbl(p_msg_index).ORDER_SOURCE_ID;
681 x_orig_sys_document_ref := G_msg_tbl(p_msg_index).ORIG_SYS_DOCUMENT_REF;
682 x_orig_sys_line_ref := G_msg_tbl(p_msg_index).ORIG_SYS_DOCUMENT_LINE_REF;
683 x_orig_sys_shipment_ref := G_msg_tbl(p_msg_index).ORIG_SYS_SHIPMENT_REF;
684 x_change_sequence := G_msg_tbl(p_msg_index).CHANGE_SEQUENCE;
685 x_source_document_type_id := G_msg_tbl(p_msg_index).SOURCE_DOCUMENT_TYPE_ID;
686 x_source_document_id := G_msg_tbl(p_msg_index).SOURCE_DOCUMENT_ID;
687 x_source_document_line_id := G_msg_tbl(p_msg_index).SOURCE_DOCUMENT_LINE_ID;
688 x_attribute_code := G_msg_tbl(p_msg_index).ATTRIBUTE_CODE;
689 x_constraint_id := G_msg_tbl(p_msg_index).CONSTRAINT_ID;
690 x_process_activity := G_msg_tbl(p_msg_index).PROCESS_ACTIVITY;
691 x_notification_flag := G_msg_tbl(p_msg_index).NOTIFICATION_FLAG;
692 x_type := G_msg_tbl(p_msg_index).TYPE;
693
694 EXCEPTION
695 WHEN NO_DATA_FOUND THEN
696 -- message not found return NULL;
697 x_entity_code := NULL;
698 x_entity_ref := NULL;
699 x_entity_id := NULL;
700 x_header_id := NULL;
701 x_line_id := NULL;
702 x_order_source_id := NULL;
703 x_orig_sys_document_ref := NULL;
704 x_orig_sys_line_ref := NULL;
705 x_orig_sys_shipment_ref := NULL;
706 x_change_sequence := NULL;
707 x_source_document_type_id := NULL;
708 x_source_document_id := NULL;
709 x_source_document_line_id := NULL;
710 x_attribute_code := NULL;
711 x_constraint_id := NULL;
712 x_process_activity := NULL;
713 x_notification_flag := NULL;
714 x_type := NULL;
715 End get_msg_context;
716
717
718 -- PROCEDURE Get
719 --
720
721 PROCEDURE Get
722 ( p_msg_index IN NUMBER := G_NEXT ,
723 p_encoded IN VARCHAR2 := FND_API.G_TRUE ,
724 p_data OUT NOCOPY VARCHAR2 ,
725
726 p_msg_index_out OUT NOCOPY NUMBER
727
728 )
729 IS
730 l_msg_index NUMBER := G_msg_index;
731 --
732 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
733 --
734 BEGIN
735
736 IF p_msg_index = G_NEXT THEN
737 G_msg_index := G_msg_index + 1;
738 ELSIF p_msg_index = G_FIRST THEN
739 G_msg_index := 1;
740 ELSIF p_msg_index = G_PREVIOUS THEN
741 G_msg_index := G_msg_index - 1;
742 ELSIF p_msg_index = G_LAST THEN
743 G_msg_index := G_msg_count ;
744 ELSE
745 G_msg_index := p_msg_index ;
746 END IF;
747
748
749 IF G_msg_tbl(G_msg_index).Message_Text IS NOT NULL THEN
750
751 p_data := G_msg_tbl(G_msg_index).Message_Text;
752
753 ELSE
754 IF FND_API.To_Boolean( p_encoded ) THEN
755
756 p_data := G_msg_tbl( G_msg_index ).Message;
757
758 ELSE
759
760 FND_MESSAGE.SET_ENCODED ( G_msg_tbl( G_msg_index ).Message );
761
762 p_data := FND_MESSAGE.GET;
763
764 END IF;
765 END IF;
766
767 p_msg_index_out := G_msg_index ;
768
769 EXCEPTION
770
771 WHEN NO_DATA_FOUND THEN
772
773 -- No more messages, revert G_msg_index and return NULL;
774
775 G_msg_index := l_msg_index;
776
777 p_data := NULL;
778 p_msg_index_out := NULL;
779 WHEN OTHERS THEN
780 IF l_debug_level > 0 THEN
781 oe_debug_pub.add('Error in Procedure Get ' || sqlerrm);
782 END IF;
783 END Get;
784
785 -- FUNCTION Get
786 --
787
788 FUNCTION Get
789 ( p_msg_index IN NUMBER := G_NEXT ,
790 p_encoded IN VARCHAR2 := FND_API.G_TRUE
791 )
792 RETURN VARCHAR2
793 IS
794 l_data VARCHAR2(2000) ;
795 l_msg_index_out NUMBER ;
796 --
797 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
798 --
799 BEGIN
800
801 Get
802 ( p_msg_index ,
803 p_encoded ,
804 l_data ,
805 l_msg_index_out
806 );
807
808 RETURN l_data ;
809
810 EXCEPTION
811 WHEN OTHERS THEN
812 IF l_debug_level > 0 THEN
813 oe_debug_pub.add('Error in Function Get ' || sqlerrm);
814 END IF;
815 END Get;
816
817 -- PROCEDURE Reset
818 --
819 -- Usage Used to reset the message table index used in reading
820 -- messages to point to the top of the message table or
821 -- the botom of the message table.
822 --
823 -- Desc Sets G_msg_index to 0 or G_msg_count+1 depending on
824 -- the reset mode.
825 --
826 -- Parameters p_mode IN NUMBER := G_FIRST Optional
827 -- possible values are :
828 -- G_FIRST resets index to the begining of msg tbl
829 -- G_LAST resets index to the end of msg tbl
830 --
831
832 PROCEDURE Reset ( p_mode IN NUMBER := G_FIRST )
833 IS
834 l_procedure_name CONSTANT VARCHAR2(15):='Reset';
835 --
836 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
837 --
838 BEGIN
839
840 IF p_mode = G_FIRST THEN
841
842 G_msg_index := 0;
843
844 ELSIF p_mode = G_LAST THEN
845
846 G_msg_index := G_msg_count;
847
848 ELSE
849
850 -- Invalid mode.
851
852 OE_MSG_PUB.Add_Exc_Msg
853 ( p_pkg_name => G_PKG_NAME ,
854 p_procedure_name => l_procedure_name ,
855 p_error_text => 'Invalid p_mode: '||p_mode
856 );
857
858 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
859
860 END IF;
861
862 EXCEPTION
863 WHEN OTHERS THEN
864 IF l_debug_level > 0 THEN
865 oe_debug_pub.add('Error in Procedure Reset ' || sqlerrm);
866 END IF;
867 END Reset;
868
869 -- FUNCTION Check_Msg_Level
870 --
871 -- Usage Used by API developers to check if the level of the
872 -- message they want to write to the message table is
873 -- higher or equal to the message level threshold or not.
874 -- If the function returns TRUE the developer should go
875 -- ahead and write the message to the message table else
876 -- he/she should skip writing this message.
877 -- Desc Accepts a message level as input fetches the value of
878 -- the message threshold profile option and compares it
879 -- to the input level.
880 -- Return TRUE if the level is equal to or higher than the
881 -- threshold. Otherwise, it returns FALSE.
882 --
883
884 FUNCTION Check_Msg_Level
885 ( p_message_level IN NUMBER := G_MSG_LVL_SUCCESS
886 ) RETURN BOOLEAN
887 IS
888 --
889 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
890 --
891 BEGIN
892
893 IF G_msg_level_threshold = FND_API.G_MISS_NUM THEN
894
895 -- Read the Profile option value.
896
897 G_msg_level_threshold :=
898 TO_NUMBER ( FND_PROFILE.VALUE('FND_AS_MSG_LEVEL_THRESHOLD') );
899
900 IF G_msg_level_threshold IS NULL THEN
901
902 G_msg_level_threshold := G_MSG_LVL_SUCCESS;
903
904 END IF;
905
906 END IF;
907
908 RETURN p_message_level >= G_msg_level_threshold ;
909 EXCEPTION
910 WHEN OTHERS THEN
911 IF l_debug_level > 0 THEN
912 oe_debug_pub.add('Error in Function Check_Msg_Level ' || sqlerrm);
913 END IF;
914 END; -- Check_Msg_Level
915
916 PROCEDURE Build_Exc_Msg
917 ( p_pkg_name IN VARCHAR2 :=FND_API.G_MISS_CHAR ,
918 p_procedure_name IN VARCHAR2 :=FND_API.G_MISS_CHAR ,
919 p_error_text IN VARCHAR2 :=FND_API.G_MISS_CHAR
920 )
921 IS
922 l_error_text VARCHAR2(2000) := p_error_text ;
923 --
924 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
925 --
926 BEGIN
927
928 -- If p_error_text is missing use SQLERRM.
929
930 IF p_error_text = FND_API.G_MISS_CHAR THEN
931
932 l_error_text := SUBSTR (SQLERRM , 1 , 2000);
933
934 END IF;
935
936 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
937
938 IF p_pkg_name <> FND_API.G_MISS_CHAR THEN
939 FND_MESSAGE.SET_TOKEN('PKG_NAME',p_pkg_name);
940 END IF;
941
942 IF p_procedure_name <> FND_API.G_MISS_CHAR THEN
943 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME',p_procedure_name);
944 END IF;
945
946 IF l_error_text <> FND_API.G_MISS_CHAR THEN
947 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_error_text);
948 END IF;
949 EXCEPTION
950 WHEN OTHERS THEN
951 IF l_debug_level > 0 THEN
952 oe_debug_pub.add('Error in Function Build_Exc_Msg ' || sqlerrm);
953 END IF;
954 END; -- Build_Exc_Msg
955
956 PROCEDURE Add_Exc_Msg
957 ( p_pkg_name IN VARCHAR2 :=FND_API.G_MISS_CHAR ,
958 p_procedure_name IN VARCHAR2 :=FND_API.G_MISS_CHAR ,
959 p_error_text IN VARCHAR2 :=FND_API.G_MISS_CHAR ,
960 p_context_flag IN VARCHAR2 DEFAULT 'Y'
961 )
962 IS
963 --
964 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
965 --
966 BEGIN
967 Build_Exc_Msg
968 ( p_pkg_name ,
969 p_procedure_name ,
970 p_error_text
971 );
972 Add((p_context_flag));
973 EXCEPTION
974 WHEN OTHERS THEN
975 IF l_debug_level > 0 THEN
976 oe_debug_pub.add('Error in Procedure Add_Exc_Msg ' || sqlerrm);
977 END IF;
978 END Add_Exc_Msg ;
979
980 -- PROCEDURE Dump_Msg
981 --
982
983 PROCEDURE Dump_Msg
984 ( p_msg_index IN NUMBER )
985 IS
986 --
987 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
988 --
989 BEGIN
990 OE_DEBUG_PUB.debug_on;
991 IF l_debug_level > 0 THEN
992 oe_debug_pub.add( 'MESSAGE NUMBER : '||P_MSG_INDEX ) ;
993 oe_debug_pub.add( 'DATA = '||G_MSG_TBL ( P_MSG_INDEX ) .MESSAGE ) ;
994 oe_debug_pub.add( 'ENTITY_CODE = '||G_MSG_TBL ( P_MSG_INDEX ) .ENTITY_CODE ) ;
995 oe_debug_pub.add( 'ENTITY_ID = '||G_MSG_TBL ( P_MSG_INDEX ) .ENTITY_ID ) ;
996 oe_debug_pub.add( 'HEADER_ID = '||G_MSG_TBL ( P_MSG_INDEX ) .HEADER_ID ) ;
997 oe_debug_pub.add( 'LINE_ID = '||G_MSG_TBL ( P_MSG_INDEX ) .LINE_ID ) ;
998 oe_debug_pub.add( 'ORDER_SOURCE_ID = '||G_MSG_TBL ( P_MSG_INDEX ) .ORDER_SOURCE_ID ) ;
999 oe_debug_pub.add( 'ORIG_SYS_DOC_REF = '||G_MSG_TBL ( P_MSG_INDEX ) .ORIG_SYS_DOCUMENT_REF ) ;
1000 oe_debug_pub.add( 'ORIG_SYS_LIN_REF = '||G_MSG_TBL ( P_MSG_INDEX ) .ORIG_SYS_DOCUMENT_LINE_REF ) ;
1001 oe_debug_pub.add( 'SOURCE_DOC_TYPE_ID = '||G_MSG_TBL ( P_MSG_INDEX ) .SOURCE_DOCUMENT_TYPE_ID ) ;
1002 oe_debug_pub.add( 'SOURCE_DOC_ID = '||G_MSG_TBL ( P_MSG_INDEX ) .SOURCE_DOCUMENT_ID ) ;
1003 oe_debug_pub.add( 'SOURCE_LIN_ID = '||G_MSG_TBL ( P_MSG_INDEX ) .SOURCE_DOCUMENT_LINE_ID ) ;
1004 oe_debug_pub.add( 'ATTRIBUTE_CODE = '||G_MSG_TBL ( P_MSG_INDEX ) .ATTRIBUTE_CODE ) ;
1005 oe_debug_pub.add( 'CONSTRAINT_ID = '||G_MSG_TBL ( P_MSG_INDEX ) .CONSTRAINT_ID ) ;
1006 oe_debug_pub.add( 'PROCESS_ACTIVITY = '||G_MSG_TBL ( P_MSG_INDEX ) .PROCESS_ACTIVITY ) ;
1007 END IF;
1008 OE_DEBUG_PUB.dumpdebug;
1009 OE_DEBUG_PUB.debug_off;
1010 EXCEPTION
1011 WHEN OTHERS THEN
1012 IF l_debug_level > 0 THEN
1013 oe_debug_pub.add('Error in Procedure Dump_Msg ' || sqlerrm);
1014 END IF;
1015 END Dump_Msg;
1016
1017 -- PROCEDURE Dump_List
1018 --
1019 PROCEDURE Dump_List
1020 ( p_messages IN BOOLEAN := FALSE
1021 )
1022 IS
1023 --
1024 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1025 --
1026 BEGIN
1027
1028 OE_DEBUG_PUB.debug_on;
1029 IF l_debug_level > 0 THEN
1030 oe_debug_pub.add( 'DUMPING MESSAGE LIST :' ) ;
1031 oe_debug_pub.add( 'G_MSG_TBL.COUNT = '||G_MSG_TBL.COUNT ) ;
1032 oe_debug_pub.add( 'G_MSG_COUNT = '||G_MSG_COUNT ) ;
1033 oe_debug_pub.add( 'G_MSG_INDEX = '||G_MSG_INDEX ) ;
1034 END IF;
1035 OE_DEBUG_PUB.dumpdebug;
1036 OE_DEBUG_PUB.debug_off;
1037
1038 IF p_messages THEN
1039
1040 FOR I IN 1..G_msg_tbl.COUNT LOOP
1041
1042 dump_Msg (I);
1043
1044 END LOOP;
1045
1046 END IF;
1047 EXCEPTION
1048 WHEN OTHERS THEN
1049 IF l_debug_level > 0 THEN
1050 oe_debug_pub.add('Error in Procedure Dump_List ' || sqlerrm);
1051 END IF;
1052 END Dump_List;
1053
1054
1055 -- PROCEDURE save_messages
1056 --
1057 -- Usage Used by API developers to save messages in database.
1058 -- This procedure is used to save massages which were created by
1059 -- batch programs.
1060 --
1061 -- Desc Accepts request_id as input and assign the same to all
1062 -- messages.
1063 --
1064 --
1065 -- Parameters p_request_id IN NUMBER.
1066
1067 Procedure save_messages(p_request_id IN NUMBER
1068 ,p_message_source_code IN VARCHAR2 DEFAULT 'C')
1069 IS
1070 l_count_msg NUMBER := OE_MSG_PUB.Count_Msg;
1071 --
1072 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1073 --
1074 begin
1075 IF l_debug_level > 0 THEN
1076 oe_debug_pub.add( 'ENTER SAVE_MESSAGES' , 1 ) ;
1077 END IF;
1078 FOR I IN 1..l_count_msg LOOP
1079 IF nvl(g_msg_tbl(I).processed,'N') = 'N' THEN
1080 insert_message(I,p_request_id,p_message_source_code);
1081 END IF;
1082 End Loop;
1083 IF l_debug_level > 0 THEN
1084 oe_debug_pub.add( 'EXIT SAVE_MESSAGES' , 1 ) ;
1085 END IF;
1086 oe_msg_pub.initialize;
1087 EXCEPTION
1088 WHEN OTHERS THEN
1089 IF l_debug_level > 0 THEN
1090 oe_debug_pub.add('Error in Procedure save_messages ' || sqlerrm);
1091 END IF;
1092 end;
1093
1094 -- Bug 5007836, Created this overloaded API which is to be called
1095 -- from OEXERMSG.pld, OE_UI_MESSAGE.Save API
1096 Function save_messages(p_request_id IN NUMBER
1097 ,p_message_source_code IN VARCHAR2 DEFAULT 'A')
1098 RETURN VARCHAR2
1099 IS
1100 Pragma AUTONOMOUS_TRANSACTION;
1101 l_count_msg NUMBER := OE_MSG_PUB.Count_Msg;
1102 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1103 l_count NUMBER := 0;
1104 begin
1105 IF l_debug_level > 0 THEN
1106 oe_debug_pub.add( 'ENTER Function SAVE_MESSAGES' , 1 ) ;
1107 END IF;
1108 FOR I IN 1..l_count_msg LOOP
1109 IF nvl(g_msg_tbl(I).processed,'N') = 'N' THEN
1110 insert_message(I,p_request_id,p_message_source_code);
1111 l_count := l_count +1;
1112 END IF;
1113 End Loop;
1114
1115 oe_msg_pub.initialize;
1116
1117 COMMIT;
1118 IF l_count = l_count_msg THEN
1119 -- All messages processed
1120 RETURN('A');
1121 ELSIF l_count >0 THEN
1122 -- Some messages processed
1123 RETURN('S');
1124 ELSE
1125 -- No message processed
1126 RETURN('N');
1127 END IF;
1128 EXCEPTION
1129 WHEN OTHERS THEN
1130 IF l_debug_level > 0 THEN
1131 oe_debug_pub.add('Error in Function save_messages ' || sqlerrm);
1132 END IF;
1133 end save_messages;
1134
1135 -- PROCEDURE insert_messages
1136 --
1137 -- Usage Used by save_messages procedure to insert messages in database.
1138 --
1139 -- Desc Accepts request_id and index as input
1140 -- Reads the message from stack based on the index and inserts the
1141 -- same in OE_PROCESSING_MSGS. This procedure resolvs message
1142 -- text before inserting in database.
1143 --
1144 --
1145 procedure insert_message (
1146 p_msg_index IN NUMBER
1147 ,p_request_id IN NUMBER
1148 ,p_message_source_code IN VARCHAR2)
1149 IS
1150 l_msg_data VARCHAR2(2000);
1151 l_entity_code VARCHAR2(30);
1152 l_entity_ref VARCHAR2(50);
1153 l_entity_id NUMBER;
1154 l_header_id NUMBER;
1155 l_line_id NUMBER;
1156 l_order_source_id NUMBER;
1157 l_orig_sys_document_ref VARCHAR2(50);
1158 l_orig_sys_line_ref VARCHAR2(50);
1159 l_orig_sys_shipment_ref VARCHAR2(50);
1160 l_change_sequence VARCHAR2(50);
1161 l_source_document_type_id NUMBER;
1162 l_source_document_id NUMBER;
1163 l_source_document_line_id NUMBER;
1164 l_attribute_code VARCHAR2(30);
1165 l_constraint_id NUMBER;
1166 l_process_activity NUMBER;
1167 l_transaction_id NUMBER;
1168 l_notification_flag VARCHAR2(1) := 'N' ;
1169 l_type VARCHAR2(30) ;
1170 l_org_id NUMBER;
1171 l_order_number NUMBER;
1172 l_line_number VARCHAR2(30);
1173
1174 --
1175 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1176 --
1177 BEGIN
1178
1179
1180 IF G_msg_tbl(p_msg_index).message_text IS NOT NULL THEN
1181 l_msg_data := G_msg_tbl(p_msg_index).message_text;
1182 ELSE
1183 l_msg_data := Get(p_msg_index, 'F');
1184 END IF;
1185 l_org_id := G_msg_tbl(p_msg_index).ORG_ID;
1186
1187 Get_msg_context(
1188 p_msg_index => p_msg_index
1189 ,x_entity_code => l_entity_code
1190 ,x_entity_ref => l_entity_ref
1191 ,x_entity_id => l_entity_id
1192 ,x_header_id => l_header_id
1193 ,x_line_id => l_line_id
1194 ,x_order_source_id => l_order_source_id
1195 ,x_orig_sys_document_ref => l_orig_sys_document_ref
1196 ,x_orig_sys_line_ref => l_orig_sys_line_ref
1197 ,x_orig_sys_shipment_ref => l_orig_sys_shipment_ref
1198 ,x_change_sequence => l_change_sequence
1199 ,x_source_document_type_id => l_source_document_type_id
1200 ,x_source_document_id => l_source_document_id
1201 ,x_source_document_line_id => l_source_document_line_id
1202 ,x_attribute_code => l_attribute_code
1203 ,x_constraint_id => l_constraint_id
1204 ,x_process_activity => l_process_activity
1205 ,x_notification_flag => l_notification_flag
1206 ,x_type => l_type
1207 );
1208
1209 IF l_debug_level > 0 THEN
1210 oe_debug_pub.add( 'L_MSG_DATA='||L_MSG_DATA,3 ) ;
1211 END IF;
1212
1213 if nvl(fnd_profile.value('CONC_REQUEST_ID'),0) > 0 then
1214 BEGIN --bug 7184951
1215 IF l_header_id IS NOT NULL
1216 AND NOT OE_GLOBALS.Equal(l_header_id,g_header_id) THEN
1217 SELECT order_number
1218 INTO l_order_number
1219 FROM oe_order_headers_all
1220 WHERE header_id = l_header_id;
1221 g_order_number := l_order_number;
1222 g_header_id := l_header_id;
1223
1224 END IF;
1225 l_line_number := OE_ORDER_MISC_PUB.GET_CONCAT_LINE_NUMBER(l_line_id);
1226
1227 FND_FILE.put_line(FND_FILE.LOG,'Order Number :'||g_order_number ||' Line Number :'||l_line_number);
1228 FND_FILE.put_line(FND_FILE.LOG,'Message :'|| l_msg_data);
1229 EXCEPTION ---start bug 7184951
1230 WHEN OTHERS THEN
1231 IF l_debug_level > 0 THEN
1232 oe_debug_pub.add('Error in Procedure insert_message ' || sqlerrm);
1233 oe_debug_pub.add('Order number not found');
1234 END IF;
1235 /*End bug 7184951*/
1236 END ;
1237
1238
1239 end if;
1240
1241
1242 if p_msg_index IS NOT NULL then
1243
1244 BEGIN
1245
1246 SELECT oe_msg_id_S.NEXTVAL
1247 INTO l_transaction_id
1248 FROM dual;
1249
1250 END;
1251
1252 insert into OE_PROCESSING_MSGS
1253 ( Transaction_id
1254 ,request_Id
1255 -- ,message_text
1256 ,entity_code
1257 ,entity_ref
1258 ,entity_id
1259 ,header_id
1260 ,line_id
1261 ,order_source_id
1262 ,original_sys_document_ref
1263 ,original_sys_document_line_ref
1264 ,orig_sys_shipment_ref
1265 ,change_sequence
1266 ,source_document_type_id
1267 ,source_document_id
1268 ,source_document_line_id
1269 ,attribute_code
1270 ,creation_date
1271 ,created_by
1272 ,last_update_date
1273 ,last_updated_by
1274 ,last_update_login
1275 ,program_application_id
1276 ,program_id
1277 ,program_update_date
1278 ,process_activity
1279 ,notification_flag
1280 ,type
1281 ,message_source_code
1282 ,message_status_code
1283 ,org_id
1284 ) VALUES
1285 ( l_transaction_id
1286 ,p_request_id
1287 -- ,l_msg_data
1288 ,l_entity_code
1289 ,l_entity_ref
1290 ,l_entity_id
1291 ,l_header_id
1292 ,l_line_id
1293 ,l_order_source_id
1294 ,l_orig_sys_document_ref
1295 ,l_orig_sys_line_ref
1296 ,l_orig_sys_shipment_ref
1297 ,l_change_sequence
1298 ,l_source_document_type_id
1299 ,l_source_document_id
1300 ,l_source_document_line_id
1301 ,l_attribute_code
1302 ,sysdate
1303 ,FND_GLOBAL.USER_ID
1304 ,sysdate
1305 ,FND_GLOBAL.USER_ID
1306 ,decode(p_message_source_code,'C',FND_GLOBAL.CONC_LOGIN_ID,FND_GLOBAL.LOGIN_ID)
1307 ,NULL
1308 ,NULL
1309 ,NULL
1310 ,l_process_activity
1311 ,l_notification_flag
1312 ,l_type
1313 ,p_message_source_code
1314 ,'OPEN'
1315 ,nvl(l_org_id,MO_GLOBAL.get_current_org_id)
1316 );
1317
1318 BEGIN
1319
1320 INSERT INTO OE_PROCESSING_MSGS_TL
1321 (Transaction_id
1322 ,language
1323 ,source_lang
1324 ,message_text
1325 ,created_by
1326 ,creation_date
1327 ,last_updated_by
1328 ,last_update_date
1329 ,last_update_login
1330 )
1331 SELECT
1332 l_transaction_id
1333 ,l.language_code
1334 ,USERENV('LANG')
1335 ,l_msg_data
1336 ,FND_GLOBAL.USER_ID
1337 ,sysdate
1338 ,FND_GLOBAL.USER_ID
1339 ,sysdate
1340 ,decode(p_message_source_code,'C',FND_GLOBAL.CONC_LOGIN_ID,FND_GLOBAL.LOGIN_ID)
1341 FROM fnd_languages l
1342 WHERE l.installed_flag in ('I','B')
1343 AND language_code = USERENV('LANG')
1344 AND not exists
1345 (SELECT null
1346 FROM oe_processing_msgs_tl t
1347 WHERE t.transaction_id = l_transaction_id
1348 AND t.language = l.language_code);
1349
1350 END;
1351
1352 G_msg_tbl(p_msg_index).processed := 'Y';
1353 end if;
1354 EXCEPTION
1355 WHEN OTHERS THEN
1356 IF l_debug_level > 0 THEN
1357 oe_debug_pub.add('Error in Procedure insert_message ' || sqlerrm);
1358 END IF;
1359 End insert_message;
1360
1361
1362 -- PROCEDURE Get_msg_tbl
1363 --
1364 -- Usage Used by process messages form to retreive messages from stack .
1365 --
1366 -- Desc This procedure returns message_table to the caller.
1367 -- This procedure also resolvs message text before returning
1368 -- message table to the caller.
1369 --
1370 --
1371 PROCEDURE Get_msg_tbl(x_msg_tbl IN OUT NOCOPY /* file.sql.39 change */ msg_tbl_type)
1372 IS
1373 --
1374 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1375 --
1376 BEGIN
1377
1378 FOR i IN G_msg_tbl.FIRST .. G_msg_tbl.LAST LOOP
1379
1380
1381 IF G_msg_tbl(i).message_text IS NOT NULL THEN
1382
1383 x_msg_tbl(i).message := G_msg_tbl(i).message_text;
1384
1385 ELSE
1386
1387 x_msg_tbl(i).message := GET(i,'F');
1388
1389 END IF;
1390
1391 -- x_msg_tbl(i).message := GET(i,'F');
1392 -- x_msg_tbl(i).message := G_msg_tbl(i).message;
1393 x_msg_tbl(i).entity_code := G_msg_tbl(i).entity_code;
1394 x_msg_tbl(i).entity_id := G_msg_tbl(i).entity_id;
1395 x_msg_tbl(i).header_id := G_msg_tbl(i).header_id;
1396 x_msg_tbl(i).line_id := G_msg_tbl(i).line_id;
1397 x_msg_tbl(i).order_source_id := G_msg_tbl(i).order_source_id;
1398 x_msg_tbl(i).orig_sys_document_ref := G_msg_tbl(i).orig_sys_document_ref;
1399 x_msg_tbl(i).orig_sys_document_line_ref := G_msg_tbl(i).orig_sys_document_line_ref;
1400 x_msg_tbl(i).source_document_type_id := G_msg_tbl(i).source_document_type_id;
1401 x_msg_tbl(i).source_document_id := G_msg_tbl(i).source_document_id;
1402 x_msg_tbl(i).source_document_line_id := G_msg_tbl(i).source_document_line_id;
1403 x_msg_tbl(i).attribute_code := G_msg_tbl(i).attribute_code;
1404 x_msg_tbl(i).constraint_id := G_msg_tbl(i).constraint_id;
1405 x_msg_tbl(i).process_activity := G_msg_tbl(i).process_activity;
1406 x_msg_tbl(i).notification_flag := G_msg_tbl(i).notification_flag;
1407 x_msg_tbl(i).message_text := null;
1408 x_msg_tbl(i).type := G_msg_tbl(i).type;
1409 x_msg_tbl(i).processed := g_msg_tbl(i).processed;
1410 x_msg_tbl(i).org_id := g_msg_tbl(i).org_id;
1411
1412 END LOOP;
1413 EXCEPTION
1414 WHEN OTHERS THEN
1415 IF l_debug_level > 0 THEN
1416 oe_debug_pub.add('Error in Procedure get_msg_tbl ' || sqlerrm);
1417 END IF;
1418
1419 END;
1420 /* Added the below procedure for bug 4716444 */
1421 PROCEDURE Populate_Msg_tbl ( p_msg_tbl IN msg_tbl_type )
1422 IS
1423 BEGIN
1424 FOR i IN p_msg_tbl.first .. p_msg_tbl.last LOOP
1425 --Increment message count
1426 G_msg_count := G_msg_count + 1;
1427
1428 -- G_msg_tbl(G_msg_count):= p_msg_tbl(i);
1429 --Add the message
1430 G_msg_tbl(G_msg_count).message_text := p_msg_tbl(i).message;
1431 G_msg_tbl(G_msg_count).entity_code := p_msg_tbl(i).entity_code;
1432 G_msg_tbl(G_msg_count).entity_id := p_msg_tbl(i).entity_id;
1433 G_msg_tbl(G_msg_count).header_id := p_msg_tbl(i).header_id;
1434 G_msg_tbl(G_msg_count).line_id := p_msg_tbl(i).line_id;
1435 G_msg_tbl(G_msg_count).order_source_id := p_msg_tbl(i).order_source_id;
1436 G_msg_tbl(G_msg_count).orig_sys_document_ref := p_msg_tbl(i).orig_sys_document_ref;
1437 G_msg_tbl(G_msg_count).orig_sys_document_line_ref := p_msg_tbl(i).orig_sys_document_line_ref;
1438 G_msg_tbl(G_msg_count).source_document_type_id := p_msg_tbl(i).source_document_type_id;
1439 G_msg_tbl(G_msg_count).source_document_id := p_msg_tbl(i).source_document_id;
1440 G_msg_tbl(G_msg_count).source_document_line_id := p_msg_tbl(i).source_document_line_id;
1441 G_msg_tbl(G_msg_count).attribute_code := p_msg_tbl(i).attribute_code;
1442 G_msg_tbl(G_msg_count).constraint_id := p_msg_tbl(i).constraint_id;
1443 G_msg_tbl(G_msg_count).process_activity := p_msg_tbl(i).process_activity;
1444 G_msg_tbl(G_msg_count).notification_flag := p_msg_tbl(i).notification_flag;
1445 G_msg_tbl(G_msg_count).type := p_msg_tbl(i).type;
1446 G_msg_tbl(G_msg_count).processed := p_msg_tbl(i).processed;
1447 G_msg_tbl(G_msg_count).org_id := p_msg_tbl(i).org_id;
1448
1449
1450 END LOOP;
1451
1452 END;
1453 /* End of code for bug 4716444 */
1454
1455 -- PROCEDURE Save_UI_Messages.
1456 --
1457 -- Usage Used by process messages form to store messages in database
1458 -- by using autonomous transaction .
1459 --
1460 -- Desc This procedure is set for autonomous transaction.
1461 -- This procedure calls save_messages procedure to insert messages
1462 -- in OE_PROCESSING_MSGS.
1463 -- This procedure accepts request_id as input from the caller
1464 --
1465 -- Note This procedure uses autonomous transaction.That means
1466 -- commit or rollback with in this procedure will not affect
1467 -- the callers transaction.
1468
1469
1470 PROCEDURE Save_UI_Messages(p_request_id IN NUMBER
1471 ,p_message_source_code IN VARCHAR2)
1472 IS
1473 Pragma AUTONOMOUS_TRANSACTION;
1474 --
1475 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1476 --
1477 BEGIN
1478
1479 Save_Messages(p_request_id
1480 ,p_message_source_code);
1481 COMMIT;
1482 EXCEPTION
1483 WHEN OTHERS THEN
1484 IF l_debug_level > 0 THEN
1485 oe_debug_pub.add('Error in Procedure save_ui_messages ' || sqlerrm);
1486 END IF;
1487 END Save_UI_Messages;
1488
1489 -- PROCEDURE Update_notification_flag.
1490 --
1491 -- Usage Used by process messages form to set notification_flag
1492 -- for batch program generated messages.
1493 --
1494 -- Desc This procedure is set for autonomous transaction.
1495 -- This procedure accepts transaction_id and updates
1496 -- the OE_PROCESSING_MSGS.
1497 --
1498 -- Note This procedure uses autonomous transaction.That means
1499 -- commit or rollback with in this procedure will not affect
1500 -- the callers transaction.
1501
1502 PROCEDURE Update_Notification_Flag(p_transaction_id IN NUMBER)
1503 IS
1504 Pragma AUTONOMOUS_TRANSACTION;
1505 --
1506 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1507 --
1508 BEGIN
1509
1510 UPDATE oe_processing_msgs
1511 SET notification_flag = 'Y'
1512 WHERE transaction_id = p_transaction_id;
1513
1514 COMMIT;
1515 EXCEPTION
1516 WHEN OTHERS THEN
1517 IF l_debug_level > 0 THEN
1518 oe_debug_pub.add('Error in Procedure Update_Notification_Flag ' || sqlerrm);
1519 END IF;
1520 END Update_Notification_Flag;
1521
1522 -- PROCEDURE Update_UI_notification_flag.
1523 --
1524 -- Usage Used by process messages form to set notification_flag
1525 -- for UI generated messages.
1526 --
1527 -- Desc This procedure accepts stack index and updates
1528 -- the msg stack.
1529 --
1530
1531 PROCEDURE Update_UI_Notification_Flag(p_msg_ind IN NUMBER)
1532 IS
1533 --
1534 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1535 --
1536 BEGIN
1537
1538 G_msg_tbl(p_msg_ind).Notification_flag := 'Y';
1539 EXCEPTION
1540 WHEN OTHERS THEN
1541 IF l_debug_level > 0 THEN
1542 oe_debug_pub.add('Error in Procedure Update_UI_Notification_Flag ' || sqlerrm);
1543 END IF;
1544
1545 END Update_UI_Notification_Flag;
1546
1547
1548 -- FUNCTION Get_Single_Message
1549 --
1550 -- Usage Used by form developers when one process message
1551 -- to be shown on the screen.
1552 --
1553 -- Desc This function makes sure that there is only one
1554 -- message in the stack and returns the message based
1555 -- on the constraint_id. If the constraint_id is null
1556 -- then only the message will be return to the caller
1557 -- , Otherwise message will be returned with resolving
1558 -- responsibilities.
1559 --
1560
1561 FUNCTION Get_Single_Message
1562 (
1563 x_return_status OUT NOCOPY VARCHAR2
1564
1565 )
1566 RETURN VARCHAR2
1567 IS
1568
1569 l_WF_Roles_Tbl OE_PC_GLOBALS.Authorized_WF_Roles_TBL;
1570 l_return_status VARCHAR2(1);
1571 l_message VARCHAR2(2000);
1572 l_constraint_id NUMBER;
1573 l_msg_length NUMBER := 0;
1574 --
1575 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1576 --
1577 BEGIN
1578
1579 x_return_status := FND_API.G_RET_STS_SUCCESS;
1580
1581 IF OE_Msg_pub.Count_Msg <> 1 THEN
1582
1583 x_return_status := FND_API.G_RET_STS_ERROR;
1584
1585 ELSE
1586
1587
1588 IF G_msg_tbl(1).message_text IS NOT NULL THEN
1589
1590 l_message := G_msg_tbl(1).message_text;
1591
1592 ELSE
1593
1594 l_message := GET(1,'F');
1595
1596 END IF;
1597
1598
1599 l_constraint_id := G_msg_tbl(1).constraint_id;
1600
1601 IF l_constraint_id IS NOT NULL THEN
1602
1603 l_WF_Roles_Tbl := Oe_PC_Constraints_Admin_Pub.Get_Authorized_WF_Roles
1604 (p_constraint_id => l_constraint_id
1605 ,x_return_status => l_return_status);
1606
1607 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1608 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1609 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1610 RAISE FND_API.G_EXC_ERROR;
1611 END IF;
1612
1613 l_msg_length := length(l_message);
1614
1615 IF l_msg_length < 1600 THEN
1616
1617 FOR i IN l_WF_Roles_Tbl.FIRST .. l_WF_Roles_Tbl.LAST LOOP
1618
1619 l_msg_length := l_msg_length + nvl(length(l_WF_Roles_Tbl(i).display_name),0);
1620
1621 IF l_msg_length <= 1600 THEN
1622
1623 l_message := l_message || ' , ' || l_WF_Roles_Tbl(i).display_name;
1624
1625 ELSE
1626
1627 Delete_msg;
1628 RETURN l_message;
1629
1630 END IF;
1631
1632 END LOOP;
1633
1634 END IF;
1635
1636 END IF;
1637
1638 END IF;
1639
1640 Delete_msg;
1641 RETURN l_message;
1642
1643 EXCEPTION
1644
1645 WHEN OTHERS THEN
1646 /* l_message := 'Something wrong';
1647 Return l_message;*/
1648 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1649
1650
1651 END Get_Single_Message;
1652
1653
1654 -- PROCEDURE Delete_Message
1655 --
1656 -- Usage Used by process message form to delete queried
1657 -- messages.
1658 --
1659 -- Desc This procedure accepts where clause parameters
1660 -- from the caller and deletes data from OE_PROCESSING_MSGS_TL
1661 -- and OE_PROCESSING_MSGS.
1662 --
1663
1664 PROCEDURE DELETE_MESSAGE
1665 (p_message_source_code IN VARCHAR2 DEFAULT NULL
1666 ,p_request_id_from IN NUMBER DEFAULT NULL
1667 ,p_request_id_to IN NUMBER DEFAULT NULL
1668 ,p_order_number_from IN NUMBER DEFAULT NULL
1669 ,p_order_number_to IN NUMBER DEFAULT NULL
1670 ,p_creation_date_from IN VARCHAR2 DEFAULT NULL
1671 ,p_creation_date_to IN VARCHAR2 DEFAULT NULL
1672 ,p_program_id IN NUMBER DEFAULT NULL
1673 ,p_process_activity_name IN VARCHAR2 DEFAULT NULL
1674 ,p_order_type_id IN NUMBER DEFAULT NULL
1675 ,p_attribute_code IN VARCHAR2 DEFAULT NULL
1676 ,p_organization_id IN NUMBER DEFAULT NULL
1677 ,p_created_by IN NUMBER DEFAULT NULL)
1678
1679
1680 IS
1681 /* These types and variables introduced to fix 1922443 */
1682 TYPE Transactionidtab is TABLE OF oe_processing_msgs.transaction_id%TYPE;
1683 TYPE Transactionrowidtab is TABLE OF varchar2(100);
1684 Transactionids Transactionidtab := Transactionidtab();
1685 Transactionrowids Transactionrowidtab := Transactionrowidtab();
1686
1687 l_stmt VARCHAR2(4000) :=NULL;
1688 l_cursor_id INTEGER;
1689 l_retval INTEGER;
1690 J NUMBER := 0;
1691 d NUMBER;
1692 l_transaction_id NUMBER;
1693 l_creation_date_from DATE;
1694 l_creation_date_to DATE;
1695 --
1696 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1697 --
1698 BEGIN
1699 IF l_debug_level > 0 THEN
1700 oe_debug_pub.add( 'IN OE_MSG_PUB.DELETE_MESSAGE' ) ;
1701 END IF;
1702 /* 1922443 */
1703 /* select msg.rowid,
1704 msg.transaction_id
1705 bulk collect
1706 into transactionrowids,
1707 transactionids
1708 from oe_processing_msgs msg,
1709 oe_order_headers hdr,
1710 wf_process_activities wpa,
1711 wf_activities_tl wa
1712 Where msg.header_id = hdr.header_id (+)
1713 AND msg.process_activity = wpa.instance_id(+)
1714 AND wpa.activity_name = wa.name(+)
1715 AND wpa.activity_item_type =wa.item_type(+)
1716 AND wpa.process_version = wa.version(+)
1717 AND wa.language(+) = USERENV('LANG')
1718 AND nvl(msg.message_source_code,0) = nvl(p_message_source_code
1719 ,nvl(msg.message_source_code,0))
1720 AND nvl(msg.request_id,0) between nvl(p_request_id_from
1721 ,nvl(msg.request_id,0))
1722 and nvl(p_request_id_to
1723 ,nvl(msg.request_id,0))
1724 AND nvl(hdr.order_number,0) between nvl(p_order_number_from
1725 ,nvl(hdr.order_number,0))
1726 and nvl(p_order_number_to
1727 ,nvl(hdr.order_number,0))
1728 AND msg.creation_date between nvl(p_creation_date_from
1729 ,msg.creation_date)
1730 and nvl(p_creation_date_to
1731 ,msg.creation_date)
1732 AND nvl(msg.program_id,0) = nvl(p_program_id
1733 ,nvl(msg.program_id,0))
1734 AND nvl(wa.display_name,0) = nvl(p_process_activity_name
1735 ,nvl(wa.display_name,0))
1736 AND nvl(hdr.order_type_id,0) = nvl(p_order_type_id
1737 ,nvl(hdr.order_type_id,0))
1738 AND nvl(msg.attribute_code,0) = nvl(p_attribute_code
1739 ,nvl(msg.attribute_code,0))
1740 AND nvl(hdr.sold_to_org_id,0) = nvl(p_organization_id
1741 ,nvl(hdr.sold_to_org_id,0))
1742 AND msg.created_by = nvl(p_created_by
1743 ,msg.created_by);
1744 */
1745
1746
1747 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1748
1749
1750 IF p_order_type_id is null
1751 AND p_order_number_from is null
1752 AND p_order_number_to is null
1753 AND p_organization_id is null
1754 AND p_process_activity_name is null
1755
1756 THEN
1757
1758 l_stmt := 'select transaction_id ' ||
1759 ' from oe_processing_msgs msg';
1760
1761 l_stmt := l_stmt ||' WHERE 1 = 1';
1762 ELSIF p_process_activity_name is null
1763 THEN
1764
1765 l_stmt := 'select transaction_id ' ||
1766 ' from oe_processing_msgs msg, oe_order_headers_all hdr';
1767
1768 l_stmt := l_stmt ||' WHERE msg.header_id = hdr.header_id';
1769 ELSE
1770
1771 l_stmt := 'select transaction_id ' ||
1772 ' from oe_processing_msgs msg, oe_order_headers hdr' ||
1773 ',wf_process_activities wpa, wf_activities_tl wa ';
1774
1775 l_stmt := l_stmt ||' WHERE msg.header_id = hdr.header_id' ||
1776 ' AND msg.process_activity = wpa.instance_id(+)' ||
1777 ' AND wpa.activity_name = wa.name(+)' ||
1778 ' AND wpa.activity_item_type =wa.item_type(+)' ||
1779 ' AND wpa.process_version = wa.version(+)' ||
1780 ' AND wa.language(+) = USERENV('||'''LANG''' ||')';
1781 END IF;
1782
1783
1784 IF p_message_source_code IS NOT NULL THEN
1785 l_stmt := l_stmt ||' AND msg.message_source_code =:message_source_code';
1786 END IF;
1787
1788 IF p_request_id_from IS NOT NULL THEN
1789 l_stmt := l_stmt ||' AND msg.request_id >= :request_id_from';
1790 END IF;
1791
1792 IF p_request_id_to IS NOT NULL THEN
1793 l_stmt := l_stmt ||' AND msg.request_id <= :request_id_to';
1794 END IF;
1795 --5121760
1796 IF p_creation_date_from IS NOT NULL THEN
1797 l_stmt := l_stmt ||' AND trunc(msg.creation_date) >= :creation_date_from';
1798 END IF;
1799
1800 IF p_creation_date_to IS NOT NULL THEN
1801 l_stmt := l_stmt ||' AND trunc(msg.creation_date) <= :creation_date_to';
1802 END IF;
1803
1804 IF p_program_id IS NOT NULL THEN
1805 l_stmt := l_stmt ||' AND msg.program_id = :program_id';
1806 END IF;
1807
1808 IF p_attribute_code IS NOT NULL THEN
1809 l_stmt := l_stmt ||' AND msg.attribute_code = :attribute_code';
1810 END IF;
1811
1812 IF p_created_by IS NOT NULL THEN
1813 l_stmt := l_stmt ||' AND msg.created_by = :created_by'; --Bug # 5398729
1814 END IF;
1815
1816 IF p_order_number_from IS NOT NULL THEN
1817 l_stmt := l_stmt ||' AND hdr.order_number >= :order_number_from';
1818 END IF;
1819
1820 IF p_order_number_to IS NOT NULL THEN
1821 l_stmt := l_stmt ||' AND hdr.order_number <= :order_number_to';
1822 END IF;
1823
1824 IF p_order_type_id IS NOT NULL THEN
1825 l_stmt := l_stmt ||' AND hdr.order_type_id = :order_type_id';
1826 END IF;
1827
1828 IF p_organization_id IS NOT NULL THEN
1829 l_stmt := l_stmt ||' AND hdr.sold_to_org_id = :sold_to_org_id';
1830 END IF;
1831
1832 IF p_process_activity_name IS NOT NULL THEN
1833 l_stmt := l_stmt ||' AND wa.display_name = :process_activity_name';
1834 END IF;
1835
1836 OE_DEBUG_PUB.Add(substr(l_stmt,1,length(l_stmt)),1);
1837
1838 DBMS_SQL.PARSE(l_cursor_id, l_stmt, DBMS_SQL.native);
1839
1840 OE_DEBUG_PUB.Add('after parse',1);
1841 --5121760
1842 SELECT fnd_date.chardt_to_date(p_creation_date_from),
1843 fnd_date.chardt_to_date(p_creation_date_to)
1844 INTO l_creation_date_from,l_creation_date_to
1845 FROM DUAL;
1846 IF p_message_source_code IS NOT NULL THEN
1847 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':message_source_code',p_message_source_code);
1848 END IF;
1849
1850 IF p_request_id_from IS NOT NULL THEN
1851 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':request_id_from',p_request_id_from);
1852 END IF;
1853
1854 IF p_request_id_to IS NOT NULL THEN
1855 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':request_id_to',p_request_id_to);
1856 END IF;
1857 --5121760
1858 IF p_creation_date_from IS NOT NULL THEN
1859 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':creation_date_from',trunc(l_creation_date_from));
1860 END IF;
1861 IF p_creation_date_to IS NOT NULL THEN
1862 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':creation_date_to',trunc(l_creation_date_to));
1863 END IF;
1864
1865 IF p_program_id IS NOT NULL THEN
1866 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':program_id',p_program_id);
1867 END IF;
1868
1869 IF p_attribute_code IS NOT NULL THEN
1870 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':attribute_code',p_attribute_code);
1871 END IF;
1872
1873 IF p_created_by IS NOT NULL THEN
1874 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':created_by',p_created_by);
1875 END IF;
1876
1877 IF p_order_number_from IS NOT NULL THEN
1878 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':order_number_from',p_order_number_from);
1879 END IF;
1880
1881 IF p_order_number_to IS NOT NULL THEN
1882 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':order_number_to',p_order_number_to);
1883 END IF;
1884
1885 IF p_order_type_id IS NOT NULL THEN
1886 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':order_type_id',p_order_type_id);
1887 END IF;
1888
1889 IF p_organization_id IS NOT NULL THEN
1890 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':sold_to_org_id',p_organization_id);
1891 END IF;
1892
1893 IF p_process_activity_name IS NOT NULL THEN
1894 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':process_activity_name',p_process_activity_name);
1895 END IF;
1896
1897 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,1,l_transaction_id);
1898 oe_debug_pub.add('Before execute ',1);
1899 l_retval := DBMS_SQL.EXECUTE(l_cursor_id);
1900
1901 LOOP
1902 oe_debug_pub.add('J: ' || J,1);
1903
1904 IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN
1905 EXIT;
1906 END IF;
1907
1908 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1, l_transaction_id);
1909 J := J +1 ;
1910
1911 oe_debug_pub.add('l_transaction_id: ' || l_transaction_id,1);
1912 oe_debug_pub.add('J1: ' || J,1);
1913
1914 Transactionids.extend(1);
1915 Transactionids(J) := l_transaction_id;
1916
1917 END LOOP;
1918
1919 oe_debug_pub.add('Before Close ',1);
1920 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
1921
1922
1923 oe_debug_pub.add('Count: ' || Transactionids.COUNT,1);
1924
1925 FORALL J in 1..Transactionids.COUNT
1926 Delete
1927 from oe_processing_msgs_tl
1928 Where transaction_id = Transactionids(J);
1929
1930 FORALL J in 1..Transactionids.COUNT
1931 Delete
1932 from oe_processing_msgs
1933 Where transaction_id = Transactionids(J);
1934
1935 /*
1936 FORALL J in 1..Transactionrowids.COUNT
1937 Delete
1938 from oe_processing_msgs
1939 Where rowid = Transactionrowids(J);
1940 */
1941 Transactionids.DELETE;
1942 Transactionrowids.DELETE;
1943
1944 commit;
1945
1946 IF l_debug_level > 0 THEN
1947 oe_debug_pub.add( 'EXITING OE_MSG_PUB.DELETE_MESSAGE' ) ;
1948 END IF;
1949
1950 EXCEPTION
1951
1952 WHEN OTHERS THEN
1953
1954 oe_debug_pub.add ('error :' || sqlerrm,1);
1955 oe_debug_pub.add (l_stmt,1);
1956 END DELETE_MESSAGE;
1957
1958 PROCEDURE DELETE_OI_MESSAGE
1959 (p_request_id IN NUMBER DEFAULT NULL
1960 ,p_order_source_id IN NUMBER DEFAULT NULL
1961 ,p_orig_sys_document_ref IN VARCHAR2 DEFAULT NULL
1962 ,p_change_sequence IN VARCHAR2 DEFAULT NULL
1963 ,p_orig_sys_document_line_ref IN VARCHAR2 DEFAULT NULL
1964 ,p_orig_sys_shipment_ref IN VARCHAR2 DEFAULT NULL
1965 ,p_entity_code IN VARCHAR2 DEFAULT NULL
1966 ,p_entity_ref IN VARCHAR2 DEFAULT NULL
1967 ,p_org_id IN NUMBER DEFAULT NULL)
1968
1969 IS
1970 /* Replaced with the following to fix 1922443
1971 TYPE Transaction_tab is TABLE OF oe_processing_msgs.transaction_id%TYPE;
1972 Transactions_oi Transaction_tab;
1973 */
1974
1975 TYPE Transactionidtab is TABLE OF oe_processing_msgs.transaction_id%TYPE;
1976 TYPE Transactionrowidtab is TABLE OF varchar2(100);
1977 Transactionids Transactionidtab := Transactionidtab();
1978 Transactionrowids Transactionrowidtab := Transactionrowidtab();
1979
1980 l_stmt VARCHAR2(4000) :=NULL;
1981 l_cursor_id INTEGER;
1982 l_retval INTEGER;
1983 J NUMBER := 0;
1984 d NUMBER;
1985 l_transaction_id NUMBER;
1986
1987 --
1988 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1989 --
1990 BEGIN
1991
1992 IF l_debug_level > 0 THEN
1993 oe_debug_pub.add( 'IN OE_MSG_PUB.DELETE_OI_MESSAGE' ) ;
1994 END IF;
1995 /* Replaced with the following for 1922443
1996 Select transaction_id
1997 Bulk Collect Into transactions_oi
1998
1999 select rowid,
2000 transaction_id
2001 bulk collect
2002 into transactionrowids,
2003 transactionids
2004 from oe_processing_msgs
2005 Where nvl(request_id,0) = nvl(p_request_id,
2006 nvl(request_id,0))
2007 Removed nvl condition as we expect order_source_id and
2008 original_sys_document_ref to be passed # 2467558
2009 And order_source_id = p_order_source_id
2010 And original_sys_document_ref = p_orig_sys_document_ref
2011 And nvl(Original_sys_document_line_ref,0) =
2012 nvl(p_Orig_sys_document_line_ref,
2013 nvl(Original_sys_document_line_ref,0))
2014 And nvl(orig_sys_shipment_ref,0) = nvl(p_orig_sys_shipment_ref,
2015 nvl(orig_sys_shipment_ref,0))
2016 And nvl(change_sequence,0) = nvl(p_change_sequence,
2017 nvl(change_sequence,0))
2018 And nvl(entity_code,0) = nvl(p_entity_code,
2019 nvl(entity_code,0))
2020 And nvl(entity_ref,0) = nvl(p_entity_ref,
2021 nvl(entity_ref,0));
2022
2023 Replaced with the following to fix 1922443
2024 FORALL J in 1..Transactions_oi.COUNT
2025 Delete
2026 from oe_processing_msgs_tl
2027 Where transaction_id = Transactions_oi(J);
2028
2029
2030 FORALL J in 1..Transactions_oi.COUNT
2031 Delete
2032 from oe_processing_msgs
2033 Where transaction_id = Transactions_oi(J);
2034
2035 Transactions_oi.DELETE;
2036
2037
2038 FORALL J in 1..Transactionids.COUNT
2039 Delete
2040 from oe_processing_msgs_tl
2041 Where transaction_id = Transactionids(J);
2042
2043
2044 FORALL J in 1..Transactionrowids.COUNT
2045 Delete
2046 from oe_processing_msgs
2047 Where rowid = Transactionrowids(J);
2048
2049
2050 Transactionids.DELETE;
2051 Transactionrowids.DELETE;
2052 commit;
2053 */
2054
2055 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
2056
2057
2058
2059 l_stmt := 'select transaction_id ' ||
2060 ' from oe_processing_msgs';
2061
2062
2063
2064 l_stmt := l_stmt ||' WHERE order_source_id = :order_source_id';
2065
2066 IF p_request_id IS NOT NULL THEN
2067 l_stmt := l_stmt ||' AND request_id = :request_id';
2068 END IF;
2069
2070 IF p_orig_sys_document_ref IS NOT NULL THEN
2071 l_stmt := l_stmt ||' AND original_sys_document_ref = :orig_sys_document_ref';
2072 END IF;
2073
2074 IF p_Orig_sys_document_line_ref IS NOT NULL THEN
2075 l_stmt := l_stmt ||' AND Original_sys_document_line_ref = :Orig_sys_document_line_ref';
2076 END IF;
2077
2078 IF p_orig_sys_shipment_ref IS NOT NULL THEN
2079 l_stmt := l_stmt ||' AND orig_sys_shipment_ref = :orig_sys_shipment_ref';
2080 END IF;
2081
2082 IF p_change_sequence IS NOT NULL THEN
2083 l_stmt := l_stmt ||' AND change_sequence = :change_sequence';
2084 END IF;
2085
2086 IF p_entity_code IS NOT NULL THEN
2087 l_stmt := l_stmt ||' AND entity_code = :entity_code';
2088 END IF;
2089
2090 IF p_entity_ref IS NOT NULL THEN
2091 l_stmt := l_stmt ||' AND entity_ref = :entity_ref';
2092 END IF;
2093
2094 IF p_org_id IS NOT NULL THEN
2095 l_stmt := l_stmt ||' AND org_id = :org_id';
2096 END IF;
2097
2098 OE_DEBUG_PUB.Add(substr(l_stmt,1,length(l_stmt)),1);
2099
2100 DBMS_SQL.PARSE(l_cursor_id, l_stmt, DBMS_SQL.native);
2101
2102 OE_DEBUG_PUB.Add('after parse',1);
2103 IF p_order_source_id IS NOT NULL THEN
2104 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':order_source_id',p_order_source_id);
2105 END IF;
2106
2107 IF p_request_id IS NOT NULL THEN
2108 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':request_id',p_request_id);
2109 END IF;
2110
2111 IF p_orig_sys_document_ref IS NOT NULL THEN
2112 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':orig_sys_document_ref',p_orig_sys_document_ref);
2113 END IF;
2114
2115 IF p_Orig_sys_document_line_ref IS NOT NULL THEN
2116 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':Orig_sys_document_line_ref',p_Orig_sys_document_line_ref);
2117 END IF;
2118
2119 IF p_orig_sys_shipment_ref IS NOT NULL THEN
2120 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':orig_sys_shipment_ref',p_orig_sys_shipment_ref);
2121 END IF;
2122
2123 IF p_change_sequence IS NOT NULL THEN
2124 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':change_sequence',p_change_sequence);
2125 END IF;
2126
2127 IF p_entity_code IS NOT NULL THEN
2128 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':entity_code',p_entity_code);
2129 END IF;
2130
2131 IF p_entity_ref IS NOT NULL THEN
2132 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':entity_ref',p_entity_ref);
2133 END IF;
2134
2135 IF p_org_id IS NOT NULL THEN
2136 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':org_id',p_org_id);
2137 END IF;
2138
2139 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,1,l_transaction_id);
2140 oe_debug_pub.add('Before execute ',1);
2141 l_retval := DBMS_SQL.EXECUTE(l_cursor_id);
2142
2143 LOOP
2144 oe_debug_pub.add('J: ' || J,1);
2145
2146 IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN
2147 EXIT;
2148 END IF;
2149
2150 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1, l_transaction_id);
2151 J := J +1 ;
2152
2153 oe_debug_pub.add('l_transaction_id: ' || l_transaction_id,1);
2154 oe_debug_pub.add('J1: ' || J,1);
2155
2156 Transactionids.extend(1);
2157 Transactionids(J) := l_transaction_id;
2158
2159 END LOOP;
2160
2161 oe_debug_pub.add('Before Close ',1);
2162 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
2163
2164
2165 oe_debug_pub.add('Count: ' || Transactionids.COUNT,1);
2166
2167 FORALL J in 1..Transactionids.COUNT
2168 Delete
2169 from oe_processing_msgs_tl
2170 Where transaction_id = Transactionids(J);
2171
2172 FORALL J in 1..Transactionids.COUNT
2173 Delete
2174 from oe_processing_msgs
2175 Where transaction_id = Transactionids(J);
2176
2177 Transactionids.DELETE;
2178 Transactionrowids.DELETE;
2179
2180 commit;
2181
2182 IF l_debug_level > 0 THEN
2183 oe_debug_pub.add( 'EXITING OE_MSG_PUB.DELETE_OI_MESSAGE' ) ;
2184 END IF;
2185
2186 /*
2187 EXCEPTION
2188
2189 WHEN OTHERS THEN
2190
2191 oe_debug_pub.add('Error : ' || sqlerrm);*/
2192 END DELETE_OI_MESSAGE;
2193
2194 -- 4091185 - Added parameter p_type.
2195 PROCEDURE Transfer_Msg_Stack
2196 ( p_msg_index IN NUMBER DEFAULT NULL,
2197 p_type IN VARCHAR2 DEFAULT NULL)
2198 IS
2199 l_count NUMBER;
2200 l_message VARCHAR2(2000);
2201 --
2202 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2203 --
2204 BEGIN
2205
2206 IF p_msg_index IS NOT NULL THEN
2207
2208 l_message := fnd_msg_pub.get(p_msg_index,'F');
2209 add_text(p_message_text => l_message,
2210 p_type => p_type);
2211
2212 ELSE
2213
2214 l_count := fnd_msg_pub.count_msg;
2215
2216 FOR i in 1..l_count LOOP
2217
2218 l_message := fnd_msg_pub.get(i,'F');
2219 add_text(p_message_text => l_message,
2220 p_type => p_type);
2221
2222
2223 END LOOP;
2224
2225 END IF; -- p_msg_index
2226
2227 fnd_msg_pub.delete_msg; -- Adding this call to fix 4642102.
2228 EXCEPTION
2229 WHEN OTHERS THEN
2230 IF l_debug_level > 0 THEN
2231 oe_debug_pub.add('Error in Procedure Transfer_Msg_Stack ' || sqlerrm);
2232 END IF;
2233 END Transfer_Msg_Stack;
2234
2235 /* Procedure Save_API_Messages will be used by many API's to store the
2236 error messages before raising the exceptions or rolling back the transaction
2237 so that the errors are captured.
2238
2239 This API can be called from anywhere to save the messages without affecting the
2240 actual transaction. If the call is made from the concurrent program, this API will
2241 store the messages against the concurrent ID.
2242
2243 This API will also capture the sql errors if there are any.
2244
2245 Message source Code 'A' will be used for 'API' calls.
2246 */
2247
2248 PROCEDURE Save_API_Messages (p_request_id IN NUMBER DEFAULT NULL
2249 ,p_message_source_code IN VARCHAR2 DEFAULT 'A')
2250 IS
2251 Pragma AUTONOMOUS_TRANSACTION;
2252
2253 l_count_msg NUMBER;
2254 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2255 l_errmsg VARCHAR2(1000) := Null;
2256 l_request_id NUMBER := p_request_id;
2257 l_message_source_code VARCHAR2(3) := p_message_source_code;
2258
2259 BEGIN
2260
2261
2262 IF l_debug_level > 0 THEN
2263 oe_debug_pub.add( 'ENTER Save_API_Messages' , 1 ) ;
2264 END IF;
2265
2266 IF nvl(fnd_profile.value('CONC_REQUEST_ID'),0) > 0
2267 THEN
2268 l_request_id := fnd_profile.value('CONC_REQUEST_ID');
2269 l_message_source_code := 'C';
2270 END IF;
2271
2272 l_errmsg := ltrim(Substr(SQLERRM,1,1000));
2273
2274 IF l_errmsg IS NOT NULL THEN
2275 oe_debug_pub.add('l_errmsg ' || l_errmsg, 2 ) ;
2276 END IF;
2277
2278 /* Commenting the following code
2279 to fix the bug 5201283 as we do not need to
2280 show the tech messages to users
2281 We will continue to write the messages to log files
2282
2283 IF l_errmsg IS NOT NULL
2284 AND substr(l_errmsg,5,4) <> '0000'
2285 AND rtrim(ltrim(l_errmsg)) <> 'ORA-20001:'
2286 AND upper(substr(ltrim(l_errmsg),1,5)) <> 'USER-' THEN
2287 oe_debug_pub.add('l_errmsg ' || l_errmsg, 2 ) ;
2288 oe_msg_pub.add_text(p_message_text => l_errmsg);
2289 END IF;
2290
2291 */
2292 l_count_msg := OE_MSG_PUB.Count_Msg;
2293
2294 FOR I IN 1..l_count_msg LOOP
2295 IF nvl(g_msg_tbl(I).processed,'N') = 'N' THEN
2296 insert_message(I,l_request_id,l_message_source_code);
2297 END IF;
2298 End Loop;
2299
2300 IF l_debug_level > 0 THEN
2301 oe_debug_pub.add( 'EXIT Save_API_Messages' , 1 ) ;
2302 END IF;
2303
2304 COMMIT;
2305 EXCEPTION
2306 WHEN OTHERS THEN
2307 IF l_debug_level > 0 THEN
2308 oe_debug_pub.add('Error in Procedure Save_API_Messages ' || sqlerrm);
2309 END IF;
2310 END Save_API_Messages;
2311 PROCEDURE Update_status_code(
2312 p_request_id IN NUMBER DEFAULT NULL
2313 ,p_org_id IN NUMBER DEFAULT NULL
2314 ,p_entity_code IN VARCHAR2 DEFAULT NULL
2315 ,p_entity_id IN NUMBER DEFAULT NULL
2316 ,p_header_id IN NUMBER DEFAULT NULL
2317 ,p_line_id IN NUMBER DEFAULT NULL
2318 ,p_order_source_id IN NUMBER DEFAULT NULL
2319 ,p_orig_sys_document_ref IN VARCHAR2 DEFAULT NULL
2320 ,p_orig_sys_document_line_ref IN VARCHAR2 DEFAULT NULL
2321 ,p_orig_sys_shipment_ref IN VARCHAR2 DEFAULT NULL
2322 ,p_change_sequence IN VARCHAR2 DEFAULT NULL
2323 ,p_source_document_type_id IN NUMBER DEFAULT NULL
2324 ,p_source_document_id IN NUMBER DEFAULT NULL
2325 ,p_source_document_line_id IN NUMBER DEFAULT NULL
2326 ,p_attribute_code IN VARCHAR2 DEFAULT NULL
2327 ,p_constraint_id IN NUMBER DEFAULT NULL
2328 ,p_process_activity IN NUMBER DEFAULT NULL
2329 ,p_sold_to_org_id IN NUMBER DEFAULT NULL
2330 ,p_status_code IN Varchar2)
2331 IS
2332 --
2333 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2334
2335 l_transaction_id NUMBER;
2336 l_cursor_id INTEGER;
2337 l_retval INTEGER;
2338 l_stmt VARCHAR2(4000) :=NULL;
2339 --
2340 BEGIN
2341
2342 oe_debug_pub.add('p_entity_code: ' || p_entity_code,2);
2343 oe_debug_pub.add('p_entity_id: ' || p_entity_id,2);
2344 oe_debug_pub.add('p_header_id: ' || p_header_id,2);
2345 oe_debug_pub.add('p_line_id: ' || p_line_id,2);
2346 oe_debug_pub.add('p_order_source_id: ' || p_order_source_id,2);
2347 oe_debug_pub.add('p_orig_sys_document_ref: ' || p_orig_sys_document_ref,2);
2348 oe_debug_pub.add('p_process_activity: ' || p_process_activity,2);
2349 oe_debug_pub.add('p_sold_to_org_id: ' || p_sold_to_org_id,2);
2350 oe_debug_pub.add('p_status_code: ' || p_status_code,2);
2351 oe_debug_pub.add('p_request_id: ' || p_request_id,2);
2352
2353 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
2354
2355
2356 l_stmt := 'Select transaction_id from oe_processing_msgs Where 1 = 1';
2357
2358
2359 IF p_request_id IS NOT NULL THEN
2360 l_stmt := l_stmt ||' AND request_id = :request_id';
2361 END IF;
2362 IF p_entity_code IS NOT NULL THEN
2363 l_stmt := l_stmt ||' AND entity_code = :entity_code';
2364 END IF;
2365 IF p_entity_id IS NOT NULL THEN
2366 l_stmt := l_stmt ||' AND entity_id = :entity_id';
2367 END IF;
2368 IF p_header_id IS NOT NULL THEN
2369 l_stmt := l_stmt ||' AND header_id = :header_id';
2370 END IF;
2371 IF p_line_id IS NOT NULL THEN
2372 l_stmt := l_stmt ||' AND line_id = :line_id';
2373 END IF;
2374 IF p_order_source_id IS NOT NULL THEN
2375 l_stmt := l_stmt ||' AND order_source_id = :order_source_id';
2376 END IF;
2377 IF p_orig_sys_document_ref IS NOT NULL THEN
2378 l_stmt := l_stmt ||' AND original_sys_document_ref = :orig_sys_document_ref';
2379 END IF;
2380 IF p_orig_sys_document_line_ref IS NOT NULL THEN
2381 l_stmt := l_stmt ||' AND original_sys_document_line_ref = :orig_sys_document_line_ref';
2382 END IF;
2383 IF p_orig_sys_shipment_ref IS NOT NULL THEN
2384 l_stmt := l_stmt ||' AND orig_sys_shipment_ref = :orig_sys_shipment_ref';
2385 END IF;
2386 IF p_change_sequence IS NOT NULL THEN
2387 l_stmt := l_stmt ||' AND change_sequence = :change_sequence';
2388 END IF;
2389 IF p_source_document_type_id IS NOT NULL THEN
2390 l_stmt := l_stmt ||' AND source_document_type_id = :source_document_type_id';
2391 END IF;
2392 IF p_source_document_id IS NOT NULL THEN
2393 l_stmt := l_stmt ||' AND source_document_id = :source_document_id';
2394 END IF;
2395 IF p_source_document_line_id IS NOT NULL THEN
2396 l_stmt := l_stmt ||' AND source_document_line_id = :source_document_line_id';
2397 END IF;
2398 IF p_attribute_code IS NOT NULL THEN
2399 l_stmt := l_stmt ||' AND attribute_code = :attribute_code';
2400 END IF;
2401 IF p_constraint_id IS NOT NULL THEN
2402 l_stmt := l_stmt ||' AND constraint_id = :constraint_id';
2403 END IF;
2404 IF p_process_activity IS NOT NULL THEN
2405 l_stmt := l_stmt ||' AND process_activity = :process_activity';
2406 END IF;
2407 IF p_sold_to_org_id IS NOT NULL THEN
2408 l_stmt := l_stmt ||' AND sold_to_org_id = :sold_to_org_id';
2409 END IF;
2410 IF p_org_id IS NOT NULL THEN
2411 l_stmt := l_stmt ||' AND org_id = :org_id';
2412 END IF;
2413
2414 DBMS_SQL.PARSE(l_cursor_id,l_stmt,DBMS_SQL.NATIVE);
2415
2416
2417 IF p_request_id IS NOT NULL THEN
2418 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':request_id',p_request_id);
2419 END IF;
2420 IF p_entity_code IS NOT NULL THEN
2421 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':entity_code',p_entity_code);
2422 END IF;
2423 IF p_entity_id IS NOT NULL THEN
2424 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':entity_id',p_entity_id);
2425 END IF;
2426 IF p_header_id IS NOT NULL THEN
2427 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':header_id',p_header_id);
2428 END IF;
2429 IF p_line_id IS NOT NULL THEN
2430 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':line_id',p_line_id);
2431 END IF;
2432 IF p_order_source_id IS NOT NULL THEN
2433 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':order_source_id',p_order_source_id);
2434 END IF;
2435 IF p_orig_sys_document_ref IS NOT NULL THEN
2436 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':orig_sys_document_ref',p_orig_sys_document_ref);
2437 END IF;
2438 IF p_orig_sys_document_line_ref IS NOT NULL THEN
2439 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':orig_sys_document_line_ref',p_orig_sys_document_line_ref);
2440 END IF;
2441 IF p_orig_sys_shipment_ref IS NOT NULL THEN
2442 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':orig_sys_shipment_ref',p_orig_sys_shipment_ref);
2443 END IF;
2444 IF p_change_sequence IS NOT NULL THEN
2445 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':change_sequence',p_change_sequence);
2446 END IF;
2447 IF p_source_document_type_id IS NOT NULL THEN
2448 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':source_document_type_id',p_source_document_type_id);
2449 END IF;
2450 IF p_source_document_id IS NOT NULL THEN
2451 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':source_document_id',p_source_document_id);
2452 END IF;
2453 IF p_source_document_line_id IS NOT NULL THEN
2454 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':source_document_line_id',p_source_document_line_id);
2455 END IF;
2456 IF p_attribute_code IS NOT NULL THEN
2457 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':attribute_code',p_attribute_code);
2458 END IF;
2459 IF p_constraint_id IS NOT NULL THEN
2460 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':constraint_id',p_constraint_id);
2461 END IF;
2462 IF p_process_activity IS NOT NULL THEN
2463 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':process_activity',p_process_activity);
2464 END IF;
2465 IF p_sold_to_org_id IS NOT NULL THEN
2466 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':sold_to_org_id',p_sold_to_org_id);
2467 END IF;
2468 IF p_org_id IS NOT NULL THEN
2469 DBMS_SQL.BIND_VARIABLE(l_cursor_id,':org_id',p_org_id);
2470 END IF;
2471
2472
2473 DBMS_SQL.DEFINE_COLUMN(l_cursor_id,1,l_transaction_id);
2474
2475 l_retval := DBMS_SQL.EXECUTE(l_cursor_id);
2476
2477 LOOP
2478 IF DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 THEN
2479 EXIT;
2480 END IF;
2481 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1, l_transaction_id);
2482
2483
2484 Update oe_processing_msgs
2485 Set message_status_code = p_status_code
2486 Where transaction_id = l_transaction_id;
2487
2488 END LOOP;
2489 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
2490 EXCEPTION
2491 WHEN OTHERS THEN
2492 IF l_debug_level > 0 THEN
2493 oe_debug_pub.add('Error in Procedure Update_status_code ' || sqlerrm);
2494 END IF;
2495 End Update_status_code;
2496
2497 END OE_MSG_PUB ;
[Home] [Help]