[Home] [Help]
PACKAGE BODY: APPS.FUN_WF_COMMON
Source
1 PACKAGE BODY FUN_WF_COMMON AS
2 /* $Header: FUN_WF_COMMON_B.pls 120.34.12020000.3 2012/11/12 17:26:30 shnaraya ship $ */
3
4 /*-----------------------------------------------------
5 * FUNCTION generate_event_key
6 * ----------------------------------------------------
7 * Get the attributes for the recipient WF.
8 * ---------------------------------------------------*/
9
10 FUNCTION generate_event_key (
11 batch_id IN number,
12 trx_id IN number) RETURN varchar2
13 IS
14 l_result varchar2(64);
15 BEGIN
16 l_result := TO_CHAR(batch_id)||'_'||TO_CHAR(trx_id)||SYS_GUID();
17 RETURN l_result;
18 END generate_event_key;
19
20
21
22 /*-----------------------------------------------------
23 * FUNCTION concat_msg_stack
24 * ----------------------------------------------------
25 * Pop <p_depth> messages off the fnd_message stack and
26 * concat them, separated by '\n'.
27 *
28 * If there are not enough messages in the stack, then
29 * all the messages are popped.
30 * ---------------------------------------------------*/
31
32 FUNCTION concat_msg_stack (
33 p_depth IN number,
34 p_flush IN boolean default TRUE) RETURN varchar2
35 IS
36 i number;
37 l_depth number;
38 l_result varchar2(2000) ;
39 l_curr varchar2(2000);
40 l_msg_index number;
41 BEGIN
42 l_result :='';
43 IF (p_depth = 0) THEN
44 RETURN '';
45 END IF;
46
47 l_depth := fnd_msg_pub.count_msg;
48 IF (p_depth < l_depth) THEN
49 l_depth := p_depth;
50 END IF;
51
52 FOR i IN 1..l_depth LOOP
53 IF i = 1
54 THEN
55 fnd_msg_pub.get(fnd_msg_pub.g_first, 'F', l_curr, l_msg_index);
56 ELSE
57 fnd_msg_pub.get(fnd_msg_pub.g_next, 'F', l_curr, l_msg_index);
58 END IF;
59 IF ( nvl(p_flush,TRUE)) THEN
60 fnd_msg_pub.delete_msg(l_msg_index);
61 END IF;
62 l_result := l_result || l_curr || fnd_global.newline;
63 END LOOP;
64
65 fnd_msg_pub.get(fnd_msg_pub.g_next, 'F', l_curr, l_msg_index);
66 IF ( nvl(p_flush,TRUE)) THEN
67 fnd_msg_pub.delete_msg(l_msg_index);
68 END IF;
69 l_result := l_result || l_curr;
70 RETURN l_result;
71 END concat_msg_stack;
72
73
74
75 /*-----------------------------------------------------
76 * FUNCTION get_contact_role
77 * ----------------------------------------------------
78 * Get the contact for this party into an item attr
79 * called CONTACT.
80 * It assumes there is an item attr called PARTY_ID.
81 * ---------------------------------------------------*/
82
83 FUNCTION get_contact_role (
84 p_party_id IN number) RETURN varchar2
85 IS
86 l_role_name varchar2(30) ;
87 l_role_display varchar2(60);
88 l_exp_date date ;
89 l_users varchar2(360) ;
90 l_wf_user varchar2(360);
91 l_hz_user_id number;
92 l_dummy varchar2(60);
93 l_count number ;
94 l_exist number;
95 l_role_name_db varchar2(30);
96
97
98 BEGIN
99 -- Function not used anymore.
100
101 NULL;
102
103
104 RETURN l_role_name;
105 END get_contact_role;
106
107
108 /*-----------------------------------------------------
109 * PROCEDURE is_arap_batch_mode
110 * ----------------------------------------------------
111 * Check whether AR/AP transfer is in batch mode.
112 * ---------------------------------------------------*/
113
114 PROCEDURE is_arap_batch_mode (
115 itemtype IN varchar2,
116 itemkey IN varchar2,
117 actid IN number,
118 funcmode IN varchar2,
119 resultout IN OUT NOCOPY varchar2)
120 IS
121 l_result boolean;
122 BEGIN
123 IF (funcmode = 'RUN' OR funcmode = 'CANCEL') THEN
124 l_result := fun_system_options_pkg.is_apar_batch();
125 IF (l_result) THEN
126 resultout := wf_engine.eng_completed||':T';
127 ELSE
128 resultout := wf_engine.eng_completed||':F';
129 END IF;
130
131 RETURN;
132 END IF;
133
134 resultout := wf_engine.eng_null;
135 RETURN;
136
137 EXCEPTION
138 WHEN others THEN
139 wf_core.context('FUN_WF_COMMON', 'IS_ARAP_BATCH_MODE',
140 itemtype, itemkey, TO_CHAR(actid), funcmode);
141 RAISE;
142 END is_arap_batch_mode;
143
144
145
146 /*-----------------------------------------------------
147 * PROCEDURE raise_complete
148 * ----------------------------------------------------
149 * Raise the complete event.
150 * ---------------------------------------------------*/
151
152 PROCEDURE raise_complete (
153 itemtype IN varchar2,
154 itemkey IN varchar2,
155 actid IN number,
156 funcmode IN varchar2,
157 resultout IN OUT NOCOPY varchar2)
158 IS
159 l_batch_id number;
160 l_trx_id number;
161 l_event_key varchar2(240);
162 l_params wf_parameter_list_t := wf_parameter_list_t();
163 BEGIN
164 IF (funcmode = 'RUN') THEN
165 l_batch_id := wf_engine.GetItemAttrNumber
166 (itemtype => itemtype,
167 itemkey => itemkey,
168 aname => 'BATCH_ID');
169 l_trx_id := wf_engine.GetItemAttrNumber
170 (itemtype => itemtype,
171 itemkey => itemkey,
172 aname => 'TRX_ID');
173 l_event_key := fun_wf_common.generate_event_key(l_batch_id, l_trx_id);
174
175 wf_event.AddParameterToList(p_name => 'TRX_ID',
176 p_value => TO_CHAR(l_trx_id),
177 p_parameterlist => l_params);
178 wf_event.AddParameterToList(p_name => 'BATCH_ID',
179 p_value => TO_CHAR(l_batch_id),
180 p_parameterlist => l_params);
181
182 wf_event.raise(
183 p_event_name => 'oracle.apps.fun.manualtrx.complete.send',
184 p_event_key => l_event_key,
185 p_parameters => l_params);
186
187 l_params.delete();
188 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
189 RETURN;
190 END IF;
191
192 resultout := wf_engine.eng_null;
193 RETURN;
194
195 EXCEPTION
196 WHEN others THEN
197 wf_core.context('FUN_WF_COMMON', 'RAISE_COMPLETE',
198 itemtype, itemkey, TO_CHAR(actid), funcmode);
199 RAISE;
200 END raise_complete;
201
202
203 /*-----------------------------------------------------
204 * PROCEDURE update_status_error
205 * ----------------------------------------------------
206 * Update status to error.
207 * ---------------------------------------------------*/
208
209 PROCEDURE update_status_error (
210 itemtype IN varchar2,
211 itemkey IN varchar2,
212 actid IN number,
213 funcmode IN varchar2,
214 resultout IN OUT NOCOPY varchar2)
215 IS
216 l_trx_id number;
217 l_status varchar2(1);
218 l_msg_count number;
219 l_msg_data varchar2(1000);
220 BEGIN
221 IF (funcmode = 'RUN') THEN
222 l_trx_id := wf_engine.GetItemAttrNumber
223 (itemtype => itemtype,
224 itemkey => itemkey,
225 aname => 'TRX_ID');
226
227 fun_trx_pvt.update_trx_status
228 (p_api_version => 1.0,
229 x_return_status => l_status,
230 x_msg_count => l_msg_count,
231 x_msg_data => l_msg_data,
232 p_trx_id => l_trx_id,
233 p_update_status_to => 'ERROR');
234 -- TODO: check return status
235 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
236 RETURN;
237 END IF;
238
239 resultout := wf_engine.eng_null;
240 RETURN;
241
242 EXCEPTION
243 WHEN others THEN
244 wf_core.context('FUN_RECIPIENT_WF', 'UPDATE_STATUS_ERROR',
245 itemtype, itemkey, TO_CHAR(actid), funcmode);
246 RAISE;
247 END update_status_error;
248
249
250 /*-----------------------------------------------------
251 * PROCEDURE update_status_received
252 * ----------------------------------------------------
253 * Update status to received.
254 * ---------------------------------------------------*/
255
256 PROCEDURE update_status_received (
257 itemtype IN varchar2,
258 itemkey IN varchar2,
259 actid IN number,
260 funcmode IN varchar2,
261 resultout IN OUT NOCOPY varchar2)
262 IS
263 l_trx_id number;
264 l_status varchar2(1);
265 l_msg_count number;
266 l_msg_data varchar2(1000);
267 BEGIN
268 IF (funcmode = 'RUN') THEN
269 l_trx_id := wf_engine.GetItemAttrNumber
270 (itemtype => itemtype,
271 itemkey => itemkey,
272 aname => 'TRX_ID');
273
274 fun_trx_pvt.update_trx_status
275 (p_api_version => 1.0,
276 x_return_status => l_status,
277 x_msg_count => l_msg_count,
278 x_msg_data => l_msg_data,
279 p_trx_id => l_trx_id,
280 p_update_status_to => 'RECEIVED');
281 -- TODO: check return status
282 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
283 RETURN;
284 END IF;
285
286 resultout := wf_engine.eng_null;
287 RETURN;
288
289 EXCEPTION
290 WHEN others THEN
291 wf_core.context('FUN_RECIPIENT_WF', 'UPDATE_STATUS_RECEIVED',
292 itemtype, itemkey, TO_CHAR(actid), funcmode);
293 RAISE;
294 END update_status_received;
295
296
297 /*-----------------------------------------------------
298 * PROCEDURE update_status_complete
299 * ----------------------------------------------------
300 * Update status to complete.
301 * ---------------------------------------------------*/
302
303 PROCEDURE update_status_complete (
304 itemtype IN varchar2,
305 itemkey IN varchar2,
306 actid IN number,
307 funcmode IN varchar2,
308 resultout IN OUT NOCOPY varchar2)
309 IS
310 l_trx_id number;
311 l_status varchar2(1);
312 l_msg_count number;
313 l_msg_data varchar2(1000);
314 BEGIN
315 IF (funcmode = 'RUN') THEN
316 l_trx_id := wf_engine.GetItemAttrNumber
317 (itemtype => itemtype,
318 itemkey => itemkey,
319 aname => 'TRX_ID');
320
321 fun_trx_pvt.update_trx_status
322 (p_api_version => 1.0,
323 x_return_status => l_status,
324 x_msg_count => l_msg_count,
325 x_msg_data => l_msg_data,
326 p_trx_id => l_trx_id,
327 p_update_status_to => 'COMPLETE');
328 -- TODO: check return status
329 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
330 RETURN;
331 END IF;
332
333 resultout := wf_engine.eng_null;
334 RETURN;
335
336 EXCEPTION
337 WHEN others THEN
338 wf_core.context('FUN_WF_COMMON', 'UPDATE_STATUS_COMPLETE',
339 itemtype, itemkey, TO_CHAR(actid), funcmode);
340 RAISE;
341 END update_status_complete;
342
343
344 /*-----------------------------------------------------
345 * PROCEDURE raise_wf_bus_event
346 * ----------------------------------------------------
347 * Raise workflow business event
348 * ---------------------------------------------------*/
349
350 PROCEDURE raise_wf_bus_event (
351 batch_id IN number,
352 trx_id IN number default null,
353 event_key IN varchar2 default null,
354 event_name IN varchar2 default null)
355 IS
356 l_parameter_list WF_PARAMETER_LIST_T :=wf_parameter_list_t();
357 l_user_id NUMBER;
358 l_resp_id NUMBER;
359 l_appl_id NUMBER;
360 l_user_env_lang VARCHAR2(5);
361 CURSOR c_trx ( p_batch_id NUMBER) IS
362 SELECT trx_id
363 FROM fun_trx_headers
364 WHERE batch_id = p_batch_id;
365
366 begin
367 --Bug: 7639191
368 l_user_id := fnd_global.user_id;
369 l_resp_id := fnd_global.resp_id;
370 l_appl_id := fnd_global.resp_appl_id;
371
372 select USERENV('LANG')
373 INTO l_user_env_lang
374 FROM DUAL;
375
376 FND_GLOBAL.APPS_INITIALIZE(l_user_id,l_resp_id,l_appl_id);
377
378 WF_EVENT.AddParameterToList(p_name=>'BATCH_ID',
379 p_value=>TO_CHAR(batch_id),
380 p_parameterlist=>l_parameter_list);
381 WF_EVENT.AddParameterToList(p_name=>'RESP_ID',
382 p_value=>TO_CHAR(l_resp_id),
383 p_parameterlist=>l_parameter_list);
384 WF_EVENT.AddParameterToList(p_name=>'USER_ID',
385 p_value=>TO_CHAR(l_user_id),
386 p_parameterlist=>l_parameter_list);
387 WF_EVENT.AddParameterToList(p_name=>'APPL_ID',
388 p_value=>TO_CHAR(l_appl_id),
389 p_parameterlist=>l_parameter_list);
390 IF trx_id is not null then
391 WF_EVENT.AddParameterToList(p_name=>'TRX_ID',
392 p_value=>TO_CHAR(trx_id),
393 p_parameterlist=>l_parameter_list);
394 END IF;
395
396 /* Start of changes for AME Uptake, 3671923. 11 Oct 2004 */
397 IF (event_name IS NULL
398 OR event_name = 'oracle.apps.fun.manualtrx.batch.send')
399 THEN
400 WF_EVENT.AddParameterToList(p_name=>'USER_LANG',
401 p_value=>TO_CHAR(l_user_env_lang),
402 p_parameterlist=>l_parameter_list);
403 IF trx_id IS NOT NULL
404 THEN
405 -- Initialize the AME Approval Process
406 ame_api2.clearallapprovals(applicationIdIn => 435,
407 transactionTypeIn => 'FUN_IC_RECI_TRX',
408 transactionIdIn => trx_id);
409 ELSE
410 FOR l_trx IN c_trx(batch_id)
411 LOOP
412 -- Initialize the AME Approval Process
413 ame_api2.clearallapprovals(applicationIdIn => 435,
414 transactionTypeIn => 'FUN_IC_RECI_TRX',
415 transactionIdIn => l_trx.trx_id);
416
417 END LOOP;
418
419 END IF;
420 END IF;
421
422 /* End of changes for AME Uptake, 3671923. 11 Oct 2004 */
423
424 IF event_name is not null then
425 WF_EVENT.RAISE(p_event_name =>event_name,
426 p_event_key =>nvl(event_key,'Test '||batch_id),
427 p_parameters =>l_parameter_list);
428 ELSE
429 WF_EVENT.RAISE(p_event_name =>'oracle.apps.fun.manualtrx.batch.send',
430 p_event_key =>nvl(event_key,'Test '||batch_id),
431 p_parameters =>l_parameter_list);
432 END IF;
433 END raise_wf_bus_event;
434
435
436 /* Start of changes for AME Uptake, 3671923. 07 Jun 2004 */
437
438 /* ---------------------------------------------------------------------------
439 Name : get_ame_contacts
440 Pre-reqs : None.
441 Modifies : None.
442 Function : This function is called by the various intercompany workflows
443 to get the contact list to whom FYI notifications need to be
444 sent out.
445 Parameters:
446 IN : itemtype - Workflow Item Type
447 itemkey - Workflow Item Key
448 actid - Workflow Activity Id
449 funcmode - Workflow Function Mode
450 OUT : resultout - Result of the workflow function
451 'Y' indicates contacts were found, 'N' indicates no contacts
452 Notes : None.
453 Testing : This function will be tested via workflows FUNARINT, FUNAPINT,
454 FUNGLINT, FUNRTVAL, FUNIMAIN
455 ------------------------------------------------------------------------------*/
456 PROCEDURE get_ame_contacts (itemtype IN VARCHAR2,
457 itemkey IN VARCHAR2,
458 actid IN NUMBER,
459 funcmode IN VARCHAR2,
460 resultout OUT NOCOPY VARCHAR2 )
461 IS
462 l_approvers_found VARCHAR2(1);
463 l_process_complete VARCHAR2(1);
464 l_transaction_id NUMBER;
465 l_role_name VARCHAR2(30);
466 l_contact_type VARCHAR2(1);
467 l_return_status VARCHAR2(1);
468 l_ame_admin_user VARCHAR2(30);
469 l_error_message VARCHAR2(2000);
470
471 BEGIN
472
473 l_transaction_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
474 itemkey => itemkey,
475 aname => 'TRX_ID');
476
477 l_contact_type := wf_engine.GetActivityAttrText(itemtype => itemtype,
478 itemkey => itemkey,
479 actid => actid,
480 aname => 'CONTACT_ORG_TYPE');
481
482 -- Call the procedure which will return the AME role
483 fun_wf_common.get_ame_role_list
484 (itemkey => itemkey,
485 p_transaction_id => l_transaction_id,
486 p_fyi_notification => 'Y',
487 p_contact_type => l_contact_type,
488 x_approvers_found => l_approvers_found,
489 x_process_complete => l_process_complete,
490 x_role => l_role_name,
491 x_return_status => l_return_status,
492 x_ame_admin_user => l_ame_admin_user,
493 x_error_message => l_error_message);
494
495 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
496 THEN
497 wf_engine.SetItemAttrText(itemtype => itemtype,
498 itemkey => itemkey,
499 aname => 'AME_ADMIN_USER',
500 avalue => l_ame_admin_user);
501
502 wf_engine.SetItemAttrText(itemtype => itemtype,
503 itemkey => itemkey,
504 aname => 'AME_ERROR',
505 avalue => l_error_message);
506
507 resultout := wf_engine.eng_completed||':'||'ERROR';
508 ELSIF l_approvers_found = 'N'
509 THEN
510 resultout := wf_engine.eng_completed||':'||'NO';
511 ELSIF l_approvers_found = 'Y'
512 THEN
513 -- Set the workflow attribute for CONTACT
514 wf_engine.SetItemAttrText(itemtype => itemtype,
515 itemkey => itemkey,
516 aname => 'CONTACT',
517 avalue => l_role_name);
518
519 resultout := wf_engine.eng_completed||':'||'YES';
520 END IF;
521
522 EXCEPTION
523 WHEN OTHERS
524 THEN
525 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
526 THEN
527 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
528 'fun.plsql.fun_wf_common.get_ame_contacts',
529 SQLERRM || ' Error occurred '||
530 ' for transaction ' || l_transaction_id);
531 END IF;
532
533 wf_core.context('FUN_WF_COMMON', 'GET_AME_CONTACTS',
534 itemtype, itemkey, TO_CHAR(actid), funcmode);
535 END get_ame_contacts;
536
537 /* Bug:11722204-----------------------------------------------------------------
538 Name : get_ame_admin
539 Pre-reqs : None.
540 Modifies : None.
541 Function : This function is called by FUNRMAIN to get the workflow admin
542 to whom FYI notifications need to be sent out.
543 Parameters:
544 IN : itemtype - Workflow Item Type
545 itemkey - Workflow Item Key
546 actid - Workflow Activity Id
547 funcmode - Workflow Function Mode
548 OUT : resultout - Result of the workflow function
549 'Y' indicates contacts were found, 'N' indicates no contacts
550 Notes : None.
551 Testing : This function will be tested via workflows FUNRMAIN
552 ------------------------------------------------------------------------------*/
553 PROCEDURE get_ame_admin (itemtype IN VARCHAR2,
554 itemkey IN VARCHAR2,
555 actid IN NUMBER,
556 funcmode IN VARCHAR2,
557 resultout OUT NOCOPY VARCHAR2 )
558 IS
559 l_approvers_found VARCHAR2(1);
560 l_process_complete VARCHAR2(1);
561 l_transaction_id NUMBER;
562 l_role_name VARCHAR2(30);
563 l_contact_type VARCHAR2(1);
564 l_return_status VARCHAR2(1);
565 l_ame_admin_user VARCHAR2(30);
566 l_error_message VARCHAR2(2000);
567
568 BEGIN
569
570 l_transaction_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
571 itemkey => itemkey,
572 aname => 'TRX_ID');
573
574 l_contact_type := wf_engine.GetActivityAttrText(itemtype => itemtype,
575 itemkey => itemkey,
576 actid => actid,
577 aname => 'CONTACT_ORG_TYPE');
578
579 -- Call the procedure which will return the AME role
580 fun_wf_common.get_ame_role_list
581 (itemkey => itemkey,
582 p_transaction_id => l_transaction_id,
583 p_fyi_notification => 'Y',
584 p_contact_type => l_contact_type,
585 x_approvers_found => l_approvers_found,
586 x_process_complete => l_process_complete,
587 x_role => l_role_name,
588 x_return_status => l_return_status,
589 x_ame_admin_user => l_ame_admin_user,
590 x_error_message => l_error_message);
591
592 wf_engine.SetItemAttrText(itemtype => itemtype,
593 itemkey => itemkey,
594 aname => 'AME_ADMIN_USER',
595 avalue => l_ame_admin_user);
596
597 wf_engine.SetItemAttrText(itemtype => itemtype,
598 itemkey => itemkey,
599 aname => 'AME_ERROR',
600 avalue => l_error_message);
601
602 resultout := wf_engine.eng_completed||':'||'YES';
603
604
605 EXCEPTION
606 WHEN OTHERS
607 THEN
608 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
609 THEN
610 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
611 'fun.plsql.fun_wf_common.get_ame_admin',
612 SQLERRM || ' Error occurred '||
613 ' for transaction ' || l_transaction_id);
614 END IF;
615
616 wf_core.context('FUN_WF_COMMON', 'GET_AME_CONTACTS',
617 itemtype, itemkey, TO_CHAR(actid), funcmode);
618 END get_ame_admin;
619
620 /* ---------------------------------------------------------------------------
621 Name : get_ame_approvers
622 Pre-reqs : None.
623 Modifies : None.
624 Function : This function is called by the Recipient Main workflow
625 to get the contact list to whom request for approval notfications
626 are sent out.
627 Parameters:
628 IN : itemtype - Workflow Item Type
629 itemkey - Workflow Item Key
630 actid - Workflow Activity Id
631 funcmode - Workflow Function Mode
632 OUT : resultout - Result of the workflow function
633 'NOTIFY' indicates approvers were found,
634 'COMPLETE' indicates no more approvals required - process is complete
635 'ERROR' indicates there was an error
636 'WAIT' indicates we are waiting for some approver to respond to notification
637 Notes : None.
638 Testing : This function will be tested via workflow FUNRMAIN
639 ------------------------------------------------------------------------------*/
640 PROCEDURE get_ame_approvers (itemtype IN VARCHAR2,
641 itemkey IN VARCHAR2,
642 actid IN NUMBER,
643 funcmode IN VARCHAR2,
644 resultout OUT NOCOPY VARCHAR2 )
645 IS
646 l_approvers_found VARCHAR2(1);
647 l_process_complete VARCHAR2(1);
648 l_transaction_id NUMBER;
649 l_role_name VARCHAR2(30);
650 l_contact_type VARCHAR2(1);
651 l_trx_status fun_trx_headers.status%TYPE;
652 l_return_status VARCHAR2(1);
653 l_ame_admin_user VARCHAR2(30);
654 l_error_message VARCHAR2(2000);
655
656
657 BEGIN
658
659 l_transaction_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
660 itemkey => itemkey,
661 aname => 'TRX_ID');
662
663 l_contact_type := wf_engine.GetActivityAttrText(itemtype => itemtype,
664 itemkey => itemkey,
665 actid => actid,
666 aname => 'CONTACT_ORG_TYPE');
667
668 -- Initialize the UI Action and Approver name workflow attributes.
669 -- They will get set if and when user takes some approval action
670 -- from the UI
671 wf_engine.SetItemAttrText
672 (itemtype => 'FUNRMAIN',
673 itemkey => itemkey,
674 aname => 'UI_ACTION_TYPE',
675 avalue => 'NONE');
676
677 wf_engine.SetItemAttrText
678 (itemtype => 'FUNRMAIN',
679 itemkey => itemkey,
680 aname => 'UI_ACTION_USER_NAME',
681 avalue => NULL);
682
683 wf_engine.SetItemAttrNumber
684 (itemtype => 'FUNRMAIN',
685 itemkey => itemkey,
686 aname => 'UI_ACTION_USER_ID',
687 avalue => NULL);
688
689 -- Call the procedure which will return the AME role list
690 fun_wf_common.get_ame_role_list
691 (itemkey => itemkey,
692 p_transaction_id => l_transaction_id,
693 p_fyi_notification => 'N',
694 p_contact_type => l_contact_type,
695 x_approvers_found => l_approvers_found,
696 x_process_complete => l_process_complete,
697 x_role => l_role_name,
698 x_return_status => l_return_status ,
699 x_ame_admin_user => l_ame_admin_user,
700 x_error_message => l_error_message);
701
702 -- Check if Approval process is complete or was there an error
703 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
704 THEN
705 wf_engine.SetItemAttrText(itemtype => itemtype,
706 itemkey => itemkey,
707 aname => 'AME_ADMIN_USER',
708 avalue => l_ame_admin_user);
709
710 wf_engine.SetItemAttrText(itemtype => itemtype,
711 itemkey => itemkey,
712 aname => 'AME_ERROR',
713 avalue => l_error_message);
714
715 resultout := wf_engine.eng_completed||':'||'ERROR';
716 ELSIF l_process_complete = 'Y'
717 THEN
718 resultout := wf_engine.eng_completed||':'||'COMPLETE';
719 ELSIF (l_approvers_found = 'N' AND l_process_complete = 'N')
720 THEN
721 resultout := wf_engine.eng_completed||':'||'WAIT';
722 ELSIF l_approvers_found = 'Y'
723 THEN
724 wf_engine.SetItemAttrText(itemtype => itemtype,
725 itemkey => itemkey,
726 aname => 'CONTACT',
727 avalue => l_role_name);
728
729 resultout := wf_engine.eng_completed||':'||'NOTIFY';
730 END IF;
731
732 EXCEPTION
733 WHEN OTHERS
734 THEN
735 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
736 THEN
737 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
738 'fun.plsql.fun_wf_common.get_ame_approvers',
739 SQLERRM || ' Error occurred '||
740 'for transaction ' || l_transaction_id);
741 END IF;
742
743 wf_core.context('FUN_WF_COMMON', 'GET_AME_APPROVERS',
744 itemtype, itemkey, TO_CHAR(actid), funcmode);
745 END get_ame_approvers;
746
747
748 /* ---------------------------------------------------------------------------
749 Name : get_ame_role_list
750 Pre-reqs : None.
751 Modifies : None.
752 Function : This function is called by get_ame_contacts() and
753 get_ame_approvers functions. It returns the name of the wflow
754 role to whom FYI or approval notifications are sent out.
755 Parameters:
756 IN : p_transaction_id - fun_trx_headers.trx_id
757 p_fyi_notification - 'Y' or 'N' indicating if its FYI notification
758 p_contact_type - 'I'- Initiator or 'R'- Recipient
759 OUT : x_approvers_found - 'Y'or 'N' indicating approvers found
760 x_process_complete - 'Y'or 'N' indicating process complete
761 x_role - workflow role name
762 x_return_status - Return Status.
763 x_ame_admin_user - Ame Administrator
764 x_error_message - Error Message
765 Notes : None.
766 Testing : This function will be tested via the various intercompany
767 workflows
768 ------------------------------------------------------------------------------*/
769 PROCEDURE get_ame_role_list(itemkey IN VARCHAR2,
770 p_transaction_id IN NUMBER,
771 p_fyi_notification IN VARCHAR2,
772 p_contact_type IN VARCHAR2,
773 x_approvers_found OUT NOCOPY VARCHAR2,
774 x_process_complete OUT NOCOPY VARCHAR2,
775 x_role OUT NOCOPY VARCHAR2,
776 x_return_status OUT NOCOPY VARCHAR2,
777 x_ame_admin_user OUT NOCOPY VARCHAR2,
778 x_error_message OUT NOCOPY VARCHAR2)
779
780 IS
781 l_approver_table ame_util.approversTable2;
782 l_invalid_usr_rec ame_util.approverRecord2;
783 l_ame_admin_rec ame_util.approverRecord2;
784 l_process_complete VARCHAR2(1);
785 l_party_id NUMBER;
786 l_index NUMBER;
787 l_role_name VARCHAR2(30) ;
788 l_role_display hz_parties.party_name%TYPE;
789 l_exp_date DATE ;
790 l_role_exists VARCHAR2(1) ;
791 l_transaction_type VARCHAR2(30);
792 l_users VARCHAR2(2000);
793 l_invalid_users VARCHAR2(2000);
794 l_valid_user VARCHAR2(1);
795
796
797 CURSOR c_chk_wf_role (p_role_name VARCHAR2) IS
798 SELECT 'Y'
799 FROM wf_local_roles
800 WHERE name = p_role_name;
801
802 CURSOR c_get_orgname (p_transaction_id NUMBER,
803 p_contact_type VARCHAR2) IS
804 SELECT hzp.party_name
805 FROM hz_parties hzp,
806 fun_trx_headers trx
807 WHERE hzp.party_id = DECODE(p_contact_type,'I',trx.initiator_id,
808 trx.recipient_id)
809 AND trx.trx_id = p_transaction_id;
810
811 BEGIN
812
813 OPEN c_get_orgname (p_transaction_id, p_contact_type);
814 FETCH c_get_orgname INTO l_role_display;
815 CLOSE c_get_orgname;
816
817 IF p_contact_type = 'I'
818 THEN
819 -- We need the contact list for the initiating organization
820 l_transaction_type := 'FUN_IC_INIT_TRX';
821 l_role_name:='FUN_ADHOC_INIT_'||p_transaction_id;
822 ELSE
823 -- We need the contact list for the recipient organization
824 l_transaction_type := 'FUN_IC_RECI_TRX';
825 l_role_name:='FUN_ADHOC_RECI_'||p_transaction_id;
826 END IF;
827
828 l_exp_date := SYSDATE + 1;
829 l_role_exists:='N';
830 x_return_status := FND_API.G_RET_STS_SUCCESS;
831
832 -- Check if the ADHOC role already exists
833 OPEN c_chk_wf_role(l_role_name);
834 FETCH c_chk_wf_role INTO l_role_exists;
835 CLOSE c_chk_wf_role;
836
837 -- Get the AME Admin Approver
838 ame_api2.getAdminApprover(
839 applicationIdIn => 435,
840 transactionTypeIn => l_transaction_type,
841 adminApproverOut => l_ame_admin_rec);
842
843 x_ame_admin_user := l_ame_admin_rec.name;
844
845 IF p_fyi_notification = 'Y'
846 THEN
847 -- Call the AME API to get the list of ALL approvers
848 ame_api2.getAllApprovers7 (
849 applicationIdIn => 435,
850 transactionTypeIn => l_transaction_type,
851 transactionIdIn => to_char(p_transaction_id),
852 approvalProcessCompleteYNOut => l_process_complete,
853 ApproversOut => l_approver_table);
854
855 ELSE
856 -- Call the AME API to get the list of next approver
857 ame_api2.getNextApprovers4 (
858 applicationIdIn => 435,
859 transactionTypeIn => l_transaction_type,
860 transactionIdIn => to_char(p_transaction_id),
861 approvalProcessCompleteYNOut => l_process_complete,
862 nextApproversOut => l_approver_table);
863
864 END IF;
865
866 -- Check if AME encountered any errors.
867 IF l_approver_table.COUNT > 0
868 AND l_approver_table(1).approval_status = ame_util.exceptionStatus
869 THEN
870 -- Ame had an exception whilst generating the approver
871 -- list.
872 x_return_status := FND_API.G_RET_STS_ERROR;
873 FND_MESSAGE.Set_Name('FUN','FUN_AME_EXECUTION_EXCEPTION');
874 x_error_message := FND_MESSAGE.GET;
875
876 RETURN;
877 END IF;
878
879 -- Check approval status etc ...
880 -- IF l_process_complete = ame_util.booleanTrue
881 IF l_process_complete = 'Y' -- 6995183
882 THEN
883 x_process_complete := 'Y';
884 ELSE
885 IF l_approver_table.COUNT = 0
886 THEN
887 -- This indicates we are still waiting for response from a few approvers
888 x_approvers_found := 'N';
889 --Bug No:5897122
890 x_process_complete := 'Y';
891 ELSE
892 -- This indicates there are still approvers to be notified.
893 x_approvers_found := 'Y';
894 x_process_complete := 'N';
895 END IF;
896 END IF;
897
898 -- If the approval process is not over or if this is an FYI
899 -- notification, build the list of resources to send out the
900 -- notifications
901 If x_approvers_found = 'Y' OR p_fyi_notification = 'Y'
902 THEN
903 -- Check that the list of approvers returned are all authorised
904 -- to view the intercompany transaction.
905
906 -- The security model is undergoing changes as per ER 3358579
907 -- Once the technical design for the ER has been finalized,
908 -- this section will be updated with the correct
909 -- validation (see open issues 2)
910
911 FOR l_index IN 1..l_approver_table.COUNT
912 LOOP
913 -- Check user has access to the transaction
914 IF p_fyi_notification = 'Y'
915 THEN
916 -- Check if user has Update, View or Contact access.
917 NULL;
918 ELSE
919 -- Check if user has Update access.
920 NULL;
921 END IF;
922
923 -- For now, call is_valid_approver procedure
924 l_valid_user := fun_wf_common.is_user_valid_approver
925 (p_transaction_id => p_transaction_id,
926 p_user_id => NULL,
927 p_role_name => l_approver_table(l_index).name,
928 p_org_type => p_contact_type,
929 p_mode => 'WF');
930
931 IF l_valid_user = 'Y'
932 THEN
933 -- Add the user to list of users who can approve the
934 -- document.
935 IF l_users IS NULL
936 THEN
937 l_users := l_approver_table(l_index).name;
938 ELSE
939 l_users := l_users ||','||l_approver_table(l_index).name;
940 END IF;
941 ELSE
942 -- Add the user to list of invalid users
943 -- These users will be put on the notification sent
944 -- out to the AME administrator.
945 IF l_invalid_users IS NULL
946 THEN
947 l_invalid_users := l_approver_table(l_index).name;
948 ELSE
949 l_invalid_users := l_invalid_users ||', '||
950 l_approver_table(l_index).name;
951 END IF;
952
953 END IF;
954 END LOOP;
955
956 IF l_invalid_users IS NOT NULL
957 OR l_users IS NULL
958 THEN
959 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
960 THEN
961 fnd_log.string(FND_LOG.LEVEL_ERROR,
962 'fun.plsql.fun_wf_common.get_ame_role_list',
963 ' Invalid users found without access to org ' ||
964 'for transaction ' || p_transaction_id);
965 END IF;
966
967 -- Since we will be recalling AME after sysadmin
968 -- has fixed the error, we need to reset the approval
969 -- status within AME so that the same list of users
970 -- are returned again.
971 FOR l_index IN 1..l_approver_table.COUNT
972 LOOP
973 l_invalid_usr_rec := l_approver_table(l_index);
974 l_invalid_usr_rec.approval_status := NULL;
975
976 ame_api2.updateApprovalStatus(
977 applicationIdIn => 435,
978 transactionTypeIn => l_transaction_type,
979 transactionIdIn => p_transaction_id,
980 approverIn => l_invalid_usr_rec);
981 END LOOP;
982
983 x_return_status := FND_API.G_RET_STS_ERROR;
984 FND_MESSAGE.Set_Name('FUN','FUN_AME_INVALID_USERS');
985 FND_MESSAGE.Set_Token ('USER_LIST',l_invalid_users);
986 x_error_message := FND_MESSAGE.GET;
987
988 ELSE
989 -- Check if the ADHOC role already exists
990 IF l_role_exists = 'Y'
991 THEN
992 -- If the role exists, then empty the existing role list
993 wf_directory.RemoveUsersFromAdHocRole
994 (role_name => l_role_name,
995 role_users => NULL
996 );
997
998 -- Add the users we have identified to the role list.
999 wf_directory.AddUsersToAdHocRole
1000 (role_name => l_role_name,
1001 role_users => l_users);
1002
1003 ELSE
1004 -- Create an ADHOC role for the approver list and add the
1005 -- users.
1006 wf_directory.CreateAdHocRole
1007 (role_name => l_role_name,
1008 role_display_name => l_role_display,
1009 role_users => l_users,
1010 notification_preference =>'MAILHTML',
1011 expiration_date => NULL);
1012
1013 END IF; -- Check ADHOC role exists
1014 END IF; -- valid IC users found
1015
1016 x_role := l_role_name;
1017
1018 END IF; -- approvers found
1019
1020 EXCEPTION
1021
1022 WHEN OTHERS
1023 THEN
1024 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1025 THEN
1026 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
1027 'fun.plsql.fun_wf_common.get_ame_role_list',
1028 SQLERRM || ' Error occurred '||
1029 'for transaction ' || p_transaction_id);
1030 END IF;
1031
1032 FND_MESSAGE.Set_Name('FUN','FUN_AME_UNEXPECTED_EXCEPTION');
1033 x_error_message := FND_MESSAGE.GET;
1034 x_return_status := FND_API.G_RET_STS_ERROR;
1035
1036 END get_ame_role_list;
1037
1038 -- 6995183 START
1039 /* ---------------------------------------------------------------------------
1040 Name : validate_approver
1041 Pre-reqs : None.
1042 Modifies : None.
1043 Function : This function is called to check if the user is a valid approver before enabling
1044 the 'Approve' and 'Reject' button.
1045
1046 Parameters:
1047 IN : p_transaction_id - fun_trx_headers.trx_id
1048 OUT : Varchar2 - 'Y' implies user has access, 'N' means no access
1049 Notes : None.
1050 Testing : This function will be tested via the inbound trx UI
1051 ------------------------------------------------------------------------------*/
1052
1053 FUNCTION validate_approver (p_transaction_id IN VARCHAR2 )
1054 RETURN VARCHAR2 IS PRAGMA AUTONOMOUS_TRANSACTION;
1055 l_transaction_type VARCHAR2(30);
1056 l_approver_table ame_util.approversTable2;
1057 l_application_id NUMBER;
1058 l_approver_table1 ame_util.approversTable2;
1059 l_process_complete VARCHAR2(1);
1060 flag varchar2(1);
1061 groupNameOut ame_util.stringType;
1062
1063 BEGIN
1064 flag := 'Y';
1065 l_transaction_type := 'FUN_IC_RECI_TRX';
1066 l_application_id := 435;
1067
1068 ame_api2.getAllApprovers7 (
1069 applicationIdIn => 435,
1070 transactionTypeIn => 'FUN_IC_RECI_TRX',
1071 transactionIdIn => to_char(p_transaction_id),
1072 approvalProcessCompleteYNOut => l_process_complete ,
1073 ApproversOut => l_approver_table1);
1074 BEGIN
1075 FOR l_index IN 1..l_approver_table1.COUNT
1076 LOOP
1077 ame_api5.getApprovalGroupName
1078 (groupIdIn => l_approver_table1(l_index).group_or_chain_id,
1079 groupNameOut => groupNameOut);
1080
1081 IF groupNameOut <>'FUN Recipient Approval Group' then
1082 flag :='N';
1083 END IF;
1084
1085 END LOOP;
1086 IF flag ='Y' then
1087 COMMIT;
1088 RETURN 'Y';
1089 END IF ;
1090
1091
1092
1093 ame_api3.getOldApprovers (
1094 applicationIdIn => l_application_id,
1095 transactionIdIn => to_char(p_transaction_id),
1096 transactionTypeIn => l_transaction_type,
1097 oldApproversOut => l_approver_table);
1098
1099
1100 FOR l_index IN 1..l_approver_table.COUNT
1101 LOOP
1102 IF (l_approver_table(l_index).name = fnd_global.user_name AND l_approver_table(l_index).approval_status <> 'APPROVE') THEN
1103 COMMIT;
1104 RETURN 'Y';
1105 END IF;
1106 END LOOP;
1107 COMMIT;
1108 RETURN 'N';
1109 EXCEPTION
1110 WHEN OTHERS
1111 THEN
1112 ame_api3.getOldApprovers (
1113 applicationIdIn => l_application_id,
1114 transactionIdIn => to_char(p_transaction_id),
1115 transactionTypeIn => l_transaction_type,
1116 oldApproversOut => l_approver_table);
1117
1118
1119 FOR l_index IN 1..l_approver_table.COUNT
1120 LOOP
1121 IF (l_approver_table(l_index).name = fnd_global.user_name AND l_approver_table(l_index).approval_status <> 'APPROVE') THEN
1122 COMMIT;
1123 RETURN 'Y';
1124 END IF;
1125 END LOOP;
1126 COMMIT;
1127 RETURN 'N';
1128 END;
1129 EXCEPTION
1130 WHEN OTHERS
1131 THEN
1132 COMMIT;
1133 RETURN 'N';
1134 END validate_approver;
1135
1136 -- 6995183 END
1137
1138
1139 /* ---------------------------------------------------------------------------
1140 Name : is_user_valid_approver
1141 Pre-reqs : None.
1142 Modifies : None.
1143 Function : This function is called from the Inbound Transaction UI
1144 to check if the user is a valid approver before enabling
1145 the 'Approve' and 'Reject' button.
1146 This function is also called from within workflow to decide
1147 whether or not the user the notification is going to be sent to
1148 is a valid user or not.
1149 Parameters:
1150 IN : p_transaction_id - fun_trx_headers.trx_id
1151 p_user_id - fnd_user.userid of the person navigating
1152 to the Inbound Trx UI.
1153 p_role_name - wf_roles.name of the person the notification
1154 is being sent to or forwarded to.
1155 p_org_type = 'I' Initiating, 'R' Recipient.
1156 p_mode - UI - called from the UI
1157 WF - called from the workflow.
1158
1159 OUT : Varchar2 - 'Y' implies user has access, 'N' means no access
1160 Notes : None.
1161 Testing : This function will be tested via the inbound trx UI
1162 ------------------------------------------------------------------------------*/
1163 FUNCTION is_user_valid_approver (p_transaction_id IN VARCHAR2,
1164 p_user_id IN NUMBER,
1165 p_role_name IN VARCHAR2,
1166 p_org_type IN VARCHAR2,
1167 p_mode IN VARCHAR2)
1168 RETURN VARCHAR2 IS
1169
1170 CURSOR c_chk_user IS
1171 SELECT 'X'
1172 FROM fnd_user usr,
1173 WF_USER_ROLE_ASSIGNMENTS role
1174 WHERE usr.user_id = p_user_id
1175 AND usr.user_name = role.user_name
1176 AND role.role_name = 'FUN_ADHOC_RECI_'||p_transaction_id;
1177 -- Bug No: 5897122
1178 CURSOR c_chk_web IS
1179 SELECT 'X'
1180 FROM hz_parties p,
1181 fnd_user fu,
1182 fun_trx_headers ftrx,
1183 hz_relationships hzr,
1184 hz_org_contacts hc,
1185 hz_org_contact_roles hcr
1186 WHERE p.party_type = 'PERSON'
1187 AND p.party_id = hzr.subject_id
1188 AND hzr.object_id = ftrx.recipient_id
1189 AND hzr.relationship_code = 'CONTACT_OF'
1190 AND hzr.relationship_type = 'CONTACT'
1191 AND hzr.directional_flag = 'F'
1192 AND hzr.subject_table_name = 'HZ_PARTIES'
1193 AND hzr.object_table_name = 'HZ_PARTIES'
1194 AND hzr.subject_type = 'PERSON'
1195 AND hc.party_relationship_id = hzr.relationship_id
1196 AND hcr.org_contact_id = hc.org_contact_id
1197 AND hcr.role_type = 'INTERCOMPANY_CONTACT_FOR'
1198 AND fu.person_party_id = p.party_id
1199 AND sysdate BETWEEN
1200 nvl(hzr.start_date, sysdate -1)
1201 AND nvl(hzr.end_date, sysdate + 1)
1202 AND ftrx.trx_id = p_transaction_id;
1203 -- End: Bug No: 5897122
1204 CURSOR c_chk_role IS
1205 SELECT 'X'
1206 FROM fun_trx_headers trx,
1207 hz_relationships rel,
1208 fnd_user wf,
1209 hz_org_contacts c,
1210 hz_org_contact_roles cr
1211 WHERE wf.user_name = p_role_name
1212 AND wf.person_party_id = rel.subject_id
1213 AND trx.trx_id = p_transaction_id
1214 AND rel.object_id = DECODE(p_org_type, 'R',trx.recipient_id,
1215 'I',trx.initiator_id)
1216 AND rel.relationship_code = 'CONTACT_OF'
1217 AND rel.relationship_type = 'CONTACT'
1218 AND rel.directional_flag = 'F'
1219 AND rel.subject_table_name = 'HZ_PARTIES'
1220 AND rel.object_table_name = 'HZ_PARTIES'
1221 AND rel.subject_type = 'PERSON'
1222 AND SYSDATE BETWEEN NVL(rel.start_date, SYSDATE -1)
1223 AND NVL(rel.end_date, SYSDATE + 1)
1224 AND c.party_relationship_id = rel.relationship_id
1225 AND cr.org_contact_id = c.org_contact_id
1226 AND cr.role_type = 'INTERCOMPANY_CONTACT_FOR';
1227
1228 l_user_name fnd_user.user_name%TYPE;
1229
1230 BEGIN
1231 IF p_mode = 'UI'
1232 THEN
1233 -- Called from the inbound UI
1234 OPEN c_chk_user;
1235 FETCH c_chk_user INTO l_user_name;
1236
1237 IF c_chk_user%FOUND
1238 THEN
1239 CLOSE c_chk_user;
1240 RETURN 'Y';
1241 ELSE
1242 CLOSE c_chk_user;
1243 -- Bug No: 5897122
1244 OPEN c_chk_web;
1245 FETCH c_chk_web INTO l_user_name;
1246
1247 IF c_chk_web%FOUND
1248 THEN
1249 CLOSE c_chk_web;
1250 RETURN 'Y';
1251 ELSE
1252 CLOSE c_chk_web;
1253 RETURN 'N';
1254 END IF;
1255 --End Bug No: 5897122
1256 END IF;
1257 ELSIF p_mode = 'WF'
1258 THEN
1259 -- Called from the workflow
1260 OPEN c_chk_role;
1261 FETCH c_chk_role INTO l_user_name;
1262
1263 IF c_chk_role%FOUND
1264 THEN
1265 CLOSE c_chk_role;
1266 RETURN 'Y';
1267 ELSE
1268 CLOSE c_chk_role;
1269 RETURN 'N';
1270 END IF;
1271 END IF;
1272
1273 EXCEPTION
1274 WHEN OTHERS
1275 THEN
1276 RETURN 'N';
1277 END is_user_valid_approver;
1278
1279
1280 PROCEDURE set_invoice_reqd_flag(p_batch_id IN NUMBER,
1281 x_return_status OUT NOCOPY VARCHAR2)
1282 IS
1283
1284 CURSOR c_trx (p_batch_id NUMBER)
1285 IS
1286 SELECT trx_id,
1287 recipient_id,
1288 to_le_id
1289 FROM fun_trx_headers
1290 WHERE batch_id = p_batch_id;
1291
1292 l_initiator_id NUMBER;
1293 l_ini_le_id NUMBER;
1294 l_trx_invoice_flag VARCHAR2(1);
1295 l_ini_invoice_flag VARCHAR2(1);
1296 l_rec_invoice_flag VARCHAR2(1);
1297 l_return_status VARCHAR2(1);
1298 l_le_error VARCHAR2(2000);
1299
1300 BEGIN
1301 x_return_status := FND_API.G_RET_STS_SUCCESS;
1302
1303 SELECT b.initiator_id,
1304 b.from_le_id,
1305 Nvl(t.allow_invoicing_flag, 'N')
1306 INTO l_initiator_id,
1307 l_ini_le_id,
1308 l_trx_invoice_flag
1309 FROM fun_trx_batches b,
1310 fun_trx_types_vl t
1311 WHERE b.batch_id = p_batch_id
1312 AND b.trx_type_id = t.trx_type_id;
1313
1314 IF l_trx_invoice_flag = 'N'
1315 THEN
1316 -- Check if initiator requires invoicing
1317 XLE_UTILITIES_GRP. Check_IC_Invoice_required(
1318 x_return_status => l_return_status,
1319 x_msg_data => l_le_error,
1320 p_legal_entity_id => l_ini_le_id,
1321 p_party_id => l_initiator_id,
1322 x_intercompany_inv => l_ini_invoice_flag);
1323
1324 IF l_ini_invoice_flag = FND_API.G_TRUE
1325 THEN
1326 -- invoicing is required for the initator and therefore
1327 -- required for all recipients.
1328 UPDATE fun_trx_headers
1329 SET invoice_flag = 'Y'
1330 WHERE batch_id = p_batch_id;
1331 ELSE
1332 -- check if invoice is required for the recipient
1333 FOR l_trx_rec IN c_trx (p_batch_id)
1334 LOOP
1335 -- Check if initiator requires invoicing
1336 XLE_UTILITIES_GRP. Check_IC_Invoice_required(
1337 x_return_status => l_return_status,
1338 x_msg_data => l_le_error,
1339 p_legal_entity_id => l_trx_rec.to_le_id,
1340 p_party_id => l_trx_rec.recipient_id,
1341 x_intercompany_inv => l_rec_invoice_flag);
1342
1343 IF l_rec_invoice_flag = FND_API.G_TRUE
1344 THEN
1345 -- invoicing is required for the recipient
1346 UPDATE fun_trx_headers
1347 SET invoice_flag = 'Y'
1348 WHERE trx_id = l_trx_rec.trx_id;
1349 ELSE
1350 -- invoicing is not required for the recipient
1351 UPDATE fun_trx_headers
1352 SET invoice_flag = 'N'
1353 WHERE trx_id = l_trx_rec.trx_id;
1354 END IF; -- invoicing required for recipient
1355 END LOOP;
1356
1357 END IF; -- invoicing enabled for inititator
1358 ELSE
1359 -- invoicing is required for this transaction
1360 UPDATE fun_trx_headers
1361 SET invoice_flag = 'Y'
1362 WHERE batch_id = p_batch_id;
1363
1364 END IF; -- invoicing enabled for trx type
1365
1366 COMMIT; -- commit here before starting further wf processing
1367
1368 EXCEPTION
1369 WHEN OTHERS
1370 THEN
1371 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1372 THEN
1373 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
1374 'fun.plsql.fun_wf_common.set_invoice_reqd_flag',
1375 SQLERRM || ' Error occurred '||
1376 'for batch ' || p_batch_id);
1377 END IF;
1378
1379 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1380
1381 END set_invoice_reqd_flag;
1382
1383 PROCEDURE wf_abort (p_batch_id IN NUMBER,
1384 p_trx_id IN NUMBER)
1385 IS
1386
1387 cursor batch_abort is
1388 select distinct item_type,item_key
1389 from WF_ITEM_ACTIVITY_STATUSES
1390 where item_type in ('FUNRMAIN','FUNIMAIN','FUNRTVAL','FUNMSYST')
1391 and item_key like to_char(p_batch_id)||'%'
1392 and activity_status in( 'ACTIVE');
1393
1394 cursor trx_abort is
1395 select distinct item_type,item_key
1396 from WF_ITEM_ACTIVITY_STATUSES
1397 where item_type in ('FUNRMAIN','FUNIMAIN','FUNRTVAL','FUNMSYST')
1398 and item_key like to_char(p_batch_id)||'_'||to_char(p_trx_id)||'%'
1399 and activity_status in( 'ACTIVE');
1400
1401 BEGIN
1402
1403 if p_trx_id is NULL then
1404 for wf_abort in batch_abort
1405 loop
1406
1407 wf_engine.AbortProcess
1408 (itemtype =>wf_abort.item_type,
1409 itemkey => wf_abort.item_key
1410 );
1411
1412 end loop;
1413 else
1414 for wf_trx_abort in trx_abort
1415 loop
1416 wf_engine.AbortProcess
1417 (itemtype =>wf_trx_abort.item_type,
1418 itemkey => wf_trx_abort.item_key
1419 );
1420
1421 end loop;
1422
1423 end if;
1424 COMMIT;
1425
1426 EXCEPTION
1427 WHEN NO_DATA_FOUND THEN
1428 null;
1429 WHEN OTHERS THEN
1430 RAISE;
1431
1432 END wf_abort;
1433
1434 --Bug: 12703241.
1435
1436 PROCEDURE wf_abort (p_batch_id IN NUMBER,
1437 p_trx_id IN NUMBER,
1438 p_item_type IN VARCHAR2)
1439 IS
1440
1441 cursor trx_abort is
1442 select distinct item_type,item_key
1443 from WF_ITEM_ACTIVITY_STATUSES
1444 where item_type = p_item_type
1445 and item_key like to_char(p_batch_id)||'_'||to_char(p_trx_id)||'%'
1446 and activity_status in('ACTIVE');
1447
1448 BEGIN
1449
1450 for wf_trx_abort in trx_abort
1451 loop
1452 wf_engine.AbortProcess
1453 (itemtype =>wf_trx_abort.item_type,
1454 itemkey => wf_trx_abort.item_key
1455 );
1456 end loop;
1457
1458 COMMIT;
1459
1460 EXCEPTION
1461 WHEN NO_DATA_FOUND THEN
1462 null;
1463 WHEN OTHERS THEN
1464 RAISE;
1465
1466 END wf_abort;
1467
1468 END FUN_WF_COMMON;