1 PACKAGE EDR_PSIG AS
2 /* $Header: EDRPSIGS.pls 120.2.12000000.1 2007/01/18 05:54:46 appldev ship $ */
3
4 /* Record Type Decleration */
5
6 TYPE params_rec IS RECORD (Param_Name VARCHAR2(80),
7 Param_Value VARCHAR2(4000),Param_displayname varchar2(240));
8 TYPE params_table is TABLE of params_rec INDEX by Binary_INTEGER;
9
10 TYPE Document is Record (
11 DOCUMENT_ID EDR_PSIG_DOCUMENTS.DOCUMENT_ID%TYPE,
12 PSIG_XML EDR_PSIG_DOCUMENTS.PSIG_XML%TYPE,
13 PSIG_DOCUMENT EDR_PSIG_DOCUMENTS.PSIG_DOCUMENT%TYPE,
14 PSIG_DOCUMENTFORMAT EDR_PSIG_DOCUMENTS.PSIG_DOCUMENTFORMAT%TYPE,
15 PSIG_TIMESTAMP EDR_PSIG_DOCUMENTS.PSIG_TIMESTAMP%TYPE,
16 PSIG_TIMEZONE EDR_PSIG_DOCUMENTS.PSIG_TIMEZONE%TYPE,
17 DOCUMENT_REQUESTER EDR_PSIG_DOCUMENTS.DOCUMENT_REQUESTER%TYPE,
18 PSIG_STATUS EDR_PSIG_DOCUMENTS.PSIG_STATUS%TYPE,
19 PSIG_SOURCE EDR_PSIG_DOCUMENTS.PSIG_SOURCE%TYPE,
20 EVENT_NAME EDR_PSIG_DOCUMENTS.EVENT_NAME%TYPE,
21 EVENT_KEY EDR_PSIG_DOCUMENTS.EVENT_KEY%TYPE,
22 PRINT_COUNT EDR_PSIG_DOCUMENTS.PRINT_COUNT%TYPE,
23 CREATION_DATE EDR_PSIG_DOCUMENTS.CREATION_DATE%TYPE,
24 CREATED_BY EDR_PSIG_DOCUMENTS.CREATED_BY%TYPE,
25 LAST_UPDATE_DATE EDR_PSIG_DOCUMENTS.LAST_UPDATE_DATE%TYPE,
26 LAST_UPDATED_BY EDR_PSIG_DOCUMENTS.LAST_UPDATED_BY%TYPE,
27 LAST_UPDATE_LOGIN EDR_PSIG_DOCUMENTS.LAST_UPDATE_LOGIN%TYPE
28 );
29
30 TYPE DocumentTable is TABLE of Document INDEX by Binary_INTEGER;
31
32 TYPE Signature is Record (
33 SIGNATURE_ID EDR_PSIG_DETAILS.SIGNATURE_ID%TYPE,
34 DOCUMENT_ID EDR_PSIG_DETAILS.DOCUMENT_ID%TYPE,
35 EVIDENCE_STORE_ID EDR_PSIG_DETAILS.EVIDENCE_STORE_ID%TYPE,
36 USER_NAME EDR_PSIG_DETAILS.USER_NAME%TYPE,
37 USER_RESPONSE EDR_PSIG_DETAILS.USER_RESPONSE%TYPE,
38 SIGNATURE_TIMESTAMP EDR_PSIG_DETAILS.SIGNATURE_TIMESTAMP%TYPE,
39 SIGNATURE_TIMEZONE EDR_PSIG_DETAILS.SIGNATURE_TIMEZONE%TYPE,
40 SIGNATURE_STATUS EDR_PSIG_DETAILS.SIGNATURE_STATUS%TYPE,
41 CREATION_DATE EDR_PSIG_DETAILS.CREATION_DATE%TYPE,
42 CREATED_BY EDR_PSIG_DETAILS.CREATED_BY%TYPE,
43 LAST_UPDATE_DATE EDR_PSIG_DETAILS.LAST_UPDATE_DATE%TYPE,
44 LAST_UPDATED_BY EDR_PSIG_DETAILS.LAST_UPDATED_BY%TYPE,
45 LAST_UPDATE_LOGIN EDR_PSIG_DETAILS.LAST_UPDATE_LOGIN%TYPE,
46 --Bug 3101047 : Start
47 USER_DISPLAY_NAME EDR_PSIG_DETAILS.USER_DISPLAY_NAME%TYPE
48 --Bug 3101047 : End
49 );
50
51 TYPE SignatureTable is TABLE of Signature INDEX by Binary_INTEGER;
52
53 --Bug 3212117: Start
54 Type XMLTYPE_TBL is table of XMLType INDEX BY BINARY_INTEGER;
55 Type NUMBER_TBL is table of NUMBER INDEX BY BINARY_INTEGER;
56 --Bug 3212117: End
57
58 /* Document Creation Procedure
59 IN:
60 PSIG_XML
61 PSIG_DOCUMENT
62 PSIG_DOCUMENTFORMAT
63 PSIG_REQUESTER
64 PSIG_SOURCE
65 EVENT_NAME
66 EVENT_KEY
67
68 */
69
70 PROCEDURE openDocument
71 (
72 P_PSIG_XML IN CLOB DEFAULT NULL,
73 P_PSIG_DOCUMENT IN CLOB DEFAULT NULL,
74 P_PSIG_DOCUMENTFORMAT IN VARCHAR2 DEFAULT NULL,
75 P_PSIG_REQUESTER IN VARCHAR2,
76 P_PSIG_SOURCE IN VARCHAR2 DEFAULT NULL,
77 P_EVENT_NAME IN VARCHAR2 DEFAULT NULL,
78 P_EVENT_KEY IN VARCHAR2 DEFAULT NULL,
79 p_WF_NID IN NUMBER DEFAULT NULL,
80 P_DOCUMENT_ID OUT NOCOPY NUMBER,
81 P_ERROR OUT NOCOPY NUMBER,
82 P_ERROR_MSG OUT NOCOPY VARCHAR2
83 );
84
85
86
87 /* Document Creation Procedure
88
89 Over Loaded Procedure if you just want to create a document and later update the columns you can use this
90 procedure */
91
92 PROCEDURE openDocument
93 ( P_DOCUMENT_ID OUT NOCOPY NUMBER,
94 P_ERROR OUT NOCOPY NUMBER,
95 P_ERROR_MSG OUT NOCOPY VARCHAR2
96 );
97
98
99 /* Close Document
100 IN:
101 P_DOCUMENT_ID
102
103 */
104
105 PROCEDURE closeDocument
106 (
107 P_DOCUMENT_ID IN NUMBER,
108 P_ERROR OUT NOCOPY NUMBER,
109 P_ERROR_MSG OUT NOCOPY VARCHAR2
110 );
111
112 /* Update Document
113 IN:
114 PSIG_XML
115 PSIG_DOCUMENT
116 PSIG_DOCUMENTFORMAT
117 PSIG_REQUESTER
118 PSIG_SOURCE
119 EVENT_NAME
120 EVENT_KEY
121
122 */
123
124 PROCEDURE updateDocument
125 (
126 P_DOCUMENT_ID IN NUMBER,
127 P_PSIG_XML IN CLOB DEFAULT NULL,
128 P_PSIG_DOCUMENT IN CLOB DEFAULT NULL,
129 P_PSIG_DOCUMENTFORMAT IN VARCHAR2 DEFAULT NULL,
130 P_PSIG_REQUESTER IN VARCHAR2,
131 P_PSIG_SOURCE IN VARCHAR2 DEFAULT NULL,
132 P_EVENT_NAME IN VARCHAR2 DEFAULT NULL,
133 P_EVENT_KEY IN VARCHAR2 DEFAULT NULL,
134 p_WF_NID IN NUMBER DEFAULT NULL,
135 P_ERROR OUT NOCOPY NUMBER,
136 P_ERROR_MSG OUT NOCOPY VARCHAR2
137 );
138
139 /* Change document Statues */
140
141
142 PROCEDURE changeDocumentStatus
143 (
144 P_DOCUMENT_ID IN NUMBER,
145 P_STATUS IN VARCHAR2,
146 P_ERROR OUT NOCOPY NUMBER,
147 P_ERROR_MSG OUT NOCOPY VARCHAR2
148 );
149
150
151
152
153 /* Cancel Document
154 IN:
155 P_DOCUMENT_ID
156
157
158 */
159
160 PROCEDURE cancelDocument
161 (
162 P_DOCUMENT_ID IN NUMBER,
163 P_ERROR OUT NOCOPY NUMBER,
164 P_ERROR_MSG OUT NOCOPY VARCHAR2
165 );
166
167 /* this Procedure is used to requrest a signature for a given document .
168 this procedure will allow a new signature row to be create in the signature table for the
169 given document and user. This should have a follow up with postsignature api with more details */
170
171 --Bug 3330240 : start
172 --Added two new IN parameters P_SIGNATURE_SEQUENCE, P_ADHOC_STATUS
173
174 PROCEDURE requestSignature
175 (
176 P_DOCUMENT_ID IN NUMBER,
177 P_USER_NAME IN VARCHAR2,
178 P_ORIGINAL_RECIPIENT IN VARCHAR2 DEFAULT NULL,
179 P_OVERRIDING_COMMENTS IN VARCHAR2 DEFAULT NULL,
180 P_SIGNATURE_SEQUENCE IN NUMBER DEFAULT NULL,
181 P_ADHOC_STATUS IN VARCHAR2 DEFAULT NULL,
182 P_SIGNATURE_ID OUT NOCOPY NUMBER,
183 P_ERROR OUT NOCOPY NUMBER,
184 P_ERROR_MSG OUT NOCOPY VARCHAR2
185 );
186
187
188
189 /* Post Signatures
190 IN:
191 P_DOCUMENT_ID
192 P_EVIDENCE_STORE_ID
193 P_USER_NAME
194 P_USER_RESPONSE
195
196 */
197
198
199 PROCEDURE postSignature
200 (
201 P_DOCUMENT_ID IN NUMBER,
202 P_EVIDENCE_STORE_ID IN VARCHAR2,
203 P_USER_NAME IN VARCHAR2,
204 P_USER_RESPONSE IN VARCHAR2,
205 P_ORIGINAL_RECIPIENT IN VARCHAR2 DEFAULT NULL,
206 P_OVERRIDING_COMMENTS IN VARCHAR2 DEFAULT NULL,
207 P_SIGNATURE_ID OUT NOCOPY NUMBER,
208 P_ERROR OUT NOCOPY NUMBER,
209 P_ERROR_MSG OUT NOCOPY VARCHAR2
210 );
211
212 /* Cancel Signature
213 IN:
214 P_SIGNATURE_ID
215
216
217 */
218
219 PROCEDURE cancelSignature
220 (
221 P_SIGNATURE_ID IN NUMBER,
222 P_ERROR OUT NOCOPY NUMBER,
223 P_ERROR_MSG OUT NOCOPY VARCHAR2
224 );
225
226
227 /* Post Document Parameters */
228
229 PROCEDURE postDocumentParameter
230 (
231 P_DOCUMENT_ID IN NUMBER,
232 P_PARAMETERS IN EDR_PSIG.params_table,
233 P_ERROR OUT NOCOPY NUMBER,
234 P_ERROR_MSG OUT NOCOPY VARCHAR2
235 );
236
237 /* Delete Document Parameters */
238
239 PROCEDURE deleteDocumentParameter
240 (
241 P_DOCUMENT_ID IN NUMBER,
242 P_PARAMETER_NAME IN VARCHAR,
243 P_ERROR OUT NOCOPY NUMBER,
244 P_ERROR_MSG OUT NOCOPY VARCHAR2
245 );
246
247
248 /* Delete All Document Parameters */
249
250 PROCEDURE deleteAllDocumentParams
251 (
252 P_DOCUMENT_ID IN NUMBER,
253 P_ERROR OUT NOCOPY NUMBER,
254 P_ERROR_MSG OUT NOCOPY VARCHAR2
255 );
256
257 /* Post Signature Parameters */
258
259
260 PROCEDURE postSignatureParameter
261 (
262 P_SIGNATURE_ID IN NUMBER,
263 P_PARAMETERS IN EDR_PSIG.params_table,
264 P_ERROR OUT NOCOPY NUMBER,
265 P_ERROR_MSG OUT NOCOPY VARCHAR2
266 );
267
268 /* Delete Signature Parameters */
269
270 PROCEDURE deleteSignatureParameter
271 (
272 P_SIGNATURE_ID IN NUMBER,
273 P_PARAMETER_NAME IN VARCHAR,
274 P_ERROR OUT NOCOPY NUMBER,
275 P_ERROR_MSG OUT NOCOPY VARCHAR2
276 );
277
278
279 /* Delete All Signature Parameters */
280
281 PROCEDURE deleteAllSignatureParams
282 (
283 P_SIGNATURE_ID IN NUMBER,
284 P_ERROR OUT NOCOPY NUMBER,
285 P_ERROR_MSG OUT NOCOPY VARCHAR2
286 );
287
288 /* Get Document Details */
289
290 PROCEDURE getDocumentDetails
291 (
292 P_DOCUMENT_ID IN NUMBER,
293 P_DOCUMENT OUT NOCOPY EDR_PSIG.DOCUMENT,
294 P_DOCPARAMS OUT NOCOPY EDR_PSIG.params_table,
295 P_SIGNATURES OUT NOCOPY EDR_PSIG.SignatureTable,
296 P_ERROR OUT NOCOPY NUMBER,
297 P_ERROR_MSG OUT NOCOPY VARCHAR2
298 );
299
300 /* Get Document Details */
301
302 PROCEDURE getSignatureDetails
303 (
304 P_SIGNATURE_ID IN NUMBER DEFAULT NULL,
305 P_SIGNATUREDETAILS OUT NOCOPY EDR_PSIG.Signature,
306 P_SIGNATUREPARAMS OUT NOCOPY EDR_PSIG.params_table,
307 P_ERROR OUT NOCOPY NUMBER,
308 P_ERROR_MSG OUT NOCOPY VARCHAR2
309 );
310
311
312 PROCEDURE updatePrintCount (
313 P_DOC_ID IN edr_psig_documents.document_id%TYPE,
314 P_NEW_COUNT OUT NOCOPY NUMBER
315 );
316
317 --Bug 3330240 : start
318 --This procedure would get the signatureid for the
319 --for the documentid, originalrecipient, username, signaturestatus
320
321 -- Start of comments
322 -- API name : getSignatureId
323 -- Type : Private Utility.
324 -- Function : Gets Signatureid for the documentid,
325 -- originalrecipeint, username, signaturestatus
326 -- Pre-reqs : None.
327 -- Parameters :
328 -- IN : P_DOCUMENT_ID in number
329 -- P_ORIGINAL_RECIPIENT in varchar2
330 -- P_USER_NAME in varchar2
331 -- P_SIGNATURE_STATUS in varchar2
332 -- OUT : X_SIGNATURE_ID out NOCOPY number
333 -- X_ERROR out NOCOPY number
334 -- X_ERROR_MSG out NOCOPY varchar2
335 --
336 -- End of comments
337
338 procedure getSignatureId (P_DOCUMENT_ID in number,
339 P_ORIGINAL_RECIPIENT in varchar2,
340 P_USER_NAME in varchar2,
341 P_SIGNATURE_STATUS in varchar2,
342 X_SIGNATURE_ID out NOCOPY number,
343 X_ERROR out NOCOPY number,
344 X_ERROR_MSG out NOCOPY varchar2);
345
346 --This procedure would get the adhoc status for the signatureid
347
348 -- Start of comments
349 -- API name : GET_ADHOC_STATUS
350 -- Type : Private Utility.
351 -- Function : Gets Adhoc status for Signatureid ,
352 -- Pre-reqs : None.
353 -- Parameters :
354 -- IN : P_SIGNATURE_ID in number
355 -- OUT : X_STATUS out NOCOPY varchar2
356 -- X_ERROR out NOCOPY number
357 -- X_ERROR_MSG out NOCOPY varchar2
358 --
359 -- End of comments
360
361 procedure GET_ADHOC_STATUS ( P_SIGNATURE_ID IN NUMBER,
362 X_STATUS OUT NOCOPY VARCHAR2,
363 X_ERROR OUT NOCOPY NUMBER,
364 X_ERROR_MSG OUT NOCOPY VARCHAR2);
365
366 --This procedure would delete the signer row if the signer is adhoc for the signatureid
367
368 -- Start of comments
369 -- API name : DELETE_ADHOC_USER
370 -- Type : Private Utility.
371 -- Function : Delete the signer row is thts adhos user
372 -- Pre-reqs : None.
373 -- Parameters :
374 -- IN : P_SIGNATURE_ID in number
375 -- OUT : X_ERROR out NOCOPY number
376 -- X_ERROR_MSG out NOCOPY varchar2
377 --
378 -- End of comments
379
380 procedure DELETE_ADHOC_USER ( P_SIGNATURE_ID IN NUMBER,
381 X_ERROR OUT NOCOPY NUMBER,
382 X_ERROR_MSG OUT NOCOPY VARCHAR2);
383
384
385 --This procedure would update the signature sequence for the signatureid
386
387 -- Start of comments
388 -- API name : UPDATE_SIGNATURE_SEQUENCE
389 -- Type : Private Utility.
390 -- Function : Update the signatire sequence for the signature id
391 -- Pre-reqs : None.
392 -- Parameters :
393 -- IN : P_SIGNATURE_ID in number
394 -- P_SIGNATURE_SEQUENCE in number
395 -- OUT : X_ERROR out NOCOPY number
396 -- X_ERROR_MSG out NOCOPY varchar2
397 --
398 -- End of comments
399 procedure UPDATE_SIGNATURE_SEQUENCE ( P_SIGNATURE_ID in number,
400 P_SIGNATURE_SEQUENCE in number,
401 X_ERROR OUT NOCOPY number,
402 X_ERROR_MSG OUT NOCOPY varchar2);
403
404
405 --This procedure would update the adhoc status for the signatureid
406
407 -- Start of comments
408 -- API name : UPDATE_ADHOC_STATUS
409 -- Type : Private Utility.
410 -- Function : Update the adhoc status for the signature id
411 -- Pre-reqs : None.
412 -- Parameters :
413 -- IN : P_SIGNATURE_ID in number
414 -- P_ADHOC_STATUS in varchar2
418 -- End of comments
415 -- OUT : X_ERROR out NOCOPY number
416 -- X_ERROR_MSG out NOCOPY varchar2
417 --
419
420
421 procedure UPDATE_ADHOC_STATUS ( P_SIGNATURE_ID in number,
422 P_ADHOC_STATUS in varchar2,
423 X_ERROR OUT NOCOPY number,
424 X_ERROR_MSG OUT NOCOPY varchar2);
425
426 --Bug 3330240 : end
427
428 -- Bug 3170251 - Start
429 -- Added a getter to get PSIG_XML (eRecord XML ) in CLOB.
430 --This procedure gets the PSIG_XML from EDR_PSIG_DOCUMENTS table
431 --for the given p_document_id i.e. eRecordId
432
433 -- Start of comments
434 -- API name : getERecordXML
435 -- Type : Public Utility
436 -- Function : Get the XML Document Contents for given eRecordId
437 -- Pre-reqs : None.
438 -- Parameters :
439 -- IN : P_DOCUMENT_ID in number
440 -- OUT : X_PSIG_XML OUT NOCOPY CLOB
441 -- : X_ERROR_CODE out NOCOPY number
442 -- : X_ERROR_MSG out NOCOPY varchar2
443 -- End of comments
444
445 procedure getERecordXML( P_DOCUMENT_ID number,
446 X_PSIG_XML OUT NOCOPY CLOB,
447 X_ERROR_CODE OUT NOCOPY NUMBER,
448 X_ERROR_MSG OUT NOCOPY VARCHAR2 );
449
450
451 -- Bug 3170251 - End
452
453
454 --Bug 3101047: Start
455
456 -- Start of comments
457 -- API name : UPDATE_PSIG_USER_DETAILS
458 -- Type : Private Utility
459 -- Function : Update the user details of those users in EDR_PSIG_DETAILS table
460 -- : for the specified document_id and whose signature status is pending.
461 -- Pre-reqs : None.
462 -- Parameters :
463 -- IN : P_DOCUMENT_ID IN NUMBER
464 -- OUT : NONE
465 -- End of comments
466
467 PROCEDURE UPDATE_PSIG_USER_DETAILS (P_DOCUMENT_ID IN NUMBER);
468
469 --Bug 3101047: End
470
471
472 --Bug 3212117: Start
473 -- Start of comments
474 -- API name : GET_EVENT_XML
475 -- Type : Private Utility
476 -- Function : Obtains the event xml for the specified event name,event key and e-record ID combination.
477 -- : This API can also be used in conjunction with an event name/event key combination or just the e-record ID value.
478 -- : In this scenario, the details of all the e-records identified by this combination would be
479 -- : fetched in XML format.
480 -- Pre-reqs : None.
481 -- Parameters :
482 -- IN : P_EVENT_NAME IN VARCHAR2 - The event name
483 -- : P_EVENT_KEY IN VARCHAR2 - The event key
484 -- : P_ERECORD_ID IN NUMBER - The e-record ID
485 -- : P_GET_ERECORD_XML IN VARCHAR2 - Flag indicating if the psig xml data is to be fetched.
486 -- : P_GET_PSIG_DETAILS IN VARCHAR2 - Flag indicating if the signature details are to be fetched.
487 -- : P_GET_ACKN_DETAILS IN VARCHAR2 - Flag indicating if the acknowledgement details are to be fetched.
488 -- : P_GET_PRINT_DETAILS IN VARCHAR2 - Flag indicating if the print history details are to be fetched.
489 -- : P_GET_RELATED_EREC_DETAILS IN VARCHAR2 - Flag indicating if the related e-record details are to be fetched.
490 --
491 -- OUT : X_FINAL_XML OUT CLOB - The event data in XML format.
492 -- End of comments
493 PROCEDURE GET_EVENT_XML(P_EVENT_NAME IN VARCHAR2,
494 P_EVENT_KEY IN VARCHAR2,
495 P_ERECORD_ID IN NUMBER,
496 P_GET_ERECORD_XML IN VARCHAR2 DEFAULT FND_API.G_FALSE,
497 P_GET_PSIG_DETAILS IN VARCHAR2 DEFAULT FND_API.G_FALSE,
498 P_GET_ACKN_DETAILS IN VARCHAR2 DEFAULT FND_API.G_FALSE,
499 P_GET_PRINT_DETAILS IN VARCHAR2 DEFAULT FND_API.G_FALSE,
500 P_GET_RELATED_EREC_DETAILS IN VARCHAR2 DEFAULT FND_API.G_FALSE,
501 X_FINAL_XML OUT NOCOPY CLOB);
502 --Bug 3212117: End
503
504 -- Bug 4558923 :start
505
506
507 /* Get_DOCUMENT_STATUS. This procedure will return the current document status for a given document_id
508 if document is not availalbe the procedure will raise a not data found exception
509 */
510
511 PROCEDURE GET_DOCUMENT_STATUS(P_DOCUMENT_ID IN NUMBER,
512 X_STATUS OUT NOCOPY VARCHAR2,
513 X_ERROR OUT NOCOPY NUMBER,
514 X_ERROR_MSG OUT NOCOPY VARCHAR2);
515
516 -- Bug 4558923 :end
517
518 --Bug 4577122 : start
519 --This procedure take an erecord id and make the signature status null
520 --for all the pending signers. the signature row itself would not be removed
521 --only status would be made null
522
523 -- Start of comments
524 -- API name : clear_pending_signatures
525 -- Type : Private Utility.
526 -- Function : nullifies the signature status of pending signers
527 -- Pre-reqs : None.
528 -- Parameters :
529 -- IN : P_DOCUMENT_ID in number
530 -- OUT : none
531 --
532 -- End of comments
533 procedure clear_pending_signatures
534 (p_document_id in number);
535
536 --Bug 4577122: End
537
538 END EDR_PSIG;
[Home] [Help]