[Home] [Help]
PACKAGE BODY: APPS.BSC_PMF_UI_API_PUB
Source
1 package body BSC_PMF_UI_API_PUB as
2 /* $Header: BSCUIAPB.pls 120.3 2006/02/10 01:31:26 ppandey noship $ */
3 /*
4 +======================================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +======================================================================================+
8 | FILENAME |
9 | BSCUIAPB.pls |
10 | |
11 | Creation Date: |
12 | October 16, 2001 |
13 | |
14 | Creator: |
15 | Mario-Jair Campos |
16 | |
17 | Description: |
18 | 04-MAR-2003 PAJOHRI MLS Bug #2721899 |
19 | Changed BSC_SYS_DIM_GROUPS_TL to BSC_SYS_DIM_GROUPS_VL in |
20 | select query. |
21 | |
22 | 12-MAR-2003 ADRAO FIXED Bug #2834277 |
23 | 20-MAR-03 PWALI for bug #2843082 |
24 | 13-MAY-2003 PWALI Bug #2942895, SQL BIND COMPLIANCE |
25 | 04-APR-03 ASHANKAR Fix for the bug#2883880 added new procedure Update_Bsc_Dataset |
26 | 13-JUN-03 ADEULGAO Bug#2878840, Modified function Create_Bsc_Dimension to have |
27 | single DIM group for including all DIM LEVELS imported |
28 | 05-DEC-03 PAJOHRI Removed use of All_Objects, Bug #3236002 |
29 | 27-FEB-2004 adeulgao fixed bug#3431750 |
30 | 25-OCT-2005 kyadamak Removed literals for Enhancement#4618419 |
31 +======================================================================================+
32 */
33 G_PKG_NAME varchar2(30) := 'BSC_PMF_UI_API_PUB';
34
35 g_Bsc_Pmf_Ui_Rec BSC_PMF_UI_API_PUB.Bsc_Pmf_Ui_Rec_Type;
36 g_Bsc_Pmf_Dim_Tbl BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Tbl_Type;
37 g_Bsc_Dim_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
38 g_Bsc_Dim_Group_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
39 g_Bsc_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
40 g_Bsc_Dimset_Rec BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
41 g_Bsc_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
42 --g_Bsc_Kpi_Rec BSC_KPI_METADATA_PUB.Bsc_Kpi_Rec_Type;
43 --g_Bsc_Kpi_Tbl BSC_KPI_METADATA_PUB.Bsc_Kpi_Tbl_Type;
44
45 g_source varchar2(10);
46 g_invalid_level varchar2(50);
47
48 procedure Bsc_Pmf_Ui_Api(
49 p_commit IN varchar2 := FND_API.G_TRUE
50 ,p_Bsc_Pmf_Ui_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Ui_Rec_Type
51 ,p_Bsc_Pmf_Dim_Tbl IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Tbl_Type
52 ,p_Dim_Count IN number
53 ,x_bad_level OUT NOCOPY varchar2
54 ,x_return_status OUT NOCOPY varchar2
55 ,x_msg_count OUT NOCOPY number
56 ,x_msg_data OUT NOCOPY varchar2
57 ) is
58
59 begin
60
61 -- Delete all leftover values from global variables.
62 g_Bsc_Pmf_Ui_Rec := null;
63 g_Bsc_Dim_Rec := null;
64 g_Bsc_Dim_Group_Rec := null;
65 g_Bsc_Dataset_Rec := null;
66 g_Bsc_Dimset_Rec := null;
67 g_Bsc_Anal_Opt_Rec := null;
68 -- g_Bsc_Kpi_Rec := null;
69
70
71 for i in 1..g_Bsc_Pmf_Dim_Tbl.count loop
72 g_Bsc_Pmf_Dim_Tbl.delete(i);
73 end loop;
74
75 /*
76 for i in 1..g_Bsc_Kpi_Tbl.count loop
77 g_Bsc_Kpi_Tbl.delete(i);
78 end loop;
79 */
80
81 if p_Bsc_Pmf_Ui_Rec.Kpi_Id is null then
82 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KPI_ID_ENTERED');
83 FND_MESSAGE.SET_TOKEN('BSC_KPI', p_Bsc_Pmf_Ui_Rec.Kpi_Id);
84 FND_MSG_PUB.ADD;
85 RAISE FND_API.G_EXC_ERROR;
86 end if;
87
88 g_Bsc_Pmf_Ui_Rec.Kpi_Id := p_Bsc_Pmf_Ui_Rec.Kpi_Id;
89 g_Bsc_Pmf_Ui_Rec.Kpi_Group_Id := p_Bsc_Pmf_Ui_Rec.Kpi_Group_Id;
90 g_Bsc_Pmf_Ui_Rec.Tab_Id := p_Bsc_Pmf_Ui_Rec.Tab_Id;
91 g_Bsc_Pmf_Ui_Rec.Option_Name := p_Bsc_Pmf_Ui_Rec.Option_Name;
92 g_Bsc_Pmf_Ui_Rec.Option_Description := p_Bsc_Pmf_Ui_Rec.Option_Description;
93
94 Get_Measure_Long_Name( p_commit
95 ,p_Bsc_Pmf_Ui_Rec
96 ,x_return_status
97 ,x_msg_count
98 ,x_msg_data);
99
100 Modify_Passed_Parameters( p_commit
101 ,p_Bsc_Pmf_Ui_Rec
102 ,p_Bsc_Pmf_Dim_Tbl
103 ,p_Dim_Count
104 ,x_return_status
105 ,x_msg_count
106 ,x_msg_data);
107
108
109
110 Create_Bsc_Dimension( p_commit
111 ,p_Bsc_Pmf_Dim_Tbl
112 ,p_Dim_Count
113 ,x_return_status
114 ,x_msg_count
115 ,x_msg_data);
116
117
118
119 Create_Bsc_Dataset( p_commit
120 ,p_Bsc_Pmf_Ui_Rec
121 ,x_return_status
122 ,x_msg_count
123 ,x_msg_data);
124
125 Update_Bsc_Dataset( p_commit
126 ,p_Bsc_Pmf_Ui_Rec
127 ,x_return_status
128 ,x_msg_count
129 ,x_msg_data);
130
131
132
133 Create_Bsc_Dimension_Set( p_commit
134 ,p_Bsc_Pmf_Ui_Rec
135 ,p_Bsc_Pmf_Dim_Tbl
136 ,p_Dim_Count
137 ,x_return_status
138 ,x_msg_count
139 ,x_msg_data);
140
141 EXCEPTION
142 WHEN FND_API.G_EXC_ERROR THEN
143 rollback;
144 -- if the error is and invalid level we won't raise, we need to pass value of invalid
145 -- level, else raise.
146 if(g_invalid_level is not null) then
147 x_bad_level := g_invalid_level;
148 x_return_status := FND_API.G_RET_STS_ERROR;
149 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
150 ,p_data => x_msg_data);
151 else
152 x_bad_level := null;
153 x_return_status := FND_API.G_RET_STS_ERROR;
154 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
155 ,p_data => x_msg_data);
156 raise;
157 end if;
158 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
159 rollback;
160 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
161 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
162 ,p_data => x_msg_data);
163 raise;
164 WHEN NO_DATA_FOUND THEN
165 rollback;
166 x_return_status := FND_API.G_RET_STS_ERROR;
167 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
168 ,p_data => x_msg_data);
169 raise;
170 WHEN OTHERS THEN
171 rollback;
172 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
173 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
174 ,p_data => x_msg_data);
175 raise;
176
177 end Bsc_Pmf_Ui_Api;
178
179 /************************************************************************************
180 ************************************************************************************/
181
182 procedure Get_Measure_Long_Name(
183 p_commit IN varchar2 := FND_API.G_TRUE
184 ,p_Bsc_Pmf_Ui_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Ui_Rec_Type
185 ,x_return_status OUT NOCOPY varchar2
186 ,x_msg_count OUT NOCOPY number
187 ,x_msg_data OUT NOCOPY varchar2
188 ) is
189
190 begin
191
192 if p_Bsc_Pmf_Ui_Rec.Measure_Short_Name is null then
193 FND_MESSAGE.SET_NAME('BSC','BSC_NO_SHORT_NAME');
194 FND_MESSAGE.SET_TOKEN('BSC_SHORT_NAME', p_Bsc_Pmf_Ui_Rec.Measure_Short_Name);
195 FND_MSG_PUB.ADD;
196 RAISE FND_API.G_EXC_ERROR;
197 end if;
198
199 g_Bsc_Pmf_Ui_Rec.Measure_Short_Name := p_Bsc_Pmf_Ui_Rec.Measure_Short_Name;
200
201 select distinct(name)
202 into g_Bsc_Pmf_Ui_Rec.Measure_Long_Name
203 from bis_indicators_vl
204 where short_name = p_Bsc_Pmf_Ui_Rec.Measure_Short_Name;
205
206 -- g_Bsc_Dataset_Rec.Bsc_Measure_Long_Name := g_Bsc_Pmf_Ui_Rec.Measure_Long_Name;
207 g_Bsc_Dataset_Rec.Bsc_Measure_Short_Name := g_Bsc_Pmf_Ui_Rec.Measure_Short_Name;
208 g_Bsc_Dataset_Rec.Bsc_Measure_Long_Name := g_Bsc_Pmf_Ui_Rec.Option_Name;
209
210
211 EXCEPTION
212 WHEN FND_API.G_EXC_ERROR THEN
213 rollback;
214 x_return_status := FND_API.G_RET_STS_ERROR;
215 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
216 ,p_data => x_msg_data);
217 raise;
218 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
219 rollback;
220 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
221 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
222 ,p_data => x_msg_data);
223 raise;
224 WHEN NO_DATA_FOUND THEN
225 rollback;
226 x_return_status := FND_API.G_RET_STS_ERROR;
227 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
228 ,p_data => x_msg_data);
229 raise;
230 WHEN OTHERS THEN
231 rollback;
232 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
233 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
234 ,p_data => x_msg_data);
235 raise;
236
237 end Get_Measure_Long_Name;
238
239 /************************************************************************************
240 ************************************************************************************/
241
242 procedure Get_Dimension_Long_Name(
243 p_commit IN varchar2 := FND_API.G_TRUE
244 ,p_Bsc_Pmf_Ui_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Ui_Rec_Type
245 ,x_return_status OUT NOCOPY varchar2
246 ,x_msg_count OUT NOCOPY number
247 ,x_msg_data OUT NOCOPY varchar2
248 ) is
249
250 begin
251
252 select distinct(dimension_name)
253 into g_Bsc_Pmf_Ui_Rec.Dimension_Long_Name
254 from bisfv_dimensions
255 where dimension_short_name = p_Bsc_Pmf_Ui_Rec.Dimension_Short_Name;
256
257
258
259
260 EXCEPTION
261 WHEN FND_API.G_EXC_ERROR THEN
262 rollback;
263 x_return_status := FND_API.G_RET_STS_ERROR;
264 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
265 ,p_data => x_msg_data);
266 raise;
267 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
268 rollback;
269 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
270 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
271 ,p_data => x_msg_data);
272 raise;
273 WHEN NO_DATA_FOUND THEN
274 rollback;
275 x_return_status := FND_API.G_RET_STS_ERROR;
276 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
277 ,p_data => x_msg_data);
278 raise;
279 WHEN OTHERS THEN
280 rollback;
281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
282 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
283 ,p_data => x_msg_data);
284 raise;
285
286 end Get_Dimension_Long_Name;
287
288 /************************************************************************************
289 ************************************************************************************/
290
291 /*
292 procedure Get_Dimension_Level_Name(
293 p_commit IN varchar2 := FND_API.G_TRUE
294 ,p_Bsc_Pmf_Ui_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Ui_Rec_Type
295 ,x_return_status OUT NOCOPY varchar2
296 ,x_msg_count OUT NOCOPY number
297 ,x_msg_data OUT NOCOPY varchar2
298 ) is
299
300 begin
301
302 g_Bsc_Pmf_Ui_Rec.Dimension_Short_Name := p_Bsc_Pmf_Ui_Rec.Dimension_Short_Name;
303
304 EXCEPTION
305 WHEN FND_API.G_EXC_ERROR THEN
306 rollback;
307 x_return_status := FND_API.G_RET_STS_ERROR;
308 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
309 ,p_data => x_msg_data);
310 raise;
311 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
312 rollback;
313 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
314 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
315 ,p_data => x_msg_data);
316 raise;
317 WHEN NO_DATA_FOUND THEN
318 rollback;
319 x_return_status := FND_API.G_RET_STS_ERROR;
320 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
321 ,p_data => x_msg_data);
322 raise;
323 WHEN OTHERS THEN
324 rollback;
325 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
326 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
327 ,p_data => x_msg_data);
328 raise;
329
330 end Get_Dimension_Level_Name;
331 */
332
333 /************************************************************************************
334 ************************************************************************************/
335
336 procedure Modify_Passed_Parameters(
337 p_commit IN varchar2 := FND_API.G_TRUE
338 ,p_Bsc_Pmf_Ui_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Ui_Rec_Type
339 ,p_Bsc_Pmf_Dim_Tbl IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Tbl_Type
340 ,p_Dim_Count IN number
341 ,x_return_status OUT NOCOPY varchar2
342 ,x_msg_count OUT NOCOPY number
343 ,x_msg_data OUT NOCOPY varchar2
344 ) is
345
346 TYPE Recdc_value IS REF CURSOR;
347 dc_value Recdc_value;
348 dc_value1 Recdc_value;
349
350 no_dim_level exception;
351
352 l_alternate_level_view varchar2(30);
353 l_sql varchar2(1000);
354 l_sql1 varchar2(1000);
355 l_owner VARCHAR2(256);
356
357 begin
358
359 g_invalid_level := null;
360
361 -- Set and modify the passed Record for measure and Dimension.
362 g_Bsc_Pmf_Ui_Rec.Measure_Short_Name := p_Bsc_Pmf_Ui_Rec.Measure_Short_Name;
363
364 -- Set and modify the passed Table for Dimension and Dimension Levels.
365 for i in 1..p_Bsc_Pmf_Dim_Tbl.count loop
366
367 -- Set the dimension level short name and get the dimension level long name.
368 if p_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Short_Name is null then
369 raise no_dim_level;
370 end if;
371
372 g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Short_Name := p_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Short_Name;
373 g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Status := p_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Status;
374
375 select distinct source
376 -- into g_source
377 into g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Source
378 from bisfv_dimension_levels
379 where upper(dimension_level_short_name) = upper(p_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Short_Name);
380
381 if g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Source = 'OLTP' then
382 select distinct dimension_level_name, level_values_view_name, 'ID', 'value'
383 -- select distinct dimension_level_name, level_values_view_name, 'rownum', 'value'
384 into g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Long_Name,
385 g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_View_Name,
386 g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Pk_Key,
387 g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Name_Column
388 from bisbv_dimension_levels
389 where upper(dimension_level_short_name) = upper(p_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Short_Name);
390
391 -- added as a request by PM to fix bug# 2598829
392 g_invalid_level := g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Long_Name;
393
394 l_sql := 'select max(length(value)) ' ||
395 'from ' || g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_View_Name;
396
397 open dc_value for l_sql;
398 fetch dc_value into g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Disp_Size;
399 close dc_value;
400
401
402 else
403
404 select distinct dimension_level_name
405 ,dimension_level_short_name || '_LTC'
406 ,level_values_view_name
407 into g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Long_Name
408 ,g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_View_Name
409 ,l_alternate_level_view
410 from bisfv_dimension_levels
411 where dimension_level_short_name = p_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Short_Name;
412
413 if l_alternate_level_view is not null then
414 g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_View_Name := l_alternate_level_view;
415 g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Pk_Key_edw := 'ID';
416 g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Name_Column := 'VALUE';
417
418 -- added as a request by PM to fix bug# 2598829
419 -- g_invalid_level := p_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Short_Name;
420 g_invalid_level := g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Long_Name;
421
422
423 else
424
425 /*
426 -- Changed to dynamic sql, in case EDW has not been installed.
427 l_sql1 := ' select distinct level_table_col_name ' ||
428 ' from edw_level_Table_atts_md_v ' ||
429 ' where key_type=''UK'' and ' ||
430 ' upper(level_Table_name) = upper(''' || g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_View_Name || ''') and ' ||
431 ' upper(level_table_col_name) like ''%PK_KEY%''';
432 */
433
434 -- Change to query data dictionary due to EDW APIs not being there.
435 l_owner := bsc_utility.get_owner_for_object(g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_View_Name);
436
437 l_sql1 := ' SELECT column_name ' ||
438 ' FROM ALL_TAB_COLUMNS ' ||
439 ' WHERE table_name = UPPER(:1) AND ' ||
440 ' column_name LIKE ''%PK_KEY%'''||
441 ' AND OWNER = :2 ';
442 open dc_value1 for l_sql1 using g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_View_Name, l_owner;
443 fetch dc_value1 into g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Pk_Key_edw;
444 close dc_value1;
445
446 /*
447 -- Changed to dynamic sql, in case EDW has not been installed.
448 l_sql1 := ' select level_table_col_name ' ||
449 ' from edw_level_Table_atts_md_v ' ||
450 ' where upper(level_Table_name) = upper(''' || g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_View_Name || ''') and ' ||
451 ' (upper(level_table_col_name) like ''%DESCRIPTION%'' or ' ||
452 ' upper(level_table_col_name) like ''NAME%'') and ' ||
453 ' rownum < 2';
454 */
455
456 -- Change to query data dictionary due to EDW APIs not being there.
457 l_sql1 := ' select column_name ' ||
458 ' from ALL_TAB_COLUMNS ' ||
459 ' where table_name = upper(:1) and ' ||
460 ' (column_name like ''%DESCRIPTION%'' or ' ||
461 ' column_name like ''NAME%'') ' ||
462 ' AND OWNER = :2 '||
463 ' AND rownum < 2';
464
465
466 open dc_value1 for l_sql1 using g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_View_Name, l_owner;
467 fetch dc_value1 into g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Name_Column;
468 close dc_value1;
469
470 end if;
471
472 l_sql := 'select max(length(' || g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Name_Column || ')) ' ||
473 'from ' || g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_View_Name;
474
475 open dc_value for l_sql;
476 fetch dc_value into g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Disp_Size;
477 close dc_value;
478
479
480
481 end if;
482
483 -- Double the size of the Level Display Size if under 125;
484 if g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Disp_Size < 125 then
485 g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Disp_Size := g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Disp_Size * 2;
486 end if;
487
488 end loop;
489
490 -- If execution has come this far then clear out NOCOPY g_invalid_level variable.
491 g_invalid_level := null;
492
493 EXCEPTION
494 WHEN FND_API.G_EXC_ERROR THEN
495 rollback;
496 x_return_status := FND_API.G_RET_STS_ERROR;
497 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
498 ,p_data => x_msg_data);
499 raise;
500 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
501 rollback;
502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
503 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
504 ,p_data => x_msg_data);
505 raise;
506 WHEN NO_DATA_FOUND THEN
507 rollback;
508 x_return_status := FND_API.G_RET_STS_ERROR;
509 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
510 ,p_data => x_msg_data);
511 raise;
512 WHEN OTHERS THEN
513 rollback;
514 if g_invalid_level is not null then
515 FND_MESSAGE.SET_NAME('BSC','BSC_UNAVAILABLE_LEVEL');
516 FND_MESSAGE.SET_TOKEN('BSC_LEVEL', g_invalid_level);
517 FND_MSG_PUB.ADD;
518 RAISE FND_API.G_EXC_ERROR;
519 else
520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
521 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
522 ,p_data => x_msg_data);
523 raise;
524 end if;
525
526 end Modify_Passed_Parameters;
527
528 /************************************************************************************
529 ************************************************************************************/
530
531 procedure Create_Bsc_Dimension(
532 p_commit IN varchar2 := FND_API.G_TRUE
533 ,p_Bsc_Pmf_Dim_Tbl IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Tbl_Type
534 ,p_Dim_Count IN number
535 ,x_return_status OUT NOCOPY varchar2
536 ,x_msg_count OUT NOCOPY number
537 ,x_msg_data OUT NOCOPY varchar2
538 ) is
539
540 begin
541
542
543 /* from now on all the dimensions will be attached to single dim group
544 generate the unique group name here and attach all the dim levels to it */
545
546 g_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Id := BSC_DIMENSION_GROUPS_PUB.Get_Next_Value('BSC_SYS_DIM_GROUPS_TL'
547 ,'DIM_GROUP_ID');
548
549 g_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Name := 'Dgrp_'||g_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Id;
550 g_Bsc_Dim_Group_Rec.Bsc_Language := 'US';
551 g_Bsc_Dim_Group_Rec.Bsc_Source_Language := 'US';
552
553
554 for i in 1..g_Bsc_Pmf_Dim_Tbl.count loop
555
556 -- Set values for Dimension Level in BSC.
557 g_Bsc_Dim_Rec.Bsc_Level_Short_Name := g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Short_Name;
558 g_Bsc_Dim_Rec.Bsc_Dim_Level_Long_Name := g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Long_Name;
559 g_Bsc_Dim_Rec.Bsc_Level_Disp_Key_Size := g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Disp_Size;
560 g_Bsc_Dim_Rec.Bsc_Level_Name := get_Dim_Level_View_Name(g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Short_Name);
561 g_Bsc_Dim_Rec.Bsc_Level_View_Name := g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_View_Name;
562 -- if g_source = 'OLTP' then
563 if g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Source = 'OLTP' then
564 g_Bsc_Dim_Rec.Bsc_Level_Pk_Key := g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Pk_Key;
565 else
566 g_Bsc_Dim_Rec.Bsc_Level_Pk_Key := g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Pk_Key_edw;
567 end if;
568 -- if g_source = 'OLTP' then
569 if g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Source = 'OLTP' then
570 g_Bsc_Dim_Rec.Bsc_Pk_Col := 'ID';
571 else
572 g_Bsc_Dim_Rec.Bsc_Pk_Col := g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Pk_Key_edw;
573 end if;
574 /*
575 if g_source = 'OLTP' then
576 g_Bsc_Dim_Rec.Bsc_Pk_Col := g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Pk_Key;
577 else
578 g_Bsc_Dim_Rec.Bsc_Pk_Col := g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Pk_Key_edw;
579 end if;
580 */
581 g_Bsc_Dim_Rec.Bsc_Source := 'PMF';
582 g_Bsc_Dim_Rec.Source := g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Source; /* Added to fix 2674365 */
583
584 g_Bsc_Dim_Rec.Bsc_Level_Name_Column := g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Name_Column;
585 -- g_Bsc_Dim_Rec.Bsc_Kpi_Id := g_Bsc_Pmf_Ui_Rec.Kpi_Id;
586 g_Bsc_Dim_Rec.Bsc_Language := 'US';
587 g_Bsc_Dim_Rec.Bsc_Source_Language := 'US';
588
589 BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level( FND_API.G_TRUE
590 ,g_Bsc_Dim_Rec
591 ,x_return_status
592 ,x_msg_count
593 ,x_msg_data);
594
595 -- Set values for Dimension Group in BSC.
596 --g_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Name := 'Dgrp ' || lower(replace(g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Short_Name, '_', ' '));
597 --g_Bsc_Dim_Group_Rec.Bsc_Language := 'US';
598 --g_Bsc_Dim_Group_Rec.Bsc_Source_Language := 'US';
599
600
601 -- Get the Id for the recently created Dimension (Level) in BSC.
602 select distinct dim_level_id
603 into g_Bsc_Dim_Group_Rec.Bsc_Level_Id
604 from BSC_SYS_DIM_LEVELS_B
605 where SHORT_NAME = g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Short_Name;
606
607
608 -- Create a Dimension Group for all Dimension Level.
609 BSC_DIMENSION_GROUPS_PUB.Create_Dimension_Group( FND_API.G_TRUE
610 ,g_Bsc_Dim_Group_Rec
611 ,x_return_status
612 ,x_msg_count
613 ,x_msg_data);
614
615
616 end loop;
617
618 EXCEPTION
619 WHEN FND_API.G_EXC_ERROR THEN
620 rollback;
621 x_return_status := FND_API.G_RET_STS_ERROR;
622 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
623 ,p_data => x_msg_data);
624 raise;
625 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
626 rollback;
627 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
628 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
629 ,p_data => x_msg_data);
630 raise;
631 WHEN NO_DATA_FOUND THEN
632 rollback;
633 x_return_status := FND_API.G_RET_STS_ERROR;
634 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
635 ,p_data => x_msg_data);
636 raise;
637 WHEN OTHERS THEN
638 rollback;
639 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
640 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
641 ,p_data => x_msg_data);
642 raise;
643
644 end Create_Bsc_Dimension;
645
646 /************************************************************************************
647 ************************************************************************************/
648 procedure Update_Bsc_Dataset(
649 p_commit IN VARCHAR2 := FND_API.G_TRUE
650 ,p_Bsc_Pmf_Ui_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Ui_Rec_Type
651 ,x_return_status OUT NOCOPY VARCHAR2
652 ,x_msg_count OUT NOCOPY NUMBER
653 ,x_msg_data OUT NOCOPY VARCHAR2
654 ) is
655
656
657 l_language VARCHAR2(2000);
658 l_Dataset_Rec BSC_DATASETS_PUB.Bsc_Dataset_Rec_Type;
659 l_Measure_Id NUMBER;
660 l_dataset_id NUMBER;
661
662 CURSOR c_language IS
663 SELECT language_code
664 FROM fnd_languages
665 WHERE installed_flag IN ('I','B') AND language_code <> USERENV('LANG');
666
667 BEGIN
668
669 l_Dataset_Rec.Bsc_Measure_Short_Name := p_Bsc_Pmf_Ui_Rec.Measure_Short_Name;
670
671 SELECT A.MEASURE_ID, B.DATASET_ID
672 INTO l_Measure_Id,
673 l_dataset_id
674 FROM BSC_SYS_MEASURES A,
675 BSC_SYS_DATASETS_B B
676 WHERE A.SHORT_NAME = l_Dataset_Rec.Bsc_Measure_Short_Name
677 AND A.SOURCE = 'PMF'
678 AND A.MEASURE_ID = B.MEASURE_ID1;
679
680 IF (l_dataset_id IS NOT NULL) THEN
681
682 l_Dataset_Rec.Bsc_Dataset_Id := l_dataset_id;
683
684 IF (c_language%ISOPEN) THEN
685 CLOSE c_language;
686 END IF;
687
688 OPEN c_language;
689
690 LOOP
691 FETCH c_language INTO l_language;
692 EXIT WHEN c_language%NOTFOUND;
693
694 SELECT T.NAME,
695 T.DESCRIPTION,
696 T.SOURCE_LANG
697 INTO l_Dataset_Rec.Bsc_Dataset_Name,
698 l_Dataset_Rec.Bsc_Dataset_Help,
699 l_Dataset_Rec.Bsc_Source_Language
700 FROM BIS_INDICATORS_TL T,
701 BIS_INDICATORS B
702 WHERE T.INDICATOR_ID = B.INDICATOR_ID
703 AND B.SHORT_NAME = l_Dataset_Rec.Bsc_Measure_Short_Name
704 AND T.LANGUAGE = l_language;
705
706 BSC_DATASETS_PUB.Update_Dataset
707 (
708 p_commit => p_commit
709 , p_Dataset_Rec => l_Dataset_Rec
710 , p_update_dset_calc => FALSE
711 , x_return_status => x_return_status
712 , x_msg_count => x_msg_count
713 , x_msg_data => x_msg_data
714 );
715
716 END LOOP;
717 CLOSE c_language;
718 IF (p_commit = FND_API.G_TRUE) THEN
719 COMMIT;
720 END if;
721 END IF;
722
723 EXCEPTION
724 WHEN FND_API.G_EXC_ERROR THEN
725 IF (c_language%ISOPEN) THEN
726 CLOSE c_language;
727 END IF;
728 rollback;
729 x_return_status := FND_API.G_RET_STS_ERROR;
730 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
731 ,p_data => x_msg_data);
732 raise;
733 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
734 IF (c_language%ISOPEN) THEN
735 CLOSE c_language;
736 END IF;
737
738 rollback;
739 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
740 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
741 ,p_data => x_msg_data);
742 raise;
743 WHEN NO_DATA_FOUND THEN
744 IF (c_language%ISOPEN) THEN
745 CLOSE c_language;
746 END IF;
747
748 rollback;
749 x_return_status := FND_API.G_RET_STS_ERROR;
750 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
751 ,p_data => x_msg_data);
752 raise;
753 WHEN OTHERS THEN
754 IF (c_language%ISOPEN) THEN
755 CLOSE c_language;
756 END IF;
757 rollback;
758 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
759 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
760 ,p_data => x_msg_data);
761 raise;
762
763 END Update_Bsc_Dataset;
764 /***************************************************************************************/
765
766
767 procedure Create_Bsc_Dataset(
768 p_commit IN varchar2 := FND_API.G_TRUE
769 ,p_Bsc_Pmf_Ui_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Ui_Rec_Type
770 ,x_return_status OUT NOCOPY varchar2
771 ,x_msg_count OUT NOCOPY number
772 ,x_msg_data OUT NOCOPY varchar2
773 ) is
774
775 --Bug 2677766
776 l_bsc_format_id number;
777 l_measure_col BSC_SYS_MEASURES.MEASURE_COL%TYPE;
778 begin
779
780 g_Bsc_Dataset_Rec.Bsc_Measure_Short_Name := p_Bsc_Pmf_Ui_Rec.Measure_Short_Name;
781 g_Bsc_Dataset_Rec.Bsc_Measure_Operation := 'SUM';
782 --g_Bsc_Dataset_Rec.Bsc_Measure_Col := p_Bsc_Pmf_Ui_Rec.Measure_Short_Name;
783 g_Bsc_Dataset_Rec.Bsc_Language := 'US';
784 g_Bsc_Dataset_Rec.Bsc_Source_Language := 'US';
785 g_Bsc_Dataset_Rec.Bsc_Source := 'PMF';
786 g_Bsc_Dataset_Rec.Bsc_Dataset_Name := g_Bsc_Pmf_Ui_Rec.Measure_Long_Name;
787 l_measure_col := BSC_BIS_MEASURE_PUB.get_measure_col(g_Bsc_Dataset_Rec.Bsc_Dataset_Name, NULL, NULL,g_Bsc_Dataset_Rec.Bsc_Measure_Short_Name);
788 if (l_measure_col is not null) then
789 g_Bsc_Dataset_Rec.Bsc_Measure_Col := l_measure_col;
790 else
791 g_Bsc_Dataset_Rec.Bsc_Measure_Col := g_Bsc_Dataset_Rec.Bsc_Measure_Short_Name;
792 end if;
793 --Bug 2677766
794 BSC_BIS_WRAPPER_PVT.get_bsc_format_id( p_measure_shortname => p_Bsc_Pmf_Ui_Rec.Measure_Short_Name
795 ,x_bsc_format_id => l_bsc_format_id);
796 if l_bsc_format_id is null then
797 l_bsc_format_id := 5;
798 end if;
799 g_Bsc_Dataset_Rec.Bsc_Dataset_Format_Id := l_bsc_format_id;
800 --end 2677766
801
802 BSC_DATASETS_PUB.Create_Measures( FND_API.G_TRUE
803 ,g_Bsc_Dataset_Rec
804 ,x_return_status
805 ,x_msg_count
806 ,x_msg_data);
807 commit;
808
809
810 EXCEPTION
811 WHEN FND_API.G_EXC_ERROR THEN
812 rollback;
813 x_return_status := FND_API.G_RET_STS_ERROR;
814 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
815 ,p_data => x_msg_data);
816 raise;
817 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
818 rollback;
819 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
820 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
821 ,p_data => x_msg_data);
822 raise;
823 WHEN NO_DATA_FOUND THEN
824 rollback;
825 x_return_status := FND_API.G_RET_STS_ERROR;
826 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
827 ,p_data => x_msg_data);
828 raise;
829 WHEN OTHERS THEN
830 rollback;
831 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
832 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
833 ,p_data => x_msg_data);
834 raise;
835
836 end Create_Bsc_Dataset;
837
838 /************************************************************************************
839 ************************************************************************************/
840
841 procedure Create_Bsc_Dimension_Set(
842 p_commit IN varchar2 := FND_API.G_TRUE
843 ,p_Bsc_Pmf_Ui_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Ui_Rec_Type
844 ,p_Bsc_Pmf_Dim_Tbl IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Tbl_Type
845 ,p_Dim_Count IN number
846 ,x_return_status OUT NOCOPY varchar2
847 ,x_msg_count OUT NOCOPY number
848 ,x_msg_data OUT NOCOPY varchar2
849 ) is
850
851 begin
852
853 -- Get the next dimension set id for the current dimension set.
854 select max(dim_set_id) + 1
855 into g_Bsc_Dimset_Rec.Bsc_Dim_Set_Id
856 from BSC_KPI_DIM_SETS_TL
857 where indicator = g_Bsc_Pmf_Ui_Rec.Kpi_Id;
858
859 -- Set the record parameter Bsc_New_Dset to 'Y'. This tells the Dimension
860 -- set API that this is a new Dim set. Set it to 'N' after the first call to
861 -- the Dim Set.
862 g_Bsc_Dimset_Rec.Bsc_New_Dset := 'Y';
863
864 for i in 1..g_Bsc_Pmf_Dim_Tbl.count loop
865
866 -- g_Bsc_Dimset_Rec.Source_Level_Short_Name := g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Short_Name;
867 -- g_Bsc_Dimset_Rec.Source_Level_Long_Name := g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Long_Name;
868 g_Bsc_Dimset_Rec.Bsc_Level_Name := get_Dim_Level_View_Name(g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Short_Name);
869 g_Bsc_Dimset_Rec.Bsc_Dset_Default_Value := g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Status;
870 select TOTAL_DISP_NAME, COMP_DISP_NAME
871 into g_Bsc_Dimset_Rec.Bsc_Dim_Tot_Disp_Name,
872 g_Bsc_Dimset_Rec.Bsc_Dim_Comp_Disp_Name
873 from BSC_SYS_DIM_LEVELS_VL
874 where SHORT_NAME = g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Short_Name;
875
876 -- g_Bsc_Dimset_Rec.Bsc_Level_View_Name := g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_View_Name;
877 -- g_Bsc_Dimset_Rec.Bsc_Level_Pk_Key := g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Pk_Key;
878 -- g_Bsc_Dimset_Rec.Bsc_Level_Name_Column := g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Name_Column;
879 g_Bsc_Dimset_Rec.Bsc_Kpi_Id := g_Bsc_Pmf_Ui_Rec.Kpi_Id;
880 g_Bsc_Dimset_Rec.Bsc_Language := 'US';
881 g_Bsc_Dimset_Rec.Bsc_Source_Language := 'US';
882 g_Bsc_Dimset_Rec.Bsc_Dim_Level_Group_Index := i;
883
884 --set the name of the group using the dimension level record.
885 g_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Name := 'Dgrp ' || lower(replace(g_Bsc_Pmf_Dim_Tbl(i).Dimension_Level_Short_Name, '_', ' '));
886
887
888 -- Get the group Id for the current dimension level.
889 select distinct dim_group_id
890 into g_Bsc_Dimset_Rec.Bsc_Dim_Level_Group_Id
891 from BSC_SYS_DIM_GROUPS_VL
892 where upper(name) = upper(g_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Name);
893
894
895 -- Call the BSC API to Populate Dimension sets.
896 BSC_DIMENSION_SETS_PUB.Create_Dim_Group_In_Dset( FND_API.G_TRUE
897 ,g_Bsc_Dimset_Rec
898 ,x_return_status
899 ,x_msg_count
900 ,x_msg_data);
901
902 g_Bsc_Dimset_Rec.Bsc_New_Dset := 'N';
903
904 end loop;
905
906 -- Get the Dataset Id for the current PMF Measure. This Dataset Id was set in the
907 -- Create_Bsc_Dataset procedure.
908 select distinct a.dataset_id
909 into g_Bsc_Anal_Opt_Rec.Bsc_Dataset_Id
910 from BSC_SYS_DATASETS_B a,
911 BSC_SYS_MEASURES b
912 where upper(b.short_name) = upper(p_Bsc_Pmf_Ui_Rec.Measure_Short_Name)
913 and a.measure_id1 = b.measure_id
914 and rownum < 2;
915
916 g_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id := p_Bsc_Pmf_Ui_Rec.Kpi_Id;
917 g_Bsc_Anal_Opt_Rec.Bsc_Dim_Set_Id := g_Bsc_Dimset_Rec.Bsc_Dim_Set_Id;
918
919
920 -- Call the procedure that will create the Analysis Option.
921 Create_Bsc_Analysis_Option( p_commit
922 ,x_return_status
923 ,x_msg_count
924 ,x_msg_data);
925
926 EXCEPTION
927 WHEN FND_API.G_EXC_ERROR THEN
928 rollback;
929 x_return_status := FND_API.G_RET_STS_ERROR;
930 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
931 ,p_data => x_msg_data);
932 raise;
933 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
934 rollback;
935 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
936 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
937 ,p_data => x_msg_data);
938 raise;
939 WHEN NO_DATA_FOUND THEN
940 rollback;
941 x_return_status := FND_API.G_RET_STS_ERROR;
942 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
943 ,p_data => x_msg_data);
944 raise;
945 WHEN OTHERS THEN
946 rollback;
947 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
948 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
949 ,p_data => x_msg_data);
950 raise;
951
952 end Create_Bsc_Dimension_Set;
953
954 /************************************************************************************
955 ************************************************************************************/
956
957 procedure Create_Bsc_Analysis_Option(
958 p_commit IN varchar2 := FND_API.G_TRUE
959 ,x_return_status OUT NOCOPY varchar2
960 ,x_msg_count OUT NOCOPY number
961 ,x_msg_data OUT NOCOPY varchar2
962 ) is
963
964 l_count number;
965
966 begin
967
968 -- Set the values for Option Properties.
969 g_Bsc_Anal_Opt_Rec.Bsc_Analysis_Group_Id := 0;
970 g_Bsc_Anal_Opt_Rec.Bsc_Parent_Option_Id := 0;
971 g_Bsc_Anal_Opt_Rec.Bsc_Grandparent_Option_Id := 0;
972 g_Bsc_Anal_Opt_Rec.Bsc_Option_Group1 := 0;
973 g_Bsc_Anal_Opt_Rec.Bsc_Option_Group2 := 0;
974 g_Bsc_Anal_Opt_Rec.Bsc_Language := 'US';
975 g_Bsc_Anal_Opt_Rec.Bsc_Source_Language := 'US';
976 g_Bsc_Anal_Opt_Rec.Bsc_Option_Name := g_Bsc_Pmf_Ui_Rec.Option_Name;
977 g_Bsc_Anal_Opt_Rec.Bsc_Option_Help := g_Bsc_Pmf_Ui_Rec.Option_Description;
978
979 select count(option_id) + 1
980 into g_Bsc_Anal_Opt_Rec.Bsc_Option_Group0
981 from BSC_KPI_ANALYSIS_OPTIONS_B
982 where indicator = g_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
983 and analysis_group_id = g_Bsc_Anal_Opt_Rec.Bsc_Analysis_Group_Id;
984
985 -- Need to create Analysis Options for this KPI. But first we need to determine
986 -- if the KPI only has 1 option, and if this option is the default option. If it
987 -- is then we need to replace it.
988 select count(option_id)
989 into l_count
990 from BSC_KPI_ANALYSIS_OPTIONS_B
991 where indicator = g_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
992 and analysis_group_id = g_Bsc_Anal_Opt_Rec.Bsc_Analysis_Group_Id;
993
994 -- if there's only one option then check if this is the default option.
995 if l_count = 1 then
996 select count(option_id)
997 into l_count
998 from BSC_KPI_ANALYSIS_OPTIONS_VL
999 where name = 'Option 0'
1000 and indicator = g_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id;
1001
1002 -- now double check by checking the dataset id.
1003 if l_count = 1 then
1004 select dataset_id
1005 into l_count
1006 from BSC_KPI_ANALYSIS_MEASURES_B
1007 where indicator = g_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id
1008 and analysis_option0 = 0
1009 and analysis_option1 = g_Bsc_Anal_Opt_Rec.Bsc_Option_Group1
1010 and analysis_option2 = g_Bsc_Anal_Opt_Rec.Bsc_Option_Group2
1011 and series_id = 0;
1012 if l_count = -1 then
1013 -- If we've come this far then we need to update the default Option.
1014 g_Bsc_Anal_Opt_Rec.Bsc_Analysis_Option_Id := 0;
1015 g_Bsc_Anal_Opt_Rec.Bsc_Option_Group0 := 0;
1016 BSC_ANALYSIS_OPTION_PUB.Update_Analysis_Options( FND_API.G_TRUE
1017 ,g_Bsc_Anal_Opt_Rec
1018 ,x_return_status
1019 ,x_msg_count
1020 ,x_msg_data);
1021
1022 -- Now we need to change the defaults from BSC values to PMF values.
1023 BSC_DESIGNER_PVT.Deflt_RefreshKpi(g_Bsc_Anal_Opt_Rec.Bsc_Kpi_Id);
1024
1025 else
1026 -- Call procedure to create Analysis Option.
1027 BSC_ANALYSIS_OPTION_PUB.Create_Analysis_Options( FND_API.G_TRUE
1028 ,g_Bsc_Anal_Opt_Rec
1029 ,x_return_status
1030 ,x_msg_count
1031 ,x_msg_data);
1032 end if;
1033 else
1034 -- Call procedure to create Analysis Option.
1035 BSC_ANALYSIS_OPTION_PUB.Create_Analysis_Options( FND_API.G_TRUE
1036 ,g_Bsc_Anal_Opt_Rec
1037 ,x_return_status
1038 ,x_msg_count
1039 ,x_msg_data);
1040
1041 end if;
1042
1043 else
1044
1045 -- Call procedure to create Analysis Option.
1046 BSC_ANALYSIS_OPTION_PUB.Create_Analysis_Options( FND_API.G_TRUE
1047 ,g_Bsc_Anal_Opt_Rec
1048 ,x_return_status
1049 ,x_msg_count
1050 ,x_msg_data);
1051 end if;
1052
1053 EXCEPTION
1054 WHEN FND_API.G_EXC_ERROR THEN
1055 rollback;
1056 x_return_status := FND_API.G_RET_STS_ERROR;
1057 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1058 ,p_data => x_msg_data);
1059 raise;
1060 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1061 rollback;
1062 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1063 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1064 ,p_data => x_msg_data);
1065 raise;
1066 WHEN NO_DATA_FOUND THEN
1067 rollback;
1068 x_return_status := FND_API.G_RET_STS_ERROR;
1069 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1070 ,p_data => x_msg_data);
1071 raise;
1072 WHEN OTHERS THEN
1073 rollback;
1074 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1075 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1076 ,p_data => x_msg_data);
1077 raise;
1078
1079 end Create_Bsc_Analysis_Option;
1080
1081 /************************************************************************************
1082 ************************************************************************************/
1083
1084 procedure Import_PMF_Dim_Level(
1085 p_commit IN varchar2 := FND_API.G_TRUE
1086 ,p_Bsc_Pmf_Dim_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Rec_Type
1087 ,x_return_status OUT NOCOPY varchar2
1088 ,x_msg_count OUT NOCOPY number
1089 ,x_msg_data OUT NOCOPY varchar2
1090 ) is
1091
1092 v_Bsc_Pmf_Dim_Rec BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Rec_Type;
1093 v_Bsc_Dim_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
1094 v_Bsc_Dim_Group_Rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
1095
1096
1097 BEGIN
1098
1099 --DBMS_OUTPUT.PUT_LINE('Begin Import_Dim_Level ' );
1100 --DBMS_OUTPUT.PUT_LINE(' Import_Dim_Level p_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name = ' || p_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name );
1101
1102 Populate_Bsc_Pmf_Dim_Rec(
1103 p_commit
1104 ,p_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name
1105 ,v_Bsc_Pmf_Dim_Rec
1106 ,x_return_status
1107 ,x_msg_count
1108 ,x_msg_data );
1109
1110 --DBMS_OUTPUT.PUT_LINE(' Import_Dim_Level Flag 1 x_return_status = ' || x_return_status );
1111
1112 -- Set values for Dimension Level in BSC.
1113 v_Bsc_Dim_Rec.Bsc_Level_Short_Name := p_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name;
1114 v_Bsc_Dim_Rec.Bsc_Dim_Level_Long_Name := v_Bsc_Pmf_Dim_Rec.Dimension_Level_Long_Name;
1115 v_Bsc_Dim_Rec.Bsc_Level_Disp_Key_Size := v_Bsc_Pmf_Dim_Rec.Dimension_Level_Disp_Size;
1116 v_Bsc_Dim_Rec.Bsc_Level_Name := get_Dim_Level_View_Name(p_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name);
1117 v_Bsc_Dim_Rec.Bsc_Level_View_Name := v_Bsc_Pmf_Dim_Rec.Dimension_Level_View_Name;
1118 if v_Bsc_Pmf_Dim_Rec.Dimension_Level_Source = 'OLTP' then
1119 v_Bsc_Dim_Rec.Bsc_Level_Pk_Key := v_Bsc_Pmf_Dim_Rec.Dimension_Level_Pk_Key;
1120 else
1121 v_Bsc_Dim_Rec.Bsc_Level_Pk_Key := v_Bsc_Pmf_Dim_Rec.Dimension_Level_Pk_Key_edw;
1122 end if;
1123 if v_Bsc_Pmf_Dim_Rec.Dimension_Level_Source = 'OLTP' then
1124 v_Bsc_Dim_Rec.Bsc_Pk_Col := 'ID';
1125 else
1126 v_Bsc_Dim_Rec.Bsc_Pk_Col := v_Bsc_Pmf_Dim_Rec.Dimension_Level_Pk_Key_edw;
1127 end if;
1128 /*
1129 if v_Bsc_Pmf_Dim_Rec.Dimension_Level_Source = 'OLTP' then
1130 v_Bsc_Dim_Rec.Bsc_Pk_Col := v_Bsc_Pmf_Dim_Rec.Dimension_Level_Pk_Key;
1131 else
1132 v_Bsc_Dim_Rec.Bsc_Pk_Col := v_Bsc_Pmf_Dim_Rec.Dimension_Level_Pk_Key_edw;
1133 end if;
1134 */
1135 v_Bsc_Dim_Rec.Bsc_Source := 'PMF';
1136 v_Bsc_Dim_Rec.Source := v_Bsc_Pmf_Dim_Rec.Dimension_Level_Source; /* added to fix 2674365 */
1137
1138 v_Bsc_Dim_Rec.Bsc_Level_Name_Column := v_Bsc_Pmf_Dim_Rec.Dimension_Level_Name_Column;
1139 -- v_Bsc_Dim_Rec.Bsc_Kpi_Id := g_Bsc_Pmf_Ui_Rec.Kpi_Id;
1140 v_Bsc_Dim_Rec.Bsc_Language := 'US';
1141 v_Bsc_Dim_Rec.Bsc_Source_Language := 'US';
1142
1143 --DBMS_OUTPUT.PUT_LINE(' Import_Dim_Level Flag 2 x_return_status = ' || x_return_status );
1144
1145 BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level( FND_API.G_TRUE
1146 ,v_Bsc_Dim_Rec
1147 ,x_return_status
1148 ,x_msg_count
1149 ,x_msg_data);
1150
1151 --DBMS_OUTPUT.PUT_LINE(' Import_Dim_Level Flag 3 x_return_status = ' || x_return_status );
1152
1153
1154 -- Set values for Dimension Group in BSC.
1155 v_Bsc_Dim_Group_Rec.Bsc_Dim_Level_Group_Name := 'Dgrp ' || lower(replace(v_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name, '_', ' '));
1156 v_Bsc_Dim_Group_Rec.Bsc_Language := 'US';
1157 v_Bsc_Dim_Group_Rec.Bsc_Source_Language := 'US';
1158
1159 --DBMS_OUTPUT.PUT_LINE(' Import_Dim_Level Flag 4 x_return_status = ' || x_return_status );
1160
1161 -- Get the Id for the recently created Dimension (Level) in BSC.
1162 select distinct dim_level_id
1163 into v_Bsc_Dim_Group_Rec.Bsc_Level_Id
1164 from BSC_SYS_DIM_LEVELS_B
1165 where SHORT_NAME = p_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name;
1166
1167 -- Create a Dimension Group for the Dimension Level.
1168
1169 --DBMS_OUTPUT.PUT_LINE(' Import_Dim_Level - Flag 5 - Create Dimension Group - x_return_status = ' || x_return_status );
1170
1171 BSC_DIMENSION_GROUPS_PUB.Create_Dimension_Group( FND_API.G_TRUE
1172 ,v_Bsc_Dim_Group_Rec
1173 ,x_return_status
1174 ,x_msg_count
1175 ,x_msg_data);
1176
1177 --DBMS_OUTPUT.PUT_LINE('End Import_Dim_Level ');
1178
1179
1180 EXCEPTION
1181 WHEN FND_API.G_EXC_ERROR THEN
1182 rollback;
1183 x_return_status := FND_API.G_RET_STS_ERROR;
1184 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1185 ,p_data => x_msg_data);
1186 raise;
1187 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1188 rollback;
1189 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1190 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1191 ,p_data => x_msg_data);
1192 raise;
1193 WHEN NO_DATA_FOUND THEN
1194 rollback;
1195 x_return_status := FND_API.G_RET_STS_ERROR;
1196 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1197 ,p_data => x_msg_data);
1198 raise;
1199 WHEN OTHERS THEN
1200 rollback;
1201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1202 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1203 ,p_data => x_msg_data);
1204 raise;
1205
1206 end Import_PMF_Dim_Level;
1207
1208 /************************************************************************************
1209 ************************************************************************************/
1210
1211 procedure Populate_Bsc_Pmf_Dim_Rec(
1212 p_commit IN varchar2 := FND_API.G_TRUE
1213 ,p_Dim_Level_Short_Name IN varchar2
1214 ,x_Bsc_Pmf_Dim_Rec OUT NOCOPY BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Rec_Type
1215 ,x_return_status OUT NOCOPY varchar2
1216 ,x_msg_count OUT NOCOPY number
1217 ,x_msg_data OUT NOCOPY varchar2
1218 ) is
1219
1220 TYPE Recdc_value IS REF CURSOR;
1221 dc_value Recdc_value;
1222 dc_value1 Recdc_value;
1223
1224 no_dim_level exception;
1225
1226 l_alternate_level_view varchar2(30);
1227 l_sql varchar2(1000);
1228 l_sql1 varchar2(1000);
1229 l_count number;
1230
1231 BEGIN
1232
1233 --DBMS_OUTPUT.PUT_LINE('Begin Populate_Bsc_Pmf_Dim_Rec '); /* 949 */
1234
1235 -- Set the dimension level short name and get the dimension level long name.
1236 if p_Dim_Level_Short_Name is null then
1237 --DBMS_OUTPUT.PUT_LINE('Populate_Bsc_Pmf_Dim_Rec - Short_Name is null ');
1238 raise no_dim_level;
1239 end if;
1240
1241 --x_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name := substr(p_Dim_Level_Short_Name, 1, 24);
1242 x_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name := p_Dim_Level_Short_Name;
1243
1244 --DBMS_OUTPUT.PUT_LINE(' Populate_Bsc_Pmf_Dim_Rec p_Dim_Level_Short_Name = ' || p_Dim_Level_Short_Name );
1245 --DBMS_OUTPUT.PUT_LINE(' Populate_Bsc_Pmf_Dim_Rec x_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name = ' || x_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name );
1246
1247 select distinct source
1248 into x_Bsc_Pmf_Dim_Rec.Dimension_Level_Source
1249 from bisfv_dimension_levels
1250 where upper(dimension_level_short_name) = upper(p_Dim_Level_Short_Name);
1251
1252 --DBMS_OUTPUT.PUT_LINE('Populate_Bsc_Pmf_Dim_Rec x_Bsc_Pmf_Dim_Rec.Dimension_Level_Source = ' || x_Bsc_Pmf_Dim_Rec.Dimension_Level_Source );
1253
1254 if x_Bsc_Pmf_Dim_Rec.Dimension_Level_Source = 'OLTP' then
1255 --DBMS_OUTPUT.PUT_LINE(' Populate_Bsc_Pmf_Dim_Rec - OLTP ' );
1256
1257 select distinct dimension_level_name, level_values_view_name, 'ID', 'value'
1258 -- select distinct dimension_level_name, level_values_view_name, 'rownum', 'value'
1259 into x_Bsc_Pmf_Dim_Rec.Dimension_Level_Long_Name,
1260 x_Bsc_Pmf_Dim_Rec.Dimension_Level_View_Name,
1261 x_Bsc_Pmf_Dim_Rec.Dimension_Level_Pk_Key,
1262 x_Bsc_Pmf_Dim_Rec.Dimension_Level_Name_Column
1263 from bisbv_dimension_levels
1264 where upper(dimension_level_short_name) = upper(p_Dim_Level_Short_Name);
1265
1266 else
1267 --DBMS_OUTPUT.PUT_LINE(' Populate_Bsc_Pmf_Dim_Rec Flag A ' );
1268
1269 select distinct dimension_level_name
1270 ,dimension_level_short_name || '_LTC'
1271 ,level_values_view_name
1272 into x_Bsc_Pmf_Dim_Rec.Dimension_Level_Long_Name
1273 ,x_Bsc_Pmf_Dim_Rec.Dimension_Level_View_Name
1274 ,l_alternate_level_view
1275 from bisbv_dimension_levels
1276 where upper(dimension_level_short_name) = upper(p_Dim_Level_Short_Name);
1277
1278 --DBMS_OUTPUT.PUT_LINE('Populate_Bsc_Pmf_Dim_Rec x_Bsc_Pmf_Dim_Rec.Dimension_Level_View_Name = ' || x_Bsc_Pmf_Dim_Rec.Dimension_Level_View_Name );
1279
1280
1281 if l_alternate_level_view is not null then
1282 --DBMS_OUTPUT.PUT_LINE('Populate_Bsc_Pmf_Dim_Rec l_alternate_level_view = ' || l_alternate_level_view );
1283 x_Bsc_Pmf_Dim_Rec.Dimension_Level_View_Name := l_alternate_level_view;
1284 x_Bsc_Pmf_Dim_Rec.Dimension_Level_Pk_Key_edw := 'ID';
1285 x_Bsc_Pmf_Dim_Rec.Dimension_Level_Name_Column := 'VALUE';
1286
1287 else
1288 --DBMS_OUTPUT.PUT_LINE(' Populate_Bsc_Pmf_Dim_Rec Flag B ' );
1289
1290 -- Changed to dynamic sql, in case EDW not installed.
1291 l_sql1 := ' select distinct level_table_col_name ' ||
1292 ' from edw_level_Table_atts_md_v ' ||
1293 ' where key_type=''UK'' and ' ||
1294 ' upper(level_Table_name) = upper(:1) and ' ||
1295 ' upper(level_table_col_name) like ''%PK_KEY%''';
1296
1297
1298 open dc_value1 for l_sql1 using x_Bsc_Pmf_Dim_Rec.Dimension_Level_View_Name;
1299 fetch dc_value1 into x_Bsc_Pmf_Dim_Rec.Dimension_Level_Pk_Key_edw;
1300 close dc_value1;
1301
1302 --DBMS_OUTPUT.PUT_LINE('Populate_Bsc_Pmf_Dim_Rec C x_Bsc_Pmf_Dim_Rec.Dimension_Level_Pk_Key_edw = ' || x_Bsc_Pmf_Dim_Rec.Dimension_Level_Pk_Key_edw );
1303
1304 -- Changed to dynamic sql, in case EDW not installed.
1305 l_sql1 := 'select level_table_col_name ' ||
1306 ' from edw_level_Table_atts_md_v ' ||
1307 ' where upper(level_Table_name) = upper(:1) and ' ||
1308 ' (upper(level_table_col_name) like ''%DESCRIPTION%'' or ' ||
1309 ' upper(level_table_col_name) like ''NAME%'') and ' ||
1310 ' rownum < 2';
1311
1312 open dc_value1 for l_sql1 using x_Bsc_Pmf_Dim_Rec.Dimension_Level_View_Name;
1313 fetch dc_value1 into x_Bsc_Pmf_Dim_Rec.Dimension_Level_Name_Column;
1314 close dc_value1;
1315
1316 --DBMS_OUTPUT.PUT_LINE('Populate_Bsc_Pmf_Dim_Rec D x_Bsc_Pmf_Dim_Rec.Dimension_Level_Name_Column = ' || x_Bsc_Pmf_Dim_Rec.Dimension_Level_Name_Column );
1317
1318 end if;
1319
1320 end if;
1321
1322 --DBMS_OUTPUT.PUT_LINE('Populate_Bsc_Pmf_Dim_Rec XX x_Bsc_Pmf_Dim_Rec.Dimension_Level_View_Name = ' || x_Bsc_Pmf_Dim_Rec.Dimension_Level_View_Name );
1323
1324 -- Included to fixed bug 2382059
1325 IF (NOT BSC_UTILITY.is_Table_View_Exists(x_Bsc_Pmf_Dim_Rec.Dimension_Level_View_Name)) THEN
1326 FND_MESSAGE.SET_NAME('BSC','BSC_PMF_LEVEL_NOT_EXISTS');
1327 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_NAME', x_Bsc_Pmf_Dim_Rec.Dimension_Level_View_Name);
1328 FND_MSG_PUB.ADD;
1329 RAISE FND_API.G_EXC_ERROR;
1330 END IF;
1331 -- Included to fixed bug 2382059
1332
1333 -- The 'order by' added to fix bug 2406866
1334 l_sql := 'select max(length(' || x_Bsc_Pmf_Dim_Rec.Dimension_Level_Name_Column || '))' ||
1335 ' from ' || x_Bsc_Pmf_Dim_Rec.Dimension_Level_View_Name ||
1336 ' order by NVL(:1,:2) ';
1337
1338 --DBMS_OUTPUT.PUT_LINE('Populate_Bsc_Pmf_Dim_Rec l_sql = ' || l_sql );
1339
1340 open dc_value for l_sql using x_Bsc_Pmf_Dim_Rec.Dimension_Level_Pk_Key,x_Bsc_Pmf_Dim_Rec.Dimension_Level_Pk_Key_edw ;
1341 fetch dc_value into x_Bsc_Pmf_Dim_Rec.Dimension_Level_Disp_Size;
1342 close dc_value;
1343 --DBMS_OUTPUT.PUT_LINE('Populate_Bsc_Pmf_Dim_Rec F x_Bsc_Pmf_Dim_Rec.Dimension_Level_Disp_Size = ' || x_Bsc_Pmf_Dim_Rec.Dimension_Level_Disp_Size );
1344
1345 -- Double the size of the Level Display Size if under 125;
1346 if x_Bsc_Pmf_Dim_Rec.Dimension_Level_Disp_Size < 125 then
1347 x_Bsc_Pmf_Dim_Rec.Dimension_Level_Disp_Size := x_Bsc_Pmf_Dim_Rec.Dimension_Level_Disp_Size * 2;
1348 end if;
1349
1350
1351 --DBMS_OUTPUT.PUT_LINE('End Populate_Bsc_Pmf_Dim_Rec ');
1352
1353
1354 EXCEPTION
1355 WHEN FND_API.G_EXC_ERROR THEN
1356 rollback;
1357 x_return_status := FND_API.G_RET_STS_ERROR;
1358 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1359 ,p_data => x_msg_data);
1360 raise;
1361 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1362 rollback;
1363 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1364 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1365 ,p_data => x_msg_data);
1366
1367 raise;
1368 WHEN NO_DATA_FOUND THEN
1369 rollback;
1370 x_return_status := FND_API.G_RET_STS_ERROR;
1371 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1372 ,p_data => x_msg_data);
1373 raise;
1374 WHEN OTHERS THEN
1375 rollback;
1376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1377 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1378 ,p_data => x_msg_data);
1379 raise;
1380
1381
1382 end Populate_Bsc_Pmf_Dim_Rec;
1383
1384 /************************************************************************************
1385 ************************************************************************************/
1386 /*-------------------------------------------------------------------------------------
1387 get_Dim_Level_View_Name
1388 Return the Dimension Level View Name to use in BSC for an Imported Dimension Level
1389 ---------------------------------------------------------------------------------------*/
1390 FUNCTION get_Dim_Level_View_Name(
1391 p_Short_Name IN VARCHAR2
1392 ) RETURN VARCHAR2 IS
1393 l_short_Name varchar2(100);
1394 l_view_name varchar2(100);
1395 l_count number;
1396 l_index number;
1397
1398 BEGIN
1399 -- See if the Level Short Name is already imported
1400 select count(LEVEL_TABLE_NAME)
1401 into l_count
1402 from bsc_sys_dim_levels_vl
1403 where SHORT_NAME = 'p_Short_Name';
1404
1405 if l_count <> 0 then
1406 -- if the level is already imported return the same name
1407 select LEVEL_TABLE_NAME
1408 into l_view_name
1409 from bsc_sys_dim_levels_vl
1410 where SHORT_NAME = p_Short_Name;
1411 else
1412 l_index := 0;
1413
1414 -- if the level is not imported yet
1415 l_view_name := 'BSC_D_' || substr( replace(p_Short_Name, ' ', '_') , 1, 22) || '_V';
1416 loop
1417 select count(object_name)
1418 into l_count
1419 from user_objects
1420 where object_name = upper(l_view_name);
1421 exit when l_count = 0;
1422
1423 -- Tries other object name
1424 l_index := l_index + 1;
1425 l_view_name := 'BSC_D_' || substr( replace(p_Short_Name, ' ', '_') , 1, 22 - LENGTH('' || l_index) ) || l_index || '_V';
1426
1427 end loop;
1428
1429 end if;
1430
1431 RETURN l_view_name;
1432
1433 EXCEPTION
1434 WHEN OTHERS THEN
1435 RETURN null;
1436
1437 END get_Dim_Level_View_Name;
1438
1439 /*********************************************************************************
1440 **********************************************************************************/
1441
1442 PROCEDURE Get_DimLevel_Viewby
1443 ( p_api_version IN NUMBER
1444 , p_Region_Code IN VARCHAR2
1445 , p_Measure_Short_Name IN VARCHAR2
1446 , x_DimLevel_Viewby_Tbl OUT NOCOPY DimLevel_Viewby_Tbl_Type /* BIS_PMV_BSC_API_PUB.DimLevel_Viewby_Tbl_Type */
1447 , x_return_status OUT NOCOPY VARCHAR2
1448 , x_msg_count OUT NOCOPY NUMBER
1449 , x_msg_data OUT NOCOPY VARCHAR2
1450 ) is
1451
1452 l_Region_Code VARCHAR2(200);
1453 l_nested_region_code VARCHAR2(200) := null;
1454 l_Region VARCHAR2(200);
1455 l_attribute1 VARCHAR2(200);
1456 l_attribute2 VARCHAR2(200);
1457 l_required_flag VARCHAR2(200);
1458 l_Report_View_By_Flag VARCHAR2(1);
1459 l_Level_In_Main_Report_Flag number;
1460 l_count number;
1461 l_Dimlevel_Viewby_Rec Dimlevel_Viewby_Rec_Type;
1462 l_DimLevel_Viewby_Tbl DimLevel_Viewby_Tbl_Type;
1463 l_index number;
1464 NOT_VALID_DIMENSION VARCHAR2(40) := 'BSC_NOT_VALID_DIMENSION_FLAG' ;
1465
1466 -- Cursor to get the Dimension Level Info from ak_region_items table ---------
1467
1468 CURSOR c_region IS
1469 SELECT region_code
1470 FROM ak_region_items
1471 WHERE attribute1='MEASURE'
1472 AND attribute2 = p_Measure_Short_Name
1473 ORDER BY creation_date DESC;
1474
1475 CURSOR c_nested_region IS
1476 SELECT DISTINCT nested_region_code
1477 FROM ak_region_items
1478 WHERE region_code = l_Region_Code
1479 AND item_style = 'NESTED_REGION';
1480
1481 CURSOR c_Viewby_Report IS
1482 SELECT attribute1
1483 FROM ak_regions
1484 WHERE region_code = l_Region_Code;
1485
1486 CURSOR c_dim_levels IS
1487 SELECT attribute2, attribute1, required_flag
1488 FROM ak_region_items
1489 WHERE region_code = l_Region_Code
1490 AND (attribute1 = 'DIMENSION LEVEL' OR attribute1 = 'HIDE PARAMETER' OR attribute1 = 'HIDE VIEW BY DIMENSION')
1491 ORDER BY attribute2, attribute1;
1492
1493 -- Cursor to get the Dimension Level Info from ak_region_items table when exists a Nested Region
1494 CURSOR c_dim_levels1 IS
1495 SELECT attribute2, attribute1, region_code, required_flag
1496 FROM ak_region_items
1497 WHERE (region_code = l_Region_Code OR region_code = l_nested_region_code)
1498 AND (attribute1 = 'DIMENSION LEVEL' OR attribute1 = 'HIDE PARAMETER' OR attribute1 = 'HIDE VIEW BY DIMENSION')
1499 ORDER BY attribute2, attribute1, region_code;
1500 Begin
1501
1502 --DBMS_OUTPUT.PUT_LINE('Begin BSC_PMF_UI_API_PUB.Get_DimLevel_Viewby');
1503 --DBMS_OUTPUT.PUT_LINE('--- BSC_PMF_UI_API_PUB.Get_DimLevel_Viewby - p_Measure_Short_Name = ' || p_Measure_Short_Name);
1504
1505 -- Check the Region code passed to the API
1506 if p_Region_Code is null then
1507 --If the region_code passed to the API is NULL, then the Measure is not associated with any Report.
1508 --So we have to query ak_region_items table to get the region_code
1509
1510 OPEN c_region;
1511 FETCH c_region INTO l_Region_Code;
1512 if c_region%NOTFOUND then
1513 l_Region_Code := null;
1514 -- through and Error : Measure does not exist
1515 --DBMS_OUTPUT.PUT_LINE('--- BSC_PMF_UI_API_PUB.Get_DimLevel_Viewby - Measure does not exist ' );
1516 end if;
1517 close c_region;
1518 else
1519 l_Region_Code := p_Region_Code;
1520 end if;
1521
1522 --DBMS_OUTPUT.PUT_LINE('--- BSC_PMF_UI_API_PUB.Get_DimLevel_Viewby - l_Region_Code = ' || l_Region_Code);
1523
1524 -- Check whether the report region contains a Nested Region (DBI Report). -----
1525 OPEN c_nested_region;
1526 FETCH c_nested_region INTO l_nested_region_code;
1527 if c_nested_region%NOTFOUND then
1528 l_nested_region_code := null;
1529 end if;
1530 close c_nested_region;
1531 --DBMS_OUTPUT.PUT_LINE('--- BSC_PMF_UI_API_PUB.Get_DimLevel_Viewby - l_nested_region_code = ' || l_nested_region_code);
1532
1533 -- Check whether the report is 'View By' or 'not View By' -------------------
1534 l_Report_View_By_Flag := 'Y';
1535 OPEN c_Viewby_Report;
1536 FETCH c_Viewby_Report INTO l_attribute1;
1537 if c_Viewby_Report%FOUND then
1538 if l_attribute1 = 'Y' then
1539 l_Report_View_By_Flag := 'N';
1540 end if;
1541 end if;
1542 close c_Viewby_Report;
1543 --DBMS_OUTPUT.PUT_LINE('--- BSC_PMF_UI_API_PUB.Get_DimLevel_Viewby - l_Report_View_By_Flag = ' || l_Report_View_By_Flag);
1544
1545 -- Get the Dimension Level Info from the main Region -------------------------
1546 -- When not exits a Nested Region
1547
1548 if l_nested_region_code is null then
1549
1550 --DBMS_OUTPUT.PUT_LINE('--- BSC_PMF_UI_API_PUB.Get_DimLevel_Viewby - NESTED REGION NOT EXISTS ');
1551
1552 l_Dimlevel_Viewby_Rec.Dim_DimLevel := NOT_VALID_DIMENSION;
1553 l_index := 1;
1554
1555 OPEN c_dim_levels;
1556 LOOP
1557 FETCH c_dim_levels INTO l_attribute2, l_attribute1, l_required_flag;
1558 EXIT WHEN c_dim_levels%NOTFOUND;
1559
1560 --DBMS_OUTPUT.PUT_LINE('--- l_attribute2 = ' || l_attribute2 || ' l_attribute1 = ' || l_attribute1 || ' l_required_flag = ' || l_required_flag);
1561
1562 if l_Dimlevel_Viewby_Rec.Dim_DimLevel <> l_attribute2 then
1563 if l_Dimlevel_Viewby_Rec.Dim_DimLevel <> NOT_VALID_DIMENSION then
1564 l_DimLevel_Viewby_Tbl(l_index) := l_Dimlevel_Viewby_Rec;
1565 l_index := l_index + 1;
1566 end if;
1567 if l_attribute1 = 'DIMENSION LEVEL' then
1568 l_Dimlevel_Viewby_Rec.Dim_DimLevel := l_attribute2;
1569 l_Dimlevel_Viewby_Rec.Viewby_Applicable := l_Report_View_By_Flag;
1570 if l_required_flag = 'Y' then
1571 l_Dimlevel_Viewby_Rec.All_Applicable := 'N';
1572 else
1573 l_Dimlevel_Viewby_Rec.All_Applicable := 'Y';
1574 end if;
1575 else
1576 l_Dimlevel_Viewby_Rec.Dim_DimLevel := NOT_VALID_DIMENSION;
1577 end if;
1578 else
1579 if l_Report_View_By_Flag = 'Y' then
1580 if l_attribute1 = 'HIDE PARAMETER' or l_attribute1 = 'HIDE VIEW BY DIMENSION' then
1581 l_Dimlevel_Viewby_Rec.Viewby_Applicable := 'N';
1582 end if;
1583 end if;
1584 if l_required_flag = 'Y' then
1585 l_Dimlevel_Viewby_Rec.All_Applicable := 'N';
1586 end if;
1587 end if;
1588 END LOOP;
1589 if l_Dimlevel_Viewby_Rec.Dim_DimLevel <> NOT_VALID_DIMENSION then
1590 l_DimLevel_Viewby_Tbl(l_index) := l_Dimlevel_Viewby_Rec;
1591 end if;
1592
1593 else -- Get the Dimension Level Info from the main Region and Nested Region -----
1594 -- When exits a Nested Region
1595
1596 --DBMS_OUTPUT.PUT_LINE('--- BSC_PMF_UI_API_PUB.Get_DimLevel_Viewby - NESTED REGION EXISTS ');
1597
1598 l_Dimlevel_Viewby_Rec.Dim_DimLevel := NOT_VALID_DIMENSION;
1599 l_index := 1;
1600 OPEN c_dim_levels1;
1601 LOOP
1602 FETCH c_dim_levels1 INTO l_attribute2, l_attribute1, l_Region, l_required_flag ;
1603 EXIT WHEN c_dim_levels1%NOTFOUND;
1604 --DBMS_OUTPUT.PUT_LINE('--- l_attribute2 = ' || l_attribute2 || ' l_attribute1 = ' || l_attribute1 || ' l_Region = ' || l_Region || ' l_required_flag = ' || l_required_flag );
1605
1606 if l_Dimlevel_Viewby_Rec.Dim_DimLevel <> l_attribute2 then
1607 if l_Dimlevel_Viewby_Rec.Dim_DimLevel <> NOT_VALID_DIMENSION then
1608 --DBMS_OUTPUT.PUT_LINE('--- l_attribute2 = ' || l_attribute2 || ' l_attribute1 = ' || l_attribute1 || ' l_Region = ' || l_Region );
1609 l_DimLevel_Viewby_Tbl(l_index) := l_Dimlevel_Viewby_Rec;
1610 l_index := l_index + 1;
1611 end if;
1612 if l_attribute1 = 'DIMENSION LEVEL' then
1613 l_Dimlevel_Viewby_Rec.Dim_DimLevel := l_attribute2;
1614 l_Dimlevel_Viewby_Rec.Viewby_Applicable := l_Report_View_By_Flag;
1615 if l_required_flag = 'Y' then
1616 l_Dimlevel_Viewby_Rec.All_Applicable := 'N';
1617 else
1618 l_Dimlevel_Viewby_Rec.All_Applicable := 'Y';
1619 end if;
1620 if l_Region = l_Region_Code then
1621 l_Level_In_Main_Report_Flag := 1;
1622 else
1623 l_Level_In_Main_Report_Flag := 0;
1624 end if;
1625 else
1626 l_Dimlevel_Viewby_Rec.Dim_DimLevel := NOT_VALID_DIMENSION;
1627 end if;
1628 elsif l_attribute1 = 'DIMENSION LEVEL' then
1629 if l_Region = l_Region_Code then
1630 l_Level_In_Main_Report_Flag := 1;
1631 l_Dimlevel_Viewby_Rec.Viewby_Applicable := l_Report_View_By_Flag;
1632 if l_required_flag = 'Y' then
1633 l_Dimlevel_Viewby_Rec.All_Applicable := 'N';
1634 else
1635 l_Dimlevel_Viewby_Rec.All_Applicable := 'Y';
1636 end if;
1637 end if;
1638 else
1639 if l_Region = l_Region_Code or l_Level_In_Main_Report_Flag = 0 then
1640 if l_Report_View_By_Flag = 'Y' then
1641 if l_attribute1 = 'HIDE PARAMETER' or l_attribute1 = 'HIDE VIEW BY DIMENSION' then
1642 l_Dimlevel_Viewby_Rec.Viewby_Applicable := 'N';
1643 end if;
1644 end if;
1645 if l_required_flag = 'Y' then
1646 l_Dimlevel_Viewby_Rec.All_Applicable := 'N';
1647 end if;
1648 end if;
1649 end if;
1650 END LOOP;
1651 if l_Dimlevel_Viewby_Rec.Dim_DimLevel <> NOT_VALID_DIMENSION then
1652 l_DimLevel_Viewby_Tbl(l_index) := l_Dimlevel_Viewby_Rec;
1653 end if;
1654
1655 end if;
1656
1657 x_DimLevel_Viewby_Tbl := l_DimLevel_Viewby_Tbl;
1658
1659 --DBMS_OUTPUT.PUT_LINE('End BSC_PMF_UI_API_PUB.Get_DimLevel_Viewby');
1660
1661 EXCEPTION
1662 WHEN FND_API.G_EXC_ERROR THEN
1663 rollback;
1664 x_return_status := FND_API.G_RET_STS_ERROR;
1665 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1666 ,p_data => x_msg_data);
1667 raise;
1668 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1669 rollback;
1670 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1671 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1672 ,p_data => x_msg_data);
1673
1674 raise;
1675 WHEN NO_DATA_FOUND THEN
1676 rollback;
1677 x_return_status := FND_API.G_RET_STS_ERROR;
1678 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1679 ,p_data => x_msg_data);
1680 raise;
1681 WHEN OTHERS THEN
1682 rollback;
1683 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1684 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1685 ,p_data => x_msg_data);
1686 raise;
1687
1688 end Get_DimLevel_Viewby;
1689
1690 /*********************************************************************************
1691 **********************************************************************************/
1692
1693
1694
1695 end BSC_PMF_UI_API_PUB;