[Home] [Help]
PACKAGE BODY: APPS.WF_DIG_SIG_EVIDENCE_STORE
Source
1 PACKAGE BODY WF_Dig_Sig_Evidence_Store AS
2 /* $Header: WFDSEVSB.pls 115.5 2003/01/13 04:09:36 vshanmug ship $ */
3
4 --
5 -- Function
6 -- GetMostRecentSigID
7 --
8 -- Purpose
9 -- Finds most recently created Sig_ID for a given object Type/ID
10 -- (e.g. WF_NTF 1234).
11 --
12 -- Returns: SIG_ID for success; -1 if not successful.
13 --
14 --
15
16 Function GetMostRecentSigID(
17 SigObjType VARCHAR2,
18 SigObjID Varchar2) return number is
19
20 /* cursor to get most recent activity */
21 cursor cursig(ObjType varchar2, ObjID number) is
22 Select SIG_ID
23 from wf_dig_sigs
24 where Sig_Obj_Type = ObjType
25 and Sig_Obj_ID = ObjID
26 order by sig_id desc;
27
28 retval number;
29
30 begin
31 /* Get the most recent signature for object */
32 open cursig(SigObjType, SigObjID);
33 fetch cursig into retval;
34 close cursig;
35
36 return retval;
37
38 exception
39 when others then return -1;
40 end;
41
42
43 --
44 -- Function
45 -- GetCertID
46 --
47 -- Purpose
48 -- Finds Cert_ID for a given Sig ID
49 --
50 -- Returns: Cert_ID for success; -1 if not successful.
51 --
52 --
53
54 Function GetCertID(SigID NUMBER) return number is
55
56 retval number;
57
58 begin
59 Select nvl(Cert_ID,-1)
60 into retval
61 from wf_dig_sigs
62 where Sig_Id = SigID;
63
64 return retval;
65 exception
66 when others then return -1;
67 end;
68
69 --
70 -- Function
71 -- GetSigFlavor
72 --
73 -- Purpose
74 -- Finds SigFlavor for a given Sig ID
75 --
76 -- Returns: SigFlavor for success; null if not successful.
77 --
78 --
79
80 Function GetSigFlavor(SigID NUMBER) return Varchar2 is
81
82 retval varchar2(30);
83
84 begin
85 Select Sig_Flavor
86 into retval
87 from wf_dig_sigs
88 where Sig_Id = SigID;
89
90 return retval;
91 exception
92 when others then return null;
93 end;
94
95 --
96 -- Function
97 -- GetSigPolicy
98 --
99 -- Purpose
100 -- Finds SigPolicy for a given Sig ID
101 --
102 -- Returns: SigPolicy for success; null if not successful.
103 --
104 --
105
106 Function GetSigPolicy(SigID NUMBER) return Varchar2 is
107
108 retval varchar2(30);
109
110 begin
111 Select Sig_Policy
112 into retval
113 from wf_dig_sigs
114 where Sig_Id = SigID;
115
116 return retval;
117 exception
118 when others then return null;
119 end;
120
121
122 --
123 -- Procedure
124 -- GetSignature
125 --
126 -- Purpose
127 -- Finds Signature for a given Sig ID
128 --
129
130 Procedure GetSignature(SigID NUMBER, Sig out nocopy Clob) is
131
132 begin
133 Select Signature
134 into Sig
135 from wf_dig_sigs
136 where Sig_Id = SigID;
137
138 exception
139 when others then Sig := null;
140 end;
141
142
143 --
144 -- Procedure
145 -- GetSigObjectInfo
146 --
147 -- Purpose
148 -- Finds Object Info for a given Sig ID
149 --
150
151 Procedure GetSigObjectInfo(SigID NUMBER, ObjType out nocopy Varchar2,
152 ObjID out nocopy varchar2, ObjText out nocopy CLOB) is
153
154 begin
155 Select Sig_Obj_Type, Sig_Obj_ID, Plaintext
156 into ObjType, ObjID, ObjText
157 from wf_dig_sigs
158 where Sig_Id = SigID;
159
160 exception
161 when others then
162 ObjType := null;
163 ObjID := '-1';
164 ObjText := null;
165 end;
166
167
168
169 --
170 -- Procedure
171 -- GetSigStatusInfo
172 --
173 -- Purpose
174 -- Finds Status Info for a given Sig ID
175 --
176
177 Procedure GetSigStatusInfo(SigID NUMBER, SigStatus out nocopy Number,
178 CreationDate out nocopy Date,
179 SignedDate out nocopy Date,
180 VerifiedDate out nocopy Date,
181 LastAttValDate out nocopy Date,
182 ValidatedDate out nocopy Date,
183 Ebuf out nocopy Varchar2,
184 Estack out nocopy Varchar2) is
185
186 begin
187 Select Status, Creation_Date, Signed_Date, Verified_Date,
188 Last_Validation_Attempt, Validated_Complete_Date, Errbuf, ErrStack
189 into SigStatus, CreationDate, SignedDate, VerifiedDate,
190 LastAttValDate, ValidatedDate, Ebuf, Estack
191 from wf_dig_sigs
192 where Sig_Id = SigID;
193
194 exception
195 when others then
196 SigStatus := -1;
197 CreationDate := null;
198 SignedDate := null;
199 VerifiedDate := null;
200 LastAttValDate := null;
201 ValidatedDate := null;
202 Ebuf := null;
203 Estack := null;
204 end;
205
206
207 --
208 -- Procedure
209 -- GetReqSignerInfo
210 --
211 -- Purpose
212 -- Finds Requested Signer Info for a given Sig ID
213 --
214
215 Procedure GetReqSignerInfo(SigID NUMBER, SignerType out nocopy Varchar2,
216 SignerID out nocopy varchar2) is
217
218
219 begin
220 Select Requested_Signer_Type, Requested_Signer_ID
221 into SignerType, SignerID
222 from wf_dig_sigs
223 where Sig_Id = SigID;
224
225 exception
226 when others then
227 SignerType := null;
228 SignerID := '-1';
229 end;
230
231
232 --
233 -- Procedure
234 -- GetActualSignerInfo
235 --
236 -- Purpose
237 -- Finds Actual Signer Info for a given Sig ID
238 --
239
240 Procedure GetActualSignerInfo(SigID NUMBER, SignerType out nocopy Varchar2,
241 SignerID out nocopy varchar2) is
242
243
244 begin
245 Select c.Owner_domain, c.Owner_ID
246 into SignerType, SignerID
247 from wf_dig_sigs S, wf_dig_certs C
248 where S.Sig_Id = SigID
249 and S.cert_id = C.cert_id;
250
251 exception
252 when others then
253 SignerType := null;
254 SignerID := '-1';
255 end;
256
257
258 --
259 -- Function
260 -- GetCertType
261 --
262 -- Purpose
263 -- Finds Cert_Type for a given Cert ID
264 --
265 -- Returns: CertType for success; null if not successful.
266 --
267 --
268
269 Function GetCertType(CertID NUMBER) return Varchar2 is
270
271 retval varchar2(30);
272
273 begin
274 Select Cert_Type
275 into retval
276 from wf_dig_certs
277 where Cert_Id = CertID;
278
279 return retval;
280 exception
281 when others then return null;
282 end;
283
284
285
286 END WF_Dig_Sig_Evidence_Store;