1 PACKAGE BODY WF_PUB_ICC AS
2 /* $Header: EGOPICNB.pls 120.0.12020000.1 2013/01/09 10:43:57 jewen noship $ */
3 function CONVERT_PERFORMER(
4 p_mail_to varchar2
5 )RETURN VARCHAR2
6 IS
7 l_orig_sys varchar2(20);
8 l_seperator varchar2(2):=':';
9 l_sep_index number;
10 l_receiver varchar2(50);
11 l_name varchar2(50);
12 BEGIN
13 select instr(p_mail_to,':',1) INTO l_sep_index from dual;
14 l_orig_sys:= SubStr(p_mail_to,1,l_sep_index-1);
15 l_name :=SubStr(p_mail_to,l_sep_index+1,length(p_mail_to));
16 if l_orig_sys='PER' then
17 select user_name into l_receiver
18 from ego_people_v
19 where user_name=l_name
20 and rownum=1;
21 elsif l_orig_sys='GROUP' then
22 select 'HZ_GROUP:'||group_id into l_receiver
23 from EGO_GROUPS_V
24 where group_name=l_name
25 and rownum=1;
26 END if;
27 return l_receiver;
28
29 Exception
30 when NO_DATA_FOUND then
31 return l_receiver;
32 END CONVERT_PERFORMER;
33
34 /*This function is used to check if the systems where the icc is published to contained the
35 *system defined as subscription parameter 'PUB_SYSTEM'.
36 *p_batch_id: batch id of the publish action
37 *p_system:system internal name that is intested in the icc publication
38 */
39 function is_valid_system(
40 p_batch_id number,
41 p_system varchar2
42 ) return boolean
43 is
44 l_valid boolean :=false;
45 cursor l_pub_systems(l_batch_id varchar2)
46 is select system_code
47 from EGO_PUB_BAT_SYSTEMS_B
48 where batch_id=l_batch_id;
49 begin
50 /*
51 *If the subscription paramter pub_system is null, then return false to indicate the system is invalid.
52 */
53 if p_system is null then
54 return l_valid;
55 end if;
56
57 /*
58 *Get system list from batch id
59 *If the system list contain the system defined in the event subscription parameter 'PUB_SYSTEM'
60 */
61 for cur_system in l_pub_systems(p_batch_id) loop
62 if cur_system.system_code = p_system then
63 l_valid:=true;
64 end if;
65 end loop;
66
67 return l_valid;
68 end is_valid_system;
69
70 /**
71 *This custom rule function was used to get subscription parameters and set value for item attributes of
72 *item type 'WFPUBICC'.
73 *If the systems to which the icc is published contain the system defined in susbcription paramter 'PUB_SYSTEM',
74 *start workflow process 'WF_PUB_ICC_NOTIFICATION' to send notification.
75 */
76 FUNCTION SEND_NOTIFICATION(p_subscription in RAW,
77 p_event in out NOCOPY WF_EVENT_T)return varchar2
78 IS
79 l_rule VARCHAR2(20);
80 l_batch_id NUMBER;
81 l_mail_to VARCHAR2(320);
82 l_wf_admin VARCHAR2(320);
83 l_system VARCHAR2(30);
84 l_subparams varchar2(4000);
85 wftype varchar2(15) :='WFPUBICC';
86 wfname varchar2(30) :='WF_PUB_ICC_NOTIFICATION';
87 BEGIN
88 l_batch_id:=p_event.GetValueForParameter('batch_id');
89
90 /**
91 *Get subscription parameters
92 */
93 select parameters
94 into l_subparams
95 from wf_event_subscriptions
96 where guid = p_subscription;
97
98 if l_subparams is not null then
99 l_mail_to := wf_event_functions_pkg.subscriptionparameters(p_string=>l_subparams,p_key=> 'MAIL_TO') ;
100 l_wf_admin := wf_event_functions_pkg.subscriptionparameters(p_string=>l_subparams,p_key=> 'WF_ADMINISTRATOR') ;
101 l_system :=wf_event_functions_pkg.subscriptionparameters(p_string=>l_subparams,p_key=> 'PUB_SYSTEM') ;
102 l_wf_admin := CONVERT_PERFORMER(l_wf_admin);
103 if l_wf_admin is null then
104 l_wf_admin :=WF_DEFAULT_ADMIN;
105 end if;
106 end if;
107
108 /**
109 *If system is valid, start a workflow process to send notification to receiver defined in the event subscription
110 */
111 if is_valid_system(l_batch_id,l_system)=true then
112 WF_ENGINE.CreateProcess(wftype, l_batch_id,wfname);
113 WF_ENGINE.SetItemAttrNumber(wftype, l_batch_id, 'BATCH_ID', l_batch_id);
114 WF_ENGINE.SetItemAttrText(wftype, l_batch_id, 'MAIL_TO', l_mail_to);
115 WF_ENGINE.SetItemAttrText(wftype, l_batch_id, 'WF_ADMINISTRATOR', l_wf_admin);
116 WF_ENGINE.SetItemAttrText(wftype, l_batch_id, 'PUB_SYSTEM', l_system);
117 WF_ENGINE.StartProcess(wftype, l_batch_id);
118 end if;
119 return('SUCCESS');
120 EXCEPTION
121 WHEN OTHERS THEN
122 wf_core.context('WF_PUB_ICC',
123 'SEND_NOTIFICATION',
124 p_event.geteventname(),
125 p_subscription,
126 SQLERRM);
127 wf_event.seterrorinfo(p_event, 'ERROR');
128 RETURN 'ERROR';
129 END SEND_NOTIFICATION;
130
131
132
133 /*
134 *The procedure is to check whether the receiver of the notification defined in the subscription parameter 'MAIL_TO'
135 * is valid.
136 * The procedure will be called by the workflow function during the workflow process
137 */
138 procedure CHECK_RECEIVER(
139 itemtype in varchar2,
140 itemkey in varchar2,
141 actid in number,
142 funcmode in varchar2,
143 resultout in out NOCOPY varchar2)
144 IS
145 wf_yes varchar2(1) := 'Y';
146 wf_no varchar2(1) := 'N';
147 l_receiver varchar2(50);
148 l_exist number;
149 l_orig_sys varchar2(20);
150 l_seperator varchar2(2):=':';
151 l_sep_index number;
152 l_name varchar2(50);
153 l_user_name varchar2(50);
154 l_group_id number;
155 begin
156 l_receiver:=wf_engine.GetItemAttrText(itemtype => itemtype
157 ,itemkey => itemkey
158 ,aname => 'MAIL_TO');
159 /**
160 *Definition of l_receiver varias for different receiver type, the receiver can either be people or group of people
161 *PER:<User_Name> ,<User_Name> is the login user name of the application
162 *HZ_GROUP:<GROUP_NAME>,<GROUP_NAME> is the name of the user group
163 */
164 if l_receiver=null then
165 resultout := wf_engine.eng_completed||':'||wf_no;
166 return;
167 end if;
168
169 select instr(l_receiver,':',1) INTO l_sep_index from dual;
170
171 if l_sep_index =0 then
172 --If the seperator does not exist, it mean that no receiver type is specified ,that is incorrect
173 resultout := wf_engine.eng_completed||':'||wf_no;
174 return;
175 else
176 l_receiver:=CONVERT_PERFORMER(l_receiver);
177 end if;
178
179 if l_receiver IS NOT NULL then
180 wf_engine.SetItemAttrText(itemtype => itemtype
181 ,itemkey => itemkey
182 ,aname => 'MAIL_TO'
183 ,avalue =>l_receiver);
184
185 resultout := wf_engine.eng_completed||':'||wf_yes;
186 else
187 resultout := wf_engine.eng_completed||':'||wf_no;
188 end if;
189 return;
190
191 Exception
192 when others then
193 -- The line below records this function call in the error system
194 -- in the case of an exception.
195 wf_core.context('WFPUBICC', 'CHECK_RECEIVER',
196 itemtype, itemkey, to_char(actid), funcmode,SQLERRM);
197 raise;
198 end CHECK_RECEIVER;
199
200 /*
201 * This procedure is used to generate notification content
202 */
203 procedure PUB_ICC_DOC(
204 document_id in varchar2,
205 display_type in varchar2,
206 document in out NOCOPY varchar2,
207 document_type in out NOCOPY varchar2)
208 is
209 l_version varchar2(150);
210 l_icc_id varchar2(150);
211 l_icc_name varchar2(40);
212 l_icc_description varchar2(240);
213 l_start_date Date;
214 l_end_date Date;
215
216 Cursor aa(p_batch_id varchar2) is
217 select pk1_value,pk2_value
218 from EGO_PUB_BAT_ENT_OBJS_B
219 where batch_id=p_batch_id;
220
221 Cursor bb is
222 select SEGMENT1,description,start_date_active,end_date_active
223 from MTL_ITEM_CATALOG_GROUPS
224 where item_catalog_group_id=l_icc_id;
225
226 begin
227
228 if(document_id is not null) then
229 for cr in aa(document_id) loop
230 l_icc_id :=cr.pk1_value;
231 l_version:=cr.pk2_value;
232 end loop;
233 if(bb%isopen=false) then
234 open bb;
235 end if;
236 fetch bb into l_icc_name,l_icc_description,l_start_date,l_end_date;
237 close bb;
238 if l_version is not null then
239 select start_active_date,end_active_date
240 into l_start_date,l_end_date
241 from EGO_MTL_CATALOG_GRP_VERS_B
242 where item_catalog_group_id=l_icc_id
243 and version_seq_id=l_version;
244 end if;
245 end if;
246 document_type := 'text/html';
247 document :=
248 '<LEFT>The following Product Class Definition has been changed . Please query the new version. '||'<BR><BR>'||
249 'Class Name : '||l_icc_name||'<BR>'||
250 'Description : '||l_icc_description||'<BR>'||
251 'Type : '||'Item Catalog Category'||'<BR>'||
252 'Object Number : '||l_icc_id||'<BR><BR><BR><BR>'||
253 'Product Class Version Details'||'<BR>'||
254 '============================='||'<BR>'||
255 '_____________________________________________________'||'<BR>'||
256 '| Effective Start Date : ' ||l_start_date||'<BR>'||
257 '| ' ||'<BR>'||
258 '| Effective End Date : '||l_end_date||'<BR>'||
259 '| '||'<BR>'||
260 '| Version Number : '||l_version||'<BR>'||
261 '______________________________________________________<LEFT>';
262
263 return;
264
265 exception
266 when others then
267 -- The line below records this procedure call in the error system
268 -- in the case of an exception.
269 wf_core.context('WFPUBICC', 'PUB_ICC_DOC',
270 document_id, display_type,SQLERRM);
271 raise;
272 end PUB_ICC_DOC;
273
274 /*
275 *The procedure is used to update the status of the batch system
276 */
277 procedure UPDATE_SYS_STATUS(
278 itemtype in varchar2,
279 itemkey in varchar2,
280 actid in number,
281 funcmode in varchar2,
282 resultout in out NOCOPY varchar2)
283 IS
284 l_pub_system varchar2(50);
285 l_batch_id number;
286 l_message varchar2(200);
287 l_icc_name varchar2(100);
288 l_sys_status varchar2(10);
289 BEGIN
290 l_batch_id:=wf_engine.GetItemAttrNumber(itemtype => itemtype
291 ,itemkey => itemkey
292 ,aname => 'BATCH_ID');
293 l_pub_system:=wf_engine.GetItemAttrText(itemtype => itemtype
294 ,itemkey => itemkey
295 ,aname => 'PUB_SYSTEM');
296 l_sys_status:=wf_engine.GetItemAttrText(itemtype => itemtype
297 ,itemkey => itemkey
298 ,aname => 'SYSTEM_STATUS');
299 if l_sys_status = 'S' then
300 l_message:='Email notification has been successfully sent.';
301 else
302 l_message:='Errors happened while sending email notification.';
303 end if;
304
305 update EGO_PUB_BAT_STATUS_B
306 set status_code =l_sys_status,
307 message =l_message
308 where batch_id=l_batch_id
309 and system_code=l_pub_system;
310
311 resultout := wf_engine.eng_completed||':'||wf_engine.eng_null;
312 return;
313
314 exception
315 when others then
316 -- The line below records this function call in the error system
317 -- in the case of an exception.
318 wf_core.context('WFPUBICC', 'UPDATE_SYS_STATUS',
319 itemtype, itemkey, to_char(actid), funcmode,SQLERRM);
320 raise;
321 END UPDATE_SYS_STATUS;
322
323 END WF_PUB_ICC;
324
[Home] [Help]