[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_PRC_PKG
Source
1 PACKAGE BODY eni_dbi_prc_pkg AS
2 /*$Header: ENIPRCPB.pls 120.0 2005/05/26 19:34:46 appldev noship $*/
3
4 PROCEDURE get_sql
5 (
6 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
7 x_custom_sql OUT NOCOPY VARCHAR2,
8 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
9 )
10 IS
11
12 l_period_type VARCHAR2(100);
13 l_period_bitand NUMBER;
14 l_view_by VARCHAR2(100);
15 l_as_of_date DATE;
16 l_prev_as_of_date DATE;
17 l_report_start DATE;
18 l_cur_period NUMBER;
19 l_days_into_period NUMBER;
20 l_comp_type VARCHAR2(100);
21 l_category VARCHAR2(100);
22 l_category1 VARCHAR2(100);
23 l_item VARCHAR2(5000);
24 l_org VARCHAR2(5000);
25 -- l_item_org VARCHAR2(5000); -- eletuchy 11-17-04: made unnecessary by ITEM+ENI_ITEM BIS bind
26 l_id_column VARCHAR2(100);
27 l_order_by VARCHAR2(100);
28 l_drill VARCHAR2(10);
29 l_status VARCHAR2(100);
30 l_priority VARCHAR2(100);
31 l_reason VARCHAR2(100);
32 l_lifecycle_phase VARCHAR2(100);
33 l_currency VARCHAR2(100);
34 l_bom_type VARCHAR2(100);
35 l_type VARCHAR2(100);
36 l_manager VARCHAR2(100);
37 l_lob VARCHAR2(100);
38
39 l_from_clause VARCHAR2(1000);
40 l_where_clause VARCHAR2(1000) := NULL;
41 l_where_clause1 VARCHAR2(1000) := NULL;
42 l_group_by_clause VARCHAR2(500);
43
44 l_err_msg VARCHAR2(100);
45
46 l_table VARCHAR2(100);
47
48 -- The record structure for bind variable values
49 l_custom_rec BIS_QUERY_ATTRIBUTES;
50
51 l_lookup VARCHAR2(100);
52 l_lookup_table VARCHAR2(100);
53 l_summary VARCHAR2(100);
54 l_oex_columns VARCHAR2(500);
55 l_oex_total_columns VARCHAR2(500);
56 l_drill_to_cat_url VARCHAR2(500);
57 l_drill_to_other_expenses VARCHAR2(500);
58 top_flag VARCHAR2(1);
59 leaf_flag VARCHAR2(1);
60 l_where_clause_outer VARCHAR2(1000);
61 l_revenue VARCHAR2(100);
62 l_cogs VARCHAR2(100);
63 l_expense VARCHAR2(100);
64 l_currency_value VARCHAR2(100);
65
66 BEGIN
67
68
69 l_revenue := 'rev_amount';
70 l_cogs := 'cogs_amount';
71 l_expense := 'exp_amount';
72
73 /* eletuchy 11-17-04: made unnecessary by ITEM+ENI_ITEM BIS bind
74 FOR i in 1..p_page_parameter_tbl.COUNT LOOP
75 IF ( (p_page_parameter_tbl(i).parameter_name = 'ITEM+ENI_ITEM')
76 OR ( p_page_parameter_tbl(i).parameter_name = 'ITEM+ENI_ITEM_ORG') ) THEN
77 l_item_org := p_page_parameter_tbl(i).parameter_id;
78 EXIT;
79 END IF;
80 END LOOP;
81 */
82
83 eni_dbi_util_pkg.get_parameters
84 (
85 p_page_parameter_tbl,
86 l_period_type,
87 l_period_bitand,
88 l_view_by,
89 l_as_of_date,
90 l_prev_as_of_date,
91 l_report_start,
92 l_cur_period,
93 l_days_into_period,
94 l_comp_type,
95 l_category,
96 l_item,
97 l_org,
98 l_id_column,
99 l_order_by,
100 l_drill,
101 l_status,
102 l_priority,
103 l_reason,
104 l_lifecycle_phase,
105 l_currency,
106 l_bom_type,
107 l_type,
108 l_manager,
109 l_lob
110 );
111
112 l_category1 := TRIM(both '''' from l_category);
113
114 l_currency_value := eni_dbi_util_pkg.get_curr_sec;
115
116 IF (l_category1 IS NOT NULL) THEN
117 select top_node_flag,leaf_node_flag
118 into top_flag,leaf_flag
119 from eni_denorm_hierarchies edh
120 where edh.parent_id = l_category1
121 and edh.child_id = edh.parent_id;
122
123 IF (top_flag = 'Y' and leaf_flag = 'Y') THEN
124 l_where_clause1 := '';
125 ELSE
126 l_where_clause1 := ' AND vbh.parent_id <> vbh.child_id ';
127 END IF;
128 END IF;
129
130 IF (l_currency = l_currency_value) THEN
131 l_revenue := 'rev_sec_amount';
132 l_cogs := 'cogs_sec_amount';
133 l_expense := 'exp_sec_amount';
134 END IF;
135
136 l_oex_columns := ' , NULL AS ENI_MEASURE13, NULL AS ENI_MEASURE14 ';
137 l_oex_total_columns := ' , NULL AS ENI_MEASURE33, NULL AS ENI_MEASURE34 ';
138 l_drill_to_other_expenses := 'decode(ENI_MEASURE13, NULL, NULL, 0, NULL,''pFunctionName=ENI_DBI_OEX_R' ||
139 '&' || 'VIEW_BY_NAME=VIEW_BY_ID' ||
140 '&' || 'VIEW_BY=LOB+FII_LOB' || '&' || 'pParamIds=Y'') ';
141
142 IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') THEN
143 -- VIEW BY PRODUCT CATEGORY
144 l_lookup := 'vbh.value AS VIEWBY, vbh.id AS VIEWBYID, vbh.leaf_node_flag AS leaf_node_flag';
145 l_lookup_table := ', eni_item_vbh_nodes_v vbh';
146 l_drill_to_cat_url := 'decode(leaf_node_flag, ''Y'', ' ||
147 '''pFunctionName=ENI_DBI_PRC_R' || '&' || 'VIEW_BY_NAME=VIEW_BY_ID' ||
148 '&' || 'VIEW_BY=ITEM+ENI_ITEM' || '&' || 'pParamIds=Y'',
149 ''pFunctionName=ENI_DBI_PRC_R' || '&' || 'VIEW_BY_NAME=VIEW_BY_ID' ||
150 '&' || 'VIEW_BY=ITEM+ENI_ITEM_VBH_CAT' || '&' || 'pParamIds=Y'') ';
151 l_group_by_clause := 'vbh.id, vbh.value,vbh.leaf_node_flag';
152
153 IF (l_item IS NULL AND l_category IS NULL) THEN
154
155 l_summary := 'edps2mv';
156 l_from_clause := 'eni_dbi_prc_sum2_mv edps2mv ';
157 l_where_clause := ' AND edps2mv.marker = 2 ' ||
158 ' AND vbh.top_node_flag = ''Y'' ' ||
159 ' AND vbh.parent_id = vbh.child_id ' ||
160 ' AND edps2mv.product_category_id = vbh.child_id ';
161 l_oex_columns := '
162 , SUM( case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
163 then NVL(edps2mv.' || l_expense || ',0) else 0 end ) AS ENI_MEASURE13
164 , SUM(case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
165 then edps2mv.' || l_expense || ' else 0 end ) AS ENI_MEASURE14 ';
166 l_oex_total_columns := '
167 ,SUM(SUM(case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
168 then NVL(edps2mv.' || l_expense || ',0) else 0 end )) OVER() AS ENI_MEASURE33
169 ,SUM(SUM(case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
170 then edps2mv.' || l_expense || ' else 0 end )) OVER() AS ENI_MEASURE34';
171
172 ELSIF (l_item IS NULL AND l_category IS NOT NULL) THEN
173
174 l_summary := 'edps2mv';
175
176 l_from_clause := 'eni_dbi_prc_sum2_mv edps2mv ';
177 l_where_clause := ' AND edps2mv.marker = 2 ' ||
178 ' AND vbh.parent_id = :PRODUCT_CATEGORY ' ||
179 ' AND vbh.id = edps2mv.product_category_id ' ||
180 ' AND vbh.id = vbh.child_id ';
181
182 l_oex_columns := '
183 , SUM( case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
184 then NVL(edps2mv.' || l_expense || ',0) else 0 end ) AS ENI_MEASURE13
185 , SUM(case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
186 then edps2mv.' || l_expense || ' else 0 end ) AS ENI_MEASURE14 ';
187 l_oex_total_columns := '
188 ,SUM(SUM(case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
189 then NVL(edps2mv.' || l_expense || ',0) else 0 end )) OVER() AS ENI_MEASURE33
190 ,SUM(SUM(case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
191 then edps2mv.' || l_expense || ' else 0 end )) OVER() AS ENI_MEASURE34';
192 l_where_clause := l_where_clause || l_where_clause1;
193
194 ELSIF (l_item IS NOT NULL AND l_category IS NULL) THEN
195
196 l_summary := 'edps1mv';
197 l_from_clause := ' eni_dbi_prc_sum1_mv edps1mv , eni_denorm_hierarchies edh';
198 l_where_clause := ' AND edps1mv.item_org_id IN ('|| '&' || 'ITEM+ENI_ITEM)' ||
199 ' AND edh.top_node_flag = ''Y'' ' ||
200 ' AND edps1mv.product_category_id = edh.child_id ' ||
201 ' AND vbh.id = edh.imm_child_id ' ||
202 ' AND vbh.parent_id = vbh.child_id ' ||
203 ' AND vbh.child_id = vbh.id ';
204
205 ELSIF (l_item IS NOT NULL AND l_category IS NOT NULL) THEN
206
207 l_summary := 'edps1mv';
208 l_from_clause := ' eni_dbi_prc_sum1_mv edps1mv ';
209 l_where_clause := ' AND edps1mv.item_org_id IN IN ('|| '&' || 'ITEM+ENI_ITEM)' ||
210 ' AND vbh.parent_id = :PRODUCT_CATEGORY '||
211 ' AND edps1mv.product_category_id = vbh.child_id ';
212
213 END IF;
214
215 -- modifications by achampan for bug X
216 x_custom_sql := '
217 SELECT vbh.value AS VIEWBY, vbh.id AS VIEWBYID
218 , ' || l_drill_to_cat_url || ' AS ENI_ATTRIBUTE4
219 , ENI_MEASURE1 -- current revenue
220 , ENI_MEASURE2 -- prior revenue
221 , ENI_MEASURE7 -- current cogs
222 , ENI_MEASURE8 -- prior cogs
223 , ((ENI_MEASURE2 - ENI_MEASURE8)
224 /decode(ENI_MEASURE2, 0, null, ENI_MEASURE2))*100
225 AS ENI_MEASURE11 -- prior gross margin
226 , ((ENI_MEASURE2 - ENI_MEASURE8 - ENI_MEASURE14)
227 /decode(ENI_MEASURE2, 0, null, ENI_MEASURE2))*100
228 AS ENI_MEASURE17 -- prior product margin
229 , ((ENI_MEASURE1 - ENI_MEASURE7)
230 /decode(ENI_MEASURE1, 0, null, ENI_MEASURE1))*100
231 AS ENI_MEASURE10 -- current gross margin
232 , ENI_MEASURE13 -- current other expenses
233 , ENI_MEASURE14 -- prior other expenses
234 , ((ENI_MEASURE1 - ENI_MEASURE7 - ENI_MEASURE13)
235 /decode(ENI_MEASURE1, 0, null, ENI_MEASURE1))*100
236 AS ENI_MEASURE16 -- current product margin
237 , ENI_MEASURE21 -- current revenue grand total
238 , ENI_MEASURE22 -- prior revenue grand total
239 , ENI_MEASURE27 -- current cogs grand total
240 , ENI_MEASURE28 -- prior cogs grand total
241 , ((ENI_MEASURE21 - ENI_MEASURE27)
242 /decode(ENI_MEASURE21, 0, null, ENI_MEASURE21))*100
243 AS ENI_MEASURE30 -- gross margin grand total
244 , (
245 (ENI_MEASURE21-ENI_MEASURE27)
246 /decode(ENI_MEASURE21, 0, null, ENI_MEASURE21)
247 )
248 -
249 (
250 (ENI_MEASURE22-ENI_MEASURE28)
251 /decode(ENI_MEASURE22, 0, null, ENI_MEASURE22)
252 )
253 AS ENI_MEASURE32 -- gross margin change
254 , ENI_MEASURE33 -- current other expenses grand total
255 , ENI_MEASURE34 -- prior other expenses grand total
256 , ((ENI_MEASURE21 - ENI_MEASURE27 - ENI_MEASURE33) /decode(ENI_MEASURE21, 0, null, ENI_MEASURE21))*100 AS ENI_MEASURE36 -- product margin grand total
257 , (
258 (
259 (ENI_MEASURE21-ENI_MEASURE27-ENI_MEASURE33)
260 /decode(ENI_MEASURE21, 0, null, ENI_MEASURE21)
261 -
262 (
263 (ENI_MEASURE22-ENI_MEASURE28-ENI_MEASURE34)
264 /decode(ENI_MEASURE22, 0, null, ENI_MEASURE22)
265 )
266 )
267 )
268 AS ENI_MEASURE38 -- product margin change
269 , ' || l_drill_to_other_expenses || ' -- drill across url for other expenses
270 AS ENI_MEASURE43
271 , NVL(ENI_MEASURE7,0)+NVL(ENI_MEASURE13,0)
272 AS ENI_MEASURE47 -- for Costs(COGS+Expenses) on graph 1
273 , ((ENI_MEASURE22 - ENI_MEASURE28 - ENI_MEASURE34) /decode(ENI_MEASURE22, 0, null, ENI_MEASURE22))*100 AS ENI_MEASURE20 -- Prior product margin grand total
274
275 FROM
276 (
277 SELECT t.*, (rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last, id) - 1) col_rank
278 FROM
279 (
280 SELECT
281 vbh.id,
282 SUM
283 (
284 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
285 then
286 NVL(' || l_summary || '.' || l_revenue || ',0)
287 else
288 0
289 end
290 ) AS ENI_MEASURE1
291 , SUM
292 (
293 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
294 then
295 ' || l_summary || '.' || l_revenue || '
296 else
297 0
298 end
299 ) AS ENI_MEASURE2
300 , SUM
301 (
302 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
303 then
304 NVL(' || l_summary || '.' || l_cogs || ',0)
305 else
306 0
307 end
308 ) AS ENI_MEASURE7
309 , SUM
310 (
311 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
312 then
313 ' || l_summary || '.' || l_cogs || '
314 else
315 0
316 end
317 ) AS ENI_MEASURE8
318 '||l_oex_columns||'
319 ,SUM
320 (
321 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
322 then
323 NVL(((' || l_summary || '.rev_amount - ' || l_summary || '.cogs_amount)
324 /decode(' || l_summary || '.rev_amount, 0, null, ' || l_summary || '.rev_amount))*100,0)
325 else
326 0
327 end
328 )
329 AS ENI_MEASURE10
330 , SUM
331 (
332 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
333 then
334 NVL(((' || l_summary || '.' || l_revenue || ' - ' || l_summary || '.' || l_cogs || '
335 - ' || l_summary || '.' || l_expense || ')
336 /decode(' || l_summary || '.' || l_revenue || ', 0, null, ' || l_summary || '.' || l_revenue || '))*100,0)
337 else
338 0
339 end
340 )
341 AS ENI_MEASURE16
342 , SUM
343 (
344 SUM
345 (
346 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
347 then
348 NVL(' || l_summary || '.' || l_revenue || ',0)
349 else
350 0
351 end
352 )
353 ) OVER()
354 AS ENI_MEASURE21
355 , SUM
356 (
357 SUM
358 (
359 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
360 then
361 ' || l_summary || '.' || l_revenue || '
362 else
363 0
364 end
365 )
366 ) OVER()
367 AS ENI_MEASURE22
368 , SUM
369 (
370 SUM
371 (
372 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
373 then
374 NVL(' || l_summary || '.' || l_cogs || ',0)
375 else
376 0
377 end
378 )
379 ) OVER()
380 AS ENI_MEASURE27
381 , SUM
382 (
383 SUM
384 (
385 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
386 then
387 NVL(' || l_summary || '.' || l_cogs || ',0)
388 else
389 0
390 end
391 )
392 ) OVER()
393 AS ENI_MEASURE28
394 '||l_oex_total_columns||'
395 FROM
396 ' || l_from_clause || '
397 , fii_time_rpt_struct ftrs
398 ' || l_lookup_table ||'
399 WHERE
400 ' || l_summary || '.time_id = ftrs.time_id
401 AND
402 (
403 ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
404 OR ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
405 )
406 AND BITAND(ftrs.record_type_id, &' || 'BIS_NESTED_PATTERN) = ftrs.record_type_id
407 ' || l_where_clause || '
408 GROUP BY
409 ' || l_group_by_clause || '
410 )t
411 where
412 NOT( (ENI_MEASURE1 = 0) AND (NVL(ENI_MEASURE2,0) = 0) AND
413 (ENI_MEASURE7 = 0) AND (NVL(ENI_MEASURE8,0) = 0) AND
414 (NVL(ENI_MEASURE13,0) = 0) AND (NVL(ENI_MEASURE14,0) = 0))
415 ) a '||l_lookup_table||'
416 where ((a.col_rank between &'||'START_INDEX and &'||'END_INDEX) OR (&'||'END_INDEX = -1)) '||
417 l_where_clause_outer || ' and a.id = vbh.id and vbh.parent_id = vbh.child_id order by a.col_rank' ;
418
419 ELSIF (l_view_by = 'ITEM+ENI_ITEM') THEN
420
421 -- view by item
422 l_lookup := 'eiv.value AS VIEWBY, eiv.id AS VIEWBYID';
423 l_lookup_table := ', eni_item_v eiv';
424 l_summary := 'edps1mv';
425 l_group_by_clause := ' item_org_id ';
426 l_drill_to_cat_url := 'NULL';
427 l_where_clause_outer :='';
428 l_where_clause_outer := ' AND eiv.id = a.item_org_id ';
429 IF (l_item IS NULL AND l_category IS NULL) THEN
430 l_from_clause := ' eni_dbi_prc_sum1_mv edps1mv ';
431 l_where_clause :='';
432 ELSIF (l_item IS NULL AND l_category IS NOT NULL) THEN
433 l_from_clause := ' eni_dbi_prc_sum1_mv edps1mv, eni_denorm_hierarchies edh ';
434 l_where_clause := ' AND edh.parent_id = :PRODUCT_CATEGORY ' ||
435 ' AND edps1mv.product_category_id = edh.child_id ';-- ||
436 --' AND edps1mv.inventory_item_id = eiv.inventory_item_id ' ||
437 --' AND edps1mv.organization_id = eiv.organization_id ';
438 ELSIF (l_item IS NOT NULL AND l_category IS NULL) THEN
439 l_from_clause := ' eni_dbi_prc_sum1_mv edps1mv ';
440 l_where_clause := ' AND edps1mv.item_org_id IN ('|| '&' || 'ITEM+ENI_ITEM)' ;--||
441 --' AND edps1mv.item_org_id = eiv.id ';
442 l_where_clause_outer := ' AND eiv.id = a.item_org_id ';
443 ELSIF (l_item IS NOT NULL AND l_category IS NOT NULL) THEN
444 l_from_clause := ' eni_dbi_prc_sum1_mv edps1mv, eni_denorm_hierarchies edh ';
445 l_where_clause := ' AND edh.parent_id = :PRODUCT_CATEGORY ' ||
446 ' AND edps1mv.product_category_id = edh.child_id ' ||
447 ' AND edps1mv.item_org_id IN ('|| '&' || 'ITEM+ENI_ITEM) '; -- ||
448 -- ' AND edps1mv.item_org_id IN (' || l_item_org || ')'; -- ||
449 --' AND edps1mv.item_org_id = eiv.id ';
450 END IF;
451
452 -- achampan: added rank - 1 to fix windowing of item viewby
453 x_custom_sql := '
454 SELECT eiv.value as VIEWBY
455 , eiv.id as VIEWBYID
456 , ' || l_drill_to_cat_url || ' AS ENI_ATTRIBUTE4
457 , ENI_MEASURE1
458 , ENI_MEASURE2
459 , ENI_MEASURE7
460 , ENI_MEASURE8
461 , ((ENI_MEASURE2 - ENI_MEASURE8)
462 /decode(ENI_MEASURE2, 0, null, ENI_MEASURE2))*100
463 AS ENI_MEASURE11
464 , ((ENI_MEASURE2 - ENI_MEASURE8 - ENI_MEASURE14)
465 /decode(ENI_MEASURE2, 0, null, ENI_MEASURE2))*100
466 AS ENI_MEASURE17
467 , ((ENI_MEASURE1 - ENI_MEASURE7)
468 /decode(ENI_MEASURE1, 0, null, ENI_MEASURE1))*100
469 AS ENI_MEASURE10
470 , ENI_MEASURE13
471 , ENI_MEASURE14
472 ,((ENI_MEASURE1 - ENI_MEASURE7 - ENI_MEASURE13)
473 /decode(ENI_MEASURE1, 0, null, ENI_MEASURE1))*100
474 AS ENI_MEASURE16
475 , ENI_MEASURE21
476 , ENI_MEASURE22
477 , ENI_MEASURE27
478 , ENI_MEASURE28
479 , ((ENI_MEASURE21 - ENI_MEASURE27)
480 /decode(ENI_MEASURE21, 0, null, ENI_MEASURE21))*100
481 AS ENI_MEASURE30
482 , (
483 (ENI_MEASURE21-ENI_MEASURE27)
484 /decode(ENI_MEASURE21, 0, null, ENI_MEASURE21)
485 )
486 -
487 (
488 (ENI_MEASURE22-ENI_MEASURE28)
489 /decode(ENI_MEASURE22, 0, null, ENI_MEASURE22)
490 )
491 AS ENI_MEASURE32
492 , ENI_MEASURE33
493 , ENI_MEASURE34
494 , ((ENI_MEASURE21 - ENI_MEASURE27 - ENI_MEASURE33) /decode(ENI_MEASURE21, 0, null, ENI_MEASURE21))*100 AS ENI_MEASURE36
495 , (
496 (
497 (ENI_MEASURE21-ENI_MEASURE27-ENI_MEASURE33)
498 /decode(ENI_MEASURE21, 0, null, ENI_MEASURE21)
499 -
500 (
501 (ENI_MEASURE22-ENI_MEASURE28-ENI_MEASURE34)
502 /decode(ENI_MEASURE22, 0, null, ENI_MEASURE22)
503 )
504 )
505 )
506 AS ENI_MEASURE38
507 , ' || l_drill_to_other_expenses || '
508 AS ENI_MEASURE43
509 , NVL(ENI_MEASURE7,0)+NVL(ENI_MEASURE13,0)
510 AS ENI_MEASURE47
511 , ((ENI_MEASURE22 - ENI_MEASURE28 - ENI_MEASURE34) /decode(ENI_MEASURE22, 0, null, ENI_MEASURE22))*100 AS ENI_MEASURE20 -- Prior product margin grand total
512 FROM
513 (
514 SELECT t.*, (rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last, item_org_id) - 1) col_rank
515 FROM
516 (
517 SELECT
518 item_org_id,
519 SUM
520 (
521 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
522 then
523 NVL(' || l_summary || '.' || l_revenue || ',0)
524 else
525 0
526 end
527 )
528 AS ENI_MEASURE1
529 , SUM
530 (
531 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
532 then
533 ' || l_summary || '.' || l_revenue || '
534 else
535 0
536 end
537 )
538 AS ENI_MEASURE2
539 , SUM
540 (
541 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
542 then
543 NVL(' || l_summary || '.' || l_cogs || ',0)
544 else
545 0
546 end
547 )
548 AS ENI_MEASURE7
549 , SUM
550 (
551 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
552 then
553 ' || l_summary || '.' || l_cogs || '
554 else
555 0
556 end
557 )
558 AS ENI_MEASURE8
559 '||l_oex_columns||'
560 , SUM
561 (
562 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
563 then
564 NVL(((' || l_summary || '.rev_amount - ' || l_summary || '.cogs_amount)
565 /decode(' || l_summary || '.rev_amount, 0, null, ' || l_summary || '.rev_amount))*100,0)
566 else
567 0
568 end
569 )
570 AS ENI_MEASURE10
571 , SUM
572 (
573 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
574 then
575 NVL(((' || l_summary || '.' || l_revenue || ' - ' || l_summary || '.' || l_cogs || '
576 - ' || l_summary || '.' || l_expense || ')
577 /decode(' || l_summary || '.' || l_revenue || ', 0, null, ' || l_summary || '.' || l_revenue || '))*100,0)
578 else
579 0
580 end
581 )
582 AS ENI_MEASURE16
583 , SUM
584 (
585 SUM
586 (
587 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
588 then
589 NVL(' || l_summary || '.' || l_revenue || ',0)
590 else
591 0
592 end
593 )
594 ) OVER() AS ENI_MEASURE21
595 , SUM
596 (
597 SUM
598 (
599 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
600 then
601 ' || l_summary || '.' || l_revenue || '
602 else
603 0
604 end
605 )
606 ) OVER() AS ENI_MEASURE22
607 , SUM
608 (
609 SUM
610 (
611 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
612 then
613 NVL(' || l_summary || '.' || l_cogs || ',0)
614 else
615 0
616 end
617 )
618 ) OVER() AS ENI_MEASURE27
619 , SUM
620 (
621 SUM
622 (
623 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
624 then
625 NVL(' || l_summary || '.' || l_cogs || ',0)
626 else
627 0
628 end
629 )
630 ) OVER() AS ENI_MEASURE28
631 '||l_oex_total_columns||'
632 FROM
633 ' || l_from_clause || '
634 , fii_time_rpt_struct ftrs
635 WHERE
636 ' || l_summary || '.time_id = ftrs.time_id
637 AND
638 (
639 ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
640 OR ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
641 )
642 AND BITAND(ftrs.record_type_id, &' || 'BIS_NESTED_PATTERN) = ftrs.record_type_id
643 ' || l_where_clause || '
644 GROUP BY
645 ' || l_group_by_clause || '
646 )t
647 where
648 NOT( (ENI_MEASURE1 = 0) AND (NVL(ENI_MEASURE2,0) = 0) AND
649 (ENI_MEASURE7 = 0) AND (NVL(ENI_MEASURE8,0) = 0) AND
650 (NVL(ENI_MEASURE13,0) = 0) AND (NVL(ENI_MEASURE14,0) = 0))
651 ) a '||l_lookup_table||'
652 where ((a.col_rank between &'||'START_INDEX and &'||'END_INDEX) OR (&'||'END_INDEX = -1)) '||
653 l_where_clause_outer || 'order by a.col_rank' ;
654
655 -- Added 'order by a.col_rank' to fix bug # 3760722
656
657 END IF;
658
659 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
660 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
661
662 IF (l_category1 is not null ) THEN
663 x_custom_output.extend;
664
665 l_custom_rec.attribute_name := ':PRODUCT_CATEGORY';
666 l_custom_rec.attribute_value := l_category1;
667 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
668 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
669 x_custom_output.extend;
670 x_custom_output(1) := l_custom_rec;
671 END IF;
672
673 END get_sql;
674
675 END eni_dbi_prc_pkg;