[Home] [Help]
PACKAGE BODY: APPS.BIS_PMF_GET_DIMLEVELS_PVT
Source
1 PACKAGE BODY BIS_PMF_GET_DIMLEVELS_PVT AS
2 /* $Header: BISVGDLB.pls 120.19 2007/12/27 13:33:25 lbodired ship $ */
3 /*
4 REM dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
5 REM dbdrv: checkfile:~PROD:~PATH:~FILE
6 REM +=======================================================================+
7 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
8 REM | All rights reserved. |
9 REM +=======================================================================+
10 REM | FILENAME |
11 REM | BISVGDLS.pls |
12 REM | |
13 REM | DESCRIPTION |
14 REM | Private API for getting the Select String for DimensionLevelValues|
15 REM | This API will get the Select String from either EDW or BIS |
16 REM | depending on the profile option BIS_SOURCE |
17 REM | |
18 REM | HISTORY |
19 REM | December-2000 amkulkar Creation |
20 REM | 09-OCT-2002 MAHRAO Fix for 2617369 |
21 REM | 17-OCT-2002 MAHRAO Fix for 2525408 |
22 REM | 23-DEC-2002 MAHRAO Fix for 2668693 |
23 REM | 03-JAN-2003 RCHANDRA Bug 2721710, populate the global variable |
24 REM | G_DIM_LEVEL_SELECT_INFO_REC; reuse info if the |
25 REM | same the API is called for the same dimensionlevel
26 REM | 08-JAN-2003 RCHANDRA Bug 2721710,
27 REM | added proc cahce_dim_lvl_Select_info
28 REM | to copy values into the global variable
29 REM | 05-MAY-2003 arhegde enh#2819971 overload get_dim_level_select_string |
30 REM | 16-MAY-2003 rchandra bug#2959622 logic to parse p_paramlist |
31 REM | get_dimlevel_select_wrap |
32 REM | 11-JUL-2003 rchandra bug#3014105, added support for EDW dimensions |
33 REM | level relationships thru API get_oltp_edw_cols |
34 REM | 25-JAN-2004 gbhaloti bug#3388371 add support to get select string for |
35 REM | BSC dimension levels |
36 REM | 25-JAN-2004 gbhaloti bug#3395623 Removed "ORGANIZATION/ |
37 REM | INV ORGANIZATION" from DimLvlList |
38 REM | 11-FEB-2004 ankgoel bug#3426427 Added parameter p_add_distinct and |
39 REM | included "ORGANIZATION/INV ORGANIZATION" in DimLvlList |
40 REM | 12-FEB-2004 ankgoel bug#3436329 Removed whitespaces from "NAME" in |
41 REM | GET_EDW_SELECT_STRING procedure |
42 REM | 15-FEB-05 ppandey Enh #4016669, support ID, Value for Autogen DO |
43 REM | 27-JUN-05 arhegde enh# 4456833 - SQL + where clause for bis/bsc |
44 REM | dim level relationship from main API that PMV calls |
45 REM | 29-JUN-05 arhegde enh# 4456833 - Chgd where clause + filtered "All" |
46 REM | 30-JUN-05 arhegde enh# 4456833 - isRecursive in wrap API + BSC dim |
47 REM | level without parent_id is handled |
48 REM | 13-JUL-05 adrao added condition to check if it is a Periodicity time |
49 REM | dimension object in the API GET_BIS_SELECT_STRING |
50 REM | 27-Sep-05 ankgoel Bug#4625598,4626579 Uptake common API to get dim |
51 REM | level values |
52 REM | 19-Oct-05 arhegde enh# 4456833 parent-child whereclause relationship |
53 REM | 26-Oct-05 arhegde bug# 4699787 hierarchial where clause changes |
54 REM | 26-Oct-05 arhegde bug# 4699787 is_append_where_clause() pre-seeded |
55 REM | relationships do not send back dynamic where clause |
56 REM | 17-Nov-05 arhegde bug# 4697700 BSC DOs used in VBR fails.Passed back |
57 REM | code, value and bsc datasource for BSC DOs (get_bis_select_string|
58 REM | 11-Jan-06 arhegde bug# 4914929 Parent_Id is not passed back from SQL |
59 REM | unless it is a recursive dim object |
60 REM | 02-Feb-06 ashankar Bug#4871663 For BSC dim objects passing user_code |
61 REM | instead of code |
62 REM | 10-Feb-06 arhegde bug# 5029245 is_append_where_clause modified to |
63 REM | return true for non-seeded recursive dim object relations |
64 REM | 17-Feb-06 arhegde bug# 5041300 Reverting fix for bug# 4871663 due to |
65 REM | other issues such as parameter passing. Will fix bug# 4871663 later |
66 REM | 09-feb-2007 ashankar Simulation Tree Enhacement 5386112 |
67 REM | 09-Mar-2007 ashankar Fix for the bug #5920996 |
68 REM | 29/03/07 ashankar Bug#5932973 Supporting filters and key items for SM tree |
69 REM | 12/19/07 bijain Bug Fix 5945766 |
70 REM +=======================================================================+
71 */
72 --
73 -- CONSTANTS
74 EDW_ACCT_FLEXFIELD VARCHAR2(200) := 'EDW_GL_ACCT';
75 EDW_LVL_TBL_SUFFIX VARCHAR2(200) := '_LTC';
76 EDW_LVL_FLEX_PK_SUFFIX VARCHAR2(200) := '_NAME';
77 EDW_PK_KEY VARCHAR2(200) := '%PK_KEY';
78 G_PKG_NAME VARCHAR2(200) := 'BIS_PMF_GET_DIMLEVELS_PVT';
79 G_DIM_LEVEL_SELECT_INFO_REC BIS_PMF_GET_DIMLEVELS_PVT.dim_level_select_rec_Type;
80
81 G_PMF_BIND_VAR VARCHAR2(200) := ':PARENT_DIMLVL_VALUE_ID';
82 G_BIND_INTEGER NUMBER(2) := 1;
83 G_BIND_VARCHAR2 NUMBER(2) := 2;
84 G_BIND_DATE NUMBER(2) := 3;
85 G_BIND_NUMERIC NUMBER(2) := 4;
86 G_ID_NAME VARCHAR2(80) := 'Id';
87 G_VALUE_NAME VARCHAR2(80) := 'Value';
88 G_PARENT_NAME VARCHAR2(80) := 'Parent';
89
90 G_OLTP VARCHAR2(10) := 'OLTP';
91 G_EDW VARCHAR2(10) := 'EDW';
92
93 /* For bug #3388371 */
94 G_BSC_ID_NAME VARCHAR2(80) := 'Code';
95 G_BSC_VALUE_NAME VARCHAR2(80) := 'Name';
96
97 TYPE DimLvlList IS VARRAY(100) of VARCHAR2(100);
98 -- This list is prepared with dimension levels under 'EDW_ORGANIZATION_M', 'ORGANIZATION', 'EDW_MTL_INVENTORY_LOC_M',
99 -- 'INVENTORY LOCATION' dimensions (as existed, but validated thru. code) + HRI_PER_USRDR_H
100 -- EDW_TIME_CAL_PERIOD, EDW_TIME_CAL_MONTH, EDW_TIME_CAL_YEAR
101 Dlist DimLvlList := DimLvlList('EDW_ORGA_LEG_ENTITY', 'EDW_ORGA_TREE1_LVL2', 'EDW_ORGA_TREE1_LVL6', 'EDW_ORGANIZATION_A',
102 'EDW_ORGA_BUSINESS_GRP', 'EDW_ORGA_OPER_UNIT', 'EDW_ORGA_ORG', 'EDW_ORGA_TREE1_LVL1',
103 'EDW_ORGA_TREE1_LVL3', 'EDW_ORGA_TREE1_LVL4', 'EDW_ORGA_TREE1_LVL5', 'EDW_ORGA_TREE1_LVL7',
104 'EDW_ORGA_TREE1_LVL8', 'OPERATING UNIT', 'ORGANIZATION', 'INV ORGANIZATION', 'LEGAL ENTITY', 'BUSINESS GROUP',
105 'HR ORGANIZATION', 'TOTAL_ORGANIZATIONS', 'OPM COMPANY', 'SET OF BOOKS', 'PJI_ORGANIZATIONS',
106 'PJI_SUBORG', 'JTF_RESOURCE_GROUP_MEM', 'JTF_ORG_INTERACTION_CENTER_GRP', 'JTF_ORG_SALES_GROUP',
107 'FII_OPERATING_UNITS', 'HRI_CL_ORGCC', 'HRI_ORG_BGR_HX', 'HRI_ORG_HRCYVRSN_BX', 'HRI_ORG_HRCY_BX',
108 'HRI_ORG_HR_H', 'HRI_ORG_HR_HX', 'HRI_ORG_INHV_H', 'HRI_ORG_SRHL', 'HRI_ORG_SSUP_H', 'HRI_PER_USRDR_H',
109 'EDW_MTL_ILDM_OU', 'EDW_MTL_ILDM_SUB_INV', 'EDW_MTL_ILDM_LOCATOR', 'EDW_MTL_ILDM_PCMP', 'EDW_MTL_ILDM_PLANT',
110 'EDW_MTL_ILDM_PORG', 'EDW_MTL_INVENTORY_LOC_A', 'OPM ORGANIZATION', 'OPM WAREHOUSE',
111 'SUBINVENTORY', 'TOTAL INV LOCATIONS', 'EDW_TIME_CAL_PERIOD', 'EDW_TIME_CAL_QTR', 'EDW_TIME_CAL_YEAR');
112
113 --====================================================================
114 PROCEDURE get_oltp_edw_cols (
115 p_Dim_Level_Short_Name IN VARCHAR2
116 ,p_Source IN VARCHAR2
117 ,x_Table_Name OUT NOCOPY VARCHAR2
118 ,x_Id_Name OUT NOCOPY VARCHAR2
119 ,x_Value_Name OUT NOCOPY VARCHAR2
120 ,x_return_status OUT NOCOPY VARCHAR2
121 ,x_msg_count OUT NOCOPY NUMBER
122 ,x_msg_data OUT NOCOPY VARCHAR2
123 );
124 --====================================================================
125
126 PROCEDURE get_select_string (
127 p_bis_source IN VARCHAR2
128 ,p_is_relation_recursive IN VARCHAR2
129 ,p_is_relationship_found IN VARCHAR2
130 ,p_dim_rel_info_rec IN bis_pmf_get_dimlevels_pub.dim_rel_info_rec
131 ,p_region_code IN ak_regions.region_code%TYPE
132 ,x_select_string OUT NOCOPY VARCHAR2
133 ,x_bind_params OUT NOCOPY BIS_PMF_QUERY_ATTRIBUTES_TABLE-- (attribute_value, attribute_data_type)
134 ,x_where_clause OUT NOCOPY VARCHAR2
135 ,x_data_source OUT NOCOPY VARCHAR2
136 ,x_id_name OUT NOCOPY VARCHAR2
137 ,x_value_name OUT NOCOPY VARCHAR2
138 ,x_parent_name OUT NOCOPY VARCHAR2
139 ,x_time_level OUT NOCOPY VARCHAR2
140 ,x_return_status OUT NOCOPY VARCHAR2
141 ,x_msg_count OUT NOCOPY NUMBER
142 ,x_msg_data OUT NOCOPY VARCHAR2
143
144 );
145 --================================================================
146 PROCEDURE get_pmf_data_source(
147 p_bis_source IN VARCHAR2
148 ,p_dim_rel_info_rec IN bis_pmf_get_dimlevels_pub.dim_rel_info_rec
149 ,p_is_relation_recursive IN VARCHAR2
150 ,x_select_string OUT NOCOPY VARCHAR2
151 ,x_bind_params OUT NOCOPY BIS_PMF_QUERY_ATTRIBUTES_TABLE
152 ,x_data_source OUT NOCOPY VARCHAR2
153 ,x_id_name OUT NOCOPY VARCHAR2
154 ,x_value_name OUT NOCOPY VARCHAR2
155 ,x_parent_name OUT NOCOPY VARCHAR2
156 ,x_time_level OUT NOCOPY VARCHAR2
157 ,x_return_status OUT NOCOPY VARCHAR2
158 ,x_msg_count OUT NOCOPY NUMBER
159 ,x_msg_data OUT NOCOPY VARCHAR2
160 );
161
162 --================================================================
163 PROCEDURE get_bsc_data_source(
164 p_dim_rel_info_rec IN bis_pmf_get_dimlevels_pub.dim_rel_info_rec
165 ,p_region_code IN ak_regions.region_code%TYPE
166 ,x_id_name OUT NOCOPY VARCHAR2
167 ,x_value_name OUT NOCOPY VARCHAR2
168 ,x_parent_name OUT NOCOPY VARCHAR2
169 ,x_select_string OUT NOCOPY VARCHAR2
170 ,x_data_source OUT NOCOPY VARCHAR2
171 ,x_return_status OUT NOCOPY VARCHAR2
172 ,x_msg_count OUT NOCOPY NUMBER
173 ,x_msg_data OUT NOCOPY VARCHAR2
174 );
175 --================================================================
176
177 PROCEDURE get_relationship_details (
178 p_child_dimlvl_rec IN bis_pmf_get_dimlevels_pub.dimlvl_rec_type
179 ,p_parent_dimlvl_tbl IN bis_pmf_get_dimlevels_pub.dimlvl_tbl_type
180 ,x_is_relation_recursive OUT NOCOPY VARCHAR2
181 ,x_is_relationship_found OUT NOCOPY VARCHAR2
182 ,x_dim_rel_info_rec OUT NOCOPY bis_pmf_get_dimlevels_pub.dim_rel_info_rec
183 ,x_return_status OUT NOCOPY VARCHAR2
184 ,x_msg_count OUT NOCOPY NUMBER
185 ,x_msg_data OUT NOCOPY VARCHAR2
186 );
187 --================================================================
188
189 PROCEDURE get_bsc_relationship_data (
190 p_child_dimlvl_rec IN bis_pmf_get_dimlevels_pub.dimlvl_rec_type
191 ,p_parent_dimlvl_rec IN bis_pmf_get_dimlevels_pub.dimlvl_rec_type
192 ,x_bsc_dim_rel_info_rec OUT NOCOPY bsc_dimension_levels_pub.bsc_dim_level_rec_type
193 ,x_is_relationship_found OUT NOCOPY VARCHAR2
194 ,x_is_relation_recursive OUT NOCOPY VARCHAR2
195 ,x_return_status OUT NOCOPY VARCHAR2
196 ,x_msg_count OUT NOCOPY NUMBER
197 ,x_msg_data OUT NOCOPY VARCHAR2
198 );
199 --================================================================
200
201 FUNCTION get_source (
202 p_dim_level_short_name IN VARCHAR2
203 )RETURN VARCHAR2;
204 --================================================================
205
206 FUNCTION needs_time_cols (
207 p_source IN bis_levels.source%TYPE
208 ,p_data_source IN bis_levels.level_values_view_name%TYPE
209 ,p_lvlshortname IN bis_levels.short_name%TYPE
210 ,p_dimshortname IN bis_dimensions.short_name%TYPE
211 ) RETURN BOOLEAN;
212
213 --====================================================================
214 FUNCTION IS_DISTINCT_USED
215 (p_Dimension_Level_Short_Name IN bis_levels.short_name%TYPE
216 ) RETURN BOOLEAN;
217
218 -- Private function used to to see if the dim level info is already cached
219 FUNCTION IS_DIM_LVL_INFO_CACHED
220 (p_DimLevelSName IN bis_levels.short_name%TYPE
221 ,x_Select_String OUT NOCOPY VARCHAR2
222 ,x_table_name OUT NOCOPY VARCHAR2
223 ,x_id_name OUT NOCOPY VARCHAR2
224 ,x_value_name OUT NOCOPY VARCHAR2
225 ,x_parent_name OUT NOCOPY VARCHAR2
226 ,x_time_level OUT NOCOPY VARCHAR2
227 ) RETURN BOOLEAN;
228
229 -- private function to Store the values into the Global variable
230 PROCEDURE cache_dim_lvl_Select_info
231 (p_DimLevelSName IN VARCHAR2
232 ,p_Select_String IN VARCHAR2
233 ,p_table_name IN VARCHAR2
234 ,p_id_name IN VARCHAR2
235 ,p_value_name IN VARCHAR2
236 ,p_parent_name IN VARCHAR2
237 ,p_time_level IN VARCHAR2
238 ) ;
239
240 FUNCTION is_append_where_clause(
241 p_dim_rel_info_rec IN bis_pmf_get_dimlevels_pub.dim_rel_info_rec
242 ,p_is_relation_recursive IN VARCHAR2
243 ) RETURN BOOLEAN;
244
245 --==================================================================
246 PROCEDURE Get_Bsc_Dim_Obj_details
247 (
248 p_dim_rel_info_rec IN bis_pmf_get_dimlevels_pub.dim_rel_info_rec
249 ,p_region_code IN ak_regions.region_code%TYPE
250 ,x_child_level_pk_col OUT NOCOPY BSC_SYS_DIM_LEVELS_B.LEVEL_PK_COL%TYPE
251 ,x_child_level_view_name OUT NOCOPY BSC_SYS_DIM_LEVELS_B.LEVEL_VIEW_NAME%TYPE
252 ,x_parent_level_pk_col OUT NOCOPY BSC_SYS_DIM_LEVELS_B.LEVEL_PK_COL%TYPE
253 ,x_parent_level_view_name OUT NOCOPY BSC_SYS_DIM_LEVELS_B.LEVEL_VIEW_NAME%TYPE
254 ,x_return_status OUT NOCOPY VARCHAR2
255 ,x_msg_count OUT NOCOPY NUMBER
256 ,x_msg_data OUT NOCOPY VARCHAR2
257
258 );
259
260 PROCEDURE GET_DIMLEVEL_SELECT_STRING
261 (p_DimLevelName IN VARCHAR2
262 ,p_add_distinct IN VARCHAR2 := 'F'
263 ,x_Select_String OUT NOCOPY VARCHAR2
264 ,x_table_name OUT NOCOPY VARCHAR2
265 ,x_id_name OUT NOCOPY VARCHAR2
266 ,x_value_name OUT NOCOPY VARCHAR2
267 ,x_time_level OUT NOCOPY VARCHAR2
268 ,x_return_status OUT NOCOPY VARCHAR2
269 ,x_msg_count OUT NOCOPY NUMBER
270 ,x_msg_data OUT NOCOPY VARCHAR2
271 )
272 IS
273 l_api_name VARCHAR2(200) :=' GET_DIMLEVEL_SELECT_STRING';
274 CURSOR c_dims IS
275 SELECT source, level_values_view_name
276 FROM bis_levels
277 WHERE short_name = p_DimLevelName
278 ;
279 l_source VARCHAR2(2000);
280 l_level_values_view_name VARCHAR2(32000);
281 l_parent_name VARCHAR2(100);
282
283 /* 3388371- gbhaloti FOR BSC LEVELS */
284 -- l_bsc_source VARCHAR(10);
285
286 BEGIN
287 FND_MSG_PUB.initialize;
288 -- Reuse the value from the global record if it is the same dimension level
289 IF (IS_DIM_LVL_INFO_CACHED (p_DimLevelSName => p_DimLevelName
290 ,x_Select_String => x_Select_String
291 ,x_table_name => x_table_name
292 ,x_id_name => x_id_name
293 ,x_value_name => x_value_name
294 ,x_parent_name => l_parent_name
295 ,x_time_level => x_time_level ) ) THEN
296
297 x_return_status := FND_API.G_RET_STS_SUCCESS ;
298
299 RETURN;
300 END IF;
301
305
302 /* 3388371- gbhaloti FOR BSC LEVELS */
303 -- l_bsc_source := get_dim_level_source (p_DimLevelName);
304
306 OPEN c_dims;
307 FETCH c_dims INTO l_Source, l_level_Values_view_name;
308 IF (c_dims%NOTFOUND) THEN
309 FND_MESSAGE.SET_NAME('BIS','BIS_INAVLID_LEVEL_SHORT_NAME');
310 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME', p_DimLevelName);
311 FND_MSG_PUB.ADD;
312 RAISE FND_API.G_EXC_ERROR;
313 END IF;
314 CLOSE c_dims;
315 --IF (fnd_profile.value('BIS_SOURCE') = 'EDW')
316 IF ((l_source = 'EDW') and
317 (l_level_values_view_name IS NULL))-- OR l_level_values_view_name='AAA'))
318 THEN
319 BIS_PMF_GET_DIMLEVELS_PVT.GET_EDW_SELECT_STRING
320 (p_dim_level_name => p_DimLevelName
321 ,p_source => l_source
322 ,x_table_name => x_table_name
323 ,x_id_name => x_id_name
324 ,x_value_name => x_value_name
325 ,x_edw_select_String => x_Select_String
326 ,x_time_level => x_time_level
327 ,x_return_status => x_return_status
328 ,x_msg_count => x_msg_count
329 ,x_msg_data => x_msg_data
330 );
331 ELSE
332 BIS_PMF_GET_DIMLEVELS_PVT.GET_BIS_SELECT_STRING
333 (p_dim_level_name => p_DimLevelName
334 ,p_source => l_source
335 ,p_add_distinct => p_add_distinct
336 ,x_table_name => x_table_name
337 ,x_id_name => x_id_name
338 ,x_value_name => x_value_name
339 ,x_bis_select_string => x_select_string
340 ,x_time_level => x_time_level
341 ,x_return_status => x_return_status
342 ,x_msg_count => x_msg_count
343 ,x_msg_data => x_msg_data
344 );
345 END IF;
346
347
348 -- Store the values into the Global variable
349 cache_dim_lvl_Select_info
350 (p_DimLevelSName => p_DimLevelName
351 ,p_Select_String => x_Select_String
352 ,p_table_name => x_table_name
353 ,p_id_name => x_id_name
354 ,p_value_name => x_value_name
355 ,p_parent_name => l_parent_name
356 ,p_time_level => x_time_level
357 );
358
359 EXCEPTION
360 WHEN FND_API.G_EXC_ERROR THEN
361 x_return_status := FND_API.G_RET_STS_ERROR;
362 FND_MSG_PUB.Count_And_Get
363 ( p_count => x_msg_count,
364 p_data => x_msg_data
365 );
366
367 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
368 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
369 FND_MSG_PUB.Count_And_Get
370 ( p_count => x_msg_count,
371 p_data => x_msg_data
372 );
373 WHEN OTHERS THEN
374 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
375 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
376 THEN
377 FND_MSG_PUB.Add_Exc_Msg
378 ( G_PKG_NAME,
379 l_api_name
380 );
381 END IF;
382 FND_MSG_PUB.Count_And_Get
383 ( p_count => x_msg_count,
384 p_data => x_msg_data
385 );
386 END;
387 --
388
389 PROCEDURE get_dimlevel_select_string (
390 p_DimLevelName IN VARCHAR2
391 ,p_bis_source IN VARCHAR2
392 ,x_Select_String OUT NOCOPY VARCHAR2
393 ,x_table_name OUT NOCOPY VARCHAR2
394 ,x_id_name OUT NOCOPY VARCHAR2
395 ,x_value_name OUT NOCOPY VARCHAR2
396 ,x_time_level OUT NOCOPY VARCHAR2
397 ,x_return_status OUT NOCOPY VARCHAR2
398 ,x_msg_count OUT NOCOPY NUMBER
399 ,x_msg_data OUT NOCOPY VARCHAR2
400 )
401 IS
402 l_parent_name VARCHAR2(100);
403 BEGIN
404
405 GET_DIMLEVEL_SELECT_STRING (
406 p_DimLevelName => p_DimLevelName
407 ,p_bis_source => p_bis_source
408 ,x_Select_String => x_Select_String
409 ,x_table_name => x_table_name
410 ,x_id_name => x_id_name
411 ,x_value_name => x_value_name
412 ,x_parent_name => l_parent_name
413 ,x_time_level => x_time_level
414 ,x_return_status => x_return_status
415 ,x_msg_count => x_msg_count
416 ,x_msg_data => x_msg_data
417 );
418
419 END;
420
421 --Overloaded to get the parent_name
422 PROCEDURE GET_DIMLEVEL_SELECT_STRING
423 (p_DimLevelName IN VARCHAR2
424 ,p_bis_source IN VARCHAR2
425 ,x_Select_String OUT NOCOPY VARCHAR2
426 ,x_table_name OUT NOCOPY VARCHAR2
427 ,x_id_name OUT NOCOPY VARCHAR2
428 ,x_value_name OUT NOCOPY VARCHAR2
429 ,x_parent_name OUT NOCOPY VARCHAR2
430 ,x_time_level OUT NOCOPY VARCHAR2
431 ,x_return_status OUT NOCOPY VARCHAR2
432 ,x_msg_count OUT NOCOPY NUMBER
433 ,x_msg_data OUT NOCOPY VARCHAR2
434 )
435 IS
436 l_api_name VARCHAR2(200) :=' GET_DIMLEVEL_SELECT_STRING';
437 CURSOR c_dims IS
438 SELECT source, level_values_view_name
439 FROM bis_levels
440 WHERE short_name = p_DimLevelName
441 ;
442 l_source VARCHAR2(2000);
446 -- l_bsc_source VARCHAR(10);
443 l_level_values_view_name VARCHAR2(32000);
444
445 /* 3388371- gbhaloti FOR BSC LEVELS */
447 BEGIN
448 FND_MSG_PUB.initialize;
449 -- Reuse the value from the global record if it is the same dimension level
450 IF (IS_DIM_LVL_INFO_CACHED (p_DimLevelSName => p_DimLevelName
451 ,x_Select_String => x_Select_String
452 ,x_table_name => x_table_name
453 ,x_id_name => x_id_name
454 ,x_value_name => x_value_name
455 ,x_parent_name => x_parent_name
456 ,x_time_level => x_time_level ) ) THEN
457
458 x_return_status := FND_API.G_RET_STS_SUCCESS ;
459
460 RETURN;
461 END IF;
462
463 /* 3388371- gbhaloti FOR BSC LEVELS */
464 -- l_bsc_source := get_dim_level_source (p_DimLevelName);
465
466 l_source := p_bis_source;
467 --IF (l_source IS null)
468 --THEN
469 OPEN c_dims;
470 FETCH c_dims INTO l_Source, l_level_values_view_name;
471 IF (c_dims%NOTFOUND) THEN
472 FND_MESSAGE.SET_NAME('BIS','BIS_INAVLID_LEVEL_SHORT_NAME');
473 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME', p_DimLevelName);
474 FND_MSG_PUB.ADD;
475 RAISE FND_API.G_EXC_ERROR;
476 END IF;
477 CLOSE c_dims;
478 --END IF;
479 --IF (fnd_profile.value('BIS_SOURCE') = 'EDW')
480 IF ((l_source = 'EDW') and (l_level_values_view_name IS NULL))
481 THEN
482 BIS_PMF_GET_DIMLEVELS_PVT.GET_EDW_SELECT_STRING
483 (p_dim_level_name => p_DimLevelName
484 ,p_source => l_source
485 ,x_table_name => x_table_name
486 ,x_id_name => x_id_name
487 ,x_value_name => x_value_name
488 ,x_edw_select_String => x_Select_String
489 ,x_time_level => x_time_level
490 ,x_return_status => x_return_status
491 ,x_msg_count => x_msg_count
492 ,x_msg_data => x_msg_data
493 );
494 ELSE
495 BIS_PMF_GET_DIMLEVELS_PVT.GET_BIS_SELECT_STRING
496 (p_dim_level_name => p_DimLevelName
497 ,p_source => l_source
498 ,x_table_name => x_table_name
499 ,x_id_name => x_id_name
500 ,x_value_name => x_value_name
501 ,x_parent_name => x_parent_name
502 ,x_bis_select_string => x_select_string
503 ,x_time_level => x_time_level
504 ,x_return_status => x_return_status
505 ,x_msg_count => x_msg_count
506 ,x_msg_data => x_msg_data
507 );
508 END IF;
509
510
511 -- Store the values into the Global variable
512 cache_dim_lvl_Select_info
513 (p_DimLevelSName => p_DimLevelName
514 ,p_Select_String => x_Select_String
515 ,p_table_name => x_table_name
516 ,p_id_name => x_id_name
517 ,p_value_name => x_value_name
518 ,p_parent_name => x_parent_name
519 ,p_time_level => x_time_level
520 );
521
522 EXCEPTION
523 WHEN FND_API.G_EXC_ERROR THEN
524 x_return_status := FND_API.G_RET_STS_ERROR;
525 FND_MSG_PUB.Count_And_Get
526 ( p_count => x_msg_count,
527 p_data => x_msg_data
528 );
529
530 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
531 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
532 FND_MSG_PUB.Count_And_Get
533 ( p_count => x_msg_count,
534 p_data => x_msg_data
535 );
536 WHEN OTHERS THEN
537 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
538 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
539 THEN
540 FND_MSG_PUB.Add_Exc_Msg
541 ( G_PKG_NAME,
542 l_api_name
543 );
544 END IF;
545 FND_MSG_PUB.Count_And_Get
546 ( p_count => x_msg_count,
547 p_data => x_msg_data
548 );
549 END;
550 FUNCTION isAccounting_Flexfield
551 (p_dim_level_name IN VARCHAR2
552 )
553 RETURN BOOLEAN
554 IS
555 l_dimshortname varchar2(32000);
556 l_dim_lvl_sql varchar2(32000);
557
558 BEGIN
559 -- 2214178
560 l_dim_lvl_sql := 'SELECT dim.DIM_NAME dimshortname '||
561 ' FROM '||
562 ' edw_dimensions_md_v dim, edw_levels_md_v lvl '||
563 ' WHERE '||
564 ' lvl.DIM_ID = dim.DIM_ID AND '||
565 ' lvl.LEVEL_NAME = :p_dim_level_name ';
566
567 BEGIN
568 EXECUTE IMMEDIATE l_dim_lvl_sql INTO
569 l_dimshortname
570 USING p_dim_level_name;
571 EXCEPTION WHEN NO_DATA_FOUND THEN
572 NULL;
573 END;
574 IF (instr(l_dimshortname,EDW_ACCT_FLEXFIELD) <> 0)
575 THEN
576 RETURN true;
577 ELSE
578 RETURN false;
579 END IF;
580 END;
581
582 PROCEDURE GET_EDW_SELECT_STRING
583 (p_dim_level_name IN VARCHAR2
584 ,p_source IN VARCHAR2 := NULL -- 2617369
585 ,x_table_name OUT NOCOPY VARCHAR2
589 ,x_time_level OUT NOCOPY VARCHAR2
586 ,x_id_name OUT NOCOPY VARCHAR2
587 ,x_value_name OUT NOCOPY VARCHAR2
588 ,x_edw_select_String OUT NOCOPY VARCHAR2
590 ,x_return_status OUT NOCOPY VARCHAR2
591 ,x_msg_count OUT NOCOPY NUMBER
592 ,x_msg_data OUT NOCOPY VARCHAR2
593 )
594 IS
595 l_api_name VARCHAR2(200) :=' GET_EDW_SELECT_STRING';
596 --Commented out NOCOPY the TYPE declaration to not depend on OWB/EDW objects
597 l_lvlshortname VARCHAR2(32000);--CMPLEVEL_V.name%TYPE;
598 l_dimshortname VARCHAR2(32000);--CMPWBDIMENSION_V.name%TYPE;
599 l_dimname VARCHAR2(32000);--CMPWBDIMENSION_V.longname%TYPE;
600 l_dimdesc VARCHAR2(32000);--CMPWBDIMENSION_V.description%TYPE;
601 l_lvlname VARCHAR2(32000);--CMPLEVEL_V.longname%TYPE;
602 l_lvldesc VARCHAR2(32000);--CMPLEVEL_V.description%TYPE;
603 l_prefix VARCHAR2(32000);--CMPLEVEL_V.prefix%TYPE;
604 l_pkkey VARCHAR(2000);
605 l_valuename VARCHAR2(2000);
606 l_tablename VARCHAR2(2000);
607 l_distinct VARCHAR2(2000);
608 l_select_string VARCHAR2(32000);
609 l_temp_tablename VARCHAR2(32000);
610 l_sql_string VARCHAR2(32000);
611 l_lvlshortname_ltc VARCHAR2(32000);
612
613 l_dimshortname_time VARCHAR2(32000);
614 l_lvlshortname_total VARCHAR2(32000);
615 l_time_columns VARCHAR2(2000);
616 l_dim_lvl_sql VARCHAR2(32000);
617 l_pkkey_sql VARCHAR2(32000);
618 BEGIN
619 -- 2214178
620 l_dim_lvl_sql := 'SELECT dim.DIM_NAME dimshortname, dim.DIM_LONG_NAME dimname, dim.DIM_DESCRIPTION dimdesc'||
621 ',lvl.LEVEL_NAME lvlshortname, lvl.LEVEL_LONG_NAME lvlname, lvl.description lvldesc'||
622 ',lvl.LEVEL_PREFIX prefix'||
623 ' FROM '||
624 ' edw_dimensions_md_v dim, edw_levels_md_v lvl '||
625 ' WHERE '||
626 ' lvl.DIM_ID = dim.DIM_ID AND '||
627 ' lvl.LEVEL_NAME = :p_dim_level_name ';
628
629 BEGIN
630 EXECUTE IMMEDIATE l_dim_lvl_sql INTO
631 l_dimshortname, l_dimname,l_dimdesc, l_lvlshortname, l_lvlname, l_lvldesc, l_prefix
632 USING p_dim_level_name;
633 EXCEPTION WHEN NO_DATA_FOUND THEN
634 FND_MESSAGE.SET_NAME ('BIS','BIS_INVALID_LEVEL_SHORT_NAME');
635 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME',p_dim_level_name);
636 FND_MSG_PUB.ADD;
637 RAISE FND_API.G_EXC_ERROR;
638 END;
639 IF (BIS_PMF_GET_DIMLEVELS_PVT.isAccounting_Flexfield(l_lvlshortname))
640 THEN
641 l_pkkey := l_prefix||EDW_LVL_FLEX_PK_SUFFIX;
642 l_tablename := l_dimshortname;
643 l_distinct := ' DISTINCT ';
644 l_valuename := l_prefix||EDW_LVL_FLEX_PK_SUFFIX;
645 l_sql_string := 'SELECT '||l_pkkey||' from '||l_tablename|| ' where rownum < 2';
646 BEGIN
647 EXECUTE IMMEDIATE l_sql_string ;
648 EXCEPTION
649 WHEN OTHERS THEN
650 x_return_status := FND_API.G_RET_STS_ERROR;
651 IF (SQLCODE = -942) THEN
652 FND_MESSAGE.SET_NAME ('BIS', 'BIS_NO_LTC_TABLE');
653 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME', p_dim_level_name);
654 FND_MSG_PUB.ADD;
655 END IF;
656 IF (SQLCODE= -904) THEN
657 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_EDW_PK_KEY');
658 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME',p_dim_level_name);
659 FND_MSG_PUB.ADD;
660 END IF;
661 RAISE FND_API.G_EXC_ERROR;
662 FND_MSG_PUB.Count_And_Get
663 (p_count => x_msg_count,
664 p_data => x_msg_data
665 );
666 END;
667 ELSE
668 BEGIN
669 l_lvlshortname_ltc := l_lvlshortname || '_LTC';
670 l_pkkey_sql := ' SELECT level_table_col_name '||
671 ' FROM edw_level_Table_atts_md_v '||
672 ' WHERE key_type=''UK'' AND '||
673 ' upper(level_Table_name) = upper(:l_lvlshortname) AND '||
674 ' level_table_col_name like '''||EDW_PK_KEY||'''';
675
676 EXECUTE IMMEDIATE l_pkkey_sql INTO l_pkkey USING l_lvlshortname_ltc;
677 EXCEPTION
678 WHEN NO_DATA_FOUND THEN
679 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_EDW_PK_KEY');
680 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME',p_dim_level_name);
681 FND_MSG_PUB.ADD;
682 RAISE FND_API.G_EXC_ERROR;
683 END;
684 l_tablename := l_lvlshortname || EDW_LVL_TBL_SUFFIX ;
685 l_sql_string := 'SELECT '||l_pkkey||' from '||l_tablename|| ' where rownum < 2';
686 BEGIN
687 EXECUTE IMMEDIATE l_sql_string ;
688 EXCEPTION
689 WHEN OTHERS THEN
690 x_return_status := FND_API.G_RET_STS_ERROR;
691 IF (SQLCODE = -942) THEN
692 FND_MESSAGE.SET_NAME ('BIS', 'BIS_NO_LTC_TABLE');
693 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME', p_dim_level_name);
694 FND_MSG_PUB.ADD;
695 END IF;
696 IF (SQLCODE= -904) THEN
697 FND_MESSAGE.SET_NAME ('BIS', 'BIS_INVALID_EDW_PK_KEY');
698 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME', p_dim_level_name);
699 FND_MSG_PUB.ADD;
700 END IF;
701 RAISE FND_API.G_EXC_ERROR;
705 IF p_source IS NOT NULL THEN -- 2617369
702 END;
703 l_valuename := 'NAME';
704 END IF;
706 l_dimshortname_time := BIS_UTILITIES_PVT.GET_TIME_DIMENSION_NAME_SRC(p_source => p_source);
707 l_lvlshortname_total := BIS_UTILITIES_PVT.GET_TOTAL_DIMLEVEL_NAME_SRC(p_dim_short_name=>l_dimshortname
708 ,p_source => p_source
709 );
710 ELSE
711 l_dimshortname_time := BIS_UTILITIES_PVT.GET_TIME_DIMENSION_NAME(p_DimLevelId => NULL
712 ,p_DimLevelName =>l_lvlshortname
713 );
714 l_lvlshortname_total := BIS_UTILITIES_PVT.GET_TOTAL_DIMLEVEL_NAME(p_dim_short_name=>l_dimshortname
715 ,p_DimLevelId => NULL
716 ,p_DimLevelName => l_lvlshortname
717 );
718 END IF;
719 IF ((l_dimshortname = l_dimshortname_time) AND
720 (l_lvlshortname <> l_lvlshortname_total)
721 ) THEN
722 l_time_columns := ' ,start_date, end_date ';
723 x_time_level := 'Y';
724 l_sql_string := 'SELECT start_date from '||l_tablename||' where rownum < 2';
725 BEGIN
726 EXECUTE IMMEDIATE l_sql_string ;
727 EXCEPTION
728 WHEN OTHERS THEN
729 x_return_status := FND_API.G_RET_STS_ERROR;
730 IF (SQLCODE = -942) THEN
731 FND_MESSAGE.SET_NAME ('BIS', 'BIS_NO_LTC_TABLE');
732 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME', p_dim_level_name);
733 FND_MSG_PUB.ADD;
734 END IF;
735 IF (SQLCODE= -904) THEN
736 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_START_DATE');
737 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME',p_dim_level_name);
738 FND_MSG_PUB.ADD;
739 END IF;
740 RAISE FND_API.G_EXC_ERROR;
741 END;
742 l_sql_string := 'SELECT end_date from '||l_tablename||' where rownum < 2';
743 BEGIN
744 EXECUTE IMMEDIATE l_sql_string ;
745 EXCEPTION
746 WHEN OTHERS THEN
747 x_return_status := FND_API.G_RET_STS_ERROR;
748 IF (SQLCODE = -942) THEN
749 FND_MESSAGE.SET_NAME ('BIS', 'BIS_NO_LTC_TABLE');
750 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME', p_dim_level_name);
751 FND_MSG_PUB.ADD;
752 END IF;
753 IF (SQLCODE= -904) THEN
754 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_EDW_END_DATEY');
755 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME',p_dim_level_name);
756 FND_MSG_PUB.ADD;
757 END IF;
758 RAISE FND_API.G_EXC_ERROR;
759 END;
760
761 ELSE
762 l_time_columns := '';
763 END IF;
764 l_sql_string := 'SELECT '||l_valuename||' from '||l_tablename|| ' where rownum < 2';
765 BEGIN
766 EXECUTE IMMEDIATE l_sql_string ;
767 EXCEPTION
768 WHEN OTHERS THEN
769 x_return_status := FND_API.G_RET_STS_ERROR;
770 IF (SQLCODE = -942) THEN
771 FND_MESSAGE.SET_NAME ('BIS', 'BIS_NO_LTC_TABLE');
772 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME', p_dim_level_name);
773 FND_MSG_PUB.ADD;
774 END IF;
775 IF (SQLCODE= -904) THEN
776 FND_MESSAGE.SET_NAME ('BIS', 'BIS_INVALID_VALUE');
777 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME', p_dim_level_name);
778 FND_MSG_PUB.ADD;
779 END IF;
780 RAISE FND_API.G_EXC_ERROR;
781 END;
782
783 x_edw_select_string := 'select '||l_distinct||' '|| l_pkkey ||' id , ' ||
784 l_valuename ||' value '||l_time_columns|| ' FROM '|| l_tablename;
785 x_table_name := l_tablename;
786 x_id_name := l_pkkey;
787 x_value_name := l_valuename;
788 x_return_status := FND_API.G_RET_STS_SUCCESS;
789 FND_MSG_PUB.Count_And_Get
790 (p_count => x_msg_count,
791 p_data => x_msg_data
792 );
793
794
795 EXCEPTION
796 WHEN FND_API.G_EXC_ERROR THEN
797 x_return_status := FND_API.G_RET_STS_ERROR;
798 FND_MSG_PUB.Count_And_Get
799 ( p_count => x_msg_count,
800 p_data => x_msg_data
801 );
802 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
803 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
804 FND_MSG_PUB.Count_And_Get
805 ( p_count => x_msg_count,
806 p_data => x_msg_data
807 );
808 WHEN OTHERS THEN
809 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
810 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
811 THEN
812 FND_MSG_PUB.Add_Exc_Msg
813 (G_PKG_NAME,
814 l_api_name
815 );
816 END IF;
817 FND_MSG_PUB.Count_And_Get
818 (p_count => x_msg_count,
819 p_data => x_msg_data
820 );
821 END;
822 --
823 PROCEDURE GET_BIS_SELECT_STRING (
824 p_dim_level_name IN VARCHAR2
825 ,p_source IN VARCHAR2 := NULL -- 2617369
826 ,p_add_distinct IN VARCHAR2 := 'F'
830 ,x_bis_select_string OUT NOCOPY VARCHAR2
827 ,x_table_name OUT NOCOPY VARCHAR2
828 ,x_id_name OUT NOCOPY VARCHAR2
829 ,x_value_name OUT NOCOPY VARCHAR2
831 ,x_time_level OUT NOCOPY VARCHAR2
832 ,x_return_status OUT NOCOPY VARCHAR2
833 ,x_msg_count OUT NOCOPY NUMBER
834 ,x_msg_data OUT NOCOPY VARCHAR2
835 )
836 IS
837 l_parent_name VARCHAR2(100);
838 BEGIN
839
840 GET_BIS_SELECT_STRING (
841 p_dim_level_name => p_dim_level_name
842 ,p_source => p_source
843 ,p_add_distinct => p_add_distinct
844 ,x_table_name => x_table_name
845 ,x_id_name => x_id_name
846 ,x_value_name => x_value_name
847 ,x_parent_name => l_parent_name
848 ,x_bis_select_string => x_bis_select_string
849 ,x_time_level => x_time_level
850 ,x_return_status => x_return_status
851 ,x_msg_count => x_msg_count
852 ,x_msg_data => x_msg_data
853 );
854
855 END;
856
857
858 -- overloaded for parent name
859 PROCEDURE GET_BIS_SELECT_STRING
860 (p_dim_level_name IN VARCHAR2
861 ,p_source IN VARCHAR2 := NULL -- 2617369
862 ,p_add_distinct IN VARCHAR2 := 'F'
863 ,x_table_name OUT NOCOPY VARCHAR2
864 ,x_id_name OUT NOCOPY VARCHAR2
865 ,x_value_name OUT NOCOPY VARCHAR2
866 ,x_parent_name OUT NOCOPY VARCHAR2
867 ,x_bis_select_string OUT NOCOPY VARCHAR2
868 ,x_time_level OUT NOCOPY VARCHAR2
869 ,x_return_status OUT NOCOPY VARCHAR2
870 ,x_msg_count OUT NOCOPY NUMBER
871 ,x_msg_data OUT NOCOPY VARCHAR2
872 )
873 IS
874 CURSOR c_dimlvls IS
875 SELECT lvl.short_name, dim.short_name, lvl.level_values_view_name, bscdl.source, bscdl.level_view_name
876 FROM bis_levels lvl, bis_dimensions dim, bsc_sys_dim_levels_b bscdl
877 WHERE lvl.dimension_id = dim.dimension_id AND
878 (lvl.short_name = p_dim_level_name AND p_dim_level_name IS NOT NULL)
879 AND bscdl.short_name = lvl.short_name
880 ;
881 l_lvlshortname bis_levels.short_name%TYPE;
882 l_levelvalueview bis_levels.level_values_view_name%TYPE;
883 l_dimshortname bis_dimensions.short_name%TYPE;
884 l_time_columns VARCHAR2(2000);
885 l_Api_name VARCHAR2(200) := 'GET_BIS_SELECT_STRING';
886 l_Is_Rolling_Period_Level NUMBER; -- 2408906
887 l_dimshortname_time bis_dimensions.short_name%TYPE;
888 l_pmfBscSource VARCHAR2(30);
889 l_bscLevelViewName VARCHAR2(30);
890 l_id VARCHAR2(30) := 'ID';
891 l_value VARCHAR2(30) := 'VALUE';
892 BEGIN
893
894 OPEN c_dimlvls;
895 FETCH c_dimlvls INTO l_lvlshortname, l_dimshortname, l_levelvalueview, l_pmfBscSource, l_bscLevelViewName;
896 IF c_dimlvls%NOTFOUND THEN
897 FND_MESSAGE.SET_NAME('BIS', 'BIS_INVALID_LEVEL_SHORTNAME');
898 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME', p_dim_level_name);
899 FND_MSG_PUB.ADD;
900 RAISE FND_API.G_EXC_ERROR;
901 END IF;
902 CLOSE c_dimlvls;
903 l_Is_Rolling_Period_Level := BIS_UTILITIES_PVT.Is_Rolling_Period_Level( -- 2408906
904 p_level_short_name => l_lvlshortname);
905
906 IF ( l_Is_Rolling_Period_Level = 1 ) THEN -- 2408906
907
908 x_bis_select_string := ' select -1 Id, '
909 || ''''
910 || BIS_UTILITIES_PVT.GET_FND_MESSAGE( 'BIS_ALL_VALUE_ROLLING' )
911 || ''''
912 || ' Value, sysdate start_date, sysdate end_date from dual ' ;
913 -- ' select -1 Id, ' || '''All''' || ' Value from dual ' ;
914 -- ' select id, value, start_date, end_date from BIS_ROLLING_LEVEL_002 ' ;
915
916 x_table_name := 'dual'; -- 'BIS_ROLLING_LEVEL_002'; -- 'dual'; --
917 x_id_name := 'ID';
918 x_value_name := 'VALUE';
919 x_time_level := 'Y'; -- rolling period should not be checking if x_time_level is null
920 x_return_status := FND_API.G_RET_STS_SUCCESS;
921
922
923 ELSE
924
925 IF p_source IS NOT NULL THEN -- 2617369
926 l_dimshortname_time := BIS_UTILITIES_PVT.GET_TIME_DIMENSION_NAME_SRC(p_Source => p_source);
927 ELSE
928 l_dimshortname_time := BIS_UTILITIES_PVT.GET_TIME_DIMENSION_NAME(p_DimLevelId => NULL
929 , p_DimLevelName => l_lvlshortname);
930 END IF;
931
932 -- Added condition to check if it is a Periodicity time Dimension Object
933 IF ((l_dimshortname = l_dimshortname_time) OR
934 (BSC_UTILITY.Is_Dim_Object_Periodicity_Type(l_lvlshortname) = FND_API.G_TRUE)) THEN
935 l_time_columns := ' , start_date, end_date ';
936 x_time_level := 'Y';
937 ELSE
938 l_time_columns := '';
939 END IF;
940
941 IF ( IS_DISTINCT_USED (p_Dimension_Level_Short_Name => l_lvlshortname) AND (p_add_distinct = 'T') )THEN
942 x_bis_select_string := 'SELECT DISTINCT id , value '|| l_time_columns ||' FROM ' || l_levelvalueview;
943 ELSE
944 IF (l_pmfBscSource IS NOT NULL AND l_pmfBscSource = 'PMF') THEN
945 x_bis_select_string := 'SELECT id , value '|| l_time_columns ||' FROM ' || l_levelvalueview;
946 ELSE
947 l_levelvalueview := l_bscLevelViewName;
951 END IF;
948 l_id := 'CODE';
949 l_value := 'NAME';
950 x_bis_select_string := 'SELECT code , name '|| l_time_columns ||' FROM ' || l_bscLevelViewName;
952 END IF;
953
954 --------------- bug1906157
955 IF (l_dimshortname = 'GL COMPANY' and p_dim_level_name <> 'TOTAL GL COMPANIES')
956 THEN
957 x_parent_name := 'PARENT_ID'; -- check
958 x_bis_select_string := 'SELECT id , value , set_of_books_id '|| l_time_columns ||' FROM ' ||
959 l_levelvalueview;
960 END IF;
961 ----------
962
963 --------------- bug 2381087
964 IF (l_dimshortname = 'GL SECONDARY MEASURE' and p_dim_level_name <> 'TOTAL GL SECONDARY MEASURES')
965 THEN
966 x_parent_name := 'PARENT_ID'; -- check
967 x_bis_select_string := 'SELECT id , value , set_of_books_id '|| l_time_columns ||' FROM ' ||
968 l_levelvalueview;
969 END IF;
970 ----------
971
972 -- x_bis_select_string := 'SELECT id , value '|| l_time_columns ||' FROM ' ||
973 -- l_levelvalueview;
974 x_table_name := l_levelvalueview;
975 x_id_name := l_id;
976 x_value_name := l_value;
977 x_return_status := FND_API.G_RET_STS_SUCCESS;
978
979 FND_MSG_PUB.Count_And_Get
980 (p_count => x_msg_count,
981 p_data => x_msg_data
982 );
983
984 END IF;
985
986 EXCEPTION
987 WHEN FND_API.G_EXC_ERROR THEN
988 x_return_status := FND_API.G_RET_STS_ERROR;
989 FND_MSG_PUB.Count_And_Get
990 ( p_count => x_msg_count,
991 p_data => x_msg_data
992 );
993 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
994 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
995 FND_MSG_PUB.Count_And_Get
996 ( p_count => x_msg_count,
997 p_data => x_msg_data
998 );
999 WHEN NO_DATA_FOUND THEN
1000 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1001 FND_MSG_PUB.Count_And_Get
1002 (p_count => x_msg_count,
1003 p_data => x_msg_data
1004 );
1005 WHEN OTHERS THEN
1006 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1007 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1008 THEN
1009 FND_MSG_PUB.Add_Exc_Msg
1010 (G_PKG_NAME,
1011 l_api_name
1012 );
1013 END IF;
1014 FND_MSG_PUB.Count_And_Get
1015 (p_count => x_msg_count,
1016 p_data => x_msg_data
1017 );
1018 END;
1019 PROCEDURE GET_DIMLEVEL_VALUES_DATA
1020 (p_bis_dimlevel_id IN NUMBER
1021 ,x_dimlevel_short_name OUT NOCOPY VARCHAR2
1022 ,x_select_String OUT NOCOPY VARCHAR2
1023 ,x_table_name OUT NOCOPY VARCHAR2
1024 ,x_value_name OUT NOCOPY VARCHAR2
1025 ,x_id_name OUT NOCOPY VARCHAR2
1026 ,x_level_name OUT NOCOPY VARCHAR2
1027 ,x_description OUT NOCOPY VARCHAR2
1028 ,x_return_status OUT NOCOPY VARCHAR2
1029 ,x_msg_Count OUT NOCOPY NUMBER
1030 ,x_msg_data OUT NOCOPY VARCHAR2
1031 )
1032 IS
1033 l_api_name VARCHAR2(200) :=' GET_DIMLEVEL_SELECT_STRING';
1034 CURSOR c_dims IS
1035 SELECT source, name,description , short_name, level_values_view_name
1036 FROM bis_levels_vl
1037 WHERE level_id = p_bis_dimlevel_id
1038 ;
1039 l_short_name BIS_LEVELS_VL.SHORT_NAME%TYPE;
1040 l_source BIS_LEVELS_VL.SOURCE%TYPE;
1041 l_level_name BIS_LEVELS_VL.NAME%TYPE;
1042 l_description BIS_LEVELS_VL.DESCRIPTION%TYPE;
1043 l_time_level VARCHAR2(2001);
1044 l_level_values_view_name VARCHAR2(32000);
1045 BEGIN
1046 FND_MSG_PUB.initialize;
1047 OPEN c_dims;
1048 FETCH c_dims INTO l_Source,l_level_name, l_description,l_short_name,l_level_values_view_name;
1049 IF (c_dims%NOTFOUND) THEN
1050 FND_MESSAGE.SET_NAME('BIS','BIS_INAVLID_LEVEL_SHORT_NAME');
1051 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME', l_short_name);
1052 FND_MSG_PUB.ADD;
1053 RAISE FND_API.G_EXC_ERROR;
1054 END IF;
1055 CLOSE c_dims;
1056 --IF (fnd_profile.value('BIS_SOURCE') = 'EDW')
1057 IF ((l_source = 'EDW') AND (l_level_Values_view_name IS NULL))
1058 THEN
1059 BIS_PMF_GET_DIMLEVELS_PVT.GET_EDW_SELECT_STRING
1060 (p_dim_level_name => l_Short_name
1061 ,p_source => l_source
1062 ,x_table_name => x_table_name
1063 ,x_id_name => x_id_name
1064 ,x_value_name => x_value_name
1065 ,x_edw_select_String => x_Select_String
1066 ,x_time_level => l_time_level
1067 ,x_return_status => x_return_status
1068 ,x_msg_count => x_msg_count
1069 ,x_msg_data => x_msg_data
1070 );
1071 ELSE
1072 BIS_PMF_GET_DIMLEVELS_PVT.GET_BIS_SELECT_STRING
1073 (p_dim_level_name => l_short_name
1074 ,p_source => l_source
1075 ,x_table_name => x_table_name
1076 ,x_id_name => x_id_name
1077 ,x_value_name => x_value_name
1078 ,x_bis_select_string => x_select_string
1079 ,x_time_level => l_time_level
1080 ,x_return_status => x_return_status
1081 ,x_msg_count => x_msg_count
1085 x_level_name := l_level_name;
1082 ,x_msg_data => x_msg_data
1083 );
1084 END IF;
1086 x_Description := l_description;
1087 x_dimlevel_short_name := l_short_name;
1088 EXCEPTION
1089 WHEN FND_API.G_EXC_ERROR THEN
1090 x_return_status := FND_API.G_RET_STS_ERROR;
1091 FND_MSG_PUB.Count_And_Get
1092 ( p_count => x_msg_count,
1093 p_data => x_msg_data
1094 );
1095
1096 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1097 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1098 FND_MSG_PUB.Count_And_Get
1099 ( p_count => x_msg_count,
1100 p_data => x_msg_data
1101 );
1102 WHEN OTHERS THEN
1103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1104 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1105 THEN
1106 FND_MSG_PUB.Add_Exc_Msg
1107 ( G_PKG_NAME,
1108 l_api_name
1109 );
1110 END IF;
1111 FND_MSG_PUB.Count_And_Get
1112 ( p_count => x_msg_count,
1113 p_data => x_msg_data
1114 );
1115 END;
1116 -- Fix for 2668693
1117 FUNCTION IS_DISTINCT_USED
1118 (p_Dimension_Level_Short_Name IN bis_levels.short_name%TYPE
1119 ) RETURN BOOLEAN IS
1120 BEGIN
1121
1122 FOR i IN 1..Dlist.Count LOOP
1123 IF (Dlist(i) = p_Dimension_Level_Short_Name) THEN
1124 RETURN TRUE;
1125 END IF;
1126 END LOOP;
1127 RETURN FALSE;
1128
1129 EXCEPTION
1130 WHEN OTHERS THEN
1131 RETURN FALSE;
1132 END IS_DISTINCT_USED ;
1133
1134 -- procedure to Store the values into the Global variable
1135 PROCEDURE cache_dim_lvl_Select_info
1136 (p_DimLevelSName IN VARCHAR2
1137 ,p_Select_String IN VARCHAR2
1138 ,p_table_name IN VARCHAR2
1139 ,p_id_name IN VARCHAR2
1140 ,p_value_name IN VARCHAR2
1141 ,p_parent_name IN VARCHAR2
1142 ,p_time_level IN VARCHAR2
1143 ) IS
1144 BEGIN
1145
1146 -- Store the values into the Global variable
1147 G_DIM_LEVEL_SELECT_INFO_REC.Dim_level_sname := p_DimLevelSName ;
1148 G_DIM_LEVEL_SELECT_INFO_REC.Table_name := p_table_name ;
1149 G_DIM_LEVEL_SELECT_INFO_REC.Id_name := p_id_name ;
1150 G_DIM_LEVEL_SELECT_INFO_REC.Value_name := p_value_name ;
1151 G_DIM_LEVEL_SELECT_INFO_REC.parent_name := p_parent_name ;
1152 G_DIM_LEVEL_SELECT_INFO_REC.Select_String := p_select_String ;
1153 G_DIM_LEVEL_SELECT_INFO_REC.Time_level := p_time_level ;
1154
1155 EXCEPTION
1156 WHEN OTHERS THEN
1157 NULL;
1158 END cache_dim_lvl_Select_info;
1159
1160 -- Private function to see if the dim level info is already cached
1161 -- if so return the values from the global variable
1162 FUNCTION IS_DIM_LVL_INFO_CACHED
1163 (p_DimLevelSName IN bis_levels.short_name%TYPE
1164 ,x_Select_String OUT NOCOPY VARCHAR2
1165 ,x_table_name OUT NOCOPY VARCHAR2
1166 ,x_id_name OUT NOCOPY VARCHAR2
1167 ,x_value_name OUT NOCOPY VARCHAR2
1168 ,x_parent_name OUT NOCOPY VARCHAR2
1169 ,x_time_level OUT NOCOPY VARCHAR2
1170 ) RETURN BOOLEAN IS
1171 BEGIN
1172
1173 IF ( UPPER(G_DIM_LEVEL_SELECT_INFO_REC.Dim_level_sname) = UPPER(p_DimLevelSName) ) THEN
1174 x_table_name := G_DIM_LEVEL_SELECT_INFO_REC.Table_name ;
1175 x_id_name := G_DIM_LEVEL_SELECT_INFO_REC.Id_name ;
1176 x_value_name := G_DIM_LEVEL_SELECT_INFO_REC.Value_name ;
1177 x_parent_name := G_DIM_LEVEL_SELECT_INFO_REC.Parent_name ;
1178 x_Select_String := G_DIM_LEVEL_SELECT_INFO_REC.Select_String ;
1179 x_time_level := G_DIM_LEVEL_SELECT_INFO_REC.Time_level ;
1180
1181 RETURN TRUE;
1182 END IF;
1183
1184 RETURN FALSE;
1185
1186 EXCEPTION
1187 WHEN OTHERS THEN
1188 RETURN FALSE;
1189 END IS_DIM_LVL_INFO_CACHED ;
1190
1191
1192
1193 --=======================================================================
1194 --=======================================================================
1195 PROCEDURE get_dimlevel_select_wrap (
1196 p_dimLevel IN VARCHAR2
1197 ,p_paramlist IN VARCHAR2
1198 ,p_bis_source IN VARCHAR2
1199 ,p_region_code IN ak_regions.region_code%TYPE
1200 ,x_select_string OUT NOCOPY VARCHAR2
1201 ,x_table_name OUT NOCOPY VARCHAR2
1202 ,x_where_clause OUT NOCOPY VARCHAR2
1203 ,x_bind_param_string OUT NOCOPY VARCHAR2
1204 ,x_id_name OUT NOCOPY VARCHAR2
1205 ,x_value_name OUT NOCOPY VARCHAR2
1206 ,x_parent_name OUT NOCOPY VARCHAR2
1207 ,x_time_level OUT NOCOPY VARCHAR2
1208 ,x_is_relation_recursive OUT NOCOPY VARCHAR2
1209 ,x_return_status OUT NOCOPY VARCHAR2
1210 ,x_msg_count OUT NOCOPY NUMBER
1211 ,x_msg_data OUT NOCOPY VARCHAR2
1212 )
1213 IS
1214 l_dimlevelrec BIS_PMF_GET_DIMLEVELS_PUB.dimlvl_rec_Type;
1215 l_parlevelrec BIS_PMF_GET_DIMLEVELS_PUB.dimlvl_rec_Type;
1216 l_dimleveltbl BIS_PMF_GET_DIMLEVELS_PUB.dimlvl_tbl_Type;
1220 -- l_time_level VARCHAR2(300);
1217 l_bind_params BIS_PMF_QUERY_ATTRIBUTES_TABLE;
1218 l_count NUMBER;
1219 -- l_parent_name VARCHAR2(300);
1221 l_param_list VARCHAR2(32000);
1222 l_index PLS_INTEGER;
1223 err_msg VARCHAR2(32000);
1224 BEGIN
1225 IF (p_dimlevel IS NOT NULL) THEN
1226 l_dimlevelrec.dimension_short_name := SUBSTR(p_dimlevel, 1, INSTR (p_dimlevel, '^', 1, 1)-1);
1227 l_dimlevelrec.dimension_level_short_name := SUBSTR(p_dimlevel, INSTR (p_dimlevel, '^', 1, 1)+1, INSTR (p_dimlevel, '^', 1, 2) - INSTR (p_dimlevel, '^', 1, 1) -1);
1228 l_dimlevelrec.dimension_level_value_id := SUBSTR(p_dimlevel, INSTR (p_dimlevel, '^', 1, 2)+1, INSTR (p_dimlevel, '^', 1, 3) - INSTR (p_dimlevel, '^', 1, 2) -1);
1229 l_dimlevelrec.ri_attribute_code := SUBSTR(p_dimlevel, INSTR (p_dimlevel, '^', 1, 3)+1);
1230 -- put p_dimlevel string into p_dimlevelrecord
1231 END IF;
1232
1233 IF (p_paramlist IS NOT NULL) THEN
1234 l_count := 1;
1235 l_param_list := p_paramlist;
1236
1237 l_index := INSTR (l_param_list, '~*');
1238 WHILE (l_index > 0 ) LOOP
1239
1240 l_parlevelrec.dimension_short_name := SUBSTR(l_param_list, 1, (INSTR (l_param_list, '^', 1, 1)-1));
1241 l_parlevelrec.dimension_level_short_name := SUBSTR(l_param_list, (INSTR (l_param_list, '^', 1, 1)+1), (INSTR (l_param_list, '^', 1, 2) - INSTR (l_param_list, '^', 1, 1) -1));
1242 l_parlevelrec.dimension_level_value_id := SUBSTR(l_param_list, (INSTR (l_param_list, '^', 1, 2)+1), (INSTR (l_param_list, '^', 1, 3) - INSTR (l_param_list, '^', 1, 2) -1));
1243 l_parlevelrec.ri_attribute_code := SUBSTR(l_param_list, (INSTR (l_param_list, '^', 1, 3)+1),
1244 l_index - (INSTR (l_param_list, '^', 1, 3)) - 1);
1245 l_dimleveltbl(l_count) := l_parlevelrec;
1246 l_param_list := substr (l_param_list , l_index+2, length(l_param_list));
1247 l_index := INSTR (l_param_list, '~*');
1248 l_count := l_count+1;
1249
1250 END LOOP;
1251
1252 l_parlevelrec.dimension_short_name := SUBSTR(l_param_list, 1, (INSTR (l_param_list, '^', 1, 1)-1));
1253 l_parlevelrec.dimension_level_short_name := SUBSTR(l_param_list, (INSTR (l_param_list, '^', 1, 1)+1), (INSTR (l_param_list, '^', 1, 2) - INSTR (l_param_list, '^', 1, 1) -1));
1254 l_parlevelrec.dimension_level_value_id := SUBSTR(l_param_list, (INSTR (l_param_list, '^', 1, 2)+1), (INSTR (l_param_list, '^', 1, 3) - INSTR (l_param_list, '^', 1, 2) -1));
1255 l_parlevelrec.ri_attribute_code := SUBSTR(l_param_list, (INSTR (l_param_list, '^', 1, 3)+1),
1256 (LENGTH(l_param_list)) - (INSTR (l_param_list, '^', 1, 3)));
1257
1258 l_dimleveltbl(l_count) := l_parlevelrec;
1259
1260 -- put p_paramlist string into p_paramlist table
1261 END IF;
1262
1263 get_dimlevel_select_string(
1264 p_dimlevel_rec => l_dimlevelrec
1265 ,p_param_lists_tbl => l_dimleveltbl
1266 ,p_bis_source => p_bis_source
1267 ,x_select_string => x_select_string
1268 ,x_table_name => x_table_name
1269 ,x_where_clause => x_where_clause
1270 ,x_bind_params => l_bind_params
1271 ,x_id_name => x_id_name
1272 ,x_value_name => x_value_name
1273 ,x_parent_name => x_parent_name
1274 ,x_time_level => x_time_level
1275 ,x_is_relation_recursive => x_is_relation_recursive
1276 ,x_return_status => x_return_status
1277 ,x_msg_count => x_msg_count
1278 ,x_msg_data => x_msg_data
1279 ,p_region_code => p_region_code
1280 );
1281
1282 IF ( l_bind_params IS NOT NULL AND l_bind_params.COUNT > 0) THEN
1283 FOR i IN l_bind_params.FIRST..l_bind_params.LAST LOOP
1284 x_bind_param_string := x_bind_param_string || l_bind_params(i).attribute_name|| '^^';
1285 x_bind_param_string := x_bind_param_string || l_bind_params(i).attribute_value|| '^^';
1286 x_bind_param_string := x_bind_param_string || l_bind_params(i).attribute_data_type;
1287 IF (i <> l_bind_params.LAST) THEN
1288 x_bind_param_string := x_bind_param_string || '~*';
1289 END IF;
1290 END LOOP;
1291 END IF;
1292
1293 -- Here put l_bind_params into x_bind_param_string with ~* as separators for each record
1294 EXCEPTION
1295 WHEN OTHERS THEN
1296 null;
1297 err_msg := SQLERRM;
1298 END GET_DIMLEVEL_SELECT_WRAP;
1299
1300 --================================================================
1301
1302 PROCEDURE get_dimlevel_select_string(
1303 p_dimlevel_rec IN BIS_PMF_GET_DIMLEVELS_PUB.dimlvl_rec_Type
1304 ,p_param_lists_tbl IN BIS_PMF_GET_DIMLEVELS_PUB.dimlvl_tbl_Type
1305 ,p_bis_source IN bis_levels.source%TYPE := NULL
1306 ,p_region_code IN ak_regions.region_code%TYPE
1307 ,x_select_string OUT NOCOPY VARCHAR2
1308 ,x_table_name OUT NOCOPY VARCHAR2
1309 ,x_where_clause OUT NOCOPY VARCHAR2
1310 ,x_bind_params OUT NOCOPY BIS_PMF_QUERY_ATTRIBUTES_TABLE
1311 ,x_id_name OUT NOCOPY VARCHAR2
1312 ,x_value_name OUT NOCOPY VARCHAR2
1313 ,x_parent_name OUT NOCOPY VARCHAR2
1314 ,x_time_level OUT NOCOPY VARCHAR2
1315 ,x_is_relation_recursive OUT NOCOPY VARCHAR2
1316 ,x_return_status OUT NOCOPY VARCHAR2
1317 ,x_msg_count OUT NOCOPY NUMBER
1318 ,x_msg_data OUT NOCOPY VARCHAR2
1322 l_is_relationship_found VARCHAR2(10);
1319 )
1320 IS
1321 l_bis_source bis_levels.source%TYPE;
1323 l_dim_level_short_name VARCHAR2(80);
1324 l_dim_rel_info_rec bis_pmf_get_dimlevels_pub.dim_rel_info_rec;
1325 l_is_pmf_bsc_source BSC_SYS_DIM_LEVELS_B.SOURCE%TYPE;
1326 l_dim_un_rel_info_rec bis_pmf_get_dimlevels_pub.dim_rel_info_rec;
1327 BEGIN
1328 x_return_status := FND_API.G_RET_STS_SUCCESS;
1329 l_bis_source := p_bis_source;
1330 IF (l_bis_source IS NULL) THEN
1331 l_bis_source := get_source(p_dim_level_short_name => l_dim_level_short_name);
1332 END IF;
1333
1334 get_relationship_details(
1335 p_child_dimlvl_rec => p_dimlevel_rec
1336 ,p_parent_dimlvl_tbl => p_param_lists_tbl -- p_parent_dimlvl_tbl
1337 ,x_is_relation_recursive => x_is_relation_recursive
1338 ,x_is_relationship_found => l_is_relationship_found
1339 ,x_dim_rel_info_rec => l_dim_rel_info_rec
1340 ,x_return_status => x_return_status
1341 ,x_msg_count => x_msg_count
1342 ,x_msg_data => x_msg_data
1343 );
1344
1345
1346 IF ( (l_is_relationship_found = FND_API.G_FALSE)) THEN
1347 -- Since relationship data not present in bsc data model, call existing code.
1348 l_is_pmf_bsc_source := get_dim_level_source (p_dimlevel_rec.dimension_level_short_name);
1349 IF (l_is_pmf_bsc_source = 'PMF') THEN
1350 get_dimlevel_select_string(
1351 p_DimLevelName => p_dimlevel_rec.dimension_level_short_name
1352 ,p_bis_source => l_bis_source
1353 ,x_Select_String => x_select_string
1354 ,x_table_name => x_table_name
1355 ,x_id_name => x_id_name
1356 ,x_value_name => x_value_name
1357 ,x_parent_name => x_parent_name
1358 ,x_time_level => x_time_level
1359 ,x_return_status => x_return_status
1360 ,x_msg_count => x_msg_count
1361 ,x_msg_data => x_msg_data
1362 );
1363 ELSE -- BSC type when there are no relationships
1364 l_dim_un_rel_info_rec.dimension_short_name := p_dimlevel_rec.dimension_short_name;
1365 l_dim_un_rel_info_rec.dimension_level_short_name := p_dimlevel_rec.dimension_level_short_name;
1366 -- This API will return the source, id, value, sql, etc as id, value (without parent) since
1367 -- the l_dim_un_rel_info_rec does not pass in the parent info + relation info.
1368 get_bsc_data_source(
1369 p_dim_rel_info_rec => l_dim_un_rel_info_rec
1370 ,x_id_name => x_id_name
1371 ,x_value_name => x_value_name
1372 ,x_parent_name => x_parent_name
1373 ,x_select_string => x_select_string
1374 ,x_data_source => x_table_name
1375 ,x_return_status => x_return_status
1376 ,x_msg_count => x_msg_count
1377 ,x_msg_data => x_msg_data
1378 ,p_region_code => p_region_code
1379 );
1380 END IF;
1381 ELSIF ( (l_is_relationship_found = FND_API.G_TRUE) AND (l_dim_rel_info_rec.relation_col IS NULL) ) THEN
1382 -- throw exception if relation found but relation column is null
1383 FND_MESSAGE.SET_NAME('BIS','BIS_PMF_REL_COL_NULL');
1384 FND_MSG_PUB.ADD;
1385 RAISE FND_API.G_EXC_ERROR;
1386 ELSE -- relationship is found - both BIS and BSC is handled by this API.
1387 get_select_string(
1388 p_bis_source => l_bis_source
1389 ,p_is_relation_recursive => x_is_relation_recursive
1390 ,p_is_relationship_found => l_is_relationship_found
1391 ,p_dim_rel_info_rec => l_dim_rel_info_rec
1392 ,x_select_string => x_select_string
1393 ,x_bind_params => x_bind_params-- (attribute_value, attribute_data_type)
1394 ,x_where_clause => x_where_clause
1395 ,x_data_source => x_table_name
1396 ,x_id_name => x_id_name
1397 ,x_value_name => x_value_name
1398 ,x_parent_name => x_parent_name
1399 ,x_time_level => x_time_level
1400 ,x_return_status => x_return_status
1401 ,x_msg_count => x_msg_count
1402 ,x_msg_data => x_msg_data
1403 ,p_region_code => p_region_code
1404
1405 );
1406 END IF;
1407 EXCEPTION
1408 WHEN FND_API.G_EXC_ERROR THEN
1409 x_return_status := FND_API.G_RET_STS_ERROR;
1410 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1411 ,p_data => x_msg_data);
1412 WHEN OTHERS THEN
1413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1414 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1415 ,p_data => x_msg_data);
1416 END get_dimlevel_select_string;
1417
1418 --==================================================================
1419 FUNCTION INITIALIZE_QUERY_TYPE
1420 RETURN BIS_PMF_QUERY_ATTRIBUTES
1421 IS
1422 l_query_attributes BIS_PMF_QUERY_ATTRIBUTES := BIS_PMF_QUERY_ATTRIBUTES(null,null,null);
1423 BEGIN
1424 RETURN l_query_attributes;
1425 END INITIALIZE_QUERY_TYPE;
1426
1427 --===================================================================
1428 -- It is assumed that "distinct" and "set_of_books_id" (hardcode for GL Sec Measure), etc in
1429 -- the apis above will not be done below, since the relationship select
1430 -- and where clause will handle this
1431 PROCEDURE get_select_string (
1432 p_bis_source IN VARCHAR2
1433 ,p_is_relation_recursive IN VARCHAR2
1434 ,p_is_relationship_found IN VARCHAR2
1438 ,x_bind_params OUT NOCOPY BIS_PMF_QUERY_ATTRIBUTES_TABLE-- (attribute_value, attribute_data_type)
1435 ,p_dim_rel_info_rec IN bis_pmf_get_dimlevels_pub.dim_rel_info_rec
1436 ,p_region_code IN ak_regions.region_code%TYPE
1437 ,x_select_string OUT NOCOPY VARCHAR2
1439 ,x_where_clause OUT NOCOPY VARCHAR2
1440 ,x_data_source OUT NOCOPY VARCHAR2
1441 ,x_id_name OUT NOCOPY VARCHAR2
1442 ,x_value_name OUT NOCOPY VARCHAR2
1443 ,x_parent_name OUT NOCOPY VARCHAR2
1444 ,x_time_level OUT NOCOPY VARCHAR2
1445 ,x_return_status OUT NOCOPY VARCHAR2
1446 ,x_msg_count OUT NOCOPY NUMBER
1447 ,x_msg_data OUT NOCOPY VARCHAR2
1448 )
1449 IS
1450 l_is_pmf_bsc_source BSC_SYS_DIM_LEVELS_B.SOURCE%TYPE;
1451 BEGIN
1452 x_return_status := FND_API.G_RET_STS_SUCCESS;
1453
1454 l_is_pmf_bsc_source := get_dim_level_source (p_dim_rel_info_rec.dimension_level_short_name);
1455
1456 IF (l_is_pmf_bsc_source = 'PMF') THEN
1457 get_pmf_data_source(
1458 p_bis_source => p_bis_source
1459 ,p_dim_rel_info_rec => p_dim_rel_info_rec
1460 ,p_is_relation_recursive => p_is_relation_recursive
1461 ,x_select_string => x_select_string
1462 ,x_bind_params => x_bind_params
1463 ,x_data_source => x_data_source
1464 ,x_id_name => x_id_name
1465 ,x_value_name => x_value_name
1466 ,x_parent_name => x_parent_name
1467 ,x_time_level => x_time_level
1468 ,x_return_status => x_return_status
1469 ,x_msg_count => x_msg_count
1470 ,x_msg_data => x_msg_data
1471 );
1472 ELSE
1473 get_bsc_data_source(
1474 p_dim_rel_info_rec => p_dim_rel_info_rec
1475 ,x_Id_Name => x_Id_Name
1476 ,x_Value_Name => x_Value_Name
1477 ,x_parent_name => x_parent_name
1478 ,x_select_string => x_select_string
1479 ,x_data_source => x_data_source
1480 ,x_return_status => x_return_status
1481 ,x_msg_count => x_msg_count
1482 ,x_msg_data => x_msg_data
1483 ,p_region_code => p_region_code
1484 );
1485 END IF;
1486 -- the source will alias the actual parent column with parent_id always.
1487 -- if ALL is selected in the parent, then do not filter the child dim level values.
1488 -- Always use "IN" and not "=", so both multi-select and single select is taken care of.
1489
1490 IF (p_dim_rel_info_rec.parent_ri_attribute_code IS NOT NULL) THEN
1491 IF (is_append_where_clause(p_dim_rel_info_rec => p_dim_rel_info_rec, p_is_relation_recursive => p_is_relation_recursive)) THEN
1492 IF (p_is_relation_recursive IS NOT NULL AND p_is_relation_recursive = FND_API.G_TRUE) THEN
1493 -- recursive relationship should bring itself, its children and its parent.
1494 x_where_clause := ' AND (( ''ALL'' IN ({' || p_dim_rel_info_rec.parent_ri_attribute_code || '}) OR ' || x_parent_name || ' IN ({' || p_dim_rel_info_rec.parent_ri_attribute_code || '}))' ||
1495 ' OR ( ' || x_id_name || ' IN ({' || p_dim_rel_info_rec.parent_ri_attribute_code || '}))' ||
1496 ' OR ( ' || x_id_name || ' IN (SELECT ' || x_parent_name || ' FROM ' || x_data_source || ' WHERE ' || x_id_name || ' IN ({' || p_dim_rel_info_rec.parent_ri_attribute_code || '}))))';
1497 ELSE
1498 x_where_clause := ' AND ( ''ALL'' IN ({' || p_dim_rel_info_rec.parent_ri_attribute_code || '}) OR ' || x_parent_name || ' IN ({' || p_dim_rel_info_rec.parent_ri_attribute_code || '}))';
1499 END IF;
1500 END IF;
1501 END IF;
1502
1503 x_return_status := FND_API.G_RET_STS_SUCCESS;
1504
1505 EXCEPTION
1506 WHEN FND_API.G_EXC_ERROR THEN
1507 x_return_status := FND_API.G_RET_STS_ERROR;
1508 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1509 ,p_data => x_msg_data);
1510
1511 WHEN OTHERS THEN
1512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1513 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1514 ,p_data => x_msg_data);
1515 END get_select_string;
1516
1517 --================================================================
1518 PROCEDURE get_pmf_data_source(
1519 p_bis_source IN VARCHAR2
1520 ,p_dim_rel_info_rec IN bis_pmf_get_dimlevels_pub.dim_rel_info_rec
1521 ,p_is_relation_recursive IN VARCHAR2
1522 ,x_select_string OUT NOCOPY VARCHAR2
1523 ,x_bind_params OUT NOCOPY BIS_PMF_QUERY_ATTRIBUTES_TABLE
1524 ,x_data_source OUT NOCOPY VARCHAR2
1525 ,x_id_name OUT NOCOPY VARCHAR2
1526 ,x_value_name OUT NOCOPY VARCHAR2
1527 ,x_parent_name OUT NOCOPY VARCHAR2
1528 ,x_time_level OUT NOCOPY VARCHAR2
1529 ,x_return_status OUT NOCOPY VARCHAR2
1530 ,x_msg_count OUT NOCOPY NUMBER
1531 ,x_msg_data OUT NOCOPY VARCHAR2
1532 )
1533 IS
1534 l_data_source BIS_LEVELS.level_values_view_name%TYPE;
1535 l_dim_short_name VARCHAR2(80);
1536 l_dimlvl_short_name VARCHAR2(80);
1537 l_parent_dim_short_name VARCHAR2(80);
1538 l_parent_dimlvl_short_name VARCHAR2(80);
1539 l_parent_lvl_value_id VARCHAR2(32000);
1540 l_relation_col VARCHAR2(80);
1541 l_relation_col_in_select VARCHAR2(300);
1542 l_time_columns VARCHAR2(100);
1543
1544 BEGIN
1545 l_dim_short_name := p_dim_rel_info_rec.dimension_short_name ;
1546 l_dimlvl_short_name := p_dim_rel_info_rec.dimension_level_short_name ;
1547 l_parent_dim_short_name := p_dim_rel_info_rec.parent_dimension_short_name ;
1548 l_parent_dimlvl_short_name := p_dim_rel_info_rec.parent_level_short_name ;
1549 l_parent_lvl_value_id := p_dim_rel_info_rec.parent_dim_level_value_id;
1550 l_relation_col := p_dim_rel_info_rec.relation_col;
1551 -- EDW may not be needed anymore since EDW dim levels do not show up on the UI
1552 -- and hence relationships cannot be defined for them.
1553
1554 get_oltp_edw_cols (
1555 p_Dim_Level_Short_Name => p_dim_rel_info_rec.dimension_level_short_name
1556 ,p_Source => p_bis_source
1557 ,x_Table_Name => l_data_source
1558 ,x_Id_Name => x_Id_Name
1559 ,x_Value_Name => x_Value_Name
1560 ,x_return_status => x_return_status
1561 ,x_msg_count => x_msg_count
1562 ,x_msg_data => x_msg_data
1563 );
1564
1565 IF ( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1566 RAISE FND_API.G_EXC_ERROR;
1567 END IF;
1568
1569 IF ( needs_time_cols(--check if this is fine for both edw and oltp
1570 p_source => p_bis_source
1571 ,p_data_source => l_data_source
1572 ,p_lvlshortname => l_dimlvl_short_name
1573 ,p_dimshortname => l_dim_short_name
1574 )) THEN
1575 l_time_columns := ' ,start_date, end_date ';
1576 x_time_level := FND_API.G_TRUE;
1577 END IF;
1578
1579 /* Removed code for get_binds() for enh# 4456833 - not needed now - binds etc are not used. No values are passed
1580 into this API but keeping in the original API signature in DimLevelUtil for backward compatibility.
1581 */
1582 -- PMV does not need the parent_id column in the select statement unless it is recursive - bug# 4914929.
1583 IF (p_is_relation_recursive IS NOT NULL AND p_is_relation_recursive = FND_API.G_TRUE) THEN
1584 l_relation_col_in_select := ', ' || l_relation_col || ' as parent_id ';
1585 END IF;
1586 x_select_string := 'SELECT '|| x_Id_Name ||' , '|| x_Value_Name || l_relation_col_in_select || l_time_columns || ' FROM '|| l_data_source;
1587 x_data_source := l_data_source ;
1588 x_parent_name := l_relation_col; -- 'PARENT_ID'; -- parent_id is the alias always.
1589
1590
1591 EXCEPTION
1592 WHEN FND_API.G_EXC_ERROR THEN
1593 x_return_status := FND_API.G_RET_STS_ERROR;
1594 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1595 ,p_data => x_msg_data);
1596 WHEN OTHERS THEN
1597 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1598 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1599 ,p_data => x_msg_data);
1600 END get_pmf_data_source;
1601
1602 --================================================================
1603 PROCEDURE get_bsc_data_source(
1604 p_dim_rel_info_rec IN bis_pmf_get_dimlevels_pub.dim_rel_info_rec
1605 ,p_region_code IN ak_regions.region_code%TYPE
1606 ,x_id_name OUT NOCOPY VARCHAR2
1607 ,x_value_name OUT NOCOPY VARCHAR2
1608 ,x_parent_name OUT NOCOPY VARCHAR2
1609 ,x_select_string OUT NOCOPY VARCHAR2
1610 ,x_data_source OUT NOCOPY VARCHAR2
1611 ,x_return_status OUT NOCOPY VARCHAR2
1612 ,x_msg_count OUT NOCOPY NUMBER
1613 ,x_msg_data OUT NOCOPY VARCHAR2
1614 )
1615 IS
1616
1617 l_relation_type BSC_SYS_DIM_LEVEL_RELS.RELATION_TYPE%TYPE;
1618 l_child_level_pk_col BSC_SYS_DIM_LEVELS_B.LEVEL_PK_COL%TYPE;
1619 l_child_level_view_name BSC_SYS_DIM_LEVELS_B.LEVEL_VIEW_NAME%TYPE;
1620 l_parent_level_pk_col BSC_SYS_DIM_LEVELS_B.LEVEL_PK_COL%TYPE;
1621 l_parent_level_view_name BSC_SYS_DIM_LEVELS_B.LEVEL_VIEW_NAME%TYPE;
1622 l_restrict_all_value VARCHAR2(100);
1623
1624 BEGIN
1625
1626 l_relation_type := p_dim_rel_info_rec.relation_type;
1627
1628 BIS_PMF_GET_DIMLEVELS_PVT.Get_Bsc_Dim_Obj_details
1629 (
1630 p_dim_rel_info_rec => p_dim_rel_info_rec
1631 ,p_region_code => p_region_code
1632 ,x_child_level_pk_col => l_child_level_pk_col
1633 ,x_child_level_view_name => l_child_level_view_name
1634 ,x_parent_level_pk_col => l_parent_level_pk_col
1635 ,x_parent_level_view_name => l_parent_level_view_name
1636 ,x_return_status => x_return_status
1637 ,x_msg_count => x_msg_count
1638 ,x_msg_data => x_msg_data
1639 );
1640 IF(x_return_status <>FND_API.G_RET_STS_SUCCESS) THEN
1641 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1645 * In BSC views (level_view_name), All is always present with code as 0. This needs to be filtered out so that
1642 END IF;
1643 /* PMV appends "All" based on All enabled/disabled (bsc_sys_dim_levels_by_group.total_flag.
1644 * In PMF views (level_values_view_name), All is not present (or restricted by product team where clause).
1646 * PMV can continue the same logic irrespective of a PMF or BSC dim level.
1647 */
1648 l_restrict_all_value := ' a.code <> 0';
1649 -- if it is a 1 X n relationship or not related (no entries in bsc_sys_dim_level_rels)
1650 IF (l_relation_type IS NULL OR l_relation_type = 1) THEN
1651 IF (p_dim_rel_info_rec.relation_col IS NOT NULL) THEN -- entry in bsc_sys_dim_level_rels.
1652 x_data_source := ' (SELECT a.code id, a.name value, ' || p_dim_rel_info_rec.relation_col || ' parent_id FROM '|| l_child_level_view_name || ' a WHERE ' || l_restrict_all_value || ')';
1653 x_select_string := 'SELECT id, value FROM ' || x_data_source;
1654 x_parent_name := 'PARENT_ID';
1655 ELSE -- only for a un-related BSC dim level - to prepare the data source similar to BIS.
1656 x_data_source := ' (SELECT a.code id, a.name value FROM '|| l_child_level_view_name || ' a WHERE ' || l_restrict_all_value || ')';
1657 x_select_string := 'SELECT id, value FROM ' || x_data_source;
1658 END IF;
1659 ELSE -- l_relation_type = 2 - if it is a m X n relationship
1660 x_data_source := ' (SELECT a.code id, a.name value, c.' || l_parent_level_pk_col || ' parent_id FROM ' || l_child_level_view_name
1661 || ' a, ' || l_parent_level_view_name || ' b,' || p_dim_rel_info_rec.relation_col || ' c WHERE a.code'
1662 || ' = c.' || l_child_level_pk_col || ' AND b.code = ' || 'c.' || l_parent_level_pk_col || ' AND ' || l_restrict_all_value || ')';
1663 -- Parent_id is removed from select string and distinct added since mXn will result in duplicate values when "All"
1664 -- is selected in the parent. bug# 4914929.
1665 x_select_string := 'SELECT distinct id, value FROM ' || x_data_source;
1666 x_parent_name := 'PARENT_ID';
1667 END IF;
1668 x_id_name := G_ID_NAME;
1669 x_value_name := G_VALUE_NAME;
1670
1671 EXCEPTION
1672 WHEN FND_API.G_EXC_ERROR THEN
1673 x_return_status := FND_API.G_RET_STS_ERROR;
1674 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1675 ,p_data => x_msg_data);
1676
1677 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1678 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1679 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1680 ,p_data => x_msg_data);
1681
1682 WHEN OTHERS THEN
1683 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1684 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1685 ,p_data => x_msg_data);
1686 END get_bsc_data_source;
1687
1688 --===================================================================
1689 /**
1690 *
1691 * This method will return with details of the first occurrence of a
1692 * parent-child relationship. The child short name
1693 * (p_child_dimlvl_rec.dimension_level_short_name) is used as the
1694 * parent and child for the "recursive parent-child relationship" check
1695 *
1696 */
1697 --===================================================================
1698
1699 PROCEDURE get_relationship_details (
1700 p_child_dimlvl_rec IN bis_pmf_get_dimlevels_pub.dimlvl_rec_type
1701 ,p_parent_dimlvl_tbl IN bis_pmf_get_dimlevels_pub.dimlvl_tbl_type
1702 ,x_is_relation_recursive OUT NOCOPY VARCHAR2
1703 ,x_is_relationship_found OUT NOCOPY VARCHAR2
1704 ,x_dim_rel_info_rec OUT NOCOPY bis_pmf_get_dimlevels_pub.dim_rel_info_rec
1705 ,x_return_status OUT NOCOPY VARCHAR2
1706 ,x_msg_count OUT NOCOPY NUMBER
1707 ,x_msg_data OUT NOCOPY VARCHAR2
1708 )
1709 IS
1710 l_bsc_dim_rel_info_rec bsc_dimension_levels_pub.bsc_dim_level_rec_type;
1711 l_parent_dimlvl_tbl bis_pmf_get_dimlevels_pub.dimlvl_tbl_type;
1712 l_count NUMBER;
1713 err_msg VARCHAR2(3000);
1714 BEGIN
1715 l_parent_dimlvl_tbl := p_parent_dimlvl_tbl;
1716 x_is_relation_recursive := FND_API.G_FALSE;
1717 x_is_relationship_found := FND_API.G_FALSE;
1718 x_return_status := FND_API.G_RET_STS_SUCCESS;
1719
1720 -- check for recursive parent-child relationship.
1721 -- For this, append the child to the parent table type.
1722 -- Recursive takes precedence and hence it is added as the first.
1723 l_count := l_parent_dimlvl_tbl.COUNT;
1724 l_parent_dimlvl_tbl(l_count+1) := p_child_dimlvl_rec;
1725
1726 FOR i IN REVERSE 1 .. l_parent_dimlvl_tbl.COUNT LOOP
1727 get_bsc_relationship_data (
1728 p_child_dimlvl_rec => p_child_dimlvl_rec
1729 ,p_parent_dimlvl_rec => l_parent_dimlvl_tbl(i)
1730 ,x_bsc_dim_rel_info_rec => l_bsc_dim_rel_info_rec
1731 ,x_is_relationship_found => x_is_relationship_found
1732 ,x_is_relation_recursive => x_is_relation_recursive
1733 ,x_return_status => x_return_status
1734 ,x_msg_count => x_msg_count
1735 ,x_msg_data => x_msg_data
1736 );
1737 IF (x_is_relationship_found = FND_API.G_TRUE) THEN
1738 x_dim_rel_info_rec.dimension_short_name := p_child_dimlvl_rec.dimension_short_name;
1739 x_dim_rel_info_rec.dimension_level_short_name := l_bsc_dim_rel_info_rec.bsc_level_short_name;
1743 x_dim_rel_info_rec.relation_col := l_bsc_dim_rel_info_rec.bsc_relation_column;
1740 x_dim_rel_info_rec.parent_dimension_short_name := l_parent_dimlvl_tbl(i).dimension_short_name;
1741 x_dim_rel_info_rec.parent_level_short_name := l_bsc_dim_rel_info_rec.bsc_parent_level_short_name;
1742 x_dim_rel_info_rec.parent_dim_level_value_id := l_parent_dimlvl_tbl(i).dimension_level_value_id;
1744 x_dim_rel_info_rec.relation_type := l_bsc_dim_rel_info_rec.bsc_relation_type;
1745 x_dim_rel_info_rec.parent_ri_attribute_code := l_parent_dimlvl_tbl(i).ri_attribute_code;
1746 RETURN;
1747 END IF;
1748
1749 END LOOP;
1750 -- Reach here concludes that there is no parent-child relationship defined for this.
1751
1752 EXCEPTION
1753 WHEN OTHERS THEN
1754 err_msg := SQLERRM;
1755 END get_relationship_details;
1756
1757 --=====================================================
1758
1759 PROCEDURE get_bsc_relationship_data (
1760 p_child_dimlvl_rec IN bis_pmf_get_dimlevels_pub.dimlvl_rec_type
1761 ,p_parent_dimlvl_rec IN bis_pmf_get_dimlevels_pub.dimlvl_rec_type
1762 ,x_bsc_dim_rel_info_rec OUT NOCOPY bsc_dimension_levels_pub.bsc_dim_level_rec_type
1763 ,x_is_relationship_found OUT NOCOPY VARCHAR2
1764 ,x_is_relation_recursive OUT NOCOPY VARCHAR2
1765 ,x_return_status OUT NOCOPY VARCHAR2
1766 ,x_msg_count OUT NOCOPY NUMBER
1767 ,x_msg_data OUT NOCOPY VARCHAR2
1768 )
1769 IS
1770 l_bsc_dim_level_rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
1771 -- l_return_status VARCHAR2(10);
1772 -- l_msg_count NUMBER;
1773 -- l_msg_data VARCHAR2(3000);
1774 BEGIN
1775 x_is_relationship_found := FND_API.G_FALSE;
1776 x_is_relation_recursive := FND_API.G_FALSE;
1777 l_bsc_dim_level_rec.bsc_level_short_name := p_child_dimlvl_rec.dimension_level_short_name;
1778 l_bsc_dim_level_rec.bsc_parent_level_short_name := p_parent_dimlvl_rec.dimension_level_short_name;
1779
1780 -- check if a direct parent-child relationship exists
1781 BSC_DIMENSION_LEVELS_PUB.Retrieve_Relationship (
1782 p_Dim_Level_Rec => l_bsc_dim_level_rec
1783 ,x_Dim_Level_Rec => x_bsc_dim_rel_info_rec
1784 ,x_return_status => x_return_status
1785 ,x_msg_count => x_msg_count
1786 ,x_msg_data => x_msg_data
1787 );
1788
1789 IF ( ((x_return_status IS NULL) OR (x_return_status = FND_API.G_RET_STS_SUCCESS)) AND (x_bsc_dim_rel_info_rec.bsc_level_short_name IS NOT NULL) ) THEN -- This shows that relationship exists
1790 x_is_relationship_found := FND_API.G_TRUE;
1791 -- if parent child, then check for recursive relationship
1792 IF ( UPPER(p_child_dimlvl_rec.dimension_level_short_name) = UPPER(p_parent_dimlvl_rec.dimension_level_short_name) ) THEN
1793 x_is_relation_recursive := FND_API.G_TRUE;
1794 END IF;
1795 END IF;
1796
1797 EXCEPTION
1798 WHEN OTHERS THEN
1799 NULL;
1800 END get_bsc_relationship_data;
1801 --===================================================================
1802 /* bug# 4699787
1803 * For pre-seeded relationships, do not append the dynamic where clause
1804 * for parent-child relationship.
1805 * bsc_sys_dim_level_rels does not have WHO columns.
1806 */
1807 FUNCTION is_append_where_clause(
1808 p_dim_rel_info_rec IN bis_pmf_get_dimlevels_pub.dim_rel_info_rec
1809 ,p_is_relation_recursive IN VARCHAR2
1810 ) RETURN BOOLEAN
1811 IS
1812 l_created_by NUMBER;
1813 l_created_by_parent NUMBER;
1814 BEGIN
1815 SELECT created_by INTO l_created_by FROM bis_levels WHERE short_name = p_dim_rel_info_rec.dimension_level_short_name;
1816
1817 IF ((p_dim_rel_info_rec.parent_level_short_name IS NOT NULL) AND (p_is_relation_recursive IS NULL OR p_is_relation_recursive = FND_API.G_FALSE)) THEN
1818 SELECT created_by INTO l_created_by_parent FROM bis_levels WHERE short_name = p_dim_rel_info_rec.parent_level_short_name;
1819 ELSE
1820 l_created_by_parent := l_created_by;
1821 END IF;
1822 --Followed the logic from AFLDUTLB.pls
1823 IF (l_created_by IN ('1', '2', '120','121','122','123','124','125','126','127','128','129') AND l_created_by_parent IN ('1', '2', '120','121','122','123','124','125','126','127','128','129')) THEN
1824 RETURN FALSE;
1825 END IF;
1826
1827 RETURN TRUE;
1828 EXCEPTION
1829 WHEN OTHERS THEN
1830 RETURN FALSE;
1831 END;
1832
1833 --===================================================================
1834 /**
1835 * Returns the source from bis_levels table, given the
1836 * dim level short name
1837 */
1838 ----=================================================================
1839 FUNCTION get_source (
1840 p_dim_level_short_name IN VARCHAR2
1841 ) RETURN VARCHAR2
1842 IS
1843 l_source BIS_LEVELS.SOURCE%TYPE;
1844 CURSOR c_dim_source(cp_dimLevelName IN bis_levels.source%TYPE) IS
1845 SELECT source
1846 FROM bis_levels
1847 WHERE short_name = cp_dimLevelName ;
1848 BEGIN
1849
1850 IF ( c_dim_source%ISOPEN) THEN
1851 CLOSE c_dim_source;
1852 END IF;
1853
1854 OPEN c_dim_source(cp_DimLevelName => p_dim_level_short_name );
1855 FETCH c_dim_source INTO l_source ;
1856 IF (c_dim_source%NOTFOUND) THEN
1857 FND_MESSAGE.SET_NAME('BIS','BIS_INAVLID_LEVEL_SHORT_NAME');
1858 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME', p_dim_level_short_name);
1859 FND_MSG_PUB.ADD;
1860 RAISE FND_API.G_EXC_ERROR;
1861 END IF;
1862 CLOSE c_dim_source;
1863
1864 RETURN l_source;
1865
1866 EXCEPTION
1870 END IF;
1867 WHEN OTHERS THEN
1868 IF ( c_dim_source%ISOPEN) THEN
1869 CLOSE c_dim_source;
1871 RAISE FND_API.G_EXC_ERROR;
1872 END get_source;
1873
1874 --=================================================================
1875 FUNCTION needs_time_cols (
1876 p_source IN bis_levels.source%TYPE
1877 ,p_data_source IN bis_levels.level_values_view_name%TYPE
1878 ,p_lvlshortname IN bis_levels.short_name%TYPE
1879 ,p_dimshortname IN bis_dimensions.short_name%TYPE
1880 ) RETURN BOOLEAN
1881 IS
1882 l_dimshortname_time bis_dimensions.short_name%TYPE;
1883 l_lvlshortname_total bis_levels.short_name%TYPE;
1884 BEGIN
1885 IF p_source IS NOT NULL THEN
1886 l_dimshortname_time := BIS_UTILITIES_PVT.GET_TIME_DIMENSION_NAME_SRC(p_source => p_source);
1887
1888 IF ((p_source = 'EDW') AND (p_data_source IS NULL)) THEN
1889 l_lvlshortname_total := BIS_UTILITIES_PVT.GET_TOTAL_DIMLEVEL_NAME_SRC(p_dim_short_name=>p_dimshortname
1890 ,p_source => p_source
1891 );
1892 END IF;
1893 ELSE -- if source is null, it is oltp case
1894 l_dimshortname_time := BIS_UTILITIES_PVT.GET_TIME_DIMENSION_NAME(p_DimLevelId => NULL
1895 ,p_DimLevelName =>p_lvlshortname
1896 );
1897 END IF;
1898
1899 IF (p_source = 'EDW' AND p_data_source IS NULL) THEN
1900 IF ((p_dimshortname = l_dimshortname_time) AND (p_lvlshortname <> l_lvlshortname_total)) THEN
1901 RETURN TRUE;
1902 END IF;
1903 ELSE -- oltp case
1904 IF (p_dimshortname = l_dimshortname_time) THEN
1905 RETURN TRUE;
1906 END IF;
1907 END IF;
1908
1909 RETURN FALSE;
1910
1911 EXCEPTION
1912 WHEN OTHERS THEN
1913 RETURN FALSE;
1914 END needs_time_cols;
1915
1916 --======================================================================
1917 PROCEDURE get_oltp_edw_cols (
1918 p_Dim_Level_Short_Name IN VARCHAR2
1919 ,p_Source IN VARCHAR2
1920 ,x_Table_Name OUT NOCOPY VARCHAR2
1921 ,x_Id_Name OUT NOCOPY VARCHAR2
1922 ,x_Value_Name OUT NOCOPY VARCHAR2
1923 ,x_return_status OUT NOCOPY VARCHAR2
1924 ,x_msg_count OUT NOCOPY NUMBER
1925 ,x_msg_data OUT NOCOPY VARCHAR2
1926 ) IS
1927 CURSOR c_bis_levels_source (cp_dimLevel_short_name IN bis_levels.short_name%TYPE) IS
1928 SELECT level_values_view_name
1929 FROM bis_levels
1930 WHERE short_name = cp_dimLevel_short_name ;
1931
1932 l_select_string VARCHAR2(1000);
1933 l_time_level VARCHAR2(100);
1934 l_data_source BIS_LEVELS.level_values_view_name%TYPE;
1935
1936 /* 3388371- gbhaloti FOR BSC LEVELS */
1937 --l_bsc_source VARCHAR(10);
1938 BEGIN
1939 x_return_status := FND_API.G_RET_STS_SUCCESS;
1940
1941 /* 3388371- gbhaloti FOR BSC LEVELS */
1942 --l_bsc_source := get_dim_level_source (p_Dim_Level_Short_Name);
1943
1944 IF ( c_bis_levels_source%ISOPEN) THEN
1945 CLOSE c_bis_levels_source;
1946 END IF;
1947
1948 OPEN c_bis_levels_source(cp_dimLevel_short_name => p_Dim_Level_Short_Name );
1949 FETCH c_bis_levels_source INTO l_data_source ;
1950 IF (c_bis_levels_source%NOTFOUND) THEN
1951 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_LEVEL_SHORTNAME');
1952 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME', p_Dim_Level_Short_Name);
1953 FND_MSG_PUB.ADD;
1954 RAISE FND_API.G_EXC_ERROR;
1955 END IF;
1956 CLOSE c_bis_levels_source;
1957
1958 IF ((p_Source = G_EDW) AND (l_data_source IS NULL)) THEN
1959 BIS_PMF_GET_DIMLEVELS_PVT.GET_EDW_SELECT_STRING
1960 (p_dim_level_name => p_Dim_Level_Short_Name
1961 ,p_source => p_Source
1962 ,x_table_name => x_table_name
1963 ,x_id_name => x_id_name
1964 ,x_value_name => x_value_name
1965 ,x_edw_select_String => l_Select_String
1966 ,x_time_level => l_time_level
1967 ,x_return_status => x_return_status
1968 ,x_msg_count => x_msg_count
1969 ,x_msg_data => x_msg_data
1970 );
1971
1972 ELSE
1973 IF (l_data_source IS NULL) THEN -- check - should use existing API
1974 FND_MESSAGE.SET_NAME('BIS','BIS_PMF_NULL_DATA_SOURCE');
1975 FND_MSG_PUB.ADD;
1976 RAISE FND_API.G_EXC_ERROR;
1977 END IF;
1978 x_id_name := G_ID_NAME;
1979 x_value_name := G_VALUE_NAME;
1980 x_table_name := l_data_source;
1981 END IF;
1982
1983 EXCEPTION
1984 WHEN FND_API.G_EXC_ERROR THEN
1985 IF ( c_bis_levels_source%ISOPEN) THEN
1986 CLOSE c_bis_levels_source;
1987 END IF;
1988 x_return_status := FND_API.G_RET_STS_ERROR;
1989 FND_MSG_PUB.Count_And_Get
1990 ( p_count => x_msg_count,
1991 p_data => x_msg_data
1992 );
1993 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1994 IF ( c_bis_levels_source%ISOPEN) THEN
1995 CLOSE c_bis_levels_source;
1996 END IF;
1997 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1998 FND_MSG_PUB.Count_And_Get
1999 ( p_count => x_msg_count,
2000 p_data => x_msg_data
2001 );
2002 WHEN OTHERS THEN
2003 IF ( c_bis_levels_source%ISOPEN) THEN
2004 CLOSE c_bis_levels_source;
2005 END IF;
2006 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2010 'GET_OLTP_EDW_COLS'
2007 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2008 FND_MSG_PUB.Add_Exc_Msg
2009 (G_PKG_NAME,
2011 );
2012 END IF;
2013 FND_MSG_PUB.Count_And_Get
2014 (p_count => x_msg_count,
2015 p_data => x_msg_data
2016 );
2017 END get_oltp_edw_cols;
2018
2019 --===================================================================
2020 /**
2021 * Returns the source from bsc_sys_dim_levels_b table, given the
2022 * dim level short name
2023 */
2024 ----=================================================================
2025 FUNCTION get_dim_level_source (
2026 p_dim_level_short_name IN VARCHAR2
2027 ) RETURN VARCHAR2
2028 IS
2029 l_source BSC_SYS_DIM_LEVELS_B.SOURCE%TYPE;
2030 CURSOR c_dim_source(cp_dimLevelName IN VARCHAR2) IS
2031 SELECT source
2032 FROM bsc_sys_dim_levels_b
2033 WHERE short_name = cp_dimLevelName ;
2034 BEGIN
2035
2036 IF ( c_dim_source%ISOPEN) THEN
2037 CLOSE c_dim_source;
2038 END IF;
2039
2040 OPEN c_dim_source(cp_DimLevelName => p_dim_level_short_name );
2041 FETCH c_dim_source INTO l_source ;
2042 IF (c_dim_source%NOTFOUND) THEN
2043 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_LEVEL_SHORTNAME');
2044 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME', p_dim_level_short_name);
2045 FND_MSG_PUB.ADD;
2046 RAISE FND_API.G_EXC_ERROR;
2047 END IF;
2048 CLOSE c_dim_source;
2049
2050 RETURN l_source;
2051
2052 EXCEPTION
2053 WHEN OTHERS THEN
2054 IF ( c_dim_source%ISOPEN) THEN
2055 CLOSE c_dim_source;
2056 END IF;
2057 RAISE FND_API.G_EXC_ERROR;
2058 END get_dim_level_source;
2059
2060
2061 PROCEDURE Get_Bsc_Dim_Obj_details
2062 (
2063 p_dim_rel_info_rec IN bis_pmf_get_dimlevels_pub.dim_rel_info_rec
2064 ,p_region_code IN ak_regions.region_code%TYPE
2065 ,x_child_level_pk_col OUT NOCOPY BSC_SYS_DIM_LEVELS_B.LEVEL_PK_COL%TYPE
2066 ,x_child_level_view_name OUT NOCOPY BSC_SYS_DIM_LEVELS_B.LEVEL_VIEW_NAME%TYPE
2067 ,x_parent_level_pk_col OUT NOCOPY BSC_SYS_DIM_LEVELS_B.LEVEL_PK_COL%TYPE
2068 ,x_parent_level_view_name OUT NOCOPY BSC_SYS_DIM_LEVELS_B.LEVEL_VIEW_NAME%TYPE
2069 ,x_return_status OUT NOCOPY VARCHAR2
2070 ,x_msg_count OUT NOCOPY NUMBER
2071 ,x_msg_data OUT NOCOPY VARCHAR2
2072
2073 ) IS
2074 CURSOR c_bsc_level_metadata(cp_dimlevelSN IN BSC_SYS_DIM_LEVELS_B.SHORT_NAME%TYPE) IS
2075 SELECT level_pk_col
2076 ,level_view_name
2077 FROM bsc_sys_dim_levels_vl
2078 WHERE short_name = cp_dimlevelSN;
2079
2080 --/////////////////Added for Simulation Tree Enhancement for supporting filter views ////////////
2081
2082 CURSOR c_bsc_obj_dim_level_metadata(cp_dimlevelSN IN BSC_KPI_DIM_LEVELS_VL.LEVEL_SHORTNAME%TYPE,cp_objId IN BSC_KPIS_B.INDICATOR%TYPE) IS
2083 SELECT level_pk_col
2084 ,level_view_name
2085 FROM bsc_kpi_dim_levels_vl
2086 WHERE indicator = cp_objId
2087 AND level_shortname =cp_dimlevelSN;
2088
2089 --//Not filtering for simulation objectives,because we can use it for AG reports also
2090 --//if we open up filters for AG reports.
2091
2092 CURSOR c_kpi_cur IS
2093 SELECT indicator
2094 FROM bsc_kpis_b
2095 WHERE short_name = p_region_code;
2096
2097
2098 l_dimlevel_short_name BSC_SYS_DIM_LEVELS_B.SHORT_NAME%TYPE;
2099 l_parent_dimlevel_short_name BSC_SYS_DIM_LEVELS_B.SHORT_NAME%TYPE;
2100 l_relation_type BSC_SYS_DIM_LEVEL_RELS.RELATION_TYPE%TYPE;
2101 l_child_level_pk_col BSC_SYS_DIM_LEVELS_B.LEVEL_PK_COL%TYPE;
2102 l_child_level_view_name BSC_SYS_DIM_LEVELS_B.LEVEL_VIEW_NAME%TYPE;
2103 l_parent_level_pk_col BSC_SYS_DIM_LEVELS_B.LEVEL_PK_COL%TYPE;
2104 l_parent_level_view_name BSC_SYS_DIM_LEVELS_B.LEVEL_VIEW_NAME%TYPE;
2105 l_restrict_all_value VARCHAR2(100);
2106
2107 l_region_code ak_regions.region_code%TYPE;
2108 l_indicator bsc_kpis_b.indicator%TYPE;
2109 l_count NUMBER :=0;
2110
2111 BEGIN
2112 FND_MSG_PUB.INITIALIZE;
2113 x_return_status := FND_API.G_RET_STS_SUCCESS;
2114
2115 l_dimlevel_short_name := p_dim_rel_info_rec.dimension_level_short_name;
2116 l_parent_dimlevel_short_name := p_dim_rel_info_rec.parent_level_short_name;
2117 l_relation_type := p_dim_rel_info_rec.relation_type;
2118
2119
2120 l_region_code := p_region_code;
2121
2122 FOR cd IN c_kpi_cur LOOP
2123 l_indicator := cd.indicator;
2124 END LOOP;
2125
2126 IF (c_bsc_obj_dim_level_metadata%ISOPEN) THEN
2127 CLOSE c_bsc_obj_dim_level_metadata;
2128 END IF;
2129
2130 OPEN c_bsc_obj_dim_level_metadata(l_dimlevel_short_name,l_indicator);
2131 FETCH c_bsc_obj_dim_level_metadata INTO l_child_level_pk_col, l_child_level_view_name;
2132 CLOSE c_bsc_obj_dim_level_metadata;
2133
2134
2135 IF(l_region_code IS NULL OR l_child_level_view_name IS NULL) THEN
2136 -- BSC types will be handled using the table names from bsc data model and using the datasource as a subquery
2137 -- for a id, value, parent_id SQL.
2138 IF (c_bsc_level_metadata%ISOPEN) THEN
2139 CLOSE c_bsc_level_metadata;
2140 END IF;
2141
2142 OPEN c_bsc_level_metadata(l_dimlevel_short_name);
2143 FETCH c_bsc_level_metadata INTO l_child_level_pk_col, l_child_level_view_name;
2144 IF (c_bsc_level_metadata%NOTFOUND) THEN
2145 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_LEVEL_SHORTNAME');
2146 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME', l_dimlevel_short_name);
2147 FND_MSG_PUB.ADD;
2148 RAISE FND_API.G_EXC_ERROR;
2149 END IF;
2150 CLOSE c_bsc_level_metadata;
2151
2152 IF (l_parent_dimlevel_short_name IS NOT NULL) THEN
2153 OPEN c_bsc_level_metadata(l_parent_dimlevel_short_name);
2154 FETCH c_bsc_level_metadata INTO l_parent_level_pk_col, l_parent_level_view_name;
2155 IF (c_bsc_level_metadata%NOTFOUND) THEN
2156 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_LEVEL_SHORTNAME');
2157 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME', l_parent_dimlevel_short_name);
2158 FND_MSG_PUB.ADD;
2159 RAISE FND_API.G_EXC_ERROR;
2160 END IF;
2161 CLOSE c_bsc_level_metadata;
2162 END IF;
2163 ELSE
2164 IF (l_parent_dimlevel_short_name IS NOT NULL) THEN
2165 OPEN c_bsc_obj_dim_level_metadata(l_parent_dimlevel_short_name,l_indicator);
2166 FETCH c_bsc_obj_dim_level_metadata INTO l_parent_level_pk_col, l_parent_level_view_name;
2167 IF (c_bsc_obj_dim_level_metadata%NOTFOUND) THEN
2168 FND_MESSAGE.SET_NAME('BIS','BIS_INVALID_LEVEL_SHORTNAME');
2169 FND_MESSAGE.SET_TOKEN('DIMLEVEL_SHORT_NAME', l_parent_dimlevel_short_name);
2170 FND_MSG_PUB.ADD;
2171 RAISE FND_API.G_EXC_ERROR;
2172 END IF;
2173 CLOSE c_bsc_obj_dim_level_metadata;
2174 END IF;
2175 END IF;
2176
2177 x_child_level_pk_col := l_child_level_pk_col;
2178 x_child_level_view_name := l_child_level_view_name;
2179 x_parent_level_pk_col := l_parent_level_pk_col ;
2180 x_parent_level_view_name:= l_parent_level_view_name;
2181
2182
2183 EXCEPTION
2184 WHEN FND_API.G_EXC_ERROR THEN
2185 IF (c_bsc_level_metadata%ISOPEN) THEN
2186 CLOSE c_bsc_level_metadata;
2187 END IF;
2188 IF (c_bsc_obj_dim_level_metadata%ISOPEN) THEN
2189 CLOSE c_bsc_obj_dim_level_metadata;
2190 END IF;
2191 x_return_status := FND_API.G_RET_STS_ERROR;
2192 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2193 ,p_data => x_msg_data);
2194 WHEN OTHERS THEN
2195 IF (c_bsc_level_metadata%ISOPEN) THEN
2196 CLOSE c_bsc_level_metadata;
2197 END IF;
2198 IF (c_bsc_obj_dim_level_metadata%ISOPEN) THEN
2199 CLOSE c_bsc_obj_dim_level_metadata;
2200 END IF;
2201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2202 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
2203 ,p_data => x_msg_data);
2204
2205 END Get_Bsc_Dim_Obj_details;
2206
2207
2208 --======================================================================
2209
2210 END BIS_PMF_GET_DIMLEVELS_PVT;