1 PACKAGE BODY EDR_EINITIALS_PVT AS
2 /* $Header: EDRVINTB.pls 120.5.12000000.1 2007/01/18 05:56:15 appldev ship $ */
3
4 --This procedure is aimed at fetching the workflow attribtues values for the
5 --specified attribute names associated with the item type and item key.
6 PROCEDURE GET_WF_ATTRIBUTES(P_ITEMTYPE IN VARCHAR2,
7 P_ITEMKEY IN VARCHAR2,
8 P_PARAM_NAMES IN FND_TABLE_OF_VARCHAR2_255,
9 X_PARAM_VALUES OUT NOCOPY FND_TABLE_OF_VARCHAR2_255)
10
11 IS
12
13 L_PARAM_VALUE VARCHAR2(4000);
14
15 BEGIN
16
17 X_PARAM_VALUES := FND_TABLE_OF_VARCHAR2_255();
18
19 --For each parameter specified obtain the parameter value.
20 FOR i IN 1..P_PARAM_NAMES.COUNT LOOP
21 X_PARAM_VALUES.EXTEND;
22 L_PARAM_VALUE := WF_ENGINE.GETITEMATTRTEXT(P_ITEMTYPE, P_ITEMKEY,P_PARAM_NAMES(i), TRUE);
23
24 --If the parameter name is the requester of the ERES transaction, then
25 --obtain his display name as the attribute value.
26 IF(P_PARAM_NAMES(i) = '#WF_SIGN_REQUESTER') THEN
27 X_PARAM_VALUES(i) := EDR_UTILITIES.GETUSERDISPLAYNAME(L_PARAM_VALUE);
28 ELSE
29 X_PARAM_VALUES(i) := L_PARAM_VALUE;
30 END IF;
31 END LOOP;
32
33 END GET_WF_ATTRIBUTES;
34 -- Bug 5158510 : start
35 -- returns wf text item attribute
36 FUNCTION GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype varchar2, p_itemkey varchar2,
37 p_attname varchar2)
38 RETURN VARCHAR2
39 IS
40 BEGIN
41
42 return WF_ENGINE.GETITEMATTRTEXT(p_itemtype, p_itemkey,p_attname, TRUE);
43 END GET_WF_ITEM_ATTRIBUTE_TEXT;
44 -- Bug 5158510 : end
45
46 --This procedure is used to obtain the e-record details associated with the specified workflow item type
47 --and item key.
48 PROCEDURE GET_ERECORD_DETAILS(P_ITEMTYPE IN VARCHAR2,
49 P_ITEMKEY IN VARCHAR2,
50 P_PARAM_NAMES IN FND_TABLE_OF_VARCHAR2_255,
51 X_PARAM_VALUES OUT NOCOPY FND_TABLE_OF_VARCHAR2_255,
52 X_ERECORD_TEXT OUT NOCOPY CLOB)
53 IS
54
55 L_COUNT NUMBER;
56 L_ERECORD_ID NUMBER;
57 BEGIN
58
59 --Fetch the attribute values from workflow.
60 GET_WF_ATTRIBUTES(P_ITEMTYPE => P_ITEMTYPE,
61 P_ITEMKEY => P_ITEMKEY,
62 P_PARAM_NAMES => P_PARAM_NAMES,
63 X_PARAM_VALUES => X_PARAM_VALUES);
64
65 --Obtain the e-record ID value in number format.
66 L_ERECORD_ID := TO_NUMBER(WF_ENGINE.GETITEMATTRTEXT(P_ITEMTYPE,P_ITEMKEY,EDR_CONSTANTS_GRP.G_ERECORD_ID_ATTR,TRUE),'999999999999.999999');
67
68 --Set the secure context.
69 EDR_CTX_PKG.SET_SECURE_ATTR;
70
71 --Fetch the e-record document.
72 SELECT PSIG_DOCUMENT INTO X_ERECORD_TEXT
73 FROM EDR_PSIG_DOCUMENTS
74 WHERE DOCUMENT_ID = L_ERECORD_ID;
75
76 --Adjust the CLOB document for rendering in the OA page.
77 --Primarily we would be converting all instances of '<' to '<' and '>' to '>'.
78 if X_ERECORD_TEXT IS NOT NULL AND DBMS_LOB.GETLENGTH(X_ERECORD_TEXT) > 0 then
79
80 X_ERECORD_TEXT := EDR_UTILITIES.ADJUST_CLOB_FOR_DISPLAY(P_PAYLOAD => X_ERECORD_TEXT,
81 P_PAYLOAD_TYPE => 'ERECORD');
82 end if;
83
84 --Unset the secure context.
85 EDR_CTX_PKG.UNSET_SECURE_ATTR;
86
87 END GET_ERECORD_DETAILS;
88
89
90 --This procedure is used to obtain the e-record details associated with the specified ERES process ID.
91 PROCEDURE GET_ERECORD_DETAILS(P_PROCESS_ID IN VARCHAR2,
92 P_PARAM_NAMES IN FND_TABLE_OF_VARCHAR2_255,
93 X_ITEMTYPE OUT NOCOPY VARCHAR2,
94 X_ITEMKEY OUT NOCOPY VARCHAR2,
95 X_PARAM_VALUES OUT NOCOPY FND_TABLE_OF_VARCHAR2_255,
96 X_ERECORD_COUNT OUT NOCOPY NUMBER,
97 X_ESIGN_COMPLETED OUT NOCOPY VARCHAR2,
98 X_ERECORD_TEXT OUT NOCOPY CLOB)
99 IS
100
101 L_ERECORD_STATUS VARCHAR2(100);
102
103 BEGIN
104
105 --Fetch the number of e-records associated with the specified ERES process ID.
106 SELECT COUNT(*) INTO X_ERECORD_COUNT FROM EDR_PROCESS_ERECORDS_T WHERE ERES_PROCESS_ID = P_PROCESS_ID;
107
108 --If more than one e-record is present, then an invalid process ID parameter has been used.
109 --Hence just return.
110 IF X_ERECORD_COUNT > 1 OR X_ERECORD_COUNT = 0 THEN
111
112 RETURN;
113
114 END IF;
115
116
117 --Obtain the status of the e-record.
118 SELECT STATUS INTO L_ERECORD_STATUS FROM EDR_PROCESS_ERECORDS_T WHERE ERES_PROCESS_ID = P_PROCESS_ID;
119
120 --Set the ESIGN completed flag based on the e-record status.
121 IF L_ERECORD_STATUS IS NOT NULL THEN
122
123 X_ESIGN_COMPLETED := 'Y';
124 RETURN;
125
126 ELSE
127
128 X_ESIGN_COMPLETED := 'N';
129
130 END IF;
131
132 --Fetch the workflow item type and item key values.
133 SELECT WF_ITEM_TYPE,WF_ITEM_KEY INTO X_ITEMTYPE,X_ITEMKEY
134 FROM EDR_PROCESS_ERECORDS_T
135 WHERE ERES_PROCESS_ID = P_PROCESS_ID;
136
137 --Get the e-record details.
138 GET_ERECORD_DETAILS
139 (P_ITEMTYPE => X_ITEMTYPE,
140 P_ITEMKEY => X_ITEMKEY,
141 P_PARAM_NAMES => P_PARAM_NAMES,
142 X_PARAM_VALUES => X_PARAM_VALUES,
143 X_ERECORD_TEXT => X_ERECORD_TEXT);
144
145 END GET_ERECORD_DETAILS;
146
147
148
149 --This private procedure validates the approver details passed as API parameters.
150 PROCEDURE VALIDATE_APPROVER(P_ROLE_NAME IN VARCHAR2,
151 P_SIGNER_NAME IN VARCHAR2,
152 P_SIGNER_PASSWORD IN VARCHAR2,
153 P_SIGNER_RESPONSE IN VARCHAR2,
154 X_IS_APPROVER_VALID OUT NOCOPY VARCHAR2)
155
156 IS
157
158 i NUMBER;
159 L_ROLE_USERS WF_DIRECTORY.USERTABLE;
160 L_IS_APPROVER_VALID BOOLEAN;
161
162 BEGIN
163
164
165 --Initials the flags.
166 X_IS_APPROVER_VALID := 'Y';
167
168 L_IS_APPROVER_VALID := TRUE;
169
170
171 --Validate the approver details only if the signer response is not 'DEFER' and signer response is not null.
172 IF INSTR(P_ROLE_NAME,'#') = 1 THEN
173 --The role name is a responsibility
174 --Hence strip the '#' character symbol and validate the signer name with the role.
175 L_IS_APPROVER_VALID := WF_DIRECTORY.ISPERFORMER(UPPER(P_SIGNER_NAME),
176 LTRIM(P_ROLE_NAME,'#'));
177
178 ELSIF P_ROLE_NAME IS NOT NULL AND LENGTH(P_ROLE_NAME) > 0 THEN
179 --The role name itself is a signer name
180 --Hence validate the same against the specified signer name.
181 L_IS_APPROVER_VALID := WF_DIRECTORY.ISPERFORMER(UPPER(P_SIGNER_NAME),
182 P_ROLE_NAME);
183 END IF;
184
185 --Identify the invalid approvers and set them into a comma separated string.
186 IF NOT L_IS_APPROVER_VALID THEN
187 X_IS_APPROVER_VALID := 'N';
188 END IF;
189
190 IF (L_IS_APPROVER_VALID) THEN
191 --Validate the login details of the signers.
192 L_IS_APPROVER_VALID := FND_USER_PKG.VALIDATELOGIN(P_SIGNER_NAME, P_SIGNER_PASSWORD);
193
194 --Identify the invalid approvers and set them into a comma separated string.
195 IF NOT L_IS_APPROVER_VALID THEN
196 X_IS_APPROVER_VALID := 'N';
197 END IF;
198 END IF;
199
200 END VALIDATE_APPROVER;
201
202
203 --This procedure is used to post the signature details into evidence store.
204 PROCEDURE POST_SIGNATURE_DETAILS(P_ERECORD_ID IN VARCHAR2,
205 P_ITEMTYPE IN VARCHAR2,
206 P_ITEMKEY IN VARCHAR2,
207 P_SIGNATURE_ID IN NUMBER,
208 P_ROLE_NAME IN VARCHAR2,
209 P_SIGNER_NAME IN VARCHAR2,
210 P_SIGNER_PASSWORD IN VARCHAR2,
211 P_SIGNATURE_SEQUENCE IN NUMBER,
212 P_SIGNER_RESPONSE IN VARCHAR2,
213 P_SIGNER_TYPE IN VARCHAR2,
214 P_SIGNER_COMMENTS IN VARCHAR2,
215 P_SIGNING_REASON IN VARCHAR2,
216 X_IS_APPROVER_VALID OUT NOCOPY VARCHAR2,
217 X_SIGNER_DISPLAY_NAME OUT NOCOPY VARCHAR2)
218
219 IS
220
221 PRAGMA AUTONOMOUS_TRANSACTION;
222
223 i NUMBER;
224 L_ERECORD_ID NUMBER;
225 L_SIGNATURE_ID NUMBER;
226 L_SIGNATURE_SEQUENCE NUMBER;
227 L_ERROR_CODE NUMBER;
228 L_ERROR_MSG VARCHAR2(4000);
229 L_SIGN_PARAMS EDR_PSIG.PARAMS_TABLE;
230 L_RESPONSE_MEANING VARCHAR2(400);
231 L_EVENT_ID NUMBER;
232 L_OVERRIDING_COMMENTS VARCHAR2(4000);
233 L_ORIGINAL_RECIPIENT VARCHAR2(100);
234 UNEXPECTED_ERROR EXCEPTION;
235
236 CURSOR FETCH_OVERRIDING_DETAILS IS
237 SELECT ORIGINAL_RECIPIENT,
238 SIGNATURE_OVERRIDING_COMMENTS
239 FROM EDR_ESIGNATURES
240 WHERE EVENT_ID = L_EVENT_ID
241 AND SIGNATURE_ID = P_SIGNATURE_ID;
242
243 BEGIN
244
245 --Get the e-record ID value in number format.
246 L_ERECORD_ID := TO_NUMBER(P_ERECORD_ID,'999999999999.999999');
247
248 --Get the event ID in number format.
249 L_EVENT_ID := TO_NUMBER(P_ITEMKEY,'999999999999.999999');
250
251 --Validate the approver.
252 VALIDATE_APPROVER(P_ROLE_NAME => P_ROLE_NAME,
253 P_SIGNER_NAME => P_SIGNER_NAME,
254 P_SIGNER_PASSWORD => P_SIGNER_PASSWORD,
255 P_SIGNER_RESPONSE => P_SIGNER_RESPONSE,
256 X_IS_APPROVER_VALID => X_IS_APPROVER_VALID);
257
258 --Post the signature details only if the approver is valid.
259 IF X_IS_APPROVER_VALID = 'Y' THEN
260
261 --Fetch the signature sequence in number format.
262 L_SIGNATURE_SEQUENCE := P_SIGNATURE_SEQUENCE;
263
264 --Fetch the signature ID in number format.
265 L_SIGNATURE_ID := P_SIGNATURE_ID;
266
267
268 --Check if the role name is null or if it contains a responsibility.
269 IF P_ROLE_NAME IS NULL OR INSTR(P_ROLE_NAME,'#') = 1 THEN
270
271 --Update the signer details in the EDR_ESIGNATURES table.
272 UPDATE EDR_ESIGNATURES
273 SET USER_NAME = P_SIGNER_NAME,
274 SIGNATURE_TYPE = P_SIGNER_TYPE,
275 SIGNATURE_TIMESTAMP = SYSDATE,
276 SIGNATURE_STATUS = P_SIGNER_RESPONSE,
277 SIGNER_COMMENTS = P_SIGNER_COMMENTS,
278 ORIGINAL_RECIPIENT = P_SIGNER_NAME,
279 SIGNATURE_REASON_CODE = P_SIGNING_REASON
280
281 WHERE SIGNATURE_ID = L_SIGNATURE_ID;
282
283
284 --Since the role name is either null or contains a responsibility,
285 --we need to first request signature in EDR_PSIG_DETAILS before posting the signer response
286 --details.
287 EDR_PSIG.REQUESTSIGNATURE(P_DOCUMENT_ID => L_ERECORD_ID,
288 P_USER_NAME => UPPER(P_SIGNER_NAME),
289 P_ORIGINAL_RECIPIENT => NULL,
290 P_OVERRIDING_COMMENTS => NULL,
291 P_SIGNATURE_SEQUENCE => L_SIGNATURE_SEQUENCE,
292 P_ADHOC_STATUS => NULL,
293 P_SIGNATURE_ID => L_SIGNATURE_ID,
294 P_ERROR => L_ERROR_CODE,
295 P_ERROR_MSG => L_ERROR_MSG);
296
297 --If the error code is not null then an unexpected error has occurred.
298 --Hence raise an exception.
299 IF L_ERROR_CODE IS NOT NULL THEN
300 RAISE UNEXPECTED_ERROR;
301 END IF;
302
303 --Fetch the signer response meaning from FND LOOKUPS.
304 EDR_STANDARD.GET_MEANING('EDR_SIGN_RESPONSE_TYPE', P_SIGNER_RESPONSE,L_RESPONSE_MEANING);
305
306 --Post the signature details into evidence store.
307 EDR_PSIG.POSTSIGNATURE(P_DOCUMENT_ID => L_ERECORD_ID,
308 P_EVIDENCE_STORE_ID => NULL,
309 P_USER_NAME => UPPER(P_SIGNER_NAME),
310 P_USER_RESPONSE => L_RESPONSE_MEANING,
311 P_ORIGINAL_RECIPIENT => NULL,
312 P_OVERRIDING_COMMENTS => NULL,
313 P_SIGNATURE_ID => L_SIGNATURE_ID,
314 P_ERROR => L_ERROR_CODE,
315 P_ERROR_MSG => L_ERROR_MSG);
316
317 --If the error code is not null then an unexpected error has occurred.
318 --Hence raise an exception.
319 IF L_ERROR_CODE IS NOT NULL THEN
320 RAISE UNEXPECTED_ERROR;
321 END IF;
322
323 ELSE
324
325 --Update the signer response in EDR_ESIGNATURES table.
326 UPDATE EDR_ESIGNATURES
327 SET SIGNATURE_TYPE = P_SIGNER_TYPE,
328 SIGNATURE_TIMESTAMP = SYSDATE,
329 SIGNATURE_STATUS = P_SIGNER_RESPONSE,
330 SIGNER_COMMENTS = P_SIGNER_COMMENTS,
331 SIGNATURE_REASON_CODE = P_SIGNING_REASON
332
333 WHERE SIGNATURE_ID = L_SIGNATURE_ID;
334
335
336 --Fetch the signer response meaning from FND LOOKUPS.
337 EDR_STANDARD.GET_MEANING('EDR_SIGN_RESPONSE_TYPE', P_SIGNER_RESPONSE,L_RESPONSE_MEANING);
338
339 --Fetch the overring details from edr_esignatures table.
340 OPEN FETCH_OVERRIDING_DETAILS;
341 FETCH FETCH_OVERRIDING_DETAILS INTO L_ORIGINAL_RECIPIENT,L_OVERRIDING_COMMENTS;
342 CLOSE FETCH_OVERRIDING_DETAILS;
343
344 EDR_PSIG.POSTSIGNATURE(P_DOCUMENT_ID => L_ERECORD_ID,
345 P_EVIDENCE_STORE_ID => NULL,
346 P_USER_NAME => upper(P_SIGNER_NAME),
347 P_USER_RESPONSE => L_RESPONSE_MEANING,
348 P_ORIGINAL_RECIPIENT => L_ORIGINAL_RECIPIENT,
349 P_OVERRIDING_COMMENTS => NULL,
350 P_SIGNATURE_ID => L_SIGNATURE_ID,
351 P_ERROR => L_ERROR_CODE,
352 P_ERROR_MSG => L_ERROR_MSG);
353
354 IF L_ERROR_CODE IS NOT NULL THEN
355 RAISE UNEXPECTED_ERROR;
356 END IF;
357 END IF;
358
359 --Set the signature parameters.
360 L_SIGN_PARAMS(1).PARAM_NAME := 'WF_SIGNER_TYPE';
361 EDR_STANDARD.GET_MEANING('EDR_SIGNATURE_TYPES',P_SIGNER_TYPE,L_SIGN_PARAMS(1).PARAM_VALUE);
362 EDR_STANDARD.GET_MEANING('EDR_SIGN_PARAMS_DISPLAY_TYPE','WF_SIGNER_TYPE',L_SIGN_PARAMS(1).PARAM_DISPLAYNAME);
363
364 L_SIGN_PARAMS(2).PARAM_NAME := 'SIGNERS_COMMENT';
365 L_SIGN_PARAMS(2).PARAM_VALUE := P_SIGNER_COMMENTS;
366 EDR_STANDARD.GET_MEANING('EDR_SIGN_PARAMS_DISPLAY_TYPE','SIGNERS_COMMENT',L_SIGN_PARAMS(2).PARAM_DISPLAYNAME);
367
368 L_SIGN_PARAMS(3).PARAM_NAME := 'REASON_CODE';
369 EDR_STANDARD.GET_MEANING('EDR_SIGN_REASON_TYPE',P_SIGNING_REASON,L_SIGN_PARAMS(3).PARAM_VALUE);
370 EDR_STANDARD.GET_MEANING('EDR_SIGN_PARAMS_DISPLAY_TYPE','REASON_CODE',L_SIGN_PARAMS(3).PARAM_DISPLAYNAME);
371
372 --Post the signature parameters into evidence store.
373 EDR_PSIG.POSTSIGNATUREPARAMETER(P_SIGNATURE_ID => L_SIGNATURE_ID,
374 P_PARAMETERS => L_SIGN_PARAMS,
375 P_ERROR => L_ERROR_CODE,
376 P_ERROR_MSG => L_ERROR_MSG);
377 --If the error code is not null then an unexpected error has occurred.
378 --Hence raise an exception.
379 IF L_ERROR_CODE IS NOT NULL THEN
380 RAISE UNEXPECTED_ERROR;
381 END IF;
382 END IF;
383
384 X_SIGNER_DISPLAY_NAME := EDR_UTILITIES.GETUSERDISPLAYNAME(UPPER(P_SIGNER_NAME));
385
386 --Commit the transaction.
387 COMMIT;
388
389 EXCEPTION
390 WHEN UNEXPECTED_ERROR THEN
391 ROLLBACK;
392 --Diagnostics Start
393 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
394 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',NVL(L_ERROR_MSG,SQLERRM));
395 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_EINITIALS_PVT');
396 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','POST_SIGNATURE_DETAILS');
397 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
398 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
399 'edr.plsql.EDR_EINITIALS_PVT.POST_SIGNATURE_DETAILS',
400 FALSE
401 );
402 end if;
403 --Diagnostics End
404 APP_EXCEPTION.RAISE_EXCEPTION;
405
406 WHEN OTHERS THEN
407 ROLLBACK;
408 --Diagnostics Start
409 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
410 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
411 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_EINITIALS_PVT');
412 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','POST_SIGNATURE_DETAILS');
413 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
414 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
415 'edr.plsql.EDR_EINITIALS_PVT.POST_SIGNATURE_DETAILS',
416 FALSE
417 );
418 end if;
419 --Diagnostics End
420 APP_EXCEPTION.RAISE_EXCEPTION;
421
422 END POST_SIGNATURE_DETAILS;
423
424
425 --This procedure is used to move the workflow blocked activity appropriately to complete the
426 --signature process.
427 PROCEDURE COMPLETE_SIGNATURE(P_ITEMTYPE IN VARCHAR2,
428 P_ITEMKEY IN VARCHAR2,
429 P_ERECORD_ID IN VARCHAR2,
430 P_UPDATE_ORES_TEMP_TABLES IN VARCHAR2,
431 X_ERECORD_STATUS OUT NOCOPY VARCHAR2)
432
433 IS
434
435 PRAGMA AUTONOMOUS_TRANSACTION;
436
437 --This variable stores the e-record ID in Number format.
438 L_ERECORD_ID NUMBER;
439
440 --This variable is used to store the event ID in number format.
441 L_EVENT_ID NUMBER;
442
443 --This cursor is used to fetch the e-record status value for the specified event_id.
444 CURSOR FETCH_ERECORD_STATUS IS
445 SELECT ERECORD_SIGNATURE_STATUS
446 FROM EDR_ERECORDS
447 WHERE EVENT_ID = L_EVENT_ID;
448
449 BEGIN
450
451 --Get the e-record ID value in number format.
452 L_ERECORD_ID := TO_NUMBER(P_ERECORD_ID,'999999999999.999999');
453
454 --Get the event ID in number format.
455 L_EVENT_ID := TO_NUMBER(P_ITEMKEY,'999999999999.999999');
456
457 --Set the e-record ID in workflow.
458 WF_ENGINE.SETITEMATTRNUMBER(P_ITEMTYPE,P_ITEMKEY,'EDR_PSIG_DOC_ID',L_ERECORD_ID);
459
460 --Move the workflow blocked activity as required.
461 FND_WF_ENGINE.COMPLETEACTIVITY(P_ITEMTYPE, P_ITEMKEY, 'LITE_MODE', 'DONE');
462
463
464 --Get the e-record ID value in number format.
465 L_ERECORD_ID := TO_NUMBER(P_ERECORD_ID,'999999999999.999999');
466
467 --Obtain the e-record status value.
468 OPEN FETCH_ERECORD_STATUS;
469 FETCH FETCH_ERECORD_STATUS INTO X_ERECORD_STATUS;
470 CLOSE FETCH_ERECORD_STATUS;
471
472 --Update the ORES temp tables based on the flag.
473 IF P_UPDATE_ORES_TEMP_TABLES = FND_API.G_TRUE THEN
474 UPDATE EDR_PROCESS_ERECORDS_T
475 SET STATUS = X_ERECORD_STATUS
476 WHERE ERECORD_ID = L_ERECORD_ID;
477 END IF;
478
479 --Commit the transaction
480 COMMIT;
481
482 EXCEPTION
483 WHEN OTHERS THEN
484 ROLLBACK;
485 --Diagnostics Start
486 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
487 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
488 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_EINITIALS_PVT');
489 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','COMPLETE_SIGNATURE');
490 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
491 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
492 'edr.plsql.EDR_EINITIALS_PVT.COMPLETE_SIGNATURE',
493 FALSE
494 );
495 end if;
496 --Diagnostics End
497 APP_EXCEPTION.RAISE_EXCEPTION;
498
499 END COMPLETE_SIGNATURE;
500
501
502 --This procedure cancels the signature process.
503 PROCEDURE CANCEL_SIGNATURE(P_ITEMTYPE IN VARCHAR2,
504 P_ITEMKEY IN VARCHAR2,
505 P_ERECORD_ID IN VARCHAR2,
506 P_UPDATE_ORES_TEMP_TABLES IN VARCHAR2)
507 IS
508
509 L_ERECORD_ID NUMBER;
510 -- Bug 5158510 : start
511 l_signature_mode VARCHAR2(80);
512 -- Bug 5158510 : end
513
514 PRAGMA AUTONOMOUS_TRANSACTION;
515
516 BEGIN
517
518 --Get the e-record ID value in number format.
519 L_ERECORD_ID := TO_NUMBER(P_ERECORD_ID,'999999999999.999999');
520
521 --Set the e-record ID in workflow.
522 WF_ENGINE.SETITEMATTRNUMBER(P_ITEMTYPE,P_ITEMKEY,'EDR_PSIG_DOC_ID',L_ERECORD_ID);
523
524 -- Bug 5158510 : start
525 l_signature_mode := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,EDR_CONSTANTS_GRP.G_SIGNATURE_MODE);
526 IF(EDR_CONSTANTS_GRP.G_ERES_LITE = l_signature_mode) then
527 --Cancel the signature process by moving the blocked activity as required.
528 FND_WF_ENGINE.COMPLETEACTIVITY(P_ITEMTYPE, P_ITEMKEY, 'LITE_MODE', 'CANCEL');
529 ELSIF(EDR_CONSTANTS_GRP.G_ERES_REGULAR = l_signature_mode) then
530 --Cancel the signature process by moving the blocked activity as required.
531 FND_WF_ENGINE.COMPLETEACTIVITY(P_ITEMTYPE, P_ITEMKEY, 'PSIG_ESIGN_SIGNER_LIST', 'PAGE_CANCEL');
532 END IF;
533
534
535 IF P_UPDATE_ORES_TEMP_TABLES = FND_API.G_TRUE THEN
536 UPDATE EDR_PROCESS_ERECORDS_T
537 SET STATUS = EDR_CONSTANTS_GRP.G_ERROR_STATUS
538 WHERE ERECORD_ID = L_ERECORD_ID;
539
540 UPDATE EDR_ERESMANAGER_T
541 SET OVERALL_STATUS = EDR_CONSTANTS_GRP.G_ERROR_STATUS
542 WHERE ERES_PROCESS_ID IN (SELECT ERES_PROCESS_ID
543 FROM EDR_PROCESS_ERECORDS_T
544 WHERE ERECORD_ID = L_ERECORD_ID);
545 END IF;
546
547 COMMIT;
548
549 EXCEPTION
550 WHEN OTHERS THEN
551 ROLLBACK;
552 --Diagnostics Start
553 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
554 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
555 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_EINITIALS_PVT');
556 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','CANCEL_SIGNATURE');
557 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
558 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
559 'edr.plsql.EDR_EINITIALS_PVT.CANCEL_SIGNATURE',
560 FALSE
561 );
562 end if;
563 --Diagnostics End
564 APP_EXCEPTION.RAISE_EXCEPTION;
565
566 END CANCEL_SIGNATURE;
567
568
569 END EDR_EINITIALS_PVT;
[Home] [Help]