[Home] [Help]
PACKAGE BODY: APPS.BSC_DIMENSION_LEVELS_PUB
Source
1 package body BSC_DIMENSION_LEVELS_PUB as
2 /* $Header: BSCPDMLB.pls 120.0 2005/06/01 16:50:01 appldev noship $ */
3 /*
4 +======================================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +======================================================================================+
8 | FILENAME |
9 | BSCPDMLB.pls |
10 | |
11 | Creation Date: |
12 | October 9, 2001 |
13 | |
14 | Creator: |
15 | Mario-Jair Campos |
16 | |
17 | Description: |
18 | Public version. |
19 | This Package creates a dimension level in BSC. |
20 | History |
21 | 24-APR-2003 is_Child_Parent() Added by ADRAO for Enh#2901823 |
22 | 07-MAY-2003 Retrieve_Relationship() Added by ADRAO for change Enh#2901823 |
23 | 01-JUN-2003 Created Overloaded procedure Create_Dim_Level |
24 | 07-JUN-03 mahrao Modified for ALL enhancement |
25 | 05-JUN-2003 ADRAO made changes for Granular Locking |
26 | 14-JUN-03 mahrao Added Translate_dimesnsion_level procedure for enh# 2842894 |
27 | 17-JUL-03 mahrao Modified Load_Dimension_Level exception handling section |
28 | as part of forward port of ALL enhancement to BSC 5.1 |
29 | 22-JUL-2003 arhegde bug# 3050270 Modified Create_Dim_Level(); 'All' and 'Comparison' |
30 | got from lookups |
31 | 04-NOV-2003 PAJOHRI Bug #3232366 |
32 | 02-MAR-2004 ankgoel Bug #3464470 |
33 | 26-MAR-2004 kyadamak Bug# 3528143 Removed the hardcoding of 'US' for source language |
34 | and language |
35 | 30-Jul-04 rpenneru Modified for enhancemen#3748519 |
36 | 21-DEC-04 vtulasi Modified for bug#4045278 - Addtion of LUD |
37 | 30-DEC-04 vtulasi For bug #4093926 |
38 | 09-Feb-05 ankgoel Bug#4172055 LUD validations for dim_lvls_by_group |
39 | 02-MAR-05 ashankar Bug#3583110 modified l_Dim_Level_Rec.Bsc_Level_Abbreviation |
40 | when called from upload of LDT file |
41 +======================================================================================+
42 */
43
44 G_PKG_NAME CONSTANT varchar2(30) := 'BSC_DIMENSION_LEVELS_PUB';
45
46 /*
47 The following procedures are used by BSC to create Dimension Levels.
48 */
49
50 --: The following procedure is used to create the BSC Dimension entity.
51 --: It is the entry point to populate all necessary meta data.
52 --: This procedure is part of the Dimension API.
53 procedure Create_Dim_Level(
54 p_commit IN varchar2 ---:= FND_API.G_FALSE
55 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
56 ,p_create_tables IN BOOLEAN
57 ,x_return_status OUT NOCOPY varchar2
58 ,x_msg_count OUT NOCOPY number
59 ,x_msg_data OUT NOCOPY varchar2
60 )is
61
62 l_Dim_Level_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
63
64 l_count number;
65
66 begin
67 --Assign passed Record values to local Record.
68 x_return_status := FND_API.G_RET_STS_SUCCESS;
69 l_Dim_Level_Rec := p_Dim_Level_Rec;
70
71 -- Check to see that dimension level has not been created. If it has not then create it
72 -- else do nothing.
73 select count(1)
74 into l_count
75 from BSC_SYS_DIM_LEVELS_B
76 where short_name = l_Dim_Level_Rec.Bsc_Level_Short_Name;
77
78 if l_count = 0 then
79
80 --Assign certain default values if they are null.
81 if l_Dim_Level_Rec.Bsc_Dim_Comp_Disp_Name is null then
82 l_Dim_Level_Rec.Bsc_Dim_Comp_Disp_Name := NVL(BSC_APPS.get_lookup_value('BSC_UI_COMMON', 'COMPARISON'), 'COMPARISON');
83 end if;
84 if l_Dim_Level_Rec.Bsc_Dim_Level_Help is null then
85 l_Dim_Level_Rec.Bsc_Dim_Level_Help := 'Help: ' || l_Dim_Level_Rec.Bsc_Dim_Level_Long_Name;
86 end if;
87 if l_Dim_Level_Rec.Bsc_Dim_Tot_Disp_Name is null then
88 l_Dim_Level_Rec.Bsc_Dim_Tot_Disp_Name := NVL(BSC_APPS.get_lookup_value('BSC_UI_COMMON', 'ALL'), 'ALL');
89 end if;
90 if l_Dim_Level_Rec.Bsc_Level_Abbreviation is null then
91 l_Dim_Level_Rec.Bsc_Level_Abbreviation := substr(replace(l_Dim_Level_Rec.Bsc_Dim_Level_Long_Name, ' ', ''), 1, 8);
92 end if;
93 if l_Dim_Level_Rec.Bsc_Level_Comp_Order_By is null then
94 l_Dim_Level_Rec.Bsc_Level_Comp_Order_By := 0;
95 end if;
96 if l_Dim_Level_Rec.Bsc_Level_Custom_Group is null then
97 l_Dim_Level_Rec.Bsc_Level_Custom_Group := 0;
98 end if;
99 if l_Dim_Level_Rec.Bsc_Level_Disp_Key_Size is null then
100 l_Dim_Level_Rec.Bsc_Level_Disp_Key_Size := 15;
101 end if;
102 if l_Dim_Level_Rec.Bsc_Level_User_Key_Size is null then
103 l_Dim_Level_Rec.Bsc_Level_User_Key_Size := 5;
104 end if;
105 if l_Dim_Level_Rec.Bsc_Level_Table_Type is null then
106 l_Dim_Level_Rec.Bsc_Level_Table_Type := 1;
107 end if;
108 if l_Dim_Level_Rec.Bsc_Level_Value_Order_By is null then
109 l_Dim_Level_Rec.Bsc_Level_Value_Order_By := 0;
110 end if;
111
112 -- Aditya added from PMD GL
113 if l_Dim_Level_Rec.Bsc_Created_By is null then
114 l_Dim_Level_Rec.Bsc_Created_By := FND_GLOBAL.USER_ID;
115 end if;
116 if l_Dim_Level_Rec.Bsc_Creation_Date is null then
117 l_Dim_Level_Rec.Bsc_Creation_Date := l_Dim_Level_Rec.Bsc_Last_Update_Date;
118 end if;
119 if l_Dim_Level_Rec.Bsc_Last_Updated_By is null then
120 l_Dim_Level_Rec.Bsc_Last_Updated_By := FND_GLOBAL.USER_ID;
121 end if;
122 l_Dim_Level_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Level_Rec.Bsc_Last_Update_Date, SYSDATE);
123 if l_Dim_Level_Rec.Bsc_Last_Update_Login is null then
124 l_Dim_Level_Rec.Bsc_Last_Update_Login := FND_GLOBAL.LOGIN_ID;
125 end if;
126
127
128
129 --Get the next id for this level.
130 IF ( l_Dim_Level_Rec.Bsc_Level_Id IS NULL) THEN
131 l_Dim_Level_Rec.Bsc_Level_Id := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value( 'BSC_SYS_DIM_LEVELS_B'
132 ,'DIM_LEVEL_ID');
133 END IF;
134
135 IF(p_create_tables) THEN
136 BSC_DIMENSION_LEVELS_PVT.Create_Dim_Level( p_commit
137 ,l_Dim_Level_Rec
138 ,x_return_status
139 ,x_msg_count
140 ,x_msg_data);
141 END IF;
142
143 Create_Bsc_Dim_Levels_Md( p_commit
144 ,l_Dim_Level_Rec
145 ,x_return_status
146 ,x_msg_count
147 ,x_msg_data);
148 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
150 END IF;
151 END IF;
152 EXCEPTION
153 WHEN FND_API.G_EXC_ERROR THEN
154 IF (x_msg_data IS NULL) THEN
155 FND_MSG_PUB.Count_And_Get
156 ( p_encoded => 'F'
157 , p_count => x_msg_count
158 , p_data => x_msg_data
159 );
160 END IF;
161 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
162 x_return_status := FND_API.G_RET_STS_ERROR;
163 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
164 IF (x_msg_data IS NULL) THEN
165 FND_MSG_PUB.Count_And_Get
166 ( p_encoded => 'F'
167 , p_count => x_msg_count
168 , p_data => x_msg_data
169 );
170 END IF;
171 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
172 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
173 WHEN NO_DATA_FOUND THEN
174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
175 IF (x_msg_data IS NOT NULL) THEN
176 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
177 ELSE
178 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
179 END IF;
180 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
181 WHEN OTHERS THEN
182 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
183 IF (x_msg_data IS NOT NULL) THEN
184 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
185 ELSE
186 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
187 END IF;
188 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
189 end Create_Dim_Level;
190
191
192 procedure Create_Dim_Level(
193 p_commit IN varchar2 --:= FND_API.G_FALSE
194 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
195 ,x_return_status OUT NOCOPY varchar2
196 ,x_msg_count OUT NOCOPY number
197 ,x_msg_data OUT NOCOPY varchar2
198 ) is
199
200 l_Dim_Level_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
201 BEGIN
202 x_return_status := FND_API.G_RET_STS_SUCCESS;
203 l_Dim_Level_Rec := p_Dim_Level_Rec;
204 l_Dim_Level_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Level_Rec.Bsc_Last_Update_Date, SYSDATE);
205
206 BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level
207 (
208 p_commit => p_commit
209 , p_Dim_Level_Rec => l_Dim_Level_Rec
210 , p_create_tables => TRUE
211 , x_return_status => x_return_status
212 , x_msg_count => x_msg_count
213 , x_msg_data => x_msg_data
214 );
215 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
217 END IF;
218 EXCEPTION
219 WHEN FND_API.G_EXC_ERROR THEN
220 IF (x_msg_data IS NULL) THEN
221 FND_MSG_PUB.Count_And_Get
222 ( p_encoded => 'F'
223 , p_count => x_msg_count
224 , p_data => x_msg_data
225 );
226 END IF;
227 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
228 x_return_status := FND_API.G_RET_STS_ERROR;
229 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
230 IF (x_msg_data IS NULL) THEN
231 FND_MSG_PUB.Count_And_Get
232 ( p_encoded => 'F'
233 , p_count => x_msg_count
234 , p_data => x_msg_data
235 );
236 END IF;
237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
238 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
239 WHEN NO_DATA_FOUND THEN
240 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
241 IF (x_msg_data IS NOT NULL) THEN
242 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
243 ELSE
244 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
245 END IF;
246 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
247 WHEN OTHERS THEN
248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
249 IF (x_msg_data IS NOT NULL) THEN
250 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
251 ELSE
252 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
253 END IF;
254 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
255 end Create_Dim_Level;
256
257 /************************************************************************************
258 ************************************************************************************/
259
260 procedure Retrieve_Dim_Level(
261 p_commit IN varchar2 --:= FND_API.G_FALSE
262 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
263 ,x_Dim_Level_Rec IN OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
264 ,x_return_status OUT NOCOPY varchar2
265 ,x_msg_count OUT NOCOPY number
266 ,x_msg_data OUT NOCOPY varchar2
267 ) is
268
269 begin
270 x_return_status := FND_API.G_RET_STS_SUCCESS;
271 Retrieve_Bsc_Dim_Levels_Md( p_commit
272 ,p_Dim_Level_Rec
273 ,x_Dim_Level_Rec
274 ,x_return_status
275 ,x_msg_count
276 ,x_msg_data);
277 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
278 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
279 END IF;
280 EXCEPTION
281 WHEN FND_API.G_EXC_ERROR THEN
282 IF (x_msg_data IS NULL) THEN
283 FND_MSG_PUB.Count_And_Get
284 ( p_encoded => 'F'
285 , p_count => x_msg_count
286 , p_data => x_msg_data
287 );
288 END IF;
289 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
290 x_return_status := FND_API.G_RET_STS_ERROR;
291 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
292 IF (x_msg_data IS NULL) THEN
293 FND_MSG_PUB.Count_And_Get
294 ( p_encoded => 'F'
295 , p_count => x_msg_count
296 , p_data => x_msg_data
297 );
298 END IF;
299 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
300 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
301 WHEN NO_DATA_FOUND THEN
302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
303 IF (x_msg_data IS NOT NULL) THEN
304 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Retrieve_Dim_Level ';
305 ELSE
306 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Retrieve_Dim_Level ';
307 END IF;
308 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
309 WHEN OTHERS THEN
310 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
311 IF (x_msg_data IS NOT NULL) THEN
312 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Retrieve_Dim_Level ';
313 ELSE
314 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Retrieve_Dim_Level ';
315 END IF;
316 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
317 end Retrieve_Dim_Level;
318
319 /************************************************************************************
320 ************************************************************************************/
321
322 procedure Update_Dim_Level(
323 p_commit IN varchar2 --:= FND_API.G_FALSE
324 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
325 ,x_return_status OUT NOCOPY varchar2
326 ,x_msg_count OUT NOCOPY number
327 ,x_msg_data OUT NOCOPY varchar2
328 ) is
329
330 l_Dim_Level_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
331 begin
332
333 x_return_status := FND_API.G_RET_STS_SUCCESS;
334
335 l_Dim_Level_Rec := p_Dim_Level_Rec;
336 l_Dim_Level_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Level_Rec.Bsc_Last_Update_Date, SYSDATE);
337
338 Update_Bsc_Dim_Levels_Md( p_commit
339 ,l_Dim_Level_Rec
340 ,x_return_status
341 ,x_msg_count
342 ,x_msg_data);
343
344 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
345 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
346 END IF;
347 EXCEPTION
348 WHEN FND_API.G_EXC_ERROR THEN
349 IF (x_msg_data IS NULL) THEN
350 FND_MSG_PUB.Count_And_Get
351 ( p_encoded => 'F'
352 , p_count => x_msg_count
353 , p_data => x_msg_data
354 );
355 END IF;
356 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
357 x_return_status := FND_API.G_RET_STS_ERROR;
358 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
359 IF (x_msg_data IS NULL) THEN
360 FND_MSG_PUB.Count_And_Get
361 ( p_encoded => 'F'
362 , p_count => x_msg_count
363 , p_data => x_msg_data
364 );
365 END IF;
366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
367 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
368 WHEN NO_DATA_FOUND THEN
369 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
370 IF (x_msg_data IS NOT NULL) THEN
371 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Update_Dim_Level ';
372 ELSE
373 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Update_Dim_Level ';
374 END IF;
375 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
376 WHEN OTHERS THEN
377 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
378 IF (x_msg_data IS NOT NULL) THEN
379 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Update_Dim_Level ';
380 ELSE
381 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Update_Dim_Level ';
382 END IF;
383 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
384 end Update_Dim_Level;
385
386 /************************************************************************************
387 ************************************************************************************/
388
389 procedure Delete_Dim_Level(
390 p_commit IN varchar2 --:= FND_API.G_FALSE
391 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
392 ,x_return_status OUT NOCOPY varchar2
393 ,x_msg_count OUT NOCOPY number
394 ,x_msg_data OUT NOCOPY varchar2
395 ) is
396
397 begin
398 x_return_status := FND_API.G_RET_STS_SUCCESS;
399 BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level
400 (
401 p_commit => p_commit
402 , p_Dim_Level_Rec => p_Dim_Level_Rec
403 , x_return_status => x_return_status
404 , x_msg_count => x_msg_count
405 , x_msg_data => x_msg_data
406 );
407 Delete_Bsc_Dim_Levels_Md( p_commit
408 ,p_Dim_Level_Rec
409 ,x_return_status
410 ,x_msg_count
411 ,x_msg_data);
412 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
413 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
414 END IF;
415 EXCEPTION
416 WHEN FND_API.G_EXC_ERROR THEN
417 IF (x_msg_data IS NULL) THEN
418 FND_MSG_PUB.Count_And_Get
419 ( p_encoded => 'F'
420 , p_count => x_msg_count
421 , p_data => x_msg_data
422 );
423 END IF;
424 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
425 x_return_status := FND_API.G_RET_STS_ERROR;
426 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
427 IF (x_msg_data IS NULL) THEN
428 FND_MSG_PUB.Count_And_Get
429 ( p_encoded => 'F'
430 , p_count => x_msg_count
431 , p_data => x_msg_data
432 );
433 END IF;
434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
435 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
436 WHEN NO_DATA_FOUND THEN
437 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
438 IF (x_msg_data IS NOT NULL) THEN
439 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level ';
440 ELSE
441 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level ';
442 END IF;
443 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
444 WHEN OTHERS THEN
445 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
446 IF (x_msg_data IS NOT NULL) THEN
447 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level ';
448 ELSE
449 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level ';
450 END IF;
451 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
452 end Delete_Dim_Level;
453
454 /************************************************************************************
455 ************************************************************************************/
456
457 --: This procedure populates the meta data for BSC dimensions, such as
458 --: dimension id, dimension names, dimension view/table columns.
459 --: This procedure is part of the Dimension API.
460
461 procedure Create_Bsc_Dim_Levels_Md(
462 p_commit IN varchar2 -- := FND_API.G_FALSE
463 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
464 ,x_return_status OUT NOCOPY varchar2
465 ,x_msg_count OUT NOCOPY number
466 ,x_msg_data OUT NOCOPY varchar2
467 ) is
468
469 l_Dim_Level_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
470
471 begin
472 x_return_status := FND_API.G_RET_STS_SUCCESS;
473 -- Assign all values in the passed "Record" parameter to the locally defined
474 -- "Record" variable.
475 l_Dim_Level_Rec := p_Dim_Level_Rec;
476
477 l_Dim_Level_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Level_Rec.Bsc_Last_Update_Date, SYSDATE);
478
479 -- Call the private package.procedure.
480 BSC_DIMENSION_LEVELS_PVT.Create_Bsc_Dim_Levels_Md( p_commit
481 ,l_Dim_Level_Rec
482 ,x_return_status
483 ,x_msg_count
484 ,x_msg_data);
485
486 -- Reminder: The values below are hard coded, need to find a source for them.
487 -- Also there are more values when the table has parent and children.
488
489 l_Dim_Level_Rec.Bsc_Level_Column_Name := 'CODE';
490 l_Dim_Level_Rec.Bsc_Level_Column_Type := 'P';
491 Create_Bsc_Sys_Dim_Lvl_Cols( p_commit
492 ,l_Dim_Level_Rec
493 ,x_return_status
494 ,x_msg_count
495 ,x_msg_data);
496 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
497 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
498 END IF;
499 l_Dim_Level_Rec.Bsc_Level_Column_Name := 'USER_CODE';
500 l_Dim_Level_Rec.Bsc_Level_Column_Type := 'U';
501 Create_Bsc_Sys_Dim_Lvl_Cols( p_commit
502 ,l_Dim_Level_Rec
503 ,x_return_status
504 ,x_msg_count
505 ,x_msg_data);
506 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
507 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
508 END IF;
509 l_Dim_Level_Rec.Bsc_Level_Column_Name := 'NAME';
510 l_Dim_Level_Rec.Bsc_Level_Column_Type := 'D';
511 Create_Bsc_Sys_Dim_Lvl_Cols( p_commit
512 ,l_Dim_Level_Rec
513 ,x_return_status
514 ,x_msg_count
515 ,x_msg_data);
516 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
517 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
518 END IF;
519 EXCEPTION
520 WHEN FND_API.G_EXC_ERROR THEN
521 IF (x_msg_data IS NULL) THEN
522 FND_MSG_PUB.Count_And_Get
523 ( p_encoded => 'F'
524 , p_count => x_msg_count
525 , p_data => x_msg_data
526 );
527 END IF;
528 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
529 x_return_status := FND_API.G_RET_STS_ERROR;
530 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
531 IF (x_msg_data IS NULL) THEN
532 FND_MSG_PUB.Count_And_Get
533 ( p_encoded => 'F'
534 , p_count => x_msg_count
535 , p_data => x_msg_data
536 );
537 END IF;
538 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
539 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
540 WHEN NO_DATA_FOUND THEN
541 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
542 IF (x_msg_data IS NOT NULL) THEN
543 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Bsc_Dim_Levels_Md ';
544 ELSE
545 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Bsc_Dim_Levels_Md ';
546 END IF;
547 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
548 WHEN OTHERS THEN
549 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
550 IF (x_msg_data IS NOT NULL) THEN
551 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Bsc_Dim_Levels_Md ';
552 ELSE
553 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Bsc_Dim_Levels_Md ';
554 END IF;
555 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
556 end Create_Bsc_Dim_Levels_Md;
557
558 /************************************************************************************
559 ************************************************************************************/
560
561 procedure Retrieve_Bsc_Dim_Levels_Md(
562 p_commit IN varchar2 --:= FND_API.G_FALSE
563 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
564 ,x_Dim_Level_Rec IN OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
565 ,x_return_status OUT NOCOPY varchar2
566 ,x_msg_count OUT NOCOPY number
567 ,x_msg_data OUT NOCOPY varchar2
568 ) is
569
570 begin
571 x_return_status := FND_API.G_RET_STS_SUCCESS;
572 BSC_DIMENSION_LEVELS_PVT.Retrieve_Bsc_Dim_Levels_Md( p_commit
573 ,p_Dim_Level_Rec
574 ,x_Dim_Level_Rec
575 ,x_return_status
576 ,x_msg_count
577 ,x_msg_data);
578
579 Retrieve_Bsc_Sys_Dim_Lvl_Cols( p_commit
580 ,p_Dim_Level_Rec
581 ,x_Dim_Level_Rec
582 ,x_return_status
583 ,x_msg_count
584 ,x_msg_data);
585 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
586 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
587 END IF;
588 EXCEPTION
589 WHEN FND_API.G_EXC_ERROR THEN
590 IF (x_msg_data IS NULL) THEN
591 FND_MSG_PUB.Count_And_Get
592 ( p_encoded => 'F'
593 , p_count => x_msg_count
594 , p_data => x_msg_data
595 );
596 END IF;
597 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
598 x_return_status := FND_API.G_RET_STS_ERROR;
599 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
600 IF (x_msg_data IS NULL) THEN
601 FND_MSG_PUB.Count_And_Get
602 ( p_encoded => 'F'
603 , p_count => x_msg_count
604 , p_data => x_msg_data
605 );
606 END IF;
607 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
608 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
609 WHEN NO_DATA_FOUND THEN
610 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
611 IF (x_msg_data IS NOT NULL) THEN
612 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Retrieve_Bsc_Dim_Levels_Md ';
613 ELSE
614 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Retrieve_Bsc_Dim_Levels_Md ';
615 END IF;
616 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
617 WHEN OTHERS THEN
618 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
619 IF (x_msg_data IS NOT NULL) THEN
620 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Retrieve_Bsc_Dim_Levels_Md ';
621 ELSE
622 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Retrieve_Bsc_Dim_Levels_Md ';
623 END IF;
624 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
625 end Retrieve_Bsc_Dim_Levels_Md;
626
627 /************************************************************************************
628 ************************************************************************************/
629
630 procedure Update_Bsc_Dim_Levels_Md(
631 p_commit IN varchar2 --:= FND_API.G_FALSE
632 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
633 ,x_return_status OUT NOCOPY varchar2
634 ,x_msg_count OUT NOCOPY number
635 ,x_msg_data OUT NOCOPY varchar2
636 ) is
637
638 l_Dim_Level_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
639
640 begin
641 x_return_status := FND_API.G_RET_STS_SUCCESS;
642 -- Assign all values in the passed "Record" parameter to the locally defined
643 -- "Record" variable.
644 l_Dim_Level_Rec := p_Dim_Level_Rec;
645 l_Dim_Level_Rec.Bsc_Last_Update_Date := NVL(p_Dim_Level_Rec.Bsc_Last_Update_Date, SYSDATE);
646 -- If language values are null assign 'US'.
647
648 BSC_DIMENSION_LEVELS_PVT.Update_Bsc_Dim_Levels_Md( p_commit
649 ,l_Dim_Level_Rec
650 ,x_return_status
651 ,x_msg_count
652 ,x_msg_data);
653
654 Update_Bsc_Sys_Dim_Lvl_Cols( p_commit
655 ,l_Dim_Level_Rec
656 ,x_return_status
657 ,x_msg_count
658 ,x_msg_data);
659 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
660 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
661 END IF;
662 EXCEPTION
663 WHEN FND_API.G_EXC_ERROR THEN
664 IF (x_msg_data IS NULL) THEN
665 FND_MSG_PUB.Count_And_Get
666 ( p_encoded => 'F'
667 , p_count => x_msg_count
668 , p_data => x_msg_data
669 );
670 END IF;
671 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
672 x_return_status := FND_API.G_RET_STS_ERROR;
673 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
674 IF (x_msg_data IS NULL) THEN
675 FND_MSG_PUB.Count_And_Get
676 ( p_encoded => 'F'
677 , p_count => x_msg_count
678 , p_data => x_msg_data
679 );
680 END IF;
681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
682 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
683 WHEN NO_DATA_FOUND THEN
684 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
685 IF (x_msg_data IS NOT NULL) THEN
686 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Update_Bsc_Dim_Levels_Md ';
687 ELSE
688 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Update_Bsc_Dim_Levels_Md ';
689 END IF;
690 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
691 WHEN OTHERS THEN
692 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
693 IF (x_msg_data IS NOT NULL) THEN
694 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Update_Bsc_Dim_Levels_Md ';
695 ELSE
696 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Update_Bsc_Dim_Levels_Md ';
697 END IF;
698 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
699 end Update_Bsc_Dim_Levels_Md;
700
701 /************************************************************************************
702 ************************************************************************************/
703
704 procedure Delete_Bsc_Dim_Levels_Md(
705 p_commit IN varchar2 --:= FND_API.G_FALSE
706 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
707 ,x_return_status OUT NOCOPY varchar2
708 ,x_msg_count OUT NOCOPY number
709 ,x_msg_data OUT NOCOPY varchar2
710 ) is
711
712 -- Procedure to delete meta data for the Dimension Level.
713
714 begin
715 x_return_status := FND_API.G_RET_STS_SUCCESS;
716 -- Call the private package.procedure.
717 BSC_DIMENSION_LEVELS_PVT.Delete_Bsc_Dim_Levels_Md( p_commit
718 ,p_Dim_Level_Rec
719 ,x_return_status
720 ,x_msg_count
721 ,x_msg_data);
722 IF ((x_return_status = FND_API.G_RET_STS_ERROR) OR (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)) THEN
723 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
724 END IF;
725 Delete_Bsc_Sys_Dim_Lvl_Cols( p_commit
726 ,p_Dim_Level_Rec
727 ,x_return_status
728 ,x_msg_count
729 ,x_msg_data);
730 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
731 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
732 END IF;
733 EXCEPTION
734 WHEN FND_API.G_EXC_ERROR THEN
735 IF (x_msg_data IS NULL) THEN
736 FND_MSG_PUB.Count_And_Get
737 ( p_encoded => 'F'
738 , p_count => x_msg_count
739 , p_data => x_msg_data
740 );
741 END IF;
742 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
743 x_return_status := FND_API.G_RET_STS_ERROR;
744 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
745 IF (x_msg_data IS NULL) THEN
746 FND_MSG_PUB.Count_And_Get
747 ( p_encoded => 'F'
748 , p_count => x_msg_count
749 , p_data => x_msg_data
750 );
751 END IF;
752 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
753 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
754 WHEN NO_DATA_FOUND THEN
755 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
756 IF (x_msg_data IS NOT NULL) THEN
757 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Delete_Bsc_Dim_Levels_Md ';
758 ELSE
759 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Delete_Bsc_Dim_Levels_Md ';
760 END IF;
761 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
762 WHEN OTHERS THEN
763 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
764 IF (x_msg_data IS NOT NULL) THEN
765 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Delete_Bsc_Dim_Levels_Md ';
766 ELSE
767 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Delete_Bsc_Dim_Levels_Md ';
768 END IF;
769 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
770 end Delete_Bsc_Dim_Levels_Md;
771
772 /************************************************************************************
773 ************************************************************************************/
774
775 --: This procedure populates column information for the Dimension view/table.
776 --: This procedure is part of the Dimension API.
777
778 procedure Create_Bsc_Sys_Dim_Lvl_Cols(
779 p_commit IN varchar2 -- := FND_API.G_FALSE
780 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
781 ,x_return_status OUT NOCOPY varchar2
782 ,x_msg_count OUT NOCOPY number
783 ,x_msg_data OUT NOCOPY varchar2
784 ) is
785
786 begin
787 x_return_status := FND_API.G_RET_STS_SUCCESS;
788 -- Call the private package.procedure.
789 BSC_DIMENSION_LEVELS_PVT.Create_Bsc_Sys_Dim_Lvl_Cols( p_commit
790 ,p_Dim_Level_Rec
791 ,x_return_status
792 ,x_msg_count
793 ,x_msg_data);
794
795 EXCEPTION
796 WHEN FND_API.G_EXC_ERROR THEN
797 IF (x_msg_data IS NULL) THEN
798 FND_MSG_PUB.Count_And_Get
799 ( p_encoded => 'F'
800 , p_count => x_msg_count
801 , p_data => x_msg_data
802 );
803 END IF;
804 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
805 x_return_status := FND_API.G_RET_STS_ERROR;
806 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
807 IF (x_msg_data IS NULL) THEN
808 FND_MSG_PUB.Count_And_Get
809 ( p_encoded => 'F'
810 , p_count => x_msg_count
811 , p_data => x_msg_data
812 );
813 END IF;
814 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
815 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
816 WHEN NO_DATA_FOUND THEN
817 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
818 IF (x_msg_data IS NOT NULL) THEN
819 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Bsc_Sys_Dim_Lvl_Cols ';
820 ELSE
821 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Bsc_Sys_Dim_Lvl_Cols ';
822 END IF;
823 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
824 WHEN OTHERS THEN
825 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
826 IF (x_msg_data IS NOT NULL) THEN
827 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Bsc_Sys_Dim_Lvl_Cols ';
828 ELSE
829 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Bsc_Sys_Dim_Lvl_Cols ';
830 END IF;
831 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
832 end Create_Bsc_Sys_Dim_Lvl_Cols;
833
834 /************************************************************************************
835 ************************************************************************************/
836
837 procedure Retrieve_Bsc_Sys_Dim_Lvl_Cols(
838 p_commit IN varchar2 --:= FND_API.G_FALSE
839 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
840 ,x_Dim_Level_Rec IN OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
841 ,x_return_status OUT NOCOPY varchar2
842 ,x_msg_count OUT NOCOPY number
843 ,x_msg_data OUT NOCOPY varchar2
844 ) is
845
846 begin
847 x_return_status := FND_API.G_RET_STS_SUCCESS;
848 BSC_DIMENSION_LEVELS_PVT.Retrieve_Bsc_Sys_Dim_Lvl_Cols( p_commit
849 ,p_Dim_Level_Rec
850 ,x_Dim_Level_Rec
851 ,x_return_status
852 ,x_msg_count
853 ,x_msg_data);
854
855 EXCEPTION
856 WHEN FND_API.G_EXC_ERROR THEN
857 IF (x_msg_data IS NULL) THEN
858 FND_MSG_PUB.Count_And_Get
859 ( p_encoded => 'F'
860 , p_count => x_msg_count
861 , p_data => x_msg_data
862 );
863 END IF;
864 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
865 x_return_status := FND_API.G_RET_STS_ERROR;
866 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
867 IF (x_msg_data IS NULL) THEN
868 FND_MSG_PUB.Count_And_Get
869 ( p_encoded => 'F'
870 , p_count => x_msg_count
871 , p_data => x_msg_data
872 );
873 END IF;
874 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
875 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
876 WHEN NO_DATA_FOUND THEN
877 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
878 IF (x_msg_data IS NOT NULL) THEN
879 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Retrieve_Bsc_Sys_Dim_Lvl_Cols ';
880 ELSE
881 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Retrieve_Bsc_Sys_Dim_Lvl_Cols ';
882 END IF;
883 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
884 WHEN OTHERS THEN
885 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
886 IF (x_msg_data IS NOT NULL) THEN
887 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Retrieve_Bsc_Sys_Dim_Lvl_Cols ';
888 ELSE
889 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Retrieve_Bsc_Sys_Dim_Lvl_Cols ';
890 END IF;
891 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
892 end Retrieve_Bsc_Sys_Dim_Lvl_Cols;
893
894 /************************************************************************************
895 ************************************************************************************/
896
897 procedure Update_Bsc_Sys_Dim_Lvl_Cols(
898 p_commit IN varchar2 --:= FND_API.G_FALSE
899 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
900 ,x_return_status OUT NOCOPY varchar2
901 ,x_msg_count OUT NOCOPY number
902 ,x_msg_data OUT NOCOPY varchar2
903 ) is
904
905 begin
906 x_return_status := FND_API.G_RET_STS_SUCCESS;
907 BSC_DIMENSION_LEVELS_PVT.Update_Bsc_Sys_Dim_Lvl_Cols( p_commit
908 ,p_Dim_Level_Rec
909 ,x_return_status
910 ,x_msg_count
911 ,x_msg_data);
912
913
914 EXCEPTION
915 WHEN FND_API.G_EXC_ERROR THEN
916 IF (x_msg_data IS NULL) THEN
917 FND_MSG_PUB.Count_And_Get
918 ( p_encoded => 'F'
919 , p_count => x_msg_count
920 , p_data => x_msg_data
921 );
922 END IF;
923 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
924 x_return_status := FND_API.G_RET_STS_ERROR;
925 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
926 IF (x_msg_data IS NULL) THEN
927 FND_MSG_PUB.Count_And_Get
928 ( p_encoded => 'F'
929 , p_count => x_msg_count
930 , p_data => x_msg_data
931 );
932 END IF;
933 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
934 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
935 WHEN NO_DATA_FOUND THEN
936 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
937 IF (x_msg_data IS NOT NULL) THEN
938 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Update_Bsc_Sys_Dim_Lvl_Cols ';
939 ELSE
940 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Update_Bsc_Sys_Dim_Lvl_Cols ';
941 END IF;
942 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
943 WHEN OTHERS THEN
944 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
945 IF (x_msg_data IS NOT NULL) THEN
946 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Update_Bsc_Sys_Dim_Lvl_Cols ';
947 ELSE
948 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Update_Bsc_Sys_Dim_Lvl_Cols ';
949 END IF;
950 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
951 end Update_Bsc_Sys_Dim_Lvl_Cols;
952
953 /************************************************************************************
954 ************************************************************************************/
955
956 procedure Delete_Bsc_Sys_Dim_Lvl_Cols(
957 p_commit IN varchar2 --:= FND_API.G_FALSE
958 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
959 ,x_return_status OUT NOCOPY varchar2
960 ,x_msg_count OUT NOCOPY number
961 ,x_msg_data OUT NOCOPY varchar2
962 ) is
963
964 -- Procedure to Delete data on Dimension Level column information.
965 begin
966 x_return_status := FND_API.G_RET_STS_SUCCESS;
967 -- Call the private package.procedure.
968 BSC_DIMENSION_LEVELS_PVT.Delete_Bsc_Sys_Dim_Lvl_Cols( p_commit
969 ,p_Dim_Level_Rec
970 ,x_return_status
971 ,x_msg_count
972 ,x_msg_data);
973 IF ((x_return_status = FND_API.G_RET_STS_ERROR) OR (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR)) THEN
974 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
975 END IF;
976 EXCEPTION
977 WHEN FND_API.G_EXC_ERROR THEN
978 IF (x_msg_data IS NULL) THEN
979 FND_MSG_PUB.Count_And_Get
980 ( p_encoded => 'F'
981 , p_count => x_msg_count
982 , p_data => x_msg_data
983 );
984 END IF;
985 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
986 x_return_status := FND_API.G_RET_STS_ERROR;
987 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
988 IF (x_msg_data IS NULL) THEN
989 FND_MSG_PUB.Count_And_Get
990 ( p_encoded => 'F'
991 , p_count => x_msg_count
992 , p_data => x_msg_data
993 );
994 END IF;
995 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
996 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
997 WHEN NO_DATA_FOUND THEN
998 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
999 IF (x_msg_data IS NOT NULL) THEN
1000 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Delete_Bsc_Sys_Dim_Lvl_Cols ';
1001 ELSE
1002 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Delete_Bsc_Sys_Dim_Lvl_Cols ';
1003 END IF;
1004 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1005 WHEN OTHERS THEN
1006 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1007 IF (x_msg_data IS NOT NULL) THEN
1008 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Delete_Bsc_Sys_Dim_Lvl_Cols ';
1009 ELSE
1010 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Delete_Bsc_Sys_Dim_Lvl_Cols ';
1011 END IF;
1012 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1013 end Delete_Bsc_Sys_Dim_Lvl_Cols;
1014
1015
1016
1017 /*********************************************************************************
1018
1019 -- Procedures to Handle Relationships between Dimension Levels
1020
1021 **********************************************************************************/
1022
1023 /*---------------------------------------------------------------------------------------
1024 Procedure :
1025 ---------------------------------------------------------------------------------------*/
1026 PROCEDURE Create_Dim_Level_Relation(
1027 p_commit IN varchar2 --:= FND_API.G_FALSE
1028 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
1029 ,x_return_status OUT NOCOPY varchar2
1030 ,x_msg_count OUT NOCOPY number
1031 ,x_msg_data OUT NOCOPY varchar2
1032 ) IS
1033 v_count number;
1034 v_Dim_Level_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
1035 v_Bsc_Pmf_Dim_Rec BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Rec_Type;
1036
1037
1038 BEGIN
1039 x_return_status := FND_API.G_RET_STS_SUCCESS;
1040 v_Dim_Level_Rec := p_Dim_Level_Rec;
1041
1042 -- if the child is a PMF Level Check for Import the Level --
1043 if p_Dim_Level_Rec.Bsc_Source <> 'BSC' then
1044 v_Dim_Level_Rec.Bsc_Level_Id := BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Id(v_Dim_Level_Rec.Bsc_Level_Short_Name);
1045 if v_Dim_Level_Rec.Bsc_Level_Id is null then
1046 v_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name := v_Dim_Level_Rec.Bsc_Level_Short_Name;
1047 BSC_PMF_UI_API_PUB.Import_PMF_Dim_Level(p_commit ,v_Bsc_Pmf_Dim_Rec
1048 ,x_return_status ,x_msg_count ,x_msg_data );
1049 v_Dim_Level_Rec.Bsc_Level_Id := BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Id(v_Dim_Level_Rec.Bsc_Level_Short_Name);
1050 end if;
1051 end if;
1052
1053 -- if the parent is a PMF Level Check for Import it --
1054 if p_Dim_Level_Rec.Bsc_Parent_Level_Source <> 'BSC' then
1055 v_Dim_Level_Rec.Bsc_Parent_Level_Id := BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Id(v_Dim_Level_Rec.Bsc_Parent_Level_Short_Name);
1056 if v_Dim_Level_Rec.Bsc_Parent_Level_Id is null then
1057 v_Bsc_Pmf_Dim_Rec.Dimension_Level_Short_Name := v_Dim_Level_Rec.Bsc_Parent_Level_Short_Name;
1058 BSC_PMF_UI_API_PUB.Import_PMF_Dim_Level(p_commit ,v_Bsc_Pmf_Dim_Rec
1059 ,x_return_status ,x_msg_count ,x_msg_data );
1060 v_Dim_Level_Rec.Bsc_Parent_Level_Id := BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Id(v_Dim_Level_Rec.Bsc_Parent_Level_Short_Name);
1061 end if;
1062 end if;
1063
1064 -- Insert the relationship Metadata --
1065 if Is_Valid_Relationship(p_commit, v_Dim_Level_Rec
1066 ,x_return_status, x_msg_count, x_msg_data) then
1067 BSC_DIMENSION_LEVELS_PVT.Create_Dim_Level_Relation(p_commit, v_Dim_Level_Rec
1068 ,x_return_status, x_msg_count, x_msg_data);
1069 -- Recreate Dimension Level View --
1070 if p_Dim_Level_Rec.Bsc_Source <> 'BSC' then
1071 BSC_DIMENSION_LEVELS_PVT.Create_BSC_Dim_Level_View (p_commit, v_Dim_Level_Rec
1072 ,x_return_status, x_msg_count, x_msg_data);
1073 end if;
1074
1075 end if;
1076
1077 x_return_status := FND_API.G_RET_STS_SUCCESS;
1078 EXCEPTION
1079 WHEN FND_API.G_EXC_ERROR THEN
1080 IF (x_msg_data IS NULL) THEN
1081 FND_MSG_PUB.Count_And_Get
1082 ( p_encoded => 'F'
1083 , p_count => x_msg_count
1084 , p_data => x_msg_data
1085 );
1086 END IF;
1087 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1088 x_return_status := FND_API.G_RET_STS_ERROR;
1089 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1090 IF (x_msg_data IS NULL) THEN
1091 FND_MSG_PUB.Count_And_Get
1092 ( p_encoded => 'F'
1093 , p_count => x_msg_count
1094 , p_data => x_msg_data
1095 );
1096 END IF;
1097 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1098 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1099 WHEN NO_DATA_FOUND THEN
1100 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1101 IF (x_msg_data IS NOT NULL) THEN
1102 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level_Relation ';
1103 ELSE
1104 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level_Relation ';
1105 END IF;
1106 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1107 WHEN OTHERS THEN
1108 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1109 IF (x_msg_data IS NOT NULL) THEN
1110 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level_Relation ';
1111 ELSE
1112 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level_Relation ';
1113 END IF;
1114 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1115 END Create_Dim_Level_Relation;
1116
1117 /*---------------------------------------------------------------------------------------
1118 Procedure :
1119 ---------------------------------------------------------------------------------------*/
1120 PROCEDURE Delete_Dim_Level_Relation(
1121 p_commit IN varchar2 --:= FND_API.G_FALSE
1122 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
1123 ,x_return_status OUT NOCOPY varchar2
1124 ,x_msg_count OUT NOCOPY number
1125 ,x_msg_data OUT NOCOPY varchar2
1126 ) IS
1127
1128 v_count number;
1129 v_Dim_Level_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
1130
1131
1132 BEGIN
1133 x_return_status := FND_API.G_RET_STS_SUCCESS;
1134 v_Dim_Level_Rec := p_Dim_Level_Rec;
1135
1136 -- Check Bsc_Level_Id if the child is a PMF --
1137 IF (v_Dim_Level_Rec.Bsc_Level_Id IS NULL) THEN
1138 if v_Dim_Level_Rec.Bsc_Source <> 'BSC' then
1139 if v_Dim_Level_Rec.Bsc_Level_Id is null then
1140 v_Dim_Level_Rec.Bsc_Level_Id := BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Id(v_Dim_Level_Rec.Bsc_Level_Short_Name);
1141 end if;
1142 end if;
1143 END IF;
1144
1145 -- Check Bsc_Level_Id if the parent is a PMF --
1146 IF (v_Dim_Level_Rec.Bsc_Parent_Level_Id IS NULL) THEN
1147 if v_Dim_Level_Rec.Bsc_Parent_Level_Source <> 'BSC' then
1148 if v_Dim_Level_Rec.Bsc_Parent_Level_Id is null then
1149 v_Dim_Level_Rec.Bsc_Parent_Level_Id := BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Id(v_Dim_Level_Rec.Bsc_Parent_Level_Short_Name);
1150 end if;
1151 end if;
1152 END IF;
1153
1154 -- Delete the relationship Metadata --
1155 BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level_Relation(p_commit, v_Dim_Level_Rec
1156 ,x_return_status, x_msg_count, x_msg_data);
1157
1158 -- Recreate Level View for BSC --
1159 if v_Dim_Level_Rec.Bsc_Source <> 'BSC' then
1160 BSC_DIMENSION_LEVELS_PVT.Create_BSC_Dim_Level_View (p_commit, v_Dim_Level_Rec,
1161 x_return_status, x_msg_count, x_msg_data);
1162 end if;
1163
1164 --DBMS_OUTPUT.PUT_LINE('End Delete_Dim_Level_Relation ' );
1165
1166 EXCEPTION
1167 WHEN FND_API.G_EXC_ERROR THEN
1168 IF (x_msg_data IS NULL) THEN
1169 FND_MSG_PUB.Count_And_Get
1170 ( p_encoded => 'F'
1171 , p_count => x_msg_count
1172 , p_data => x_msg_data
1173 );
1174 END IF;
1175 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1176 x_return_status := FND_API.G_RET_STS_ERROR;
1177 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1178 IF (x_msg_data IS NULL) THEN
1179 FND_MSG_PUB.Count_And_Get
1180 ( p_encoded => 'F'
1181 , p_count => x_msg_count
1182 , p_data => x_msg_data
1183 );
1184 END IF;
1185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1186 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1187 WHEN NO_DATA_FOUND THEN
1188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1189 IF (x_msg_data IS NOT NULL) THEN
1190 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level_Relation ';
1191 ELSE
1192 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level_Relation ';
1193 END IF;
1194 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1195 WHEN OTHERS THEN
1196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1197 IF (x_msg_data IS NOT NULL) THEN
1198 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level_Relation ';
1199 ELSE
1200 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Delete_Dim_Level_Relation ';
1201 END IF;
1202 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1203 END Delete_Dim_Level_Relation;
1204 /*---------------------------------------------------------------------------------------
1205 Is_Valid_Relationship :
1206 Return tre if the future relation is valid
1207 --------------------------------------------------------------------------------------*/
1208 FUNCTION Is_Valid_Relationship(
1209 p_commit IN varchar2 --:= FND_API.G_FALSE
1210 ,p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
1211 ,x_return_status OUT NOCOPY varchar2
1212 ,x_msg_count OUT NOCOPY number
1213 ,x_msg_data OUT NOCOPY varchar2
1214 ) RETURN BOOLEAN IS
1215
1216 v_Temp BOOLEAN;
1217 v_msg varchar2(4000);
1218
1219 BEGIN
1220 x_return_status := FND_API.G_RET_STS_SUCCESS;
1221 FND_MSG_PUB.Initialize;
1222 v_Temp := BSC_DIMENSION_LEVELS_PVT.Evaluate_Circular_Relationship
1223 ( p_Dim_Level_Rec.Bsc_Level_Id
1224 ,p_Dim_Level_Rec.Bsc_Parent_Level_Id
1225 ,p_Dim_Level_Rec.Bsc_Relation_Type
1226 ,true
1227 ,v_msg
1228 ,x_return_status
1229 ,x_msg_count
1230 ,x_msg_data
1231 );
1232 if v_Temp = false then
1233 if x_return_status = 'SAME' then
1234 FND_MESSAGE.SET_NAME('BSC','BSC_SAME_DIM_LEVEL_REL');
1235 FND_MESSAGE.SET_TOKEN('LEVEL_CHILD', BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Name(p_Dim_Level_Rec.Bsc_Level_Id) );
1236 FND_MSG_PUB.ADD;
1237 RAISE FND_API.G_EXC_ERROR;
1238 else
1239 FND_MESSAGE.SET_NAME('BSC','BSC_CIRCULAR_DIM_LEVEL_REL');
1240 FND_MESSAGE.SET_TOKEN('LEVEL_CHILD', BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Name(p_Dim_Level_Rec.Bsc_Level_Id) );
1241 FND_MESSAGE.SET_TOKEN('LEVEL_PARENT', BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Name(p_Dim_Level_Rec.Bsc_Parent_Level_Id));
1242 FND_MSG_PUB.ADD;
1243 --FND_MESSAGE.SET_NAME('BSC','BSC_EXISTING_DEPENDENCIES');
1244 --FND_MESSAGE.SET_TOKEN('DEPENDENCY', SUBSTR(v_msg, 1, 220)) ;
1245 --FND_MSG_PUB.ADD;
1246 RAISE FND_API.G_EXC_ERROR;
1247 end if;
1248 end if;
1249 x_return_status := FND_API.G_RET_STS_SUCCESS;
1250 RETURN v_Temp;
1251 EXCEPTION
1252 WHEN FND_API.G_EXC_ERROR THEN
1253 IF (x_msg_data IS NULL) THEN
1254 FND_MSG_PUB.Count_And_Get
1255 ( p_encoded => 'F'
1256 , p_count => x_msg_count
1257 , p_data => x_msg_data
1258 );
1259 END IF;
1260 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1261 x_return_status := FND_API.G_RET_STS_ERROR;
1262 RETURN FALSE;
1263 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1264 IF (x_msg_data IS NULL) THEN
1265 FND_MSG_PUB.Count_And_Get
1266 ( p_encoded => 'F'
1267 , p_count => x_msg_count
1268 , p_data => x_msg_data
1269 );
1270 END IF;
1271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1272 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1273 RETURN FALSE;
1274 WHEN NO_DATA_FOUND THEN
1275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1276 IF (x_msg_data IS NOT NULL) THEN
1277 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Is_Valid_Relationship ';
1278 ELSE
1279 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Is_Valid_Relationship ';
1280 END IF;
1281 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1282 RETURN FALSE;
1283 WHEN OTHERS THEN
1284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1285 IF (x_msg_data IS NOT NULL) THEN
1286 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Is_Valid_Relationship ';
1287 ELSE
1288 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Is_Valid_Relationship ';
1289 END IF;
1290 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1291 RETURN FALSE;
1292 END Is_Valid_Relationship;
1293
1294 --------------------------------------------------------------------------------------
1295 -- is_dependent :
1296 -- Return Out NOCOPY parameter x_return_value = 'TRUE' or 'FALSE'
1297 --------------------------------------------------------------------------------------
1298 PROCEDURE is_dependent(
1299 p_commit IN varchar2 --:= FND_API.G_FALSE
1300 ,p_child_dim_level_short_name IN varchar2
1301 ,p_parent_dim_level_short_name IN varchar2
1302 ,x_return_value OUT NOCOPY varchar2
1303 ,x_return_status OUT NOCOPY varchar2 /* return FND_API.G_FALSE or FND_API.G_TRUE */
1304 ,x_msg_count OUT NOCOPY number
1305 ,x_msg_data OUT NOCOPY varchar2
1306 ) IS
1307
1308 CURSOR c_parents IS
1309 Select PARENT_SHORT_NAME
1310 from BSC_SYS_DIM_LEVEL_RELS_V
1311 where SHORT_NAME = UPPER(p_child_dim_level_short_name);
1312
1313 v_parent varchar2(50);
1314 v_count number;
1315
1316 BEGIN
1317 x_return_status := FND_API.G_RET_STS_SUCCESS;
1318 x_return_value := FND_API.G_FALSE;
1319
1320 Select COUNT(1)
1321 into v_count
1322 from BSC_SYS_DIM_LEVEL_RELS_V
1323 where SHORT_NAME = UPPER(p_child_dim_level_short_name)
1324 and PARENT_SHORT_NAME = UPPER(p_parent_dim_level_short_name);
1325
1326 if v_count <> 0 then
1327 x_return_value := FND_API.G_TRUE;
1328 else
1329 OPEN c_parents;
1330 LOOP
1331 FETCH c_parents INTO v_parent;
1332 EXIT WHEN c_parents%NOTFOUND;
1333 is_dependent( p_commit
1334 ,v_parent
1335 ,p_parent_dim_level_short_name
1336 ,x_return_value
1337 ,x_return_status ,x_msg_count ,x_msg_data
1338 );
1339 if x_return_value = FND_API.G_TRUE then
1340 EXIT;
1341 end if;
1342 END LOOP;
1343 CLOSE c_parents;
1344 end if;
1345
1346 --DBMS_OUTPUT.PUT_LINE('end is_dependent ' );
1347
1348 EXCEPTION
1349 WHEN FND_API.G_EXC_ERROR THEN
1350 IF (x_msg_data IS NULL) THEN
1351 FND_MSG_PUB.Count_And_Get
1352 ( p_encoded => 'F'
1353 , p_count => x_msg_count
1354 , p_data => x_msg_data
1355 );
1356 END IF;
1357 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1358 x_return_status := FND_API.G_RET_STS_ERROR;
1359 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1360 IF (x_msg_data IS NULL) THEN
1361 FND_MSG_PUB.Count_And_Get
1362 ( p_encoded => 'F'
1363 , p_count => x_msg_count
1364 , p_data => x_msg_data
1365 );
1366 END IF;
1367 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1368 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1369 WHEN NO_DATA_FOUND THEN
1370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1371 IF (x_msg_data IS NOT NULL) THEN
1372 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.is_dependent ';
1373 ELSE
1374 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.is_dependent ';
1375 END IF;
1376 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1377 WHEN OTHERS THEN
1378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1379 IF (x_msg_data IS NOT NULL) THEN
1380 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.is_dependent ';
1381 ELSE
1382 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.is_dependent ';
1383 END IF;
1384 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1385 END is_dependent;
1386
1387 --------------------------------------------------------------------------------------
1388 -- get_parent_dimension_levels :
1389 -- Return out NOCOPY parameter p_parent_dim_level_short_names with the parent
1390 -- short names separated by commas ","
1391 --------------------------------------------------------------------------------------
1392 PROCEDURE get_parent_dimension_levels(
1393 p_commit IN varchar2 --:= FND_API.G_FALSE
1394 ,p_child_dim_level_short_name IN varchar2
1395 ,p_parent_dim_level_short_names OUT NOCOPY varchar2
1396 ,x_return_status OUT NOCOPY varchar2 /* return FND_API.G_FALSE or FND_API.G_TRUE */
1397 ,x_msg_count OUT NOCOPY number
1398 ,x_msg_data OUT NOCOPY varchar2
1399 ) IS
1400
1401 CURSOR c_parents IS
1402 Select PARENT_SHORT_NAME
1403 from BSC_SYS_DIM_LEVEL_RELS_V
1404 where SHORT_NAME = UPPER(p_child_dim_level_short_name);
1405
1406 v_parent varchar2(50);
1407
1408
1409 BEGIN
1410 x_return_status := FND_API.G_RET_STS_SUCCESS;
1411 --DBMS_OUTPUT.PUT_LINE('Begin get_parent_dimension_levels' );
1412
1413 p_parent_dim_level_short_names := null;
1414 OPEN c_parents;
1415 LOOP
1416 FETCH c_parents INTO v_parent;
1417 EXIT WHEN c_parents%NOTFOUND;
1418 --DBMS_OUTPUT.PUT_LINE(' get_parent_dimension_levels v_parent = ' || v_parent );
1419 if p_parent_dim_level_short_names is not null then
1420 p_parent_dim_level_short_names := p_parent_dim_level_short_names || ',';
1421 end if;
1422 p_parent_dim_level_short_names := p_parent_dim_level_short_names || v_parent;
1423 --DBMS_OUTPUT.PUT_LINE(' get_parent_dimension_levels p_parent_dim_level_short_names = ' || p_parent_dim_level_short_names );
1424
1425 END LOOP;
1426 CLOSE c_parents;
1427
1428 --DBMS_OUTPUT.PUT_LINE('end get_parent_dimension_levels' );
1429
1430 EXCEPTION
1431 WHEN FND_API.G_EXC_ERROR THEN
1432 IF (x_msg_data IS NULL) THEN
1433 FND_MSG_PUB.Count_And_Get
1434 ( p_encoded => 'F'
1435 , p_count => x_msg_count
1436 , p_data => x_msg_data
1437 );
1438 END IF;
1439 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1440 x_return_status := FND_API.G_RET_STS_ERROR;
1441 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1442 IF (x_msg_data IS NULL) THEN
1443 FND_MSG_PUB.Count_And_Get
1444 ( p_encoded => 'F'
1445 , p_count => x_msg_count
1446 , p_data => x_msg_data
1447 );
1448 END IF;
1449 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1450 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1451 WHEN NO_DATA_FOUND THEN
1452 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1453 IF (x_msg_data IS NOT NULL) THEN
1454 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.get_parent_dimension_levels ';
1455 ELSE
1456 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.get_parent_dimension_levels ';
1457 END IF;
1458 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1459 WHEN OTHERS THEN
1460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1461 IF (x_msg_data IS NOT NULL) THEN
1462 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.get_parent_dimension_levels ';
1463 ELSE
1464 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.get_parent_dimension_levels ';
1465 END IF;
1466 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1467 END get_parent_dimension_levels;
1468 --------------------------------------------------------------------------------------
1469 -- get_child_dimension_levels :
1470 -- Return out NOCOPY parameter p_parent_dim_level_short_names with the parent
1471 -- short names separated by commas ","
1472 --------------------------------------------------------------------------------------
1473 PROCEDURE get_child_dimension_levels(
1474 p_commit IN varchar2 --:= FND_API.G_FALSE
1475 ,p_parent_dim_level_short_name IN varchar2
1476 ,p_child_dim_level_short_names OUT NOCOPY varchar2
1477 ,x_return_status OUT NOCOPY varchar2 /* return FND_API.G_FALSE or FND_API.G_TRUE */
1478 ,x_msg_count OUT NOCOPY number
1479 ,x_msg_data OUT NOCOPY varchar2
1480 ) IS
1481 CURSOR c_childs IS
1482 Select SHORT_NAME
1483 from BSC_SYS_DIM_LEVEL_RELS_V
1484 where PARENT_SHORT_NAME = UPPER(p_parent_dim_level_short_name);
1485
1486 v_child varchar2(50);
1487
1488
1489 BEGIN
1490 x_return_status := FND_API.G_RET_STS_SUCCESS;
1491 --DBMS_OUTPUT.PUT_LINE('Begin get_child_dimension_levels' );
1492
1493 p_child_dim_level_short_names := null;
1494 OPEN c_childs;
1495 LOOP
1496 FETCH c_childs INTO v_child;
1497 EXIT WHEN c_childs%NOTFOUND;
1498 --DBMS_OUTPUT.PUT_LINE(' get_child_dimension_levels v_child = ' || v_child );
1499 if p_child_dim_level_short_names is not null then
1500 p_child_dim_level_short_names := p_child_dim_level_short_names || ',' ;
1501 end if;
1502 p_child_dim_level_short_names := p_child_dim_level_short_names || v_child;
1503 --DBMS_OUTPUT.PUT_LINE(' get_child_dimension_levels p_child_dim_level_short_names = ' || p_child_dim_level_short_names );
1504
1505 END LOOP;
1506 CLOSE c_childs;
1507
1508 --DBMS_OUTPUT.PUT_LINE('end get_child_dimension_levels' );
1509
1510 EXCEPTION
1511 WHEN FND_API.G_EXC_ERROR THEN
1512 IF (x_msg_data IS NULL) THEN
1513 FND_MSG_PUB.Count_And_Get
1514 ( p_encoded => 'F'
1515 , p_count => x_msg_count
1516 , p_data => x_msg_data
1517 );
1518 END IF;
1519 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1520 x_return_status := FND_API.G_RET_STS_ERROR;
1521 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1522 IF (x_msg_data IS NULL) THEN
1523 FND_MSG_PUB.Count_And_Get
1524 ( p_encoded => 'F'
1525 , p_count => x_msg_count
1526 , p_data => x_msg_data
1527 );
1528 END IF;
1529 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1530 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1531 WHEN NO_DATA_FOUND THEN
1532 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1533 IF (x_msg_data IS NOT NULL) THEN
1534 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.get_child_dimension_levels ';
1535 ELSE
1536 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.get_child_dimension_levels ';
1537 END IF;
1538 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1539 WHEN OTHERS THEN
1540 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1541 IF (x_msg_data IS NOT NULL) THEN
1542 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.get_child_dimension_levels ';
1543 ELSE
1544 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.get_child_dimension_levels ';
1545 END IF;
1546 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1547 END get_child_dimension_levels;
1548
1549
1550 --------------------------------------------------------------------------------------
1551 -- is_Child_Parent :
1552 -- Check to see if the passed Dim Level Ids form a Parent child relationship.
1553 --
1554 -- Added by ADRAO for Enh#2901823
1555 -- Values returned wouid be in x_return_status (FND_API.G_FALSE or FND_API.G_TRUE)
1556 --------------------------------------------------------------------------------------
1557 FUNCTION is_Child_Parent
1558 (
1559 p_child_dim_level_short_name IN VARCHAR2
1560 , p_parent_dim_level_short_name IN VARCHAR2
1561 , x_return_status OUT NOCOPY VARCHAR2
1562 , x_msg_count OUT NOCOPY NUMBER
1563 , x_msg_data OUT NOCOPY VARCHAR2
1564 ) RETURN BOOLEAN IS
1565
1566 l_count NUMBER;
1567 BEGIN
1568 x_return_status := FND_API.G_RET_STS_SUCCESS;
1569
1570 SELECT COUNT(DIM_LEVEL_ID)
1571 INTO l_count
1572 FROM BSC_SYS_DIM_LEVEL_RELS_V
1573 WHERE SHORT_NAME = p_child_dim_level_short_name
1574 AND PARENT_SHORT_NAME = p_parent_dim_level_short_name ;
1575
1576 IF (l_count <> 0) THEN
1577 RETURN TRUE;
1578 ELSE
1579 RETURN FALSE;
1580 END IF;
1581 EXCEPTION
1582 WHEN OTHERS THEN
1583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1584 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1585 ,p_data => x_msg_data);
1586 RETURN FALSE;
1587 END is_Child_Parent;
1588
1589
1590
1591 /************************************************************************************
1592 ************************************************************************************/
1593
1594 procedure Retrieve_Relationship
1595 (
1596 p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
1597 , x_Dim_Level_Rec OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
1598 , x_return_status OUT NOCOPY VARCHAR2
1599 , x_msg_count OUT NOCOPY NUMBER
1600 , x_msg_data OUT NOCOPY VARCHAR2
1601 ) IS
1602 BEGIN
1603 x_return_status := FND_API.G_RET_STS_SUCCESS;
1604 BSC_DIMENSION_LEVELS_PVT.Retrieve_Relationship
1605 (
1606 p_Dim_Level_Rec => p_Dim_Level_Rec
1607 , x_Dim_Level_Rec => x_Dim_Level_Rec
1608 , x_return_status => x_return_status
1609 , x_msg_count => x_msg_count
1610 , x_msg_data => x_msg_data
1611 );
1612 EXCEPTION
1613 WHEN FND_API.G_EXC_ERROR THEN
1614 IF (x_msg_data IS NULL) THEN
1615 FND_MSG_PUB.Count_And_Get
1616 ( p_encoded => 'F'
1617 , p_count => x_msg_count
1618 , p_data => x_msg_data
1619 );
1620 END IF;
1621 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1622 x_return_status := FND_API.G_RET_STS_ERROR;
1623 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1624 IF (x_msg_data IS NULL) THEN
1625 FND_MSG_PUB.Count_And_Get
1626 ( p_encoded => 'F'
1627 , p_count => x_msg_count
1628 , p_data => x_msg_data
1629 );
1630 END IF;
1631 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1632 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1633 WHEN NO_DATA_FOUND THEN
1634 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1635 IF (x_msg_data IS NOT NULL) THEN
1636 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Retrieve_Relationship ';
1637 ELSE
1638 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Retrieve_Relationship ';
1639 END IF;
1640 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1641 WHEN OTHERS THEN
1642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1643 IF (x_msg_data IS NOT NULL) THEN
1644 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Retrieve_Relationship ';
1645 ELSE
1646 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Retrieve_Relationship ';
1647 END IF;
1648 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1649 END Retrieve_Relationship;
1650
1651 /************************************************************************************
1652 ************************************************************************************/
1653 --PAJOHRI added 01-JUN-2003
1654 PROCEDURE Drop_Dim_Level_Tabs
1655 (
1656 p_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
1657 , x_return_status OUT NOCOPY VARCHAR2
1658 , x_msg_count OUT NOCOPY NUMBER
1659 , x_msg_data OUT NOCOPY VARCHAR2
1660 ) IS
1661 BEGIN
1662 x_return_status := FND_API.G_RET_STS_SUCCESS;
1663 BSC_DIMENSION_LEVELS_PVT.Delete_Dim_Level
1664 (
1665 p_commit => FND_API.G_FALSE
1666 ,p_Dim_Level_Rec => p_Dim_Level_Rec
1667 ,x_return_status => x_return_status
1668 ,x_msg_count => x_msg_count
1669 ,x_msg_data => x_msg_data
1670 );
1671 EXCEPTION
1672 WHEN FND_API.G_EXC_ERROR THEN
1673 IF (x_msg_data IS NULL) THEN
1674 FND_MSG_PUB.Count_And_Get
1675 ( p_encoded => 'F'
1676 , p_count => x_msg_count
1677 , p_data => x_msg_data
1678 );
1679 END IF;
1680 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1681 x_return_status := FND_API.G_RET_STS_ERROR;
1682 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1683 IF (x_msg_data IS NULL) THEN
1684 FND_MSG_PUB.Count_And_Get
1685 ( p_encoded => 'F'
1686 , p_count => x_msg_count
1687 , p_data => x_msg_data
1688 );
1689 END IF;
1690 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1691 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1692 WHEN NO_DATA_FOUND THEN
1693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1694 IF (x_msg_data IS NOT NULL) THEN
1695 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Drop_Dim_Level_Tabs ';
1696 ELSE
1697 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level ';
1698 END IF;
1699 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1700 WHEN OTHERS THEN
1701 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1702 IF (x_msg_data IS NOT NULL) THEN
1703 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Drop_Dim_Level_Tabs ';
1704 ELSE
1705 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Drop_Dim_Level_Tabs ';
1706 END IF;
1707 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1708 end Drop_Dim_Level_Tabs;
1709 --=============================================================================
1710 PROCEDURE Translate_Dimension_Level (
1711 p_Commit IN VARCHAR2 --:= FND_API.G_FALSE
1712 ,p_Bsc_Pmf_Dim_Rec IN BSC_PMF_UI_API_PUB.Bsc_Pmf_Dim_Rec_Type
1713 ,p_Bsc_Dim_Level_Rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
1714 ,x_return_status OUT NOCOPY VARCHAR2
1715 ,x_msg_count OUT NOCOPY NUMBER
1716 ,x_msg_data OUT NOCOPY VARCHAR2
1717 )
1718 IS
1719 BEGIN
1720 x_return_status := FND_API.G_RET_STS_SUCCESS;
1721 BSC_DIMENSION_LEVELS_PVT.Translate_Dimension_Level
1722 ( p_commit => p_Commit
1723 ,p_Bsc_Pmf_Dim_Rec => p_Bsc_Pmf_Dim_Rec
1724 ,p_Bsc_Dim_Level_Rec => p_Bsc_Dim_Level_Rec
1725 ,x_return_status => x_return_status
1726 ,x_msg_count => x_msg_count
1727 ,x_msg_data => x_msg_data
1728 );
1729 EXCEPTION
1730 WHEN FND_API.G_EXC_ERROR THEN
1731 IF (x_msg_data IS NULL) THEN
1732 FND_MSG_PUB.Count_And_Get
1733 ( p_encoded => 'F'
1734 , p_count => x_msg_count
1735 , p_data => x_msg_data
1736 );
1737 END IF;
1738 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1739 x_return_status := FND_API.G_RET_STS_ERROR;
1740 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1741 IF (x_msg_data IS NULL) THEN
1742 FND_MSG_PUB.Count_And_Get
1743 ( p_encoded => 'F'
1744 , p_count => x_msg_count
1745 , p_data => x_msg_data
1746 );
1747 END IF;
1748 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1749 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1750 WHEN NO_DATA_FOUND THEN
1751 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1752 IF (x_msg_data IS NOT NULL) THEN
1753 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Translate_Dimension_Level ';
1754 ELSE
1755 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Translate_Dimension_Level ';
1756 END IF;
1757 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1758 WHEN OTHERS THEN
1759 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1760 IF (x_msg_data IS NOT NULL) THEN
1761 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.Translate_Dimension_Level ';
1762 ELSE
1763 x_msg_data := SQLERRM||' at BSC_DIMENSION_LEVELS_PUB.Translate_Dimension_Level ';
1764 END IF;
1765 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1766 END Translate_Dimension_Level;
1767 --=============================================================================
1768 --=============================================================================
1769 /*
1770 * API called from PMF for "All" enhancement
1771 */
1772 PROCEDURE load_dimension_level(
1773 p_commit IN VARCHAR2 --:= FND_API.G_FALSE
1774 ,p_dim_level_rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
1775 ,x_return_status OUT NOCOPY VARCHAR2
1776 ,x_msg_count OUT NOCOPY NUMBER
1777 ,x_msg_data OUT NOCOPY VARCHAR2
1778 )
1779 IS
1780 l_bsc_dim_rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
1781 l_level_view_name BSC_SYS_DIM_LEVELS_B.Level_View_Name%TYPE;
1782 l_count NUMBER;
1783 l_table_type BSC_SYS_DIM_LEVELS_B.Table_Type%TYPE;
1784 l_level_pk_col BSC_SYS_DIM_LEVELS_B.Level_Pk_Col%TYPE;
1785
1786 BEGIN
1787 x_return_status := FND_API.G_RET_STS_SUCCESS;
1788
1789 l_bsc_dim_rec := p_dim_level_rec;
1790 BEGIN
1791 SELECT dim_level_id
1792 , level_view_name
1793 , table_type
1794 , level_pk_col
1795 INTO l_Bsc_Dim_Rec.Bsc_Level_Id
1796 , l_level_view_name
1797 , l_table_type
1798 , l_level_pk_col
1799 FROM bsc_sys_dim_levels_b
1800 WHERE short_name = p_dim_level_rec.Bsc_Level_Short_Name;
1801 EXCEPTION
1802 WHEN NO_DATA_FOUND THEN
1803 l_Bsc_Dim_Rec.Bsc_Level_Id := NULL;
1804 END;
1805
1806 IF (l_Bsc_Dim_Rec.Bsc_Level_Id IS NULL) THEN
1807 BSC_DIMENSION_LEVELS_PUB.Create_Dim_Level(
1808 p_commit => p_Commit
1809 ,p_Dim_Level_Rec => l_Bsc_Dim_Rec
1810 ,x_return_status => x_return_status
1811 ,x_msg_count => x_msg_count
1812 ,x_msg_data => x_msg_data
1813 );
1814 ELSE
1815 l_Bsc_Dim_Rec.Bsc_Level_Table_Type := l_table_type;
1816 l_Bsc_Dim_Rec.Bsc_Pk_Col := l_level_pk_col;
1817
1818 BSC_DIMENSION_LEVELS_PUB.Update_Dim_Level(
1819 p_commit => p_Commit
1820 ,p_Dim_Level_Rec => l_Bsc_Dim_Rec
1821 ,x_return_status => x_return_status
1822 ,x_msg_count => x_msg_count
1823 ,x_msg_data => x_msg_data
1824 );
1825
1826 END IF;
1827
1828 EXCEPTION
1829 WHEN FND_API.G_EXC_ERROR THEN
1830 x_return_status := FND_API.G_RET_STS_ERROR;
1831 FND_MSG_PUB.Count_And_Get(
1832 p_encoded => 'F'
1833 ,p_count => x_msg_count
1834 ,p_data => x_msg_data
1835 );
1836 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1838 FND_MSG_PUB.Count_And_Get(
1839 p_encoded => 'F'
1840 ,p_count => x_msg_count
1841 ,p_data => x_msg_data
1842 );
1843 WHEN OTHERS THEN
1844 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1845 IF (x_msg_data IS NOT NULL) THEN
1846 x_msg_data := x_msg_data||' -> BSC_DIMENSION_LEVELS_PUB.load_dimension_level ';
1847 ELSE
1848 x_msg_data := 'BSC_DIMENSION_LEVELS_PUB.load_dimension_level '||SQLERRM;
1849 END IF;
1850
1851 END load_dimension_level;
1852 --=============================================================================
1853
1854
1855 /*************************************************************************************
1856
1857 API TO SYNC UP THE DIMENSION LEVEL DATA FROM PMF TO BSC
1858
1859 *************************************************************************************/
1860
1861 procedure Trans_DimObj_By_Given_Lang
1862 (
1863 p_commit IN VARCHAR2
1864 , p_dim_level_rec IN BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
1865 , x_return_status OUT NOCOPY VARCHAR2
1866 , x_msg_count OUT NOCOPY NUMBER
1867 , x_msg_data OUT NOCOPY VARCHAR2
1868 )
1869 IS
1870
1871 BEGIN
1872
1873 BSC_DIMENSION_LEVELS_PVT.Trans_DimObj_By_Given_Lang
1874 (
1875 p_commit => FND_API.G_FALSE
1876 , p_dim_level_rec => p_dim_level_rec
1877 , x_return_status => x_return_status
1878 , x_msg_count => x_msg_count
1879 , x_msg_data => x_msg_data
1880 );
1881
1882
1883 EXCEPTION
1884 WHEN FND_API.G_EXC_ERROR THEN
1885 x_return_status := FND_API.G_RET_STS_ERROR;
1886 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1887 ,p_data => x_msg_data);
1888 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1889 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1890 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1891 ,p_data => x_msg_data);
1892 WHEN NO_DATA_FOUND THEN
1893 x_return_status := FND_API.G_RET_STS_ERROR;
1894 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1895 ,p_data => x_msg_data);
1896 WHEN OTHERS THEN
1897 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1898 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1899 ,p_data => x_msg_data);
1900
1901 END Trans_DimObj_By_Given_Lang;
1902
1903
1904 /*************************************************************************************
1905
1906 API TO FIX/VALIDATE VIEWS FROM SOURCE PMF DIM LEVELS
1907 Called by Concurrent Program BSC_VALID_DIM_LEVELS_TABLE
1908
1909 *************************************************************************************/
1910
1911 PROCEDURE Validate_Imported_Level_Views
1912 (
1913 ERRBUF OUT NOCOPY VARCHAR2
1914 , RETCODE OUT NOCOPY VARCHAR2
1915 )
1916 IS
1917 x_return_status VARCHAR2(1);
1918 x_msg_count NUMBER;
1919 x_msg_data VARCHAR2(200);
1920
1921 l_dim_level_rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
1922 l_Error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1923 l_Count number;
1924 l_Temp_Count NUMBER;
1925 l_Dummy VARCHAR2(100);
1926 l_Debug_Flag BOOLEAN;
1927 l_Fix_Row_Flag BOOLEAN ;
1928 p_commit VARCHAR2(1);
1929 BEGIN
1930
1931 l_Fix_Row_Flag := FALSE;
1932 p_commit := FND_API.G_TRUE;
1933
1934 SAVEPOINT ValidImportDimLevelViews;
1935 FND_MSG_PUB.Initialize;
1936
1937 BIS_UTILITIES_PUB.Get_Debug_Mode_Profile
1938 (
1939 x_Is_Debug_Mode => l_Debug_Flag
1940 , x_Return_Status => x_Return_Status
1941 , x_Return_Msg => x_Msg_Data
1942 );
1943
1944 BIS_UTILITIES_PUB.Set_Debug_Log_Flag
1945 ( p_is_true => TRUE
1946 , x_Return_Status => x_Return_Status
1947 , x_Return_Msg => x_Msg_Data
1948 );
1949
1950 BIS_UTILITIES_PUB.PUT_LINE
1951 (
1952 p_text => 'BEGIN IMPORT DIMENSION OBJECT VIEWS VALIDATION ....'
1953 );
1954
1955
1956 -- Sucess --
1957 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1958
1959 -- Call the refresh Dimension Object Views.
1960 BSC_BIS_DIM_OBJ_PUB.Refresh_BSC_PMF_Dim_View
1961 ( p_Short_Name => NULL
1962 , x_return_status => x_Return_Status
1963 , x_msg_count => x_Msg_Count
1964 , x_msg_data => x_Msg_Data
1965 );
1966
1967
1968 IF (p_commit = FND_API.G_TRUE) THEN
1969 COMMIT;
1970 END IF;
1971
1972 BIS_UTILITIES_PUB.PUT_LINE
1973 (
1974 p_text => 'END IMPORT DIMENSION OBJECT VIEWS VALIDATION ...'
1975 );
1976
1977 BIS_UTILITIES_PUB.Set_Debug_Log_Flag
1978 (
1979 p_Is_True => l_Debug_Flag
1980 , x_Return_Status => x_Return_Status
1981 , x_Return_Msg => x_Msg_Data
1982 );
1983
1984 EXCEPTION
1985 WHEN FND_API.G_EXC_ERROR THEN
1986 x_return_status := FND_API.G_RET_STS_ERROR;
1987 RETCODE := 2; -- Concurrent program display Error
1988 ERRBUF := x_msg_data; -- Concurrent program err message
1989
1990 BIS_UTILITIES_PUB.PUT_LINE
1991 (
1992 p_Text => x_Msg_Data
1993 );
1994
1995 BIS_UTILITIES_PUB.set_debug_log_flag
1996 (
1997 p_is_true => l_debug_flag
1998 , x_return_status => x_return_status
1999 , x_return_msg => x_msg_data
2000 );
2001
2002 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2003 ,p_data => x_msg_data);
2004 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2005 RETCODE := 2; -- Concurrent program display Error
2006 ERRBUF := x_msg_data; -- Concurrent program err message
2007 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2008 BIS_UTILITIES_PUB.PUT_LINE
2009 (
2010 p_Text => x_Msg_Data
2011 );
2012
2013 BIS_UTILITIES_PUB.set_debug_log_flag
2014 (
2015 p_is_true => l_debug_flag
2016 , x_return_status => x_return_status
2017 , x_return_msg => x_msg_data
2018 );
2019 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2020 ,p_data => x_msg_data);
2021 WHEN NO_DATA_FOUND THEN
2022 x_return_status := FND_API.G_RET_STS_ERROR;
2023 RETCODE := 2; -- Concurrent program display Error
2024 ERRBUF := x_msg_data; -- Concurrent program err message
2025 BIS_UTILITIES_PUB.PUT_LINE
2026 (
2027 p_Text => x_Msg_Data
2028 );
2029
2030 BIS_UTILITIES_PUB.set_debug_log_flag
2031 (
2032 p_is_true => l_debug_flag
2033 , x_return_status => x_return_status
2034 , x_return_msg => x_msg_data
2035 );
2036 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2037 ,p_data => x_msg_data);
2038 WHEN OTHERS THEN
2039 RETCODE := 2; -- Concurrent program display Error
2040 ERRBUF := x_msg_data; -- Concurrent program err message
2041 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2042 BIS_UTILITIES_PUB.PUT_LINE
2043 (
2044 p_Text => x_Msg_Data
2045 );
2046
2047 BIS_UTILITIES_PUB.set_debug_log_flag
2048 (
2049 p_is_true => l_debug_flag
2050 , x_return_status => x_return_status
2051 , x_return_msg => x_msg_data
2052 );
2053 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2054 ,p_data => x_msg_data);
2055 RAISE;
2056
2057 END Validate_Imported_Level_Views;
2058
2059 end BSC_DIMENSION_LEVELS_PUB;