[Home] [Help]
PACKAGE BODY: APPS.ZPB_WF
Source
1 PACKAGE BODY ZPB_WF AS
2 /* $Header: zpbwrkfl.plb 120.18 2007/12/04 16:24:57 mbhat ship $ */
3
4
5 Owner varchar2(30);
6 g_owner varchar2(50) := ' ';
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ZPB_WF';
8
9 /*+=========================================================================+
10 | ACStart
11 |
12 | Start a specific AC schedule FROM UI with a Wait. Pause until start date + run
13 | MakeInstance to start new instance.
14 |
15 | Notes:
16 | 1. Manages context for WF.
17 | 2. Reads ZPB_AC schema to get AC info.
18 | 3. Starts WF scheduler.
19 | 4. Submits Concurrent program to manges Workflow Background Engine for
20 | WAIT activities that could pause for days.
21 |
22 | IN
23 | ACID A valid ID from zpb_ac
24 | PublishedBefore If Y then check to see if we should abort and restart.
25 +========================================================================+
26 */
27 procedure ACStart(ACID in number, PublishedBefore in varchar2, isEvent in varchar2 default 'N')
28
29 IS
30
31 ACname varchar2(300);
32 -- 04/23/03 AGB ZPBSCHED
33 itemtype varchar2(8) := 'ZPBSCHED';
34 workflowProcess varchar2(30);
35 itemkey varchar2(240);
36 TaskID number;
37 ParamID number;
38 ACstatusID number;
39 ACstatusCode varchar2(30);
40 WaitMode varchar2(24);
41 StartDateTxt varchar(100);
42 StartDateDt date;
43 charDate varchar2(30);
44 owner varchar2(30) := fnd_global.user_name;
45 ownerID number := fnd_global.USER_ID;
46 respNam varchar2(80) := fnd_global.RESP_NAME;
47 respID number := fnd_global.RESP_ID;
48 --Bug 5223405: Change start
49 --appNam varchar2(3) := fnd_global.APPLICATION_SHORT_NAME;
50 appName FND_APPLICATION.APPLICATION_SHORT_NAME%type := fnd_global.APPLICATION_SHORT_NAME;
51 --Bug 5223405: Change end
52 respAppID number := fnd_global.RESP_APPL_ID;
53 EngItemKey varchar2(200);
54 errbuf varchar2(80);
55 bkgREQID number;
56 InstanceCount number;
57 WfRetcode number;
58 retcode number;
59 rphase varchar2(30);
60 rstatus varchar2(30);
61 dphase varchar2(30);
62 dstatus varchar2(30);
63 message varchar2(240);
64 call_status boolean;
65 request_id number;
66 freqType varchar2(30);
67 schedProfileOption varchar2(80);
68 l_business_area_id number; -- abudnik 17NOV2005 BUSINESS AREA ID
69
70 BEGIN
71
72 errbuf := ' ';
73
74 If PublishedBefore = 'Y' and isEvent = 'N' then
75
76 CallWFAbort(ACID);
77
78 -- Ignore this code for now AGB 02/20/2004
79 -- In future will need to remap instances
80 retcode := 1;
81 end if;
82
83
84 -- Get status and name of AC
85 --
86 -- AGB 11/07/2003 Publish change
87 select STATUS_CODE, NAME, PUBLISHED_BY
88 into ACstatusCode, ACname, OwnerID
89 from zpb_analysis_cycles
90 where ANALYSIS_CYCLE_ID = ACID;
91 Owner := ZPB_WF_NTF.ID_to_FNDUser(OwnerID);
92
93
94 -- Get start date for WF WAIT
95 select TAG as PARAM_ID
96 into paramID
97 from FND_LOOKUP_VALUES_VL WHERE LOOKUP_CODE = 'CALENDAR_START_DATE'
98 and LOOKUP_TYPE = 'ZPB_PARAMS';
99
100 select value
101 Into StartDateTxt
102 from ZPB_AC_PARAM_VALUES
103 where ANALYSIS_CYCLE_ID = ACID and PARAM_ID = paramID;
104
105 StartDateDt := to_date(StartDateTxt, 'YYYY/MM/DD-HH24:MI:SS');
106
107 /*
108 If Sysdate is passed the StartDate, set the StartDate to
109 the Sysdate plus one day at midnight. This ensures that no instances
110 are kicked off during re-publish. The exception is BPs that have
111 their calendar frequency set to either ONE_TIME_ONLY or EVENT.
112 */
113
114
115 begin
116
117 select pva.value into freqType
118 from zpb_ac_param_values pva,
119 fnd_lookup_values_vl pna
120 where pna.lookup_code='CALENDAR_FREQUENCY_TYPE'
121 and pna.tag = pva.param_id and
122 pna.lookup_type = 'ZPB_PARAMS' and
123 pva.analysis_cycle_id=ACID;
124
125 exception
126 when NO_DATA_FOUND then
127 freqType:='NOT_FOUND';
128 end;
129
130
131 if freqType = 'EXTERNAL_EVENT' then
132 return;
133 end if;
134
135
136 schedProfileOption:= FND_PROFILE.VALUE_SPECIFIC('ZPB_BPSCHEDULER_TYPE', OwnerId);
137
138 -- BUG 4291814 - WORKFLOW COMPONENTS: START BP EXTERNALLY
139 if schedProfileOption<>'DEBUG' then
140
141 if freqType <> 'EVENT' and freqType <> 'ONE_TIME_ONLY' and sysdate > StartDateDt then
142
143 StartDateDt := trunc(sysdate+1);
144
145 end if;
146
147
148 end if; -- profile option
149
150
151
152 -- Start the Workflow Process
153 if isEvent = 'Y' then
154 WorkflowProcess := 'ACIDEVENT';
155 else
156 WorkflowProcess := 'SCHEDULER';
157 end if;
158
159 -- abudnik 17NOV2005 BUSINESS AREA ID.
160 select BUSINESS_AREA_ID
161 into l_business_area_id
162 from ZPB_ANALYSIS_CYCLES
163 where ANALYSIS_CYCLE_ID = ACId;
164
165 -- create itemkey for workflow
166 charDate := to_char(sysdate, 'MM/DD/YYYY-HH24-MI-SS');
167 itemkey := rtrim(substr(ACName, 1, 50), ' ') || '-' || to_char(ACID) || '-0-' || workflowprocess || '-' || charDate ;
168
169 -- Create WF start process instance
170 wf_engine.CreateProcess(ItemType => ItemType,
171 itemKey => ItemKey,
172 process => WorkflowProcess);
173
174 -- abudnik 17NOV2005 BUSINESS AREA ID.
175 -- set Bus Area ID!
176 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
177 Itemkey => ItemKey,
178 aname => 'BUSINESSAREAID',
179 avalue => l_business_area_id);
180
181
182 -- set item key for execute concurrent program
183 wf_engine.SetItemAttrText(Itemtype => ItemType,
184 Itemkey => ItemKey,
185 aname => 'ARG1',
186 avalue => ItemKey);
187
188 -- Set current value of Taskseq [not sure if it is always 1 might be for startup]
189 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
190 Itemkey => ItemKey,
191 aname => 'TASKSEQ',
192 avalue => 0);
193 -- set Cycle ID!
194 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
195 Itemkey => ItemKey,
196 aname => 'ACID',
197 avalue => ACID);
198 -- set cycle Name!
199 wf_engine.SetItemAttrText(Itemtype => ItemType,
200 Itemkey => ItemKey,
201 aname => 'ACNAME',
202 avalue => ACNAME);
203
204 -- globals set to WF attributes
205
206 -- This should be the EPB controller user.
207 wf_engine.SetItemOwner(ItemType => ItemType,
208 ItemKey => ItemKey,
209 owner => owner);
210
211 -- set EPBPerformer to owner name for notifications DEFAULT!
212 wf_engine.SetItemAttrText(Itemtype => ItemType,
213 Itemkey => ItemKey,
214 aname => 'EPBPERFORMER',
215 avalue => owner);
216
217 -- will get error notifications
218 wf_engine.SetItemAttrText(Itemtype => ItemType,
219 Itemkey => ItemKey,
220 aname => 'WF_ADMINISTRATOR',
221 avalue => owner);
222
223 wf_engine.SetItemAttrText(Itemtype => ItemType,
224 Itemkey => ItemKey,
225 aname => 'FNDUSERNAM',
226 avalue => owner);
227
228 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
229 Itemkey => ItemKey,
230 aname => 'OWNERID',
231 avalue => ownerID);
232
233 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
234 Itemkey => ItemKey,
235 aname => 'RESPID',
236 avalue => respID);
237
238 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
239 Itemkey => ItemKey,
240 aname => 'RESPAPPID',
241 avalue => respAppID);
242
243 -- end global stuff
244
245
246 wf_engine.SetItemAttrDate(Itemtype => ItemType,
247 Itemkey => ItemKey,
248 aname => 'ABSOLUTE_START_DATE',
249 avalue => StartDateDt);
250
251 -- if calendar frequency is yearly and we have reset the startDate
252 -- make sure that an instance is not immediately created once
253 -- modified start date is reached. Get flag here set wf attribute below
254
255 if freqType='YEARLY' and sysdate<StartDateDt then
256
257 wf_engine.SetItemAttrText(Itemtype => ItemType,
258 Itemkey => ItemKey,
259 aname => 'FREQBEHAVIOR',
260 avalue => 'YEARLYRESET');
261
262 end if;
263
264
265 -- Now that all is created and set: START the PROCESS!
266
267 wf_engine.StartProcess(ItemType => ItemType,
268 ItemKey => ItemKey);
269 -- commit;
270
271 /*+======================================================================+
272 -- abudnik b 4725904 COMMENTING OUT OUR USE OF ZPB BACKGROUND ENGINES.
273 -- THIS WILL NOW BE DONE BY STANDARD WORKFLOW via OAM
274
275 -- WF BACKGROUND ENGINE TO RUN deferred activities like WAIT.
276 call_status := FND_CONCURRENT.GET_REQUEST_STATUS(request_id, 'ZPB', 'ZPB_WFBKGMGR', rphase,rstatus,dphase,dstatus, message);
277
278 if call_status = TRUE then
279 if dphase <> 'RUNNING' then
280 bkgREQID := FND_REQUEST.SUBMIT_REQUEST ('ZPB', 'ZPB_WFBKGMGR', NULL, NULL, FALSE, Itemtype, itemkey );
281 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
282 Itemkey => ItemKey,
283 aname => 'BKGREQID',
284 avalue => bkgREQID);
285 end if;
286 else
287 bkgREQID := FND_REQUEST.SUBMIT_REQUEST ('ZPB', 'ZPB_WFBKGMGR', NULL, NULL, FALSE, Itemtype, itemkey );
288 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
289 Itemkey => ItemKey,
290 aname => 'BKGREQID',
291 avalue => bkgREQID);
292 end if;
293
294 +======================================================================+*/
295
296 -- commit;
297 return;
298
299 exception
300 when NO_DATA_FOUND then
301 Null;
302 when others then
303 errbuf:=substr(sqlerrm, 1, 255);
304 raise;
305
306 end ACStart;
307
308
309 /*+=================================================================+
310 | RUN the next task in the AC that is currently running
311 +=================================================================+*/
312
313 procedure RunNextTask (itemtype in varchar2,
314 itemkey in varchar2,
315 actid in number,
316 funcmode in varchar2,
317 resultout out nocopy varchar2)
318 IS
319
320 -- NOTE: all values are for the WF that is ending need new values set for the
321 -- process to be started.
322
323 CurrtaskSeq number;
324 ACID number;
325 ACNAME varchar2(300);
326 ACstatusCode varchar2(30);
327 InstatusCode varchar2(30);
328 TaskID number;
329 priorTaskID number;
330 owner varchar2(30);
331 ownerID number;
332 respID number;
333 respAppID number;
334 charDate varchar2(30);
335 newitemkey varchar2(240);
336 workflowprocess varchar2(30);
337 TaskName varchar2(256);
338 bkgREQID number;
339 InstanceID number;
340 Marked varchar2(16);
341 l_REQID number;
342 retcode number;
343 rphase varchar2(30);
344 rstatus varchar2(30);
345 dphase varchar2(30);
346 dstatus varchar2(30);
347 message varchar2(240);
348 call_status boolean;
349 request_id number;
350 l_migration varchar2(4000);
351 usr_paused_BP_Name varchar2(30);
352 usr_paused_BP_ID number;
353 l_business_area_id number; -- abudnik 17NOV2005 BUSINESS AREA ID
354 l_business_area varchar2(60); -- A. Budnik 04/12/2006 bugs 3126256, 3856551 and others
355 l_InstanceDesc varchar2(300); -- A. Budnik 04/12/2006 bugs 3126256, 3856551 and others
356
357 CURSOR c_tasks is
358 select *
359 from zpb_analysis_cycle_tasks
360 where ANALYSIS_CYCLE_ID = InstanceID
361 and Sequence = CurrtaskSeq+1;
362 v_tasks c_Tasks%ROWTYPE;
363
364
365 -- 28 MIGRATION_INSTANCE
366 CURSOR c_mparams is
367 select value
368 from ZPB_AC_PARAM_VALUES
369 where ANALYSIS_CYCLE_ID = InstanceID and PARAM_ID = 28;
370
371 v_mparams c_mparams%ROWTYPE;
372
373
374
375 BEGIN
376
377 IF (funcmode = 'RUN') THEN
378 -- Default is do not RUN NEXT TASK. Must have adequate values to run next task.
379 resultout :='COMPLETE:N';
380
381 -- Get current global attributes to run next WF task!
382 CurrtaskSeq := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
383 Itemkey => ItemKey,
384 aname => 'TASKSEQ');
385 ACID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
386 Itemkey => ItemKey,
387 aname => 'ACID');
388 ACNAME := wf_engine.GetItemAttrText(Itemtype => ItemType,
389 Itemkey => ItemKey,
390 aname => 'ACNAME');
391 ownerID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
392 Itemkey => ItemKey,
393 aname => 'OWNERID');
394 respID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
395 Itemkey => ItemKey,
396 aname => 'RESPID');
397 respAppID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
398 Itemkey => ItemKey,
399 aname => 'RESPAPPID');
400 owner := wf_engine.GetItemAttrText(Itemtype => ItemType,
401 Itemkey => ItemKey,
402 aname => 'FNDUSERNAM');
403 InstanceID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
404 Itemkey => ItemKey,
405 aname => 'INSTANCEID');
406 l_InstanceDesc := wf_engine.GetItemAttrText(Itemtype => ItemType,
407 Itemkey => ItemKey,
408 aname => 'INSTANCEDESC');
409 PriorTaskID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
410 Itemkey => ItemKey,
411 aname => 'TASKID');
412
413 -- abudnik 17NOV2005 BUSINESS AREA ID.
414 l_business_area_id := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
415 Itemkey => ItemKey,
416 aname => 'BUSINESSAREAID');
417 l_business_area := wf_engine.GetItemAttrText(Itemtype => ItemType,
418 Itemkey => ItemKey,
419 aname => 'BUSINESSAREA');
420
421
422
423
424 -- agb DISABLE 07/16/2003
425 select STATUS_CODE
426 into ACstatusCode
427 from ZPB_ANALYSIS_CYCLES
428 where ANALYSIS_CYCLE_ID = ACID;
429
430
431 /* agb DISABLE 07/16/2003
432 select STATUS_CODE
433 into ACstatusCode
434 from ZPB_PUBLISHED_CYCLES_V
435 where ANALYSIS_CYCLE_ID = ACID;
436 */
437
438 -- First set prior task to complete - one that just finished.
439 update zpb_analysis_cycle_tasks
440 set status_code = 'COMPLETE',
441 LAST_UPDATED_BY = fnd_global.USER_ID,
442 LAST_UPDATE_DATE = SYSDATE,
443 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
444 where ANALYSIS_CYCLE_ID = InstanceID and TASK_ID = priorTaskID;
445
446 -- Get next task [wf process] to run - if NONE you are COMPLETE!
447 workflowprocess := 'NONE';
448 TaskID := NULL;
449
450 for v_Tasks in c_Tasks loop
451 TaskID := v_Tasks.TASK_ID;
452 workflowprocess := v_Tasks.wf_process_name;
453 taskName := v_Tasks.task_name;
454 end loop;
455
456 select STATUS_CODE
457 into InstatusCode
458 from ZPB_ANALYSIS_CYCLES
459 where ANALYSIS_CYCLE_ID = InstanceID;
460
461 -- If instance is marked for deletion do not mark it as complete
462 -- Clean up the instance measures and quit
463 if InstatusCode = 'MARKED_FOR_DELETION' then
464 l_REQID := FND_REQUEST.SUBMIT_REQUEST ('ZPB', 'ZPB_WF_DELAWINST', NULL, NULL, FALSE, InstanceID, ownerID, l_business_area_id);
465
466 -- Clean up Current Instance Measure if Appropriate
467 DeleteCurrInstMeas(ACID, ownerID);
468
469 resultout :='COMPLETE:N';
470 return;
471 end if;
472
473 -- LAST TASK FOR THIS INSTANCE
474 if workflowprocess = 'NONE' then
475
476 update zpb_ANALYSIS_CYCLES
477 set status_code = 'COMPLETE',
478 LAST_UPDATED_BY = fnd_global.USER_ID,
479 LAST_UPDATE_DATE = SYSDATE,
480 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
481 where ANALYSIS_CYCLE_ID = INSTANCEID;
482
483 update zpb_analysis_cycle_instances
484 set LAST_UPDATED_BY = fnd_global.USER_ID,
485 LAST_UPDATE_DATE = SYSDATE, LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
486 where instance_ac_id = INSTANCEID;
487
488 l_migration := 'N';
489 for v_mparams in c_mparams loop
490 l_migration := v_mparams.VALUE;
491 end loop;
492
493 if l_migration = 'N' then
494 -- Mark for delete
495 zpb_wf.markfordelete(ACID, ownerID, respID, respAppID);
496 end if;
497
498 resultout :='COMPLETE:RUN_COMPLETE';
499 return;
500 end if;
501
502 -- IF PAUSED or PAUSING EXIT.
503
504 if (InstatusCode = 'PAUSED') THEN
505 resultout :='COMPLETE:PAUSED';
506 return;
507 elsif (InstatusCode = 'PAUSING') THEN
508 select LAST_UPDATED_BY into usr_paused_BP_ID
509 from zpb_ANALYSIS_CYCLES where ANALYSIS_CYCLE_ID = INSTANCEID;
510
511 update zpb_ANALYSIS_CYCLES
512 set status_code = 'PAUSED',
513 LAST_UPDATED_BY = fnd_global.USER_ID,
514 LAST_UPDATE_DATE = SYSDATE, LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
515 where ANALYSIS_CYCLE_ID = INSTANCEID;
516
517 update zpb_analysis_cycle_instances
518 set LAST_UPDATED_BY = fnd_global.USER_ID,
519 LAST_UPDATE_DATE = SYSDATE, LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
520 where instance_ac_id = INSTANCEID;
521
522 select user_name into usr_paused_BP_Name from
523 FND_USER where user_id = usr_paused_BP_ID;
524
525 resultout :='COMPLETE:PAUSED';
526 wf_engine.SetItemAttrText(
527 Itemtype => ItemType,
528 Itemkey => ItemKey,
529 aname => '#FROM_ROLE',
530 avalue => usr_paused_BP_Name);
531
532 return;
533 elsif (InstatusCode = 'DISABLE_ASAP') then
534 resultout :='COMPLETE:N';
535 return;
536 elsif (InstatusCode = 'ENABLE_FIRST') then
537 -- if this instance has been marked for enable first, we submit a CM
538 -- that will clean its previous measure out, and restart from Task 1
539 l_REQID := FND_REQUEST.SUBMIT_REQUEST ('ZPB', 'ZPB_CLEANANDRESTARTINST', NULL, NULL, FALSE, ACID, InstanceID, l_business_area_id);
540 resultout :='COMPLETE:N';
541 return;
542
543 end if;
544
545 -- A. Budnik 08/26/04 B: 3856388 Migration and disable_asap
546 -- Do not run if DISABLED!
547 if ACstatusCode = 'DISABLE_ASAP' and l_migration = 'N' then
548 resultout :='COMPLETE:N';
549 return;
550 end if;
551
552
553 -- Set item key and date
554 charDate := to_char(sysdate, 'MM/DD/YYYY-HH24-MI-SS');
555 newitemkey := rtrim(substr(ACName, 1, 50), ' ') || '-' || to_char(ACID) || '-' || to_char(CurrtaskSeq+1) || '-' || workflowprocess || '-' || charDate;
556
557
558 -- +============================================================+
559 -- +============================================================+
560 -- set newItemKey for submit engine mgr proc IN CURRENT PROCESS!
561 -- This will have the bkg engine pushing the new task through.
562 -- +============================================================+
563 wf_engine.SetItemAttrText(Itemtype => ItemType,
564 Itemkey => ItemKey,
565 aname => 'ARG2',
566 avalue => newItemKey);
567 -- +============================================================+
568
569 -- Create WF start process instance
570 wf_engine.CreateProcess(ItemType => ItemType,
571 itemKey => newItemKey,
572 process => WorkflowProcess);
573
574 -- This should be the EPB controller.
575 wf_engine.SetItemOwner(ItemType => ItemType,
576 ItemKey => NEWItemKey,
577 owner => owner);
578
579 -- Set current value of Taskseq [not sure if it is always 1 might be for startup]
580 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
581 Itemkey => newItemKey,
582 aname => 'TASKSEQ',
583 avalue => CurrtaskSeq+1);
584
585 -- +============================================================+
586 -- set globals for new key
587
588 -- abudnik 17NOV2005 BUSINESS AREA ID.
589 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
590 Itemkey => newItemKey,
591 aname => 'BUSINESSAREAID',
592 avalue => l_business_area_id);
593
594 wf_engine.SetItemAttrText(Itemtype => ItemType,
595 Itemkey => newItemKey,
596 aname => 'BUSINESSAREA',
597 avalue => l_business_area);
598
599 -- set Cycle ID!
600 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
601 Itemkey => newItemKey,
602 aname => 'ACID',
603 avalue => ACID);
604
605 -- set workflow with Instance Cycle ID!
606 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
607 Itemkey => newItemKey,
608 aname => 'INSTANCEID',
609 avalue => InstanceID);
610
611 wf_engine.SetItemAttrText(Itemtype => ItemType,
612 Itemkey => newItemKey,
613 aname => 'INSTANCEDESC',
614 avalue => l_InstanceDesc);
615
616 -- set cycle Name!
617 wf_engine.SetItemAttrText(Itemtype => ItemType,
618 Itemkey => newItemKey,
619 aname => 'ACNAME',
620 avalue => ACNAME);
621 -- set Task ID!
622 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
623 Itemkey => newItemKey,
624 aname => 'TASKID',
625 avalue => TaskID);
626 -- set Task Name!
627 wf_engine.SetItemAttrText(Itemtype => ItemType,
628 Itemkey => newItemKey,
629 aname => 'TASKNAME',
630 avalue => TaskName);
631
632 -- set owner name attr!
633 wf_engine.SetItemAttrText(Itemtype => ItemType,
634 Itemkey => newItemKey,
635 aname => 'FNDUSERNAM',
636 avalue => owner);
637
638 -- set EPBPerformer to owner name for notifications DEFAULT!
639 wf_engine.SetItemAttrText(Itemtype => ItemType,
640 Itemkey => newItemKey,
641 aname => 'EPBPERFORMER',
642 avalue => owner);
643
644 -- AGB 11/07/2003 Publish change
645 -- will get error notifications
646 wf_engine.SetItemAttrText(Itemtype => ItemType,
647 Itemkey => newItemKey,
648 aname => 'WF_ADMINISTRATOR',
649 avalue => owner);
650
651 -- set owner ID!
652 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
653 Itemkey => newItemKey,
654 aname => 'OWNERID',
655 avalue => ownerID);
656 -- set resp ID!
657 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
658 Itemkey => newItemKey,
659 aname => 'RESPID',
660 avalue => respID);
661 -- set resp ID!
662 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
663 Itemkey => newItemKey,
664 aname => 'RESPAPPID',
665 avalue => respAppID);
666
667 if workflowprocess = 'EXCEPTION' then
668 -- plsql document procedure
669 wf_engine.SetItemAttrText(itemtype => itemtype,
670 itemkey => newItemKey,
671 aname => 'RESPNOTE',
672 avalue => 'PLSQL:ZPB_EXCEPTION_ALERT.NON_RESPONDERS/' || newItemKey );
673 end if;
674
675 update zpb_analysis_cycle_tasks
676 set item_KEY = newitemkey,
677 Start_date = to_Date(charDate,'MM/DD/YYYY-HH24-MI-SS'),
678 status_code = 'ACTIVE',
679 LAST_UPDATED_BY = fnd_global.USER_ID,
680 LAST_UPDATE_DATE = SYSDATE,
681 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
682 where ANALYSIS_CYCLE_ID = INSTANCEID and task_id = TaskID;
683
684 -- Now that all is created and set START the PROCESS!
685 wf_engine.StartProcess(ItemType => ItemType,
686 ItemKey => newItemKey);
687
688
689 /*+======================================================================+
690 -- abudnik b 4725904 COMMENTING OUT OUR USE OF ZPB BACKGROUND ENGINES.
691 -- THIS WILL NOW BE DONE BY STANDARD WORKFLOW via OAM
692 -- WF BACKGROUND ENGINE TO RUN deferred activities like WAIT.
693 call_status := FND_CONCURRENT.GET_REQUEST_STATUS(request_id, 'ZPB', 'ZPB_WF_START', rphase,rstatus,dphase,dstatus, message);
694
695 if call_status = TRUE then
696 if dphase <> 'RUNNING' then
697 resultout :='COMPLETE:Y';
698 else
699 resultout :='COMPLETE:NOCONC';
700 end if;
701 else
702 resultout :='COMPLETE:Y';
703 end if;
704
705 +======================================================================+*/
706 -- abudnik b 4725904 this resultout will take path in wf process that will
707 -- not run a ZPB background engine.
708 resultout :='COMPLETE:NOCONC';
709
710 END IF;
711 return;
712
713 exception
714
715 when others then
716 -- b5222930 ERROR RAISED IN PROC: RUNNEXTTASK WILL PROVIDE INCORRECT ERROR MESSAGE
717 -- when the error is raised here ZPBWFERR will update zpb status
718 -- commits can not be done inside WF.
719 -- UPDATE_STATUS('ERROR', Instanceid, NULL, NULL);
720
721 WF_CORE.CONTEXT('ZPB_WF.RunNextTask', itemtype, itemkey, to_char(actid), funcmode);
722 raise;
723
724 end RunNextTask;
725
726
727
728 -- RunLoad
729 -- call the load data task for current AC that is running
730
731 procedure RunLoad (itemtype in varchar2,
732 itemkey in varchar2,
733 actid in number,
734 funcmode in varchar2,
735 resultout out nocopy varchar2)
736 IS
737
738
739 CurrtaskSeq number;
740 ACID number;
741 ACNAME varchar2(300);
742 TaskID number;
743 retval varchar2(4000);
744 ownerID number;
745 owner varchar2(30);
746 respID number;
747 respAppID number;
748 sessionID number;
749 ACprgtype varchar2(2) := 'AC';
750 DLcmd varchar2(100);
751 reqID number;
752 CodeAW varchar2(30);
753 DataAW varchar2(30);
754 AnnoAW varchar2(30);
755 thisCount number;
756 returnStat varchar2(1000);
757 msgData varchar2(1000);
758 l_business_area_id number; -- abudnik 17NOV2005 BUSINESS AREA ID
759
760 BEGIN
761
762 IF (funcmode = 'RUN') THEN
763 resultout :='COMPLETE:N';
764
765 CurrtaskSeq := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
766 Itemkey => ItemKey,
767 aname => 'TASKSEQ');
768
769 ACID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
770 Itemkey => ItemKey,
771 aname => 'ACID');
772
773 ACNAME := wf_engine.GetItemAttrText(Itemtype => ItemType,
774 Itemkey => ItemKey,
775 aname => 'ACNAME');
776
777 TaskID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
778 Itemkey => ItemKey,
779 aname => 'TASKID');
780
781 ownerID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
782 Itemkey => ItemKey,
783 aname => 'OWNERID');
784
785
786 respID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
787 Itemkey => ItemKey,
788 aname => 'RESPID');
789
790 owner := wf_engine.GetItemAttrText(Itemtype => ItemType,
791 Itemkey => ItemKey,
792 aname => 'FNDUSERNAM');
793
794 respAppID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
795 Itemkey => ItemKey,
796 aname => 'RESPAPPID');
797
798 -- abudnik 17NOV2005 BUSINESS AREA ID.
799 l_business_area_id := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
800 Itemkey => ItemKey,
801 aname => 'BUSINESSAREAID');
802
803 -- COMMAND TO RUN ON aw
804 -- DLcmd := 'call wf.call.mgr(' || taskID || ');';
805 -- 'zpbdata',
806 -- 'zpbcode',
807
808 DLcmd := 'call wf.call.mgr(' || taskID || ');';
809
810
811 ZPB_AW.INITIALIZE_FOR_AC (p_api_version => 1.0,
812 p_init_msg_list => FND_API.G_TRUE,
813 x_return_status => returnStat,
814 x_msg_count => thisCount,
815 x_msg_data => msgData,
816 p_analysis_cycle_id => ACID,
817 p_shared_rw => FND_API.G_FALSE);
818
819 DataAW := ZPB_AW.GET_SHARED_AW;
820 CodeAW := ZPB_AW.GET_CODE_AW;
821 AnnoAW := ZPB_AW.GET_ANNOTATION_AW;
822
823 -- abudnik 17NOV2005 BUSINESS AREA ID.
824 reqID := FND_REQUEST.SUBMIT_REQUEST ('ZPB', 'ZPB_WF_TO_AW', NULL, NULL, FALSE, ACID, taskID, dataAW, CodeAW, AnnoAW, l_business_area_id);
825
826 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
827 Itemkey => ItemKey,
828 aname => 'REQUEST_ID',
829 avalue => reqID);
830
831 resultout :='COMPLETE:Y';
832
833 END IF;
834 return;
835
836 exception
837 when others then
838
839 -- b5222930 ERROR RAISED IN PROC: RUNNEXTTASK WILL PROVIDE INCORRECT ERROR MESSAGE
840 -- when the error is raised here ZPBWFERR will update zpb status
841 -- commits can not be done inside WF.
842 -- UPDATE_STATUS('ERROR', NULL, taskid, NULL);
843
844 WF_CORE.CONTEXT('ZPB_WF.RunLoad', itemtype, itemkey, to_char(actid), funcmode);
845 raise;
846
847 end RunLoad;
848
849 /*+=================================================================+
850 | Selector
851 | ZPB_CONTROLLER_RESP 56073, ZPB 210, USER ID: ownerID
852 |
853 | This is an ItemType Selector function that contains callback
854 | functionality to reestablish context for each process in our
855 | ItemType. This is a requierment of the Concurrent Manager
856 | Standard Activity to be sure that the context is set up by
857 | calling:
858 | fnd_global.apps_initialize(user_id, resp_id, resp_appl_id)
859 |
860 | IN
861 | Set of arguments for Standard Activity Function:
862 | itemtype - A valid item type from WF_ITEM_TYPES table.
863 | itemkey - string generated as WF primary key.
864 | actid - An Activity ID.
865 | command - Oracle Workflow calls selector/callback function with
866 | following commands: 'RUN', 'TEST_CTX'.
867 |
868 | OUT
869 | resultout - A result that can be returned.
870 +==================================================================+*/
871
872 procedure Selector(itemtype in varchar2,
873 itemkey in varchar2,
874 actid in number,
875 command in varchar2,
876 resultout out nocopy varchar2)
877 IS
878
879 ownerID number;
880 respID number;
881 respAppID number;
882 l_wfprocess varchar2(30);
883
884 BEGIN
885
886
887 select root_activity into l_wfprocess
888 from wf_items_v
889 where item_key = ItemKey;
890
891
892 IF (command = 'SET_CTX') THEN
893
894 ownerID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
895 Itemkey => ItemKey,
896 aname => 'OWNERID');
897
898 respID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
899 Itemkey => ItemKey,
900 aname => 'RESPID');
901
902 respAppID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
903 Itemkey => ItemKey,
904 aname => 'RESPAPPID');
905
906
907 if l_wfprocess <> 'CALCSYNCPRC' then
908 fnd_global.apps_initialize(ownerID, respID, RespAppId);
909 end if;
910
911 resultout := 'COMPLETE';
912 return;
913
914 ELSIF(command = 'TEST_CTX') THEN
915
916 ownerID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
917 Itemkey => ItemKey,
918 aname => 'OWNERID');
919
920 respID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
921 Itemkey => ItemKey,
922 aname => 'RESPID');
923
924 respAppID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
925 Itemkey => ItemKey,
926 aname => 'RESPAPPID');
927
928 if l_wfprocess <> 'CALCSYNCPRC' then
929 fnd_global.apps_initialize(ownerID, respID, RespAppId);
930 end if;
931
932 return;
933
934 ELSE
935
936 resultout := 'COMPLETE';
937 return;
938
939 END IF;
940
941 exception
942
943 when others then
944 WF_CORE.CONTEXT('ZPB_WF.Selector', itemtype, itemkey,
945 to_char(actid), command);
946 raise;
947 end Selector;
948
949
950 -- Runs WF_ENGINE.BACKGROUND to monitor and interface with any deffered activities.
951 -- Currently used for WAIT activites.
952
953 procedure WFbkgMgr (errbuf out nocopy varchar2,
954 retcode out nocopy number,
955 itemtype in varchar2,
956 itemkey in varchar2)
957 IS
958 status_code varchar2(30);
959 seconds number;
960 status varchar2(30);
961 itemsToProcess number;
962
963 BEGIN
964 seconds := 10;
965 errbuf := ' ';
966 retcode := 0;
967 status := 'NONE';
968
969 -- run Background Engine Program in the loop for each Deferred
970 -- Activity until no Deferred or notified Activities are found.
971 -- Itemtype and Itemkey for future use
972
973 Loop
974 -- BUG 4517776 - SELECT ON WF VIEW WITHIN ZPB_WFBKGMGR SLOW
975 select count(*)
976 into itemsToProcess
977 from wf_items_v v
978 WHERE v.ITEM_TYPE = 'ZPBSCHED' AND end_date is NULL
979 AND v.item_key in (select t.item_key from WF_ITEM_ACTIVITY_STATUSES t
980 where t.item_type = 'ZPBSCHED' AND t.ACTIVITY_STATUS IN ('DEFERRED', 'NOTIFIED'));
981
982 if itemsToProcess > 0 then
983 -- giving it a few second to propigate to AQ tables.
984 dbms_lock.sleep(seconds);
985 wf_engine.Background(itemtype);
986 else
987 dbms_lock.sleep(seconds);
988
989 -- One last look before exit
990 select count(*)
991 into itemsToProcess
992 from wf_items_v v
993 WHERE v.ITEM_TYPE = 'ZPBSCHED' AND end_date is NULL
994 AND v.item_key in (select t.item_key from WF_ITEM_ACTIVITY_STATUSES t
995 where t.item_type = 'ZPBSCHED' AND t.ACTIVITY_STATUS IN ('DEFERRED', 'NOTIFIED'));
996
997 if itemsToProcess = 0 then
998 exit;
999 end if;
1000 end if;
1001
1002 end loop;
1003
1004 return;
1005
1006 exception
1007 when others then
1008 retcode := 2;
1009 errbuf:=substr(sqlerrm, 1, 255);
1010
1011 end WFBkgMgr;
1012
1013
1014 -- Runs WF_ENGINE.BACKGROUND to monitor and interface with concurrent
1015 -- programs. For WirteBack manager.
1016 -- 04/23/03 agb ZPBSCHED
1017 procedure STARTPRCMGR (errbuf out nocopy varchar2,
1018 retcode out nocopy number,
1019 TGT_ITEMTYPE in varchar2,
1020 TGT_ITEMKEY in varchar2)
1021 IS
1022
1023 status_code varchar2(30);
1024 seconds number;
1025 status varchar2(30);
1026 itemsToProcess number;
1027
1028 BEGIN
1029 seconds := 10;
1030 errbuf := ' ';
1031 retcode := 0;
1032 status := 'NONE';
1033
1034 -- run Background Engine Program in the loop for each Deferred
1035 -- Activity until no Deferred or notified Activities are found.
1036 -- TGT_ITEMTYPE and TGT_ITEMKEY for future use.
1037
1038 Loop
1039 -- BUG 4517776 - SELECT ON WF VIEW WITHIN ZPB_WFBKGMGR SLOW
1040 select count(*)
1041 into itemsToProcess
1042 from wf_items_v v
1043 WHERE v.ITEM_TYPE = 'EPBCYCLE' AND end_date is NULL
1044 AND v.item_key in (select t.item_key from WF_ITEM_ACTIVITY_STATUSES t
1045 where t.item_type = 'EPBCYCLE' AND t.ACTIVITY_STATUS IN ('DEFERRED', 'NOTIFIED'));
1046
1047 if itemsToProcess > 0 then
1048 -- giving it a few second to propigate to AQ tables.
1049 dbms_lock.sleep(seconds);
1050 wf_engine.Background(TGT_ITEMTYPE);
1051 else
1052 dbms_lock.sleep(seconds);
1053 -- One last look before exit
1054 select count(*)
1055 into itemsToProcess
1056 from wf_items_v v
1057 WHERE v.ITEM_TYPE = 'EPBCYCLE' AND end_date is NULL
1058 AND v.item_key in (select t.item_key from WF_ITEM_ACTIVITY_STATUSES t
1059 where t.item_type = 'EPBCYCLE' AND t.ACTIVITY_STATUS IN ('DEFERRED', 'NOTIFIED'));
1060
1061 if itemsToProcess = 0 then
1062 exit;
1063 end if;
1064 end if;
1065
1066 end loop;
1067
1068 return;
1069
1070 exception
1071 when others then
1072 retcode := 2;
1073 errbuf:=substr(sqlerrm, 1, 255);
1074
1075
1076 end STARTPRCMGR;
1077
1078
1079
1080 -- Purges [deletes] completed and aborts and purges active workflows
1081 -- for the defintion AC_ID or INSTANCE_AC_ID passed in.
1082 -- If ACID is ths argument all of its WF instances will be purged.
1083 -- If INSTANCE_AC_ID is passed in, just that one is purged.
1084 -- 04/23/03 agb ZPBSCHED support for many item types.
1085
1086 procedure DeleteWorkflow (errbuf out nocopy varchar2,
1087 retcode out nocopy varchar2,
1088 inACID in Number,
1089 ACIDType in varchar2 default 'I')
1090 IS
1091 --ItemType varchar2(20);
1092 AttrName varchar2(30);
1093 CurrStatus varchar2(20);
1094 result varchar2(100);
1095 -- agb 01/21/02 added for select below
1096 -- 04/23/03 agb ZPBSCHED Need to take this further and eliminate item type restriction.
1097 CURSOR c_ItemKeys is
1098 select item_type, item_key
1099 from WF_ITEM_ATTRIBUTE_VALUES
1100 where (item_type = 'ZPBSCHED' OR item_type = 'EPBCYCLE')
1101 and name = AttrName
1102 and number_value = inACID;
1103
1104 v_ItemKey c_ItemKeys%ROWTYPE;
1105
1106 BEGIN
1107
1108 -- agb 01/21/02 convert to text for some selects
1109 -- 04/23/03 agb ZPBSCHED
1110 -- ItemType := 'EPBCYCLE';
1111 retcode := '0';
1112
1113 if ACIDType = 'I' then
1114 AttrName := 'INSTANCEID';
1115 else
1116 AttrName := 'ACID';
1117 end if;
1118
1119 -- Check activity process for current plan
1120 -- 04/23/03 agb ZPBSCHED support for many item types
1121 for v_ItemKey in c_ItemKeys loop
1122
1123 wf_engine.ItemStatus(v_ItemKey.item_type, v_ItemKey.item_key, currStatus, result);
1124
1125 if UPPER(RTRIM(currStatus)) = 'COMPLETE' then
1126 WF_PURGE.Total(v_ItemKey.item_Type, v_ItemKey.item_key);
1127 elsif UPPER(RTRIM(currStatus)) = 'ERROR' or UPPER(RTRIM(currStatus)) = 'ACTIVE' then
1128 WF_ENGINE.AbortProcess(v_ItemKey.item_Type, v_ItemKey.item_key);
1129 WF_PURGE.Total(v_ItemKey.item_Type, v_ItemKey.item_key);
1130 elsif UPPER(RTRIM(currStatus)) = 'SUSPENDED' then
1131 NULL;
1132 else
1133 retcode := '2';
1134 errbuf:='Plan has an ACTIVE process and Workflow cannot be deleted.';
1135 -- exit;
1136 end if;
1137
1138 end loop;
1139 return;
1140
1141 exception
1142
1143 when NO_DATA_FOUND then
1144 retcode :='0';
1145 when others then
1146 retcode :='2';
1147 errbuf:=substr(sqlerrm, 1, 255);
1148 -- raise;
1149
1150 end DeleteWorkflow;
1151
1152
1153 -- Wrapper to call DeleteWorkflow and clean zpb_excp* tables
1154 Procedure CallDelWF(inACID in number, ACIDType in varchar2 default 'I')
1155 is
1156
1157 thisInst number;
1158 retcode varchar2(2);
1159 errbuf varchar2(100);
1160
1161 CURSOR c_instances is
1162 select instance_ac_id
1163 from zpb_analysis_cycle_instances
1164 where analysis_cycle_id = inACID;
1165
1166 v_instance c_instances%ROWTYPE;
1167
1168
1169 BEGIN
1170
1171 -- find all workflows for this ACID or instance, abort and purge.
1172 ZPB_WF.DeleteWorkflow(errbuf, retcode, inACID, ACIDType);
1173
1174 -- The default date setting for exec wf_purge.adhocdirectory is sysdate.
1175 -- This will purge out any ad hoc roles or users I've generated based on the expiration_date
1176 -- set in wf_directory.CreateAdHocRole. This is a standard WF API.
1177 wf_purge.adhocdirectory;
1178
1179 -- Delete task rows from zpb_excp_results, zpb_exp_explanations by instance
1180
1181 if ACIDType = 'I' then
1182 delete from zpb_excp_results re
1183 where re.task_id in (select pd.task_id from zpb_process_details_v pd
1184 where analysis_cycle_id = inACID);
1185 delete from zpb_excp_explanations ex
1186 where ex.task_id in (select pd.task_id from zpb_process_details_v pd
1187 where analysis_cycle_id = inACID);
1188 else
1189
1190 for v_instance in c_instances loop
1191 thisInst := v_instance.instance_ac_id;
1192 delete from zpb_excp_results re
1193 where re.task_id in (select pd.task_id from zpb_process_details_v pd
1194 where analysis_cycle_id = thisInst);
1195 delete from zpb_excp_explanations ex
1196 where ex.task_id in (select pd.task_id from zpb_process_details_v pd
1197 where analysis_cycle_id = thisInst);
1198 end loop;
1199
1200 end if;
1201
1202 return;
1203
1204 exception
1205
1206 when others then
1207 RAISE_APPLICATION_ERROR(-20100, 'Error in ZPB_WF.CallDelWF');
1208 end CallDelWF;
1209
1210 --BPEXT
1211 PROCEDURE updateHorizonParams(p_start_mem IN VARCHAR2
1212 ,p_end_mem IN VARCHAR2
1213 ,new_ac_id IN NUMBER)
1214 is
1215 CURSOR records_cur IS
1216 SELECT AcParamValuesEO.VALUE,ParamsEO.lookup_code
1217 FROM ZPB_AC_PARAM_VALUES AcParamValuesEO,
1218 FND_LOOKUP_VALUES_VL ParamsEO
1219 WHERE AcParamValuesEO.PARAM_ID = ParamsEO.TAG and
1220 ParamsEO.LOOKUP_TYPE = 'ZPB_PARAMS' and ParamsEO.TAG in (4,5,6,7,8,9,10,11,12,13,14,15,16,17) and AcParamValuesEO.ANALYSIS_CYCLE_ID = new_ac_id;
1221 opRec records_cur%ROWTYPE;
1222 l_tmpVar varchar2(4000);
1223 l_retStat varchar2(1);
1224 l_msgCnt number;
1225 l_msgData varchar2(2000);
1226 BEGIN
1227 IF (p_start_mem IS NOT NULL ) THEN
1228 UPDATE zpb_ac_param_values SET value = p_start_mem,
1229 LAST_UPDATED_BY = fnd_global.USER_ID,
1230 LAST_UPDATE_DATE = SYSDATE,
1231 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1232 WHERE
1233 analysis_cycle_id = new_ac_id AND param_id =
1234 ( SELECT tag FROM fnd_lookup_values_vl WHERE lookup_type = 'ZPB_PARAMS' AND
1235 lookup_code = 'CAL_HS_TIME_MEMBER');
1236 END IF;
1237
1238 IF (p_end_mem IS NOT NULL ) THEN
1239 UPDATE zpb_ac_param_values SET value = p_end_mem,
1240 LAST_UPDATED_BY = fnd_global.USER_ID,
1241 LAST_UPDATE_DATE = SYSDATE,
1242 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1243 WHERE
1244 analysis_cycle_id = new_ac_id AND param_id =
1245 ( SELECT tag FROM fnd_lookup_values_vl WHERE lookup_type = 'ZPB_PARAMS' AND
1246 lookup_code = 'CAL_HE_TIME_MEMBER');
1247 END IF;
1248 --the following code adds two more params to zpb_ac_params_values
1249 -- to store start and end periods for the running BP so as to display
1250 --the hgrid in the dimensions tab
1251 l_tmpVar := null;
1252 l_tmpVar := 'call dl.getstartendnames('''||new_ac_id||''' ''';
1253 for opRec in records_cur loop
1254 l_tmpVar := l_tmpVar || opRec.lookup_code || ','||opRec.value ||':';
1255 end loop;
1256 l_tmpVar := l_tmpVar || ''' ''FROM_MAKE_INSTANCE'' )';
1257
1258 zpb_aw.initialize (p_api_version => 1.0,
1259 x_return_status => l_retStat,
1260 x_msg_count => l_msgCnt,
1261 x_msg_data => l_msgData,
1262 p_business_area_id => sys_context('ZPB_CONTEXT', 'business_area_id'),
1263 p_shadow_id => sys_context('ZPB_CONTEXT','user_id'),
1264 p_shared_rw => FND_API.G_FALSE);
1265 zpb_aw.execute(l_tmpVar);
1266
1267 return ;
1268 exception
1269 when others then
1270 raise;
1271
1272
1273 END updateHorizonParams;
1274 --BPEXT
1275
1276 procedure MakeInstance (errbuf out nocopy varchar2,
1277 retcode out nocopy varchar2,
1278 ItemKey in varchar2,
1279 ACID in number,
1280 p_business_area_id in number)
1281 IS
1282
1283 ItemType varchar2(8) := 'ZPBSCHED';
1284 InstItemType varchar2(8) := 'EPBCYCLE';
1285 outInstanceID number;
1286 CurrtaskSeq number;
1287 ACNAME varchar2(300);
1288 ACstatusCode varchar2(30);
1289 TaskID number;
1290 taskName varchar2(256);
1291 owner varchar2(30);
1292 ownerID number;
1293 respID number;
1294 respAppID number;
1295 charDate varchar2(30);
1296 newitemkey varchar2(240);
1297 workflowprocess varchar2(30);
1298 bkgREQID number;
1299 InstanceID number;
1300 rphase varchar2(30);
1301 rstatus varchar2(30);
1302 dphase varchar2(30);
1303 dstatus varchar2(30);
1304 message varchar2(240);
1305 call_status boolean;
1306 request_id number;
1307 overide_start_mem VARCHAR2(240);
1308 overide_end_mem VARCHAR2(240);
1309 l_BUSINESSAREA varchar2(60); -- A. Budnik 04/12/2006 bugs 3126256, 3856551
1310 l_InstanceDesc varchar2(300); -- A. Budnik 04/12/2006 bugs 3126256, 3856551
1311
1312 BEGIN
1313
1314 select STATUS_CODE
1315 into ACstatusCode
1316 -- from ZPB_PUBLISHED_CYCLES_V
1317 from ZPB_ANALYSIS_CYCLES
1318 where ANALYSIS_CYCLE_ID = ACID;
1319
1320 -- Do not run if DISABLED!
1321 if instr(ACstatusCode, 'DISABLE') > 0 then
1322 retcode :='0';
1323 return;
1324 end if;
1325
1326 -- Do not run if MARKED_FOR_DELETED!
1327 if instr(ACstatusCode, 'MARKED_FOR_DELETION') > 0 then
1328 retcode :='0';
1329 return;
1330 end if;
1331
1332 -- Begining the creation of a new Business Process Run
1333 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_WF_NEW_BP_START');
1334 FND_FILE.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
1335
1336 -- call ops prg to make new instance
1337 ZPB_AC_OPS.CREATE_NEW_INSTANCE(ACID, outInstanceID);
1338
1339 -- update the horizon params if they exist
1340 overide_start_mem := wf_engine.GetItemAttrText(Itemtype => ItemType,
1341 Itemkey => ItemKey,
1342 aname => 'OVERIDE_START_MEM',
1343 ignore_notfound => true);
1344
1345 overide_end_mem := wf_engine.GetItemAttrText(Itemtype => ItemType,
1346 Itemkey => ItemKey,
1347 aname => 'OVERIDE_END_MEM',
1348 ignore_notfound => true);
1349 updateHorizonParams(overide_start_mem, overide_end_mem, outInstanceID);
1350
1351
1352 -- set workflow with Instance Cycle ID!
1353 -- This will be respective to the Scheduler or and Event Task
1354 -- GET the correct ITEMTYPE for this run
1355 select ITEM_TYPE into ItemType from WF_ITEMS_V
1356 where item_key = ItemKey;
1357
1358 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
1359 Itemkey => ItemKey,
1360 aname => 'INSTANCEID',
1361 avalue => outInstanceID);
1362
1363 instanceID := outInstanceID;
1364
1365 ACNAME := wf_engine.GetItemAttrText(Itemtype => ItemType,
1366 Itemkey => ItemKey,
1367 aname => 'ACNAME');
1368 ownerID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
1369 Itemkey => ItemKey,
1370 aname => 'OWNERID');
1371 -- AGB 11/07/2003 Publish change
1372 Owner := ZPB_WF_NTF.ID_to_FNDUser(OwnerID);
1373 respID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
1374 Itemkey => ItemKey,
1375 aname => 'RESPID');
1376 respAppID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
1377 Itemkey => ItemKey,
1378 aname => 'RESPAPPID');
1379
1380 -- +=================================================================+
1381 -- Get next task [wf process] to run
1382
1383 select WF_Process_Name, TASK_ID, TASK_NAME
1384 into workflowprocess, TaskID, taskName
1385 from zpb_analysis_cycle_tasks
1386 where ANALYSIS_CYCLE_ID = InstanceID and Sequence = 1;
1387
1388
1389
1390 -- +=================================================================+
1391
1392 -- b 5170327 intializes the pv_status variable
1393 ZPB_ERROR_HANDLER.INIT_CONC_REQ_STATUS;
1394
1395 -- currently calls zpb_gen_phys_model.gen_physical_model + in.run.init
1396 ZPB_WF.INIT_BUSINESS_PROCESS(ACID, outInstanceID, TaskID, ownerID);
1397
1398 -- Set item key and date
1399 charDate := to_char(sysdate, 'MM/DD/YYYY-HH24-MI-SS');
1400 newitemkey := rtrim(substr(ACName, 1, 50), ' ') || '-' || to_char(ACID) || '-1-' || workflowprocess || '-' || charDate;
1401
1402 -- +============================================================+
1403 -- set newItemKey for submit engine mgr proc IN CURRENT PROCESS!
1404 wf_engine.SetItemAttrText(Itemtype => ItemType,
1405 Itemkey => ItemKey,
1406 aname => 'ARG2',
1407 avalue => newItemKey);
1408
1409 -- +============================================================+
1410 -- 04/23/03 agb ZPBSCHED support for additional item type
1411 -- Next task New process
1412
1413 -- Create WF start process instance
1414 wf_engine.CreateProcess(ItemType => InstItemType,
1415 itemKey => newItemKey,
1416 process => WorkflowProcess);
1417
1418 -- This should be the EPB controller.
1419 wf_engine.SetItemOwner(ItemType => InstItemType,
1420 ItemKey => NEWItemKey,
1421 owner => owner);
1422
1423 -- Set current value of Taskseq always 1 for startup
1424 wf_engine.SetItemAttrNumber(Itemtype => InstItemType,
1425 Itemkey => newItemKey,
1426 aname => 'TASKSEQ',
1427 avalue => 1);
1428
1429 -- set globals for new key
1430 -- set Cycle ID! Scheduler or Target event ACID ##########################################
1431 wf_engine.SetItemAttrNumber(Itemtype => InstItemType,
1432 Itemkey => newItemKey,
1433 aname => 'ACID',
1434 avalue => ACID);
1435
1436 --***********************************************************************
1437 -- abudnik 17NOV2005 BUSINESS AREA ID.
1438 wf_engine.SetItemAttrNumber(Itemtype => InstItemType,
1439 Itemkey => newItemKey,
1440 aname => 'BUSINESSAREAID',
1441 avalue => p_business_area_id);
1442
1443 -- get business area display name
1444 select NAME into l_BUSINESSAREA
1445 from zpb_business_areas_vl
1446 where BUSINESS_AREA_ID = p_business_area_id;
1447
1448 -- SET business area display name to BUSINESSAREA in notification
1449 wf_engine.SetItemAttrText(Itemtype => InstItemType,
1450 Itemkey => newItemKey,
1451 aname => 'BUSINESSAREA',
1452 avalue => l_BUSINESSAREA);
1453
1454
1455 -- set workflow with Instance Cycle ID!
1456 wf_engine.SetItemAttrNumber(Itemtype => InstItemType,
1457 Itemkey => newItemKey,
1458 aname => 'INSTANCEID',
1459 avalue => InstanceID);
1460
1461 select INSTANCE_DESCRIPTION
1462 into l_InstanceDesc
1463 from zpb_analysis_cycle_instances
1464 where instance_ac_id = InstanceID;
1465
1466
1467 wf_engine.SetItemAttrText(Itemtype => InstItemType,
1468 Itemkey => newItemKey,
1469 aname => 'INSTANCEDESC',
1470 avalue => l_InstanceDesc);
1471 --***********************************************************************
1472
1473
1474 -- set cycle Name!
1475 wf_engine.SetItemAttrText(Itemtype => InstItemType,
1476 Itemkey => newItemKey,
1477 aname => 'ACNAME',
1478 avalue => ACNAME);
1479 -- set Task ID!
1480 wf_engine.SetItemAttrNumber(Itemtype => InstItemType,
1481 Itemkey => newItemKey,
1482 aname => 'TASKID',
1483 avalue => TaskID);
1484
1485 -- set Task Name!
1486 wf_engine.SetItemAttrText(Itemtype => InstItemType,
1487 Itemkey => newItemKey,
1488 aname => 'TASKNAME',
1489 avalue => TaskName);
1490
1491 -- set newItemKey for submit engine mgr proc!
1492 wf_engine.SetItemAttrText(Itemtype => InstItemType,
1493 Itemkey => newItemKey,
1494 aname => 'ARG2',
1495 avalue => newItemKey);
1496
1497 -- set owner name attr!
1498 wf_engine.SetItemAttrText(Itemtype => InstItemType,
1499 Itemkey => newItemKey,
1500 aname => 'FNDUSERNAM',
1501 avalue => owner);
1502
1503 -- set EPBPerformer to owner name for notifications DEFAULT!
1504 wf_engine.SetItemAttrText(Itemtype => InstItemType,
1505 Itemkey => newItemKey,
1506 aname => 'EPBPERFORMER',
1507 avalue => owner);
1508
1509 -- will get error notifications
1510 wf_engine.SetItemAttrText(Itemtype => InstItemType,
1511 Itemkey => newItemKey,
1512 aname => 'WF_ADMINISTRATOR',
1513 avalue => owner);
1514
1515 -- set owner ID!
1516 wf_engine.SetItemAttrNumber(Itemtype => InstItemType,
1517 Itemkey => newItemKey,
1518 aname => 'OWNERID',
1519 avalue => ownerID);
1520
1521 -- set resp ID!
1522 wf_engine.SetItemAttrNumber(Itemtype => InstItemType,
1523 Itemkey => newItemKey,
1524 aname => 'RESPID',
1525 avalue => respID);
1526
1527 -- set appresp ID!
1528 wf_engine.SetItemAttrNumber(Itemtype => InstItemType,
1529 Itemkey => newItemKey,
1530 aname => 'RESPAPPID',
1531 avalue => respAppID);
1532
1533 if workflowprocess = 'EXCEPTION' then
1534 -- plsql document procedure
1535 wf_engine.SetItemAttrText(itemtype => InstItemType,
1536 itemkey => newItemKey,
1537 aname => 'RESPNOTE',
1538 avalue => 'PLSQL:ZPB_EXCEPTION_ALERT.NON_RESPONDERS/' || newItemKey );
1539 end if;
1540
1541 update zpb_analysis_cycle_tasks
1542 set item_KEY = newitemkey,
1543 Start_date = to_Date(charDate,'MM/DD/YYYY-HH24-MI-SS'),
1544 status_code = 'ACTIVE',
1545 LAST_UPDATED_BY = fnd_global.USER_ID,
1546 LAST_UPDATE_DATE = SYSDATE,
1547 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1548 where ANALYSIS_CYCLE_ID = InstanceID and task_id = TaskID;
1549
1550 update zpb_ANALYSIS_CYCLES
1551 set status_code = 'ACTIVE',
1552 LAST_UPDATED_BY = fnd_global.USER_ID,
1553 LAST_UPDATE_DATE = SYSDATE,
1554 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1555 where ANALYSIS_CYCLE_ID = InstanceID;
1556
1557 update zpb_analysis_cycle_instances
1558 set last_update_date = sysdate,
1559 LAST_UPDATED_BY = fnd_global.USER_ID,
1560 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1561 where instance_ac_id = INSTANCEID;
1562
1563 -- Now that all is created and set START the PROCESS!
1564 wf_engine.StartProcess(ItemType => InstItemType,
1565 ItemKey => newItemKey);
1566
1567 commit;
1568
1569
1570 /*+======================================================================+
1571 -- abudnik b 4725904 COMMENTING OUT OUR USE OF ZPB BACKGROUND ENGINES.
1572 -- THIS WILL NOW BE DONE BY STANDARD WORKFLOW via OAM
1573
1574 -- WF BACKGROUND ENGINE TO RUN deferred activities like WAIT.
1575 call_status := FND_CONCURRENT.GET_REQUEST_STATUS(request_id, 'ZPB', 'ZPB_WF_START', rphase,rstatus,dphase,dstatus, message);
1576
1577 if call_status = TRUE then
1578 if dphase <> 'RUNNING' then
1579 -- WF BACKGROUND ENGINE TO RUN deferred activities like WAIT.
1580 bkgREQID := FND_REQUEST.SUBMIT_REQUEST ('ZPB', 'ZPB_WF_START', NULL, NULL, FALSE, InstItemType, newitemkey );
1581 wf_engine.SetItemAttrNumber(Itemtype => InstItemType,
1582 Itemkey => newItemKey,
1583 aname => 'BKGREQID',
1584 avalue => bkgREQID);
1585 end if;
1586 else
1587 -- WF BACKGROUND ENGINE TO RUN deferred activities like WAIT.
1588 bkgREQID := FND_REQUEST.SUBMIT_REQUEST ('ZPB', 'ZPB_WF_START', NULL, NULL, FALSE, InstItemType, newitemkey );
1589 wf_engine.SetItemAttrNumber(Itemtype => InstItemType,
1590 Itemkey => newItemKey,
1591 aname => 'BKGREQID',
1592 avalue => bkgREQID);
1593 end if;
1594
1595
1596 +======================================================================+*/
1597
1598
1599 -- The new Business Process Run has started
1600 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_WF_NEW_BP_END');
1601 FND_FILE.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
1602
1603 -- b 5170327 - retcode is an OUT parameter conc program standard - 0=success, 1=warning or 2=error.
1604 -- will have warning if set by AW dml from call to ZPB_WF.INIT_BUSINESS_PROCESS
1605 retcode := ZPB_ERROR_HANDLER.GET_CONC_REQ_STATUS;
1606
1607 return;
1608
1609 exception
1610
1611 when others then
1612 retcode :='2';
1613 errbuf:=substr(sqlerrm, 1, 255);
1614
1615 -- update zpb instance info with ERROR
1616 UPDATE_STATUS('ERROR', Instanceid, taskid, NULL);
1617
1618
1619 end MakeInstance;
1620
1621 -- MarkforDelete
1622 procedure MarkforDelete (ACID in Number,
1623 ownerID in number,
1624 respID in number,
1625 RespAppID in number)
1626
1627 AS
1628
1629 retValue varchar2(16);
1630 InstanceID number;
1631 InstancesToKeep number;
1632 InstancesToKeepT varchar2(2);
1633 InstancesToDel varchar2(4000);
1634 sessionID number;
1635 DLcmd varchar2(100);
1636 DataAW varchar2(30);
1637 ItemType varchar2(8) := 'EPBCYCLE';
1638 ThisInstace number;
1639 l_REQID number;
1640 ItemKey varchar2(240);
1641 l_return_status VARCHAR2(1);
1642 l_msg_count NUMBER;
1643 l_msg_data VARCHAR2(2000);
1644 l_business_area_id number;
1645 l_appended varchar2(18);
1646
1647
1648 CURSOR c_instances is
1649 select instance_ac_id
1650 from zpb_analysis_cycle_instances
1651 where zpb_analysis_cycle_instances.ANALYSIS_CYCLE_ID = ACID
1652 and instance_ac_id = (select ac.ANALYSIS_CYCLE_ID from zpb_ANALYSIS_CYCLES ac
1653 where ac.ANALYSIS_CYCLE_ID = instance_ac_id and
1654 ac.status_code in ('COMPLETE','COMPLETE_WITH_WARNING', 'ERROR'))
1655 order by instance_ac_id DESC;
1656
1657 v_instances c_instances%ROWTYPE;
1658
1659 CURSOR c_params is
1660 select value
1661 from ZPB_AC_PARAM_VALUES
1662 where ANALYSIS_CYCLE_ID = ACID and PARAM_ID = 2;
1663
1664 v_params c_params%ROWTYPE;
1665
1666
1667 BEGIN
1668
1669 -- abudnik 17NOV2005 BUSINESS AREA ID.
1670 select BUSINESS_AREA_ID
1671 into l_business_area_id
1672 from ZPB_ANALYSIS_CYCLES
1673 where ANALYSIS_CYCLE_ID = ACId;
1674
1675 InstancesToKeep := -1;
1676
1677 -- 2 CALENDAR_VERSIONS_PERSISTED
1678 for v_params in c_params loop
1679 InstancesToKeepT := v_params.value;
1680 InstancesToKeep := to_number(InstancesToKeepT);
1681 end loop;
1682
1683 DataAW := fnd_profile.VALUE_SPECIFIC('ZPB_APPMGR_AW_NAME', ownerID, respID, respAppID);
1684
1685 -- obsolete instances MARKED_FOR_DELETION
1686 if InstancesToKeep >= 0 then
1687
1688 for v_instances in c_instances loop
1689
1690 if c_instances%ROWCOUNT > InstancesToKEEP then
1691 -- update delete flag
1692
1693 InstanceID := v_instances.instance_ac_id;
1694
1695 update zpb_ANALYSIS_CYCLES
1696 set status_code = 'MARKED_FOR_DELETION',
1697 LAST_UPDATED_BY = fnd_global.USER_ID,
1698 LAST_UPDATE_DATE = SYSDATE,
1699 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1700 where ANALYSIS_CYCLE_ID = InstanceID;
1701
1702 update zpb_analysis_cycle_instances
1703 set last_update_date = sysdate,
1704 LAST_UPDATED_BY = fnd_global.USER_ID,
1705 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
1706 where instance_ac_id = INSTANCEID;
1707
1708 -- now delete any Data Collection templates
1709 -- associated with this cycle
1710 zpb_dc_objects_pvt.delete_template(
1711 1.0, FND_API.G_TRUE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL,
1712 l_return_status, l_msg_count, l_msg_data, InstanceID);
1713
1714
1715 -- b5007895
1716 begin
1717
1718 select value
1719 into l_appended
1720 from ZPB_AC_PARAM_VALUES
1721 where ANALYSIS_CYCLE_ID = ACID and PARAM_ID = 26;
1722 exception
1723 when NO_DATA_FOUND then
1724 l_appended := 'PARM_NOT_FOUND';
1725 end;
1726
1727
1728 -- 03/07/2004 agb new instance delete
1729 if rtrim(l_appended, ' ') = 'DO_NOT_APPEND_VIEW' then
1730 -- zpb_build_metadata.remove_instance(DataAW, InstanceID);
1731 l_REQID := FND_REQUEST.SUBMIT_REQUEST ('ZPB', 'ZPB_WF_DELAWINST', NULL, NULL, FALSE, instanceid, ownerID, l_business_area_id);
1732 end if;
1733
1734 retValue := 'MARKED';
1735 end if;
1736
1737 retValue := 'NONE_MARKED';
1738 end loop;
1739 else
1740 retValue := 'PARM_NOT_FOUND';
1741 end if;
1742
1743 return;
1744 exception
1745 when others then
1746 raise;
1747
1748 end MarkforDelete;
1749
1750 /*+=========================================================================+
1751 | private function SET_MONTHLY_LIST
1752 |
1753 | This function takes in a list of days of the month and finds
1754 | the day that is closest in the future to the current day
1755 | It assumes that the passed in list is chronologically sorted
1756 |
1757 | IN
1758 | p_itemtype - The itemType of the workflow for which attributes will be updated
1759 | p_itemkey - The itemKey of the workflow for which attributes will be updated
1760 | p_daylist - list of days of the month
1761 |
1762 | OUT - the day in the list that is closest to current day
1763 |
1764 |
1765 +========================================================================+
1766 */
1767
1768 function set_monthly_list(p_itemtype in varchar2,
1769 p_itemkey in varchar2,
1770 p_daylist in varchar2) return varchar2
1771
1772 AS
1773
1774 l_api_name CONSTANT VARCHAR2(30) := 'SET_MONTHLY_LIST';
1775
1776 l_todayDay number;
1777 l_testToUse number;
1778 l_testDay varchar2(100);
1779 l_testRemaining varchar2(100);
1780 l_lastDiff number;
1781 l_thisDiff number;
1782 l_loopFlag boolean:=TRUE;
1783 l_lastDayDiff number;
1784 l_lastDay number;
1785
1786 BEGIN
1787
1788 zpb_log.write('G_PKG_NAME' || '.' || l_api_name ,
1789 'prcoedure start. daylist '||p_daylist);
1790
1791 -- initialize variables and first test case
1792 l_testDay:=upper(substr(p_daylist, 1, instr(p_daylist, ',') -1));
1793 l_testToUse:=to_number(l_testDay);
1794 l_testRemaining:=substr(p_daylist, instr(p_daylist, ',')+1);
1795
1796 l_todayDay:=to_number(to_char(sysdate, 'DD'));
1797
1798 -- if first day in list is today, we are done
1799 -- while looping over all values in list, watch out for 'LastDay' option
1800 if l_todayDay = l_testToUse then
1801 l_loopFlag:=FALSE;
1802 else
1803 if l_testToUse > l_todayDay then
1804 l_lastDiff:= l_testToUse-l_todayDay;
1805 else
1806 l_lastDiff:= l_testToUse-l_todayDay+31;
1807 end if;
1808
1809 if instr(l_testRemaining, ',') > 0 then
1810 l_testDay:=upper(substr(l_testRemaining, 1, instr(l_testRemaining, ',') -1));
1811 l_testToUse:=to_number(l_testDay);
1812 l_testRemaining:=substr(l_testRemaining, instr(l_testRemaining, ',')+1);
1813 else
1814 l_testDay:=l_testRemaining;
1815 l_testToUse:=to_number(l_testDay);
1816 l_testRemaining:=NULL;
1817
1818 if l_testDay='LastDay' then
1819 l_loopFlag:=FALSE;
1820 l_testDay:=upper(substr(p_daylist, 1, instr(p_daylist, ',') -1));
1821 l_testToUse:=to_number(l_testDay);
1822 l_testRemaining:=substr(p_daylist, instr(p_daylist, ',')+1);
1823 end if;
1824 end if;
1825 end if;
1826
1827 while l_loopFlag=TRUE loop
1828
1829 if l_testToUse =l_todayDay then
1830 l_loopFlag:=FALSE;
1831 else
1832 if l_testToUse > l_todayDay then
1833 l_thisDiff:= l_testToUse-l_todayDay;
1834 else
1835 l_thisDiff:= l_testToUse-l_todayDay+31;
1836 end if;
1837
1838 if l_thisDiff<l_lastDiff then
1839 l_loopFlag:=FALSE;
1840 else
1841 l_lastDiff:=l_thisDiff;
1842
1843 if instr(l_testRemaining, ',') > 0 then
1844 l_testDay:=upper(substr(l_testRemaining, 1, instr(l_testRemaining, ',') -1));
1845 l_testToUse:=to_number(l_testDay);
1846 l_testRemaining:=substr(l_testRemaining, instr(l_testRemaining, ',')+1);
1847 else
1848 if length(l_testRemaining) > 0 then
1849 l_testDay:=l_testRemaining;
1850 l_testRemaining:=NULL;
1851
1852 if l_testDay='LastDay' then
1853 l_loopFlag:=FALSE;
1854 l_testDay:=upper(substr(p_daylist, 1, instr(p_daylist, ',') -1));
1855 l_testToUse:=to_number(l_testDay);
1856 l_testRemaining:=substr(p_daylist, instr(p_daylist, ',')+1);
1857 else
1858 l_testToUse:=to_number(l_testDay);
1859 end if;
1860 else
1861 l_testDay:=upper(substr(p_daylist, 1, instr(p_daylist, ',') -1));
1862 l_testToUse:=to_number(l_testDay);
1863 l_testRemaining:=substr(p_daylist, instr(p_daylist, ',')+1);
1864 l_loopFlag:=FALSE;
1865 end if;
1866 end if;
1867 end if;
1868 end if;
1869 end loop;
1870
1871 -- Check for LAST_DAY in list
1872 if instr(p_daylist, 'LastDay')>0 then
1873 l_lastDay:=to_number(to_char(last_day(sysdate), 'DD'));
1874 l_lastDayDiff:=l_lastDay-l_todayDay;
1875
1876 if l_testToUse >= l_todayDay then
1877 l_thisDiff:= l_testToUse-l_todayDay;
1878 else
1879 l_thisDiff:= l_testToUse-l_todayDay+31;
1880 end if;
1881
1882 if l_thisDiff>l_lastDayDiff then
1883 l_testDay:='LAST';
1884 l_testRemaining:=NULL;
1885 end if;
1886 end if;
1887
1888 if length(nvl(l_testRemaining, '') || ' ' ) < 2 then
1889 l_testRemaining:= p_daylist;
1890 end if;
1891
1892 wf_engine.SetItemAttrText(Itemtype => p_itemtype,
1893 Itemkey => p_itemkey,
1894 aname => 'REMAININGSEL',
1895 avalue => l_testRemaining);
1896
1897 wf_engine.SetItemAttrText(Itemtype => p_itemtype,
1898 Itemkey => p_itemkey,
1899 aname => 'WAIT_DAY_OF_MONTH',
1900 avalue => l_testDay);
1901
1902 zpb_log.write('G_PKG_NAME' || '.' || l_api_name ,
1903 'prcoedure end. closest-day '|| l_testDay || ' remainder ' || l_testRemaining);
1904
1905 return l_testDay;
1906
1907 END set_monthly_list;
1908
1909 /*+=========================================================================+
1910 | private function SET_WEEKLY_LIST
1911 |
1912 | This function takes in a list of days of the week and finds
1913 | the day of the week that is closest in the future to the current day
1914 | It assumes that the passed in list is chronologically sorted
1915 |
1916 | IN
1917 | p_itemtype - The itemType of the workflow for which attributes will be updated
1918 | p_itemkey - The itemKey of the workflow for which attributes will be updated
1919 | p_daylist - list of days of the week
1920 |
1921 | OUT - the day in the list that is closest to current day
1922 |
1923 |
1924 +========================================================================+
1925 */
1926 function set_weekly_list(p_itemtype in varchar2,
1927 p_itemkey in varchar2,
1928 p_daylist in varchar2) return varchar2
1929 IS
1930
1931 l_api_name CONSTANT VARCHAR2(30) := 'SET_WEEKLY_LIST';
1932
1933 l_todayDay varchar2(30);
1934 l_testDay varchar2(100);
1935 l_testRemaining varchar2(100);
1936 l_lastDiff number;
1937 l_thisDiff number;
1938 l_loopFlag boolean:=TRUE;
1939
1940 BEGIN
1941
1942 zpb_log.write('G_PKG_NAME' || '.' || l_api_name ,
1943 'prcoedure start. daylist '||p_daylist);
1944
1945 -- get current day of week for comparison
1946 l_todayDay :=to_char(sysdate, 'DAY') || '';
1947
1948 l_testDay:=upper(substr(p_daylist, 1, instr(p_daylist, ',') -1));
1949 l_testRemaining:=substr(p_daylist, instr(p_daylist, ',')+1);
1950
1951
1952 -- if first day in list is today, then we do not need to loop through the list
1953 if to_char(sysdate, 'DAY') || '' = upper(l_testDay) then
1954 l_loopFlag:=FALSE;
1955 else
1956 -- set difference variable and prepare next day for comparison
1957 l_lastDiff:=NEXT_DAY(sysdate, l_testDay) - sysdate;
1958 if instr(l_testRemaining, ',') > 0 then
1959 l_testDay:=upper(substr(l_testRemaining, 1, instr(l_testRemaining, ',') -1));
1960 l_testRemaining:=substr(l_testRemaining, instr(l_testRemaining, ',')+1);
1961 else
1962 l_testDay:=l_testRemaining;
1963 l_testRemaining:=NULL;
1964 end if;
1965 end if;
1966
1967 while l_loopFlag=TRUE loop
1968
1969 if upper(l_testDay) = l_todayDay then
1970 l_loopFlag:=FALSE;
1971 else
1972 -- save difference
1973 l_thisDiff:=NEXT_DAY(sysdate, l_testDay) - sysdate;
1974 -- if found closer day, then we have looped around and are done searching
1975 if l_thisDiff<l_lastDiff then
1976 l_loopFlag:=FALSE;
1977 else
1978 l_lastDiff:=l_thisDiff;
1979 if instr(l_testRemaining, ',') > 0 then
1980 l_testDay:=upper(substr(l_testRemaining, 1, instr(l_testRemaining, ',') -1));
1981 l_testRemaining:=substr(l_testRemaining, instr(l_testRemaining, ',')+1);
1982 else
1983 if length(l_testRemaining) > 0 then
1984 l_testDay:=l_testRemaining;
1985 l_testRemaining:=NULL;
1986 else
1987 l_testDay:=upper(substr(p_daylist, 1, instr(p_daylist, ',') -1));
1988 l_testRemaining:=substr(p_daylist, instr(p_daylist, ',')+1);
1989 l_loopFlag:=FALSE;
1990 end if; -- anything left to test
1991 end if; -- at least two things left to test
1992 end if; -- test case is today
1993 end if; -- first test case is today
1994 end loop;
1995
1996 -- if closest day happens to be last in list, reset remainder to be the full list
1997 if length(nvl(l_testRemaining, '') || ' ' ) < 2 then
1998 l_testRemaining:= p_daylist;
1999 end if;
2000
2001 -- set appropriate workflow attributess
2002 wf_engine.SetItemAttrText(Itemtype => p_itemtype,
2003 Itemkey => p_itemkey,
2004 aname => 'REMAININGSEL',
2005 avalue => l_testRemaining);
2006
2007 wf_engine.SetItemAttrText(Itemtype => p_itemtype,
2008 Itemkey => p_itemkey,
2009 aname => 'WAIT_DAY_OF_WEEK',
2010 avalue => l_testDay);
2011
2012 zpb_log.write('G_PKG_NAME' || '.' || l_api_name ,
2013 'prcoedure end. closest-day '|| l_testDay || ' remainder ' || l_testRemaining);
2014
2015 return l_testDay;
2016
2017 end set_weekly_list;
2018
2019 procedure FrequencyInit (itemtype in varchar2,
2020 itemkey in varchar2,
2021 actid in number,
2022 funcmode in varchar2,
2023 resultout out nocopy varchar2)
2024 AS
2025
2026
2027 ACID number;
2028 ACNAME varchar2(300);
2029 retval varchar2(4000);
2030 ownerID number;
2031 owner varchar2(30);
2032
2033 paramID number;
2034 FreqType varchar2(100);
2035 FreqRep number;
2036 freqSelW varchar2(100);
2037 freqSelM varchar2(100);
2038 freqSel varchar2(100);
2039 UntilDate date;
2040 errMsg varchar2(100);
2041 thisValue varchar2(100);
2042 addMonths number;
2043 NewDate date;
2044 thisFreqSel varchar2(100);
2045 curFreqBehavior varchar2(100);
2046
2047 schedProfileOption varchar2(80);
2048 oneTimeOnlyActive number;
2049
2050 compDay varchar2(100);
2051
2052 CURSOR c_params is
2053 select param_id, value
2054 from ZPB_AC_PARAM_VALUES
2055 where ANALYSIS_CYCLE_ID = ACID and PARAM_ID IN (19, 20, 21, 22, 23, 24);
2056
2057 v_params c_params%ROWTYPE;
2058
2059 BEGIN
2060 -- 24 CALENDAR_REPEAT_DAY_OF_MONTH
2061 -- 23 CALENDAR_REPEAT_WEEKDAY
2062 -- 22 CALENDAR_REPEAT_UNTIL_DATE
2063 -- 21 CALENDAR_REPEAT_FREQUENCY
2064 -- 19 CALENDAR_FREQUENCY_TYPE
2065
2066 IF (funcmode = 'RUN') THEN
2067 resultout :='COMPLETE:N';
2068
2069
2070 ACID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
2071 Itemkey => ItemKey,
2072 aname => 'ACID');
2073
2074 ACNAME := wf_engine.GetItemAttrText(Itemtype => ItemType,
2075 Itemkey => ItemKey,
2076 aname => 'ACNAME');
2077
2078 OWNERID := wf_engine.GetItemAttrText(Itemtype => ItemType,
2079 Itemkey => ItemKey,
2080 aname => 'OWNERID');
2081
2082 for v_params in c_params loop
2083
2084 paramID := v_params.param_id;
2085
2086 if ParamID = 19 then
2087 freqType := v_params.value;
2088 elsif ParamID = 21 then
2089 thisValue := v_params.value;
2090 freqRep := to_number(thisValue);
2091 elsif ParamID = 22 then
2092 thisValue := v_params.value;
2093 -- set date to last second of chosen date so that last instance may be created
2094 -- at any time of the cut off date
2095 UntilDate := to_date(thisValue, 'YYYY/MM/DD-HH24:MI:SS') + 1 - 1/(24*3600) ;
2096 elsif ParamID = 23 then
2097 freqSelW := v_params.value;
2098 elsif ParamID = 24 then
2099 freqSelM := v_params.value;
2100 else
2101 errMsg := v_params.value;
2102 end if;
2103
2104 end loop;
2105
2106 -- +==============================================================
2107 -- ONE_TIME_ONLY
2108 -- BUG 4291814 - WORKFLOW COMPONENTS: START BP EXTERNALLY
2109
2110 if freqType = 'ONE_TIME_ONLY' or freqType = 'EXTERNAL_EVENT' then
2111
2112 -- if freq type is one-time-only, only create a new instance if
2113 -- there are no active instance of this BP
2114
2115 schedProfileOption:= FND_PROFILE.VALUE_SPECIFIC('ZPB_BPSCHEDULER_TYPE', OwnerId);
2116
2117 resultout :='COMPLETE:BYPASS';
2118
2119 wf_engine.SetItemAttrText(Itemtype => ItemType,
2120 Itemkey => ItemKey,
2121 aname => 'FREQBEHAVIOR',
2122 avalue => 'ONE_TIME_ONLY');
2123 end if;
2124
2125 -- +==============================================================
2126 -- Set the run Until date. Used for comparison to sysdate.
2127 -- Reference date.
2128 if length(UntilDate) > 0 then
2129 wf_engine.SetItemAttrDate(Itemtype => ItemType,
2130 Itemkey => ItemKey,
2131 aname => 'VALUE2',
2132 avalue => UntilDate);
2133 end if;
2134
2135 -- +==============================================================
2136 -- Case Daily Will first run based on start date
2137 if freqType = 'DAILY' then
2138
2139 -- Sets Wait attributes for scheduling.
2140 wf_engine.SetItemAttrText(Itemtype => ItemType,
2141 Itemkey => ItemKey,
2142 aname => 'WAIT_MODE',
2143 avalue => 'RELATIVE');
2144
2145 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
2146 Itemkey => ItemKey,
2147 aname => 'WAIT_RELATIVE_TIME',
2148 avalue => freqRep);
2149
2150 wf_engine.SetItemAttrText(Itemtype => ItemType,
2151 Itemkey => ItemKey,
2152 aname => 'FREQBEHAVIOR',
2153 avalue => 'LOOP');
2154
2155 resultout :='COMPLETE:BYPASS';
2156 return;
2157 end if;
2158
2159 -- +==============================================================
2160 -- Case Yearly
2161
2162 curFreqBehavior := wf_engine.GetItemAttrText(Itemtype => ItemType,
2163 Itemkey => ItemKey,
2164 aname => 'FREQBEHAVIOR');
2165
2166 if freqType = 'YEARLY' then
2167
2168 wf_engine.SetItemAttrText(Itemtype => ItemType,
2169 Itemkey => ItemKey,
2170 aname => 'WAIT_MODE',
2171 avalue => 'ABSOLUTE');
2172 -- make adjustment
2173 addMonths := freqRep * 12;
2174 NewDate := add_months(sysdate, addMonths);
2175
2176 wf_engine.SetItemAttrDate(Itemtype => ItemType,
2177 Itemkey => ItemKey,
2178 aname => 'WAIT_ABSOLUTE_DATE',
2179 avalue => NewDate);
2180
2181 wf_engine.SetItemAttrText(Itemtype => ItemType,
2182 Itemkey => ItemKey,
2183 aname => 'FREQBEHAVIOR',
2184 avalue => 'LOOP');
2185
2186
2187 -- if the frequency is yearly and the start data has been reset,
2188 -- do not create instance right away
2189 if curFreqBehavior='YEARLYRESET' then
2190 resultout :='COMPLETE:LOOP';
2191 else
2192 resultout :='COMPLETE:BYPASS';
2193 end if;
2194
2195 end if;
2196
2197 -- +==============================================================
2198 -- Case Monthly
2199 if freqType = 'MONTHLY' then
2200
2201 freqSel := freqSelM;
2202 wf_engine.SetItemAttrText(Itemtype => ItemType,
2203 Itemkey => ItemKey,
2204 aname => 'WAIT_MODE',
2205 avalue => 'DAY_OF_MONTH');
2206
2207 --Parse selection.
2208 if length(freqSel) > 0 then
2209
2210 thisFreqSel := set_monthly_list(ItemType, ItemKey, freqSel);
2211
2212 end if; --length
2213
2214
2215 -- don't forget freqRep > 1!!!!
2216
2217 if freqRep > 1 then
2218
2219 -- make adjustment
2220 -- NewDate := add_months(sysdate, freqRep);
2221 NewDate := to_date((to_char(add_months(sysdate, freqRep), 'YYYY/MM') || '/01'), 'YYYY/MM/DD');
2222
2223 wf_engine.SetItemAttrDate(Itemtype => ItemType,
2224 Itemkey => ItemKey,
2225 aname => 'ABSOLUTE_SLEEP_DATE',
2226 avalue => NewDate);
2227
2228 wf_engine.SetItemAttrText(Itemtype => ItemType,
2229 Itemkey => ItemKey,
2230 aname => 'FREQBEHAVIOR',
2231 avalue => 'SLEEP');
2232
2233 resultout :='COMPLETE:SLEEP';
2234 else
2235 wf_engine.SetItemAttrText(Itemtype => ItemType,
2236 Itemkey => ItemKey,
2237 aname => 'FREQBEHAVIOR',
2238 avalue => 'LOOP');
2239
2240 -- Workflow WAIT blocks that are set to wait for a day on that day will not fire
2241 -- until that day of the next month. Thus check if next scheduled day is today
2242 -- and bypass wait in this case
2243 if thisFreqSel='LAST' then
2244 compDay:= to_char(LAST_DAY(sysdate), 'dd');
2245 else
2246 compDay:= thisFreqSel;
2247 end if;
2248
2249 -- prefix compDay with zero if appropriate
2250 -- to_char(sysdate) returns 04 instead of 4 for the fourth
2251 if length(compDay)=1 then
2252 compDay:= '0' || compDay;
2253 end if;
2254
2255
2256 if compDay = to_char(sysdate, 'dd') then
2257 resultout:='COMPLETE:BYPASS';
2258 else
2259 resultout:='COMPLETE:LOOP';
2260 end if; -- bypassing for today
2261
2262 end if; -- freqRep
2263
2264 end if;
2265
2266 -- +==============================================================
2267 -- Case Weekly
2268 if freqType = 'WEEKLY' then
2269
2270 freqSel := freqSelW;
2271 wf_engine.SetItemAttrText(Itemtype => ItemType,
2272 Itemkey => ItemKey,
2273 aname => 'WAIT_MODE',
2274 avalue => 'DAY_OF_WEEK');
2275
2276 --Parse selection.
2277 if length(freqSel) > 0 then
2278
2279 thisFreqSel := set_weekly_list(ItemType, ItemKey, freqSel);
2280
2281 end if; --length
2282
2283 if freqRep > 1 then
2284
2285 -- make adjustment
2286 NewDate := sysdate + (7*freqRep);
2287
2288 wf_engine.SetItemAttrDate(Itemtype => ItemType,
2289 Itemkey => ItemKey,
2290 aname => 'ABSOLUTE_SLEEP_DATE',
2291 avalue => NewDate);
2292
2293 wf_engine.SetItemAttrText(Itemtype => ItemType,
2294 Itemkey => ItemKey,
2295 aname => 'FREQBEHAVIOR',
2296 avalue => 'SLEEP');
2297
2298 resultout :='COMPLETE:SLEEP';
2299 else
2300
2301 wf_engine.SetItemAttrText(Itemtype => ItemType,
2302 Itemkey => ItemKey,
2303 aname => 'FREQBEHAVIOR',
2304 avalue => 'LOOP');
2305
2306 -- Workflow WAIT blocks that are set to wait for a day on that day will not fire
2307 -- until that day of the next week. Thus check if next scheduled day is today
2308 -- and bypass wait in this case
2309 if upper(thisFreqSel) = to_char(sysdate, 'fmDAY') then
2310 resultout :='COMPLETE:BYPASS';
2311 else
2312 resultout :='COMPLETE:LOOP';
2313 end if;
2314 end if;
2315
2316 end if;
2317
2318
2319 END IF;
2320 return;
2321
2322 exception
2323 when others then
2324 WF_CORE.CONTEXT('ZPB_WF.FrequencyInit', itemtype, itemkey, to_char(actid), funcmode);
2325 raise;
2326
2327 end FrequencyInit;
2328
2329 --
2330 --
2331 procedure FrequencyMgr (itemtype in varchar2,
2332 itemkey in varchar2,
2333 actid in number,
2334 funcmode in varchar2,
2335 resultout out nocopy varchar2)
2336 AS
2337
2338 ACID number;
2339 ACNAME varchar2(300);
2340 retval varchar2(4000);
2341
2342 paramID number;
2343 FreqType varchar2(100);
2344 FreqRep number;
2345 UntilDate date;
2346 errMsg varchar2(100);
2347 thisValue varchar2(100);
2348 addMonths number;
2349 NewDate date;
2350 thisFreqSel varchar2(100);
2351 freqMode varchar2(30);
2352 Behavior varchar2(16);
2353 freqSel varchar2(100);
2354 selValue varchar2(100);
2355
2356 BEGIN
2357
2358
2359 IF (funcmode = 'RUN') THEN
2360 resultout :='COMPLETE:N';
2361
2362
2363 -- Get Wait MODE
2364
2365 ACID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
2366 Itemkey => ItemKey,
2367 aname => 'ACID');
2368
2369 ACNAME := wf_engine.GetItemAttrText(Itemtype => ItemType,
2370 Itemkey => ItemKey,
2371 aname => 'ACNAME');
2372
2373 freqMode := wf_engine.GetItemAttrText(Itemtype => ItemType,
2374 Itemkey => ItemKey,
2375 aname => 'WAIT_MODE');
2376
2377 Behavior := wf_engine.GetItemAttrText(Itemtype => ItemType,
2378 Itemkey => ItemKey,
2379 aname => 'FREQBEHAVIOR');
2380
2381
2382 -- Case one time only
2383 if Behavior = 'ONE_TIME_ONLY' then
2384
2385 resultout := 'COMPLETE:ONE_TIME_ONLY';
2386 return;
2387
2388 end if;
2389
2390 -- Case Daily
2391 if freqMode = 'RELATIVE' then
2392
2393 resultout := 'COMPLETE:LOOP';
2394 return;
2395
2396 end if;
2397
2398 -- Case Yearly
2399
2400 if freqMode = 'ABSOLUTE' then
2401
2402 select Value
2403 into freqRep
2404 from ZPB_AC_PARAM_VALUES
2405 where ANALYSIS_CYCLE_ID = ACID and PARAM_ID = 21;
2406
2407 -- make adjustment
2408 addMonths := freqRep * 12;
2409
2410 NewDate := add_months(sysdate, addMonths);
2411
2412 wf_engine.SetItemAttrDate(Itemtype => ItemType,
2413 Itemkey => ItemKey,
2414 aname => 'WAIT_ABSOLUTE_DATE',
2415 avalue => NewDate);
2416
2417 resultout := 'COMPLETE:LOOP';
2418 return;
2419 end if;
2420
2421
2422 -- Case Monthly
2423 if freqMode = 'DAY_OF_MONTH' then
2424
2425 -- get the remaining days and set them
2426 FreqSel := wf_engine.GetItemAttrText(Itemtype => ItemType,
2427 Itemkey => ItemKey,
2428 aname => 'REMAININGSEL');
2429
2430 --Parse selection.
2431 if length(FreqSel) > 0 then
2432
2433 if instr(FreqSel, ',') > 0 then
2434 thisFreqSel := substr(FreqSel, 1, instr(freqSel, ',') -1);
2435 freqSel := substr(freqSel, instr(freqSel, ',')+1);
2436 else
2437 thisFreqSel := substr(freqSel, 1);
2438
2439 -- have gone over all days specified; reset REMAININGSEL from DB
2440 -- AC Param value
2441 select Value
2442 into freqSel
2443 from ZPB_AC_PARAM_VALUES
2444 where ANALYSIS_CYCLE_ID = ACID and PARAM_ID = 24;
2445
2446 end if; -- comma
2447
2448 if thisFreqSel = 'LastDay' then
2449 thisFreqSel := 'LAST';
2450 end if;
2451
2452 wf_engine.SetItemAttrText(Itemtype => ItemType,
2453 Itemkey => ItemKey,
2454 aname => 'WAIT_DAY_OF_MONTH',
2455 avalue => thisFreqSel);
2456
2457 -- adjusts freq sel.
2458 wf_engine.SetItemAttrText(Itemtype => ItemType,
2459 Itemkey => ItemKey,
2460 aname => 'REMAININGSEL',
2461 avalue => FreqSel);
2462 end if; -- length, length should never be 0 because above we reset REMAININGSEL when
2463 -- it reaches null
2464
2465 -- Only sleep
2466
2467 if Behavior = 'SLEEP' then
2468 -- make adjustment 21 CALENDAR_REPEAT_FREQUENCY
2469
2470 select Value
2471 into freqRep
2472 from ZPB_AC_PARAM_VALUES
2473 where ANALYSIS_CYCLE_ID = ACID and PARAM_ID = 21;
2474
2475 -- NewDate := add_months(sysdate, freqRep);
2476 NewDate := to_date((to_char(add_months(sysdate, freqRep), 'YYYY/MM') || '/01'), 'YYYY/MM/DD');
2477
2478 wf_engine.SetItemAttrDate(Itemtype => ItemType,
2479 Itemkey => ItemKey,
2480 aname => 'ABSOLUTE_SLEEP_DATE',
2481 avalue => NewDate);
2482
2483 resultout :='COMPLETE:SLEEP';
2484
2485 end if; -- sleep
2486
2487 if Behavior = 'LOOP' then
2488 resultout :='COMPLETE:LOOP';
2489 end if;
2490
2491 end if; -- end mode
2492
2493
2494 -- Case Weekly
2495 if freqMode = 'DAY_OF_WEEK' then
2496
2497 -- get the remaining days and set them
2498 FreqSel := wf_engine.GetItemAttrText(Itemtype => ItemType,
2499 Itemkey => ItemKey,
2500 aname => 'REMAININGSEL');
2501
2502 --Parse selection.
2503 if length(FreqSel) > 0 then
2504
2505 if instr(FreqSel, ',') > 0 then
2506 thisFreqSel := upper(substr(FreqSel, 1, instr(freqSel, ',') -1));
2507 freqSel := substr(freqSel, instr(freqSel, ',')+1);
2508 else
2509 thisFreqSel := upper(substr(freqSel, 1));
2510
2511 -- have gone over all days specified; reset REMAININGSEL from DB
2512 -- AC Param value
2513 select Value
2514 into freqSel
2515 from ZPB_AC_PARAM_VALUES
2516 where ANALYSIS_CYCLE_ID = ACID and PARAM_ID = 23;
2517
2518 end if; -- comma
2519
2520 wf_engine.SetItemAttrText(Itemtype => ItemType,
2521 Itemkey => ItemKey,
2522 aname => 'WAIT_DAY_OF_WEEK',
2523 avalue => thisFreqSel);
2524
2525 -- adjusts freq sel.
2526 wf_engine.SetItemAttrText(Itemtype => ItemType,
2527 Itemkey => ItemKey,
2528 aname => 'REMAININGSEL',
2529 avalue => FreqSel);
2530
2531 end if; -- length, length should never be 0 because above we reset REMAININGSEL when
2532 -- it reaches null
2533
2534 -- Only sleep
2535 if Behavior = 'SLEEP' then
2536 -- make adjustment 21 CALENDAR_REPEAT_FREQUENCY
2537
2538 select Value
2539 into freqRep
2540 from ZPB_AC_PARAM_VALUES
2541 where ANALYSIS_CYCLE_ID = ACID and PARAM_ID = 21;
2542
2543 NewDate := sysdate + (7*freqRep);
2544
2545 wf_engine.SetItemAttrDate(Itemtype => ItemType,
2546 Itemkey => ItemKey,
2547 aname => 'ABSOLUTE_SLEEP_DATE',
2548 avalue => NewDate);
2549
2550 resultout :='COMPLETE:SLEEP';
2551 end if; -- sleep
2552
2553 if Behavior = 'LOOP' then
2554 resultout :='COMPLETE:LOOP';
2555 end if;
2556
2557 end if; -- end mode
2558
2559 END IF;
2560
2561 return;
2562
2563 exception
2564 when others then
2565 WF_CORE.CONTEXT('ZPB_WF.FrequencyMgr', itemtype, itemkey, to_char(actid), funcmode);
2566 raise;
2567
2568 end FrequencyMgr;
2569
2570
2571 procedure SetCompDate (itemtype in varchar2,
2572 itemkey in varchar2,
2573 actid in number,
2574 funcmode in varchar2,
2575 resultout out nocopy varchar2)
2576 IS
2577
2578 BEGIN
2579 -- Test value date SYSDATE. The desired End date is compared to this.
2580 -- if UntilDate is < SYSDATE keep running.
2581
2582 IF (funcmode = 'RUN') THEN
2583 resultout :='COMPLETE:N';
2584
2585 wf_engine.SetItemAttrDate(Itemtype => ItemType,
2586 Itemkey => ItemKey,
2587 aname => 'VALUE1',
2588 avalue => sysdate);
2589
2590 resultout :='COMPLETE:Y';
2591
2592 END IF;
2593
2594 exception
2595 when others then
2596 WF_CORE.CONTEXT('ZPB_WF', 'SetCompDate', itemtype, itemkey, to_char(actid), funcmode);
2597 raise;
2598 end SetCompDate;
2599
2600
2601 procedure PAUSE_INSTANCE (InstanceID in number)
2602 IS
2603
2604 InstatusCode varchar2(30);
2605
2606 BEGIN
2607
2608 select STATUS_CODE
2609 into InstatusCode
2610 from ZPB_ANALYSIS_CYCLES
2611 where ANALYSIS_CYCLE_ID = InstanceID
2612 for update nowait;
2613
2614 if InstatusCode = 'ACTIVE' or InstatusCode = 'WARNING' then
2615
2616
2617 update ZPB_ANALYSIS_CYCLES
2618 set prev_status_code = status_code
2619 where ANALYSIS_CYCLE_ID = InstanceID;
2620
2621 -- agb 04/22/04 change from PAUSED to PAUSING.
2622 update ZPB_ANALYSIS_CYCLES
2623 set status_code = 'PAUSING',
2624 LAST_UPDATED_BY = fnd_global.USER_ID,
2625 LAST_UPDATE_DATE = SYSDATE,
2626 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
2627 where ANALYSIS_CYCLE_ID = InstanceID;
2628 end if;
2629
2630 commit;
2631 return;
2632
2633 exception
2634
2635 when NO_DATA_FOUND then
2636 Null;
2637
2638 when others then
2639 -- ORA-00054: resource busy and acquire with NOWAIT specified
2640 if instr(sqlerrm, 'ORA-00054') > 0 then
2641 Null;
2642 return;
2643 else
2644 raise;
2645 end if;
2646
2647 end PAUSE_INSTANCE;
2648
2649
2650 procedure RESUME_INSTANCE (InstanceID in number, PResumeType varchar2 default 'NORMAL')
2651 IS
2652
2653 CurrtaskSeq number;
2654 ITEMTYPE varchar(8) := 'EPBCYCLE';
2655 ACID number;
2656 ACNAME varchar2(300);
2657 ACstatusCode varchar2(30);
2658 InstanceStatusCode varchar2(30);
2659 InPrevStatusCode varchar2(30);
2660 TaskID number;
2661 owner varchar2(30);
2662 ownerID number;
2663 respID number;
2664 respAppID number;
2665 charDate varchar2(30);
2666 newitemkey varchar2(240);
2667 olditemkey varchar2(240);
2668 workflowprocess varchar2(30);
2669 bkgREQID number;
2670 Marked varchar2(16);
2671 resultout varchar2(100);
2672 retcode number;
2673 l_business_area_id number;
2674
2675
2676 CURSOR c_tasks is
2677 select *
2678 from zpb_analysis_cycle_tasks
2679 where ANALYSIS_CYCLE_ID = InstanceID
2680 and Sequence = CurrtaskSeq+1;
2681 v_tasks c_Tasks%ROWTYPE;
2682 -- 5867453 bkport of 5371156 get resume parameters from zpb not wf
2683 CURSOR c_respID is
2684 select A.RESP_ID
2685 from zpb_account_states A, fnd_responsibility_vl R
2686 where A.ACCOUNT_STATUS = 0
2687 AND A.USER_ID = ownerID
2688 AND A.BUSINESS_AREA_ID = l_business_area_id
2689 AND A.RESP_ID = R.RESPONSIBILITY_ID
2690 AND R.RESPONSIBILITY_KEY IN
2691 ('ZPB_MANAGER_RESP', 'ZPB_CONTROLLER_RESP', 'ZPB_SUPER_CONTROLLER_RESP');
2692 v_respID c_respID%ROWTYPE;
2693
2694 -- This needs to
2695
2696 BEGIN
2697
2698 begin
2699
2700 select status_code, prev_status_code
2701 into InstanceStatusCode, InPrevStatusCode
2702 from zpb_analysis_cycles
2703 where analysis_cycle_id = InstanceID;
2704
2705 EXCEPTION
2706 WHEN NO_DATA_FOUND THEN
2707 InstanceStatusCode:='ISCNotFound';
2708 InPrevStatusCode:='ACTIVE';
2709 end;
2710
2711 if InPrevStatusCode is null then
2712 InPrevStatusCode:='ACTIVE';
2713 end if;
2714
2715 -- if the status of the instance to be resumed is PAUSING
2716 -- all we need to do is set the status to previous status code
2717 if InstanceStatusCode = 'PAUSING' then
2718 update zpb_analysis_cycles
2719 set status_code= InPrevStatusCode
2720 where analysis_cycle_id = InstanceID;
2721
2722 resultout :='COMPLETE:Y';
2723 commit;
2724 return;
2725 end if;
2726
2727 -- 5867453 bkport of 5371156 add ownerID drop Item_key
2728 select sequence, owner_id
2729 into CurrtaskSeq, ownerID
2730 from ZPB_ANALYSIS_CYCLE_TASKS
2731 where ANALYSIS_CYCLE_ID = InstanceID and
2732 sequence = (select MAX(SEQUENCE) from ZPB_ANALYSIS_CYCLE_TASKS
2733 where ANALYSIS_CYCLE_ID = InstanceID and STATUS_CODE = 'COMPLETE');
2734
2735 -- When resume_instance is called from enable_cycle there is a
2736 -- specal case when transitioning from DISABLE_ASAP to ENABLE_FIRST
2737 if PResumeType = 'RUN_FROM_TOP' then
2738 CurrtaskSeq := 0;
2739 --
2740 update ZPB_ANALYSIS_CYCLE_TASKS
2741 set STATUS_CODE = null,
2742 LAST_UPDATED_BY = fnd_global.USER_ID,
2743 LAST_UPDATE_DATE = SYSDATE,
2744 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
2745 where ANALYSIS_CYCLE_ID = InstanceID;
2746 end if;
2747
2748 -- 5867453 bkport of 5371156 new source for base parmameters ownerID from task table above.
2749 select i.ANALYSIS_CYCLE_ID, c.name, c.BUSINESS_AREA_ID
2750 into ACID, ACNAME, l_business_area_id
2751 from zpb_analysis_cycle_instances i,
2752 zpb_analysis_cycles c
2753 where i.INSTANCE_AC_ID = InstanceID
2754 and i.ANALYSIS_CYCLE_ID = c.ANALYSIS_CYCLE_ID;
2755
2756 Owner := ZPB_WF_NTF.ID_to_FNDUser(OwnerID);
2757 respAppID := 210;
2758 for v_respID in c_respID loop
2759 RespID := v_respID.RESP_ID;
2760 exit;
2761 end loop;
2762
2763 -- Get next task [wf process] to run If none COMPLETE
2764
2765 workflowprocess := 'NONE';
2766 TaskID := NULL;
2767
2768 for v_Tasks in c_Tasks loop
2769 TaskID := v_Tasks.TASK_ID;
2770 workflowprocess := v_Tasks.wf_process_name;
2771 end loop;
2772
2773 -- LAST TASK FOR THIS INSTANCE
2774 if workflowprocess = 'NONE' then
2775
2776 update zpb_ANALYSIS_CYCLES
2777 set status_code = 'COMPLETE',
2778 LAST_UPDATED_BY = fnd_global.USER_ID,
2779 LAST_UPDATE_DATE = SYSDATE,
2780 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
2781 where ANALYSIS_CYCLE_ID = INSTANCEID;
2782
2783 update zpb_analysis_cycle_instances
2784 set last_update_date = sysdate,
2785 LAST_UPDATED_BY = fnd_global.USER_ID,
2786 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
2787 where instance_ac_id = INSTANCEID;
2788
2789 -- Mark for delete
2790 zpb_wf.markfordelete(ACID, ownerID, respID, respAppID);
2791
2792 resultout :='COMPLETE:N';
2793 return;
2794 end if;
2795
2796 -- Set item key and date
2797 charDate := to_char(sysdate, 'MM/DD/YYYY-HH24-MI-SS');
2798 newitemkey := rtrim(substr(ACName, 1, 50), ' ') || '-' || to_char(ACID) || '-' || to_char(CurrtaskSeq+1) || '-' || workflowprocess || '-' || charDate;
2799
2800
2801 -- Create WF start process instance
2802 wf_engine.CreateProcess(ItemType => ItemType,
2803 itemKey => newItemKey,
2804 process => WorkflowProcess);
2805
2806 -- This should be the EPB controller.
2807 wf_engine.SetItemOwner(ItemType => ItemType,
2808 ItemKey => NEWItemKey,
2809 owner => owner);
2810
2811 -- Set current value of Taskseq [not sure if it is always 1 might be for startup]
2812 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
2813 Itemkey => newItemKey,
2814 aname => 'TASKSEQ',
2815 avalue => CurrtaskSeq+1);
2816
2817 -- set globals for new key???
2818
2819 -- set Cycle ID!
2820 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
2821 Itemkey => newItemKey,
2822 aname => 'ACID',
2823 avalue => ACID);
2824
2825 -- abudnik 17NOV2005 BUSINESS AREA ID.
2826 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
2827 Itemkey => newItemKey,
2828 aname => 'BUSINESSAREAID',
2829 avalue => l_business_area_id);
2830
2831 -- set workflow with Instance Cycle ID!
2832 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
2833 Itemkey => newItemKey,
2834 aname => 'INSTANCEID',
2835 avalue => InstanceID);
2836
2837 -- set cycle Name!
2838 wf_engine.SetItemAttrText(Itemtype => ItemType,
2839 Itemkey => newItemKey,
2840 aname => 'ACNAME',
2841 avalue => ACNAME);
2842 -- set Task ID!
2843 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
2844 Itemkey => newItemKey,
2845 aname => 'TASKID',
2846 avalue => TaskID);
2847
2848 -- set owner name attr!
2849 wf_engine.SetItemAttrText(Itemtype => ItemType,
2850 Itemkey => newItemKey,
2851 aname => 'FNDUSERNAM',
2852 avalue => owner);
2853
2854 -- set EPBPerformer to owner name for notifications!
2855 wf_engine.SetItemAttrText(Itemtype => ItemType,
2856 Itemkey => newItemKey,
2857 aname => 'EPBPERFORMER',
2858 avalue => owner);
2859
2860
2861 -- will get error notifications
2862 wf_engine.SetItemAttrText(Itemtype => ItemType,
2863 Itemkey => newItemKey,
2864 aname => 'WF_ADMINISTRATOR',
2865 avalue => owner);
2866
2867 -- set owner ID!
2868 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
2869 Itemkey => newItemKey,
2870 aname => 'OWNERID',
2871 avalue => ownerID);
2872
2873 -- set resp ID!
2874 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
2875 Itemkey => newItemKey,
2876 aname => 'RESPID',
2877 avalue => respID);
2878
2879 -- set App resp ID!
2880 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
2881 Itemkey => newItemKey,
2882 aname => 'RESPAPPID',
2883 avalue => respAppID);
2884
2885 if workflowprocess = 'EXCEPTION' then
2886 -- plsql document procedure
2887 wf_engine.SetItemAttrText(itemtype => itemtype,
2888 itemkey => newItemKey,
2889 aname => 'RESPNOTE',
2890 avalue => 'PLSQL:ZPB_EXCEPTION_ALERT.NON_RESPONDERS/' || newItemKey );
2891 end if;
2892
2893
2894 -- Now that all is created and set START the PROCESS!
2895 wf_engine.StartProcess(ItemType => ItemType,
2896 ItemKey => newItemKey);
2897
2898 update zpb_analysis_cycle_tasks
2899 set item_KEY = newitemkey,
2900 Start_date = to_Date(charDate,'MM/DD/YYYY-HH24-MI-SS'),
2901 status_code = 'ACTIVE',
2902 LAST_UPDATED_BY = fnd_global.USER_ID,
2903 LAST_UPDATE_DATE = SYSDATE,
2904 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
2905 where ANALYSIS_CYCLE_ID = INSTANCEID and task_id = TaskID;
2906
2907 update ZPB_ANALYSIS_CYCLES
2908 set status_code = InPrevStatusCode,
2909 LAST_UPDATED_BY = fnd_global.USER_ID,
2910 LAST_UPDATE_DATE = SYSDATE,
2911 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
2912 where ANALYSIS_CYCLE_ID = INSTANCEID;
2913
2914 update zpb_analysis_cycle_instances
2915 set last_update_date = sysdate,
2916 LAST_UPDATED_BY = fnd_global.USER_ID,
2917 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
2918 where instance_ac_id = INSTANCEID;
2919
2920
2921
2922
2923
2924 resultout :='COMPLETE:Y';
2925
2926 commit;
2927 return;
2928
2929 exception
2930 when NO_DATA_FOUND then
2931 Null;
2932
2933 when others then
2934 raise;
2935
2936 end RESUME_INSTANCE;
2937 --
2938 -- added Concurrent_Wrapper 03/27/2003
2939 --
2940 -- abudnik 17NOV2005 BUSINESS AREA ID.
2941 procedure Concurrent_Wrapper (errbuf out nocopy varchar2,
2942 retcode out nocopy varchar2,
2943 ACID in Number,
2944 TaskID in Number,
2945 DataAW in Varchar2,
2946 CodeAW in Varchar2,
2947 AnnoAW in Varchar2,
2948 P_BUSINESS_AREA_ID in Number)
2949 IS
2950
2951 attached varchar2(1) := 'N';
2952 l_migration varchar2(4000);
2953 l_InstanceID number;
2954 thisCount number;
2955 returnStat varchar2(1000);
2956 msgData varchar2(1000);
2957 l_api_name varchar2(64) := 'Concurrent_Wrapper';
2958 l_published_by number;
2959
2960 -- 28 MIGRATION_INSTANCE
2961 CURSOR c_mparams is
2962 select value
2963 from ZPB_AC_PARAM_VALUES
2964 where ANALYSIS_CYCLE_ID = l_InstanceID and PARAM_ID = 28;
2965
2966 v_mparams c_mparams%ROWTYPE;
2967
2968
2969
2970
2971 BEGIN
2972
2973 retcode := '0';
2974
2975 -- set olap page pool size based on ZPB_OPPS_DATA_MOVE param for BP publisher
2976 begin
2977 select published_by into l_published_by
2978 from zpb_analysis_cycles
2979 where analysis_cycle_id = ACID;
2980
2981 zpb_util_pvt.set_opps(ZPB_UTIL_PVT.ZPB_OPPS_DATA_MOVE, l_published_by);
2982
2983 -- if somethinf goes wrong during olap page pool setting from forms param,
2984 -- continue with load data request
2985 exception
2986 when others then
2987 zpb_log.write_event(G_PKG_NAME || '.' || l_api_name, 'Could not set olap page pool size to profile value for ' || to_char(ACID));
2988
2989 end;
2990
2991
2992
2993 select analysis_cycle_id into l_InstanceID
2994 from zpb_analysis_cycle_tasks
2995 where task_id = TaskID;
2996
2997 ZPB_AW.INITIALIZE_FOR_AC (p_api_version => 1.0,
2998 p_init_msg_list => FND_API.G_TRUE,
2999 x_return_status => returnStat,
3000 x_msg_count => thisCount,
3001 x_msg_data => msgData,
3002 p_analysis_cycle_id => l_InstanceID,
3003 p_shared_rw => FND_API.G_TRUE);
3004 attached := 'Y';
3005
3006 l_migration := 'N';
3007 for v_mparams in c_mparams loop
3008 l_migration := v_mparams.VALUE;
3009 end loop;
3010
3011 -- b 5170327 intializes the pv_status variable for warnigs generated in AW
3012 ZPB_ERROR_HANDLER.INIT_CONC_REQ_STATUS;
3013
3014 if l_migration = 'Y' then
3015 ZPB_GEN_PHYS_MODEL.GEN_PHYSICAL_MODEL(l_InstanceID);
3016 end if;
3017
3018 zpb_data_load.run_data_load(TaskID, DataAW, CodeAW, AnnoAW);
3019
3020 -- b 5170327 - retcode is an OUT parameter conc program standard - 0=success, 1=warning or 2=error.
3021 -- for warnings from just above flow of calls.
3022 retcode := ZPB_ERROR_HANDLER.GET_CONC_REQ_STATUS;
3023
3024
3025 ZPB_AW.DETACH_ALL;
3026
3027 return;
3028
3029 exception
3030
3031 when others then
3032 retcode :='2';
3033
3034 if attached = 'Y' then
3035 ZPB_AW.DETACH_ALL;
3036 end if;
3037
3038 -- update zpb instance info with ERROR
3039 UPDATE_STATUS('ERROR', l_InstanceID, taskid, NULL);
3040
3041 errbuf:=substr(sqlerrm, 1, 255);
3042
3043
3044 end Concurrent_wrapper;
3045 --
3046 --
3047
3048 procedure SET_CURRINST (itemtype in varchar2,
3049 itemkey in varchar2,
3050 actid in number,
3051 funcmode in varchar2,
3052 resultout out nocopy varchar2)
3053
3054 IS
3055
3056 CurrtaskSeq number;
3057 ACID number;
3058 ACNAME varchar2(300);
3059 TaskID number;
3060 InstanceID number;
3061 ownerID number;
3062 respID number;
3063 respAppID number;
3064 sessionID number;
3065 DLcmd varchar2(100);
3066 P_OUTVAL number;
3067 reqID number;
3068 l_business_area_id number;
3069 BEGIN
3070
3071 IF (funcmode = 'RUN') THEN
3072 resultout :='ERROR';
3073
3074 ACID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
3075 Itemkey => ItemKey,
3076 aname => 'ACID');
3077 InstanceID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
3078 Itemkey => ItemKey,
3079 aname => 'INSTANCEID');
3080 TaskID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
3081 Itemkey => ItemKey,
3082 aname => 'TASKID');
3083
3084
3085 ownerID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
3086 Itemkey => ItemKey,
3087 aname => 'OWNERID');
3088 respID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
3089 Itemkey => ItemKey,
3090 aname => 'RESPID');
3091 respAppID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
3092 Itemkey => ItemKey,
3093 aname => 'RESPAPPID');
3094
3095 sessionid := userenv('SESSIONID');
3096
3097 DLcmd := 'call CM.SETCURRINST ('''||instanceID||''' '''||ACID||''')';
3098
3099 select BUSINESS_AREA_ID
3100 into l_business_area_id
3101 from ZPB_ANALYSIS_CYCLES
3102 where ANALYSIS_CYCLE_ID = ACId;
3103
3104 -- change agb to dbdata.
3105 ZPB_AW_WRITE_BACK.SUBMIT_WRITEBACK_REQUEST(l_business_area_id,
3106 ownerID,
3107 respID,
3108 sessionID,
3109 'SPL',
3110 DLcmd,
3111 NULL,
3112 P_OUTVAL);
3113
3114 -- P_OUTVAL is request ID
3115
3116 reqID := P_OUTVAL;
3117 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
3118 Itemkey => ItemKey,
3119 aname => 'REQUEST_ID',
3120 avalue => reqID);
3121
3122 resultout :='COMPLETE';
3123 return;
3124 end if;
3125
3126 exception
3127 when others then
3128 WF_CORE.CONTEXT('ZPB_WF', 'SET_CURRINST', itemtype, itemkey, to_char(actid), funcmode);
3129 raise;
3130
3131 end SET_CURRINST;
3132
3133 function GetEventACID(taskID in number) return varchar2
3134 AS
3135
3136 ACIDList varchar2(4000);
3137 ACIDtxt varchar2(30);
3138
3139 CURSOR c_eventACID is
3140 select analysis_cycle_id, v.status_code, v.validate_status
3141 from zpb_published_cycles_v v
3142 where v.status_code not in ('DISABLE_ASAP', 'DISABLE_NEXT') and
3143 v.validate_status = 'VALID' and
3144 v.analysis_cycle_id in (select pa.analysis_cycle_id
3145 from zpb_ac_param_values pa
3146 where pa.param_id = 20 and pa.value in (select d.value
3147 from ZPB_PROCESS_DETAILS_V d
3148 where d.name = 'CREATE_EVENT_IDENTIFIER' and d.task_id = TaskID));
3149
3150 v_eventACID c_eventACID%ROWTYPE;
3151
3152
3153 BEGIN
3154
3155 for v_eventACID in c_eventACID loop
3156
3157 if c_eventACID%ROWCOUNT = 1 then
3158 ACIDlist := to_char(v_eventACID.analysis_cycle_id);
3159 else
3160 ACIDtxt := to_char(v_eventACID.analysis_cycle_id);
3161 ACIDlist := ACIDlist ||',' || ACIDtxt;
3162 end if;
3163
3164 end loop;
3165
3166 return ACIDlist;
3167
3168 exception
3169 when others then
3170 raise;
3171 END;
3172
3173 procedure PREP_EVENT_ACID (itemtype in varchar2,
3174 itemkey in varchar2,
3175 actid in number,
3176 funcmode in varchar2,
3177 resultout out nocopy varchar2)
3178 IS
3179
3180 ACNAME varchar2(300);
3181 TaskID number;
3182 ActEntry varchar2(30);
3183 InstanceID number;
3184 ACIDlist varchar2(300);
3185 thisACID varchar2(16);
3186 owner varchar2(30);
3187 EventName varchar(4000);
3188 EventACNAME varchar2(300);
3189 NameList varchar(4000);
3190
3191 BEGIN
3192
3193 resultout := 'COMPLETE:NO_EVENTS';
3194
3195 SELECT ACTIVITY_NAME INTO ActEntry
3196 FROM WF_PROCESS_ACTIVITIES
3197 WHERE INSTANCE_ID=actid;
3198
3199
3200 -- get TaskID from attribute
3201 TaskID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
3202 Itemkey => ItemKey,
3203 aname => 'TASKID');
3204
3205 -- TaskName := wf_engine.GetItemAttrText(Itemtype => ItemType,
3206 -- Itemkey => ItemKey,
3207 -- aname => 'TASKNAME');
3208
3209 -- setup event name for message
3210 select value into EventName from zpb_task_parameters
3211 where task_ID = taskID and NAME = 'CREATE_EVENT_NAME';
3212
3213 wf_engine.SetItemAttrText(Itemtype => ItemType,
3214 Itemkey => ItemKey,
3215 aname => 'SUBJECT',
3216 avalue => EventName);
3217
3218
3219 -- get list of event ACIDs to run
3220
3221 If ActEntry = 'INIT_LIST' then
3222 ACIDlist := GetEventACID(taskID);
3223 else
3224 ACIDlist := wf_engine.GetItemAttrText(Itemtype => ItemType,
3225 Itemkey => ItemKey,
3226 aname => 'RESULT');
3227
3228 NameList := wf_engine.GetItemAttrText(Itemtype => ItemType,
3229 Itemkey => ItemKey,
3230 aname => 'MSGHISTORY');
3231 end if;
3232
3233 --Parse selection.
3234
3235 if ACIDlist is not NULL then
3236
3237 if length(ACIDlist) > 0 then
3238
3239 if instr(ACIDlist, ',') > 0 then
3240 thisACID := substr(ACIDlist, 1, instr(ACIDlist, ',') -1);
3241 ACIDlist := substr(ACIDlist, instr(ACIDlist, ',')+1);
3242 else
3243 thisACID := substr(ACIDlist, 1);
3244 ACIDlist := NULL;
3245 end if; -- comma
3246
3247 select NAME into EventACname
3248 from zpb_all_cycles_v
3249 where ANALYSIS_CYCLE_ID = thisACID;
3250
3251
3252 if (NameList is not null) then
3253 NameList := NameList || fnd_global.newline || EventACName;
3254 else
3255 NameList := EventACName;
3256 end if;
3257
3258 wf_engine.SetItemAttrText(Itemtype => ItemType,
3259 Itemkey => ItemKey,
3260 aname => 'MSGHISTORY',
3261 avalue => NameList);
3262
3263
3264 -- wf_engine.SetItemAttrText(Itemtype => ItemType,
3265 -- Itemkey => ItemKey,
3266 -- aname => 'ISSUEMSG',
3267 -- avalue => EventACName);
3268
3269
3270 -- This attribute is being overloaded for now.
3271 -- I'm using it to hold list of ACIDs
3272 -- adjusted acid list
3273 wf_engine.SetItemAttrText(Itemtype => ItemType,
3274 Itemkey => ItemKey,
3275 aname => 'RESULT',
3276 avalue => ACIDlist);
3277 zpb_wf.ACStart(thisACID, 'N', 'Y');
3278 dbms_lock.sleep(15);
3279 resultout :='COMPLETE:PROCEED';
3280 else
3281 -- 0 length remaining OR NULL
3282 resultout :='COMPLETE:NO_EVENTS';
3283 end if; -- length
3284 else
3285 resultout :='COMPLETE:NO_EVENTS';
3286 end if;
3287
3288 return;
3289
3290 exception
3291 when others then
3292 WF_CORE.CONTEXT('ZPB_WF', 'PREP_EVENT_ACID', itemtype, itemkey, to_char(actid), funcmode);
3293 raise;
3294
3295 end PREP_EVENT_ACID;
3296
3297
3298 procedure ENABLE_CYCLE(Pacid in number, PStatus in varchar2)
3299 IS
3300
3301
3302 ACstatusCode varchar2(30);
3303 InSTATCode varchar2(30);
3304 CodeToUpdateTo varchar2(30);
3305 InstanceID number;
3306 l_REQID number;
3307 ownerID number := fnd_global.USER_ID;
3308 respID number := fnd_global.RESP_ID;
3309 respAppID number := fnd_global.RESP_APPL_ID;
3310 ItemType varchar2(8) := 'ZPBSCHED';
3311 ItemKey varchar2(240);
3312 freqType varchar2(30);
3313 l_business_area_id number; -- abudnik 17NOV2005 BUSINESS AREA ID
3314
3315
3316 -- There may be active overlapping instances of an AC
3317 CURSOR c_instance is
3318
3319 select distinct zac.analysis_cycle_id
3320 FROM ZPB_ANALYSIS_CYCLE_INSTANCES zaci,
3321 ZPB_ANALYSIS_CYCLE_TASKS zact,
3322 ZPB_ANALYSIS_CYCLES zac
3323 WHERE zaci.analysis_cycle_id = Pacid and
3324 zaci.instance_ac_id = zac.analysis_cycle_id and
3325 zac.analysis_cycle_id = zact.analysis_cycle_id and
3326 zac.status_code = 'DISABLE_ASAP' and
3327 zact.status_code not in ('ACTIVE', 'PENDING');
3328 v_instnace c_instance%ROWTYPE;
3329
3330 -- 5867453 bkport of b5371156 new way to get params for setting context
3331 -- before submiting a request to the concurrent manager
3332 CURSOR c_owner_resp is
3333 select C.OWNER_ID, A.RESP_ID
3334 from zpb_analysis_cycles C, zpb_account_states A, fnd_responsibility_vl R
3335 where ANALYSIS_CYCLE_ID = Pacid
3336 AND A.ACCOUNT_STATUS = 0
3337 AND A.USER_ID = C.OWNER_ID
3338 AND C.BUSINESS_AREA_ID = A.BUSINESS_AREA_ID
3339 AND A.RESP_ID = R.RESPONSIBILITY_ID
3340 AND R.RESPONSIBILITY_KEY IN
3341 ('ZPB_MANAGER_RESP', 'ZPB_CONTROLLER_RESP', 'ZPB_SUPER_CONTROLLER_RESP');
3342
3343 v_owner_resp c_owner_resp%ROWTYPE;
3344
3345 -- This needs to
3346
3347 BEGIN
3348
3349 -- abudnik 17NOV2005 BUSINESS AREA ID.
3350 select STATUS_CODE, BUSINESS_AREA_ID
3351 into ACstatusCode, l_business_area_id
3352 from ZPB_ANALYSIS_CYCLES
3353 where ANALYSIS_CYCLE_ID = Pacid;
3354
3355 if instr(ACstatusCode, 'DISABLE') > 0 then
3356
3357 -- Set the BP Status to Enable
3358 update ZPB_ANALYSIS_CYCLES
3359 set STATUS_CODE = 'ENABLE_TASK',
3360 LAST_UPDATED_BY = fnd_global.USER_ID,
3361 LAST_UPDATE_DATE = SYSDATE,
3362 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
3363 where ANALYSIS_CYCLE_ID = Pacid;
3364 commit;
3365
3366
3367 if ACstatusCode = 'DISABLE_ASAP' then
3368
3369 --The type of enable determines what the status of disabled instances with active tasks
3370 --and the disabled instances with complete tasks should be updated to
3371
3372 CodeToUpdateTo:='ACTIVE';
3373
3374 if PStatus='ENABLE_TASK' then
3375 CodeToUpdateTo:='ACTIVE';
3376 end if;
3377
3378 if PStatus='ENABLE_FIRST' then
3379 CodeToUpdateTo:='ENABLE_FIRST';
3380 end if;
3381
3382 if PStatus='ENABLE_NEXT' then
3383 CodeToUpdateTo:='MARKED_FOR_DELETION';
3384 end if;
3385
3386 -- Update active instances
3387
3388 -- if updating to status ACTIVE, should in fact update to the
3389 -- previously saved status
3390
3391 if CodeToUpdateTo = 'ACTIVE' then
3392
3393 UPDATE zpb_analysis_cycles
3394 SET status_code=decode(prev_status_code, null, 'ACTIVE', prev_status_code)
3395 where analysis_cycle_id in
3396
3397 (select distinct zac.analysis_cycle_id
3398 FROM ZPB_ANALYSIS_CYCLE_INSTANCES zaci,
3399 ZPB_ANALYSIS_CYCLE_TASKS zact,
3400 ZPB_ANALYSIS_CYCLES zac
3401 WHERE zaci.analysis_cycle_id = Pacid and
3402 zaci.instance_ac_id = zac.analysis_cycle_id and
3403 zac.analysis_cycle_id = zact.analysis_cycle_id and
3404 zac.status_code = 'DISABLE_ASAP' and
3405 zact.status_code in ('ACTIVE', 'PENDING'));
3406 else
3407 UPDATE zpb_analysis_cycles
3408 SET status_code=CodeToUpdateTo
3409 where analysis_cycle_id in
3410
3411 (select distinct zac.analysis_cycle_id
3412 FROM ZPB_ANALYSIS_CYCLE_INSTANCES zaci,
3413 ZPB_ANALYSIS_CYCLE_TASKS zact,
3414 ZPB_ANALYSIS_CYCLES zac
3415 WHERE zaci.analysis_cycle_id = Pacid and
3416 zaci.instance_ac_id = zac.analysis_cycle_id and
3417 zac.analysis_cycle_id = zact.analysis_cycle_id and
3418 zac.status_code = 'DISABLE_ASAP' and
3419 zact.status_code in ('ACTIVE', 'PENDING'));
3420 end if;
3421
3422
3423 -- loop over instances with completetd tasks resume if enable choice is not enable next,
3424 -- if choice is enable next, mark for deletion and clean up instance
3425 -- 5867453 bkport of b5371156 new way to get params for setting context
3426 for v_owner_resp in c_owner_resp loop
3427 ownerID := v_owner_resp.owner_id;
3428 respID :=v_owner_resp.resp_id;
3429 exit;
3430 end loop;
3431 -- always EPB
3432 respAppID := 210;
3433
3434 -- Set the context before calling submit_request
3435 fnd_global.apps_initialize(ownerID, respID, RespAppId);
3436
3437 for v_instance in c_instance loop
3438
3439 InstanceID := v_instance.ANALYSIS_CYCLE_ID;
3440
3441 -- If enable_asap we resume the instance from the left-on task
3442 -- The associated AW measure for the instance is unchanged
3443 if PStatus = 'ENABLE_TASK' then
3444 zpb_wf.resume_instance(InstanceID);
3445 end if;
3446
3447 -- If enable first we restart the instance from its first task
3448 -- we also set the context and submit a CM request that will
3449 -- clean out the AW measure for the instance and recreate it again
3450 if PStatus = 'ENABLE_FIRST' then
3451
3452 -- for monitor page display set stauts to enable_first here
3453 -- it will get reset to active once instance restarts
3454 update zpb_analysis_cycles
3455 set status_code='ENABLE_FIRST'
3456 where analysis_cycle_id=InstanceID;
3457
3458 l_REQID := FND_REQUEST.SUBMIT_REQUEST ('ZPB', 'ZPB_CLEANANDRESTARTINST', NULL, NULL, FALSE, Pacid, InstanceID, l_business_area_id);
3459 end if;
3460
3461 if PStatus = 'ENABLE_NEXT' then
3462 -- In the case of Enabling Next, Both active and inactive instances should be set to MARKED_FOR_DELETION
3463 update zpb_analysis_cycles
3464 set status_code='MARKED_FOR_DELETION'
3465 where analysis_cycle_id=InstanceID;
3466
3467 -- clean the measure
3468 l_REQID := FND_REQUEST.SUBMIT_REQUEST ('ZPB', 'ZPB_WF_DELAWINST', NULL, NULL, FALSE, InstanceID, ownerID, l_business_area_id);
3469 end if;
3470 end loop;
3471 end if;
3472
3473 -- if this BP has schedule frequency type = ONE_TIME_ONLY, we should create an instance immeditely,
3474 -- if no active instances exist. To do so, we restart the scheduler for the BP
3475
3476 begin
3477
3478 select pva.value into freqType
3479 from zpb_ac_param_values pva,
3480 fnd_lookup_values_vl pna
3481 where pna.lookup_code='CALENDAR_FREQUENCY_TYPE'
3482 and pna.tag = pva.param_id and
3483 pna.lookup_type = 'ZPB_PARAMS' and
3484 pva.analysis_cycle_id=Pacid;
3485
3486 exception
3487 when NO_DATA_FOUND then
3488 freqType:='NOT_FOUND';
3489 end;
3490
3491 -- BUG 4291814 - WORKFLOW COMPONENTS: START BP EXTERNALLY
3492 -- BUG 4496397 - Only kick off new instace in One-Time-Only and Enable
3493 -- from next run case
3494 if (freqType='ONE_TIME_ONLY' and PStatus='ENABLE_NEXT') or freqType= 'EXTERNAL_EVENT' then
3495 zpb_wf.ACStart(Pacid,'Y');
3496 end if;
3497
3498
3499 end if; -- outermost
3500
3501 commit;
3502 return;
3503
3504 exception
3505 when NO_DATA_FOUND then
3506 Null;
3507
3508 when others then
3509 raise;
3510
3511 end ENABLE_CYCLE;
3512
3513 procedure INIT_BUSINESS_PROCESS (ACID in Number,
3514 InstanceID in Number,
3515 TaskID in Number,
3516 UserID in Number)
3517 IS
3518 attached varchar2(1) := 'N';
3519 thisCount number;
3520 returnStat varchar2(1000);
3521 msgData varchar2(1000);
3522 BEGIN
3523
3524 select count(wf_process_name)
3525 into thisCount
3526 from zpb_analysis_cycle_tasks
3527 where analysis_cycle_id = InstanceID and
3528 wf_process_name in ('LOAD_DATA', 'GENERATE_TEMPLATE', 'DISTRIBUTE_TEMPLATE', 'SOLVE', 'MANAGE_SUBMISSION');
3529
3530 if thisCount > 0 then -- build phys model
3531
3532 -- set olap page pool size based on ZPB_OPPS_AW_BUILD profile setting
3533 zpb_util_pvt.set_opps(ZPB_UTIL_PVT.ZPB_OPPS_AW_BUILD, UserID);
3534
3535 --log START Generate Physical Model
3536 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_WF_GENPHYS_START');
3537 FND_FILE.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
3538
3539 ZPB_AW.INITIALIZE_FOR_AC (p_api_version => 1.0,
3540 p_init_msg_list => FND_API.G_TRUE,
3541 x_return_status => returnStat,
3542 x_msg_count => thisCount,
3543 x_msg_data => msgData,
3544 p_analysis_cycle_id => InstanceID,
3545 p_shared_rw => FND_API.G_TRUE);
3546 -- Test run of solve
3547 attached := 'Y';
3548
3549 ZPB_GEN_PHYS_MODEL.GEN_PHYSICAL_MODEL(InstanceID);
3550
3551 ZPB_AW.DETACH_ALL;
3552 attached := 'N';
3553
3554 --log END Generate Physical Model has completed
3555 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_WF_GENPHYS_END');
3556 FND_FILE.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
3557
3558 -- NEW CALL to intialize process run data for instance
3559 ZPB_WF.INIT_PROC_RUN_DATA (ACID, InstanceID, TaskID, UserID);
3560
3561 end if;
3562 return;
3563
3564 exception
3565 when others then
3566
3567 --log Generate Physical Model has errored
3568 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_WF_GENPHYS_ERROR');
3569 FND_FILE.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
3570
3571 if attached = 'Y' then
3572 ZPB_AW.DETACH_ALL;
3573 end if;
3574
3575 -- update zpb instance info with ERROR
3576 UPDATE_STATUS('ERROR', Instanceid);
3577 raise;
3578 end INIT_BUSINESS_PROCESS;
3579
3580 procedure RUN_SOLVE (errbuf out nocopy varchar2,
3581 retcode out nocopy varchar2,
3582 InstanceID in Number,
3583 TaskID in Number,
3584 UserID in Number,
3585 P_BUSINESS_AREA_ID in number)
3586 IS
3587
3588 attached varchar2(1) := 'N';
3589 l_dbname varchar2(150);
3590 l_count number;
3591
3592 BEGIN
3593
3594 retcode := '0';
3595
3596 -- set olap page pool size based on ZPB_OPPS_DATA_SOLVE profile setting
3597 zpb_util_pvt.set_opps(ZPB_UTIL_PVT.ZPB_OPPS_DATA_SOLVE, UserID);
3598
3599 -- Test run of solve
3600 ZPB_AW.INITIALIZE_FOR_AC (p_api_version => 1.0,
3601 p_init_msg_list => FND_API.G_TRUE,
3602 x_return_status => retcode,
3603 x_msg_count => l_count,
3604 x_msg_data => errbuf,
3605 p_analysis_cycle_id => InstanceID,
3606 p_shared_rw => FND_API.G_TRUE);
3607 attached := 'Y';
3608
3609 l_dbname := ZPB_AW.GET_SCHEMA || '.' || ZPB_AW.GET_SHARED_AW;
3610 ZPB_AW.EXECUTE('APPS_USER_ID = ''' || TO_CHAR(UserID) || '''');
3611
3612 -- b 5170327 intializes the pv_status variable
3613 ZPB_ERROR_HANDLER.INIT_CONC_REQ_STATUS;
3614 ZPB_AW.EXECUTE('call SV.RUN.SOLVE(''' || l_dbname || ''', ''' || TO_CHAR(Instanceid) || ''', ''' || TO_CHAR(taskid) || ''')');
3615 -- b 5170327 - retcode is an OUT parameter conc program standard - 0=success, 1=warning or 2=error.
3616 retcode := ZPB_ERROR_HANDLER.GET_CONC_REQ_STATUS;
3617
3618 -- update
3619 ZPB_AW.EXECUTE('upd');
3620 commit;
3621
3622 ZPB_AW.DETACH_ALL;
3623 attached := 'N';
3624
3625 --log solve OK
3626 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_WF_SOLVEOK');
3627 FND_FILE.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
3628
3629 return;
3630
3631 exception
3632
3633 when others then
3634 retcode :='2';
3635
3636 if attached = 'Y' then
3637 ZPB_AW.DETACH_ALL;
3638 end if;
3639
3640 --log solve OK
3641 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_WF_SOLVE_NOTOK');
3642 FND_FILE.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
3643 errbuf:=substr(sqlerrm, 1, 255);
3644
3645 -- update zpb instance info with ERROR
3646 UPDATE_STATUS('ERROR', Instanceid, taskid, UserID);
3647
3648 end RUN_SOLVE;
3649
3650
3651 procedure UPDATE_STATUS (p_type in varchar2,
3652 p_InstanceID in Number default NULL,
3653 p_TaskID in Number default NULL,
3654 p_UserID in Number default NULL)
3655 IS
3656
3657
3658 l_InstanceID number;
3659 instance_status varchar2(30);
3660 l_REQID number;
3661 ownerID number;
3662 ACID number;
3663 l_business_area_id number;
3664
3665 respIDW number := fnd_global.RESP_ID;
3666 respAppID number := fnd_global.RESP_APPL_ID;
3667 ItemType varchar2(8) := 'ZPBSCHED';
3668 ItemKey varchar2(240);
3669
3670 -- get RespId and RespAppId from params set on the workflow sched for this BP
3671 CURSOR c_wfItemKey is
3672 select /*+ FIRST_ROWS */ item_key
3673 from WF_ITEM_ATTRIBUTE_VALUES
3674 where item_type = 'ZPBSCHED'
3675 and name = 'ACID'
3676 and number_value = ACID;
3677 v_wfItemKey c_wfItemKey%ROWTYPE;
3678
3679 -- can also get RespId and RespAppId by simply selecting first ones found for owner of BP
3680 CURSOR c_respFromOwner is
3681 select /*+ FIRST_ROWS */ responsibility_id, responsibility_application_id
3682 from fnd_user_resp_groups
3683 where user_id=ownerID;
3684 v_respFromOwner c_respFromOwner%ROWTYPE;
3685
3686
3687 BEGIN
3688
3689 begin
3690
3691 select status_code, published_by into instance_status, ownerID
3692 from zpb_analysis_cycles
3693 where analysis_cycle_id=p_InstanceID;
3694
3695 EXCEPTION
3696 WHEN NO_DATA_FOUND THEN
3697 instance_status:='NotMarkedForDelete';
3698
3699 end;
3700
3701 -- If instance has been marked for deletion and last task
3702 -- errors out, keep instancein MARKED_FOR_DELETE status
3703 -- and clean up its AW measure
3704 if instance_status='MARKED_FOR_DELETION' then
3705
3706 -- abudnik 17NOV2005 BUSINESS AREA ID.
3707 select ANALYSIS_CYCLE_ID into ACID
3708 from ZPB_ANALYSIS_CYCLE_INSTANCES
3709 where INSTANCE_AC_ID = p_InstanceID;
3710
3711 select BUSINESS_AREA_ID
3712 into l_business_area_id
3713 from ZPB_ANALYSIS_CYCLES
3714 where ANALYSIS_CYCLE_ID = ACId;
3715
3716 -- abudnik 17NOV2005 BUSINESS AREA ID.
3717 l_REQID := FND_REQUEST.SUBMIT_REQUEST ('ZPB', 'ZPB_WF_DELAWINST', NULL, NULL, FALSE, p_InstanceID, ownerID, l_business_area_id);
3718 return;
3719 end if;
3720
3721
3722 if p_TaskID is NOT NULL then
3723
3724 update zpb_analysis_cycle_tasks
3725 set status_code = p_type,
3726 LAST_UPDATED_BY = fnd_global.USER_ID,
3727 LAST_UPDATE_DATE = SYSDATE,
3728 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
3729 where task_id = p_TaskID;
3730 else
3731
3732 if p_instanceID is NULL then
3733 -- return 'NOINFO';
3734 return;
3735 end if;
3736
3737 end if;
3738
3739
3740
3741 if p_instanceID is NOT NULL then
3742
3743 update zpb_ANALYSIS_CYCLES
3744 set status_code = p_type,
3745 LAST_UPDATED_BY = fnd_global.USER_ID,
3746 LAST_UPDATE_DATE = SYSDATE,
3747 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
3748 where ANALYSIS_CYCLE_ID = p_InstanceID;
3749
3750 update zpb_analysis_cycle_instances
3751 set last_update_date = sysdate,
3752 LAST_UPDATED_BY = fnd_global.USER_ID,
3753 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
3754 where instance_ac_id = p_InstanceID;
3755
3756
3757 else
3758
3759 if p_TaskID is NOT NULL then
3760 select distinct ANALYSIS_CYCLE_ID into l_InstanceID
3761 from zpb_analysis_cycle_tasks
3762 where task_id = p_TaskID;
3763
3764 update zpb_ANALYSIS_CYCLES
3765 set status_code = p_type,
3766 LAST_UPDATED_BY = fnd_global.USER_ID,
3767 LAST_UPDATE_DATE = SYSDATE,
3768 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
3769 where ANALYSIS_CYCLE_ID = l_InstanceID;
3770
3771 update zpb_analysis_cycle_instances
3772 set last_update_date = sysdate,
3773 LAST_UPDATED_BY = fnd_global.USER_ID,
3774 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
3775 where instance_ac_id = l_InstanceID;
3776
3777
3778 end if;
3779
3780 end if;
3781
3782 -- if updated the status to ERROR, should check to see if any instances of this BP now need to be cleaned up
3783 if p_type='ERROR' then
3784
3785 select ANALYSIS_CYCLE_ID into ACID
3786 from ZPB_ANALYSIS_CYCLE_INSTANCES
3787 where INSTANCE_AC_ID = p_InstanceID;
3788
3789 ItemKey:='NOT_FOUND';
3790
3791 for v_wfItemKey in c_wfItemKey loop
3792 ItemKey:=v_wfItemKey.item_key;
3793 end loop;
3794
3795 -- try to get RespId and RespAppId from Scheduler workflow for this BP
3796 -- if that fails, get any RespId and RespAppId for BP owner
3797 if ItemKey<>'NOT_FOUND' and ItemKey<>null and ItemKey<>' 'then
3798
3799 respIDW := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
3800 Itemkey => ItemKey,
3801 aname => 'RESPID');
3802
3803 respAppID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
3804 Itemkey => ItemKey,
3805 aname => 'RESPAPPID');
3806
3807 else
3808
3809 for v_respFromOwner in c_respFromOwner loop
3810 respIDW:=v_respFromOwner.responsibility_id;
3811 respAppID := v_respFromOwner.responsibility_application_id;
3812 exit;
3813 end loop;
3814
3815 end if;
3816
3817 -- This call will clean up as many COMPLETED/ERRORED instances
3818 -- as determined by the Number of Processes Stored BP option
3819 MarkforDelete(ACID, ownerID, respIDW, RespAppID);
3820
3821 commit;
3822 end if;
3823
3824
3825
3826 -- return 'DONE';
3827 return;
3828
3829 exception
3830 when others then
3831 raise;
3832
3833 end UPDATE_STATUS;
3834
3835
3836 --
3837 -- Kicks off a CM to submit data up to the shared
3838 --
3839 procedure SUBMIT_TO_SHARED (p_user in number,
3840 p_templateID in number,
3841 p_retVal out nocopy number)
3842 is
3843 l_business_area_id number;
3844 begin
3845 select distinct A.BUSINESS_AREA_ID
3846 into l_business_area_id
3847 from ZPB_ANALYSIS_CYCLES A,
3848 ZPB_DC_OBJECTS B
3849 where B.AC_INSTANCE_ID = A.ANALYSIS_CYCLE_ID
3850 and B.TEMPLATE_ID = p_templateID;
3851
3852 ZPB_AW_WRITE_BACK.SUBMIT_WRITEBACK_REQUEST
3853 (l_business_area_id,
3854 p_user,
3855 FND_GLOBAL.RESP_ID,
3856 FND_GLOBAL.SESSION_ID,
3857 'SPL',
3858 'CALL PA.ATTACH.PERSONAL('''||p_user||''' ''ro'');'||
3859 'CALL DC.SUBMIT.DRIVER(''ACCEPT'' ''-100'' '''||p_user||''' '''||
3860 p_templateID||''')',
3861 sysdate,
3862 p_retVal);
3863 end SUBMIT_TO_SHARED;
3864
3865 --
3866 -- copies old instance of data to the new instance
3867 --
3868 procedure INIT_PROC_RUN_DATA (ACID in Number,
3869 InstanceID in Number,
3870 TaskID in Number,
3871 UserID in Number)
3872 IS
3873 attached varchar2(1) := 'N';
3874 l_count number;
3875 l_dbname varchar2(150);
3876 retcode varchar2(100);
3877 msgData varchar2(1000);
3878 BEGIN
3879
3880 -- retcode := '0';
3881
3882 --log Initialization of Business Process Run data has started
3883 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_WF_INIT_DATA_START');
3884 FND_FILE.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
3885
3886 ZPB_AW.INITIALIZE_FOR_AC (p_api_version => 1.0,
3887 p_init_msg_list => FND_API.G_TRUE,
3888 x_return_status => retcode,
3889 x_msg_count => l_count,
3890 x_msg_data => msgData,
3891 p_analysis_cycle_id => InstanceID,
3892 p_shared_rw => FND_API.G_TRUE);
3893
3894 l_dbname := ZPB_AW.GET_SCHEMA || '.' || ZPB_AW.GET_SHARED_AW;
3895 attached := 'Y';
3896
3897
3898 ZPB_AW.EXECUTE('call in.run.init(''' || l_dbname || ''', ''' || TO_CHAR(ACID) || ''', ''' || TO_CHAR(taskid) || ''', ''' || TO_CHAR(Instanceid) || ''')');
3899
3900 -- update
3901 ZPB_AW.EXECUTE('upd');
3902 commit;
3903
3904 ZPB_AW.DETACH_ALL;
3905 attached := 'N';
3906
3907 --log Initialization of Business Process Run data has completed
3908 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_WF_INIT_DATA_END');
3909 FND_FILE.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
3910
3911 return;
3912
3913 exception
3914 when others then
3915 -- retcode :='2';
3916
3917 if attached = 'Y' then
3918 ZPB_AW.DETACH_ALL;
3919 end if;
3920
3921 --log Initialization of Business Process Run data encountered an error
3922 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_WF_INIT_DATA_ERROR');
3923 FND_FILE.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
3924 -- errbuf:=substr(sqlerrm, 1, 255);
3925
3926 -- update zpb instance info with ERROR
3927 UPDATE_STATUS('ERROR', Instanceid);
3928 raise;
3929
3930 end INIT_PROC_RUN_DATA;
3931
3932
3933 procedure WF_DELAWINST (errbuf out nocopy varchar2,
3934 retcode out nocopy varchar2,
3935 InstanceID in Number,
3936 UserID in Number,
3937 P_BUSINESS_AREA_ID in Number)
3938 IS
3939 attached varchar2(1) := 'N';
3940 l_count number;
3941 --Bug - 5126892: Change start
3942 instanceDesc ZPB_ANALYSIS_CYCLE_INSTANCES.INSTANCE_DESCRIPTION%type;
3943 --Bug - 5126892: Change end
3944 BEGIN
3945
3946 retcode := '0';
3947
3948 --log DEL INST OK
3949 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_WF_BEGDELINST');
3950 FND_FILE.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
3951
3952 delete from zpb_measures
3953 where instance_id = InstanceID;
3954
3955 ZPB_AW.INITIALIZE_FOR_AC (p_api_version => 1.0,
3956 p_init_msg_list => FND_API.G_TRUE,
3957 x_return_status => retcode,
3958 x_msg_count => l_count,
3959 x_msg_data => errbuf,
3960 p_analysis_cycle_id => InstanceID,
3961 p_shared_rw => FND_API.G_TRUE,
3962 p_annot_rw => FND_API.G_TRUE);
3963 attached := 'Y';
3964
3965 ZPB_AW.EXECUTE('call cm.delshinsts(''' || TO_CHAR(Instanceid) || ''')');
3966
3967 --Bug - 5126892: Change start
3968
3969 -- Bug: 5753320
3970 -- Handled the no_data_found exception
3971 Begin
3972 select instance_description
3973 into instanceDesc from zpb_analysis_cycle_instances
3974 where instance_ac_id = Instanceid;
3975 exception
3976 when no_data_found then
3977 instanceDesc := '-99-';
3978 end;
3979
3980 -- Bug: 5753320
3981 -- If there was no record in zpb_analysis_cycle_instances,
3982 -- it means that the instance already got deleted.
3983 -- So, do not call calc.validate
3984 if (instanceDesc <> '-99-') then
3985 ZPB_AW.EXECUTE('call calc.validate(false,'''||To_CHAR(instanceDesc)||''',''' || TO_CHAR(UserID) || ''','''||'View'||''')');
3986 end if;
3987 --Bug - 5126892: Change end
3988
3989 -- update
3990 ZPB_AW.EXECUTE('upd');
3991 commit;
3992
3993 ZPB_AW.DETACH_ALL;
3994 attached := 'N';
3995
3996 --log DEL INST OK
3997 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_WF_ENDDELINST');
3998 FND_FILE.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
3999
4000 return;
4001
4002 exception
4003
4004 when others then
4005 retcode :='2';
4006
4007 if attached = 'Y' then
4008 ZPB_AW.DETACH_ALL;
4009 end if;
4010
4011 --log DEL INST ERR
4012 FND_MESSAGE.SET_NAME('ZPB', 'ZPB_WF_ERRDELINST');
4013 FND_FILE.put_line(FND_FILE.LOG, FND_MESSAGE.GET);
4014 errbuf:=substr(sqlerrm, 1, 255);
4015
4016 -- do not update zpb instance info with ERROR from this procedure
4017 -- as this would cause another DELAWISNT CM request causing
4018 -- an infinite loop
4019
4020 end WF_DELAWINST;
4021
4022 --
4023 -- calls abortWorkflow with 'A' acid argument
4024 --
4025 Procedure CallWFAbort(inACID in number)
4026
4027 is
4028
4029 thisInst number;
4030 retcode varchar2(2);
4031 errbuf varchar2(100);
4032
4033 BEGIN
4034
4035 -- find all workflows for this ACID or instance, abort.
4036 ZPB_WF.abortWorkflow(errbuf, retcode, inACID, 'A');
4037
4038 return;
4039
4040 exception
4041
4042 when others then
4043 RAISE_APPLICATION_ERROR(-20100, 'Error in ZPB_WF.CallWFABORT');
4044
4045 end CallWFAbort;
4046
4047 procedure AbortWorkflow (errbuf out nocopy varchar2,
4048 retcode out nocopy varchar2,
4049 inACID in Number,
4050 ACIDType in varchar2 default 'A')
4051 IS
4052 --ItemType varchar2(20);
4053 AttrName varchar2(30);
4054 CurrStatus varchar2(20);
4055 result varchar2(100);
4056
4057 CURSOR c_ItemKeys is
4058 select item_type, item_key
4059 from WF_ITEM_ATTRIBUTE_VALUES
4060 where item_type = 'ZPBSCHED'
4061 and name = AttrName
4062 and number_value = inACID;
4063
4064 v_ItemKey c_ItemKeys%ROWTYPE;
4065
4066 BEGIN
4067
4068 retcode := '0';
4069
4070 if ACIDType = 'I' then
4071 AttrName := 'INSTANCEID';
4072 else
4073 AttrName := 'ACID';
4074 end if;
4075
4076 -- Check activity process for current plan
4077 -- 04/23/03 agb ZPBSCHED support for many item types
4078 for v_ItemKey in c_ItemKeys loop
4079
4080 wf_engine.ItemStatus(v_ItemKey.item_type, v_ItemKey.item_key, currStatus, result);
4081
4082 if UPPER(RTRIM(currStatus)) = 'ERROR' or UPPER(RTRIM(currStatus)) = 'ACTIVE' then
4083 WF_ENGINE.AbortProcess(v_ItemKey.item_Type, v_ItemKey.item_key);
4084 end if;
4085
4086 end loop;
4087 return;
4088
4089 exception
4090
4091 when NO_DATA_FOUND then
4092 retcode :='0';
4093 when others then
4094 retcode :='2';
4095 errbuf:=substr(sqlerrm, 1, 255);
4096 -- raise;
4097
4098 end AbortWorkflow;
4099
4100
4101 --
4102 -- If BP has no active instances and no completed instances, deletes CurrentInstance measure from AW
4103 -- If BP has no active instances and some completed instances, resets CurrentInstance measure to last started instance
4104 -- If BP has active instances does nothing
4105 --
4106 procedure DeleteCurrInstMeas (ACId in number,
4107 ownerId in number)
4108 IS
4109
4110 activeInstances number;
4111 completedInstances number;
4112 currInstanceId number;
4113 currInstExistsCnt number;
4114 lastCompleted number;
4115 sessionid number;
4116 DLcmd varchar2(100);
4117 P_OUTVAL number;
4118 l_REQID number;
4119 respID number := fnd_global.RESP_ID;
4120 ACStatusCode varchar2(30);
4121 respIDW number := fnd_global.RESP_ID;
4122 respAppID number := fnd_global.RESP_APPL_ID;
4123 ItemType varchar2(8) := 'ZPBSCHED';
4124 ItemKey varchar2(240);
4125 l_business_area_id number;
4126
4127 -- Need itemKey for ACID to get variables to set context before
4128 -- calling SUBMIT_REQUEST
4129 CURSOR c_wfItemKey is
4130 select /*+ FIRST_ROWS */ item_key
4131 from WF_ITEM_ATTRIBUTE_VALUES
4132 where item_type = 'ZPBSCHED'
4133 and name = 'ACID'
4134 and number_value = ACId;
4135 v_wfItemKey c_wfItemKey%ROWTYPE;
4136
4137 -- can also get RespId and RespAppId by simply selecting first ones found for owner of BP
4138 CURSOR c_respFromOwner is
4139 select /*+ FIRST_ROWS */ responsibility_id, responsibility_application_id
4140 from fnd_user_resp_groups
4141 where user_id=ownerId;
4142 v_respFromOwner c_respFromOwner%ROWTYPE;
4143
4144 begin
4145
4146 begin
4147
4148 -- abudnik 17NOV2005 BUSINESS AREA ID
4149 select status_code, BUSINESS_AREA_ID
4150 into ACStatusCode, l_business_area_id
4151 from zpb_analysis_cycles
4152 where analysis_cycle_id = ACId;
4153
4154 EXCEPTION
4155 WHEN NO_DATA_FOUND THEN
4156 ACStatusCode:='Do Not Delete';
4157 end;
4158
4159 -- If the BP definition itself is not marked for deletion
4160 -- do not remove the Current Instance Measure
4161 if ACStatusCode<>'MARKED_FOR_DELETION' then
4162 return;
4163 end if;
4164
4165 -- If the BP has never had a Set Current Instance task and therefore
4166 -- does not have a Current Instance, do not attempt to remove the
4167 -- non-existent current instance
4168 begin
4169 select current_instance_id into currInstanceId
4170 from zpb_analysis_cycles
4171 where analysis_cycle_id = ACId;
4172
4173 select count(*) into currInstExistsCnt
4174 from zpb_measures
4175 where instance_id = currInstanceId;
4176
4177 if currInstExistsCnt = 0 then
4178 return;
4179 end if;
4180
4181 EXCEPTION
4182 WHEN NO_DATA_FOUND THEN
4183 return;
4184 end;
4185
4186 select count(*) into activeInstances
4187 FROM ZPB_ANALYSIS_CYCLE_INSTANCES zaci,
4188 ZPB_ANALYSIS_CYCLE_TASKS zact,
4189 ZPB_ANALYSIS_CYCLES zac
4190 WHERE zaci.analysis_cycle_id = ACId and
4191 zaci.instance_ac_id = zac.analysis_cycle_id and
4192 zac.analysis_cycle_id = zact.analysis_cycle_id and
4193 zact.status_code in ('ACTIVE', 'PENDING');
4194
4195 -- if no active instances for this BP then can delete/reassign the CurrentInstance measure
4196 -- otherwise we are done here
4197 IF activeInstances=0 THEN
4198
4199 begin
4200
4201 SELECT zac2.analysis_cycle_id into lastCompleted
4202 FROM ZPB_ANALYSIS_CYCLE_INSTANCES zaci2,
4203 ZPB_ANALYSIS_CYCLE_TASKS zact2,
4204 ZPB_ANALYSIS_CYCLES zac2
4205 WHERE zaci2.analysis_cycle_id = ACId and
4206 zaci2.instance_ac_id = zac2.analysis_cycle_id and
4207 zac2.analysis_cycle_id = zact2.analysis_cycle_id and
4208 zact2.wf_process_name='SET_CURRENT_INSTANCE' and
4209 zac2.status_code in ('COMPLETE', 'COMPLETE_WITH_WARNING') and
4210 zact2.last_update_date =
4211 (SELECT max(zact.last_update_date)
4212 FROM ZPB_ANALYSIS_CYCLE_INSTANCES zaci,
4213 ZPB_ANALYSIS_CYCLE_TASKS zact,
4214 ZPB_ANALYSIS_CYCLES zac
4215 WHERE zaci.analysis_cycle_id = ACId and
4216 zaci.instance_ac_id = zac.analysis_cycle_id and
4217 zac.analysis_cycle_id = zact.analysis_cycle_id and
4218 zact.wf_process_name='SET_CURRENT_INSTANCE' and
4219 zac.status_code in ('COMPLETE', 'COMPLETE_WITH_WARNING'));
4220
4221
4222 completedInstances:=1;
4223
4224 EXCEPTION
4225 WHEN NO_DATA_FOUND THEN
4226 completedInstances:=0;
4227 end;
4228
4229 IF lastCompleted IS NULL THEN
4230 completedInstances:=0;
4231 END IF;
4232
4233 -- if there are no completed instances, then delete the CurrentInstance measure
4234 -- otherwise re-set the CurrentInstance measure to the completed instance that started last, via CM request
4235 IF completedInstances=0 THEN
4236
4237 begin
4238
4239 -- attemp to retrieve responsibility Id and responsibility App Id from BP Scheduler
4240 -- if BP Scheduler no longer exists, get any responsibility Id and responsibility App Id
4241 -- for the owner of this BP and submit the request
4242 ItemKey:='NOT_INITIALIZED';
4243
4244 for v_wfItemKey in c_wfItemKey loop
4245 ItemKey:=v_wfItemKey.item_key;
4246 end loop;
4247
4248 if ItemKey<>'NOT_INITIALIZED' and ItemKey<>null and ItemKey<>' ' then
4249
4250 respIDW := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
4251 Itemkey => ItemKey,
4252 aname => 'RESPID');
4253
4254 respAppID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
4255 Itemkey => ItemKey,
4256 aname => 'RESPAPPID');
4257 else
4258
4259 for v_respFromOwner in c_respFromOwner loop
4260 respIDW:=v_respFromOwner.responsibility_id;
4261 respAppID := v_respFromOwner.responsibility_application_id;
4262 exit;
4263 end loop;
4264 end if;
4265
4266
4267 -- Set context before calling SUBMIT_REQUEST
4268 fnd_global.apps_initialize(ownerID, respIDW, RespAppID);
4269
4270 -- abudnik 17NOV2005 BUSINESS AREA ID
4271 l_REQID := FND_REQUEST.SUBMIT_REQUEST ('ZPB', 'ZPB_WF_DELAWINST', NULL, NULL, FALSE, currInstanceId, ownerID, l_business_area_id);
4272
4273 EXCEPTION
4274 WHEN NO_DATA_FOUND THEN
4275 NULL;
4276 end;
4277
4278 ELSE
4279 -- there are completed instance, must reset CurrentInstance measure to last started instance
4280
4281 sessionid := userenv('SESSIONID');
4282 DLcmd := 'call CM.SETCURRINST ('''||lastCompleted||''' '''||
4283 ACId||''')';
4284
4285 select BUSINESS_AREA_ID
4286 into l_business_area_id
4287 from ZPB_ANALYSIS_CYCLES
4288 where ANALYSIS_CYCLE_ID = ACId;
4289
4290 -- change agb to dbdata.
4291 ZPB_AW_WRITE_BACK.SUBMIT_WRITEBACK_REQUEST(l_business_area_id,
4292 ownerID,
4293 respID,
4294 sessionID,
4295 'SPL',
4296 DLcmd,
4297 NULL,
4298 P_OUTVAL);
4299
4300 END IF;
4301 END IF;
4302
4303 exception
4304 when others then
4305 null;
4306
4307 end DeleteCurrInstMeas;
4308
4309
4310 -- This procedure is called by a CM program. For instance P_InstanceId of BP P_ACId
4311 -- it first deletes the AW measure associted with the instance, it then recreates and
4312 -- initializes the AW measure. Used when enabling BPs ENABLE_FIRST
4313 procedure CleanAndRestartInst (errbuf out nocopy varchar2,
4314 retcode out nocopy varchar2,
4315 P_ACId in number,
4316 P_InstanceId in number,
4317 P_BUSINESS_AREA_ID in number)
4318
4319 IS
4320
4321 ownerID number;
4322 errbufReturned varchar2(100);
4323 retCodeReturned varchar2(100);
4324 TaskId number;
4325
4326 begin
4327
4328 begin
4329
4330 select published_by into ownerID
4331 from zpb_analysis_cycles
4332 where analysis_cycle_id = P_ACId;
4333
4334
4335 -- First delete the associated AW measure
4336 WF_DELAWINST (errbuf => errbufReturned,
4337 retcode => retCodeReturned,
4338 InstanceID => P_InstanceId,
4339 UserID => ownerID,
4340 P_BUSINESS_AREA_ID => P_BUSINESS_AREA_ID);
4341
4342
4343 select TASK_ID
4344 into TaskId
4345 from zpb_analysis_cycle_tasks
4346 where ANALYSIS_CYCLE_ID = P_InstanceId and Sequence = 1;
4347
4348 -- b 5170327 intializes the pv_status variable
4349 ZPB_ERROR_HANDLER.INIT_CONC_REQ_STATUS;
4350
4351 -- Now initialize the instance, this will recreate the AW measure for it
4352 INIT_BUSINESS_PROCESS (ACID => P_ACId,
4353 InstanceID => P_InstanceId,
4354 TaskID => TaskId,
4355 UserID => ownerID);
4356
4357
4358 -- Resume the instance from the first task
4359 RESUME_INSTANCE (InstanceID => P_InstanceId,
4360 PResumeType => 'RUN_FROM_TOP');
4361
4362 -- b 5170327 - retcode is an OUT parameter conc program standard - 0=success, 1=warning or 2=error.
4363 retcode := ZPB_ERROR_HANDLER.GET_CONC_REQ_STATUS;
4364
4365 errbuf:=' ';
4366
4367 exception
4368 when NO_DATA_FOUND then
4369 retcode:=2;
4370 errbuf:='No Data Found';
4371 end;
4372
4373 end CleanAndRestartInst;
4374
4375 -- API to start task for instance for migration only.
4376
4377 procedure RUN_MIGRATE_INST (p_InstanceID in NUMBER,
4378 p_api_version IN NUMBER,
4379 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4380 p_commit IN VARCHAR2 := FND_API.G_TRUE,
4381 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4382 x_return_status OUT nocopy varchar2,
4383 x_msg_count OUT nocopy number,
4384 x_msg_data OUT nocopy varchar2)
4385
4386 IS
4387
4388 l_api_name CONSTANT VARCHAR2(30) := 'RUN_MIGRATE_INST';
4389 l_api_version CONSTANT NUMBER := 1.0;
4390
4391
4392 CurrtaskSeq number;
4393 ITEMTYPE varchar(8);
4394 ACID number;
4395 ACNAME varchar2(300);
4396 InstanceStatusCode varchar2(30);
4397 InPrevStatusCode varchar2(30);
4398 TaskID number;
4399 owner varchar2(30);
4400 ownerID number;
4401 respID number;
4402 respAppID number;
4403 charDate varchar2(30);
4404 newitemkey varchar2(240);
4405 workflowprocess varchar2(30);
4406 retcode number;
4407
4408 CURSOR c_tasks is
4409 select *
4410 from zpb_analysis_cycle_tasks
4411 where ANALYSIS_CYCLE_ID = p_InstanceID
4412 and Sequence = 1;
4413 v_tasks c_Tasks%ROWTYPE;
4414
4415 -- This needs to
4416
4417 BEGIN
4418
4419
4420 -- Standard Start of API savepoint
4421 SAVEPOINT zpb_request_explanation;
4422 -- Standard call to check for call compatibility.
4423 IF NOT FND_API.Compatible_API_Call( l_api_version,
4424 p_api_version,
4425 l_api_name,
4426 G_PKG_NAME)
4427 THEN
4428 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4429 END IF;
4430 -- Initialize message list if p_init_msg_list is set to TRUE.
4431 IF FND_API.to_Boolean(p_init_msg_list) THEN
4432 FND_MSG_PUB.initialize;
4433 END IF;
4434 -- Initialize API return status to success
4435 x_return_status := FND_API.G_RET_STS_SUCCESS;
4436
4437
4438 begin
4439
4440 select status_code, prev_status_code
4441 into InstanceStatusCode, InPrevStatusCode
4442 from zpb_analysis_cycles
4443 where analysis_cycle_id = p_InstanceID;
4444
4445 EXCEPTION
4446 WHEN NO_DATA_FOUND THEN
4447 InstanceStatusCode:='ISCNotFound';
4448 InPrevStatusCode:='ACTIVE';
4449 end;
4450
4451
4452 if InstanceStatusCode = 'ISCNotFound' then
4453 x_msg_count := 1;
4454 x_msg_data := 'No Instance ws found for this Business Process.';
4455 -- Standard call to get message count and if count is 1, get message info.
4456 FND_MSG_PUB.Count_And_Get(
4457 p_count => x_msg_count,
4458 p_data => x_msg_data );
4459
4460 -- DBMS_OUTPUT.PUT_LINE('No Instance ws found for this Business Process.');
4461 return;
4462 end if;
4463
4464 if InstanceStatusCode = 'ACTIVE' then
4465 x_msg_count := 1;
4466 x_msg_data := 'The instance of this Business Process is currently running. Instance: ' || p_InstanceID ;
4467 -- Standard call to get message count and if count is 1, get message info.
4468 FND_MSG_PUB.Count_And_Get(
4469 p_count => x_msg_count,
4470 p_data => x_msg_data );
4471
4472 -- DBMS_OUTPUT.PUT_LINE('The instance of this Business Process is currently running. Instance: ' || p_InstanceID);
4473 return;
4474 end if;
4475
4476 -- get base parmameters from last completed task and itemkey.
4477 -- AGB MIGRATION Get ACID and Analysis Cycle name
4478
4479 CurrtaskSeq := 0;
4480 ITEMTYPE := 'EPBCYCLE';
4481
4482 -- get ACID ACNAME and ownerid
4483 select ANALYSIS_CYCLE_ID into ACID
4484 from ZPB_ANALYSIS_CYCLE_INSTANCES
4485 where INSTANCE_AC_ID = p_InstanceID;
4486
4487 select NAME, CREATED_BY into ACNAME, ownerID
4488 from ZPB_ANALYSIS_CYCLES
4489 where ANALYSIS_CYCLE_ID = ACID;
4490
4491 -- Converted from zpb_analysis_cycles select above
4492 Owner := ZPB_WF_NTF.ID_to_FNDUser(OwnerID);
4493
4494 -- Using ZPB_CONTROLLER_RESP as the source for this
4495 select RESPONSIBILITY_ID
4496 into respID
4497 from fnd_responsibility_vl
4498 where APPLICATION_ID = 210 and RESPONSIBILITY_KEY = 'ZPB_CONTROLLER_RESP';
4499
4500 respAppID := 210; -- Hard coded for zpb
4501
4502 if InPrevStatusCode is null then
4503 InPrevStatusCode:='ACTIVE';
4504 end if;
4505
4506 -- if the status of the instance to be resumed is PAUSING
4507 -- all we need to do is set the status to previous status code
4508 /*
4509 if InstanceStatusCode = 'PAUSING' then
4510 update zpb_analysis_cycles
4511 set status_code= InPrevStatusCode
4512 where analysis_cycle_id = p_InstanceID;
4513 commit;
4514 return;
4515 end if;
4516 */
4517
4518 -- Get next task [wf process] to run If none COMPLETE
4519 workflowprocess := 'NONE';
4520 TaskID := NULL;
4521
4522 for v_Tasks in c_Tasks loop
4523 TaskID := v_Tasks.TASK_ID;
4524 workflowprocess := v_Tasks.wf_process_name;
4525 end loop;
4526
4527 -- LAST TASK FOR THIS INSTANCE
4528 if workflowprocess = 'NONE' then
4529
4530 update zpb_ANALYSIS_CYCLES
4531 set status_code = 'ERROR',
4532 LAST_UPDATED_BY = fnd_global.USER_ID,
4533 LAST_UPDATE_DATE = SYSDATE,
4534 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
4535 where ANALYSIS_CYCLE_ID = p_InstanceID;
4536
4537 update zpb_analysis_cycle_instances
4538 set last_update_date = sysdate,
4539 LAST_UPDATED_BY = fnd_global.USER_ID,
4540 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
4541 where instance_ac_id = p_InstanceID;
4542
4543 -- Mark for delete
4544 -- zpb_wf.markfordelete(ACID, ownerID, respID, respAppID);
4545
4546 x_msg_count := 1;
4547 x_msg_data := 'The Data Load task was missing the WF Process Name for this Business Process.';
4548 -- Standard call to get message count and if count is 1, get message info.
4549 FND_MSG_PUB.Count_And_Get(
4550 p_count => x_msg_count,
4551 p_data => x_msg_data );
4552
4553 -- DBMS_OUTPUT.PUT_LINE('The Data Load task was not found for this Instance of the Business Process.');
4554 return;
4555 end if;
4556
4557
4558 -- Set item key and date
4559 charDate := to_char(sysdate, 'MM/DD/YYYY-HH24-MI-SS');
4560 newitemkey := rtrim(substr(ACName, 1, 50), ' ') || '-' || to_char(p_InstanceID) || '-' || workflowprocess || '-' || charDate;
4561
4562
4563 -- Create WF start process instance
4564 wf_engine.CreateProcess(ItemType => ItemType,
4565 itemKey => newItemKey,
4566 process => WorkflowProcess);
4567
4568 -- This should be the EPB controller.
4569 wf_engine.SetItemOwner(ItemType => ItemType,
4570 ItemKey => NEWItemKey,
4571 owner => owner);
4572
4573 -- Set current value of Taskseq [not sure if it is always 1 might be for startup]
4574 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
4575 Itemkey => newItemKey,
4576 aname => 'TASKSEQ',
4577 avalue => CurrtaskSeq+1);
4578
4579 -- set globals for new key???
4580
4581 -- set Cycle ID!
4582 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
4583 Itemkey => newItemKey,
4584 aname => 'ACID',
4585 avalue => ACID);
4586
4587 -- set workflow with Instance Cycle ID!
4588 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
4589 Itemkey => newItemKey,
4590 aname => 'INSTANCEID',
4591 avalue => p_InstanceID);
4592
4593 -- set cycle Name!
4594 wf_engine.SetItemAttrText(Itemtype => ItemType,
4595 Itemkey => newItemKey,
4596 aname => 'ACNAME',
4597 avalue => ACNAME);
4598 -- set Task ID!
4599 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
4600 Itemkey => newItemKey,
4601 aname => 'TASKID',
4602 avalue => TaskID);
4603
4604 -- set owner name attr!
4605 wf_engine.SetItemAttrText(Itemtype => ItemType,
4606 Itemkey => newItemKey,
4607 aname => 'FNDUSERNAM',
4608 avalue => owner);
4609
4610 -- set EPBPerformer to owner name for notifications!
4611 wf_engine.SetItemAttrText(Itemtype => ItemType,
4612 Itemkey => newItemKey,
4613 aname => 'EPBPERFORMER',
4614 avalue => owner);
4615
4616
4617 -- will get error notifications
4618 wf_engine.SetItemAttrText(Itemtype => ItemType,
4619 Itemkey => newItemKey,
4620 aname => 'WF_ADMINISTRATOR',
4621 avalue => owner);
4622
4623 -- set owner ID!
4624 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
4625 Itemkey => newItemKey,
4626 aname => 'OWNERID',
4627 avalue => ownerID);
4628
4629 -- set resp ID!
4630 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
4631 Itemkey => newItemKey,
4632 aname => 'RESPID',
4633 avalue => respID);
4634
4635 -- set resp ID!
4636 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
4637 Itemkey => newItemKey,
4638 aname => 'RESPAPPID',
4639 avalue => respAppID);
4640
4641
4642
4643
4644 -- Now that all is created and set START the PROCESS!
4645 wf_engine.StartProcess(ItemType => ItemType,
4646 ItemKey => newItemKey);
4647
4648 update zpb_analysis_cycle_tasks
4649 set item_KEY = newitemkey,
4650 Start_date = to_Date(charDate,'MM/DD/YYYY-HH24-MI-SS'),
4651 status_code = 'ACTIVE',
4652 LAST_UPDATED_BY = fnd_global.USER_ID,
4653 LAST_UPDATE_DATE = SYSDATE,
4654 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
4655 where ANALYSIS_CYCLE_ID = p_InstanceID and task_id = TaskID;
4656
4657 update ZPB_ANALYSIS_CYCLES
4658 set status_code = InPrevStatusCode,
4659 LAST_UPDATED_BY = fnd_global.USER_ID,
4660 LAST_UPDATE_DATE = SYSDATE,
4661 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
4662 where ANALYSIS_CYCLE_ID = p_InstanceID;
4663
4664 update zpb_analysis_cycle_instances
4665 set last_update_date = sysdate,
4666 LAST_UPDATED_BY = fnd_global.USER_ID,
4667 LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID
4668 where instance_ac_id = p_InstanceID;
4669
4670
4671 -- DBMS_OUTPUT.PUT_LINE('The Business Process Run has been started.');
4672 -- DBMS_OUTPUT.PUT_LINE('The Workflow ITEM_KEY is: ' || newitemkey);
4673
4674
4675 -- Standard check of p_commit.
4676 IF FND_API.To_Boolean( p_commit ) THEN
4677 COMMIT WORK;
4678 END IF;
4679 -- Standard call to get message count and if count is 1, get message info.
4680 FND_MSG_PUB.Count_And_Get(
4681 p_count => x_msg_count,
4682 p_data => x_msg_data
4683 );
4684
4685
4686 return;
4687
4688 exception
4689 WHEN FND_API.G_EXC_ERROR THEN
4690 ROLLBACK TO zpb_request_explanation;
4691 x_return_status := FND_API.G_RET_STS_ERROR;
4692 FND_MSG_PUB.Count_And_Get(
4693 p_count => x_msg_count,
4694 p_data => x_msg_data
4695 );
4696 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4697 ROLLBACK TO zpb_request_explanation;
4698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4699 FND_MSG_PUB.Count_And_Get(
4700 p_count => x_msg_count,
4701 p_data => x_msg_data
4702 );
4703 WHEN OTHERS THEN
4704 ROLLBACK TO zpb_request_explanation;
4705 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4706 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4707 FND_MSG_PUB.Add_Exc_Msg(
4708 G_PKG_NAME,
4709 l_api_name
4710 );
4711 END IF;
4712 FND_MSG_PUB.Count_And_Get(
4713 p_count => x_msg_count,
4714 p_data => x_msg_data
4715 );
4716
4717
4718 end RUN_MIGRATE_INST;
4719
4720 /*+=========================================================================+
4721 | REVIEW_NOTIF_RESPONCE
4722 |
4723 | This procedure does nothing functionally.
4724 | It is added just to get rid of the Validation warning in zpbcycle.wft
4725 | REVIEWNTF notification needs to have a PL/SQL function associated with it
4726 | for it to have 'Expand Roles' option checked.
4727 |
4728 | IN
4729 | itemtype - A valid item type from WF_ITEM_TYPES table.
4730 | itemkey - string generated as WF primary key.
4731 | actid - An Activity ID.
4732 | funcmode - The mode in which this procedure is called
4733 |
4734 | OUT
4735 | resultout - A result that can be returned.
4736 +========================================================================+
4737 */
4738 procedure REVIEW_NOTIF_RESPONSE(itemtype in varchar2,
4739 itemkey in varchar2,
4740 actid in number,
4741 funcmode in varchar2,
4742 resultout out NOCOPY varchar2)
4743 IS
4744 result varchar2(24);
4745
4746 BEGIN
4747 if (funcmode = 'RUN') then
4748 result := wf_engine.GetItemAttrText(Itemtype => ItemType,
4749 Itemkey => ItemKey,
4750 aname => 'RESULT');
4751
4752 resultout := result;
4753 end if;
4754 if (funcmode = 'TIMEOUT') then
4755 resultout := wf_engine.eng_timedout;
4756 end if;
4757 exception
4758 when others then
4759 WF_CORE.CONTEXT('ZPB_WF.REVIEW_NOTIF_RESPONSE', itemtype, itemkey, to_char(actid), funcmode);
4760 raise;
4761 end REVIEW_NOTIF_RESPONSE;
4762
4763
4764 -- A. Budnik 04/26/2006 b 3126256 SUBMIT_CONC_REQUEST
4765 -- Implemented to submit concurrent programs from Workflow because WF EXECUTECONCPROG
4766 -- does not commit for the concurrent request until the workflow process ends.
4767
4768 procedure SUBMIT_CONC_REQUEST (itemtype in varchar2,
4769 itemkey in varchar2,
4770 actid in number,
4771 funcmode in varchar2,
4772 resultout out nocopy varchar2)
4773 IS
4774
4775 l_ACID number;
4776 l_TaskID number;
4777 l_UserID number;
4778 l_respID number;
4779 l_InstanceID number;
4780
4781 reqID number;
4782 l_business_area_id number; -- abudnik 17NOV2005 BUSINESS AREA ID
4783 l_wfprocess varchar2(240);
4784 l_ActEntry varchar2(30);
4785
4786 BEGIN
4787
4788 IF (funcmode = 'RUN') THEN
4789 resultout :='COMPLETE';
4790
4791
4792 l_ACID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
4793 Itemkey => ItemKey,
4794 aname => 'ACID');
4795 l_userID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
4796 Itemkey => ItemKey,
4797 aname => 'OWNERID');
4798 l_respID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
4799 Itemkey => ItemKey,
4800 aname => 'RESPID');
4801 l_InstanceID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
4802 Itemkey => ItemKey,
4803 aname => 'INSTANCEID');
4804 l_business_area_id := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
4805 Itemkey => ItemKey,
4806 aname => 'BUSINESSAREAID');
4807 l_TaskID := wf_engine.GetItemAttrNumber(Itemtype => ItemType,
4808 Itemkey => ItemKey,
4809 aname => 'TASKID');
4810
4811
4812 select root_activity
4813 into l_wfprocess
4814 from wf_items_v
4815 where item_type = itemtype
4816 and item_KEY = itemkey;
4817
4818
4819 CASE l_wfprocess
4820
4821 WHEN 'SET_VIEW_RESTRICTION' THEN
4822
4823 -- reqID := FND_REQUEST.SUBMIT_REQUEST ('ZPB', 'ZPB_DVAC_TASK', NULL, NULL, FALSE, l_ACID, l_InstanceID, l_business_area_id);
4824
4825 -- to add the task ID just uncomment this and comment the above call.
4826 reqID := FND_REQUEST.SUBMIT_REQUEST ('ZPB', 'ZPB_DVAC_TASK', NULL, NULL, FALSE, l_ACID, l_InstanceID, l_business_area_id, l_TaskID );
4827
4828 WHEN 'EXCEPTION' THEN
4829 reqID := FND_REQUEST.SUBMIT_REQUEST ('ZPB', 'ZPB_RUN_EXCEPTION', NULL, NULL, FALSE, l_TaskID, l_userID);
4830
4831 ELSE
4832
4833
4834 if substr(itemtype, 1, 8) = 'ZPBSCHED' THEN
4835
4836 SELECT ACTIVITY_NAME INTO l_ActEntry
4837 FROM WF_PROCESS_ACTIVITIES
4838 WHERE INSTANCE_ID=actid;
4839
4840 If l_ActEntry = 'SUBMIT_CONC_REQUEST' then
4841 reqID := FND_REQUEST.SUBMIT_REQUEST ('ZPB', 'ZPB_WF_INSTANCE', NULL, NULL, FALSE, itemkey, l_ACID, l_business_area_id);
4842 -- l_ActEntry = 'EXECUTECONCPROG' then
4843
4844 elsif l_ActEntry = 'VALIDATE_BP' then
4845 -- procedure can be used by a new function activity for validate bp
4846 reqID := FND_REQUEST.SUBMIT_REQUEST ('ZPB', 'ZPB_BP_VALIDATION_CP', NULL, NULL, FALSE, l_ACID, l_userID, l_respID, l_business_area_id);
4847
4848 else
4849 reqID := -1;
4850
4851 end if;
4852
4853 end if;
4854
4855 END CASE;
4856
4857
4858 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
4859 Itemkey => ItemKey,
4860 aname => 'REQUEST_ID',
4861 avalue => reqID);
4862
4863
4864 resultout :='COMPLETE';
4865
4866 END IF;
4867
4868 return;
4869
4870 exception
4871 when others then
4872 Wf_Core.Context('ZPB_WF', 'SUBMIT_CONC_REQUEST', itemtype,
4873 itemkey, to_char(actid), funcmode);
4874 raise;
4875
4876
4877
4878 END SUBMIT_CONC_REQUEST;
4879
4880
4881 end ZPB_WF;
4882