1 PACKAGE XDP_AQ_UTILITIES AUTHID CURRENT_USER AS
2 /* $Header: XDPAQUTS.pls 120.1 2005/06/15 21:52:13 appldev $ */
3
4 -- PL/SQL Specification
5 -- Define exception
6 e_QTimeOut EXCEPTION;
7 PRAGMA EXCEPTION_INIT(e_QTimeOut, -25228);
8 e_QNavOut EXCEPTION;
9 PRAGMA EXCEPTION_INIT(e_QNavOut, -25237);
10 resource_busy exception;
11 pragma exception_init(resource_busy, -00054);
12
13 e_NothingToDequeueException exception;
14 stop_processing exception;
15
16 --Global variables
17 g_msg_wait_timeout NUMBER := 10;
18 g_sleep_time NUMBER := 0.01;
19
20 --
21 -- Start SFM AQs
22 --
23 PROCEDURE Start_WF_AQ(
24 p_return_code OUT NOCOPY NUMBER,
25 p_error_description OUT NOCOPY VARCHAR2);
26
27 --
28 -- Start an AQ
29 --
30 PROCEDURE Start_WF_AQ(
31 p_queue_name IN varchar2,
32 p_queue_table IN varchar2,
33 p_payload IN varchar2,
34 p_expq_name IN varchar2 DEFAULT NULL,
35 p_max_retries IN number default 0,
36 p_return_code OUT NOCOPY NUMBER,
37 p_error_description OUT NOCOPY VARCHAR2);
38
39 --
40 -- Stop the SFM AQ
41 --
42 PROCEDURE STOP_WF_AQ(
43 p_return_code OUT NOCOPY NUMBER,
44 p_error_description OUT NOCOPY VARCHAR2);
45 --
46 -- DROP the SFM AQ
47 --
48 PROCEDURE DROP_WF_AQ(
49 p_return_code OUT NOCOPY NUMBER,
50 p_error_description OUT NOCOPY VARCHAR2);
51
52 /*************** Commented out by SPUSEGAO as pending order Queue has been removed
53 --
54 -- Add order to pending queue
55 --
56 PROCEDURE Pending_Order_EQ(
57 p_order_id IN NUMBER,
58 p_prov_date IN DATE,
59 p_priority IN NUMBER DEFAULT 100,
60 p_return_code OUT NOCOPY NUMBER,
61 p_error_description OUT NOCOPY VARCHAR2);
62
63 ****************/
64 --
65 -- Dequeue an order from the pending order queue
66 --
67
68 PROCEDURE Pending_Order_DQ;
69
70 /*************** Commented out by SPUSEGAO as pending order Queue has been removed
71
72 PROCEDURE Pending_Order_DQ (p_message_wait_timeout IN NUMBER DEFAULT 1,
73 p_correlation_id IN VARCHAR2,
74 x_message_key OUT NOCOPY VARCHAR2,
75 x_queue_timed_out OUT NOCOPY VARCHAR2);
76
77 ****************/
78
79 /*
80 --
81 -- Remove an order from the pending order queue
82 -- Obsolete
83 PROCEDURE Remove_Pending_Order(
84 p_order_id IN NUMBER,
85 p_return_code OUT NOCOPY NUMBER,
86 p_error_description OUT NOCOPY VARCHAR2);
87 */
88
89 --
90 -- Add order to order processor queue
91 --
92 PROCEDURE Add_OrderToProcessorQ(
93 p_order_id IN NUMBER ,
94 p_order_type in varchar2 default null,
95 p_priority IN NUMBER DEFAULT 100,
96 p_prov_date IN DATE DEFAULT SYSDATE,
97 p_wf_item_type IN varchar2,
98 p_wf_item_key IN Varchar2);
99
100 /************** Commented out as this code was being executed by old C dequeuers
101 --
102 -- Dequeue from order processor queue
103 --
104 PROCEDURE Start_OrderProcessor_Workflow;
105
106 ****************/
107
108 PROCEDURE Start_OrderProcessor_Workflow (p_message_wait_timeout IN NUMBER DEFAULT 1,
109 p_correlation_id IN VARCHAR2,
110 x_message_key OUT NOCOPY VARCHAR2,
111 x_queue_timed_out OUT NOCOPY VARCHAR2);
112
113 --
114 -- Allow API to start workitem WF through enqueue
115 --
116 PROCEDURE Add_WorkItem_ToQ(
117 p_order_id IN NUMBER,
118 p_wi_instance_id IN NUMBER,
119 p_prov_date IN DATE,
120 p_wf_item_type IN VARCHAR2 ,
121 p_wf_item_key IN VARCHAR2,
122 p_priority IN number DEFAULT 100,
123 p_return_code OUT NOCOPY NUMBER,
124 p_error_description OUT NOCOPY VARCHAR2);
125
126 /************** Commented out as this code was being executed by old C dequeuers
127 --
128 -- Dequeue from workitem queue
129 --
130 Procedure Start_Workitem_Workflow;
131
132 ****************/
133
134 PROCEDURE Start_Workitem_Workflow (p_message_wait_timeout IN NUMBER DEFAULT 1,
135 p_correlation_id IN VARCHAR2,
136 x_message_key OUT NOCOPY VARCHAR2,
137 x_queue_timed_out OUT NOCOPY VARCHAR2);
138
139 --
140 -- Allow workitem workflow to register a FA through eq
141 --
142 PROCEDURE Add_FA_ToQ(
143 p_order_id IN NUMBER,
144 p_wi_instance_id IN NUMBER,
145 p_fa_instance_id IN number,
146 p_wf_item_type in VARCHAR2 ,
147 p_wf_item_key in VARCHAR2,
148 p_priority in number default 100,
149 p_return_code OUT NOCOPY NUMBER,
150 p_error_description OUT NOCOPY VARCHAR2);
151
152 /************** Commented out as this code was being executed by old C dequeuers
153 --
154 -- Used by API to start FA workflow
155 -- through dequeue
156 --
157 PROCEDURE Start_FA_Workflow;
158
159 ****************/
160
161 PROCEDURE Start_FA_Workflow (p_message_wait_timeout IN NUMBER DEFAULT 1,
162 p_correlation_id IN VARCHAR2,
163 x_message_key OUT NOCOPY VARCHAR2,
164 x_queue_timed_out OUT NOCOPY VARCHAR2);
165
166 --
167 -- Allow WF to pass the pipe to next WF through enqueue
168 --
169 PROCEDURE HANDOVER_Channel(
170 p_channel_name IN VARCHAR2,
171 p_fe_name IN VARCHAR2,
172 p_wf_item_type IN VARCHAR2,
173 p_wf_item_key IN VARCHAR2,
174 p_wf_activity IN Varchar2 Default NULL,
175 p_order_id IN number,
176 p_wi_instance_id IN number,
177 p_fa_instance_id IN number,
178 p_return_code OUT NOCOPY NUMBER,
179 p_error_description OUT NOCOPY VARCHAR2);
180
181
182 /************** Commented out as this code was being executed by old C dequeuers
183 --
184 -- Used by DB job to resume a WF with the new pipe
185 -- through dequeue
186 --
187
188 PROCEDURE Resume_Next_WF;
189
190 ****************/
191
192 PROCEDURE Resume_Next_WF (p_message_wait_timeout IN NUMBER DEFAULT 1,
193 p_correlation_id IN VARCHAR2,
194 x_message_key OUT NOCOPY VARCHAR2,
195 x_queue_timed_out OUT NOCOPY VARCHAR2);
196
197 PROCEDURE SDP_RESUME_WF
198 (p_pipe_name IN VARCHAR2
199 ,p_wf_item_type IN VARCHAR2
200 ,p_wf_item_key IN VARCHAR2
201 ,p_wf_activity IN VARCHAR2
202 ,p_enq_time IN DATE
203 ,P_RETURN_CODE OUT NOCOPY NUMBER
204 ,P_ERROR_DESCRIPTION OUT NOCOPY VARCHAR2
205 );
206
207 --
208 -- Allow workflow to register a notification event through eq
209 --
210 PROCEDURE Resume_WF_EQ(
211 p_event_id number,
212 p_wf_item_type VARCHAR2 ,
213 p_wf_item_key VARCHAR2,
214 p_wf_activity VARCHAR2,
215 p_callback VARCHAR2,
216 p_priority number default 100,
217 p_return_code OUT NOCOPY NUMBER,
218 p_error_description OUT NOCOPY VARCHAR2);
219 --
220 -- Used by API to notify the parent workflow to resume
221 -- through dequeue
222 --
223 PROCEDURE Resume_Parent_Workflow;
224
225 --
226 -- Get the current state of the given queue
227 --
228 FUNCTION Get_Queue_State(
229 p_queue_name IN VARCHAR2)
230 RETURN VARCHAR2;
231
232 --
233 -- Suspend a given queue, if queue_name is not supplied,
234 -- all queues will be disabled
235 --
236 PROCEDURE DISABLE_SDP_AQ(
237 p_queue_name IN VARCHAR2,
238 p_return_code OUT NOCOPY NUMBER,
239 p_error_description OUT NOCOPY VARCHAR2);
240
241 --
242 -- Enable a given queue, if queue_name is not supplied,
243 -- all queues will be enabled
244 --
245 PROCEDURE ENABLE_SDP_AQ(
246 p_queue_name IN VARCHAR2,
247 p_return_code OUT NOCOPY NUMBER,
248 p_error_description OUT NOCOPY VARCHAR2);
249
250 --
251 -- Shut down a given SFM queue, if queue_name is not supplied,
252 -- all queues will be shutdown
253 --
254 PROCEDURE SHUTDOWN_SDP_AQ(
255 p_queue_name IN VARCHAR2,
256 p_return_code OUT NOCOPY NUMBER,
257 p_error_description OUT NOCOPY VARCHAR2);
258
259 --
260 -- Log the dequeue exceptions for the dequeuer
261 --
262 PROCEDURE Handle_DQ_Exception(
263 p_MESSAGE_ID IN RAW,
264 p_WF_ITEM_TYPE IN VARCHAR2 DEFAULT NULL,
265 p_WF_ITEM_KEY IN VARCHAR2 DEFAULT NULL,
266 p_CALLER_NAME IN VARCHAR2,
267 p_CALLBACK_TEXT IN VARCHAR2 DEFAULT NULL,
268 p_Q_NAME IN VARCHAR2,
269 p_ERROR_DESCRIPTION IN VARCHAR2,
270 p_ERROR_TIME IN DATE DEFAULT sysdate );
271
272
273 PROCEDURE InterfaceWithOSS (
274 p_OrderID IN NUMBER,
275 p_ObjectType IN VARCHAR2,
276 p_ReturnCode OUT NOCOPY NUMBER,
277 p_ErrorDescription OUT NOCOPY VARCHAR2);
278
279 Procedure LogCommandAuditTrail (FAInstanceID in number,
280 FeName in varchar2,
281 FeType in varchar2,
282 SW_Generic in varchar2,
283 CommandSent in varchar2,
284 SentDate in DATE,
285 Response in varchar2,
286 ResponseLong in CLOB,
287 RespDate in DATE,
288 ProcName in varchar2);
289
290 Procedure LogCommandAuditTrail (FAInstanceID in number,
291 FeName in varchar2,
292 FeType in varchar2,
293 SW_Generic in varchar2,
294 CommandSent in varchar2,
295 SentDate in DATE,
296 Response in varchar2,
297 RespDate in DATE,
298 ProcName in varchar2);
299
300
301 PROCEDURE DQ_XNP_EVT_Q( p_return_code OUT NOCOPY NUMBER,
302 p_error_description OUT NOCOPY VARCHAR2) ;
303
304 PROCEDURE DQ_XNP_IN_MSG_Q( p_return_code OUT NOCOPY NUMBER,
305 p_error_description OUT NOCOPY VARCHAR2) ;
306
307 PROCEDURE DQ_XNP_IN_TMR_Q( p_return_code OUT NOCOPY NUMBER,
308 p_error_description OUT NOCOPY VARCHAR2) ;
309
310 PROCEDURE DQ_XDP_ORDER_PROC_QUEUE( p_return_code OUT NOCOPY NUMBER,
311 p_error_description OUT NOCOPY VARCHAR2) ;
312
313 PROCEDURE DQ_XDP_FA_QUEUE( p_return_code OUT NOCOPY NUMBER,
314 p_error_description OUT NOCOPY VARCHAR2) ;
315
316 PROCEDURE DQ_XDP_WF_CHANNEL_Q( p_return_code OUT NOCOPY NUMBER,
317 p_error_description OUT NOCOPY VARCHAR2) ;
318
319 PROCEDURE DQ_XDP_WORKITEM_QUEUE( p_return_code OUT NOCOPY NUMBER,
320 p_error_description OUT NOCOPY VARCHAR2) ;
321
322 PROCEDURE DQ_EXCP_REENQ( p_return_code OUT NOCOPY NUMBER,
323 p_error_description OUT NOCOPY VARCHAR2) ;
324
325 PROCEDURE DQ_XDP_ORDER_PROC_REENQ( p_message_wait_timeout IN NUMBER DEFAULT 1,
326 p_correlation_id IN VARCHAR2 );
327
328 PROCEDURE DQ_XDP_FA_REENQ( p_message_wait_timeout IN NUMBER DEFAULT 1,
329 p_correlation_id IN VARCHAR2 );
330
331 PROCEDURE DQ_XDP_WORKITEM_REENQ( p_message_wait_timeout IN NUMBER DEFAULT 1,
332 p_correlation_id IN VARCHAR2 );
333
334 PROCEDURE DQ_XDP_WF_CHANNEL_REENQ( p_message_wait_timeout IN NUMBER DEFAULT 1,
335 p_correlation_id IN VARCHAR2 );
336
337 PROCEDURE DQ_XNP_IN_MSG_REENQ( p_message_wait_timeout IN NUMBER DEFAULT 1 );
338
339 PROCEDURE DQ_XNP_IN_EVT_REENQ( p_message_wait_timeout IN NUMBER DEFAULT 1 );
340
341 PROCEDURE DQ_XNP_IN_TMR_REENQ( p_message_wait_timeout IN NUMBER DEFAULT 1 );
342
343 PROCEDURE DQ_XNP_OUT_MSG_REENQ( p_message_wait_timeout IN NUMBER DEFAULT 1 );
344
345 PROCEDURE ReENQUEUE( p_msg_header IN XNP_MESSAGE.MSG_HEADER_REC_TYPE
346 ,p_body_text IN VARCHAR2
347 ,p_queue_name IN VARCHAR2
348 ,p_correlation_id IN VARCHAR2 DEFAULT NULL
349 ,p_priority IN INTEGER DEFAULT 1
350 ,p_commit_mode IN NUMBER DEFAULT XNP_MESSAGE.C_ON_COMMIT
351 ,p_delay IN NUMBER DEFAULT DBMS_AQ.NO_DELAY
352 );
353
354 PROCEDURE SET_CONTEXT( object_id IN NUMBER
355 ,object_key IN VARCHAR2
356 );
357
358 PROCEDURE DQ_XDP_WF_CHANNEL_REPROCESS;
359
360 END XDP_AQ_UTILITIES;
[Home] [Help]