[Home] [Help]
PACKAGE BODY: APPS.MSC_MANAGE_PLAN_PARTITIONS
Source
1 PACKAGE BODY MSC_MANAGE_PLAN_PARTITIONS AS
2 /* $Header: MSCPRPRB.pls 120.11.12010000.2 2008/09/08 19:07:54 cmuntean ship $ */
3
4
5 TYPE table_list IS TABLE of varchar2(300);
6 -- following list contain all the partitioned tables.
7 -- tables having only inst part (and not plan part) should be added at the begining of the list
8
9 partitioned_tables table_list := table_list('SUPPLIES',
10 'ROUTING_OPERATIONS',
11 'RESOURCE_REQUIREMENTS',
12 'JOB_OPERATION_NETWORKS',
13 'JOB_OPERATIONS',
14 'JOB_OP_RESOURCES',
15 'JOB_REQUIREMENT_OPS',
16 'RESOURCE_INSTANCE_REQS', /* ds_plan: change */
17 'JOB_OP_RES_INSTANCES',
18 'NET_RES_INST_AVAIL', /* ds_plan: change */
19 'NET_RESOURCE_AVAIL',
20 'DEMANDS',
21 'SYSTEM_ITEMS',
22 'BOMS',
23 'BOM_COMPONENTS',
24 'ROUTINGS',
25 'OPERATION_RESOURCE_SEQS',
26 'OPERATION_RESOURCES',
27 'ITEM_SUBSTITUTES',
28 'ITEM_CATEGORIES',
29 'SALES_ORDERS',
30 'ATP_SUMMARY_SO',
31 'ATP_SUMMARY_SD',
32 'ATP_SUMMARY_RES',
33 'ATP_SUMMARY_SUP',
34 'DELIVERY_DETAILS',
35 'REGIONS',
36 'REGION_LOCATIONS',
37 'ZONE_REGIONS',
38 'ALLOC_DEMANDS',
39 'ALLOC_SUPPLIES',
40 'FULL_PEGGING',
41 'PART_PEGGING',
42 'PART_SUPPLIES',
43 'PART_DEMANDS',
44 'ITEM_EXCEPTIONS',
45 'CRITICAL_PATHS',
46 -- 'EXC_DETAILS_ALL',
47 'EXCEPTION_DETAILS',
48 -- CTO ODR Simplified Pegging
49 'ATP_PEGGING',
50 'PQ_RESULTS', --pabram
51 'SINGLE_LVL_PEG', -- dsting
52 'SUPPLIER_REQUIREMENTS',
53 'SRP_ITEM_EXCEPTIONS'); -- dsting
54
55 -- updated partition_count CTO ODR Simplified Pegging
56 g_partition_count CONSTANT NUMBER := 43;
57 g_inst_partition_count CONSTANT NUMBER := 31;
58 MAXVALUE CONSTANT NUMBER := 999999;
59 PMAXVALUE CONSTANT NUMBER := 999999;
60 g_need_refresh_mv boolean := true;
61 --
62 -- private functions
63 --
64
65 -- -----------------------------------------------------
66 -- Checks the availability of healty partition of a plan
67 -- -----------------------------------------------------
68 FUNCTION check_partition_pvt(p_plan_id IN NUMBER) RETURN VARCHAR2 IS
69 l_partition_name VARCHAR2(100);
70 l_sql_stmt VARCHAR2(300);
71 l_cur_table VARCHAR2(100);
72
73 NON_EXISTING_PARTITION EXCEPTION;
74 pragma exception_init(NON_EXISTING_PARTITION, -02149);
75
76 BEGIN
77 FOR i IN 1..g_partition_count LOOP
78 l_cur_table := 'MSC_'|| partitioned_tables(i);
79 IF (l_cur_table NOT IN ('MSC_ITEM_CATEGORIES','MSC_SALES_ORDERS', 'MSC_ATP_SUMMARY_SO'
80 ,'MSC_DELIVERY_DETAILS','MSC_REGIONS','MSC_REGION_LOCATIONS','MSC_ZONE_REGIONS')) THEN
81 l_partition_name := partitioned_tables(i) || '_' || to_char(p_plan_id);
82 l_sql_stmt := 'Lock Table '||l_cur_table||' Partition ('||l_partition_name||') In Exclusive Mode Nowait';
83
84 msc_util.msc_debug(l_sql_stmt);
85
86 execute immediate l_sql_stmt;
87 END IF;
88 END LOOP;
89
90 RETURN FND_API.G_RET_STS_SUCCESS;
91
92 EXCEPTION
93 WHEN NON_EXISTING_PARTITION THEN
94 msc_util.msc_debug('Partition '||l_partition_name||'does not exist.');
95 RETURN FND_API.G_RET_STS_ERROR;
96 WHEN OTHERS THEN
97 l_sql_stmt := to_char(sqlcode) ||':'|| substr(sqlerrm,1,90);
98 msc_util.msc_debug(l_sql_stmt);
99 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
100 END check_partition_pvt;
101
102 --
103 -- this procedure cleans (truncate or drop) partitions
104 --
105 PROCEDURE clean_partition_pvt( P_plan_num IN NUMBER,
106 P_instance_num IN NUMBER,
107 p_is_plan IN NUMBER,
108 p_operation IN VARCHAR2,
109 x_return_status OUT NOCOPY VARCHAR2,
110 x_msg_data OUT NOCOPY VARCHAR2) IS
111
112 CURSOR C_SCHEMA IS
113 SELECT a.oracle_username
114 FROM FND_ORACLE_USERID a, FND_PRODUCT_INSTALLATIONS b
115 WHERE a.oracle_id = b.oracle_id
116 AND b.application_id = 724;
117
118 l_partition_name VARCHAR2(100);
119 l_applsys_schema VARCHAR2(100);
120 i NUMBER;
121 sql_stmt VARCHAR2(200);
122 cur_table VARCHAR2(100);
123 dummy1 VARCHAR2(50);
124 dummy2 VARCHAR2(50);
125 l_name VARCHAR2(10);
126
127 BEGIN
128
129 if (p_is_plan = SYS_YES) then
130 l_name := to_char(P_plan_num);
131 else
132 l_name := '_'|| to_char(P_instance_num);
133 end if;
134
135 --
136 -- get fnd schema name
137 --
138 if (FND_INSTALLATION.GET_APP_INFO('FND',dummy1,dummy2,l_applsys_schema) = FALSE) then
139 x_return_status := FND_API.G_RET_STS_ERROR;
140 fnd_message.set_name('MSC','MSC_PART_UNDEFINED_SCHEMA');
141 x_msg_data := fnd_message.get;
142 return;
143 end if;
144
145 --dbms_output.put_line('Schemas are:'||l_applsys_schema);
146
147
148 FOR i in 1..g_partition_count LOOP
149
150 cur_table := 'MSC_'|| partitioned_tables(i);
151 l_partition_name := partitioned_tables(i) || '_'
152 ||l_name ;
153
154 --
155 -- construct the partition statement
156 --
157 sql_stmt := 'alter table ' || cur_table || ' '
158 || p_operation || ' partition '
159 || l_partition_name;
160
161 -- dbms_output.put_line(sql_stmt);
162 -- execute immediate sql_stmt;
163 begin
164 ad_ddl.do_ddl(l_applsys_schema,'MSC',
165 ad_ddl.alter_table,sql_stmt,cur_table);
166 exception
167 --
168 -- ignore the drop errors
169 --
170 when others then
171 x_msg_data := to_char(sqlcode) ||':'|| substr(sqlerrm,1,90);
172 x_return_status := FND_API.G_RET_STS_SUCCESS;
173 end;
174
175
176 END LOOP;
177
178 x_return_status := FND_API.G_RET_STS_SUCCESS;
179
180 EXCEPTION
181 WHEN others THEN
182 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
183
184
185 END clean_partition_pvt;
186
187 --
188 -- this procedure creates the partitions for all the
189 -- partitioned tables
190 --
191 PROCEDURE create_partition_pvt (plan_num IN NUMBER,
192 instance_num IN NUMBER,
193 x_return_status OUT NOCOPY VARCHAR2,
194 x_msg_data OUT NOCOPY VARCHAR2)
195 IS
196
197 CURSOR C_SCHEMA IS
198 SELECT a.oracle_username
199 FROM FND_ORACLE_USERID a, FND_PRODUCT_INSTALLATIONS b
200 WHERE a.oracle_id = b.oracle_id
201 AND b.application_id = 724;
202
203
204 l_partition_name VARCHAR2(100);
205 l_applsys_schema VARCHAR2(100);
206 i NUMBER;
207 part_created NUMBER;
208 sql_stmt VARCHAR2(200);
209 cur_table VARCHAR2(100);
210 dummy1 VARCHAR2(50);
211 dummy2 VARCHAR2(50);
212 l_err_buf VARCHAR2(4000);
213 l_ret_code NUMBER;
214
215
216 BEGIN
217
218
219 --
220 -- get fnd schema name
221 --
222 if (FND_INSTALLATION.GET_APP_INFO('FND',dummy1,dummy2,l_applsys_schema) = FALSE) then
223 x_return_status := FND_API.G_RET_STS_ERROR;
224 fnd_message.set_name('MSC','MSC_PART_UNDEFINED_SCHEMA');
225 x_msg_data := fnd_message.get;
226 return;
227 end if;
228
229 -- dbms_output.put_line('Schemas are:'||l_applsys_schema);
230
231 --- first check if paritions for old plans in summary table exist or not. If not then
232 -- create them
233 MSC_POST_PRO.CREATE_PARTITIONS(l_err_buf, l_ret_code);
234 IF (l_ret_code <> 0) THEN
235 --- error occured during partition creation in summary tables
236 --- Since partitions are not created for lower plan IDs successfully, partition
237 --- for higher plan ID will not be created
238 x_msg_data := to_char(sqlcode) ||':'|| substr(sqlerrm,1,90);
239 x_return_status := FND_API.G_RET_STS_ERROR;
240 return;
241 END IF;
242
243 part_created := 1;
244
245 FOR i in 1..g_partition_count LOOP
246
247
248 cur_table := 'MSC_'|| partitioned_tables(i) ;
249 l_partition_name := partitioned_tables(i) || '_' || to_char(plan_num);
250
251 --
252 -- construct the partition statement
253 -- For non-collected tables partition by plan_id only
254 --
255 if (cur_table = 'MSC_FULL_PEGGING' OR cur_table = 'MSC_EXCEPTION_DETAILS'
256 OR cur_table = 'MSC_ITEM_EXCEPTIONS'
257 OR cur_table = 'MSC_CRITICAL_PATHS'
258 OR cur_table = 'MSC_PQ_RESULTS'
259 OR cur_table = 'MSC_SUPPLIER_REQUIREMENTS'
260 OR cur_table = 'MSC_SRP_ITEM_EXCEPTIONS'
261 OR cur_table = 'MSC_SINGLE_LVL_PEG'
262 OR cur_table = 'MSC_PART_PEGGING'
263 OR cur_table = 'MSC_PART_SUPPLIES'
264 OR cur_table = 'MSC_PART_DEMANDS'
265 ) THEN -- dsting
266
267 sql_stmt := 'alter table ' || cur_table || ' add partition '
268 || l_partition_name
269 || ' VALUES LESS THAN ('
270 || to_char(plan_num+1)
271 || ')';
272 else
273 sql_stmt := 'alter table ' || cur_table || ' add partition '
274 || l_partition_name
275 || ' VALUES LESS THAN ('
276 || to_char(plan_num)
277 || ','
278 || to_char(instance_num +1)
279 || ')';
280 end if;
281
282 --dbms_output.put_line(sql_stmt);
283 -- execute immediate sql_stmt;
284
285 if (cur_table NOT IN ('MSC_ITEM_CATEGORIES','MSC_SALES_ORDERS', 'MSC_ATP_SUMMARY_SO'
286 ,'MSC_DELIVERY_DETAILS','MSC_REGIONS','MSC_REGION_LOCATIONS','MSC_ZONE_REGIONS')) then
287 ad_ddl.do_ddl(l_applsys_schema,'MSC',
288 ad_ddl.alter_table,sql_stmt,cur_table);
289 end if;
290 part_created := part_created +1;
291
292 END LOOP;
293 --dbms_output.put_line('****returning success****'||x_msg_data);
294 x_return_status := FND_API.G_RET_STS_SUCCESS;
295
296 EXCEPTION
297 --
298 -- could not create partition
299 --
300 WHEN OTHERS THEN
301
302 -- dbms_output.put_line(sql_stmt || ' Error:'||to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
303 -- fnd_message.set_name('MSC','MSC_PART_CREATE_FAILED');
304 -- fnd_message.set_token('PARTITION',l_partition_name);
305 -- x_msg_data := fnd_message.get;
306 x_msg_data := to_char(sqlcode) ||':'|| substr(sqlerrm,1,90);
307 x_return_status := FND_API.G_RET_STS_ERROR;
308
309 --
310 -- if partitions were created partially then remove the ones
311 -- that were successful so that we can exit in a clean state
312 --
313 for i in 1..part_created - 1 LOOP
314 cur_table := 'MSC_'|| partitioned_tables(i);
315 l_partition_name := partitioned_tables(i) || '_'
316 || to_char(plan_num);
317
318 --
319 -- construct the partition statement
320 --
321 sql_stmt := 'alter table ' || cur_table || ' drop partition '
322 || l_partition_name;
323
324 -- dbms_output.put_line(sql_stmt);
325 -- execute immediate sql_stmt;
326 ad_ddl.do_ddl(l_applsys_schema,'MSC',
327 ad_ddl.alter_table,sql_stmt,cur_table);
328
329 END LOOP;
330
331
332 END create_partition_pvt;
333
334 --
335 -- this procedure creates the instance partitions for all the
336 -- partitioned tables
337 --
338
339 PROCEDURE create_inst_partitions_pvt(
340 p_instance_id IN NUMBER,
341 x_return_status OUT NOCOPY VARCHAR2,
342 x_msg_data OUT NOCOPY VARCHAR2)
343 IS
344
345 CURSOR C_SCHEMA IS
346 SELECT a.oracle_username
347 FROM FND_ORACLE_USERID a, FND_PRODUCT_INSTALLATIONS b
348 WHERE a.oracle_id = b.oracle_id
349 AND b.application_id = 724;
350
351 l_dummy_partition_name VARCHAR2(100);
352 l_partition_name VARCHAR2(100);
353 l_applsys_schema VARCHAR2(100);
354 i NUMBER;
355 part_created NUMBER;
356 sql_stmt VARCHAR2(200);
357 cur_table VARCHAR2(100);
358 dummy1 VARCHAR2(50);
359 dummy2 VARCHAR2(50);
360 l_err_buf VARCHAR2(4000);
361 l_ret_code NUMBER;
362
363
364 BEGIN
365
369 if (FND_INSTALLATION.GET_APP_INFO('FND',dummy1,dummy2,l_applsys_schema) = FALSE) then
366 --
367 -- get fnd schema name
368 --
370 x_return_status := FND_API.G_RET_STS_ERROR;
371 fnd_message.set_name('MSC','MSC_PART_UNDEFINED_SCHEMA');
372 x_msg_data := fnd_message.get;
373 return;
374 end if;
375
376 --dbms_output.put_line('Schemas are:'||l_applsys_schema);
377
378 --- first check if paritions for old plans in summary table exist or not. If not then
379 -- create them
380 MSC_POST_PRO.CREATE_PARTITIONS(l_err_buf, l_ret_code);
381 IF (l_ret_code <> 0) THEN
382 --- error occured during partition creation in summary tables
383 --- Since partitions are not created for lower instance IDs successfully, partition
384 --- for higher instnaces will not be created
385 x_msg_data := to_char(sqlcode) ||':'|| substr(sqlerrm,1,90);
386 x_return_status := FND_API.G_RET_STS_ERROR;
387 return;
388 END IF;
389
390
391 part_created := 1;
392
393 FOR i in 1..g_inst_partition_count LOOP
394
395 --msc_util.log_msg(MSC_UTIL.G_D_STATUS,'db0-'||partitioned_tables(i));
396 cur_table := 'MSC_'|| partitioned_tables(i) ;
397 l_dummy_partition_name := partitioned_tables(i) || '_0';
398 l_partition_name := partitioned_tables(i) || '__' || to_char(p_instance_id);
399
400 --
401 -- construct the partition statement
402 --
403 IF cur_table IN ('MSC_ITEM_CATEGORIES','MSC_SALES_ORDERS', 'MSC_ATP_SUMMARY_SO'
404 ,'MSC_DELIVERY_DETAILS','MSC_REGIONS','MSC_REGION_LOCATIONS','MSC_ZONE_REGIONS') then
405 sql_stmt := 'alter table ' || cur_table
406 || ' split partition '|| l_dummy_partition_name
407 || ' AT ( '|| to_char(p_instance_id+1)||')'
408 || ' INTO ( PARTITION '||l_partition_name||','
409 || ' PARTITION '||l_dummy_partition_name||')';
410 ELSE
411 sql_stmt := 'alter table ' || cur_table
412 || ' split partition '|| l_dummy_partition_name
413 || ' AT ( -1, '|| to_char(p_instance_id+1)||')'
414 || ' INTO ( PARTITION '||l_partition_name||','
415 || ' PARTITION '||l_dummy_partition_name||')';
416 END IF;
417
418 -- execute immediate sql_stmt;
419 -- msc_util.log_msg(MSC_UTIL.G_D_STATUS,'db1-'||sql_stmt);
420
421 ad_ddl.do_ddl(l_applsys_schema,'MSC',
422 ad_ddl.alter_table,sql_stmt,cur_table);
423 -- msc_util.log_msg(MSC_UTIL.G_D_STATUS,'db1-competed');
424 part_created := part_created +1;
425
426 END LOOP;
427 --dbms_output.put_line('****returning success****'||x_msg_data);
428
429 INSERT INTO MSC_INST_PARTITIONS (
430 INSTANCE_ID,
431 FREE_FLAG,
432 LAST_UPDATE_DATE,
433 LAST_UPDATED_BY,
434 CREATION_DATE,
435 CREATED_BY,
436 LAST_UPDATE_LOGIN
437 )
438 VALUES (
439 p_instance_id,
440 2, /* used partition */
441 sysdate,
442 FND_GLOBAL.USER_ID,
443 sysdate,
444 FND_GLOBAL.USER_ID,
445 FND_GLOBAL.LOGIN_ID
446 );
447
448 COMMIT;
449
450 x_return_status := FND_API.G_RET_STS_SUCCESS;
451
452 EXCEPTION
453 --
454 -- could not create partition
455 --
456 WHEN OTHERS THEN
457
458 -- dbms_output.put_line(sql_stmt || ' Error:'||to_char(sqlcode) ||':'|| substr(sqlerrm,1,90));
459 -- fnd_message.set_name('MSC','MSCFULL_PEGGINGCREATE_FAILED');
460 -- fnd_message.set_token('PARTITION',l_partition_name);
461 -- x_msg_data := fnd_message.get;
462 x_msg_data := to_char(sqlcode) ||':'|| substr(sqlerrm,1,90);
463 x_return_status := FND_API.G_RET_STS_ERROR;
464
465 --
466 -- if partitions were created partially then remove the ones
467 -- that were successful so that we can exit in a clean state
468 --
469 for i in 1..part_created - 1 LOOP
470 cur_table := 'MSC_'|| partitioned_tables(i);
471 l_partition_name := partitioned_tables(i) || '__'
472 || to_char(p_instance_id);
473
474 --
475 -- construct the partition statement
476 --
477 sql_stmt := 'alter table ' || cur_table || ' drop partition '
478 || l_partition_name;
479
480 -- dbms_output.put_line(sql_stmt);
481 -- execute immediate sql_stmt;
482 ad_ddl.do_ddl(l_applsys_schema,'MSC',
483 ad_ddl.alter_table,sql_stmt,cur_table);
484
485 END LOOP;
486
487
488 END create_inst_partitions_pvt;
489
490 --
491 -- public functions
492 --
493
494 --
495 -- Called by Create Plan UI. This procedure will identify if ther
496 -- is a free partition available in MSC_APPS_INSTANCES. If yes then
497 -- it returns the plan_id. Otherwise it create a new partition by
498 -- performing DDL on all the partitioned tables. It store the new
499 -- plan_id in MSC_PLAN_PARTITIONS, marks it as being used and returns it
500 -- to the calling UI
501
502 FUNCTION get_plan (p_plan_name IN VARCHAR2,
503 x_return_status OUT NOCOPY VARCHAR2,
504 x_msg_data OUT NOCOPY VARCHAR2) RETURN NUMBER IS
505
506 CURSOR C_FREE_PLAN IS
507 SELECT plan_id
511
508 FROM MSC_PLAN_PARTITIONS
509 WHERE free_flag = 1
510 FOR UPDATE;
512
513 CURSOR C_PLAN_COUNT IS
514 SELECT count(*)
515 FROM MSC_PLAN_PARTITIONS;
516
517 CURSOR C_MAX_PLAN IS
518 SELECT max(plan_id)
519 FROM MSC_PLAN_PARTITIONS;
520
521 l_plan_id NUMBER;
522 l_max_plan NUMBER;
523 l_plan_count NUMBER;
524 X_login_id NUMBER;
525 X_user_id NUMBER;
526 l_return_status VARCHAR2(1);
527 i NUMBER;
528 cur_table VARCHAR2(100);
529 l_partition_name VARCHAR2(100);
530 sql_stmt VARCHAR2(200);
531 share_partition VARCHAR2(1);
532
533 BEGIN
534
535 -- set return status
536
537 x_return_status := FND_API.G_RET_STS_SUCCESS;
538
539 --
540 -- check if plans are sharing partitions
541 --
542
543 share_partition := fnd_profile.value('MSC_SHARE_PARTITIONS');
544
545 if (share_partition = 'Y') then
546 SELECT MSC_PLANS_S.nextval
547 INTO l_plan_id
548 from dual;
549
550 return l_plan_id;
551 end if;
552
553 -- find free plans
554 OPEN C_FREE_PLAN;
555 LOOP
556 FETCH C_FREE_PLAN INTO l_plan_id;
557 if (C_FREE_PLAN%NOTFOUND) then
558
559 --
560 -- based on performance team's input, we donot want to create
561 -- partitions on the fly. Hence, return error. Partitions
562 -- should be created by the system dba because that involves
563 -- analyzing as well as recompiling db objects
564
565 x_return_status := FND_API.G_RET_STS_ERROR;
566 fnd_message.set_name('MSC','MSC_NO_FREE_PARTITION');
567 x_msg_data := fnd_message.get;
568 l_plan_id := -1; --return -1;
569 EXIT;
570 --
571 -- the code below is no longer needed
572 --
573 --
574 -- No free plans available. So create one.
575 --
576 /* CLOSE C_FREE_PLAN;
577
578 SELECT msc_plans_s.nextval
579 INTO l_plan_id
580 FROm dual;
581
582 --
583 -- now add a new partition to all the partitioned tables
584 --
585
586 create_partition_pvt(l_plan_id,MAXVALUE,x_return_status,x_msg_data);
587
588 --
589 -- if could not create partition then return error
590 --
591 if (x_return_status = FND_API.G_RET_STS_ERROR) then
592 l_plan_id := -1;
593 return l_plan_id;
594 end if;
595
596 X_user_id := to_number(FND_GLOBAL.User_Id);
597 X_Login_Id := to_number(FND_GLOBAL.Login_Id);
598
599 begin
600 INSERT INTO MSC_PLAN_PARTITIONS (
601 PLAN_ID,
602 PLAN_NAME,
603 FREE_FLAG,
604 PARTITION_NUMBER,
605 LAST_UPDATE_DATE,
606 LAST_UPDATED_BY,
607 CREATION_DATE,
608 CREATED_BY,
609 LAST_UPDATE_LOGIN
610 )
611 VALUES (
612 l_plan_id,
613 p_plan_name,
614 2,
615 l_plan_id,
616 sysdate,
617 X_User_Id,
618 sysdate,
619 X_User_Id,
620 X_Login_Id
621 );
622 exception
623 when others then
624 --
625 -- drop the partitions that were created in this run so
626 -- as to ensure a clean exit
627 --
628 clean_partition_pvt(l_plan_id,MAXVALUE,1,'drop',x_return_status, x_msg_data);
629 x_return_status := FND_API.G_RET_STS_ERROR;
630 x_msg_data := to_char(sqlcode) ||':'|| substr(sqlerrm,1,90);
631 l_plan_id := -1;
632 return l_plan_id;
633 end;*/
634 else
635 --
636 -- found a free plan. update it's name
637 --
638 -- dbms_output.put_line('Found a free plan'||p_plan_name);
639 BEGIN
640 if check_partition_pvt(l_plan_id) = FND_API.G_RET_STS_SUCCESS then
641 UPDATE MSC_PLAN_PARTITIONS
642 SET plan_name = p_plan_name,
643 free_flag = 2
644 WHERE plan_id = l_plan_id;
645 EXIT;
646 ELSE
647 UPDATE MSC_PLAN_PARTITIONS
648 SET plan_name = '*UNUSABLE*',
649 free_flag = 2
650 WHERE plan_id = l_plan_id;
651 END IF;
652 EXCEPTION
653 WHEN OTHERS THEN
654 x_return_status := FND_API.G_RET_STS_ERROR;
655 --fnd_message.set_name('MSC','MSC_PART_UPDATE_FAILED');
656 x_msg_data := to_char(sqlcode) ||':'|| substr(sqlerrm,1,90);
657 -- fnd_message.get;
658 l_plan_id := -1;
659 EXIT; --return l_plan_id;
663 CLOSE C_FREE_PLAN;
660 END;
661 END IF;
662 END LOOP;
664 RETURN l_plan_id;
665
666 EXCEPTION WHEN others THEN
667
668
669 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
670 l_plan_id := -1;
671 return l_plan_id;
672
673 END get_plan;
674
675
676 PROCEDURE create_inst_partition( errbuf OUT NOCOPY VARCHAR2,
677 retcode OUT NOCOPY NUMBER,
678 instance_count IN NUMBER) IS
679 x_return_status VARCHAR2(10);
680 i NUMBER;
681
682 BEGIN
683
684 --
685 -- create partition for each instance. Do not create partition
686 -- for plan_id = -1 because that would have been created by
687 -- adpatch during table creation time
688 --
689 FOR i in 1..instance_count LOOP
690
691 create_partition_pvt(-1,i,x_return_status,errbuf);
692
693 --
694 -- break if partition creation fails
695 --
696 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
697 exit;
698 end if;
699
700 INSERT INTO MSC_INST_PARTITIONS (
701 INSTANCE_ID,
702 FREE_FLAG,
703 LAST_UPDATE_DATE,
704 LAST_UPDATED_BY,
705 CREATION_DATE,
706 CREATED_BY,
707 LAST_UPDATE_LOGIN
708 )
709 VALUES (
710 i,
711 1, /* free partition */
712 sysdate,
713 1,
714 sysdate,
715 1,
716 1
717 );
718
719 commit;
720
721 END LOOP;
722
723 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
724 retcode := G_SUCCESS;
725 else
726 retcode := G_ERROR;
727 end if;
728
729 END create_inst_partition;
730
731
732 --
733 -- This function creates the partition for the data
734 -- collected from the instance P_instance_id. The plan_id is
735 -- defaulted to -1
736 --
737 FUNCTION get_instance ( x_return_status OUT NOCOPY VARCHAR2,
738 x_msg_data OUT NOCOPY VARCHAR2) RETURN NUMBER IS
739
740 CURSOR C_FREE_INSTANCE IS
741 SELECT instance_id
742 FROM MSC_INST_PARTITIONS
743 WHERE free_flag = 1;
744
745 sql_stmt VARCHAR2(200);
746 l_inst_id NUMBER:= NULL;
747
748 BEGIN
749 -- set return status
750 x_return_status := FND_API.G_RET_STS_SUCCESS;
751
752 -- find free instance
753 OPEN C_FREE_INSTANCE;
754 FETCH C_FREE_INSTANCE INTO l_inst_id;
755 CLOSE C_FREE_INSTANCE;
756
757 if l_inst_id IS NULL then
758
759 --
760 -- cannot create an instance partition dynamically.
761 -- the sysadmin will have to create them
762 --
763 fnd_message.set_name('MSC','MSC_NO_FREE_PARTITION');
764 x_return_status := FND_API.G_RET_STS_ERROR;
765 x_msg_data := fnd_message.get;
766 return -1;
767
768 /*
769 SELECT msc_apps_instances_s.nextval
770 INTO l_inst_id
771 FROM DUAL;
772
773 create_inst_partitions_pvt( l_inst_id,x_return_status,x_msg_data);
774
775 if x_return_status = FND_API.G_RET_STS_ERROR THEN
776 return -1;
777 end if;
778 */
779
780 else
781 null;
782 -- dbms_output.put_line('Found a free instance'||l_inst_id);
783 end if;
784
785 begin
786 UPDATE MSC_INST_PARTITIONS
787 SET free_flag = 2
788 WHERE instance_id = l_inst_id;
789
790 exception
791 when others then
792 x_return_status := FND_API.G_RET_STS_ERROR;
793 --fnd_message.set_name('MSC','MSC_PART_UPDATE_FAILED');
794 x_msg_data := to_char(sqlcode) ||':'|| substr(sqlerrm,1,90);
795 -- fnd_message.get;
796 l_inst_id := -1;
797 return l_inst_id;
798 end;
799
800 return l_inst_id;
801
802 EXCEPTION WHEN others THEN
803 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
804 l_inst_id := -1;
805 return l_inst_id;
806
807 END get_instance;
808 --
809 -- returns the partition name
810 --
811
812 PROCEDURE get_partition_name (P_plan_id IN NUMBER,
813 P_instance_id IN NUMBER,
814 P_table_name IN VARCHAR2,
815 P_is_plan IN NUMBER,
816 P_partition_name OUT NOCOPY VARCHAR2,
817 x_return_status OUT NOCOPY VARCHAR2,
818 x_msg_data OUT NOCOPY VARCHAR2) IS
819
820 i NUMBER;
821 cur_table_name VARCHAR2(40);
822 l_count NUMBER;
823 l_id NUMBER;
824
825 CURSOR Plan_exists_C(P_plan_id IN NUMBER) IS
826 SELECT count(*)
827 FROM MSC_PLAN_PARTITIONS
828 WHERE plan_id = P_plan_id;
829
830 CURSOR Instance_exists_C(P_instance_id IN NUMBER) IS
831 SELECT count(*)
832 FROM MSC_INST_PARTITIONS
833 WHERE instance_id = P_instance_id;
834
835 BEGIN
836 msc_util.msc_debug('P_table_name = '||P_table_name );
840 if (cur_table_name = P_table_name) then
837 for i in 1..g_partition_count LOOP
838
839 cur_table_name := 'MSC_' || partitioned_tables(i);
841
842
843 if (P_is_plan = SYS_YES) then
844 l_id := P_plan_id;
845 --
846 -- now see if the plan/instance id exists in msc_plan_partitions
847 --
848 OPEN Plan_exists_C(P_plan_id);
849 FETCH Plan_exists_C INTO l_count;
850 CLOSE Plan_exists_C;
851 else
852 l_id := P_instance_id;
853 OPEN Instance_exists_C(P_instance_id);
854 FETCH Instance_exists_C INTO l_count;
855 CLOSE Instance_exists_C;
856 end if;
857
858 if (l_count <> 1) then
859 x_return_status := FND_API.G_RET_STS_ERROR;
860 x_msg_data := to_char(l_id) || 'Id does not exist';
861 return;
862 end if;
863
864 --
865 -- syntax: tab_name__<instance_id> for instances
866 -- tab_name_<plan_id> for plans
867 --
868 if (P_is_plan = SYS_YES) then
869 P_partition_name := partitioned_tables(i) || '_' || to_char(P_plan_id);
870 else
871
872 P_partition_name := partitioned_tables(i) || '__' || to_char(P_instance_id);
873 end if;
874
875 x_return_status := FND_API.G_RET_STS_SUCCESS;
876 return;
877 end if;
878 END LOOP;
879
880 x_return_status := FND_API.G_RET_STS_ERROR;
881 x_msg_data := P_table_name || ' is not partitioned';
882 return;
883 END get_partition_name;
884
885 --
886 -- purges a partiton from all the partitioned tables
887 --
888 PROCEDURE purge_partition( P_plan_id IN NUMBER,
889 x_return_status OUT NOCOPY VARCHAR2,
890 x_msg_data OUT NOCOPY VARCHAR2) IS
891 BEGIN
892 clean_partition_pvt(P_plan_id,MAXVALUE,1,'truncate', x_return_status, x_msg_data);
893
894 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
895 begin
896
897 UPDATE MSC_PLAN_PARTITIONS
898 SET free_flag = 1
899 WHERE plan_id = P_plan_id;
900 exception
901 when others then
902 x_return_status := FND_API.G_RET_STS_ERROR;
903 fnd_message.set_name('MSC','MSC_PART_UPDATE_FAILED');
904 x_msg_data := fnd_message.get;
905 return;
906 end;
907
908 end if;
909
910 END purge_partition;
911
912 PROCEDURE create_force_partition (errbuf OUT NOCOPY varchar2,
913 retcode OUT NOCOPY number,
914 partition_num IN number,
915 plan IN NUMBER) IS
916 x_return_status VARCHAR2(10);
917 sql_stmt varchar2(100);
918
919 BEGIN
920 -- dbms_output.put_line('---creating partitions---');
921 if (plan = SYS_YES) then
922 create_partition_pvt(partition_num, MAXVALUE,x_return_status, errbuf);
923 else
924 create_partition_pvt(-1,partition_num,x_return_status, errbuf);
925 end if;
926
927 -- dbms_output.put_line('return status was '||x_return_status||' '||errbuf);
928
929 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
930 retcode := G_SUCCESS;
931 else
932 retcode := G_ERROR;
933 end if;
934
935 -- sql_stmt := 'alter table temp.xx add partition xx'|| to_char(partition_num) ||
936 -- ' values less than ('|| to_char(partition_num+1)||')' ;
937 -- dbms_output.put_line(sql_stmt);
938 -- execute immediate sql_stmt;
939
940
941 begin
942 if (plan = SYS_YES) then
943 INSERT INTO MSC_PLAN_PARTITIONS (
944 PLAN_ID,
945 PLAN_NAME,
946 FREE_FLAG,
947 PARTITION_NUMBER,
948 LAST_UPDATE_DATE,
949 LAST_UPDATED_BY,
950 CREATION_DATE,
951 CREATED_BY,
952 LAST_UPDATE_LOGIN
953 )
954 VALUES (
955 partition_num,
956 to_char(partition_num),
957 1, /* free partition */
958 partition_num,
959 sysdate,
960 1,
961 sysdate,
962 1,
963 1
964 );
965 else
966 INSERT INTO MSC_INST_PARTITIONS (
967 INSTANCE_ID,
968 FREE_FLAG,
969 LAST_UPDATE_DATE,
970 LAST_UPDATED_BY,
971 CREATION_DATE,
972 CREATED_BY,
973 LAST_UPDATE_LOGIN
974 )
975 VALUES (
976 partition_num,
977 1, /* free partition */
978 sysdate,
979 1,
980 sysdate,
981 1,
982 1
983 );
984 end if;
985 commit;
986
987 exception
988 when others then
989
990 --
991 -- drop the partitions that were created in this run so
992 -- as to ensure a clean exit
993 --
994 if (plan = SYS_YES) then
995 clean_partition_pvt(partition_num,MAXVALUE,1,'drop',x_return_status, errbuf);
996 else
997 clean_partition_pvt(-1,partition_num,2,'drop',x_return_status, errbuf);
998 end if;
999
1003 retcode := to_number(x_return_status);
1000 x_return_status := FND_API.G_RET_STS_ERROR;
1001 fnd_message.set_name('MSC','MSC_PART_INSERT_FAILED');
1002 errbuf := fnd_message.get;
1004 end;
1005 exception
1006 when others then
1007 errbuf := 'partition = '||to_char(partition_num) || ' ' ||
1008 to_char(sqlcode) ||':'|| substr(sqlerrm,1,90);
1009 retcode := G_ERROR;
1010
1011 END create_force_partition;
1012 PROCEDURE drop_force_partition (errbuf OUT NOCOPY varchar2,
1013 retcode OUT NOCOPY number,
1014 partition_num IN number,
1015 plan IN NUMBER) IS
1016 x_return_status VARCHAR2(10);
1017 sql_stmt varchar2(100);
1018
1019 BEGIN
1020 --
1021 -- Is it a plan partition or instance partition
1022 --
1023 if (plan = SYS_YES) then
1024 clean_partition_pvt(partition_num, MAXVALUE,1,'drop', x_return_status, errbuf);
1025 else
1026 clean_partition_pvt(-1,partition_num,2,'drop', x_return_status, errbuf);
1027 end if;
1028
1029 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1030 retcode := G_SUCCESS;
1031 else
1032 retcode := G_ERROR;
1033 end if;
1034
1035 -- sql_stmt := 'alter table temp.xx drop partition xx'|| to_char(partition_num) ;
1036 -- dbms_output.put_line(sql_stmt);
1037 -- execute immediate sql_stmt;
1038
1039 if (plan = SYS_YES) then
1040 DELETE FROM MSC_PLAN_PARTITIONS
1041 WHERE plan_id = partition_num;
1042 commit;
1043 else
1044 DELETE FROM MSC_INST_PARTITIONS
1045 WHERE instance_id = partition_num;
1046 commit;
1047 end if;
1048
1049 exception
1050 when others then
1051 errbuf := 'delete failed'||to_char(partition_num) || ' ' ||
1052 to_char(sqlcode) ||':'|| substr(sqlerrm,1,90);
1053 retcode := G_ERROR;
1054
1055 END drop_force_partition;
1056
1057 PROCEDURE create_exist_plan_partitions( errbuf OUT NOCOPY VARCHAR2,
1058 retcode OUT NOCOPY NUMBER) IS
1059 CURSOR C_PLAN IS
1060 SELECT plan_id, compile_designator
1061 FROM MSC_PLANS
1062 WHERE plan_id <> -1
1063 ORDER BY plan_id;
1064
1065
1066 l_plan_id NUMBER;
1067 l_plan_name VARCHAR(100);
1068 x_return_status VARCHAR2(10) := FND_API.G_RET_STS_SUCCESS;
1069 share_partition VARCHAR2(1);
1070 plan_exists BOOLEAN := FALSE;
1071 i NUMBER;
1072 BEGIN
1073
1074 --
1075 -- check if plans are sharing partitions
1076 -- if yes then just create one partition and return
1077 --
1078 share_partition := fnd_profile.value('MSC_SHARE_PARTITIONS');
1079 -- dbms_output.put_line('share_partition := '||share_partition);
1080 if (share_partition = 'Y') then
1081 create_partition_pvt(PMAXVALUE,MAXVALUE,x_return_status,errbuf);
1082 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1083 retcode := G_SUCCESS;
1084 else
1085 retcode := G_ERROR;
1086 end if;
1087 return;
1088
1089 end if;
1090
1091
1092 OPEN C_PLAN;
1093 FETCH C_PLAN INTO l_plan_id,l_plan_name;
1094
1095 --
1096 -- create partition for each plan. Do not create partition
1097 -- for plan_id = -1 because that would have been created by
1098 -- adpatch during table creation time
1099 --
1100 LOOP
1101 if (C_PLAN%NOTFOUND) then
1102 exit;
1103 end if;
1104
1105 plan_exists := TRUE;
1106 create_partition_pvt(l_plan_id,MAXVALUE,x_return_status,errbuf);
1107
1108 --
1109 -- break if partition creation fails
1110 --
1111 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1112 exit;
1113 end if;
1114
1115 INSERT INTO MSC_PLAN_PARTITIONS (
1116 PLAN_ID,
1117 PLAN_NAME,
1118 FREE_FLAG,
1119 PARTITION_NUMBER,
1120 LAST_UPDATE_DATE,
1121 LAST_UPDATED_BY,
1122 CREATION_DATE,
1123 CREATED_BY,
1124 LAST_UPDATE_LOGIN
1125 )
1126 VALUES (
1127 l_plan_id,
1128 l_plan_name,
1129 2, /* used partition */
1130 l_plan_id,
1131 sysdate,
1132 1,
1133 sysdate,
1134 1,
1135 1
1136 );
1137
1138 commit;
1139
1140 FETCH C_PLAN INTO l_plan_id,l_plan_name;
1141
1142 END LOOP;
1143
1144 CLOSE C_PLAN;
1145
1146 --
1147 -- create 5 plans if none existed to begin with
1148 --
1149 if (plan_exists = FALSE) then
1150 FOR i in 1..5 LOOP
1151
1152 SELECT msc_plans_s.nextval
1153 INTO l_plan_id
1154 FROm dual;
1155 create_partition_pvt(l_plan_id,MAXVALUE,x_return_status,errbuf);
1156
1157 --
1158 -- break if partition creation fails
1159 --
1160 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1161 exit;
1162 end if;
1163
1164 INSERT INTO MSC_PLAN_PARTITIONS (
1165 PLAN_ID,
1166 PLAN_NAME,
1167 FREE_FLAG,
1168 PARTITION_NUMBER,
1169 LAST_UPDATE_DATE,
1170 LAST_UPDATED_BY,
1174 )
1171 CREATION_DATE,
1172 CREATED_BY,
1173 LAST_UPDATE_LOGIN
1175 VALUES (
1176 l_plan_id,
1177 to_char(l_plan_id),
1178 1, /* free partition */
1179 l_plan_id,
1180 sysdate,
1181 1,
1182 sysdate,
1183 1,
1184 1
1185 );
1186
1187 commit;
1188
1189 END LOOP;
1190 end if;
1191
1192 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1193 retcode := G_SUCCESS;
1194 else
1195 retcode := G_ERROR;
1196 end if;
1197
1198
1199 END create_exist_plan_partitions;
1200
1201
1202 PROCEDURE drop_exist_plan_partitions( errbuf OUT NOCOPY VARCHAR2,
1203 retcode OUT NOCOPY NUMBER) IS
1204 CURSOR C_PLAN IS
1205 SELECT plan_id
1206 FROM MSC_PLANS
1207 WHERE plan_id <> -1;
1208
1209 l_plan_id NUMBER;
1210 x_return_status VARCHAR2(10);
1211 BEGIN
1212
1213 OPEN C_PLAN;
1214 FETCH C_PLAN INTO l_plan_id;
1215
1216 --
1217 -- drop partition for each plan except for plan_id = -1
1218 --
1219 LOOP
1220 if (C_PLAN%NOTFOUND) then
1221 exit;
1222 end if;
1223
1224 --
1225 -- ignore the x_return_status since this is a forced drop
1226 -- so even if the partition drop failed because the partition
1227 -- never existed, that is OK.
1228 --
1229 clean_partition_pvt(l_plan_id,MAXVALUE,1,'drop',x_return_status,errbuf);
1230
1231
1232 FETCH C_PLAN INTO l_plan_id;
1233 END LOOP;
1234
1235 CLOSE C_PLAN;
1236
1237 DELETE FROM MSC_PLAN_PARTITIONS;
1238 retcode := G_SUCCESS;
1239
1240
1241 END drop_exist_plan_partitions;
1242
1243 PROCEDURE create_exist_inst_partitions( errbuf OUT NOCOPY VARCHAR2,
1244 retcode OUT NOCOPY NUMBER) IS
1245 CURSOR C_INSTANCE IS
1246 SELECT instance_id
1247 FROM MSC_apps_instances
1248 ORDER BY instance_id;
1249
1250 l_instance_id NUMBER;
1251 x_return_status VARCHAR2(10) := FND_API.G_RET_STS_SUCCESS;
1252 instance_exists BOOLEAN := FALSE;
1253 BEGIN
1254
1255 OPEN C_INSTANCE;
1256 FETCH C_INSTANCE INTO l_instance_id;
1257
1258 --
1259 -- create partition for each instance. Do not create partition
1260 -- for instance_id = -1 because that would have been created by
1261 -- adpatch during table creation time
1262 --
1263 LOOP
1264 if (C_INSTANCE%NOTFOUND) then
1265 exit;
1266 end if;
1267
1268 instance_exists := TRUE;
1269 -- dbms_output.put_line('instance_id ='||to_char(l_instance_id));
1270 create_inst_partitions_pvt(l_instance_id,x_return_status,errbuf);
1271
1272 --
1273 -- break if partition creation fails
1274 --
1275 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1276 exit;
1277 end if;
1278
1279 commit;
1280
1281 FETCH C_INSTANCE INTO l_instance_id;
1282
1283 END LOOP;
1284
1285 CLOSE C_INSTANCE;
1286
1287 --
1288 -- if there were no instances then create one
1289 -- instance partition as default
1290 --
1291 if (instance_exists = FALSE) then
1292
1293 SELECT msc_apps_instances_s.nextval
1294 INTO l_instance_id
1295 FROM DUAL;
1296
1297 create_inst_partitions_pvt(l_instance_id,x_return_status,errbuf);
1298
1299 --
1300 -- update msc_inst_partitions to mark the partition
1301 -- as free since the pvt api defaults it to used.
1302 --
1303 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1304 UPDATE MSC_INST_PARTITIONS
1305 SET free_flag = 1
1306 WHERE instance_id = l_instance_id;
1307 end if;
1308
1309 commit;
1310 end if;
1311
1312 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1313 retcode := G_SUCCESS;
1314 else
1315 retcode := G_ERROR;
1316 end if;
1317
1318
1319 END create_exist_inst_partitions;
1320
1321 FUNCTION get_partition_number ( errbuf OUT NOCOPY VARCHAR2,
1322 retcode OUT NOCOPY NUMBER,
1323 x_plan_id IN NUMBER) RETURN NUMBER IS
1324 part_number NUMBER;
1325 BEGIN
1326 SELECT partition_number
1327 INTO part_number
1328 FROM MSC_PLAN_PARTITIONS
1329 WHERE plan_id = x_plan_id;
1330
1331 retcode := G_SUCCESS;
1332 return part_number;
1333 exception
1334 when others then
1335 retcode := G_ERROR;
1336 errbuf := to_char(sqlcode) ||':'|| substr(sqlerrm,1,90);
1337 return -1;
1338 END get_partition_number;
1339
1340 --
1341 -- analyze a new partition
1342 -- This procedure also refreshes the ATP snapshot
1343 --
1344 PROCEDURE analyze_plan(errbuf OUT NOCOPY VARCHAR2,
1345 retcode OUT NOCOPY NUMBER,
1346 x_plan_id IN NUMBER) IS
1347 i NUMBER;
1348 l_partition_name VARCHAR2(51);
1349 cur_table VARCHAR2(51);
1353 v_snap_exist number;
1350 share_partition VARCHAR2(1);
1351 lv_msc_schema VARCHAR2(30);
1352 is_partitioned VARCHAR2(10);
1354 -- v_tree_exist number;
1355 v_analyze_plan_scope NUMBER := NVL(FND_PROFILE.VALUE('MSC_ANALYZE_PARTITION_SCOPE'),1);
1356
1357
1358 Cursor msc_schema IS
1359 SELECT a.oracle_username
1360 FROM FND_ORACLE_USERID a, FND_PRODUCT_INSTALLATIONS b
1361 WHERE a.oracle_id = b.oracle_id
1362 AND b.application_id= 724;
1363
1364 /* --bug 3274373: Refresh Materialized view in ATP post plan processing.
1365 Cursor atp_snap IS
1366 SELECT 1
1367 FROM all_objects
1368 WHERE object_name like 'MSC_ATP_PLAN_SN'
1369 AND owner = lv_msc_schema;
1370 */
1371 /*
1372 Cursor tree_snap IS
1373 SELECT 1
1374 FROM all_objects
1375 WHERE object_name = 'MSC_SUPPLIER_TREE_MV'
1376 AND owner = lv_msc_schema;
1377 */
1378
1379 BEGIN
1380 msc_util.msc_debug('starting atp snapshot refresh');
1381 retcode := G_SUCCESS;
1382
1383 OPEN msc_schema;
1384 FETCH msc_schema INTO lv_msc_schema;
1385 CLOSE msc_schema;
1386
1387
1388
1389 /* --bug 3274373: Refresh Materialized view in ATP post plan processing.
1390 OPEN atp_snap;
1391 FETCH atp_snap INTO v_snap_exist;
1392 CLOSE atp_snap;
1393 */
1394 /*
1395 OPEN tree_snap;
1396 FETCH tree_snap INTO v_tree_exist;
1397 CLOSE tree_snap;
1398 */
1399
1400 /* --bug 3274373: Refresh Materialized view in ATP post plan processing.
1401 --
1402 -- refresh the snapshot if it exists
1403 --
1404 if v_snap_exist =1 then
1405 MSC_UTIL.msc_debug('---- complete refresh of snapshot----');
1406 DBMS_SNAPSHOT.REFRESH( lv_msc_schema||'.MSC_ATP_PLAN_SN', 'C');
1407 end if;
1408
1409 */
1410
1411 SELECT partitioned
1412 INTO is_partitioned
1413 FROM dba_tables
1414 WHERE table_name = 'MSC_SUPPLIES'
1415 AND owner = lv_msc_schema;
1416
1417 --
1418 -- do not analyze partitions if the db is not partitioned
1419 --
1420 if (is_partitioned = 'YES' AND x_plan_id <> 0) then
1421
1422 msc_util.msc_debug('analyzing plan '||to_char(x_plan_id));
1423
1424 FOR i in 1..g_partition_count LOOP
1425
1426
1427 share_partition := fnd_profile.value('MSC_SHARE_PARTITIONS');
1428 if (share_partition= 'Y') then
1429 l_partition_name := partitioned_tables(i) || '_'
1430 || to_char(PMAXVALUE);
1431 else
1432 l_partition_name := partitioned_tables(i) || '_'
1433 ||to_char(x_plan_id);
1434 end if;
1435
1436 cur_table := 'MSC_'|| partitioned_tables(i);
1437 if (cur_table NOT IN ('MSC_ITEM_CATEGORIES','MSC_SALES_ORDERS', 'MSC_ATP_SUMMARY_SO',
1438 'MSC_ATP_SUMMARY_SD', 'MSC_ATP_SUMMARY_RES', 'MSC_ATP_SUMMARY_SUP',
1439 'MSC_ALLOC_SUPPLIES', 'MSC_ALLOC_DEMANDS',
1440 -- CTO ODR Simplified Pegging MSC_ATP_PEGGING not analyzed here
1441 'MSC_EXC_DETAILS_ALL', 'MSC_ATP_PEGGING'
1442 ,'MSC_DELIVERY_DETAILS','MSC_REGIONS','MSC_REGION_LOCATIONS','MSC_ZONE_REGIONS')) then
1443
1444 msc_util.msc_debug('analyzing partition '||l_partition_name);
1445 IF v_analyze_plan_scope = 2 THEN
1446 fnd_stats.gather_table_stats(ownname=>'MSC',tabname=>cur_table,
1447 partname=>l_partition_name,
1448 granularity=>'PARTITION',
1449 percent =>10,degree=>4);
1450 ELSE
1451 fnd_stats.gather_table_stats(ownname=>'MSC',tabname=>cur_table,
1452 partname=>l_partition_name,
1453 granularity=>'ALL',
1454 percent =>10,degree=>4);
1455 END IF;
1456 end if;
1457 END LOOP;
1458
1459 end if;
1460 /*
1461 if g_need_refresh_mv and x_plan_id <> 0 and x_plan_id <> PMAXVALUE then
1462
1463 declare
1464 p_out number;
1465 begin
1466 -- launch a concurrent program to refresh msc_supplier_tree_mv,
1467 -- populate kpi summary table and
1468 -- mark plan need to be recompared for plan comparison reports
1469 p_out := fnd_request.submit_request(
1470 'MSC',
1471 'MSC_UI_POST_PLAN',
1472 null,
1473 null,
1474 false,
1475 x_plan_id);
1476 commit;
1477 msc_util.msc_debug('launch UI Post Plan Program, request_id ='||p_out);
1478 end;
1479
1480 end if;
1481 */
1482 END analyze_plan;
1483
1484 --
1485 -- creates partitions
1486 --
1487 PROCEDURE create_partitions(errbuf OUT NOCOPY VARCHAR2,
1488 retcode OUT NOCOPY NUMBER,
1489 plan_partition_count IN NUMBER,
1490 inst_partition_count IN NUMBER) IS
1491
1492 l_plan_id NUMBER;
1493 l_inst_id NUMBER;
1494 X_login_id NUMBER;
1495 X_user_id NUMBER;
1496 x_return_status VARCHAR2(10);
1497 share_partition VARCHAR2(1);
1498
1499 BEGIN
1500
1501 retcode := G_SUCCESS;
1502 --
1503 -- check if plans are sharing partitions
1504 -- if yes then just create one partition and return
1505 --
1506 share_partition := fnd_profile.value('MSC_SHARE_PARTITIONS');
1507
1508 -- dbms_output.put_line('share partitions :='||share_partition);
1509 if (share_partition = 'Y' and plan_partition_count > 0) then
1510 create_partition_pvt(PMAXVALUE,MAXVALUE,x_return_status,errbuf);
1511 analyze_plan(errbuf,retcode,PMAXVALUE);
1512 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1513 retcode := G_SUCCESS;
1514 else
1515 retcode := G_ERROR;
1516 end if;
1517
1518 else
1519
1520 FOR i in 1..plan_partition_count LOOP
1521
1522 SELECT msc_plans_s.nextval
1523 INTO l_plan_id
1524 FROm dual;
1525
1526
1527 --
1528 -- now add a new partition to all the partitioned tables
1529 --
1530
1531 create_partition_pvt(l_plan_id,MAXVALUE,x_return_status,errbuf);
1532
1533 --
1534 -- if could not create partition then return error
1535 --
1536 if (x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1537 retcode := G_ERROR;
1538 return;
1539 else
1540 retcode := G_SUCCESS;
1541 end if;
1542
1543
1544
1545 X_user_id := to_number(FND_GLOBAL.User_Id);
1546 X_Login_Id := to_number(FND_GLOBAL.Login_Id);
1547
1548
1549 begin
1550 INSERT INTO MSC_PLAN_PARTITIONS (
1551 PLAN_ID,
1552 PLAN_NAME,
1553 FREE_FLAG,
1554 PARTITION_NUMBER,
1555 LAST_UPDATE_DATE,
1556 LAST_UPDATED_BY,
1557 CREATION_DATE,
1558 CREATED_BY,
1559 LAST_UPDATE_LOGIN
1560 )
1561 VALUES (
1562 l_plan_id,
1563 to_char(l_plan_id),
1564 1,
1565 l_plan_id,
1566 sysdate,
1567 X_User_Id,
1568 sysdate,
1569 X_User_Id,
1570 X_Login_Id
1571 );
1572 exception
1573 when others then
1574
1575 --
1576 -- drop the partitions that were created in this run so
1577 -- as to ensure a clean exit
1578 --
1579 clean_partition_pvt(l_plan_id,MAXVALUE,1,'drop',x_return_status, errbuf);
1580
1581 retcode := G_ERROR;
1582 errbuf := to_char(sqlcode) ||':'|| substr(sqlerrm,1,90);
1583 return ;
1584 end;
1585 --
1586 -- no need to analyze if no data
1587 --
1588 g_need_refresh_mv := false;
1589 analyze_plan(errbuf,retcode,l_plan_id);
1590 g_need_refresh_mv := true;
1591 END LOOP;
1592
1593 end if;
1594
1595 FOR i in 1..inst_partition_count LOOP
1596 SELECT msc_apps_instances_s.nextval
1597 INTO l_inst_id
1598 FROM DUAL;
1599
1600 create_inst_partitions_pvt( l_inst_id,x_return_status,errbuf);
1601
1602 if x_return_status = FND_API.G_RET_STS_ERROR THEN
1603 retcode := G_ERROR;
1604 return;
1605 end if;
1606 --
1607 -- update msc_inst_partitions to mark the partition
1608 -- as free since the pvt api defaults it to used.
1609 --
1610 if (x_return_status = FND_API.G_RET_STS_SUCCESS) then
1611 UPDATE MSC_INST_PARTITIONS
1612 SET free_flag = 1
1613 WHERE instance_id = l_inst_id;
1614 end if;
1615
1616 END LOOP;
1617
1618 commit;
1619 return;
1620 END create_partitions;
1621
1622 END MSC_MANAGE_PLAN_PARTITIONS;