[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_REV_PL_PKG
Source
1 PACKAGE BODY ISC_DBI_REV_PL_PKG AS
2 /* $Header: ISCRGBDB.pls 120.0 2005/05/25 17:19:11 appldev noship $ */
3
4 PROCEDURE GET_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,x_custom_sql OUT NOCOPY VARCHAR2,
5 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
6
7 l_inner_sql VARCHAR2(32000);
8 l_stmt VARCHAR2(32000);
9 l_view_by VARCHAR2(32000);
10 l_sgid VARCHAR2(32000);
11 l_sg_where VARCHAR2(32000);
12 l_prod_cat VARCHAR2(32000);
13 l_prod_cat_from VARCHAR2(32000);
14 l_prod_cat_where VARCHAR2(32000);
15 l_cust VARCHAR2(32000);
16 l_cust_where VARCHAR2(32000);
17 l_class VARCHAR2(32000);
18 l_class_where VARCHAR2(32000);
19 l_viewby_col VARCHAR2(200);
20 l_sg_sg NUMBER;
21 l_sg_res NUMBER;
22 l_item_cat_flag NUMBER;
23 l_cust_flag NUMBER; -- 0 for customer, 1 for cust classification, 3 for all
24 l_drill_bklg VARCHAR2(10000);
25 l_cat_join VARCHAR2(50);
26 l_flags VARCHAR2(32000);
27 l_mv VARCHAR2(100);
28 l_curr VARCHAR2(10000);
29 l_curr_suffix VARCHAR2(120);
30 l_invalid_curr BOOLEAN;
31 l_custom_rec BIS_QUERY_ATTRIBUTES ;
32
33
34 BEGIN
35
36 l_invalid_curr := FALSE;
37
38 FOR i IN 1..p_param.COUNT
39 LOOP
40
41 IF( p_param(i).parameter_name= 'VIEW_BY') THEN
42 l_view_by := p_param(i).parameter_value;
43 END IF;
44
45 IF(p_param(i).parameter_name = 'ORGANIZATION+JTF_ORG_SALES_GROUP') THEN
46 l_sgid := p_param(i).parameter_id;
47 END IF;
48
49 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT') THEN
50 l_prod_cat := p_param(i).parameter_id;
51 END IF;
52
53 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS') THEN
54 l_cust := p_param(i).parameter_id;
55 END IF;
56
57 IF(p_param(i).parameter_name = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') THEN
58 l_class := p_param(i).parameter_id;
59 END IF;
60
61 IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
62 THEN l_curr := p_param(i).parameter_id;
63 END IF;
64
65 END LOOP;
66
67 IF (l_curr = '''FII_GLOBAL1''')
68 THEN l_curr_suffix := 'g';
69 ELSIF (l_curr = '''FII_GLOBAL2''')
70 THEN l_curr_suffix := 'g1';
71 ELSE
72 l_invalid_curr := TRUE;
73 END IF;
74
75 l_sg_sg := to_number(replace(substr(l_sgid,instr(l_sgid,'.') + 1),''''));
76 l_sg_res := to_number(replace(substr(l_sgid,1,instr(l_sgid,'.') - 1),''''));
77
78 IF (l_sg_res IS NULL) -- when a sales group is chosen
79 THEN
80 l_drill_bklg := 'NULL';
81 IF l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP'
82 THEN
83 l_sg_where := '
84 AND f.parent_grp_id = (&ORGANIZATION+JTF_ORG_SALES_GROUP)
85 AND f.grp_marker <> ''TOP GROUP'''; -- exclude the top groups when VB=SG
86 ELSE -- other view bys
87 l_sg_where := '
88 AND f.sales_grp_id = (&ORGANIZATION+JTF_ORG_SALES_GROUP)
89 AND f.resource_id IS NULL';
90 END IF;
91 ELSE -- when the LOV parameter is a SRep (no need to go through the SG hierarchy MV
92 l_drill_bklg := '''pFunctionName=ISC_DBI_BKLG_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''';
93 l_sg_where := '
94 AND f.sales_grp_id = :ISC_SG
95 AND f.resource_id = :ISC_RES';
96 END IF;
97
98
99 IF (l_cust IS NULL)
100 THEN
101 l_cust_where:='';
102 IF (l_view_by = 'CUSTOMER+FII_CUSTOMERS')
103 THEN l_cust_flag := 0; -- customer
104 ELSIF (l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS')
105 THEN l_cust_flag := 1; -- customer classification
106 ELSE
107 IF (l_class IS NULL)
108 THEN l_cust_flag := 3; -- all
109 ELSE l_cust_flag := 1; -- customer classification
110 END IF;
111 END IF;
112 ELSE
113 l_cust_where :='
114 AND f.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
115 l_cust_flag := 0; -- customer
116 END IF;
117
118 IF (l_class IS NULL) THEN
119 l_class_where:='';
120 ELSE
121 l_class_where :='
122 AND f.class_code in (&FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
123 END IF;
124
125 IF (l_view_by <> 'CUSTOMER+FII_CUSTOMERS' AND l_cust IS NULL
126 AND l_view_by <> 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS'
127 AND l_class IS NULL) THEN -- use double rollup without cust
128 l_flags := '';
129 l_mv := 'ISC_DBI_SCR_002_MV';
130 IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') THEN
131 l_prod_cat_from := ',
132 ENI_DENORM_HIERARCHIES eni_cat,
133 MTL_DEFAULT_CATEGORY_SETS mdcs';
134 IF (l_prod_cat IS NULL) THEN
135 l_prod_cat_where := '
136 AND f.cat_top_node_flag = ''Y''
137 AND f.item_category_id = eni_cat.imm_child_id
138 AND eni_cat.top_node_flag = ''Y''
139 AND eni_cat.dbi_flag = ''Y''
140 AND eni_cat.object_type = ''CATEGORY_SET''
141 AND eni_cat.object_id = mdcs.category_set_id
142 AND mdcs.functional_area_id = 11';
143 ELSE l_prod_cat_where := '
144 AND f.item_category_id = eni_cat.imm_child_id
145 AND ((eni_cat.leaf_node_flag = ''N'' and
146 eni_cat.child_id <> eni_cat.parent_id and imm_child_id = child_id)
147 OR (eni_cat.leaf_node_flag = ''Y''))
148 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
149 AND eni_cat.dbi_flag = ''Y''
150 AND eni_cat.object_type = ''CATEGORY_SET''
151 AND eni_cat.object_id = mdcs.category_set_id
152 AND mdcs.functional_area_id = 11';
153 END IF;
154 ELSE -- view by <> cat.
155 l_prod_cat_from := ''; -- do not need to join to denorm table
156 IF (l_prod_cat IS NULL) THEN
157 l_prod_cat_where :='
158 AND f.cat_top_node_flag = ''Y''';
159 ELSE -- view by sales group, prod.cat selected
160 l_prod_cat_where :='
161 AND f.item_category_id IN (&ITEM+ENI_ITEM_VBH_CAT)';
162 END IF;
163 END IF;
164
165 ELSE -- use single rollup with customer dimension
166 l_flags := '
167 AND f.item_cat_flag = :ISC_ITEM_CAT_FLAG
168 AND f.customer_flag =:ISC_CUST';
169 l_mv := 'ISC_DBI_SCR_001_MV';
170 IF (l_prod_cat IS NULL) THEN
171 IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') THEN
172 l_prod_cat_from := ',
173 ENI_DENORM_HIERARCHIES eni_cat,
174 MTL_DEFAULT_CATEGORY_SETS mdcs';
175 l_prod_cat_where := '
176 AND f.item_category_id = eni_cat.child_id
177 AND eni_cat.top_node_flag = ''Y''
178 AND eni_cat.dbi_flag = ''Y''
179 AND eni_cat.object_type = ''CATEGORY_SET''
180 AND eni_cat.object_id = mdcs.category_set_id
181 AND mdcs.functional_area_id = 11';
182 ELSE
183 l_prod_cat_from := '';
184 l_prod_cat_where := '';
185 END IF;
186 ELSE -- a prod cat has been selected
187 l_prod_cat_from := ',
188 ENI_DENORM_HIERARCHIES eni_cat,
189 MTL_DEFAULT_CATEGORY_SETS mdcs';
190 l_prod_cat_where := '
191 AND f.item_category_id = eni_cat.child_id
192 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
193 AND eni_cat.dbi_flag = ''Y''
194 AND eni_cat.object_type = ''CATEGORY_SET''
195 AND eni_cat.object_id = mdcs.category_set_id
196 AND mdcs.functional_area_id = 11';
197 END IF;
198 END IF;
199
200
201 IF (l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP') THEN
202 l_viewby_col :='resource_id, sales_grp_id';
203
204 ELSIF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') THEN
205 IF (l_prod_cat is null) THEN
206 l_viewby_col := 'parent_id';
207 ElSE
208 l_viewby_col :='imm_child_id';
209 END IF;
210
211 ELSIF (l_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
212 l_viewby_col :='customer_id';
213
214 ELSIF (l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') THEN
215 l_viewby_col :='class_code';
216
217 END IF;
218
219 IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' OR l_prod_cat IS NOT NULL)
220 THEN l_item_cat_flag := 0; -- Product Category
221 ELSE l_item_cat_flag := 1; -- All
222 END IF;
223
224 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
225 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
226
227 IF (l_invalid_curr)
228 THEN l_stmt := '
229 /* Unsupported currency */
230 SELECT 0 VIEWBY,
231 0 VIEWBYID,
232 0 ISC_ATTRIBUTE_2,
233 0 ISC_ATTRIBUTE_3,
234 0 ISC_ATTRIBUTE_4,
235 0 ISC_MEASURE_2,
236 0 ISC_MEASURE_3,
237 0 ISC_MEASURE_4,
238 0 ISC_MEASURE_5,
239 0 ISC_MEASURE_6,
240 0 ISC_MEASURE_7,
241 0 ISC_MEASURE_9,
242 0 ISC_MEASURE_10,
243 0 ISC_MEASURE_11,
244 0 ISC_MEASURE_12,
245 0 ISC_MEASURE_13,
246 0 ISC_MEASURE_14,
247 0 ISC_MEASURE_16,
248 0 ISC_MEASURE_17,
249 0 ISC_MEASURE_18,
250 0 ISC_MEASURE_19,
251 0 ISC_MEASURE_20,
252 0 ISC_MEASURE_21
253 FROM dual
254 WHERE 1 = 2';
255
256 ELSE
257
258 l_inner_sql:='
259 ISC_MEASURE_2,ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,ISC_MEASURE_6,ISC_MEASURE_7,
260 ISC_MEASURE_9,ISC_MEASURE_10,ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_14,
261 ISC_MEASURE_16,ISC_MEASURE_17,ISC_MEASURE_18,ISC_MEASURE_19,ISC_MEASURE_20,ISC_MEASURE_21
262 FROM(SELECT
263 (rank() over (&ORDER_BY_CLAUSE nulls last,'||l_viewby_col||'))-1 rnk,
264 '||l_viewby_col||',
265 ISC_MEASURE_2,ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,ISC_MEASURE_6,ISC_MEASURE_7,
266 ISC_MEASURE_9,ISC_MEASURE_10,ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_14,
267 ISC_MEASURE_16,ISC_MEASURE_17,ISC_MEASURE_18,ISC_MEASURE_19,ISC_MEASURE_20,ISC_MEASURE_21
268 FROM
269 (SELECT '||l_viewby_col||',
270 nvl(c_backlog, 0) ISC_MEASURE_2,
271 c_backlog
272 / decode(sum(c_backlog) over (), 0, null,
273 sum(c_backlog) over ()) * 100 ISC_MEASURE_3,
274 (c_backlog - p_backlog)
275 / decode(p_backlog, 0, null,
276 abs(p_backlog)) * 100 ISC_MEASURE_4,
277 nvl(sum(c_backlog) over (), 0) ISC_MEASURE_5,
278 sum(c_backlog) over ()
279 / decode(sum(c_backlog) over (), 0, null,
280 sum(c_backlog) over ()) * 100 ISC_MEASURE_6,
281 (sum(c_backlog) over () - sum(p_backlog) over ())
282 / decode(sum(p_backlog) over (), 0, null,
283 abs(sum(p_backlog) over ())) * 100 ISC_MEASURE_7,
284 nvl(c_defer_rev, 0) ISC_MEASURE_9,
285 c_defer_rev
286 / decode(sum(c_defer_rev) over (), 0, null,
287 sum(c_defer_rev) over ()) * 100 ISC_MEASURE_10,
288 (c_defer_rev - p_defer_rev)
289 / decode(p_defer_rev, 0, null,
290 abs(p_defer_rev)) * 100 ISC_MEASURE_11,
291 nvl(sum(c_defer_rev) over (), 0) ISC_MEASURE_12,
292 sum(c_defer_rev) over ()
293 / decode(sum(c_defer_rev) over (), 0, null,
294 sum(c_defer_rev) over ()) * 100 ISC_MEASURE_13,
295 (sum(c_defer_rev) over () - sum(p_defer_rev) over ())
296 / decode(sum(p_defer_rev) over (), 0, null,
297 abs(sum(p_defer_rev) over ())) * 100 ISC_MEASURE_14,
298 nvl((c_backlog+c_defer_rev), 0) ISC_MEASURE_16,
299 (c_backlog+c_defer_rev)
300 / decode(sum(c_backlog+c_defer_rev) over (), 0, null,
301 sum(c_backlog+c_defer_rev) over ()) * 100 ISC_MEASURE_17,
302 ((c_backlog+c_defer_rev) - (p_backlog+p_defer_rev))
303 / decode((p_backlog+p_defer_rev), 0, null,
304 abs((p_backlog+p_defer_rev))) * 100 ISC_MEASURE_18,
305 nvl(sum(c_backlog+c_defer_rev) over (), 0) ISC_MEASURE_19,
306 sum(c_backlog+c_defer_rev) over ()
307 / decode(sum(c_backlog+c_defer_rev) over (), 0, null,
308 sum(c_backlog+c_defer_rev) over ()) * 100 ISC_MEASURE_20,
309 (sum(c_backlog+c_defer_rev) over () - sum(p_backlog+p_defer_rev) over ())
310 / decode(sum(p_backlog+p_defer_rev) over (), 0, null,
311 abs(sum(p_backlog+p_defer_rev) over ())) * 100 ISC_MEASURE_21
312 FROM
313 (
314 SELECT '||l_viewby_col||',
315 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
316 nvl(backlog_amt_'||l_curr_suffix||', 0), 0)) C_BACKLOG,
317 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
318 nvl(backlog_amt_'||l_curr_suffix||', 0), 0)) P_BACKLOG,
319 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
320 nvl(deferred_amt_'||l_curr_suffix||', 0), 0)) C_DEFER_REV,
321 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
322 nvl(deferred_amt_'||l_curr_suffix||', 0), 0)) P_DEFER_REV
323 FROM '||l_mv||' f,
324 FII_TIME_RPT_STRUCT_V cal'
325 ||l_prod_cat_from||'
326 WHERE f.time_id = cal.time_id
327 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
328 AND cal.period_type_id = f.period_type_id
329 AND bitand(cal.record_type_id,1143) = cal.record_type_id'
330 ||l_flags
331 ||l_sg_where||l_prod_cat_where||l_cust_where||l_class_where||'
332 GROUP BY '||l_viewby_col||'))) c,';
333
334
335 IF l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' THEN
336 IF (l_prod_cat is null) THEN
337 l_cat_join := 'AND c.parent_id = ecat.id';
338 ElSE
339 l_cat_join := 'AND c.imm_child_id = ecat.id';
340 END IF;
341
342 l_stmt := ' SELECT ecat.value VIEWBY,
343 ecat.id VIEWBYID,
344 null ISC_ATTRIBUTE_2, -- Drill - Sales Group
345 decode(ecat.leaf_node_flag, ''Y'',
346 NULL,
347 ''pFunctionName=ISC_DBI_REV_PIPELINE&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'')
348 ISC_ATTRIBUTE_3, -- Drill - Product Category
349 '||l_drill_bklg||' ISC_ATTRIBUTE_4, -- Drill - Net Prod Order Bklg Value'
350 ||l_inner_sql||'
351 ENI_ITEM_VBH_NODES_V ecat
352 WHERE ecat.parent_id = ecat.child_id
353 '||l_cat_join||'
354 AND ((c.rnk between &START_INDEX and &END_INDEX) OR(&END_INDEX = -1))
355 ORDER BY rnk';
356
357 ELSIF l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP' THEN
358 l_stmt := 'SELECT
359 decode(c.resource_id,null,g.group_name,
360 r.resource_name) VIEWBY,
361 decode(c.resource_id,null,to_char(c.sales_grp_id),
362 c.resource_id||''.''||c.sales_grp_id)
363 VIEWBYID,
364 decode(c.resource_id, NULL,
365 ''pFunctionName=ISC_DBI_REV_PIPELINE&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY'',
366 NULL) ISC_ATTRIBUTE_2, -- Drill - Sales Group
367 null ISC_ATTRIBUTE_3, -- Drill - Product Category
368 decode(c.resource_id, NULL,
369 NULL,
370 decode(c.sales_grp_id, -1, NULL,
371 ''pFunctionName=ISC_DBI_BKLG_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''))
372 ISC_ATTRIBUTE_4, -- Drill - Net Prod Order Bklg Value'
373 ||l_inner_sql||'
374 JTF_RS_GROUPS_VL g,
375 JTF_RS_RESOURCE_EXTNS_VL r
376 WHERE c.sales_grp_id = g.group_id
377 AND c.resource_id = r.resource_id(+)
378 AND ((c.rnk between &START_INDEX and &END_INDEX) OR(&END_INDEX = -1))
379 ORDER BY rnk' ;
380
381 ELSIF l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
382 l_stmt := '
383 SELECT cc.value VIEWBY,
384 cc.id VIEWBYID,
385 null ISC_ATTRIBUTE_2, -- Drill - Sales Group
386 null ISC_ATTRIBUTE_3, -- Drill - Product Category
387 '||l_drill_bklg||' ISC_ATTRIBUTE_4, -- Drill - Net Prod Order Bklg Value'
388 ||l_inner_sql||'
389 FII_PARTNER_MKT_CLASS_V cc
390 WHERE c.class_code = cc.id
391 AND ((c.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
392 ORDER BY rnk';
393
394 ELSE -- l_view_by = 'CUSTOMER+FII_CUSTOMERS'
395 l_stmt := 'SELECT cust.value VIEWBY,
396 cust.id VIEWBYID,
397 null ISC_ATTRIBUTE_2, -- Drill - Sales Group
398 null ISC_ATTRIBUTE_3, -- Drill - Product Category
399 '||l_drill_bklg||' ISC_ATTRIBUTE_4, -- Drill - Net Prod Order Bklg Value'
400 ||l_inner_sql||'
401 FII_CUSTOMERS_V cust
402 WHERE c.customer_id = cust.id
403 AND ((c.rnk between &START_INDEX and &END_INDEX) OR(&END_INDEX = -1))
404 ORDER BY rnk';
405
406 END IF;
407
408 END IF;
409
410 x_custom_sql := l_stmt;
411
412 l_custom_rec.attribute_name := ':ISC_CUST';
413 l_custom_rec.attribute_value := l_cust_flag;
414 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
415 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
416 x_custom_output.EXTEND;
417 x_custom_output(1) := l_custom_rec;
418
419 l_custom_rec.attribute_name := ':ISC_SG';
420 l_custom_rec.attribute_value := to_char(l_sg_sg);
421 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
422 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
423 x_custom_output.extend;
424 x_custom_output(2) := l_custom_rec;
425
426 l_custom_rec.attribute_name := ':ISC_RES';
427 l_custom_rec.attribute_value := to_char(l_sg_res);
428 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
429 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
430 x_custom_output.extend;
431 x_custom_output(3) := l_custom_rec;
432
433 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
434 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
435 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
436 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
437 x_custom_output.extend;
438 x_custom_output(4) := l_custom_rec;
439
440 END get_sql;
441
442 END ISC_DBI_REV_PL_PKG ;
443