[Home] [Help]
PACKAGE BODY: APPS.MSDWF
Source
1 PACKAGE BODY MSDWF AS
2 /* $Header: msddpwfb.pls 115.17 2002/04/09 08:05:13 pkm ship $ */
3
4 express_server varchar2(240);
5 DBName varchar2(80);
6 CodeLoc varchar2(80);
7 SharedLoc varchar2(80);
8 PlName varchar2(30);
9 planID varchar2(15);
10 Owner varchar2(30);
11 DPAdmin varchar2(30);
12 FixedDate varchar2(30);
13 g_owner varchar2(50) := ' ';
14 Master varchar2(1); --agb 02/15/02 is ODP Master controling cycle
15
16 -- connects to OES and run programs in OES and gets values back.
17 PROCEDURE DOEXPRESS (itemtype in varchar2,
18 itemkey in varchar2,
19 actid in number,
20 funcmode in varchar2,
21 resultout out varchar2)
22 IS
23 ActEntry varchar2(16);
24 ActRetCode varchar2(2000);
25 ActRetText varchar2(2000);
26 ActRetVal varchar2(2000);
27 ActRetErr varchar2(2000);
28 thisrole varchar2(30);
29 TempDate date;
30 TxtDate varchar2(8); -- agb 06/14/00 to deal with ambiguous date
31 Process varchar2(30); -- agb 03/19/02 B2173260 added to wf.setactivity
32
33 BEGIN
34 IF (funcmode = 'RUN') THEN
35 resultout :='COMPLETE:N';
36 SELECT ACTIVITY_NAME, PROCESS_NAME INTO ActEntry, Process FROM WF_PROCESS_ACTIVITIES
37 WHERE INSTANCE_ID=actid;
38 SELECT TEXT_VALUE INTO thisrole FROM WF_ITEM_ATTRIBUTE_VALUES
39 WHERE ITEM_KEY=itemkey AND ITEM_TYPE=itemtype AND NAME='ODPROLE';
40 express_server:=wf_engine.GetItemAttrText(Itemtype => ItemType,
41 Itemkey => ItemKey,
42 aname => 'EXPCONN');
43
44 -- new to indicate if ODP Master is governing Cycle
45 Master:=wf_engine.GetItemAttrText(Itemtype => ItemType,
46 Itemkey => ItemKey,
47 aname => 'ISMASTER');
48
49 DBName:=wf_engine.GetItemAttrText(Itemtype => ItemType,
50 Itemkey => ItemKey,
51 aname => 'DBNAME');
52 DPAdmin:=wf_engine.GetItemAttrText(Itemtype => ItemType,
53 Itemkey => ItemKey,
54 aname => 'DPADMIN');
55 SharedLoc:=wf_engine.GetItemAttrText(Itemtype => ItemType,
56 Itemkey => ItemKey,
57 aname => 'SHAREDLOC');
58 CodeLoc:=wf_engine.GetItemAttrText(Itemtype => ItemType,
59 Itemkey => ItemKey,
60 aname => 'CODELOC');
61 PlanID:=wf_engine.GetItemAttrText(Itemtype => ItemType,
62 Itemkey => ItemKey,
63 aname => 'ODPPLAN');
64 SELECT C0, C1, C2, C3
65 into ActRetCode, ActRetText, ActRetVal, ActRetErr
66 from THE (SELECT CAST (EPS.query(express_server,
67 'DB0='|| CodeLoc || '/ODPCODE\'
68 || 'DBCount=1\'
69 || 'MeasureCount=4\'
70 || 'Measure0=ACTIVITY.FORMULA\'
71 || 'Measure1=ACTIVITY.TEXT\'
72 || 'Measure2=ACTIVITY.RETVAL\'
73 || 'Measure3=ACTIVITY.ERROR\'
74 || 'E0Count=2\'
75 || 'E0Dim0Name=PLACEHOLDER\'
76 || 'E0Dim1Name=ACTIVITY.ENTRY\'
77 || 'E0Dim1Script=CALL WF.SETACTIVITY('''|| ActEntry || ''', '''|| PlanID ||''', '''|| DBName ||''', '''|| SharedLoc ||''', '''|| DPAdmin ||''', '''|| thisrole ||''', '''|| ItemKey ||''', '''|| Master ||''', '''|| Process ||''')\'
78 ,NULL
79 ,NULL
80 ,NULL
81 ,NULL
82 ,NULL
83 ,NULL
84 ,NULL
85 ,NULL
86 ,NULL)
87 AS EPS_express_list_t)
88 from DUAL);
89 if ActRetCode = 'Y' then
90 resultout :='COMPLETE:Y';
91 end if;
92
93 if ActRetCode = 'MSG' then
94 -- corrected this name
95 if ActEntry = 'ODPSUBSTAT'
96 then
97 TxtDate := to_char(sysdate, 'YY MM DD');
98 TempDate := to_date(TxtDate, 'YY MM DD');
99 -- obsolete agb 6/14/00 TempDate := sysdate;
100 wf_engine.SetItemAttrDate(Itemtype => ItemType,
101 Itemkey => ItemKey,
102 aname => 'VALUE1',
103 avalue => TempDate);
104 end if;
105 resultout :='COMPLETE:MSG';
106 end if;
107
108 if ActRetCode = 'CYCLE' then
109 -- corrected this name
110 if ActEntry = 'ODPSUBSTAT'
111 then
112 TxtDate := to_char(sysdate, 'YY MM DD');
113 TempDate := to_date(TxtDate, 'YY MM DD');
114 -- obsolete agb 6/14/00 TempDate := sysdate;
115 wf_engine.SetItemAttrDate(Itemtype => ItemType,
116 Itemkey => ItemKey,
117 aname => 'VALUE1',
118 avalue => TempDate);
119 end if;
120 resultout :='COMPLETE:CYCLE';
121 end if;
122
123 if ActRetCode = 'BUSY' then
124 resultout :='COMPLETE:BUSY';
125 end if;
126 if ActRetCode = 'DONE' then
127 resultout :='COMPLETE:DONE';
128 end if;
129 if ActRetCode = 'END' then
130 resultout :='COMPLETE:END';
131 end if;
132 if ActRetCode = 'Y' then
133 if ActEntry = 'ODPDIST'
134 then
135 wf_engine.SetItemAttrText(Itemtype => ItemType,
136 Itemkey => ItemKey,
137 aname => 'ASSIGNID',
138 avalue => ActRetVal);
139 wf_engine.SetItemAttrText(Itemtype => ItemType,
140 Itemkey => ItemKey,
141 aname => 'ASSIGNNAME',
142 avalue => ActRetText);
143 end if;
144
145 end if;
146 if ActRetCode = 'N' then
147 wf_engine.SetItemAttrText(Itemtype => ItemType,
148 Itemkey => ItemKey,
149 aname => 'DPPROBLEM',
150 avalue => ActRetErr);
151 end if;
152 return;
153 END IF;
154 exception
155 when others then
156 WF_CORE.CONTEXT('MSDWF', 'DOEXPRESS',
157 itemtype, itemkey, to_char(actid), funcmode);
158 raise;
159 end DOEXPRESS;
160 -- Starts a Workflow process.
161 PROCEDURE STARTPRO (WorkflowProcess in varchar2,
162 iteminput in varchar2,
163 inputkey in varchar2,
164 inowner in varchar2,
165 inrole in varchar2,
166 inplan in varchar2,
167 inCDate in varchar2)
168 IS
169 itemtype varchar2(30) := iteminput;
170 itemkey varchar2(200) := inputkey;
171 owner varchar2(30) := inowner;
172 CompDate date;
173 BEGIN
174 SELECT demand_plan_name, code_location, shared_db_prefix,
175 shared_db_location, express_connect_string INTO PlName,
176 CodeLoc, DBName, SharedLoc, express_server
177 from msd_demand_plans_v
178 where demand_plan_id=to_number(inPlan);
179 wf_engine.CreateProcess(ItemType => ItemType,
180 itemKey => ItemKey,
181 process => WorkflowProcess);
182 -- The sysinfo(user that launched the process or the Owner of the process.
183 -- This would be the demand planning administrator.
184 wf_engine.SetItemOwner(ItemType => ItemType,
185 ItemKey => ItemKey,
186 owner => owner);
187 wf_engine.SetItemAttrText(Itemtype => ItemType,
188 Itemkey => ItemKey,
189 aname => 'DPADMIN',
190 avalue => owner);
191 wf_engine.SetItemAttrText(Itemtype => ItemType,
192 Itemkey => ItemKey,
193 aname => 'ODPROLE',
194 avalue => inrole);
195 -- Plan ID!
196 wf_engine.SetItemAttrText(Itemtype => ItemType,
197 Itemkey => ItemKey,
198 aname => 'ODPPLAN',
199 avalue => inplan);
200 wf_engine.SetItemAttrText(Itemtype => ItemType,
201 Itemkey => ItemKey,
202 aname => 'PLNAME',
203 avalue => PlName);
204 wf_engine.SetItemAttrText(Itemtype => ItemType,
205 Itemkey => ItemKey,
206 aname => 'CODELOC',
207 avalue => CodeLoc);
208 wf_engine.SetItemAttrText(Itemtype => ItemType,
209 Itemkey => ItemKey,
210 aname => 'DBNAME',
211 avalue => DBName);
212 wf_engine.SetItemAttrText(Itemtype => ItemType,
213 Itemkey => ItemKey,
214 aname => 'SHAREDLOC',
215 avalue => SharedLoc);
216 wf_engine.SetItemAttrText(Itemtype => ItemType,
217 Itemkey => ItemKey,
218 aname => 'EXPCONN',
219 avalue => express_server);
220 if WorkflowProcess = 'ODPSTDCOL'
221 then
222 CompDate:= to_date(inCDate, 'YY MM DD');
223 wf_engine.SetItemAttrDate(Itemtype => ItemType,
224 Itemkey => ItemKey,
225 aname => 'VALUE2',
226 avalue => CompDate);
227 end if;
228
229 -- if fixed date for Distrbution or Standard Collection then set Wait to null.
230 if WorkflowProcess = 'ODPDISTPLANS'
231 then
232 select count(value) into FixedDate from v$parameter
233 where name like '%fixed_date%' AND length(VALUE) > 0;
234 if FixedDate > 0
235 then
236 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
237 Itemkey => ItemKey,
238 aname => 'WAIT_RELATIVE_TIME',
239 avalue => NULL);
240 end if;
241 end if;
242
243 wf_engine.StartProcess(ItemType => ItemType,
244 ItemKey => ItemKey);
245 return;
246 exception
247 when others then
248 WF_CORE.CONTEXT('MSDWF', 'STARTPRO',
249 itemtype, itemkey);
250 raise;
251 end STARTPRO;
252 PROCEDURE LAUNCH (itemtype in varchar2,
253 itemkey in varchar2,
254 actid in number,
255 funcmode in varchar2,
256 resultout out varchar2)
257 IS
258 ActEntry varchar2(16);
259 ActRetCode varchar2(2000);
260 ActRetText varchar2(2000);
261 ActRetErr varchar2(2000);
262 thisrole varchar2(30);
263 LaunchMgr varchar2(12);
264 AttachDBName varchar2(240);
265 BEGIN
266 IF (funcmode = 'RUN') THEN
267 resultout :='COMPLETE:N';
268 SELECT ACTIVITY_NAME INTO ActEntry FROM WF_PROCESS_ACTIVITIES
269 WHERE INSTANCE_ID=actid;
270 SELECT TEXT_VALUE INTO thisrole FROM WF_ITEM_ATTRIBUTE_VALUES
271 WHERE ITEM_KEY=itemkey AND ITEM_TYPE=itemtype AND NAME='ODPROLE';
272 -- Inital default is "CONTINUE". Values are CYCLE, DONE, LAUNCH
273 -- LAUNCH means Make the call to launch a sub process.
274 -- DONE means all DB Assignments are complete.
275 -- CYCLE means keep processing.
276 -- This is set upon the start of the process.
277 -- This is set by MSDWF.GOVERNOR.
278 LaunchMgr:=wf_engine.GetItemAttrText(Itemtype => ItemType,
279 Itemkey => ItemKey,
280 aname => 'LAUNCHMGR');
281 PlanID:=wf_engine.GetItemAttrText(Itemtype => ItemType,
282 Itemkey => ItemKey,
283 aname => 'ODPPLAN');
284 if LaunchMgr = 'CYCLE' or LaunchMgr = 'DONE'
285 then
286 resultout :='COMPLETE:Y';
287 return;
288 end if;
289 -- Call to Launch a process.
290 if LaunchMgr = 'LAUNCH'
291 then
292 express_server:=wf_engine.GetItemAttrText(Itemtype => ItemType,
293 Itemkey => ItemKey,
294 aname => 'EXPCONN');
295 DPAdmin:=wf_engine.GetItemAttrText(Itemtype => ItemType,
296 Itemkey => ItemKey,
297 aname => 'DPADMIN');
298 DBName:=wf_engine.GetItemAttrText(Itemtype => ItemType,
299 Itemkey => ItemKey,
300 aname => 'DBNAME');
301 SharedLoc:=wf_engine.GetItemAttrText(Itemtype => ItemType,
302 Itemkey => ItemKey,
303 aname => 'SHAREDLOC');
304 CodeLoc:=wf_engine.GetItemAttrText(Itemtype => ItemType,
305 Itemkey => ItemKey,
306 aname => 'CODELOC');
307 SELECT C0, C1, C2
308 into ActRetCode, ActRetText, ActRetErr
309 from THE (SELECT CAST (EPS.query(express_server,
310 'DB0='|| CodeLoc || '/ODPCODE\'
311 || 'DBCount=1\'
312 || 'MeasureCount=3\'
313 || 'Measure0=ACTIVITY.FORMULA\'
314 || 'Measure1=ACTIVITY.TEXT\'
315 || 'Measure2=ACTIVITY.ERROR\'
316 || 'E0Count=2\'
317 || 'E0Dim0Name=PLACEHOLDER\'
318 || 'E0Dim1Name=ACTIVITY.ENTRY\'
319 || 'E0Dim1Script=CALL WF.SETACTIVITY('''|| ActEntry || ''', '''|| PlanID ||''', '''|| DBName ||''', '''|| SharedLoc ||''', '''|| DPAdmin ||''', '''|| thisrole ||''', '''|| ItemKey ||''', '''|| Master ||''')\'
320 ,NULL
321 ,NULL
322 ,NULL
323 ,NULL
324 ,NULL
325 ,NULL
326 ,NULL
327 ,NULL
328 ,NULL)
329 AS EPS_express_list_t)
330 from DUAL);
331 if ActRetCode = 'Y' then
332 resultout :='COMPLETE:Y';
333 end if;
334 -- if ActRetCode = 'Y' then
335 -- wf_engine.SetItemAttrText(Itemtype => ItemType,
336 -- Itemkey => ItemKey,
337 -- aname => 'ODPBODY',
338 -- avalue => ActRetText);
339 -- end if;
340 if ActRetCode = 'N' then
341 wf_engine.SetItemAttrText(Itemtype => ItemType,
342 Itemkey => ItemKey,
343 aname => 'DPPROBLEM',
344 avalue => ActRetErr);
345 end if;
346 return;
347 end if;
348 END IF;
349 exception
350 when others then
351 WF_CORE.CONTEXT('MSDWF', 'LAUNCH',
352 itemtype, itemkey, to_char(actid), funcmode, ActRetErr);
353 raise;
354 end LAUNCH;
355 PROCEDURE GOVERNOR (itemtype in varchar2,
356 itemkey in varchar2,
357 actid in number,
358 funcmode in varchar2,
359 resultout out varchar2)
360 IS
361 ActEntry varchar2(16);
362 ActRetCode varchar2(2000);
363 ActRetText varchar2(2000);
364 ActRetVal varchar2(2000);
365 ActRetErr varchar2(2000);
366 thisrole varchar2(30);
367 owner varchar2(30);
368 PlanID varchar2(200);
369 BEGIN
370 IF (funcmode = 'RUN') THEN
371 resultout :='COMPLETE:N';
372 SELECT ACTIVITY_NAME INTO ActEntry FROM WF_PROCESS_ACTIVITIES
373 WHERE INSTANCE_ID=actid;
374 SELECT TEXT_VALUE INTO thisrole FROM WF_ITEM_ATTRIBUTE_VALUES
375 WHERE ITEM_KEY=itemkey AND ITEM_TYPE=itemtype AND NAME='ODPROLE';
376 express_server := wf_engine.GetItemAttrText(Itemtype => ItemType,
377 Itemkey => ItemKey,
378 aname => 'EXPCONN');
379 DPAdmin:=wf_engine.GetItemAttrText(Itemtype => ItemType,
380 Itemkey => ItemKey,
381 aname => 'DPADMIN');
382 DBName := wf_engine.GetItemAttrText(Itemtype => ItemType,
383 Itemkey => ItemKey,
384 aname => 'DBNAME');
385 SharedLoc := wf_engine.GetItemAttrText(Itemtype => ItemType,
386 Itemkey => ItemKey,
387 aname => 'SHAREDLOC');
388 CodeLoc := wf_engine.GetItemAttrText(Itemtype => ItemType,
389 Itemkey => ItemKey,
390 aname => 'CODELOC');
391 SELECT C0, C1, C2, C3
392 into ActRetCode, ActRetText, ActRetVal, ActRetErr
393 from THE (SELECT CAST (EPS.query(express_server,
394 'DB0='|| CodeLoc || '/ODPCODE\'
395 || 'DBCount=1\'
396 || 'MeasureCount=4\'
397 || 'Measure0=ACTIVITY.FORMULA\'
398 || 'Measure1=ACTIVITY.TEXT\'
399 || 'Measure2=ACTIVITY.RETVAL\'
400 || 'Measure3=ACTIVITY.ERROR\'
401 || 'E0Count=2\'
402 || 'E0Dim0Name=PLACEHOLDER\'
403 || 'E0Dim1Name=ACTIVITY.ENTRY\'
404 || 'E0Dim1Script=CALL WF.SETACTIVITY('''|| ActEntry || ''', '''|| PlanID ||''', '''|| DBName ||''', '''|| SharedLoc ||''', '''|| owner ||''', '''|| thisrole ||''', '''|| ItemKey ||''', '''|| Master ||''')\'
405 ,NULL
406 ,NULL
407 ,NULL
408 ,NULL
409 ,NULL
410 ,NULL
411 ,NULL
412 ,NULL
413 ,NULL)
414 AS EPS_express_list_t)
418 if ActRetCode = 'DONE' then
415 from DUAL);
416 -- This is the end. We are out.
417 -- I need Y/N/DONE as possible return values.
419 resultout :='COMPLETE:DONE';
420 return;
421 end if;
422 -- its time to launch another or contiue
423 -- ActRetVal should be either LAUNCH or CYCLE.
424 if ActRetCode = 'Y' then
425 wf_engine.setItemAttrText(Itemtype => ItemType,
426 Itemkey => ItemKey,
427 aname => 'LAUNCHMGR',
428 avalue => ActRetVal);
429 resultout :='COMPLETE:CYCLE';
430 end if;
431 if ActRetCode = 'N' then
432 wf_engine.SetItemAttrText(Itemtype => ItemType,
433 Itemkey => ItemKey,
434 aname => 'DPPROBLEM',
435 avalue => ActRetErr);
436 end if;
437 return;
438 END IF;
439 exception
440 when others then
441 WF_CORE.CONTEXT('MSDWF', 'GOVERNOR',
442 itemtype, itemkey, to_char(actid), funcmode, ActRetErr);
443 raise;
444 end GOVERNOR;
445
446 --========================================================================
447 --
448 --
449 -- StartConcProc
453 -- the WF Background Program in the loop.
450 --
451 -- Start a small WF Process which uses only one activity named
452 -- the Standard Submit Concurrent Program Activity that in turn will run
454 --
455 --
456 -- IN
457 -- WFProcess - A name of the process with Standard Submit Concurrent Program Activity.
458 -- itemtype - A valid item type from WF_ITEM_TYPES table.
459 -- itemkey - A string generated from application object's primary key.
460 -- owner - A owner of the process.
461 -- cost_itemKey - A ItemKey of the process whose Activity was deferred.
462 --
463
464 procedure StartConcProc (WFProcess in varchar2,
465 itemtype in varchar2,
466 itemkey in varchar2,
467 owner in varchar2,
471
468 inplan in varchar2,
469 cost_itemKey in varchar2)
470 IS
472 BEGIN
473
474 g_owner := owner;
475
476 wf_engine.CreateProcess(ItemType => ItemType,
477 itemKey => ItemKey,
478 process => WFProcess);
479
480 wf_engine.SetItemOwner(ItemType => ItemType,
481 ItemKey => ItemKey,
482 owner => owner);
483
484 -- Plan ID!
485 wf_engine.SetItemAttrText(Itemtype => ItemType,
486 Itemkey => ItemKey,
487 aname => 'ODPPLAN',
488 avalue => inplan);
489
490 wf_engine.SetItemAttrText(Itemtype => ItemType,
491 Itemkey => itemKey ,
492 aname => 'ARG2',
493 avalue => cost_itemKey );
494
495 wf_engine.SetItemAttrText(Itemtype => ItemType,
496 Itemkey => itemKey ,
497 aname => 'ARG1',
498 avalue => ItemType);
499
500 wf_engine.StartProcess(ItemType => ItemType,
501 ItemKey => ItemKey);
502
503
504 return;
505
506 exception
507 when others then
508 WF_CORE.CONTEXT('MSDWF', 'StartConcProc',
509 itemtype, itemkey);
510 raise;
511
512 end StartConcProc;
513
514 --========================================================================
515
516 -- ConcLoop
517 --
518 -- Run Background Engine Program in the loop for each Deferred Activity
519 -- until the Process with Deferred Activities is not completed.
520 --
521 -- AGBLoop has to be used from SubmitCost Concurrent program
522 -- to complete deferred process.
523 --
524 -- IN
525 -- itemtype - A valid item type from WF_ITEM_TYPES table.
526 -- itemkey - A string generated from application object's primary key.
527 --
528 -- OUT
529 -- errbuf - error message : process or PL/SQL error.
530 -- retcode - return code (0 = success, 2 = error).
531 --
532
533 procedure ConcLoop(errbuf out varchar2,
534 retcode out number,
535 itemtype in varchar2,
536 itemkey in varchar2)
537 IS
538
539 status_code varchar2(50);
540 seconds number;
541 result varchar2(200);
542 deferred_found varchar2(3);
543
544 BEGIN
545 seconds := 20;
546
547 errbuf := ' ';
548 retcode := 0;
549
550 status_code := 'NONE';
551 deferred_found := 'NO';
552
553 dbms_lock.sleep(seconds);
554
555 -- run Background Engine Program in the loop for each Deferred
556 -- Activity until the Process with Deferred Activities is not completed.
557
558 Loop
559
560 wf_engine.ItemStatus(itemType, itemkey, status_code, result);
561
562 if RTRIM(status_code) = 'COMPLETE' then
563 exit;
564 end if;
565
566 select distinct ACTIVITY_STATUS_CODE into status_code
567 from wf_item_activity_statuses_v
568 where item_type = itemtype
569 and item_key = itemkey
570 and ACTIVITY_STATUS_CODE = 'DEFERRED';
571
572 if status_code = 'DEFERRED' then
573 deferred_found := 'YES';
574 wf_engine.Background(itemtype);
575 dbms_lock.sleep(seconds);
576 end if;
577 end loop;
578
579 return;
580
581 exception
582
583 when NO_DATA_FOUND then
584 if deferred_found = 'YES' then
585 return;
586 else
587 WF_CORE.CONTEXT('MSDWF', 'ConcLoop', itemtype, itemkey, ' NO_DATA_FOUND ');
588 retcode := 2;
589 errbuf:=substr(sqlerrm, 1, 255);
590 raise;
591 end if;
592
593 when others then
594 WF_CORE.CONTEXT('MSDWF', 'ConcLoop', itemtype, itemkey);
595 retcode := 2;
596 errbuf:=substr(sqlerrm, 1, 255);
597
598 raise;
599
600 end ConcLoop;
601
602 --========================================================================
603 --
604 -- RunConcLoop
605 --
606 -- Concurrent program that is used to run WF Background Engine in the looop
607 -- to complete each deferred activity for particular WF Process(ItemType/ItemKey).
608 --
609 -- This program has to be run from Standard Submit Concurrent Program Activity.
610 --
611 -- IN
612 -- itemtype - A valid item type from WF_ITEM_TYPES table.
613 -- cost_itemkey - Item key of the process whose activities were deferred.
614 --
615 -- OUT
616 -- errbuf - error message : process or PL/SQL error.
617 -- retcode - return code (0 = success, 2 = error).
618 --
619
620 procedure RunConcLoop(errbuf out varchar2,
621 retcode out number,
622 ItemType in varchar2,
623 cost_ItemKey in varchar2)
624
625 IS
626 retText varchar2(200);
627 planName varchar2(100);
628 dispMesg varchar2(200);
629
630 BEGIN
631
632 errbuf := ' ';
633
634 MSDWF.ConcLoop(errbuf, retcode, itemtype, cost_ItemKey);
635
636 return;
637
638 exception
639 when others then
640 retcode :='2';
641 errbuf:=substr(sqlerrm, 1, 255);
642
643 raise;
644
645 end RunConcLoop;
646
650 --
647 --========================================================================
648 --
649 -- Selector
651 -- This is an Item Type Selector function that contains callback functionality
652 -- to reestablish context for each process in our Item Type.
653 -- This is a requrements of the Concurrent Manager Standard Activity
654 -- to be sure that the context is set up by calling
655 -- fnd_global.apps_initialize(user_id, resp_id, resp_appl_id)
656 --
657 -- IN
658 -- Set of arguments for Standard Activity Function:
659 -- itemtype - A valid item type from WF_ITEM_TYPES table.
660 -- itemkey - A string generated from application object's primary key.
661 -- actid - An Activity ID.
662 -- command - Oracle Workflow calls selector/callback function with
663 -- following commands: 'RUN', 'TEST_CTX'.
664 --
665 -- OUT
666 -- resultout - A result that can be returned.
667 --
668
669 procedure Selector(itemtype in varchar2,
670 itemkey in varchar2,
671 actid in number,
672 command in varchar2,
673 resultout out varchar2)
674 IS
675
676 temp varchar2(100);
677 msd_user_id number;
678 resp_id number;
679 resp_appl_id number;
680 user_name varchar2(50);
681 errbuf varchar2(240);
682 number_resps number;
683 resp_key varchar2(20);
684
685 request_responsibility_group exception;
686
687 BEGIN
688
689 IF (command = 'TEST_CTX') THEN
690
691 if g_owner <> ' ' then
692
693 select user_id into msd_user_id
694 from fnd_user
695 where user_name = g_owner;
696
697 /* select r.application_id, r.responsibility_id into resp_appl_id, resp_id
698 from fnd_application a, fnd_responsibility r
699 where r.application_id = a.application_id
700 and a.application_short_name = 'MSD'
701 and r.responsibility_key = 'MSD_SYSADMIN'; */
702
703 select count(*) into number_resps
704 from fnd_responsibility r, fnd_user_resp_groups urg, fnd_application a
705 where r.application_id = a.application_id
706 and r.application_id = urg.responsibility_application_id
707 and r.responsibility_id = urg.responsibility_id
708 and urg.user_id = msd_user_id
709 and a.application_short_name = 'MSD'
710 and (r.responsibility_key = 'MSD_SYSADMIN' or
711 r.responsibility_key = 'MSD_INTEGADMIN' or
712 r.responsibility_key = 'MSD_ADMIN')
713 and r.request_group_id <> 0;
714
715 if number_resps = 0 then
716
717 raise request_responsibility_group;
718
719 elsif number_resps = 1 then
720
721 select r.application_id, r.responsibility_id into resp_appl_id, resp_id
722 from fnd_responsibility r, fnd_user_resp_groups urg, fnd_application a
723 where r.application_id = a.application_id
724 and r.application_id = urg.responsibility_application_id
725 and r.responsibility_id = urg.responsibility_id
726 and urg.user_id = msd_user_id
727 and a.application_short_name = 'MSD'
728 and (r.responsibility_key = 'MSD_SYSADMIN' or
729 r.responsibility_key = 'MSD_INTEGADMIN' or
730 r.responsibility_key = 'MSD_ADMIN')
731 and r.request_group_id <> 0;
732
733 else
734 resp_key := 'MSD_SYSADMIN';
735 select r.application_id, r.responsibility_id into resp_appl_id, resp_id
736 from fnd_responsibility r, fnd_user_resp_groups urg, fnd_application a
737 where r.application_id = a.application_id
738 and r.application_id = urg.responsibility_application_id
739 and r.responsibility_id = urg.responsibility_id
740 and urg.user_id = msd_user_id
741 and a.application_short_name = 'MSD'
742 and r.responsibility_key = resp_key
743 and r.request_group_id <> 0;
744
745 end if;
746
747 fnd_global.apps_initialize(msd_user_id, resp_id, resp_appl_id);
748
749 end if;
750
751 end if;
752
753 exception
754
755 when NO_DATA_FOUND then
756
757 errbuf:=substr(sqlerrm, 1, 255);
758
759 raise;
760
761 when request_responsibility_group then
762 --fnd_message.set_name ('FND', 'CONC-Illegal printer spec');
763 resultout := '2';
764 return ;
765
766 when others then
767
768 errbuf:=substr(sqlerrm, 1, 255);
769
770 raise;
771
772
773 end Selector;
774 --========================================================================
775 --========================================================================
776 -- SetColDate
777 --
778 -- WF proc to set the number of days to run the Standard Collection.
779 --
780 -- IN Standard parameters supplied by WF engine:
781 -- itemtype , itemkey, actid, funcmode
782 --
783 -- OUT
784 -- resultout 'COMPLETE:N' for failure, 'COMPLETE:Y' for success
785 --
786 procedure SetColDate (itemtype in varchar2,
787 itemkey in varchar2,
788 actid in number,
789 funcmode in varchar2,
790 resultout out varchar2)
791 IS
792 TempDate date;
793 TxtDate varchar2(8);
794 NumDays number;
795
796 BEGIN
797
798 IF (funcmode = 'RUN') THEN
799 resultout :='COMPLETE:N';
800
801
802 NumDays:=wf_engine.GetItemAttrNumber(Itemtype => ItemType,
803 Itemkey => ItemKey,
804 aname => 'NUMDAYS');
805
806 TempDate := sysdate + NumDays;
810 Itemkey => ItemKey,
807 TxtDate := to_char(TempDate, 'YY MM DD');
808 TempDate := to_date(TxtDate, 'YY MM DD');
809 wf_engine.SetItemAttrDate(Itemtype => ItemType,
811 aname => 'VALUE2',
812 avalue => TempDate);
813
814 resultout :='COMPLETE:Y';
815
816 END IF;
817
818 exception
819 when others then
820 WF_CORE.CONTEXT('MSDWF', 'SetColDate', itemtype, itemkey, to_char(actid), funcmode);
821 raise;
822 end SetColDate;
823
824 --========================================================================
825 -- StartMaster
826 --
827 -- Concurrent program that is used to run the ODP master WF process
828 --
829 --
830 -- IN
831 -- PlanName - A valid plan name from msd_demand_plans_v
832 -- NumDays_to_collect - Number of days to run the collection
833 -- OUT
834 -- errbuf - error message : process or PL/SQL error.
835 -- retcode - return code (0 = success, 2 = error).
836 --
837 procedure StartMaster(errbuf out varchar2,
838 retcode out number,
839 PlanName in varchar2,
840 NumDays_to_collect in varchar2)
841
842 IS
843
844 retText varchar2(200);
845 dispMesg varchar2(200);
846 itemtype varchar2(8) := 'ODPCYCLE';
847 workflowProcess varchar2(11) := 'ODPAUTOMATE';
848 owner varchar2(30) := fnd_global.user_name;
849 orgcode varchar2(3);
850 instcode varchar2(3);
851 org varchar2(8);
852 PlanID varchar2(16);
853 itemkey varchar2(200);
854 numDaysToCol number;
855 EngItemKey varchar2(200);
856
857
858 BEGIN
859 errbuf := ' ';
860
861 -- Get needed plan information
862 SELECT demand_plan_id, code_location, shared_db_prefix, shared_db_location, express_connect_string INTO PlanID, CodeLoc, DBName, SharedLoc, express_server
863 from msd_demand_plans_v
864 where demand_plan_name=PlanName;
865
866 -- Get organiztion code and instance
867 SELECT msd_organization_definitions.organization_code, msc_apps_instances.instance_code
868 INTO orgcode, instcode
869 FROM msd_organization_definitions, msc_apps_instances, msd_demand_plans_v
870 WHERE PlanID = msd_demand_plans_v.DEMAND_PLAN_ID AND
871 msd_demand_plans_v.ORGANIZATION_ID = msd_organization_definitions.ORGANIZATION_ID AND msd_demand_plans_v.SR_INSTANCE_ID = msc_apps_instances.instance_id;
872
873 numDaysToCol := to_number(NumDays_to_collect);
874 -- Set item key
875 org := orgcode || ':' || instcode;
876 itemkey := org || '-' || PlanName || '-' || to_char( sysdate, 'MM/DD/YYYY-HH24:MI:SS') || '-' || workflowprocess;
877
878 -- Create WF Automate process instance
879 wf_engine.CreateProcess(ItemType => ItemType,
880 itemKey => ItemKey,
881 process => WorkflowProcess);
882 -- This should be the demand planning administrator.
883 wf_engine.SetItemOwner(ItemType => ItemType,
884 ItemKey => ItemKey,
885 owner => owner);
886 -- Sets new attribute Is OPD Master running.
887 wf_engine.SetItemAttrText(Itemtype => ItemType,
888 Itemkey => ItemKey,
889 aname => 'ISMASTER',
890 avalue => 'Y');
891 wf_engine.SetItemAttrText(Itemtype => ItemType,
892 Itemkey => ItemKey,
893 aname => 'DPADMIN',
894 avalue => owner);
895 wf_engine.SetItemAttrText(Itemtype => ItemType,
896 Itemkey => ItemKey,
897 aname => 'ODPROLE',
898 avalue => owner);
899 -- Plan ID!
900 wf_engine.SetItemAttrText(Itemtype => ItemType,
901 Itemkey => ItemKey,
902 aname => 'ODPPLAN',
903 avalue => PlanID);
904 wf_engine.SetItemAttrText(Itemtype => ItemType,
905 Itemkey => ItemKey,
906 aname => 'PLNAME',
907 avalue => PlanName);
908 wf_engine.SetItemAttrText(Itemtype => ItemType,
909 Itemkey => ItemKey,
910 aname => 'CODELOC',
911 avalue => CodeLoc);
912 wf_engine.SetItemAttrText(Itemtype => ItemType,
913 Itemkey => ItemKey,
914 aname => 'DBNAME',
915 avalue => DBName);
916 wf_engine.SetItemAttrText(Itemtype => ItemType,
917 Itemkey => ItemKey,
918 aname => 'SHAREDLOC',
919 avalue => SharedLoc);
920 wf_engine.SetItemAttrText(Itemtype => ItemType,
921 Itemkey => ItemKey,
922 aname => 'EXPCONN',
923 avalue => express_server);
924 -- set NumDays to collect
925 wf_engine.SetItemAttrNumber(Itemtype => ItemType,
926 Itemkey => ItemKey,
927 aname => 'NUMDAYS',
928 avalue => NumDaysToCol);
929
930 -- Now that all is created and set START the PROCESS!
931 wf_engine.StartProcess(ItemType => ItemType,
932 ItemKey => ItemKey);
933 commit;
934
935 -- Start background engine for this process.
936 EngItemKey := org || '-' || PlanName || '-' || to_char( sysdate, 'MM/DD/YYYY-HH24:MI:SS') || '- MSDWFSTRTBG';
937 MSDWF.StartConcProc('MSDWFSTRTBG', 'ODPCYCLE', EngItemKey, owner, PlanID, Itemkey);
938 commit;
939
940 return;
941
942 exception
943 when others then
944 retcode :='2';
945 errbuf:=substr(sqlerrm, 1, 255);
946 raise;
947
948 end StartMaster;
949 -- ========================================================================
950 end MSDWF;