[Home] [Help]
PACKAGE BODY: APPS.EDR_PSIG
Source
1 PACKAGE BODY EDR_PSIG AS
2 /* $Header: EDRPSIGB.pls 120.4 2011/03/31 14:10:58 srpuri ship $ */
3
4 /* Exceptions */
5 TIMEZONE_ERROR exception;
6 DOCUMENT_NOT_FOUND exception;
7 DOCUMENT_PENDING exception;
8 DOCUMENT_CLOSE_ERROR exception;
9 EDR_GENERIC_ERROR exception;
10 EDR_INVALID_DOC_STATUS exception;
11 EDR_INVALID_DOC_TRAN exception;
12 EDR_INVALID_SIGN_REQUEST exception;
13 EDR_INVALID_USER exception;
14 EDR_DUPLICATE_SIGNER Exception;
15
16 --Bug 3212117: Start
17 G_CHILD_ERECORD_COUNT NUMBER;
18 --Bug 3212117: End
19
20
21 /* Global variables */
22 /* Verify Servertime Zone */
23 PROCEDURE VERIFY_TIMEZONE(X_TIMEZONE OUT NOCOPY VARCHAR2,
24 X_ERROR OUT NOCOPY NUMBER,
25 X_ERROR_MSG OUT NOCOPY VARCHAR2) IS
26
27 l_server_timezone varchar2(240);
28 l_edr_timezone varchar2(240);
29
30 BEGIN
31
32 --Bug 4073809 : start
33 l_server_timezone :=fnd_timezones.GET_SERVER_TIMEZONE_CODE;
34 l_edr_timezone :=fnd_profile.VALUE('EDR_SERVER_TIMEZONE');
35 --Bug 4073809 : end
36
37 /* Bug Fix 3225490 . Added new or condition to check null value for edr_timezone */
38 IF (l_edr_timezone <> l_server_timezone) or (l_server_timezone is NULL) or (l_edr_timezone is NULL) THEN
39 /*end of bug fix 3225490 */
40 fnd_message.set_name('EDR','EDR_PSIG_TIMEZONE_ERROR');
41 X_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_TIMEZONE_ERROR');
42 X_ERROR_MSG:= fnd_message.get();
43 X_TIMEZONE:=NULL;
44 END IF;
45 X_TIMEZONE := L_EDR_TIMEZONE;
46 END VERIFY_TIMEZONE;
47
48 /* Get_DOCUMENT_STATUS. This procedure will return the current document status for a given document_id
49 if document is not availalbe the procedure will raise a not data found exception
50 */
51
52 PROCEDURE GET_DOCUMENT_STATUS(P_DOCUMENT_ID IN NUMBER,
53 X_STATUS OUT NOCOPY VARCHAR2,
54 X_ERROR OUT NOCOPY NUMBER,
55 X_ERROR_MSG OUT NOCOPY VARCHAR2) IS
56 CURSOR C1 is
57 SELECT PSIG_STATUS from EDR_PSIG_DOCUMENTS
58 WHERE DOCUMENT_ID = P_DOCUMENT_ID;
59 L_document_status varchar2(240);
60 BEGIN
61 OPEN C1;
62 FETCH C1 into X_STATUS;
63 if c1%NOTFOUND THEN
64 raise DOCUMENT_NOT_FOUND;
65 END IF;
66 CLOSE C1;
67 EXCEPTION
68 WHEN DOCUMENT_NOT_FOUND THEN
69 fnd_message.set_name('EDR','EDR_PSIG_INVALID_DOCUMENT');
70 X_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_DOCUMENT');
71 X_ERROR_MSG:= fnd_message.get();
72 CLOSE c1;
73 WHEN OTHERS then
74 X_ERROR:=SQLCODE;
75 X_ERROR_MSG:=SQLERRM;
76 CLOSE c1;
77 END GET_DOCUMENT_STATUS;
78
79 PROCEDURE GET_SIGNATURE_STATUS(P_SIGNATURE_ID IN NUMBER,
80 X_STATUS OUT NOCOPY VARCHAR2,
81 X_ERROR OUT NOCOPY NUMBER,
82 X_ERROR_MSG OUT NOCOPY VARCHAR2) IS
83 CURSOR C1 is
84 SELECT SIGNATURE_STATUS from EDR_PSIG_DETAILS
85 WHERE SIGNATURE_ID = P_SIGNATURE_ID;
86 L_Signature_status varchar2(240);
87 BEGIN
88 OPEN C1;
89 FETCH C1 into X_STATUS;
90 if c1%NOTFOUND THEN
91 raise DOCUMENT_NOT_FOUND;
92 END IF;
93 CLOSE C1;
94 EXCEPTION
95 WHEN DOCUMENT_NOT_FOUND THEN
96 fnd_message.set_name('EDR','EDR_PSIG_INVALID_SIGNATURE');
97 X_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_SIGNATURE');
98 X_ERROR_MSG:= fnd_message.get();
99 CLOSE c1;
100 WHEN OTHERS then
101 X_ERROR:=SQLCODE;
102 X_ERROR_MSG:=SQLERRM;
103 CLOSE c1;
104 END GET_SIGNATURE_STATUS;
105
106 PROCEDURE VALIDATE_USER(P_USER IN VARCHAR2,
107 X_STATUS OUT NOCOPY boolean,
108 X_ERROR OUT NOCOPY NUMBER,
109 X_ERROR_MSG OUT NOCOPY VARCHAR2) IS
110 CURSOR C1 is
111 SELECT count(*) from FND_USER
112 WHERE USER_NAME = P_USER;
113 L_count number;
114 BEGIN
115 OPEN C1;
116 FETCH C1 into l_count;
117 CLOSE C1;
118 IF l_count > 0 THEN
119 x_status :=true;
120 else
121 x_status := false;
122 RAISE EDR_INVALID_USER;
123 END IF;
124 EXCEPTION
125 WHEN EDR_INVALID_USER THEN
126 fnd_message.set_name('EDR','EDR_PSIG_INVALID_USER');
127 fnd_message.set_token('USER',P_USER);
128 X_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_USER');
129 X_ERROR_MSG:= fnd_message.get();
130
131 WHEN OTHERS then
132 X_ERROR:=SQLCODE;
133 X_ERROR_MSG:=SQLERRM;
134 CLOSE c1;
135 END VALIDATE_USER;
136
137 /* Copy Attachments */
138 function COPY_NTF_ATTACHMENTS(p_nid VARCHAR2,
139 p_target_value varchar2) return boolean
140 IS
141 l_erecord_id number;
142 l_entity_name varchar2(240);
143 l_pk1_value VARCHAR2(100);
144 l_pk2_value VARCHAR2(100);
145 l_pk3_value VARCHAR2(100);
146 l_pk4_value VARCHAR2(100);
147 l_pk5_value VARCHAR2(100);
148 l_eres_category_id NUMBER;
149 l_user_id NUMBER;
150 l_login_id NUMBER;
151 l_attachment_string varchar2(2000);
152 --Bug 4006844: Start
153 --This would hold the value of the document category specified in workflow.
154 l_category_name VARCHAR2(100);
155 --This would hold the corresponding category id, if it exists.
156 l_category_id NUMBER;
157 --Bug 4006844: End
158 BEGIN
159 /* Parse the string */
160 BEGIN
161 wf_log_pkg.string(6, 'Copy Attachments...','Verifying....the attribute attachments');
162 l_attachment_string:=wf_notification.getattrtext(p_nid,'#ATTACHMENTS');
163 wf_log_pkg.string(6, 'Copy Attachments...','Attribute String.....'||l_attachment_string);
164 exception
165 when others then
166 NULL;
167 END;
168 if l_attachment_string is NULL then
169 return true;
170 end if;
171 wf_log_pkg.string(6, 'Copy Attachments...','Parsing String'||l_attachment_string);
172 /* parse String */
173 /* get the entity name */
174 l_entity_name:=substr(l_attachment_string,instr(l_attachment_string,'=')+1,
175 instr(l_attachment_string,'&')-(instr(l_attachment_string,'=')+1));
176 if l_entity_name ='ERECORD' then
177 /* this is already an ERECORD entity Don't make a copy of it */
178 return true;
179 end if;
180 wf_log_pkg.string(6, 'Copy Attachments...','ENTITY....'||l_entity_Name);
181 /* Check the for number of parameter */
182 --Bug 4006844: Start
183 --This parsing logic would be changed completely
184 /*
185 if instr(l_attachment_string,'&',1,3) > 0 then
186 l_pk1_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,3)+1, instr(l_attachment_string,'&',1,3)-(instr(l_attachment_string,'=',1,3)+1));
187 if instr(l_attachment_string,'&',1,5) > 0 then
188 l_pk2_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,5)+1,
189 instr(l_attachment_string,'&',1,5)-(instr(l_attachment_string,'=',1,5)+1));
190 if instr(l_attachment_string,'&',1,7) > 0 then
191 l_pk3_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,7)+1,
192 instr(l_attachment_string,'&',1,7)-(instr(l_attachment_string,'=',1,7)+1));
193 if instr(l_attachment_string,'&',1,9) > 0 then
194 l_pk4_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,9)+1,
195 instr(l_attachment_string,'&',1,9)-(instr(l_attachment_string,'=',1,9)+1));
196 if instr(l_attachment_string,'&',1,11) > 0 then
197 l_pk5_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,11)+1,
198 instr(l_attachment_string,'&',1,11)-(instr(l_attachment_string,'=',1,11)+1));
199 else
200 l_pk5_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,11)+1);
201 end if;
202 else
203 l_pk4_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,9)+1);
204 end if;
205 else
206 l_pk3_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,7)+1);
207 end if;
208 else
209 l_pk2_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,5)+1);
210 end if;
211 else
212 l_pk1_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,3)+1);
213 END IF; */
214
215
216 --The new parsing technique would take care of the category attribute if it exists.
217 if instr(l_attachment_string,'&',1,3) > 0 then
218 l_pk1_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,3)+1,
219 instr(l_attachment_string,'&',1,3)-(instr(l_attachment_string,'=',1,3)+1));
220 if instr(l_attachment_string,'&',1,5) > 0 then
221 l_pk2_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,5)+1,
222 instr(l_attachment_string,'&',1,5)-(instr(l_attachment_string,'=',1,5)+1));
223 if instr(l_attachment_string,'&',1,7) > 0 then
224 l_pk3_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,7)+1,
225 instr(l_attachment_string,'&',1,7)-(instr(l_attachment_string,'=',1,7)+1));
226 if instr(l_attachment_string,'&',1,9) > 0 then
227 l_pk4_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,9)+1,
228 instr(l_attachment_string,'&',1,9)-(instr(l_attachment_string,'=',1,9)+1));
229 if instr(l_attachment_string,'&',1,11) > 0 then
230 l_pk5_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,11)+1,
231 instr(l_attachment_string,'&',1,11)-(instr(l_attachment_string,'=',1,11)+1));
232
233 if(instr(l_attachment_string,'=',1,12) > 0) then
234 l_category_name := substr(l_attachment_string,instr(l_attachment_string,'=',1,12)+1);
235 end if;
236 else
237
238 if(instr(l_attachment_string,'&',1,10) > 0 and instr(l_attachment_string,'=',1,11) >0) then
239 l_pk5_value := substr(l_attachment_string,instr(l_attachment_string,'=',1,11)+1);
240
241 elsif(instr(l_attachment_string,'&',1,9) > 0 and instr(l_attachment_string,'=',1,10) > 0) then
242 l_category_name := substr(l_attachment_string,instr(l_attachment_string,'=',1,10)+1);
243
244 end if;
245
246 end if;
247
248 else
249
250 if(instr(l_attachment_string,'&',1,8) > 0 and instr(l_attachment_string,'=',1,9) >0) then
251 l_pk4_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,9)+1);
252
253 elsif(instr(l_attachment_string,'&',1,7) > 0 and instr(l_attachment_string,'=',1,8) > 0) then
254 l_category_name := substr(l_attachment_string,instr(l_attachment_string,'=',1,8)+1);
255 end if;
256
257 end if;
258 else
259 if(instr(l_attachment_string,'&',1,6) > 0 and instr(l_attachment_string,'=',1,7) >0) then
260 l_pk3_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,7)+1);
261
262 elsif(instr(l_attachment_string,'&',1,5) > 0 and instr(l_attachment_string,'=',1,6) > 0) then
263 l_category_name := substr(l_attachment_string,instr(l_attachment_string,'=',1,6)+1);
264
265 end if;
266
267 end if;
268
269 else
270 if(instr(l_attachment_string,'&',1,4) > 0 and instr(l_attachment_string,'=',1,5) >0) then
271 l_pk2_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,5)+1);
272
273 elsif(instr(l_attachment_string,'&',1,3) > 0 and instr(l_attachment_string,'=',1,4) > 0) then
274 l_category_name := substr(l_attachment_string,instr(l_attachment_string,'=',1,4)+1);
275
276 end if;
277 end if;
278 else
279 l_pk1_value:= substr(l_attachment_string,instr(l_attachment_string,'=',1,3)+1);
280 END IF;
281 --Bug 4006844: End
282
283
284 wf_log_pkg.string(6, 'Copy Attachments...','pk1....'||l_pk1_value);
285 wf_log_pkg.string(6, 'Copy Attachments...','pk2....'||l_pk2_value);
286 wf_log_pkg.string(6, 'Copy Attachments...','pk3....'||l_pk3_value);
287 wf_log_pkg.string(6, 'Copy Attachments...','pk4....'||l_pk4_value);
288 wf_log_pkg.string(6, 'Copy Attachments...','pk5....'||l_pk5_value);
289
290 l_user_id := fnd_global.user_id;
291 l_login_id := fnd_global.login_id;
292 l_erecord_id := p_target_value;
293
294 /* obtain the category id of the document category 'ERES' */
295 select category_id into l_eres_category_id
296 from fnd_document_categories_vl
297 where name = 'ERES';
298
299 --Bug 4006844: Start
300 --Obtain the ID of the document category specified in workflow.
301 if(l_category_name is not null) then
302 select category_id into l_category_id
303 from fnd_document_categories_vl
304 where name = l_category_name;
305 end if;
306 --Bug 4006844: End
307
308
309
310 wf_log_pkg.string(6, 'Copy Attachments...','Category....'||l_eres_category_id);
311 --Bug 4381237: Start
312 --fnd_attached_documents2_pkg.copy_attachments(
313 edr_attachments_grp.copy_attachments(
314 --Bug 4381237: End
315 X_from_entity_name => l_entity_name,
316 X_from_pk1_value => l_pk1_value,
317 X_from_pk2_value => l_pk2_value,
318 X_from_pk3_value => l_pk3_value,
319 X_from_pk4_value => l_pk4_value,
320 X_from_pk5_value => l_pk5_value,
321 X_to_entity_name => 'ERECORD',
322 X_to_pk1_value => l_erecord_id,
323 X_to_pk2_value => null,
324 X_to_pk3_value => null,
325 X_to_pk4_value => null,
326 X_to_pk5_value => null,
327 X_created_by => l_user_id,
328 X_last_update_login => l_login_id,
329 X_program_application_id => null,
330 X_program_id => null,
331 X_request_id => null,
332 X_automatically_added_flag => 'N',
333 --Bug 4006844: Start
334 --Pass the category ID
335 X_from_category_id => l_category_id,
336 --Bug 4006844: End
337 X_to_category_id => l_eres_category_id);
338 wf_log_pkg.string(6, 'Copy Attachments...','Copy Completed');
339
340 return true;
341 EXCEPTION
342 WHEN OTHERS then
343 return false;
344 END COPY_NTF_ATTACHMENTS;
345
346 /* Document Creation Procedure
347 IN:
348 PSIG_XML
349 PSIG_DOCUMENT
350 PSIG_DOCUMENTFORMAT
351 PSIG_REQUESTER
352 PSIG_SOURCE
353 EVENT_NAME
354 EVENT_KEY
355 Description :
356 This procedure will create a document instance for signature and can associate signatures
357 before closing the docuemnt
358
359 */
360
361 PROCEDURE openDocument
362 ( P_PSIG_XML IN CLOB DEFAULT NULL ,
363 P_PSIG_DOCUMENT IN CLOB DEFAULT NULL ,
364 P_PSIG_DOCUMENTFORMAT IN VARCHAR2 DEFAULT NULL ,
365 P_PSIG_REQUESTER IN VARCHAR2 ,
366 P_PSIG_SOURCE IN VARCHAR2 DEFAULT NULL ,
367 P_EVENT_NAME IN VARCHAR2 DEFAULT NULL ,
368 P_EVENT_KEY IN VARCHAR2 DEFAULT NULL ,
369 p_WF_NID IN NUMBER DEFAULT NULL ,
370 P_DOCUMENT_ID OUT NOCOPY NUMBER ,
371 P_ERROR OUT NOCOPY NUMBER ,
372 P_ERROR_MSG OUT NOCOPY VARCHAR2
373 )
374 IS
375 l_document_id NUMBER;
376 l_document CLOB;
377 l_xml CLOB;
378 l_temp varchar2(32767);
379 l_end_of_msgbody varchar2(1);
380 l_msg varchar2(32000);
381 l_status boolean;
382
383 l_return_status varchar2(1);
384 l_msg_count number;
385 l_msg_data varchar2(2000);
386 l_ackn_id number;
387 l_server_timezone varchar2(240);
388 l_doc_format varchar2(100);
389
390 l_doc_req_disp_name varchar2(360); -- Bug : 3604783
391
392 L_CREATION_DATE DATE;
393 L_CREATED_BY NUMBER;
394 L_LAST_UPDATE_DATE DATE;
395 L_LAST_UPDATED_BY NUMBER;
396 L_LAST_UPDATE_LOGIN NUMBER;
397
398 BEGIN
399 --Bug 4073809 : start
400 l_document := empty_clob();
401 l_end_of_msgbody := 'N';
402 --Bug 4073809 : end
403
404 wf_notification.newclob(l_document,l_msg);
405
406 /* Check if Server time Zone is set to NULL and if error out */
407 VERIFY_TIMEZONE(X_TIMEZONE =>l_server_timezone,
408 X_ERROR => P_ERROR,
409 X_ERROR_MSG =>P_ERROR_MSG);
410
411 if P_ERROR > 0 then
412 RAISE TIMEZONE_ERROR;
413 end if;
414
415 if P_WF_NID is NULL then
416 /* Assign passed Document to l_document */
417 l_document:= P_PSIG_DOCUMENT;
418 else
419 /* Begin Bug Fix 3142631*/
420 /* Check the Document_type */
421 if (P_PSIG_DOCUMENTFORMAT is NULL
422 or P_PSIG_DOCUMENTFORMAT ='TEXT'
423 or P_PSIG_DOCUMENTFORMAT = WF_NOTIFICATION.doc_text) THEN
424 l_doc_format:=WF_NOTIFICATION.doc_text;
425 elsif ( P_PSIG_DOCUMENTFORMAT = 'HTML' or P_PSIG_DOCUMENTFORMAT = WF_NOTIFICATION.doc_html) THEN
426 l_doc_format:=WF_NOTIFICATION.doc_html;
427 else
428 fnd_message.set_name('EDR','EDR_PSIG_WFNTF_DOCFORMAT');
429 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_WFNTF_FORMAT');
430 P_ERROR_MSG:= fnd_message.get();
431 RAISE EDR_GENERIC_ERROR;
432 end if;
433 /* End Bug Fix 3142631*/
434
435 /* User requested to use Workflow notification body as text */
436 BEGIN
437 while (l_end_of_msgbody <> 'Y') Loop
438 wf_notification.GetFullBodyWrapper
439 ( NID => P_WF_NID ,
440 msgbody => l_msg ,
441 end_of_body => l_end_of_msgbody,
442 disptype => l_doc_format
443 );
444 wf_log_pkg.string(6, 'EDR_PSIG_rule.psig_rule','l_msg '|| l_msg);
445 wf_notification.writetoclob(l_document,l_msg);
446 end loop;
447 EXCEPTION
448 WHEN OTHERS then
449 fnd_message.set_name('EDR','EDR_PSIG_WFNTF_ERROR');
450 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_WFNTF_ERROR');
451 P_ERROR_MSG:= fnd_message.get();
452 RAISE EDR_GENERIC_ERROR;
453 END;
454 end if;
455
456 /* Generate a new Document ID */
457 select EDR_PSIG_DOCUMENTS_S.nextval into l_document_id from dual;
458
459 /* Attachments Bug Fix. Added if statement */
460 if P_WF_NID is not NULL then
461 /* capture Attachemnts if Exists */
462 l_status:= COPY_NTF_ATTACHMENTS
463 ( p_nid => P_WF_NID ,
464 p_target_value => l_document_id
465 );
466 END IF;
467
468 /* Insert the document in pending status */
469 /* Recieve the CLOB */
470 l_XML:=P_PSIG_XML;
471
472 -- Bug 3604783 : Start
473 -- Find the document requester's display name
474 l_doc_req_disp_name := edr_utilities.getuserdisplayname(p_psig_requester);
475
476 --Bug 4672801: start
477 EDR_UTILITIES.getWhoColumns
478 ( creation_date => l_creation_date ,
479 created_by => l_created_by ,
480 last_update_date => l_last_update_date ,
481 last_updated_by => l_last_updated_by ,
482 last_update_login => l_last_update_login
483 );
484 --Bug 4672801: end
485
486 --Insert the row
487 INSERT into EDR_PSIG_DOCUMENTS
488 (
489 DOCUMENT_ID ,
490 PSIG_XML ,
491 PSIG_DOCUMENT ,
492 PSIG_DOCUMENTFORMAT ,
493 PSIG_TIMESTAMP ,
494 PSIG_TIMEZONE ,
495 DOCUMENT_REQUESTER ,
496 DOC_REQ_DISP_NAME ,
497 PSIG_STATUS ,
498 PSIG_SOURCE ,
499 EVENT_NAME ,
500 --Bug 4672801: start
501 --EVENT_KEY
502 EVENT_KEY,
503 CREATION_DATE,
504 CREATED_BY,
505 LAST_UPDATE_DATE,
506 LAST_UPDATED_BY,
507 LAST_UPDATE_LOGIN
508 --Bug 4672801: end
509 )
510 values
511 (
512 l_document_id ,
513 L_XML ,
514 l_document ,
515 P_PSIG_DOCUMENTFORMAT ,
516 sysdate ,
517 l_server_timezone ,
518 P_PSIG_REQUESTER ,
519 L_DOC_REQ_DISP_NAME ,
520 'PENDING' ,
521 P_PSIG_SOURCE ,
522 P_EVENT_NAME ,
523 --Bug 4672801: start
524 --P_EVENT_KEY
525 P_EVENT_KEY,
526 l_CREATION_DATE,
527 l_CREATED_BY,
528 l_LAST_UPDATE_DATE,
529 l_LAST_UPDATED_BY,
530 l_LAST_UPDATE_LOGIN
531 --Bug 4672801: end
532 );
533
534 -- Bug 3604783 : End
535
536 p_document_id:=l_document_id;
537
538 -- after inserting a row in the erecord table, insert a row in the
539 -- acknowledgement table with the default ack status of NOTACKNOWLEDGED
540 -- we dont have to do any validation at all as erecord id is valid
541 -- we just created it, status is valid, there is no risk of duplicate
542 -- ack status for this erecord as its just created in the step above
543
544 EDR_TRANS_ACKN_PVT.INSERT_ROW
545 ( p_api_version => 1.0 ,
546 p_init_msg_list => FND_API.G_TRUE ,
547 p_validation_level => FND_API.G_VALID_LEVEL_NONE ,
548 x_return_status => l_return_status ,
549 x_msg_count => l_msg_count ,
550 x_msg_data => l_msg_data ,
551 p_erecord_id => l_document_id ,
552 p_trans_status => EDR_CONSTANTS_GRP.g_no_ack_status ,
553 p_ackn_by => null ,
554 p_ackn_note => null ,
555 x_ackn_id => l_ackn_id
556 );
557
558 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
560 END IF;
561
562 EXCEPTION
563 WHEN EDR_GENERIC_ERROR then
564 NULL;
565 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
566 FND_MSG_PUB.Count_And_Get
567 ( p_count => l_msg_count ,
568 p_data => l_msg_data
569 );
570
571 p_error := 22000;
572 P_ERROR_MSG := l_msg_data ;
573 WHEN TIMEZONE_ERROR then
574 NULL;
575 WHEN OTHERS then
576 P_ERROR:=SQLCODE;
577 P_ERROR_MSG:=SQLERRM;
578 END openDocument;
579
580 /* Document Creation Procedure over loaded
581 IN:
582 Description :
583 This procedure will create a document instance for signature and can associate signatures
584 before closing the docuemnt
585
586 */
587
588 PROCEDURE openDocument
589 (
590 P_DOCUMENT_ID OUT NOCOPY NUMBER,
591 P_ERROR OUT NOCOPY NUMBER,
592 P_ERROR_MSG OUT NOCOPY VARCHAR2
593 ) IS
594 l_document_id NUMBER;
595 l_document CLOB;
596 l_xml CLOB;
597 l_temp varchar2(32767);
598 l_end_of_msgbody varchar2(1);
599 l_msg varchar2(32000);
600 l_return_status varchar2(1);
601 l_msg_count number;
602 l_msg_data varchar2(2000);
603 l_ackn_id number;
604 l_server_timezone varchar2(240);
605
606 L_CREATION_DATE DATE;
607 L_CREATED_BY NUMBER;
608 L_LAST_UPDATE_DATE DATE;
609 L_LAST_UPDATED_BY NUMBER;
610 L_LAST_UPDATE_LOGIN NUMBER;
611
612 BEGIN
613 --Bug 4073809 : start
614 l_document := empty_clob();
615 l_end_of_msgbody := 'N';
616 --Bug 4073809 : end
617
618
619
620 wf_notification.newclob(l_document,l_msg);
621 /* Check if Server time Zone is set to NULL and if error out */
622 VERIFY_TIMEZONE(X_TIMEZONE =>l_server_timezone,
623 X_ERROR => P_ERROR,
624 X_ERROR_MSG =>P_ERROR_MSG);
625
626 if P_ERROR > 0 then
627 RAISE TIMEZONE_ERROR;
628 end if;
629
630 --Generate a new Document ID
631 select EDR_PSIG_DOCUMENTS_S.nextval into l_document_id from dual;
632
633 --Bug 4672801: start
634 EDR_UTILITIES.getWhoColumns
635 ( creation_date => l_creation_date ,
636 created_by => l_created_by ,
637 last_update_date => l_last_update_date ,
638 last_updated_by => l_last_updated_by ,
639 last_update_login => l_last_update_login
640 );
641 --Bug 4672801: end
642
643 --Insert the document in pending status
644 INSERT into EDR_PSIG_DOCUMENTS
645 (DOCUMENT_ID,
646 PSIG_STATUS,
647 PSIG_TIMESTAMP,
648 --Bug 4672801: start
649 --PSIG_TIMEZONE
650 PSIG_TIMEZONE,
651 CREATION_DATE,
652 CREATED_BY,
653 LAST_UPDATE_DATE,
654 LAST_UPDATED_BY,
655 LAST_UPDATE_LOGIN
656 --Bug 4672801: end
657 )
658 values
659 (l_document_id,
660 'PENDING',
661 sysdate,
662 --Bug 4672801: start
663 --l_server_timezone
664 l_server_timezone,
665 l_CREATION_DATE,
666 l_CREATED_BY,
667 l_LAST_UPDATE_DATE,
668 l_LAST_UPDATED_BY,
669 l_LAST_UPDATE_LOGIN
670 --Bug 4672801: end
671 );
672
673 p_document_id:=l_document_id;
674
675 p_document_id:=l_document_id;
676
677 -- after inserting a row in the erecord table, insert a row in the
678 -- acknowledgement table with the default ack status of NOTACKNOWLEDGED
679 -- we dont have to do any validation at all as erecord id is valid
680 -- we just created it, status is valid, there is no risk of duplicate
681 -- ack status for this erecord as its just created in the step above
682
683 EDR_TRANS_ACKN_PVT.INSERT_ROW
684 ( p_api_version => 1.0 ,
685 p_init_msg_list => FND_API.G_TRUE ,
686 p_validation_level => FND_API.G_VALID_LEVEL_NONE ,
687 x_return_status => l_return_status ,
688 x_msg_count => l_msg_count ,
689 x_msg_data => l_msg_data ,
690 p_erecord_id => l_document_id ,
691 p_trans_status => EDR_CONSTANTS_GRP.g_no_ack_status ,
692 p_ackn_by => null ,
693 p_ackn_note => null ,
694 x_ackn_id => l_ackn_id
695 );
696
697 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
698 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
699 END IF;
700
701 EXCEPTION
702 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
703 FND_MSG_PUB.Count_And_Get
704 ( p_count => l_msg_count ,
705 p_data => l_msg_data
706 );
707 p_error := 22000;
708 P_ERROR_MSG := l_msg_data ;
709
710 WHEN TIMEZONE_ERROR then
711 NULL;
712 WHEN OTHERS then
713 P_ERROR:=SQLCODE;
714 P_ERROR_MSG:=SQLERRM;
715 END openDocument;
716
717 PROCEDURE changeDocumentstatus
718 (
719 P_DOCUMENT_ID IN NUMBER,
720 P_STATUS IN VARCHAR2,
721 P_ERROR OUT NOCOPY NUMBER,
722 P_ERROR_MSG OUT NOCOPY VARCHAR2
723 ) IS
724 l_status varchar2(240);
725 L_LAST_UPDATE_DATE DATE;
726 L_LAST_UPDATED_BY NUMBER;
727 L_LAST_UPDATE_LOGIN NUMBER;
728 CURSOR C1 is
729 SELECT count(*) from EDR_PSIG_DETAILS
730 WHERE document_id =P_DOCUMENT_ID AND
731 SIGNATURE_STATUS='PENDING';
732 l_count number;
733 BEGIN
734 /*12-26-2002 Start: Set the secure context to access edr_psig_documents table */
735 edr_ctx_pkg.set_secure_attr;
736 /*12-26-2002 End*/
737 /* CHECK if document Status is Valid */
738 IF UPPER(P_STATUS) NOT IN ('CANCEL','COMPLETE','ERROR','PENDING','REJECTED','TIMEDOUT') then
739 RAISE EDR_INVALID_DOC_STATUS;
740 END IF;
741
742 /* Check if document is existing */
743
744 GET_DOCUMENT_STATUS(P_DOCUMENT_ID =>P_DOCUMENT_ID,
745 X_STATUS =>L_STATUS,
746 X_ERROR =>P_ERROR,
747 X_ERROR_MSG =>P_ERROR_MSG);
748
749 IF P_ERROR > 0 THEN
750 RAISE EDR_GENERIC_ERROR;
751 END IF;
752 IF L_STATUS IN ('COMPLETE','REJECTED','CANCEL','TIMEDOUT') THEN
753 RAISE EDR_INVALID_DOC_TRAN;
754 END IF;
755
756 IF UPPER(P_STATUS) = 'COMPLETE' THEN
757 /* Check if there are any pending signatures */
758 OPEN C1;
759 FETCH C1 into l_count;
760 CLOSE C1;
761
762 IF l_count > 0 THEN
763 fnd_message.set_name('EDR','EDR_PSIG_DOC_SIGNATURES');
764 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_DOC_SIGNATURES');
765 P_ERROR_MSG:= fnd_message.get();
766 RAISE EDR_GENERIC_ERROR;
767 END IF;
768 END IF;
769
770
771 -- Document Exist
772 UPDATE EDR_PSIG_DOCUMENTS
773 set PSIG_STATUS=UPPER(p_STATUS),
774 --Bug 4672801: start
775 last_update_date = sysdate,
776 last_updated_by = fnd_global.user_id(),
777 last_update_login = fnd_global.login_id()
778 --Bug 4672801: end
779 where DOCUMENT_ID=P_DOCUMENT_ID;
780
781 --Bug 3101047: Start
782 if UPPER(p_status) in ('CANCEL','REJECTED') then
783 EDR_PSIG.UPDATE_PSIG_USER_DETAILS( P_DOCUMENT_ID => P_DOCUMENT_ID);
784 end if;
785 --Bug 3101047: End
786
787 EXCEPTION
788 WHEN EDR_GENERIC_ERROR then
789 NULL;
790 WHEN EDR_INVALID_DOC_TRAN then
791 fnd_message.set_name('EDR','EDR_PSIG_INVALID_DOC_TRAN');
792 fnd_message.set_token('FROM',l_status);
793 fnd_message.set_token('TO',p_status);
794 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_DOC_TRAN');
795 P_ERROR_MSG:= fnd_message.get();
796 WHEN EDR_INVALID_DOC_STATUS then
797 fnd_message.set_name('EDR','EDR_PSIG_INVALID_DOC_STATUS');
798 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_DOC_STATUS');
799 P_ERROR_MSG:= fnd_message.get();
800 WHEN OTHERS then
801 P_ERROR:=SQLCODE;
802 P_ERROR_MSG:=SQLERRM;
803 END changeDocumentstatus;
804
805 /* Document Update Procedure
806 IN:
807 PSIG_XML
808 PSIG_DOCUMENT
809 PSIG_DOCUMENTFORMAT
810 PSIG_REQUESTER
811 PSIG_SOURCE
812 EVENT_NAME
813 EVENT_KEY
814 DOCUMENT_ID
815 Description :
816 This procedure will update a docuemnt
817
818 */
819
820 PROCEDURE updateDocument
821 (
822 P_DOCUMENT_ID IN NUMBER,
823 P_PSIG_XML IN CLOB DEFAULT NULL,
824 P_PSIG_DOCUMENT IN CLOB DEFAULT NULL,
825 P_PSIG_DOCUMENTFORMAT IN VARCHAR2 DEFAULT NULL,
826 P_PSIG_REQUESTER IN VARCHAR2,
827 P_PSIG_SOURCE IN VARCHAR2 DEFAULT NULL,
828 P_EVENT_NAME IN VARCHAR2 DEFAULT NULL,
829 P_EVENT_KEY IN VARCHAR2 DEFAULT NULL,
830 p_WF_NID IN NUMBER DEFAULT NULL,
831 P_ERROR OUT NOCOPY NUMBER,
832 P_ERROR_MSG OUT NOCOPY VARCHAR2
833 ) IS
834 l_document_id NUMBER;
835 l_document CLOB;
836 l_xml CLOB;
837 l_temp varchar2(32767);
838 l_end_of_msgbody varchar2(1);
839 l_msg varchar2(32000);
840 l_count number;
841 l_status varchar2(240);
842 l_doc_format varchar2(240);
843 l_doc_req_disp_name varchar2(360); -- Bug 3604783
844
845 BEGIN
846
847 --Bug 4073809 : start
848 l_document := empty_clob();
849 l_end_of_msgbody := 'N';
850 --Bug 4073809 : end
851
852
853
854 /*12-26-2002 Start: Set the secure context to access edr_psig_documents table */
855 edr_ctx_pkg.set_secure_attr;
856 /*12-26-2002 End*/
857
858 /* Check if document is existing */
859
860 GET_DOCUMENT_STATUS(P_DOCUMENT_ID =>P_DOCUMENT_ID,
861 X_STATUS =>L_STATUS,
862 X_ERROR =>P_ERROR,
863 X_ERROR_MSG =>P_ERROR_MSG);
864
865 IF P_ERROR > 0 THEN
866 RAISE EDR_GENERIC_ERROR;
867 END IF;
868 IF L_STATUS IN ('COMPLETE','REJECTED','CANCEL','TIMEDOUT') THEN
869 RAISE EDR_INVALID_DOC_TRAN;
870 END IF;
871
872 wf_notification.newclob(l_document,l_msg);
873 if P_WF_NID is NULL then
874 /* Assign passed Document to l_document */
875 l_document:= P_PSIG_DOCUMENT;
876 else
877
878 /* Begin Bug Fix 3142631*/
879 /* Check the Document_type */
880 if (P_PSIG_DOCUMENTFORMAT is NULL
881 or P_PSIG_DOCUMENTFORMAT ='TEXT'
882 or P_PSIG_DOCUMENTFORMAT = WF_NOTIFICATION.doc_text) THEN
883 l_doc_format:=WF_NOTIFICATION.doc_text;
884 elsif ( P_PSIG_DOCUMENTFORMAT = 'HTML' or P_PSIG_DOCUMENTFORMAT = WF_NOTIFICATION.doc_html) THEN
885 l_doc_format:=WF_NOTIFICATION.doc_html;
886 else
887 fnd_message.set_name('EDR','EDR_PSIG_WFNTF_DOCFORMAT');
888 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_WFNTF_FORMAT');
889 P_ERROR_MSG:= fnd_message.get();
890 RAISE EDR_GENERIC_ERROR;
891 end if;
892
893 /* End Bug Fix 3142631*/
894
895 /* User requested to use Workflow notification body as text */
896 BEGIN
897 while (l_end_of_msgbody <> 'Y') Loop
898 wf_notification.GetFullBodyWrapper
899 ( NID => P_WF_NID ,
900 msgbody => l_msg ,
901 end_of_body => l_end_of_msgbody,
902 disptype => l_doc_format
903 );
904 wf_log_pkg.string(6, 'EDR_PSIG_rule.psig_rule','l_msg '|| l_msg);
905 wf_notification.writetoclob(l_document,l_msg);
906 end loop;
907 EXCEPTION
908 WHEN OTHERS then
909 fnd_message.set_name('EDR','EDR_PSIG_WFNTF_ERROR');
910 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_WFNTF_ERROR');
911 P_ERROR_MSG:= fnd_message.get();
912 RAISE EDR_GENERIC_ERROR;
913 END;
914 end if;
915 /* Recieve the CLOB */
916 l_XML:=P_PSIG_XML;
917
918 -- Bug 3604783 : Start
919 /* Recieve the document requester's display name */
920 l_doc_req_disp_name := edr_utilities.getuserdisplayname(p_psig_requester);
921
922 update EDR_PSIG_DOCUMENTS
923 set
924 PSIG_XML=l_xml,
925 PSIG_DOCUMENT=l_document,
926 PSIG_DOCUMENTFORMAT=P_PSIG_DOCUMENTFORMAT,
927 DOCUMENT_REQUESTER=p_PSIG_REQUESTER,
928 DOC_REQ_DISP_NAME=DECODE(DOC_REQ_DISP_NAME, NULL,
929 L_DOC_REQ_DISP_NAME, DOC_REQ_DISP_NAME),
930 PSIG_SOURCE=P_PSIG_SOURCE,
931 EVENT_NAME=P_EVENT_NAME,
932 EVENT_KEY=P_EVENT_KEY,
933 --Bug 4672801: start
934 last_update_date = sysdate,
935 last_updated_by = fnd_global.user_id(),
936 last_update_login = fnd_global.login_id()
937 --Bug 4672801: end
938 where DOCUMENT_ID=P_DOCUMENT_ID;
939 -- Bug 3604783 : End
940
941 EXCEPTION
942 WHEN EDR_GENERIC_ERROR then
943 NULL;
944 WHEN EDR_INVALID_DOC_TRAN then
945 fnd_message.set_name('EDR','EDR_PSIG_INVALID_DOC_TRAN');
946 fnd_message.set_token('FROM',l_status);
947 fnd_message.set_token('TO','COMPLETE');
948 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_DOC_TRAN');
949 P_ERROR_MSG:= fnd_message.get();
950 WHEN EDR_INVALID_DOC_STATUS then
951 fnd_message.set_name('EDR','EDR_PSIG_INVALID_DOC_STATUS');
952 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_DOC_STATUS');
953 P_ERROR_MSG:= fnd_message.get();
954 WHEN OTHERS then
955 P_ERROR:=SQLCODE;
956 P_ERROR_MSG:=SQLERRM;
957
958
959 END updateDocument;
960
961 /* Close Document
962 IN:
963 P_DOCUMENT_ID
964
965
966 */
967
968 PROCEDURE closeDocument
969 (
970 P_DOCUMENT_ID IN NUMBER,
971 P_ERROR OUT NOCOPY NUMBER,
972 P_ERROR_MSG OUT NOCOPY VARCHAR2
973 ) IS
974 l_status varchar2(240);
975 CURSOR C1 is
976 SELECT count(*) from EDR_PSIG_DETAILS
977 WHERE document_id =P_DOCUMENT_ID AND
978 SIGNATURE_STATUS='PENDING';
979 l_count number;
980 BEGIN
981 /*12-26-2002 Start: Set the secure context to access edr_psig_documents table */
982 edr_ctx_pkg.set_secure_attr;
983 /*12-26-2002 End*/
984
985 /* Check if document is existing */
986
987 GET_DOCUMENT_STATUS(P_DOCUMENT_ID =>P_DOCUMENT_ID,
988 X_STATUS =>L_STATUS,
989 X_ERROR =>P_ERROR,
990 X_ERROR_MSG =>P_ERROR_MSG);
991
992 IF nvl(P_ERROR,0) > 0 THEN
993 RAISE EDR_GENERIC_ERROR;
994 END IF;
995 IF L_STATUS IN ('COMPLETE','REJECTED','CANCEL','TIMEDOUT') THEN
996 RAISE EDR_INVALID_DOC_TRAN;
997 END IF;
998
999 /* Check if there are any pending signatures */
1000 OPEN C1;
1001 FETCH C1 into l_count;
1002 CLOSE C1;
1003
1004 IF l_count > 0 THEN
1005 fnd_message.set_name('EDR','EDR_PSIG_DOC_SIGNATURES');
1006 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_DOC_SIGNATURES');
1007 P_ERROR_MSG:= fnd_message.get();
1008 RAISE EDR_GENERIC_ERROR;
1009 END IF;
1010
1011 /* Document Exist */
1012 UPDATE EDR_PSIG_DOCUMENTS
1013 set PSIG_STATUS='COMPLETE',
1014 --Bug 4672801: start
1015 last_update_date = sysdate,
1016 last_updated_by = fnd_global.user_id(),
1017 last_update_login = fnd_global.login_id()
1018 --Bug 4672801: end
1019 where DOCUMENT_ID=P_DOCUMENT_ID;
1020
1021 EXCEPTION
1022 WHEN EDR_GENERIC_ERROR then
1023 NULL;
1024 WHEN EDR_INVALID_DOC_TRAN then
1025 fnd_message.set_name('EDR','EDR_PSIG_INVALID_DOC_TRAN');
1026 fnd_message.set_token('FROM',l_status);
1027 fnd_message.set_token('TO','COMPLETE');
1028 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_DOC_TRAN');
1029 P_ERROR_MSG:= fnd_message.get();
1030 WHEN OTHERS then
1031 P_ERROR:=SQLCODE;
1032 P_ERROR_MSG:=SQLERRM;
1033 END closeDocument;
1034
1035
1036
1037 /* Cancel Document
1038 IN:
1039 P_DOCUMENT_ID
1040
1041 */
1042
1043 PROCEDURE cancelDocument
1044 (
1045 P_DOCUMENT_ID IN NUMBER,
1046 P_ERROR OUT NOCOPY NUMBER,
1047 P_ERROR_MSG OUT NOCOPY VARCHAR2
1048 ) AS
1049 l_status varchar2(240);
1050
1051 BEGIN
1052 /*12-26-2002 Start: Set the secure context to access edr_psig_documents table */
1053 edr_ctx_pkg.set_secure_attr;
1054 /*12-26-2002 End*/
1055
1056 /* Check if document is existing */
1057
1058 GET_DOCUMENT_STATUS(P_DOCUMENT_ID =>P_DOCUMENT_ID,
1059 X_STATUS =>L_STATUS,
1060 X_ERROR =>P_ERROR,
1061 X_ERROR_MSG =>P_ERROR_MSG);
1062
1063 IF nvl(P_ERROR,0) > 0 THEN
1064 RAISE EDR_GENERIC_ERROR;
1065 END IF;
1066 IF L_STATUS IN ('COMPLETE','REJECTED','CANCEL','TIMEDOUT') THEN
1067 RAISE EDR_INVALID_DOC_TRAN;
1068 END IF;
1069
1070 /* Document Exist */
1071 UPDATE EDR_PSIG_DOCUMENTS
1072 set PSIG_STATUS='CANCEL',
1073 --Bug 4672801: start
1074 last_update_date = sysdate,
1075 last_updated_by = fnd_global.user_id(),
1076 last_update_login = fnd_global.login_id()
1077 --Bug 4672801: end
1078 where DOCUMENT_ID=P_DOCUMENT_ID;
1079
1080 EXCEPTION
1081 WHEN EDR_GENERIC_ERROR then
1082 NULL;
1083 WHEN EDR_INVALID_DOC_TRAN then
1084 fnd_message.set_name('EDR','EDR_PSIG_INVALID_DOC_TRAN');
1085 fnd_message.set_token('FROM',l_status);
1086 fnd_message.set_token('TO','CANCEL');
1087 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_DOC_TRAN');
1088 P_ERROR_MSG:= fnd_message.get();
1089 WHEN OTHERS then
1090 P_ERROR:=SQLCODE;
1091 P_ERROR_MSG:=SQLERRM;
1092 END cancelDocument;
1093
1094 /* Post Signatures
1095 IN:
1096 P_DOCUMENT_ID
1097 P_EVIDENCE_STORE_ID
1098 P_USER_NAME
1099 P_USER_RESPONSE
1100
1101 */
1102
1103 --Bug 3330240 : start
1104 --adding new in parameters
1105 --P_SIGNATURE_SEQUENCE
1106 --P_ADHOC_STATUS
1107
1108 PROCEDURE requestSignature
1109 (
1110 P_DOCUMENT_ID IN NUMBER,
1111 P_USER_NAME IN VARCHAR2,
1112 P_ORIGINAL_RECIPIENT IN VARCHAR2 DEFAULT NULL,
1113 P_OVERRIDING_COMMENTS IN VARCHAR2 DEFAULT NULL,
1114 P_SIGNATURE_SEQUENCE IN NUMBER DEFAULT NULL,
1115 P_ADHOC_STATUS IN VARCHAR2 DEFAULT NULL,
1116 P_SIGNATURE_ID OUT NOCOPY NUMBER,
1117 P_ERROR OUT NOCOPY NUMBER,
1118 P_ERROR_MSG OUT NOCOPY VARCHAR2
1119 ) IS
1120 --bug 3330240 : end
1121 l_status varchar2(240);
1122 l_usrstatus boolean;
1123 l_signature_id NUMBER;
1124 l_cpint Number;
1125 l_count number;
1126 l_server_timezone varchar2(240);
1127 --Bug 3330240 :Start
1128 -- we need to check the uniquencess of user now on adhoc_status also
1129 -- default signer can now have two rows with ADHOC_STATUS as DELETED
1130 -- and ADDED also
1131 /*
1132 CURSOR C1 is
1133 Select count(*)
1134 from EDR_PSIG_DETAILS
1135 where document_id=p_document_id and
1136 USER_NAME=P_USER_NAME and
1137 NVL(ORIGINAL_RECIPIENT,0)=NVL(P_ORIGINAL_RECIPIENT,0);
1138 */
1139 CURSOR C1 is
1140 Select count(*)
1141 from EDR_PSIG_DETAILS
1142 where document_id=p_document_id and
1143 USER_NAME=P_USER_NAME and
1144 NVL(ORIGINAL_RECIPIENT,0)=NVL(P_ORIGINAL_RECIPIENT,0) and
1145 NVL(ADHOC_STATUS,'0') <> 'DELETED';
1146
1147 l_signer_seq number :=0;
1148 --Bug 3330240: end
1149
1150 BEGIN
1151 /*12-26-2002 Start: Set the secure context to access edr_psig_documents table */
1152 edr_ctx_pkg.set_secure_attr;
1153 /*12-26-2002 End*/
1154
1155 /* Check if Server time Zone is set to NULL and if error out */
1156 VERIFY_TIMEZONE(X_TIMEZONE =>l_server_timezone,
1157 X_ERROR => P_ERROR,
1158 X_ERROR_MSG =>P_ERROR_MSG);
1159
1160 if P_ERROR > 0 then
1161 RAISE TIMEZONE_ERROR;
1162 end if;
1163
1164 /*Check the validity of user */
1165 VALIDATE_USER (P_USER =>P_USER_NAME,
1166 X_STATUS =>L_usrSTATUS,
1167 X_ERROR =>P_ERROR,
1168 X_ERROR_MSG =>P_ERROR_MSG);
1169 IF nvl(P_ERROR,0) > 0 THEN
1170 RAISE EDR_GENERIC_ERROR;
1171 END IF;
1172 IF P_ORIGINAL_RECIPIENT IS NOT NULL THEN
1173 VALIDATE_USER(P_USER =>P_ORIGINAL_RECIPIENT,
1174 X_STATUS =>L_usrSTATUS,
1175 X_ERROR =>P_ERROR,
1176 X_ERROR_MSG =>P_ERROR_MSG);
1177
1178 IF nvl(P_ERROR,0) > 0 THEN
1179 RAISE EDR_GENERIC_ERROR;
1180 END IF;
1181 END IF;
1182
1183 /* Check if document is existing */
1184
1185 GET_DOCUMENT_STATUS(P_DOCUMENT_ID =>P_DOCUMENT_ID,
1186 X_STATUS =>L_STATUS,
1187 X_ERROR =>P_ERROR,
1188 X_ERROR_MSG =>P_ERROR_MSG);
1189
1190 IF nvl(P_ERROR,0) > 0 THEN
1191 RAISE EDR_GENERIC_ERROR;
1192 END IF;
1193 IF L_STATUS IN ('COMPLETE','REJECTED','CANCEL','TIMEDOUT') THEN
1194 RAISE EDR_INVALID_SIGN_REQUEST;
1195 END IF;
1196 /* Document Exist
1197 Check Duplicate Rows */
1198 OPEN c1;
1199 fetch c1 into l_count;
1200 CLOSE C1;
1201 IF l_count > 0 THEN
1202 RAISE EDR_DUPLICATE_SIGNER;
1203 END IF;
1204
1205
1206 --Bug 3330240: start
1207 --If the signature_sequence is not present in the argument
1208 --get the max and then add 1
1209 --raise error if the signature sequence is passed as 0
1210 IF (P_SIGNATURE_SEQUENCE is null ) then
1211 select (nvl(max(signature_sequence),0) +1) into l_signer_seq from edr_psig_details
1212 where document_id = P_DOCUMENT_ID;
1213 ELSIF ( P_SIGNATURE_SEQUENCE <= 0) THEN
1214 fnd_message.set_name('EDR','EDR_PSIG_INVALID_SIGNATURE_SEQ');
1215 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_SIGNATURE_SEQ');
1216 P_ERROR_MSG:= fnd_message.get();
1217 RAISE EDR_GENERIC_ERROR;
1218 ELSE
1219 l_signer_seq := P_SIGNATURE_SEQUENCE;
1220 END IF;
1221 --verify the adhoc status value if its not null
1222 --adhoc status should be ADDED or DELETED only
1223 IF (P_ADHOC_STATUS is not null) then
1224 IF (P_ADHOC_STATUS not in ('ADDED','DELETED') ) then
1225 fnd_message.set_name('EDR','EDR_PSIG_INVALID_ADHOC_STATUS');
1226 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_ADHOC_STATUS');
1227 P_ERROR_MSG:= fnd_message.get();
1228 RAISE EDR_GENERIC_ERROR;
1229 END IF;
1230 END IF;
1231
1232 --Bug 3330240 : end
1233 /* Get New Signature Id */
1234 select EDR_PSIG_DETAILS_S.nextval into l_signature_id from dual;
1235 /* Insert signature Details */
1236
1237 --Bug 3330240 : Start
1238 --Inert the adhoc_status, signature_sequence also
1239 -- and also the 5 WHO columns
1240 /*
1241 INSERT into EDR_PSIG_DETAILS
1242 (
1243 SIGNATURE_ID,
1244 DOCUMENT_ID,
1245 USER_NAME,
1246 ORIGINAL_RECIPIENT,
1247 SIGNATURE_OVERRIDING_COMMENTS,
1248 SIGNATURE_STATUS
1249 )
1250 values
1251 (
1252 L_SIGNATURE_ID,
1253 P_DOCUMENT_ID,
1254 P_USER_NAME,
1255 P_ORIGINAL_RECIPIENT,
1256 P_OVERRIDING_COMMENTS,
1257 'PENDING'
1258 );
1259 */
1260 INSERT into EDR_PSIG_DETAILS
1261 (
1262 SIGNATURE_ID,
1263 DOCUMENT_ID,
1264 USER_NAME,
1265 ORIGINAL_RECIPIENT,
1266 SIGNATURE_OVERRIDING_COMMENTS,
1267 SIGNATURE_STATUS,
1268 CREATION_DATE,
1269 CREATED_BY ,
1270 LAST_UPDATE_DATE,
1271 LAST_UPDATED_BY,
1272 LAST_UPDATE_LOGIN ,
1273 ADHOC_STATUS,
1274 SIGNATURE_SEQUENCE
1275 )
1276 values
1277 (
1278 L_SIGNATURE_ID,
1279 P_DOCUMENT_ID,
1280 P_USER_NAME,
1281 P_ORIGINAL_RECIPIENT,
1282 P_OVERRIDING_COMMENTS,
1283 'PENDING',
1284 SYSDATE,
1285 FND_GLOBAL.USER_ID,
1286 SYSDATE,
1287 FND_GLOBAL.USER_ID,
1288 FND_GLOBAL.LOGIN_ID,
1289 P_ADHOC_STATUS,
1290 l_signer_seq
1291 );
1292 --Bug 3330240 : end
1293 P_SIGNATURE_ID:=l_SIGNATURE_ID;
1294 EXCEPTION
1295 WHEN EDR_GENERIC_ERROR then
1296 NULL;
1297 WHEN TIMEZONE_ERROR then
1298 NULL;
1299 WHEN EDR_INVALID_SIGN_REQUEST then
1300 fnd_message.set_name('EDR','EDR_PSIG_INVALID_SIGN_REQUEST');
1301 fnd_message.set_token('STATUS',L_STATUS);
1302 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_SIGN_REQUEST');
1303 P_ERROR_MSG:= fnd_message.get();
1304 WHEN EDR_DUPLICATE_SIGNER then
1305 fnd_message.set_name('EDR','EDR_PSIG_DUPLICATE_SIGNER');
1306 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_DUPLICATE_SIGNER');
1307 P_ERROR_MSG:= fnd_message.get();
1308 WHEN OTHERS then
1309 P_ERROR:=SQLCODE;
1310 P_ERROR_MSG:=SQLERRM;
1311 END requestSignature;
1312
1313 /* Post Signatures
1314 IN:
1315 P_DOCUMENT_ID
1316 P_EVIDENCE_STORE_ID
1317 P_USER_NAME
1318 P_USER_RESPONSE
1319
1320 */
1321
1322
1323 PROCEDURE postSignature
1324 (
1325 P_DOCUMENT_ID IN NUMBER,
1326 P_EVIDENCE_STORE_ID IN VARCHAR2,
1327 P_USER_NAME IN VARCHAR2,
1328 P_USER_RESPONSE IN VARCHAR2,
1329 P_ORIGINAL_RECIPIENT IN VARCHAR2 DEFAULT NULL,
1330 P_OVERRIDING_COMMENTS IN VARCHAR2 DEFAULT NULL,
1331 P_SIGNATURE_ID OUT NOCOPY NUMBER,
1332 P_ERROR OUT NOCOPY NUMBER,
1333 P_ERROR_MSG OUT NOCOPY VARCHAR2
1334 ) IS
1335
1336 l_status varchar2(240);
1337 l_usrstatus boolean;
1338 l_signature_id NUMBER;
1339 l_server_timezone varchar2(240);
1340
1341 --Bug 3101047 : Start
1342 l_user_display_name VARCHAR2(360);
1343 l_orig_system VARCHAR2(240);
1344 l_orig_system_id NUMBER;
1345 --Bug 3101047 : End
1346
1347 --Bug 3330240 : start
1348 --we need to verify for signature_status CANCEL also
1349 -- as if a default signer is deleted we put the status as CANCEL
1350 /*
1351 CURSOR C1 IS
1352 select Signature_id from EDR_PSIG_DETAILS
1353 where DOCUMENT_ID=P_DOCUMENT_ID AND
1354 decode(P_ORIGINAL_RECIPIENT,NULL,USER_NAME,ORIGINAL_RECIPIENT)
1355 = nvl(P_ORIGINAL_RECIPIENT,P_USER_NAME)
1356 AND SIGNATURE_STATUS <> 'COMPLETE';
1357 */
1358
1359 CURSOR C1 IS
1360 select Signature_id from EDR_PSIG_DETAILS
1361 where DOCUMENT_ID=P_DOCUMENT_ID AND
1362 decode(P_ORIGINAL_RECIPIENT,NULL,USER_NAME,ORIGINAL_RECIPIENT)
1363 = nvl(P_ORIGINAL_RECIPIENT,P_USER_NAME)
1364 AND SIGNATURE_STATUS not in ('COMPLETE','CANCEL');
1365 l_pending_count number;
1366 --Bug 3330240 : end
1367
1368 BEGIN
1369 /*12-26-2002 Start: Set the secure context to access edr_psig_documents table */
1370 edr_ctx_pkg.set_secure_attr;
1371 /*12-26-2002 End*/
1372
1373 /* Check if Server time Zone is set to NULL and if error out */
1374 VERIFY_TIMEZONE(X_TIMEZONE =>l_server_timezone,
1375 X_ERROR => P_ERROR,
1376 X_ERROR_MSG =>P_ERROR_MSG);
1377
1378 if P_ERROR > 0 then
1379 RAISE TIMEZONE_ERROR;
1380 end if;
1381
1382 /*Check the validity of user */
1383 VALIDATE_USER (P_USER =>P_USER_NAME,
1384 X_STATUS =>L_usrSTATUS,
1385 X_ERROR =>P_ERROR,
1386 X_ERROR_MSG =>P_ERROR_MSG);
1387 IF nvl(P_ERROR,0) > 0 THEN
1388 RAISE EDR_GENERIC_ERROR;
1389 END IF;
1390 IF P_ORIGINAL_RECIPIENT IS NOT NULL THEN
1391 VALIDATE_USER(P_USER =>P_ORIGINAL_RECIPIENT,
1392 X_STATUS =>L_usrSTATUS,
1393 X_ERROR =>P_ERROR,
1394 X_ERROR_MSG =>P_ERROR_MSG);
1395 IF nvl(P_ERROR,0) > 0 THEN
1396 RAISE EDR_GENERIC_ERROR;
1397 END IF;
1398 END IF;
1399
1400 /* Check if document is existing */
1401
1402 GET_DOCUMENT_STATUS(P_DOCUMENT_ID =>P_DOCUMENT_ID,
1403 X_STATUS =>L_STATUS,
1404 X_ERROR =>P_ERROR,
1405 X_ERROR_MSG =>P_ERROR_MSG);
1406
1407 IF nvl(P_ERROR,0) > 0 THEN
1408 RAISE EDR_GENERIC_ERROR;
1409 END IF;
1410 IF L_STATUS IN ('COMPLETE','REJECTED','CANCEL','TIMEDOUT') THEN
1411 RAISE EDR_INVALID_SIGN_REQUEST;
1412 END IF;
1413
1414
1415 -- IF P_ORIGINAL_RECIPIENT is NULL THEN
1416 OPEN C1;
1417 FETCH C1 into l_signature_id;
1418 IF c1%NOTFOUND THEN
1419 fnd_message.set_name('EDR','EDR_PSIG_INVALID_SIGN_POST');
1420 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_SIGN_POST');
1421 P_ERROR_MSG:= fnd_message.get();
1422 RAISE EDR_GENERIC_ERROR;
1423 END IF;
1424 CLOSE C1;
1425
1426 --Bug 3330240 : start
1427 -- validate if the posting is in conjuction with the signature
1428 -- sequence
1429
1430 select count(*) into l_pending_count from edr_psig_details
1431 where signature_status = 'PENDING'
1432 and signature_sequence < (select signature_sequence
1433 from edr_psig_details
1434 where signature_id = l_signature_id)
1435 and document_id = P_DOCUMENT_ID
1436 and adhoc_status <> 'DELETED';
1437
1438 IF (l_pending_count > 0) THEN
1439 fnd_message.set_name('EDR','EDR_PSIG_PENDING_SIGNATURE');
1440 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_PENDING_SIGNATURE');
1441 P_ERROR_MSG:= fnd_message.get();
1442 RAISE EDR_GENERIC_ERROR;
1443 END IF;
1444 -- we need to update the WHO columns also
1445
1446 /* Above NVL logic is to make sure that we support old code */
1447 /* UPDATE signature Details */
1448 /*
1449 UPDATE EDR_PSIG_DETAILS
1450 SET
1451 EVIDENCE_STORE_ID=P_EVIDENCE_STORE_ID,
1452 USER_RESPONSE=P_USER_RESPONSE,
1453 ORIGINAL_RECIPIENT=nvl(P_ORIGINAL_RECIPIENT,ORIGINAL_RECIPIENT),
1454 USER_NAME=P_USER_NAME,
1455 SIGNATURE_OVERRIDING_COMMENTS=SIGNATURE_OVERRIDING_COMMENTS||' '||P_OVERRIDING_COMMENTS,
1456 SIGNATURE_TIMESTAMP=SYSDATE,
1457 SIGNATURE_TIMEZONE=l_server_timezone,
1458 SIGNATURE_STATUS='COMPLETE'
1459 WHERE DOCUMENT_ID=P_DOCUMENT_ID AND
1460 decode(P_ORIGINAL_RECIPIENT,NULL,USER_NAME,ORIGINAL_RECIPIENT)
1461 =nvl(P_ORIGINAL_RECIPIENT,P_USER_NAME);
1462 */
1463
1464 --Bug 3101047 : Start
1465 EDR_UTILITIES .getUserRoleInfo(p_user_name, l_user_display_name, l_orig_system, l_orig_system_id);
1466 UPDATE EDR_PSIG_DETAILS
1467 SET
1468 EVIDENCE_STORE_ID=P_EVIDENCE_STORE_ID,
1469 USER_RESPONSE=P_USER_RESPONSE,
1470 ORIGINAL_RECIPIENT=nvl(P_ORIGINAL_RECIPIENT,ORIGINAL_RECIPIENT),
1471 USER_NAME=P_USER_NAME,
1472 SIGNATURE_OVERRIDING_COMMENTS=SIGNATURE_OVERRIDING_COMMENTS||' '||P_OVERRIDING_COMMENTS,
1473 SIGNATURE_TIMESTAMP=SYSDATE,
1474 SIGNATURE_TIMEZONE=l_server_timezone,
1475 SIGNATURE_STATUS='COMPLETE',
1476 LAST_UPDATE_DATE = SYSDATE,
1477 LAST_UPDATED_BY=FND_GLOBAL.USER_ID,
1478 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
1479 USER_DISPLAY_NAME = L_USER_DISPLAY_NAME,
1480 ORIG_SYSTEM = L_ORIG_SYSTEM,
1481 ORIG_SYSTEM_ID = L_ORIG_SYSTEM_ID
1482 WHERE SIGNATURE_ID = l_SIGNATURE_ID;
1483 --bug 3101047 : end
1484
1485 --bug 3330240 : end
1486 P_SIGNATURE_ID:=l_SIGNATURE_ID;
1487
1488 EXCEPTION
1489 WHEN EDR_GENERIC_ERROR then
1490 NULL;
1491 WHEN TIMEZONE_ERROR then
1492 NULL;
1493 WHEN EDR_INVALID_SIGN_REQUEST then
1494 fnd_message.set_name('EDR','EDR_PSIG_INVALID_SIGN_REQUEST');
1495 fnd_message.set_token('STATUS',L_STATUS);
1496 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_SIGN_REQUEST');
1497 P_ERROR_MSG:= fnd_message.get();
1498 WHEN EDR_DUPLICATE_SIGNER then
1499 fnd_message.set_name('EDR','EDR_PSIG_DUPLICATE_SIGNER');
1500 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_DUPLICATE_SIGNER');
1501 P_ERROR_MSG:= fnd_message.get();
1502 WHEN OTHERS then
1503 P_ERROR:=SQLCODE;
1504 P_ERROR_MSG:=SQLERRM;
1505
1506 END postSignature;
1507
1508
1509 /* Cancel Signature
1510 IN:
1511 P_SIGNATURE_ID
1512
1513
1514 */
1515
1516 PROCEDURE cancelSignature
1517 (
1518 P_SIGNATURE_ID IN NUMBER,
1519 P_ERROR OUT NOCOPY NUMBER,
1520 P_ERROR_MSG OUT NOCOPY VARCHAR2
1521 ) IS
1522
1523 l_document_id NUMBER;
1524 l_status varchar2(240);
1525 l_sig_status varchar2(100);
1526 CURSOR C1
1527 is SELECT DOCUMENT_ID,SIGNATURE_STATUS
1528 from EDR_PSIG_DETAILS
1529 where SIGNATURE_ID = P_SIGNATURE_ID;
1530 BEGIN
1531 /*12-26-2002 Start: Set the secure context to access edr_psig_documents table */
1532 edr_ctx_pkg.set_secure_attr;
1533 /*12-26-2002 End*/
1534 /* Check if Signature Row Exist */
1535 OPEN C1;
1536 FETCH C1 into l_document_id,l_sig_status;
1537 IF c1%NOTFOUND THEN
1538 fnd_message.set_name('EDR','EDR_PSIG_INVALID_SIGNID');
1539 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_SIGNID');
1540 P_ERROR_MSG:= fnd_message.get();
1541 RAISE EDR_GENERIC_ERROR;
1542 END IF;
1543 CLOSE C1;
1544 /* Check if it's the row is in PENDING */
1545 IF l_sig_status = 'COMPLETE' THEN
1546 fnd_message.set_name('EDR','EDR_PSIG_SIGN_COMPLETE');
1547 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_SIGN_COMPLETE');
1548 P_ERROR_MSG:= fnd_message.get();
1549 RAISE EDR_GENERIC_ERROR;
1550 END IF;
1551
1552 /* Check if document is existing */
1553
1554 GET_DOCUMENT_STATUS(P_DOCUMENT_ID =>L_DOCUMENT_ID,
1555 X_STATUS =>L_STATUS,
1556 X_ERROR =>P_ERROR,
1557 X_ERROR_MSG =>P_ERROR_MSG);
1558
1559 IF nvl(P_ERROR,0) > 0 THEN
1560 RAISE EDR_GENERIC_ERROR;
1561 END IF;
1562 IF L_STATUS IN ('COMPLETE','REJECTED','CANCEL','TIMEDOUT') THEN
1563 RAISE EDR_INVALID_DOC_TRAN;
1564 END IF;
1565
1566 /* Document Exist */
1567 --Bug 3330240 : start
1568 --update the WHO columns also
1569 /*
1570 UPDATE EDR_PSIG_DETAILS
1571 set SIGNATURE_STATUS='CANCEL'
1572 where SIGNATURE_ID=P_SIGNATURE_ID;
1573 */
1574 UPDATE EDR_PSIG_DETAILS
1575 set SIGNATURE_STATUS='CANCEL',
1576 LAST_UPDATE_DATE=SYSDATE,
1577 LAST_UPDATED_BY=FND_GLOBAL.USER_ID,
1578 LAST_UPDATE_LOGIN=FND_GLOBAL.LOGIN_ID
1579 where SIGNATURE_ID=P_SIGNATURE_ID;
1580 --Bug 3330240 : end
1581 EXCEPTION
1582 WHEN EDR_GENERIC_ERROR then
1583 NULL;
1584 WHEN EDR_INVALID_DOC_TRAN then
1585 fnd_message.set_name('EDR','EDR_PSIG_SIGN_COMPLETE');
1586 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_SIGN_COMPLETE');
1587 P_ERROR_MSG:= fnd_message.get();
1588 WHEN OTHERS then
1589 P_ERROR:=SQLCODE;
1590 P_ERROR_MSG:=SQLERRM;
1591 END cancelSignature;
1592
1593
1594 /* Post Document Parameters */
1595
1596 PROCEDURE postDocumentParameter
1597 (
1598 P_DOCUMENT_ID IN NUMBER,
1599 P_PARAMETERS IN EDR_PSIG.params_table,
1600 P_ERROR OUT NOCOPY NUMBER,
1601 P_ERROR_MSG OUT NOCOPY VARCHAR2
1602 )
1603 IS
1604 l_status varchar2(240);
1605 i number;
1606 l_parameter_id NUMBER;
1607 L_ROWID ROWID;
1608
1609 BEGIN
1610 /*12-26-2002 Start: Set the secure context to access edr_psig_documents table */
1611 edr_ctx_pkg.set_secure_attr;
1612 /*12-26-2002 End*/
1613
1614 /* Check if document is existing */
1615 SELECT PSIG_STATUS into l_status
1616 from EDR_PSIG_DOCUMENTS
1617 where DOCUMENT_ID=P_DOCUMENT_ID;
1618 IF l_status in ('PENDING','ERROR') THEN
1619 /* Document Exist */
1620 /* Process Table and inser rows in Table */
1621 FOR i in 1.. P_PARAMETERS.count LOOP
1622 IF P_PARAMETERS(i).PARAM_NAME IS NOT NULL THEN
1623 BEGIN
1624 select parameter_id into l_parameter_id from EDR_PSIG_DOC_PARAMS_VL
1625 where document_id=P_DOCUMENT_ID and
1626 NAME=P_PARAMETERS(i).PARAM_NAME;
1627 EDR_PSIG_DOC_PARAMS_PKG.UPDATE_ROW (
1628 X_PARAMETER_ID => L_PARAMETER_ID,
1629 X_DOCUMENT_ID => P_DOCUMENT_ID,
1630 X_NAME => P_PARAMETERS(i).PARAM_NAME,
1631 X_VALUE => P_PARAMETERS(i).PARAM_VALUE,
1632 X_DISPLAY_NAME => NVL(P_PARAMETERS(i).PARAM_DISPLAYNAME,P_PARAMETERS(i).PARAM_NAME),
1633 X_LAST_UPDATE_DATE => SYSDATE,
1634 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1635 X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
1636
1637
1638 EXCEPTION
1639 WHEN NO_DATA_FOUND then
1640 /* Insert a ROW */
1641 select EDR_PSIG_PARAMS_S.nextval into l_parameter_id from dual;
1642 EDR_PSIG_DOC_PARAMS_PKG.INSERT_ROW(
1643 X_ROWID => L_ROWID,
1644 X_PARAMETER_ID => L_PARAMETER_ID,
1645 X_DOCUMENT_ID => P_DOCUMENT_ID,
1646 X_NAME => P_PARAMETERS(i).PARAM_NAME,
1647 X_VALUE => P_PARAMETERS(i).PARAM_VALUE,
1648 X_DISPLAY_NAME => NVL(P_PARAMETERS(i).PARAM_DISPLAYNAME,P_PARAMETERS(i).PARAM_NAME),
1649 X_CREATION_DATE => SYSDATE,
1650 X_CREATED_BY => FND_GLOBAL.USER_ID,
1651 X_LAST_UPDATE_DATE => SYSDATE,
1652 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1653 X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
1654 END;
1655 END IF;
1656 END LOOP;
1657 else
1658 raise DOCUMENT_CLOSE_ERROR;
1659 end if;
1660
1661 EXCEPTION
1662 WHEN DOCUMENT_CLOSE_ERROR then
1663 fnd_message.set_name('EDR','EDR_PSIG_DOC_CLOSED');
1664 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_DOC_CLOSED');
1665 P_ERROR_MSG:= fnd_message.get();
1666 WHEN NO_DATA_FOUND then
1667 fnd_message.set_name('EDR','EDR_PSIG_INVALID_DOCUMENT');
1668 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_DOCUMENT');
1669 P_ERROR_MSG:= fnd_message.get();
1670 WHEN OTHERS then
1671 P_ERROR:=SQLCODE;
1672 P_ERROR_MSG:=SQLERRM;
1673 END postDocumentParameter;
1674
1675 /* Delete Document Parameters */
1676
1677 PROCEDURE deleteDocumentParameter
1678 (
1679 P_DOCUMENT_ID IN NUMBER,
1680 P_PARAMETER_NAME IN VARCHAR,
1681 P_ERROR OUT NOCOPY NUMBER,
1682 P_ERROR_MSG OUT NOCOPY VARCHAR2
1683 )
1684 IS
1685
1686 l_status varchar2(240);
1687 i number;
1688 l_parameter_id NUMBER;
1689 L_ROWID ROWID;
1690 BEGIN
1691 fnd_message.set_name('EDR','EDR_PSIG_OBSOLETE_CALL');
1692 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_OBSOLETE_CALL');
1693 P_ERROR_MSG:= fnd_message.get();
1694
1695 END deleteDocumentParameter;
1696
1697
1698 /* Delete All Document Parameters */
1699
1700 PROCEDURE deleteAllDocumentParams
1701 (
1702 P_DOCUMENT_ID IN NUMBER,
1703 P_ERROR OUT NOCOPY NUMBER,
1704 P_ERROR_MSG OUT NOCOPY VARCHAR2
1705 )
1706 IS
1707 BEGIN
1708 fnd_message.set_name('EDR','EDR_PSIG_OBSOLETE_CALL');
1709 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_OBSOLETE_CALL');
1710 P_ERROR_MSG:= fnd_message.get();END deleteAllDocumentParams;
1711
1712 /* Post Signature Parameters */
1713
1714
1715 PROCEDURE postSignatureParameter
1716 (
1717 P_SIGNATURE_ID IN NUMBER,
1718 P_PARAMETERS IN EDR_PSIG.params_table,
1719 P_ERROR OUT NOCOPY NUMBER,
1720 P_ERROR_MSG OUT NOCOPY VARCHAR2
1721 )
1722 IS
1723
1724 l_status varchar2(240);
1725 i number;
1726 l_parameter_id NUMBER;
1727 L_ROWID ROWID;
1728 L_PARAM_NAME VARCHAR2(240);
1729 CURSOR C1 IS
1730 select parameter_id from EDR_PSIG_SIGN_PARAMS_VL
1731 where signature_id=P_SIGNATURE_ID and
1732 NAME=L_PARAM_NAME;
1733
1734 BEGIN
1735 /*12-26-2002 Start: Set the secure context to access edr_psig_documents table */
1736 edr_ctx_pkg.set_secure_attr;
1737 /*12-26-2002 End*/
1738
1739 /* Check if document is existing */
1740 SELECT PSIG_STATUS into l_status
1741 from EDR_PSIG_DOCUMENTS
1742 where DOCUMENT_ID in
1743 (select Document_id from EDR_PSIG_DETAILS
1744 where signature_id=P_SIGNATURE_ID);
1745 IF l_status in ('PENDING','ERROR') THEN
1746 /* Document Exist */
1747 /* Process Table and inser rows in Table */
1748 FOR i in 1.. P_PARAMETERS.count LOOP
1749 IF P_PARAMETERS(i).PARAM_NAME IS NOT NULL THEN
1750 L_PARAM_NAME:=P_PARAMETERS(i).PARAM_NAME;
1751 OPEN C1;
1752 FETCH C1 into l_parameter_id;
1753 IF C1%NOTFOUND THEN
1754 /* Insert a ROW */
1755 select EDR_PSIG_PARAMS_S.nextval into l_parameter_id from dual;
1756 EDR_PSIG_SIGN_PARAMS_PKG.INSERT_ROW(
1757 X_ROWID => L_ROWID,
1758 X_PARAMETER_ID => L_PARAMETER_ID,
1759 X_SIGNATURE_ID => P_SIGNATURE_ID,
1760 X_NAME => P_PARAMETERS(i).PARAM_NAME,
1761 X_VALUE => P_PARAMETERS(i).PARAM_VALUE,
1762 X_DISPLAY_NAME => NVL(P_PARAMETERS(i).PARAM_DISPLAYNAME,P_PARAMETERS(i).PARAM_NAME),
1763 X_CREATION_DATE => SYSDATE,
1764 X_CREATED_BY => FND_GLOBAL.USER_ID,
1765 X_LAST_UPDATE_DATE => SYSDATE,
1766 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1767 X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
1768 ELSE
1769 EDR_PSIG_SIGN_PARAMS_PKG.UPDATE_ROW(
1770 X_PARAMETER_ID => L_PARAMETER_ID,
1771 X_SIGNATURE_ID => P_SIGNATURE_ID,
1772 X_NAME => P_PARAMETERS(i).PARAM_NAME,
1773 X_VALUE => P_PARAMETERS(i).PARAM_VALUE,
1774 X_DISPLAY_NAME => NVL(P_PARAMETERS(i).PARAM_DISPLAYNAME,P_PARAMETERS(i).PARAM_NAME),
1775 X_LAST_UPDATE_DATE => SYSDATE,
1776 X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1777 X_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID);
1778 END IF;
1779 CLOSE C1;
1780 END IF;
1781 END LOOP;
1782 else
1783 raise DOCUMENT_CLOSE_ERROR;
1784 end if;
1785
1786 EXCEPTION
1787 WHEN DOCUMENT_CLOSE_ERROR then
1788 fnd_message.set_name('EDR','EDR_PSIG_DOC_CLOSED');
1789 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_DOC_CLOSED');
1790 P_ERROR_MSG:= fnd_message.get();
1791 WHEN NO_DATA_FOUND then
1792 fnd_message.set_name('EDR','EDR_PSIG_INVALID_DOCUMENT');
1793 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_DOCUMENT');
1794 P_ERROR_MSG:= fnd_message.get();
1795 WHEN OTHERS then
1796 P_ERROR:=SQLCODE;
1797 P_ERROR_MSG:=SQLERRM;
1798
1799 END postSignatureParameter;
1800
1801 /* Delete Signature Parameters */
1802
1803 PROCEDURE deleteSignatureParameter
1804 (
1805 P_SIGNATURE_ID IN NUMBER,
1806 P_PARAMETER_NAME IN VARCHAR,
1807 P_ERROR OUT NOCOPY NUMBER,
1808 P_ERROR_MSG OUT NOCOPY VARCHAR2
1809 )
1810 IS
1811 BEGIN
1812 fnd_message.set_name('EDR','EDR_PSIG_OBSOLETE_CALL');
1813 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_OBSOLETE_CALL');
1814 P_ERROR_MSG:= fnd_message.get();
1815 END deleteSignatureParameter;
1816
1817 /* Delete All Signature Parameters */
1818
1819 PROCEDURE deleteAllSignatureParams
1820 (
1821 P_SIGNATURE_ID IN NUMBER,
1822 P_ERROR OUT NOCOPY NUMBER,
1823 P_ERROR_MSG OUT NOCOPY VARCHAR2
1824 )
1825 IS
1826
1827 BEGIN
1828 fnd_message.set_name('EDR','EDR_PSIG_OBSOLETE_CALL');
1829 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_OBSOLETE_CALL');
1830 P_ERROR_MSG:= fnd_message.get();
1831 END deleteAllSignatureParams;
1832
1833 /* Get Document Details */
1834
1835 PROCEDURE getDocumentDetails
1836 (
1837 P_DOCUMENT_ID IN NUMBER,
1838 P_DOCUMENT OUT NOCOPY EDR_PSIG.DOCUMENT,
1839 P_DOCPARAMS OUT NOCOPY EDR_PSIG.params_table,
1840 P_SIGNATURES OUT NOCOPY EDR_PSIG.SignatureTable,
1841 P_ERROR OUT NOCOPY NUMBER,
1842 P_ERROR_MSG OUT NOCOPY VARCHAR2
1843 ) IS
1844 l_status varchar2(240);
1845 l_parameter_id NUMBER;
1846 i number;
1847
1848 CURSOR DOC_PARAMS
1849 is SELECT NAME,VALUE,DISPLAY_NAME
1850 from EDR_PSIG_DOC_PARAMS_VL
1851 where DOCUMENT_ID=P_DOCUMENT_ID;
1852 CURSOR SIGN_DETAILS
1853 is SELECT SIGNATURE_ID,
1854 DOCUMENT_ID,
1855 EVIDENCE_STORE_ID,
1856 USER_NAME,
1857 USER_RESPONSE,
1858 SIGNATURE_TIMESTAMP,
1859 SIGNATURE_TIMEZONE,
1860 SIGNATURE_STATUS,
1861 CREATION_DATE,
1862 CREATED_BY,
1863 LAST_UPDATE_DATE,
1864 LAST_UPDATE_LOGIN,
1865 LAST_UPDATED_BY,
1866 --Bug 3101047 : Start
1867 DECODE(SIGNATURE_TIMESTAMP,NULL,EDR_UTILITIES.GETUSERDISPLAYNAME(USER_NAME), USER_DISPLAY_NAME)USER_DISPLAY_NAME
1868 --Bug 3101047 : End
1869 FROM EDR_PSIG_DETAILS
1870 where DOCUMENT_ID=P_DOCUMENT_ID;
1871 BEGIN
1872 --Bug 4073809 : start
1873 i := 1;
1874 --Bug 4073809 : end
1875
1876
1877
1878
1879
1880 /* Fetch Document information */
1881 SELECT
1882 DOCUMENT_ID,
1883 PSIG_XML,
1884 PSIG_DOCUMENT,
1885 PSIG_DOCUMENTFORMAT,
1886 PSIG_TIMESTAMP,
1887 PSIG_TIMEZONE,
1888 DOCUMENT_REQUESTER,
1889 PSIG_STATUS,
1890 PSIG_SOURCE,
1891 EVENT_NAME,
1892 EVENT_KEY,
1893 PRINT_COUNT,
1894 CREATION_DATE,
1895 CREATED_BY,
1896 LAST_UPDATE_DATE,
1897 LAST_UPDATE_LOGIN,
1898 LAST_UPDATED_BY
1899 into
1900 P_DOCUMENT.DOCUMENT_ID,
1901 P_DOCUMENT.PSIG_XML,
1902 P_DOCUMENT.PSIG_DOCUMENT,
1903 P_DOCUMENT.PSIG_DOCUMENTFORMAT,
1904 P_DOCUMENT.PSIG_TIMESTAMP,
1905 P_DOCUMENT.PSIG_TIMEZONE,
1906 P_DOCUMENT.DOCUMENT_REQUESTER,
1907 P_DOCUMENT.PSIG_STATUS,
1908 P_DOCUMENT.PSIG_SOURCE,
1909 P_DOCUMENT.EVENT_NAME,
1910 P_DOCUMENT.EVENT_KEY,
1911 P_DOCUMENT.PRINT_COUNT,
1912 P_DOCUMENT.CREATION_DATE,
1913 P_DOCUMENT.CREATED_BY,
1914 P_DOCUMENT.LAST_UPDATE_DATE,
1915 P_DOCUMENT.LAST_UPDATE_LOGIN,
1916 P_DOCUMENT.LAST_UPDATED_BY
1917 from EDR_PSIG_DOCUMENTS
1918 where DOCUMENT_ID=P_DOCUMENT_ID;
1919 /* Fetch Document Parameters */
1920 open DOC_PARAMS;
1921 Loop
1922 -- Bug 3581537 : Start
1923 -- Replaced PARAM_NAME with PARAM_VALUE for the value column. Probably it was
1924 -- copy paste error.
1925 fetch DOC_PARAMS into P_DOCPARAMS(i).PARAM_NAME,P_DOCPARAMS(i).PARAM_VALUE,P_DOCPARAMS(i).PARAM_DISPLAYNAME ;
1926 -- Bug 3581537 : End
1927 exit when DOC_PARAMS%notfound;
1928 i:=i+1;
1929 end loop;
1930 close DOC_PARAMS;
1931 /* Fetch Signature Details */
1932 open SIGN_DETAILS;
1933 i:=1;
1934 Loop
1935 fetch SIGN_DETAILS into
1936 P_SIGNATURES(i).SIGNATURE_ID,
1937 P_SIGNATURES(i).DOCUMENT_ID,
1938 P_SIGNATURES(i).EVIDENCE_STORE_ID,
1939 P_SIGNATURES(i).USER_NAME,
1940 P_SIGNATURES(i).USER_RESPONSE,
1941 P_SIGNATURES(i).SIGNATURE_TIMESTAMP,
1942 P_SIGNATURES(i).SIGNATURE_TIMEZONE,
1943 P_SIGNATURES(i).SIGNATURE_STATUS,
1944 P_SIGNATURES(i).CREATION_DATE,
1945 P_SIGNATURES(i).CREATED_BY,
1946 P_SIGNATURES(i).LAST_UPDATE_DATE,
1947 P_SIGNATURES(i).LAST_UPDATE_LOGIN,
1948 P_SIGNATURES(i).LAST_UPDATED_BY,
1949 --Bug 3101047 : Start
1950 P_SIGNATURES(i).USER_DISPLAY_NAME;
1951 --Bug 3101047 : End
1952 exit when SIGN_DETAILS%notfound;
1953 i:=i+1;
1954 end loop;
1955 close SIGN_DETAILS;
1956
1957 EXCEPTION
1958 WHEN NO_DATA_FOUND then
1959 fnd_message.set_name('EDR','EDR_PSIG_INVALID_DOCUMENT');
1960 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_DOCUMENT');
1961 P_ERROR_MSG:= fnd_message.get();
1962
1963 WHEN OTHERS then
1964 P_ERROR:=SQLCODE;
1965 P_ERROR_MSG:=SQLERRM;
1966 END getDocumentDetails;
1967
1968
1969 PROCEDURE getSignatureDetails
1970 (
1971 P_SIGNATURE_ID IN NUMBER DEFAULT NULL,
1972 P_SIGNATUREDETAILS OUT NOCOPY EDR_PSIG.Signature,
1973 P_SIGNATUREPARAMS OUT NOCOPY EDR_PSIG.params_table,
1974 P_ERROR OUT NOCOPY NUMBER,
1975 P_ERROR_MSG OUT NOCOPY VARCHAR2
1976 ) AS
1977 l_status varchar2(240);
1978 l_parameter_id NUMBER;
1979 i number;
1980 CURSOR SIGN_PARAMS
1981 is SELECT PARAMETER_ID
1982 from EDR_PSIG_SIGN_PARAMS_VL
1983 where SIGNATURE_ID =P_SIGNATURE_ID;
1984 CURSOR SIG_PARAMS
1985 is SELECT NAME,VALUE,DISPLAY_NAME
1986 from EDR_PSIG_SIGN_PARAMS_VL
1987 where SIGNATURE_ID=P_SIGNATURE_ID;
1988 CURSOR SIGN_DETAILS
1989 is SELECT SIGNATURE_ID,
1990 DOCUMENT_ID,
1991 EVIDENCE_STORE_ID,
1992 USER_NAME,
1993 USER_RESPONSE,
1994 SIGNATURE_TIMESTAMP,
1995 SIGNATURE_TIMEZONE,
1996 SIGNATURE_STATUS,
1997 CREATION_DATE,
1998 CREATED_BY,
1999 LAST_UPDATE_DATE,
2000 LAST_UPDATE_LOGIN,
2001 LAST_UPDATED_BY,
2002 --Bug 3101047 : Start
2003 DECODE(SIGNATURE_TIMESTAMP,NULL,EDR_UTILITIES.GETUSERDISPLAYNAME(USER_NAME), USER_DISPLAY_NAME)USER_DISPLAY_NAME
2004 --Bug 3101047 : End
2005 FROM EDR_PSIG_DETAILS
2006 where SIGNATURE_ID=P_SIGNATURE_ID;
2007 BEGIN
2008
2009 --Bug 4073809 : start
2010 i := 1;
2011 --Bug 4073809 : end
2012
2013
2014
2015 /* Fetch Signature Details */
2016 open SIGN_DETAILS;
2017 Loop
2018 fetch SIGN_DETAILS into
2019 P_SIGNATUREDETAILS.SIGNATURE_ID,
2020 P_SIGNATUREDETAILS.DOCUMENT_ID,
2021 P_SIGNATUREDETAILS.EVIDENCE_STORE_ID,
2022 P_SIGNATUREDETAILS.USER_NAME,
2023 P_SIGNATUREDETAILS.USER_RESPONSE,
2024 P_SIGNATUREDETAILS.SIGNATURE_TIMESTAMP,
2025 P_SIGNATUREDETAILS.SIGNATURE_TIMEZONE,
2026 P_SIGNATUREDETAILS.SIGNATURE_STATUS,
2027 P_SIGNATUREDETAILS.CREATION_DATE,
2028 P_SIGNATUREDETAILS.CREATED_BY,
2029 P_SIGNATUREDETAILS.LAST_UPDATE_DATE,
2030 P_SIGNATUREDETAILS.LAST_UPDATE_LOGIN,
2031 P_SIGNATUREDETAILS.LAST_UPDATED_BY,
2032 --Bug 3101047 : Start
2033 P_SIGNATUREDETAILS.USER_DISPLAY_NAME;
2034 --Bug 3101047 : End
2035 exit when SIGN_DETAILS%notfound;
2036 i:=i+1;
2037 end loop;
2038 close SIGN_DETAILS;
2039
2040 /* Fetch Signature Parameters */
2041 open SIG_PARAMS;
2042 i:=1;
2043 Loop
2044 fetch SIG_PARAMS into P_SIGNATUREPARAMS(i).PARAM_NAME,P_SIGNATUREPARAMS(i).PARAM_VALUE,P_SIGNATUREPARAMS(i).PARAM_DISPLAYNAME ;
2045 exit when SIG_PARAMS%notfound;
2046 i:=i+1;
2047 end loop;
2048 close SIG_PARAMS;
2049
2050 EXCEPTION
2051 WHEN NO_DATA_FOUND then
2052 fnd_message.set_name('EDR','EDR_PSIG_INVALID_SIGNID');
2053 P_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_SIGNID');
2054 P_ERROR_MSG:= fnd_message.get();
2055 WHEN OTHERS then
2056 P_ERROR:=SQLCODE;
2057 P_ERROR_MSG:=SQLERRM;
2058 END getSignatureDetails;
2059
2060
2061 PROCEDURE updatePrintCount (
2062 P_DOC_ID IN edr_psig_documents.document_id%TYPE,
2063 P_NEW_COUNT OUT NOCOPY NUMBER
2064 ) IS PRAGMA AUTONOMOUS_TRANSACTION;
2065 BEGIN
2066
2067 SELECT print_count into P_NEW_COUNT
2068 FROM edr_psig_documents
2069 WHERE document_id = P_DOC_ID;
2070
2071 P_NEW_COUNT := NVL(P_NEW_COUNT,0) + 1;
2072
2073 UPDATE edr_psig_documents
2074 SET
2075 print_count = P_NEW_COUNT,
2076 --Bug 4672801: start
2077 last_update_date = sysdate,
2078 last_updated_by = fnd_global.user_id(),
2079 last_update_login = fnd_global.login_id()
2080 --Bug 4672801: end
2081 WHERE document_id = P_DOC_ID;
2082
2083 commit;
2084 END updatePrintCount;
2085
2086 --Bug 3161859 : start
2087
2088 /* Get the signatureid based on the originalrecipient
2089 * final receipient and documentid and signature status */
2090
2091 procedure getSignatureId (P_DOCUMENT_ID in number,
2092 P_ORIGINAL_RECIPIENT in varchar2,
2093 P_USER_NAME in varchar2,
2094 P_SIGNATURE_STATUS in varchar2,
2095 X_SIGNATURE_ID out NOCOPY number,
2096 X_ERROR out NOCOPY number,
2097 X_ERROR_MSG out NOCOPY varchar2)
2098 is
2099 CURSOR c1 is
2100 SELECT SIGNATURE_ID from EDR_PSIG_DETAILS
2101 WHERE DOCUMENT_ID = P_DOCUMENT_ID
2102 AND ORIGINAL_RECIPIENT = P_ORIGINAL_RECIPIENT
2103 AND USER_NAME = P_USER_NAME
2104 AND SIGNATURE_STATUS = P_SIGNATURE_STATUS;
2105 BEGIN
2106 OPEN c1;
2107 FETCH c1 into X_SIGNATURE_ID;
2108 if c1%NOTFOUND THEN
2109 raise DOCUMENT_NOT_FOUND;
2110 END IF;
2111 CLOSE c1;
2112 X_ERROR := 0;
2113 EXCEPTION
2114 WHEN DOCUMENT_NOT_FOUND THEN
2115 fnd_message.set_name('EDR','EDR_PSIG_INVALID_DOCUMENT');
2116 X_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_DOCUMENT');
2117 X_ERROR_MSG:= fnd_message.get();
2118 CLOSE c1;
2119 WHEN OTHERS then
2120 X_ERROR:=SQLCODE;
2121 X_ERROR_MSG:=SQLERRM;
2122 CLOSE c1;
2123 END getSignatureId;
2124
2125 /* Get the adhoc status for the signature id */
2126 procedure GET_ADHOC_STATUS ( P_SIGNATURE_ID IN NUMBER,
2127 X_STATUS OUT NOCOPY VARCHAR2,
2128 X_ERROR OUT NOCOPY NUMBER,
2129 X_ERROR_MSG OUT NOCOPY VARCHAR2)
2130 IS
2131 CURSOR C1 is
2132 SELECT ADHOC_STATUS from EDR_PSIG_DETAILS
2133 WHERE SIGNATURE_ID = P_SIGNATURE_ID;
2134
2135 BEGIN
2136 OPEN C1;
2137 FETCH C1 into X_STATUS;
2138 if c1%NOTFOUND THEN
2139 raise DOCUMENT_NOT_FOUND;
2140 END IF;
2141 CLOSE C1;
2142 X_ERROR := 0;
2143 EXCEPTION
2144 WHEN DOCUMENT_NOT_FOUND THEN
2145 fnd_message.set_name('EDR','EDR_PSIG_INVALID_SIGNATURE');
2146 X_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_SIGNATURE');
2147 X_ERROR_MSG:= fnd_message.get();
2148 CLOSE c1;
2149 WHEN OTHERS then
2150 X_ERROR:=SQLCODE;
2151 X_ERROR_MSG:=SQLERRM;
2152 CLOSE c1;
2153 END GET_ADHOC_STATUS;
2154
2155 /* Delete the adhoc user */
2156 procedure DELETE_ADHOC_USER ( P_SIGNATURE_ID IN NUMBER,
2157 X_ERROR OUT NOCOPY NUMBER,
2158 X_ERROR_MSG OUT NOCOPY VARCHAR2)
2159
2160 is
2161 l_document_id NUMBER;
2162 l_status varchar2(240);
2163 l_sig_status varchar2(100);
2164 l_adhoc_status varchar2(32);
2165 CURSOR C1
2166 is SELECT DOCUMENT_ID,SIGNATURE_STATUS, ADHOC_STATUS
2167 from EDR_PSIG_DETAILS
2168 where SIGNATURE_ID = P_SIGNATURE_ID;
2169 BEGIN
2170 /* set the secure context */
2171 edr_ctx_pkg.set_secure_attr;
2172 /* Check if Signature Row Exist */
2173 OPEN C1;
2174 FETCH C1 into l_document_id,l_sig_status, l_adhoc_status;
2175 IF c1%NOTFOUND THEN
2176 fnd_message.set_name('EDR','EDR_PSIG_INVALID_SIGNID');
2177 X_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_SIGNID');
2178 X_ERROR_MSG:= fnd_message.get();
2179 RAISE EDR_GENERIC_ERROR;
2180 END IF;
2181 CLOSE C1;
2182
2183 /* Check if the user is adhoc */
2184 IF (l_adhoc_status <> 'ADDED') THEN
2185 fnd_message.set_name('EDR','EDR_PSIG_NOT_ADHOC_SIGNER');
2186 x_error :=fnd_message.get_number('EDR','EDR_PSIG_NOT_ADHOC_SIGNER');
2187 X_ERROR_MSG:= fnd_message.get();
2188 RAISE EDR_GENERIC_ERROR;
2189 END IF;
2190
2191 /* Check if it's the row is in PENDING */
2192 IF l_sig_status = 'COMPLETE' THEN
2193 fnd_message.set_name('EDR','EDR_PSIG_SIGN_COMPLETE');
2194 X_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_SIGN_COMPLETE');
2195 X_ERROR_MSG:= fnd_message.get();
2196 RAISE EDR_GENERIC_ERROR;
2197 END IF;
2198
2199 /* Check if document is existing */
2200 GET_DOCUMENT_STATUS(P_DOCUMENT_ID =>L_DOCUMENT_ID,
2201 X_STATUS =>L_STATUS,
2202 X_ERROR =>X_ERROR,
2203 X_ERROR_MSG =>X_ERROR_MSG);
2204
2205 IF nvl(X_ERROR,0) > 0 THEN
2206 RAISE EDR_GENERIC_ERROR;
2207 END IF;
2208
2209 IF L_STATUS IN ('COMPLETE','REJECTED','CANCEL','TIMEDOUT') THEN
2210 RAISE EDR_INVALID_DOC_TRAN;
2211 END IF;
2212
2213
2214 /* Document Exist */
2215 DELETE FROM EDR_PSIG_DETAILS
2216 where SIGNATURE_ID=P_SIGNATURE_ID;
2217
2218 X_ERROR := 0;
2219 EXCEPTION
2220 WHEN EDR_GENERIC_ERROR then
2221 NULL;
2222
2223 WHEN EDR_INVALID_DOC_TRAN then
2224 fnd_message.set_name('EDR','EDR_PSIG_SIGN_COMPLETE');
2225 X_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_SIGN_COMPLETE');
2226 X_ERROR_MSG:= fnd_message.get();
2227
2228 WHEN OTHERS then
2229 X_ERROR:=SQLCODE;
2230 X_ERROR_MSG:=SQLERRM;
2231
2232 END DELETE_ADHOC_USER;
2233
2234 --Bug 3161859 : end
2235 --Bug 3330240 : start
2236 /* Update the Signer Sequence for the record */
2237 procedure UPDATE_SIGNATURE_SEQUENCE ( P_SIGNATURE_ID in number,
2238 P_SIGNATURE_SEQUENCE in number,
2239 X_ERROR OUT NOCOPY number,
2240 X_ERROR_MSG OUT NOCOPY varchar2)
2241 is
2242 l_document_id NUMBER;
2243 l_status varchar2(240);
2244 l_sig_status varchar2(100);
2245 CURSOR C1
2246 is SELECT DOCUMENT_ID,SIGNATURE_STATUS
2247 from EDR_PSIG_DETAILS
2248 where SIGNATURE_ID = P_SIGNATURE_ID;
2249
2250 BEGIN
2251 /*Start: Set the secure context to access edr_psig_documents table */
2252 edr_ctx_pkg.set_secure_attr;
2253 /* Check if Signature Row Exist */
2254 OPEN C1;
2255 FETCH C1 into l_document_id,l_sig_status;
2256 IF c1%NOTFOUND THEN
2257 fnd_message.set_name('EDR','EDR_PSIG_INVALID_SIGNID');
2258 X_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_SIGNID');
2259 X_ERROR_MSG:= fnd_message.get();
2260 RAISE EDR_GENERIC_ERROR;
2261 END IF;
2262 CLOSE C1;
2263
2264 /* Check if it's the row is in PENDING */
2265 IF l_sig_status = 'COMPLETE' THEN
2266 fnd_message.set_name('EDR','EDR_PSIG_SIGN_COMPLETE');
2267 X_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_SIGN_COMPLETE');
2268 X_ERROR_MSG:= fnd_message.get();
2269 RAISE EDR_GENERIC_ERROR;
2270 END IF;
2271
2272 /* Check if document is existing */
2273 GET_DOCUMENT_STATUS(P_DOCUMENT_ID =>L_DOCUMENT_ID,
2274 X_STATUS =>L_STATUS,
2275 X_ERROR =>X_ERROR,
2276 X_ERROR_MSG =>X_ERROR_MSG);
2277
2278 IF nvl(X_ERROR,0) > 0 THEN
2279 RAISE EDR_GENERIC_ERROR;
2280 END IF;
2281
2282 IF L_STATUS IN ('COMPLETE','REJECTED','CANCEL','TIMEDOUT') THEN
2283 RAISE EDR_INVALID_DOC_TRAN;
2284 END IF;
2285
2286 IF ( P_SIGNATURE_SEQUENCE <= 0) THEN
2287 fnd_message.set_name('EDR','EDR_PSIG_INVALID_SIGNATURE_SEQ');
2288 X_ERROR:= fnd_message.get_number('EDR','EDR_PSIG_INVALID_SIGNATURE_SEQ');
2289 X_ERROR_MSG:= fnd_message.get();
2290 RAISE EDR_GENERIC_ERROR;
2291 END IF;
2292
2293 /* Document Exist */
2294 UPDATE EDR_PSIG_DETAILS
2295 set SIGNATURE_SEQUENCE=P_SIGNATURE_SEQUENCE,
2296 LAST_UPDATE_DATE=SYSDATE,
2297 LAST_UPDATED_BY=FND_GLOBAL.USER_ID,
2298 LAST_UPDATE_LOGIN=FND_GLOBAL.LOGIN_ID
2299 where SIGNATURE_ID=P_SIGNATURE_ID;
2300
2301 X_ERROR := 0;
2302 EXCEPTION
2303 WHEN EDR_GENERIC_ERROR then
2304 NULL;
2305
2306 WHEN EDR_INVALID_DOC_TRAN then
2307 fnd_message.set_name('EDR','EDR_PSIG_SIGN_COMPLETE');
2308 X_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_SIGN_COMPLETE');
2309 X_ERROR_MSG:= fnd_message.get();
2310
2311 WHEN OTHERS then
2312 X_ERROR:=SQLCODE;
2313 X_ERROR_MSG:=SQLERRM;
2314 END UPDATE_SIGNATURE_SEQUENCE;
2315
2316 /* Update the Adhoc Status for the record */
2317 procedure UPDATE_ADHOC_STATUS ( P_SIGNATURE_ID in number,
2318 P_ADHOC_STATUS in varchar2,
2319 X_ERROR OUT NOCOPY number,
2320 X_ERROR_MSG OUT NOCOPY varchar2)
2321 is
2322 l_document_id NUMBER;
2323 l_status varchar2(240);
2324 l_sig_status varchar2(100);
2325 CURSOR C1
2326 is SELECT DOCUMENT_ID,SIGNATURE_STATUS
2327 from EDR_PSIG_DETAILS
2328 where SIGNATURE_ID = P_SIGNATURE_ID;
2329
2330 BEGIN
2331 /*Start: Set the secure context to access edr_psig_documents table */
2332 edr_ctx_pkg.set_secure_attr;
2333 /* Check if Signature Row Exist */
2334 OPEN C1;
2335 FETCH C1 into l_document_id,l_sig_status;
2336 IF c1%NOTFOUND THEN
2337 fnd_message.set_name('EDR','EDR_PSIG_INVALID_SIGNID');
2338 X_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_SIGNID');
2339 X_ERROR_MSG:= fnd_message.get();
2340 RAISE EDR_GENERIC_ERROR;
2341 END IF;
2342 CLOSE C1;
2343
2344 /* Check if it's the row is in PENDING */
2345 IF l_sig_status = 'COMPLETE' THEN
2346 fnd_message.set_name('EDR','EDR_PSIG_SIGN_COMPLETE');
2347 X_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_SIGN_COMPLETE');
2348 X_ERROR_MSG:= fnd_message.get();
2349 RAISE EDR_GENERIC_ERROR;
2350 END IF;
2351
2352 /* Check if document is existing */
2353 GET_DOCUMENT_STATUS(P_DOCUMENT_ID =>L_DOCUMENT_ID,
2354 X_STATUS =>L_STATUS,
2355 X_ERROR =>X_ERROR,
2356 X_ERROR_MSG =>X_ERROR_MSG);
2357
2358 IF nvl(X_ERROR,0) > 0 THEN
2359 RAISE EDR_GENERIC_ERROR;
2360 END IF;
2361
2362 IF L_STATUS IN ('COMPLETE','REJECTED','CANCEL','TIMEDOUT') THEN
2363 RAISE EDR_INVALID_DOC_TRAN;
2364 END IF;
2365
2366 --adhoc status should be ADDED or DELETED only
2367 IF (P_ADHOC_STATUS not in ('ADDED','DELETED') ) then
2368 fnd_message.set_name('EDR','EDR_PSIG_INVALID_ADHOC_STATUS');
2369 X_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_ADHOC_STATUS');
2370 X_ERROR_MSG:= fnd_message.get();
2371 RAISE EDR_GENERIC_ERROR;
2372 END IF;
2373 /* Document Exist */
2374 UPDATE EDR_PSIG_DETAILS
2375 set ADHOC_STATUS=P_ADHOC_STATUS,
2376 LAST_UPDATE_DATE=SYSDATE,
2377 LAST_UPDATED_BY=FND_GLOBAL.USER_ID,
2378 LAST_UPDATE_LOGIN=FND_GLOBAL.LOGIN_ID
2379 where SIGNATURE_ID=P_SIGNATURE_ID;
2380
2381 X_ERROR := 0;
2382 EXCEPTION
2383 WHEN EDR_GENERIC_ERROR then
2384 NULL;
2385
2386 WHEN EDR_INVALID_DOC_TRAN then
2387 fnd_message.set_name('EDR','EDR_PSIG_SIGN_COMPLETE');
2388 X_ERROR:=fnd_message.get_number('EDR','EDR_PSIG_SIGN_COMPLETE');
2389 X_ERROR_MSG:= fnd_message.get();
2390
2391 WHEN OTHERS then
2392 X_ERROR:=SQLCODE;
2393 X_ERROR_MSG:=SQLERRM;
2394 END UPDATE_ADHOC_STATUS;
2395
2396 --Bug 3330240 : end
2397
2398 -- Bug 3170251 - Start
2399 -- Added a getter to get PSIG_XML (eRecord XML ) in CLOB.
2400 --This procedure gets the PSIG_XML from EDR_PSIG_DOCUMENTS table
2401 --for the given p_document_id i.e. eRecordId
2402
2403 -- Start of comments
2404 -- API name : getERecordXML
2405 -- Type : Public Utility
2406 -- Function : Get the XML Document Contents for given eRecordId
2407 -- Pre-reqs : None.
2408 -- Parameters :
2409 -- IN : P_DOCUMENT_ID in number
2410 -- OUT : X_PSIG_XML OUT NOCOPY CLOB
2411 -- : X_ERROR_CODE out NOCOPY number
2412 -- : X_ERROR_MSG out NOCOPY varchar2
2413 -- End of comments
2414 procedure getERecordXML( P_DOCUMENT_ID number,
2415 X_PSIG_XML OUT NOCOPY CLOB,
2416 X_ERROR_CODE OUT NOCOPY NUMBER,
2417 X_ERROR_MSG OUT NOCOPY VARCHAR2 )
2418 IS
2419 BEGIN
2420 /* Set the security context to get access to PSIG_DOCUMENTS */
2421 edr_ctx_pkg.set_secure_attr;
2422
2423 select psig_xml into x_psig_xml
2424 from edr_psig_documents
2425 where document_id = p_document_id;
2426 exception
2427 WHEN NO_DATA_FOUND then
2428 fnd_message.set_name('EDR','EDR_PSIG_INVALID_DOCUMENT');
2429 X_ERROR_CODE:=fnd_message.get_number('EDR','EDR_PSIG_INVALID_DOCUMENT');
2430 X_ERROR_MSG:= fnd_message.get();
2431 WHEN OTHERS then
2432 X_ERROR_CODE:=SQLCODE;
2433 X_ERROR_MSG:=SQLERRM;
2434 END getERecordXML;
2435 -- Bug 3170251 - End
2436
2437
2438 --Bug 3101047: Start
2439 PROCEDURE UPDATE_PSIG_USER_DETAILS(P_DOCUMENT_ID IN NUMBER)
2440 IS
2441
2442 l_user_name VARCHAR2(100);
2443 l_user_display_name VARCHAR2(360);
2444 l_orig_system VARCHAR2(240);
2445 l_orig_system_id NUMBER;
2446
2447 CURSOR GET_PSIG_USER_NAME IS
2448 select USER_NAME from EDR_PSIG_DETAILS where DOCUMENT_ID=p_document_id
2449 and SIGNATURE_STATUS = 'PENDING'
2450 and USER_DISPLAY_NAME is null
2451 and ORIG_SYSTEM is null
2452 and ORIG_SYSTEM_ID is null;
2453
2454
2455 BEGIN
2456
2457
2458 SAVEPOINT PSIG_USER_DETAILS;
2459
2460 OPEN GET_PSIG_USER_NAME;
2461 LOOP
2462 FETCH GET_PSIG_USER_NAME INTO l_user_name;
2463 EXIT WHEN GET_PSIG_USER_NAME%NOTFOUND;
2464
2465 EDR_UTILITIES.GETUSERROLEINFO(P_USER_NAME => l_user_name,
2466 X_USER_DISPLAY_NAME => l_user_display_name,
2467 X_ORIG_SYSTEM => l_orig_system,
2468 X_ORIG_SYSTEM_ID => l_orig_system_id);
2469
2470
2471 update EDR_PSIG_DETAILS
2472 set
2473 USER_DISPLAY_NAME=l_user_display_name,
2474 ORIG_SYSTEM=l_orig_system,
2475 ORIG_SYSTEM_ID=l_orig_system_id
2476
2477 where
2478 USER_NAME=l_user_name
2479 and DOCUMENT_ID=p_document_id;
2480 END LOOP;
2481
2482 CLOSE GET_PSIG_USER_NAME;
2483
2484 EXCEPTION
2485 WHEN OTHERS THEN
2486 ROLLBACK TO PSIG_USER_DETAILS;
2487 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
2488 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
2489 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_PSIG');
2490 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','UPDATE_PSIG_USER_DETAILS');
2491 RAISE;
2492
2493 END UPDATE_PSIG_USER_DETAILS;
2494
2495 --Bug 3101047: End
2496
2497
2498 --Bug 3212117: Start
2499
2500 --This function would fetch the e-record header details in XML format for the specified e-record ID.
2501 FUNCTION GET_ERECORD_HEADER_XML(P_DOCUMENT_ID IN NUMBER)
2502
2503 RETURN XMLType
2504
2505 IS
2506
2507 --This variable would hold the resultant XML for e-record header details obtained from the query.
2508 l_result XMLType;
2509
2510 begin
2511
2512 --This query would fetch the e-record header details in XML format.
2513 select XMLELEMENT("ERECORD_HEADER_DETAILS",
2514 XMLFOREST(evid.event_name as EVENT_NAME,
2515 wfev.display_name as EVENT_DISPLAY_NAME,
2516 evid.event_key as EVENT_KEY,
2517 evid.document_id as ERECORD_ID,
2518 evid.PSIG_TIMESTAMP as EVENT_DATE,
2519 evid.psig_timezone as TIMEZONE,
2520 evid.psig_source as APPLICATION_SOURCE,
2521 evid.DOCUMENT_REQUESTER as DOCUMENT_REQUESTER,
2522 evid.DOC_REQ_DISP_NAME DOCUMENT_REQUESTER_DISP_NAME,
2523 lookup1.meaning as DOCUMENT_STATUS,
2524 evid.print_count as PRINT_COUNT,
2525 evid.creation_date as CREATION_DATE,
2526 evid.created_by as CREATED_BY,
2527 evid.last_update_date as LAST_UPDATE_DATE,
2528 evid.last_updated_by as LAST_UPDATED_BY,
2529 evid.last_update_login as LAST_UPDATE_LOGIN,
2530 (select xmlagg(xmlelement("DOC_PARAMS",
2531 xmlforest(param1.name as PARAM_NAME,
2532 param1.value as PARAM_VALUE)
2533 ))
2534 from edr_psig_doc_params_vl param1
2535 where param1.document_id = evid.document_id
2536 ) as DOC_PARAM_DETAILS
2537 )
2538 )
2539
2540 into l_result
2541
2542 from edr_psig_documents evid,
2543 wf_events_vl wfev,
2544 fnd_lookup_values_vl lookup1
2545
2546 where
2547 evid.event_name = wfev.name
2548 and lookup1.lookup_type = 'EDR_DOCUMENT_STATUS'
2549 and lookup1.lookup_code = evid.psig_status
2550 and evid.document_id = p_document_id;
2551
2552 return l_result;
2553
2554 end GET_ERECORD_HEADER_XML;
2555
2556
2557 --This function would fetch the PSIG_XML identified by the specified e-record ID.
2558 FUNCTION GET_ERECORD_XML (P_DOCUMENT_ID IN NUMBER)
2559
2560 return XMLType
2561
2562 is
2563
2564 --This holds the query context.
2565 qryCtx DBMS_XMLGEN.ctxHandle;
2566
2567 --This would hold the resultant CLOB.
2568 l_result XMLType;
2569
2570 BEGIN
2571
2572 --Create the query context based on the SQL QUERY.
2573 qryCtx := dbms_xmlgen.newContext('select xmltype(psig_xml) as ERECORD_XML from edr_psig_documents where document_id = ' || p_document_id);
2574
2575 --We want the "<ROW>" tag to be null
2576 dbms_xmlgen.setRowTag(qryCtx,null);
2577
2578 --We want the "ROWSET" tag to be null
2579 dbms_xmlgen.setRowSetTag(qryCtx,null);
2580
2581 --We don't want any conversion of special characters.
2582 --This will ensure that the '<' and '> symbols in the base psig_xml are retained.
2583 dbms_xmlgen.setConvertSpecialChars(qryCtx,false);
2584
2585 --This would give us the required XML without any standard comments.
2586 select extract(dbms_xmlgen.getXMLType(qryCtx),'/ERECORD_XML/*')
2587 into l_result
2588 from dual;
2589
2590 SELECT xmlelement("ERECORD_XML",l_result)
2591 into l_result
2592 from dual;
2593
2594 return l_result;
2595
2596 END GET_ERECORD_XML;
2597
2598
2599 --This function would fetch the signature details for the specified e-record ID in XML format.
2600 FUNCTION GET_PSIG_DETAIL_XML(P_DOCUMENT_ID IN NUMBER)
2601
2602 return XMLType
2603
2604 is
2605
2606 l_result XMLType;
2607
2608 l_temp XMLType;
2609
2610 begin
2611
2612 --This query would fetch the required XML.
2613 select xmlelement("ERECORD_SIGNATURE_DETAILS",
2614 xmlagg(xmlelement("SIGNER_DETAIL",
2615 xmlforest(psig.signature_sequence as SIGNATURE_SEQUENCE,
2616 psig.user_name as SIGNER_USER_NAME,
2617 psig.user_display_name as SIGNER_DISPLAY_NAME,
2618 psig.user_response as SIGNER_RESPONSE,
2619 fnd_date.date_to_displayDT(psig.signature_timestamp,FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) as SIGNING_DATE,
2620 psig.SIGNATURE_OVERRIDING_COMMENTS as OVERRIDING_DETAILS,
2621 (select xmlagg(xmlelement("SIGNATURE_PARAMS",
2622 xmlforest(param1.name as PARAM_NAME,
2623 param1.value as PARAM_VALUE)
2624 )
2625 )
2626 from edr_psig_sign_params_vl param1
2627 where param1.signature_id = psig.signature_id
2628 ) as SIGNATURE_PARAM_DETAILS
2629 )
2630 )
2631 )
2632 )
2633
2634 into l_result
2635
2636 from edr_psig_details psig
2637
2638 where
2639
2640 psig.document_id = p_document_id;
2641
2642 select extract(l_result,'/ERECORD_SIGNATURE_DETAILS/*') into l_temp from dual;
2643
2644 if l_temp is null then
2645 l_result := null;
2646 end if;
2647
2648 return l_result;
2649
2650 END GET_PSIG_DETAIL_XML;
2651
2652
2653 --This function would fetch the acknowledgement details for the specified in XML format.
2654 FUNCTION GET_ACKN_DETAIL_XML(P_DOCUMENT_ID IN NUMBER)
2655
2656 return XMLType
2657
2658 is
2659
2660 l_result XMLType;
2661
2662 begin
2663
2664 --This query would fetch the required XML.
2665 select xmlelement("ACKNOWLEDGEMENT_DETAILS",
2666 xmlforest(ackn.ackn_id as acknowledgement_id,
2667 ackn.ackn_date as acknowledgement_date,
2668 lookup1.meaning as acknowledgement_status,
2669 ackn.ackn_by as acknowledged_by,
2670 ackn.ackn_note as acknowledgement_comment,
2671 ackn.CREATED_BY as CREATED_BY,
2672 ackn.CREATION_DATE as CREATION_DATE,
2673 ackn.LAST_UPDATED_BY as LAST_UPDATED_BY,
2674 ackn.LAST_UPDATE_LOGIN as LAST_UPDATE_LOGIN,
2675 ackn.LAST_UPDATE_DATE as LAST_UPDATE_DATE
2676 )
2677 )
2678
2679 into l_result
2680
2681 from edr_Trans_ackn ackn,
2682 fnd_lookup_values_vl lookup1
2683
2684 where ackn.erecord_id = p_document_id
2685 and lookup1.lookup_type = 'EDR_TRANS_STATUS'
2686 and lookup1.lookup_code = ackn.transaction_status;
2687
2688 return l_result;
2689
2690 end GET_ACKN_DETAIL_XML;
2691
2692
2693 --This function would fetch the print history details for the specified e-record ID in XML format.
2694 FUNCTION GET_PRINT_HISTORY_DETAIL_XML(P_DOCUMENT_ID IN NUMBER)
2695
2696 return XMLType
2697
2698 is
2699
2700 l_result XMLType;
2701
2702 l_temp XMLType;
2703
2704 begin
2705
2706
2707 --This query would fetch the required XML.
2708 select xmlelement("PRINT_HISTORY_DETAILS",
2709 xmlagg(xmlelement("PRINT_DETAILS",
2710 xmlforest(print.print_document_id as PRINT_ERECORD_ID,
2711 print.PRINT_COUNT as PRINT_COUNT,
2712 PRINT.PRINT_REQUESTED_BY as PRINT_REQUESTER,
2713 PRINT.USER_DISPLAY_NAME as PRINT_REQUESTER_DISPLAY_NAME,
2714 PRINT.PRINT_REQUESTED_DATE as PRINT_EVENT_DATE,
2715 (select lookup3.meaning
2716 from fnd_lookup_values_vl lookup3,
2717 edr_psig_documents printevid
2718 where lookup3.lookup_code = printevid.psig_status
2719 and lookup3.lookup_type = 'EDR_DOCUMENT_STATUS'
2720 and printevid.document_id = print.print_document_id
2721 ) as PRINT_ERECORD_STATUS,
2722 print.CREATED_BY as CREATED_BY,
2723 print.CREATION_DATE as CREATION_DATE,
2724 print.LAST_UPDATED_BY as LAST_UPDATED_BY,
2725 print.LAST_UPDATE_LOGIN as LAST_UPDATE_LOGIN,
2726 print.LAST_UPDATE_DATE as LAST_UPDATE_DATE
2727 )
2728 )
2729 )
2730 )
2731
2732 into l_result
2733
2734 from edr_psig_print_history print
2735
2736 where print.document_id = p_document_id;
2737
2738
2739 --Verify if the print history details actually exist.
2740 select extract(l_result,'/PRINT_HISTORY_DETAILS/*') into l_temp from dual;
2741
2742 --If the print history details do not exist, then set the result variable to null.
2743 if l_temp is null then
2744 l_result := null;
2745 end if;
2746
2747 return l_result;
2748
2749 END GET_PRINT_HISTORY_DETAIL_XML;
2750
2751
2752 --This function would fetch the parent e-record details for the specified e-record ID in XML format.
2753 FUNCTION GET_PARENT_ERECORD_DETAIL_XML(P_DOCUMENT_ID IN NUMBER,
2754 P_GET_ERECORD_XML IN VARCHAR2,
2755 P_GET_PSIG_DETAILS IN VARCHAR2,
2756 P_GET_ACKN_DETAILS IN VARCHAR2,
2757 P_GET_PRINT_DETAILS IN VARCHAR2)
2758
2759 RETURN XMLType
2760
2761 as
2762
2763 l_result XMLType;
2764 l_current_xml XMLType;
2765 l_parent_erecord_id NUMBER;
2766
2767 begin
2768
2769 --Obtain the parent e-record ID.
2770 select rel.parent_erecord_id into l_parent_erecord_id
2771 from edr_event_relationship rel
2772 where child_erecord_id = p_document_id;
2773
2774 --Obtain the e-record header details.
2775 l_current_xml := get_erecord_header_xml(p_document_id => l_parent_erecord_id);
2776
2777 --Copy the same to the result variable.
2778 select xmlconcat(l_result,l_current_xml) into l_result from dual;
2779
2780 --Fetch the psig_xml if required.
2781 if(FND_API.TO_BOOLEAN(p_get_erecord_xml)) then
2782
2783 l_current_xml := GET_ERECORD_XML(p_document_id => l_parent_erecord_id);
2784
2785 --Append the psig_xml into the result variable.
2786 select xmlconcat(l_result,l_current_xml) into l_result from dual;
2787
2788 end if;
2789
2790 --Fetch the signature details if required.
2791 if(FND_API.TO_BOOLEAN(p_get_psig_details)) then
2792
2793 l_current_xml := GET_PSIG_DETAIL_XML(p_document_id => l_parent_erecord_id);
2794
2795 --Append the signature details
2796 select xmlconcat(l_result,l_current_xml) into l_result from dual;
2797 end if;
2798
2799 if(FND_API.TO_BOOLEAN(p_get_ackn_details)) then
2800
2801 l_current_xml := get_ackn_detail_xml(p_document_id => l_parent_erecord_id);
2802
2803 --Append the acknowledgement details.
2804 select xmlconcat(l_result,l_current_xml) into l_result from dual;
2805
2806 end if;
2807
2808 if(FND_API.TO_BOOLEAN(p_get_print_details)) then
2809 l_current_xml := get_print_history_detail_xml(p_document_id => l_parent_erecord_id);
2810 select xmlconcat(l_result,l_current_xml) into l_result from dual;
2811 end if;
2812
2813 select xmlelement("ERECORD",l_result) into l_result from dual;
2814
2815 select xmlelement("PARENT_ERECORD_DETAILS",l_result) into l_result from dual;
2816
2817 return l_result;
2818
2819 EXCEPTION
2820 WHEN NO_DATA_FOUND then
2821 return null;
2822
2823 end get_parent_erecord_detail_xml;
2824
2825
2826 --This function is called recursively to fetch the entire child e-record hierarchy.
2827 FUNCTION PROCESS_RESULT_SET(X_CURRENT_LEVEL IN OUT NOCOPY NUMBER,
2828 X_CURRENT_NODE IN OUT NOCOPY XMLTYPE,
2829 P_LEVEL_TBL IN NUMBER_TBL,
2830 P_RESULT_XML_TBL IN XMLTYPE_TBL)
2831 return XMLType
2832 is
2833
2834 l_this_Level number;
2835 l_this_Node xmlType;
2836 l_result xmlType;
2837
2838 begin
2839
2840 l_this_Level := x_current_Level;
2841 l_this_Node := x_current_Node;
2842
2843 g_child_erecord_count := g_child_erecord_count + 1;
2844
2845
2846 if (g_child_erecord_count > p_level_tbl.count) then
2847 x_current_Level := -1;
2848
2849 else
2850
2851 x_current_level := p_level_tbl(g_child_erecord_count);
2852
2853 x_current_node := p_result_xml_tbl(g_child_erecord_count);
2854
2855 end if;
2856
2857
2858 while (x_current_Level >= l_this_Level) loop
2859
2860 -- Next Node is a decendant of sibling of this Node.
2861 if (x_current_Level > l_this_Level) then
2862
2863 -- Next Node is a decendant of this Node.
2864 l_result := process_Result_Set(x_current_Level, x_current_Node,p_level_tbl,
2865 p_result_xml_tbl);
2866 select xmlElement
2867 (
2868 "ERECORD",
2869 extract(l_this_Node,'/ERECORD/*'),
2870 xmlElement
2871 (
2872 "CHILD_ERECORD_DETAILS",
2873 l_result
2874 )
2875 )
2876 into l_this_Node
2877 from dual;
2878 else
2879 -- Next node is a sibling of this Node.
2880 l_result := process_Result_Set(x_current_Level, x_current_Node,p_level_tbl,
2881 p_result_xml_tbl);
2882
2883 --Append the child details in to the result variable.
2884 select xmlconcat(l_this_Node,l_result) into l_this_Node from dual;
2885 end if;
2886 end loop;
2887
2888 return l_this_Node;
2889
2890 end process_result_set;
2891
2892
2893 --This function is used to obtain the child e-record details in XML for the specified e-record ID.
2894 --The entire recursive child hierarchy is obtained.
2895 FUNCTION GET_CHILD_ERECORD_DETAIL_XML(P_DOCUMENT_ID IN NUMBER,
2896 P_GET_ERECORD_XML IN VARCHAR2,
2897 P_GET_PSIG_DETAILS IN VARCHAR2,
2898 P_GET_ACKN_DETAILS IN VARCHAR2,
2899 P_GET_PRINT_DETAILS IN VARCHAR2)
2900 return XMLType
2901
2902 as
2903
2904 l_result XMLType;
2905
2906 l_current_xml XMLType;
2907
2908 l_result_xml_tbl XMLType_TBL;
2909
2910 l_level_tbl NUMBER_TBL;
2911
2912 l_current_level NUMBER;
2913 l_current_node XMLType;
2914
2915 l_child_erecord_ids NUMBER_TBL;
2916
2917 l_count NUMBER;
2918
2919
2920
2921 cursor get_child_erecord_ids_cur(p_document_id NUMBER) is
2922 select level,rel.CHILD_ERECORD_ID CHILD_ERECORD_ID
2923 from EDR_EVENT_RELATIONSHIP rel
2924 connect by prior CHILD_ERECORD_ID = PARENT_ERECORD_ID
2925 start with PARENT_ERECORD_ID = p_document_id and CHILD_ERECORD_ID <> p_document_id;
2926
2927
2928 begin
2929
2930 l_count := 0;
2931
2932 --Fetch the child e-record details hierarchy into the respective variables.
2933 open get_child_erecord_ids_cur(p_document_id);
2934 loop
2935 fetch get_child_erecord_ids_cur into l_level_tbl(l_count + 1),l_child_erecord_ids(l_count+1);
2936 exit when get_child_erecord_ids_cur%NOTFOUND;
2937 l_count := l_count + 1;
2938 end loop;
2939
2940 close get_child_erecord_ids_cur;
2941
2942 --If the no child e-records are present then return zero.
2943 if l_count = 0 then
2944 return null;
2945 end if;
2946
2947
2948 for i in 1..l_count loop
2949
2950 --For each child e-record fetch the required details in XML format.
2951 --Set them into a XML Type Table.
2952
2953 l_current_xml := get_erecord_header_xml(p_document_id => l_child_erecord_ids(i));
2954
2955 l_result_xml_tbl(i) := l_current_xml;
2956
2957 if(FND_API.TO_BOOLEAN(p_get_erecord_xml)) then
2958 l_current_xml := GET_ERECORD_XML(p_document_id => l_child_erecord_ids(i));
2959 select xmlconcat(l_result_xml_tbl(i),l_current_xml) into l_result_xml_tbl(i) from dual;
2960 end if;
2961
2962 if(FND_API.TO_BOOLEAN(p_get_psig_details)) then
2963 l_current_xml := GET_PSIG_DETAIL_XML(p_document_id => l_child_erecord_ids(i));
2964 select xmlconcat(l_result_xml_tbl(i),l_current_xml) into l_result_xml_tbl(i) from dual;
2965 end if;
2966
2967 if(FND_API.TO_BOOLEAN(p_get_ackn_details)) then
2968 l_current_xml := get_ackn_detail_xml(p_document_id => l_child_erecord_ids(i));
2969 select xmlconcat(l_result_xml_tbl(i),l_current_xml) into l_result_xml_tbl(i) from dual;
2970 end if;
2971
2972 if(FND_API.TO_BOOLEAN(p_get_print_details)) then
2973 l_current_xml := get_print_history_detail_xml(p_document_id => l_child_erecord_ids(i));
2974 select xmlconcat(l_result_xml_tbl(i),l_current_xml) into l_result_xml_tbl(i) from dual;
2975 end if;
2976
2977 select xmlelement("ERECORD",l_result_xml_tbl(i)) into l_result_xml_tbl(i) from dual;
2978
2979 end loop;
2980
2981 g_child_erecord_count := 1;
2982
2983 l_current_level := l_level_tbl(1);
2984 l_current_node := l_result_xml_tbl(1);
2985
2986 --Proceed the table of child e-record IDs.
2987 --Re-arrange them to form the correct hierarchy of child e-records.
2988 l_result := process_result_set(l_current_level,l_current_node,l_level_tbl,l_result_xml_tbl);
2989
2990 select xmlelement("CHILD_ERECORD_DETAILS",l_result) into l_result from dual;
2991
2992 return l_result;
2993
2994 end get_child_erecord_detail_xml;
2995
2996
2997
2998 PROCEDURE GET_XML_FOR_ERECORDS(P_ERECORD_IDS IN EDR_ERES_EVENT_PUB.ERECORD_ID_TBL_TYPE,
2999 P_GET_ERECORD_XML IN VARCHAR2,
3000 P_GET_PSIG_DETAILS IN VARCHAR2,
3001 P_GET_ACKN_DETAILS IN VARCHAR2,
3002 P_GET_PRINT_DETAILS IN VARCHAR2,
3003 P_GET_RELATED_EREC_DETAILS IN VARCHAR2,
3004 X_FINAL_XML OUT NOCOPY CLOB)
3005
3006 IS
3007
3008 --This variable would hold the result XML.
3009 l_result XMLType;
3010
3011 --This variable would hold the current XML data being processed.
3012 l_current_xml XMLType;
3013
3014 l_current_result XMLType;
3015
3016 BEGIN
3017
3018 for i in 1..P_ERECORD_IDS.COUNT loop
3019 l_current_result := null;
3020
3021 --Fetch the e-record header details in XML format.
3022 l_current_xml := get_erecord_header_xml(p_document_id => p_erecord_ids(i));
3023
3024 select xmlconcat(l_current_result,l_current_xml) into l_current_result from dual;
3025
3026 --If the psig_xml is required, fetch the same in XML format.
3027 if(FND_API.TO_BOOLEAN(p_get_erecord_xml)) then
3028 l_current_xml := GET_ERECORD_XML(p_document_id => p_erecord_ids(i));
3029 select xmlconcat(l_current_result,l_current_xml) into l_current_result from dual;
3030 end if;
3031
3032 --If the signature details are required, fetch the same in XML format.
3033 if(FND_API.TO_BOOLEAN(p_get_psig_details)) then
3034 l_current_xml := GET_PSIG_DETAIL_XML(p_document_id => p_erecord_ids(i));
3035 select xmlconcat(l_current_result,l_current_xml) into l_current_result from dual;
3036 end if;
3037
3038 --If the acknowledgement details are required, fetch the same in XML format.
3039 if(FND_API.TO_BOOLEAN(p_get_ackn_details)) then
3040 l_current_xml := get_ackn_detail_xml(p_document_id => p_erecord_ids(i));
3041 select xmlconcat(l_current_result,l_current_xml) into l_current_result from dual;
3042 end if;
3043
3044 --If the print history details are required, fetch the same in XML format.
3045 if(FND_API.TO_BOOLEAN(p_get_print_details)) then
3046 l_current_xml := get_print_history_detail_xml(p_document_id => p_erecord_ids(i));
3047 select xmlconcat(l_current_result,l_current_xml) into l_current_result from dual;
3048 end if;
3049
3050 --If the related e-record details are required, fetch the same in XML format.
3051 if(FND_API.TO_BOOLEAN(p_get_related_erec_details)) then
3052 l_current_xml := get_parent_erecord_detail_xml(p_document_id => p_erecord_ids(i),
3053 P_GET_ERECORD_XML => P_GET_ERECORD_XML,
3054 P_GET_PSIG_DETAILS => P_GET_PSIG_DETAILS,
3055 P_GET_ACKN_DETAILS => P_GET_ACKN_DETAILS,
3056 P_GET_PRINT_DETAILS => P_GET_PRINT_DETAILS
3057 );
3058 select xmlconcat(l_current_result,l_current_xml) into l_current_result from dual;
3059
3060
3061 l_current_xml := get_child_erecord_detail_xml(p_document_id => p_erecord_ids(i),
3062 P_GET_ERECORD_XML => P_GET_ERECORD_XML,
3063 P_GET_PSIG_DETAILS => P_GET_PSIG_DETAILS,
3064 P_GET_ACKN_DETAILS => P_GET_ACKN_DETAILS,
3065 P_GET_PRINT_DETAILS => P_GET_PRINT_DETAILS
3066 );
3067 select xmlconcat(l_current_result,l_current_xml) into l_current_result from dual;
3068 end if;
3069
3070 select xmlelement("ERECORD",l_current_result) into l_current_result from dual;
3071
3072 select xmlconcat(l_result,l_current_result) into l_result from dual;
3073
3074 end loop;
3075
3076 select xmlelement("ERECORDS",l_result) into l_result from dual;
3077
3078 --Convert the final result value into a CLOB.
3079 X_FINAL_XML := l_result.getClobVal;
3080
3081 EXCEPTION
3082 WHEN NO_DATA_FOUND THEN
3083 X_FINAL_XML := NULL;
3084
3085 END GET_XML_FOR_ERECORDS;
3086
3087
3088 --This PROCEDURE will fetch the event data in XML format.
3089 --The individual e-record details such as acknowledgement details, signature details etc... are fetched based
3090 --the value of the individual flags set.
3091 PROCEDURE GET_EVENT_XML(P_EVENT_NAME IN VARCHAR2,
3092 P_EVENT_KEY IN VARCHAR2,
3093 P_ERECORD_ID IN NUMBER,
3094 P_GET_ERECORD_XML IN VARCHAR2,
3095 P_GET_PSIG_DETAILS IN VARCHAR2,
3096 P_GET_ACKN_DETAILS IN VARCHAR2,
3097 P_GET_PRINT_DETAILS IN VARCHAR2,
3098 P_GET_RELATED_EREC_DETAILS IN VARCHAR2,
3099 X_FINAL_XML OUT NOCOPY CLOB
3100 )
3101
3102 is
3103
3104 l_count NUMBER;
3105
3106 l_temp_counter NUMBER;
3107
3108 l_erecord_ids EDR_ERES_EVENT_PUB.ERECORD_ID_TBL_TYPE;
3109
3110 --This cursor is used to fetch the e-record IDs identified by the event name and event key.
3111 cursor get_erecord_ids_cur(p_event_name VARCHAR2,
3112 p_event_key VARCHAR2) is
3113 select evid.document_id
3114 from edr_psig_documents evid
3115 where evid.event_name = p_event_name
3116 and evid.event_key = p_event_key;
3117
3118 INVALID_EVENT_NAME_ERROR EXCEPTION;
3119
3120 INVALID_EVENT_KEY_ERROR EXCEPTION;
3121
3122 INVALID_PARAMS_ERROR EXCEPTION;
3123
3124 begin
3125
3126 --Set the secure context.
3127 edr_ctx_pkg.set_secure_attr;
3128
3129 l_count := 0;
3130 l_temp_counter := 0;
3131
3132 if p_event_name is null and p_event_key is null and p_erecord_id is null then
3133
3134 RAISE INVALID_PARAMS_ERROR;
3135
3136 END IF;
3137
3138 --If the event name and event key are set with the e-record ID being null, then fetch the
3139 --e-record IDs identified by the event name/event key combination and set them on
3140 --the e-record ID table type.
3141 if(p_event_name is not null and p_event_key is not null and p_erecord_id is null) then
3142
3143 open get_erecord_ids_cur(p_event_name,
3144 p_event_key);
3145 loop
3146 fetch get_erecord_ids_cur into l_erecord_ids(l_count + 1);
3147 exit when get_erecord_ids_cur%NOTFOUND;
3148 l_count := l_count + 1;
3149 end loop;
3150
3151 close get_erecord_ids_cur;
3152
3153 if l_count = 0 then
3154 --Quit the API.
3155 return;
3156 end if;
3157
3158 --If only the e-record ID is specified then copy the same into the e-record ID table type.
3159 elsif (p_event_name is null and p_event_key is null and p_erecord_id is not null) then
3160
3161 select evid.document_id into l_erecord_ids(l_count + 1)
3162 from edr_psig_documents evid
3163 where evid.document_id = p_erecord_id;
3164 l_count := l_count +1;
3165
3166 elsif (p_event_name is not null and p_event_key is not null and p_erecord_id is not null) then
3167
3168 select evid.document_id into l_erecord_ids(l_count + 1)
3169 from edr_psig_documents evid
3170 where evid.event_name=p_event_name
3171 and evid.event_key = event_key
3172 and evid.document_id = p_erecord_id;
3173 l_count := l_count +1;
3174
3175 elsif (p_event_name is null and p_event_key is not null) then
3176
3177 --Event name is not set but event key is set.
3178 --Hence raise an exception.
3179 RAISE INVALID_EVENT_NAME_ERROR;
3180
3181 elsif (p_event_name is not null and p_event_key is null) then
3182
3183 --Event name is set, but event key is not set.
3184 --Hence raise an exception.
3185 RAISE INVALID_EVENT_KEY_ERROR;
3186
3187 end if;
3188
3189 GET_XML_FOR_ERECORDS(P_ERECORD_IDS => L_ERECORD_IDS,
3190 P_GET_ERECORD_XML => P_GET_ERECORD_XML,
3191 P_GET_PSIG_DETAILS => P_GET_PSIG_DETAILS,
3192 P_GET_ACKN_DETAILS => P_GET_ACKN_DETAILS,
3193 P_GET_PRINT_DETAILS => P_GET_PRINT_DETAILS,
3194 P_GET_RELATED_EREC_DETAILS => P_GET_RELATED_EREC_DETAILS,
3195 X_FINAL_XML => X_FINAL_XML);
3196
3197
3198 --Unset the secure context.
3199 edr_ctx_pkg.unset_secure_attr;
3200
3201
3202 EXCEPTION
3203 WHEN NO_DATA_FOUND THEN
3204 X_FINAL_XML := null;
3205
3206 WHEN INVALID_EVENT_NAME_ERROR THEN
3207
3208 FND_MESSAGE.SET_NAME('EDR','EDR_EVENT_XML_EVENT_NAME_ERR');
3209
3210 --Diagnostics Start
3211 if FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL then
3212 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3213 'edr.plsql.EDR_PSIG.GET_EVENT_XML',
3214 FALSE
3215 );
3216 end if;
3217
3218 APP_EXCEPTION.RAISE_EXCEPTION;
3219 --Diagnostics End
3220
3221 WHEN INVALID_EVENT_KEY_ERROR THEN
3222 FND_MESSAGE.SET_NAME('EDR','EDR_EVENT_XML_EVENT_KEY_ERR');
3223
3224 --Diagnostics Start
3225 if FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL then
3226 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3227 'edr.plsql.EDR_PSIG.GET_EVENT_XML',
3228 FALSE
3229 );
3230 end if;
3231
3232 APP_EXCEPTION.RAISE_EXCEPTION;
3233 --Diagnostics End
3234
3235 WHEN INVALID_PARAMS_ERROR THEN
3236 FND_MESSAGE.SET_NAME('EDR','EDR_EVENT_XML_PARAMS_ERR');
3237
3238 --Diagnostics Start
3239 if FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL then
3240 FND_LOG.MESSAGE(FND_LOG.LEVEL_EXCEPTION,
3241 'edr.plsql.EDR_PSIG.GET_EVENT_XML',
3242 FALSE
3243 );
3244 end if;
3245
3246 APP_EXCEPTION.RAISE_EXCEPTION;
3247 --Diagnostics End
3248
3249 WHEN OTHERS THEN
3250
3251 --Diagnostics Start
3252 FND_MESSAGE.SET_NAME('FND','FND_AS_UNEXPECTED_ERROR');
3253 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',SQLERRM);
3254 FND_MESSAGE.SET_TOKEN('PKG_NAME','EDR_PSIG');
3255 FND_MESSAGE.SET_TOKEN('PROCEDURE_NAME','GET_EVENT_XML');
3256 if FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL then
3257 FND_LOG.MESSAGE(FND_LOG.LEVEL_UNEXPECTED,
3258 'edr.plsql.EDR_PSIG.GET_EVENT_XML',
3259 FALSE
3260 );
3261 end if;
3262 --Diagnostics End
3263
3264
3265 END GET_EVENT_XML;
3266
3267 --Bug 3212117: End
3268
3269 --Bug 4577122: Start
3270 procedure clear_pending_signatures
3271 (p_document_id in number)
3272 is
3273 begin
3274 update edr_psig_details
3275 set signature_status = null,
3276 LAST_UPDATE_DATE=SYSDATE,
3277 LAST_UPDATED_BY=FND_GLOBAL.USER_ID,
3278 LAST_UPDATE_LOGIN=FND_GLOBAL.LOGIN_ID
3279 where document_id = p_document_id
3280 and signature_status = 'PENDING';
3281 end clear_pending_signatures;
3282 --Bug 4577122: End
3283
3284 END EDR_PSIG;