[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_EXCHANGE_PARTTBL
Source
1 PACKAGE BODY MSC_CL_EXCHANGE_PARTTBL AS
2 /* $Header: MSCCLJAB.pls 120.19.12010000.2 2008/05/19 15:07:15 sbyerram ship $ */
3
4 v_is_initialized BOOLEAN:= FALSE; /* default to FALSE */
5
6 v_partTblList TblNmTblTyp:=
7 TblNmTblTyp( 'MSC_SYSTEM_ITEMS',
8 'MSC_RESOURCE_REQUIREMENTS',
9 'MSC_SUPPLIES',
10 'MSC_BOM_COMPONENTS',
11 'MSC_ITEM_CATEGORIES',
12 'MSC_ITEM_SUBSTITUTES',
13 'MSC_OPERATION_RESOURCES',
14 'MSC_OPERATION_RESOURCE_SEQS',
15 'MSC_ROUTING_OPERATIONS',
16 'MSC_DEMANDS',
17 'MSC_JOB_OPERATION_NETWORKS',
18 'MSC_JOB_OPERATIONS',
19 'MSC_JOB_REQUIREMENT_OPS',
20 'MSC_JOB_OP_RESOURCES',
21 'MSC_RESOURCE_INSTANCE_REQS', /* ds_plan: chaneg */
22 'MSC_JOB_OP_RES_INSTANCES', /* ds_plan: change */
23 'MSC_SALES_ORDERS'
24 );
25
26 /* concatenate the tempTblList with the instance_code to be the exact
27 temp table name */
28 v_tempTblList TblNmTblTyp:=
29 TblNmTblTyp( 'SYSTEM_ITEMS_',
30 'RESOURCE_REQUIREMENTS_',
31 'SUPPLIES_',
32 'BOM_COMPONENTS_',
33 'ITEM_CATEGORIES_',
34 'ITEM_SUBSTITUTES_',
35 'OPERATION_RESOURCES_',
36 'OPERATION_RESOURCE_SEQS_',
37 'ROUTING_OPERATIONS_',
38 'DEMANDS_',
39 'JOB_OPERATION_NETWORKS_',
40 'JOB_OPERATIONS_',
41 'JOB_REQUIREMENT_OPS_',
42 'JOB_OP_RESOURCES_',
43 'RESOURCE_INSTANCE_REQS_', /* ds_plan: change */
44 'JOB_OP_RES_INSTANCES_', /* ds_plan: change */
45 'SALES_ORDERS_'
46 );
47
48
49 v_is_TTL_initialized BOOLEAN:= FALSE; /* default to FALSE */
50
51 v_sql_stmt VARCHAR2(4000);
52 v_applsys_schema VARCHAR2(32);
53 v_msc_schema VARCHAR2(32);
54
55 v_instance_id NUMBER;
56 v_instance_code VARCHAR2(3);
57
58 v_100K VARCHAR2(20) := '102400';
59 v_1M VARCHAR2(20) := '1048576';
60 v_10M VARCHAR2(20) := '10485760';
61
62
63
64 /* ======== private functions ========= */
65 /* ======== Log Messages ========== */
66 PROCEDURE LOG_MESSAGE( pBUFF IN VARCHAR2)
67 IS
68 BEGIN
69 IF fnd_global.conc_request_id > 0 THEN
70 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
71 null;
72 ELSE
73 --DBMS_OUTPUT.PUT_LINE( pBUFF);
74 null;
75 END IF;
76 END LOG_MESSAGE;
77
78 PROCEDURE TRC( pBUFF IN VARCHAR2)
79 IS
80 BEGIN
81 IF fnd_global.conc_request_id > 0 THEN
82 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, '<trc '||TO_CHAR(SYSDATE,'HH24:MI:SS>')||pBUFF);
83 ELSE
84 --DBMS_OUTPUT.PUT_LINE( '<trc '||pBUFF);
85 null;
86 END IF;
87 END TRC;
88
89 /* =========== Initialize Schema ============= */
90 FUNCTION Initialize_Schema_PVT RETURN BOOLEAN
91 IS
92 lv_retval BOOLEAN;
93 lv_dummy1 VARCHAR2(32);
94 lv_dummy2 VARCHAR2(32);
95
96 CURSOR c_msc IS
97 SELECT a.oracle_username
98 FROM FND_ORACLE_USERID a,
99 FND_PRODUCT_INSTALLATIONS b
100 WHERE a.oracle_id = b.oracle_id
101 AND b.application_id= 724;
102
103 BEGIN
104 trc( 'st:Initialize_Schema_PVT');
105 /* APPLSYS */
106 lv_retval := FND_INSTALLATION.GET_APP_INFO(
107 'FND', lv_dummy1,lv_dummy2, v_applsys_schema);
108 /* MSC */
109 OPEN c_msc;
110 FETCH c_msc INTO v_msc_schema;
111 CLOSE c_msc;
112
113 trc( 'en:Initialize_Schema_PVT');
114 RETURN TRUE;
115
116 EXCEPTION
117 WHEN OTHERS THEN
118 IF c_msc%ISOPEN THEN CLOSE c_msc; END IF;
119
120 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<Initialize_Schema_PVT>>');
121 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
122 RETURN FALSE;
123 END Initialize_Schema_PVT;
124
125 /* =========== Initialize Temporary Table List ============= */
126 FUNCTION Initialize_TTL_List_PVT RETURN BOOLEAN
127 IS
128 BEGIN
129 trc( 'st:Initialize_TTL_List_PVT');
130
131 /* concatenate the tempTblList with the instance_code to be the exact
132 temp table name */
133 IF NOT v_is_TTL_initialized THEN
134 trc( 'ST:V_PARTtBLlIST COUNT = '|| to_char(v_partTblList.COUNT));
135 FOR i IN 1..v_partTblList.COUNT LOOP
136 v_tempTblList(i):= v_tempTblList(i)||v_instance_code;
137 END LOOP;
138
139 v_is_TTL_initialized:= TRUE;
140 END IF;
141
142 trc( 'en:Initialize_TTL_List_PVT');
143 RETURN TRUE;
144 EXCEPTION
145 WHEN OTHERS THEN
146 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<Initialize_TTL_List_PVT>>');
147 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
148 RETURN FALSE;
149 END Initialize_TTL_List_PVT;
150
151
152 FUNCTION Initialize_SWAP_Tbl_List( p_instance_id IN NUMBER,
153 p_instance_code IN VARCHAR2)
154 RETURN BOOLEAN
155 IS
156 BEGIN
157
158 IF NOT Initialize_Schema_PVT THEN RETURN FALSE; END IF;
159
160 /* Add the following details for each table
161
162 v_swapTblList(1).ods_table_name := 'MSC_TABLE_NAME'; --ODS table name
163 v_swapTblList(1).stg_table_name := 'MSC_ST_TABLE_NAME'; --Staging table name
164 v_swapTblList(1).temp_table_name := 'TABLE_NAME_'|| p_instance_code ; --Temp table name
165 v_swapTblList(1).stg_table_partn_name := 'TABLE_NAME_' || p_instance_id ; -- staging table partition name
166 v_swapTblList(1).entity_name := 'Entity Name'; --Entity name
167 v_swapTblList(1).column_name := 'entity_status'; --status column in msc_coll_parameters to track the swap status
168 */
169 v_swapTblList(1).ods_table_name := 'MSC_DELIVERY_DETAILS';
170 v_swapTblList(1).stg_table_name := 'MSC_ST_DELIVERY_DETAILS';
171 v_swapTblList(1).temp_table_name := 'DELIVERY_DETAILS_'|| p_instance_code ;
172 v_swapTblList(1).stg_table_partn_name := 'DELIVERY_DETAILS_' || p_instance_id ;
173 v_swapTblList(1).entity_name := 'Delivery Details';
174 v_swapTblList(1).column_name := 'DELIVERY_DTL_SWAP_FLAG';
175
176 v_swapTblList(2).ods_table_name := 'MSC_REGION_LOCATIONS';
177 v_swapTblList(2).stg_table_name := 'MSC_ST_REGION_LOCATIONS';
178 v_swapTblList(2).temp_table_name := 'REGION_LOCATIONS_'|| p_instance_code ;
179 v_swapTblList(2).stg_table_partn_name := 'REGION_LOCATIONS_' || p_instance_id ;
180 v_swapTblList(2).entity_name := 'Sourcing Rules';
181 v_swapTblList(2).column_name := 'SOURCING_DTL_SWAP_FLAG';
182
183 v_swapTblList(3).ods_table_name := 'MSC_ZONE_REGIONS';
184 v_swapTblList(3).stg_table_name := 'MSC_ST_ZONE_REGIONS';
185 v_swapTblList(3).temp_table_name := 'ZONE_REGIONS_'|| p_instance_code ;
186 v_swapTblList(3).stg_table_partn_name := 'ZONE_REGIONS_' || p_instance_id ;
187 v_swapTblList(3).entity_name := 'Sourcing Rules';
188 v_swapTblList(3).column_name := 'SOURCING_DTL_SWAP_FLAG';
189
190 v_swapTblList(4).ods_table_name := 'MSC_REGIONS';
191 v_swapTblList(4).stg_table_name := 'MSC_ST_REGIONS';
192 v_swapTblList(4).temp_table_name := 'REGIONS_'|| p_instance_code ;
193 v_swapTblList(4).stg_table_partn_name := 'REGIONS_' || p_instance_id ;
194 v_swapTblList(4).entity_name := 'Sourcing Rules';
195 v_swapTblList(4).column_name := 'SOURCING_DTL_SWAP_FLAG';
196
197 RETURN TRUE;
198 EXCEPTION
199 WHEN OTHERS THEN
200 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<Initialize_SWAP_Tbl_List_PVT>>');
201 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
202 RETURN FALSE;
203 END Initialize_SWAP_Tbl_List;
204
205 FUNCTION Drop_Temp_Tab_PVT(pTableName VARCHAR2) RETURN BOOLEAN
206 IS
207 CURSOR c_query_table( p_tblname IN VARCHAR2) IS
208 SELECT 1
209 FROM ALL_TABLES
210 WHERE table_name= upper(p_tblname)
211 AND owner = v_msc_schema;
212
213 lv_table_exist NUMBER;
214 BEGIN
215 trc('st:Drop_Temp_Tab_PVT :' || pTableName);
216 /* dropTemparyTable; */
217 lv_table_exist := SYS_NO;
218 OPEN c_query_table( pTableName);
219 FETCH c_query_table INTO lv_table_exist;
220 CLOSE c_query_table;
221
222 IF lv_table_exist=SYS_YES THEN
223 v_sql_stmt := 'DROP TABLE '||pTableName;
224
225 ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
226 application_short_name => 'MSC',
227 statement_type => AD_DDL.DROP_TABLE,
228 statement => v_sql_stmt,
229 object_name => pTableName);
230 END IF;
231
232 trc('en:Drop_Temp_Tab_PVT');
233 RETURN TRUE;
234
235 EXCEPTION
236 WHEN OTHERS THEN
237 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<Drop_Temp_Tab_PVT>>');
238 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
239 RETURN FALSE;
240 END Drop_Temp_Tab_PVT;
241
242 FUNCTION get_block_size RETURN NUMBER
243 IS
244 lv_block_size NUMBER := 8192;
245 BEGIN
246
247 select value
248 into lv_block_size
249 from v$parameter
250 where name = 'db_block_size' ;
251 return lv_block_size ;
252
253 EXCEPTION
254 WHEN OTHERS THEN
255 lv_block_size := 8192;
256 return lv_block_size;
257
258 END get_block_size;
259
260 /* =========== Create Temporary Tables ============= */
261 FUNCTION Create_Temp_Tab_PVT(pTableName VARCHAR2,
262 pTmpTableName VARCHAR2) RETURN BOOLEAN
263 IS
264 lv_partition_name VARCHAR2(30);
265 lv_return_status VARCHAR2(2048);
266 lv_msg_data VARCHAR2(2048);
267 lv_extent_management VARCHAR2(10);
268 lv_allocation_type VARCHAR2(9);
269 lv_part_exists NUMBER := 0;
270
271 CURSOR c_part_para( p_owner IN VARCHAR,
272 p_tbl IN VARCHAR,
273 p_partname IN VARCHAR) IS
274 SELECT atp.tablespace_name,
275 decode(atp.initial_extent, NULL, '', ' INITIAL ' || atp.initial_extent),
276 decode(atp.next_extent, NULL, '', ' NEXT ' || atp.next_extent),
277 decode(atp.pct_increase, NULL, '', ' PCTINCREASE ' || atp.pct_increase),
278 decode(atp.pct_free, NULL, '', ' PCTFREE ' || atp.pct_free),
279 decode(atp.ini_trans, NULL, '', ' INITRANS ' || atp.ini_trans),
280 decode(atp.max_trans, NULL, '', ' MAXTRANS ' || atp.max_trans),
281 dt.EXTENT_MANAGEMENT,
282 dt.ALLOCATION_TYPE
283 FROM ALL_TAB_PARTITIONS atp ,
284 dba_tablespaces dt
285 WHERE atp.table_name = p_tbl
286 AND atp.table_owner = p_owner
287 AND atp.partition_name=p_partname
288 AND atp.TABLESPACE_Name = dt.TABLESPACE_NAME;
289
290
291 lv_tablespace_name VARCHAR2(30);
292 lv_initial_extent VARCHAR2(50);
293 lv_next_extent VARCHAR2(50);
294 lv_pct_increase VARCHAR2(50);
295 lv_storage_clause VARCHAR2(255);
296
297 lv_ini_trans VARCHAR2(50);
298 lv_max_trans VARCHAR2(50);
299 lv_pct_free VARCHAR2(50);
300
301
302
303 BEGIN
304 trc('st:Create_Temp_Tab_PVT');
305 /* createTempraryTable; */
306
307 trc('st ' || pTableName);
308
309 msc_manage_plan_partitions.get_partition_name
310 ( -1,
311 v_instance_id,
312 pTableName,
313 SYS_NO,
314 lv_partition_name,
315 lv_return_status,
316 lv_msg_data);
317 trc('i='|| lv_msg_data);
318
319 select count(*)
320 into lv_part_exists
321 from ALL_TAB_PARTITIONS
322 where table_name = pTableName
323 AND table_owner = v_msc_schema
324 AND partition_name=lv_partition_name;
325
326 IF lv_part_exists = 0 THEN
327 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<Create_Temp_Tab_PVT>>');
328 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Specified partition: ' || lv_partition_name || ' on table: '|| pTableName ||' does not exist');
329 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please create all the missing partitions');
330 RETURN FALSE;
331 END IF;
332
333 OPEN c_part_para( v_msc_schema, pTableName,lv_partition_name);
334 FETCH c_part_para
335 INTO lv_tablespace_name,
336 lv_initial_extent,
337 lv_next_extent,
338 lv_pct_increase,
339 lv_pct_free,
340 lv_ini_trans,
341 lv_max_trans,
342 lv_extent_management,
343 lv_allocation_type;
344 CLOSE c_part_para;
345
349 ELSE
346 IF (lv_extent_management = 'DICTIONARY' OR (lv_extent_management = 'LOCAL' AND lv_allocation_type = 'USER')) THEN
347 IF (lv_initial_extent is null and lv_next_extent is null and lv_pct_increase is null) THEN
348 lv_storage_clause:='';
350 lv_storage_clause:=' STORAGE( ' || lv_initial_extent || lv_next_extent || lv_pct_increase ||')';
351 END IF;
352 END IF ;
353
354 IF ( lv_extent_management = 'LOCAL' AND lv_allocation_type = 'SYSTEM') THEN
355 lv_next_extent :='';
356 lv_pct_increase := '';
357 IF (lv_initial_extent is null and lv_next_extent is null and lv_pct_increase is null) THEN
358 lv_storage_clause:='';
359 ELSE
360 lv_storage_clause:=' STORAGE( ' || lv_initial_extent || lv_next_extent || lv_pct_increase ||')';
361 END IF;
362 END IF;
363
364 IF ( lv_extent_management = 'LOCAL' AND lv_allocation_type = 'UNIFORM') THEN
365 lv_storage_clause:='';
366 END IF;
367
368 v_sql_stmt:=
369 'CREATE TABLE '||pTmpTableName
370 ||' TABLESPACE '||lv_tablespace_name
371 ||lv_storage_clause
372 ||lv_pct_free
373 ||lv_ini_trans
374 ||lv_max_trans
375 -- ||' NOLOGGING '
376 ||' AS SELECT *'
377 ||' FROM '||pTableName
378 ||' WHERE NULL=NULL';
379
380
381
382 ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
383 application_short_name => 'MSC',
384 statement_type => AD_DDL.CREATE_TABLE,
385 statement => v_sql_stmt,
386 object_name => pTmpTableName);
387
388 --trc('en:Create_Temp_Tab_PVT');
389 RETURN TRUE;
390
391 EXCEPTION
392 WHEN OTHERS THEN
393 IF c_part_para%ISOPEN THEN CLOSE c_part_para; END IF;
394 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<Create_Temp_Tab_PVT>>');
395 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<SQL>>'||v_sql_stmt);
396 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
397 RETURN FALSE;
398 END Create_Temp_Tab_PVT;
399
400 /* =========== Create Indexes on Temporary Tables ========== */
401
402 FUNCTION Create_Index_PVT( p_uniqueness IN VARCHAR2) RETURN BOOLEAN
403 IS
404 lv_crt_ind_status NUMBER;
405 BEGIN
406 trc('st:Create_Index_PVT');
407 /* creatIndexOnTempraryTable; */
408
409
410 FOR i IN 1..v_tempTblList.COUNT LOOP
411
412 lv_crt_ind_status := create_temp_table_index
413 ( p_uniqueness,
414 v_partTblList(i),
415 v_tempTblList(i),
416 v_instance_code,
417 v_instance_id,
418 SYS_NO,
419 MSC_CL_COLLECTION.G_WARNING
420 );
421
422 IF lv_crt_ind_status = MSC_CL_COLLECTION.G_WARNING THEN
423 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Warning during unique index creation on table, ' || v_tempTblList(i));
424 ELSIF lv_crt_ind_status = MSC_CL_COLLECTION.G_ERROR THEN
425 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error during unique index creation on table, ' || v_tempTblList(i));
426 RETURN FALSE;
427 ELSE
428 trc('Unique index creation successful on table, ' || v_tempTblList(i));
429 END IF;
430
431 END LOOP;
432
433 trc('en:Create_Index_PVT');
434 RETURN TRUE;
435
436 EXCEPTION
437 WHEN OTHERS THEN
438 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<Create_Index_PVT>>');
439 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<SQL>>'||v_sql_stmt);
440 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
441 RETURN FALSE;
442 END Create_Index_PVT;
443
444 /* ========= Exchange Partitions =========== */
445 FUNCTION Exchange_Partition_PVT RETURN BOOLEAN
446 IS
447
448 lv_partition_name VARCHAR2(30);
449 lv_return_status VARCHAR2(2048);
450 lv_msg_data VARCHAR2(2048);
451 lv_result BOOLEAN;
452
453 BEGIN
454 trc('st:Exchange_Partition_PVT');
455 trc('st:Exchange_Partition_PVT: v_tempTblList='||to_char(v_tempTblList.COUNT + v_swapTblList.COUNT ));
456 /* exchange partition with temporary table */
457 FOR i IN 1..v_tempTblList.COUNT LOOP
458
459 msc_manage_plan_partitions.get_partition_name
460 ( -1,
461 v_instance_id,
462 v_partTblList(i),
463 SYS_NO,
464 lv_partition_name,
465 lv_return_status,
466 lv_msg_data);
467
468 if NOT EXCHANGE_SINGLE_TAB_PARTN ( v_partTblList(i) ,
469 lv_partition_name ,
470 v_tempTblList(i),
471 MSC_UTIL.SYS_YES) THEN
472 return FALSE;
473 END IF;
474 END LOOP;
475
476 FOR i IN 1..v_swapTblList.COUNT LOOP
477
478 msc_manage_plan_partitions.get_partition_name
479 ( -1,
483 lv_partition_name,
480 v_instance_id,
481 v_swapTblList(i).ods_table_name,
482 SYS_NO,
484 lv_return_status,
485 lv_msg_data);
486
487 if NOT EXCHANGE_SINGLE_TAB_PARTN ( v_swapTblList(i).ods_table_name ,
488 lv_partition_name ,
489 v_swapTblList(i).temp_table_name,
490 MSC_UTIL.SYS_YES) THEN
491 return FALSE;
492 END IF;
493
494 --if the table is in the ods-staging swap table list.
495 EXECUTE IMMEDIATE ' update msc_coll_parameters set '
496 || v_swapTblList(i).column_name || ' = ' || MSC_CL_COLLECTION.G_STG_ODS_SWP_PHASE_2
497 || ' where instance_id = ' || v_instance_id;
498
499 END LOOP;
500
501 trc('en:Exchange_Partition_PVT');
502 RETURN TRUE;
503
504 EXCEPTION
505 WHEN OTHERS THEN
506 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<Exchange_Partition_PVT>>');
507 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<SQL>>'||v_sql_stmt);
508 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
509 RETURN FALSE;
510 END Exchange_Partition_PVT;
511
512 /* ========== Analyse Temporary Table =========== */
513 FUNCTION Analyse_Temp_Tab_PVT RETURN BOOLEAN
514 IS
515 BEGIN
516 trc('st:Analyse_Temp_Tab_PVT');
517 /* Analyse temporary table; */
518 FOR i IN 1..v_tempTblList.COUNT LOOP
519 msc_analyse_tables_pk.analyse_table( v_tempTblList(i));
520 END LOOP;
521
522 FOR i IN 1..v_swapTblList.COUNT LOOP
523 msc_analyse_tables_pk.analyse_table( v_tempTblList(i));
524 END LOOP;
525
526 trc('en:Analyse_Temp_Tab_PVT');
527 RETURN TRUE;
528
529 EXCEPTION
530 WHEN OTHERS THEN
531 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<Analyse_Temp_Tab_PVT>>');
532 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
533 RETURN FALSE;
534 END Analyse_Temp_Tab_PVT;
535
536
537 /* ========== Public Functions ============= */
538 /* ========== Create Temporary Tables =========== */
539 FUNCTION Create_Temp_Tbl RETURN BOOLEAN
540 IS
541 BEGIN
542
543 IF NOT v_is_initialized THEN RETURN FALSE; END IF;
544
545 trc('DS tbl count= ' || to_char(v_partTblList.COUNT + v_swapTblList.COUNT));
546
547 FOR i IN 1..v_partTblList.COUNT LOOP
548 IF NOT Drop_Temp_Tab_PVT(v_tempTblList(i)) THEN RETURN FALSE; END IF;
549 IF NOT Create_Temp_Tab_PVT (v_partTblList(i),v_tempTblList(i)) THEN RETURN FALSE; END IF;
550 END LOOP;
551
552 FOR i IN 1..v_swapTblList.COUNT LOOP
553 IF NOT Drop_Temp_Tab_PVT(v_swapTblList(i).temp_table_name) THEN RETURN FALSE; END IF;
554 IF NOT Create_Temp_Tab_PVT (v_swapTblList(i).ods_table_name,v_swapTblList(i).temp_table_name) THEN RETURN FALSE; END IF;
555 END LOOP;
556
557 RETURN TRUE;
558
559 EXCEPTION
560 WHEN OTHERS THEN
561 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<Create_Temp_Tbl>>');
562 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
563 RETURN FALSE;
564 END Create_Temp_Tbl;
565
566 FUNCTION Exchange_Partition RETURN BOOLEAN
567 IS
568 BEGIN
569
570 IF NOT v_is_initialized THEN RETURN FALSE; END IF;
571 IF NOT Exchange_Partition_PVT THEN RETURN FALSE; END IF;
572
573 RETURN TRUE;
574 EXCEPTION
575 WHEN OTHERS THEN
576 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<Exchange_Partition>>');
577 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
578 RETURN FALSE;
579 END Exchange_Partition;
580
581 /* ========== Drop Temporary Tables =========== */
582 FUNCTION Drop_Temp_Tbl RETURN BOOLEAN
583 IS
584 BEGIN
585
586 IF NOT v_is_initialized THEN RETURN FALSE; END IF;
587
588 FOR i IN 1..v_partTblList.COUNT LOOP
589 IF NOT Drop_Temp_Tab_PVT(v_tempTblList(i)) THEN RETURN FALSE; END IF;
590 END LOOP;
591
592 FOR i IN 1..v_swapTblList.COUNT LOOP
593 IF NOT Drop_Temp_Tab_PVT(v_swapTblList(i).temp_table_name) THEN RETURN FALSE; END IF;
594 END LOOP;
595
596 RETURN TRUE;
597
598 EXCEPTION
599 WHEN OTHERS THEN
600 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<Drop_Temp_Tbl>>');
601 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
602 RETURN FALSE;
603 END Drop_Temp_Tbl;
604
605 FUNCTION Create_Unique_Index RETURN BOOLEAN
606 IS
607 BEGIN
608 IF NOT v_is_initialized THEN RETURN FALSE; END IF;
609 IF NOT Create_Index_PVT('UNIQUE') THEN RETURN FALSE; END IF;
610
611 RETURN TRUE;
612 EXCEPTION
613 WHEN OTHERS THEN
614 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<Create_Unique_Index>>');
615 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
616 RETURN FALSE;
617 END Create_Unique_Index;
618
619 FUNCTION Create_NonUnique_Index RETURN BOOLEAN
620 IS
621 BEGIN
622
623 IF NOT v_is_initialized THEN RETURN FALSE; END IF;
624 IF NOT Create_Index_PVT('NONUNIQUE') THEN RETURN FALSE; END IF;
625
626 RETURN TRUE;
627 EXCEPTION
631 RETURN FALSE;
628 WHEN OTHERS THEN
629 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<Create_NonUnique_Index>>');
630 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
632 END Create_NonUnique_Index;
633
634 FUNCTION Analyse_Temp_Tbl RETURN BOOLEAN
635 IS
636 BEGIN
637
638 IF NOT v_is_initialized THEN RETURN FALSE; END IF;
639 IF NOT Analyse_Temp_Tab_PVT THEN RETURN FALSE; END IF;
640
641 RETURN TRUE;
642 EXCEPTION
643 WHEN OTHERS THEN
644 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<Analyse_Temp_Tbl>>');
645 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
646 RETURN FALSE;
647 END Analyse_Temp_Tbl;
648
649 FUNCTION Initialize( p_instance_id IN NUMBER,
650 p_instance_code IN VARCHAR2,
651 p_is_so_cmp_rf IN BOOLEAN)
652 RETURN BOOLEAN
653 IS
654 BEGIN
655
656 IF NOT v_is_initialized THEN
657 v_instance_id := p_instance_id;
658 v_instance_code := p_instance_code;
659
660 IF NOT Initialize_Schema_PVT THEN RETURN FALSE; END IF;
661 IF NOT Initialize_TTL_List_PVT THEN RETURN FALSE; END IF;
662 IF NOT Initialize_SWAP_Tbl_List(p_instance_id,p_instance_code ) THEN RETURN FALSE; END IF;
663
664 IF NOT p_is_so_cmp_rf THEN
665 v_partTblList.TRIM;
666 v_tempTblList.TRIM;
667 END IF;
668
669 v_is_initialized:= TRUE;
670 END IF;
671
672 RETURN v_is_initialized;
673 EXCEPTION
674 WHEN OTHERS THEN
675 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<Initialize>>');
676 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
677 RETURN v_is_initialized;
678 END Initialize;
679
680 /*********************** PREPLACE CHANGE START ***********************/
681
682 FUNCTION Get_Table_Index (p_table_name VARCHAR2) RETURN INTEGER
683
684 IS
685
686 j INTEGER;
687 parm_str VARCHAR2(30);
688 tab_str VARCHAR2(30);
689
690
691 BEGIN
692
693 parm_str := SUBSTR(p_table_name, 1, INSTR(p_table_name, '_', -1));
694
695 FOR j IN 1..v_tempTblList.COUNT LOOP
696 tab_str := SUBSTR(v_tempTblList(j), 1, INSTR(v_tempTblList(j), '_', -1));
697 IF (parm_str = tab_str) THEN
698 RETURN j;
699 END IF;
700 END LOOP;
701
702 RETURN 0;
703
704 END Get_Table_Index;
705
706
707 FUNCTION Get_SWAP_Table_Index (p_table_name VARCHAR2) RETURN INTEGER
708 IS
709 BEGIN
710 FOR j IN 1..v_swapTblList.COUNT LOOP
711 IF (p_table_name = v_swapTblList(j).ods_table_name) THEN
712 RETURN j;
713 END IF;
714 END LOOP;
715 RETURN 0;
716 END Get_SWAP_Table_Index;
717
718
719 FUNCTION Get_SWAP_TMP_Table_Index (p_table_name VARCHAR2) RETURN INTEGER
720 IS
721 BEGIN
722 FOR j IN 1..v_swapTblList.COUNT LOOP
723 IF (p_table_name = v_swapTblList(j).temp_table_name) THEN
724 RETURN j;
725 END IF;
726 END LOOP;
727 RETURN 0;
728 END Get_SWAP_TMP_Table_Index;
729 /* ========= Exchange Partitions New function for PREPLACE =========== */
730 FUNCTION Exchange_Partition_PARTIAL ( p_tempTblList TblNmTblTyp)
731 RETURN BOOLEAN
732 IS
733
734 lv_partition_name VARCHAR2(30);
735 lv_return_status VARCHAR2(2048);
736 lv_msg_data VARCHAR2(2048);
737 j INTEGER;
738 lv_is_swap_table BOOLEAN := FALSE;
739 lv_partn_tbl_name VARCHAR2(30);
740
741 BEGIN
742 trc('st:Exchange_Partition_PARTIAL: p_tempTblList='||to_char(p_tempTblList.COUNT));
743 /* exchange partition with temporary table */
744 FOR i IN 1..p_tempTblList.COUNT LOOP
745
746 lv_is_swap_table := FALSE;
747 j := Get_Table_Index(p_tempTblList(i));
748
749 IF j = 0 THEN
750 j := Get_SWAP_TMP_Table_Index(p_tempTblList(i));
751 IF j = 0 THEN
752 trc('st:Exchange_Partition_PARTIAL - NO Tables to Exchange');
753 RETURN TRUE;
754 END IF;
755 lv_is_swap_table := TRUE;
756 END IF;
757
758 if lv_is_swap_table then
759 lv_partn_tbl_name := v_swapTblList(j).ods_table_name;
760 else
761 lv_partn_tbl_name := v_partTblList(j);
762 end if;
763
764 msc_manage_plan_partitions.get_partition_name
765 ( -1,
766 v_instance_id,
767 lv_partn_tbl_name,
768 SYS_NO,
769 lv_partition_name,
770 lv_return_status,
771 lv_msg_data);
772
773 if NOT EXCHANGE_SINGLE_TAB_PARTN ( lv_partn_tbl_name ,
774 lv_partition_name ,
775 p_tempTblList(i),
776 MSC_UTIL.SYS_YES) THEN
777 return FALSE;
778 END IF;
779
780 --if the table is in the ods-staging swap table list.
781 if lv_is_swap_table then
785 end if;
782 EXECUTE IMMEDIATE ' update msc_coll_parameters set '
783 || v_swapTblList(j).column_name || ' = ' || MSC_CL_COLLECTION.G_STG_ODS_SWP_PHASE_2
784 || ' where instance_id = ' || v_instance_id;
786
787 END LOOP;
788
789 trc('en:Exchange_Partition_PARTIAL');
790 RETURN TRUE;
791
792 EXCEPTION
793 WHEN OTHERS THEN
794 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<Exchange_Partition_PARTIAL>>');
795 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<SQL>>'||v_sql_stmt);
796 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
797 RETURN FALSE;
798 END Exchange_Partition_PARTIAL;
799
800 FUNCTION Exchange_Single_Tab_Partn ( pPartitionedTableName IN VARCHAR2,
801 pPartitionName IN VARCHAR2,
802 pUnPartitionedTableName IN VARCHAR2,
803 pIncludeIndexes IN NUMBER DEFAULT MSC_UTIL.SYS_YES ) RETURN BOOLEAN
804 IS
805 BEGIN
806
807 trc('st:EXCHANGE_SINGLE_TAB_PARTN ' || pPartitionedTableName);
808
809 v_sql_stmt:=
810 'ALTER TABLE '|| pPartitionedTableName
811 ||' EXCHANGE PARTITION '||pPartitionName
812 ||' WITH TABLE '|| pUnPartitionedTableName;
813
814 IF ( pIncludeIndexes = MSC_UTIL.SYS_YES )THEN
815 v_sql_stmt:= v_sql_stmt ||' INCLUDING INDEXES';
816 END IF;
817
818 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'exchange part:-'||v_sql_stmt);
819
820 /* execute the sql statement */
821 ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
822 application_short_name => 'MSC',
823 statement_type => AD_DDL.ALTER_TABLE,
824 statement => v_sql_stmt,
825 object_name => pPartitionedTableName);
826
827 trc('en:EXCHANGE_SINGLE_TAB_PARTN' || pPartitionedTableName);
828 RETURN TRUE;
829 EXCEPTION
830 WHEN OTHERS THEN
831 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, sqlerrm);
832 RETURN FALSE;
833 END EXCHANGE_SINGLE_TAB_PARTN;
834
835
836 FUNCTION Undo_Stg_Ods_Swap RETURN BOOLEAN IS
837 lv_partition_name VARCHAR2(30);
838 lv_return_status VARCHAR2(2048);
839 lv_msg_data VARCHAR2(2048);
840 lv_swap_status NUMBER;
841 BEGIN
842
843 FOR i IN 1..v_swapTblList.COUNT LOOP
844
845 EXECUTE IMMEDIATE ' select nvl(' ||v_swapTblList(i).column_name || ',' || MSC_CL_COLLECTION.G_STG_ODS_SWP_PHASE_0 || ') from msc_coll_parameters '
846 || ' where instance_id = ' || v_instance_id
847 INTO lv_swap_status;
848
849 If lv_swap_status = MSC_CL_COLLECTION.G_STG_ODS_SWP_PHASE_2 then
850
851 msc_manage_plan_partitions.get_partition_name
852 ( -1,
853 v_instance_id,
854 v_swapTblList(i).ods_table_name ,
855 SYS_NO,
856 lv_partition_name,
857 lv_return_status,
858 lv_msg_data);
859
860 if NOT EXCHANGE_SINGLE_TAB_PARTN ( v_swapTblList(i).ods_table_name ,
861 lv_partition_name ,
862 v_swapTblList(i).temp_table_name,
863 MSC_UTIL.SYS_YES) then
864 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,' Error while exchanging partition for table :' || v_swapTblList(i).ods_table_name );
865 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,' Please launch targetted collections for following entities :' );
866
867 FOR j IN i..v_swapTblList.COUNT LOOP
868 EXECUTE IMMEDIATE ' select ' ||v_swapTblList(j).column_name || 'from msc_coll_parameters '
869 || ' where instance_id = ' || v_instance_id
870 INTO lv_swap_status;
871 If lv_swap_status <> MSC_CL_COLLECTION.G_STG_ODS_SWP_PHASE_0 then
872 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,v_swapTblList(j).entity_name );
873 End if;
874 END LOOP;
875
876 return FALSE;
877 END IF;
878 lv_swap_status := MSC_CL_COLLECTION.G_STG_ODS_SWP_PHASE_1;
879 End if;
880
881 If lv_swap_status = MSC_CL_COLLECTION.G_STG_ODS_SWP_PHASE_1 then
882
883 if NOT EXCHANGE_SINGLE_TAB_PARTN ( v_swapTblList(i).stg_table_name ,
884 v_swapTblList(i).stg_table_partn_name ,
885 v_swapTblList(i).temp_table_name,
886 MSC_UTIL.SYS_NO) then
887 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,' Error while exchanging partition for table :' || v_swapTblList(i).stg_table_name );
888 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,' Please launch planning data pull again for following entities :' );
889 FOR j IN i..v_swapTblList.COUNT LOOP
890 EXECUTE IMMEDIATE ' select ' ||v_swapTblList(j).column_name || 'from msc_coll_parameters '
891 || ' where instance_id = ' || v_instance_id
895 End if;
892 INTO lv_swap_status;
893 If lv_swap_status <> MSC_CL_COLLECTION.G_STG_ODS_SWP_PHASE_0 then
894 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,v_swapTblList(j).entity_name );
896 END LOOP;
897
898 return FALSE;
899 END IF;
900 End if;
901
902
903 EXECUTE IMMEDIATE ' update msc_coll_parameters set '
904 || v_swapTblList(i).column_name || ' = ' || MSC_CL_COLLECTION.G_STG_ODS_SWP_PHASE_0
905 || ' where instance_id = ' || v_instance_id;
906
907 End loop;
908
909 RETURN TRUE;
910 END;
911
912 /****** This is the overloaded Function for partial replacement *****/
913
914 FUNCTION Exchange_Partition (prec CollParamREC,
915 p_is_cont_refresh in boolean) RETURN BOOLEAN
916 IS
917
918 lv_TblList TblNmTblTyp;
919 tbl_count INTEGER := 0;
920 i INTEGER ;
921 tbl_nam_str VARCHAR2(30);
922 lv_swap_status NUMBER;
923
924 BEGIN
925
926 IF NOT v_is_initialized THEN RETURN FALSE; END IF;
927
928 lv_TBlList := TblNmTblTyp('INITIALIZE');
929
930 /* Add entries to the Temp Table List */
931
932 -- agmcont
933 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'payback flag:-'||prec.payback_demand_supply_flag);
934 IF prec.item_flag = SYS_YES THEN
935 if (p_is_cont_refresh and
936 (prec.item_sn_flag = SYS_INCR or prec.item_sn_flag = SYS_NO)) then
937 null;
938 else
939 tbl_nam_str := 'SYSTEM_ITEMS_'||v_instance_code;
940 IF (tbl_count = 0) THEN
941 tbl_count := tbl_count + 1;
942 lv_TblList(tbl_count) := tbl_nam_str;
943 ELSE
944 lv_TblList.EXTEND; -- Extend the size
945 tbl_count := tbl_count + 1;
946 lv_TblList(tbl_count) := tbl_nam_str;
947 END IF;
948 tbl_nam_str := 'ITEM_CATEGORIES_'||v_instance_code;
949 lv_TblList.EXTEND; -- Extend the size
950 tbl_count := tbl_count + 1;
951 lv_TblList(tbl_count) := tbl_nam_str;
952 end if;
953 END IF; -- item_flag
954
955 IF prec. item_subst_flag = SYS_YES THEN
956 IF(NOT p_is_cont_refresh) THEN
957 tbl_nam_str := 'ITEM_SUBSTITUTES_'||v_instance_code;
958 IF (tbl_count = 0) THEN
959 tbl_count := tbl_count + 1;
960 lv_TblList(tbl_count) := tbl_nam_str;
961 ELSE
962 lv_TblList.EXTEND; -- Extend the size
963 tbl_count := tbl_count + 1;
964 lv_TblList(tbl_count) := tbl_nam_str;
965 END IF;
966 END IF ;
967
968 END IF;
969
970
971 IF prec.wip_flag = SYS_YES THEN
972
973 if (p_is_cont_refresh and
974 (prec.wip_sn_flag = SYS_INCR or prec.wip_sn_flag = SYS_NO)) then
975 null;
976 else
977 tbl_nam_str := 'RESOURCE_REQUIREMENTS_'||v_instance_code;
978 IF (tbl_count = 0) THEN
979 tbl_count := tbl_count + 1;
980 lv_TblList(tbl_count) := tbl_nam_str;
981 ELSE
982 lv_TblList.EXTEND; -- Extend the size
983 tbl_count := tbl_count + 1;
984 lv_TblList(tbl_count) := tbl_nam_str;
985 END IF;
986 tbl_nam_str :='JOB_OPERATION_NETWORKS_'||v_instance_code;
987 lv_TblList.EXTEND;
988 tbl_count := tbl_count + 1;
989 lv_TblList(tbl_count) := tbl_nam_str;
990 tbl_nam_str :='JOB_OPERATIONS_'||v_instance_code;
991 lv_TblList.EXTEND;
992 tbl_count := tbl_count + 1;
993 lv_TblList(tbl_count) := tbl_nam_str;
994 tbl_nam_str :='JOB_REQUIREMENT_OPS_'||v_instance_code;
995 lv_TblList.EXTEND;
996 tbl_count := tbl_count + 1;
997 lv_TblList(tbl_count) := tbl_nam_str;
998 tbl_nam_str :='JOB_OP_RESOURCES_'||v_instance_code;
999 lv_TblList.EXTEND;
1000 tbl_count := tbl_count + 1;
1001 lv_TblList(tbl_count) := tbl_nam_str;
1002
1003 /* ds_plan: change start */
1004 tbl_nam_str :='RESOURCE_INSTANCE_REQS_'||v_instance_code;
1005 lv_TblList.EXTEND;
1006 tbl_count := tbl_count + 1;
1007 lv_TblList(tbl_count) := tbl_nam_str;
1008
1009 tbl_nam_str :='JOB_OP_RES_INSTANCES_'||v_instance_code;
1010 lv_TblList.EXTEND;
1011 tbl_count := tbl_count + 1;
1012 lv_TblList(tbl_count) := tbl_nam_str;
1013
1014 /* ds_plan: change end */
1015 end if;
1016 END IF; -- wip_flag
1017
1018 -- All supplies related flags
1019 IF ((prec.po_flag = SYS_YES) or
1020 (prec.oh_flag = SYS_YES) or
1021 (prec.wip_flag = SYS_YES) or
1022 (prec.mps_flag = SYS_YES) or
1023 (prec.user_supply_demand_flag = SYS_YES)
1024 /* CP-ACK starts */
1025 or(prec.supplier_response_flag = SYS_YES)
1026 or (prec.internal_repair_flag = SYS_YES) -- Added for Bug 5909379 SRP Additions
1027 or (prec.external_repair_flag = SYS_YES)
1031
1028 /* CP-ACK ends */
1029 OR (prec.payback_demand_supply_flag = SYS_YES)
1030 ) THEN
1032 if (p_is_cont_refresh and
1033 (prec.po_sn_flag = SYS_INCR or prec.po_sn_flag = SYS_NO) and
1034 (prec.oh_sn_flag = SYS_INCR or prec.oh_sn_flag = SYS_NO) and
1035 (prec.wip_sn_flag = SYS_INCR or prec.wip_sn_flag = SYS_NO) and
1036 (prec.mps_sn_flag = SYS_INCR or prec.mps_sn_flag = SYS_NO) and
1037 (prec.usup_sn_flag = SYS_INCR or prec.usup_sn_flag = SYS_NO ) and
1038 /* CP-AUTO */
1039 (prec.suprep_sn_flag = SYS_INCR or prec.suprep_sn_flag = SYS_NO) ) then
1040 NULL;
1041 else
1042
1043 tbl_nam_str := 'SUPPLIES_'||v_instance_code;
1044 IF (tbl_count = 0) THEN
1045 tbl_count := tbl_count + 1;
1046 lv_TblList(tbl_count) := tbl_nam_str;
1047 ELSE
1048 lv_TblList.EXTEND; -- Extend the size
1049 tbl_count := tbl_count + 1;
1050 lv_TblList(tbl_count) := tbl_nam_str;
1051 END IF;
1052 end if;
1053 END IF; -- supplies
1054
1055 IF prec.bom_flag = SYS_YES THEN
1056
1057 if (p_is_cont_refresh and
1058 (prec.bom_sn_flag = SYS_INCR or prec.bom_sn_flag = SYS_NO)) then
1059 null;
1060 else
1061 tbl_nam_str := 'BOM_COMPONENTS_'||v_instance_code;
1062 IF (tbl_count = 0) THEN
1063 tbl_count := tbl_count + 1;
1064 lv_TblList(tbl_count) := tbl_nam_str;
1065 ELSE
1066 lv_TblList.EXTEND; -- Extend the size
1067 tbl_count := tbl_count + 1;
1068 lv_TblList(tbl_count) := tbl_nam_str;
1069 END IF;
1070 tbl_nam_str := 'OPERATION_RESOURCES_'||v_instance_code;
1071 lv_TblList.EXTEND; -- Extend the size
1072 tbl_count := tbl_count + 1;
1073 lv_TblList(tbl_count) := tbl_nam_str;
1074 tbl_nam_str := 'OPERATION_RESOURCE_SEQS_'||v_instance_code;
1075 lv_TblList.EXTEND; -- Extend the size
1076 tbl_count := tbl_count + 1;
1077 lv_TblList(tbl_count) := tbl_nam_str;
1078 tbl_nam_str := 'ROUTING_OPERATIONS_'||v_instance_code;
1079 lv_TblList.EXTEND; -- Extend the size
1080 tbl_count := tbl_count + 1;
1081 lv_TblList(tbl_count) := tbl_nam_str;
1082 end if;
1083 END IF; -- bom_flag
1084
1085 -- All demands related flags
1086 IF ((prec.wip_flag = SYS_YES) or
1087 (prec.mds_flag = SYS_YES) or
1088 (prec.forecast_flag = SYS_YES) or
1089 (prec.user_supply_demand_flag = SYS_YES)
1090 or (prec.internal_repair_flag = SYS_YES)
1091 or (prec.external_repair_flag = SYS_YES)
1092 OR (prec.payback_demand_supply_flag = SYS_YES ) ) THEN -- added for Bug 5909379 Srp Additions
1093
1094 if (p_is_cont_refresh and
1095 (prec.wip_sn_flag = SYS_INCR or prec.wip_sn_flag = SYS_NO) and
1096 (prec.mds_sn_flag = SYS_INCR or prec.mds_sn_flag = SYS_NO) and
1097 (prec.fcst_sn_flag = SYS_INCR or prec.fcst_sn_flag = SYS_NO) and
1098 (prec.udmd_sn_flag = SYS_INCR or prec.udmd_sn_flag = SYS_NO ) ) then
1099 null;
1100 else
1101 tbl_nam_str := 'DEMANDS_'||v_instance_code;
1102 IF (tbl_count = 0) THEN
1103 tbl_count := tbl_count + 1;
1104 lv_TblList(tbl_count) := tbl_nam_str;
1105 ELSE
1106 lv_TblList.EXTEND; -- Extend the size
1107 tbl_count := tbl_count + 1;
1108 lv_TblList(tbl_count) := tbl_nam_str;
1109 END IF;
1110 end if;
1111 END IF; -- demands
1112
1113 IF prec.sales_order_flag = SYS_YES THEN
1114 if (p_is_cont_refresh and
1115 (prec.so_sn_flag = SYS_INCR or prec.so_sn_flag = SYS_NO)) then
1116 null;
1117 else
1118 tbl_nam_str := 'SALES_ORDERS_'||v_instance_code;
1119 IF (tbl_count = 0) THEN
1120 tbl_count := tbl_count + 1;
1121 lv_TblList(tbl_count) := tbl_nam_str;
1122 ELSE
1123 lv_TblList.EXTEND; -- Extend the size
1124 tbl_count := tbl_count + 1;
1125 lv_TblList(tbl_count) := tbl_nam_str;
1126 END IF;
1127 end if;
1128 END IF; -- sales_order_flag
1129
1130 FOR i IN 1..v_swapTblList.COUNT LOOP
1131
1132 EXECUTE IMMEDIATE ' select nvl(' ||v_swapTblList(i).column_name || ',' || MSC_CL_COLLECTION.G_STG_ODS_SWP_PHASE_0 || ') from msc_coll_parameters '
1133 || ' where instance_id = ' || v_instance_id
1134 INTO lv_swap_status;
1135
1136 If lv_swap_status = MSC_CL_COLLECTION.G_STG_ODS_SWP_PHASE_1 then
1137 lv_TblList.EXTEND; -- Extend the size
1138 tbl_count := tbl_count + 1;
1139 lv_TblList(tbl_count) := v_swapTblList(i).temp_table_name;
1140 END if;
1141 END LOOP;
1142
1143 FOR i IN 1..lv_TblList.COUNT LOOP
1144
1145 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<< '||TO_CHAR(i) ||' '||lv_TblList(i)||' >>');
1146
1147 END LOOP;
1148
1149
1150 IF NOT Exchange_Partition_PARTIAL (lv_TBlList) THEN
1151 RETURN FALSE;
1152 END IF;
1153
1154 RETURN TRUE;
1155 EXCEPTION
1159 RETURN FALSE;
1156 WHEN OTHERS THEN
1157 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<Exchange_Partition with prec PARAM>>');
1158 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1160 END Exchange_Partition;
1161
1162 /*
1163 -- added below procedure for bug 3890821
1164 -- procedure checks for the existance of a partition p_part_name;
1165 -- if not present, a new partition is created.
1166 */
1167
1168 PROCEDURE create_partition(p_table_name IN VARCHAR2,
1169 p_part_name IN VARCHAR2,
1170 p_part_type IN NUMBER,
1171 p_high_value IN VARCHAR2)
1172 IS
1173
1174 lv_sql_stmt VARCHAR2(3000);
1175 lv_msc_schema VARCHAR2(32);
1176 lv_base_part VARCHAR2(50);
1177 lv_part_to_split VARCHAR2(30);
1178 lv_part_exists NUMBER;
1179
1180 lv_tblspace_name VARCHAR2(50);
1181 lv_pct_free VARCHAR2(50);
1182 lv_pct_used VARCHAR2(50);
1183 lv_init_ext VARCHAR2(50);
1184 lv_nxt_ext VARCHAR2(50);
1185 lv_pct_inc VARCHAR2(50);
1186
1187 BEGIN
1188
1189 BEGIN
1190 SELECT application_short_name
1191 INTO lv_msc_schema
1192 FROM fnd_application
1193 WHERE application_id = 724;
1194
1195 EXCEPTION
1196 WHEN NO_DATA_FOUND THEN
1197 RAISE_APPLICATION_ERROR(-20001,
1198 'Application ID "'|| '724'
1199 ||'" is not registered in FND_APPLICATION.');
1200 END;
1201
1202
1203 lv_base_part := REPLACE(p_table_name, lv_msc_schema || '_') || '_' || '0';
1204
1205
1206 lv_part_exists := 2;
1207
1208 BEGIN
1209
1210 SELECT decode(t1.tablespace_name, NULL, '', ' TABLESPACE ' || t1.tablespace_name),
1211 decode(t1.pct_free, NULL, '', ' PCTFREE ' || t1.pct_free),
1212 decode(t1.pct_used, NULL, '', ' PCTUSED ' || t1.pct_used),
1213 decode(t1.initial_extent, NULL, '', ' INITIAL ' || t1.initial_extent),
1214 decode(t1.next_extent, NULL, '', ' NEXT ' || t1.next_extent),
1215 decode(t1.pct_increase, NULL, '', ' PCTINCREASE ' || t1.pct_increase)
1216 INTO lv_tblspace_name, lv_pct_free, lv_pct_used, lv_init_ext, lv_nxt_ext, lv_pct_inc
1217 FROM all_tab_partitions t1
1218 WHERE t1.table_name = p_table_name
1219 AND t1.partition_name = lv_base_part
1220 AND t1.table_owner = lv_msc_schema
1221 AND NOT EXISTS (SELECT 1 FROM all_tab_partitions t2
1222 WHERE t2.table_name = t1.table_name
1223 AND t2.partition_name = p_part_name
1224 AND t2.table_owner = t1.table_owner);
1225
1226 EXCEPTION
1227 WHEN NO_DATA_FOUND THEN
1228 lv_part_exists := 1;
1229
1230 END;
1231
1232 IF lv_part_exists = 2 THEN
1233
1234 IF p_part_type = SPLIT_PARTITION THEN
1235 lv_part_to_split := get_next_high_val_part(lv_msc_schema,p_table_name,p_high_value);
1236
1237 lv_sql_stmt := 'ALTER TABLE ' || lv_msc_schema || '.' || p_table_name || ' SPLIT PARTITION ' || lv_part_to_split
1238 || ' AT (' || p_high_value || ')'
1239 || ' INTO ( PARTITION ' || p_part_name
1240 || lv_pct_free || lv_pct_used
1241 || ' STORAGE(' || lv_init_ext || lv_nxt_ext || lv_pct_inc || ')' || lv_tblspace_name
1242 || ' ,PARTITION ' || lv_part_to_split
1243 || ')';
1244
1245 EXECUTE IMMEDIATE lv_sql_stmt;
1246
1247
1248 ELSE
1249
1250 lv_sql_stmt := 'ALTER TABLE ' || lv_msc_schema || '.' || p_table_name || ' ADD PARTITION ' || p_part_name
1251 || ' VALUES LESS THAN ('|| p_high_value || ')'
1252 || lv_pct_free || lv_pct_used
1253 || ' STORAGE(' || lv_init_ext || lv_nxt_ext || lv_pct_inc || ')' || lv_tblspace_name;
1254
1255 EXECUTE IMMEDIATE lv_sql_stmt;
1256
1257 END IF;
1258 END IF;
1259
1260 EXCEPTION
1261 WHEN OTHERS THEN
1262 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ERROR in procedure create_partition ');
1263 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1264
1265 END create_partition;
1266
1267 /*-----------------------------------------------------------------------------
1268 Procedure : create_st_partition
1269
1270 Description : This procedure partitions the staging table, for a newly
1271 created ERP instance.
1272
1273 Parameters : p_instance_id (IN NUMBER)
1274 Instance Id of ERP instance
1275 -----------------------------------------------------------------------------*/
1276 PROCEDURE create_st_partition (p_instance_id IN NUMBER) IS
1277
1278 lv_retval boolean;
1279 lv_dummy1 varchar2(32);
1280 lv_dummy2 varchar2(32);
1281 lv_schema varchar2(30);
1282 lv_prod_short_name varchar2(30);
1283
1284 CURSOR c_tab_list IS
1285 SELECT attribute1 application_id, attribute2 table_name
1286 FROM fnd_lookup_values
1287 WHERE lookup_type = 'MSC_STAGING_TABLE' AND
1288 enabled_flag = 'Y' AND
1289 view_application_id = 700 AND
1290 language = userenv('lang') AND
1291 attribute5 = 'L';
1292
1293 lv_sql_stmt varchar2(1000);
1294
1295 BEGIN
1299
1296
1297 FOR c_rec IN c_tab_list
1298 LOOP
1300 lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(to_number(c_rec.application_id));
1301 lv_retval := FND_INSTALLATION.GET_APP_INFO (lv_prod_short_name, lv_dummy1, lv_dummy2, lv_schema);
1302
1303 BEGIN
1304 lv_sql_stmt := ' ALTER TABLE ' || lv_schema || '.' || c_rec.table_name
1305 || ' SPLIT PARTITION ' || SUBSTR(c_rec.table_name, 5) || '_DEF '
1306 || ' VALUES (' || p_instance_id || ') INTO (PARTITION '
1307 || SUBSTR(c_rec.table_name, 8) || '_' || to_char(p_instance_id)
1308 || ' , PARTITION ' || SUBSTR(c_rec.table_name, 5) || '_DEF )';
1309
1310 EXECUTE IMMEDIATE lv_sql_stmt;
1311 EXCEPTION
1312 WHEN OTHERS THEN
1313 IF SQLCODE IN (-14322) THEN
1314 /* supress exp if partition already exists */
1315 NULL;
1316 ELSE
1317 RAISE;
1318 END IF;
1319 END;
1320
1321 END LOOP;
1322
1323 EXCEPTION
1324 WHEN OTHERS THEN
1325 RAISE;
1326 END;
1327
1328 /*-----------------------------------------------------------------------------
1329 Procedure : drop_st_partition
1330
1331 Description : This procedure drops the partition for a dropped/deleted ERP
1332 instance.
1333
1334 Parameters : p_instance_id (IN NUMBER)
1335 Instance Id of ERP instance
1336 -----------------------------------------------------------------------------*/
1337 PROCEDURE drop_st_partition (p_instance_id IN NUMBER) IS
1338
1339 lv_retval boolean;
1340 lv_dummy1 varchar2(32);
1341 lv_dummy2 varchar2(32);
1342 lv_schema varchar2(30);
1343 lv_prod_short_name varchar2(30);
1344
1345 CURSOR c_tab_list IS
1346 SELECT flv.attribute1 application_id,
1347 flv.attribute2 table_name,
1348 substr(flv.attribute2, 8) || '_' || p_instance_id partition_name
1349 FROM fnd_lookup_values flv
1350 WHERE flv.lookup_type = 'MSC_STAGING_TABLE' AND
1351 flv.enabled_flag = 'Y' AND
1352 flv.view_application_id = 700 AND
1353 flv.language = userenv('lang') AND
1354 flv.attribute5 = 'L' AND
1355 EXISTS (
1356 SELECT 1
1357 FROM all_tab_partitions atp
1358 WHERE atp.table_name = flv.attribute2 AND
1359 atp.partition_name = substr(flv.attribute2, 8) || '_' || to_char(p_instance_id)
1360 );
1361
1362 lv_sql_stmt varchar2(1000);
1363
1364 BEGIN
1365
1366 FOR c_rec IN c_tab_list
1367 LOOP
1368
1369 lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(to_number(c_rec.application_id));
1370 lv_retval := FND_INSTALLATION.GET_APP_INFO (lv_prod_short_name, lv_dummy1, lv_dummy2, lv_schema);
1371
1372 lv_sql_stmt := 'ALTER TABLE ' || lv_schema || '.' || c_rec.table_name
1373 || ' DROP PARTITION ' || c_rec.partition_name;
1374 EXECUTE IMMEDIATE lv_sql_stmt;
1375 END LOOP;
1376
1377 EXCEPTION
1378 WHEN OTHERS THEN
1379 RAISE;
1380 END;
1381
1382 /*-----------------------------------------------------------------------------
1383 Procedure : modify_st_partition_add
1384
1385 Description : This procedure adds the legacy instance_id to the default
1386 partitions' list.
1387
1388 Parameters : p_instance_id (IN NUMBER)
1389 Instance Id of Legacy instance
1390 -----------------------------------------------------------------------------*/
1391 PROCEDURE modify_st_partition_add (p_instance_id IN NUMBER) IS
1392
1393 lv_retval boolean;
1394 lv_dummy1 varchar2(32);
1395 lv_dummy2 varchar2(32);
1396 lv_schema varchar2(30);
1397 lv_prod_short_name varchar2(30);
1398
1399 CURSOR c_tab_list IS
1400 SELECT attribute1 application_id, attribute2 table_name
1401 FROM fnd_lookup_values
1402 WHERE lookup_type = 'MSC_STAGING_TABLE' AND
1403 enabled_flag = 'Y' AND
1404 view_application_id = 700 AND
1405 language = userenv('lang') AND
1406 attribute5 = 'L';
1407
1408 lv_sql_stmt varchar2(1000);
1409
1410 BEGIN
1411
1412 FOR c_rec IN c_tab_list
1413 LOOP
1414
1415 lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(to_number(c_rec.application_id));
1416 lv_retval := FND_INSTALLATION.GET_APP_INFO (lv_prod_short_name, lv_dummy1, lv_dummy2, lv_schema);
1417
1418 BEGIN
1419 lv_sql_stmt := 'ALTER TABLE ' || lv_schema || '.'
1420 || c_rec.table_name || ' MODIFY PARTITION '
1421 || SUBSTR(c_rec.table_name, 5) || '_LEG ADD VALUES (' || p_instance_id || ')';
1422 EXECUTE IMMEDIATE lv_sql_stmt;
1423 EXCEPTION
1424 WHEN OTHERS THEN
1425 IF SQLCODE IN (-14312) THEN
1426 /* supress exp if value already exists */
1427 NULL;
1428 ELSE
1429 RAISE;
1430 END IF;
1431 END;
1432 END LOOP;
1433
1434 EXCEPTION
1435 WHEN OTHERS THEN
1436 RAISE;
1437 END;
1438
1439 /*-----------------------------------------------------------------------------
1440 Procedure : modify_st_partition_drop
1441
1445 Parameters : p_instance_id (IN NUMBER)
1442 Description : This procedure drops the legacy instance_id from the default
1443 partitions' list.
1444
1446 Instance Id of Legacy instance
1447 -----------------------------------------------------------------------------*/
1448 PROCEDURE modify_st_partition_drop (p_instance_id IN NUMBER) IS
1449
1450 lv_retval boolean;
1451 lv_dummy1 varchar2(32);
1452 lv_dummy2 varchar2(32);
1453 lv_schema varchar2(30);
1454 lv_prod_short_name varchar2(30);
1455
1456 CURSOR c_tab_list IS
1457 SELECT attribute1 application_id, attribute2 table_name
1458 FROM fnd_lookup_values
1459 WHERE lookup_type = 'MSC_STAGING_TABLE' AND
1460 enabled_flag = 'Y' AND
1461 view_application_id = 700 AND
1462 language = userenv('lang') AND
1463 attribute5 = 'L';
1464
1465 lv_sql_stmt varchar2(1000);
1466
1467 BEGIN
1468
1469 FOR c_rec IN c_tab_list
1470 LOOP
1471
1472 lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(to_number(c_rec.application_id));
1473 lv_retval := FND_INSTALLATION.GET_APP_INFO (lv_prod_short_name, lv_dummy1, lv_dummy2, lv_schema);
1474
1475 BEGIN
1476 lv_sql_stmt := 'ALTER TABLE ' || lv_schema || '.' || c_rec.table_name
1477 || ' MODIFY PARTITION ' || SUBSTR(c_rec.table_name, 5) || '_LEG '
1478 || ' DROP VALUES (' || p_instance_id || ')';
1479 EXECUTE IMMEDIATE lv_sql_stmt;
1480 EXCEPTION
1481 WHEN OTHERS THEN
1482 IF SQLCODE IN (-14313) THEN
1483 /* supress exp if value does not exist */
1484 NULL;
1485 ELSE
1486 RAISE;
1487 END IF;
1488 END;
1489 END LOOP;
1490
1491 EXCEPTION
1492 WHEN OTHERS THEN
1493 RAISE;
1494 END;
1495
1496 FUNCTION create_temp_table_index (p_uniqueness IN VARCHAR2,
1497 p_part_table IN VARCHAR2,
1498 p_temp_table IN VARCHAR2,
1499 p_instance_code IN VARCHAR2,
1500 p_instance_id IN NUMBER,
1501 p_is_plan IN NUMBER,
1502 p_error_level IN NUMBER
1503 )
1504 RETURN NUMBER
1505 IS
1506 CURSOR c_index_name( cp_tblname IN VARCHAR2,
1507 cp_inscode IN VARCHAR2,
1508 cp_uniqueness IN VARCHAR2,
1509 cp_partname IN VARCHAR2
1510 ) IS
1511 SELECT ipa.index_name,
1512 DECODE( di.uniqueness,'UNIQUE','UNIQUE',NULL) uniqueness,
1513 SUBSTRB( ipa.index_name,5)||'_'||cp_inscode,
1514 ipa.tablespace_name,
1515 decode(ipa.initial_extent, NULL, '', ' INITIAL ' || ipa.initial_extent),
1516 decode(ipa.next_extent, NULL, '', ' NEXT ' || ipa.next_extent),
1517 decode(ipa.pct_increase, NULL, '', ' PCTINCREASE ' || ipa.pct_increase),
1518 decode(ipa.pct_free, NULL, '', ' PCTFREE ' || ipa.pct_free),
1519 decode(ipa.ini_trans, NULL, '', ' INITRANS ' || ipa.ini_trans),
1520 decode(ipa.max_trans, NULL, '', ' MAXTRANS ' || ipa.max_trans),
1521 di.index_type,
1522 dt.EXTENT_MANAGEMENT,
1523 dt.ALLOCATION_TYPE
1524 FROM ALL_IND_PARTITIONS ipa,
1525 ALL_INDEXES di,
1526 dba_tablespaces dt
1527 WHERE ipa.index_owner= v_msc_schema
1528 AND di.table_owner= v_msc_schema
1529 AND ipa.partition_name=cp_partname
1530 AND di.table_name= cp_tblname
1531 AND di.uniqueness= cp_uniqueness
1532 AND ipa.index_name= di.index_name
1533 AND ipa.index_owner= di.owner
1534 AND ipa.tablespace_name= dt.tablespace_name;
1535
1536 CURSOR index_columns ( p_msc_schema IN VARCHAR2,
1537 p_index_name IN VARCHAR2,
1538 p_table_name IN VARCHAR2)
1539 IS
1540 select ai.INDEX_TYPE, aic.column_name, aie.column_expression, aic.column_position
1541 from all_indexes ai, all_ind_columns aic, all_ind_expressions aie
1542 where ai.index_name = aic.index_name
1543 and ai.owner = aic.index_owner
1544 and ai.table_name = aic.table_name
1545 and ai.table_owner = aic.table_owner
1546 and aic.index_name = aie.index_name (+)
1547 and aic.index_owner = aie.index_owner (+)
1548 and aic.table_name = aie.table_name (+)
1549 and aic.table_owner = aie.table_owner (+)
1550 and aic.column_position = aie.column_position (+)
1551 AND aic.index_owner= p_msc_schema
1552 AND aic.table_owner= p_msc_schema
1553 AND aic.index_name = p_index_name
1554 AND aic.table_name = p_table_name
1555 order by aic.column_position;
1556
1557 lv_indexColList IndCharTblTyp;
1558 lv_indexColList1 IndCharTblTyp;
1559 lv_indColList IndNmTblTyp;
1560
1561 lv_indexColListCnt NUMBER;
1562
1563 lv_index_name VARCHAR2(30);
1564 lv_uniqueness VARCHAR2(9);
1565 lv_temp_index_name VARCHAR2(30);
1566
1567 lv_tablespace_name VARCHAR2(30);
1568
1569 lv_min_extent VARCHAR2(40);
1570 lv_max_extent VARCHAR2(40);
1571
1572
1573 lv_index_type VARCHAR2(27);
1574 lv_segment_space_management VARCHAR2(20);
1575
1579
1576 lv_partition_name VARCHAR2(30);
1577 lv_return_status VARCHAR2(2048);
1578 lv_msg_data VARCHAR2(2048);
1580 lv_deg_parallel NUMBER;
1581 lv_ind_stmt VARCHAR2(2048);
1582 lv_sql_stmt2 VARCHAR2(2048);
1583 lv_sql_stmt3 VARCHAR2(2048);
1584 lv_sql_stmt4 VARCHAR2(2048);
1585 lv_retval NUMBER := MSC_CL_COLLECTION.G_SUCCESS;
1586 TYPE CharTblTyp IS TABLE OF VARCHAR2(4000);
1587 lv_error_msg CharTblTyp;
1588 lv_extent_management VARCHAR2(10);
1589 lv_allocation_type VARCHAR2(9);
1590 lv_initial_extent VARCHAR2(50);
1591 lv_next_extent VARCHAR2(50);
1592 lv_pct_increase VARCHAR2(50);
1593 lv_storage_clause VARCHAR2(255);
1594 lv_pct_free VARCHAR2(50);
1595 lv_ini_trans VARCHAR2(50);
1596 lv_max_trans VARCHAR2(50);
1597
1598
1599 BEGIN
1600 trc('st:create_temp_table_index');
1601 /* creatIndexOnTempraryTable; */
1602
1603 /* this profile option will determine the number of parallel threads to be used in creating the index in parallel*/
1604
1605 SELECT to_number(fnd_profile.value('MSC_INDEX_PARALLEL_THREADS'))
1606 INTO lv_deg_parallel
1607 FROM dual;
1608
1609 msc_manage_plan_partitions.get_partition_name
1610 (-1,
1611 p_instance_id,
1612 p_part_table,
1613 p_is_plan,
1614 lv_partition_name,
1615 lv_return_status,
1616 lv_msg_data);
1617
1618 OPEN c_index_name
1619 (p_part_table,
1620 p_instance_code,
1621 p_uniqueness,
1622 lv_partition_name);
1623
1624 LOOP
1625 FETCH c_index_name
1626 INTO lv_index_name,
1627 lv_uniqueness,
1628 lv_temp_index_name,
1629 lv_tablespace_name,
1630 lv_initial_extent,
1631 lv_next_extent,
1632 lv_pct_increase,
1633 lv_pct_free,
1634 lv_ini_trans,
1635 lv_max_trans,
1636 lv_index_type,
1637 lv_extent_management,
1638 lv_allocation_type;
1639
1640 EXIT WHEN c_index_name%NOTFOUND;
1641
1642 /* select the index column order by the column position */
1643
1644 IF (lv_index_type <> 'NORMAL') THEN
1645 lv_indexColList :=lv_indexColList1;
1646
1647 OPEN index_columns(v_msc_schema,
1648 lv_index_name,
1649 p_part_table);
1650
1651 lv_indexColListCnt :=0;
1652
1653 LOOP
1654 FETCH index_columns INTO
1655 lv_indexColList(lv_indexColListCnt).l_index_type,
1656 lv_indexColList(lv_indexColListCnt).l_column_name,
1657 lv_indexColList(lv_indexColListCnt).l_column_expression,
1658 lv_indexColList(lv_indexColListCnt).l_column_position;
1659
1660 EXIT WHEN index_columns%NOTFOUND;
1661 IF (lv_indexColList(lv_indexColListCnt).l_column_expression is not null) AND ( lv_indexColList(lv_indexColListCnt).l_index_type='FUNCTION-BASED NORMAL') THEN
1662 lv_indexColList(lv_indexColListCnt).l_column_name := lv_indexColList(lv_indexColListCnt).l_column_expression;
1663 END IF;
1664 lv_indexColListCnt := lv_indexColListCnt + 1;
1665 END LOOP;
1666
1667 CLOSE index_columns;
1668
1669 ELSE
1670 SELECT b.column_name
1671 BULK COLLECT
1672 INTO lv_indColList
1673 FROM ALL_IND_COLUMNS b
1674 WHERE b.index_owner = v_msc_schema AND
1675 b.table_owner = v_msc_schema AND
1676 b.index_name = lv_index_name AND
1677 b.table_name = p_part_table
1678 ORDER BY b.COLUMN_POSITION;
1679
1680 lv_indexColListCnt:= SQL%ROWCOUNT;
1681
1682 END IF;
1683
1684 /* prepare the sql statement */
1685 v_sql_stmt := ' CREATE '||lv_uniqueness||' INDEX '||lv_temp_index_name
1686 ||' ON '||p_temp_table ||'(';
1687
1688 IF (lv_index_type <> 'NORMAL') THEN
1689 FOR j IN 0..lv_indexColList.count-1 LOOP
1690 IF j= 0 THEN
1691 lv_ind_stmt:= lv_indexColList(j).l_column_name;
1692 ELSE
1693 lv_ind_stmt:= lv_ind_stmt||','||lv_indexColList(j).l_column_name;
1694 END IF;
1695 END LOOP;
1696
1697 ELSE
1698 FOR j IN 1..lv_indexColListCnt LOOP
1699 IF j= 1 THEN
1700 lv_ind_stmt:= lv_indColList(j);
1701 ELSE
1702 lv_ind_stmt:= lv_ind_stmt||','||lv_indColList(j);
1703 END IF;
1704 END LOOP;
1705
1706 END IF;
1707
1708 IF lv_tablespace_name IS NULL THEN
1709 v_sql_stmt:= v_sql_stmt || lv_ind_stmt || ') PARALLEL '|| lv_deg_parallel ;
1710 ELSE
1711 v_sql_stmt:= v_sql_stmt || lv_ind_stmt || ') PARALLEL '|| lv_deg_parallel ||' TABLESPACE '||lv_tablespace_name;
1712 END IF ;
1713
1717 ELSE
1714 IF (lv_extent_management = 'DICTIONARY' OR (lv_extent_management = 'LOCAL' AND lv_allocation_type = 'USER')) THEN
1715 IF (lv_initial_extent is null and lv_next_extent is null and lv_pct_increase is null) THEN
1716 lv_storage_clause:='';
1718 lv_storage_clause:=' STORAGE( ' || lv_initial_extent || lv_next_extent || lv_pct_increase ||')';
1719 END IF;
1720 END IF ;
1721
1722 IF ( lv_extent_management = 'LOCAL' AND lv_allocation_type = 'SYSTEM') THEN
1723 lv_next_extent :='';
1724 lv_pct_increase := '';
1725 IF (lv_initial_extent is null and lv_next_extent is null and lv_pct_increase is null) THEN
1726 lv_storage_clause:='';
1727 ELSE
1728 lv_storage_clause:=' STORAGE( ' || lv_initial_extent || lv_next_extent || lv_pct_increase ||')';
1729 END IF;
1730 END IF;
1731
1732 IF ( lv_extent_management = 'LOCAL' AND lv_allocation_type = 'UNIFORM') THEN
1733 lv_storage_clause:='';
1734 END IF;
1735 v_sql_stmt:= v_sql_stmt||lv_storage_clause ||lv_pct_free ||lv_ini_trans ||lv_max_trans;
1736
1737
1738
1739 v_sql_stmt:= v_sql_stmt || ' COMPUTE STATISTICS';
1740
1741 /* execute the sql statement */
1742
1743 BEGIN
1744 /* create index */
1745 ad_ddl.do_ddl(applsys_schema => v_applsys_schema,
1746 application_short_name => 'MSC',
1747 statement_type => AD_DDL.CREATE_INDEX,
1748 statement => v_sql_stmt,
1749 object_name => lv_temp_index_name);
1750 EXCEPTION
1751 WHEN OTHERS THEN
1752 /* handle unique index violation exception */
1753
1754 IF SQLCODE IN (-00001, -01452, -12801) THEN
1755 IF p_error_level = MSC_CL_COLLECTION.G_ERROR THEN
1756 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ERROR');
1757 ELSE
1758 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'WARNING');
1759 END IF;
1760
1761 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Unique index violated - ' || lv_temp_index_name);
1762 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1763
1764 lv_sql_stmt2 := 'SELECT ';
1765 lv_sql_stmt3 := 'WHERE ';
1766
1767 IF (lv_index_type <> 'NORMAL') THEN
1768
1769 FOR j IN 0..lv_indexColList.count-1 LOOP
1770 lv_sql_stmt2 := lv_sql_stmt2 || '''' || lv_indexColList(j).l_column_name || '''' || ' || '' '' || '
1771 || 't1.' || lv_indexColList(j).l_column_name;
1772
1773 lv_sql_stmt3 := lv_sql_stmt3 || ' t1.' || lv_indexColList(j).l_column_name
1774 || ' = t2.' || lv_indexColList(j).l_column_name || ' AND ';
1775
1776 IF j <> lv_indexColList.count-1 THEN
1777 lv_sql_stmt2 := lv_sql_stmt2 || '|| '' / '' || ';
1778 END IF;
1779 END LOOP;
1780
1781 ELSE
1782 FOR j IN 1..lv_indexColListCnt LOOP
1783 lv_sql_stmt2 := lv_sql_stmt2 || '''' || lv_indColList(j) || '''' || ' || '' '' || '
1784 || 't1.' || lv_indColList(j);
1785 lv_sql_stmt3 := lv_sql_stmt3 || 'nvl( t1.' || lv_indColList(j)
1786 || ',''-99999'') = nvl(t2.' || lv_indColList(j) || ',''-99999'') AND ';
1787
1788 IF j <> lv_indexColListCnt THEN
1789 lv_sql_stmt2 := lv_sql_stmt2 || '|| '' / '' || ';
1790 END IF;
1791 END LOOP;
1792 END IF;
1793
1794 lv_sql_stmt2 := lv_sql_stmt2 || ' err_text FROM ' || p_temp_table || ' t1 WHERE EXISTS '
1795 || '(SELECT 1 FROM ' || p_temp_table || ' t2 ' || lv_sql_stmt3
1796 || 't1.rowid < t2.rowid)';
1797
1798 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '======= Below records violate the unique constraint =======');
1799
1800 EXECUTE IMMEDIATE lv_sql_stmt2 BULK COLLECT INTO lv_error_msg;
1801
1802 FOR j IN 1..lv_error_msg.COUNT LOOP
1803 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_error_msg(j) );
1804 END LOOP;
1805
1806 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '===========================================================');
1807
1808 IF p_error_level = MSC_CL_COLLECTION.G_ERROR THEN
1809 lv_retval := MSC_CL_COLLECTION.G_ERROR;
1810 RAISE;
1811 ELSE
1812 lv_retval := MSC_CL_COLLECTION.G_WARNING;
1813 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The above records would not be collected');
1814 lv_sql_stmt4 := 'DELETE FROM ' || p_temp_table || ' t1 WHERE EXISTS '
1815 || '(SELECT 1 FROM ' || p_temp_table || ' t2 ' || lv_sql_stmt3
1816 || 't1.rowid < t2.rowid)';
1817
1818 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'deleting the duplicate row ' ||lv_sql_stmt4 );
1819
1820 EXECUTE IMMEDIATE lv_sql_stmt4;
1821 COMMIT;
1822
1823 BEGIN
1824 ad_ddl.do_ddl( applsys_schema => v_applsys_schema,
1828 object_name => lv_temp_index_name);
1825 application_short_name => 'MSC',
1826 statement_type => AD_DDL.CREATE_INDEX,
1827 statement => v_sql_stmt,
1829 EXCEPTION
1830 WHEN OTHERS THEN
1831 lv_retval := MSC_CL_COLLECTION.G_ERROR;
1832 RAISE;
1833 END;
1834 END IF;
1835 ELSE
1836 lv_retval := MSC_CL_COLLECTION.G_ERROR;
1837 RAISE;
1838 END IF;
1839 END;
1840 trc('Index creation done - ' || lv_temp_index_name);
1841
1842 END LOOP;
1843 CLOSE c_index_name;
1844
1845 trc('en:create_temp_table_index');
1846 RETURN lv_retval;
1847
1848 EXCEPTION
1849 WHEN OTHERS THEN
1850 lv_retval := MSC_CL_COLLECTION.G_ERROR;
1851 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<create_temp_table_index>>');
1852 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '<<SQL>>'||v_sql_stmt);
1853 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1854 RETURN lv_retval;
1855 END create_temp_table_index;
1856
1857
1858 /*********************** PREPLACE CHANGE END *************************/
1859 ------------- Clean Repaiir junk inst Part--------------------------------
1860 PROCEDURE EXEC_DDL(qry varchar2)
1861 IS
1862 BEGIN
1863 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1,qry);
1864 EXECUTE IMMEDIATE qry ;
1865 END EXEC_DDL;
1866
1867 PROCEDURE list_create_def_part_stg ( ERRBUF OUT NOCOPY VARCHAR2,
1868 RETCODE OUT NOCOPY NUMBER,
1869 p_mode number default 0) -- 0 -- List; 1- drop
1870 IS
1871 lv_qry_add_part varchar2(2000);
1872 lv_schema varchar2(30);
1873 BEGIN
1874 lv_schema:=msc_util.get_schema_name(724);
1875 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Following DEFAULT partitions are missing');
1876 FOR tab in (select table_name
1877 from MSC_STAGING_TABLE_V mst
1878 where partition_type <> 'U'
1879 and not exists (select 1 from all_tab_partitions ATP
1880 where ATP.table_owner = lv_schema
1881 and atp.table_name=mst.table_name
1882 and partition_name like '%_DEF') )
1883 loop
1884 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,tab.table_name||'.'||substr(tab.table_name,5)||'_DEF');
1885 lv_qry_add_part := 'ALTER TABLE '||lv_schema||'.'||tab.table_name||' ADD PARTITION '||substr(tab.table_name,5)||'_DEF VALUES (DEFAULT)';
1886 IF p_mode = 1 then
1887 EXEC_DDL(lv_qry_add_part);
1888 end if;
1889 end loop;
1890 EXCEPTION
1891 WHEN OTHERS THEN
1892 ERRBUF := SQLERRM;
1893 RETCODE := G_ERROR;
1894 RAISE;
1895 END list_create_def_part_stg;
1896
1897 PROCEDURE list_drop_bad_staging_part ( ERRBUF OUT NOCOPY VARCHAR2,
1898 RETCODE OUT NOCOPY NUMBER,
1899 p_mode number default 0) -- 0 -- List; 1- drop
1900 IS
1901 TYPE cur_typ IS REF CURSOR;
1902 lv_inst_str VARCHAR2(2000);
1903 lv_leg_inst_str VARCHAR2(2000);
1904 --lv_qry_str VARCHAR2(4000);
1905 --lv_tab VARCHAR2(30);
1906 --lv_tab_Part VARCHAR2(30);
1907 --row_limit number;
1908 --lv_dummy1 VARCHAR2(30);
1909 --lv_dummy2 VARCHAR2(30);
1910 lv_schema VARCHAR2(30);
1911 --lv_source_schema VARCHAR2(30);
1912 --lv_schema_short_nm VARCHAR2(30);
1913 --lv_err_flag BOOLEAN :=FALSE;
1914 lv_qry_drop_part varchar2(1000);
1915 lv_Part_high_value long;
1916 lv_Part_high_val_len number;
1917 lv_Part_inst_id varchar2(100);
1918 IS_BAD_PARTITION BOOLEAN;
1919 l_tablen BINARY_INTEGER;
1920 l_tab DBMS_UTILITY.uncl_array;
1921 lv_high_val_str varchar2(2000);
1922 lv_str varchar2(2000);
1923 i number :=0;
1924
1925 CURSOR c_tab_part(p_schema varchar2) IS
1926 SELECT b.table_name
1927 ,b.partition_name
1928 ,b.high_value
1929 ,b.high_value_length
1930 --,SUBSTR(b.partition_name,INSTR(partition_name,'_',-1)+1) part_inst_id
1931 FROM MSC_STAGING_TABLE_V a,DBA_TAB_PARTITIONS b
1932 WHERE a.table_name = b.table_name
1933 AND b.table_owner = p_schema
1934 AND a.PARTITION_TYPE <> 'U' -- Table is Partitioned
1935 ORDER BY a.table_name ;
1936 --AND b.partition_name like substr( a.table_name,8)||'%';
1937
1938
1939 BEGIN
1940
1941 lv_schema:=msc_util.get_schema_name(724);
1942 --
1943 -- Generate List of Instances passed --
1944 lv_inst_str := ',';
1945 lv_leg_inst_str := ', ';
1946 FOR inst IN (select instance_id,instance_type from MSC_APPS_INSTANCES )
1947 LOOP
1948 if inst.instance_type <> 3 then
1949 lv_inst_str := lv_inst_str || inst.instance_id || ',' ;
1950 else
1951 lv_leg_inst_str := lv_leg_inst_str || inst.instance_id || ', ' ;
1952 end if;
1953 END LOOP;
1954
1955 --End Generate List of Instances passed --
1956 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'List of Junk Stg Partitions:-');
1957 FOR tab in c_tab_part(lv_schema)
1958 LOOP
1959 --
1963 AND tab.partition_name <> substr(tab.table_name,5)||'_DEF')
1960 IF ( (INSTR(lv_inst_str,','||SUBSTR(tab.partition_name,INSTR(tab.partition_name,'_',-1)+1)||',') < 1
1961 OR SUBSTR(tab.table_name,8) <> SUBSTR(tab.partition_name,1,INSTR(tab.partition_name,'_',-1)- 1))
1962 AND tab.partition_name <> substr(tab.table_name,5)||'_LEG'
1964 then -- not in list
1965 IS_BAD_PARTITION := TRUE;
1966
1967 ELSE -- check the High Value
1968 IF tab.partition_name = substr(tab.table_name,5)||'_DEF' THEN
1969 NULL; -- DO nothing
1970 ELSIF tab.partition_name = substr(tab.table_name,5)||'_LEG' THEN
1971 for inst in (select instance_id from msc_apps_instances where instance_type = 3 ) loop
1972 if instr(', '||substr(tab.high_value,1,tab.high_value_length)||',' , ', '||inst.instance_id||',') < 1 then
1973 IS_BAD_PARTITION := TRUE;
1974 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_2,'Leg part marked as wrong1:-'||tab.table_name||'.'||tab.partition_name);
1975 end if;
1976 end loop;
1977
1978 -- check if there is any extra value in the high_value of leg part
1979 lv_high_val_str := ','||substr(tab.high_value,1,tab.high_value_length)||',';
1980 i := 0;
1981 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_high_val_str--'||lv_high_val_str);
1982 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_leg_inst_str--'||lv_leg_inst_str);
1983 LOOP
1984 i := i+1;
1985 lv_str := substr (lv_high_val_str,instr(lv_high_val_str,',',1,i) +1
1986 ,instr(lv_high_val_str,',',1,i+1)-instr(lv_high_val_str,',',1,i) -1);
1987 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'lv_str--'||lv_str);
1988 exit when lv_str is null;
1989 if lv_str in ('0',' NULL') then
1990 NULL;
1991 ELSIF instr(lv_leg_inst_str,','||lv_str||',') < 1 then
1992 IS_BAD_PARTITION := TRUE;
1993 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Leg part marked as wrong2:-'||tab.table_name||'.'||tab.partition_name);
1994 end if;
1995 END LOOP;
1996
1997
1998 ELSIF substr(tab.high_value,1,tab.high_value_length) <> SUBSTR(tab.partition_name,INSTR(tab.partition_name,'_',-1)+1) then
1999 IS_BAD_PARTITION := TRUE;
2000 END IF;
2001 END IF;
2002
2003 IF IS_BAD_PARTITION THEN
2004 BEGIN
2005 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,tab.table_name||'.'||tab.partition_name);
2006 lv_qry_drop_part := 'ALTER TABLE '||lv_schema||'.'||tab.table_name||' DROP PARTITION '||tab.partition_name;
2007 IF p_mode = 1 then
2008 EXEC_DDL(lv_qry_drop_part);
2009 end if;
2010 EXCEPTION
2011 WHEN OTHERS THEN
2012 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'ERROR while executing --'||lv_qry_drop_part);
2013 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
2014 RAISE;
2015 END;
2016 IS_BAD_PARTITION := FALSE;
2017 END IF;
2018 END LOOP;
2019 EXCEPTION
2020 WHEN OTHERS THEN
2021 ERRBUF := SQLERRM;
2022 RETCODE := G_ERROR;
2023 RAISE;
2024 END list_drop_bad_staging_part;
2025 --1
2026 PROCEDURE list_drop_bad_ods_inst_part ( ERRBUF OUT NOCOPY VARCHAR2,
2027 RETCODE OUT NOCOPY NUMBER,
2028 p_mode number default 0) -- 0 -- List; 1- repair
2029 IS
2030 lv_inst_str VARCHAR2(2000);
2031 lv_schema VARCHAR2(30);
2032 lv_qry_drop_part varchar2(1000);
2033 lv_Part_high_value long;
2034 lv_Part_high_val_len number;
2035 lv_Part_inst_id varchar2(100);
2036 lv_inst_col varchar2(1);
2037 lv_plan_col varchar2(1);
2038
2039 cursor c_tab_part(p_schema varchar2) IS
2040 SELECT b.table_name
2041 ,b.partition_name
2042 ,b.high_value
2043 ,b.high_value_length
2044 ,SUBSTR(b.partition_name,INSTR(b.partition_name,'__',-1)+2) part_inst_id
2045 ,nvl(instance_id_flag,'N') instance_id_flag
2046 ,nvl(plan_id_flag,'N') plan_id_flag
2047 FROM MSC_ODS_TABLE_V a,DBA_TAB_PARTITIONS b
2048 WHERE a.table_name = b.table_name
2049 AND b.table_owner = p_schema
2050 AND a.instance_id_flag = 'Y'
2051 AND a.PARTITION_TYPE <> 'U' -- Table is Partitioned
2052 AND NVL(a.global_flag,'-1')<>'G'
2053 AND b.partition_name like substr( a.table_name,5)||'%'
2054 AND INSTR(b.partition_name,'__') > 0;
2055 BEGIN
2056
2057 lv_schema:=msc_util.get_schema_name(724);
2058
2059 -- row_limit := TO_NUMBER( FND_PROFILE.VALUE('MRP_PURGE_BATCH_SIZE'));
2060 --
2061 -- Generate List of Instances passed --
2062 lv_inst_str := ',';
2063 FOR inst IN (select instance_id from MSC_INST_PARTITIONS )
2064 LOOP
2065 lv_inst_str := lv_inst_str || inst.instance_id || ',' ;
2066 END LOOP;
2067
2068 --End Generate List of Instances passed --
2069 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'List of junk ODS Partitions');
2070 FOR tab in c_tab_part(lv_schema)
2071 LOOP
2075 BEGIN
2072 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'|tab.part_inst_id:-'||tab.part_inst_id||';');
2073 --IF SUBSTR(tabs.partition_name,INSTR(tabs.partition_name,'__')+2) NOT IN lv_inst_str then
2074 IF INSTR(lv_inst_str,','||SUBSTR(tab.partition_name,INSTR(tab.partition_name,'__')+2)||',') < 1 then -- not in list
2076 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,tab.table_name||'.'||tab.partition_name);
2077 lv_qry_drop_part := 'ALTER TABLE '||lv_schema||'.'||tab.table_name||' DROP PARTITION '||tab.partition_name;
2078 IF p_mode = 1 then
2079 EXEC_DDL(lv_qry_drop_part);
2080 end if;
2081 EXCEPTION
2082 WHEN OTHERS THEN
2083 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'ERROR while executing --'||lv_qry_drop_part);
2084 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
2085 RAISE;
2086 END;
2087 ELSE
2088
2089 if tab.instance_id_flag = 'Y' and tab.plan_id_flag = 'Y' then
2090 lv_Part_inst_id := '-1, '||to_char(to_number(tab.part_inst_id)+1);
2091 else
2092 lv_Part_inst_id := to_char(to_number(tab.part_inst_id)+1);
2093 end if;
2094
2095 if substr(tab.high_value,1,tab.high_value_length) <> lv_Part_inst_id then
2096 BEGIN
2097 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,tab.table_name||'.'||tab.partition_name);
2098 lv_qry_drop_part := 'ALTER TABLE '||lv_schema||'.'||tab.table_name||' DROP PARTITION '||tab.partition_name;
2099 IF p_mode = 1 then
2100 EXEC_DDL(lv_qry_drop_part);
2101 end if;
2102 EXCEPTION
2103 WHEN OTHERS THEN
2104 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'ERROR while executing --'||lv_qry_drop_part);
2105 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
2106 RAISE;
2107 END;
2108 end if;
2109 END IF;
2110 END LOOP;
2111
2112 EXCEPTION
2113 WHEN OTHERS THEN
2114 ERRBUF := SQLERRM;
2115 RETCODE := G_ERROR;
2116 RAISE;
2117 END list_drop_bad_ods_inst_part;
2118
2119 PROCEDURE list_create_missing_ods_partn( ERRBUF OUT NOCOPY VARCHAR2,
2120 RETCODE OUT NOCOPY NUMBER,
2121 p_mode number default 0)
2122 IS
2123 CURSOR missing_ods_part(cp_schema varchar2) is
2124 select b.table_name
2125 ,a.instance_id
2126 ,nvl(instance_id_flag,'N') instance_id_flag
2127 ,nvl(plan_id_flag,'N') plan_id_flag
2128 from msc_inst_partitions a,
2129 MSC_ODS_TABLE_V b
2130 WHERE b.PARTITION_TYPE='R'
2131 AND NOT EXISTS ( select 1
2132 FROM all_tab_partitions atp
2133 WHERE atp.table_name = b.table_name AND
2134 atp.table_owner = cp_schema AND
2135 atp.partition_name = substr(b.table_name, 5) || '__' || to_char(a.instance_id)
2136 )
2137 ;
2138 lv_high_value varchar2(1000);
2139 lv_part_name varchar2(1000);
2140 lv_schema VARCHAR2(30);
2141
2142 BEGIN
2143 lv_schema:=msc_util.get_schema_name(724);
2144 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Following Partitions to be created');
2145 FOR tab IN missing_ods_part(lv_schema)
2146 LOOP
2147 if tab.instance_id_flag = 'Y' and tab.plan_id_flag = 'Y' then
2148 lv_high_value := '-1, ' || to_char(tab.instance_id+1);
2149 elsif tab.instance_id_flag = 'Y' and tab.plan_id_flag = 'N' then
2150 lv_high_value := to_char(tab.instance_id+1);
2151 else
2152 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Partitioned ODS Table '||tab.table_name||' does not have Instance_id as expected. Pls investigate');
2153 ERRBUF := 'Partitioned ODS Table '||tab.table_name||' does not have Instance_id as expected. Pls investigate';
2154 RETCODE := G_WARNING;
2155 end if;
2156 lv_part_name := substr(tab.table_name, 5) || '__' || tab.instance_id;
2157 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Create Part:-'||tab.table_name||'.'||lv_part_name);
2158 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,tab.table_name||'.'||lv_part_name);
2159 IF p_mode = 1 THEN
2160 --GET_NEXT_PART(lv_high_value)
2161 MSC_CL_EXCHANGE_PARTTBL.create_partition(tab.table_name,
2162 lv_part_name,
2163 1, --split
2164 lv_high_value);
2165 END IF;
2166 END LOOP;
2167 EXCEPTION
2168 WHEN OTHERS THEN
2169 ERRBUF := SQLERRM;
2170 RETCODE := G_ERROR;
2171 RAISE;
2172 END list_create_missing_ods_partn;
2173
2174 PROCEDURE list_create_missing_stg_part( ERRBUF OUT NOCOPY VARCHAR2,
2175 RETCODE OUT NOCOPY NUMBER,
2176 p_mode number default 0)
2177 IS
2178
2179 lv_schema varchar2(30);
2180
2181 CURSOR c_tab_list(cp_owner varchar2,cp_table varchar2) IS
2182 SELECT mst.application_id,
2183 mst.table_name,
2184 mai.instance_id instance_id,
2185 mai.instance_type instance_type
2186 FROM msc_staging_table_v mst,
2190 (
2187 msc_apps_instances mai
2188 WHERE mst.table_name = cp_table AND
2189 mst.PARTITION_TYPE = 'L' AND
2191 mai.instance_type = 3 OR
2192 NOT EXISTS (
2193 select 1
2194 FROM all_tab_partitions atp
2195 WHERE atp.table_name = mst.table_name AND
2196 atp.table_owner = cp_owner AND
2197 atp.partition_name = substr(mst.table_name, 8) || '_' || to_char(mai.instance_id)
2198 )
2199 )
2200 order by mst.table_name;
2201
2202 cursor c_leg_part(cp_owner varchar2,cp_table varchar2) IS
2203 SELECT 1
2204 from all_tab_partitions
2205 where table_owner = cp_owner
2206 AND table_name = cp_table
2207 AND partition_name = substr(table_name,5)||'_LEG';
2208
2209
2210
2211
2212 lv_sql_stmt VARCHAR2(1000);
2213 tbl_name VARCHAR2(30);
2214 lv_count number;
2215
2216 BEGIN
2217
2218 lv_schema:=msc_util.get_schema_name(724);
2219
2220 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'Following Partitions to be created');
2221 for stg_tab in (select table_name from msc_staging_table_v where PARTITION_TYPE = 'L' ) loop
2222
2223 open c_leg_part (lv_schema,stg_tab.table_name);
2224 fetch c_leg_part into lv_count;
2225 if c_leg_part%notfound then
2226 lv_sql_stmt :='ALTER TABLE '||lv_schema||'.'||stg_tab.table_name
2227 ||' SPLIT PARTITION '||substr(stg_tab.table_name,5)||'_DEF'
2228 ||' VALUES (0, NULL) INTO (PARTITION '||substr(stg_tab.table_name,5)||'_LEG, PARTITION '|| substr(stg_tab.table_name,5)||'_DEF )';
2229
2230 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,stg_tab.table_name||'.'||substr(stg_tab.table_name,5)||'_LEG');
2231 IF p_mode = 1 then
2232 EXEC_DDL(lv_sql_stmt);
2233 end if;
2234 end if;
2235
2236 close c_leg_part;
2237
2238 --
2239 FOR c_rec IN c_tab_list(lv_schema,stg_tab.table_name)
2240 LOOP
2241 IF c_rec.instance_type <> 3 THEN
2242 lv_sql_stmt := ' ALTER TABLE ' || lv_schema || '.' || c_rec.table_name
2243 || ' SPLIT PARTITION ' || SUBSTR(c_rec.table_name, 5) || '_DEF '
2244 || ' VALUES (' || c_rec.instance_id || ') INTO (PARTITION '
2245 || SUBSTR(c_rec.table_name, 8) || '_' || to_char(c_rec.instance_id)
2246 || ' , PARTITION ' || SUBSTR(c_rec.table_name, 5) || '_DEF )';
2247
2248 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, c_rec.table_name||'.'||SUBSTR(c_rec.table_name, 8) || '_' || to_char(c_rec.instance_id));
2249 IF p_mode = 1 then
2250 EXEC_DDL(lv_sql_stmt);
2251 end if;
2252 ELSE
2253 BEGIN
2254 lv_sql_stmt := ' ALTER TABLE ' || lv_schema || '.' || c_rec.table_name
2255 || ' MODIFY PARTITION ' || SUBSTR(c_rec.table_name, 5) || '_LEG'
2256 || ' ADD VALUES (' || c_rec.instance_id || ')';
2257
2258 IF p_mode = 1 then
2259 EXEC_DDL(lv_sql_stmt);
2260 end if;
2261 EXCEPTION
2262 WHEN OTHERS THEN
2263 IF SQLCODE IN (-14312) THEN
2264 /* supress exp if value already exists */
2265 NULL;
2266 ELSE
2267 RAISE;
2268 END IF;
2269 END;
2270 END IF;
2271
2272 END LOOP;
2273
2274 end loop;
2275 EXCEPTION
2276 WHEN OTHERS THEN
2277 ERRBUF := SQLERRM;
2278 RETCODE := G_ERROR;
2279 RAISE;
2280 END list_create_missing_stg_part;
2281
2282 PROCEDURE Clean_Instance_partitions( ERRBUF OUT NOCOPY VARCHAR2,
2283 RETCODE OUT NOCOPY NUMBER,
2284 p_mode number default 0)
2285 IS
2286 begin
2287 list_create_def_part_stg(ERRBUF,RETCODE,p_mode);
2288 list_drop_bad_staging_part(ERRBUF,RETCODE,p_mode);
2289 list_drop_bad_ods_inst_part(ERRBUF,RETCODE,p_mode);
2290 list_create_missing_stg_part(ERRBUF,RETCODE,p_mode);
2291 list_create_missing_ods_partn(ERRBUF,RETCODE,p_mode);
2292 end Clean_Instance_partitions;
2293
2294
2295 FUNCTION get_next_high_val_part(powner varchar2,p_tab varchar2,p_high_val varchar2) return VARCHAR2 IS
2296 BEGIN
2297 For i in (select partition_name,high_value,high_value_length
2298 from all_tab_partitions
2299 where table_name = p_tab
2300 AND table_owner= powner
2301 order by partition_position
2302 )
2303 LOOP
2304 IF COMPARE_PARTITION_BOUND(powner, p_tab, 'TABLE', p_high_val, i.high_value)=2 THEN
2305 RETURN i.partition_name ;
2306 END IF;
2307 end loop;
2308 RETURN -1;
2309 END get_next_high_val_part;
2310
2311
2312 FUNCTION COMPARE_PARTITION_BOUND( powner IN VARCHAR2
2313 , pobject_name IN VARCHAR2
2314 , pobject_type IN VARCHAR2
2315 , phval1 IN VARCHAR2
2316 , phval2 IN VARCHAR2) RETURN NUMBER
2317 IS
2318 --return 1 if hval1 > hval2
2319 --return 2 if hval1 < hval2
2320 --return 0 if they are equal
2321 l_sql VARCHAR2(2001);
2322 numval1 number;
2323 numval2 number;
2324 lv_hval1 varchar2(1000);
2325 lv_hval2 varchar2(1000);
2326
2327 lv_column_position number;
2328 lv_column_name varchar2(30);
2329 lv_data_type varchar2(30);
2330
2331
2332 TYPE refCursorTp IS REF CURSOR;
2333 cur_part refCursorTp;
2334
2335 BEGIN
2336 lv_hval1 :=','||phval1||',';
2337 lv_hval2 :=','||phval2||',';
2338
2339
2340 IF pobject_type='TABLE' THEN
2341 l_sql := ' SELECT pk.column_position, pk.COLUMN_NAME, tc.DATA_TYPE
2342 FROM ALL_PART_KEY_COLUMNS pk, ALL_TAB_COLUMNS tc
2343 WHERE pk.OWNER = tc.OWNER
2344 AND pk.name = tc.table_name
2345 AND pk.column_name = tc.column_name
2346 AND pk.owner = '''||powner||''''||
2347 ' AND pk.name = '''||pobject_name||''''||
2348 ' ORDER BY pk.column_position ASC';
2349 ELSIF pobject_type='INDEX' THEN
2350 l_sql:= 'select c.column_position, a.column_name,a.data_type
2351 from ALL_TAB_COLUMNS a,all_part_indexes b,all_part_key_columns c
2352 where a.owner = b.owner
2353 and a.table_name = b.table_name
2354 and b.index_name = c.name
2355 and a.column_name = c.column_name
2356 and a.owner = '''||powner||''''||
2357 ' and b.owner = '''||powner||''''||
2358 'and c.owner = '''||powner||''''||
2359 ' and b.index_name = '''||pobject_name||''''||
2360 ' ORDER BY c.column_position ASC';
2361 ELSE
2362 return -1;
2363 END IF;
2364
2365
2366 -- FOR partkeyrec IN cur_1(powner,pobject_name) LOOP
2367 open cur_part for l_sql;
2368 loop
2369 fetch cur_part into lv_column_position,lv_column_name,lv_data_type;
2370 exit when cur_part%NOTFOUND;
2371
2372 CASE --partkeyrec.DATA_TYPE
2373 WHEN lv_data_type='NUMBER' THEN
2374 numval1:= to_number(substr( lv_hval1
2375 , instr(lv_hval1, ',',1,lv_column_position)+1
2376 , instr(lv_hval1, ',',1,lv_column_position + 1) - instr(lv_hval1, ',',1,lv_column_position)-1));
2377 numval2:= to_number(substr( lv_hval2
2378 , instr(lv_hval2, ',',1,lv_column_position)+1
2379 , instr(lv_hval2, ',',1,lv_column_position + 1) - instr(lv_hval2, ',',1,lv_column_position)-1));
2380
2381
2382 if numval1 = numval2 then
2383 null; -- check next col
2384 elsif numval1 > numval2 then
2385 return 1;
2386 elsif numval1 < numval2 then
2387 return 2;
2388 else
2389 return -1;
2390 end if;
2391
2392 WHEN lv_data_type IN('VARCHAR2','CHAR') THEN
2393 NULL;
2394
2395 WHEN lv_data_type='DATE' THEN
2396 return -1;
2397
2398 ELSE
2399 return -1;
2400 END CASE;
2401
2402 /* IF lv_ret <>0 THE
2403 return lv_ret
2404 END IF;*/
2405 END LOOP;
2406
2407 return 0;
2408 END COMPARE_PARTITION_BOUND;
2409
2410 --------------------------------------------------------------------------
2411
2412
2413 END MSC_CL_EXCHANGE_PARTTBL;