[Home] [Help]
PACKAGE BODY: APPS.MSC_UTIL
Source
1 PACKAGE BODY MSC_UTIL AS
2 /* $Header: MSCUTILB.pls 120.45 2012/06/05 12:22:00 lsindhur ship $ */
3
4 -- GLOBAL VARIABLES IN BODY
5 APPS_SCHEMA VARCHAR2(30);
6 G_CAT_SET_ID NUMBER := NULL ;
7 --
8
9 v_deprecatedMVList TblNmTblTyp:=
10 TblNmTblTyp( 'BOM_CTO_ORDER_DMD_SN');
11 v_deprecatedMVSchemaList TblNmTblTyp:=
12 TblNmTblTyp( msc_util.G_BOM_SCHEMA );
13
14 FUNCTION Check_MSG_Level(pType IN NUMBER) RETURN BOOLEAN
15 IS
16 BEGIN
17 IF (bitand(G_CL_DEBUG, pType) > 0) THEN RETURN TRUE; END IF;
18 RETURN FALSE;
19 END Check_MSG_Level;
20
21 PROCEDURE Print_Msg (buf IN VARCHAR2)
22 IS
23 BEGIN
24 FND_FILE.PUT_LINE(FND_FILE.LOG, buf); -- add a line of text to the log file and
25 EXCEPTION
26 WHEN OTHERS THEN
27 NULL; --suppressing the exceptions
28 END Print_Msg;
29
33 Parameters : p_Type (IN) - number which holds the bebug type
30 /*-----------------------------------------------------------------------------
31 Procedure : LOG_MSG
32
34 of the message to be printed
35
36 buf (IN) - string which consists of the message to be printed
37
38 Description : this procedure will print the message to the log file after
39 checking the current debug status of collections (G_CL_DEBUG)
40 -----------------------------------------------------------------------------*/
41 PROCEDURE LOG_MSG(
42 pType IN NUMBER,
43 buf IN VARCHAR2
44 )
45 IS
46 BEGIN
47 IF Check_MSG_Level(pType) THEN
48 Print_Msg (TO_CHAR(sysdate,'DD-MON HH24:MI:SS') || ' : ' || buf);
49 END IF;
50 END LOG_MSG;
51
52 Procedure print_query( p_query in varchar2,
53 p_display_type in number default 1 )
54 is
55 l_theCursor integer default dbms_sql.open_cursor;
56 l_columnValue varchar2(4000);
57 l_status integer;
58 l_descTbl dbms_sql.desc_tab;
59 l_colCnt number;
60 buff varchar2(4000);
61 begin
62
63 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
64 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
65
66 for i in 1 .. l_colCnt loop
67 dbms_sql.define_column
68 (l_theCursor, i, l_columnValue, 4000);
69 end loop;
70
71 l_status := dbms_sql.execute(l_theCursor);
72 IF p_display_type = 1 THEN
73 --Print one row per line
74 buff := '';
75 for i in 1 .. l_colCnt loop
76 buff := buff || rpad( l_descTbl(i).col_name, 30 ) ;
77 end loop;
78 Print_Msg( buff );Print_Msg(' ');
79 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
80 buff := '';
81 for i in 1 .. l_colCnt loop
82 dbms_sql.column_value
83 ( l_theCursor, i, l_columnValue );
84 buff := buff ||( rpad( l_columnValue, 30 ) );
85 end loop;
86 Print_Msg( buff );
87 --dbms_output.put_line( '-----------------' );
88 end loop;
89 ELSE
90 --Print one column per line
91 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
92 for i in 1 .. l_colCnt loop
93 dbms_sql.column_value( l_theCursor, i, l_columnValue );
94 Print_Msg( rpad( l_descTbl(i).col_name, 30 ) || ': ' || l_columnValue );
95 end loop;
96 Print_Msg( '-----------------' );
97 end loop;
98 END IF;
99 end;
100
101 PROCEDURE print_top_wait(pElaTime NUMBER DEFAULT 0) IS
102 BEGIN
103 IF ( Check_MSG_Level(G_LVL_PERFDBG_2) OR (Check_MSG_Level(G_LVL_PERFDBG_1) AND pElaTime > G_PERF_STAT_TRSHLD_TIME) )THEN
104 Print_Msg('************************************************************');
105 Print_Msg('Top WAIT events');
106 Print_Msg('------------------------------------------------------------');
107 print_query('SELECT SID, EVENT,seconds_waited FROM
108 (SELECT SID, EVENT, TIME_WAITED/100 seconds_waited
109 FROM v$session_event
110 WHERE SID=' || G_CURRENT_SESSION_ID || '
111 ORDER BY sid, TIME_WAITED DESC )
112 WHERE ROWNUM < 10');
113 Print_Msg('************************************************************');
114 END IF;
115 END print_top_wait;
116
117 PROCEDURE print_cum_stat(pElaTime NUMBER DEFAULT 0) IS
118 BEGIN
119 IF ( Check_MSG_Level(G_LVL_PERFDBG_2) OR (Check_MSG_Level(G_LVL_PERFDBG_1) AND pElaTime > G_PERF_STAT_TRSHLD_TIME) )THEN
120 Print_Msg('************************************************************');
121 Print_Msg('Cummilative stats for this session');
122 Print_Msg('------------------------------------------------------------');
123 print_query( 'SELECT A.SID, A.STATISTIC#, B.NAME, A.VALUE
124 FROM V$SESSTAT A, V$STATNAME B --V$MYSTAT
125 WHERE A.SID=' || G_CURRENT_SESSION_ID || '
126 AND A.STATISTIC# = B.STATISTIC#
127 AND B.NAME IN (''recursive calls'', ''recursive cpu usage'',
128 ''session logical reads'',''CPU used when call started'',
129 ''CPU used by this session'', ''DB time'',
130 ''session uga memory'',''IPC CPU used by this session'',
131 ''db block gets'', ''consistent gets'',''physical reads'')');
132 Print_Msg('************************************************************');
133 END IF;
134 END print_cum_stat;
135
136 PROCEDURE print_bad_sqls(pElaTime NUMBER DEFAULT 0) IS
137 BEGIN
138 Print_Msg('************************************************************');
139 Print_Msg('Bad sqls for this session');
140 Print_Msg('------------------------------------------------------------');
141
142 Print_Msg('************************************************************');
143 END print_bad_sqls;
144
145 PROCEDURE print_pull_params(pINSTANCE_ID IN NUMBER) IS
146 BEGIN
147 IF Check_MSG_Level(G_LVL_STATUS) THEN
148 Print_Msg('************************************************************');
149 Print_Msg('Parameters selected for planning data pull:');
150 Print_Msg('------------------------------------------------------------');
151 print_query( Q'[
152 SELECT
153 decode(delete_ods_data ,1,'YES','NO') "Purge collected data",
154 org_group "Org Group",
155 threshold "Time out",
159 decode(bom ,1,'YES','NO') "BOM/Routings/Resources",
156 decode(supplier_capacity ,1,'YES, But retain CP data'
157 ,2,'YES, Replace all values', 'NO') "Approved supplier lists",
158 decode(atp_rules ,1,'YES','NO') "Atp Rules",
160 decode(bor ,1,'YES','NO') "Bill of Resources",
161 decode(calendar_check ,1,'YES','NO') "Calendars",
162 decode(demand_class ,1,'YES','NO') "Demand Class",
163 decode(ITEM_SUBSTITUTES ,1,'YES','NO') "End Item Substitutions",
164 decode(forecast ,1,'YES','NO') "Forecast",
165 decode(item ,1,'YES','NO') "Item",
166 decode(kpi_targets_bis ,1,'YES','NO') "Kpi Targets Bis",
167 decode(mds ,1,'YES','NO') "MDS",
168 decode(mps ,1,'YES','NO') "MPS",
169 decode(oh ,1,'YES','NO') "OnHand",
170 decode(parameter ,1,'YES','NO') "Planning Parameters",
171 decode(planners ,1,'YES','NO') "Planners",
172 decode(projects ,1,'YES','NO') "Projects / Tasks",
173 decode(po ,1,'YES','NO') "PO",
174 decode(reservations ,1,'YES','NO') "Reservations",
175 decode(nra ,1,'Collect Existing Data'
176 ,2,'Do not Collect Data'
177 ,3,'Regenerate and Collect Data') "Resource Availability",
178 decode(safety_stock ,1,'YES','NO') "Safety Stock",
179 decode(sales_order ,1,'YES','NO') "Sales Order",
180 decode(sourcing_history ,1,'YES','NO') "Sourcing History",
181 decode(sourcing ,1,'YES','NO') "Sourcing Rules",
182 decode(sub_inventories ,1,'YES','NO') "Sub Inventories",
183 decode(customer ,1,'YES','NO') "Customer",
184 decode(supplier ,1,'YES','NO') "Supplier",
185 decode(unit_numbers ,1,'YES','NO') "Unit Numbers",
186 decode(uom ,1,'YES','NO') "Uom",
187 decode(user_supply_demand ,1,'YES','NO') "User Supply Demand",
188 decode(wip ,1,'YES','NO') "Wip",
189 decode(user_comp_association ,1,'Crete Users and Enable'
190 ,2,'Enable'
191 ,'NO') "User Comp Association",
192 decode(supplier_response ,1,'YES','NO') "Supplier Response",
193 decode(trip ,1,'YES','NO') "Transportation details",
194 decode(po_receipts ,1,'YES','NO') "po receipts",
195 decode(sales_channel ,1,'YES','NO') "sales channel",
196 decode(fiscal_calendar ,1,'YES','NO') "fiscal calendar",
197 decode(INTERNAL_REPAIR ,1,'YES','NO') "Internal Repair Orders",
198 decode(EXTERNAL_REPAIR ,1,'YES','NO') "External Repair Orders",
199 decode(payback_demand_supply ,1,'YES','NO') "Payback demand/supply",
200 decode(currency_conversion ,1,'YES','NO') "Currency conversion",
201 decode(delivery_Details ,1,'YES','NO') "Delivery Details" ,
202 decode(ibuc_history ,1,'YES','NO') "Install Base under Contracts" ,
203 decode(notes_attach ,1,'YES','NO') "Notes (Attachments)" ,
204 decode(eAM_info ,1,'YES','NO') "eAM Info" , /* USAF*/
205 decode(eAM_forecasts ,1,'YES','NO') "eAM Forecasts",
206 decode(cmro_forecasts ,1,'YES','NO') "CMRO Forecasts",
207 decode(cmro ,1,'YES','NO') "CMRO Data" ,
208 eam_fc_st_date " eAm Forecasts Start date",
209 eam_fc_end_date " eAm Forecasts end date" ,
210 cmro_fc_st_date " CMRO Forecasts Start date",
211 cmro_fc_end_date " CMRO Forecasts end date",
212 decode(osp_supply ,1,'YES','NO') "CMRO OSP Supply"
213 FROM msc_coll_parameters
214 WHERE instance_id = ]' || pINSTANCE_ID , 2);
215 Print_Msg('************************************************************');
216 END IF;
217 END print_pull_params;
218
219 PROCEDURE print_ods_params(pRECALC_SH IN NUMBER, pPURGE_SH IN NUMBER) IS
220 BEGIN
221 IF Check_MSG_Level(G_LVL_STATUS) THEN
222 Print_Msg('************************************************************');
223 Print_Msg('Parameters selected for planning data pull:');
224 Print_Msg('------------------------------------------------------------');
225 IF pRECALC_SH = MSC_UTIL.SYS_YES THEN
226 Print_Msg('Recalculate Sourcing History: YES ' );
227 ELSE
228 Print_Msg('Recalculate Sourcing History: NO ' );
229 END IF;
230 IF pPURGE_SH = MSC_UTIL.SYS_YES THEN
231 Print_Msg('Purge Sourcing History : YES ' );
232 ELSE
233 Print_Msg('Purge Sourcing History : NO ' );
234 END IF;
235 Print_Msg('************************************************************');
236 END IF;
237 END;
238
239 PROCEDURE print_trace_file_name(pReqID NUMBER) IS
240 BEGIN
241 IF Check_MSG_Level(G_LVL_PERFDBG_2)THEN
242 Print_Msg('************************************************************');
243 Print_Msg('Possible Trace file names and location');
244 Print_Msg('------------------------------------------------------------');
245 Begin
246 print_query( 'SELECT request_id ,
247 oracle_Process_id Trace_id ,
248 req.enable_trace Trace_Flag,
249 dest.value||''/''||lower (dbnm.value) ||''_ora_''||oracle_process_id||''.trc'' Trace_File_Name
250 FROM fnd_concurrent_requests req,
251 v$session ses ,
252 v$process PROC ,
256 fnd_executables execname
253 v$parameter dest ,
254 v$parameter dbnm ,
255 fnd_concurrent_programs_vl prog ,
257 WHERE req.oracle_process_id = proc.spid(+)
258 AND proc.addr = ses.paddr(+)
259 AND dest.name = ''user_dump_dest''
260 AND dbnm.name = ''db_name''
261 AND req.concurrent_program_id = prog.concurrent_program_id
262 AND req.program_application_id = prog.application_id
263 AND prog.application_id = execname.application_id
264 AND prog.executable_id = execname.executable_id
265 AND request_id IN ( select request_id from fnd_concurrent_requests req where request_id = '|| pReqID || ' or req.parent_request_id = '|| pReqID ||' )' );
266 Exception when others then
267 Print_Msg('Unable to get the trace file names for request IDs: '|| pReqID);
268 Print_Msg(SQLERRM);
269 end;
270 Print_Msg('************************************************************');
271 END IF;
272 END print_trace_file_name;
273
274 /*-----------------------------------------------------------------------------
275 Procedure : MSC_SET_DEBUG_LEVEL
276
277 Parameters : pType (IN) - new debug status that needs to be set.
278
279 Description : This procedure adds the debug mode 'pType', if it is not already set.
280 -----------------------------------------------------------------------------*/
281
282 PROCEDURE MSC_SET_DEBUG_LEVEL(pType IN NUMBER)
283 IS
284 BEGIN
285 IF (bitand(G_CL_DEBUG, pType) = 0) THEN
286 G_CL_DEBUG := G_CL_DEBUG + pType;
287 LOG_MSG(G_LVL_STATUS, 'Debug level added :' || pType );
288 END IF;
289 END MSC_SET_DEBUG_LEVEL;
290
291
292
293 -- log messaging if debug is turned on
294 PROCEDURE MSC_DEBUG( buf IN VARCHAR2)
295 IS
296 BEGIN
297 -- if MSC:Debug profile is not set return
298 IF (G_MSC_DEBUG <> 'Y') THEN
299 return;
300 END IF;
301 -- add a line of text to the log file and
302
303 FND_FILE.PUT_LINE(FND_FILE.LOG, buf);
304
305 return;
306
307 EXCEPTION
308 WHEN OTHERS THEN
309 return;
310 END MSC_DEBUG;
311
312 -- log messaging irrespective of whether debug is turned on or off
313 PROCEDURE MSC_LOG( buf IN VARCHAR2)
314 IS
315 BEGIN
316
317 -- log the message
318 FND_FILE.PUT_LINE(FND_FILE.LOG, buf);
319
320 return;
321
322 EXCEPTION
323 WHEN OTHERS THEN
324 return;
325 END MSC_LOG;
326
327 -- out messaging
328 PROCEDURE MSC_OUT(buf IN VARCHAR2)
329 IS
330 BEGIN
331 -- add a line of text to the output file and
332 -- add the line terminator
333 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, buf);
334 FND_FILE.NEW_LINE(FND_FILE.OUTPUT,1);
335
336 return;
337
338 EXCEPTION
339 WHEN OTHERS THEN
340 return;
341 END MSC_OUT;
342
343
344 PROCEDURE compare_index(
345 p_table_name IN VARCHAR2,
346 p_index_name IN VARCHAR2,
347 p_column_list IN MSC_UTIL.char30_arr,
348 x_create_index OUT NOCOPY BOOLEAN,
349 x_partitioned OUT NOCOPY BOOLEAN
350 )
351 IS
352
353 l_column_name VARCHAR2(30);
354 l_partitioned VARCHAR2(30);
355
356 v_msc_schema VARCHAR2(32);
357 lv_retval boolean;
358 lv_dummy1 varchar2(32);
359 lv_dummy2 varchar2(32);
360
361
362 CURSOR c_ind_columns(p_owner varchar2)
363 IS
364 SELECT column_name
365 FROM all_ind_columns
366 WHERE index_owner=p_owner
367 AND table_name = p_table_name
368 AND index_name = p_index_name
369 ORDER BY column_position;
370
371
372 BEGIN
373
374 lv_retval := FND_INSTALLATION.GET_APP_INFO ('MSC', lv_dummy1, lv_dummy2,v_msc_schema);
375 x_create_index := FALSE;
376 x_partitioned := FALSE;
377
378 BEGIN
379 SELECT partitioned
380 INTO l_partitioned
381 FROM all_indexes
382 WHERE owner=v_msc_schema
383 AND table_name = p_table_name
384 AND index_name = p_index_name;
385
386 --dbms_output.put_line('l_partitioned : ' || l_partitioned);
387 IF l_partitioned = 'YES' THEN
388 x_partitioned := TRUE;
389 ELSIF l_partitioned = 'NO' THEN
390 x_partitioned := FALSE;
391 END IF;
392 EXCEPTION
393 WHEN no_data_found THEN
394 x_partitioned := FALSE;
395 END;
396
397 OPEN c_ind_columns(v_msc_schema);
398
399 FOR i IN p_column_list.FIRST..p_column_list.COUNT LOOP
400 FETCH c_ind_columns INTO l_column_name;
401 EXIT WHEN c_ind_columns%NOTFOUND;
402
403 --dbms_output.put_line('l_column_name : ' || l_column_name);
404 IF l_column_name <> UPPER(p_column_list(i)) THEN
405 x_create_index := TRUE;
406 EXIT;
407 END IF;
408 END LOOP;
409
410 --dbms_output.put_line('ROWCOUNT : ' || c_ind_columns%ROWCOUNT);
411 IF c_ind_columns%ROWCOUNT = 0 THEN
412 x_create_index := TRUE;
413 --dbms_output.put_line('x_drop_index is FALSE');
414 END IF;
415 CLOSE c_ind_columns;
416
417 EXCEPTION
418 WHEN others THEN
419 IF c_ind_columns%ISOPEN THEN
420 CLOSE c_ind_columns;
421 END IF;
425
422 ---- bug 2234098 change error code from 21001 to 20001
423 RAISE_APPLICATION_ERROR(-20001, 'MSC_UTIL.COMPARE_INDEX: Error while checking the index attributes: ' || SQLERRM);
424 END compare_index;
426
427
428 /* ======== Create Snap Log========== */
429 PROCEDURE CREATE_SNAP_LOG( p_schema in VARCHAR2,
430 p_table in VARCHAR2,
431 p_applsys_schema IN VARCHAR2)
432 IS
433 v_sql_stmt VARCHAR2(6000);
434 BEGIN
435
436 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Creating Snapshot Log for ' ||p_table||' ...');
437
438 v_sql_stmt:=
439 ' CREATE SNAPSHOT LOG ON '||p_schema ||'.'||p_table||' WITH ROWID ' ;
440
441 ad_ddl.do_ddl( applsys_schema => p_applsys_schema,
442 application_short_name => p_schema,
443 statement_type => AD_DDL.CREATE_TABLE,
444 statement => v_sql_stmt,
445 object_name => p_table);
446
447 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Snapshot Log for ' ||p_table||' successfully created...');
448
449 EXCEPTION
450 WHEN OTHERS THEN
451
452 IF SQLCODE IN (-12000) THEN
453 /*Snapshot Log already EXISTS*/
454 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Snapshot Log on ' ||p_table||' already exists...');
455
456 ELSIF SQLCODE IN (-00942) THEN
457 /*Base Table does not exist*/
458 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Table '||p_table||' does not exist...');
459 ELSE
460 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, SQLERRM);
461 RAISE_APPLICATION_ERROR(-20001, 'Snapshot Log Creation on '|| p_table||' failed : ' || sqlerrm);
462 END IF;
463 END CREATE_SNAP_LOG; --create_snap Log
464 /* ======== Create Snap Log========== */
465
466 /* ======== Overloading Create Snap Log========== */
467 PROCEDURE CREATE_SNAP_LOG( p_schema IN VARCHAR2,
468 p_table IN VARCHAR2,
469 p_applsys_schema IN VARCHAR2,
470 p_appl_id IN NUMBER)
471 IS
472 v_sql_stmt VARCHAR2(6000);
473 lappshortname VARCHAR2(30);
474 BEGIN
475
476 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Creating Snapshot Log for ' ||p_table||' ...');
477
478 SELECT application_short_name
479 into lappshortname
480 FROM fnd_application
481 WHERE application_id = p_appl_id;
482
483 v_sql_stmt:=
484 ' CREATE SNAPSHOT LOG ON '||p_schema ||'.'||p_table||' WITH ROWID ' ;
485
486 ad_ddl.do_ddl( applsys_schema => p_applsys_schema,
487 application_short_name => lappshortname,
488 statement_type => AD_DDL.CREATE_TABLE,
489 statement => v_sql_stmt,
490 object_name => p_table);
491
492 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Snapshot Log for ' ||p_table||' successfully created...');
493
494 EXCEPTION
495 WHEN OTHERS THEN
496
497 IF SQLCODE IN (-12000) THEN
498 /*Snapshot Log already EXISTS*/
499 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Snapshot Log on ' ||p_table||' already exists...');
500
501 ELSIF SQLCODE IN (-00942) THEN
502 /*Base Table does not exist*/
503 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Table '||p_table||' does not exist...');
504 ELSE
505 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, SQLERRM);
506 RAISE_APPLICATION_ERROR(-20001, 'Snapshot Log Creation on '|| p_table||' failed : ' || sqlerrm);
507 END IF;
508 END CREATE_SNAP_LOG; --create_snap Log
509 /* ======== Overloading Create Snap Log========== */
510
511
512 PROCEDURE GET_STORAGE_PARAMETERS( p_table_name IN VARCHAR2,
513 p_schema IN VARCHAR2,
514 v_table_space OUT NOCOPY VARCHAR2,
515 v_index_space OUT NOCOPY VARCHAR2,
516 v_storage_clause OUT NOCOPY VARCHAR2)
517 IS
518 lv_initial_extent NUMBER;
519 lv_next_extent NUMBER;
520 lv_extent_management VARCHAR2(10);
521 lv_is_object_registered VARCHAR2(10);
522 lv_ts_exists VARCHAR2(10);
523 lv_is_new_ts_mode VARCHAR2(10);
524 BEGIN
525 ad_tspace_util.is_new_ts_mode(lv_is_new_ts_mode);
526 IF(upper(lv_is_new_ts_mode) = 'N') THEN-- code for old tabel space structure
527
528 SELECT alt.tablespace_name,
529 alt.initial_extent,
530 alt.next_extent ,
531 dt.extent_management
532 INTO v_table_space,
533 lv_initial_extent,
534 lv_next_extent ,
535 lv_extent_management
536 FROM ALL_TABLES alt,
537 DBA_TABLESPACES dt
538 WHERE alt.table_name = upper(p_table_name)
539 AND alt.owner = upper(p_schema)
540 AND alt.tablespace_name = dt.tablespace_name ;
541 BEGIN
542 SELECT TABLESPACE_NAME
543 INTO v_index_space
544 FROM ALL_INDEXES
545 WHERE table_name = upper(p_table_name)
546 and owner = upper(p_schema)
547 and rownum = 1;
548 EXCEPTION
549 WHEN NO_DATA_FOUND THEN
550 v_index_space := v_table_space;
551 END;
552
553 ELSE --- start of code for new tablespace structure
554
555 ad_tspace_util.get_object_tablespace(
556 x_product_short_name => p_schema,
557 x_object_name => p_table_name,
558 x_object_type => 'TABLE',
559 x_index_lookup_flag => 'N',
560 x_validate_ts_exists => 'Y',
564 ad_tspace_util.get_object_tablespace(
561 x_is_object_registered => lv_is_object_registered,
562 x_ts_exists => lv_ts_exists,
563 x_tablespace => v_table_space);
565 x_product_short_name => p_schema,
566 x_object_name => p_table_name,
567 x_object_type => 'TABLE',
568 x_index_lookup_flag => 'Y',
569 x_validate_ts_exists => 'Y',
570 x_is_object_registered => lv_is_object_registered,
571 x_ts_exists => lv_ts_exists,
572 x_tablespace => v_index_space);
573
574 SELECT alt.initial_extent,
575 alt.next_extent ,
576 dt.extent_management
577 INTO lv_initial_extent,
578 lv_next_extent ,
579 lv_extent_management
580 FROM ALL_TABLES alt,
581 DBA_TABLESPACES dt
582 WHERE alt.table_name = upper(p_table_name)
583 AND alt.owner = upper(p_schema)
584 AND alt.tablespace_name = dt.tablespace_name ;
585 IF v_index_space is NULL THEN
586 v_index_space := v_table_space;
587 END IF;
588
589 END IF;
590
591 IF (lv_extent_management = 'DICTIONARY') THEN
592 v_storage_clause := ' STORAGE (INITIAL '||lv_initial_extent||' NEXT '||lv_next_extent
593 || ' PCTINCREASE 0 ) '||' USING INDEX TABLESPACE '||v_index_space;
594
595 ELSE ---locally managed tablespace
596 v_storage_clause := ' ';
597 END IF;
598
599 EXCEPTION
600 WHEN OTHERS THEN
601 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, SQLERRM);
602 raise_application_error(-20001, 'Error in Getting Storage Parameters : ' || sqlerrm);
603 END GET_STORAGE_PARAMETERS;
604
605
606 /* ======== Create Snapshot ========== */
607 FUNCTION CREATE_SNAP (p_schema IN VARCHAR2,
608 p_table IN VARCHAR2,
609 p_object IN VARCHAR2,
610 p_sql_stmt IN VARCHAR2,
611 p_applsys_schema IN VARCHAR2,
612 p_logging IN VARCHAR2 DEFAULT 'NOLOGGING',
613 p_parallel_degree IN NUMBER DEFAULT 1,
614 p_error IN VARCHAR2 DEFAULT NULL )
615 RETURN BOOLEAN IS
616 v_sql_stmt VARCHAR2(6000);
617 lv_pctg NUMBER:= 10;
618 lv_deg NUMBER:= 4;
619 v_logging_stmt VARCHAR2(6000);
620 lv_appl_short_nm VARCHAR2(30);
621
622
623 BEGIN -- Snapshot
624
625 v_logging_stmt := 'ALTER MATERIALIZED VIEW '||p_schema||'.'||p_object
626 ||' '||p_logging ||' PARALLEL '|| p_parallel_degree;
627
628 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Creating Snapshot for '||p_table||' ...');
629
630
631 SELECT application_short_name
632 INTO lv_appl_short_nm
633 FROM fnd_application
634 WHERE application_id=724;
635
636 IF p_schema = MSC_UTIL.G_APPS_SCHEMA THEN
637
638 EXECUTE IMMEDIATE p_sql_stmt;
639
640 ELSE
641
642 ad_ddl.do_ddl( applsys_schema => p_applsys_schema,
643 application_short_name => lv_appl_short_nm,
644 statement_type => AD_DDL.CREATE_TABLE,
645 statement => p_sql_stmt,
646 object_name => p_object);
647 END IF;
648
649 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Snapshot for '||p_table||' succesfully created...');
650
651 FND_STATS.gather_table_stats(p_schema,p_object,lv_pctg, lv_deg);
652
653 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Gathered Statistics for the Snapshot '||p_object||' succesfully ...');
654
655 EXECUTE IMMEDIATE v_logging_stmt;
656 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Altering Snapshot : '||p_object);
657
658
659 RETURN TRUE;
660
661 EXCEPTION
662 WHEN OTHERS THEN
663 IF SQLCODE IN (-12006) THEN
664 /*Snapshot already EXISTS*/
665 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Snapshot on '||p_table||' already exists...');
666 EXECUTE IMMEDIATE v_logging_stmt;
667 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Altering Snapshot : '||p_object);
668 RETURN TRUE;
669 ELSIF SQLCODE IN (-01749) THEN
670 /*you may not GRANT/REVOKE privileges to/from yourself*/
671 /* snapshot created in apps schema*/
672 RETURN TRUE;
673 ELSIF instr(p_error,','||trim(SQLCODE)||',')>0 THEN /*6501625*/
674 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, sqlerrm);
675 RETURN FALSE;
676 ELSE
677 -- no need to log the error message twice, hence commenting.
678 -- The following error will be logged in the place from where create_snap is called.
679 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, SQLERRM);
680 RAISE_APPLICATION_ERROR(-20001, 'Snapshot Creation on '|| p_table||' failed : ' || sqlerrm);
681 END IF;
682
683 END CREATE_SNAP ; --Snapshot
684 /* ======== Snapshot ========== */
685
686
687 /*CREATE_SNAP2 function creates mview in ebr env & sets loggingmode, degree etc; bug 12964052
688 scenario of APPS schema is complete and tested
689 scenario of non-apps schema is INCOMPLETE AND NOT tested due to further inputs needed from AD */
690 /* ======== Create Snapshot ========== */
691 FUNCTION CREATE_SNAP2 (p_schema IN VARCHAR2,
692 p_table IN VARCHAR2,
693 p_object IN VARCHAR2,
694 p_sql_stmt IN VARCHAR2,
695 p_applsys_schema IN VARCHAR2,
696 p_logging IN VARCHAR2 DEFAULT 'NOLOGGING',
700 v_sql_stmt VARCHAR2(6000);
697 p_parallel_degree IN NUMBER DEFAULT 1,
698 p_error IN VARCHAR2 DEFAULT NULL )
699 RETURN BOOLEAN IS
701 lv_pctg NUMBER:= 10;
702 lv_deg NUMBER:= 4;
703 v_logging_stmt VARCHAR2(6000);
704 lv_appl_short_nm VARCHAR2(30);
705
706 BEGIN -- Snapshot
707
708 v_logging_stmt := 'ALTER MATERIALIZED VIEW '||p_schema||'.'||p_object
709 ||' '||p_logging ||' PARALLEL '|| p_parallel_degree;
710
711 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Creating Snapshot for '||p_table||' ...');
712
713 SELECT application_short_name
714 INTO lv_appl_short_nm
715 FROM fnd_application
716 WHERE application_id=724;
717
718 IF p_schema = MSC_UTIL.G_APPS_SCHEMA THEN
719 return CREATE_SNAP2_AS( p_table,
720 p_object,
721 p_sql_stmt,
722 p_logging,
723 p_parallel_degree,
724 p_error );
725 ELSE
726
727 ad_ddl.do_ddl( applsys_schema => p_applsys_schema,
728 application_short_name => lv_appl_short_nm,
729 statement_type => AD_DDL.CREATE_TABLE,
730 statement => p_sql_stmt,
731 object_name => p_object);
732 END IF;
733
734 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Snapshot for '||p_table||' succesfully created...');
735
736 FND_STATS.gather_table_stats(p_schema,p_object,lv_pctg, lv_deg);
737
738 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Gathered Statistics for the Snapshot '||p_object||' succesfully ...');
739
740 EXECUTE IMMEDIATE v_logging_stmt;
741 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Altering Snapshot : '||p_object);
742
743
744 RETURN TRUE;
745
746 EXCEPTION
747 WHEN OTHERS THEN
748 IF SQLCODE IN (-12006) THEN
749 /*Snapshot already EXISTS*/
750 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Snapshot on '||p_table||' already exists...');
751 EXECUTE IMMEDIATE v_logging_stmt;
752 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Altering Snapshot : '||p_object);
753 RETURN TRUE;
754 ELSIF SQLCODE IN (-01749) THEN
755 /*you may not GRANT/REVOKE privileges to/from yourself*/
756 /* snapshot created in apps schema*/
757 RETURN TRUE;
758 ELSIF instr(p_error,','||trim(SQLCODE)||',')>0 THEN /*6501625*/
759 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, sqlerrm);
760 RETURN FALSE;
761 ELSE
762 -- no need to log the error message twice, hence commenting.
763 -- The following error will be logged in the place from where create_snap is called.
764 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, SQLERRM);
765 RAISE_APPLICATION_ERROR(-20001, 'Snapshot Creation on '|| p_table||' failed : ' || sqlerrm);
766 END IF;
767
768 END CREATE_SNAP2 ; --Snapshot
769 /* ======== Snapshot ========== */
770 /*Addition ends here w.r.t bug 12964052*/
771
772
773 /* CREATE_SNAP2_AS FUNCTION to CREATE APPS schema MVIEW IN EBR env & sets logging mode, parallel etc; bug 12964052 */
774 FUNCTION CREATE_SNAP2_AS (p_table IN VARCHAR2,
775 p_object IN VARCHAR2,
776 p_sql_stmt IN VARCHAR2,
777 p_logging IN VARCHAR2 DEFAULT 'NOLOGGING',
778 p_parallel_degree IN NUMBER DEFAULT 1,
779 p_error IN VARCHAR2 DEFAULT NULL )
780 RETURN BOOLEAN IS
781 v_sql_stmt VARCHAR2(6000);
782 lv_pctg NUMBER:= 10;
783 lv_deg NUMBER:= 4;
784 v_logging_stmt VARCHAR2(6000);
785
786
787 BEGIN -- Snapshot
788
789 v_logging_stmt := 'ALTER MATERIALIZED VIEW '||MSC_UTIL.G_APPS_SCHEMA||'.'||p_object
790 ||' '||p_logging ||' PARALLEL '|| p_parallel_degree;
791
792 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Creating Snapshot for '||p_table||' ...');
793
794 ad_mv.do_mv_ddl(
795 an_operation_i => ad_mv.mv_create,
796 as_mview_name_i => p_object,
797 as_stmt_i => p_sql_stmt,
798 ab_execute_i => TRUE
799 );
800
801 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Snapshot for '||p_table||' succesfully created...');
802
803 FND_STATS.gather_table_stats(MSC_UTIL.G_APPS_SCHEMA,p_object,lv_pctg, lv_deg);
804
805 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Gathered Statistics for the Snapshot '||p_object||' succesfully ...');
806
807 EXECUTE IMMEDIATE v_logging_stmt;
808 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Altering Snapshot : '||p_object);
809
810
811 RETURN TRUE;
812
813 EXCEPTION
814 WHEN OTHERS THEN
815 IF SQLCODE IN (-12006) THEN
816 /*Snapshot already EXISTS*/
817 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Snapshot on '||p_table||' already exists...');
818 EXECUTE IMMEDIATE v_logging_stmt;
819 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Altering Snapshot : '||p_object);
820 RETURN TRUE;
821 ELSIF SQLCODE IN (-01749) THEN
822 /*you may not GRANT/REVOKE privileges to/from yourself*/
823 /* snapshot created in apps schema*/
824 RETURN TRUE;
825 ELSIF instr(p_error,','||trim(SQLCODE)||',')>0 THEN /*6501625*/
826 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, sqlerrm);
827 RETURN FALSE;
828 ELSE
829 -- no need to log the error message twice, hence commenting.
830 -- The following error will be logged in the place from where create_snap is called.
834
831 --MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, SQLERRM);
832 RAISE_APPLICATION_ERROR(-20001, 'Snapshot Creation on '|| p_table||' failed : ' || sqlerrm);
833 END IF;
835 END CREATE_SNAP2_AS ; --Snapshot
836 /* ======== Snapshot ========== */
837 /*Addition ends here w.r.t bug 12964052*/
838
839
840 /* INDEX */
841 PROCEDURE CREATE_INDEX (p_schema IN VARCHAR2,
842 p_sql_stmt IN VARCHAR2,
843 p_object IN VARCHAR2,
844 p_applsys_schema IN VARCHAR2)
845 IS
846 v_sql_stmt VARCHAR2(6000);
847 lv_schema VARCHAR2(30);
848 lv_appl_short_nm VARCHAR2(30);
849 BEGIN -- Index
850
851
852 SELECT application_short_name
853 INTO lv_appl_short_nm
854 FROM fnd_application
855 WHERE application_id=724;
856
857 IF p_schema = MSC_UTIL.G_APPS_SCHEMA THEN
858
859 EXECUTE IMMEDIATE p_sql_stmt;
860
861 ELSE
862
863 ad_ddl.do_ddl( applsys_schema => p_applsys_schema,
864 application_short_name => lv_appl_short_nm,
865 statement_type => AD_DDL.CREATE_INDEX,
866 statement => p_sql_stmt,
867 object_name => p_object);
868 END IF;
869
870 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Index '||p_object||' succesfully created...');
871
872 EXCEPTION
873 WHEN OTHERS THEN
874 IF SQLCODE IN (-01408) THEN
875 /*Index on same column already exists*/
876 NULL;
877 ELSIF
878 SQLCODE IN (-00955) THEN
879 /*Index already exists*/
880 NULL;
881 ELSE
882 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, SQLERRM);
883 raise_application_error(-20001, 'Index Creation failed: ' || sqlerrm);
884 END IF;
885
886 END CREATE_INDEX; --Index
887
888
889 /* ======== Drop index ========== */
890 PROCEDURE DROP_INDEX (p_schema IN VARCHAR2,
891 p_sql_stmt IN VARCHAR2,
892 p_index IN VARCHAR2,
893 p_table IN VARCHAR2,
894 p_applsys_schema IN VARCHAR2)
895 IS
896 v_sql_stmt VARCHAR2(6000);
897 lv_appl_short_nm VARCHAR2(30);
898 BEGIN -- Index
899
900
901 SELECT application_short_name
902 INTO lv_appl_short_nm
903 FROM fnd_application
904 WHERE application_id=724;
905
906 IF p_schema = MSC_UTIL.G_APPS_SCHEMA THEN
907
908 EXECUTE IMMEDIATE p_sql_stmt;
909
910 ELSE
911
912 ad_ddl.do_ddl( applsys_schema => p_applsys_schema,
913 application_short_name => lv_appl_short_nm,
914 statement_type => AD_DDL.DROP_INDEX,
915 statement => p_sql_stmt,
916 object_name => p_table);
917 END IF;
918
919 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Index '||p_index||' succesfully dropped...');
920
921 EXCEPTION
922 WHEN OTHERS THEN
923 IF SQLCODE IN (-01418) THEN
924 /*Index does not exist */
925 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Index ' ||p_index||' does not exist...');
926 ELSE
927 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, SQLERRM);
928 raise_application_error(-20001, 'Dropping Index failed: ' || sqlerrm);
929 END IF;
930 END DROP_INDEX; --Index
931
932 FUNCTION GET_SCHEMA_NAME( p_apps_id IN NUMBER)
933 RETURN VARCHAR2 IS
934 lv_schema VARCHAR2(30);
935 lv_prod_short_name VARCHAR2(30);
936 lv_retval boolean;
937 lv_dummy1 varchar2(32);
938 lv_dummy2 varchar2(32);
939 lv_is_new_ts_mode VARCHAR2(10);
940 BEGIN
941
942 case p_apps_id
943 WHEN 867 THEN lv_schema:= G_AHL_SCHEMA;
944 WHEN 401 THEN lv_schema:= G_INV_SCHEMA;
945 WHEN 702 THEN lv_schema:= G_BOM_SCHEMA;
946 WHEN 201 THEN lv_schema:= G_PO_SCHEMA;
947 WHEN 665 THEN lv_schema:= G_WSH_SCHEMA;
948 WHEN 426 THEN lv_schema:= G_EAM_SCHEMA;
949 WHEN 660 THEN lv_schema:= G_ONT_SCHEMA;
950 WHEN 704 THEN lv_schema:= G_MRP_SCHEMA;
951 WHEN 410 THEN lv_schema:= G_WSM_SCHEMA;
952 WHEN 523 THEN lv_schema:= G_CSP_SCHEMA;
953 WHEN 706 THEN lv_schema:= G_WIP_SCHEMA;
954 WHEN 512 THEN lv_schema:= G_CSD_SCHEMA;
955 WHEN 0 THEN lv_schema:= G_FND_SCHEMA;
956 ELSE lv_schema:= NULL ;
957 end case;
958
959 if lv_schema is not null then
960 return lv_schema;
961 end if;
962
963 ad_tspace_util.is_new_ts_mode(lv_is_new_ts_mode);
964 IF(upper(lv_is_new_ts_mode) = 'N') THEN
965 SELECT a.oracle_username
966 INTO lv_schema
967 FROM FND_ORACLE_USERID a,
968 FND_PRODUCT_INSTALLATIONS b
969 WHERE a.oracle_id = b.oracle_id
970 AND b.application_id = p_apps_id;
971
972 ELSE
973 lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(p_apps_id);
974 lv_retval := FND_INSTALLATION.GET_APP_INFO (lv_prod_short_name, lv_dummy1, lv_dummy2, lv_schema);
975 END IF;
976
977 RETURN lv_schema;
978
979 EXCEPTION
980 WHEN OTHERS THEN
981 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, SQLERRM);
982 raise_application_error(-20001, 'Error getting the Schema : ' || sqlerrm);
983 END GET_SCHEMA_NAME;
984
985
986 -- This function returns the VMI_FLAG in MSC_ITEM_SUPPLIERS as follows:
987 -- 1. If for a (item,supplier) combination there exists both local and
991 -- 3. If the supplier and supplier_site are both null in msc_supplies and supplier_site is
988 -- global ASLs, then the local will take precedence.
989 -- 2. If the supplier_site is null in msc_supplies but not null in
990 -- msc_item_suppliers, then this record's vmi_flag will not be considered.
992 -- null in msc_item_suppliers, then this record's vmi_flag will be considered
993 -- 4. If the supplier and supplier_site are not null, then supplier_site level
994 -- record's vmi_flag will be considered.
995
996 FUNCTION get_vmi_flag(var_plan_id IN NUMBER,
997 var_sr_instance_id IN NUMBER,
998 var_org_id IN NUMBER,
999 var_inventory_item_id IN NUMBER,
1000 var_supplier_id IN NUMBER,
1001 var_supplier_site_id IN NUMBER) RETURN NUMBER IS
1002
1003 lv_vmi_flag NUMBER := 2;
1004
1005 CURSOR GET_VMI_FLAG_C1 is
1006 select vmi_flag from msc_item_suppliers mis
1007 Where mis.supplier_id = var_supplier_id
1008 and nvl(mis.supplier_site_id,-1) = nvl(var_supplier_site_id,-1)
1009 and mis.using_organization_id = var_org_id
1010 --AND mis.plan_id = var_plan_id
1011 AND mis.plan_id = -1
1012 AND mis.sr_instance_id = var_sr_instance_id
1013 AND mis.sr_instance_id2 = var_sr_instance_id
1014 AND mis.organization_id = var_org_id
1015 AND mis.inventory_item_id = var_inventory_item_id
1016 AND ROWNUM = 1;
1017
1018 CURSOR GET_VMI_FLAG_C2 is
1019 select vmi_flag
1020 from msc_item_suppliers mis
1021 Where mis.supplier_id = var_supplier_id
1022 and mis.supplier_site_id is null
1023 and var_supplier_site_id is not null
1024 and mis.using_organization_id = var_org_id
1025 --AND mis.plan_id = var_plan_id
1026 AND mis.plan_id = -1
1027 AND mis.sr_instance_id = var_sr_instance_id
1028 AND mis.sr_instance_id2 = var_sr_instance_id
1029 AND mis.organization_id = var_org_id
1030 AND mis.inventory_item_id = var_inventory_item_id
1031 AND ROWNUM = 1;
1032
1033 CURSOR GET_VMI_FLAG_C3 is
1034 select vmi_flag
1035 from msc_item_suppliers mis
1036 Where mis.supplier_id = var_supplier_id
1037 and nvl(mis.supplier_site_id,-1) = nvl(var_supplier_site_id,-1)
1038 and mis.using_organization_id = -1
1039 --AND mis.plan_id = var_plan_id
1040 AND mis.plan_id = -1
1041 AND mis.sr_instance_id = var_sr_instance_id
1042 AND mis.sr_instance_id2 = var_sr_instance_id
1043 AND mis.organization_id = var_org_id
1044 AND mis.inventory_item_id = var_inventory_item_id
1045 AND ROWNUM = 1;
1046
1047 CURSOR GET_VMI_FLAG_C4 is
1048 select vmi_flag
1049 from msc_item_suppliers mis
1050 Where mis.supplier_id = var_supplier_id
1051 and mis.supplier_site_id is null
1052 and var_supplier_site_id is not null
1053 and mis.using_organization_id = -1
1054 --AND mis.plan_id = var_plan_id
1055 AND mis.plan_id = -1
1056 AND mis.sr_instance_id = var_sr_instance_id
1057 AND mis.sr_instance_id2 = var_sr_instance_id
1058 AND mis.organization_id = var_org_id
1059 AND mis.inventory_item_id = var_inventory_item_id
1060 AND ROWNUM = 1;
1061
1062 BEGIN
1063
1064 /*
1065 We need to query from msc_item_suppliers based
1066 on it's unique index in 4 ways depending on the supplier_site_id
1067 is null/not null and org_id is -1 or not -1.
1068 Instead of having 4 unions, here we have declared 4 cursors
1069 with org_id as a parameter.
1070 We call the appropriate cursor based on the passed in supplier_site_id.
1071 The logic is that we open the next cursor, only if
1072 the current one fetches 0 rows.
1073 */
1074
1075 OPEN GET_VMI_FLAG_C1;
1076 FETCH GET_VMI_FLAG_C1 into lv_vmi_flag;
1077 if GET_VMI_FLAG_C1%ROWCOUNT = 0 then
1078 CLOSE GET_VMI_FLAG_C1;
1079 OPEN GET_VMI_FLAG_C2;
1080 FETCH GET_VMI_FLAG_C2 into lv_vmi_flag;
1081 if GET_VMI_FLAG_C2%ROWCOUNT = 0 then
1082 CLOSE GET_VMI_FLAG_C2;
1083 OPEN GET_VMI_FLAG_C3;
1084 FETCH GET_VMI_FLAG_C3 into lv_vmi_flag;
1085 if GET_VMI_FLAG_C3%ROWCOUNT = 0 then
1086 CLOSE GET_VMI_FLAG_C3;
1087 OPEN GET_VMI_FLAG_C4;
1088 FETCH GET_VMI_FLAG_C4 into lv_vmi_flag;
1089 CLOSE GET_VMI_FLAG_C4;
1090 else
1091 CLOSE GET_VMI_FLAG_C3;
1092 end if;
1093 else
1094 CLOSE GET_VMI_FLAG_C2;
1095 end if;
1096 else
1097 CLOSE GET_VMI_FLAG_C1;
1098 end if;
1099
1100 return nvl(lv_vmi_flag,2);
1101
1102 EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 2;
1103 WHEN OTHERS THEN RAISE;
1104
1105 END get_vmi_flag;
1106
1107 Function Source_Instance_State(p_dblink varchar2)
1108 return boolean
1109 is
1110 l_sql varchar2(2000);
1111 l_state boolean := TRUE;
1112 Begin
1113 begin
1114 l_sql := 'select 1 from dual@'||p_dblink;
1115 execute immediate(l_sql);
1116 exception
1117 when too_many_rows then
1118 null;
1119 when no_data_found then
1120 null;
1121 when others then
1122 l_state := FALSE;
1123 end ;
1124 return l_state ;
1125 End Source_Instance_State;
1126
1127 /*
1128 PROCEDURE debug_message( P_line_no in number ,
1129 P_Line_msg in varchar2 ,
1130 P_Package_name in varchar2 default null ,
1131 P_Program_unit in varchar2 default null ,
1132 P_Table_Name in varchar2 default 'DEBUG_DB_MESSAGES' )
1133 is
1134 l_sql_stmt varchar2(32000);
1135 PRAGMA AUTONOMOUS_TRANSACTION ;
1136 begin
1137 l_sql_stmt := 'insert into '||P_Table_Name||' values( :v_line_no '||
1138 ', :v_line_msg '||
1139 ', :v_package_name '||
1140 ', :v_program_unit )';
1144 commit;
1141 EXECUTE IMMEDIATE l_sql_stmt using
1142 p_line_no , p_line_msg ,
1143 P_package_name , p_program_unit ;
1145
1146 exception
1147 when others then
1148 raise_application_error(-20001 , sqlerrm);
1149 end debug_message;
1150
1151 PROCEDURE init_message(P_Table_Name in varchar2 default 'DEBUG_DB_MESSAGES')
1152 is
1153 l_sql_stmt varchar2(32000);
1154 l_var number;
1155
1156 v_msc_schema VARCHAR2(32);
1157 lv_retval boolean;
1158 lv_dummy1 varchar2(32);
1159 lv_dummy2 varchar2(32);
1160
1161 cursor c_obj(p_obj varchar2 , p_owner varchar2) is
1162 select 1 from all_objects
1163 where object_name = p_obj
1164 and owner = p_owner
1165 and object_type = 'TABLE';
1166
1167 PRAGMA AUTONOMOUS_TRANSACTION ;
1168
1169 Begin
1170 --bug #3777761 modified cursor c_obj and retrived owner value using function FND_INSTALLATION.GET_APP_INFO.
1171 lv_retval := FND_INSTALLATION.GET_APP_INFO ('MSC', lv_dummy1, lv_dummy2,v_msc_schema);
1172
1173 open c_obj(P_Table_Name , v_msc_schema);
1174 fetch c_obj into l_var;
1175 close c_obj;
1176
1177 if nvl(l_var,-1) = 1 then
1178 l_sql_stmt := 'drop table '||P_Table_Name ||' ';
1179
1180 EXECUTE IMMEDIATE l_sql_stmt ;
1181 l_sql_stmt := 'create table '||P_Table_Name || '( '||
1182 ' line_no number , line_msg long , '||
1183 ' package_name varchar2(50) ,Program_unit varchar2(50)) ' ;
1184
1185 EXECUTE IMMEDIATE l_sql_stmt ;
1186 elsif nvl(l_var,-1) = -1 then
1187 l_sql_stmt := 'create table '||P_Table_Name || '( '||
1188 ' line_no number , line_msg long , '||
1189 ' package_name varchar2(50) ,Program_unit varchar2(50)) ' ;
1190
1191 EXECUTE IMMEDIATE l_sql_stmt ;
1192 end if;
1193
1194 commit;
1195
1196 End init_message;
1197 */
1198
1199 PROCEDURE init_dbmessage
1200 is
1201 l_count number;
1202 begin
1203 l_count := MSC_UTIL.g_dbmessage.count;
1204 if nvl(l_count,0) > 0 then
1205 MSC_UTIL.g_dbmessage.delete;
1206 end if;
1207 End init_dbmessage;
1208
1209
1210 PROCEDURE set_dbmessage(p_msg in varchar2 ,
1211 p_Package_name in varchar2 default null ,
1212 P_Program_unit in varchar2 default null )
1213 is
1214 l_count number ;
1215 begin
1216 l_count := MSC_UTIL.g_dbmessage.count;
1217 MSC_UTIL.g_dbmessage(nvl(l_count , 0) + 1).msg_no := nvl(l_count , 0) + 1 ;
1218 MSC_UTIL.g_dbmessage(nvl(l_count , 0) + 1).msg_desc := p_msg ;
1219 MSC_UTIL.g_dbmessage(nvl(l_count , 0) + 1).package_name := p_Package_name ;
1220 MSC_UTIL.g_dbmessage(nvl(l_count , 0) + 1).program_unit := P_Program_unit ;
1221 End set_dbmessage;
1222
1223 FUNCTION get_dbmessage return
1224 DbMessageTabType
1225 is
1226 begin
1227 return(MSC_UTIL.g_dbmessage);
1228 end get_dbmessage;
1229
1230 /*-----------------------------------------------------------------------------
1231 Function : MSC_NUMVAL
1232
1233 Parameters : p_input (IN) - string which needs to be converted in to numeric values
1234
1235 Description : this function will return the numeric value of any valid string.
1236 If the input string is not in valid numeric format, it returns null.
1237 This function is implemented as there is no equivalent to IS_NUMERIC in Oracle.
1238 -----------------------------------------------------------------------------*/
1239 FUNCTION MSC_NUMVAL(p_input varchar2) return NUMBER IS
1240 BEGIN
1241 BEGIN
1242 RETURN to_number(p_input);
1243 EXCEPTION
1244 WHEN OTHERS THEN
1245 IF SQLCODE IN (-01722, -06502) THEN RETURN null;
1246 ELSE raise;
1247 END IF;
1248 END;
1249 END MSC_NUMVAL;
1250
1251 -- -------------------------------------
1252 -- called from ASCP plan options screen
1253 -- -------------------------------------
1254
1255 FUNCTION is_app_installed(p_product IN NUMBER) RETURN BOOLEAN IS
1256 l_status VARCHAR2(30);
1257 l_industry VARCHAR2(30);
1258 l_schema VARCHAR2(30);
1259 l_prod_short_name VARCHAR2(30);
1260 BEGIN
1261 l_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(p_product);
1262 IF fnd_installation.get_app_info(l_prod_short_name, l_status, l_industry, l_schema) <> TRUE THEN
1263 RETURN FALSE;
1264 ELSE
1265 IF l_status = 'I' THEN
1266 RETURN TRUE;
1267 ELSE
1268 RETURN FALSE;
1269 END IF;
1270 END IF;
1271 END is_app_installed;
1272
1273 -- ----------------------------
1274 -- called from Instances screen
1275 -- and ASCP plan options screen
1276 -- ----------------------------
1277
1278 FUNCTION get_aps_config_level(p_sr_instance_id IN Number, p_dblink IN VARCHAR2 DEFAULT NULL) RETURN NUMBER IS
1279
1280 l_profile CONSTANT VARCHAR2(80) := 'GMP_APS_CONFIG_LEVEL';
1281
1282 CURSOR db_cur(pv_instance_id NUMBER) IS
1283 SELECT DECODE(M2A_dblink,null,' ','@'||M2A_dblink)
1284 FROM msc_apps_instances
1285 WHERE instance_id = pv_instance_id;
1286
1287 l_level NUMBER;
1288 l_level_dest NUMBER;
1289
1290 l_dblink VARCHAR2(100);
1291 l_sql_stmt VARCHAR2(500);
1292 INVALID_IDENTIFIER EXCEPTION;
1293 PRAGMA EXCEPTION_INIT(INVALID_IDENTIFIER, -6550);
1294 BEGIN
1295 IF p_dblink IS NULL THEN
1296 OPEN db_cur(p_sr_instance_id);
1297 FETCH db_cur INTO l_dblink;
1298 IF db_cur%NOTFOUND THEN
1299 l_level := 3;
1300 END IF;
1301 CLOSE db_cur;
1302 ELSE
1303 l_dblink := '@'||P_dblink;
1304 l_sql_stmt := ' BEGIN'
1308 EXECUTE IMMEDIATE l_sql_stmt USING OUT l_level,
1305 ||' :v_level:= nvl(fnd_profile.value'||l_dblink||'(:p_profile),3);'
1306 ||' END;';
1307
1309 IN l_profile;
1310 END IF;
1311 l_level_dest := NVL(fnd_profile.value(l_profile),3);
1312
1313 IF l_level < l_level_dest THEN
1314 RETURN l_level;
1315 ELSE
1316 RETURN l_level_dest;
1317 END IF;
1318 EXCEPTION
1319 WHEN INVALID_IDENTIFIER THEN
1320 RETURN -23453;
1321 WHEN OTHERS THEN
1322 RETURN -23453;
1323 END get_aps_config_level;
1324
1325 PROCEDURE initialize_common_globals(pINSTANCE_ID IN NUMBER)
1326 IS
1327 v_apps_ver NUMBER;
1328 BEGIN
1329
1330
1331 BEGIN
1332 SELECT ITEM_TYPE_ID, ITEM_TYPE_VALUE
1333 INTO G_PARTCONDN_ITEMTYPEID, G_PARTCONDN_GOOD
1334 FROM MSC_ITEM_TYPE_DEFINITIONS
1335 WHERE ITEM_TYPE_NAME = 'PART_CONDITION'
1336 AND ITEM_TYPE_VALUE_MEANING = 'USABLE';
1337
1338 SELECT ITEM_TYPE_VALUE
1339 INTO G_PARTCONDN_BAD
1340 FROM MSC_ITEM_TYPE_DEFINITIONS
1341 WHERE ITEM_TYPE_NAME = 'PART_CONDITION'
1342 AND ITEM_TYPE_VALUE_MEANING = 'DEFECTIVE';
1343
1344 EXCEPTION
1345 WHEN NO_DATA_FOUND THEN
1346 LOG_MSG(G_LVL_FATAL_ERR,'Seed Data not found for Item Part condition');
1347 RAISE;
1348 END;
1349
1350 ------ set v_in_org_str and v_in_all_org_str----------
1351
1352 MSC_UTIL.v_in_org_str := msc_cl_pull.get_org_str(pinstance_id,2);
1353 MSC_UTIL.v_in_all_org_str := msc_cl_pull.get_org_str(pinstance_id,3);
1354
1355 IF MSC_UTIL.G_COLLECT_SRP_DATA = 'Y' THEN
1356 SELECT APPS_VER
1357 INTO v_apps_ver
1358 FROM MSC_APPS_INSTANCES
1359 WHERE INSTANCE_ID= pINSTANCE_ID;
1360
1361 IF (v_apps_ver<> -1 AND v_apps_ver < MSC_UTIL.G_APPS115) THEN --bug#5684183 (bcaru)
1362 MSC_UTIL.G_COLLECT_SRP_DATA := 'N' ; --SRP not supported for version < 12.1
1363 LOG_MSG(G_LVL_FATAL_ERR,'v115 SRP data is not collected because of wrong source version...');
1364 ELSE
1365 MSC_CL_PULL.GET_DEPOT_ORG_STRINGS(pINSTANCE_ID); -- For Bug 5909379
1366 END IF;
1367 END IF;
1368
1369 BEGIN
1370 SELECT NVL(TO_NUMBER(FND_PROFILE.VALUE('MSC_COLLECTION_WINDOW_FOR_TP_CHANGES')),0)
1371 INTO v_msc_tp_coll_window
1372 FROM DUAL;
1373 EXCEPTION
1374 WHEN OTHERS THEN
1375 v_msc_tp_coll_window := 0;
1376 END ;
1377
1378
1379
1380 END initialize_common_globals;
1381
1382 FUNCTION mv_exists_in_schema(p_schema_name VARCHAR2, p_MV_name VARCHAR2) RETURN BOOLEAN IS
1383 lv_exists number;
1384 begin
1385 EXECUTE IMMEDIATE
1386 ' select 1
1387 from all_mviews
1388 where mview_name =:p1
1389 and owner = :p2 '
1390 into lv_exists using p_MV_name, p_schema_name;
1391
1392 if lv_exists = 1 then
1393 return TRUE;
1394 else
1395 return FALSE;
1396 end if;
1397 Exception
1398 WHEN NO_DATA_FOUND THEN
1399 return FALSE;
1400 END mv_exists_in_schema;
1401
1402
1403 FUNCTION GET_SERVICE_ITEMS_CATSET_ID RETURN NUMBER IS
1404 l_cat_set VARCHAR2(30) := NULL ;
1405 BEGIN
1406 IF G_CAT_SET_ID IS NULL THEN
1407
1408 l_cat_set := FND_PROFILE.VALUE('MSC_SERVICE_ITEMS_CATSET') ;
1409 SELECT CATEGORY_SET_ID INTO G_CAT_SET_ID
1410 FROM MSC_CATEGORY_SETS
1411 WHERE CATEGORY_SET_NAME = l_cat_set;
1412 END IF ;
1413 RETURN G_CAT_SET_ID;
1414 EXCEPTION
1415 WHEN NO_DATA_FOUND THEN
1416 RETURN NULL;
1417 END GET_SERVICE_ITEMS_CATSET_ID ;
1418
1419 -- Procedure to execute any API given as parameter Bug 6469713
1420 PROCEDURE EXECUTE_API(ERRBUF OUT NOCOPY VARCHAR2,
1421 RETCODE OUT NOCOPY NUMBER,
1422 p_package_name IN VARCHAR2,
1423 p_proc_name IN VARCHAR2 ,
1424 comma_sep_para_list IN VARCHAR2) IS
1425 lv_exists number := 0;
1426 lv_str varchar2(2000);
1427 lv_sql_str varchar2(2000);
1428
1429 BEGIN
1430 /* To Check if the object is existing and VAlid;*/
1431 BEGIN
1432 lv_sql_str := ' select 1
1433 from all_objects
1434 where object_name = :p_package_name
1435 and owner = :p2
1436 and object_type = ''PACKAGE''
1437 and status =''VALID''
1438 ';
1439 Execute immediate lv_sql_str into lv_exists
1440 USING
1441 p_package_name,MSC_UTIL.G_APPS_SCHEMA;
1442
1443 EXCEPTION WHEN no_data_found THEN
1444 RAISE_APPLICATION_ERROR(-20056,'Package name does not exists or is Invalid');
1445 END;
1446
1447 IF lv_exists = 1 Then
1448 Begin
1449 /* If Package exists then submitting the block */
1450 lv_str := 'BEGIN '||
1451 p_package_name||'.'|| p_proc_name||
1452 '(' || comma_sep_para_list || ');' ||
1453 ' END;';
1454
1455 Execute immediate lv_str;
1456
1457 EXCEPTION
1458 WHEN OTHERS THEN
1459 LOG_MSG(G_LVL_FATAL_ERR,'Error while trying to execute the API');
1460 LOG_MSG(G_LVL_FATAL_ERR,SQLERRM);
1461 RETCODE:= G_ERROR;
1462 RAISE;
1463 End;
1464 --ERRBUF := 'NO_USER_DEFINED';
1465 End if;
1466 END;
1467
1468 PROCEDURE DROP_MVIEW_SYNONYMS(mview_owner VARCHAR2, mview_name VARCHAR2) IS
1469 lv_sql varchar2(1000);
1470 begin
1471 /* droping only the synonym with the same name in APPS schema.
1472 for i in (SELECT syn.owner SYNONYM_owner, syn.synonym_name
1473 FROM --fnd_lookup_values a,
1474 all_synonyms syn
1475 WHERE syn.table_owner = mview_owner and
1476 syn.table_name = mview_name
1477 )
1478 loop
1479 BEGIN
1480 IF I.SYNONYM_owner ='PUBLIC' THEN
1481 lv_sql:='DROP PUBLIC SYNONYM '||i.synonym_name;
1482 ELSE
1483 lv_sql:='DROP SYNONYM '||I.SYNONYM_owner||'.'||i.synonym_name;
1484 END IF;
1485 */
1486 lv_sql:='DROP SYNONYM '|| mview_name;
1487 EXECUTE IMMEDIATE lv_sql;
1488
1489 EXCEPTION
1490 WHEN OTHERS THEN
1491 IF instr(DBMS_UTILITY.FORMAT_ERROR_STACK ,'ORA-01434') > 0 OR
1492 instr(DBMS_UTILITY.FORMAT_ERROR_STACK ,'ORA-01432') > 0 THEN
1493 NULL; --private/public synonym to be dropped does not exist
1494 ELSE
1495 RAISE_APPLICATION_ERROR(-20001,'Error while executing:-'||lv_sql || ': ' || SQLERRM);
1496 END IF;
1497 /* END;
1498 end loop;*/
1499 END DROP_MVIEW_SYNONYMS;
1500
1501 PROCEDURE DROP_MVIEW_TRIGGERS(mview_owner VARCHAR2, mview_name VARCHAR2) IS
1502 lv_sql varchar2(1000);
1503 begin
1504 for i in (SELECT trg.owner, trg.trigger_name
1505 FROM all_TRIGGERS TRG
1506 WHERE trg.table_owner = mview_owner and
1507 trg.table_name = mview_name
1508 )
1509 loop
1510 BEGIN
1511 lv_sql:= 'DROP TRIGGER '||I.owner||'.'||i.trigger_name;
1512 EXECUTE IMMEDIATE lv_sql;
1513
1514 EXCEPTION
1515 WHEN OTHERS THEN
1516 IF instr(DBMS_UTILITY.FORMAT_ERROR_STACK ,'ORA-04080') > 0 THEN
1517 NULL;
1518 ELSE
1519 RAISE_APPLICATION_ERROR(-20001,'Error while executing:-'||lv_sql || ': ' || SQLERRM);
1520 END IF;
1521 END;
1522 end loop;
1523 END DROP_MVIEW_TRIGGERS;
1524
1525 PROCEDURE DROP_WRONGSCHEMA_MVIEWS IS
1526 lv_sql varchar2(1000);
1527 lv_Nologging_tblsp varchar2(30);
1528 begin
1529 for i in (SELECT mview_name,msc_util.GET_SCHEMA_NAME(erp_product_code) mview_owner
1530 FROM msc_coll_snapshots_v
1531 WHERE mview_name <> 'ALL SNAPSHOTS'
1532 UNION /*Old Mviews that need to be dropped*/
1533 Select 'BOM_CTO_ORDER_DMD_SN',msc_util.G_BOM_SCHEMA
1534 FROM Dual)
1535 loop
1536 BEGIN
1537 lv_sql:='DROP MATERIALIZED VIEW '||I.mview_owner||'.'||i.mview_name;
1538 EXECUTE IMMEDIATE lv_sql;
1539 EXCEPTION
1540 WHEN OTHERS THEN
1541 IF instr(DBMS_UTILITY.FORMAT_ERROR_STACK ,'ORA-12003') > 0 THEN --materialized view does not exist
1542 NULL;--materialized view does not exist
1543 ELSE
1544 RAISE_APPLICATION_ERROR(-20001,'Error while executing:-'||lv_sql || ': ' || SQLERRM);
1545 END IF;
1546 END;
1547
1548 DROP_MVIEW_TRIGGERS(i.mview_owner, i.mview_name);
1549 DROP_MVIEW_SYNONYMS(i.mview_owner, i.mview_name);
1550 end loop;
1551
1552 -- drop MVs which are not in NOLLOGING tblspc
1553 select tablespace into lv_Nologging_tblsp from FND_TABLESPACES where tablespace_type = 'NOLOGGING';
1554 FOR j IN(select a.mview_name,c.table_name,c.TABLESPACE_NAME
1555 from MSC_COLL_SNAPSHOTS_V a, ALL_MVIEWS b,ALL_TABLES c
1556 where a.mview_name = b.mview_name
1557 AND b.OWNER = G_APPS_SCHEMA
1558 AND b.CONTAINER_NAME = c.table_name
1559 AND c.owner = G_APPS_SCHEMA
1560 AND c.TABLESPACE_NAME <> lv_Nologging_tblsp )
1561 loop
1562 lv_sql:='DROP MATERIALIZED VIEW '||G_APPS_SCHEMA||'.'||j.mview_name;
1563
1564 EXECUTE IMMEDIATE lv_sql;
1565 DROP_MVIEW_TRIGGERS(G_APPS_SCHEMA, j.mview_name);
1566 DROP_MVIEW_SYNONYMS(G_APPS_SCHEMA, j.mview_name);
1567 end loop;
1568
1569 END;
1570
1571 /*Old Mviews that need to be dropped*/
1572 PROCEDURE DROP_DEPRECATED_MVIEWS IS
1573 lv_sql varchar2(1000);
1574 BEGIN
1575 for i in 1..v_deprecatedMVList.COUNT
1576 loop
1577 BEGIN
1578 lv_sql:='DROP MATERIALIZED VIEW '||v_deprecatedMVSchemaList(i)||'.'||v_deprecatedMVList(i);
1579 EXECUTE IMMEDIATE lv_sql;
1580 EXCEPTION
1581 WHEN OTHERS THEN
1582 IF instr(DBMS_UTILITY.FORMAT_ERROR_STACK ,'ORA-12003') > 0 THEN --materialized view does not exist
1583 NULL;--materialized view does not exist
1584 ELSE
1585 RAISE_APPLICATION_ERROR(-20001,'Error while executing:-'||lv_sql || ': ' || SQLERRM);
1586 END IF;
1587 END;
1588
1589 DROP_MVIEW_TRIGGERS(v_deprecatedMVSchemaList(i), v_deprecatedMVList(i));
1590 DROP_MVIEW_SYNONYMS(v_deprecatedMVSchemaList(i), v_deprecatedMVList(i));
1591 end loop;
1592
1593 END;
1594
1595 /* ======== Addition of code w.r.t Bug 13602645 by ngaddamp starts here ========== */
1596 /* ======== Drop Orphan MV container tables ========== */
1597
1598 PROCEDURE DROP_ORPHAN_MVIEW_CONTAINERS IS
1599
1600 lv_sql_stmt VARCHAR2(1000);
1601
1602 CURSOR tabWithoutMV IS
1603 SELECT table_name,owner
1604 FROM sys.dba_tables dt
1605 WHERE table_name IN (SELECT mview_name
1606 FROM msc_coll_snapshots_v)
1607 AND NOT EXISTS (SELECT 1
1608 FROM sys.dba_objects do
1609 WHERE do.object_type='MATERIALIZED VIEW'
1610 AND do.owner=dt.owner
1611 AND do.object_name = dt.table_NAME);
1612 BEGIN
1613
1614 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'Deleting orphan MV container tables ');
1615
1616 FOR c_rec IN tabWithoutMV
1617
1618 LOOP
1619
1620 BEGIN
1621 lv_sql_stmt := 'DROP TABLE '||c_rec.owner||'.'||c_rec.table_name;
1622
1623 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'The Table dropped is: '||c_rec.owner||'.'||c_rec.table_name);
1624
1625 EXECUTE IMMEDIATE lv_sql_stmt;
1626
1627 EXCEPTION
1628 WHEN OTHERS THEN
1629
1630 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'The table FAILED to be dropped is:'||c_rec.owner||'.'||c_rec.table_name);
1631
1632 NULL;
1633 END;
1634
1635 END LOOP;
1636
1637 EXCEPTION
1638 WHEN NO_DATA_FOUND THEN
1639
1640 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_STATUS, 'No Records found for Orphan MV containers ');
1641
1642 WHEN OTHERS THEN
1643
1644 RAISE_APPLICATION_ERROR(-20001, 'MSCUTILSB: Error in Dropping the table ' || sqlerrm);
1645
1646 END ;
1647
1648 /* ======== Addition of code w.r.t Bug 13602645 by ngaddamp ends here ========== */
1649
1650
1651 BEGIN -- pkg initialization section
1652 -- set globals for schema name
1653 select oracle_username
1654 into G_APPS_SCHEMA
1655 from fnd_oracle_userid
1656 where read_only_flag = 'U';
1657 begin
1658 G_AHL_SCHEMA := GET_SCHEMA_NAME(867) ;
1659 exception
1660 when others then
1661 G_AHL_SCHEMA:=null;
1662 end;
1663 G_INV_SCHEMA := GET_SCHEMA_NAME(401) ;
1664 G_BOM_SCHEMA := GET_SCHEMA_NAME(702) ;
1665 G_PO_SCHEMA := GET_SCHEMA_NAME(201) ;
1666 G_WSH_SCHEMA := GET_SCHEMA_NAME(665) ;
1667 G_EAM_SCHEMA := GET_SCHEMA_NAME(426) ;
1668 G_ONT_SCHEMA := GET_SCHEMA_NAME(660) ;
1669 G_MRP_SCHEMA := GET_SCHEMA_NAME(704) ;
1670 G_WSM_SCHEMA := GET_SCHEMA_NAME(410) ;
1671 G_CSP_SCHEMA := GET_SCHEMA_NAME(523) ;
1672 G_WIP_SCHEMA := GET_SCHEMA_NAME(706) ;
1673 G_CSD_SCHEMA := GET_SCHEMA_NAME(512) ;
1674 G_MSC_SCHEMA := GET_SCHEMA_NAME(724) ;
1675 G_FND_SCHEMA := GET_SCHEMA_NAME(0) ;
1676 -- end set globals for schema name
1677
1678 begin
1679 select sid
1680 into G_CURRENT_SESSION_ID
1681 from v$session
1682 where audsid = SYS_CONTEXT('USERENV','SESSIONID');
1683 exception when others then
1684 G_CURRENT_SESSION_ID := 0;
1685 end ;
1686
1687 EXCEPTION
1688 WHEN OTHERS THEN
1689 RAISE_APPLICATION_ERROR(-20001,'MSC_UTIL:Error while initilizing Global Variables for Source Schema names: ' || SQLERRM);
1690 END MSC_UTIL;