[Home] [Help]
PACKAGE BODY: APPS.BIS_DIMENSION_LEVEL_PVT
Source
1 PACKAGE BODY BIS_DIMENSION_LEVEL_PVT AS
2 /* $Header: BISVDMLB.pls 120.3 2006/01/06 03:34:31 akoduri noship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BISVDMLB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Private API for managing dimension levels for the
13 REM | Key Performance Framework.
14 REM |
15 REM | This package should be maintaind by EDW once it gets integrated
16 REM | with BIS.
17 REM |
18 REM | NOTES |
19 REM | juwang 15-APR-2002 Retrieve_Dimension_Level added source column |
20 REM | 25-JUL-2002 jxyu Modified for enhancement #2435226 |
21 REM | 21-OCT-02 arhegde Added retrieve_mult_dim_levels |
22 REM | 27-JAN-03 arhegde For having different local variables for IN and OUT |
23 REM | parameters (bug#2758428) |
24 REM | 24-NOV-02 MAHRAO Modified for enhancement #2668271 |
25 REM | 23-FEB-03 PAJOHRI Added procedures DELETE_DIMENSION_LEVEL |
26 REM | 23-FEB-03 PAJOHRI Modified the package, to handle Application_ID|
27 REM | 29-OCT-03 MAHRAO enh of adding new attributes to dim objects |
28 REM | 15-NOV-03 RCHANDRA enh 2997632 , added methods to check if it is |
29 REM | ok to disable a dimension level |
30 REM | 25-NOV-03 ADEULGAO fixed Bug#3266503 |
31 REM | 01-DEC-03 ADRAO Fixed Bug #3266561 Removed an additional check |
32 REM | to default Comparison_Label_Code & Default_Search to null|
33 REM | if passed as null from UI |
34 REM | 25-JUN-04 ANKGOEL Modified for bug#3567463 |
35 REM | 30-Jul-04 rpenneru Modified for enhancemen#3748519 |
36 REM | 29-SEP-2004 ankgoel Added WHO columns in Rec for Bug#3891748 |
37 REM | 21-DEC-04 vtulasi Modified for bug#4045278 - Addtion of LUD |
38 REM | 08-Feb-04 skchoudh Enh#3873195 drill_to_form_function column |
39 REM | is added |
40 REM | 08-Feb-05 ankgoel Enh#4172034 DD Seeding by Product Teams |
41 REM | 26-Sep-05 ankgoel Bug#4625611 - enable all BSC type dim objects |
42 REM | 07-NOV-05 akoduri Bug#4696105,Added overloaded API |
43 REM | get_customized_enabled |
44 REM | 06-Jan-06 akoduri Enh#4739401 - Hide Dimensions/Dim Objects |
45 REM +=======================================================================+
46 */
47 --
48 G_PKG_NAME CONSTANT VARCHAR2(30):='BIS_DIMENSION_LEVEL_PVT';
49
50 TYPE bind_variables_tbl_type IS TABLE OF NUMBER
51 INDEX BY BINARY_INTEGER;
52
53 -- private functions
54 FUNCTION isPMFDimensionLevel(p_dim_level_id IN NUMBER) RETURN BOOLEAN ;
55 FUNCTION IS_TARGET_DEFINED( p_dim_level_id IN NUMBER) RETURN BOOLEAN;
56 FUNCTION IS_ASSIGNED_TO_KPI( p_dim_level_id IN NUMBER) RETURN BOOLEAN ;
57 PROCEDURE validate_disabling (p_dim_level_id IN NUMBER
58 , p_error_Tbl IN BIS_UTILITIES_PUB.Error_Tbl_Type
59 , x_return_status OUT NOCOPY VARCHAR2
60 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
61 );
62
63 --
64 --
65
66 PROCEDURE Create_New_Dimension_Level
67 ( p_level_id IN NUMBER, -- l_id
68 p_level_short_name IN VARCHAR2, -- l_Dimension_Rec.Dimension_Short_Name
69 p_dimension_id IN NUMBER,
70 p_level_values_view_name IN VARCHAR2,
71 p_where_clause IN VARCHAR2,
72 p_source IN VARCHAR2,
73 p_created_by IN NUMBER, -- created_by
74 p_last_updated_by IN NUMBER, -- last_updated_by
75 p_login_id IN NUMBER, -- l_login_id
76 p_level_name IN VARCHAR2, -- l_Dimension_Rec.Dimension_Name
77 p_description IN VARCHAR2, -- l_Dimension_Rec.Description
78 p_comparison_label_code IN VARCHAR2,
79 p_attribute_code IN VARCHAR2,
80 p_application_id IN NUMBER := NULL,
81 p_default_search IN VARCHAR2,
82 p_long_lov IN VARCHAR2,
83 p_master_level IN VARCHAR2,
84 p_view_object_name IN VARCHAR2,
85 p_default_values_api IN VARCHAR2,
86 p_enabled IN VARCHAR2,
87 p_drill_to_form_function IN VARCHAR2,
88 p_last_update_date IN DATE := SYSDATE,
89 p_hide IN VARCHAR2 := FND_API.G_FALSE
90 );
91 --
92 -- returns the record with the G_MISS_CHAR/G_MISS_NUM replaced
93 -- by null
94 --
95 PROCEDURE SetNULL
96 ( p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
97 , x_Dimension_Level_Rec OUT NOCOPY BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
98 );
99 --
100 -- queries database to retrieve the dimension level from the database
101 -- updates the record with the changes sent in
102 --
103 PROCEDURE UpdateRecord
104 ( p_Dimension_Level_Rec BIS_Dimension_Level_PUB.Dimension_Level_Rec_Type
105 , x_Dimension_Level_Rec OUT NOCOPY BIS_Dimension_Level_PUB.Dimension_Level_Rec_Type
106 , x_return_status OUT NOCOPY VARCHAR2
107 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
108 );
109 --
110 FUNCTION Is_Level_Name_Used
111 (
112 p_level_name IN VARCHAR2
113 , p_source IN VARCHAR2
114 , p_dimension_id IN NUMBER
115 )
116 RETURN BOOLEAN;
117
118 --==================================================================
119 PROCEDURE retrieve_sql(
120 p_all_dim_levels_tbl IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Tbl_Type
121 ,x_is_bind OUT NOCOPY BOOLEAN
122 ,x_is_execute OUT NOCOPY BOOLEAN
123 ,x_sql OUT NOCOPY VARCHAR2
124 ,x_bind_variables_tbl OUT NOCOPY bind_variables_tbl_type
125 );
126
127 --==================================================================
128 --
129 PROCEDURE SetNULL
130 ( p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
131 , x_Dimension_Level_Rec OUT NOCOPY BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
132 )
133 IS
134 BEGIN
135
136 x_dimension_level_rec.Dimension_ID
137 := BIS_UTILITIES_PVT.CheckMissNum(p_dimension_level_rec.Dimension_ID);
138 x_dimension_level_rec.Dimension_Short_Name
139 := BIS_UTILITIES_PVT.CheckMissChar(
140 p_dimension_level_rec.Dimension_Short_Name);
141 x_dimension_level_rec.Dimension_Name
142 := BIS_UTILITIES_PVT.CheckMissChar(
143 p_dimension_level_rec.Dimension_Name);
144
145 x_dimension_level_rec.Dimension_Level_ID
146 := BIS_UTILITIES_PVT.CheckMissNum(
147 p_dimension_level_rec.Dimension_Level_ID);
148 x_dimension_level_rec.Dimension_Level_Short_Name
149 := BIS_UTILITIES_PVT.CheckMissChar(
150 p_dimension_level_rec.Dimension_Level_Short_Name);
151 x_dimension_level_rec.Dimension_Level_Name
152 := BIS_UTILITIES_PVT.CheckMissChar(
153 p_dimension_level_rec.Dimension_Level_Name);
154 x_dimension_level_rec.Description
155 := BIS_UTILITIES_PVT.CheckMissChar(p_dimension_level_rec.Description);
156 x_dimension_level_rec.Level_Values_View_Name
157 := BIS_UTILITIES_PVT.CheckMissChar(
158 p_dimension_level_rec.Level_Values_View_Name);
159 x_dimension_level_rec.where_Clause
160 := BIS_UTILITIES_PVT.CheckMissChar(p_dimension_level_rec.where_Clause);
161 x_dimension_level_rec.source
162 := BIS_UTILITIES_PVT.CheckMissChar(p_dimension_level_rec.source);
163 --jxyu added for #2435226
164 x_dimension_level_rec.Comparison_Label_Code
165 := BIS_UTILITIES_PVT.CheckMissChar(p_dimension_level_rec.Comparison_Label_Code);
166 x_dimension_level_rec.Attribute_Code
167 := BIS_UTILITIES_PVT.CheckMissChar(p_dimension_level_rec.Attribute_Code);
168 x_dimension_level_rec.Application_ID
169 := BIS_UTILITIES_PVT.CheckMissNum(p_dimension_level_rec.Application_ID);
170 x_dimension_level_rec.default_search
171 := BIS_UTILITIES_PVT.CheckMissChar(p_dimension_level_rec.default_search);
172 x_dimension_level_rec.Long_Lov
173 := BIS_UTILITIES_PVT.CheckMissChar(p_dimension_level_rec.Long_Lov);
174 x_dimension_level_rec.Master_Level
175 := BIS_UTILITIES_PVT.CheckMissChar(p_dimension_level_rec.Master_Level);
176 x_dimension_level_rec.View_Object_Name
177 := BIS_UTILITIES_PVT.CheckMissChar(p_dimension_level_rec.View_Object_Name);
178 x_dimension_level_rec.Default_Values_Api
179 := BIS_UTILITIES_PVT.CheckMissChar(p_dimension_level_rec.Default_Values_Api);
180 x_dimension_level_rec.Enabled
181 := BIS_UTILITIES_PVT.CheckMissChar(p_dimension_level_rec.Enabled);
182 x_dimension_level_rec.Drill_To_Form_Function
183 := BIS_UTILITIES_PVT.CheckMissChar(p_dimension_level_rec.Drill_To_Form_Function);
184 x_dimension_level_rec.Hide
185 := BIS_UTILITIES_PVT.CheckMissChar(p_dimension_level_rec.Hide);
186 x_dimension_level_rec.Created_By := BIS_UTILITIES_PVT.CheckMissNum(p_dimension_level_rec.Created_By);
187 x_dimension_level_rec.Creation_Date := BIS_UTILITIES_PVT.CheckMissDate(p_dimension_level_rec.Creation_Date);
188 x_dimension_level_rec.Last_Updated_By := BIS_UTILITIES_PVT.CheckMissNum(p_dimension_level_rec.Last_Updated_By);
189 x_dimension_level_rec.Last_Update_Date := BIS_UTILITIES_PVT.CheckMissDate(p_dimension_level_rec.Last_Update_Date);
190 x_dimension_level_rec.Last_Update_Login := BIS_UTILITIES_PVT.CheckMissNum(p_dimension_level_rec.Last_Update_Login);
191
192 EXCEPTION
193 WHEN FND_API.G_EXC_ERROR THEN
194 RAISE
195 ;
196 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
197 RAISE;
198 WHEN OTHERS THEN
199 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
200
201 END SetNULL;
202 --
203 PROCEDURE UpdateRecord
204 ( p_Dimension_Level_Rec BIS_Dimension_Level_PUB.Dimension_Level_Rec_Type
205 , x_Dimension_Level_Rec OUT NOCOPY BIS_Dimension_Level_PUB.Dimension_Level_Rec_Type
206 , x_return_status OUT NOCOPY VARCHAR2
207 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
208 )
209 IS
210 --
211 l_Dimension_Level_Rec BIS_Dimension_Level_PUB.Dimension_Level_Rec_Type;
212 l_return_status VARCHAR2(10);
213 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
214 --
215 BEGIN
216
217 -- retrieve record from db
218 BIS_Dimension_Level_PVT.Retrieve_Dimension_Level
219 ( p_api_version => 1.0
220 , p_Dimension_Level_Rec => p_Dimension_Level_Rec
221 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
222 , x_return_status => l_return_status
223 , x_error_Tbl => x_error_Tbl
224 );
225
226 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
227 RAISE FND_API.G_EXC_ERROR;
228 END IF;
229
230 -- apply changes
231
232 -- Primary dimension starts
233 IF( (BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Level_Rec.Dimension_ID) = FND_API.G_TRUE) AND (p_Dimension_Level_Rec.Primary_Dim = FND_API.G_TRUE) ) THEN
234 l_Dimension_Level_Rec.Dimension_ID := p_Dimension_Level_Rec.Dimension_ID;
235 END IF;
236 --
237 IF( (BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Level_Rec.Dimension_Short_Name) = FND_API.G_TRUE) AND(p_Dimension_Level_Rec.Primary_Dim = FND_API.G_TRUE) ) THEN
238 l_Dimension_Level_Rec.Dimension_Short_Name := p_Dimension_Level_Rec.Dimension_Short_Name ;
239 END IF;
240 --
241 IF( (BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Level_Rec.Dimension_Name) = FND_API.G_TRUE) AND (p_Dimension_Level_Rec.Primary_Dim = FND_API.G_TRUE) ) THEN
242 l_Dimension_Level_Rec.Dimension_Name := p_Dimension_Level_Rec.Dimension_Name;
243 END IF;
244 -- Primary dimension ends
245
246 IF( BIS_UTILITIES_PUB.Value_Not_Missing(
247 p_Dimension_Level_Rec.Dimension_Level_ID)
248 = FND_API.G_TRUE
249 ) THEN
250 l_Dimension_Level_Rec.Dimension_Level_ID
251 := p_Dimension_Level_Rec.Dimension_Level_ID;
252 END IF;
253 --
254 IF( BIS_UTILITIES_PUB.Value_Not_Missing(
255 p_Dimension_Level_Rec.Dimension_Level_Short_Name)
256 = FND_API.G_TRUE
257 ) THEN
258 l_Dimension_Level_Rec.Dimension_Level_Short_Name
259 := p_Dimension_Level_Rec.Dimension_Level_Short_Name ;
260 END IF;
261 --
262 IF( BIS_UTILITIES_PUB.Value_Not_Missing(
263 p_Dimension_Level_Rec.Dimension_Level_Name)
264 = FND_API.G_TRUE
265 ) THEN
266 l_Dimension_Level_Rec.Dimension_Level_Name
267 := p_Dimension_Level_Rec.Dimension_Level_Name;
268 END IF;
269 --
270 -- jxyu added AND condition
271 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Level_Rec.Description)
272 = FND_API.G_TRUE)
273 AND (p_Dimension_Level_Rec.Description IS NOT NULL) THEN
274 l_Dimension_Level_Rec.Description
275 := p_Dimension_Level_Rec.Description;
276 END IF;
277 --
278 -- jxyu added AND condition
279 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Level_Rec.Level_Values_View_Name) = FND_API.G_TRUE)
280 AND (p_Dimension_Level_Rec.Level_Values_View_Name IS NOT NULL) THEN
281 l_Dimension_Level_Rec.Level_Values_View_Name
282 := p_Dimension_Level_Rec.Level_Values_View_Name;
283 END IF;
284 --
285 -- jxyu added AND condition
286 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Level_Rec.where_Clause )
287 = FND_API.G_TRUE)
288 AND (p_Dimension_Level_Rec.where_Clause IS NOT NULL) THEN
289 l_Dimension_Level_Rec.where_Clause
290 := p_Dimension_Level_Rec.where_Clause;
291 END IF;
292 --
293 --jxyu modified the condition
294 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Level_Rec.source )
295 = FND_API.G_TRUE)
296 AND (p_Dimension_Level_Rec.source IS NOT NULL) THEN
297 l_Dimension_Level_Rec.source
298 := p_Dimension_Level_Rec.source;
299 END IF;
300
301 --jxyu added for #2435226
302 -- Bug #3266561 -removed condition to default comparision to NULL when passed from UI;
303 l_Dimension_Level_Rec.Comparison_Label_Code := p_Dimension_Level_Rec.Comparison_Label_Code;
304
305 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Level_Rec.Attribute_Code ) = FND_API.G_TRUE)
306 AND (p_Dimension_Level_Rec.Attribute_Code IS NOT NULL) THEN
307 l_Dimension_Level_Rec.Attribute_Code
308 := p_Dimension_Level_Rec.Attribute_Code;
309 END IF;
310
311 IF(BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Level_Rec.Application_ID) = FND_API.G_TRUE)
312 AND (p_Dimension_Level_Rec.Application_ID IS NOT NULL) THEN
313 l_Dimension_Level_Rec.Application_ID := p_Dimension_Level_Rec.Application_ID;
314 END IF;
315
316 -- Bug #3266561 -removed condition to default default_search to NULL when passed from UI;
317 l_Dimension_Level_Rec.default_search := p_Dimension_Level_Rec.Default_Search;
318
319 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Level_Rec.Long_Lov ) = FND_API.G_TRUE)
320 AND (p_Dimension_Level_Rec.Long_Lov IS NOT NULL) THEN
321 l_Dimension_Level_Rec.Long_Lov
322 := p_Dimension_Level_Rec.Long_Lov;
323 END IF;
324
325 l_Dimension_Level_Rec.Master_Level := p_Dimension_Level_Rec.Master_Level;
326
327
328 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Level_Rec.View_Object_Name ) = FND_API.G_TRUE)
329 AND (p_Dimension_Level_Rec.View_Object_Name IS NOT NULL) THEN
330 l_Dimension_Level_Rec.View_Object_Name
331 := p_Dimension_Level_Rec.View_Object_Name;
332 END IF;
333
334 -- Bug #3567463 -removed condition to default Default_Values_Api to NULL when passed from UI;
335 l_Dimension_Level_Rec.Default_Values_Api := p_Dimension_Level_Rec.Default_Values_Api;
336
337 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Level_Rec.Enabled ) = FND_API.G_TRUE)
338 AND (p_Dimension_Level_Rec.Enabled IS NOT NULL) THEN
339 l_Dimension_Level_Rec.Enabled
340 := p_Dimension_Level_Rec.Enabled;
341 END IF;
342
343 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Level_Rec.Hide ) = FND_API.G_TRUE)
344 AND (p_Dimension_Level_Rec.Hide IS NOT NULL) THEN
345 l_Dimension_Level_Rec.Hide := p_Dimension_Level_Rec.Hide;
346 END IF;
347
348 l_Dimension_Level_Rec.Drill_To_Form_Function := p_Dimension_Level_Rec.Drill_To_Form_Function;
349
350 x_Dimension_Level_Rec := l_Dimension_Level_Rec;
351 --
352 --commented RAISE
353 EXCEPTION
354 WHEN FND_API.G_EXC_ERROR THEN
355 x_return_status:= FND_API.G_RET_STS_ERROR;
356 --RAISE;
357 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
358 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
359 --RAISE;
360 WHEN OTHERS THEN
361 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
362 l_error_tbl := x_error_tbl;
363 BIS_UTILITIES_PVT.Add_Error_Message
364 ( p_error_table => l_error_Tbl
365 , p_error_msg_id => SQLCODE
366 , p_error_description => SQLERRM
367 , x_error_table => x_error_Tbl
368 );
369 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
370
371 END UpdateRecord;
372 --
373 --
374 Procedure Retrieve_Dimension_Levels
375 ( p_api_version IN NUMBER
376 , p_Dimension_Rec IN BIS_DIMENSION_PUB.Dimension_Rec_Type
377 , x_Dimension_Level_Tbl OUT NOCOPY BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Tbl_Type
378 , x_return_status OUT NOCOPY VARCHAR2
379 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
380 )
381 IS
382 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
383 l_dimension_rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
384 l_dim_level_rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
385 --flag to check if there is an error
386 l_flag NUMBER :=0;
387 cursor cr_dim_id is
388 select dimension_id
389 , dimension_short_name
390 , dimension_name
391 , dimension_level_id
392 , dimension_level_short_name
393 , dimension_level_name
394 , description
395 , Level_Values_View_Name
396 , where_clause
397 , source
398 , comparison_label_code
399 , attribute_code
400 , application_id
401 , default_search
402 , long_lov
403 , master_level
404 , view_object_name
405 , default_values_api
406 , enabled
407 , drill_to_form_function
408 , hide_in_design
409 from bisfv_dimension_levels
410 where dimension_id = p_Dimension_Rec.dimension_id;
411
412 cursor cr_dim_short_name is
413 select dimension_id
414 , dimension_short_name
415 , dimension_name
416 , dimension_level_id
417 , dimension_level_short_name
418 , dimension_level_name
419 , description
420 , Level_Values_View_Name
421 , where_clause
422 , source
423 , comparison_label_code
424 , attribute_code
425 , application_id
426 , default_search
427 , long_lov
428 , master_level
429 , view_object_name
430 , default_values_api
431 , enabled
432 , drill_to_form_function
433 , hide_in_design
434 from bisfv_dimension_levels
435 where dimension_short_name = p_Dimension_Rec.dimension_short_name;
436
437 cursor cr_dim_name is
438 select dimension_id
439 , dimension_short_name
440 , dimension_name
441 , dimension_level_id
442 , dimension_level_short_name
443 , dimension_level_name
444 , description
445 , Level_Values_View_Name
446 , where_clause
447 , source
448 , comparison_label_code
449 , attribute_code
450 , application_id
451 , default_search
452 , long_lov
453 , master_level
454 , view_object_name
455 , default_values_api
456 , enabled
457 , drill_to_form_function
458 , hide_in_design
459 from bisfv_dimension_levels
460 where dimension_name = p_Dimension_Rec.dimension_name;
461
462 BEGIN
463
464 x_return_status := FND_API.G_RET_STS_SUCCESS;
465
466 IF BIS_UTILITIES_PUB.Value_Not_Missing(
467 p_Dimension_Rec.dimension_id)
468 = FND_API.G_TRUE
469 THEN
470
471 for cr in cr_dim_id loop
472 l_flag := 1;
473 l_dim_level_rec.dimension_id := cr.dimension_id;
474 l_dim_level_rec.dimension_short_name := cr.dimension_short_name;
475 l_dim_level_rec.dimension_name := cr.dimension_name;
476
477 l_dim_level_rec.dimension_level_id := cr.dimension_level_id;
478 l_dim_level_rec.dimension_level_short_name := cr.dimension_level_short_name;
479 l_dim_level_rec.dimension_level_name := cr.dimension_level_name;
480 l_dim_level_rec.description := cr.description;
481 l_dim_level_rec.level_values_view_name := cr.level_values_view_name;
482 l_dim_level_rec.where_clause := cr.where_clause;
483 l_dim_level_rec.source := cr.source;
484 l_dim_level_rec.comparison_label_code := cr.comparison_label_code;
485 l_dim_level_rec.attribute_code := cr.attribute_code;
486 l_dim_level_rec.application_id := cr.application_id;
487
488 l_dim_level_rec.default_search := cr.default_search;
489 l_dim_level_rec.long_lov := cr.long_lov;
490 l_dim_level_rec.master_level := cr.master_level;
491
492 l_dim_level_rec.view_object_name := cr.view_object_name;
493 l_dim_level_rec.default_values_api := cr.default_values_api;
494 l_dim_level_rec.enabled := cr.enabled;
495 l_dim_level_rec.hide := cr.hide_in_design;
496 l_dim_level_rec.drill_to_form_function := cr.drill_to_form_function;
497 x_dimension_level_tbl(x_dimension_level_tbl.count+1) := l_dim_level_rec;
498
499 end loop;
500
501 ELSIF BIS_UTILITIES_PUB.Value_Not_Missing(
502 p_Dimension_Rec.dimension_short_name)
503 = FND_API.G_TRUE
504 THEN
505
506 for cr in cr_dim_short_name loop
507 l_flag := 1;
508 l_dim_level_rec.dimension_id := cr.dimension_id;
509 l_dim_level_rec.dimension_short_name := cr.dimension_short_name;
510 l_dim_level_rec.dimension_name := cr.dimension_name;
511
512 l_dim_level_rec.dimension_level_id := cr.dimension_level_id;
513 l_dim_level_rec.dimension_level_short_name := cr.dimension_level_short_name;
514 l_dim_level_rec.dimension_level_name := cr.dimension_level_name;
515 l_dim_level_rec.description := cr.description;
516 l_dim_level_rec.level_values_view_name := cr.level_values_view_name;
517 l_dim_level_rec.where_clause := cr.where_clause;
518 l_dim_level_rec.source := cr.source;
519 l_dim_level_rec.comparison_label_code := cr.comparison_label_code;
520 l_dim_level_rec.attribute_code := cr.attribute_code;
521 l_dim_level_rec.application_id := cr.application_id;
522 l_dim_level_rec.default_search := cr.default_search;
523 l_dim_level_rec.long_lov := cr.long_lov;
524 l_dim_level_rec.master_level := cr.master_level;
525 l_dim_level_rec.view_object_name := cr.view_object_name;
526 l_dim_level_rec.default_values_api := cr.default_values_api;
527 l_dim_level_rec.enabled := cr.enabled;
528 l_dim_level_rec.hide := cr.hide_in_design;
529 l_dim_level_rec.drill_to_form_function := cr.drill_to_form_function;
530
531 x_dimension_level_tbl(x_dimension_level_tbl.count+1) := l_dim_level_rec;
532
533 end loop;
534 ELSIF BIS_UTILITIES_PUB.Value_Not_Missing(
535 p_Dimension_Rec.dimension_name)
536 = FND_API.G_TRUE
537 THEN
538
539 for cr in cr_dim_name loop
540 l_flag := 1;
541 l_dim_level_rec.dimension_id := cr.dimension_id;
542 l_dim_level_rec.dimension_short_name := cr.dimension_short_name;
543 l_dim_level_rec.dimension_name := cr.dimension_name;
544
545 l_dim_level_rec.dimension_level_id := cr.dimension_level_id;
546 l_dim_level_rec.dimension_level_short_name := cr.dimension_level_short_name;
547 l_dim_level_rec.dimension_level_name := cr.dimension_level_name;
548 l_dim_level_rec.description := cr.description;
549 l_dim_level_rec.level_values_view_name := cr.level_values_view_name;
550 l_dim_level_rec.where_clause := cr.where_clause;
551 l_dim_level_rec.source := cr.source;
552 l_dim_level_rec.comparison_label_code := cr.comparison_label_code;
553 l_dim_level_rec.attribute_code := cr.attribute_code;
554 l_dim_level_rec.application_id := cr.application_id;
555 l_dim_level_rec.default_search := cr.default_search;
556 l_dim_level_rec.long_lov := cr.long_lov;
557 l_dim_level_rec.master_level := cr.master_level;
558 l_dim_level_rec.view_object_name := cr.view_object_name;
559 l_dim_level_rec.default_values_api := cr.default_values_api;
560 l_dim_level_rec.enabled := cr.enabled;
561 l_dim_level_rec.hide := cr.hide_in_design;
562 l_dim_level_rec.drill_to_form_function := cr.drill_to_form_function;
563
564 x_dimension_level_tbl(x_dimension_level_tbl.count+1) := l_dim_level_rec;
565
566 end loop;
567
568 ELSE
569 --added Add Error Message
570 l_error_tbl := x_error_tbl;
571 BIS_UTILITIES_PVT.Add_Error_Message
572 ( p_error_msg_name => 'BIS_INVALID_DIMENSION_VALUE'
573 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
574 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Dimension_Levels'
575 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
576 , p_error_table => l_error_tbl
577 , x_error_table => x_error_tbl
578 );
579
580 RAISE FND_API.G_EXC_ERROR;
581 END IF;
582
583 --added this check
584 IF l_flag = 0 then
585 l_error_tbl := x_error_tbl;
586 BIS_UTILITIES_PVT.Add_Error_Message
587 ( p_error_msg_name => 'BIS_INVALID_DIMENSION_VALUE'
588 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
589 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Dimension_Levels'
590 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
591 , p_error_table => l_error_tbl
592 , x_error_table => x_error_tbl
593 );
594
595 RAISE FND_API.G_EXC_ERROR;
596 END IF;
597
598 --commented RAISE
599 EXCEPTION
600 WHEN NO_DATA_FOUND THEN
601 x_return_status := FND_API.G_RET_STS_ERROR ;
602 --RAISE FND_API.G_EXC_ERROR;
603 when FND_API.G_EXC_ERROR then
604 x_return_status := FND_API.G_RET_STS_ERROR ;
605 -- RAISE FND_API.G_EXC_ERROR;
606 when FND_API.G_EXC_UNEXPECTED_ERROR then
607 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
608 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
609 when others then
610 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
611 l_error_tbl := x_error_tbl;
612 --added last two parameters
613 BIS_UTILITIES_PVT.Add_Error_Message
614 ( p_error_msg_id => SQLCODE
615 , p_error_description => SQLERRM
616 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Dimension_Levels'
617 , p_error_table => l_error_tbl
618 , x_error_table => x_error_tbl
619 );
620 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
621
622
623 END Retrieve_Dimension_Levels;
624 --
625 Procedure Retrieve_Dimension_Level
626 ( p_api_version IN NUMBER
627 , p_Dimension_level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
628 , x_Dimension_level_Rec IN OUT NOCOPY BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
629 , x_return_status OUT NOCOPY VARCHAR2
630 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
631 )
632 IS
633 l_dim_id NUMBER;
634 l_dim_short_name varchar2(30);
635 l_dim_name varchar2(80);
636 l_dimension_rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
637 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
638 cursor cr_lev_id is
639 select dimension_id
640 , dimension_short_name
641 , dimension_name
642 , dimension_level_id
643 , dimension_level_short_name
644 , dimension_level_name
645 , description
646 , Level_Values_View_Name
647 , where_clause
648 , source
649 , comparison_label_code
650 , attribute_code
651 , application_id
652 , default_search
653 , long_lov
654 , master_level
655 , view_object_name
656 , default_values_api
657 , enabled
658 , drill_to_form_function
659 , hide_in_design
660 from bisfv_dimension_levels
661 where dimension_level_id = p_Dimension_level_Rec.dimension_level_id;
662
663 cursor cr_lev_short_name is
664 select dimension_id
665 , dimension_short_name
666 , dimension_name
667 , dimension_level_id
668 , dimension_level_short_name
669 , dimension_level_name
670 , description
671 , Level_Values_View_Name
672 , where_clause
673 , source
674 , comparison_label_code
675 , attribute_code
676 , application_id
677 , default_search
678 , long_lov
679 , master_level
680 , view_object_name
681 , default_values_api
682 , enabled
683 , drill_to_form_function
684 , hide_in_design
685 from bisfv_dimension_levels
686 where dimension_level_short_name
687 = p_Dimension_level_Rec.dimension_level_short_name;
688
689 cursor cr_lev_name is
690 select dimension_id
691 , dimension_short_name
692 , dimension_name
693 , dimension_level_id
694 , dimension_level_short_name
695 , dimension_level_name
696 , description
697 , Level_Values_View_Name
698 , where_clause
699 , source
700 , comparison_label_code
701 , attribute_code
702 , application_id
703 , default_search
704 , long_lov
705 , master_level
706 , view_object_name
707 , default_values_api
708 , enabled
709 , drill_to_form_function
710 , hide_in_design
711 from bisfv_dimension_levels
712 where dimension_level_name = p_Dimension_level_Rec.dimension_level_name;
713
714 BEGIN
715
716 x_return_status := FND_API.G_RET_STS_SUCCESS;
717
718 IF BIS_UTILITIES_PUB.Value_Not_Missing(
719 p_Dimension_level_Rec.dimension_level_id)
720 = FND_API.G_TRUE
721 THEN
722 OPEN cr_lev_id;
723 FETCH cr_lev_id INTO
724 x_Dimension_level_Rec.dimension_id
725 , x_Dimension_level_Rec.dimension_short_name
726 , x_Dimension_level_Rec.dimension_name
727 , x_Dimension_level_Rec.dimension_level_id
728 , x_Dimension_level_Rec.dimension_level_short_name
729 , x_Dimension_level_Rec.dimension_level_name
730 , x_Dimension_level_Rec.description
731 , x_dimension_level_rec.level_values_view_name
732 , x_dimension_level_rec.where_clause
733 , x_dimension_level_rec.source
734 , x_dimension_level_rec.comparison_label_code
735 , x_dimension_level_rec.attribute_code
736 , x_Dimension_level_Rec.application_id
737 , x_Dimension_level_Rec.default_search
738 , x_Dimension_level_Rec.long_lov
739 , x_Dimension_level_Rec.master_level
740 , x_Dimension_level_Rec.view_object_name
741 , x_Dimension_level_Rec.default_values_api
742 , x_Dimension_level_Rec.enabled
743 , x_Dimension_level_Rec.drill_to_form_function
744 , x_Dimension_level_Rec.hide;
745 --
746 IF cr_lev_id%ROWCOUNT = 0 THEN
747 x_return_status := FND_API.G_RET_STS_ERROR;
748 END IF;
749 CLOSE cr_lev_id;
750
751 ELSIF BIS_UTILITIES_PUB.Value_Not_Missing(
752 p_Dimension_level_Rec.dimension_level_short_name)
753 = FND_API.G_TRUE
754 THEN
755 OPEN cr_lev_short_name;
756 FETCH cr_lev_short_name INTO
757 x_Dimension_level_Rec.dimension_id
758 , x_Dimension_level_Rec.dimension_short_name
759 , x_Dimension_level_Rec.dimension_name
760 , x_Dimension_level_Rec.dimension_level_id
761 , x_Dimension_level_Rec.dimension_level_short_name
762 , x_Dimension_level_Rec.dimension_level_name
763 , x_Dimension_level_Rec.description
764 , x_dimension_level_rec.level_values_view_name
765 , x_dimension_level_rec.where_clause
766 , x_dimension_level_rec.source
767 , x_dimension_level_rec.comparison_label_code
768 , x_dimension_level_rec.attribute_code
769 , x_Dimension_level_Rec.application_id
770 , x_Dimension_level_Rec.default_search
771 , x_Dimension_level_Rec.long_lov
772 , x_Dimension_level_Rec.master_level
773 , x_Dimension_level_Rec.view_object_name
774 , x_Dimension_level_Rec.default_values_api
775 , x_Dimension_level_Rec.enabled
776 , x_Dimension_level_Rec.drill_to_form_function
777 , x_Dimension_level_Rec.hide;
778 --
779 IF cr_lev_short_name%ROWCOUNT = 0 THEN
780 x_return_status := FND_API.G_RET_STS_ERROR;
781 END IF;
782 CLOSE cr_lev_short_name;
783
784 ELSIF BIS_UTILITIES_PUB.Value_Not_Missing(
785 p_Dimension_level_Rec.dimension_level_name)
786 = FND_API.G_TRUE
787 THEN
788 OPEN cr_lev_name;
789 FETCH cr_lev_name INTO
790 x_Dimension_level_Rec.dimension_id
791 , x_Dimension_level_Rec.dimension_short_name
792 , x_Dimension_level_Rec.dimension_name
793 , x_Dimension_level_Rec.dimension_level_id
794 , x_Dimension_level_Rec.dimension_level_short_name
795 , x_Dimension_level_Rec.dimension_level_name
796 , x_Dimension_level_Rec.description
797 , x_dimension_level_rec.level_values_view_name
798 , x_dimension_level_rec.where_clause
799 , x_dimension_level_rec.source
800 , x_dimension_level_rec.comparison_label_code
801 , x_dimension_level_rec.attribute_code
802 , x_Dimension_level_Rec.application_id
803 , x_Dimension_level_Rec.default_search
804 , x_Dimension_level_Rec.long_lov
805 , x_Dimension_level_Rec.master_level
806 , x_Dimension_level_Rec.view_object_name
807 , x_Dimension_level_Rec.default_values_api
808 , x_Dimension_level_Rec.enabled
809 , x_Dimension_level_Rec.drill_to_form_function
810 , x_Dimension_level_Rec.hide;
811 --
812 IF cr_lev_name%ROWCOUNT = 0 THEN
813 x_return_status := FND_API.G_RET_STS_ERROR;
814 END IF;
815 CLOSE cr_lev_name;
816 ELSE
817 l_error_tbl := x_error_tbl;
818 --added Error Msg
819 BIS_UTILITIES_PVT.Add_Error_Message
820 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_VALUE'
821 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
822 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Dimension_Level'
823 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
824 , p_error_table => l_error_tbl
825 , x_error_table => x_error_tbl
826 );
827 RAISE FND_API.G_EXC_ERROR;
828 END IF;
829
830 --added this check
831 IF x_return_status <> FND_API.G_RET_STS_SUCCESS then
832 l_error_tbl := x_error_tbl;
833 BIS_UTILITIES_PVT.Add_Error_Message
834 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_VALUE'
835 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
836 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Dimension_Level'
837 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
838 , p_error_table => l_error_tbl
839 , x_error_table => x_error_tbl
840 );
841 RAISE FND_API.G_EXC_ERROR;
842 END IF;
843
844 --commented RAISE
845 EXCEPTION
846 WHEN NO_DATA_FOUND THEN
847 x_return_status := FND_API.G_RET_STS_ERROR ;
848 --RAISE FND_API.G_EXC_ERROR;
849 when FND_API.G_EXC_ERROR then
850 x_return_status := FND_API.G_RET_STS_ERROR ;
851 --RAISE FND_API.G_EXC_ERROR;
852 when FND_API.G_EXC_UNEXPECTED_ERROR then
853 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
854 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
855 when others then
856 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
857 l_error_tbl := x_error_tbl;
858 --added last two parameters
859 BIS_UTILITIES_PVT.Add_Error_Message
860 ( p_error_msg_id => SQLCODE
861 , p_error_description => SQLERRM
862 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Dimension_Level'
863 , p_error_table => l_error_tbl
864 , x_error_table => x_error_tbl
865 );
866 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
867
868
869 END Retrieve_Dimension_Level;
870
871 --===================================================================
872 -- p_all_dim_levels_tbl contains different dimension level ids.
873 -- x_all_dim_levels_tbl contains all records for the input dimension
874 -- level ids from bisfv_dimension_levels.
875 -- The output plsql table will be indexed by dimension level id
876
877 PROCEDURE retrieve_mult_dim_levels(
878 p_api_version IN NUMBER
879 ,p_all_dim_levels_tbl IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Tbl_Type
880 ,x_all_dim_levels_tbl OUT NOCOPY BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Tbl_Type
881 ,x_return_status OUT NOCOPY VARCHAR2
882 ,x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
883 )
884 IS
885 TYPE ref_cursor_type IS REF CURSOR;
886 c_dim_level_details ref_cursor_type;
887
888 l_dimension_level_rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
889 l_bind_var_tbl bind_variables_tbl_type;
890 l_sql VARCHAR2(32000);
891 l_is_bind BOOLEAN := FALSE;
892 l_is_execute BOOLEAN := FALSE;
893 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
894
895 BEGIN
896
897 x_return_status := FND_API.G_RET_STS_SUCCESS;
898
899 IF (p_all_dim_levels_tbl.COUNT = 0) THEN
900 RETURN;
901 END IF;
902
903 retrieve_sql(
904 p_all_dim_levels_tbl => p_all_dim_levels_tbl
905 ,x_is_bind => l_is_bind
906 ,x_is_execute => l_is_execute
907 ,x_sql => l_sql
908 ,x_bind_variables_tbl => l_bind_var_tbl
909 );
910
911 IF ( (l_is_execute) AND (l_sql IS NOT NULL) ) THEN
912
913 IF (c_dim_level_details%ISOPEN) THEN
914 close c_dim_level_details;
915 END IF;
916
917 IF (l_is_bind) THEN
918 OPEN c_dim_level_details FOR l_sql USING l_bind_var_tbl(1), l_bind_var_tbl(2), l_bind_var_tbl(3), l_bind_var_tbl(4),
919 l_bind_var_tbl(5), l_bind_var_tbl(6), l_bind_var_tbl(7), l_bind_var_tbl(8), l_bind_var_tbl(9), l_bind_var_tbl(10);
920 ELSE
921 OPEN c_dim_level_details FOR l_sql;
922 END IF;
923
924 LOOP
925
926 FETCH c_dim_level_details INTO
927 l_dimension_level_rec.dimension_id
928 , l_dimension_level_rec.dimension_short_name
929 , l_dimension_level_rec.dimension_name
930 , l_dimension_level_rec.dimension_level_id
931 , l_dimension_level_rec.dimension_level_short_name
932 , l_dimension_level_rec.dimension_level_name
933 , l_dimension_level_rec.description
934 , l_dimension_level_rec.level_values_view_name
935 , l_dimension_level_rec.where_clause
936 , l_dimension_level_rec.source
937 , l_dimension_level_rec.comparison_label_code
938 , l_dimension_level_rec.attribute_code
939 , l_Dimension_level_Rec.application_id
940 , l_Dimension_level_Rec.default_search
941 , l_Dimension_level_Rec.long_lov
942 , l_Dimension_level_Rec.master_level
943 , l_Dimension_level_Rec.view_object_name
944 , l_Dimension_level_Rec.default_values_api
945 , l_Dimension_level_Rec.enabled
946 , l_Dimension_level_Rec.drill_to_form_function
947 , l_Dimension_level_Rec.hide;
948
949 EXIT WHEN c_dim_level_details%NOTFOUND;
950
951 x_all_dim_levels_tbl(l_dimension_level_rec.dimension_level_id) := l_dimension_level_rec;
952
953 END LOOP;
954 CLOSE c_dim_level_details;
955
956 END IF; -- end of execution
957
958 EXCEPTION
959 WHEN OTHERS THEN
960 IF (c_dim_level_details%ISOPEN) THEN
961 CLOSE c_dim_level_details;
962 END IF;
963
964 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
965 l_error_tbl := x_error_tbl;
966 BIS_UTILITIES_PVT.Add_Error_Message(
967 p_error_msg_id => SQLCODE
968 , p_error_description => SQLERRM
969 , p_error_proc_name => G_PKG_NAME||'.retrieve_multiple_dim_levels'
970 , p_error_table => l_error_tbl
971 , x_error_table => x_error_tbl
972 );
973 END retrieve_mult_dim_levels;
974
975 --====================================================================
976
977 PROCEDURE retrieve_sql(
978 p_all_dim_levels_tbl IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Tbl_Type
979 ,x_is_bind OUT NOCOPY BOOLEAN
980 ,x_is_execute OUT NOCOPY BOOLEAN
981 ,x_sql OUT NOCOPY VARCHAR2
982 ,x_bind_variables_tbl OUT NOCOPY bind_variables_tbl_type
983 )
984 IS
985 l_all_dim_level_ids_lit VARCHAR2(32000);
986 l_index NUMBER;
987 l_tbl_index NUMBER;
988
989 BEGIN
990
991 x_is_execute := FALSE;
992
993 x_sql := ' SELECT dimension_id
994 , dimension_short_name
995 , dimension_name
996 , dimension_level_id
997 , dimension_level_short_name
998 , dimension_level_name
999 , description
1000 , Level_Values_View_Name
1001 , where_clause
1002 , source
1003 , comparison_label_code
1004 , attribute_code
1005 , application_id
1006 , default_search
1007 , long_lov
1008 , master_level
1009 , view_object_name
1010 , default_values_api
1011 , enabled
1012 , drill_to_form_function
1013 , hide_in_design
1014 FROM bisfv_dimension_levels
1015 WHERE dimension_level_id IN (';
1016
1017 IF (p_all_dim_levels_tbl.COUNT <= 10) THEN -- lesser than 10 use bind variables
1018 l_index := 1;
1019 x_is_bind := TRUE;
1020 -- The input and returned plsql table will be indexed by dimension level id to avoid loops
1021 l_tbl_index := p_all_dim_levels_tbl.FIRST;
1022 WHILE l_tbl_index IS NOT NULL LOOP
1023 IF (p_all_dim_levels_tbl(l_tbl_index).dimension_level_id IS NOT NULL) THEN
1024 x_is_execute := TRUE;
1025 x_bind_variables_tbl(l_index) := p_all_dim_levels_tbl(l_tbl_index).dimension_level_id;
1026 ELSE
1027 x_bind_variables_tbl(l_index) := NULL;
1028 END IF;
1029 l_index := l_index + 1;
1030 l_tbl_index := p_all_dim_levels_tbl.NEXT(l_tbl_index);
1031 END LOOP;
1032
1033 FOR i IN l_index .. 10 LOOP
1034 x_bind_variables_tbl(i) := NULL;
1035 END LOOP;
1036
1037 x_sql := x_sql || ':1, :2, :3, :4, :5, :6, :7, :8, :9, :10)';
1038
1039 ELSE -- If more than 10, then use literals.
1040 l_tbl_index := p_all_dim_levels_tbl.FIRST;
1041 WHILE l_tbl_index IS NOT NULL LOOP
1042 IF (p_all_dim_levels_tbl(l_tbl_index).dimension_level_id IS NOT NULL) THEN
1043 x_is_execute := TRUE;
1044 IF (l_all_dim_level_ids_lit IS NOT NULL) THEN
1045 l_all_dim_level_ids_lit := l_all_dim_level_ids_lit || ', ''' || p_all_dim_levels_tbl(l_tbl_index).dimension_level_id || '''';
1046 ELSE
1047 l_all_dim_level_ids_lit := '''' || p_all_dim_levels_tbl(l_tbl_index).dimension_level_id || '''';
1048 END IF;
1049 END IF;
1050 l_tbl_index := p_all_dim_levels_tbl.NEXT(l_tbl_index);
1051 END LOOP;
1052
1053 x_sql := x_sql || l_all_dim_level_ids_lit || ')';
1054
1055 END IF;
1056
1057 EXCEPTION
1058 WHEN OTHERS THEN
1059 x_is_execute := FALSE;
1060 RETURN;
1061 END retrieve_sql;
1062
1063 --=====================================================================
1064
1065 --
1066 Procedure Create_Dimension_Level
1067 ( p_api_version IN NUMBER
1068 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1069 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1070 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
1071 , x_return_status OUT NOCOPY VARCHAR2
1072 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1073 )
1074 IS
1075 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1076 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
1077 BEGIN
1078
1079
1080 l_Dimension_Level_Rec := p_Dimension_Level_Rec;
1081 l_Dimension_Level_Rec.Last_Update_Date := NVL(p_Dimension_Level_Rec.Last_Update_Date, SYSDATE);
1082 Create_Dimension_Level
1083 ( p_api_version => p_api_version
1084 , p_commit => p_commit
1085 , p_validation_level => p_validation_level
1086 , p_Dimension_Level_Rec => l_Dimension_Level_Rec
1087 , p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
1088 , x_return_status => x_return_status
1089 , x_error_Tbl => x_error_Tbl
1090 );
1091
1092 --commented RAISE
1093 EXCEPTION
1094 when others then
1095 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1096 l_error_tbl := x_error_tbl;
1097 --added last two parameters
1098 BIS_UTILITIES_PVT.Add_Error_Message
1099 ( p_error_msg_id => SQLCODE
1100 , p_error_description => SQLERRM
1101 , p_error_proc_name => G_PKG_NAME||'.Create_Dimension_Level'
1102 , p_error_table => l_error_tbl
1103 , x_error_table => x_error_tbl
1104 );
1105 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1106
1107 END Create_Dimension_Level;
1108 --
1109 PROCEDURE Create_Dimension_Level
1110 ( p_api_version IN NUMBER
1111 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1112 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1113 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
1114 , p_owner IN VARCHAR2
1115 , x_return_status OUT NOCOPY VARCHAR2
1116 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1117 )
1118 IS
1119 l_user_id NUMBER;
1120 l_login_id NUMBER;
1121 l_id NUMBER;
1122 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
1123 l_level_name_is_used BOOLEAN;
1124 l_msg VARCHAR2(1000);
1125 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1126
1127 DUPLICATE_DIMENSION_VALUE EXCEPTION;
1128 PRAGMA EXCEPTION_INIT(DUPLICATE_DIMENSION_VALUE, -1);
1129
1130 BEGIN
1131
1132 x_return_status := FND_API.G_RET_STS_SUCCESS;
1133
1134 SetNULL
1135 ( p_dimension_level_Rec => p_dimension_level_Rec
1136 , x_dimension_level_Rec => l_dimension_level_Rec
1137 );
1138
1139 IF (BIS_UTILITIES_PUB.Value_Missing(l_Dimension_Level_Rec.Dimension_id) = FND_API.G_TRUE )
1140 OR (BIS_UTILITIES_PUB.Value_NULL(l_Dimension_Level_Rec.Dimension_id) = FND_API.G_TRUE )
1141 THEN
1142
1143 BIS_DIMENSION_PVT.Value_ID_Conversion
1144 ( p_api_version
1145 , l_Dimension_Level_Rec.Dimension_Short_Name
1146 , l_Dimension_Level_Rec.Dimension_Name
1147 , l_Dimension_Level_Rec.Dimension_ID
1148 , x_return_status
1149 , x_error_Tbl
1150 );
1151
1152 END IF;
1153
1154
1155 Validate_Dimension_Level
1156 ( p_api_version
1157 , p_validation_level
1158 , l_Dimension_Level_Rec
1159 , x_return_status
1160 , x_error_Tbl
1161 );
1162
1163
1164 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1165 l_error_tbl := x_error_tbl;
1166 BIS_UTILITIES_PVT.Add_Error_Message
1167 ( p_error_msg_name => 'BIS_NAME_SHORT_NAME_MISSING'
1168 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1169 , p_error_proc_name => G_PKG_NAME||'.Create_Dimension_Level'
1170 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
1171 , p_error_table => l_error_tbl
1172 , x_error_table => x_error_tbl
1173 );
1174
1175 RAISE FND_API.G_EXC_ERROR;
1176
1177 END IF;
1178
1179 -- ankgoel: bug#3891748 - Created_By will take precedence over Owner.
1180 -- Last_Updated_By can be different from Created_By while creating dim levels
1181 -- during sync-up
1182 IF (l_Dimension_Level_Rec.Created_By IS NULL) THEN
1183 l_Dimension_Level_Rec.Created_By := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
1184 END IF;
1185 IF (l_Dimension_Level_Rec.Last_Updated_By IS NULL) THEN
1186 l_Dimension_Level_Rec.Last_Updated_By := l_Dimension_Level_Rec.Created_By;
1187 END IF;
1188 IF (l_Dimension_Level_Rec.Last_Update_Login IS NULL) THEN
1189 l_Dimension_Level_Rec.Last_Update_Login := fnd_global.LOGIN_ID;
1190 END IF;
1191
1192 IF (BIS_UTILITIES_PUB.Value_Missing(l_Dimension_Level_Rec.source)
1193 = FND_API.G_TRUE)
1194 OR (BIS_UTILITIES_PUB.Value_NULL(l_Dimension_Level_Rec.source)
1195 = FND_API.G_TRUE)
1196 THEN
1197 l_Dimension_Level_Rec.source := FND_PROFILE.value('BIS_SOURCE');
1198 END IF;
1199
1200
1201
1202 l_level_name_is_used := Is_Level_Name_Used
1203 (
1204 p_level_name => l_Dimension_Level_Rec.Dimension_Level_Name
1205 , p_source => l_Dimension_Level_Rec.source
1206 , p_dimension_id => l_Dimension_Level_Rec.Dimension_ID
1207 );
1208
1209
1210 IF ( l_level_name_is_used = FALSE ) THEN
1211
1212 select bis_levels_s.NextVal into l_id from dual;
1213
1214 l_Dimension_Level_Rec.Last_Update_Date := NVL(p_Dimension_Level_Rec.Last_Update_Date, SYSDATE);
1215
1216 Create_New_Dimension_Level
1217 ( p_level_id => l_id
1218 , p_level_short_name => l_Dimension_Level_Rec.Dimension_Level_Short_Name
1219 , p_dimension_id => l_Dimension_Level_Rec.Dimension_ID
1220 , p_level_values_view_name => l_Dimension_Level_Rec.Level_Values_View_Name
1221 , p_where_clause => l_Dimension_Level_Rec.Where_Clause
1222 , p_source => l_Dimension_Level_Rec.Source
1223 , p_created_by => l_Dimension_Level_Rec.Created_By
1224 , p_last_updated_by => l_Dimension_Level_Rec.Last_Updated_By
1225 , p_login_id => l_Dimension_Level_Rec.Last_Update_Login
1226 , p_level_name => l_Dimension_Level_Rec.Dimension_Level_Name
1227 , p_description => l_Dimension_Level_Rec.Description
1228 , p_comparison_label_code => l_Dimension_Level_Rec.Comparison_Label_Code
1229 , p_attribute_code => l_Dimension_Level_Rec.attribute_code
1230 , p_application_id => l_Dimension_Level_Rec.Application_Id
1231 , p_default_search => l_Dimension_Level_Rec.default_search
1232 , p_long_lov => l_Dimension_Level_Rec.long_lov
1233 , p_master_level => l_Dimension_Level_Rec.master_level
1234 , p_view_object_name => l_Dimension_Level_Rec.view_object_name
1235 , p_default_values_api => l_Dimension_Level_Rec.default_values_api
1236 , p_enabled => l_Dimension_Level_Rec.enabled
1237 , p_drill_to_form_function => l_DImension_Level_Rec.Drill_To_Form_Function
1238 , p_hide => l_Dimension_Level_Rec.Hide
1239 , p_last_update_date => l_Dimension_Level_Rec.Last_Update_Date
1240 );
1241
1242 if (p_commit = FND_API.G_TRUE) then
1243 COMMIT;
1244 end if;
1245
1246 ELSE
1247
1248 /*
1249 fnd_message.set_name('BIS', 'BIS_LVL_UPLD_FAIL');
1250 fnd_message.set_token('SHORT_NAME', l_Dimension_Level_Rec.Dimension_Level_Short_Name);
1251 fnd_message.set_token('NAME', l_Dimension_Level_Rec.Dimension_Level_Name);
1252 l_msg := fnd_message.get;
1253 */
1254
1255 l_msg := 'Failed to upload ' || nvl(l_Dimension_Level_Rec.Dimension_Level_Short_Name, ' ');
1256 l_msg := l_msg || ' Level name: ' || nvl(l_Dimension_Level_Rec.Dimension_Level_Name, ' ');
1257 l_msg := l_msg || ' already exists in the database. ' ;
1258 BIS_UTILITIES_PUB.put_line(p_text =>l_msg);
1259
1260 END IF;
1261
1262
1263 EXCEPTION
1264
1265 WHEN DUPLICATE_DIMENSION_VALUE THEN
1266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1267 l_error_tbl := x_error_tbl;
1268 BIS_UTILITIES_PVT.Add_Error_Message
1269 ( p_error_msg_name => 'BIS_DIM_LEVEL_UNIQUENESS_ERROR'
1270 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1271 , p_error_proc_name => G_PKG_NAME||'.Create_Dimension_Level'
1272 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
1273 , p_error_table => l_error_tbl
1274 , x_error_table => x_error_tbl
1275 );
1276
1277 WHEN NO_DATA_FOUND THEN
1278 x_return_status := FND_API.G_RET_STS_ERROR ;
1279
1280 when FND_API.G_EXC_ERROR then
1281 x_return_status := FND_API.G_RET_STS_ERROR ;
1282
1283 when FND_API.G_EXC_UNEXPECTED_ERROR then
1284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1285
1286 when others then
1287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1288 l_error_tbl := x_error_tbl;
1289 BIS_UTILITIES_PVT.Add_Error_Message
1290 ( p_error_msg_id => SQLCODE
1291 , p_error_description => SQLERRM
1292 , p_error_proc_name => G_PKG_NAME||'.Create_Dimension_Level'
1293 , p_error_table => l_error_tbl
1294 , x_error_table => x_error_tbl
1295 );
1296
1297 END Create_Dimension_Level;
1298 --
1299
1300 -- Should not create a new level with same name as existing ones
1301 -- for the same dimension and source ('EDW' or 'OLTP').
1302 FUNCTION Is_Level_Name_Used
1303 (
1304 p_level_name IN VARCHAR2
1305 , p_source IN VARCHAR2
1306 , p_dimension_id IN NUMBER
1307 )
1308 RETURN BOOLEAN
1309 IS
1310
1311 l_level_id NUMBER;
1312 l_is_used BOOLEAN;
1313
1314 BEGIN
1315
1316 SELECT level_id
1317 INTO l_level_id
1318 FROM bis_levels_vl
1319 WHERE
1320 name = p_level_name
1321 AND source = p_source
1322 AND dimension_id = p_dimension_id;
1323
1324 RETURN TRUE;
1325
1326 EXCEPTION
1327 WHEN NO_DATA_FOUND THEN
1328 RETURN FALSE;
1329
1330 WHEN TOO_MANY_ROWS THEN
1331 RETURN TRUE;
1332
1333 WHEN OTHERS THEN
1334 RETURN TRUE;
1335
1336 END;
1337
1338 --
1339
1340 PROCEDURE Create_New_Dimension_Level
1341 ( p_level_id IN NUMBER, -- l_id
1342 p_level_short_name IN VARCHAR2, -- l_Dimension_Rec.Dimension_Short_Name
1343 p_dimension_id IN NUMBER,
1344 p_level_values_view_name IN VARCHAR2,
1345 p_where_clause IN VARCHAR2,
1346 p_source IN VARCHAR2,
1347 p_created_by IN NUMBER, -- created_by
1348 p_last_updated_by IN NUMBER, -- last_updated_by
1349 p_login_id IN NUMBER, -- l_login_id
1350 p_level_name IN VARCHAR2, -- l_Dimension_Rec.Dimension_Name
1351 p_description IN VARCHAR2, -- l_Dimension_Rec.Description
1352 p_comparison_label_code IN VARCHAR2,
1353 p_attribute_code IN VARCHAR2,
1354 p_application_id IN NUMBER := NULL,
1355 p_default_search IN VARCHAR2,
1356 p_long_lov IN VARCHAR2,
1357 p_master_level IN VARCHAR2,
1358 p_view_object_name IN VARCHAR2,
1359 p_default_values_api IN VARCHAR2,
1360 p_enabled IN VARCHAR2,
1361 p_drill_to_form_function IN VARCHAR2,
1362 p_last_update_date IN DATE := SYSDATE,
1363 p_Hide IN VARCHAR2 := FND_API.G_FALSE
1364 )
1365 IS
1366
1367 l_msg VARCHAR2(3000);
1368
1369 BEGIN
1370
1371
1372 SAVEPOINT InsertIntoBISLevels;
1373
1374 insert into bis_levels(
1375 LEVEL_ID
1376 , SHORT_NAME
1377 , DIMENSION_ID
1378 , LEVEL_VALUES_VIEW_NAME
1379 , WHERE_CLAUSE
1380 , CREATION_DATE
1381 , CREATED_BY
1382 , LAST_UPDATE_DATE
1383 , LAST_UPDATED_BY
1384 , LAST_UPDATE_LOGIN
1385 , SOURCE
1386 , COMPARISON_LABEL_CODE
1387 , ATTRIBUTE_CODE
1388 , APPLICATION_ID
1389 , default_search
1390 , LONG_LOV
1391 , MASTER_LEVEL
1392 , VIEW_OBJECT_NAME
1393 , DEFAULT_VALUES_API
1394 , ENABLED
1395 , DRILL_TO_FORM_FUNCTION
1396 , HIDE_IN_DESIGN
1397 )
1398 values
1399 ( p_level_id
1400 , p_level_short_name
1401 , p_dimension_id
1402 , p_level_values_view_name
1403 , p_where_clause
1404 , p_last_update_date
1405 , p_created_by
1406 , p_last_update_date
1407 , p_last_updated_by
1408 , p_login_id
1409 , p_source
1410 , p_comparison_label_code
1411 , UPPER(p_attribute_code)
1412 , p_application_id
1413 , p_default_search
1414 , NVL(p_long_lov, 'F')
1415 , p_master_level
1416 , p_view_object_name
1417 , p_default_values_api
1418 , NVL(p_enabled, FND_API.G_TRUE)
1419 , p_drill_to_form_function
1420 , p_hide
1421 );
1422
1423 insert into bis_LEVELS_TL (
1424 LEVEL_ID,
1425 LANGUAGE,
1426 NAME,
1427 DESCRIPTION,
1428 CREATION_DATE,
1429 CREATED_BY,
1430 LAST_UPDATE_DATE,
1431 LAST_UPDATED_BY,
1432 LAST_UPDATE_LOGIN,
1433 TRANSLATED,
1434 SOURCE_LANG
1435 ) select
1436 DL.LEVEL_ID
1437 , L.LANGUAGE_CODE
1438 , p_level_name
1439 , p_description
1440 , p_last_update_date
1441 , p_created_by
1442 , p_last_update_date
1443 , p_last_updated_by
1444 , p_login_id
1445 , 'Y'
1446 , userenv('LANG')
1447 from FND_LANGUAGES L
1448 , BIS_LEVELS DL
1449 where L.INSTALLED_FLAG in ('I', 'B')
1450 and DL.SHORT_NAME = p_level_short_name
1451 and not exists
1452 (select 'EXIST'
1453 from BIS_LEVELS_TL TL
1454 , BIS_LEVELS T
1455 where T.level_ID = TL.level_id
1456 and T.SHORT_NAME = p_level_short_name
1457 and TL.LANGUAGE = L.LANGUAGE_CODE);
1458
1459
1460 EXCEPTION
1461
1462 WHEN OTHERS THEN
1463
1464 /*
1465 fnd_message.set_name('BIS', 'BIS_LVL_UPLD_FAIL');
1466 fnd_message.set_token('SHORT_NAME', p_level_short_name);
1467 fnd_message.set_token('NAME', p_level_name);
1468 l_msg := fnd_message.get;
1469 BIS_UTILITIES_PUB.put_line(p_text =>l_msg);
1470 */
1471
1472 l_msg := 'Failed to upload ' || nvl ( p_level_short_name , ' ' );
1473 l_msg := l_msg || ' Level name: ' || nvl ( p_level_name , ' ' );
1474 l_msg := l_msg || ' already exists in the database. ' ;
1475 BIS_UTILITIES_PUB.put_line(p_text =>l_msg);
1476
1477
1478 ROLLBACK TO InsertIntoBISLevels;
1479 RAISE;
1480
1481 END Create_New_Dimension_Level;
1482
1483
1484 --
1485 PROCEDURE Update_Dimension_Level
1486 ( p_api_version IN NUMBER
1487 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1488 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1489 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
1490 , x_return_status OUT NOCOPY VARCHAR2
1491 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1492 )
1493 IS
1494 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
1495 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1496 BEGIN
1497
1498 l_Dimension_Level_Rec := p_Dimension_Level_Rec;
1499 l_Dimension_Level_Rec.Last_Update_Date := NVL(p_Dimension_Level_Rec.Last_Update_Date, SYSDATE);
1500
1501 Update_Dimension_Level
1502 ( p_api_version => p_api_version
1503 , p_commit => p_commit
1504 , p_validation_level => p_validation_level
1505 , p_Dimension_Level_Rec => l_Dimension_Level_Rec
1506 , p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
1507 , x_return_status => x_return_status
1508 , x_error_Tbl => x_error_Tbl
1509 );
1510
1511 --commented RAISE
1512 EXCEPTION
1513 when others then
1514 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1515 l_error_tbl := x_error_tbl;
1516 --added last two paramaters
1517 BIS_UTILITIES_PVT.Add_Error_Message
1518 ( p_error_msg_id => SQLCODE
1519 , p_error_description => SQLERRM
1520 , p_error_proc_name => G_PKG_NAME||'.Update_Dimension_Level'
1521 , p_error_table => l_error_tbl
1522 , x_error_table => x_error_tbl
1523 );
1524 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1525
1526 END Update_Dimension_Level;
1527 --
1528 PROCEDURE Update_Dimension_Level
1529 ( p_api_version IN NUMBER
1530 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1531 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1532 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
1533 , p_owner IN VARCHAR2
1534 , x_return_status OUT NOCOPY VARCHAR2
1535 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1536 )
1537 IS
1538 l_user_id NUMBER;
1539 l_login_id NUMBER;
1540 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
1541 l_count NUMBER := 0;
1542 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1543 --exception
1544
1545 DUPLICATE_DIMENSION_VALUE EXCEPTION;
1546 PRAGMA EXCEPTION_INIT(DUPLICATE_DIMENSION_VALUE, -1);
1547
1548 BEGIN
1549
1550 x_return_status := FND_API.G_RET_STS_SUCCESS;
1551
1552 -- retrieve record from database and apply changes
1553 UpdateRecord
1554 ( p_Dimension_Level_Rec => p_Dimension_Level_Rec
1555 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
1556 , x_return_status => x_return_status
1557 , x_error_Tbl => x_error_Tbl
1558 );
1559
1560 Validate_Dimension_Level
1561 ( p_api_version
1562 , p_validation_level
1563 , l_Dimension_Level_Rec
1564 , x_return_status
1565 , x_error_Tbl
1566 );
1567
1568 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1569 l_error_tbl := x_error_tbl;
1570 --added Error Msg--------
1571 BIS_UTILITIES_PVT.Add_Error_Message
1572 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_ID'
1573 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1574 , p_error_proc_name => G_PKG_NAME||'.Update_Dimension_Level'
1575 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
1576 , p_error_table => l_error_tbl
1577 , x_error_table => x_error_tbl
1578 );
1579 RAISE FND_API.G_EXC_ERROR;
1580 END IF;
1581
1582 IF ((l_Dimension_Level_Rec.Enabled IS NULL) OR (l_Dimension_Level_Rec.Enabled = FND_API.G_FALSE)) THEN
1583 validate_disabling
1584 (p_dim_level_id => l_Dimension_Level_Rec.dimension_level_id
1585 ,p_error_tbl => l_error_tbl
1586 ,x_return_status => x_return_status
1587 ,x_error_tbl => x_error_tbl
1588 );
1589 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1590 RAISE FND_API.G_EXC_ERROR;
1591 END IF;
1592 END IF;
1593
1594 l_user_id := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
1595 l_login_id := fnd_global.LOGIN_ID;
1596
1597 ----------------
1598 --Adding this for the source column
1599 IF (BIS_UTILITIES_PUB.Value_Missing(l_Dimension_Level_Rec.source)
1600 = FND_API.G_TRUE)
1601 OR (BIS_UTILITIES_PUB.Value_NULL(l_Dimension_Level_Rec.source)
1602 = FND_API.G_TRUE)
1603 THEN
1604 l_Dimension_Level_Rec.source := FND_PROFILE.value('BIS_SOURCE');
1605 END IF;
1606 ------------------
1607
1608 l_Dimension_Level_Rec.Last_Update_Date := NVL(p_Dimension_Level_Rec.Last_Update_Date, SYSDATE);
1609 Update bis_Levels
1610 set
1611 SHORT_NAME = l_Dimension_Level_Rec.Dimension_Level_Short_Name
1612 , DIMENSION_ID = l_Dimension_Level_Rec.Dimension_ID
1613 , LEVEL_VALUES_VIEW_NAME = l_Dimension_Level_Rec.Level_Values_View_Name
1614 , WHERE_CLAUSE = l_Dimension_Level_Rec.Where_Clause
1615 , LAST_UPDATE_DATE = l_Dimension_Level_Rec.Last_Update_Date
1616 , LAST_UPDATED_BY = l_user_id
1617 , LAST_UPDATE_LOGIN = l_login_id
1618 , SOURCE = l_Dimension_Level_Rec.Source
1619 , COMPARISON_LABEL_CODE = l_Dimension_Level_Rec.Comparison_Label_Code
1620 , ATTRIBUTE_CODE = UPPER(l_Dimension_Level_Rec.Attribute_Code)
1621 , APPLICATION_ID = l_Dimension_Level_Rec.Application_ID
1622 , default_search = l_Dimension_Level_Rec.default_search
1623 , LONG_LOV = NVL(l_Dimension_Level_Rec.Long_Lov, 'F')
1624 , MASTER_LEVEL = l_Dimension_Level_Rec.Master_Level
1625 , VIEW_OBJECT_NAME = l_Dimension_Level_Rec.View_Object_Name
1626 , DEFAULT_VALUES_API = l_Dimension_Level_Rec.Default_Values_Api
1627 , ENABLED = NVL(l_Dimension_Level_Rec.Enabled,FND_API.G_TRUE)
1628 , DRILL_TO_FORM_FUNCTION = l_Dimension_Level_Rec.Drill_To_Form_Function
1629 , HIDE_IN_DESIGN = l_Dimension_Level_Rec.Hide
1630 where Level_ID = l_Dimension_Level_Rec.Dimension_Level_Id;
1631
1632 if (p_commit = FND_API.G_TRUE) then
1633 COMMIT;
1634 end if;
1635
1636 Translate_dimension_level
1637 ( p_api_version => p_api_version
1638 , p_commit => p_commit
1639 , p_validation_level => p_validation_level
1640 , p_Dimension_level_Rec => l_Dimension_level_Rec
1641 , p_owner => p_owner
1642 , x_return_status => x_return_status
1643 , x_error_Tbl => x_error_Tbl
1644 );
1645
1646 --commented RAISE
1647 EXCEPTION
1648 --new exception
1649 WHEN DUPLICATE_DIMENSION_VALUE THEN
1650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1651 l_error_tbl := x_error_tbl;
1652 BIS_UTILITIES_PVT.Add_Error_Message
1653 ( p_error_msg_name => 'BIS_DIM_LEVEL_UNIQUENESS_ERROR'
1654 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1655 , p_error_proc_name => G_PKG_NAME||'.Update_Dimension_Level'
1656 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
1657 , p_error_table => l_error_tbl
1658 , x_error_table => x_error_tbl
1659 );
1660 WHEN NO_DATA_FOUND THEN
1661 x_return_status := FND_API.G_RET_STS_ERROR ;
1662 --RAISE FND_API.G_EXC_ERROR;
1663 when FND_API.G_EXC_ERROR then
1664 x_return_status := FND_API.G_RET_STS_ERROR ;
1665 --RAISE FND_API.G_EXC_ERROR;
1666 when FND_API.G_EXC_UNEXPECTED_ERROR then
1667 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1668 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1669 when others then
1670 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1671 l_error_tbl := x_error_tbl;
1672 --added last two parameters
1673 BIS_UTILITIES_PVT.Add_Error_Message
1674 ( p_error_msg_id => SQLCODE
1675 , p_error_description => SQLERRM
1676 , p_error_proc_name => G_PKG_NAME||'.Create_Dimension_Level'
1677 , p_error_table => l_error_tbl
1678 , x_error_table => x_error_tbl
1679 );
1680 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1681
1682 END Update_Dimension_Level;
1683 --
1684 --
1685 --
1686 Procedure Translate_Dimension_Level
1687 ( p_api_version IN NUMBER
1688 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1689 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1690 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
1691 , x_return_status OUT NOCOPY VARCHAR2
1692 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1693 )
1694 IS
1695 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1696 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
1697 BEGIN
1698
1699 l_Dimension_Level_Rec := p_Dimension_Level_Rec;
1700 l_Dimension_Level_Rec.Last_Update_Date := NVL(p_Dimension_Level_Rec.Last_Update_Date, SYSDATE);
1701
1702 Translate_Dimension_Level
1703 ( p_api_version => p_api_version
1704 , p_commit => p_commit
1705 , p_validation_level => p_validation_level
1706 , p_Dimension_Level_Rec => l_Dimension_Level_Rec
1707 , p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
1708 , x_return_status => x_return_status
1709 , x_error_Tbl => x_error_Tbl
1710 );
1711
1712 --commented RAISE
1713 EXCEPTION
1714 when others then
1715 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1716 l_error_tbl := x_error_tbl;
1717 --added last two paramaters
1718 BIS_UTILITIES_PVT.Add_Error_Message
1719 ( p_error_msg_id => SQLCODE
1720 , p_error_description => SQLERRM
1721 , p_error_proc_name => G_PKG_NAME||'.Translate_Dimension_Level'
1722 , p_error_table => l_error_tbl
1723 , x_error_table => x_error_tbl
1724 );
1725 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1726
1727 END Translate_Dimension_Level;
1728 --
1729 Procedure Translate_Dimension_Level
1730 ( p_api_version IN NUMBER
1731 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1732 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1733 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
1734 , p_owner IN VARCHAR2
1735 , x_return_status OUT NOCOPY VARCHAR2
1736 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1737 )
1738 IS
1739 l_user_id NUMBER;
1740 l_login_id NUMBER;
1741 l_count NUMBER := 0;
1742 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
1743 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1744
1745 BEGIN
1746 x_return_status := FND_API.G_RET_STS_SUCCESS;
1747
1748 -- retrieve record from database and apply changes
1749 UpdateRecord
1750 ( p_Dimension_Level_Rec => p_Dimension_Level_Rec
1751 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
1752 , x_return_status => x_return_status
1753 , x_error_Tbl => x_error_Tbl
1754 );
1755
1756 Validate_Dimension_Level
1757 ( p_api_version
1758 , p_validation_level
1759 , l_Dimension_Level_Rec
1760 , x_return_status
1761 , x_error_Tbl
1762 );
1763
1764 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1765 RAISE FND_API.G_EXC_ERROR;
1766 END IF;
1767 --
1768 l_user_id := BIS_UTILITIES_PUB.Get_Owner_Id(p_owner);
1769 l_login_id := fnd_global.LOGIN_ID;
1770 --
1771 l_Dimension_Level_Rec.Last_Update_Date := NVL(p_Dimension_Level_Rec.Last_Update_Date, SYSDATE);
1772 Update bis_levels_TL
1773 set
1774 NAME = l_Dimension_Level_Rec.Dimension_Level_Name
1775 , DESCRIPTION = l_Dimension_Level_Rec.description
1776 , LAST_UPDATE_DATE = l_Dimension_Level_Rec.Last_Update_Date
1777 , LAST_UPDATED_BY = l_user_id
1778 , LAST_UPDATE_LOGIN = l_login_id
1779 , SOURCE_LANG = userenv('LANG')
1780 where LEVEL_ID = l_Dimension_Level_Rec.Dimension_Level_Id
1781 and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
1782
1783 if (p_commit = FND_API.G_TRUE) then
1784 COMMIT;
1785 end if;
1786
1787 --commented RAISE
1788 EXCEPTION
1789 WHEN NO_DATA_FOUND THEN
1790 x_return_status := FND_API.G_RET_STS_ERROR ;
1791 --RAISE FND_API.G_EXC_ERROR;
1792 when FND_API.G_EXC_ERROR then
1793 x_return_status := FND_API.G_RET_STS_ERROR ;
1794 --RAISE FND_API.G_EXC_ERROR;
1795 when FND_API.G_EXC_UNEXPECTED_ERROR then
1796 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1797 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1798 when others then
1799 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1800 l_error_tbl := x_error_tbl;
1801 --added last two parameters
1802 BIS_UTILITIES_PVT.Add_Error_Message
1803 ( p_error_msg_id => SQLCODE
1804 , p_error_description => SQLERRM
1805 , p_error_proc_name => G_PKG_NAME||'.Translate_Dimension_Level'
1806 , p_error_table => l_error_tbl
1807 , x_error_table => x_error_tbl
1808 );
1809 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1810
1811 END Translate_Dimension_Level;
1812 --
1813 --
1814 -- Value - ID conversion
1815 PROCEDURE Value_ID_Conversion
1816 ( p_api_version IN NUMBER
1817 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
1818 , x_Dimension_Level_Rec OUT NOCOPY BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
1819 , x_return_status OUT NOCOPY VARCHAR2
1820 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1821 )
1822 is
1823 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1824 begin
1825
1826 x_return_status := FND_API.G_RET_STS_SUCCESS;
1827 x_Dimension_Level_Rec := p_Dimension_Level_Rec;
1828
1829 if (BIS_UTILITIES_PUB.Value_Missing
1830 (x_Dimension_Level_Rec.Dimension_level_id) = FND_API.G_TRUE
1831 AND ( BIS_UTILITIES_PUB.Value_Not_Missing
1832 (x_Dimension_Level_Rec.Dimension_level_short_name) = FND_API.G_TRUE
1833 OR BIS_UTILITIES_PUB.Value_Not_Missing
1834 (x_Dimension_Level_Rec.Dimension_level_name) = FND_API.G_TRUE)
1835 ) then
1836 BIS_DIMENSION_LEVEL_PVT.Value_ID_Conversion
1837 ( p_api_version
1838 , x_Dimension_Level_Rec.Dimension_Level_Short_Name
1839 , x_Dimension_Level_Rec.Dimension_Level_Name
1840 , x_Dimension_Level_Rec.Dimension_Level_ID
1841 , x_return_status
1842 , x_error_Tbl
1843 );
1844 end if;
1845
1846 if (BIS_UTILITIES_PUB.Value_Missing
1847 (x_Dimension_Level_Rec.Dimension_id) = FND_API.G_TRUE
1848 AND ( BIS_UTILITIES_PUB.Value_Not_Missing
1849 (x_Dimension_Level_Rec.Dimension_short_name) = FND_API.G_TRUE
1850 OR BIS_UTILITIES_PUB.Value_Not_Missing
1851 (x_Dimension_Level_Rec.Dimension_name) = FND_API.G_TRUE)
1852 ) then
1853 BIS_DIMENSION_PVT.Value_ID_Conversion
1854 ( p_api_version
1855 , x_Dimension_Level_Rec.Dimension_Short_Name
1856 , x_Dimension_Level_Rec.Dimension_Name
1857 , x_Dimension_Level_Rec.Dimension_ID
1858 , x_return_status
1859 , x_error_Tbl
1860 );
1861 end if;
1862
1863 --commented RAISE
1864 EXCEPTION
1865 WHEN NO_DATA_FOUND THEN
1866 x_return_status := FND_API.G_RET_STS_ERROR ;
1867 --RAISE FND_API.G_EXC_ERROR;
1868 when FND_API.G_EXC_ERROR then
1869 x_return_status := FND_API.G_RET_STS_ERROR ;
1870 --RAISE FND_API.G_EXC_ERROR;
1871 when FND_API.G_EXC_UNEXPECTED_ERROR then
1872 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1873 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1874 when others then
1875 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1876 l_error_tbl := x_error_tbl;
1877 --added last two parameters
1878 BIS_UTILITIES_PVT.Add_Error_Message
1879 ( p_error_msg_id => SQLCODE
1880 , p_error_description => SQLERRM
1881 , p_error_proc_name => G_PKG_NAME||'.Value_ID_Conversion'
1882 , p_error_table => l_error_tbl
1883 , x_error_table => x_error_tbl
1884 );
1885 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1886
1887
1888 end Value_ID_Conversion;
1889 --
1890 PROCEDURE Value_ID_Conversion
1891 ( p_api_version IN NUMBER
1892 , p_Dimension_Level_Short_Name IN VARCHAR2
1893 , p_Dimension_Level_Name IN VARCHAR2
1894 , x_Dimension_Level_ID OUT NOCOPY NUMBER
1895 , x_return_status OUT NOCOPY VARCHAR2
1896 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1897 )
1898 is
1899 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1900 begin
1901
1902 x_return_status := FND_API.G_RET_STS_SUCCESS;
1903
1904 if (BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Level_Short_Name)
1905 = FND_API.G_TRUE) then
1906 SELECT dimension_level_id into x_Dimension_Level_ID
1907 FROM bisbv_dimension_levels
1908 WHERE dimension_level_short_name = p_Dimension_Level_Short_Name;
1909 elsif (BIS_UTILITIES_PUB.Value_Not_Missing(p_Dimension_Level_Name)
1910 = FND_API.G_TRUE) then
1911 SELECT dimension_level_id into x_Dimension_Level_ID
1912 FROM bisbv_dimension_levels
1913 WHERE dimension_level_name = p_Dimension_Level_Name;
1914 else
1915
1916 -- POPULATE THE ERROR TABLE: added last two parameters
1917 l_error_tbl := x_error_tbl;
1918 BIS_UTILITIES_PVT.Add_Error_Message
1919 ( p_error_msg_name => 'BIS_NAME_SHORT_NAME_MISSING'
1920 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1921 , p_error_proc_name => G_PKG_NAME||'.Value_ID_Conversion'
1922 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
1923 , p_error_table => l_error_tbl
1924 , x_error_table => x_error_tbl
1925 );
1926
1927 RAISE FND_API.G_EXC_ERROR;
1928 end if;
1929
1930 EXCEPTION
1931 WHEN NO_DATA_FOUND THEN
1932 x_return_status := FND_API.G_RET_STS_ERROR ;
1933 when FND_API.G_EXC_ERROR then
1934 x_return_status := FND_API.G_RET_STS_ERROR ;
1935 when FND_API.G_EXC_UNEXPECTED_ERROR then
1936 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1937 when others then
1938 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1939 l_error_tbl := x_error_tbl;
1940 BIS_UTILITIES_PVT.Add_Error_Message
1941 ( p_error_msg_id => SQLCODE
1942 , p_error_description => SQLERRM
1943 , p_error_proc_name => G_PKG_NAME||'.Value_ID_Conversion'
1944 , p_error_table => l_error_tbl
1945 , x_error_table => x_error_tbl
1946 );
1947
1948 end Value_ID_Conversion;
1949 --
1950 -- Validates Dimension_Level
1951 PROCEDURE Validate_Dimension_Level
1952 ( p_api_version IN NUMBER
1953 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1954 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
1955 , x_return_status OUT NOCOPY VARCHAR2
1956 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1957 )
1958 IS
1959 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1960 l_error VARCHAR2(10) := FND_API.G_FALSE;
1961 l_error_tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
1962 BEGIN
1963
1964 BEGIN
1965 BIS_DIM_LEVEL_VALIDATE_PVT.Validate_Record
1966 ( p_api_version => p_api_version
1967 , p_validation_level => p_validation_level
1968 , p_Dimension_Level_Rec => p_Dimension_Level_Rec
1969 , x_return_status => x_return_status
1970 , x_error_Tbl => l_error_Tbl
1971 );
1972 EXCEPTION
1973 when FND_API.G_EXC_ERROR then
1974 l_error := FND_API.G_TRUE;
1975 l_error_tbl_p := x_error_tbl;
1976 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_tbl_p
1977 , l_error_Tbl
1978 , x_error_tbl
1979 );
1980 x_return_status := FND_API.G_RET_STS_ERROR;
1981 END;
1982
1983 if (l_error = FND_API.G_TRUE) then
1984 RAISE FND_API.G_EXC_ERROR;
1985 end if;
1986
1987 --commented RAISE
1988 EXCEPTION
1989 WHEN NO_DATA_FOUND THEN
1990 x_return_status := FND_API.G_RET_STS_ERROR ;
1991 --RAISE FND_API.G_EXC_ERROR;
1992 when FND_API.G_EXC_ERROR then
1993 x_return_status := FND_API.G_RET_STS_ERROR ;
1994 -- RAISE FND_API.G_EXC_ERROR;
1995 when FND_API.G_EXC_UNEXPECTED_ERROR then
1996 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1997 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1998 when others then
1999 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2000 l_error_tbl_p := x_error_tbl;
2001 --added last two parameters
2002 BIS_UTILITIES_PVT.Add_Error_Message
2003 ( p_error_msg_id => SQLCODE
2004 , p_error_description => SQLERRM
2005 , p_error_proc_name => G_PKG_NAME||'.Validate_Dimension_Level'
2006 , p_error_table => l_error_tbl_p
2007 , x_error_table => x_error_tbl
2008 );
2009 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2010
2011 END Validate_Dimension_Level;
2012 --
2013 PROCEDURE Delete_Dimension_Level
2014 (
2015 p_commit IN VARCHAR2 := FND_API.G_FALSE
2016 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2017 , p_Dimension_Level_Rec IN BIS_Dimension_Level_PUB.Dimension_Level_Rec_Type
2018 , x_return_status OUT NOCOPY VARCHAR2
2019 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2020 ) IS
2021 l_dim_level_id NUMBER;
2022 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2023
2024 CURSOR cr_dim_short_name IS
2025 SELECT level_id
2026 FROM BIS_LEVELS
2027 WHERE short_name = p_Dimension_Level_Rec.Dimension_Level_Short_Name;
2028 BEGIN
2029 SAVEPOINT DeleteFromBISDimLevs;
2030
2031 IF (p_Dimension_Level_Rec.Dimension_Level_ID IS NOT NULL) THEN
2032 l_dim_level_id := p_Dimension_Level_Rec.Dimension_Level_ID;
2033
2034 ELSIF (p_Dimension_Level_Rec.Dimension_Level_Short_Name IS NOT NULL) THEN
2035 IF (cr_dim_short_name%ISOPEN) THEN
2036 CLOSE cr_dim_short_name;
2037 END IF;
2038 OPEN cr_dim_short_name;
2039 FETCH cr_dim_short_name
2040 INTO l_dim_level_id;
2041 ELSE
2042 l_error_tbl := x_error_tbl;
2043 BIS_UTILITIES_PVT.Add_Error_Message
2044 ( p_error_msg_name => 'BIS_NAME_SHORT_NAME_MISSING'
2045 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2046 , p_error_proc_name => G_PKG_NAME||'.Delete_Dimension_Level'
2047 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
2048 , p_error_table => l_error_tbl
2049 , x_error_table => x_error_tbl
2050 );
2051 RAISE FND_API.G_EXC_ERROR;
2052 END IF;
2053
2054 IF(l_dim_level_id IS NOT NULL) THEN
2055 DELETE FROM bis_levels
2056 WHERE level_id = l_dim_level_id;
2057
2058 DELETE FROM bis_levels_tl
2059 WHERE level_id = l_dim_level_id;
2060 END IF;
2061 IF (p_commit = FND_API.G_TRUE) THEN
2062 COMMIT;
2063 END if;
2064 x_return_status := FND_API.G_RET_STS_SUCCESS;
2065 EXCEPTION
2066 WHEN FND_API.G_EXC_ERROR THEN
2067 x_return_status := FND_API.G_RET_STS_ERROR;
2068 IF (cr_dim_short_name%ISOPEN) THEN
2069 CLOSE cr_dim_short_name;
2070 END IF;
2071 ROLLBACK TO DeleteFromBISDimLevs;
2072 WHEN OTHERS THEN
2073 l_error_tbl := x_error_tbl;
2074 BIS_UTILITIES_PVT.Add_Error_Message
2075 ( p_error_msg_id => SQLCODE
2076 , p_error_description => SQLERRM
2077 , p_error_proc_name => G_PKG_NAME||'.Delete_Dimension_Level'
2078 , p_error_table => l_error_tbl
2079 , x_error_table => x_error_tbl
2080 );
2081 IF (cr_dim_short_name%ISOPEN) THEN
2082 CLOSE cr_dim_short_name;
2083 END IF;
2084 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2085 ROLLBACK TO DeleteFromBISDimLevs;
2086 END Delete_Dimension_Level;
2087 --
2088
2089 --=============================================================================
2090
2091 PROCEDURE Trans_DimObj_By_Given_Lang
2092 (
2093 p_commit IN VARCHAR2 := FND_API.G_FALSE
2094 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2095 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
2096 , x_return_status OUT NOCOPY VARCHAR2
2097 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2098 ) IS
2099
2100 l_dim_level_id NUMBER;
2101 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2102 l_user_id NUMBER;
2103 l_login_id NUMBER;
2104
2105 BEGIN
2106 SAVEPOINT TransDimObjByLangPvt;
2107
2108 l_user_id := FND_GLOBAL.USER_ID;
2109
2110 l_login_id := fnd_global.LOGIN_ID;
2111
2112
2113 SELECT LEVEL_ID
2114 INTO l_dim_level_id
2115 FROM BIS_LEVELS
2116 WHERE SHORT_NAME = p_Dimension_Level_Rec.Dimension_Level_Short_Name;
2117
2118 UPDATE BIS_LEVELS_TL
2119 SET NAME = p_Dimension_Level_Rec.Dimension_Level_Name
2120 , DESCRIPTION = p_Dimension_Level_Rec.Description
2121 , SOURCE_LANG = p_Dimension_Level_Rec.Source_Lang
2122 , LAST_UPDATED_BY = l_user_id
2123 , LAST_UPDATE_LOGIN = l_login_id
2124 WHERE LEVEL_ID = l_dim_level_id
2125 AND LANGUAGE = p_Dimension_Level_Rec.Language;
2126
2127 IF (p_commit = FND_API.G_TRUE) THEN
2128 COMMIT;
2129 END if;
2130 x_return_status := FND_API.G_RET_STS_SUCCESS;
2131
2132 EXCEPTION
2133 WHEN NO_DATA_FOUND THEN
2134 x_return_status := FND_API.G_RET_STS_ERROR ;
2135 ROLLBACK TO TransDimObjByLangPvt;
2136 WHEN FND_API.G_EXC_ERROR THEN
2137 x_return_status := FND_API.G_RET_STS_ERROR ;
2138 ROLLBACK TO TransDimObjByLangPvt;
2139 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2140 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2141 ROLLBACK TO TransDimObjByLangPvt;
2142 WHEN OTHERS THEN
2143 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2144 l_error_tbl := x_error_tbl;
2145 BIS_UTILITIES_PVT.Add_Error_Message
2146 ( p_error_msg_id => SQLCODE
2147 , p_error_description => SQLERRM
2148 , p_error_proc_name => G_PKG_NAME||'.Trans_DimObj_By_Given_Lang'
2149 , p_error_table => l_error_tbl
2150 , x_error_table => x_error_tbl
2151 );
2152 ROLLBACK TO TransDimObjByLangPvt;
2153 END Trans_DimObj_By_Given_Lang;
2154
2155 --=============================================================================
2156
2157 -------------------- Get customized name ----------------------
2158 FUNCTION get_customized_name( p_dim_level_id IN NUMBER) RETURN VARCHAR2 AS
2159 CURSOR c_cust IS SELECT
2160 NAME ,
2161 USER_ID,
2162 APPLICATION_ID,
2163 RESPONSIBILITY_ID,
2164 ORG_ID,
2165 SITE_ID
2166 FROM BIS_LEVELS_CUSTOMIZATIONS_VL
2167 WHERE LEVEL_ID = p_dim_level_id
2168 AND (user_id = fnd_global.user_id
2169 OR responsibility_id = fnd_global.RESP_ID
2170 OR application_id = fnd_global.RESP_APPL_ID
2171 OR org_id = fnd_global.ORG_ID
2172 OR site_id = 0) ;
2173 l_dim_lvl_custom_name_usr bis_levels_customizations_tl.name%TYPE;
2174 l_dim_lvl_custom_name_resp bis_levels_customizations_tl.name%TYPE;
2175 l_dim_lvl_custom_name_appl bis_levels_customizations_tl.name%TYPE;
2176 l_dim_lvl_custom_name_org bis_levels_customizations_tl.name%TYPE;
2177 l_dim_lvl_custom_name_site bis_levels_customizations_tl.name%TYPE;
2178 BEGIN
2179 IF (c_cust%ISOPEN) THEN
2180 CLOSE c_cust;
2181 END IF;
2182
2183 FOR cr IN c_cust LOOP
2184 IF (cr.user_id IS NOT NULL) THEN
2185 l_dim_lvl_custom_name_usr := cr.name;
2186 ELSIF (cr.responsibility_id IS NOT NULL) THEN
2187 l_dim_lvl_custom_name_resp := cr.name;
2188 ELSIF (cr.application_id IS NOT NULL) THEN
2189 l_dim_lvl_custom_name_appl := cr.name;
2190 ELSIF (cr.org_id IS NOT NULL) THEN
2191 l_dim_lvl_custom_name_org := cr.name;
2192 ELSIF (cr.site_id IS NOT NULL) THEN
2193 l_dim_lvl_custom_name_site := cr.name;
2194 END IF;
2195 END LOOP;
2196
2197 IF ( l_dim_lvl_custom_name_usr IS NOT NULL) THEN
2198 RETURN l_dim_lvl_custom_name_usr ;
2199 ELSIF (l_dim_lvl_custom_name_resp IS NOT NULL) THEN
2200 RETURN l_dim_lvl_custom_name_resp ;
2201 ELSIF (l_dim_lvl_custom_name_appl IS NOT NULL) THEN
2202 RETURN l_dim_lvl_custom_name_appl ;
2203 ELSIF (l_dim_lvl_custom_name_org IS NOT NULL) THEN
2204 RETURN l_dim_lvl_custom_name_org ;
2205 ELSIF (l_dim_lvl_custom_name_site IS NOT NULL) THEN
2206 RETURN l_dim_lvl_custom_name_site ;
2207 END IF;
2208
2209 RETURN NULL;
2210 EXCEPTION
2211 WHEN OTHERS THEN
2212 IF (c_cust%ISOPEN) THEN
2213 CLOSE c_cust;
2214 END IF;
2215 RETURN NULL;
2216 END get_customized_name;
2217
2218 -------------------- get_customized_desc --------------------
2219 FUNCTION get_customized_description( p_dim_level_id IN NUMBER) RETURN VARCHAR2 AS
2220 CURSOR c_cust IS SELECT
2221 DESCRIPTION ,
2222 USER_ID,
2223 APPLICATION_ID,
2224 RESPONSIBILITY_ID,
2225 ORG_ID,
2226 SITE_ID
2227 FROM BIS_LEVELS_CUSTOMIZATIONS_VL
2228 WHERE LEVEL_ID = p_dim_level_id
2229 AND (user_id = fnd_global.user_id
2230 OR responsibility_id = fnd_global.RESP_ID
2231 OR application_id = fnd_global.RESP_APPL_ID
2232 OR org_id = fnd_global.ORG_ID
2233 OR site_id = 0) ;
2234 l_dim_lvl_custom_desc_usr bis_levels_customizations_tl.description%TYPE;
2235 l_dim_lvl_custom_desc_resp bis_levels_customizations_tl.description%TYPE;
2236 l_dim_lvl_custom_desc_appl bis_levels_customizations_tl.description%TYPE;
2237 l_dim_lvl_custom_desc_org bis_levels_customizations_tl.description%TYPE;
2238 l_dim_lvl_custom_desc_site bis_levels_customizations_tl.description%TYPE;
2239 BEGIN
2240 IF (c_cust%ISOPEN) THEN
2241 CLOSE c_cust;
2242 END IF;
2243
2244 FOR cr IN c_cust LOOP
2245 IF (cr.user_id IS NOT NULL) THEN
2246 l_dim_lvl_custom_desc_usr := cr.description;
2247 ELSIF (cr.responsibility_id IS NOT NULL) THEN
2248 l_dim_lvl_custom_desc_resp := cr.description;
2249 ELSIF (cr.application_id IS NOT NULL) THEN
2250 l_dim_lvl_custom_desc_appl := cr.description;
2251 ELSIF (cr.org_id IS NOT NULL) THEN
2252 l_dim_lvl_custom_desc_org := cr.description;
2253 ELSIF (cr.site_id IS NOT NULL) THEN
2254 l_dim_lvl_custom_desc_site := cr.description;
2255 END IF;
2256 END LOOP;
2257
2258 IF ( l_dim_lvl_custom_desc_usr IS NOT NULL) THEN
2259 RETURN l_dim_lvl_custom_desc_usr ;
2260 ELSIF (l_dim_lvl_custom_desc_resp IS NOT NULL) THEN
2261 RETURN l_dim_lvl_custom_desc_resp ;
2262 ELSIF (l_dim_lvl_custom_desc_appl IS NOT NULL) THEN
2263 RETURN l_dim_lvl_custom_desc_appl ;
2264 ELSIF (l_dim_lvl_custom_desc_org IS NOT NULL) THEN
2265 RETURN l_dim_lvl_custom_desc_org ;
2266 ELSIF (l_dim_lvl_custom_desc_site IS NOT NULL) THEN
2267 RETURN l_dim_lvl_custom_desc_site ;
2268 END IF;
2269
2270 RETURN NULL;
2271 EXCEPTION
2272 WHEN OTHERS THEN
2273 IF (c_cust%ISOPEN) THEN
2274 CLOSE c_cust;
2275 END IF;
2276 RETURN NULL;
2277 END get_customized_description;
2278
2279 FUNCTION get_customized_enabled( p_dim_level_sht_name IN VARCHAR2) RETURN VARCHAR2 AS
2280 l_dim_level_id bis_levels.level_id%TYPE;
2281 l_dim_level_enabled bis_levels.enabled%TYPE;
2282 l_dim_level_custom_enabled bis_levels_customizations.enabled%TYPE;
2283 BEGIN
2284 SELECT LEVEL_ID,ENABLED
2285 INTO l_dim_level_id,l_dim_level_enabled
2286 FROM BIS_LEVELS
2287 WHERE short_name = p_dim_level_sht_name;
2288
2289 IF l_dim_level_id IS NOT NULL THEN
2290 l_dim_level_custom_enabled := get_customized_enabled(l_dim_level_id);
2291 RETURN NVL(l_dim_level_custom_enabled,l_dim_level_enabled);
2292 END IF;
2293
2294 EXCEPTION
2295 WHEN OTHERS THEN
2296 RETURN NULL;
2297 END get_customized_enabled;
2298
2299
2300 ------------- get_customized_enabled -----------------
2301 FUNCTION get_customized_enabled( p_dim_level_id IN NUMBER) RETURN VARCHAR2 AS
2302 CURSOR c_cust IS SELECT
2303 ENABLED ,
2304 USER_ID,
2305 APPLICATION_ID,
2306 RESPONSIBILITY_ID,
2307 ORG_ID,
2308 SITE_ID
2309 FROM BIS_LEVELS_CUSTOMIZATIONS
2310 WHERE LEVEL_ID = p_dim_level_id
2311 AND (user_id = fnd_global.user_id
2312 OR responsibility_id = fnd_global.RESP_ID
2313 OR application_id = fnd_global.RESP_APPL_ID
2314 OR org_id = fnd_global.ORG_ID
2315 OR site_id = 0) ;
2316 l_dim_lvl_custom_enabled_usr bis_levels_customizations.enabled%TYPE;
2317 l_dim_lvl_custom_enabled_resp bis_levels_customizations.enabled%TYPE;
2318 l_dim_lvl_custom_enabled_appl bis_levels_customizations.enabled%TYPE;
2319 l_dim_lvl_custom_enabled_org bis_levels_customizations.enabled%TYPE;
2320 l_dim_lvl_custom_enabled_site bis_levels_customizations.enabled%TYPE;
2321 BEGIN
2322 IF (c_cust%ISOPEN) THEN
2323 CLOSE c_cust;
2324 END IF;
2325
2326 FOR cr IN c_cust LOOP
2327 IF (cr.user_id IS NOT NULL) THEN
2328 l_dim_lvl_custom_enabled_usr := cr.enabled;
2329 ELSIF (cr.responsibility_id IS NOT NULL) THEN
2330 l_dim_lvl_custom_enabled_resp := cr.enabled;
2331 ELSIF (cr.application_id IS NOT NULL) THEN
2332 l_dim_lvl_custom_enabled_appl := cr.enabled;
2333 ELSIF (cr.org_id IS NOT NULL) THEN
2334 l_dim_lvl_custom_enabled_org := cr.enabled;
2335 ELSIF (cr.site_id IS NOT NULL) THEN
2336 l_dim_lvl_custom_enabled_site := cr.enabled;
2337 END IF;
2338 END LOOP;
2339
2340 IF ( l_dim_lvl_custom_enabled_usr IS NOT NULL) THEN
2341 RETURN l_dim_lvl_custom_enabled_usr ;
2342 ELSIF (l_dim_lvl_custom_enabled_resp IS NOT NULL) THEN
2343 RETURN l_dim_lvl_custom_enabled_resp ;
2344 ELSIF (l_dim_lvl_custom_enabled_appl IS NOT NULL) THEN
2345 RETURN l_dim_lvl_custom_enabled_appl ;
2346 ELSIF (l_dim_lvl_custom_enabled_org IS NOT NULL) THEN
2347 RETURN l_dim_lvl_custom_enabled_org ;
2348 ELSIF (l_dim_lvl_custom_enabled_site IS NOT NULL) THEN
2349 RETURN l_dim_lvl_custom_enabled_site ;
2350 END IF;
2351
2352 RETURN NULL;
2353 EXCEPTION
2354 WHEN OTHERS THEN
2355 IF (c_cust%ISOPEN) THEN
2356 CLOSE c_cust;
2357 END IF;
2358 RETURN NULL;
2359 END get_customized_enabled;
2360
2361 FUNCTION isPMFDimensionLevel(p_dim_level_id IN NUMBER) RETURN BOOLEAN AS
2362 l_do_source BSC_SYS_DIM_LEVELS_B.SOURCE%TYPE;
2363 CURSOR c_do_src(cp_dim_level_id IN VARCHAR2) IS
2364 SELECT do.source FROM bsc_sys_dim_levels_b do, bis_levels dl WHERE do.short_name = dl.short_name
2365 AND dl.level_id = cp_dim_level_id;
2366 BEGIN
2367 -- check if the dimension level type is of PMF and then check if this can be disabled.
2368 IF (c_do_src%ISOPEN) THEN
2369 CLOSE c_do_src;
2370 END IF;
2371 OPEN c_do_src(cp_dim_level_id => p_dim_level_id);
2372 FETCH c_do_src INTO l_do_source;
2373 CLOSE c_do_src;
2374
2375 IF ( l_do_source = 'PMF') THEN
2376 RETURN TRUE;
2377 ELSE
2378 RETURN FALSE;
2379 END IF;
2380 EXCEPTION
2381 WHEN OTHERS THEN
2382 IF (c_do_src%ISOPEN) THEN
2383 CLOSE c_do_src;
2384 END IF;
2385 RETURN FALSE;
2386 END isPMFDimensionLevel;
2387
2388 FUNCTION IS_TARGET_DEFINED( p_dim_level_id IN NUMBER) RETURN BOOLEAN IS
2389 l_target_usage NUMBER;
2390 BEGIN
2391 SELECT COUNT(1) INTO l_target_usage FROM bis_target_levels tl, bis_target_values tv
2392 WHERE ( tl.TIME_LEVEL_ID = p_dim_level_id OR
2393 tl.ORG_LEVEL_ID = p_dim_level_id OR
2394 tl.DIMENSION1_LEVEL_ID = p_dim_level_id OR
2395 tl.DIMENSION2_LEVEL_ID = p_dim_level_id OR
2396 tl.DIMENSION3_LEVEL_ID = p_dim_level_id OR
2397 tl.DIMENSION4_LEVEL_ID = p_dim_level_id OR
2398 tl.DIMENSION5_LEVEL_ID = p_dim_level_id OR
2399 tl.DIMENSION6_LEVEL_ID = p_dim_level_id OR
2400 tl.DIMENSION7_LEVEL_ID = p_dim_level_id
2401 ) AND tl.target_level_id = tv.target_level_id ;
2402
2403 IF ( l_target_usage > 0 ) THEN
2404 RETURN TRUE;
2405 ELSE
2406 RETURN FALSE;
2407 END IF;
2408
2409 END IS_TARGET_DEFINED;
2410
2411 FUNCTION IS_ASSIGNED_TO_KPI( p_dim_level_id IN NUMBER) RETURN BOOLEAN IS
2412 l_kpi_assing_usage NUMBER;
2413 BEGIN
2414 SELECT COUNT(1) INTO l_kpi_assing_usage FROM bsc_kpi_dim_levels_b kpi,
2415 bsc_sys_dim_levels_b do , bis_levels lvl
2416 WHERE do.level_table_name = kpi.level_table_name
2417 AND do.short_name = lvl.short_name
2418 AND lvl.level_id = p_dim_level_id;
2419
2420 IF ( l_kpi_assing_usage > 0 ) THEN
2421 RETURN TRUE;
2422 ELSE
2423 RETURN FALSE;
2424 END IF;
2425
2426 END IS_ASSIGNED_TO_KPI;
2427
2428 PROCEDURE validate_disabling (p_dim_level_id IN NUMBER
2429 , p_error_Tbl IN BIS_UTILITIES_PUB.Error_Tbl_Type
2430 , x_return_status OUT NOCOPY VARCHAR2
2431 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2432 ) IS
2433 BEGIN
2434 -- check if the dimension level type is of PMF and then check if this can be disabled.
2435 IF ( isPMFDimensionLevel( p_dim_level_id => p_dim_level_id )) THEN
2436
2437 -- check if this dimension level is used to set target
2438 IF ( IS_TARGET_DEFINED(p_dim_level_id => p_dim_level_id) ) THEN
2439 BIS_UTILITIES_PVT.Add_Error_Message
2440 ( p_error_msg_name => 'BIS_PMF_DIM_LVL_USED_IN_TARGET'
2441 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2442 , p_error_proc_name => G_PKG_NAME||'.Update_Dimension_Level'
2443 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
2444 , p_error_table => p_error_tbl
2445 , x_error_table => x_error_tbl
2446 );
2447 RAISE FND_API.G_EXC_ERROR;
2448 END IF;
2449
2450 -- check if this dimension level is assigned to a KPI
2451 IF ( IS_ASSIGNED_TO_KPI(p_dim_level_id => p_dim_level_id) ) THEN
2452 BIS_UTILITIES_PVT.Add_Error_Message
2453 ( p_error_msg_name => 'BIS_BSC_DIM_LVL_KPI_ASSIGNED'
2454 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2455 , p_error_proc_name => G_PKG_NAME||'.Update_Dimension_Level'
2456 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
2457 , p_error_table => p_error_tbl
2458 , x_error_table => x_error_tbl
2459 );
2460 RAISE FND_API.G_EXC_ERROR;
2461 END IF;
2462
2463 END IF;
2464 EXCEPTION
2465 WHEN FND_API.G_EXC_ERROR THEN
2466 x_return_status := FND_API.G_RET_STS_ERROR ;
2467 WHEN OTHERS THEN
2468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2469 END validate_disabling;
2470
2471 -- for the table handler , app_exception is needed. Otherwise it does not throws up in
2472 -- customization APIs
2473
2474 PROCEDURE validate_disabling (p_dim_level_id IN NUMBER) IS
2475 BEGIN
2476 -- check if the dimension level type is of PMF and then check if this can be disabled.
2477 IF ( isPMFDimensionLevel( p_dim_level_id => p_dim_level_id )) THEN
2478 -- check if this dimension level is used to set target
2479 IF ( IS_TARGET_DEFINED(p_dim_level_id => p_dim_level_id) ) THEN
2480 fnd_message.set_name('BIS','BIS_PMF_DIM_LVL_USED_IN_TARGET');
2481 app_exception.raise_exception;
2482 END IF;
2483
2484 -- check if this dimension level is assigned to a KPI
2485 IF ( IS_ASSIGNED_TO_KPI(p_dim_level_id => p_dim_level_id) ) THEN
2486 fnd_message.set_name('BIS', 'BIS_BSC_DIM_LVL_KPI_ASSIGNED');
2487 app_exception.raise_exception;
2488 END IF;
2489 END IF;
2490 END validate_disabling;
2491
2492 END BIS_DIMENSION_LEVEL_PVT;