[Home] [Help]
PACKAGE BODY: APPS.BSC_TEMPLATE
Source
1 PACKAGE BODY BSC_TEMPLATE AS
2 /* $Header: BSCUTMPB.pls 120.1 2006/02/07 12:19:15 hcamacho noship $ */
3
4
5 /*===========================================================================+
6 |
7 | Name: Create_Template
8 |
9 | Description: Main entry from KPI designer
10 |
11 | Parameters:
12 | x_template_name template name
13 | x_template_type template type, 6 for Tab and 1 for Cross
14 | x_sys_name OBSC system name
15 | x_sys_desc OBSC system description
16 | x_sys_help Help text
17 | x_debug_flag debug_flag, default is 'NO'
18 |
19 +============================================================================*/
20 Procedure Create_Template (
21 x_template_name IN Varchar2,
22 x_template_type IN Number,
23 x_sys_name IN Varchar2,
24 x_sys_desc IN Varchar2,
25 x_sys_help IN Varchar2,
26 x_debug_flag IN Varchar2 := 'NO'
27 ) Is
28 l_sys_type Varchar2(60);
29 l_message Varchar2(200);
30 Begin
31
32 BSC_MESSAGE.Init(X_Debug_Flag => X_Debug_Flag);
33
34 -- Init app varaibles
35 bsc_apps.init_bsc_apps;
36
37 -- Init lookup table names, remove personal option
38 -- Apps
39 LOOKUP_VALUES_TABLE := 'FND_LOOKUP_VALUES';
40
41 Select PROPERTY_VALUE
42 Into l_sys_type
43 From BSC_SYS_INIT
44 Where PROPERTY_CODE = 'MODEL_TYPE';
45
46 -- DBMS_OUTPUT.PUT_LINE('sys_type=' || l_sys_type);
47
48 if (l_sys_type <> '-1') then
49 l_message := bsc_apps.get_message('BSC_TEMPLATE_EXIST');
50 Raise BSC_DEF_ERROR;
51 end if;
52
53 -- set system name
54
55 Update BSC_SYS_INIT
56 Set PROPERTY_VALUE = x_sys_name , LAST_UPDATED_BY =2,LAST_UPDATE_DATE=SYSDATE
57 Where PROPERTY_CODE = 'SYSTEM_NAME';
58
59 Update BSC_SYS_INIT
60 Set PROPERTY_VALUE = to_char(x_template_type),LAST_UPDATED_BY =2,LAST_UPDATE_DATE=SYSDATE
61 Where PROPERTY_CODE = 'MODEL_TYPE';
62
63
64 if (x_template_type = 6) then
65
66 if (NOT BSC_Tab_Tplate.Create_Tab_Template) then
67 l_message := 'System Error: BSC_Tab_Tplate.Create_Tab_Template()';
68 Raise BSC_SYS_ERROR;
69 end if;
70 else
71 l_message := bsc_apps.get_message('BSC_INVALID_TEMPLATE');
72 l_message := bsc_apps.replace_token(l_message,'TEMPLATE_TYPE', to_char(x_template_type));
73 Raise BSC_DEF_ERROR;
74 end if;
75
76 Exception
77 When BSC_DEF_ERROR Then
78 BSC_MESSAGE.Add(
79 X_Message => l_message,
80 X_Source => 'bsc_template.create_template',
81 X_Mode => 'I');
82
83 When BSC_SYS_ERROR Then
84 BSC_MESSAGE.Add(
85 X_Message => l_message,
86 X_Source => 'bsc_template.create_template',
87 X_Mode => 'I');
88
89 When OTHERS Then
90 BSC_MESSAGE.Add(
91 X_Message => SQLERRM,
92 X_Source => 'bsc_template.create_template',
93 X_Mode => 'I');
94
95 End Create_Template;
96
97
98 /*===========================================================================+
99 |
100 | Name: Restore_Init_Layout
101 |
102 | Description: Rollback changes if error occurs during template creation
103 |
104 | Parameters:
105 | x_template_name template name
106 | x_template_type template type, 6 for Tab and 1 for Cross
107 | x_debug_flag debug_flag, default is 'NO'
108 |
109 | HCC 12/21/99 Data model 4.0
110 +============================================================================*/
111 Procedure Restore_Init_Layout(
112 x_template_name IN Varchar2,
113 x_template_type IN Number,
114 x_debug_flag IN Varchar2 := 'NO'
115 ) Is
116 l_count number;
117 l_cursor number;
118 l_ignore number;
119 l_sql_stmt varchar2(2000);
120 l_debug_stmt varchar2(2000);
121 l_message Varchar2(200);
122 l_panel0_count number := 0;
123 l_panel1_count number := 0;
124 l_project_count number := 0;
125
126 Begin
127
128 l_debug_stmt := 'Deleting from BSC_SYS_DATASET_CALC table';
129 Delete From BSC_SYS_DATASET_CALC Where DATASET_ID <> -1;
130
131 l_debug_stmt := 'Deleting from BSC_SYS_DATASETS_TL table';
132 Delete From BSC_SYS_DATASETS_TL Where DATASET_ID <> -1;
133
134 l_debug_stmt := 'Deleting from BSC_SYS_DATASETS_B table';
135 Delete From BSC_SYS_DATASETS_B Where DATASET_ID <> -1;
136
137 l_debug_stmt := 'Deleting from BSC_DB_MEASURE_COLS_TL table';
138 Delete From BSC_DB_MEASURE_COLS_TL where MEASURE_COL <> 'Default_Field';
139
140 l_debug_stmt := 'Deleting from BSC_SYS_MEASURES table';
141 Delete From BSC_SYS_MEASURES where MEASURE_ID <> -1;
142
143 l_debug_stmt := 'Deleting from BSC_SYS_DIM_LEVELS_BY_GROUP table';
144 Delete From BSC_SYS_DIM_LEVELS_BY_GROUP;
145
146 l_debug_stmt := 'Deleting from BSC_SYS_DIM_GROUPS_TL table';
147 Delete From BSC_SYS_DIM_GROUPS_TL;
148
149 --* l_debug_stmt := 'Deleting from MPROJ_DRILLS_FAMILIES table';
150 --* Delete From MPROJ_DRILLS_FAMILIES;
151
152 l_debug_stmt := 'Deleting from BSC_SYS_DIM_LEVEL_RELS';
153 Delete From BSC_SYS_DIM_LEVEL_RELS;
154
155 l_debug_stmt := 'Deleting from BSC_SYS_DIM_LEVELS_TL table';
156 Delete From BSC_SYS_DIM_LEVELS_TL;
157
158 l_debug_stmt := 'Deleting from BSC_SYS_DIM_LEVELS_B table';
159 Delete From BSC_SYS_DIM_LEVELS_B;
160
161 l_debug_stmt := 'Deleting from BSC_SYS_DIM_LEVEL_COLS table';
162 Delete From BSC_SYS_DIM_LEVEL_COLS;
163
164 l_debug_stmt := 'Deleting from BSC_KPI_DIM_GROUPS table';
165 Delete From BSC_KPI_DIM_GROUPS;
166
167 l_debug_stmt := 'Deleting from BSC_KPI_DIM_LEVEL_PROPERTIES table';
168 Delete From BSC_KPI_DIM_LEVEL_PROPERTIES;
169
170 -- for Tab only
171 l_debug_stmt := 'Deleting from BSC_TAB_INDICATORS table';
172 Delete From BSC_TAB_INDICATORS;
173
174 l_debug_stmt := 'Deleting from BSC_KPI_DATA_TABLES table';
175 Delete From BSC_KPI_DATA_TABLES ;
176
177 l_debug_stmt := 'Deleting from BSC_KPI_PERIODICITIES table';
178 Delete From BSC_KPI_PERIODICITIES ;
179
180 l_debug_stmt := 'Deleting from BSC_KPI_ANALYSIS_OPTIONS_B table';
181 Delete From BSC_KPI_ANALYSIS_OPTIONS_B ;
182
183 l_debug_stmt := 'Deleting from BSC_KPI_ANALYSIS_OPTIONS_TL table';
184 Delete From BSC_KPI_ANALYSIS_OPTIONS_TL ;
185
186 l_debug_stmt := 'Deleting from BSC_KPI_DIM_LEVELS_TL table';
187 Delete From BSC_KPI_DIM_LEVELS_TL ;
188
189 l_debug_stmt := 'Deleting from BSC_KPI_DIM_LEVELS_B table';
190 Delete From BSC_KPI_DIM_LEVELS_B ;
191
192 l_debug_stmt := 'Deleting from BSC_KPI_DIM_SETS_TL table';
193 Delete From BSC_KPI_DIM_SETS_TL ;
194
195 l_debug_stmt := 'Deleting from BSC_KPI_ANALYSIS_MEASURES_B table';
196 Delete From BSC_KPI_ANALYSIS_MEASURES_B;
197
198 l_debug_stmt := 'Deleting from BSC_KPI_ANALYSIS_MEASURES_TL table';
199 Delete From BSC_KPI_ANALYSIS_MEASURES_TL;
200
201 l_debug_stmt := 'Deleting from BSC_KPI_CALCULATIONS table';
202 Delete From BSC_KPI_CALCULATIONS ;
203
204 l_debug_stmt := 'Deleting from BSC_KPI_ANALYSIS_GROUPS table';
205 Delete From BSC_KPI_ANALYSIS_GROUPS;
206
207 l_debug_stmt := 'Deleting from BSC_KPI_DEFAULTS_TL table';
208 Delete From BSC_KPI_DEFAULTS_TL ;
209
210 l_debug_stmt := 'Deleting from BSC_KPI_DEFAULTS_B table';
211 Delete From BSC_KPI_DEFAULTS_B ;
212
213 l_debug_stmt := 'Deleting from BSC_KPIS_TL table';
214 Delete From BSC_KPIS_TL;
215
216 l_debug_stmt := 'Deleting from BSC_KPI_PROPERTIES table';
217 Delete From BSC_KPI_PROPERTIES;
218
219 l_debug_stmt := 'Deleting from BSC_KPIS_B table';
220 Delete From BSC_KPIS_B ;
221
222
223 -- for Tab only
224 l_debug_stmt := 'Deleting from BSC_SYS_LINES table';
225 Delete From BSC_SYS_LINES ;
226
227 l_debug_stmt := 'Deleting from BSC_SYS_USER_OPTIONS table';
228 Delete From BSC_SYS_USER_OPTIONS;
229
230 l_debug_stmt := 'Deleting from BSC_TAB_IND_GROUPS_B table';
231 Delete From BSC_TAB_IND_GROUPS_B;
232
233 l_debug_stmt := 'Deleting from BSC_TAB_IND_GROUPS_TL table';
234 Delete From BSC_TAB_IND_GROUPS_TL;
235
236
237 l_debug_stmt := 'Deleting from BSC_TAB_CSF_B table';
238 Delete From BSC_TAB_CSF_B;
239
240 l_debug_stmt := 'Deleting from BSC_TAB_CSF_TL table';
241 Delete From BSC_TAB_CSF_TL;
242
243 l_debug_stmt := 'Deleting from BSC_TABS_B table';
244 Delete From BSC_TABS_B;
245
246 l_debug_stmt := 'Deleting from BSC_TABS_TL table';
247 Delete From BSC_TABS_TL;
248
249
250 l_debug_stmt := 'Updating BSC_SYS_INIT.MODEL_TYPE';
251 Update BSC_SYS_INIT Set PROPERTY_VALUE= '-1',LAST_UPDATED_BY =2,LAST_UPDATE_DATE=SYSDATE
252 Where PROPERTY_CODE= 'MODEL_TYPE';
253
254
255 l_debug_stmt := 'Updating BSC_SYS_INIT.SYSTEM_NAME';
256 Update BSC_SYS_INIT Set PROPERTY_VALUE= 'NoSystem',LAST_UPDATED_BY =2,LAST_UPDATE_DATE=SYSDATE
257 Where PROPERTY_CODE= 'SYSTEM_NAME';
258
259
260 Select count(*)
261 Into l_count
262 From User_Tables
263 Where table_name = 'BSC_D_TYPE_OF_ACCOUNT';
264
265 if (l_count <> 0) then
266 l_sql_stmt := 'Drop Table BSC_D_TYPE_OF_ACCOUNT';
267 l_debug_stmt := l_sql_stmt;
268
269 l_cursor := DBMS_SQL.Open_Cursor;
270 DBMS_SQL.Parse(l_cursor, l_sql_stmt, DBMS_SQL.native);
271 l_ignore := DBMS_SQL.Execute(l_cursor);
272 DBMS_SQL.Close_Cursor(l_cursor);
273 end if;
274
275 Select count(*)
276 Into l_count
277 From User_Tables
278 Where table_name = 'BSC_D_ACCOUNT';
279
280 if (l_count <> 0) then
281 l_sql_stmt := 'Drop Table BSC_D_ACCOUNT';
282 l_debug_stmt := l_sql_stmt;
283
284 l_cursor := DBMS_SQL.Open_Cursor;
285 DBMS_SQL.Parse(l_cursor, l_sql_stmt, DBMS_SQL.native);
286 l_ignore := DBMS_SQL.Execute(l_cursor);
287 DBMS_SQL.Close_Cursor(l_cursor);
288 end if;
289
290 Select count(*)
291 Into l_count
292 From User_Tables
293 Where table_name = 'BSC_D_SUBACCOUNT';
294
295 if (l_count <> 0) then
296 l_sql_stmt := 'Drop Table BSC_D_SUBACCOUNT';
297 l_debug_stmt := l_sql_stmt;
298
299 l_cursor := DBMS_SQL.Open_Cursor;
300 DBMS_SQL.Parse(l_cursor, l_sql_stmt, DBMS_SQL.native);
301 l_ignore := DBMS_SQL.Execute(l_cursor);
302 DBMS_SQL.Close_Cursor(l_cursor);
303 end if;
304
305 if (x_template_type = 1) then -- Cross system
306
307 Select count(*)
308 Into l_project_count
309 From User_Tables
310 Where table_name = 'BSC_D_PROJECT';
311
312 if (l_project_count <> 0) then
313
314 l_sql_stmt := 'Drop Table BSC_D_PROJECT';
315 l_debug_stmt := l_sql_stmt;
316
317 l_cursor := DBMS_SQL.Open_Cursor;
318 DBMS_SQL.Parse(l_cursor, l_sql_stmt, DBMS_SQL.native);
319 l_ignore := DBMS_SQL.Execute(l_cursor);
320 DBMS_SQL.Close_Cursor(l_cursor);
321
322 end if;
323
324 END IF;
325
326
327 Exception
328 When BSC_DEF_ERROR Then
329 BSC_MESSAGE.Add(
330 X_Message => l_message,
331 X_Source => 'bsc_template.restore_init_layout',
332 X_Mode => 'I');
333
334 When OTHERS Then
335 BSC_MESSAGE.Add(
336 X_Message => SQLERRM,
337 X_Source => 'bsc_template.restore_init_layout',
338 X_Mode => 'I');
339
340 End Restore_Init_Layout;
341
342
343 END BSC_TEMPLATE;