[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_CPM_CP_ACT_TREND_PKG
Source
1 PACKAGE BODY ISC_DBI_CPM_CP_ACT_TREND_PKG AS
2 /* $Header: ISCRGBMB.pls 120.0 2005/05/25 17:30:42 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_period_type 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_prod_where VARCHAR2(32000) := '';
66 l_leaf_cat_where VARCHAR2(32000) := '';
67 l_prod_cat_where VARCHAR2(32000) := '';
68 l_cust_where VARCHAR2(32000) := '';
69 l_class_where VARCHAR2(32000) := '';
70 l_biv_flag_where VARCHAR2(32000) := '';
71
72 l_prod_cat_from VARCHAR2(32000) := '';
73
74 l_item_cat_flag NUMBER;
75 l_cust_flag NUMBER;
76 l_biv_flag NUMBER;
77
78 l_mv1 VARCHAR2(1000);
79 l_mv2 VARCHAR2(1000);
80 l_mv3 VARCHAR2(1000);
81 l_mv4 VARCHAR2(1000);
82
83 l_dates_subquery VARCHAR2(32000);
84
85 l_custom_rec BIS_QUERY_ATTRIBUTES;
86
87 begin
88
89 for i in 1..p_param.count
90 loop
91 case p_param(i).parameter_name
92 when 'PERIOD_TYPE' then l_period_type := get_parameter_value(p_param(i));
93 when 'ITEM+ENI_ITEM_VBH_CAT' then l_prod_cat := get_parameter_value(p_param(i));
94 when 'ITEM+ENI_ITEM_PROD_LEAF_CAT' then l_leaf_cat := get_parameter_value(p_param(i));
95 when 'ITEM+ENI_ITEM' then l_prod := get_parameter_value(p_param(i));
96 when 'CUSTOMER+PROSPECT' then l_cust := get_parameter_value(p_param(i));
97 when 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS'
98 then l_class := get_parameter_value(p_param(i));
99 when 'CURRENCY+FII_CURRENCIES' then l_curr := get_parameter_value(p_param(i));
100 else null;
101 end case;
102 end loop;
103
104 if (l_curr = l_curr_g1) then
105 sfx := '_g1';
106 else
107 sfx := '_g';
108 end if;
109
110 if (l_cust <> 'All') then
111 l_cust_where := '
112 AND fact.customer_id in (&CUSTOMER+PROSPECT)';
113 end if;
114
115 if (l_class <> 'All') then
116 l_class_where := '
117 AND fact.class_code in (&FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
118 end if;
119
120 if (l_prod <> 'All') then
121 l_prod_where := '
122 AND fact.product_id in (&ITEM+ENI_ITEM)';
123 else
124 if (l_leaf_cat <> 'All') then
125 l_leaf_cat_where := '
126 AND fact.item_category_id = &ITEM+ENI_ITEM_PROD_LEAF_CAT';
127 else
128 if (l_prod_cat <> 'All') then
129 l_prod_cat_from := ',
130 ENI_DENORM_HIERARCHIES eni_cat,
131 MTL_DEFAULT_CATEGORY_SETS mdcs';
132 l_prod_cat_where := '
133 AND fact.item_category_id = eni_cat.child_id
134 AND eni_cat.parent_id = &ITEM+ENI_ITEM_VBH_CAT
135 AND eni_cat.dbi_flag = ''Y''
136 AND eni_cat.object_type = ''CATEGORY_SET''
137 AND eni_cat.object_id = mdcs.category_set_id
138 AND mdcs.functional_area_id = 11';
139 end if;
140 end if;
141 end if;
142
143 if (l_prod <> 'All') then
144 l_item_cat_flag := 0;
145 else
146 if (l_leaf_cat <> 'All' or l_prod_cat <> 'All') then
147 l_item_cat_flag := 1;
148 else
149 l_item_cat_flag := 3;
150 end if;
151 end if;
152
153 if (l_cust = 'All') then
154 if (l_class = 'All')
155 then l_cust_flag := 3; -- all
156 else l_cust_flag := 1; -- customer classification
157 end if;
158 else
159 l_cust_flag := 0; -- customer
160 end if;
161
162 if ((l_cust <> 'All' and l_prod <> 'All') or
163 (l_class <> 'All' and l_prod <> 'All')) then
164 l_biv_flag := 0;
165 elsif (l_cust <> 'All') then
166 l_biv_flag := 2;
167 elsif (l_class <> 'All') then
168 l_biv_flag := 4;
169 elsif (l_prod <> 'All') then
170 l_biv_flag := 1;
171 else
172 l_biv_flag := 5;
173 end if;
174
175 if (l_leaf_cat = 'All' and l_prod = 'All' and l_cust = 'All' and l_class = 'All') then
176 l_mv1 := 'ISC_DBI_CPM_003_MV';
177 l_mv2 := 'ISC_DBI_CPM_004_MV';
178 l_mv3 := 'ISC_DBI_CPM_005_MV';
179 l_mv4 := 'BIV_ACT_H_SUM_MV';
180 l_prod_cat_from := '';
181 l_item_cat_flag := 1;
182 l_cust_flag := 3;
183 if (l_prod_cat = 'All') then
184 l_prod_cat_where := '
185 AND fact.top_node_flag = ''Y''';
186 else
187 l_prod_cat_where := '
188 AND fact.parent_id = &ITEM+ENI_ITEM_VBH_CAT';
189 end if;
190 else
191 l_mv1 := 'ISC_DBI_CPM_000_MV';
192 l_mv2 := 'ISC_DBI_CPM_001_MV';
193 l_mv3 := 'ISC_DBI_CPM_002_MV';
194 l_mv4 := 'BIV_ACT_SUM_MV';
195 l_biv_flag_where := '
196 AND fact.grp_id = :ISC_BIV_FLAG';
197 end if;
198
199 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
200 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
201
202 l_dates_subquery := ' (SELECT fii.start_date START_DATE,
203 ''C'' PERIOD,
204 least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
205 FROM '||l_period_type||' fii
206 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
207 AND &BIS_CURRENT_ASOF_DATE
208 UNION ALL
209 SELECT p2.start_date START_DATE,
210 ''P'' PERIOD,
211 p1.report_date REPORT_DATE
212 FROM (SELECT least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE,
213 rownum ID
214 FROM '||l_period_type||' fii
215 WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
216 AND &BIS_PREVIOUS_ASOF_DATE
217 ORDER BY fii.start_date DESC) p1,
218 (SELECT fii.start_date START_DATE,
219 rownum ID
220 FROM '||l_period_type||' fii
221 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
222 AND &BIS_CURRENT_ASOF_DATE
223 ORDER BY fii.start_date DESC) p2
224 WHERE p1.id(+) = p2.id)';
225
226 l_stmt := '
227 SELECT dim_view.name VIEWBY,
228 nvl(c.prev_book,0) ISC_MEASURE_1,
229 nvL(c.curr_book,0) ISC_MEASURE_2,
230 (c.curr_book - c.prev_book)
231 / decode(c.prev_book, 0, null, abs(c.prev_book))
232 * 100 ISC_MEASURE_3,
233 nvl(c.prev_serv,0) ISC_MEASURE_4,
234 nvl(c.curr_serv,0) ISC_MEASURE_5,
235 (c.curr_serv - c.prev_serv)
236 / decode(c.prev_serv, 0, null, abs(c.prev_serv))
237 * 100 ISC_MEASURE_6,
238 nvl(c.prev_active,0) ISC_MEASURE_7,
239 nvl(c.curr_active,0) ISC_MEASURE_8,
240 (c.curr_active - c.prev_active)
241 / decode(c.prev_active, 0, null, abs(c.prev_active))
242 * 100 ISC_MEASURE_9
243 FROM (SELECT dimension_id DIMENSION_ID,
244 sum(curr_book) CURR_BOOK,
245 sum(prev_book) PREV_BOOK,
246 sum(curr_serv) CURR_SERV,
247 sum(prev_serv) PREV_SERV,
248 sum(curr_active) CURR_ACTIVE,
249 sum(prev_active) PREV_ACTIVE
250 FROM (SELECT dates.start_date DIMENSION_ID,
251 0 CURR_BOOK,
252 0 PREV_BOOK,
253 0 CURR_SERV,
254 0 PREV_SERV,
255 decode(dates.period, ''C'',
256 nvl(fact.activated'||sfx||',0)-nvl(fact.expired'||sfx||',0), 0) CURR_ACTIVE,
257 decode(dates.period, ''P'',
258 nvl(fact.activated'||sfx||',0)-nvl(fact.expired'||sfx||',0), 0) PREV_ACTIVE
259 FROM '||l_dates_subquery||' dates,
260 '||l_mv1 ||' fact,
261 FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
262 WHERE cal.report_date = dates.report_date
263 AND bitand(cal.record_type_id, 119) = cal.record_type_id
264 AND cal.time_id = fact.time_id
265 AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
266 AND fact.customer_flag = :ISC_CUST_FLAG '||l_prod_cat_where||l_leaf_cat_where||l_prod_where||l_cust_where||l_class_where||'
267 UNION ALL
268 SELECT dates.start_date DIMENSION_ID,
269 0 CURR_BOOK,
270 0 PREV_BOOK,
271 0 CURR_SERV,
272 0 PREV_SERV,
273 decode(dates.period, ''C'',
274 nvl(fact.active'||sfx||',0), 0) CURR_ACTIVE,
275 decode(dates.period, ''P'',
276 nvl(fact.active'||sfx||',0), 0) PREV_ACTIVE
277 FROM '||l_dates_subquery||' dates,
278 '||l_mv2 ||' fact,
279 FII_TIME_DAY cal'||l_prod_cat_from||'
280 WHERE cal.report_date = dates.report_date
281 AND cal.ent_year_id = fact.ent_year_id
282 AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
283 AND fact.customer_flag = :ISC_CUST_FLAG '||l_prod_cat_where||l_leaf_cat_where||l_prod_where||l_cust_where||l_class_where||'
284 UNION ALL
285 SELECT dates.start_date DIMENSION_ID,
286 decode(dates.period, ''C'',
287 nvl(fact.booked_amt'||sfx||',0)-nvl(fact.returned_amt'||sfx||',0), 0) CURR_BOOK,
288 decode(dates.period, ''P'',
289 nvl(fact.booked_amt'||sfx||',0)-nvl(fact.returned_amt'||sfx||',0), 0) PREV_BOOK,
290 0 CURR_SERV,
291 0 PREV_SERV,
292 0 CURR_ACTIVE,
293 0 PREV_ACTIVE
294 FROM '||l_dates_subquery||' dates,
295 '||l_mv3 ||' fact,
296 FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
297 WHERE cal.report_date = dates.report_date
298 AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id
299 AND cal.time_id = fact.time_id
300 AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
301 AND fact.customer_flag = :ISC_CUST_FLAG '||l_prod_cat_where||l_leaf_cat_where||l_prod_where||l_cust_where||l_class_where||'
302 UNION ALL
303 SELECT dates.start_date DIMENSION_ID,
304 0 CURR_BOOK,
305 0 PREV_BOOK,
306 decode(dates.period, ''C'',
307 nvl(fact.first_opened_count,0), 0) CURR_SERV,
308 decode(dates.period, ''P'',
309 nvl(fact.first_opened_count,0), 0) PREV_SERV,
310 0 CURR_ACTIVE,
311 0 PREV_ACTIVE
312 FROM '||l_dates_subquery||' dates,
313 '||l_mv4 ||' fact,
314 FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
315 WHERE cal.report_date = dates.report_date
316 AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id
317 AND cal.time_id = fact.time_id
318 AND cal.period_type_id = fact.period_type_id '
319 ||l_biv_flag_where
320 ||biv_column_name(l_prod_cat_where)
321 ||biv_column_name(l_leaf_cat_where)
322 ||l_prod_where
323 ||l_cust_where||l_class_where||' )
324 GROUP BY dimension_id) c,
325 '||l_period_type||' dim_view
326 WHERE dim_view.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
327 AND &BIS_CURRENT_ASOF_DATE
328 AND dim_view.start_date = c.dimension_id(+)
329 ORDER BY dim_view.start_date';
330
331 x_custom_sql := l_stmt;
332
333 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
334 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
335 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
336 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
337 x_custom_output.extend;
338 x_custom_output(1) := l_custom_rec;
339
340 l_custom_rec.attribute_name := ':ISC_CUST_FLAG';
341 l_custom_rec.attribute_value := to_char(l_cust_flag);
342 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
343 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
344 x_custom_output.extend;
345 x_custom_output(2) := l_custom_rec;
346
347 l_custom_rec.attribute_name := ':ISC_BIV_FLAG';
348 l_custom_rec.attribute_value := to_char(l_biv_flag);
349 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
350 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
351 x_custom_output.extend;
352 x_custom_output(3) := l_custom_rec;
353
354 END Get_Sql;
355
356 END ISC_DBI_CPM_CP_ACT_TREND_PKG;