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