PACKAGE: APPS.BSC_IM_UTILS

Source

1 package BSC_IM_UTILS AS
2 /*$Header: BSCOLUTS.pls 120.1 2006/02/16 15:18:03 arsantha noship $*/
3 
4 Type varchar_tabletype is Table of varchar2(32000) index by binary_integer;
5 Type number_tabletype is Table of number index by binary_integer;
6 Type boolean_tabletype is Table of boolean index by binary_integer;
7 Type date_tabletype is Table of date index by binary_integer;
8 g_debug boolean;
9 G_CLOB clob;
10 g_stmt varchar2(32000);
11 g_in_stmt varchar2(29000);
12 g_number_global_dimension number;
13 g_global_dimension varchar_tabletype;
14 g_id number;
15 g_aw_installed boolean;
16 g_status_message varchar2(10000);
17 g_db_version varchar2(40);
18 g_apps_owner varchar2(200);
19 --functions--------------------------------------------------------
20 procedure open_file(p_object_name varchar2);
21 procedure write_to_log(p_message varchar2,p_new_line boolean);
22 procedure write_to_log_file_s(p_message varchar2);
23 procedure write_to_log_file(p_message varchar2);
24 procedure write_to_log_file_n(p_message varchar2);
25 procedure write_to_debug_n(p_message varchar2);
26 procedure write_to_debug(p_message varchar2);
27 procedure write_to_out_file(p_message varchar2);
28 procedure write_to_out_file_s(p_message varchar2);
29 procedure write_to_out_file_n(p_message varchar2);
30 
31 -- Start of apis added by arun for bug 3876730
32 FUNCTION getParsedIndicNumber(p_Stable IN VARCHAR2) RETURN VARCHAR2;
33 function needs_zero_code_mv(p_mv_name varchar2,p_kpi varchar2,p_fk varchar2) return boolean ;
34 function needs_zero_code_b_pt(p_b_pt_table_name varchar2,p_fk varchar2) return boolean ;
35 -- End of apis added by arun for bug 3876730
36 
37 FUNCTION IsNumber (str IN VARCHAR2) RETURN BOOLEAN ;
38 function get_time return varchar2;
39 function is_column_in_object(p_object varchar2,p_column varchar2) return boolean;
40 function in_array(p_table varchar_tabletype,p_number_table number,p_value varchar2) return boolean;
41 function in_array(p_table number_tabletype,p_number_table number,p_value number) return boolean;
42 function in_array(p_table number_tabletype, p_table2 varchar_tabletype,
43 p_number_table number,p_value number,p_value2 varchar2) return boolean;
44 function in_array(p_table number_tabletype, p_table2 number_tabletype,
45 p_number_table number,p_value number,p_value2 number) return boolean;
46 function in_array(p_table varchar_tabletype, p_table2 varchar_tabletype,
47 p_number_table number,p_value varchar2,p_value2 varchar2) return boolean;
48 function in_array(p_table1 number_tabletype, p_table2 number_tabletype,
49 p_table3 number_tabletype,p_number_table number,p_value1 number,
50 p_value2 number,p_value3 number) return boolean;
51 function add_distinct_values_to_table(
52 p_table in out nocopy varchar_tabletype,
53 p_number_table in out nocopy number,
54 p_values_table varchar_tabletype,
55 p_number_values_table number,
56 p_options varchar2) return boolean;
57 function add_distinct_values_to_table(
58 p_table in out nocopy varchar_tabletype,
59 p_number_table in out nocopy number,
60 p_value varchar2,
61 p_options varchar2) return boolean;
62 procedure set_globals(p_debug boolean);
63 function get_db_user(
64 p_product varchar2,
65 p_db_user out nocopy varchar2
66 )return boolean;
67 function read_global return number;
68 function read_sequence(p_seq varchar) return number;
69 function sort_number_array(
70 p_list number_tabletype,
71 p_number_list number,
72 p_direction varchar2,
73 p_sorted_list out nocopy number_tabletype) return boolean;
74 function get_index(p_table varchar_tabletype,p_number_table number,p_value varchar2) return number;
75 function get_index(p_table number_tabletype,p_number_table number,p_value number) return number;
76 function get_index(
77 p_table_1 varchar_tabletype,
78 p_table_2 number_tabletype,
79 p_number_table number,
80 p_value_1 varchar2,
81 p_value_2 number
82 ) return number;
83 function get_index(
84 p_table_1 varchar_tabletype,
85 p_table_2 varchar_tabletype,
86 p_number_table number,
87 p_value_1 varchar2,
88 p_value_2 varchar2
89 ) return number;
90 function get_rank(
91 p_parent_array varchar_tabletype,
92 p_child_array varchar_tabletype,
93 p_number_array number,
94 p_rep_array out nocopy varchar_tabletype,
95 p_rep_rank out nocopy number_tabletype,
96 p_number_rep_array out nocopy number,
97 p_max_rank out nocopy number
98 ) return boolean;
99 function set_rank(
100 p_parent_array varchar_tabletype,
101 p_child_array varchar_tabletype,
102 p_number_array number,
103 p_child_level varchar2,
104 p_rank number,
105 p_rep_array in out nocopy varchar_tabletype,
106 p_rep_rank in out nocopy number_tabletype,
107 p_number_rep_array in out nocopy number
108 ) return boolean;
109 function get_distinct_list(
110 p_input varchar_tabletype,
111 p_number_input number,
112 p_dist_list out nocopy varchar_tabletype,
113 p_number_dist_list out nocopy number
114 ) return boolean;
115 function get_distinct_list(
116 p_input number_tabletype,
117 p_number_input number,
118 p_dist_list out nocopy number_tabletype,
119 p_number_dist_list out nocopy number
120 ) return boolean;
121 function parse_values(
122 p_list varchar2,
123 p_separator varchar2,
124 p_names out nocopy varchar_tabletype,
125 p_number_names out nocopy number) return boolean;
126 function parse_values(
127 p_list varchar2,
128 p_separator varchar2,
129 p_names out nocopy number_tabletype,
130 p_number_names out nocopy number) return boolean;
131 function parse_and_find(
132 p_list varchar2,
133 p_separator varchar2,
134 p_string  varchar2
135 )return boolean;
136 function get_value(
137 p_list varchar_tabletype,
138 p_list_values varchar_tabletype,
139 p_number_list number,
140 p_list_name varchar2
141 )return varchar2;
142 function get_seq_nextval(p_seq varchar2) return number;
143 function drop_db_object(p_object varchar2,p_type varchar2,p_owner varchar2) return boolean;
144 function set_global_dimensions return boolean;
145 function get_global_dimensions(
146 p_global_dimensions out nocopy varchar_tabletype,
147 p_number_global_dimensions out nocopy number
148 ) return boolean;
149 function is_global_dimension(
150 p_column varchar2
151 )return boolean;
152 function check_package(p_package varchar2) return boolean;
153 function get_table_owner(p_table varchar2) return varchar2;
154 function get_object_owner(p_object varchar2) return varchar2;
155 function get_table_constraints(
156 p_table_name varchar2,
157 p_table_owner varchar2,
158 p_constraint_name out nocopy varchar_tabletype,
159 p_constraint_type out nocopy varchar_tabletype,
160 p_status out nocopy varchar_tabletype,
161 p_validated out nocopy varchar_tabletype,
162 p_index_name out nocopy varchar_tabletype,
163 p_number_constraints out nocopy number
164 )return boolean;
165 function create_mv_log_on_table(
166 p_table_name varchar2,
167 p_table_owner varchar2,
168 p_options varchar_tabletype,
169 p_number_options number,
170 p_uk_columns varchar_tabletype,
171 p_numbet_uk_columns number,
172 p_columns varchar_tabletype,
173 p_number_columns number,
174 p_snplog_creates out nocopy boolean
175 )return boolean;
176 function check_snapshot_log(
177 p_table_name varchar2,
178 p_table_owner varchar2
179 )return boolean;
180 function get_mv_owner(p_mv_name varchar2) return varchar2;
181 function get_mv_properties(
182 p_mv_name varchar2,
183 p_mv_owner in out nocopy varchar2,
184 p_refresh_mode out nocopy varchar2,
185 p_refresh_method out nocopy varchar2,
186 p_build_mode out nocopy varchar2,
187 p_last_refresh_type out nocopy varchar2,
188 p_last_refresh_date out nocopy date,
189 p_staleness out nocopy varchar2
190 )return boolean;
191 function drop_materialized_view(p_mview varchar2,p_owner varchar2) return boolean;
192 function check_mv(
193 p_mv_name varchar2,
194 p_mv_owner varchar2
195 )return boolean;
196 function drop_table(p_table varchar2,p_owner varchar2) return boolean;
197 function get_table_properties(
198 p_table varchar2,
199 p_owner varchar2,
200 p_columns out nocopy varchar_tabletype,
201 p_columns_data_type out nocopy varchar_tabletype,
202 p_number_columns out nocopy number
203 )return boolean;
204 function drop_synonym(p_syn_name varchar2) return boolean;
205 function get_ordered_levels(
206 p_dim_name varchar2,
207 p_apps_origin varchar2,
208 p_levels varchar_tabletype,
209 p_number_levels number,
210 p_ordered_levels out nocopy varchar_tabletype,
211 p_number_children out nocopy number_tabletype
212 )return boolean;
213 function get_ordered_levels(
214 p_levels varchar_tabletype,
215 p_level_number_children number_tabletype,
216 p_number_levels number,
217 p_ordered_levels out nocopy varchar_tabletype) return boolean;
218 function get_all_levels_between(
219 p_dim_name varchar2,
220 p_apps_origin varchar2,
221 p_level_1 varchar2,
222 p_level_2 varchar2,
223 p_child_level out nocopy varchar_tabletype,
224 p_child_level_fk out nocopy varchar_tabletype,
225 p_parent_level out nocopy varchar_tabletype,
226 p_parent_level_pk out nocopy varchar_tabletype,
227 p_hier out nocopy varchar_tabletype,
228 p_number_hier out nocopy number
229 )return boolean;
230 function get_all_levels_between(
231 p_level_1 varchar2,
232 p_level_2 varchar2,
233 p_hier varchar2,
234 p_child_level varchar_tabletype,
235 p_parent_level varchar_tabletype,
236 p_child_fk varchar_tabletype,
237 p_parent_pk varchar_tabletype,
238 p_hier_rel varchar_tabletype,
239 p_number_rels number,
240 po_child_level out nocopy varchar_tabletype,
241 po_child_level_fk out nocopy varchar_tabletype,
242 po_parent_level out nocopy varchar_tabletype,
243 po_parent_level_pk out nocopy varchar_tabletype,
244 po_hier out nocopy varchar_tabletype,
245 po_number_hier out nocopy number
246 )return boolean;
247 function get_option_value(
248 p_options varchar_tabletype,
249 p_number_options number,
250 p_check_option varchar2
251 )return varchar2;
252 function get_option_value(
253 p_options varchar2,
254 p_separator varchar2,
255 p_check_option varchar2
256 )return varchar2;
257 function drop_mv_log(
258 p_table_name varchar2,
259 p_table_owner varchar2
260 )return boolean;
261 function drop_constraint(
262 p_table_name varchar2,
263 p_table_owner varchar2,
264 p_constraint varchar2
265 )return boolean;
266 function get_table_storage(
267 p_table varchar2,
268 p_owner varchar2,
269 p_table_space out nocopy varchar2,
270 p_initial_extent out nocopy number,
271 p_next_extent out nocopy number,
272 p_pct_free out nocopy number,
273 p_pct_used out nocopy number,
274 p_pct_increase out nocopy number,
275 p_max_extents out nocopy number,
276 p_avg_row_len out nocopy number
277 ) return boolean;
278 function drop_mv(
279 p_mv varchar2,
280 p_mv_owner varchar2
281 )return boolean ;
282 function get_object_type(
283 p_object varchar2,
284 p_owner varchar2
285 ) return varchar2;
286 function get_table_indexes(
287 p_table_name varchar2,
288 p_table_owner varchar2,
289 p_index out nocopy varchar_tabletype,
290 p_uniqueness out nocopy varchar_tabletype,
291 p_tablespace out nocopy varchar_tabletype,
292 p_initial_extent out nocopy number_tabletype,
293 p_next_extent out nocopy number_tabletype,
294 p_max_extents out nocopy number_tabletype,
295 p_pct_increase  out nocopy number_tabletype,
296 p_number_index out nocopy number
297 ) return boolean ;
298 function get_table_indexes(
299 p_table_name varchar2,
300 p_table_owner varchar2,
301 p_index out nocopy varchar_tabletype,
302 p_uniqueness out nocopy varchar_tabletype,
303 p_tablespace out nocopy varchar_tabletype,
304 p_initial_extent out nocopy number_tabletype,
305 p_next_extent out nocopy number_tabletype,
306 p_max_extents out nocopy number_tabletype,
307 p_pct_increase  out nocopy number_tabletype,
308 p_number_index out nocopy number,
309 p_ind_name out nocopy varchar_tabletype,
310 p_ind_col out nocopy varchar_tabletype,
311 p_number_ind_col out nocopy number
312 ) return boolean;
313 function get_synonym_property(
314 p_synonym varchar2,
315 p_syn_owner out nocopy varchar2,
316 p_syn_object out nocopy varchar2
317 )return boolean;
318 function create_synonym(
319 p_synonym varchar2,
320 p_syn_owner varchar2,
321 p_syn_object varchar2
322 )return boolean;
323 procedure set_trace;
324 procedure analyze_object(
325 p_object varchar2,
326 p_owner varchar2,
327 p_sample number,
328 p_parallel number,
329 p_partname varchar2
330 );
331 function get_object_name(p_object_name varchar2) return varchar2 ;
332 function get_corrected_map_table(
333 p_map_table varchar2,--could be sql stmt
334 p_map_table_list varchar2,
335 p_options varchar_tabletype,
336 p_number_options number,
337 p_apps_src varchar2,
338 p_olap_target varchar2,
339 p_corr_table_name out nocopy varchar2,
340 p_corr_table_list out nocopy varchar_tabletype,
341 p_original_table_list out nocopy varchar_tabletype,
342 p_number_corr_table out nocopy number
343 )return boolean;
344 function get_corrected_map_table_bsc_mv(
345 p_map_table varchar2,--could be sql stmt
346 p_map_table_list varchar2,
347 p_options varchar_tabletype,
348 p_number_options number,
349 p_corr_table_name out nocopy varchar2,
350 p_corr_table_list out nocopy varchar_tabletype,
351 p_original_table_list out nocopy varchar_tabletype,
352 p_number_corr_table out nocopy number
353 )return boolean;
354 function get_db_version return varchar2;
355 function drop_object(
356 p_object varchar2,
357 p_owner varchar2
358 ) return boolean;
359 function is_mview(
360 p_mview varchar2,
361 p_owner varchar2
362 )return boolean ;
363 function does_table_have_data(p_table varchar2, p_where varchar2) return number;
364 function does_table_have_data(p_table varchar2, p_where varchar2,p_bind varchar2) return boolean;
365 function truncate_table(p_table varchar2, p_owner varchar2) return boolean;
366 function find_aggregation_columns(
367 p_formula varchar2,
368 p_columns out nocopy varchar_tabletype,
369 p_number_columns out nocopy number
370 )return boolean;
371 function refresh_mv(
372 p_mv_name varchar2,
373 p_mv_owner varchar2,
374 p_kpi varchar2,
375 p_options varchar_tabletype,
376 p_number_options number
377 )return boolean;
378 function drop_view(
379 p_view varchar2,
380 p_view_owner varchar2
381 )return boolean;
382 function check_view(
383 p_view_name varchar2,
384 p_view_owner varchar2
385 )return boolean;
386 function get_bsc_owner return varchar2;
387 function get_lang return varchar2;
388 function is_like(p_string varchar2,p_comp_string varchar2) return boolean;
389 function create_index(
390 p_table_name varchar2,
391 p_table_owner varchar2,
392 p_index varchar_tabletype,
393 p_uniqueness varchar_tabletype,
394 p_tablespace varchar_tabletype,
395 p_initial_extent number_tabletype,
396 p_next_extent number_tabletype,
397 p_max_extents number_tabletype,
398 p_pct_increase  number_tabletype,
399 p_number_index number,
400 ------
401 p_ind_name varchar_tabletype,
402 p_ind_col varchar_tabletype,
403 p_number_ind_col number
404 )return boolean;
405 function create_index(
406 p_stmt varchar2,
407 p_options varchar2
408 )return boolean;
409 function execute_immediate(
410 p_stmt varchar2,
411 p_options varchar2
412 ) return boolean;
413 function is_cube_present(
414 p_cube varchar2,
418 p_table_name varchar2,
415 p_apps_origin varchar2
416 )return boolean;
417 function get_snapshot_log(
419 p_table_owner varchar2,
420 p_snplog out nocopy varchar2
421 )return boolean;
422 function is_view_present(p_view_like varchar2) return boolean;
423 function get_parent_mv(
424 p_mv varchar2,
425 p_parent_mv out nocopy varchar_tabletype,
426 p_number_parent_mv out nocopy number
427 )return boolean ;
428 function get_child_mv(
429 p_mv varchar2,
430 p_child_mv out nocopy varchar_tabletype,
431 p_number_child_mv out nocopy number
432 )return boolean;
433 function is_parent_of_type_present(
434 p_object varchar2,
435 p_parent_type varchar2
436 )return boolean;
437 function get_apps_owner return varchar2;
438 END BSC_IM_UTILS;