[Home] [Help]
PACKAGE BODY: APPS.EDR_PSIG_PAGE_FLOW
Source
1 PACKAGE BODY EDR_PSIG_PAGE_FLOW AS
2 /* $Header: EDRESGPB.pls 120.11.12000000.1 2007/01/18 05:52:51 appldev ship $ */
3
4 --global variables
5 G_YES CONSTANT varchar2(15) := 'COMPLETE:Y';
6 G_NO CONSTANT varchar2(15) := 'COMPLETE:N';
7 G_PRESUCCESS_STATUS CONSTANT varchar2(25) := 'PRESUCCESS';
8 G_SUCCESS_STATUS CONSTANT varchar2(25) := 'SUCCESS';
9
10 /*
11 G_SEND_APPROVAL_NONE varchar2(50) := 'COMPLETE:SEND_NONE';
12 G_SEND_APPROVAL_INDIVIDUAL varchar2(50) := 'COMPLETE:SEND_INDIVIDUAL';
13 G_SEND_APPROVAL_FINAL varchar2(50) := 'COMPLETE:SEND_FINAL';
14 */
15
16 /*********************************************************************************
17 *** This procedure is executed when workflow process is completed in offline **
18 *** mode. **
19 *********************************************************************************/
20
21 /********************************************************************************
22 **** Bug#3368868 Starts: Function to get the transaction Status using Erecord id
23 ******************************************************************/
24
25 FUNCTION GET_TRANSACTIONSTATUS (eRecordId NUMBER)
26 RETURN VARCHAR2 IS
27 x_transaction_status VARCHAR2(100);
28 begin
29 SELECT TRANSACTION_STATUS into x_transaction_status
30 FROM EDR_TRANS_ACKN
31 WHERE ERECORD_ID = eRecordId;
32 return (x_transaction_status);
33 end GET_TRANSACTIONSTATUS;
34 -- Bug #3368868 : Ends
35
36 -- Bug 3916445 : Starts
37 -- Introducing new wrapper Apis for workflow and notification Apis to get and
38 -- set item attributes (number and text)
39
40 -- returns wf number item attribute
41 FUNCTION GET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype varchar2, p_itemkey varchar2,
42 p_attname varchar2)
43 RETURN NUMBER
44 IS
45 BEGIN
46
47 return wf_engine.getitemattrnumber(p_itemtype, p_itemkey,p_attname, TRUE);
48 END GET_WF_ITEM_ATTRIBUTE_NUMBER;
49
50 -- returns wf text item attribute
51 FUNCTION GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype varchar2, p_itemkey varchar2,
52 p_attname varchar2)
53 RETURN VARCHAR2
54 IS
55 BEGIN
56
57 return wf_engine.getitemattrtext(p_itemtype, p_itemkey,p_attname, TRUE);
58 END GET_WF_ITEM_ATTRIBUTE_TEXT;
59
60 -- sets wf number item attribute
61 PROCEDURE SET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype varchar2, p_itemkey varchar2,
62 p_attname varchar2, p_attvalue NUMBER)
63 IS
64 BEGIN
65 wf_engine.setitemattrnumber(p_itemtype,p_itemkey,p_attname,p_attvalue);
66 EXCEPTION WHEN OTHERS THEN
67 NULL;
68 END SET_WF_ITEM_ATTRIBUTE_NUMBER;
69
70 -- sets wf text item attribute
71 PROCEDURE SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype varchar2, p_itemkey varchar2,
72 p_attname varchar2, p_attvalue varchar2)
73 IS
74 BEGIN
75 wf_engine.setitemattrtext(p_itemtype,p_itemkey,p_attname,p_attvalue);
76 EXCEPTION WHEN OTHERS THEN
77 NULL;
78 END SET_WF_ITEM_ATTRIBUTE_TEXT;
79
80 -- returns workflow notification's number item attribute
81
82 FUNCTION GET_NOTIF_ITEM_ATTR_NUMBER(p_nid NUMBER, p_attname VARCHAR2)
83 RETURN NUMBER
84 IS
85 l_number NUMBER;
86 BEGIN
87 l_number := null;
88 BEGIN
89 l_number := wf_notification.GETATTRNUMBER(p_nid, p_attname);
90 EXCEPTION WHEN OTHERS THEN
91 NULL;
92 END;
93 return l_number;
94 END GET_NOTIF_ITEM_ATTR_NUMBER;
95 -- returns workflow notification's text item attribute
96
97 FUNCTION GET_NOTIF_ITEM_ATTR_TEXT(p_nid NUMBER, p_attname VARCHAR2)
98 RETURN VARCHAR2
99 IS
100 l_text VARCHAR2(1000);
101 BEGIN
102 l_text := null;
103 BEGIN
104 l_text := wf_notification.GETATTRTEXT(p_nid, p_attname);
105 EXCEPTION WHEN OTHERS THEN
106 NULL;
107 END;
108 return l_text;
109 END GET_NOTIF_ITEM_ATTR_TEXT;
110 -- Bug 3916445 : Starts
111
112
113 PROCEDURE EXECUTE_POST_OP_API(
114 p_itemtype IN VARCHAR2,
115 p_itemkey IN VARCHAR2,
116 p_result IN VARCHAR2) IS
117 L_POST_OP_API VARCHAR2(4000) := null;
118 l_sql varchar2(4000);
119 l_occur number;
120 l_last_char VARCHAR2(1);
121 l_trans_status VARCHAR2(100);
122 l_eRecord_id NUMBER := null;
123 BEGIN
124 -- Bug #3368868 : Starts
125 -- Get the transaction status
126
127 -- Bug 3903471 : Start
128 l_eRecord_id := GET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype,p_itemkey,'EDR_PSIG_DOC_ID');
129
130 if l_eRecord_id is NULL THEN
131 l_eRecord_id := TO_NUMBER(GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#ERECORD_ID'),'999999999999.999999');
132 end if;
133
134 l_trans_status := GET_TRANSACTIONSTATUS(l_eRecord_id) ;
135 -- Bug 3903471 : End
136
137 -- Bug #3368868 : Ends
138
139 EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS := p_result;
140 EDR_STANDARD_PUB.G_SIGNATURE_STATUS := p_result;
141 -- Bug #3368868 : Starts
142 -- If transaction status is ERROR do not call post_op_api
143 IF l_trans_status <> 'ERROR' THEN
144 -- Bug #3368868 : Ends
145 L_POST_OP_API := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'POST_OPERATION_API');
146 END IF;
147
148 IF nvl(L_POST_OP_API,'NONE') <> 'NONE' THEN
149 L_POST_OP_API := rtrim(L_POST_OP_API);
150 l_last_char := SUBSTR(L_POST_OP_API,LENGTH(L_POST_OP_API));
151 IF l_last_char <> ';' THEN
152 L_POST_OP_API := L_POST_OP_API ||';';
153 END IF;
154 l_sql:='BEGIN'||FND_GLOBAL.Newline||
155 L_POST_OP_API|| FND_GLOBAL.Newline||
156 'END;'|| FND_GLOBAL.Newline;
157 EXECUTE IMMEDIATE l_SQL;
158 END IF;
159
160 -- Bug 3411859 : Start
161 -- Signature status should be set to null in all cases .
162 EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS := null;
163 EDR_STANDARD_PUB.G_SIGNATURE_STATUS := null;
164 -- Bug 3411859 : End
165
166 EXCEPTION
167 WHEN OTHERS THEN
168 EDR_PSIG_PAGE_FLOW.SIGNATURE_STATUS := null;
169 EDR_STANDARD_PUB.G_SIGNATURE_STATUS := null;
170 WF_CORE.CONTEXT('EDR_PSIG_PAGE_FLOW','Post Operation Proc Excution',p_itemtype,p_itemkey,l_SQL);
171 raise;
172 END;
173
174
175 /************************************************************************
176 *** Function to get event display name from workflow business events ***
177 *** Tables. ***
178 *************************************************************************/
179
180 Function Get_event_disp_name (p_event_id varchar2) return VARCHAR2 IS
181 L_event_name Varchar2(80);
182 L_event_disp_name VARCHAR2(240);
183 BEGIN
184 SELECT event_name into l_event_name
185 FROM EDR_ERECORDS
186 WHERE event_id = p_event_id;
187 select display_name into L_event_disp_name
188 from wf_events_vl
189 where name = l_event_name;
190 return(L_event_disp_name);
191 EXCEPTION WHEN OTHERS THEN
192 WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','Get_event_disp_name',p_event_id);
193 raise;
194 END;
195
196
197
198
199 /************************************************************************
200 *** Following Procedure Returns current page flow process status ****
201 *** It accepts Event ID as in parameter ****
202 *************************************************************************/
203
204 PROCEDURE GET_PAGE_FLOW_STATUS(P_EVENT_ID IN NUMBER ,
205 P_SIGNATURE_STATUS OUT NOCOPY VARCHAR2) IS
206 l_count NUMBER;
207
208 BEGIN
209
210 SELECT count(*) INTO l_count
211 from EDR_ESIGNATURES
212 WHERE EVENT_ID = P_event_id
213 AND SIGNATURE_STATUS <> 'APPROVED';
214
215 IF l_count = 0 THEN -- All Are Approved
216 P_SIGNATURE_STATUS := 'SUCCESS';
217 ELSE -- Not All are Approved
218 SELECT count(*) INTO l_count
219 from EDR_ESIGNATURES
220 WHERE EVENT_ID = p_event_id
221 AND SIGNATURE_STATUS = 'REJECTED';
222 IF l_count > 0 THEN
223 /* Means Some one is rejected */
224 P_SIGNATURE_STATUS := 'REJECTED';
225 ELSE
226 SELECT count(*) INTO l_count
227 from EDR_ESIGNATURES
228 WHERE EVENT_ID = p_event_id
229 AND SIGNATURE_STATUS = 'TIMEDOUT';
230 IF l_count > 0 THEN
231 /* Means Timeout SKARIMIS*/
232 P_SIGNATURE_STATUS := 'TIMEDOUT';
233 ELSE
234 P_SIGNATURE_STATUS := 'PENDING';
235 END IF;
236 END IF; -- Pending or rejected caeses
237 END IF;
238 END GET_PAGE_FLOW_STATUS;
239
240
241
242 --Bug 4577122 : start
243 PROCEDURE GET_PAGE_FLOW_STATUS_NEW(P_EVENT_ID IN NUMBER ,
244 P_VOTING_REGIME IN VARCHAR2,
245 P_SIGNATURE_STATUS OUT NOCOPY VARCHAR2) IS
246 l_count NUMBER;
247
248 --Change related to bug 4577122
249 l_approver_count number;
250 --Change related to bug 4577122
251
252 BEGIN
253
254 --Change related to bug 4577122
255 if (p_voting_regime = ame_util.firstApproverVoting) then
256
257 select count(*) into l_approver_count
258 from edr_esignatures
259 where event_id = p_event_id
260 and signature_status = 'APPROVED';
261
262 if (l_approver_count > 0) then
263 P_SIGNATURE_STATUS := 'SUCCESS';
264 return;
265 end if;
266 end if;
267 --Change related to bug 4577122
268
269 SELECT count(*) INTO l_count
270 from EDR_ESIGNATURES
271 WHERE EVENT_ID = P_event_id
272 AND SIGNATURE_STATUS <> 'APPROVED';
273
274 IF l_count = 0 THEN -- All Are Approved
275 P_SIGNATURE_STATUS := 'SUCCESS';
276 ELSE -- Not All are Approved
277 SELECT count(*) INTO l_count
278 from EDR_ESIGNATURES
279 WHERE EVENT_ID = p_event_id
280 AND SIGNATURE_STATUS = 'REJECTED';
281
282 IF l_count > 0 THEN
283 /* Means Some one is rejected */
284 P_SIGNATURE_STATUS := 'REJECTED';
285 ELSE
286 SELECT count(*) INTO l_count
287 from EDR_ESIGNATURES
288 WHERE EVENT_ID = p_event_id
289 AND SIGNATURE_STATUS = 'TIMEDOUT';
290 IF l_count > 0 THEN
291 /* Means Timeout SKARIMIS*/
292 P_SIGNATURE_STATUS := 'TIMEDOUT';
293 ELSE
294 P_SIGNATURE_STATUS := 'PENDING';
295 END IF;
296 END IF; -- Pending or rejected caeses
297
298 END IF;
299 END GET_PAGE_FLOW_STATUS_NEW;
300 --Bug 4577122 : end
301
302 /*******************************************************************************
303 *** This procedure is associated with EDRESGPF workflow. This code will **
304 *** execute when user clicks on Authenticate Button in list of signer page **
305 *******************************************************************************/
306
307 PROCEDURE AUTHENTICATE_RESPONSE(
308 /* procedure to signature process response in case of Authenticate */
309 p_itemtype IN VARCHAR2,
310 p_itemkey IN VARCHAR2,
311 p_actid IN NUMBER,
312 p_funcmode IN VARCHAR2,
313 p_resultout OUT NOCOPY VARCHAR2
314 )
315 IS
316 BEGIN
317 IF (p_funcmode = 'RUN') THEN
318 /*
319 wf_engine.setitemAttrText(p_itemtype, p_itemkey,'REASON_CODE',null);
320 wf_engine.setitemAttrText(p_itemtype, p_itemkey,'WF_SIGNER_TYPE',null);
321 */
322 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'REASON_CODE','PSIG_NONE');
323 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'SIGNERS_COMMENT',null);
324 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'WF_SIGNER_TYPE','AUTHOR');
325 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'SIGNERS_COMMENT',null);
326
327 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'READ_RESPONSE','N');
328 p_resultout := 'COMPLETE:';
329 END IF;
330 EXCEPTION
331 WHEN OTHERS THEN
332 WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','AUTHENTICATE_RESPONSE',p_itemtype,p_itemkey,'Notified');
333 raise;
334 END;
335 /*******************************************************************************************
336 ***** Update record status to cancel assuming EVENT_ID and Workflow ITEM_KEY is Same ***
337 ********************************************************************************************/
338 PROCEDURE CANCEL_RESPONSE(
339 /* procedure to signature process response in case of Cancel */
340 p_itemtype IN VARCHAR2,
341 p_itemkey IN VARCHAR2,
342 p_actid IN NUMBER,
343 p_funcmode IN VARCHAR2,
344 p_resultout OUT NOCOPY VARCHAR2
345 )
346 IS
347 L_doc_id NUMBER;
348 EDR_PSIG_DOC_ERR EXCEPTION;
349 l_error_num NUMBER;
350 l_ERROR_MESG VARCHAR(2000);
351 --Bug 3634954 : Start
352 --A new variable to hold the erecord id in varchar2 format
353 --Bug 3634954 : End
354 l_erecord_id VARCHAR2(128);
355 BEGIN
356 IF (p_funcmode = 'RUN') THEN
357
358 /* Update workflow STATUS to Cancel */
359
360 UPDATE EDR_ERECORDS
361 SET ERECORD_SIGNATURE_STATUS = 'CANCEL'
362 WHERE EVENT_ID = P_itemkey;
363
364 --Bug 3634954 : Start
365 --The following line is commented, as from now on erecord id will be fetched from the
366 --#ERECORD_ID workflow parameter instead of EDR_PSIG_DOC_ID parameter.
367
368 --Bug 3903471 : Start
369 --However #ERECORD_ID parameter is not used in older versions. Hence to
370 --support backward compatibility a conditional check would be provided.
371
372 l_doc_id := GET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype, p_itemkey,'EDR_PSIG_DOC_ID');
373
374 if l_doc_id is NULL THEN
375
376 l_erecord_id := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,'#ERECORD_ID');
377
378 --Convert erecord id obtained in varchar2 to number format.
379 --To ensure that no problems occur in MLS environments appropriate number format is used.
380
381 l_doc_id := TO_NUMBER(l_erecord_id,'999999999999.999999');
382
383 end if;
384 --Bug 3903471 : End
385
386 --Bug 3634954 : End
387
388
389 IF l_doc_id IS NOT NULL THEN
390 EDR_PSIG.changeDocumentStatus( P_DOCUMENT_ID => l_doc_id,
391 P_STATUS => 'CANCEL',
392 P_ERROR => l_error_num,
393 P_ERROR_MSG => l_error_mesg);
394 --Bug 3207385: Start
395 WF_ENGINE.ADDITEMATTR(itemtype => p_itemtype,
396 itemkey => p_itemkey,
397 aname => EDR_CONSTANTS_GRP.G_FINAL_DOCUMENT_STATUS,
398 text_value => EDR_CONSTANTS_GRP.G_CANCEL_STATUS);
399 --Bug 3207385: End
400 IF l_ERROR_NUM IS NOT NULL THEN
401 RAISE EDR_PSIG_DOC_ERR;
402 END IF;
403 END IF;
404 -- Bug Fix: 3178035
405 -- Modified Page flow status to 'ERROR'
406 -- As per cookbook get transaction status returns one of these
407 -- status ('SUCCESS','ERROR','REJECTED','PENDING','NOACTION')
408 -- based on this we need to send 'ERROR'
409 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_STATUS','ERROR');
410 p_resultout := 'COMPLETE:';
411 END IF;
412 EXCEPTION
413 WHEN EDR_PSIG_DOC_ERR THEN
414 WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','CANCEL_RESPONSE',p_itemtype,p_itemkey,
415 l_error_num,L_ERROR_MESG);
416 raise;
417 WHEN OTHERS THEN
418 WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','CANCEL_RESPONSE',p_itemtype,p_itemkey,'OK');
419 raise;
420 END;
421 /******************************************************************************************
422 *** This is the main procedure which controls total offline flow and response to done ***
423 *** incase of on-line. When user clicks on done button and still some responses are ***
424 *** pending then this procedure verifies deferred mode is allowed or not. if deferred ***
425 *** mode is allowed sets the workflow mode to OFFLINE. ***
426 ******************************************************************************************/
427
428 PROCEDURE PROCESS_RESPONSE(
429 /* procedure to signature process Incase of all OFFLINE cases and ONLINE when user selects Done */
430 p_itemtype IN VARCHAR2,
431 p_itemkey IN VARCHAR2,
432 p_actid IN NUMBER,
433 p_funcmode IN VARCHAR2,
434 p_resultout OUT NOCOPY VARCHAR2
435 )
436 IS
437 L_CURR_MODE VARCHAR2(30);
438 L_DEFER_MODE_ALLOWED VARCHAR2(30);
439 l_EVENT_ID NUMBER;
440
441 L_SIGNATURE_STATUS VARCHAR2(30);
442 L_doc_id NUMBER;
443 EDR_PSIG_DOC_ERR EXCEPTION;
444 l_error_num NUMBER;
445 l_ERROR_MESG VARCHAR(2000);
446 l_ERROR_MSG VARCHAR(4000);
447
448 -- Bug 4213923 : Start
449 l_username VARCHAR2(100);
450 l_overriding_approver VARCHAR2(100);
451 l_overriding_comments VARCHAR2(100);
452 l_signature_id NUMBER;
453 l_esign_id VARCHAR2(100);
454 l_value VARCHAR2(100);
455
456 CURSOR C1(l_document_id NUMBER) IS
457 Select original_recipient from edr_psig_details
458 where document_id = l_document_id and USER_RESPONSE is null;
459 -- Bug 4213923 : End
460
461 --Bug 4577122: Start
462 l_ignore_wfattr_notfound boolean := true;
463 l_voting_regime varchar2(1);
464 --Bug 4577122: End
465
466 BEGIN
467
468 --Bug 4074173 : start
469 L_CURR_MODE := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_CURRENT_MODE');
470 L_DEFER_MODE_ALLOWED := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'DEFERRED');
471 l_EVENT_ID := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_ITEMKEY');
472 --Bug 4074173 : end
473
474 --Bug 4577122: Start
475 l_voting_regime := wf_engine.GetitemAttrText
476 (p_itemtype, p_itemkey,'AME_VOTING_REGIME',
477 l_ignore_wfattr_notfound);
478 --Bug 4577122: End
479
480 IF (p_funcmode = 'RUN') THEN
481
482 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'REASON_CODE','PSIG_NONE');
483 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'SIGNERS_COMMENT',null);
484 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'WF_SIGNER_TYPE','AUTHOR');
485
486 --Bug 4577122: Start
487 --GET_PAGE_FLOW_STATUS(L_EVENT_ID,L_SIGNATURE_STATUS);
488 GET_PAGE_FLOW_STATUS_NEW(L_EVENT_ID,l_voting_regime, L_SIGNATURE_STATUS);
489 --Bug 4577122: End
490
491 IF L_SIGNATURE_STATUS in ('SUCCESS','REJECTED','TIMEDOUT') THEN
492
493 l_doc_id := GET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype, p_itemkey,'EDR_PSIG_DOC_ID');
494
495 --Bug 4577122: Start
496 --clear out the status etc fields of edr_psig_details IFF voting regime is F
497 if (l_voting_regime = ame_util.firstApproverVoting) then
498 edr_psig.clear_pending_signatures(l_doc_id);
499 end if;
500 --Bug 4577122: End
501
502 UPDATE EDR_ERECORDS
503 SET ERECORD_SIGNATURE_STATUS = L_SIGNATURE_STATUS
504 WHERE EVENT_ID = l_event_id;
505
506
507 IF L_SIGNATURE_STATUS = 'SUCCESS' THEN
508
509 EDR_PSIG.closeDocument( P_DOCUMENT_ID => l_doc_id,
510 P_ERROR => l_error_num,
511 P_ERROR_MSG => l_error_mesg);
512
513 --Bug 3207385: Start
514 WF_ENGINE.ADDITEMATTR(itemtype => p_itemtype,
515 itemkey => p_itemkey,
516 aname => EDR_CONSTANTS_GRP.G_FINAL_DOCUMENT_STATUS,
517 text_value => EDR_CONSTANTS_GRP.G_COMPLETE_STATUS);
518 --Bug 3207385: End
519
520 IF l_ERROR_NUM IS NOT NULL THEN
521 RAISE EDR_PSIG_DOC_ERR;
522 END IF;
523 ELSIF L_SIGNATURE_STATUS ='REJECTED' THEN
524 EDR_PSIG.changeDocumentStatus( P_DOCUMENT_ID => l_doc_id,
525 P_STATUS => 'REJECTED',
526 P_ERROR => l_error_num,
527 P_ERROR_MSG => l_error_mesg);
528 --Bug 3207385: Start
529 WF_ENGINE.ADDITEMATTR(itemtype => p_itemtype,
530 itemkey => p_itemkey,
531 aname => EDR_CONSTANTS_GRP.G_FINAL_DOCUMENT_STATUS,
532 text_value => L_SIGNATURE_STATUS);
533 --Bug 3207385: End
534 IF l_ERROR_NUM IS NOT NULL THEN
535 RAISE EDR_PSIG_DOC_ERR;
536 END IF;
537
538 ELSIF L_SIGNATURE_STATUS ='TIMEDOUT' THEN
539 /* Check for the first person timeout. In this case doc_id might be null.
540 so fetch it from PSIG_DOCUMENT_ID */
541 IF l_doc_id is NULL then
542 l_doc_id := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'PSIG_DOCUMENT_ID');
543 END IF;
544
545
546 EDR_PSIG.changeDocumentStatus( P_DOCUMENT_ID => l_doc_id,
547 P_STATUS => 'TIMEDOUT',
548 P_ERROR => l_error_num,
549 P_ERROR_MSG => l_error_mesg);
550 --Bug 3207385: Start
551 WF_ENGINE.ADDITEMATTR(itemtype => p_itemtype,
552 itemkey => p_itemkey,
553 aname => EDR_CONSTANTS_GRP.G_FINAL_DOCUMENT_STATUS,
554 text_value => L_SIGNATURE_STATUS);
555 --Bug 3207385: End
556
557 IF l_ERROR_NUM IS NOT NULL THEN
558 RAISE EDR_PSIG_DOC_ERR;
559 END IF;
560
561 END IF;
562
563 IF L_CURR_MODE = 'OFFLINE' THEN
564 EXECUTE_POST_OP_API
565 (p_itemtype ,
566 p_itemkey ,
567 L_SIGNATURE_STATUS);
568 END IF;
569
570 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_STATUS',L_SIGNATURE_STATUS);
571 IF L_SIGNATURE_STATUS = 'SUCCESS' THEN
572 p_resultout := 'COMPLETE:RESP_DONE_COMPLETED';
573 ELSE
574 p_resultout := 'COMPLETE:PSIG_REJECTED';
575 END IF;
576
577 ELSE -- Still Pending Cases are there
578
579 l_doc_id := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'PSIG_DOCUMENT_ID');
580
581 -- Bug 4213923 : Start
582
583 OPEN C1(l_doc_id);
584
585 --for all users which have not signed yet find out offline
586 --overiding approvers and update the tables with that info
587 LOOP
588 fetch C1 into l_username;
589
590 if C1%NOTFOUND THEN
591 CLOSE C1;
592 EXIT;
593 END IF;
594
595 --obtain the overiding details for offline ntf
596 EDR_STANDARD.FIND_WF_NTF_RECIPIENT
597 (P_ORIGINAL_RECIPIENT => l_username,
598 P_MESSAGE_TYPE => 'EDRPSIGF',
599 P_MESSAGE_NAME => 'PSIG_OFFLINE_MSG_11511',
600 P_RECIPIENT => l_overriding_approver,
601 P_NTF_ROUTING_COMMENTS => l_overriding_comments,
602 P_ERR_CODE => l_error_num,
603 P_ERR_MSG => l_error_mesg);
604
605 --update the edr_psig_details table for correct evidence store
606 --information
607 UPDATE EDR_PSIG_DETAILS
608 SET USER_NAME=nvl(l_overriding_approver,l_username),
609 SIGNATURE_OVERRIDING_COMMENTS = l_overriding_comments
610 where document_id = l_doc_id
611 and user_response is null
612 and ORIGINAL_RECIPIENT = l_username;
613
614 --update the edr_esignature tables for correct ntf history rgn
615 --information
616 UPDATE EDR_ESIGNATURES
617 SET USER_NAME = nvl(l_overriding_approver,l_username) ,
618 SIGNATURE_OVERRIDING_COMMENTS = l_overriding_comments
619 where event_id = l_event_id
620 and ORIGINAL_RECIPIENT = l_username;
621
622 END LOOP;
623 -- Bug 4213923 : End
624
625 IF L_DEFER_MODE_ALLOWED <> 'Y' THEN
626 UPDATE EDR_ERECORDS
627 SET ERECORD_SIGNATURE_STATUS = 'FAILURE'
628 WHERE EVENT_ID = p_itemkey;
629
630 p_resultout := 'COMPLETE:PSIG_INCOMPLETE';
631 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_STATUS','FAILURE');
632 IF l_doc_id IS NOT NULL THEN
633 EDR_PSIG.changeDocumentStatus( P_DOCUMENT_ID => l_doc_id,
634 P_STATUS => 'ERROR',
635 P_ERROR => l_error_num,
636 P_ERROR_MSG => l_error_mesg);
637 IF l_ERROR_NUM IS NOT NULL THEN
638 RAISE EDR_PSIG_DOC_ERR;
639 END IF;
640 END IF;
641 ELSE
642 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_CURRENT_MODE','OFFLINE');
643 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_STATUS','PENDING');
644
645 UPDATE EDR_ERECORDS
646 SET ERECORD_SIGNATURE_STATUS = 'PENDING'
647 WHERE EVENT_ID = p_itemkey;
648
649 IF l_doc_id IS NOT NULL THEN
650 EDR_PSIG.changeDocumentStatus( P_DOCUMENT_ID => l_doc_id,
651 P_STATUS => 'PENDING',
652 P_ERROR => l_error_num,
653 P_ERROR_MSG => l_error_mesg);
654 IF l_ERROR_NUM IS NOT NULL THEN
655 RAISE EDR_PSIG_DOC_ERR;
656 END IF;
657 END IF;
658
659 p_resultout := 'COMPLETE:RESP_DONE_OFFLINE';
660 END IF; --end if L_DEFER_MODE_ALLOWED <> 'Y'
661
662 END IF; --end if IF L_SIGNATURE_STATUS in ('SUCCESS','REJECTED','TIMEDOUT')
663
664 END IF; -- end if Run Mode
665
666 EXCEPTION
667 WHEN EDR_PSIG_DOC_ERR THEN
668 WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','PROCESS_RESPONSE',p_itemtype,p_itemkey,
669 l_error_num,l_error_msg||L_ERROR_MESG);
670 raise;
671 WHEN OTHERS THEN
672 WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','PROCESS_RESPONSE',p_itemtype,p_itemkey,L_ERROR_MSG);
673 raise;
674 END;
675
676 /****************************************************************
677 ***** This procedure associated with Notification this is ****
678 ***** used to store data in PSIG evidence store ****
679 *****************************************************************/
680
681 PROCEDURE UPDATE_NOTIF_RESPONSE(
682 p_itemtype IN VARCHAR2,
683 p_itemkey IN VARCHAR2,
684 p_actid IN NUMBER,
685 p_funcmode IN VARCHAR2,
686 p_resultout OUT NOCOPY VARCHAR2
687 )
688 IS
689 l_SIGNER_TYPE VARCHAR2(30);
690 l_signer_reason VARCHAR2(32);
691 EDR_PSIG_NOT_NULL EXCEPTION;
692 EDR_PSIG_DOC_ERR EXCEPTION;
693 EDR_PSIG_NOT_READ EXCEPTION;
694
695 L_doc_id NUMBER;
696 L_signature_id NUMBER;
697 l_error_num NUMBER;
698 l_ERROR_MESG VARCHAR(2000);
699
700 l_doc_format VARCHAR2(30);
701 l_psig_event WF_EVENT_T;
702 l_paramlist WF_PARAMETER_LIST_T;
703 l_doc_params EDR_PSIG.params_table;
704 l_sign_params EDR_PSIG.params_table;
705 i INTEGER;
706
707 l_event_xml CLOB ;
708 l_event_text CLOB ;
709 l_event_name VARCHAR2(240);
710 l_event_key VARCHAR2(240);
711 L_ROLE VARCHAR2(320);
712 L_MESSAGE_TYPE VARCHAR2(80);
713 L_MESSAGE_NAME VARCHAR2(80);
714 L_PRIORITY VARCHAR2(80);
715 L_DUE_DATE VARCHAR2(80);
716 L_STATUS VARCHAR2(80);
717
718 l_item_type VARCHAR2(240);
719 l_item_key VARCHAR2(240);
720 L_response VARCHAR2(80);
721
722
723 CURSOR GET_RESPONSE_ATTR IS
724 select WMA.NAME,WMA.DISPLAY_NAME, WL.meaning
725 from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
726 WF_MESSAGE_ATTRIBUTES_VL WMA , WF_LOOKUPS WL
727 where WNA.NOTIFICATION_ID = wf_engine.context_nid
728 and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
729 and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
730 and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
731 and WNA.NAME = WMA.NAME
732 and wma.subtype='RESPOND'
733 and wma.format = wl.lookup_type
734 and wna.text_value = wl.lookup_code
735 and wma.type ='LOOKUP'
736 and decode(wma.name,'RESULT','RESULT','NORESULT') = 'NORESULT'
737 union
738 select WMA.NAME,WMA.DISPLAY_NAME, decode(wma.type,'VARCHAR2',wna.text_value,'NUMBER',
739 to_char(wna.number_value),'DATE',to_char(wna.date_value))
740 from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
741 WF_MESSAGE_ATTRIBUTES_VL WMA
742 where WNA.NOTIFICATION_ID = wf_engine.context_nid
743 and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
744 and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
745 and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
746 and WNA.NAME = WMA.NAME
747 and wma.subtype='RESPOND'
748 and wma.type <>'LOOKUP'
749 and decode(wma.name,'RESULT','RESULT','NORESULT') = 'NORESULT';
750
751 CURSOR GET_USER_RESPONSE_RESULT IS
752 select WL.LOOKUP_CODE,WMA.DISPLAY_NAME, WL.meaning
753 from WF_NOTIFICATION_ATTRIBUTES WNA, WF_NOTIFICATIONS WN,
754 WF_MESSAGE_ATTRIBUTES_VL WMA , WF_LOOKUPS WL
755 where WNA.NOTIFICATION_ID = wf_engine.context_nid
756 and WNA.NOTIFICATION_ID = WN.NOTIFICATION_ID
757 and WN.MESSAGE_NAME = WMA.MESSAGE_NAME
758 and WN.MESSAGE_TYPE = WMA.MESSAGE_TYPE
759 and WNA.NAME = WMA.NAME
760 and wma.subtype='RESPOND'
761 and wma.format = wl.lookup_type
762 and wna.text_value = wl.lookup_code
763 and wma.type ='LOOKUP'
764 and decode(wma.name,'RESULT','RESULT','NORESULT') = 'RESULT';
765
766
767 GET_USER_RESPONSE_RESULT_REC GET_USER_RESPONSE_RESULT%ROWTYPE;
768 GET_RESPONSE_ATTR_REC GET_RESPONSE_ATTR%ROWTYPE;
769
770 /* This Cursor is to build the temporary group for sending rejection notification */
771 CURSOR GET_USER is
772 SELECT distinct USER_NAME from EDR_ESIGNATURES where to_char(event_id)=l_item_key;
773
774 l_userlist VARCHAR2(4000) ;
775 l_requester VARCHAR2(4000) ;
776 l_user varchar2(32);
777
778 l_display_name varchar2(240);
779 l_group varchar2(240);
780 l_ESIGN_ID VARCHAR2(30);
781
782 /* this curosr is to get original approve */
783 --Bug 3214398: start
784 /*Get User_Name alongwith original recipient*/
785 /*
786 CURSOR original_recipient is
787 SELECT ORIGINAL_RECIPIENT from EDR_ESIGNATURES where
788 signature_id=l_esign_id;
789 */
790 CURSOR signer_detail is
791 SELECT ORIGINAL_RECIPIENT, USER_NAME from EDR_ESIGNATURES where
792 signature_id = l_esign_id;
793 l_user_name varchar2(200);
794 l_overriding_approver varchar2(200);
795 l_overriding_comments varchar2(4000);
796 --Bug 3214398: end
797
798 l_erec_template_type varchar2(256);
799 l_responder varchar2(200);
800 l_original_recipient varchar2(200);
801 l_comments varchar2(4000);
802 /* End of enhancemtns */
803 L_NTF_RESPOND_ROLE varchar2(320);
804 L_NTF_MESSAGE_TYPE varchar2(240);
805 L_NTF_MESSAGE_NAME varchar2(240);
806 L_NTF_PRIORITY varchar2(240);
807 L_NTF_DUE_DATE varchar2(240);
808 L_NTF_STATUS varchar2(240);
809
810 L_RESPONSE_READ varchar2(240);
811
812 --Bug 2674799 : start
813 l_count number;
814
815 L_CURR_SIGN_LEVEL number;
816
817
818 lp_itemtype varchar2(50);
819 lp_itemkey varchar2(50);
820 lp_status varchar2(50);
821 lp_result varchar2(50);
822 lp_notification_id varchar2(50);
823 lp_curr number;
824 lc_itemkey varchar2(50);
825
826
827 CURSOR CURR_LIST_SIGNERS IS
828 SELECT event_name, SIGNATURE_ID,NVL(ORIGINAL_RECIPIENT,USER_NAME) USER_NAME
829 FROM EDR_ESIGNATURES
830 WHERE EVENT_ID = lp_itemkey
831 --Bug 4272262: Start
832 --Convert signature sequence to a number value.
833 AND to_number(SIGNATURE_SEQUENCE,'999999999999.999999') = L_CURR_SIGN_LEVEL
834 --Bug 4272262: End
835 AND SIGNATURE_STATUS='PENDING';
836
837 SIGNER_LIST_REC CURR_LIST_SIGNERS%ROWTYPE;
838
839 --Bug 2674799 : end
840
841
842 --Bug 4577122: Start
843 l_ignore_wfattr_notfound boolean := true;
844 l_voting_regime varchar2(1);
845 --Bug 4577122: End
846
847 BEGIN
848
849 --Bug 4074173 : start
850
851 l_doc_format := 'text/plain';
852 l_item_type := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_ITEMTYPE');
853 l_item_key := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_ITEMKEY');
854 l_userlist := GET_WF_ITEM_ATTRIBUTE_TEXT(l_item_type, l_item_key,'#WF_SIGN_REQUESTER');
855 l_requester := GET_WF_ITEM_ATTRIBUTE_TEXT(l_item_type, l_item_key,'#WF_SIGN_REQUESTER');
856
857 l_display_name :='eSignature Group';
858 l_group :='EDRPSIG_ROLE';
859 l_ESIGN_ID := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_ESIGN_ROW_ID');
860
861 l_event_xml := null;
862 l_event_text := null;
863
864 --Bug 4074173 : end
865
866 -- Bug 4577122: Start
867 l_voting_regime := wf_engine.GetitemAttrText
868 (p_itemtype, p_itemkey,'AME_VOTING_REGIME',
869 l_ignore_wfattr_notfound);
870 --Bug 4577122: End
871 -- Bug 5120197 : start
872 IF(p_funcmode = 'TRANSFER' OR p_funcmode = 'FORWARD') THEN
873 WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','UPDATE_NOTIF_RESPONSE',p_itemtype,p_itemkey,
874 FND_MESSAGE.GET_STRING('EDR','EDR_EREC_REASSIGN_ERR'));
875 raise_application_error(-20003,FND_MESSAGE.GET_STRING('EDR','EDR_EREC_REASSIGN_ERR'));
876 END IF;
877
878 -- Bug 5120197 : END
879
880 IF (p_funcmode = 'RESPOND') THEN
881
882 l_SIGNER_TYPE := GET_NOTIF_ITEM_ATTR_TEXT(wf_engine.context_nid,'WF_SIGNER_TYPE');
883 l_signer_reason := GET_NOTIF_ITEM_ATTR_TEXT(wf_engine.context_nid,'REASON_CODE');
884 IF l_signer_reason IS null OR l_SIGNER_TYPE IS NULL THEN
885 WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','UPDATE_NOTIF_RESPONSE',p_itemtype,p_itemkey,
886 'Signer Comment and Signing Reason code can not be null.');
887 raise EDR_PSIG_NOT_NULL;
888 END IF;
889
890 -- Bug 3170251 : Check if user has selected yes for reading the attached eRecord
891 OPEN GET_USER_RESPONSE_RESULT;
892 FETCH GET_USER_RESPONSE_RESULT INTO GET_USER_RESPONSE_RESULT_REC;
893 IF GET_USER_RESPONSE_RESULT%FOUND THEN
894 IF GET_USER_RESPONSE_RESULT_REC.LOOKUP_CODE <> '#SIG_CANCEL' THEN
895 -- Begin Bug 3847779
896 -- The getattrtext has been wrapped in a being and end block to trap wf exception if
897 -- attribute is not found for previous notificaitons backward compitability.
898 L_RESPONSE_READ := GET_NOTIF_ITEM_ATTR_TEXT(wf_engine.context_nid, 'READ_RESPONSE');
899 -- End Bug 3847779
900 IF (L_RESPONSE_READ = 'N') THEN
901 WF_CORE.CONTEXT('EDR_PSIG_PAGE_FLOW', 'UPDATE_NOTIF_RESPONSE',p_itemtype, p_itemkey,
902 FND_MESSAGE.GET_STRING('EDR','EDR_EREC_NOT_REVIEWED_ERR'));
903 raise_application_error(-20002,FND_MESSAGE.GET_STRING('EDR','EDR_EREC_NOT_REVIEWED_ERR'));
904 END IF;
905 END IF;
906
907
908 --BUg 2674799 : start
909 lp_curr := INSTR(p_itemkey,'-');
910 lp_itemkey := SUBSTR(p_itemkey,0,lp_curr-1);
911
912 --Bug 4577122 : start
913
914 IF GET_USER_RESPONSE_RESULT_REC.LOOKUP_CODE = 'REJECTED' or
915 (GET_USER_RESPONSE_RESULT_REC.LOOKUP_CODE = 'APPROVED' AND
916 L_VOTING_REGIME = ame_util.firstApproverVoting) THEN
917 --Bug 4577122 : end
918
919 --Bug 4272262: Start
920 --Convert signature sequence to a number value.
921 SELECT MIN(to_number(SIGNATURE_SEQUENCE,'999999999999.999999')) INTO L_CURR_SIGN_LEVEL
922 from EDR_ESIGNATURES
923 WHERE EVENT_ID =lp_itemkey
924 AND SIGNATURE_STATUS = 'PENDING' ;
925 --Bug 4272262: End
926
927 OPEN CURR_LIST_SIGNERS;
928 LOOP
929
930 FETCH CURR_LIST_SIGNERS INTO SIGNER_LIST_REC;
931 EXIT WHEN CURR_LIST_SIGNERS%NOTFOUND;
932
933 lc_itemkey := lp_itemkey || '-' || SIGNER_LIST_REC.SIGNATURE_ID;
934
935 WF_ENGINE.ITEMSTATUS ( itemtype => p_itemtype,
936 itemkey => lc_itemkey,
937 status => lp_status,
938 result => lp_result);
939
940 if(lc_itemkey <> p_itemkey and lp_status = 'ACTIVE' ) then
941
942 select notification_id into lp_notification_id
943 from wf_item_activity_statuses where item_type=p_itemtype
944 and item_key = lc_itemkey and notification_id is not null
945 and activity_status ='NOTIFIED';
946
947
948 if lp_notification_id is not null and lp_notification_id > 0 then
949 wf_notification.cancel(lp_notification_id);
950
951 end if;
952
953
954 WF_ENGINE.ABORTPROCESS(itemtype => p_itemtype,
955 itemkey => lc_itemkey
956 );
957
958 end if;
959
960 END LOOP;
961
962 CLOSE CURR_LIST_SIGNERS;
963
964 END IF; -- REJECTED
965
966 --Bug 2674799 : end
967
968 END IF;
969 CLOSE GET_USER_RESPONSE_RESULT;
970 -- Bug 3170251 : End
971
972 --Bug 2674799 : Start
973 /* SELECT count(*) INTO l_count
974 FROM EDR_ESIGNATURES
975 WHERE EVENT_ID = l_item_key
976 AND SIGNATURE_STATUS = 'REJECTED';
977
978 IF l_count > 0 then
979 raise_application_error(-20002,FND_MESSAGE.GET_STRING('EDR','EDR_EREC_ALREADY_REJECTED'));
980 END IF;
981 */
982 --Bug 2674799 : End
983
984 /* Following statements are to store data in PSIG evidance store */
985 l_doc_id := GET_WF_ITEM_ATTRIBUTE_NUMBER(l_item_type, l_item_key,'EDR_PSIG_DOC_ID');
986 IF l_doc_id is null THEN
987 l_psig_event := wf_engine.getItemAttrEvent(p_itemtype, p_itemkey,'#PSIG_EVENT');
988 l_event_xml := l_psig_event.getEventData();
989 l_event_name := l_psig_event.getEventName();
990 l_event_key := l_psig_event.getEventKey();
991 l_doc_id := GET_WF_ITEM_ATTRIBUTE_TEXT(l_item_type, l_item_key,'PSIG_DOCUMENT_ID');
992
993 /* Fixed to take care of PDF format */
994
995 l_erec_template_type := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey, 'EREC_TEMPLATE_TYPE');
996
997 if (l_erec_template_type = 'RTF') then
998 -- Bug 3474765 : Start
999 /* Changing the file content type to be MIME Formatted */
1000 l_doc_format:='application/pdf';
1001 -- Bug 3474765 : End
1002 end if;
1003 /* END of PDF fix */
1004
1005 EDR_PSIG.updateDocument(P_PSIG_XML => l_event_xml,
1006 P_PSIG_DOCUMENT => l_event_xml,
1007 P_PSIG_DOCUMENTFORMAT => l_doc_format,
1008 P_PSIG_REQUESTER => GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_SIGN_REQUESTER'),
1009 P_PSIG_SOURCE => GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_SOURCE_APPLICATION_TYPE'),
1010 P_EVENT_NAME => l_event_name,
1011 P_EVENT_KEY => l_event_key,
1012 P_WF_NID => wf_engine.context_nid,
1013 P_DOCUMENT_ID => l_doc_id,
1014 P_ERROR => l_error_num,
1015 P_ERROR_MSG => L_ERROR_MESG);
1016 IF l_ERROR_NUM IS NOT NULL THEN
1017 RAISE EDR_PSIG_DOC_ERR;
1018 ELSE
1019 SET_WF_ITEM_ATTRIBUTE_NUMBER(l_item_type, l_item_key,'EDR_PSIG_DOC_ID',l_doc_id);
1020 l_paramlist:=l_psig_event.Parameter_List;
1021 IF (l_paramlist is not null) THEN
1022 FOR i IN l_paramlist.first .. l_paramlist.last LOOP
1023
1024 l_doc_params(i).param_name:=l_paramlist(i).GetName;
1025 l_doc_params(i).param_value:=l_paramlist(i).GetValue;
1026 l_doc_params(i).param_displayname:=l_paramlist(i).GetName;
1027 END LOOP;
1028
1029 EDR_PSIG.POSTDOCUMENTPARAMETER( P_DOCUMENT_ID =>l_doc_id,
1030 P_PARAMETERS =>l_doc_params,
1031 P_ERROR =>l_error_num,
1032 P_ERROR_MSG =>l_error_mesg);
1033 IF l_ERROR_NUM IS NOT NULL THEN
1034 RAISE EDR_PSIG_DOC_ERR;
1035 END IF;
1036 END IF; -- Param List Error
1037 END IF; -- Doc Error
1038 END IF; -- First Time
1039
1040 OPEN GET_USER_RESPONSE_RESULT;
1041 FETCH GET_USER_RESPONSE_RESULT INTO GET_USER_RESPONSE_RESULT_REC;
1042 IF GET_USER_RESPONSE_RESULT%FOUND THEN
1043 IF GET_USER_RESPONSE_RESULT_REC.LOOKUP_CODE <> '#SIG_CANCEL' THEN
1044 --Bug 3214398: Start
1045 --We need to get the user_name and pass the overriding details for user_name
1046 --to edr_psig.postSignature to avoid partial routing rules in GQ
1047 --In postSignature the overriding comments are appended so if there is no change
1048 --after the signer process has started there will be no impact
1049 --and if the routing has changed GQ will be populated with correct results
1050 /*
1051 OPEN original_recipient;
1052 FETCH original_recipient into l_original_recipient;
1053 CLOSE original_recipient;
1054 */
1055 OPEN signer_detail;
1056 FETCH signer_detail into l_original_recipient, l_user_name;
1057 CLOSE signer_detail;
1058
1059 -- Bug 3902969 : Start
1060 -- Check if the recipient l_user_name is same as WF_ENGINE.CONTEXT_TEXT
1061 -- if they are differnt then only call find wf ntf recipients API.
1062
1063 IF( l_user_name <> WF_ENGINE.CONTEXT_USER) THEN
1064
1065 EDR_STANDARD.FIND_WF_NTF_RECIPIENT(P_ORIGINAL_RECIPIENT => l_user_name,
1066 P_MESSAGE_TYPE => 'EDRPSIGF',
1067 P_MESSAGE_NAME => 'PSIG_EREC_MESSAGE_BLAF',
1068 P_RECIPIENT => l_overriding_approver,
1069 P_NTF_ROUTING_COMMENTS => l_overriding_comments,
1070 P_ERR_CODE => l_error_num,
1071 P_ERR_MSG => l_error_mesg);
1072
1073 END IF;
1074 -- Bug 3902969 : End
1075
1076 IF (l_ERROR_NUM > 0 ) THEN
1077 RAISE EDR_PSIG_DOC_ERR;
1078 END IF;
1079
1080 -- Bug 4190367 : Modifying to make use of wf_notification.getattrtext to populate Evidence store id.
1081 /*
1082 EDR_PSIG.postSignature(P_DOCUMENT_ID => l_doc_id,
1083 P_EVIDENCE_STORE_ID => wf_notification.getattrtext(wf_engine.context_nid,'#WF_SIG_ID'),
1084 P_USER_NAME => WF_ENGINE.context_text,
1085 P_USER_RESPONSE => GET_USER_RESPONSE_RESULT_REC.MEANING,
1086 P_SIGNATURE_ID => l_SIGNATURE_id,
1087 P_ORIGINAL_RECIPIENT => l_ORIGINAL_RECIPIENT,
1088 P_ERROR => l_error_num,
1089 P_ERROR_MSG => L_ERROR_MESG);
1090 */
1091
1092
1093 EDR_PSIG.postSignature(P_DOCUMENT_ID => l_doc_id,
1094 P_EVIDENCE_STORE_ID => wf_notification.getattrtext(wf_engine.context_nid,'#WF_SIG_ID'),
1095 P_USER_NAME => WF_ENGINE.context_text,
1096 P_USER_RESPONSE => GET_USER_RESPONSE_RESULT_REC.MEANING,
1097 P_SIGNATURE_ID => l_SIGNATURE_id,
1098 P_ORIGINAL_RECIPIENT => l_ORIGINAL_RECIPIENT,
1099 P_OVERRIDING_COMMENTS => l_overriding_comments,
1100 P_ERROR => l_error_num,
1101 P_ERROR_MSG => L_ERROR_MESG);
1102
1103 -- Bug 4190367 : End
1104 --Bug 3214398: end
1105
1106 IF l_ERROR_NUM IS NOT NULL THEN
1107 RAISE EDR_PSIG_DOC_ERR;
1108 END IF;
1109 i := 0;
1110 OPEN GET_RESPONSE_ATTR;
1111 LOOP
1112 FETCH GET_RESPONSE_ATTR INTO GET_RESPONSE_ATTR_REC;
1113 EXIT WHEN GET_RESPONSE_ATTR%NOTFOUND;
1114 i := i + 1;
1115 /* To support WF_NOTE attribute SKARIMIS*/
1116 IF GET_RESPONSE_ATTR_REC.Name = 'WF_NOTE' THEN
1117 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,
1118 p_itemkey,
1119 'SIGNERS_COMMENT',
1120 GET_RESPONSE_ATTR_REC.Meaning);
1121 GET_RESPONSE_ATTR_REC.Name:='SIGNERS_COMMENT';
1122 END IF;
1123 l_sign_params(i).param_name:=GET_RESPONSE_ATTR_REC.Name;
1124 l_sign_params(i).param_value:=GET_RESPONSE_ATTR_REC.Meaning;
1125 l_sign_params(i).param_displayname:=GET_RESPONSE_ATTR_REC.display_Name;
1126 END LOOP;
1127 CLOSE GET_RESPONSE_ATTR;
1128
1129 IF i > 0 THEN
1130 EDR_PSIG.postSignatureParameter(P_SIGNATURE_ID => l_SIGNATURE_id,
1131 P_PARAMETERS => l_sign_params,
1132 P_ERROR => l_error_num,
1133 P_ERROR_MSG => L_ERROR_MESG);
1134 IF l_ERROR_NUM IS NOT NULL THEN
1135 RAISE EDR_PSIG_DOC_ERR;
1136 END IF;
1137 END IF;
1138 END IF;
1139 END IF;
1140 CLOSE GET_USER_RESPONSE_RESULT;
1141
1142 /* Capture Current Notification Responder BUG Fix 2903607 SKARIMIS*/
1143 wf_notification.getinfo(
1144 NID =>wf_engine.context_nid,
1145 ROLE =>L_NTF_RESPOND_ROLE,
1146 MESSAGE_TYPE =>L_NTF_MESSAGE_TYPE,
1147 MESSAGE_NAME =>L_NTF_MESSAGE_NAME,
1148 PRIORITY =>L_NTF_PRIORITY,
1149 DUE_DATE =>L_NTF_DUE_DATE,
1150 STATUS =>L_NTF_STATUS);
1151
1152 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,
1153 p_itemkey,
1154 'NTF_RESPONDER',
1155 L_NTF_RESPOND_ROLE);
1156 END IF; -- Function Mode
1157
1158 IF (p_funcmode = 'RUN') THEN
1159
1160
1161 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,
1162 '#WF_SIGNER_ROLE_DISPLAY_NAME',
1163 WF_DIRECTORY.GETROLEDISPLAYNAME(WF_ENGINE.context_text));
1164 /* SKARIMIS Get the user List */
1165 OPEN GET_USER;
1166 LOOP
1167 fetch GET_USER into l_user;
1168 EXIT when GET_USER%NOTFOUND;
1169 if l_requester <> l_user then
1170 l_userlist:=l_userlist||','||l_user;
1171 end if;
1172 END LOOP;
1173 CLOSE GET_USER;
1174
1175 /* Create ADHOC group */
1176
1177 If (wf_directory.getRoleDisplayName('EDRPSIG_ROLE') is NULL) then
1178 /* Start Creating the Role */
1179
1180 wf_directory.CreateAdHocRole(role_name=>l_group,
1181 role_display_name=>l_display_name,
1182 role_users=>l_userlist,
1183 expiration_date=>NULL);
1184
1185 ELSE
1186 wf_directory.RemoveUsersFromAdHocRole(role_name=>'EDRPSIG_ROLE',
1187 role_users=>NULL);
1188
1189 wf_directory.AddUsersToAdHocRole(role_name=>'EDRPSIG_ROLE',
1190 role_users=>l_userlist);
1191 END IF;
1192
1193 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,
1194 'ESIG_GROUP' ,
1195 'EDRPSIG_ROLE');
1196
1197 END IF; -- RUN function ends
1198
1199 IF (p_funcmode ='TIMEOUT') then
1200 wf_notification.getinfo(
1201 NID =>wf_engine.context_nid,
1202 ROLE =>L_NTF_RESPOND_ROLE,
1203 MESSAGE_TYPE =>L_NTF_MESSAGE_TYPE,
1204 MESSAGE_NAME =>L_NTF_MESSAGE_NAME,
1205 PRIORITY =>L_NTF_PRIORITY,
1206 DUE_DATE =>L_NTF_DUE_DATE,
1207 STATUS =>L_NTF_STATUS);
1208 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,
1209 p_itemkey,
1210 'NTF_RESPONDER',
1211 L_NTF_RESPOND_ROLE);
1212 END IF;
1213
1214 EXCEPTION WHEN EDR_PSIG_NOT_NULL THEN
1215 WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','UPDATE_NOTIF_RESPONSE',p_itemtype,p_itemkey,
1216 'Signer Comment and Signing Reason code can not be null.');
1217 raise;
1218 WHEN EDR_PSIG_DOC_ERR THEN
1219 WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','UPDATE_NOTIF_RESPONSE',p_itemtype,p_itemkey,
1220 l_error_num,L_ERROR_MESG);
1221 raise;
1222 WHEN EDR_PSIG_NOT_READ THEN
1223 WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','UPDATE_NOTIF_RESPONSE',p_itemtype,p_itemkey,
1224 FND_MESSAGE.GET_STRING('EDR','EDR_EREC_NOT_REVIEWED_ERR'));
1225 raise;
1226 WHEN OTHERS THEN
1227 WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','UPDATE_NOTIF_RESPONSE',p_itemtype,p_itemkey,
1228 l_error_num,L_ERROR_MESG);
1229 raise;
1230 END UPDATE_NOTIF_RESPONSE;
1231
1232 /******************************************************************************
1233 *** This procedure is associated with EDRESGPF workflow. This code will **
1234 *** execute when user clicks on Approve Button in response to notification **
1235 ******************************************************************************/
1236
1237 PROCEDURE NOTIF_APPROVED(
1238 p_itemtype IN VARCHAR2,
1239 p_itemkey IN VARCHAR2,
1240 p_actid IN NUMBER,
1241 p_funcmode IN VARCHAR2,
1242 p_resultout OUT NOCOPY VARCHAR2
1243 )
1244 IS
1245 l_ESIGN_ID VARCHAR2(30) ;
1246 l_EVENT_ID NUMBER ;
1247 l_SIGNER_TYPE VARCHAR2(30) ;
1248 l_signer_reason VARCHAR2(32) ;
1249 l_signer_comment VARCHAR2(4000) ;
1250 l_count number;
1251
1252 --Bug 4577122: Start
1253 l_ignore_wfattr_notfound boolean := true;
1254 l_voting_regime varchar2(1);
1255 --Bug 4577122: End
1256
1257 BEGIN
1258
1259 --Bug 4074173 : start
1260 l_ESIGN_ID := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_ESIGN_ROW_ID');
1261 l_EVENT_ID := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_ITEMKEY');
1262 l_SIGNER_TYPE := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'WF_SIGNER_TYPE');
1263 l_signer_reason := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'REASON_CODE');
1264 l_signer_comment := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'SIGNERS_COMMENT');
1265 --Bug 4074173 : end
1266
1267 --Bug 4577122: Start
1268 l_voting_regime := wf_engine.GetitemAttrText
1269 (p_itemtype, p_itemkey,'AME_VOTING_REGIME',
1270 l_ignore_wfattr_notfound);
1271 --Bug 4577122: End
1272
1273 IF p_funcmode = 'RUN' THEN
1274 UPDATE EDR_ESIGNATURES
1275 SET SIGNATURE_STATUS = 'APPROVED' ,
1276 SIGNATURE_TYPE = l_SIGNER_TYPE,
1277 SIGNATURE_REASON_CODE = l_signer_reason,
1278 SIGNATURE_TIMESTAMP = SYSDATE,
1279 SIGNER_COMMENTS = l_signer_comment
1280 WHERE SIGNATURE_ID = l_ESIGN_ID;
1281
1282 /* Verify all are Approved */
1283
1284 SELECT count(*) INTO l_count
1285 from EDR_ESIGNATURES
1286 WHERE EVENT_ID = l_event_id
1287 AND SIGNATURE_STATUS <> 'APPROVED' ;
1288
1289 --Bug 4577122: Start
1290 --IF l_count = 0 THEN
1291 IF (l_count = 0 or l_voting_regime = ame_util.firstApproverVoting) THEN
1292 --Bug 4577122: End
1293
1294 /* if all signers are approved set the status of edr_erecords to presuccess.
1295 this will be used on list of signer screen to handle done button. */
1296
1297 UPDATE EDR_ERECORDS
1298 SET ERECORD_SIGNATURE_STATUS = 'PRESUCCESS'
1299 WHERE EVENT_ID = l_event_id;
1300
1301
1302 --Bug 4577122: Start
1303 --clear out the status etc fields of rest of the edr_esignatures
1304 if (l_voting_regime = ame_util.firstApproverVoting) then
1305 update edr_esignatures
1306 set signature_status = null
1307 where event_id = l_event_id
1308 and signature_status = 'PENDING';
1309 end if;
1310 --Bug 4577122: End
1311
1312 END IF;
1313 -- p_resultout := 'COMPLETE:';
1314 END IF;
1315
1316 END NOTIF_APPROVED;
1317
1318 /*******************************************************************************
1319 *** This procedure is associated with EDRESGPF workflow. This code will **
1320 *** execute when user clicks on Cancel Button in response to notification **
1321 *** No processing is done here. **
1322 *******************************************************************************/
1323
1324 PROCEDURE NOTIF_CANCELED(
1325 p_itemtype IN VARCHAR2,
1326 p_itemkey IN VARCHAR2,
1327 p_actid IN NUMBER,
1328 p_funcmode IN VARCHAR2,
1329 p_resultout OUT NOCOPY VARCHAR2
1330 )
1331 IS
1332 BEGIN
1333 IF (p_funcmode = 'RUN') THEN
1334 null;
1335 -- p_resultout := 'COMPLETE:';
1336 END IF;
1337 END NOTIF_CANCELED;
1338
1339 /*****************************************************************************
1340 *** This procedure is associated with EDRESGPF workflow. This code will **
1341 *** executed when use click on Reject Button in response to notification **
1342 *****************************************************************************/
1343
1344 PROCEDURE NOTIF_REJECTED(
1345 p_itemtype IN VARCHAR2,
1346 p_itemkey IN VARCHAR2,
1347 p_actid IN NUMBER,
1348 p_funcmode IN VARCHAR2,
1349 p_resultout OUT NOCOPY VARCHAR2
1350 )
1351 IS
1352 l_ESIGN_ID VARCHAR2(30);
1353 l_EVENT_ID NUMBER;
1354 l_SIGNER_TYPE VARCHAR2(30);
1355 l_signer_reason VARCHAR2(32);
1356 l_signer_comment VARCHAR2(4000);
1357 l_count number;
1358 BEGIN
1359
1360 --Bug 4074173 : start
1361 l_ESIGN_ID := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_ESIGN_ROW_ID');
1362 l_EVENT_ID := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_ITEMKEY');
1363 l_SIGNER_TYPE := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'WF_SIGNER_TYPE');
1364 l_signer_reason := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'REASON_CODE');
1365 l_signer_comment := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'SIGNERS_COMMENT');
1366 --Bug 4074173 : end
1367
1368
1369
1370 IF p_funcmode = 'RUN' THEN
1371 /* Skarimis */
1372
1373 wf_directory.RemoveUsersFromAdHocRole(role_name=>'EDRPSIG_ROLE', role_users=>NULL);
1374
1375 /* Skarimis */
1376
1377 /* Change following code to derieve Timezone info */
1378 UPDATE EDR_ESIGNATURES
1379 SET SIGNATURE_STATUS = 'REJECTED' ,
1380 SIGNATURE_TYPE = l_SIGNER_TYPE,
1381 SIGNATURE_REASON_CODE = l_signer_reason,
1382 SIGNATURE_TIMESTAMP = SYSDATE,
1383 SIGNER_COMMENTS = l_signer_comment
1384 WHERE SIGNATURE_ID = l_ESIGN_ID;
1385
1386 /* set the status of edr_erecords to pre rejected. this will be used on list of signer screen.
1387 to handle done button. */
1388
1389 UPDATE EDR_ERECORDS
1390 SET ERECORD_SIGNATURE_STATUS = 'PREREJECTED'
1391 WHERE EVENT_ID = l_event_id;
1392
1393 -- p_resultout := 'COMPLETE:';
1394 END IF;
1395 END NOTIF_REJECTED;
1396
1397 /************************************************************************************
1398 **** Following Procedure is used by list of signer screen. it accepts item_type **
1399 **** and Item_key returns current open notification id this is used to build **
1400 **** Notification URL. This is required as OANavigation.nextPage does not return **
1401 **** notification URL. ***
1402 *************************************************************************************/
1403
1404
1405 FUNCTION getNotificationID(p_itemtype IN VARCHAR2,
1406 p_itemkey IN VARCHAR2) RETURN VARCHAR2 IS
1407 -- Bug 5223211 : start
1408 /*
1409 CURSOR CUR_NOTIFICATION IS
1410 SELECT NOTIFICATION_ID
1411 FROM WF_ITEM_ACTIVITIES_HISTORY_V
1412 WHERE ITEM_KEY = p_itemkey
1413 AND ITEM_TYPE = p_itemtype
1414 AND ACTIVITY_TYPE = 'NOTICE'
1415 AND NOTIFICATION_STATUS = 'OPEN'
1416 ORDER BY BEGIN_DATE DESC;
1417 */
1418 CURSOR CUR_NOTIFICATION IS
1419 SELECT NOTIFICATION_ID from WF_ITEM_ACTIVITY_STATUSES_V
1420 WHERE ITEM_KEY = p_itemkey
1421 AND ITEM_TYPE = p_itemtype
1422 AND notification_id is not null
1423 ORDER BY activity_begin_date, execution_time;
1424 -- Bug 5223211 : End
1425 l_notification_id NUMBER;
1426 BEGIN
1427 OPEN CUR_NOTIFICATION;
1428 FETCH CUR_NOTIFICATION INTO l_notification_id;
1429 CLOSE CUR_NOTIFICATION;
1430 RETURN l_notification_id;
1431 END;
1432
1433 /************************************************************************************************
1434 ****** Following Procedure is to spwan child process to get off-line electronic signatures ***
1435 ****** It gets the current level to process and sends the notifications to all signers ***
1436 ****** of current level. It also copies required attributes parent process to child process ***
1437 *************************************************************************************************/
1438
1439 PROCEDURE SPWAN_OFFLINE_PROCESS(
1440 p_itemtype IN VARCHAR2,
1441 p_itemkey IN VARCHAR2,
1442 p_actid IN NUMBER,
1443 p_funcmode IN VARCHAR2,
1444 p_resultout OUT NOCOPY VARCHAR2
1445 )
1446 IS
1447 L_CURR_SIGN_LEVEL NUMBER ;
1448 CURSOR CURR_LIST_SIGNERS IS
1449 /*Changed the Cursor to get ORIGNIAL RECIPIENT instead of USER_NAME SKARIMIS */
1450 SELECT event_name, SIGNATURE_ID,NVL(ORIGINAL_RECIPIENT,USER_NAME) USER_NAME
1451 FROM EDR_ESIGNATURES
1452 WHERE EVENT_ID = p_itemkey
1453 --Bug 4272262: Start
1454 --Convert signature sequence to a number value.
1455 AND to_number(SIGNATURE_SEQUENCE,'999999999999.999999') = L_CURR_SIGN_LEVEL
1456 AND SIGNATURE_STATUS = 'PENDING';
1457 --Bug 4272262: End
1458
1459 SIGNER_LIST_REC CURR_LIST_SIGNERS%ROWTYPE;
1460 l_itemtype WF_ITEMS.ITEM_TYPE%TYPE ;
1461 l_WorkflowProcess VARCHAR2(30) ;
1462 l_performer_name FND_USER.USER_NAME%TYPE ;
1463 l_performer_display_name FND_USER.DESCRIPTION%TYPE ;
1464 l_item_key VARCHAR2(100);
1465
1466 --Bug 4577122: Start
1467 l_ignore_wfattr_notfound boolean := true;
1468 l_voting_regime varchar2(1);
1469 --Bug 4577122: End
1470
1471 BEGIN
1472
1473 --Bug 4074173 : Start
1474 l_itemtype := 'EDRPSIGF';
1475 l_WorkflowProcess := 'PSIG_OFFLINE_NOTIF_PROCESS';
1476 --Bug 4074173 : End
1477
1478 if p_funcmode='RUN' then
1479
1480 --Bug 4272262: Start
1481 SELECT MIN( to_number(SIGNATURE_SEQUENCE,'999999999999.999999')) INTO L_CURR_SIGN_LEVEL
1482 from EDR_ESIGNATURES
1483 WHERE EVENT_ID = p_itemkey
1484 AND SIGNATURE_STATUS = 'PENDING' ;
1485 --Bug 4272262: End
1486
1487 OPEN CURR_LIST_SIGNERS;
1488 LOOP
1489 FETCH CURR_LIST_SIGNERS INTO SIGNER_LIST_REC;
1490 EXIT WHEN CURR_LIST_SIGNERS%NOTFOUND;
1491
1492 l_item_key := p_itemkey ||'-'||SIGNER_LIST_REC.SIGNATURE_ID;
1493
1494 /* create the process */
1495 WF_ENGINE.CREATEPROCESS (itemtype => l_itemtype,
1496 itemkey => l_item_key,
1497 process => l_WorkflowProcess) ;
1498 /* set the item attributes */
1499
1500 --Bug 4577122: Start
1501 l_voting_regime := wf_engine.GetitemAttrText
1502 (p_itemtype, p_itemkey,'AME_VOTING_REGIME',
1503 l_ignore_wfattr_notfound);
1504
1505 WF_ENGINE.ADDITEMATTR
1506 (itemtype => l_itemtype,
1507 itemkey => l_item_key,
1508 aname => 'AME_VOTING_REGIME',
1509 text_value => l_voting_regime);
1510
1511 --Bug 4577122: End
1512
1513 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1514 '#WF_PAGEFLOW_ITEMKEY',
1515 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_ITEMKEY'));
1516 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1517 '#WF_PAGEFLOW_ITEMTYPE',
1518 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_ITEMTYPE'));
1519 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1520 'DEFERRED',
1521 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'DEFERRED'));
1522
1523 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1524 '#WF_ESIGN_ROW_ID' ,
1525 SIGNER_LIST_REC.SIGNATURE_ID);
1526 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1527 '#WF_CURRENT_MODE' ,
1528 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_CURRENT_MODE'));
1529 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1530 'PSIG_EVENT_NAME' ,
1531 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'PSIG_EVENT_NAME'));
1532 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1533 'PSIG_TIMEZONE' ,
1534 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'PSIG_TIMEZONE'));
1535 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1536 'PSIG_TIMESTAMP' ,
1537 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'PSIG_TIMESTAMP'));
1538
1539 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1540 'PSIG_USER_KEY_LABEL' ,
1541 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'PSIG_USER_KEY_LABEL'));
1542 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1543 'PSIG_USER_KEY_VALUE' ,
1544 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'PSIG_USER_KEY_VALUE'));
1545
1546 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1547 'SIGNATURE_HISTORY' ,
1548 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'SIGNATURE_HISTORY'));
1549
1550 /* Style Sheet Version Fix */
1551 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1552 'TEXT_XSLNAME',
1553 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'TEXT_XSLNAME'));
1554
1555 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1556 'TEXT_XSLVERSION' ,
1557 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'TEXT_XSLVERSION'));
1558
1559 SET_WF_ITEM_ATTRIBUTE_NUMBER(l_itemtype,
1560 l_item_key,
1561 '#WF_NOTIFICATION_TIMEOUT',
1562 GET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype, p_itemkey,'#WF_NOTIFICATION_TIMEOUT'));
1563 /* SKARIMIS Added Resend count */
1564
1565 SET_WF_ITEM_ATTRIBUTE_NUMBER(l_itemtype,
1566 l_item_key,
1567 'TIMEOUT_RESEND_COUNT',
1568 GET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype, p_itemkey,'TIMEOUT_RESEND_COUNT'));
1569 SET_WF_ITEM_ATTRIBUTE_NUMBER(l_itemtype,
1570 l_item_key,
1571 '#WF_NOTIFICATION_TIMEOUT_HR',
1572 GET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype, p_itemkey,'#WF_NOTIFICATION_TIMEOUT')/60);
1573
1574 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype, l_item_key,'PSIG_DOCUMENT_ID',
1575 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'PSIG_DOCUMENT_ID'));
1576
1577 SET_WF_ITEM_ATTRIBUTE_NUMBER(l_itemtype, l_item_key,'EDR_PSIG_DOC_ID',
1578 GET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype, p_itemkey,'EDR_PSIG_DOC_ID'));
1579
1580 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1581 '#WF_SIGNER_ROLE' ,
1582 SIGNER_LIST_REC.USER_NAME);
1583 /* set the item attributes */
1584 WF_ENGINE.SETITEMATTREVENT(itemtype => l_itemtype,itemkey => l_item_key,
1585 name => '#PSIG_EVENT',
1586 event => wf_engine.GETITEMATTREVENT(p_itemtype, p_itemkey,'#PSIG_EVENT'));
1587 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,'#WF_ERECORD_TEXT',
1588 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_ERECORD_TEXT'));
1589
1590 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1591 '#WF_SIGN_REQUESTER',
1592 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_SIGN_REQUESTER'));
1593
1594 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1595 '#ATTACHMENTS',
1596 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#ATTACHMENTS'));
1597 -- Bug: 3467353 - Start
1598
1599 -- Bug 3903471 : Start
1600 BEGIN
1601 WF_ENGINE.ADDITEMATTR(itemtype => l_itemtype,itemkey => l_item_key,
1602 aname => 'EREC_TEMPLATE_TYPE',
1603 text_value => GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'EREC_TEMPLATE_TYPE'));
1604 EXCEPTION WHEN OTHERS THEN
1605 NULL;
1606 END;
1607 --Bug 3903471 : End
1608
1609 --Bug 3998932 : Start
1610 --Isign Checklist ER
1611 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,
1612 l_item_key,
1613 'RELATED_APPLICATION',
1614 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,'RELATED_APPLICATION'));
1615 --Bug 3998932 : End
1616
1617 --Bug 4122622: Start
1618 --Fetch the related e-records and child e-record ids attribute values
1619 --from the parent workflow process and set them on the child
1620 --workflow process.
1621 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,
1622 l_item_key,
1623 'RELATED_ERECORDS',
1624 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,'RELATED_ERECORDS'));
1625
1626 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,
1627 l_item_key,
1628 'CHILD_ERECORD_IDS',
1629 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,'CHILD_ERECORD_IDS'));
1630
1631 --Bug 4122622: End
1632
1633 /* Setting Patent Child association */
1634 WF_ENGINE.SETITEMPARENT(itemtype =>l_itemtype,itemkey =>l_item_key,
1635 parent_itemtype => p_itemtype,
1636 parent_itemkey=> p_itemkey,
1637 parent_context=> NULL);
1638 /* start the Workflow process */
1639
1640
1641 /* Set process Owner SKARIMIS for BLAF standard */
1642 wf_engine.setitemowner
1643 (ITEMTYPE=>l_itemtype,
1644 ITEMKEY=>l_item_key,
1645 OWNER=>GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,'#WF_SIGN_REQUESTER'));
1646 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,itemkey => l_item_key);
1647
1648 END LOOP;
1649 CLOSE CURR_LIST_SIGNERS;
1650 end if;
1651 EXCEPTION
1652 WHEN OTHERS THEN
1653 WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','SPWAN_OFFLINE_PROCESS',l_itemtype,l_item_key,'Initial' );
1654 CLOSE CURR_LIST_SIGNERS;
1655 raise;
1656 END SPWAN_OFFLINE_PROCESS;
1657
1658 /****************************************************************
1659 *** Following procedure is associated to workflow activity. **
1660 *** This procedure set the mode os signature process. **
1661 *** If 'WF_SOURCE_APPLICATION_TYPE' is "DB" and Deferred **
1662 *** mode is allowed then we should not pop up List of signers **
1663 *** page. In this case This procedure simulates done button **
1664 *** on list of signers page. **
1665 *****************************************************************/
1666
1667 PROCEDURE IS_IT_TOTAL_OFFLINE(
1668 p_itemtype IN VARCHAR2,
1669 p_itemkey IN VARCHAR2,
1670 p_actid IN NUMBER,
1671 p_funcmode IN VARCHAR2,
1672 p_resultout OUT NOCOPY VARCHAR2
1673 )
1674 IS
1675
1676 L_DOCUMENT_ID NUMBER;
1677 l_wf_timeout NUMBER ;
1678 l_wf_timeout_interval NUMBER ;
1679
1680 L_SOURCE_APPL_TYPE VARCHAR2(80) ;
1681 L_DEFER_MODE_ALLOWED VARCHAR2(30);
1682 l_plsql_clob_api VARCHAR2(2000);
1683 l_plsql_api VARCHAR2(2000);
1684 l_history_api VARCHAR2(2000);
1685 L_event_name VARCHAR2(240);
1686
1687 l_message VARCHAR2(2000);
1688 l_return_status VARCHAR2(1);
1689 l_msg_count NUMBER;
1690 l_msg_data VARCHAR2(240);
1691 l_document_rec edr_psig_documents%ROWTYPE;
1692 l_doc_param_tbl EDR_EvidenceStore_PUB.Params_tbl_type;
1693 l_sig_tbl EDR_EvidenceStore_PUB.Signature_tbl_type;
1694 l_erec_template_type varchar2(256);
1695 --Bug 4160412: Start
1696 l_signature_mode VARCHAR2(80);
1697 --Bug 4160412: End
1698
1699 BEGIN
1700
1701 --Bug 4074173 : start
1702
1703 --Bug: 3499311 Start - Specify Number Format in TO_NUMBER
1704 --Bug: 3903471 : Start
1705 --Old versions do not use #ERECORD_ID parameter.
1706 --Hence for backward compatibility we should verify with EDR_PSIG_DOC_ID attribute.
1707 --If the attribute does not exist, then #ERECORD_ID should be used.
1708 L_DOCUMENT_ID := nvl(GET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype, p_itemkey,'EDR_PSIG_DOC_ID'),
1709 TO_NUMBER(GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#ERECORD_ID'),'999999999999.999999'));
1710 --Bug 3903471 : End
1711
1712 l_wf_timeout := TO_NUMBER(FND_PROFILE.VALUE('EDR_WF_TIMEOUT'),'999999999999.999999');
1713 l_wf_timeout_interval := TO_NUMBER(FND_PROFILE.VALUE('EDR_WF_TIMEOUT_INTERVAL'),'999999999999.999999');
1714 --Bug : 3499311 End.
1715
1716 L_SOURCE_APPL_TYPE := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_SOURCE_APPLICATION_TYPE');
1717 L_DEFER_MODE_ALLOWED := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'DEFERRED');
1718 l_plsql_clob_api := 'PLSQLCLOB:wf_render.XML_STYLE_SHEET/#PSIG_EVENT:&'||'#NID';
1719 l_plsql_api := 'PLSQL:edr_xdoc_util_pkg.get_ntf_message_body/' || L_DOCUMENT_ID;
1720 l_history_api := 'PLSQL:EDR_UTILITIES.EDR_NTF_HISTORY/'||p_itemkey;
1721
1722 --Bug 4074173 : end
1723
1724 --Bug 4160412: Start
1725 --Obtain the value of the signature mode.
1726 l_signature_mode := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,EDR_CONSTANTS_GRP.G_SIGNATURE_MODE);
1727 --Bug 4160412: End
1728
1729 IF p_funcmode='RUN' THEN
1730 /* Set process Owner SKARIMIS for BLAF standard */
1731 wf_engine.setitemowner
1732 (ITEMTYPE=>p_itemtype,
1733 ITEMKEY=>p_itemkey,
1734 OWNER=>GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,'#WF_SIGN_REQUESTER'));
1735
1736 l_plsql_clob_api := l_plsql_clob_api ||'/'||
1737 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'TEXT_XSLNAME')||'/'||
1738 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'TEXT_XSLVERSION')||'/'||
1739 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'APPLICATION_CODE') ||'/'||
1740 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'HTML_XSLNAME')||'/'||
1741 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'HTML_XSLVERSION')||'/'||
1742 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'APPLICATION_CODE');
1743
1744
1745 -- Bug 3170251 : Start- Check if EREC_TEMPLATE TYPE IS XSL or RTF and based on that decide the ERECORD TEXT Content
1746 -- and Notification Subject Token
1747 l_erec_template_type := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey, 'EREC_TEMPLATE_TYPE');
1748
1749 if (l_erec_template_type = 'RTF') then
1750 FND_MESSAGE.SET_NAME('EDR','EDR_WF_EREC_RTF_NTF_SUB');
1751 FND_MESSAGE.SET_TOKEN('ERECORD_ID',l_document_id);
1752
1753 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,'WF_MSG_SUB_EREC_TOKEN',FND_MESSAGE.GET());
1754 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,'#WF_ERECORD_TEXT', l_plsql_api);
1755 else
1756 FND_MESSAGE.SET_NAME('EDR','EDR_WF_EREC_XSL_NTF_SUB');
1757 FND_MESSAGE.SET_TOKEN('ERECORD_ID',l_document_id);
1758
1759 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,'WF_MSG_SUB_EREC_TOKEN',FND_MESSAGE.GET());
1760 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,'#WF_ERECORD_TEXT', l_plsql_clob_api);
1761 end if;
1762 -- Bug 3170251 : End
1763
1764 L_event_name := Get_event_disp_name(p_itemkey);
1765 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,
1766 p_itemkey,
1767 'PSIG_EVENT_NAME',
1768 L_EVENT_NAME);
1769
1770 /* Get the eRecrod */
1771 EDR_EvidenceStore_PUB.Get_DocumentDetails(p_api_version =>1.0,
1772 x_return_status => l_return_status,
1773 x_msg_count => l_msg_count,
1774 x_msg_data => l_msg_data,
1775 P_DOCUMENT_ID => L_DOCUMENT_ID,
1776 x_document_rec => l_document_rec,
1777 x_doc_parameters_tbl => l_doc_param_tbl,
1778 x_signatures_tbl => l_sig_tbl);
1779
1780 --Bug 3903471 : Start
1781 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,
1782 p_itemkey,
1783 'PSIG_TIMEZONE' ,
1784 l_document_rec.PSIG_TIMEZONE);
1785 --Bug 3903471 : End
1786
1787
1788 -- BUG 3271711 : Start Calling FND_DATE.DATE_TO_DISPLAYDT with server timezone
1789 -- Commenting the orignal code
1790 /*
1791 wf_engine.setitemattrtext(itemtype => p_itemtype,
1792 itemkey => p_itemkey,
1793 aname =>'PSIG_TIMESTAMP' ,
1794 avalue => FND_DATE.DATE_TO_DISPLAYDT(l_document_rec.PSIG_TIMESTAMP));
1795 */
1796 --Bug 3903471 : Start
1797 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,
1798 p_itemkey,
1799 'PSIG_TIMESTAMP' ,
1800 FND_DATE.DATE_TO_DISPLAYDT(l_document_rec.PSIG_TIMESTAMP, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE));
1801 --Bug 3903471 : End
1802
1803 -- BUG 3271711 : End
1804
1805 /* UPDATE EDR_ERECORDS
1806 SET EVENT_TIMEZONE = fnd_timezones.GET_SERVER_TIMEZONE_CODE
1807 Replaced the profile option to EDR_SERVER_ZONE
1808 SET EVENT_TIMEZONE = fnd_profile.VALUE('EDR_SERVER_TIMEZONE')
1809 WHERE event_id = p_itemkey;*/
1810
1811 -- Converting days into minutes
1812
1813 l_wf_timeout := round(l_wf_timeout * 60);
1814
1815 SET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype,
1816 p_itemkey,
1817 '#WF_NOTIFICATION_TIMEOUT',
1818 l_wf_timeout);
1819
1820 SET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype,
1821 p_itemkey,
1822 'TIMEOUT_RESEND_COUNT',
1823 l_wf_timeout_interval);
1824
1825 l_message := FND_MESSAGE.GET_STRING('EDR','EDR_PSIG_PROCESS_ABORTED');
1826
1827 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,
1828 p_itemkey,
1829 '#WF_PAGEFLOW_MESSAGE',
1830 l_message);
1831
1832 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,
1833 p_itemkey,
1834 'SIGNATURE_HISTORY',
1835 l_history_api);
1836
1837 IF L_SOURCE_APPL_TYPE = 'DB' and L_DEFER_MODE_ALLOWED ='Y' THEN
1838 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_CURRENT_MODE','OFFLINE');
1839 p_resultout := 'COMPLETE:TOTAL_OFFLINE';
1840 --Bug 2637353: Start
1841 --Set the result out value for MSCA source type.
1842 ELSIF L_SOURCE_APPL_TYPE='MSCA' then
1843 p_resultout := 'COMPLETE:MSCA';
1844
1845 --Bug 4160412: Start
1846
1847 --Bug 4543216: Start
1848 ELSIF L_SIGNATURE_MODE = EDR_CONSTANTS_GRP.G_ERES_LITE then
1849 --Bug 4543216: End
1850
1851 p_resultout := 'COMPLETE:LITE_MODE';
1852 --Bug 4160412: End
1853
1854 --Bug 2637353: End
1855 ELSE
1856 p_resultout := 'COMPLETE:NOT_TOTAL_OFFLINE';
1857 END IF;
1858 END IF;
1859 END IS_IT_TOTAL_OFFLINE;
1860
1861 PROCEDURE CHECK_TIMEOUT(
1862 p_itemtype IN VARCHAR2,
1863 p_itemkey IN VARCHAR2,
1864 p_actid IN NUMBER,
1865 p_funcmode IN VARCHAR2,
1866 p_resultout OUT NOCOPY VARCHAR2 ) IS
1867
1868 l_wf_timeout NUMBER;
1869 l_wf_temp_timeout NUMBER;
1870 l_item_key VARCHAR2(240);
1871 l_message VARCHAR2(2000);
1872
1873 /* This Cursor is to build the temporary group for sending timeout notification */
1874 CURSOR GET_USER is
1875 SELECT distinct USER_NAME from EDR_ESIGNATURES where to_char(event_id)=l_item_key;
1876
1877 l_userlist VARCHAR2(4000);
1878 l_requester VARCHAR2(4000);
1879 l_user varchar2(32);
1880 l_display_name varchar2(240);
1881 l_group varchar2(240);
1882 l_ESIGN_ID VARCHAR2(30);
1883
1884 BEGIN
1885
1886 --Bug 4074173 : start
1887 l_wf_timeout := GET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype, p_itemkey,'TIMEOUT_RESEND_COUNT');
1888 l_wf_temp_timeout := GET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype, p_itemkey,'TEMP_RESEND_COUNT');
1889 l_item_key := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_ITEMKEY');
1890
1891 l_userlist := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_SIGN_REQUESTER');
1892 l_requester := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_SIGN_REQUESTER');
1893 l_display_name :='eSignature Group';
1894 l_group :='EDRPSIG_ROLE';
1895 l_ESIGN_ID := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_ESIGN_ROW_ID');
1896 --Bug 4074173 : end
1897
1898 IF p_funcmode='RUN' THEN
1899 /* Figure out if the count exceeded */
1900
1901 if l_wf_temp_timeout >= l_wf_timeout then
1902
1903 /* Count Reached, prepare role */
1904 /* SKARIMIS Get the user List */
1905 OPEN GET_USER;
1906 LOOP
1907 fetch GET_USER into l_user;
1908 EXIT when GET_USER%NOTFOUND;
1909 IF l_requester <> l_user THEN
1910 l_userlist:=l_userlist||','||l_user;
1911 END IF;
1912 END LOOP;
1913 CLOSE GET_USER;
1914
1915 /* Create ADHOC group */
1916
1917 If (wf_directory.getRoleDisplayName('EDRPSIG_ROLE') is NULL) then
1918 /* Start Creating the Role */
1919
1920 wf_directory.CreateAdHocRole(role_name=>l_group,
1921 role_display_name=>l_display_name,
1922 role_users=>l_userlist,
1923 expiration_date=>NULL);
1924 ELSE
1925 wf_directory.RemoveUsersFromAdHocRole(role_name=>'EDRPSIG_ROLE',
1926 role_users=>NULL);
1927 wf_directory.AddUsersToAdHocRole(role_name=>'EDRPSIG_ROLE',
1928 role_users=>l_userlist);
1929 END IF;
1930
1931 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,
1932 'ESIG_GROUP' ,
1933 'EDRPSIG_ROLE');
1934 UPDATE EDR_ESIGNATURES
1935 SET SIGNATURE_STATUS = 'TIMEDOUT'
1936 WHERE SIGNATURE_ID = l_ESIGN_ID;
1937 p_resultout := 'COMPLETE:Y';
1938 ELSE
1939 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'TEMP_RESEND_COUNT',l_wf_temp_timeout+1);
1940 p_resultout := 'COMPLETE:N';
1941 END IF;
1942 END IF;
1943 END CHECK_TIMEOUT;
1944
1945 /************************************************************************************
1946 **** Following Procedure is used by sign-on screen. it accepts notification id ***
1947 **** and returns workflow item type ane item key these values are used to set ***
1948 **** page context in offline case. ***
1949 *************************************************************************************/
1950
1951 PROCEDURE getItemKey(p_notif in varchar2,
1952 p_item_key out NOCOPY varchar2,
1953 p_item_type out NOCOPY varchar2) is
1954 cursor getItemKey is
1955 select ITEM_KEY, ITEM_TYPE
1956 from WF_ITEM_ACTIVITIES_HISTORY_V
1957 where NOTIFICATION_ID = p_notif;
1958 BEGIN
1959 open getItemKey;
1960 fetch getItemKey into p_item_key,p_item_type;
1961 close getItemKey;
1962 END;
1963
1964 --Bug 3072401: Start
1965 PROCEDURE SEND_FINAL_APPROVAL_NTF
1966 ( p_itemtype VARCHAR2,
1967 p_itemkey VARCHAR2,
1968 p_actid NUMBER,
1969 p_funcmode VARCHAR2,
1970 p_resultout OUT NOCOPY VARCHAR2
1971 )
1972 AS
1973 l_return_status VARCHAR2(25);
1974 l_event_id NUMBER;
1975 l_signature_status VARCHAR2(25);
1976 L_CURR_MODE VARCHAR2(30);
1977
1978 BEGIN
1979
1980 --Bug 4074173 : start
1981 l_return_status := G_NO;
1982 l_event_id := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_ITEMKEY');
1983 L_CURR_MODE := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_CURRENT_MODE');
1984 --Bug 4074173 : end
1985
1986 --get the status of the signature process
1987 select ERECORD_SIGNATURE_STATUS into l_signature_status
1988 from EDR_ERECORDS
1989 WHERE EVENT_ID = l_event_id;
1990
1991 if (l_signature_status = G_SUCCESS_STATUS and L_CURR_MODE='OFFLINE') then
1992 l_return_status := G_YES;
1993 end if;
1994
1995 p_resultout := l_return_status;
1996
1997 END SEND_FINAL_APPROVAL_NTF;
1998
1999 PROCEDURE SEND_INDV_APPROVAL_NTF
2000 ( p_itemtype VARCHAR2,
2001 p_itemkey VARCHAR2,
2002 p_actid NUMBER,
2003 p_funcmode VARCHAR2,
2004 p_resultout OUT NOCOPY VARCHAR2
2005 )
2006 AS
2007 l_return_status VARCHAR2(25);
2008
2009 l_event_id NUMBER;
2010 l_send_individual_ntf VARCHAR2(10);
2011 l_requester VARCHAR2(4000);
2012
2013 -- Bug 3315185 : Added to store requester id to fetch profile option
2014 l_requester_id number;
2015
2016 BEGIN
2017
2018 --Bug 4074173 : start
2019 l_return_status := G_NO;
2020 l_event_id := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_ITEMKEY');
2021 l_requester := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_SIGN_REQUESTER');
2022 --Bug 4074173 : end
2023
2024 -- Bug 3315185 : Modified profile option access level to user level
2025 -- for accessing the requester's preference first.
2026
2027 --read the profile option to find out when the requester needs the notification
2028 --l_send_individual_ntf := fnd_profile.value('EDR_INDIVIDUAL_APPROVAL_NTF');
2029
2030 select user_id into l_requester_id from fnd_user where user_name = trim(l_requester);
2031
2032 l_send_individual_ntf := fnd_profile.value_specific(NAME => 'EDR_INDIVIDUAL_APPROVAL_NTF',
2033 USER_ID => l_requester_id );
2034
2035
2036 -- If the user has not set this profile option, access it at site level
2037 if ( l_send_individual_ntf is null) then
2038 l_send_individual_ntf := fnd_profile.value_specific( NAME => 'EDR_INDIVIDUAL_APPROVAL_NTF');
2039 end if;
2040
2041 -- Bug 3315185 : End
2042
2043 if (l_send_individual_ntf = 'Y') then
2044 l_return_status := G_YES;
2045 end if;
2046
2047 p_resultout := l_return_status;
2048
2049 END SEND_INDV_APPROVAL_NTF;
2050 --Bug 3072401: End
2051
2052
2053 --Bug 3207385: Start
2054 PROCEDURE RAISE_APPR_COMPLETION_EVT
2055 ( p_itemtype VARCHAR2,
2056 p_itemkey VARCHAR2,
2057 p_actid NUMBER,
2058 p_funcmode VARCHAR2,
2059 p_resultout OUT NOCOPY VARCHAR2
2060 )
2061
2062 is
2063
2064 --This variable would hold the event object store in workflow.
2065 l_psig_event wf_event_t;
2066
2067 --This variable would hold the original event name.
2068 l_orig_event_name varchar2(240);
2069
2070 --This variable would hold the original event key.
2071 l_orig_event_key varchar2(240);
2072
2073 --This would hold the name of the approval completion event.
2074 l_event_name varchar2(240);
2075
2076 --This would hold the name of the event key set while raising the approval completion event.
2077 l_event_key varchar2(240);
2078
2079 --This would hold the e-record ID of the event which is being processed.
2080 l_erecord_id varchar2(128);
2081
2082 --This would hold the document status as it exists in the evidence store for the e-record identified
2083 --by the above e-record ID.
2084 l_event_status varchar2(32);
2085
2086 l_param_list WF_PARAMETER_LIST_T;
2087
2088 BEGIN
2089
2090 l_psig_event := wf_engine.getItemAttrEvent(p_itemtype, p_itemkey,'#PSIG_EVENT');
2091
2092 l_orig_event_name := l_psig_event.getEventName();
2093
2094 l_orig_event_key := l_psig_Event.getEventKey();
2095
2096 --Obtain the e-record and event status from workflow from workflow.
2097 l_erecord_id := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#ERECORD_ID');
2098 l_event_status := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,EDR_CONSTANTS_GRP.G_FINAL_DOCUMENT_STATUS);
2099
2100
2101 l_event_name := EDR_CONSTANTS_GRP.G_APPROVAL_COMPLETION_EVT;
2102 l_event_key := l_erecord_id;
2103
2104 --Set the payload parameters for the approval completion event.
2105
2106 wf_event.addParameterToList(EDR_CONSTANTS_GRP.G_ORIGINAL_EVENT_NAME,l_orig_event_name,l_param_list);
2107
2108 wf_event.addParameterToList(EDR_CONSTANTS_GRP.G_ORIGINAL_EVENT_KEY,l_orig_event_key,l_param_list);
2109
2110 wf_event.addParameterToList(EDR_CONSTANTS_GRP.G_ERECORD_ID,l_erecord_id,l_param_list);
2111
2112 wf_event.addParameterToList(EDR_CONSTANTS_GRP.G_EVENT_STATUS,l_event_status,l_param_list);
2113
2114 --Raise the approval completion event.
2115 WF_EVENT.RAISE3(L_EVENT_NAME,
2116 L_EVENT_KEY,
2117 null,
2118 L_PARAM_LIST,
2119 NULL);
2120
2121 END RAISE_APPR_COMPLETION_EVT;
2122 --Bug 3207385: End
2123 -- Bug 5166723 : start
2124 PROCEDURE MOVE_WF_ACTIVITY(P_ITEMTYPE IN VARCHAR2,
2125 P_ITEMKEY IN VARCHAR2,
2126 P_CURRENT_ACTIVITY IN VARCHAR2,
2127 P_RESULT_CODE IN VARCHAR2)
2128 IS
2129 PRAGMA AUTONOMOUS_TRANSACTION;
2130 BEGIN
2131 -- P_CURRENT_ACTIVITY => PSIG_ESIGN_SIGNER_LIST
2132 FND_WF_ENGINE.COMPLETEACTIVITY(P_ITEMTYPE, P_ITEMKEY, P_CURRENT_ACTIVITY , P_RESULT_CODE);
2133 COMMIT;
2134 EXCEPTION
2135 WHEN OTHERS THEN
2136 ROLLBACK;
2137 --Diagnostics Start
2138 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
2139 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
2140 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_PSIG_PAGE_FLOW');
2141 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','MOVE_WF_ACTIVITY');
2142 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2143 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
2144 'edr.plsql.EDR_PSIG_PAGE_FLOW.MOVE_WF_ACTIVITY',
2145 FALSE
2146 );
2147 end if;
2148 --Diagnostics End
2149 APP_EXCEPTION.RAISE_EXCEPTION;
2150 END MOVE_WF_ACTIVITY;
2151 -- Bug 5166723 : End
2152 END EDR_PSIG_PAGE_FLOW;