[Home] [Help]
PACKAGE BODY: APPS.MSD_WS_DEM_RENAME_FORECAST
Source
1 PACKAGE BODY MSD_WS_DEM_RENAME_FORECAST AS
2 /* $Header: MSDWDRFB.pls 120.21.12020000.3 2013/02/04 07:42:44 kkhatri ship $ */
3
4
5 /*** PROCEDURES FUNCTIONS ***
6 * ASSIGN_PLAN_NAME_TO_FORECAST
7 * ASSIGN_PLAN_NAME_TO_FORECAST_C
8 * GET_PLAN_SCENARIO_MEMBER_ID
9 */
10
11
12
13
14
15
16 --Bug 13980576
17 /* This new procedure will be called by ASSIGN_PLAN_NAME_TO_FORECAST_C and ASSIGN_PLAN_NAME_PUBLIC
18 * and by ASSIGN_PLAN_NAME_TO_FORECAST */
19
20
21 PROCEDURE ASSIGN_PLAN_NAME_INT (
22 errbuf out NOCOPY varchar2,
23 retcode out NOCOPY varchar2,
24 NewPlanName IN VARCHAR2,
25 DataProfileName IN VARCHAR2,
26 ArchiveFlag IN NUMBER,
27 p_parallelism_degree IN NUMBER DEFAULT 0)
28 IS
29
30 TYPE CUR_TYPE IS REF CURSOR;
31 x_cur_type CUR_TYPE;
32
33 x_sql_stmt VARCHAR2(2000) := NULL;
34 x_sql VARCHAR2(6000) := NULL;
35
36 x_new_plan_name VARCHAR2(45) := NULL;
37 x_data_profile_name VARCHAR2(200) := NULL;
38
39 x_archive_flag NUMBER := NULL;
40 x_data_profile_id NUMBER := NULL;
41 x_scenario_id NUMBER := NULL;
42 x_parallelism_degree NUMBER :=NULL;
43
44 /* For Planning Hub */
45 x_errbuf VARCHAR2(1000) := NULL;
46 x_retcode VARCHAR2(1000) := NULL;
47 x_plan_run_id NUMBER := NULL;
48
49 BEGIN
50
51
52 x_new_plan_name := substr(NewPlanName, 1, 45);
53 x_data_profile_name := DataProfileName;
54 x_archive_flag := ArchiveFlag;
55 x_parallelism_degree := p_parallelism_degree;
56
57
58 /* Check if the Data Profile Name specified is present inside Demantra or not */
59 x_sql_stmt := 'SELECT nvl(sum(tq.id), 0) '
60 || ' FROM msd_dem_transfer_query tq '
61 || ' WHERE tq.query_name = ''' || x_data_profile_name || ''''
62 || ' AND msd_dem_upload_forecast.is_valid_scenario(tq.id) = 1 ';
63 EXECUTE IMMEDIATE x_sql_stmt INTO x_data_profile_id;
64
65
66 IF (x_data_profile_id <> 0)
67 THEN
68
69
70 /* Check if the given New Plan Name already exists */
71 x_sql_stmt := 'SELECT nvl(sum(scenario_id), 0) '
72 || ' FROM msd_dp_scenarios mds '
73 || ' WHERE mds.demand_plan_id = ' || MSD_DEM_UPLOAD_FORECAST.C_DEMAND_PLAN_ID
74 || ' AND mds.scenario_name = ''' || x_new_plan_name || '''';
75 EXECUTE IMMEDIATE x_sql_stmt INTO x_scenario_id;
76
77
78 /* Create/Update an entry for the Plan Name in the table msd_dp_scenarios */
79 IF (x_scenario_id = 0) THEN
80
81 SELECT MSD_DP_SCENARIOS_S.nextval
82 INTO x_scenario_id
83 FROM DUAL;
84 x_scenario_id := x_scenario_id + C_DUMMY_SCENARIO_ID_OFFSET;
85
86 INSERT INTO msd_dp_scenarios (
87 demand_plan_id,
88 scenario_id,
89 scenario_name,
90 forecast_based_on,
91 sc_type,
92 error_type,
93 associate_parameter,
94 last_update_date,
95 last_updated_by,
96 creation_date,
97 created_by )
98 VALUES (
99 MSD_DEM_UPLOAD_FORECAST.C_DEMAND_PLAN_ID,
100 x_scenario_id,
101 x_new_plan_name,
102 substrb(x_data_profile_name,1,30),
103 msd_dem_upload_forecast.is_global_scenario(x_data_profile_id),
104 msd_dem_upload_forecast.get_error_type(x_data_profile_id),
105 C_ASSOCIATE_PARAMETER,
106 sysdate,
107 FND_GLOBAL.USER_ID,
108 sysdate,
109 FND_GLOBAL.USER_ID);
110
111 ELSE
112
113 UPDATE msd_dp_scenarios
114 SET
115 forecast_based_on = substrb(x_data_profile_name,1,30),
116 sc_type = msd_dem_upload_forecast.is_global_scenario(x_data_profile_id),
117 error_type = msd_dem_upload_forecast.get_error_type(x_data_profile_id),
118 last_update_date = sysdate,
119 last_updated_by = FND_GLOBAL.USER_ID
120 WHERE scenario_id = x_scenario_id;
121
122 DELETE FROM msd_dp_scenario_output_levels
123 WHERE scenario_id = x_scenario_id;
124
125 commit;
126
127 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
128
129 x_sql := 'DELETE /*+ PARALLEL (msd_dp_scn_entries_denorm,' || x_parallelism_degree
130 || ') */ FROM msd_dp_scn_entries_denorm'
131 || ' WHERE scenario_id =' || x_scenario_id;
132
133
134 EXECUTE IMMEDIATE x_sql;
135 commit;
136
137 EXECUTE IMMEDIATE 'ALTER SESSION DISABLE PARALLEL DML';
138
139 END IF;
140
141
142 /* Populate output levels for the forecast data */
143 INSERT INTO msd_dp_scenario_output_levels (
144 demand_plan_id,
145 scenario_id,
146 level_id,
147 last_update_date,
148 last_updated_by,
149 creation_date,
150 created_by )
151 SELECT
152 MSD_DEM_UPLOAD_FORECAST.C_DEMAND_PLAN_ID,
153 x_scenario_id,
154 to_number(flv.lookup_code),
155 sysdate,
156 FND_GLOBAL.USER_ID,
157 sysdate,
158 FND_GLOBAL.USER_ID
159 FROM
160 msd_dem_transfer_query tq,
161 msd_dem_transfer_query_levels tql,
162 msd_dem_group_tables gt,
163 fnd_lookup_values_vl flv
164 WHERE
165 tq.id = x_data_profile_id
166 AND tql.id = tq.id
167 AND gt.group_table_id = tql.level_id
168 AND flv.lookup_type = 'MSD_DEM_LEVELS'
169 AND to_number(msd_dem_common_utilities.get_app_id_text('MSD_DEM_DEMANTRA_OBJECT_ID',
170 flv.meaning,
171 1,
172 'group_table_id')) = gt.group_table_id;
173
174 commit;
175 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
176
177
178
179
180
181
182
183
184
185 x_sql := 'INSERT /*+ PARALLEL (msd_dp_scn_entries_denorm,' || x_parallelism_degree ||
186 ') */ INTO MSD_DP_SCN_ENTRIES_DENORM (' ||
187 'DEMAND_PLAN_ID, '||
188 'SCENARIO_ID, '||
189 'DEMAND_ID, '||
190 'BUCKET_TYPE, '||
191 'START_TIME, '||
192 'END_TIME, '||
193 'SR_INSTANCE_ID, '||
194 'SR_ORGANIZATION_ID, '||
195 'SR_INVENTORY_ITEM_ID, '||
196 'SR_SHIP_TO_LOC_ID, '||
197 'SR_CUSTOMER_ID, '||
198 'SR_ZONE_ID, '||
199 'DEMAND_CLASS, '||
200 'INVENTORY_ITEM_ID, '||
201 'DP_UOM_CODE, '||
202 'ASCP_UOM_CODE, '||
203 'UNIT_PRICE, '||
204 'QUANTITY, '||
205 'ERROR_TYPE, '||
206 'FORECAST_ERROR, '||
207 'PRIORITY, '||
208 'PF_NAME, '||
209 'REQUEST_ID,' ||
210 'MAPE_IN_SAMPLE, '||
211 'MAPE_OUT_SAMPLE, '||
212 'FORECAST_VOLATILITY, '||
213 'AVG_DEMAND, '||
214 'CREATION_DATE, '||
215 'CREATED_BY, '||
216 'LAST_UPDATE_LOGIN )'||
217 'SELECT ' ||
218 'entries.DEMAND_PLAN_ID, ' ||
219 x_scenario_id ||
220 ',entries.DEMAND_ID, ' ||
221 'entries.BUCKET_TYPE, ' ||
222 'entries.START_TIME, ' ||
223 'entries.END_TIME, ' ||
224 'entries.SR_INSTANCE_ID, ' ||
225 'entries.SR_ORGANIZATION_ID, ' ||
226 'entries.SR_INVENTORY_ITEM_ID, ' ||
227 'entries.SR_SHIP_TO_LOC_ID, ' ||
228 'entries.SR_CUSTOMER_ID, ' ||
229 'entries.SR_ZONE_ID, ' ||
230 'entries.DEMAND_CLASS, ' ||
231 'entries.INVENTORY_ITEM_ID, ' ||
232 'entries.DP_UOM_CODE, ' ||
233 'entries.ASCP_UOM_CODE, ' ||
234 'entries.UNIT_PRICE, ' ||
235 'entries.QUANTITY, ' ||
236 'entries.ERROR_TYPE, ' ||
237 'entries.FORECAST_ERROR, ' ||
238 'entries.PRIORITY, ' ||
239 'entries.PF_NAME, ' ||
240 'entries.REQUEST_ID, ' ||
241 'entries.MAPE_IN_SAMPLE, ' ||
242 'entries.MAPE_OUT_SAMPLE, ' ||
243 'entries.FORECAST_VOLATILITY, ' ||
244 'entries.AVG_DEMAND, ' ||
245 'entries.CREATION_DATE, ' ||
246 'entries.CREATED_BY, ' ||
247 'entries.LAST_UPDATE_LOGIN ' ||
248 'from ' ||
249 ' (select '||
250 'DEMAND_PLAN_ID, ' ||
251 'SCENARIO_ID, ' ||
252 'DEMAND_ID, ' ||
253 'BUCKET_TYPE, ' ||
254 'START_TIME, ' ||
255 'END_TIME, ' ||
256 'SR_INSTANCE_ID, ' ||
257 'SR_ORGANIZATION_ID, ' ||
258 'SR_INVENTORY_ITEM_ID, ' ||
259 'SR_SHIP_TO_LOC_ID, ' ||
260 'SR_CUSTOMER_ID, ' ||
261 'SR_ZONE_ID, ' ||
262 'DEMAND_CLASS, ' ||
263 'INVENTORY_ITEM_ID, ' ||
264 'DP_UOM_CODE, ' ||
265 'ASCP_UOM_CODE, ' ||
266 'UNIT_PRICE, ' ||
267 'QUANTITY, ' ||
268 'ERROR_TYPE, ' ||
269 'FORECAST_ERROR, ' ||
270 'PRIORITY, ' ||
271 'PF_NAME, ' ||
272 'REQUEST_ID, ' ||
273 'MAPE_IN_SAMPLE, ' ||
274 'MAPE_OUT_SAMPLE, ' ||
275 'FORECAST_VOLATILITY, ' ||
276 'AVG_DEMAND, ' ||
277 'CREATION_DATE, ' ||
278 'CREATED_BY, ' ||
279 'LAST_UPDATE_LOGIN ' ||
280 'FROM msd_dp_scn_entries_denorm ' ||
281 ' WHERE scenario_id = ' || MSD_DEM_UPLOAD_FORECAST.C_SCENARIO_ID_OFFSET || '+ ' || x_data_profile_id || ') entries';
282 EXECUTE IMMEDIATE x_sql;
283 COMMIT;
284
285 x_sql := 'DELETE /*+ PARALLEL (msd_dp_scn_entries_denorm,' || x_parallelism_degree
286 || ') */ FROM msd_dp_scn_entries_denorm'
287 || ' WHERE scenario_id =' || MSD_DEM_UPLOAD_FORECAST.C_SCENARIO_ID_OFFSET || '+ ' || x_data_profile_id;
288 EXECUTE IMMEDIATE x_sql;
289
290 COMMIT;
291 EXECUTE IMMEDIATE 'ALTER SESSION DISABLE PARALLEL DML';
292
293
294 ELSE
295 retcode := -1;
296 errbuf:='INVALID_DATA_PROFILE';
297 msd_dem_common_utilities.log_message ('Error: can not assign plan name to forecast' );
298 msd_dem_common_utilities.log_message(errbuf);
299 msd_dem_common_utilities.log_message ('Exiting: MSD_WS_DEM_RENAME_FORECAST.ASSIGN_PLAN_NAME_TO_FORECAST- ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
300
301 RETURN;
302 END IF;
303
304 COMMIT;
305
306
307
308 /* For Planning Hub */
309 msc_phub_pkg.populate_demantra_details (
310 x_errbuf,
311 x_retcode,
312 x_scenario_id,
313 x_plan_run_id,
314 x_archive_flag );
315
316 IF (x_retcode = '1')
317 THEN
318 retcode := x_retcode;
319 errbuf:= x_errbuf;
320 msd_dem_common_utilities.log_message ('Warning: IN assign plan name to forecast' );
321 msd_dem_common_utilities.log_debug ('Warning: IN assign plan name to forecast' );
322 msd_dem_common_utilities.log_debug (errbuf);
323
324 ELSIF(x_retcode = '-1')
325 THEN
326 retcode := x_retcode;
327 errbuf:= x_errbuf;
328 msd_dem_common_utilities.log_message ('Error: In assign plan name to forecast' );
329 msd_dem_common_utilities.log_message(errbuf);
330 msd_dem_common_utilities.log_message ('Exiting: MSD_WS_DEM_RENAME_FORECAST.ASSIGN_PLAN_NAME_TO_FORECAST- ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
331 RETURN;
332 END IF;
333
334 EXCEPTION
335 WHEN OTHERS THEN
336 retcode:= -1;
337 errbuf := substr(SQLERRM,1,150);
338 msd_dem_common_utilities.log_message ('Error: can not assign plan name to forecast' );
339 msd_dem_common_utilities.log_message ('Exception: MSD_WS_DEM_RENAME_FORECAST.ASSIGN_PLAN_NAME_TO_FORECAST - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS') );
340 msd_dem_common_utilities.log_message (errbuf);
341 RETURN;
342
343
344 END ASSIGN_PLAN_NAME_INT;
345
346
347 /*
348 * Procedure Name - ASSIGN_PLAN_NAME_TO_FORECAST
349 * This procedure assigns a user specified name to the forecast
350 * uploaded from Demantra into the MSD_DP_SCN_ENTRIES_DENORM table.
351 * It calls ASSIGN_PLAN_NAME_INT
352 *
353 * Parameters -
354 * NewPlanName - Name to be assigned to the recently exported
355 * forecast output.
356 * DataProfileName - Name of the Data Profile used to export
357 * forecast out of Demantra
358 *
359 * Given the parameter NewPlanName, create (or replace) an entry in the table MSD_DP_SCENARIOS.
360 * Demand Plan Id - (Hardcoded to) 5555555
361 * Scenario Id - Sequence starting from 8888888
362 *
363 * Using the DataProfileName, populate the table MSD_DP_SCENARIO_OUTPUT_LEVELS with
364 * the levels at which the forecast has been exported.
365 *
366 * Update the scenario id in the MSD_DP_SCN_ENTRIES_DENORM table to the Scenario Id generated
367 * for the given Plan Name.
368 *
369 * Return Values -
370 * The procedure returns a status. The possible return statuses are:
371 * SUCCESS, ERROR, INVALID_DATA_PROFILE
372 *
373 */
374
375
376
377 PROCEDURE ASSIGN_PLAN_NAME_TO_FORECAST (
378 status OUT NOCOPY VARCHAR2,
379
380 NewPlanName IN VARCHAR2,
381 DataProfileName IN VARCHAR2,
382 ArchiveFlag IN NUMBER)
383
384 IS
385
386
387 x_errbuf VARCHAR2(1000) := NULL;
388 x_retcode VARCHAR2(1000) := NULL;
389
390
391 BEGIN
392
393
394
395 ASSIGN_PLAN_NAME_INT (x_errbuf,x_retcode,NewPlanName,DataProfileName,ArchiveFlag);
396
397 IF (x_retcode <> '0')
398 THEN
399 status := 'ERROR';
400 ELSE
401 status := 'SUCCESS';
402 END IF;
403 EXCEPTION
404 WHEN OTHERS THEN
405 status := 'ERROR';
406
407 RETURN;
408
409 END ASSIGN_PLAN_NAME_TO_FORECAST;
410
411
412
413
414
415
416 PROCEDURE ASSIGN_PLAN_NAME_PUBLIC (
417 status OUT NOCOPY VARCHAR2,
418 UserName IN VARCHAR2,
419 RespName IN VARCHAR2,
420 RespApplName IN VARCHAR2,
421 SecurityGroupName IN VARCHAR2,
422 Language IN VARCHAR2,
423 NewPlanName IN VARCHAR2,
424 DataProfileName IN VARCHAR2,
425 ArchiveFlag IN NUMBER ) AS
426 userid number;
427 respid number;
428 l_String VARCHAR2(30);
429 error_tracking_num number;
430 l_SecutirtGroupId NUMBER;
431 x_errbuf VARCHAR2(2000) := NULL;
432 x_retcode VARCHAR2(20) := NULL;
433 BEGIN
434 error_tracking_num :=2010;
435 MSC_WS_COMMON.GET_PERMISSION_IDS(l_String, userid, respid, l_SecutirtGroupId, UserName, RespName, RespApplName, SecurityGroupName, Language);
436 IF (l_String <> 'OK') THEN
437 Status := l_String;
438 RETURN;
439 END IF;
440
441 error_tracking_num :=2030;
442 MSC_WS_COMMON.VALIDATE_USER_RESP_FUNC(l_String, userid, respid, 'MSD_DEM_WF_MGR', l_SecutirtGroupId);
443 IF (l_String <> 'OK') THEN
444 Status := l_String;
445 RETURN;
446 END IF;
447 error_tracking_num :=2040;
448
449
450 ASSIGN_PLAN_NAME_INT ( x_errbuf,
451 x_retcode,
452 NewPlanName,
453 DataProfileName,
454 ArchiveFlag );
455 IF (x_retcode <> '0')
456 THEN
457 status := 'ERROR';
458 ELSE
459 status := 'SUCCESS';
460 END IF;
461
462
463
464 EXCEPTION
465 WHEN others THEN
466 status := 'ERROR_UNEXPECTED_'||error_tracking_num;
467
468 return;
469
470
471 END ASSIGN_PLAN_NAME_PUBLIC;
472
473 /*
474 * Procedure Name - ASSIGN_PLAN_NAME_TO_FORECAST_C
475 * This procedure calls the procedure ASSIGN_PLAN_NAME_TO_FORECAST
476 * This is used in the assign plan name concurrent program
477 */
478 PROCEDURE ASSIGN_PLAN_NAME_TO_FORECAST_C(
479 errbuf out NOCOPY varchar2,
480 retcode out NOCOPY varchar2,
481 NewPlanName IN VARCHAR2,
482 DataProfileName IN VARCHAR2,
483 ArchiveFlag IN NUMBER default 1 ,
484 p_parallelism_degree IN NUMBER default 4)
485 IS
486
487 x_errbuf VARCHAR2(2000) := NULL;
488 x_retcode VARCHAR2(20) := NULL;
489 BEGIN
490 ASSIGN_PLAN_NAME_INT (x_errbuf,x_retcode,NewPlanName,DataProfileName,ArchiveFlag,p_parallelism_degree);
491 retcode := x_retcode;
492 errbuf:= x_errbuf;
493
494 EXCEPTION
495 WHEN OTHERS THEN
496 retcode := -1;
497 RETURN;
498 END ASSIGN_PLAN_NAME_TO_FORECAST_C;
499
500
501 /*
502 * Procedure Name - PUSH_ODS_DATA
503 * This procedure is made use of in the workflow- Export OBI Data to push
504 * demantra ODS data to APCC table- msc_demantra_ods_f
505 * It is a wrapper to APCC procedure - msc_phub_pkg.populate_demantra_ods.
506 */
507
508 PROCEDURE PUSH_ODS_DATA
509 IS
510 /* For Planning Hub */
511 x_errbuf VARCHAR2(1000) := NULL;
512 x_retcode VARCHAR2(1000) := NULL;
513 x_small_sql VARCHAR2(200) := NULL;
514 x_schema VARCHAR2(30) := NULL;
515
516 BEGIN
517 /* Alter session to APPS */
518 x_schema := msd_dem_demantra_utilities.get_demantra_schema;
519 x_small_sql := 'alter session set current_schema = APPS';
520 EXECUTE IMMEDIATE x_small_sql;
521 msc_phub_pkg.populate_demantra_ods(x_errbuf,
522 x_retcode);
523 /* Alter session to demantra schema */
524 x_small_sql := 'alter session set current_schema = ' || x_schema;
525 EXECUTE IMMEDIATE x_small_sql;
526
527
528 IF (x_retcode <> '0')
529 THEN
530 msd_dem_demantra_utilities.log_message ('The procedure MSD_WS_DEM_RENAME_FORECAST.PUSH_ODS_DATA failed with the following error : '||x_errbuf);
531 END IF;
532 EXCEPTION
533 WHEN OTHERS THEN
534 /* Alter session to demantra schema */
535 x_small_sql := 'alter session set current_schema = ' || x_schema;
536 EXECUTE IMMEDIATE x_small_sql;
537 msd_dem_demantra_utilities.log_message ('The procedure MSD_WS_DEM_RENAME_FORECAST.PUSH_ODS_DATA failed with error : '||SQLCODE||' -ERROR- '||SQLERRM);
538 END PUSH_ODS_DATA;
539
540
541 /*
542 * Function Name - GET_PLAN_SCENARIO_MEMBER_ID
543 * Given the id of a supply plan in ASCP, this function gets the plan scenario member id
544 * from Demantra.
545 *
546 * Parameters -
547 * PlanId - ID of the supply plan from the table MSC_PLANS.
548 *
549 * Return Values -
550 * The procedure returns the plan scenario member ID in Demantra. If not found or in case
551 * of any error it returns -1.
552 *
553 */
554 FUNCTION GET_PLAN_SCENARIO_MEMBER_ID (
555 PlanId IN NUMBER )
556 RETURN NUMBER
557 IS
558
559 x_member_id NUMBER := NULL;
560
561 BEGIN
562
563 EXECUTE IMMEDIATE ' SELECT supply_plan_id '
564 || ' FROM ' || fnd_profile.value('MSD_DEM_SCHEMA') || '.supply_plan sp'
565 || ' WHERE sp.plan_id = ' || PlanId
566 INTO x_member_id;
567
568 RETURN x_member_id;
569
570 EXCEPTION
571 WHEN OTHERS THEN
572 RETURN -1;
573
574 END GET_PLAN_SCENARIO_MEMBER_ID;
575
576 /*
577 * Procedure Name - REFRESH_MVIEW
578 * Given the name of a materialized view, this procedure refreshed the mview
579 *
580 * Parameters -
581 * MviewName - Name of the materialized view
582 *
583 */
584 PROCEDURE REFRESH_MVIEW(
585 mviewname IN VARCHAR2)
586 IS
587
588 x_small_sql VARCHAR2(200) := NULL;
589 x_schema VARCHAR2(30) := NULL;
590
591 BEGIN
592
593 /* Alter session to APPS */
594 x_schema := apps.fnd_profile.VALUE('MSD_DEM_SCHEMA');
595 x_small_sql := 'alter session set current_schema = APPS';
596 EXECUTE IMMEDIATE x_small_sql;
597
598 /*Refresh the mview */
599 dbms_mview.refresh(upper(mviewname), 'C');
600
601 /* Alter session to demantra schema */
602 x_small_sql := 'alter session set current_schema = ' || x_schema;
603 EXECUTE IMMEDIATE x_small_sql;
604
605 EXCEPTION
606 WHEN others THEN
607 x_small_sql := 'alter session set current_schema = ' || x_schema;
608 EXECUTE IMMEDIATE x_small_sql;
609
610 END REFRESH_MVIEW;
611 /*
612 * Procedure Name - DROP_MVIEW
613 * Given the name of a materialized view, this procedure drops the mview
614 *
615 * Parameters -
616 * MviewName - Name of the materialized view
617 *
618 */
619 PROCEDURE DROP_MVIEW(
620 mviewname IN VARCHAR2)
621 IS
622
623 x_small_sql VARCHAR2(200) := NULL;
624 x_schema VARCHAR2(30) := NULL;
625 x_mview VARCHAR2(30) := NULL;
626 x_drop_mview_sql VARCHAR2(300) := NULL;
627
628 BEGIN
629
630 x_schema := apps.fnd_profile.VALUE('MSD_DEM_SCHEMA');
631
632 /*Check if the Materialized view exists*/
633 SELECT object_name into x_mview
634 FROM dba_objects
635 WHERE owner = x_schema
636 AND object_type = 'MATERIALIZED VIEW'
637 AND object_name = upper(mviewname)
638 ORDER BY created DESC;
639 /*If the meview is present, drop it*/
640 IF (x_mview IS NOT NULL)
641 THEN
642 x_drop_mview_sql := 'DROP MATERIALIZED VIEW '||x_schema||'.'||mviewname;
643 EXECUTE IMMEDIATE x_drop_mview_sql;
644 END IF;
645
646
647
648 EXCEPTION
649 WHEN others THEN
650 x_small_sql := 'alter session set current_schema = ' || x_schema;
651 EXECUTE IMMEDIATE x_small_sql;
652 END DROP_MVIEW;
653
654
655
656 /*
657 * Procedure Name - DELETE_PLAN
658 * This procedure deletes the given assigned plan name
659 *
660 * 1. If the plan_name is assocaited to supply plans and p_delete_assoc is set NO
661 * then ERROR out with message
662 *
663 * 2. If the plan_name is assocaited to supply plans and p_delete_assoc is set YES
664 * (OR)
665 * If the plan_name is assocaited NOT to any supply plan
666 * then delete all the data for the plan_name from
667 * msd_dp_scenarios, msd_dp_scenario_output_levels,
668 * msd_dp_scn_entries_denorm, msc_plan_schedules tables
669 *
670 */
671
672 PROCEDURE DELETE_PLAN(
673 errbuf OUT NOCOPY VARCHAR2,
674 retcode OUT NOCOPY VARCHAR2,
675 p_plan_scn_id IN NUMBER,
676 p_delete_assoc IN NUMBER )
677 IS
678
679 x_sql_stmt VARCHAR2(500) := NULL;
680 x_del_stmt VARCHAR2(500) := NULL;
681 x_count NUMBER := 0;
682 x_attached_flag NUMBER := 0;
683 x_scenario_id NUMBER := NULL;
684
685
686 BEGIN
687
688 msd_dem_common_utilities.log_debug ('Entering: msd_ws_dem_rename_forecast.delete_plan - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
689 msd_dem_common_utilities.log_debug (' ');
690
691 msd_dem_common_utilities.log_message (' ' || rpad('Plan Scenario Id', 30) || ' - ' || to_char(p_plan_scn_id));
692 msd_dem_common_utilities.log_message (' ' || rpad('Delete Associations', 30) || ' - ' || to_char(p_delete_assoc));
693 msd_dem_common_utilities.log_message (' ');
694
695 /* Get the scenario_id of given Plan scenario id */
696 x_sql_stmt := 'SELECT nvl(sum(scenario_id), 0) '
697 || ' FROM msd_dp_scenarios mds '
698 || ' WHERE associate_parameter = ''Assign Plan Name'' '
699 || ' AND mds.demand_plan_id = ' || MSD_DEM_UPLOAD_FORECAST.C_DEMAND_PLAN_ID
700 || ' AND mds.scenario_id = ' || p_plan_scn_id ;
701
702 EXECUTE IMMEDIATE x_sql_stmt INTO x_scenario_id ;
703
704 msd_dem_common_utilities.log_debug (x_sql_stmt);
705 msd_dem_common_utilities.log_debug (' ');
706
707 /* Check if the scenario_id is attached to any ascp plan */
708 x_sql_stmt := 'SELECT nvl(sum(1),0) FROM msc_plan_schedules WHERE input_schedule_id = ' || x_scenario_id ;
709
710 EXECUTE IMMEDIATE x_sql_stmt INTO x_attached_flag ;
711
712 msd_dem_common_utilities.log_debug (x_sql_stmt);
713 msd_dem_common_utilities.log_debug (' ');
714
715 msd_dem_common_utilities.log_debug ('***Variables***');
716 msd_dem_common_utilities.log_debug ('x_scenario_id - ' || x_scenario_id);
717 msd_dem_common_utilities.log_debug ('x_attached_flag - ' || x_attached_flag);
718 msd_dem_common_utilities.log_debug ('p_delete_assoc - ' || p_delete_assoc);
719 msd_dem_common_utilities.log_debug (' ');
720
721 IF (x_attached_flag <> 0 AND p_delete_assoc = C_NO) THEN -- scenario_id is attached, delete_assoc is NO
722
723 retcode := -1;
724 errbuf := 'There are associations for this plan, hence the plan cannot be deleted.';
725 msd_dem_common_utilities.log_message ('ERROR(1): msd_ws_dem_rename_forecast.delete_plan - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
726 msd_dem_common_utilities.log_message ('There are associations for this plan, hence the plan cannot be deleted.');
727 msd_dem_common_utilities.log_message ('If you still want to delete, rerun program with parameter "Delete Associations to Supply Plans" set to yes');
728 msd_dem_common_utilities.log_message (' ');
729
730 RETURN;
731
732 ELSIF (x_attached_flag = 0 OR (x_attached_flag <> 0 AND p_delete_assoc = C_YES) ) THEN -- scenario_id is NOT attached OR scenario_id is attached, delete_assoc is YES
733
734 /* msd_dp_scenarios */
735 x_del_stmt := 'DELETE FROM msd_dp_scenarios WHERE scenario_id = ' || x_scenario_id ;
736 EXECUTE IMMEDIATE x_del_stmt;
737 x_count := SQL%ROWCOUNT;
738 COMMIT;
739
740 msd_dem_common_utilities.log_debug (x_del_stmt);
741 msd_dem_common_utilities.log_debug ('Rows Deleted - ' || x_count );
742 msd_dem_common_utilities.log_debug (' ');
743
744 /* msd_dp_scenario_output_levels */
745 x_del_stmt := 'DELETE FROM msd_dp_scenario_output_levels WHERE scenario_id = ' || x_scenario_id ;
746 EXECUTE IMMEDIATE x_del_stmt;
747 x_count := SQL%ROWCOUNT;
748 COMMIT;
749
750 msd_dem_common_utilities.log_debug (x_del_stmt);
751 msd_dem_common_utilities.log_debug ('Rows Deleted - ' || x_count );
752 msd_dem_common_utilities.log_debug (' ');
753
754 /* msd_dp_scn_entries_denorm */
755 x_del_stmt := 'DELETE FROM msd_dp_scn_entries_denorm WHERE scenario_id = ' || x_scenario_id ;
756 EXECUTE IMMEDIATE x_del_stmt;
757 x_count := SQL%ROWCOUNT;
758 COMMIT;
759
760 msd_dem_common_utilities.log_debug (x_del_stmt);
761 msd_dem_common_utilities.log_debug ('Rows Deleted - ' || x_count );
762 msd_dem_common_utilities.log_debug (' ');
763
764
765 IF (x_attached_flag <> 0 AND p_delete_assoc = C_YES) THEN
766 /* msc_plan_schedules */
767 x_del_stmt := 'DELETE FROM msc_plan_schedules WHERE input_schedule_id = ' || x_scenario_id ;
768 EXECUTE IMMEDIATE x_del_stmt;
769 x_count := SQL%ROWCOUNT;
770 COMMIT;
771
772 msd_dem_common_utilities.log_debug (x_del_stmt);
773 msd_dem_common_utilities.log_debug ('Rows Deleted - ' || x_count );
774 msd_dem_common_utilities.log_debug (' ');
775 END IF;
776
777 END IF;
778
779 msd_dem_common_utilities.log_debug ('Exiting: msd_ws_dem_rename_forecast.delete_plan - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
780 msd_dem_common_utilities.log_debug (' ');
781
782
783 EXCEPTION
784 WHEN others THEN
785 errbuf := substr(SQLERRM,1,150);
786 retcode := -1 ;
787 msd_dem_common_utilities.log_message ('Exception: msd_ws_dem_rename_forecast.delete_plan - ' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
788 msd_dem_common_utilities.log_message (errbuf);
789 msd_dem_common_utilities.log_message (' ');
790 ROLLBACK;
791
792 END DELETE_PLAN;
793
794
795 END MSD_WS_DEM_RENAME_FORECAST;