[Home] [Help]
PACKAGE BODY: APPS.BIS_DIMS_AND_LEVELS_PKG
Source
1 PACKAGE BODY BIS_DIMS_AND_LEVELS_PKG AS
2 /* $Header: BISKDDLB.pls 120.2 2006/09/01 04:59:17 ankgoel noship $ */
3
4
5 FUNCTION bis_kpi_report ( p_params IN BIS_PMV_PAGE_PARAMETER_TBL )
6 RETURN VARCHAR2 IS
7
8 l_ret_string VARCHAR2(8000) := NULL;
9 l_startdate VARCHAR2(1000) := NULL;
10 l_enddate VARCHAR2(1000) := NULL;
11 l_pname VARCHAR2(2000) := NULL;
12 l_pvalue VARCHAR2(2000) := NULL;
13 l_name VARCHAR(500) := NULL;
14 l_developer VARCHAR2(500) := NULL;
15 l_kpi_name_filter VARCHAR2(1500) := NULL ; -- ' AND 1 = 1 ';
16 l_lvl_nm_filter VARCHAR2(1500) := NULL ; -- ' AND 1 = 1 ';
17 l_app_nm_filter VARCHAR2(1500) := NULL ; -- ' AND 1 = 1 ';
18 l_dim_name_filter VARCHAR2(1500) := NULL ; -- ' AND 1 = 1 ';
19 l_kpi_desc_filter VARCHAR2(1500) := NULL ; -- ' AND 1 = 1 ';
20 l_order_by VARCHAR2(150) := NULL;
21 l_group_by VARCHAR2(150) := ' GROUP BY bi.name, bi.description, bi.indicator_id, f.application_name ';
22 l_source_filter VARCHAR2(1500) := NULL;
23 l_where_clause VARCHAR2(1500) := NULL;
24
25
26 BEGIN
27
28 -- insert into tmp2 (seq, value) values (temp_seq.nextval, ' <*************** begin ***************> '|| to_char(sysdate, 'dd-mon-yyyy hh:mi:ss') ); commit;
29
30 FOR i IN 1..p_params.count LOOP
31
32 l_pname := p_params(i).parameter_name;
33 l_pvalue := BIS_UTILITIES_PVT.filter_quotes(p_params(i).parameter_value);
34
35 -- insert into tmp2 (seq, value) values (temp_seq.nextval, '(<---------------- pname ->'||l_pname||'<-pvalue->'||l_pvalue||'<------------------>)' ); commit;
36
37
38 IF ( l_pname = 'KPI_DESCRIPTION2' ) THEN
39
40 IF (l_pvalue <> 'All') THEN
41 l_kpi_desc_filter := ' AND UPPER(bi.description) LIKE UPPER(''' || '%' || l_pvalue || '%' || ''') ';
42 END IF;
43
44
45 ELSIF ( l_pname = 'APPLX+APPLX' ) THEN
46
47 IF (l_pvalue <> 'All') THEN
48 l_app_nm_filter := ' AND f.application_name = ''' || l_pvalue || ''' ' ;
49 END IF;
50
51 ELSIF ( l_pname = 'LVL_NM+LVL_NM' ) THEN
52
53 IF (l_pvalue <> 'All') THEN
54 l_lvl_nm_filter := ' AND bl.name = ''' || l_pvalue || ''' ';
55 END IF;
56
57 ELSIF ( l_pname = 'KPI_NAME1' ) THEN
58 IF (l_pvalue <> 'All') THEN
59 l_kpi_name_filter := ' AND UPPER(bi.name) LIKE UPPER(''' || '%' || l_pvalue || '%' || ''') ';
60 END IF;
61
62 ELSIF (l_pname LIKE '%ORDERBY%') THEN
63
64 -- Select * from tmp2 order by seq desc;
65 -- l_order_by := ' WHERE ';
66 l_order_by := ' ORDER BY ';
67
68 IF (l_pvalue LIKE '%LEVEL_NAME , DIMENSION_NAME1, LEVEL_DESCRIPTION%') THEN
69 l_order_by := l_order_by || ' LEVEL_NAME , DIMENSION_NAME1, LEVEL_DESCRIPTION ';
70 ELSIF (l_pvalue LIKE '%LEVEL_NAME%') THEN
71 l_order_by := l_order_by || ' LEVEL_NAME ';
72 ELSIF (l_pvalue LIKE '%DIMENSION_NAME1%') THEN
73 l_order_by := l_order_by || ' DIMENSION_NAME1 ';
74 ELSIF (l_pvalue LIKE '%LEVEL_DESCRIPTION%') THEN
75 l_order_by := l_order_by || ' LEVEL_DESCRIPTION ';
76 ELSE
77 l_order_by := l_order_by || ' LEVEL_NAME , DIMENSION_NAME1, LEVEL_DESCRIPTION ';
78 END IF;
79
80 IF (l_pvalue LIKE '%DESC') THEN
81 l_order_by := l_order_by || ' DESC ' ;
82 ELSE
83 IF (l_pvalue IS NOT NULL ) THEN
84 l_order_by := l_order_by || ' ' ;
85 END IF;
86 END IF;
87
88 -- insert into tmp2 (seq, value) values (temp_seq.nextval, ' l_order_by: ' || l_order_by ); commit;
89
90 END IF;
91 END LOOP;
92
93 l_where_clause := l_app_nm_filter || l_kpi_desc_filter || l_lvl_nm_filter || l_kpi_name_filter;
94
95
96 -- decode (product_id, 163, ''BIS'', 205, ''BSC'')
97 -- Used SUBSTR on the queries as PMV restricts the length to 180 characters
98 -- For NLS issues, an error occurs if the length exceeds 180 characters
99 l_ret_string := '
100 SELECT
101 bi.name KPI_NAME2
102 , SUBSTR(NVL(bi.description, '' ''),1,180) KPI_DESCRIPTION1
103 , SUBSTR(NVL(BIS_DIMS_AND_LEVELS_PKG.get_level_names(bi.indicator_id), '' ''),1,180) LEVEL_NAMES
104 , SUBSTR(NVL(f.application_name, '' ''),1,180) Owning_applicn_name2
105 FROM
106 bis_indicators_vl bi
107 , bis_levels_vl bl
108 , BIS_IND_TL_LVL_V bitlv
109 , bis_application_measures bam
110 , fnd_application_vl f
111 WHERE bitlv.indicator_id = bi.indicator_id
112 AND bitlv.level_id = bl.level_id
113 AND bi.indicator_id = bam.indicator_id
114 AND bam.application_id = f.application_id
115 '
116 || l_where_clause
117 || l_group_by
118 || ' ORDER BY bi.name, bi.description ';
119
120 -- insert into tmp2 (seq, value) values (temp_seq.nextval, l_ret_string); commit;
121
122 -- insert into tmp2 (seq, value) values (temp_seq.nextval, ' <*************** end ***************> '|| to_char(sysdate, 'dd-mon-yyyy hh:mi:ss') ); commit;
123
124 return l_ret_string;
125
126 END bis_kpi_report;
127
128 FUNCTION bis_measure_report ( p_params IN BIS_PMV_PAGE_PARAMETER_TBL )
129 RETURN VARCHAR2 IS
130 l_ret_string VARCHAR2(8000) := NULL;
131 l_pname VARCHAR2(2000) := NULL;
132 l_pvalue VARCHAR2(2000) := NULL;
133 l_pid VARCHAR2(100) := NULL;
134 l_kpi_name_filter VARCHAR2(1500) := NULL;
135 l_app_nm_filter VARCHAR2(1500) := NULL;
136 l_kpi_source_filter VARCHAR2(1500) := NULL;
137 l_source_filter VARCHAR2(1500) := NULL;
138 l_where_clause VARCHAR2(1500) := NULL;
139 BEGIN
140 FOR i IN 1..p_params.count LOOP
141 l_pname := p_params(i).parameter_name;
142 l_pvalue := BIS_UTILITIES_PVT.filter_quotes(p_params(i).parameter_value);
143 l_pid := p_params(i).parameter_id;
144 IF ( l_pname = 'LVLNM+LVLNM' ) THEN -- Filter according to KPI Indicator
145 IF (l_pvalue <> 'All') THEN
146 l_kpi_name_filter := ' AND bi.indicator_id = '||l_pid ;
147 END IF;
148 ELSIF ( l_pname = 'APPLX+APPLX' ) THEN -- Filter according to Application
149 IF (l_pvalue <> 'All') THEN
150 l_app_nm_filter := ' AND f.application_name = ''' || l_pvalue || ''' ' ;
151 END IF;
152 ELSIF ( l_pname = 'KTYPE+KTYPE' ) THEN -- -- Filter according to Source
153 IF (l_pvalue <> 'All') THEN
154 l_kpi_source_filter := ' AND sys.source = '||l_pid;
155 END IF;
156 END IF;
157 END LOOP;
158 l_where_clause := l_app_nm_filter || l_kpi_source_filter ||l_kpi_name_filter;
159 l_ret_string := '
160 SELECT
161 bi.name KPI_NAME2
162 ,SUBSTR(NVL(bi.description, '' ''),1,180) KPI_DESCRIPTION1
163 ,bi.short_name LEVEL_NAMES
164 ,SUBSTR(NVL(f.application_name, '' ''),1,180) Owning_applicn_name2
165 FROM
166 bis_indicators_vl bi,
167 bis_application_measures measures,
168 bis_indicators indicators,
169 bsc_sys_datasets_vl datasets,
170 bsc_sys_measures sys,
171 fnd_application_vl f
172 WHERE
173 bi.indicator_id = measures.indicator_id AND
174 measures.application_id = f.application_id(+) AND
175 indicators.dataset_id = datasets.dataset_id AND
176 datasets.measure_id1 = sys.measure_id AND
177 indicators.indicator_id = bi.indicator_id '
178 || l_where_clause
179 || ' ORDER BY bi.name, bi.description ';
180 return l_ret_string;
181 END bis_measure_report;
182
183 FUNCTION bis_levels_report ( p_params IN BIS_PMV_PAGE_PARAMETER_TBL )
184 RETURN VARCHAR2 IS
185
186 l_ret_string VARCHAR2(8000) := NULL;
187 l_startdate VARCHAR2(1000) := NULL;
188 l_enddate VARCHAR2(1000) := NULL;
189 l_pname VARCHAR2(2000) := NULL;
190 l_pvalue VARCHAR2(2000) := NULL;
191 l_name VARCHAR(500) := NULL;
192 l_developer VARCHAR2(500) := NULL;
193 l_kpi_nm_filter VARCHAR2(1500) := NULL ; -- ' AND 1 = 1 ';
194 l_lvl_nm_filter VARCHAR2(1500) := NULL ; -- ' AND 1 = 1 ';
195 l_app_nm_filter VARCHAR2(1500) := NULL ; -- ' AND 1 = 1 ';
196 l_dim_nm_filter VARCHAR2(1500) := NULL ; -- ' AND 1 = 1 ';
197 l_lvl_desc_filter VARCHAR2(1500) := NULL ; -- ' AND 1 = 1 ';
198 l_order_by VARCHAR2(150) := NULL;
199 l_source_filter VARCHAR2(1500) := NULL;
200 l_where_clause VARCHAR2(1500) := NULL;
201 l_group_by VARCHAR2(150) := ' GROUP BY bd.name, bl.name, bl.description, bl.level_values_view_name ';
202 l_src_filter VARCHAR2(1500) := NULL;
203 l_dyn_table VARCHAR2(50) := NULL;
204 l_kpi_table_names VARCHAR2(100) := NULL;
205 l_kpi_where_clause VARCHAR2(100) := NULL;
206
207 BEGIN
208
209 -- insert into tmp2 (seq, value) values (temp_seq.nextval, ' <*************** begin ***************> '|| to_char(sysdate, 'dd-mon-yyyy hh:mi:ss') ); commit;
210
211 -- LEVEL_NAME1 KPI_NM+KPI_NM APPLX+APPLX LEVEL_DESCRIPTION
212
213 FOR i IN 1..p_params.count LOOP
214
215 l_pname := p_params(i).parameter_name;
216 l_pvalue := BIS_UTILITIES_PVT.filter_quotes(p_params(i).parameter_value);
217
218 -- insert into tmp2 (seq, value) values (temp_seq.nextval, '(<---------------- pname ->'||l_pname||'<-pvalue->'||l_pvalue||'<------------------>)' ); commit;
219
220 IF ( l_pname = 'LEVEL_NAME1' ) THEN
221
222 IF (l_pvalue <> 'All') THEN
223 l_lvl_nm_filter := ' AND UPPER(bl.name) LIKE UPPER( ''' || '%' || l_pvalue || '%' || ''') ' ;
224 END IF;
225
226 ELSIF ( l_pname = 'LVL_SRC_TYP+LVL_SRC_TYP' ) THEN
227
228 IF (l_pvalue <> 'All') THEN
229 l_src_filter := ' AND bl.source = ''' || l_pvalue || ''' ';
230 END IF;
231
232 ELSIF ( l_pname = 'DIM_NMX+DIM_NMX' ) THEN
233
234 IF (l_pvalue <> 'All') THEN
235 l_dim_nm_filter := ' AND bd.name = ''' || l_pvalue || ''' ' ;
236 END IF;
237
238 ELSIF ( l_pname = 'KPI_NM+KPI_NM' ) THEN
239
240 IF (l_pvalue <> 'All') THEN
241 l_kpi_nm_filter := ' AND bi.name = ''' || l_pvalue || ''' ';
242 l_kpi_table_names := ' , bis_indicators_vl bi ' ||
243 ' , BIS_IND_TL_LVL_V bitlv ';
244 l_kpi_where_clause := ' AND bitlv.indicator_id = bi.indicator_id ' ||
245 ' AND bitlv.level_id = bl.level_id ';
246 END IF;
247
248 ELSIF ( l_pname = 'APPLX+APPLX' ) THEN
249
250 IF (l_pvalue <> 'All') THEN
251 l_app_nm_filter := ' AND bl.application_id = f.application_id AND f.application_name = ''' || l_pvalue || ''' ' ;
252 l_dyn_table := ' , fnd_application_vl f ' ;
253 END IF;
254
255 ELSIF ( l_pname = 'LEVEL_DESCRIPTION' ) THEN
256
257 IF (l_pvalue <> 'All') THEN
258 l_lvl_desc_filter := ' AND UPPER(bl.description) LIKE UPPER( ''' || '%' || l_pvalue || '%' || ''') ' ;
259 END IF;
260
261 ELSIF (l_pname LIKE '%ORDERBY%') THEN
262
263 -- Select * from tmp2 order by seq desc;
264 -- l_order_by := ' WHERE ';
265
266 IF (l_pvalue LIKE '%DIMENSION_NAME1%') THEN
267 l_order_by := ' DIMENSION_NAME1, LEVEL_NAME , LEVEL_DESCRIPTION ';
268 ELSIF (l_pvalue LIKE '%APPLICATION_SHORT_NAME%') THEN
269 l_order_by := ' APPLICATION_SHORT_NAME ';
270 ELSIF (l_pvalue LIKE '%APPLICATION_NAME%') THEN
271 l_order_by := ' APPLICATION_NAME , DIMENSION_NAME1, LEVEL_NAME , LEVEL_DESCRIPTION ';
272 ELSIF (l_pvalue LIKE '%DIMENSION_SHORT_NAME%') THEN
273 l_order_by := ' DIMENSION_SHORT_NAME , DIMENSION_NAME1, LEVEL_NAME , LEVEL_DESCRIPTION ';
274 ELSIF (l_pvalue LIKE '%DIMENSION_DESCRIPTION%') THEN
275 l_order_by := ' DIMENSION_DESCRIPTION , DIMENSION_NAME1, LEVEL_NAME ';
276 ELSIF (l_pvalue LIKE '%LEVEL_SHORT_NAME%') THEN
277 l_order_by := ' LEVEL_SHORT_NAME , DIMENSION_NAME1, LEVEL_NAME , LEVEL_DESCRIPTION ';
278 ELSIF (l_pvalue LIKE '%LEVEL_NAME%') THEN
279 l_order_by := ' LEVEL_NAME , DIMENSION_NAME1, LEVEL_DESCRIPTION';
280 ELSIF (l_pvalue LIKE '%LEVEL_DESCRIPTION%') THEN
281 l_order_by := ' LEVEL_DESCRIPTION , DIMENSION_NAME1, LEVEL_NAME ';
282 ELSIF (l_pvalue LIKE '%LEVEL_VALUES_VIEW_NAME%') THEN
283 l_order_by := ' LEVEL_VALUES_VIEW_NAME , DIMENSION_NAME1, LEVEL_NAME , LEVEL_DESCRIPTION ';
284 ELSIF (l_pvalue LIKE '%LEVEL_VAL_SOURCE%') THEN
285 l_order_by := ' LEVEL_VAL_SOURCE , DIMENSION_NAME1, LEVEL_NAME , LEVEL_DESCRIPTION ';
286 ELSE
287 l_order_by := ' DIMENSION_NAME1, LEVEL_NAME , LEVEL_DESCRIPTION ';
288 END IF;
289
290 IF (l_pvalue LIKE '%DESC') THEN
291 l_order_by := l_order_by || ' DESC ' ;
292 ELSE
293 IF (l_pvalue IS NOT NULL ) THEN
294 l_order_by := l_order_by || ' ' ;
295 END IF;
296 END IF;
297
298 -- insert into tmp2 (seq, value) values (temp_seq.nextval, ' l_order_by: ' || l_order_by ); commit;
299
300 END IF;
301 END LOOP;
302
303 l_where_clause := l_kpi_where_clause || l_lvl_nm_filter || l_kpi_nm_filter || l_app_nm_filter
304 || l_lvl_desc_filter || l_src_filter || l_dim_nm_filter ;
305
306 -- decode (product_id, 163, ''BIS'', 205, ''BSC'')
307 l_ret_string := '
308 SELECT
309 SUBSTR(bd.name,1,180) Dimension_name2
310 , SUBSTR(bl.name,1,180) Level_name2
311 , SUBSTR(NVL(bl.description, '' ''),1,180) Level_description2
312 , NVL(bl.level_values_view_name, '' '') Level_value_Source_view2
313 FROM
314 Bis_dimensions_vl bd
315 , bis_levels_vl bl
316 '
317 || l_kpi_table_names
318 || l_dyn_table ||
319 '
320 WHERE
321 bl.dimension_id = bd.dimension_id
322 AND NVL(bd.hide_in_design, ''F'') <> ''T''
323 AND NVL(bl.hide_in_design, ''F'') <> ''T''
324 '
325 || l_where_clause
326 || l_group_by
327 || ' ORDER BY bd.name, bl.name ' ;
328
329 -- insert into tmp2 (seq, value) values (temp_seq.nextval, l_ret_string); commit;
330
331 -- insert into tmp2 (seq, value) values (temp_seq.nextval, ' <*************** end ***************> '|| to_char(sysdate, 'dd-mon-yyyy hh:mi:ss') ); commit;
332
333 return l_ret_string;
334
335 END bis_levels_report;
336
337
338 FUNCTION bis_levels_details_report ( p_params IN BIS_PMV_PAGE_PARAMETER_TBL )
339 RETURN VARCHAR2 IS
340
341 l_ret_string VARCHAR2(8000) := NULL;
342 l_startdate VARCHAR2(1000) := NULL;
343 l_enddate VARCHAR2(1000) := NULL;
344 l_pname VARCHAR2(2000) := NULL;
345 l_pvalue VARCHAR2(2000) := NULL;
346 l_name VARCHAR(500) := NULL;
347 l_developer VARCHAR2(500) := NULL;
348 l_lvl_nm_filter VARCHAR2(1500) := NULL;
349 l_kpi_nm_filter VARCHAR2(1500) := NULL;
350 l_dim_nm_filter VARCHAR2(1500) := NULL;
351 l_dim_gen_filter VARCHAR2(1500) := NULL;
352 l_src_filter VARCHAR2(1500) := NULL;
353 l_lvl_desc_filter VARCHAR2(1500) := NULL;
354 l_app_nm_filter VARCHAR2(1500) := NULL;
355 l_order_by VARCHAR2(150) := NULL;
356 l_source_filter VARCHAR2(1500) := NULL;
357 l_where_clause VARCHAR2(1500) := NULL;
358 l_group_by VARCHAR2(1500) := ' GROUP BY bl.name, bl.short_name, aa.attribute_label_long, bl.description, bl.source, bl.level_id' ;
359 l_dyn_table VARCHAR2(50) := NULL;
360
361 BEGIN
362
363 -- insert into tmp2 (seq, value) values (temp_seq.nextval, ' <*************** begin ***************> '); commit;
364
365 FOR i IN 1..p_params.count LOOP
366
367 l_pname := p_params(i).parameter_name;
368 l_pvalue := BIS_UTILITIES_PVT.filter_quotes(p_params(i).parameter_value);
369
370 -- insert into tmp2 (seq, value) values (temp_seq.nextval, '(<---------------- pname ->'||l_pname||'<-pvalue->'||l_pvalue||'<------------------>)' ); commit;
371
372 IF ( l_pname = 'LEVEL_NAME1' ) THEN
373
374 IF (l_pvalue <> 'All') THEN
375 l_lvl_nm_filter := ' AND UPPER(bl.name) LIKE UPPER( ''' || '%' || l_pvalue || '%' || ''') ' ;
376 END IF;
377
378 ELSIF ( l_pname = 'LVL_SRC_TYP+LVL_SRC_TYP' ) THEN
379
380 IF (l_pvalue <> 'All') THEN
381 l_src_filter := ' AND bl.source = ''' || l_pvalue || ''' ';
385
382 END IF;
383
384 ELSIF ( l_pname = 'DIM_NMX+DIM_NMX' ) THEN
386 IF (l_pvalue <> 'All') THEN
387 l_dim_nm_filter := ' AND bd.name = ''' || l_pvalue || ''' ' ;
388 END IF;
389
390 ELSIF ( l_pname = 'LEVEL_DESCRIPTION1' ) THEN
391
392 IF (l_pvalue <> 'All') THEN
393 l_lvl_desc_filter := ' AND UPPER(bl.description) LIKE UPPER( ''' || '%' || l_pvalue || '%' || ''') ' ;
394 END IF;
395
396 ELSIF ( l_pname = 'APPLX+APPLX' ) THEN
397
398 IF (l_pvalue <> 'All') THEN
399 l_app_nm_filter := ' AND bl.application_id = f.application_id AND f.application_name = ''' || l_pvalue || ''' ' ;
400 l_dyn_table := ' , fnd_application_vl f ' ;
401 END IF;
402
403 ELSIF (l_pname LIKE '%ORDERBY%') THEN
404
405 -- Select * from tmp2 order by seq desc;
406
407 -- l_order_by := ' WHERE ';
408
409 IF ( l_pvalue LIKE '%NAME%DESKRIPTION%SHORT_NAME%' ) THEN
410 l_order_by := ' NAME ';
411 ELSIF (l_pvalue LIKE '%SHORT_NAME%') THEN
412 l_order_by := ' SHORT_NAME ';
413 ELSIF (l_pvalue LIKE '%DESKRIPTION%') THEN
414 l_order_by := ' DESKRIPTION ';
415 ELSIF (l_pvalue LIKE '%NAME%') THEN
416 l_order_by := ' NAME ';
417 END IF;
418
419 IF (l_pvalue LIKE '%DESC%') THEN
420 l_order_by := l_order_by || ' DESC ' ;
421 ELSE
422 IF (l_pvalue IS NOT NULL ) THEN
423 l_order_by := l_order_by || ' ' ;
424 END IF;
425 END IF;
426
427 -- insert into tmp2 (seq, value) values (temp_seq.nextval, ' l_order_by: ' || l_order_by ); commit;
428
429 END IF;
430 END LOOP;
431
432 l_where_clause := l_lvl_nm_filter || l_src_filter || l_dim_nm_filter
433 || l_lvl_desc_filter || l_app_nm_filter;
434
435 -- decode (product_id, 163, ''BIS'', 205, ''BSC'')
436 l_ret_string := '
437 SELECT
438 SUBSTR(bl.name,1,180) Level_name
439 , bl.short_name Level_Internal_name
440 , aa.attribute_label_long Level_Short_name
441 , SUBSTR(NVL(bl.description, '' ''),1,180) Level_description
442 , bl.source Level_value_Source_type1
443 , SUBSTR(NVL(BIS_DIMS_AND_LEVELS_PKG.get_kpi_names(bl.level_id), '' ''),1,180) Kpi_names
444 FROM
445 bis_levels_vl bl
446 , ak_attributes_vl aa
447 , bis_dimensions_vl bd
448 '
449 || l_dyn_table ||
450 '
451 WHERE
452 bl.attribute_code = aa.attribute_code
453 AND bl.dimension_id = bd.dimension_id
454 AND NVL(bd.hide_in_design, ''F'') <> ''T''
455 AND NVL(bl.hide_in_design, ''F'') <> ''T''
456 '
457 || l_where_clause
458 || l_group_by
459 || '
460 ORDER BY bl.name, bl.short_name, aa.attribute_label_long
461 , bl.description, bl.source ' ;
462
463
464 -- insert into tmp2 (seq, value) values (temp_seq.nextval, l_ret_string); commit;
465
466 -- insert into tmp2 (seq, value) values (temp_seq.nextval, ' <*************** end ***************> '); commit;
467
468 return l_ret_string;
469
470 END bis_levels_details_report;
471
472
473
474 FUNCTION bis_dimensions_report ( p_params IN BIS_PMV_PAGE_PARAMETER_TBL )
475 RETURN VARCHAR2 IS
476
477 l_ret_string VARCHAR2(8000) := NULL;
478 l_startdate VARCHAR2(1000) := NULL;
479 l_enddate VARCHAR2(1000) := NULL;
480 l_pname VARCHAR2(2000) := NULL;
481 l_pvalue VARCHAR2(2000) := NULL;
482 l_name VARCHAR(500) := NULL;
483 l_developer VARCHAR2(500) := NULL;
484 l_lvl_sht_nm_filter VARCHAR2(1500) := NULL;
485 l_dim_nm_filter VARCHAR2(1500) := NULL;
486 l_dim_sht_nm_filter VARCHAR2(1500) := NULL;
487 l_app_nm_filter VARCHAR2(1500) := NULL;
488 l_dim_desc_filter VARCHAR2(1500) := NULL;
489 l_order_by VARCHAR2(150) := NULL;
490 l_group_by VARCHAR2(150) := ' group by bd.name, bd.description, bd.short_name, bl.source ' ;
491 l_source_filter VARCHAR2(1500) := NULL;
492 l_where_clause VARCHAR2(1500) := NULL;
493 l_group_by VARCHAR2(150) := ' GROUP BY bd.name, bl.name, bl.description, bl.level_values_view_name ';
494 l_dyn_table VARCHAR2(50) := NULL;
495
496 BEGIN
497
498 -- -- insert into tmp2 (seq, value) values (temp_seq.nextval, ' <*************** begin ***************> '); commit;
499
500 FOR i IN 1..p_params.count LOOP
501
502 l_pname := p_params(i).parameter_name;
503 l_pvalue := BIS_UTILITIES_PVT.filter_quotes(p_params(i).parameter_value);
504
505 -- insert into tmp2 (seq, value) values (temp_seq.nextval, '(<---------------- pname ->'||l_pname||'<-pvalue->'||l_pvalue||'<------------------>)' ); commit;
506
507 IF ( l_pname = 'DIMENSION_NAME' ) THEN
508
509 IF (l_pvalue <> 'All') THEN
510 l_dim_nm_filter := ' AND UPPER(bd.name) LIKE UPPER(''' || '%' || l_pvalue || '%' || ''' ) ' ;
511 END IF;
512
513 ELSIF ( l_pname = 'APPLX+APPLX' ) THEN
514
515 IF (l_pvalue <> 'All') THEN
516 l_app_nm_filter := ' AND bd.application_id = f.application_id AND f.application_name = ''' || l_pvalue || ''' ' ;
517 l_dyn_table := ' , fnd_application_vl f ' ;
518 END IF;
519
520 ELSIF ( l_pname = 'DIMENSION_DESCRIPTION' ) THEN
521
522 IF (l_pvalue <> 'All') THEN
523 l_dim_desc_filter := ' AND UPPER(bd.description) LIKE UPPER(''' || '%' || l_pvalue || '%' || ''' ) ' ;
524 END IF;
525
526 ELSIF (l_pname LIKE '%ORDERBY%') THEN
527
528 -- Select * from tmp2 order by seq desc;
529
530 -- l_order_by := ' WHERE ';
531
532 IF ( l_pvalue LIKE '%NAME%DESKRIPTION%SHORT_NAME%' ) THEN
533 l_order_by := ' NAME ';
534 ELSIF (l_pvalue LIKE '%SHORT_NAME%') THEN
535 l_order_by := ' SHORT_NAME ';
536 ELSIF (l_pvalue LIKE '%DESKRIPTION%') THEN
537 l_order_by := ' DESKRIPTION ';
538 ELSIF (l_pvalue LIKE '%NAME%') THEN
539 l_order_by := ' NAME ';
540 END IF;
541
542 IF (l_pvalue LIKE '%DESC%') THEN
543 l_order_by := l_order_by || ' DESC ' ;
544 ELSE
545 IF (l_pvalue IS NOT NULL ) THEN
546 l_order_by := l_order_by || ' ' ;
547 END IF;
548 END IF;
549
550 -- insert into tmp2 (seq, value) values (temp_seq.nextval, ' l_order_by: ' || l_order_by ); commit;
551
552 END IF;
553 END LOOP;
554
555 l_where_clause := l_dim_nm_filter || l_app_nm_filter || l_dim_desc_filter ;
556
557
558 -- decode (product_id, 163, ''BIS'', 205, ''BSC'')
559 l_ret_string := '
560 SELECT
561 SUBSTR(bd.name,1,180) Dimension_name1
562 , SUBSTR(NVL(bd.description, '' ''),1,180) Dimension_description1
563 , bd.short_name Dimension_short_name1
564 FROM bis_dimensions_vl bd
565 '
566 || l_dyn_table ||
567 '
568 WHERE NVL(bd.hide_in_design, ''F'') <> ''T''
569 '
570 || l_where_clause
571 || '
572 ORDER BY bd.name, bd.description, bd.short_name
573 ';
574
575
576 -- insert into tmp2 (seq, value) values (temp_seq.nextval, l_ret_string); commit;
577
578 -- insert into tmp2 (seq, value) values (temp_seq.nextval, ' <*************** end ***************> '); commit;
579
580 return l_ret_string;
581
582 END bis_dimensions_report;
583
584
585 FUNCTION get_level_names ( kpi_id IN NUMBER )
586 RETURN VARCHAR2 IS
587
588 l_lvl_names VARCHAR2(32000) := NULL;
589 l_lvl_name bis_levels_vl.name%TYPE;
590
591 CURSOR cur_get_level_name IS
592 SELECT name
593 FROM bis_levels_vl bl
594 , BIS_IND_TL_LVL_V bitlv
595 WHERE bitlv.level_id = bl.level_id
596 AND bitlv.indicator_id = kpi_id;
597
598 BEGIN
599
600 IF cur_get_level_name%ISOPEN THEN
601 CLOSE cur_get_level_name;
602 END IF;
603
604 FOR c_rec in cur_get_level_name LOOP
605 l_lvl_name := c_rec.name;
606 IF (l_lvl_names IS NULL) THEN
607 l_lvl_names := l_lvl_name;
608 ELSE
609 l_lvl_names := l_lvl_names || ', ' || l_lvl_name;
610 END IF;
611 END LOOP;
612
613 IF cur_get_level_name%ISOPEN THEN
614 CLOSE cur_get_level_name;
615 END IF;
616
617 RETURN l_lvl_names;
618
619 EXCEPTION
620 WHEN OTHERS THEN
621 IF cur_get_level_name%ISOPEN THEN
622 CLOSE cur_get_level_name;
623 END IF;
624 RETURN ' ';
625
626 END get_level_names;
627
628
629 FUNCTION get_kpi_names ( lvl_id IN NUMBER )
630 RETURN VARCHAR2 IS
631
632 l_kpi_names VARCHAR2(32000) := NULL;
633 l_kpi_name bis_indicators_vl.name%TYPE;
634
635 CURSOR cur_get_kpi_name IS
636 SELECT DISTINCT name
637 FROM bis_indicators_vl bi
638 , BIS_IND_TL_LVL_V bitlv
639 WHERE bitlv.indicator_id = bi.indicator_id
640 AND bitlv.level_id = lvl_id;
641
642 BEGIN
643
644 IF cur_get_kpi_name%ISOPEN THEN
645 CLOSE cur_get_kpi_name;
646 END IF;
647
648 FOR c_rec in cur_get_kpi_name LOOP
649 l_kpi_name := c_rec.name;
650 IF (l_kpi_names IS NULL) THEN
651 l_kpi_names := l_kpi_name;
652 ELSE
653 l_kpi_names := l_kpi_names || ', ' || l_kpi_name;
654 END IF;
655 END LOOP;
656
657 IF cur_get_kpi_name%ISOPEN THEN
658 CLOSE cur_get_kpi_name;
659 END IF;
660
661 RETURN l_kpi_names;
662
663 EXCEPTION
664 WHEN OTHERS THEN
665 IF cur_get_kpi_name%ISOPEN THEN
666 CLOSE cur_get_kpi_name;
667 END IF;
668 RETURN ' ';
669
670 END get_kpi_names;
671
672
673 END BIS_DIMS_AND_LEVELS_PKG;