[Home] [Help]
PACKAGE BODY: APPS.BSC_TAB_TPLATE
Source
1 PACKAGE BODY BSC_TAB_TPLATE AS
2 /* $Header: BSCUTABB.pls 120.5 2007/06/29 08:30:01 ankgoel ship $ */
3
4
5 --
6 -- Global Variables
7 --
8 G_Tab_Tbl BSC_TEMPLATE.Tab_Tbl_Type;
9 G_Csf_Tbl BSC_TEMPLATE.Csf_Tbl_Type;
10 G_Group_Tbl BSC_TEMPLATE.Group_Tbl_Type;
11 G_Ind_Tbl BSC_TEMPLATE.Ind_Tbl_Type;
12 G_Var_Tbl BSC_TEMPLATE.Var_Tbl_Type;
13 G_Cal_Tbl BSC_TEMPLATE.Cal_Tbl_Type;
14 G_Period_Tbl BSC_TEMPLATE.Period_Tbl_Type;
15 G_Drill_Tbl BSC_TEMPLATE.Drill_Tbl_Type;
16
17 -- Assign default value to global variable
18
19 G_num_of_tabs Number(3) := 3;
20 G_num_of_indicators Number(3) := 13;
21 G_num_of_variables Number(3) := 9;
22 G_num_of_Calculations Number(3) := 12;
23
24 BSC_ERROR Exception;
25
26 l_option_r number(3) := 0;
27 l_drill_ind number(3) := 0;
28 l_panel0_count number(3) := 0;
29 l_panel1_count number(3) := 0;
30 l_panel2_count number(3) := 0;
31 l_debug_stmt varchar2(2000) := 'Debug: ';
32
33 -- User Options
34 TYPE t_array_of_number IS TABLE OF Number(3)
35 INDEX BY BINARY_INTEGER;
36 l_user_options t_array_of_number;
37
38 /*===========================================================================+
39 |
40 | Name: Create_Tab_Template
41 |
42 | Description: To create a tab system layout.
43 | The following are the insertion order of tables:
44 |
45 | 1. MNAV_SYSTEMS
46 | 2. MNAV_INTERMEDIATE_GROUPS
47 | 3. MNAV_INTERMEDIATE_GROUPS_L
48 | 4. MATRIX
49 | 5. MATRIX_INFO
50 | 6. MATRIX_LANGUAGE
51 | 7. MIND_ANALYSIS
52 | 8. MIND_CALCULATIONS
53 | 9. MIND_FIELDS
54 | 40. MIND_DATA
55 | 11. MIND_DATA_SERIE
56 | 12. MIND_DRILLS_CONFIG
57 | 13. MIND_DRILLS
58 | 14. MIND_DRILLS_LANGUAGE
59 | 15. MIND_OPTIONS
60 | 16. MIND_PERIODS
61 | 17. MIND_PERIODS_LANGUAGE
62 | 18. MIND_TABLES_NEW
63 | 19. MNAV_INDICATORS_BY_SYSTEM
64 |
65 +============================================================================*/
66
67
68 Function Create_Tab_Template
69 Return Boolean
70 Is
71 l_top_c BSC_TAB_IND_GROUPS_B.top_position%type;
72 l_left_r BSC_TAB_IND_GROUPS_B.left_position%type;
73 l_height BSC_TAB_IND_GROUPS_B.height%type;
74 l_width BSC_TAB_IND_GROUPS_B.width%type;
75 l_group_ind number;
76
77 l_gl_type BSC_SYS_LINES.line_type%type;
78 l_gl_index_r BSC_SYS_LINES.line_id%type;
79 l_gl_top_c BSC_SYS_LINES.top_position%type;
80 l_gl_left_r BSC_SYS_LINES.left_position%type;
81 l_gl_length BSC_SYS_LINES.length%type;
82 l_gl_arrow BSC_SYS_LINES.arrow%type;
83
84 l_data_code BSC_KPI_ANALYSIS_MEASURES_B.dataset_id%type;
85 l_opt_caption BSC_KPI_ANALYSIS_OPTIONS_TL.name%type;
86 l_opt_lookup BSC_KPI_ANALYSIS_OPTIONS_B.option_id%type;
87
88 l_cursor number;
89 l_ignore number;
90 l_sql_stmt varchar2(32700);
91
92 l_kpidefaults varchar2(2000);
93 level_per number;
94 l_count number := 0;
95 -- 2828685 enhancement
96 h_max_id NUMBER;
97 h_cursor INTEGER;
98 h_ret INTEGER;
99 l_sql VARCHAR2(32000);
100 l_sql_defaults_b VARCHAR2(32000);
101 l_kpi_measure_id NUMBER;
102 x_return_status VARCHAR2(1);
103 x_msg_count NUMBER;
104 x_msg_data VARCHAR2(4000);
105
106 begin
107
108 -- Define tabs
109
110 G_Tab_Tbl(0).Code := 0;
111 G_Tab_Tbl(0).Name := 'Tab 1';
112 G_Tab_Tbl(0).Help := 'Tab 1 Description';
113 G_Tab_Tbl(0).N_groups := 4;
114
115
116 G_Tab_Tbl(1).Code := 1;
117 G_Tab_Tbl(1).Name := 'Tab 2';
118 G_Tab_Tbl(1).Help := 'Tab 2 Description';
119 G_Tab_Tbl(1).N_groups := 4;
120
121 G_Tab_Tbl(2).Code := 2;
122 G_Tab_Tbl(2).Name := 'Tab 3';
123 G_Tab_Tbl(2).Help := 'Tab 3 Description';
124 G_Tab_Tbl(2).N_groups := 1;
125
126 -- Define CSF
127 G_Csf_Tbl(0).Code := 0;
128 G_Csf_Tbl(0).Type := 0;
129 G_Csf_Tbl(0).Inter_Flag:= 0;
130 G_Csf_Tbl(0).Name := 'CSF Default';
131 G_Csf_Tbl(0).Help := 'CSF Default';
132
133
134
135 -- Define groups
136
137 G_Group_Tbl(0).Tab := 0;
138 G_Group_Tbl(0).Code := 0;
139 G_Group_Tbl(0).Name := 'Customer Perspective';
140 G_Group_Tbl(0).Help := 'Customer Perspective';
141
142 G_Group_Tbl(1).Tab := 0;
143 G_Group_Tbl(1).Code := 1;
144 G_Group_Tbl(1).Name := 'Internal Process Perspective';
145 G_Group_Tbl(1).Help := 'Internal Process Perspective';
146
147 G_Group_Tbl(2).Tab := 0;
148 G_Group_Tbl(2).Code := 2;
149 G_Group_Tbl(2).Name := 'Learning and Growth Perspective';
150 G_Group_Tbl(2).Help := 'Learning and Growth Perspective';
151
152 G_Group_Tbl(3).Tab := 0;
153 G_Group_Tbl(3).Code := 3;
154 G_Group_Tbl(3).Name := 'Financial Perspective';
155 G_Group_Tbl(3).Help := 'Financial Perspective';
156
157 -- Define indicator
158
159 G_Ind_Tbl(0).Indicator := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value('BSC_KPIS_B','INDICATOR');
160 G_Ind_Tbl(0).Csf := 0;
161 G_Ind_Tbl(0).Group_r := 0;
162 G_Ind_Tbl(0).Type := 1;
163 G_Ind_Tbl(0).Config := 1;
164 G_Ind_Tbl(0).Periodicity := 5;
165 G_Ind_Tbl(0).Name := 'KPI 1 Customer Perspective';
166 G_Ind_Tbl(0).Help := 'Indicator Help';
167 G_Ind_Tbl(0).Options := 9;
168 G_Ind_Tbl(0).Detail_Flag := 'YES';
169 G_Ind_Tbl(0).Period_Shown := 1;
170 G_Ind_Tbl(0).Tab := 0;
171 G_Ind_Tbl(0).Drills := 1;
172 G_Ind_Tbl(0).User_Options := 5;
173
174
175
176 G_Ind_Tbl(1).Indicator := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value('BSC_KPIS_B','INDICATOR');
177 G_Ind_Tbl(1).Csf := 0;
178 G_Ind_Tbl(1).Group_R := 1;
179 G_Ind_Tbl(1).Type := 1;
180 G_Ind_Tbl(1).Config := 1;
181 G_Ind_Tbl(1).Periodicity := 5;
182 G_Ind_Tbl(1).Name := 'KPI 1 Internal Process';
183 G_Ind_Tbl(1).Help := 'Indicator Help';
184 G_Ind_Tbl(1).Options := 9;
185 G_Ind_Tbl(1).Detail_Flag := 'YES';
186 G_Ind_Tbl(1).Period_Shown := 1;
187 G_Ind_Tbl(1).Tab := 0;
188 G_Ind_Tbl(1).Drills := 1;
189 G_Ind_Tbl(1).User_Options := 5;
190
191 G_Ind_Tbl(2).Indicator := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value('BSC_KPIS_B','INDICATOR');
192 G_Ind_Tbl(2).Csf := 0;
193 G_Ind_Tbl(2).Group_R := 2;
194 G_Ind_Tbl(2).Type := 1;
195 G_Ind_Tbl(2).Config := 1;
196 G_Ind_Tbl(2).Periodicity := 5;
197 G_Ind_Tbl(2).Name := 'KPI 1 Learning';
198 G_Ind_Tbl(2).Help := 'Indicator Help';
199 G_Ind_Tbl(2).Options := 9;
200 G_Ind_Tbl(2).Detail_Flag := 'YES';
201 G_Ind_Tbl(2).Period_Shown := 1;
202 G_Ind_Tbl(2).Tab := 0;
203 G_Ind_Tbl(2).Drills := 1;
204 G_Ind_Tbl(2).User_Options := 0;
205
206 G_Ind_Tbl(3).Indicator := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value('BSC_KPIS_B','INDICATOR');
207 G_Ind_Tbl(3).Csf := 0;
208 G_Ind_Tbl(3).Group_R := 3;
209 G_Ind_Tbl(3).Type := 1;
210 G_Ind_Tbl(3).Config := 1;
211 G_Ind_Tbl(3).Periodicity := 5;
212 G_Ind_Tbl(3).Name := 'KPI 1 Financial';
213 G_Ind_Tbl(3).Help := 'Indicator Help';
214 G_Ind_Tbl(3).Options := 9;
215 G_Ind_Tbl(3).Detail_Flag := 'YES';
216 G_Ind_Tbl(3).Period_Shown := 1;
217 G_Ind_Tbl(3).Tab := 0;
218 G_Ind_Tbl(3).Drills := 1;
219 G_Ind_Tbl(3).User_Options := 0;
220
221 G_Ind_Tbl(4).Indicator := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value('BSC_KPIS_B','INDICATOR');
222 G_Ind_Tbl(4).Csf := 0;
223 G_Ind_Tbl(4).Group_R := 1;
224 G_Ind_Tbl(4).Type := 1;
225 G_Ind_Tbl(4).Config := 1;
226 G_Ind_Tbl(4).Periodicity := 5;
227 G_Ind_Tbl(4).Name := 'KPI 2 Internal Process';
228 G_Ind_Tbl(4).Help := 'Indicator Help';
229 G_Ind_Tbl(4).Options := 9;
230 G_Ind_Tbl(4).Detail_Flag := 'YES';
231 G_Ind_Tbl(4).Period_Shown := 1;
232 G_Ind_Tbl(4).Tab := 0;
233 G_Ind_Tbl(4).Drills := 1;
234 G_Ind_Tbl(4).User_Options := 5;
235
236 G_Ind_Tbl(5).Indicator := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value('BSC_KPIS_B','INDICATOR');
237 G_Ind_Tbl(5).Csf := 0;
238 G_Ind_Tbl(5).Group_R := 1;
239 G_Ind_Tbl(5).Type := 1;
240 G_Ind_Tbl(5).Config := 1;
241 G_Ind_Tbl(5).Periodicity := 5;
242 G_Ind_Tbl(5).Name := 'KPI 3 Internal Process';
243 G_Ind_Tbl(5).Help := 'Indicator Help';
244 G_Ind_Tbl(5).Options := 9;
245 G_Ind_Tbl(5).Detail_Flag := 'YES';
246 G_Ind_Tbl(5).Period_Shown := 1;
247 G_Ind_Tbl(5).Tab := 1;
248 G_Ind_Tbl(5).Drills := 1;
249 G_Ind_Tbl(5).User_Options := 0;
250
251 G_Ind_Tbl(6).Indicator := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value('BSC_KPIS_B','INDICATOR');
252 G_Ind_Tbl(6).Csf := 0;
253 G_Ind_Tbl(6).Group_R := 0;
254 G_Ind_Tbl(6).Type := 1;
255 G_Ind_Tbl(6).Config := 1;
256 G_Ind_Tbl(6).Periodicity := 1;
257 G_Ind_Tbl(6).Name := 'KPI 2 Customer Perspective';
258 G_Ind_Tbl(6).Help := 'Indicator Help';
259 G_Ind_Tbl(6).Options := 9;
260 G_Ind_Tbl(6).Detail_Flag := 'YES';
261 G_Ind_Tbl(6).Period_Shown := 2;
262 G_Ind_Tbl(6).Tab := 0;
263 G_Ind_Tbl(6).Drills := 1;
264 G_Ind_Tbl(6).User_Options := 0;
265
266 G_Ind_Tbl(7).Indicator := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value('BSC_KPIS_B','INDICATOR');
267 G_Ind_Tbl(7).Csf := 0;
268 G_Ind_Tbl(7).Group_R := 0;
269 G_Ind_Tbl(7).Type := 1;
270 G_Ind_Tbl(7).Config := 1;
271 G_Ind_Tbl(7).Periodicity := 5;
272 G_Ind_Tbl(7).Name := 'KPI 3 Customer Perspective';
273 G_Ind_Tbl(7).Help := 'Indicator Help';
274 G_Ind_Tbl(7).Options := 9;
275 G_Ind_Tbl(7).Detail_Flag := 'YES';
276 G_Ind_Tbl(7).Period_Shown := 1;
277 G_Ind_Tbl(7).Tab := 1;
278 G_Ind_Tbl(7).Drills := 1;
279 G_Ind_Tbl(7).User_Options := 0;
280
281 G_Ind_Tbl(8).Indicator := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value('BSC_KPIS_B','INDICATOR');
282 G_Ind_Tbl(8).Csf := 0;
283 G_Ind_Tbl(8).Group_R := 2;
284 G_Ind_Tbl(8).Type := 1;
285 G_Ind_Tbl(8).Config := 1;
286 G_Ind_Tbl(8).Periodicity := 5;
287 G_Ind_Tbl(8).Name := 'KPI 2 Learning';
288 G_Ind_Tbl(8).Help := 'Indicator Help';
289 G_Ind_Tbl(8).Options := 9;
290 G_Ind_Tbl(8).Detail_Flag := 'YES';
291 G_Ind_Tbl(8).Period_Shown := 1;
292 G_Ind_Tbl(8).Tab := 0;
293 G_Ind_Tbl(8).Drills := 1;
294 G_Ind_Tbl(8).User_Options := 0;
295
296 G_Ind_Tbl(9).Indicator := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value('BSC_KPIS_B','INDICATOR');
297 G_Ind_Tbl(9).Csf := 0;
298 G_Ind_Tbl(9).Group_R := 2;
299 G_Ind_Tbl(9).Type := 1;
300 G_Ind_Tbl(9).Config := 1;
301 G_Ind_Tbl(9).Periodicity := 5;
302 G_Ind_Tbl(9).Name := 'KPI 3 Learning';
303 G_Ind_Tbl(9).Help := 'Indicator Help';
304 G_Ind_Tbl(9).Options := 9;
305 G_Ind_Tbl(9).Detail_Flag := 'YES';
306 G_Ind_Tbl(9).Period_Shown := 1;
307 G_Ind_Tbl(9).Tab := 1;
308 G_Ind_Tbl(9).Drills := 1;
309 G_Ind_Tbl(9).User_Options := 0;
310
311 G_Ind_Tbl(10).Indicator := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value('BSC_KPIS_B','INDICATOR');
312 G_Ind_Tbl(10).Csf := 0;
313 G_Ind_Tbl(10).Group_R := 3;
314 G_Ind_Tbl(10).Type := 1;
315 G_Ind_Tbl(10).Config := 1;
316 G_Ind_Tbl(10).Periodicity := 5;
317 G_Ind_Tbl(10).Name := 'KPI 2 Financial';
318 G_Ind_Tbl(10).Help := 'Indicator Help';
319 G_Ind_Tbl(10).Options := 9;
320 G_Ind_Tbl(10).Detail_Flag := 'YES';
321 G_Ind_Tbl(10).Period_Shown := 1;
322 G_Ind_Tbl(10).Tab := 0;
323 G_Ind_Tbl(10).Drills := 1;
324 G_Ind_Tbl(10).User_Options := 0;
325
326 G_Ind_Tbl(11).Indicator := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value('BSC_KPIS_B','INDICATOR');
327 G_Ind_Tbl(11).Csf := 0;
328 G_Ind_Tbl(11).Group_R := 3;
329 G_Ind_Tbl(11).Type := 1;
330 G_Ind_Tbl(11).Config := 1;
331 G_Ind_Tbl(11).Periodicity := 5;
332 G_Ind_Tbl(11).Name := 'KPI 3 Financial';
333 G_Ind_Tbl(11).Help := 'Indicator Help';
334 G_Ind_Tbl(11).Options := 9;
335 G_Ind_Tbl(11).Detail_Flag := 'YES';
336 G_Ind_Tbl(11).Period_Shown := 1;
337 G_Ind_Tbl(11).Tab := 1;
338 G_Ind_Tbl(11).Drills := 1;
339 G_Ind_Tbl(11).User_Options := 0;
340
341 G_Ind_Tbl(12).Indicator := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value('BSC_KPIS_B','INDICATOR');
342 G_Ind_Tbl(12).Csf := 0;
343 G_Ind_Tbl(12).Group_R := 3;
344 G_Ind_Tbl(12).Type := 1;
345 G_Ind_Tbl(12).Config := 3;
346 G_Ind_Tbl(12).Periodicity := 5;
347 G_Ind_Tbl(12).Name := 'Profit and Loss Statement';
348 G_Ind_Tbl(12).Help := 'Indicator Help';
349 G_Ind_Tbl(12).Options := 9;
350 G_Ind_Tbl(12).Detail_Flag := 'YES';
351 G_Ind_Tbl(12).Period_Shown := 1;
352 G_Ind_Tbl(12).Tab := 2;
353 G_Ind_Tbl(12).Drills := 3;
354 G_Ind_Tbl(12).User_Options := 5;
355
356 -- Define variables
357
358 --G_Var_Tbl(8).Code := 'LOCK_INDICATOR'; G_Var_Tbl(8).Value := 1;
359 G_Var_Tbl(0).Code := 'LOCK_INDICATOR'; G_Var_Tbl(0).Value := 1;
360
361 -- Define Calculation options
362
363 G_Cal_Tbl(0).Calculation := 0; G_Cal_Tbl(0).EV0 := 2;
364 G_Cal_Tbl(1).Calculation := 1; G_Cal_Tbl(1).EV0 := 2;
365 G_Cal_Tbl(2).Calculation := 2; G_Cal_Tbl(2).EV0 := 2;
366 G_Cal_Tbl(3).Calculation := 3; G_Cal_Tbl(3).EV0 := 0;
367 G_Cal_Tbl(4).Calculation := 4; G_Cal_Tbl(4).EV0 := 0;
368 G_Cal_Tbl(5).Calculation := 5; G_Cal_Tbl(5).EV0 := 2;
369 G_Cal_Tbl(6).Calculation := 6; G_Cal_Tbl(6).EV0 := 0;
370 G_Cal_Tbl(7).Calculation := 7; G_Cal_Tbl(7).EV0 := 0;
371 G_Cal_Tbl(8).Calculation := 8; G_Cal_Tbl(8).EV0 := 0;
372 G_Cal_Tbl(9).Calculation := 9; G_Cal_Tbl(9).EV0 := 0;
373 G_Cal_Tbl(10).Calculation := 10; G_Cal_Tbl(10).EV0 := 0;
374 G_Cal_Tbl(11).Calculation := 20; G_Cal_Tbl(11).EV0 := 0;
375
376 G_Period_Tbl(0).Order_r := 0;
377 G_Period_Tbl(0).Period_Type := 5;
378 G_Period_Tbl(0).Prev_Year := 0;
379 G_Period_Tbl(0).Num_Years := 0;
380 G_Period_Tbl(0).Viewport_flag := 0;
381 G_Period_Tbl(0).Viewport_Size := 0;
382
383 G_Period_Tbl(1).Order_r := 0;
384 G_Period_Tbl(1).Period_Type := 1;
385 G_Period_Tbl(1).Prev_Year := 1;
386 G_Period_Tbl(1).Num_Years := 2;
387 G_Period_Tbl(1).Viewport_flag := 1;
388 G_Period_Tbl(1).Viewport_Size := 2;
389
390 -- define Drill relation
391
392 G_Drill_Tbl(0).Dim_level_index:= 0;
393 G_Drill_Tbl(0).Table_Name := 'XXX';
394 G_Drill_Tbl(0).Filter_Val := NULL;
395 G_Drill_Tbl(0).Default_val := 'T';
396 G_Drill_Tbl(0).Default_Type := 0;
397 G_Drill_Tbl(0).Value_Order := 0;
398 G_Drill_Tbl(0).Comp_Order := 0;
399 G_Drill_Tbl(0).Level_pk_col := 'XXX';
400 G_Drill_Tbl(0).Parent := NULL;
401 G_Drill_Tbl(0).Parent_Rel := 'XXX';
402 G_Drill_Tbl(0).Table_Rel := NULL;
403 G_Drill_Tbl(0).Parent2 := NULL;
404 G_Drill_Tbl(0).Parent_Rel2 := 'REL';
405 G_Drill_Tbl(0).Status := 0;
406 G_Drill_Tbl(0).Position := 0;
407 G_Drill_Tbl(0).Total0 := NULL;
408 G_Drill_Tbl(0).Ev0 := 0;
409 G_Drill_Tbl(0).Ev1d := 0;
410 G_Drill_Tbl(0).Total := 'T';
411 G_Drill_Tbl(0).Comp := 'C';
412 G_Drill_Tbl(0).name := 'XXX';
413 G_Drill_Tbl(0).Help := 'XXX';
414 G_Drill_Tbl(0).dim_group_id := 0;
415 G_Drill_Tbl(0).dim_group_idx := 0;
416 G_Drill_Tbl(0).dim_level_id := 0;
417 G_Drill_Tbl(0).level_display := 0;
418 G_Drill_Tbl(0).Level_View_Name := 'XXX';
419
420 G_Drill_Tbl(1).Dim_level_index:= 0;
421 G_Drill_Tbl(1).Table_Name := 'BSC_D_TYPE_OF_ACCOUNT';
422 G_Drill_Tbl(1).Filter_Val := 0;
423 G_Drill_Tbl(1).Default_val := 'T';
424 G_Drill_Tbl(1).Default_Type := 0;
425 G_Drill_Tbl(1).Value_Order := 2;
426 G_Drill_Tbl(1).Comp_Order := 0;
427 G_Drill_Tbl(1).Level_pk_col := 'TYP_OF_ACC_CODE';
428 G_Drill_Tbl(1).Parent := NULL;
429 G_Drill_Tbl(1).Parent_Rel := NULL;
430 G_Drill_Tbl(1).Table_Rel := NULL;
431 G_Drill_Tbl(1).Parent2 := NULL;
432 G_Drill_Tbl(1).Parent_Rel2 := NULL;
433 G_Drill_Tbl(1).Status := 2;
434 G_Drill_Tbl(1).Position := 2;
435 G_Drill_Tbl(1).Total0 := 0;
436 G_Drill_Tbl(1).Ev0 := 2;
437 G_Drill_Tbl(1).Ev1d := 2;
438 G_Drill_Tbl(1).Total := 'ALL';
439 G_Drill_Tbl(1).Comp := 'C';
440 G_Drill_Tbl(1).name := 'Account Type';
441 G_Drill_Tbl(1).Help := 'Account Types';
442 G_Drill_Tbl(1).dim_group_id := 3;
443 G_Drill_Tbl(1).dim_group_idx := 1;
444 G_Drill_Tbl(1).dim_level_id := 2;
445 G_Drill_Tbl(1).level_display := 0;
446 G_Drill_Tbl(1).Level_View_Name := 'BSC_D_2_VL';
447
448 G_Drill_Tbl(2).Dim_level_index:= 1;
449 G_Drill_Tbl(2).Table_Name := 'BSC_D_ACCOUNT';
450 G_Drill_Tbl(2).Filter_Val := 0;
451 G_Drill_Tbl(2).Default_val := 'C';
452 G_Drill_Tbl(2).Default_Type := 0;
453 G_Drill_Tbl(2).Value_Order := 1;
454 G_Drill_Tbl(2).Comp_Order := 0;
455 G_Drill_Tbl(2).Level_pk_col := 'ACCOUNT_CODE';
456 G_Drill_Tbl(2).Parent := 0;
457 G_Drill_Tbl(2).Parent_Rel := 'TYP_OF_ACC_CODE';
458 G_Drill_Tbl(2).Table_Rel := NULL;
459 G_Drill_Tbl(2).Parent2 := NULL;
460 G_Drill_Tbl(2).Parent_Rel2 := NULL;
461 G_Drill_Tbl(2).Status := 2;
462 G_Drill_Tbl(2).Position := 2;
463 G_Drill_Tbl(2).Total0 := 0;
464 G_Drill_Tbl(2).Ev0 := 2;
465 G_Drill_Tbl(2).Ev1d := 2;
466 G_Drill_Tbl(2).Total := 'ALL';
467 G_Drill_Tbl(2).Comp := 'COMPARISON';
468 G_Drill_Tbl(2).name := 'Account';
469 G_Drill_Tbl(2).Help := 'Accounts';
470 G_Drill_Tbl(2).dim_group_id := 1;
471 G_Drill_Tbl(2).dim_group_idx := 2;
472 G_Drill_Tbl(2).dim_level_id := 0;
473 G_Drill_Tbl(2).level_display := 2;
474 G_Drill_Tbl(2).Level_View_Name := 'BSC_D_0_VL';
475
476 G_Drill_Tbl(3).Dim_level_index:= 2;
477 G_Drill_Tbl(3).Table_Name := 'BSC_D_SUBACCOUNT';
478 G_Drill_Tbl(3).Filter_Val := 0;
479 G_Drill_Tbl(3).Default_val := 'T';
480 G_Drill_Tbl(3).Default_Type := 0;
481 G_Drill_Tbl(3).Value_Order := 0;
482 G_Drill_Tbl(3).Comp_Order := 0;
483 G_Drill_Tbl(3).Level_pk_col := 'SUBACCOUNT_CODE';
484 G_Drill_Tbl(3).Parent := 1;
485 G_Drill_Tbl(3).Parent_Rel := 'ACCOUNT_CODE';
486 G_Drill_Tbl(3).Table_Rel := NULL;
487 G_Drill_Tbl(3).Parent2 := NULL;
488 G_Drill_Tbl(3).Parent_Rel2 := NULL;
489 G_Drill_Tbl(3).Status := 2;
490 G_Drill_Tbl(3).Position := 0;
491 G_Drill_Tbl(3).Total0 := 0;
492 G_Drill_Tbl(3).Ev0 := 2;
493 G_Drill_Tbl(3).Ev1d := 2;
494 G_Drill_Tbl(3).Total := 'ALL';
495 G_Drill_Tbl(3).Comp := 'COMPARISON';
496 G_Drill_Tbl(3).name := 'SubAccount';
497 G_Drill_Tbl(3).Help := 'SubAccount';
498 G_Drill_Tbl(3).dim_group_id := 2;
499 G_Drill_Tbl(3).dim_group_idx := 3;
500 G_Drill_Tbl(3).dim_level_id := 1;
501 G_Drill_Tbl(3).level_display := 0;
502 G_Drill_Tbl(3).Level_View_Name := 'BSC_D_1_VL';
503
504 -- Define User Options
505 l_user_options(0) :=3;
506 l_user_options(1) :=4;
507 l_user_options(2) :=5;
508 l_user_options(3) :=6;
509 l_user_options(4) :=7;
510
511
512 -- Check records in BSC_SYS_PERIODS_TL for apps
513 Select count(*)
514 Into l_count
515 From BSC_SYS_PERIODS_TL;
516 if (l_count = 0) then
517 INSERT INTO BSC_SYS_PERIODS_TL
518 ( YEAR,
519 PERIODICITY_ID,
520 PERIOD_ID,
521 MONTH,
522 LANGUAGE,
523 SOURCE_LANG,
524 NAME,
525 SHORT_NAME)
526 ( SELECT
527 CA.YEAR,
528 CA.PERIODICITY_ID,
529 CA.PERIOD_ID,
530 1 AS MONTH,
531 L.LANGUAGE_CODE AS LANGUAGE,
532 L.LANGUAGE_CODE AS SOURCE_LANG,
533 CA.NAME,
534 NULL AS SHORT_NAME
535 FROM
536 (SELECT
537 C.YEAR AS YEAR,
538 2 AS PERIODICITY_ID,
539 C.SEMESTER AS PERIOD_ID,
540 C.CALENDAR_MONTH||';'||C2.CALENDAR_MONTH AS NAME
541 FROM
542 BSC_DB_CALENDAR C,
543 BSC_DB_CALENDAR C2
544 WHERE
545 C.YEAR = C2.YEAR AND
546 C.SEMESTER = C2.SEMESTER AND
547 TO_DATE(C.CALENDAR_YEAR||'-'||C.CALENDAR_MONTH||'-'||C.CALENDAR_DAY,'YYYY-MM-DD') =
548 (SELECT
549 MIN(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
550 FROM
551 BSC_DB_CALENDAR C1
552 WHERE
553 C1.YEAR = C.YEAR AND
554 C1.SEMESTER = C.SEMESTER
555 ) AND
556 TO_DATE(C2.CALENDAR_YEAR||'-'||C2.CALENDAR_MONTH||'-'||C2.CALENDAR_DAY,'YYYY-MM-DD') =
557 (SELECT
558 MAX(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
559 FROM
560 BSC_DB_CALENDAR C1
561 WHERE
562 C1.YEAR = C2.YEAR AND
563 C1.SEMESTER = C2.SEMESTER)
564 UNION
565 SELECT
566 C.YEAR AS YEAR,
567 3 AS PERIODICITY_ID,
568 C.QUARTER AS PERIOD_ID,
569 C.CALENDAR_MONTH||';'||C2.CALENDAR_MONTH AS NAME
570 FROM
571 BSC_DB_CALENDAR C,
572 BSC_DB_CALENDAR C2
573 WHERE
574 C.YEAR = C2.YEAR AND
575 C.QUARTER = C2.QUARTER AND
576 TO_DATE(C.CALENDAR_YEAR||'-'||C.CALENDAR_MONTH||'-'||C.CALENDAR_DAY,'YYYY-MM-DD') =
577 (SELECT
578 MIN(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
579 FROM
580 BSC_DB_CALENDAR C1
581 WHERE
582 C1.YEAR = C.YEAR AND
583 C1.QUARTER = C.QUARTER
584 ) AND
585 TO_DATE(C2.CALENDAR_YEAR||'-'||C2.CALENDAR_MONTH||'-'||C2.CALENDAR_DAY,'YYYY-MM-DD') =
586 (SELECT
587 MAX(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
588 FROM
589 BSC_DB_CALENDAR C1
590 WHERE
591 C1.YEAR = C2.YEAR AND
592 C1.QUARTER = C2.QUARTER)
593 UNION
594 SELECT
595 C.YEAR AS YEAR,
596 4 AS PERIODICITY_ID,
597 C.BIMESTER AS PERIOD_ID,
598 C.CALENDAR_MONTH||';'||C2.CALENDAR_MONTH AS NAME
599 FROM
600 BSC_DB_CALENDAR C,
601 BSC_DB_CALENDAR C2
602 WHERE
603 C.YEAR = C2.YEAR AND
604 C.BIMESTER = C2.BIMESTER AND
605 TO_DATE(C.CALENDAR_YEAR||'-'||C.CALENDAR_MONTH||'-'||C.CALENDAR_DAY,'YYYY-MM-DD') =
606 (SELECT
607 MIN(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
608 FROM
609 BSC_DB_CALENDAR C1
610 WHERE
611 C1.YEAR = C.YEAR AND
612 C1.BIMESTER = C.BIMESTER
613 ) AND
614 TO_DATE(C2.CALENDAR_YEAR||'-'||C2.CALENDAR_MONTH||'-'||C2.CALENDAR_DAY,'YYYY-MM-DD') =
615 (SELECT
616 MAX(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
617 FROM
618 BSC_DB_CALENDAR C1
619 WHERE
620 C1.YEAR = C2.YEAR AND
621 C1.BIMESTER = C2.BIMESTER)
622 UNION
623 SELECT
624 C.YEAR AS YEAR,
625 5 AS PERIODICITY_ID,
626 C.MONTH AS PERIOD_ID,
627 TO_CHAR(C.CALENDAR_MONTH) AS NAME
628 FROM
629 BSC_DB_CALENDAR C
630 GROUP BY
631 C.YEAR,
632 C.MONTH,
633 C.CALENDAR_MONTH
634 UNION
635 SELECT
636 C.YEAR AS YEAR,
637 7 AS PERIODICITY_ID,
638 C.WEEK52 AS PERIOD_ID,
639 C.CALENDAR_MONTH||';'||C.CALENDAR_DAY AS NAME
640 FROM
641 BSC_DB_CALENDAR C
642 WHERE
643 TO_DATE(C.CALENDAR_YEAR||'-'||C.CALENDAR_MONTH||'-'||C.CALENDAR_DAY,'YYYY-MM-DD') =
644 (SELECT
645 MIN(TO_DATE(C1.CALENDAR_YEAR||'-'||C1.CALENDAR_MONTH||'-'||C1.CALENDAR_DAY,'YYYY-MM-DD'))
646 FROM
647 BSC_DB_CALENDAR C1
648 WHERE
649 C1.YEAR = C.YEAR AND
650 C1.WEEK52 = C.WEEK52)
651 UNION
652 SELECT
653 C.YEAR AS YEAR,
654 9 AS PERIODICITY_ID,
655 C.DAY365 AS PERIOD_ID,
656 C.CALENDAR_MONTH||';'||C.CALENDAR_DAY AS NAME
657 FROM
658 BSC_DB_CALENDAR C) CA,
659 FND_LANGUAGES L
660 WHERE
661 L.INSTALLED_FLAG <> 'D');
662 end if;
663 -- finish inserting BSC_SYS_PERIODS_TL
664
665 -- create dimensions
666 --DBMS_OUTPUT.PUT_LINE('create dimensions------------------------------');
667 if (NOT BSC_DIM_TPLATE.Create_Dimensions) then
668 l_debug_stmt := bsc_apps.get_message('BSC_ERROR_CREATE_DIM');
669 Raise BSC_ERROR;
670 end if;
671
672 -- create analysis options
673 --DBMS_OUTPUT.PUT_LINE('create analysis options------------------------------');
674
675 if (NOT BSC_AOP_TPLATE.Create_Analysis_Options) then
676 l_debug_stmt := bsc_apps.get_message('BSC_ERROR_CREATE_AO');
677 Raise BSC_ERROR;
678 end if;
679
680 --DBMS_OUTPUT.PUT_LINE('end create analysis options------------------------------');
681 --UPDATE BSC_SYS_INIT
682
683 Update BSC_SYS_INIT
684 Set PROPERTY_VALUE = '1',LAST_UPDATED_BY =2,LAST_UPDATE_DATE=SYSDATE
685 Where PROPERTY_CODE = 'SHOW_TABS';
686
687 -- Defining TAB ....
688
689 For i_system In 0 .. (G_num_of_tabs -1)
690 Loop
691
692 l_debug_stmt := 'Inserting BSC_TABS_B, i_system = ' ||
693 to_char(i_system);
694 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
695
696 Insert Into BSC_TABS_B(
697 TAB_ID,KPI_MODEL,BSC_MODEL,
698 CROSS_MODEL,DEFAULT_MODEL,
699 ZOOM_FACTOR,
700 CREATED_BY,CREATION_DATE,
701 LAST_UPDATED_BY,LAST_UPDATE_DATE,
702 LAST_UPDATE_LOGIN
703 )
704 Values (
705 G_Tab_Tbl(i_system).code,
706 1,
707 0,
708 0,
709 0,
710 0.8227025,
711 0,SYSDATE,
712 0,SYSDATE,
713 0
714 );
715
716 l_debug_stmt := 'Inserting BSC_TABS_TL, i_system = ' ||
717 to_char(i_system);
718 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
719
720 l_sql_stmt := 'INSERT INTO BSC_TABS_TL '||
721 ' (TAB_ID,LANGUAGE,SOURCE_LANG,NAME,HELP) ' ||
722 'SELECT '||
723 G_Tab_Tbl(i_system).Code||' AS TAB_ID, '||
724 'FEM.LANGUAGE AS LANGUAGE, '||
725 'FEM.SOURCE_LANG AS SOURCE_LANG, '||
726 'SUBSTR(FEM.MEANING,1,35) AS NAME, '||
727 'SUBSTR(FEM.MEANING,1,40)||'' ''||SUBSTR(FEM_DESC.MEANING,1,40) AS HELP '||
728 'FROM '||
729 BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM, '||
730 BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_DESC '||
731 'WHERE '||
732 'FEM.LOOKUP_TYPE = ''BSC_TPLATE_TAB_NAME'' AND '||
733 'FEM.LOOKUP_CODE = :1 AND '||
734 'FEM_DESC.LOOKUP_TYPE = ''BSC_UI_COMMON'' AND '||
735 'FEM_DESC.LOOKUP_CODE = ''DESCRIPTION'' AND '||
736 'FEM.LANGUAGE = FEM_DESC.LANGUAGE';
737
738 EXECUTE IMMEDIATE l_sql_stmt USING G_Tab_Tbl(i_system).Code; --literals bug fix
739
740 l_debug_stmt := 'Inserting BSC_TAB_CSF_B, i_system = ' ||
741 to_char(i_system);
742 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
743
744
745 Insert Into BSC_TAB_CSF_B(
746 TAB_ID,
747 CSF_ID,
748 CSF_TYPE,
749 INTERMEDIATE_FLAG
750 )
751 Values (
752 G_Tab_Tbl(i_system).Code,
753 G_Csf_Tbl(0).Code,
754 G_Csf_Tbl(0).Type,
755 G_Csf_Tbl(0).Inter_Flag
756 );
757
758 l_debug_stmt := 'Inserting BSC_TAB_CSF_TL, i_system = ' ||
759 to_char(i_system);
760 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
761
762 l_sql_stmt := 'INSERT INTO BSC_TAB_CSF_TL '||
763 ' (TAB_ID,CSF_ID,LANGUAGE,SOURCE_LANG,NAME,HELP) ' ||
764 'SELECT '||
765 G_Tab_Tbl(i_system).Code||' AS TAB_ID, '||
766 G_Csf_Tbl(0).Code||' AS CSF_ID, '||
767 'FEM.LANGUAGE AS LANGUAGE, '||
768 'FEM.SOURCE_LANG AS SOURCE_LANG, '||
769 'SUBSTR(FEM.MEANING,1,30) AS NAME, '||
770 'SUBSTR(FEM.MEANING,1,80) AS HELP '||
771 'FROM '||
772 BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM '||
773 'WHERE '||
774 'FEM.LOOKUP_TYPE = ''BSC_TPLATE_TAB_CSF'' AND '||
775 'FEM.LOOKUP_CODE = :1 ';
776
777 EXECUTE IMMEDIATE l_sql_stmt USING G_Csf_Tbl(0).Code; --literals bug fix
778
779 --DBMS_OUTPUT.PUT_LINE('Looping j_group ...');
780
781 For j_group In 0 .. (G_Tab_Tbl(i_system).n_groups -1)
782 Loop
783
784 if (i_system = 0) then
785 if (j_group = 0) then
786 l_top_c := 1037; l_left_r := 2001;
787 l_height := 528; l_width := 2283;
788 --
789 l_gl_type := 'H'; l_gl_index_r := 0;
790 l_gl_top_c := 2563; l_gl_left_r := 3858;
791 l_gl_length := 1378;
792 elsif (j_group = 1) then
793 l_top_c := 2308; l_left_r := 1978;
794 l_height := 528; l_width := 2283;
795 --
796 l_gl_type := 'H'; l_gl_index_r := 1;
797 l_gl_top_c := 1270; l_gl_left_r := 3656;
798 l_gl_length := 1063;
799 elsif (j_group = 2) then
800 l_top_c := 3694; l_left_r := 1981;
801 l_height := 512; l_width := 2283;
802 --
803 l_gl_type := 'H'; l_gl_index_r := 2;
804 l_gl_top_c := 3956; l_gl_left_r := 3553;
805 l_gl_length := 1168;
806 elsif (j_group = 3) then
807 l_top_c := 2328; l_left_r := 6886;
808 l_height := 546; l_width := 2088;
809 --
810 l_gl_type := 'V'; l_gl_index_r := 0;
811 l_gl_top_c := 1280; l_gl_left_r := 4696;
812 l_gl_length := 2692;
813 end if;
814 elsif (i_system = 1) then
815 if (j_group = 0) then
816 l_top_c := 798; l_left_r := 2006;
817 l_height := 516; l_width := 2408;
818 --
819 l_gl_type := 'H'; l_gl_index_r := 0;
820 l_gl_top_c := 978; l_gl_left_r := 3923;
821 l_gl_length := 838;
822 elsif (j_group = 1) then
823 l_top_c := 3531; l_left_r := 1981;
824 l_height := 546; l_width := 2418;
825 --
826 l_gl_type := 'H'; l_gl_index_r := 1;
827 l_gl_top_c := 2488; l_gl_left_r := 4038;
828 l_gl_length := 1228;
829 elsif (j_group = 2) then
830 l_top_c := 2164; l_left_r := 2008;
831 l_height := 531; l_width := 2418;
832 --
833 l_gl_type := 'H'; l_gl_index_r := 2;
834 l_gl_top_c := 3813; l_gl_left_r := 3941;
835 l_gl_length := 808;
836 elsif (j_group = 3) then
837 l_top_c := 2163; l_left_r := 6858;
838 l_height := 546; l_width := 2223;
839 --
840 l_gl_type := 'V'; l_gl_index_r := 0;
841 l_gl_top_c := 978; l_gl_left_r := 4728;
842 l_gl_length := 2861;
843 end if;
844 elsif (i_system = 2) then
845 if (j_group = 0) then
846 l_top_c := 827; l_left_r := 1871;
847 l_height := 909; l_width := 2088;
848 --
849 l_gl_type := NULL; l_gl_index_r := 0;
850 l_gl_top_c := 0; l_gl_left_r := 0;
851 l_gl_length := 0;
852 end if;
853 end if;
854
855 if (i_system = 2) and (j_group = 0) then
856 l_group_ind := 3;
857 else
858 l_group_ind := j_group;
859 end if;
860
861 l_debug_stmt := 'Inserting BSC_TAB_IND_GROUPS_B, i_system=' ||
862 to_char(i_system) || ', j_group=' || to_char(j_group) ||
863 ', l_group_ind=' || to_char(l_group_ind);
864 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
865
866 Insert Into BSC_TAB_IND_GROUPS_B(
867 TAB_ID,
868 CSF_ID,
869 IND_GROUP_ID,
870 GROUP_TYPE,
871 NAME_POSITION,
872 NAME_JUSTIFICATION,
873 LEFT_POSITION,
874 TOP_POSITION,
875 WIDTH,
876 HEIGHT)
877 Values (
878 i_system, -- system_c
879 0,
880 G_Group_Tbl(l_group_ind).Code, -- code
881 0,
882 1,
883 0,
884 l_left_r, -- left_r
885 l_top_c, -- top_c
886 l_width, -- width
887 l_height -- height
888 );
889
890 l_debug_stmt := 'Inserting BSC_TAB_IND_GROUPS_TL, i_system=' ||
891 to_char(i_system) || ', j_group=' || to_char(j_group) ||
892 ', l_group_ind=' || to_char(l_group_ind);
893 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
894
895
896 l_sql_stmt := 'INSERT INTO BSC_TAB_IND_GROUPS_TL '||
897 ' (TAB_ID,CSF_ID,IND_GROUP_ID,LANGUAGE,SOURCE_LANG,NAME,HELP) ' ||
898 'SELECT '||
899 i_system||' AS TAB_ID, '||
900 '0 AS CSF_ID, '||
901 G_Group_Tbl(l_group_ind).Code||' AS IND_GROUP_ID, '||
902 'FEM.LANGUAGE AS LANGUAGE, '||
903 'FEM.SOURCE_LANG AS SOURCE_LANG, '||
904 'SUBSTR(FEM.MEANING,1,50) AS NAME, '||
905 'SUBSTR(FEM.MEANING,1,80) AS HELP '||
906 'FROM '||
907 BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM '||
908 'WHERE '||
909 'FEM.LOOKUP_TYPE = ''BSC_TPLATE_TAB_IND_GROUPS'' AND '||
910 'FEM.LOOKUP_CODE = :1 ';
911
912 EXECUTE IMMEDIATE l_sql_stmt USING G_Group_Tbl(l_group_ind).Code; --literals bug fix
913
914 -- insert lines among groups, if tab has only one group then
915 -- there is no line.
916
917 if (G_Tab_Tbl(i_system).n_groups <> 1) then
918
919 l_debug_stmt := 'Inserting BSC_SYS_LINES, i_system=' ||
920 to_char(i_system) || ', j_group=' || to_char(j_group);
921 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
922
923 Insert Into BSC_SYS_LINES (
924 SOURCE_TYPE,
925 SOURCE_CODE,
926 LINE_TYPE,
927 LINE_ID,
928 LEFT_POSITION,
929 TOP_POSITION,
930 LENGTH,
931 ARROW)
932 Values (
933 1,
934 i_system, -- system_c
935 l_gl_type, -- type
936 l_gl_index_r, -- index_r
937 l_gl_left_r, -- left_r
938 l_gl_top_c, -- top_c
939 l_gl_length, -- length
940 NULL -- arrow
941 );
942
943 end if;
944 End loop; -- group loop
945
946 End Loop; -- system loop
947
948 --DBMS_OUTPUT.PUT_LINE('Looping k_indicator ...');
949
950 For k_indicator In 0 .. (G_num_of_indicators -1)
951 Loop
952
953 l_debug_stmt := 'Inserting BSC_KPIS_B, indicator=' ||
954 to_char(G_Ind_Tbl(k_indicator).Indicator) ||
955 ', k_indicator=' || to_char(k_indicator);
956 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
957
958 Insert Into BSC_KPIS_B (
959 INDICATOR,
960 CSF_ID,
961 IND_GROUP_ID,
962 DISP_ORDER,
963 PROTOTYPE_FLAG ,
964 INDICATOR_TYPE ,
965 CONFIG_TYPE ,
966 PERIODICITY_ID,
967 BM_GROUP_ID,
968 APPLY_COLOR_FLAG,
969 PROTOTYPE_COLOR,
970 SHARE_FLAG,
971 PUBLISH_FLAG,
972 CREATED_BY,CREATION_DATE,
973 LAST_UPDATED_BY,LAST_UPDATE_DATE,
974 LAST_UPDATE_LOGIN,
975 EDW_FLAG,
976 CALENDAR_ID,
977 COLOR_ROLLUP_TYPE,
978 PROTOTYPE_COLOR_ID,
979 WEIGHTED_COLOR_METHOD
980 )
981 Values (
982 G_Ind_Tbl(k_indicator).Indicator, -- indicator
983 G_Ind_Tbl(k_indicator).Csf, -- intermediate_r
984 G_Ind_Tbl(k_indicator).Group_r, -- group_r
985 0, -- position
986 1, -- prototype
987 G_Ind_Tbl(k_indicator).type, -- indicator_type
988 G_Ind_Tbl(k_indicator).config, -- configuration
989 G_Ind_Tbl(k_indicator).Periodicity, -- panel_periodicity
990 1,
991 1,
992 'G',
993 1,
994 1,
995 0,SYSDATE,
996 0,SYSDATE,
997 0,
998 0,
999 1,
1000 'DEFAULT_KPI',
1001 24865,
1002 NULL
1003 );
1004
1005 l_debug_stmt := 'Inserting BSC_KPI_DEFAULTS_B, indicator=' ||
1006 to_char(G_Ind_Tbl(k_indicator).Indicator) ||
1007 ', k_indicator=' || to_char(k_indicator);
1008 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
1009
1010 if (G_Ind_Tbl(k_indicator).Indicator = 3013) then
1011 l_opt_caption := 'Amount';
1012 else
1013 l_opt_caption := 'Option 0';
1014 end if;
1015
1016 l_sql_defaults_b:= 'Insert Into BSC_KPI_DEFAULTS_B ('||
1017 'TAB_ID,INDICATOR,FORMAT_MASK,COLOR_METHOD,'||
1018 'DIM_SET_ID,DIM_LEVEL1_VALUE,DIM_LEVEL2_VALUE,DIM_LEVEL3_VALUE,DIM_LEVEL4_VALUE,'||
1019 'DIM_LEVEL5_VALUE,DIM_LEVEL6_VALUE,DIM_LEVEL7_VALUE,DIM_LEVEL8_VALUE,'||
1020 'LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN)'||
1021 'Values (:1,:2,'||
1022 '''#,###,##0'''||
1023 ',1,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,SYSDATE,0,SYSDATE,0,NULL)';
1024 EXECUTE IMMEDIATE l_sql_defaults_b USING G_Ind_Tbl(k_indicator).Tab,G_Ind_Tbl(k_indicator).Indicator;
1025
1026 l_debug_stmt := 'Inserting BSC_KPI_DEFAULTS_TL, indicator=' ||
1027 to_char(G_Ind_Tbl(k_indicator).Indicator) ||
1028 ', k_indicator=' || to_char(k_indicator);
1029 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
1030
1031 if (G_Ind_Tbl(k_indicator).Indicator <> 3013) then
1032
1033 l_sql_stmt := 'INSERT INTO BSC_KPI_DEFAULTS_TL
1034 (TAB_ID,INDICATOR,LANGUAGE,SOURCE_LANG, ANALYSIS_OPTION0_NAME, ANALYSIS_OPTION1_NAME,
1035 ANALYSIS_OPTION2_NAME,PERIOD_NAME,SERIES_NAME,
1036 DIM_LEVEL1_NAME,DIM_LEVEL2_NAME,DIM_LEVEL3_NAME,DIM_LEVEL4_NAME,
1037 DIM_LEVEL5_NAME,DIM_LEVEL6_NAME,DIM_LEVEL7_NAME,DIM_LEVEL8_NAME,
1038 DIM_LEVEL1_TEXT,DIM_LEVEL2_TEXT,DIM_LEVEL3_TEXT,DIM_LEVEL4_TEXT,
1039 DIM_LEVEL5_TEXT,DIM_LEVEL6_TEXT,DIM_LEVEL7_TEXT,DIM_LEVEL8_TEXT)
1040 SELECT '||G_Ind_Tbl(k_indicator).Tab||' AS TAB_ID,'
1041 ||G_Ind_Tbl(k_indicator).Indicator||' AS INDICATOR,
1042 FEM.LANGUAGE AS LANGUAGE,
1043 FEM.SOURCE_LANG AS SOURCE_LANG,
1044 SUBSTR(FEM.MEANING,1,30) AS ANALYSIS_OPTION0_NAME,
1045 NULL AS ANALYSIS_OPTION1_NAME,
1046 NULL AS ANALYSIS_OPTION2_NAME,
1047 NULL AS PERIOD_NAME,
1048 SUBSTR(FEM_DS.MEANING,1,30) AS SERIES_NAME,
1049 NULL AS DIM_LEVEL1_NAME,NULL AS DIM_LEVEL2_NAME,NULL AS DIM_LEVEL3_NAME,NULL AS DIM_LEVEL4_NAME,
1050 NULL AS DIM_LEVEL5_NAME,NULL AS DIM_LEVEL6_NAME,NULL AS DIM_LEVEL7_NAME,NULL AS DIM_LEVEL8_NAME,
1051 NULL AS DIM_LEVEL1_TEXT,NULL AS DIM_LEVEL2_TEXT,NULL AS DIM_LEVEL3_TEXT,NULL AS DIM_LEVEL4_TEXT,
1052 NULL AS DIM_LEVEL5_TEXT,NULL AS DIM_LEVEL6_TEXT,NULL AS DIM_LEVEL7_TEXT,NULL AS DIM_LEVEL8_TEXT
1053 FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM, '
1054 ||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_DS
1055 WHERE FEM.LOOKUP_TYPE =''BSC_TPLATE_TAB_AO_NAMES'' AND
1056 FEM.LOOKUP_CODE = ''0'' AND
1057 FEM_DS.LOOKUP_TYPE =''BSC_TPLATE_TAB_DSERIES_NAME'' AND
1058 FEM_DS.LOOKUP_CODE = ''0'' AND
1059 FEM.LANGUAGE = FEM_DS.LANGUAGE';
1060
1061 l_debug_stmt := SUBSTR(l_sql_stmt,1,2000);
1062 l_cursor := DBMS_SQL.Open_Cursor;
1063 DBMS_SQL.Parse(l_cursor, l_sql_stmt, DBMS_SQL.native);
1064 l_ignore := DBMS_SQL.Execute(l_cursor);
1065 DBMS_SQL.Close_Cursor(l_cursor);
1066
1067 else
1068
1069 l_sql_stmt := ' INSERT INTO BSC_KPI_DEFAULTS_TL
1070 (TAB_ID,INDICATOR,LANGUAGE,SOURCE_LANG,
1071 ANALYSIS_OPTION0_NAME,ANALYSIS_OPTION1_NAME,
1072 ANALYSIS_OPTION2_NAME,
1073 PERIOD_NAME,SERIES_NAME,
1074 DIM_LEVEL1_NAME,DIM_LEVEL2_NAME,DIM_LEVEL3_NAME,DIM_LEVEL4_NAME,
1075 DIM_LEVEL5_NAME,DIM_LEVEL6_NAME,DIM_LEVEL7_NAME,DIM_LEVEL8_NAME,
1076 DIM_LEVEL1_TEXT,DIM_LEVEL2_TEXT,DIM_LEVEL3_TEXT,DIM_LEVEL4_TEXT,
1077 DIM_LEVEL5_TEXT,DIM_LEVEL6_TEXT,DIM_LEVEL7_TEXT,DIM_LEVEL8_TEXT)
1078 SELECT '
1079 ||G_Ind_Tbl(k_indicator).Tab||' AS TAB_ID,'
1080 ||G_Ind_Tbl(k_indicator).Indicator||' AS INDICATOR,
1081 FEM.LANGUAGE AS LANGUAGE,
1082 FEM.SOURCE_LANG AS SOURCE_LANG,
1083 SUBSTR(FEM.MEANING,1,30) AS ANALYSIS_OPTION0_NAME,
1084 NULL AS ANALYSIS_OPTION1_NAME,
1085 NULL AS ANALYSIS_OPTION2_NAME,
1086 NULL AS PERIOD_NAME,
1087 SUBSTR(FEM_DS.MEANING,1,30) AS SERIES_NAME,
1088 SUBSTR(FEM_ATYPE.MEANING,1,80) AS DIM_LEVEL1_NAME,
1089 SUBSTR(FEM_ACCOUNT.MEANING,1,80) AS DIM_LEVEL2_NAME,
1090 SUBSTR(FEM_SUBACCOUNT.MEANING,1,80) AS DIM_LEVEL3_NAME,NULL AS DIM_LEVEL4_NAME,
1091 NULL AS DIM_LEVEL5_NAME,NULL AS DIM_LEVEL6_NAME,NULL AS DIM_LEVEL7_NAME,NULL AS DIM_LEVEL8_NAME,
1092 NULL AS DIM_LEVEL1_TEXT,NULL AS DIM_LEVEL2_TEXT,NULL AS DIM_LEVEL3_TEXT,NULL AS DIM_LEVEL4_TEXT,
1093 NULL AS DIM_LEVEL5_TEXT,NULL AS DIM_LEVEL6_TEXT,NULL AS DIM_LEVEL7_TEXT,NULL AS DIM_LEVEL8_TEXT
1094 FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM, '
1095 ||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_DS, '
1096 ||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_ATYPE, '
1097 ||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_ACCOUNT, '
1098 ||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_SUBACCOUNT
1099 WHERE FEM.LOOKUP_TYPE =''BSC_TPLATE_TAB_AO_NAMES'' AND
1100 FEM.LOOKUP_CODE = ''1'' AND
1101 FEM_DS.LOOKUP_TYPE =''BSC_TPLATE_TAB_DSERIES_NAME'' AND
1102 FEM_DS.LOOKUP_CODE = ''0'' AND
1103 FEM_ATYPE.LOOKUP_TYPE =''BSC_TPLATE_TAB_DIM_LEVEL_NAME'' AND
1104 FEM_ATYPE.LOOKUP_CODE = ''2'' AND
1105 FEM_ACCOUNT.LOOKUP_TYPE =''BSC_TPLATE_TAB_DIM_LEVEL_NAME'' AND
1106 FEM_ACCOUNT.LOOKUP_CODE = ''0'' AND
1107 FEM_SUBACCOUNT.LOOKUP_TYPE =''BSC_TPLATE_TAB_DIM_LEVEL_NAME'' AND
1108 FEM_SUBACCOUNT.LOOKUP_CODE = ''1'' AND
1109 FEM.LANGUAGE = FEM_DS.LANGUAGE AND
1110 FEM_DS.LANGUAGE = FEM_ATYPE.LANGUAGE AND
1111 FEM_ATYPE.LANGUAGE = FEM_ACCOUNT.LANGUAGE AND
1112 FEM_SUBACCOUNT.LANGUAGE = FEM_ACCOUNT.LANGUAGE';
1113
1114 l_debug_stmt := SUBSTR(l_sql_stmt,1,2000);
1115 l_cursor := DBMS_SQL.Open_Cursor;
1116 DBMS_SQL.Parse(l_cursor, l_sql_stmt, DBMS_SQL.native);
1117 l_ignore := DBMS_SQL.Execute(l_cursor);
1118 DBMS_SQL.Close_Cursor(l_cursor);
1119
1120 end if;
1121
1122
1123
1124 --DBMS_OUTPUT.PUT_LINE('Looping l_variable ...');
1125
1126 For l_variable In 0 .. (G_Ind_Tbl(k_indicator).Options -1)
1127 Loop
1128
1129 l_debug_stmt := 'Inserting BSC_KPI_PROPERTIES, k_indicator=' ||
1130 to_char(k_indicator) ||
1131 ', l_variable=' || to_char(l_variable) ||
1132 ', PROPERTY_CODE=' || G_Var_Tbl(l_variable).Code ||
1133 ', PROPERTY_VALUE=' || to_char(G_Var_Tbl(l_variable).Value);
1134 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
1135
1136 Insert Into BSC_KPI_PROPERTIES (
1137 INDICATOR,
1138 PROPERTY_CODE,
1139 PROPERTY_VALUE,
1140 SECONDARY_VALUE )
1141 Values (
1142 G_Ind_Tbl(k_indicator).Indicator,
1143 G_Var_Tbl(l_variable).code, -- variable
1144 G_Var_Tbl(l_variable).Value, -- value_r
1145 NULL -- secondary_value
1146 );
1147 End loop;
1148
1149 -- the Detail_Flag should be always set to 'YES'
1150
1151 if (G_Ind_Tbl(k_indicator).Detail_Flag ='YES') then
1152
1153 l_debug_stmt := 'Inserting BSC_KPIS_TL, k_indicator=' ||
1154 to_char(k_indicator) ||
1155 ', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
1156 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
1157
1158
1159 l_sql_stmt := 'INSERT INTO BSC_KPIS_TL
1160 (INDICATOR,LANGUAGE,SOURCE_LANG,NAME,HELP)
1161 SELECT '||G_Ind_Tbl(k_indicator).Indicator|| ' AS INDICATOR,
1162 FEM.LANGUAGE AS LANGUAGE,FEM.SOURCE_LANG AS SOURCE_LANG,
1163 SUBSTR(FEM.MEANING,1,50) AS NAME,
1164 SUBSTR(FEM.MEANING,1,25)|| '' '' ||SUBSTR(FEM_DESC.MEANING,1,25) AS HELP
1165 FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM, '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_DESC
1166 WHERE FEM.LOOKUP_TYPE =''BSC_TPLATE_TAB_KPIS'' AND
1167 FEM.LOOKUP_CODE = :1 AND
1168 FEM_DESC.LOOKUP_TYPE = :2 AND
1169 FEM_DESC.LOOKUP_CODE = :3 AND
1170 FEM.LANGUAGE = FEM_DESC.LANGUAGE';
1171
1172 l_debug_stmt := l_sql_stmt;
1173
1174 EXECUTE IMMEDIATE l_sql_stmt USING to_char(G_Ind_Tbl(k_indicator).Indicator) ,'BSC_UI_COMMON', 'DESCRIPTION';
1175
1176 l_debug_stmt := 'Inserting BSC_KPI_ANALYSIS_GROUPS, Indicator=' ||
1177 to_char(G_Ind_Tbl(k_indicator).Indicator) ||
1178 ', k_indicator=' || to_char(k_indicator);
1179 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
1180
1181 Insert Into BSC_KPI_ANALYSIS_GROUPS (
1182 INDICATOR,
1183 ANALYSIS_GROUP_ID,
1184 NUM_OF_OPTIONS,
1185 DEPENDENCY_FLAG,
1186 PARENT_ANALYSIS_ID,
1187 CHANGE_DIM_SET,
1188 DEFAULT_VALUE )
1189 Values (
1190 G_Ind_Tbl(k_indicator).Indicator,
1191 0, -- analysis
1192 1, -- number_of_options
1193 0, -- dependency
1194 0, -- parent
1195 NULL, -- changes_drill
1196 0 -- default_value
1197 );
1198
1199 l_sql := 'Insert Into BSC_KPI_CALCULATIONS '||
1200 '(INDICATOR, CALCULATION_ID, USER_LEVEL0,'||
1201 'USER_LEVEL1,USER_LEVEL1_DEFAULT, USER_LEVEL2,'||
1202 'USER_LEVEL2_DEFAULT, DEFAULT_VALUE ) values('||
1203 ':1,:2,:3,:4,null,null,null,0 )';
1204
1205 For m_calculation In 0 .. (G_num_of_calculations - 1) Loop
1206
1207 l_debug_stmt := 'Inserting BSC_KPI_CALCULATIONS' ||
1208 ', k_indicator=' || to_char(k_indicator) ||
1209 ', m_calculation=' || to_char(m_calculation) ||
1210 ', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
1211 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
1212 EXECUTE IMMEDIATE l_sql USING G_Ind_Tbl(k_indicator).Indicator,G_Cal_Tbl(m_calculation).Calculation,G_Cal_Tbl(m_calculation).EV0,G_Cal_Tbl(m_calculation).EV0;
1213
1214 End loop; -- calculation loop
1215 -- Vertical Analysis for PL
1216 if (G_Ind_Tbl(k_indicator).Indicator = 3013) then
1217 Insert Into BSC_KPI_CALCULATIONS (
1218 INDICATOR,
1219 CALCULATION_ID,
1220 USER_LEVEL0,
1221 USER_LEVEL1,
1222 USER_LEVEL1_DEFAULT,
1223 USER_LEVEL2,
1224 USER_LEVEL2_DEFAULT,
1225 DEFAULT_VALUE )
1226 Values (
1227 G_Ind_Tbl(k_indicator).Indicator,
1228 11, -- calculation
1229 2, -- ev0
1230 2, -- ev1
1231 NULL, -- ev1d
1232 NULL, -- ev2
1233 NULL, -- ev2d
1234 0 -- value_r
1235 );
1236 end if;
1237
1238 if (G_Ind_Tbl(k_indicator).Indicator = 3013) then
1239 l_data_code := 1;
1240 else
1241 l_data_code := -1;
1242 end if;
1243
1244 SELECT bsc_kpi_measure_s.NEXTVAL INTO l_kpi_measure_id from dual;
1245
1246 l_debug_stmt := 'Inserting BSC_KPI_ANALYSIS_MEASURES_B' ||
1247 ', k_indicator=' || to_char(k_indicator) ||
1248 ', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
1249 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
1250
1251 Insert Into BSC_KPI_ANALYSIS_MEASURES_B (
1252 INDICATOR,
1253 ANALYSIS_OPTION0,
1254 ANALYSIS_OPTION1,
1255 ANALYSIS_OPTION2,
1256 SERIES_ID,
1257 DATASET_ID,
1258 AXIS,
1259 SERIES_TYPE,
1260 STACK_SERIES_ID,
1261 BM_FLAG,
1262 BUDGET_FLAG,
1263 DEFAULT_VALUE,
1264 SERIES_COLOR,
1265 BM_COLOR,
1266 KPI_MEASURE_ID)
1267 Values (
1268 G_Ind_Tbl(k_indicator).Indicator,
1269 0, -- analysis_option0
1270 0, -- analysis_option1
1271 0, -- analysis_option2
1272 0, -- serie
1273 l_data_code, -- data_code
1274 1, -- axis
1275 1, -- series_type
1276 NULL, -- stack
1277 1, -- reference
1278 1, -- plan_series
1279 1, -- default_r,
1280 10053171,
1281 10053171,
1282 l_kpi_measure_id
1283 );
1284
1285 l_debug_stmt := 'Inserting BSC_KPI_ANALYSIS_MEASURES_TL' ||
1286 ', k_indicator=' || to_char(k_indicator) ||
1287 ', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
1288 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
1289
1290 l_sql_stmt := 'INSERT INTO BSC_KPI_ANALYSIS_MEASURES_TL
1291 (INDICATOR,ANALYSIS_OPTION0,ANALYSIS_OPTION1,ANALYSIS_OPTION2,
1292 SERIES_ID,LANGUAGE,SOURCE_LANG,NAME,HELP)
1293 SELECT '
1294 ||G_Ind_Tbl(k_indicator).Indicator||' AS INDICATOR,
1295 0 AS ANALYSIS_OPTION0,
1296 0 AS ANALYSIS_OPTION1,
1297 0 AS ANALYSIS_OPTION2,
1298 0 AS SERIES_ID,
1299 FEM.LANGUAGE AS LANGUAGE,
1300 FEM.SOURCE_LANG AS SOURCE_LANG,
1301 SUBSTR(FEM.MEANING,1,20) AS NAME,
1302 SUBSTR(FEM.MEANING,1,80) AS HELP
1303 FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM
1304 WHERE FEM.LOOKUP_TYPE =''BSC_TPLATE_TAB_DSERIES_NAME'' AND
1305 FEM.LOOKUP_CODE = ''0''';
1306
1307 l_debug_stmt := l_sql_stmt;
1308 l_cursor := DBMS_SQL.Open_Cursor;
1309 DBMS_SQL.Parse(l_cursor, l_sql_stmt, DBMS_SQL.native);
1310 l_ignore := DBMS_SQL.Execute(l_cursor);
1311 DBMS_SQL.Close_Cursor(l_cursor);
1312
1313 l_debug_stmt := 'Inserting BSC_KPI_MEASURE_PROPS' ||
1314 ', k_indicator=' || to_char(k_indicator) ||
1315 ', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
1316
1317 -- Insert KPI Measure Properties
1318 BSC_KPI_MEASURE_PROPS_PUB.Create_Default_Kpi_Meas_Props (
1319 p_commit => FND_API.G_FALSE
1320 , p_objective_id => G_Ind_Tbl(k_indicator).Indicator
1321 , p_kpi_measure_id => l_kpi_measure_id
1322 , p_cascade_shared => FALSE
1323 , x_return_status => x_return_status
1324 , x_msg_count => x_msg_count
1325 , x_msg_data => x_msg_data
1326 );
1327 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1328 RAISE FND_API.G_EXC_ERROR;
1329 END IF;
1330
1331 l_debug_stmt := 'Inserting BSC_COLOR_TYPE_PROPS and BSC_COLOR_RANGES' ||
1332 ', k_indicator=' || to_char(k_indicator) ||
1333 ', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
1334
1335 -- Insert KPI Measure Thresholds
1336 IF (G_Ind_Tbl(k_indicator).config = 3) THEN
1337 BSC_COLOR_RANGES_PUB.create_pl_def_clr_prop_ranges (
1338 p_commit => FND_API.G_FALSE
1339 , p_objective_id => G_Ind_Tbl(k_indicator).Indicator
1340 , p_kpi_measure_id => l_kpi_measure_id
1341 , p_cascade_shared => FALSE
1342 , x_return_status => x_return_status
1343 , x_msg_count => x_msg_count
1344 , x_msg_data => x_msg_data
1345 );
1346 ELSE
1347 BSC_COLOR_RANGES_PUB.create_def_color_prop_ranges (
1348 p_commit => FND_API.G_FALSE
1349 , p_objective_id => G_Ind_Tbl(k_indicator).Indicator
1350 , p_kpi_measure_id => l_kpi_measure_id
1351 , p_cascade_shared => FALSE
1352 , x_return_status => x_return_status
1353 , x_msg_count => x_msg_count
1354 , x_msg_data => x_msg_data
1355 );
1356 END IF;
1357 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1358 RAISE FND_API.G_EXC_ERROR;
1359 END IF;
1360
1361 l_debug_stmt := 'Inserting BSC_KPI_DIM_SETS_TL' ||
1362 ', k_indicator=' || to_char(k_indicator) ||
1363 ', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
1364 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
1365
1366
1367 --2828685 ADD WHO COLUMNS
1368 l_sql_stmt := 'INSERT INTO BSC_KPI_DIM_SETS_TL
1369 (INDICATOR,DIM_SET_ID,LANGUAGE,SOURCE_LANG,NAME,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE)
1370 SELECT '||G_Ind_Tbl(k_indicator).Indicator||' AS INDICATOR,
1371 0 AS DIM_SET_ID,
1372 FEM.LANGUAGE AS LANGUAGE,
1373 FEM.SOURCE_LANG AS SOURCE_LANG,
1374 SUBSTR(FEM.MEANING,1,20) AS NAME,
1375 1,SYSDATE,1,SYSDATE
1376 FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM
1377 WHERE FEM.LOOKUP_TYPE =''BSC_TPLATE_TAB_DIM_SET_NAME'' AND
1378 FEM.LOOKUP_CODE = ''0''';
1379
1380 l_debug_stmt := l_sql_stmt;
1381 l_cursor := DBMS_SQL.Open_Cursor;
1382 DBMS_SQL.Parse(l_cursor, l_sql_stmt, DBMS_SQL.native);
1383 l_ignore := DBMS_SQL.Execute(l_cursor);
1384 DBMS_SQL.Close_Cursor(l_cursor);
1385
1386 For i_drill In 0 .. (G_Ind_Tbl(k_indicator).drills - 1)
1387 Loop
1388 if (G_Ind_Tbl(k_indicator).Indicator = 3013) then
1389 l_drill_ind := i_drill + 1;
1390 else
1391 l_drill_ind := 0;
1392 end if;
1393
1394 l_debug_stmt := 'Inserting BSC_KPI_DIM_LEVELS_B' ||
1395 ', k_indicator=' || to_char(k_indicator) ||
1396 ', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
1397 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
1398
1399 Insert Into BSC_KPI_DIM_LEVELS_B (
1400 INDICATOR,
1401 DIM_SET_ID,
1402 DIM_LEVEL_INDEX,
1403 LEVEL_TABLE_NAME,
1404 LEVEL_VIEW_NAME,
1405 FILTER_COLUMN,
1406 FILTER_VALUE,
1407 DEFAULT_VALUE,
1408 DEFAULT_TYPE,
1409 VALUE_ORDER_BY,
1410 COMP_ORDER_BY,
1411 LEVEL_PK_COL,
1412 PARENT_LEVEL_INDEX,
1413 PARENT_LEVEL_REL,
1414 TABLE_RELATION,
1415 PARENT_LEVEL_INDEX2,
1416 PARENT_LEVEL_REL2,
1417 STATUS,
1418 PARENT_IN_TOTAL,
1419 POSITION,
1420 TOTAL0,
1421 LEVEL_DISPLAY,
1422 NO_ITEMS,
1423 DEFAULT_KEY_VALUE,
1424 USER_LEVEL0,
1425 USER_LEVEL1,
1426 USER_LEVEL1_DEFAULT,
1427 USER_LEVEL2,
1428 USER_LEVEL2_DEFAULT
1429 )
1430 Values (
1431 G_Ind_Tbl(k_indicator).Indicator,
1432 0, -- configuration
1433 G_Drill_Tbl(l_drill_ind).Dim_level_index, -- drill
1434 G_Drill_Tbl(l_drill_ind).Table_Name, -- master_T
1435 G_Drill_Tbl(l_drill_ind).Level_View_Name,-- Level View Name
1436 NULL, -- condition_field
1437 G_Drill_Tbl(l_drill_ind).Filter_Val, -- condition_value
1438 G_Drill_Tbl(l_drill_ind).Default_val, -- init
1439 G_Drill_Tbl(l_drill_ind).Default_type, -- init_type
1440 G_Drill_Tbl(l_drill_ind).Value_Order, -- order_r
1441 G_Drill_Tbl(l_drill_ind).Comp_Order, -- order_r
1442 G_Drill_Tbl(l_drill_ind).Level_pk_col, -- field_n
1443 G_Drill_Tbl(l_drill_ind).Parent, -- parent
1444 G_Drill_Tbl(l_drill_ind).Parent_Rel, -- parent_relation
1445 G_Drill_Tbl(l_drill_ind).Table_Rel, -- table_relation
1446 G_Drill_Tbl(l_drill_ind).Parent2, -- parent2
1447 G_Drill_Tbl(l_drill_ind).Parent_Rel2, -- parent_relation2
1448 G_Drill_Tbl(l_drill_ind).Status, -- status
1449 2, -- status_whn_parnt_is_total
1450 G_Drill_Tbl(l_drill_ind).Position, -- position
1451 G_Drill_Tbl(l_drill_ind).Total0, -- total0
1452 0, -- LEVEL_DISPLAY
1453 0, -- No items
1454 NULL, -- Key Value
1455 G_Drill_Tbl(l_drill_ind).Ev0, -- ev0
1456 G_Drill_Tbl(l_drill_ind).Ev0, -- ev1
1457 G_Drill_Tbl(l_drill_ind).Ev1d, -- ev1d
1458 0, -- ev2
1459 0 -- ev2d
1460 );
1461
1462
1463 l_debug_stmt := 'Inserting BSC_KPI_DIM_LEVELS_TL' ||
1464 ', k_indicator=' || to_char(k_indicator) ||
1465 ', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
1466 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
1467
1468 -- Create dummy
1469 IF l_drill_ind = 0 THEN
1470 l_sql_stmt := 'INSERT INTO BSC_KPI_DIM_LEVELS_TL
1471 (INDICATOR,DIM_SET_ID,DIM_LEVEL_INDEX,LANGUAGE,SOURCE_LANG,NAME,
1472 HELP,TOTAL_DISP_NAME,COMP_DISP_NAME)
1473 SELECT '
1474 ||G_Ind_Tbl(k_indicator).Indicator||' AS INDICATOR,
1475 0 AS DIM_SET_ID,'
1476 ||G_Drill_Tbl(l_drill_ind).Dim_level_index||' AS DIM_LEVEL_INDEX,
1477 FEM_ALL.LANGUAGE AS LANGUAGE,
1478 FEM_ALL.SOURCE_LANG AS SOURCE_LANG,
1479 ''XXX'' AS NAME,
1480 ''XXX'' AS HELP,
1481 SUBSTR(FEM_ALL.MEANING,1,15) AS TOTAL_DISP_NAME,
1482 SUBSTR(FEM_COMP.MEANING,1,15) AS COMP_DISP_NAME
1483 FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_ALL, '
1484 ||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_COMP
1485 WHERE FEM_ALL.LOOKUP_TYPE =''BSC_UI_COMMON'' AND
1486 FEM_ALL.LOOKUP_CODE = ''ALL'' AND
1487 FEM_COMP.LOOKUP_TYPE =''BSC_UI_COMMON'' AND
1488 FEM_COMP.LOOKUP_CODE = ''COMPARISON'' AND
1489 FEM_COMP.LANGUAGE = FEM_ALL.LANGUAGE';
1490
1491 l_debug_stmt := l_sql_stmt;
1492 l_cursor := DBMS_SQL.Open_Cursor;
1493 DBMS_SQL.Parse(l_cursor, l_sql_stmt, DBMS_SQL.native);
1494 l_ignore := DBMS_SQL.Execute(l_cursor);
1495 DBMS_SQL.Close_Cursor(l_cursor);
1496 else
1497 l_sql_stmt := 'INSERT INTO BSC_KPI_DIM_LEVELS_TL
1498 (INDICATOR,DIM_SET_ID,DIM_LEVEL_INDEX,LANGUAGE,SOURCE_LANG,NAME,
1499 HELP,TOTAL_DISP_NAME,COMP_DISP_NAME)
1500 SELECT '
1501 ||G_Ind_Tbl(k_indicator).Indicator||' AS INDICATOR,
1502 0 AS DIM_SET_ID,'
1503 ||G_Drill_Tbl(l_drill_ind).Dim_level_index||' AS DIM_LEVEL_INDEX,
1504 FEM.LANGUAGE AS LANGUAGE,
1505 FEM.SOURCE_LANG AS SOURCE_LANG,
1506 SUBSTR(FEM.MEANING,1,30) AS NAME,
1507 SUBSTR(FEM.MEANING,1,80) AS HELP,
1508 SUBSTR(FEM_ALL.MEANING,1,15) AS TOTAL_DISP_NAME,
1509 SUBSTR(FEM_COMP.MEANING,1,15) AS COMP_DISP_NAME
1510 FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM, '
1511 ||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_ALL, '
1512 ||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM_COMP
1513 WHERE FEM.LOOKUP_TYPE =''BSC_TPLATE_TAB_DIM_LEVEL_NAME'' AND
1514 FEM.LOOKUP_CODE = '''||G_Drill_Tbl(l_drill_ind).dim_level_id||''' AND
1515 FEM_ALL.LOOKUP_TYPE =''BSC_UI_COMMON'' AND
1516 FEM_ALL.LOOKUP_CODE = ''ALL'' AND
1517 FEM_COMP.LOOKUP_TYPE =''BSC_UI_COMMON'' AND
1518 FEM_COMP.LOOKUP_CODE = ''COMPARISON'' AND
1519 FEM.LANGUAGE = FEM_ALL.LANGUAGE AND
1520 FEM_COMP.LANGUAGE = FEM_ALL.LANGUAGE';
1521
1522 l_debug_stmt := l_sql_stmt;
1523 l_cursor := DBMS_SQL.Open_Cursor;
1524 DBMS_SQL.Parse(l_cursor, l_sql_stmt, DBMS_SQL.native);
1525 l_ignore := DBMS_SQL.Execute(l_cursor);
1526 DBMS_SQL.Close_Cursor(l_cursor);
1527 end if;
1528
1529 if (G_Ind_Tbl(k_indicator).Indicator = 3013) then
1530
1531 l_debug_stmt := 'Inserting BSC_KPI_DIM_GROUPS' ||
1532 ', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator) ||
1533 ', Family_Code= ' || to_char(G_Drill_Tbl(l_drill_ind).dim_group_id);
1534
1535 Insert Into BSC_KPI_DIM_GROUPS (
1536 INDICATOR,
1537 DIM_SET_ID,
1538 DIM_GROUP_ID,
1539 DIM_GROUP_INDEX )
1540 Values (
1541 G_Ind_Tbl(k_indicator).Indicator,
1542 0,
1543 G_Drill_Tbl(l_drill_ind).dim_group_id,
1544 G_Drill_Tbl(l_drill_ind).dim_group_idx
1545 );
1546
1547 l_debug_stmt := 'Inserting BSC_KPI_DIM_LEVEL_PROPERTIES' ||
1548 ', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator) ||
1549 ', Entity_Code= ' || to_char(G_Drill_Tbl(l_drill_ind).dim_level_id);
1550
1551 Insert Into BSC_KPI_DIM_LEVEL_PROPERTIES (
1552 INDICATOR,
1553 DIM_SET_ID,
1554 DIM_LEVEL_ID,
1555 POSITION,
1556 TOTAL0,
1557 LEVEL_DISPLAY,
1558 DEFAULT_KEY_VALUE,
1559 USER_LEVEL0,
1560 USER_LEVEL1,
1561 USER_LEVEL1_DEFAULT,
1562 USER_LEVEL2,
1563 USER_LEVEL2_DEFAULT
1564 )
1565 Values (
1566 G_Ind_Tbl(k_indicator).Indicator,
1567 0,
1568 G_Drill_Tbl(l_drill_ind).dim_level_id,
1569 G_Drill_Tbl(l_drill_ind).Position,
1570 G_Drill_Tbl(l_drill_ind).Total0,
1571 G_Drill_Tbl(l_drill_ind).level_display,
1572 NULL,
1573 G_Drill_Tbl(l_drill_ind).Ev0, -- ev0
1574 G_Drill_Tbl(l_drill_ind).Ev0, -- ev1
1575 G_Drill_Tbl(l_drill_ind).Ev1d, -- ev1d
1576 NULL, -- ev2
1577 NULL -- ev2d
1578 );
1579
1580 end if;
1581
1582 End Loop; -- i_drill
1583
1584 if (G_Ind_Tbl(k_indicator).Indicator = 3013) then
1585 l_opt_caption := 'Amount';
1586 l_opt_lookup := 1;
1587 else
1588 l_opt_caption := 'Option 0';
1589 l_opt_lookup := 0;
1590 end if;
1591
1592 l_debug_stmt := 'Inserting BSC_KPI_ANALYSIS_OPTIONS_B' ||
1593 ', k_indicator=' || to_char(k_indicator) ||
1594 ', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
1595 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
1596
1597 l_sql_defaults_b := 'Insert Into BSC_KPI_ANALYSIS_OPTIONS_B (INDICATOR,'||
1598 'ANALYSIS_GROUP_ID,OPTION_ID,PARENT_OPTION_ID,GRANDPARENT_OPTION_ID,'||
1599 'DIM_SET_ID,USER_LEVEL0,USER_LEVEL1,USER_LEVEL1_DEFAULT,USER_LEVEL2,'||
1600 'USER_LEVEL2_DEFAULT )Values (:1,0,0,0,0,0,1,1,NULL,NULL,NULL)';
1601 EXECUTE IMMEDIATE l_sql_defaults_b USING G_Ind_Tbl(k_indicator).Indicator;
1602
1603 l_debug_stmt := 'Inserting BSC_KPI_ANALYSIS_OPTIONS_TL' ||
1604 ', k_indicator=' || to_char(k_indicator) ||
1605 ', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
1606 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
1607
1608 l_sql_stmt := 'INSERT INTO BSC_KPI_ANALYSIS_OPTIONS_TL
1609 (INDICATOR,ANALYSIS_GROUP_ID,OPTION_ID,PARENT_OPTION_ID,GRANDPARENT_OPTION_ID,
1610 LANGUAGE,SOURCE_LANG,NAME,HELP)
1611 SELECT '
1612 ||G_Ind_Tbl(k_indicator).Indicator||' AS INDICATOR,
1613 0 AS ANALYSIS_GROUP_ID,
1614 0 AS OPTION_ID,
1615 0 AS PARENT_OPTION_ID,
1616 0 AS GRANDPARENT_OPTION_ID,
1617 FEM.LANGUAGE AS LANGUAGE,
1618 FEM.SOURCE_LANG AS SOURCE_LANG,
1619 SUBSTR(FEM.MEANING,1,25) AS NAME,
1620 SUBSTR(FEM.MEANING,1,80) AS HELP
1621 FROM '||BSC_TEMPLATE.LOOKUP_VALUES_TABLE||' FEM
1622 WHERE FEM.LOOKUP_TYPE =''BSC_TPLATE_TAB_AO_NAMES'' AND
1623 FEM.LOOKUP_CODE =:1';
1624
1625
1626 l_debug_stmt := l_sql_stmt;
1627 EXECUTE IMMEDIATE l_sql_stmt using l_opt_lookup;
1628
1629 For p_period In 0 .. (G_Ind_Tbl(k_indicator).period_shown - 1)
1630 Loop
1631
1632 if (G_Ind_Tbl(k_indicator).Indicator = 3007) and
1633 (G_Period_Tbl(p_period).Period_Type = 5) then
1634 l_option_r := 1;
1635 level_per :=2;
1636 else
1637 l_option_r := 0;
1638 level_per :=1;
1639 end if;
1640
1641 l_debug_stmt := 'Inserting BSC_KPI_PERIODICITIES' ||
1642 ', k_indicator=' || to_char(k_indicator) ||
1643 ', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
1644 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
1645
1646
1647 Insert Into BSC_KPI_PERIODICITIES (
1648 INDICATOR,
1649 PERIODICITY_ID,
1650 DISPLAY_ORDER,
1651 PREVIOUS_YEARS,
1652 NUM_OF_YEARS,
1653 VIEWPORT_FLAG,
1654 VIEWPORT_DEFAULT_SIZE,
1655 USER_LEVEL0,
1656 USER_LEVEL1,
1657 USER_LEVEL1_DEFAULT,
1658 USER_LEVEL2,
1659 USER_LEVEL2_DEFAULT,
1660 CURRENT_PERIOD,
1661 LAST_UPDATE_DATE)
1662 Values (
1663 G_Ind_Tbl(k_indicator).Indicator,
1664 G_Period_Tbl(p_period).Period_Type, -- periodicity_type
1665 l_option_r, -- option_r
1666 G_Period_Tbl(p_period).Prev_Year, -- previous_years
1667 G_Period_Tbl(p_period).Num_Years, -- number_of_years
1668 G_Period_Tbl(p_period).Viewport_flag, -- viewport
1669 G_Period_Tbl(p_period).Viewport_Size, -- viewport_default_size
1670 level_per, -- ev0
1671 level_per, -- ev1
1672 NULL, -- ev1d
1673 NULL, -- ev2
1674 NULL, -- ev2d
1675 1,
1676 SYSDATE
1677 );
1678
1679
1680 l_debug_stmt := 'Inserting BSC_KPI_DATA_TABLES' ||
1681 ', k_indicator=' || to_char(k_indicator) ||
1682 ', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
1683 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
1684
1685 l_sql_defaults_b := 'Insert Into BSC_KPI_DATA_TABLES (INDICATOR,PERIODICITY_ID,'||
1686 'DIM_SET_ID,LEVEL_COMB,TABLE_NAME,FILTER_CONDITION )Values ('||
1687 ':1,:2,0,'||
1688 '''?'''||
1689 ',NULL,NULL)';
1690
1691 EXECUTE IMMEDIATE l_sql_defaults_b USING G_Ind_Tbl(k_indicator).Indicator,G_Period_Tbl(p_period).Period_Type;
1692
1693 End Loop; -- p_period loop
1694
1695
1696 -- Config Indicator to Tabs system
1697
1698 l_debug_stmt := 'Inserting BSC_TAB_INDICATORS' ||
1699 ', k_indicator=' || to_char(k_indicator) ||
1700 ', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
1701 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
1702
1703 Insert Into BSC_TAB_INDICATORS (
1704 TAB_ID,
1705 INDICATOR,
1706 BSC_MODEL_FLAG,
1707 LEFT_POSITION,
1708 TOP_POSITION,
1709 WIDTH,
1710 HEIGHT,
1711 BACKCOLOR )
1712 Values (
1713 G_Ind_Tbl(k_indicator).Tab,
1714 G_Ind_Tbl(k_indicator).Indicator,
1715 0, 0, 0, 0, 0, 0
1716 );
1717
1718
1719 else -- Indicator.Detail_Flag = 'NO'
1720
1721 -- the following codes should not be used
1722
1723 l_debug_stmt := 'Inserting BSC_KPI_PERIODICITIES' ||
1724 ', k_indicator=' || to_char(k_indicator) ||
1725 ', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
1726 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
1727
1728 Insert Into BSC_KPI_PERIODICITIES (
1729 INDICATOR,
1730 PERIODICITY_ID,
1731 DISPLAY_ORDER,
1732 PREVIOUS_YEARS,
1733 NUM_OF_YEARS,
1734 VIEWPORT_FLAG,
1735 VIEWPORT_DEFAULT_SIZE,
1736 USER_LEVEL0,
1737 USER_LEVEL1,
1738 USER_LEVEL1_DEFAULT,
1739 USER_LEVEL2,
1740 USER_LEVEL2_DEFAULT,
1741 CURRENT_PERIOD,
1742 LAST_UPDATE_DATE
1743 )
1744 Values (
1745 G_Ind_Tbl(k_indicator).Indicator,
1746 5, -- periodicity_type
1747 0, -- option_r
1748 0, -- previous_years
1749 0, -- number_of_years
1750 0, -- viewport
1751 0, -- viewport_default_size
1752 2, -- ev0
1753 2, -- ev1
1754 NULL, -- ev1d
1755 NULL, -- ev2
1756 NULL, -- ev2d
1757 1,
1758 SYSDATE
1759 );
1760 end if; -- Indicator.Detail_Flag
1761
1762 -- User Options
1763 l_debug_stmt := 'Inserting BSC_SYS_USER_OPTIONS at System Level' ||
1764 ', Indicator= ' || to_char(G_Ind_Tbl(k_indicator).Indicator);
1765
1766 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
1767 For l_variable In 0 .. (G_Ind_Tbl(k_indicator).User_Options -1) Loop
1768
1769 Insert Into BSC_SYS_USER_OPTIONS(
1770 SOURCE_TYPE,
1771 SOURCE_CODE,
1772 USER_OPT_ID,
1773 ENABLED_FLAG,
1774 DISPLAY_FLAG)
1775 Values (2,
1776 G_Ind_Tbl(k_indicator).Indicator,
1777 l_user_options (l_variable),
1778 0,
1779 1);
1780 end Loop;
1781
1782 End loop; -- indicator loop
1783
1784 -- Fix BSC_KPI_PERIODICITIES. Current period for annual periodicities MUST be the current year.
1785 -- BUG 1949762
1786 update BSC_KPI_PERIODICITIES
1787 set CURRENT_PERIOD = (
1788 SELECT CURRENT_YEAR
1789 FROM BSC_SYS_CALENDARS_B
1790 WHERE CALENDAR_ID=1
1791 )
1792 where PERIODICITY_ID = 1;
1793
1794 -- General
1795 l_debug_stmt := 'Inserting BSC_SYS_USER_OPTIONS at System Level';
1796 --DBMS_OUTPUT.PUT_LINE(l_debug_stmt);
1797 Insert Into BSC_SYS_USER_OPTIONS(
1798 SOURCE_TYPE,SOURCE_CODE,USER_OPT_ID,ENABLED_FLAG,DISPLAY_FLAG)
1799 Values (0,0,1,0,1);
1800 Insert Into BSC_SYS_USER_OPTIONS(
1801 SOURCE_TYPE,SOURCE_CODE,USER_OPT_ID,ENABLED_FLAG,DISPLAY_FLAG)
1802 Values (0,0,2,0,1);
1803
1804 /* -- ---------------------------------------------------------
1805 -- Reser sequences:
1806 -- BSC_SYS_DIM_LEVEL_ID_S :Get the DIM_LEVEL_ID for new Dimensions.TABLE= BSC_SYS_DIM_LEVELS_B
1807 -- BSC_SYS_DIM_GROUP_ID_S: Get the GROUP_ID for new Dimension Groups.TABLE =BSC_SYS_DIM_GROUPS_TL
1808 -- BSC_SYS_DATASET_ID_S
1809 -- BSC_SYS_MEASURE_ID_S
1810 -- ---------------------------------------------------------*/
1811 -- Reset sequence BSC_SYS_DIM_LEVEL_ID_S
1812 l_sql_stmt := 'SELECT NVL(MAX(DIM_LEVEL_ID),0) FROM BSC_SYS_DIM_LEVELS_B';
1813 h_cursor := DBMS_SQL.OPEN_CURSOR;
1814 DBMS_SQL.PARSE(h_cursor, l_sql_stmt, DBMS_SQL.NATIVE);
1815 DBMS_SQL.DEFINE_COLUMN(h_cursor, 1, h_max_id);
1816 h_ret := DBMS_SQL.EXECUTE(h_cursor);
1817
1818 IF DBMS_SQL.FETCH_ROWS(h_cursor) > 0 THEN
1819 DBMS_SQL.COLUMN_VALUE(h_cursor, 1, h_max_id);
1820 ELSE
1821 h_max_id := 0;
1822 END IF;
1823 DBMS_SQL.CLOSE_CURSOR(h_cursor);
1824 h_max_id := h_max_id + 1;
1825
1826 l_sql_stmt := 'DROP SEQUENCE BSC_SYS_DIM_LEVEL_ID_S';
1827 BEGIN BSC_APPS.Do_DDL(l_sql_stmt, AD_DDL.DROP_SEQUENCE, 'BSC_SYS_DIM_LEVEL_ID_S'); EXCEPTION WHEN OTHERS THEN NULL; END;
1828 l_sql_stmt := 'CREATE SEQUENCE BSC_SYS_DIM_LEVEL_ID_S START WITH '||h_max_id;
1829 BSC_APPS.Do_DDL(l_sql_stmt, AD_DDL.CREATE_SEQUENCE, 'BSC_SYS_DIM_LEVEL_ID_S');
1830
1831 -- Reset sequence BSC_SYS_DIM_GROUP_ID_S
1832 l_sql_stmt := 'SELECT NVL(MAX(DIM_GROUP_ID),0) FROM BSC_SYS_DIM_GROUPS_TL';
1833 h_cursor := DBMS_SQL.OPEN_CURSOR;
1834 DBMS_SQL.PARSE(h_cursor, l_sql_stmt, DBMS_SQL.NATIVE);
1835 DBMS_SQL.DEFINE_COLUMN(h_cursor, 1, h_max_id);
1836 h_ret := DBMS_SQL.EXECUTE(h_cursor);
1837
1838 IF DBMS_SQL.FETCH_ROWS(h_cursor) > 0 THEN
1839 DBMS_SQL.COLUMN_VALUE(h_cursor, 1, h_max_id);
1840 ELSE
1841 h_max_id := 0;
1842 END IF;
1843 DBMS_SQL.CLOSE_CURSOR(h_cursor);
1844 h_max_id := h_max_id + 1;
1845
1846 l_sql_stmt := 'DROP SEQUENCE BSC_SYS_DIM_GROUP_ID_S';
1847 BEGIN BSC_APPS.Do_DDL(l_sql_stmt, AD_DDL.DROP_SEQUENCE, 'BSC_SYS_DIM_GROUP_ID_S'); EXCEPTION WHEN OTHERS THEN NULL; END;
1848 l_sql_stmt := 'CREATE SEQUENCE BSC_SYS_DIM_GROUP_ID_S START WITH '||h_max_id;
1849 BSC_APPS.Do_DDL(l_sql_stmt, AD_DDL.CREATE_SEQUENCE, 'BSC_SYS_DIM_GROUP_ID_S');
1850
1851
1852 -- Reset sequence BSC_SYS_DATASET_ID_S
1853 l_sql_stmt := 'SELECT NVL(MAX(DATASET_ID),0) FROM BSC_SYS_DATASETS_B';
1854 h_cursor := DBMS_SQL.OPEN_CURSOR;
1855 DBMS_SQL.PARSE(h_cursor, l_sql_stmt, DBMS_SQL.NATIVE);
1856 DBMS_SQL.DEFINE_COLUMN(h_cursor, 1, h_max_id);
1857 h_ret := DBMS_SQL.EXECUTE(h_cursor);
1858
1859 IF DBMS_SQL.FETCH_ROWS(h_cursor) > 0 THEN
1860 DBMS_SQL.COLUMN_VALUE(h_cursor, 1, h_max_id);
1861 ELSE
1862 h_max_id := 0;
1863 END IF;
1864 DBMS_SQL.CLOSE_CURSOR(h_cursor);
1865 h_max_id := h_max_id + 1;
1866
1867 l_sql_stmt := 'DROP SEQUENCE BSC_SYS_DATASET_ID_S';
1868 BEGIN BSC_APPS.Do_DDL(l_sql_stmt, AD_DDL.DROP_SEQUENCE, 'BSC_SYS_DATASET_ID_S'); EXCEPTION WHEN OTHERS THEN NULL; END;
1869 l_sql_stmt := 'CREATE SEQUENCE BSC_SYS_DATASET_ID_S START WITH '||h_max_id;
1870 BSC_APPS.Do_DDL(l_sql_stmt, AD_DDL.CREATE_SEQUENCE, 'BSC_SYS_DATASET_ID_S');
1871
1872 -- Reset sequence BSC_SYS_MEASURE_ID_S
1873 l_sql_stmt := 'SELECT NVL(MAX(MEASURE_ID),0) FROM BSC_SYS_MEASURES';
1874 h_cursor := DBMS_SQL.OPEN_CURSOR;
1875 DBMS_SQL.PARSE(h_cursor, l_sql_stmt, DBMS_SQL.NATIVE);
1876 DBMS_SQL.DEFINE_COLUMN(h_cursor, 1, h_max_id);
1877 h_ret := DBMS_SQL.EXECUTE(h_cursor);
1878
1879 IF DBMS_SQL.FETCH_ROWS(h_cursor) > 0 THEN
1880 DBMS_SQL.COLUMN_VALUE(h_cursor, 1, h_max_id);
1881 ELSE
1882 h_max_id := 0;
1883 END IF;
1884 DBMS_SQL.CLOSE_CURSOR(h_cursor);
1885 h_max_id := h_max_id + 1;
1886
1887 l_sql_stmt := 'DROP SEQUENCE BSC_SYS_MEASURE_ID_S';
1888 BEGIN BSC_APPS.Do_DDL(l_sql_stmt, AD_DDL.DROP_SEQUENCE, 'BSC_SYS_MEASURE_ID_S'); EXCEPTION WHEN OTHERS THEN NULL; END;
1889 l_sql_stmt := 'CREATE SEQUENCE BSC_SYS_MEASURE_ID_S START WITH '||h_max_id;
1890 BSC_APPS.Do_DDL(l_sql_stmt, AD_DDL.CREATE_SEQUENCE, 'BSC_SYS_MEASURE_ID_S');
1891
1892
1893 /* --END 2828685 SYNCH SEQUENCES */
1894
1895 Return(TRUE);
1896
1897 EXCEPTION
1898 WHEN FND_API.G_EXC_ERROR THEN
1899 IF (x_msg_data IS NULL) THEN
1900 FND_MSG_PUB.Count_And_Get
1901 ( p_encoded => FND_API.G_FALSE
1902 , p_count => x_msg_count
1903 , p_data => x_msg_data
1904 );
1905 END IF;
1906 BSC_MESSAGE.Add(
1907 X_Message => x_msg_data,
1908 X_Source => 'bsc_template.create_tab_template',
1909 X_Mode => 'I');
1910 BSC_MESSAGE.Add(
1911 X_Message => l_debug_stmt,
1912 X_Source => 'bsc_template.create_tab_template',
1913 x_type => 3,
1914 X_Mode => 'I');
1915 RETURN FALSE;
1916
1917 WHEN BSC_ERROR THEN
1918 BSC_MESSAGE.Add(
1919 X_Message => l_debug_stmt,
1920 X_Source => 'bsc_template.create_tab_template',
1921 X_Mode => 'I');
1922
1923 RETURN(FALSE);
1924
1925 WHEN OTHERS THEN
1926 BSC_MESSAGE.Add(
1927 X_Message => SQLERRM,
1928 X_Source => 'bsc_template.create_tab_template',
1929 X_Mode => 'I');
1930
1931 BSC_MESSAGE.Add(
1932 X_Message => l_debug_stmt,
1933 X_Source => 'bsc_template.create_tab_template',
1934 x_type => 3,
1935 X_Mode => 'I');
1936
1937 IF (DBMS_SQL.IS_OPEN(l_cursor)) then
1938 DBMS_SQL.CLOSE_CURSOR(l_cursor);
1939 END IF;
1940
1941 RETURN(FALSE);
1942
1943 End Create_Tab_Template;
1944
1945
1946 END BSC_TAB_TPLATE;