1 Package Body BSC_PMA_APIS_PUB AS
2 /* $Header: BSCPMAPB.pls 120.3 2006/01/11 13:26:10 arsantha noship $ */
3
4 FUNCTION is_recursive(p_dim_short_name VARCHAR2) return boolean is
5 cursor cParent is
6 select dim_level_id, parent_dim_level_id from bsc_sys_dim_level_rels
7 where dim_level_id in(select dim_level_id from bsc_sys_dim_levels_b where short_name=p_dim_short_name);
8 l_dim_level_id number;
9 l_parent_dim_level_id number;
10 BEGIN
11 open cParent;
12 fetch cParent into l_dim_level_id, l_parent_dim_level_id;
13 if (cParent%NOTFOUND) then
14 close cParent;
15 return false;
16 end if;
17 close cParent;
18 if (l_dim_level_id=l_parent_dim_level_id) then
19 return true;
20 end if;
21 return false;
22 END;
23
24 procedure drop_denorm_table(p_dim_short_name varchar2) is
25 l_denorm_table varchar2(100);
26 begin
27 l_denorm_table := BSC_DBGEN_METADATA_READER.get_denorm_dimension_table(p_dim_short_name);
28 if bsc_dbgen_utils.table_exists(l_denorm_table) then
29 bsc_dbgen_utils.drop_table(l_denorm_table);
30 end if;
31 end;
32
33 FUNCTION sync_dimension_table(p_dim_short_name VARCHAR2, p_action VARCHAR2, p_error_message OUT NOCOPY VARCHAR2) return BOOLEAN IS
34 BEGIN
35 if (p_action='DROP') then
36 drop_denorm_table(p_dim_short_name);
37 elsif p_action='ALTER' then
38 -- check if a recursive dim has been changed to non-recursive, in this case, drop denorm table
39 if is_recursive(p_dim_short_name)=false then
40 drop_denorm_table(p_dim_short_name);
41 end if;
42 end if;
43 return true;
44 exception when others then
45 p_error_message := sqlerrm;
46 return false;
47 END;
48
49 PROCEDURE get_summary_object_for_level(
50 p_objective in number,
51 p_periodicity_id in number,
52 p_dim_set_id in number,
53 p_level_pattern in varchar2,
54 p_option_string in varchar2,
55 p_table_name out nocopy varchar2,
56 p_mv_name out nocopy varchar2,
57 p_data_source out nocopy varchar2,
58 p_sql_stmt out nocopy varchar2,
59 p_projection_source out nocopy number,
60 p_projection_data out nocopy varchar2,
61 p_error_message out nocopy varchar2
62 ) IS
63 cursor cSummaryInfo IS
64 select table_name, mv_name, data_source, sql_stmt, projection_source, projection_data
65 from bsc_kpi_data_tables
66 where indicator = p_objective
67 and periodicity_id = p_periodicity_id
68 and dim_set_id = p_dim_set_id
69 and level_comb = p_level_pattern;
70 BEGIN
71 open cSummaryInfo;
72 fetch cSummaryInfo into p_table_name, p_mv_name, p_data_source, p_sql_stmt,
73 p_projection_source, p_projection_data;
74 close cSummaryInfo;
75 EXCEPTION WHEN OTHERS THEN
76 p_error_message:= 'Error in get_summary_object_for_level:'||sqlerrm;
77 RAISE;
78 END;
79
80 END BSC_PMA_APIS_PUB;
[Home] [Help]