1 package WF_QUEUE AUTHID CURRENT_USER as
2 /* $Header: wfques.pls 120.2.12010000.2 2009/08/16 14:14:51 skandepu ship $ */
3 /*#
4 * Provides APIs that can be called by an application program or a
5 * workflow function in the runtime phase to handle workflow Advanced
6 * Queue processing. Although these APIs will continue to be supported
7 * for backward compatibility, customers using Oracle Workflow Release
8 * 2.6 and higher should use the Business Event System rather than the
9 * queue APIs to integrate with Oracle Advanced Queuing.
10 * @rep:scope public
11 * @rep:product OWF
12 * @rep:displayname Workflow Queues
13 * @rep:lifecycle active
14 * @rep:compatibility S
15 * @rep:category BUSINESS_ENTITY WF_ENGINE
16 * @rep:ihelp FND/@que_api See the related online help
17 */
18
19 -- define the queue variables
20 -- for Apps installs this includes the schema name (because queue is in
21 -- different schema to the package).
22 -- Standalone does not need the schema defined because everything is in
23 -- same schema.
24 deferred_queue_name varchar2(60);
25 inbound_queue_name varchar2(60);
26 outbound_queue_name varchar2(60);
27 -- define the account name. This only has a value for Apps installs.
28 account_name varchar2(320);
29 name_init boolean:= FALSE;
30
31 -- ==================================================
32 -- Bug 4005674
33 -- define variables to calculate the number of occurrences from the
34 -- history table since the background engine started
35 -- ==================================================
36 g_defer_occurrence number;
37 g_add_delay_seconds number;
38 g_max_delay_seconds number;
39
40 -- ==================================================
41 -- declare types for Developer APIs for Inbound Queue
42 -- Note: we may change these in the future. For example
43 -- we may convert to XHTML so dont make types PUBLIC
44 -- ==================================================
45 type TypeArrayTyp is table of varchar2(8) index by binary_integer;
46 type StckItemkey is table of varchar2(200) index by binary_integer;
47 type StckActidTyp is table of pls_integer index by binary_integer;
48 type StckResultTyp is table of varchar2(30) index by binary_integer;
49 type StckAttrListTyp is table of varchar2(4000) index by binary_integer;
50 type StckCtrTyp is table of pls_integer index by binary_integer;
51
52
53 stck_itemtype TypeArrayTyp;
54 stck_itemkey StckItemKey;
55 stck_actid StckActidTyp;
56 stck_result StckResultTyp;
57 stck_attrlist StckAttrListTyp;
58
59 stck_ctr pls_integer := 0;
60
61 /*===========================================================================
62
63 PL*SQL TABLE NAME: wf_queue_protocol_rec_type
64
65 DESCRIPTION: Stores a list of queue types with an index into the
66 queue name list
67
68 ============================================================================*/
69
70 TYPE wf_queue_protocol_rec_type IS RECORD
71 (
72 protocol VARCHAR2(30), -- Protocol - SMTP,
73 inbound_outbound VARCHAR2(10), -- Is this an outbound or inbound queue
74 queue_count NUMBER -- How many queues are defined for the protocol
75 );
76
77 TYPE wf_queue_protocol_tbl_type IS TABLE OF
78 wf_queue.wf_queue_protocol_rec_type INDEX BY BINARY_INTEGER;
79
80 -- List of indexes for the queue
81 queue_names_index wf_queue_protocol_tbl_type;
82
83 -- ==================================================================
84 -- PUBLIC APIs
85 -- ==================================================================
86 /*#
87 * Enqueues the result from an outbound event onto the inbound queue.
88 * Oracle Workflow marks the external function activity as complete with the
89 * specified result when it processes the inbound queue.
90 * @param itemtype Item Type
91 * @param itemkey Item Key
92 * @param actid Activity ID
93 * @param result Result
94 * @param attrlist Item Attribute List
95 * @param correlation Correlation ID
96 * @param error_stack Error Stack
97 * @rep:scope public
98 * @rep:lifecycle active
99 * @rep:displayname Enqueue Inbound Message
100 * @rep:compatibility S
101 * @rep:ihelp FND/@que_api#a_eqib See the related online help
102 */
103 procedure EnqueueInbound(
104 itemtype in varchar2,
105 itemkey in varchar2,
106 actid in number,
107 result in varchar2 default null,
108 attrlist in varchar2 default null,
109 correlation in varchar2 default null,
110 error_stack in varchar2 default null);
111
112 /*#
113 * Dequeues a message from the outbound queue for some agent to consume.
114 * @param dequeuemode Dequeue Mode
115 * @param navigation Message Navigation
116 * @param correlation Correlation ID
117 * @param itemtype Item Type
118 * @param payload Payload
119 * @param message_handle Message Handle
120 * @param timeout Dequeue Timeout
121 * @rep:scope public
122 * @rep:lifecycle active
123 * @rep:displayname Dequeue Outbound Message
124 * @rep:compatibility S
125 * @rep:ihelp FND/@que_api#a_dqob See the related online help
126 */
127 procedure DequeueOutbound(
128 dequeuemode in number,
129 navigation in number default 1,
130 correlation in varchar2 default null,
131 itemtype in varchar2 default null,
132 payload out nocopy system.wf_payload_t,
133 message_handle in out nocopy raw,
134 timeout out nocopy boolean);
135
136 /*#
137 * Dequeues the full event details for a given message from the outbound
138 * queue. This API is similar to DequeueOutbound except it does not
139 * reference the payload type. Instead, it outputs the item key, activity
140 * ID, function name, and parameter list, which are part of the payload.
141 * @param dequeuemode Dequeue Mode
142 * @param navigation Message Navigation
143 * @param correlation Correlation ID
144 * @param itemtype Item Type
145 * @param itemkey Item Key
146 * @param actid Activity ID
147 * @param function_name Function Name
148 * @param param_list Parameter List
149 * @param message_handle Message Handle
150 * @param timeout Dequeue Timeout
151 * @rep:scope public
152 * @rep:lifecycle active
153 * @rep:displayname Dequeue Event Detail
154 * @rep:compatibility S
155 * @rep:ihelp FND/@que_api#a_dqed See the related online help
156 */
157 procedure DequeueEventDetail(
158 dequeuemode in number,
159 navigation in number default 1,
160 correlation in varchar2 default null,
161 itemtype in out nocopy varchar2,
162 itemkey out nocopy varchar2,
163 actid out nocopy number,
164 function_name out nocopy varchar2,
165 param_list out nocopy varchar2,
166 message_handle in out nocopy raw,
167 timeout out nocopy boolean);
168
169 /*#
170 * Removes an event from a specified queue without further processing.
171 * @param queuename Queue Name to purge
172 * @param message_handle Message Handle
173 * @param multiconsumer For Internal Use Only
174 * @rep:scope public
175 * @rep:lifecycle active
176 * @rep:displayname Purge Event from Queue
177 * @rep:compatibility S
178 * @rep:ihelp FND/@que_api#a_pe See the related online help
179 */
180 procedure PurgeEvent(queuename in varchar2,
181 message_handle in raw,
182 multiconsumer in boolean default FALSE);
183
184 /*#
185 * Removes all events belonging to a specific item type from a specified
186 * queue without further processing.
187 * @param queuename Queue Name to purge
188 * @param itemtype Item Type
189 * @param correlation Correlation ID
190 * @rep:scope public
191 * @rep:lifecycle active
192 * @rep:displayname Purge Events based on Item Type
193 * @rep:compatibility S
194 * @rep:ihelp FND/@que_api#a_pit See the related online help
195 */
196 procedure PurgeItemtype(queuename in varchar2,
197 itemtype in varchar2 default null,
198 correlation in varchar2 default null);
199
200 /*#
201 * Reads every message off the inbound queue and records each message
202 * as a completed event. The result of the completed event and the list of
203 * item attributes that are updated as a consequence of the completed
204 * event are specified by each message in the inbound queue.
205 * @param itemtype Item Type
206 * @param correlation Correlation ID
207 * @rep:scope public
208 * @rep:lifecycle active
209 * @rep:displayname Process Inbound Queue
210 * @rep:compatibility S
211 * @rep:ihelp FND/@que_api#a_pibq See the related online help
212 */
213 procedure ProcessInboundQueue (itemtype in varchar2 default null,
214 correlation in varchar2 default null);
215
216 /*#
217 * Returns a message handle ID for a specified message.
218 * @param queuename Queue Name
219 * @param itemtype Item Type
220 * @param itemkey Item Key
221 * @param actid Activity ID
222 * @param correlation Correlation ID
223 * @param multiconsumer For Internal Use Only
224 * @return Message Handle
225 * @rep:scope public
226 * @rep:lifecycle active
227 * @rep:displayname Get Message Handle
228 * @rep:compatibility S
229 * @rep:ihelp FND/@que_api#a_gmh See the related online help
230 */
231 function GetMessageHandle(queuename in varchar2,
232 itemtype in varchar2,
233 itemkey in varchar2,
234 actid in number,
235 correlation in varchar2 default null,
236 multiconsumer in boolean default FALSE) return raw;
237
238 /*#
239 * Dequeues all messages from an exception queue and places the
240 * messages on the standard Business Event System WF_ERROR queue
241 * with the error message 'Message Expired'. When the messages are
242 * dequeued from WF_ERROR, a predefined subscription is triggered that
243 * launches the Default Event Error process.
244 * @param queuename Queue Name
245 * @rep:scope public
246 * @rep:lifecycle active
247 * @rep:displayname Dequeue from Exception Queue
248 * @rep:compatibility S
249 * @rep:ihelp FND/@que_api#a_dqexc See the related online help
250 */
251 procedure DequeueException (queuename in varchar2);
252
253 procedure AddSubscriber(queuename in varchar2,
254 name in varchar2);
255 --Bug 2307428
256 --Enable inbound and deferred queues.
257 --To be called by wf_engine.Background
258 procedure EnableBackgroundQueues;
259
260 -- ==================================================================
261 -- PRIVATE APIs
262 -- ==================================================================
263
264 procedure Enqueue_Event(queuename in varchar2,
265 itemtype in varchar2,
266 itemkey in varchar2,
267 actid in number,
268 correlation in varchar2 default null,
269 delay in number default 0,
270 funcname in varchar2 default null,
271 paramlist in varchar2 default null,
272 result in varchar2 default null,
273 message_handle in out nocopy raw,
274 priority in number default null);
275
276 procedure Dequeue_Event(queuename in varchar2,
277 dequeuemode in number,
278 navigation in number default 1,
279 correlation in varchar2 default null,
280 payload out nocopy system.wf_payload_t,
281 message_handle in out nocopy raw,
282 timeout out nocopy boolean,
283 multiconsumer in boolean default FALSE);
284
285 procedure ProcessDeferredQueue (itemtype in varchar2 default null,
286 minthreshold in number default null,
287 maxthreshold in number default null,
288 correlation in varchar2 default null);
289
290
291 procedure ProcessDeferredEvent(itemtype in varchar2,
292 itemkey in varchar2,
293 actid in number,
294 message_handle in raw,
295 minthreshold in number,
296 maxthreshold in number);
297
298 function Get_param_list(itemtype in varchar2,
299 itemkey in varchar2,
300 actid in number) return varchar2 ;
301
302 -- ==================================================================
303 -- QUEUE set up
304 -- replace this with simple synonyms when AQ supports them
305 -- for now build a string as schema_name.queue_name
306 -- ==================================================================
307 procedure set_queue_names;
308
309 /*#
310 * Returns the name of the queue and schema used by the background engine
311 * for deferred processing.
312 * @return Name and Schema of Deferred Queue
313 * @rep:scope public
314 * @rep:lifecycle active
315 * @rep:displayname Get Deferred Queue Information
316 * @rep:compatibility S
317 * @rep:ihelp FND/@que_api#a_dq See the related online help
318 */
319 function DeferredQueue return varchar2; --(PUBLIC)
320
321 /*#
322 * Returns the name of the inbound queue and schema. The inbound queue
323 * contains messages for the Workflow Engine to consume.
324 * @return Name and Schema of Inbound Queue
325 * @rep:scope public
326 * @rep:lifecycle active
327 * @rep:displayname Get Inbound Queue Information
328 * @rep:compatibility S
329 * @rep:ihelp FND/@que_api#a_iq See the related online help
330 */
331 function InboundQueue return varchar2; --(PUBLIC)
332
333 /*#
334 * Returns the name of the outbound queue and schema. The outbound queue
335 * contains messages for external agents to consume.
336 * @return Name and Schema of Outbound Queue
337 * @rep:scope public
338 * @rep:lifecycle active
339 * @rep:displayname Get Outbound Queue Information
340 * @rep:compatibility S
341 * @rep:ihelp FND/@que_api#a_oq See the related online help
342 */
343 function OutboundQueue return varchar2; --(PUBLIC)
344
345 --===================================================================
346 --
347 -- Declare all developer APIs for manipulating Inbound Queue
348 --
349 --===================================================================
350 --
351 -- ClearMsgStack
352 -- clears the stack
353 --
354 /*#
355 * Clears the internal stack.
359 * @rep:compatibility S
356 * @rep:scope public
357 * @rep:lifecycle active
358 * @rep:displayname Clear Internal Stack
360 * @rep:ihelp FND/@que_api#a_cms See the related online help
361 */
362 procedure ClearMsgStack;
363
364 --
365 -- CreateMsg (public)
366 -- creates a new message on the stack if it doesnt already exist
367 --
368 /*#
369 * Creates a new message in the internal stack if it doesn't already exist.
370 * @param itemtype Item Type
371 * @param itemkey Item Key
372 * @param actid Activity ID
373 * @rep:scope public
374 * @rep:lifecycle active
375 * @rep:displayname Create New Message
376 * @rep:compatibility S
377 * @rep:ihelp FND/@que_api#a_cmsg See the related online help
378 */
379 procedure CreateMsg(
380 itemtype in varchar2,
381 itemkey in varchar2,
382 actid in number);
383
384 --
385 -- WriteMsg (public)
386 -- writes a message from the stack to the Inbound Queue
387 --
388 /*#
389 * Writes a message from the internal stack to the inbound queue.
390 * @param itemtype Item Type
391 * @param itemkey Item Key
392 * @param actid Activity ID
393 * @rep:scope public
394 * @rep:lifecycle active
395 * @rep:displayname Write Message
396 * @rep:compatibility S
397 * @rep:ihelp FND/@que_api#a_wm See the related online help
398 */
399 procedure WriteMsg(
400 itemtype in varchar2,
401 itemkey in varchar2,
402 actid in number);
403
404 --
405 -- SetMsgAttr (public)
406 -- appends a message attribute
407 --
408 /*#
409 * Appends an item attribute to the message in the internal stack.
410 * @param itemtype Item Type
411 * @param itemkey Item Key
412 * @param actid Activity ID
413 * @param attrName Attribute Name
414 * @param attrValue Attribute Value
415 * @rep:scope public
416 * @rep:lifecycle active
417 * @rep:displayname Set Message Attribute
418 * @rep:compatibility S
419 * @rep:ihelp FND/@que_api#a_sma See the related online help
420 */
421 procedure SetMsgAttr(
422 itemtype in varchar2,
423 itemkey in varchar2,
424 actid in number,
425 attrName in varchar2,
426 attrValue in varchar2);
427
428 --
429 -- SetMsgResult (public)
430 -- sets the message result
431 --
432 /*#
433 * Sets a result to the message written in the internal stack.
434 * @param itemtype Item Type
435 * @param itemkey Item Key
436 * @param actid Activity ID
437 * @param result Result
438 * @rep:scope public
439 * @rep:lifecycle active
440 * @rep:displayname Set Message Result
441 * @rep:compatibility S
442 * @rep:ihelp FND/@que_api#a_smr See the related online help
443 */
444 procedure SetMsgResult(
445 itemtype in varchar2,
446 itemkey in varchar2,
447 actid in number,
448 result in varchar2);
449
450 --
451 -- AddNewMsg (Private)
452 -- adds a msg to the stack
453 procedure AddNewMsg(
454 itemtype in varchar2,
455 itemkey in varchar2,
456 actid in number);
457
458 --
459 -- SearchMsgStack (private)
460 -- Sequential search of message stack.
461 function SearchMsgStack(
462 itemtype in varchar2,
463 itemkey in varchar2,
464 actid in number) RETURN number;
465
466 --
467 -- Generic_Queue_Display
468 -- Produce list of generic_queues
469 --
470 procedure Generic_Queue_Display;
471
472 --
473 -- Generic_Queue_View_Detail
474 -- Produce list of generic_queues
475 --
476 procedure Generic_Queue_View_Detail (
477 p_protocol IN VARCHAR2 DEFAULT NULL,
478 p_inbound_outbound IN VARCHAR2 DEFAULT NULL
479 );
480
481 --
482 -- Procedure: get_hash_queue_name
483 --
484 -- Description: Load all queue definitions into memory. The use a hashing algorithm
485 -- to return a queue name
486 --
487 procedure get_hash_queue_name
488 (p_protocol in varchar2,
489 p_inbound_outbound in varchar2,
490 p_queue_name out nocopy varchar2);
491
492
493 --
494 -- Procedure: Generic_Queue_Edit
495 --
496 -- Description: UI to Add a new queue definition or modify the properties of a queue
497 --
498 procedure Generic_Queue_Edit (
499 p_protocol IN VARCHAR2 DEFAULT NULL,
500 p_inbound_outbound IN VARCHAR2 DEFAULT NULL
501 );
502
503 --
504 -- Procedure: create_generic_queue
505 --
506 -- Description: Create the aq components and insert into the wf_queues table
507 --
508 procedure create_generic_queue
509 (p_protocol IN VARCHAR2,
510 p_inbound_outbound IN VARCHAR2,
511 p_description IN VARCHAR2,
512 p_queue_count IN NUMBER);
513
514
515 --
516 -- Procedure: delete_generic_queue
517 --
518 -- delete a generic queue with the object type of WF_MESSAGE_PAYLOAD_T
519 --
520 procedure delete_generic_queue
521 (p_protocol IN VARCHAR2,
522 p_inbound_outbound IN VARCHAR2);
523
524 --
525 -- Procedure: generic_queue_update
526 --
527 -- Execute all the dml to either create the generic queues or modify them
528 -- in some way.
529 --
530 procedure Generic_Queue_Update (
531 p_protocol IN VARCHAR2 DEFAULT NULL,
532 p_inbound_outbound IN VARCHAR2 DEFAULT NULL,
533 p_description IN VARCHAR2 DEFAULT NULL,
534 p_queue_count IN VARCHAR2 DEFAULT NULL,
538 --
535 p_original_protocol IN VARCHAR2 DEFAULT NULL,
536 p_original_inbound IN VARCHAR2 DEFAULT NULL);
537
539 -- Procedure: generic_queue_display_contents
540 --
541 -- Display the contents of a message in a generic queue
542 --
543 procedure generic_queue_display_contents
544 (p_protocol IN VARCHAR2 DEFAULT NULL,
545 p_inbound_outbound IN VARCHAR2 DEFAULT NULL,
546 p_queue_number IN NUMBER DEFAULT NULL,
547 p_message_number IN NUMBER DEFAULT 1);
548
549 --
550 -- Function: enable_exception_queue
551 --
552 -- Enable the exception queue for the queue table for dequing
553 -- Returns the name of the exception queue for the given queue name
554 --
555 function enable_Exception_Queue(p_queue_name in varchar2) return varchar2;
556
557 --
558 -- getCntMsgSt
559 --
560 -- For all agents that the business event system knows about on the
561 -- local system, returns the number of messages in different states.
562 -- In addition, we will return the number of messages on the error
563 -- agent.
564 --
565 /*#
566 * Returns the number of messages in different statuses for the specified
567 * agent, or for all agents on the local system if no agent is specified.
568 * @param p_agent The agent name.
569 * @param p_ready The number of messages with the status READY.
570 * @param p_wait The number of messages with the status WAIT.
571 * @param p_processed The number of messages with the status PROCESSED.
572 * @param p_expired The number of messages with the status EXPIRED.
573 * @param p_undeliverable The number of messages with the status UNDELIVERED.
574 * @param p_error The number of messages on the standard Business Event System error agents.
575 * @rep:scope public
576 * @rep:lifecycle active
577 * @rep:displayname Get Message Status Counts
578 * @rep:compatibility S
579 */
580 procedure getCntMsgSt
581 (p_agent IN VARCHAR2 DEFAULT '%',
582 p_ready OUT NOCOPY NUMBER,
583 p_wait OUT NOCOPY NUMBER,
584 p_processed OUT NOCOPY NUMBER,
585 p_expired OUT NOCOPY NUMBER,
586 p_undeliverable OUT NOCOPY NUMBER,
587 p_error OUT NOCOPY NUMBER);
588
589 --
590 -- move_msgs_excep2normal (CONCURRENT PROGRAM API)
591 --
592 -- API to move messages from the exception queue to the normal queue
593 -- of the given agent. Handles wf_event_t and JMS_TEXT_MESSAGE payloads.
594 --
595 /*#
596 * Moves messages from the exception queue associated with the specified
597 * agent back to the agent's normal queue. This procedure helps enable
598 * mass reprocessing in case of a large number of errors. The procedure
599 * handles both queues whose payload type is WF_EVENT_T and queues whose
600 * payload type is SYS.AQ$_JMS_TEXT_MESSAGE.
601 * @param errbuf The error buffer.
602 * @param retcode The return code.
603 * @param p_agent_name The agent name.
604 * @rep:scope public
605 * @rep:lifecycle active
606 * @rep:displayname Move Messages from Exception to Normal Queue
607 * @rep:compatibility S
608 */
609 procedure move_msgs_excep2normal
610 (errbuf OUT NOCOPY VARCHAR2,
611 retcode OUT NOCOPY VARCHAR2,
612 p_agent_name IN VARCHAR2);
613
614 --
615 -- Overloaded Procedure 1 : Definition without the AGE parameter
616 --
617 -- clean_evt
618 -- Procedure to purge the messages in the READY state of a Queue
619 -- of WF_EVENT_T payload type. Supports correlation id based purge.
620 --
621 /*#
622 * Purges event messages with the status READY from the queue associated with
623 * the specified agent. The queue must use the WF_EVENT_T datatype as its
624 * payload type. You can optionally specify a correlation ID to purge
625 * only messages marked with that ID.
626 * @param p_agent_name The agent name.
627 * @param p_correlation The correlation ID for the messages to purge.
628 * @param p_commit_frequency The number of messages to purge before committing.
629 * @param p_msg_count The number of messages that were purged.
630 * @rep:scope public
631 * @rep:lifecycle active
632 * @rep:displayname Clean Events from Queue
633 * @rep:compatibility S
634 */
635 procedure clean_evt
636 (p_agent_name IN VARCHAR2,
637 p_correlation IN VARCHAR2 DEFAULT NULL,
638 p_commit_frequency IN NUMBER DEFAULT 500,
639 p_msg_count OUT NOCOPY NUMBER);
640
641 --
642 -- Overloaded Procedure 2 : Definition with the AGE parameter
643 --
644 -- clean_evt
645 -- Procedure to purge the messages in the READY state of a Queue
646 -- of WF_EVENT_T payload type. Supports time-based selective purge
647 -- with the given correlation id.
648 --
649 /*#
650 * Purges event messages with the status READY and of the specified age
651 * from the queue associated with the specified agent. The queue must
652 * use the WF_EVENT_T datatype as its payload type. You can optionally
653 * specify a correlation ID to purge only messages marked with that ID.
654 * @param p_agent_name The agent name.
655 * @param p_correlation The correlation ID for the messages to purge.
656 * @param p_commit_frequency The number of messages to purge before committing.
657 * @param p_msg_count The number of messages that were purged.
658 * @param p_age The age of the messages to purge.
659 * @rep:scope public
660 * @rep:lifecycle active
661 * @rep:displayname Clean Events from Queue by Age
662 * @rep:compatibility S
663 */
664 procedure clean_evt
665 (p_agent_name IN VARCHAR2,
666 p_correlation IN VARCHAR2 DEFAULT NULL,
667 p_commit_frequency IN NUMBER DEFAULT 500,
671 end;
668 p_msg_count OUT NOCOPY NUMBER,
669 p_age IN NUMBER);
670
[Home] [Help]