[Home] [Help]
PACKAGE BODY: APPS.GMI_WF_ITEM_ACTIVATION
Source
1 package body gmi_wf_item_activation as
2 /* $Header: gmiitmwb.pls 120.1 2005/10/03 11:52:28 jsrivast noship $ */
3
4 procedure init_wf
5 (
6 /* called via trigger on ic_item_mst */
7 p_item_id in number,
8 p_item_no in varchar2,
9 p_item_um in varchar2,
10 p_item_desc1 in varchar2,
11 p_created_by in varchar2
12 )
13 is
14 l_itemtype varchar2(30) := 'GMWITACT';
15 l_itemkey varchar2(30) := to_char(p_item_id);
16 l_WorkflowProcess varchar2(30) := 'ITEM_ACTIVATION_PROC';
17 l_name wf_users.name%type;
18 l_display_name wf_users.display_name%type;
19 l_errname varchar2(30);
20 l_errmsg varchar2(2000);
21 l_status varchar2(2000);
22 l_errstack varchar2(32000);
23 l_result VARCHAR2 (30);
24 wf_item_exists EXCEPTION ;
25
26 esig_active VARCHAR2(2);
27
28 begin
29 /*Check if the workflow data exists and remove the same for the itemtype and itemkey
30 combination */
31
32
33
34 BEGIN
35 IF (FND_PROFILE.DEFINED('GMI_ERES_ACTIVE')) THEN
36 esig_active := FND_PROFILE.VALUE('GMI_ERES_ACTIVE');
37 IF (esig_active = '1') THEN
38 return; -- if eres active for item abort the workflow
39 END IF;
40 END IF;
41
42
43 IF (wf_item.item_exist (l_itemtype, l_itemkey)) THEN
44 /* Check the status of the root activity */
45 wf_item_activity_status.root_status (l_itemtype, l_itemkey, l_status, l_result);
46 /* If it is not completed then abort the process */
47 IF (l_status = 'COMPLETE')THEN
48 /* Purge the workflow data for workflow key */
49 wf_purge.items(itemtype=> l_itemtype, itemkey=> l_itemkey, docommit=>FALSE);
50 END IF;
51 END IF;
52 EXCEPTION
53 WHEN OTHERS THEN
54 WF_CORE.CONTEXT ('GMI_WF_ITEM_ACTIVATION', 'init_wf', l_itemtype, l_itemkey) ;
55 WF_CORE.GET_ERROR (l_errname, l_errmsg, l_errstack);
56 RAISE;
57 END;
58 BEGIN
59 /* create the process */
60 wf_engine.createprocess (itemtype => l_itemtype,
61 itemkey => l_itemkey,
62 process => l_WorkflowProcess);
63 EXCEPTION
64 WHEN DUP_VAL_ON_INDEX THEN
65 RAISE wf_item_exists ;
66 END ;
67
68 /* get the user name from fnd_user */
69 select
70 user_name
71 into
72 l_name
73 from
74 fnd_user
75 where
76 user_id = p_created_by;
77
78
79 /* the following is a hack to get the display_name */
80 /* it should be replaced by a proper API, and depends upon the */
81 /* fnd_user row containing the user's display_name in the */
82 /* description field */
83 /* BEGIN BUG#2513365 Nayini Vikranth */
84 /* Added the ROWNUM condition to avoid the ON-INSERT error. */
85 select
86 display_name
90 wf_users
87 into
88 l_display_name
89 from
91 where
92 name = l_name
93 and
94 rownum =1;
95 /* END BUG#2513365 */
96
97
98
99 /* set the item attributes */
100 wf_engine.setitemattrnumber (itemtype => l_itemtype,
101 itemkey => l_itemkey, aname => 'ITEM_ID',
102 avalue => p_item_id);
103
104 wf_engine.setitemattrtext(itemtype => l_itemtype,
105 itemkey => l_itemkey, aname => 'ITEM_NO',
106 avalue => p_item_no);
107
108 wf_engine.setitemattrtext(itemtype => l_itemtype,
109 itemkey => l_itemkey, aname => 'ITEM_UM',
110 avalue => p_item_um);
111
112 wf_engine.setitemattrtext(itemtype => l_itemtype,
113 itemkey => l_itemkey, aname => 'ITEM_DESC1',
114 avalue => p_item_desc1);
115
116 wf_engine.setitemattrtext(itemtype => l_itemtype,
117 itemkey => l_itemkey, aname => 'REQNAME',
118 avalue => l_name);
119
120 wf_engine.setitemattrtext(itemtype => l_itemtype,
121 itemkey => l_itemkey,
122 aname => 'REQDISP',
123 avalue => l_display_name);
124
125 wf_engine.startprocess (itemtype => l_itemtype,
126 itemkey =>l_itemkey);
127
128 /* the inactive_ind on the inventory item */
129 /* is set in the trigger, not here! */
130
131 exception
132 WHEN wf_item_exists THEN
133 null;
134 when others then
135 wf_core.context ('GMI_WF_ITEM_ACTIVATION',
136 'INIT_WF',
137 l_itemtype, l_itemkey,
138 p_item_id, p_item_no);
139 wf_core.get_error (l_errname, l_errmsg, l_errstack);
140 if ((l_errname is null) and (sqlcode <> 0))
141 then
142 l_errname := to_char(sqlcode);
143 l_errmsg := sqlerrm(-sqlcode);
144 end if;
145 raise;
146 end init_wf;
147
148 procedure select_approver
149 (
150 p_itemtype in varchar2,
151 p_itemkey in varchar2,
152 p_actid in number,
153 p_funcmode in varchar2,
154 p_result out nocopy varchar2
155 )
156 is
157 l_item_no varchar2(32) :=
158 wf_engine.getitemattrtext (p_itemtype, p_itemkey,
159 'ITEM_NO');
160 l_requestor_name varchar2(100) :=
161 wf_engine.getitemattrtext (p_itemtype, p_itemkey,
162 'REQNAME');
163 l_requestor_display_name varchar2(100) :=
164 wf_engine.getitemattrtext (p_itemtype, p_itemkey,
165 'REQDISP');
166 l_approver_name varchar2(100);
167 l_approver_display_name varchar2(100);
168
169 l_sqlcode number;
170 l_sqlerrm varchar2(512);
171 l_errname varchar2(30);
172 l_errmsg varchar2(2000);
173 l_errstack varchar2(32000);
174 l_hierarchy_flag varchar2(20);
175 l_item_desc varchar2(72);
176
177 selection_cancelled exception;
178 selection_timeout exception;
179 pragma exception_init(selection_cancelled, -20101);
180 pragma exception_init(selection_timeout, -20102);
181 begin
182 if (p_funcmode = 'RUN') then
183 /* Added the code to fix bug 1102815 */
184 IF (FND_PROFILE.DEFINED ('IC$WF_ITEM_HIERARCHY')) THEN
185 l_hierarchy_flag := FND_PROFILE.VALUE ('IC$WF_ITEM_HIERARCHY');
186 IF (l_hierarchy_flag is NULL) THEN
187 p_result := 'COMPLETE:SELERR';
188 return;
189 END IF;
190 END IF;
191 IF (l_hierarchy_flag = 'HRMS') THEN
192 select c.user_name,wu.display_name
193 into l_approver_name,l_approver_display_name
194 from per_assignments_f a, fnd_user b, fnd_user c, wf_roles wu
195 where a.person_id=b.employee_id
196 and a.supervisor_id=c.employee_id
197 and b.user_name = l_requestor_name
198 and wu.name = c.user_name
199 group by c.user_name,wu.display_name;
200 ELSIF (l_hierarchy_flag = 'OPM') THEN
201 SELECT supervisor_user_name, wu.display_name
202 into l_approver_name,l_approver_display_name
203 from ic_item_hierarchy , wf_roles wu
204 where creator_user_name = l_requestor_name
205 and rownum = 1
206 and supervisor_user_name=wu.name;
207 END IF;
208
209 Select ITEM_DESC1 into l_item_desc
210 from IC_ITEM_MST
211 where ITEM_NO=l_ITEM_NO;
212
213 wf_engine.setitemattrtext(itemtype => p_itemtype,
214 itemkey => p_itemkey, aname => 'ITEM_DESC1',
215 avalue => l_item_desc);
216
217 wf_engine.setitemattrtext (itemtype => p_itemtype,
218 itemkey => p_itemkey,
219 aname => 'APPNM',
220 avalue => l_approver_name);
221
222 wf_engine.setitemattrtext (itemtype => p_itemtype,
223 itemkey => p_itemkey,
224 aname => '#FROM_ROLE',
225 avalue => l_approver_name);
226
227 wf_engine.setitemattrtext (itemtype => p_itemtype,
228 itemkey => p_itemkey,
229 aname => 'APPDISP',
230 avalue => l_approver_display_name);
231 p_result := 'COMPLETE:FOUND';
232 return;
233 end if;
234
235 if (p_funcmode = 'CANCEL') then
236 raise selection_cancelled;
237 end if;
238
242
239 if (p_funcmode = 'TIMEOUT') then
240 raise selection_timeout;
241 end if;
243 exception
244 when selection_cancelled then
245 wf_engine.setitemattrtext (p_itemtype,
246 p_itemkey, 'ERRMSG',
247 'The workflow approver selection process was cancelled for item ' ||
248 l_item_no || '.');
249 p_result := 'COMPLETE:SELERR';
250 return;
251 when selection_timeout then
252 wf_engine.setitemattrtext (p_itemtype,
253 p_itemkey, 'ERRMSG',
254 'The workflow approver selection process timed out for item ' ||
255 l_item_no || '.');
256 p_result := 'COMPLETE:SELERR';
257 return;
258 when no_data_found then
259 IF (FND_PROFILE.DEFINED ('IC$WF_DEFAULT_ITEM_APPROVER')) THEN
260 l_approver_name := FND_PROFILE.VALUE ('IC$WF_DEFAULT_ITEM_APPROVER');
261 IF l_approver_name is NULL THEN
262 p_result := 'COMPLETE:SELERR';
263 return;
264 ELSE
265
266 select display_name into l_approver_display_name
267 from wf_roles where name =l_approver_name;
268
269
270 wf_engine.setitemattrtext (itemtype => p_itemtype,
271 itemkey => p_itemkey,
272 aname => 'APPNM',
273 avalue => l_approver_name);
274
275 wf_engine.setitemattrtext (itemtype => p_itemtype,
276 itemkey => p_itemkey,
277 aname => 'APPDISP',
278 avalue => l_approver_display_name);
279 p_result := 'COMPLETE:FOUND';
280 return;
281
282 END IF;
283 ELSE
284 p_result := 'COMPLETE:SELERR';
285 return;
286 END IF;
287 when others then
288 l_sqlcode := sqlcode;
289 l_sqlerrm := sqlerrm (-l_sqlcode);
290 wf_core.get_error (l_errname, l_errmsg, l_errstack);
291 if ((l_errname is null) and (sqlcode <> 0))
292 then
293 l_errname := to_char(sqlcode);
294 l_errmsg := sqlerrm(-sqlcode);
295 end if;
296 wf_engine.setitemattrtext (p_itemtype,
297 p_itemkey, 'ERRMSG',
298 'A database error occurred in ' ||
299 'the workflow approver selection ' ||
300 'process for item ' || l_item_no ||
301 '. Message text: ' || l_errmsg);
302 p_result := 'COMPLETE:SELERR';
303 return;
304 end select_approver;
305
306 procedure activate_item
307 (
308 p_itemtype in varchar2,
309 p_itemkey in varchar2,
310 p_actid in number,
311 p_funcmode in varchar2,
312 p_result out nocopy varchar2
313 )
314 is
315 l_item_id number :=
316 wf_engine.getitemattrnumber (p_itemtype, p_itemkey,
317 'ITEM_ID');
318 l_item_no varchar2(32) :=
319 wf_engine.getitemattrtext (p_itemtype, p_itemkey,
320 'ITEM_NO');
321 l_sqlcode number;
322 l_sqlerrm varchar2(512);
323 l_errname varchar2(30);
324 l_errmsg varchar2(2000);
325 l_errstack varchar2(32000);
326
327 activate_cancelled exception;
328 activate_timeout exception;
329 pragma exception_init(activate_cancelled, -20103);
330 pragma exception_init(activate_timeout, -20104);
331 begin
332 if (p_funcmode = 'RUN')
333 then
334 update
335 ic_item_mst
336 set
337 inactive_ind = 0, trans_cnt = -99
338 where
339 item_id = l_item_id;
340 p_result := 'COMPLETE:ACTIVE';
341 return;
342 end if;
343
344 if (p_funcmode = 'CANCEL')
345 then
346 p_result := 'COMPLETE:INACTIVE';
347 return;
348 end if;
349
350 if (p_funcmode = 'TIMEOUT')
351 then
352 p_result := 'COMPLETE:INACTIVE';
353 return;
354 end if;
355
356 exception
357 when activate_cancelled then
358 wf_engine.setitemattrtext (p_itemtype,
359 p_itemkey, 'ERRMSG',
360 'The workflow item activation process was cancelled for item '||
361 l_item_no || '.');
362 p_result := 'ERROR:';
363 return;
364 when activate_timeout then
365 wf_engine.setitemattrtext (p_itemtype,
366 p_itemkey, 'ERRMSG',
367 'The workflow item activation process timed out for item ' ||
368 l_item_no || '.');
369 p_result := 'ERROR:';
370 return;
371 when no_data_found then
372 p_result := 'ERROR:';
373 return;
374 when others then
375 l_sqlcode := sqlcode;
376 l_sqlerrm := sqlerrm (-l_sqlcode);
377 wf_engine.setitemattrtext (p_itemtype,
378 p_itemkey, 'ERRMSG',
379 'A database error occurred in ' ||
380 'the workflow item activation ' ||
381 'process for item ' || l_item_no ||
382 '. Message text: ' || l_sqlerrm);
383 p_result := 'ERROR:';
384 return;
385 end activate_item;
386
387
388 end gmi_wf_item_activation;