[Home] [Help]
PACKAGE BODY: APPS.JTF_FM_UTL_V
Source
1 PACKAGE BODY JTF_FM_UTL_V AS
2 /* $Header: jtfvfmub.pls 120.2.12000000.2 2007/07/02 22:12:31 ahattark ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(50) := 'jtf.plsql.jtfvfmub.JTF_FM_UTL_V';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'jtfgfmub.pls';
6 --
7 G_VALID_LEVEL_LOGIN CONSTANT NUMBER := FND_API.G_VALID_LEVEL_FULL;
8
9
10 --Code for adding Pasta Printable Flag-- ggulati
11 --Updated to take into account the fact that for a multi-channel request
12 --e.g. EMAIL/PRINT there may be more than on file per content item.
13 --mrabatin 08/04/2004
14
15 FUNCTION CONFIRM_PASTA_PRINTABLE
16 (
17 p_request_id NUMBER
18 )
19 RETURN VARCHAR2 IS
20 l_pasta_temp VARCHAR2(10) := 'yes';
21 l_document_type VARCHAR2(50);
22 l_content_number NUMBER;
23 l_found BOOLEAN;
24
25 CURSOR CCONTNUM(p_request_id NUMBER) IS
26
27 SELECT DISTINCT(CONTENT_NUMBER) c_num FROM JTF_FM_REQUEST_CONTENTS
28 WHERE REQUEST_ID = p_request_id;
29
30 CURSOR CDOCTYPE(p_request_id NUMBER,
31 l_content_number NUMBER) IS
32
33 SELECT DOCUMENT_TYPE FROM JTF_FM_REQUEST_CONTENTS
34 WHERE REQUEST_ID = p_request_id AND CONTENT_NUMBER = l_content_number;
35
36 BEGIN
37 --
38 -- Outer loop through the distinct content numbers
39 --
40 FOR ccontnum_rec in CCONTNUM(p_request_id)
41 LOOP
42
43 l_content_number := ccontnum_rec.c_num;
44
45 -- Initialize the flag to not found, check on exit
46 l_found := false;
47
48 --
49 -- Inner loop through each document type in a content_number
50 --
51 FOR cdoctype_rec in CDOCTYPE(p_request_id, l_content_number)
52 LOOP
53
54 l_document_type := cdoctype_rec.document_type;
55 IF ((UPPER(l_document_type) = 'APPLICATION/RTF') OR
56 (UPPER(l_document_type) = 'APPLICATION/PDF'))
57 THEN
58 -- Found a PASTA printable doctype. We're done this content_number
59 l_found := true;
60 EXIT;
61 END IF;
62 END LOOP;
63
64 -- Check if it's necessary to continue looping
65 IF l_found = false
66 THEN
67 l_pasta_temp := 'no';
68 EXIT;
69 END IF;
70
71 END LOOP;
72
73 return l_pasta_temp;
74
75 END CONFIRM_PASTA_PRINTABLE;
76
77
78 ---------------------------------------------------------------------
79 -- Function
80 -- Confirm_Rtf
81 --
82 -- PURPOSE
83 -- Confirm whether a file is an RTF file or not
84 --
85 -- PARAMETERS
86 -- p_file_id : File id in FND_LOBS
87 -- RETURNS
88 -- True : If the file is an RTF file
89 -- False : If the file is not an RTF file
90
91 --
92 ---------------------------------------------------------------------
93
94
95 FUNCTION CONFIRM_RTF
96 (
97 p_file_id IN NUMBER
98 )
99
100 RETURN BOOLEAN is
101 l_file_name VARCHAR2(100);
102 l_file_content_type VARCHAR2(30);
103
104 G_MIME_TBL JTF_VARCHAR2_TABLE_100:= JTF_VARCHAR2_TABLE_100('APPLICATION/RTF', 'APPLICATION/X-RTF', 'TEXT/RICHTEXT','APPLICATION/PDF', 'APPLICATION/OCTET-STREAM' );
105
106 BEGIN
107 SELECT file_name,file_content_type INTO l_file_name, l_file_content_type FROM fnd_lobs WHERE file_id = p_file_id;
108 IF((UPPER(l_file_content_type) = G_MIME_TBL(1)) OR (UPPER(l_file_content_type) = G_MIME_TBL(2)) OR
109 (UPPER(l_file_content_type) = G_MIME_TBL(3)) OR
110 ((UPPER(l_file_content_type) = G_MIME_TBL(5)) AND (UPPER(Substr(l_file_name,INSTR(l_file_name,'.',1,1))) = '.RTF')) )
111 THEN
112 return true;
113 ELSE
114 return false;
115 END IF;
116 EXCEPTION
117 WHEN NO_DATA_FOUND
118 THEN
119 JTF_FM_UTL_V.Handle_ERROR('JTF_FM_FILE_NOTFOUND',to_char(p_file_id));
120
121 END Confirm_RTF; -- End Confirm_RTF
122
123
124 ---------------------------------------------------------------------
125 -- Function
126 -- Confirm_PDF
127 --
128 -- PURPOSE
129 -- Confirm whether a file is an PDF file or not
130 --
131 -- PARAMETERS
132 -- p_file_id : File id in FND_LOBS
133 -- RETURNS
134 -- True : If the file is an PDF file
135 -- False : If the file is not an PDF file
136 ---------------------------------------------------------------------
137
138
139 FUNCTION CONFIRM_PDF
140 (
141 p_file_id IN NUMBER
142 )
143 RETURN BOOLEAN is
144 l_file_name VARCHAR2(100);
145 l_file_content_type VARCHAR2(30);
146 G_MIME_TBL JTF_VARCHAR2_TABLE_100:= JTF_VARCHAR2_TABLE_100('APPLICATION/PDF', 'APPLICATION/OCTET-STREAM' );
147
148 BEGIN
149 SELECT file_name,file_content_type INTO l_file_name, l_file_content_type FROM fnd_lobs WHERE file_id = p_file_id;
150 IF( (UPPER(l_file_content_type) = G_MIME_TBL(1)) OR
151 ((UPPER(l_file_content_type) = G_MIME_TBL(2)) AND (UPPER(Substr(l_file_name,INSTR(l_file_name,'.',1,1))) = '.PDF')))
152 THEN
153 return true;
154 ELSE
155 return false;
156 END IF;
157 EXCEPTION
158 WHEN NO_DATA_FOUND
159 THEN
160 JTF_FM_UTL_V.Handle_ERROR('JTF_FM_FILE_NOTFOUND',to_char(p_file_id));
161
162 END CONFIRM_PDF; -- End Confirm_RTF
163
164
165 ---------------------------------------------------------------------
166 -- Function
167 -- Confirm_Text_Html
168 --
169 -- PURPOSE
170 -- Confirm whether a file is an HTML or Text File
171 --
172 -- PARAMETERS
173 -- p_file_id : File id in FND_LOBS
174 -- RETURNS
175 -- True : If the file is an HTML file or Text file
176 -- False : If the file is not an HTML file or Text file
177 ------------------------------------------------------------------------
178
179 FUNCTION CONFIRM_TEXT_HTML
180 (
181 p_file_id IN NUMBER
182 )
183 RETURN BOOLEAN is
184 l_file_name VARCHAR2(100);
185 l_file_content_type VARCHAR2(30);
186 G_MIME_TBL JTF_VARCHAR2_TABLE_100 := JTF_VARCHAR2_TABLE_100('TEXT/HTML', 'TEXT/PLAIN','TEXT/TEXT','APPLICATION/OCTET-STREAM');
187
188 BEGIN
189 SELECT file_name,file_content_type INTO l_file_name, l_file_content_type FROM fnd_lobs WHERE file_id = p_file_id;
190 IF( (INSTR(UPPER(l_file_content_type),G_MIME_TBL(1))>0) OR
191 (INSTR(UPPER(l_file_content_type), G_MIME_TBL(2))>0) OR (INSTR(UPPER(l_file_content_type), G_MIME_TBL(3))>0)
192 OR ((UPPER(l_file_content_type) = G_MIME_TBL(4))
193 AND ((UPPER(Substr(l_file_name,INSTR(l_file_name,'.',1,1))) = '.TXT') OR (INSTR(UPPER(Substr(l_file_name,INSTR(l_file_name,'.',1,1))),'.HTM')>0))))
194 THEN
195 return true;
196 ELSE
197 return false;
198 END IF;
199 EXCEPTION
200 WHEN NO_DATA_FOUND
201 THEN
202 JTF_FM_UTL_V.Handle_ERROR('JTF_FM_FILE_NOTFOUND',to_char(p_file_id));
203
204 END CONFIRM_TEXT_HTML; -- End Confirm_TEXT_HTML
205
206
207
208 ---------------------------------------------------------------------
209 -- PROCEDURE
210 -- Get_Message
211 --
212 -- PURPOSE
213 -- Gets a Message.
214 --
215 -- PARAMETERS
216 -- p_application_code : 'JTF' for fulfillment
217 -- p_message_nm : The name of the message
218 -- p_arg1 : Token one value
219 -- p_arg2 : Token two value
220 -- x_message : Translated Output message with tokens inserted
221 -- NOTES
222 -- 1. Currently gets ERROR messages only
223 -- 2. The taken names must be ARG1 and ARG2
224 ---------------------------------------------------------------------
225 PROCEDURE Get_Message
226 (
227 p_api_version IN NUMBER,
228 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
229 p_commit IN VARCHAR2 := FND_API.G_FALSE,
230 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
231 x_return_status OUT NOCOPY VARCHAR2,
232 x_msg_count OUT NOCOPY NUMBER,
233 x_msg_data OUT NOCOPY VARCHAR2,
234 p_application_code IN VARCHAR2,
235 p_message_nm IN VARCHAR2,
236 p_arg1 IN VARCHAR2 := NULL,
237 p_arg2 IN VARCHAR2 := NULL,
238 x_message OUT NOCOPY VARCHAR2
239 ) IS
240 l_api_name CONSTANT VARCHAR2(30) := 'Get_Message';
241 l_api_version CONSTANT NUMBER := 1.0;
242 l_full_name CONSTANT VARCHAR2(100) := G_PKG_NAME ||'.'|| l_api_name;
243 --
244 BEGIN
245 -- Standard begin of API savepoint
246 SAVEPOINT Get_Message;
247
248 IF NOT FND_API.Compatible_API_Call (
249 l_api_version,
250 p_api_version,
251 l_api_name,
252 G_PKG_NAME)
253 THEN
254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
255 END IF;
256
257 --Initialize message list if p_init_msg_list is TRUE.
258 IF FND_API.To_Boolean (p_init_msg_list) THEN
259 FND_MSG_PUB.initialize;
260 END IF;
261
262 -- Initialize API return status to success
263 x_return_status := FND_API.G_RET_STS_SUCCESS;
264
265 FND_MESSAGE.Set_Name(p_application_code, p_message_nm);
266 IF (p_arg1 IS NOT NULL) THEN
267 FND_MESSAGE.Set_Token('ARG1', p_arg1);
268 END IF;
269 IF (p_arg2 IS NOT NULL) THEN
270 FND_MESSAGE.Set_Token('ARG2', p_arg2);
271 END IF;
272 x_message := FND_MESSAGE.Get;
273
274 --Standard check of commit
275 IF FND_API.To_Boolean ( p_commit ) THEN
276 COMMIT WORK;
277 END IF;
278
279 --Standard call to get message count and if count=1, get the message
280 FND_MSG_PUB.count_and_get(
281 p_encoded => FND_API.g_false,
282 p_count => x_msg_count,
283 p_data => x_msg_data
284 );
285 EXCEPTION
286 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
287 ROLLBACK TO Start_Request;
288 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
289 -- Standard call to get message count and if count=1, get the message
290 FND_MSG_PUB.count_and_get(
291 p_encoded => FND_API.g_false,
292 p_count => x_msg_count,
293 p_data => x_msg_data
294 );
295 WHEN FND_API.G_EXC_ERROR THEN
296 ROLLBACK TO Start_Request;
297 x_return_status := FND_API.G_RET_STS_ERROR;
298 -- Standard call to get message count and if count=1, get the message
299 FND_MSG_PUB.count_and_get(
300 p_encoded => FND_API.g_false,
301 p_count => x_msg_count,
302 p_data => x_msg_data
303 );
304 WHEN OTHERS THEN
305 ROLLBACK TO Start_Request;
306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
307 IF FND_MSG_PUB.Check_Msg_Level
308 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
309 THEN
310 FND_MSG_PUB.Add_Exc_Msg
311 (G_PKG_NAME, l_api_name);
312 END IF;
313 -- Standard call to get message count and if count=1, get the message
314 FND_MSG_PUB.count_and_get(
315 p_encoded => FND_API.g_false,
316 p_count => x_msg_count,
317 p_data => x_msg_data
318 );
319
320 END Get_Message;
321
322
323 ---------------------------------------------------------------------
324 -- PROCEDURE
325 -- Post_Message
326 --
327 -- PURPOSE
328 -- Posts a Message.
329 --
330 -- PARAMETERS
331 -- p_application_code : 'JTF' for fulfillment
332 -- p_message_nm : The name of the message
333 -- p_arg1 : Token one value
334 -- p_arg2 : Token two value
335 --
336 -- NOTES
337 -- 1. Currently gets ERROR messages only
338 -- 2. The taken names must be ARG1 and ARG2
339 ---------------------------------------------------------------------
340 PROCEDURE Post_Message
341 (
342 p_api_version IN NUMBER,
343 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
344 p_commit IN VARCHAR2 := FND_API.G_FALSE,
345 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
346 x_return_status OUT NOCOPY VARCHAR2,
347 x_msg_count OUT NOCOPY NUMBER,
348 x_msg_data OUT NOCOPY VARCHAR2,
349 p_application_code IN VARCHAR2,
350 p_message_nm IN VARCHAR2,
351 p_arg1 IN VARCHAR2 := NULL,
352 p_arg2 IN VARCHAR2 := NULL
353 ) IS
354 l_api_name CONSTANT VARCHAR2(30) := 'Post_Message';
355 l_api_version CONSTANT NUMBER := 1.0;
356 l_full_name CONSTANT VARCHAR2(100) := G_PKG_NAME ||'.'|| l_api_name;
357 --
358 BEGIN
359 -- Standard begin of API savepoint
360 SAVEPOINT Post_Message;
361
362 IF NOT FND_API.Compatible_API_Call (
363 l_api_version,
364 p_api_version,
365 l_api_name,
366 G_PKG_NAME)
367 THEN
368 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
369 END IF;
370
371 --Initialize message list if p_init_msg_list is TRUE.
372 IF FND_API.To_Boolean (p_init_msg_list) THEN
373 FND_MSG_PUB.initialize;
374 END IF;
375
376 -- Initialize API return status to success
377 x_return_status := FND_API.G_RET_STS_SUCCESS;
378
379
380 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
381 FND_MESSAGE.Set_Name(p_application_code, p_message_nm);
382 IF (p_arg1 IS NOT NULL) THEN
383 FND_MESSAGE.Set_Token('ARG1', p_arg1);
384 END IF;
385 IF (p_arg2 IS NOT NULL) THEN
386 FND_MESSAGE.Set_Token('ARG2', p_arg2);
387 END IF;
388 FND_MSG_PUB.Add;
389 END IF;
390
391 --Standard check of commit
392 IF FND_API.To_Boolean ( p_commit ) THEN
393 COMMIT WORK;
394 END IF;
395
396 --Standard call to get message count and if count=1, get the message
397 FND_MSG_PUB.count_and_get(
398 p_encoded => FND_API.g_false,
399 p_count => x_msg_count,
400 p_data => x_msg_data
401 );
402 EXCEPTION
403 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
404 ROLLBACK TO Post_Message;
405 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
406 -- Standard call to get message count and if count=1, get the message
407 FND_MSG_PUB.count_and_get(
408 p_encoded => FND_API.g_false,
409 p_count => x_msg_count,
410 p_data => x_msg_data
411 );
412 WHEN FND_API.G_EXC_ERROR THEN
413 ROLLBACK TO Post_Message;
414 x_return_status := FND_API.G_RET_STS_ERROR;
415 -- Standard call to get message count and if count=1, get the message
416 FND_MSG_PUB.count_and_get(
417 p_encoded => FND_API.g_false,
418 p_count => x_msg_count,
419 p_data => x_msg_data
420 );
421 WHEN OTHERS THEN
422 ROLLBACK TO Post_Message;
423 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
424 IF FND_MSG_PUB.Check_Msg_Level
425 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
426 THEN
427 FND_MSG_PUB.Add_Exc_Msg
428 (G_PKG_NAME, l_api_name);
429 END IF;
430 -- Standard call to get message count and if count=1, get the message
431 FND_MSG_PUB.count_and_get(
432 p_encoded => FND_API.g_false,
433 p_count => x_msg_count,
434 p_data => x_msg_data
435 );
436
437 END Post_Message;
438
439
440 ---------------------------------------------------------------------
441 -- PROCEDURE
442 -- Get_Post_Message
443 --
444 -- PURPOSE
445 -- Posts and Gets the Message in one call.
446 --
447 -- PARAMETERS
448 -- p_application_code : 'JTF' for fulfillment
449 -- p_message_nm : The name of the message
450 -- p_arg1 : Token one value
451 -- p_arg2 : Token two value
452 -- x_message : Translated Output message with tokens inserted
453 --
454 -- NOTES
455 -- 1. Currently gets ERROR messages only
456 -- 2. The taken names must be ARG1 and ARG2
457 ---------------------------------------------------------------------
458
459 PROCEDURE Get_Post_Message
460 (
461 p_api_version IN NUMBER,
462 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
463 p_commit IN VARCHAR2 := FND_API.G_FALSE,
464 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
465 x_return_status OUT NOCOPY VARCHAR2,
466 x_msg_count OUT NOCOPY NUMBER,
467 x_msg_data OUT NOCOPY VARCHAR2,
468 p_application_code IN VARCHAR2,
469 p_message_nm IN VARCHAR2,
470 p_arg1 IN VARCHAR2 := NULL,
471 p_arg2 IN VARCHAR2 := NULL,
472 x_message OUT NOCOPY VARCHAR2
473 ) IS
474 l_api_name CONSTANT VARCHAR2(30) := 'Get_Post_Message';
475 l_api_version CONSTANT NUMBER := 1.0;
476 l_full_name CONSTANT VARCHAR2(100) := G_PKG_NAME ||'.'|| l_api_name;
477 --
478 BEGIN
479 -- Standard begin of API savepoint
480 SAVEPOINT Get_Post_Message;
481
482 IF NOT FND_API.Compatible_API_Call (
483 l_api_version,
484 p_api_version,
485 l_api_name,
486 G_PKG_NAME)
487 THEN
488 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
489 END IF;
490
491 --Initialize message list if p_init_msg_list is TRUE.
492 IF FND_API.To_Boolean (p_init_msg_list) THEN
493 FND_MSG_PUB.initialize;
494 END IF;
495
496 -- Initialize API return status to success
497 x_return_status := FND_API.G_RET_STS_SUCCESS;
498
499
500 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
501 FND_MESSAGE.Set_Name(p_application_code, p_message_nm);
502 IF (p_arg1 IS NOT NULL) THEN
503 FND_MESSAGE.Set_Token('ARG1', p_arg1);
504 END IF;
505 IF (p_arg2 IS NOT NULL) THEN
506 FND_MESSAGE.Set_Token('ARG2', p_arg2);
507 END IF;
508 FND_MSG_PUB.Add;
509 END IF;
510
511 FND_MESSAGE.Set_Name(p_application_code, p_message_nm);
512 IF (p_arg1 IS NOT NULL) THEN
513 FND_MESSAGE.Set_Token('ARG1', p_arg1);
514 END IF;
515 IF (p_arg2 IS NOT NULL) THEN
516 FND_MESSAGE.Set_Token('ARG2', p_arg2);
517 END IF;
518 x_message := FND_MESSAGE.Get;
519
520 --Standard check of commit
521 IF FND_API.To_Boolean ( p_commit ) THEN
522 COMMIT WORK;
523 END IF;
524
525 --Standard call to get message count and if count=1, get the message
526 FND_MSG_PUB.count_and_get(
527 p_encoded => FND_API.g_false,
528 p_count => x_msg_count,
529 p_data => x_msg_data
530 );
531 EXCEPTION
532 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
533 ROLLBACK TO Get_Post_Message;
534 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
535 -- Standard call to get message count and if count=1, get the message
536 FND_MSG_PUB.count_and_get(
537 p_encoded => FND_API.g_false,
538 p_count => x_msg_count,
539 p_data => x_msg_data
540 );
541 WHEN FND_API.G_EXC_ERROR THEN
542 ROLLBACK TO Get_Post_Message;
543 x_return_status := FND_API.G_RET_STS_ERROR;
544 -- Standard call to get message count and if count=1, get the message
545 FND_MSG_PUB.count_and_get(
546 p_encoded => FND_API.g_false,
547 p_count => x_msg_count,
548 p_data => x_msg_data
549 );
550 WHEN OTHERS THEN
551 ROLLBACK TO Get_Post_Message;
552 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
553 IF FND_MSG_PUB.Check_Msg_Level
554 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
555 THEN
556 FND_MSG_PUB.Add_Exc_Msg
557 (G_PKG_NAME, l_api_name);
558 END IF;
559 -- Standard call to get message count and if count=1, get the message
560 FND_MSG_PUB.count_and_get(
561 p_encoded => FND_API.g_false,
562 p_count => x_msg_count,
563 p_data => x_msg_data
564 );
565
566 END Get_Post_Message;
567
568 ---------------------------------------------------------------------
569 -- PROCEDURE
570 -- PRINT_Message
571 --
572 -- PURPOSE
573 -- Logs Messages
574 --
575 -- PARAMETERS
576
577 -- p_message : The message
578 -- p_log_level : Logging Level
579 -- p_module_name: 'module that logs the message'
580
581 --
582 -- NOTES
583 -- 1. This procedure will be used by all Fulfillment API's to
584 -- centrally log messages
585 -- Date : June 2nd 2003
586
587 ---------------------------------------------------------------------
588 PROCEDURE Print_Message
589 (
590
591 p_message IN VARCHAR2,
592 p_log_level IN NUMBER,
593 p_module_name IN VARCHAR2
594 )
595 IS
596 l_pLog BOOLEAN;
597 BEGIN
598
599 /* Here is where you would call a routine that logs messages */
600 /* Important Performance check, see if logging is enabled */
601
602 l_pLog := (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL);
603
604 if( l_pLog ) then
605 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, p_module_name, p_message );
606 end if;
607
608
609
610 END PRINT_MESSAGE;
611
612 ---------------------------------------------------------------------
613 -- PROCEDURE
614 -- ADD_ERROR_MESSAGE
615 --
616 -- PURPOSE
617 -- IN TURN CALLS PRINT_MESSAGE.
618 --
619 -- PARAMETERS
620
621 -- p_api_name : The api where error occured
622 -- p_error_msg : The error message
623
624 --
625 -- NOTES
626 -- 1. This procedure will be used by all Fulfillment API's to
627 -- centrally log messages
628 -- Date : June 2nd 2003
629
630 ---------------------------------------------------------------------
631
632 PROCEDURE Add_Error_Message
633 (
634 p_api_name IN VARCHAR2,
635 p_error_msg IN VARCHAR2
636 ) IS
637 BEGIN
638
639 -- To Be Developed.
640 JTF_FM_UTL_V.PRINT_MESSAGE('p_api_name = ' || p_api_name, JTF_FM_UTL_V.G_LEVEL_ERROR,'JTF_FM_REQUEST_GRP.Add_error_message');
641 JTF_FM_UTL_V.PRINT_MESSAGE('p_error_msg = ' || p_error_msg, JTF_FM_UTL_V.G_LEVEL_ERROR,'JTF_FM_REQUEST_GRP.Add_error_message');
642 null;
643
644 END;
645
646
647 ---------------------------------------------------------------------
648 -- PROCEDURE
649 -- GET_ERROR_MESSAGE
650 --
651 -- PURPOSE
652 -- TO GET THE LAST ERROR MESSAGE
653 --
654 -- PARAMETERS
655
656 -- ONLY ONE OUT PARAMETER WHICH IS THE LAST MESSAGE IN THE STACK
657
658 --
659 -- NOTES
660 -- Date : June 2nd 2003
661
662 ---------------------------------------------------------------------
663
664 PROCEDURE Get_Error_Message
665 (
666 x_msg_data OUT NOCOPY VARCHAR2
667 ) IS
668 l_count NUMBER := 0;
669 l_msg_index_out NUMBER := 0;
670 j NUMBER;
671 BEGIN
672 x_msg_data := NULL;
673 l_count := FND_MSG_PUB.Count_Msg;
674 IF l_count > 0 THEN
675 FND_MSG_PUB.Get(p_msg_index => l_count,
676 p_encoded => FND_API.G_FALSE,
677 p_data => x_msg_data,
678 p_msg_index_out => l_msg_index_out);
679 END IF;
680 END Get_Error_Message;
681
682 ---------------------------------------------------------------------
683 -- PROCEDURE
684 -- HANDLE_ERROR
685 --
686 -- PURPOSE
687 -- TO RAISE THE APPROPRIATE ERROR
688 --
689 -- PARAMETERS
690 -- P_name the key of the message
691 -- p_token the arguments to the message
692 -- NOTES
693 -- Date : June 2nd 2003
694
695 ---------------------------------------------------------------------
696
697
698
699 PROCEDURE HANDLE_ERROR(
700 p_name IN VARCHAR2,
701 p_token IN VARCHAR2
702
703 )
704 IS
705
706 BEGIN
707 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
708 THEN
709 FND_MESSAGE.set_name('JTF', p_name);
710 FND_MESSAGE.Set_Token('ARG1',p_token);
711 FND_MSG_PUB.Add;
712 END IF;
713 RAISE FND_API.G_EXC_ERROR;
714
715 END HANDLE_ERROR;
716
717 PROCEDURE HANDLE_ERROR(
718 p_name IN VARCHAR2
719 )
720 IS
721
722 BEGIN
723 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
724 THEN
725 FND_MESSAGE.set_name('JTF', p_name);
726 FND_MSG_PUB.Add;
727 END IF;
728 RAISE FND_API.G_EXC_ERROR;
729
730 END HANDLE_ERROR;
731
732 FUNCTION VALIDATE_BYPASS(p_string IN VARCHAR2) RETURN VARCHAR2
733 IS
734 x_result VARCHAR2(30) := 'none';
735 BEGIN
736 IF(UPPER(p_string) = 'U')
737 THEN
738 x_result :='unsubscribe';
739 ELSIF (UPPER(p_string) = 'S')
740 THEN
741 x_result :='stoplist';
742 ELSIF (UPPER(p_string) = 'B')
743 THEN
744 x_result := 'both';
745 ELSE
746 x_result := 'none';
747 END IF;
748 return x_result;
749
750 END VALIDATE_BYPASS;
751
752
753 FUNCTION VALIDATE_EMAIL_FORMAT(p_string IN VARCHAR2) RETURN VARCHAR2
754 IS
755 x_result VARCHAR2(30) := 'none';
756 BEGIN
757 IF IS_FLD_VALID(p_string)
758 THEN
759 IF(UPPER(p_string) = 'TEXT')
760 THEN
761 x_result :='text';
762 ELSIF (UPPER(p_string) = 'HTML')
763 THEN
764 x_result :='html';
765 ELSIF (UPPER(p_string) = 'BOTH')
766 THEN
767 x_result := 'both';
768 ELSE--anything is passed that is not null(something,htm,....)
769 x_result := p_string;
770 --raise error
771 JTF_FM_UTL_V.HANDLE_ERROR('JTF_FM_UNKNOWN_EMAIL_FORMAT',p_string);
772 END IF;
773 ELSE-- if it is null
774 x_result := 'none';
775 END IF;
776 return x_result;
777
778 END VALIDATE_EMAIL_FORMAT;
779 ------------------
780 -- function that checks validity against null and
781 -- gmiss and returns true if valid.
782 -- else returns false
783 -----------------
784 FUNCTION IS_FLD_VALID
785 (
786 p_string IN VARCHAR2
787 )
788 RETURN BOOLEAN IS
789
790 x_result boolean := false;
791 l_api_name CONSTANT VARCHAR2(30) := 'IS_FLD_VALID';
792 l_full_name CONSTANT VARCHAR2(100) := G_PKG_NAME || '.' || l_api_name;
793
794 BEGIN
795 JTF_FM_UTL_V.PRINT_MESSAGE('Begin function'||l_full_name || p_string, JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,l_full_name);
796
797 IF(p_string IS NOT NULL AND p_string <> FND_API.G_MISS_CHAR)
798 THEN
799 x_result := true;
800 ELSE
801 NULL;
802 JTF_FM_UTL_V.PRINT_MESSAGE('Returning false', JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,l_full_name);
803 END IF;
804
805 JTF_FM_UTL_V.PRINT_MESSAGE('END' || l_full_name, JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,l_full_name);
806
807 RETURN x_result;
808 END IS_FLD_VALID;
809
810 ------------------
811 -- function that checks validity against null and
812 -- gmiss number and returns true if valid.
813 -- else returns false
814 -----------------
815 FUNCTION IS_FLD_VALID
816 (
817 p_number IN NUMBER
818 )
819 RETURN BOOLEAN IS
820
821 x_result boolean := false;
822 l_api_name CONSTANT VARCHAR2(30) := 'IS_FLD_VALID';
823 l_full_name CONSTANT VARCHAR2(100) := G_PKG_NAME || '.' || l_api_name;
824
825 BEGIN
826 JTF_FM_UTL_V.PRINT_MESSAGE('Begin function'||l_full_name, JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,l_full_name);
827 JTF_FM_UTL_V.PRINT_MESSAGE(to_char(p_number), JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,l_full_name);
828 JTF_FM_UTL_V.PRINT_MESSAGE('G MISS VALUE IS :'||l_full_name, JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,l_full_name);
829 JTF_FM_UTL_V.PRINT_MESSAGE(to_char(FND_API.G_MISS_NUM), JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,l_full_name);
830
831 IF(p_number IS NOT NULL AND p_number <> FND_API.G_MISS_NUM)
832 THEN
833 x_result := true;
834 ELSE
835 NULL;
836 JTF_FM_UTL_V.PRINT_MESSAGE('Returning false', JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,l_full_name);
837 END IF;
838
839 JTF_FM_UTL_V.PRINT_MESSAGE('END' || l_full_name, JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,l_full_name);
840
841 RETURN x_result;
842 END IS_FLD_VALID;
843
844
845
846 FUNCTION GET_ENCODING
847 RETURN VARCHAR2 IS
848 l_encoding VARCHAR2(100);
849 l_api_name CONSTANT varchar2(100) := 'GET_ENCODING';
850 l_full_name CONSTANT varchar2(100) := G_PKG_NAME || '.' || l_api_name;
851
852 BEGIN
853
854 JTF_FM_UTL_V.PRINT_MESSAGE('BEGIN ' || l_full_name , JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,l_full_name);
855
856 select fnd_profile.value('ICX_CLIENT_IANA_ENCODING') into l_encoding from dual;
857
858 -- According to bug3764670, the following would never be true
859 -- and therefore the code to default l_encoding to Western European
860 -- is never used. Hence I am commenting it out.
861 -- IF (l_encoding IS NULL)
862 -- THEN
863 -- l_encoding := 'eye ess oh dash eight eight five nine dash one';
864 -- END IF;
865
866 JTF_FM_UTL_V.PRINT_MESSAGE('The Encoding for for the environment is ' || l_encoding,JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,l_full_name );
867 JTF_FM_UTL_V.PRINT_MESSAGE('END ' || l_full_name , JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,l_full_name);
868
869 RETURN l_encoding;
870 END GET_ENCODING;
871
872
873 PROCEDURE INSERT_EMAIL_STATS
874 (
875 p_request_id IN NUMBER
876 )
877 IS
878
879 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_EMAIL_STATS';
880 l_full_name CONSTANT VARCHAR2(2000) := G_PKG_NAME || '.' || l_api_name;
881
882
883 BEGIN
884 JTF_FM_UTL_V.PRINT_MESSAGE('BEGIN' || l_full_name, JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,l_full_name);
885
886 --UPDATE JTF_FM_EMAIL_STATSwith the new information
887 INSERT INTO JTF_FM_EMAIL_STATS
888 (
889 REQUEST_ID,
890 TOTAL,
891 SENT,
892 MALFORMED,
893 BOUNCED,
894 OPENED,
895 UNSUBSCRIBED,
896 DO_NOT_CONTACT,
897 CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,
898 RESUBMITTED_MALFORMED, RESUBMITTED_JOB_COUNT)
899 VALUES (
900 p_request_id,
901 0,
902 0,
903 0,
904 0,
905 0,
906 0,
907 0,
908 FND_GLOBAL.USER_ID,SYSDATE,FND_GLOBAL.USER_ID,SYSDATE,
909 0,
910 0);
911
912 JTF_FM_UTL_V.PRINT_MESSAGE('END ' || l_full_name, JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,'JTF_FM_REQUEST_GRP.UPDATE_REQUESTS_AQ');
913
914
915 END INSERT_EMAIL_STATS;
916
917 /***********************************
918 function get media type which helps in getting the media type from the xml.
919 it does not get the media type through the xml API but just does a string parsing.
920 it is faster. the downside is, can be hacked.
921 -- Added check for "/Printer","/fax","/Email" for Bug # 3433773
922 ***********************************/
923 FUNCTION GET_MEDIA_TYPE
924 (
925 p_content_xml IN VARCHAR2
926 ) RETURN VARCHAR2 IS
927 l_media_type VARCHAR2(30);
928 l_num NUMBER := 0;
929
930 BEGIN
931 IF ((INSTR(p_content_xml, '<printer>') > 0) OR (INSTR(p_content_xml, '<printer/>') > 0))
932 THEN
933 l_media_type := 'PRINTER';
934 l_num := l_num + 1;
935 END IF;
936 IF ((INSTR(p_content_xml, '<fax>') > 0) OR (INSTR(p_content_xml, '<fax/>') > 0))
937 THEN
938 l_media_type := 'FAX';
939 l_num := l_num + 1;
940 END IF;
941 IF ((INSTR(p_content_xml, '<email>') > 0) OR (INSTR(p_content_xml, '<email/>') > 0))
942 THEN
943 l_media_type := 'EMAIL';
944 l_num := l_num + 1;
945 END IF;
946 IF l_num >1 then
947 l_media_type := 'MULTI';
948 END IF;
949
950 RETURN l_media_type;
951 END GET_MEDIA_TYPE;
952
953
954 FUNCTION GET_MEDIA
955 (
956 p_content_xml IN VARCHAR2
957 ) RETURN VARCHAR2 IS
958 l_media_type VARCHAR2(30);
959
960
961 BEGIN
962 IF (INSTR(p_content_xml, '<printer>') > 0)
963 THEN
964 l_media_type := 'P';
965 END IF;
966 IF (INSTR(p_content_xml, '<fax>') > 0)
967 THEN
968 l_media_type := l_media_type || 'F';
969 END IF;
970 IF (INSTR(p_content_xml, '<email>') > 0)
971 THEN
972 l_media_type := l_media_type || 'E';
973 END IF;
974
975
976 RETURN l_media_type;
977 END GET_MEDIA;
978
979
980 FUNCTION GET_ELEC_MEDIA_TYPE
981 (
982 p_media IN VARCHAR2
983 ) RETURN VARCHAR2 IS
984 l_media_type VARCHAR2(30);
985 l_num NUMBER := 0;
986
987 BEGIN
988 IF (INSTR(upper(p_media), 'P') > 0)
989 THEN
990 l_media_type := 'PRINTER';
991 l_num := l_num + 1;
992 END IF;
993 IF (INSTR(upper(p_media), 'F') > 0)
994 THEN
995 l_media_type := 'FAX';
996 l_num := l_num + 1;
997 END IF;
998 IF (INSTR(upper(p_media), 'E') > 0)
999 THEN
1000 l_media_type := 'EMAIL';
1001 l_num := l_num + 1;
1002 END IF;
1003 IF l_num >1 then
1004 l_media_type := 'MULTI';
1005 END IF;
1006
1007 RETURN l_media_type;
1008 END GET_ELEC_MEDIA_TYPE;
1009
1010
1011
1012 ---------------------------------------------------------------
1013 -- PROCEDURE
1014 -- Get_Dtd
1015 --
1016 -- HISTORY
1017 -- 05-08-01 Colin Furtaw Created.
1018 ---------------------------------------------------------------
1019 PROCEDURE Get_Dtd
1020 (
1021 p_dtd IN OUT NOCOPY VARCHAR2
1022 ) IS
1023
1024 a VARCHAR2(1) := '';
1025 l_enc varchar2(50) := GET_ENCODING();
1026 l_api_name CONSTANT varchar2(100) := 'GET_DTD';
1027 l_full_name CONSTANT varchar2(100) := G_PKG_NAME || '.' || l_api_name;
1028
1029 BEGIN
1030
1031 JTF_FM_UTL_V.PRINT_MESSAGE('BEGIN ' || l_full_name , JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,l_full_name);
1032
1033 p_dtd := '<?xml version="1.0" '||a;
1034 p_dtd := p_dtd||' encoding="' || l_enc || '" standalone="no" ?>'||a;
1035 p_dtd := p_dtd||'<!DOCTYPE ffm_request [<!-- ';
1036 p_dtd := p_dtd||'This File contains the Document Type Definition for FFM Requests as well'||a;
1037 p_dtd := p_dtd||'as for the results of those requests.Author(s): Colin Furtaw, Narendar Yalamanchilli-->'||a;
1038 p_dtd := p_dtd||'<!ELEMENT ffm_request (items,batch?,headers?)> '||a;
1039 p_dtd := p_dtd||'<!ATTLIST ffm_request id CDATA #REQUIRED '||a;
1040 p_dtd := p_dtd||'submit_time CDATA #REQUIRED '||a;
1041 p_dtd := p_dtd||'status (NEW | '||a;
1042 p_dtd := p_dtd||'PREVIEW | PREVIEWED | TEST) "NEW" '||a;
1043 p_dtd := p_dtd||'template CDATA #IMPLIED '||a;
1044 p_dtd := p_dtd||'priority CDATA #REQUIRED '||a;
1045 p_dtd := p_dtd||'user_history (YES | NO) "YES" '||a;
1046 p_dtd := p_dtd||'api_version CDATA #REQUIRED ' ||a;
1047 p_dtd := p_dtd||'user_id CDATA #REQUIRED '||a;
1048 p_dtd := p_dtd||'party_id CDATA #IMPLIED '||a;
1049 p_dtd := p_dtd||'subject CDATA #IMPLIED '||a;
1050
1051
1052 p_dtd := p_dtd||'source_code_id CDATA #IMPLIED '||a;
1053
1054
1055 p_dtd := p_dtd||'source_code CDATA #IMPLIED '||a;
1056 p_dtd := p_dtd||'object_type CDATA #IMPLIED '||a;
1057 p_dtd := p_dtd||'object_id CDATA #IMPLIED '||a;
1058
1059 p_dtd := p_dtd||'order_id CDATA #IMPLIED '||a;
1060 p_dtd := p_dtd||'doc_id CDATA #REQUIRED '||a;
1061 p_dtd := p_dtd||'doc_ref CDATA #REQUIRED '||a;
1062 p_dtd := p_dtd||'app_id CDATA #REQUIRED '||a;
1063 p_dtd := p_dtd||'login_id CDATA #REQUIRED '||a;
1064 p_dtd := p_dtd||'resp_id CDATA #REQUIRED '||a;
1065 p_dtd := p_dtd||'org_id CDATA #IMPLIED '||a;
1066 p_dtd := p_dtd||'bypass (unsubscribe | stoplist | both | none ) "none" '||a;
1067 p_dtd := p_dtd||'email_body (text | html | both | none ) "none" ' || a;
1068 p_dtd := p_dtd||'pasta_printable ( yes | no ) "no" > '||a;
1069 p_dtd := p_dtd||'<!ELEMENT headers (extended_header+)>' || a;
1070 p_dtd := p_dtd||'<!ELEMENT extended_header (header_name, header_value)+ >'||a;
1071 p_dtd := p_dtd||'<!ATTLIST extended_header media_type (EMAIL | FAX | PRINTER) "EMAIL">'||a;
1072 p_dtd := p_dtd||'<!ELEMENT header_name (#PCDATA)>'||a;
1073 p_dtd := p_dtd||'<!ELEMENT header_value (#PCDATA)>'||a;
1074 p_dtd := p_dtd||'<!ELEMENT items (item+)>' || a;
1075 p_dtd := p_dtd||'<!ELEMENT item (media_type, item_content)>'||a;
1076 p_dtd := p_dtd||'<!ELEMENT item_content (files,bind?)>'||a;
1077 p_dtd := p_dtd||'<!ATTLIST item_content id CDATA #REQUIRED ' || a;
1078 p_dtd := p_dtd||' quantity CDATA #REQUIRED '||a;
1079 p_dtd := p_dtd||' user_note CDATA #IMPLIED '||a;
1080 p_dtd := p_dtd||' source CDATA #REQUIRED '||a;
1081 p_dtd := p_dtd||' version_id CDATA #IMPLIED >'||a;
1082 p_dtd := p_dtd||'<!ELEMENT files (file+)>'||a;
1083 p_dtd := p_dtd||'<!ELEMENT file (#PCDATA)>'||a;
1084 p_dtd := p_dtd||'<!ATTLIST file id CDATA #IMPLIED'||a;
1085 p_dtd := p_dtd||' body (yes | no | merge | label) "merge"' || a;
1086 p_dtd := p_dtd||' query_id CDATA #IMPLIED ' || a;
1087 p_dtd := p_dtd||' txt_id CDATA #IMPLIED ' || a;
1088 p_dtd := p_dtd||' pdf_id CDATA #IMPLIED ' || a;
1089 p_dtd := p_dtd||' rtf_id CDATA #IMPLIED ' || a;
1090 p_dtd := p_dtd||' content_no CDATA #REQUIRED ' || a;
1091 p_dtd := p_dtd||' labels_per_page CDATA #IMPLIED ' || a;
1092 p_dtd := p_dtd||' cols_per_page CDATA #IMPLIED ' || a;
1093 p_dtd := p_dtd||' lines_per_label CDATA #IMPLIED>' || a;
1094 p_dtd := p_dtd||'<!ELEMENT bind (record+)>'||a;
1095 p_dtd := p_dtd||'<!ELEMENT bind_var (#PCDATA)>'||a;
1096 p_dtd := p_dtd||'<!ATTLIST bind_var bind_type (VARCHAR2 | NUMBER | DATE) #REQUIRED '||a;
1097 p_dtd := p_dtd||' bind_object CDATA #REQUIRED>'||a;
1098 p_dtd := p_dtd||'<!ELEMENT record (bind_var+)>'||a;
1099 p_dtd := p_dtd||'<!ELEMENT batch (view | list | mass | segment | label)>'||a;
1100 p_dtd := p_dtd||'<!ELEMENT view EMPTY>'||a;
1101 p_dtd := p_dtd||'<!ATTLIST view name CDATA #REQUIRED>'||a;
1102 p_dtd := p_dtd||'<!ELEMENT list (party+)>'||a;
1103 p_dtd := p_dtd||'<!ELEMENT party (media_type,record?)>'||a;
1104 p_dtd := p_dtd||'<!ATTLIST party id CDATA #IMPLIED>'||a;
1105 p_dtd := p_dtd||'<!ELEMENT mass (#PCDATA)>'||a;
1106 p_dtd := p_dtd||'<!ELEMENT segment (party+)>'||a;
1107 p_dtd := p_dtd||'<!ATTLIST segment id CDATA #REQUIRED ' ||a;
1108 p_dtd := p_dtd||' batch_index CDATA #REQUIRED '||a;
1109 p_dtd := p_dtd||' length CDATA #REQUIRED >'||a;
1110
1111 p_dtd := p_dtd||'<!ELEMENT media_type ((printer, email?, fax?)|'||a;
1112 p_dtd := p_dtd||'(email, fax?)|'||a;
1113 p_dtd := p_dtd||'(fax))>'||a;
1114
1115 p_dtd := p_dtd||'<!ELEMENT printer (#PCDATA)>'||a;
1116 p_dtd := p_dtd||'<!ELEMENT email (#PCDATA)>'||a;
1117 p_dtd := p_dtd||'<!ELEMENT fax (#PCDATA)>'||a;
1118
1119 p_dtd := p_dtd||' ]>'||a;
1120
1121 JTF_FM_UTL_V.PRINT_MESSAGE('END ' || l_full_name , JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,l_full_name);
1122 END Get_Dtd;
1123
1124 -- Utility function to replace XML tags
1125 FUNCTION REPLACE_TAG
1126 (
1127 p_string IN VARCHAR2
1128 )
1129 RETURN VARCHAR2 IS
1130 l_message VARCHAR2(32767);
1131 l_tag VARCHAR2(10);
1132 l_api_name CONSTANT VARCHAR2(30) := 'REPLACE_TAG';
1133 l_full_name CONSTANT VARCHAR2(100) := G_PKG_NAME || '.' || l_api_name;
1134 BEGIN
1135 JTF_FM_UTL_V.PRINT_MESSAGE('BEGIN ' || l_full_name, JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,l_full_name);
1136
1137
1138 IF LENGTH(p_string) <> 0
1139 THEN
1140 -- Initialize the string
1141 l_message := p_string;
1142
1143 -- Replace the tags <,>,',&," with corresponding xml tags.
1144 l_tag := '&' || 'amp;';
1145 l_message := replace(l_message, '&', l_tag);
1146
1147 l_tag := '&' || 'lt;';
1148 l_message := replace(l_message, '<', l_tag);
1149
1150 l_tag := '&' || 'gt;';
1151 l_message := replace(l_message, '>', l_tag);
1152
1153 l_tag := '&' || 'quot;';
1154 l_message := replace(l_message, '"', l_tag);
1155
1156 l_tag := '&' || 'apos;';
1157 l_message := replace(l_message, '''', l_tag);
1158
1159 l_tag := '^@' || ' ';
1160 l_message := replace(l_message, '''', l_tag);
1161
1162
1163
1164 ELSE
1165
1166 JTF_FM_UTL_V.HANDLE_ERROR('JTF_FM_NO_BIND_VAR',p_string);
1167 END IF;
1168 JTF_FM_UTL_V.PRINT_MESSAGE('END' || l_full_name, JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,l_full_name);
1169
1170 RETURN l_message;
1171 END REPLACE_TAG;
1172
1173 PROCEDURE FM_SUBMIT_REQ_V1
1174 (p_api_version IN NUMBER,
1175 p_init_msg_list IN VARCHAR2,
1176 p_commit IN VARCHAR2,
1177 x_return_status OUT NOCOPY VARCHAR2,
1178 x_msg_count OUT NOCOPY NUMBER,
1179 x_msg_data OUT NOCOPY VARCHAR2,
1180 p_fulfill_electronic_rec IN JTF_FM_OCM_REQUEST_GRP.FULFILL_ELECTRONIC_REC_TYPE,
1181 fm_pvt_rec IN FM_PVT_REC_TYPE
1182
1183 )
1184 IS
1185 l_api_name CONSTANT VARCHAR2(30) := 'FM_SUBMIT_REQ_V1';
1186 l_api_version CONSTANT NUMBER := 1.0;
1187 l_full_name CONSTANT VARCHAR2(100) := G_PKG_NAME ||'.'|| l_api_name;
1188 l_user_id NUMBER := -1;
1189 l_login_user_id NUMBER := -1;
1190 l_login_user_status NUMBER;
1191 l_Error_Msg VARCHAR2(32767);
1192 l_request_queue VARCHAR2(50) := NULL;
1193 l_enqueue_options dbms_aq.enqueue_options_t;
1194 l_message_properties dbms_aq.message_properties_t;
1195 l_message_handle RAW(16);
1196 l_message VARCHAR2(32767) := '';
1197 l_dtd VARCHAR2(32767);
1198 l_mesg RAW(32767);
1199 l_temp VARCHAR2(32767);
1200 l_request CLOB;
1201 l_amount INTEGER;
1202 l_buffer RAW(32767);
1203 l_request_dtd BLOB;
1204 l_pattern RAW(100);
1205 l_position INTEGER := 0;
1206 l_submit_dt DATE;
1207 l_meaning VARCHAR2(100) := NULL;
1208 l_server_id NUMBER;
1209 l_count NUMBER := -1;
1210 l_login_id NUMBER;
1211 l_resp_id NUMBER;
1212 l_org_id NUMBER;
1213 b VARCHAR2(1);
1214 c VARCHAR2(1);
1215 a VARCHAR2(2);
1216 l_mass_req_q VARCHAR2(30);
1217 l_batch_req_q VARCHAR2(30);
1218 l_mp_req_q VARCHAR2(30);
1219 l_bp_req_q VARCHAR2(30);
1220 l_single_req_q VARCHAR2(30);
1221 l_media_type VARCHAR2(30);
1222 l_parser xmlparser.parser;
1223 l_bypass_flag VARCHAR2(30);
1224 l_request_type VARCHAR2(30);
1225 l_hdtd VARCHAR2(1000);
1226 l_enc VARCHAR2(200) := GET_ENCODING();
1227 l_1 Number;
1228 xml_length NUMBER;
1229 loop_count NUMBER;
1230 data_len NUMBER := 2000;
1231
1232
1233
1234 -- mpetrosi 4-oct-2001 added a.f_deletedflag is null
1235 -- mpetrosi 15-oct-2001 added b.f_deletedflag is null
1236 CURSOR CSERV IS
1237 SELECT
1238 a.server_id
1239 FROM
1240 jtf_fm_groups_all a,
1241 jtf_fm_group_fnd_user b,
1242 jtf_fm_fnd_user_v c
1243 WHERE
1244 a.group_id = b.group_id
1245 AND
1246 b.user_id = c.user_id
1247 AND
1248 b.user_id = p_fulfill_electronic_rec.requestor_id
1249 AND
1250 a.f_deletedflag is null
1251 AND
1252 b.f_deletedflag is null;
1253
1254
1255 CURSOR CREQID IS
1256 SELECT
1257 count(hist_req_id)
1258 FROM
1259 JTF_FM_REQUEST_HISTORY
1260 WHERE
1261 hist_req_id = fm_pvt_rec.request_id;
1262
1263 CURSOR CREQ_Q IS
1264 SELECT
1265 DECODE(fm_pvt_rec.queue,'M', MASS_REQUEST_Q, 'B', BATCH_REQUEST_Q,'MP',
1266 MASS_PAUSE_Q ,'BP', BATCH_PAUSE_Q,REQUEST_QUEUE_NAME)
1267 FROM
1268 JTF_FM_SERVICE_ALL
1269 WHERE
1270 SERVER_ID = l_server_id;
1271
1272 BEGIN
1273 -- Standard begin of API savepoint
1274 JTF_FM_UTL_V.PRINT_MESSAGE('BEGIN ' || l_full_name , JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,l_full_name);
1275
1276
1277 SAVEPOINT FM_SUBMIT_REQ_V1;
1278 a := '';
1279 IF NOT FND_API.Compatible_API_Call (l_api_version,p_api_version,
1280 l_api_name,G_PKG_NAME)
1281 THEN
1282 RAISE
1283 FND_API.G_EXC_UNEXPECTED_ERROR;
1284 END IF; -- IF NOT FND_API.Compatible_API_Call
1285
1286 --Initialize message list if p_init_msg_list is TRUE.
1287
1288 IF FND_API.To_Boolean (p_init_msg_list)
1289 THEN
1290 FND_MSG_PUB.initialize;
1291 END IF; -- FND_API.To_Boolean
1292
1293 -- Debug Message
1294
1295 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1296 THEN
1297 FND_MESSAGE.Set_Name('JTF','JTF_FM_API_DEBUG_MESSAGE');
1298 FND_MESSAGE.Set_Token('ARG1',l_full_name||': Start');
1299 FND_MSG_PUB.Add;
1300 END IF; -- IF FND_MSG_PUB.Check_Msg_Level
1301
1302 -- Initialize API return status to success
1303
1304 x_return_status := FND_API.G_RET_STS_SUCCESS;
1305 -- Check if the user_id(agent_id) is NULL
1306
1307 IF (p_fulfill_electronic_rec.requestor_id IS NULL)
1308 THEN
1309 l_Error_Msg := 'Must pass p_user_id parameter';
1310 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1311 THEN
1312 FND_MESSAGE.set_name('JTF', 'JTF_FM_API_MISSING_USER_ID');
1313 FND_MSG_PUB.Add;
1314 END IF; -- FND_MSG_PUB.check_msg_level
1315 RAISE FND_API.G_EXC_ERROR;
1316
1317 -- Check if the Content_XML is NULL
1318 ELSIF(fm_pvt_rec.content_XML IS NULL) -- IF (p_user_id IS NULL
1319 THEN
1320 l_Error_Msg := 'Must pass a valid Content_XML parameter';
1321 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1322 THEN
1323 FND_MESSAGE.set_name('JTF', 'JTF_FM_API_MISSING_CONTENT_XML');
1324 FND_MSG_PUB.Add;
1325 END IF; -- IF FND_MSG_PUB.check_msg_level
1326 RAISE FND_API.G_EXC_ERROR;
1327
1328 ELSIF(fm_pvt_rec.request_id IS NULL) -- IF (p_user_id IS NULL
1329 THEN
1330 l_Error_Msg := 'Must pass p_request_id parameter';
1331 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1332 THEN
1333 FND_MESSAGE.set_name('JTF', 'JTF_FM_API_MISSING_REQUEST_ID');
1334 FND_MSG_PUB.Add;
1335 END IF; -- IF FND_MSG_PUB.check_msg_level
1336 RAISE FND_API.G_EXC_ERROR;
1337 ELSE -- IF (p_user_id IS NULL)
1338 OPEN CREQID;
1339 FETCH CREQID INTO l_count;
1340 CLOSE CREQID;
1341 IF l_count >= 1
1342 THEN
1343 l_Error_Msg := 'A request with the request_id passed already'
1344 || 'exists in the JTF_FM_REQUEST_HISTORY table';
1345
1346 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1347 THEN
1348 FND_MESSAGE.set_name('JTF', 'JTF_FM_API_REQUESTID_REUSED');
1349 FND_MSG_PUB.Add;
1350 END IF; -- IF FND_MSG_PUB.check_msg_level
1351
1352 RAISE FND_API.G_EXC_ERROR;
1353 END IF; -- IF l_count >= 1
1354
1355 -- if server_id has not been passed, get it from the fulfillment tables
1356 -- based on the user_id passed
1357 IF (p_fulfill_electronic_rec.server_group = FND_API.G_MISS_NUM OR p_fulfill_electronic_rec.server_group IS NULL)
1358 THEN
1359 OPEN CSERV;
1360 FETCH CSERV INTO l_server_id;
1361 IF (CSERV%NOTFOUND)
1362 THEN
1363 JTF_FM_UTL_V.PRINT_MESSAGE('No server found for this User',JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1364 SELECT Fnd_Profile.value('JTF_FM_DEFAULT_SERVER') INTO l_server_id FROM DUAL;
1365 JTF_FM_UTL_V.PRINT_MESSAGE('DEFAULT SERVER will be used' || l_server_id
1366 ,JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1367 IF l_server_id IS NULL
1368 THEN
1369 JTF_FM_UTL_V.PRINT_MESSAGE('No Default Server found',JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1370 l_Error_Msg := 'Could not find server_id for the user passed';
1371 JTF_FM_UTL_V.HANDLE_ERROR('JTF_FM_API_MISSING_SERVER_ID',p_fulfill_electronic_rec.server_group);
1372 END IF;
1373 --RAISE FND_API.G_EXC_ERROR;
1374 END IF; -- IF (CSERV%NOTFOUND)
1375 CLOSE CSERV;
1376 ELSE -- IF (p_server_id = FND_API.G_MISS_NUM OR p_server_id IS NULL)
1377 l_server_id := p_fulfill_electronic_rec.server_group;
1378 END IF; -- IF (p_server_id = FND_API.G_MISS_NUM OR p_server_id IS NULL)
1379 JTF_FM_UTL_V.PRINT_MESSAGE('Server_id Got: ' || l_server_id,JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1380 JTF_FM_UTL_V.PRINT_MESSAGE('p_queue_response: ' || fm_pvt_rec.queue,JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1381 OPEN CREQ_Q;
1382 FETCH CREQ_Q INTO l_request_queue;
1383 IF (CREQ_Q%NOTFOUND)
1384 THEN
1385 JTF_FM_UTL_V.PRINT_MESSAGE('Fetched queue unsuccessful',JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1386 l_Error_Msg := 'Could not find request_queue_name in the database';
1387 JTF_FM_UTL_V.HANDLE_ERROR('JTF_FM_API_QUEUE_NOTFOUND',to_char(l_server_id));
1388 RAISE
1389 FND_API.G_EXC_ERROR;
1390 END IF;
1391 CLOSE CREQ_Q;
1392 JTF_FM_UTL_V.PRINT_MESSAGE('Updating record in history',JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1393 l_submit_dt := sysdate;
1394
1395 --Bug Fix #3214491
1396
1397 IF p_fulfill_electronic_rec.media_types <> NULL THEN
1398 l_media_type := GET_ELEC_MEDIA_TYPE(p_fulfill_electronic_rec.media_types);
1399 ELSE
1400 l_media_type := GET_MEDIA_TYPE(fm_pvt_rec.content_xml);
1401 END IF;
1402
1403 -- Create a hitory record for the request
1404 BEGIN
1405
1406 INSERT INTO JTF_FM_REQUEST_HISTORY_ALL
1407 (
1408 HIST_REQ_ID,
1409 SUBMIT_DT_TM,
1410 REQUEST,
1411 LAST_UPDATE_DATE,
1412 LAST_UPDATED_BY,
1413 LAST_UPDATE_LOGIN,
1414 CREATION_DATE,
1415 CREATED_BY,
1416 REQUEST_TYPE,
1417 MEDIA_TYPE
1418 )
1419 VALUES
1420 (
1421 fm_pvt_rec.request_id,
1422 l_submit_dt,
1423 empty_clob(),
1424 l_submit_dt,
1425 FND_GLOBAL.USER_ID,
1426 FND_GLOBAL.LOGIN_ID,
1427 l_submit_dt,
1428 FND_GLOBAL.USER_ID,
1429 fm_pvt_rec.queue,
1430 l_media_type
1431 );
1432
1433 JTF_FM_UTL_V.PRINT_MESSAGE('Updating record in status',JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1434 -- Create a status record for the request
1435 INSERT INTO JTF_FM_STATUS_ALL
1436 (
1437 REQUEST_ID,
1438 SUBMIT_DT_TM,
1439 LAST_UPDATE_DATE,
1440 LAST_UPDATED_BY,
1441 LAST_UPDATE_LOGIN,
1442 CREATION_DATE,
1443 CREATED_BY
1444 )
1445 VALUES
1446 (
1447 fm_pvt_rec.request_id,
1448 l_submit_dt,
1449 l_submit_dt,
1450 FND_GLOBAL.USER_ID,
1451 FND_GLOBAL.CONC_LOGIN_ID,
1452 l_submit_dt,
1453 FND_GLOBAL.USER_ID
1454 );
1455
1456 JTF_FM_UTL_V.PRINT_MESSAGE('Updated record in status******',JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1457 INSERT INTO JTF_FM_EMAIL_STATS
1458 (
1459 REQUEST_ID,
1460 TOTAL,
1461 SENT,
1462 MALFORMED,
1463 BOUNCED,
1464 OPENED,UNSUBSCRIBED,DO_NOT_CONTACT,CREATED_BY,
1465 CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE)
1466 VALUES (fm_pvt_rec.request_id,0,0,0,0,
1467 0,0,0,FND_GLOBAL.USER_ID,l_submit_dt,
1468 FND_GLOBAL.USER_ID,l_submit_dt);
1469
1470 EXCEPTION
1471 WHEN NO_DATA_FOUND THEN
1472 l_Error_Msg := 'Data not found for the request_id passed';
1473 JTF_FM_UTL_V.HANDLE_ERROR('JTF_FM_API_REQUEST_NOTFOUND',fm_pvt_rec.request_id);
1474 RAISE FND_API.G_EXC_ERROR;
1475
1476
1477 END;
1478
1479
1480 -- Check if the update was successful
1481
1482
1483 JTF_FM_UTL_V.PRINT_MESSAGE('Forming the request ..',JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1484 -- Insert the Document Type Definition
1485 JTF_FM_UTL_V.Get_Dtd(l_dtd);
1486 -- start forming the request
1487 l_message := l_message||'<ffm_request id="'||to_char(fm_pvt_rec.request_id)||'" '||a;
1488 l_message := l_message || 'submit_time="'||to_char(l_submit_dt, 'YYYY-MM-DD HH24:MI:SS')||'" '||a;
1489 --l_message := l_message || 'status="NEW_REQUEST" '||a;
1490 -- new code added for previewing
1491 IF fm_pvt_rec.preview = FND_API.G_TRUE
1492 THEN
1493 l_message := l_message || 'status="PREVIEW" '||a;
1494 ELSIF fm_pvt_rec.preview = 'TEST'
1495 THEN
1496 l_message := l_message || 'status="TEST" ' || a;
1497 ELSE
1498 l_message := l_message || 'status="NEW" '||a;
1499 END IF; -- IF (p_preview
1500
1501 -- new code added for previewing
1502
1503 IF (JTF_FM_UTL_V.IS_FLD_VALID(p_fulfill_electronic_rec.template_id))
1504 THEN
1505 l_message := l_message || 'template="'||to_char(p_fulfill_electronic_rec.template_id)||'" '||a;
1506 END IF; -- IF p_template_id
1507 l_message := l_message || 'priority="'||to_char(fm_pvt_rec.priority)||'" '||a;
1508 IF fm_pvt_rec.party_id = -229929
1509 THEN
1510 l_message := l_message || 'user_history="NO" '||a;
1511 ELSE -- IF p_party_id
1512 l_message := l_message || 'user_history="YES" '||a;
1513 END IF; -- IF p_party_id
1514 l_message := l_message || 'api_version="'||to_char(l_api_version)|| '" '||a;
1515
1516 -- add the application info
1517
1518 l_message := l_message || 'user_id="'||to_char(p_fulfill_electronic_rec.requestor_id)||'" '||a;
1519 IF (fm_pvt_rec.party_id <> FND_API.G_MISS_NUM AND fm_pvt_rec.party_id >= 0)
1520 THEN
1521 l_message := l_message || 'party_id="'||to_char(fm_pvt_rec.party_id)||'" '||a;
1522 END IF; -- IF (p_party_id
1523
1524 IF (JTF_FM_UTL_V.IS_FLD_VALID(p_fulfill_electronic_rec.subject))
1525 THEN
1526 l_message := l_message || 'subject="'||
1527 JTF_FM_UTL_V.REPLACE_TAG(p_fulfill_electronic_rec.subject)||'" '||a;
1528 END IF; -- IF (p_subject
1529
1530 IF (JTF_FM_UTL_V.IS_FLD_VALID(p_fulfill_electronic_rec.source_code_id))
1531 THEN
1532 l_message := l_message || 'source_code_id="'||to_char(p_fulfill_electronic_rec.source_code_id)||'" '||a;
1533 END IF; -- IF (p_source_code_id
1534 IF (JTF_FM_UTL_V.IS_FLD_VALID(p_fulfill_electronic_rec.source_code))
1535 THEN
1536 l_message := l_message || 'source_code="'||p_fulfill_electronic_rec.source_code||'" '||a;
1537 END IF; -- IF (p_source_code
1538
1539 IF (JTF_FM_UTL_V.IS_FLD_VALID(p_fulfill_electronic_rec.object_type))
1540 THEN
1541 l_message := l_message || 'object_type="'||p_fulfill_electronic_rec.object_type||'" '||a;
1542 END IF; -- IF (p_object_type
1543 IF (JTF_FM_UTL_V.IS_FLD_VALID(p_fulfill_electronic_rec.object_id))
1544 THEN
1545 l_message := l_message || 'object_id="'||to_char(p_fulfill_electronic_rec.object_id)||'" '||a;
1546 END IF; -- IF (p_object_id
1547 IF (JTF_FM_UTL_V.IS_FLD_VALID(p_fulfill_electronic_rec.order_id))
1548 THEN
1549 l_message := l_message || 'order_id="'||to_char(p_fulfill_electronic_rec.order_id)||'" '||a;
1550 END IF; -- IF (p_order_id
1551 JTF_FM_UTL_V.PRINT_MESSAGE('doc_id' ||to_char(fm_pvt_rec.doc_id), JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,l_full_name);
1552 IF (JTF_FM_UTL_V.IS_FLD_VALID(fm_pvt_rec.doc_id))
1553 THEN
1554 l_message := l_message || 'doc_id="'||to_char(fm_pvt_rec.doc_id)||'" '||a;
1555 l_message := l_message || 'doc_ref="'||fm_pvt_rec.doc_ref||'" '||a;
1556 ELSE -- IF (p_doc_id
1557 -- if the doc_id is not passed then use the fulfillment doc_id
1558 l_message := l_message || 'doc_id="'||to_char(fm_pvt_rec.request_id)||'" '||a;
1559 l_message := l_message || 'doc_ref="'||'JFUF'||'" '||a;
1560 END IF; -- IF (p_doc_id
1561
1562 l_message := l_message || 'app_id="'||'690'||'" '||a;
1563 l_login_id := FND_GLOBAL.CONC_LOGIN_ID;
1564 -- This IF clause is not required in production.
1565
1566 IF l_login_id <= 0
1567 THEN
1568 l_login_id := -1;
1569 END IF; -- IF l_login_id
1570
1571 l_message := l_message || 'login_id="'||to_char(l_login_id)||'" '||a;
1572 l_resp_id := FND_GLOBAL.RESP_ID;
1573
1574 IF l_resp_id <= 0 OR l_resp_id IS NULL
1575 THEN
1576 l_resp_id := -1;
1577 END IF; -- IF l_resp_id
1578
1579 l_message := l_message || 'resp_id="'||to_char(l_resp_id)||'" '||a;
1580 -- Following code added by sxkrishn for org_id
1581 select to_number(decode(substrb(userenv('CLIENT_INFO'),1,1),' ',null,substrb(userenv('CLIENT_INFO'),1,10)))
1582 into l_org_id
1583 from dual;
1584
1585 l_message := l_message || 'org_id="'||to_char(l_org_id)||'" '||a;
1586
1587 --adding the new bypass flag for unsubscribe and overriding tca
1588 --at request level
1589 l_bypass_flag := VALIDATE_BYPASS(p_fulfill_electronic_rec.stop_list_bypass);
1590 l_message := l_message || 'bypass="'|| l_bypass_flag ||'" '||a;
1591 l_message := l_message || 'email_body="'|| VALIDATE_EMAIL_FORMAT(p_fulfill_electronic_rec.email_format) ||'" '||a;
1592 l_message := l_message || 'pasta_printable="'||CONFIRM_PASTA_PRINTABLE(fm_pvt_rec.request_id)||'"'||a;
1593 l_message := l_message || '> ';
1594
1595 l_count := INSTR(fm_pvt_rec.content_xml, '<');
1596 IF fm_pvt_rec.queue = 'B' or fm_pvt_rec.queue = 'BP' or fm_pvt_rec.queue = 'M' or fm_pvt_rec.queue = 'MP'
1597 THEN
1598 l_message := l_message || SUBSTR(fm_pvt_rec.content_xml, l_count) ;
1599 ELSE
1600 l_message := l_message|| '<items>' || SUBSTR(fm_pvt_rec.content_xml, l_count) || '</items>';
1601 END IF;
1602
1603 IF (JTF_FM_UTL_V.IS_FLD_VALID(p_fulfill_electronic_rec.extended_header))
1604 THEN
1605 l_message := l_message || '<headers>' || p_fulfill_electronic_rec.extended_header|| '</headers>' ||a;
1606 END IF; -- IF (p_extended_header
1607 l_message := l_message || '</ffm_request> '||a;
1608
1609
1610 xml_length := Length(l_message);
1611 loop_count := FLOOR(xml_length/2000);
1612 --DBMS_OUTPUT.PUT_LINE('loop_count is ' || to_char(loop_count));
1613 IF MOD(xml_length,2000) <> 0 THEN
1614 loop_count := loop_count +1;
1615 END IF;
1616 --DBMS_OUTPUT.PUT_LINE('loop_count is ' || to_char(loop_count));
1617 JTF_FM_UTL_V.PRINT_MESSAGE('LENGTH of the MSG:'||to_char(LENGTH(l_message)),JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1618 JTF_FM_UTL_V.PRINT_MESSAGE('XML FORMED IS:' ,JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1619 -- The following code added to print the entire XML in log messages
1620 FOR l_row in 1 .. loop_count
1621 LOOP
1622 --DBMS_OUTPUT.PUT_LINE('First variable--------:' || to_char((l_row-1)*data_len +1));
1623 --DBMS_OUTPUT.PUT_LINE('Second variable***********:' || to_char(data_len*l_row));
1624 L_ERROR_MSG := SUBSTRB(l_message, (l_row-1)*data_len +1, data_len*l_row);
1625 JTF_FM_UTL_V.PRINT_MESSAGE(l_error_msg ,JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1626
1627 END LOOP;
1628
1629
1630 -- end of request
1631
1632 JTF_FM_UTL_V.PRINT_MESSAGE('Successfully formed the request',JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1633
1634
1635 l_temp := l_dtd || l_message;
1636
1637 -- validate the xml
1638
1639 l_parser := xmlparser.newparser();
1640 xmlparser.setvalidationmode(l_parser, TRUE);
1641 xmlparser.showwarnings(l_parser, TRUE);
1642
1643 --xmlparser.SETDOCTYPE(l_parser,l_dtd);
1644
1645 xmlparser.parseBuffer(l_parser, l_temp);
1646 xmlparser.FREEPARSER(l_parser);
1647 -- end validation
1648
1649
1650 -- Bug # 3226158 Added enc header to the XML
1651 l_hdtd := '<?xml version = "1.0" encoding ="' || l_enc || '" ?>' ||a;
1652 l_message := l_hdtd || l_message;
1653
1654
1655
1656 JTF_FM_UTL_V.PRINT_MESSAGE('Validated and set the parser free',JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1657 -- Convert the message to RAW so that it can be enqueued as RAW payload
1658 l_mesg := UTL_RAW.CAST_TO_RAW(l_message);
1659 JTF_FM_UTL_V.PRINT_MESSAGE('cast the message to raw',JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1660
1661 -- Set the default message properties
1662
1663 l_message_properties.priority := fm_pvt_rec.priority;
1664 JTF_FM_UTL_V.PRINT_MESSAGE('set the priority',JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1665
1666 -- Enqueue the request in to the Request queue for the fulfillment Processor
1667
1668 dbms_aq.enqueue(queue_name => l_request_queue,
1669 enqueue_options => l_enqueue_options,
1670 message_properties => l_message_properties,
1671 payload => l_mesg, msgid => l_message_handle);
1672
1673 JTF_FM_UTL_V.PRINT_MESSAGE('Successfully enqueued the request',JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1674
1675 -- Insert the XML request into the History record created above
1676
1677
1678 SELECT
1679 REQUEST INTO l_request
1680 FROM
1681 JTF_FM_REQUEST_HISTORY_ALL
1682 WHERE
1683 HIST_REQ_ID = fm_pvt_rec.request_id
1684 AND
1685 SUBMIT_DT_TM = l_submit_dt
1686 FOR UPDATE;
1687
1688 DBMS_LOB.OPEN(l_request, DBMS_LOB.LOB_READWRITE);
1689 l_amount := LENGTH(l_message);
1690 DBMS_LOB.WRITE (l_request, l_amount, 1, l_message);
1691 DBMS_LOB.CLOSE (l_request);
1692
1693 l_meaning := 'SUBMITTED';
1694 JTF_FM_UTL_V.PRINT_MESSAGE('Before Updating History table --after enque',
1695 JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1696
1697
1698
1699 --- Determine the request type as follows because the p_queue_response can be set to FND_API.G_TRUE
1700 -- The value then is 'T' which can be misinterpreted as Test Req.
1701 -- So, check to make sure it is really test req before updating the table.
1702 IF fm_pvt_rec.preview = 'TEST' THEN
1703 l_request_type := 'T';
1704 ELSE
1705 l_request_type := fm_pvt_rec.queue;
1706 END IF;
1707
1708
1709 -- Updating the history table
1710 -- Mod to update org_id when sent
1711 BEGIN
1712
1713 UPDATE JTF_FM_REQUEST_HISTORY_ALL
1714 SET
1715 TEMPLATE_ID = decode(p_fulfill_electronic_rec.template_id, FND_API.G_MISS_NUM,
1716 NULL,p_fulfill_electronic_rec.template_id),
1717 USER_ID = p_fulfill_electronic_rec.requestor_id,
1718 PRIORITY = fm_pvt_rec.priority,
1719 LAST_UPDATE_DATE = sysdate,
1720 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1721 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID,
1722 SOURCE_CODE_ID = decode(p_fulfill_electronic_rec.source_code_id, FND_API.G_MISS_NUM,
1723 NULL,p_fulfill_electronic_rec.source_code_id),
1724 SOURCE_CODE = decode(p_fulfill_electronic_rec.source_code, FND_API.G_MISS_CHAR,
1725 NULL,p_fulfill_electronic_rec.source_code),
1726 OBJECT_TYPE = decode(p_fulfill_electronic_rec.object_type, FND_API.G_MISS_CHAR,
1727 NULL,p_fulfill_electronic_rec.object_type),
1728 OBJECT_ID = decode(p_fulfill_electronic_rec.object_id, FND_API.G_MISS_NUM,
1729 NULL,p_fulfill_electronic_rec.object_id),
1730 ORDER_ID = decode(p_fulfill_electronic_rec.order_id, FND_API.G_MISS_NUM,
1731 NULL,p_fulfill_electronic_rec.order_id),
1732 RESUBMIT_COUNT = 1,
1733 SERVER_ID = l_server_id,
1734 MESSAGE_ID = l_message_handle,
1735 OUTCOME_CODE = l_meaning,
1736 ORG_ID = l_org_id,
1737 OBJECT_VERSION_NUMBER = 1,
1738 REQUEST_TYPE = l_request_type
1739 WHERE
1740 HIST_REQ_ID = fm_pvt_rec.request_id;
1741 --AND
1742 --SUBMIT_DT_TM = l_submit_dt;
1743 EXCEPTION
1744 WHEN NO_DATA_FOUND THEN
1745 l_Error_Msg := 'Data not found for the request_id passed';
1746 JTF_FM_UTL_V.HANDLE_ERROR('JTF_FM_API_REQUEST_NOTFOUND',fm_pvt_rec.request_id);
1747 RAISE FND_API.G_EXC_ERROR;
1748
1749
1750 END;
1751
1752
1753 JTF_FM_UTL_V.PRINT_MESSAGE('Before Updating Status table --after enque',
1754 JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1755
1756
1757 -- Update tables with the type of request.
1758 -- Updating the status table
1759 BEGIN
1760 UPDATE JTF_FM_STATUS_ALL
1761 SET
1762 TEMPLATE_ID = decode(p_fulfill_electronic_rec.template_id, FND_API.G_MISS_NUM,
1763 NULL,p_fulfill_electronic_rec.template_id),
1764 USER_ID = p_fulfill_electronic_rec.requestor_id,
1765 PRIORITY = fm_pvt_rec.priority,
1766 LAST_UPDATE_DATE = sysdate,
1767 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1768 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID,
1769 SOURCE_CODE_ID = decode(p_fulfill_electronic_rec.source_code_id, FND_API.G_MISS_NUM,
1770 NULL,p_fulfill_electronic_rec.source_code_id),
1771 SOURCE_CODE = decode(p_fulfill_electronic_rec.source_code, FND_API.G_MISS_CHAR,
1772 NULL,p_fulfill_electronic_rec.source_code),
1773 OBJECT_TYPE = decode(p_fulfill_electronic_rec.object_type, FND_API.G_MISS_CHAR,
1774 NULL,p_fulfill_electronic_rec.object_type),
1775 OBJECT_ID = decode(p_fulfill_electronic_rec.object_id, FND_API.G_MISS_NUM,
1776 NULL,p_fulfill_electronic_rec.object_id),
1777 ORDER_ID = decode(p_fulfill_electronic_rec.order_id, FND_API.G_MISS_NUM, NULL,
1778 p_fulfill_electronic_rec.order_id),
1779 SERVER_ID = l_server_id,REQUEUE_COUNT = 1,
1780 MESSAGE_ID = l_message_handle,
1781 REQUEST_STATUS = l_meaning,
1782 ORG_ID = l_org_id,
1783 OBJECT_VERSION_NUMBER = 1
1784
1785 WHERE
1786 REQUEST_ID = fm_pvt_rec.request_id;
1787 --AND
1788 --SUBMIT_DT_TM = l_submit_dt;
1789 EXCEPTION
1790 WHEN NO_DATA_FOUND THEN
1791 l_Error_Msg := 'Data not found for the request_id passed';
1792 JTF_FM_UTL_V.HANDLE_ERROR('JTF_FM_API_REQUEST_NOTFOUND',fm_pvt_rec.request_id);
1793 RAISE FND_API.G_EXC_ERROR;
1794
1795
1796 END;
1797
1798
1799
1800
1801
1802 -- END IF; -- IF (l_request_queue is NULL)
1803 END IF; -- IF (p_user_id IS NULL)
1804
1805 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1806 THEN
1807 FND_MESSAGE.Set_Name('JTF', 'JTF_FM_API_SUCCESS_MESSAGE');
1808 FND_MESSAGE.Set_Token('ARG1', l_full_name);
1809 FND_MSG_PUB.Add;
1810 END IF; -- IF FND_MSG_PUB.Check_Msg_Level
1811
1812 --Standard check of commit
1813
1814 IF FND_API.To_Boolean(p_commit)
1815 THEN
1816 COMMIT WORK;
1817 END IF; -- IF FND_API.To_Boolean
1818
1819 -- Debug Message
1820 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1821 THEN
1822 FND_MESSAGE.Set_Name('JTF','JTF_FM_API_DEBUG_MESSAGE');
1823 FND_MESSAGE.Set_Token('ARG1',l_full_name||': End');
1824 FND_MSG_PUB.Add;
1825 END IF; -- IF FND_MSG.PUB.Check_Msg_level
1826
1827 --Standard call to get message count and if count=1, get the message
1828 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.g_false,
1829 p_count => x_msg_count,
1830 p_data => x_msg_data
1831 );
1832 EXCEPTION
1833 -----------------EXCEPTION BLOCK-------------------
1834
1835 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1836 THEN
1837 ROLLBACK TO FM_SUBMIT_REQ_V1;
1838 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1839 JTF_FM_UTL_V.ADD_ERROR_MESSAGE (l_api_name, l_Error_Msg);
1840 -- Standard call to get message count and if count=1, get the message
1841 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.g_false,
1842 p_count => x_msg_count,
1843 p_data => x_msg_data
1844 );
1845 JTF_FM_UTL_V.GET_ERROR_MESSAGE(x_msg_data);
1846
1847 WHEN FND_API.G_EXC_ERROR
1848 THEN
1849 ROLLBACK TO FM_SUBMIT_REQ_V1;
1850 x_return_status := FND_API.G_RET_STS_ERROR;
1851 JTF_FM_UTL_V.ADD_ERROR_MESSAGE (l_api_name, l_Error_Msg);
1852 JTF_FM_UTL_V.PRINT_MESSAGE('Expected Error Occured'||
1853 l_Error_Msg,JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1854 -- Standard call to get message count and if count=1, get the message
1855 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.g_false,
1856 p_count => x_msg_count,
1857 p_data => x_msg_data
1858 );
1859 JTF_FM_UTL_V.GET_ERROR_MESSAGE(x_msg_data);
1860
1861 WHEN OTHERS
1862 THEN
1863 ROLLBACK TO FM_SUBMIT_REQ_V1;
1864 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1865 JTF_FM_UTL_V.ADD_ERROR_MESSAGE (l_api_name, SQLERRM);
1866
1867 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1868 THEN
1869 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1870 END IF; -- IF FND_MSG_PUB.Check_Msg_Level
1871
1872 -- Standard call to get message count and if count=1, get the message
1873 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.g_false,
1874 p_count => x_msg_count,
1875 p_data => x_msg_data
1876 );
1877 JTF_FM_UTL_V.GET_ERROR_MESSAGE(x_msg_data);
1878
1879 -----------------END EXCEPTION BLOCK-------------------
1880 JTF_FM_UTL_V.PRINT_MESSAGE('END'||l_full_name,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1881 END FM_SUBMIT_REQ_V1;
1882
1883
1884
1885
1886 END JTF_FM_UTL_V;
1887