[Home] [Help]
PACKAGE BODY: APPS.BIS_DIM_LEVEL_VALUE_PVT
Source
1 PACKAGE BODY BIS_DIM_LEVEL_VALUE_PVT AS
2 /* $Header: BISVDMVB.pls 120.2 2005/11/02 17:43:27 jxyu noship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BISVDMVB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Private API for managing dimension level valuesfor the
13 REM | Key Performance Framework.
14 REM |
15 REM | This package should be maintaind by EDW once it gets integrated
16 REM | with BIS.
17 REM |
18 REM | NOTES |
19 REM | 01.23.02 sashaik Modified Is_Current_Time_Period and Is_Previous_Time_Period |
20 REM | for bug 1740789 |
21 REM | 22-OCT-02 mahrao Fix for 2631537 |
22 REM | 13-NOV-2002 mahrao Fix for 2665526 |
23 REM | 02-DEC-02 rchandra added check for org dependency in time
24 REM | dim levels for 2684911
25 REM | 10-DEC-02 rchandra changed the DimensionX_ID_to_Value to retrieve
26 REM | only one row if the dim lvl is org dep
27 REM | 26-JUN-03 RCHANDRA do away with hard coded length for name and |
28 REM | description for bug 2910316 |
29 REM | for dimension and dimension levels |
30 REM | 04-JUL-03 RCHANDRA changed the hard coded length for description |
31 REM | for dimension level for bug 3033028 |
32 REM | 24-NOV-03 GRAMASAM added a check for time dimension level for bug |
33 REM | 3255072 |
34 REM | 09-JAN-2004 rpenneru bug#3352065 modified Check for TIME DIMENSION |
35 REM | level method to BIS_UTILITIES_PVT.is_valid_time_dimension_level|
36 REM | 09-JAN-2004 ankgoel bug#3001359 Modified to limit the dim level value|
37 REM | length to 240 characters |
38 REM | 19-OCT-2005 ppandey Enh 4618419- SQL Literal Fix |
39 REM | 02-NOV-2005 jxyu Fix bug#4711882 |
40 REM +=======================================================================+
41 */
42
43 C_GL_COMPANY CONSTANT VARCHAR2(40) := 'GL COMPANY';
44 C_GL_SECONDARY_MEASURE CONSTANT VARCHAR2(40) := 'GL SECONDARY MEASURE';
45
46 --
47 --
48 Procedure Retrieve_Dim_Level_Values
49 ( p_api_version IN NUMBER
50 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
51 , x_Dim_Level_Value_Tbl OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
52 , x_return_status OUT NOCOPY VARCHAR2
53 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
54 )
55 IS
56 BEGIN
57 NULL;
58 END Retrieve_Dim_Level_Values;
59 --
60 --
61 PROCEDURE Get_Org_Dim_Values
62 ( p_api_version IN NUMBER
63 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
64 , p_Responsibility_Tbl IN BIS_RESPONSIBILITY_PVT.Responsibility_Tbl_Type
65 , x_Dim_Level_Value_Tbl OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
66 , x_return_status OUT NOCOPY VARCHAR2
67 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
68 )
69 IS
70 --
71 l_view_name VARCHAR2(80);
72 l_short_name VARCHAR2(30);
73 l_name bis_levels_tl.name%TYPE;
74 l_cursor INTEGER;
75 l_ind NUMBER := 0;
76 l_id VARCHAR2(250);
77 l_value VARCHAR2(250);
78 l_select_stmt VARCHAR2(32000);
79 l_sql_result INTEGER := 0;
80 l_resp_clause VARCHAR2(10000);
81 --
82 l_id1 DBMS_SQL.VARCHAR2_TABLE;
83 l_value1 DBMS_SQL.VARCHAR2_TABLE;
84 l_size NUMBER := 100000;
85 l_retrieved NUMBER;
86 --
87 BEGIN
88 x_return_status := FND_API.G_RET_STS_SUCCESS;
89 --
90 SELECT
91 DIMENSION_LEVEL_SHORT_NAME
92 , DIMENSION_LEVEL_NAME
93 , LEVEL_VALUES_VIEW_NAME
94 INTO
95 l_short_name
96 , l_name
97 , l_view_name
98 FROM BISBV_DIMENSION_LEVELS
99 WHERE DIMENSION_LEVEL_ID = p_Dimension_Level_Rec.Dimension_Level_ID;
100 --
101 IF(l_short_name <> 'TOTAL_ORGANIZATIONS') THEN
102 -- create the resps clause
103 IF(p_Responsibility_Tbl.COUNT <> 0) THEN
104 l_resp_clause := ' RESPONSIBILITY_ID IN ( ';
105 FOR l_ind IN 1..p_Responsibility_Tbl.COUNT LOOP
106 IF(l_ind = 1) THEN
107 l_resp_clause
108 := l_resp_clause
109 || ':b_resp' || l_ind;
110 ELSE
111 l_resp_clause
112 := l_resp_clause
113 || ', '
114 || ':b_resp' || l_ind;
115 END IF;
116 END LOOP;
117 l_resp_clause := l_resp_clause || ' ) ';
118 l_resp_clause := l_resp_clause || ' OR RESPONSIBILITY_ID IS NULL ';
119 END IF;
120
121 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
122 DBMS_SQL.CLOSE_CURSOR(l_cursor);
123 END IF;
124 l_cursor := DBMS_SQL.OPEN_CURSOR;
125 --
126 IF(p_Responsibility_Tbl.COUNT <> 0) THEN
127 l_select_stmt := 'SELECT DISTINCT'
128 || ' ID '
129 || ', VALUE '
130 || ' FROM '
131 || l_view_name
132 || ' WHERE '
133 || l_resp_clause
134 || ' ORDER BY VALUE';
135 ELSE
136 l_select_stmt := 'SELECT DISTINCT'
137 || ' ID '
138 || ', VALUE '
139 || ' FROM '
140 || l_view_name
141 || ' ORDER BY VALUE';
142 END IF;
143 DBMS_SQL.PARSE( c => l_cursor
144 , statement => l_select_stmt
145 , language_flag => DBMS_SQL.NATIVE
146 );
147 FOR l_ind IN 1..p_Responsibility_Tbl.COUNT LOOP
148 DBMS_SQL.BIND_VARIABLE
149 ( l_cursor
150 , ':b_resp' || TO_CHAR(l_ind)
151 , p_Responsibility_Tbl(l_ind).Responsibility_ID
152 );
153 END LOOP;
154 DBMS_SQL.DEFINE_ARRAY(l_cursor, 1, l_id1, l_size, 1);
155 DBMS_SQL.DEFINE_ARRAY(l_cursor, 2, l_value1, l_size, 1);
156 l_sql_result := DBMS_SQL.EXECUTE(l_cursor);
157 --
158 LOOP
159 l_retrieved := DBMS_SQL.FETCH_ROWS(l_cursor);
160 EXIT WHEN l_retrieved = 0;
161 --
162 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_id1);
163 DBMS_SQL.COLUMN_VALUE(l_cursor, 2, l_value1);
164 --
165 FOR l_ind1 IN 1..l_id1.COUNT LOOP
166 l_ind := l_ind + 1;
167 x_Dim_Level_Value_Tbl(l_ind).Dimension_Level_Value_ID
168 := l_id1(l_ind1);
169 x_Dim_Level_Value_Tbl(l_ind).Dimension_Level_Value_Name
170 := l_value1(l_ind1);
171 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_ID
172 := p_Dimension_Level_Rec.Dimension_Level_ID;
173 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_Short_Name
174 := l_short_name;
175 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_Name
176 := l_name;
177 END LOOP;
178 --
179 EXIT WHEN l_retrieved < l_size;
180 END LOOP;
181 --
182 DBMS_SQL.CLOSE_CURSOR(l_cursor);
183 ELSE
184 l_ind := l_ind + 1;
185 x_Dim_Level_Value_Tbl(l_ind).Dimension_Level_Value_ID := '-1';
186 x_Dim_Level_Value_Tbl(l_ind).Dimension_Level_Value_Name := l_name;
187 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_ID
188 := p_Dimension_Level_Rec.Dimension_Level_ID;
189 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_Short_Name := l_short_name;
190 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_Name := l_name;
191 END IF;
192 --
193 EXCEPTION
194 WHEN NO_DATA_FOUND THEN
195 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
196 DBMS_SQL.CLOSE_CURSOR(l_cursor);
197 END IF;
198 x_return_status := FND_API.G_RET_STS_ERROR;
199 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
200 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_Org_Dim_Values:NO_DATA_FOUND'); htp.para;
201 END IF;
202 RAISE FND_API.G_EXC_ERROR;
203 WHEN FND_API.G_EXC_ERROR THEN
204 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
205 DBMS_SQL.CLOSE_CURSOR(l_cursor);
206 END IF;
207 x_return_status := FND_API.G_RET_STS_ERROR;
208 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
209 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_Org_Dim_Values:G_EXC_ERROR'); htp.para;
210 END IF;
211 RAISE;
212 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
213 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
214 DBMS_SQL.CLOSE_CURSOR(l_cursor);
215 END IF;
216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
217 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
218 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_Org_Dim_Values:G_EXC_UNEXPECTED_ERROR'); htp.para;
219 END IF;
220 RAISE;
221 WHEN OTHERS THEN
222 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
223 DBMS_SQL.CLOSE_CURSOR(l_cursor);
224 END IF;
225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
226 BIS_UTILITIES_PVT.Add_Error_Message
227 ( p_error_table => x_error_Tbl
228 , p_error_msg_id => SQLCODE
229 , p_error_description => SQLERRM
230 , x_error_table => x_error_Tbl
231 );
232 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
233 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_Org_Dim_Values:OTHERS'); htp.para;
234 END IF;
235 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
236 --
237 END Get_Org_Dim_Values;
238 --
239 --
240 PROCEDURE Get_Org_Dim_Values
241 ( p_api_version IN NUMBER
242 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
243 , p_Responsibility_ID IN NUMBER
244 , x_Dim_Level_Value_Tbl OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
245 , x_return_status OUT NOCOPY VARCHAR2
246 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
247 )
248 IS
249 --
250 l_view_name VARCHAR2(80);
251 l_short_name VARCHAR2(30);
252 l_name bis_levels_tl.name%TYPE;
253 l_cursor INTEGER;
254 l_ind NUMBER := 0;
255 l_id VARCHAR2(250);
256 l_value VARCHAR2(250);
257 l_select_stmt VARCHAR2(2000);
258 l_sql_result INTEGER := 0;
259 --
260 l_id1 DBMS_SQL.VARCHAR2_TABLE;
261 l_value1 DBMS_SQL.VARCHAR2_TABLE;
262 l_size NUMBER := 100000;
263 l_retrieved NUMBER;
264 --
265 BEGIN
266 x_return_status := FND_API.G_RET_STS_SUCCESS;
267 --
268 SELECT
269 DIMENSION_LEVEL_SHORT_NAME
270 , DIMENSION_LEVEL_NAME
271 , LEVEL_VALUES_VIEW_NAME
272 INTO
273 l_short_name
274 , l_name
275 , l_view_name
276 FROM BISBV_DIMENSION_LEVELS
277 WHERE DIMENSION_LEVEL_ID = p_Dimension_Level_Rec.Dimension_Level_ID;
278 --
279 IF(l_short_name <> 'TOTAL_ORGANIZATIONS') THEN
280 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
281 DBMS_SQL.CLOSE_CURSOR(l_cursor);
282 END IF;
283 l_cursor := DBMS_SQL.OPEN_CURSOR;
284 --
285 IF(p_Responsibility_ID IS NOT NULL) THEN
286 l_select_stmt := 'SELECT '
287 || ' ID '
288 || ', VALUE '
289 || ' FROM '
290 || l_view_name
291 || ' WHERE '
292 || ' RESPONSIBILITY_ID = :p_Responsibility_ID '
293 || ' ORDER BY VALUE';
294 ELSE
295 l_select_stmt := 'SELECT DISTINCT'
296 || ' ID '
297 || ', VALUE '
298 || ' FROM '
299 || l_view_name
300 || ' ORDER BY VALUE';
301 END IF;
302
303 DBMS_SQL.PARSE( c => l_cursor
304 , statement => l_select_stmt
305 , language_flag => DBMS_SQL.NATIVE
306 );
307 DBMS_SQL.DEFINE_ARRAY(l_cursor, 1, l_id1, l_size, 1);
308 DBMS_SQL.DEFINE_ARRAY(l_cursor, 2, l_value1, l_size, 1);
309 IF(p_Responsibility_ID IS NOT NULL) THEN
310 DBMS_SQL.BIND_VARIABLE(l_cursor, ':p_Responsibility_ID', p_Responsibility_ID);
311 END IF;
312
313 l_sql_result := DBMS_SQL.EXECUTE(l_cursor);
314 --
315 LOOP
316 l_retrieved := DBMS_SQL.FETCH_ROWS(l_cursor);
317 EXIT WHEN l_retrieved = 0;
318 --
319 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_id1);
320 DBMS_SQL.COLUMN_VALUE(l_cursor, 2, l_value1);
321 --
322 FOR l_ind1 IN 1..l_id1.COUNT LOOP
323 l_ind := l_ind + 1;
324 x_Dim_Level_Value_Tbl(l_ind).Dimension_Level_Value_ID
325 := l_id1(l_ind1);
326 x_Dim_Level_Value_Tbl(l_ind).Dimension_Level_Value_Name
327 := l_value1(l_ind1);
328 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_ID
329 := p_Dimension_Level_Rec.Dimension_Level_ID;
330 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_Short_Name
331 := l_short_name;
332 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_Name
333 := l_name;
334 END LOOP;
335 --
336 EXIT WHEN l_retrieved < l_size;
337 END LOOP;
338 --
339 DBMS_SQL.CLOSE_CURSOR(l_cursor);
340 ELSE
341 l_ind := l_ind + 1;
342 x_Dim_Level_Value_Tbl(l_ind).Dimension_Level_Value_ID := '-1';
343 x_Dim_Level_Value_Tbl(l_ind).Dimension_Level_Value_Name := l_name;
344 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_ID
345 := p_Dimension_Level_Rec.Dimension_Level_ID;
346 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_Short_Name := l_short_name;
347 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_Name := l_name;
348 END IF;
349 --
350 EXCEPTION
351 WHEN NO_DATA_FOUND THEN
352 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
353 DBMS_SQL.CLOSE_CURSOR(l_cursor);
354 END IF;
355 x_return_status := FND_API.G_RET_STS_ERROR;
356 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
357 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_Org_Dim_Values:NO_DATA_FOUND'); htp.para;
358 END IF;
359 RAISE FND_API.G_EXC_ERROR;
360 WHEN FND_API.G_EXC_ERROR THEN
361 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
362 DBMS_SQL.CLOSE_CURSOR(l_cursor);
363 END IF;
364 x_return_status := FND_API.G_RET_STS_ERROR;
365 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
366 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_Org_Dim_Values:G_EXC_ERROR'); htp.para;
367 END IF;
368 RAISE;
369 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
370 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
371 DBMS_SQL.CLOSE_CURSOR(l_cursor);
372 END IF;
373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
374 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
375 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_Org_Dim_Values:G_EXC_UNEXPECTED_ERROR'); htp.para;
376 END IF;
377 RAISE;
378 WHEN OTHERS THEN
379 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
380 DBMS_SQL.CLOSE_CURSOR(l_cursor);
381 END IF;
382 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
383 BIS_UTILITIES_PVT.Add_Error_Message
384 ( p_error_table => x_error_Tbl
385 , p_error_msg_id => SQLCODE
386 , p_error_description => SQLERRM
387 , x_error_table => x_error_Tbl
388 );
389 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
390 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_Org_Dim_Values:OTHERS'); htp.para;
391 END IF;
392 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
393 --
394 END Get_Org_Dim_Values;
395 --
396 --
397 PROCEDURE Get_Org_Dim_Values
398 ( p_api_version IN NUMBER
399 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
400 , x_Dim_Level_Value_Tbl OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
401 , x_return_status OUT NOCOPY VARCHAR2
402 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
403 )
404 IS
405 --
406 l_view_name VARCHAR2(80);
407 l_short_name VARCHAR2(30);
408 l_name bis_levels_tl.name%TYPE;
409 l_cursor INTEGER;
410 l_ind NUMBER := 0;
411 l_id VARCHAR2(250);
412 l_value VARCHAR2(250);
413 l_select_stmt VARCHAR2(2000);
414 l_sql_result INTEGER := 0;
415 --
416 l_id1 DBMS_SQL.VARCHAR2_TABLE;
417 l_value1 DBMS_SQL.VARCHAR2_TABLE;
418 l_size NUMBER := 100000;
419 l_retrieved NUMBER;
420 --
421 BEGIN
422 x_return_status := FND_API.G_RET_STS_SUCCESS;
423 --
424 SELECT
425 DIMENSION_LEVEL_SHORT_NAME
426 , DIMENSION_LEVEL_NAME
427 , LEVEL_VALUES_VIEW_NAME
428 INTO
429 l_short_name
430 , l_name
431 , l_view_name
432 FROM BISBV_DIMENSION_LEVELS
433 WHERE DIMENSION_LEVEL_ID = p_Dimension_Level_Rec.Dimension_Level_ID;
434 --
435 IF(l_short_name <> 'TOTAL_ORGANIZATIONS') THEN
436 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
437 DBMS_SQL.CLOSE_CURSOR(l_cursor);
438 END IF;
439 l_cursor := DBMS_SQL.OPEN_CURSOR;
440 --
441 l_select_stmt := 'SELECT DISTINCT'
442 || ' ID '
443 || ', VALUE '
444 || ' FROM '
445 || l_view_name
446 || ' ORDER BY VALUE';
447 --
448 DBMS_SQL.PARSE( c => l_cursor
449 , statement => l_select_stmt
450 , language_flag => DBMS_SQL.NATIVE
451 );
452 DBMS_SQL.DEFINE_ARRAY(l_cursor, 1, l_id1, l_size, 1);
453 DBMS_SQL.DEFINE_ARRAY(l_cursor, 2, l_value1, l_size, 1);
454 l_sql_result := DBMS_SQL.EXECUTE(l_cursor);
455 --
456 LOOP
457 l_retrieved := DBMS_SQL.FETCH_ROWS(l_cursor);
458 EXIT WHEN l_retrieved = 0;
459 --
460 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_id1);
461 DBMS_SQL.COLUMN_VALUE(l_cursor, 2, l_value1);
462 --
463 FOR l_ind1 IN 1..l_id1.COUNT LOOP
464 l_ind := l_ind + 1;
465 x_Dim_Level_Value_Tbl(l_ind).Dimension_Level_Value_ID
466 := l_id1(l_ind1);
467 x_Dim_Level_Value_Tbl(l_ind).Dimension_Level_Value_Name
468 := l_value1(l_ind1);
469 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_ID
470 := p_Dimension_Level_Rec.Dimension_Level_ID;
471 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_Short_Name
472 := l_short_name;
473 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_Name
474 := l_name;
475 END LOOP;
476 --
477 EXIT WHEN l_retrieved < l_size;
478 END LOOP;
479 --
480 DBMS_SQL.CLOSE_CURSOR(l_cursor);
481 ELSE
482 l_ind := l_ind + 1;
483 x_Dim_Level_Value_Tbl(l_ind).Dimension_Level_Value_ID := '-1';
484 x_Dim_Level_Value_Tbl(l_ind).Dimension_Level_Value_Name := l_name;
485 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_ID
486 := p_Dimension_Level_Rec.Dimension_Level_ID;
487 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_Short_Name := l_short_name;
488 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_Name := l_name;
489 END IF;
490 --
491 EXCEPTION
492 WHEN NO_DATA_FOUND THEN
493 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
494 DBMS_SQL.CLOSE_CURSOR(l_cursor);
495 END IF;
496 x_return_status := FND_API.G_RET_STS_ERROR;
497 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
498 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_Org_Dim_Values:NO_DATA_FOUND'); htp.para;
499 END IF;
500 RAISE FND_API.G_EXC_ERROR;
501 WHEN FND_API.G_EXC_ERROR THEN
502 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
503 DBMS_SQL.CLOSE_CURSOR(l_cursor);
504 END IF;
505 x_return_status := FND_API.G_RET_STS_ERROR;
506 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
507 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_Org_Dim_Values:G_EXC_ERROR'); htp.para;
508 END IF;
509 RAISE;
510 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
511 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
512 DBMS_SQL.CLOSE_CURSOR(l_cursor);
513 END IF;
514 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
515 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
516 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_Org_Dim_Values:G_EXC_UNEXPECTED_ERROR'); htp.para;
517 END IF;
518 RAISE;
519 WHEN OTHERS THEN
520 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
521 DBMS_SQL.CLOSE_CURSOR(l_cursor);
522 END IF;
523 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
524 BIS_UTILITIES_PVT.Add_Error_Message
525 ( p_error_table => x_error_Tbl
526 , p_error_msg_id => SQLCODE
527 , p_error_description => SQLERRM
528 , x_error_table => x_error_Tbl
529 );
530 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
531 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_Org_Dim_Values:OTHERS'); htp.para;
532 END IF;
533 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
534 END Get_Org_Dim_Values;
535 --
536 --
537 PROCEDURE Get_DimensionX_Values
538 ( p_api_version IN NUMBER
539 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
540 , x_Dim_Level_Value_Tbl OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
541 , x_return_status OUT NOCOPY VARCHAR2
542 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
543 )
544 IS
545 --
546 l_view_name VARCHAR2(80);
547 l_short_name VARCHAR2(30);
548 l_name bis_levels_tl.name%TYPE;
549 l_cursor INTEGER;
550 l_ind NUMBER := 0;
551 l_id VARCHAR2(250);
552 l_value VARCHAR2(250);
553 l_select_stmt VARCHAR2(2000);
554 l_sql_result INTEGER := 0;
555 --
556 l_id1 DBMS_SQL.VARCHAR2_TABLE;
557 l_value1 DBMS_SQL.VARCHAR2_TABLE;
558 l_size NUMBER := 100000;
559 l_retrieved NUMBER;
560 --
561 BEGIN
562 x_return_status := FND_API.G_RET_STS_SUCCESS;
563 --
564 SELECT
565 DIMENSION_LEVEL_SHORT_NAME
566 , DIMENSION_LEVEL_NAME
567 , LEVEL_VALUES_VIEW_NAME
568 INTO
569 l_short_name
570 , l_name
571 , l_view_name
572 FROM BISBV_DIMENSION_LEVELS
573 WHERE DIMENSION_LEVEL_ID = p_Dimension_Level_Rec.Dimension_Level_ID;
574 --
575 IF(SUBSTR(l_short_name, 1, 5) <> 'TOTAL') THEN
576 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
577 DBMS_SQL.CLOSE_CURSOR(l_cursor);
578 END IF;
579 l_cursor := DBMS_SQL.OPEN_CURSOR;
580 --
581 l_select_stmt := 'SELECT DISTINCT '
582 || ' ID '
583 || ', VALUE '
584 || ' FROM '
585 || l_view_name
586 || ' ORDER BY VALUE';
587 --
588 -- htp.header(4, 'l_select_stmt = ' || l_select_stmt);
589 DBMS_SQL.PARSE( c => l_cursor
590 , statement => l_select_stmt
591 , language_flag => DBMS_SQL.NATIVE
592 );
593 DBMS_SQL.DEFINE_ARRAY(l_cursor, 1, l_id1, l_size, 1);
594 DBMS_SQL.DEFINE_ARRAY(l_cursor, 2, l_value1, l_size, 1);
595 l_sql_result := DBMS_SQL.EXECUTE(l_cursor);
596 --
597 LOOP
598 l_retrieved := DBMS_SQL.FETCH_ROWS(l_cursor);
599 EXIT WHEN l_retrieved = 0;
600 --
601 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_id1);
602 DBMS_SQL.COLUMN_VALUE(l_cursor, 2, l_value1);
603 --
604 FOR l_ind1 IN 1..l_id1.COUNT LOOP
605 l_ind := l_ind + 1;
606 x_Dim_Level_Value_Tbl(l_ind).Dimension_Level_Value_ID
607 := l_id1(l_ind1);
608 x_Dim_Level_Value_Tbl(l_ind).Dimension_Level_Value_Name
609 := l_value1(l_ind1);
610 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_ID
611 := p_Dimension_Level_Rec.Dimension_Level_ID;
612 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_Short_Name
613 := l_short_name;
614 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_Name
615 := l_name;
616 END LOOP;
617 --
618 EXIT WHEN l_retrieved < l_size;
619 END LOOP;
620 --
621 DBMS_SQL.CLOSE_CURSOR(l_cursor);
622 ELSE
623 l_ind := l_ind + 1;
624 x_Dim_Level_Value_Tbl(l_ind).Dimension_Level_Value_ID := '-1';
625 x_Dim_Level_Value_Tbl(l_ind).Dimension_Level_Value_Name := l_name;
626 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_ID
627 := p_Dimension_Level_Rec.Dimension_Level_ID;
628 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_Short_Name := l_short_name;
629 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_Name := l_name;
630 END IF;
631
632 --
633 EXCEPTION
634 WHEN NO_DATA_FOUND THEN
635 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
636 DBMS_SQL.CLOSE_CURSOR(l_cursor);
637 END IF;
638 x_return_status := FND_API.G_RET_STS_ERROR;
639 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
640 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_DimensionX_Values:NO_DATA_FOUND'); htp.para;
641 END IF;
642 RAISE FND_API.G_EXC_ERROR;
643 WHEN FND_API.G_EXC_ERROR THEN
644 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
645 DBMS_SQL.CLOSE_CURSOR(l_cursor);
646 END IF;
647 x_return_status := FND_API.G_RET_STS_ERROR;
648 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
649 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_DimensionX_Values:G_EXC_ERROR'); htp.para;
650 END IF;
651 RAISE;
652 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
653 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
654 DBMS_SQL.CLOSE_CURSOR(l_cursor);
655 END IF;
656 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
657 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
658 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_DimensionX_Values:G_EXC_UNEXPECTED_ERROR'); htp.para;
659 END IF;
660 RAISE;
661 WHEN OTHERS THEN
662 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
663 DBMS_SQL.CLOSE_CURSOR(l_cursor);
664 END IF;
665 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
666 BIS_UTILITIES_PVT.Add_Error_Message
667 ( p_error_table => x_error_Tbl
668 , p_error_msg_id => SQLCODE
669 , p_error_description => SQLERRM
670 , x_error_table => x_error_Tbl
671 );
672 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
673 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_DimensionX_Values:OTHERS'); htp.para;
674 END IF;
675 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
676 --
677 END Get_DimensionX_Values;
678 --
679 --
680 PROCEDURE Get_Time_Dim_Values
681 ( p_api_version IN NUMBER
682 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
683 , p_Dim_Level_Value_Rec IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
684 , x_Dim_Level_Value_Tbl OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
685 , x_return_status OUT NOCOPY VARCHAR2
686 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
687 )
688 IS
689 --
690 l_view_name VARCHAR2(80);
691 l_short_name VARCHAR2(30);
692 l_name bis_levels_tl.name%TYPE;
693 l_cursor INTEGER := NULL;
694 l_ind NUMBER := 0;
695 l_period_set VARCHAR2(250);
696 l_id VARCHAR2(250) := NULL;
697 l_value VARCHAR2(250) := NULL;
698 l_start_date DATE;
699 l_select_stmt VARCHAR2(2000) := NULL;
700 l_sql_result INTEGER := 0;
701 --
702 l_id1 DBMS_SQL.VARCHAR2_TABLE;
703 l_value1 DBMS_SQL.VARCHAR2_TABLE;
704 l_start_date1 DBMS_SQL.DATE_TABLE;
705 l_size NUMBER := 100000;
706 l_retrieved NUMBER;
707 l_Org_Dept BOOLEAN;
708 --
709 BEGIN
710 x_return_status := FND_API.G_RET_STS_SUCCESS;
711 --
712 SELECT
713 DIMENSION_LEVEL_SHORT_NAME
714 , DIMENSION_LEVEL_NAME
715 , LEVEL_VALUES_VIEW_NAME
716 INTO
717 l_short_name
718 , l_name
719 , l_view_name
720 FROM BISBV_DIMENSION_LEVELS
721 WHERE DIMENSION_LEVEL_ID = p_Dimension_Level_Rec.Dimension_Level_ID;
722 --
723 IF(l_short_name <> 'TOTAL_TIME') THEN
724 IF (BIS_UTILITIES_PUB.is_time_dependent_on_org(p_time_lvl_short_name => l_short_name)
725 = BIS_UTILITIES_PUB.G_TIME_IS_DEPEN_ON_ORG) THEN --2684911
726 l_Org_Dept := TRUE;
727 l_select_stmt := 'SELECT '
728 || ' ID '
729 || ', VALUE '
730 || ', START_DATE '
731 || ' FROM '
732 || l_view_name
733 || ' WHERE '
734 || ' ORGANIZATION_ID = :org_id'
735 || ' AND NVL(ORGANIZATION_TYPE, ''%'') LIKE :org_type '
736 || ' ORDER BY START_DATE';
737 ELSE
738 l_Org_Dept := FALSE;
739 l_select_stmt := 'SELECT '
740 || ' ID '
741 || ', VALUE '
742 || ', START_DATE '
743 || ' FROM '
744 || l_view_name
745 || ' ORDER BY START_DATE';
746 END IF;
747 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
748 DBMS_SQL.CLOSE_CURSOR(l_cursor);
749 END IF;
750 l_cursor := DBMS_SQL.OPEN_CURSOR;
751 DBMS_SQL.PARSE( c => l_cursor
752 , statement => l_select_stmt
753 , language_flag => DBMS_SQL.NATIVE
754 );
755 DBMS_SQL.DEFINE_ARRAY(l_cursor, 1, l_id1, l_size, 1);
756 DBMS_SQL.DEFINE_ARRAY(l_cursor, 2, l_value1, l_size, 1);
757 DBMS_SQL.DEFINE_ARRAY(l_cursor, 3, l_start_date1, l_size, 1);
758 IF(l_Org_Dept) THEN
759 DBMS_SQL.BIND_VARIABLE(l_cursor, ':org_id', p_Dim_Level_Value_Rec.Dimension_Level_Value_ID);
760 DBMS_SQL.BIND_VARIABLE(l_cursor, ':org_type', p_Dim_Level_Value_Rec.Dimension_Level_Short_Name);
761 END IF;
762 l_sql_result := DBMS_SQL.EXECUTE(l_cursor);
763 --
764 LOOP
765 l_retrieved := DBMS_SQL.FETCH_ROWS(l_cursor);
766 EXIT WHEN l_retrieved = 0;
767 --
768 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_id1);
769 DBMS_SQL.COLUMN_VALUE(l_cursor, 2, l_value1);
770 DBMS_SQL.COLUMN_VALUE(l_cursor, 3, l_start_date1);
771 --
772 FOR l_ind1 IN 1..l_id1.COUNT LOOP
773 l_ind := l_ind + 1;
774 x_Dim_Level_Value_Tbl(l_ind).Dimension_Level_Value_ID
775 := l_id1(l_ind1);
776 x_Dim_Level_Value_Tbl(l_ind).Dimension_Level_Value_Name
777 := l_value1(l_ind1);
778 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_ID
779 := p_Dimension_Level_Rec.Dimension_Level_ID;
780 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_Short_Name
781 := l_short_name;
782 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_Name
783 := l_name;
784 END LOOP;
785 --
786 EXIT WHEN l_retrieved < l_size;
787 END LOOP;
788 --
789 DBMS_SQL.CLOSE_CURSOR(l_cursor);
790 ELSE
791 l_ind := l_ind + 1;
792 x_Dim_Level_Value_Tbl(l_ind).Dimension_Level_Value_ID := '-1';
793 x_Dim_Level_Value_Tbl(l_ind).Dimension_Level_Value_Name := l_name;
794 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_ID
795 := p_Dimension_Level_Rec.Dimension_Level_ID;
796 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_Short_Name := l_short_name;
797 x_Dim_Level_Value_Tbl(l_ind).Dimension_level_Name := l_name;
798 END IF;
799 --
800 EXCEPTION
801 WHEN NO_DATA_FOUND THEN
802 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
803 DBMS_SQL.CLOSE_CURSOR(l_cursor);
804 END IF;
805 x_return_status := FND_API.G_RET_STS_ERROR;
806 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
807 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_Time_Dim_Values:NO_DATA_FOUND'); htp.para;
808 END IF;
809 RAISE FND_API.G_EXC_ERROR;
810 WHEN FND_API.G_EXC_ERROR THEN
811 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
812 DBMS_SQL.CLOSE_CURSOR(l_cursor);
813 END IF;
814 x_return_status := FND_API.G_RET_STS_ERROR;
815 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
816 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_Time_Dim_Values:G_EXC_ERROR'); htp.para;
817 END IF;
818 RAISE;
819 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
820 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
821 DBMS_SQL.CLOSE_CURSOR(l_cursor);
822 END IF;
823 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
824 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
825 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_Time_Dim_Values:G_EXC_UNEXPECTED_ERROR'); htp.para;
826 END IF;
827 RAISE;
828 WHEN OTHERS THEN
829 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
830 DBMS_SQL.CLOSE_CURSOR(l_cursor);
831 END IF;
832 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
833 BIS_UTILITIES_PVT.Add_Error_Message
834 ( p_error_table => x_error_Tbl
835 , p_error_msg_id => SQLCODE
836 , p_error_description => SQLERRM
837 , x_error_table => x_error_Tbl
838 );
839 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
840 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_Time_Dim_Values:OTHERS'); htp.para;
841 END IF;
842 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
843 --
844 END Get_Time_Dim_Values;
845 --
846 --
847 PROCEDURE Remove_Dup_Dim_Level_Values
848 ( p_api_version IN NUMBER
849 , p_Dim_Level_Value_Tbl IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
850 , x_Dim_Level_Value_Tbl OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Tbl_Type
851 , x_return_status OUT NOCOPY VARCHAR2
852 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
853 )
854 IS
855 --
856 l_unique BOOLEAN;
857 --
858 BEGIN
859 x_return_status := FND_API.G_RET_STS_SUCCESS;
860 --
861 FOR l_ind IN 1..p_Dim_Level_Value_Tbl.COUNT LOOP
862 l_unique := TRUE;
863 --
864 FOR l_ind1 IN 1..x_Dim_Level_Value_Tbl.COUNT LOOP
865 IF( p_Dim_Level_Value_Tbl(l_ind).Dimension_Level_Value_ID
866 = x_Dim_Level_Value_Tbl(l_ind1).Dimension_Level_Value_ID
867 ) THEN
868 l_unique := FALSE;
869 EXIT;
870 END IF;
871 END LOOP;
872 --
873 IF(l_unique) THEN
874 x_Dim_Level_Value_Tbl(x_Dim_Level_Value_Tbl.COUNT + 1)
875 := p_Dim_Level_Value_Tbl(l_ind);
876 END IF;
877 END LOOP;
878 --
879 EXCEPTION
880 WHEN FND_API.G_EXC_ERROR THEN
881 x_return_status := FND_API.G_RET_STS_ERROR;
882 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
883 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Remove_Dup_Dim_Level_Values:G_EXC_ERROR'); htp.para;
884 END IF;
885 RAISE;
886 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
887 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
888 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
889 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Remove_Dup_Dim_Level_Values:G_EXC_UNEXPECTED_ERROR'); htp.para;
890 END IF;
891 RAISE;
892 WHEN OTHERS THEN
893 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
894 BIS_UTILITIES_PVT.Add_Error_Message
895 ( p_error_table => x_error_Tbl
896 , p_error_msg_id => SQLCODE
897 , p_error_description => SQLERRM
898 , x_error_table => x_error_Tbl
899 );
900 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
901 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Remove_Dup_Dim_Level_Values:OTHERS'); htp.para;
902 END IF;
903 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
904 END Remove_Dup_Dim_Level_Values;
905 --
906 --
907 PROCEDURE Get_Start_Date
908 ( p_api_version IN NUMBER
909 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
910 , p_start_period IN VARCHAR2
911 , x_start_date OUT NOCOPY DATE
912 , x_return_status OUT NOCOPY VARCHAR2
913 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
914 )
915 IS
916 --
917 l_short_name VARCHAR2(30);
918 l_view_name VARCHAR2(30);
919 l_period_name VARCHAR2(15);
920 l_period_set_name VARCHAR2(15);
921 l_cursor INTEGER;
922 l_select_stmt VARCHAR2(2000);
923 l_sql_result INTEGER := 0;
924 l_start_date DATE;
925 --
926 BEGIN
927 x_return_status := FND_API.G_RET_STS_SUCCESS;
928 --
929 SELECT
930 DIMENSION_LEVEL_SHORT_NAME
931 , LEVEL_VALUES_VIEW_NAME
932 INTO
933 l_short_name
934 , l_view_name
935 FROM BISBV_DIMENSION_LEVELS
936 WHERE DIMENSION_LEVEL_ID = p_Dimension_Level_Rec.Dimension_Level_ID;
937 --
938 --
939 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
940 DBMS_SQL.CLOSE_CURSOR(l_cursor);
941 END IF;
942 l_cursor := DBMS_SQL.OPEN_CURSOR;
943 --
944 l_select_stmt := 'SELECT DISTINCT '
945 || ' START_DATE '
946 || ' FROM '
947 || l_view_name
948 || ' WHERE '
949 || ' ID = :p_start_period ';
950 --
951 DBMS_SQL.PARSE( c => l_cursor
952 , statement => l_select_stmt
953 , language_flag => DBMS_SQL.NATIVE
954 );
955 DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_start_date);
956 DBMS_SQL.BIND_VARIABLE(l_cursor, ':p_start_period', p_start_period);
957 l_sql_result := DBMS_SQL.EXECUTE_AND_FETCH(l_cursor, TRUE);
958 --
959 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_start_date);
960 --
961 DBMS_SQL.CLOSE_CURSOR(l_cursor);
962 --
963 x_start_date := l_start_date;
964 --
965 EXCEPTION
966 WHEN NO_DATA_FOUND THEN
967 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
968 DBMS_SQL.CLOSE_CURSOR(l_cursor);
969 END IF;
970 x_return_status := FND_API.G_RET_STS_ERROR;
971 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
972 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_Start_Date:NO_DATA_FOUND'); htp.para;
973 END IF;
974 RAISE FND_API.G_EXC_ERROR;
975 WHEN FND_API.G_EXC_ERROR THEN
976 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
977 DBMS_SQL.CLOSE_CURSOR(l_cursor);
978 END IF;
979 x_return_status := FND_API.G_RET_STS_ERROR;
980 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
981 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_Start_Date:G_EXC_ERROR'); htp.para;
982 END IF;
983 RAISE;
984 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
985 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
986 DBMS_SQL.CLOSE_CURSOR(l_cursor);
987 END IF;
988 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
989 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
990 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_Start_Date:G_EXC_UNEXPECTED_ERROR'); htp.para;
991 END IF;
992 RAISE;
993 WHEN OTHERS THEN
994 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
995 DBMS_SQL.CLOSE_CURSOR(l_cursor);
996 END IF;
997 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
998 BIS_UTILITIES_PVT.Add_Error_Message
999 ( p_error_table => x_error_Tbl
1000 , p_error_msg_id => SQLCODE
1001 , p_error_description => SQLERRM
1002 , x_error_table => x_error_Tbl
1003 );
1004 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1005 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_Start_Date:OTHERS'); htp.para;
1006 END IF;
1007 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1008 END Get_Start_Date;
1009 --
1010 --
1011 PROCEDURE Get_End_Date
1012 ( p_api_version IN NUMBER
1013 , p_Dimension_Level_Rec IN BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type
1014 , p_end_period IN VARCHAR2
1015 , x_end_date OUT NOCOPY DATE
1016 , x_return_status OUT NOCOPY VARCHAR2
1017 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1018 )
1019 IS
1020 --
1021 l_short_name VARCHAR2(30);
1022 l_view_name VARCHAR2(30);
1023 l_period_name VARCHAR2(15);
1024 l_period_set_name VARCHAR2(15);
1025 l_cursor INTEGER;
1026 l_select_stmt VARCHAR2(2000);
1027 l_sql_result INTEGER := 0;
1028 l_end_date DATE;
1029 --
1030 BEGIN
1031 x_return_status := FND_API.G_RET_STS_SUCCESS;
1032 --
1033 SELECT
1034 DIMENSION_LEVEL_SHORT_NAME
1035 , LEVEL_VALUES_VIEW_NAME
1036 INTO
1037 l_short_name
1038 , l_view_name
1039 FROM BISBV_DIMENSION_LEVELS
1040 WHERE DIMENSION_LEVEL_ID = p_Dimension_Level_Rec.Dimension_Level_ID;
1041 --
1042 --
1043 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
1044 DBMS_SQL.CLOSE_CURSOR(l_cursor);
1045 END IF;
1046 l_cursor := DBMS_SQL.OPEN_CURSOR;
1047 --
1048 l_select_stmt := 'SELECT DISTINCT '
1049 || ' END_DATE '
1050 || ' FROM '
1051 || l_view_name
1052 || ' WHERE '
1053 || ' ID = :p_end_period ';
1054 --
1055 DBMS_SQL.PARSE( c => l_cursor
1056 , statement => l_select_stmt
1057 , language_flag => DBMS_SQL.NATIVE
1058 );
1059 DBMS_SQL.DEFINE_COLUMN(l_cursor, 1, l_end_date);
1060 DBMS_SQL.BIND_VARIABLE(l_cursor, ':p_end_period', p_end_period);
1061 l_sql_result := DBMS_SQL.EXECUTE_AND_FETCH(l_cursor, TRUE);
1062 --
1063 DBMS_SQL.COLUMN_VALUE(l_cursor, 1, l_end_date);
1064 --
1065 DBMS_SQL.CLOSE_CURSOR(l_cursor);
1066 --
1067 x_end_date := l_end_date;
1068 --
1069 EXCEPTION
1070 WHEN NO_DATA_FOUND THEN
1071 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
1072 DBMS_SQL.CLOSE_CURSOR(l_cursor);
1073 END IF;
1074 x_return_status := FND_API.G_RET_STS_ERROR;
1075 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1076 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_End_Date:NO_DATA_FOUND'); htp.para;
1077 END IF;
1078 RAISE FND_API.G_EXC_ERROR;
1079 WHEN FND_API.G_EXC_ERROR THEN
1080 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
1081 DBMS_SQL.CLOSE_CURSOR(l_cursor);
1082 END IF;
1083 x_return_status := FND_API.G_RET_STS_ERROR;
1084 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1085 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_End_Date:G_EXC_ERROR'); htp.para;
1086 END IF;
1087 RAISE;
1088 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1089 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
1090 DBMS_SQL.CLOSE_CURSOR(l_cursor);
1091 END IF;
1092 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1093 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1094 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_End_Date:G_EXC_UNEXPECTED_ERROR'); htp.para;
1095 END IF;
1096 RAISE;
1097 WHEN OTHERS THEN
1098 IF(DBMS_SQL.IS_OPEN(l_cursor)) THEN
1099 DBMS_SQL.CLOSE_CURSOR(l_cursor);
1100 END IF;
1101 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1102 BIS_UTILITIES_PVT.Add_Error_Message
1103 ( p_error_table => x_error_Tbl
1104 , p_error_msg_id => SQLCODE
1105 , p_error_description => SQLERRM
1106 , x_error_table => x_error_Tbl
1107 );
1108 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1109 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Get_End_Date:OTHERS'); htp.para;
1110 END IF;
1111 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1112 END Get_End_Date;
1113 --
1114 --
1115 PROCEDURE Org_ID_to_Value
1116 ( p_api_version IN NUMBER
1117 , p_Dim_Level_Value_Rec IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
1118 , x_Dim_Level_Value_Rec OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
1119 , x_return_status OUT NOCOPY VARCHAR2
1120 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1121 )
1122 IS
1123 --
1124 l_view_name VARCHAR2(80);
1125 l_short_name VARCHAR2(30);
1126 l_name bis_levels_tl.name%TYPE;
1127 l_id VARCHAR2(250);
1128 l_value VARCHAR2(250);
1129 l_select_stmt VARCHAR2(2000);
1130 --
1131 BEGIN
1132 x_return_status := FND_API.G_RET_STS_SUCCESS;
1133 x_Dim_Level_Value_Rec := p_Dim_Level_Value_Rec;
1134 --
1135 SELECT
1136 DIMENSION_LEVEL_SHORT_NAME
1137 , DIMENSION_LEVEL_NAME
1138 , LEVEL_VALUES_VIEW_NAME
1139 INTO
1140 l_short_name
1141 , l_name
1142 , l_view_name
1143 FROM BISBV_DIMENSION_LEVELS
1144 WHERE DIMENSION_LEVEL_ID = p_Dim_Level_Value_Rec.Dimension_Level_ID;
1145 --
1146 IF(l_short_name <> 'TOTAL_ORGANIZATIONS') THEN
1147 --
1148 l_select_stmt := 'SELECT DISTINCT '
1149 || ' ID '
1150 || ', VALUE '
1151 || ' FROM '
1152 || l_view_name
1153 || ' WHERE ID = :1';
1154 --
1155 EXECUTE IMMEDIATE l_select_stmt INTO l_id, l_value USING p_Dim_Level_Value_Rec.Dimension_Level_Value_ID;
1156 IF (l_id IS NULL) THEN
1157 BIS_UTILITIES_PVT.Add_Error_Message
1158 ( p_error_msg_name => 'BIS_INVALID_ORGANIZATION_ID'
1159 , p_error_proc_name => 'BIS_TARGET_VALIDATE_PVT.Org_ID_to_Value'
1160 );
1161 RAISE FND_API.G_EXC_ERROR;
1162 END IF;
1163 x_Dim_Level_Value_Rec.Dimension_Level_Value_Name := l_value;
1164 x_Dim_Level_Value_Rec.Dimension_Level_Short_Name := l_short_name;
1165 x_Dim_Level_Value_Rec.Dimension_Level_Name := l_name;
1166 --
1167 ELSIF(p_Dim_Level_Value_Rec.Dimension_Level_Value_ID <> '-1') THEN
1168 -- populate error table
1169 BIS_UTILITIES_PVT.Add_Error_Message
1170 ( p_error_msg_name => 'BIS_INVALID_ORGANIZATION_ID'
1171 , p_error_proc_name => 'BIS_TARGET_VALIDATE_PVT.Org_ID_to_Value'
1172 );
1173 RAISE FND_API.G_EXC_ERROR;
1174 --
1175 ELSE
1176 -- populate record with name
1177 x_Dim_Level_Value_Rec.Dimension_Level_Value_Name := l_name;
1178 x_Dim_Level_Value_Rec.Dimension_Level_Short_Name := l_short_name;
1179 x_Dim_Level_Value_Rec.Dimension_Level_Name := l_name;
1180 END IF;
1181 --
1182 EXCEPTION
1183 WHEN NO_DATA_FOUND THEN
1184 x_return_status := FND_API.G_RET_STS_ERROR;
1185 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1186 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Org_ID_to_Value:NO_DATA_FOUND'); htp.para;
1187 END IF;
1188 RAISE FND_API.G_EXC_ERROR;
1189 WHEN FND_API.G_EXC_ERROR THEN
1190 x_return_status := FND_API.G_RET_STS_ERROR;
1191 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1192 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Org_ID_to_Value:G_EXC_ERROR'); htp.para;
1193 END IF;
1194 RAISE;
1195 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1197 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1198 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Org_ID_to_Value:G_EXC_UNEXPECTED_ERROR'); htp.para;
1199 END IF;
1200 RAISE;
1201 WHEN OTHERS THEN
1202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1203 BIS_UTILITIES_PVT.Add_Error_Message
1204 ( p_error_table => x_error_Tbl
1205 , p_error_msg_id => SQLCODE
1206 , p_error_description => SQLERRM
1207 , x_error_table => x_error_Tbl
1208 );
1209 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1210 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Org_ID_to_Value:OTHERS'); htp.para;
1211 END IF;
1212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1213 END Org_ID_to_Value;
1214 --
1215 --
1216 PROCEDURE Org_Value_to_ID
1217 ( p_api_version IN NUMBER
1218 , p_Dim_Level_Value_Rec IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
1219 , x_Dim_Level_Value_Rec OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_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 --
1225 l_view_name VARCHAR2(80);
1226 l_short_name VARCHAR2(30);
1227 l_name bis_levels_tl.name%TYPE;
1228 l_id VARCHAR2(80);
1229 l_value VARCHAR2(250);
1230 l_select_stmt VARCHAR2(2000);
1231 --
1232 BEGIN
1233 x_return_status := FND_API.G_RET_STS_SUCCESS;
1234 x_Dim_Level_Value_Rec := p_Dim_Level_Value_Rec;
1235 --
1236 SELECT
1237 DIMENSION_LEVEL_SHORT_NAME
1238 , DIMENSION_LEVEL_NAME
1239 , LEVEL_VALUES_VIEW_NAME
1240 INTO
1241 l_short_name
1242 , l_name
1243 , l_view_name
1244 FROM BISBV_DIMENSION_LEVELS
1245 WHERE DIMENSION_LEVEL_ID = p_Dim_Level_Value_Rec.Dimension_Level_ID;
1246 --
1247 IF(l_short_name <> 'TOTAL_ORGANIZATIONS') THEN
1248 --
1249 l_select_stmt := 'SELECT DISTINCT '
1250 || ' ID '
1251 || ', VALUE '
1252 || ' FROM '
1253 || l_view_name
1254 || ' WHERE VALUE = :1 ';
1255 --
1256 EXECUTE IMMEDIATE l_select_stmt INTO l_id, l_value USING p_Dim_Level_Value_Rec.Dimension_Level_Value_Name;
1257
1258 IF (l_id IS NULL) THEN
1259 BIS_UTILITIES_PVT.Add_Error_Message
1260 ( p_error_msg_name => 'BIS_INVALID_ORGANIZATION_VALUE'
1261 , p_error_proc_name => 'BIS_TARGET_VALIDATE_PVT.Org_Value_to_ID'
1262 );
1263 RAISE FND_API.G_EXC_ERROR;
1264 END IF;
1265 --
1266 x_Dim_Level_Value_Rec.Dimension_Level_Value_ID := l_id;
1267 x_Dim_Level_Value_Rec.Dimension_Level_Short_Name := l_short_name;
1268 x_Dim_Level_Value_Rec.Dimension_Level_Name := l_name;
1269 --
1270 ELSIF(p_Dim_Level_Value_Rec.Dimension_Level_Value_Name <> l_name) THEN
1271 -- populate error table
1272 BIS_UTILITIES_PVT.Add_Error_Message
1273 ( p_error_msg_name => 'BIS_INVALID_ORGANIZATION_VALUE'
1274 , p_error_proc_name => 'BIS_TARGET_VALIDATE_PVT.Org_Value_to_ID'
1275 );
1276 RAISE FND_API.G_EXC_ERROR;
1277 --
1278 ELSE
1279 -- populate record with id
1280 x_Dim_Level_Value_Rec.Dimension_Level_Value_ID := '-1';
1281 x_Dim_Level_Value_Rec.Dimension_Level_Short_Name := l_short_name;
1282 x_Dim_Level_Value_Rec.Dimension_Level_Name := l_name;
1283 END IF;
1284 --
1285 EXCEPTION
1286 WHEN NO_DATA_FOUND THEN
1287 x_return_status := FND_API.G_RET_STS_ERROR;
1288 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1289 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Org_Value_to_ID:NO_DATA_FOUND'); htp.para;
1290 END IF;
1291 RAISE FND_API.G_EXC_ERROR;
1292 WHEN FND_API.G_EXC_ERROR THEN
1293 x_return_status := FND_API.G_RET_STS_ERROR;
1294 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1295 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Org_Value_to_ID:G_EXC_ERROR'); htp.para;
1296 END IF;
1297 RAISE;
1298 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1299 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1300 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1301 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Org_Value_to_ID:G_EXC_UNEXPECTED_ERROR'); htp.para;
1302 END IF;
1303 RAISE;
1304 WHEN OTHERS THEN
1305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1306 BIS_UTILITIES_PVT.Add_Error_Message
1307 ( p_error_table => x_error_Tbl
1308 , p_error_msg_id => SQLCODE
1309 , p_error_description => SQLERRM
1310 , x_error_table => x_error_Tbl
1311 );
1312 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1313 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Org_Value_to_ID:OTHERS'); htp.para;
1314 END IF;
1315 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1316 END Org_Value_to_ID;
1317 --
1318 --
1319 PROCEDURE Time_ID_to_Value
1320 ( p_api_version IN NUMBER
1321 , p_Org_Level_Value_Rec IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
1322 , p_Dim_Level_Value_Rec IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
1323 , x_Dim_Level_Value_Rec OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
1324 , x_return_status OUT NOCOPY VARCHAR2
1325 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1326 )
1327 IS
1328 --
1329 l_view_name VARCHAR2(80);
1330 l_short_name VARCHAR2(30);
1331 l_name bis_levels_tl.name%TYPE;
1332 l_period_set VARCHAR2(250);
1333 l_value VARCHAR2(250);
1334 l_select_stmt VARCHAR2(2000);
1335 --
1336 BEGIN
1337 x_return_status := FND_API.G_RET_STS_SUCCESS;
1338 x_Dim_Level_Value_Rec := p_Dim_Level_Value_Rec;
1339 --
1340 SELECT
1341 DIMENSION_LEVEL_SHORT_NAME
1342 , DIMENSION_LEVEL_NAME
1343 , LEVEL_VALUES_VIEW_NAME
1344 INTO
1345 l_short_name
1346 , l_name
1347 , l_view_name
1348 FROM BISBV_DIMENSION_LEVELS
1349 WHERE DIMENSION_LEVEL_ID = p_Dim_Level_Value_Rec.Dimension_Level_ID;
1350 --
1351 IF(l_short_name <> 'TOTAL_TIME') THEN
1352 --
1353 IF (BIS_UTILITIES_PUB.is_time_dependent_on_org(p_time_lvl_short_name => l_short_name)
1354 = BIS_UTILITIES_PUB.G_TIME_IS_DEPEN_ON_ORG) THEN --2684911
1355 l_select_stmt := 'SELECT '
1356 || ' PERIOD_SET_NAME '
1357 || ', PERIOD_NAME '
1358 || ' FROM '
1359 || l_view_name
1360 || ' WHERE '
1361 || ' ORGANIZATION_ID = :1 '
1362 || ' AND NVL(ORGANIZATION_TYPE, ''%'') LIKE :2 '
1363 || ' AND '
1364 || ' PERIOD_SET_NAME || ''+'' || PERIOD_NAME = :3 ';
1365 EXECUTE IMMEDIATE l_select_stmt INTO l_period_set, l_value
1366 USING p_Org_Level_Value_Rec.Dimension_Level_Value_ID
1367 , p_Org_Level_Value_Rec.Dimension_Level_Short_Name
1368 , p_Dim_Level_Value_Rec.Dimension_Level_Value_ID;
1369 ELSE
1370 l_select_stmt := 'SELECT '
1371 || ' PERIOD_SET_NAME '
1372 || ', PERIOD_NAME '
1373 || ' FROM '
1374 || l_view_name
1375 || ' WHERE '
1376 || ' PERIOD_SET_NAME || ''+'' || PERIOD_NAME = :1';
1377 EXECUTE IMMEDIATE l_select_stmt INTO l_period_set, l_value
1378 USING p_Dim_Level_Value_Rec.Dimension_Level_Value_ID;
1379 END IF;
1380
1381 IF l_period_set IS NULL THEN
1382 BIS_UTILITIES_PVT.Add_Error_Message
1383 ( p_error_msg_name => 'BIS_INVALID_TIME_ID'
1384 , p_error_proc_name => 'BIS_TARGET_VALIDATE_PVT.Time_ID_to_Value'
1385 );
1386 RAISE FND_API.G_EXC_ERROR;
1387 END IF;
1388 --
1389 x_Dim_Level_Value_Rec.Dimension_Level_Value_Name := l_value;
1390 x_Dim_Level_Value_Rec.Dimension_Level_Short_Name := l_short_name;
1391 x_Dim_Level_Value_Rec.Dimension_Level_Name := l_name;
1392 --
1393 ELSIF(p_Dim_Level_Value_Rec.Dimension_Level_Value_ID <> '-1') THEN
1394 -- populate error table
1395 BIS_UTILITIES_PVT.Add_Error_Message
1396 ( p_error_msg_name => 'BIS_INVALID_TIME_ID'
1397 , p_error_proc_name => 'BIS_TARGET_VALIDATE_PVT.Time_ID_to_Value'
1398 );
1399 RAISE FND_API.G_EXC_ERROR;
1400 --
1401 ELSE
1402 -- populate record with name
1403 x_Dim_Level_Value_Rec.Dimension_Level_Value_Name := l_name;
1404 x_Dim_Level_Value_Rec.Dimension_Level_Short_Name := l_short_name;
1405 x_Dim_Level_Value_Rec.Dimension_Level_Name := l_name;
1406 END IF;
1407 --
1408 EXCEPTION
1409 WHEN NO_DATA_FOUND THEN
1410 x_return_status := FND_API.G_RET_STS_ERROR;
1411 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1412 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Time_ID_to_Value:NO_DATA_FOUND'); htp.para;
1413 END IF;
1414 RAISE FND_API.G_EXC_ERROR;
1415 WHEN FND_API.G_EXC_ERROR THEN
1416 x_return_status := FND_API.G_RET_STS_ERROR;
1417 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1418 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Time_ID_to_Value:G_EXC_ERROR'); htp.para;
1419 END IF;
1420 RAISE;
1421 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1422 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1423 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1424 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Time_ID_to_Value:G_EXC_UNEXPECTED_ERROR'); htp.para;
1425 END IF;
1426 RAISE;
1427 WHEN OTHERS THEN
1428 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1429 BIS_UTILITIES_PVT.Add_Error_Message
1430 ( p_error_table => x_error_Tbl
1431 , p_error_msg_id => SQLCODE
1432 , p_error_description => SQLERRM
1433 , x_error_table => x_error_Tbl
1434 );
1435 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1436 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Time_ID_to_Value:OTHERS'); htp.para;
1437 END IF;
1438 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1439 END Time_ID_to_Value;
1440 --
1441 --
1442 PROCEDURE Time_Value_to_ID
1443 ( p_api_version IN NUMBER
1444 , p_Org_Level_Value_Rec IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
1445 , p_Dim_Level_Value_Rec IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
1446 , x_Dim_Level_Value_Rec OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
1447 , x_return_status OUT NOCOPY VARCHAR2
1448 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1449 )
1450 IS
1451 --
1452 l_view_name VARCHAR2(80);
1453 l_short_name VARCHAR2(30);
1454 l_name bis_levels_tl.name%TYPE;
1455 l_id VARCHAR2(80);
1456 l_value VARCHAR2(250);
1457 l_select_stmt VARCHAR2(2000);
1458 --
1459 BEGIN
1460 x_return_status := FND_API.G_RET_STS_SUCCESS;
1461 x_Dim_Level_Value_Rec := p_Dim_Level_Value_Rec;
1462 --
1463 SELECT
1464 DIMENSION_LEVEL_SHORT_NAME
1465 , DIMENSION_LEVEL_NAME
1466 , LEVEL_VALUES_VIEW_NAME
1467 INTO
1468 l_short_name
1469 , l_name
1470 , l_view_name
1471 FROM BISBV_DIMENSION_LEVELS
1472 WHERE DIMENSION_LEVEL_ID = p_Dim_Level_Value_Rec.Dimension_Level_ID;
1473 --
1474 IF(l_short_name <> 'TOTAL_TIME') THEN
1475 --
1476 IF (BIS_UTILITIES_PUB.is_time_dependent_on_org(p_time_lvl_short_name => l_short_name)
1477 = BIS_UTILITIES_PUB.G_TIME_IS_DEPEN_ON_ORG) THEN --2684911
1478 l_select_stmt := 'SELECT '
1479 || ' ID '
1480 || ', VALUE '
1481 || ' FROM '
1482 || l_view_name
1483 || ' WHERE '
1484 || ' ORGANIZATION_ID = :1 '
1485 || ' AND NVL(ORGANIZATION_TYPE, ''%'') LIKE :2 '
1486 || ' AND '
1487 || ' VALUE = :3 ';
1488 EXECUTE IMMEDIATE l_select_stmt INTO l_id, l_value
1489 USING p_Org_Level_Value_Rec.Dimension_Level_Value_ID
1490 , p_Org_Level_Value_Rec.Dimension_Level_Short_Name
1491 , p_Dim_Level_Value_Rec.Dimension_Level_Value_Name;
1492 ELSE
1493 l_select_stmt := 'SELECT '
1494 || ' ID '
1495 || ', VALUE '
1496 || ' FROM '
1497 || l_view_name
1498 || ' WHERE '
1499 || ' VALUE = :1 '
1500 || ' AND rownum < 2 '; -- take the first row
1501 EXECUTE IMMEDIATE l_select_stmt INTO l_id, l_value
1502 USING p_Dim_Level_Value_Rec.Dimension_Level_Value_Name;
1503 END IF;
1504 --
1505 --
1506 IF(l_id IS NULL) THEN
1507 -- populate error table
1508 BIS_UTILITIES_PVT.Add_Error_Message
1509 ( p_error_msg_name => 'BIS_INVALID_TIME_VALUE'
1510 , p_error_proc_name => 'BIS_TARGET_VALIDATE_PVT.Time_Value_to_ID'
1511 );
1512 RAISE FND_API.G_EXC_ERROR;
1513 END IF;
1514 --
1515 --
1516 x_Dim_Level_Value_Rec.Dimension_Level_Value_Name := l_value;
1517 x_Dim_Level_Value_Rec.Dimension_Level_Short_Name := l_short_name;
1518 x_Dim_Level_Value_Rec.Dimension_Level_Name := l_name;
1519 --
1520 ELSIF(p_Dim_Level_Value_Rec.Dimension_Level_Value_Name <> l_name) THEN
1521 -- populate error table
1522 BIS_UTILITIES_PVT.Add_Error_Message
1523 ( p_error_msg_name => 'BIS_INVALID_TIME_VALUE'
1524 , p_error_proc_name => 'BIS_TARGET_VALIDATE_PVT.Time_Value_to_ID'
1525 );
1526 RAISE FND_API.G_EXC_ERROR;
1527 --
1528 ELSE
1529 -- populate record with name
1530 x_Dim_Level_Value_Rec.Dimension_Level_Value_ID := '-1';
1531 x_Dim_Level_Value_Rec.Dimension_Level_Short_Name := l_short_name;
1532 x_Dim_Level_Value_Rec.Dimension_Level_Name := l_name;
1533 END IF;
1534 --
1535 EXCEPTION
1536 WHEN NO_DATA_FOUND THEN
1537 x_return_status := FND_API.G_RET_STS_ERROR;
1538 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1539 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Time_Value_to_ID:NO_DATA_FOUND'); htp.para;
1540 END IF;
1541 RAISE FND_API.G_EXC_ERROR;
1542 WHEN FND_API.G_EXC_ERROR THEN
1543 x_return_status := FND_API.G_RET_STS_ERROR;
1544 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1545 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Time_Value_to_ID:G_EXC_ERROR'); htp.para;
1546 END IF;
1547 RAISE;
1548 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1549 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1550 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1551 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Time_Value_to_ID:G_EXC_UNEXPECTED_ERROR'); htp.para;
1552 END IF;
1553 RAISE;
1554 WHEN OTHERS THEN
1555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1556 BIS_UTILITIES_PVT.Add_Error_Message
1557 ( p_error_table => x_error_Tbl
1558 , p_error_msg_id => SQLCODE
1559 , p_error_description => SQLERRM
1560 , x_error_table => x_error_Tbl
1561 );
1562 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1563 htp.p('BIS_DIM_LEVEL_VALUE_PVT.Time_Value_to_ID:OTHERS'); htp.para;
1564 END IF;
1565 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1566 END Time_Value_to_ID;
1567 --
1568 --
1569 PROCEDURE DimensionX_ID_to_Value
1570 ( p_api_version IN NUMBER
1571 , p_Dim_Level_Value_Rec IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
1572 , p_set_of_books_id IN VARCHAR2 := NULL
1573 , x_Dim_Level_Value_Rec IN OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
1574 , x_return_status OUT NOCOPY VARCHAR2
1575 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1576 )
1577 IS
1578 --
1579 l_view_name VARCHAR2(80);
1580 l_short_name VARCHAR2(30);
1581 l_name bis_levels_tl.name%TYPE;
1582 l_cursor INTEGER;
1583 l_id VARCHAR2(250);
1584 l_value VARCHAR2(2000);
1585 l_select_stmt VARCHAR2(2000);
1586 l_sql_result INTEGER := 0;
1587 l_description bis_levels_tl.description%TYPE;
1588 l_id_name VARCHAR(2000);
1589 l_value_name VARCHAR(2000);
1590 l_msg_count NUMBER;
1591 l_msg_data VARCHAR(32000);
1592 l_start_date DATE;
1593 l_end_date DATE;
1594 --
1595 BEGIN
1596 x_return_status := FND_API.G_RET_STS_SUCCESS;
1597 x_Dim_Level_Value_Rec := p_Dim_Level_Value_Rec;
1598 --
1599
1600 BIS_PMF_GET_DIMLEVELS_PVT.Get_DimLevel_Values_Data
1601 (
1602 p_bis_dimlevel_id => p_Dim_Level_Value_Rec.Dimension_Level_ID
1603 ,x_dimlevel_short_name => l_short_name
1604 ,x_select_String => l_select_stmt
1605 ,x_table_name => l_view_name
1606 ,x_value_name => l_value_name
1607 ,x_id_name => l_id_name
1608 ,x_level_name => l_name
1609 ,x_description => l_description
1610 ,x_return_status => x_return_status
1611 ,x_msg_count => l_msg_count
1612 ,x_msg_data => l_msg_data
1613 );
1614 if(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
1615 BIS_UTILITIES_PVT.Add_Error_Message
1616 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_ID'
1617 , p_error_proc_name => 'BIS_TARGET_VALIDATE_PVT.DimensionX_ID_to_Value'
1618 , p_error_table => x_error_tbl
1619 , x_error_table => x_error_tbl
1620 );
1621 RAISE FND_API.G_EXC_ERROR;
1622 end if;
1623
1624 --------------------
1625
1626 IF(SUBSTR(l_short_name, 1, 5) <> 'TOTAL') THEN
1627
1628 IF ( UPPER ( l_view_name ) = 'DUAL') then -- 2408906
1629
1630 x_Dim_Level_Value_Rec.Dimension_Level_Value_Name := BIS_UTILITIES_PVT.GET_FND_MESSAGE( 'BIS_ALL_VALUE_ROLLING' ) ; -- 'All';
1631 x_Dim_Level_Value_Rec.Dimension_Level_Short_Name := l_short_name;
1632 x_Dim_Level_Value_Rec.Dimension_Level_Name := l_name;
1633
1634 ELSE
1635 l_select_stmt := l_select_stmt || ' WHERE ' ||l_id_name ||' = :1';
1636 --2699983
1637 l_select_stmt := l_select_stmt || ' AND ROWNUM < 2 ';
1638
1639 IF ( (p_Dim_Level_Value_Rec.Dimension_Level_Short_Name IN (C_GL_COMPANY, C_GL_SECONDARY_MEASURE)) AND
1640 (p_set_of_books_id IS NOT NULL)
1641 ) THEN
1642 l_select_stmt := l_select_stmt || ' AND SET_OF_BOOKS_ID = :2';
1643 EXECUTE IMMEDIATE l_select_stmt INTO l_id, l_value USING p_Dim_Level_Value_Rec.Dimension_Level_Value_Id, p_set_of_books_id;
1644 ELSIF(BIS_UTILITIES_PVT.is_valid_time_dimension_level(p_Dim_Level_Value_Rec.Dimension_Level_ID, x_return_status) = TRUE) THEN
1645 EXECUTE IMMEDIATE l_select_stmt INTO l_id, l_value, l_start_date, l_end_date USING p_Dim_Level_Value_Rec.Dimension_Level_Value_Id;
1646 ELSE
1647 EXECUTE IMMEDIATE l_select_stmt INTO l_id, l_value USING p_Dim_Level_Value_Rec.Dimension_Level_Value_Id;
1648 END IF;
1649
1650 IF (l_id IS NULL) THEN
1651 BIS_UTILITIES_PVT.Add_Error_Message
1652 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_VALUE_ID'
1653 , p_error_proc_name => 'BIS_TARGET_VALIDATE_PVT.DimensionX_ID_to_Value'
1654 , p_token1 => 'DIM_LEVEL_NAME'
1655 , p_value1 => l_name
1656 , p_error_table => x_error_tbl
1657 , x_error_table => x_error_tbl
1658 );
1659 RAISE FND_API.G_EXC_ERROR;
1660 END IF;
1661 x_Dim_Level_Value_Rec.Dimension_Level_Value_Name := SUBSTRB(l_value,1,240);
1662 x_Dim_Level_Value_Rec.Dimension_Level_Short_Name := l_short_name;
1663 x_Dim_Level_Value_Rec.Dimension_Level_Name := l_name;
1664 --
1665
1666 END IF;
1667
1668 ELSIF(p_Dim_Level_Value_Rec.Dimension_Level_Value_ID <> '-1') THEN
1669 -- populate error table
1670 --added last two params
1671 BIS_UTILITIES_PVT.Add_Error_Message
1672 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_VALUE_ID'
1673 , p_error_proc_name => 'BIS_TARGET_VALIDATE_PVT.DimensionX_ID_to_Value'
1674 , p_token1 => 'DIM_LEVEL_NAME'
1675 , p_value1 => l_name
1676 , p_error_table => x_error_tbl
1677 , x_error_table => x_error_tbl
1678 );
1679 RAISE FND_API.G_EXC_ERROR;
1680 --
1681 ELSE
1682 -- populate record with name
1683 x_Dim_Level_Value_Rec.Dimension_Level_Value_Name := l_name;
1684 x_Dim_Level_Value_Rec.Dimension_Level_Short_Name := l_short_name;
1685 x_Dim_Level_Value_Rec.Dimension_Level_Name := l_name;
1686 END IF;
1687 --
1688 --commented out NOCOPY RAISE
1689 EXCEPTION
1690 WHEN NO_DATA_FOUND THEN
1691 x_return_status := FND_API.G_RET_STS_ERROR;
1692 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1693 htp.p('BIS_DIM_LEVEL_VALUE_PVT.DimensionX_ID_to_Value:NO_DATA_FOUND'); htp.para;
1694 END IF;
1695 --RAISE FND_API.G_EXC_ERROR;
1696 WHEN FND_API.G_EXC_ERROR THEN
1697 x_return_status := FND_API.G_RET_STS_ERROR;
1698 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1699 htp.p('BIS_DIM_LEVEL_VALUE_PVT.DimensionX_ID_to_Value:G_EXC_ERROR'); htp.para;
1700 END IF;
1701 --RAISE;
1702 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1703 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1704 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1705 htp.p('BIS_DIM_LEVEL_VALUE_PVT.DimensionX_ID_to_Value:G_EXC_UNEXPECTED_ERROR'); htp.para;
1706 END IF;
1707 --RAISE;
1708 WHEN OTHERS THEN
1709 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1710 BIS_UTILITIES_PVT.Add_Error_Message
1711 ( p_error_table => x_error_Tbl
1712 , p_error_msg_id => SQLCODE
1713 , p_error_description => SQLERRM
1714 , x_error_table => x_error_Tbl
1715 );
1716 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1717 htp.p('BIS_DIM_LEVEL_VALUE_PVT.DimensionX_ID_to_Value:OTHERS'); htp.para;
1718 END IF;
1719 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1720 END DimensionX_ID_to_Value;
1721 --
1722 --
1723 PROCEDURE DimensionX_Value_to_ID
1724 ( p_api_version IN NUMBER
1725 , p_Dim_Level_Value_Rec IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
1726 , x_Dim_Level_Value_Rec OUT NOCOPY BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
1727 , x_return_status OUT NOCOPY VARCHAR2
1728 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1729 )
1730 IS
1731 --
1732 l_view_name VARCHAR2(80);
1733 l_short_name VARCHAR2(30);
1734 l_name bis_levels_tl.name%TYPE;
1735 l_cursor INTEGER;
1736 l_id VARCHAR2(80);
1737 l_value VARCHAR2(250);
1738 l_select_stmt VARCHAR2(2000);
1739 l_sql_result INTEGER := 0;
1740 --
1741 BEGIN
1742 x_return_status := FND_API.G_RET_STS_SUCCESS;
1743 x_Dim_Level_Value_Rec := p_Dim_Level_Value_Rec;
1744 --
1745 SELECT
1746 DIMENSION_LEVEL_SHORT_NAME
1747 , DIMENSION_LEVEL_NAME
1748 , LEVEL_VALUES_VIEW_NAME
1749 INTO
1750 l_short_name
1751 , l_name
1752 , l_view_name
1753 FROM BISBV_DIMENSION_LEVELS
1754 WHERE DIMENSION_LEVEL_ID = p_Dim_Level_Value_Rec.Dimension_Level_ID;
1755 --
1756 IF(SUBSTR(l_short_name, 1, 5) <> 'TOTAL') THEN
1757 l_select_stmt := 'SELECT DISTINCT ID, VALUE FROM '|| l_view_name
1758 || ' WHERE Value = :1';
1759
1760 EXECUTE IMMEDIATE l_select_stmt INTO l_id, l_value USING p_Dim_Level_Value_Rec.Dimension_Level_Value_Name;
1761 IF (l_id IS NULL) THEN
1762 BIS_UTILITIES_PVT.Add_Error_Message
1763 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_VALUE_NAME'
1764 , p_error_proc_name => 'BIS_TARGET_VALIDATE_PVT.DimensionX_Value_to_ID'
1765 , p_token1 => 'DIM_LEVEL_NAME'
1766 , p_value1 => l_name
1767 );
1768 RAISE FND_API.G_EXC_ERROR;
1769 END IF;
1770 x_Dim_Level_Value_Rec.Dimension_Level_Value_ID := l_id;
1771 x_Dim_Level_Value_Rec.Dimension_Level_Short_Name := l_short_name;
1772 x_Dim_Level_Value_Rec.Dimension_Level_Name := l_name;
1773 --
1774 ELSIF(p_Dim_Level_Value_Rec.Dimension_Level_Value_Name <> l_name) THEN
1775 -- populate error table
1776 BIS_UTILITIES_PVT.Add_Error_Message
1777 ( p_error_msg_name => 'BIS_INVALID_DIM_LEVEL_VALUE_NAME'
1778 , p_error_proc_name => 'BIS_TARGET_VALIDATE_PVT.DimensionX_Value_to_ID'
1779 , p_token1 => 'DIM_LEVEL_NAME'
1780 , p_value1 => l_name
1781 );
1782 RAISE FND_API.G_EXC_ERROR;
1783 --
1784 ELSE
1785 -- populate record with name
1786 x_Dim_Level_Value_Rec.Dimension_Level_Value_ID := '-1';
1787 x_Dim_Level_Value_Rec.Dimension_Level_Short_Name := l_short_name;
1788 x_Dim_Level_Value_Rec.Dimension_Level_Name := l_name;
1789 END IF;
1790 --
1791 EXCEPTION
1792 WHEN NO_DATA_FOUND THEN
1793 x_return_status := FND_API.G_RET_STS_ERROR;
1794 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1795 htp.p('BIS_DIM_LEVEL_VALUE_PVT.DimensionX_Value_to_ID:NO_DATA_FOUND'); htp.para;
1796 END IF;
1797 RAISE FND_API.G_EXC_ERROR;
1798 WHEN FND_API.G_EXC_ERROR THEN
1799 x_return_status := FND_API.G_RET_STS_ERROR;
1800 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1801 htp.p('BIS_DIM_LEVEL_VALUE_PVT.DimensionX_Value_to_ID:G_EXC_ERROR'); htp.para;
1802 END IF;
1803 RAISE;
1804 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1805 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1806 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1807 htp.p('BIS_DIM_LEVEL_VALUE_PVT.DimensionX_Value_to_ID:G_EXC_UNEXPECTED_ERROR'); htp.para;
1808 END IF;
1809 RAISE;
1810 WHEN OTHERS THEN
1811 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1812 BIS_UTILITIES_PVT.Add_Error_Message
1813 ( p_error_table => x_error_Tbl
1814 , p_error_msg_id => SQLCODE
1815 , p_error_description => SQLERRM
1816 , x_error_table => x_error_Tbl
1817 );
1818 IF(BIS_UTILITIES_PVT.G_DEBUG_FLAG = 1) THEN
1819 htp.p('BIS_DIM_LEVEL_VALUE_PVT.DimensionX_Value_to_ID:OTHERS'); htp.para;
1820 END IF;
1821 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1822 END DimensionX_Value_to_ID;
1823
1824 --
1825 Function Is_Current_Time_Period
1826 ( p_Dim_Level_Value_Rec IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
1827 , p_Org_Level_ID IN VARCHAR2
1828 , p_Org_Level_Short_name IN VARCHAR2
1829 , x_current_time_id OUT NOCOPY VARCHAR2
1830 ) RETURN BOOLEAN
1831 IS
1832
1833 l_sql VARCHAR2(32000);
1834 l_time_id VARCHAR2(32000) := NULL;
1835 l_dimension_level_rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
1836 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1837
1838 l_view_name VARCHAR2(80);
1839 l_short_name VARCHAR2(30);
1840 l_name bis_levels_tl.name%TYPE;
1841 l_cursor INTEGER;
1842 l_id VARCHAR2(250);
1843 l_value VARCHAR2(250);
1844 l_select_stmt VARCHAR2(2000);
1845 l_sql_result INTEGER := 0;
1846 l_description bis_levels_tl.description%TYPE;
1847 l_return_status VARCHAR2(240);
1848 l_id_name VARCHAR(2000);
1849 l_value_name VARCHAR(2000);
1850 l_msg_count NUMBER;
1851 l_msg_data VARCHAR(32000);
1852
1853 l_level_id NUMBER ; -- :=11, 99 on bis115dv -- 1740789 -- sashaik
1854 l_level_short_name VARCHAR2(60) ;
1855 l_source VARCHAR2(30);
1856
1857 l_star VARCHAR2(2) := '*';
1858 TYPE tcursor IS REF CURSOR;
1859 l1_cursor tcursor;
1860
1861 l_Org_Level_Id VARCHAR2(50) := null; -- 'XXX' ;
1862 l_Org_Level_Short_name VARCHAR2(50) := null; -- 'XXX' ;
1863
1864
1865 BEGIN
1866
1867 BIS_UTILITIES_PUB.put_line(p_text =>'..... in Is_Current_Time_Period');
1868
1869
1870 BIS_PMF_GET_DIMLEVELS_PVT.Get_DimLevel_Values_Data
1871 (
1872 p_bis_dimlevel_id => p_Dim_Level_Value_Rec.Dimension_Level_ID
1873 ,x_dimlevel_short_name => l_short_name
1874 ,x_select_String => l_select_stmt
1875 ,x_table_name => l_view_name
1876 ,x_value_name => l_value_name
1877 ,x_id_name => l_id_name
1878 ,x_level_name => l_name
1879 ,x_description => l_description
1880 ,x_return_status => l_return_status
1881 ,x_msg_count => l_msg_count
1882 ,x_msg_data => l_msg_data
1883 );
1884
1885
1886 l_level_id := p_Dim_Level_Value_Rec.Dimension_Level_ID;
1887
1888 l_source := bis_utilities_pvt.GET_SOURCE_FROM_DIM_LEVEL
1889 (
1890 p_DimLevelId => l_level_id
1891 , p_DimLevelShortName => l_level_short_name -- l_level_name
1892 );
1893
1894 BIS_UTILITIES_PUB.put_line(p_text =>' Source is '|| l_source );
1895
1896
1897 if ( l_source = 'OLTP' ) then
1898 bis_utilities_pvt.Get_Org_Info_Based_On_Source
1899 ( p_source => l_source,
1900 p_org_level_id => p_org_level_id,
1901 p_org_level_short_name => p_org_level_short_name,
1902 x_org_level_id => l_org_level_id,
1903 x_org_level_short_name => l_org_level_short_name
1904 );
1905
1906
1907 if ( l_org_level_id is not null ) then
1908 BIS_UTILITIES_PUB.put_line(p_text => ' org level id = ' || l_org_level_id ) ;
1909 else
1910 BIS_UTILITIES_PUB.put_line(p_text => ' org level id = ' || l_org_level_id ) ;
1911 end if;
1912
1913 elsif ( l_source = 'EDW') then
1914 l_org_level_id := p_org_level_id;
1915 l_org_level_short_name := p_org_level_short_name;
1916
1917 elsif ( l_source <> 'EDW') then
1918 BIS_UTILITIES_PUB.put_line(p_text => ' ERROR: Is_Current_Time_Period : source can be only either OLTP or EDW ' );
1919 end if;
1920
1921
1922 bis_utilities_pvt.Get_Time_Level_Value_ID_Minus
1923 ( p_source => l_source,
1924 p_view_name => l_view_name,
1925 p_id_name => l_id_name,
1926 p_org_level_id => l_org_level_id,
1927 p_org_level_short_name => l_org_level_short_name,
1928 p_sysdate_less => 0,
1929 x_time_id => l_time_id
1930 );
1931
1932 x_current_time_id := l_time_id;
1933
1934 if ( bis_utilities_pub.value_not_missing ( l_time_id ) = FND_API.G_TRUE )
1935 and ( bis_utilities_pub.value_not_null ( l_time_id ) = FND_API.G_TRUE )
1936 then
1937 BIS_UTILITIES_PUB.put_line(p_text => ' time id is ' || l_time_id );
1938 else
1939 BIS_UTILITIES_PUB.put_line(p_text => ' time id is NULL ' );
1940 end if;
1941
1942 IF l_time_id = p_Dim_Level_Value_Rec.dimension_level_value_id THEN
1943 BIS_UTILITIES_PUB.put_line(p_text =>' is_current_time_period: This time level value id is in current period ');
1944 RETURN true;
1945 ELSE
1946 BIS_UTILITIES_PUB.put_line(p_text =>' is_current_time_period: This time level value id is not in current period ');
1947 RETURN false;
1948 END IF;
1949
1950
1951 EXCEPTION
1952 WHEN OTHERS THEN
1953 BIS_UTILITIES_PUB.put_line(p_text =>'exception at Is_Current_Time_Period 0500: '||sqlerrm);
1954
1955 END Is_Current_Time_Period;
1956
1957
1958 --
1959 --
1960
1961 Function Is_Previous_Time_Period
1962 ( p_Dim_Level_Value_Rec IN BIS_DIM_LEVEL_VALUE_PUB.Dim_Level_Value_Rec_Type
1963 , p_Org_Level_ID IN VARCHAR2
1964 , p_Org_Level_Short_name IN VARCHAR2
1965 , x_Previous_time_id OUT NOCOPY VARCHAR2
1966 ) RETURN BOOLEAN
1967 IS
1968
1969 l_sql VARCHAR2(32000);
1970 l_time_id VARCHAR2(32000) := NULL;
1971 l_dimension_level_rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
1972 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1973 --l_period_set_name VARCHAR2(32000);
1974
1975 l_view_name VARCHAR2(80);
1976 l_short_name VARCHAR2(30);
1977 l_name bis_levels_tl.name%TYPE;
1978 l_cursor INTEGER;
1979 l_id VARCHAR2(250);
1980 l_value VARCHAR2(250);
1981 l_select_stmt VARCHAR2(2000);
1982 l_sql_result INTEGER := 0;
1983 l_description bis_levels_tl.description%TYPE;
1984 l_return_status VARCHAR2(240);
1985 l_id_name VARCHAR(2000);
1986 l_value_name VARCHAR(2000);
1987 l_msg_count NUMBER;
1988 l_msg_data VARCHAR(32000);
1989
1990 l_start_date date;
1991 l_end_date date;
1992
1993 l_level_id NUMBER ; -- :=11, 99 on bis115dv -- 1740789 -- sashaik
1994 l_level_short_name VARCHAR2(60) ;
1995 l_source VARCHAR2(30);
1996
1997 l_star VARCHAR2(2) := '*';
1998 TYPE tcursor IS REF CURSOR;
1999 l1_cursor tcursor;
2000 l_Org_Level_Id VARCHAR2(50) := null; -- 'XXX' ;
2001 l_Org_Level_Short_name VARCHAR2(50) := null; -- 'XXX' ;
2002 l_text VARCHAR2(3000) := null;
2003
2004
2005 BEGIN
2006
2007
2008 l_text := '..... in Is_Previous_Time_Period.';
2009
2010 BIS_PMF_GET_DIMLEVELS_PVT.Get_DimLevel_Values_Data
2011 (
2012 p_bis_dimlevel_id => p_Dim_Level_Value_Rec.Dimension_Level_ID
2013 ,x_dimlevel_short_name => l_short_name
2014 ,x_select_String => l_select_stmt
2015 ,x_table_name => l_view_name
2016 ,x_value_name => l_value_name
2017 ,x_id_name => l_id_name
2018 ,x_level_name => l_name
2019 ,x_description => l_description
2020 ,x_return_status => l_return_status
2021 ,x_msg_count => l_msg_count
2022 ,x_msg_data => l_msg_data
2023 );
2024
2025 l_level_id := p_Dim_Level_Value_Rec.Dimension_Level_ID;
2026
2027 l_source := bis_utilities_pvt.GET_SOURCE_FROM_DIM_LEVEL
2028 (
2029 p_DimLevelId => l_level_id
2030 , p_DimLevelShortName => l_level_short_name -- l_level_name
2031 );
2032
2033 l_text := l_text || ' Source is '|| l_source;
2034
2035
2036 if ( l_source = 'OLTP' ) then
2037 bis_utilities_pvt.Get_Org_Info_Based_On_Source
2038 ( p_source => l_source,
2039 p_org_level_id => p_org_level_id,
2040 p_org_level_short_name => p_org_level_short_name,
2041 x_org_level_id => l_org_level_id,
2042 x_org_level_short_name => l_org_level_short_name
2043 );
2044 elsif ( l_source = 'EDW') then
2045 l_org_level_id := p_org_level_id;
2046 l_org_level_short_name := p_org_level_short_name;
2047
2048 elsif ( l_source <> 'EDW') then
2049 l_text := l_text || ' ERROR: Is_Current_Time_Period : source can be only either OLTP or EDW ';
2050 end if;
2051
2052
2053 bis_utilities_pvt.Get_Time_Level_Value_ID_Minus
2054 ( p_source => l_source,
2055 p_view_name => l_view_name,
2056 p_id_name => l_id_name,
2057 p_org_level_id => l_org_level_id,
2058 p_org_level_short_name => l_org_level_short_name,
2059 p_sysdate_less => 0,
2060 x_time_id => l_time_id
2061 );
2062
2063
2064 bis_utilities_pvt.Get_Start_End_Dates
2065 ( p_source => l_source,
2066 p_view_name => l_view_name,
2067 p_id_col_name => l_id_name,
2068 p_id_value_name => l_time_id,
2069 p_org_level_id => l_org_level_id,
2070 p_org_level_short_name => l_org_level_short_name,
2071 x_start_date => l_start_date,
2072 x_end_date => l_end_date
2073 );
2074
2075
2076 l_time_id := NULL;
2077
2078
2079 bis_utilities_pvt.Get_Time_Level_Value_ID_Date
2080 ( p_source => l_source,
2081 p_view_name => l_view_name,
2082 p_id_name => l_id_name,
2083 p_org_level_id => l_org_level_id,
2084 p_org_level_short_name => l_org_level_short_name,
2085 p_target_date => l_start_date - 1,
2086 x_time_id => l_time_id
2087 );
2088
2089
2090 x_Previous_time_id := l_time_id;
2091
2092 l_text := l_text || ' input time level value id = ' || p_Dim_Level_Value_Rec.Dimension_Level_value_ID ;
2093
2094 l_text := l_text || ' ..... out NOCOPY of Is_Previous_Time_Period.';
2095
2096 if ( bis_utilities_pub.value_not_missing ( l_time_id ) = FND_API.G_TRUE )
2097 and ( bis_utilities_pub.value_not_null ( l_time_id ) = FND_API.G_TRUE )
2098 then
2099 l_text := l_text || ' previous time id is ' || l_time_id ;
2100 else
2101 l_text := l_text || ' previous time id is NULL ' ;
2102 end if;
2103
2104
2105 BIS_UTILITIES_PUB.put_line(p_text => l_text ) ;
2106
2107
2108 IF l_time_id = p_Dim_Level_Value_Rec.Dimension_Level_value_ID THEN
2109 BIS_UTILITIES_PUB.put_line(p_text =>' This time level value id is in previous period ');
2110 RETURN true;
2111 ELSE
2112 BIS_UTILITIES_PUB.put_line(p_text =>' This time level value id is not in previous period ');
2113 RETURN false;
2114 END IF;
2115
2116
2117 EXCEPTION
2118 WHEN OTHERS THEN
2119 BIS_UTILITIES_PUB.put_line(p_text =>'exception at Is_Previous_Time_Period: 0500 '||sqlerrm);
2120
2121 END Is_Previous_Time_Period;
2122
2123 --
2124 --
2125
2126 END BIS_DIM_LEVEL_VALUE_PVT;