1 PACKAGE BSC_UPDATE_SUM AS
2 /* $Header: BSCDSUMS.pls 120.0 2005/06/01 17:07:41 appldev noship $ */
3
4 -- BSC-MV Note: Global variable to store the name of MV that has been refreshed.
5 g_refreshed_mvs BSC_UPDATE_UTIL.t_array_of_varchar2;
6 g_num_refreshed_mvs NUMBER := 0;
7
8
9 /*===========================================================================+
10 |
11 | Name: Calculate_Period_Summary_Table
12 |
13 | Description: This function calculates the period of a summary table
14 | based on the possible change of periodicity and origin
15 | period.
16 |
17 | Parameters: x_periodicity - periodicity of summary table
18 | x_origin_periodicity - periodicity of origin tables
19 | x_origin_period - minimum period of origin tables
20 | x_current_fy - current fiscal year
21 |
22 | Returns: NULL - Failure.
23 |
24 | Notes:
25 |
26 +============================================================================*/
27 FUNCTION Calculate_Period_Summary_Table(
28 x_periodicity IN NUMBER,
29 x_origin_periodicity IN NUMBER,
30 x_origin_period IN NUMBER,
31 x_current_fy IN NUMBER
32 ) RETURN NUMBER;
33
34
35 /*===========================================================================+
36 |
37 | Name: Calculate_Sum_Table
38 |
39 | Description: This function calculates the summary table and its current
40 | period which is stored in the database.
41 |
42 | Parameters: x_sum_table - summary table name
43 |
44 | Returns: TRUE - Success.
45 | FALSE - Failure.
46 |
47 | Notes:
48 |
49 +============================================================================*/
50 FUNCTION Calculate_Sum_Table(
51 x_sum_table IN VARCHAR2
52 ) RETURN BOOLEAN;
53
54 --LOCKING: new function
55 FUNCTION Calculate_Sum_Table_AT(
56 x_sum_table IN VARCHAR2
57 ) RETURN BOOLEAN;
58
59
60 /*===========================================================================+
61 |
62 | Name: Calculate_Sum_Table_MV
63 |
64 | Description: This function calculates the summary table (in BSC-MV
65 | Architecture) and its current
66 | period which is stored in the database.
67 |
68 | Returns: TRUE - Success.
69 | FALSE - Failure.
70 |
71 | Notes:
72 |
73 +============================================================================*/
74 FUNCTION Calculate_Sum_Table_MV(
75 x_sum_table IN VARCHAR2,
76 x_calculated_sys_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
77 x_num_calculated_sys_tables IN NUMBER,
78 x_system_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
79 x_num_system_tables IN NUMBER
80 ) RETURN BOOLEAN;
81
82 --LOCKING: new function
83 FUNCTION Calculate_Sum_Table_MV_AT(
84 x_sum_table IN VARCHAR2,
85 x_calculated_sys_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
86 x_num_calculated_sys_tables IN NUMBER,
87 x_system_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
88 x_num_system_tables IN NUMBER
89 ) RETURN BOOLEAN;
90
91
92 -- AW_INTEGRATION: new fucntion
93 /*===========================================================================+
94 |
95 | Name: Calculate_Sum_Table_AW
96 |
97 | Description: This function calculates the summary table for the give
98 | table that belongs to a AW indicator
99 | It only needs to calculate the current period of the table
100 |
101 | Returns: TRUE - Success.
102 | FALSE - Failure.
103 |
104 | Notes:
105 |
106 +============================================================================*/
107 FUNCTION Calculate_Sum_Table_AW(
108 x_sum_table IN VARCHAR2
109 ) RETURN BOOLEAN;
110
111 --LOCKING: new function
112 FUNCTION Calculate_Sum_Table_AW_AT(
113 x_sum_table IN VARCHAR2
114 ) RETURN BOOLEAN;
115
116
117 /*===========================================================================+
118 |
119 | Name: Calculate_Sum_Table_Total
120 |
121 | Description: This function calculates the summary table (support total
122 | and balance fields).
123 |
124 | Parameters: x_sum_table - summary table name
125 | x_key_columns - array that contains the key columns of
126 | summary table
127 | x_key_dim_tables - array that contains the dimension tables
128 | associated to the keys
129 | x_source_columns - array that contains the source columns
130 | - of each key column of summary table
131 | x_source_dim_tables - array that contains the dimension tables
132 | associated to the source keys
133 | x_num_key_columns - number of key columns of summary table
134 | x_data_columns - array that contains the data columns of
135 | summary table
136 | x_data_formulas - array that contains the data formulas of
137 | summary table
138 | x_data_measure_types - array that constains the type of each
139 | data column (1:Total, 2:Balance)
140 | x_num_data_columns - number of data columns of summary table
141 | x_origin_tables - array that contains the name of origin tables
142 | x_num_origin_tables - number of origin tables
143 | x_key_columns_ori - key columns of origin tables
144 | x_num_key_columns_ori - number of key columns of origin tables
145 | x_periodicity - periodicity of summary table
146 | x_origin_periodicity - periodicity of origin tables
147 | x_period - new period of the summary table Fix bug#4177794
148 | x_origin_period - minimum period of origin tables
149 | x_current_fy - current fiscal year
150 |
151 | Returns: TRUE - Success.
152 | FALSE - Failure.
153 |
154 | Notes:
155 |
156 +============================================================================*/
157 FUNCTION Calculate_Sum_Table_Total(
158 x_sum_table IN VARCHAR2,
159 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
160 x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
161 x_source_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
162 x_source_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
163 x_num_key_columns IN NUMBER,
164 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
165 x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
166 x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
167 x_num_data_columns IN NUMBER,
168 x_origin_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
169 x_num_origin_tables IN NUMBER,
170 x_key_columns_ori IN BSC_UPDATE_UTIL.t_array_of_varchar2,
171 x_num_key_columns_ori IN NUMBER,
172 x_periodicity IN NUMBER,
173 x_origin_periodicity IN NUMBER,
174 x_period IN NUMBER,
175 x_origin_period IN NUMBER,
176 x_current_fy IN NUMBER
177 ) RETURN BOOLEAN;
178
179
180
181 /*===========================================================================+
182 |
183 | Name: Get_Minimun_Origin_Period
184 |
185 | Description: This function returns the minimun period between the current
186 | period of the origin tables given in the array x_origin_tables
187 |
188 | Parameters: x_table_name - table name
189 | x_origin_tables - array of origin tables
190 | x_num_origin_tables - number of origin tables
191 |
192 | Returns: NULL - Failure.
193 |
194 | Notes:
195 |
196 +============================================================================*/
197 FUNCTION Get_Minimun_Origin_Period(
198 x_origin_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
199 x_num_origin_tables IN NUMBER
200 ) RETURN NUMBER;
201
202
203 /*===========================================================================+
204 |
205 | Name: Get_Origin_Tables
206 |
207 | Description: This function returns in the array x_origin_tables the
208 | tables from where the given table is originated. Look
209 | bsc_db_tables_rels
210 |
211 | Parameters: x_table_name - table name
212 | x_origin_tables - array of origin tables
213 | x_num_origin_tables - number of origin tables
214 |
215 | Returns: TRUE - Success.
216 | FALSE - Failure.
217 |
218 | Notes:
219 |
220 +============================================================================*/
221 FUNCTION Get_Origin_Tables(
222 x_table_name IN VARCHAR2,
223 x_origin_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
224 x_num_origin_tables IN OUT NOCOPY NUMBER
225 ) RETURN BOOLEAN;
226
227
228 --LOCKING: new procedure
229 PROCEDURE Refresh_AW_Kpi_AT (
230 x_indicator IN NUMBER
231 );
232
233
234 /*===========================================================================+
235 |
236 | Name: Refresh_Zero_MVs
237 |
238 | Description: This function refreshes all the MVs created
239 | for zero codes for the given MV
240 |
241 | Returns: TRUE - Success.
242 | FALSE - Failure.
243 |
244 | Notes:
245 |
246 +============================================================================*/
247 FUNCTION Refresh_Zero_MVs(
248 x_table_name IN VARCHAR2,
249 x_mv_name IN VARCHAR2,
250 x_error_message IN OUT NOCOPY VARCHAR2
251 ) RETURN BOOLEAN;
252
253 --LOCKING: new function
254 FUNCTION Refresh_Zero_MVs_AT(
255 x_table_name IN VARCHAR2,
256 x_mv_name IN VARCHAR2,
257 x_error_message IN OUT NOCOPY VARCHAR2
258 ) RETURN BOOLEAN;
259
260
261 END BSC_UPDATE_SUM;