[Home] [Help]
PACKAGE BODY: APPS.BSC_DIMENSION_LEVELS_PVT
Source
1 package body BSC_DIMENSION_LEVELS_PVT as
2 /* $Header: BSCVDMLB.pls 120.6 2006/02/10 01:16:27 adrao noship $ */
3 /*
4 +======================================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +======================================================================================+
8 | FILENAME |
9 | BSCVDMLB.pls |
10 | |
11 | Creation Date: |
12 | October 9, 2001 |
13 | |
14 | Creator: |
15 | Mario-Jair Campos |
16 | |
17 | Description: |
18 | Private Version. |
19 | This package creates a dimension level in BSC. |
20 | |
21 | History: |
22 | 04-MAR-2003 PAJOHRI MLS Bug #2721899 |
23 | Modified Update Query for BSC_SYS_DIM_LEVELS_TL and Insert |
24 | Query for BSC_SYS_DIM_LEVELS_TL to handle MLS. |
25 | 22-APR-2003 ADRAO Modified Evaluate_Circular_Relationship() to allow, PMF |
26 | to insert Parent Dim Level being its own child Enh#2901823 |
27 | 07-MAY-2003 Retrieve_Relationship() Added by ADRAO for change Enh#2901823 |
28 | 13-MAY-2003 PWALI Bug #2942895, SQL BIND COMPLIANCE |
29 | 07-JUN-2003 MAHRAO Modified Create_Dim_Level for ALL enhancement |
30 | 14-JUN-03 mahrao Added Translate_dimesnsion_level procedure for enh# 2842894 |
31 | 16-JUN-03 ADRAO Modified Get_Next_Value to use sequences for Granular Locking |
32 | Enh #2828689 |
33 | 17-JUL-03 mahrao Modified Retr_Updated_Bsc_Dim_Levels procedure |
34 | as part of forward porting of ALL enhancement to BSC 5.1 |
35 | Modified Translate_Dimension_Level procedure |
36 | as part of forward porting of ALL enhancement to BSC 5.1 |
37 | 16-SEP-03 Adeulgao fixed bug#3108877 |
38 | 04-NOV-2003 PAJOHRI Bug #3232366 |
39 | 25-MAR-2004 KAYAMAK Bug #3528143 (removed source language condition) |
40 | 30-Jul-04 rpenneru Modified for enhancemen#3748519 |
41 | 10-Aug-04 arhegde bug# 3814375 Appsperf: reduce sql executions |
42 | 01-NOV-04 Krishna removed the cursor leaks |
43 | 21-DEC-04 vtulasi Modified for bug#4045278 - Addtion of LUD |
44 | 15-FEB-05 ppandey Enh #4016669, support ID, Value for Autogenerated Dimension Obj|
45 | 27-JUN-05 arhegde bug# 4456833 relation_type in retrieve_relationship() |
46 | 25-OCT-2005 kyadamak Removed literals for Enhancement#4618419 |
47 | 08-FEB-2006 adrao Bug#5011937 Changed all the calls from Do_Ddl() to Do_Ddl_AT() |
48 | [Autonomous Transaction] |
49 +======================================================================================+
50 */
51 G_PKG_NAME CONSTANT varchar2(30) := 'BSC_DIMENSION_LEVELS_PVT';
52 g_db_object varchar2(30) := null;
53
54 /**********************************************************************************/
55
56 FUNCTION Validate_Dim_Level_Id (
57 p_dim_level_id IN NUMBER
58 ) RETURN NUMBER
59 IS
60 l_Count NUMBER := 0;
61 BEGIN
62
63 SELECT COUNT(Dim_Level_Id)
64 INTO l_Count
65 FROM BSC_SYS_DIM_LEVELS_B
66 WHERE Dim_Level_Id = p_Dim_Level_Id;
67
68 RETURN l_count;
69 EXCEPTION
70 WHEN OTHERS THEN
71 RETURN l_Count;
72 END Validate_Dim_Level_Id;
73
74 --: The following procedure is used to create the BSC Dimension entity.
75 --: It is the entry point to populate all necessary meta data.
76 --: This procedure is part of the Dimension API.
77
78
79 -- WARNING: This API has been stubbed, this should never be used to create Dimension Object views
80 -- Please use BSC_BIS_DIM_OBJ_PUB.Refresh_BSC_PMF_Dim_View instead.
81
82 -- Stubbed for Bug#3739872
83
84 procedure Create_Dim_Level(
85 p_commit IN varchar2 := FND_API.G_FALSE
86 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
87 ,x_return_status OUT NOCOPY varchar2
88 ,x_msg_count OUT NOCOPY number
89 ,x_msg_data OUT NOCOPY varchar2
90 ) is
91
92 BEGIN
93 x_return_status := FND_API.G_RET_STS_SUCCESS;
94
95 -- do nothing
96 -- Please use BSC_BIS_DIM_OBJ_PUB.Refresh_BSC_PMF_Dim_View to create views instead
97 NULL;
98
99 EXCEPTION
100 WHEN OTHERS THEN
101 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
102 IF (x_msg_data IS NOT NULL) THEN
103 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Create_Dim_Level ';
104 ELSE
105 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Create_Dim_Level ';
106 END IF;
107 end Create_Dim_Level;
108
109 /************************************************************************************
110 ************************************************************************************/
111
112 procedure Retrieve_Dim_Level(
113 p_commit IN varchar2 := FND_API.G_FALSE
114 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
115 ,x_Dim_Level_Rec IN OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
116 ,x_return_status OUT NOCOPY varchar2
117 ,x_msg_count OUT NOCOPY number
118 ,x_msg_data OUT NOCOPY varchar2
119 ) is
120
121 l_count number;
122
123 begin
124 x_return_status := FND_API.G_RET_STS_SUCCESS;
125 -- This procedure itself does not retrieve anything. The two calls below are usually
126 -- never executed, because the public version calls these procedures.
127
128 Retrieve_Bsc_Dim_Levels_Md( p_commit
129 ,p_Dim_Level_Rec
130 ,x_Dim_Level_Rec
131 ,x_return_status
132 ,x_msg_count
133 ,x_msg_data);
134
135 if (p_commit = FND_API.G_TRUE) then
136 commit;
137 end if;
138
139 EXCEPTION
140 WHEN FND_API.G_EXC_ERROR THEN
141 FND_MSG_PUB.Count_And_Get
142 ( p_encoded => FND_API.G_FALSE
143 , p_count => x_msg_count
144 , p_data => x_msg_data
145 );
146 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
147 x_return_status := FND_API.G_RET_STS_ERROR;
148 RAISE;
149 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
150 FND_MSG_PUB.Count_And_Get
151 ( p_encoded => FND_API.G_FALSE
152 , p_count => x_msg_count
153 , p_data => x_msg_data
154 );
155 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
156 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
157 RAISE;
158 WHEN NO_DATA_FOUND THEN
159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
160 IF (x_msg_data IS NOT NULL) THEN
161 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Retrieve_Dim_Level ';
162 ELSE
163 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Retrieve_Dim_Level ';
164 END IF;
165 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
166 RAISE;
167 WHEN OTHERS THEN
168 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
169 IF (x_msg_data IS NOT NULL) THEN
170 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Retrieve_Dim_Level ';
171 ELSE
172 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Retrieve_Dim_Level ';
173 END IF;
174 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
175 RAISE;
176 end Retrieve_Dim_Level;
177
178 /************************************************************************************
179 ************************************************************************************/
180
181 procedure Update_Dim_Level(
182 p_commit IN varchar2 := FND_API.G_FALSE
183 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
184 ,x_return_status OUT NOCOPY varchar2
185 ,x_msg_count OUT NOCOPY number
186 ,x_msg_data OUT NOCOPY varchar2
187 ) is
188
189 l_dummy varchar2(10);
190
191 begin
192 x_return_status := FND_API.G_RET_STS_SUCCESS;
193 SAVEPOINT UpdateBSCDimLevPVT;
194 l_dummy := 'dummy';
195
196 -- This procedure itself does not update anything. The two calls below are usually
197 -- never executed, because the public version calls these procedures.
198
199 if (p_commit = FND_API.G_TRUE) then
200 commit;
201 end if;
202
203 EXCEPTION
204 WHEN FND_API.G_EXC_ERROR THEN
205 ROLLBACK TO UpdateBSCDimLevPVT;
206 FND_MSG_PUB.Count_And_Get
207 ( p_encoded => FND_API.G_FALSE
208 , p_count => x_msg_count
209 , p_data => x_msg_data
210 );
211 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
212 x_return_status := FND_API.G_RET_STS_ERROR;
213 RAISE;
214 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
215 ROLLBACK TO UpdateBSCDimLevPVT;
216 FND_MSG_PUB.Count_And_Get
217 ( p_encoded => FND_API.G_FALSE
218 , p_count => x_msg_count
219 , p_data => x_msg_data
220 );
221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
222 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
223 RAISE;
224 WHEN NO_DATA_FOUND THEN
225 ROLLBACK TO UpdateBSCDimLevPVT;
226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
227 IF (x_msg_data IS NOT NULL) THEN
228 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Update_Dim_Level ';
229 ELSE
230 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Update_Dim_Level ';
231 END IF;
232 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
233 RAISE;
234 WHEN OTHERS THEN
235 ROLLBACK TO UpdateBSCDimLevPVT;
236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
237 IF (x_msg_data IS NOT NULL) THEN
238 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Update_Dim_Level ';
239 ELSE
240 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Update_Dim_Level ';
241 END IF;
242 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
243 RAISE;
244 end Update_Dim_Level;
245
246 /************************************************************************************
247 ************************************************************************************/
248
249 procedure Delete_Dim_Level(
250 p_commit IN varchar2 := FND_API.G_FALSE
251 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
252 ,x_return_status OUT NOCOPY varchar2
253 ,x_msg_count OUT NOCOPY number
254 ,x_msg_data OUT NOCOPY varchar2
255 ) is
256
257 l_sql varchar2(1000);
258
259 l_count number;
260 l_view_name varchar2(100);
261 l_Dim_Level_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
262 CURSOR c_drop_tabs IS
263 SELECT LEVEL_TABLE_NAME,
264 LEVEL_VIEW_NAME,
265 NVL(SOURCE, 'BSC') SOURCE
266 FROM BSC_SYS_DIM_LEVELS_B
267 WHERE DIM_LEVEL_ID = p_Dim_Level_Rec.Bsc_Level_Id;
268 begin
269 x_return_status := FND_API.G_RET_STS_SUCCESS;
270 FND_MSG_PUB.Initialize;
271 SAVEPOINT DeleteBSCDimLevPVT;
272 BSC_APPS.Init_Bsc_Apps;
273
274 l_Dim_Level_Rec := p_Dim_Level_Rec;
275 -- Validate level id exists.
276 if p_Dim_Level_Rec.Bsc_Level_Id is not null then
277 l_count := BSC_DIMENSION_LEVELS_PVT.validate_dim_level_id(p_Dim_Level_Rec.Bsc_Level_Id);
278 if l_count = 0 then
279 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_ID');
280 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', p_Dim_Level_Rec.Bsc_Level_Id);
281 FND_MSG_PUB.ADD;
282 RAISE FND_API.G_EXC_ERROR;
283 end if;
284 else
285 FND_MESSAGE.SET_NAME('BSC','BSC_NO_LEVEL_ID_ENTERED');
286 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', p_Dim_Level_Rec.Bsc_Level_Id);
287 FND_MSG_PUB.ADD;
288 RAISE FND_API.G_EXC_ERROR;
289 end if;
290 IF ((l_Dim_Level_Rec.Bsc_Level_Name IS NOT NULL) OR
291 (l_Dim_Level_Rec.Bsc_Level_View_Name IS NOT NULL) OR
292 (l_Dim_Level_Rec.Bsc_Source IS NOT NULL)) THEN
293 IF (c_drop_tabs%ISOPEN) THEN
294 CLOSE c_drop_tabs;
295 END IF;
296 OPEN c_drop_tabs;
297 FETCH c_drop_tabs
298 INTO l_Dim_Level_Rec.Bsc_Level_Name
299 , l_Dim_Level_Rec.Bsc_Level_View_Name
300 , l_Dim_Level_Rec.Bsc_Source;
301 CLOSE c_drop_tabs;
302 END IF;
303
304 -- Changed all the calls from Do_Ddl() to Do_Ddl_AT() [Autonomous Transaction]
305 -- For Bug#5011937
306 IF(l_Dim_Level_Rec.Bsc_Source = 'PMF') THEN
307 -- Check view exists.
308 IF (BSC_UTILITY.is_Table_View_Exists(l_Dim_Level_Rec.Bsc_Level_Name)) THEN
309
310 -- SQL to drop dimension level view.
311 l_sql := 'DROP VIEW ' || l_Dim_Level_Rec.Bsc_Level_Name;
312 --EXECUTE IMMEDIATE l_sql;
313 BSC_APPS.Do_Ddl_AT(l_sql, ad_ddl.drop_view, l_Dim_Level_Rec.Bsc_Level_Name, BSC_APPS.fnd_apps_schema, BSC_APPS.bsc_apps_short_name);
314 end if;
315 ELSIF(l_Dim_Level_Rec.Bsc_Source = 'BSC') THEN
316 --delete the enteries from BSC_DB_TABLES and BSC_DB_TABLES_RELS
317 DELETE FROM BSC_DB_TABLES
318 WHERE Table_Name = 'BSC_DI_'||p_Dim_Level_Rec.Bsc_Level_Id;
319
320 DELETE FROM BSC_DB_TABLES_RELS
321 WHERE Source_Table_Name = 'BSC_DI_'||p_Dim_Level_Rec.Bsc_Level_Id;
322
323 --DROP VIEW
324 l_view_name := 'BSC_D_'||l_Dim_Level_Rec.Bsc_Level_Id||'_VL';
325 l_sql := ' SELECT COUNT(1) FROM USER_OBJECTS '||
326 ' WHERE OBJECT_NAME = :1';
327 EXECUTE IMMEDIATE l_sql INTO l_count USING l_view_name;
328
329 IF (l_count <> 0) THEN
330 l_sql := 'DROP VIEW BSC_D_'||l_Dim_Level_Rec.Bsc_Level_Id||'_VL';
331 --EXECUTE IMMEDIATE l_sql;
332 BSC_APPS.Do_Ddl_AT(l_sql, ad_ddl.drop_view, 'BSC_D_'||l_Dim_Level_Rec.Bsc_Level_Id||'_VL', BSC_APPS.fnd_apps_schema, BSC_APPS.bsc_apps_short_name);
333 END IF;
334
335 --DROP MASTER TABLE
336 l_sql := ' SELECT COUNT(1) FROM USER_OBJECTS '||
337 ' WHERE OBJECT_NAME = :1';
338 EXECUTE IMMEDIATE l_sql INTO l_count USING l_Dim_Level_Rec.Bsc_Level_Name ;
339
340 IF (l_count <> 0) THEN
341 l_sql := 'DROP TABLE '||l_Dim_Level_Rec.Bsc_Level_Name;
342 BSC_APPS.Do_Ddl_AT(l_sql, ad_ddl.drop_table, l_Dim_Level_Rec.Bsc_Level_Name, BSC_APPS.fnd_apps_schema, BSC_APPS.bsc_apps_short_name);
343 END IF;
344
345 --DROP INPUT TABLE
346 l_view_name := 'BSC_DI_'||l_Dim_Level_Rec.Bsc_Level_Id;
347 l_sql := ' SELECT COUNT(1) FROM USER_OBJECTS '||
348 ' WHERE OBJECT_NAME = :1';
349 EXECUTE IMMEDIATE l_sql INTO l_count USING l_view_name;
350 IF (l_count <> 0) THEN
351 l_sql := 'DROP TABLE BSC_DI_'||l_Dim_Level_Rec.Bsc_Level_Id;
352 BSC_APPS.Do_Ddl_AT(l_sql, ad_ddl.drop_table, 'BSC_DI_'||l_Dim_Level_Rec.Bsc_Level_Id, BSC_APPS.fnd_apps_schema, BSC_APPS.bsc_apps_short_name);
353 END IF;
354
355 --remove/drop filter views if any
356 BSC_DIM_FILTERS_PUB.Drop_Filter_By_Dim_Obj
357 ( p_Dim_Level_Id => p_Dim_Level_Rec.Bsc_Level_Id
358 , x_return_status => x_return_status
359 , x_msg_Count => x_msg_Count
360 , x_msg_data => x_msg_data
361 );
362 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
363 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
364 END IF;
365 END IF;
366 if (p_commit = FND_API.G_TRUE) then
367 commit;
368 end if;
369
370 EXCEPTION
371 WHEN FND_API.G_EXC_ERROR THEN
372 IF (c_drop_tabs%ISOPEN) THEN
373 CLOSE c_drop_tabs;
374 END IF;
375 ROLLBACK TO DeleteBSCDimLevPVT;
376 FND_MSG_PUB.Count_And_Get
377 ( p_encoded => FND_API.G_FALSE
378 , p_count => x_msg_count
379 , p_data => x_msg_data
380 );
381 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
382 x_return_status := FND_API.G_RET_STS_ERROR;
383 RAISE;
384 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
385 IF (c_drop_tabs%ISOPEN) THEN
386 CLOSE c_drop_tabs;
387 END IF;
388 ROLLBACK TO DeleteBSCDimLevPVT;
389 FND_MSG_PUB.Count_And_Get
390 ( p_encoded => FND_API.G_FALSE
391 , p_count => x_msg_count
392 , p_data => x_msg_data
393 );
394 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
395 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
396 RAISE;
397 WHEN NO_DATA_FOUND THEN
398 IF (c_drop_tabs%ISOPEN) THEN
399 CLOSE c_drop_tabs;
400 END IF;
401 ROLLBACK TO DeleteBSCDimLevPVT;
402 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
403 IF (x_msg_data IS NOT NULL) THEN
404 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level ';
405 ELSE
406 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level ';
407 END IF;
408 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
409 RAISE;
410 WHEN OTHERS THEN
411 IF (c_drop_tabs%ISOPEN) THEN
412 CLOSE c_drop_tabs;
413 END IF;
414 ROLLBACK TO DeleteBSCDimLevPVT;
415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
416 IF (x_msg_data IS NOT NULL) THEN
417 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level ';
418 ELSE
419 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level ';
420 END IF;
421 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
422 RAISE;
423 end Delete_Dim_Level;
424
425 /************************************************************************************
426 05-JUN-2003 - Aditya modified to add WHO Cols for Granular Locking.
427 ************************************************************************************/
428
429 --: This procedure populates the meta data for BSC dimensions, such as
430 --: dimension id, dimension names, dimension view/table columns.
431 --: This procedure is part of the Dimension API.
432
433 procedure Create_Bsc_Dim_Levels_Md(
434 p_commit IN varchar2 := FND_API.G_FALSE
435 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
436 ,x_return_status OUT NOCOPY varchar2
437 ,x_msg_count OUT NOCOPY number
438 ,x_msg_data OUT NOCOPY varchar2
439 ) is
440
441 l_Dim_Level_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
442 l_count number;
443 l_level_view_name VARCHAR2(30);
444 begin
445 x_return_status := FND_API.G_RET_STS_SUCCESS;
446 l_Dim_Level_Rec := p_Dim_Level_Rec;
447
448 SAVEPOINT CreateBSCDimLevMdPVT;
449 FND_MSG_PUB.Initialize;
450 -- Validate Level Id not exists.
451 if p_Dim_Level_Rec.Bsc_Level_Id is not null then
452 l_count := BSC_DIMENSION_LEVELS_PVT.validate_dim_level_id(p_Dim_Level_Rec.Bsc_Level_Id);
453 if l_count <> 0 then
454 FND_MESSAGE.SET_NAME('BSC','BSC_LEVEL_ID_EXISTS');
455 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', p_Dim_Level_Rec.Bsc_Level_Id);
456 FND_MSG_PUB.ADD;
457 RAISE FND_API.G_EXC_ERROR;
458 end if;
459 else
460 FND_MESSAGE.SET_NAME('BSC','BSC_NO_LEVEL_ID_ENTERED');
461 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', p_Dim_Level_Rec.Bsc_Level_Id);
462 FND_MSG_PUB.ADD;
463 RAISE FND_API.G_EXC_ERROR;
464 end if;
465
466 g_db_object := 'BSC_SYS_DIM_LEVELS_B';
467
468 -- insert the pertaining values into table bsc_sys_dim_levels_b.
469 -- Reminder: Some values are hard coded, need to get them from somewhere.
470 --if source= 'PMF' same values will be inserted for level_table_name, level_view_name
471 --if source= 'BSC' level_table_name will contain the name of master table and
472 -- level_view_name will contain the name of view name.
473 IF (p_Dim_Level_Rec.Bsc_Source = 'PMF') THEN
474 l_level_view_name := p_Dim_Level_Rec.Bsc_Level_Name;
475 ELSE
476 l_level_view_name := p_Dim_Level_Rec.Bsc_Level_View_Name;
477 END IF;
478 l_Dim_Level_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Level_Rec.Bsc_Last_Update_Date, SYSDATE);
479
480 insert into BSC_SYS_DIM_LEVELS_B( dim_level_id
481 ,level_table_name
482 ,table_type
483 ,level_pk_col
484 ,abbreviation
485 ,value_order_by
486 ,comp_order_by
487 ,custom_group
488 ,user_key_size
489 ,disp_key_size
490 ,level_view_name
491 ,short_name
492 ,source
493 ,created_by --PMD
494 ,creation_date --PMD
495 ,last_updated_by --PMD
496 ,last_update_date --PMD
497 ,last_update_login) --PMD
498 values( p_Dim_Level_Rec.Bsc_Level_Id
499 ,p_Dim_Level_Rec.Bsc_Level_Name
500 ,p_Dim_Level_Rec.Bsc_Level_Table_Type
501 ,p_Dim_Level_Rec.Bsc_Pk_Col
502 ,p_Dim_Level_Rec.Bsc_Level_Abbreviation
503 ,p_Dim_Level_Rec.Bsc_Level_Value_Order_By
504 ,p_Dim_Level_Rec.Bsc_Level_Comp_Order_By
505 ,p_Dim_Level_Rec.Bsc_Level_Custom_Group
506 ,p_Dim_Level_Rec.Bsc_Level_User_Key_Size
507 ,p_Dim_Level_Rec.Bsc_Level_Disp_Key_Size
508 ,l_level_view_name
509 ,p_Dim_Level_Rec.Bsc_Level_Short_Name
510 ,p_Dim_Level_Rec.Bsc_Source
511 ,p_Dim_Level_Rec.Bsc_Created_By --PMD
512 ,l_Dim_Level_Rec.Bsc_Last_Update_Date --PMD
513 ,p_Dim_Level_Rec.Bsc_Last_Updated_By --PMD
514 ,l_Dim_Level_Rec.Bsc_Last_Update_Date --PMD
515 ,p_Dim_Level_Rec.Bsc_Last_Update_Login); --PMD
516 g_db_object := 'BSC_SYS_DIM_LEVELS_TL';
517 -- Insert values into table bsc_sys_dim_levels_tl.
518 -- Reminder: The last two values are hard coded, need to get them from somewhere,
519 -- talk to Henry.
520 insert into BSC_SYS_DIM_LEVELS_TL( dim_level_id
521 ,language
522 ,source_lang
523 ,name
524 ,help
525 ,total_disp_name
526 ,comp_disp_name
527 )
528 SELECT p_Dim_Level_Rec.Bsc_Level_Id
529 , L.LANGUAGE_CODE
530 , USERENV('LANG')
531 , p_Dim_Level_Rec.Bsc_Dim_Level_Long_Name
532 , p_Dim_Level_Rec.Bsc_Dim_Level_Help
533 , p_Dim_Level_Rec.Bsc_Dim_Tot_Disp_Name
534 , p_Dim_Level_Rec.Bsc_Dim_Comp_Disp_Name
535 FROM FND_LANGUAGES L
536 WHERE L.INSTALLED_FLAG IN ('I', 'B')
537 AND NOT EXISTS
538 ( SELECT NULL
539 FROM BSC_SYS_DIM_LEVELS_TL T
540 WHERE T.DIM_LEVEL_ID = p_Dim_Level_Rec.Bsc_Level_Id
541 AND T.LANGUAGE = L.LANGUAGE_CODE);
542
543 if (p_commit = FND_API.G_TRUE) then
544 commit;
545 end if;
546
547 EXCEPTION
548 WHEN FND_API.G_EXC_ERROR THEN
549 ROLLBACK TO CreateBSCDimLevMdPVT;
550 FND_MSG_PUB.Count_And_Get
551 ( p_encoded => FND_API.G_FALSE
552 , p_count => x_msg_count
553 , p_data => x_msg_data
554 );
555 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
556 x_return_status := FND_API.G_RET_STS_ERROR;
557 RAISE;
558 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
559 ROLLBACK TO CreateBSCDimLevMdPVT;
560 FND_MSG_PUB.Count_And_Get
561 ( p_encoded => FND_API.G_FALSE
562 , p_count => x_msg_count
563 , p_data => x_msg_data
564 );
565 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
566 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
567 RAISE;
568 WHEN NO_DATA_FOUND THEN
569 ROLLBACK TO CreateBSCDimLevMdPVT;
570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
571 IF (x_msg_data IS NOT NULL) THEN
572 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Create_Bsc_Dim_Levels_Md ';
573 ELSE
574 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Create_Bsc_Dim_Levels_Md ';
575 END IF;
576 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
577 RAISE;
578 WHEN OTHERS THEN
579 ROLLBACK TO CreateBSCDimLevMdPVT;
580 if (SQLCODE = -01400) then
581 FND_MESSAGE.SET_NAME('BSC','BSC_TABLE_NULL_VALUE');
582 FND_MESSAGE.SET_TOKEN('BSC_OBJECT', g_db_object);
583 FND_MSG_PUB.ADD;
584 RAISE FND_API.G_EXC_ERROR;
585 end if;
586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
587 IF (x_msg_data IS NOT NULL) THEN
588 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Create_Bsc_Dim_Levels_Md ';
589 ELSE
590 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Create_Bsc_Dim_Levels_Md ';
591 END IF;
592 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
593 RAISE;
594 end Create_Bsc_Dim_Levels_Md;
595
596 /************************************************************************************
597 ************************************************************************************/
598
599 procedure Retrieve_Bsc_Dim_Levels_Md(
600 p_commit IN varchar2 := FND_API.G_FALSE
601 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
602 ,x_Dim_Level_Rec IN OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
603 ,x_return_status OUT NOCOPY varchar2
604 ,x_msg_count OUT NOCOPY number
605 ,x_msg_data OUT NOCOPY varchar2
606 ) is
607
608 begin
609
610 x_return_status:= FND_API.G_RET_STS_SUCCESS;
611 FND_MSG_PUB.Initialize;
612 g_db_object := 'Retrieve_Bsc_Dim_Levels_Md';
613
614 select a.level_view_name
615 ,a.level_table_name
616 ,a.table_type
617 ,a.level_pk_col
618 ,a.abbreviation
619 ,a.value_order_by
620 ,a.comp_order_by
621 ,a.custom_group
622 ,a.user_key_size
623 ,a.disp_key_size
624 ,a.short_name
625 ,a.source
626 ,b.name
627 ,b.help
628 ,b.total_disp_name
629 ,b.comp_disp_name
630 ,a.created_by -- PMD
631 ,a.creation_date -- PMD
632 ,a.last_updated_by -- PMD
633 ,a.last_update_date -- PMD
634 ,a.last_update_login -- PMD
635 into x_Dim_Level_Rec.Bsc_Level_View_Name
636 ,x_Dim_Level_Rec.Bsc_Level_Name
637 ,x_Dim_Level_Rec.Bsc_Level_Table_Type
638 ,x_Dim_Level_Rec.Bsc_Pk_Col
639 ,x_Dim_Level_Rec.Bsc_Level_Abbreviation
640 ,x_Dim_Level_Rec.Bsc_Level_Value_Order_By
641 ,x_Dim_Level_Rec.Bsc_Level_Comp_Order_By
642 ,x_Dim_Level_Rec.Bsc_Level_Custom_Group
643 ,x_Dim_Level_Rec.Bsc_Level_User_Key_Size
644 ,x_Dim_Level_Rec.Bsc_Level_Disp_Key_Size
645 ,x_Dim_Level_Rec.Bsc_Level_Short_Name
646 ,x_Dim_Level_Rec.Bsc_Source
647 ,x_Dim_Level_Rec.Bsc_Dim_Level_Long_Name
648 ,x_Dim_Level_Rec.Bsc_Dim_Level_Help
649 ,x_Dim_Level_Rec.Bsc_Dim_Tot_Disp_Name
650 ,x_Dim_Level_Rec.Bsc_Dim_Comp_Disp_Name
651 ,x_Dim_Level_Rec.Bsc_Created_By -- PMD
652 ,x_Dim_Level_Rec.Bsc_Creation_Date -- PMD
653 ,x_Dim_Level_Rec.Bsc_Last_Updated_By -- PMD
654 ,x_Dim_Level_Rec.Bsc_Last_Update_Date -- PMD
655 ,x_Dim_Level_Rec.Bsc_Last_Update_Login -- PMD
656 from BSC_SYS_DIM_LEVELS_B a
657 ,BSC_SYS_DIM_LEVELS_TL b
658 where a.dim_level_id = b.dim_level_id
659 and a.dim_level_id = p_Dim_Level_Rec.Bsc_Level_Id
660 and b.language = NVL(p_Dim_Level_Rec.Bsc_Language,USERENV('LANG')); --Bug #3528143
661
662
663 if (p_commit = FND_API.G_TRUE) then
664 commit;
665 end if;
666
667 EXCEPTION
668 WHEN FND_API.G_EXC_ERROR THEN
669 FND_MSG_PUB.Count_And_Get
670 ( p_encoded => FND_API.G_FALSE
671 , p_count => x_msg_count
672 , p_data => x_msg_data
673 );
674 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
675 x_return_status := FND_API.G_RET_STS_ERROR;
676 RAISE;
677 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
678 FND_MSG_PUB.Count_And_Get
679 ( p_encoded => FND_API.G_FALSE
680 , p_count => x_msg_count
681 , p_data => x_msg_data
682 );
683 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
684 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
685 RAISE;
686 WHEN NO_DATA_FOUND THEN
687 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
688 IF (x_msg_data IS NOT NULL) THEN
689 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Retrieve_Bsc_Dim_Levels_Md ';
690 ELSE
691 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Retrieve_Bsc_Dim_Levels_Md ';
692 END IF;
693 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
694 RAISE;
695 WHEN OTHERS THEN
696 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
697 IF (x_msg_data IS NOT NULL) THEN
698 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Retrieve_Bsc_Dim_Levels_Md ';
699 ELSE
700 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Retrieve_Bsc_Dim_Levels_Md ';
701 END IF;
702 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
703 RAISE;
704 end Retrieve_Bsc_Dim_Levels_Md;
705
706 /************************************************************************************
707 ************************************************************************************/
708
709 procedure Update_Bsc_Dim_Levels_Md(
710 p_commit IN varchar2 := FND_API.G_FALSE
711 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
712 ,x_return_status OUT NOCOPY varchar2
713 ,x_msg_count OUT NOCOPY number
714 ,x_msg_data OUT NOCOPY varchar2
715 ) is
716
717 l_Dim_Level_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
718 l_level_view_name VARCHAR2(30);
719 l_count number;
720
721 begin
722 x_return_status := FND_API.G_RET_STS_SUCCESS;
723 FND_MSG_PUB.Initialize;
724 SAVEPOINT UpdateBSCDimLevMdPVT;
725 -- Check that valid level id was entered.
726 if p_Dim_Level_Rec.Bsc_Level_Id is not null then
727 l_count := validate_dim_level_id(p_Dim_Level_Rec.Bsc_Level_Id);
728 if l_count = 0 then
729 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_ID');
730 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', p_Dim_Level_Rec.Bsc_Level_Id);
731 FND_MSG_PUB.ADD;
732 RAISE FND_API.G_EXC_ERROR;
733 end if;
734 else
735 FND_MESSAGE.SET_NAME('BSC','BSC_NO_LEVEL_ID_ENTERED');
736 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', p_Dim_Level_Rec.Bsc_Level_Id);
737 FND_MSG_PUB.ADD;
738 RAISE FND_API.G_EXC_ERROR;
739 end if;
740
741 -- Not all values will be passed. We need to make sure values not passed are not
742 -- changed by procedure, therefore we get what is there before we do any updates.
743 Retrieve_Bsc_Dim_Levels_Md( p_commit
744 ,p_Dim_Level_Rec
745 ,l_Dim_Level_Rec
746 ,x_return_status
747 ,x_msg_count
748 ,x_msg_data);
749
750 -- update LOCAL language ,source language and level Id values with PASSED values.
751 l_Dim_Level_Rec.Bsc_Language := p_Dim_Level_Rec.Bsc_Language;
752 l_Dim_Level_Rec.Bsc_Source_Language := p_Dim_Level_Rec.Bsc_Source_Language;
753 l_Dim_Level_Rec.Bsc_Level_Id := p_Dim_Level_Rec.Bsc_Level_Id;
754
755 -- Copy PASSED Record values into LOCAL Record values for the PASSED Record values
756 -- which are NOT NULL.
757 if p_Dim_Level_Rec.Bsc_Level_Name is not null then
758 l_Dim_Level_Rec.Bsc_Level_Name := p_Dim_Level_Rec.Bsc_Level_Name;
759 end if;
760 if p_Dim_Level_Rec.Bsc_Level_Table_Type is not null then
761 l_Dim_Level_Rec.Bsc_Level_Table_Type := p_Dim_Level_Rec.Bsc_Level_Table_Type;
762 end if;
763 if p_Dim_Level_Rec.Bsc_Pk_Col is not null then
764 l_Dim_Level_Rec.Bsc_Pk_Col := p_Dim_Level_Rec.Bsc_Pk_Col;
765 end if;
766 if p_Dim_Level_Rec.Bsc_Level_Abbreviation is not null then
767 l_Dim_Level_Rec.Bsc_Level_Abbreviation := p_Dim_Level_Rec.Bsc_Level_Abbreviation;
768 end if;
769 if p_Dim_Level_Rec.Bsc_Level_Value_Order_By is not null then
770 l_Dim_Level_Rec.Bsc_Level_Value_Order_By := p_Dim_Level_Rec.Bsc_Level_Value_Order_By;
771 end if;
772 if p_Dim_Level_Rec.Bsc_Level_Comp_Order_By is not null then
773 l_Dim_Level_Rec.Bsc_Level_Comp_Order_By := p_Dim_Level_Rec.Bsc_Level_Comp_Order_By;
774 end if;
775 if p_Dim_Level_Rec.Bsc_Level_Custom_Group is not null then
776 l_Dim_Level_Rec.Bsc_Level_Custom_Group := p_Dim_Level_Rec.Bsc_Level_Custom_Group;
777 end if;
778 if p_Dim_Level_Rec.Bsc_Level_User_Key_Size is not null then
779 l_Dim_Level_Rec.Bsc_Level_User_Key_Size := p_Dim_Level_Rec.Bsc_Level_User_Key_Size;
780 end if;
781 if p_Dim_Level_Rec.Bsc_Level_Disp_Key_Size is not null then
782 l_Dim_Level_Rec.Bsc_Level_Disp_Key_Size := p_Dim_Level_Rec.Bsc_Level_Disp_Key_Size;
783 end if;
784 if p_Dim_Level_Rec.Bsc_Level_View_Name is not null then
785 l_Dim_Level_Rec.Bsc_Level_View_Name := p_Dim_Level_Rec.Bsc_Level_View_Name;
786 end if;
787 if p_Dim_Level_Rec.Bsc_Dim_Level_Long_Name is not null then
788 l_Dim_Level_Rec.Bsc_Dim_Level_Long_Name := p_Dim_Level_Rec.Bsc_Dim_Level_Long_Name;
789 end if;
790 if p_Dim_Level_Rec.Bsc_Dim_Level_Help is not null then
791 l_Dim_Level_Rec.Bsc_Dim_Level_Help := p_Dim_Level_Rec.Bsc_Dim_Level_Help;
792 end if;
793 if p_Dim_Level_Rec.Bsc_Dim_Tot_Disp_Name is not null then
794 l_Dim_Level_Rec.Bsc_Dim_Tot_Disp_Name := p_Dim_Level_Rec.Bsc_Dim_Tot_Disp_Name;
795 end if;
796 if p_Dim_Level_Rec.Bsc_Dim_Comp_Disp_Name is not null then
797 l_Dim_Level_Rec.Bsc_Dim_Comp_Disp_Name := p_Dim_Level_Rec.Bsc_Dim_Comp_Disp_Name;
798 end if;
799 if p_Dim_Level_Rec.Bsc_Source is not null then
800 l_Dim_Level_Rec.Bsc_Source := p_Dim_Level_Rec.Bsc_Source;
801 end if;
802 -- PMD
803 if p_Dim_Level_Rec.Bsc_Last_Updated_By is not null then
804 l_Dim_Level_Rec.Bsc_Last_Updated_By := p_Dim_Level_Rec.Bsc_Last_Updated_By;
805 else
806 l_Dim_Level_Rec.Bsc_Last_Updated_By := FND_GLOBAL.USER_ID; -- not null column
807 end if;
808
809 if p_Dim_Level_Rec.Bsc_Last_Update_Login is not null then
810 l_Dim_Level_Rec.Bsc_Last_Update_Login := p_Dim_Level_Rec.Bsc_Last_Update_Login;
811 else
812 l_Dim_Level_Rec.Bsc_Last_Update_Login := FND_GLOBAL.LOGIN_ID;
813 end if;
814 -- PMD
815
816 --if source= 'PMF' same values will be inserted for level_table_name, level_view_name
817 --if source= 'BSC' level_table_name will contain the name of master table and
818 -- level_view_name will contain the name of view name.
819 IF (p_Dim_Level_Rec.Bsc_Source = 'PMF') THEN
820 l_level_view_name := l_Dim_Level_Rec.Bsc_Level_Name;
821 ELSE
822 l_level_view_name := l_Dim_Level_Rec.Bsc_Level_View_Name;
823 END IF;
824
825
826 l_Dim_Level_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Level_Rec.Bsc_Last_Update_Date, SYSDATE);
827
828 update BSC_SYS_DIM_LEVELS_B
829 set level_table_name = l_Dim_Level_Rec.Bsc_Level_Name
830 ,table_type = l_Dim_Level_Rec.Bsc_Level_Table_Type
831 ,level_pk_col = l_Dim_Level_Rec.Bsc_Pk_Col
832 ,abbreviation = l_Dim_Level_Rec.Bsc_Level_Abbreviation
833 ,value_order_by = l_Dim_Level_Rec.Bsc_Level_Value_Order_By
834 ,comp_order_by = l_Dim_Level_Rec.Bsc_Level_Comp_Order_By
835 ,custom_group = l_Dim_Level_Rec.Bsc_Level_Custom_Group
836 ,user_key_size = l_Dim_Level_Rec.Bsc_Level_User_Key_Size
837 ,disp_key_size = l_Dim_Level_Rec.Bsc_Level_Disp_Key_Size
838 ,level_view_name = l_level_view_name
839 ,source = l_Dim_Level_Rec.Bsc_Source
840 ,last_updated_by = l_Dim_Level_Rec.Bsc_Last_Updated_By -- PMD
841 ,last_update_date = l_Dim_Level_Rec.Bsc_Last_Update_Date -- PMD
842 ,last_update_login = l_Dim_Level_Rec.Bsc_Last_Update_Login -- PMD
843 where dim_level_id = l_Dim_Level_Rec.Bsc_Level_Id;
844
845 update BSC_SYS_DIM_LEVELS_TL
846 set name = l_Dim_Level_Rec.Bsc_Dim_Level_Long_Name
847 ,help = l_Dim_Level_Rec.Bsc_Dim_Level_Help
848 ,total_disp_name = l_Dim_Level_Rec.Bsc_Dim_Tot_Disp_Name
849 ,comp_disp_name = l_Dim_Level_Rec.Bsc_Dim_Comp_Disp_Name
850 ,SOURCE_LANG = userenv('LANG')
851 where dim_level_id = l_Dim_Level_Rec.Bsc_Level_Id
852 and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
853
854 if (p_commit = FND_API.G_TRUE) then
855 commit;
856 end if;
857
858 EXCEPTION
859 WHEN FND_API.G_EXC_ERROR THEN
860 ROLLBACK TO UpdateBSCDimLevMdPVT;
861 FND_MSG_PUB.Count_And_Get
862 ( p_encoded => FND_API.G_FALSE
863 , p_count => x_msg_count
864 , p_data => x_msg_data
865 );
866 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
867 x_return_status := FND_API.G_RET_STS_ERROR;
868 RAISE;
869 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
870 ROLLBACK TO UpdateBSCDimLevMdPVT;
871 FND_MSG_PUB.Count_And_Get
872 ( p_encoded => FND_API.G_FALSE
873 , p_count => x_msg_count
874 , p_data => x_msg_data
875 );
876 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
877 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
878 RAISE;
879 WHEN NO_DATA_FOUND THEN
880 ROLLBACK TO UpdateBSCDimLevMdPVT;
881 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
882 IF (x_msg_data IS NOT NULL) THEN
883 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Update_Bsc_Dim_Levels_Md ';
884 ELSE
885 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Update_Bsc_Dim_Levels_Md ';
886 END IF;
887 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
888 RAISE;
889 WHEN OTHERS THEN
890 ROLLBACK TO UpdateBSCDimLevMdPVT;
891 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
892 IF (x_msg_data IS NOT NULL) THEN
893 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Update_Bsc_Dim_Levels_Md ';
894 ELSE
895 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Update_Bsc_Dim_Levels_Md ';
896 END IF;
897 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
898 RAISE;
899 end Update_Bsc_Dim_Levels_Md;
900
901 /************************************************************************************
902 ************************************************************************************/
903
904 procedure Delete_Bsc_Dim_Levels_Md(
905 p_commit IN varchar2 := FND_API.G_FALSE
906 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
907 ,x_return_status OUT NOCOPY varchar2
908 ,x_msg_count OUT NOCOPY number
909 ,x_msg_data OUT NOCOPY varchar2
910 ) is
911
912 l_id number;
913
914 l_sql varchar2(1000);
915
916 begin
917 x_return_status := FND_API.G_RET_STS_SUCCESS;
918 SAVEPOINT DeleteBSCDimLevMdPVT;
919 delete from BSC_SYS_DIM_LEVELS_B
920 where dim_level_id = p_Dim_Level_Rec.Bsc_Level_Id;
921
922 delete from BSC_SYS_DIM_LEVELS_TL
923 where dim_level_id = p_Dim_Level_Rec.Bsc_Level_Id;
924
925 --PAJOHRI Added 01-JUN-2003
926 -- delete dimensin from groups
927 DELETE FROM BSC_SYS_DIM_LEVELS_BY_GROUP
928 WHERE dim_level_id = p_Dim_Level_Rec.Bsc_Level_Id;
929
930 --delete dimension from relationships
931 DELETE FROM BSC_SYS_DIM_LEVEL_RELS
932 WHERE dim_level_id = p_Dim_Level_Rec.Bsc_Level_Id
933 OR parent_dim_level_id = p_Dim_Level_Rec.Bsc_Level_Id;
934 if (p_commit = FND_API.G_TRUE) then
935 commit;
936 end if;
937
938 EXCEPTION
939 WHEN FND_API.G_EXC_ERROR THEN
940 ROLLBACK TO DeleteBSCDimLevMdPVT;
941 FND_MSG_PUB.Count_And_Get
942 ( p_encoded => FND_API.G_FALSE
943 , p_count => x_msg_count
944 , p_data => x_msg_data
945 );
946 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
947 x_return_status := FND_API.G_RET_STS_ERROR;
948 RAISE;
949 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
950 ROLLBACK TO DeleteBSCDimLevMdPVT;
951 FND_MSG_PUB.Count_And_Get
952 ( p_encoded => FND_API.G_FALSE
953 , p_count => x_msg_count
954 , p_data => x_msg_data
955 );
956 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
957 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
958 RAISE;
959 WHEN NO_DATA_FOUND THEN
960 ROLLBACK TO DeleteBSCDimLevMdPVT;
961 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
962 IF (x_msg_data IS NOT NULL) THEN
963 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Delete_Bsc_Dim_Levels_Md ';
964 ELSE
965 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Delete_Bsc_Dim_Levels_Md ';
966 END IF;
967 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
968 RAISE;
969 WHEN OTHERS THEN
970 ROLLBACK TO DeleteBSCDimLevMdPVT;
971 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
972 IF (x_msg_data IS NOT NULL) THEN
973 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Delete_Bsc_Dim_Levels_Md ';
974 ELSE
975 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Delete_Bsc_Dim_Levels_Md ';
976 END IF;
977 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
978 RAISE;
979 end Delete_Bsc_Dim_Levels_Md;
980
981 /************************************************************************************
982 ************************************************************************************/
983
984 --: This procedure populates column information for the Dimension view/table.
985 --: This procedure is part of the Dimension API.
986
987 procedure Create_Bsc_Sys_Dim_Lvl_Cols(
988 p_commit IN varchar2 := FND_API.G_FALSE
989 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
990 ,x_return_status OUT NOCOPY varchar2
991 ,x_msg_count OUT NOCOPY number
992 ,x_msg_data OUT NOCOPY varchar2
993 ) is
994
995 l_count number;
996
997 begin
998 x_return_status := FND_API.G_RET_STS_SUCCESS;
999 FND_MSG_PUB.Initialize;
1000 SAVEPOINT CreateBSCSysDimLevColsPVT;
1001 -- Validate Level Id exists.
1002 if p_Dim_Level_Rec.Bsc_Level_Id is not null then
1003 l_count := BSC_DIMENSION_LEVELS_PVT.validate_dim_level_id(p_Dim_Level_Rec.Bsc_Level_Id);
1004 if l_count <> 1 then
1005 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_ID');
1006 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', p_Dim_Level_Rec.Bsc_Level_Id);
1007 FND_MSG_PUB.ADD;
1008 RAISE FND_API.G_EXC_ERROR;
1009 end if;
1010 else
1011 FND_MESSAGE.SET_NAME('BSC','BSC_NO_LEVEL_ID_ENTERED');
1012 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', p_Dim_Level_Rec.Bsc_Level_Id);
1013 FND_MSG_PUB.ADD;
1014 RAISE FND_API.G_EXC_ERROR;
1015 end if;
1016
1017 g_db_object := 'BSC_SYS_DIM_LEVEL_COLS';
1018
1019 -- Insert pertaining values into table bsc_sys_dim_level_cols.
1020 insert into BSC_SYS_DIM_LEVEL_COLS( dim_level_id
1021 ,column_name
1022 ,column_type)
1023 values( p_Dim_Level_Rec.Bsc_Level_Id
1024 ,p_Dim_Level_Rec.Bsc_Level_Column_Name
1025 ,p_Dim_Level_Rec.Bsc_Level_Column_Type);
1026
1027 if (p_commit = FND_API.G_TRUE) then
1028 commit;
1029 end if;
1030
1031 EXCEPTION
1032 WHEN FND_API.G_EXC_ERROR THEN
1033 ROLLBACK TO CreateBSCSysDimLevColsPVT;
1034 FND_MSG_PUB.Count_And_Get
1035 ( p_encoded => FND_API.G_FALSE
1036 , p_count => x_msg_count
1037 , p_data => x_msg_data
1038 );
1039 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1040 x_return_status := FND_API.G_RET_STS_ERROR;
1041 RAISE;
1042 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1043 ROLLBACK TO CreateBSCSysDimLevColsPVT;
1044 FND_MSG_PUB.Count_And_Get
1045 ( p_encoded => FND_API.G_FALSE
1046 , p_count => x_msg_count
1047 , p_data => x_msg_data
1048 );
1049 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1050 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1051 RAISE;
1052 WHEN NO_DATA_FOUND THEN
1053 ROLLBACK TO CreateBSCSysDimLevColsPVT;
1054 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1055 IF (x_msg_data IS NOT NULL) THEN
1056 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Create_Bsc_Sys_Dim_Lvl_Cols ';
1057 ELSE
1058 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Create_Bsc_Sys_Dim_Lvl_Cols ';
1059 END IF;
1060 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1061 RAISE;
1062 WHEN OTHERS THEN
1063 ROLLBACK TO CreateBSCSysDimLevColsPVT;
1064 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1065 if (SQLCODE = -01400) then
1066 FND_MESSAGE.SET_NAME('BSC','BSC_TABLE_NULL_VALUE');
1067 FND_MESSAGE.SET_TOKEN('BSC_OBJECT', g_db_object);
1068 FND_MSG_PUB.ADD;
1069 RAISE FND_API.G_EXC_ERROR;
1070 end if;
1071 IF (x_msg_data IS NOT NULL) THEN
1072 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Create_Bsc_Sys_Dim_Lvl_Cols ';
1073 ELSE
1074 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Create_Bsc_Sys_Dim_Lvl_Cols ';
1075 END IF;
1076 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1077 RAISE;
1078 end Create_Bsc_Sys_Dim_Lvl_Cols;
1079
1080 /************************************************************************************
1081 ************************************************************************************/
1082
1083 procedure Retrieve_Bsc_Sys_Dim_Lvl_Cols(
1084 p_commit IN varchar2 := FND_API.G_FALSE
1085 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
1086 ,x_Dim_Level_Rec IN OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_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 begin
1093 x_return_status := FND_API.G_RET_STS_SUCCESS;
1094 g_db_object := 'Retrieve_Bsc_Sys_Dim_Lvl_Cols';
1095
1096 select distinct column_name
1097 into x_Dim_Level_Rec.Bsc_Level_Column_Name
1098 from BSC_SYS_DIM_LEVEL_COLS
1099 where dim_level_id = p_Dim_Level_Rec.Bsc_Level_Id
1100 and column_type = p_Dim_Level_Rec.Bsc_Level_Column_Type;
1101
1102 if (p_commit = FND_API.G_TRUE) then
1103 commit;
1104 end if;
1105
1106 EXCEPTION
1107 WHEN FND_API.G_EXC_ERROR THEN
1108 FND_MSG_PUB.Count_And_Get
1109 ( p_encoded => FND_API.G_FALSE
1110 , p_count => x_msg_count
1111 , p_data => x_msg_data
1112 );
1113 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1114 x_return_status := FND_API.G_RET_STS_ERROR;
1115 RAISE;
1116 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1117 FND_MSG_PUB.Count_And_Get
1118 ( p_encoded => FND_API.G_FALSE
1119 , p_count => x_msg_count
1120 , p_data => x_msg_data
1121 );
1122 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1123 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1124 RAISE;
1125 WHEN NO_DATA_FOUND THEN
1126 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1127 IF (x_msg_data IS NOT NULL) THEN
1128 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Retrieve_Bsc_Sys_Dim_Lvl_Cols ';
1129 ELSE
1130 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Retrieve_Bsc_Sys_Dim_Lvl_Cols ';
1131 END IF;
1132 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1133 RAISE;
1134 WHEN OTHERS THEN
1135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1136 IF (x_msg_data IS NOT NULL) THEN
1137 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Retrieve_Bsc_Sys_Dim_Lvl_Cols ';
1138 ELSE
1139 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Retrieve_Bsc_Sys_Dim_Lvl_Cols ';
1140 END IF;
1141 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1142 RAISE;
1143 end Retrieve_Bsc_Sys_Dim_Lvl_Cols;
1144
1145 /************************************************************************************
1146 ************************************************************************************/
1147
1148 procedure Update_Bsc_Sys_Dim_Lvl_Cols(
1149 p_commit IN varchar2 := FND_API.G_FALSE
1150 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
1151 ,x_return_status OUT NOCOPY varchar2
1152 ,x_msg_count OUT NOCOPY number
1153 ,x_msg_data OUT NOCOPY varchar2
1154 ) is
1155
1156 l_Dim_Level_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
1157
1158 l_count number;
1159
1160 begin
1161 x_return_status := FND_API.G_RET_STS_SUCCESS;
1162 FND_MSG_PUB.Initialize;
1163 SAVEPOINT UpdateBSCSysDimLevColsPVT;
1164 -- Check that valid level id was entered.
1165 if p_Dim_Level_Rec.Bsc_Level_Id is not null then
1166 l_count := validate_dim_level_id(p_Dim_Level_Rec.Bsc_Level_Id);
1167 if l_count <> 1 then
1168 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_ID');
1169 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', p_Dim_Level_Rec.Bsc_Level_Id);
1170 FND_MSG_PUB.ADD;
1171 RAISE FND_API.G_EXC_ERROR;
1172 end if;
1173 else
1174 FND_MESSAGE.SET_NAME('BSC','BSC_NO_LEVEL_ID_ENTERED');
1175 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', p_Dim_Level_Rec.Bsc_Level_Id);
1176 FND_MSG_PUB.ADD;
1177 RAISE FND_API.G_EXC_ERROR;
1178 end if;
1179
1180 -- Level Column Type must be entered, if not raise error.
1181 if l_Dim_Level_Rec.Bsc_Level_Column_Type not in ('A', 'D', 'F', 'P', 'U') then
1182 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_COL_TYPE');
1183 FND_MESSAGE.SET_TOKEN('BSC_COL_TYPE', l_Dim_Level_Rec.Bsc_Level_Column_Type);
1184 FND_MSG_PUB.ADD;
1185 RAISE FND_API.G_EXC_ERROR;
1186 end if;
1187
1188 -- In this case there is only one column that may be updated, therefore there is no
1189 -- retrieval of previous values.
1190
1191 update BSC_SYS_DIM_LEVEL_COLS
1192 set column_name = l_Dim_Level_Rec.Bsc_Level_Column_Name
1193 where dim_level_id = l_Dim_Level_Rec.Bsc_Level_Id
1194 and column_type = l_Dim_Level_Rec.Bsc_Level_Column_Type;
1195
1196 if (p_commit = FND_API.G_TRUE) then
1197 commit;
1198 end if;
1199
1200 EXCEPTION
1201 WHEN FND_API.G_EXC_ERROR THEN
1202 ROLLBACK TO UpdateBSCSysDimLevColsPVT;
1203 FND_MSG_PUB.Count_And_Get
1204 ( p_encoded => FND_API.G_FALSE
1205 , p_count => x_msg_count
1206 , p_data => x_msg_data
1207 );
1208 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1209 x_return_status := FND_API.G_RET_STS_ERROR;
1210 RAISE;
1211 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1212 ROLLBACK TO UpdateBSCSysDimLevColsPVT;
1213 FND_MSG_PUB.Count_And_Get
1214 ( p_encoded => FND_API.G_FALSE
1215 , p_count => x_msg_count
1216 , p_data => x_msg_data
1217 );
1218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1219 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1220 RAISE;
1221 WHEN NO_DATA_FOUND THEN
1222 ROLLBACK TO UpdateBSCSysDimLevColsPVT;
1223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1224 IF (x_msg_data IS NOT NULL) THEN
1225 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Update_Bsc_Sys_Dim_Lvl_Cols ';
1226 ELSE
1227 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Update_Bsc_Sys_Dim_Lvl_Cols ';
1228 END IF;
1229 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1230 RAISE;
1231 WHEN OTHERS THEN
1232 ROLLBACK TO UpdateBSCSysDimLevColsPVT;
1233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1234 IF (x_msg_data IS NOT NULL) THEN
1235 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Update_Bsc_Sys_Dim_Lvl_Cols ';
1236 ELSE
1237 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Update_Bsc_Sys_Dim_Lvl_Cols ';
1238 END IF;
1239 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1240 RAISE;
1241 end Update_Bsc_Sys_Dim_Lvl_Cols;
1242
1243 /************************************************************************************
1244 ************************************************************************************/
1245
1246 procedure Delete_Bsc_Sys_Dim_Lvl_Cols(
1247 p_commit IN varchar2 := FND_API.G_FALSE
1248 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
1249 ,x_return_status OUT NOCOPY varchar2
1250 ,x_msg_count OUT NOCOPY number
1251 ,x_msg_data OUT NOCOPY varchar2
1252 ) is
1253
1254 l_id number;
1255
1256 l_sql varchar2(1000);
1257
1258 begin
1259 x_return_status := FND_API.G_RET_STS_SUCCESS;
1260 SAVEPOINT DeleteBSCSysDimLevColsPVT;
1261 delete from BSC_SYS_DIM_LEVEL_COLS
1262 where dim_level_id = p_Dim_Level_Rec.Bsc_Level_Id;
1263
1264 if (p_commit = FND_API.G_TRUE) then
1265 commit;
1266 end if;
1267
1268 EXCEPTION
1269 WHEN FND_API.G_EXC_ERROR THEN
1270 ROLLBACK TO DeleteBSCSysDimLevColsPVT;
1271 FND_MSG_PUB.Count_And_Get
1272 ( p_encoded => FND_API.G_FALSE
1273 , p_count => x_msg_count
1274 , p_data => x_msg_data
1275 );
1276 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1277 x_return_status := FND_API.G_RET_STS_ERROR;
1278 RAISE;
1279 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1280 ROLLBACK TO DeleteBSCSysDimLevColsPVT;
1281 FND_MSG_PUB.Count_And_Get
1282 ( p_encoded => FND_API.G_FALSE
1283 , p_count => x_msg_count
1284 , p_data => x_msg_data
1285 );
1286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1287 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1288 RAISE;
1289 WHEN NO_DATA_FOUND THEN
1290 ROLLBACK TO DeleteBSCSysDimLevColsPVT;
1291 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1292 IF (x_msg_data IS NOT NULL) THEN
1293 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Delete_Bsc_Sys_Dim_Lvl_Cols ';
1294 ELSE
1295 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Delete_Bsc_Sys_Dim_Lvl_Cols ';
1296 END IF;
1297 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1298 RAISE;
1299 WHEN OTHERS THEN
1300 ROLLBACK TO DeleteBSCSysDimLevColsPVT;
1301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1302 IF (x_msg_data IS NOT NULL) THEN
1303 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Delete_Bsc_Sys_Dim_Lvl_Cols ';
1304 ELSE
1305 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Delete_Bsc_Sys_Dim_Lvl_Cols ';
1306 END IF;
1307 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1308 RAISE;
1309 end Delete_Bsc_Sys_Dim_Lvl_Cols;
1310
1311 /************************************************************************************
1312 06-JUN-03 = ADRAO Added sequences to generate IDs for Granular Locking
1313 ************************************************************************************/
1314
1315 --: This function returns the next ID for a given column and a given table.
1316 --: This is used much like a sequence.
1317
1318 function Get_Next_Value(
1319 p_table_name IN varchar2
1320 ,p_column_name IN varchar2
1321 )return number is
1322
1323 TYPE Recdc_value IS REF CURSOR;
1324 dc_value Recdc_value;
1325
1326 l_next number;
1327
1328 l_sql varchar2(500);
1329
1330 l_return_status varchar2(100);
1331 l_msg_data varchar2(10);
1332 l_msg_count number;
1333
1334 begin
1335
1336 -- Check if it is a Dimension Level, Group, Dataset or a Measure.
1337
1338 IF (UPPER(p_table_name) = 'BSC_SYS_DIM_LEVELS_B') THEN
1339 SELECT BSC_SYS_DIM_LEVEL_ID_S.NEXTVAL
1340 INTO l_next
1341 FROM DUAL;
1342 ELSIF (UPPER(p_table_name) = 'BSC_SYS_DIM_GROUPS_TL') THEN
1343 SELECT BSC_SYS_DIM_GROUP_ID_S.NEXTVAL
1344 INTO l_next
1345 FROM DUAL;
1346 ELSIF (UPPER(p_table_name) = 'BSC_SYS_MEASURES') THEN
1347 SELECT BSC_SYS_MEASURE_ID_S.NEXTVAL
1348 INTO l_next
1349 FROM DUAL;
1350 ELSIF (UPPER(p_table_name) = 'BSC_SYS_DATASETS_TL') THEN
1351 SELECT BSC_SYS_DATASET_ID_S.NEXTVAL
1352 INTO l_next
1353 FROM DUAL;
1354 ELSIF (UPPER(p_table_name) = 'BSC_KPIS_B') THEN
1355 SELECT BSC_INDICATOR_ID_S.NEXTVAL
1356 INTO l_next
1357 FROM DUAL;
1358 ELSE
1359 l_sql := 'select NVL(max(' || p_column_name || '), 0) + 1 ' ||
1360 ' from ' || p_table_name;
1361
1362 OPEN dc_value FOR l_sql;
1363 FETCH dc_value INTO l_next;
1364 CLOSE dc_value;
1365 END IF;
1366
1367 RETURN l_next;
1368
1369 EXCEPTION
1370 WHEN FND_API.G_EXC_ERROR THEN
1371 l_return_status := FND_API.G_RET_STS_ERROR;
1372 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1373 ,p_data => l_msg_data);
1374 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1375 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1376 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1377 ,p_data => l_msg_data);
1378 raise;
1379 WHEN NO_DATA_FOUND THEN
1380 l_return_status := FND_API.G_RET_STS_ERROR;
1381 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1382 ,p_data => l_msg_data);
1383 raise;
1384 WHEN OTHERS THEN
1385 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1386 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1387 ,p_data => l_msg_data);
1388 raise;
1389
1390 end Get_Next_Value;
1391
1392 /************************************************************************************
1393 ************************************************************************************/
1394
1395 --: This function is used to get an objects ID (dimension, dimensin group, etc.)
1396 --: based on a table, name for the name or description column, name of the object,
1397 --: and name for the column id.
1398
1399 function Get_Id(
1400 p_table_name IN varchar2
1401 ,p_column_name IN varchar2
1402 ,p_column_value IN varchar2
1403 ,p_column_ID_name IN varchar2
1404 ) return number is
1405
1406 TYPE Recdc_value IS REF CURSOR;
1407 dc_value Recdc_value;
1408
1409 l_ID number;
1410
1411 l_sql varchar2(500);
1412
1413 l_return_status varchar2(100);
1414 l_msg_data varchar2(10);
1415 l_msg_count number;
1416
1417 begin
1418
1419 l_sql := 'select distinct ' || p_column_ID_name ||
1420 ' from ' || p_table_name ||
1421 ' where upper(' || p_column_name || ') = upper(:1)';
1422
1423 open dc_value for l_sql using p_column_value;
1424 fetch dc_value into l_ID;
1425 close dc_value;
1426
1427 return l_ID;
1428
1429 EXCEPTION
1430 WHEN FND_API.G_EXC_ERROR THEN
1431 l_return_status := FND_API.G_RET_STS_ERROR;
1432 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1433 ,p_data => l_msg_data);
1434 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1435 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1436 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1437 ,p_data => l_msg_data);
1438 raise;
1439 WHEN NO_DATA_FOUND THEN
1440 l_return_status := FND_API.G_RET_STS_ERROR;
1441 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1442 ,p_data => l_msg_data);
1443 raise;
1444 WHEN OTHERS THEN
1445 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1446 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1447 ,p_data => l_msg_data);
1448 raise;
1449
1450 end Get_Id;
1451
1452 /************************************************************************************
1453 ************************************************************************************/
1454
1455 --: This function gets the count of rows for the dimension with a given name.
1456 --: It is used like a validation method.
1457
1458 function Validate_Dim_Level(
1459 p_level_name IN varchar2
1460 ) return varchar2 is
1461
1462 l_count number;
1463
1464
1465 l_return_status varchar2(100);
1466 l_msg_data varchar2(10);
1467 l_msg_count number;
1468
1469 begin
1470 SELECT COUNT(*) INTO l_Count
1471 FROM BSC_SYS_DIM_LEVELS_B
1472 WHERE Level_Table_Name = p_level_name;
1473
1474 RETURN l_Count;
1475 EXCEPTION
1476 WHEN FND_API.G_EXC_ERROR THEN
1477 l_return_status := FND_API.G_RET_STS_ERROR;
1478 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1479 ,p_data => l_msg_data);
1480 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1481 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1482 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1483 ,p_data => l_msg_data);
1484 raise;
1485 WHEN NO_DATA_FOUND THEN
1486 l_return_status := FND_API.G_RET_STS_ERROR;
1487 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1488 ,p_data => l_msg_data);
1489 raise;
1490 WHEN OTHERS THEN
1491 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1492 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1493 ,p_data => l_msg_data);
1494 raise;
1495
1496 end Validate_Dim_Level;
1497
1498 /************************************************************************************
1499 ************************************************************************************/
1500
1501 --: This function gets the count of rows for dimension groups with a given name.
1502 --: It is used like a validation method.
1503
1504 function Validate_Dim_Group(
1505 p_group_name varchar2
1506 ) return number is
1507
1508 l_count number;
1509
1510 l_return_status varchar2(100);
1511 l_msg_data varchar2(10);
1512 l_msg_count number;
1513
1514 begin
1515 SELECT COUNT(*) INTO l_Count
1516 FROM BSC_SYS_DIM_GROUPS_VL
1517 WHERE UPPER(NAME) = UPPER(p_group_name);
1518
1519 RETURN l_Count;
1520 EXCEPTION
1521 WHEN FND_API.G_EXC_ERROR THEN
1522 l_return_status := FND_API.G_RET_STS_ERROR;
1523 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1524 ,p_data => l_msg_data);
1525 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1526 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1527 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1528 ,p_data => l_msg_data);
1529 raise;
1530 WHEN NO_DATA_FOUND THEN
1531 l_return_status := FND_API.G_RET_STS_ERROR;
1532 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1533 ,p_data => l_msg_data);
1534 raise;
1535 WHEN OTHERS THEN
1536 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1537 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1538 ,p_data => l_msg_data);
1539 raise;
1540
1541 end Validate_Dim_Group;
1542
1543 /************************************************************************************
1544 ************************************************************************************/
1545
1546 function get_dim_levels(
1547 p_meas_short_name varchar2
1548 ,p_dim_short_name varchar2
1549 ) return Dim_Level_Tbl_Type is
1550
1551 l_Dim_Level_Tbl Dim_Level_Tbl_Type;
1552
1553 TYPE Recdc_value IS REF CURSOR;
1554 dc_value Recdc_value;
1555
1556 l_sql varchar2(5000);
1557
1558 l_cnt number;
1559
1560 l_return_status varchar2(100);
1561 l_msg_data varchar2(10);
1562 l_msg_count number;
1563
1564 begin
1565
1566 l_sql := 'select distinct d.short_name, substr(e.name, 1, 45) ' ||
1567 ' from bis_indicators a ' ||
1568 ' ,bis_indicator_dimensions b ' ||
1569 ' ,bis_dimensions_tl c ' ||
1570 ' ,bis_levels d ' ||
1571 ' ,bis_levels_tl e ' ||
1572 ' ,bis_indicators_tl f ' ||
1573 ' ,bis_dimensions h ' ||
1574 ' ,( select distinct dimension1_level_id as tgt_level_id from bis_target_levels b, bis_indicators a ' ||
1575 ' where b.indicator_id = a.indicator_id and upper(a.short_name) = upper(:1)' ||
1576 ' UNION select distinct dimension2_level_id from bis_target_levels b, bis_indicators a ' ||
1577 ' where b.indicator_id = a.indicator_id and upper(a.short_name) = upper(:2)' ||
1578 ' UNION select distinct dimension3_level_id from bis_target_levels b, bis_indicators a ' ||
1579 ' where b.indicator_id = a.indicator_id and upper(a.short_name) = upper(:3)' ||
1580 ' UNION select distinct dimension4_level_id from bis_target_levels b, bis_indicators a ' ||
1581 ' where b.indicator_id = a.indicator_id and upper(a.short_name) = upper(:4)' ||
1582 ' UNION select distinct dimension5_level_id from bis_target_levels b, bis_indicators a ' ||
1583 ' where b.indicator_id = a.indicator_id and upper(a.short_name) = upper(:5)' ||
1584 ' UNION select distinct dimension6_level_id from bis_target_levels b, bis_indicators a ' ||
1585 ' where b.indicator_id = a.indicator_id and upper(a.short_name) = upper(:6)' ||
1586 ' UNION select distinct dimension7_level_id from bis_target_levels b, bis_indicators a ' ||
1587 ' where b.indicator_id = a.indicator_id and upper(a.short_name) = upper(:7) ) g' ||
1588 ' where ' ||
1589 ' upper(a.short_name) like upper(:8) and ' ||
1590 ' a.indicator_id = b.indicator_id and ' ||
1591 ' b.dimension_id = c.dimension_id and ' ||
1592 ' c.dimension_id = d.dimension_id and ' ||
1593 ' d.level_id = e.level_id and ' ||
1594 ' a.indicator_id = f.indicator_id and ' ||
1595 ' d.level_id = g.tgt_level_id and ' ||
1596 ' b.dimension_id = h.dimension_id and ' ||
1597 ' h.short_name = :9';
1598
1599 l_cnt := 0;
1600 open dc_value for l_sql using p_meas_short_name, p_meas_short_name,
1601 p_meas_short_name, p_meas_short_name, p_meas_short_name, p_meas_short_name,
1602 p_meas_short_name, p_meas_short_name, p_dim_short_name;
1603 loop
1604 fetch dc_value into l_Dim_Level_Tbl(l_cnt + 1).Level_Short_Name,
1605 l_Dim_Level_Tbl(l_cnt + 1).Level_Long_Name;
1606 exit when dc_value%NOTFOUND;
1607 l_cnt := l_cnt + 1;
1608 end loop;
1609 close dc_value;
1610
1611 return l_Dim_Level_Tbl;
1612
1613 EXCEPTION
1614 WHEN FND_API.G_EXC_ERROR THEN
1615 l_return_status := FND_API.G_RET_STS_ERROR;
1616 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1617 ,p_data => l_msg_data);
1618 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1619 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1620 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1621 ,p_data => l_msg_data);
1622 raise;
1623 WHEN NO_DATA_FOUND THEN
1624 l_return_status := FND_API.G_RET_STS_ERROR;
1625 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1626 ,p_data => l_msg_data);
1627 raise;
1628 WHEN OTHERS THEN
1629 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1630 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1631 ,p_data => l_msg_data);
1632 raise;
1633
1634 end get_dim_levels;
1635
1636 /************************************************************************************
1637 ************************************************************************************/
1638
1639 function Validate_Value(
1640 p_Table_Name varchar2
1641 ,p_Table_Column_Name varchar2
1642 ,p_Column_Value number
1643 ) return number is
1644
1645 TYPE Recdc_value IS REF CURSOR;
1646 dc_value Recdc_value;
1647
1648 l_count number;
1649
1650 l_sql varchar2(1000);
1651
1652 l_return_status varchar2(100);
1653 l_msg_data varchar2(10);
1654 l_msg_count number;
1655
1656 begin
1657
1658 l_sql := 'select count(distinct ' || p_Table_Column_Name || ')' ||
1659 ' from ' || p_Table_Name ||
1660 ' where ' || p_Table_Column_Name || ' = :1'; --|| p_Column_Value;
1661
1662 open dc_value for l_sql using p_Column_Value; -- bug 3111300
1663 fetch dc_value into l_count;
1664 close dc_value;
1665
1666 return l_count;
1667
1668 EXCEPTION
1669 WHEN FND_API.G_EXC_ERROR THEN
1670 l_return_status := FND_API.G_RET_STS_ERROR;
1671 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1672 ,p_data => l_msg_data);
1673 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1674 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1675 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1676 ,p_data => l_msg_data);
1677 raise;
1678 WHEN NO_DATA_FOUND THEN
1679 l_return_status := FND_API.G_RET_STS_ERROR;
1680 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1681 ,p_data => l_msg_data);
1682 raise;
1683 WHEN OTHERS THEN
1684 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1685 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1686 ,p_data => l_msg_data);
1687 raise;
1688
1689 end Validate_Value;
1690
1691 /************************************************************************************
1692 ************************************************************************************/
1693
1694 function Get_Object_Name(
1695 p_Table_Name varchar2
1696 ,p_Table_Name_Column varchar2
1697 ,p_Table_Id_Column varchar2
1698 ,p_Id_Value number
1699 ) return varchar2 is
1700
1701 TYPE Recdc_value IS REF CURSOR;
1702 dc_value Recdc_value;
1703
1704 l_count number;
1705
1706 l_sql varchar2(1000);
1707 l_name varchar2(255);
1708
1709 l_return_status varchar2(100);
1710 l_msg_data varchar2(10);
1711 l_msg_count number;
1712
1713 begin
1714
1715 l_sql := 'select distinct ' || p_Table_Name_Column || ')' ||
1716 ' from ' || p_Table_Name ||
1717 ' where ' || p_Table_Id_Column || ' = :1' ;
1718
1719 open dc_value for l_sql using p_Id_Value;
1720 fetch dc_value into l_count;
1721 close dc_value;
1722
1723 return l_count;
1724
1725 EXCEPTION
1726 WHEN FND_API.G_EXC_ERROR THEN
1727 l_return_status := FND_API.G_RET_STS_ERROR;
1728 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1729 ,p_data => l_msg_data);
1730 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1731 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1732 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1733 ,p_data => l_msg_data);
1734 raise;
1735 WHEN NO_DATA_FOUND THEN
1736 l_return_status := FND_API.G_RET_STS_ERROR;
1737 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1738 ,p_data => l_msg_data);
1739 raise;
1740 WHEN OTHERS THEN
1741 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1742 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1743 ,p_data => l_msg_data);
1744 raise;
1745
1746 end Get_Object_Name;
1747
1748
1749 /*********************************************************************************
1750
1751 -- Procedures to Handle Relationships between Dimension Levels
1752
1753 **********************************************************************************/
1754
1755 /*---------------------------------------------------------------------------------------
1756 Create_Dim_Level_Relation
1757 ---------------------------------------------------------------------------------------*/
1758 PROCEDURE Create_Dim_Level_Relation(
1759 p_commit IN varchar2 := FND_API.G_FALSE
1760 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
1761 ,x_return_status OUT NOCOPY varchar2
1762 ,x_msg_count OUT NOCOPY number
1763 ,x_msg_data OUT NOCOPY varchar2
1764 ) IS
1765
1766 l_count NUMBER;
1767 l_temp_col VARCHAR2(50);
1768
1769 BEGIN
1770 x_return_status := FND_API.G_RET_STS_SUCCESS;
1771 FND_MSG_PUB.Initialize;
1772 SAVEPOINT CreateBSCDimLevRelsPVT;
1773 g_db_object := 'Create_Dim_Level_Relation';
1774 x_return_status := FND_API.G_RET_STS_SUCCESS;
1775 -- Validate level id exists.
1776
1777 IF ((p_Dim_Level_Rec.Bsc_Level_Id IS NOT NULL) AND (p_Dim_Level_Rec.Bsc_Parent_Level_Id IS NOT NULL)) THEN
1778
1779 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_SYS_DIM_LEVELS_B', 'DIM_LEVEL_ID', p_Dim_Level_Rec.Bsc_Level_Id);
1780 IF (l_count = 0) THEN
1781 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_ID');
1782 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', p_Dim_Level_Rec.Bsc_Level_Id);
1783 FND_MSG_PUB.ADD;
1784 RAISE FND_API.G_EXC_ERROR;
1785 END IF;
1786
1787 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_SYS_DIM_LEVELS_B', 'DIM_LEVEL_ID', p_Dim_Level_Rec.Bsc_Parent_Level_Id);
1788 IF (l_count = 0) THEN
1789 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_ID');
1790 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', p_Dim_Level_Rec.Bsc_Parent_Level_Id);
1791 FND_MSG_PUB.ADD;
1792 RAISE FND_API.G_EXC_ERROR;
1793 END IF;
1794
1795 ---- DBMS_OUTPUT.PUT_LINE('After Validation');
1796
1797 l_temp_col := p_Dim_Level_Rec.Bsc_Relation_Column;
1798
1799 IF (p_Dim_Level_Rec.Bsc_Relation_Column IS NULL) THEN
1800
1801 -- p_Dim_Level_Rec.Bsc_Relation_Column
1802
1803 /*
1804 If this is a PMF Relationship type, then the following validation has to be
1805 carried out to validation the Dat Source type for the current PMF Relationship
1806
1807 SELECT MEANING FROM BSC_LOOKUPS
1808 WHERE LOOKUP_TYPE = 'BSC_COMMON_UI'
1809 AND LOOKUP_CODE IN ('TABLE', 'FUNCTION')
1810
1811 */
1812
1813 l_temp_col :=
1814 get_Relation_Column (
1815 p_Dim_Level_Rec.Bsc_Level_Id
1816 , p_Dim_Level_Rec.Bsc_Parent_Level_Id
1817 , p_Dim_Level_Rec.Bsc_Relation_Type
1818 , x_return_status
1819 , x_msg_count
1820 , x_msg_data
1821 );
1822 END IF;
1823
1824 ---- DBMS_OUTPUT.PUT_LINE('After 1 If');
1825
1826 IF (p_Dim_Level_Rec.Bsc_Relation_Type = 1) THEN
1827
1828 -- DBMS_OUTPUT.PUT_LINE('p_Dim_Level_Rec.Bsc_Level_Id ' || p_Dim_Level_Rec.Bsc_Level_Id);
1829 -- DBMS_OUTPUT.PUT_LINE('p_Dim_Level_Rec.Bsc_Parent_Level_Id ' || p_Dim_Level_Rec.Bsc_Parent_Level_Id);
1830 -- DBMS_OUTPUT.PUT_LINE('p_Dim_Level_Rec.Bsc_Relation_Type ' || p_Dim_Level_Rec.Bsc_Relation_Type);
1831 -- DBMS_OUTPUT.PUT_LINE('l_temp_col ' || l_temp_col);
1832
1833 INSERT INTO BSC_SYS_DIM_LEVEL_RELS (DIM_LEVEL_ID,
1834 RELATION_COL,
1835 PARENT_DIM_LEVEL_ID,
1836 RELATION_TYPE,
1837 DIRECT_RELATION,
1838 DATA_SOURCE_TYPE,
1839 DATA_SOURCE)
1840 VALUES (p_Dim_Level_Rec.Bsc_Level_Id,
1841 l_temp_col,
1842 p_Dim_Level_Rec.Bsc_Parent_Level_Id,
1843 p_Dim_Level_Rec.Bsc_Relation_Type,
1844 null,
1845 p_Dim_Level_Rec.Bsc_Data_Source_Type,
1846 p_Dim_Level_Rec.Bsc_Data_Source);
1847
1848 ELSIF (p_Dim_Level_Rec.Bsc_Relation_Type = 2) THEN
1849
1850
1851 --DBMS_OUTPUT.PUT_LINE('Insert 2');
1852 /*
1853 For MxN relationship types, we need to insert two columns,
1854
1855 With the Parent and the Child interchanged.
1856
1857 */
1858
1859 INSERT INTO BSC_SYS_DIM_LEVEL_RELS (DIM_LEVEL_ID,
1860 RELATION_COL,
1861 PARENT_DIM_LEVEL_ID,
1862 RELATION_TYPE,
1863 DIRECT_RELATION,
1864 DATA_SOURCE_TYPE,
1865 DATA_SOURCE)
1866 VALUES (p_Dim_Level_Rec.Bsc_Level_Id,
1867 l_temp_col,
1868 p_Dim_Level_Rec.Bsc_Parent_Level_Id,
1869 p_Dim_Level_Rec.Bsc_Relation_Type,
1870 null,
1871 null,
1872 null); -- There will be no Data Source/Data Type for BSC Dim Object
1873
1874
1875 /* Allow the MxN Relationship only for BSC Type of Dimension Objects */
1876
1877 IF (p_Dim_Level_Rec.Bsc_Source = 'BSC') THEN
1878 INSERT INTO BSC_SYS_DIM_LEVEL_RELS (DIM_LEVEL_ID,
1879 RELATION_COL,
1880 PARENT_DIM_LEVEL_ID,
1881 RELATION_TYPE,
1882 DIRECT_RELATION,
1883 DATA_SOURCE_TYPE,
1884 DATA_SOURCE)
1885 VALUES (p_Dim_Level_Rec.Bsc_Parent_Level_Id,
1886 l_temp_col,
1887 p_Dim_Level_Rec.Bsc_Level_Id,
1888 p_Dim_Level_Rec.Bsc_Relation_Type,
1889 null,
1890 null,
1891 null); -- There will be no Data Source/Data Type for BSC Dim Object
1892
1893 END IF;
1894
1895 END IF;
1896
1897 ELSE
1898 FND_MESSAGE.SET_NAME('BSC','BSC_NO_LEVEL_ID_ENTERED');
1899 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', P_DIM_LEVEL_REC.BSC_LEVEL_ID);
1900 FND_MSG_PUB.ADD;
1901 RAISE FND_API.G_EXC_ERROR;
1902 END IF;
1903
1904 ---- DBMS_OUTPUT.PUT_LINE('END ....');
1905
1906 IF (p_commit = FND_API.G_TRUE) THEN
1907 COMMIT;
1908 END IF;
1909
1910 EXCEPTION
1911 WHEN FND_API.G_EXC_ERROR THEN
1912 ROLLBACK TO CreateBSCDimLevRelsPVT;
1913 FND_MSG_PUB.Count_And_Get
1914 ( p_encoded => FND_API.G_FALSE
1915 , p_count => x_msg_count
1916 , p_data => x_msg_data
1917 );
1918 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1919 x_return_status := FND_API.G_RET_STS_ERROR;
1920 RAISE;
1921 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1922 ROLLBACK TO CreateBSCDimLevRelsPVT;
1923 FND_MSG_PUB.Count_And_Get
1924 ( p_encoded => FND_API.G_FALSE
1925 , p_count => x_msg_count
1926 , p_data => x_msg_data
1927 );
1928 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1929 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1930 RAISE;
1931 WHEN NO_DATA_FOUND THEN
1932 ROLLBACK TO CreateBSCDimLevRelsPVT;
1933 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1934 IF (x_msg_data IS NOT NULL) THEN
1935 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Create_Dim_Level_Relation ';
1936 ELSE
1937 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Create_Dim_Level_Relation ';
1938 END IF;
1939 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1940 RAISE;
1941 WHEN OTHERS THEN
1942 ROLLBACK TO CreateBSCDimLevRelsPVT;
1943 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1944 IF (x_msg_data IS NOT NULL) THEN
1945 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Create_Dim_Level_Relation ';
1946 ELSE
1947 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Create_Dim_Level_Relation ';
1948 END IF;
1949 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1950 RAISE;
1951 END Create_Dim_Level_Relation;
1952
1953 /*---------------------------------------------------------------------------------------
1954 Delete_Dim_Level_Relation:
1955 Parameters: p_Dim_Level_Rec.Bsc_Level_Id
1956 p_Dim_Level_Rec.Bsc_Parent_Level_Id
1957 ---------------------------------------------------------------------------------------*/
1958 PROCEDURE Delete_Dim_Level_Relation(
1959 p_commit IN varchar2 := FND_API.G_FALSE
1960 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
1961 ,x_return_status OUT NOCOPY varchar2
1962 ,x_msg_count OUT NOCOPY number
1963 ,x_msg_data OUT NOCOPY varchar2
1964 ) IS
1965
1966 BEGIN
1967 x_return_status := FND_API.G_RET_STS_SUCCESS;
1968 SAVEPOINT DeleteBSCDimLevRelsPVT;
1969
1970 DELETE FROM BSC_SYS_DIM_LEVEL_RELS
1971 WHERE DIM_LEVEL_ID = p_Dim_Level_Rec.Bsc_Level_Id
1972 AND PARENT_DIM_LEVEL_ID = p_Dim_Level_Rec.Bsc_Parent_Level_Id;
1973
1974 EXCEPTION
1975 WHEN FND_API.G_EXC_ERROR THEN
1976 ROLLBACK TO DeleteBSCDimLevRelsPVT;
1977 FND_MSG_PUB.Count_And_Get
1978 ( p_encoded => FND_API.G_FALSE
1979 , p_count => x_msg_count
1980 , p_data => x_msg_data
1981 );
1982 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1983 x_return_status := FND_API.G_RET_STS_ERROR;
1984 RAISE;
1985 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1986 ROLLBACK TO DeleteBSCDimLevRelsPVT;
1987 FND_MSG_PUB.Count_And_Get
1988 ( p_encoded => FND_API.G_FALSE
1989 , p_count => x_msg_count
1990 , p_data => x_msg_data
1991 );
1992 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1993 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1994 RAISE;
1995 WHEN NO_DATA_FOUND THEN
1996 ROLLBACK TO DeleteBSCDimLevRelsPVT;
1997 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1998 IF (x_msg_data IS NOT NULL) THEN
1999 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level_Relation ';
2000 ELSE
2001 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level_Relation ';
2002 END IF;
2003 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2004 RAISE;
2005 WHEN OTHERS THEN
2006 ROLLBACK TO DeleteBSCDimLevRelsPVT;
2007 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2008 IF (x_msg_data IS NOT NULL) THEN
2009 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level_Relation ';
2010 ELSE
2011 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level_Relation ';
2012 END IF;
2013 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2014 RAISE;
2015 END Delete_Dim_Level_Relation;
2016
2017 /*-------------------------------------------------------------------------------------
2018 Evaluate_Circular_Relationship
2019
2020 This function Return False if the Relationship is not valid
2021 and Return in x_Parents the Relationship that already exist
2022 ---------------------------------------------------------------------------------------*/
2023 FUNCTION Evaluate_Circular_Relationship
2024 (
2025 p_Child_level_Id IN NUMBER
2026 , p_Parent_Dim_Level_Id IN NUMBER
2027 , p_Relation_Type IN NUMBER := 1
2028 , p_Output_Flag IN BOOLEAN := TRUE
2029 , x_Parents OUT NOCOPY VARCHAR2
2030 , x_return_status OUT NOCOPY VARCHAR2
2031 , x_msg_count OUT NOCOPY NUMBER
2032 , x_msg_data OUT NOCOPY VARCHAR2
2033 ) RETURN BOOLEAN IS
2034
2035 CURSOR c_grand_parents IS
2036 SELECT PARENT_DIM_LEVEL_ID
2037 FROM BSC_SYS_DIM_LEVEL_RELS
2038 WHERE DIM_LEVEL_ID = p_Parent_Dim_Level_Id
2039 AND RELATION_TYPE = 1;
2040
2041 v_parent NUMBER;
2042 v_temp BOOLEAN := TRUE;
2043 v_parents VARCHAR2(3000) := '';
2044 l_count NUMBER;
2045 l_num NUMBER := 0;
2046 BEGIN
2047 x_return_status := FND_API.G_RET_STS_SUCCESS;
2048
2049 IF (p_Relation_Type <> 1) THEN
2050 RETURN TRUE;
2051 END IF;
2052 IF p_Child_level_Id <> p_Parent_Dim_Level_Id THEN
2053
2054 FOR cd IN c_grand_parents LOOP
2055 v_parent := cd.Parent_Dim_Level_Id;
2056
2057 IF v_parent = p_Child_level_Id THEN
2058 v_temp := FALSE;
2059 ELSIF v_parent = p_Parent_Dim_Level_Id THEN
2060
2061 SELECT COUNT(dim_level_id)
2062 INTO l_count
2063 FROM BSC_SYS_DIM_LEVELS_B
2064 WHERE dim_level_id = p_Parent_Dim_Level_Id
2065 AND source = 'PMF';
2066
2067 IF (l_count <> 0) THEN
2068 v_temp := TRUE;
2069 ELSE
2070 v_temp := FALSE;
2071 x_return_status := 'SAME';
2072 END IF;
2073 ELSE
2074 l_num := l_num + 1;
2075 v_temp := Evaluate_Circular_Relationship
2076 (
2077 p_Child_level_Id => p_Child_level_Id
2078 , p_Parent_Dim_Level_Id => v_parent
2079 , p_Relation_Type => p_Relation_Type
2080 , p_Output_Flag => FALSE
2081 , x_Parents => v_parents
2082 , x_return_status => x_return_status
2083 , x_msg_count => x_msg_count
2084 , x_msg_data => x_msg_data
2085 );
2086 x_Parents := v_parents;
2087 END IF;
2088 IF v_temp = FALSE THEN
2089 x_Parents := ' -> ' || get_Dim_Level_Name(v_parent) || x_Parents ;
2090 IF p_Output_Flag THEN
2091 x_Parents := get_Dim_Level_Name(p_Parent_Dim_Level_Id) || x_Parents ;
2092 END IF;
2093 EXIT;
2094 END IF;
2095 END LOOP;
2096 ELSE
2097 SELECT COUNT(dim_level_id)
2098 INTO l_count
2099 FROM BSC_SYS_DIM_LEVELS_B
2100 WHERE dim_level_id = p_Parent_Dim_Level_Id
2101 AND source = 'PMF';
2102
2103 IF (l_count <> 0) THEN
2104 v_temp := TRUE;
2105 ELSE
2106 v_temp := FALSE;
2107 x_return_status := 'SAME';
2108 END IF;
2109 END IF;
2110 RETURN v_temp;
2111 EXCEPTION
2112 WHEN FND_API.G_EXC_ERROR THEN
2113 FND_MSG_PUB.Count_And_Get
2114 ( p_encoded => FND_API.G_FALSE
2115 , p_count => x_msg_count
2116 , p_data => x_msg_data
2117 );
2118 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2119 x_return_status := FND_API.G_RET_STS_ERROR;
2120 RAISE;
2121 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2122 FND_MSG_PUB.Count_And_Get
2123 ( p_encoded => FND_API.G_FALSE
2124 , p_count => x_msg_count
2125 , p_data => x_msg_data
2126 );
2127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2128 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2129 RAISE;
2130 WHEN NO_DATA_FOUND THEN
2131 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2132 IF (x_msg_data IS NOT NULL) THEN
2133 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Evaluate_Circular_Relationship ';
2134 ELSE
2135 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Evaluate_Circular_Relationship ';
2136 END IF;
2137 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2138 RAISE;
2139 WHEN OTHERS THEN
2140 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2141 IF (x_msg_data IS NOT NULL) THEN
2142 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Evaluate_Circular_Relationship ';
2143 ELSE
2144 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Evaluate_Circular_Relationship ';
2145 END IF;
2146 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2147 RAISE;
2148 END Evaluate_Circular_Relationship;
2149
2150 /*-------------------------------------------------------------------------------------
2151 Create_BSC_Dim_Level_View
2152 ---------------------------------------------------------------------------------------*/
2153 PROCEDURE Create_BSC_Dim_Level_View (
2154 p_commit IN varchar2 := FND_API.G_FALSE
2155 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
2156 ,x_return_status OUT NOCOPY varchar2
2157 ,x_msg_count OUT NOCOPY number
2158 ,x_msg_data OUT NOCOPY varchar2
2159 )IS
2160 v_Temp Number;
2161 BEGIN
2162 x_return_status := FND_API.G_RET_STS_SUCCESS;
2163
2164 --DBMS_OUTPUT.PUT_LINE('Begin Create_BSC_Dim_Level_View - PENDING ');
2165
2166 v_Temp:=0;
2167 -- ***** PENDING *****
2168
2169 --DBMS_OUTPUT.PUT_LINE('End Create_BSC_Dim_Level_View ');
2170
2171 x_return_status := FND_API.G_RET_STS_SUCCESS;
2172 END Create_BSC_Dim_Level_View;
2173
2174 /*-------------------------------------------------------------------------------------
2175 get_Dim_Level_Name
2176 ---------------------------------------------------------------------------------------*/
2177 FUNCTION get_Dim_Level_Name(
2178 p_Child_level_Id IN NUMBER
2179 ) RETURN VARCHAR2 IS
2180
2181 v_Dim_Level_Name VARCHAR2(1000) := '' ;
2182
2183 BEGIN
2184
2185 SELECT NAME INTO v_Dim_Level_Name FROM BSC_SYS_DIM_LEVELS_VL
2186 WHERE DIM_LEVEL_ID = p_Child_level_Id;
2187
2188 RETURN v_Dim_Level_Name;
2189
2190 EXCEPTION
2191 WHEN OTHERS THEN
2192 RETURN '' || p_Child_level_Id ;
2193
2194 END get_Dim_Level_Name;
2195
2196 --
2197 /*-------------------------------------------------------------------------------------
2198 get_Dim_Level_Id:
2199 Return the Dimension Level ID, null is the Short Name Not exist
2200 ---------------------------------------------------------------------------------------*/
2201 FUNCTION get_Dim_Level_Id(
2202 p_Short_Name IN VARCHAR2
2203 ) RETURN number IS
2204 v_Id number;
2205
2206 BEGIN
2207
2208 Select DIM_LEVEL_ID
2209 into v_Id
2210 from BSC_SYS_DIM_LEVELS_B
2211 where SHORT_NAME = p_Short_Name;
2212
2213 RETURN v_Id;
2214
2215 EXCEPTION
2216 WHEN OTHERS THEN
2217 RETURN null;
2218 END get_Dim_Level_Id;
2219
2220 /*********************************************************************************
2221 **********************************************************************************/
2222
2223
2224 procedure Retrieve_Relationship
2225 (
2226 p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
2227 , x_Dim_Level_Rec OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
2228 , x_return_status OUT NOCOPY VARCHAR2
2229 , x_msg_count OUT NOCOPY NUMBER
2230 , x_msg_data OUT NOCOPY VARCHAR2
2231 ) IS
2232 l_count NUMBER;
2233
2234 CURSOR cr_getId_rels IS
2235 SELECT LR.DIM_LEVEL_ID
2236 , LR.PARENT_DIM_LEVEL_ID
2237 , DL.SHORT_NAME
2238 , PL.SHORT_NAME
2239 , DL.SOURCE
2240 , PL.NAME
2241 , PL.SOURCE
2242 , LR.RELATION_COL
2243 , LR.DATA_SOURCE_TYPE
2244 , LR.DATA_SOURCE
2245 , LR.RELATION_TYPE
2246 FROM BSC_SYS_DIM_LEVEL_RELS LR, BSC_SYS_DIM_LEVELS_VL DL, BSC_SYS_DIM_LEVELS_VL PL
2247 WHERE DL.dim_level_id = LR.dim_level_id
2248 AND PL.dim_level_id = LR.parent_dim_level_id
2249 AND LR.DIM_LEVEL_ID = p_Dim_Level_Rec.Bsc_Level_Id
2250 AND LR.PARENT_DIM_LEVEL_ID = p_Dim_Level_Rec.Bsc_Parent_Level_Id;
2251
2252 CURSOR cr_getName_rels IS
2253 SELECT LR.DIM_LEVEL_ID
2254 , LR.PARENT_DIM_LEVEL_ID
2255 , DL.SHORT_NAME
2256 , PL.SHORT_NAME
2257 , DL.SOURCE
2258 , PL.NAME
2259 , PL.SOURCE
2260 , LR.RELATION_COL
2261 , LR.DATA_SOURCE_TYPE
2262 , LR.DATA_SOURCE
2263 , LR.RELATION_TYPE
2264 FROM BSC_SYS_DIM_LEVEL_RELS LR, BSC_SYS_DIM_LEVELS_VL DL, BSC_SYS_DIM_LEVELS_VL PL
2265 WHERE DL.dim_level_id = LR.dim_level_id
2266 AND PL.dim_level_id = LR.parent_dim_level_id
2267 AND DL.SHORT_NAME = p_Dim_Level_Rec.Bsc_Level_Short_Name
2268 AND PL.SHORT_NAME = p_Dim_Level_Rec.Bsc_Parent_Level_Short_Name;
2269 BEGIN
2270 x_return_status := FND_API.G_RET_STS_SUCCESS;
2271 FND_MSG_PUB.Initialize;
2272 g_db_object := 'Retrieve_Relationship';
2273 -- Validate level id exists.
2274 IF ((p_Dim_Level_Rec.Bsc_Level_Id IS NOT NULL) AND (p_Dim_Level_Rec.Bsc_Parent_Level_Id IS NOT NULL)) THEN
2275 l_count := BSC_DIMENSION_LEVELS_PVT.validate_dim_level_id(p_Dim_Level_Rec.Bsc_Level_Id);
2276 IF (l_count = 0) THEN
2277 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_ID');
2278 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', p_Dim_Level_Rec.Bsc_Level_Id);
2279 FND_MSG_PUB.ADD;
2280 RAISE FND_API.G_EXC_ERROR;
2281 END IF;
2282 l_count := BSC_DIMENSION_LEVELS_PVT.validate_dim_level_id(p_Dim_Level_Rec.Bsc_Parent_Level_Id);
2283 IF (l_count = 0) THEN
2284 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_ID');
2285 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', p_Dim_Level_Rec.Bsc_Parent_Level_Id);
2286 FND_MSG_PUB.ADD;
2287 RAISE FND_API.G_EXC_ERROR;
2288 END IF;
2289 IF (cr_getId_rels%ISOPEN) THEN
2290 CLOSE cr_getId_rels;
2291 END IF;
2292 OPEN cr_getId_rels;
2293 FETCH cr_getId_rels
2294 INTO x_Dim_Level_Rec.Bsc_Level_Id
2295 , x_Dim_Level_Rec.Bsc_Parent_Level_Id
2296 , x_Dim_Level_Rec.Bsc_Level_Short_Name
2297 , x_Dim_Level_Rec.Bsc_Parent_Level_Short_Name
2298 , x_Dim_Level_Rec.Bsc_Source
2299 , x_Dim_Level_Rec.Bsc_Parent_Level_Name
2300 , x_Dim_Level_Rec.Bsc_Parent_Level_Source
2301 , x_Dim_Level_Rec.Bsc_Relation_Column
2302 , x_Dim_Level_Rec.Bsc_Data_Source_Type
2303 , x_Dim_Level_Rec.Bsc_Data_Source
2304 , x_Dim_Level_Rec.Bsc_Relation_Type;
2305 CLOSE cr_getId_rels;
2306 ELSIF ((p_Dim_Level_Rec.Bsc_Level_Short_Name IS NOT NULL) AND (p_Dim_Level_Rec.Bsc_Parent_Level_Short_Name IS NOT NULL)) THEN
2307 SELECT COUNT(dim_level_id) INTO l_count
2308 FROM BSC_SYS_DIM_LEVELS_B
2309 WHERE short_name = p_Dim_Level_Rec.Bsc_Level_Short_Name;
2310
2311 IF (l_count = 0) THEN
2312 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_NAME');
2313 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_NAME', p_Dim_Level_Rec.Bsc_Level_Short_Name);
2314 FND_MSG_PUB.ADD;
2315 RAISE FND_API.G_EXC_ERROR;
2316 END IF;
2317
2318 -- bug# 3814375: Most often short name and parent are the same - hence, need not fire this SQL if the above is validated.
2319 IF (p_Dim_Level_Rec.Bsc_Parent_Level_Short_Name <> p_Dim_Level_Rec.Bsc_Level_Short_Name) THEN
2320 SELECT COUNT(dim_level_id) INTO l_count
2321 FROM BSC_SYS_DIM_LEVELS_B
2322 WHERE short_name = p_Dim_Level_Rec.Bsc_Parent_Level_Short_Name;
2323
2324 IF (l_count = 0) THEN
2325 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_NAME');
2326 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_NAME', p_Dim_Level_Rec.Bsc_Parent_Level_Short_Name);
2327 FND_MSG_PUB.ADD;
2328 RAISE FND_API.G_EXC_ERROR;
2329 END IF;
2330 END IF;
2331
2332 IF (cr_getName_rels%ISOPEN) THEN
2333 CLOSE cr_getName_rels;
2334 END IF;
2335 OPEN cr_getName_rels;
2336 FETCH cr_getName_rels
2337 INTO x_Dim_Level_Rec.Bsc_Level_Id
2338 , x_Dim_Level_Rec.Bsc_Parent_Level_Id
2339 , x_Dim_Level_Rec.Bsc_Level_Short_Name
2340 , x_Dim_Level_Rec.Bsc_Parent_Level_Short_Name
2341 , x_Dim_Level_Rec.Bsc_Source
2342 , x_Dim_Level_Rec.Bsc_Parent_Level_Name
2343 , x_Dim_Level_Rec.Bsc_Parent_Level_Source
2344 , x_Dim_Level_Rec.Bsc_Relation_Column
2345 , x_Dim_Level_Rec.Bsc_Data_Source_Type
2346 , x_Dim_Level_Rec.Bsc_Data_Source
2347 , x_Dim_Level_Rec.Bsc_Relation_Type;
2348 CLOSE cr_getName_rels;
2349 ELSE
2350 FND_MESSAGE.SET_NAME('BSC','BSC_NO_LEVEL_ID_ENTERED');
2351 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', P_DIM_LEVEL_REC.BSC_LEVEL_ID);
2352 FND_MSG_PUB.ADD;
2353 RAISE FND_API.G_EXC_ERROR;
2354 END IF;
2355 EXCEPTION
2356 WHEN FND_API.G_EXC_ERROR THEN
2357 IF (cr_getId_rels%ISOPEN) THEN
2358 CLOSE cr_getId_rels;
2359 END IF;
2360 FND_MSG_PUB.Count_And_Get
2361 ( p_encoded => FND_API.G_FALSE
2362 , p_count => x_msg_count
2363 , p_data => x_msg_data
2364 );
2365 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2366 x_return_status := FND_API.G_RET_STS_ERROR;
2367 RAISE;
2368 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2369 IF (cr_getId_rels%ISOPEN) THEN
2370 CLOSE cr_getId_rels;
2371 END IF;
2372 FND_MSG_PUB.Count_And_Get
2373 ( p_encoded => FND_API.G_FALSE
2374 , p_count => x_msg_count
2375 , p_data => x_msg_data
2376 );
2377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2378 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2379 RAISE;
2380 WHEN NO_DATA_FOUND THEN
2381 IF (cr_getId_rels%ISOPEN) THEN
2382 CLOSE cr_getId_rels;
2383 END IF;
2384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2385 IF (x_msg_data IS NOT NULL) THEN
2386 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Retrieve_Relationship ';
2387 ELSE
2388 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Retrieve_Relationship ';
2389 END IF;
2390 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2391 RAISE;
2392 WHEN OTHERS THEN
2393 IF (cr_getId_rels%ISOPEN) THEN
2394 CLOSE cr_getId_rels;
2395 END IF;
2396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2397 IF (x_msg_data IS NOT NULL) THEN
2398 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Retrieve_Relationship ';
2399 ELSE
2400 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Retrieve_Relationship ';
2401 END IF;
2402 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2403 RAISE;
2404 END Retrieve_Relationship;
2405 /*===========================================================================+
2406 | PROCEDURE
2407 | get_Relation_Column
2408 |
2409 | PURPOSE
2410 | Helps in generating the RELATION_COL for the BSC_SYS_DIM_LEVLES_B
2411 | when the relationship is either 1xN => 1 or MxN => 2
2412 | PSEUDO CODE
2413 | - Check for Valid DIM_LEVEL_IDs
2414 | - If p_Relation_Type is 1, then, just return the LEVEL_PK_COL of the
2415 | Dimension Object
2416 | - If p_Relation_Type is 2, then, create a table name of the type
2417 | BSC_D_Abbr(Child)_Abbr(Parent)
2418 | - Return the value to back to function
2419 | - Caller should check the x_return_status.
2420 | PARAMETERS
2421 | p_Child_level_Id IN NUMBER
2422 | p_Parent_level_Id IN NUMBER
2423 | p_Relation_Type IN NUMBER
2424 | x_return_status OUT NOCOPY VARCHAR2
2425 | x_msg_count OUT NOCOPY NUMBER
2426 | x_msg_data OUT NOCOPY VARCHAR2
2427 | HISTORY
2428 | 24-MAY-2003 Aditya Rao Created for PMD
2429 +---------------------------------------------------------------------------*/
2430
2431 FUNCTION get_Relation_Column(
2432 p_Child_level_Id IN NUMBER
2433 , p_Parent_level_Id IN NUMBER
2434 , p_Relation_Type IN NUMBER
2435 , x_return_status OUT NOCOPY VARCHAR2
2436 , x_msg_count OUT NOCOPY NUMBER
2437 , x_msg_data OUT NOCOPY VARCHAR2
2438 )
2439 RETURN VARCHAR2 IS
2440
2441 l_count NUMBER;
2442 x_rel_col VARCHAR2(50);
2443 str_c_abbr VARCHAR2(30);
2444 str_p_abbr VARCHAR2(30);
2445
2446 CURSOR c_abbr IS
2447 SELECT ABBREVIATION
2448 FROM BSC_SYS_DIM_LEVELS_B
2449 WHERE DIM_LEVEL_ID = p_Child_level_Id;
2450
2451 CURSOR p_abbr IS
2452 SELECT ABBREVIATION
2453 FROM BSC_SYS_DIM_LEVELS_B
2454 WHERE DIM_LEVEL_ID = p_Parent_level_Id;
2455
2456 CURSOR r_col IS
2457 SELECT LEVEL_PK_COL
2458 FROM BSC_SYS_DIM_LEVELS_B
2459 WHERE DIM_LEVEL_ID = p_Parent_level_Id;
2460
2461 BEGIN
2462 x_return_status := FND_API.G_RET_STS_SUCCESS;
2463 g_db_object := 'get_Relation_Column';
2464 FND_MSG_PUB.Initialize;
2465
2466 IF ((p_Child_level_Id IS NOT NULL) AND (p_Parent_level_Id IS NOT NULL)) THEN
2467
2468 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_SYS_DIM_LEVELS_B', 'DIM_LEVEL_ID', p_Child_level_Id);
2469 IF (l_count = 0) THEN
2470 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_ID');
2471 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', p_Child_level_Id);
2472 FND_MSG_PUB.ADD;
2473 RAISE FND_API.G_EXC_ERROR;
2474 END IF;
2475
2476 l_count := BSC_DIMENSION_LEVELS_PVT.Validate_Value( 'BSC_SYS_DIM_LEVELS_B', 'DIM_LEVEL_ID', p_Parent_level_Id);
2477 IF (l_count = 0) THEN
2478 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_LEVEL_ID');
2479 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', p_Parent_level_Id);
2480 FND_MSG_PUB.ADD;
2481 RAISE FND_API.G_EXC_ERROR;
2482 END IF;
2483
2484
2485 IF (p_Relation_Type = 1) THEN
2486
2487 IF (r_col%ISOPEN) THEN
2488 CLOSE r_col;
2489 END IF;
2490
2491 -- If the Relationship is 1xN, then return the parent
2492 -- PK_LEVEL_COL....
2493
2494 ---- DBMS_OUTPUT.PUT_LINE(' Got to the cursor' );
2495
2496 OPEN r_col;
2497 FETCH r_col
2498 INTO x_rel_col;
2499 CLOSE r_col;
2500 ---- DBMS_OUTPUT.PUT_LINE(' After the cursor' );
2501
2502 ELSIF (p_Relation_Type = 2) THEN
2503 IF (c_abbr%ISOPEN) THEN
2504 CLOSE c_abbr;
2505 END IF;
2506
2507 OPEN c_abbr;
2508 FETCH c_abbr
2509 INTO str_c_abbr;
2510 CLOSE c_abbr;
2511
2512 -- Open the Cursors
2513 IF (p_abbr%ISOPEN) THEN
2514 CLOSE p_abbr;
2515 END IF;
2516
2517 OPEN p_abbr;
2518 FETCH p_abbr
2519 INTO str_p_abbr;
2520 CLOSE p_abbr;
2521
2522 x_rel_col := 'BSC_D_';
2523
2524 -- Return the RELATION_COL as a table name of the format
2525 -- BSC_D_X_Y, where X and Y are arranged alphabetically.
2526
2527 IF str_c_abbr > str_p_abbr THEN
2528 x_rel_col := x_rel_col || str_p_abbr || '_' || str_c_abbr;
2529 ELSE
2530 x_rel_col := x_rel_col || str_c_abbr || '_' || str_p_abbr;
2531 END IF;
2532 END IF;
2533
2534 ELSE
2535 FND_MESSAGE.SET_NAME('BSC','BSC_NO_LEVEL_ID_ENTERED');
2536 FND_MESSAGE.SET_TOKEN('BSC_LEVEL_ID', p_Child_level_Id);
2537 FND_MSG_PUB.ADD;
2538 RAISE FND_API.G_EXC_ERROR;
2539 END IF;
2540
2541 RETURN x_rel_col;
2542
2543 EXCEPTION
2544 WHEN FND_API.G_EXC_ERROR THEN
2545 IF (c_abbr%ISOPEN) THEN
2546 CLOSE c_abbr;
2547 END IF;
2548 IF (p_abbr%ISOPEN) THEN
2549 CLOSE p_abbr;
2550 END IF;
2551 IF (r_col%ISOPEN) THEN
2552 CLOSE r_col;
2553 END IF;
2554 FND_MSG_PUB.Count_And_Get
2555 ( p_encoded => FND_API.G_FALSE
2556 , p_count => x_msg_count
2557 , p_data => x_msg_data
2558 );
2559 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2560 x_return_status := FND_API.G_RET_STS_ERROR;
2561 RAISE;
2562 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2563 IF (c_abbr%ISOPEN) THEN
2564 CLOSE c_abbr;
2565 END IF;
2566 IF (p_abbr%ISOPEN) THEN
2567 CLOSE p_abbr;
2568 END IF;
2569 IF (r_col%ISOPEN) THEN
2570 CLOSE r_col;
2571 END IF;
2572 FND_MSG_PUB.Count_And_Get
2573 ( p_encoded => FND_API.G_FALSE
2574 , p_count => x_msg_count
2575 , p_data => x_msg_data
2576 );
2577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2578 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2579 RAISE;
2580 WHEN NO_DATA_FOUND THEN
2581 IF (c_abbr%ISOPEN) THEN
2582 CLOSE c_abbr;
2583 END IF;
2584 IF (p_abbr%ISOPEN) THEN
2585 CLOSE p_abbr;
2586 END IF;
2587 IF (r_col%ISOPEN) THEN
2588 CLOSE r_col;
2589 END IF;
2590 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2591 IF (x_msg_data IS NOT NULL) THEN
2592 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.get_Relation_Column ';
2593 ELSE
2594 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.get_Relation_Column ';
2595 END IF;
2596 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2597 RAISE;
2598 WHEN OTHERS THEN
2599 IF (c_abbr%ISOPEN) THEN
2600 CLOSE c_abbr;
2601 END IF;
2602 IF (p_abbr%ISOPEN) THEN
2603 CLOSE p_abbr;
2604 END IF;
2605 IF (r_col%ISOPEN) THEN
2606 CLOSE r_col;
2607 END IF;
2608 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2609 IF (x_msg_data IS NOT NULL) THEN
2610 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.get_Relation_Column ';
2611 ELSE
2612 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.get_Relation_Column ';
2613 END IF;
2614 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2615 RAISE;
2616 END get_Relation_Column;
2617
2618 --================================================================================================
2619 --
2620 PROCEDURE Retr_Updated_Bsc_Dim_Levels(
2621 p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
2622 ,x_Dim_Level_Rec OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
2623 ,x_return_status OUT NOCOPY VARCHAR2
2624 ,x_msg_count OUT NOCOPY NUMBER
2625 ,x_msg_data OUT NOCOPY VARCHAR2
2626 ) IS
2627
2628 BEGIN
2629
2630 x_return_status:= FND_API.G_RET_STS_SUCCESS;
2631
2632 SELECT
2633 dim_level_id
2634 ,short_name
2635 ,name
2636 ,help
2637 ,total_disp_name
2638 ,comp_disp_name
2639 INTO
2640 x_Dim_Level_Rec.Bsc_Level_Id
2641 ,x_Dim_Level_Rec.Bsc_Level_Short_Name
2642 ,x_Dim_Level_Rec.Bsc_Dim_Level_Long_Name
2643 ,x_Dim_Level_Rec.Bsc_Dim_Level_Help
2644 ,x_Dim_Level_Rec.Bsc_Dim_Tot_Disp_Name
2645 ,x_Dim_Level_Rec.Bsc_Dim_Comp_Disp_Name
2646 FROM BSC_SYS_DIM_LEVELS_VL
2647 WHERE short_name = p_Dim_Level_Rec.Bsc_Level_Short_Name;
2648
2649
2650 IF (p_Dim_Level_Rec.Bsc_Dim_Level_Long_Name IS NOT NULL) THEN
2651 x_Dim_Level_Rec.Bsc_Dim_Level_Long_Name := p_Dim_Level_Rec.Bsc_Dim_Level_Long_Name;
2652 END IF;
2653
2654 IF (p_Dim_Level_Rec.Bsc_Dim_Comp_Disp_Name IS NOT NULL) THEN
2655 x_Dim_Level_Rec.Bsc_Dim_Comp_Disp_Name := p_Dim_Level_Rec.Bsc_Dim_Comp_Disp_Name;
2656 END IF;
2657
2658 IF (p_Dim_Level_Rec.Bsc_Dim_Tot_Disp_Name IS NOT NULL) THEN
2659 x_Dim_Level_Rec.Bsc_Dim_Tot_Disp_Name := p_Dim_Level_Rec.Bsc_Dim_Tot_Disp_Name;
2660 END IF;
2661
2662 IF (p_Dim_Level_Rec.Bsc_Dim_Level_Help IS NOT NULL) THEN
2663 x_Dim_Level_Rec.Bsc_Dim_Level_Help := p_Dim_Level_Rec.Bsc_Dim_Level_Help;
2664 END IF;
2665
2666 EXCEPTION
2667 WHEN FND_API.G_EXC_ERROR THEN
2668 x_return_status := FND_API.G_RET_STS_ERROR;
2669 FND_MSG_PUB.Count_And_Get(
2670 p_encoded => 'F'
2671 ,p_count => x_msg_count
2672 ,p_data => x_msg_data
2673 );
2674 RAISE;
2675 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2676 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2677 FND_MSG_PUB.Count_And_Get(
2678 p_encoded => 'F'
2679 ,p_count => x_msg_count
2680 ,p_data => x_msg_data
2681 );
2682 RAISE;
2683 WHEN NO_DATA_FOUND THEN
2684 x_return_status := FND_API.G_RET_STS_ERROR;
2685 IF (x_msg_data IS NOT NULL) THEN
2686 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Retr_Updated_Bsc_Dim_Levels ';
2687 ELSE
2688 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PVT.Retr_Updated_Bsc_Dim_Levels ';
2689 END IF;
2690 RAISE FND_API.G_EXC_ERROR;
2691 WHEN OTHERS THEN
2692 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2693 IF (x_msg_data IS NOT NULL) THEN
2694 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Retr_Updated_Bsc_Dim_Levels ';
2695 ELSE
2696 x_msg_data := 'BSC_DIMENSION_LEVELS_PVT.Retr_Updated_Bsc_Dim_Levels '||SQLERRM;
2697 END IF;
2698 RAISE;
2699
2700 END Retr_Updated_Bsc_Dim_Levels;
2701 --================================================================================================
2702 --
2703 --================================================================================================
2704 --
2705 PROCEDURE Translate_Dimension_Level (
2706 p_Commit IN VARCHAR2 := FND_API.G_FALSE
2707 ,p_Bsc_Pmf_Dim_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Rec_Type
2708 ,p_Bsc_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
2709 ,x_return_status OUT NOCOPY VARCHAR2
2710 ,x_msg_count OUT NOCOPY NUMBER
2711 ,x_msg_data OUT NOCOPY VARCHAR2
2712 )
2713 IS
2714 l_Dim_Level_Rec_In BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
2715 l_Dim_Level_Rec_Out BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
2716
2717 BEGIN
2718
2719 SAVEPOINT TranslateBSCDimLevPVT;
2720 x_return_status:= FND_API.G_RET_STS_SUCCESS;
2721 l_Dim_Level_Rec_In := p_Bsc_Dim_Level_Rec;
2722
2723 l_Dim_Level_Rec_In.Bsc_Dim_Level_Long_Name := p_Bsc_Pmf_Dim_Rec.dimension_Level_long_name;
2724 l_Dim_Level_Rec_In.Bsc_Level_Short_Name := p_Bsc_Pmf_Dim_Rec.dimension_level_short_name;
2725
2726 BSC_DIMENSION_LEVELS_PVT.Retr_Updated_Bsc_Dim_Levels
2727 (p_Dim_Level_Rec => l_Dim_Level_Rec_In
2728 ,x_Dim_Level_Rec => l_Dim_Level_Rec_out
2729 ,x_return_status => x_return_status
2730 ,x_msg_count => x_msg_count
2731 ,x_msg_data => x_msg_data
2732 );
2733
2734
2735 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2736 RAISE FND_API.G_EXC_ERROR;
2737 END IF;
2738
2739 UPDATE bsc_sys_dim_levels_tl
2740 SET name = l_Dim_Level_Rec_Out.Bsc_Dim_Level_Long_Name
2741 ,total_disp_name = l_Dim_Level_Rec_Out.Bsc_Dim_Tot_Disp_Name
2742 ,comp_disp_name = l_Dim_Level_Rec_Out.Bsc_Dim_Comp_Disp_Name
2743 ,help = l_Dim_Level_Rec_Out.Bsc_Dim_Level_Help
2744 ,SOURCE_LANG = userenv('LANG')
2745 WHERE dim_level_id = l_Dim_Level_Rec_Out.Bsc_Level_Id
2746 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
2747
2748 IF (p_commit = FND_API.G_TRUE) THEN
2749 COMMIT;
2750 END IF;
2751 EXCEPTION
2752 WHEN FND_API.G_EXC_ERROR THEN
2753 x_return_status := FND_API.G_RET_STS_ERROR ;
2754 ROLLBACK TO TranslateBSCDimLevPVT;
2755 FND_MSG_PUB.Count_And_Get(
2756 p_encoded => 'F'
2757 ,p_count => x_msg_count
2758 ,p_data => x_msg_data
2759 );
2760 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2761 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2762 ROLLBACK TO TranslateBSCDimLevPVT;
2763 FND_MSG_PUB.Count_And_Get(
2764 p_encoded => 'F'
2765 ,p_count => x_msg_count
2766 ,p_data => x_msg_data
2767 );
2768 WHEN OTHERS THEN
2769 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2770 ROLLBACK TO TranslateBSCDimLevPVT;
2771 IF (x_msg_data IS NOT NULL) THEN
2772 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PVT.Translate_Dimension_Level ';
2773 ELSE
2774 x_msg_data := 'BSC_DIMENSION_LEVELS_PVT.Translate_Dimension_Level '||SQLERRM;
2775 END IF;
2776 END Translate_Dimension_Level;
2777
2778
2779
2780 /*************************************************************************************
2781
2782 API TO SYNC UP THE DIMENSION LEVEL DATA FROM PMF TO BSC
2783
2784 *************************************************************************************/
2785
2786 procedure Trans_DimObj_By_Given_Lang
2787 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
2788 , p_dim_level_rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
2789 , x_return_status OUT NOCOPY VARCHAR2
2790 , x_msg_count OUT NOCOPY NUMBER
2791 , x_msg_data OUT NOCOPY VARCHAR2
2792 )
2793 IS
2794 l_Dim_Level_Id NUMBER;
2795 BEGIN
2796 SAVEPOINT TransDimObjByLangBsc;
2797 x_return_status := FND_API.G_RET_STS_SUCCESS;
2798 FND_MSG_PUB.Initialize;
2799
2800 SELECT Dim_Level_Id
2801 INTO l_Dim_Level_Id
2802 FROM BSC_SYS_DIM_LEVELS_B
2803 WHERE Short_Name = p_Dim_Level_Rec.Bsc_Level_Short_Name;
2804
2805
2806 UPDATE BSC_SYS_DIM_LEVELS_TL
2807 SET NAME = p_dim_level_rec.Bsc_Dim_Level_Long_Name
2808 ,HELP = p_dim_level_rec.Bsc_Dim_Level_Help
2809 ,SOURCE_LANG = p_dim_level_rec.Bsc_Source_Language
2810 WHERE DIM_LEVEL_ID = l_Dim_Level_Id
2811 AND LANGUAGE = p_dim_level_rec.Bsc_Language;
2812
2813
2814 IF (p_commit = FND_API.G_TRUE) THEN
2815 COMMIT;
2816 END IF;
2817
2818
2819 EXCEPTION
2820 WHEN FND_API.G_EXC_ERROR THEN
2821 ROLLBACK TO TransDimObjByLangBsc;
2822 x_return_status := FND_API.G_RET_STS_ERROR;
2823 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2824 ,p_data => x_msg_data);
2825
2826 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2827 ROLLBACK TO TransDimObjByLangBsc;
2828 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2829 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2830 ,p_data => x_msg_data);
2831 WHEN NO_DATA_FOUND THEN
2832 ROLLBACK TO TransDimObjByLangBsc;
2833 x_return_status := FND_API.G_RET_STS_ERROR;
2834 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2835 ,p_data => x_msg_data);
2836
2837 WHEN OTHERS THEN
2838 ROLLBACK TO TransDimObjByLangBsc;
2839 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2840 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2841 ,p_data => x_msg_data);
2842
2843 END Trans_DimObj_By_Given_Lang;
2844
2845
2846 end BSC_DIMENSION_LEVELS_PVT;