[Home] [Help]
PACKAGE BODY: APPS.BIS_TARGET_LEVEL_PVT
Source
1 PACKAGE BODY BIS_Target_Level_PVT AS
2 /* $Header: BISVTALB.pls 120.1 2006/01/23 01:36:21 ankgoel noship $ */
3 --
4 /*
5 REM +=======================================================================+
6 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
7 REM | All rights reserved. |
8 REM +=======================================================================+
9 REM | FILENAME |
10 REM | BISVINLB.pls |
11 REM | |
12 REM | DESCRIPTION |
13 REM | Private API for creating and managing Indicator Levels
14 REM |
15 REM | NOTES |
16 REM | |
17 REM | HISTORY |
18 REM | 28-NOV-98 irchen Creation
19 REM | 23-JAN-02 sashaik Added Retrieve_Org_level procedure for 1740789
20 REM | 15-FEB-02-- juwang bug#2225110
21 REM | 26-SEP-02 SASHAIK 2486702
22 REM | 29-SEP-02 arhegde bug#2528442 - added retrieve_mult_targ_levels() |
23 REM | 09-OCT-02 arhegde Modified for bug#2616667 |
24 REM | 23-JAN-03 sugopal For having different local variables for IN and OUT |
25 REM | parameters (bug#2758428) |
26 REM | 17-MAR-03 smuruges Modified the WHERE clause for the cursor ind_res |
27 REM | Replaced the OR clause with nvl on sysdate. |
28 REM | 30-JUN-03 rchandra Selected DATASET_ID from bisfv_target_levels to |
29 REM | populate BIS_Target_Level_PUB.Target_Level_Rec_Type|
30 REM | for bug 3004651 |
31 REM | 21-OCT-04 arhegde bug# 3634587 The SQL used shows up on performance |
32 REM | repository top-20, Removed Retrieve_Measure_Notify_Resps() |
33 REM | 05-jul-04 rpenneru Modified for bug#3735203 |
34 REM | 21-Mar-05 ankagarw bug#4235732 - changing count(*) to count(1) |
35 REM | 23-Jan-06 ankgoel bug#4946492 - do not update creation date on UPDATE|
36 REM +=======================================================================+
37 */
38 --
39 G_PKG_NAME CONSTANT VARCHAR2(30):='BIS_TARGET_LEVEL_PVT';
40
41 TYPE lvl_tbl_type IS TABLE OF bis_levels.short_name%TYPE -- Defn added for 2486702
42 INDEX BY BINARY_INTEGER;
43
44 TYPE dim_tbl_type IS TABLE OF NUMBER -- Defn added for 2486702
45 INDEX BY BINARY_INTEGER;
46
47 TYPE bind_variables_tbl_type IS TABLE OF VARCHAR2(100)
48 INDEX BY BINARY_INTEGER;
49
50 --
51 -- PROCEDUREs
52 --
53 -- returns the record with the G_MISS_CHAR/G_MISS_NUM replaced
54 -- by null
55 --
56 PROCEDURE SetNULL
57 ( p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
58 , x_Dimension_Level_Rec OUT NOCOPY BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
59 );
60
61 --==================================================================
62
63 PROCEDURE retrieve_sql(
64 p_target_level_tbl IN BIS_TARGET_LEVEL_PUB.Target_Level_Tbl_Type
65 ,x_is_bind OUT NOCOPY BOOLEAN
66 ,x_is_execute OUT NOCOPY BOOLEAN
67 ,x_sql OUT NOCOPY VARCHAR2
68 ,x_bind_variables_tbl OUT NOCOPY bind_variables_tbl_type
69 );
70
71 --==================================================================
72
73 PROCEDURE Level_Correspond_To_Dim -- Procedure added for 2486702
74 (
75 p_target_level_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
76 , p_measure_rec IN BIS_MEASURE_PUB.MEASURE_REC_TYPE
77 , x_return_status OUT NOCOPY VARCHAR2
78 , x_return_msg OUT NOCOPY VARCHAR2
79 );
80
81
82 PROCEDURE COMPARE_LEVELS_DIMS -- Procedure added for 2486702
83 ( p_dim_tbl_type IN dim_tbl_type
84 , p_lvl_tbl_type IN lvl_tbl_type
85 , p_tl_short_name IN VARCHAR2
86 , p_pm_short_name IN VARCHAR2
87 , x_return_status OUT NOCOPY VARCHAR2
88 , x_return_msg OUT NOCOPY VARCHAR2
89 );
90
91
92 FUNCTION GET_DIM_ID_FRM_LVL_SHTNM -- Function added for 2486702
93 ( p_level_shtnm IN VARCHAR2)
94 RETURN NUMBER;
95
96
97 PROCEDURE GET_MEASURE_DIMS_ARRAY -- Procedure added for 2486702
98 ( p_measure_rec IN BIS_MEASURE_PUB.MEASURE_REC_TYPE
99 , x_dim_tbl_type OUT NOCOPY dim_tbl_type
100 , x_num_dims OUT NOCOPY NUMBER
101 , x_return_status OUT NOCOPY VARCHAR2
102 , x_return_msg OUT NOCOPY VARCHAR2
103 );
104
105
106 PROCEDURE ADD_TO_MEASURE_ARRAY -- Procedure added for 2486702
107 ( p_dim_tbl_type IN OUT NOCOPY dim_tbl_type
108 , p_dim_id IN NUMBER
109 );
110
111
112 PROCEDURE GET_TL_LVLS_ARRAY -- Procedure added for 2486702
113 ( p_target_level_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
114 , x_lvl_tbl_type OUT NOCOPY lvl_tbl_type
115 , x_num_lvls OUT NOCOPY NUMBER
116 , x_return_status OUT NOCOPY VARCHAR2
117 , x_return_msg OUT NOCOPY VARCHAR2
118 );
119
120
121 PROCEDURE ADD_TO_LEVEL_ARRAY -- Procedure added for 2486702
122 ( p_lvl_tbl_type IN OUT NOCOPY lvl_tbl_type
123 , p_short_name IN VARCHAR
124 );
125
126
127 FUNCTION CHECK_UNIQUE_DIMS -- Function added for 2486702
128 (p_dim_tbl_type IN dim_tbl_type)
129 RETURN BOOLEAN;
130
131
132 FUNCTION CHECK_UNIQUE_LEVELS -- Function added for 2486702
133 (p_lvl_tbl_type IN lvl_tbl_type)
134 RETURN BOOLEAN;
135
136
137 FUNCTION IS_ORG_OR_TIME_LEVEL -- Function added for 2486702
138 (p_lvl_short_name IN VARCHAR2)
139 RETURN BOOLEAN;
140
141
142 FUNCTION IS_NOT_NULL_MISSING_CHAR -- Function added for 2486702
143 (p_string IN VARCHAR2)
144 RETURN BOOLEAN;
145
146
147 FUNCTION IS_NOT_NULL_MISSING_NUM -- Function added for 2486702
148 (p_number IN NUMBER)
149 RETURN BOOLEAN;
150
151
152 --
153 PROCEDURE SetNULL
154 ( p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
155 , x_Dimension_Level_Rec OUT NOCOPY BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
156 )
157 IS
158 BEGIN
159
160 x_dimension_level_rec.Dimension_ID
161 := BIS_UTILITIES_PUB.G_NULL_NUM;
162 x_dimension_level_rec.Dimension_Short_Name
163 := BIS_UTILITIES_PUB.G_NULL_CHAR;
164 x_dimension_level_rec.Dimension_Name
165 := BIS_UTILITIES_PUB.G_NULL_CHAR;
166 x_dimension_level_rec.Dimension_Level_ID
167 := BIS_UTILITIES_PUB.G_NULL_NUM;
168 x_dimension_level_rec.Dimension_Level_Short_Name
169 := BIS_UTILITIES_PUB.G_NULL_CHAR;
170 x_dimension_level_rec.Dimension_Level_Name
171 := BIS_UTILITIES_PUB.G_NULL_CHAR;
172 x_dimension_level_rec.Description
173 := BIS_UTILITIES_PUB.G_NULL_CHAR;
174 x_dimension_level_rec.Level_Values_View_Name
175 := BIS_UTILITIES_PUB.G_NULL_CHAR;
176 x_dimension_level_rec.where_Clause
177 := BIS_UTILITIES_PUB.G_NULL_CHAR;
178 x_dimension_level_rec.source
179 := BIS_UTILITIES_PUB.G_NULL_CHAR;
180
181 EXCEPTION
182 WHEN FND_API.G_EXC_ERROR THEN
183 RAISE
184 ;
185 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
186 RAISE;
187 WHEN OTHERS THEN
188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
189
190 END SetNULL;
191 PROCEDURE Set_NULL
192 ( p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
193 , x_Target_Level_Rec OUT NOCOPY BIS_Target_Level_PUB.Target_Level_Rec_Type
194 )
195 IS
196 BEGIN
197
198 x_target_level_rec.Measure_ID :=
199 BIS_UTILITIES_PVT.CheckMissNum(p_target_level_rec.Measure_ID);
200 x_target_level_rec.Measure_Short_Name :=
201 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Measure_Short_Name);
202 x_target_level_rec.Measure_Name :=
203 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Measure_Name);
204 x_target_level_rec.Target_Level_ID :=
205 BIS_UTILITIES_PVT.CheckMissNum(p_target_level_rec.Target_Level_ID);
206 x_target_level_rec.Target_Level_Short_Name :=
207 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Target_Level_Short_Name);
208 x_target_level_rec.Target_Level_Name :=
209 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Target_Level_Name);
210 x_target_level_rec.Description :=
211 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Description);
212 x_target_level_rec.Org_Level_ID :=
213 BIS_UTILITIES_PVT.CheckMissNum(p_target_level_rec.Org_Level_ID);
214 x_target_level_rec.Org_Level_Short_Name :=
215 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Org_Level_Short_Name);
216 x_target_level_rec.Org_Level_Name :=
217 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Org_Level_Name);
218 x_target_level_rec.Time_Level_ID :=
219 BIS_UTILITIES_PVT.CheckMissNum(p_target_level_rec.Time_Level_ID);
220 x_target_level_rec.Time_Level_Short_Name :=
221 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Time_Level_Short_Name);
222 x_target_level_rec.Time_Level_Name :=
223 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Time_Level_Name);
224 x_target_level_rec.Dimension1_Level_ID :=
225 BIS_UTILITIES_PVT.CheckMissNum(p_target_level_rec.Dimension1_Level_ID);
226 x_target_level_rec.Dimension1_Level_Short_Name :=
227 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Dimension1_Level_Short_Name);
228 x_target_level_rec.Dimension1_Level_Name :=
229 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Dimension1_Level_Name);
230 x_target_level_rec.Dimension2_Level_ID :=
231 BIS_UTILITIES_PVT.CheckMissNum(p_target_level_rec.Dimension2_Level_ID);
232 x_target_level_rec.Dimension2_Level_Short_Name :=
233 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Dimension2_Level_Short_Name);
234 x_target_level_rec.Dimension2_Level_Name :=
235 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Dimension2_Level_Name);
236 x_target_level_rec.Dimension3_Level_ID :=
237 BIS_UTILITIES_PVT.CheckMissNum(p_target_level_rec.Dimension3_Level_ID);
238 x_target_level_rec.Dimension3_Level_Short_Name :=
239 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Dimension3_Level_Short_Name);
240 x_target_level_rec.Dimension3_Level_Name :=
241 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Dimension3_Level_Name);
242 x_target_level_rec.Dimension4_Level_ID :=
243 BIS_UTILITIES_PVT.CheckMissNum(p_target_level_rec.Dimension4_Level_ID);
244 x_target_level_rec.Dimension4_Level_Short_Name :=
245 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Dimension4_Level_Short_Name);
246 x_target_level_rec.Dimension4_Level_Name :=
247 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Dimension4_Level_Name);
248 x_target_level_rec.Dimension5_Level_ID :=
249 BIS_UTILITIES_PVT.CheckMissNum(p_target_level_rec.Dimension5_Level_ID);
250 x_target_level_rec.Dimension5_Level_Short_Name :=
251 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Dimension5_Level_Short_Name);
252 x_target_level_rec.Dimension5_Level_Name :=
253 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Dimension5_Level_Name);
254 x_target_level_rec.Dimension6_Level_ID :=
255 BIS_UTILITIES_PVT.CheckMissNum(p_target_level_rec.Dimension6_Level_ID);
256 x_target_level_rec.Dimension6_Level_Short_Name :=
257 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Dimension6_Level_Short_Name);
258 x_target_level_rec.Dimension6_Level_Name :=
259 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Dimension6_Level_Name);
260 x_target_level_rec.Dimension7_Level_ID :=
261 BIS_UTILITIES_PVT.CheckMissNum(p_target_level_rec.Dimension7_Level_ID);
262 x_target_level_rec.Dimension7_Level_Short_Name :=
263 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Dimension7_Level_Short_Name);
264 x_target_level_rec.Dimension7_Level_Name :=
265 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Dimension7_Level_Name);
266 x_target_level_rec.Workflow_Process_Short_Name :=
267 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Workflow_Process_Short_Name);
268 x_target_level_rec.Workflow_Process_Name :=
269 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Workflow_Process_Name);
270 x_target_level_rec.Workflow_Item_Type :=
271 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Workflow_Item_Type);
272 x_target_level_rec.Default_Notify_Resp_ID :=
273 BIS_UTILITIES_PVT.CheckMissNum(p_target_level_rec.Default_Notify_Resp_ID);
274 x_target_level_rec.Default_Notify_Resp_short_name:=
275 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Default_Notify_Resp_short_name);
276 x_target_level_rec.Default_Notify_Resp_Name :=
277 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Default_Notify_Resp_Name);
278 x_target_level_rec.Computing_Function_ID :=
279 BIS_UTILITIES_PVT.CheckMissNum(p_target_level_rec.Computing_Function_ID);
280 x_target_level_rec.Computing_Function_Name :=
281 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Computing_Function_Name);
282 x_target_level_rec.Computing_User_Function_Name :=
283 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Computing_User_Function_Name);
284 x_target_level_rec.Report_Function_ID :=
285 BIS_UTILITIES_PVT.CheckMissNum(p_target_level_rec.Report_Function_ID);
286 x_target_level_rec.Report_Function_Name :=
287 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Report_Function_Name);
288 x_target_level_rec.Report_User_Function_Name :=
289 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Report_User_Function_Name);
290 x_target_level_rec.Unit_Of_Measure :=
291 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.Unit_Of_Measure);
292 x_target_level_rec.System_Flag :=
293 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.System_Flag);
294 x_target_level_rec.SOURCE :=
295 BIS_UTILITIES_PVT.CheckMissChar(p_target_level_rec.SOURCE);
296 END;
297
298 FUNCTION Get_Level_Id_From_Short_Name
299 ( p_tl_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
300 ) RETURN NUMBER
301 IS
302 cursor short_name_cursor IS
303 select target_level_id
304 from bis_target_levels
305 where short_name like p_tl_rec.target_level_short_name;
306 l_dummy number := NULL;
307 BEGIN
308
309 open short_name_cursor;
310 fetch short_name_cursor into l_dummy;
311 if (short_name_cursor%NOTFOUND) then
312 close short_name_cursor;
313 return NULL;
314 end if;
315 close short_name_cursor;
316
317 return l_dummy;
318
319 END Get_Level_Id_From_Short_Name;
320
321 FUNCTION Get_Level_Id_From_Dimlevels
322 ( p_tl_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
323 ) RETURN NUMBER
324 IS
325 --changed cursor
326 cursor dimlevel_cursor(p_tl_curs_rec BIS_Target_Level_PUB.Target_Level_Rec_Type) IS
327 select target_level_id
328 from bis_target_levels
329 where NVL(INDICATOR_ID, -1) = NVL(p_tl_curs_rec.Measure_Id, -1)
330 AND (p_tl_curs_rec.Org_Level_Id IS NULL OR NVL(ORG_LEVEL_ID, -1) = NVL(p_tl_curs_rec.Org_Level_Id, -1))
331 AND (p_tl_curs_rec.Time_Level_Id IS NULL OR NVL(TIME_LEVEL_ID, -1) = NVL(p_tl_curs_rec.Time_Level_Id, -1))
332 AND NVL(DIMENSION1_LEVEL_ID, -1) = NVL(p_tl_curs_rec.Dimension1_Level_Id, -1)
333 AND NVL(DIMENSION2_LEVEL_ID, -1) = NVL(p_tl_curs_rec.Dimension2_Level_Id, -1)
334 AND NVL(DIMENSION3_LEVEL_ID, -1) = NVL(p_tl_curs_rec.Dimension3_Level_Id, -1)
335 AND NVL(DIMENSION4_LEVEL_ID, -1) = NVL(p_tl_curs_rec.Dimension4_Level_Id, -1)
336 AND NVL(DIMENSION5_LEVEL_ID, -1) = NVL(p_tl_curs_rec.Dimension5_Level_Id, -1)
337 AND NVL(DIMENSION6_LEVEL_ID, -1) = NVL(p_tl_curs_rec.Dimension6_Level_Id, -1)
338 AND NVL(DIMENSION7_LEVEL_ID, -1) = NVL(p_tl_curs_rec.Dimension7_Level_Id, -1);
339
340 l_dummy number;
341 l_tl_Rec BIS_Target_Level_PUB.Target_Level_Rec_Type;
342 BEGIN
343 --set null first
344 Set_NULL( p_Target_Level_Rec => p_tl_Rec
345 , x_Target_Level_Rec => l_tl_Rec);
346
347 open dimlevel_cursor(l_tl_Rec);
348 fetch dimlevel_cursor into l_dummy;
349
350 if (dimlevel_cursor%NOTFOUND) then
351 close dimlevel_cursor;
352 return NULL;
353 end if;
354 close dimlevel_cursor;
355
356 return l_dummy;
357
358 END Get_Level_Id_From_Dimlevels;
359
360 -----------------------------------------------------------------------------
361 -- New Function to return TargetLevelId given the DimensionLevel ShortNames
362 -- and the Measure Short Name
363
364 FUNCTION Get_Id_From_DimLevelShortNames
365 ( p_target_level_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
366 ) RETURN NUMBER
367 IS
368
369 l_target_level_id number;
370
371 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
372 l_Measure_Rec BIS_MEASURE_PUB.Measure_Rec_Type;
373 l_target_level_rec BIS_Target_Level_PUB.Target_Level_Rec_Type;
374 l_return_status VARCHAR2(100);
375 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
376 l_Measure_Rec_p BIS_MEASURE_PUB.Measure_Rec_Type;
377 l_Dimension_Level_Rec_p BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
378
379 BEGIN
380 if (BIS_UTILITIES_PUB.Value_Missing(p_target_level_rec.Measure_ID)
381 = FND_API.G_TRUE) then
382 if (BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.Measure_Short_Name)
383 = FND_API.G_TRUE) then
384
385 l_Measure_Rec.Measure_Short_Name := p_target_level_rec.Measure_Short_Name;
386
387 l_Measure_Rec_p := l_Measure_Rec;
388 BIS_Measure_PVT.Measure_Value_ID_Conversion
389 ( p_api_version => 1.0
390 , p_Measure_Rec => l_Measure_Rec_p
391 , x_Measure_Rec => l_Measure_Rec
392 , x_return_status => l_return_status
393 , x_error_Tbl => l_error_Tbl
394 );
395 if(l_return_status = FND_API.G_RET_STS_SUCCESS) then
396 l_target_level_rec.Measure_ID := l_Measure_Rec.Measure_ID;
397
398 end if;
399 end if;
400 else
401 l_target_level_rec.Measure_ID := p_target_level_rec.Measure_ID;
402 end if;
403
404 if (BIS_UTILITIES_PUB.Value_Missing(p_target_level_rec.Org_Level_ID)
405 = FND_API.G_TRUE) then
406 if (BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.Org_Level_Short_Name)
407 = FND_API.G_TRUE) then
408 l_Dimension_Level_Rec.Dimension_Level_Short_Name := p_target_level_rec.Org_Level_Short_Name;
409
410 l_Dimension_Level_Rec_p := l_Dimension_Level_Rec;
411 BIS_DIMENSION_LEVEL_PVT.Value_ID_Conversion
412 ( p_api_version => 1.0
413 , p_Dimension_Level_Rec => l_Dimension_Level_Rec_p
414 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
415 , x_return_status => l_return_status
416 , x_error_Tbl => l_error_tbl
417 );
418 if(l_return_status = FND_API.G_RET_STS_SUCCESS) then
419 l_target_level_rec.Org_Level_ID := l_Dimension_Level_Rec.Dimension_Level_Id;
420
421 end if;
422 end if;
423 else
424 l_target_level_rec.Org_Level_ID := p_target_level_rec.Org_Level_ID;
425
426 end if;
427 l_Dimension_Level_Rec.Dimension_Level_Short_Name := BIS_UTILITIES_PUB.G_NULL_CHAR;
428 l_Dimension_Level_Rec.Dimension_Level_Id := BIS_UTILITIES_PUB.G_NULL_NUM;
429
430 if (BIS_UTILITIES_PUB.Value_Missing(p_target_level_rec.Time_Level_ID)
431 = FND_API.G_TRUE) then
432 if (BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.Time_Level_Short_Name)
433 = FND_API.G_TRUE) then
434 l_Dimension_Level_Rec.Dimension_Level_Short_Name := p_target_level_rec.Time_Level_Short_Name;
435
436 l_Dimension_Level_Rec_p := l_Dimension_Level_Rec;
437 BIS_DIMENSION_LEVEL_PVT.Value_ID_Conversion
438 ( p_api_version => 1.0
439 , p_Dimension_Level_Rec => l_Dimension_Level_Rec_p
440 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
441 , x_return_status => l_return_status
442 , x_error_Tbl => l_error_tbl
443 );
444 if(l_return_status = FND_API.G_RET_STS_SUCCESS) then
445 l_target_level_rec.Time_Level_ID := l_Dimension_Level_Rec.Dimension_Level_Id;
446
447 end if;
448 end if;
449 else
450 l_target_level_rec.Time_Level_ID := p_target_level_rec.Time_Level_ID;
451
452 end if;
453 l_Dimension_Level_Rec.Dimension_Level_Short_Name := BIS_UTILITIES_PUB.G_NULL_CHAR;
454 l_Dimension_Level_Rec.Dimension_Level_Id := BIS_UTILITIES_PUB.G_NULL_NUM;
455
456
457 if (BIS_UTILITIES_PUB.Value_Missing(p_target_level_rec.Dimension1_Level_ID)
458 = FND_API.G_TRUE) then
459 if (BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.Dimension1_Level_Short_Name)
460 = FND_API.G_TRUE) then
461 l_Dimension_Level_Rec.Dimension_Level_Short_Name := p_target_level_rec.Dimension1_Level_Short_Name;
462
463 l_Dimension_Level_Rec_p := l_Dimension_Level_Rec;
464 BIS_DIMENSION_LEVEL_PVT.Value_ID_Conversion
465 ( p_api_version => 1.0
466 , p_Dimension_Level_Rec => l_Dimension_Level_Rec_p
467 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
468 , x_return_status => l_return_status
469 , x_error_Tbl => l_error_tbl
470 );
471 if(l_return_status = FND_API.G_RET_STS_SUCCESS) then
472 l_target_level_rec.Dimension1_Level_ID := l_Dimension_Level_Rec.Dimension_Level_Id;
473
474 end if;
475 end if;
476 else
477 l_target_level_rec.Dimension1_Level_ID := p_target_level_rec.Dimension1_Level_ID;
478
479 end if;
480 l_Dimension_Level_Rec.Dimension_Level_Short_Name := BIS_UTILITIES_PUB.G_NULL_CHAR;
481 l_Dimension_Level_Rec.Dimension_Level_Id := BIS_UTILITIES_PUB.G_NULL_NUM;
482
483
484 if (BIS_UTILITIES_PUB.Value_Missing(p_target_level_rec.Dimension2_Level_ID)
485 = FND_API.G_TRUE) then
486 if (BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.Dimension2_Level_Short_Name)
487 = FND_API.G_TRUE) then
488 l_Dimension_Level_Rec.Dimension_Level_Short_Name := p_target_level_rec.Dimension2_Level_Short_Name;
489
490 l_Dimension_Level_Rec_p := l_Dimension_Level_Rec;
491 BIS_DIMENSION_LEVEL_PVT.Value_ID_Conversion
492 ( p_api_version => 1.0
493 , p_Dimension_Level_Rec => l_Dimension_Level_Rec_p
494 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
495 , x_return_status => l_return_status
496 , x_error_Tbl => l_error_tbl
497 );
498 if(l_return_status = FND_API.G_RET_STS_SUCCESS) then
499 l_target_level_rec.Dimension2_Level_ID := l_Dimension_Level_Rec.Dimension_Level_Id;
500
501 end if;
502 end if;
503 else
504 l_target_level_rec.Dimension2_Level_ID := p_target_level_rec.Dimension2_Level_ID;
505
506 end if;
507 l_Dimension_Level_Rec.Dimension_Level_Short_Name := BIS_UTILITIES_PUB.G_NULL_CHAR;
508 l_Dimension_Level_Rec.Dimension_Level_Id := BIS_UTILITIES_PUB.G_NULL_NUM;
509
510 if (BIS_UTILITIES_PUB.Value_Missing(p_target_level_rec.Dimension3_Level_ID)
511 = FND_API.G_TRUE) then
512 if (BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.Dimension3_Level_Short_Name)
513 = FND_API.G_TRUE) then
514 l_Dimension_Level_Rec.Dimension_Level_Short_Name := p_target_level_rec.Dimension3_Level_Short_Name;
515
516 l_Dimension_Level_Rec_p := l_Dimension_Level_Rec;
517 BIS_DIMENSION_LEVEL_PVT.Value_ID_Conversion
518 ( p_api_version => 1.0
519 , p_Dimension_Level_Rec => l_Dimension_Level_Rec_p
520 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
521 , x_return_status => l_return_status
522 , x_error_Tbl => l_error_tbl
523 );
524 if(l_return_status = FND_API.G_RET_STS_SUCCESS) then
525 l_target_level_rec.Dimension3_Level_ID := l_Dimension_Level_Rec.Dimension_Level_Id;
526
527 end if;
528 end if;
529 else
530 l_target_level_rec.Dimension3_Level_ID := p_target_level_rec.Dimension3_Level_ID;
531
532 end if;
533 l_Dimension_Level_Rec.Dimension_Level_Short_Name := BIS_UTILITIES_PUB.G_NULL_CHAR;
534 l_Dimension_Level_Rec.Dimension_Level_Id := BIS_UTILITIES_PUB.G_NULL_NUM;
535
536
537 if (BIS_UTILITIES_PUB.Value_Missing(p_target_level_rec.Dimension4_Level_ID)
538 = FND_API.G_TRUE) then
539 if (BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.Dimension4_Level_Short_Name)
540 = FND_API.G_TRUE) then
541 l_Dimension_Level_Rec.Dimension_Level_Short_Name := p_target_level_rec.Dimension4_Level_Short_Name;
542
543 l_Dimension_Level_Rec_p := l_Dimension_Level_Rec;
544 BIS_DIMENSION_LEVEL_PVT.Value_ID_Conversion
545 ( p_api_version => 1.0
546 , p_Dimension_Level_Rec => l_Dimension_Level_Rec_p
547 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
548 , x_return_status => l_return_status
549 , x_error_Tbl => l_error_tbl
550 );
551 if(l_return_status = FND_API.G_RET_STS_SUCCESS) then
552 l_target_level_rec.Dimension4_Level_ID := l_Dimension_Level_Rec.Dimension_Level_Id;
553
554 end if;
555 end if;
556 else
557 l_target_level_rec.Dimension4_Level_ID := p_target_level_rec.Dimension4_Level_ID;
558
559 end if;
560 l_Dimension_Level_Rec.Dimension_Level_Short_Name := BIS_UTILITIES_PUB.G_NULL_CHAR;
561 l_Dimension_Level_Rec.Dimension_Level_Id := BIS_UTILITIES_PUB.G_NULL_NUM;
562
563 if (BIS_UTILITIES_PUB.Value_Missing(p_target_level_rec.Dimension5_Level_ID)
564 = FND_API.G_TRUE) then
565 if (BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.Dimension5_Level_Short_Name)
566 = FND_API.G_TRUE) then
567 l_Dimension_Level_Rec.Dimension_Level_Short_Name := p_target_level_rec.Dimension5_Level_Short_Name;
568 l_Dimension_Level_Rec_p := l_Dimension_Level_Rec;
569 BIS_DIMENSION_LEVEL_PVT.Value_ID_Conversion
570 ( p_api_version => 1.0
571 , p_Dimension_Level_Rec => l_Dimension_Level_Rec_p
572 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
573 , x_return_status => l_return_status
574 , x_error_Tbl => l_error_tbl
575 );
576 if(l_return_status = FND_API.G_RET_STS_SUCCESS) then
577 l_target_level_rec.Dimension5_Level_ID := l_Dimension_Level_Rec.Dimension_Level_Id;
578 end if;
579 end if;
580 else
581 l_target_level_rec.Dimension5_Level_ID := p_target_level_rec.Dimension5_Level_ID;
582 end if;
583 l_Dimension_Level_Rec.Dimension_Level_Short_Name := BIS_UTILITIES_PUB.G_NULL_CHAR;
584 l_Dimension_Level_Rec.Dimension_Level_Id := BIS_UTILITIES_PUB.G_NULL_NUM;
585
586 if (BIS_UTILITIES_PUB.Value_Missing(p_target_level_rec.Dimension6_Level_ID)
587 = FND_API.G_TRUE) then
588 if (BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.Dimension6_Level_Short_Name)
589 = FND_API.G_TRUE) then
590 l_Dimension_Level_Rec.Dimension_Level_Short_Name := p_target_level_rec.Dimension6_Level_Short_Name;
591 l_Dimension_Level_Rec_p := l_Dimension_Level_Rec;
592 BIS_DIMENSION_LEVEL_PVT.Value_ID_Conversion
593 ( p_api_version => 1.0
594 , p_Dimension_Level_Rec => l_Dimension_Level_Rec_p
595 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
596 , x_return_status => l_return_status
597 , x_error_Tbl => l_error_tbl
598 );
599 if(l_return_status = FND_API.G_RET_STS_SUCCESS) then
600 l_target_level_rec.Dimension6_Level_ID := l_Dimension_Level_Rec.Dimension_Level_Id;
601 end if;
602 end if;
603 else
604 l_target_level_rec.Dimension6_Level_ID := p_target_level_rec.Dimension6_Level_ID;
605 end if;
606 l_Dimension_Level_Rec.Dimension_Level_Short_Name := BIS_UTILITIES_PUB.G_NULL_CHAR;
607 l_Dimension_Level_Rec.Dimension_Level_Id := BIS_UTILITIES_PUB.G_NULL_NUM;
608
609 if (BIS_UTILITIES_PUB.Value_Missing(p_target_level_rec.Dimension7_Level_ID)
610 = FND_API.G_TRUE) then
611 if (BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.Dimension7_Level_Short_Name)
612 = FND_API.G_TRUE) then
613 l_Dimension_Level_Rec.Dimension_Level_Short_Name := p_target_level_rec.Dimension7_Level_Short_Name;
614 l_Dimension_Level_Rec_p := l_Dimension_Level_Rec;
615 BIS_DIMENSION_LEVEL_PVT.Value_ID_Conversion
616 ( p_api_version => 1.0
617 , p_Dimension_Level_Rec => l_Dimension_Level_Rec_p
618 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
619 , x_return_status => l_return_status
620 , x_error_Tbl => l_error_tbl
621 );
622 if(l_return_status = FND_API.G_RET_STS_SUCCESS) then
623 l_target_level_rec.Dimension7_Level_ID := l_Dimension_Level_Rec.Dimension_Level_Id;
624 end if;
625 end if;
626 else
627 l_target_level_rec.Dimension7_Level_ID := p_target_level_rec.Dimension7_Level_ID;
628 end if;
629 l_Dimension_Level_Rec.Dimension_Level_Short_Name := BIS_UTILITIES_PUB.G_NULL_CHAR;
630 l_Dimension_Level_Rec.Dimension_Level_Id := BIS_UTILITIES_PUB.G_NULL_NUM;
631
632 l_target_level_id := Get_Level_Id_From_Dimlevels(l_target_level_rec);
633
634 return l_target_level_id;
635 END Get_Id_From_DimLevelShortNames;
636 ----------------------------
637
638
639
640 -- creates one Indicator Level
641 PROCEDURE Create_Target_Level
642 ( p_api_version IN NUMBER
643 , p_commit IN VARCHAR2 := FND_API.G_FALSE
644 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
645 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
646 , x_return_status OUT NOCOPY VARCHAR2
647 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
648 )
649 IS
650 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
651 BEGIN
652
653 Create_Target_Level
654 ( p_api_version => p_api_version
655 , p_commit => p_commit
656 , p_validation_level => p_validation_level
657 , p_Target_Level_Rec => p_Target_Level_Rec
658 , p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
659 , x_return_status => x_return_status
660 , x_error_Tbl => x_error_Tbl
661 );
662
663 --commented RAISE
664 EXCEPTION
665 when others then
666 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
667 --Added last two parameters
668 l_error_tbl := x_error_tbl;
669 BIS_UTILITIES_PVT.Add_Error_Message
670 ( p_error_msg_id => SQLCODE
671 , p_error_description => SQLERRM
672 , p_error_proc_name => G_PKG_NAME||'.Create_Target_Level'
673 , p_error_table => l_error_tbl
674 , x_error_table => x_error_tbl
675 );
676 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
677
678 END Create_Target_Level;
679 --
680 -- creates one Indicator Level for the given owner
681 PROCEDURE Create_Target_Level
682 ( p_api_version IN NUMBER
683 , p_commit IN VARCHAR2 := FND_API.G_FALSE
684 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
685 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
686 , p_owner IN VARCHAR2
687 , x_return_status OUT NOCOPY VARCHAR2
688 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
689 )
690 IS
691 l_user_id NUMBER;
692 l_login_id NUMBER;
693 l_id NUMBER;
694 l_Target_Level_Rec BIS_Target_Level_PUB.Target_Level_Rec_Type;
695 l_Target_Level_Id NUMBER;
696 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
697
698 -- l_msg VARCHAR2(3000); -- 2515991
699
700
701 DUPLICATE_DIMENSION_VALUE EXCEPTION;
702 PRAGMA EXCEPTION_INIT(DUPLICATE_DIMENSION_VALUE, -1);
703
704 BEGIN
705
706 /* -- 2515991
707 fnd_message.set_name('BIS', 'BIS_SUMLVL_NOT_CREATED');
708 fnd_message.set_token('NAME', p_Target_Level_Rec.target_level_name );
709 l_msg := fnd_message.get;
710
711 l_msg := ' The Summary level ' || nvl( p_Target_Level_Rec.target_level_name , ' ' ) ;
712 l_msg := l_msg || ' could not be created/updated.';
713 */
714
715 x_return_status := FND_API.G_RET_STS_SUCCESS;
716
717 Set_NULL( p_Target_Level_Rec => p_Target_Level_Rec
718 , x_Target_Level_Rec => l_Target_Level_Rec);
719
720
721 Validate_Target_Level
722 ( p_api_version => p_api_version
723 , p_validation_level => p_validation_level
724 , p_Target_Level_Rec => p_Target_Level_Rec
725 , x_return_status => x_return_status
726 , x_error_Tbl => x_error_Tbl
727 );
728
729
730 --
731 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
732 RAISE FND_API.G_EXC_ERROR;
733 END IF;
734 --
735 l_target_level_Id := Get_Level_Id_From_Short_Name(p_target_level_rec);
736 if (l_target_level_id is NOT NULL) then
737 --added last two params
738 l_error_tbl := x_error_tbl;
739 BIS_UTILITIES_PVT.Add_Error_Message
740 ( p_error_msg_name => 'BIS_TRG_LVL_SHORT_NAME_UNIQUE'
741 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
742 , p_error_proc_name => G_PKG_NAME||'.Create_Target_Level'
743 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
744 , p_error_table => l_error_tbl
745 , x_error_table => x_error_tbl
746 );
747 RAISE FND_API.G_EXC_ERROR;
748 end if;
749
750 l_target_level_Id := Get_Level_Id_From_dimlevels(p_target_level_rec);
751
752 if (l_target_level_id is NOT NULL) then
753 --added last two params
754 l_error_tbl := x_error_tbl;
755 BIS_UTILITIES_PVT.Add_Error_Message
756 ( p_error_msg_name => 'BIS_TRG_LVL_DIMLEVELS_UNIQUE'
757 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
758 , p_error_proc_name => G_PKG_NAME||'.Create_Target_Level'
759 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
760 , p_error_table => l_error_tbl
761 , x_error_table => x_error_tbl
762 );
763 RAISE FND_API.G_EXC_ERROR;
764 end if;
765
766 IF p_owner = BIS_UTILITIES_PUB.G_SEED_OWNER THEN
767 l_user_id := BIS_UTILITIES_PUB.G_SEED_USER_ID;
768 ELSE
769 l_user_id := fnd_global.user_id;
770 END IF;
771 l_login_id := fnd_global.LOGIN_ID;
772
773 IF (BIS_UTILITIES_PUB.Value_Missing(l_Target_Level_Rec.system_flag)
774 = FND_API.G_TRUE)
775 OR (BIS_UTILITIES_PUB.Value_NULL(l_Target_Level_Rec.system_flag)
776 = FND_API.G_TRUE)
777 THEN
778 l_Target_Level_Rec.system_flag := 'N';
779 END IF;
780
781
782
783 select bis_target_levels_s.NEXTVAL into l_id from dual;
784 --
785
786 insert into bis_TARGET_LEVELS (
787 TARGET_LEVEL_ID,
788 INDICATOR_ID,
789 SHORT_NAME,
790 ORG_LEVEL_ID,
791 TIME_LEVEL_ID,
792 DIMENSION1_LEVEL_ID,
793 DIMENSION2_LEVEL_ID,
794 DIMENSION3_LEVEL_ID,
795 DIMENSION4_LEVEL_ID,
796 DIMENSION5_LEVEL_ID,
797 DIMENSION6_LEVEL_ID,
798 DIMENSION7_LEVEL_ID,
799 WF_PROCESS,
800 WF_ITEM_TYPE,
801 REPORT_FUNCTION_ID,
802 DEFAULT_COMPUTING_FUNCTION_ID,
803 -- UNIT_OF_MEASURE,
804 DEFAULT_ROLE_ID,
805 DEFAULT_ROLE,
806 SYSTEM_FLAG,
807 CREATION_DATE,
808 CREATED_BY,
809 LAST_UPDATE_DATE,
810 LAST_UPDATED_BY,
811 LAST_UPDATE_LOGIN,
812 SOURCE
813 )
814 values
815 ( l_id
816 , l_Target_Level_Rec.Measure_ID
817 , l_Target_Level_Rec.Target_Level_Short_Name
818 , l_Target_Level_Rec.Org_Level_ID
819 , l_Target_Level_Rec.Time_Level_ID
820 , l_Target_Level_Rec.Dimension1_Level_ID
821 , l_Target_Level_Rec.Dimension2_Level_ID
822 , l_Target_Level_Rec.Dimension3_Level_ID
823 , l_Target_Level_Rec.Dimension4_Level_ID
824 , l_Target_Level_Rec.Dimension5_Level_ID
825 , l_Target_Level_Rec.Dimension6_Level_ID
826 , l_Target_Level_Rec.Dimension7_Level_ID
827 , l_Target_Level_Rec.Workflow_Process_Short_Name
828 , l_Target_Level_Rec.Workflow_Item_Type
829 , l_Target_Level_Rec.Report_Function_ID
830 , l_target_level_rec.Computing_function_ID
831 -- , l_Target_Level_Rec.Unit_Of_Measure
832 , l_Target_Level_Rec.Default_Notify_Resp_ID
833 , l_Target_Level_Rec.Default_Notify_Resp_short_name
834 , l_Target_Level_Rec.system_flag
835 , SYSDATE
836 , l_user_id
837 , SYSDATE
838 , l_user_id
839 , l_login_id
840 , l_target_level_rec.Source
841 );
842
843 insert into bis_TARGET_LEVELS_TL (
844 TARGET_LEVEL_ID,
845 LANGUAGE,
846 NAME,
847 DESCRIPTION,
848 UNIT_OF_MEASURE,
849 CREATION_DATE,
850 CREATED_BY,
851 LAST_UPDATE_DATE,
852 LAST_UPDATED_BY,
853 LAST_UPDATE_LOGIN,
854 TRANSLATED,
855 SOURCE_LANG
856 ) select
857 l_id
858 , L.LANGUAGE_CODE
859 , l_Target_Level_Rec.Target_Level_Name
860 , l_Target_Level_Rec.Description
861 , l_Target_Level_Rec.Unit_Of_Measure
862 , SYSDATE
863 , l_user_id
864 , SYSDATE
865 , l_user_id
866 , l_login_id
867 , 'Y'
868 , userenv('LANG')
869 from FND_LANGUAGES L
870 where L.INSTALLED_FLAG in ('I', 'B')
871 and not exists
872 (select NULL
873 from bis_TARGET_LEVELS_TL T
874 where T.TARGET_LEVEL_ID = l_id
875 and T.LANGUAGE = L.LANGUAGE_CODE);
876
877 if (p_commit = FND_API.G_TRUE) then
878 COMMIT;
879 end if;
880
881 --commented RAISE
882 EXCEPTION
883 --added this
884 WHEN DUPLICATE_DIMENSION_VALUE THEN
885 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
886 l_error_tbl := x_error_tbl;
887 BIS_UTILITIES_PVT.Add_Error_Message
888 ( p_error_msg_name => 'BIS_TAR_LEVEL_UNIQUENESS_ERROR'
889 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
890 , p_error_proc_name => G_PKG_NAME||'.Create_Target_Level'
891 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
892 , p_error_table => l_error_tbl
893 , x_error_table => x_error_tbl
894 );
895 -- BIS_UTILITIES_PUB.put_line(p_text => l_msg ) ; -- 2515991
896
897 when FND_API.G_EXC_ERROR then
898 x_return_status := FND_API.G_RET_STS_ERROR ;
899 -- BIS_UTILITIES_PUB.put_line(p_text => l_msg ) ;-- 2515991
900
901 when FND_API.G_EXC_UNEXPECTED_ERROR then
902 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
903 -- BIS_UTILITIES_PUB.put_line(p_text => l_msg ) ; -- 2515991
904
905 when others then
906 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
907 -- BIS_UTILITIES_PUB.put_line(p_text => l_msg ) ; -- 2515991
908
909 l_error_tbl := x_error_tbl;
910 BIS_UTILITIES_PVT.Add_Error_Message
911 ( p_error_msg_id => SQLCODE
912 , p_error_description => SQLERRM
913 , p_error_proc_name => G_PKG_NAME||'.Create_Target_Level'
914 , p_error_table => l_error_tbl
915 , x_error_table => x_error_tbl
916 );
917
918 END Create_Target_Level;
919 --
920 PROCEDURE Count_Target_Levels
921 ( p_api_version IN NUMBER
922 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
923 , x_count OUT NOCOPY NUMBER
924 , x_return_status OUT NOCOPY VARCHAR2
925 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
926 )
927 IS
928 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
929 BEGIN
930
931 x_return_status := FND_API.G_RET_STS_SUCCESS;
932 select count(1) into x_count
933 from bis_target_levels
934 where indicator_id = p_Measure_Rec.Measure_id;
935
936 --commented RAISE
937 EXCEPTION
938 when FND_API.G_EXC_ERROR then
939 x_return_status := FND_API.G_RET_STS_ERROR ;
940 --RAISE FND_API.G_EXC_ERROR;
941 when FND_API.G_EXC_UNEXPECTED_ERROR then
942 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
943 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
944 when others then
945 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
946 --added last two params
947 l_error_tbl := x_error_tbl;
948 BIS_UTILITIES_PVT.Add_Error_Message
949 ( p_error_msg_id => SQLCODE
950 , p_error_description => SQLERRM
951 , p_error_proc_name => G_PKG_NAME||'.Count_Target_Levels'
952 , p_error_table => l_error_tbl
953 , x_error_table => x_error_tbl
954 );
955 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
956
957 END Count_Target_Levels;
958 --
959 -- Gets All Indicator Levels
960 -- If information about the dimensions are not required, set all_info to
961 -- FALSE
962 PROCEDURE Retrieve_Target_Levels
963 ( p_api_version IN NUMBER
964 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
965 , p_Measure_Rec IN BIS_MEASURE_PUB.Measure_Rec_Type
966 , x_Target_Level_tbl OUT NOCOPY BIS_Target_Level_PUB.Target_Level_Tbl_Type
967 , x_return_status OUT NOCOPY VARCHAR2
968 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
969 )
970 IS
971
972 i NUMBER := 0;
973 l_Measure_id NUMBER;
974 l_Target_Level_rec BIS_Target_Level_PUB.Target_Level_Rec_Type;
975 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
976
977 cursor tar_level_bv IS
978 Select measure_id
979 , TARGET_LEVEL_ID
980 , TARGET_LEVEL_SHORT_NAME
981 , TARGET_LEVEL_NAME
982 , DESCRIPTION
983 , ORG_LEVEL_ID
984 , TIME_LEVEL_ID
985 , DIMENSION1_LEVEL_ID
986 , DIMENSION2_LEVEL_ID
987 , DIMENSION3_LEVEL_ID
988 , DIMENSION4_LEVEL_ID
989 , DIMENSION5_LEVEL_ID
990 , DIMENSION6_LEVEL_ID
991 , DIMENSION7_LEVEL_ID
992 , WORKFLOW_ITEM_TYPE
993 , WORKFLOW_PROCESS_SHORT_NAME
994 , DEFAULT_NOTIFY_RESP_ID
995 , DEFAULT_NOTIFY_RESP_SHORT_NAME
996 , COMPUTING_FUNCTION_ID
997 , REPORT_FUNCTION_ID
998 , UNIT_OF_MEASURE
999 , SYSTEM_FLAG
1000 from bisbv_target_levels
1001 where measure_id = l_Measure_id;
1002
1003 cursor tar_level_fv IS
1004 select TARGET_LEVEL_ID
1005 , TARGET_LEVEL_SHORT_NAME
1006 , TARGET_LEVEL_NAME
1007 , DESCRIPTION
1008 , MEASURE_ID
1009 , MEASURE_SHORT_NAME
1010 , MEASURE_NAME
1011 , ORG_LEVEL_ID
1012 , ORG_LEVEL_SHORT_NAME
1013 , ORG_LEVEL_NAME
1014 , TIME_LEVEL_ID
1015 , TIME_LEVEL_SHORT_NAME
1016 , TIME_LEVEL_NAME
1017 , DIMENSION1_LEVEL_ID
1018 , DIMENSION1_LEVEL_SHORT_NAME
1019 , DIMENSION1_LEVEL_NAME
1020 , DIMENSION2_LEVEL_ID
1021 , DIMENSION2_LEVEL_SHORT_NAME
1022 , DIMENSION2_LEVEL_NAME
1023 , DIMENSION3_LEVEL_ID
1024 , DIMENSION3_LEVEL_SHORT_NAME
1025 , DIMENSION3_LEVEL_NAME
1026 , DIMENSION4_LEVEL_ID
1027 , DIMENSION4_LEVEL_SHORT_NAME
1028 , DIMENSION4_LEVEL_NAME
1029 , DIMENSION5_LEVEL_ID
1030 , DIMENSION5_LEVEL_SHORT_NAME
1031 , DIMENSION5_LEVEL_NAME
1032 , DIMENSION6_LEVEL_ID
1033 , DIMENSION6_LEVEL_SHORT_NAME
1034 , DIMENSION6_LEVEL_NAME
1035 , DIMENSION7_LEVEL_ID
1036 , DIMENSION7_LEVEL_SHORT_NAME
1037 , DIMENSION7_LEVEL_NAME
1038 , WORKFLOW_ITEM_TYPE
1039 , WORKFLOW_PROCESS_SHORT_NAME
1040 , WORKFLOW_PROCESS_NAME
1041 , DEFAULT_NOTIFY_RESP_ID
1042 , DEFAULT_NOTIFY_RESP_SHORT_NAME
1043 , DEFAULT_NOTIFY_RESP_NAME
1044 , COMPUTING_FUNCTION_ID
1045 , COMPUTING_FUNCTION_NAME
1046 , COMPUTING_USER_FUNCTION_NAME
1047 , REPORT_FUNCTION_ID
1048 , REPORT_FUNCTION_NAME
1049 , REPORT_USER_FUNCTION_NAME
1050 , UNIT_OF_MEASURE
1051 , SYSTEM_FLAG
1052 , DATASET_ID
1053 from bisfv_target_levels
1054 where measure_id = l_Measure_id;
1055
1056 BEGIN
1057 x_return_status := FND_API.G_RET_STS_SUCCESS;
1058
1059 l_measure_id := p_Measure_Rec.Measure_id;
1060
1061 if (p_all_info = FND_API.G_FALSE) then
1062 FOR cr in tar_level_bv LOOP
1063 i := i+1;
1064
1065 l_Target_Level_rec.measure_id := cr.measure_id;
1066 l_Target_Level_rec.Target_Level_id := cr.TARGET_LEVEL_ID;
1067 l_Target_Level_rec.Target_Level_short_name := cr.TARGET_LEVEL_SHORT_NAME;
1068 l_Target_Level_rec.Target_Level_name := cr.TARGET_LEVEL_NAME;
1069 l_Target_Level_rec.description := cr.DESCRIPTION;
1070 l_Target_Level_rec.org_level_id := cr.ORG_LEVEL_ID;
1071 l_Target_Level_rec.time_level_id := cr.TIME_LEVEL_ID;
1072 l_Target_Level_rec.dimension1_level_id := cr.DIMENSION1_LEVEL_ID;
1073 l_Target_Level_rec.dimension2_level_id := cr.DIMENSION2_LEVEL_ID;
1074 l_Target_Level_rec.dimension3_level_id := cr.DIMENSION3_LEVEL_ID;
1075 l_Target_Level_rec.dimension4_level_id := cr.DIMENSION4_LEVEL_ID;
1076 l_Target_Level_rec.dimension5_level_id := cr.DIMENSION5_LEVEL_ID;
1077 l_Target_Level_rec.dimension6_level_id := cr.DIMENSION6_LEVEL_ID;
1078 l_Target_Level_rec.dimension7_level_id := cr.DIMENSION7_LEVEL_ID;
1079 l_Target_Level_rec.Workflow_Item_Type := cr.WORKFLOW_ITEM_TYPE;
1080 l_Target_Level_rec.Workflow_process_short_name :=
1081 cr.WORKFLOW_PROCESS_SHORT_NAME;
1082 l_Target_Level_rec.Default_Notify_Resp_ID := cr.DEFAULT_NOTIFY_RESP_ID;
1083 l_Target_Level_rec.Default_Notify_Resp_short_name :=
1084 cr.DEFAULT_NOTIFY_RESP_SHORT_NAME;
1085 l_Target_Level_rec.Computing_Function_Id := cr.COMPUTING_FUNCTION_ID;
1086 l_Target_Level_rec.Report_Function_ID := cr.REPORT_FUNCTION_ID;
1087 l_Target_Level_rec.Unit_Of_Measure := cr.UNIT_OF_MEASURE;
1088 l_Target_Level_rec.system_flag := cr.SYSTEM_FLAG;
1089
1090 x_Target_Level_Tbl(i) := l_Target_Level_rec;
1091 END LOOP;
1092
1093 else
1094
1095 FOR cr in tar_level_fv LOOP
1096 i := i+1;
1097
1098 l_target_level_rec.TARGET_LEVEL_ID := cr.TARGET_LEVEL_ID;
1099 l_target_level_rec.TARGET_LEVEL_SHORT_NAME := cr.TARGET_LEVEL_SHORT_NAME;
1100 l_target_level_rec.TARGET_LEVEL_NAME := cr.TARGET_LEVEL_NAME;
1101 l_target_level_rec.DESCRIPTION := cr.DESCRIPTION;
1102 l_target_level_rec.MEASURE_ID := cr.MEASURE_ID;
1103 l_target_level_rec.MEASURE_SHORT_NAME := cr.MEASURE_SHORT_NAME;
1104 l_target_level_rec.MEASURE_NAME := cr.MEASURE_NAME;
1105 l_target_level_rec.ORG_LEVEL_ID := cr.ORG_LEVEL_ID;
1106 l_target_level_rec.ORG_LEVEL_SHORT_NAME :=
1107 cr.ORG_LEVEL_SHORT_NAME;
1108 l_target_level_rec.ORG_LEVEL_NAME := cr.ORG_LEVEL_NAME;
1109 l_target_level_rec.TIME_LEVEL_ID := cr.TIME_LEVEL_ID;
1110 l_target_level_rec.TIME_LEVEL_SHORT_NAME :=
1111 cr.TIME_LEVEL_SHORT_NAME;
1112 l_target_level_rec.TIME_LEVEL_NAME := cr.TIME_LEVEL_NAME;
1113 l_target_level_rec.DIMENSION1_LEVEL_ID := cr.DIMENSION1_LEVEL_ID;
1114 l_target_level_rec.DIMENSION1_LEVEL_SHORT_NAME :=
1115 cr.DIMENSION1_LEVEL_SHORT_NAME;
1116 l_target_level_rec.DIMENSION1_LEVEL_NAME := cr.DIMENSION1_LEVEL_NAME;
1117 l_target_level_rec.DIMENSION2_LEVEL_ID := cr.DIMENSION2_LEVEL_ID;
1118 l_target_level_rec.DIMENSION2_LEVEL_SHORT_NAME :=
1119 cr.DIMENSION2_LEVEL_SHORT_NAME;
1120 l_target_level_rec.DIMENSION2_LEVEL_NAME := cr.DIMENSION2_LEVEL_NAME;
1121 l_target_level_rec.DIMENSION3_LEVEL_ID := cr.DIMENSION3_LEVEL_ID;
1122 l_target_level_rec.DIMENSION3_LEVEL_SHORT_NAME :=
1123 cr.DIMENSION3_LEVEL_SHORT_NAME;
1124 l_target_level_rec.DIMENSION3_LEVEL_NAME := cr.DIMENSION3_LEVEL_NAME;
1125 l_target_level_rec.DIMENSION4_LEVEL_ID := cr.DIMENSION4_LEVEL_ID;
1126 l_target_level_rec.DIMENSION4_LEVEL_SHORT_NAME :=
1127 cr.DIMENSION4_LEVEL_SHORT_NAME;
1128 l_target_level_rec.DIMENSION4_LEVEL_NAME := cr.DIMENSION4_LEVEL_NAME;
1129 l_target_level_rec.DIMENSION5_LEVEL_ID := cr.DIMENSION5_LEVEL_ID;
1130 l_target_level_rec.DIMENSION5_LEVEL_SHORT_NAME :=
1131 cr.DIMENSION5_LEVEL_SHORT_NAME;
1132 l_target_level_rec.DIMENSION5_LEVEL_NAME := cr.DIMENSION5_LEVEL_NAME;
1133 l_target_level_rec.DIMENSION6_LEVEL_ID := cr.DIMENSION6_LEVEL_ID;
1134 l_target_level_rec.DIMENSION6_LEVEL_SHORT_NAME :=
1135 cr.DIMENSION6_LEVEL_SHORT_NAME;
1136 l_target_level_rec.DIMENSION6_LEVEL_NAME := cr.DIMENSION6_LEVEL_NAME;
1137 l_target_level_rec.DIMENSION7_LEVEL_ID := cr.DIMENSION7_LEVEL_ID;
1138 l_target_level_rec.DIMENSION7_LEVEL_SHORT_NAME :=
1139 cr.DIMENSION7_LEVEL_SHORT_NAME;
1140 l_target_level_rec.DIMENSION7_LEVEL_NAME := cr.DIMENSION7_LEVEL_NAME;
1141 l_target_level_rec.WORKFLOW_ITEM_TYPE := cr.WORKFLOW_ITEM_TYPE;
1142 l_target_level_rec.WORKFLOW_PROCESS_SHORT_NAME :=
1143 cr.WORKFLOW_PROCESS_SHORT_NAME;
1144 l_target_level_rec.WORKFLOW_PROCESS_NAME := cr.WORKFLOW_PROCESS_NAME;
1145 l_target_level_rec.DEFAULT_NOTIFY_RESP_ID := cr.DEFAULT_NOTIFY_RESP_ID;
1146 l_target_level_rec.DEFAULT_NOTIFY_RESP_SHORT_NAME :=
1147 cr.DEFAULT_NOTIFY_RESP_SHORT_NAME;
1148 l_target_level_rec.DEFAULT_NOTIFY_RESP_NAME:=cr.DEFAULT_NOTIFY_RESP_NAME;
1149 l_target_level_rec.COMPUTING_FUNCTION_ID := cr.COMPUTING_FUNCTION_ID;
1150 l_target_level_rec.COMPUTING_FUNCTION_NAME := cr.COMPUTING_FUNCTION_NAME;
1151 l_target_level_rec.COMPUTING_USER_FUNCTION_NAME :=
1152 cr.COMPUTING_USER_FUNCTION_NAME;
1153 l_target_level_rec.REPORT_FUNCTION_ID := cr.REPORT_FUNCTION_ID;
1154 l_target_level_rec.REPORT_FUNCTION_NAME := cr.REPORT_FUNCTION_NAME;
1155 l_target_level_rec.REPORT_USER_FUNCTION_NAME :=
1156 cr.REPORT_USER_FUNCTION_NAME;
1157 l_target_level_rec.UNIT_OF_MEASURE := cr.UNIT_OF_MEASURE;
1158 l_target_level_rec.SYSTEM_FLAG := cr.SYSTEM_FLAG;
1159 l_Target_Level_rec.Dataset_ID := cr.DATASET_ID;
1160
1161 x_Target_Level_Tbl(i) := l_Target_Level_rec;
1162
1163 END LOOP;
1164
1165 IF tar_level_fv%ISOPEN THEN CLOSE tar_level_fv; END IF;
1166 IF tar_level_bv%ISOPEN THEN CLOSE tar_level_bv; END IF;
1167
1168 end if;
1169
1170 --added this check and message
1171 if ( i= 0) then
1172 l_error_tbl := x_error_tbl;
1173 BIS_UTILITIES_PVT.Add_Error_Message
1174 ( p_error_msg_name => 'BIS_INVALID_MEASURE_ID'
1175 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1176 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target_Levels'
1177 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
1178 , p_error_table => l_error_tbl
1179 , x_error_table => x_error_tbl
1180 );
1181 RAISE FND_API.G_EXC_ERROR;
1182 end if;
1183 --commented RAISE
1184 EXCEPTION
1185 when FND_API.G_EXC_ERROR then
1186 IF tar_level_fv%ISOPEN THEN CLOSE tar_level_fv; END IF;
1187 IF tar_level_bv%ISOPEN THEN CLOSE tar_level_bv; END IF;
1188 x_return_status := FND_API.G_RET_STS_ERROR ;
1189 -- RAISE FND_API.G_EXC_ERROR;
1190 when FND_API.G_EXC_UNEXPECTED_ERROR then
1191 IF tar_level_fv%ISOPEN THEN CLOSE tar_level_fv; END IF;
1192 IF tar_level_bv%ISOPEN THEN CLOSE tar_level_bv; END IF;
1193 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1194 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1195 when others then
1196 IF tar_level_fv%ISOPEN THEN CLOSE tar_level_fv; END IF;
1197 IF tar_level_bv%ISOPEN THEN CLOSE tar_level_bv; END IF;
1198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1199 --added last two params
1200 l_error_tbl := x_error_tbl;
1201 BIS_UTILITIES_PVT.Add_Error_Message
1202 ( p_error_msg_id => SQLCODE
1203 , p_error_description => SQLERRM
1204 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target_Levels'
1205 , p_error_table => l_error_tbl
1206 , x_error_table => x_error_tbl
1207 );
1208 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1209
1210 END Retrieve_Target_Levels;
1211 --
1212 --
1213 -- Gets Information for one Indicator Level
1214 -- If information about the dimension are not required, set all_info to FALSE.
1215 PROCEDURE Retrieve_Target_Level
1216 ( p_api_version IN NUMBER
1217 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
1218 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
1219 , x_Target_Level_Rec IN OUT NOCOPY BIS_Target_Level_PUB.Target_Level_Rec_Type
1220 , x_return_status OUT NOCOPY VARCHAR2
1221 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1222 )
1223 IS
1224 l_ID NUMBER;
1225 l_indicator_id NUMBER;
1226 l_Target_Level_rec BIS_Target_Level_PUB.Target_Level_Rec_Type;
1227 l_wf_process_short_name VARCHAR2(30);
1228 l_wf_process_name VARCHAR2(80);
1229 l_Def_Notify_Resp_short_name VARCHAR2(100);
1230 l_Def_Notify_Resp_name VARCHAR2(240);
1231 l_Def_Notify_Resp_ID NUMBER;
1232 l_dimension_level_rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
1233 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1234 BEGIN
1235 x_return_status := FND_API.G_RET_STS_SUCCESS;
1236
1237 l_Target_Level_rec := x_Target_Level_rec;
1238
1239 if (p_all_info = FND_API.G_FALSE) then
1240 Select measure_id
1241 , TARGET_LEVEL_ID
1242 , TARGET_LEVEL_SHORT_NAME
1243 , TARGET_LEVEL_NAME
1244 , DESCRIPTION
1245 , ORG_LEVEL_ID
1246 , TIME_LEVEL_ID
1247 , DIMENSION1_LEVEL_ID
1248 , DIMENSION2_LEVEL_ID
1249 , DIMENSION3_LEVEL_ID
1250 , DIMENSION4_LEVEL_ID
1251 , DIMENSION5_LEVEL_ID
1252 , DIMENSION6_LEVEL_ID
1253 , DIMENSION7_LEVEL_ID
1254 , WORKFLOW_ITEM_TYPE
1255 , WORKFLOW_PROCESS_SHORT_NAME
1256 , DEFAULT_NOTIFY_RESP_ID
1257 , DEFAULT_NOTIFY_RESP_SHORT_NAME
1258 , COMPUTING_FUNCTION_ID
1259 , REPORT_FUNCTION_ID
1260 , UNIT_OF_MEASURE
1261 , SYSTEM_FLAG
1262 into l_Target_Level_rec.measure_id
1263 , l_Target_Level_rec.Target_Level_id
1264 , l_Target_Level_rec.Target_Level_short_name
1265 , l_Target_Level_rec.Target_Level_name
1266 , l_Target_Level_rec.description
1267 , l_Target_Level_rec.org_level_id
1268 , l_Target_Level_rec.time_level_id
1269 , l_Target_Level_rec.dimension1_level_id
1270 , l_Target_Level_rec.dimension2_level_id
1271 , l_Target_Level_rec.dimension3_level_id
1272 , l_Target_Level_rec.dimension4_level_id
1273 , l_Target_Level_rec.dimension5_level_id
1274 , l_Target_Level_rec.dimension6_level_id
1275 , l_Target_Level_rec.dimension7_level_id
1276 , l_Target_Level_rec.Workflow_Item_Type
1277 , l_Target_Level_rec.Workflow_process_short_name
1278 , l_Target_Level_rec.Default_Notify_Resp_ID
1279 , l_Target_Level_rec.Default_Notify_Resp_short_name
1280 , l_Target_Level_rec.Computing_Function_Id
1281 , l_Target_Level_rec.Report_Function_ID
1282 , l_Target_Level_rec.Unit_Of_Measure
1283 , l_Target_Level_rec.system_flag
1284 from bisbv_target_levels
1285 where target_level_ID = p_Target_Level_rec.Target_Level_ID;
1286 -- 2528450
1287 ELSIF (NOT p_Target_Level_Rec.is_wf_info_needed) THEN
1288 SELECT INDICATOR_ID
1289 , TARGET_LEVEL_ID
1290 , SHORT_NAME
1291 , DIMENSION1_LEVEL_ID
1292 , DIMENSION2_LEVEL_ID
1293 , DIMENSION3_LEVEL_ID
1294 , DIMENSION4_LEVEL_ID
1295 , DIMENSION5_LEVEL_ID
1296 , DIMENSION6_LEVEL_ID
1297 , DIMENSION7_LEVEL_ID
1298 into l_target_level_rec.MEASURE_ID
1299 , l_target_level_rec.TARGET_LEVEL_ID
1300 , l_target_level_rec.TARGET_LEVEL_SHORT_NAME
1301 , l_target_level_rec.DIMENSION1_LEVEL_ID
1302 , l_target_level_rec.DIMENSION2_LEVEL_ID
1303 , l_target_level_rec.DIMENSION3_LEVEL_ID
1304 , l_target_level_rec.DIMENSION4_LEVEL_ID
1305 , l_target_level_rec.DIMENSION5_LEVEL_ID
1306 , l_target_level_rec.DIMENSION6_LEVEL_ID
1307 , l_target_level_rec.DIMENSION7_LEVEL_ID
1308 FROM BIS_TARGET_LEVELS
1309 WHERE TARGET_LEVEL_ID = p_Target_Level_rec.Target_Level_ID;
1310 -- end of 2528450
1311 else -- p_all_info = true and p_Target_Level_Rec.is_wf_info_needed = true
1312 select TARGET_LEVEL_ID
1313 , TARGET_LEVEL_SHORT_NAME
1314 , TARGET_LEVEL_NAME
1315 , DESCRIPTION
1316 , MEASURE_ID
1317 , MEASURE_SHORT_NAME
1318 , MEASURE_NAME
1319 , ORG_LEVEL_ID
1320 , ORG_LEVEL_SHORT_NAME
1321 , ORG_LEVEL_NAME
1322 , TIME_LEVEL_ID
1323 , TIME_LEVEL_SHORT_NAME
1324 , TIME_LEVEL_NAME
1325 , DIMENSION1_LEVEL_ID
1326 , DIMENSION1_LEVEL_SHORT_NAME
1327 , DIMENSION1_LEVEL_NAME
1328 , DIMENSION2_LEVEL_ID
1329 , DIMENSION2_LEVEL_SHORT_NAME
1330 , DIMENSION2_LEVEL_NAME
1331 , DIMENSION3_LEVEL_ID
1332 , DIMENSION3_LEVEL_SHORT_NAME
1333 , DIMENSION3_LEVEL_NAME
1334 , DIMENSION4_LEVEL_ID
1335 , DIMENSION4_LEVEL_SHORT_NAME
1336 , DIMENSION4_LEVEL_NAME
1337 , DIMENSION5_LEVEL_ID
1338 , DIMENSION5_LEVEL_SHORT_NAME
1339 , DIMENSION5_LEVEL_NAME
1340 , DIMENSION6_LEVEL_ID
1341 , DIMENSION6_LEVEL_SHORT_NAME
1342 , DIMENSION6_LEVEL_NAME
1343 , DIMENSION7_LEVEL_ID
1344 , DIMENSION7_LEVEL_SHORT_NAME
1345 , DIMENSION7_LEVEL_NAME
1346 , WORKFLOW_ITEM_TYPE
1347 , WORKFLOW_PROCESS_SHORT_NAME
1348 , WORKFLOW_PROCESS_NAME
1349 , DEFAULT_NOTIFY_RESP_ID
1350 , DEFAULT_NOTIFY_RESP_SHORT_NAME
1351 , DEFAULT_NOTIFY_RESP_NAME
1352 , COMPUTING_FUNCTION_ID
1353 , COMPUTING_FUNCTION_NAME
1354 , COMPUTING_USER_FUNCTION_NAME
1355 , REPORT_FUNCTION_ID
1356 , REPORT_FUNCTION_NAME
1357 , REPORT_USER_FUNCTION_NAME
1358 , UNIT_OF_MEASURE
1359 , SYSTEM_FLAG
1360 , DATASET_ID
1361 into l_target_level_rec.TARGET_LEVEL_ID
1362 , l_target_level_rec.TARGET_LEVEL_SHORT_NAME
1363 , l_target_level_rec.TARGET_LEVEL_NAME
1364 , l_target_level_rec.DESCRIPTION
1365 , l_target_level_rec.MEASURE_ID
1366 , l_target_level_rec.MEASURE_SHORT_NAME
1367 , l_target_level_rec.MEASURE_NAME
1368 , l_target_level_rec.ORG_LEVEL_ID
1369 , l_target_level_rec.ORG_LEVEL_SHORT_NAME
1370 , l_target_level_rec.ORG_LEVEL_NAME
1371 , l_target_level_rec.TIME_LEVEL_ID
1372 , l_target_level_rec.TIME_LEVEL_SHORT_NAME
1373 , l_target_level_rec.TIME_LEVEL_NAME
1374 , l_target_level_rec.DIMENSION1_LEVEL_ID
1375 , l_target_level_rec.DIMENSION1_LEVEL_SHORT_NAME
1376 , l_target_level_rec.DIMENSION1_LEVEL_NAME
1377 , l_target_level_rec.DIMENSION2_LEVEL_ID
1378 , l_target_level_rec.DIMENSION2_LEVEL_SHORT_NAME
1379 , l_target_level_rec.DIMENSION2_LEVEL_NAME
1380 , l_target_level_rec.DIMENSION3_LEVEL_ID
1381 , l_target_level_rec.DIMENSION3_LEVEL_SHORT_NAME
1382 , l_target_level_rec.DIMENSION3_LEVEL_NAME
1383 , l_target_level_rec.DIMENSION4_LEVEL_ID
1384 , l_target_level_rec.DIMENSION4_LEVEL_SHORT_NAME
1385 , l_target_level_rec.DIMENSION4_LEVEL_NAME
1386 , l_target_level_rec.DIMENSION5_LEVEL_ID
1387 , l_target_level_rec.DIMENSION5_LEVEL_SHORT_NAME
1388 , l_target_level_rec.DIMENSION5_LEVEL_NAME
1389 , l_target_level_rec.DIMENSION6_LEVEL_ID
1390 , l_target_level_rec.DIMENSION6_LEVEL_SHORT_NAME
1391 , l_target_level_rec.DIMENSION6_LEVEL_NAME
1392 , l_target_level_rec.DIMENSION7_LEVEL_ID
1393 , l_target_level_rec.DIMENSION7_LEVEL_SHORT_NAME
1394 , l_target_level_rec.DIMENSION7_LEVEL_NAME
1395 , l_target_level_rec.WORKFLOW_ITEM_TYPE
1396 , l_target_level_rec.WORKFLOW_PROCESS_SHORT_NAME
1397 , l_target_level_rec.WORKFLOW_PROCESS_NAME
1398 , l_target_level_rec.DEFAULT_NOTIFY_RESP_ID
1399 , l_target_level_rec.DEFAULT_NOTIFY_RESP_SHORT_NAME
1400 , l_target_level_rec.DEFAULT_NOTIFY_RESP_NAME
1401 , l_target_level_rec.COMPUTING_FUNCTION_ID
1402 , l_target_level_rec.COMPUTING_FUNCTION_NAME
1403 , l_target_level_rec.COMPUTING_USER_FUNCTION_NAME
1404 , l_target_level_rec.REPORT_FUNCTION_ID
1405 , l_target_level_rec.REPORT_FUNCTION_NAME
1406 , l_target_level_rec.REPORT_USER_FUNCTION_NAME
1407 , l_target_level_rec.UNIT_OF_MEASURE
1408 , l_target_level_rec.SYSTEM_FLAG
1409 , l_target_level_rec.DATASET_ID
1410 from bisfv_target_levels
1411 where target_level_ID = p_Target_Level_rec.Target_Level_ID;
1412 end if;
1413
1414 x_Target_Level_rec := l_Target_Level_rec;
1415
1416 --commented RAISE
1417 EXCEPTION
1418 --added NO DATA FOUND
1419 WHEN NO_DATA_FOUND THEN
1420 --added this error message
1421
1422 l_error_tbl := x_error_tbl;
1423 BIS_UTILITIES_PVT.Add_Error_Message
1424 ( p_error_msg_name => 'BIS_INVALID_TARGET_LEVEL_VALUE'
1425 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1426 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target_Level'
1427 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
1428 , p_error_table => l_error_tbl
1429 , x_error_table => x_error_tbl
1430 );
1431 x_return_status := FND_API.G_RET_STS_ERROR ;
1432 when FND_API.G_EXC_ERROR then
1433 x_return_status := FND_API.G_RET_STS_ERROR ;
1434 --RAISE FND_API.G_EXC_ERROR;
1435 when FND_API.G_EXC_UNEXPECTED_ERROR then
1436 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1437 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1438 when others then
1439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1440 --added last two params
1441 l_error_tbl := x_error_tbl;
1442 BIS_UTILITIES_PVT.Add_Error_Message
1443 ( p_error_msg_id => SQLCODE
1444 , p_error_description => SQLERRM
1445 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target_Level'
1446 , p_error_table => l_error_tbl
1447 , x_error_table => x_error_tbl
1448 );
1449 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1450
1451 END Retrieve_Target_Level;
1452 --
1453
1454 --====================================================================
1455 -- Retrieves target level records into table given
1456 -- multiple target level short names.
1457 -- This is used in KPI portlet as of now to retrieve
1458 -- details of all required target level short names with one call.
1459 -- As of now, the input table structure into this should start from 1 incremented by 1
1460 PROCEDURE retrieve_mult_targ_levels(
1461 p_api_version IN NUMBER
1462 ,p_target_level_tbl IN BIS_TARGET_LEVEL_PUB.target_level_tbl_type
1463 ,p_all_info IN VARCHAR2 := FND_API.G_TRUE
1464 ,x_target_level_tbl OUT NOCOPY BIS_TARGET_LEVEL_PUB.target_level_tbl_type
1465 ,x_return_status OUT NOCOPY VARCHAR2
1466 ,x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1467 )
1468 IS
1469 TYPE ref_cursor_type IS REF CURSOR;
1470 c_targ_level_details ref_cursor_type;
1471
1472 l_target_level_rec BIS_TARGET_LEVEL_PUB.target_level_rec_type;
1473 l_bind_var_tbl bind_variables_tbl_type;
1474 l_index NUMBER;
1475 l_sql VARCHAR2(32000);
1476 l_is_bind BOOLEAN := FALSE;
1477 l_is_execute BOOLEAN := FALSE;
1478 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1479
1480 BEGIN
1481
1482 x_return_status := FND_API.G_RET_STS_SUCCESS;
1483
1484 IF (p_target_level_tbl.COUNT = 0) THEN
1485 RETURN;
1486 END IF;
1487
1488 retrieve_sql(
1489 p_target_level_tbl => p_target_level_tbl
1490 ,x_is_bind => l_is_bind
1491 ,x_is_execute => l_is_execute
1492 ,x_sql => l_sql
1493 ,x_bind_variables_tbl => l_bind_var_tbl
1494 );
1495
1496 IF ( (l_is_execute) AND (l_sql IS NOT NULL) ) THEN
1497
1498 IF (c_targ_level_details%ISOPEN) THEN
1499 close c_targ_level_details;
1500 END IF;
1501
1502 IF (l_is_bind) THEN
1503 OPEN c_targ_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),
1504 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);
1505 ELSE
1506 OPEN c_targ_level_details FOR l_sql;
1507 END IF;
1508
1509 l_index := 1;
1510 LOOP
1511
1512 FETCH c_targ_level_details INTO
1513 l_target_level_rec.MEASURE_ID
1514 ,l_target_level_rec.TARGET_LEVEL_ID
1515 ,l_target_level_rec.TARGET_LEVEL_SHORT_NAME
1516 ,l_target_level_rec.DIMENSION1_LEVEL_ID
1517 ,l_target_level_rec.DIMENSION2_LEVEL_ID
1518 ,l_target_level_rec.DIMENSION3_LEVEL_ID
1519 ,l_target_level_rec.DIMENSION4_LEVEL_ID
1520 ,l_target_level_rec.DIMENSION5_LEVEL_ID
1521 ,l_target_level_rec.DIMENSION6_LEVEL_ID
1522 ,l_target_level_rec.DIMENSION7_LEVEL_ID;
1523
1524 EXIT WHEN c_targ_level_details%NOTFOUND;
1525
1526 x_target_level_tbl(l_index) := l_target_level_rec;
1527 l_index := l_index + 1;
1528 END LOOP;
1529 CLOSE c_targ_level_details;
1530
1531 END IF; -- end of execution
1532
1533 EXCEPTION
1534 WHEN OTHERS THEN
1535 IF (c_targ_level_details%ISOPEN) THEN
1536 CLOSE c_targ_level_details;
1537 END IF;
1538
1539 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1540
1541 l_error_tbl := x_error_tbl;
1542 BIS_UTILITIES_PVT.Add_Error_Message(
1543 p_error_msg_id => SQLCODE
1544 , p_error_description => SQLERRM
1545 , p_error_proc_name => G_PKG_NAME||'.retrieve_multiple_target_levels'
1546 , p_error_table => l_error_tbl
1547 , x_error_table => x_error_tbl
1548 );
1549 END retrieve_mult_targ_levels;
1550
1551 --====================================================================
1552
1553 PROCEDURE retrieve_sql(
1554 p_target_level_tbl IN BIS_TARGET_LEVEL_PUB.Target_Level_Tbl_Type
1555 ,x_is_bind OUT NOCOPY BOOLEAN
1556 ,x_is_execute OUT NOCOPY BOOLEAN
1557 ,x_sql OUT NOCOPY VARCHAR2
1558 ,x_bind_variables_tbl OUT NOCOPY bind_variables_tbl_type
1559 )
1560 IS
1561 l_all_targ_short_nm VARCHAR2(32000);
1562 BEGIN
1563 x_is_execute := FALSE;
1564
1565 x_sql := 'SELECT INDICATOR_ID
1566 , TARGET_LEVEL_ID
1567 , SHORT_NAME
1568 , DIMENSION1_LEVEL_ID
1569 , DIMENSION2_LEVEL_ID
1570 , DIMENSION3_LEVEL_ID
1571 , DIMENSION4_LEVEL_ID
1572 , DIMENSION5_LEVEL_ID
1573 , DIMENSION6_LEVEL_ID
1574 , DIMENSION7_LEVEL_ID
1575 FROM BIS_TARGET_LEVELS
1576 WHERE short_name IN (';
1577
1578 IF (p_target_level_tbl.COUNT <= 10) THEN
1579 x_is_bind := TRUE;
1580 FOR i IN p_target_level_tbl.FIRST .. p_target_level_tbl.LAST LOOP
1581 IF (p_target_level_tbl(i).target_level_short_name IS NOT NULL) THEN
1582 x_is_execute := TRUE;
1583 x_bind_variables_tbl(i) := p_target_level_tbl(i).target_level_short_name;
1584 ELSE
1585 x_bind_variables_tbl(i) := NULL;
1586 END IF;
1587 END LOOP;
1588
1589 FOR i IN p_target_level_tbl.LAST + 1 .. 10 LOOP
1590 x_bind_variables_tbl(i) := NULL;
1591 END LOOP;
1592
1593 x_sql := x_sql || ':1, :2, :3, :4, :5, :6, :7, :8, :9, :10)';
1594
1595 ELSE -- If more than 10, then use literals.
1596
1597 FOR i IN p_target_level_tbl.FIRST .. p_target_level_tbl.LAST LOOP
1598 IF (p_target_level_tbl(i).target_level_short_name IS NOT NULL) THEN
1599 x_is_execute := TRUE;
1600 IF (l_all_targ_short_nm IS NOT NULL) THEN
1601 l_all_targ_short_nm := l_all_targ_short_nm || ', ''' || p_target_level_tbl(i).target_level_short_name || '''';
1602 ELSE
1603 l_all_targ_short_nm := '''' || p_target_level_tbl(i).target_level_short_name || '''';
1604 END IF;
1605 END IF;
1606 END LOOP;
1607
1608 x_sql := x_sql || l_all_targ_short_nm || ')';
1609
1610 END IF;
1611
1612 EXCEPTION
1613 WHEN OTHERS THEN
1614 x_is_execute := FALSE;
1615 RETURN;
1616 END retrieve_sql;
1617
1618 --=================================================================
1619
1620 Procedure Check_Changed
1621 ( p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
1622 , p_Target_Level_Rec1 IN BIS_Target_Level_PUB.Target_Level_Rec_Type
1623 , p_up_loaded IN VARCHAR2 := FND_API.G_TRUE
1624 , x_changed OUT NOCOPY VARCHAR2
1625 )
1626 IS
1627 a BIS_Target_Level_PUB.Target_Level_Rec_Type;
1628 b BIS_Target_Level_PUB.Target_Level_Rec_Type;
1629
1630 BEGIN
1631
1632 a := p_target_level_rec1;
1633 b := p_target_level_rec;
1634
1635 Set_NULL( p_Target_Level_Rec => p_target_level_rec1
1636 , x_Target_Level_Rec => a);
1637 Set_NULL( p_Target_Level_Rec => p_target_level_rec
1638 , x_Target_Level_Rec => b);
1639
1640 /*
1641 htp.p( 'a.Measure_ID ='||a.Measure_ID ||', b = '||b.Measure_ID );
1642 htp.p( 'a.Target_Level_ID ='||a.Target_Level_ID ||', b = '||b.Target_Level_ID );
1643 htp.p( 'a.Target_Level_Short_Name ='||a.Target_Level_Short_Name ||', b = '||b.Target_Level_Short_Name );
1644 htp.p( 'a.Target_Level_Name ='||a.Target_Level_Name ||', b = '||b.Target_Level_Name );
1645 htp.p( 'a.Description ='||a.Description ||', b = '||b.Description );
1646 htp.p( 'a.Dimension1_Level_ID ='||a.Dimension1_Level_ID ||', b = '||b.Dimension1_Level_ID);
1647 htp.p( 'a.Dimension2_Level_ID ='||a.Dimension2_Level_ID ||', b = '||b.Dimension2_Level_ID );
1648 htp.p( 'a.Dimension3_Level_ID ='||a.Dimension3_Level_ID ||', b = '||b.Dimension3_Level_ID );
1649 htp.p( 'a.Dimension4_Level_ID ='||a.Dimension4_Level_ID ||', b = '||b.Dimension4_Level_ID );
1650 htp.p( 'a.Dimension5_Level_ID ='||a.Dimension5_Level_ID ||', b = '||b.Dimension5_Level_ID );
1651 htp.p( 'a.Unit_of_Measure ='||a.Unit_of_Measure ||', b = '||b.Unit_of_Measure );
1652 htp.p( 'a.WF_Process_Short_Name ='||a.workflow_Process_Short_Name||', b = '||b.workflow_Process_Short_Name);
1653 htp.p( 'a.workflow_item_type ='||a.workflow_item_type ||', b = '||b.workflow_item_type);
1654 htp.p( 'a.Def_Notify_Res.ID ='||a.Default_Notify_Resp_ID ||', b = '||b.Default_Notify_Resp_ID );
1655 htp.p( 'a.Default_Cmp_Target_ID ='||a.Computing_function_ID ||', b = '||b.computing_function_ID );
1656 htp.p( 'a.System_Flag ='||a.System_Flag ||', b = '||b.System_Flag );
1657 */
1658
1659 IF a.Measure_ID = b.Measure_ID
1660 AND a.Target_Level_ID = b.Target_Level_ID
1661 AND a.Target_Level_Short_Name = b.Target_Level_Short_Name
1662 AND a.Target_Level_Name = b.Target_Level_Name
1663 AND NVL(a.org_Level_Id,-999) = NVL(b.org_Level_Id,-999)
1664 AND NVL(a.time_Level_Id,-999) = NVL(b.time_Level_Id,-999)
1665 AND NVL(a.dimension1_Level_Id,-999) = NVL(b.dimension1_Level_Id,-999)
1666 AND NVL(a.dimension2_Level_Id,-999) = NVL(b.dimension2_Level_Id,-999)
1667 AND NVL(a.dimension3_Level_Id,-999) = NVL(b.dimension3_Level_Id,-999)
1668 AND NVL(a.dimension4_Level_Id,-999) = NVL(b.dimension4_Level_Id,-999)
1669 AND NVL(a.dimension5_Level_Id,-999) = NVL(b.dimension5_Level_Id,-999)
1670 AND NVL(a.dimension6_Level_Id,-999) = NVL(b.dimension6_Level_Id,-999)
1671 AND NVL(a.dimension7_Level_Id,-999) = NVL(b.dimension7_Level_Id,-999)
1672 AND NVL(a.Unit_Of_Measure, 'NULL') = NVL(b.Unit_Of_Measure,'NULL')
1673 THEN
1674
1675 IF p_up_loaded = FND_API.G_TRUE THEN
1676 x_changed := FND_API.G_FALSE;
1677
1678 ELSE
1679
1680 IF NVL(a.Workflow_Process_Short_Name,'NULL')
1681 = NVL(b.Workflow_Process_Short_Name,'NULL')
1682 AND NVL(a.Workflow_Item_Type,'NULL') = NVL(b.Workflow_Item_Type,'NULL')
1683 -- AND NVL(a.Default_Notify_Resp_ID,-999)
1684 -- = NVL(b.Default_Notify_Resp_ID,-999)
1685 AND NVL(a.Computing_Function_ID,-999)
1686 = NVL(b.Computing_Function_ID,-999)
1687 AND NVL(a.Description,'NULL') = NVL(b.Description,'NULL')
1688 THEN
1689 x_changed := FND_API.G_FALSE;
1690 ELSE
1691 x_changed := FND_API.G_TRUE;
1692 END IF;
1693 END IF;
1694
1695 ELSE
1696 x_changed := FND_API.G_TRUE;
1697 END IF;
1698
1699 EXCEPTION
1700 WHEN OTHERS THEN
1701 NULL;
1702 END Check_Changed;
1703 --
1704 PROCEDURE Update_Target_Level_Rec
1705 ( p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
1706 , p_Target_Level_Rec1 IN BIS_Target_Level_PUB.Target_Level_Rec_Type
1707 , p_up_loaded IN VARCHAR2 := FND_API.G_TRUE
1708 , x_Target_Level_Rec OUT NOCOPY BIS_Target_Level_PUB.Target_Level_Rec_Type
1709 , x_changed OUT NOCOPY VARCHAR2
1710 )
1711 IS
1712 a BIS_Target_Level_PUB.Target_Level_Rec_Type;
1713 b BIS_Target_Level_PUB.Target_Level_Rec_Type;
1714
1715 BEGIN
1716
1717 a := p_target_level_rec1;
1718 b := p_target_level_rec;
1719
1720 Set_NULL( p_Target_Level_Rec => p_target_level_rec1
1721 , x_Target_Level_Rec => a);
1722 Set_NULL( p_Target_Level_Rec => p_target_level_rec
1723 , x_Target_Level_Rec => b);
1724 x_Target_Level_Rec := a;
1725
1726 -- First check if the input record has changed from the original
1727 --
1728 Check_Changed
1729 ( p_Target_Level_Rec => b
1730 , p_Target_Level_Rec1 => a
1731 , p_up_loaded => p_up_loaded
1732 , x_changed => x_changed
1733 );
1734
1735 -- If input record changed, then update record
1736 --
1737 IF (BIS_UTILITIES_PUB.Value_Missing
1738 (p_Target_Level_Rec.Target_Level_Short_Name) = FND_API.G_FALSE) THEN
1739 x_target_level_rec.Target_Level_Short_Name :=
1740 p_target_level_rec.Target_Level_Short_Name;
1741 -- x_changed := FND_API.G_TRUE;
1742 END IF;
1743
1744 IF (BIS_UTILITIES_PUB.Value_Missing
1745 (p_Target_Level_Rec.Target_Level_Name) = FND_API.G_FALSE) THEN
1746 x_target_level_rec.Target_Level_Name :=
1747 p_target_level_rec.Target_Level_Name;
1748 -- x_changed := FND_API.G_TRUE;
1749 END IF;
1750
1751 IF (BIS_UTILITIES_PUB.Value_Missing
1752 (p_Target_Level_Rec.Description) = FND_API.G_FALSE) THEN
1753 x_target_level_rec.Description := p_target_level_rec.Description;
1754 -- x_changed := FND_API.G_TRUE;
1755 END IF;
1756
1757 IF (BIS_UTILITIES_PUB.Value_Missing
1758 (p_Target_Level_Rec.org_Level_ID) = FND_API.G_FALSE) THEN
1759 x_target_level_rec.org_Level_ID :=
1760 p_target_level_rec.org_Level_ID;
1761 -- x_changed := FND_API.G_TRUE;
1762 END IF;
1763
1764 IF (BIS_UTILITIES_PUB.Value_Missing
1765 (p_Target_Level_Rec.time_Level_ID) = FND_API.G_FALSE) THEN
1766 x_target_level_rec.time_Level_ID :=
1767 p_target_level_rec.time_Level_ID;
1768 -- x_changed := FND_API.G_TRUE;
1769 END IF;
1770
1771 IF (BIS_UTILITIES_PUB.Value_Missing
1772 (p_Target_Level_Rec.Dimension1_Level_ID) = FND_API.G_FALSE) THEN
1773 x_target_level_rec.Dimension1_Level_ID :=
1774 p_target_level_rec.Dimension1_Level_ID;
1775 -- x_changed := FND_API.G_TRUE;
1776 END IF;
1777
1778 IF (BIS_UTILITIES_PUB.Value_Missing
1779 (p_Target_Level_Rec.Dimension2_Level_ID) = FND_API.G_FALSE) THEN
1780 x_target_level_rec.Dimension2_Level_ID :=
1781 p_target_level_rec.Dimension2_Level_ID;
1782 -- x_changed := FND_API.G_TRUE;
1783 END IF;
1784
1785 IF (BIS_UTILITIES_PUB.Value_Missing
1786 (p_Target_Level_Rec.Dimension3_Level_ID) = FND_API.G_FALSE) THEN
1787 x_target_level_rec.Dimension3_Level_ID :=
1788 p_target_level_rec.Dimension3_Level_ID;
1789 -- x_changed := FND_API.G_TRUE;
1790 END IF;
1791
1792 IF (BIS_UTILITIES_PUB.Value_Missing
1793 (p_Target_Level_Rec.Dimension4_Level_ID) = FND_API.G_FALSE) THEN
1794 x_target_level_rec.Dimension4_Level_ID :=
1795 p_target_level_rec.Dimension4_Level_ID;
1796 -- x_changed := FND_API.G_TRUE;
1797 END IF;
1798
1799 IF (BIS_UTILITIES_PUB.Value_Missing
1800 (p_Target_Level_Rec.Dimension5_Level_ID) = FND_API.G_FALSE) THEN
1801 x_target_level_rec.Dimension5_Level_ID :=
1802 p_target_level_rec.Dimension5_Level_ID;
1803 -- x_changed := FND_API.G_TRUE;
1804 END IF;
1805
1806 IF (BIS_UTILITIES_PUB.Value_Missing
1807 (p_Target_Level_Rec.Dimension6_Level_ID) = FND_API.G_FALSE) THEN
1808 x_target_level_rec.Dimension6_Level_ID :=
1809 p_target_level_rec.Dimension6_Level_ID;
1810 -- x_changed := FND_API.G_TRUE;
1811 END IF;
1812
1813 IF (BIS_UTILITIES_PUB.Value_Missing
1814 (p_Target_Level_Rec.Dimension7_Level_ID) = FND_API.G_FALSE) THEN
1815 x_target_level_rec.Dimension7_Level_ID :=
1816 p_target_level_rec.Dimension7_Level_ID;
1817 -- x_changed := FND_API.G_TRUE;
1818 END IF;
1819
1820 IF (BIS_UTILITIES_PUB.Value_Missing
1821 (p_Target_Level_Rec.Workflow_Process_Short_Name) = FND_API.G_FALSE) THEN
1822 -- bug# 3735203
1823 IF(p_Target_Level_Rec.Workflow_Process_Short_Name <> '-1') THEN
1824 x_target_level_rec.Workflow_Process_Short_Name :=
1825 p_target_level_rec.Workflow_Process_Short_Name;
1826 ELSE
1827 x_target_level_rec.Workflow_Process_Short_Name := NULL;
1828 END IF;
1829 -- x_changed := FND_API.G_TRUE;
1830 END IF;
1831
1832 IF (BIS_UTILITIES_PUB.Value_Missing
1833 (p_Target_Level_Rec.Default_Notify_Resp_ID) = FND_API.G_FALSE) THEN
1834 x_target_level_rec.Default_Notify_Resp_ID :=
1835 p_target_level_rec.Default_Notify_Resp_ID;
1836 END IF;
1837
1838 IF (BIS_UTILITIES_PUB.Value_Missing
1839 (p_Target_Level_Rec.Default_Notify_Resp_short_name)=FND_API.G_FALSE) THEN
1840 x_target_level_rec.Default_Notify_Resp_short_name :=
1841 p_target_level_rec.Default_Notify_Resp_short_name;
1842 END IF;
1843
1844
1845 IF (BIS_UTILITIES_PUB.Value_Missing
1846 (p_Target_Level_Rec.Computing_function_ID) = FND_API.G_FALSE) THEN
1847 -- bug# 3735203
1848 IF (p_Target_Level_Rec.Computing_function_ID <> -1) THEN
1849 x_target_level_rec.Computing_function_ID :=
1850 p_target_level_rec.Computing_function_ID;
1851 ELSE
1852 x_target_level_rec.Computing_function_ID := NULL;
1853 END IF;
1854 -- x_changed := FND_API.G_TRUE;
1855 END IF;
1856
1857 IF (BIS_UTILITIES_PUB.Value_Missing
1858 (p_Target_Level_Rec.Workflow_Item_Type) = FND_API.G_FALSE) THEN
1859 -- bug# 3735203
1860 IF( p_Target_Level_Rec.Workflow_Item_Type <> '-1') THEN
1861 x_target_level_rec.Workflow_Item_Type :=
1862 p_target_level_rec.Workflow_Item_Type;
1863 ELSE
1864 x_target_level_rec.Workflow_Item_Type := NULL;
1865 END IF;
1866 -- x_changed := FND_API.G_TRUE;
1867 END IF;
1868
1869 IF (BIS_UTILITIES_PUB.Value_Missing
1870 (p_Target_Level_Rec.Report_Function_ID) = FND_API.G_FALSE) THEN
1871 x_target_level_rec.Report_Function_ID :=
1872 p_target_level_rec.Report_Function_ID;
1873 -- x_changed := FND_API.G_TRUE;
1874 END IF;
1875
1876 IF (BIS_UTILITIES_PUB.Value_Missing
1877 (p_Target_Level_Rec.Unit_Of_Measure) = FND_API.G_FALSE) THEN
1878 x_target_level_rec.Unit_Of_Measure :=
1879 p_target_level_rec.Unit_Of_Measure;
1880 -- x_changed := FND_API.G_TRUE;
1881 END IF;
1882
1883 IF (BIS_UTILITIES_PUB.Value_Missing
1884 (p_Target_Level_Rec.System_Flag) = FND_API.G_FALSE) THEN
1885 x_target_level_rec.System_Flag :=
1886 p_target_level_rec.System_Flag;
1887 -- x_changed := FND_API.G_TRUE;
1888 END IF;
1889
1890 IF (BIS_UTILITIES_PUB.Value_Missing
1891 (p_Target_Level_Rec.Source) = FND_API.G_FALSE) THEN
1892 x_target_level_rec.Source :=
1893 p_target_level_rec.Source;
1894 -- x_changed := FND_API.G_TRUE;
1895 END IF;
1896
1897 EXCEPTION
1898 WHEN OTHERS THEN
1899 htp.p('Exception in Update_Target_Level_Rec: '||SQLERRM);
1900 END Update_Target_Level_Rec;
1901 --
1902 -- Update_Target_Levels
1903 PROCEDURE Update_Target_Level
1904 ( p_api_version IN NUMBER
1905 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1906 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1907 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
1908 , x_return_status OUT NOCOPY VARCHAR2
1909 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1910 )
1911 IS
1912 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1913 BEGIN
1914
1915 Update_Target_Level
1916 ( p_api_version => p_api_version
1917 , p_commit => p_commit
1918 , p_validation_level => p_validation_level
1919 , p_Target_Level_Rec => p_Target_Level_Rec
1920 , p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
1921 , p_up_loaded => FND_API.G_FALSE
1922 , x_return_status => x_return_status
1923 , x_error_Tbl => x_error_Tbl
1924 );
1925
1926 --commented RAISE
1927 EXCEPTION
1928 when others then
1929 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1930 --added last two params
1931 l_error_tbl := x_error_tbl;
1932 BIS_UTILITIES_PVT.Add_Error_Message
1933 ( p_error_msg_id => SQLCODE
1934 , p_error_description => SQLERRM
1935 , p_error_proc_name => G_PKG_NAME||'.Update_Target_Level'
1936 , p_error_table => l_error_tbl
1937 , x_error_table => x_error_tbl
1938 );
1939 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1940
1941 END Update_Target_Level;
1942 --
1943 -- Update_Target_Levels
1944 PROCEDURE Update_Target_Level
1945 ( p_api_version IN NUMBER
1946 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1947 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
1948 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
1949 , p_owner IN VARCHAR2
1950 , p_up_loaded IN VARCHAR2 := FND_API.G_FALSE
1951 , x_return_status OUT NOCOPY VARCHAR2
1952 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1953 )
1954 IS
1955 l_user_id NUMBER;
1956 l_login_id NUMBER;
1957 l_Target_Level_Rec BIS_Target_Level_PUB.Target_Level_Rec_Type;
1958 l_Target_Level_orig BIS_Target_Level_PUB.Target_Level_Rec_Type;
1959 l_changed VARCHAR2(10) := FND_API.G_FALSE;
1960 l_target_level_id NUMBER;
1961 l_Target_Tbl BIS_TARGET_PUB.Target_Tbl_Type;
1962 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1963
1964 --added this
1965 DUPLICATE_DIMENSION_VALUE EXCEPTION;
1966 PRAGMA EXCEPTION_INIT(DUPLICATE_DIMENSION_VALUE, -1);
1967
1968 BEGIN
1969
1970 Validate_Target_Level
1971 ( p_api_version => p_api_version
1972 , p_validation_level => p_validation_level
1973 , p_Target_Level_Rec => p_Target_Level_Rec
1974 , x_return_status => x_return_status
1975 , x_error_Tbl => x_error_Tbl
1976 );
1977
1978 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1979 RAISE FND_API.G_EXC_ERROR;
1980 END IF;
1981
1982 BIS_TARGET_LEVEL_PVT.Retrieve_Target_Level
1983 ( p_api_version => 1.0
1984 , p_Target_Level_Rec => p_Target_Level_Rec
1985 , p_all_info => FND_API.G_FALSE
1986 , x_Target_Level_Rec => l_target_level_orig
1987 , x_return_status => x_return_status
1988 , x_error_Tbl => x_error_tbl
1989 );
1990
1991 --added this check
1992 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1993 l_error_tbl := x_error_tbl;
1994 BIS_UTILITIES_PVT.Add_Error_Message
1995 ( p_error_msg_name => 'BIS_INVALID_TAR_LEVEL_ID'
1996 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1997 , p_error_proc_name => G_PKG_NAME||'.Update_Target_Level'
1998 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
1999 , p_error_table => l_error_tbl
2000 , x_error_table => x_error_tbl
2001 );
2002 RAISE FND_API.G_EXC_ERROR;
2003 END IF;
2004
2005
2006
2007 Update_Target_Level_Rec
2008 ( p_target_level_rec => p_target_level_rec
2009 , p_target_level_rec1 => l_target_level_orig
2010 , p_up_loaded => p_up_loaded
2011 , x_target_level_rec => l_target_level_rec
2012 , x_changed => l_changed
2013 );
2014
2015 IF ( l_changed = FND_API.G_TRUE
2016 AND l_target_level_orig.System_Flag = 'Y')
2017 THEN
2018
2019 -- ADD error message
2020 --added last two params
2021 l_error_tbl := x_error_tbl;
2022 BIS_UTILITIES_PVT.Add_Error_Message
2023 ( p_error_msg_name => 'BIS_NO_CHANGE_SEED_TARGET_LVL'
2024 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2025 , p_error_proc_name => G_PKG_NAME||'.Update_Target_Level'
2026 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
2027 , p_error_table => l_error_tbl
2028 , x_error_table => x_error_tbl
2029 );
2030
2031 RAISE FND_API.G_EXC_ERROR;
2032 end if;
2033
2034 l_target_level_Id := Get_Level_Id_From_Short_Name(p_target_level_rec);
2035
2036 if (l_target_level_id is NOT NULL) then
2037 if (l_target_level_id <> p_target_level_Rec.Target_Level_Id) then
2038
2039 --added last two params
2040 l_error_tbl := x_error_tbl;
2041 BIS_UTILITIES_PVT.Add_Error_Message
2042 ( p_error_msg_name => 'BIS_TRG_LVL_SHORT_NAME_UNIQUE'
2043 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2044 , p_error_proc_name => G_PKG_NAME||'.Update_Target_Level'
2045 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
2046 , p_error_table => l_error_tbl
2047 , x_error_table => x_error_tbl
2048 );
2049 RAISE FND_API.G_EXC_ERROR;
2050 end if;
2051 end if;
2052
2053 BIS_TARGET_PVT.Retrieve_Targets( p_api_version
2054 , p_Target_Level_Rec
2055 , FND_API.G_FALSE
2056 , l_Target_Tbl
2057 , x_return_status
2058 , x_error_Tbl
2059 );
2060
2061 l_target_level_Id := Get_Level_Id_From_dimlevels(p_target_level_rec);
2062
2063 if (l_target_level_id is NOT NULL) then
2064 if (l_target_level_id <> p_target_level_Rec.Target_Level_Id) then
2065 --added last two params
2066 l_error_tbl := x_error_tbl;
2067 BIS_UTILITIES_PVT.Add_Error_Message
2068 ( p_error_msg_name => 'BIS_TRG_LVL_DIMLEVELS_UNIQUE'
2069 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2070 , p_error_proc_name => G_PKG_NAME||'.Update_Target_Level'
2071 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
2072 , p_error_table => l_error_tbl
2073 , x_error_table => x_error_tbl
2074 );
2075 RAISE FND_API.G_EXC_ERROR;
2076 end if;
2077 end if;
2078
2079 IF p_owner = BIS_UTILITIES_PUB.G_SEED_OWNER THEN
2080 l_user_id := BIS_UTILITIES_PUB.G_SEED_USER_ID;
2081 ELSE
2082 l_user_id := fnd_global.user_id;
2083 END IF;
2084 l_login_id := fnd_global.LOGIN_ID;
2085 --
2086 /*
2087 ----------------
2088 --Adding this for the source column
2089 IF (BIS_UTILITIES_PUB.Value_Missing(l_Target_Level_Rec.source)
2090 = FND_API.G_TRUE)
2091 OR (BIS_UTILITIES_PUB.Value_NULL(l_Target_Level_Rec.source)
2092 = FND_API.G_TRUE)
2093 THEN
2094 l_Target_Level_Rec.source := FND_PROFILE.value('BIS_SOURCE');
2095 END IF;
2096 ------------------
2097 */
2098
2099
2100 UPDATE BIS_TARGET_LEVELS
2101 set
2102 INDICATOR_ID = l_Target_Level_Rec.Measure_ID
2103 , SHORT_NAME = l_Target_Level_Rec.Target_Level_Short_Name
2104 , ORG_LEVEL_ID = l_Target_Level_Rec.org_Level_ID
2105 , TIME_LEVEL_ID = l_Target_Level_Rec.time_Level_ID
2106 , DIMENSION1_LEVEL_ID = l_Target_Level_Rec.Dimension1_Level_ID
2107 , DIMENSION2_LEVEL_ID = l_Target_Level_Rec.Dimension2_Level_ID
2108 , DIMENSION3_LEVEL_ID = l_Target_Level_Rec.Dimension3_Level_ID
2109 , DIMENSION4_LEVEL_ID = l_Target_Level_Rec.Dimension4_Level_ID
2110 , DIMENSION5_LEVEL_ID = l_Target_Level_Rec.Dimension5_Level_ID
2111 , DIMENSION6_LEVEL_ID = l_Target_Level_Rec.Dimension6_Level_ID
2112 , DIMENSION7_LEVEL_ID = l_Target_Level_Rec.Dimension7_Level_ID
2113 , WF_PROCESS = l_Target_Level_Rec.Workflow_Process_Short_Name
2114 , WF_ITEM_TYPE = l_Target_Level_Rec.Workflow_Item_Type
2115 , REPORT_FUNCTION_ID = l_Target_Level_Rec.Report_Function_ID
2116 -- , UNIT_OF_MEASURE = l_Target_Level_Rec.Unit_Of_Measure
2117 , DEFAULT_ROLE_ID = l_Target_Level_Rec.Default_Notify_Resp_ID
2118 , DEFAULT_ROLE = l_Target_Level_Rec.Default_Notify_Resp_short_name
2119 , SYSTEM_FLAG = l_Target_Level_Rec.System_Flag
2120 , DEFAULT_COMPUTING_FUNCTION_ID = l_target_level_rec.Computing_function_ID
2121 -- , CREATION_DATE = SYSDATE
2122 , CREATED_BY = l_user_id
2123 , LAST_UPDATE_DATE = SYSDATE
2124 , LAST_UPDATED_BY = l_user_id
2125 , LAST_UPDATE_LOGIN = l_login_id
2126 , SOURCE = l_Target_Level_Rec.Source
2127 where TARGET_LEVEL_ID = l_Target_Level_Rec.Target_Level_Id;
2128
2129 Translate_Target_Level
2130 ( p_api_version => p_api_version
2131 , p_commit => p_commit
2132 , p_validation_level => p_validation_level
2133 , p_Target_Level_Rec => l_Target_Level_Rec
2134 , p_owner => p_owner
2135 , x_return_status => x_return_status
2136 , x_error_Tbl => x_error_Tbl
2137 );
2138
2139 if (p_commit = FND_API.G_TRUE) then
2140 COMMIT;
2141 end if;
2142
2143 --commented RAISE
2144 EXCEPTION
2145 --added this
2146 WHEN DUPLICATE_DIMENSION_VALUE THEN
2147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2148 l_error_tbl := x_error_tbl;
2149 BIS_UTILITIES_PVT.Add_Error_Message
2150 ( p_error_msg_name => 'BIS_TAR_LEVEL_UNIQUENESS_ERROR'
2151 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2152 , p_error_proc_name => G_PKG_NAME||'.Update_Target_Level'
2153 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
2154 , p_error_table => l_error_tbl
2155 , x_error_table => x_error_tbl
2156 );
2157 when FND_API.G_EXC_ERROR then
2158 x_return_status := FND_API.G_RET_STS_ERROR ;
2159 --RAISE FND_API.G_EXC_ERROR;
2160 when FND_API.G_EXC_UNEXPECTED_ERROR then
2161 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2162 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2163 when others then
2164 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2165 --added last two params
2166 l_error_tbl := x_error_tbl;
2167 BIS_UTILITIES_PVT.Add_Error_Message
2168 ( p_error_msg_id => SQLCODE
2169 , p_error_description => SQLERRM
2170 , p_error_proc_name => G_PKG_NAME||'.Update_Target_Level'
2171 , p_error_table => l_error_tbl
2172 , x_error_table => x_error_tbl
2173 );
2174 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2175
2176 END Update_Target_Level;
2177 --
2178 --
2179 Procedure Translate_Target_Level
2180 ( p_api_version IN NUMBER
2181 , p_commit IN VARCHAR2 := FND_API.G_FALSE
2182 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2183 , p_Target_Level_Rec IN BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type
2184 , x_return_status OUT NOCOPY VARCHAR2
2185 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2186 )
2187 IS
2188 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2189 BEGIN
2190
2191 Translate_Target_Level
2192 ( p_api_version => p_api_version
2193 , p_commit => p_commit
2194 , p_validation_level => p_validation_level
2195 , p_Target_Level_Rec => p_Target_Level_Rec
2196 , p_owner => BIS_UTILITIES_PUB.G_CUSTOM_OWNER
2197 , x_return_status => x_return_status
2198 , x_error_Tbl => x_error_Tbl
2199 );
2200
2201 --commented RAISE
2202 EXCEPTION
2203 when others then
2204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2205 --added last two params
2206 l_error_tbl := x_error_tbl;
2207 BIS_UTILITIES_PVT.Add_Error_Message
2208 ( p_error_msg_id => SQLCODE
2209 , p_error_description => SQLERRM
2210 , p_error_proc_name => G_PKG_NAME||'.Translate_Target_Level'
2211 , p_error_table => l_error_tbl
2212 , x_error_table => x_error_tbl
2213 );
2214 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2215
2216 END Translate_Target_Level;
2217 --
2218 Procedure Translate_Target_Level
2219 ( p_api_version IN NUMBER
2220 , p_commit IN VARCHAR2 := FND_API.G_FALSE
2221 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2222 , p_Target_Level_Rec IN BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type
2223 , p_owner IN VARCHAR2
2224 , x_return_status OUT NOCOPY VARCHAR2
2225 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2226 )
2227 IS
2228 l_user_id NUMBER;
2229 l_login_id NUMBER;
2230 l_count NUMBER := 0;
2231 l_changed VARCHAR2(10) := FND_API.G_FALSE;
2232 l_Target_Level_rec BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
2233 l_Target_Level_orig BIS_TARGET_LEVEL_PUB.Target_Level_Rec_Type;
2234 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2235
2236 BEGIN
2237
2238 l_Target_Level_rec := p_Target_Level_Rec;
2239
2240 BIS_TARGET_LEVEL_PVT.Retrieve_Target_Level
2241 ( p_api_version => 1.0
2242 , p_Target_Level_Rec => l_Target_Level_Rec
2243 , p_all_info => FND_API.G_FALSE
2244 , x_Target_Level_Rec => l_target_level_orig
2245 , x_return_status => x_return_status
2246 , x_error_Tbl => x_error_tbl
2247 );
2248
2249 Validate_Target_Level
2250 ( p_api_version => p_api_version
2251 , p_validation_level => p_validation_level
2252 , p_Target_Level_Rec => l_Target_Level_orig
2253 , x_return_status => x_return_status
2254 , x_error_Tbl => x_error_Tbl
2255 );
2256 --
2257 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2258 RAISE FND_API.G_EXC_ERROR;
2259 END IF;
2260 --
2261
2262 /*
2263 if (l_target_level_Orig.target_level_id is NOT NULL) then
2264 if (l_target_level_Orig.target_level_id <>
2265 l_Target_Level_Rec.Target_Level_Id) then
2266 --added last two params
2267 l_error_tbl := x_error_tbl;
2268 BIS_UTILITIES_PVT.Add_Error_Message
2269 ( p_error_msg_name => 'BIS_TARGET_LEVEL_SHORT_NAME_UNIQUE'
2270 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2271 , p_error_proc_name => G_PKG_NAME||'.Translate_Target_Level'
2272 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
2273 , p_error_table => l_error_tbl
2274 , x_error_table => x_error_tbl
2275 );
2276 RAISE FND_API.G_EXC_ERROR;
2277 end if;
2278 end if;
2279
2280 Update_Target_Level_Rec
2281 ( p_target_level_rec => p_target_level_rec
2282 , p_target_level_rec1 => l_target_level_orig
2283 , x_target_level_rec => l_target_level_rec
2284 , x_changed => l_changed
2285 );
2286 */
2287
2288 l_target_level_rec.target_level_id := l_target_level_Orig.target_level_id;
2289 --
2290 IF p_owner = BIS_UTILITIES_PUB.G_SEED_OWNER THEN
2291 l_user_id := BIS_UTILITIES_PUB.G_SEED_USER_ID;
2292 ELSE
2293 l_user_id := fnd_global.user_id;
2294 END IF;
2295
2296 l_login_id := fnd_global.LOGIN_ID;
2297 --
2298
2299 Update BIS_TARGET_LEVELS_TL
2300 set
2301 NAME = l_Target_Level_Rec.Target_Level_Name
2302 , DESCRIPTION = l_Target_Level_Rec.description
2303 , UNIT_OF_MEASURE = l_Target_Level_Rec.Unit_Of_Measure
2304 , LAST_UPDATE_DATE = SYSDATE
2305 , LAST_UPDATED_BY = l_user_id
2306 , LAST_UPDATE_LOGIN = l_login_id
2307 , SOURCE_LANG = userenv('LANG')
2308 where TARGET_LEVEL_ID = l_Target_Level_Rec.Target_Level_Id
2309 and userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
2310
2311 if (p_commit = FND_API.G_TRUE) then
2312 COMMIT;
2313 end if;
2314
2315 --commented RAISE
2316 EXCEPTION
2317 WHEN NO_DATA_FOUND THEN
2318 x_return_status := FND_API.G_RET_STS_ERROR ;
2319 -- RAISE FND_API.G_EXC_ERROR;
2320 when FND_API.G_EXC_ERROR then
2321 x_return_status := FND_API.G_RET_STS_ERROR ;
2322 -- RAISE FND_API.G_EXC_ERROR;
2323 when FND_API.G_EXC_UNEXPECTED_ERROR then
2324 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2325 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2326 when others then
2327 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2328 --added last two params
2329 l_error_tbl := x_error_tbl;
2330 BIS_UTILITIES_PVT.Add_Error_Message
2331 ( p_error_msg_id => SQLCODE
2332 , p_error_description => SQLERRM
2333 , p_error_proc_name => G_PKG_NAME||'.Translate_Target_Level'
2334 , p_error_table => l_error_tbl
2335 , x_error_table => x_error_tbl
2336 );
2337 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2338
2339 END Translate_Target_Level;
2340 --
2341 -- deletes one Target_Level
2342 PROCEDURE Delete_Target_Level
2343 ( p_api_version IN NUMBER
2344 , p_force_delete IN NUMBER := 0--gbhaloti #3148615
2345 , p_commit IN VARCHAR2 := FND_API.G_FALSE
2346 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
2347 , x_return_status OUT NOCOPY VARCHAR2
2348 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2349 )
2350 IS
2351 l_Target_Tbl BIS_TARGET_PUB.Target_Tbl_Type;
2352 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2353 BEGIN
2354
2355 x_return_status := FND_API.G_RET_STS_SUCCESS;
2356 BIS_TARGET_PVT.Retrieve_Targets( p_api_version
2357 , p_Target_Level_Rec
2358 , FND_API.G_FALSE
2359 , l_Target_Tbl
2360 , x_return_status
2361 , x_error_Tbl
2362 );
2363
2364 /*
2365 --added this check and message and RAISE
2366 if(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2367 RAISE FND_API.G_EXC_ERROR;
2368 end if;
2369 */
2370 if (l_Target_Tbl.COUNT > 0 AND p_force_delete = 0) then
2371 --added last two params
2372 l_error_tbl := x_error_tbl;
2373 BIS_UTILITIES_PVT.Add_Error_Message
2374 ( p_error_msg_name => 'BIS_NO_DELETE_TARGET_LEVEL'
2375 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
2376 , p_error_proc_name => G_PKG_NAME||'.Delete_Target_Level'
2377 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
2378 , p_error_table => l_error_tbl
2379 , x_error_table => x_error_tbl
2380 );
2381
2382
2383 RAISE FND_API.G_EXC_ERROR;
2384 end if;
2385
2386 delete from bis_TARGET_LEVELS
2387 where TARGET_LEVEL_ID = p_Target_Level_Rec.Target_Level_Id;
2388
2389 delete from bis_TARGET_LEVELS_TL
2390 where TARGET_LEVEL_ID = p_Target_Level_Rec.Target_Level_Id;
2391
2392 BIS_MEASURE_SECURITY_PVT.Delete_Measure_Security( p_api_version
2393 , p_commit
2394 , p_Target_Level_Rec
2395 , x_return_status
2396 , x_error_Tbl
2397 );
2398
2399 delete from bis_indicator_resps
2400 where target_level_id = p_Target_Level_Rec.Target_Level_Id;
2401
2402 if (p_commit = FND_API.G_TRUE) then
2403 COMMIT;
2404 end if;
2405
2406 --commented RAISE
2407 EXCEPTION
2408 when FND_API.G_EXC_ERROR then
2409 x_return_status := FND_API.G_RET_STS_ERROR ;
2410 -- RAISE FND_API.G_EXC_ERROR;
2411 when FND_API.G_EXC_UNEXPECTED_ERROR then
2412 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2413 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2414 when others then
2415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2416 --added last two params
2417 l_error_tbl := x_error_tbl;
2418 BIS_UTILITIES_PVT.Add_Error_Message
2419 ( p_error_msg_id => SQLCODE
2420 , p_error_description => SQLERRM
2421 , p_error_proc_name => G_PKG_NAME||'.Delete_Target_Level'
2422 , p_error_table => l_error_tbl
2423 , x_error_table => x_error_tbl
2424 );
2425 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2426
2427 END Delete_Target_Level;
2428 --
2429 -- Validates measure
2430 --PROCEDURE @Target_Level
2431 PROCEDURE Validate_Target_Level
2432 ( p_api_version IN NUMBER
2433 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
2434 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
2435 , x_return_status OUT NOCOPY VARCHAR2
2436 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2437 )
2438 IS
2439 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2440 l_error VARCHAR2(10) := FND_API.G_FALSE;
2441 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
2442 BEGIN
2443
2444 x_return_status := FND_API.G_RET_STS_SUCCESS;
2445
2446 BEGIN
2447 BIS_TARGET_LEVEL_VALIDATE_PVT.Validate_org_Level_Id
2448 ( p_api_version => p_api_version
2449 , p_validation_level => p_validation_level
2450 , p_Target_Level_Rec => p_Target_Level_Rec
2451 , x_return_status => x_return_status
2452 , x_error_Tbl => l_error_Tbl
2453 );
2454 --EXCEPTION
2455 --when FND_API.G_EXC_ERROR then
2456 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2457 l_error := FND_API.G_TRUE;
2458 l_error_Tbl_p := x_error_Tbl;
2459 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
2460 , l_error_Tbl
2461 , x_error_tbl
2462 );
2463 x_return_status := FND_API.G_RET_STS_ERROR;
2464 END IF;
2465 END;
2466
2467 BEGIN
2468 BIS_TARGET_LEVEL_VALIDATE_PVT.Validate_time_Level_Id
2469 ( p_api_version => p_api_version
2470 , p_validation_level => p_validation_level
2471 , p_Target_Level_Rec => p_Target_Level_Rec
2472 , x_return_status => x_return_status
2473 , x_error_Tbl => l_error_Tbl
2474 );
2475 --EXCEPTION
2476 --when FND_API.G_EXC_ERROR then
2477 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2478 l_error := FND_API.G_TRUE;
2479 l_error_Tbl_p := x_error_Tbl;
2480 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
2481 , l_error_Tbl
2482 , x_error_tbl
2483 );
2484 x_return_status := FND_API.G_RET_STS_ERROR;
2485 END IF;
2486 END;
2487
2488 BEGIN
2489 BIS_TARGET_LEVEL_VALIDATE_PVT.Validate_Dimension1_Level_Id
2490 ( p_api_version => p_api_version
2491 , p_validation_level => p_validation_level
2492 , p_Target_Level_Rec => p_Target_Level_Rec
2493 , x_return_status => x_return_status
2494 , x_error_Tbl => l_error_Tbl
2495 );
2496 --EXCEPTION
2497 --when FND_API.G_EXC_ERROR then
2498 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2499 l_error := FND_API.G_TRUE;
2500 l_error_Tbl_p := x_error_Tbl;
2501 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
2502 , l_error_Tbl
2503 , x_error_tbl
2504 );
2505 x_return_status := FND_API.G_RET_STS_ERROR;
2506 END IF;
2507 END;
2508
2509 BEGIN
2510 BIS_TARGET_LEVEL_VALIDATE_PVT.Validate_Dimension2_Level_Id
2511 ( p_api_version => p_api_version
2512 , p_validation_level => p_validation_level
2513 , p_Target_Level_Rec => p_Target_Level_Rec
2514 , x_return_status => x_return_status
2515 , x_error_Tbl => l_error_Tbl
2516 );
2517 --EXCEPTION
2518 --when FND_API.G_EXC_ERROR then
2519 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2520 l_error := FND_API.G_TRUE;
2521 l_error_Tbl_p := x_error_Tbl;
2522 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
2523 , l_error_Tbl
2524 , x_error_tbl
2525 );
2526 x_return_status := FND_API.G_RET_STS_ERROR;
2527 END IF;
2528 END;
2529
2530 BEGIN
2531 BIS_TARGET_LEVEL_VALIDATE_PVT.Validate_Dimension3_Level_Id
2532 ( p_api_version => p_api_version
2533 , p_validation_level => p_validation_level
2534 , p_Target_Level_Rec => p_Target_Level_Rec
2535 , x_return_status => x_return_status
2536 , x_error_Tbl => l_error_Tbl
2537 );
2538 --EXCEPTION
2539 --when FND_API.G_EXC_ERROR then
2540 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2541 l_error := FND_API.G_TRUE;
2542 l_error_Tbl_p := x_error_Tbl;
2543 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
2544 , l_error_Tbl
2545 , x_error_tbl
2546 );
2547 x_return_status := FND_API.G_RET_STS_ERROR;
2548 END IF;
2549 END;
2550
2551 BEGIN
2552 BIS_TARGET_LEVEL_VALIDATE_PVT.Validate_Dimension4_Level_Id
2553 ( p_api_version => p_api_version
2554 , p_validation_level => p_validation_level
2555 , p_Target_Level_Rec => p_Target_Level_Rec
2556 , x_return_status => x_return_status
2557 , x_error_Tbl => l_error_Tbl
2558 );
2559 -- EXCEPTION
2560 --when FND_API.G_EXC_ERROR then
2561 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2562 l_error := FND_API.G_TRUE;
2563 l_error_Tbl_p := x_error_Tbl;
2564 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
2565 , l_error_Tbl
2566 , x_error_tbl
2567 );
2568 x_return_status := FND_API.G_RET_STS_ERROR;
2569 END IF;
2570 END;
2571
2572 BEGIN
2573 BIS_TARGET_LEVEL_VALIDATE_PVT.Validate_Dimension5_Level_Id
2574 ( p_api_version => p_api_version
2575 , p_validation_level => p_validation_level
2576 , p_Target_Level_Rec => p_Target_Level_Rec
2577 , x_return_status => x_return_status
2578 , x_error_Tbl => l_error_Tbl
2579 );
2580 --EXCEPTION
2581 --when FND_API.G_EXC_ERROR then
2582 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2583 l_error := FND_API.G_TRUE;
2584 l_error_Tbl_p := x_error_Tbl;
2585 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
2586 , l_error_Tbl
2587 , x_error_tbl
2588 );
2589 x_return_status := FND_API.G_RET_STS_ERROR;
2590 END IF;
2591 END;
2592
2593 BEGIN
2594 BIS_TARGET_LEVEL_VALIDATE_PVT.Validate_Dimension6_Level_Id
2595 ( p_api_version => p_api_version
2596 , p_validation_level => p_validation_level
2597 , p_Target_Level_Rec => p_Target_Level_Rec
2598 , x_return_status => x_return_status
2599 , x_error_Tbl => l_error_Tbl
2600 );
2601 -- EXCEPTION
2602 -- when FND_API.G_EXC_ERROR then
2603 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2604 l_error := FND_API.G_TRUE;
2605 l_error_Tbl_p := x_error_Tbl;
2606 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
2607 , l_error_Tbl
2608 , x_error_tbl
2609 );
2610 x_return_status := FND_API.G_RET_STS_ERROR;
2611 END IF;
2612 END;
2613
2614 BEGIN
2615 BIS_TARGET_LEVEL_VALIDATE_PVT.Validate_Dimension7_Level_Id
2616 ( p_api_version => p_api_version
2617 , p_validation_level => p_validation_level
2618 , p_Target_Level_Rec => p_Target_Level_Rec
2619 , x_return_status => x_return_status
2620 , x_error_Tbl => l_error_Tbl
2621 );
2622 -- EXCEPTION
2623 -- when FND_API.G_EXC_ERROR then
2624 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2625 l_error := FND_API.G_TRUE;
2626 l_error_Tbl_p := x_error_Tbl;
2627 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
2628 , l_error_Tbl
2629 , x_error_tbl
2630 );
2631 x_return_status := FND_API.G_RET_STS_ERROR;
2632 END IF;
2633 END;
2634
2635 BEGIN
2636 -- bug# 3735203
2637 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Target_Level_Rec.Workflow_Process_Short_Name) =FND_API.G_TRUE ) THEN
2638 IF(p_Target_Level_Rec.Workflow_Process_Short_Name <> '-1') THEN
2639 BIS_TARGET_LEVEL_VALIDATE_PVT.Validate_WF_Process_Short_Name
2640 ( p_api_version => p_api_version
2641 , p_validation_level => p_validation_level
2642 , p_Target_Level_Rec => p_Target_Level_Rec
2643 , x_return_status => x_return_status
2644 , x_error_Tbl => l_error_Tbl
2645 );
2646 END IF;
2647 END IF;
2648
2649 -- EXCEPTION
2650 -- when FND_API.G_EXC_ERROR then
2651 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2652 l_error := FND_API.G_TRUE;
2653 l_error_Tbl_p := x_error_Tbl;
2654 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
2655 , l_error_Tbl
2656 , x_error_tbl
2657 );
2658 x_return_status := FND_API.G_RET_STS_ERROR;
2659 END IF;
2660 END;
2661
2662 BEGIN
2663 BIS_TARGET_LEVEL_VALIDATE_PVT.Validate_Def_Notify_Resp_Id
2664 ( p_api_version => p_api_version
2665 , p_validation_level => p_validation_level
2666 , p_Target_Level_Rec => p_Target_Level_Rec
2667 , x_return_status => x_return_status
2668 , x_error_Tbl => l_error_Tbl
2669 );
2670
2671 -- EXCEPTION
2672 -- when FND_API.G_EXC_ERROR then
2673 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2674 l_error := FND_API.G_TRUE;
2675 l_error_Tbl_p := x_error_Tbl;
2676 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
2677 , l_error_Tbl
2678 , x_error_tbl
2679 );
2680 x_return_status := FND_API.G_RET_STS_ERROR;
2681 END IF;
2682 END;
2683
2684 BEGIN
2685 -- bug# 3735203
2686 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Target_Level_Rec.Computing_function_ID) =FND_API.G_TRUE ) THEN
2687 IF(p_Target_Level_Rec.Computing_function_ID <> -1) THEN
2688 BIS_TARGET_LEVEL_VALIDATE_PVT.Validate_Df_computed_target_Id
2689 ( p_api_version => p_api_version
2690 , p_validation_level => p_validation_level
2691 , p_Target_Level_Rec => p_Target_Level_Rec
2692 , x_return_status => x_return_status
2693 , x_error_Tbl => l_error_Tbl
2694 );
2695 END IF;
2696 END IF;
2697
2698 -- EXCEPTION
2699 -- when FND_API.G_EXC_ERROR then
2700 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2701 l_error := FND_API.G_TRUE;
2702 l_error_Tbl_p := x_error_Tbl;
2703 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
2704 , l_error_Tbl
2705 , x_error_tbl
2706 );
2707 x_return_status := FND_API.G_RET_STS_ERROR;
2708 END IF;
2709 END;
2710
2711 if (l_error = FND_API.G_TRUE) then
2712 RAISE FND_API.G_EXC_ERROR;
2713 end if;
2714
2715 --added this
2716 -- x_error_tbl := l_error_tbl;
2717 --commented RAISE
2718 EXCEPTION
2719 when FND_API.G_EXC_ERROR then
2720 x_return_status := FND_API.G_RET_STS_ERROR ;
2721 -- RAISE FND_API.G_EXC_ERROR;
2722 when FND_API.G_EXC_UNEXPECTED_ERROR then
2723 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2724 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2725 when others then
2726 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2727 --added last two params
2728 l_error_tbl := x_error_tbl;
2729 BIS_UTILITIES_PVT.Add_Error_Message
2730 ( p_error_msg_id => SQLCODE
2731 , p_error_description => SQLERRM
2732 , p_error_proc_name => G_PKG_NAME||'.Validate_Target_Level'
2733 , p_error_table => l_error_tbl
2734 , x_error_table => x_error_tbl
2735 );
2736 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2737
2738 END Validate_Target_Level;
2739 --
2740 PROCEDURE Check_Value_id_Conversion
2741 ( p_dim_level_id in NUMBER
2742 , p_Dim_level_short_name in VARCHAR2
2743 , p_Dime_Level_NAME in VARCHAR2
2744 , x_dim_level_id OUT NOCOPY NUMBER
2745 , x_return_status OUT NOCOPY VARCHAR2
2746 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2747 )
2748 IS
2749 l_convert VARCHAR2(10);
2750
2751 BEGIN
2752
2753 x_return_status := FND_API.G_RET_STS_SUCCESS;
2754
2755 l_convert := BIS_UTILITIES_PVT.Convert_to_ID( p_dim_level_id
2756 , p_Dim_level_short_name
2757 , p_Dime_Level_NAME
2758 );
2759
2760 if (l_convert = FND_API.G_TRUE) then
2761 BEGIN
2762 BIS_DIMENSION_LEVEL_PVT.Value_ID_Conversion
2763 ( 1.0
2764 , p_Dim_level_short_name
2765 , p_Dime_Level_NAME
2766 , x_dim_level_id
2767 , x_return_status
2768 , x_error_Tbl
2769 );
2770 EXCEPTION
2771 WHEN FND_API.G_EXC_ERROR then
2772 NULL;
2773 END;
2774 else
2775 x_dim_level_id := p_dim_level_id;
2776 end if;
2777
2778 END Check_Value_id_Conversion;
2779
2780 -- Value - ID conversion
2781 PROCEDURE Value_ID_Conversion
2782 ( p_api_version IN NUMBER
2783 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
2784 , x_Target_Level_Rec OUT NOCOPY BIS_Target_Level_PUB.Target_Level_Rec_Type
2785 , x_return_status OUT NOCOPY VARCHAR2
2786 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
2787 )
2788 IS
2789 l_convert VARCHAR2(10);
2790 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2791 l_Target_Level_Rec BIS_Target_Level_PUB.Target_Level_Rec_Type;
2792 BEGIN
2793 x_return_status := FND_API.G_RET_STS_SUCCESS;
2794 x_Target_Level_Rec := p_Target_Level_Rec;
2795
2796 l_convert := BIS_UTILITIES_PVT.Convert_to_ID
2797 ( x_Target_Level_Rec.Target_Level_id
2798 , x_Target_Level_Rec.Target_Level_Short_Name
2799 , x_Target_Level_Rec.Target_Level_Name
2800 );
2801
2802 if (l_convert = FND_API.G_TRUE) then
2803 BEGIN
2804 BIS_Target_Level_PVT.Value_ID_Conversion
2805 ( p_api_version
2806 , x_Target_Level_Rec.Target_Level_Short_Name
2807 , x_Target_Level_Rec.Target_Level_Name
2808 , x_Target_Level_Rec.Target_Level_ID
2809 , x_return_status
2810 , x_error_Tbl
2811 );
2812 EXCEPTION
2813 WHEN FND_API.G_EXC_ERROR then
2814 NULL;
2815 END;
2816 end if;
2817
2818 l_convert := BIS_UTILITIES_PVT.Convert_to_ID
2819 ( x_Target_Level_Rec.Measure_id
2820 , x_Target_Level_Rec.Measure_Short_Name
2821 , x_Target_Level_Rec.Measure_Name
2822 );
2823 if (l_convert = FND_API.G_TRUE) then
2824 BEGIN
2825 BIS_MEASURE_PVT.Value_ID_Conversion
2826 ( p_api_version
2827 , x_Target_Level_Rec.Measure_Short_Name
2828 , x_Target_Level_Rec.Measure_Name
2829 , x_Target_Level_Rec.Measure_ID
2830 , x_return_status
2831 , x_error_Tbl
2832 );
2833 EXCEPTION
2834 WHEN FND_API.G_EXC_ERROR then
2835 NULL;
2836 END;
2837 end if;
2838
2839 l_Target_Level_Rec.org_Level_id := x_Target_Level_Rec.org_Level_id;
2840 Check_Value_id_Conversion
2841 ( p_dim_level_id => l_Target_Level_Rec.org_Level_id
2842 , p_Dim_level_short_name => x_Target_Level_Rec.org_Level_Short_Name
2843 , p_Dime_Level_NAME => x_Target_Level_Rec.org_Level_Name
2844 , x_dim_level_id => x_Target_Level_Rec.org_Level_ID
2845 , x_return_status => x_return_status
2846 , x_error_Tbl => x_error_Tbl
2847 );
2848
2849 l_Target_Level_Rec.time_Level_id := x_Target_Level_Rec.time_Level_id;
2850 Check_Value_id_Conversion
2851 ( p_dim_level_id => l_Target_Level_Rec.time_Level_id
2852 , p_Dim_level_short_name => x_Target_Level_Rec.time_Level_Short_Name
2853 , p_Dime_Level_NAME => x_Target_Level_Rec.time_Level_Name
2854 , x_dim_level_id => x_Target_Level_Rec.time_Level_ID
2855 , x_return_status => x_return_status
2856 , x_error_Tbl => x_error_Tbl
2857 );
2858
2859 l_Target_Level_Rec.Dimension1_Level_id := x_Target_Level_Rec.Dimension1_Level_id;
2860 Check_Value_id_Conversion
2861 ( p_dim_level_id => l_Target_Level_Rec.Dimension1_Level_id
2862 , p_Dim_level_short_name => x_Target_Level_Rec.Dimension1_Level_Short_Name
2863 , p_Dime_Level_NAME => x_Target_Level_Rec.Dimension1_Level_Name
2864 , x_dim_level_id => x_Target_Level_Rec.Dimension1_Level_ID
2865 , x_return_status => x_return_status
2866 , x_error_Tbl => x_error_Tbl
2867 );
2868
2869 l_Target_Level_Rec.Dimension2_Level_id := x_Target_Level_Rec.Dimension2_Level_id;
2870 Check_Value_id_Conversion
2871 ( p_dim_level_id => l_Target_Level_Rec.Dimension2_Level_id
2872 , p_Dim_level_short_name => x_Target_Level_Rec.Dimension2_Level_Short_Name
2873 , p_Dime_Level_NAME => x_Target_Level_Rec.Dimension2_Level_Name
2874 , x_dim_level_id => x_Target_Level_Rec.Dimension2_Level_ID
2875 , x_return_status => x_return_status
2876 , x_error_Tbl => x_error_Tbl
2877 );
2878
2879 l_Target_Level_Rec.Dimension3_Level_id := x_Target_Level_Rec.Dimension3_Level_id;
2880 Check_Value_id_Conversion
2881 ( p_dim_level_id => l_Target_Level_Rec.Dimension3_Level_id
2882 , p_Dim_level_short_name => x_Target_Level_Rec.Dimension3_Level_Short_Name
2883 , p_Dime_Level_NAME => x_Target_Level_Rec.Dimension3_Level_Name
2884 , x_dim_level_id => x_Target_Level_Rec.Dimension3_Level_ID
2885 , x_return_status => x_return_status
2886 , x_error_Tbl => x_error_Tbl
2887 );
2888
2889 l_Target_Level_Rec.Dimension4_Level_id := x_Target_Level_Rec.Dimension4_Level_id;
2890 Check_Value_id_Conversion
2891 ( p_dim_level_id => l_Target_Level_Rec.Dimension4_Level_id
2892 , p_Dim_level_short_name => x_Target_Level_Rec.Dimension4_Level_Short_Name
2893 , p_Dime_Level_NAME => x_Target_Level_Rec.Dimension4_Level_Name
2894 , x_dim_level_id => x_Target_Level_Rec.Dimension4_Level_ID
2895 , x_return_status => x_return_status
2896 , x_error_Tbl => x_error_Tbl
2897 );
2898
2899 l_Target_Level_Rec.Dimension5_Level_id := x_Target_Level_Rec.Dimension5_Level_id;
2900 Check_Value_id_Conversion
2901 ( p_dim_level_id => l_Target_Level_Rec.Dimension5_Level_id
2902 , p_Dim_level_short_name => x_Target_Level_Rec.Dimension5_Level_Short_Name
2903 , p_Dime_Level_NAME => x_Target_Level_Rec.Dimension5_Level_Name
2904 , x_dim_level_id => x_Target_Level_Rec.Dimension5_Level_ID
2905 , x_return_status => x_return_status
2906 , x_error_Tbl => x_error_Tbl
2907 );
2908
2909 l_Target_Level_Rec.Dimension6_Level_id := x_Target_Level_Rec.Dimension6_Level_id;
2910 Check_Value_id_Conversion
2911 ( p_dim_level_id => l_Target_Level_Rec.Dimension6_Level_id
2912 , p_Dim_level_short_name => x_Target_Level_Rec.Dimension6_Level_Short_Name
2913 , p_Dime_Level_NAME => x_Target_Level_Rec.Dimension6_Level_Name
2914 , x_dim_level_id => x_Target_Level_Rec.Dimension6_Level_ID
2915 , x_return_status => x_return_status
2916 , x_error_Tbl => x_error_Tbl
2917 );
2918
2919 l_Target_Level_Rec.Dimension7_Level_id := x_Target_Level_Rec.Dimension7_Level_id;
2920 Check_Value_id_Conversion
2921 ( p_dim_level_id => l_Target_Level_Rec.Dimension7_Level_id
2922 , p_Dim_level_short_name => x_Target_Level_Rec.Dimension7_Level_Short_Name
2923 , p_Dime_Level_NAME => x_Target_Level_Rec.Dimension7_Level_Name
2924 , x_dim_level_id => x_Target_Level_Rec.Dimension7_Level_ID
2925 , x_return_status => x_return_status
2926 , x_error_Tbl => x_error_Tbl
2927 );
2928
2929 l_convert := BIS_UTILITIES_PVT.Convert_to_ID
2930 ( x_Target_Level_Rec.Default_Notify_Resp_id
2931 , x_Target_Level_Rec.Default_Notify_Resp_Short_Name
2932 , x_Target_Level_Rec.Default_Notify_Resp_Name
2933 );
2934
2935 if (l_convert = FND_API.G_TRUE) then
2936 BEGIN
2937 BIS_RESPONSIBILITY_PVT.DFR_Value_ID_Conversion
2938 ( p_api_version
2939 , x_Target_Level_Rec.Default_Notify_Resp_Short_Name
2940 , x_Target_Level_Rec.Default_Notify_Resp_Name
2941 , x_Target_Level_Rec.Default_Notify_Resp_ID
2942 , x_return_status
2943 , x_error_Tbl
2944 );
2945 EXCEPTION
2946 WHEN FND_API.G_EXC_ERROR then
2947 NULL;
2948 END;
2949 end if;
2950
2951 l_convert := BIS_UTILITIES_PVT.Convert_to_ID
2952 ( x_Target_Level_Rec.Computing_Function_id
2953 , x_Target_Level_Rec.Computing_Function_Name
2954 , x_Target_Level_Rec.Computing_user_Function_Name
2955 );
2956
2957 if (l_convert = FND_API.G_TRUE) then
2958
2959 -- if (BIS_UTILITIES_PUB.Value_Missing
2960 -- (x_Target_Level_Rec.Computing_Function_ID) = FND_API.G_TRUE) then
2961 BEGIN
2962 BIS_COMPUTED_TARGET_PVT.Value_ID_Conversion
2963 ( p_api_version => p_api_version
2964 , p_Computed_Target_Short_Name
2965 => x_Target_Level_Rec.Computing_Function_Name
2966 , p_Computed_Target_Name
2967 => x_Target_Level_Rec.Computing_User_Function_Name
2968 , x_Computed_Target_ID
2969 => x_Target_Level_Rec.Computing_Function_ID
2970 , x_return_status => x_return_status
2971 , x_error_Tbl => x_error_Tbl
2972 );
2973
2974 EXCEPTION
2975 WHEN FND_API.G_EXC_ERROR then
2976 NULL;
2977 END;
2978 end if;
2979
2980
2981 -- BUGBUG Value Id Conversion of UOM code, WF_ITEM_TYPE and report function
2982 -- NOT done
2983
2984 --commented RAISE
2985 EXCEPTION
2986 when FND_API.G_EXC_ERROR then
2987 x_return_status := FND_API.G_RET_STS_ERROR ;
2988 -- RAISE FND_API.G_EXC_ERROR;
2989 when FND_API.G_EXC_UNEXPECTED_ERROR then
2990 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2991 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2992 when others then
2993 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2994 --added last two params
2995 l_error_tbl := x_error_tbl;
2996 BIS_UTILITIES_PVT.Add_Error_Message
2997 ( p_error_msg_id => SQLCODE
2998 , p_error_description => SQLERRM
2999 , p_error_proc_name => G_PKG_NAME||'.Value_ID_Conversion'
3000 , p_error_table => l_error_tbl
3001 , x_error_table => x_error_tbl
3002 );
3003 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3004
3005 END Value_ID_Conversion;
3006 --
3007 PROCEDURE Value_ID_Conversion
3008 ( p_api_version IN NUMBER
3009 , p_Target_Level_Short_Name IN VARCHAR2
3010 , p_Target_Level_Name IN VARCHAR2
3011 , x_Target_Level_ID OUT NOCOPY NUMBER
3012 , x_return_status OUT NOCOPY VARCHAR2
3013 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
3014 )
3015 IS
3016 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3017 BEGIN
3018
3019 x_return_status := FND_API.G_RET_STS_SUCCESS;
3020
3021 if (BIS_UTILITIES_PUB.Value_Not_Missing(p_Target_Level_Short_Name)
3022 = FND_API.G_TRUE) then
3023 SELECT Target_Level_id into x_Target_Level_ID
3024 FROM bisbv_Target_Levels
3025 WHERE target_level_short_name = p_Target_Level_Short_Name;
3026 elsif (BIS_UTILITIES_PUB.Value_Not_Missing(p_Target_Level_Name)
3027 = FND_API.G_TRUE) then
3028 SELECT Target_Level_id into x_Target_Level_ID
3029 FROM bisbv_Target_Levels
3030 WHERE target_level_name = p_Target_Level_Name;
3031 else
3032 -- POLPULATE ERROR TABLE
3033 --added last two params
3034 l_error_tbl := x_error_tbl;
3035 BIS_UTILITIES_PVT.Add_Error_Message
3036 ( p_error_msg_name => 'BIS_NAME_SHORT_NAME_MISSING'
3037 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
3038 , p_error_proc_name => G_PKG_NAME||'.Value_ID_Conversion'
3039 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
3040 , p_error_table => l_error_tbl
3041 , x_error_table => x_error_tbl
3042 );
3043
3044 RAISE FND_API.G_EXC_ERROR;
3045 end if;
3046
3047 --commented RAISE
3048 EXCEPTION
3049 WHEN NO_DATA_FOUND THEN
3050 x_return_status := FND_API.G_RET_STS_ERROR ;
3051 -- RAISE FND_API.G_EXC_ERROR;
3052 when FND_API.G_EXC_ERROR then
3053 x_return_status := FND_API.G_RET_STS_ERROR ;
3054 -- RAISE FND_API.G_EXC_ERROR;
3055 when FND_API.G_EXC_UNEXPECTED_ERROR then
3056 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3057 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3058 when others then
3059 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3060 --added last two params
3061 l_error_tbl := x_error_tbl;
3062 BIS_UTILITIES_PVT.Add_Error_Message
3063 ( p_error_msg_id => SQLCODE
3064 , p_error_description => SQLERRM
3065 , p_error_proc_name => G_PKG_NAME||'.Value_ID_Conversion'
3066 , p_error_table => l_error_tbl
3067 , x_error_table => x_error_tbl
3068 );
3069 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3070
3071 END Value_ID_Conversion;
3072 --
3073 Procedure Retrieve_User_Target_Levels
3074 ( p_api_version IN NUMBER
3075 , p_user_id IN NUMBER
3076 , p_all_info IN VARCHAR2 := FND_API.G_TRUE
3077 , x_Target_Level_Tbl OUT NOCOPY BIS_Target_LEVEL_PUB.Target_Level_Tbl_Type
3078 , x_return_status OUT NOCOPY VARCHAR2
3079 , x_error_tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
3080 )
3081 IS
3082 l_flag NUMBER :=0;
3083 -- juwang bug#2225110 needs to check if end date and start date
3084 -- expires in fnd_user_resp_groups
3085 CURSOR ind_res IS
3086 select distinct ir.target_level_id, target_level_name
3087 from bis_indicator_resps ir
3088 , fnd_user_resp_groups ur
3089 , bisbv_target_levels il
3090 where ur.user_id = p_user_id
3091 and ir.responsibility_id = ur.responsibility_id
3092 AND ur.start_date <= sysdate
3093 AND nvl(ur.end_date, sysdate) >= sysdate
3094 and il.target_level_id = ir.target_level_id
3095 order by UPPER(target_level_name);
3096
3097 l_Target_Level_rec BIS_Target_LEVEL_PUB.Target_Level_Rec_Type;
3098 l_Target_Level_rec_p BIS_Target_LEVEL_PUB.Target_Level_Rec_Type;
3099 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3100
3101 BEGIN
3102
3103 for cr in ind_res loop
3104 l_flag :=1 ;
3105 l_Target_Level_rec.target_level_id := cr.target_level_id;
3106
3107 l_Target_Level_rec_p := l_Target_Level_rec;
3108 BIS_Target_Level_PVT.Retrieve_Target_Level
3109 ( p_api_version => 1.0
3110 , p_Target_Level_Rec => l_Target_Level_rec_p
3111 , p_all_info => p_all_info
3112 , x_Target_Level_Rec => l_Target_Level_rec
3113 , x_return_status => x_return_status
3114 , x_error_Tbl => x_error_tbl
3115 );
3116
3117 x_Target_Level_Tbl(x_Target_Level_Tbl.count + 1) := l_Target_Level_rec;
3118 end loop;
3119
3120 if ind_res%isopen then close ind_res; end if;
3121
3122 --added this check
3123 if(l_flag = 0) then
3124 l_error_tbl := x_error_tbl;
3125 BIS_UTILITIES_PVT.Add_Error_Message
3126 ( p_error_msg_name => 'BIS_INVALID_TARGET_LEVEL_VALUE'
3127 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
3128 , p_error_proc_name => G_PKG_NAME||'.Retrieve_User_Target_Levels'
3129 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
3130 , p_error_table => l_error_tbl
3131 , x_error_table => x_error_tbl
3132 );
3133 RAISE FND_API.G_EXC_ERROR;
3134 end if;
3135
3136 --commented RAISE
3137 EXCEPTION
3138 when FND_API.G_EXC_ERROR then
3139 if ind_res%isopen then close ind_res; end if;
3140 x_return_status := FND_API.G_RET_STS_ERROR ;
3141 -- RAISE FND_API.G_EXC_ERROR;
3142 when FND_API.G_EXC_UNEXPECTED_ERROR then
3143 if ind_res%isopen then close ind_res; end if;
3144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3145 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3146 when others then
3147 if ind_res%isopen then close ind_res; end if;
3148 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3149 --added last two params
3150 l_error_tbl := x_error_tbl;
3151 BIS_UTILITIES_PVT.Add_Error_Message
3152 ( p_error_msg_id => SQLCODE
3153 , p_error_description => SQLERRM
3154 , p_error_proc_name => G_PKG_NAME||'.Retrieve_User_Target_Levels'
3155 , p_error_table => l_error_tbl
3156 , x_error_table => x_error_tbl
3157 );
3158 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3159
3160 END Retrieve_User_Target_Levels;
3161
3162 --
3163 --
3164 PROCEDURE Retrieve_Last_Update_Date
3165 ( p_api_version IN NUMBER
3166 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
3167 , x_last_update_date OUT NOCOPY DATE
3168 , x_return_status OUT NOCOPY VARCHAR2
3169 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
3170 )
3171 IS
3172 l_date_char VARCHAR2(32000);
3173 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3174
3175 BEGIN
3176 x_return_status := FND_API.G_RET_STS_SUCCESS;
3177 --
3178 IF( BIS_UTILITIES_PUB.Value_Not_Missing(p_Target_Level_Rec.Target_Level_ID)
3179 = FND_API.G_TRUE
3180 ) THEN
3181 SELECT NVL(LAST_UPDATE_DATE, CREATION_DATE)
3182 INTO x_last_update_date
3183 FROM BIS_TARGET_LEVELS
3184 WHERE TARGET_LEVEL_ID = p_Target_Level_Rec.Target_Level_ID;
3185 END IF;
3186
3187 --
3188 --commented RAISE
3189 EXCEPTION
3190 WHEN NO_DATA_FOUND THEN
3191 --added this message
3192 l_error_tbl := x_error_tbl;
3193 BIS_UTILITIES_PVT.Add_Error_Message
3194 ( p_error_msg_name => 'BIS_INVALID_TARGET_LEVEL_VALUE'
3195 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
3196 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Last_Update_Date'
3197 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
3198 , p_error_table => l_error_tbl
3199 , x_error_table => x_error_tbl
3200 );
3201 x_return_status := FND_API.G_RET_STS_ERROR;
3202 -- RAISE FND_API.G_EXC_ERROR;
3203 WHEN FND_API.G_EXC_ERROR THEN
3204 x_return_status:= FND_API.G_RET_STS_ERROR;
3205 -- RAISE;
3206 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3207 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
3208 -- RAISE;
3209 WHEN OTHERS THEN
3210 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
3211 --added last two params
3212 l_error_tbl := x_error_tbl;
3213 BIS_UTILITIES_PVT.Add_Error_Message
3214 ( p_error_msg_id => SQLCODE
3215 , p_error_description => SQLERRM
3216 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Last_Update_Date'
3217 , p_error_table => l_error_tbl
3218 , x_error_table => x_error_tbl
3219 );
3220 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3221 --
3222 END Retrieve_Last_Update_Date;
3223 --
3224 --
3225 PROCEDURE Lock_Record
3226 ( p_api_version IN NUMBER
3227 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
3228 , p_timestamp IN VARCHAR := NULL
3229 , x_return_status OUT NOCOPY VARCHAR2
3230 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
3231 )
3232 IS
3233 --
3234 l_form_date DATE;
3235 l_last_update_date DATE;
3236 l_Target_Level_Rec BIS_Target_Level_PUB.Target_Level_Rec_Type;
3237 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3238 --
3239 BEGIN
3240 x_return_status := FND_API.G_RET_STS_SUCCESS;
3241 --
3242 l_Target_Level_Rec.Target_Level_Id := p_Target_Level_Rec.Target_Level_Id;
3243 BIS_Target_Level_PVT.Retrieve_Last_Update_Date
3244 ( p_api_version => 1.0
3245 , p_Target_Level_Rec => p_Target_Level_Rec
3246 , x_last_update_date => l_last_update_date
3247 , x_return_status => x_return_status
3248 , x_error_Tbl => x_error_Tbl
3249 );
3250 IF(p_timestamp IS NOT NULL) THEN
3251 l_form_date := TO_DATE(p_timestamp, BIS_UTILITIES_PVT.G_DATE_FORMAT);
3252
3253 IF(l_form_date = l_last_update_date) THEN
3254 x_return_status := FND_API.G_TRUE;
3255 ELSE
3256 x_return_status := FND_API.G_FALSE;
3257 END IF;
3258 ELSE
3259 x_return_status := FND_API.G_FALSE;
3260 END IF;
3261 --
3262 --commented RAISE
3263 EXCEPTION
3264 WHEN FND_API.G_EXC_ERROR THEN
3265 x_return_status:= FND_API.G_RET_STS_ERROR;
3266 -- RAISE;
3267 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3268 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
3269 -- RAISE;
3270 WHEN OTHERS THEN
3271 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
3272 --added last two params
3273 l_error_tbl := x_error_tbl;
3274 BIS_UTILITIES_PVT.Add_Error_Message
3275 ( p_error_msg_id => SQLCODE
3276 , p_error_description => SQLERRM
3277 , p_error_proc_name => G_PKG_NAME||'.BIS_Target_Level_PVT'
3278 , p_error_table => l_error_tbl
3279 , x_error_table => x_error_tbl
3280 );
3281 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3282 --
3283 END Lock_Record;
3284 --
3285
3286 -- Retrieves the time level for the given target level
3287 --
3288 PROCEDURE Retrieve_Time_level
3289 ( p_api_version IN NUMBER
3290 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
3291 , x_Dimension_Level_Rec OUT NOCOPY BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
3292 , x_dimension_level_number OUT NOCOPY NUMBER
3293 , x_return_status OUT NOCOPY VARCHAR2
3294 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
3295 )
3296 IS
3297
3298 l_time_dimension_id NUMBER;
3299 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
3300 l_dimension_level_tbl BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Tbl_Type;
3301 l_found NUMBER := 0;
3302 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3303
3304 CURSOR cr_tl_id_dim_level(p_target_level_id NUMBER) IS
3305 select
3306 dimension1_level_id,
3307 dimension2_level_id,
3308 dimension3_level_id,
3309 dimension4_level_id,
3310 dimension5_level_id,
3311 dimension6_level_id,
3312 dimension7_level_id
3313 from bisbv_target_levels
3314 where target_level_id = p_target_level_id;
3315
3316 CURSOR cr_tl_sn_dim_level(p_target_level_short_name VARCHAR2) IS
3317 select
3318 dimension1_level_id,
3319 dimension2_level_id,
3320 dimension3_level_id,
3321 dimension4_level_id,
3322 dimension5_level_id,
3323 dimension6_level_id,
3324 dimension7_level_id
3325 from bisbv_target_levels
3326 where target_level_short_name = p_target_level_short_name;
3327
3328 BEGIN
3329
3330 IF (BIS_UTILITIES_PVT.Value_Not_Missing(p_Target_Level_Rec.target_level_id)
3331 = FND_API.G_TRUE)
3332 AND (BIS_UTILITIES_PVT.Value_Not_Null(p_Target_Level_Rec.target_level_id)
3333 = FND_API.G_TRUE)
3334 THEN
3335 OPEN cr_tl_id_dim_level(p_Target_Level_Rec.target_level_id);
3336 FETCH cr_tl_id_dim_level INTO
3337 l_dimension_level_tbl(1).dimension_level_id,
3338 l_dimension_level_tbl(2).dimension_level_id,
3339 l_dimension_level_tbl(3).dimension_level_id,
3340 l_dimension_level_tbl(4).dimension_level_id,
3341 l_dimension_level_tbl(5).dimension_level_id,
3342 l_dimension_level_tbl(6).dimension_level_id,
3343 l_dimension_level_tbl(7).dimension_level_id;
3344
3345 --added this
3346 if cr_tl_id_dim_level%NOTFOUND then
3347 --added this message
3348 l_error_tbl := x_error_tbl;
3349 BIS_UTILITIES_PVT.Add_Error_Message
3350 ( p_error_msg_name => 'BIS_INVALID_TARGET_LEVEL_VALUE'
3351 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
3352 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Time_Level_Value'
3353 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
3354 , p_error_table => l_error_tbl
3355 , x_error_table => x_error_tbl
3356 );
3357 x_return_status := FND_API.G_RET_STS_ERROR;
3358 RAISE FND_API.G_EXC_ERROR;
3359 end if;
3360
3361 CLOSE cr_tl_id_dim_level;
3362 --------
3363 Select dimension_id
3364 into l_time_dimension_id
3365 from bisbv_dimensions
3366 --'TIME'
3367 where upper(dimension_short_name) =
3368 BIS_UTILITIES_PVT.Get_Time_Dimension_Name_TL(p_Target_Level_Rec.target_level_id,NULL);
3369 --------
3370 ELSE
3371 OPEN cr_tl_sn_dim_level(p_Target_Level_Rec.target_level_short_name);
3372 FETCH cr_tl_sn_dim_level INTO
3373 l_dimension_level_tbl(1).dimension_level_id,
3374 l_dimension_level_tbl(2).dimension_level_id,
3375 l_dimension_level_tbl(3).dimension_level_id,
3376 l_dimension_level_tbl(4).dimension_level_id,
3377 l_dimension_level_tbl(5).dimension_level_id,
3378 l_dimension_level_tbl(6).dimension_level_id,
3379 l_dimension_level_tbl(7).dimension_level_id;
3380
3381 --added this
3382 if cr_tl_sn_dim_level%NOTFOUND then
3383 --added this message
3384 l_error_tbl := x_error_tbl;
3385 BIS_UTILITIES_PVT.Add_Error_Message
3386 ( p_error_msg_name => 'BIS_INVALID_TARGET_LEVEL_VALUE'
3387 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
3388 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Time_Level_Value'
3389 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
3390 , p_error_table => l_error_tbl
3391 , x_error_table => x_error_tbl
3392 );
3393 x_return_status := FND_API.G_RET_STS_ERROR;
3394 RAISE FND_API.G_EXC_ERROR;
3395 end if;
3396
3397 CLOSE cr_tl_sn_dim_level;
3398 --------
3399 Select dimension_id
3400 into l_time_dimension_id
3401 from bisbv_dimensions
3402 --'TIME'
3403 where upper(dimension_short_name) =
3404 BIS_UTILITIES_PVT.Get_Time_Dimension_Name_TL(NULL,p_Target_Level_Rec.target_level_short_name);
3405 --------
3406 END IF;
3407 --moved this in the if condition for tl id and shortname
3408 /*
3409 Select dimension_id
3410 into l_time_dimension_id
3411 from bisbv_dimensions
3412 --'TIME'
3413 where upper(dimension_short_name) =
3414 BIS_UTILITIES_PVT.Get_Time_Dimension_Name;
3415 */
3416 FOR i IN 1..l_dimension_level_tbl.COUNT LOOP
3417 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level
3418 ( p_api_version
3419 , p_Dimension_Level_Rec => l_dimension_level_tbl(i)
3420 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
3421 , x_return_status => x_return_status
3422 , x_error_Tbl => x_error_Tbl
3423 );
3424 IF l_Dimension_Level_Rec.DIMENSION_ID = l_time_dimension_id THEN
3425 x_Dimension_Level_Rec := l_Dimension_Level_Rec;
3426 x_dimension_level_number := i;
3427 l_found := 1;
3428 -- BIS_UTILITIES_PUB.put_line(p_text =>'Target level time dim level is: '||x_dimension_level_number);
3429 EXIT;
3430 END IF;
3431 END LOOP;
3432
3433 IF (l_found = 0) then
3434 x_Dimension_Level_Rec.dimension_id := null;
3435 x_Dimension_Level_Rec.dimension_short_name := null;
3436 x_Dimension_Level_Rec.dimension_level_id := null;
3437 x_Dimension_Level_Rec.dimension_level_short_name := null;
3438 x_dimension_level_number := null;
3439 END IF;
3440
3441 EXCEPTION
3442
3443 --added this
3444 WHEN FND_API.G_EXC_ERROR THEN
3445 x_return_status := FND_API.G_RET_STS_ERROR;
3446 IF cr_tl_sn_dim_level%ISOPEN THEN CLOSE cr_tl_sn_dim_level; END IF;
3447 IF cr_tl_id_dim_level%ISOPEN THEN CLOSE cr_tl_id_dim_level; END IF;
3448 WHEN OTHERS THEN
3449 --dbms_output('Error while getting time level: '||SQLERRM);
3450 IF cr_tl_sn_dim_level%ISOPEN THEN CLOSE cr_tl_sn_dim_level; END IF;
3451 IF cr_tl_id_dim_level%ISOPEN THEN CLOSE cr_tl_id_dim_level; END IF;
3452 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
3453 l_error_Tbl := x_error_Tbl;
3454 BIS_UTILITIES_PVT.Add_Error_Message
3455 ( p_error_table => l_error_Tbl
3456 , p_error_msg_id => SQLCODE
3457 , p_error_description => SQLERRM
3458 , x_error_table => x_error_Tbl
3459 );
3460
3461 END Retrieve_Time_level;
3462
3463 -- Retrieves the time level for the given target level
3464 --
3465 PROCEDURE Retrieve_Time_level
3466 ( p_api_version IN NUMBER
3467 , p_Target_Level_id IN NUMBER
3468 , x_Dimension_Level_id OUT NOCOPY NUMBER
3469 , x_Dimension_Level_short_Name OUT NOCOPY NUMBER
3470 , x_Dimension_Level_name OUT NOCOPY NUMBER
3471 , x_dimension_level_number OUT NOCOPY NUMBER
3472 , x_return_status OUT NOCOPY VARCHAR2
3473 )
3474 IS
3475
3476 l_Target_Level_Rec BIS_Target_Level_PUB.Target_Level_Rec_Type;
3477 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
3478 l_dimension_level_number NUMBER;
3479 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3480 l_return_status VARCHAR2(32000);
3481
3482 BEGIN
3483
3484 l_Target_Level_Rec.Target_Level_id := p_Target_Level_id;
3485
3486 Retrieve_Time_level
3487 ( p_api_version => 1.0
3488 , p_Target_Level_Rec => l_Target_Level_Rec
3489 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
3490 , x_dimension_level_number => l_dimension_level_number
3491 , x_return_status => x_return_status
3492 , x_error_Tbl => l_error_Tbl
3493 );
3494
3495 x_Dimension_Level_id := l_Dimension_Level_Rec.Dimension_Level_id;
3496 x_Dimension_Level_short_Name
3497 := l_Dimension_Level_Rec.Dimension_Level_short_Name;
3498 x_Dimension_Level_name := l_Dimension_Level_Rec.Dimension_Level_name;
3499 x_dimension_level_number := l_dimension_level_number;
3500
3501 END Retrieve_Time_level;
3502
3503
3504 --
3505 -- Retrieves the Org level for the given target level
3506 --
3507
3508 PROCEDURE Retrieve_Org_level
3509 ( p_api_version IN NUMBER
3510 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
3511 , x_Dimension_Level_Rec OUT NOCOPY BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
3512 , x_dimension_level_number OUT NOCOPY NUMBER
3513 , x_return_status OUT NOCOPY VARCHAR2
3514 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
3515 )
3516 IS
3517
3518 l_org_dimension_id NUMBER;
3519 l_dimension_short_name VARCHAR2(80);
3520 l_Dimension_Level_Rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
3521 l_dimension_level_tbl BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Tbl_Type;
3522 l_err_track NUMBER := 0;
3523 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3524
3525
3526 CURSOR cr_ol_id_dim_level(p_target_level_id NUMBER) IS
3527 select
3528 dimension1_level_id,
3529 dimension2_level_id,
3530 dimension3_level_id,
3531 dimension4_level_id,
3532 dimension5_level_id,
3533 dimension6_level_id,
3534 dimension7_level_id
3535 from bisbv_target_levels
3536 where target_level_id = p_target_level_id;
3537
3538 CURSOR cr_ol_sn_dim_level(p_target_level_short_name VARCHAR2) IS
3539 select
3540 dimension1_level_id,
3541 dimension2_level_id,
3542 dimension3_level_id,
3543 dimension4_level_id,
3544 dimension5_level_id,
3545 dimension6_level_id,
3546 dimension7_level_id
3547 from bisbv_target_levels
3548 where target_level_short_name = p_target_level_short_name;
3549
3550 BEGIN
3551
3552 -- BIS_UTILITIES_PUB.put_line(p_text =>' test inside bisvtalb tgt lvl id = ' || p_Target_Level_Rec.target_level_id );
3553
3554 l_err_track := 100;
3555
3556 IF (BIS_UTILITIES_PVT.Value_Not_Missing(p_Target_Level_Rec.target_level_id)
3557 = FND_API.G_TRUE)
3558 AND (BIS_UTILITIES_PVT.Value_Not_Null(p_Target_Level_Rec.target_level_id)
3559 = FND_API.G_TRUE)
3560 THEN
3561
3562 l_err_track := 200;
3563
3564 OPEN cr_ol_id_dim_level(p_Target_Level_Rec.target_level_id);
3565 FETCH cr_ol_id_dim_level INTO
3566 l_dimension_level_tbl(1).dimension_level_id,
3567 l_dimension_level_tbl(2).dimension_level_id,
3568 l_dimension_level_tbl(3).dimension_level_id,
3569 l_dimension_level_tbl(4).dimension_level_id,
3570 l_dimension_level_tbl(5).dimension_level_id,
3571 l_dimension_level_tbl(6).dimension_level_id,
3572 l_dimension_level_tbl(7).dimension_level_id;
3573
3574 --added this
3575 if cr_ol_id_dim_level%NOTFOUND then
3576 --added this message
3577 l_error_tbl := x_error_tbl;
3578 BIS_UTILITIES_PVT.Add_Error_Message
3579 ( p_error_msg_name => 'BIS_INVALID_TARGET_LEVEL_VALUE'
3580 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
3581 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Org_Level'
3582 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
3583 , p_error_table => l_error_tbl
3584 , x_error_table => x_error_tbl
3585 );
3586 x_return_status := FND_API.G_RET_STS_ERROR;
3587 RAISE FND_API.G_EXC_ERROR;
3588 end if;
3589
3590 l_err_track := 300;
3591
3592 CLOSE cr_ol_id_dim_level;
3593
3594
3595 --------
3596
3597 l_err_track := 400;
3598
3599 l_dimension_short_name := BIS_UTILITIES_PVT.Get_Org_Dimension_Name_TL(p_Target_Level_Rec.target_level_id,NULL);
3600
3601 -- BIS_UTILITIES_PUB.put_line(p_text => ' dim short name = ' || l_dimension_short_name ) ;
3602
3603 l_err_track := 500;
3604
3605 Select dimension_id
3606 into l_org_dimension_id
3607 from bisbv_dimensions -- 'ORG'
3608 where upper(dimension_short_name) = l_dimension_short_name;
3609
3610 l_err_track := 600;
3611
3612 --------
3613
3614
3615 ELSE
3616
3617
3618 OPEN cr_ol_sn_dim_level(p_Target_Level_Rec.target_level_short_name);
3619
3620 FETCH cr_ol_sn_dim_level INTO
3621 l_dimension_level_tbl(1).dimension_level_id,
3622 l_dimension_level_tbl(2).dimension_level_id,
3623 l_dimension_level_tbl(3).dimension_level_id,
3624 l_dimension_level_tbl(4).dimension_level_id,
3625 l_dimension_level_tbl(5).dimension_level_id,
3626 l_dimension_level_tbl(6).dimension_level_id,
3627 l_dimension_level_tbl(7).dimension_level_id;
3628
3629
3630 if cr_ol_sn_dim_level%NOTFOUND then
3631
3632 l_error_tbl := x_error_tbl;
3633 BIS_UTILITIES_PVT.Add_Error_Message
3634 ( p_error_msg_name => 'BIS_INVALID_TARGET_LEVEL_VALUE'
3635 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
3636 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Org_Level'
3637 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
3638 , p_error_table => l_error_tbl
3639 , x_error_table => x_error_tbl
3640 );
3641
3642 x_return_status := FND_API.G_RET_STS_ERROR;
3643 RAISE FND_API.G_EXC_ERROR;
3644
3645 end if;
3646
3647 CLOSE cr_ol_sn_dim_level;
3648
3649 --------
3650
3651 l_dimension_short_name := BIS_UTILITIES_PVT.Get_Org_Dimension_Name_TL(NULL, p_Target_Level_Rec.target_level_short_name);
3652
3653 Select dimension_id
3654 into l_org_dimension_id
3655 from bisbv_dimensions --''ORG''
3656 where upper(dimension_short_name) = l_dimension_short_name;
3657
3658 --------
3659
3660 END IF;
3661
3662
3663 -- BIS_UTILITIES_PUB.put_line(p_text =>' test inside bisvtalb ' );
3664 -- BIS_UTILITIES_PUB.put_line(p_text => ' l_org_dimension_id inside bisvtalb is = ' || l_org_dimension_id ) ;
3665
3666 x_dimension_level_number := 0;
3667
3668 FOR i IN 1..l_dimension_level_tbl.COUNT
3669 LOOP
3670
3671 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level
3672 ( p_api_version
3673 , p_Dimension_Level_Rec => l_dimension_level_tbl(i)
3674 , x_Dimension_Level_Rec => l_Dimension_Level_Rec
3675 , x_return_status => x_return_status
3676 , x_error_Tbl => x_error_Tbl
3677 );
3678
3679 IF l_Dimension_Level_Rec.DIMENSION_ID = l_org_dimension_id THEN
3680 x_Dimension_Level_Rec := l_Dimension_Level_Rec;
3681 x_dimension_level_number := i;
3682 BIS_UTILITIES_PUB.put_line(p_text =>'Target level org dim level is: '||x_dimension_level_number);
3683 EXIT;
3684 END IF;
3685
3686 END LOOP;
3687
3688 if (x_dimension_level_number = 0) then
3689 BIS_UTILITIES_PUB.put_line(p_text =>' bisvtalb: Org level could not be found for this target level ' ) ;
3690 end if;
3691
3692
3693 EXCEPTION
3694
3695
3696 WHEN FND_API.G_EXC_ERROR THEN
3697 x_return_status := FND_API.G_RET_STS_ERROR;
3698 IF cr_ol_sn_dim_level%ISOPEN THEN CLOSE cr_ol_sn_dim_level; END IF;
3699 IF cr_ol_id_dim_level%ISOPEN THEN CLOSE cr_ol_id_dim_level; END IF;
3700 WHEN OTHERS THEN
3701 BIS_UTILITIES_PUB.put_line(p_text =>'Error while getting org level: bisvtalb ' || l_err_track || SQLERRM);
3702 IF cr_ol_sn_dim_level%ISOPEN THEN CLOSE cr_ol_sn_dim_level; END IF;
3703 IF cr_ol_id_dim_level%ISOPEN THEN CLOSE cr_ol_id_dim_level; END IF;
3704 x_return_status:= FND_API.G_RET_STS_UNEXP_ERROR;
3705 l_error_Tbl := x_error_Tbl;
3706 BIS_UTILITIES_PVT.Add_Error_Message
3707 ( p_error_table => l_error_Tbl
3708 , p_error_msg_id => SQLCODE
3709 , p_error_description => SQLERRM
3710 , x_error_table => x_error_Tbl
3711 );
3712
3713 END Retrieve_Org_level;
3714
3715
3716
3717
3718 -- New Procedure to return TargetLevel given the DimensionLevel ShortNames in any sequence
3719 -- and the Measure Short Name
3720
3721 PROCEDURE Retrieve_TL_From_DimLvlShNms
3722 (
3723 p_api_version IN NUMBER
3724 , p_target_level_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
3725 , x_Target_Level_Rec OUT NOCOPY BIS_Target_Level_PUB.Target_Level_Rec_Type
3726 , x_return_status OUT NOCOPY VARCHAR2
3727 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
3728 )
3729 IS
3730 l_measure_rec BIS_MEASURE_PUB.MEASURE_REC_TYPE;
3731 l_measure_rec_p BIS_MEASURE_PUB.MEASURE_REC_TYPE;
3732 l_dim_level_rec BIS_DIMENSION_LEVEL_PUB.DIMENSION_LEVEL_REC_TYPE;
3733 l_dim_level_rec_p BIS_DIMENSION_LEVEL_PUB.DIMENSION_LEVEL_REC_TYPE;
3734 l_target_level_id NUMBER;
3735 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3736
3737 l_dim1_id NUMBER;
3738 l_dim2_id NUMBER;
3739 l_dim3_id NUMBER;
3740 l_dim4_id NUMBER;
3741 l_dim5_id NUMBER;
3742 l_dim6_id NUMBER;
3743 l_dim7_id NUMBER;
3744 l_dim1_level_id NUMBER;
3745 l_dim2_level_id NUMBER;
3746 l_dim3_level_id NUMBER;
3747 l_dim4_level_id NUMBER;
3748 l_dim5_level_id NUMBER;
3749 l_dim6_level_id NUMBER;
3750 l_dim7_level_id NUMBER;
3751
3752 l_dim1_level_short_name VARCHAR2(32000);
3753 l_dim2_level_short_name VARCHAR2(32000);
3754 l_dim3_level_short_name VARCHAR2(32000);
3755 l_dim4_level_short_name VARCHAR2(32000);
3756 l_dim5_level_short_name VARCHAR2(32000);
3757 l_dim6_level_short_name VARCHAR2(32000);
3758 l_dim7_level_short_name VARCHAR2(32000);
3759
3760 l_dim1_level_name VARCHAR2(32000);
3761 l_dim2_level_name VARCHAR2(32000);
3762 l_dim3_level_name VARCHAR2(32000);
3763 l_dim4_level_name VARCHAR2(32000);
3764 l_dim5_level_name VARCHAR2(32000);
3765 l_dim6_level_name VARCHAR2(32000);
3766 l_dim7_level_name VARCHAR2(32000);
3767
3768 CURSOR c_dim_lvl(p_dim_level_short_name in varchar2) IS
3769 SELECT level_id , name
3770 FROM bis_levels_vl
3771 WHERE short_name=p_dim_level_short_name;
3772
3773 BEGIN
3774
3775
3776 IF (p_target_level_rec.measure_short_name IS NOT NULL
3777 AND BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.measure_short_name) = FND_API.G_TRUE) THEN
3778 l_measure_rec.measure_short_name := p_target_level_rec.measure_short_name;
3779 END IF;
3780 IF (p_target_level_rec.measure_id IS NOT NULL
3781 AND BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.measure_id) = FND_API.G_TRUE) THEN
3782 l_measure_rec.measure_id := p_target_level_rec.measure_id;
3783 END IF;
3784 --Populate the measure record with all the relevant values
3785 l_measure_rec_p := l_measure_rec;
3786 BIS_MEASURE_PUB.RETRIEVE_MEASURE( p_api_version => p_api_version
3787 ,p_measure_rec => l_measure_rec_p
3788 ,p_all_info =>FND_API.G_TRUE
3789 ,x_measure_rec => l_measure_rec
3790 ,x_return_status => x_return_status
3791 ,x_error_tbl => x_error_tbl
3792 );
3793
3794
3795 IF (p_target_level_rec.dimension1_level_short_name IS NOT NULL
3796 AND BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.dimension1_level_short_name) = FND_API.G_TRUE) THEN
3797 OPEN c_dim_lvl(p_target_level_rec.dimension1_level_short_name);
3798 FETCH c_dim_lvl INTO x_target_level_rec.dimension1_level_id,x_target_level_rec.dimension1_level_name;
3799 CLOSE c_dim_lvl;
3800 END IF;
3801 IF (p_target_level_rec.dimension2_level_short_name IS NOT NULL
3802 AND BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.dimension2_level_short_name)= FND_API.G_TRUE) THEN
3803 OPEN c_dim_lvl(p_target_level_rec.dimension2_level_short_name);
3804 FETCH c_dim_lvl INTO x_target_level_rec.dimension2_level_id,x_target_level_rec.dimension2_level_name;
3805 CLOSE c_dim_lvl;
3806 END IF;
3807 IF (p_target_level_rec.dimension3_level_short_name IS NOT NULL
3808 AND BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.dimension3_level_short_name)= FND_API.G_TRUE) THEN
3809 OPEN c_dim_lvl(p_target_level_rec.dimension3_level_short_name);
3810 FETCH c_dim_lvl INTO x_target_level_rec.dimension3_level_id,x_target_level_rec.dimension3_level_name;
3811 CLOSE c_dim_lvl;
3812 END IF;
3813 IF (p_target_level_rec.dimension4_level_short_name IS NOT NULL
3814 AND BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.dimension4_level_short_name)= FND_API.G_TRUE) THEN
3815 OPEN c_dim_lvl(p_target_level_rec.dimension4_level_short_name);
3816 FETCH c_dim_lvl INTO x_target_level_rec.dimension4_level_id,x_target_level_rec.dimension4_level_name;
3817 CLOSE c_dim_lvl;
3818 END IF;
3819 IF (p_target_level_rec.dimension5_level_short_name IS NOT NULL
3820 AND BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.dimension5_level_short_name)= FND_API.G_TRUE) THEN
3821 OPEN c_dim_lvl(p_target_level_rec.dimension5_level_short_name);
3822 FETCH c_dim_lvl INTO x_target_level_rec.dimension5_level_id,x_target_level_rec.dimension5_level_name;
3823 CLOSE c_dim_lvl;
3824 END IF;
3825 IF (p_target_level_rec.dimension6_level_short_name IS NOT NULL
3826 AND BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.dimension6_level_short_name)= FND_API.G_TRUE) THEN
3827 OPEN c_dim_lvl(p_target_level_rec.dimension6_level_short_name);
3828 FETCH c_dim_lvl INTO x_target_level_rec.dimension6_level_id,x_target_level_rec.dimension6_level_name;
3829 CLOSE c_dim_lvl;
3830 END IF;
3831 IF (p_target_level_rec.dimension7_level_short_name IS NOT NULL
3832 AND BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_rec.dimension7_level_short_name)= FND_API.G_TRUE) THEN
3833 OPEN c_dim_lvl(p_target_level_rec.dimension7_level_short_name);
3834 FETCH c_dim_lvl INTO x_target_level_rec.dimension7_level_id,x_target_level_rec.dimension7_level_name;
3835 CLOSE c_dim_lvl;
3836 END IF;
3837 x_target_level_rec.measure_name := l_measure_rec.measure_name;
3838 x_target_level_Rec.measure_id := l_measure_rec.measure_id;
3839
3840 --Get the dimension ids for all the dimension level ids. This will be later used to
3841 --sequence the dimension levels
3842 IF (x_target_level_rec.dimension1_level_id IS NOT NULL
3843 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_level_rec.dimension1_level_id)= FND_API.G_TRUE) THEN
3844 SetNULL(l_dim_level_rec,l_dim_level_rec);
3845 l_dim_level_rec.dimension_level_id := x_target_level_rec.dimension1_level_id;
3846 l_dim_level_rec_p := l_dim_level_rec;
3847 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level( p_api_version => p_api_version
3848 ,p_Dimension_Level_Rec => l_dim_level_rec_p
3849 ,x_Dimension_Level_Rec => l_dim_level_rec
3850 ,x_return_status => x_return_status
3851 ,x_error_Tbl => x_error_tbl
3852 );
3853 l_dim1_id := BIS_UTILITIES_PVT.checkmissnum(l_dim_level_rec.dimension_id);
3854 END IF;
3855
3856 IF (x_target_level_rec.dimension2_level_id IS NOT NULL
3857 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_level_rec.dimension2_level_id)= FND_API.G_TRUE) THEN
3858 SetNULL(l_dim_level_rec,l_dim_level_rec);
3859 l_dim_level_rec.dimension_level_id := x_target_level_rec.dimension2_level_id;
3860 l_dim_level_rec_p := l_dim_level_rec;
3861 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level( p_api_version => p_api_version
3862 ,p_Dimension_Level_Rec => l_dim_level_rec_p
3863 ,x_Dimension_Level_Rec => l_dim_level_rec
3864 ,x_return_status => x_return_status
3865 ,x_error_Tbl => x_error_tbl
3866 );
3867 l_dim2_id := BIS_UTILITIES_PVT.checkmissnum(l_dim_level_rec.dimension_id);
3868 END IF;
3869
3870 IF (x_target_level_rec.dimension3_level_id IS NOT NULL
3871 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_level_rec.dimension3_level_id)= FND_API.G_TRUE) THEN
3872 SetNULL(l_dim_level_rec,l_dim_level_rec);
3873 l_dim_level_rec.dimension_level_id := x_target_level_rec.dimension3_level_id;
3874 l_dim_level_rec_p := l_dim_level_rec;
3875 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level( p_api_version => p_api_version
3876 ,p_Dimension_Level_Rec => l_dim_level_rec_p
3877 ,x_Dimension_Level_Rec => l_dim_level_rec
3878 ,x_return_status => x_return_status
3879 ,x_error_Tbl => x_error_tbl
3880 );
3881 l_dim3_id := BIS_UTILITIES_PVT.checkmissnum(l_dim_level_rec.dimension_id);
3882 END IF;
3883
3884 IF (x_target_level_rec.dimension4_level_id IS NOT NULL
3885 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_level_rec.dimension4_level_id)= FND_API.G_TRUE) THEN
3886 SetNULL(l_dim_level_rec,l_dim_level_rec);
3887 l_dim_level_rec.dimension_level_id := x_target_level_rec.dimension4_level_id;
3888 l_dim_level_rec_p := l_dim_level_rec;
3889 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level( p_api_version => p_api_version
3890 ,p_Dimension_Level_Rec => l_dim_level_rec_p
3891 ,x_Dimension_Level_Rec => l_dim_level_rec
3892 ,x_return_status => x_return_status
3893 ,x_error_Tbl => x_error_tbl
3894 );
3895 l_dim4_id := BIS_UTILITIES_PVT.checkmissnum(l_dim_level_rec.dimension_id);
3896 END IF;
3897
3898 IF (x_target_level_rec.dimension5_level_id IS NOT NULL
3899 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_level_rec.dimension5_level_id)= FND_API.G_TRUE) THEN
3900 SetNULL(l_dim_level_rec,l_dim_level_rec);
3901 l_dim_level_rec.dimension_level_id := x_target_level_rec.dimension5_level_id;
3902 l_dim_level_rec_p := l_dim_level_rec;
3903 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level( p_api_version => p_api_version
3904 ,p_Dimension_Level_Rec => l_dim_level_rec_p
3905 ,x_Dimension_Level_Rec => l_dim_level_rec
3906 ,x_return_status => x_return_status
3907 ,x_error_Tbl => x_error_tbl
3908 );
3909 l_dim5_id := BIS_UTILITIES_PVT.checkmissnum(l_dim_level_rec.dimension_id);
3910 END IF;
3911
3912 IF (x_target_level_rec.dimension6_level_id IS NOT NULL
3913 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_level_rec.dimension6_level_id)= FND_API.G_TRUE) THEN
3914 SetNULL(l_dim_level_rec,l_dim_level_rec);
3915 l_dim_level_rec.dimension_level_id := x_target_level_rec.dimension6_level_id;
3916 l_dim_level_rec_p := l_dim_level_rec;
3917 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level( p_api_version => p_api_version
3918 ,p_Dimension_Level_Rec => l_dim_level_rec_p
3919 ,x_Dimension_Level_Rec => l_dim_level_rec
3920 ,x_return_status => x_return_status
3921 ,x_error_Tbl => x_error_tbl
3922 );
3923 l_dim6_id := BIS_UTILITIES_PVT.checkmissnum(l_dim_level_rec.dimension_id);
3924 END IF;
3925
3926 IF (x_target_level_rec.dimension7_level_id IS NOT NULL
3927 AND BIS_UTILITIES_PUB.Value_Not_Missing(x_target_level_rec.dimension7_level_id)= FND_API.G_TRUE) THEN
3928 SetNULL(l_dim_level_rec,l_dim_level_rec);
3929 l_dim_level_rec.dimension_level_id := x_target_level_rec.dimension7_level_id;
3930 l_dim_level_rec_p := l_dim_level_rec;
3931 BIS_DIMENSION_LEVEL_PUB.Retrieve_Dimension_Level( p_api_version => p_api_version
3932 ,p_Dimension_Level_Rec => l_dim_level_rec_p
3933 ,x_Dimension_Level_Rec => l_dim_level_rec
3934 ,x_return_status => x_return_status
3935 ,x_error_Tbl => x_error_tbl
3936 );
3937 l_dim7_id := BIS_UTILITIES_PVT.checkmissnum(l_dim_level_rec.dimension_id);
3938 END IF;
3939
3940
3941 IF (l_measure_rec.dimension1_id = l_dim1_id) THEN
3942 l_dim1_level_id := x_target_level_rec.dimension1_level_id;
3943 l_dim1_level_short_name := p_target_level_rec.dimension1_level_short_name;
3944 l_dim1_level_name := x_target_level_rec.dimension1_level_name;
3945 --l_dim1_level_value_id := p_dim1_level_value_id;
3946 ELSIF (l_measure_rec.dimension2_id = l_dim1_id) THEN
3947 l_dim2_level_id := x_target_level_rec.dimension1_level_id;
3948 l_dim2_level_short_name := p_target_level_rec.dimension1_level_short_name;
3949 l_dim2_level_name := x_target_level_rec.dimension1_level_name;
3950 -- l_dim2_level_value_id := p_dim1_level_value_id;
3951 ELSIF (l_measure_rec.dimension3_id = l_dim1_id) THEN
3952 l_dim3_level_id := x_target_level_rec.dimension1_level_id;
3953 l_dim3_level_short_name := p_target_level_rec.dimension1_level_short_name;
3954 l_dim3_level_name := x_target_level_rec.dimension1_level_name;
3955 --l_dim3_level_value_id := p_dim1_level_value_id;
3956 ELSIF (l_measure_rec.dimension4_id = l_dim1_id) THEN
3957 l_dim4_level_id := x_target_level_rec.dimension1_level_id;
3958 l_dim4_level_short_name := p_target_level_rec.dimension1_level_short_name;
3959 l_dim4_level_name := x_target_level_rec.dimension1_level_name;
3960 -- l_dim4_level_value_id := p_dim1_level_value_id;
3961 ELSIF (l_measure_rec.dimension5_id = l_dim1_id) THEN
3962 l_dim5_level_id := x_target_level_rec.dimension1_level_id;
3963 l_dim5_level_short_name := p_target_level_rec.dimension1_level_short_name;
3964 l_dim5_level_name := x_target_level_rec.dimension1_level_name;
3965 -- l_dim5_level_value_id := p_dim1_level_value_id;
3966 ELSIF (l_measure_rec.dimension6_id = l_dim1_id) THEN
3967 l_dim6_level_id := x_target_level_rec.dimension1_level_id;
3968 l_dim6_level_short_name := p_target_level_rec.dimension1_level_short_name;
3969 l_dim6_level_name := x_target_level_rec.dimension1_level_name;
3970 -- l_dim6_level_value_id := p_dim1_level_value_id;
3971 ELSIF (l_measure_rec.dimension7_id = l_dim1_id) THEN
3972 l_dim7_level_id := x_target_level_rec.dimension1_level_id;
3973 l_dim7_level_short_name := p_target_level_rec.dimension1_level_short_name;
3974 l_dim7_level_name := x_target_level_rec.dimension1_level_name;
3975 -- l_dim7_level_value_id := p_dim1_level_value_id;
3976 END IF;
3977 IF (l_measure_rec.dimension1_id = l_dim2_id) THEN
3978 l_dim1_level_id := x_target_level_rec.dimension2_level_id;
3979 l_dim1_level_short_name := p_target_level_rec.dimension2_level_short_name;
3980 l_dim1_level_name := x_target_level_rec.dimension2_level_name;
3981 -- l_dim1_level_value_id := p_dim2_level_value_id;
3982 ELSIF (l_measure_rec.dimension2_id = l_dim2_id) THEN
3983 l_dim2_level_id := x_target_level_rec.dimension2_level_id;
3984 l_dim2_level_short_name := p_target_level_rec.dimension2_level_short_name;
3985 l_dim2_level_name := x_target_level_rec.dimension2_level_name;
3986 -- l_dim2_level_value_id := p_dim2_level_value_id;
3987 ELSIF (l_measure_rec.dimension3_id = l_dim2_id) THEN
3988 l_dim3_level_id := x_target_level_rec.dimension2_level_id;
3989 l_dim3_level_short_name := p_target_level_rec.dimension2_level_short_name;
3990 l_dim3_level_name := x_target_level_rec.dimension2_level_name;
3991 -- l_dim3_level_value_id := p_dim2_level_value_id;
3992 ELSIF (l_measure_rec.dimension4_id = l_dim2_id) THEN
3993 l_dim4_level_id := x_target_level_rec.dimension2_level_id;
3994 l_dim4_level_short_name := p_target_level_rec.dimension2_level_short_name;
3995 l_dim4_level_name := x_target_level_rec.dimension2_level_name;
3996 -- l_dim4_level_value_id := p_dim2_level_value_id;
3997 ELSIF (l_measure_rec.dimension5_id = l_dim2_id) THEN
3998 l_dim5_level_id := x_target_level_rec.dimension2_level_id;
3999 l_dim5_level_short_name := p_target_level_rec.dimension2_level_short_name;
4000 l_dim5_level_name := x_target_level_rec.dimension2_level_name;
4001 --l_dim5_level_value_id := p_dim2_level_value_id;
4002 ELSIF (l_measure_rec.dimension6_id = l_dim2_id) THEN
4003 l_dim6_level_id := x_target_level_rec.dimension2_level_id;
4004 l_dim6_level_short_name := p_target_level_rec.dimension2_level_short_name;
4005 l_dim6_level_name := x_target_level_rec.dimension2_level_name;
4006 --l_dim6_level_value_id := p_dim2_level_value_id;
4007 ELSIF (l_measure_rec.dimension7_id = l_dim2_id) THEN
4008 l_dim7_level_id := x_target_level_rec.dimension2_level_id;
4009 l_dim7_level_short_name := p_target_level_rec.dimension2_level_short_name;
4010 l_dim7_level_name := x_target_level_rec.dimension2_level_name;
4011 -- l_dim7_level_value_id := p_dim2_level_value_id;
4012 END IF;
4013 IF (l_measure_rec.dimension1_id = l_dim3_id) THEN
4014 l_dim1_level_id := x_target_level_rec.dimension3_level_id;
4015 l_dim1_level_short_name := p_target_level_rec.dimension3_level_short_name;
4016 l_dim1_level_name := x_target_level_rec.dimension3_level_name;
4017 --l_dim1_level_value_id := p_dim3_level_value_id;
4018 ELSIF (l_measure_rec.dimension2_id = l_dim3_id) THEN
4019 l_dim2_level_id := x_target_level_rec.dimension3_level_id;
4020 l_dim2_level_short_name := p_target_level_rec.dimension3_level_short_name;
4021 l_dim2_level_name := x_target_level_rec.dimension3_level_name;
4022 --l_dim2_level_value_id := p_dim3_level_value_id;
4023 ELSIF (l_measure_rec.dimension3_id = l_dim3_id) THEN
4024 l_dim3_level_id := x_target_level_rec.dimension3_level_id;
4025 l_dim3_level_short_name := p_target_level_rec.dimension3_level_short_name;
4026 l_dim3_level_name := x_target_level_rec.dimension3_level_name;
4027 --l_dim3_level_value_id := p_dim3_level_value_id;
4028 ELSIF (l_measure_rec.dimension4_id = l_dim3_id) THEN
4029 l_dim4_level_id := x_target_level_rec.dimension3_level_id;
4030 l_dim4_level_short_name := p_target_level_rec.dimension3_level_short_name;
4031 l_dim4_level_name := x_target_level_rec.dimension3_level_name;
4032 -- l_dim4_level_value_id := p_dim3_level_value_id;
4033 ELSIF (l_measure_rec.dimension5_id = l_dim3_id) THEN
4034 l_dim5_level_id := x_target_level_rec.dimension3_level_id;
4035 l_dim5_level_short_name := p_target_level_rec.dimension3_level_short_name;
4036 l_dim5_level_name := x_target_level_rec.dimension3_level_name;
4037 -- l_dim5_level_value_id := p_dim3_level_value_id;
4038 ELSIF (l_measure_rec.dimension6_id = l_dim3_id) THEN
4039 l_dim6_level_id := x_target_level_rec.dimension3_level_id;
4040 l_dim6_level_short_name := p_target_level_rec.dimension3_level_short_name;
4041 l_dim6_level_name := x_target_level_rec.dimension3_level_name;
4042 --l_dim6_level_value_id := p_dim3_level_value_id;
4043 ELSIF (l_measure_rec.dimension7_id = l_dim3_id) THEN
4044 l_dim7_level_id := x_target_level_rec.dimension3_level_id;
4045 l_dim7_level_short_name := p_target_level_rec.dimension3_level_short_name;
4046 l_dim7_level_name := x_target_level_rec.dimension3_level_name;
4047 -- l_dim7_level_value_id := p_dim3_level_value_id;
4048 END IF;
4049 IF (l_measure_rec.dimension1_id = l_dim4_id) THEN
4050 l_dim1_level_id := x_target_level_rec.dimension4_level_id;
4051 l_dim1_level_short_name := p_target_level_rec.dimension4_level_short_name;
4052 l_dim1_level_name := x_target_level_rec.dimension4_level_name;
4053 -- l_dim1_level_value_id := p_dim4_level_value_id;
4054 ELSIF (l_measure_rec.dimension2_id = l_dim4_id) THEN
4055 l_dim2_level_id := x_target_level_rec.dimension4_level_id;
4056 l_dim2_level_short_name := p_target_level_rec.dimension4_level_short_name;
4057 l_dim2_level_name := x_target_level_rec.dimension4_level_name;
4058 -- l_dim2_level_value_id := p_dim4_level_value_id;
4059 ELSIF (l_measure_rec.dimension3_id = l_dim4_id) THEN
4060 l_dim3_level_id := x_target_level_rec.dimension4_level_id;
4061 l_dim3_level_short_name := p_target_level_rec.dimension4_level_short_name;
4062 l_dim3_level_name := x_target_level_rec.dimension4_level_name;
4063 -- l_dim3_level_value_id := p_dim4_level_value_id;
4064 ELSIF (l_measure_rec.dimension4_id = l_dim4_id) THEN
4065 l_dim4_level_id := x_target_level_rec.dimension4_level_id;
4066 l_dim4_level_short_name := p_target_level_rec.dimension4_level_short_name;
4067 l_dim4_level_name := x_target_level_rec.dimension4_level_name;
4068 -- l_dim4_level_value_id := p_dim4_level_value_id;
4069 ELSIF (l_measure_rec.dimension5_id = l_dim4_id) THEN
4070 l_dim5_level_id := x_target_level_rec.dimension4_level_id;
4071 l_dim5_level_short_name := p_target_level_rec.dimension4_level_short_name;
4072 l_dim5_level_name := x_target_level_rec.dimension4_level_name;
4073 -- l_dim5_level_value_id := p_dim4_level_value_id;
4074 ELSIF (l_measure_rec.dimension6_id = l_dim4_id) THEN
4075 l_dim6_level_id := x_target_level_rec.dimension4_level_id;
4076 l_dim6_level_short_name := p_target_level_rec.dimension4_level_short_name;
4077 l_dim6_level_name := x_target_level_rec.dimension4_level_name;
4078 -- l_dim6_level_value_id := p_dim4_level_value_id;
4079 ELSIF (l_measure_rec.dimension7_id = l_dim4_id) THEN
4080 l_dim7_level_id := x_target_level_rec.dimension4_level_id;
4081 l_dim7_level_short_name := p_target_level_rec.dimension4_level_short_name;
4082 l_dim7_level_name := x_target_level_rec.dimension4_level_name;
4083 -- l_dim7_level_value_id := p_dim4_level_value_id;
4084 END IF;
4085 IF (l_measure_rec.dimension1_id = l_dim5_id) THEN
4086 l_dim1_level_id := x_target_level_rec.dimension5_level_id;
4087 l_dim1_level_short_name := p_target_level_rec.dimension5_level_short_name;
4088 l_dim1_level_name := x_target_level_rec.dimension5_level_name;
4089 -- l_dim1_level_value_id := p_dim5_level_value_id;
4090 ELSIF (l_measure_rec.dimension2_id = l_dim5_id) THEN
4091 l_dim2_level_id := x_target_level_rec.dimension5_level_id;
4092 l_dim2_level_short_name := p_target_level_rec.dimension5_level_short_name;
4093 l_dim2_level_name := x_target_level_rec.dimension5_level_name;
4094 -- l_dim2_level_value_id := p_dim5_level_value_id;
4095 ELSIF (l_measure_rec.dimension3_id = l_dim5_id) THEN
4096 l_dim3_level_id := x_target_level_rec.dimension5_level_id;
4097 l_dim3_level_short_name := p_target_level_rec.dimension5_level_short_name;
4098 l_dim3_level_name := x_target_level_rec.dimension5_level_name;
4099 -- l_dim3_level_value_id := p_dim5_level_value_id;
4100 ELSIF (l_measure_rec.dimension4_id = l_dim5_id) THEN
4101 l_dim4_level_id := x_target_level_rec.dimension5_level_id;
4102 l_dim4_level_short_name := p_target_level_rec.dimension5_level_short_name;
4103 l_dim4_level_name := x_target_level_rec.dimension5_level_name;
4104 -- l_dim4_level_value_id := p_dim5_level_value_id;
4105 ELSIF (l_measure_rec.dimension5_id = l_dim5_id) THEN
4106 l_dim5_level_id := x_target_level_rec.dimension5_level_id;
4107 l_dim5_level_short_name := p_target_level_rec.dimension5_level_short_name;
4108 l_dim5_level_name := x_target_level_rec.dimension5_level_name;
4109 -- l_dim5_level_value_id := p_dim5_level_value_id;
4110 ELSIF (l_measure_rec.dimension6_id = l_dim5_id) THEN
4111 l_dim6_level_id := x_target_level_rec.dimension5_level_id;
4112 l_dim6_level_short_name := p_target_level_rec.dimension5_level_short_name;
4113 l_dim6_level_name := x_target_level_rec.dimension5_level_name;
4114 -- l_dim6_level_value_id := p_dim5_level_value_id;
4115 ELSIF (l_measure_rec.dimension7_id = l_dim5_id) THEN
4116 l_dim7_level_id := x_target_level_rec.dimension5_level_id;
4117 l_dim7_level_short_name := p_target_level_rec.dimension5_level_short_name;
4118 l_dim7_level_name := x_target_level_rec.dimension5_level_name;
4119 -- l_dim7_level_value_id := p_dim5_level_value_id;
4120 END IF;
4121 IF (l_measure_rec.dimension1_id = l_dim6_id) THEN
4122 l_dim1_level_id := x_target_level_rec.dimension6_level_id;
4123 l_dim1_level_short_name := p_target_level_rec.dimension6_level_short_name;
4124 l_dim1_level_name := x_target_level_rec.dimension6_level_name;
4125 -- l_dim1_level_value_id := p_dim6_level_value_id;
4126 ELSIF (l_measure_rec.dimension2_id = l_dim6_id) THEN
4127 l_dim2_level_id := x_target_level_rec.dimension6_level_id;
4128 l_dim2_level_short_name := p_target_level_rec.dimension6_level_short_name;
4129 l_dim2_level_name := x_target_level_rec.dimension6_level_name;
4130 -- l_dim2_level_value_id := p_dim6_level_value_id;
4131 ELSIF (l_measure_rec.dimension3_id = l_dim6_id) THEN
4132 l_dim3_level_id := x_target_level_rec.dimension6_level_id;
4133 l_dim3_level_short_name := p_target_level_rec.dimension6_level_short_name;
4134 l_dim3_level_name := x_target_level_rec.dimension6_level_name;
4135 -- l_dim3_level_value_id := p_dim6_level_value_id;
4136 ELSIF (l_measure_rec.dimension4_id = l_dim6_id) THEN
4137 l_dim4_level_id := x_target_level_rec.dimension6_level_id;
4138 l_dim4_level_short_name := p_target_level_rec.dimension6_level_short_name;
4139 l_dim4_level_name := x_target_level_rec.dimension6_level_name;
4140 -- l_dim4_level_value_id := p_dim6_level_value_id;
4141 ELSIF (l_measure_rec.dimension5_id = l_dim6_id) THEN
4142 l_dim5_level_id := x_target_level_rec.dimension6_level_id;
4143 l_dim5_level_short_name := p_target_level_rec.dimension6_level_short_name;
4144 l_dim5_level_name := x_target_level_rec.dimension6_level_name;
4145 -- l_dim5_level_value_id := p_dim6_level_value_id;
4146 ELSIF (l_measure_rec.dimension6_id = l_dim6_id) THEN
4147 l_dim6_level_id := x_target_level_rec.dimension6_level_id;
4148 l_dim6_level_short_name := p_target_level_rec.dimension6_level_short_name;
4149 l_dim6_level_name := x_target_level_rec.dimension6_level_name;
4150 -- l_dim6_level_value_id := p_dim6_level_value_id;
4151 ELSIF (l_measure_rec.dimension7_id = l_dim6_id) THEN
4152 l_dim7_level_id := x_target_level_rec.dimension6_level_id;
4153 l_dim7_level_short_name := p_target_level_rec.dimension6_level_short_name;
4154 l_dim7_level_name := x_target_level_rec.dimension6_level_name;
4155 -- l_dim7_level_value_id := p_dim6_level_value_id;
4156 END IF;
4157 IF (l_measure_rec.dimension1_id = l_dim7_id) THEN
4158 l_dim1_level_id := x_target_level_rec.dimension7_level_id;
4159 l_dim1_level_short_name := p_target_level_rec.dimension7_level_short_name;
4160 l_dim1_level_name := x_target_level_rec.dimension7_level_name;
4161 -- l_dim1_level_value_id := p_dim7_level_value_id;
4162 ELSIF (l_measure_rec.dimension2_id = l_dim7_id) THEN
4163 l_dim2_level_id := x_target_level_rec.dimension7_level_id;
4164 l_dim2_level_short_name := p_target_level_rec.dimension7_level_short_name;
4165 l_dim2_level_name := x_target_level_rec.dimension7_level_name;
4166 -- l_dim2_level_value_id := p_dim7_level_value_id;
4167 ELSIF (l_measure_rec.dimension3_id = l_dim7_id) THEN
4168 l_dim3_level_id := x_target_level_rec.dimension7_level_id;
4169 l_dim3_level_short_name := p_target_level_rec.dimension7_level_short_name;
4170 l_dim3_level_name := x_target_level_rec.dimension7_level_name;
4171 --l_dim3_level_value_id := p_dim7_level_value_id;
4172 ELSIF (l_measure_rec.dimension4_id = l_dim7_id) THEN
4173 l_dim4_level_id := x_target_level_rec.dimension7_level_id;
4174 l_dim4_level_short_name := p_target_level_rec.dimension7_level_short_name;
4175 l_dim4_level_name := x_target_level_rec.dimension7_level_name;
4176 -- l_dim4_level_value_id := p_dim7_level_value_id;
4177 ELSIF (l_measure_rec.dimension5_id = l_dim7_id) THEN
4178 l_dim5_level_id := x_target_level_rec.dimension7_level_id;
4179 l_dim5_level_short_name := p_target_level_rec.dimension7_level_short_name;
4180 l_dim5_level_name := x_target_level_rec.dimension7_level_name;
4181 -- l_dim5_level_value_id := p_dim7_level_value_id;
4182 ELSIF (l_measure_rec.dimension6_id = l_dim7_id) THEN
4183 l_dim6_level_id := x_target_level_rec.dimension7_level_id;
4184 l_dim6_level_short_name := p_target_level_rec.dimension7_level_short_name;
4185 l_dim6_level_name := x_target_level_rec.dimension7_level_name;
4186 -- l_dim6_level_value_id := p_dim7_level_value_id;
4187 ELSIF (l_measure_rec.dimension7_id = l_dim7_id) THEN
4188 l_dim7_level_id := x_target_level_rec.dimension7_level_id;
4189 l_dim7_level_short_name := p_target_level_rec.dimension7_level_short_name;
4190 l_dim7_level_name := x_target_level_rec.dimension7_level_name;
4191 -- l_dim7_level_value_id := p_dim7_level_value_id;
4192 END IF;
4193
4194 x_Target_Level_Rec.Measure_ID := l_Measure_Rec.Measure_ID;
4195 x_Target_Level_Rec.Dimension1_Level_ID := NVL(l_DIM1_LEVEL_ID,BIS_UTILITIES_PUB.G_NULL_NUM);
4196 x_Target_Level_Rec.Dimension2_Level_ID := NVL(l_DIM2_LEVEL_ID,BIS_UTILITIES_PUB.G_NULL_NUM);
4197 x_Target_Level_Rec.Dimension3_Level_ID := NVL(l_DIM3_LEVEL_ID,BIS_UTILITIES_PUB.G_NULL_NUM);
4198 x_Target_Level_Rec.Dimension4_Level_ID := NVL(l_DIM4_LEVEL_ID,BIS_UTILITIES_PUB.G_NULL_NUM);
4199 x_Target_Level_Rec.Dimension5_Level_ID := NVL(l_DIM5_LEVEL_ID,BIS_UTILITIES_PUB.G_NULL_NUM);
4200 x_Target_Level_Rec.Dimension6_Level_ID := NVL(l_DIM6_LEVEL_ID,BIS_UTILITIES_PUB.G_NULL_NUM);
4201 x_Target_Level_Rec.Dimension7_Level_ID := NVL(l_DIM7_LEVEL_ID,BIS_UTILITIES_PUB.G_NULL_NUM);
4202 l_target_level_id := Get_Level_Id_From_Dimlevels(x_target_level_rec) ;
4203 x_Target_Level_Rec.Target_Level_Id := l_target_level_id;
4204 x_Target_Level_Rec.Dimension1_Level_Short_Name := NVL(l_DIM1_LEVEL_SHORT_NAME,BIS_UTILITIES_PUB.G_NULL_CHAR);
4205 x_Target_Level_Rec.Dimension2_Level_Short_Name := NVL(l_DIM2_LEVEL_SHORT_NAME,BIS_UTILITIES_PUB.G_NULL_CHAR);
4206 x_Target_Level_Rec.Dimension3_Level_Short_Name := NVL(l_DIM3_LEVEL_SHORT_NAME,BIS_UTILITIES_PUB.G_NULL_CHAR);
4207 x_Target_Level_Rec.Dimension4_Level_Short_Name := NVL(l_DIM4_LEVEL_SHORT_NAME,BIS_UTILITIES_PUB.G_NULL_CHAR);
4208 x_Target_Level_Rec.Dimension5_Level_Short_Name := NVL(l_DIM5_LEVEL_SHORT_NAME,BIS_UTILITIES_PUB.G_NULL_CHAR);
4209 x_Target_Level_Rec.Dimension6_Level_Short_Name := NVL(l_DIM6_LEVEL_SHORT_NAME,BIS_UTILITIES_PUB.G_NULL_CHAR);
4210 x_Target_Level_Rec.Dimension7_Level_Short_Name := NVL(l_DIM7_LEVEL_SHORT_NAME,BIS_UTILITIES_PUB.G_NULL_CHAR);
4211 x_Target_Level_Rec.Dimension1_Level_Name := NVL(l_DIM1_LEVEL_NAME,BIS_UTILITIES_PUB.G_NULL_CHAR);
4212 x_Target_Level_Rec.Dimension2_Level_Name := NVL(l_DIM2_LEVEL_NAME,BIS_UTILITIES_PUB.G_NULL_CHAR);
4213 x_Target_Level_Rec.Dimension3_Level_Name := NVL(l_DIM3_LEVEL_NAME,BIS_UTILITIES_PUB.G_NULL_CHAR);
4214 x_Target_Level_Rec.Dimension4_Level_Name := NVL(l_DIM4_LEVEL_NAME,BIS_UTILITIES_PUB.G_NULL_CHAR);
4215 x_Target_Level_Rec.Dimension5_Level_Name := NVL(l_DIM5_LEVEL_NAME,BIS_UTILITIES_PUB.G_NULL_CHAR);
4216 x_Target_Level_Rec.Dimension6_Level_Name := NVL(l_DIM6_LEVEL_NAME,BIS_UTILITIES_PUB.G_NULL_CHAR);
4217 x_Target_Level_Rec.Dimension7_Level_Name := NVL(l_DIM7_LEVEL_NAME,BIS_UTILITIES_PUB.G_NULL_CHAR);
4218
4219 EXCEPTION
4220 when others then
4221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4222 --Added last two parameters
4223 l_error_tbl := x_error_tbl;
4224 BIS_UTILITIES_PVT.Add_Error_Message
4225 ( p_error_msg_id => SQLCODE
4226 , p_error_description => SQLERRM
4227 , p_error_proc_name => G_PKG_NAME||'.Create_Target_Level'
4228 , p_error_table => l_error_tbl
4229 , x_error_table => x_error_tbl
4230 );
4231 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4232
4233 END Retrieve_TL_From_DimLvlShNms;
4234 --------------------------------
4235
4236 -- Given a target level short name update the
4237 -- bis_target_levels, bis_target_levels_tl
4238 -- for last_updated_by , created_by as 1
4239 PROCEDURE updt_tl_attributes(p_tl_short_name IN VARCHAR2
4240 ,p_tl_new_short_name IN VARCHAR2
4241 ,x_return_status OUT NOCOPY VARCHAR2) AS
4242 CURSOR c_updt1 IS
4243 SELECT target_level_id , last_updated_by , created_by
4244 FROM bis_target_levels
4245 WHERE short_name = p_tl_short_name FOR UPDATE OF last_updated_by , created_by;
4246
4247 l_pm_count NUMBER := 0;
4248
4249 BEGIN
4250
4251 x_return_status := FND_API.G_RET_STS_SUCCESS;
4252
4253 FOR i IN c_updt1 LOOP
4254
4255 l_pm_count := l_pm_count + 1;
4256
4257 IF p_tl_new_short_name IS NOT NULL THEN
4258 UPDATE bis_target_levels SET last_updated_by = 1 , created_by = 1, short_name = p_tl_new_short_name
4259 WHERE current of c_updt1;
4260 ELSE
4261 UPDATE bis_target_levels SET last_updated_by = 1 , created_by = 1
4262 WHERE current of c_updt1;
4263 END IF;
4264
4265 UPDATE bis_target_levels_tl SET last_updated_by = 1 , created_by = 1
4266 WHERE target_level_id = i.target_level_id;
4267
4268 END LOOP;
4269
4270 if l_pm_count = 0 then
4271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4272 end if;
4273
4274 EXCEPTION
4275 WHEN OTHERS THEN
4276
4277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4278
4279 IF c_updt1%ISOPEN THEN
4280 CLOSE c_updt1;
4281 END IF;
4282
4283 END updt_tl_attributes;
4284
4285
4286
4287 PROCEDURE Validate_Dimensions -- Procedure added for 2486702
4288 (
4289 p_target_level_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type,
4290 x_return_status OUT NOCOPY VARCHAR2,
4291 x_return_msg OUT NOCOPY VARCHAR2
4292 ) IS
4293 l_measure_id NUMBER;
4294 l_measure_rec BIS_MEASURE_PUB.MEASURE_REC_TYPE;
4295 l_measure_rec_p BIS_MEASURE_PUB.MEASURE_REC_TYPE;
4296 l_return_status VARCHAR2(100);
4297 l_return_msg VARCHAR2(3000);
4298 l_level_short_nm bis_levels.short_name%TYPE;
4299 l_dim_short_nm bis_dimensions.short_name%TYPE;
4300 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
4301
4302 BEGIN
4303
4304 SELECT indicator_id
4305 INTO l_measure_id
4306 FROM bis_indicators
4307 WHERE short_name = p_Target_Level_rec.Measure_short_Name;
4308
4309 l_measure_rec.measure_id := l_measure_id ;
4310
4311 l_measure_rec_p := l_measure_rec;
4312 BIS_MEASURE_PVT.Retrieve_Measure
4313 ( p_api_version => 1.0
4314 , p_Measure_Rec => l_measure_rec_p
4315 , p_all_info => FND_API.G_TRUE
4316 , x_Measure_Rec => l_measure_rec
4317 , x_return_status => l_return_status
4318 , x_error_Tbl => l_error_Tbl
4319 );
4320
4321 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
4322 RAISE FND_API.G_EXC_ERROR;
4323 END IF;
4324
4325 Level_Correspond_To_Dim
4326 (
4327 p_target_level_rec => p_target_level_rec,
4328 p_measure_rec => l_measure_rec,
4329 x_return_status => l_return_status,
4330 x_return_msg => l_return_msg
4331 );
4332
4333 x_return_status := l_return_status ;
4334
4335 IF (l_return_status = 'E') THEN
4336 RAISE FND_API.G_EXC_ERROR;
4337 END IF;
4338
4339 EXCEPTION
4340 WHEN NO_DATA_FOUND THEN
4341 x_return_status := 'E';
4342 /* -- 2515991
4343 BIS_UTILITIES_PUB.put_line(p_text => 'Error in validation of Performance Measure for the Summary Level '
4344 || nvl (p_target_level_rec.target_level_short_name, ' ' )
4345 || '. ' || sqlerrm
4346 || ' The measure ' || nvl(p_Target_Level_rec.Measure_short_Name, ' ')
4347 || ' was not found in the target system. '
4348 || ' This Summary Level will not be created. ') ;
4349 */
4350
4351 WHEN OTHERS THEN
4352 x_return_status := 'E';
4353 /* -- 2515991
4354 BIS_UTILITIES_PUB.put_line(p_text => 'Error in validation of dimension levels for the Summary Level '
4355 || nvl (p_target_level_rec.target_level_short_name, ' ' ) || '.'
4356 || ' This Summary Level will not be created. ') ;
4357 */
4358
4359 END;
4360
4361
4362 PROCEDURE Level_Correspond_To_Dim -- Procedure added for 2486702
4363 (
4364 p_target_level_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
4365 , p_measure_rec IN BIS_MEASURE_PUB.MEASURE_REC_TYPE
4366 , x_return_status OUT NOCOPY VARCHAR2
4367 , x_return_msg OUT NOCOPY VARCHAR2
4368 ) IS
4369
4370 l_return_status1 VARCHAR2(100);
4371 l_return_status2 VARCHAR2(100);
4372 l_unique_levels BOOLEAN;
4373 l_unique_dims BOOLEAN;
4374 l_return_status4 VARCHAR2(100);
4375 l_return_msg VARCHAR2(3000);
4376 l_num_dims NUMBER := 0;
4377 l_num_levels NUMBER := 0;
4378 l_dim_array dim_tbl_type;
4379 l_lvl_array lvl_tbl_type;
4380 l_error_msg VARCHAR2(1000);
4381
4382 BEGIN
4383
4384 GET_MEASURE_DIMS_ARRAY
4385 ( p_measure_rec => p_measure_rec
4386 , x_dim_tbl_type => l_dim_array
4387 , x_num_dims => l_num_dims
4388 , x_return_status => l_return_status1
4389 , x_return_msg => l_return_msg
4390 );
4391
4392 l_unique_levels := CHECK_UNIQUE_DIMS
4393 (p_dim_tbl_type => l_dim_array);
4394
4395
4396 GET_TL_LVLS_ARRAY
4397 ( p_target_level_rec => p_target_level_rec
4398 , x_lvl_tbl_type => l_lvl_array
4399 , x_num_lvls => l_num_levels
4400 , x_return_status => l_return_status2
4401 , x_return_msg => l_return_msg
4402 );
4403
4404
4405 l_unique_dims := CHECK_UNIQUE_LEVELS
4406 (p_lvl_tbl_type => l_lvl_array);
4407
4408
4409 IF ( (l_return_status1 = 'E')
4410 OR (l_return_status2 = 'E')
4411 OR (NOT(l_unique_levels))
4412 OR (NOT(l_unique_dims)) ) THEN
4413
4414 RAISE FND_API.G_EXC_ERROR;
4415
4416 END IF;
4417
4418 COMPARE_LEVELS_DIMS
4419 ( p_dim_tbl_type => l_dim_array
4420 , p_lvl_tbl_type => l_lvl_array
4421 , p_tl_short_name => p_target_level_rec.target_level_short_name
4422 , p_pm_short_name => p_target_level_rec.measure_short_name
4423 , x_return_status => l_return_status1
4424 , x_return_msg => l_return_msg
4425 );
4426
4427 IF (l_return_status1 = 'E') THEN
4428 RAISE FND_API.G_EXC_ERROR;
4429 END IF;
4430
4431 x_return_status := 'S';
4432
4433
4434 EXCEPTION
4435 WHEN OTHERS THEN
4436 -- BIS_UTILITIES_PUB.put_line(p_text => ' Error in Level_Correspond_To_Dim in BIS_TARGET_LEVEL_PVT -- ' || sqlerrm ) ; -- 2515991
4437 x_return_status := 'E';
4438
4439 END Level_Correspond_To_Dim;
4440
4441
4442 PROCEDURE COMPARE_LEVELS_DIMS -- Procedure added for 2486702
4443 ( p_dim_tbl_type IN dim_tbl_type
4444 , p_lvl_tbl_type IN lvl_tbl_type
4445 , p_tl_short_name IN VARCHAR2
4446 , p_pm_short_name IN VARCHAR2
4447 , x_return_status OUT NOCOPY VARCHAR2
4448 , x_return_msg OUT NOCOPY VARCHAR2
4449 )
4450 IS
4451
4452 i NUMBER;
4453 j NUMBER;
4454 l_dim_id NUMBER;
4455 l_match BOOLEAN;
4456 l_dim_short_name bis_dimensions.short_name%TYPE := NULL;
4457 l_lvl_short_name bis_levels.short_name%TYPE := NULL;
4458 l_exists NUMBER := NULL;
4459 l_dim_present BOOLEAN;
4460 l_lvl_present BOOLEAN;
4461 -- l_error_msg VARCHAR2(1000);
4462
4463 BEGIN
4464
4465
4466 FOR i IN 1..p_lvl_tbl_type.COUNT LOOP
4467
4468 l_lvl_short_name := p_lvl_tbl_type(i);
4469
4470 l_match := FALSE;
4471
4472 l_dim_id := GET_DIM_ID_FRM_LVL_SHTNM
4473 ( p_level_shtnm => l_lvl_short_name);
4474
4475 IF NOT(IS_NOT_NULL_MISSING_NUM(l_dim_id)) THEN
4476 RAISE FND_API.G_EXC_ERROR;
4477 END IF;
4478
4479 FOR j IN 1..p_dim_tbl_type.COUNT LOOP
4480 IF (l_dim_id = p_dim_tbl_type(j)) THEN
4481 l_match := TRUE; -- l_dim_short_name := GET_DIM_SHTNM_FRM_ID ( p_dim_id => l_dim_id);
4482 EXIT;
4483 END IF;
4484 END LOOP;
4485
4486 IF (l_match = FALSE) THEN
4487
4488 /* -- 2515991
4489 l_error_msg := 'In the definition of Summary Level '
4490 || nvl( p_tl_short_name , ' ')
4491 || ' for the Performance Measure '
4492 || nvl( p_pm_short_name , ' ' )
4493 || ' , there is no Dimension corresponding to the Level '
4494 || nvl( l_lvl_short_name, ' ' )
4495 || '. Upload of this Summary Level is therefore aborted.';
4496
4497 BIS_UTILITIES_PUB.put_line(p_text => l_error_msg ) ;
4498 */
4499
4500 RAISE FND_API.G_EXC_ERROR;
4501
4502 END IF;
4503
4504 END LOOP;
4505
4506
4507 FOR i IN 1..p_dim_tbl_type.COUNT LOOP
4508
4509 l_match := FALSE;
4510
4511 FOR j IN 1..p_lvl_tbl_type.COUNT LOOP
4512
4513 l_lvl_short_name := p_lvl_tbl_type(j);
4514
4515 l_dim_id := GET_DIM_ID_FRM_LVL_SHTNM
4516 ( p_level_shtnm => l_lvl_short_name);
4517
4518 IF (l_dim_id = p_dim_tbl_type(i)) THEN
4519 l_match := TRUE;
4520 EXIT;
4521 END IF;
4522 END LOOP;
4523
4524 IF (l_match = FALSE) THEN
4525
4526 SELECT short_name
4527 INTO l_dim_short_name
4528 FROM bis_dimensions
4529 WHERE dimension_id = p_dim_tbl_type(i);
4530
4531 /* -- 2515991
4532 l_error_msg := 'In the definition of Summary Level '
4533 || nvl( p_tl_short_name , ' ')
4534 || ' for the Performance Measure '
4535 || nvl( p_pm_short_name , ' ' )
4536 || ' , there is no Dimension Level corresponding to the Dimension '
4537 || nvl( l_dim_short_name , ' ' )
4538 || '. Upload of this Summary Level is therefore aborted.';
4539 BIS_UTILITIES_PUB.put_line(p_text => l_error_msg ) ;
4540 */
4541
4542 RAISE FND_API.G_EXC_ERROR;
4543 END IF;
4544
4545 END LOOP;
4546
4547 x_return_status := 'S';
4548
4549
4550 EXCEPTION
4551 WHEN OTHERS THEN
4552 x_return_status := 'E';
4553 -- BIS_UTILITIES_PUB.put_line(p_text => ' Error in COMPARE_LEVELS_DIMS in BIS_TARGET_LEVEL_PVT -- ' || sqlerrm ) ; -- 2515991
4554
4555 END COMPARE_LEVELS_DIMS;
4556
4557
4558
4559 FUNCTION GET_DIM_ID_FRM_LVL_SHTNM -- Function added for 2486702
4560 ( p_level_shtnm IN VARCHAR2)
4561 RETURN NUMBER
4562 IS
4563 l_dim_id NUMBER := NULL;
4564
4565 BEGIN
4566
4567 SELECT dimension_id
4568 INTO l_dim_id
4569 FROM bis_levels
4570 WHERE short_name = p_level_shtnm;
4571
4572 RETURN l_dim_id;
4573
4574 EXCEPTION
4575 WHEN NO_DATA_FOUND THEN
4576 -- BIS_UTILITIES_PUB.put_line(p_text => 'There is no dimension corresponding to level ' || nvl( p_level_shtnm , ' ' ) ); -- 2515991
4577 RETURN NULL;
4578 WHEN OTHERS THEN
4579 -- BIS_UTILITIES_PUB.put_line(p_text => ' Error in GET_DIM_ID_FRM_LVL_SHTNM in BIS_TARGET_LEVEL_PVT -- ' || sqlerrm );-- 2515991
4580 RETURN NULL;
4581 END GET_DIM_ID_FRM_LVL_SHTNM;
4582
4583
4584
4585 PROCEDURE GET_MEASURE_DIMS_ARRAY -- Procedure added for 2486702
4586 ( p_measure_rec IN BIS_MEASURE_PUB.MEASURE_REC_TYPE
4587 , x_dim_tbl_type OUT NOCOPY dim_tbl_type
4588 , x_num_dims OUT NOCOPY NUMBER
4589 , x_return_status OUT NOCOPY VARCHAR2
4590 , x_return_msg OUT NOCOPY VARCHAR2
4591 )
4592 IS
4593 l_num_dims NUMBER := 0;
4594 l_dim_tbl_type dim_tbl_type;
4595
4596 BEGIN
4597
4598 SELECT COUNT(1)
4599 INTO l_num_dims
4600 FROM BIS_INDICATOR_DIMENSIONS
4601 WHERE indicator_id = p_measure_rec.measure_id;
4602
4603 IF ( (l_num_dims > 0)
4604 AND (l_num_dims < 8) ) THEN
4605
4606 add_to_measure_array( p_dim_tbl_type => l_dim_tbl_type
4607 , p_dim_id => p_measure_rec.dimension1_id );
4608 add_to_measure_array( p_dim_tbl_type => l_dim_tbl_type
4609 , p_dim_id => p_measure_rec.dimension2_id );
4610 add_to_measure_array( p_dim_tbl_type => l_dim_tbl_type
4611 , p_dim_id => p_measure_rec.dimension3_id );
4612 add_to_measure_array( p_dim_tbl_type => l_dim_tbl_type
4613 , p_dim_id => p_measure_rec.dimension4_id );
4614 add_to_measure_array( p_dim_tbl_type => l_dim_tbl_type
4615 , p_dim_id => p_measure_rec.dimension5_id );
4616 add_to_measure_array( p_dim_tbl_type => l_dim_tbl_type
4617 , p_dim_id => p_measure_rec.dimension6_id );
4618 add_to_measure_array( p_dim_tbl_type => l_dim_tbl_type
4619 , p_dim_id => p_measure_rec.dimension7_id );
4620
4621 x_dim_tbl_type := l_dim_tbl_type;
4622 x_num_dims := l_num_dims;
4623 x_return_status := 'S';
4624
4625 ELSE
4626 RAISE FND_API.G_EXC_ERROR;
4627 END IF;
4628
4629 EXCEPTION
4630 WHEN OTHERS THEN
4631 x_return_status := 'E';
4632 x_num_dims := 0;
4633 -- BIS_UTILITIES_PUB.put_line(p_text => 'The number of dimensions = ' || nvl (l_num_dims, 0) || ' is incorrect. ' ) ; -- 2515991
4634 END GET_MEASURE_DIMS_ARRAY;
4635
4636
4637 PROCEDURE ADD_TO_MEASURE_ARRAY -- Procedure added for 2486702
4638 ( p_dim_tbl_type IN OUT NOCOPY dim_tbl_type
4639 , p_dim_id IN NUMBER
4640 )
4641 IS
4642 BEGIN
4643
4644 IF IS_NOT_NULL_MISSING_NUM(p_dim_id) THEN
4645 p_dim_tbl_type(p_dim_tbl_type.COUNT+1) := p_dim_id;
4646 END IF;
4647
4648 EXCEPTION
4649 WHEN OTHERS THEN
4650 NULL;
4651 -- BIS_UTILITIES_PUB.put_line(p_text => 'Error in ADD_TO_MEASURE_ARRAY in BIS_TARGET_LEVEL_PVT -- ' || sqlerrm ) ; -- 2515991
4652 END ADD_TO_MEASURE_ARRAY;
4653
4654
4655 PROCEDURE GET_TL_LVLS_ARRAY -- Procedure added for 2486702
4656 ( p_target_level_rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
4657 , x_lvl_tbl_type OUT NOCOPY lvl_tbl_type
4658 , x_num_lvls OUT NOCOPY NUMBER
4659 , x_return_status OUT NOCOPY VARCHAR2
4660 , x_return_msg OUT NOCOPY VARCHAR2
4661 )
4662 IS
4663
4664 l_num_levels NUMBER := 0;
4665 l_lvl_tbl_type lvl_tbl_type;
4666
4667 BEGIN
4668
4669 ADD_TO_LEVEL_ARRAY( p_lvl_tbl_type => l_lvl_tbl_type
4670 , p_short_name => p_target_level_rec.ORG_LEVEL_SHORT_NAME);
4671
4672 ADD_TO_LEVEL_ARRAY( p_lvl_tbl_type => l_lvl_tbl_type
4673 , p_short_name => p_target_level_rec.TIME_LEVEL_SHORT_NAME);
4674
4675 ADD_TO_LEVEL_ARRAY( p_lvl_tbl_type => l_lvl_tbl_type
4676 , p_short_name => p_target_level_rec.dimension1_level_short_name);
4677
4678 ADD_TO_LEVEL_ARRAY( p_lvl_tbl_type => l_lvl_tbl_type
4679 , p_short_name => p_target_level_rec.dimension2_level_short_name);
4680
4681 ADD_TO_LEVEL_ARRAY( p_lvl_tbl_type => l_lvl_tbl_type
4682 , p_short_name => p_target_level_rec.dimension3_level_short_name);
4683
4684 ADD_TO_LEVEL_ARRAY( p_lvl_tbl_type => l_lvl_tbl_type
4685 , p_short_name => p_target_level_rec.dimension4_level_short_name);
4686
4687 ADD_TO_LEVEL_ARRAY( p_lvl_tbl_type => l_lvl_tbl_type
4688 , p_short_name => p_target_level_rec.dimension5_level_short_name);
4689
4690 ADD_TO_LEVEL_ARRAY( p_lvl_tbl_type => l_lvl_tbl_type
4691 , p_short_name => p_target_level_rec.dimension6_level_short_name);
4692
4693 ADD_TO_LEVEL_ARRAY( p_lvl_tbl_type => l_lvl_tbl_type
4694 , p_short_name => p_target_level_rec.dimension7_level_short_name);
4695
4696 l_num_levels := l_lvl_tbl_type.COUNT;
4697 IF ( IS_NOT_NULL_MISSING_NUM(l_num_levels) ) THEN
4698 IF ( (l_num_levels>0) AND (l_num_levels<10)) THEN
4699 x_num_lvls := l_num_levels;
4700 ELSE
4701 RAISE FND_API.G_EXC_ERROR;
4702 END IF;
4703 ELSE
4704 RAISE FND_API.G_EXC_ERROR;
4705 END IF;
4706
4707 x_lvl_tbl_type := l_lvl_tbl_type;
4708 x_return_status := 'S';
4709
4710 EXCEPTION
4711 WHEN OTHERS THEN
4712 x_return_status := 'E';
4713 x_num_lvls := 0;
4714 -- BIS_UTILITIES_PUB.put_line(p_text => 'The number of dimension levels = ' || nvl(l_num_levels, 0) || ' is incorrect. ' ) ; -- 2515991
4715 -- BIS_UTILITIES_PUB.put_line(p_text => ' Error in GET_TL_LVLS_ARRAY in BIS_TARGET_LEVEL_PVT -- ' || sqlerrm ) ;
4716 END GET_TL_LVLS_ARRAY;
4717
4718
4719
4720 FUNCTION CHECK_UNIQUE_DIMS -- Function added for 2486702
4721 (p_dim_tbl_type IN dim_tbl_type)
4722 RETURN BOOLEAN IS
4723
4724 i NUMBER;
4725 j NUMBER;
4726 l_num_dims NUMBER;
4727 l_dim_short_name bis_dimensions.short_name%TYPE;
4728
4729 BEGIN
4730
4731 l_num_dims := p_dim_tbl_type.COUNT;
4732
4733 IF NOT(IS_NOT_NULL_MISSING_NUM(l_num_dims)) THEN
4734 RAISE FND_API.G_EXC_ERROR;
4735 END IF;
4736
4737 FOR i IN 1..l_num_dims LOOP
4738 FOR j IN (i+1)..l_num_dims LOOP
4739 IF ( p_dim_tbl_type(i) = p_dim_tbl_type(j) ) THEN
4740
4741 SELECT short_name
4742 INTO l_dim_short_name
4743 FROM BIS_DIMENSIONS
4744 WHERE dimension_id = p_dim_tbl_type(i);
4745
4746 -- BIS_UTILITIES_PUB.put_line(p_text =>'The dimension ' || nvl(l_dim_short_name, ' ') || ' is not unique. ' ); -- 2515991
4747
4748 RAISE FND_API.G_EXC_ERROR;
4749
4750 END IF;
4751 END LOOP;
4752 END LOOP;
4753
4754 RETURN TRUE;
4755
4756 EXCEPTION
4757 WHEN OTHERS THEN
4758 RETURN FALSE;
4759 END CHECK_UNIQUE_DIMS;
4760
4761
4762
4763 PROCEDURE ADD_TO_LEVEL_ARRAY -- Procedure added for 2486702
4764 ( p_lvl_tbl_type IN OUT NOCOPY lvl_tbl_type
4765 , p_short_name IN VARCHAR
4766 )
4767 IS
4768 BEGIN
4769
4770 IF IS_NOT_NULL_MISSING_CHAR(p_short_name) THEN
4771 p_lvl_tbl_type(p_lvl_tbl_type.COUNT+1) := p_short_name;
4772 END IF;
4773
4774 EXCEPTION
4775 WHEN OTHERS THEN
4776 NULL;
4777 -- BIS_UTILITIES_PUB.put_line(p_text => 'The level ' || nvl(p_short_name, ' ') || ' is invalid. ' ) ; -- 2515991
4778 END ADD_TO_LEVEL_ARRAY;
4779
4780
4781
4782 FUNCTION CHECK_UNIQUE_LEVELS -- Function added for 2486702
4783 (p_lvl_tbl_type IN lvl_tbl_type)
4784 RETURN BOOLEAN IS
4785
4786 i NUMBER;
4787 j NUMBER;
4788 l_num_levels NUMBER;
4789 l_count NUMBER;
4790 l_dim_short_name bis_dimensions.short_name%TYPE;
4791
4792 BEGIN
4793
4794 l_num_levels := p_lvl_tbl_type.COUNT;
4795
4796 IF NOT(IS_NOT_NULL_MISSING_NUM(l_num_levels)) THEN
4797 RAISE FND_API.G_EXC_ERROR;
4798 END IF;
4799
4800 FOR i IN 1..l_num_levels LOOP
4801
4802 l_count := 0;
4803
4804 FOR j IN (i+1)..l_num_levels LOOP
4805 IF (p_lvl_tbl_type(i) = p_lvl_tbl_type(j)) THEN
4806 IF (IS_ORG_OR_TIME_LEVEL(p_lvl_tbl_type(i))) THEN
4807 l_count := l_count + 1;
4808 ELSE
4809 -- BIS_UTILITIES_PUB.put_line(p_text =>'The level ' || nvl(p_lvl_tbl_type(i), ' ') || ' is not unique. ' ); -- 2515991
4810 RAISE FND_API.G_EXC_ERROR;
4811 END IF;
4812 END IF;
4813 END LOOP;
4814
4815 IF (l_count > 1) THEN
4816
4817 -- BIS_UTILITIES_PUB.put_line(p_text =>'The dimension level corresponding to Organization or Time dimension appears -- 2515991
4818 -- more than twice in the definition of this Summary Level.' );
4819
4820 RAISE FND_API.G_EXC_ERROR;
4821 END IF;
4822
4823 END LOOP;
4824
4825 RETURN TRUE;
4826
4827 EXCEPTION
4828 WHEN OTHERS THEN
4829 -- BIS_UTILITIES_PUB.put_line(p_text => ' Error in CHECK_UNIQUE_LEVELS in BIS_TARGET_LEVEL_PVT -- ' || sqlerrm ) ; -- 2515991
4830 RETURN FALSE;
4831 END CHECK_UNIQUE_LEVELS;
4832
4833
4834
4835 FUNCTION IS_ORG_OR_TIME_LEVEL -- Function added for 2486702
4836 (p_lvl_short_name IN VARCHAR2)
4837 RETURN BOOLEAN IS
4838
4839 l_dim_short_name bis_dimensions.short_name%TYPE;
4840
4841 BEGIN
4842
4843 SELECT D.short_name
4844 INTO l_dim_short_name
4845 FROM BIS_LEVELS L,
4846 BIS_DIMENSIONS D
4847 WHERE
4848 L.short_name = p_lvl_short_name
4849 AND L.dimension_id = D.dimension_id;
4850
4851 IF (l_dim_short_name IN ('EDW_ORGANIZATION_M',
4852 'ORGANIZATION',
4853 'EDW_TIME_M',
4854 'TIME')) THEN
4855 RETURN TRUE;
4856 ELSE
4857 RETURN FALSE;
4858 END IF;
4859
4860 EXCEPTION
4861 WHEN OTHERS THEN
4862 -- BIS_UTILITIES_PUB.put_line(p_text => ' Error in IS_ORG_OR_TIME_LEVEL in BIS_TARGET_LEVEL_PVT -- ' || sqlerrm ) ; -- 2515991
4863 RETURN FALSE;
4864 END IS_ORG_OR_TIME_LEVEL;
4865
4866
4867
4868 FUNCTION IS_NOT_NULL_MISSING_CHAR -- Function added for 2486702
4869 (p_string IN VARCHAR2)
4870 RETURN BOOLEAN IS
4871 BEGIN
4872 IF (
4873 (BIS_UTILITIES_PVT.Value_Not_Missing(p_string) = FND_API.G_TRUE)
4874 AND
4875 (BIS_UTILITIES_PVT.Value_Not_Null(p_string) = FND_API.G_TRUE)
4876 ) THEN
4877 RETURN TRUE;
4878 ELSE
4879 RETURN FALSE;
4880 END IF;
4881 EXCEPTION
4882 WHEN OTHERS THEN
4883 RETURN FALSE;
4884 END IS_NOT_NULL_MISSING_CHAR;
4885
4886
4887 FUNCTION IS_NOT_NULL_MISSING_NUM -- Function added for 2486702
4888 (p_number IN NUMBER)
4889 RETURN BOOLEAN IS
4890 BEGIN
4891 IF (
4892 (BIS_UTILITIES_PVT.Value_Not_Missing(p_number) = FND_API.G_TRUE)
4893 AND
4894 (BIS_UTILITIES_PVT.Value_Not_Null(p_number) = FND_API.G_TRUE)
4895 ) THEN
4896 RETURN TRUE;
4897 ELSE
4898 RETURN FALSE;
4899 END IF;
4900 EXCEPTION
4901 WHEN OTHERS THEN
4902 RETURN FALSE;
4903 END IS_NOT_NULL_MISSING_NUM;
4904
4905
4906 END BIS_Target_Level_PVT;