1 PACKAGE BSC_TEMPLATE AUTHID CURRENT_USER AS
2 /* $Header: BSCUTMPS.pls 115.6 2003/06/10 07:10:22 pajohri ship $ */
3
4 /*=========================================================================+
5 | Copyright (c) 1999 Oracle Corporation Belmont, California, USA |
6 | All rights reserved |
7 +=========================================================================*/
8 /*-------------------------------------------------------------------------*
9 | |
10 |FILENAME |
11 | |
12 | BSCUTMPS.pls |
13 | |
14 |DESCRIPTION |
15 | |
16 | Package for creating a Baclanced Scorecard layout for a new system. |
17 | |
18 |Notes: |
19 | |
20 | OBSC only supports 2 layouts, Tab and Cross, while creating a new |
21 | system from KPI designer in release 1.0. In the later releases, |
22 | OBSC may have more templates, for example, Tab with 3 tabs, or |
23 | with 4 tabs. |
24 | |
25 |HISTORY |
26 | 02/07/1999 Alex Yang Created. |
27 | 12/21/1999 Henry Camacho Modified to Model 4.0 |
28 | 23-FEB-03 PAJOHRI Added Short_Name to Dfamily_Rec_Type |
29 *-------------------------------------------------------------------------*/
30 -- Global Variables
31 --
32
33 -- Record type for BSC_TABS_B,BSC_TABS_TL table
34 -- For Tab system only
35 Type Tab_Rec_Type Is Record (
36 Code BSC_TABS_B.tab_id%type,
37 Name BSC_TABS_TL.name%type,
38 Help BSC_TABS_TL.help%type,
39 N_Groups number(3) -- number of groups within tab
40 );
41
42
43 TYPE Tab_Tbl_Type IS TABLE OF Tab_Rec_Type
44 INDEX BY BINARY_INTEGER;
45
46
47 -- Record type for BSC_TAB_CSF_B, BSC_TAB_CSF_TL table
48 -- For a Cross system only
49 Type Csf_Rec_Type Is Record (
50 Code BSC_TAB_CSF_B.csf_id%type,
51 Type BSC_TAB_CSF_B.csf_type%type,
52 Inter_Flag BSC_TAB_CSF_B.intermediate_flag%type,
53 Name BSC_TAB_CSF_TL.name%type,
54 Help BSC_TAB_CSF_TL.help%type
55 );
56
57
58 TYPE Csf_Tbl_Type IS TABLE OF Csf_Rec_Type
59 INDEX BY BINARY_INTEGER;
60
61
62 -- record type for BSC_TAB_IND_GROUPS_B, BSC_TAB_IND_GROUPS_TL table
63 Type Group_Rec_Type Is Record (
64 Tab BSC_TAB_IND_GROUPS_B.tab_id%type,
65 Code BSC_TAB_IND_GROUPS_B.ind_group_id%type,
66 Name BSC_TAB_IND_GROUPS_TL.name%type,
67 Help BSC_TAB_IND_GROUPS_TL.help%type
68 );
69
70 TYPE Group_Tbl_Type IS TABLE OF Group_Rec_Type
71 INDEX BY BINARY_INTEGER;
72
73
74 -- record type for BSC_KPIS_B, BSC_KPIS_TL table
75 -- Options : the number of records in BSC_KPI_PROPERTIESfor an indicator
76 -- Detail_Flag : value 'NO' means only inserting records into BSC_KPI_PERIODICITIES
77 -- table for a indicator
78 -- Period_Shown : the number of records in BSC_KPI_PERIODICITIES,
79 -- and BSC_KPI_DATA_TABLES tables
80 -- for an indicator
81 -- System_C : For Tab system only. The Tab number which the indicator
82 -- belong to
83
84
85 Type Ind_Rec_Type Is Record (
86 Indicator BSC_KPIS_B.indicator%type,
87 Csf BSC_KPIS_B.csf_id%type,
88 Group_r BSC_KPIS_B.ind_group_id%type,
89 Type BSC_KPIS_B.indicator_type%type,
90 Config BSC_KPIS_B.config_type%type,
91 Periodicity BSC_KPIS_B.periodicity_id%type,
92 --
93 Name BSC_KPIS_TL.name%type,
94 Help BSC_KPIS_TL.help%type,
95 --
96 Options Number,
97 Detail_Flag Varchar2(3) := 'YES',
98 Period_Shown Number := 1,
99 Tab Number(5) := NULL,
100 Drills Number(5) := 1, -- Number of dimensions
101 ---
102 User_Options Number -- User Options
103 );
104
105 TYPE Ind_Tbl_Type IS TABLE OF Ind_Rec_Type
106 INDEX BY BINARY_INTEGER;
107
108 -- record type for BSC_KPI_PROPERTIES table
109 Type Var_Rec_Type Is Record (
110 Code BSC_KPI_PROPERTIES.property_code%type,
111 Value BSC_KPI_PROPERTIES.property_value%type
112 );
113
114 TYPE Var_Tbl_Type IS TABLE OF Var_Rec_Type
115 INDEX BY BINARY_INTEGER;
116
117 -- record type for BSC_KPI_CALCULATIONS table
118 Type Cal_Rec_Type Is Record (
119 Calculation BSC_KPI_CALCULATIONS.calculation_id%type,
120 EV0 BSC_KPI_CALCULATIONS.user_level0%type
121 );
122
123 TYPE Cal_Tbl_Type IS TABLE OF Cal_Rec_Type
124 INDEX BY BINARY_INTEGER;
125
126 -- record type for BSC_KPI_PERIODICITIES table
127 Type Period_Rec_Type Is Record (
128 Order_r BSC_KPI_PERIODICITIES.display_order%type,
129 Period_Type BSC_KPI_PERIODICITIES.periodicity_id%type,
130 Prev_Year BSC_KPI_PERIODICITIES.previous_years%type,
131 Num_Years BSC_KPI_PERIODICITIES.num_of_years%type,
132 Viewport_flag BSC_KPI_PERIODICITIES.viewport_flag%type,
133 Viewport_Size BSC_KPI_PERIODICITIES.viewport_default_size%type
134 );
135
136 Type Period_Tbl_Type IS TABLE OF Period_Rec_Type
137 INDEX BY BINARY_INTEGER;
138
139
140 -- record type for BSC_KPI_DIM_LEVELS_B, BSC_KPI_DIM_LEVELS_TL table
141 Type Drill_Rec_Type Is Record (
142 Dim_level_index BSC_KPI_DIM_LEVELS_B.Dim_level_index%type,
143 Table_Name BSC_KPI_DIM_LEVELS_B.level_table_Name%type,
144 Filter_Val BSC_KPI_DIM_LEVELS_B.filter_value%type,
145 Default_val BSC_KPI_DIM_LEVELS_B.default_value%type,
146 Default_type BSC_KPI_DIM_LEVELS_B.Default_type%type,
147 Value_Order BSC_KPI_DIM_LEVELS_B.value_order_by%type,
148 Comp_Order BSC_KPI_DIM_LEVELS_B.Comp_order_by%type,
149 Level_pk_col BSC_KPI_DIM_LEVELS_B.Level_pk_col%type,
150 Parent BSC_KPI_DIM_LEVELS_B.parent_level_index%type,
151 Parent_Rel BSC_KPI_DIM_LEVELS_B.parent_level_rel%type,
152 Table_Rel BSC_KPI_DIM_LEVELS_B.table_relation%type,
153 Parent2 BSC_KPI_DIM_LEVELS_B.parent_level_index2%type,
154 Parent_Rel2 BSC_KPI_DIM_LEVELS_B.parent_level_rel2%type,
155 Status BSC_KPI_DIM_LEVELS_B.status%type,
156 Position BSC_KPI_DIM_LEVELS_B.position%type,
157 Total0 BSC_KPI_DIM_LEVELS_B.total0%type,
158 Ev0 BSC_KPI_DIM_LEVELS_B.user_level0%type,
159 Ev1d BSC_KPI_DIM_LEVELS_B.user_level1_default%type,
160 --
161 Name BSC_KPI_DIM_LEVELS_TL.name%type,
162 Help BSC_KPI_DIM_LEVELS_TL.help%type,
163 Total BSC_KPI_DIM_LEVELS_TL.total_disp_name%type,
164 Comp BSC_KPI_DIM_LEVELS_TL.comp_disp_name%type,
165
166 --
167 dim_group_id BSC_KPI_DIM_GROUPS.dim_group_id%type,
168 dim_group_idx BSC_KPI_DIM_GROUPS.dim_group_index%type,
169 --
170 dim_level_id BSC_KPI_DIM_LEVEL_PROPERTIES.dim_level_id%type,
171 level_display BSC_KPI_DIM_LEVEL_PROPERTIES.level_display%type,
172 --
173 Level_View_Name BSC_SYS_DIM_LEVELS_B.level_view_name%type
174 );
175
176 TYPE Drill_Tbl_Type IS TABLE OF Drill_Rec_Type
177 INDEX BY BINARY_INTEGER;
178
179
180 -- record type for BSC_SYS_DIM_LEVELS_B, BSC_SYS_DIM_LEVELS_TL table
181
182 Type Project_Rec_Type Is Record (
183 Dim_level_id BSC_SYS_DIM_LEVELS_B.Dim_level_id%type,
184 Table_name BSC_SYS_DIM_LEVELS_B.level_Table_name%type,
185 Table_Type BSC_SYS_DIM_LEVELS_B.table_type%type,
186 Level_pk_col BSC_SYS_DIM_LEVELS_B.Level_pk_col%type,
187 Abbreviation BSC_SYS_DIM_LEVELS_B.abbreviation%type,
188 Value_Order BSC_SYS_DIM_LEVELS_B.value_order_by%type,
189 Comp_Order BSC_SYS_DIM_LEVELS_B.Comp_order_by%type,
190 Custom_Group BSC_SYS_DIM_LEVELS_B.custom_group%type,
191 User_size BSC_SYS_DIM_LEVELS_B.user_key_size%type,
192 Disp_size BSC_SYS_DIM_LEVELS_B.disp_key_size%type,
193 --
194 Name BSC_SYS_DIM_LEVELS_TL.name%type,
195 Help BSC_SYS_DIM_LEVELS_TL.help%type,
196 Caption_Tot BSC_SYS_DIM_LEVELS_TL.total_disp_name%type,
197 Caption_Com BSC_SYS_DIM_LEVELS_TL.comp_disp_name%type,
198 --
199 parent_level BSC_SYS_DIM_LEVEL_RELS.parent_dim_level_id%type,
200 fk_field BSC_SYS_DIM_LEVEL_RELS.relation_col%type,
201 rel_type BSC_SYS_DIM_LEVEL_RELS.relation_type%type,
202 direct_rel BSC_SYS_DIM_LEVEL_RELS.direct_relation%type,
203 --
204 Level_View_Name BSC_SYS_DIM_LEVELS_B.level_view_name%type
205 );
206
207 Type Project_Tbl_Type IS TABLE OF Project_Rec_Type
208 INDEX BY BINARY_INTEGER;
209
210
211 -- record type for BSC_SYS_DIM_GROUPS_TL, BSC_SYS_DIM_LEVELS_BY_GROUP table
212
213 Type Dfamily_Rec_Type Is Record (
214 Dim_group_id BSC_SYS_DIM_GROUPS_TL.dim_group_id%type,
215 Name BSC_SYS_DIM_GROUPS_TL.name%type,
216 Short_Name BSC_SYS_DIM_GROUPS_TL.short_name%type,
217 --
218 Dim_level_id BSC_SYS_DIM_LEVELS_BY_GROUP.Dim_level_id%type,
219 Dim_level_idx BSC_SYS_DIM_LEVELS_BY_GROUP.Dim_level_index%type,
220 Total BSC_SYS_DIM_LEVELS_BY_GROUP.total_flag%type,
221 Comparison BSC_SYS_DIM_LEVELS_BY_GROUP.comparison_flag%type,
222 filter_col BSC_SYS_DIM_LEVELS_BY_GROUP.filter_column%type,
223 filter_val BSC_SYS_DIM_LEVELS_BY_GROUP.filter_value%type,
224 default_val BSC_SYS_DIM_LEVELS_BY_GROUP.default_value%type,
225 default_type BSC_SYS_DIM_LEVELS_BY_GROUP.default_type%type,
226 Parent_Total BSC_SYS_DIM_LEVELS_BY_GROUP.parent_in_total%type,
227 No_items BSC_SYS_DIM_LEVELS_BY_GROUP.No_items%type
228 );
229
230 Type Dfamily_Tbl_Type IS TABLE OF Dfamily_Rec_Type
231 INDEX BY BINARY_INTEGER;
232
233
234 -- record type for BSC_SYS_MEASURES table
235
236 Type Proj_Field_Rec_Type Is Record (
237 Measure_id BSC_SYS_MEASURES.Measure_id%type,
238 Measure_col BSC_SYS_MEASURES.Measure_col%type,
239 Operation BSC_SYS_MEASURES.operation%type,
240 Type BSC_SYS_MEASURES.type%type,
241 Min_Actual BSC_SYS_MEASURES.min_actual_value%type,
242 Max_Actual BSC_SYS_MEASURES.max_actual_value%type,
243 Min_Plan BSC_SYS_MEASURES.min_budget_value%type,
244 Max_Plan BSC_SYS_MEASURES.max_budget_value%type,
245 Style BSC_SYS_MEASURES.random_style%type,
246 Insert_Var Boolean := FALSE
247 );
248
249 Type Proj_Field_Tbl_Type IS TABLE OF Proj_Field_Rec_Type
250 INDEX BY BINARY_INTEGER;
251
252
253 -- record type for BSC_SYS_DATASETS_B, BSC_SYS_DATASETS_TL table
254
255 Type Proj_Data_Rec_Type Is Record (
256 Dataset_id BSC_SYS_DATASETS_B.Dataset_id%type,
257 Measure1 BSC_SYS_DATASETS_B.Measure_id1%type,
258 Operation BSC_SYS_DATASETS_B.operation%type,
259 Measure2 BSC_SYS_DATASETS_B.Measure_id2%type,
260 Format BSC_SYS_DATASETS_B.format_id%type,
261 Color_Method BSC_SYS_DATASETS_B.color_method%type,
262 Proj_Flag BSC_SYS_DATASETS_B.projection_flag%type,
263 --
264 name BSC_SYS_DATASETS_TL.name%type,
265 Help BSC_SYS_DATASETS_TL.help%type,
266 num_of_calc number(3)
267 );
268
269 Type Proj_Data_Tbl_Type IS TABLE OF Proj_Data_Rec_Type
270 INDEX BY BINARY_INTEGER;
271
272
273 -- record type for BSC_SYS_DIM_LEVEL_COLS table
274
275 Type Proj_Dim_Cols_Rec_Type Is Record (
276 Dim_level_id BSC_SYS_DIM_LEVEL_COLS.dim_level_id%type,
277 Column_Name BSC_SYS_DIM_LEVEL_COLS.column_name%type,
278 Column_Type BSC_SYS_DIM_LEVEL_COLS.column_type%type
279 );
280
281 Type Proj_Dim_Cols_Tbl_Type IS TABLE OF Proj_Dim_Cols_Rec_Type
282 INDEX BY BINARY_INTEGER;
283
284
285 BSC_SYS_ERROR Exception;
286 BSC_DEF_ERROR Exception;
287
288 LOOKUP_VALUES_TABLE VARCHAR2(50);
289
290
291 /*===========================================================================+
292 |
293 | Name: Create_Template
294 |
295 | Description: Main entry from BSC designer to create BSC default
296 | layout from selected template
297 |
298 | Parameters:
299 | x_template_name template name
300 | x_template_type template type, 6 for Tab and 1 for Cross
301 | x_sys_name OBSC system name
302 | x_sys_desc OBSC system description
303 | x_sys_help Help text
304 | x_debug_flag debug_flag, default is 'NO'
305 |
306 +============================================================================*/
307
308 Procedure Create_Template (
309 x_template_name IN Varchar2,
310 x_template_type IN Number,
311 x_sys_name IN Varchar2,
312 x_sys_desc IN Varchar2,
313 x_sys_help IN Varchar2,
314 x_debug_flag IN Varchar2 := 'NO'
315 );
316
317
318 /*===========================================================================+
319 |
320 | Name: Restore_Init_Layout
321 |
322 | Description: Rollback changes if error occurs during template creation
323 |
324 | Parameters:
325 | x_template_name template name
326 | x_template_type template type, 6 for Tab and 1 for Cross
327 | x_debug_flag debug_flag, default is 'NO'
328 |
329 +============================================================================*/
330 Procedure Restore_Init_Layout(
331 x_template_name IN Varchar2,
332 x_template_type IN Number,
333 x_debug_flag IN Varchar2 := 'NO'
334 );
335
336
337 END BSC_TEMPLATE;
[Home] [Help]