[Home] [Help]
PACKAGE BODY: APPS.JTF_FM_OCM_REND_REQ
Source
1 PACKAGE BODY JTF_FM_OCM_REND_REQ AS
2 /* $Header: jtfgfmrb.pls 120.6 2005/10/25 07:19:27 gjoby noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(100) := 'jtf.plsql.jtfgfmrb.JTF_FM_OCM_REND_REQ';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'jtfgfmrb.pls';
6
7
8 --Global Variables
9 G_MIME_TBL JTF_VARCHAR2_TABLE_100 :=
10 JTF_VARCHAR2_TABLE_100(
11 'TEXT/HTML',
12 'TEXT/PLAIN',
13 'APPLICATION/PDF',
14 'APPLICATION/RTF',
15 'APPLICATION/X-RTF',
16 'TEXT/RICHTEXT');
17
18 /**
19
20 **/
21
22 FUNCTION IS_REQ_ETSL(p_string VARCHAR2) RETURN BOOLEAN
23 IS
24 x_result BOOLEAN := FALSE;
25 BEGIN
26 IF (upper(p_string) = 'E' OR upper(p_string) = 'T' OR upper(p_string) = 'S'
27 OR upper(p_string) = 'L')
28 THEN
29 x_result := TRUE;
30 END IF;
31 return x_result;
32
33 END IS_REQ_ETSL;
34
35
36 FUNCTION IS_MED_EPF(p_string VARCHAR2) RETURN BOOLEAN
37 IS
38 x_result BOOLEAN := FALSE;
39 BEGIN
40 IF(INSTR(upper(p_string), 'E')> 0 OR INSTR(upper(p_string), 'P')>0 OR
41 INSTR(upper(p_string) , 'F') > 0 )
42 THEN
43 x_result := TRUE;
44 END IF;
45 return x_result;
46
47 END IS_MED_EPF;
48
49
50 PROCEDURE GET_TEST_XML
51 (
52 p_party_id IN JTF_FM_REQUEST_GRP.G_NUMBER_TBL_TYPE ,
53 p_email IN JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ,
54 p_fax IN JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ,
55 p_printer IN JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ,
56 p_media IN VARCHAR2,
57 p_content_xml IN VARCHAR2,
58 x_return_status OUT NOCOPY VARCHAR2,
59 x_test_xml OUT NOCOPY VARCHAR2
60
61 )
62 IS
63 l_api_name CONSTANT VARCHAR2(30) := 'GET_TEST_XML';
64 l_api_version CONSTANT NUMBER := 1.0;
65 l_full_name CONSTANT VARCHAR2(100) := G_PKG_NAME ||'.'|| l_api_name;
66 --
67 l_Error_Msg VARCHAR2(2000);
68 --
69 l_index BINARY_INTEGER;
70 l_printer_count INTEGER;
71 l_fax_count INTEGER;
72 l_file_path_count INTEGER;
73 l_email_count INTEGER;
74 l_message VARCHAR2(32767);
75
76
77 BEGIN
78
79 -- Initialize API return status to success
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81
82 l_message := '<items>' || p_content_xml || '</items>';
83
84 JTF_FM_UTL_V.PRINT_MESSAGE('Creating Batch XML ..',JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
85 l_index := 0;
86 -- Get the greatest index of the last entry in all the address tables.
87 IF l_index < p_fax.LAST THEN
88 l_index := p_fax.LAST;
89 END IF;
90 IF l_index < p_email.LAST THEN
91 l_index := p_email.LAST;
92 END IF;
93 IF l_index < p_printer.LAST THEN
94 l_index := p_printer.LAST;
95 END IF;
96 JTF_FM_UTL_V.PRINT_MESSAGE (to_char(l_index),JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
97
98 IF (l_index = 0) THEN
99 l_Error_Msg := 'Must pass batch address list';
100 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
101 FND_MESSAGE.set_name('JTF', 'JTF_FM_API_MISSING_BATCH_LIST');
102 FND_MSG_PUB.Add;
103 END IF;
104 RAISE FND_API.G_EXC_ERROR;
105 ELSE
106
107 IF ( INSTR(upper(p_media),'P') > 0) THEN
108 IF NOT p_printer.EXISTS(1) THEN
109 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
110 FND_MESSAGE.set_name('JTF', 'JTF_FM_API_TEST_MISS_PRINT_ADD');
111 FND_MSG_PUB.Add;
112 END IF;
113 RAISE FND_API.G_EXC_ERROR;
114 END IF;
115 END IF;
116 IF ( INSTR(upper(p_media),'E') > 0) THEN
117 IF NOT p_email.EXISTS(1) THEN
118 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
119 FND_MESSAGE.set_name('JTF', 'JTF_FM_API_TEST_MISS_EMAIL_ADD');
120 FND_MSG_PUB.Add;
121 END IF;
122 RAISE FND_API.G_EXC_ERROR;
123 END IF;
124 END IF;
125 IF ( INSTR(upper(p_media),'F') > 0) THEN
126 IF NOT p_fax.EXISTS(1) THEN
127 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
128 FND_MESSAGE.set_name('JTF', 'JTF_FM_API_TEST_MISS_FAX_ADD');
129 FND_MSG_PUB.Add;
130 END IF;
131 RAISE FND_API.G_EXC_ERROR;
132 END IF;
133 END IF;
134
135
136 l_message := l_message||'<batch><list>';
137 JTF_FM_UTL_V.PRINT_MESSAGE('Getting the greatest value ..'||TO_CHAR(l_index),
138 JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
139 FOR i IN 1..l_index LOOP
140 -- Check if atleast one destination address has been passed
141 IF( p_email.EXISTS(i) OR p_fax.EXISTS(i) OR p_printer.EXISTS(i)) THEN
142 -- For each table check if the record exists.
143 --If yes then add it to the XML
144 l_message := l_message||'<party ';
145 IF p_party_id.EXISTS(i) THEN
146 l_message := l_message || 'id= "'||to_char(p_party_id(i))||'"> ';
147 ELSE
148 l_message := l_message || '>';
149 END IF;
150 l_message := l_message||'<media_type>';
151
152 IF p_printer.EXISTS(i) THEN
153 l_message := l_message||'<printer>'||p_printer(i)||'</printer>';
154 END IF;
155 IF p_email.EXISTS(i) THEN
156 l_message := l_message||'<email>'||p_email(i)||'</email>';
157 END IF;
158 IF p_fax.EXISTS(i) THEN
159 l_message := l_message||'<fax>'||p_fax(i)||'</fax>';
160 END IF;
161
162 l_message := l_message||'</media_type></party>';
163
164
165 END IF;
166 END LOOP;
167 IF l_index > 0 THEN
168 l_message := l_message||'</list>';
169 END IF;
170
171 l_message := l_message||'</batch>';
172
173 END IF;
174 x_test_xml := l_message;
175 --SPLIT_LINE(x_test_xml,80);
176
177 -- Success message
178 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
179 THEN
180 FND_MESSAGE.Set_Name('JTF', 'JTF_FM_API_SUCCESS_MESSAGE');
181 FND_MESSAGE.Set_Token('ARG1', l_full_name);
182 FND_MSG_PUB.Add;
183 END IF;
184
185
186
187
188 END;
189
190
191 PROCEDURE INSERT_REQUEST_CONTENTS(
192 p_request_id IN NUMBER,
193 p_content_id IN NUMBER,
194 p_content_number IN NUMBER,
195 p_content_name IN VARCHAR2,
196 p_content_type IN VARCHAR2,
197 p_document_type IN VARCHAR2,
198 p_body IN VARCHAR2,
199 p_user_note IN VARCHAR2,
200 p_quantity IN NUMBER,
201 p_media_type IN VARCHAR2,
202 p_content_source IN VARCHAR2,
203 p_file_id IN NUMBER
204 )
205 IS
206 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_REQUEST_CONTENTS';
207 l_full_name CONSTANT VARCHAR2(2000) := G_PKG_NAME || '.' || l_api_name;
208
209
210 BEGIN
211 JTF_FM_UTL_V.PRINT_MESSAGE('Begin PROCEDURE INSERT_REQUEST_CONTENTS', JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
212
213
214 INSERT INTO JTF_FM_REQUEST_CONTENTS (
215 REQUEST_ID,
216 CONTENT_ID,
217 CONTENT_NUMBER,
218 CONTENT_NAME,
219 CONTENT_TYPE,
220 DOCUMENT_TYPE,
221 BODY,
222 USER_NOTES,
223 QUANTITY,
224 CREATED_BY,
225 CREATION_DATE,
226 LAST_UPDATED_BY,
227 LAST_UPDATE_DATE,
228 LAST_UPDATE_LOGIN,
229 MEDIA_TYPE,
230 CONTENT_SOURCE,
231 FND_FILE_ID)
232 VALUES (
233 p_request_id ,
234 p_content_id,
235 p_content_number,
236 p_content_name,
237 p_content_type,
238 p_document_type,
239 p_body,
240 p_user_note,
241 p_quantity ,
242 FND_GLOBAL.USER_ID ,
243 SYSDATE ,
244 FND_GLOBAL.USER_ID ,
245 SYSDATE,
246 FND_GLOBAL.LOGIN_ID ,
247 p_media_type ,
248 p_content_source ,
249 p_file_id );
250
251
252 JTF_FM_UTL_V.PRINT_MESSAGE('End PROCEDURE INSERT_REQUEST_CONTENTS', JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
253
254 EXCEPTION
255 WHEN OTHERS
256 THEN
257 JTF_FM_UTL_V.PRINT_MESSAGE('UNEXPECTED ERROR IN PROCEDURE INSERT_REQUEST_CONTENTS', JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
258
259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
260
261 END INSERT_REQUEST_CONTENTS;
262
263
264
265
266
267 FUNCTION GET_FILE_NAME (
268 p_file_id IN NUMBER
269 )
270 RETURN VARCHAR2
271 IS
272 l_file_name VARCHAR2(256);
273 l_api_name CONSTANT VARCHAR2(30) := 'GET_FILE_NAME';
274 l_full_name CONSTANT VARCHAR2(2000) := G_PKG_NAME || '.' || l_api_name;
275
276 BEGIN
277 JTF_FM_UTL_V.PRINT_MESSAGE('BEGIN' || l_full_name , JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
278 JTF_FM_UTL_V.PRINT_MESSAGE('File Id' || p_file_id, JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
279 -- Bug Fix # 3769865 (removed Userenv Lang condition)
280 SELECT FILE_NAME into l_file_name from fnd_lobs where file_id = p_file_id ;
281
282 JTF_FM_UTL_V.PRINT_MESSAGE('END function ', JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
283
284 RETURN l_file_name;
285
286 EXCEPTION
287 WHEN NO_DATA_FOUND
288 THEN
289 --l_Error_Msg := 'Could not find queue_names in the database';
290 JTF_FM_UTL_V.Handle_ERROR('JTF_FM_API_FILENAME_NOTFOUND',to_char(p_file_id));
291
292 JTF_FM_UTL_V.PRINT_MESSAGE('END' || l_full_name, JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
293
294
295 END GET_FILE_NAME;
296
297 /***
298 p_citem_id is required only for "TEST" requests
299 **/
300 FUNCTION GET_FILE_ID
301 (
302 p_citem_ver_id IN VARCHAR2,
303 p_citem_id IN VARCHAR2,
304 p_request_id IN NUMBER
305 )
306 RETURN VARCHAR2 IS
307
308 file_id NUMBER;
309 l_api_name CONSTANT VARCHAR2(30) := 'GET_FILE_ID';
310 l_full_name CONSTANT VARCHAR2(2000) := G_PKG_NAME || '.' || l_api_name;
311 l_req_count NUMBER := 0;
312
313 CURSOR get_file_query1_c is
314 SELECT attachment_file_id
315 FROM
316 ibc_content_items b,
317 ibc_citem_versions_vl a
318 WHERE
319 b.live_citem_version_id = a.citem_version_id
320 AND
321 b.content_item_id = p_citem_id;
322
323 CURSOR get_file_query2_c is
324 SELECT attachment_file_id
325 FROM ibc_citem_versions_vl a,
326 (SELECT MAX(version_number) version_number
327 FROM ibc_citem_versions_b
328 WHERE content_item_id=p_citem_id) b
329 WHERE
330 a.content_item_id = p_citem_id AND
331 a.version_number = b.version_number;
332
333
334 BEGIN
335
336 JTF_FM_UTL_V.PRINT_MESSAGE('BEGIN' || l_full_name,
337 JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
338
339 -- First we need to determine whether this is a test request or a
340 -- normal request, as the rules for determining the file id are different.
341
342 SELECT DISTINCT COUNT(REQUEST_ID) INTO l_req_count FROM JTF_FM_TEST_REQUESTS
343 WHERE REQUEST_ID = p_request_id ;
344
345 BEGIN
346 IF l_req_count > 0 THEN
347
348 OPEN get_file_query1_c;
349 FETCH get_file_query1_c INTO file_id;
350
351 IF (get_file_query1_c%NOTFOUND) THEN
352 OPEN get_file_query2_c;
353 FETCH get_file_query2_c INTO file_id;
354 IF (get_file_query2_c%NOTFOUND) THEN
355 CLOSE get_file_query1_c;
356 CLOSE get_file_query2_c ;
357 RAISE NO_DATA_FOUND;
358 END IF;
359 CLOSE get_file_query2_c ;
360 END IF;
361
362 CLOSE get_file_query1_c;
363
364 -- dbms_output.put_line('Attachment_file_id in live version is ' ||
365 -- file_id);
366
367 --**********************************************************************
368 -- The following code recommended by OCM team to allow for test
369 -- requests where user has uploaded an inprogress attachment where
370 -- it wouldn't be approved yet. Reference bug4398752
371
372 --- EXCEPTION WHEN NO_DATA_FOUND THEN
373
374 -- User can also add attachment on the fly by browsing
375 -- the filesystem. This attachment is created in "INPROGRESS"
376 -- status which the above SQL won't find.
377 -- So get the latest version of this attachment.
378
379 -- SELECT attachment_file_id INTO file_id
380 -- FROM ibc_citem_versions_vl a,
381 -- (SELECT MAX(version_number) version_number
382 -- FROM ibc_citem_versions_b
383 -- WHERE content_item_id=p_citem_id) b
384 -- WHERE
385 -- a.content_item_id = p_citem_id AND
386 -- a.version_number = b.version_number;
387
388 --dbms_output.put_line('attachment_file_id is not yet approved ' ||
389 -- file_id);
390
391 -- end OCM code. Reference bug4398752
392 --********************************************************************
393
394 ELSE
395
396 --First get the approved CITEM_VER_ID for the given content ID.
397
398 SELECT ATTACH_FID INTO file_id FROM ibc_citems_v
399 WHERE CITEM_VER_ID = p_citem_ver_id AND item_status = 'APPROVED'
400 and LANGUAGE = USERENV('LANG');
401
402 END IF;
403
404 EXCEPTION
405 WHEN NO_DATA_FOUND THEN
406 JTF_FM_UTL_V.PRINT_MESSAGE('JTF_FM_OCM_ATTACH_ABS',
407 JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
408 JTF_FM_UTL_V.HANDLE_ERROR('JTF_FM_OCM_ATTACH_ABS', p_citem_ver_id);
409 RAISE FND_API.G_EXC_ERROR;
410 END;
411
412 JTF_FM_UTL_V.PRINT_MESSAGE('END' || l_full_name,
413 JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
414 RETURN file_id;
415
416 END GET_FILE_ID;
417
418 FUNCTION IS_KNOWN_MIME_TYPE(mime_type VARCHAR2)
419 RETURN Boolean IS
420 known_mime boolean := false ;
421 counter NUMBER := 0;
422 tbl_count NUMBER :=0 ;
423 BEGIN
424 tbl_count := G_MIME_TBL.COUNT;
425 --dbms_output.put_line('tbl count is :' || tbl_count);
426 LOOP
427 EXIT WHEN tbl_count = counter;
428 counter := counter + 1;
429 --dbms_output.put_line('counter is ' || counter);
430 --dbms_output.put_line('G_MIME_TBL of counter is ' || G_MIME_TBL(counter));
431
432 IF (mime_type= G_MIME_TBL(counter)) THEN
433 known_mime := true;
434 END IF;
435 END LOOP;
436 --dbms_output.put_line('Returning known mime' );
437 RETURN known_mime;
438 END IS_KNOWN_MIME_TYPE;
439
440
441
442 --------------------------------------------------------------
443 -- PROCEDURE
444 -- GET_AND_INSERT_REQUEST_DETAILS
445 -- DESCRIPTION
446 -- Constructs the XML based on the media type and inserts the req details
447 -- JTF_FM_REQUEST_CONTENTS table
448 --
449 --
450 -- HISTORY
451 -- 11/11/03 sxkrishn Create.
452 --
453
454 ---------------------------------------------------------------
455
456 PROCEDURE GET_AND_INSERT_REQUEST_DETAILS(
457 p_request_id IN NUMBER,
458 p_content_id IN NUMBER,
459 p_user_note IN VARCHAR2,
460 p_quantity IN NUMBER,
461 p_media_type IN VARCHAR2,
462 p_query_id IN NUMBER,
463 p_email_format IN VARCHAR2,
464 p_version IN NUMBER,
465 p_content_nm IN VARCHAR2,
466 rendition_file_names IN JTF_VARCHAR2_TABLE_300,
467 rendition_mime_names IN JTF_VARCHAR2_TABLE_100,
468 rendition_mime_types IN JTF_VARCHAR2_TABLE_100,
469 rendition_file_ids IN JTF_NUMBER_TABLE,
470 x_rend_xml OUT NOCOPY VARCHAR2,
471 x_return_status OUT NOCOPY VARCHAR2,
472 x_msg_count OUT NOCOPY NUMBER,
473 x_msg_data OUT NOCOPY VARCHAR2
474 )
475 IS
476 l_api_name CONSTANT varchar2(30) := 'GET_AND_INSERT_REQUEST_DETAILS';
477 l_full_name CONSTANT varchar2(2000) := G_PKG_NAME || '.' || l_api_name;
478 att_count NUMBER := 0;
479 x_html VARCHAR2(32767) ;
480 counter NUMBER := 0;
481 html_file_id NUMBER := 0;
482 l_count_total NUMBER := 1;
483 rend_count NUMBER := 0;
484 text_file_id NUMBER := 0;
485 pdf_file_id NUMBER := 0;
486 rtf_file_id NUMBER := 0;
487 html_flag BOOLEAN := false;
488 text_flag BOOLEAN := false;
489 pdf_mime_name VARCHAR2(100);
490 rtf_mime_name VARCHAR2(100);
491 html_mime_name VARCHAR2(100);
492 text_mime_name VARCHAR2(100);
493 a VARCHAR2(1) := '';
494 l_file_name VARCHAR2(250);
495
496
497
498
499 BEGIN
500 JTF_FM_UTL_V.PRINT_MESSAGE('GET_AND_INSERT_REQUEST_DETAILS name = ' ,JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
501 JTF_FM_UTL_V.PRINT_MESSAGE('THE REQUEST ID IS name = '|| p_request_id,JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
502 IF rendition_file_ids IS NOT NULL THEN
503 att_count := rendition_file_ids.COUNT;
504 JTF_FM_UTL_V.PRINT_MESSAGE('att_count size is ' || att_count,JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
505 x_html := '<files> ' || a;
506
507 LOOP
508 EXIT WHEN att_count = counter;
509 counter := counter + 1;
510
511 --DBMS_OUTPUT.PUT_LINE('Rendition Mime Names = ' || rendition_mime_names(counter));
512
513 --DBMS_OUTPUT.PUT_LINE('------------------------------------------------');
514
515 JTF_FM_UTL_V.PRINT_MESSAGE('Rendition File ID = ' || rendition_file_ids(counter),JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
516 JTF_FM_UTL_V.PRINT_MESSAGE('Rendition File name = ' || rendition_file_names(counter),JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
517 JTF_FM_UTL_V.PRINT_MESSAGE('Rendition Mime Types = ' || rendition_mime_types(counter),JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
518 JTF_FM_UTL_V.PRINT_MESSAGE('Rendition Mime Names = ' || rendition_mime_names(counter),JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
519
520 JTF_FM_UTL_V.PRINT_MESSAGE('------------------------------------------------',JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,l_full_name);
521 IF UPPER(rendition_mime_types(counter)) = G_MIME_TBL(1) THEN
522 html_file_id := rendition_file_ids(counter);
523 html_mime_name := rendition_mime_names(counter);
524 IF l_count_total = 0 THEN
525 l_count_total := l_count_total +1;
526 END IF;
527 rend_count := rend_count + 1;
528 END IF;
529 IF UPPER(rendition_mime_types(counter)) = G_MIME_TBL(2) THEN
530 text_file_id := rendition_file_ids(counter);
531 text_mime_name := rendition_mime_names(counter);
532 IF l_count_total = 0 THEN
533 l_count_total := l_count_total +1;
534 END IF;
535 rend_count := rend_count + 1;
536 END IF;
537 IF UPPER(rendition_mime_types(counter)) = G_MIME_TBL(3) THEN
538 pdf_file_id := rendition_file_ids(counter);
539 pdf_mime_name := rendition_mime_names(counter);
540 IF l_count_total = 0 THEN
541 l_count_total := l_count_total +1;
542 END IF;
543 rend_count := rend_count + 1;
544 END IF;
545 IF UPPER(rendition_mime_types(counter)) = G_MIME_TBL(4) THEN
546 rtf_file_id := rendition_file_ids(counter);
547 rtf_mime_name := rendition_mime_names(counter);
548 IF l_count_total = 0 THEN
549 l_count_total := l_count_total +1;
550 END IF;
551 rend_count := rend_count + 1;
552 END IF;
553 IF UPPER(rendition_mime_types(counter)) = G_MIME_TBL(5) THEN
554 rtf_file_id := rendition_file_ids(counter);
555 rtf_mime_name := rendition_mime_names(counter);
556 IF l_count_total = 0 THEN
557 l_count_total := l_count_total +1;
558 END IF;
559 rend_count := rend_count + 1;
560 END IF;
561 IF UPPER(rendition_mime_types(counter)) = G_MIME_TBL(6) THEN
562 rtf_file_id := rendition_file_ids(counter);
563 rtf_mime_name := rendition_mime_names(counter);
564 IF l_count_total = 0 THEN
565 l_count_total := l_count_total +1;
566 END IF;
567 rend_count := rend_count + 1;
568 END IF;
569
570 END LOOP;
571
572 IF rend_count = 0 THEN
573 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
574 FND_MESSAGE.set_name('JTF', 'JTF_FM_OCM_CONTENT_VER_ABS');
575 FND_MESSAGE.Set_Token('ARG1',p_content_id);
576 FND_MESSAGE.Set_Token('ARG2',p_version);
577 FND_MSG_PUB.Add;
578 END IF;
579 RAISE FND_API.G_EXC_ERROR;
580
581 END IF;
582
583 -- Now begin construction of XML and insert into request contents
584 x_html := x_html || '<file ' || a;
585
586 -- Following changes were made for Labels
587 IF p_content_nm = 'L' THEN
588 x_html := x_html || ' body = "label" ' ;
589 ELSE
590 x_html := x_html || ' body = "merge" ';
591 END IF;
592
593 -- Attach the query now
594 IF p_query_id <> 0 THEN
595 x_html := x_html || ' query_id ="' || p_query_id || '"' || a;
596 END IF;
597
598
599
600
601 IF (INSTR(p_media_type, 'P') > 0) THEN
602 IF(rtf_file_id <> 0) THEN
603 -- Add the rtf id here
604 x_html := x_html || ' rtf_id ="' || rtf_file_id || '"' || a;
605 l_file_name := GET_FILE_NAME(rtf_file_id);
606 INSERT_REQUEST_CONTENTS(
607 p_request_id,
608 p_content_id,
609 l_count_total,
610 l_file_name,
611 rtf_mime_name,
612 G_MIME_TBL(4),
613 'Y',
614 p_user_note,
615 p_quantity,
616 p_media_type,
617 'ocm' ,
618 rtf_file_id);
619 ELSIF(pdf_file_id <> 0) THEN
620 -- Add the pdf id here
621 x_html := x_html || ' pdf_id ="' || pdf_file_id || '"' || a;
622 l_file_name := GET_FILE_NAME(pdf_file_id);
623 INSERT_REQUEST_CONTENTS(
624 p_request_id,
625 p_content_id,
626 l_count_total,
627 l_file_name,
628 pdf_mime_name,
629 G_MIME_TBL(3),
630 'Y',
631 p_user_note,
632 p_quantity,
633 p_media_type,
634 'ocm' ,
635 pdf_file_id);
636 ELSIF(html_file_id <> 0) THEN
637 -- Add the html id here
638 html_flag := true;
639 x_html := x_html || ' id = "' || html_file_id || '" ' || a;
640 l_file_name := GET_FILE_NAME(html_file_id);
641 INSERT_REQUEST_CONTENTS(
642 p_request_id,
643 p_content_id,
644 l_count_total,
645 l_file_name,
646 html_mime_name,
647 G_MIME_TBL(1),
648 'Y',
649 p_user_note,
650 p_quantity,
651 p_media_type,
652 'ocm' ,
653 html_file_id);
654 ELSIF(text_file_id <> 0) THEN
655 -- Add the text if here
656 text_flag := true;
657 x_html := x_html || ' txt_id ="' || text_file_id || '"' || a;
658 l_file_name := GET_FILE_NAME(text_file_id);
659 INSERT_REQUEST_CONTENTS(
660 p_request_id,
661 p_content_id,
662 l_count_total,
663 l_file_name,
664 text_mime_name,
665 G_MIME_TBL(2),
666 'Y',
667 p_user_note,
668 p_quantity,
669 p_media_type,
670 'ocm' ,
671 text_file_id);
672 ELSE
673 -- Throw Error
674 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
675 FND_MESSAGE.set_name('JTF', 'JTF_FM_OCM_CONTENT_VER_ABS');
676 FND_MESSAGE.Set_Token('ARG1',p_content_id);
677 FND_MESSAGE.Set_Token('ARG2',p_version);
678 FND_MSG_PUB.Add;
679 END IF;
680 RAISE FND_API.G_EXC_ERROR;
681 END IF;
682 END IF;
683
684 IF (INSTR(p_media_type, 'E') > 0 OR INSTR(p_media_type, 'F') > 0) THEN
685 IF html_file_id = 0 AND text_file_id = 0 THEN
686 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
687 FND_MESSAGE.set_name('JTF', 'JTF_FM_API_TXT_HTML_ABS');
688 FND_MESSAGE.Set_Token('ARG1',p_content_id);
689 FND_MESSAGE.Set_Token('ARG2',p_version);
690 FND_MSG_PUB.Add;
691 END IF;
692 RAISE FND_API.G_EXC_ERROR;
693
694 END IF;
695
696 IF JTF_FM_UTL_V.IS_FLD_VALID(p_email_format) THEN
697 IF(upper(p_email_format) = 'BOTH') THEN
698 IF NOT (html_flag) THEN
699 -- add html
700 html_flag := true;
701 x_html := x_html || ' id = "' || html_file_id || '" ' || a;
702 l_file_name := GET_FILE_NAME(html_file_id);
703 INSERT_REQUEST_CONTENTS(
704 p_request_id,
705 p_content_id,
706 l_count_total,
707 l_file_name,
708 html_mime_name,
709 G_MIME_TBL(1),
710 'Y',
711 p_user_note,
712 p_quantity,
713 p_media_type,
714 'ocm' ,
715 html_file_id);
716 END IF;
717 IF NOT(text_flag) THEN
718 -- add text
719 text_flag := true;
720 x_html := x_html || ' txt_id ="' || text_file_id || '"' || a;
721 END IF;
722 ELSIF(upper(p_email_format) = 'TEXT') THEN
723 IF NOT(text_flag) THEN
724 -- add text
725 text_flag := true;
726 x_html := x_html || ' txt_id ="' || text_file_id || '"' || a;
727 l_file_name := GET_FILE_NAME(text_file_id);
728 INSERT_REQUEST_CONTENTS(
729 p_request_id,
730 p_content_id,
731 l_count_total,
732 l_file_name,
733 text_mime_name,
734 G_MIME_TBL(2),
735 'Y',
736 p_user_note,
737 p_quantity,
738 p_media_type,
739 'ocm' ,
740 text_file_id);
741 END IF;
742 ELSE
743 IF NOT (html_flag) THEN
744 -- add html
745 html_flag := true;
746 x_html := x_html || ' id = "' || html_file_id || '" ' || a;
747 l_file_name := GET_FILE_NAME(html_file_id);
748 INSERT_REQUEST_CONTENTS(
749 p_request_id,
750 p_content_id,
751 l_count_total,
752 l_file_name,
753 html_mime_name,
754 G_MIME_TBL(1),
755 'Y',
756 p_user_note,
757 p_quantity,
758 p_media_type,
759 'ocm' ,
760 html_file_id);
761 END IF;
762 END IF;
763
764
765
766
767 ELSE -- ELSE IF JTF_FM_UTL_V.IS_FLD_VALID(p_email_format))
768 -- Get html rendition
769
770 IF NOT (html_flag) THEN
771 -- add html
772 html_flag := true;
773 x_html := x_html || ' id = "' || html_file_id || '" ' || a;
774 l_file_name := GET_FILE_NAME(html_file_id);
775 INSERT_REQUEST_CONTENTS(
776 p_request_id,
777 p_content_id,
778 l_count_total,
779 l_file_name,
780 html_mime_name,
781 G_MIME_TBL(1),
782 'Y',
783 p_user_note,
784 p_quantity,
785 p_media_type,
786 'ocm' ,
787 html_file_id);
788
789 ELSIF NOT(text_flag) THEN
790 -- add text
791 text_flag := true;
792 x_html := x_html || ' txt_id ="' || text_file_id || '"' || a;
793 l_file_name := GET_FILE_NAME(text_file_id);
794 INSERT_REQUEST_CONTENTS(
795 p_request_id,
796 p_content_id,
797 l_count_total,
798 l_file_name,
799 text_mime_name,
800 G_MIME_TBL(2),
801 'Y',
802 p_user_note,
803 p_quantity,
804 p_media_type,
805 'ocm' ,
806 text_file_id);
807
808 -- else get text rendition
809 ELSE
810 --else throw error
811 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
812 FND_MESSAGE.set_name('JTF', 'JTF_FM_API_TXT_HTML_ABS');
813 FND_MESSAGE.Set_Token('ARG1',p_content_id);
814 FND_MESSAGE.Set_Token('ARG2',p_version);
815 FND_MSG_PUB.Add;
816 END IF;
817 RAISE FND_API.G_EXC_ERROR;
818
819 END IF; -- End IF Not(html_flag
820 END IF;--END IF(JTF_FM_UTL_V.IS_FLD_VALID
821
822 END IF; -- IF (INSTR(p_media_type, 'E') > 0 OR INSTR(p_media_type, 'F') > 0)
823 x_html := x_html || ' content_no = "1" ' ||a;
824 x_html := x_html || '></file>' ||a; -- End
825
826 x_rend_xml := x_html;
827
828
829 ELSE -- Means there were no renditions available
830 --else throw error
831 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
832 FND_MESSAGE.set_name('JTF', 'JTF_FM_OCM_CONTENT_VER_ABS');
833 FND_MESSAGE.Set_Token('ARG1',p_content_id);
834 FND_MESSAGE.Set_Token('ARG2',p_version);
835 FND_MSG_PUB.Add;
836 END IF;
837 RAISE FND_API.G_EXC_ERROR;
838
839 END IF;
840
841
842
843 EXCEPTION
844
845 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
846 THEN
847
848 --x_citem_name := NULL;
849 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
850 -- Standard call to get message count and if count=1, get the message
851 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.g_false,
852 p_count => x_msg_count,
853 p_data => x_msg_data
854 );
855 JTF_FM_UTL_V.GET_ERROR_MESSAGE(x_msg_data);
856
857 WHEN FND_API.G_EXC_ERROR
858 THEN
859
860 --x_citem_name := NULL;
861 x_return_status := FND_API.G_RET_STS_ERROR;
862
863 -- Standard call to get message count and if count=1, get the message
864 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.g_false,
865 p_count => x_msg_count,
866 p_data => x_msg_data
867 );
868 JTF_FM_UTL_V.GET_ERROR_MESSAGE(x_msg_data);
869
870 WHEN OTHERS
871 THEN
872
873 --x_citem_name := NULL;
874 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
875 JTF_FM_UTL_V.ADD_ERROR_MESSAGE (l_api_name, SQLERRM);
876
877 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
878 THEN
879 FND_MSG_PUB.Add_Exc_Msg('JTF_FM_OCM_REND_REQ', G_PKG_NAME );
880 END IF; -- IF FND_MSG_PUB.Check_Msg_Level
881
882 -- Standard call to get message count and if count=1, get the message
883 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.g_false,
884 p_count => x_msg_count,
885 p_data => x_msg_data
886 );
887 JTF_FM_UTL_V.GET_ERROR_MESSAGE(x_msg_data);
888
889 for i in 0..x_msg_count loop
890 JTF_FM_UTL_V.PRINT_MESSAGE(FND_MSG_PUB.get(i,FND_API.G_FALSE),JTF_FM_UTL_V.G_LEVEL_PROCEDURE ,'JTF_FM_REQUEST_GRP.GET_OCM_REND_DETAILS');
891
892 end loop;
893
894
895
896
897 END GET_AND_INSERT_REQUEST_DETAILS;
898
899
900
901
902 --------------------------------------------------------------
903 -- PROCEDURE
904 -- GET_OCM_REND_DETAILS
905 -- DESCRIPTION
906 -- Queries IBC_CITEM_ADMIN_GRP.get_item to get details on Content Id passed
907 --
908 --
909 -- HISTORY
910 -- 10/29/02 sxkrishn Create.
911 -- Need to figure out whether Query is attached to the document
912
913 ---------------------------------------------------------------
914
915
916 PROCEDURE GET_OCM_REND_DETAILS
917 (
918 p_content_id IN NUMBER,
919 p_request_id IN NUMBER,
920 p_user_note IN VARCHAR2,
921 p_quantity IN NUMBER,
922 p_media_type IN VARCHAR2,
923 p_version IN NUMBER,
924 p_content_nm IN VARCHAR2,
925 p_email_format IN VARCHAR2,
926 x_citem_name OUT NOCOPY VARCHAR2,
927 x_query_id OUT NOCOPY NUMBER ,
928 x_html OUT NOCOPY VARCHAR2 ,
929 x_return_status OUT NOCOPY VARCHAR2,
930 x_msg_count OUT NOCOPY NUMBER,
931 x_msg_data OUT NOCOPY VARCHAR2
932
933 )
934 IS
935 content_item_id NUMBER;
936 citem_name VARCHAR2(240);
937 citem_version NUMBER;
938 dir_node_id NUMBER;
939 dir_node_name VARCHAR2(240);
940 dir_node_code VARCHAR2(100);
941 item_status VARCHAR2(30);
942 version_status VARCHAR2(30);
943 version_number NUMBER;
944 citem_description VARCHAR2(32767);
945 ctype_code VARCHAR2(32767);
946 ctype_name VARCHAR2(32767);
947 start_date DATE;
948 end_date DATE;
949 owner_resource_id NUMBER;
950 owner_resource_type VARCHAR2(100);
951 reference_code VARCHAR2(100);
952 trans_required VARCHAR2(1);
953 parent_item_id NUMBER;
954 locked_by NUMBER;
955 wd_restricted VARCHAR2(32767);
956 attach_file_id NUMBER;
957 attach_file_name VARCHAR2(256);
958 object_version_number NUMBER;
959 created_by NUMBER;
960 creation_date DATE;
961 last_updated_by NUMBER;
962 last_update_date DATE;
963 attribute_type_codes JTF_VARCHAR2_TABLE_100 DEFAULT NULL;
964 attribute_type_names JTF_VARCHAR2_TABLE_300 DEFAULT NULL;
965 attributes JTF_VARCHAR2_TABLE_32767 DEFAULT NULL;
966 component_citems JTF_NUMBER_TABLE DEFAULT NULL;
967 component_attrib_types JTF_VARCHAR2_TABLE_100 DEFAULT NULL;
968 component_citem_names JTF_VARCHAR2_TABLE_300 DEFAULT NULL;
969 component_owner_ids JTF_NUMBER_TABLE DEFAULT NULL;
970 component_owner_types JTF_VARCHAR2_TABLE_100 DEFAULT NULL;
971 component_sort_orders JTF_NUMBER_TABLE DEFAULT NULL;
972 componenet_owner_types JTF_VARCHAR2_TABLE_100 DEFAULT NULL;
973
974 -- New GET ITEM Params
975
976 attach_file_ids NUMBER;
977
978 attach_mime_type VARCHAR2(32767) DEFAULT NULL;
979 attach_mime_name VARCHAR2(32767) DEFAULT NULL;
980 component_citem_ver_ids JTF_NUMBER_TABLE DEFAULT NULL;
981 -- End New Params
982 return_status VARCHAR2(1);
983 msg_count NUMBER;
984 msg_data VARCHAR2(32767);
985 x_item_version_id NUMBER;
986
987 counter NUMBER := 0;
988 att_count NUMBER;
989 comp_count NUMBER;
990 l_query_id NUMBER;
991
992 l_count_total NUMBER :=1;
993
994 x_rend_xml VARCHAR2(32767);
995
996
997 x_attach_file_name VARCHAR2(250) := '';
998 x_attach_file_id NUMBER;
999 a VARCHAR2(1) := '';
1000 query_flag VARCHAR2(1) := 'N';
1001 x_query_file_id NUMBER := 0;
1002 x_temp_file_id NUMBER;
1003 l_req_count NUMBER := 0;
1004 l_api_name CONSTANT varchar2(30) := 'GET_OCM_REND_DETAILS';
1005 l_full_name CONSTANT varchar2(2000) :=
1006 G_PKG_NAME || '.' || l_api_name;
1007 x_file_id NUMBER ;
1008 l_file_name VARCHAR2(256);
1009 html_file_id NUMBER ;
1010 text_file_id NUMBER ;
1011 pdf_file_id NUMBER;
1012 rtf_file_id NUMBER;
1013 x_attach_xml VARCHAR2(32767);
1014 x_delv_xml VARCHAR2(32767);
1015
1016 rend_count NUMBER := 0;
1017 l_doc_type VARCHAR2(100);
1018 l_file_type VARCHAR2(100);
1019 l_userEnvLang VARCHAR2(256) := USERENV('LANG');
1020 rendition_file_ids JTF_NUMBER_TABLE DEFAULT NULL;
1021 rendition_file_names JTF_VARCHAR2_TABLE_300 DEFAULT NULL;
1022 rendition_mime_types JTF_VARCHAR2_TABLE_100 DEFAULT NULL;
1023 rendition_mime_names JTF_VARCHAR2_TABLE_100 DEFAULT NULL;
1024 default_rendition NUMBER;
1025
1026 keywords JTF_VARCHAR2_TABLE_100 DEFAULT NULL;
1027
1028
1029 BEGIN
1030
1031 JTF_FM_UTL_V.PRINT_MESSAGE('BEGIN' || l_full_name,
1032 JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1033
1034 -- Initialize API return status to success
1035 x_return_status := FND_API.G_RET_STS_SUCCESS;
1036
1037
1038 -- Query to detemine if this is a test account as the rules for determining
1039 -- content item versions differ between test requests and regular requests
1040
1041 SELECT DISTINCT COUNT(REQUEST_ID) INTO l_req_count FROM JTF_FM_TEST_REQUESTS
1042 WHERE REQUEST_ID = p_request_id ;
1043
1044
1045 BEGIN
1046
1047 IF l_req_count > 0 THEN
1048
1049 JTF_FM_UTL_V.PRINT_MESSAGE('IT IS A TEST REQUEST: THE COUNT IS ' ||
1050 l_req_count, JTF_FM_UTL_V.G_LEVEL_PROCEDURE,
1051 'JTF_FM_REQUEST_GRP.GET_OCM_REND_DETAILS');
1052
1053 -- This is a test request, so the next thing to determine is if there
1054 -- was a version passed into the call. If so, we use it, othewise
1055 -- we need to determine what the version is so we can find the FND_LOBS
1056 -- file_id.
1057
1058 IF (p_version IS NOT NULL AND p_version <> FND_API.G_MISS_NUM) THEN
1059
1060 x_item_version_id := p_version;
1061
1062 ELSE
1063
1064 -- Comment via email from [email protected]
1065 -- *******************************************************************
1066 -- For a test request, I think the calling program should always
1067 -- pass the version number, it is possible that the user might be
1068 -- updating a version which is not the latest version, although we
1069 -- display only "Live Version" or "Latest version" in the coverletter
1070 -- summary UI, there could be a situation when two users are
1071 -- concurrently updating the same cover letter - one picks the live
1072 -- version to update and the other picks the latest version.
1073 --
1074 -- All and all this SQL would work in all cases except the one I
1075 -- outlined above.
1076 -- *******************************************************************
1077 -- The following gets the max version regardless of any status;
1078 -- item_status or version_status
1079
1080 SELECT MAX(CITEM_VER_ID) INTO x_item_version_id
1081 FROM IBC_CITEMS_V
1082 WHERE CITEM_ID = TO_NUMBER(p_content_id)
1083 AND LANGUAGE = USERENV('LANG') ;
1084
1085 END IF;
1086
1087 ELSE
1088
1089 -- This isn't a test request...
1090
1091 IF (p_version IS NOT NULL AND p_version <> FND_API.G_MISS_NUM) THEN
1092
1093 x_item_version_id := p_version;
1094
1095 ELSE
1096
1097 -- When no version is passed in, we should use the live version.
1098 -- **** Query provided and approved by OCM in bug 4398752 ****
1099 SELECT live_citem_version_id INTO x_item_version_id
1100 FROM ibc_content_items
1101 WHERE content_item_id = p_content_id;
1102
1103 END IF;
1104
1105 END IF;
1106
1107 EXCEPTION
1108 WHEN NO_DATA_FOUND THEN
1109 JTF_FM_UTL_V.PRINT_MESSAGE(
1110 'Content is either not present in OCM or is not approved' ||
1111 p_content_id, JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1112
1113 JTF_FM_UTL_V.HANDLE_ERROR('JTF_FM_OCM_NOTAPP_OR_ABS', p_content_id);
1114 RAISE FND_API.G_EXC_ERROR;
1115 END;
1116
1117
1118
1119 JTF_FM_UTL_V.PRINT_MESSAGE('Before calling IBC_CITEM_ADMIN_GRP.get_item',
1120 JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1121
1122
1123 BEGIN
1124
1125 IBC_CITEM_ADMIN_GRP.get_trans_item(
1126 x_item_version_id
1127 ,l_UserEnvLang
1128 ,FND_API.G_TRUE -- p_skip_security added as per bug # 3409965
1129 ,FND_API.G_TRUE
1130 ,IBC_CITEM_ADMIN_GRP.G_API_VERSION_DEFAULT
1131 ,content_item_id
1132 ,citem_name
1133 ,citem_version
1134 ,dir_node_id
1135 ,dir_node_name
1136 ,dir_node_code
1137 ,item_status
1138 ,version_status
1139 ,citem_description
1140 ,ctype_code
1141 ,ctype_name
1142 ,start_date
1143 ,end_date
1144 ,owner_resource_id
1145 ,owner_resource_type
1146 ,reference_code
1147 ,trans_required
1148 ,parent_item_id
1149 ,locked_by
1150 ,wd_restricted
1151 ,attach_file_id
1152 ,attach_file_name
1153 ,attach_mime_type
1154 ,attach_mime_name
1155 ,rendition_file_ids
1156 ,rendition_file_names
1157 ,rendition_mime_types
1158 ,rendition_mime_names
1159 ,default_rendition
1160 ,object_version_number
1161 ,created_by
1162 ,creation_date
1163 ,last_updated_by
1164 ,last_update_date
1165 ,attribute_type_codes
1166 ,attribute_type_names
1167 ,attributes
1168 ,component_citems
1169 ,component_citem_ver_ids
1170 ,component_attrib_types
1171 ,component_citem_names
1172 ,component_owner_ids
1173 ,componenet_owner_types
1174 ,component_sort_orders
1175 ,keywords
1176 ,return_status
1177 ,msg_count
1178 ,msg_data);
1179
1180 EXCEPTION
1181 WHEN OTHERS THEN
1182 JTF_FM_UTL_V.PRINT_MESSAGE('JTF_FM_EXCEPTION_IN_GET_ITEM name = '
1183 ||x_item_version_id, JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1184
1185 JTF_FM_UTL_V.HANDLE_ERROR('JTF_FM_EXCEPTION_IN_GET_ITEM',
1186 x_item_version_id);
1187
1188 RAISE FND_API.G_EXC_ERROR;
1189
1190 END;
1191
1192 if (return_status <> FND_API.G_RET_STS_SUCCESS) then
1193
1194 JTF_FM_UTL_V.PRINT_MESSAGE('JTF_FM_GET_TRANS_ITEM_FAIL name = '||
1195 p_content_id, JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1196 JTF_FM_UTL_V.HANDLE_ERROR('JTF_FM_GET_TRANS_ITEM_FAIL', p_content_id);
1197
1198 RAISE FND_API.G_EXC_ERROR;
1199
1200 else
1201
1202 JTF_FM_UTL_V.PRINT_MESSAGE('IN GET_OCM_REND_DETAILS name = '||citem_name,
1203 JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1204 JTF_FM_UTL_V.PRINT_MESSAGE('THE REQUEST ID IS name = '|| p_request_id,
1205 JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1206
1207 counter := 0;
1208
1209 -- Starting to process the returns from get_trans_item()
1210
1211 IF component_citems IS NOT NULL THEN
1212
1213 comp_count := component_citems.COUNT;
1214
1215 JTF_FM_UTL_V.PRINT_MESSAGE('com count '||
1216 comp_count, JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1217
1218 LOOP
1219
1220 EXIT WHEN comp_count = counter;
1221 counter := counter + 1;
1222
1223 JTF_FM_UTL_V.PRINT_MESSAGE('component citems = ' ||
1224 component_citems(counter),JTF_FM_UTL_V.G_LEVEL_PROCEDURE,
1225 l_full_name);
1226 JTF_FM_UTL_V.PRINT_MESSAGE('component_attrib_types = ' ||
1227 component_attrib_types(counter),JTF_FM_UTL_V.G_LEVEL_PROCEDURE,
1228 l_full_name);
1229 JTF_FM_UTL_V.PRINT_MESSAGE('component_citem_names = ' ||
1230 component_citem_names(counter),JTF_FM_UTL_V.G_LEVEL_PROCEDURE,
1231 l_full_name);
1232
1233 -- Check if this is a QUERY
1234
1235 IF component_attrib_types(counter) = 'QUERY' THEN
1236
1237 x_query_file_id :=
1238 GET_FILE_ID(
1239 TO_NUMBER(component_citem_ver_ids(counter)),
1240 component_citems(counter),
1241 p_request_id);
1242
1243 ELSIF component_attrib_types(counter) = 'ATTACHMENT' THEN
1244
1245 x_temp_file_id :=
1246 GET_FILE_ID(
1247 TO_NUMBER(component_citem_ver_ids(counter)),
1248 component_citems(counter),
1249 p_request_id);
1250
1251 -- For the attachment, need to determine whether RTF, PDF,
1252 -- HTML, or text
1253
1254 IF (JTF_FM_UTL_V.CONFIRM_RTF(x_temp_file_id)) THEN
1255
1256 l_doc_type := G_MIME_TBL(4);
1257 l_file_type := ' rtf_id ="';
1258
1259 ELSIF(JTF_FM_UTL_V.CONFIRM_PDF(x_temp_file_id)) THEN
1260
1261 l_doc_type := G_MIME_TBL(3);
1262 l_file_type := ' pdf_id ="';
1263
1264 ELSIF(JTF_FM_UTL_V.CONFIRM_TEXT_HTML(x_temp_file_id)) THEN
1265
1266 l_doc_type := G_MIME_TBL(2);
1267 l_file_type := ' id ="';
1268
1269 ELSE
1270
1271 l_doc_type := G_MIME_TBL(1);
1272 l_file_type := ' id = "';
1273
1274 END IF;
1275
1276 l_count_total := l_count_total +1;
1277
1278 -- Now build the XML string for this component
1279
1280 x_attach_xml := x_attach_xml ||
1281 '<file ' || l_file_type || x_temp_file_id || '" ' || a;
1282 x_attach_xml := x_attach_xml ||
1283 ' body = "no" ' || a;
1284 x_attach_xml := x_attach_xml ||
1285 ' content_no = "' || l_count_total || '" ' ||a;
1286 x_attach_xml := x_attach_xml || '></file>';
1287
1288
1289 --SPLIT_LINE(x_attach_xml,80);
1290
1291 l_file_name := GET_FILE_NAME(x_temp_file_id);
1292
1293 INSERT_REQUEST_CONTENTS(
1294 p_request_id,
1295 p_content_id,
1296 l_count_total,
1297 l_file_name,
1298 'ATTACHMENT',
1299 l_doc_type,
1300 'N',
1301 p_user_note,
1302 p_quantity,
1303 p_media_type,
1304 'ocm' ,
1305 x_temp_file_id);
1306
1307 END IF;
1308
1309 JTF_FM_UTL_V.PRINT_MESSAGE('x_html :' || x_html,
1310 JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1311 JTF_FM_UTL_V.PRINT_MESSAGE('----------------------------------------',
1312 JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1313 END LOOP;
1314
1315 END IF;
1316
1317 GET_AND_INSERT_REQUEST_DETAILS(
1318 p_request_id,
1319 p_content_id,
1320 p_user_note,
1321 p_quantity,
1322 p_media_type,
1323 x_query_file_id,
1324 p_email_format,
1325 p_version,
1326 p_content_nm,
1327 rendition_file_names,
1328 rendition_mime_names,
1329 rendition_mime_types,
1330 rendition_file_ids ,
1331 x_rend_xml,
1332 return_status,
1333 msg_count,
1334 msg_data);
1335
1336 IF (return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1337 RAISE FND_API.G_EXC_ERROR;
1338 END IF;
1339
1340
1341 --Now add attachment xml and delv xml to this
1342
1343 x_html := x_rend_xml || x_attach_xml ;
1344 x_html := x_html || '</files>' || a;
1345 --SPLIT_LINE(x_html,80);
1346 end if;
1347
1348 DELETE FROM JTF_FM_TEST_REQUESTS WHERE REQUEST_ID = p_request_id;
1349
1350 JTF_FM_UTL_V.PRINT_MESSAGE('End GET_OCM_REND_DETAILS',
1351 JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1352
1353 EXCEPTION
1354 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1355
1356 x_citem_name := NULL;
1357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1358
1359 -- Standard call to get message count and if count=1, get the message
1360 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.g_false,
1361 p_count => x_msg_count,
1362 p_data => x_msg_data
1363 );
1364 JTF_FM_UTL_V.GET_ERROR_MESSAGE(x_msg_data);
1365
1366 WHEN FND_API.G_EXC_ERROR THEN
1367
1368 x_citem_name := NULL;
1369 x_return_status := FND_API.G_RET_STS_ERROR;
1370
1371 -- Standard call to get message count and if count=1, get the message
1372 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.g_false,
1373 p_count => x_msg_count,
1374 p_data => x_msg_data
1375 );
1376 JTF_FM_UTL_V.GET_ERROR_MESSAGE(x_msg_data);
1377
1378 WHEN OTHERS THEN
1379
1380 x_citem_name := NULL;
1381 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1382 JTF_FM_UTL_V.ADD_ERROR_MESSAGE (l_api_name, SQLERRM);
1383
1384 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1385 THEN
1386 FND_MSG_PUB.Add_Exc_Msg('JTF_FM_OCM_REND_REQ', G_PKG_NAME );
1387 END IF; -- IF FND_MSG_PUB.Check_Msg_Level
1388
1389 -- Standard call to get message count and if count=1, get the message
1390 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.g_false,
1391 p_count => x_msg_count,
1392 p_data => x_msg_data
1393 );
1394 JTF_FM_UTL_V.GET_ERROR_MESSAGE(x_msg_data);
1395
1396 for i in 0..x_msg_count loop
1397
1398 JTF_FM_UTL_V.PRINT_MESSAGE(FND_MSG_PUB.get(i,FND_API.G_FALSE),
1399 JTF_FM_UTL_V.G_LEVEL_PROCEDURE,
1400 'JTF_FM_REQUEST_GRP.GET_OCM_REND_DETAILS');
1401
1402 end loop;
1403
1404 END GET_OCM_REND_DETAILS;
1405
1406
1407
1408 PROCEDURE GET_RENDITION_XML
1409 (
1410 p_api_version IN NUMBER,
1411 p_init_msg_list IN VARCHAR2 ,
1412 p_commit IN VARCHAR2 ,
1413 p_validation_level IN NUMBER ,
1414 x_return_status OUT NOCOPY VARCHAR2,
1415 x_msg_count OUT NOCOPY NUMBER,
1416 x_msg_data OUT NOCOPY VARCHAR2,
1417 p_content_id IN NUMBER,
1418 p_content_nm IN VARCHAR2 ,
1419 p_quantity IN NUMBER ,
1420 p_media_type IN VARCHAR2,
1421 p_printer IN VARCHAR2 ,
1422 p_email IN VARCHAR2 ,
1423 p_fax IN VARCHAR2 ,
1424 p_bind_var IN JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ,
1425 p_bind_val IN JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ,
1426 p_bind_var_type IN JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE ,
1427 p_request_id IN NUMBER,
1428 x_content_xml OUT NOCOPY VARCHAR2,
1429 p_version IN NUMBER,
1430 p_email_format IN VARCHAR2
1431 ) IS
1432 l_api_name CONSTANT VARCHAR2(30) := 'GET_REND_XML';
1433 l_api_version CONSTANT NUMBER := 1.0;
1434 l_full_name CONSTANT VARCHAR2(2000) := G_PKG_NAME ||'.'|| l_api_name;
1435 --
1436 l_user_id NUMBER := -1;
1437 l_login_user_id NUMBER := -1;
1438 l_login_user_status NUMBER;
1439 l_Error_Msg VARCHAR2(2000);
1440 --
1441 l_message VARCHAR2(32767) := '';
1442 l_temp NUMBER := 0;
1443 l_count NUMBER := 0;
1444 l_destination VARCHAR2(200) := NULL;
1445 l_content_nm VARCHAR2(200);
1446 l_meaning VARCHAR2(200);
1447 l_query_id NUMBER;
1448 l_media VARCHAR2(30);
1449 --
1450 b VARCHAR2(1);
1451 c VARCHAR2(1);
1452 a VARCHAR2(2);
1453
1454
1455 x_citem_name VARCHAR2(250);
1456 x_html VARCHAR2(2000);
1457 x_query_id NUMBER;
1458 p_user_note VARCHAR2(30) := 'USER_NOTE';
1459 --
1460 -- Moved all cursors to JTF_FM_UTILITY PACKAGE
1461
1462 BEGIN
1463
1464 JTF_FM_UTL_V.PRINT_MESSAGE('BEGIN' || l_full_name, JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1465
1466 -- Standard begin of API savepoint
1467 SAVEPOINT Content_XML;
1468
1469 -- Select end-of-line character from dual
1470 /* select chr(13) cr, chr(10) lf into b, c from dual;
1471 a:= b||c; */
1472 a := '';
1473
1474 -- Check for API version compatibility
1475 IF NOT FND_API.Compatible_API_Call (l_api_version,
1476 p_api_version,
1477 l_api_name,
1478 G_PKG_NAME)
1479 THEN
1480 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1481 END IF; -- NOT FND_API.Compatible_API_Call
1482
1483 --Initialize message list if p_init_msg_list is TRUE.
1484 IF FND_API.To_Boolean (p_init_msg_list)
1485 THEN
1486 FND_MSG_PUB.initialize;
1487 END IF; -- FND_API.To_Boolean
1488
1489 -- Debug Message
1490 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1491 THEN
1492 FND_MESSAGE.Set_Name('JTF','JTF_FM_API_DEBUG_MESSAGE');
1493 FND_MESSAGE.Set_Token('ARG1',l_full_name||': Start');
1494 FND_MSG_PUB.Add;
1495 END IF; -- FND_MSG_PUB.Check_Msg_level
1496
1497 -- Initialize API return status to success
1498 x_return_status := FND_API.G_RET_STS_SUCCESS;
1499
1500 -- Check if Content_id parameter is NULL. Content_id represents the
1501 -- unique identifier for getting the document from MES tables
1502 IF (p_content_id IS NULL)
1503 THEN
1504 l_Error_Msg := 'Must pass p_content_id parameter';
1505 JTF_FM_UTL_V.PRINT_MESSAGE('Must pass p_content_id parameter ' ,JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1506
1507
1508 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1509 THEN
1510 FND_MESSAGE.set_name('JTF', 'JTF_FM_API_MISSING_CONTENT_ID');
1511 FND_MSG_PUB.Add;
1512 END IF; -- IF FND_MSG_PUB.check_msg_level
1513
1514 RAISE FND_API.G_EXC_ERROR;
1515
1516 -- check if the media_type paramater is NULL. No point in processing a
1517 -- request without a media_type
1518 ELSIF (p_media_type IS NULL) -- IF (p_media_type IS NULL)
1519 THEN
1520 l_Error_Msg := 'Must pass p_media_type parameters';
1521 JTF_FM_UTL_V.PRINT_MESSAGE('Must pass p_media_type parameter ' ,JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1522
1523 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1524 FND_MESSAGE.set_name('JTF', 'JTF_FM_API_MISSING_MEDIA_TYPE');
1525 FND_MSG_PUB.Add;
1526 END IF; -- IF FND_MSG_PUB.check_msg_level
1527
1528 RAISE FND_API.G_EXC_ERROR;
1529 -- Must pass a request_type
1530
1531 ELSIF (p_request_id IS NULL) -- IF (p_request_id IS NULL)
1532 THEN
1533 l_Error_Msg := 'Must pass p_request_id parameters';
1534 JTF_FM_UTL_V.PRINT_MESSAGE('Must pass p_request_id parameter ' ,JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1535
1536 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1537 FND_MESSAGE.set_name('JTF', 'JTF_FM_API_MISSING_REQUEST_ID');
1538 FND_MSG_PUB.Add;
1539 END IF; -- IF _FND_MSG_PUB.check_msg_level
1540
1541 RAISE FND_API.G_EXC_ERROR;
1542 ELSE -- IF (p_content_id IS NULL)
1543
1544 -- Start forming the XML Request for the content
1545
1546 l_message := '<item>'||a;
1547
1548 l_message := l_message||' <media_type>'||a;
1549
1550 -- Identify the media types requested
1551 IF (INSTR(p_media_type, 'PRINTER') > 0)
1552 THEN
1553 IF p_printer = FND_API.G_MISS_CHAR
1554 THEN
1555 l_message := l_message||'<printer>'||null||'</printer> '||a;
1556 ELSE -- IF p_printer
1557 l_message := l_message||'<printer>'||p_printer||'</printer> '||a;
1558 END IF; -- IF p_printer
1559
1560 l_destination := l_destination ||', '|| p_printer;
1561 l_temp := l_temp + 1;
1562 END IF; -- IF (INSTR(p_media_type,
1563
1564 IF (INSTR(p_media_type, 'EMAIL') > 0)
1565 THEN
1566 IF p_email = FND_API.G_MISS_CHAR
1567 THEN
1568 l_message := l_message||'<email>'||null||'</email> '||a;
1569 ELSE -- IF p_email
1570 l_message := l_message||'<email>'||p_email||'</email> '||a;
1571 END IF; -- IF p_email
1572
1573 l_destination := l_destination ||', '|| p_email;
1574 l_temp := l_temp + 1;
1575 END IF; -- IF (INSTR(p_media_type
1576
1577 IF (INSTR(p_media_type, 'FAX') > 0)
1578 THEN
1579 IF p_fax = FND_API.G_MISS_CHAR
1580 THEN
1581 l_message := l_message||'<fax>'||null||'</fax> '||a;
1582 ELSE -- IF p_fax
1583 l_message := l_message||'<fax>'||p_fax||'</fax> '||a;
1584 END IF; -- IF p_fax
1585
1586 l_destination := l_destination ||', '|| p_fax;
1587 l_temp := l_temp + 1;
1588 END IF; -- IF (INSTR(p_media_type
1589
1590 -- Check if atleast one valid media type has been specified
1591 IF (l_temp = 0)
1592 THEN
1593 l_Error_Msg := 'Invalid media type specified. Allowed media_types are EMAIL, FAX, PRINTER';
1594 JTF_FM_UTL_V.PRINT_MESSAGE('Invalid media type specified. Allowed media_types are EMAIL, FAX, PRINTER' ,JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1595
1596 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1597 THEN
1598 FND_MESSAGE.set_name('JTF', 'JTF_FM_API_INVALID_MEDIATYPE');
1599 FND_MSG_PUB.Add;
1600 END IF; -- IF FND_MSG_PUB.check_msg_level
1601
1602 RAISE FND_API.G_EXC_ERROR;
1603
1604 END IF; -- IF (l_temp = 0)
1605
1606 l_message := l_message||'</media_type> '||a;
1607
1608 -- New XML code added by sxkrishn 10-25-02
1609 JTF_FM_UTL_V.PRINT_MESSAGE('Right after media has been formed' ,JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1610
1611
1612
1613
1614
1615
1616 l_message := l_message||'<item_content id="'|| p_content_id || '" '||a;
1617
1618 l_message := l_message||' quantity="'||to_char(p_quantity)||'" user_note="User_Note" source ="ocm" '||a;
1619 IF p_version <> FND_API.G_MISS_NUM THEN
1620 l_message := l_message||' version_id="'||p_version ||'"'||a;
1621 END IF;
1622 l_message := l_message||'>' || a;
1623
1624
1625 l_media := JTF_FM_UTL_V.GET_MEDIA(l_message);
1626 --dbms_output.PUT_LINE('media type is :' || l_media);
1627
1628
1629 -- Assuming that this call will be made only for OCM contents
1630 GET_OCM_REND_DETAILS(p_content_id,
1631 p_request_id,
1632 p_user_note,
1633 p_quantity,
1634 l_media,
1635 p_version,
1636 p_content_nm,
1637 p_email_format,
1638 x_citem_name ,
1639 x_query_id,
1640 x_html,
1641 x_return_status ,
1642 x_msg_count ,
1643 x_msg_data
1644 );
1645 IF(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1646 JTF_FM_UTL_V.PRINT_MESSAGE('Item present in OCM Repository' ,JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1647
1648 l_message := l_message|| x_html;
1649 ELSIF(x_return_status = FND_API.G_RET_STS_ERROR) THEN
1650 RAISE FND_API.G_EXC_ERROR;
1651 ELSE
1652 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1653
1654 JTF_FM_UTL_V.PRINT_MESSAGE('Item NOT present in OCM Repository',JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1655 END IF;
1656
1657
1658
1659 IF (p_bind_var.count <> 0) THEN
1660 l_message := l_message||'<bind> '||a;
1661 l_message := l_message||'<record> '||a;
1662 FOR i IN 1..p_bind_var.count LOOP
1663
1664 l_message := l_message||'<bind_var bind_type="'
1665 ||JTF_FM_REQUEST_GRP.REPLACE_TAG(p_bind_var_type(i));
1666 l_message := l_message||'" bind_object="'
1667 ||JTF_FM_REQUEST_GRP.REPLACE_TAG(p_bind_var(i))||'" > '
1668 ||JTF_FM_REQUEST_GRP.REPLACE_TAG(p_bind_val(i))||'</bind_var>'||a;
1669
1670 END LOOP; -- FOR i IN
1671 l_message := l_message||'</record> '||a;
1672 l_message := l_message||'</bind> '||a;
1673 END IF; -- IF (p_bind_var.count
1674
1675 l_message := l_message||'</item_content> '||a;
1676 l_message := l_message||'</item> '||a;
1677
1678 --dbms_output.put_line('created the XML');
1679 -- End of the XML Request
1680
1681 --SPLIT_LINE(l_message,80);
1682
1683 x_content_xml := l_message;
1684
1685 END IF; -- IF (p_content_id IS NULL)
1686
1687 -- Success message
1688 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS) THEN
1689 FND_MESSAGE.Set_Name('JTF', 'JTF_FM_API_SUCCESS_MESSAGE');
1690 FND_MESSAGE.Set_Token('ARG1', l_full_name);
1691 FND_MSG_PUB.Add;
1692 END IF; -- IF FND_MSG_PUB.Check_Msg_Level
1693
1694 --Standard check of commit
1695
1696 IF FND_API.To_Boolean ( p_commit ) THEN
1697 COMMIT WORK;
1698 END IF; -- IF FND_API.To_Boolean
1699
1700 -- Debug Message
1701 IF FND_MSG_PUB.Check_Msg_level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW) THEN
1702 FND_MESSAGE.Set_Name('FFM','JTF_FM_API_DEBUG_MESSAGE');
1703 FND_MESSAGE.Set_Token('ROW',l_full_name||': End');
1704 FND_MSG_PUB.Add;
1705 END IF; -- IF FND_MSG_PUB.Check_Msg_level
1706
1707 --Standard call to get message count and if count=1, get the message
1708 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.g_false,
1709 p_count => x_msg_count,
1710 p_data => x_msg_data
1711 );
1712
1713 EXCEPTION
1714 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1715 ROLLBACK TO Content_XML;
1716 x_content_xml := NULL;
1717 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1718 JTF_FM_UTL_V.ADD_ERROR_MESSAGE (l_api_name, l_Error_Msg);
1719 -- Standard call to get message count and if count=1, get the message
1720 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.g_false,
1721 p_count => x_msg_count,
1722 p_data => x_msg_data
1723 );
1724 JTF_FM_UTL_V.GET_ERROR_MESSAGE(x_msg_data);
1725
1726 WHEN FND_API.G_EXC_ERROR THEN
1727 ROLLBACK TO Content_XML;
1728 x_content_xml := NULL;
1729 x_return_status := FND_API.G_RET_STS_ERROR;
1730 JTF_FM_UTL_V.ADD_ERROR_MESSAGE (l_api_name, l_Error_Msg);
1731 -- Standard call to get message count and if count=1, get the message
1732 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.g_false,
1733 p_count => x_msg_count,
1734 p_data => x_msg_data
1735 );
1736 JTF_FM_UTL_V.GET_ERROR_MESSAGE(x_msg_data);
1737
1738 WHEN OTHERS THEN
1739 ROLLBACK TO Content_XML;
1740 x_content_xml := NULL;
1741 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1742 JTF_FM_UTL_V.ADD_ERROR_MESSAGE (l_api_name, SQLERRM);
1743
1744 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1745 FND_MSG_PUB.Add_Exc_Msg
1746 (G_PKG_NAME, l_api_name);
1747 END IF; -- IF FND_MSG_PUB.Check_Msg_Level
1748
1749 -- Standard call to get message count and if count=1, get the message
1750 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.g_false,
1751 p_count => x_msg_count,
1752 p_data => x_msg_data
1753 );
1754 JTF_FM_UTL_V.GET_ERROR_MESSAGE(x_msg_data);
1755
1756 JTF_FM_UTL_V.PRINT_MESSAGE('END ' || l_full_name , JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1757
1758 END Get_RENDITION_XML;
1759
1760
1761
1762
1763
1764
1765 PROCEDURE create_fulfillment_rendition
1766 (
1767 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1768 p_api_version IN NUMBER,
1769 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1770 p_order_header_rec IN JTF_Fulfillment_PUB.ORDER_HEADER_REC_TYPE,
1771 p_order_line_tbl IN JTF_Fulfillment_PUB.ORDER_LINE_TBL_TYPE,
1772 p_fulfill_electronic_rec IN JTF_FM_OCM_REQUEST_GRP.FULFILL_ELECTRONIC_REC_TYPE,
1773 p_request_type IN VARCHAR2,
1774 x_return_status OUT NOCOPY VARCHAR2,
1775 x_msg_count OUT NOCOPY NUMBER,
1776 x_msg_data OUT NOCOPY VARCHAR2,
1777 x_order_header_rec OUT NOCOPY ASO_ORDER_INT.order_header_rec_type,
1778 x_request_history_id OUT NOCOPY NUMBER
1779 )
1780 IS
1781
1782 l_api_name CONSTANT VARCHAR2(30) := 'create_fulfillment_rendition';
1783 l_full_name CONSTANT VARCHAR2(100) := G_PKG_NAME ||'.'|| l_api_name;
1784 l_api_version CONSTANT NUMBER := 1.0;
1785 l_init_msg_list VARCHAR2(2) := FND_API.G_FALSE;
1786 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
1787 l_content_xml VARCHAR2(32767);
1788 l_content_xml1 VARCHAR2(32767);
1789 l_bind_var JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
1790 l_bind_val JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
1791 l_bind_var_type JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
1792 l_content_id NUMBER;
1793 l_per_user_history VARCHAR2(2);
1794 l_subject VARCHAR2(255);
1795 l_quantity NUMBER := 1;
1796 l_return_status VARCHAR2(200);
1797 l_request_id NUMBER;
1798 l_request_history_id NUMBER;
1799 l_msg_data VARCHAR2(1000);
1800 l_Error_Msg VARCHAR2(1000);
1801 l_msg_count NUMBER;
1802 l_commit VARCHAR2(2) := FND_API.G_FALSE;
1803 l_total NUMBER;
1804 l_var_media_type VARCHAR2(30);
1805 l_printer_val VARCHAR2(250) := null;
1806 l_fax_val VARCHAR2(250):= null;
1807 l_email_val VARCHAR2(250):= null;
1808 l_extended_header VARCHAR2(32767) ;
1809 l_message VARCHAR2(32767);
1810 l_content_nm VARCHAR2(1) := null;
1811 x_test_xml VARCHAR2(32767);
1812
1813 l_fm_pvt_rec JTF_FM_UTL_V.FM_PVT_REC_TYPE;
1814
1815 BEGIN
1816 --dbms_output.put_line('In create Fulfillment API');
1817
1818 JTF_FM_UTL_V.PRINT_MESSAGE('BEGIN' || l_full_name,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1819 -- Standard Start of API savepoint
1820 SAVEPOINT create_fulfillment;
1821 -- Standard call to check for call compatibility.
1822 IF NOT FND_API.Compatible_API_Call
1823 (
1824 l_api_version,
1825 p_api_version,
1826 l_api_name, G_PKG_NAME )
1827 THEN
1828 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1829 END IF;
1830
1831 -- Initialize message list if p_init_msg_list is set to TRUE.
1832 IF FND_API.to_Boolean( p_init_msg_list )
1833 THEN
1834 FND_MSG_PUB.initialize;
1835 END IF;
1836
1837 -- If loggin is set at the highest level, record the in params to the API
1838 JTF_FM_UTL_V.PRINT_MESSAGE('In params-Create_fulfillment Rendition' || l_full_name,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1839 JTF_FM_UTL_V.PRINT_MESSAGE('template_id: ' || p_fulfill_electronic_rec.template_id,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1840 JTF_FM_UTL_V.PRINT_MESSAGE('version_id: ' || p_fulfill_electronic_rec.version_id,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1841 JTF_FM_UTL_V.PRINT_MESSAGE('object_type: ' || p_fulfill_electronic_rec.object_type,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1842 JTF_FM_UTL_V.PRINT_MESSAGE('object_id: ' || p_fulfill_electronic_rec.object_id,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1843 JTF_FM_UTL_V.PRINT_MESSAGE('source_code: ' || p_fulfill_electronic_rec.source_code,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1844 JTF_FM_UTL_V.PRINT_MESSAGE('source_code_id: ' || p_fulfill_electronic_rec.source_code_id,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1845 JTF_FM_UTL_V.PRINT_MESSAGE('requestor_type: ' || p_fulfill_electronic_rec.requestor_type,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1846 JTF_FM_UTL_V.PRINT_MESSAGE('requestor_id: ' || p_fulfill_electronic_rec.requestor_id,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1847 JTF_FM_UTL_V.PRINT_MESSAGE('server_group: ' || p_fulfill_electronic_rec.server_group,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1848 JTF_FM_UTL_V.PRINT_MESSAGE('schedule_date: ' || p_fulfill_electronic_rec.schedule_date,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1849 JTF_FM_UTL_V.PRINT_MESSAGE('media_types: ' || p_fulfill_electronic_rec.media_types,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1850 JTF_FM_UTL_V.PRINT_MESSAGE('archive: ' || p_fulfill_electronic_rec.archive,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1851 JTF_FM_UTL_V.PRINT_MESSAGE('log_user_ih: ' || p_fulfill_electronic_rec.log_user_ih,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1852 JTF_FM_UTL_V.PRINT_MESSAGE('request_type: ' || p_fulfill_electronic_rec.request_type,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1853 JTF_FM_UTL_V.PRINT_MESSAGE('language_code: ' || p_fulfill_electronic_rec.language_code,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1854 JTF_FM_UTL_V.PRINT_MESSAGE('profile_id: ' || p_fulfill_electronic_rec.profile_id,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1855 JTF_FM_UTL_V.PRINT_MESSAGE('order_id: ' || p_fulfill_electronic_rec.order_id,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1856 JTF_FM_UTL_V.PRINT_MESSAGE('collateral_id: ' || p_fulfill_electronic_rec.collateral_id,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1857 JTF_FM_UTL_V.PRINT_MESSAGE('subject: ' || p_fulfill_electronic_rec.subject,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1858
1859 -- Following have been commented out as they may exceed the limits set:
1860 --JTF_FM_UTL_V.PRINT_MESSAGE('party_id: ' || p_fulfill_electronic_rec.party_id,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1861 --JTF_FM_UTL_V.PRINT_MESSAGE('email: ' || p_fulfill_electronic_rec.email,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1862 --JTF_FM_UTL_V.PRINT_MESSAGE('fax: ' || p_fulfill_electronic_rec.fax,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1863 --JTF_FM_UTL_V.PRINT_MESSAGE('printer: ' || p_fulfill_electronic_rec.printer,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1864 --JTF_FM_UTL_V.PRINT_MESSAGE('bind_values: ' || p_fulfill_electronic_rec.bind_values,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1865 --JTF_FM_UTL_V.PRINT_MESSAGE('bind_names: ' || p_fulfill_electronic_rec.bind_names,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1866 --JTF_FM_UTL_V.PRINT_MESSAGE('extended_header: ' || p_fulfill_electronic_rec.extended_header,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1867
1868 JTF_FM_UTL_V.PRINT_MESSAGE('email_text: ' || p_fulfill_electronic_rec.email_text,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1869 JTF_FM_UTL_V.PRINT_MESSAGE('content_name: ' || p_fulfill_electronic_rec.content_name,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1870 JTF_FM_UTL_V.PRINT_MESSAGE('content_type: ' || p_fulfill_electronic_rec.content_type,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1871 JTF_FM_UTL_V.PRINT_MESSAGE('stop_list_bypass: ' || p_fulfill_electronic_rec.stop_list_bypass,JTF_FM_UTL_V.G_LEVEL_PROCEDURE,l_full_name);
1872
1873
1874
1875
1876 -- Initialize API return status to success
1877 -- API body
1878 IF (upper(p_fulfill_electronic_rec.request_type) = 'P') THEN
1879 -- call physical fulfillment
1880 JTF_Fulfillment_PUB.create_fulfill_physical
1881 (p_init_msg_list => p_init_msg_list,
1882 p_api_version => p_api_version,
1883 p_commit => p_commit,
1884 x_return_status => x_return_status,
1885 x_msg_count => x_msg_count,
1886 x_msg_data => x_msg_data,
1887 p_order_header_rec => p_order_header_rec,
1888 p_order_line_tbl => p_order_line_tbl,
1889 x_order_header_rec => x_order_header_rec,
1890 x_request_history_id => x_request_history_id
1891 );
1892 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1893 THEN
1894 RAISE FND_API.G_EXC_ERROR;
1895 END IF;
1896 ELSIF (IS_REQ_ETSL(p_fulfill_electronic_rec.request_type)) THEN
1897
1898
1899 x_return_status := FND_API.G_RET_STS_SUCCESS;
1900 l_bind_var := JTF_FM_REQUEST_GRP.L_VARCHAR_TBL;
1901 l_bind_val := JTF_FM_REQUEST_GRP.L_VARCHAR_TBL;
1902 l_bind_var_type := JTF_FM_REQUEST_GRP.L_VARCHAR_TBL;
1903
1904 IF LENGTH(p_fulfill_electronic_rec.media_types) >3 THEN
1905 l_Error_Msg := null;
1906 l_Error_Msg := 'Invalid media type specified. Only allowed values are ';
1907 l_Error_Msg := l_ERROR_Msg || 'EPF,EFP,FEP,FPE,PEF,PFE,EP,EF,E,PE,PF,P,FE,FP,F';
1908 JTF_FM_UTL_V.PRINT_MESSAGE(l_Error_msg, JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1909 JTF_FM_UTL_V.HANDLE_ERROR('JTF_FM_API_CF_INVALID_MEDIA');
1910 RAISE FND_API.G_EXC_ERROR;
1911
1912 ELSE
1913 IF (IS_MED_EPF(p_fulfill_electronic_rec.media_types)) THEN
1914
1915 IF ( INSTR(upper(p_fulfill_electronic_rec.media_types),'P') > 0) THEN
1916
1917 l_var_media_type := 'PRINTER,';
1918
1919 IF p_fulfill_electronic_rec.printer.EXISTS(1) THEN
1920 l_printer_val := p_fulfill_electronic_rec.printer(1);
1921 ELSE
1922 IF p_fulfill_electronic_rec.request_type = 'S' THEN
1923 l_Error_Msg := 'Chosen Media is Print but missing print address';
1924 JTF_FM_UTL_V.PRINT_MESSAGE(l_Error_msg, JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1925 JTF_FM_UTL_V.HANDLE_ERROR('JTF_FM_API_MISSING_PRINT_ADDR');
1926 RAISE FND_API.G_EXC_ERROR;
1927 ELSE--for mass requests
1928 l_printer_val := 'Query';
1929 END IF;
1930 END IF;
1931
1932 END IF;
1933
1934 IF (INSTR(upper(p_fulfill_electronic_rec.media_types), 'F' ) > 0) THEN
1935 l_var_media_type := l_var_media_type || 'FAX,';
1936 IF p_fulfill_electronic_rec.fax.EXISTS(1) THEN
1937 l_fax_val := p_fulfill_electronic_rec.fax(1);
1938 ELSE
1939 IF p_fulfill_electronic_rec.request_type = 'S' THEN
1940 l_Error_Msg := 'Chosen Media is FAX but missing FAX address';
1941 JTF_FM_UTL_V.PRINT_MESSAGE(l_Error_msg, JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1942 JTF_FM_UTL_V.HANDLE_ERROR('JTF_FM_API_MISSING_FAX_ADDR');
1943 RAISE FND_API.G_EXC_ERROR;
1944 ELSE -- no need of fax address for mass requests
1945 l_fax_val := 'Query';
1946 END IF;
1947 END IF;
1948
1949 END IF;
1950
1951 IF((INSTR(upper(p_fulfill_electronic_rec.media_types),'E')>0)
1952 OR p_fulfill_electronic_rec.media_types IS NULL
1953 OR p_fulfill_electronic_rec.media_types= FND_API.G_MISS_CHAR) THEN
1954 l_var_media_type := l_var_media_type ||'EMAIL';
1955 IF p_fulfill_electronic_rec.email.EXISTS(1) THEN
1956 l_email_val := p_fulfill_electronic_rec.email(1);
1957 ELSE
1958 IF p_fulfill_electronic_rec.request_type = 'S' THEN
1959
1960 l_Error_Msg := 'Chosen Media is Email but missing email address';
1961 JTF_FM_UTL_V.PRINT_MESSAGE(l_Error_msg, JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1962 JTF_FM_UTL_V.HANDLE_ERROR('JTF_FM_API_MISSING_EMAIL_ADDR');
1963 RAISE FND_API.G_EXC_ERROR;
1964 ELSE
1965 l_email_val := 'Query';
1966 END IF;
1967 END IF;
1968
1969
1970 END IF;
1971 ELSE -- Means media is not E or P or F
1972 l_Error_Msg := null;
1973 l_Error_Msg := 'Invalid media type specified. Only allowed values are ';
1974 l_Error_Msg := l_ERROR_Msg || 'EPF,EFP,FEP,FPE,PEF,PFE,EP,EF,E,PE,PF,P,FE,FP,F';
1975 JTF_FM_UTL_V.PRINT_MESSAGE(l_Error_msg, JTF_FM_UTL_V.G_LEVEL_PROCEDURE, l_full_name);
1976 JTF_FM_UTL_V.HANDLE_ERROR('JTF_FM_API_CF_INVALID_MEDIA');
1977 RAISE FND_API.G_EXC_ERROR;
1978
1979
1980 END IF;
1981
1982 END IF ;-- End IF(p_fulfill_electronic_rec.media_types.LENGTH >3) THEN
1983
1984
1985
1986 IF(p_fulfill_electronic_rec.extended_header IS NULL) THEN
1987 JTF_FM_UTL_V.PRINT_MESSAGE('Extended header is null',JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
1988 l_extended_header := FND_API.G_MISS_CHAR;
1989 ELSE
1990 l_extended_header := p_fulfill_electronic_rec.extended_header;
1991 END IF;
1992
1993 /**
1994 internally three apis are called
1995 start request is called first
1996 **/
1997
1998 JTF_FM_REQUEST_GRP.start_request
1999 (
2000 p_api_version => l_api_version,
2001 p_init_msg_list => l_init_msg_list,
2002 x_return_status => x_return_status,
2003 x_msg_count => l_msg_count,
2004 x_msg_data => l_msg_data,
2005 x_request_id => x_request_history_id
2006 );
2007 JTF_FM_UTL_V.PRINT_MESSAGE('Start_Request Return Status is ' || x_return_status,
2008 JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
2009
2010 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2011 RAISE FND_API.G_EXC_ERROR;
2012 END IF;
2013
2014 JTF_FM_UTL_V.PRINT_MESSAGE('Inside ocm pkg request id is ' || to_char(x_request_history_id),
2015 JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
2016 l_total := p_fulfill_electronic_rec.bind_names.count;
2017 FOR i IN 1..l_total LOOP
2018 l_bind_var(i) := p_fulfill_electronic_rec.bind_names(i);
2019 l_bind_val(i) := p_fulfill_electronic_rec.bind_values(i);
2020 l_bind_var_type(i) := 'VARCHAR2';
2021 END LOOP;
2022
2023 l_content_id := p_fulfill_electronic_rec.template_id;
2024 JTF_FM_UTL_V.PRINT_MESSAGE('Inside ocm pkg l_content_id is ' || to_char(l_content_id),
2025 JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
2026
2027 /**
2028 just before calling send request we will insert a record into
2029 history table. Reason. GET_FILE_ID should
2030 know about REQUEST_TYPE 'T'
2031 **/
2032 IF (upper(p_fulfill_electronic_rec.request_type) = 'T') THEN
2033 JTF_FM_UTL_V.PRINT_MESSAGE('THE REQUEST TYPE IS TEST',JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
2034 INSERT INTO JTF_FM_TEST_REQUESTS (REQUEST_ID) VALUES (x_request_history_id);
2035 END IF;
2036
2037 /********************************************************
2038 Following modifications were made for Label but
2039 the design is not finalized yet 8-27-03. .
2040 This is subject to change
2041
2042
2043 *********************************************************/
2044 IF(upper(p_fulfill_electronic_rec.request_type) = 'L') THEN
2045 l_content_nm := 'L';
2046 END IF;
2047
2048 /**
2049 get content xml is called after calling start request
2050 this prepares the content related xml
2051
2052 for single request
2053
2054 ***********/
2055 GET_RENDITION_XML(
2056 l_api_version,
2057 l_init_msg_list ,
2058 l_commit ,
2059 l_validation_level ,
2060 l_return_status,
2061 l_msg_count,
2062 l_msg_data,
2063 l_content_id,
2064 l_content_nm,
2065 l_quantity,
2066 l_var_media_type,
2067 l_printer_val,
2068 l_email_val,
2069 l_fax_val,
2070 l_bind_var,
2071 l_bind_val,
2072 l_bind_var_type,
2073 x_request_history_id,
2074 l_content_xml1,
2075 p_fulfill_electronic_rec.version_id,
2076 nvl(p_fulfill_electronic_rec.email_format,'BOTH')
2077 );
2078
2079 JTF_FM_UTL_V.PRINT_MESSAGE('Get_Rendition_XML Return Status is ' || l_return_status,
2080 JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
2081
2082 l_content_xml := l_content_xml1;
2083
2084 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2085 RAISE FND_API.G_EXC_ERROR;
2086 END IF;
2087
2088 IF(upper(p_fulfill_electronic_rec.log_user_ih) = 'Y') THEN
2089 l_per_user_history := FND_API.G_TRUE;
2090 ELSE
2091 l_per_user_history := FND_API.G_FALSE;
2092 END IF;
2093
2094 IF(length(p_fulfill_electronic_rec.subject) > 250) THEN
2095 l_subject := substrb(p_fulfill_electronic_rec.subject,1,250);
2096 ELSE
2097 l_subject := p_fulfill_electronic_rec.subject;
2098 END IF;
2099
2100 /**
2101 if the request type is 'T', then call the submit test request
2102 **/
2103 IF upper(p_fulfill_electronic_rec.request_type) = 'T' THEN
2104 GET_TEST_XML
2105 (p_party_id => p_fulfill_electronic_rec.party_id,
2106 p_email => p_fulfill_electronic_rec.email,
2107 p_fax => p_fulfill_electronic_rec.fax,
2108 p_printer => p_fulfill_electronic_rec.printer,
2109 p_media => p_fulfill_electronic_rec.media_types,
2110 p_content_xml => l_content_xml,
2111 x_return_status => l_return_status,
2112 x_test_xml => x_test_xml
2113
2114 ) ;
2115 JTF_FM_UTL_V.PRINT_MESSAGE('GET_TEST_XML Return Status is ' || l_return_status,
2116 JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
2117 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
2118 THEN
2119 RAISE FND_API.G_EXC_ERROR;
2120 END IF;
2121
2122 END IF;
2123
2124
2125 -- Check if the agent wants detailed history
2126 IF l_per_user_history = FND_API.G_FALSE THEN
2127 l_fm_pvt_rec.party_id := -229929;
2128 END IF;
2129
2130 /**
2131 set all the values for the record type based on the
2132 parameters passed into CREATE_FULFILLMENT
2133 **/
2134 /**
2135 identify if it it is a single or a mass request
2136 **/
2137 IF(upper(p_fulfill_electronic_rec.request_type) = 'S')THEN
2138
2139 l_fm_pvt_rec.queue := 'S';
2140 l_message :=l_content_xml;
2141 ELSIF(upper(p_fulfill_electronic_rec.request_type) = 'T') THEN
2142 l_fm_pvt_rec.queue := 'B';
2143 l_message := x_test_xml;
2144 l_fm_pvt_rec.preview := 'TEST';
2145 ELSE --assuming the only other case is mass
2146 l_fm_pvt_rec.queue := 'M';
2147 -- check if content_source is 'ocm', else throw error
2148 -- Mass request is supported only for OCM contents
2149 -- Proceed
2150 IF(INSTR(l_content_xml,'query_id') >0)
2151 THEN
2152 JTF_FM_UTL_V.PRINT_MESSAGE('Item has a valid query OCM Repository',
2153 JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
2154 IF upper(p_fulfill_electronic_rec.request_type) = 'L' THEN
2155 l_message := '<items>' || l_content_xml || '</items><batch><label/></batch>';
2156 ELSE
2157 l_message := '<items>' || l_content_xml || '</items><batch><mass/></batch>';
2158 END IF;
2159 ELSE
2160 -- throw error, item should have a query assoc for mass requests
2161 l_Error_Msg := 'Content must have a valid query associated with it.';
2162 JTF_FM_UTL_V.HANDLE_ERROR('JTF_FM_API_MISSING_OCM_QUERY',l_content_id);
2163 RAISE FND_API.G_EXC_ERROR;
2164 END IF;
2165 END IF;
2166 l_fm_pvt_rec.priority := 1.0;
2167 l_fm_pvt_rec.content_xml := l_message;
2168 l_fm_pvt_rec.request_id := x_request_history_id ;
2169 l_fm_pvt_rec.doc_id := 1.0;
2170 l_fm_pvt_rec.doc_ref := 'UNSET';
2171 JTF_FM_UTL_V.FM_SUBMIT_REQ_V1
2172 (
2173 p_api_version ,
2174 p_init_msg_list,
2175 p_commit,
2176 x_return_status,
2177 x_msg_count,
2178 x_msg_data,
2179 p_fulfill_electronic_rec,
2180 l_fm_pvt_rec
2181 );
2182 JTF_FM_UTL_V.PRINT_MESSAGE('Submit_Mass_Request Return Status is ' || x_return_status,
2183 JTF_FM_UTL_V.G_LEVEL_STATEMENT,l_full_name);
2184 IF(l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2185 RAISE FND_API.G_EXC_ERROR;
2186 END IF;
2187
2188 IF(upper(p_fulfill_electronic_rec.request_type) = 'T') THEN
2189
2190 UPDATE JTF_FM_REQUEST_HISTORY_ALL
2191 SET request_type = 'T'
2192 WHERE hist_req_id = x_request_history_id;
2193 END IF;
2194
2195
2196 ELSE
2197 l_Error_Msg := 'Invalid request type specified. Only allowed values are ';
2198 l_Error_Msg := l_ERROR_Msg || 'E,P or T';
2199 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
2200 THEN
2201 FND_MESSAGE.set_name('JTF', 'JTF_FM_API_INVALID_REQTYPE');
2202 FND_MSG_PUB.Add;
2203 END IF; -- IF FND_MSG_PUB.check_msg_level
2204 RAISE FND_API.G_EXC_ERROR;
2205 END IF; -- end if electronic fulfillment
2206
2207 -- Standard check of p_commit.
2208 IF FND_API.To_Boolean( p_commit ) THEN
2209 COMMIT WORK;
2210 END IF;
2211
2212 -- Standard call to get message count and if count is 1, get message info.
2213 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2214
2215
2216 EXCEPTION
2217 WHEN FND_API.G_EXC_ERROR THEN
2218 ROLLBACK TO create_fulfillment;
2219 x_return_status := FND_API.G_RET_STS_ERROR ;
2220 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2221
2222 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2223 THEN
2224 ROLLBACK TO create_fulfillment;
2225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2226 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2227
2228 WHEN OTHERS
2229 THEN
2230 ROLLBACK TO create_fulfillment;
2231 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2232 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2233 THEN
2234 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name);
2235 END IF;
2236 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count, p_data => x_msg_data );
2237
2238 END CREATE_FULFILLMENT_RENDITION;
2239 END JTF_FM_OCM_REND_REQ;