[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_REV_BACKLOG_PKG
Source
1 PACKAGE BODY ISC_DBI_REV_BACKLOG_PKG AS
2 /* $Header: ISCRGBBB.pls 120.0 2005/05/25 17:18:20 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_period_type VARCHAR2(32000);
10 l_rev_book VARCHAR2(32000);
11 l_view_by VARCHAR2(32000);
12 l_sgid VARCHAR2(32000);
13 l_sg_where VARCHAR2(32000);
14 l_prod_cat VARCHAR2(32000);
15 l_prod_cat_from VARCHAR2(32000);
16 l_prod_cat_where VARCHAR2(32000);
17 l_cust VARCHAR2(32000);
18 l_cust_where VARCHAR2(32000);
19 l_class VARCHAR2(32000);
20 l_class_where VARCHAR2(32000);
21 l_viewby_col VARCHAR2(200);
22 l_sg_sg NUMBER;
23 l_sg_res NUMBER;
24 l_item_cat_flag NUMBER;
25 l_cust_flag NUMBER; -- 0 for customer, 1 for cust classification, 3 for all
26 l_cat_join VARCHAR2(50);
27 l_flags VARCHAR2(32000);
28 l_mv VARCHAR2(100);
29 l_curr VARCHAR2(10000);
30 l_curr_suffix VARCHAR2(120);
31 l_invalid_curr BOOLEAN;
32 l_func VARCHAR2(32000);
33 l_custom_rec BIS_QUERY_ATTRIBUTES ;
34
35
36 BEGIN
37
38 l_invalid_curr := FALSE;
39
40 FOR i IN 1..p_param.COUNT
41 LOOP
42
43 IF( p_param(i).parameter_name= 'BIS_FXN_NAME') THEN
44 l_func := p_param(i).parameter_value;
45 END IF;
46
47 IF( p_param(i).parameter_name= 'VIEW_BY') THEN
48 l_view_by := p_param(i).parameter_value;
49 END IF;
50
51 IF(p_param(i).parameter_name = 'ORGANIZATION+JTF_ORG_SALES_GROUP') THEN
52 l_sgid := p_param(i).parameter_id;
53 END IF;
54
55 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT') THEN
56 l_prod_cat := p_param(i).parameter_id;
57 END IF;
58
59 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS') THEN
60 l_cust := p_param(i).parameter_id;
61 END IF;
62
63 IF(p_param(i).parameter_name = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') THEN
64 l_class := p_param(i).parameter_id;
65 END IF;
66
67 IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
68 THEN l_curr := p_param(i).parameter_id;
69 END IF;
70
71 IF (p_param(i).parameter_name = 'PERIOD_TYPE')
72 THEN l_period_type := p_param(i).parameter_value;
73 END IF;
74
75 END LOOP;
76
77 IF (l_func = 'ISC_DBI_REV_SG_P')
78 THEN l_func := 'ISC_DBI_REV_SG';
79 ELSIF (l_func = 'ISC_DBI_REV_PC_P')
80 THEN l_func := 'ISC_DBI_REV_PC';
81 END IF;
82
83 IF (l_curr = '''FII_GLOBAL1''')
84 THEN l_curr_suffix := 'g';
85 ELSIF (l_curr = '''FII_GLOBAL2''')
86 THEN l_curr_suffix := 'g1';
87 ELSE
88 l_invalid_curr := TRUE;
89 END IF;
90
91 IF l_period_type = 'FII_TIME_ENT_YEAR'
92 THEN l_rev_book := 'booked_rev_yr_'||l_curr_suffix;
93 ELSIF l_period_type = 'FII_TIME_ENT_QTR'
94 THEN l_rev_book := 'booked_rev_qr_'||l_curr_suffix;
95 ELSIF l_period_type = 'FII_TIME_ENT_PERIOD'
96 THEN l_rev_book := 'booked_rev_pe_'||l_curr_suffix;
97 ELSE -- l_period_type = 'FII_TIME_WEEK'
98 l_rev_book := 'booked_rev_wk_'||l_curr_suffix;
99 END IF;
100
101 l_sg_sg := to_number(replace(substr(l_sgid,instr(l_sgid,'.') + 1),''''));
102 l_sg_res := to_number(replace(substr(l_sgid,1,instr(l_sgid,'.') - 1),''''));
103
104 IF (l_sg_res IS NULL) -- when a sales group is chosen
105 THEN
106 IF l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP'
107 THEN
108 l_sg_where := '
109 AND f.parent_grp_id = (&ORGANIZATION+JTF_ORG_SALES_GROUP)
110 AND f.grp_marker <> ''TOP GROUP'''; -- exclude the top groups when VB=SG
111 ELSE -- other view bys
112 l_sg_where := '
113 AND f.sales_grp_id = (&ORGANIZATION+JTF_ORG_SALES_GROUP)
114 AND f.resource_id IS NULL';
115 END IF;
116 ELSE -- when the LOV parameter is a SRep (no need to go through the SG hierarchy MV
117 l_sg_where := '
118 AND f.sales_grp_id = :ISC_SG
119 AND f.resource_id = :ISC_RES';
120 END IF;
121
122
123 IF (l_cust IS NULL)
124 THEN
125 l_cust_where:='';
126 IF (l_view_by = 'CUSTOMER+FII_CUSTOMERS')
127 THEN l_cust_flag := 0; -- customer
128 ELSIF (l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS')
129 THEN l_cust_flag := 1; -- customer classification
130 ELSE
131 IF (l_class IS NULL)
132 THEN l_cust_flag := 3; -- all
133 ELSE l_cust_flag := 1; -- customer classification
134 END IF;
135 END IF;
136 ELSE
137 l_cust_where :='
138 AND f.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
139 l_cust_flag := 0; -- customer
140 END IF;
141
142 IF (l_class IS NULL) THEN
143 l_class_where:='';
144 ELSE
145 l_class_where :='
146 AND f.class_code in (&FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
147 END IF;
148
149 IF (l_view_by <> 'CUSTOMER+FII_CUSTOMERS' AND l_cust IS NULL
150 AND l_view_by <> 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS'
151 AND l_class IS NULL) THEN -- use double rollup without cust
152 l_flags := '';
153 l_mv := 'ISC_DBI_SCR_002_MV';
154 IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') THEN
155 l_prod_cat_from := ',
156 ENI_DENORM_HIERARCHIES eni_cat,
157 MTL_DEFAULT_CATEGORY_SETS mdcs';
158 IF (l_prod_cat IS NULL) THEN
159 l_prod_cat_where := '
160 AND f.cat_top_node_flag = ''Y''
161 AND f.item_category_id = eni_cat.imm_child_id
162 AND eni_cat.top_node_flag = ''Y''
163 AND eni_cat.dbi_flag = ''Y''
164 AND eni_cat.object_type = ''CATEGORY_SET''
165 AND eni_cat.object_id = mdcs.category_set_id
166 AND mdcs.functional_area_id = 11';
167 ELSE l_prod_cat_where := '
168 AND f.item_category_id = eni_cat.imm_child_id
169 AND ((eni_cat.leaf_node_flag = ''N'' and
170 eni_cat.child_id <> eni_cat.parent_id and imm_child_id = child_id)
171 OR (eni_cat.leaf_node_flag = ''Y''))
172 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
173 AND eni_cat.dbi_flag = ''Y''
174 AND eni_cat.object_type = ''CATEGORY_SET''
175 AND eni_cat.object_id = mdcs.category_set_id
176 AND mdcs.functional_area_id = 11';
177 END IF;
178 ELSE -- view by <> cat.
179 l_prod_cat_from := ''; -- do not need to join to denorm table
180 IF (l_prod_cat IS NULL) THEN
181 l_prod_cat_where :='
182 AND f.cat_top_node_flag = ''Y''';
183 ELSE -- view by sales group, prod.cat selected
184 l_prod_cat_where :='
185 AND f.item_category_id IN (&ITEM+ENI_ITEM_VBH_CAT)';
186 END IF;
187 END IF;
188
189 ELSE -- use single rollup with customer dimension
190 l_flags := '
191 AND f.item_cat_flag = :ISC_ITEM_CAT_FLAG
192 AND f.customer_flag = :ISC_CUST';
193 l_mv := 'ISC_DBI_SCR_001_MV';
194 IF (l_prod_cat IS NULL) THEN
195 IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') THEN
196 l_prod_cat_from := ',
197 ENI_DENORM_HIERARCHIES eni_cat,
198 MTL_DEFAULT_CATEGORY_SETS mdcs';
199 l_prod_cat_where := '
200 AND f.item_category_id = eni_cat.child_id
201 AND eni_cat.top_node_flag = ''Y''
202 AND eni_cat.dbi_flag = ''Y''
203 AND eni_cat.object_type = ''CATEGORY_SET''
204 AND eni_cat.object_id = mdcs.category_set_id
205 AND mdcs.functional_area_id = 11';
206 ELSE
207 l_prod_cat_from := '';
208 l_prod_cat_where := '';
209 END IF;
210 ELSE -- a prod cat has been selected
211 l_prod_cat_from := ',
212 ENI_DENORM_HIERARCHIES eni_cat,
213 MTL_DEFAULT_CATEGORY_SETS mdcs';
214 l_prod_cat_where := '
215 AND f.item_category_id = eni_cat.child_id
216 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
217 AND eni_cat.dbi_flag = ''Y''
218 AND eni_cat.object_type = ''CATEGORY_SET''
219 AND eni_cat.object_id = mdcs.category_set_id
220 AND mdcs.functional_area_id = 11';
221 END IF;
222 END IF;
223
224
225 IF (l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP') THEN
226 l_viewby_col :='resource_id, sales_grp_id';
227
228 ELSIF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') THEN
229 IF (l_prod_cat IS NULL) THEN
230 l_viewby_col := 'parent_id';
231 ElSE
232 l_viewby_col :='imm_child_id';
233 END IF;
234
235 ELSIF (l_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
236 l_viewby_col :='customer_id';
237
238 ELSIF (l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') THEN
239 l_viewby_col :='class_code';
240
241 END IF;
242
243
244 IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' OR l_prod_cat IS NOT NULL)
245 THEN l_item_cat_flag := 0; -- Product Category
246 ELSE l_item_cat_flag := 1; -- All
247 END IF;
248
249 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
250 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
251
252 IF (l_invalid_curr)
253 THEN l_stmt := '
254 /* Unsupported currency */
255 SELECT 0 VIEWBY,
256 0 VIEWBYID,
257 0 ISC_ATTRIBUTE_2,
258 0 ISC_ATTRIBUTE_3,
259 0 ISC_ATTRIBUTE_1,
260 0 ISC_ATTRIBUTE_4,
261 0 ISC_ATTRIBUTE_5,
262 0 ISC_ATTRIBUTE_6,
263 0 ISC_ATTRIBUTE_7,
264 0 ISC_MEASURE_1,
265 0 ISC_MEASURE_2,
266 0 ISC_MEASURE_3,
267 0 ISC_MEASURE_4,
268 0 ISC_MEASURE_5,
269 0 ISC_MEASURE_6,
270 0 ISC_MEASURE_7,
271 0 ISC_MEASURE_8,
272 0 ISC_MEASURE_16,
273 0 ISC_MEASURE_17,
274 0 ISC_MEASURE_18,
275 0 ISC_MEASURE_19,
276 0 ISC_MEASURE_20,
277 0 ISC_MEASURE_9,
278 0 ISC_MEASURE_10,
279 0 ISC_MEASURE_11,
280 0 ISC_MEASURE_12,
281 0 ISC_MEASURE_13,
282 0 ISC_MEASURE_14,
283 0 ISC_MEASURE_15,
284 0 ISC_MEASURE_21,
285 0 ISC_MEASURE_22,
286 0 ISC_MEASURE_24,
287 0 ISC_MEASURE_25,
288 0 ISC_MEASURE_26,
289 0 ISC_MEASURE_27,
290 0 ISC_MEASURE_28,
291 0 ISC_MEASURE_29
292 FROM dual
293 WHERE 1 = 2';
294
295 ELSE
296
297 l_inner_sql:='
298 ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,
299 ISC_MEASURE_6,ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_16,ISC_MEASURE_17,
300 ISC_MEASURE_18,ISC_MEASURE_19,ISC_MEASURE_20,ISC_MEASURE_9,ISC_MEASURE_10,
301 ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_14,ISC_MEASURE_15,
302 ISC_MEASURE_21,ISC_MEASURE_22,ISC_MEASURE_24,ISC_MEASURE_25,
303 ISC_MEASURE_26,ISC_MEASURE_27,ISC_MEASURE_28,ISC_MEASURE_29
304 FROM (SELECT (rank() over (&ORDER_BY_CLAUSE nulls last,'||l_viewby_col||'))-1 rnk,
305 '||l_viewby_col||',
306 ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,
307 ISC_MEASURE_6,ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_16,ISC_MEASURE_17,
308 ISC_MEASURE_18,ISC_MEASURE_19,ISC_MEASURE_20,ISC_MEASURE_9,ISC_MEASURE_10,
309 ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_14,ISC_MEASURE_15,
310 isc_measure_5 - isc_measure_16 ISC_MEASURE_21, -- Revenue Booked in Prior Periods
311 isc_measure_14 - isc_measure_17 ISC_MEASURE_22, -- Prior - Rev Booked in Prior Per
312 isc_measure_7 - isc_measure_19 ISC_MEASURE_24, -- Gd Total - Rev Booked in Prior Per
313 p_rev_total - p_rev_book_total ISC_MEASURE_25, -- Gd Total - Prior - Rev Booked in Prior Per
314 ISC_MEASURE_26,ISC_MEASURE_27,ISC_MEASURE_28,ISC_MEASURE_29
315 FROM (SELECT '||l_viewby_col||',
316 nvl(c_net_book, 0) ISC_MEASURE_1,
317 (c_net_book - p_net_book)
318 / decode(p_net_book, 0, null,
319 abs(p_net_book)) *100 ISC_MEASURE_2,
320 nvl(sum(c_net_book) over (), 0) ISC_MEASURE_3,
321 (sum(c_net_book) over () - sum(p_net_book) over ())
322 / decode(sum(p_net_book) over (), 0, null,
323 abs(sum(p_net_book) over ())) *100
324 ISC_MEASURE_4,
325 nvl(c_rev_rec, 0) ISC_MEASURE_5,
326 (c_rev_rec - p_rev_rec)
327 / decode(p_rev_rec, 0, null,
328 abs(p_rev_rec)) *100 ISC_MEASURE_6,
329 nvl(sum(c_rev_rec) over (), 0) ISC_MEASURE_7,
330 (sum(c_rev_rec) over () - sum(p_rev_rec) over ())
331 / decode(sum(p_rev_rec) over (), 0, null,
332 abs(sum(p_rev_rec) over ())) *100
333 ISC_MEASURE_8,
334 nvl(c_rev_book, 0) ISC_MEASURE_16, -- Revenue Booked this Period
335 nvl(p_rev_book, 0) ISC_MEASURE_17, -- Prior (Rev BTP)
336 (c_rev_book - p_rev_book)
337 / decode(p_rev_book, 0, null,
338 abs(p_rev_book)) *100 ISC_MEASURE_18, -- Change (Rev BTP)
339 nvl(sum(c_rev_book) over (), 0) ISC_MEASURE_19, -- Gd Total - Rev BTP
340 (sum(c_rev_book) over () - sum(p_rev_book) over ())
341 / decode(sum(p_rev_book) over (), 0, null,
342 abs(sum(p_rev_book) over ())) *100
343 ISC_MEASURE_20, -- Gd Total - Change (Rev BTP)
344 nvl(c_rev_backlog, 0) ISC_MEASURE_9,
345 (c_rev_backlog - p_rev_backlog)
346 / decode(p_rev_backlog, 0, null,
347 abs(p_rev_backlog)) *100 ISC_MEASURE_10,
348 nvl(sum(c_rev_backlog) over (), 0) ISC_MEASURE_11,
349 (sum(c_rev_backlog) over () - sum(p_rev_backlog) over ())
350 / decode(sum(p_rev_backlog) over (), 0, null,
351 abs(sum(p_rev_backlog) over ())) *100
352 ISC_MEASURE_12,
353 nvl(p_net_book, 0) ISC_MEASURE_13,
354 nvl(p_rev_rec, 0) ISC_MEASURE_14,
355 nvl(p_rev_backlog, 0) ISC_MEASURE_15,
356 sum(nvl(p_net_book, 0)) over () ISC_MEASURE_26,
357 sum(nvl(p_rev_rec, 0)) over () ISC_MEASURE_27,
358 sum(nvl(p_rev_book, 0)) over () ISC_MEASURE_28,
359 sum(nvl(p_rev_backlog, 0)) over () ISC_MEASURE_29,
360 nvl(sum(p_rev_rec) over (), 0) p_rev_total, -- Gd Total Prior Revenue
361 nvl(sum(p_rev_book) over (), 0) p_rev_book_total -- Gd Total Prior Rev BTP
362 FROM
363 (SELECT '||l_viewby_col||',
364 sum(c_book_xtd) c_net_book,
365 sum(p_book_xtd) p_net_book,
369 sum(p_rev_book_xtd) p_rev_book,
366 sum(c_rev_rec_xtd) c_rev_rec,
367 sum(p_rev_rec_xtd) p_rev_rec,
368 sum(c_rev_book_xtd) c_rev_book,
370 sum(c_backlog) + sum(c_defer_rev) c_rev_backlog,
371 sum(p_backlog) + sum(p_defer_rev) p_rev_backlog
372 FROM
373 (/* Compute XTD components */
374 SELECT '||l_viewby_col||',
375 decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
376 nvl(net_booked_amt_'||l_curr_suffix||', 0), 0) C_BOOK_XTD,
377 decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
378 nvl(net_booked_amt_'||l_curr_suffix||', 0), 0) P_BOOK_XTD,
379 decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
380 nvl(recognized_amt_'||l_curr_suffix||', 0), 0) C_REV_REC_XTD,
381 decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
382 nvl(recognized_amt_'||l_curr_suffix||', 0), 0) P_REV_REC_XTD,
383 decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
384 nvl('||l_rev_book||', 0), 0) C_REV_BOOK_XTD,
385 decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
386 nvl('||l_rev_book||', 0), 0) P_REV_BOOK_XTD,
387
388 0 C_BACKLOG,
389 0 P_BACKLOG,
390 0 C_DEFER_REV,
391 0 P_DEFER_REV
392 FROM '||l_mv||' f,
393 FII_TIME_RPT_STRUCT_V cal'
394 ||l_prod_cat_from||'
395 WHERE f.time_id = cal.time_id
396 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
397 AND cal.period_type_id = f.period_type_id
398 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id'
399 ||l_flags
400 ||l_sg_where||l_prod_cat_where||l_cust_where||l_class_where||'
401 UNION ALL /* Compute ITD components */
402 SELECT '||l_viewby_col||',
403 0 C_BOOK_XTD,
404 0 P_BOOK_XTD,
405 0 C_REV_REC_XTD,
406 0 P_REV_REC_XTD,
407 0 C_REV_BOOK_XTD,
408 0 P_REV_BOOK_XTD,
409 decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
410 nvl(backlog_amt_'||l_curr_suffix||', 0), 0) C_BACKLOG,
411 decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
412 nvl(backlog_amt_'||l_curr_suffix||', 0), 0) P_BACKLOG,
413 decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
414 nvl(deferred_amt_'||l_curr_suffix||', 0), 0) C_DEFER_REV,
415 decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
416 nvl(deferred_amt_'||l_curr_suffix||', 0), 0) P_DEFER_REV
417 FROM '||l_mv||' f,
418 FII_TIME_RPT_STRUCT_V cal'
419 ||l_prod_cat_from||'
420 WHERE f.time_id = cal.time_id
421 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
422 AND cal.period_type_id = f.period_type_id
423 AND bitand(cal.record_type_id,1143) = cal.record_type_id'
424 ||l_flags
425 ||l_sg_where||l_prod_cat_where||l_cust_where||l_class_where||'
426 ) GROUP BY '||l_viewby_col||'))) c,';
427
428
429 IF l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' THEN
430 IF (l_prod_cat IS NULL) THEN
431 l_cat_join := 'AND c.parent_id = ecat.id';
432 ElSE
433 l_cat_join := 'AND c.imm_child_id = ecat.id';
434 END IF;
435
436 l_stmt := '
437 SELECT ecat.value VIEWBY,
438 ecat.id VIEWBYID,
439 NULL ISC_ATTRIBUTE_2, -- Drill - Sales Group
440 decode(ecat.leaf_node_flag, ''Y'',
441 NULL,
442 ''pFunctionName='||l_func||'&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'')
443 ISC_ATTRIBUTE_3, -- Drill - Product Category
444 NULL ISC_ATTRIBUTE_1, -- Drill - Customer Classification
445 ''pFunctionName=ISC_DBI_NET_BOOK_FULF&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
446 ISC_ATTRIBUTE_4, -- Drill - Net Booked
447 ''pFunctionName=FII_AR_SG_PROD_REV&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
448 ISC_ATTRIBUTE_5, -- Drill - Revenue
449 ''pFunctionName=FII_AR_SG_PROD_REV&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
450 ISC_ATTRIBUTE_6, -- Drill - Revenue Booked this Period
451 ''pFunctionName=ISC_DBI_REV_PIPELINE&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
452 ISC_ATTRIBUTE_7, -- Drill - Product Revenue Backlog'
453 ||l_inner_sql||'
454 ENI_ITEM_VBH_NODES_V ecat
455 WHERE ecat.parent_id = ecat.child_id
456 '||l_cat_join||'
457 AND ((c.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
458 ORDER BY rnk';
459
460 ELSIF l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP' THEN
461 l_stmt := '
462 SELECT decode(c.resource_id,NULL,g.group_name,
463 r.resource_name) VIEWBY,
464 decode(c.resource_id,NULL,to_char(c.sales_grp_id),
465 c.resource_id||''.''||c.sales_grp_id)
466 VIEWBYID,
467 decode(c.resource_id, NULL,
468 ''pFunctionName='||l_func||'&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY'',
469 NULL) ISC_ATTRIBUTE_2, -- Drill - Sales Group
470 NULL ISC_ATTRIBUTE_3, -- Drill - Product Category
471 NULL ISC_ATTRIBUTE_1, -- Drill - Customer Classification
472 decode(c.sales_grp_id, -1, NULL,
473 ''pFunctionName=ISC_DBI_NET_BOOK_FULF&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY'')
474 ISC_ATTRIBUTE_4, -- Drill - Net Booked
475 decode(c.sales_grp_id, -1, NULL,
476 ''pFunctionName=FII_AR_SG_PROD_REV&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY'')
477 ISC_ATTRIBUTE_5, -- Drill - Revenue
478 decode(c.sales_grp_id, -1, NULL,
479 ''pFunctionName=FII_AR_SG_PROD_REV&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY'')
480 ISC_ATTRIBUTE_6, -- Drill - Revenue Booked this Period
481 decode(c.sales_grp_id, -1, NULL,
485 JTF_RS_GROUPS_VL g,
482 ''pFunctionName=ISC_DBI_REV_PIPELINE&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY'')
483 ISC_ATTRIBUTE_7, -- Drill - Product Revenue Backlog'
484 ||l_inner_sql||'
486 JTF_RS_RESOURCE_EXTNS_VL r
487 WHERE c.sales_grp_id = g.group_id
488 AND c.resource_id = r.resource_id(+)
489 AND ((c.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
490 ORDER BY rnk' ;
491
492 ELSIF l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
493 l_stmt := '
494 SELECT cc.value VIEWBY,
495 cc.id VIEWBYID,
496 NULL ISC_ATTRIBUTE_2, -- Drill - Sales Group
497 NULL ISC_ATTRIBUTE_3, -- Drill - Product Category
498 ''pFunctionName='||l_func||'&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=CUSTOMER+FII_CUSTOMERS''
499 ISC_ATTRIBUTE_1, -- Drill - Customer Classification
500 ''pFunctionName=ISC_DBI_NET_BOOK_FULF&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
501 ISC_ATTRIBUTE_4, -- Drill - Net Booked
502 ''pFunctionName=FII_AR_SG_PROD_REV&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
503 ISC_ATTRIBUTE_5, -- Drill - Revenue
504 ''pFunctionName=FII_AR_SG_PROD_REV&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
505 ISC_ATTRIBUTE_6, -- Drill - Revenue Booked this Period
506 ''pFunctionName=ISC_DBI_REV_PIPELINE&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
507 ISC_ATTRIBUTE_7, -- Drill - Product Revenue Backlog'
508 ||l_inner_sql||'
509 FII_PARTNER_MKT_CLASS_V cc
510 WHERE c.class_code = cc.id
511 AND ((c.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
512 ORDER BY rnk';
513
514 ELSE -- l_view_by = 'CUSTOMER+FII_CUSTOMERS'
515 l_stmt := '
516 SELECT cust.value VIEWBY,
517 cust.id VIEWBYID,
518 NULL ISC_ATTRIBUTE_2, -- Drill - Sales Group
519 NULL ISC_ATTRIBUTE_3, -- Drill - Product Category
520 NULL ISC_ATTRIBUTE_1, -- Drill - Customer Classification
521 ''pFunctionName=ISC_DBI_NET_BOOK_FULF&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
522 ISC_ATTRIBUTE_4, -- Drill - Net Booked
523 ''pFunctionName=FII_AR_SG_PROD_REV&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
524 ISC_ATTRIBUTE_5, -- Drill - Revenue
525 ''pFunctionName=FII_AR_SG_PROD_REV&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
526 ISC_ATTRIBUTE_6, -- Drill - Revenue Booked this Period
527 ''pFunctionName=ISC_DBI_REV_PIPELINE&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
528 ISC_ATTRIBUTE_7, -- Drill - Product Revenue Backlog'
529 ||l_inner_sql||'
530 FII_CUSTOMERS_V cust
531 WHERE c.customer_id = cust.id
532 AND ((c.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
533 ORDER BY rnk';
534
535 END IF;
536
537 END IF;
538
539 x_custom_sql := l_stmt;
540
541 l_custom_rec.attribute_name := ':ISC_CUST';
542 l_custom_rec.attribute_value := l_cust_flag;
543 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
544 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
545 x_custom_output.EXTEND;
546 x_custom_output(1) := l_custom_rec;
547
548 l_custom_rec.attribute_name := ':ISC_SG';
549 l_custom_rec.attribute_value := to_char(l_sg_sg);
550 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
551 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
552 x_custom_output.extend;
553 x_custom_output(2) := l_custom_rec;
554
555 l_custom_rec.attribute_name := ':ISC_RES';
556 l_custom_rec.attribute_value := to_char(l_sg_res);
557 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
558 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
559 x_custom_output.extend;
560 x_custom_output(3) := l_custom_rec;
561
562 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
563 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
564 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
565 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
566 x_custom_output.extend;
567 x_custom_output(4) := l_custom_rec;
568
569 END get_sql;
570
571 END ISC_DBI_REV_BACKLOG_PKG ;
572