[Home] [Help]
PACKAGE BODY: APPS.EDR_XDOC_UTIL_PKG
Source
1 PACKAGE BODY EDR_XDOC_UTIL_PKG AS
2 /* $Header: EDRXDUB.pls 120.12.12000000.1 2007/01/18 05:56:47 appldev ship $ */
3
4 --Bug 5256904: Start
5 --This procedure is used to delete the temporary parameters that were set prior to the PDF e-record
6 --creation process.
7 PROCEDURE DELETE_TEMP_PARAMS
8 (
9 P_ERECORD_ID IN NUMBER
10 )
11
12 IS
13
14 PRAGMA AUTONOMOUS_TRANSACTION;
15
16 BEGIN
17
18 DELETE FROM EDR_ERESPARAMETERS_T
19 WHERE PARENT_ID = P_ERECORD_ID
20 AND PARENT_TYPE = 'EDR_XDOC_PARAMS';
21
22 COMMIT;
23
24 EXCEPTION
25 WHEN OTHERS THEN
26 ROLLBACK;
27 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
28 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
29 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_XDOC_UTIL_PKG');
30 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','DELETE_TEMP_PARAMS');
31 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
32 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
33 'edr.plsql.EDR_XDOC_UTIL_PKG.DELETE_TEMP_PARAMS',
34 FALSE
35 );
36 end if;
37 APP_EXCEPTION.RAISE_EXCEPTION;
38 END DELETE_TEMP_PARAMS;
39
40 --This procedure sets those parameters required for the AppsContext initialization
41 --into the ERES Temp Table.
42 PROCEDURE SET_TEMP_PARAMS
43 (
44 P_ERECORD_ID IN NUMBER
45 )
46
47 IS
48
49 L_NLS_DATE_FORMAT VARCHAR2(4000);
50 L_NLS_DATE_LANGUAGE VARCHAR2(4000);
51 L_NLS_LANGUAGE VARCHAR2(4000);
52 L_NLS_NUMERIC_CHARACTERS VARCHAR2(4000);
53 L_NLS_SORT VARCHAR2(4000);
54 L_NLS_TERRITORY VARCHAR2(4000);
55 L_CURRENT_LANG VARCHAR2(4000);
56 L_USER_ID NUMBER;
57 L_RESP_ID NUMBER;
58 L_RESP_APPL_ID NUMBER;
59 L_SECURITY_GROUP_ID NUMBER;
60 PRAGMA AUTONOMOUS_TRANSACTION;
61
62 BEGIN
63
64 L_USER_ID := FND_GLOBAL.USER_ID();
65 L_RESP_ID := FND_GLOBAL.RESP_ID();
66 L_RESP_APPL_ID := FND_GLOBAL.RESP_APPL_ID();
67 L_SECURITY_GROUP_ID :=FND_GLOBAL.SECURITY_GROUP_ID();
68 L_NLS_DATE_FORMAT := FND_GLOBAL.NLS_DATE_FORMAT;
69 L_NLS_DATE_LANGUAGE := FND_GLOBAL.NLS_DATE_LANGUAGE;
70 L_NLS_LANGUAGE := FND_GLOBAL.NLS_LANGUAGE;
71 L_NLS_NUMERIC_CHARACTERS := FND_GLOBAL.NLS_NUMERIC_CHARACTERS;
72 L_NLS_SORT := FND_GLOBAL.NLS_SORT;
73 L_NLS_TERRITORY := FND_GLOBAL.NLS_TERRITORY;
74 L_CURRENT_LANG := USERENV('LANG');
75
76 --We will log the NLS Parameters in the EVENT Level.
77 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
78 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,'edr.plsql.EDR_XDOC_UTIL_PKG.SET_TEMP_PARAMS',
79 'These are the NLS parameters for the e-record ID '||p_erecord_id);
80 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,'edr.plsql.EDR_XDOC_UTIL_PKG.SET_TEMP_PARAMS',
81 'NLS Date Format: '||L_NLS_DATE_FORMAT);
82 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,'edr.plsql.EDR_XDOC_UTIL_PKG.SET_TEMP_PARAMS',
83 'NLS Date Language: '||L_NLS_DATE_LANGUAGE);
84 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,'edr.plsql.EDR_XDOC_UTIL_PKG.SET_TEMP_PARAMS',
85 'NLS Language: '||L_NLS_LANGUAGE);
86 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,'edr.plsql.EDR_XDOC_UTIL_PKG.SET_TEMP_PARAMS',
87 'NLS Numeric Characters: '||L_NLS_NUMERIC_CHARACTERS);
88 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,'edr.plsql.EDR_XDOC_UTIL_PKG.SET_TEMP_PARAMS',
89 'NLS Sort: '||L_NLS_SORT);
90 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,'edr.plsql.EDR_XDOC_UTIL_PKG.SET_TEMP_PARAMS',
91 'NLS Territory: '||L_NLS_TERRITORY);
92
93 end if;
94
95
96 INSERT INTO EDR_ERESPARAMETERS_T(PARAM_ID,
97 PARENT_ID,
98 PARENT_TYPE,
99 PARAM_NAME,
100 PARAM_VALUE)
101
102 VALUES(EDR_ERESPARAMETERS_T_S.NEXTVAL,
103 P_ERECORD_ID,
104 'EDR_XDOC_PARAMS',
105 'NLS_DATE_FORMAT',
106 L_NLS_DATE_FORMAT);
107
108 INSERT INTO EDR_ERESPARAMETERS_T(PARAM_ID,
109 PARENT_ID,
110 PARENT_TYPE,
111 PARAM_NAME,
112 PARAM_VALUE)
113
114 VALUES(EDR_ERESPARAMETERS_T_S.NEXTVAL,
115 P_ERECORD_ID,
116 'EDR_XDOC_PARAMS',
117 'NLS_DATE_LANGUAGE',
118 L_NLS_DATE_LANGUAGE);
119
120 INSERT INTO EDR_ERESPARAMETERS_T(PARAM_ID,
121 PARENT_ID,
122 PARENT_TYPE,
123 PARAM_NAME,
124 PARAM_VALUE)
125
126 VALUES(EDR_ERESPARAMETERS_T_S.NEXTVAL,
127 P_ERECORD_ID,
128 'EDR_XDOC_PARAMS',
129 'NLS_LANGUAGE',
130 L_NLS_LANGUAGE);
131
132 INSERT INTO EDR_ERESPARAMETERS_T(PARAM_ID,
133 PARENT_ID,
134 PARENT_TYPE,
135 PARAM_NAME,
136 PARAM_VALUE)
137
138 VALUES(EDR_ERESPARAMETERS_T_S.NEXTVAL,
139 P_ERECORD_ID,
140 'EDR_XDOC_PARAMS',
141 'NLS_NUMERIC_CHARACTERS',
142 L_NLS_NUMERIC_CHARACTERS);
143
144 INSERT INTO EDR_ERESPARAMETERS_T(PARAM_ID,
145 PARENT_ID,
146 PARENT_TYPE,
147 PARAM_NAME,
148 PARAM_VALUE)
149
150 VALUES(EDR_ERESPARAMETERS_T_S.NEXTVAL,
151 P_ERECORD_ID,
152 'EDR_XDOC_PARAMS',
153 'NLS_SORT',
154 L_NLS_SORT);
155
156 INSERT INTO EDR_ERESPARAMETERS_T(PARAM_ID,
157 PARENT_ID,
158 PARENT_TYPE,
159 PARAM_NAME,
160 PARAM_VALUE)
161
162 VALUES(EDR_ERESPARAMETERS_T_S.NEXTVAL,
163 P_ERECORD_ID,
164 'EDR_XDOC_PARAMS',
165 'NLS_TERRITORY',
166 L_NLS_TERRITORY);
167
168 INSERT INTO EDR_ERESPARAMETERS_T(PARAM_ID,
169 PARENT_ID,
170 PARENT_TYPE,
171 PARAM_NAME,
172 PARAM_VALUE)
173
174 VALUES(EDR_ERESPARAMETERS_T_S.NEXTVAL,
175 P_ERECORD_ID,
176 'EDR_XDOC_PARAMS',
177 'CURRENT_LANG',
178 L_CURRENT_LANG);
179 INSERT INTO EDR_ERESPARAMETERS_T(PARAM_ID,
180 PARENT_ID,
181 PARENT_TYPE,
182 PARAM_NAME,
183 PARAM_VALUE)
184
185 VALUES(EDR_ERESPARAMETERS_T_S.NEXTVAL,
186 P_ERECORD_ID,
187 'EDR_XDOC_PARAMS',
188 'USER_ID',
189 TO_CHAR(L_USER_ID));
190
191 INSERT INTO EDR_ERESPARAMETERS_T(PARAM_ID,
192 PARENT_ID,
193 PARENT_TYPE,
194 PARAM_NAME,
195 PARAM_VALUE)
196
197 VALUES(EDR_ERESPARAMETERS_T_S.NEXTVAL,
198 P_ERECORD_ID,
199 'EDR_XDOC_PARAMS',
200 'RESP_ID',
201 TO_CHAR(L_RESP_ID));
202
203 INSERT INTO EDR_ERESPARAMETERS_T(PARAM_ID,
204 PARENT_ID,
205 PARENT_TYPE,
206 PARAM_NAME,
207 PARAM_VALUE)
208
209 VALUES(EDR_ERESPARAMETERS_T_S.NEXTVAL,
210 P_ERECORD_ID,
211 'EDR_XDOC_PARAMS',
212 'RESP_APPL_ID',
213 TO_CHAR(L_RESP_APPL_ID));
214
215 INSERT INTO EDR_ERESPARAMETERS_T(PARAM_ID,
216 PARENT_ID,
217 PARENT_TYPE,
218 PARAM_NAME,
219 PARAM_VALUE)
220
221 VALUES(EDR_ERESPARAMETERS_T_S.NEXTVAL,
222 P_ERECORD_ID,
223 'EDR_XDOC_PARAMS',
224 'SECURITY_GROUP_ID',
225 TO_CHAR(L_SECURITY_GROUP_ID));
226
227 COMMIT;
228
229
230 EXCEPTION
231 WHEN OTHERS THEN
232 ROLLBACK;
233 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
234 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
235 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_XDOC_UTIL_PKG');
236 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','SET_TEMP_PARAMS');
237 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
238 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
239 'edr.plsql.EDR_XDOC_UTIL_PKG.SET_TEMP_PARAMS',
240 FALSE
241 );
242 end if;
243 APP_EXCEPTION.RAISE_EXCEPTION;
244 END SET_TEMP_PARAMS;
245 --Bug 5256904: End
246
247
248
249 -- EDR_XDOC_UTIL_PKG.GENERATE_ERECORD Procedure is called from STORE_ERECORD
250 -- Procedure of RULE FUNCTION. Its purpose is to call the JSP with all the
251 -- required parameters passed in URL and return ERECORD or return error message
252 -- if there is any error in this processing.
253
254 -- P_EDR_EVENT_ID - Unique event id for the event being executed, for which
255 -- eRecord is to be generated
256 -- P_ERECORD_ID - ERecord Id for the eRecord to be generated.
257 -- P_STYLE_SHEET_REPOSITORY - Name of template repository to be used to generate the
258 -- eRecord
259 -- P_STYLE_SHEET - Template File Name as Setup in AME / Transaction Variable for
260 -- this event
261 -- P_STYLE_SHEET_VER - Version Label of the template file as setup in AME / Transaction Variable for
262 -- this event
263 -- X_OUTPUT_FORMAT - Format of eRecord Output viz. PDF, DOC, HTML, TEXT
264 -- X_ERROR_CODE - Error Code in case of any errors
265 -- X_ERROR_MESSAGE - Error Message in case of any errors.
266
267 -- Bug 3170251 : start : rvsingh
268 -- Bug 3761813 : start : rvsingh
269 PROCEDURE GENERATE_ERECORD
270 (p_edr_event_id NUMBER,
271 p_erecord_id NUMBER,
272 p_style_sheet_repository VARCHAR2,
273 p_style_sheet VARCHAR2,
274 p_style_sheet_ver VARCHAR2,
275 p_application_code VARCHAR2,
276 p_redline_mode VARCHAR2,
277 x_output_format OUT NOCOPY VARCHAR2,
278 x_error_code OUT NOCOPY NUMBER,
279 x_error_msg OUT NOCOPY VARCHAR2
280 )
281 as
282 l_http_response VARCHAR2(20480);
283 l_url VARCHAR2(1000);
284 l_position Number;
285 l_subr1 VARCHAR2(100);
286 l_status VARCHAR2(100);
287 l_output_format VARCHAR2(100);
288 L_PAGENOTFOUND EXCEPTION;
289 l_agent varchar2(1000);
290 PROFILE_ERROR exception;
291 REQUEST_FAIL exception;
292 INIT_FAIL exception;
293 -- Bug 4450651 Start
294 l_src_req varchar2(100);
295 -- Bug 4450651 End
296 -- Bug 5170875 : start
297 l_USER_ID NUMBER;
298 l_RESP_ID NUMBER;
299 l_RESP_APPL_ID NUMBER;
300 l_SECURITY_GROUP_ID NUMBER;
301 -- Bug 5170875 : End
302 l_module_name varchar2(50) := 'edr.plsql.EDR_XDOC_UTIL_PKG.GENERATE_ERECORD';
303 begin
304 l_http_response := null;
305 l_status := null;
306 l_output_format := null;
307 L_url := null;
308
309 l_src_req := null;
310 l_src_req := GET_SERVICE_TICKET_STRING(EDR_CONSTANTS_GRP.g_service_name);
311
312
313 l_agent := FND_PROFILE.VALUE('APPS_JSP_AGENT');
314 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
315 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,l_module_name,
316 'APPS JSP Agent is: '||l_agent);
317 end if;
318
319 --Bug 5256904: Start
320 SET_TEMP_PARAMS(P_ERECORD_ID);
321 --Bug 5256904: End
322
323 -- Bug 3761813 : start : new query string ''redLineMode' added in HTTP request
324 l_url := l_agent || '/OA_HTML/jsp/edr/EDRRuleXMLPublisherHandler.jsp?eventId=' || p_edr_event_id;
325 l_url := l_url || '&' || 'repository=' || p_style_sheet_repository || '&'|| 'erecordId=' || p_erecord_id;
326 l_url := l_url || '&' || 'ssName=' ||p_style_sheet || '&' || 'ssVer=' || p_style_sheet_ver || '&' || 'src_req=' || l_src_req;
327 l_url := l_url || '&' || 'appCode=' ||p_application_code;
328 l_url := l_url || '&' || 'redLineMode=' ||p_redline_mode ;
329 -- Bug 3761813 : END
330
331 --These will be picked up when the AppsContext is initialized in the Java Layer.
332
333 -- Call UTL_HTTP using EDR_XDOC_UTIL_PKG.REQUEST_HTTP package wrapper
334 l_http_response := EDR_XDOC_UTIL_PKG.REQUEST_HTTP( p_request_url => l_url);
335
336 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
337 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,l_module_name,
338 'HTTP Response from JSP: '||l_http_response);
339 end if;
340
341 --Bug 5256904: Start
342 DELETE_TEMP_PARAMS(P_ERECORD_ID);
343 --bug 5256904: End
344
345 --Check if UTL_HTTP request returned with JSP Not found message.
346 IF ((INSTR(SUBSTR(L_HTTP_RESPONSE,1,1024), 'FileNotFoundException', 1) > 0)
347 OR (INSTR(SUBSTR(L_HTTP_RESPONSE,1,1024), '404 Not Found',1) > 0))
348 THEN
349 RAISE L_PAGENOTFOUND;
350 END IF;
351
352 l_position := instr(l_http_response,'=',1);
353 l_http_response := substr(l_http_response,l_position+1);
354 l_position :=instr(l_http_response,';',1);
355 l_status := substr(l_http_response,1,l_position-1);
356
357 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
358 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,l_module_name,
359 'Status from JSP: '||l_status);
360 end if;
361
362 l_position := instr(l_http_response,'=',1);
363 l_http_response := substr(l_http_response,l_position+1);
364 l_position :=instr(l_http_response,';',1);
365 --p_stylesheet_type := substr(l_http_response,1,l_position-1);
366 -- Bug 4731317 : start
367
368 -- Error Message
369 l_position := instr(l_http_response,'=',1);
370 l_http_response := substr(l_http_response,l_position+1);
371 l_position :=instr(l_http_response,';',1);
372 x_error_msg := substr(l_http_response,1,l_position-1);
373 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
374 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,l_module_name,
375 'Error message from JSP: '||x_error_msg);
376 end if;
377
378 l_http_response := substr(l_http_response,l_position+1);
379
380 -- OUTPUT FORMAT
381 l_position := instr(l_http_response,'=',1);
382 l_http_response := substr(l_http_response,l_position+1);
383 l_position :=instr(l_http_response,';',1);
384 x_output_format := substr(l_http_response,1,l_position-1);
385 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
386 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,l_module_name,
387 'Output format from JSP: '||x_output_format);
388 end if;
389
390 -- Bug 4731317 : end
391
392 -- PDF Generation was successful.
393 if(l_status = 'SUCCESS') then
394 x_error_code := 0;
395 return;
396 end if;
397
398
399 -- PDF Generation failed. Can be some issue with template
400 if(INSTR(l_status,'FAILURE',1) > 0) then
401 x_error_code := 40;
402 return;
403 end if;
404
405 -- XSL FO for given template is missing
406 if (l_status = 'RTFTEMPLATEMISSING') then
407 x_error_code := 10;
408 x_error_msg := l_status;
409 return;
410 end if;
411
412 -- ERecord PSIG XML was not Accessible
413 if (l_status = 'ERECORDMISSING') then
414 x_error_code := 20;
415 x_error_msg := l_status;
416 return;
417 end if;
418
419 -- Fatal Error in case of database connection or context not available.
420 if (l_status = 'FATALERROR') then
421 x_error_code := 30;
422 x_error_msg := x_error_msg;
423 return;
424 else
425 begin
426 -- Unhandled error mean error not known to us and currently not handled
427 x_error_msg := 'UNHANDLEDERROR';
428 x_error_code := 100;
429 return;
430 end;
431 end if;
432 EXCEPTION
433 WHEN L_PAGENOTFOUND THEN
434 BEGIN
435 X_ERROR_MSG := 'JSPMISSING';
436 X_ERROR_CODE := 505;
437 END;
438 WHEN PROFILE_ERROR THEN
439 BEGIN
440 fnd_message.set_name('EDR','EDR_PROFILE_CHECK_FAILURE');
441 fnd_message.set_token('PROFILENAME','FND_DB_WALLET_DIR');
442 X_ERROR_MSG := FND_MESSAGE.GET;
443 X_ERROR_CODE := 100;
444 END;
445 WHEN REQUEST_FAIL THEN
446 BEGIN
447 X_ERROR_MSG := 'JSPREQFAIL';
448 X_ERROR_CODE := 500;
449 END;
450 WHEN INIT_FAIL THEN
451 BEGIN
452 X_ERROR_MSG := 'JSPREQFAIL';
453 X_ERROR_CODE := 500;
454 END;
455 WHEN OTHERS THEN
456 BEGIN
457 X_ERROR_MSG := SQLERRM;
458 X_ERROR_CODE := 800;
459 END;
460 End GENERATE_ERECORD;
461
462
463 -- EDR_XDOC_UTIL_PKG.EDR_CREATE_ATTACHEMENT is called from EDRRuleXMLPublisher Object.
464 -- It creates an FND Attachment for the eRecord PDF to be generated and returns
465 -- the file_id created in FND_LOBS table.
466
467 -- P_ERECORD_ID - ERecord Id for the eRecord to be generated.
468 -- P_FILE_NAME - Name of EReocrd File
469 -- P_STYLE_SHEET - ERecord File Description
470 -- P_CONTENT_TYPE - ERecord File Content Type
471 -- X_File_ID - MediaId (FND_LOBS File Id) of the ERecord Attachement
472 -- created
473
474 Procedure EDR_CREATE_ATTACHMENT (
475 p_eRecord_ID NUMBER,
476 P_FILE_NAME VARCHAR2,
477 p_description VARCHAR2,
478 p_content_type VARCHAR2,
479 p_file_format VARCHAR2,
480 p_source_lang VARCHAR2,
481 x_FILE_id OUT NOCOPY NUMBER
482 )
483
484 IS
485
486 /* Cursors */
487 CURSOR c_get_category IS
488 SELECT category_id
489 FROM fnd_document_categories
490 WHERE name = 'ERES';
491
492 LocalCatRecord c_get_category%ROWTYPE;
493
494 /* Used to get the next attached_document_id for a new attachment SKARIMIS*/
495 CURSOR c_get_id IS
496 SELECT fnd_attached_documents_s.nextval
497 FROM dual;
498
499 /* Numeric Variables */
500 L_ROW_ID VARCHAR2(240);
501 L_CATEGORY_ID NUMBER;
502 L_FND_DOCUMENT_ID NUMBER;
503 L_ATTACHED_DOCUMENT_ID NUMBER;
504 l_user_id number;
505 l_user_name varchar2(240);
506
507 BEGIN
508
509
510 /* Select the category id */
511 OPEN c_get_category;
512 FETCH c_get_category INTO LocalCatRecord;
513 l_category_id := LocalCatRecord.category_id;
514 CLOSE c_get_category;
515
516
517 OPEN c_get_id;
518 FETCH c_get_id INTO l_attached_document_id;
519 CLOSE c_get_id;
520
521 /* Set the Security Context to access EDR_PSIG_DOCUMENTS */
522 edr_ctx_pkg.set_secure_attr;
523
524 select document_requester into l_user_name
525 from edr_psig_documents where document_id = p_eRecord_Id;
526
527 /* Get the requestors user-id for Last Updated by field in Attachments */
528 select user_id into l_user_id
529 from fnd_user where user_name = l_user_name;
530
531 -- Bug 4045057 : Start
532 -- Unset the secure context
533
534 edr_ctx_pkg.unset_secure_attr;
535
536 -- Bug 4045057 : End
537
538 FND_ATTACHED_DOCUMENTS_PKG.Insert_Row(X_Rowid=>l_row_id,
539 X_attached_document_id=>l_attached_document_id,
540 X_document_id=>l_fnd_document_id,
541 X_creation_date=>SYSDATE,
542 X_created_by=> L_USER_ID,
543 X_last_update_date=>SYSDATE,
544 X_last_updated_by=>L_USER_ID,
545 X_last_update_login=>L_USER_ID,
546 X_seq_num=>1,
547 X_entity_name=>'ERECORD',
548 X_column1=>NULL,
549 X_pk1_value=>p_eRecord_ID,
550 X_pk2_value=>NULL,
551 X_pk3_value=>NULL,
552 X_pk4_value=>NULL,
553 X_pk5_value=>NULL,
554 X_automatically_added_flag=>'N',
555 X_datatype_id=>6,
556 X_category_id=>l_category_id,
557 --Bug 4381237: Start
558 --We want to set the security type to 4.
559 X_security_type=>4,
560 --Security ID should be set to null
561 X_security_id=>null,
562 --Bug 4381237: End
563 X_publish_flag=>'N',
564 X_storage_type=>1,
565 X_usage_type=>'S',
566 X_language=>p_source_lang,
567 X_description=>p_description,
568 X_file_name=>p_file_name,
569 X_media_id=>x_file_id,
570 X_doc_attribute_category=>null,
571 X_doc_attribute1=>null,
572 X_doc_attribute2=>null,
573 X_doc_attribute3=>null,
574 X_doc_attribute4=>null,
575 X_doc_attribute5=>null,
576 X_doc_attribute6=>null,
577 X_doc_attribute7=>null,
578 X_doc_attribute8=>null,
579 X_doc_attribute9=>null,
580 X_doc_attribute10=>null,
581 X_create_doc=>'N');
582
583 INSERT into FND_LOBS
584 (file_id,
585 file_name,
586 file_data,
587 file_content_type,
588 file_format)
589 VALUES
590 (x_file_id,
591 p_file_name,
592 empty_blob(),
593 p_content_type,
594 p_file_format);
595
596 END EDR_CREATE_ATTACHMENT;
597
598 -- EDR_XDOC_UTIL_PKG.GET_NTF_MESSAGE_BODY is called from Workflow while rendering the
599 -- Notification for rendering E-Record Message "Please read the attached ... eRecord_XXXX.pdf"
600 -- This procedure follows PLSQL Document Attrubute Format API Call conventions
601
602 -- p_document_id - This field is used to pass eRecord Id -- > ERECORD_ID
603 -- E_RECORD_ID to be part of message body in file name
604 -- p_display_type - Format of display text/palin, text/html etc...
605 -- x_document - Document rendered in VARCHAR2 string is returned
606 -- eRecord
607 -- x_document_type - Document type i.e. text, rtf, doc, etc...
608
609 Procedure GET_NTF_MESSAGE_BODY
610 ( p_document_id in varchar2,
611 p_display_type in varchar2,
612 x_document in out nocopy varchar2,
613 x_document_type in out nocopy varchar2
614 )
615 Is
616 Begin
617 fnd_message.set_name('EDR','EDR_EREC_ATT_NTF_BODY');
618 fnd_message.set_token('ERECORD_ID',p_document_id);
619 x_document := fnd_message.get;
620 x_document_type := 'text/plain';
621 End GET_NTF_MESSAGE_BODY;
622
623 -- Bug 3950047 : Start
624
625 -- EDR_XDOC_UTIL_PKG.REQUEST_HTTP provides a wrapper over UTL_HTTP calls
626 -- It performs all the checks required on URL before calling UTL_HTTP.REQUEST
627 -- This FUNCTION follows PLSQL API Call conventions.
628
629 -- p_request_url - Request URL over which UTL_HTTP call is to be made.
630 -- returns varchar2 - HTTP_RESPONSE returned from UTL_HTTP.REQUEST
631
632 -- throws - profile_error which must be caught in calling procedure.
633
634 function REQUEST_HTTP
635 (
636 p_request_url in varchar2
637
638 ) return varchar2
639 is
640 l_http_response varchar2(20480);
641 -- Bug : 5170875 : start
642 l_module_name varchar2(50) := 'edr.plsql.EDR_XDOC_UTIL_PKG.REQUEST_HTTP';
643 -- Bug : 5170875 : end
644 l_wallet varchar2(1000);
645 profile_error exception;
646 begin
647 l_http_response := null;
648 l_wallet := null;
649
650 if(instr(upper(p_request_url),'HTTPS' ) > 0 ) then
651 if(fnd_profile.defined('FND_DB_WALLET_DIR')) then
652 l_wallet := fnd_profile.value('FND_DB_WALLET_DIR');
653 if(l_wallet is null) then
654 raise profile_error;
655 end if;
656 else
657 raise profile_error;
658 end if;
659
660 -- Append file: in the beginning and set the wallet directory
661 -- Before UTL_HTTP call.
662
663 l_wallet := 'file:' || l_wallet;
664 utl_http.set_wallet(l_wallet);
665 end if;
666 -- Bug : 5170875 : start
667 if (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
668 FND_LOG.STRING(FND_LOG.LEVEL_EVENT,l_module_name,
669 'HTTP Request to JSP: '||p_request_url);
670 end if;
671 -- Bug : 5170875 : End
672 l_http_response := utl_http.request( URL=> p_request_url);
673 return l_http_response;
674 END REQUEST_HTTP;
675
676 -- Bug 3950047 : End
677
678 -- Bug 4450651 start
679 -- Gets the ticket for use with an HTTP service.
680 -- Returns string va;ue of a pair of 16-byte secure random raw values concatenated into
681 -- a 32-byte raw, or null upon failure. The first 16 bytes are the
682 -- current ticket value, and the last 16 bytes are the previous ticket
683 -- value.
684
685
686 function GET_SERVICE_TICKET_STRING
687 (
688 p_request_service_name in varchar2
689 ) return varchar2 IS PRAGMA AUTONOMOUS_TRANSACTION;
690 l_service_ticket varchar2(100);
691 X raw(16) := hextoraw('00000000000000000000000000000000');
692 begin
693 l_service_ticket := null;
694 delete from FND_HTTP_SERVICE_TICKETS where SERVICE = p_request_service_name;
695 insert into FND_HTTP_SERVICE_TICKETS
696 (SERVICE, TICKET, OLD_TICKET,END_DATE) values (p_request_service_name, X, X,sysdate);
697 commit;
698 l_service_ticket := FND_HTTP_TICKET.GET_SERVICE_TICKET_STRING(p_request_service_name);
699 return l_service_ticket;
700 END GET_SERVICE_TICKET_STRING;
701
702
703 -- Compare service ticket
704
705 function VALIDATE_SERVICE_TICKET(P_TICKET in varchar2)
706 return varchar2
707 is
708 X_TICKET VARCHAR2(100);
709 begin
710
711 X_TICKET := GET_SERVICE_TICKET_STRING(EDR_CONSTANTS_GRP.g_service_name);
712
713 IF(FND_HTTP_TICKET.COMPARE_SERVICE_TICKET_STRINGS(P_TICKET,X_TICKET)) THEN
714 return EDR_CONSTANTS_GRP.g_success_service_req_status;
715 ELSE
716 return EDR_CONSTANTS_GRP.g_fail_service_req_status;
717 END IF;
718 end VALIDATE_SERVICE_TICKET;
719 -- Bug 4450651 End
720
721 End EDR_XDOC_UTIL_PKG;