[Home] [Help]
PACKAGE BODY: APPS.MSD_DPE
Source
1 PACKAGE BODY MSD_DPE AS
2 /* $Header: msddpprb.pls 120.0 2005/05/25 20:25:29 appldev noship $ */
3
4 -- Constants
5
6 INFORMATION Constant varchar2(30):='INFORMATION';
7 HEADING Constant varchar2(30):='HEADING';
8 SECTION Constant varchar2(30):='SECTION';
9
10 -- USED BY DISPLAY_MESSAGE
11 --
12 l_last_msg_type varchar2(30);
13
14
15 /* Overloaded method. Added so that defaults could be removed,
16 * for performance reasons.
17 */
18
19 procedure Purge (errbuf out nocopy varchar2,
20 retcode out nocopy varchar2,
21 PlanID in varchar2,
22 Demand_Plan_Name in varchar2,
23 Shared_DB_Prefix in varchar2,
24 Code_Location in varchar2,
25 Shared_DB_Location in varchar2,
26 Express_Machine_Port in varchar2,
27 OWA_Virtual_Path_Name in varchar2,
28 EAD_Name in varchar2,
29 Express_Connect_String in varchar2,
30 DelIfWFActive in varchar2) is
31 begin
32 Purge (errbuf => errbuf,
33 retcode => retcode,
34 PlanID => PlanID,
35 Demand_Plan_Name => Demand_Plan_Name,
36 Shared_DB_Prefix => Shared_DB_Prefix,
37 Code_Location => Code_Location,
38 Shared_DB_Location => Shared_DB_Location,
39 Express_Machine_Port => Express_Machine_Port,
40 OWA_Virtual_Path_Name => OWA_Virtual_Path_Name,
41 EAD_Name => EAD_Name,
42 Express_Connect_String => Express_Connect_String,
43 DelIfWFActive => DelIfWFActive,
44 DelIfconFail => 'NO');
45
46 /* update the build refresh num if the plans are deleted. */
47
48 if (nvl(retcode, '0') <> '2') then
49
50 update msd_demand_plans
51 set dp_build_refresh_num = null,
52 dp_build_error_flag = null
53 where demand_plan_id = PlanID;
54
55 end if;
56
57 end;
58
59
60 --
61 -- Purge (PUBLIC)
62 --
63 -- 1.Checks the state for the plan to be deleted.
64 -- 2.If plan doesn't have Active process
65 -- delets all DPE databases and all obsolete workfdlow data for current plan.
66 -- 3.Removes validation status for this plan (DPE_BUILD field in MSD_DEMAND_PLANS table).
67 -- 4.Populates log file with the process's messages.
68 --
69 -- IN
70 -- number of msd_demand_plans_v columns - PlanID ,
71 -- Shared_DB_Prefix ,
72 -- Code_Location,
73 -- Shared_DB_Location,
74 -- Express_Machine_Port,
75 -- OWA_Virtual_Path_Name,
76 -- EAD_Name,
77 -- Express_Connect_String
78 --
79 -- DeleteAnyway - depends on 'Delete even Plan has an Active process' checkbox in
80 -- the Purge plan dialog box. Values:
81 -- YES - checkbox is checked,
82 -- NO - checkbox is unchecked
83 -- OUT
84 -- errbuf - error message : process or PL/SQL error
85 -- retcode - return code (0 = success, 2 = error)
86 --
87 --
88 procedure Purge (errbuf out nocopy varchar2,
89 retcode out nocopy varchar2,
90 PlanID in varchar2,
91 Demand_Plan_Name in varchar2,
92 Shared_DB_Prefix in varchar2,
93 Code_Location in varchar2,
94 Shared_DB_Location in varchar2,
95 Express_Machine_Port in varchar2,
96 OWA_Virtual_Path_Name in varchar2,
97 EAD_Name in varchar2,
98 Express_Connect_String in varchar2,
99 DelIfWFActive in varchar2,
100 DelIfconFail in varchar2)
101
102 IS
103 ItemType varchar2(20);
104 CurrStatus varchar2(20);
105 retText varchar2(200);
106 planName varchar2(100);
107 dispMesg varchar2(500);
108 ActRetcode varchar2(10);
109 ExpRetcode varchar2(10);
110 WfRetcode varchar2(10);
111
112 pos number;
113
114 ERROR Constant varchar2(30):='ERROR';
115 INFORMATION Constant varchar2(30):='INFORMATION';
116 SECTION Constant varchar2(30):='SECTION';
117 SPLPROB_NODEL EXCEPTION;
118 PROBWF_NODEL EXCEPTION;
119 BEGIN
120
121 errbuf := ' ';
122
123 -- get current plan's name
124 planName := Demand_Plan_Name;
125
126 dispMesg := 'Beginning Purge for demand plan ' || planName || ' (Plan ID = ' || PlanID || ').';
127 MSD_DPE.display_message(dispMesg , SECTION);
128
129 --
130 if ((DelIfWFActive = 'YES') and (DelIfconFail = 'YES')) then
131
132 -- delete all databases for this plan if no one in use
133 dispMesg := 'Deleting Express Demand Planning Engine databases.';
134 MSD_DPE.display_message(dispMesg , INFORMATION);
135
136 MSD_DPE.DeleteBuildDBS(errbuf, retText, ExpRetcode,
137 inPlan => PlanID,
138 Demand_Plan_Name => Demand_Plan_Name,
139 Shared_DB_Prefix => Shared_DB_Prefix,
140 Code_Location => Code_Location,
141 Shared_DB_Location => Shared_DB_Location,
142 Express_Machine_Port => Express_Machine_Port,
143 OWA_Virtual_Path_Name => OWA_Virtual_Path_Name,
144 EAD_Name => EAD_Name,
145 Express_Connect_String => Express_Connect_String);
146
147 if ExpRetcode = '2' or ExpRetcode = '1' then
148 retcode := '1';
149 --dispMesg := errbuf || retText;
150 MSD_DPE.display_error_warning(errbuf, retText);
151 errbuf := ' ';
152 else
153 retcode := '0';
154 end if;
155
156 -- purge all obsolete workfdlow data for current plan.
157 dispMesg := 'Purging Workflow Processes.';
158 MSD_DPE.display_message(dispMesg , INFORMATION);
159
160 MSD_DPE.DeleteWorkflow (errbuf, WfRetcode, PlanID);
161 -- Warning when DelIfWFActive = 'YES'
162 if WFRetCode = '2' then
163 retcode := '1';
164 dispMesg := errbuf;
165 MSD_DPE.display_message(dispMesg , INFORMATION);
166 errbuf := ' ';
167 end if;
168
169 dispMesg := 'End of Purge processing.';
170 MSD_DPE.display_message(dispMesg , SECTION);
171 -- RETURN HERE!
172 return;
173 end if;
174
175 --
176
177 if ((DelIfWFActive = 'NO') and (DelIfconFail = 'NO')) then
178
179 dispMesg := 'Checking for active Workflow Processes.';
180 MSD_DPE.display_message(dispMesg , INFORMATION);
181 MSD_DPE.ActivityTest(errbuf, ActRetcode, PlanID);
182
183 if ActRetCode = '2' then
184 retcode := '2';
185 dispMesg := errbuf;
186 errbuf := ' ';
187 else
188 retcode := '0';
189 -- delete all databases for this plan if no one in use
190
191 dispMesg := 'Deleting Express Demand Planning Engine databases.';
192 MSD_DPE.display_message(dispMesg , INFORMATION);
193
194 MSD_DPE.DeleteBuildDBS(errbuf, retText, ExpRetCode,
195 inPlan => PlanID,
196 Demand_Plan_Name => Demand_Plan_Name,
197 Shared_DB_Prefix => Shared_DB_Prefix,
198 Code_Location => Code_Location,
199 Shared_DB_Location => Shared_DB_Location,
200 Express_Machine_Port => Express_Machine_Port,
201 OWA_Virtual_Path_Name => OWA_Virtual_Path_Name,
202 EAD_Name => EAD_Name,
203 Express_Connect_String => Express_Connect_String);
204
205 if ExpRetcode = '2' then
206 retcode := '2';
207 MSD_DPE.display_error_warning(errbuf, retText);
208 errbuf := ' ';
209 raise SPLPROB_NODEL;
210 else
211 if ExpRetCode = '1' then
212 retcode := '1';
213 MSD_DPE.display_error_warning(errbuf, retText);
214 errbuf := ' ';
215 else
216 retcode := '0';
217 end if;
218
219 -- purge all obsolete workfdlow data for current plan.
220 dispMesg := 'Purging Workflow Processes.';
221 MSD_DPE.display_message(dispMesg , INFORMATION);
222
223 MSD_DPE.DeleteWorkflow (errbuf, WfRetcode, PlanID);
224
225 if WFRetCode = '2' then
226 retcode := '2';
227 dispMesg := errbuf;
228 MSD_DPE.display_message(dispMesg , INFORMATION);
229 errbuf := ' ';
230 raise PROBWF_NODEL;
231 end if;
232
233 end if;
234 end if;
235
236 dispMesg := 'End of Purge processing.';
237 MSD_DPE.display_message(dispMesg , SECTION);
238 -- RETURN HERE!
239 return;
240 end if;
241
242 --
243 --
244
245 if ((DelIfWFActive = 'NO') and (DelIfconFail = 'YES')) then
246
247 dispMesg := 'Checking for active Workflow Processes.';
248 MSD_DPE.display_message(dispMesg , INFORMATION);
249 MSD_DPE.ActivityTest(errbuf, ActRetcode, PlanID);
250
251 if ActRetCode = '2' then
252 retcode := '2';
253 else
254 retcode := '0';
255 -- delete all databases for this plan if no one in use
256
257 dispMesg := 'Deleting Express Demand Planning Engine databases.';
258 MSD_DPE.display_message(dispMesg , INFORMATION);
259
260 MSD_DPE.DeleteBuildDBS(errbuf, retText, ExpRetCode,
261 inPlan => PlanID,
262 Demand_Plan_Name => Demand_Plan_Name,
263 Shared_DB_Prefix => Shared_DB_Prefix,
264 Code_Location => Code_Location,
265 Shared_DB_Location => Shared_DB_Location,
266 Express_Machine_Port => Express_Machine_Port,
267 OWA_Virtual_Path_Name => OWA_Virtual_Path_Name,
268 EAD_Name => EAD_Name,
269 Express_Connect_String => Express_Connect_String);
270
271 if ExpRetcode = '2' or ExpRetcode = '1' then
272 retcode := '1';
273 --dispMesg := errbuf || retText;
274 MSD_DPE.display_error_warning(errbuf, retText);
275 errbuf := ' ';
276 else
277 retcode := '0';
278 end if;
279
280 -- purge all obsolete workfdlow data for current plan.
281 dispMesg := 'Purging Workflow Processes.';
282 MSD_DPE.display_message(dispMesg , INFORMATION);
283
284 MSD_DPE.DeleteWorkflow (errbuf, WfRetcode, PlanID);
285
286 if WFRetCode = '2' then
287 retcode := '2';
288 dispMesg := errbuf;
289 MSD_DPE.display_message(dispMesg , INFORMATION);
290 errbuf := ' ';
291 raise PROBWF_NODEL;
292 end if;
293
294 end if;
295
296 dispMesg := 'End of Purge processing.';
297 MSD_DPE.display_message(dispMesg , SECTION);
298 -- RETURN HERE!
299 return;
300 end if;
301
302 --
303 --
304
305 if ((DelIfWFActive = 'YES') and (DelIfconFail = 'NO')) then
306
307 -- delete all databases for this plan if no one in use
308 dispMesg := 'Deleting Express Demand Planning Engine databases.';
309 MSD_DPE.display_message(dispMesg , INFORMATION);
310
311 MSD_DPE.DeleteBuildDBS(errbuf, retText, ExpRetcode,
312 inPlan => PlanID,
313 Demand_Plan_Name => Demand_Plan_Name,
314 Shared_DB_Prefix => Shared_DB_Prefix,
315 Code_Location => Code_Location,
316 Shared_DB_Location => Shared_DB_Location,
317 Express_Machine_Port => Express_Machine_Port,
318 OWA_Virtual_Path_Name => OWA_Virtual_Path_Name,
319 EAD_Name => EAD_Name,
320 Express_Connect_String => Express_Connect_String);
321
322 if ExpRetcode = '2' then
323 retcode := '2';
324 MSD_DPE.display_error_warning(errbuf, retText);
325 errbuf := ' ';
326 raise SPLPROB_NODEL;
327 else
328
329 if ExpRetCode = '1' then
330 retcode := '1';
331 MSD_DPE.display_error_warning(errbuf, retText);
332 errbuf := ' ';
333 else
334 retcode := '0';
335 end if;
336
337 -- purge all obsolete workfdlow data for current plan.
338 dispMesg := 'Purging Workflow Processes.';
339 MSD_DPE.display_message(dispMesg , INFORMATION);
340
341 MSD_DPE.DeleteWorkflow (errbuf, WfRetcode, PlanID);
342
343 if WFRetCode = '2' then
344 retcode := '1';
345 dispMesg := errbuf;
346 MSD_DPE.display_message(dispMesg , INFORMATION);
347 errbuf := ' ';
348 end if;
349
350 end if;
351
352 dispMesg := 'End of Purge processing.';
353 MSD_DPE.display_message(dispMesg , SECTION);
354 -- RETURN HERE!
355 return;
356 end if;
357 --
358 --
359 exception
360
361 when SPLPROB_NODEL then
362 retcode :='2';
363
364 dispMesg := 'The demand plan databases have not been deleted.';
365 MSD_DPE.display_message(dispMesg , INFORMATION);
366
367 dispMesg := 'End of Purge processing.';
368 MSD_DPE.display_message(dispMesg , SECTION);
369
370 dispMesg := 'The demand plan was not deleted.';
371 MSD_DPE.display_message(dispMesg , SECTION);
372 errbuf:= ' ';
373
374
375 when PROBWF_NODEL then
376 retcode :='2';
377 dispMesg := 'Problem encountered when purging Workflow processes.';
378 MSD_DPE.display_message(dispMesg , INFORMATION);
379
380 dispMesg := 'End of Purge processing.';
381 MSD_DPE.display_message(dispMesg , SECTION);
382
383 dispMesg := 'The demand plan was not deleted.';
384 MSD_DPE.display_message(dispMesg , SECTION);
385 errbuf:= ' ';
386
387
388 when others then
389
390 errbuf:=substr(sqlerrm, 1, 255);
391
392 dispMesg := errbuf;
393 MSD_DPE.display_message(dispMesg , INFORMATION);
394
398 dispMesg := 'End of Purge processing.';
395 -- dispMesg := 'The demand plan databases have not been deleted.';
396 -- MSD_DPE.display_message(dispMesg , INFORMATION);
397
399 MSD_DPE.display_message(dispMesg , SECTION);
400
401 if ((DelIfWFActive = 'NO') or (DelIfconFail = 'NO')) then
402 retcode :='2';
403 dispMesg := 'The demand plan was not deleted.';
404 MSD_DPE.display_message(dispMesg , SECTION);
405 else
406 retcode :='1';
407 end if;
408 errbuf:= ' ';
409
410 -- raise;
411
412 end Purge;
413
414
415 --
416 -- ActivityTest (PUBLIC)
417 --
418 -- 1. Selects all ItemKeys(instances) for current planID and checks if
419 -- any ACTIVITY_STATUS for each ItemKey is ACTIVE.
420 -- IN
421 -- PlanID - ID of Plan to be deleted
422 -- OUT
423 -- errbuf - error message : 'Process is ACTIVE' or PL/SQL error
424 -- retcode - return code (0 = success, 2 = error)
425 --
426 --
427
428 PROCEDURE ActivityTest (errbuf out nocopy varchar2,
429 retcode out nocopy varchar2,
430 inPlan in number)
431 IS
432 itemType varchar2(20);
433 CurrStatus varchar2(200);
434 result varchar2(200);
435 -- agb 01/21/02 added for select below
436 inplanTXT varchar2(16);
437
438
439
440 CURSOR c_ItemKeys is
441 select item_key
442 from WF_ITEM_ATTRIBUTE_VALUES
443 where item_type = itemType
444 and name = 'ODPPLAN'
445 and text_value = inPlanTXT;
446
447 v_ItemKey c_ItemKeys%ROWTYPE;
448
449 BEGIN
450
451 -- agb 01/21/02 convert to text for some selects
452 inplanTXT := to_char(inPlan);
453
454 itemType := 'ODPCYCLE';
455 retcode := '0';
456
457 -- Check activity process for current plan
458 for v_ItemKey in c_ItemKeys loop
459 wf_engine.ItemStatus(itemType, v_ItemKey.item_key, currStatus, result);
460 if RTRIM(currStatus) = 'ACTIVE' then
461 retcode := '2';
462 errbuf:='Cannot purge. The plan has an ACTIVE Workflow Process.';
463 exit;
464 else
465 retcode := '0';
466 end if;
467 end loop;
468
469 return;
470
471 exception
472 when NO_DATA_FOUND then
473 retcode :='0';
474
475 when others then
476 retcode :='2';
477 errbuf:=substr(sqlerrm, 1, 255);
478
479 -- raise;
480
481 end ActivityTest;
482
483
484 --
485 -- DeleteBuildDBS (PUBLIC)
486 --
487 -- 1. Selects data from MSD_DEMAND_PLANS_V table for the specific planID.
488 -- 2. Takes selected columns and uses that data to exercise an EPS/SNAPI
489 -- connection to OES and then, if connection is established,
490 -- runs ODPDELPLAN activity to run delete plan functionality.
491
492 -- IN
493 -- number of msd_demand_plans_v columns - PlanID ,
494 -- Demand_Plan_Name,
495 -- Shared_DB_Prefix ,
496 -- Code_Location,
497 -- Shared_DB_Location,
498 -- Express_Machine_Port,
499 -- OWA_Virtual_Path_Name,
500 -- EAD_Name,
501 -- Express_Connect_String
502 -- OUT
503 -- errbuf - error message : 'Database in use', Express error or PL/SQL error
504 -- actText out varchar2,
505 -- retcode - return code (0 = success, 2 = error)
506 --
507 --
508
509 PROCEDURE DeleteBuildDBS (errbuf out nocopy varchar2,
510 actText out nocopy varchar2,
511 retcode out nocopy varchar2,
512 inPlan in number,
513 Demand_Plan_Name in varchar2,
514 Shared_DB_Prefix in varchar2,
515 Code_Location in varchar2,
516 Shared_DB_Location in varchar2,
517 Express_Machine_Port in varchar2,
518 OWA_Virtual_Path_Name in varchar2,
519 EAD_Name in varchar2,
520 Express_Connect_String in varchar2)
521 IS
522 ActEntry varchar2(16);
523 EPSRetErr varchar2(2000);
524 EPSRetcode varchar2(100);
525 EPSRetVal varchar2(2000);
526 EPSRetText varchar2(2000);
527 thisrole varchar2(30);
528 express_server varchar2(240);
529 DBName varchar2(80);
530 CodeLoc varchar2(240);
531 SharedLoc varchar2(240);
532 PlName varchar2(30);
533 Owner varchar2(30);
534 DPAdmin varchar2(30);
535 URLret varchar2(10);
536 OESPort varchar2(80);
537 thisURL varchar2(100);
538
539 BEGIN
540
541 -- uses inPlan to get Express connection and DPE plan information
542
543 PlName := Demand_Plan_Name;
544 CodeLoc := Code_Location;
545 DBName := Shared_DB_Prefix;
546 SharedLoc := Shared_DB_Location;
547 express_server := Express_Connect_String;
548 OESPort := Express_Machine_Port;
549
550
551 -- value activity.entry, points to program in Express
552 ActEntry := 'ODPDELPLAN';
553 /*
554 SELECT C0, C1, C2
555 into EPSretcode, EPSRetText, EPSRetErr
556 from THE (SELECT CAST (EPS.query(express_server,
557 'DB0='|| CodeLoc || '/ODPCODE\'
558 || 'DBCount=1\'
559 || 'MeasureCount=3\'
560 || 'Measure0=ACTIVITY.FORMULA\'
561 || 'Measure1= ACTIVITY.TEXT\'
562 || 'Measure2=ACTIVITY.ERROR\'
563 || 'E0Count=2\'
564 || 'E0Dim0Name=PLACEHOLDER\'
565 || 'E0Dim1Name=ACTIVITY.ENTRY\'
566 || 'E0Dim1Script=CALL WF.SETACTIVITY('''|| ActEntry || ''', '''|| inPlan ||''', '''|| DBName ||''', '''|| SharedLoc ||''', '''|| DPAdmin ||''', '''|| thisrole ||''')\'
567 ,NULL
571 ,NULL
568 ,NULL
569 ,NULL
570 ,NULL
572 ,NULL
573 ,NULL
574 ,NULL
575 ,NULL)
576 AS EPS_express_list_t)
577 from DUAL);
578 */
579 msd_wf.execute_dml2(ActEntry, inPlan, DBName, SharedLoc,DPAdmin, thisrole,'','',
580 '', EPSretcode, EPSRetText, EPSRetVal,EPSRetErr);
581
582
583
584
585
586 -- this means the test succeeded.
587 if UPPER(RTRIM(EPSretcode)) = 'Y' then
588 actText := EPSRetText;
589 retcode := '0';
590 end if;
591
592 -- this means the test was completed with warnings.
593 if UPPER(RTRIM(EPSretcode)) = 'W' then
594 actText := EPSRetText;
595 retcode := '1';
596 end if;
597
598 -- this means the test failed. There was an error inside an Express program!
599 if UPPER(RTRIM(EPSretcode)) = 'N' then
600 errbuf:=substr(EPSRetErr, 1, 255);
601 actText := EPSRetText;
602 retcode := '2';
603 end if;
604
605 return;
606
607 exception
608 when others then
609
610 retcode :='2';
611 errbuf:=substr(sqlerrm, 1, 255);
612 raise;
613
614 end DeleteBuildDBS;
615
616
617 --
618 -- DeleteWorkflow (PUBLIC)
619 --
620 -- 1. Selects all ItemKeys(instances) for current planID and checks if
621 -- any ACTIVITY_STATUS for each ItemKey is ACTIVE.
622 -- 2. Purge obsolete workflow depending on ACTIVITY_STATUS.
623 -- IN
624 -- PlanID - ID of Plan to be deleted
625 -- OUT
626 -- errbuf - error message : 'Process is ACTIVE' or PL/SQL error
627 -- retcode - return code (0 = success, 2 = error)
628 --
629 --
630 procedure DeleteWorkflow (errbuf out nocopy varchar2,
631 retcode out nocopy varchar2,
632 inPlan in number)
633 IS
634 ItemType varchar2(20);
635 CurrStatus varchar2(20);
636 result varchar2(100);
637 -- agb 01/21/02 added for select below
638 inplanTXT varchar2(16);
639
640
641 CURSOR c_ItemKeys is
642 select item_key
643 from WF_ITEM_ATTRIBUTE_VALUES
644 where item_type = itemType
645 and name = 'ODPPLAN'
646 and text_value = inPlanTXT;
647
648 v_ItemKey c_ItemKeys%ROWTYPE;
649
650
651 BEGIN
652 -- agb 01/21/02 convert to text for some selects
653 inplanTXT := to_char(inPlan);
654 ItemType := 'ODPCYCLE';
655 retcode := '0';
656
657 -- Check activity process for current plan
658 for v_ItemKey in c_ItemKeys loop
659
660 wf_engine.ItemStatus(itemType, v_ItemKey.item_key, currStatus, result);
661
662 if UPPER(RTRIM(currStatus)) = 'COMPLETE' then
663 WF_PURGE.Total(itemType, v_ItemKey.item_key);
664 elsif UPPER(RTRIM(currStatus)) = 'ERROR' or UPPER(RTRIM(currStatus)) = 'ACTIVE' then
665 WF_ENGINE.AbortProcess(itemType, v_ItemKey.item_key);
666 WF_PURGE.Total(itemType, v_ItemKey.item_key);
667 elsif UPPER(RTRIM(currStatus)) = 'SUSPENDED' then
668 NULL;
669 else
670 retcode := '2';
671 errbuf:='Plan has an ACTIVE process and Workflow cannot be deleted.';
672 -- exit;
673 end if;
674
675 end loop;
676
677 return;
678
679 exception
680
681 when NO_DATA_FOUND then
682 retcode :='0';
683
684 when others then
685 retcode :='2';
686 errbuf:=substr(sqlerrm, 1, 255);
687
688 -- raise;
689
690 end DeleteWorkflow;
691
692
693 --
694 -- Display_Message (PUBLIC) - this procedure was taken from msd_validate_demand_plan_new
695 --
696 -- 1. Populates log file with passed messages .
697 --
698 -- IN
699 -- p_text - text of the message
700 -- msg_type - the message's location definition
701 --
702 --
703
704 Procedure display_message(p_text in varchar2, msg_type in varchar2) is
705
706 l_tab varchar2(4):=' ';
707 L_MAX_LENGTH number:=90;
708
709 BEGIN
710 if msg_type = SECTION then
711 if nvl(l_last_msg_type, 'xx') <> SECTION then
712 show_message('');
713 end if;
714 show_message( substr(p_text, 1, L_MAX_LENGTH) );
715 elsif msg_type in (INFORMATION, HEADING) then
716 show_message( l_tab || substr(p_text, 1, L_MAX_LENGTH));
717 else
718 show_message( l_tab || rpad(p_text, L_MAX_LENGTH) || ' ' || msg_type);
719 end if;
720
721 l_last_msg_type := msg_type;
722
723 End display_message;
724
725
726 --
727 -- Show_Message (PUBLIC) - this procedure was taken from msd_validate_demand_plan_new
728 --
729 -- 1. Populates log file with passed messages .
730 --
731 -- IN
732 -- p_text - text of the message
733 --
734
735 Procedure show_message(p_text in varchar2) is
736
737 BEGIN
738
739 if (p_text is not NULL) then
740 fnd_file.put_line(fnd_file.log, p_text);
741 end if;
742
743 END;
744
745 /* Wrapper to call DeleteWorkflow from Express SPL named odpwf.cleanup. */
746 Procedure CallDelWF(inPlan in number) is
747
748 retcode varchar2(2);
749 errbuf varchar2(100);
750
751 BEGIN
752 MSD_DPE.DeleteWorkflow(errbuf, retcode, inPlan);
753 return;
754
755 exception
756 when others then
757 RAISE_APPLICATION_ERROR(-20100, 'Error in MSD_DPE.CallDelWF');
758 END;
759
760 --+++++++++++++++++++++++++++++++++++++++++
761 --
762 -- display_error_warning(PUBLIC)
763 --
764 -- 1. Displays error/warning message returned by the DML procedure.
765 -- The symbols '++' are using as separators to divide too long messages in pieces.
766 -- IN
767 -- errbuf - system part of error message.
768 -- retText - ODP's description of current error.
769 --
770 Procedure display_error_warning(errbuf in varchar2, retText in varchar2) is
771
772 pos number;
773 dispMesg varchar2(500);
774 messageText varchar2(500);
775
776 BEGIN
777
778 dispMesg := errbuf;
779 MSD_DPE.display_message(dispMesg , INFORMATION);
780
781 messageText := retText;
782
783 if (messageText is not NULL) then
784 --check if retText is too long and has a '++' separator,
785 pos := instr(messageText, '++');
786 while pos > 0 loop
787 dispMesg := substr(messageText, 1, pos-1);
788 MSD_DPE.display_message(dispMesg , INFORMATION);
789 messageText := substr(messageText, pos+2);
790 pos := instr(messageText, '++');
791 end loop;
792
793 dispMesg := messageText;
794 MSD_DPE.display_message(dispMesg , INFORMATION);
795 end if;
796
797 return;
798
799 END;
800
801 --+++++++++++++++++++++++++++++++++++++++++
802
803
804 end MSD_DPE;