[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_IVA_PKG
Source
1 PACKAGE BODY eni_dbi_iva_pkg AS
2 /*$Header: ENIIVAPB.pls 120.0.12000000.2 2007/02/22 08:49:28 lparihar ship $*/
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_item VARCHAR2(5000);
23 l_item_org VARCHAR2(5000);
24 l_id_column VARCHAR2(100);
25 l_order_by VARCHAR2(100);
26 l_drill VARCHAR2(100);
27 l_status VARCHAR2(100);
28 l_priority VARCHAR2(100);
29 l_reason VARCHAR2(100);
30 l_lifecycle_phase VARCHAR2(100);
31 l_currency VARCHAR2(100);
32 l_bom_type VARCHAR2(100);
33 l_type VARCHAR2(100);
34 l_manager VARCHAR2(100);
35 l_lob VARCHAR2(100);
36
37 l_from_clause VARCHAR2(1000);
38 l_from_clause_1 VARCHAR2(1000):= NULL;
39 l_where_clause VARCHAR2(1000) := NULL;
40 l_group_by_clause VARCHAR2(1000) := NULL;
41
42 l_comp_where VARCHAR2(100);
43 l_org VARCHAR2(500);
44 l_summary VARCHAR2(100);
45 l_lookup_select VARCHAR2(100);
46 l_lookup VARCHAR2(100);
47 l_lookup_group VARCHAR2(100);
48 l_drill_to_cat_url VARCHAR2(500);
49 l_err_msg VARCHAR2(100);
50 l_excep VARCHAR2(1000);
51 l_where_clause1 VARCHAR2(200);
52 l_category1 NUMBER(15);
53 top_flag VARCHAR2(1):=NULL;
54 leaf_flag VARCHAR2(1):=NULL;
55 -- The record structure for bind variable values
56 l_custom_rec BIS_QUERY_ATTRIBUTES;
57 garbage VARCHAR2(1000);
58 l_lookup_where VARCHAR2(1000);
59 l_lookup_inner_select VARCHAR2(100);
60 l_rank_measure VARCHAR2(20);
61
62 l_curr_suffix VARCHAR2(20);
63 l_curr VARCHAR2(100);
64
65 BEGIN
66
67 -- Getting the value for the item org necessary for multiple item selection
68 for i in 1..p_page_parameter_tbl.COUNT
69 LOOP
70 IF ((p_page_parameter_tbl(i).parameter_name = 'ITEM+ENI_ITEM')
71 OR (p_page_parameter_tbl(i).parameter_name = 'ITEM+ENI_ITEM_ORG')) THEN
72
73 l_item_org := p_page_parameter_tbl(i).parameter_id;
74
75 END IF;
76
77 END LOOP;
78
79 eni_dbi_util_pkg.get_parameters(
80 p_page_parameter_tbl,
81 l_period_type,
82 l_period_bitand,
83 l_view_by,
84 l_as_of_date,
85 l_prev_as_of_date,
86 l_report_start,
87 l_cur_period,
88 l_days_into_period,
89 l_comp_type,
90 l_category,
91 l_item,
92 l_org,
93 l_id_column,
94 l_order_by,
95 l_drill,
96 l_status,
97 l_priority,
98 l_reason,
99 l_lifecycle_phase,
100 l_currency,
101 l_bom_type,
102 l_type,
103 l_manager,
104 l_lob
105 );
106
107 l_category1 := TRIM(both '''' from l_category);
108 IF (l_category1 IS NOT NULL) THEN
109 select top_node_flag,leaf_node_flag
110 into top_flag,leaf_flag
111 from eni_denorm_hierarchies edh
112 where edh.parent_id = l_category1
113 and edh.child_id = edh.parent_id;
114
115 IF (top_flag = 'Y' and leaf_flag = 'Y') THEN
116
117 l_where_clause1 := '';
118
119 ELSE
120
121 l_where_clause1 := ' AND lookupv.parent_id <> lookupv.child_id ';
122
123 END IF;
124
125 END IF;
126
127 l_curr_suffix :=
128 CASE l_currency
129 WHEN ENI_DBI_UTIL_PKG.get_curr_sec THEN 'sg' -- secondary global currency
130 ELSE 'g' -- primary global currency (default)
131 END;
132
133 l_summary := 'edismv';
134 IF (l_view_by = 'ITEM+ENI_ITEM') THEN
135
136 -- Modified the clauses for product and organization viewbys to provide windowing feature. Bug # 3781824
137 -- VIEW BY IS PRODUCT
138 l_lookup_select := 'lookupv.value VIEWBY, lookupv.id VIEWBYID';
139 l_lookup := ', ENI_ITEM_V lookupv';
140 l_group_by_clause := ' edismv.item_org_id ';
141 l_drill_to_cat_url := 'NULL';
142 l_lookup_inner_select := ' edismv.item_org_id ';
143 l_lookup_where := ' and b.item_org_id = lookupv.id ';
144 l_rank_measure := 'item_org_id';
145
146 IF (l_item_org IS NULL AND l_category IS NULL) THEN
147
148 l_from_clause := ' eni_dbi_inv_sum_mv edismv ';
149 l_where_clause := ' AND edismv.marker = 3 ';
150
151 ELSIF (l_item_org IS NULL AND l_category IS NOT NULL) THEN
152
153 l_from_clause := ' eni_dbi_inv_sum_mv edismv , eni_denorm_hierarchies edh ';
154 l_where_clause := ' AND edismv.marker = 3 ' ||
155 ' AND edh.parent_id = &' || 'ITEM+ENI_ITEM_VBH_CAT' ||
156 ' AND edismv.product_category_id = edh.child_id ';
157
158 ELSIF (l_item_org IS NOT NULL AND l_category IS NULL) THEN
159
160 l_from_clause := ' eni_dbi_inv_sum_mv edismv ';
161 l_where_clause := ' AND edismv.marker = 3 ' ||
162 ' AND edismv.item_org_id IN ( &' || 'ITEM+ENI_ITEM ) ';
163
164 ELSIF (l_item_org IS NOT NULL AND l_category IS NOT NULL) THEN
165 l_from_clause := ' eni_dbi_inv_sum_mv edismv, eni_denorm_hierarchies edh ';
166 l_where_clause := ' AND edismv.marker = 3 ' ||
167 ' AND edismv.item_org_id IN ( &' || 'ITEM+ENI_ITEM ) '||
168 ' AND edh.parent_id = &' || 'ITEM+ENI_ITEM_VBH_CAT' ||
169 ' AND edismv.product_category_id = edh.child_id ';
170 END IF;
171
172 ELSIF l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' THEN
173
174 -- VIEW BY IS PRODUCT CATEGORY
175 l_drill_to_cat_url := 'decode(leaf_node_flag, ''Y'', '||
176 '''pFunctionName=ENI_DBI_IVA_R' || '&' || 'VIEW_BY_NAME=VIEW_BY_ID' ||
177 '&' || 'VIEW_BY=ITEM+ENI_ITEM' || '&' || 'pParamIds=Y'',
178 ''pFunctionName=ENI_DBI_IVA_R' || '&' || 'VIEW_BY_NAME=VIEW_BY_ID' ||
179 '&' || 'VIEW_BY=ITEM+ENI_ITEM_VBH_CAT' || '&' || 'pParamIds=Y'') ';
180 l_lookup_select := 'lookupv.VALUE VIEWBY, lookupv.id VIEWBYID';
181 l_lookup := ', eni_item_vbh_nodes_v lookupv';
182 l_group_by_clause := ' product_category_id ';
183
184 l_lookup_inner_select := ' edismv.product_category_id ';
185 l_lookup_where := ' and b.product_category_id = lookupv.child_id ';
186 l_rank_measure := 'product_category_id';
187
188 IF (l_item_org IS NULL AND l_category IS NULL) THEN
189
190 l_from_clause := ' eni_dbi_inv_sum_mv edismv , eni_item_vbh_nodes_v lookupv ';
191 l_where_clause := ' AND edismv.marker = 2 '||
192 ' AND edismv.gid = 1 '||
193 ' AND lookupv.top_node_flag = ''Y'' ' ||
194 ' AND product_category_id = lookupv.parent_id ' ||
195 ' AND product_category_id = lookupv.child_id ';
196 l_lookup_where :=
197 ' AND lookupv.top_node_flag = ''Y'' ' ||
198 ' AND product_category_id = lookupv.parent_id ' ||
199 ' AND product_category_id = lookupv.child_id ';
200
201 ELSIF (l_item_org IS NULL AND l_category IS NOT NULL) THEN
202
203 l_from_clause := ' eni_dbi_inv_sum_mv edismv , eni_item_vbh_nodes_v lookupv ';
204 /*Bug 5843937*/
205 l_where_clause := ' AND edismv.organization_id IS NOT NULL AND edismv.marker = 2 '||
206 ' AND lookupv.parent_id = &' || 'ITEM+ENI_ITEM_VBH_CAT ' ||
207 ' AND lookupv.id = product_category_id ' ||
208 ' AND lookupv.id = lookupv.child_id '||
209 ' AND lookupv.parent_id <> lookupv.child_id ';
210 l_lookup_where :=
211 ' AND lookupv.child_id = product_category_id ' ||
212 ' AND lookupv.parent_id = lookupv.child_id ';
213
214 ELSIF (l_item_org IS NOT NULL AND l_category IS NULL) THEN
215
216 l_from_clause := ' eni_dbi_inv_sum_mv edismv, eni_denorm_hierarchies edh ';
217 l_where_clause := ' AND edismv.marker = 3 ' ||
218 ' AND edismv.item_org_id IN ( &' || 'ITEM+ENI_ITEM ) ' ||
219 ' AND edh.top_node_flag = ''Y'' ' ||
220 ' AND edismv.product_category_id = edh.child_id ';
221 l_lookup_inner_select := ' edh.parent_id product_category_id ';
222 l_group_by_clause := ' edh.parent_id ';
223
224 ELSIF (l_item_org IS NOT NULL AND l_category IS NOT NULL) THEN
225
226 l_from_clause:= ' eni_dbi_inv_sum_mv edismv, eni_denorm_hierarchies edh ';
227 l_where_clause := ' AND edismv.marker = 3 ' ||
228 ' AND edismv.item_org_id IN ( &' || 'ITEM+ENI_ITEM ) '||
229 ' AND edh.parent_id = &' || 'ITEM+ENI_ITEM_VBH_CAT' ||
230 ' AND edismv.product_category_id = edh.child_id ';
231 l_lookup_inner_select := ' edh.imm_child_id product_category_id ';
232 l_group_by_clause := 'edh.imm_child_id ';
233 l_lookup_where :=
234 ' AND lookupv.child_id = product_category_id ' ||
235 ' AND lookupv.parent_id = lookupv.child_id ';
236
237 END IF;
238
239 l_where_clause := l_where_clause; -- || l_where_clause1;
240
241 ELSIF l_view_by = 'ORGANIZATION+ORGANIZATION' THEN
242
243 -- VIEW BY IS ORGANIZATION
244 l_summary := 'odipmv';
245 l_lookup_select := 'lookupv.organization_name VIEWBY, lookupv.organization_id VIEWBYID ';
246 l_lookup := ', org_organization_definitions lookupv';
247 l_group_by_clause := ' odipmv.organization_id ';
248 l_drill_to_cat_url := 'NULL';
249 l_lookup_inner_select := ' odipmv.organization_id ';
250 l_lookup_where := ' and b.organization_id = lookupv.organization_id ';
251 l_rank_measure := 'organization_id';
252
253 IF (l_item_org IS NULL AND l_category IS NULL) THEN
254 l_from_clause := ' eni_dbi_inv_base_mv odipmv ';
255 l_where_clause := NULL;
256 ELSIF (l_item_org IS NULL AND l_category IS NOT NULL) THEN
257 l_from_clause := ' eni_dbi_inv_base_mv odipmv, eni_denorm_hierarchies edh ';
258 l_where_clause := ' AND edh.parent_id = &' || 'ITEM+ENI_ITEM_VBH_CAT' ||
259 ' AND odipmv.item_category_id = edh.child_id ';
260 ELSIF (l_item_org IS NOT NULL AND l_category IS NULL) THEN
261 l_from_clause := ' eni_dbi_inv_base_mv odipmv ';
262 l_where_clause := ' AND odipmv.item_master_org_id IN ( &' || 'ITEM+ENI_ITEM ) ';
263 ELSIF (l_item_org IS NOT NULL AND l_category IS NOT NULL) THEN
264 l_from_clause := ' eni_dbi_inv_base_mv odipmv , eni_denorm_hierarchies edh ';
265 l_where_clause := ' AND odipmv.item_master_org_id IN ( &' || 'ITEM+ENI_ITEM ) '||
266 ' AND edh.parent_id = &' || 'ITEM+ENI_ITEM_VBH_CAT' ||
267 ' AND odipmv.item_category_id = edh.child_id ';
268 END IF;
269 END IF;
270
271 /*
272 Bug : 3258092
273 Desc: Values computed for XTD instead of ITD. Values stored in base table are instaneous not summary
274
275 Bug: 3123997
276 Inv Total, InTransit Value and WIP Value mustbe N/A if the item doesn't exist
277 Issue: NVL(edismv.xxxx,0)
278 Fix : edismv.xxxx
279 removed the 'else' clause as well
280 */
281
282 IF l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' THEN
283
284 IF (UPPER(l_order_by) like '%ENI_MEASURE26%DESC%') THEN
285 l_order_by := ' ENI_MEASURE1 DESC';
286 ELSIF (UPPER(l_order_by) like '%ENI_MEASURE26%ASC%') THEN
287 l_order_by := ' ENI_MEASURE1 ASC';
288 END IF;
289
290 -- added LEADING hint to avoid full table access of ftrs
291 x_custom_sql :=
292 'SELECT /*+ LEADING(ftrs) */
293 ' || l_lookup_select || ',
294 ENI_MEASURE1,
295 ENI_MEASURE2,
296 ENI_MEASURE4,
297 ENI_MEASURE5,
298 ENI_MEASURE7,
299 ENI_MEASURE8,
300 ENI_MEASURE10,
301 ENI_MEASURE11,
302 SUM(ENI_MEASURE1) OVER() ENI_MEASURE14,
303 SUM(ENI_MEASURE2) OVER() ENI_MEASURE15,
304 SUM(ENI_MEASURE4) OVER() ENI_MEASURE17,
305 SUM(ENI_MEASURE5) OVER() ENI_MEASURE18,
306 SUM(ENI_MEASURE7) OVER() ENI_MEASURE20,
307 SUM(ENI_MEASURE8) OVER() ENI_MEASURE21,
308 SUM(ENI_MEASURE10) OVER() ENI_MEASURE23,
309 SUM(ENI_MEASURE11) OVER() ENI_MEASURE24,
310 (RATIO_TO_REPORT(ENI_MEASURE1) OVER())*100 AS ENI_MEASURE26,
311 100 AS ENI_MEASURE27,
312 ' || l_drill_to_cat_url || ' AS ENI_MEASURE28
313 FROM
314 (
315 SELECT a.*,
316 rank() over ( &'||'ORDER_BY_CLAUSE'||' nulls last,' || l_rank_measure ||')-1 as rank_num
317 FROM
318 (
319 SELECT
320 ' || l_lookup_inner_select || ',
321 SUM
322 (
323 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
324 THEN
325 '|| l_summary || '.inv_total_value_'||l_curr_suffix||'
326 END
327 ) AS ENI_MEASURE1,
328 SUM
329 (
330 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
331 THEN
332 '|| l_summary || '.inv_total_value_'||l_curr_suffix||'
333 END
334 ) AS ENI_MEASURE2,
335 SUM
336 (
337 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
338 THEN
339 '|| l_summary || '.onhand_value_'||l_curr_suffix||'
340 END
341 ) AS ENI_MEASURE4,
342 SUM
343 (
344 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
345 THEN
346 '|| l_summary || '.onhand_value_'||l_curr_suffix||'
347 END
348 ) AS ENI_MEASURE5,
349 SUM
350 (
351 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
352 THEN
353 '|| l_summary || '.intransit_value_'||l_curr_suffix||'
354 END
355 ) AS ENI_MEASURE7,
356 SUM
357 (
358 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
359 THEN
360 '|| l_summary || '.intransit_value_'||l_curr_suffix||'
361 END
362 ) AS ENI_MEASURE8,
363 SUM
364 (
365 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
366 THEN
367 '|| l_summary || '.wip_value_'||l_curr_suffix||'
368 END
369 ) AS ENI_MEASURE10,
370 SUM
371 (
372 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
373 THEN
374 '|| l_summary || '.wip_value_'||l_curr_suffix||'
375 END
376 ) AS ENI_MEASURE11
377 FROM
378 ' || l_from_clause || ', fii_time_rpt_struct ftrs
379 WHERE
380 ftrs.time_id = '|| l_summary || '.time_id
381 AND
382 (
383 ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
384 OR ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
385 )
386 AND BITAND(ftrs.record_type_id,1143) = ftrs.record_type_id
387 ' || l_where_clause || '
388 GROUP BY
389 ' || l_group_by_clause || '
390 ) a
391 )b ' || l_lookup || '
392 where ((b.rank_num between &'||'START_INDEX and &'||'END_INDEX) OR (&'||'END_INDEX = -1)) '||
393 l_lookup_where || '
394 order by rank_num';
395
396
397
398
399 ELSE -- windowing provided for Product and Organization viewby. Bug # 3781824
400
401 IF (UPPER(l_order_by) like '%ENI_MEASURE26%DESC%') THEN
402 l_order_by := ' ENI_MEASURE1 DESC';
403 ELSIF (UPPER(l_order_by) like '%ENI_MEASURE26%ASC%') THEN
404 l_order_by := ' ENI_MEASURE1 ASC';
405 END IF;
406
407 x_custom_sql :=
408 'SELECT
409 ' || l_lookup_select || ',
410 ENI_MEASURE1,
411 ENI_MEASURE2,
412 ENI_MEASURE4,
413 ENI_MEASURE5,
414 ENI_MEASURE7,
415 ENI_MEASURE8,
416 ENI_MEASURE10,
417 ENI_MEASURE11,
418 ENI_MEASURE14,
419 ENI_MEASURE15,
420 ENI_MEASURE17,
421 ENI_MEASURE18,
422 ENI_MEASURE20,
423 ENI_MEASURE21,
424 ENI_MEASURE23,
425 ENI_MEASURE24,
426 ENI_MEASURE26,
427 100 AS ENI_MEASURE27,
428 ' || l_drill_to_cat_url || ' AS ENI_MEASURE28
429 FROM
430 (
431 SELECT a.*,
432 (RATIO_TO_REPORT(ENI_MEASURE1) OVER())*100 AS ENI_MEASURE26,
433 rank() over ( &'||'ORDER_BY_CLAUSE'||' nulls last,' || l_rank_measure ||')-1 as rank_num
434 FROM
435 (
436 SELECT
437 ' || l_lookup_inner_select || ',
438 SUM
439 (
440 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
441 THEN
442 '|| l_summary || '.inv_total_value_'||l_curr_suffix||'
443 END
444 ) AS ENI_MEASURE1,
445 SUM
446 (
447 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
448 THEN
449 '|| l_summary || '.inv_total_value_'||l_curr_suffix||'
450 END
451 ) AS ENI_MEASURE2,
452 SUM
453 (
454 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
455 THEN
456 '|| l_summary || '.onhand_value_'||l_curr_suffix||'
457 END
458 ) AS ENI_MEASURE4,
459 SUM
460 (
461 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
462 THEN
463 '|| l_summary || '.onhand_value_'||l_curr_suffix||'
464 END
465 ) AS ENI_MEASURE5,
466 SUM
467 (
468 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
469 THEN
470 '|| l_summary || '.intransit_value_'||l_curr_suffix||'
471 END
472 ) AS ENI_MEASURE7,
473 SUM
474 (
475 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
476 THEN
477 '|| l_summary || '.intransit_value_'||l_curr_suffix||'
478 END
479 ) AS ENI_MEASURE8,
480 SUM
481 (
482 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
483 THEN
484 '|| l_summary || '.wip_value_'||l_curr_suffix||'
485 END
486 ) AS ENI_MEASURE10,
487 SUM
488 (
489 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
490 THEN
491 '|| l_summary || '.wip_value_'||l_curr_suffix||'
492 END
493 ) AS ENI_MEASURE11,
494 SUM
495 (
496 SUM
497 (
498 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
499 THEN
500 '|| l_summary || '.inv_total_value_'||l_curr_suffix||'
501 END
502 )
503 ) OVER() AS ENI_MEASURE14,
504 SUM
505 (
506 SUM
507 (
508 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
509 THEN
510 '|| l_summary || '.inv_total_value_'||l_curr_suffix||'
511 END
512 )
513 ) OVER() AS ENI_MEASURE15,
514 SUM
515 (
516 SUM
517 (
518 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
519 THEN
520 '|| l_summary || '.onhand_value_'||l_curr_suffix||'
521 END
522 )
523 ) OVER() AS ENI_MEASURE17,
524 SUM
525 (
526 SUM
527 (
528 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
529 THEN
530 '|| l_summary || '.onhand_value_'||l_curr_suffix||'
531 END
532 )
533 ) OVER() AS ENI_MEASURE18,
534 SUM
535 (
536 SUM
537 (
538 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
539 THEN
540 '|| l_summary || '.intransit_value_'||l_curr_suffix||'
541 END
542 )
543 ) OVER() AS ENI_MEASURE20,
544 SUM
545 (
546 SUM
547 (
548 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
549 THEN
550 '|| l_summary || '.intransit_value_'||l_curr_suffix||'
551 END
552 )
553 ) OVER() AS ENI_MEASURE21,
554 SUM
555 (
556 SUM
557 (
558 case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
559 THEN
560 '|| l_summary || '.wip_value_'||l_curr_suffix||'
561 END
562 )
563 ) OVER() AS ENI_MEASURE23,
564 SUM
565 (
566 SUM
567 (
568 case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
569 THEN
570 '|| l_summary || '.wip_value_'||l_curr_suffix||'
571 END
572 )
573 ) OVER() AS ENI_MEASURE24
574 FROM
575 ' || l_from_clause || ', fii_time_rpt_struct ftrs
576 WHERE
577 ftrs.time_id = '|| l_summary || '.time_id
578 AND
579 (
580 ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
581 OR ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
582 )
583 AND BITAND(ftrs.record_type_id,1143) = ftrs.record_type_id
584 ' || l_where_clause || '
585 GROUP BY
586 ' || l_group_by_clause || '
587 ) a
588 )b ' || l_lookup || '
589 where ((b.rank_num between &'||'START_INDEX and &'||'END_INDEX) OR (&'||'END_INDEX = -1)) '||
590 l_lookup_where || '
591 order by rank_num';
592
593 END IF;
594
595 EXCEPTION
596
597 WHEN OTHERS THEN
598 NULL;
599
600 END get_sql;
601
602 END eni_dbi_iva_pkg;