[Home] [Help]
PACKAGE BODY: APPS.ZPB_WF_ERROR
Source
1 PACKAGE BODY ZPB_WF_ERROR AS
2 /* $Header: zpbwferror.plb 120.5 2007/12/04 16:22:19 mbhat noship $ */
3
4
5 -- ======================================================================
6 -- Procedure
7 -- SET_ERROR
8 -- Purpose
9 -- This gets called when an exception is raised to an EPB WF process.
10 -- It gets WF itemkey information for the WF item that just errored and then
11 -- it uses this to get more BP data and send notifications to BP owner.
12 -- It will also set the BP run and task status to ERROR if it can.
13 -- Called by EPB WF error process: ZPB_WFERR as a wf activity.
14 -- History
15 -- abudnik 10/27/2005 Created
16 -- Arguments
17 -- standard WF arguments. procuedure is called by WF activity.
18 -- ======================================================================
19
20 PROCEDURE SET_ERROR(itemtype in VARCHAR2,
21 itemkey in VARCHAR2,
22 actid in NUMBER,
23 funcmode in VARCHAR2,
24 resultout OUT NOCOPY VARCHAR2)
25 IS
26
27
28 l_error_item_type varchar2(8);
29 l_error_item_key varchar2(240);
30 l_ACNAME varchar2(150);
31 l_ACID NUMBER;
32 l_TASKID NUMBER;
33 l_TASKNAME varchar2(140);
34 l_INSTANCEID NUMBER;
35 l_INSTANCEDESC varchar2(140);
36 l_BUSINESSAREA varchar2(140);
37 l_BUSINESSAREAID number;
38 l_requestid number;
39 l_req_status varchar2(1);
40 l_ERRMSG varchar2(240);
41 l_reqlog varchar2(240);
42
43 -- bug 5251227
44 l_ownerID NUMBER;
45 l_RoleName varchar2(320);
46 l_thisRecipient varchar2(100);
47 l_label varchar2(50);
48 l_NewDispName varchar2(360);
49
50 BEGIN
51
52
53 resultout :='ERROR';
54
55 IF (funcmode = 'RUN') THEN
56
57 resultout :='COMPLETE';
58 --
59 -- Get the type and the key of the process that errored out
60 -- these were set in the erroring out process by Execute_Error_Process
61 --Error Item Type EPBCYCLE
62 --Error Item Key bp-15082-2-MANAGE_SUBMISSION-09/04/2005
63 --
64
65
66 l_error_item_key := WF_ENGINE.GetItemAttrText(
67 itemtype => itemtype,
68 itemkey => itemkey,
69 aname => 'ERROR_ITEM_KEY' );
70 l_error_item_type := WF_ENGINE.GetItemAttrText(
71 itemtype => itemtype,
72 itemkey => itemkey,
73 aname => 'ERROR_ITEM_TYPE' );
74
75
76 --
77 -- Get details of the process that errored out using above l_error_item_key
78 -- these were set in the erroring out process by Execute_Error_Process
79
80
81 l_ACNAME := WF_ENGINE.GetItemAttrText(
82 itemtype => l_error_item_type,
83 itemkey => l_error_item_key,
84 aname => 'ACNAME');
85 l_ACID := WF_ENGINE.GetItemAttrNumber(
86 itemtype => l_error_item_type,
87 itemkey => l_error_item_key,
88 aname => 'ACID');
89
90 l_TASKID := WF_ENGINE.GetItemAttrNumber(
91 itemtype => l_error_item_type,
92 itemkey => l_error_item_key,
93 aname => 'TASKID');
94 l_TASKNAME := WF_ENGINE.GetItemAttrText(
95 itemtype => l_error_item_type,
96 itemkey => l_error_item_key,
97 aname => 'TASKNAME');
98 l_INSTANCEID := WF_ENGINE.GetItemAttrNumber(
99 itemtype => l_error_item_type,
100 itemkey => l_error_item_key,
101 aname => 'INSTANCEID');
102 l_INSTANCEDESC := WF_ENGINE.GetItemAttrText(
103 itemtype => l_error_item_type,
104 itemkey => l_error_item_key,
105 aname => 'INSTANCEDESC');
106 l_BUSINESSAREAID := WF_ENGINE.GetItemAttrNumber(
107 itemtype => l_error_item_type,
108 itemkey => l_error_item_key,
109 aname => 'BUSINESSAREAID');
110
111 -- bug 5251227
112 l_OWNERID := WF_ENGINE.GetItemAttrNumber(
113 itemtype => l_error_item_type,
114 itemkey => l_error_item_key,
115 aname => 'OWNERID');
116
117
118
119 -- set business area info if we have it
120 if l_BUSINESSAREAID > 0 then
121
122 -- get business area display name
123 select NAME into l_BUSINESSAREA
124 from zpb_business_areas_vl
125 where BUSINESS_AREA_ID = l_BUSINESSAREAID;
126
127 -- SET business area display name to BUSINESSAREA in notification
128 wf_engine.SetItemAttrText(Itemtype => ItemType,
129 Itemkey => ItemKey,
130 aname => 'BUSINESSAREA',
131 avalue => l_BUSINESSAREA);
132 end if;
133
134
135 -- add shadows for owner b5251227 FOR ERROR MESSAGE=====================
136
137 l_thisRecipient := zpb_wf_ntf.ID_to_FNDUser(l_OWNERID);
138
139 if zpb_wf_ntf.has_Shadow(l_OWNERID) = 'Y' then
140 l_rolename := zpb_wf_ntf.MakeRoleName(l_INSTANCEID, l_TASKID);
141
142 select distinct display_name
143 into l_NewDispName
144 from wf_users
145 where name = l_thisRecipient;
146
147 -- add (And Shadows) display to role dispaly name
148 FND_MESSAGE.SET_NAME ('ZPB', 'ZPB_NTF_ANDSHADOWS');
149 l_label := FND_MESSAGE.GET;
150 l_NewDispName := l_NewDispName || l_label;
151 zpb_wf_ntf.SetRole(l_rolename, 7, l_NewDispName);
152
153 ZPB_UTIL_PVT.AddUsersToAdHocRole(l_rolename, l_thisRecipient);
154 zpb_wf_ntf.add_Shadow(l_rolename, l_OWNERID);
155 else
156 l_rolename := l_thisRecipient;
157 end if;
158
159 if l_rolename is not null then
160
161 wf_engine.SetItemAttrText(Itemtype => ItemType,
162 Itemkey => ItemKey,
163 aname => 'WF_ADMINISTRATOR',
164 avalue => l_rolename);
165 end if;
166
167 -- end b5251227 ====================================================
168
169
170 -- Set attribures for NOTIFICATION if itemtype is ZPBWFERR only.
171 -- for EPBCYCLE or ZPBSCHED these are already set.
172
173 -- BP Name
174 wf_engine.SetItemAttrText(Itemtype => ItemType,
175 Itemkey => ItemKey,
176 aname => 'ACNAME',
177 avalue => l_ACNAME);
178
179 --ACID
180 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
181 Itemkey => ItemKey,
182 aname => 'ACID',
183 avalue => l_ACID);
184
185 --TASKID
186 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
187 Itemkey => ItemKey,
188 aname => 'TASKID',
189 avalue => l_TASKID);
190 --TASKNAME
191 wf_engine.SetItemAttrText(Itemtype => ItemType,
192 Itemkey => ItemKey,
193 aname => 'TASKNAME',
194 avalue => l_TASKNAME);
195
196 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
197 Itemkey => ItemKey,
198 aname => 'INSTANCEID',
199 avalue => l_INSTANCEID);
200
201 wf_engine.SetItemAttrText(Itemtype => ItemType,
202 Itemkey => ItemKey,
203 aname => 'INSTANCEDESC',
204 avalue => l_INSTANCEDESC);
205
206
207 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
208 Itemkey => ItemKey,
209 aname => 'BUSINESSAREAID',
210 avalue => l_BUSINESSAREAID);
211
212
213
214
215 -- 1 if have taskID then set error status for it.
216 if l_TASKID is NOT NULL then
217
218 update zpb_analysis_cycle_tasks
219 set status_code = 'ERROR',
220 LAST_UPDATED_BY = fnd_global.USER_ID,
221 LAST_UPDATE_DATE = SYSDATE,
222 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
223 where task_id = l_TaskID;
224
225 end if;
226
227 -- 2 if have instance ID set BP run status to error.
228 if l_instanceID is NOT NULL then
229
230 update zpb_ANALYSIS_CYCLES
231 set status_code = 'ERROR',
232 LAST_UPDATED_BY = fnd_global.USER_ID,
233 LAST_UPDATE_DATE = SYSDATE,
234 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
235 where ANALYSIS_CYCLE_ID = l_InstanceID;
236
237 update zpb_analysis_cycle_instances
238 set last_update_date = sysdate,
239 LAST_UPDATED_BY = fnd_global.USER_ID,
240 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
241 where instance_ac_id = l_InstanceID;
242
243 else
244 -- return 'NOINFO';
245 return;
246 end if;
247
248 END IF;
249
250
251 return;
252
253
254 exception
255 when others then
256
257 Wf_Core.Context('ZPB_WF_ERROR', 'SET_ERROR', itemtype,
258 itemkey, to_char(actid), funcmode);
259 raise;
260
261 END SET_ERROR;
262
263
264 -- ======================================================================
265 -- Procedure
266 -- SET_CONC_ERROR
267 -- Purpose
268 -- Based on the return code ACTIVITY_RESULT_CODE of the concurrent program
269 -- being run by WF it gets BP data and Conc request data abd sends
270 -- a notification to BP owner. It will also set the BP run and task status
271 -- to ERROR if it can.
272 -- History
273 -- abudnik 10/27/2005 Created
274 -- Arguments
275 -- standard WF arguments. procuedure is called by WF activity.
276 -- ======================================================================
277
278
279 PROCEDURE SET_CONC_ERROR(itemtype in VARCHAR2,
280 itemkey in VARCHAR2,
281 actid in NUMBER,
282 funcmode in VARCHAR2,
283 resultout OUT NOCOPY VARCHAR2)
284 IS
285
286
287 l_error_item_type varchar2(8);
288 l_error_item_key varchar2(240);
289 l_ACNAME varchar2(150);
290 l_ACID NUMBER;
291 l_TASKID NUMBER;
292 l_TASKNAME varchar2(140);
293 l_INSTANCEID NUMBER;
294 l_INSTANCEDESC varchar2(140);
295 l_BUSINESSAREA varchar2(140);
296 l_BUSINESSAREAID number;
297 l_ownerID NUMBER;
298 l_appID NUMBER;
299 l_result_code varchar2(12);
300 l_reqlog varchar2(255);
301 l_req_status varchar2(30);
302 l_req_resultDisp varchar2(30);
303 l_concprgID number;
304 l_ConcName varchar2(240);
305 l_count number;
306
307 l_request_id number;
308 l_req_by number;
309 -- bug 5251227
310 l_RoleName varchar2(320);
311 l_thisRecipient varchar2(100);
312 l_label varchar2(50);
313 l_NewDispName varchar2(360);
314
315
316
317 BEGIN
318
319
320 resultout :='ERROR';
321
322 IF (funcmode = 'RUN') THEN
323
324 resultout :='COMPLETE';
325 --
326 -- Get details of the process that errored out using above itemkey
327 -- these were set in the erroring out process by Execute_Error_Process
328
329
330
331 l_TASKID := WF_ENGINE.GetItemAttrNumber(
332 itemtype => itemtype,
333 itemkey => itemkey,
334 aname => 'TASKID');
335
336 l_INSTANCEID := WF_ENGINE.GetItemAttrNumber(
337 itemtype => itemtype,
338 itemkey => itemkey,
339 aname => 'INSTANCEID');
340
341
342 l_OWNERID := WF_ENGINE.GetItemAttrNumber(
343 itemtype => itemtype,
344 itemkey => itemkey,
345 aname => 'OWNERID');
346
347 l_APPID := WF_ENGINE.GetItemAttrNumber(
348 itemtype => itemtype,
349 itemkey => itemkey,
350 aname => 'RESPAPPID');
351
352 l_BUSINESSAREAID := WF_ENGINE.GetItemAttrNumber(
353 itemtype => itemtype,
354 itemkey => itemkey,
355 aname => 'BUSINESSAREAID');
356
357 -- set Business Area info if we have it
358 if l_BUSINESSAREAID > 0 then
359
360 -- get business area display name
361 select NAME into l_BUSINESSAREA
362 from zpb_business_areas_vl
363 where BUSINESS_AREA_ID = l_BUSINESSAREAID;
364
365
366 -- set business area display name
367 wf_engine.SetItemAttrText(Itemtype => ItemType,
368 Itemkey => ItemKey,
369 aname => 'BUSINESSAREA',
370 avalue => l_BUSINESSAREA);
371
372
373 end if;
374
375
376
377 -- check on the value of ACTIVITY_RESULT_CODE
378 select DISTINCT ACTIVITY_RESULT_CODE, ACTIVITY_RESULT_DISPLAY_NAME
379 into l_result_code, l_req_resultDisp
380 from wf_item_activity_statuses_v
381 where item_type= itemtype
382 and item_key= itemkey
383 and ACTIVITY_RESULT_CODE
384 in ('ERROR', 'CANCELLED', 'TERMINATED', 'WARNING');
385
386
387 if l_result_code in ('ERROR', 'CANCELLED', 'TERMINATED', 'WARNING') then
388
389 dbms_lock.sleep(5);
390 l_request_id := WF_ENGINE.GetItemAttrNumber(
391 itemtype => itemtype,
392 itemkey => itemkey,
393 aname => 'REQUEST_ID');
394
395 --DBMS_OUTPUT.PUT_LINE('request_id: ' || l_request_id);
396 -- should only be one entry. This should be 1 or there is
397 -- something very wrong.
398 SELECT COUNT(r.STATUS_CODE)
399 INTO l_count
400 FROM Fnd_Concurrent_Requests r
401 WHERE r.REQUEST_ID = l_request_id
402 and r.PROGRAM_APPLICATION_ID = l_appID
403 and r.REQUESTED_BY = l_ownerID;
404
405 if l_count = 1 then
406
407 SELECT distinct R.STATUS_CODE, R.LOGFILE_NAME, CONCURRENT_PROGRAM_ID
408 into l_req_status, l_reqlog, l_concprgID
409 FROM Fnd_Concurrent_Requests r
410 WHERE r.REQUEST_ID = l_request_id
411 and r.PROGRAM_APPLICATION_ID = l_appID
412 and r.REQUESTED_BY = l_ownerID;
413
414 -- get concurrent program name
415 select USER_CONCURRENT_PROGRAM_NAME
416 into l_ConcName
417 from fnd_concurrent_programs_vl
418 where APPLICATION_ID = 210
419 and CONCURRENT_PROGRAM_ID = l_concprgID;
420
421 wf_engine.SetItemAttrText(Itemtype => ItemType,
422 Itemkey => ItemKey,
423 aname => 'REGISTER1',
424 avalue => l_ConcName);
425
426 wf_engine.SetItemAttrText(Itemtype => ItemType,
427 Itemkey => ItemKey,
428 aname => 'MSGHISTORY',
429 avalue => l_reqlog);
430
431 wf_engine.SetItemAttrText(Itemtype => ItemType,
432 Itemkey => ItemKey,
433 aname => 'ISSUEMSG',
434 avalue => l_req_resultDisp);
435
436 else
437
438 wf_engine.SetItemAttrText(Itemtype => ItemType,
439 Itemkey => ItemKey,
440 aname => 'ISSUEMSG',
441 avalue => to_char(l_count));
442
443 end if;
444
445 end if;
446
447 -- add shadows for owner b5251227 =============================
448 -- This will use the BP owner ID to find shadows and set the
449 -- role "EPBPERFORMER" with shadows, if there are shadows.
450
451 zpb_wf_ntf.SHADOWS_FOR_EPBPERFORMER(ItemType,
452 ItemKey,
453 0,
454 'EPB_BPOWNERID',
455 resultout);
456
457 -- end b5251227 ====================================================
458
459
460 -- if have taskID then set error status for it.
461 if l_TASKID is NOT NULL then
462
463 update zpb_analysis_cycle_tasks
464 set status_code = 'ERROR',
465 LAST_UPDATED_BY = fnd_global.USER_ID,
466 LAST_UPDATE_DATE = SYSDATE,
467 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
468 where task_id = l_TaskID;
469
470 end if;
471
472 -- if have instance ID set BP run status to error.
473 if l_instanceID is NOT NULL then
474
475 update zpb_ANALYSIS_CYCLES
476 set status_code = 'ERROR',
477 LAST_UPDATED_BY = fnd_global.USER_ID,
478 LAST_UPDATE_DATE = SYSDATE,
479 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
480 where ANALYSIS_CYCLE_ID = l_InstanceID;
481
482 update zpb_analysis_cycle_instances
483 set last_update_date = sysdate,
484 LAST_UPDATED_BY = fnd_global.USER_ID,
485 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
486 where instance_ac_id = l_InstanceID;
487
488 else
489 -- return 'NOINFO';
490 return;
491 end if;
492
493 END IF;
494
495
496 return;
497
498
499
500 exception
501 when others then
502 Wf_Core.Context('ZPB_WF_ERROR', 'SET_conc_ERROR', itemtype,
503 itemkey, to_char(actid), funcmode);
504 raise;
505
506 END SET_CONC_ERROR;
507
508
509
510 END ZPB_WF_ERROR;