[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_RVA_PKG
Source
1 PACKAGE BODY ENI_DBI_RVA_PKG AS
2 /* $Header: ENIRVAPB.pls 120.1 2006/03/22 23:36:32 ajerome noship $ */
3
4 PROCEDURE GET_SQL( p_param IN BIS_PMV_PAGE_PARAMETER_TBL
5 , x_custom_sql OUT NOCOPY VARCHAR2
6 , x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL )
7 IS
8
9 l_stmt VARCHAR2(32000);
10 l_measures VARCHAR2(32000);
11 l_select_stmt VARCHAR2(32000);
12 l_inner_sql VARCHAR2(32000);
13 l_inner_select_stmt VARCHAR2(32000);
14 l_inner_group_by_stmt VARCHAR2(32000);
15 l_union_select_stmt VARCHAR2(32000);
16 l_union_group_by_stmt VARCHAR2(32000);
17 l_where_stmt VARCHAR2(32000);
18 l_mv1 VARCHAR2(100);
19 l_flags_where VARCHAR2(1000);
20 l_view_by VARCHAR2(32000);
21 l_org VARCHAR2(32000);
22 l_org_where VARCHAR2(32000);
23 l_prod VARCHAR2(32000);
24 l_prod_where VARCHAR2(32000);
25 l_cust_where VARCHAR2(32000);
26 l_cust VARCHAR2(32000);
27 l_prod_cat VARCHAR2(32000);
28 l_prod_cat_from VARCHAR2(32000);
29 l_prod_cat_where VARCHAR2(32000);
30 l_ret_reason VARCHAR2(32000);
31 l_ret_reason_where VARCHAR2(32000);
32
33 -- l_curr VARCHAR2(15) := 'NOT PASSED IN';
34 l_curr_suffix VARCHAR2(10);
35
36 l_lang VARCHAR2(10);
37 l_item_cat_flag NUMBER; -- 0 for product and 1 for product category
38 l_cust_flag NUMBER; -- 0 for customer and 1 for no customer selected
39 l_reason_flag NUMBER; -- 0 for reason and 1 for all reasons
40 l_custom_rec BIS_QUERY_ATTRIBUTES ;
41
42 l_all_prods BOOLEAN;
43 l_all_prod_cats BOOLEAN;
44 l_all_custs BOOLEAN;
45 l_all_reasons BOOLEAN;
46
47 l_vb_prod_cat BOOLEAN;
48 l_vb_prod BOOLEAN;
49 l_vb_cust BOOLEAN;
50 l_vb_org BOOLEAN;
51
52 l_open_url1 VARCHAR2(200);
53 l_open_url2 VARCHAR2(200);
54 l_open_urls VARCHAR2(500);
55
56 --Bug 5083648
57 l_inv_org_flag VARCHAR2(1);
58
59 BEGIN
60
61 l_lang := userenv('LANG');
62
63 FOR i IN 1..p_param.COUNT LOOP
64 CASE p_param(i).parameter_name
65 WHEN 'VIEW_BY' THEN l_view_by := p_param(i).parameter_value;
66 WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN l_prod_cat := p_param(i).parameter_value;
67 /* ENI Reports need MASTER ORG Items as a parameter */
68 WHEN 'ITEM+ENI_ITEM' THEN l_prod := p_param(i).parameter_value;
69 /* Commenting out as the below are not required in ENI Reports */
70 -- WHEN 'ORGANIZATION+ORGANIZATION' THEN l_org := p_param(i).parameter_value;
71 -- WHEN 'ITEM+ENI_ITEM_ORG' THEN l_item_org := p_param(i).parameter_value;
72 WHEN 'CUSTOMER+FII_CUSTOMERS' THEN l_cust := p_param(i).parameter_value;
73 WHEN 'ORDER_ITEM_RETURN_REASON+ORDER_ITEM_RETURN_REASON' THEN l_ret_reason := p_param(i).parameter_id;
74 WHEN 'CURRENCY+FII_CURRENCIES' THEN
75 l_curr_suffix :=
76 CASE p_param(i).parameter_id
77 WHEN eni_dbi_util_pkg.get_curr_prim THEN 'g' -- primary global currency
78 WHEN eni_dbi_util_pkg.get_curr_sec THEN 'g1' -- secondary global currency
79 ELSE 'f' -- functional currency
80 END;
81 ELSE null;
82 END CASE;
83 END LOOP;
84
85 l_all_prods := (l_prod IS NULL OR l_prod = '' OR l_prod = 'All');
86 l_all_prod_cats := (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All');
87 l_all_custs := (l_cust IS NULL OR l_cust = '' OR l_cust = 'All');
88 l_all_reasons := (l_ret_reason IS NULL OR l_ret_reason = '' OR l_ret_reason = 'All');
89
90 l_vb_prod_cat := (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' );
91 l_vb_prod := (l_view_by = 'ITEM+ENI_ITEM' );
92 l_vb_cust := (l_view_by = 'CUSTOMER+FII_CUSTOMERS' );
93 l_vb_org := (l_view_by = 'ORGANIZATION+ORGANIZATION');
94
95 IF l_all_prod_cats
96 THEN
97 IF ( l_vb_prod_cat OR
98 l_vb_org )
99 THEN
100 l_prod_cat_from := '
101 , ENI_DENORM_HIERARCHIES eni_cat
102 , MTL_DEFAULT_CATEGORY_SETS mdcs';
103 l_prod_cat_where := '
104 AND fact.item_category_id = eni_cat.child_id
105 AND eni_cat.top_node_flag = ''Y''
106 AND eni_cat.dbi_flag = ''Y''
107 AND eni_cat.object_type = ''CATEGORY_SET''
108 AND eni_cat.object_id = mdcs.category_set_id
109 AND mdcs.functional_area_id = 11';
110 ELSE
111 l_prod_cat_from := '';
112 l_prod_cat_where := '';
113 END IF;
114 ELSE
115 l_prod_cat_from := '
116 , ENI_DENORM_HIERARCHIES eni_cat
117 , MTL_DEFAULT_CATEGORY_SETS mdcs';
118 l_prod_cat_where := '
119 AND fact.item_category_id = eni_cat.child_id
120 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
121 AND eni_cat.dbi_flag = ''Y''
122 AND eni_cat.object_type = ''CATEGORY_SET''
123 AND eni_cat.object_id = mdcs.category_set_id
124 AND mdcs.functional_area_id = 11';
125 END IF;
126
127 -- ITEM AND ITEM CATEGORY
128 IF l_all_prods THEN
129 l_prod_where := '';
130
131 l_item_cat_flag := CASE -- order matters
132 WHEN l_vb_prod THEN 4 -- rollup on master item
133 WHEN l_vb_prod_cat THEN 1 -- rollup on category
134 WHEN l_all_prod_cats THEN 3 -- all product categories
135 ELSE 1
136 END;
137 ELSE
138 l_prod_where := '
139 AND fact.master_item_id IN (&ITEM+ENI_ITEM)';
140
141 IF l_vb_prod THEN
142 l_item_cat_flag := 4;
143 ELSE
144 l_item_cat_flag := 0;
145 END IF;
146 END IF;
147
148 -- CUSTOMER
149 IF l_all_custs THEN
150 l_cust_where := '';
151 l_cust_flag := CASE
152 WHEN l_vb_cust THEN 0 -- customers selected
153 ELSE 1 -- all customers & not viewed by customer
154 END;
155 ELSE
156 l_cust_where := '
157 AND fact.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
158 l_cust_flag := 0; -- customer selected
159 END IF;
160
161 -- REASON
162 IF l_all_reasons THEN
163 l_ret_reason_where := '';
164 l_reason_flag := 1;
165 ELSE
166 l_ret_reason_where := '
167 AND fact.return_reason IN (&ORDER_ITEM_RETURN_REASON+ORDER_ITEM_RETURN_REASON)';
168 l_reason_flag := 0;
169 END IF;
170
171 l_measures :=
172 ', ENI_MEASURE1,ENI_MEASURE2,ENI_MEASURE3,ENI_MEASURE4,ENI_MEASURE5
173 , ENI_MEASURE6,ENI_MEASURE7,ENI_MEASURE8,ENI_MEASURE9,ENI_MEASURE10 ';
174 -- Commenting out measures not required in ENI Reports
175 -- , ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13';
176 l_open_url1 :=
177 '''pFunctionName=ENI_DBI_RVR_R&VIEW_BY=ORDER_ITEM_RETURN_REASON+ORDER_ITEM_RETURN_REASON&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''';
178 l_open_url2 :=
179 '''pFunctionName=ENI_DBI_RVD_R&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''';
180 l_open_urls := '
181 , DECODE(ENI_MEASURE1,0,NULL,
182 '||l_open_url1||') ENI_ATTRIBUTE5
183 , DECODE(ENI_MEASURE5,0,NULL,
184 '||l_open_url2||') ENI_ATTRIBUTE6';
185
186
187 /* This portion of the code sets up spaghetti pieces for particular viewbys */
188 CASE
189 WHEN l_vb_org THEN -- +=================== ORGANIZATION ========================+
190 l_select_stmt := '
191 SELECT org.name VIEWBY
192 , org.organization_id VIEWBYID
193 , NULL ENI_ATTRIBUTE3 -- drill across url
194 , NULL ENI_ATTRIBUTE4 -- item description
195 ' || l_measures || '
196 , NULL ENI_ATTRIBUTE5
197 , NULL ENI_ATTRIBUTE6
198 FROM
199 (SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, inv_org_id)) - 1 rnk
200 , inv_org_id '
201 || l_measures || '
202 FROM
203 (SELECT c.inv_org_id, ';
204
205 l_inner_select_stmt := '
206 SELECT fact.inv_org_id INV_ORG_ID';
207 l_union_select_stmt := '
208 SELECT inv_org_id INV_ORG_ID';
209 l_inner_group_by_stmt := '
210 GROUP BY fact.inv_org_id';
211 l_union_group_by_stmt := '
212 GROUP BY inv_org_id';
213 l_where_stmt := '
214 HR_ALL_ORGANIZATION_UNITS_TL org
215 WHERE a.inv_org_id = org.organization_id
216 AND org.language = :ENI_LANG
217 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
218 &ORDER_BY_CLAUSE NULLS LAST';
219
220 WHEN l_vb_prod THEN -- +====================== PRODUCT ===========================+
221 l_select_stmt := '
222 SELECT items.value VIEWBY
223 , items.id VIEWBYID
224 , NULL ENI_ATTRIBUTE3 -- drill across url
225 , items.description ENI_ATTRIBUTE4 -- item description
226 '||l_measures||l_open_urls||'
227 FROM
228 (SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, item_id)) - 1 rnk
229 , item_id
230 '||l_measures||'
231 FROM
232 (SELECT c.item_id, ';
233 -- c.uom, ';
234
235 l_inner_select_stmt := '
236 SELECT fact.master_item_id ITEM_ID';
237 -- , fact.uom UOM';
238
239 l_union_select_stmt := '
240 SELECT item_id ITEM_ID';
241 -- uom UOM,';
242
243 l_inner_group_by_stmt := '
244 GROUP BY fact.master_item_id';
245 --, fact.uom';
246
247 l_union_group_by_stmt := '
248 GROUP BY item_id';
249 --, uom';
250
251 l_where_stmt := '
252 ENI_ITEM_V items
253 WHERE a.item_id = items.id
254 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
255 &ORDER_BY_CLAUSE NULLS LAST';
256
257 WHEN l_vb_cust THEN -- +===================== CUSTOMER ==========================+
258 l_select_stmt := '
259 SELECT cust.value VIEWBY
260 , cust.id VIEWBYID
261 , NULL ENI_ATTRIBUTE3 -- drill across url
262 , NULL ENI_ATTRIBUTE4 -- item description
263 '||l_measures||l_open_urls||'
264 FROM
265 (SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, customer_id)) - 1 rnk
266 , customer_id
267 '||l_measures||'
268 FROM
269 (SELECT c.customer_id, ';
270 l_inner_select_stmt := '
271 SELECT fact.customer_id CUSTOMER_ID';
272 l_union_select_stmt := '
273 SELECT customer_id CUSTOMER_ID';
274 l_inner_group_by_stmt := '
275 GROUP BY fact.customer_id';
276 l_union_group_by_stmt := '
277 GROUP BY customer_id';
278 l_where_stmt := '
279 FII_CUSTOMERS_V cust
280 WHERE a.customer_id = cust.id
281 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
282 &ORDER_BY_CLAUSE NULLS LAST';
283
284 WHEN l_vb_prod_cat THEN -- +================== PRODUCT CAT ==========================+
285 l_select_stmt := '
286 SELECT eni_vbh.value VIEWBY
287 , eni_vbh.id VIEWBYID
288 , DECODE(eni_vbh.leaf_node_flag, ''Y'',
289 ''pFunctionName=ENI_DBI_RVA_R&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM&pParamIds=Y'',
290 ''pFunctionName=ENI_DBI_RVA_R&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'')
291 ENI_ATTRIBUTE3 -- drill across url
292 , NULL ENI_ATTRIBUTE4 -- item description
293 '||l_measures||l_open_urls||'
294 FROM
295 (SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, item_category_id)) - 1 rnk,
296 item_category_id
297 '||l_measures||'
298 FROM
299 (SELECT c.item_category_id, ';
300 IF l_all_prod_cats
301 THEN
302 l_inner_select_stmt := '
303 SELECT eni_cat.parent_id ITEM_CATEGORY_ID';
304 l_inner_group_by_stmt := '
305 GROUP BY eni_cat.parent_id';
306 ELSE
307 l_inner_select_stmt := '
308 SELECT eni_cat.imm_child_id ITEM_CATEGORY_ID';
309 l_inner_group_by_stmt := '
310 GROUP BY eni_cat.imm_child_id';
311 END IF;
312 l_union_select_stmt := '
313 SELECT item_category_id ITEM_CATEGORY_ID';
314 l_union_group_by_stmt := '
315 GROUP BY item_category_id';
316 l_where_stmt := '
317 ENI_ITEM_VBH_NODES_V eni_vbh
318 WHERE a.item_category_id = eni_vbh.id
319 AND a.item_category_id = eni_vbh.parent_id
320 AND a.item_category_id = eni_vbh.child_id
321 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
322 &ORDER_BY_CLAUSE NULLS LAST';
323 END CASE;
324
325 IF( l_all_prods
326 AND l_all_custs
327 AND l_all_reasons
328 AND ( l_vb_prod_cat
329 OR l_vb_org )
330 )
331 THEN
332 l_mv1 := 'ISC_DBI_CFM_011_MV';
333 l_flags_where := '
334 ';
335
336 l_prod_cat_from := '';
337 IF l_vb_prod_cat THEN
338 l_inner_select_stmt := '
339 SELECT fact.parent_id ITEM_CATEGORY_ID';
340 l_inner_group_by_stmt := '
341 GROUP BY fact.parent_id';
342 ELSE
343 l_inner_select_stmt := '
344 SELECT fact.inv_org_id INV_ORG_ID';
345 l_inner_group_by_stmt := '
346 GROUP BY fact.inv_org_id';
347 END IF;
348
349 IF l_all_prod_cats THEN
350 -- inv_org_flag == 1 ==> look at the rows that rollup() over inv_org_id;
351 -- i.e. sum over all orgs
352 -- inv_org_flag == 0 ==> look at the non-rollup rows
353
354 --Bug 5083648 : Replaced Literal with Bind Parameter
355 /*
356 l_prod_cat_where := '
357 AND fact.top_node_flag = ''Y''
358 AND fact.inv_org_flag = '|| CASE
359 WHEN l_vb_prod_cat THEN '1'
360 WHEN l_vb_org THEN '0'
361 END;
362 */
363 l_prod_cat_where := '
364 AND fact.top_node_flag = ''Y''
365 AND fact.inv_org_flag = :INV_ORG_FLAG';
366
367 IF l_vb_prod_cat
368 THEN l_inv_org_flag := '1';
369 ELSE
370 IF l_vb_org
371 THEN l_inv_org_flag := '0';
372 END IF;
373 END IF;
374
375 ELSE -- prod cat has been specified
376 IF l_vb_prod_cat THEN
377 l_prod_cat_from := '
378 , ENI_DENORM_HIERARCHIES eni_cat
379 , MTL_DEFAULT_CATEGORY_SETS mdcs';
380 -- inv_org_flag == 1 ==> look at the rows that rollup() over inv_org_id;
381 -- i.e. sum over all orgs
382 l_prod_cat_where := '
383 AND fact.inv_org_flag = 1
384 AND fact.parent_id = eni_cat.child_id
385 AND eni_cat.dbi_flag = ''Y''
386 AND eni_cat.object_type = ''CATEGORY_SET''
387 AND eni_cat.object_id = mdcs.category_set_id
388 AND mdcs.functional_area_id = 11
389 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
390 AND ( (eni_cat.leaf_node_flag = ''Y''
391 AND eni_cat.parent_id = eni_cat.child_id)
392 OR (eni_cat.imm_child_id = eni_cat.child_id
393 AND eni_cat.parent_id <> child_id) )';
394 ELSIF l_vb_org THEN
395 -- inv_org_flag == 0 ==> look at the non-rollup rows
396 l_prod_cat_where := '
397 AND fact.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
398 AND fact.inv_org_flag = 0';
399 END IF;
400 END IF;
401 ELSE
402 l_mv1 := 'ISC_DBI_CFM_002_MV';
403 IF( NOT l_vb_prod_cat AND
404 l_all_prod_cats )
405 THEN
406 l_prod_cat_from := '';
407 l_prod_cat_where := '';
408 END IF;
409
410 l_flags_where := '
411 AND fact.item_cat_flag = :ENI_ITEM_CAT_FLAG
412 AND fact.customer_flag = :ENI_CUST_FLAG';
413 END IF;
414
415 IF l_reason_flag = 0 -- use of ISC_DBI_CFM_007_MV (return reason)
416 THEN l_inner_sql := l_union_select_stmt||'
417 , sum(curr_return) CURR_RETURN
418 , sum(prev_return) PREV_RETURN
419 , sum(curr_ship) CURR_SHIP
420 , sum(prev_ship) PREV_SHIP
421 , sum(lines_cnt) LINES_CNT
422 , sum(return_qty) RETURN_QTY
423 FROM ('||l_inner_select_stmt||'
424 , sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
425 fact.returned_amt_'||l_curr_suffix||', 0)) CURR_RETURN
426 , sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
427 fact.returned_amt_'||l_curr_suffix||', 0)) PREV_RETURN
428 , 0 CURR_SHIP
429 , 0 PREV_SHIP
430 , sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
431 fact.lines_cnt, 0)) LINES_CNT
432 , sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
433 fact.returned_qty, 0)) RETURN_QTY
434 FROM ISC_DBI_CFM_007_MV fact
435 , FII_TIME_RPT_STRUCT cal'||l_prod_cat_from||'
436 WHERE fact.time_id = cal.time_id
437 AND fact.period_type_id = cal.period_type_id
438 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id
439 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
440 AND fact.customer_flag = :ENI_CUST_FLAG
441 AND fact.item_cat_flag = :ENI_ITEM_CAT_FLAG
442 AND fact.return_reason_flag = :ENI_REASON_FLAG'
443 --||l_org_where
444 ||l_prod_cat_where
445 ||l_prod_where
446 ||l_cust_where
447 ||l_ret_reason_where
448 ||l_inner_group_by_stmt||'
449 UNION ALL
450 '||l_inner_select_stmt||'
451 , 0 CURR_RETURN
452 , 0 PREV_RETURN
453 , sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
454 fact.fulfilled_amt2_'||l_curr_suffix||', 0)) CURR_SHIP
455 , sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
456 fact.fulfilled_amt2_'||l_curr_suffix||', 0)) PREV_SHIP
457 , 0 LINES_CNT
458 , 0 RETURN_QTY
459 FROM ISC_DBI_CFM_002_MV fact
460 , FII_TIME_RPT_STRUCT cal'||l_prod_cat_from||'
461 WHERE fact.time_id = cal.time_id
462 AND fact.return_flag = 0
463 AND fact.period_type_id = cal.period_type_id
464 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id
465 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
466 AND fact.customer_flag = :ENI_CUST_FLAG
467 AND fact.item_cat_flag = :ENI_ITEM_CAT_FLAG'
468 -- ||l_org_where
469 ||l_prod_cat_where
470 ||l_prod_where
471 ||l_cust_where
472 ||l_inner_group_by_stmt||')'
473 ||l_union_group_by_stmt;
474
475 ELSE
476 l_inner_sql := l_inner_select_stmt||'
477 , sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
478 decode(fact.return_flag, 1,
479 fact.returned_amt_'||l_curr_suffix||', 0), 0)) CURR_RETURN
480 , sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
481 decode(fact.return_flag, 1,
482 fact.returned_amt_'||l_curr_suffix||', 0), 0)) PREV_RETURN
483 , sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
484 decode(fact.return_flag, 0,
485 fact.fulfilled_amt2_'||l_curr_suffix||', 0), 0)) CURR_SHIP
486 , sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
487 decode(fact.return_flag, 0,
488 fact.fulfilled_amt2_'||l_curr_suffix||', 0), 0)) PREV_SHIP
489 , sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
490 decode(fact.return_flag, 1,
491 fact.lines_cnt, 0), 0)) LINES_CNT
492 , sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
493 decode(fact.return_flag, 1,
494 fact.returned_qty, 0), 0)) RETURN_QTY
495 FROM '||l_mv1||' fact
496 , FII_TIME_RPT_STRUCT cal'||l_prod_cat_from||'
497 WHERE fact.time_id = cal.time_id
498 AND fact.period_type_id = cal.period_type_id
499 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id
500 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)'
501 ||l_flags_where
502 -- ||l_org_where
503 ||l_prod_cat_where
504 ||l_prod_where
505 ||l_cust_where
506 ||l_inner_group_by_stmt;
507 END IF;
508 /* End spaghetti pieces */
509
510 l_stmt := l_select_stmt||'
511 c.curr_return ENI_MEASURE1 -- return value
512 , (c.curr_return - c.prev_return)
513 / decode(c.prev_return, 0, NULL,
514 abs(c.prev_return)) * 100 ENI_MEASURE2 -- return value change
515 , c.curr_return
516 / decode(c.curr_ship, 0, NULL,
517 c.curr_ship) * 100 ENI_MEASURE3 -- return rate
518 , c.curr_return
519 / decode(c.curr_ship, 0, NULL,
520 c.curr_ship) * 100 -
521 c.prev_return
522 / decode(c.prev_ship, 0, NULL,
523 c.prev_ship) * 100 ENI_MEASURE4 -- return rate change
524 , c.lines_cnt ENI_MEASURE5 -- past due lines
525 , sum(c.curr_return) over () ENI_MEASURE6 -- gd total return value
526 , (sum(c.curr_return) over () - sum(c.prev_return) over ())
527 / decode(sum(c.prev_return) over (), 0, NULL,
528 abs(sum(c.prev_return) over ())) * 100 ENI_MEASURE7 -- gd total return change
529 , sum(c.curr_return) over ()
530 / decode(sum(c.curr_ship) over (), 0, NULL,
531 sum(c.curr_ship) over ()) * 100 ENI_MEASURE8 -- gd total return rate
532 , sum(c.curr_return) over ()
533 / decode(sum(c.curr_ship) over (), 0, NULL,
534 sum(c.curr_ship) over ()) * 100 -
535 sum(c.prev_return) over()
536 / decode(sum(c.prev_ship) over (), 0, NULL,
537 sum(c.prev_ship) over ()) * 100 ENI_MEASURE9 -- gd total return rate change
538 , sum(c.lines_cnt) over () ENI_MEASURE10 -- gd return lines
539 FROM ('||l_inner_sql||') c)
540 WHERE ENI_MEASURE1 <> 0
541 OR ENI_MEASURE2 IS NOT NULL
542 OR ENI_MEASURE3 IS NOT NULL
543 OR ENI_MEASURE4 IS NOT NULL
544 OR ENI_MEASURE5 <> 0) a,'
545 ||l_where_stmt;
546 -- || '-- CURR:' || l_curr;
547
548 x_custom_sql := l_stmt;
549
550 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
551 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
552
553 l_custom_rec.attribute_name := ':ENI_ITEM_CAT_FLAG';
554 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
555 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
556 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
557 x_custom_output.EXTEND;
558 x_custom_output(1) := l_custom_rec;
559
560 l_custom_rec.attribute_name := ':ENI_CUST_FLAG';
561 l_custom_rec.attribute_value := to_char(l_cust_flag);
562 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
563 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
564 x_custom_output.EXTEND;
565 x_custom_output(2) := l_custom_rec;
566
567 l_custom_rec.attribute_name := ':ENI_REASON_FLAG';
568 l_custom_rec.attribute_value := to_char(l_reason_flag);
569 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
570 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
571 x_custom_output.EXTEND;
572 x_custom_output(3) := l_custom_rec;
573
574 l_custom_rec.attribute_name := ':ENI_LANG';
575 l_custom_rec.attribute_value := l_lang;
576 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
577 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
578 x_custom_output.EXTEND;
579 x_custom_output(4) := l_custom_rec;
580
581 l_custom_rec.attribute_name := ':INV_ORG_FLAG';
582 l_custom_rec.attribute_value := l_inv_org_flag;
583 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
584 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
585 x_custom_output.EXTEND;
586 x_custom_output(5) := l_custom_rec;
587
588
589 END get_sql;
590
591 END ENI_DBI_RVA_PKG;
592