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