[Home] [Help]
PACKAGE BODY: APPS.FUN_WF_COMMON
Source
1 PACKAGE BODY FUN_WF_COMMON AS
2 /* $Header: FUN_WF_COMMON_B.pls 120.24.12010000.2 2009/01/15 10:33:50 srampure 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 CURSOR c_trx ( p_batch_id NUMBER) IS
361 SELECT trx_id
362 FROM fun_trx_headers
363 WHERE batch_id = p_batch_id;
364
365 begin
366 --Bug: 7639191
367 l_user_id := fnd_global.user_id;
368 l_resp_id := fnd_global.resp_id;
369 l_appl_id := fnd_global.resp_appl_id;
370
371 FND_GLOBAL.APPS_INITIALIZE(l_user_id,l_resp_id,l_appl_id);
372
373 WF_EVENT.AddParameterToList(p_name=>'BATCH_ID',
374 p_value=>TO_CHAR(batch_id),
375 p_parameterlist=>l_parameter_list);
376 WF_EVENT.AddParameterToList(p_name=>'RESP_ID',
377 p_value=>TO_CHAR(l_resp_id),
378 p_parameterlist=>l_parameter_list);
379 WF_EVENT.AddParameterToList(p_name=>'USER_ID',
380 p_value=>TO_CHAR(l_user_id),
381 p_parameterlist=>l_parameter_list);
382 WF_EVENT.AddParameterToList(p_name=>'APPL_ID',
383 p_value=>TO_CHAR(l_appl_id),
384 p_parameterlist=>l_parameter_list);
385 IF trx_id is not null then
386 WF_EVENT.AddParameterToList(p_name=>'TRX_ID',
387 p_value=>TO_CHAR(trx_id),
388 p_parameterlist=>l_parameter_list);
389 END IF;
390
391 /* Start of changes for AME Uptake, 3671923. 11 Oct 2004 */
392 IF (event_name IS NULL
393 OR event_name = 'oracle.apps.fun.manualtrx.batch.send')
394 THEN
395 IF trx_id IS NOT NULL
396 THEN
397 -- Initialize the AME Approval Process
398 ame_api2.clearallapprovals(applicationIdIn => 435,
399 transactionTypeIn => 'FUN_IC_RECI_TRX',
400 transactionIdIn => trx_id);
401 ELSE
402 FOR l_trx IN c_trx(batch_id)
403 LOOP
404 -- Initialize the AME Approval Process
405 ame_api2.clearallapprovals(applicationIdIn => 435,
406 transactionTypeIn => 'FUN_IC_RECI_TRX',
407 transactionIdIn => l_trx.trx_id);
408
409 END LOOP;
410
411 END IF;
412 END IF;
413
414 /* End of changes for AME Uptake, 3671923. 11 Oct 2004 */
415
416 IF event_name is not null then
417 WF_EVENT.RAISE(p_event_name =>event_name,
418 p_event_key =>nvl(event_key,'Test '||batch_id),
419 p_parameters =>l_parameter_list);
420 ELSE
421 WF_EVENT.RAISE(p_event_name =>'oracle.apps.fun.manualtrx.batch.send',
422 p_event_key =>nvl(event_key,'Test '||batch_id),
423 p_parameters =>l_parameter_list);
424 END IF;
425 END raise_wf_bus_event;
426
427
428 /* Start of changes for AME Uptake, 3671923. 07 Jun 2004 */
429
430 /* ---------------------------------------------------------------------------
431 Name : get_ame_contacts
432 Pre-reqs : None.
433 Modifies : None.
434 Function : This function is called by the various intercompany workflows
435 to get the contact list to whom FYI notifications need to be
436 sent out.
437 Parameters:
438 IN : itemtype - Workflow Item Type
439 itemkey - Workflow Item Key
440 actid - Workflow Activity Id
441 funcmode - Workflow Function Mode
442 OUT : resultout - Result of the workflow function
443 'Y' indicates contacts were found, 'N' indicates no contacts
444 Notes : None.
445 Testing : This function will be tested via workflows FUNARINT, FUNAPINT,
446 FUNGLINT, FUNRTVAL, FUNIMAIN
447 ------------------------------------------------------------------------------*/
448 PROCEDURE get_ame_contacts (itemtype IN VARCHAR2,
449 itemkey IN VARCHAR2,
450 actid IN NUMBER,
451 funcmode IN VARCHAR2,
452 resultout OUT NOCOPY VARCHAR2 )
453 IS
454 l_approvers_found VARCHAR2(1);
455 l_process_complete VARCHAR2(1);
456 l_transaction_id NUMBER;
457 l_role_name VARCHAR2(30);
458 l_contact_type VARCHAR2(1);
459 l_return_status VARCHAR2(1);
460 l_ame_admin_user VARCHAR2(30);
461 l_error_message VARCHAR2(2000);
462
463 BEGIN
464
465 l_transaction_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
466 itemkey => itemkey,
467 aname => 'TRX_ID');
468
469 l_contact_type := wf_engine.GetActivityAttrText(itemtype => itemtype,
470 itemkey => itemkey,
471 actid => actid,
472 aname => 'CONTACT_ORG_TYPE');
473
474 -- Call the procedure which will return the AME role
475 fun_wf_common.get_ame_role_list
476 (itemkey => itemkey,
477 p_transaction_id => l_transaction_id,
478 p_fyi_notification => 'Y',
479 p_contact_type => l_contact_type,
480 x_approvers_found => l_approvers_found,
481 x_process_complete => l_process_complete,
482 x_role => l_role_name,
483 x_return_status => l_return_status,
484 x_ame_admin_user => l_ame_admin_user,
485 x_error_message => l_error_message);
486
487 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
488 THEN
489 wf_engine.SetItemAttrText(itemtype => itemtype,
490 itemkey => itemkey,
491 aname => 'AME_ADMIN_USER',
492 avalue => l_ame_admin_user);
493
494 wf_engine.SetItemAttrText(itemtype => itemtype,
495 itemkey => itemkey,
496 aname => 'AME_ERROR',
497 avalue => l_error_message);
498
499 resultout := wf_engine.eng_completed||':'||'ERROR';
500 ELSIF l_approvers_found = 'N'
501 THEN
502 resultout := wf_engine.eng_completed||':'||'NO';
503 ELSIF l_approvers_found = 'Y'
504 THEN
505 -- Set the workflow attribute for CONTACT
506 wf_engine.SetItemAttrText(itemtype => itemtype,
507 itemkey => itemkey,
508 aname => 'CONTACT',
509 avalue => l_role_name);
510
511 resultout := wf_engine.eng_completed||':'||'YES';
512 END IF;
513
514 EXCEPTION
515 WHEN OTHERS
516 THEN
517 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
518 THEN
519 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
520 'fun.plsql.fun_wf_common.get_ame_contacts',
521 SQLERRM || ' Error occurred '||
522 ' for transaction ' || l_transaction_id);
523 END IF;
524
525 wf_core.context('FUN_WF_COMMON', 'GET_AME_CONTACTS',
526 itemtype, itemkey, TO_CHAR(actid), funcmode);
527 END get_ame_contacts;
528
529 /* ---------------------------------------------------------------------------
530 Name : get_ame_approvers
531 Pre-reqs : None.
532 Modifies : None.
533 Function : This function is called by the Recipient Main workflow
534 to get the contact list to whom request for approval notfications
535 are sent out.
536 Parameters:
537 IN : itemtype - Workflow Item Type
538 itemkey - Workflow Item Key
539 actid - Workflow Activity Id
540 funcmode - Workflow Function Mode
541 OUT : resultout - Result of the workflow function
542 'NOTIFY' indicates approvers were found,
543 'COMPLETE' indicates no more approvals required - process is complete
544 'ERROR' indicates there was an error
545 'WAIT' indicates we are waiting for some approver to respond to notification
546 Notes : None.
547 Testing : This function will be tested via workflow FUNRMAIN
548 ------------------------------------------------------------------------------*/
549 PROCEDURE get_ame_approvers (itemtype IN VARCHAR2,
550 itemkey IN VARCHAR2,
551 actid IN NUMBER,
552 funcmode IN VARCHAR2,
553 resultout OUT NOCOPY VARCHAR2 )
554 IS
555 l_approvers_found VARCHAR2(1);
556 l_process_complete VARCHAR2(1);
557 l_transaction_id NUMBER;
558 l_role_name VARCHAR2(30);
559 l_contact_type VARCHAR2(1);
560 l_trx_status fun_trx_headers.status%TYPE;
561 l_return_status VARCHAR2(1);
562 l_ame_admin_user VARCHAR2(30);
563 l_error_message VARCHAR2(2000);
564
565
566 BEGIN
567
568 l_transaction_id := wf_engine.GetItemAttrNumber (itemtype => itemtype,
569 itemkey => itemkey,
570 aname => 'TRX_ID');
571
572 l_contact_type := wf_engine.GetActivityAttrText(itemtype => itemtype,
573 itemkey => itemkey,
574 actid => actid,
575 aname => 'CONTACT_ORG_TYPE');
576
577 -- Initialize the UI Action and Approver name workflow attributes.
578 -- They will get set if and when user takes some approval action
579 -- from the UI
580 wf_engine.SetItemAttrText
581 (itemtype => 'FUNRMAIN',
582 itemkey => itemkey,
583 aname => 'UI_ACTION_TYPE',
584 avalue => 'NONE');
585
586 wf_engine.SetItemAttrText
587 (itemtype => 'FUNRMAIN',
588 itemkey => itemkey,
589 aname => 'UI_ACTION_USER_NAME',
590 avalue => NULL);
591
592 wf_engine.SetItemAttrNumber
593 (itemtype => 'FUNRMAIN',
594 itemkey => itemkey,
595 aname => 'UI_ACTION_USER_ID',
596 avalue => NULL);
597
598 -- Call the procedure which will return the AME role list
599 fun_wf_common.get_ame_role_list
600 (itemkey => itemkey,
601 p_transaction_id => l_transaction_id,
602 p_fyi_notification => 'N',
603 p_contact_type => l_contact_type,
604 x_approvers_found => l_approvers_found,
605 x_process_complete => l_process_complete,
606 x_role => l_role_name,
607 x_return_status => l_return_status ,
608 x_ame_admin_user => l_ame_admin_user,
609 x_error_message => l_error_message);
610
611 -- Check if Approval process is complete or was there an error
612 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
613 THEN
614 wf_engine.SetItemAttrText(itemtype => itemtype,
615 itemkey => itemkey,
616 aname => 'AME_ADMIN_USER',
617 avalue => l_ame_admin_user);
618
619 wf_engine.SetItemAttrText(itemtype => itemtype,
620 itemkey => itemkey,
621 aname => 'AME_ERROR',
622 avalue => l_error_message);
623
624 resultout := wf_engine.eng_completed||':'||'ERROR';
625 ELSIF l_process_complete = 'Y'
626 THEN
627 resultout := wf_engine.eng_completed||':'||'COMPLETE';
628 ELSIF (l_approvers_found = 'N' AND l_process_complete = 'N')
629 THEN
630 resultout := wf_engine.eng_completed||':'||'WAIT';
631 ELSIF l_approvers_found = 'Y'
632 THEN
633 wf_engine.SetItemAttrText(itemtype => itemtype,
634 itemkey => itemkey,
635 aname => 'CONTACT',
636 avalue => l_role_name);
637
638 resultout := wf_engine.eng_completed||':'||'NOTIFY';
639 END IF;
640
641 EXCEPTION
642 WHEN OTHERS
643 THEN
644 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
645 THEN
646 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
647 'fun.plsql.fun_wf_common.get_ame_approvers',
648 SQLERRM || ' Error occurred '||
649 'for transaction ' || l_transaction_id);
650 END IF;
651
652 wf_core.context('FUN_WF_COMMON', 'GET_AME_APPROVERS',
653 itemtype, itemkey, TO_CHAR(actid), funcmode);
654 END get_ame_approvers;
655
656
657 /* ---------------------------------------------------------------------------
658 Name : get_ame_role_list
659 Pre-reqs : None.
660 Modifies : None.
661 Function : This function is called by get_ame_contacts() and
662 get_ame_approvers functions. It returns the name of the wflow
663 role to whom FYI or approval notifications are sent out.
664 Parameters:
665 IN : p_transaction_id - fun_trx_headers.trx_id
666 p_fyi_notification - 'Y' or 'N' indicating if its FYI notification
667 p_contact_type - 'I'- Initiator or 'R'- Recipient
668 OUT : x_approvers_found - 'Y'or 'N' indicating approvers found
669 x_process_complete - 'Y'or 'N' indicating process complete
670 x_role - workflow role name
671 x_return_status - Return Status.
672 x_ame_admin_user - Ame Administrator
673 x_error_message - Error Message
674 Notes : None.
675 Testing : This function will be tested via the various intercompany
676 workflows
677 ------------------------------------------------------------------------------*/
678 PROCEDURE get_ame_role_list(itemkey IN VARCHAR2,
679 p_transaction_id IN NUMBER,
680 p_fyi_notification IN VARCHAR2,
681 p_contact_type IN VARCHAR2,
682 x_approvers_found OUT NOCOPY VARCHAR2,
683 x_process_complete OUT NOCOPY VARCHAR2,
684 x_role OUT NOCOPY VARCHAR2,
685 x_return_status OUT NOCOPY VARCHAR2,
686 x_ame_admin_user OUT NOCOPY VARCHAR2,
687 x_error_message OUT NOCOPY VARCHAR2)
688
689 IS
690 l_approver_table ame_util.approversTable2;
691 l_invalid_usr_rec ame_util.approverRecord2;
692 l_ame_admin_rec ame_util.approverRecord2;
693 l_process_complete VARCHAR2(1);
694 l_party_id NUMBER;
695 l_index NUMBER;
696 l_role_name VARCHAR2(30) ;
697 l_role_display VARCHAR2(60) ;
698 l_exp_date DATE ;
699 l_role_exists VARCHAR2(1) ;
700 l_transaction_type VARCHAR2(30);
701 l_users VARCHAR2(2000);
702 l_invalid_users VARCHAR2(2000);
703 l_valid_user VARCHAR2(1);
704
705
706 CURSOR c_chk_wf_role (p_role_name VARCHAR2) IS
707 SELECT 'Y'
708 FROM wf_local_roles
709 WHERE name = p_role_name;
710
711 CURSOR c_get_orgname (p_transaction_id NUMBER,
712 p_contact_type VARCHAR2) IS
713 SELECT hzp.party_name
714 FROM hz_parties hzp,
715 fun_trx_headers trx
716 WHERE hzp.party_id = DECODE(p_contact_type,'I',trx.initiator_id,
717 trx.recipient_id)
718 AND trx.trx_id = p_transaction_id;
719
720 BEGIN
721
722 OPEN c_get_orgname (p_transaction_id, p_contact_type);
723 FETCH c_get_orgname INTO l_role_display;
724 CLOSE c_get_orgname;
725
726 IF p_contact_type = 'I'
727 THEN
728 -- We need the contact list for the initiating organization
729 l_transaction_type := 'FUN_IC_INIT_TRX';
730 l_role_name:='FUN_ADHOC_INIT_'||p_transaction_id;
731 ELSE
732 -- We need the contact list for the recipient organization
733 l_transaction_type := 'FUN_IC_RECI_TRX';
734 l_role_name:='FUN_ADHOC_RECI_'||p_transaction_id;
735 END IF;
736
737 l_exp_date := SYSDATE + 1;
738 l_role_exists:='N';
739 x_return_status := FND_API.G_RET_STS_SUCCESS;
740
741 -- Check if the ADHOC role already exists
742 OPEN c_chk_wf_role(l_role_name);
743 FETCH c_chk_wf_role INTO l_role_exists;
744 CLOSE c_chk_wf_role;
745
746 -- Get the AME Admin Approver
747 ame_api2.getAdminApprover(
748 applicationIdIn => 435,
749 transactionTypeIn => l_transaction_type,
750 adminApproverOut => l_ame_admin_rec);
751
752 x_ame_admin_user := l_ame_admin_rec.name;
753
754 IF p_fyi_notification = 'Y'
755 THEN
756 -- Call the AME API to get the list of ALL approvers
757 ame_api2.getAllApprovers7 (
758 applicationIdIn => 435,
759 transactionTypeIn => l_transaction_type,
760 transactionIdIn => to_char(p_transaction_id),
761 approvalProcessCompleteYNOut => l_process_complete,
762 ApproversOut => l_approver_table);
763
764 ELSE
765 -- Call the AME API to get the list of next approver
766 ame_api2.getNextApprovers4 (
767 applicationIdIn => 435,
768 transactionTypeIn => l_transaction_type,
769 transactionIdIn => to_char(p_transaction_id),
770 approvalProcessCompleteYNOut => l_process_complete,
771 nextApproversOut => l_approver_table);
772
773 END IF;
774
775 -- Check if AME encountered any errors.
776 IF l_approver_table.COUNT > 0
777 AND l_approver_table(1).approval_status = ame_util.exceptionStatus
778 THEN
779 -- Ame had an exception whilst generating the approver
780 -- list.
781 x_return_status := FND_API.G_RET_STS_ERROR;
782 FND_MESSAGE.Set_Name('FUN','FUN_AME_EXECUTION_EXCEPTION');
783 x_error_message := FND_MESSAGE.GET;
784
785 RETURN;
786 END IF;
787
788 -- Check approval status etc ...
789 IF l_process_complete = ame_util.booleanTrue
790 THEN
791 x_process_complete := 'Y';
792 ELSE
793 IF l_approver_table.COUNT = 0
794 THEN
795 -- This indicates we are still waiting for response from a few approvers
796 x_approvers_found := 'N';
797 --Bug No:5897122
798 x_process_complete := 'Y';
799 ELSE
800 -- This indicates there are still approvers to be notified.
801 x_approvers_found := 'Y';
802 x_process_complete := 'N';
803 END IF;
804 END IF;
805
806 -- If the approval process is not over or if this is an FYI
807 -- notification, build the list of resources to send out the
808 -- notifications
809 If x_approvers_found = 'Y' OR p_fyi_notification = 'Y'
810 THEN
811 -- Check that the list of approvers returned are all authorised
812 -- to view the intercompany transaction.
813
814 -- The security model is undergoing changes as per ER 3358579
815 -- Once the technical design for the ER has been finalized,
816 -- this section will be updated with the correct
817 -- validation (see open issues 2)
818
819 FOR l_index IN 1..l_approver_table.COUNT
820 LOOP
821 -- Check user has access to the transaction
822 IF p_fyi_notification = 'Y'
823 THEN
824 -- Check if user has Update, View or Contact access.
825 NULL;
826 ELSE
827 -- Check if user has Update access.
828 NULL;
829 END IF;
830
831 -- For now, call is_valid_approver procedure
832 l_valid_user := fun_wf_common.is_user_valid_approver
833 (p_transaction_id => p_transaction_id,
834 p_user_id => NULL,
835 p_role_name => l_approver_table(l_index).name,
836 p_org_type => p_contact_type,
837 p_mode => 'WF');
838
839 IF l_valid_user = 'Y'
840 THEN
841 -- Add the user to list of users who can approve the
842 -- document.
843 IF l_users IS NULL
844 THEN
845 l_users := l_approver_table(l_index).name;
846 ELSE
847 l_users := l_users ||','||l_approver_table(l_index).name;
848 END IF;
849 ELSE
850 -- Add the user to list of invalid users
851 -- These users will be put on the notification sent
852 -- out to the AME administrator.
853 IF l_invalid_users IS NULL
854 THEN
855 l_invalid_users := l_approver_table(l_index).name;
856 ELSE
857 l_invalid_users := l_invalid_users ||', '||
858 l_approver_table(l_index).name;
859 END IF;
860
861 END IF;
862 END LOOP;
863
864 IF l_invalid_users IS NOT NULL
865 OR l_users IS NULL
866 THEN
867 IF ( FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
868 THEN
869 fnd_log.string(FND_LOG.LEVEL_ERROR,
870 'fun.plsql.fun_wf_common.get_ame_role_list',
871 ' Invalid users found without access to org ' ||
872 'for transaction ' || p_transaction_id);
873 END IF;
874
875 -- Since we will be recalling AME after sysadmin
876 -- has fixed the error, we need to reset the approval
877 -- status within AME so that the same list of users
878 -- are returned again.
879 FOR l_index IN 1..l_approver_table.COUNT
880 LOOP
881 l_invalid_usr_rec := l_approver_table(l_index);
882 l_invalid_usr_rec.approval_status := NULL;
883
884 ame_api2.updateApprovalStatus(
885 applicationIdIn => 435,
886 transactionTypeIn => l_transaction_type,
887 transactionIdIn => p_transaction_id,
888 approverIn => l_invalid_usr_rec);
889 END LOOP;
890
891 x_return_status := FND_API.G_RET_STS_ERROR;
892 FND_MESSAGE.Set_Name('FUN','FUN_AME_INVALID_USERS');
893 FND_MESSAGE.Set_Token ('USER_LIST',l_invalid_users);
894 x_error_message := FND_MESSAGE.GET;
895
896 ELSE
897 -- Check if the ADHOC role already exists
898 IF l_role_exists = 'Y'
899 THEN
900 -- If the role exists, then empty the existing role list
901 wf_directory.RemoveUsersFromAdHocRole
902 (role_name => l_role_name,
903 role_users => NULL);
904
905 -- Add the users we have identified to the role list.
906 wf_directory.AddUsersToAdHocRole
907 (role_name => l_role_name,
908 role_users => l_users);
909
910 ELSE
911 -- Create an ADHOC role for the approver list and add the
912 -- users.
913 wf_directory.CreateAdHocRole
914 (role_name => l_role_name,
915 role_display_name => l_role_display,
916 role_users => l_users,
917 expiration_date => NULL);
918
919 END IF; -- Check ADHOC role exists
920 END IF; -- valid IC users found
921
922 x_role := l_role_name;
923
924 END IF; -- approvers found
925
926 EXCEPTION
927
928 WHEN OTHERS
929 THEN
930 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
931 THEN
932 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
933 'fun.plsql.fun_wf_common.get_ame_role_list',
934 SQLERRM || ' Error occurred '||
935 'for transaction ' || p_transaction_id);
936 END IF;
937
938 FND_MESSAGE.Set_Name('FUN','FUN_AME_UNEXPECTED_EXCEPTION');
939 x_error_message := FND_MESSAGE.GET;
940 x_return_status := FND_API.G_RET_STS_ERROR;
941
942 END get_ame_role_list;
943
944
945 /* ---------------------------------------------------------------------------
946 Name : is_user_valid_approver
947 Pre-reqs : None.
948 Modifies : None.
949 Function : This function is called from the Inbound Transaction UI
950 to check if the user is a valid approver before enabling
951 the 'Approve' and 'Reject' button.
952 This function is also called from within workflow to decide
953 whether or not the user the notification is going to be sent to
954 is a valid user or not.
955 Parameters:
956 IN : p_transaction_id - fun_trx_headers.trx_id
957 p_user_id - fnd_user.userid of the person navigating
958 to the Inbound Trx UI.
959 p_role_name - wf_roles.name of the person the notification
960 is being sent to or forwarded to.
961 p_org_type = 'I' Initiating, 'R' Recipient.
962 p_mode - UI - called from the UI
963 WF - called from the workflow.
964
965 OUT : Varchar2 - 'Y' implies user has access, 'N' means no access
966 Notes : None.
967 Testing : This function will be tested via the inbound trx UI
968 ------------------------------------------------------------------------------*/
969 FUNCTION is_user_valid_approver (p_transaction_id IN VARCHAR2,
970 p_user_id IN NUMBER,
971 p_role_name IN VARCHAR2,
972 p_org_type IN VARCHAR2,
973 p_mode IN VARCHAR2)
974 RETURN VARCHAR2 IS
975
976 CURSOR c_chk_user IS
977 SELECT 'X'
978 FROM fnd_user usr,
979 wf_local_user_roles role
980 WHERE usr.user_id = p_user_id
981 AND usr.user_name = role.user_name
982 AND role.role_name = 'FUN_ADHOC_RECI_'||p_transaction_id;
983 -- Bug No: 5897122
984 CURSOR c_chk_web IS
985 SELECT 'X'
986 FROM hz_parties p,
987 fnd_user fu,
988 fun_trx_headers ftrx,
989 hz_relationships hzr,
990 hz_org_contacts hc,
991 hz_org_contact_roles hcr
992 WHERE p.party_type = 'PERSON'
993 AND p.party_id = hzr.subject_id
994 AND hzr.object_id = ftrx.recipient_id
995 AND hzr.relationship_code = 'CONTACT_OF'
996 AND hzr.relationship_type = 'CONTACT'
997 AND hzr.directional_flag = 'F'
998 AND hzr.subject_table_name = 'HZ_PARTIES'
999 AND hzr.object_table_name = 'HZ_PARTIES'
1000 AND hzr.subject_type = 'PERSON'
1001 AND hc.party_relationship_id = hzr.relationship_id
1002 AND hcr.org_contact_id = hc.org_contact_id
1003 AND hcr.role_type = 'INTERCOMPANY_CONTACT_FOR'
1004 AND fu.person_party_id = p.party_id
1005 AND sysdate BETWEEN
1006 nvl(hzr.start_date, sysdate -1)
1007 AND nvl(hzr.end_date, sysdate + 1)
1008 AND ftrx.trx_id = p_transaction_id;
1009 -- End: Bug No: 5897122
1010 CURSOR c_chk_role IS
1011 SELECT 'X'
1012 FROM fun_trx_headers trx,
1013 hz_relationships rel,
1014 fnd_user wf,
1015 hz_org_contacts c,
1016 hz_org_contact_roles cr
1017 WHERE wf.user_name = p_role_name
1018 AND wf.person_party_id = rel.subject_id
1019 AND trx.trx_id = p_transaction_id
1020 AND rel.object_id = DECODE(p_org_type, 'R',trx.recipient_id,
1021 'I',trx.initiator_id)
1022 AND rel.relationship_code = 'CONTACT_OF'
1023 AND rel.relationship_type = 'CONTACT'
1024 AND rel.directional_flag = 'F'
1025 AND rel.subject_table_name = 'HZ_PARTIES'
1026 AND rel.object_table_name = 'HZ_PARTIES'
1027 AND rel.subject_type = 'PERSON'
1028 AND SYSDATE BETWEEN NVL(rel.start_date, SYSDATE -1)
1029 AND NVL(rel.end_date, SYSDATE + 1)
1030 AND c.party_relationship_id = rel.relationship_id
1031 AND cr.org_contact_id = c.org_contact_id
1032 AND cr.role_type = 'INTERCOMPANY_CONTACT_FOR';
1033
1034 l_user_name fnd_user.user_name%TYPE;
1035
1036 BEGIN
1037 IF p_mode = 'UI'
1038 THEN
1039 -- Called from the inbound UI
1040 OPEN c_chk_user;
1041 FETCH c_chk_user INTO l_user_name;
1042
1043 IF c_chk_user%FOUND
1044 THEN
1045 CLOSE c_chk_user;
1046 RETURN 'Y';
1047 ELSE
1048 CLOSE c_chk_user;
1049 -- Bug No: 5897122
1050 OPEN c_chk_web;
1051 FETCH c_chk_web INTO l_user_name;
1052
1053 IF c_chk_web%FOUND
1054 THEN
1055 CLOSE c_chk_web;
1056 RETURN 'Y';
1057 ELSE
1058 CLOSE c_chk_web;
1059 RETURN 'N';
1060 END IF;
1061 --End Bug No: 5897122
1062 END IF;
1063 ELSIF p_mode = 'WF'
1064 THEN
1065 -- Called from the workflow
1066 OPEN c_chk_role;
1067 FETCH c_chk_role INTO l_user_name;
1068
1069 IF c_chk_role%FOUND
1070 THEN
1071 CLOSE c_chk_role;
1072 RETURN 'Y';
1073 ELSE
1074 CLOSE c_chk_role;
1075 RETURN 'N';
1076 END IF;
1077 END IF;
1078
1079 EXCEPTION
1080 WHEN OTHERS
1081 THEN
1082 RETURN 'N';
1083 END is_user_valid_approver;
1084
1085
1086 PROCEDURE set_invoice_reqd_flag(p_batch_id IN NUMBER,
1087 x_return_status OUT NOCOPY VARCHAR2)
1088 IS
1089
1090 CURSOR c_trx (p_batch_id NUMBER)
1091 IS
1092 SELECT trx_id,
1093 recipient_id,
1094 to_le_id
1095 FROM fun_trx_headers
1096 WHERE batch_id = p_batch_id;
1097
1098 l_initiator_id NUMBER;
1099 l_ini_le_id NUMBER;
1100 l_trx_invoice_flag VARCHAR2(1);
1101 l_ini_invoice_flag VARCHAR2(1);
1102 l_rec_invoice_flag VARCHAR2(1);
1103 l_return_status VARCHAR2(1);
1104 l_le_error VARCHAR2(2000);
1105
1106 BEGIN
1107 x_return_status := FND_API.G_RET_STS_SUCCESS;
1108
1109 SELECT b.initiator_id,
1110 b.from_le_id,
1111 Nvl(t.allow_invoicing_flag, 'N')
1112 INTO l_initiator_id,
1113 l_ini_le_id,
1114 l_trx_invoice_flag
1115 FROM fun_trx_batches b,
1116 fun_trx_types_vl t
1117 WHERE b.batch_id = p_batch_id
1118 AND b.trx_type_id = t.trx_type_id;
1119
1120 IF l_trx_invoice_flag = 'N'
1121 THEN
1122 -- Check if initiator requires invoicing
1123 XLE_UTILITIES_GRP. Check_IC_Invoice_required(
1124 x_return_status => l_return_status,
1125 x_msg_data => l_le_error,
1126 p_legal_entity_id => l_ini_le_id,
1127 p_party_id => l_initiator_id,
1128 x_intercompany_inv => l_ini_invoice_flag);
1129
1130 IF l_ini_invoice_flag = FND_API.G_TRUE
1131 THEN
1132 -- invoicing is required for the initator and therefore
1133 -- required for all recipients.
1134 UPDATE fun_trx_headers
1135 SET invoice_flag = 'Y'
1136 WHERE batch_id = p_batch_id;
1137 ELSE
1138 -- check if invoice is required for the recipient
1139 FOR l_trx_rec IN c_trx (p_batch_id)
1140 LOOP
1141 -- Check if initiator requires invoicing
1142 XLE_UTILITIES_GRP. Check_IC_Invoice_required(
1143 x_return_status => l_return_status,
1144 x_msg_data => l_le_error,
1145 p_legal_entity_id => l_trx_rec.to_le_id,
1146 p_party_id => l_trx_rec.recipient_id,
1147 x_intercompany_inv => l_rec_invoice_flag);
1148
1149 IF l_rec_invoice_flag = FND_API.G_TRUE
1150 THEN
1151 -- invoicing is required for the recipient
1152 UPDATE fun_trx_headers
1153 SET invoice_flag = 'Y'
1154 WHERE trx_id = l_trx_rec.trx_id;
1155 ELSE
1156 -- invoicing is not required for the recipient
1157 UPDATE fun_trx_headers
1158 SET invoice_flag = 'N'
1159 WHERE trx_id = l_trx_rec.trx_id;
1160 END IF; -- invoicing required for recipient
1161 END LOOP;
1162
1163 END IF; -- invoicing enabled for inititator
1164 ELSE
1165 -- invoicing is required for this transaction
1166 UPDATE fun_trx_headers
1167 SET invoice_flag = 'Y'
1168 WHERE batch_id = p_batch_id;
1169
1170 END IF; -- invoicing enabled for trx type
1171
1172 COMMIT; -- commit here before starting further wf processing
1173
1174 EXCEPTION
1175 WHEN OTHERS
1176 THEN
1177 IF ( FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1178 THEN
1179 fnd_log.string(FND_LOG.LEVEL_UNEXPECTED,
1180 'fun.plsql.fun_wf_common.set_invoice_reqd_flag',
1181 SQLERRM || ' Error occurred '||
1182 'for batch ' || p_batch_id);
1183 END IF;
1184
1185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1186
1187 END set_invoice_reqd_flag;
1188
1189 PROCEDURE wf_abort (p_batch_id IN NUMBER,
1190 p_trx_id IN NUMBER)
1191 IS
1192
1193 cursor batch_abort is
1194 select distinct item_type,item_key
1195 from WF_ITEM_ACTIVITY_STATUSES
1196 where item_type in ('FUNRMAIN','FUNIMAIN','FUNRTVAL','FUNMSYST')
1197 and item_key like to_char(p_batch_id)||'%'
1198 and activity_status in( 'ACTIVE');
1199
1200 cursor trx_abort is
1201 select distinct item_type,item_key
1202 from WF_ITEM_ACTIVITY_STATUSES
1203 where item_type in ('FUNRMAIN','FUNIMAIN','FUNRTVAL','FUNMSYST')
1204 and item_key like to_char(p_batch_id)||'_'||to_char(p_trx_id)||'%'
1205 and activity_status in( 'ACTIVE');
1206
1207 BEGIN
1208
1209 if p_trx_id is NULL then
1210 for wf_abort in batch_abort
1211 loop
1212
1213 wf_engine.AbortProcess
1214 (itemtype =>wf_abort.item_type,
1215 itemkey => wf_abort.item_key
1216 );
1217
1218 end loop;
1219 else
1220 for wf_trx_abort in trx_abort
1221 loop
1222 wf_engine.AbortProcess
1223 (itemtype =>wf_trx_abort.item_type,
1224 itemkey => wf_trx_abort.item_key
1225 );
1226
1227 end loop;
1228
1229 end if;
1230 COMMIT;
1231
1232 EXCEPTION
1233 WHEN NO_DATA_FOUND THEN
1234 null;
1235 WHEN OTHERS THEN
1236 RAISE;
1237
1238 END wf_abort;
1239
1240 END FUN_WF_COMMON;