1 PACKAGE BSC_UPDATE_CALC AS
2 /* $Header: BSCDCALS.pls 120.0 2005/05/31 19:03:16 appldev noship $ */
3 --
4 -- Global Constants
5 --
6
7 --
8 -- Procedures and Fuctions
9 --
10
11
12 /*===========================================================================+
13 |
14 | Name: Apply_Filters
15 |
16 | Description: This function applies filters on the given table if the
17 | table belong to any indicator that has filter.
18 |
19 | Parameters: x_table_name - table name,
20 |
21 | Returns: TRUE - Success
22 | FALSE - There was some error executing the function
23 |
24 | Notes:
25 |
26 +============================================================================*/
27 FUNCTION Apply_Filters(
28 x_table_name IN VARCHAR2
29 ) RETURN BOOLEAN;
30
31
32 /*===========================================================================+
33 |
34 | Name: Calculate_Profit
35 |
36 | Description: This function calculate the profit on the given table.
37 | We suppose that the table has an account dimension.
38 |
39 | Parameters: x_table_name - table name,
40 | x_key_columns - array with table key columns
41 | x_num_key_columns - number of key columns of the table
42 | x_data_columns - array of data columns
43 | x_num_data_columns - number of data columns
44 |
45 | Returns: TRUE - Success
46 | FALSE - There was some error executing the function
47 |
48 | Notes:
49 |
50 +============================================================================*/
51 FUNCTION Calculate_Profit(
52 x_table_name IN VARCHAR2,
53 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
54 x_key_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
55 x_num_key_columns IN NUMBER,
56 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
57 x_num_data_columns IN NUMBER,
58 x_aw_flag IN BOOLEAN,
59 x_change_vector_value IN NUMBER
60 ) RETURN BOOLEAN;
61
62
63 /*===========================================================================+
64 |
65 | Name: Calculate_Proj_Avg_Last_Year
66 |
67 | Description: This function calculate the projection of a data column
68 | of the table with the method: average last year
69 |
70 | Returns: TRUE - Success
71 | FALSE - There was some error executing the function
72 |
73 | Notes:
74 |
75 +============================================================================*/
76 FUNCTION Calculate_Proj_Avg_Last_Year(
77 x_table_name IN VARCHAR2,
78 x_periodicity IN NUMBER,
79 x_period IN NUMBER,
80 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
81 x_num_key_columns IN NUMBER,
82 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
83 x_num_data_columns IN NUMBER,
84 x_lst_data_temp IN VARCHAR2, -- list of data columns in the projection table i.e: 'DATA1, DATA5'
85 x_current_fy IN NUMBER,
86 x_num_of_years IN NUMBER,
87 x_previous_years IN NUMBER,
88 x_is_base IN BOOLEAN,
89 x_aw_flag IN BOOLEAN
90 ) RETURN BOOLEAN;
91
92
93 /*===========================================================================+
94 |
95 | Name: Calculate_Proj_3_Periods_Perf
96 |
97 | Description: This function calculate the projection of a data column
98 | of the table with the method: 3 periods performance
99 |
100 | Parameters: x_table_name - table name.
101 | x_periodicity - periodicity code
102 | x_period - period of the table
103 | x_key_columns - array with table key columns
104 | x_num_key_columns - number of key columns of the table
105 | x_data_column - data column
106 | x_current_fy - current fiscal year
107 |
108 | Returns: TRUE - Success
109 | FALSE - There was some error executing the function
110 |
111 | Notes:
112 |
113 +============================================================================*/
114 FUNCTION Calculate_Proj_3_Periods_Perf(
115 x_table_name IN VARCHAR2,
116 x_periodicity IN NUMBER,
117 x_period IN NUMBER,
118 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
119 x_num_key_columns IN NUMBER,
120 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
121 x_num_data_columns IN NUMBER,
122 x_lst_data_temp IN VARCHAR2, -- list of data columns in the projection table i.e: 'DATA1, DATA5'
123 x_current_fy IN NUMBER,
124 x_is_base IN BOOLEAN,
125 x_aw_flag IN BOOLEAN
126 ) RETURN BOOLEAN;
127
128
129 /*===========================================================================+
130 |
131 | Name: Calculate_Proj_User_Defined
132 |
133 | Description: This function calculate the projection of a data column
134 | of the table with the method: user provide projection data
135 |
136 | Parameters: x_table_name - table name.
137 | x_periodicity - periodicity code
138 | x_period - period of the table
139 | x_key_columns - array with table key columns
140 | x_num_key_columns - number of key columns of the table
141 | x_data_column - data column
142 | x_current_fy - current fiscal year
143 |
144 | Returns: TRUE - Success
145 | FALSE - There was some error executing the function
146 |
147 | Notes:
148 |
149 +============================================================================*/
150 FUNCTION Calculate_Proj_User_Defined(
151 x_table_name IN VARCHAR2,
152 x_periodicity IN NUMBER,
153 x_period IN NUMBER,
154 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
155 x_num_key_columns IN NUMBER,
156 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
157 x_num_data_columns IN NUMBER,
158 x_lst_data_temp IN VARCHAR2, -- list of data columns in the projection table i.e: 'DATA1, DATA5'
159 x_current_fy IN NUMBER,
160 x_is_base IN BOOLEAN,
161 x_aw_flag IN BOOLEAN
162 ) RETURN BOOLEAN;
163
164
165 /*===========================================================================+
166 |
167 | Name: Calculate_Projection
168 |
169 | Description: This function calculate the projection on the table
170 |
171 | Parameters: x_table_name - table name.
172 | x_periodicity - periodicity code
173 | x_period - period of the table
174 | x_key_columns - array with table key columns
175 | x_num_key_columns - number of key columns of the table
176 | x_data_columns - array with table data columns
177 | x_data_proj_methods -array with data projection methods
178 | x_num_data_columns - number of data columns of the table
179 | x_num_of_years - number of years used by the table
180 | x_previous_years - number of previous years
181 | x_is_base - true - base table, false - summary table
182 |
183 | Returns: TRUE - Success
184 | FALSE - There was some error executing the function
185 |
186 | Notes:
187 |
188 +============================================================================*/
189 FUNCTION Calculate_Projection(
190 x_table_name IN VARCHAR2,
191 x_periodicity IN NUMBER,
192 x_period IN NUMBER,
193 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
194 x_num_key_columns IN NUMBER,
195 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
196 x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
197 x_num_data_columns IN NUMBER,
198 x_current_fy IN NUMBER,
199 x_num_of_years IN NUMBER,
200 x_previous_years IN NUMBER,
201 x_is_base IN BOOLEAN,
202 x_aw_flag IN BOOLEAN,
203 x_change_vector_value IN NUMBER
204 ) RETURN BOOLEAN;
205
206
207 /*===========================================================================+
208 |
209 | Name: Calculate_Zero_Code
210 |
211 | Description: This function calculate the zero code in the table.
212 |
213 | Parameters: x_table_name - table name.
214 | x_zero_code_calc_method - zero code calculation method
215 | x_src_table If this parameter is different from null
216 | it does not calculate zero code in keys where
217 | the source table already has zero code.
218 |
219 | Returns: TRUE - Success
220 | FALSE - There was some error executing the function
221 |
222 | Notes:
223 |
224 +============================================================================*/
225 FUNCTION Calculate_Zero_Code(
226 x_table_name IN VARCHAR2,
227 x_zero_code_calc_method IN NUMBER,
228 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
229 x_num_key_columns IN NUMBER,
230 x_src_table IN VARCHAR2
231 ) RETURN BOOLEAN;
232
233
234 /*===========================================================================+
235 |
236 | Name: Create_Proj_Temps
237 |
238 | Description: This function creates temporary tables for projection.
239 |
240 | Parameters: x_periodicity - periodicity code
241 | x_num_of_years - number of years used by the table
242 | x_previous_years - number of previous years
243 |
244 | Returns: TRUE - Success
245 | FALSE - There was some error executing the function
246 |
247 | Notes:
248 |
249 +============================================================================*/
250 FUNCTION Create_Proj_Temps(
251 x_periodicity IN NUMBER,
252 x_current_fy IN NUMBER,
253 x_num_of_years IN NUMBER,
254 x_previous_years IN NUMBER,
255 x_trunc_proj_table IN BOOLEAN
256 ) RETURN BOOLEAN;
257
258
259 /*===========================================================================+
260 |
261 | Name: Init_Projection_Table
262 |
263 | Description: This function initialize the projection table BSC_TMP_PROJ_CALC
264 | with the projection rows
265 |
266 | Parameters: x_table_name - table name.
267 | x_periodicity - periodicity code
268 | x_key_columns - array with table key columns
269 | x_num_key_columns - number of key columns of the table
270 | x_current_fy - current fiscal year
271 | x_is_base - true - base table, false - summary table
272 |
273 | Returns: TRUE - Success
274 | FALSE - There was some error executing the function
275 |
276 | Notes:
277 |
278 +============================================================================*/
279 FUNCTION Init_Projection_Table(
280 x_table_name IN VARCHAR2,
281 x_periodicity IN NUMBER,
282 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
283 x_num_key_columns IN NUMBER,
284 x_current_fy IN NUMBER,
285 x_current_period IN NUMBER,
286 x_is_base IN BOOLEAN,
287 x_aw_flag IN BOOLEAN,
288 x_change_vector_value IN NUMBER
289 ) RETURN BOOLEAN;
290
291
292 /*===========================================================================+
293 |
294 | Name: Delete_Projection
295 |
296 | Description: This function delete the projection of a table
297 |
298 | Parameters: x_table_name - table name.
299 | x_periodicity - periodicity code
300 | x_period - current period
301 | x_data_columns - array with table data columns
302 | x_data_proj_methods - array with the data projection methods
303 | x_num_data_columns - number of data columns of the table
304 | x_current_fy - current fiscal year
305 |
306 | Returns: TRUE - Success
307 | FALSE - There was some error executing the function
308 |
309 | Notes:
310 |
311 +============================================================================*/
312 FUNCTION Delete_Projection(
313 x_table_name IN VARCHAR2,
314 x_periodicity IN NUMBER,
315 x_period IN NUMBER,
316 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
317 x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
318 x_num_data_columns IN NUMBER,
319 x_current_fy IN NUMBER,
320 x_is_base IN BOOLEAN
321 ) RETURN BOOLEAN;
322
323
324 /*===========================================================================+
325 |
326 | Name: Delete_Projection_Base_Table
327 |
328 | Description: This function delete the projection of a base table.
329 | It update the data with NULL for periods > x_current_period
330 | and <=x_new_current_period
331 |
332 | Returns: TRUE - Success
333 | FALSE - There was some error executing the function
334 |
335 | Notes:
336 |
337 +============================================================================*/
338 FUNCTION Delete_Projection_Base_Table(
339 x_table_name IN VARCHAR2,
340 x_periodicity IN NUMBER,
341 x_current_period IN NUMBER,
342 x_new_current_period IN NUMBER,
343 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
344 x_data_proj_methods IN BSC_UPDATE_UTIL.t_array_of_number,
345 x_num_data_columns IN NUMBER,
346 x_current_fy IN NUMBER,
347 x_aw_flag IN BOOLEAN,
348 x_change_vector_value IN NUMBER
349 ) RETURN BOOLEAN;
350
351
352 /*===========================================================================+
353 |
354 | Name: Drop_Proj_Temps
355 |
356 | Description: This function drop temporary tables for projection.
357 |
358 | Parameters:
359 |
360 | Returns: TRUE - Success
361 | FALSE - There was some error executing the function
362 |
363 | Notes:
364 |
365 +============================================================================*/
366 FUNCTION Drop_Proj_Temps RETURN BOOLEAN;
367
368
369 /*===========================================================================+
370 |
371 | Name: Get_Zero_Code_Calc_Method
372 |
373 | Description: This function returns the code zero method of the table.
374 | A table only has one zero code calculation method.
375 |
376 | Parameters: x_table_name - table name
377 |
378 | Returns: NULL - There was some error executing the function.
379 | # - Code zero method (3 or 4). If the table doesn't
380 | calculate zero code then returns 0.
381 |
382 | Notes:
383 |
384 +============================================================================*/
385 FUNCTION Get_Zero_Code_Calc_Method(
386 x_table_name IN VARCHAR2
387 ) RETURN NUMBER;
388
389
390 /*===========================================================================+
391 |
392 | Name: Merge_Data_From_Tables
393 |
394 | Description: Merge data from another tables in the given table.
395 | Rules:
396 | -- Target table can have multiple source tables and are
397 | in BSC_DB_CALCULATIONS as CALCULATION_TYPE = 5.
401 | BSC_DB_CALCULATIONS (PARAMETER2). The specified data
398 | -- Source tables must have same dimensions and periodicity
399 | as the target table.
400 | -- The data column are specify by source table in
402 | columns must exist in both the source and target table.
403 |
404 | Parameters: x_table_name - table name
405 | x_key_columns - array with table key columns
406 | x_num_key_columns - number of key columns of the table
407 |
408 | Returns: TRUE - Success
409 | FALSE - There was some error executing the function
410 |
411 | Notes:
412 |
413 +============================================================================*/
414 FUNCTION Merge_Data_From_Tables(
415 x_table_name IN VARCHAR2,
416 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
417 x_num_key_columns IN NUMBER
418 ) RETURN BOOLEAN;
419
420
421 /*===========================================================================+
422 | FUNCTION Merge_Projection |
423 +============================================================================*/
424 FUNCTION Merge_Projection(
425 x_table_name VARCHAR2,
426 x_key_columns BSC_UPDATE_UTIL.t_array_of_varchar2,
427 x_num_key_columns NUMBER,
428 x_data_columns BSC_UPDATE_UTIL.t_array_of_varchar2,
429 x_num_data_columns NUMBER,
430 x_is_base BOOLEAN,
431 x_aw_flag BOOLEAN
432 ) RETURN BOOLEAN;
433
434
435 /*===========================================================================+
436 |
437 | Name: Refresh_EDW_Views
438 |
439 | Description: This function refresh the materialized view associated to
440 | the given BSC table. Also deletes rows from the bsc table
441 | which exist in the materialized view. Finally recreate
442 | the union view (bsc table + materialized view).
443 | Returns in x_current_period the maximun period reported in
444 | the materialized view.
445 |
446 | Returns TRUE - Success
447 | FALSE - There was some error executing the function
448 |
449 | Notes:
450 |
451 +============================================================================*/
452 FUNCTION Refresh_EDW_Views(
453 x_table_name IN VARCHAR2,
454 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2 ,
455 x_num_key_columns IN NUMBER ,
456 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
457 x_num_data_columns IN NUMBER,
458 x_current_fy IN NUMBER,
459 x_periodicity IN NUMBER,
460 x_current_period OUT NOCOPY NUMBER
461 ) RETURN BOOLEAN;
462
463
464 /*===========================================================================+
465 |
466 | Name: Rollup_Projection
467 |
468 | Description: This function calculate the projection for a higher
469 | periodicity based on the projection already calculated
470 | for the base periodicity.
471 |
472 | Notes:
473 |
474 +============================================================================*/
475 FUNCTION Rollup_Projection(
476 x_periodicity IN NUMBER,
477 x_period IN NUMBER,
478 x_base_periodicity IN NUMBER,
479 x_base_period IN NUMBER,
480 x_key_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
481 x_num_key_columns IN NUMBER,
482 x_data_columns IN BSC_UPDATE_UTIL.t_array_of_varchar2,
483 x_data_formulas IN BSC_UPDATE_UTIL.t_array_of_varchar2,
484 x_data_measure_types IN BSC_UPDATE_UTIL.t_array_of_number,
485 x_num_data_columns IN NUMBER,
486 x_current_fy IN NUMBER,
487 x_is_base IN BOOLEAN
488 ) RETURN BOOLEAN;
489
490
491 /*===========================================================================+
492 |
493 | Name: Table_Has_Profit_Calc
494 |
495 | Description: This function say if the table has prifit calculation.
496 |
497 | Parameters: x_table_name - Table name.
498 |
499 | Returns: TRUE - Table has profit calculation
500 | FALSE - Table doesn't have profit calculation
501 | NULL - There was some error executing the function
502 |
503 | Notes:
504 |
505 +============================================================================*/
506 FUNCTION Table_Has_Profit_Calc(
507 x_table_name IN VARCHAR2
508 ) RETURN BOOLEAN;
509
510
511 /*===========================================================================+
512 |
513 | Name: Table_Has_Proj_Calc
514 |
515 | Description: This function say if the table has projection calculation.
516 |
517 | Parameters: x_table_name - Table name.
518 |
519 | Returns: TRUE - Table has projection calculation
520 | FALSE - Table doesn't have projection calculation
521 | NULL - There was some error executing the function
522 |
523 | Notes:
524 |
525 +============================================================================*/
526 FUNCTION Table_Has_Proj_Calc(
527 x_table_name IN VARCHAR2
528 ) RETURN BOOLEAN;
529
530
531 END BSC_UPDATE_CALC;
[Home] [Help]