[Home] [Help]
PACKAGE BODY: APPS.ZPB_WF_EVENT
Source
1 PACKAGE BODY ZPB_WF_EVENT AS
2 /* $Header: zpbwfevent.plb 120.5 2007/12/04 16:22:37 mbhat noship $ */
3
4
5 procedure SET_ATTRIBUTES (itemtype in varchar2,
6 itemkey in varchar2,
7 actid in number,
8 funcmode in varchar2,
9 resultout out nocopy varchar2)
10 AS
11
12 ACID number;
13 ACNAME varchar2(300);
14 retval varchar2(4000);
15 l_result varchar2(16);
16 workflowProcess varchar2(30);
17 TaskID number;
18 ACstatusID number;
19 ACstatusCode varchar2(30);
20 owner varchar2(30);
21 ownerID number;
22 respID number;
23 respAppID number;
24 -- l_business_area varchar2(140);
25 l_business_area_id number;
26
27 BEGIN
28
29
30 IF (funcmode = 'RUN') THEN
31
32
33 resultout := 'ERROR';
34
35 ACID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
36 Itemkey => ItemKey,
37 aname => 'ACID');
38
39 l_result := wf_engine.GetItemAttrText(Itemtype => ItemType,
40 Itemkey => ItemKey,
41 aname => 'RESULT');
42
43 ownerID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
44 Itemkey => ItemKey,
45 aname => 'OWNERID');
46 respID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
47 Itemkey => ItemKey,
48 aname => 'RESPID');
49 respAPPID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
50 Itemkey => ItemKey,
51 aname => 'RESPAPPID');
52
53 -- BUSINESS AREA ID.
54 l_business_area_id := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
55 Itemkey => ItemKey,
56 aname => 'BUSINESSAREAID');
57
58
59 fnd_global.apps_initialize(ownerID, respID, RespAppId);
60
61
62 -- Get status and name of AC
63 --
64 -- AGB 11/07/2003 Publish change
65 select STATUS_CODE, NAME, PUBLISHED_BY
66 into ACstatusCode, ACname, OwnerID
67 from zpb_analysis_cycles
68 where ANALYSIS_CYCLE_ID = ACID;
69
70 Owner := ZPB_WF_NTF.ID_to_FNDUser(OwnerID);
71
72
73
74
75 -- set item key for execute concurrent program
76
77
78 wf_engine.SetItemAttrText(Itemtype => ItemType,
79 Itemkey => ItemKey,
80 aname => 'ARG1',
81 avalue => ItemKey);
82
83 -- Set current value of Taskseq [not sure if it is always 1 might be for startup]
84 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
85 Itemkey => ItemKey,
86 aname => 'TASKSEQ',
87 avalue => 0);
88 -- set Cycle ID!
89 -- wf_engine.SetItemAttrNumber(Itemtype => ItemType,
90 -- Itemkey => ItemKey,
91 -- aname => 'ACID',
92 -- avalue => ACID);
93 -- set cycle Name!
94 wf_engine.SetItemAttrText(Itemtype => ItemType,
95 Itemkey => ItemKey,
96 aname => 'ACNAME',
97 avalue => ACNAME);
98
99
100
101 /*
102 -- future use currently just useing ID not display name.
103 -- set business area info if we have it
104 if l_business_area_id is not null or l_business_area_id > 0 then
105
106 -- get business area display name
107 select NAME into l_business_area
108 from zpb_business_areas_vl
109 where BUSINESS_AREA_ID = l_business_area_id;
110
111 -- SET business area display name to BUSINESSAREA in notification
112 wf_engine.SetItemAttrText(Itemtype => ItemType,
113 Itemkey => ItemKey,
114 aname => 'BUSINESSAREA',
115 avalue => l_business_area);
116 end if;
117
118 */
119
120
121 -- globals set to WF attributes
122
123 -- This should be the EPB controller user.
124 wf_engine.SetItemOwner(ItemType => ItemType,
125 ItemKey => ItemKey,
126 owner => owner);
127
128 -- set EPBPerformer to owner name for notifications DEFAULT!
129 wf_engine.SetItemAttrText(Itemtype => ItemType,
130 Itemkey => ItemKey,
131 aname => 'EPBPERFORMER',
132 avalue => owner);
133
134 -- will get error notifications
135 wf_engine.SetItemAttrText(Itemtype => ItemType,
136 Itemkey => ItemKey,
137 aname => 'WF_ADMINISTRATOR',
138 avalue => owner);
139
140
141
142
143
144
145
146 -- wf_engine.SetItemAttrText(Itemtype => ItemType,
147 -- Itemkey => ItemKey,
148 -- aname => 'FNDUSERNAM',
149 -- avalue => owner);
150 --
151 -- wf_engine.SetItemAttrNumber(Itemtype => ItemType,
152 -- Itemkey => ItemKey,
153 -- aname => 'OWNERID',
154 -- avalue => ownerID);
155
156 -- wf_engine.SetItemAttrNumber(Itemtype => ItemType,
157 -- Itemkey => ItemKey,
158 -- aname => 'RESPID',
159 -- avalue => respID);
160
161 -- wf_engine.SetItemAttrNumber(Itemtype => ItemType,
162 -- Itemkey => ItemKey,
163 -- aname => 'RESPAPPID',
164 -- avalue => respAppID);
165
166 resultout := 'COMPLETE';
167 end if;
168
169 return;
170
171 exception
172 when others then
173 WF_CORE.CONTEXT('ZPB_WF_EVENT.SET_ATTRIBUTES', itemtype, itemkey, to_char(actid), funcmode);
174 raise;
175 end SET_ATTRIBUTES;
176
177
178
179 procedure GET_ATTRIBUTES (itemtype in varchar2,
180 itemkey in varchar2,
181 actid in number,
182 funcmode in varchar2,
183 resultout out nocopy varchar2)
184
185 AS
186
187 InstanceID number;
188 ACID number;
189 InstDesc varchar2(300);
190 retval varchar2(4000);
191 l_result varchar2(30);
192 workflowProcess varchar2(30);
193 TaskID number;
194 ACstatusID number;
195 ACstatusCode varchar2(30);
196 owner varchar2(30);
197 ownerID number;
198 respID number;
199 respAppID number;
200 workflow varchar(30);
201 thisRecipient varchar2(100);
202 rolename varchar2(100);
203 relative number;
204 l_errorname varchar(30);
205 l_errormsg varchar2(2000);
206
207
208 BEGIN
209
210
211 IF (funcmode = 'RUN') THEN
212
213 resultout := 'ERROR';
214 relative := 7;
215 -- get and set the BP run desc
216 InstanceID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
217 Itemkey => ItemKey,
218 aname => 'INSTANCEID');
219
220 if InstanceID is not NULL then
221
222 select INSTANCE_DESCRIPTION
223 into InstDesc
224 from ZPB_ANALYSIS_CYCLE_INSTANCES
225 where INSTANCE_AC_ID = InstanceID;
226
227 -- set descripton
228 wf_engine.SetItemAttrText(Itemtype => ItemType,
229 Itemkey => ItemKey,
230 aname => 'INSTANCEDESC',
231 avalue => InstDesc);
232 end if;
233
234
235
236 ACID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
237 Itemkey => ItemKey,
238 aname => 'ACID');
239
240
241 ownerID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
242 Itemkey => ItemKey,
243 aname => 'OWNERID');
244
245
246 -- if result is normal when coming from conc makeinstance then send full notificaton
247
248 select ACTIVITY_RESULT_CODE, ERROR_NAME, ERROR_MESSAGE
249 into l_result, l_errorName, l_errormsg
250 from wf_item_activity_statuses_v
251 where item_type = 'ZPBSCHED' AND ACTIVITY_NAME = 'SUBMIT_CONC_REQUEST'
252 AND ITEM_KEY = ItemKey;
253
254 if upper(l_result) = 'NORMAL' then
255 -- set up users for notifications
256 ZPB_WF_EVENT.SET_AUTHORIZED_USERS (ACID, OwnerID, itemtype, itemkey, instanceID);
257 else
258 -- some error encountered
259 -- BUG 4355208 WF_INVALID_ROLE ancillary corrections for rolename and recipients
260 rolename := zpb_wf_ntf.MakeRoleName(ACID, instanceID, OwnerID);
261 zpb_wf_ntf.SetRole(rolename, relative);
262
263 -- BUG 4407850 06/02/2005 NTF NOT SENT TO CALLER changed ATTRNumber to ATTRTEXT
264 thisRecipient := wf_engine.GetItemAttrText(Itemtype => ItemType,
265 Itemkey => ItemKey,
266 aname => 'EPBPERFORMER');
267
268
269 if zpb_wf_ntf.user_in_role(rolename, thisRecipient) = 'N' then
270 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisRecipient);
271 end if;
272
273 -- BUG 4407850 06/02/2005 NTF NOT SENT TO CALLER changed ATTRNumber to ATTRTEXT
274 thisRecipient := wf_engine.GetItemAttrText(Itemtype => ItemType,
275 Itemkey => ItemKey,
276 aname => 'FNDUSERNAM');
277
278 if zpb_wf_ntf.user_in_role(rolename, thisRecipient) = 'N' then
279 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisRecipient);
280 end if;
281
282 -- reset EPBPERFORMER to ntf role
283 wf_engine.SetItemAttrText(Itemtype => ItemType,
284 Itemkey => ItemKey,
285 aname => 'EPBPERFORMER',
286 avalue => RoleName);
287
288
289 -- set error message
290
291 wf_engine.SetItemAttrText(Itemtype => ItemType,
292 Itemkey => ItemKey,
293 aname => 'ISSUEMSG',
294 avalue => l_result || ': ' || l_errorname || ' ' || l_errormsg);
295
296 end if;
297
298
299 resultout := 'COMPLETE';
300
301 end if;
302
303
304 return;
305
306
307
308 exception
309 when others then
310 WF_CORE.CONTEXT('ZPB_WF_EVENT.GET_ATTRIBUTES', itemtype, itemkey, to_char(actid), funcmode);
311 raise;
312
313
314
315 end GET_ATTRIBUTES;
316
317
318
319 Procedure ACSTART_EVENT(ACID in number,
320 p_start_mem IN VARCHAR2,
321 p_end_mem IN VARCHAR2,
322 p_send_date in date default Null,
323 x_event_key out nocopy varchar2)
324
325 IS
326
327 ACname varchar2(300);
328 ACstatusCode varchar2(30);
329 -- 04/23/03 AGB ZPBSCHED
330
331 workflow varchar2(30);
332 itemkey varchar2(240);
333
334 charDate varchar2(30);
335 owner varchar2(30);
336 ownerID number;
337 respNam varchar2(80);
338 respID number;
339 respAppID number;
340 errbuf varchar2(80);
341 retcode number;
342
343 l_event_name varchar2(50);
344 l_event_key varchar2(250);
345 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
346 taskseq number;
347 l_EVENT_DATA clob;
348 l_send_date date;
349 l_business_area_id number;
350
351
352 BEGIN
353
354 errbuf := ' ';
355
356
357 -- Get status and name of AC
358 --
359 select STATUS_CODE, NAME, PUBLISHED_BY, BUSINESS_AREA_ID
360 into ACstatusCode, ACname, OwnerID, l_business_area_id
361 from zpb_analysis_cycles
362 where ANALYSIS_CYCLE_ID = ACID;
363
364 -- Lookfor and Abort Running process because it is a restart
365 ZPB_WF.CallWFAbort(ACID);
366
367
368 -- ownerID := fnd_global.USER_ID;
369 -- respID := fnd_global.RESP_ID;
370 -- respAppID := fnd_global.RESP_APPL_ID;
371
372
373 --==============================================================
374 -- Get responsiblity for published by user ID
375 --==============================================================
376 respAppID :=210;
377
378 select min(RESPONSIBILITY_ID)
379 into respID
380 from FND_USER_RESP_GROUPS g
381 where USER_ID = OwnerID and RESPONSIBILITY_APPLICATION_ID = respAppID
382 and g.RESPONSIBILITY_ID in (select r.RESPONSIBILITY_ID
383 from FND_RESPONSIBILITY r where RESPONSIBILITY_KEY in
384 ('ZPB_CONTROLLER_RESP', 'ZPB_SUPER_CONTROLLER_RESP', 'ZPB_MANAGER_RESP'));
385
386 -- fnd_global.apps_initialize(ownerID, respID, RespAppId);
387
388 Owner := ZPB_WF_NTF.ID_to_FNDUser(OwnerID);
389 workflow := 'ACIDWFEVENT_START';
390
391 -- create itemkey and event key for workflow - they are the same value
392 charDate := to_char(sysdate, 'MM/DD/YYYY-HH24-MI-SS');
393 l_event_key := rtrim(substr(ACName, 1, 50), ' ') || '-' || to_char(ACID) || '-' || workflow || '-' || charDate ;
394
395 -- set out variable for item_key - Note l_event_key[BES] =item_key[WF]
396 x_event_key := l_event_key;
397 l_event_name := 'oracle.apps.zpb.bp.local.start';
398 l_send_date := p_send_date;
399
400
401 -- Set parameters
402
403 wf_event.AddParameterToList('ACID', ACID, l_parameter_list);
404 wf_event.AddParameterToList('OVERIDE_START_MEM' ,p_start_mem , l_parameter_list);
405 wf_event.AddParameterToList('OVERIDE_END_MEM' ,p_end_mem , l_parameter_list);
406 wf_event.AddParameterToList('RESPAPPID', respAppID, l_parameter_list);
407 wf_event.AddParameterToList('RESPID', respID, l_parameter_list);
408 wf_event.AddParameterToList('OWNERID', OwnerID, l_parameter_list);
409 wf_event.AddParameterToList('OWNER', Owner, l_parameter_list);
410 wf_event.AddParameterToList('ARG1', l_event_key, l_parameter_list);
411 taskseq := 0;
412 wf_event.AddParameterToList('TASKSEQ', taskseq, l_parameter_list);
413 wf_event.AddParameterToList('ACNAME', ACNAME, l_parameter_list);
414 wf_event.AddParameterToList('EPBPERFORMER', Owner, l_parameter_list);
415 wf_event.AddParameterToList('WF_ADMINISTRATOR', Owner, l_parameter_list);
416 -- wf_event.AddParameterToList('FNDUSERNAM', Owner, l_parameter_list);
417 -- caller of program
418 wf_event.AddParameterToList('FNDUSERNAM', ZPB_WF_NTF.ID_to_FNDUser(fnd_global.USER_ID), l_parameter_list);
419
420
421 -- Budnik, A. 1/12/06 B 4947816 add in business area id
422 wf_event.AddParameterToList('BUSINESSAREAID', l_business_area_id, l_parameter_list);
423
424 wf_event.raise(l_event_name, l_event_key, l_event_data, l_parameter_list, l_send_date);
425 l_parameter_list.DELETE;
426
427 commit;
428 return;
429
430 exception
431 when others then
432 raise;
433
434 end ACSTART_EVENT;
435
436
437 procedure SET_AUTHORIZED_USERS (ACID in number,
438 OwnerID in number,
439 itemtype in varchar2,
440 itemkey in varchar2,
441 instanceID in number)
442
443 IS
444
445 rolename varchar2(100);
446 relative number := 7;
447 thisRecipID number;
448 thisRecipient varchar2(100);
449 thisUserID number;
450
451
452 CURSOR c_notify is
453 select user_id
454 from ZPB_BP_EXTERNAL_USERS
455 where ANALYSIS_CYCLE_ID = ACID;
456
457 v_notify c_notify%ROWTYPE;
458
459 BEGIN
460 -- BUG 4355208 WF_INVALID_ROLE moved here from loop below.
461 -- make and set role name we will always generate a role even for one user.
462 rolename := zpb_wf_ntf.MakeRoleName(ACID, instanceID, OwnerID);
463 zpb_wf_ntf.SetRole(rolename, relative);
464 -- end BUG 4355208
465
466 for v_notify in c_notify loop
467
468 thisRecipID := v_notify.user_id;
469 -- convert ID to username
470 thisRecipient:= zpb_wf_ntf.ID_to_FNDUser(thisrecipID);
471
472 if zpb_wf_ntf.user_in_role(rolename, thisRecipient) = 'N' then
473 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisRecipient);
474 -- DBMS_OUTPUT.PUT_LINE('name set2: ' || thisRecipient);
475 end if;
476
477 -- add in shadow if there is one
478 -- thisUserID := zpb_wf_ntf.fnduser_to_ID(thisRecipient);
479 zpb_wf_ntf.add_Shadow(rolename, thisRecipID);
480
481 end loop;
482
483 -- add BP owner as recipient also
484 thisRecipient := zpb_wf_ntf.ID_to_FNDUser(OwnerID);
485
486 if zpb_wf_ntf.user_in_role(rolename, thisRecipient) = 'N' then
487 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisRecipient);
488 zpb_wf_ntf.add_Shadow(rolename, OwnerID);
489 -- DBMS_OUTPUT.PUT_LINE('owner name set: ' || thisRecipient);
490 end if;
491
492 -- BUG 4355208 WF_INVALID_ROLE add in caller if not already there.
493 -- BUG 4407850 06/02/2005 NTF NOT SENT TO CALLER changed ATTRNumber to ATTRTEXT
494 thisRecipient := wf_engine.GetItemAttrText(Itemtype => ItemType,
495 Itemkey => ItemKey, aname => 'FNDUSERNAM');
496
497 if zpb_wf_ntf.user_in_role(rolename, thisRecipient) = 'N' then
498 ZPB_UTIL_PVT.AddUsersToAdHocRole(rolename, thisRecipient);
499 end if;
500
501 -- end BUG 4355208
502
503 wf_engine.SetItemAttrText(Itemtype => ItemType,
504 Itemkey => ItemKey,
505 aname => 'EPBPERFORMER',
506
507 avalue => RoleName);
508
509 return;
510
511 exception
512 when others then
513 raise;
514
515 end SET_AUTHORIZED_USERS;
516
517
518 end ZPB_WF_EVENT;
519