1 PACKAGE BODY BIS_PMV_BSC_API_PVT AS
2 /* $Header: BISVVEWB.pls 120.0 2005/06/01 17:14:04 appldev noship $ */
3 --
4 /*
5 REM +=======================================================================+
6 REM | Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA |
7 REM | All rights reserved. |
8 REM +=======================================================================+
9 REM | FILENAME |
10 REM | BISVVEWB.pls |
11 REM | |
12 REM | DESCRIPTION |
13 REM | Private API for getting information about PMV Reports |
14 REM | |
15 REM | NOTES |
16 REM | |
17 REM | HISTORY |
18 REM | Date Developer Comments |
19 REM | 08/22/02 nbarik Creation |
20 REM | 06/16/04 nbarik Bug Fix 3616680 |
21 REM | |
22 REM +=======================================================================+
23 */
24
25 -- Global package name
26 --
27 G_PKG_NAME CONSTANT VARCHAR2(30) := 'BIS_PMV_BSC_API_PVT';
28 G_DEBUG BOOLEAN := FALSE;
29 G_ERROR BOOLEAN := FALSE;
30
31 -- Procedure for debugging
32 --PROCEDURE Print(p_string IN VARCHAR2);
33
34 --
35 -- Get all the Dimension+Dimension Level combination in the report
36 -- associated with a Measure, and whether View By and All applies to those
37 -- Dimension Levels
38 --
39 PROCEDURE Get_DimLevel_Viewby
40 ( p_api_version IN NUMBER DEFAULT NULL
41 , p_Region_Code IN VARCHAR2
42 , p_Measure_Short_Name IN VARCHAR2
43 , x_DimLevel_Viewby_Tbl OUT NOCOPY BIS_PMV_BSC_API_PUB.DimLevel_Viewby_Tbl_Type
44 , x_return_status OUT NOCOPY VARCHAR2
45 , x_msg_count OUT NOCOPY NUMBER
46 , x_msg_data OUT NOCOPY VARCHAR2
47 ) IS
48
49 l_api_name VARCHAR2(30) := 'Get_DimLevel_Viewby';
50 l_region_code VARCHAR2(30) := NULL;
51 l_nested_region_code VARCHAR2(30) := NULL;
52 l_ak_region_items_rec BIS_PMV_BSC_API_PVT.AK_REGION_ITEMS_REC_TYPE;
53 l_dimlevel_viewby_rec BIS_PMV_BSC_API_PUB.Dimlevel_Viewby_Rec_Type;
54 l_disable_viewby VARCHAR2(1) := 'N';
55 l_index NUMBER := 1;
56 is_duplicate BOOLEAN := FALSE;
57
58 CURSOR region_code_cursor(cp_measure_short_name VARCHAR2) IS
59 SELECT region_code FROM ak_region_items WHERE attribute1='MEASURE' AND
60 attribute2 = cp_measure_short_name
61 ORDER BY creation_date DESC;
62
63 CURSOR disable_viewby_cursor(cp_region_code VARCHAR2) IS
64 SELECT attribute1 FROM ak_regions
65 WHERE region_code = cp_region_code;
66
67 CURSOR nested_region_cursor(cp_region_code VARCHAR2) IS
68 SELECT nested_region_code FROM ak_region_items
69 WHERE region_code=cp_region_code AND item_style='NESTED_REGION';
70
71 -- Enh 3420818 - retrieve attribute_code
72 CURSOR ak_region_items_cursor(cp_region_code VARCHAR2)
73 IS
74 SELECT attribute_code attribute_code, attribute1 attribute_type, attribute2 attribute_value, required_flag
75 FROM ak_region_items WHERE region_code = cp_region_code AND
76 attribute1 IN ('DIM LEVEL SINGLE VALUE', 'DIMENSION LEVEL', 'HIDE DIMENSION LEVEL',
77 'HIDE PARAMETER', 'HIDE VIEW BY DIMENSION', 'VIEWBY PARAMETER', 'HIDE_VIEW_BY_DIM_SINGLE');
78
79 BEGIN
80 /*
81 IF fnd_profile.value('BIS_SQL_TRACE')= 'Y' THEN
82 g_debug := TRUE;
83 ELSE
84 g_debug := FALSE;
85 END IF;
86 */
87 x_return_status := FND_API.G_RET_STS_SUCCESS;
88 G_ERROR := FALSE;
89
90 -- get default region code
91 IF p_Region_Code IS NOT NULL THEN -- region_code is passed to the API
92 l_region_code := p_Region_Code;
93 ELSE -- no default report associated with the measure
94 IF p_Measure_Short_Name IS NOT NULL THEN
95 IF region_code_cursor%ISOPEN THEN
96 CLOSE region_code_cursor;
97 END IF;
98 FOR cr IN region_code_cursor(p_Measure_Short_Name) LOOP
99 l_region_code := cr.region_code; -- get the first region
100 EXIT;
101 END LOOP;
102 IF region_code_cursor%ISOPEN THEN
103 CLOSE region_code_cursor;
104 END IF;
105 END IF;
106 END IF;
107
108 IF l_region_code IS NULL THEN
109 G_ERROR := TRUE;
110 -- nbarik - 06/16/04 - Bug Fix 3616680
111 --x_msg_data := G_PKG_NAME || '.' || l_api_name || ' : No Region Associated with the Measure Short Name : ' || p_Measure_Short_Name;
112 fnd_message.set_name('BIS','BIS_NO_REGION_MEASURE');
113 fnd_message.set_token('SHORT_NAME', p_Measure_Short_Name);
114 x_msg_data := fnd_message.get;
115 --print(x_msg_data);
116 RAISE FND_API.G_EXC_ERROR;
117 END IF;
118
119 --print(G_PKG_NAME || '.' || l_api_name || ' : l_region_code : ' || l_region_code );
120
121 IF disable_viewby_cursor%ISOPEN THEN
122 CLOSE disable_viewby_cursor;
123 END IF;
124 OPEN disable_viewby_cursor(l_region_code);
125 FETCH disable_viewby_cursor INTO l_disable_viewby;
126 CLOSE disable_viewby_cursor;
127
128 --print(G_PKG_NAME || '.' || l_api_name || ' : l_disable_viewby : ' || l_disable_viewby);
129
130 IF ak_region_items_cursor%ISOPEN THEN
131 CLOSE ak_region_items_cursor;
132 END IF;
133 -- populate records for report region
134 -- Enh 3420818 - skip AS_OF_DATE
135 FOR cr IN ak_region_items_cursor(l_region_code) LOOP
136 IF cr.attribute_value IS NOT NULL AND cr.attribute_code <> 'AS_OF_DATE' THEN
137
138 Populate_DimLevel_Viewby_Rec(
139 p_Attribute_Type => cr.attribute_type
140 , p_Attribute_Value => cr.attribute_value
141 , p_Required_Flag => cr.required_flag
142 , p_Disable_Viewby => l_disable_viewby
143 , x_DimLevel_Viewby_Rec => l_dimlevel_viewby_rec
144 );
145
146 x_DimLevel_Viewby_Tbl(l_index) := l_dimlevel_viewby_rec;
147 l_index := l_index + 1;
148
149 END IF;
150 END LOOP;
151
152 IF ak_region_items_cursor%ISOPEN THEN
153 CLOSE ak_region_items_cursor;
154 END IF;
155
156 -- check whether the report region contains nested region
157 IF nested_region_cursor%ISOPEN THEN
158 CLOSE nested_region_cursor;
159 END IF;
160
161 OPEN nested_region_cursor(l_region_code);
162 FETCH nested_region_cursor INTO l_nested_region_code;
163 CLOSE nested_region_cursor;
164
165 --print(G_PKG_NAME || '.' || l_api_name || ' : l_nested_region_code : '|| l_nested_region_code);
166
167 -- If report has a nested region - dbi report
168 -- Enh 3420818 - skip AS_OF_DATE
169 IF l_nested_region_code IS NOT NULL THEN
170 IF ak_region_items_cursor%ISOPEN THEN
171 CLOSE ak_region_items_cursor;
172 END IF;
173 FOR cr IN ak_region_items_cursor(l_nested_region_code) LOOP
174 IF cr.attribute_value IS NOT NULL AND cr.attribute_code <> 'AS_OF_DATE' THEN
175 IF (x_DimLevel_Viewby_Tbl.COUNT > 0) THEN
176 is_duplicate := FALSE;
177 FOR i IN x_DimLevel_Viewby_Tbl.FIRST..x_DimLevel_Viewby_Tbl.LAST LOOP
178 l_dimlevel_viewby_rec := x_DimLevel_Viewby_Tbl(i);
179 IF (cr.attribute_value = l_dimlevel_viewby_rec.Dim_DimLevel) THEN
180 is_duplicate := TRUE;
181 EXIT;
182 END IF;
183 END LOOP;
184 IF NOT is_duplicate THEN
185 Populate_DimLevel_Viewby_Rec(
186 p_Attribute_Type => cr.attribute_type
187 , p_Attribute_Value => cr.attribute_value
188 , p_Required_Flag => cr.required_flag
189 , p_Disable_Viewby => l_disable_viewby
190 , x_DimLevel_Viewby_Rec => l_dimlevel_viewby_rec
191 );
192
193 x_DimLevel_Viewby_Tbl(l_index) := l_dimlevel_viewby_rec;
194 l_index := l_index + 1;
195 END IF;
196 END IF;
197 END IF;
198 END LOOP;
199 IF ak_region_items_cursor%ISOPEN THEN
200 CLOSE ak_region_items_cursor;
201 END IF;
202 END IF;
203
204 EXCEPTION
205 WHEN FND_API.G_EXC_ERROR THEN
206 x_return_status := FND_API.G_RET_STS_ERROR ;
207 IF ak_region_items_cursor%ISOPEN THEN
208 CLOSE ak_region_items_cursor;
209 END IF;
210 IF nested_region_cursor%ISOPEN THEN
211 CLOSE nested_region_cursor;
212 END IF;
213 IF disable_viewby_cursor%ISOPEN THEN
214 CLOSE disable_viewby_cursor;
215 END IF;
216 IF region_code_cursor%ISOPEN THEN
217 CLOSE region_code_cursor;
218 END IF;
219 IF NOT G_ERROR THEN
220 FND_MSG_PUB.Count_And_Get
221 ( p_count => x_msg_count
222 ,p_data => x_msg_data
223 );
224 END IF;
225
226 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
228 IF ak_region_items_cursor%ISOPEN THEN
229 CLOSE ak_region_items_cursor;
230 END IF;
231 IF nested_region_cursor%ISOPEN THEN
232 CLOSE nested_region_cursor;
233 END IF;
234 IF disable_viewby_cursor%ISOPEN THEN
235 CLOSE disable_viewby_cursor;
236 END IF;
237 IF region_code_cursor%ISOPEN THEN
238 CLOSE region_code_cursor;
239 END IF;
240 FND_MSG_PUB.Count_And_Get
241 ( p_count => x_msg_count
242 ,p_data => x_msg_data
243 );
244
245 WHEN others THEN
246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
247 IF ak_region_items_cursor%ISOPEN THEN
248 CLOSE ak_region_items_cursor;
249 END IF;
250 IF nested_region_cursor%ISOPEN THEN
251 CLOSE nested_region_cursor;
252 END IF;
253 IF disable_viewby_cursor%ISOPEN THEN
254 CLOSE disable_viewby_cursor;
255 END IF;
256 IF region_code_cursor%ISOPEN THEN
257 CLOSE region_code_cursor;
258 END IF;
259 FND_MSG_PUB.Count_And_Get
260 ( p_count => x_msg_count
261 ,p_data => x_msg_data
262 );
263 END Get_DimLevel_Viewby;
264
265 --
266 -- PROCEDURE Populate_DimLevel_Viewby_Rec
267 --
268 -- Populate each DimLevel_Viewby_Rec record depending on Attribute Type,
269 -- Attribute Value, Required Flag and Disable Viewby Parameter
270 --
271 PROCEDURE Populate_DimLevel_Viewby_Rec
272 ( p_Attribute_Type IN VARCHAR2
273 , p_Attribute_Value IN VARCHAR2
274 , p_Required_Flag IN VARCHAR2
275 , p_Disable_Viewby IN VARCHAR2
276 , x_DimLevel_Viewby_Rec OUT NOCOPY BIS_PMV_BSC_API_PUB.DimLevel_Viewby_Rec_Type
277 ) IS
278
279 BEGIN
280 --populate Dimension+Dimension Level
281 x_DimLevel_Viewby_Rec.Dim_DimLevel := p_Attribute_Value;
282
283 --populate ViewBy Applicable or not
284 IF p_Disable_Viewby = 'Y' THEN --No ViewBy Report
285 x_DimLevel_Viewby_Rec.ViewBy_Applicable := 'N';
286 ELSE
287 IF p_Attribute_Type = 'HIDE PARAMETER' OR p_Attribute_Type = 'HIDE VIEW BY DIMENSION' THEN
288 x_DimLevel_Viewby_Rec.ViewBy_Applicable := 'N';
289 ELSE
290 x_DimLevel_Viewby_Rec.ViewBy_Applicable := 'Y';
291 END IF;
292 END IF;
293
294 --populate All Applicable or not
295 IF p_Required_Flag = 'Y' THEN
296 x_DimLevel_Viewby_Rec.All_Applicable := 'N';
297 ELSE
298 x_DimLevel_Viewby_Rec.All_Applicable := 'Y';
299 END IF;
300
301 IF ( (p_Attribute_Type = 'HIDE DIMENSION LEVEL') OR (p_Attribute_Type = 'HIDE PARAMETER')
302 OR (p_Attribute_Type = 'VIEWBY PARAMETER') )
303 THEN
304 x_DimLevel_Viewby_Rec.Hide_Level := 'Y';
305 ELSE
306 x_DimLevel_Viewby_Rec.Hide_Level := 'N';
307 END IF;
308
309 END Populate_DimLevel_Viewby_Rec;
310
311 -- Procedure for debugging
312 /*
313 PROCEDURE Print(p_string IN VARCHAR2)
314 IS
315 BEGIN
316 IF g_debug THEN
317 NULL;
318 --Enable for Debugging
319 --dbms_output.put_line(p_string);
320 --fnd_file.put_line(fnd_file.log, p_string);
321 END IF;
322 END Print;
323 */
324
325 END BIS_PMV_BSC_API_PVT;
[Home] [Help]