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