[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_CPM_CP_ACT_PKG
Source
1 PACKAGE BODY ISC_DBI_CPM_CP_ACT_PKG AS
2 /* $Header: ISCRGB7B.pls 120.0 2005/05/25 17:41:50 appldev noship $ */
3
4 FUNCTION simplify_all (
5 p_param in varchar2
6 ) return varchar2 is
7 begin
8 if ( p_param is null or
9 p_param = '' or
10 upper(p_param) = 'ALL')
11 then return 'All';
12 else return p_param;
13 end if;
14 end simplify_all;
15
16 FUNCTION get_parameter_value (
17 p_param_rec in bis_pmv_page_parameter_rec
18 ) return varchar2 is
19 begin
20 case p_param_rec.parameter_name
21 when 'VIEW_BY' then return p_param_rec.parameter_value;
22 when 'PERIOD_TYPE' then return p_param_rec.parameter_value;
23 when 'ITEM+ENI_ITEM_VBH_CAT' then return simplify_all(p_param_rec.parameter_value);
24 when 'ITEM+ENI_ITEM_PROD_LEAF_CAT' then return simplify_all(p_param_rec.parameter_value);
25 when 'ITEM+ENI_ITEM' then return simplify_all(p_param_rec.parameter_value);
26 when 'CUSTOMER+PROSPECT' then return simplify_all(p_param_rec.parameter_value);
27 when 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS'
28 then return simplify_all(p_param_rec.parameter_value);
29 when 'CURRENCY+FII_CURRENCIES' then return p_param_rec.parameter_id;
30 else return null;
31 end case;
32 end get_parameter_value;
33
34 FUNCTION biv_column_name (
35 p_param in varchar2
36 ) return varchar2 is
37 l_param varchar2(1000);
38 begin
39 l_param := p_param;
40 l_param := replace(l_param, 'fact.top_node_flag', 'fact.vbh_top_node_flag');
41 l_param := replace(l_param, 'fact.parent_id', 'fact.vbh_parent_category_id');
42 l_param := replace(l_param, 'fact.imm_child_id', 'fact.vbh_child_category_id');
43 l_param := replace(l_param, 'fact.item_category_id', 'fact.vbh_category_id');
44 return l_param;
45 end biv_column_name;
46
47 PROCEDURE Get_Sql ( p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
48 x_custom_sql OUT NOCOPY VARCHAR2,
49 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
50
51 l_stmt VARCHAR2(32000);
52
53 l_view_by VARCHAR2(32000);
54 l_prod_cat VARCHAR2(32000) := 'All';
55 l_leaf_cat VARCHAR2(32000) := 'All';
56 l_prod VARCHAR2(32000) := 'All';
57 l_cust VARCHAR2(32000) := 'All';
58 l_class VARCHAR2(32000) := 'All';
59 l_curr VARCHAR2(32000);
60
61 l_curr_g VARCHAR2(100) := '''FII_GLOBAL1''';
62 l_curr_g1 VARCHAR2(100) := '''FII_GLOBAL2''';
63 sfx VARCHAR2(100);
64
65 l_drill_prod_cat VARCHAR2(32000);
66 l_drill_active VARCHAR2(32000);
67 l_drill_leaf_cat VARCHAR2(32000);
68 l_dimension_id VARCHAR2(32000);
69 l_dimension_view VARCHAR2(32000);
70 l_dim_where_clause VARCHAR2(32000);
71
72 l_prod_where VARCHAR2(32000) := '';
73 l_leaf_cat_where VARCHAR2(32000) := '';
74 l_prod_cat_where VARCHAR2(32000) := '';
75 l_cust_where VARCHAR2(32000) := '';
76 l_class_where VARCHAR2(32000) := '';
77 l_biv_flag_where VARCHAR2(32000) := '';
78
79 l_prod_cat_from VARCHAR2(32000) := '';
80
81 l_item_cat_flag NUMBER;
82 l_cust_flag NUMBER;
83 l_biv_flag NUMBER;
84
85 l_mv1 VARCHAR2(1000);
86 l_mv2 VARCHAR2(1000);
87 l_mv3 VARCHAR2(1000);
88 l_mv4 VARCHAR2(1000);
89
90 l_measures VARCHAR2(32000);
91
92 l_custom_rec BIS_QUERY_ATTRIBUTES;
93
94 begin
95
96 for i in 1..p_param.count
97 loop
98 case p_param(i).parameter_name
99 when 'VIEW_BY' then l_view_by := get_parameter_value(p_param(i));
100 when 'ITEM+ENI_ITEM_VBH_CAT' then l_prod_cat := get_parameter_value(p_param(i));
101 when 'ITEM+ENI_ITEM_PROD_LEAF_CAT' then l_leaf_cat := get_parameter_value(p_param(i));
102 when 'ITEM+ENI_ITEM' then l_prod := get_parameter_value(p_param(i));
103 when 'CUSTOMER+PROSPECT' then l_cust := get_parameter_value(p_param(i));
104 when 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS'
105 then l_class := get_parameter_value(p_param(i));
106 when 'CURRENCY+FII_CURRENCIES' then l_curr := get_parameter_value(p_param(i));
107 else null;
108 end case;
109 end loop;
110
111 if (l_curr = l_curr_g1) then
112 sfx := '_g1';
113 else
114 sfx := '_g';
115 end if;
116
117 if (l_view_by = 'CUSTOMER+PROSPECT') then
118 l_drill_prod_cat := 'null';
119 l_drill_active := '''pFunctionName=ISC_DBI_CPM_SPT_COMP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+PROSPECT&pParamIds=Y''';
120 l_drill_leaf_cat := 'null';
121 l_dimension_id := 'fact.customer_id';
122 l_dimension_view := 'ASO_BI_PROSPECT_V';
123 l_dim_where_clause := '';
124
125 elsif (l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') then
126 l_drill_prod_cat := 'null';
127 l_drill_active := 'null';
128 l_drill_leaf_cat := 'null';
129 l_dimension_id := 'fact.class_code';
130 l_dimension_view := 'FII_PARTNER_MKT_CLASS_V';
131 l_dim_where_clause := '';
132
133 elsif (l_view_by = 'ITEM+ENI_ITEM') then
134 l_drill_prod_cat := 'null';
135 l_drill_active := '''pFunctionName=ISC_DBI_CPM_SPT_COMP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+PROSPECT&pParamIds=Y''';
136 l_drill_leaf_cat := 'null';
137 l_dimension_id := 'fact.product_id';
138 l_dimension_view := 'ENI_OLTP_ITEM_STAR';
139 l_dim_where_clause := '
140 AND dim_view.master_id is null';
141
142 elsif (l_view_by = 'ITEM+ENI_ITEM_PROD_LEAF_CAT') then
143 l_drill_prod_cat := 'null';
144 l_drill_active := '''pFunctionName=ISC_DBI_CPM_SPT_COMP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+PROSPECT&pParamIds=Y''';
145 l_drill_leaf_cat := '''pFunctionName=ISC_DBI_CPM_CP_ACT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM&pParamIds=Y''';
146 l_dimension_id := 'fact.item_category_id';
147 l_dimension_view := 'ENI_ITEM_PROD_LEAF_CAT_V';
148 l_dim_where_clause := '';
149
150 else -- l_view_by = 'ITEM+ENI_ITEM_VBH_CAT'
151 l_drill_prod_cat := 'decode(dim_view.leaf_node_flag, ''Y'',
152 ''pFunctionName=ISC_DBI_CPM_CP_ACT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM&pParamIds=Y'',
153 ''pFunctionName=ISC_DBI_CPM_CP_ACT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'')';
154 l_drill_active := 'decode(dim_view.leaf_node_flag, ''Y'',
155 ''pFunctionName=ISC_DBI_CPM_SPT_COMP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=CUSTOMER+PROSPECT&pParamIds=Y'', null)';
156 l_drill_leaf_cat := 'null';
157 if (l_prod_cat = 'All') then
158 l_dimension_id := 'eni_cat.parent_id';
159 else
160 l_dimension_id := 'eni_cat.imm_child_id';
161 end if;
162 l_dimension_view := 'ENI_ITEM_VBH_NODES_V';
163 l_dim_where_clause := '
164 AND dim_view.parent_id = dim_view.child_id';
165 end if;
166
167 if (l_cust <> 'All') then
168 l_cust_where := '
169 AND fact.customer_id in (&CUSTOMER+PROSPECT)';
170 end if;
171
172 if (l_class <> 'All') then
173 l_class_where := '
174 AND fact.class_code in (&FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
175 end if;
176
177 if (l_prod <> 'All') then
178 l_prod_where := '
179 AND fact.product_id in (&ITEM+ENI_ITEM)';
180 else
181 if (l_leaf_cat <> 'All') then
182 l_leaf_cat_where := '
183 AND fact.item_category_id = &ITEM+ENI_ITEM_PROD_LEAF_CAT';
184 else
185 if (l_prod_cat <> 'All') then
186 l_prod_cat_from := ',
187 ENI_DENORM_HIERARCHIES eni_cat,
188 MTL_DEFAULT_CATEGORY_SETS mdcs';
189 l_prod_cat_where := '
190 AND fact.item_category_id = eni_cat.child_id
191 AND eni_cat.parent_id = &ITEM+ENI_ITEM_VBH_CAT
192 AND eni_cat.dbi_flag = ''Y''
193 AND eni_cat.object_type = ''CATEGORY_SET''
194 AND eni_cat.object_id = mdcs.category_set_id
195 AND mdcs.functional_area_id = 11';
196 end if;
197 end if;
198 end if;
199
200 if (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') then
201 l_prod_cat_from := ',
202 ENI_DENORM_HIERARCHIES eni_cat,
203 MTL_DEFAULT_CATEGORY_SETS mdcs';
204 l_prod_cat_where := '
205 AND fact.item_category_id = eni_cat.child_id
206 AND eni_cat.dbi_flag = ''Y''
207 AND eni_cat.object_type = ''CATEGORY_SET''
208 AND eni_cat.object_id = mdcs.category_set_id
209 AND mdcs.functional_area_id = 11';
210 if (l_prod_cat = 'All') then
211 l_prod_cat_where := l_prod_cat_where||'
212 AND eni_cat.top_node_flag = ''Y''';
213 else
214 l_prod_cat_where := l_prod_cat_where||'
215 AND eni_cat.parent_id = &ITEM+ENI_ITEM_VBH_CAT';
216 end if;
217 end if;
218
219 if (l_prod <> 'All' or l_view_by = 'ITEM+ENI_ITEM') then
220 l_item_cat_flag := 0;
221 else
222 if (l_leaf_cat <> 'All' or l_view_by = 'ITEM+ENI_ITEM_PROD_LEAF_CAT' or
223 l_prod_cat <> 'All' or l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') then
224 l_item_cat_flag := 1;
225 else
226 l_item_cat_flag := 3;
227 end if;
228 end if;
229
230 if (l_cust = 'All') then
231 if (l_view_by = 'CUSTOMER+PROSPECT')
232 then l_cust_flag := 0; -- customer
233 elsif (l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS')
234 then l_cust_flag := 1; -- customer classification
235 else
236 if (l_class = 'All')
237 then l_cust_flag := 3; -- all
238 else l_cust_flag := 1; -- customer classification
239 end if;
240 end if;
241 else
242 l_cust_flag := 0; -- customer
243 end if;
244
245 if (((l_prod <> 'All' or l_view_by = 'ITEM+ENI_ITEM') and (l_cust <> 'All' or l_view_by = 'CUSTOMER+PROSPECT'))
246 or
247 ((l_prod <> 'All' or l_view_by = 'ITEM+ENI_ITEM') and (l_class <> 'All' or l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS')))
248 then
249 l_biv_flag := 0;
250 elsif (l_cust <> 'All' or l_view_by = 'CUSTOMER+PROSPECT') then
251 l_biv_flag := 2;
252 elsif (l_class <> 'All' or l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') then
253 l_biv_flag := 4;
254 elsif (l_prod <> 'All' or l_view_by = 'ITEM+ENI_ITEM') then
255 l_biv_flag := 1;
256 else
257 l_biv_flag := 5;
258 end if;
259
260 if (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' and l_leaf_cat = 'All' and l_prod = 'All' and l_cust = 'All' and l_class = 'All') then
261 l_mv1 := 'ISC_DBI_CPM_003_MV';
262 l_mv2 := 'ISC_DBI_CPM_004_MV';
263 l_mv3 := 'ISC_DBI_CPM_005_MV';
264 l_mv4 := 'BIV_ACT_H_SUM_MV';
265 l_prod_cat_from := '';
266 if (l_prod_cat = 'All') then
267 l_dimension_id := 'fact.parent_id';
268 l_prod_cat_where := '
269 AND fact.top_node_flag = ''Y''';
270 else
271 l_dimension_id := 'fact.imm_child_id';
272 l_prod_cat_where := '
273 AND fact.parent_id = &ITEM+ENI_ITEM_VBH_CAT';
274 end if;
275 else
276 l_mv1 := 'ISC_DBI_CPM_000_MV';
277 l_mv2 := 'ISC_DBI_CPM_001_MV';
278 l_mv3 := 'ISC_DBI_CPM_002_MV';
279 l_mv4 := 'BIV_ACT_SUM_MV';
280 l_biv_flag_where := '
281 AND fact.grp_id = :ISC_BIV_FLAG';
282 end if;
283
284 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
285 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
286
287 l_measures := 'isc_measure_1, isc_measure_2, isc_measure_3, isc_measure_4, isc_measure_5,
288 isc_measure_6, isc_measure_7, isc_measure_8, isc_measure_9, isc_measure_10,
289 isc_measure_11, isc_measure_12, isc_measure_13, isc_measure_14, isc_measure_15,
290 isc_measure_16, isc_measure_17, isc_measure_19, isc_measure_20,
291 isc_measure_21, isc_measure_22, isc_measure_23, isc_measure_24, isc_measure_25,
292 isc_measure_26, isc_measure_27, isc_measure_28, isc_measure_29, isc_measure_30,
293 isc_measure_31';
294
295 l_stmt := '
296 SELECT /*+ LEADING(a) INDEX(dim_view) */ dim_view.value VIEWBY,
297 dim_view.id VIEWBYID,
298 '||l_drill_prod_cat||' ISC_ATTRIBUTE_1,
299 '||l_drill_leaf_cat||' ISC_ATTRIBUTE_2,
300 '||l_drill_active||' ISC_ATTRIBUTE_3,
301 '||l_measures||'
302 FROM
303 (SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, dimension_id)) - 1 RNK,
304 dimension_id,
305 '||l_measures||'
306 FROM (SELECT c.dimension_id DIMENSION_ID,
307 c.curr_book ISC_MEASURE_1,
308 (c.curr_book - c.prev_book)
309 / decode(c.prev_book, 0, null, abs(c.prev_book))
310 * 100 ISC_MEASURE_2,
311 c.curr_serv ISC_MEASURE_3,
312 (c.curr_serv - c.prev_serv)
313 / decode(c.prev_serv, 0, null, abs(c.prev_serv))
314 * 100 ISC_MEASURE_4,
315 c.curr_active ISC_MEASURE_5,
316 (c.curr_active - c.prev_active)
317 / decode(c.prev_active, 0, null, abs(c.prev_active))
318 * 100 ISC_MEASURE_6,
319 c.curr_active
320 / decode(sum(c.curr_active) over (), 0, null,
321 sum(c.curr_active) over ())
322 * 100 ISC_MEASURE_7,
323 c.curr_active
324 / decode(sum(c.curr_active) over (), 0, null,
325 sum(c.curr_active) over ()) * 100
326 - c.prev_active
327 / decode(sum(c.prev_active) over (), 0, null,
328 sum(c.prev_active) over ()) * 100 ISC_MEASURE_8,
329 0 ISC_MEASURE_9,
330 0 ISC_MEASURE_10,
331 sum(c.curr_book) over () ISC_MEASURE_11,
332 (sum(c.curr_book) over () - sum(c.prev_book) over ())
333 / decode(sum(c.prev_book) over (), 0, null,
334 abs(sum(c.prev_book) over ()))
335 * 100 ISC_MEASURE_12,
336 sum(c.curr_serv) over () ISC_MEASURE_13,
337 (sum(c.curr_serv) over () - sum(c.prev_serv) over ())
338 / decode(sum(c.prev_serv) over (), 0, null,
339 abs(sum(c.prev_serv) over ()))
340 * 100 ISC_MEASURE_14,
341 sum(c.curr_active) over () ISC_MEASURE_15,
342 (sum(c.curr_active) over () - sum(c.prev_active) over ())
343 / decode(sum(c.prev_active) over (), 0, null,
344 abs(sum(c.prev_active) over ()))
345 * 100 ISC_MEASURE_16,
346 sum(c.curr_active) over ()
347 / decode(sum(c.curr_active) over (), 0, null,
348 sum(c.curr_active) over ()) * 100 ISC_MEASURE_17,
349 0 ISC_MEASURE_19,
350 0 ISC_MEASURE_20,
351 c.prev_book ISC_MEASURE_21,
352 c.prev_serv ISC_MEASURE_22,
353 c.prev_active ISC_MEASURE_23,
354 c.curr_active ISC_MEASURE_24,
355 c.prev_active ISC_MEASURE_25,
356 c.curr_serv ISC_MEASURE_26,
357 c.prev_serv ISC_MEASURE_27,
358 sum(c.curr_active) over () ISC_MEASURE_28,
359 sum(c.prev_active) over () ISC_MEASURE_29,
360 sum(c.curr_serv) over () ISC_MEASURE_30,
361 sum(c.prev_serv) over () ISC_MEASURE_31
362 FROM (SELECT dimension_id DIMENSION_ID,
363 sum(curr_book) CURR_BOOK,
364 sum(prev_book) PREV_BOOK,
365 sum(curr_serv) CURR_SERV,
366 sum(prev_serv) PREV_SERV,
367 sum(curr_active) CURR_ACTIVE,
368 sum(prev_active) PREV_ACTIVE
369 FROM (SELECT '||l_dimension_id||' DIMENSION_ID,
370 0 CURR_BOOK,
371 0 PREV_BOOK,
372 0 CURR_SERV,
373 0 PREV_SERV,
374 decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
375 nvl(fact.activated'||sfx||',0)-nvl(fact.expired'||sfx||',0), 0) CURR_ACTIVE,
376 decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
377 nvl(fact.activated'||sfx||',0)-nvl(fact.expired'||sfx||',0), 0) PREV_ACTIVE
378 FROM '||l_mv1 ||' fact,
379 FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
380 WHERE cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
381 AND bitand(cal.record_type_id, 119) = cal.record_type_id
382 AND cal.time_id = fact.time_id
383 AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
384 AND fact.customer_flag = :ISC_CUST_FLAG '||l_prod_cat_where||l_leaf_cat_where||l_prod_where||l_cust_where||l_class_where||'
385 UNION ALL
386 SELECT '||l_dimension_id||' DIMENSION_ID,
387 0 CURR_BOOK,
388 0 PREV_BOOK,
389 0 CURR_SERV,
390 0 PREV_SERV,
391 decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
392 nvl(fact.active'||sfx||',0), 0) CURR_ACTIVE,
393 decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
394 nvl(fact.active'||sfx||',0), 0) PREV_ACTIVE
395 FROM '||l_mv2 ||' fact,
396 FII_TIME_DAY cal'||l_prod_cat_from||'
397 WHERE cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
398 AND cal.ent_year_id = fact.ent_year_id
399 AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
400 AND fact.customer_flag = :ISC_CUST_FLAG '||l_prod_cat_where||l_leaf_cat_where||l_prod_where||l_cust_where||l_class_where||'
401 UNION ALL
402 SELECT '||l_dimension_id||' DIMENSION_ID,
403 decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
404 nvl(fact.booked_amt'||sfx||',0)-nvl(fact.returned_amt'||sfx||',0), 0) CURR_BOOK,
405 decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
406 nvl(fact.booked_amt'||sfx||',0)-nvl(fact.returned_amt'||sfx||',0), 0) PREV_BOOK,
407 0 CURR_SERV,
408 0 PREV_SERV,
409 0 CURR_ACTIVE,
410 0 PREV_ACTIVE
411 FROM '||l_mv3 ||' fact,
412 FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
413 WHERE cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
414 AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id
415 AND cal.time_id = fact.time_id
416 AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
417 AND fact.customer_flag = :ISC_CUST_FLAG '||l_prod_cat_where||l_leaf_cat_where||l_prod_where||l_cust_where||l_class_where||'
418 UNION ALL
419 SELECT '||biv_column_name(l_dimension_id)||' DIMENSION_ID,
420 0 CURR_BOOK,
421 0 PREV_BOOK,
422 decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
423 nvl(fact.first_opened_count,0), 0) CURR_SERV,
424 decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
425 nvl(fact.first_opened_count,0), 0) PREV_SERV,
426 0 CURR_ACTIVE,
427 0 PREV_ACTIVE
428 FROM '||l_mv4 ||' fact,
429 FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
430 WHERE cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
431 AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id
432 AND cal.time_id = fact.time_id
433 AND cal.period_type_id = fact.period_type_id '
434 ||l_biv_flag_where
435 ||biv_column_name(l_prod_cat_where)
436 ||biv_column_name(l_leaf_cat_where)
437 ||l_prod_where
438 ||l_cust_where||l_class_where||' )
439 GROUP BY dimension_id) c
440 WHERE c.curr_book <> 0
441 OR c.prev_book <> 0
442 OR c.curr_serv <> 0
443 OR c.prev_serv <> 0
444 OR c.curr_active <> 0
445 OR c.prev_active <> 0)) a,
446 '||l_dimension_view||' dim_view
447 WHERE a.dimension_id = dim_view.id
448 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))'||l_dim_where_clause||'
449 ORDER BY rnk';
450
451 x_custom_sql := l_stmt;
452
453 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
454 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
455 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
456 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
457 x_custom_output.extend;
458 x_custom_output(1) := l_custom_rec;
459
460 l_custom_rec.attribute_name := ':ISC_CUST_FLAG';
461 l_custom_rec.attribute_value := to_char(l_cust_flag);
462 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
463 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
464 x_custom_output.extend;
465 x_custom_output(2) := l_custom_rec;
466
467 l_custom_rec.attribute_name := ':ISC_BIV_FLAG';
468 l_custom_rec.attribute_value := to_char(l_biv_flag);
469 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
470 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
471 x_custom_output.extend;
472 x_custom_output(3) := l_custom_rec;
473
474 END Get_Sql;
475
476 END ISC_DBI_CPM_CP_ACT_PKG;