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