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.EDW_ANALYZE_OBJECT

Source


1 package body EDW_ANALYZE_OBJECT AS
2 /*$Header: EDWANYZB.pls 115.6 2003/11/06 00:55:19 vsurendr noship $*/
3 
4 procedure Analyze_Dimension(Errbuf out NOCOPY varchar2,Retcode out NOCOPY varchar2,
5 p_dim_name in varchar2,p_mode number) IS
6 Begin
7   Retcode:='0';
8   g_mode:=p_mode;
9   if g_mode is null then
10     g_mode:=0;--ltc and star 1 is lstg only
11   end if;
12   EDW_OWB_COLLECTION_UTIL.init_all('ANALYZE_OBJECTS',null,'bis.edw.analyze_object');
13   EDW_OWB_COLLECTION_UTIL.set_debug(true);
14   init_all;
15   if analyze_dimension(p_dim_name)=false then
16     Retcode:='2';
17     Errbuf:=g_status_message;
18     return;
19   end if;
20 Exception when others then
21   g_status_message:='Error in Analyze_Dimension '||sqlerrm;
22   write_to_log_file_n(g_status_message);
23   Retcode:='2';
24   Errbuf:=g_status_message;
25 End;
26 
27 procedure Analyze_Fact(Errbuf out NOCOPY varchar2,Retcode out NOCOPY varchar2,
28 p_fact_name in varchar2,p_mode number) IS
29 Begin
30   Retcode:='0';
31   g_mode:=p_mode;
32   if g_mode is null then
33     g_mode:=0;--fact 1 is fstg only
34   end if;
35   EDW_OWB_COLLECTION_UTIL.init_all('ANALYZE_OBJECTS',null,'bis.edw.analyze_object');
36   EDW_OWB_COLLECTION_UTIL.set_debug(true);
37   init_all;
38   if analyze_fact(p_fact_name)=false then
39     Retcode:='2';
40     Errbuf:=g_status_message;
41     return;
42   end if;
43 Exception when others then
44   g_status_message:='Error in Analyze_Fact '||sqlerrm;
45   write_to_log_file_n(g_status_message);
46   Retcode:='2';
47   Errbuf:=g_status_message;
48 End;
49 
50 function analyze_dimension(p_dim_name varchar2) return boolean is
51 Begin
52   if get_dims(p_dim_name)=false then
53     return false;
54   end if;
55   if g_mode=0 then
56     if analyze_dims=false then
57       return false;
58     end if;
59   else
60     if analyze_dims_lstg=false then
61       return false;
62     end if;
63   end if;
64   return true;
65 Exception when others then
66   g_status_message:='Error in analyze_dimension '||sqlerrm;
67   write_to_log_file_n(g_status_message);
68   return false;
69 End;
70 
71 function get_dims(p_dim_name varchar2) return boolean is
72 l_stmt varchar2(4000);
73 TYPE CurTyp IS REF CURSOR;
74 cv   CurTyp;
75 Begin
76   write_to_log_file_n('In get_dims');
77   if p_dim_name is null then
78     g_number_dim:=1;
79     l_stmt:='select dim_name,dim_long_name from edw_dimensions_md_v';
80     write_to_log_file_n('Going to execute '||l_stmt||get_time);
81     open cv for l_stmt;
82     loop
83       fetch cv into g_dim(g_number_dim),g_dim_long(g_number_dim);
84       exit when cv%notfound;
85       g_number_dim:=g_number_dim+1;
86     end loop;
87     g_number_dim:=g_number_dim-1;
88     close cv;
89   else
90     g_number_dim:=1;
91     l_stmt:='select dim_name from edw_dimensions_md_v where dim_long_name=:a';
92     write_to_log_file_n('Going to execute '||l_stmt||get_time);
93     open cv for l_stmt using p_dim_name;
94     fetch cv into g_dim(g_number_dim);
95     close cv;
96     g_dim_long(g_number_dim):=p_dim_name;
97   end if;
98   write_to_log_file_n('Dimensions to analyze ');
99   for i in 1..g_number_dim loop
100     write_to_log_file(g_dim_long(i)||'('||g_dim(i)||')');
101   end loop;
102   return true;
103 Exception when others then
104   g_status_message:='Error in get_dims '||sqlerrm;
105   write_to_log_file_n(g_status_message);
106   return false;
107 End;
108 
109 function analyze_dims return boolean is
110 l_stmt varchar2(4000);
111 TYPE CurTyp IS REF CURSOR;
112 cv   CurTyp;
113 l_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
114 l_ltc_long EDW_OWB_COLLECTION_UTIL.varcharTableType;
115 l_ltc_number number;
116 Begin
117   write_to_log_file_n('In analyze_dims');
118   for i in 1..g_number_dim loop
119     write_to_log_file_n('Analyze '||g_dim(i)||' and ltc tables'||get_time);
120     write_to_out_file_n('Analyze '||g_dim_long(i)||'('||g_dim(i)||')'||get_time);
121     analyze_table(g_dim(i));
122     l_stmt:='select ltc.name,ltc.long_name '||
123     'from '||
124     'edw_tables_md_v ltc, '||
125     'edw_dimensions_md_v dim, '||
126     'edw_levels_md_v lvl '||
127     'where dim.dim_name=:a '||
128     'and lvl.dim_id=dim.dim_id '||
129     'and ltc.name=lvl.level_name||''_LTC''';
130     write_to_log_file_n('Going to execute '||l_stmt||get_time);
131     l_ltc_number:=1;
132     open cv for l_stmt using g_dim(i);
133     loop
134       fetch cv into l_ltc(l_ltc_number),l_ltc_long(l_ltc_number);
135       exit when cv%notfound;
136       l_ltc_number:=l_ltc_number+1;
137     end loop;
138     l_ltc_number:=l_ltc_number-1;
139     write_to_log_file_n('LTC tables');
140     for j in 1..l_ltc_number loop
141       write_to_log_file(l_ltc_long(j)||'('||l_ltc(j)||')');
142     end loop;
143     for j in 1..l_ltc_number loop
144       write_to_log_file_n('Analyze '||l_ltc(j)||get_time);
145       write_to_out_file_n('Analyze '||l_ltc_long(j)||'('||l_ltc(j)||')'||get_time);
146       analyze_table(l_ltc(j));
147     end loop;
148   end loop;
149   return true;
150 Exception when others then
151   g_status_message:='Error in analyze_dims '||sqlerrm;
152   write_to_log_file_n(g_status_message);
153   return false;
154 End;
155 
156 function analyze_dims_lstg return boolean is
157 l_stmt varchar2(4000);
158 TYPE CurTyp IS REF CURSOR;
159 cv   CurTyp;
160 l_lstg EDW_OWB_COLLECTION_UTIL.varcharTableType;
161 l_lstg_long EDW_OWB_COLLECTION_UTIL.varcharTableType;
162 l_lstg_number number;
163 Begin
164   for i in 1..g_number_dim loop
165     l_stmt:='select lstg.name,lstg.long_name '||
166     'from '||
167     'edw_tables_md_v ltc, '||
168     'edw_tables_md_v lstg, '||
169     'edw_pvt_map_properties_md_v map, '||
170     'edw_dimensions_md_v dim, '||
171     'edw_levels_md_v lvl '||
172     'where dim.dim_name=:a '||
173     'and lvl.dim_id=dim.dim_id '||
174     'and ltc.name=lvl.level_name||''_LTC'' '||
175     'and map.primary_target=ltc.elementid '||
176     'and lstg.elementid=map.primary_source ';
177     write_to_log_file_n('Going to execute '||l_stmt||get_time);
178     l_lstg_number:=1;
179     open cv for l_stmt using g_dim(i);
180     loop
181       fetch cv into l_lstg(l_lstg_number),l_lstg_long(l_lstg_number);
182       exit when cv%notfound;
183       l_lstg_number:=l_lstg_number+1;
184     end loop;
185     l_lstg_number:=l_lstg_number-1;
186     write_to_log_file_n('lstg tables');
187     for j in 1..l_lstg_number loop
188       write_to_log_file(l_lstg_long(j)||'('||l_lstg(j)||')');
189     end loop;
190     for j in 1..l_lstg_number loop
191       write_to_log_file_n('Analyze '||l_lstg(j)||get_time);
192       write_to_out_file_n('Analyze '||l_lstg_long(j)||'('||l_lstg(j)||')'||get_time);
193       analyze_table(l_lstg(j));
194     end loop;
195   end loop;
196   return true;
197 Exception when others then
198   g_status_message:='Error in analyze_dims_lstg '||sqlerrm;
199   write_to_log_file_n(g_status_message);
200   return false;
201 End;
202 
203 function analyze_fact(p_fact_name varchar2) return boolean is
204 Begin
205   if get_facts(p_fact_name)=false then
206     return false;
207   end if;
208   if g_mode=0 then
209     if analyze_facts=false then
210       return false;
211     end if;
212   else
213     if analyze_facts_fstg=false then
214       return false;
215     end if;
216   end if;
217   return true;
218 Exception when others then
219   g_status_message:='Error in analyze_fact '||sqlerrm;
220   write_to_log_file_n(g_status_message);
221   return false;
222 End;
223 
224 function get_facts(p_fact_name varchar2) return boolean is
225 l_stmt varchar2(4000);
226 TYPE CurTyp IS REF CURSOR;
227 cv   CurTyp;
228 Begin
229   write_to_log_file_n('In get_facts');
230   if p_fact_name is null then
231     g_number_fact:=1;
232     l_stmt:='select fact_name,fact_longname from edw_facts_md_v';
233     write_to_log_file_n('Going to execute '||l_stmt||get_time);
234     open cv for l_stmt;
235     loop
236       fetch cv into g_fact(g_number_fact),g_fact_long(g_number_fact);
237       exit when cv%notfound;
238       g_number_fact:=g_number_fact+1;
239     end loop;
240     g_number_fact:=g_number_fact-1;
241     close cv;
242   else
243     g_number_fact:=1;
244     l_stmt:='select fact_name from edw_facts_md_v where fact_longname=:a';
245     write_to_log_file_n('Going to execute '||l_stmt||get_time);
246     open cv for l_stmt using p_fact_name;
247     fetch cv into g_fact(g_number_fact);
248     close cv;
249     g_fact_long(g_number_fact):=p_fact_name;
250   end if;
251   write_to_log_file_n('Facts to analyze ');
252   for i in 1..g_number_fact loop
253     write_to_log_file(g_fact_long(i)||'('||g_fact(i)||')');
254   end loop;
255   return true;
256 Exception when others then
257   g_status_message:='Error in get_facts '||sqlerrm;
258   write_to_log_file_n(g_status_message);
259   return false;
260 End;
261 
262 function analyze_facts return boolean is
263 Begin
264   write_to_log_file_n('In analyze_facts');
265   for i in 1..g_number_fact loop
266     write_to_log_file_n('Analyze '||g_fact(i)||get_time);
267     write_to_out_file_n('Analyze '||g_fact_long(i)||'('||g_fact(i)||')'||get_time);
268     analyze_table(g_fact(i));
269   end loop;
270   return true;
271 Exception when others then
272   g_status_message:='Error in analyze_facts '||sqlerrm;
273   write_to_log_file_n(g_status_message);
274   return false;
275 End;
276 
277 function analyze_facts_fstg return boolean is
278 l_stmt varchar2(4000);
279 TYPE CurTyp IS REF CURSOR;
280 cv   CurTyp;
281 l_fstg varchar2(200);
282 l_fstg_long varchar2(400);
283 Begin
284   write_to_log_file_n('In analyze_facts_fstg');
285   for i in 1..g_number_fact loop
286     l_stmt:='select fstg.name,fstg.long_name '||
287     'from '||
288     'edw_tables_md_v fstg, '||
289     'edw_pvt_map_properties_md_v map, '||
290     'edw_facts_md_v fact '||
291     'where '||
292     'fact.fact_name=:a '||
293     'and map.primary_target=fact.fact_id '||
294     'and fstg.elementid=map.primary_source ';
295     write_to_log_file_n('Going to execute '||l_stmt||get_time);
296     open cv for l_stmt using g_fact(i);
297     fetch cv into l_fstg,l_fstg_long;
298     write_to_log_file_n('Analyze '||l_fstg||get_time);
299     write_to_out_file_n('Analyze '||l_fstg_long||'('||l_fstg||')'||get_time);
300     analyze_table(l_fstg);
301   end loop;
302   return true;
303 Exception when others then
304   g_status_message:='Error in analyze_facts_fstg '||sqlerrm;
305   write_to_log_file_n(g_status_message);
306   return false;
307 End;
308 
309 
310 procedure analyze_table(p_object varchar2) is
311 Begin
312   EDW_OWB_COLLECTION_UTIL.analyze_table_stats(p_object);
313 Exception when others then
314   write_to_log_file_n('Error in analyze_table '||sqlerrm);
315 End;
316 
317 
318 procedure init_all is
319 Begin
320   g_number_dim:=0;
321   g_number_fact:=0;
322   g_parallel:=fnd_profile.value('EDW_PARALLEL');
323   write_to_log_file_n('g_parallel='||g_parallel);
324   EDW_OWB_COLLECTION_UTIL.set_parallel(g_parallel);
325 Exception when others then
326   g_status:=false;
327   g_status_message:='Error in init_all '||sqlerrm;
328   write_to_log_file_n(g_status_message);
329 End;
330 
331 function get_time return varchar2 is
332 begin
333  return ' '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS');
334 Exception when others then
335   write_to_log_file_n('Exception in  get_time '||sqlerrm);
336   return null;
337 End;
338 
339 procedure write_to_log_file(p_message varchar2) is
340 begin
341   EDW_OWB_COLLECTION_UTIL.write_to_log_file(p_message);
342 Exception when others then
343   null;
344 End;
345 
346 procedure write_to_log_file_n(p_message varchar2) is
347 begin
348   write_to_log_file('   ');
349   write_to_log_file(p_message);
350 Exception when others then
351   null;
352 End;
353 
354 procedure write_to_out_file(p_message varchar2) is
355 begin
356   EDW_OWB_COLLECTION_UTIL.write_to_out_file(p_message);
357 Exception when others then
358   null;
359 End;
360 
361 procedure write_to_out_file_n(p_message varchar2) is
362 begin
363   write_to_out_file('  ');
364   write_to_out_file(p_message);
365 Exception when others then
366   null;
367 End;
368 
369 END EDW_ANALYZE_OBJECT;