[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_CPM_SPT_COMP_PKG
Source
1 PACKAGE BODY ISC_DBI_CPM_SPT_COMP_PKG AS
2 /* $Header: ISCRGB8B.pls 120.0 2005/05/25 17:16:16 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 PROCEDURE Get_Sql ( p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
35 x_custom_sql OUT NOCOPY VARCHAR2,
36 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
37
38 l_stmt VARCHAR2(32000);
39
40 l_view_by VARCHAR2(32000);
41 l_prod_cat VARCHAR2(32000) := 'All';
42 l_leaf_cat VARCHAR2(32000) := 'All';
43 l_prod VARCHAR2(32000) := 'All';
44 l_cust VARCHAR2(32000) := 'All';
45 l_class VARCHAR2(32000) := 'All';
46 l_curr VARCHAR2(32000);
47
48 l_curr_g VARCHAR2(100) := '''FII_GLOBAL1''';
49 l_curr_g1 VARCHAR2(100) := '''FII_GLOBAL2''';
50 sfx VARCHAR2(100);
51
52 l_drill_prod_cat VARCHAR2(32000);
53 l_drill_leaf_cat VARCHAR2(32000);
54 l_dimension_id VARCHAR2(32000);
55 l_dimension_view VARCHAR2(32000);
56 l_dim_where_clause VARCHAR2(32000);
57
58 l_prod_where VARCHAR2(32000) := '';
59 l_leaf_cat_where VARCHAR2(32000) := '';
60 l_prod_cat_where VARCHAR2(32000) := '';
61 l_cust_where VARCHAR2(32000) := '';
62 l_class_where VARCHAR2(32000) := '';
63
64 l_prod_cat_from VARCHAR2(32000) := '';
65
66 l_item_cat_flag NUMBER;
67 l_cust_flag NUMBER;
68
69 l_mv1 VARCHAR2(1000);
70 l_mv2 VARCHAR2(1000);
71
72 l_measures VARCHAR2(32000);
73
74 l_custom_rec BIS_QUERY_ATTRIBUTES;
75
76 begin
77
78 for i in 1..p_param.count
79 loop
80 case p_param(i).parameter_name
81 when 'VIEW_BY' then l_view_by := get_parameter_value(p_param(i));
82 when 'ITEM+ENI_ITEM_VBH_CAT' then l_prod_cat := get_parameter_value(p_param(i));
83 when 'ITEM+ENI_ITEM_PROD_LEAF_CAT' then l_leaf_cat := get_parameter_value(p_param(i));
84 when 'ITEM+ENI_ITEM' then l_prod := get_parameter_value(p_param(i));
85 when 'CUSTOMER+PROSPECT' then l_cust := get_parameter_value(p_param(i));
86 when 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS'
87 then l_class := get_parameter_value(p_param(i));
88 when 'CURRENCY+FII_CURRENCIES' then l_curr := get_parameter_value(p_param(i));
89 else null;
90 end case;
91 end loop;
92
93 if (l_curr = l_curr_g1) then
94 sfx := '_g1';
95 else
96 sfx := '_g';
97 end if;
98
99 if (l_view_by = 'CUSTOMER+PROSPECT') then
100 l_drill_prod_cat := 'null';
101 l_drill_leaf_cat := 'null';
102 l_dimension_id := 'fact.customer_id';
103 l_dimension_view := 'ASO_BI_PROSPECT_V';
104 l_dim_where_clause := '';
105
106 elsif (l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') then
107 l_drill_prod_cat := 'null';
108 l_drill_leaf_cat := 'null';
109 l_dimension_id := 'fact.class_code';
110 l_dimension_view := 'FII_PARTNER_MKT_CLASS_V';
111 l_dim_where_clause := '';
112
113 elsif (l_view_by = 'ITEM+ENI_ITEM') then
114 l_drill_prod_cat := 'null';
115 l_drill_leaf_cat := 'null';
116 l_dimension_id := 'fact.product_id';
117 l_dimension_view := 'ENI_OLTP_ITEM_STAR';
118 l_dim_where_clause := '
119 AND dim_view.master_id is null';
120
121 elsif (l_view_by = 'ITEM+ENI_ITEM_PROD_LEAF_CAT') then
122 l_drill_prod_cat := 'null';
123 l_drill_leaf_cat := '''pFunctionName=ISC_DBI_CPM_SPT_COMP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM&pParamIds=Y''';
124 l_dimension_id := 'fact.item_category_id';
125 l_dimension_view := 'ENI_ITEM_PROD_LEAF_CAT_V';
126 l_dim_where_clause := '';
127
128 else -- l_view_by = 'ITEM+ENI_ITEM_VBH_CAT'
129 l_drill_prod_cat := 'decode(dim_view.leaf_node_flag, ''Y'',
130 ''pFunctionName=ISC_DBI_CPM_SPT_COMP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM&pParamIds=Y'',
131 ''pFunctionName=ISC_DBI_CPM_SPT_COMP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'')';
132 l_drill_leaf_cat := 'null';
133 if (l_prod_cat = 'All') then
134 l_dimension_id := 'eni_cat.parent_id';
135 else
136 l_dimension_id := 'eni_cat.imm_child_id';
137 end if;
138 l_dimension_view := 'ENI_ITEM_VBH_NODES_V';
139 l_dim_where_clause := '
140 AND dim_view.parent_id = dim_view.child_id';
141 end if;
142
143 if (l_cust <> 'All') then
144 l_cust_where := '
145 AND fact.customer_id in (&CUSTOMER+PROSPECT)';
146 end if;
147
148 if (l_class <> 'All') then
149 l_class_where := '
150 AND fact.class_code in (&FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
151 end if;
152
153 if (l_prod <> 'All') then
154 l_prod_where := '
155 AND fact.product_id in (&ITEM+ENI_ITEM)';
156 else
157 if (l_leaf_cat <> 'All') then
158 l_leaf_cat_where := '
159 AND fact.item_category_id = &ITEM+ENI_ITEM_PROD_LEAF_CAT';
160 else
161 if (l_prod_cat <> 'All') then
162 l_prod_cat_from := ',
163 ENI_DENORM_HIERARCHIES eni_cat,
164 MTL_DEFAULT_CATEGORY_SETS mdcs';
165 l_prod_cat_where := '
166 AND fact.item_category_id = eni_cat.child_id
167 AND eni_cat.parent_id = &ITEM+ENI_ITEM_VBH_CAT
168 AND eni_cat.dbi_flag = ''Y''
169 AND eni_cat.object_type = ''CATEGORY_SET''
170 AND eni_cat.object_id = mdcs.category_set_id
171 AND mdcs.functional_area_id = 11';
172 end if;
173 end if;
174 end if;
175
176 if (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') then
177 l_prod_cat_from := ',
178 ENI_DENORM_HIERARCHIES eni_cat,
179 MTL_DEFAULT_CATEGORY_SETS mdcs';
180 l_prod_cat_where := '
181 AND fact.item_category_id = eni_cat.child_id
182 AND eni_cat.dbi_flag = ''Y''
183 AND eni_cat.object_type = ''CATEGORY_SET''
184 AND eni_cat.object_id = mdcs.category_set_id
185 AND mdcs.functional_area_id = 11';
186 if (l_prod_cat = 'All') then
187 l_prod_cat_where := l_prod_cat_where||'
188 AND eni_cat.top_node_flag = ''Y''';
189 else
190 l_prod_cat_where := l_prod_cat_where||'
191 AND eni_cat.parent_id = &ITEM+ENI_ITEM_VBH_CAT';
192 end if;
193 end if;
194
195 if (l_prod <> 'All' or l_view_by = 'ITEM+ENI_ITEM') then
196 l_item_cat_flag := 0;
197 else
198 if (l_leaf_cat <> 'All' or l_view_by = 'ITEM+ENI_ITEM_PROD_LEAF_CAT' or
199 l_prod_cat <> 'All' or l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') then
200 l_item_cat_flag := 1;
201 else
202 l_item_cat_flag := 3;
203 end if;
204 end if;
205
206 if (l_cust = 'All') then
207 if (l_view_by = 'CUSTOMER+PROSPECT')
208 then l_cust_flag := 0; -- customer
209 elsif (l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS')
210 then l_cust_flag := 1; -- customer classification
211 else
212 if (l_class = 'All')
213 then l_cust_flag := 3; -- all
214 else l_cust_flag := 1; -- customer classification
215 end if;
216 end if;
217 else
218 l_cust_flag := 0; -- customer
219 end if;
220
221 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
222 l_mv1 := 'ISC_DBI_CPM_003_MV';
223 l_mv2 := 'ISC_DBI_CPM_004_MV';
224 l_prod_cat_from := '';
225 if (l_prod_cat = 'All') then
226 l_dimension_id := 'fact.parent_id';
227 l_prod_cat_where := '
228 AND fact.top_node_flag = ''Y''';
229 else
230 l_dimension_id := 'fact.imm_child_id';
231 l_prod_cat_where := '
232 AND fact.parent_id = &ITEM+ENI_ITEM_VBH_CAT';
233 end if;
234 else
235 l_mv1 := 'ISC_DBI_CPM_000_MV';
236 l_mv2 := 'ISC_DBI_CPM_001_MV';
237 end if;
238
239 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
240 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
241
242 l_measures := 'isc_measure_1, isc_measure_2, isc_measure_3, isc_measure_4, isc_measure_5,
243 isc_measure_6, isc_measure_7, isc_measure_8, isc_measure_9, isc_measure_10,
244 isc_measure_11, isc_measure_12, isc_measure_13, isc_measure_14, isc_measure_15,
245 isc_measure_16, isc_measure_17';
246
247 l_stmt := '
248 SELECT /*+ LEADING(a) INDEX(dim_view) */ dim_view.value VIEWBY,
249 dim_view.id VIEWBYID,
250 '||l_drill_prod_cat||' ISC_ATTRIBUTE_1,
251 '||l_drill_leaf_cat||' ISC_ATTRIBUTE_2,
252 '||l_measures||'
253 FROM
254 (SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, dimension_id)) - 1 RNK,
255 dimension_id,
256 '||l_measures||'
257 FROM (SELECT c.dimension_id DIMENSION_ID,
258 c.curr_active ISC_MEASURE_1,
259 (c.curr_active - c.prev_active)
260 / decode(c.prev_active, 0, null,
261 abs(c.prev_active))
262 * 100 ISC_MEASURE_2,
263 c.curr_new ISC_MEASURE_3,
264 (c.curr_new - c.prev_new)
265 / decode(c.prev_new, 0, null,
266 abs(c.prev_new))
267 * 100 ISC_MEASURE_4,
268 c.curr_new
269 / decode(c.curr_active, 0, null,
270 c.curr_active)
271 * 100 ISC_MEASURE_5,
272 c.curr_new
273 / decode(c.curr_active, 0, null,
274 c.curr_active)
275 * 100
276 - c.prev_new
277 / decode(c.prev_active, 0, null,
278 c.prev_active)
279 * 100 ISC_MEASURE_6,
280 c.curr_renew ISC_MEASURE_7,
281 (c.curr_renew - c.prev_renew)
282 / decode(c.prev_renew, 0, null,
283 abs(c.prev_renew))
284 * 100 ISC_MEASURE_8,
285 sum(c.curr_active) over () ISC_MEASURE_9,
286 (sum(c.curr_active) over () - sum(c.prev_active) over ())
287 / decode(sum(c.prev_active) over (), 0, null,
288 abs(sum(c.prev_active) over ()))
289 * 100 ISC_MEASURE_10,
290 sum(c.curr_new) over () ISC_MEASURE_11,
291 (sum(c.curr_new) over () - sum(c.prev_new) over ())
292 / decode(sum(c.prev_new) over (), 0, null,
293 abs(sum(c.prev_new) over ()))
294 * 100 ISC_MEASURE_12,
295 sum(c.curr_new) over ()
296 / decode(sum(c.curr_active) over (), 0, null,
297 sum(c.curr_active) over ())
298 * 100 ISC_MEASURE_13,
299 sum(c.curr_new) over ()
300 / decode(sum(c.curr_active) over (), 0, null,
301 sum(c.curr_active) over ())
302 * 100
303 - sum(c.prev_new) over ()
304 / decode(sum(c.prev_active) over (), 0, null,
305 sum(c.prev_active) over ())
306 * 100 ISC_MEASURE_14,
307 sum(c.curr_renew) over () ISC_MEASURE_15,
308 (sum(c.curr_renew) over () - sum(c.prev_renew) over ())
309 / decode(sum(c.prev_renew) over (), 0, null,
310 abs(sum(c.prev_renew) over ()))
311 * 100 ISC_MEASURE_16,
312 c.prev_active ISC_MEASURE_17
313 FROM (SELECT dimension_id DIMENSION_ID,
314 sum(curr_active) CURR_ACTIVE,
315 sum(prev_active) PREV_ACTIVE,
316 sum(curr_new) CURR_NEW,
317 sum(prev_new) PREV_NEW,
318 sum(curr_renew) CURR_RENEW,
319 sum(prev_renew) PREV_RENEW
320 FROM (SELECT '||l_dimension_id||' DIMENSION_ID,
321 decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
322 nvl(fact.activated'||sfx||',0)-nvl(fact.expired'||sfx||',0), 0) CURR_ACTIVE,
323 decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
324 nvl(fact.activated'||sfx||',0)-nvl(fact.expired'||sfx||',0), 0) PREV_ACTIVE,
325 decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
326 nvl(fact.activated_new'||sfx||',0)-nvl(fact.expired_new'||sfx||',0), 0) CURR_NEW,
327 decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
328 nvl(fact.activated_new'||sfx||',0)-nvl(fact.expired_new'||sfx||',0), 0) PREV_NEW,
329 decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
330 nvl(fact.activated_renew'||sfx||',0)-nvl(fact.expired_renew'||sfx||',0), 0) CURR_RENEW,
331 decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
332 nvl(fact.activated_renew'||sfx||',0)-nvl(fact.expired_renew'||sfx||',0), 0) PREV_RENEW
333 FROM '||l_mv1 ||' fact,
334 FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
335 WHERE cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
336 AND bitand(cal.record_type_id, 119) = cal.record_type_id
337 AND cal.time_id = fact.time_id
338 AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
339 AND fact.customer_flag = :ISC_CUST_FLAG '||l_prod_cat_where||l_leaf_cat_where||l_prod_where||l_cust_where||l_class_where||'
340 UNION ALL
341 SELECT '||l_dimension_id||' DIMENSION_ID,
342 decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
343 nvl(fact.active'||sfx||',0), 0) CURR_ACTIVE,
344 decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
345 nvl(fact.active'||sfx||',0), 0) PREV_ACTIVE,
346 decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
347 nvl(fact.active_new'||sfx||',0), 0) CURR_NEW,
348 decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
349 nvl(fact.active_new'||sfx||',0), 0) PREV_NEW,
350 decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
351 nvl(fact.active_renew'||sfx||',0), 0) CURR_RENEW,
352 decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
353 nvl(fact.active_renew'||sfx||',0), 0) PREV_RENEW
354 FROM '||l_mv2 ||' fact,
355 FII_TIME_DAY cal'||l_prod_cat_from||'
356 WHERE cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
357 AND cal.ent_year_id = fact.ent_year_id
358 AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
359 AND fact.customer_flag = :ISC_CUST_FLAG '||l_prod_cat_where||l_leaf_cat_where||l_prod_where||l_cust_where||l_class_where||' )
360 GROUP BY dimension_id) c
361 WHERE c.curr_active <> 0
362 OR c.prev_active <> 0
363 OR c.curr_new <> 0
364 OR c.prev_new <> 0
365 OR c.curr_renew <> 0
366 OR c.prev_renew <> 0)) a,
367 '||l_dimension_view||' dim_view
368 WHERE a.dimension_id = dim_view.id
369 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))'||l_dim_where_clause||'
370 ORDER BY rnk';
371
372 x_custom_sql := l_stmt;
373
374 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
375 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
376 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
377 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
378 x_custom_output.extend;
379 x_custom_output(1) := l_custom_rec;
380
381 l_custom_rec.attribute_name := ':ISC_CUST_FLAG';
382 l_custom_rec.attribute_value := to_char(l_cust_flag);
383 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
384 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
385 x_custom_output.extend;
386 x_custom_output(2) := l_custom_rec;
387
388 END Get_Sql;
389
390 END ISC_DBI_CPM_SPT_COMP_PKG;