[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_COPY_DP_FORECAST
Source
1 PACKAGE BODY MSC_CL_COPY_DP_FORECAST AS -- body
2 /* $Header: MSCDPCPB.pls 120.6.12010000.1 2009/08/28 18:00:20 schaudha noship $ */
3
4 -- Global variable Definition
5
6 v_aps_dblink VARCHAR2(100);
7 v_src_dblink VARCHAR2(100);
8 v_src_instance_id NUMBER;
9 v_rp_instance_id NUMBER;
10 v_instance_id NUMBER;
11 lv_random_number number;
12 v_last_update_date DATE;
13 v_last_updated_by NUMBER;
14 v_creation_date DATE;
15 v_created_by NUMBER;
16
17
18 PROCEDURE GET_SOURCE_INSTANCE_ID (SOURCE_ID_INDICATOR IN NUMBER,
19 DB_LINK IN VARCHAR,
20 SOURCE_INSTANCE_ID OUT NOCOPY NUMBER) IS
21
22 lv_src_sql_stmt varchar2(2000);
23 lv_sql_stmt VARCHAR2 (1000);
24 lv_src_cent_stmt VARCHAR2 (1000);
25 m2a_dblink varchar2(100);
26 lv_instance_id number;
27 lv_found_source NUMBER := 0;
28
29 TYPE CurTyp IS REF CURSOR; -- Cursor variable
30 c1 CurTyp;
31
32 BEGIN
33
34 lv_src_sql_stmt := 'SELECT m2a_dblink, instance_id
35 FROM msc_apps_instances' || DB_LINK || '
36 WHERE m2a_dblink IS NOT NULL';
37
38 OPEN c1 FOR lv_src_sql_stmt; -- open the REF cursor
39 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL stmt in GET_SOURCE_INSTANCE_ID is : ' ||lv_src_sql_stmt);
40 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The value of lv_found_source is : ' ||lv_found_source);
41 LOOP
42 FETCH c1 INTO
43 m2a_dblink,lv_instance_id;
44
45 EXIT WHEN c1%NOTFOUND;
46
47 BEGIN
48
49 lv_found_source := 0;
50 lv_sql_stmt :=
51 'SELECT 1 FROM MRP_AP_APPS_INSTANCES_ALL@' || m2a_dblink ||
52 ' WHERE ' ||
53 'RP_SOURCE_IND = '|| to_char(SOURCE_ID_INDICATOR);
54
55 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The formed SQL to get the source instance: ' ||
56 lv_sql_stmt);
57 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_found_source;
58
59 IF (lv_found_source = 1 ) THEN
60
61 SOURCE_INSTANCE_ID := lv_instance_id;
62 EXIT;
63
64 END IF;
65
66
67 EXCEPTION
68 WHEN OTHERS THEN
69 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, sqlerrm);
70 END;
71 END LOOP;
72
73
74 Begin
75 --- for self instance
76 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'For self instance. Value of lv_found_source is :'||lv_found_source);
77 IF (v_rp_instance_id =v_src_instance_id) THEN
78
79 lv_src_cent_stmt := 'SELECT instance_id
80 FROM msc_apps_instances' || DB_LINK || '
81 WHERE m2a_dblink IS NULL
82 AND a2m_dblink is NULL
83 AND INSTANCE_TYPE <> 3';
84
85 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL to get the source instance in case of centralised inst : ' ||lv_src_cent_stmt);
86 EXECUTE IMMEDIATE lv_src_cent_stmt INTO lv_instance_id;
87 SOURCE_INSTANCE_ID := lv_instance_id;
88 lv_found_source := 1;
89
90 END IF;
91 EXCEPTION
92 WHEN OTHERS THEN
93 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, sqlerrm);
94 END;
95
96 IF (lv_found_source = 0) THEN
97 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'No Common Source found for APS and RP servers');
98 SOURCE_INSTANCE_ID := -23453;
99 ELSE
100 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Source Instance ID : ' ||to_char (SOURCE_INSTANCE_ID));
101 END IF;
102
103 END GET_SOURCE_INSTANCE_ID;
104
105 PROCEDURE LAUNCH_MONITOR( ERRBUF OUT NOCOPY VARCHAR2,
106 RETCODE OUT NOCOPY NUMBER,
107 pINSTANCE_ID IN NUMBER,
108 pSource_instance_id IN NUMBER)
109
110 IS
111
112 lv_sql_stmt varchar2(8000);
113 v_aps_dblink1 varchar2(128);
114
115
116 BEGIN
117 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'APS Instance Id: '|| pINSTANCE_ID); -- Instance ID of the APS server
118 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Source Instance Id: '|| pSource_instance_id); -- Instance ID of the RP instance
119 -- Initialize Global Variables
120 v_src_instance_id := pSource_instance_id;
121 v_rp_instance_id := pINSTANCE_ID;
122
123 --derive original source instance id
124 BEGIN
125 SELECT DECODE(M2A_DBLINK,
126 NULL,'',
127 M2A_DBLINK) ,
128 LAST_UPDATE_DATE,
129 LAST_UPDATED_BY,
130 CREATION_DATE,
131 CREATED_BY
132 INTO v_src_dblink ,v_last_update_date ,v_last_updated_by,v_creation_date ,v_created_by
133 FROM MSC_APPS_INSTANCES
134 WHERE INSTANCE_ID=pSource_instance_id;
135
136 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'SRC DB Link- v_src_dblink is: '|| v_src_dblink );
137
138 EXCEPTION
139 WHEN NO_DATA_FOUND THEN
140 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,'Source Instance Not defined for instance_id:' || to_char(pSource_instance_id));
141 RETCODE := MSC_UTIL.G_ERROR;
142 ERRBUF := 'Source Instance Not defined for instance_id';
143 RETURN;
144 WHEN OTHERS THEN
145 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, SQLERRM);
146 RETCODE := MSC_UTIL.G_ERROR;
147 ERRBUF := SQLERRM;
148 RETURN;
149 END;
150
151 select dbms_random.random
152 into lv_random_number
153 from dual;
154
155 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,' Generated random number: ' || lv_random_number);
156 lv_sql_stmt := 'Update '
157 || ' mrp_ap_apps_instances_all'||'@'|| v_src_dblink
158 || ' set rp_source_ind = '|| to_char(lv_random_number)
159 || ' where instance_id = '|| pSource_instance_id ;
160
161 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'Update SQL stmt is: '|| lv_sql_stmt );
162 EXECUTE IMMEDIATE lv_sql_stmt;
163
164 --derive aps instance_id
165 BEGIN
166 SELECT DECODE(M2A_DBLINK,
167 NULL,'',
168 '@'||M2A_DBLINK)
169 INTO v_aps_dblink
170 FROM MSC_APPS_INSTANCES
171 WHERE INSTANCE_ID=pINSTANCE_ID;
172
173
174 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'APS DB Link- v_aps_dblink is: '|| v_aps_dblink);
175
176 EXCEPTION
177 WHEN NO_DATA_FOUND THEN
178 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,'APS Instance Not defined for instance_id:' || to_char(pINSTANCE_ID));
179 RETCODE := MSC_UTIL.G_ERROR;
180 ERRBUF := 'Aps Instance Not defined for instance_id';
181 RETURN;
182 WHEN OTHERS THEN
183 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, SQLERRM);
184 RETCODE := MSC_UTIL.G_ERROR;
185 ERRBUF := SQLERRM;
186 RETURN;
187 END;
188
189 GET_SOURCE_INSTANCE_ID(lv_random_number,v_aps_dblink ,v_instance_id);
190
191 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'v_instance_id : '|| v_instance_id );
192
193 -- Call the PROCEDURE to copy forecast data and other related tables
194 COPY_DP_SCENARIOS ;
195 COPY_DP_SCENARIO_REVISIONS;
196 COPY_DP_FORECAST;
197 COPY_DP_DEMAND_PLANS;
198 COPY_MSD_DP_SCENARIO_OP_LEVELS ;
199
200 EXCEPTION
201 WHEN NO_DATA_FOUND THEN
202 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,'Error in copy- No records found ' );
203 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, SQLERRM);
204 RETCODE := MSC_UTIL.G_ERROR;
205 ERRBUF := 'No data found';
206 RETURN;
207 WHEN OTHERS THEN
208 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, SQLERRM);
209 RETCODE := MSC_UTIL.G_ERROR;
210 ERRBUF := SQLERRM;
211 ROLLBACK;
212 RETURN;
213
214 Commit;
215
216 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS,'End of CP ');
217
218 END LAUNCH_MONITOR;
219
220
221 PROCEDURE COPY_DP_FORECAST IS
222
223 lv_sql_stmt VARCHAR2(2000);
224 lv_scenario_id NUMBER;
225 lv_creation_date date;
226
227
228 BEGIN
229
230 DELETE FROM MSD_DP_SCN_ENTRIES_DENORM
231 WHERE SR_INSTANCE_ID = v_src_instance_id;
232 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The number of rows deleted from MSD_DP_SCN_ENTRIES_DENORM '|| SQL%ROWCOUNT);
233
234 v_sql_stmt:=
235 'INSERT INTO MSD_DP_SCN_ENTRIES_DENORM ('
236 || 'DEMAND_PLAN_ID,'
237 || 'SCENARIO_ID,'
238 || 'DEMAND_ID,'
239 || 'BUCKET_TYPE,'
240 || 'START_TIME,'
241 || 'END_TIME,'
242 || 'QUANTITY,'
243 || 'SR_ORGANIZATION_ID,'
244 || 'SR_INSTANCE_ID,'
245 || 'SR_INVENTORY_ITEM_ID,'
246 || 'ERROR_TYPE,'
247 || 'FORECAST_ERROR,'
248 || 'INVENTORY_ITEM_ID,'
249 || 'SR_SHIP_TO_LOC_ID,'
250 || 'SR_CUSTOMER_ID,'
251 || 'SR_ZONE_ID,'
252 || 'PRIORITY,'
253 || 'DP_UOM_CODE,'
254 || 'ASCP_UOM_CODE,'
255 || 'DEMAND_CLASS,'
256 || 'UNIT_PRICE,'
257 || 'CREATION_DATE,'
258 || 'CREATED_BY,'
259 || 'LAST_UPDATE_LOGIN,'
260 || 'REQUEST_ID,'
261 || 'PROGRAM_APPLICATION_ID,'
262 || 'PROGRAM_ID,'
263 || 'PROGRAM_UPDATE_DATE'
264 || ')'
265 ||'SELECT '
266 || 'APS.DEMAND_PLAN_ID,'
267 || 'APS.SCENARIO_ID,'
268 || 'APS.DEMAND_ID,'
269 || 'APS.BUCKET_TYPE,'
270 || 'APS.START_TIME,'
271 || 'APS.END_TIME,'
272 || 'APS.QUANTITY,'
273 || 'APS.SR_ORGANIZATION_ID,'
274 || ':v_src_instance_id,'
275 || 'APS.SR_INVENTORY_ITEM_ID,'
276 || 'APS.ERROR_TYPE,'
277 || 'APS.FORECAST_ERROR,'
278 || 'RP_lid.INVENTORY_ITEM_ID,'
279 || 'APS.SR_SHIP_TO_LOC_ID,'
280 || 'APS.SR_CUSTOMER_ID,'
281 || 'APS.SR_ZONE_ID,'
282 || 'APS.PRIORITY,'
283 || 'APS.DP_UOM_CODE,'
284 || 'APS.ASCP_UOM_CODE,'
285 || 'APS.DEMAND_CLASS,'
286 || 'APS.UNIT_PRICE,'
287 || 'APS.CREATION_DATE,'
288 || 'APS.CREATED_BY,'
289 || 'APS.LAST_UPDATE_LOGIN,'
290 || 'APS.REQUEST_ID,'
291 || 'APS.PROGRAM_APPLICATION_ID,'
292 || 'APS.PROGRAM_ID,'
293 || 'APS.PROGRAM_UPDATE_DATE'
294 ||' FROM MSD_DP_SCN_ENTRIES_DENORM'|| v_aps_dblink || ' APS, msc_item_id_lid RP_lid'
295 ||' WHERE RP_lid.sr_inventory_item_id = APS.sr_inventory_item_id'
296 ||' AND APS.SR_INSTANCE_ID = '||v_instance_id
297 ||' AND RP_lid.sr_instance_id = '|| v_src_instance_id;
298
299 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL for Insert into denorm table is :- '||v_sql_stmt);
300
301 EXECUTE IMMEDIATE v_sql_stmt USING v_src_instance_id;
302
303 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Records inserted into MSD_DP_SCN_ENTRIES_DENORM is :- '||sql%rowcount);
304
305 Commit;
306
307 END COPY_DP_FORECAST;
308
309
310 PROCEDURE COPY_DP_SCENARIOS IS
311
312 BEGIN
313
314 Delete from MSD_DP_SCENARIOS;-- commit;
315 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The number of rows deleted from MSD_DP_SCENARIOS '|| SQL%ROWCOUNT);
316
317 v_sql_stmt:=
318 'INSERT INTO MSD_DP_SCENARIOS ('
319 || 'DEMAND_PLAN_ID,'
320 || 'SCENARIO_ID,'
321 || 'SCENARIO_NAME,'
322 || 'DESCRIPTION,'
323 || 'OUTPUT_PERIOD_TYPE,'
324 || 'HORIZON_START_DATE,'
325 || 'HORIZON_END_DATE,'
326 || 'FORECAST_DATE_USED,'
327 || 'FORECAST_BASED_ON,'
328 || 'LAST_UPDATE_DATE,'
329 || 'LAST_UPDATED_BY,'
330 || 'CREATION_DATE,'
331 || 'CREATED_BY,'
332 || 'LAST_UPDATE_LOGIN,'
333 || 'REQUEST_ID,'
334 || 'PROGRAM_APPLICATION_ID,'
335 || 'PROGRAM_ID,'
336 || 'PROGRAM_UPDATE_DATE,'
337 || 'ATTRIBUTE_CATEGORY,'
338 || 'ATTRIBUTE1,'
339 || 'ATTRIBUTE2,'
340 || 'ATTRIBUTE3,'
341 || 'ATTRIBUTE4,'
342 || 'ATTRIBUTE5,'
343 || 'ATTRIBUTE6,'
344 || 'ATTRIBUTE7,'
345 || 'ATTRIBUTE8,'
346 || 'ATTRIBUTE9,'
347 || 'ATTRIBUTE10,'
348 || 'ATTRIBUTE11,'
349 || 'ATTRIBUTE12,'
350 || 'ATTRIBUTE13,'
351 || 'ATTRIBUTE14,'
352 || 'ATTRIBUTE15,'
353 || 'SCENARIO_TYPE,'
354 || 'STATUS,'
355 || 'HISTORY_START_DATE,'
356 || 'HISTORY_END_DATE,'
357 || 'PUBLISH_FLAG,'
358 || 'ENABLE_FLAG,'
359 || 'PRICE_LIST_NAME,'
360 || 'LAST_REVISION,'
361 || 'PARAMETER_NAME,'
362 || 'CONSUME_FLAG,'
363 || 'ERROR_TYPE,'
364 || 'DMD_PRIORITY_SCENARIO_ID,'
365 || 'SC_TYPE,'
366 || 'ASSOCIATE_PARAMETER'
367 || ')'
368 || ' select '
369 || 'DEMAND_PLAN_ID,'
370 || 'SCENARIO_ID,'
371 || 'SCENARIO_NAME,'
372 || 'DESCRIPTION,'
373 || 'OUTPUT_PERIOD_TYPE,'
374 || 'HORIZON_START_DATE,'
375 || 'HORIZON_END_DATE,'
376 || 'FORECAST_DATE_USED,'
377 || 'FORECAST_BASED_ON,'
378 || 'LAST_UPDATE_DATE,'
379 || 'LAST_UPDATED_BY,'
380 || 'CREATION_DATE,'
381 || 'CREATED_BY,'
382 || 'LAST_UPDATE_LOGIN,'
383 || 'REQUEST_ID,'
384 || 'PROGRAM_APPLICATION_ID,'
385 || 'PROGRAM_ID,'
386 || 'PROGRAM_UPDATE_DATE,'
387 || 'ATTRIBUTE_CATEGORY,'
388 || 'ATTRIBUTE1,'
389 || 'ATTRIBUTE2,'
390 || 'ATTRIBUTE3,'
391 || 'ATTRIBUTE4,'
392 || 'ATTRIBUTE5,'
393 || 'ATTRIBUTE6,'
394 || 'ATTRIBUTE7,'
395 || 'ATTRIBUTE8,'
396 || 'ATTRIBUTE9,'
397 || 'ATTRIBUTE10,'
398 || 'ATTRIBUTE11,'
399 || 'ATTRIBUTE12,'
400 || 'ATTRIBUTE13,'
401 || 'ATTRIBUTE14,'
402 || 'ATTRIBUTE15,'
403 || 'SCENARIO_TYPE,'
404 || 'STATUS,'
405 || 'HISTORY_START_DATE,'
406 || 'HISTORY_END_DATE,'
407 || 'PUBLISH_FLAG,'
408 || 'ENABLE_FLAG,'
409 || 'PRICE_LIST_NAME,'
410 || 'LAST_REVISION,'
411 || 'PARAMETER_NAME,'
412 || 'CONSUME_FLAG,'
413 || 'ERROR_TYPE,'
414 || 'DMD_PRIORITY_SCENARIO_ID,'
415 || 'SC_TYPE,'
416 || 'ASSOCIATE_PARAMETER'
417 || ' from MSD_DP_SCENARIOS'|| v_aps_dblink;
418
419 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL for Insert into MSD_DP_SCENARIOS table is :- '||v_sql_stmt);
420 EXECUTE IMMEDIATE v_sql_stmt;
421 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Records inserted into MSD_DP_SCENARIOS is :- '||sql%rowcount);
422
423 Commit;
424
425 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Copy MSD_DP_SCENARIOS for Demantra' );
426 v_sql_stmt:=
427 'INSERT INTO MSD_DP_SCENARIOS ('
428 || 'DEMAND_PLAN_ID,'
429 || 'SCENARIO_ID,'
430 || 'SCENARIO_NAME,'
431 || 'LAST_REVISION,'
432 || 'CONSUME_FLAG,'
433 || 'LAST_UPDATE_DATE,'
434 || 'LAST_UPDATED_BY,'
435 || 'CREATION_DATE,'
436 || 'CREATED_BY'
437 || ')'
438 || ' select '
439 || 'DEMAND_PLAN_ID,'
440 || 'SCENARIO_ID,'
441 || 'SCENARIO_NAME,'
442 || 'LAST_REVISION,'
443 || 'CONSUME_FLAG,'
444 || ':v_last_update_date,'
445 || ':v_last_updated_by ,'
446 || ':v_creation_date ,'
447 || ':v_created_by '
448 || ' from msd_dp_ascp_scenarios_v'|| v_aps_dblink || ' APS'
449 || ' where DEMAND_PLAN_ID = ' ||5555555
450 || ' and not exists (select 1 from MSD_DP_SCENARIOS RP '
451 ||' where RP.scenario_id = APS.SCENARIO_ID)';
452
453 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL for Demantra insert into MSD_DP_SCENARIOS table is :- '||v_sql_stmt);
454 EXECUTE IMMEDIATE v_sql_stmt USING v_last_update_date,v_last_updated_by,v_creation_date,v_created_by;
455 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Records inserted into MSD_DP_SCENARIOS for demantra is :- '||sql%rowcount);
456
457 Commit;
458
459 END COPY_DP_SCENARIOS;
460
461 PROCEDURE COPY_DP_SCENARIO_REVISIONS IS
462
463 BEGIN
464
465 Delete from MSD_DP_SCENARIO_REVISIONS; --commit;
466 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The number of rows deleted from MSD_DP_SCENARIO_REVISIONS '|| SQL%ROWCOUNT);
467
468 v_sql_stmt:=
469 'Insert into MSD_DP_SCENARIO_REVISIONS ('
470 || 'DEMAND_PLAN_ID,'
471 || 'SCENARIO_ID,'
472 || 'REVISION,'
473 || 'REVISION_NAME,'
474 || 'LAST_UPDATE_DATE,'
475 || 'LAST_UPDATED_BY,'
476 || 'CREATION_DATE,'
477 || 'CREATED_BY,'
481 || 'select '
478 || 'LAST_UPDATE_LOGIN,'
479 || 'ERROR_TYPE'
480 || ')'
482 || 'DEMAND_PLAN_ID,'
483 || 'SCENARIO_ID,'
484 || 'REVISION,'
485 || 'REVISION_NAME,'
486 || 'LAST_UPDATE_DATE,'
487 || 'LAST_UPDATED_BY,'
488 || 'CREATION_DATE,'
489 || 'CREATED_BY,'
490 || 'LAST_UPDATE_LOGIN,'
491 || 'ERROR_TYPE'
492 || ' from MSD_DP_SCENARIO_REVISIONS'|| v_aps_dblink;
493
494 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL for Insert into MSD_DP_SCENARIO_REVISIONS table is :- '||v_sql_stmt);
495 EXECUTE IMMEDIATE v_sql_stmt ;
496 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Records inserted into MSD_DP_SCENARIO_REVISIONS is :- '||sql%rowcount);
497
498 EXCEPTION
499 WHEN NO_DATA_FOUND THEN
500 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,'No records in MSD_DP_SCENARIO_REVISIONS ' );
501 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, SQLERRM);
502 --RETCODE := MSC_UTIL.G_ERROR;
503 --ERRBUF := 'No data found';
504 RETURN;
505 WHEN OTHERS THEN
506 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, SQLERRM);
507 -- RETCODE := MSC_UTIL.G_ERROR;
508 -- ERRBUF := SQLERRM;
509 ROLLBACK;
510 RETURN;
511
512 END COPY_DP_SCENARIO_REVISIONS;
513
514 PROCEDURE COPY_DP_DEMAND_PLANS IS
515
516 BEGIN
517
518 Delete from MSD_DEMAND_PLANS; -- commit;
519 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The number of rows deleted from MSD_DEMAND_PLANS '|| SQL%ROWCOUNT);
520 v_sql_stmt:=
521 'Insert into MSD_DEMAND_PLANS ('
522 || 'DEMAND_PLAN_ID,'
523 || 'ORGANIZATION_ID,'
524 || 'SR_INSTANCE_ID,'
525 || 'DEMAND_PLAN_NAME,'
526 || 'DESCRIPTION,'
527 || 'CALENDAR_TYPE,'
528 || 'CALENDAR_CODE,'
529 || 'PERIOD_SET_NAME,'
530 || 'BASE_UOM,'
531 || 'AVERAGE_DISCOUNT,'
532 || 'CATEGORY_SET_ID,'
533 || 'LOWEST_PERIOD_TYPE,'
534 || 'HISTORY_START_DATE,'
535 || 'VALID_FLAG,'
536 || 'ENABLE_FCST_EXPLOSION,'
537 || 'DELETE_PLAN_FLAG,'
538 || 'ROUNDOFF_THREASHOLD,'
539 || 'ROUNDOFF_DECIMAL_PLACES,'
540 || 'LAST_UPDATE_DATE,'
541 || 'LAST_UPDATED_BY,'
542 || 'CREATION_DATE,'
543 || 'CREATED_BY,'
544 || 'LAST_UPDATE_LOGIN,'
545 || 'REQUEST_ID,'
546 || 'PROGRAM_APPLICATION_ID,'
547 || 'PROGRAM_ID,'
548 || 'PROGRAM_UPDATE_DATE,'
549 || 'ATTRIBUTE_CATEGORY,'
550 || 'ATTRIBUTE1,'
551 || 'ATTRIBUTE2,'
552 || 'ATTRIBUTE3,'
553 || 'ATTRIBUTE4,'
554 || 'ATTRIBUTE5,'
555 || 'ATTRIBUTE6,'
556 || 'ATTRIBUTE7,'
557 || 'ATTRIBUTE8,'
558 || 'ATTRIBUTE9,'
559 || 'ATTRIBUTE10,'
560 || 'ATTRIBUTE11,'
561 || 'ATTRIBUTE12,'
562 || 'ATTRIBUTE13,'
563 || 'ATTRIBUTE14,'
564 || 'ATTRIBUTE15,'
565 || 'AMT_THRESHOLD,'
566 || 'AMT_DECIMAL_PLACES,'
567 || 'DP_BUILD_ERROR_FLAG,'
568 || 'DP_BUILD_REFRESH_NUM,'
569 || 'G_MIN_TIM_LVL_ID,'
570 || 'M_MIN_TIM_LVL_ID,'
571 || 'F_MIN_TIM_LVL_ID,'
572 || 'C_MIN_TIM_LVL_ID,'
573 || 'STRIPE_INSTANCE,'
574 || 'STRIPE_LEVEL_ID,'
575 || 'STRIPE_SR_LEVEL_PK,'
576 || 'BUILD_STRIPE_LEVEL_PK,'
577 || 'ROUNDING_LEVEL_ID,'
578 || 'STRIPE_STREAM_NAME,'
579 || 'STRIPE_STREAM_DESIG,'
580 || 'BUILD_STRIPE_STREAM_NAME,'
581 || 'BUILD_STRIPE_STREAM_DESIG,'
582 || 'BUILD_STRIPE_STREAM_REF_NUM,'
583 || 'USE_ORG_SPECIFIC_BOM_FLAG,'
584 || 'DELETE_REQUEST_ID'
585 || ')'
586 || ' select '
587 || 'DEMAND_PLAN_ID,'
588 || 'ORGANIZATION_ID,'
589 || ':v_src_instance_id,'
590 || 'DEMAND_PLAN_NAME,'
591 || 'DESCRIPTION,'
592 || 'CALENDAR_TYPE,'
593 || 'CALENDAR_CODE,'
594 || 'PERIOD_SET_NAME,'
595 || 'BASE_UOM,'
596 || 'AVERAGE_DISCOUNT,'
597 || 'CATEGORY_SET_ID,'
598 || 'LOWEST_PERIOD_TYPE,'
599 || 'HISTORY_START_DATE,'
600 || 'VALID_FLAG,'
601 || 'ENABLE_FCST_EXPLOSION,'
602 || 'DELETE_PLAN_FLAG,'
603 || 'ROUNDOFF_THREASHOLD,'
604 || 'ROUNDOFF_DECIMAL_PLACES,'
605 || 'LAST_UPDATE_DATE,'
606 || 'LAST_UPDATED_BY,'
607 || 'CREATION_DATE,'
608 || 'CREATED_BY,'
609 || 'LAST_UPDATE_LOGIN,'
610 || 'REQUEST_ID,'
611 || 'PROGRAM_APPLICATION_ID,'
612 || 'PROGRAM_ID,'
613 || 'PROGRAM_UPDATE_DATE,'
614 || 'ATTRIBUTE_CATEGORY,'
615 || 'ATTRIBUTE1,'
616 || 'ATTRIBUTE2,'
617 || 'ATTRIBUTE3,'
618 || 'ATTRIBUTE4,'
619 || 'ATTRIBUTE5,'
620 || 'ATTRIBUTE6,'
621 || 'ATTRIBUTE7,'
622 || 'ATTRIBUTE8,'
623 || 'ATTRIBUTE9,'
624 || 'ATTRIBUTE10,'
625 || 'ATTRIBUTE11,'
626 || 'ATTRIBUTE12,'
627 || 'ATTRIBUTE13,'
628 || 'ATTRIBUTE14,'
629 || 'ATTRIBUTE15,'
630 || 'AMT_THRESHOLD,'
631 || 'AMT_DECIMAL_PLACES,'
632 || 'DP_BUILD_ERROR_FLAG,'
633 || 'DP_BUILD_REFRESH_NUM,'
634 || 'G_MIN_TIM_LVL_ID,'
635 || 'M_MIN_TIM_LVL_ID,'
636 || 'F_MIN_TIM_LVL_ID,'
637 || 'C_MIN_TIM_LVL_ID,'
638 || 'STRIPE_INSTANCE,'
639 || 'STRIPE_LEVEL_ID,'
640 || 'STRIPE_SR_LEVEL_PK,'
641 || 'BUILD_STRIPE_LEVEL_PK,'
642 || 'ROUNDING_LEVEL_ID,'
643 || 'STRIPE_STREAM_NAME,'
644 || 'STRIPE_STREAM_DESIG,'
645 || 'BUILD_STRIPE_STREAM_NAME,'
646 || 'BUILD_STRIPE_STREAM_DESIG,'
650 || ' from MSD_DEMAND_PLANS'|| v_aps_dblink
647 || 'BUILD_STRIPE_STREAM_REF_NUM,'
648 || 'USE_ORG_SPECIFIC_BOM_FLAG,'
649 || 'DELETE_REQUEST_ID'
651 || ' where sr_instance_id = '|| v_instance_id;
652
653 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL for Insert into MSD_DEMAND_PLANS table is :- '||v_sql_stmt);
654 EXECUTE IMMEDIATE v_sql_stmt USING v_src_instance_id;
655 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Records inserted into MSD_DEMAND_PLANS is :- '||sql%rowcount);
656
657 Commit;
658
659 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Demantra Insert into MSD_DEMAND_PLANS table');
660 v_sql_stmt:=
661 'Insert into MSD_DEMAND_PLANS ('
662 || 'DEMAND_PLAN_ID,'
663 || 'DEMAND_PLAN_NAME,'
664 || 'ORGANIZATION_ID,'
665 || 'SR_INSTANCE_ID,'
666 || 'LAST_UPDATE_DATE,'
667 || 'LAST_UPDATED_BY,'
668 || 'CREATION_DATE,'
669 || 'CREATED_BY,'
670 || 'USE_ORG_SPECIFIC_BOM_FLAG'
671 || ')'
672 || 'Values (5555555,'
673 || '''Demantra Plan'','
674 || '''-23453'','
675 || ':v_src_instance_id,'
676 || ':v_last_update_date,'
677 || ':v_last_updated_by,'
678 || ':v_creation_date,'
679 || ':v_created_by,'
680 || 'NULL)';
681
682 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'SQL for Demantra Insert into MSD_DEMAND_PLANS table is :- '||v_sql_stmt);
683 EXECUTE IMMEDIATE v_sql_stmt USING v_src_instance_id,v_last_update_date,v_last_updated_by,v_creation_date,v_created_by;
684 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Records inserted for demantra into MSD_DEMAND_PLANS is :- '||sql%rowcount);
685
686 Commit;
687
688 END COPY_DP_DEMAND_PLANS;
689
690 PROCEDURE COPY_MSD_DP_SCENARIO_OP_LEVELS IS
691
692 BEGIN
693
694 Delete from MSD_DP_SCENARIO_OUTPUT_LEVELS;-- Commit;
695 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The number of rows deleted from MSD_DP_SCENARIO_OUTPUT_LEVELS '|| SQL%ROWCOUNT);
696
697 v_sql_stmt:=
698 'Insert into MSD_DP_SCENARIO_OUTPUT_LEVELS ('
699 || ' DEMAND_PLAN_ID, '
700 || ' SCENARIO_ID, '
701 || ' LEVEL_ID, '
702 || ' LAST_UPDATE_DATE, '
703 || ' LAST_UPDATED_BY, '
704 || ' CREATION_DATE, '
705 || ' CREATED_BY , '
706 || ' LAST_UPDATE_LOGIN, '
707 || ' REQUEST_ID, '
708 || ' PROGRAM_APPLICATION_ID, '
709 || ' PROGRAM_ID, '
710 || ' PROGRAM_UPDATE_DATE '
711 || ' )'
712 || ' Select '
713 || ' DEMAND_PLAN_ID, '
714 || ' SCENARIO_ID, '
715 || ' LEVEL_ID, '
716 || ' LAST_UPDATE_DATE,'
717 || ' LAST_UPDATED_BY,'
718 || ' CREATION_DATE,'
719 || ' CREATED_BY , '
720 || ' LAST_UPDATE_LOGIN, '
721 || ' REQUEST_ID, '
722 || ' PROGRAM_APPLICATION_ID,'
723 || ' PROGRAM_ID, '
724 || ' PROGRAM_UPDATE_DATE '
725 || ' from MSD_DP_SCENARIO_OUTPUT_LEVELS'|| v_aps_dblink;
726
727
728 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'SQL for insert into MSD_DP_SCENARIO_OUTPUT_LEVELS is :- '|| v_sql_stmt);
729 EXECUTE IMMEDIATE v_sql_stmt ;
730 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Records inserted into MSD_DP_SCENARIO_OUTPUT_LEVELS is :- '|| sql%rowcount);
731
732 Commit;
733
734 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Demantra insert into MSD_DP_SCENARIO_OUTPUT_LEVELS ');
735 v_sql_stmt:=
736 'Insert into MSD_DP_SCENARIO_OUTPUT_LEVELS ('
737 || ' DEMAND_PLAN_ID, '
738 || ' SCENARIO_ID, '
739 || ' LEVEL_ID, '
740 || ' LAST_UPDATE_DATE, '
741 || ' LAST_UPDATED_BY, '
742 || ' CREATION_DATE, '
743 || ' CREATED_BY , '
744 || ' LAST_UPDATE_LOGIN, '
745 || ' REQUEST_ID, '
746 || ' PROGRAM_APPLICATION_ID, '
747 || ' PROGRAM_ID, '
748 || ' PROGRAM_UPDATE_DATE '
749 || ' )'
750 || ' Select '
751 || ' DEMAND_PLAN_ID, '
752 || ' SCENARIO_ID, '
753 || ' LEVEL_ID, '
754 || ' :v_last_update_date,'
755 || ' :v_last_updated_by,'
756 || ' :v_creation_date,'
757 || ' :v_created_by,'
758 || ' LAST_UPDATE_LOGIN, '
759 || ' REQUEST_ID, '
760 || ' PROGRAM_APPLICATION_ID,'
761 || ' PROGRAM_ID, '
762 || ' PROGRAM_UPDATE_DATE '
763 || ' from msd_dp_scn_output_levels_v'|| v_aps_dblink
764 || ' where DEMAND_PLAN_ID = '|| 5555555;
765
766 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'SQL for Demantra insert into MSD_DP_SCENARIO_OUTPUT_LEVELS is :- '|| v_sql_stmt);
767 EXECUTE IMMEDIATE v_sql_stmt USING v_last_update_date,v_last_updated_by,v_creation_date,v_created_by;
768 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Records inserted for demantra into MSD_DP_SCENARIO_OUTPUT_LEVELS is :- '|| sql%rowcount);
769
770 Commit;
771
772 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Demantra insert into MSD_DP_SCENARIO_OUTPUT_LEVELS for level_id 7 ');
773 v_sql_stmt:=
774 'Insert into MSD_DP_SCENARIO_OUTPUT_LEVELS ('
775 || ' DEMAND_PLAN_ID, '
776 || ' SCENARIO_ID, '
777 || ' LEVEL_ID, '
778 || ' LAST_UPDATE_DATE, '
779 || ' LAST_UPDATED_BY, '
780 || ' CREATION_DATE, '
781 || ' CREATED_BY '
782 || ' )'
783 || ' Select '
784 || ' DEMAND_PLAN_ID, '
785 || ' SCENARIO_ID, '
786 || ' 7, '
787 || ' :v_last_update_date,'
788 || ' :v_last_updated_by,'
789 || ' :v_creation_date,'
790 || ' :v_created_by'
791 || ' from msd_dp_ascp_scenarios_v'|| v_aps_dblink ||' APS'
792 || ' where global_scenario_flag = ''N'''
793 || ' and DEMAND_PLAN_ID = '|| 5555555
794 || ' and not exists (select 1 from MSD_DP_SCENARIO_OUTPUT_LEVELS RP '
795 ||' where RP.scenario_id = APS.SCENARIO_ID'
796 ||' and RP.DEMAND_PLAN_ID = APS.DEMAND_PLAN_ID'
797 ||' and RP.level_id = 7)';
798
799 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'SQL for level_id Insert is :- '|| v_sql_stmt);
800 EXECUTE IMMEDIATE v_sql_stmt USING v_last_update_date,v_last_updated_by,v_creation_date,v_created_by;
801 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'SQL for level_id Insert is :- '|| sql%rowcount);
805
802
803 Commit;
804 END COPY_MSD_DP_SCENARIO_OP_LEVELS;
806 END MSC_CL_COPY_DP_FORECAST;