[Home] [Help]
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;
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;
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;
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;
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
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;
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;
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
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;
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:
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;
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;
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*/
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
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;
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;
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 */
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 ,
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 ,
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
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
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
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;
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;
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(),
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
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();
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');
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;
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;
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 */
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;
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:
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
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;
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;
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,
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();
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');
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;
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;
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
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 */
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;
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:
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(),
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();
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;
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 */
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(),
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();
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;
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: */
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';
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 */
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');
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
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;
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;
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;
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
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,
1256: P_OVERRIDING_COMMENTS,
1257: 'PENDING'
1258: );
1259: */
1260: INSERT into EDR_PSIG_DETAILS
1261: (
1262: SIGNATURE_ID,
1263: DOCUMENT_ID,
1264: USER_NAME,
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
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');
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;
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;
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';
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 */
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;
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;
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)
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:
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;
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),
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),
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
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');
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;
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;
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;
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;
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;
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;
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;
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:
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
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
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;
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;
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
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 */
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 */
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,
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,
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,
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,
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');
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');
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;
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;
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;
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:
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 */
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
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
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 */
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
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 */
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,
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,
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,
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');
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');
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;
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;
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:
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 */
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:
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
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
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);
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,
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;
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
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
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;
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
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
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,
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
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;
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:
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:
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,
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;
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
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;
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;
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
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;
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;
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;
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;
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:
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;
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:
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
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;
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 */
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;
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;
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;
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;
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:
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;
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:
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
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
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;
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 */
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;
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;
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;
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;
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:
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;
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 */
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
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
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;
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
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');
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;
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;
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;
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
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;
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: )
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
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 "
2576: dbms_xmlgen.setRowTag(qryCtx,null);
2577:
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: )
2632: )
2633:
2634: into l_result
2635:
2636: from edr_psig_details psig
2637:
2638: where
2639:
2640: psig.document_id = p_document_id;
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,
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:
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;
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
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;
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:
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:
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:
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',
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
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
3280: and signature_status = 'PENDING';
3281: end clear_pending_signatures;
3282: --Bug 4577122: End
3283:
3284: END EDR_PSIG;