[Home] [Help]
PACKAGE BODY: APPS.IBU_SUBS_EMAIL_PKG
Source
1 package body IBU_SUBS_EMAIL_PKG as
2 /* $Header: ibusubsb.pls 120.1 2006/01/19 12:08:30 wma noship $ */
3 SECONDS_IN_DAY constant INTEGER:=60*60*24;
4 SECONDS_IN_REP constant INTEGER:=60*10;
5
6 DoNothing BOOLEAN;
7
8 procedure ibu_create_or_update_role (user_id in NUMBER, email_address_in in varchar2, planguage in varchar2)
9 as
10 user_name varchar2(100):=NULL;
11 user_display_name varchar2(100):=NULL;
12 language varchar2(100):= planguage;
13 territory varchar2(100):='America';
14 description varchar2(100):=NULL;
15 notification_preference varchar2(100):='MAILHTML' ;
16 email_address varchar2(100):=NULL;
17 fax varchar2(100):=NULL;
18 status varchar2(100):='ACTIVE';
19 expiration_date varchar2(100):=NULL;
20 role_name varchar2(100):=NULL;
21 role_display_name varchar2(100):=NULL;
22 role_description varchar2(100):=NULL;
23 wf_id Number;
24 msg_type varchar2(100):='CRMNOTIF';
25 msg_name varchar2(100):='DEFMSG';
26
27 due_date date:=NULL;
28 callback varchar2(100):=NULL;
29 context varchar2(100):=NULL;
30 send_comment varchar2(100):=NULL;
31 priority number:=NULL;
32
33 duplicate_user_or_role exception;
34 PRAGMA EXCEPTION_INIT (duplicate_user_or_role, -20002);
35
36 begin
37 /*Create a role from user_id passed in*/
38 role_name:='IBU_ROLE' || user_id;
39 role_display_name:=role_name || 'Dis';
40 email_address:=email_address_in;
41
42 begin
43 /*
44 Pre 11.5.10:
45 Calling CreateAdHocRole alone was creating a role in
46 WF_ROLES, but wasn't creating a user in WF_USER_ROLES
47 which is required by WF_NOTIFICATION.SendGroup
48 which internally calls Wf_Directory.GetRoleOrigSysInfo
49
50 For 11.5.10:
51 Since CreateAdHocRole itself doesn't create a user in
52 WF_USER_ROLES, so calling CreateAdHocUser instead
53 which creates a user in WF_USER_ROLES and an
54 implicit role in WF_ROLES with ORIG_SYSTEM = 'WF_LOCAL_USERS'
55 and ORIG_SYSTEM_ID = 0
56 */
57
58 WF_DIRECTORY.CreateAdHocUser(role_name,
59 role_display_name,
60 language,
61 territory,
62 description,
63 notification_preference,
64 email_address,
65 fax,
66 status,
67 expiration_date);
68 exception
69 when duplicate_user_or_role then
70 WF_Directory.SetAdHocUserAttr(role_name,
71 role_display_name,
72 notification_preference,
73 language,
74 territory,
75 email_address,
76 fax);
77 end;
78 end;
79
80 /* Added by Mani on 4/9/00 - to start workflow process */
81 /* modified by mukhan on 8/15/03 */
82 -- Added addition tokens
83 -- simple text attribute were parsing HTML anchors
84 -- started using 16 PLSQL document attributes
85 -- only 2K of data gets through PLSQL document id
86 procedure StartProcess (roleName in varchar2,
87 subject in varchar2,
88 username in varchar2,
89 companyName in varchar2,
90 companyWebAddr in varchar2,
91 companyEmailAddr in varchar2,
92 currentDate in varchar2,
93 content in jtf_varchar2_table_32767,
94 ProcessOwner in varchar2,
95 Workflowprocess in varchar2 ,
96 item_type in varchar2 ) is
97 ItemType varchar2(30) := nvl(item_type, 'IBUHPSUB');
98 ItemKey varchar2(30) := 'NOTIF_' || roleName; -- this is not being used
99 ItemUserKey varchar2(30) := roleName;
100
101 cnt number := 0;
102 l_user varchar2(50);
103 seq number := 0;
104 create_seq varchar2(50) := 'create sequence IBU_NOTIFICATION_S';
105 -- get_seq varchar2(50) := 'select ' || 'IBU_NOTIFICATION_S' || '.nextval from dual';
106 get_seq varchar2(50) := 'select ' || 'IBU_WF_ITEM_KEY_S' || '.nextval from dual';
107 TYPE IBU_STRING_ARRAY IS VARRAY(10) OF VARCHAR2(32767); -- mk changed from 4000 to 32767
108 arr IBU_STRING_ARRAY;
109 l_content varchar2(32766);
110
111 --outfile UTL_FILE.FILE_TYPE;
112 mailAttrNames Wf_Engine.NameTabTyp ;
113 mailAttrVals Wf_Engine.TextTabTyp ;
114
115 begin
116
117 /* Get schema name */
118 select user into l_user from dual;
119
120 /* Get sequence for item key to be unique */
121 /* select count(*)
122 into cnt
123 from all_objects
124 where object_name like 'IBU_NOTIFICATION_S'
125 and object_type = 'SEQUENCE'
126 and owner = l_user;
127
128 if cnt = 0 then
129 execute immediate create_seq;
130
131 else
132 execute immediate get_seq into seq;
133 end if; */
134 execute immediate get_seq into seq;
135
136 ItemKey := roleName || seq;
137
138 wf_engine.CreateProcess (itemtype => ItemType,
139 itemkey => ItemKey,
140 process => WorkflowProcess );
141 wf_engine.SetItemUserKey (itemtype => Itemtype,
142 itemkey => Itemkey,
143 userkey => ItemUserKey);
144 wf_engine.SetItemAttrText (itemtype => Itemtype,
145 itemkey => Itemkey,
146 aname => 'IBU_ROLE',
147 avalue => roleName);
148 wf_engine.SetItemAttrText (itemtype => Itemtype,
149 itemkey => Itemkey,
150 aname => 'IBU_SUBJECT_ITEM',
151 avalue => subject);
152 wf_engine.SetItemAttrText (itemtype => Itemtype,
153 itemkey => Itemkey,
154 aname => 'IBU_USER_NAME',
155 avalue => username);
156 wf_engine.SetItemAttrText (itemtype => Itemtype,
157 itemkey => Itemkey,
158 aname => 'IBU_COMPANY_NAME',
159 avalue => companyName );
160 wf_engine.SetItemAttrText (itemtype => Itemtype,
161 itemkey => Itemkey,
162 aname => 'IBU_COMPANY_WEB_ADDR',
163 avalue => companyWebAddr );
164 wf_engine.SetItemAttrText (itemtype => Itemtype,
165 itemkey => Itemkey,
166 aname => 'IBU_COMPANY_EMAIL',
167 avalue => companyEmailAddr );
168 wf_engine.SetItemAttrText (itemtype => Itemtype,
169 itemkey => Itemkey,
170 aname => 'IBU_CURRENT_DATE',
171 avalue => currentDate );
172
173 -- to be used for debugging
174 --outfile := UTL_FILE.FOPEN('/appslog/srv_top/utl/srvdv11i/out',
175 -- roleName || subject,'W',32234);
176
177 for i in 1..16
178 loop
179 if (i <= content.count and content(i) is not null) then
180 mailAttrNames(i) := 'IBUCONTENT' || TO_CHAR(i);
181 mailAttrVals(i) := 'plsql:IBU_SUBS_DOC_PKG.set_msg_body_token/' || content(i);
182 --UTL_FILE.PUT_LINE(outfile,content(i), true);
183 end if;
184 end loop;
185
186 -- UTL_FILE.FCLOSE(outfile);
187
188 wf_engine.SetItemAttrTextArray( Itemtype,
189 Itemkey,
190 mailAttrNames,
191 mailAttrVals);
192
193 wf_engine.SetItemOwner (itemtype => Itemtype,
194 itemkey => Itemkey,
195 owner => roleName);
196
197 wf_engine.StartProcess (itemtype => Itemtype,
198 itemkey => Itemkey );
199
200 end StartProcess;
201
202 procedure ibu_get_role_info (role_name in varchar2,
203 display_Name out NOCOPY varchar2,
204 email_Address out NOCOPY varchar2,
205 notification_Preference out NOCOPY varchar2,
206 language out NOCOPY varchar2,
207 territory out NOCOPY varchar2) is
208 begin
209 wf_directory.GetRoleInfo (role_name,
210 display_Name,
211 email_Address,
212 notification_Preference,
213 language,
214 territory);
215 end ibu_get_role_info;
216
217 procedure ibu_update_role (role_name in varchar2,
218 role_display_name in varchar2,
219 notification_preference in varchar2,
220 language in varchar2,
221 territory in varchar2,
222 email_address in varchar2,
223 fax in varchar2) is
224 begin
225 WF_Directory.SetAdHocRoleAttr (role_name, role_display_name, notification_preference, language, territory, email_address, fax);
226 end ibu_update_role;
227
228 procedure SET_ADMIN_ROLE (itemtype in varchar2,
229 itemkey in varchar2,
230 actid in number,
231 funcmode in varchar2,
232 resultout out nocopy varchar2) is
233
234 l_error_itemtype VARCHAR2(8);
235 l_error_itemkey VARCHAR2(240);
236 l_administrator VARCHAR2(100);
237 l_ADMINISTRATOR_NOT_SET EXCEPTION;
238 begin
239
240 BEGIN
241 l_administrator := FND_PROFILE.VALUE('IBU_WF_ADMINISTRATOR');
242 EXCEPTION
243 WHEN NO_DATA_FOUND THEN
244 l_administrator := 'SYSADMIN';
245 END;
246
247 IF (l_administrator IS NULL) THEN
248 l_administrator := 'SYSADMIN';
249 END IF;
250
251 wf_engine.SetItemAttrText (itemtype => Itemtype,
252 itemkey => Itemkey,
253 aname => 'IBU_ERROR_ADMIN_ROLE',
254 avalue => l_administrator);
255
256 end SET_ADMIN_ROLE;
257
258 end IBU_SUBS_EMAIL_PKG;