1 PACKAGE BODY EDR_TEMPLATE_SUBS AS
2 /* $Header: EDRTMPSB.pls 120.2.12000000.1 2007/01/18 05:55:47 appldev ship $ */
3
4 --- Status to be returned to indicate workflow ---
5 G_YES CONSTANT VARCHAR2(25) := 'COMPLETE:Y';
6 G_NO CONSTANT varchar2(25) := 'COMPLETE:N';
7
8 --- This is the file category for eRecord Template ----
9 G_TMP_CATEGORY CONSTANT VARCHAR2(30) := 'EDR_EREC_TEMPLATE';
10
11 -- EDR_TEMPLATE_SUBS.UPLOAD_TEMPLATE
12 -- This procedure is a post process function called from RTFUPLOAF Workflow process.
13 -- It gets the name of file being approved and converts it to XSLFO if it is EDR_EREC_TEMPLATE
14 -- and File Extension is RTF.
15
16 -- P_ITEMTYPE The internal name for the item type. Item types are
17 -- defined in the Oracle Workflow Builder.
18
19 -- P_ITEMKEY A string that represents a primary key generated
20 -- by the workflow-enabled application for the item
21 -- type. The string uniquely identifies the item within
22 -- an item type.
23
24 -- P_ACTID The ID number of the activity from which this
25 -- procedure is called.
26
27 -- P_FUNCMODE The execution mode of the activity. If the activity is
28 -- a function activity, the mode is either RUN or
29 -- CANCEL. If the activity is a notification activity,
30 -- with a postnotification function, then the mode
31 -- can be RESPOND, FORWARD, TRANSFER,
32 -- TIMEOUT, or RUN.
33
34 -- P_RESULTOUT If a result type is specified in the Activities
35 -- properties page for the activity in the Oracle
36 -- Workflow Builder, this parameter represents the
37 -- expected result that is returned when the
38 -- procedure completes. The possible results are:
39 -- COMPLETE:<result_code> activity completes
40 -- with the indicated result code. The result code
41 -- must match one of the result codes specified in the
42 -- result type of the function activity.
43 -- WAITING-activity is pending, waiting on
44 -- another activity to complete before it completes.
45 -- An example is the Standard AND activity.
46 -- DEFERRED:<date>activity is deferred to a
47 -- background engine for execution until a given date.
48 -- <date> must be of the format:
49 -- to_char(<date_string>, wf_engine.date_format)
50 -- NOTIFIED:<notification_id>:<assigned_user>-a
51 -- n external entity is notified that an action must be
52 -- performed. A notification ID and an assigned user
53 -- can optionally be returned with this result. Note
54 -- that the external entity must call CompleteActivity( )
55 -- to inform the Workflow Engine when the action
56 -- completes.
57 -- ERROR:<error_code>-activity encounters an
58 -- error and returns the indicated error code.
59
60 PROCEDURE UPLOAD_TEMPLATE
61 (
62 P_ITEMTYPE VARCHAR2,
63 P_ITEMKEY VARCHAR2,
64 P_ACTID NUMBER,
65 P_FUNCMODE VARCHAR2,
66 P_RESULTOUT OUT NOCOPY VARCHAR2
67 )
68 AS
69 L_EVENT_NAME VARCHAR2(240);
70 L_EVENT_KEY VARCHAR2(240);
71
72 L_PRODUCT VARCHAR2(50);
73 L_AUTHOR VARCHAR2(100);
74 L_FILE_NAME VARCHAR2(300);
75
76 l_VERSION VARCHAR2(15);
77 L_VERSION_NUM NUMBER(15,3);
78
79 L_UPLOAD_STATUS VARCHAR2(300);
80 L_EXTENSION VARCHAR2(30);
81 L_RETURN_STATUS VARCHAR2(25);
82 L_CATEGORY_NAME VARCHAR2(30);
83 L_EVENT_STATUS VARCHAR2(15);
84 L_POS NUMBER;
85
86 -- VARIABLES USED FOR FND_ATTACHED_DOCUMENTS_PKG API
87 L_ROW_ID VARCHAR2(240);
88 L_ATTACHED_DOCUMENT_ID NUMBER;
89 L_FND_DOCUMENT_ID NUMBER;
90 L_MEDIA_ID NUMBER;
91 L_CATEGORY_ID NUMBER;
92 L_POSITION NUMBER;
93
94 -- VARIABLES USED FOR CALL TO JSP WITH UTL_HTTP PKG
95 L_HTTP_RESPONSE VARCHAR2(1024);
96 L_STATUS VARCHAR2(1024);
97 L_HTTP_URL VARCHAR2(1024);
98
99 l_no_approval_status VARCHAR2(20);
100 l_success_status VARCHAR2(20);
101
102 -- Bug : 3950047
103 -- VARIABLES RELATED TO UTL_HTTP ERROR HANDLING
104 L_PAGENOTFOUND EXCEPTION;
105 PROFILE_ERROR EXCEPTION;
106 REQUEST_FAIL EXCEPTION;
107 INIT_FAIL EXCEPTION;
108
109 L_agent VARCHAR2(1000);
110 -- Bug 4450651 Start
111 l_src_req varchar2(100);
112 -- Bug 4450651 End
113
114
115 CURSOR C_GET_ID IS
116 SELECT FND_ATTACHED_DOCUMENTS_S.NEXTVAL
117 FROM DUAL;
118 BEGIN
119 L_EVENT_NAME :=WF_ENGINE.GETITEMATTRTEXT(
120 ITEMTYPE=>P_ITEMTYPE,
121 ITEMKEY=>P_ITEMKEY,
122 ANAME=>'EVENT_NAME');
123
124 L_EVENT_KEY :=WF_ENGINE.GETITEMATTRTEXT(
125 ITEMTYPE=>P_ITEMTYPE,
126 ITEMKEY=>P_ITEMKEY,
127 ANAME=>'EVENT_KEY');
128
129 L_RETURN_STATUS := G_NO;
130 L_HTTP_RESPONSE := NULL ;
131 L_no_approval_status := 'NO APPROVAL';
132 l_success_status := 'SUCCESS';
133
134
135
136
137 WF_LOG_PKG.STRING(6, 'UPLOAD_RTF','EVENT NAME '||L_EVENT_NAME);
138 WF_LOG_PKG.STRING(6, 'UPLOAD_RTF','EVENT KEY '||L_EVENT_KEY);
139
140 --START WITH CHECKING THAT THE FILE IS AN RTF TEMPLATE
141 EDR_FILE_UTIL_PUB.GET_FILE_NAME(L_EVENT_KEY, L_FILE_NAME);
142
143 WF_LOG_PKG.STRING(6, 'UPLOAD_RTF','FILE NAME'||L_FILE_NAME);
144
145 --LOCATE BEGINNING OF THE EXTENSION IN THE FILE NAME STRING TO USE
146 --POSITION TO SPLIT UP THE FILE NAME
147 L_EXTENSION := NULL;
148 L_POS := INSTR(L_FILE_NAME, '.',-1,1);
149
150 IF L_POS <> 0 THEN
151 L_EXTENSION := SUBSTR(L_FILE_NAME,L_POS+1,LENGTH(L_FILE_NAME));
152 END IF;
153
154 WF_LOG_PKG.STRING(6, 'UPLOAD_RTF','EXTENSION '||L_EXTENSION);
155
156
157 --ONLY IF THE EXTENSION OF THE FILE IS XSL PROCEED FURTHER
158 IF (UPPER(L_EXTENSION) = 'RTF') THEN
159 --GET THE CATEGORY OF THE FILE
160 EDR_FILE_UTIL_PUB.GET_CATEGORY_NAME(L_EVENT_KEY,L_CATEGORY_NAME);
161 WF_LOG_PKG.STRING(6, 'UPLOAD_RTF','CATEGORY NAME'||L_CATEGORY_NAME);
162
163
164 -- CHECK THE FILE CATEGORY AFTER CHECKING EXTENSION, WHETHER ITS ERECORD TEMPLATE
165 IF (L_CATEGORY_NAME = G_TMP_CATEGORY) THEN
166 WF_LOG_PKG.STRING(6, 'UPLOAD_RTF','EDR ERCORD TEMPLATE CATEGORY NAME FOUND');
167 L_RETURN_STATUS := G_YES;
168 END IF;
169 END IF;
170
171 --NOW THAT WE HAVE MADE CERTAIN THAT THE FILE EXTENSION IS RTF AND THE CATEGORY
172 --IS E RECORD TEMPLATE GO AHEAD AND TRY TO UPLOAD IT
173
174 IF (L_RETURN_STATUS = G_YES) THEN
175 WF_LOG_PKG.STRING(6, 'UPLOAD_RTF','RETURN STATUS IS YES...STARTING UPLOAD');
176
177 --SET THE FILE AUTHOR ATTRIBUTE IN WORKFLOW
178 EDR_FILE_UTIL_PUB.GET_AUTHOR_NAME(L_EVENT_KEY, L_AUTHOR);
179 WF_LOG_PKG.STRING(6, 'UPLOAD_RTF','AUTHOR '||L_AUTHOR);
180 WF_ENGINE.SETITEMATTRTEXT(P_ITEMTYPE, P_ITEMKEY, 'AUTHOR',L_AUTHOR);
181 WF_LOG_PKG.STRING(6, 'UPLOAD_RTF','AUTHOR NAME SET IN THE WORKFLOW');
182
183 --set the file name
184 wf_engine.setitemattrtext(p_itemtype, p_itemkey, 'RTF_NAME', l_file_name);
185 wf_log_pkg.string(6, 'UPLOAD_TEMPLATE','file name set in the workflow');
186
187 --set the version
188 EDR_FILE_UTIL_PUB.GET_VERSION_LABEL(l_event_key, l_version);
189 l_version_num := l_version;
190 wf_log_pkg.string(6, 'UPLOAD_TEMPLATE','version num '||l_version_num);
191 wf_engine.setitemattrtext(p_itemtype, p_itemkey, 'VERSION', l_version);
192
193 --set the product name of the file owner product
194 EDR_FILE_UTIL_PUB.GET_ATTRIBUTE(l_event_key, 'ATTRIBUTE1', l_product);
195 wf_log_pkg.string(6, 'UPLOAD_XSL','PRODUCT ' ||l_product);
196 wf_engine.setitemattrtext(p_itemtype, p_itemkey, 'PRODUCT', l_product);
197
198 -- GET THE EVENT STATUS FROM WORKFLOW
199 L_EVENT_STATUS := WF_ENGINE.GETITEMATTRTEXT(P_ITEMTYPE,P_ITEMKEY,'FILE_STATUS');
200 WF_LOG_PKG.STRING(6, 'UPLOAD_RTF','EVENT STATUS'||L_EVENT_STATUS);
201
202 --IF THE STATUS IS SUCCESS ONLY THEN UPLOAD IT TO THE DATABASE
203
204
205 IF (L_EVENT_STATUS = l_success_status OR L_EVENT_STATUS = l_no_approval_status ) THEN
206
207 -- GET THE FILE CATEGORY ID
208 SELECT CATEGORY_ID INTO L_CATEGORY_ID
209 FROM
210 FND_DOCUMENT_CATEGORIES
211 WHERE
212 NAME = L_CATEGORY_NAME;
213
214 -- GET THE DOCUMENT ID FROM FND SEQUENCE CURSOR
215 OPEN C_GET_ID;
216 FETCH C_GET_ID INTO L_ATTACHED_DOCUMENT_ID;
217 CLOSE C_GET_ID;
218 -- CREATE A ROW IN FND_ATTACHED_DOCUMENTS
219 FND_ATTACHED_DOCUMENTS_PKG.INSERT_ROW(
220 X_ROWID=>L_ROW_ID,
221 X_ATTACHED_DOCUMENT_ID=>L_ATTACHED_DOCUMENT_ID,
222 X_DOCUMENT_ID=>L_FND_DOCUMENT_ID,
223 X_CREATION_DATE=>SYSDATE,
224 X_CREATED_BY=>FND_GLOBAL.USER_ID,
225 X_LAST_UPDATE_DATE=>SYSDATE,
226 X_LAST_UPDATED_BY=>FND_GLOBAL.USER_ID,
227 X_LAST_UPDATE_LOGIN=>FND_GLOBAL.USER_ID,
228 X_SEQ_NUM=>1,
229 X_ENTITY_NAME=>'EDR_XSLFO_TEMPLATE',
230 X_COLUMN1=>NULL,
231 X_PK1_VALUE=>L_EVENT_KEY,
232 X_PK2_VALUE=>NULL,
233 X_PK3_VALUE=>NULL,
234 X_PK4_VALUE=>NULL,
235 X_PK5_VALUE=>NULL,
236 X_AUTOMATICALLY_ADDED_FLAG=>'N',
237 X_DATATYPE_ID=>6,
238 X_CATEGORY_ID=>L_CATEGORY_ID,
239 X_SECURITY_TYPE=>1,
240 X_SECURITY_ID=>-1,
241 X_PUBLISH_FLAG=>'N',
242 X_STORAGE_TYPE=>1,
243 X_USAGE_TYPE=>'S',
244 X_LANGUAGE=>USERENV('LANG'),
245 X_DESCRIPTION=>'XSL FO TEMPLATE',
246 X_FILE_NAME=>L_FILE_NAME,
247 X_MEDIA_ID=>L_MEDIA_ID,
248 X_DOC_ATTRIBUTE_CATEGORY=>NULL,
249 X_DOC_ATTRIBUTE1=>NULL,
250 X_DOC_ATTRIBUTE2=>NULL,
251 X_DOC_ATTRIBUTE3=>NULL,
252 X_DOC_ATTRIBUTE4=>NULL,
253 X_DOC_ATTRIBUTE5=>NULL,
254 X_DOC_ATTRIBUTE6=>NULL,
255 X_DOC_ATTRIBUTE7=>NULL,
256 X_DOC_ATTRIBUTE8=>NULL,
257 X_DOC_ATTRIBUTE9=>NULL,
258 X_DOC_ATTRIBUTE10=>NULL,
259 X_CREATE_DOC=>'N');
260
261 -- CALL THE JSP TO CREATE AN XSL FO IN THE FND LOB TABLES.
262 WF_LOG_PKG.STRING(6, 'UPLOAD_RTF','SENDING REQUEST FOR XSL CONVERSION TO JSP');
263
264 BEGIN
265
266 L_AGENT := FND_PROFILE.VALUE('APPS_JSP_AGENT');
267
268 -- Bug 4450651 Start
269 l_src_req := null;
270 l_src_req := EDR_XDOC_UTIL_PKG.GET_SERVICE_TICKET_STRING(EDR_CONSTANTS_GRP.g_service_name);
271
272 L_HTTP_URL := L_AGENT || '/OA_HTML/jsp/edr/iSignPublisherHandler.jsp?eventId=' || L_EVENT_KEY
273 || '&' || 'mediaId='||L_MEDIA_ID || '&' || 'repository=ISIGN'|| '&' || 'src_req=' || l_src_req;
274
275 WF_LOG_PKG.STRING(6, 'UPLOAD_RTF','URL' || L_HTTP_URL);
276 -- Bug 4450651 end
277
278
279 --Bug 3950047 : Start
280 --Call EDR UTL_HTTP wrapper for using HTTP
281 L_HTTP_RESPONSE := EDR_XDOC_UTIL_PKG.REQUEST_HTTP(P_REQUEST_URL=> L_HTTP_URL);
282 --Bug 3950047 : End
283
284 EXCEPTION WHEN OTHERS THEN
285 BEGIN
286 WF_LOG_PKG.STRING(6, 'UPLOAD_RTF','EXCEPTION IN GETTING RESPONSE' || SQLERRM );
287 RAISE;
288 END;
289
290 END;
291
292 WF_LOG_PKG.STRING(6, 'UPLOAD_RTF','RESPONSE FROM JSP' || L_HTTP_RESPONSE);
293
294 -- READ THE STATUS FROM JSP RESPONSE
295 --L_POSITION := INSTR(L_HTTP_RESPONSE,'=',1);
296 --L_HTTP_RESPONSE := SUBSTR(L_HTTP_RESPONSE,L_POSITION+1);
297 --L_POSITION :=INSTR(L_HTTP_RESPONSE,';',1);
298 --L_STATUS := SUBSTR(L_HTTP_RESPONSE,1,L_POSITION-1);
299
300 L_STATUS := UPPER(TRIM(L_HTTP_RESPONSE));
301 WF_LOG_PKG.STRING(6, 'UPLOAD_RTF','STATUS ' || L_STATUS);
302
303 --Check if UTL_HTTP request returned with JSp Not found message.
304 IF (INSTR(SUBSTR(L_HTTP_RESPONSE,1,1024), 'FileNotFoundException', 1) > 0) THEN
305 RAISE L_PAGENOTFOUND;
306 END IF;
307
308 -- THE JSP RETURNS EITHER SUCCESS OR FAILURE AS STATUS CODE AND GIVE APPROPRIATE MESSAGES.
309 IF NOT (INSTR(L_STATUS, L_SUCCESS_STATUS, 1) > 0) THEN
310 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_TEMPLATE_FAILURE');
311 FND_MESSAGE.SET_TOKEN('ERROR_MSG',L_STATUS);
312 L_UPLOAD_STATUS := FND_MESSAGE.GET();
313 ELSE
314 L_UPLOAD_STATUS := FND_MESSAGE.GET_STRING('EDR','EDR_FILES_TEMPLATE_SUCCESS');
315 END IF;
316
317 ELSIF (L_EVENT_STATUS = 'REJECTED') THEN
318 L_UPLOAD_STATUS := FND_MESSAGE.GET_STRING('EDR','EDR_FILES_APPROVAL_REJECTION');
319
320 ELSE
321 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_TEMPLATE_FAILURE');
322 FND_MESSAGE.SET_TOKEN('ERROR_MSG','WF EVENT FAILED');
323 L_UPLOAD_STATUS := FND_MESSAGE.GET();
324 END IF;
325
326 END IF;
327
328 WF_ENGINE.SETITEMATTRTEXT(P_ITEMTYPE, P_ITEMKEY, 'UPLOAD_STATUS',L_UPLOAD_STATUS);
329 P_RESULTOUT := L_RETURN_STATUS;
330
331 EXCEPTION
332 WHEN L_PAGENOTFOUND THEN
333 BEGIN
334 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_TEMPLATE_HTTPFAILURE');
335 FND_MESSAGE.SET_TOKEN('ERROR_MSG','JSP NOT FOUND');
336 L_UPLOAD_STATUS := FND_MESSAGE.GET();
337 WF_ENGINE.SETITEMATTRTEXT(P_ITEMTYPE, P_ITEMKEY, 'UPLOAD_STATUS',L_UPLOAD_STATUS);
338 P_RESULTOUT := L_RETURN_STATUS;
339 END;
340 -- Bug 3950047 : Start
341 -- Improved Error Handling
342 WHEN PROFILE_ERROR THEN
343 BEGIN
344 FND_MESSAGE.SET_NAME('EDR','EDR_PROFILE_CHECK_FAILURE');
345 FND_MESSAGE.SET_TOKEN('PROFILENAME','FND_DB_WALLET_DIR');
346 L_UPLOAD_STATUS := FND_MESSAGE.GET();
347 WF_ENGINE.SETITEMATTRTEXT(P_ITEMTYPE,P_ITEMKEY, 'UPLOAD_STATUS', L_UPLOAD_STATUS);
348 P_RESULTOUT := L_RETURN_STATUS;
349 END;
350 WHEN REQUEST_FAIL THEN
351 BEGIN
352 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_TEMPLATE_HTTPFAILURE');
353 FND_MESSAGE.SET_TOKEN('ERROR_MSG','HTTP REQUEST FAILED');
354 L_UPLOAD_STATUS := FND_MESSAGE.GET();
355 WF_ENGINE.SETITEMATTRTEXT(P_ITEMTYPE, P_ITEMKEY, 'UPLOAD_STATUS',L_UPLOAD_STATUS);
356 P_RESULTOUT := L_RETURN_STATUS;
357 END;
358 WHEN INIT_FAIL THEN
359 BEGIN
360 FND_MESSAGE.SET_NAME('EDR','EDR_FILES_TEMPLATE_HTTPFAILURE');
361 FND_MESSAGE.SET_TOKEN('ERROR_MSG','UTL_HTTP INIT FAILED');
362 L_UPLOAD_STATUS := FND_MESSAGE.GET();
363 WF_ENGINE.SETITEMATTRTEXT(P_ITEMTYPE, P_ITEMKEY, 'UPLOAD_STATUS',L_UPLOAD_STATUS);
364 P_RESULTOUT := L_RETURN_STATUS;
365 END;
366 WHEN OTHERS THEN
367 BEGIN
368 L_UPLOAD_STATUS := SQLERRM;
369 WF_ENGINE.SETITEMATTRTEXT(P_ITEMTYPE, P_ITEMKEY, 'UPLOAD_STATUS',L_UPLOAD_STATUS);
370 P_RESULTOUT := L_RETURN_STATUS;
371 END;
372 -- Bug 3950047 : End
373
374 END UPLOAD_TEMPLATE;
375
376 END EDR_TEMPLATE_SUBS;
[Home] [Help]