1 package body BSC_IM_INT_MD AS
2 /*$Header: BSCIMDUB.pls 115.0 2003/12/09 00:50:15 vsurendr ship $*/
3
4 --=================PUBLIC CREATE API============================================
5 function create_dimension(
6 p_dim_name varchar2,
7 p_apps_origin varchar2,
8 p_description varchar2,
9 p_property varchar2
10 )return boolean is
11 l_found boolean;
12 l_index number;
13 Begin
14 if number_im_md_dimensions is null then
15 number_im_md_dimensions:=0;
16 im_md_dimensions:=im_md_dimensions_t(im_md_dimensions_i);
17 number_im_md_dimensions:=number_im_md_dimensions+1;
18 l_index:=number_im_md_dimensions;
19 elsif number_im_md_dimensions>0 then
20 l_found:=false;
21 for i in 1..number_im_md_dimensions loop
22 if im_md_dimensions(i).dim_name=p_dim_name and im_md_dimensions(i).apps_origin=p_apps_origin then
23 l_found:=true;
24 l_index:=i;
25 exit;
26 end if;
27 end loop;
28 if l_found=false then
29 im_md_dimensions.extend;
30 number_im_md_dimensions:=number_im_md_dimensions+1;
31 l_index:=number_im_md_dimensions;
32 end if;
33 else
34 im_md_dimensions.extend;
35 number_im_md_dimensions:=number_im_md_dimensions+1;
36 l_index:=number_im_md_dimensions;
37 end if;
38 im_md_dimensions(l_index).dim_name:=p_dim_name;
39 im_md_dimensions(l_index).apps_origin:=p_apps_origin;
40 im_md_dimensions(l_index).description:=p_description;
41 im_md_dimensions(l_index).property:=p_property;
42 return true;
43 Exception when others then
44 BSC_IM_UTILS.g_status_message:=sqlerrm;
45 g_status_message:=sqlerrm;
46 write_to_log_file_n('Error in create_dimension '||sqlerrm);
47 return false;
48 End;
49
50 function create_level(
51 p_level_name varchar2,
52 p_apps_origin varchar2,
53 p_dim_name varchar2,
54 p_number_children number,
55 p_description varchar2,
56 p_property varchar2
57 )return boolean is
58 l_found boolean;
59 l_index number;
60 Begin
61 if number_im_md_levels is null then
62 number_im_md_levels:=0;
63 im_md_levels:=im_md_levels_t(im_md_levels_i);
64 number_im_md_levels:=number_im_md_levels+1;
65 l_index:=number_im_md_levels;
66 elsif number_im_md_levels>0 then
67 l_found:=false;
68 for i in 1..number_im_md_levels loop
69 if im_md_levels(i).level_name=p_level_name and im_md_levels(i).dim_name=p_dim_name and
70 im_md_levels(i).apps_origin=p_apps_origin then
71 l_found:=true;
72 l_index:=i;
73 exit;
74 end if;
75 end loop;
76 if l_found=false then
77 im_md_levels.extend;
78 number_im_md_levels:=number_im_md_levels+1;
79 l_index:=number_im_md_levels;
80 end if;
81 else
82 im_md_levels.extend;
83 number_im_md_levels:=number_im_md_levels+1;
84 l_index:=number_im_md_levels;
85 end if;
86 im_md_levels(l_index).level_name:=p_level_name;
87 im_md_levels(l_index).apps_origin:=p_apps_origin;
88 im_md_levels(l_index).dim_name:=p_dim_name;
89 im_md_levels(l_index).number_children:=p_number_children;
90 im_md_levels(l_index).description:=p_description;
91 im_md_levels(l_index).property:=p_property;
92 return true;
93 Exception when others then
94 BSC_IM_UTILS.g_status_message:=sqlerrm;
95 g_status_message:=sqlerrm;
96 write_to_log_file_n('Error in create_level '||sqlerrm);
97 return false;
98 End;
99
100 function create_column(
101 p_column_name varchar2,
102 p_column_type varchar2,
103 p_column_data_type varchar2,
104 p_apps_origin varchar2,
105 p_column_origin varchar2,
106 p_aggregation_type varchar2,
107 p_description varchar2,
108 p_parent_name varchar2,
109 p_property varchar2
110 )return boolean is
111 l_found boolean;
112 l_index number;
113 Begin
114 if number_im_md_columns is null then
115 number_im_md_columns:=0;
116 im_md_columns:=im_md_columns_t(im_md_columns_i);
117 number_im_md_columns:=number_im_md_columns+1;
118 l_index:=number_im_md_columns;
119 elsif number_im_md_columns>0 then
120 l_found:=false;
121 for i in 1..number_im_md_columns loop
122 if im_md_columns(i).column_name=p_column_name and im_md_columns(i).apps_origin=p_apps_origin and
123 im_md_columns(i).parent_name=p_parent_name then
124 l_found:=true;
125 l_index:=i;
126 exit;
127 end if;
128 end loop;
129 if l_found=false then
130 im_md_columns.extend;
131 number_im_md_columns:=number_im_md_columns+1;
132 l_index:=number_im_md_columns;
133 end if;
134 else
135 im_md_columns.extend;
136 number_im_md_columns:=number_im_md_columns+1;
137 l_index:=number_im_md_columns;
138 end if;
139 im_md_columns(l_index).column_name:=p_column_name;
140 im_md_columns(l_index).column_type:=p_column_type;
141 im_md_columns(l_index).column_data_type:=p_column_data_type;
142 im_md_columns(l_index).apps_origin:=p_apps_origin;
143 im_md_columns(l_index).column_origin:=p_column_origin;
144 im_md_columns(l_index).aggregation_type:=p_aggregation_type;
145 im_md_columns(l_index).description:=p_description;
146 im_md_columns(l_index).parent_name:=p_parent_name;
147 im_md_columns(l_index).property:=p_property;
148 return true;
149 Exception when others then
150 BSC_IM_UTILS.g_status_message:=sqlerrm;
151 g_status_message:=sqlerrm;
152 write_to_log_file_n('Error in create_column '||sqlerrm);
153 return false;
154 End;
155
156 function create_level_relation(
157 p_child_level varchar2,
158 p_parent_level varchar2,
159 p_child_fk varchar2,
160 p_parent_pk varchar2,
161 p_hier_name varchar2,
162 p_dim_name varchar2,
163 p_apps_origin varchar2,
164 p_property varchar2
165 )return boolean is
166 l_found boolean;
167 l_index number;
168 Begin
169 if number_im_md_level_relations is null then
170 number_im_md_level_relations:=0;
171 im_md_level_relations:=im_md_level_relations_t(im_md_level_relations_i);
172 number_im_md_level_relations:=number_im_md_level_relations+1;
173 l_index:=number_im_md_level_relations;
174 elsif number_im_md_level_relations>0 then
175 l_found:=false;
176 for i in 1..number_im_md_level_relations loop
177 if im_md_level_relations(i).child_level=p_child_level and im_md_level_relations(i).parent_level=p_parent_level
178 and im_md_level_relations(i).hier_name=p_hier_name and im_md_level_relations(i).dim_name=p_dim_name
179 and im_md_level_relations(i).apps_origin=p_apps_origin then
180 l_found:=true;
181 l_index:=i;
182 exit;
183 end if;
184 end loop;
185 if l_found=false then
186 im_md_level_relations.extend;
187 number_im_md_level_relations:=number_im_md_level_relations+1;
188 l_index:=number_im_md_level_relations;
189 end if;
190 else
191 im_md_level_relations.extend;
192 number_im_md_level_relations:=number_im_md_level_relations+1;
193 l_index:=number_im_md_level_relations;
194 end if;
195 im_md_level_relations(l_index).child_level:=p_child_level;
196 im_md_level_relations(l_index).parent_level:=p_parent_level;
197 im_md_level_relations(l_index).child_fk:=p_child_fk;
198 im_md_level_relations(l_index).parent_pk:=p_parent_pk;
199 im_md_level_relations(l_index).hier_name:=p_hier_name;
200 im_md_level_relations(l_index).dim_name:=p_dim_name;
201 im_md_level_relations(l_index).apps_origin:=p_apps_origin;
202 im_md_level_relations(l_index).property:=p_property;
203 return true;
204 Exception when others then
205 BSC_IM_UTILS.g_status_message:=sqlerrm;
206 g_status_message:=sqlerrm;
207 write_to_log_file_n('Error in create_level_relation '||sqlerrm);
208 return false;
209 End;
210
211 function create_hierarchy(
212 p_hier_name varchar2,
213 p_dim_name varchar2,
214 p_apps_origin varchar2,
215 p_description varchar2,
216 p_property varchar2
217 )return boolean is
218 l_found boolean;
219 l_index number;
220 Begin
221 if number_im_md_hierarchies is null then
222 number_im_md_hierarchies:=0;
223 im_md_hierarchies:=im_md_hierarchies_t(im_md_hierarchies_i);
224 number_im_md_hierarchies:=number_im_md_hierarchies+1;
225 l_index:=number_im_md_hierarchies;
226 elsif number_im_md_hierarchies>0 then
227 l_found:=false;
228 for i in 1..number_im_md_hierarchies loop
229 if im_md_hierarchies(i).hier_name=p_hier_name and im_md_hierarchies(i).dim_name=p_dim_name
230 and im_md_hierarchies(i).apps_origin=p_apps_origin then
231 l_found:=true;
232 l_index:=i;
233 exit;
234 end if;
235 end loop;
236 if l_found=false then
237 im_md_hierarchies.extend;
238 number_im_md_hierarchies:=number_im_md_hierarchies+1;
239 l_index:=number_im_md_hierarchies;
240 end if;
241 else
242 im_md_hierarchies.extend;
243 number_im_md_hierarchies:=number_im_md_hierarchies+1;
244 l_index:=number_im_md_hierarchies;
245 end if;
246 im_md_hierarchies(l_index).hier_name:=p_hier_name;
247 im_md_hierarchies(l_index).dim_name:=p_dim_name;
248 im_md_hierarchies(l_index).apps_origin:=p_apps_origin;
249 im_md_hierarchies(l_index).description:=p_description;
250 im_md_hierarchies(l_index).property:=p_property;
251 return true;
252 Exception when others then
253 BSC_IM_UTILS.g_status_message:=sqlerrm;
254 g_status_message:=sqlerrm;
255 write_to_log_file_n('Error in create_hierarchy '||sqlerrm);
256 return false;
257 End;
258
259 function create_mapping(
260 p_map_name varchar2,
261 p_apps_origin varchar2,
262 p_map_type varchar2,
263 p_object_name varchar2,
264 p_property varchar2
265 )return boolean is
266 l_found boolean;
267 l_index number;
268 Begin
269 if number_im_md_mapping is null then
270 number_im_md_mapping:=0;
271 im_md_mapping:=im_md_mapping_t(im_md_mapping_i);
272 number_im_md_mapping:=number_im_md_mapping+1;
273 l_index:=number_im_md_mapping;
274 elsif number_im_md_mapping>0 then
275 l_found:=false;
276 for i in 1..number_im_md_mapping loop
277 if im_md_mapping(i).map_name=p_map_name and im_md_mapping(i).apps_origin=p_apps_origin then
278 l_found:=true;
279 l_index:=i;
280 exit;
281 end if;
282 end loop;
283 if l_found=false then
284 im_md_mapping.extend;
285 number_im_md_mapping:=number_im_md_mapping+1;
286 l_index:=number_im_md_mapping;
287 end if;
288 else
289 im_md_mapping.extend;
293 im_md_mapping(l_index).map_name:=p_map_name;
290 number_im_md_mapping:=number_im_md_mapping+1;
291 l_index:=number_im_md_mapping;
292 end if;
294 im_md_mapping(l_index).apps_origin:=p_apps_origin;
295 im_md_mapping(l_index).map_type:=p_map_type;
296 im_md_mapping(l_index).object_name:=p_object_name;
297 im_md_mapping(l_index).property:=p_property;
298 return true;
299 Exception when others then
300 BSC_IM_UTILS.g_status_message:=sqlerrm;
301 g_status_message:=sqlerrm;
302 write_to_log_file_n('Error in create_mapping '||sqlerrm);
303 return false;
304 End;
305
306 function create_mapping_detail(
307 p_map_name varchar2,
308 p_apps_origin varchar2,
309 p_line varchar2,
310 p_line_type varchar2,
311 p_property varchar2
312 )return boolean is
313 l_found boolean;
314 l_index number;
315 Begin
316 if number_im_md_mapping_details is null then
317 number_im_md_mapping_details:=0;
318 im_md_mapping_details:=im_md_mapping_details_t(im_md_mapping_details_i);
319 number_im_md_mapping_details:=number_im_md_mapping_details+1;
320 l_index:=number_im_md_mapping_details;
321 elsif number_im_md_mapping_details>0 then
322 l_found:=false;
323 for i in 1..number_im_md_mapping_details loop
324 if im_md_mapping_details(i).map_name=p_map_name and im_md_mapping_details(i).apps_origin=p_apps_origin and
325 im_md_mapping_details(i).line=p_line and im_md_mapping_details(i).line_type=p_line_type and
326 im_md_mapping_details(i).property=p_property then
327 l_found:=true;
328 l_index:=i;
329 exit;
330 end if;
331 end loop;
332 if l_found=false then
333 im_md_mapping_details.extend;
334 number_im_md_mapping_details:=number_im_md_mapping_details+1;
335 l_index:=number_im_md_mapping_details;
336 end if;
337 else
338 im_md_mapping_details.extend;
339 number_im_md_mapping_details:=number_im_md_mapping_details+1;
340 l_index:=number_im_md_mapping_details;
341 end if;
342 im_md_mapping_details(l_index).map_name:=p_map_name;
343 im_md_mapping_details(l_index).apps_origin:=p_apps_origin;
344 im_md_mapping_details(l_index).line:=p_line;
345 im_md_mapping_details(l_index).line_type:=p_line_type;
346 im_md_mapping_details(l_index).property:=p_property;
347 return true;
348 Exception when others then
349 BSC_IM_UTILS.g_status_message:=sqlerrm;
350 g_status_message:=sqlerrm;
351 write_to_log_file_n('Error in create_mapping_detail '||sqlerrm);
352 return false;
353 End;
354
355 function create_cube(
356 p_cube_name varchar2,
357 p_cube_id number,
358 p_cube_periodicity varchar2,
359 p_apps_origin varchar2,
360 p_description varchar2,
361 p_property varchar2
362 )return boolean is
363 l_found boolean;
364 l_index number;
365 Begin
366 if number_im_md_cube is null then
367 number_im_md_cube:=0;
368 im_md_cube:=im_md_cube_t(im_md_cube_i);
369 number_im_md_cube:=number_im_md_cube+1;
370 l_index:=number_im_md_cube;
371 elsif number_im_md_cube>0 then
372 l_found:=false;
373 for i in 1..number_im_md_cube loop
374 if im_md_cube(i).cube_name=p_cube_name and im_md_cube(i).apps_origin=p_apps_origin then
375 l_found:=true;
376 l_index:=i;
377 exit;
378 end if;
379 end loop;
380 if l_found=false then
381 im_md_cube.extend;
382 number_im_md_cube:=number_im_md_cube+1;
383 l_index:=number_im_md_cube;
384 end if;
385 else
386 im_md_cube.extend;
387 number_im_md_cube:=number_im_md_cube+1;
388 l_index:=number_im_md_cube;
389 end if;
390 im_md_cube(l_index).cube_name:=p_cube_name;
391 im_md_cube(l_index).cube_id:=p_cube_id;
392 im_md_cube(l_index).cube_periodicity:=p_cube_periodicity;
393 im_md_cube(l_index).apps_origin:=p_apps_origin;
394 im_md_cube(l_index).description:=p_description;
395 im_md_cube(l_index).property:=p_property;
396 return true;
397 Exception when others then
398 BSC_IM_UTILS.g_status_message:=sqlerrm;
399 g_status_message:=sqlerrm;
400 write_to_log_file_n('Error in create_cube '||sqlerrm);
401 return false;
402 End;
403
404 function create_fk(
405 p_fk_name varchar2,
406 p_fk_type varchar2,
407 p_owner_name varchar2,
408 p_uk_name varchar2,
409 p_uk_parent_name varchar2,
410 p_description varchar2,
411 p_apps_origin varchar2,
412 p_property varchar2
413 )return boolean is
414 l_found boolean;
415 l_index number;
416 Begin
417 if number_im_md_fk is null then
418 number_im_md_fk:=0;
419 im_md_fk:=im_md_fk_t(im_md_fk_i);
420 number_im_md_fk:=number_im_md_fk+1;
421 l_index:=number_im_md_fk;
422 elsif number_im_md_fk>0 then
423 l_found:=false;
424 for i in 1..number_im_md_fk loop
425 if im_md_fk(i).fk_name=p_fk_name and im_md_fk(i).owner_name=p_owner_name
426 and im_md_fk(i).apps_origin=p_apps_origin then
427 l_found:=true;
428 l_index:=i;
429 exit;
430 end if;
431 end loop;
432 if l_found=false then
433 im_md_fk.extend;
434 number_im_md_fk:=number_im_md_fk+1;
435 l_index:=number_im_md_fk;
436 end if;
437 else
438 im_md_fk.extend;
439 number_im_md_fk:=number_im_md_fk+1;
440 l_index:=number_im_md_fk;
441 end if;
442 im_md_fk(l_index).fk_name:=p_fk_name;
446 im_md_fk(l_index).owner_name:=p_owner_name;
443 im_md_fk(l_index).fk_type:=p_fk_type;
444 im_md_fk(l_index).uk_name:=p_uk_name;
445 im_md_fk(l_index).description:=p_description;
447 im_md_fk(l_index).uk_parent_name:=p_uk_parent_name;
448 im_md_fk(l_index).apps_origin:=p_apps_origin;
449 im_md_fk(l_index).property:=p_property;
450 return true;
451 Exception when others then
452 BSC_IM_UTILS.g_status_message:=sqlerrm;
453 g_status_message:=sqlerrm;
454 write_to_log_file_n('Error in create_fk '||sqlerrm);
455 return false;
456 End;
457
458 function create_uk(
459 p_uk_name varchar2,
460 p_uk_type varchar2,
461 p_description varchar2,
462 p_owner_name varchar2,
463 p_apps_origin varchar2,
464 p_property varchar2
465 )return boolean is
466 l_found boolean;
467 l_index number;
468 Begin
469 if number_im_md_uk is null then
470 number_im_md_uk:=0;
471 im_md_uk:=im_md_uk_t(im_md_uk_i);
472 number_im_md_uk:=number_im_md_uk+1;
473 l_index:=number_im_md_uk;
474 elsif number_im_md_uk>0 then
475 l_found:=false;
476 for i in 1..number_im_md_uk loop
477 if im_md_uk(i).uk_name=p_uk_name and im_md_uk(i).owner_name=p_owner_name
478 and im_md_uk(i).apps_origin=p_apps_origin then
479 l_found:=true;
480 l_index:=i;
481 exit;
482 end if;
483 end loop;
484 if l_found=false then
485 im_md_uk.extend;
486 number_im_md_uk:=number_im_md_uk+1;
487 l_index:=number_im_md_uk;
488 end if;
489 else
490 im_md_uk.extend;
491 number_im_md_uk:=number_im_md_uk+1;
492 l_index:=number_im_md_uk;
493 end if;
494 im_md_uk(l_index).uk_name:=p_uk_name;
495 im_md_uk(l_index).uk_type:=p_uk_type;
496 im_md_uk(l_index).description:=p_description;
497 im_md_uk(l_index).owner_name:=p_owner_name;
498 im_md_uk(l_index).apps_origin:=p_apps_origin;
499 im_md_uk(l_index).property:=p_property;
500 return true;
501 Exception when others then
502 BSC_IM_UTILS.g_status_message:=sqlerrm;
503 g_status_message:=sqlerrm;
504 write_to_log_file_n('Error in create_uk '||sqlerrm);
505 return false;
506 End;
507
508 function create_object(
509 p_object_name varchar2,
510 p_object_type varchar2,
511 p_apps_origin varchar2,
512 p_parent_name varchar2,
513 p_description varchar2,
514 p_property varchar2
515 )return boolean is
516 l_found boolean;
517 l_index number;
518 Begin
519 if number_im_md_object is null then
520 number_im_md_object:=0;
521 im_md_object:=im_md_object_t(im_md_object_i);
522 number_im_md_object:=number_im_md_object+1;
523 l_index:=number_im_md_object;
524 elsif number_im_md_object>0 then
525 l_found:=false;
526 for i in 1..number_im_md_object loop
527 if im_md_object(i).object_name=p_object_name and im_md_object(i).apps_origin=p_apps_origin and
528 im_md_object(i).object_type=p_object_type and im_md_object(i).property=p_property and
529 im_md_object(i).parent_name=p_parent_name then
530 l_found:=true;
531 l_index:=i;
532 exit;
533 end if;
534 end loop;
535 if l_found=false then
536 im_md_object.extend;
537 number_im_md_object:=number_im_md_object+1;
538 l_index:=number_im_md_object;
539 end if;
540 else
541 im_md_object.extend;
542 number_im_md_object:=number_im_md_object+1;
543 l_index:=number_im_md_object;
544 end if;
545 im_md_object(l_index).object_name:=p_object_name;
546 im_md_object(l_index).object_type:=p_object_type;
547 im_md_object(l_index).apps_origin:=p_apps_origin;
548 im_md_object(l_index).parent_name:=p_parent_name;
549 im_md_object(l_index).description:=p_description;
550 im_md_object(l_index).property:=p_property;
551 return true;
552 Exception when others then
553 BSC_IM_UTILS.g_status_message:=sqlerrm;
554 g_status_message:=sqlerrm;
555 write_to_log_file_n('Error in create_object '||sqlerrm);
556 return false;
557 End;
558
559 --==============================================================================
560
561 --============================PUBLIC DELETE API=================================
562 procedure reset_int_metadata is
563 Begin
564 if number_im_md_dimensions>0 then
565 im_md_dimensions.delete;
566 end if;
567 number_im_md_dimensions:=null;
568 if number_im_md_levels>0 then
569 im_md_levels.delete;
570 end if;
571 number_im_md_levels:=null;
572 if number_im_md_columns>0 then
573 im_md_columns.delete;
574 end if;
575 number_im_md_columns:=null;
576 if number_im_md_level_relations>0 then
577 im_md_level_relations.delete;
578 end if;
579 number_im_md_level_relations:=null;
580 if number_im_md_hierarchies>0 then
581 im_md_hierarchies.delete;
582 end if;
583 number_im_md_hierarchies:=null;
584 if number_im_md_mapping>0 then
585 im_md_mapping.delete;
586 end if;
587 number_im_md_mapping:=null;
588 if number_im_md_mapping_details>0 then
589 im_md_mapping_details.delete;
590 end if;
591 number_im_md_mapping_details:=null;
592 if number_im_md_cube>0 then
593 im_md_cube.delete;
594 end if;
595 number_im_md_cube:=null;
596 if number_im_md_fk>0 then
597 im_md_fk.delete;
598 end if;
599 number_im_md_fk:=null;
600 if number_im_md_uk>0 then
601 im_md_uk.delete;
602 end if;
603 number_im_md_uk:=null;
604 if number_im_md_object>0 then
605 im_md_object.delete;
606 end if;
610 write_to_log_file_n('Error in reset_int_metadata '||sqlerrm);
607 number_im_md_object:=null;
608 Exception when others then
609 BSC_IM_UTILS.g_status_message:=sqlerrm;
611 raise;
612 End;
613 --==============================================================================
614
615 --============================PUBLIC GET API====================================
616 function get_dimension(
617 p_dim_name varchar2,
618 p_apps_origin varchar2,
619 p_description out nocopy varchar2,
620 p_property out nocopy varchar2
621 )return boolean is
622 Begin
623 if number_im_md_dimensions is null then
624 return true;
625 end if;
626 for i in 1..number_im_md_dimensions loop
627 if im_md_dimensions(i).dim_name=p_dim_name and im_md_dimensions(i).apps_origin=p_apps_origin then
628 p_description:=im_md_dimensions(i).description;
629 p_property:=im_md_dimensions(i).property;
630 exit;
631 end if;
632 end loop;
633 return true;
634 Exception when others then
635 BSC_IM_UTILS.g_status_message:=sqlerrm;
636 g_status_message:=sqlerrm;
637 write_to_log_file_n('Error in get_dimension '||sqlerrm);
638 return false;
639 End;
640
641 function get_level(
642 p_dim_name varchar2,
643 p_apps_origin varchar2,
644 p_level_name out nocopy BSC_IM_UTILS.varchar_tabletype,
645 p_number_children out nocopy BSC_IM_UTILS.number_tabletype,
646 p_description out nocopy BSC_IM_UTILS.varchar_tabletype,
647 p_property out nocopy BSC_IM_UTILS.varchar_tabletype,
648 p_number_levels out nocopy number
649 )return boolean is
650 Begin
651 p_number_levels:=0;
652 if number_im_md_levels is null then
653 return true;
654 end if;
655 for i in 1..number_im_md_levels loop
656 if im_md_levels(i).dim_name=p_dim_name and im_md_levels(i).apps_origin=p_apps_origin then
657 p_number_levels:=p_number_levels+1;
658 p_level_name(p_number_levels):=im_md_levels(i).level_name;
659 p_number_children(p_number_levels):=im_md_levels(i).number_children;
660 p_description(p_number_levels):=im_md_levels(i).description;
661 p_property(p_number_levels):=im_md_levels(i).property;
662 end if;
663 end loop;
664 return true;
665 Exception when others then
666 BSC_IM_UTILS.g_status_message:=sqlerrm;
667 g_status_message:=sqlerrm;
668 write_to_log_file_n('Error in get_level '||sqlerrm);
669 return false;
670 End;
671
672 function get_dim_for_level(
673 p_level varchar2,
674 p_apps_origin varchar2,
675 p_dim_name out nocopy varchar2) return boolean is
676 Begin
677 if number_im_md_levels is null then
678 return true;
679 end if;
680 for i in 1..number_im_md_levels loop
681 if im_md_levels(i).level_name=p_level and im_md_levels(i).apps_origin=p_apps_origin then
682 p_dim_name:=im_md_levels(i).dim_name;
683 exit;
684 end if;
685 end loop;
686 return true;
687 Exception when others then
688 BSC_IM_UTILS.g_status_message:=sqlerrm;
689 g_status_message:=sqlerrm;
690 write_to_log_file_n('Error in get_dim_for_level '||sqlerrm);
691 return false;
692 End;
693
694 function get_column(
695 p_parent_name varchar2,
696 p_apps_origin varchar2,
697 p_column_name out nocopy BSC_IM_UTILS.varchar_tabletype,
698 p_column_type out nocopy BSC_IM_UTILS.varchar_tabletype,
699 p_column_data_type out nocopy BSC_IM_UTILS.varchar_tabletype,
700 p_column_origin out nocopy BSC_IM_UTILS.varchar_tabletype,
701 p_aggregation_type out nocopy BSC_IM_UTILS.varchar_tabletype,
702 p_description out nocopy BSC_IM_UTILS.varchar_tabletype,
703 p_property out nocopy BSC_IM_UTILS.varchar_tabletype,
704 p_number_columns out nocopy number
705 )return boolean is
706 Begin
707 p_number_columns:=0;
708 if number_im_md_columns is null then
709 return true;
710 end if;
711 for i in 1..number_im_md_columns loop
712 if im_md_columns(i).parent_name=p_parent_name and im_md_columns(i).apps_origin=p_apps_origin then
713 p_number_columns:=p_number_columns+1;
714 p_column_name(p_number_columns):=im_md_columns(i).column_name;
715 p_column_type(p_number_columns):=im_md_columns(i).column_type;
716 p_column_data_type(p_number_columns):=im_md_columns(i).column_data_type;
717 p_column_origin(p_number_columns):=im_md_columns(i).column_origin;
718 p_aggregation_type(p_number_columns):=im_md_columns(i).aggregation_type;
719 p_description(p_number_columns):=im_md_columns(i).description;
720 p_property(p_number_columns):=im_md_columns(i).property;
721 end if;
722 end loop;
723 return true;
724 Exception when others then
725 BSC_IM_UTILS.g_status_message:=sqlerrm;
726 g_status_message:=sqlerrm;
727 write_to_log_file_n('Error in get_column '||sqlerrm);
728 return false;
729 End;
730
731 function get_level_relation(
732 p_dim_name varchar2,
733 p_apps_origin varchar2,
734 p_child_level out nocopy BSC_IM_UTILS.varchar_tabletype,
735 p_parent_level out nocopy BSC_IM_UTILS.varchar_tabletype,
736 p_child_fk out nocopy BSC_IM_UTILS.varchar_tabletype,
737 p_parent_pk out nocopy BSC_IM_UTILS.varchar_tabletype,
738 p_hier_name out nocopy BSC_IM_UTILS.varchar_tabletype,
739 p_property out nocopy BSC_IM_UTILS.varchar_tabletype,
740 p_number_level_relations out nocopy number
741 )return boolean is
742 Begin
743 p_number_level_relations:=0;
744 if number_im_md_level_relations is null then
745 return true;
746 end if;
747 for i in 1..number_im_md_level_relations loop
748 if im_md_level_relations(i).dim_name=p_dim_name and im_md_level_relations(i).apps_origin=p_apps_origin then
749 p_number_level_relations:=p_number_level_relations+1;
753 p_parent_pk(p_number_level_relations):=im_md_level_relations(i).parent_pk;
750 p_child_level(p_number_level_relations):=im_md_level_relations(i).child_level;
751 p_parent_level(p_number_level_relations):=im_md_level_relations(i).parent_level;
752 p_child_fk(p_number_level_relations):=im_md_level_relations(i).child_fk;
754 p_hier_name(p_number_level_relations):=im_md_level_relations(i).hier_name;
755 p_property(p_number_level_relations):=im_md_level_relations(i).property;
756 end if;
757 end loop;
758 return true;
759 Exception when others then
760 BSC_IM_UTILS.g_status_message:=sqlerrm;
761 g_status_message:=sqlerrm;
762 write_to_log_file_n('Error in get_level_relation '||sqlerrm);
763 return false;
764 End;
765
766 function get_hierarchy(
767 p_dim_name varchar2,
768 p_apps_origin varchar2,
769 p_hier_name out nocopy BSC_IM_UTILS.varchar_tabletype,
770 p_description out nocopy BSC_im_UTILS.varchar_tabletype,
771 p_property out nocopy BSC_IM_UTILS.varchar_tabletype,
772 p_number_hierarchy out nocopy number
773 )return boolean is
774 Begin
775 p_number_hierarchy:=0;
776 if number_im_md_hierarchies is null then
777 return true;
778 end if;
779 for i in 1..number_im_md_hierarchies loop
780 if im_md_hierarchies(i).dim_name=p_dim_name and im_md_hierarchies(i).apps_origin=p_apps_origin then
781 p_number_hierarchy:=p_number_hierarchy+1;
782 p_hier_name(p_number_hierarchy):=im_md_hierarchies(i).hier_name;
783 p_description(p_number_hierarchy):=im_md_hierarchies(i).description;
784 p_property(p_number_hierarchy):=im_md_hierarchies(i).property;
785 end if;
786 end loop;
787 return true;
788 Exception when others then
789 BSC_IM_UTILS.g_status_message:=sqlerrm;
790 g_status_message:=sqlerrm;
791 write_to_log_file_n('Error in get_hierarchy '||sqlerrm);
792 return false;
793 End;
794
795 function get_mapping(
796 p_owner_name varchar2,
797 p_apps_origin varchar2,
798 p_map_name out nocopy BSC_IM_UTILS.varchar_tabletype,
799 p_map_type out nocopy BSC_IM_UTILS.varchar_tabletype,
800 p_object_name out nocopy BSC_IM_UTILS.varchar_tabletype,
801 p_property out nocopy BSC_IM_UTILS.varchar_tabletype,
802 p_number_mapping out nocopy number
803 )return boolean is
804 Begin
805 p_number_mapping:=0;
806 if number_im_md_mapping is null then
807 return true;
808 end if;
809 for i in 1..number_im_md_mapping loop
810 if im_md_mapping(i).apps_origin=p_apps_origin and im_md_mapping(i).object_name=p_owner_name then
811 p_number_mapping:=p_number_mapping+1;
812 p_map_name(p_number_mapping):=im_md_mapping(i).map_name;
813 p_map_type(p_number_mapping):=im_md_mapping(i).map_type;
814 p_object_name(p_number_mapping):=im_md_mapping(i).object_name;
815 p_property(p_number_mapping):=im_md_mapping(i).property;
816 end if;
817 end loop;
818 return true;
819 Exception when others then
820 BSC_IM_UTILS.g_status_message:=sqlerrm;
821 g_status_message:=sqlerrm;
822 write_to_log_file_n('Error in get_mapping '||sqlerrm);
823 return false;
824 End;
825
826 function get_mapping_detail(
827 p_map_name varchar2,
828 p_apps_origin varchar2,
829 p_line out nocopy BSC_IM_UTILS.varchar_tabletype,
830 p_line_type out nocopy BSC_IM_UTILS.varchar_tabletype,
831 p_property out nocopy BSC_IM_UTILS.varchar_tabletype,
832 p_number_map_detail out nocopy number
833 )return boolean is
834 Begin
835 p_number_map_detail:=0;
836 if number_im_md_mapping_details is null then
837 return true;
838 end if;
839 for i in 1..number_im_md_mapping_details loop
840 if im_md_mapping_details(i).map_name=p_map_name and im_md_mapping_details(i).apps_origin=p_apps_origin then
841 p_number_map_detail:=p_number_map_detail+1;
842 p_line(p_number_map_detail):=im_md_mapping_details(i).line;
843 p_line_type(p_number_map_detail):=im_md_mapping_details(i).line_type;
844 p_property(p_number_map_detail):=im_md_mapping_details(i).property;
845 end if;
846 end loop;
847 return true;
848 Exception when others then
849 BSC_IM_UTILS.g_status_message:=sqlerrm;
850 g_status_message:=sqlerrm;
851 write_to_log_file_n('Error in get_mapping_detail '||sqlerrm);
852 return false;
853 End;
854
855 function get_cube(
856 p_cube_name varchar2,
857 p_apps_origin varchar2,
858 p_cube_id out nocopy number,
859 p_cube_periodicity out nocopy varchar2,
860 p_description out nocopy varchar2,
861 p_property out nocopy varchar2
862 )return boolean is
863 Begin
864 if number_im_md_cube is null then
865 return true;
866 end if;
867 for i in 1..number_im_md_cube loop
868 if im_md_cube(i).cube_name=p_cube_name and im_md_cube(i).apps_origin=p_apps_origin then
869 p_cube_id:=im_md_cube(i).cube_id;
870 p_cube_periodicity:=im_md_cube(i).cube_periodicity;
871 p_description:=im_md_cube(i).description;
872 p_property:=im_md_cube(i).property;
873 exit;
874 end if;
875 end loop;
876 return true;
877 Exception when others then
878 BSC_IM_UTILS.g_status_message:=sqlerrm;
879 g_status_message:=sqlerrm;
880 write_to_log_file_n('Error in get_cube '||sqlerrm);
881 return false;
882 End;
883
884 function get_fk(
885 p_owner_name varchar2,
886 p_apps_origin varchar2,
887 p_fk_name out nocopy BSC_IM_UTILS.varchar_tabletype,
888 p_fk_type out nocopy BSC_IM_UTILS.varchar_tabletype,
889 p_uk_name out nocopy BSC_IM_UTILS.varchar_tabletype,
890 p_uk_parent_name out nocopy BSC_IM_UTILS.varchar_tabletype,
891 p_description out nocopy BSC_IM_UTILS.varchar_tabletype,
892 p_property out nocopy BSC_IM_UTILS.varchar_tabletype,
893 p_number_fk out nocopy number
897 if number_im_md_fk is null then
894 )return boolean is
895 Begin
896 p_number_fk:=0;
898 return true;
899 end if;
900 for i in 1..number_im_md_fk loop
901 if im_md_fk(i).owner_name=p_owner_name and im_md_fk(i).apps_origin=p_apps_origin then
902 p_number_fk:=p_number_fk+1;
903 p_fk_name(p_number_fk):=im_md_fk(i).fk_name;
904 p_fk_type(p_number_fk):=im_md_fk(i).fk_type;
905 p_uk_name(p_number_fk):=im_md_fk(i).uk_name;
906 p_description(p_number_fk):=im_md_fk(i).description;
907 p_uk_parent_name(p_number_fk):=im_md_fk(i).uk_parent_name;
908 p_property(p_number_fk):=im_md_fk(i).property;
909 end if;
910 end loop;
911 return true;
912 Exception when others then
913 BSC_IM_UTILS.g_status_message:=sqlerrm;
914 g_status_message:=sqlerrm;
915 write_to_log_file_n('Error in get_fk '||sqlerrm);
916 return false;
917 End;
918
919 function get_uk(
920 p_owner_name varchar2,
921 p_apps_origin varchar2,
922 p_uk_name out nocopy BSC_IM_UTILS.varchar_tabletype,
923 p_uk_type out nocopy BSC_IM_UTILS.varchar_tabletype,
924 p_description out nocopy BSC_IM_UTILS.varchar_tabletype,
925 p_property out nocopy BSC_IM_UTILS.varchar_tabletype,
926 p_number_uk out nocopy number
927 )return boolean is
928 Begin
929 p_number_uk:=0;
930 if number_im_md_uk is null then
931 return true;
932 end if;
933 for i in 1..number_im_md_uk loop
934 if im_md_uk(i).owner_name=p_owner_name and im_md_uk(i).apps_origin=p_apps_origin then
935 p_number_uk:=p_number_uk+1;
936 p_uk_name(p_number_uk):=im_md_uk(i).uk_name;
937 p_uk_type(p_number_uk):=im_md_uk(i).uk_type;
938 p_description(p_number_uk):=im_md_uk(i).description;
939 p_property(p_number_uk):=im_md_uk(i).property;
940 end if;
941 end loop;
942 return true;
943 Exception when others then
944 BSC_IM_UTILS.g_status_message:=sqlerrm;
945 g_status_message:=sqlerrm;
946 write_to_log_file_n('Error in get_uk '||sqlerrm);
947 return false;
948 End;
949
950 function get_object(
951 p_parent_name varchar2,
952 p_apps_origin varchar2,
953 p_object_name out nocopy BSC_IM_UTILS.varchar_tabletype,
954 p_object_type out nocopy BSC_IM_UTILS.varchar_tabletype,
955 p_description out nocopy BSC_IM_UTILS.varchar_tabletype,
956 p_property out nocopy BSC_IM_UTILS.varchar_tabletype,
957 p_number_object out nocopy number
958 )return boolean is
959 Begin
960 p_number_object:=0;
961 if number_im_md_object is null then
962 return true;
963 end if;
964 for i in 1..number_im_md_object loop
965 if im_md_object(i).parent_name=p_parent_name and im_md_object(i).apps_origin=p_apps_origin then
966 p_number_object:=p_number_object+1;
967 p_object_name(p_number_object):=im_md_object(i).object_name;
968 p_object_type(p_number_object):=im_md_object(i).object_type;
969 p_description(p_number_object):=im_md_object(i).description;
970 p_property(p_number_object):=im_md_object(i).property;
971 end if;
972 end loop;
973 return true;
974 Exception when others then
975 BSC_IM_UTILS.g_status_message:=sqlerrm;
976 g_status_message:=sqlerrm;
977 write_to_log_file_n('Error in get_object '||sqlerrm);
978 return false;
979 End;
980
981 function get_object(
982 p_parent_name varchar2,
983 p_apps_origin varchar2,
984 p_property varchar2,
985 p_object_name out nocopy BSC_IM_UTILS.varchar_tabletype,
986 p_object_type out nocopy BSC_IM_UTILS.varchar_tabletype,
987 p_description out nocopy BSC_IM_UTILS.varchar_tabletype,
988 p_number_object out nocopy number
989 )return boolean is
990 Begin
991 p_number_object:=0;
992 if number_im_md_object is null then
993 return true;
994 end if;
995 for i in 1..number_im_md_object loop
996 if im_md_object(i).parent_name=p_parent_name and im_md_object(i).apps_origin=p_apps_origin and
997 im_md_object(i).property=p_property then
998 p_number_object:=p_number_object+1;
999 p_object_name(p_number_object):=im_md_object(i).object_name;
1000 p_object_type(p_number_object):=im_md_object(i).object_type;
1001 p_description(p_number_object):=im_md_object(i).description;
1002 end if;
1003 end loop;
1004 return true;
1005 Exception when others then
1006 BSC_IM_UTILS.g_status_message:=sqlerrm;
1007 g_status_message:=sqlerrm;
1008 write_to_log_file_n('Error in get_object '||sqlerrm);
1009 return false;
1010 End;
1011
1012 function get_object(
1013 p_object_name varchar2,
1014 p_apps_origin varchar2,
1015 p_parent_name out nocopy varchar2,
1016 p_object_type out nocopy varchar2,
1017 p_description out nocopy varchar2,
1018 p_property out nocopy varchar2
1019 )return boolean is
1020 Begin
1021 if number_im_md_object is null then
1022 return true;
1023 end if;
1024 for i in 1..number_im_md_object loop
1025 if im_md_object(i).object_name=p_object_name and im_md_object(i).apps_origin=p_apps_origin then
1026 p_parent_name:=im_md_object(i).parent_name;
1027 p_object_type:=im_md_object(i).object_type;
1028 p_description:=im_md_object(i).description;
1029 p_property:=im_md_object(i).property;
1030 exit;
1031 end if;
1032 end loop;
1033 return true;
1034 Exception when others then
1035 BSC_IM_UTILS.g_status_message:=sqlerrm;
1036 g_status_message:=sqlerrm;
1037 write_to_log_file_n('Error in get_object '||sqlerrm);
1038 return false;
1039 End;
1040
1041 function get_object_type(p_object varchar2) return varchar2 is
1042 Begin
1043 --dim
1044 if number_im_md_dimensions is not null then
1045 for i in 1..number_im_md_dimensions loop
1049 end loop;
1046 if im_md_dimensions(i).dim_name=p_object then
1047 return 'DIMENSION';
1048 end if;
1050 end if;
1051 --level
1052 if number_im_md_levels is not null then
1053 for i in 1..number_im_md_levels loop
1054 if im_md_levels(i).level_name=p_object then
1055 return 'LEVEL';
1056 end if;
1057 end loop;
1058 end if;
1059 --hier
1060 if number_im_md_hierarchies is not null then
1061 for i in 1..number_im_md_hierarchies loop
1062 if im_md_hierarchies(i).hier_name=p_object then
1063 return 'HIERARCHY';
1064 end if;
1065 end loop;
1066 end if;
1067 --mapping
1068 if number_im_md_mapping is not null then
1069 for i in 1..number_im_md_mapping loop
1070 if im_md_mapping(i).map_name=p_object then
1071 return 'MAPPING';
1072 end if;
1073 end loop;
1074 end if;
1075 --cube
1076 if number_im_md_cube is not null then
1077 for i in 1..number_im_md_cube loop
1078 if im_md_cube(i).cube_name=p_object then
1079 return 'CUBE';
1080 end if;
1081 end loop;
1082 end if;
1083 --object
1084 if number_im_md_object is not null then
1085 for i in 1..number_im_md_object loop
1086 if im_md_object(i).object_name=p_object then
1087 return 'OBJECT';
1088 end if;
1089 end loop;
1090 end if;
1091 --fk
1092 if number_im_md_fk is not null then
1093 for i in 1..number_im_md_fk loop
1094 if im_md_fk(i).fk_name=p_object then
1095 return 'FK';
1096 end if;
1097 end loop;
1098 end if;
1099 --uk
1100 if number_im_md_uk is not null then
1101 for i in 1..number_im_md_uk loop
1102 if im_md_uk(i).uk_name=p_object then
1103 return 'UK';
1104 end if;
1105 end loop;
1106 end if;
1107 return 'OTHER';
1108 Exception when others then
1109 BSC_IM_UTILS.g_status_message:=sqlerrm;
1110 g_status_message:=sqlerrm;
1111 write_to_log_file_n('Error in get_object_type '||sqlerrm);
1112 return null;
1113 End;
1114
1115 --==============================================================================
1116 --****************** GET COUNTS ***********************************************
1117 function get_cube_count return number is
1118 Begin
1119 if number_im_md_cube is null then
1120 return 0;
1121 else
1122 return number_im_md_cube;
1123 end if;
1124 Exception when others then
1125 BSC_IM_UTILS.g_status_message:=sqlerrm;
1126 g_status_message:=sqlerrm;
1127 write_to_log_file_n('Error in get_object_type '||sqlerrm);
1128 return null;
1129 End;
1130
1131 --==============================================================================
1132 --****************** DEBUG DUMPS ***********************************************
1133 procedure dump_dimension is
1134 Begin
1135 write_to_log_file_n('Dump dimension');
1136 if number_im_md_dimensions is null then
1137 return;
1138 end if;
1139 write_to_log_file('dim_name apps_origin description property');
1140 for i in 1..number_im_md_dimensions loop
1141 write_to_log_file_s(im_md_dimensions(i).dim_name);
1142 write_to_log_file_s(im_md_dimensions(i).apps_origin);
1143 write_to_log_file_s(im_md_dimensions(i).description);
1144 write_to_log_file_s(im_md_dimensions(i).property);
1145 write_to_log_file(' ');
1146 end loop;
1147 Exception when others then
1148 BSC_IM_UTILS.g_status_message:=sqlerrm;
1149 write_to_log_file_n('Error in dump_dimension '||sqlerrm);
1150 End;
1151
1152 procedure dump_level is
1153 Begin
1154 write_to_log_file_n('Level level');
1155 if number_im_md_levels is null then
1156 return;
1157 end if;
1158 write_to_log_file('dim_name apps_origin level_name number_children description property');
1159 for i in 1..number_im_md_levels loop
1160 write_to_log_file_s(im_md_levels(i).dim_name);
1161 write_to_log_file_s(im_md_levels(i).apps_origin);
1162 write_to_log_file_s(im_md_levels(i).level_name);
1163 write_to_log_file_s(im_md_levels(i).number_children);
1164 write_to_log_file_s(im_md_levels(i).description);
1165 write_to_log_file_s(im_md_levels(i).property);
1166 write_to_log_file(' ');
1167 end loop;
1168 Exception when others then
1169 BSC_IM_UTILS.g_status_message:=sqlerrm;
1170 write_to_log_file_n('Error in dump_level '||sqlerrm);
1171 End;
1172
1173 procedure dump_column is
1174 Begin
1175 write_to_log_file_n('dump columns');
1176 if number_im_md_columns is null then
1177 return;
1178 end if;
1179 write_to_log_file('parent_name apps_origin column_name column_type column_data_type '||
1180 'column_origin aggregation_type description property');
1181 for i in 1..number_im_md_columns loop
1182 write_to_log_file_s(im_md_columns(i).parent_name);
1183 write_to_log_file_s(im_md_columns(i).apps_origin);
1184 write_to_log_file_s(im_md_columns(i).column_name);
1185 write_to_log_file_s(im_md_columns(i).column_type);
1186 write_to_log_file_s(im_md_columns(i).column_data_type);
1187 write_to_log_file_s(im_md_columns(i).column_origin);
1188 write_to_log_file_s(im_md_columns(i).aggregation_type);
1189 write_to_log_file_s(im_md_columns(i).description);
1190 write_to_log_file_s(im_md_columns(i).property);
1191 write_to_log_file(' ');
1192 end loop;
1193 Exception when others then
1194 BSC_IM_UTILS.g_status_message:=sqlerrm;
1195 write_to_log_file_n('Error in dump_column '||sqlerrm);
1196 End;
1197
1198 procedure dump_level_relation is
1199 Begin
1200 write_to_log_file_n('dump level relations');
1201 if number_im_md_level_relations is null then
1202 return;
1203 end if;
1207 write_to_log_file_s(im_md_level_relations(i).apps_origin);
1204 write_to_log_file('dim_name apps_origin child_level parent_level child_fk parent_pk hier_name property');
1205 for i in 1..number_im_md_level_relations loop
1206 write_to_log_file_s(im_md_level_relations(i).dim_name);
1208 write_to_log_file_s(im_md_level_relations(i).child_level);
1209 write_to_log_file_s(im_md_level_relations(i).parent_level);
1210 write_to_log_file_s(im_md_level_relations(i).child_fk);
1211 write_to_log_file_s(im_md_level_relations(i).parent_pk);
1212 write_to_log_file_s(im_md_level_relations(i).hier_name);
1213 write_to_log_file_s(im_md_level_relations(i).property);
1214 write_to_log_file(' ');
1215 end loop;
1216 Exception when others then
1217 BSC_IM_UTILS.g_status_message:=sqlerrm;
1218 write_to_log_file_n('Error in dump_level_relation '||sqlerrm);
1219 End;
1220
1221 procedure dump_hierarchy is
1222 Begin
1223 write_to_log_file_n('dump hierarchy');
1224 if number_im_md_hierarchies is null then
1225 return;
1226 end if;
1227 write_to_log_file('dim_name apps_origin hier_name description property');
1228 for i in 1..number_im_md_hierarchies loop
1229 write_to_log_file_s(im_md_hierarchies(i).dim_name);
1230 write_to_log_file_s(im_md_hierarchies(i).apps_origin);
1231 write_to_log_file_s(im_md_hierarchies(i).hier_name);
1232 write_to_log_file_s(im_md_hierarchies(i).description);
1233 write_to_log_file_s(im_md_hierarchies(i).property);
1234 write_to_log_file(' ');
1235 end loop;
1236 Exception when others then
1237 BSC_IM_UTILS.g_status_message:=sqlerrm;
1238 write_to_log_file_n('Error in dump_hierarchy '||sqlerrm);
1239 End;
1240
1241 procedure dump_mapping is
1242 Begin
1243 write_to_log_file_n('dump mapping');
1244 if number_im_md_mapping is null then
1245 return;
1246 end if;
1247 write_to_log_file('apps_origin object_name map_name map_type object_name property');
1248 for i in 1..number_im_md_mapping loop
1249 write_to_log_file_s(im_md_mapping(i).apps_origin);
1250 write_to_log_file_s(im_md_mapping(i).object_name);
1251 write_to_log_file_s(im_md_mapping(i).map_name);
1252 write_to_log_file_s(im_md_mapping(i).map_type);
1253 write_to_log_file_s(im_md_mapping(i).object_name);
1254 write_to_log_file_s(im_md_mapping(i).property);
1255 write_to_log_file(' ');
1256 end loop;
1257 Exception when others then
1258 BSC_IM_UTILS.g_status_message:=sqlerrm;
1259 write_to_log_file_n('Error in dump_mapping '||sqlerrm);
1260 End;
1261
1262 procedure dump_mapping_detail is
1263 Begin
1264 write_to_log_file_n('dump mapping detail');
1265 if number_im_md_mapping_details is null then
1266 return;
1267 end if;
1268 write_to_log_file('map_name apps_origin line line_type property');
1269 for i in 1..number_im_md_mapping_details loop
1270 write_to_log_file_s(im_md_mapping_details(i).map_name);
1271 write_to_log_file_s(im_md_mapping_details(i).apps_origin);
1272 write_to_log_file_s(im_md_mapping_details(i).line);
1273 write_to_log_file_s(im_md_mapping_details(i).line_type);
1274 write_to_log_file_s(im_md_mapping_details(i).property);
1275 write_to_log_file(' ');
1276 end loop;
1277 Exception when others then
1278 BSC_IM_UTILS.g_status_message:=sqlerrm;
1279 write_to_log_file_n('Error in dump_mapping_detail '||sqlerrm);
1280 End;
1281
1282 procedure dump_cube is
1283 Begin
1284 write_to_log_file_n('dump cube');
1285 if number_im_md_cube is null then
1286 return;
1287 end if;
1288 write_to_log_file('cube_name apps_origin cube_id cube_periodicity description property');
1289 for i in 1..number_im_md_cube loop
1290 write_to_log_file_s(im_md_cube(i).cube_name);
1291 write_to_log_file_s(im_md_cube(i).apps_origin);
1292 write_to_log_file_s(im_md_cube(i).cube_id);
1293 write_to_log_file_s(im_md_cube(i).cube_periodicity);
1294 write_to_log_file_s(im_md_cube(i).description);
1295 write_to_log_file_s(im_md_cube(i).property);
1296 write_to_log_file(' ');
1297 end loop;
1298 Exception when others then
1299 BSC_IM_UTILS.g_status_message:=sqlerrm;
1300 write_to_log_file_n('Error in dump_cube '||sqlerrm);
1301 End;
1302
1303 procedure dump_fk is
1304 Begin
1305 write_to_log_file_n('dump fk');
1306 if number_im_md_fk is null then
1307 return;
1308 end if;
1309 write_to_log_file('owner_name apps_origin fk_name fk_type uk_name description uk_parent_name'||
1310 'top_parent_name property');
1311 for i in 1..number_im_md_fk loop
1312 write_to_log_file_s(im_md_fk(i).owner_name);
1313 write_to_log_file_s(im_md_fk(i).apps_origin);
1314 write_to_log_file_s(im_md_fk(i).fk_name);
1315 write_to_log_file_s(im_md_fk(i).fk_type);
1316 write_to_log_file_s(im_md_fk(i).uk_name);
1317 write_to_log_file_s(im_md_fk(i).description);
1318 write_to_log_file_s(im_md_fk(i).uk_parent_name);
1319 write_to_log_file_s(im_md_fk(i).property);
1320 write_to_log_file(' ');
1321 end loop;
1322 Exception when others then
1323 BSC_IM_UTILS.g_status_message:=sqlerrm;
1324 write_to_log_file_n('Error in dump_fk '||sqlerrm);
1325 End;
1326
1327 procedure dump_uk is
1328 Begin
1329 write_to_log_file_n('dump uk');
1330 if number_im_md_uk is null then
1331 return;
1332 end if;
1333 write_to_log_file('owner_name apps_origin uk_name uk_type description property');
1334 for i in 1..number_im_md_uk loop
1335 write_to_log_file_s(im_md_uk(i).owner_name);
1336 write_to_log_file_s(im_md_uk(i).apps_origin);
1337 write_to_log_file_s(im_md_uk(i).uk_name);
1338 write_to_log_file_s(im_md_uk(i).uk_type);
1339 write_to_log_file_s(im_md_uk(i).description);
1340 write_to_log_file_s(im_md_uk(i).property);
1341 write_to_log_file(' ');
1342 end loop;
1346 End;
1343 Exception when others then
1344 BSC_IM_UTILS.g_status_message:=sqlerrm;
1345 write_to_log_file_n('Error in dump_uk '||sqlerrm);
1347
1348 procedure dump_object is
1349 Begin
1350 write_to_log_file_n('dump object');
1351 if number_im_md_object is null then
1352 return;
1353 end if;
1354 write_to_log_file('parent_name apps_origin object_name object_type description property');
1355 for i in 1..number_im_md_object loop
1356 write_to_log_file_s(im_md_object(i).parent_name);
1357 write_to_log_file_s(im_md_object(i).apps_origin);
1358 write_to_log_file_s(im_md_object(i).object_name);
1359 write_to_log_file_s(im_md_object(i).object_type);
1360 write_to_log_file_s(im_md_object(i).description);
1361 write_to_log_file_s(im_md_object(i).property);
1362 write_to_log_file(' ');
1363 end loop;
1364 Exception when others then
1365 BSC_IM_UTILS.g_status_message:=sqlerrm;
1366 write_to_log_file_n('Error in dump_object '||sqlerrm);
1367 End;
1368
1369 procedure dump_all is
1370 Begin
1371 dump_dimension;
1372 dump_level;
1373 dump_column;
1374 dump_level_relation;
1375 dump_hierarchy;
1376 dump_mapping;
1377 dump_mapping_detail;
1378 dump_cube;
1379 dump_fk;
1380 dump_uk;
1381 dump_object;
1382 Exception when others then
1383 BSC_IM_UTILS.g_status_message:=sqlerrm;
1384 write_to_log_file_n('Error in dump_all '||sqlerrm);
1385 End;
1386
1387
1388 --==============================================================================
1389 procedure write_to_log_file(p_message varchar2) is
1390 Begin
1391 BSC_IM_UTILS.write_to_log_file(p_message);
1392 Exception when others then
1393 BSC_IM_UTILS.g_status_message:=sqlerrm;
1394 null;
1395 End;
1396
1397 procedure write_to_log_file_s(p_message varchar2) is
1398 Begin
1399 BSC_IM_UTILS.write_to_log_file_s(p_message);
1400 Exception when others then
1401 BSC_IM_UTILS.g_status_message:=sqlerrm;
1402 null;
1403 End;
1404
1405 procedure write_to_log_file_n(p_message varchar2) is
1406 begin
1407 write_to_log_file(' ');
1408 write_to_log_file(p_message);
1409 Exception when others then
1410 BSC_IM_UTILS.g_status_message:=sqlerrm;
1411 null;
1412 end;
1413
1414 procedure write_to_debug_n(p_message varchar2) is
1415 begin
1416 if g_debug then
1417 write_to_log_file_n(p_message);
1418 end if;
1419 Exception when others then
1420 BSC_IM_UTILS.g_status_message:=sqlerrm;
1421 null;
1422 end;
1423
1424 procedure write_to_debug(p_message varchar2) is
1425 begin
1426 if g_debug then
1427 write_to_log_file(p_message);
1428 end if;
1429 Exception when others then
1430 BSC_IM_UTILS.g_status_message:=sqlerrm;
1431 null;
1432 end;
1433
1434 function get_time return varchar2 is
1435 begin
1436 return ' '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS');
1437 Exception when others then
1438 BSC_IM_UTILS.g_status_message:=sqlerrm;
1439 null;
1440 End;
1441
1442 procedure set_globals(p_debug boolean) is
1443 Begin
1444 g_debug:=p_debug;
1445 Exception when others then
1446 BSC_IM_UTILS.g_status_message:=sqlerrm;
1447 write_to_log_file_n('Error in set_globals '||sqlerrm);
1448 End;
1449
1450
1451 END BSC_IM_INT_MD;
[Home] [Help]