1 PACKAGE BODY FUN_MULTI_SYSTEM_WF_PKG AS
2 /* $Header: funmulsb.pls 120.2.12010000.2 2009/01/15 10:43:12 srampure ship $ */
3
4
5 -- Check a party is a local party or remote party
6 /*-----------------------------------------------------|
7 | PROCEDURE IS_LOCAL |
8 |------------------------------------------------------|
9 | Parameters p_party_id IN NUMBER |
10 | |
11 | |
12 |------------------------------------------------------|
13 | Description |
14 | Procedure to find out whether the recipient|
15 | is located in the local instance |
16 | |
17 | Right now, it is a dummy function always |
18 | return true. It will be updated when LE |
19 | API is available |
20 |-----------------------------------------------------*/
21
22
23 FUNCTION IS_LOCAL (p_party_id IN NUMBER) return boolean
24 IS
25
26 BEGIN
27
28 -- always return true
29
30 return TRUE;
31
32 EXCEPTION
33 WHEN NO_DATA_FOUND THEN
34 NULL;
35
36 -- WHEN OTHERS THEN
37 -- generic expcetion has to be handled by the calling procedure
38
39 END IS_LOCAL;
40
41 -- Raise transaction is sent by the initiator events for all
42 -- the local recipients
43
44 /*-----------------------------------------------------|
45 | PROCEDURE RAISE_LOCAL_EVENTS |
46 |------------------------------------------------------|
47 | Parameters p_item_type IN Varchar2 |
48 | p_item_key IN Varchar2 |
49 | p_act_id IN NUMBER |
50 | p_fun_mode IN Varchar2 |
51 | p_result IN Varchar2 |
52 | |
53 |------------------------------------------------------|
54 | Description |
55 | Raise transaction is sent event for all |
56 | local recipients |
57 | |
58 | event raised: |
59 | oracle.apps.fun.manualtrx.transaction. |
60 | receive |
61 |-----------------------------------------------------*/
62
63
64
65 PROCEDURE RAISE_LOCAL_EVENTS(itemtype IN VARCHAR2,
66 itemkey IN VARCHAR2,
67 actid IN NUMBER,
68 funcmode IN VARCHAR2,
69 resultout OUT NOCOPY VARCHAR2)
70
71 IS
72 l_batch_id NUMBER;
73 l_trx_id NUMBER;
74 l_recipient_id NUMBER;
75 l_parameter_list WF_PARAMETER_LIST_T :=wf_parameter_list_t();
76 l_event_key VARCHAR2(240);
77 l_resp_id NUMBER;
78 l_user_id NUMBER;
79 l_appl_id NUMBER;
80
81
82 CURSOR c_recipient(p_batch_id IN NUMBER) IS
83 SELECT trx_id, recipient_id
84 FROM FUN_TRX_HEADERS
85 WHERE batch_id = p_batch_id;
86
87
88 BEGIN
89 l_resp_id := wf_engine.GetItemAttrNumber
90 (itemtype => itemtype,
91 itemkey => itemkey,
92 aname =>'RESP_ID');
93 l_user_id := wf_engine.GetItemAttrNumber
94 (itemtype => itemtype,
95 itemkey => itemkey,
96 aname =>'USER_ID');
97 l_appl_id := wf_engine.GetItemAttrNumber
98 (itemtype => itemtype,
99 itemkey => itemkey,
100 aname =>'APPL_ID');
101 if(funcmode='RUN') then
102
103 -- get the batch_id from the item attributes
104
105 l_batch_id := wf_engine.getitemattrnumber(itemtype,
106 itemkey,
107 'BATCH_ID');
108
109 -- open the cursor, and raise the business event for each recipient
110
111 open c_recipient(l_batch_id);
112
113 LOOP
114 FETCH c_recipient INTO l_trx_id, l_recipient_id;
115 EXIT WHEN c_recipient%NOTFOUND;
116
117 -- check the recipient is local or not
118
119 if (IS_LOCAL(l_recipient_id)) then
120 -- renew the parameter list
121 l_parameter_list :=wf_parameter_list_t();
122
123
124 -- assembly the parameter list
125
126 WF_EVENT.AddParameterToList(p_name=>'BATCH_ID',
127 p_value=>TO_CHAR(l_batch_id),
128 p_parameterlist=>l_parameter_list);
129
130 WF_EVENT.AddParameterToList(p_name=>'TRX_ID',
131 p_value=>TO_CHAR(l_trx_id),
132 p_parameterlist=>l_parameter_list);
133 WF_EVENT.AddParameterToList(p_name=>'RESP_ID',
134 p_value=>TO_CHAR(l_resp_id),
135 p_parameterlist=>l_parameter_list);
136 WF_EVENT.AddParameterToList(p_name=>'USER_ID',
137 p_value=>TO_CHAR(l_user_id),
138 p_parameterlist=>l_parameter_list);
139 WF_EVENT.AddParameterToList(p_name=>'APPL_ID',
140 p_value=>TO_CHAR(l_appl_id),
141 p_parameterlist=>l_parameter_list);
142 -- generate the event key
143
144 l_event_key :=FUN_INITIATOR_WF_PKG.GENERATE_KEY(l_batch_id, l_trx_id);
145
146 -- temp solution
147 --l_event_key :=to_char(l_batch_id) || '_' || to_char(l_trx_id) || SYS_GUID();
148
149 -- Raise the event
150
151 WF_EVENT.RAISE(p_event_name =>'oracle.apps.fun.manualtrx.transaction.receive',
152 p_event_key =>l_event_key,
153 p_parameters =>l_parameter_list);
154
155 l_parameter_list.delete();
156
157 END IF;
158 END LOOP;
159
160 close c_recipient;
161
162 -- Do we need commit?
163
164 -- COMMIT;
165 resultout := 'COMPLETE';
166 return;
167
168 END IF; -- end of the run mode
169
170
171 -- Cancel mode
172
173 IF (funcmode = 'CANCEL') THEN
174
175 -- extra cancel code goes here
176
177 null;
178
179 -- no result needed
180 resultout := 'COMPLETE';
181 return;
182 END IF;
183
184
185 EXCEPTION
186
187 WHEN OTHERS THEN
188 -- Rcords this function call in the error system
189 -- in the case of an exception.
190 wf_core.context('FUN_MULTI_SYSTEM_WF_PKG', 'RAISE_LOCAL_EVENTS',
191 itemtype, itemkey, to_char(actid), funcmode);
192
193 END RAISE_LOCAL_EVENTS;
194
195
196
197 -- Set workflow item attributes for the process
198
199 /*-----------------------------------------------------|
200 | PROCEDURE SET_ATTRIBUTES |
201 |------------------------------------------------------|
202 | Parameters p_item_type IN Varchar2 |
203 | p_item_key IN Varchar2 |
204 | p_act_id IN NUMBER |
205 | p_funcmode IN Varchar2 |
206 | p_result IN Varchar2 |
207 | |
208 |------------------------------------------------------|
209 | Description |
210 | Set the attributes of the WF process |
211 | |
212 | |
213 | |
214 | |
215 | |
216 |-----------------------------------------------------*/
217
218
219 PROCEDURE SET_ATTRIBUTES (itemtype IN VARCHAR2,
220 itemkey IN VARCHAR2,
221 actid IN NUMBER,
222 funcmode IN VARCHAR2,
223 resultout OUT NOCOPY VARCHAR2)
224
225 IS
226 l_batch_id NUMBER;
227 l_sts VARCHAR2(1);
228 BEGIN
229
230 IF (funcmode = 'RUN') THEN
231
232 -- Currently we do not need additional attributes. This may change
233 -- when handling the remote instances case.
234
235 -- get the item attributes from the WF
236 l_batch_id := wf_engine.getitemattrnumber(itemtype,
237 itemkey,
238 'BATCH_ID');
239
240 -- We only need to check if invoice is required and set the
241 -- flag in fun_trx_headers table.
242 fun_wf_common.set_invoice_reqd_flag(p_batch_id => l_batch_id,
243 x_return_status => l_sts);
244
245 -- no result needed
246 resultout := 'COMPLETE';
247 return;
248 END IF;
249
250 EXCEPTION
251
252 WHEN OTHERS THEN
253
254 -- Rcords this function call in the error system
255 -- in the case of an exception.
256
257 wf_core.context('FUN_MULTI_SYSTEM_WF_PKG', 'SET_ATTRIBUTES',
258 itemtype, itemkey, to_char(actid), funcmode);
259
260 END SET_ATTRIBUTES;
261
262 -- Count the remote instances number
263
264 /*-----------------------------------------------------|
265 | PROCEDURE COUNT_REMOTE |
266 |------------------------------------------------------|
267 | Parameters p_item_type IN Varchar2 |
268 | p_item_key IN Varchar2 |
269 | p_act_id IN NUMBER |
270 | p_funcmode IN Varchar2 |
271 | p_result IN Varchar2 |
272 | |
273 |------------------------------------------------------|
274 | Description |
275 | Count remote instance |
276 | |
277 | Dummy Function |
278 | Always return zero |
279 | In the future, we can replace it in the |
280 | set_attributes procedure call |
281 |-----------------------------------------------------*/
282
283
284 PROCEDURE COUNT_REMOTE (itemtype IN VARCHAR2,
285 itemkey IN VARCHAR2,
286 actid IN NUMBER,
287 funcmode IN VARCHAR2,
288 resultout OUT NOCOPY VARCHAR2)
289
290 IS
291
292 BEGIN
293
294 IF (funcmode = 'RUN') THEN
295
296 -- Set the attribute to zero
297
298 wf_engine.setitemattrnumber(itemtype,
299 itemkey,
300 'NUM_REMOTE_INSTANCE',
301 0);
302
303 -- no result needed
304 resultout := 'COMPLETE';
305 return;
306 END IF;
307
308 EXCEPTION
309
310 WHEN OTHERS THEN
311
312 -- Rcords this function call in the error system
313 -- in the case of an exception.
314
315 wf_core.context('FUN_MULTI_SYSTEM_WF_PKG', 'COUNT_REMOTE',
316 itemtype, itemkey, to_char(actid), funcmode);
317
318 END COUNT_REMOTE;
319
320 -- Check the trading partner that the status update event for is
321 -- a local party or not.
322 -- Note the trading partner is different for different status events
323
324 /*-----------------------------------------------------|
325 | PROCEDURE CHECK_TP_LOCAL |
326 |------------------------------------------------------|
327 | Parameters p_item_type IN Varchar2 |
328 | p_item_key IN Varchar2 |
329 | p_act_id IN NUMBER |
330 | p_funcmode IN Varchar2 |
331 | p_result IN Varchar2 |
332 | |
333 |------------------------------------------------------|
334 | Description |
335 | Check the trading partner is |
336 | local or not |
337 | |
338 | return YES / NO |
339 | |
340 | |
341 |-----------------------------------------------------*/
342
343
344 PROCEDURE CHECK_TP_LOCAL (itemtype IN VARCHAR2,
345 itemkey IN VARCHAR2,
346 actid IN NUMBER,
347 funcmode IN VARCHAR2,
348 resultout OUT NOCOPY VARCHAR2)
349
350 IS
351
352 l_tp_party_id NUMBER;
353 l_batch_id NUMBER;
354 l_trx_id NUMBER;
355 l_initiator_id NUMBER;
356 l_recipient_id NUMBER;
357 l_event_name Varchar2(240);
358 l_new_event_name Varchar2(240);
359 l_event_key Varchar2(240);
360 l_invoice_num Varchar2(50);
361
362 BEGIN
363
364 IF (funcmode = 'RUN') THEN
365
366 -- get the item attributes from the WF
367 l_batch_id := wf_engine.getitemattrnumber(itemtype,
368 itemkey,
369 'BATCH_ID');
370
371 l_trx_id := wf_engine.getitemattrnumber(itemtype,
372 itemkey,
373 'TRX_ID');
374
375 l_event_name := wf_engine.getitemattrtext (itemtype,
376 itemkey,
377 'EVENT_NAME');
378
379 -- obtain the initiator_id and recipient_id
380
381 SELECT initiator_id, recipient_id
382 INTO l_initiator_id, l_recipient_id
383 FROM FUN_TRX_HEADERS
384 WHERE batch_id=l_batch_id
385 AND trx_id=l_trx_id;
386
387 -- determine the TP ID
388 /* TP is the recipient if the event is
389 oracle.apps.fun.manualtrx.glcomple.send
390 oracle.apps.fun.manualtrx.arcomplete.send
391
392 TP is the initiator if the event is
393
394 oracle.apps.fun.manualtrx.error.send
395 oracle.apps.fun.manualtrx.reception.send
396 oracle.apps.fun.manualtrx.rejection.send
397 oracle.apps.fun.manualtrx.approval.send
398 oracle.apps.fun.manualtrx.complete.send
399 */
400
401 if(lower(l_event_name) in ('oracle.apps.fun.manualtrx.glcomplete.send',
402 'oracle.apps.fun.manualtrx.arcomplete.send'))
403 then
404 l_tp_party_id :=l_recipient_id;
405
406 -- get the Invoice Number
407 l_invoice_num := wf_engine.getitemattrtext (itemtype,
408 itemkey,
409 'INVOICE_NUM');
410
411
412 else
413 l_tp_party_id :=l_initiator_id;
414 end if;
415
416 -- determine the TP is local or not
417
418 if(IS_LOCAL(l_tp_party_id)) then
419
420 resultout:='COMPLETE:Y';
421
422 -- set RECEIVE_EVENT_NAME
423 l_new_event_name :=(RTRIM(l_event_name, 'send')) || 'receive';
424 -- generate the event key
425
426 -- l_event_key :=FUN_INITIATOR_WF_PKG.GENERATE_KEY(l_batch_id, xsl_trx_id);
427
428 -- temp solution
429 l_event_key :=to_char(l_batch_id) || '_' || to_char(l_trx_id) || SYS_GUID();
430
431 --set the WF item value
432 wf_engine.setitemattrtext(itemtype,
433 itemkey,
434 'RECEIVE_EVENT_NAME',
435 l_new_event_name);
436
437 wf_engine.setitemattrtext(itemtype,
438 itemkey,
439 'LOCAL_EVT_KEY',
440 l_event_key);
441 wf_engine.setitemattrtext(itemtype,
442 itemkey,
443 'INVOICE_NUM',
444 l_invoice_num);
445 else
446 resultout:='COMPLETE:N';
447 end if;
448
449 return;
450
451 END IF; -- end of RUN mode
452
453 EXCEPTION
454
455 WHEN OTHERS THEN
456
457 -- Rcords this function call in the error system
458 -- in the case of an exception.
459
460 wf_core.context('FUN_MULTI_SYSTEM_WF_PKG', 'CHECK_TP_LOCAL',
461 itemtype, itemkey, to_char(actid), funcmode);
462
463 END CHECK_TP_LOCAL;
464
465 END FUN_MULTI_SYSTEM_WF_PKG;
466
[Home] [Help]