1 PACKAGE EDR_ERES_EVENT_PUB AS
2 /* $Header: EDRPEVTS.pls 120.1.12000000.1 2007/01/18 05:54:33 appldev ship $*/
3 /*#
4 * These APIs raise an e-signature event or related events in deferred mode.
5 * @rep:scope public
6 * @rep:metalink 268669.1 Oracle E-Records API User's Guide
7 * @rep:product EDR
8 * @rep:displayname E-records Evidence Store APIs
9 * @rep:lifecycle active
10 * @rep:category BUSINESS_ENTITY EDR_EVIDENCE_STORE
11 */
12
13 /* Global Constants */
14 G_PKG_NAME CONSTANT varchar2(30) := 'EDR_ERES_EVENT_PUB';
15
16 /* Global Types */
17
18 -- Table of erecord ids
19 TYPE ERECORD_ID_TBL_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
20
21 -- Record type to represent an ERES event
22
23 --Bug 3136403: Start
24 --Change the definition of the record type to denormalize the payload parameters
25 /*
26 TYPE ERES_EVENT_REC_TYPE IS RECORD
27 ( EVENT_NAME VARCHAR2(80) ,
28 EVENT_KEY VARCHAR2(240) ,
29 PAYLOAD FND_WF_EVENT.PARAM_TABLE ,
30 ERECORD_ID NUMBER ,
31 EVENT_STATUS VARCHAR2(20)
32 );
33 */
34 TYPE ERES_EVENT_REC_TYPE IS RECORD
35 ( EVENT_NAME VARCHAR2(80) ,
36 EVENT_KEY VARCHAR2(240) ,
37 ERECORD_ID NUMBER ,
38 EVENT_STATUS VARCHAR2(20) DEFAULT NULL ,
39 --Bug 3893101: Start
40 --Include the EVENT XML as part of the record type
41 EVENT_XML CLOB DEFAULT NULL ,
42 --Bug 3893101: End
43 PARAM_NAME_1 VARCHAR2(30) DEFAULT NULL ,
44 PARAM_VALUE_1 VARCHAR2(2000) DEFAULT NULL ,
45 PARAM_NAME_2 VARCHAR2(30) DEFAULT NULL ,
46 PARAM_VALUE_2 VARCHAR2(2000) DEFAULT NULL ,
47 PARAM_NAME_3 VARCHAR2(30) DEFAULT NULL ,
48 PARAM_VALUE_3 VARCHAR2(2000) DEFAULT NULL ,
49 PARAM_NAME_4 VARCHAR2(30) DEFAULT NULL ,
50 PARAM_VALUE_4 VARCHAR2(2000) DEFAULT NULL ,
51 PARAM_NAME_5 VARCHAR2(30) DEFAULT NULL ,
52 PARAM_VALUE_5 VARCHAR2(2000) DEFAULT NULL ,
53 PARAM_NAME_6 VARCHAR2(30) DEFAULT NULL ,
54 PARAM_VALUE_6 VARCHAR2(2000) DEFAULT NULL ,
55 PARAM_NAME_7 VARCHAR2(30) DEFAULT NULL ,
56 PARAM_VALUE_7 VARCHAR2(2000) DEFAULT NULL ,
57 PARAM_NAME_8 VARCHAR2(30) DEFAULT NULL ,
58 PARAM_VALUE_8 VARCHAR2(2000) DEFAULT NULL ,
59 PARAM_NAME_9 VARCHAR2(30) DEFAULT NULL ,
60 PARAM_VALUE_9 VARCHAR2(2000) DEFAULT NULL ,
61 PARAM_NAME_10 VARCHAR2(30) DEFAULT NULL ,
62 PARAM_VALUE_10 VARCHAR2(2000) DEFAULT NULL ,
63 PARAM_NAME_11 VARCHAR2(30) DEFAULT NULL ,
64 PARAM_VALUE_11 VARCHAR2(2000) DEFAULT NULL ,
65 PARAM_NAME_12 VARCHAR2(30) DEFAULT NULL ,
66 PARAM_VALUE_12 VARCHAR2(2000) DEFAULT NULL ,
67 PARAM_NAME_13 VARCHAR2(30) DEFAULT NULL ,
68 PARAM_VALUE_13 VARCHAR2(2000) DEFAULT NULL ,
69 PARAM_NAME_14 VARCHAR2(30) DEFAULT NULL ,
70 PARAM_VALUE_14 VARCHAR2(2000) DEFAULT NULL ,
71 PARAM_NAME_15 VARCHAR2(30) DEFAULT NULL ,
72 PARAM_VALUE_15 VARCHAR2(2000) DEFAULT NULL ,
73 PARAM_NAME_16 VARCHAR2(30) DEFAULT NULL ,
74 PARAM_VALUE_16 VARCHAR2(2000) DEFAULT NULL ,
75 PARAM_NAME_17 VARCHAR2(30) DEFAULT NULL ,
76 PARAM_VALUE_17 VARCHAR2(2000) DEFAULT NULL ,
77 PARAM_NAME_18 VARCHAR2(30) DEFAULT NULL ,
78 PARAM_VALUE_18 VARCHAR2(2000) DEFAULT NULL ,
79 PARAM_NAME_19 VARCHAR2(30) DEFAULT NULL ,
80 PARAM_VALUE_19 VARCHAR2(2000) DEFAULT NULL ,
81 PARAM_NAME_20 VARCHAR2(30) DEFAULT NULL ,
82 PARAM_VALUE_20 VARCHAR2(2000) DEFAULT NULL
83 );
84 --Bug 3136403: End
85
86 -- A table of ERES Events
87
88 TYPE ERES_EVENT_TBL_TYPE IS TABLE OF ERES_EVENT_REC_TYPE INDEX BY BINARY_INTEGER;
89
90 -- Start of comments
91 -- API name : VALIDATE_ERECORD
92 -- Type : Public.
93 -- Function : Determine if the erecord with the given id exists in
94 -- the evidence store or not
95 -- Pre-reqs : None.
96 -- Parameters :
97 -- IN :p_api_version IN NUMBER Required
98 -- p_init_msg_list IN VARCHAR2 Optional
99 -- Default = FND_API.G_FALSE
100 -- p_erecord_id IN NUMBER Required
101 -- The erecord id to be validated
102 --
103 -- OUT :x_return_status OUT VARCHAR2(1)
104 -- x_msg_count OUT NUMBER
105 -- x_msg_data OUT VARCHAR2(2000)
106 --
107 -- Version :Current version 1.0
108 -- Initial version 1.0
109 --
110 -- Notes :Checks the validity of the erecord id i.e. if it exists
111 -- in the evidence store or not. A message is written in the
112 -- message stack only in the case of an UNEXPECTED error.
113 -- If the erecord id is valid or not no message is written in
114 -- the stack only the return status would indicate the result.
115 --
116 -- End of comments
117
118 PROCEDURE VALIDATE_ERECORD
119 ( p_api_version IN NUMBER ,
120 p_init_msg_list IN VARCHAR2 DEFAULT NULL ,
121 x_return_status OUT NOCOPY VARCHAR2 ,
122 x_msg_count OUT NOCOPY NUMBER ,
123 x_msg_data OUT NOCOPY VARCHAR2 ,
124 p_erecord_id IN NUMBER
125 );
126
127 -- Start of comments
128 -- API name : VALIDATE_PAYLOAD
129 -- Type : Public
130 -- Function : Determine if the payload of parameters to be passed
131 -- to raise an ERES event is valid or not
132 -- Pre-reqs : None.
133 -- Parameters :
134 -- IN :p_api_version IN NUMBER Required
135 -- p_init_msg_list IN VARCHAR2 Optional
136 -- Default = FND_API.G_FALSE
137 -- p_event_name IN VARCHAR2(80) Optional
138 -- The event name for which the payload has to be validated
139 -- p_event_key IN VARCHAR2(240) Optional
140 -- The event key for which the payload has to be validated
141 -- p_payload IN fnd_wf_event.param_table Required
142 -- The name-value pair of parameters to be validated
143 -- p_mode IN VARCHAR2(20) Optional
144 -- The mode of validation. This can have two possible
145 -- values 'STRICT' or null. This parameter is used
146 -- only if the payload contains the parameters for
147 -- interevent processing. In that case STRICT is used
148 -- in the case where PARENT_ERECORD_ID NEEDS to be
149 -- passed
150 --
151 -- OUT :x_return_status OUT VARCHAR2
152 -- The return status S means the validation is OK
153 -- E means validation failed
154 -- x_msg_count OUT NUMBER
155 -- x_msg_data OUT VARCHAR2
156 --
157 -- Version :Current version 1.0
158 -- Initial version 1.0
159 --
160 -- Notes :If you use this API to test the payload, you should
161 -- not require validation while raising the event
162 --
163 -- End of comments
164
165 PROCEDURE VALIDATE_PAYLOAD
166 ( p_api_version IN NUMBER ,
167 p_init_msg_list IN VARCHAR2 default NULL ,
168 x_return_status OUT NOCOPY VARCHAR2 ,
169 x_msg_count OUT NOCOPY NUMBER ,
170 x_msg_data OUT NOCOPY VARCHAR2 ,
171 p_event_name IN VARCHAR2 default NULL ,
172 p_event_key IN VARCHAR2 default NULL ,
173 p_payload IN fnd_wf_event.param_table ,
174 p_mode IN VARCHAR2 default NULL
175 );
176
177 -- Start of comments
178 -- API name : VALIDATE_PAYLOAD_FORMS
179 -- Type : Public
180 -- Function : Determine if the payload of parameters to be passed
181 -- to raise an ERES event is valid or not. This API is used
182 -- from FORMS.
183 -- Pre-reqs : None.
184 -- Parameters :
185 -- IN p_event_name IN VARCHAR2(80) Optional
186 -- The event name for which the payload has to be validated
187 -- p_event_key IN VARCHAR2(240) Optional
188 -- The event key for which the payload has to be validated
189 -- p_payload IN fnd_wf_event.param_table Required
190 -- The name-value pair of parameters to be validated
191 --
192 -- RETURN :Boolean
193 -- If the payload is valid it would return TRUE else it would
194 -- raise an APPS_EXCEPTION, that should be handled in the
195 -- calling code
196 -- Version :Current version 1.0
197 -- Initial version 1.0
198 --
199 -- Notes :If you use this API to test the payload, you should
200 -- not require validation while raising the event
201 --
202 -- End of comments
203
204 FUNCTION VALIDATE_PAYLOAD_FORMS
205 ( p_event_name IN VARCHAR2 ,
206 p_event_key IN VARCHAR2 ,
207 p_payload IN fnd_wf_event.param_table
208 )
209 RETURN BOOLEAN;
210
211 -- Start of comments
212 -- API name : GET_EVENT_DETAILS
213 -- Type : Public.
214 -- Function : Get the event name and event key from the evidence
215 -- store for a given erecord id
216 -- Pre-reqs : None.
217 -- Parameters :
218 -- IN :p_api_version IN NUMBER Required
219 -- p_init_msg_list IN VARCHAR2 Optional
220 -- Default = FND_API.G_FALSE
221 -- p_erecord_id IN NUMBER Required
222 -- The erecord id for which the event details are
223 -- to be obtained
224 --
225 -- OUT :x_return_status OUT VARCHAR2
226 -- x_msg_count OUT NUMBER
227 -- x_msg_data OUT VARCHAR2
228 -- x_event_name OUT VARCHAR2(80)
229 -- Event name if erecord id valid, else null
230 -- x_event_key OUT VARCHAR2(240)
231 -- Event keyif erecord id valid, else null
232 --
233 -- Version :Current version 1.0
234 -- Initial version 1.0
235 --
236 -- Notes :Gets the event name and event key for a given
237 -- erecord id from the evidence store. A message is written
238 -- in the message stack only in the case of an UNEXPECTED error.
239 -- If the erecord id is valid or not no message is written in
240 -- the stack only the return status would indicate the result
241 -- along with null return values.
242 --
243 -- End of comments
244 /*#
245 * This API is used to obtain the event name and event key from the evidence store for
246 * an e-record ID.
247 * @rep:scope public
248 * @rep:lifecycle active
249 * @rep:displayname Get event details
250 */
251
252 PROCEDURE GET_EVENT_DETAILS
253 ( p_api_version IN NUMBER ,
254 p_init_msg_list IN VARCHAR2 default NULL ,
255 x_return_status OUT NOCOPY VARCHAR2 ,
256 x_msg_count OUT NOCOPY NUMBER ,
257 x_msg_data OUT NOCOPY VARCHAR2 ,
258 p_erecord_id IN NUMBER ,
259 x_event_name OUT NOCOPY VARCHAR2 ,
260 x_event_key OUT NOCOPY VARCHAR2
261 );
262
263 -- Start of comments
264 -- API name : RAISE_ERES_EVENT
265 -- Type : Public
266 -- Function : Raise an ERES event and get the status and erecord id back.
267 -- This API can be used to raise 'normal' as well as 'inter
268 -- event' ERES events.
269 -- In case of normal event the p_child_erecords should be left
270 -- null and payload should conform to guidelines set in the
271 -- ERES cookbook.
272 -- In case of 'inter event' context if the parent already exists
273 -- then payload should contain additional parameters to set
274 -- parent child relationship (look at cookbook)
275 -- In case of 'inter event' context if the children already
276 -- exists the p_child_erecords should contain the erecord
277 -- ids of these children
278 -- Pre-reqs : None.
279 -- Parameters :
280 -- IN :p_api_version IN NUMBER Required
281 -- p_init_msg_list IN VARCHAR2 Optional
282 -- Default = FND_API.G_FALSE
283 -- p_validation_level IN NUMBER Optional
284 -- Default = FND_API.G_VALID_LEVEL_FULL
285 -- p_child_erecords IN ERECORD_ID_TBL_TYPE Required
286 -- In case of inter-event when the children exists and
287 -- parent is being raised this parameter contains the
288 -- list of erecord ids of the children to establish
289 -- parent child relationship
290 --
291 -- OUT :x_return_status OUT VARCHAR2(1)
292 -- x_msg_count OUT NUMBER
293 -- x_msg_data OUT VARCHAR2(2000)
294 -- x_event IN OUT ERES_EVENT_REC_TYPE
295 -- Eres event structure with event name, event key
296 -- and payload supplied by the caller. At end the
297 -- erecord id and status would be populated
301 -- Initial version 1.0
298 -- Status can be: ERROR, PENDING, NOACTION
299 --
300 -- Version :Current version 1.0
302 --
303 -- Notes :Can be used to raise normal and inter-event ERES events.
304 -- Please look at the ERES Cookbook for more details of
305 -- the valid payload required to raise ERES Events from
306 -- database.
307 -- It is the responsibility of the calling code to commit
308 -- or rollback according to the status of the API and/or
309 -- the ERES event. The eRecords are created autonomously
310 -- in the evidence store but thier relationships have to be
311 -- committed and can also be rolledback
312 --
313 -- Additional Imp Note: This API doesnt accept a p_commit status becuase a savepoint
314 -- cannot be set inside due to the fact that the erecords are
315 -- created in an autonomous manner. So if the API returns a status
316 -- error or the overall status is error it is the responsibility
317 -- of the product team to rollback the changes, it would rollback
318 -- and 'partial' data inserted in the relationship table
319 -- As a part of the transaction acknowledgement procedure
320 -- the product teams can then send an ERROR acknowledgement
321 -- to mark these erecords also as ERROR
322 -- End of comments
323 /*#
324 * This API raises normal as well as related events. For normal events, the
325 * p_child_erecords must be left null and the payload must conform to guidelines set in
326 * the Oracle E-Records Developer's Guide. For a related event, if the parent already
327 * exists, then payload must contain additional parameters to set the parent-child
328 * relationship. Also, if the children already exist, then the p_child_erecords must
329 * contain the e-record IDs for the children.
330 * @rep:scope public
331 * @rep:lifecycle active
332 * @rep:displayname Raise e-signature events
333 */
334
335 PROCEDURE RAISE_ERES_EVENT
336 ( p_api_version IN NUMBER ,
337 p_init_msg_list IN VARCHAR2 default NULL ,
338 p_validation_level IN NUMBER default NULL ,
339 x_return_status OUT NOCOPY VARCHAR2 ,
340 x_msg_count OUT NOCOPY NUMBER ,
341 x_msg_data OUT NOCOPY VARCHAR2 ,
342 p_child_erecords IN ERECORD_ID_TBL_TYPE ,
343 x_event IN OUT NOCOPY ERES_EVENT_REC_TYPE
344 );
345
346 -- Start of comments
347 -- API name : RAISE_INTER_EVENT
348 -- Type : Public
349 -- Function : Used to raise a number of ERES events at one time
350 -- in the inter event context. It is expected that while
351 -- using this API atleast one parent event would be raised
352 -- along with one or more of its child events.
353 -- A parent event is a 'normal' ERES event. And a child
354 -- event is the one containing additional parameters in the
355 -- payload. Please look at the ERES cookbook for more
356 -- information on the payload structure.
357 -- Pre-reqs : None.
358 -- Parameters :
359 -- IN : p_api_version IN NUMBER Required
360 -- p_init_msg_list IN VARCHAR2 Optional
361 -- Default = FND_API.G_FALSE
362 -- p_validation_level IN NUMBER Optional
363 -- Default = FND_API.G_VALID_LEVEL_FULL
364 --
365 -- OUT : x_return_status OUT VARCHAR2
366 -- x_msg_count OUT NUMBER
367 -- x_msg_data OUT VARCHAR2
368 -- x_events IN OUT ERES_EVENT_TBL_TYPE
369 -- Table of Eres event structure with event name,
370 -- event key and payload supplied by the caller. At end
371 -- the erecord id and status would be populated
372 -- Status can be: ERROR, PENDING, NOACTION
373 -- x_overall_status OUT VARCHAR2(20)
374 -- Overall status of the ERES processing. This can be used
375 -- by the calling code in conjunction with the stndard
376 -- x_return status parameter or by itself. The possible values
377 -- are:
378 -- ERROR: The process errored out (look at messages in stack)
379 -- COMPLETE: All processing finished successfully and
380 -- the erecords were created in the case of
381 -- signatures not being required
382 -- PENDING: All the erecords required signatures and offline
383 -- notifications have been sent out for them
384 -- INDETERMINED: Some events required signatures for which
385 -- notifications have been sent out and some
389 -- to figure out individual statues.
386 -- of them required only erecords and they have
387 -- been completed. The calling code would have
388 -- look at individual event status' in the table
390 --
391 -- Version : Current version 1.0
392 -- Initial version 1.0
393 --
394 -- Notes : Whenever an error occurs in one event at the time of
395 -- validation the process is aborted. Whenever an error
396 -- occurs at the time of raising an event, the process is
397 -- aborted with the events in the list ahead of the 'error'
398 -- event would have an erecord id generated for them.
399 -- Whenever an error occurs during posting of relationship
400 -- information in the database the process would abort
401 -- and it would be upto the calling code to rollback
402 -- uncommited relationship records, the erecords would have
403 -- been commited autonomously by that time.
404 -- The overall status along with the API return status can
405 -- be used to fugure out further processing by the calling
406 -- code.
407 --
408 -- Additional Imp Note: This API doesnt accept a p_commit status becuase a savepoint
409 -- cannot be set inside due to the fact that the erecords are
410 -- created in an autonomous manner. So if the API returns a status
411 -- error or the overall status is error it is the responsibility
412 -- of the product team to rollback the changes, it would rollback
413 -- and 'partial' data inserted in the relationship table
414 -- As a part of the transaction acknowledgement procedure
415 -- the product teams can then send an ERROR acknowledgement
416 -- to mark these erecords also as ERROR
417 -- End of comments
418
419 /*#
420 * This API raises a number of e-signature related events at one time.
421 * It is expected that while using this API at least one parent event
422 * is raised along with one or more of the child events.
423 * payload must conform to guidelines set in the Oracle E-Records Developer's Guide
424 * @rep:scope public
425 * @rep:lifecycle active
426 * @rep:displayname Raise related e-signature events
427 */
428
429 PROCEDURE RAISE_INTER_EVENT
430 ( p_api_version IN NUMBER ,
431 p_init_msg_list IN VARCHAR2 default NULL ,
432 p_validation_level IN NUMBER default NULL ,
433 x_return_status OUT NOCOPY VARCHAR2 ,
434 x_msg_count OUT NOCOPY NUMBER ,
435 x_msg_data OUT NOCOPY VARCHAR2 ,
436 x_events IN OUT NOCOPY ERES_EVENT_TBL_TYPE ,
437 x_overall_status OUT NOCOPY VARCHAR2
438 );
439
440 -- Start of comments
441 -- API name : GET_ERECORD_ID
442 -- Type : Public.
443 -- Function : Get the erecord id for a combination of the event
444 -- name and event key from a table of ERES events.
445 -- This would return the 'latest' erecord id for
446 -- and event name and key combination.
447 -- Pre-reqs : None.
448 -- Parameters :
449 -- IN :p_api_version IN NUMBER Required
450 -- p_init_msg_list IN VARCHAR2 Optional
451 -- Default = FND_API.G_FALSE
452 -- p_events IN ERES_EVENT_TBL_TYPE Required
453 -- Table of ERES events
454 -- p_event_name IN VARCHAR2(80) Required
455 -- ERES Business Event Name
456 -- p_event_key IN VARCHAR2(240) Required
457 -- Event Key for the ERES event
458 -- OUT :x_return_status OUT VARCHAR2
459 -- 'S' in case of a hit and 'E' in case of a miss
460 -- x_msg_count OUT NUMBER
461 -- x_msg_data OUT VARCHAR2
462 -- x_erecord_id OUT NUMBER
463 -- eRecord id for the input event name and event key.
464 -- This would the latest entry in the table for
465 -- the event name and key combination.
466 -- If not found it would be null.
467 --
468 -- Version :Current version 1.0
469 -- Initial version 1.0
470 --
471 -- Notes :This API can be used by the calling code to find out
472 -- individual errecord ids instead of going through
473 -- the table
474 --
475 -- End of comments
476 /*#
477 * This API obtains the e-record ID for an event name and event key combination
478 * from a table of ERES events. The updated e-record ID for the combination is
479 * returned. Use this API in conjunction with 'Raise related e-signature events' API only.
480 * @rep:scope public
481 * @rep:lifecycle active
482 * @rep:displayname Get e-record ID
483 */
484
485 PROCEDURE GET_ERECORD_ID
486 ( p_api_version IN NUMBER ,
487 p_init_msg_list IN VARCHAR2 default NULL ,
488 x_return_status OUT NOCOPY VARCHAR2 ,
489 x_msg_count OUT NOCOPY NUMBER ,
490 x_msg_data OUT NOCOPY VARCHAR2 ,
491 p_events IN ERES_EVENT_TBL_TYPE ,
492 p_event_name IN VARCHAR2 ,
493 p_event_key IN VARCHAR2 ,
494 x_erecord_id OUT NOCOPY NUMBER
495 );
496
497 end EDR_ERES_EVENT_PUB;