[Home] [Help]
PACKAGE BODY: APPS.EGO_REPORT_WF_UTIL
Source
1 PACKAGE BODY EGO_REPORT_WF_UTIL AS
2 /* $Header: EGORWKFB.pls 120.3 2006/03/03 05:51:27 sdarbha noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'EGO_REPORT_WF_UTIL' ;
5
6 -- For Debug
7 g_debug_file UTL_FILE.FILE_TYPE ;
8 g_debug_flag BOOLEAN := FALSE ; -- For Debug, set TRUE
9 g_output_dir VARCHAR2(80) := NULL ;
10 g_debug_filename VARCHAR2(30) := 'EgoReportWorkflow.log' ;
11 g_debug_errmesg VARCHAR2(240);
12 g_report_url VARCHAR2(2000);
13 g_message VARCHAR2(2000);
14
15
16 /********************************************************************
17 * Debug APIs : Open_Debug_Session, Close_Debug_Session,
18 * Write_Debug
19 * Parameters IN :
20 * Parameters OUT:
21 * Purpose : These procedures are for test and debug
22 *********************************************************************/
23 -- Open_Debug_Session
24
25 PROCEDURE Open_Debug_Session
26 ( p_output_dir IN VARCHAR2 := NULL
27 , p_file_name IN VARCHAR2 := NULL
28 )
29 IS
30 l_found NUMBER := 0;
31 l_utl_file_dir VARCHAR2(2000);
32
33 BEGIN
34
35 IF p_output_dir IS NOT NULL THEN
36 g_output_dir := p_output_dir ;
37
38 END IF ;
39
40 IF p_file_name IS NOT NULL THEN
41 g_debug_filename := p_file_name ;
42 END IF ;
43
44 IF g_output_dir IS NULL
45 THEN
46
47 g_output_dir := FND_PROFILE.VALUE('ECX_UTL_LOG_DIR') ;
48
49 END IF;
50
51 select value
52 INTO l_utl_file_dir
53 FROM v$parameter
54 WHERE name = 'utl_file_dir';
55
56 l_found := INSTR(l_utl_file_dir, g_output_dir);
57
58 IF l_found = 0
59 THEN
60 RETURN;
61 END IF;
62
63 g_debug_file := utl_file.fopen( g_output_dir
64 , g_debug_filename
65 , 'w');
66 g_debug_flag := TRUE ;
67
68 EXCEPTION
69 WHEN OTHERS THEN
70 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
71 g_debug_flag := FALSE;
72
73 END Open_Debug_Session ;
74
75 -- Close Debug_Session
76 PROCEDURE Close_Debug_Session
77 IS
78 BEGIN
79 IF utl_file.is_open(g_debug_file)
80 THEN
81 utl_file.fclose(g_debug_file);
82 END IF ;
83
84 EXCEPTION
85 WHEN OTHERS THEN
86 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
87 g_debug_flag := FALSE;
88
89 END Close_Debug_Session ;
90
91 -- Test Debug
92 PROCEDURE Write_Debug
93 ( p_debug_message IN VARCHAR2 )
94 IS
95 BEGIN
96
97 IF utl_file.is_open(g_debug_file)
98 THEN
99 utl_file.put_line(g_debug_file, p_debug_message);
100 END IF ;
101
102 EXCEPTION
103 WHEN OTHERS THEN
104 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
105 g_debug_flag := FALSE;
106
107 END Write_Debug;
108
109 PROCEDURE Get_Debug_Mode
110 ( p_item_type IN VARCHAR2
111 , p_item_key IN VARCHAR2
112 , x_debug_flag OUT NOCOPY BOOLEAN
113 , x_output_dir OUT NOCOPY VARCHAR2
114 , x_debug_filename OUT NOCOPY VARCHAR2
115 )
116 IS
117
118 l_debug_flag VARCHAR2(1) ;
119
120 BEGIN
121
122 -- Get Debug Flag
123 l_debug_flag := WF_ENGINE.GetItemAttrText
124 ( p_item_type
125 , p_item_key
126 , '.DEBUG_FLAG'
127 );
128
129 IF FND_API.to_Boolean( l_debug_flag ) THEN
130 x_debug_flag := TRUE ;
131 END IF ;
132
133
134 -- Get Debug Output Directory
135 x_output_dir := WF_ENGINE.GetItemAttrText
136 ( p_item_type
137 , p_item_key
138 , '.DEBUG_OUTPUT_DIR'
139 );
140
141
142 -- Get Debug File Name
143 x_debug_filename := WF_ENGINE.GetItemAttrText
144 ( p_item_type
145 , p_item_key
146 , '.DEBUG_FILE_NAME'
147 );
148
149 EXCEPTION
150 WHEN OTHERS THEN
151 g_debug_errmesg := Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240);
152 g_debug_flag := FALSE;
153
154
155 END Get_Debug_Mode ;
156
157
158
159 FUNCTION GetUserName
160 ( p_user_id IN NUMBER)
161 RETURN VARCHAR2
162 IS
163
164 l_user_name varchar2(100) ;
165
166 BEGIN
167
168 SELECT user_name
169 INTO l_user_name
170 FROM FND_USER
171 WHERE user_id = p_user_id ;
172
173 RETURN l_user_name ;
174
175 END GetUserName ;
176
177 FUNCTION GetUserRole
178 ( p_user_id IN NUMBER)
179 RETURN VARCHAR2
180 IS
181
182 l_user_name varchar2(100) ;
183
184 BEGIN
185
186 SELECT user_name
187 INTO l_user_name
188 FROM FND_USER
189 WHERE user_id = p_user_id ;
190
191 RETURN l_user_name ;
192
193 END GetUserRole ;
194
195
196 FUNCTION GetNewItemKey
197 RETURN VARCHAR2
198 IS
199 l_rev_seq NUMBER := NULL;
200 l_new_item_key VARCHAR2(240) := NULL;
201 BEGIN
202
203 -- Generate Item Key from ego_wf_rpt_s.NEXTVAL
204 -- and return the value
205 SELECT ego_wf_rpt_s.NEXTVAL
206 INTO l_rev_seq
207 FROM DUAL;
208
209 l_new_item_key := TO_CHAR(l_rev_seq) ;
210
211 RETURN l_new_item_key ;
212
213 END GetNewItemKey ;
214
215
216 FUNCTION GetWFAdhocRoleName
217 ( p_role_prefix IN VARCHAR2
218 , p_item_type IN VARCHAR2
219 , p_item_key IN VARCHAR2
220 )
221 RETURN VARCHAR2
222 IS
223 l_adhoc_role_name VARCHAR2(320) := NULL;
224
225 BEGIN
226
227 l_adhoc_role_name := p_role_prefix ||
228 p_item_type || '-' ||
229 p_item_key ;
230
231 RETURN l_adhoc_role_name ;
232
233 END GetWFAdhocRoleName ;
234
235
236
237 FUNCTION GetPartyType
238 ( p_party_id IN NUMBER )
239 RETURN VARCHAR2
240 IS
241 l_party_type VARCHAR2(30) := NULL;
242
243 BEGIN
244
245 SELECT party_type
246 INTO l_party_type
247 FROM HZ_PARTIES
248 WHERE party_id = p_party_id ;
249
250 RETURN l_party_type ;
251
252 END GetPartyType ;
253
254
255
256
257 FUNCTION CheckRoleExistence( p_role_name IN VARCHAR2 )
258 RETURN BOOLEAN
259 IS
260
261 l_existence BOOLEAN := FALSE ;
262
263 CURSOR c_role (p_role_name VARCHAR2 )
264 IS
265 SELECT 'Role Exists'
266 FROM DUAL
267 WHERE EXISTS ( SELECT null
268 from WF_LOCAL_ROLES
269 WHERE NAME = p_role_name
270 AND ORIG_SYSTEM = 'WF_LOCAL_ROLES'
271 AND ORIG_SYSTEM_ID = 0
272 ) ;
273
274 BEGIN
275
276 begin
277
278
279 --
280 -- if p_role does not exist, it throws exception
281 -- So we should not write sql to check AdhocRole existence directly
282 -- For safety purpose we just don't care about this execption
283 --
284 WF_DIRECTORY.SetAdHocRoleStatus( role_name => p_role_name
285 , status => 'ACTIVE') ;
286
287 -- l_existence := TRUE ;
288 -- OWF.G Bug3490260
289 -- Added additoinal check because WF_DIRECTORY.SetAdHocRoleStatus
290 -- does not raise exception correctly
291 -- Once the bug is fixed need to remove
292 FOR l_rec IN c_role(p_role_name => p_role_name)
293 LOOP
294
295 l_existence := TRUE ;
296
297 END LOOP ;
298
299 exception
300 when others then
301 null ;
302
303 end ;
304
305 RETURN l_existence ;
306
307 END CheckRoleExistence ;
308
309
310
311 PROCEDURE DeleteRoleAndUsers
312 ( p_role_name IN VARCHAR2)
313 IS
314
315
316 BEGIN
317
318
319 /* This might NOT be following standard
320 -- Deleting these adhoc role and user roles
321 -- should be done by WF Purge Program
322 -- Instead of this, Set Adhoc Role Expiration
323 -- using WF API. Then once user run WF Purge progam
324 -- deleting these role and user roles is taken care of.
325
326 -- DELETE FROM wf_local_roles
327 -- WHERE name = p_role_name ;
328
329 -- DELETE FROM wf_local_user_roles
330 -- WHERE role_name = p_role_name ;
331 */
332
333 begin
334
335 --
336 -- if p_role does not exist, it throws exception
337 -- since we don't have workflow pre-req in plm115.9
338 -- we are not sure whether the customer have applied/will apply
339 -- OWF.G patchset
340 -- So we can not write correct sql to check AdhocRole existence
341 -- before calling SetAdhocRoleExpiration
342 -- For safety purpose we just don't care about this execption
343 --
344 WF_DIRECTORY.SetAdhocRoleExpiration
345 ( role_name => p_role_name
346 , expiration_date => SYSDATE) ;
347
348 exception
349 when others then
350 null ;
351 end ;
352
353
354 END DeleteRoleAndUsers ;
355
356
357
358 ----------------------------------------------------------
359 -- WF Directory related OBSOLETE API for Bug4532263
360 -- Replaced with new APIs with post_fix 2
361 -- Keep APIs here for any customization
362 ----------------------------------------------------------
363
364 -- Set User to Role Users
365 PROCEDURE SetUserToRoleUsers
366 ( p_party_id IN NUMBER
367 , x_role_users IN OUT NOCOPY VARCHAR2
368 )
369 IS
370
371 l_user_role VARCHAR2(320) ;
372
373 CURSOR c_party (p_party_id NUMBER)
374 IS
375 SELECT EngSecPeople.user_name user_role
376 FROM ENG_SECURITY_PEOPLE_V EngSecPeople
377 WHERE EngSecPeople.person_id = p_party_id ;
378
379 BEGIN
380
381
382 FOR person_rec IN c_party(p_party_id => p_party_id)
383 LOOP
384
385 l_user_role := person_rec.user_role ;
386
387 IF (WF_DIRECTORY.UserActive(l_user_role ))
388 THEN
389
390 -- Prevent duplicate user
391 IF x_role_users IS NULL OR
392 ( INSTR(x_role_users || ',' , l_user_role || ',' ) = 0 )
393 THEN
394
395 IF (x_role_users IS NOT NULL) THEN
396 x_role_users := x_role_users || ',';
397 END IF;
398
402
399 x_role_users := x_role_users || l_user_role ;
400
401 END IF ;
403 END IF ;
404
405 END LOOP ;
406
407 EXCEPTION
408 WHEN NO_DATA_FOUND THEN
409 null ;
410
411 END SetUserToRoleUsers ;
412
413 -- Set User to Role Users
414 PROCEDURE SetGroupToRoleUsers
415 ( p_group_id IN NUMBER
416 , x_role_users IN OUT NOCOPY VARCHAR2
417 )
418 IS
419
420
421 -- Replaced above sql to use ENG_SECURITY_GROUP_MEMBERS_V
422 CURSOR c_grp_members (p_group_id NUMBER)
423 IS
424 SELECT member.member_user_name user_role
425 FROM ENG_SECURITY_GROUP_MEMBERS_V member
426 WHERE member.group_id = p_group_id ;
427
428
429 BEGIN
430
431 FOR grp_member_rec in c_grp_members (p_group_id => p_group_id )
432 LOOP
433
434 IF (WF_DIRECTORY.UserActive(grp_member_rec.user_role ))
435 THEN
436
437 -- Prevent duplicate user
438 IF x_role_users IS NULL OR
439 ( INSTR(x_role_users || ',' , grp_member_rec.user_role || ',' ) = 0 )
440 THEN
441
442 IF (x_role_users IS NOT NULL) THEN
443 x_role_users := x_role_users || ',';
444 END IF;
445
446 x_role_users := x_role_users || grp_member_rec.user_role ;
447
448 END IF ;
449
450 END IF ;
451
452 END LOOP ;
453
454 END SetGroupToRoleUsers ;
455
456
457
458 -- Set Assignee to Role Users
459 PROCEDURE SetAssigneeToRoleUsers
460 ( p_assignee_party_id IN NUMBER
461 , x_role_users IN OUT NOCOPY VARCHAR2
462 )
463 IS
464 l_party_type VARCHAR2(30) ;
465
466 BEGIN
467
468 IF p_assignee_party_id IS NOT NULL THEN
469
470 l_party_type := GetPartyType(p_party_id => p_assignee_party_id ) ;
471
472 IF l_party_type = 'PERSON' THEN
473
474 SetUserToRoleUsers( p_party_id => p_assignee_party_id
475 , x_role_users => x_role_users
476 ) ;
477
478 ELSIF l_party_type = 'GROUP' THEN
479
480 SetGroupToRoleUsers( p_group_id => p_assignee_party_id
481 , x_role_users => x_role_users
482 ) ;
483
484 END IF ;
485
486 END IF ; -- if p_assignee_party_id is not null
487
488 END SetAssigneeToRoleUsers ;
489
490
491 PROCEDURE SetWFAdhocRole (p_role_name IN OUT NOCOPY VARCHAR2,
492 p_role_display_name IN OUT NOCOPY VARCHAR2,
493 p_role_users IN VARCHAR2 DEFAULT NULL,
494 p_expiration_date IN DATE DEFAULT SYSDATE)
495 IS
496
497
498
499 BEGIN
500
501 -- Check if the Role already exists
502 IF CheckRoleExistence(p_role_name => p_role_name ) THEN
503
504 -- Replacing existing Users in this Adhoc Role
505 WF_DIRECTORY.RemoveUsersFromAdhocRole
506 ( role_name => p_role_name
507 , role_users => NULL ) ;
508
509 WF_DIRECTORY.AddUsersToAdhocRole
510 ( role_name => p_role_name
511 , role_users => p_role_users ) ;
512
513 ELSE
514
515 WF_DIRECTORY.CreateAdHocRole( role_name => p_role_name
516 , role_display_name => p_role_display_name
517 , role_users => p_role_users
518 , expiration_date => p_expiration_date
519 );
520
521
522 END IF;
523
524
525 END SetWFAdhocRole ;
526 ----------------------------------------------------------
527 -- End of WF Directory related Obsolete API for Bug4532263
528 ----------------------------------------------------------
529
530
531 -------------------------------------------------------------------
532 -- New WF Directory related APIs for Bug4532263
533 -------------------------------------------------------------------
534
535 -- Add Role to WF_DIRECTORY.UserTable
536 PROCEDURE AddRoleToRoleUserTable
537 ( p_role_name IN VARCHAR2
538 , x_role_users IN OUT NOCOPY WF_DIRECTORY.UserTable
539 )
540 IS
541 l_index NUMBER ;
542 l_dup_flag BOOLEAN ;
543 l_new_index NUMBER ;
544
545 BEGIN
546
547 -- First, check the user role is Active
548 IF (WF_DIRECTORY.UserActive(p_role_name))
549 THEN
550
551 l_dup_flag := FALSE ;
552 l_new_index := 0 ;
553
554 -- Second, check the user role is duplicate
555 IF (x_role_users IS NOT NULL AND x_role_users.COUNT > 0)
556 THEN
557 l_index := x_role_users.FIRST;
558 l_new_index := x_role_users.LAST + 1;
559
560 WHILE (l_index IS NOT NULL AND NOT l_dup_flag )
561 LOOP
562 IF p_role_name = x_role_users(l_index)
563 THEN
564 l_dup_flag := TRUE ;
565 END IF ;
566 l_index := x_role_users.NEXT(l_index);
567 END LOOP ;
568
569 END IF ;
570
571
572 IF NOT l_dup_flag
573 THEN
574 x_role_users(l_new_index) := p_role_name ;
575 END IF ;
576
577 END IF ;
578
582 PROCEDURE SetUserToRoleUsers2
579 END AddRoleToRoleUserTable ;
580
581 -- Set User to Role Users2
583 ( p_party_id IN NUMBER
584 , x_role_users IN OUT NOCOPY WF_DIRECTORY.UserTable
585 )
586 IS
587
588 l_user_role VARCHAR2(320) ;
589
590 CURSOR c_party (p_party_id NUMBER)
591 IS
592 SELECT EngSecPeople.user_name user_role
593 FROM ENG_SECURITY_PEOPLE_V EngSecPeople
594 WHERE EngSecPeople.person_id = p_party_id ;
595
596 BEGIN
597
598 FOR person_rec IN c_party(p_party_id => p_party_id)
599 LOOP
600 l_user_role := person_rec.user_role ;
601 AddRoleToRoleUserTable(l_user_role, x_role_users) ;
602 END LOOP ;
603
604 EXCEPTION
605 WHEN NO_DATA_FOUND THEN
606 null ;
607
608 END SetUserToRoleUsers2 ;
609
610 -- Set User to Role Users
611 PROCEDURE SetGroupToRoleUsers2
612 ( p_group_id IN NUMBER
613 , x_role_users IN OUT NOCOPY WF_DIRECTORY.UserTable
614 )
615 IS
616 CURSOR c_grp_members (p_group_id NUMBER)
617 IS
618 SELECT member.member_user_name user_role
619 FROM ENG_SECURITY_GROUP_MEMBERS_V member
620 WHERE member.group_id = p_group_id ;
621
622
623 BEGIN
624
625 FOR grp_member_rec in c_grp_members (p_group_id => p_group_id )
626 LOOP
627 AddRoleToRoleUserTable(grp_member_rec.user_role, x_role_users) ;
628 END LOOP ;
629
630 END SetGroupToRoleUsers2 ;
631
632
633
634 -- Bug4532263
635 --
636 -- 4258267 9.2.0.5.0 MAILER 11.5.10 PRODID-174 PORTID-110 3623217
637 -- Abstract: UNABLE TO SEND NOTIFICATION FOR USER WHOSE NAME HAS SPACES
638 -- Need to call CreateAdHocRole2 which accepts
639 -- WF_DIRECTORY.UserTable as role_users.
640 -- WF base bug3623217 of wf bug4258267:
641 --
642 PROCEDURE SetWFAdhocRole2 (p_role_name IN OUT NOCOPY VARCHAR2,
643 p_role_display_name IN OUT NOCOPY VARCHAR2,
644 p_role_users IN WF_DIRECTORY.UserTable,
645 p_expiration_date IN DATE DEFAULT SYSDATE)
646 IS
647
648 BEGIN
649
650 -- Check if the Role already exists
651 IF CheckRoleExistence(p_role_name => p_role_name ) THEN
652
653 -- Replacing existing Users in this Adhoc Role
654 WF_DIRECTORY.RemoveUsersFromAdhocRole
655 ( role_name => p_role_name
656 , role_users => NULL ) ;
657
658
659 --
660 -- WF_DIRECTORY.AddUsersToAdHocRole2(role_name in varchar2,
661 -- role_users in WF_DIRECTORY.UserTable);
662 WF_DIRECTORY.AddUsersToAdhocRole2
663 ( role_name => p_role_name
664 , role_users => p_role_users ) ;
665
666
667 ELSE
668 -- WF_DIRECTORY.CreateAdHocRole2(role_name in out nocopy varchar2,
669 -- role_display_name in out nocopy varchar2,
670 -- language in varchar2 default null,
671 -- territory in varchar2 default null,
672 -- role_description in varchar2 default null,
673 -- notification_preference in varchar2 default 'MAILHTML',
674 -- role_users in WF_DIRECTORY.UserTable,
675 -- email_address in varchar2 default null,
676 -- fax in varchar2 default null,
677 -- status in varchar2 default 'ACTIVE',
678 -- expiration_date in date default null,
679 -- parent_orig_system in varchar2 default null,
680 -- parent_orig_system_id in number default null,
681 -- owner_tag in varchar2 default null);
682
683 WF_DIRECTORY.CreateAdHocRole2( role_name => p_role_name
684 , role_display_name => p_role_display_name
685 , role_users => p_role_users
686 , expiration_date => p_expiration_date
687 );
688 END IF;
689
690 END SetWFAdhocRole2 ;
691
692 -------------------------------------------------------------------
693 -- End of New WF Directory related APIs for Bug4532263
694 -------------------------------------------------------------------
695
696
697 -- Get Organization Info
698 PROCEDURE GetOrgInfo
699 ( p_organization_id IN NUMBER
700 , x_organization_code OUT NOCOPY VARCHAR2
701 , x_organization_name OUT NOCOPY VARCHAR2
702 )
703 IS
704
705 BEGIN
706
707 -- in 115.10 org id may be -1
708 IF p_organization_id IS NOT NULL AND p_organization_id > 0
709 THEN
710
711 SELECT
712 MP.organization_code organization_code,
713 HAOTL.name organization_name
714 INTO x_organization_code
715 , x_organization_name
716 FROM
717 HR_ALL_ORGANIZATION_UNITS_TL HAOTL,
718 MTL_PARAMETERS MP
719 WHERE
720 HAOTL.organization_id = p_organization_id
721 AND HAOTL.organization_id = MP.ORGANIZATION_ID
722 AND HAOTL.LANGUAGE = USERENV('LANG');
723
724 END IF ;
725
726 END GetOrgInfo ;
727
728
729
730 /********************************************************************
731 * API Type : Private APIs
732 * Purpose : Those APIs are private
736 -- Type : Private
733 *********************************************************************/
734
735 -- API name : GetMessageTextBody
737 -- Pre-reqs : None.
738 -- Function : Workflow PL/SQL CLOB Document API to get ntf text message body
739 -- Parameters : p_document_id IN VARCHAR2 Required
740 -- Format:
741 -- <wf item type>:<wf item key>:<&#NID>
742 --
743 PROCEDURE GetMessageTextBody
744 ( document_id IN VARCHAR2
745 , display_type IN VARCHAR2
746 , document IN OUT NOCOPY CLOB
747 , document_type IN OUT NOCOPY VARCHAR2
748 )
749 IS
750
751 l_index1 NUMBER;
752 l_index2 NUMBER;
753
754 l_doc VARCHAR2(32000) ;
755
756 NL VARCHAR2(1) := FND_GLOBAL.NEWLINE;
757
758 BEGIN
759
760 -- Call GetMessageHTMLBody if display type is text/plain
761 IF (display_type = WF_NOTIFICATION.DOC_HTML ) THEN
762
763 GetMessageHTMLBody
764 ( document_id => document_id
765 , display_type => display_type
766 , document => document
767 , document_type => document_type
768 ) ;
769
770 RETURN ;
771
772 END IF;
773
774 l_doc := g_message || g_report_url ;
775 WF_NOTIFICATION.WriteToClob( document , l_doc);
776
777
778 END GetMessageTextBody ;
779
780
781 -- API name : GetMessageHTMLBody
782 -- Type : Private
783 -- Pre-reqs : None.
784 -- Function : Workflow PL/SQL CLOB Document API to get ntf HTML message body
785 -- Parameters : p_document_id IN VARCHAR2 Required
786 -- Format:
787 -- <wf item type>:<wf item key>:<&#NID>
788 --
789 PROCEDURE GetMessageHTMLBody
790 ( document_id IN VARCHAR2
791 , display_type IN VARCHAR2
792 , document IN OUT NOCOPY CLOB
793 , document_type IN OUT NOCOPY VARCHAR2
794 )
795 IS
796
797 l_doc VARCHAR2(32000) ;
798 l_index1 NUMBER;
799 l_index2 NUMBER;
800 l_host_url VARCHAR2(5000);
801 NL VARCHAR2(1) := FND_GLOBAL.NEWLINE;
802 p_url VARCHAR2(5000);
803 p_message VARCHAR2(5000);
804
805 BEGIN
806
807 l_index1 := instr(document_id, ':');
808 l_index2 := instr(document_id, ':', 1, 2);
809
810 p_url := substr(document_id, 1, l_index1 - 1);
811 p_message := substr(document_id, l_index1 + 1, l_index2 - l_index1 -1);
812
813
814 l_host_url := rtrim(FND_PROFILE.VALUE('APPS_FRAMEWORK_AGENT'), '/') || '/OA_HTML/';
815 l_doc := '<base href= "' || l_host_url || '"> ';
816 l_doc := l_doc || '<br><br>' || p_message || '<br><br>' || '<a href=' || p_url || '>Report</a>';
817
818 l_doc := l_doc || '<!-- Base Href URL -->' || NL;
819
820 WF_NOTIFICATION.WriteToClob( document , l_doc);
821
822 END GetMessageHTMLBody;
823
824
825
826 -- Get Ntf Message PL/SQL Document API Info
827 PROCEDURE GetNtfMessageDocumentAPI
828 ( p_item_type IN VARCHAR2
829 , p_item_key IN VARCHAR2
830 , p_process_name IN VARCHAR2
831 , p_report_url IN VARCHAR2
832 , p_message IN VARCHAR2
833 , x_message_text_body OUT NOCOPY VARCHAR2
834 , x_message_html_body OUT NOCOPY VARCHAR2
835 )
836 IS
837
838 BEGIN
839
840 -- Message Text Body Document API
841 x_message_text_body := 'PLSQLCLOB:EGO_REPORT_WF_UTIL.GetMessageTextBody/'
842 || p_item_type ||':'||p_item_key ||':&#NID' ;
843
844 -- Message HTML Body Document API
845 x_message_html_body := 'PLSQLCLOB:EGO_REPORT_WF_UTIL.GetMessageHTMLBody/'
846 || p_report_url ||':'||p_message ||':&#NID' ;
847
848
849 END GetNtfMessageDocumentAPI ;
850
851
852 /********************************************************************
853 * API Type : Private APIs
854 * Purpose : Internal Use Only
855 *********************************************************************/
856
857
858 PROCEDURE SetAttributes
859 ( x_return_status OUT NOCOPY VARCHAR2
860 , x_msg_count OUT NOCOPY NUMBER
861 , x_msg_data OUT NOCOPY VARCHAR2
862 , p_item_type IN VARCHAR2
863 , p_item_key IN VARCHAR2
864 , p_process_name IN VARCHAR2
865 , p_report_url IN VARCHAR2
866 , p_subject IN VARCHAR2
867 , p_message IN VARCHAR2
868 , p_wf_user_id IN NUMBER
869 , p_wf_user_name IN VARCHAR2 := NULL
870 , p_adhoc_party_list IN VARCHAR2 := NULL
871 , p_report_fwk_region IN VARCHAR2 := NULL
872 , p_report_custom_code IN VARCHAR2 := NULL
873 , p_browse_mode IN VARCHAR2 := NULL
874 , p_report_org_id IN NUMBER := NULL
875 )
876 IS
877
878 l_api_name CONSTANT VARCHAR2(30) := 'SetAttributes';
879
880 -- PL/SQL Table Type Column Datatype Definition
881 -- PL/SQL Table Type Column DataType Definition
882 -- WF_ENGINE.NameTabTyp Wf_Item_Attribute_Values.NAME%TYPE
883 -- WF_ENGINE.TextTabTyp Wf_Item_Attribute_Values.TEXT_VALUE%TYPE
884 -- WF_ENGINE.NumTabTyp Wf_Item_Attribute_Values.NUMBER_VALUE%TYPE
885 -- WF_ENGINE.DateTabTyp Wf_Item_Attribute_Values.DATE_VALUE%TYPE
886
887 l_text_attr_name_tbl WF_ENGINE.NameTabTyp;
888 l_text_attr_value_tbl WF_ENGINE.TextTabTyp;
889
893 l_date_attr_name_tbl WF_ENGINE.NameTabTyp;
890 l_num_attr_name_tbl WF_ENGINE.NameTabTyp;
891 l_num_attr_value_tbl WF_ENGINE.NumTabTyp;
892
894 l_date_attr_value_tbl WF_ENGINE.DateTabTyp;
895
896 I PLS_INTEGER ;
897
898 l_wf_user_name VARCHAR2(320) ;
899 l_message_text_body VARCHAR2(4000) ;
900 l_message_html_body VARCHAR2(4000) ;
901 l_default_novalue VARCHAR2(2000) ;
902 l_host_url VARCHAR2(256);
903 l_base_href VARCHAR2(256);
904
905 l_report_recepients varchar2(2000);
906
907 l_organization_code VARCHAR2(3) ;
908 l_organization_name VARCHAR2(60) ;
909 l_organization_context VARCHAR2(2000) ;
910
911 BEGIN
912
913 -- Initialize API return status to success
914 x_return_status := FND_API.G_RET_STS_SUCCESS;
915
916
917 IF g_debug_flag THEN
918 Write_Debug('SetAttribute Private API . . .');
919 Write_Debug('-----------------------------------------------------');
920 Write_Debug('Item Type : ' || p_item_type );
921 Write_Debug('Report URL : ' || p_report_url );
922 Write_Debug('-----------------------------------------------------');
923
924 END IF ;
925
926
927 IF g_debug_flag THEN
928 Write_Debug('Got User Info . . .');
929 END IF ;
930
931 -- Get User Info
932 IF p_wf_user_name IS NULL THEN
933
934 l_wf_user_name := GetUserName(p_user_id => p_wf_user_id ) ;
935
936 ELSE
937
938 l_wf_user_name := p_wf_user_name ;
939
940 END IF ;
941
942 IF p_report_org_id IS NOT NULL THEN
943
944 IF g_debug_flag THEN
945 Write_Debug('Get Org Info: ' || TO_CHAR(p_report_org_id));
946 END IF ;
947
948
949 -- Get Organization Info
950 GetOrgInfo
951 ( p_organization_id => p_report_org_id
952 , x_organization_code => l_organization_code
953 , x_organization_name => l_organization_name
954 ) ;
955
956
957 END IF ;
958
959 IF g_debug_flag THEN
960 Write_Debug('Got Workflow Item Attribute Info . . .');
961 END IF ;
962
963 -- Set the values of an array of item type attributes
964 -- Use the correct procedure for your attribute type. All attribute types
965 -- except number and date use SetItemAttrTextArray.
966
967 -- Text Item Attributes
968 -- Using SetItemAttrTextArray():
969 I := 0 ;
970
971 -- Ntf Default From Role
972 I := I + 1 ;
973 l_text_attr_name_tbl(I) := 'FROM_ROLE' ;
974 l_text_attr_value_tbl(I) := l_wf_user_name ;
975
976
977 -- Subject
978 I := I + 1 ;
979 l_text_attr_name_tbl(I) := 'SUBJECT' ;
980 l_text_attr_value_tbl(I) := p_subject ;
981
982
983 -- Message
984 I := I + 1 ;
985 l_text_attr_name_tbl(I) := 'REPORT_MESSAGE' ;
986 l_text_attr_value_tbl(I) := p_message ;
987
988 -- Report URL
989 l_host_url := rtrim(FND_PROFILE.VALUE('APPS_FRAMEWORK_AGENT'), '/') || '/OA_HTML/';
990
991 I := I + 1 ;
992 l_text_attr_name_tbl(I) := 'REPORT_URL' ;
993 l_text_attr_value_tbl(I) := l_host_url || p_report_url ;
994
995
996
997 -- Report Framework Region
998 I := I + 1 ;
999 l_text_attr_name_tbl(I) := 'REPORT_FWK_RN' ;
1000 l_text_attr_value_tbl(I) := p_report_fwk_region || '&ntfId=-&#NID-' ;
1001
1002
1003
1004 -- Report Custom Code
1005 I := I + 1 ;
1006 l_text_attr_name_tbl(I) := 'REPORT_CUSTOM_CODE' ;
1007 l_text_attr_value_tbl(I) := p_report_custom_code ;
1008
1009 -- Report Browse Mode
1010 I := I + 1 ;
1011 l_text_attr_name_tbl(I) := 'BROWSE_MODE' ;
1012 l_text_attr_value_tbl(I) := p_browse_mode ;
1013
1014
1015 -- Organization Code
1016 I := I + 1 ;
1017 l_text_attr_name_tbl(I) := 'ORGANIZATION_CODE' ;
1018 l_text_attr_value_tbl(I) := l_organization_code ;
1019
1020 IF l_organization_code IS NOT NULL AND l_organization_name IS NOT NULL
1021 THEN
1022
1023 FND_MESSAGE.SET_NAME('ENG', 'ENG_ORG_NAME_AND_CODE') ;
1024 FND_MESSAGE.SET_TOKEN('ORG_NAME', l_organization_name) ;
1025 FND_MESSAGE.SET_TOKEN('ORG_CODE', l_organization_code) ;
1026 l_organization_context := FND_MESSAGE.GET ;
1027
1028 END IF ;
1029
1030 -- Organization Context
1031 I := I + 1 ;
1032 l_text_attr_name_tbl(I) := 'ORGANIZATION_CONTEXT' ;
1033 l_text_attr_value_tbl(I) := l_organization_context ;
1034
1035 g_report_url := p_report_url;
1036 g_message := p_message;
1037
1038 /*
1039 Code that is causing the P1 bug
1040
1041 -- Set the Message text and HTML body
1042 l_host_url := rtrim(FND_PROFILE.VALUE('APPS_FRAMEWORK_AGENT'), '/') || '/OA_HTML/';
1043 l_base_href := '<base href= "' || l_host_url || '"> ';
1044 l_message_text_body := p_message || p_report_url;
1045 l_message_html_body := l_base_href || '<br><br>' || p_message || '<br><br>' || '<a href=' || p_report_url || '>Report</a>';
1046
1047 */
1048
1049 -- Get Ntf Message PL/SQL Document API Info
1050 GetNtfMessageDocumentAPI
1051 ( p_item_type => p_item_type
1052 , p_item_key => p_item_key
1053 , p_process_name => p_process_name
1054 , p_report_url => p_report_url
1055 , p_message => p_message
1056 , x_message_text_body => l_message_text_body
1057 , x_message_html_body => l_message_html_body
1061 -- Message Text Body
1058 ) ;
1059
1060
1062 I := I + 1 ;
1063 l_text_attr_name_tbl(I) := 'MESSAGE_TEXT_BODY' ;
1064 l_text_attr_value_tbl(I) := l_message_text_body ;
1065
1066 -- Message HTML Body
1067 I := I + 1 ;
1068 l_text_attr_name_tbl(I) := 'MESSAGE_HTML_BODY' ;
1069 l_text_attr_value_tbl(I) := l_message_html_body ;
1070
1071 -- Adhoc Party List
1072 I := I + 1 ;
1073 l_text_attr_name_tbl(I) := 'ADHOC_PARTY_LIST' ;
1074 l_text_attr_value_tbl(I) := p_adhoc_party_list ;
1075
1076 -- Adhoc Party Role
1077 I := I + 1 ;
1078 l_text_attr_name_tbl(I) := 'ADHOC_PARTY_ROLE' ;
1079 l_text_attr_value_tbl(I) := GetWFAdhocRoleName
1080 ( p_role_prefix => EGO_REPORT_WF_UTIL.G_ADHOC_PARTY_ROLE
1081 , p_item_type => p_item_type
1082 , p_item_key => p_item_key ) ;
1083
1084
1085
1086
1087
1088 IF g_debug_flag THEN
1089 Write_Debug('Call WF_ENGINE.SetItemAttrTextArray . . .');
1090 END IF ;
1091
1092
1093 -- Set Text Attributes
1094 WF_ENGINE.SetItemAttrTextArray
1095 ( itemtype => p_item_type
1096 , itemkey => p_item_key
1097 , aname => l_text_attr_name_tbl
1098 , avalue => l_text_attr_value_tbl
1099 ) ;
1100
1101
1102 -- Number Item Attributes
1103 -- Using SetItemAttrNumberArray():
1104 I := 0 ;
1105
1106 -- Organization Id
1107 I := I + 1 ;
1108 l_num_attr_name_tbl(I) := 'ORGANIZATION_ID' ;
1109 l_num_attr_value_tbl(I) := p_report_org_id ;
1110
1111 -- Set Number Attributes
1112 WF_ENGINE.SetItemAttrNumberArray
1113 ( itemtype => p_item_type
1114 , itemkey => p_item_key
1115 , aname => l_num_attr_name_tbl
1116 , avalue => l_num_attr_value_tbl
1117 ) ;
1118
1119
1120 EXCEPTION
1121 WHEN OTHERS THEN
1122
1123 IF g_debug_flag THEN
1124 Write_Debug('When Others in SetAttributes ' || SQLERRM );
1125 END IF ;
1126
1127
1128 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1129 IF FND_MSG_PUB.Check_Msg_Level
1130 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1131 THEN
1132 FND_MSG_PUB.Add_Exc_Msg
1133 ( G_PKG_NAME ,
1134 l_api_name
1135 );
1136 END IF;
1137
1138
1139 END SetAttributes ;
1140
1141
1142 PROCEDURE SetAdhocPartyRole
1143 ( x_return_status OUT NOCOPY VARCHAR2
1144 , x_msg_count OUT NOCOPY NUMBER
1145 , x_msg_data OUT NOCOPY VARCHAR2
1146 , p_item_type IN VARCHAR2
1147 , p_item_key IN VARCHAR2
1148 , p_adhoc_party_list IN VARCHAR2
1149 )
1150
1151 IS
1152
1153 l_api_name CONSTANT VARCHAR2(30) := 'SetAdhocPartyRole';
1154
1155 -- Role And Users And Privileges
1156 l_party_id NUMBER ;
1157 l_party_type VARCHAR2(30) ;
1158 l_role_name VARCHAR2(320) ;
1159 l_role_display_name VARCHAR2(320) ;
1160 -- Bug4532263
1161 -- l_role_users VARCHAR2(2000) ;
1162 l_role_users WF_DIRECTORY.UserTable ;
1163
1164 c1 PLS_INTEGER;
1165 list_rest VARCHAR2(2000);
1166
1167 BEGIN
1168
1169 x_return_status := '';
1170 x_msg_count := '';
1171 x_msg_data := '';
1172
1173 -- Initialize API return status to success
1174 x_return_status := FND_API.G_RET_STS_SUCCESS;
1175
1176 -- Get Adhoc Party List
1177 list_rest := LTRIM(p_adhoc_party_list) ;
1178 LOOP
1179
1180 c1 := INSTR(list_rest, ',');
1181 IF (c1 = 0) THEN
1182 c1 := INSTR(list_rest, ' ');
1183 IF (c1 = 0) THEN
1184 l_party_id := TO_NUMBER(list_rest) ;
1185 ELSE
1186 l_party_id := TO_NUMBER(substr(list_rest, 1, c1-1));
1187 END IF;
1188 ELSE
1189 l_party_id := TO_NUMBER(substr(list_rest, 1, c1-1));
1190 END IF;
1191
1192 IF l_party_id IS NOT NULL THEN
1193
1194 l_party_type := GetPartyType(p_party_id => l_party_id) ;
1195
1196 IF l_party_type = 'PERSON' THEN
1197
1198
1199 SetUserToRoleUsers2( p_party_id => l_party_id
1200 , x_role_users => l_role_users
1201 ) ;
1202
1203 ELSIF l_party_type = 'GROUP' THEN
1204
1205 SetGroupToRoleUsers2( p_group_id => l_party_id
1206 , x_role_users => l_role_users
1207 ) ;
1208
1209 END IF ;
1210
1211 END IF ; -- if l_party_id is not null
1212
1213 exit when (c1 = 0);
1214 list_rest := LTRIM(SUBSTR(list_rest, c1+1));
1215
1216 END LOOP ;
1217
1218 -- Create adhoc role and add users to role
1219 IF ( l_role_users IS NOT NULL AND l_role_users.COUNT > 0 ) THEN
1220
1221
1222 l_role_name := WF_ENGINE.GetItemAttrText( p_item_type
1223 , p_item_key
1224 , 'ADHOC_PARTY_ROLE');
1225
1226 l_role_display_name := l_role_name ;
1227
1231 , p_role_users => l_role_users
1228 -- Set Adhoc Role and Users in WF Directory Adhoc Role
1229 SetWFAdhocRole2( p_role_name => l_role_name
1230 , p_role_display_name => l_role_display_name
1232 , p_expiration_date => NULL
1233 );
1234 ELSE
1235
1236 -- Return N as None
1237 x_return_status := EGO_REPORT_WF_UTIL.G_RET_STS_NONE;
1238
1239 END IF;
1240
1241 return;
1242 EXCEPTION
1243 WHEN OTHERS THEN
1244 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1245 IF FND_MSG_PUB.Check_Msg_Level
1246 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1247 THEN
1248 FND_MSG_PUB.Add_Exc_Msg
1249 ( G_PKG_NAME ,
1250 l_api_name
1251 );
1252 END IF;
1253
1254 END SetAdhocPartyRole ;
1255
1256
1257 PROCEDURE DeleteAdhocRolesAndUsers
1258 ( x_return_status OUT NOCOPY VARCHAR2
1259 , x_msg_count OUT NOCOPY NUMBER
1260 , x_msg_data OUT NOCOPY VARCHAR2
1261 , p_item_type IN VARCHAR2
1262 , p_item_key IN VARCHAR2
1263 )
1264 IS
1265
1266 l_api_name CONSTANT VARCHAR2(30) := 'DeleteAdhocRolesAndUsers';
1267
1268
1269 TYPE Del_Roles IS TABLE OF VARCHAR2(320)
1270 INDEX BY BINARY_INTEGER ;
1271
1272 l_role_names Del_Roles ;
1273 I PLS_INTEGER := 0 ;
1274
1275
1276 BEGIN
1277
1278 -- Initialize API return status to success
1279 x_return_status := FND_API.G_RET_STS_SUCCESS;
1280
1281
1282 I := I + 1 ;
1283 l_role_names(I) := GetWFAdhocRoleName
1284 ( p_role_prefix => EGO_REPORT_WF_UTIL.G_ADHOC_PARTY_ROLE
1285 , p_item_type => p_item_type
1286 , p_item_key => p_item_key ) ;
1287
1288
1289 FOR i IN l_role_names.FIRST..l_role_names.LAST
1290 LOOP
1291
1292 DeleteRoleAndUsers(p_role_name => l_role_names(i) ) ;
1293
1294 END LOOP ;
1295
1296
1297 EXCEPTION
1298 WHEN OTHERS THEN
1299
1300 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1301 IF FND_MSG_PUB.Check_Msg_Level
1302 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1303 THEN
1304 FND_MSG_PUB.Add_Exc_Msg
1305 ( G_PKG_NAME ,
1306 l_api_name
1307 );
1308 END IF;
1309
1310 END DeleteAdhocRolesAndUsers ;
1311
1312
1313 /********************************************************************
1314 * API Type : Public APIs
1315 * Purpose : Those APIs are public
1316 *********************************************************************/
1317
1318
1319 -- API name : StartWorkflow
1320 -- Type : Public
1321 PROCEDURE StartWorkflow
1322 ( p_api_version IN NUMBER
1323 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1324 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1325 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1326 , x_return_status OUT NOCOPY VARCHAR2
1327 , x_msg_count OUT NOCOPY NUMBER
1328 , x_msg_data OUT NOCOPY VARCHAR2
1329 , p_item_type IN VARCHAR2
1330 , x_item_key IN OUT NOCOPY VARCHAR2
1331 , p_process_name IN VARCHAR2
1332 , p_report_url IN VARCHAR2 := NULL
1333 , p_subject IN VARCHAR2 := NULL
1334 , p_message IN VARCHAR2 := NULL
1335 , p_wf_user_id IN NUMBER
1336 , p_adhoc_party_list IN VARCHAR2 := NULL
1337 , p_report_fwk_region IN VARCHAR2 := NULL
1338 , p_report_custom_code IN VARCHAR2 := NULL
1339 , p_browse_mode IN VARCHAR2 := NULL -- EGO_SUMMARY or EGO_SEQUENTIAL
1340 , p_report_org_id IN NUMBER := NULL
1341 , p_debug IN VARCHAR2 := FND_API.G_FALSE
1342 , p_output_dir IN VARCHAR2 := NULL
1343 , p_debug_filename IN VARCHAR2 := 'EgoReportStartWf.log'
1344 )
1345 IS
1346
1347 l_api_name CONSTANT VARCHAR2(30) := 'StartWorkflow';
1348 l_api_version CONSTANT NUMBER := 1.0;
1349
1350 l_wf_user_name VARCHAR2(320) ;
1351 l_wf_user_key VARCHAR2(240) ;
1352
1353
1354 BEGIN
1355
1356 -- Standard Start of API savepoint
1357 SAVEPOINT StartWorkflow_Util;
1358
1359 -- Standard call to check for call compatibility.
1360
1361 IF NOT FND_API.Compatible_API_Call( l_api_version
1362 , p_api_version
1363 , l_api_name
1364 , G_PKG_NAME )
1365 THEN
1366 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1367 END IF;
1368
1369 -- Initialize message list if p_init_msg_list is set to TRUE.
1370 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1371 FND_MSG_PUB.initialize;
1372 END IF ;
1373
1374 -- For Test/Debug
1375 IF FND_API.to_Boolean( p_debug ) THEN
1376 Open_Debug_Session(p_output_dir, p_debug_filename ) ;
1377 END IF ;
1378
1379 IF g_debug_flag THEN
1380 Write_Debug('Ego_SendReport.SendReport Log');
1381 Write_Debug('-----------------------------------------------------');
1382 Write_Debug('Item Type : ' || p_item_type );
1383 Write_Debug('Item Key : ' || x_item_key );
1384 Write_Debug('Process Name : ' || p_process_name);
1385 Write_Debug('Report URL: ' || p_report_url );
1386 Write_Debug('Subject: ' || p_subject );
1387 Write_Debug('Message: ' || p_message );
1391 Write_Debug('Report Custom Code : ' || p_report_custom_code);
1388 Write_Debug('WF User Id : ' || to_char(p_wf_user_id));
1389 Write_Debug('Adhoc Party List : ' || p_adhoc_party_list);
1390 Write_Debug('Report Fwk Region : ' || p_report_fwk_region);
1392 Write_Debug('Browse Mode : ' || p_browse_mode);
1393 Write_Debug('Report Org Id : ' || to_char(p_report_org_id));
1394 Write_Debug('-----------------------------------------------------');
1395 Write_Debug('Initialize return status ' );
1396 END IF ;
1397
1398 -- Initialize API return status to success
1399 x_return_status := FND_API.G_RET_STS_SUCCESS;
1400
1401 -----------------------------------------------------------------
1402 -- API body
1403 -----------------------------------------------------------------
1404
1405 -- 2. CreateProcess:
1406 -- 2-1. SetItemUserKey:
1407 -- 2-2. SetItemOwner:
1408 -- 3. SetItemAttribute:
1409 -- 4. SetItemParent:
1410 -- 4-1. Additional Set
1411 -- 5. Execute Custom Hook:
1412 -- 6. StartProcess:
1413
1414 -- Call Validate WFProcess
1415 IF g_debug_flag THEN
1416 Write_Debug('2. CreateProcess. . .');
1417 END IF ;
1418
1419
1420 IF x_item_key IS NULL THEN
1421
1422 -- Get new item key
1423 x_item_key := GetNewItemKey ;
1424
1425 END IF ;
1426
1427 IF g_debug_flag THEN
1428 Write_Debug('Got new wf item key: ' || x_item_key );
1429 END IF ;
1430
1431 -- Comment out
1432 -- 115.9 WF User Key
1433 -- l_wf_user_key := substr(p_report_url, 1, 100) || x_item_key;
1434
1435 IF p_report_custom_code IS NOT NULL
1436 THEN
1437 l_wf_user_key := p_report_custom_code || '-' || x_item_key ;
1438
1439 ELSE
1440 l_wf_user_key := x_item_key ;
1441 END IF ;
1442
1443
1444
1445
1446 IF g_debug_flag THEN
1447 Write_Debug('2-1. Set ItemUserKey. . .' || l_wf_user_key );
1448 END IF ;
1449
1450
1451
1452 -- Get User Info
1453 l_wf_user_name := GetUserName(p_user_id => p_wf_user_id ) ;
1454
1455
1456 IF g_debug_flag THEN
1457 Write_Debug('2-2. Set ItemOwner. . .' || l_wf_user_name );
1458 END IF ;
1459
1460
1461 -- Set Workflow Process Owner
1462 WF_ENGINE.CreateProcess
1463 ( itemtype => p_item_type
1464 , itemkey => x_item_key
1465 , process => p_process_name
1466 , user_key => l_wf_user_key
1467 , owner_role => l_wf_user_name ) ;
1468
1469
1470 IF g_debug_flag THEN
1471 Write_Debug('3. SetItemAttribute. . .');
1472 END IF ;
1473
1474 -- Set Item Attributes
1475 SetAttributes
1476 ( x_return_status => x_return_status
1477 , x_msg_count => x_msg_count
1478 , x_msg_data => x_msg_data
1479 , p_item_type => p_item_type
1480 , p_item_key => x_item_key
1481 , p_process_name => p_process_name
1482 , p_report_url => p_report_url
1483 , p_subject => p_subject
1484 , p_message => p_message
1485 , p_wf_user_id => p_wf_user_id
1486 , p_wf_user_name => l_wf_user_name
1487 , p_adhoc_party_list => p_adhoc_party_list
1488 , p_report_fwk_region => p_report_fwk_region
1489 , p_report_custom_code => p_report_custom_code
1490 , p_browse_mode => p_browse_mode
1491 , p_report_org_id => p_report_org_id
1492 ) ;
1493
1494
1495 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
1496 THEN
1497 RAISE FND_API.G_EXC_ERROR ;
1498 END IF ;
1499
1500 IF g_debug_flag THEN
1501 Write_Debug('6. StartProcess. . .');
1502 Write_Debug('Item Type : ' || p_item_type );
1503 Write_Debug('Item Key : ' || x_item_key );
1504 Write_Debug('Process Name : ' || p_process_name);
1505 END IF ;
1506
1507 IF p_item_type IS NOT NULL
1508 AND x_item_key IS NOT NULL
1509 THEN
1510
1511 IF g_debug_flag THEN
1512 Write_Debug('Calling WF_ENGINE.StartProcess . . .') ;
1513 END IF ;
1514
1515
1516 -- Start process
1517 WF_ENGINE.StartProcess
1518 ( itemtype => p_item_type
1519 , itemkey => x_item_key);
1520
1521 END IF ;
1522
1523 IF g_debug_flag THEN
1524 Write_Debug('After executing StartWorkflow API Body') ;
1525 END IF ;
1526
1527
1528 -- Standard check of p_commit.
1529 IF FND_API.To_Boolean( p_commit ) THEN
1530
1531 IF g_debug_flag THEN
1532 Write_Debug('Do Commit.') ;
1533 END IF ;
1534
1535 COMMIT WORK;
1536 END IF;
1537
1538 -- Standard call to get message count and if count is 1, get message info.
1539 FND_MSG_PUB.Count_And_Get
1540 ( p_count => x_msg_count
1541 , p_data => x_msg_data
1542 );
1543
1544
1545 IF g_debug_flag THEN
1546 Write_Debug('Finish. Eng Of Proc') ;
1547 Close_Debug_Session ;
1548 END IF ;
1549
1550
1551
1552 EXCEPTION
1553
1554 WHEN FND_API.G_EXC_ERROR THEN
1555 ROLLBACK TO StartWorkflow_Util ;
1556 x_return_status := FND_API.G_RET_STS_ERROR ;
1557
1558 FND_MSG_PUB.Count_And_Get
1559 ( p_count => x_msg_count
1560 , p_data => x_msg_data
1561 );
1562
1563 IF g_debug_flag THEN
1564 Write_Debug('RollBack and Finish with Error.') ;
1565 Close_Debug_Session ;
1566 END IF ;
1567
1568 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1572 FND_MSG_PUB.Count_And_Get
1569 ROLLBACK TO StartWorkflow_Util ;
1570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1571
1573 ( p_count => x_msg_count
1574 , p_data => x_msg_data
1575 );
1576
1577 IF g_debug_flag THEN
1578 Write_Debug('Rollback and Finish with unxepcted error.') ;
1579 Close_Debug_Session ;
1580 END IF ;
1581
1582 WHEN OTHERS THEN
1583 ROLLBACK TO StartWorkflow_Util ;
1584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1585
1586 FND_MSG_PUB.Count_And_Get
1587 ( p_count => x_msg_count
1588 , p_data => x_msg_data
1589 );
1590
1591 IF FND_MSG_PUB.Check_Msg_Level
1592 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1593 THEN
1594 FND_MSG_PUB.Add_Exc_Msg
1595 ( G_PKG_NAME
1596 , l_api_name
1597 );
1598 END IF;
1599
1600 FND_MSG_PUB.Count_And_Get
1601 ( p_count => x_msg_count
1602 , p_data => x_msg_data
1603 );
1604
1605 IF g_debug_flag THEN
1606 Write_Debug('Rollback and finish with system unxepcted error: '
1607 || Substr(To_Char(SQLCODE)||'/'||SQLERRM,1,240));
1608 Close_Debug_Session ;
1609 END IF ;
1610
1611
1612
1613 END StartWorkflow ;
1614
1615 -- PROCEDURE SELECT_ADHOC_PARTY
1616 PROCEDURE SELECT_ADHOC_PARTY(
1617 itemtype in varchar2,
1618 itemkey in varchar2,
1619 actid in number,
1620 funcmode in varchar2,
1621 result in out NOCOPY varchar2)
1622 IS
1623
1624 l_action_id NUMBER ;
1625 l_adhoc_party_list VARCHAR2(2000) ;
1626
1627 l_return_status VARCHAR2(1);
1628 l_msg_count NUMBER ;
1629 l_msg_data VARCHAR2(200);
1630 l_err_num NUMBER;
1631 l_err_msg varchar2(100);
1632
1633 BEGIN
1634
1635 l_return_status := '';
1636 l_msg_data := '';
1637 l_adhoc_party_list := '';
1638
1639
1640 --
1641 -- RUN mode - normal process execution
1642 --
1643 if (funcmode = 'RUN') then
1644
1645 -- Get Adhoc Party List
1646 l_adhoc_party_list := WF_ENGINE.GetItemAttrText( itemtype
1647 , itemkey
1648 , 'ADHOC_PARTY_LIST');
1649
1650 IF l_adhoc_party_list IS NULL THEN
1651 result := 'COMPLETE:NONE';
1652 return;
1653 END IF ;
1654
1655
1656 -- Set Adhoc Party Role
1657 SetAdhocPartyRole
1658 (
1659 x_return_status => l_return_status
1660 , x_msg_count => l_msg_count
1661 , x_msg_data => l_msg_data
1662 , p_item_type => itemtype
1663 , p_item_key => itemkey
1664 , p_adhoc_party_list => l_adhoc_party_list
1665 ) ;
1666
1667
1668 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1669 -- set result
1670 result := 'COMPLETE';
1671 return;
1672
1673 -- None
1674 ELSIF l_return_status = EGO_REPORT_WF_UTIL.G_RET_STS_NONE THEN
1675 -- set result
1676 result := 'COMPLETE:NONE';
1677 return;
1678 ELSE
1679 -- Unexpected Exception
1680 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1681
1682 END IF ;
1683 end if ; -- funcmode : RUN
1684
1685
1686 --
1687 -- CANCEL mode - activity 'compensation'
1688 --
1689 -- This is in the event that the activity must be undone,
1690 -- for example when a process is reset to an earlier point
1691 -- due to a loop back.
1692 --
1693 if (funcmode = 'CANCEL') then
1694
1695 -- your cancel code goes here
1696 null;
1697
1698 -- no result needed
1699 result := 'COMPLETE';
1700 return;
1701 end if;
1702
1703
1704 --
1705 -- Other execution modes may be created in the future. Your
1706 -- activity will indicate that it does not implement a mode
1707 -- by returning null
1708 --
1709 result := '';
1710 return;
1711
1712 EXCEPTION
1713
1714 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1715 -- The line below records this function call in the error system
1716 -- in the case of an exception.
1717 wf_core.context('EGO_REPORT_WF_UTIL', 'SELECT_ADHOC_PARTY',
1718 itemtype, itemkey, to_char(actid), funcmode);
1719 raise;
1720
1721 WHEN OTHERS THEN
1722 -- The line below records this function call in the error system
1723 -- in the case of an exception.
1724 wf_core.context('EGO_REPORT_WF_UTIL', 'SELECT_ADHOC_PARTY',
1725 itemtype, itemkey, to_char(actid), funcmode);
1726 raise;
1727
1728 END SELECT_ADHOC_PARTY ;
1729
1730 PROCEDURE DELETE_ADHOC_ROLES_AND_USERS(
1731 itemtype in varchar2,
1732 itemkey in varchar2,
1733 actid in number,
1734 funcmode in varchar2,
1735 result in out NOCOPY varchar2)
1736 IS
1737
1738 l_return_status VARCHAR2(1);
1739 l_msg_count NUMBER ;
1740 l_msg_data VARCHAR2(200);
1741
1742 -- ut Wf_Directory.UserTable;
1743
1744
1745 BEGIN
1746
1747 --
1748 -- RUN mode - normal process execution
1749 --
1750 if (funcmode = 'RUN') then
1751
1752
1753 -- Delete Workflow Adhoc Role and Local Users
1754 DeleteAdhocRolesAndUsers
1755 ( x_return_status => l_return_status
1756 , x_msg_count => l_msg_count
1757 , x_msg_data => l_msg_data
1758 , p_item_type => itemtype
1759 , p_item_key => itemkey
1760 ) ;
1761
1762 -- wf_directory.getroleusers( 'EGO_ADHOC,'||itemtype || '-' ||itemkey,ut);
1763
1764 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1765
1766 -- set result
1767 result := 'COMPLETE';
1768 return;
1769
1770 ELSE
1771
1772 -- Unexpected Exception
1773 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1774
1775 END IF ;
1776
1777 end if ; -- funcmode : RUN
1778
1779
1780 --
1781 -- CANCEL mode - activity 'compensation'
1782 --
1783 -- This is in the event that the activity must be undone,
1784 -- for example when a process is reset to an earlier point
1785 -- due to a loop back.
1786 --
1787 if (funcmode = 'CANCEL') then
1788
1789 -- your cancel code goes here
1790 null;
1791
1792 -- no result needed
1793 result := 'COMPLETE';
1794 return;
1795 end if;
1796
1797
1798 --
1799 -- Other execution modes may be created in the future. Your
1800 -- activity will indicate that it does not implement a mode
1801 -- by returning null
1802 --
1803 result := '';
1804 return;
1805
1806 EXCEPTION
1807
1808 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1809 -- The line below records this function call in the error system
1810 -- in the case of an exception.
1811 wf_core.context('EGO_REPORT_WF_UTIL', 'DELETE_ADHOC_ROLES_AND_USERS',
1812 itemtype, itemkey, to_char(actid), funcmode);
1813 raise;
1814
1815
1816 WHEN OTHERS THEN
1817 -- The line below records this function call in the error system
1818 -- in the case of an exception.
1819 wf_core.context('EGO_REPORT_WF_UTIL', 'DELETE_ADHOC_ROLES_AND_USERS',
1820 itemtype, itemkey, to_char(actid), funcmode);
1821 raise;
1822
1823 END DELETE_ADHOC_ROLES_AND_USERS ;
1824
1825 END EGO_REPORT_WF_UTIL ;