[Home] [Help]
PACKAGE BODY: APPS.EDR_PSIG_PAGE_FLOW
Source
1 PACKAGE BODY EDR_PSIG_PAGE_FLOW AS
2 /* $Header: EDRESGPB.pls 120.13.12020000.3 2013/02/21 02:55:19 qzeng 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_requester VARCHAR2(4000) ;
775 l_user varchar2(100);
776 l_userlist WF_DIRECTORY.UserTable;
777 ui number := 1;
778
779 l_display_name varchar2(240);
780 l_group varchar2(240);
781 l_ESIGN_ID VARCHAR2(30);
782
783 /* this curosr is to get original approve */
784 --Bug 3214398: start
785 /*Get User_Name alongwith original recipient*/
786 /*
787 CURSOR original_recipient is
788 SELECT ORIGINAL_RECIPIENT from EDR_ESIGNATURES where
789 signature_id=l_esign_id;
790 */
791 CURSOR signer_detail is
792 SELECT ORIGINAL_RECIPIENT, USER_NAME from EDR_ESIGNATURES where
793 signature_id = l_esign_id;
794 l_user_name varchar2(200);
795 l_overriding_approver varchar2(200);
796 l_overriding_comments varchar2(4000);
797 --Bug 3214398: end
798
799 l_erec_template_type varchar2(256);
800 l_responder varchar2(200);
801 l_original_recipient varchar2(200);
802 l_comments varchar2(4000);
803 /* End of enhancemtns */
804 L_NTF_RESPOND_ROLE varchar2(320);
805 L_NTF_MESSAGE_TYPE varchar2(240);
806 L_NTF_MESSAGE_NAME varchar2(240);
807 L_NTF_PRIORITY varchar2(240);
808 L_NTF_DUE_DATE varchar2(240);
809 L_NTF_STATUS varchar2(240);
810
811 L_RESPONSE_READ varchar2(240);
812
813 --Bug 2674799 : start
814 l_count number;
815
816 L_CURR_SIGN_LEVEL number;
817
818
819 lp_itemtype varchar2(50);
820 lp_itemkey varchar2(50);
821 lp_status varchar2(50);
822 lp_result varchar2(50);
823 lp_notification_id varchar2(50);
824 lp_curr number;
825 lc_itemkey varchar2(50);
826
827
828 CURSOR CURR_LIST_SIGNERS IS
829 SELECT event_name, SIGNATURE_ID,NVL(ORIGINAL_RECIPIENT,USER_NAME) USER_NAME
830 FROM EDR_ESIGNATURES
831 WHERE EVENT_ID = lp_itemkey
832 --Bug 4272262: Start
833 --Convert signature sequence to a number value.
834 AND to_number(SIGNATURE_SEQUENCE,'999999999999.999999') = L_CURR_SIGN_LEVEL
835 --Bug 4272262: End
836 AND SIGNATURE_STATUS='PENDING';
837
838 SIGNER_LIST_REC CURR_LIST_SIGNERS%ROWTYPE;
839
840 --Bug 2674799 : end
841
842
843 --Bug 4577122: Start
844 l_ignore_wfattr_notfound boolean := true;
845 l_voting_regime varchar2(1);
846 --Bug 4577122: End
847
848 BEGIN
849
850 --Bug 4074173 : start
851
852 l_doc_format := 'text/plain';
853 l_item_type := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_ITEMTYPE');
854 l_item_key := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_ITEMKEY');
855 l_userlist(1) := GET_WF_ITEM_ATTRIBUTE_TEXT(l_item_type, l_item_key,'#WF_SIGN_REQUESTER');
856 l_requester := GET_WF_ITEM_ATTRIBUTE_TEXT(l_item_type, l_item_key,'#WF_SIGN_REQUESTER');
857
858 l_display_name :='eSignature Group';
859 l_group :='EDRPSIG_ROLE';
860 l_ESIGN_ID := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_ESIGN_ROW_ID');
861
862 l_event_xml := null;
863 l_event_text := null;
864
865 --Bug 4074173 : end
866
867 -- Bug 4577122: Start
868 l_voting_regime := wf_engine.GetitemAttrText
869 (p_itemtype, p_itemkey,'AME_VOTING_REGIME',
870 l_ignore_wfattr_notfound);
871 --Bug 4577122: End
872 -- Bug 5120197 : start
873 IF(p_funcmode = 'TRANSFER' OR p_funcmode = 'FORWARD') THEN
874 OPEN signer_detail;
875 FETCH signer_detail into l_original_recipient, l_user_name;
876 CLOSE signer_detail;
877
878 -- Bug 3902969 : Start
879 -- Check if the recipient l_user_name is same as WF_ENGINE.CONTEXT_TEXT
880 -- if they are differnt then only call find wf ntf recipients API.
881
882 IF( l_user_name = WF_ENGINE.CONTEXT_USER) THEN
883
884 WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','UPDATE_NOTIF_RESPONSE',
885 p_itemtype,p_itemkey,
886 FND_MESSAGE.GET_STRING('EDR','EDR_EREC_REASSIGN_ERR'));
887 raise_application_error(-20003,FND_MESSAGE.GET_STRING('EDR','EDR_EREC_REASSIGN_ERR'));
888 END IF;
889 END IF;
890
891 -- Bug 5120197 : END
892
893 IF (p_funcmode = 'RESPOND') THEN
894
895 l_SIGNER_TYPE := GET_NOTIF_ITEM_ATTR_TEXT(wf_engine.context_nid,'WF_SIGNER_TYPE');
896 l_signer_reason := GET_NOTIF_ITEM_ATTR_TEXT(wf_engine.context_nid,'REASON_CODE');
897 IF l_signer_reason IS null OR l_SIGNER_TYPE IS NULL THEN
898 WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','UPDATE_NOTIF_RESPONSE',p_itemtype,p_itemkey,
899 'Signer Comment and Signing Reason code can not be null.');
900 raise EDR_PSIG_NOT_NULL;
901 END IF;
902
903 -- Bug 3170251 : Check if user has selected yes for reading the attached eRecord
904 OPEN GET_USER_RESPONSE_RESULT;
905 FETCH GET_USER_RESPONSE_RESULT INTO GET_USER_RESPONSE_RESULT_REC;
906 IF GET_USER_RESPONSE_RESULT%FOUND THEN
907 IF GET_USER_RESPONSE_RESULT_REC.LOOKUP_CODE <> '#SIG_CANCEL' THEN
908 -- Begin Bug 3847779
909 -- The getattrtext has been wrapped in a being and end block to trap wf exception if
910 -- attribute is not found for previous notificaitons backward compitability.
911 L_RESPONSE_READ := GET_NOTIF_ITEM_ATTR_TEXT(wf_engine.context_nid, 'READ_RESPONSE');
912 -- End Bug 3847779
913 IF (L_RESPONSE_READ = 'N') THEN
914 WF_CORE.CONTEXT('EDR_PSIG_PAGE_FLOW', 'UPDATE_NOTIF_RESPONSE',p_itemtype, p_itemkey,
915 FND_MESSAGE.GET_STRING('EDR','EDR_EREC_NOT_REVIEWED_ERR'));
916 raise_application_error(-20002,FND_MESSAGE.GET_STRING('EDR','EDR_EREC_NOT_REVIEWED_ERR'));
917 END IF;
918 END IF;
919
920
921 --BUg 2674799 : start
922 lp_curr := INSTR(p_itemkey,'-');
923 lp_itemkey := SUBSTR(p_itemkey,0,lp_curr-1);
924
925 --Bug 4577122 : start
926
927 IF GET_USER_RESPONSE_RESULT_REC.LOOKUP_CODE = 'REJECTED' or
928 (GET_USER_RESPONSE_RESULT_REC.LOOKUP_CODE = 'APPROVED' AND
929 L_VOTING_REGIME = ame_util.firstApproverVoting) THEN
930 --Bug 4577122 : end
931
932 --Bug 4272262: Start
933 --Convert signature sequence to a number value.
934 SELECT MIN(to_number(SIGNATURE_SEQUENCE,'999999999999.999999')) INTO L_CURR_SIGN_LEVEL
935 from EDR_ESIGNATURES
936 WHERE EVENT_ID =lp_itemkey
937 AND SIGNATURE_STATUS = 'PENDING' ;
938 --Bug 4272262: End
939
940 OPEN CURR_LIST_SIGNERS;
941 LOOP
942
943 FETCH CURR_LIST_SIGNERS INTO SIGNER_LIST_REC;
944 EXIT WHEN CURR_LIST_SIGNERS%NOTFOUND;
945
946 lc_itemkey := lp_itemkey || '-' || SIGNER_LIST_REC.SIGNATURE_ID;
947
948 WF_ENGINE.ITEMSTATUS ( itemtype => p_itemtype,
949 itemkey => lc_itemkey,
950 status => lp_status,
951 result => lp_result);
952
953 if(lc_itemkey <> p_itemkey and lp_status = 'ACTIVE' ) then
954 BEGIN
955
956 select notification_id into lp_notification_id
957 from wf_item_activity_statuses where item_type=p_itemtype
958 and item_key = lc_itemkey and notification_id is not null
959 and activity_status ='NOTIFIED';
960
961 EXCEPTION WHEN NO_DATA_FOUND THEN
962 -- If there are No notifications to be cancelled, then nothing is to be cancelled. Hence No action needed.
963 null;
964 WHEN OTHERS THEN
965 WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','UPDATE_NOTIF_RESPONSE',p_itemtype,p_itemkey,l_error_num,L_ERROR_MESG);
966 raise;
967 END;
968
969 if lp_notification_id is not null and lp_notification_id > 0 then
970 wf_notification.cancel(lp_notification_id);
971
972 end if;
973
974
975 WF_ENGINE.ABORTPROCESS(itemtype => p_itemtype,
976 itemkey => lc_itemkey
977 );
978
979 end if;
980
981 END LOOP;
982
983 CLOSE CURR_LIST_SIGNERS;
984
985 END IF; -- REJECTED
986
987 --Bug 2674799 : end
988
989 END IF;
990 CLOSE GET_USER_RESPONSE_RESULT;
991 -- Bug 3170251 : End
992
993 --Bug 2674799 : Start
994 /* SELECT count(*) INTO l_count
995 FROM EDR_ESIGNATURES
996 WHERE EVENT_ID = l_item_key
997 AND SIGNATURE_STATUS = 'REJECTED';
998
999 IF l_count > 0 then
1000 raise_application_error(-20002,FND_MESSAGE.GET_STRING('EDR','EDR_EREC_ALREADY_REJECTED'));
1001 END IF;
1002 */
1003 --Bug 2674799 : End
1004
1005 /* Following statements are to store data in PSIG evidance store */
1006 l_doc_id := GET_WF_ITEM_ATTRIBUTE_NUMBER(l_item_type, l_item_key,'EDR_PSIG_DOC_ID');
1007 IF l_doc_id is null THEN
1008 l_psig_event := wf_engine.getItemAttrEvent(p_itemtype, p_itemkey,'#PSIG_EVENT');
1009 l_event_xml := l_psig_event.getEventData();
1010 l_event_name := l_psig_event.getEventName();
1011 l_event_key := l_psig_event.getEventKey();
1012 l_doc_id := GET_WF_ITEM_ATTRIBUTE_TEXT(l_item_type, l_item_key,'PSIG_DOCUMENT_ID');
1013
1014 /* Fixed to take care of PDF format */
1015
1016 l_erec_template_type := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey, 'EREC_TEMPLATE_TYPE');
1017
1018 if (l_erec_template_type = 'RTF') then
1019 -- Bug 3474765 : Start
1020 /* Changing the file content type to be MIME Formatted */
1021 l_doc_format:='application/pdf';
1022 -- Bug 3474765 : End
1023 end if;
1024 /* END of PDF fix */
1025
1026 EDR_PSIG.updateDocument(P_PSIG_XML => l_event_xml,
1027 P_PSIG_DOCUMENT => l_event_xml,
1028 P_PSIG_DOCUMENTFORMAT => l_doc_format,
1029 P_PSIG_REQUESTER => GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_SIGN_REQUESTER'),
1030 P_PSIG_SOURCE => GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_SOURCE_APPLICATION_TYPE'),
1031 P_EVENT_NAME => l_event_name,
1032 P_EVENT_KEY => l_event_key,
1033 P_WF_NID => wf_engine.context_nid,
1034 P_DOCUMENT_ID => l_doc_id,
1035 P_ERROR => l_error_num,
1036 P_ERROR_MSG => L_ERROR_MESG);
1037 IF l_ERROR_NUM IS NOT NULL THEN
1038 RAISE EDR_PSIG_DOC_ERR;
1039 ELSE
1040 SET_WF_ITEM_ATTRIBUTE_NUMBER(l_item_type, l_item_key,'EDR_PSIG_DOC_ID',l_doc_id);
1041 l_paramlist:=l_psig_event.Parameter_List;
1042 IF (l_paramlist is not null) THEN
1043 FOR i IN l_paramlist.first .. l_paramlist.last LOOP
1044
1045 l_doc_params(i).param_name:=l_paramlist(i).GetName;
1046 l_doc_params(i).param_value:=l_paramlist(i).GetValue;
1047 l_doc_params(i).param_displayname:=l_paramlist(i).GetName;
1048 END LOOP;
1049
1050 EDR_PSIG.POSTDOCUMENTPARAMETER( P_DOCUMENT_ID =>l_doc_id,
1051 P_PARAMETERS =>l_doc_params,
1052 P_ERROR =>l_error_num,
1053 P_ERROR_MSG =>l_error_mesg);
1054 IF l_ERROR_NUM IS NOT NULL THEN
1055 RAISE EDR_PSIG_DOC_ERR;
1056 END IF;
1057 END IF; -- Param List Error
1058 END IF; -- Doc Error
1059 END IF; -- First Time
1060
1061 OPEN GET_USER_RESPONSE_RESULT;
1062 FETCH GET_USER_RESPONSE_RESULT INTO GET_USER_RESPONSE_RESULT_REC;
1063 IF GET_USER_RESPONSE_RESULT%FOUND THEN
1064 IF GET_USER_RESPONSE_RESULT_REC.LOOKUP_CODE <> '#SIG_CANCEL' THEN
1065 --Bug 3214398: Start
1066 --We need to get the user_name and pass the overriding details for user_name
1067 --to edr_psig.postSignature to avoid partial routing rules in GQ
1068 --In postSignature the overriding comments are appended so if there is no change
1069 --after the signer process has started there will be no impact
1070 --and if the routing has changed GQ will be populated with correct results
1071 /*
1072 OPEN original_recipient;
1073 FETCH original_recipient into l_original_recipient;
1074 CLOSE original_recipient;
1075 */
1076 OPEN signer_detail;
1077 FETCH signer_detail into l_original_recipient, l_user_name;
1078 CLOSE signer_detail;
1079
1080 -- Bug 3902969 : Start
1081 -- Check if the recipient l_user_name is same as WF_ENGINE.CONTEXT_TEXT
1082 -- if they are differnt then only call find wf ntf recipients API.
1083
1084 IF( l_user_name <> WF_ENGINE.CONTEXT_USER) THEN
1085
1086 EDR_STANDARD.FIND_WF_NTF_RECIPIENT(P_ORIGINAL_RECIPIENT => l_user_name,
1087 P_MESSAGE_TYPE => 'EDRPSIGF',
1088 P_MESSAGE_NAME => 'PSIG_EREC_MESSAGE_BLAF',
1089 P_RECIPIENT => l_overriding_approver,
1090 P_NTF_ROUTING_COMMENTS => l_overriding_comments,
1091 P_ERR_CODE => l_error_num,
1092 P_ERR_MSG => l_error_mesg);
1093
1094 END IF;
1095 -- Bug 3902969 : End
1096
1097 IF (l_ERROR_NUM > 0 ) THEN
1098 RAISE EDR_PSIG_DOC_ERR;
1099 END IF;
1100
1101 -- Bug 4190367 : Modifying to make use of wf_notification.getattrtext to populate Evidence store id.
1102 /*
1103 EDR_PSIG.postSignature(P_DOCUMENT_ID => l_doc_id,
1104 P_EVIDENCE_STORE_ID => wf_notification.getattrtext(wf_engine.context_nid,'#WF_SIG_ID'),
1105 P_USER_NAME => WF_ENGINE.context_text,
1106 P_USER_RESPONSE => GET_USER_RESPONSE_RESULT_REC.MEANING,
1107 P_SIGNATURE_ID => l_SIGNATURE_id,
1108 P_ORIGINAL_RECIPIENT => l_ORIGINAL_RECIPIENT,
1109 P_ERROR => l_error_num,
1110 P_ERROR_MSG => L_ERROR_MESG);
1111 */
1112
1113
1114 EDR_PSIG.postSignature(P_DOCUMENT_ID => l_doc_id,
1115 P_EVIDENCE_STORE_ID => wf_notification.getattrtext(wf_engine.context_nid,'#WF_SIG_ID'),
1116 P_USER_NAME => WF_ENGINE.context_text,
1117 P_USER_RESPONSE => GET_USER_RESPONSE_RESULT_REC.MEANING,
1118 P_SIGNATURE_ID => l_SIGNATURE_id,
1119 P_ORIGINAL_RECIPIENT => l_ORIGINAL_RECIPIENT,
1120 P_OVERRIDING_COMMENTS => l_overriding_comments,
1121 P_ERROR => l_error_num,
1122 P_ERROR_MSG => L_ERROR_MESG);
1123
1124 -- Bug 4190367 : End
1125 --Bug 3214398: end
1126
1127 IF l_ERROR_NUM IS NOT NULL THEN
1128 RAISE EDR_PSIG_DOC_ERR;
1129 END IF;
1130 i := 0;
1131 OPEN GET_RESPONSE_ATTR;
1132 LOOP
1133 FETCH GET_RESPONSE_ATTR INTO GET_RESPONSE_ATTR_REC;
1134 EXIT WHEN GET_RESPONSE_ATTR%NOTFOUND;
1135 i := i + 1;
1136 /* To support WF_NOTE attribute SKARIMIS*/
1137 IF GET_RESPONSE_ATTR_REC.Name = 'WF_NOTE' THEN
1138 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,
1139 p_itemkey,
1140 'SIGNERS_COMMENT',
1141 GET_RESPONSE_ATTR_REC.Meaning);
1142 GET_RESPONSE_ATTR_REC.Name:='SIGNERS_COMMENT';
1143 END IF;
1144 l_sign_params(i).param_name:=GET_RESPONSE_ATTR_REC.Name;
1145 l_sign_params(i).param_value:=GET_RESPONSE_ATTR_REC.Meaning;
1146 l_sign_params(i).param_displayname:=GET_RESPONSE_ATTR_REC.display_Name;
1147 END LOOP;
1148 CLOSE GET_RESPONSE_ATTR;
1149
1150 IF i > 0 THEN
1151 EDR_PSIG.postSignatureParameter(P_SIGNATURE_ID => l_SIGNATURE_id,
1152 P_PARAMETERS => l_sign_params,
1153 P_ERROR => l_error_num,
1154 P_ERROR_MSG => L_ERROR_MESG);
1155 IF l_ERROR_NUM IS NOT NULL THEN
1156 RAISE EDR_PSIG_DOC_ERR;
1157 END IF;
1158 END IF;
1159 END IF;
1160 END IF;
1161 CLOSE GET_USER_RESPONSE_RESULT;
1162
1163 /* Capture Current Notification Responder BUG Fix 2903607 SKARIMIS*/
1164 wf_notification.getinfo(
1165 NID =>wf_engine.context_nid,
1166 ROLE =>L_NTF_RESPOND_ROLE,
1167 MESSAGE_TYPE =>L_NTF_MESSAGE_TYPE,
1168 MESSAGE_NAME =>L_NTF_MESSAGE_NAME,
1169 PRIORITY =>L_NTF_PRIORITY,
1170 DUE_DATE =>L_NTF_DUE_DATE,
1171 STATUS =>L_NTF_STATUS);
1172
1173 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,
1174 p_itemkey,
1175 'NTF_RESPONDER',
1176 L_NTF_RESPOND_ROLE);
1177 END IF; -- Function Mode
1178
1179 IF (p_funcmode = 'RUN') THEN
1180
1181
1182 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,
1183 '#WF_SIGNER_ROLE_DISPLAY_NAME',
1184 WF_DIRECTORY.GETROLEDISPLAYNAME(WF_ENGINE.context_text));
1185 /* SKARIMIS Get the user List */
1186 OPEN GET_USER;
1187 LOOP
1188 fetch GET_USER into l_user;
1189 EXIT when GET_USER%NOTFOUND;
1190 if l_requester <> l_user then
1191 l_userlist(ui+1):=l_user;
1192 ui := ui +1;
1193 end if;
1194 END LOOP;
1195 CLOSE GET_USER;
1196
1197 /* Create ADHOC group */
1198
1199 If (wf_directory.getRoleDisplayName('EDRPSIG_ROLE') is NULL) then
1200 /* Start Creating the Role */
1201
1202 wf_directory.CreateAdHocRole2(role_name=>l_group,
1203 role_display_name=>l_display_name,
1204 role_users=>l_userlist,
1205 expiration_date=>NULL);
1206
1207 ELSE
1208 wf_directory.RemoveUsersFromAdHocRole(role_name=>'EDRPSIG_ROLE',
1209 role_users=>NULL);
1210
1211 wf_directory.AddUsersToAdHocRole2(role_name=>'EDRPSIG_ROLE',
1212 role_users=>l_userlist);
1213 END IF;
1214
1215 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,
1216 'ESIG_GROUP' ,
1217 'EDRPSIG_ROLE');
1218
1219 END IF; -- RUN function ends
1220
1221 IF (p_funcmode ='TIMEOUT') then
1222 wf_notification.getinfo(
1223 NID =>wf_engine.context_nid,
1224 ROLE =>L_NTF_RESPOND_ROLE,
1225 MESSAGE_TYPE =>L_NTF_MESSAGE_TYPE,
1226 MESSAGE_NAME =>L_NTF_MESSAGE_NAME,
1227 PRIORITY =>L_NTF_PRIORITY,
1228 DUE_DATE =>L_NTF_DUE_DATE,
1229 STATUS =>L_NTF_STATUS);
1230 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,
1231 p_itemkey,
1232 'NTF_RESPONDER',
1233 L_NTF_RESPOND_ROLE);
1234 END IF;
1235
1236 EXCEPTION WHEN EDR_PSIG_NOT_NULL THEN
1237 WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','UPDATE_NOTIF_RESPONSE',p_itemtype,p_itemkey,
1238 'Signer Comment and Signing Reason code can not be null.');
1239 raise;
1240 WHEN EDR_PSIG_DOC_ERR THEN
1241 WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','UPDATE_NOTIF_RESPONSE',p_itemtype,p_itemkey,
1242 l_error_num,L_ERROR_MESG);
1243 raise;
1244 WHEN EDR_PSIG_NOT_READ THEN
1245 WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','UPDATE_NOTIF_RESPONSE',p_itemtype,p_itemkey,
1246 FND_MESSAGE.GET_STRING('EDR','EDR_EREC_NOT_REVIEWED_ERR'));
1247 raise;
1248 WHEN OTHERS THEN
1249 WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','UPDATE_NOTIF_RESPONSE',p_itemtype,p_itemkey,
1250 l_error_num,L_ERROR_MESG);
1251 raise;
1252 END UPDATE_NOTIF_RESPONSE;
1253
1254 /******************************************************************************
1255 *** This procedure is associated with EDRESGPF workflow. This code will **
1256 *** execute when user clicks on Approve Button in response to notification **
1257 ******************************************************************************/
1258
1259 PROCEDURE NOTIF_APPROVED(
1260 p_itemtype IN VARCHAR2,
1261 p_itemkey IN VARCHAR2,
1262 p_actid IN NUMBER,
1263 p_funcmode IN VARCHAR2,
1264 p_resultout OUT NOCOPY VARCHAR2
1265 )
1266 IS
1267 l_ESIGN_ID VARCHAR2(30) ;
1268 l_EVENT_ID NUMBER ;
1269 l_SIGNER_TYPE VARCHAR2(30) ;
1270 l_signer_reason VARCHAR2(32) ;
1271 l_signer_comment VARCHAR2(4000) ;
1272 l_count number;
1273
1274 --Bug 4577122: Start
1275 l_ignore_wfattr_notfound boolean := true;
1276 l_voting_regime varchar2(1);
1277 --Bug 4577122: End
1278
1279 BEGIN
1280
1281 --Bug 4074173 : start
1282 l_ESIGN_ID := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_ESIGN_ROW_ID');
1283 l_EVENT_ID := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_ITEMKEY');
1284 l_SIGNER_TYPE := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'WF_SIGNER_TYPE');
1285 l_signer_reason := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'REASON_CODE');
1286 l_signer_comment := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'SIGNERS_COMMENT');
1287 --Bug 4074173 : end
1288
1289 --Bug 4577122: Start
1290 l_voting_regime := wf_engine.GetitemAttrText
1291 (p_itemtype, p_itemkey,'AME_VOTING_REGIME',
1292 l_ignore_wfattr_notfound);
1293 --Bug 4577122: End
1294
1295 IF p_funcmode = 'RUN' THEN
1296 UPDATE EDR_ESIGNATURES
1297 SET SIGNATURE_STATUS = 'APPROVED' ,
1298 SIGNATURE_TYPE = l_SIGNER_TYPE,
1299 SIGNATURE_REASON_CODE = l_signer_reason,
1300 SIGNATURE_TIMESTAMP = SYSDATE,
1301 SIGNER_COMMENTS = l_signer_comment
1302 WHERE SIGNATURE_ID = l_ESIGN_ID;
1303
1304 /* Verify all are Approved */
1305
1306 SELECT count(*) INTO l_count
1307 from EDR_ESIGNATURES
1308 WHERE EVENT_ID = l_event_id
1309 AND SIGNATURE_STATUS <> 'APPROVED' ;
1310
1311 --Bug 4577122: Start
1312 --IF l_count = 0 THEN
1313 IF (l_count = 0 or l_voting_regime = ame_util.firstApproverVoting) THEN
1314 --Bug 4577122: End
1315
1316 /* if all signers are approved set the status of edr_erecords to presuccess.
1317 this will be used on list of signer screen to handle done button. */
1318
1319 UPDATE EDR_ERECORDS
1320 SET ERECORD_SIGNATURE_STATUS = 'PRESUCCESS'
1321 WHERE EVENT_ID = l_event_id;
1322
1323
1324 --Bug 4577122: Start
1325 --clear out the status etc fields of rest of the edr_esignatures
1326 if (l_voting_regime = ame_util.firstApproverVoting) then
1327 update edr_esignatures
1328 set signature_status = null
1329 where event_id = l_event_id
1330 and signature_status = 'PENDING';
1331 end if;
1332 --Bug 4577122: End
1333
1334 END IF;
1335 -- p_resultout := 'COMPLETE:';
1336 END IF;
1337
1338 END NOTIF_APPROVED;
1339
1340 /*******************************************************************************
1341 *** This procedure is associated with EDRESGPF workflow. This code will **
1342 *** execute when user clicks on Cancel Button in response to notification **
1343 *** No processing is done here. **
1344 *******************************************************************************/
1345
1346 PROCEDURE NOTIF_CANCELED(
1347 p_itemtype IN VARCHAR2,
1348 p_itemkey IN VARCHAR2,
1349 p_actid IN NUMBER,
1350 p_funcmode IN VARCHAR2,
1351 p_resultout OUT NOCOPY VARCHAR2
1352 )
1353 IS
1354 BEGIN
1355 IF (p_funcmode = 'RUN') THEN
1356 null;
1357 -- p_resultout := 'COMPLETE:';
1358 END IF;
1359 END NOTIF_CANCELED;
1360
1361 /*****************************************************************************
1362 *** This procedure is associated with EDRESGPF workflow. This code will **
1363 *** executed when use click on Reject Button in response to notification **
1364 *****************************************************************************/
1365
1366 PROCEDURE NOTIF_REJECTED(
1367 p_itemtype IN VARCHAR2,
1368 p_itemkey IN VARCHAR2,
1369 p_actid IN NUMBER,
1370 p_funcmode IN VARCHAR2,
1371 p_resultout OUT NOCOPY VARCHAR2
1372 )
1373 IS
1374 l_ESIGN_ID VARCHAR2(30);
1375 l_EVENT_ID NUMBER;
1376 l_SIGNER_TYPE VARCHAR2(30);
1377 l_signer_reason VARCHAR2(32);
1378 l_signer_comment VARCHAR2(4000);
1379 l_count number;
1380 BEGIN
1381
1382 --Bug 4074173 : start
1383 l_ESIGN_ID := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_ESIGN_ROW_ID');
1384 l_EVENT_ID := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_ITEMKEY');
1385 l_SIGNER_TYPE := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'WF_SIGNER_TYPE');
1386 l_signer_reason := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'REASON_CODE');
1387 l_signer_comment := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'SIGNERS_COMMENT');
1388 --Bug 4074173 : end
1389
1390
1391
1392 IF p_funcmode = 'RUN' THEN
1393 /* Skarimis */
1394
1395 wf_directory.RemoveUsersFromAdHocRole(role_name=>'EDRPSIG_ROLE', role_users=>NULL);
1396
1397 /* Skarimis */
1398
1399 /* Change following code to derieve Timezone info */
1400 UPDATE EDR_ESIGNATURES
1401 SET SIGNATURE_STATUS = 'REJECTED' ,
1402 SIGNATURE_TYPE = l_SIGNER_TYPE,
1403 SIGNATURE_REASON_CODE = l_signer_reason,
1404 SIGNATURE_TIMESTAMP = SYSDATE,
1405 SIGNER_COMMENTS = l_signer_comment
1406 WHERE SIGNATURE_ID = l_ESIGN_ID;
1407
1408 /* set the status of edr_erecords to pre rejected. this will be used on list of signer screen.
1409 to handle done button. */
1410
1411 UPDATE EDR_ERECORDS
1412 SET ERECORD_SIGNATURE_STATUS = 'PREREJECTED'
1413 WHERE EVENT_ID = l_event_id;
1414
1415 -- p_resultout := 'COMPLETE:';
1416 END IF;
1417 END NOTIF_REJECTED;
1418
1419 /************************************************************************************
1420 **** Following Procedure is used by list of signer screen. it accepts item_type **
1421 **** and Item_key returns current open notification id this is used to build **
1422 **** Notification URL. This is required as OANavigation.nextPage does not return **
1423 **** notification URL. ***
1424 *************************************************************************************/
1425
1426
1427 FUNCTION getNotificationID(p_itemtype IN VARCHAR2,
1428 p_itemkey IN VARCHAR2) RETURN VARCHAR2 IS
1429 -- Bug 5223211 : start
1430 /*
1431 CURSOR CUR_NOTIFICATION IS
1432 SELECT NOTIFICATION_ID
1433 FROM WF_ITEM_ACTIVITIES_HISTORY_V
1434 WHERE ITEM_KEY = p_itemkey
1435 AND ITEM_TYPE = p_itemtype
1436 AND ACTIVITY_TYPE = 'NOTICE'
1437 AND NOTIFICATION_STATUS = 'OPEN'
1438 ORDER BY BEGIN_DATE DESC;
1439 */
1440 CURSOR CUR_NOTIFICATION IS
1441 SELECT NOTIFICATION_ID from WF_ITEM_ACTIVITY_STATUSES_V
1442 WHERE ITEM_KEY = p_itemkey
1443 AND ITEM_TYPE = p_itemtype
1444 AND notification_id is not null
1445 ORDER BY activity_begin_date, execution_time;
1446 -- Bug 5223211 : End
1447 l_notification_id NUMBER;
1448 BEGIN
1449 OPEN CUR_NOTIFICATION;
1450 FETCH CUR_NOTIFICATION INTO l_notification_id;
1451 CLOSE CUR_NOTIFICATION;
1452 RETURN l_notification_id;
1453 END;
1454
1455 /************************************************************************************************
1456 ****** Following Procedure is to spwan child process to get off-line electronic signatures ***
1457 ****** It gets the current level to process and sends the notifications to all signers ***
1458 ****** of current level. It also copies required attributes parent process to child process ***
1459 *************************************************************************************************/
1460
1461 PROCEDURE SPWAN_OFFLINE_PROCESS(
1462 p_itemtype IN VARCHAR2,
1463 p_itemkey IN VARCHAR2,
1464 p_actid IN NUMBER,
1465 p_funcmode IN VARCHAR2,
1466 p_resultout OUT NOCOPY VARCHAR2
1467 )
1468 IS
1469 L_CURR_SIGN_LEVEL NUMBER ;
1470 CURSOR CURR_LIST_SIGNERS IS
1471 /*Changed the Cursor to get ORIGNIAL RECIPIENT instead of USER_NAME SKARIMIS */
1472 SELECT event_name, SIGNATURE_ID,NVL(ORIGINAL_RECIPIENT,USER_NAME) USER_NAME
1473 FROM EDR_ESIGNATURES
1474 WHERE EVENT_ID = p_itemkey
1475 --Bug 4272262: Start
1476 --Convert signature sequence to a number value.
1477 AND to_number(SIGNATURE_SEQUENCE,'999999999999.999999') = L_CURR_SIGN_LEVEL
1478 AND SIGNATURE_STATUS = 'PENDING';
1479 --Bug 4272262: End
1480
1481 SIGNER_LIST_REC CURR_LIST_SIGNERS%ROWTYPE;
1482 l_itemtype WF_ITEMS.ITEM_TYPE%TYPE ;
1483 l_WorkflowProcess VARCHAR2(30) ;
1484 l_performer_name FND_USER.USER_NAME%TYPE ;
1485 l_performer_display_name FND_USER.DESCRIPTION%TYPE ;
1486 l_item_key VARCHAR2(100);
1487
1488 --Bug 4577122: Start
1489 l_ignore_wfattr_notfound boolean := true;
1490 l_voting_regime varchar2(1);
1491 --Bug 4577122: End
1492
1493 BEGIN
1494
1495 --Bug 4074173 : Start
1496 l_itemtype := 'EDRPSIGF';
1497 l_WorkflowProcess := 'PSIG_OFFLINE_NOTIF_PROCESS';
1498 --Bug 4074173 : End
1499
1500 if p_funcmode='RUN' then
1501
1502 --Bug 4272262: Start
1503 SELECT MIN( to_number(SIGNATURE_SEQUENCE,'999999999999.999999')) INTO L_CURR_SIGN_LEVEL
1504 from EDR_ESIGNATURES
1505 WHERE EVENT_ID = p_itemkey
1506 AND SIGNATURE_STATUS = 'PENDING' ;
1507 --Bug 4272262: End
1508
1509 OPEN CURR_LIST_SIGNERS;
1510 LOOP
1511 FETCH CURR_LIST_SIGNERS INTO SIGNER_LIST_REC;
1512 EXIT WHEN CURR_LIST_SIGNERS%NOTFOUND;
1513
1514 l_item_key := p_itemkey ||'-'||SIGNER_LIST_REC.SIGNATURE_ID;
1515 --Bug 14762573 Not to create duplicate process for an existing one
1516 IF NOT WF_ITEM.Item_Exist(l_itemtype, l_item_key) THEN
1517 /* create the process */
1518 WF_ENGINE.CREATEPROCESS (itemtype => l_itemtype,
1519 itemkey => l_item_key,
1520 process => l_WorkflowProcess) ;
1521 /* set the item attributes */
1522
1523 --Bug 4577122: Start
1524 l_voting_regime := wf_engine.GetitemAttrText
1525 (p_itemtype, p_itemkey,'AME_VOTING_REGIME',
1526 l_ignore_wfattr_notfound);
1527
1528 WF_ENGINE.ADDITEMATTR
1529 (itemtype => l_itemtype,
1530 itemkey => l_item_key,
1531 aname => 'AME_VOTING_REGIME',
1532 text_value => l_voting_regime);
1533
1534 --Bug 4577122: End
1535
1536 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1537 '#WF_PAGEFLOW_ITEMKEY',
1538 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_ITEMKEY'));
1539 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1540 '#WF_PAGEFLOW_ITEMTYPE',
1541 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_ITEMTYPE'));
1542 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1543 'DEFERRED',
1544 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'DEFERRED'));
1545
1546 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1547 '#WF_ESIGN_ROW_ID' ,
1548 SIGNER_LIST_REC.SIGNATURE_ID);
1549 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1550 '#WF_CURRENT_MODE' ,
1551 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_CURRENT_MODE'));
1552 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1553 'PSIG_EVENT_NAME' ,
1554 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'PSIG_EVENT_NAME'));
1555 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1556 'PSIG_TIMEZONE' ,
1557 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'PSIG_TIMEZONE'));
1558 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1559 'PSIG_TIMESTAMP' ,
1560 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'PSIG_TIMESTAMP'));
1561
1562 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1563 'PSIG_USER_KEY_LABEL' ,
1564 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'PSIG_USER_KEY_LABEL'));
1565 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1566 'PSIG_USER_KEY_VALUE' ,
1567 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'PSIG_USER_KEY_VALUE'));
1568
1569 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1570 'SIGNATURE_HISTORY' ,
1571 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'SIGNATURE_HISTORY'));
1572
1573 /* Style Sheet Version Fix */
1574 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1575 'TEXT_XSLNAME',
1576 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'TEXT_XSLNAME'));
1577
1578 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1579 'TEXT_XSLVERSION' ,
1580 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'TEXT_XSLVERSION'));
1581
1582 SET_WF_ITEM_ATTRIBUTE_NUMBER(l_itemtype,
1583 l_item_key,
1584 '#WF_NOTIFICATION_TIMEOUT',
1585 GET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype, p_itemkey,'#WF_NOTIFICATION_TIMEOUT'));
1586 /* SKARIMIS Added Resend count */
1587
1588 SET_WF_ITEM_ATTRIBUTE_NUMBER(l_itemtype,
1589 l_item_key,
1590 'TIMEOUT_RESEND_COUNT',
1591 GET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype, p_itemkey,'TIMEOUT_RESEND_COUNT'));
1592 SET_WF_ITEM_ATTRIBUTE_NUMBER(l_itemtype,
1593 l_item_key,
1594 '#WF_NOTIFICATION_TIMEOUT_HR',
1595 GET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype, p_itemkey,'#WF_NOTIFICATION_TIMEOUT')/60);
1596
1597 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype, l_item_key,'PSIG_DOCUMENT_ID',
1598 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'PSIG_DOCUMENT_ID'));
1599
1600 SET_WF_ITEM_ATTRIBUTE_NUMBER(l_itemtype, l_item_key,'EDR_PSIG_DOC_ID',
1601 GET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype, p_itemkey,'EDR_PSIG_DOC_ID'));
1602
1603 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1604 '#WF_SIGNER_ROLE' ,
1605 SIGNER_LIST_REC.USER_NAME);
1606 /* set the item attributes */
1607 WF_ENGINE.SETITEMATTREVENT(itemtype => l_itemtype,itemkey => l_item_key,
1608 name => '#PSIG_EVENT',
1609 event => wf_engine.GETITEMATTREVENT(p_itemtype, p_itemkey,'#PSIG_EVENT'));
1610 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,'#WF_ERECORD_TEXT',
1611 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_ERECORD_TEXT'));
1612
1613 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1614 '#WF_SIGN_REQUESTER',
1615 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_SIGN_REQUESTER'));
1616
1617 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,l_item_key,
1618 '#ATTACHMENTS',
1619 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#ATTACHMENTS'));
1620 -- Bug: 3467353 - Start
1621
1622 -- Bug 3903471 : Start
1623 BEGIN
1624 WF_ENGINE.ADDITEMATTR(itemtype => l_itemtype,itemkey => l_item_key,
1625 aname => 'EREC_TEMPLATE_TYPE',
1626 text_value => GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'EREC_TEMPLATE_TYPE'));
1627 EXCEPTION WHEN OTHERS THEN
1628 NULL;
1629 END;
1630 --Bug 3903471 : End
1631
1632 --Bug 3998932 : Start
1633 --Isign Checklist ER
1634 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,
1635 l_item_key,
1636 'RELATED_APPLICATION',
1637 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,'RELATED_APPLICATION'));
1638 --Bug 3998932 : End
1639
1640 --Bug 4122622: Start
1641 --Fetch the related e-records and child e-record ids attribute values
1642 --from the parent workflow process and set them on the child
1643 --workflow process.
1644 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,
1645 l_item_key,
1646 'RELATED_ERECORDS',
1647 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,'RELATED_ERECORDS'));
1648
1649 SET_WF_ITEM_ATTRIBUTE_TEXT(l_itemtype,
1650 l_item_key,
1651 'CHILD_ERECORD_IDS',
1652 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,'CHILD_ERECORD_IDS'));
1653
1654 --Bug 4122622: End
1655
1656 /* Setting Patent Child association */
1657 WF_ENGINE.SETITEMPARENT(itemtype =>l_itemtype,itemkey =>l_item_key,
1658 parent_itemtype => p_itemtype,
1659 parent_itemkey=> p_itemkey,
1660 parent_context=> NULL);
1661 /* start the Workflow process */
1662
1663
1664 /* Set process Owner SKARIMIS for BLAF standard */
1665 wf_engine.setitemowner
1666 (ITEMTYPE=>l_itemtype,
1667 ITEMKEY=>l_item_key,
1668 OWNER=>GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,'#WF_SIGN_REQUESTER'));
1669 WF_ENGINE.STARTPROCESS (itemtype => l_itemtype,itemkey => l_item_key);
1670 END IF; --Added by Bug 14762573 QZENG
1671 END LOOP;
1672 CLOSE CURR_LIST_SIGNERS;
1673 end if;
1674 EXCEPTION
1675 WHEN OTHERS THEN
1676 WF_CORE.CONTEXT ('EDR_PSIG_PAGE_FLOW','SPWAN_OFFLINE_PROCESS',l_itemtype,l_item_key,'Initial' );
1677 CLOSE CURR_LIST_SIGNERS;
1678 raise;
1679 END SPWAN_OFFLINE_PROCESS;
1680
1681 /****************************************************************
1682 *** Following procedure is associated to workflow activity. **
1683 *** This procedure set the mode os signature process. **
1684 *** If 'WF_SOURCE_APPLICATION_TYPE' is "DB" and Deferred **
1685 *** mode is allowed then we should not pop up List of signers **
1686 *** page. In this case This procedure simulates done button **
1687 *** on list of signers page. **
1688 *****************************************************************/
1689
1690 PROCEDURE IS_IT_TOTAL_OFFLINE(
1691 p_itemtype IN VARCHAR2,
1692 p_itemkey IN VARCHAR2,
1693 p_actid IN NUMBER,
1694 p_funcmode IN VARCHAR2,
1695 p_resultout OUT NOCOPY VARCHAR2
1696 )
1697 IS
1698
1699 L_DOCUMENT_ID NUMBER;
1700 l_wf_timeout NUMBER ;
1701 l_wf_timeout_interval NUMBER ;
1702
1703 L_SOURCE_APPL_TYPE VARCHAR2(80) ;
1704 L_DEFER_MODE_ALLOWED VARCHAR2(30);
1705 l_plsql_clob_api VARCHAR2(2000);
1706 l_plsql_api VARCHAR2(2000);
1707 l_history_api VARCHAR2(2000);
1708 L_event_name VARCHAR2(240);
1709
1710 l_message VARCHAR2(2000);
1711 l_return_status VARCHAR2(1);
1712 l_msg_count NUMBER;
1713 l_msg_data VARCHAR2(240);
1714 l_document_rec edr_psig_documents%ROWTYPE;
1715 l_doc_param_tbl EDR_EvidenceStore_PUB.Params_tbl_type;
1716 l_sig_tbl EDR_EvidenceStore_PUB.Signature_tbl_type;
1717 l_erec_template_type varchar2(256);
1718 --Bug 4160412: Start
1719 l_signature_mode VARCHAR2(80);
1720 --Bug 4160412: End
1721
1722 BEGIN
1723
1724 --Bug 4074173 : start
1725
1726 --Bug: 3499311 Start - Specify Number Format in TO_NUMBER
1727 --Bug: 3903471 : Start
1728 --Old versions do not use #ERECORD_ID parameter.
1729 --Hence for backward compatibility we should verify with EDR_PSIG_DOC_ID attribute.
1730 --If the attribute does not exist, then #ERECORD_ID should be used.
1731 L_DOCUMENT_ID := nvl(GET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype, p_itemkey,'EDR_PSIG_DOC_ID'),
1732 TO_NUMBER(GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#ERECORD_ID'),'999999999999.999999'));
1733 --Bug 3903471 : End
1734
1735 l_wf_timeout := TO_NUMBER(FND_PROFILE.VALUE('EDR_WF_TIMEOUT'),'999999999999.999999');
1736 l_wf_timeout_interval := TO_NUMBER(FND_PROFILE.VALUE('EDR_WF_TIMEOUT_INTERVAL'),'999999999999.999999');
1737 --Bug : 3499311 End.
1738
1739 L_SOURCE_APPL_TYPE := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_SOURCE_APPLICATION_TYPE');
1740 L_DEFER_MODE_ALLOWED := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'DEFERRED');
1741 l_plsql_clob_api := 'PLSQLCLOB:wf_render.XML_STYLE_SHEET/#PSIG_EVENT:&'||'#NID';
1742 l_plsql_api := 'PLSQL:edr_xdoc_util_pkg.get_ntf_message_body/' || L_DOCUMENT_ID;
1743 l_history_api := 'PLSQL:EDR_UTILITIES.EDR_NTF_HISTORY/'||p_itemkey;
1744
1745 --Bug 4074173 : end
1746
1747 --Bug 4160412: Start
1748 --Obtain the value of the signature mode.
1749 l_signature_mode := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,EDR_CONSTANTS_GRP.G_SIGNATURE_MODE);
1750 --Bug 4160412: End
1751
1752 IF p_funcmode='RUN' THEN
1753 /* Set process Owner SKARIMIS for BLAF standard */
1754 wf_engine.setitemowner
1755 (ITEMTYPE=>p_itemtype,
1756 ITEMKEY=>p_itemkey,
1757 OWNER=>GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,'#WF_SIGN_REQUESTER'));
1758
1759 l_plsql_clob_api := l_plsql_clob_api ||'/'||
1760 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'TEXT_XSLNAME')||'/'||
1761 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'TEXT_XSLVERSION')||'/'||
1762 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'APPLICATION_CODE') ||'/'||
1763 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'HTML_XSLNAME')||'/'||
1764 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'HTML_XSLVERSION')||'/'||
1765 GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'APPLICATION_CODE');
1766
1767
1768 -- Bug 3170251 : Start- Check if EREC_TEMPLATE TYPE IS XSL or RTF and based on that decide the ERECORD TEXT Content
1769 -- and Notification Subject Token
1770 l_erec_template_type := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey, 'EREC_TEMPLATE_TYPE');
1771
1772 if (l_erec_template_type = 'RTF') then
1773 FND_MESSAGE.SET_NAME('EDR','EDR_WF_EREC_RTF_NTF_SUB');
1774 FND_MESSAGE.SET_TOKEN('ERECORD_ID',l_document_id);
1775
1776 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,'WF_MSG_SUB_EREC_TOKEN',FND_MESSAGE.GET());
1777 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,'#WF_ERECORD_TEXT', l_plsql_api);
1778 else
1779 FND_MESSAGE.SET_NAME('EDR','EDR_WF_EREC_XSL_NTF_SUB');
1780 FND_MESSAGE.SET_TOKEN('ERECORD_ID',l_document_id);
1781
1782 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,'WF_MSG_SUB_EREC_TOKEN',FND_MESSAGE.GET());
1783 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,'#WF_ERECORD_TEXT', l_plsql_clob_api);
1784 end if;
1785 -- Bug 3170251 : End
1786
1787 L_event_name := Get_event_disp_name(p_itemkey);
1788 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,
1789 p_itemkey,
1790 'PSIG_EVENT_NAME',
1791 L_EVENT_NAME);
1792
1793 /* Get the eRecrod */
1794 EDR_EvidenceStore_PUB.Get_DocumentDetails(p_api_version =>1.0,
1795 x_return_status => l_return_status,
1796 x_msg_count => l_msg_count,
1797 x_msg_data => l_msg_data,
1798 P_DOCUMENT_ID => L_DOCUMENT_ID,
1799 x_document_rec => l_document_rec,
1800 x_doc_parameters_tbl => l_doc_param_tbl,
1801 x_signatures_tbl => l_sig_tbl);
1802
1803 --Bug 3903471 : Start
1804 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,
1805 p_itemkey,
1806 'PSIG_TIMEZONE' ,
1807 l_document_rec.PSIG_TIMEZONE);
1808 --Bug 3903471 : End
1809
1810
1811 -- BUG 3271711 : Start Calling FND_DATE.DATE_TO_DISPLAYDT with server timezone
1812 -- Commenting the orignal code
1813 /*
1814 wf_engine.setitemattrtext(itemtype => p_itemtype,
1815 itemkey => p_itemkey,
1816 aname =>'PSIG_TIMESTAMP' ,
1817 avalue => FND_DATE.DATE_TO_DISPLAYDT(l_document_rec.PSIG_TIMESTAMP));
1818 */
1819 --Bug 3903471 : Start
1820 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,
1821 p_itemkey,
1822 'PSIG_TIMESTAMP' ,
1823 FND_DATE.DATE_TO_DISPLAYDT(l_document_rec.PSIG_TIMESTAMP, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE));
1824 --Bug 3903471 : End
1825
1826 -- BUG 3271711 : End
1827
1828 /* UPDATE EDR_ERECORDS
1829 SET EVENT_TIMEZONE = fnd_timezones.GET_SERVER_TIMEZONE_CODE
1830 Replaced the profile option to EDR_SERVER_ZONE
1831 SET EVENT_TIMEZONE = fnd_profile.VALUE('EDR_SERVER_TIMEZONE')
1832 WHERE event_id = p_itemkey;*/
1833
1834 -- Converting days into minutes
1835
1836 l_wf_timeout := round(l_wf_timeout * 60);
1837
1838 SET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype,
1839 p_itemkey,
1840 '#WF_NOTIFICATION_TIMEOUT',
1841 l_wf_timeout);
1842
1843 SET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype,
1844 p_itemkey,
1845 'TIMEOUT_RESEND_COUNT',
1846 l_wf_timeout_interval);
1847
1848 l_message := FND_MESSAGE.GET_STRING('EDR','EDR_PSIG_PROCESS_ABORTED');
1849
1850 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,
1851 p_itemkey,
1852 '#WF_PAGEFLOW_MESSAGE',
1853 l_message);
1854
1855 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,
1856 p_itemkey,
1857 'SIGNATURE_HISTORY',
1858 l_history_api);
1859
1860 IF L_SOURCE_APPL_TYPE = 'DB' and L_DEFER_MODE_ALLOWED ='Y' THEN
1861 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_CURRENT_MODE','OFFLINE');
1862 p_resultout := 'COMPLETE:TOTAL_OFFLINE';
1863 --Bug 2637353: Start
1864 --Set the result out value for MSCA source type.
1865 ELSIF L_SOURCE_APPL_TYPE='MSCA' then
1866 p_resultout := 'COMPLETE:MSCA';
1867
1868 --Bug 4160412: Start
1869
1870 --Bug 4543216: Start
1871 ELSIF L_SIGNATURE_MODE = EDR_CONSTANTS_GRP.G_ERES_LITE then
1872 --Bug 4543216: End
1873
1874 p_resultout := 'COMPLETE:LITE_MODE';
1875 --Bug 4160412: End
1876
1877 --Bug 2637353: End
1878 ELSE
1879 p_resultout := 'COMPLETE:NOT_TOTAL_OFFLINE';
1880 END IF;
1881 END IF;
1882 END IS_IT_TOTAL_OFFLINE;
1883
1884 PROCEDURE CHECK_TIMEOUT(
1885 p_itemtype IN VARCHAR2,
1886 p_itemkey IN VARCHAR2,
1887 p_actid IN NUMBER,
1888 p_funcmode IN VARCHAR2,
1889 p_resultout OUT NOCOPY VARCHAR2 ) IS
1890
1891 l_wf_timeout NUMBER;
1892 l_wf_temp_timeout NUMBER;
1893 l_item_key VARCHAR2(240);
1894 l_message VARCHAR2(2000);
1895
1896 /* This Cursor is to build the temporary group for sending timeout notification */
1897 CURSOR GET_USER is
1898 SELECT distinct USER_NAME from EDR_ESIGNATURES where to_char(event_id)=l_item_key;
1899
1900 l_requester VARCHAR2(4000);
1901 l_user varchar2(100);
1902 l_userlist WF_DIRECTORY.UserTable;
1903 ui number := 1;
1904 l_display_name varchar2(240);
1905 l_group varchar2(240);
1906 l_ESIGN_ID VARCHAR2(30);
1907
1908 BEGIN
1909
1910 --Bug 4074173 : start
1911 l_wf_timeout := GET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype, p_itemkey,'TIMEOUT_RESEND_COUNT');
1912 l_wf_temp_timeout := GET_WF_ITEM_ATTRIBUTE_NUMBER(p_itemtype, p_itemkey,'TEMP_RESEND_COUNT');
1913 l_item_key := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_ITEMKEY');
1914
1915 l_userlist(1) := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_SIGN_REQUESTER');
1916 l_requester := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_SIGN_REQUESTER');
1917 l_display_name :='eSignature Group';
1918 l_group :='EDRPSIG_ROLE';
1919 l_ESIGN_ID := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_ESIGN_ROW_ID');
1920 --Bug 4074173 : end
1921
1922 IF p_funcmode='RUN' THEN
1923 /* Figure out if the count exceeded */
1924
1925 if l_wf_temp_timeout >= l_wf_timeout then
1926
1927 /* Count Reached, prepare role */
1928 /* SKARIMIS Get the user List */
1929 OPEN GET_USER;
1930 LOOP
1931 fetch GET_USER into l_user;
1932 EXIT when GET_USER%NOTFOUND;
1933 IF l_requester <> l_user THEN
1934 l_userlist(ui+1):=l_user;
1935 ui := ui + 1;
1936 END IF;
1937 END LOOP;
1938 CLOSE GET_USER;
1939
1940 /* Create ADHOC group */
1941
1942 If (wf_directory.getRoleDisplayName('EDRPSIG_ROLE') is NULL) then
1943 /* Start Creating the Role */
1944
1945 wf_directory.CreateAdHocRole2(role_name=>l_group,
1946 role_display_name=>l_display_name,
1947 role_users=>l_userlist,
1948 expiration_date=>NULL);
1949 ELSE
1950 wf_directory.RemoveUsersFromAdHocRole(role_name=>'EDRPSIG_ROLE',
1951 role_users=>NULL);
1952 wf_directory.AddUsersToAdHocRole2(role_name=>'EDRPSIG_ROLE',
1953 role_users=>l_userlist);
1954 END IF;
1955
1956 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,
1957 'ESIG_GROUP' ,
1958 'EDRPSIG_ROLE');
1959 UPDATE EDR_ESIGNATURES
1960 SET SIGNATURE_STATUS = 'TIMEDOUT'
1961 WHERE SIGNATURE_ID = l_ESIGN_ID;
1962 p_resultout := 'COMPLETE:Y';
1963 ELSE
1964 SET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'TEMP_RESEND_COUNT',l_wf_temp_timeout+1);
1965 p_resultout := 'COMPLETE:N';
1966 END IF;
1967 END IF;
1968 END CHECK_TIMEOUT;
1969
1970 /************************************************************************************
1971 **** Following Procedure is used by sign-on screen. it accepts notification id ***
1972 **** and returns workflow item type ane item key these values are used to set ***
1973 **** page context in offline case. ***
1974 *************************************************************************************/
1975
1976 PROCEDURE getItemKey(p_notif in varchar2,
1977 p_item_key out NOCOPY varchar2,
1978 p_item_type out NOCOPY varchar2) is
1979 cursor getItemKey is
1980 select ITEM_KEY, ITEM_TYPE
1981 from WF_ITEM_ACTIVITIES_HISTORY_V
1982 where NOTIFICATION_ID = p_notif;
1983 BEGIN
1984 open getItemKey;
1985 fetch getItemKey into p_item_key,p_item_type;
1986 close getItemKey;
1987 END;
1988
1989 --Bug 3072401: Start
1990 PROCEDURE SEND_FINAL_APPROVAL_NTF
1991 ( p_itemtype VARCHAR2,
1992 p_itemkey VARCHAR2,
1993 p_actid NUMBER,
1994 p_funcmode VARCHAR2,
1995 p_resultout OUT NOCOPY VARCHAR2
1996 )
1997 AS
1998 l_return_status VARCHAR2(25);
1999 l_event_id NUMBER;
2000 l_signature_status VARCHAR2(25);
2001 L_CURR_MODE VARCHAR2(30);
2002
2003 BEGIN
2004
2005 --Bug 4074173 : start
2006 l_return_status := G_NO;
2007 l_event_id := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_ITEMKEY');
2008 L_CURR_MODE := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_CURRENT_MODE');
2009 --Bug 4074173 : end
2010
2011 --get the status of the signature process
2012 select ERECORD_SIGNATURE_STATUS into l_signature_status
2013 from EDR_ERECORDS
2014 WHERE EVENT_ID = l_event_id;
2015
2016 if (l_signature_status = G_SUCCESS_STATUS and L_CURR_MODE='OFFLINE') then
2017 l_return_status := G_YES;
2018 end if;
2019
2020 p_resultout := l_return_status;
2021
2022 END SEND_FINAL_APPROVAL_NTF;
2023
2024 PROCEDURE SEND_INDV_APPROVAL_NTF
2025 ( p_itemtype VARCHAR2,
2026 p_itemkey VARCHAR2,
2027 p_actid NUMBER,
2028 p_funcmode VARCHAR2,
2029 p_resultout OUT NOCOPY VARCHAR2
2030 )
2031 AS
2032 l_return_status VARCHAR2(25);
2033
2034 l_event_id NUMBER;
2035 l_send_individual_ntf VARCHAR2(10);
2036 l_requester VARCHAR2(4000);
2037
2038 -- Bug 3315185 : Added to store requester id to fetch profile option
2039 l_requester_id number;
2040
2041 BEGIN
2042
2043 --Bug 4074173 : start
2044 l_return_status := G_NO;
2045 l_event_id := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_PAGEFLOW_ITEMKEY');
2046 l_requester := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#WF_SIGN_REQUESTER');
2047 --Bug 4074173 : end
2048
2049 -- Bug 3315185 : Modified profile option access level to user level
2050 -- for accessing the requester's preference first.
2051
2052 --read the profile option to find out when the requester needs the notification
2053 --l_send_individual_ntf := fnd_profile.value('EDR_INDIVIDUAL_APPROVAL_NTF');
2054
2055 select user_id into l_requester_id from fnd_user where user_name = trim(l_requester);
2056
2057 l_send_individual_ntf := fnd_profile.value_specific(NAME => 'EDR_INDIVIDUAL_APPROVAL_NTF',
2058 USER_ID => l_requester_id );
2059
2060
2061 -- If the user has not set this profile option, access it at site level
2062 if ( l_send_individual_ntf is null) then
2063 l_send_individual_ntf := fnd_profile.value_specific( NAME => 'EDR_INDIVIDUAL_APPROVAL_NTF');
2064 end if;
2065
2066 -- Bug 3315185 : End
2067
2068 if (l_send_individual_ntf = 'Y') then
2069 l_return_status := G_YES;
2070 end if;
2071
2072 p_resultout := l_return_status;
2073
2074 END SEND_INDV_APPROVAL_NTF;
2075 --Bug 3072401: End
2076
2077
2078 --Bug 3207385: Start
2079 PROCEDURE RAISE_APPR_COMPLETION_EVT
2080 ( p_itemtype VARCHAR2,
2081 p_itemkey VARCHAR2,
2082 p_actid NUMBER,
2083 p_funcmode VARCHAR2,
2084 p_resultout OUT NOCOPY VARCHAR2
2085 )
2086
2087 is
2088
2089 --This variable would hold the event object store in workflow.
2090 l_psig_event wf_event_t;
2091
2092 --This variable would hold the original event name.
2093 l_orig_event_name varchar2(240);
2094
2095 --This variable would hold the original event key.
2096 l_orig_event_key varchar2(240);
2097
2098 --This would hold the name of the approval completion event.
2099 l_event_name varchar2(240);
2100
2101 --This would hold the name of the event key set while raising the approval completion event.
2102 l_event_key varchar2(240);
2103
2104 --This would hold the e-record ID of the event which is being processed.
2105 l_erecord_id varchar2(128);
2106
2107 --This would hold the document status as it exists in the evidence store for the e-record identified
2108 --by the above e-record ID.
2109 l_event_status varchar2(32);
2110
2111 l_param_list WF_PARAMETER_LIST_T;
2112
2113 BEGIN
2114
2115 l_psig_event := wf_engine.getItemAttrEvent(p_itemtype, p_itemkey,'#PSIG_EVENT');
2116
2117 l_orig_event_name := l_psig_event.getEventName();
2118
2119 l_orig_event_key := l_psig_Event.getEventKey();
2120
2121 --Obtain the e-record and event status from workflow from workflow.
2122 l_erecord_id := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype, p_itemkey,'#ERECORD_ID');
2123 l_event_status := GET_WF_ITEM_ATTRIBUTE_TEXT(p_itemtype,p_itemkey,EDR_CONSTANTS_GRP.G_FINAL_DOCUMENT_STATUS);
2124
2125
2126 l_event_name := EDR_CONSTANTS_GRP.G_APPROVAL_COMPLETION_EVT;
2127 l_event_key := l_erecord_id;
2128
2129 --Set the payload parameters for the approval completion event.
2130
2131 wf_event.addParameterToList(EDR_CONSTANTS_GRP.G_ORIGINAL_EVENT_NAME,l_orig_event_name,l_param_list);
2132
2133 wf_event.addParameterToList(EDR_CONSTANTS_GRP.G_ORIGINAL_EVENT_KEY,l_orig_event_key,l_param_list);
2134
2135 wf_event.addParameterToList(EDR_CONSTANTS_GRP.G_ERECORD_ID,l_erecord_id,l_param_list);
2136
2137 wf_event.addParameterToList(EDR_CONSTANTS_GRP.G_EVENT_STATUS,l_event_status,l_param_list);
2138
2139 --Raise the approval completion event.
2140 WF_EVENT.RAISE3(L_EVENT_NAME,
2141 L_EVENT_KEY,
2142 null,
2143 L_PARAM_LIST,
2144 NULL);
2145
2146 END RAISE_APPR_COMPLETION_EVT;
2147 --Bug 3207385: End
2148 -- Bug 5166723 : start
2149 PROCEDURE MOVE_WF_ACTIVITY(P_ITEMTYPE IN VARCHAR2,
2150 P_ITEMKEY IN VARCHAR2,
2151 P_CURRENT_ACTIVITY IN VARCHAR2,
2152 P_RESULT_CODE IN VARCHAR2)
2153 IS
2154 PRAGMA AUTONOMOUS_TRANSACTION;
2155 BEGIN
2156 -- P_CURRENT_ACTIVITY => PSIG_ESIGN_SIGNER_LIST
2157 FND_WF_ENGINE.COMPLETEACTIVITY(P_ITEMTYPE, P_ITEMKEY, P_CURRENT_ACTIVITY , P_RESULT_CODE);
2158 COMMIT;
2159 EXCEPTION
2160 WHEN OTHERS THEN
2161 ROLLBACK;
2162 --Diagnostics Start
2163 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
2164 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
2165 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_PSIG_PAGE_FLOW');
2166 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','MOVE_WF_ACTIVITY');
2167 if (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2168 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
2169 'edr.plsql.EDR_PSIG_PAGE_FLOW.MOVE_WF_ACTIVITY',
2170 FALSE
2171 );
2172 end if;
2173 --Diagnostics End
2174 APP_EXCEPTION.RAISE_EXCEPTION;
2175 END MOVE_WF_ACTIVITY;
2176 -- Bug 5166723 : End
2177 END EDR_PSIG_PAGE_FLOW;