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