1 Package Body BIS_REPORT_PUB as
2 /* $Header: BISPREPB.pls 120.2 2005/11/03 16:49:31 hengliu noship $ */
3 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
4 -- dbdrv: checkfile(115.3=120.2):~PROD:~PATH:~FILE
5 -- Purpose: Briefly explain the functionality of the package body
6 -- Can use this package for general report related routines.
7 -- MODIFICATION HISTORY
8 -- Person Date Comments
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- mdamle 10/11/04 Initial Creation
12 -- arhegde 09/08/05 Increased lengths of variables in getRegionCode
13 -- since incorrect defn. from product teams made these fail
14 -- hengliu 11/03/05 Bug#4717611 Handle new pmv report definition
15 ---------------------------------------------------------------------
16
17 function getRegionCode(pFunctionName IN VARCHAR2) return varchar2 IS
18
19 l_parameters VARCHAR2(2000);
20 l_region VARCHAR2(2000);
21 l_type VARCHAR2(2000);
22 l_web_html_call VARCHAR2(2000);
23 l_ref_function_name VARCHAR2(2000);
24
25 cursor c_form_func(cpFunctionName varchar2) is
26 select web_html_call,parameters,type
27 from fnd_form_functions
28 where function_name = cpFunctionName;
29
30 BEGIN
31
32 -- mdamle 12/27/2001 - Region Code is specified in web_html_call when type = WWW
33 -- - Region Code may be specified in parameters when type = DBPORTLET / WEBPORTLET
34 if c_form_func%ISOPEN then
35 close c_form_func;
36 end if;
37 open c_form_func(pFunctionName);
38 fetch c_form_func into l_web_html_call, l_parameters, l_type;
39 close c_form_func;
40
41 if l_type = 'WWW' then
42 l_region := substr( substr( l_web_html_call, instr(l_web_html_call, '''')+1 ), 1, instr(substr( l_web_html_call, instr(l_web_html_call, '''')+1 ),'''')-1 );
43 if l_region is null then
44 -- Try parameters
45 l_region := BIS_COMMON_UTILS.getParameterValue(l_parameters, 'pRegionCode');
46 end if;
47 elsif l_type = 'WEBPORTLET' then
48 l_region := BIS_COMMON_UTILS.getParameterValue(l_parameters, 'pRegionCode');
49 else
50 -- Type = DBPORTLET
51 l_region := BIS_COMMON_UTILS.getParameterValue(l_parameters, 'pRegionCode');
52
53 -- Check if portlet is pointing to another function
54 -- Get region code from that function
55 l_ref_function_name := BIS_COMMON_UTILS.getParameterValue(l_parameters, 'pFunctionName');
56 if l_ref_function_name is null then
57 l_ref_function_name := BIS_COMMON_UTILS.getParameterValue(l_parameters, 'FUNCTION_NAME');
58 end if;
59
60 if l_ref_function_name is not null and l_ref_function_name <> '' then
61 if c_form_func%ISOPEN then
62 close c_form_func;
63 end if;
64 open c_form_func(l_ref_function_name);
65 fetch c_form_func into l_web_html_call, l_parameters, l_type;
66 close c_form_func;
67
68 if l_type = 'WWW' then
69 l_region := substr( substr( l_web_html_call, instr(l_web_html_call, '''')+1 ), 1, instr(substr( l_web_html_call, instr(l_web_html_call, '''')+1 ),'''')-1 );
70 if l_region is null then
71 -- Try parameters
72 l_region := BIS_COMMON_UTILS.getParameterValue(l_parameters, 'pRegionCode');
73 end if;
74 end if;
75 end if; -- l_ref_function_name is not null
76 end if;
77
78 return l_region;
79
80 END getRegionCode;
81
82 FUNCTION getRegionApplicationId(pRegionCode IN VARCHAR2) RETURN NUMBER IS
83
84 CURSOR region_app_id_cursor(cp_region_code VARCHAR2) IS
85 SELECT region_application_id FROM ak_regions
86 WHERE region_code = cp_region_code;
87
88 l_region_app_id NUMBER;
89
90 BEGIN
91
92 IF region_app_id_cursor%ISOPEN THEN
93 CLOSE region_app_id_cursor;
94 END IF;
95
96 OPEN region_app_id_cursor(pRegionCode);
97 FETCH region_app_id_cursor INTO l_region_app_id;
98 IF region_app_id_cursor%NOTFOUND THEN
99 l_region_app_id := -1;
100 END IF;
101 CLOSE region_app_id_cursor;
102
103 RETURN l_region_app_id;
104 EXCEPTION
105 WHEN others THEN
106 IF region_app_id_cursor%ISOPEN THEN
107 CLOSE region_app_id_cursor;
108 END IF;
109
110 END getRegionApplicationId;
111
112 FUNCTION getPortletType(pType IN VARCHAR2, pParameters IN VARCHAR2) RETURN VARCHAR2 IS
113 l_request_type CHAR;
114 BEGIN
115 IF (upper(pType) = 'JSP' OR upper(pType) = 'WWW') THEN
116 RETURN fnd_message.get_string('BIS', 'BIS_REPORT_TITLE');
117 ELSE
118 IF (upper(pType) = 'WEBPORTLET') THEN
119 l_request_type := BIS_COMMON_UTILS.getParameterValue(pParameters, 'pRequestType');
120 IF (l_request_type = 'T') THEN
121 RETURN fnd_message.get_string('BIS', 'BIS_TREND_TABLE');
122 ELSIF (l_request_type = 'G') THEN
123 RETURN fnd_message.get_string('BIS', 'BIS_TREND_GRAPH');
124 ELSIF (l_request_type = 'P') THEN
125 RETURN fnd_message.get_string('BIS', 'BIS_PARAMETERS');
126 END IF;
127 END IF;
128 END IF;
129
130 RETURN null;
131
132 END getPortletType;
133
134 FUNCTION getPortletTypeCode(pType IN VARCHAR2, pParameters IN VARCHAR2) RETURN CHAR IS
135 l_request_type CHAR;
136 BEGIN
137 IF (upper(pType) = 'JSP' OR upper(pType) = 'WWW') THEN
138 RETURN 'R';
139 ELSE
140 IF (upper(pType) = 'WEBPORTLET') THEN
141 RETURN BIS_COMMON_UTILS.getParameterValue(pParameters, 'pRequestType');
142 ELSE
143 RETURN NULL;
144 END IF;
145 END IF;
146 EXCEPTION
147 WHEN OTHERS THEN
148 RETURN null;
149
150 END getPortletTypeCode;
151
152 FUNCTION getRegionCode(pType IN VARCHAR2, pParameters IN VARCHAR2, webHtmlCall IN VARCHAR2, functionName IN VARCHAR2) RETURN CHAR IS
153 l_request_type CHAR;
154 BEGIN
155 IF (pType = 'JSP') THEN
156 RETURN nvl(BIS_COMMON_UTILS.getParameterValue(webHtmlCall, 'regionCode'), BIS_COMMON_UTILS.getparametervalue(pParameters,'pRegionCode'));
157 ELSIF (pType = 'WWW') THEN
158 RETURN nvl(trim(BIS_COMMON_UTILS.getParameterValue(pParameters, 'pRegionCode')), getRegionCode(functionName));
159 ELSIF (pType = 'WEBPORTLET') THEN
160 RETURN BIS_COMMON_UTILS.getParameterValue(pParameters, 'pRegionCode');
161 END IF;
162
163 RETURN NULL;
164
165 END getRegionCode;
166
167 FUNCTION getRegionApplicationName(pRegionCode IN VARCHAR2) RETURN VARCHAR2 IS
168
169 CURSOR region_app_name_cursor(cp_region_code VARCHAR2) IS
170 SELECT application_name FROM ak_regions R, fnd_application_vl A
171 WHERE R.region_code = cp_region_code AND R.region_application_id = A.application_id;
172
173 l_code VARCHAR2(3);
174
175 CURSOR app_name_from_table is
176 SELECT application_name
177 FROM fnd_application_vl app
178 WHERE app.application_short_name = l_code;
179
180 l_region_app_name VARCHAR2(2000);
181
182 BEGIN
183
184 IF region_app_name_cursor%ISOPEN THEN
185 CLOSE region_app_name_cursor;
186 END IF;
187
188 OPEN region_app_name_cursor(pRegionCode);
189 FETCH region_app_name_cursor INTO l_region_app_name;
190 IF region_app_name_cursor%NOTFOUND THEN
191 IF app_name_from_table%ISOPEN THEN
192 CLOSE app_name_from_table;
193 END IF;
194
195 l_code := 'FND';
196 OPEN app_name_from_table;
197 FETCH app_name_from_table INTO l_region_app_name;
198 CLOSE app_name_from_table;
199 END IF;
200 CLOSE region_app_name_cursor;
201
202 RETURN l_region_app_name;
203 EXCEPTION
204 WHEN others THEN
205 IF region_app_name_cursor%ISOPEN THEN
206 CLOSE region_app_name_cursor;
207 END IF;
208
209 END getRegionApplicationName;
210
211
212 FUNCTION getRegionDataSourceType(pRegionCode IN VARCHAR2) RETURN VARCHAR2 IS
213
214 CURSOR source_type_cursor(cp_region_code VARCHAR2) IS
215 SELECT attribute10 FROM ak_regions
216 WHERE region_code = cp_region_code;
217
218 l_source_type VARCHAR2(150);
219
220 BEGIN
221
222 IF source_type_cursor%ISOPEN THEN
223 CLOSE source_type_cursor;
224 END IF;
225
226 OPEN source_type_cursor(pRegionCode);
227 FETCH source_type_cursor INTO l_source_type;
228 IF source_type_cursor%NOTFOUND THEN
229 l_source_type := NULL;
230 END IF;
231 CLOSE source_type_cursor;
232
233 RETURN l_source_type;
234 EXCEPTION
235 WHEN others THEN
236 IF source_type_cursor%ISOPEN THEN
237 CLOSE source_type_cursor;
238 END IF;
239
240 END getRegionDataSourceType;
241
242
243 FUNCTION isRegionItemRequired(
244 p_required_flag in VARCHAR2
245 ,p_dim_group_name in VARCHAR2 := NULL
246 ,p_attribute1 in VARCHAR2) RETURN NUMBER IS
247
248 l_dim_level_name VARCHAR2(30);
249 l_dim_group_name VARCHAR2(30);
250 l_dim_level_id NUMBER;
251 l_dim_group_id NUMBER;
252 l_total_flag NUMBER;
253
254 BEGIN
255
256 if (p_required_flag = 'Y') then
257 return 1;
258 end if;
259
260 l_dim_group_name := p_dim_group_name;
261 if p_dim_group_name is null then
262 l_dim_group_name := SUBSTR(p_attribute1, 1,INSTR(p_attribute1, '+' ) - 1);
263 end if;
264
265 l_dim_level_name := SUBSTR(p_attribute1, INSTR(p_attribute1, '+' ) + 1);
266
267 select dim_group_id into l_dim_group_id
268 from bsc_sys_dim_groups_vl
269 where short_name = l_dim_group_name;
270
271 select dim_level_id into l_dim_level_id
272 from bsc_sys_dim_levels_b
273 where short_name = l_dim_level_name;
274
275 select total_flag into l_total_flag
276 from bsc_sys_dim_levels_by_group
277 where dim_group_id = l_dim_group_id
278 and dim_level_id = l_dim_level_id;
279
280
281 if l_total_flag = -1 then
282 return 0;
283 else
284 return 1;
285 end if;
286
287 EXCEPTION
288 WHEN others THEN
289 return 0;
290
291 END isRegionItemRequired;
292
293 FUNCTION isWeightedAverageReport(
294 p_region_code in VARCHAR2
295 ,p_region_application_id in NUMBER) RETURN CHAR IS
296
297 l_count number;
298 BEGIN
299
300 select count(*) into l_count
301 from ak_region_items ri, bis_indicators i, bsc_sys_datasets_b d
302 where ri.region_code = p_region_code
303 and ri.region_application_id = p_region_application_id
304 and attribute1 = 'MEASURE_NOTARGET'
305 and attribute2 = i.short_name
306 and i.dataset_id = d.dataset_id
307 and d.source = 'CDS';
308
309 if (l_count > 0) then
310 return 'Y';
311 else
312 return 'N';
313 end if;
314
315 EXCEPTION
316 WHEN others THEN
317 return 'N';
318
319 END isWeightedAverageReport;
320
321 END BIS_REPORT_PUB;
[Home] [Help]