[Home] [Help]
PACKAGE BODY: APPS.ECX_WF_ERRORS
Source
1 package body ECX_WF_ERRORS as
2 -- $Header: ECXWERRB.pls 120.8 2008/05/21 05:50:24 deannava ship $
3
4 ecx_programunit_error exception;
5
6 PROCEDURE CheckError ( p_package in varchar2,
7 p_programunit in varchar2,
8 p_ret_code in pls_integer,
9 p_errmsg in varchar2)
10
11 is
12 begin
13 if p_ret_code <> 0
14 then
15 raise ecx_programunit_error;
16 end if;
17 exception
18 when others then
19 -- The line below records this function call in the error system
20 -- in the case of an exception.
21 wf_core.context(p_package, p_programunit,
22 p_ret_code, p_errmsg);
23 raise;
24 end checkerror;
25
26 procedure GetInErrorDetails(
27 itemtype in varchar2,
28 itemkey in varchar2,
29 actid in number,
30 funcmode in varchar2,
31 result in out NOCOPY varchar2)
32 is
33 transaction_type varchar2(200);
34 transaction_subtype varchar2(200);
35 party_site_id varchar2(256);
36 party_id varchar2(256);
37 internal_control_number number;
38 message_id varchar2(200);
39 return_code pls_integer;
40 error_msg varchar2(2000);
41 error_type pls_integer;
42 i_org_id number(15);
43 tp_header_id number(15);
44 i_admin_email varchar2(2000);
45 i_return_code pls_integer;
46 i_error_msg varchar2(2000);
47 party_type varchar2(200); --Bug #2183619
48 error_id number(15);
49 error_params varchar2(2000);
50
51 error_item_type varchar2(200);
52 error_item_key varchar2(200);
53 i_event wf_event_t;
54 begin
55
56
57 -- RUN mode - normal process execution
58
59 if (funcmode = 'RUN') then
60
61 i_event := Wf_Engine.GetItemAttrEvent(itemtype, itemkey, name => 'EVENT_MESSAGE');
62
63 /** get the Values from the EventMessage **/
64 return_code := i_event.getValueForParameter('ECX_RETURN_CODE');
65 error_msg := i_event.getValueForParameter('ECX_ERROR_MSG');
66 error_type := i_event.getValueForParameter('ECX_ERROR_TYPE');
67 transaction_type := i_event.getValueForParameter('ECX_TRANSACTION_TYPE');
68 transaction_subtype := i_event.getValueForParameter('ECX_TRANSACTION_SUBTYPE');
69 tp_header_id := i_event.getValueForParameter('ECX_TP_HEADER_ID');
70 -- Get the party type , change in bug #2183619
71 party_type := i_event.getValueForParameter('ECX_PARTY_TYPE');
72 error_id := i_event.getValueForParameter('ECX_ERROR_ID');
73 error_params := i_event.getValueForParameter('ECX_ERROR_PARAMS');
74 ecx_trading_partner_pvt.get_tp_info
75 (
76 p_tp_header_id => tp_header_id,
77 p_party_id => party_id,
78 p_party_site_id => party_site_id,
79 p_org_id => i_org_id,
80 p_admin_email => i_admin_email,
81 retcode => i_return_code,
82 retmsg => i_error_msg
83 );
84
85 /** Getting translated message based on message and param values **/
86 error_msg := ecx_debug.getMessage(error_msg, error_params);
87
88 /** Now we need to set these values into our Error Process
89 *** We could just continually reference back to the source of
90 *** of our errors, but safer to get our own copy.
91 **/
92
93 wf_engine.SetItemAttrText ( itemtype => itemtype,
94 itemkey => itemkey,
95 aname => 'ECX_RETURN_CODE',
96 avalue => return_code);
97
98 wf_engine.SetItemAttrText ( itemtype => itemtype,
99 itemkey => itemkey,
100 aname => 'ECX_ERROR_MSG',
101 avalue => error_msg);
102
103 wf_engine.SetItemAttrText ( itemtype => itemtype,
104 itemkey => itemkey,
105 aname => 'ECX_ERROR_TYPE',
106 avalue => error_type);
107
108 wf_engine.SetItemAttrText ( itemtype => itemtype,
109 itemkey => itemkey,
110 aname => 'ECX_TRANSACTION_TYPE',
111 avalue => transaction_type);
112
113 wf_engine.SetItemAttrText ( itemtype => itemtype,
114 itemkey => itemkey,
115 aname => 'ECX_TRANSACTION_SUBTYPE',
116 avalue => transaction_subtype);
117
118 wf_engine.SetItemAttrText ( itemtype => itemtype,
119 itemkey => itemkey,
120 aname => 'ECX_PARTY_SITE_ID',
121 avalue => party_site_id);
122
123 wf_engine.SetItemAttrText ( itemtype => itemtype,
124 itemkey => itemkey,
125 aname => 'ECX_PARTY_ID',
126 avalue => party_id);
127
128 wf_engine.SetItemAttrText ( itemtype => itemtype,
129 itemkey => itemkey,
130 aname => 'ECX_PARTY_ADMIN_EMAIL',
131 avalue => i_admin_email);
132 /* Bug #2183619 */
133 begin
134 wf_engine.SetItemAttrText ( itemtype => itemtype,
135 itemkey => itemkey,
136 aname => 'ECX_PARTY_TYPE',
137 avalue => party_type);
138 exception
139 when others then
140 if(wf_core.error_name = 'WFENG_ITEM_ATTR') then
141 wf_engine.addItemAttr(itemtype, itemkey, 'ECX_PARTY_TYPE');
142 wf_engine.SetItemAttrText ( itemtype => itemtype,
143 itemkey => itemkey,
144 aname => 'ECX_PARTY_TYPE',
145 avalue => party_type);
146 else
147 raise;
148 end if;
149 end;
150
151 /* Bug 2260180 */
152 wf_engine.SetItemAttrText ( itemtype => itemtype,
153 itemkey => itemkey,
154 aname => 'ECX_TP_HEADER_ID',
155 avalue => tp_header_id);
156
157 -- example completion
158 result := 'COMPLETE';
159 return;
160 end if;
161
162
163
164 -- CANCEL mode - activity 'compensation'
165 --
166 -- This is in the event that the activity must be undone,
167 -- for example when a process is reset to an earlier point
168 -- due to a loop back.
169
170 if (funcmode = 'CANCEL') then
171
172 -- your cancel code goes here
173 null;
174
175 -- no result needed
176 result := 'COMPLETE';
177 return;
178 end if;
179
180
181
182 -- Other execution modes may be created in the future. Your
183 -- activity will indicate that it does not implement a mode
184 -- by returning null
185
186 result := '';
187 return;
188
189 exception
190 when others then
191 -- The line below records this function call in the error system
192 -- in the case of an exception.
193 wf_core.context('ECX_WF_ERRORS', 'GETINERRORDETAILS',
194 itemtype, itemkey, to_char(actid), funcmode);
195 raise;
196 end GetInErrorDetails;
197
198 procedure GetOutErrorDetails(
199 itemtype in varchar2,
200 itemkey in varchar2,
201 actid in number,
202 funcmode in varchar2,
203 result in out NOCOPY varchar2)
204 is
205 transaction_type varchar2(200);
206 transaction_subtype varchar2(200);
207 party_site_id varchar2(256);
208 document_id varchar2(200);
209 party_id varchar2(200);
210 return_code pls_integer;
211 error_msg varchar2(2000);
212 error_type pls_integer;
213
214 error_item_type varchar2(200);
215 error_item_key varchar2(200);
216 error_activity_id number;
217 party_type varchar2(200); --Bug #2183619
218 begin
219
220
221 -- RUN mode - normal process execution
222
223 if (funcmode = 'RUN') then
224
225 /** If we got here from a workflow process, we need to get a bunch
226 *** of item attributes from the errored workflow process so that we
227 *** can call ECX APIs from out Workflow Error Process
228 **/
229
230 /** Get the Error Item Type and Error Item Key **/
231
232 error_item_type := wf_engine.GetItemAttrText(
233 itemtype => itemtype,
234 itemkey => itemkey,
235 aname => 'ERROR_ITEM_TYPE' );
236
237 error_item_key := wf_engine.GetItemAttrText(
238 itemtype => itemtype,
239 itemkey => itemkey,
240 aname => 'ERROR_ITEM_KEY' );
241
242 error_activity_id := wf_engine.GetItemAttrNumber(
243 itemtype => itemtype,
244 itemkey => itemkey,
245 aname => 'ERROR_ACTIVITY_ID' );
246
247 transaction_type := wf_engine.GetActivityAttrText(
248 itemtype,
249 itemkey,
250 error_activity_id,
251 'ECX_TRANSACTION_TYPE',
252 true);
253
254 transaction_subtype := wf_engine.GetActivityAttrText(
255 itemtype,
256 itemkey,
257 error_activity_id,
258 'ECX_TRANSACTION_SUBTYPE',
259 true);
260
261
262 -- Are we sure all of these item attributes will exist
263 -- If some are optional, pass ignore_notfound
264
265
266 party_id := wf_engine.GetItemAttrText(
267 itemtype => error_item_type,
268 itemkey => error_item_key,
269 aname => 'ECX_PARTY_ID',
270 ignore_notfound=> true );
271
272 party_site_id := wf_engine.GetItemAttrText(
273 itemtype => error_item_type,
274 itemkey => error_item_key,
275 aname => 'ECX_PARTY_SITE_ID',
276 ignore_notfound=> true );
277
278 document_id := wf_engine.GetItemAttrText(
279 itemtype => error_item_type,
280 itemkey => error_item_key,
281 aname => 'ECX_DOCUMENT_ID' ,
282 ignore_notfound=> true);
283
284 /* start of chnages for bug #2183619 */
285 party_type := wf_engine.GetItemAttrText(
286 itemtype => error_item_type,
287 itemkey => error_item_key,
288 aname => 'ECX_PARTY_TYPE' ,
289 ignore_notfound=> true);
290 /* End of changes for bug #2183619*/
291
292
293 if transaction_type is null then
294 transaction_type := wf_engine.GetItemAttrText(
295 itemtype => error_item_type,
296 itemkey => error_item_key,
297 aname => 'ECX_TRANSACTION_TYPE' ,
298 ignore_notfound=> true);
299 end if;
300
301 if transaction_subtype is null then
302 transaction_subtype := wf_engine.GetItemAttrText(
303 itemtype => error_item_type,
304 itemkey => error_item_key,
305 aname => 'ECX_TRANSACTION_SUBTYPE' ,
306 ignore_notfound=> true);
307 end if;
308
309 /** This won't be set because of the rollback **/
310
311 /**
312 error_type := wf_engine.GetItemAttrText(
313 itemtype => error_item_type,
314 itemkey => error_item_key,
315 aname => 'ECX_ERROR_TYPE' );
316
317 error_msg := wf_engine.GetItemAttrText(
318 itemtype => error_item_type,
319 itemkey => error_item_key,
320 aname => 'ECX_ERROR_MSG' );
321
322 return_code := wf_engine.GetItemAttrText(
323 itemtype => error_item_type,
324 itemkey => error_item_key,
325 aname => 'ECX_RETURN_CODE' );
326 **/
327
328
329 /** Now we need to set these values into our Error Process
330 *** We could just continually reference back to the source of
331 *** of our errors, but safer to get our own copy.
332 **/
333
334 wf_engine.SetItemAttrText ( itemtype => itemtype,
335 itemkey => itemkey,
336 aname => 'ECX_PARTY_ID',
337 avalue => party_id);
338
339 wf_engine.SetItemAttrText ( itemtype => itemtype,
340 itemkey => itemkey,
341 aname => 'ECX_PARTY_SITE_ID',
342 avalue => party_site_id);
343
344 wf_engine.SetItemAttrText ( itemtype => itemtype,
345 itemkey => itemkey,
346 aname => 'ECX_DOCUMENT_ID',
347 avalue => document_id);
348
349 wf_engine.SetItemAttrText ( itemtype => itemtype,
350 itemkey => itemkey,
351 aname => 'ECX_TRANSACTION_TYPE',
352 avalue => transaction_type);
353
354 wf_engine.SetItemAttrText ( itemtype => itemtype,
355 itemkey => itemkey,
356 aname => 'ECX_TRANSACTION_SUBTYPE',
357 avalue => transaction_subtype);
358
359 /* Start of changes for bug #2183619 */
360 begin
361 wf_engine.SetItemAttrText ( itemtype => itemtype,
362 itemkey => itemkey,
363 aname => 'ECX_PARTY_TYPE',
364 avalue => party_type);
365 exception
366 when others then
367 if(wf_core.error_name = 'WFENG_ITEM_ATTR') then
368 wf_engine.addItemAttr(itemtype, itemkey, 'ECX_PARTY_TYPE');
369 wf_engine.SetItemAttrText ( itemtype => itemtype,
370 itemkey => itemkey,
371 aname => 'ECX_PARTY_TYPE',
372 avalue => party_type);
373 else
374 raise;
375 end if;
376 end;
377 /* End of changes for bug #2183619*/
378
379 error_msg := ecx_debug.getMessage(ecx_utils.i_errbuf, ecx_utils.i_errparams);
380
381 /* Using g_rec_tp_id here as inbound and passthroughs
382 will use the GetInErrorDetails
383 which gets tp_header_id from the error event. */
384 wf_engine.SetItemAttrText ( itemtype => itemtype,
385 itemkey => itemkey,
386 aname => 'ECX_TP_HEADER_ID',
387 avalue => ecx_utils.g_rec_tp_id);
388
389 wf_engine.SetItemAttrText ( itemtype => itemtype,
390 itemkey => itemkey,
391 aname => 'ECX_RETURN_CODE',
392 avalue => ecx_utils.i_ret_code);
393
394 wf_engine.SetItemAttrText ( itemtype => itemtype,
395 itemkey => itemkey,
396 aname => 'ECX_ERROR_MSG',
397 avalue => error_msg);
398
399 wf_engine.SetItemAttrText ( itemtype => itemtype,
400 itemkey => itemkey,
401 aname => 'ECX_ERROR_TYPE',
402 avalue => ecx_utils.error_type);
403
404
405 -- example completion
406 result := 'COMPLETE';
407 return;
408 end if;
409
410
411
412 -- CANCEL mode - activity 'compensation'
413 --
414 -- This is in the event that the activity must be undone,
415 -- for example when a process is reset to an earlier point
416 -- due to a loop back.
417
418 if (funcmode = 'CANCEL') then
419
420 -- your cancel code goes here
421 null;
422
423 -- no result needed
424 result := 'COMPLETE';
425 return;
426 end if;
427
428
429
430 -- Other execution modes may be created in the future. Your
431 -- activity will indicate that it does not implement a mode
432 -- by returning null
433
434 result := '';
435 return;
436
437 exception
438 when others then
439 -- The line below records this function call in the error system
440 -- in the case of an exception.
441 wf_core.context('ECX_WF_ERRORS', 'GETOUTERRORDETAILS',
442 itemtype, itemkey, to_char(actid), funcmode);
443 raise;
444 end GetOutErrorDetails;
445
446 procedure GETTPROLE(
447 itemtype in varchar2,
448 itemkey in varchar2,
449 actid in number,
450 funcmode in varchar2,
451 result in out NOCOPY varchar2)
452 is
453 transaction_type varchar2(200);
454 transaction_subtype varchar2(200);
455 party_site_id varchar2(256);
456 party_id varchar2(256);
457 rname varchar2(200);
458 email_address varchar2(2000);
459 display_name varchar2(200);
460 error_item_type varchar2(200);
461 error_item_key varchar2(200);
462 ret_code varchar2(200);
463 error_msg varchar2(200);
464
465 i_notification_preference varchar2(200);
466 i_language varchar2(200);
467 i_territory varchar2(200);
468 i_wf_email_address varchar2(200);
469 i_wf_display_name varchar2(200);
470
471 i_email_addr varchar2(2000);
472 i_party_id varchar2(2000);
473 i_party_site_id varchar2(2000);
474 party_type varchar2(200); --bug #2183619
475 tp_header_id varchar2(200);
476
477 l_params wf_parameter_list_t;
478 begin
479
480
481 -- RUN mode - normal process execution
482
483 if (funcmode = 'RUN') then
484
485 -- your run code goes here
486
487
488 /** Get the Transaction Type , Transaction Subtype, Party_id, Party_site_id from Item Attributes ***/
489
490 transaction_type := wf_engine.GetItemAttrText(
491 itemtype => itemtype,
492 itemkey => itemkey,
493 aname => 'ECX_TRANSACTION_TYPE' );
494
495 transaction_subtype := wf_engine.GetItemAttrText(
496 itemtype => itemtype,
497 itemkey => itemkey,
498 aname => 'ECX_TRANSACTION_SUBTYPE' );
499
500 party_id := wf_engine.GetItemAttrText(
501 itemtype => itemtype,
502 itemkey => itemkey,
503 aname => 'ECX_PARTY_ID' );
504
505 party_site_id := wf_engine.GetItemAttrText(
506 itemtype => itemtype,
507 itemkey => itemkey,
508 aname => 'ECX_PARTY_SITE_ID' );
509
510 email_address := wf_engine.GetItemAttrText(
511 itemtype => itemtype,
512 itemkey => itemkey,
513 aname => 'ECX_PARTY_ADMIN_EMAIL',
514 ignore_notfound => true);
515 /* Start of changes for bug #2183619 */
516 party_type := wf_engine.GetItemAttrText(
517 itemtype => itemtype,
518 itemkey => itemkey,
519 aname => 'ECX_PARTY_TYPE',
520 ignore_notfound => true);
521 /* End of changes for bug #2183619 */
522
523 tp_header_id := wf_engine.GetItemAttrText(
524 itemtype => itemtype,
525 itemkey => itemkey,
526 aname => 'ECX_TP_HEADER_ID',
527 ignore_notfound => true);
528
529 if (tp_header_id is null) then
530 result := 'COMPLETE:F';
531 return;
532 end if;
533
534 wf_directory.GetRoleName
535 (
536 p_orig_system=>'ECX_TP:'|| tp_header_id,
537 p_orig_system_id=>tp_header_id,
538 p_name=>rname,
539 p_display_name=>display_name
540 );
541
542 /** Even though the tp_header form is populating role with email
543 keeping the following for backward compatibility in case **/
544
545 if rname is null then
546
547 rname := 'ECX_TP-'|| tp_header_id;
548
549
550 if (email_address is null) then
551 ecx_trading_partner_pvt.get_tp_company_email(
552 l_transaction_type =>transaction_type,
553 l_transaction_subtype =>transaction_subtype,
554 l_party_site_id =>party_site_id,
555 l_party_type =>party_type, --Bug #2183619
556 l_email_addr =>email_address,
557 retcode =>ret_code,
558 errmsg =>error_msg);
559 end if;
560
561 if (ret_code > 0) then
562 result := 'COMPLETE:F';
563 return;
564 end if;
565
566 l_params := wf_parameter_list_t();
567
568 wf_event.addParameterToList(
569 p_name => 'USER_NAME',
570 p_value => rname,
571 p_parameterlist => l_params);
572
573 wf_event.addParameterToList(
574 p_name => 'DisplayName',
575 p_value => rname,
576 p_parameterlist => l_params);
577
578 wf_event.addParameterToList(
579 p_name => 'mail',
580 p_value => email_address,
581 p_parameterlist => l_params);
582
583 wf_local_synch.propagate_role(
584 p_orig_system => 'ECX_TP:' || tp_header_id,
585 p_orig_system_id => tp_header_id,
586 p_attributes => l_params,
587 p_start_date => sysdate,
588 p_expiration_date => sysdate +50000
589 );
590
591 else
592
593 i_party_id := party_id;
594 i_party_site_id := party_site_id;
595
596 -- get the email address from tp headers
597 begin
598 select company_admin_email
599 into i_email_addr
600 from ecx_tp_headers eth
601 where eth.party_id = i_party_id
602 and eth.party_site_id = i_party_site_id
603 and eth.party_type = nvl(party_type, eth.party_type);
604 exception
605 when too_many_rows then
606 result := 'COMPLETE:F';
607 return;
608 when others then
609 result := 'COMPLETE:F';
610 return;
611 raise;
612 end;
613
614 wf_directory.getRoleInfo(
615 role => rname,
616 display_name => i_wf_display_name,
617 email_address => i_wf_email_address,
618 notification_preference => i_notification_preference,
619 language => i_language,
620 territory => i_territory
621 );
622
623
624 if ((i_wf_email_address is null) OR
625 (i_wf_email_address <> i_email_addr))
626 then
627
628 l_params := wf_parameter_list_t();
629
630 wf_event.addParameterToList(
631 p_name => 'UpdateOnly',
632 p_value => 'TRUE',
633 p_parameterlist => l_params);
634
635 wf_event.addParameterToList(
636 p_name => 'mail',
637 p_value => i_email_addr,
638 p_parameterlist => l_params);
639
640 wf_event.addParameterToList(
641 p_name => 'USER_NAME',
642 p_value => rname,
643 p_parameterlist => l_params);
644
645 wf_local_synch.propagate_role(
646 p_orig_system => 'ECX_TP:' || tp_header_id,
647 p_orig_system_id => tp_header_id,
648 p_attributes => l_params,
649 p_start_date => sysdate,
650 p_expiration_date => sysdate +50000
651 );
652
653 end if;
654 end if;
655
656 wf_engine.SetItemAttrText ( itemtype => itemtype,
657 itemkey => itemkey,
658 aname => 'ECX_TP_ROLE',
659 avalue => rname);
660
661 -- example completion
662 result := 'COMPLETE:T';
663 return;
664 end if;
665
666
667 -- CANCEL mode - activity 'compensation'
668 --
669 -- This is in the event that the activity must be undone,
670 -- for example when a process is reset to an earlier point
671 -- due to a loop back.
672
673 if (funcmode = 'CANCEL') then
674
675 -- your cancel code goes here
676 null;
677
678 -- no result needed
679 result := 'COMPLETE:T';
680 return;
681 end if;
682
683
684 -- Other execution modes may be created in the future. Your
685 -- activity will indicate that it does not implement a mode
686 -- by returning null
687
688 result := 'COMPLETE:T';
689 return;
690
691 exception
692 when invalid_number then
693 result := 'COMPLETE:F';
694 return;
695 when value_error then
696 result := 'COMPLETE:F';
697 return;
698 when others then
699 -- The line below records this function call in the error system
700 -- in the case of an exception.
701 wf_core.context('ECX_WF_ERRORS', 'GETTPROLE',
702 itemtype, itemkey, to_char(actid),
703 funcmode, ret_code, error_msg);
704 raise;
705 end getTpRole;
706
707 Procedure GETSAROLE(
708 itemtype in varchar2,
709 itemkey in varchar2,
710 actid in number,
711 funcmode in varchar2,
712 result in out NOCOPY varchar2)
713 is
714
715 rname varchar2(200);
716 email_address varchar2(2000);
717 display_name varchar2(200);
718 ret_code varchar2(200);
719 errmsg varchar2(200);
720
721 i_notification_preference varchar2(200);
722 i_language varchar2(200);
723 i_territory varchar2(200);
724 i_wf_email_address varchar2(200);
725 i_wf_display_name varchar2(200);
726 i_email_addr varchar2(200);
727 l_params wf_parameter_list_t;
728 Begin
729
730
731 -- RUN mode - normal process execution
732
733
734 if (funcmode = 'RUN') then
735
736 -- your run code goes here
737
738 /** Check First if the Sysadmin Role Exists**/
739
740 wf_directory.GetRoleName(p_orig_system=>'ECX_SA_ROLE',
741 p_orig_system_id=>0,
742 p_name=>rname,
743 p_display_name=>display_name);
744
745 if rname is null then
746
747 rname := 'ECX_SA';
748 display_name := 'ECX System Administrator';
749
750 /**Get sys admin email from ecx_trading_partner_pvt.get_sysadmin_email api**/
751 ecx_trading_partner_pvt.get_sysadmin_email(
752 email_address =>email_address,
753 retcode =>ret_code,
754 errmsg =>errmsg);
755
756 l_params := wf_parameter_list_t();
757
758 wf_event.addParameterToList(
759 p_name => 'USER_NAME',
760 p_value => rname,
761 p_parameterlist => l_params);
762
763 wf_event.addParameterToList(
764 p_name => 'DisplayName',
765 p_value => display_name,
766 p_parameterlist => l_params);
767
768 wf_event.addParameterToList(
769 p_name => 'mail',
770 p_value => email_address,
771 p_parameterlist => l_params);
772
773 wf_local_synch.propagate_role(
774 p_orig_system => 'ECX_SA_ROLE',
775 p_orig_system_id => 0,
776 p_attributes => l_params,
777 p_start_date => sysdate,
778 p_expiration_date => sysdate +50000
779 );
780
781 else
782 /**Get sys admin email from ecx_trading_partner_pvt.get_sysadmin_email api**/
783 ecx_trading_partner_pvt.get_sysadmin_email(
784 email_address =>i_email_addr,
785 retcode =>ret_code,
786 errmsg =>errmsg);
787
788 -- Should check the return code
789 if (ret_code > 0) then
790 result := 'COMPLETE:F';
791 return;
792 elsif (i_email_addr is null) then
793 result := 'COMPLETE:F';
794 return;
795 end if;
796
797 wf_directory.getRoleInfo(
798 role => rname,
799 display_name => i_wf_display_name,
800 email_address => i_wf_email_address,
801 notification_preference => i_notification_preference,
802 language => i_language,
803 territory => i_territory
804 );
805
806 if ((i_wf_email_address is null) OR
807 (i_wf_email_address <> i_email_addr))
808 then
809
810 l_params := wf_parameter_list_t();
811
812 wf_event.addParameterToList(
813 p_name => 'UpdateOnly',
814 p_value => 'TRUE',
815 p_parameterlist => l_params);
816
817 wf_event.addParameterToList(
818 p_name => 'mail',
819 p_value => i_email_addr,
820 p_parameterlist => l_params);
821
822 wf_event.addParameterToList(
823 p_name => 'USER_NAME',
824 p_value => rname,
825 p_parameterlist => l_params);
826
827
828 wf_local_synch.propagate_role(
829 p_orig_system => 'ECX_SA_ROLE',
830 p_orig_system_id => 0,
831 p_attributes => l_params,
832 p_start_date => sysdate,
833 p_expiration_date => sysdate +50000
834 );
835
836
837 end if;
838 end if;
839
840 wf_engine.SetItemAttrText ( itemtype => itemtype,
841 itemkey => itemkey,
842 aname => 'ECX_SA_ROLE',
843 avalue => rname);
844
845 -- example completion
846 result := 'COMPLETE:T';
847 return;
848 end if;
849
850
851
852 -- CANCEL mode - activity 'compensation'
853 --
854 -- This is in the event that the activity must be undone,
855 -- for example when a process is reset to an earlier point
856 -- due to a loop back.
857
858 if (funcmode = 'CANCEL') then
859
860 -- your cancel code goes here
861 null;
862
863 -- no result needed
864 result := 'COMPLETE';
865 return;
866 end if;
867
868
869 -- Other execution modes may be created in the future. Your
870 -- activity will indicate that it does not implement a mode
871 -- by returning null
872
873 result := '';
874
875 exception
876 when others then
877 -- The line below records this function call in the error system
878 -- in the case of an exception.
879 wf_core.context('ECX_WF_ERRORS', 'GETSAROLE',
880 itemtype, itemkey, to_char(actid),
881 funcmode, ret_code, errmsg);
882 raise;
883 End GETSAROLE;
884 procedure GetErrorRetryCount(itemtype in varchar2,
885 itemkey in varchar2,
886 actid in number,
887 funcmode in varchar2,
888 result in out NOCOPY varchar2) IS
889 i_retry_count varchar2(100);
890 ttype varchar2(100);
891 att1 varchar2(100);
892 i_item_type varchar2(30);
893 i_item_key varchar2(30);
894 i_prof_error_count NUMBER;
895
896 l_params wf_parameter_list_t;
897 i_event wf_event_t;
898
899 BEGIN
900
901 IF (funcmode='RUN') THEN
902 i_event := Wf_Engine.GetItemAttrEvent(itemtype, itemkey, name => 'EVENT_MESSAGE');
903 i_retry_count :=wf_engine.GetItemAttrText ( itemtype => itemtype,
904 itemkey => itemkey,
905 aname => 'ECX_ATTRIBUTE2');
906 i_prof_error_count:=fnd_profile.value_specific(name=>'ECX_MAX_RETRY',
907 user_id=>0,
908 responsibility_id=>20420,
909 application_id=>174,
910 org_id=>null,
911 server_id=>null);
912
913 IF (to_number(i_retry_count) < i_prof_error_count) then
914 i_retry_count :=i_retry_count+1;
915 i_event.addParameterToList('ECX_ATTRIBUTE2',i_retry_count);
916 wf_engine.SetItemAttrEvent(itemtype,itemkey,'EVENT_MESSAGE',i_event);
917 result:='COMPLETE:'||'Y'; -- retry
918 return;
919
920 ELSE
921 result:='COMPLETE:'||'N';
922 return;
923
924 END IF;
925
926 END IF; --run mode
927
928 EXCEPTION
929 when others then
930 -- The line below records this function call in the error system
931 -- in the case of an exception.
932 wf_core.context('ECX_WF_ERRORS', 'GetErrorRetryCount',
933 itemtype, itemkey, to_char(actid), funcmode);
934 raise;
935 END GetErrorRetryCount;
936
937 procedure GetTimeoutValue (itemtype in varchar2,
938 itemkey in varchar2,
939 actid in number,
940 funcmode in varchar2,
941 result in out NOCOPY varchar2) IS
942 i_time_out NUMBER ;
943 BEGIN
944 i_time_out :=fnd_profile.value_specific(name=>'ECX_NOTIFY_TIMEOUT',
945 user_id=>0,
946 responsibility_id=>20420,
947 application_id=>174,
948 org_id=>null,
949 server_id=>null);
950
951
952 wf_engine.SetItemAttrText ( itemtype => itemtype,
953 itemkey => itemkey,
954 aname => 'ERROR_TIMEOUT',
955 avalue => i_time_out);
956 result:='COMPLETE';
957 return;
958
959 EXCEPTION
960 when others then
961 -- The line below records this function call in the error system
962 -- in the case of an exception.
963 wf_core.context('ECX_WF_ERRORS', 'GetTimeoutValue',
964 itemtype, itemkey, to_char(actid), funcmode);
965 raise;
966
967 END GetTimeoutValue;
968
969 Procedure GETTOROLE(
970 itemtype in varchar2,
971 itemkey in varchar2,
972 actid in number,
973 funcmode in varchar2,
974 result in out NOCOPY varchar2)
975 is
976
977 rname varchar2(200);
978 email_address varchar2(2000);
979 display_name varchar2(200);
980 ret_code varchar2(200);
981 errmsg varchar2(200);
982 i_notification_preference varchar2(200);
983 i_language varchar2(200);
984 i_territory varchar2(200);
985 i_wf_email_address varchar2(200);
986 i_wf_display_name varchar2(200);
987 i_email_addr varchar2(200);
988 l_params wf_parameter_list_t;
989 l_transaction_type ecx_transactions.transaction_type%type;
990 l_transaction_subtype ecx_transactions.transaction_subtype%type;
991 l_party_type ecx_transactions.party_type%type;
992 i_transaction_id ecx_transactions.transaction_id%type;
993 l_standard_code ecx_standards.standard_code%type;
994 l_standard_type ecx_standards.standard_type%type;
995 l_party_site_id ecx_tp_details.source_tp_location_code%type;
996 Begin
997
998
999 -- RUN mode - normal process execution
1000
1001
1002 if (funcmode = 'RUN') then
1003
1004 -- your run code goes here
1005 --get transaction_type,transaction_subtype,party_type from item_attributes
1006
1007 l_transaction_type :=wf_engine.GetItemAttrText ( itemtype => itemtype,
1008 itemkey => itemkey,
1009 aname => 'ECX_TRANSACTION_TYPE');
1010
1011 l_transaction_subtype :=wf_engine.GetItemAttrText ( itemtype => itemtype,
1012 itemkey => itemkey,
1013 aname => 'ECX_TRANSACTION_SUBTYPE');
1014 l_party_type := wf_engine.GetItemAttrText(
1015 itemtype => itemtype,
1016 itemkey => itemkey,
1017 aname => 'ECX_PARTY_TYPE',
1018 ignore_notfound => true);
1019
1020 l_standard_code := wf_engine.GetItemAttrText ( itemtype => itemtype,
1021 itemkey => itemkey,
1022 aname => 'ECX_MESSAGE_STANDARD',
1023 ignore_notfound => true);
1024 l_standard_type := wf_engine.GetItemAttrText ( itemtype => itemtype,
1025 itemkey => itemkey,
1026 aname => 'ECX_MESSAGE_TYPE',
1027 ignore_notfound => true);
1028 l_party_site_id := wf_engine.GetItemAttrText(
1029 itemtype => itemtype,
1030 itemkey => itemkey,
1031 aname => 'ECX_PARTY_SITE_ID');
1032
1033 /**Get transaction owner email from ecx_transactions from the userid**/
1034 begin
1035 select distinct et.transaction_id,usr.email_address
1036 into i_transaction_id,i_email_addr
1037 from ecx_ext_processes eep,
1038 ecx_standards es,
1039 ecx_tp_details etd,
1040 ecx_tp_headers eth,
1041 ecx_transactions et,
1042 fnd_user usr
1043 where eep.ext_type = l_transaction_type
1044 and eep.ext_subtype = l_transaction_subtype
1045 and eep.standard_id = es.standard_id
1046 and es.standard_code = l_standard_code
1047 and es.standard_type = nvl(l_standard_type,'XML')
1048 and et.transaction_id = eep.transaction_id
1049 and etd.ext_process_id = eep.ext_process_id
1050 and eth.party_site_id = l_party_site_id
1051 and (eth.party_type = l_party_type or l_party_type is null)
1052 and eth.tp_header_id = etd.tp_header_id
1053 and et.admin_user = usr.user_name
1054 and eep.direction = 'IN' ;
1055 exception
1056 when too_many_rows then
1057 result := 'COMPLETE:F';
1058 return;
1059 when others then
1060 result := 'COMPLETE:F';
1061 return;
1062 raise;
1063 end;
1064 /** Check First if the Transacion Owner Exists**/
1065
1066 wf_directory.GetRoleName(p_orig_system=>'ECX_TO:'||i_transaction_id,
1067 p_orig_system_id=>i_transaction_id,
1068 p_name=>rname,
1069 p_display_name=>display_name);
1070 if rname is null then
1071
1072 rname := 'ECX_TO-'||i_transaction_id;
1073 display_name := 'ECX Transaction Owner';
1074
1075 l_params := wf_parameter_list_t();
1076
1077 wf_event.addParameterToList(
1078 p_name => 'USER_NAME',
1079 p_value => rname,
1080 p_parameterlist => l_params);
1081
1082 wf_event.addParameterToList(
1083 p_name => 'DisplayName',
1084 p_value => display_name,
1085 p_parameterlist => l_params);
1086
1087 wf_event.addParameterToList(
1088 p_name => 'mail',
1089 p_value => i_email_addr,
1090 p_parameterlist => l_params);
1091
1092 wf_local_synch.propagate_role(
1093 p_orig_system => 'ECX_TO:'||i_transaction_id,
1094 p_orig_system_id => i_transaction_id,
1095 p_attributes => l_params,
1096 p_start_date => sysdate,
1097 p_expiration_date => sysdate +50000
1098 );
1099
1100 else
1101
1102 wf_directory.getRoleInfo(
1103 role => rname,
1104 display_name => i_wf_display_name,
1105 email_address => i_wf_email_address,
1106 notification_preference => i_notification_preference,
1107 language => i_language,
1108 territory => i_territory
1109 );
1110
1111 if ((i_wf_email_address is null) OR
1112 (i_wf_email_address <> i_email_addr))
1113 then
1114
1115 l_params := wf_parameter_list_t();
1116
1117 wf_event.addParameterToList(
1118 p_name => 'UpdateOnly',
1119 p_value => 'TRUE',
1120 p_parameterlist => l_params);
1121
1122 wf_event.addParameterToList(
1123 p_name => 'mail',
1124 p_value => i_email_addr,
1125 p_parameterlist => l_params);
1126
1127 wf_event.addParameterToList(
1128 p_name => 'USER_NAME',
1129 p_value => rname,
1130 p_parameterlist => l_params);
1131
1132
1133 wf_local_synch.propagate_role(
1134 p_orig_system => 'ECX_TO:' || i_transaction_id,
1135 p_orig_system_id => i_transaction_id,
1136 p_attributes => l_params,
1137 p_start_date => sysdate,
1138 p_expiration_date => sysdate +50000
1139 );
1140
1141
1142 end if;
1143 end if;
1144
1145 wf_engine.SetItemAttrText ( itemtype => itemtype,
1146 itemkey => itemkey,
1147 aname => 'ECX_TO_ROLE',
1148 avalue => rname);
1149
1150 -- example completion
1151 result := 'COMPLETE:T';
1152 return;
1153 end if;
1154
1155
1156
1157 -- CANCEL mode - activity 'compensation'
1158 --
1159 -- This is in the event that the activity must be undone,
1160 -- for example when a process is reset to an earlier point
1161 -- due to a loop back.
1162
1163 if (funcmode = 'CANCEL') then
1164
1165 -- your cancel code goes here
1166 null;
1167
1168 -- no result needed
1169 result := 'COMPLETE';
1170 return;
1171 end if;
1172
1173
1174 -- Other execution modes may be created in the future. Your
1175 -- activity will indicate that it does not implement a mode
1176 -- by returning null
1177
1178 result := '';
1179
1180 exception
1181 when others then
1182 -- The line below records this function call in the error system
1183 -- in the case of an exception.
1184 wf_core.context('ECX_WF_ERRORS', 'GETTOROLE',
1185 itemtype, itemkey, to_char(actid),
1186 funcmode, ret_code, errmsg);
1187 raise;
1188 End GETTOROLE;
1189
1190 end ECX_WF_ERRORS;