eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.BIS_BIA_RSG_INDEX_MGMT

Source


1 PACKAGE BODY BIS_BIA_RSG_INDEX_MGMT AS
2 /* $Header: BISBRIMB.pls 120.2 2006/01/24 09:32:33 aguwalan noship $ */
3 
4 procedure setTimer(
5     p_log_timstamp in out NOCOPY date)
6   IS
7   BEGIN
8     p_log_timstamp := sysdate;
9   END;
10 
11 PROCEDURE WRITELOG(P_TEXT VARCHAR2)
12   IS
13   L_TEXT VARCHAR2(255);
14   BEGIN
15     BIS_COLLECTION_UTILITIES.put_line(P_TEXT);
16     BIS_COLLECTION_UTILITIES.put_line(' ');
17 /*    if(length(P_TEXT)>255) then
18       L_TEXT :=substr(P_TEXT, 0, 254);
19     else
20       L_TEXT := P_TEXT ;
21     end if;
22     DBMS_OUTPUT.PUT_LINE(L_TEXT);
23     DBMS_OUTPUT.PUT_LINE(' ');
24     DBMS_OUTPUT.PUT_LINE(' ');   */
25   END;
26 
27 FUNCTION duration(
28 	p_duration		number) return VARCHAR2 IS
29   BEGIN
30     return(to_char(floor(p_duration)) ||' Days '||
31            to_char(mod(floor(p_duration*24), 24))||':'||
32            to_char(mod(floor(p_duration*24*60), 60))||':'||
33            to_char(mod(floor(p_duration*24*60*60), 60)));
34   END duration;
35 
36 PROCEDURE logTime(
37     p_process        varchar2,
38     p_log_timstamp   date)
39   IS
40     -- modified for GSCC
41     l_duration     number ;
42   BEGIN
43     l_duration := sysdate - p_log_timstamp;
44     WRITELOG('Process Time for '|| p_process || ' : ' || duration(l_duration));
45   END;
46 
47 
48 Function form_triplet( att1 varchar2,
49                        att2 varchar2,
50                        att3 varchar2  ) return varchar2
51   IS
52     --- modified for GSCC and decrease the size of the variable as it
53     --- was unneccessary
54     l_result varchar2(200) ;
55   BEGIN
56     l_result := '( ' || att1 || ', ' || att2 || ', ' || att3 || ') ';
57     return l_result;
58   END;
59 
60 
61 function is_index_mgmnt_profile_set
62   return boolean
63   is
64   begin
65     IF (fnd_profile.value('BIS_BIA_MV_INDEX_ENABLE') = 'Y') THEN
66       return true;
67     ELSE
68       WRITELOG('Runtime BIA MV Log Management feature is off');
69       return false;
70     END IF;
71   end;
72 
73 
74 
75 function is_Index_Mgmt_Enabled(p_mv_name in varchar2, p_mv_schema in varchar2) return varchar2 -- p_mv_schema is no more used after bug 4186097
76 is
77   TYPE curType IS REF CURSOR;
78   c_index_mgmt_flag curType;
79   l_stmt varchar2(1000);
80   -- modified to remove GSCC warning  File.Sql.35
81   l_index_mgmt_enabled varchar2(1);
82   begin
83     l_index_mgmt_enabled := 'N';
84     l_stmt := 'select Drop_create_index_flag from bis_obj_properties where OBJECT_TYPE=''MV'' and OBJECT_NAME=:1'; --bug 4186097. removed p_mv_schema check, because p_mv_schema gives the Schema in which
85                                                                                                                                     --the MV is created, whereas OBJECT_OWNER is a product short name to whom the MV belongs.
86     open c_index_mgmt_flag for l_stmt using p_mv_name;
87     fetch c_index_mgmt_flag into l_index_mgmt_enabled;
88     close c_index_mgmt_flag;
89     if (l_index_mgmt_enabled = 'Y' or l_index_mgmt_enabled = 'y') then --bug 4186097
90       return 'Y';
91     else
92       return 'N';
93     end if;
94   end;
95 
96 
97 
98 PROCEDURE drop_mv_index(p_index_name in varchar2, p_index_schema in varchar2)
99 is
100   l_time date;
101   l_dur  number;
102   -- modified to remove GSCC File.Sql.35, decreased the length of the variable to 200 from 1000 also
103   l_stmt varchar2(200);
104 
105 BEGIN
106   l_stmt := 'DROP INDEX ' || p_index_schema||'.'||p_index_name;
107   setTimer(l_time);
108   WRITELOG('Executing ' || l_stmt);
109   execute immediate l_stmt;
110   logTime( 'Successfuly dropped mv index of ' || form_triplet(p_index_name, P_index_schema, null) , l_time);
111   WRITELOG('************************************');
112 
113 EXCEPTION WHEN OTHERS THEN
114     WRITELOG('In procedure drop_mv_index, failed droping ' ||  p_index_schema||'.'||p_index_name || sqlerrm);
115     raise;
116 end;
117 
118 
119 procedure Capture_and_drop_index_by_mv(p_mv_name in varchar2, p_mv_schema in varchar2)
120 is
121   -- modified to remove GSCC warning File.Sql.35
122   l_mv_index_handle clob;
123   l_time date;
124   l_length number;
125   l_stmt varchar2(32767);
126   TYPE curType IS REF CURSOR ;
127   c_all_index_details	curType;
128   l_index_name bis_obj_indexes.index_name%type;
129   l_schema all_indexes.owner%type;
130   l_ddl clob;
131 begin
132   -- added to remove GSCC warning File.Sql.35
133   l_mv_index_handle := null;
134   l_length          := 0;
135   l_stmt            := null;
136 
137   if(NOT is_index_mgmnt_profile_set) then
138     WRITELOG('No further action performed!');
139     return;
140   end if;
141 
142 
143   setTimer(l_time);
144 
145   WRITELOG('Capturing MV Index for ' || form_triplet(p_mv_name, p_mv_schema, null));
146   -- bug#4704403
147   --l_mv_index_handle := sys.ad_dbms_metadata.get_dependent_ddl('INDEX',p_mv_name, p_mv_schema);
148 
149     l_stmt := 'SELECT INDEX_NAME, OWNER, TO_CHAR(sys.ad_dbms_metadata.GET_DDL(''INDEX'',INDEX_NAME,OWNER)) FROM '||
150     '(select index_name , OWNER from all_indexes where table_name = :1 and owner = :2)';
151 
152   open c_all_index_details for l_stmt using p_mv_name, p_mv_schema;
153     loop
154       fetch c_all_index_details	into l_index_name, l_schema, l_ddl;
155       exit when c_all_index_details%NOTFOUND;
156 
157       if l_ddl is not null or l_ddl <> '' then
158         update bis_obj_indexes  set  INDEX_SQL = l_ddl,  LAST_UPDATE_DATE = sysdate, LAST_UPDATED_BY = FND_GLOBAL.User_id,  LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID where OBJECT_NAME=p_mv_name and OBJECT_TYPE='MV' and INDEX_NAME=l_index_name;
159         IF SQL%NOTFOUND THEN
160           INSERT INTO bis_obj_indexes( OBJECT_NAME, OBJECT_TYPE, INDEX_NAME, INDEX_SQL, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN)
161           		values(p_mv_name, 'MV', l_index_name, l_ddl, sysdate, FND_GLOBAL.User_id, sysdate, FND_GLOBAL.User_id, fnd_global.LOGIN_ID);
162         END IF;
163         COMMIT;
164         drop_mv_index(l_index_name, p_mv_schema);
165       end if;
166     end loop;
167   close c_all_index_details	;
168 
169   logTime( 'Dropping mv index of ' || form_triplet(p_mv_name, p_mv_schema, l_index_name) , l_time);
170   WRITELOG('************************************');
171 
172 
173 
174 EXCEPTION WHEN OTHERS THEN
175     WRITELOG('In procedure Capture_and_drop_index_by_mv, failed capturing MV Index for ' ||  form_triplet(p_mv_name, p_mv_schema, l_index_name) || ', ' ||sqlerrm);
176     raise;
177 
178 
179 end;
180 
181 
182 
183 procedure recreate_indexes_by_mv(ERRBUF OUT NOCOPY VARCHAR2, RETCODE OUT NOCOPY VARCHAR2, p_mv_name in varchar2,P_mv_schema in varchar2)
184 is
185   l_stmt varchar2(300);
186   TYPE curType IS REF CURSOR ;
187   c_index_sql	curType;
188   l_index_name varchar2(30);
189   l_index_ddl varchar2(32767);--clob;
190   temp_index_ddl varchar2(32767);--clob;
191   l_time date;
192   count_created Number; -- bug 4284095
193   count_exists  Number; -- bug 4284095
194   l_program_status boolean; -- bug 4284095
195 
196   --bug 4312072
197   l_mv_name varchar2(30);
198   l_mv_schema varchar2(30);
199 begin
200   ERRBUF:= NULL;
201   RETCODE:= '0';
202 
203   if(NOT is_index_mgmnt_profile_set) then
204     WRITELOG('Index management Profile is False! No further action performed!');
205     return;
206   end if;
207 
208   --code added for bug 4284095
209   if(p_mv_name is null or P_mv_schema is null) then
210     WRITELOG('Warning!! Program will exit as parameters are NULL');
211     l_program_status := fnd_concurrent.set_completion_status('WARNING' ,NULL);
212     RETCODE := 2;
213     return ;
214   end if;
215 
216   --added for bug 4312072
217   l_mv_name   := upper(p_mv_name);
218   l_mv_schema := upper(P_mv_schema);
219 
220   -- added for bug 4284188
221   -- this API can be run directly when user run concurrent program 'Recreate MV index Management', that time we
222   -- need to check seperately if the flag set or not.
223   if (is_Index_Mgmt_Enabled(l_mv_name,l_mv_schema) = 'N')then
224        WRITELOG('The MV '||l_mv_name||' is not seeded in RSG or MV Index management is disabled for MV ' ||l_mv_name||'.  No further action performed!');
225        return ;
226   end if;
227 
228   count_created :=0; -- this counter will be used created indexes -- bug 4284095
229   count_exists  :=0;  -- bug 4284095
230 
231   setTimer(l_time);
232   WRITELOG('Recreating MV Index for ' || form_triplet(l_mv_name, l_mv_schema, null));
233 
234   -- bug 4312072
235   l_stmt := 'select INDEX_NAME, INDEX_SQL from BIS_OBJ_INDEXES where OBJECT_NAME='''
236             ||l_mv_name||
237 	    ''' and OBJECT_TYPE=''MV'' and INDEX_SQL is not null';
238 
239   open c_index_sql for l_stmt;
240   loop
241     fetch c_index_sql into l_index_name, l_index_ddl;
242     exit when c_index_sql%NOTFOUND;
243     logTime( form_triplet(l_mv_name, l_mv_schema, l_index_name) , l_time);
244     if l_index_ddl is not null /*and l_index_ddl <> ''*/ then
245       BEGIN
246       logTime( 'Checking for existence of ' || l_index_name, l_time);
247       -- bug#4704403
248       temp_index_ddl:= to_char(sys.ad_dbms_metadata.get_ddl('INDEX',l_index_name));
249       logTime( 'Index exists ' || l_index_name, l_time);
250       count_exists := count_exists + 1;   -- bug 4284095
251       EXCEPTION WHEN OTHERS THEN --Index doesnt exists already . We can recreate now without ORA error.
252         logTime( 'index does not exist, recreating index ' || l_index_name, l_time);
253         execute immediate l_index_ddl;
254 	count_created := count_created + 1;   -- bug 4284095
255         logTime( 'recreated index ' || l_index_name, l_time);
256       END;
257     end if;
258   end loop;
259   close c_index_sql;
260 
261   --code for bug 4284095
262   If(count_created =0 and count_exists =0) then
263     WRITELOG('No Index has been created, As there is no index associated for');
264     WRITELOG(' given MV '|| l_mv_name);
265   else If (count_exists >0 and count_created =0) then
266          WRITELOG('All indexes for the MV '||l_mv_name||' already existed in the system');
267 	 WRITELOG('so no indexes were recreated');
268        end if;
269   end if;
270   WRITELOG('************************************');
271 
272 EXCEPTION WHEN OTHERS THEN
273     WRITELOG('In recreate_indexes_by_mv, failed recreating MV Index for ' ||  form_triplet(l_mv_name, l_mv_schema, l_index_name) || 'due to ' ||sqlerrm);
274 
275     ERRBUF:=SQLERRM;
276     RETCODE:=SQLCODE;
277 
278     raise;
279 
280 end;
281 
282 procedure recreate_indexes_by_mv_wrapper(p_mv_name in varchar2,P_mv_schema in varchar2) is
283 ERRBUF VARCHAR2(2000);
284 RETCODE VARCHAR2(2000);
285 begin
286 
287   if(NOT is_index_mgmnt_profile_set) then
288     WRITELOG('No further action performed!');
289     return;
290   end if;
291 
292   recreate_indexes_by_mv(ERRBUF, RETCODE, p_mv_name, P_mv_schema);
293 end;
294 
295 
296 procedure enable_index_mgmt(p_mv_name in varchar2,P_mv_schema in varchar2)
297 is
298   begin
299   update bis_obj_properties set DROP_CREATE_INDEX_FLAG='Y', LAST_UPDATED_BY= FND_GLOBAL.User_id, LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID, LAST_UPDATE_DATE = sysdate  where OBJECT_NAME=p_mv_name and OBJECT_OWNER=p_mv_schema and OBJECT_TYPE='MV';
300   COMMIT;
301 
302   EXCEPTION WHEN OTHERS THEN
303     WRITELOG('In procedure enable_index_mgmt, failed enabling index mgmt flag' ||  form_triplet(p_mv_name, p_mv_schema, null) || ', ' ||sqlerrm);
304     raise;
305 
306   end;
307 
308 
309 procedure disable_index_mgmt(p_mv_name in varchar2,P_mv_schema in varchar2)
310 is
311   begin
312   update bis_obj_properties set DROP_CREATE_INDEX_FLAG='N', LAST_UPDATED_BY= FND_GLOBAL.User_id, LAST_UPDATE_LOGIN = fnd_global.LOGIN_ID, LAST_UPDATE_DATE = sysdate  where OBJECT_NAME=p_mv_name and OBJECT_OWNER=p_mv_schema and OBJECT_TYPE='MV';
313   COMMIT;
314 
315   EXCEPTION WHEN OTHERS THEN
316     WRITELOG('In procedure disable_index_mgmt, failed enabling index mgmt flag' ||  form_triplet(p_mv_name, p_mv_schema, null) || ', ' ||sqlerrm);
317     raise;
318 
319   end;
320 
321 
322 END;
323 
324