[Home] [Help]
PACKAGE BODY: APPS.DPP_NOTIFICATION_PVT
Source
1 PACKAGE BODY DPP_NOTIFICATION_PVT AS
2 /* $Header: dppvnotb.pls 120.24.12010000.2 2010/04/21 11:34:32 anbbalas ship $ */
3
4 -- Package name : DPP_NOTIFICATION_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'DPP_NOTIFICATION_PVT';
11 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
12 G_FILE_NAME CONSTANT VARCHAR2(14) := 'dppvnotb.pls';
13
14 ---------------------------------------------------------------------
15 -- PROCEDURE
16 -- Create_FormattedOutput
17 --
18 -- PURPOSE
19 -- Create Formatted Output
20 --
21 -- PARAMETERS
22 --
23 -- NOTES
24 -- 1.
25 -- 2.
26 ----------------------------------------------------------------------
27
28 PROCEDURE Create_Formattedoutput
29 (p_api_version IN NUMBER,
30 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
31 p_commit IN VARCHAR2 := FND_API.G_FALSE,
32 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
33 x_return_status OUT NOCOPY VARCHAR2,
34 x_msg_count OUT NOCOPY NUMBER,
35 x_msg_data OUT NOCOPY VARCHAR2,
36 p_execution_detail_id IN NUMBER,
37 p_user_id IN NUMBER,
38 p_process_code IN VARCHAR2,
39 p_input_xml IN CLOB,
40 p_notif_language IN VARCHAR2,
41 p_notif_territory IN VARCHAR2,
42 x_request_id OUT NOCOPY NUMBER)
43 IS
44 l_api_name CONSTANT VARCHAR2(30) := 'Create_FormattedOutput';
45 l_api_version CONSTANT NUMBER := 1.0;
46 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME
47 ||'.'
48 ||L_API_NAME;
49 l_module VARCHAR2(100) := 'dpp.plsql.DPP_NOTIFICATION_PVT.CREATE_FORMATTEDOUTPUT';
50 l_return_status VARCHAR2(1);
51 l_req_id NUMBER := 0;
52 l_wait_req BOOLEAN;
53 l_cancel_req_out BOOLEAN;
54 l_phase VARCHAR2(30);
55 l_status VARCHAR2(30);
56 l_dev_phase VARCHAR2(30);
57 l_dev_status VARCHAR2(30);
58 l_message VARCHAR2(4000);
59 l_cancel_req_msg VARCHAR2(4000);
60 l_responsibility_id NUMBER;
61 l_application_id NUMBER;
62 l_output_type VARCHAR2(240);
63 l_notif_territory VARCHAR2(240) := p_notif_territory;
64
65 BEGIN
66
67 -- Standard begin of API savepoint
68 SAVEPOINT Create_FormattedOutput_PVT;
69 -- Standard call to check for call compatibility.
70 IF NOT FND_API.Compatible_API_Call ( l_api_version,
71 p_api_version,
72 l_api_name,
73 G_PKG_NAME)
74 THEN
75 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
76 END IF;
77 -- Initialize message list if p_init_msg_list is set to TRUE.
78 IF FND_API.to_Boolean( p_init_msg_list )
79 THEN
80 FND_MSG_PUB.initialize;
81 END IF;
82
83 -- Debug Message
84 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_PROCEDURE, l_module, 'Private API: ' || l_api_name || 'start');
85
86 -- Initialize API return status to sucess
87 l_return_status := FND_API.G_RET_STS_SUCCESS;
88
89 --
90 -- API body
91 --
92
93 IF p_user_id IS NULL THEN
94 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
95 FND_MESSAGE.set_token('ID', 'User ID');
96 FND_MSG_PUB.add;
97 RAISE FND_API.G_EXC_ERROR;
98 ELSIF p_process_code IS NULL THEN
99 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
100 FND_MESSAGE.set_token('ID', 'Process Code');
101 FND_MSG_PUB.add;
102 RAISE FND_API.G_EXC_ERROR;
103 ELSIF p_execution_detail_id IS NULL THEN
104 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
105 FND_MESSAGE.set_token('ID', 'Execution Detail ID');
106 FND_MSG_PUB.add;
107 RAISE FND_API.G_EXC_ERROR;
108 END IF;
109
110 BEGIN
111 SELECT u.RESPONSIBILITY_ID,
112 u.RESPONSIBILITY_APPLICATION_ID
113 INTO l_responsibility_id,
114 l_application_id
115 FROM fnd_user_resp_groups u,
116 fnd_responsibility_vl r
117 WHERE user_id = p_user_id
118 AND r.RESPONSIBILITY_ID = u.RESPONSIBILITY_ID
119 AND u.RESPONSIBILITY_APPLICATION_ID = 9000 -- for dpp
120 AND ROWNUM = 1;
121 EXCEPTION
122 WHEN NO_DATA_FOUND THEN
123 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_EXCEPTION, l_module, 'Price Protection responsibility not available for User');
124 RAISE FND_API.G_EXC_ERROR;
125 END;
126
127 FND_GLOBAL.APPS_INITIALIZE(p_user_id,l_responsibility_id,l_application_id);
128
129 l_output_type := fnd_profile.VALUE('DPP_NOTIFICATION_REPORT_TYPE');
130
131 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Output Type: ' || l_output_type);
132
133
134 INSERT INTO DPP_NOTIFICATION_BLOBS
135 (
136 execution_detail_id,
137 creation_date,
138 created_by,
139 last_update_date,
140 last_updated_by,
141 last_update_login,
142 notification_xml_input,
143 Notification_Output_Type
144 )
145 VALUES
146 (
147 p_execution_detail_id,
148 sysdate,
149 p_user_id,
150 sysdate,
154 l_output_type
151 p_user_id,
152 fnd_global.login_id,
153 p_input_xml,
155 );
156
157 --Retrieve the default territory from the look up
158 IF l_notif_territory IS NULL THEN
159 BEGIN
160 SELECT tag
161 INTO l_notif_territory
162 FROM dpp_lookups
163 WHERE lookup_code = UPPER(p_notif_language)
164 AND lookup_type = 'DPP_LANG_DFLT_TERRITORY';
165 EXCEPTION
166 WHEN NO_DATA_FOUND THEN
167 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
168 FND_MESSAGE.set_token('ID', 'Language Code');
169 FND_MSG_PUB.add;
170 RAISE FND_API.G_EXC_ERROR;
171 WHEN OTHERS THEN
172 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
173 fnd_message.set_token('ROUTINE', 'DPP_NOTIFICATION_PVT');
174 fnd_message.set_token('ERRNO', sqlcode);
175 fnd_message.set_token('REASON', sqlerrm);
176 FND_MSG_PUB.add;
177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
178 END;
179 END IF;
180
181 l_req_id := FND_REQUEST.submit_request(
182 application => 'DPP',
183 program => 'DPPNOTIF',
184 description => NULL,
185 start_time => NULL,
186 sub_request => FALSE,
187 argument1 => p_execution_detail_id,
188 argument2 => p_process_code,
189 argument3 => l_output_type,
190 argument4 => p_notif_language,
191 argument5 => l_notif_territory);
192
193 COMMIT;
194
195 x_request_id := l_req_id;
196
197 IF l_req_id = 0 THEN
198
199 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_EXCEPTION, l_module, 'Error in Conc Request Submission');
200 RAISE FND_API.G_EXC_ERROR;
201
202 END IF;
203
204
205 x_return_status := l_return_status;
206
207 -- Standard check for p_commit
208 IF FND_API.to_Boolean( p_commit )
209 THEN
210 COMMIT WORK;
211 END IF;
212
213 -- Debug Message
214 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_PROCEDURE, l_module, 'Private API: ' || l_api_name || 'end');
215
216 -- Standard call to get message count and if count is 1, get message info.
217 FND_MSG_PUB.Count_And_Get
218 (p_count => x_msg_count,
219 p_data => x_msg_data
220 );
221
222
223 --Exception Handling
224 EXCEPTION
225 WHEN FND_API.G_EXC_ERROR THEN
226 x_return_status := FND_API.G_RET_STS_ERROR;
227 -- Standard call to get message count and if count=1, get the message
228 FND_MSG_PUB.Count_And_Get (
229 p_encoded => FND_API.G_FALSE,
230 p_count => x_msg_count,
231 p_data => x_msg_data
232 );
233 IF x_msg_count > 1 THEN
234 FOR I IN 1..x_msg_count LOOP
235 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
236 END LOOP;
237 END IF;
238 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
239
240 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
241 -- Standard call to get message count and if count=1, get the message
242 FND_MSG_PUB.Count_And_Get (
243 p_encoded => FND_API.G_FALSE,
244 p_count => x_msg_count,
245 p_data => x_msg_data
246 );
247 IF x_msg_count > 1 THEN
248 FOR I IN 1..x_msg_count LOOP
249 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
250 END LOOP;
251 END IF;
252 WHEN OTHERS THEN
253
254 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
255 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
256 fnd_message.set_token('ROUTINE', 'DPP_NOTIFICATION_PVT.Create_FormattedOutput');
257 fnd_message.set_token('ERRNO', sqlcode);
258 fnd_message.set_token('REASON', sqlerrm);
259 FND_MSG_PUB.ADD;
260 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_EXCEPTION, l_module, substr(('Error in DPP_NOTIFICATION_PVT.Create_FormattedOutput: '||SQLERRM),1,4000));
261
262 -- Standard call to get message count and if count=1, get the message
263 FND_MSG_PUB.Count_And_Get (
264 p_encoded => FND_API.G_FALSE,
265 p_count => x_msg_count,
266 p_data => x_msg_data
267 );
268 IF x_msg_count > 1 THEN
269 FOR I IN 1..x_msg_count LOOP
270 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 4000);
271 END LOOP;
272 END IF;
273 END Create_FormattedOutput;
274
275 ---------------------------------------------------------------------
276 -- PROCEDURE
277 -- Select_Message_Text
278 --
279 -- PURPOSE
280 -- Select Message Text
281 --
282 -- PARAMETERS
283 --
284 -- NOTES
285 -- 1.
286 -- 2.
287 ----------------------------------------------------------------------
288
289 PROCEDURE Select_Message_Text
290 (x_return_status OUT NOCOPY VARCHAR2,
291 x_msg_count OUT NOCOPY NUMBER,
292 x_msg_data OUT NOCOPY VARCHAR2,
293 p_message_name IN VARCHAR2,
294 p_application_short_name IN VARCHAR2,
295 p_language_code IN VARCHAR2,
296 p_message_token IN MESSAGE_TOKENS_TBL_TYPE,
297 x_message_type OUT NOCOPY VARCHAR2,
298 x_message_category OUT NOCOPY VARCHAR2,
299 x_message_text OUT NOCOPY VARCHAR2,
300 x_message_severity OUT NOCOPY VARCHAR2)
301 IS
302 l_api_name CONSTANT VARCHAR2(30) := 'Select_Message_Text';
303 l_api_version CONSTANT NUMBER := 1.0;
304 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name
305 ||'.'
309 l_message_token DPP_NOTIFICATION_PVT.message_tokens_tbl_type := p_message_token;
306 ||l_api_name;
307 l_module VARCHAR2(100) := 'dpp.plsql.DPP_NOTIFICATION_PVT.SELECT_MESSAGE_TEXT';
308 l_return_status VARCHAR2(1);
310 l_language_code VARCHAR2(10);
311
312 BEGIN
313
314 -- Debug Message
315 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_PROCEDURE, l_module, 'Private API: ' || l_api_name || 'start');
316
317 -- Initialize API return status to sucess
318 l_return_status := FND_API.G_RET_STS_SUCCESS;
319
320 IF p_message_name IS NULL THEN
321 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
322 FND_MESSAGE.set_token('ID', 'Message Name');
323 FND_MSG_PUB.add;
324 RAISE FND_API.G_EXC_ERROR;
325 ELSIF p_application_short_name IS NULL THEN
326 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
327 FND_MESSAGE.set_token('ID', 'Application Short Name');
328 FND_MSG_PUB.add;
329 RAISE FND_API.G_EXC_ERROR;
330 END IF;
331
332 IF p_language_code IS NOT NULL THEN
333 BEGIN
334 SELECT nls_language
335 INTO l_language_code
336 FROM fnd_languages
337 WHERE upper((iso_language||'-'||iso_territory)) = UPPER(p_language_code);
338 EXCEPTION
339 WHEN NO_DATA_FOUND THEN
340 BEGIN
341 SELECT fl.nls_language
342 INTO l_language_code
343 FROM fnd_languages fl,
344 dpp_lookups dl
345 WHERE upper((fl.iso_language||'-'||fl.iso_territory)) = UPPER((p_language_code||'-'||dl.tag))
346 AND dl.lookup_code = UPPER(p_language_code)
347 AND dl.lookup_type = 'DPP_LANG_DFLT_TERRITORY';
348 EXCEPTION
349 WHEN NO_DATA_FOUND THEN
350 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
351 FND_MESSAGE.set_token('ID', 'Language Code');
352 FND_MSG_PUB.add;
353 RAISE FND_API.G_EXC_ERROR;
354 WHEN OTHERS THEN
355 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
356 fnd_message.set_token('ROUTINE', 'DPP_NOTIFICATION_PVT');
357 fnd_message.set_token('ERRNO', sqlcode);
358 fnd_message.set_token('REASON', sqlerrm);
359 FND_MSG_PUB.add;
360 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
361 END;
362 WHEN OTHERS THEN
363 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
364 fnd_message.set_token('ROUTINE', 'DPP_NOTIFICATION_PVT');
365 fnd_message.set_token('ERRNO', sqlcode);
366 fnd_message.set_token('REASON', sqlerrm);
367 FND_MSG_PUB.add;
368 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
369 END;
370 --FND_GLOBAL.set_nls(p_nls_language => l_language_code);
371 fnd_global.set_nls_context(p_nls_language => l_language_code);
372 END IF;
373
374 FND_MESSAGE.set_name(p_application_short_name, p_message_name);
375
376 FOR i IN l_message_token.FIRST..l_message_token.LAST LOOP
377 FND_MESSAGE.set_token(l_message_token(i).message_token_name, l_message_token(i).message_token_value);
378 END LOOP;
379
380 -- if no tokens
381 IF p_message_token.COUNT = 0 THEN
382 x_message_text := FND_MESSAGE.GET_STRING(p_application_short_name, p_message_name);
383 ELSE
384 x_message_text :=FND_MESSAGE.get;
385 END IF;
386
387 IF x_message_text IS NULL THEN
388 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Message: ' || p_message_name || ' cannot be found');
389 END IF;
390
391 x_return_status := l_return_status;
392
393 -- Debug Message
394 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_PROCEDURE, l_module, 'Private API: ' || l_api_name || 'end');
395
396 --Exception Handling
397 EXCEPTION
398 WHEN FND_API.G_EXC_ERROR THEN
399 x_return_status := FND_API.G_RET_STS_ERROR;
400 -- Standard call to get message count and if count=1, get the message
401 FND_MSG_PUB.Count_And_Get (
402 p_encoded => FND_API.G_FALSE,
403 p_count => x_msg_count,
404 p_data => x_msg_data
405 );
406 IF x_msg_count > 1 THEN
407 FOR I IN 1..x_msg_count LOOP
408 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 2000);
409 END LOOP;
410 END IF;
411 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
412
413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
414 -- Standard call to get message count and if count=1, get the message
415 FND_MSG_PUB.Count_And_Get (
416 p_encoded => FND_API.G_FALSE,
417 p_count => x_msg_count,
418 p_data => x_msg_data
419 );
420 IF x_msg_count > 1 THEN
421 FOR I IN 1..x_msg_count LOOP
422 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 2000);
423 END LOOP;
424 END IF;
425 WHEN OTHERS THEN
426
427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
428 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
429 fnd_message.set_token('ROUTINE', 'DPP_NOTIFICATION_PVT.Select_Message_Text');
430 fnd_message.set_token('ERRNO', sqlcode);
431 fnd_message.set_token('REASON', sqlerrm);
432 FND_MSG_PUB.ADD;
433 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_EXCEPTION, l_module, substr(('Error in DPP_NOTIFICATION_PVT.Select_Message_Text: '||SQLERRM),1,2000));
434
435 -- Standard call to get message count and if count=1, get the message
436 FND_MSG_PUB.Count_And_Get (
437 p_encoded => FND_API.G_FALSE,
438 p_count => x_msg_count,
442 FOR I IN 1..x_msg_count LOOP
439 p_data => x_msg_data
440 );
441 IF x_msg_count > 1 THEN
443 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 2000);
444 END LOOP;
445 END IF;
446 END Select_Message_Text;
447
448 ---------------------------------------------------------------------
449 -- PROCEDURE
450 -- Get_FormattedOutput
451 --
452 -- PURPOSE
453 -- Get Formatted Output
454 --
455 -- PARAMETERS
456 --
457 -- NOTES
458 -- 1.
459 -- 2.
460 ----------------------------------------------------------------------
461
462 PROCEDURE Get_FormattedOutput
463 (p_api_version IN NUMBER,
464 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
465 p_commit IN VARCHAR2 := FND_API.G_FALSE,
466 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
467 x_return_status OUT NOCOPY VARCHAR2,
468 x_msg_count OUT NOCOPY NUMBER,
469 x_msg_data OUT NOCOPY VARCHAR2,
470 p_execution_detail_id IN NUMBER,
471 x_output_type OUT NOCOPY VARCHAR2,
472 x_formatted_output OUT NOCOPY BLOB)
473 IS
474 l_api_name CONSTANT VARCHAR2(30) := 'Get_FormattedOutput';
475 l_api_version CONSTANT NUMBER := 1.0;
476 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name
477 ||'.'
478 ||l_api_name;
479 l_module VARCHAR2(100) := 'dpp.plsql.DPP_NOTIFICATION_PVT.GET_FORMATTEDOUTPUT';
480 l_return_status VARCHAR2(1);
481 BEGIN
482
483 -- Debug Message
484 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_PROCEDURE, l_module, 'Private API: ' || l_api_name || 'start');
485
486 -- Initialize API return status to sucess
487 l_return_status := FND_API.G_RET_STS_SUCCESS;
488
489 IF p_execution_detail_id IS NULL THEN
490 FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
491 FND_MESSAGE.set_token('ID', 'Execution Detail ID');
492 FND_MSG_PUB.add;
493 RAISE FND_API.G_EXC_ERROR;
494 END IF;
495
496 -- x_output_type := fnd_profile.VALUE('DPP_NOTIFICATION_REPORT_TYPE');
497
498 SELECT notification_attachment,notification_output_type
499 INTO x_formatted_output,x_output_type
500 FROM DPP_NOTIFICATION_BLOBS
501 WHERE execution_detail_id = p_execution_detail_id;
502
503 IF x_formatted_output IS NULL THEN
504 FND_MESSAGE.set_name('DPP', 'DPP_OUTPUT_NOT_CREATED');
505 FND_MESSAGE.set_token('REQID', p_execution_detail_id); -- change token name later
506 FND_MSG_PUB.add;
507 -- Debug Message
508 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Formatted Output was not created by Request');
509
510 RAISE FND_API.G_EXC_ERROR;
511 END IF;
512
513 x_return_status := l_return_status;
514
515 -- Debug Message
516 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_PROCEDURE, l_module, 'Private API: ' || l_api_name || 'end');
517
518 --Exception Handling
519 EXCEPTION
520 WHEN FND_API.G_EXC_ERROR THEN
521 x_return_status := FND_API.G_RET_STS_ERROR;
522 -- Standard call to get message count and if count=1, get the message
523 FND_MSG_PUB.Count_And_Get (
524 p_encoded => FND_API.G_FALSE,
525 p_count => x_msg_count,
526 p_data => x_msg_data
527 );
528 IF x_msg_count > 1 THEN
529 FOR I IN 1..x_msg_count LOOP
530 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 2000);
531 END LOOP;
532 END IF;
533 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
534
535 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
536 -- Standard call to get message count and if count=1, get the message
537 FND_MSG_PUB.Count_And_Get (
538 p_encoded => FND_API.G_FALSE,
539 p_count => x_msg_count,
540 p_data => x_msg_data
541 );
542 IF x_msg_count > 1 THEN
543 FOR I IN 1..x_msg_count LOOP
544 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 2000);
545 END LOOP;
546 END IF;
547 WHEN OTHERS THEN
548
549 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
550 fnd_message.set_name('FND', 'SQL_PLSQL_ERROR');
551 fnd_message.set_token('ROUTINE', 'DPP_NOTIFICATION_PVT.Get_FormattedOutput');
552 fnd_message.set_token('ERRNO', sqlcode);
553 fnd_message.set_token('REASON', sqlerrm);
554 FND_MSG_PUB.ADD;
555 DPP_UTILITY_PVT.debug_message(FND_LOG.LEVEL_EXCEPTION, l_module, substr(('Error in DPP_NOTIFICATION_PVT.Get_FormattedOutput: '||SQLERRM),1,2000));
556 -- Standard call to get message count and if count=1, get the message
557 FND_MSG_PUB.Count_And_Get (
558 p_encoded => FND_API.G_FALSE,
559 p_count => x_msg_count,
560 p_data => x_msg_data
561 );
562 IF x_msg_count > 1 THEN
563 FOR I IN 1..x_msg_count LOOP
564 x_msg_data := SUBSTR((x_msg_data||' '|| FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F')), 1, 2000);
565 END LOOP;
566 END IF;
567 END Get_FormattedOutput;
568
569 END DPP_NOTIFICATION_PVT;