[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_RETURN_VALUE_PKG
Source
1 PACKAGE BODY ISC_DBI_RETURN_VALUE_PKG AS
2 /* $Header: ISCRGABB.pls 120.1 2006/06/26 06:57:50 abhdixi 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) IS
7
8 l_stmt VARCHAR2(32000);
9 l_measures VARCHAR2(32000);
10 l_select_stmt VARCHAR2(32000);
11 l_inner_sql VARCHAR2(32000);
12 l_inner_select_stmt VARCHAR2(32000);
13 l_inner_group_by_stmt VARCHAR2(32000);
14 l_union_select_stmt VARCHAR2(32000);
15 l_union_group_by_stmt VARCHAR2(32000);
16 l_where_stmt VARCHAR2(32000);
17 l_mv1 VARCHAR2(100);
18 l_flags_where VARCHAR2(1000);
19 l_view_by VARCHAR2(32000);
20 l_org VARCHAR2(32000);
21 l_org_where VARCHAR2(32000);
22 l_prod VARCHAR2(32000);
23 l_prod_where VARCHAR2(32000);
24 l_cust_where VARCHAR2(32000);
25 l_cust VARCHAR2(32000);
26 l_prod_cat VARCHAR2(32000);
27 l_prod_cat_from VARCHAR2(32000);
28 l_prod_cat_where VARCHAR2(32000);
29 l_ret_reason VARCHAR2(32000);
30 l_ret_reason_where VARCHAR2(32000);
31 l_curr VARCHAR2(50);
32 l_curr_suffix VARCHAR2(10);
33 l_lang VARCHAR2(10);
34 l_item_cat_flag NUMBER; -- 0 for product and 1 for product category
35 l_cust_flag NUMBER; -- 0 for customer and 1 for no customer selected
36 l_reason_flag NUMBER; -- 0 for reason and 1 for all reasons
37 l_custom_rec BIS_QUERY_ATTRIBUTES ;
38
39 BEGIN
40
41 l_lang := userenv('LANG');
42
43 FOR i IN 1..p_param.COUNT
44 LOOP
45 IF( p_param(i).parameter_name= 'VIEW_BY')
46 THEN l_view_by := p_param(i).parameter_value;
47 END IF;
48
49 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
50 THEN l_org := p_param(i).parameter_value;
51 END IF;
52
53 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT')
54 THEN l_prod_cat := p_param(i).parameter_value;
55 END IF;
56
57 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
58 THEN l_prod := p_param(i).parameter_value;
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 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
66 THEN l_cust := p_param(i).parameter_value;
67 END IF;
68
69 IF(p_param(i).parameter_name = 'ORDER_ITEM_RETURN_REASON+ORDER_ITEM_RETURN_REASON')
70 THEN l_ret_reason := p_param(i).parameter_id;
71 END IF;
72 END LOOP;
73
74 IF(l_curr = '''FII_GLOBAL1''')
75 THEN l_curr_suffix := 'g';
76 ELSIF (l_curr = '''FII_GLOBAL2''')
77 THEN l_curr_suffix := 'g1';
78 ELSE l_curr_suffix := 'f';
79 END IF;
80
81 IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All')
82 THEN
83 IF (l_view_by = 'ITEM+ENI_ITEM_ORG')
84 THEN l_item_cat_flag := 0; -- product
85 ELSIF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT')
86 THEN l_item_cat_flag := 1; -- category
87 ELSE
88 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
89 THEN l_item_cat_flag := 3; -- all
90 ELSE l_item_cat_flag := 1; -- category
91 END IF;
92 END IF;
93 ELSE
94 l_item_cat_flag := 0; -- product
95 END IF;
96
97 IF (l_org IS NULL OR l_org = '' OR l_org = 'All')
98 THEN l_org_where := '
99 AND (EXISTS
100 (SELECT 1
101 FROM org_access o
102 WHERE o.responsibility_id = fnd_global.resp_id
103 AND o.resp_application_id = fnd_global.resp_appl_id
104 AND o.organization_id = fact.inv_org_id)
105 OR EXISTS
106 (SELECT 1
107 FROM mtl_parameters org
108 WHERE org.organization_id = fact.inv_org_id
109 AND NOT EXISTS
110 (SELECT 1
111 FROM org_access ora
112 WHERE org.organization_id = ora.organization_id)))';
113 ELSE l_org_where := '
114 AND fact.inv_org_id = &ORGANIZATION+ORGANIZATION';
115 END IF;
116
117 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
118 THEN
119 IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT')
120 THEN
121 l_prod_cat_from := ',
122 ENI_DENORM_HIERARCHIES eni_cat,
123 MTL_DEFAULT_CATEGORY_SETS mdcs';
124 l_prod_cat_where := '
125 AND fact.item_category_id = eni_cat.child_id
126 AND eni_cat.top_node_flag = ''Y''
127 AND eni_cat.dbi_flag = ''Y''
128 AND eni_cat.object_type = ''CATEGORY_SET''
129 AND eni_cat.object_id = mdcs.category_set_id
130 AND mdcs.functional_area_id = 11';
131 ELSE
132 l_prod_cat_from := '';
133 l_prod_cat_where := '';
134 END IF;
135 ELSE
136 l_prod_cat_from := ',
137 ENI_DENORM_HIERARCHIES eni_cat,
138 MTL_DEFAULT_CATEGORY_SETS mdcs';
139 l_prod_cat_where := '
140 AND fact.item_category_id = eni_cat.child_id
141 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
142 AND eni_cat.dbi_flag = ''Y''
143 AND eni_cat.object_type = ''CATEGORY_SET''
144 AND eni_cat.object_id = mdcs.category_set_id
145 AND mdcs.functional_area_id = 11';
146 END IF;
147
148 IF ( l_prod IS NULL OR l_prod = '' OR l_prod = 'All' )
149 THEN l_prod_where := '';
150 ELSE l_prod_where := '
151 AND fact.item_id in (&ITEM+ENI_ITEM_ORG)';
152 END IF;
153
154 IF (l_cust IS NULL OR l_cust = '' OR l_cust = 'All')
155 THEN
156 l_cust_where:='';
157 IF(l_view_by = 'CUSTOMER+FII_CUSTOMERS')
158 THEN l_cust_flag := 0; -- customer selected
159 ELSE l_cust_flag := 1; -- all customers and not viewed by customer
160 END IF;
161 ELSE
162 l_cust_where :='
163 AND fact.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
164 l_cust_flag := 0; -- customer selected
165 END IF;
166
167 IF ( l_ret_reason IS NULL OR l_ret_reason = '' OR l_ret_reason = 'All' )
168 THEN l_ret_reason_where := '';
169 l_reason_flag := 1;
170 ELSE l_ret_reason_where := '
171 AND fact.return_reason IN (&ORDER_ITEM_RETURN_REASON+ORDER_ITEM_RETURN_REASON)';
172 l_reason_flag := 0;
173 END IF;
174
175 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
176 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
177
178 l_measures := 'ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,
179 ISC_MEASURE_6,ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_9,ISC_MEASURE_10,
180 ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_14,ISC_MEASURE_15';
181
182 IF l_view_by = 'ORGANIZATION+ORGANIZATION'
183 THEN l_select_stmt := '
184 SELECT org.name VIEWBY,
185 org.organization_id VIEWBYID,
186 NULL ISC_ATTRIBUTE_3, -- item description
187 NULL ISC_ATTRIBUTE_4, -- item uom
188 NULL ISC_ATTRIBUTE_6, -- drill across url
189 '||l_measures||'
190 FROM
191 (SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, inv_org_id)) - 1 rnk,
192 inv_org_id,
193 '||l_measures||'
194 FROM
195 (SELECT c.inv_org_id, ';
196 l_inner_select_stmt := '
197 SELECT fact.inv_org_id INV_ORG_ID,';
198 l_union_select_stmt := '
199 SELECT inv_org_id INV_ORG_ID,';
200 l_inner_group_by_stmt := '
201 GROUP BY fact.inv_org_id';
202 l_union_group_by_stmt := '
203 GROUP BY inv_org_id';
204 l_where_stmt := '
205 HR_ALL_ORGANIZATION_UNITS_TL org
206 WHERE a.inv_org_id = org.organization_id
207 AND org.language = :ISC_LANG
208 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
209 ORDER BY rnk';
210
211 ELSIF l_view_by = 'ITEM+ENI_ITEM_ORG'
212 THEN l_select_stmt := '
213 SELECT items.value VIEWBY,
214 items.id VIEWBYID,
215 items.description ISC_ATTRIBUTE_3, -- item description
216 mtl.unit_of_measure ISC_ATTRIBUTE_4, -- item uom
217 NULL ISC_ATTRIBUTE_6, -- drill across url
218 '||l_measures||'
219 FROM
220 (SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, item_id)) - 1 rnk,
221 item_id,
222 uom,
223 '||l_measures||'
224 FROM
225 (SELECT c.item_id,
226 c.uom, ';
227 l_inner_select_stmt := '
228 SELECT fact.item_id ITEM_ID,
229 fact.uom UOM,';
230 l_union_select_stmt := '
231 SELECT item_id ITEM_ID,
232 uom UOM,';
233 l_inner_group_by_stmt := '
234 GROUP BY fact.item_id, fact.uom';
235 l_union_group_by_stmt := '
236 GROUP BY item_id, uom';
237 l_where_stmt := '
238 ENI_ITEM_ORG_V items,
239 MTL_UNITS_OF_MEASURE_TL mtl
240 WHERE a.uom = mtl.uom_code
241 AND mtl.language = :ISC_LANG
242 AND a.item_id = items.id
243 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
244 ORDER BY rnk';
245
246 ELSIF l_view_by = 'CUSTOMER+FII_CUSTOMERS'
247 THEN l_select_stmt := '
248 SELECT cust.value VIEWBY,
249 cust.id VIEWBYID,
250 NULL ISC_ATTRIBUTE_3, -- item description
251 NULL ISC_ATTRIBUTE_4, -- item uom
252 NULL ISC_ATTRIBUTE_6, -- drill across url
253 '||l_measures||'
254 FROM
255 (SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, customer_id)) - 1 rnk,
256 customer_id,
257 '||l_measures||'
258 FROM
259 (SELECT c.customer_id, ';
260 l_inner_select_stmt := '
261 SELECT fact.customer_id CUSTOMER_ID,';
262 l_union_select_stmt := '
263 SELECT customer_id CUSTOMER_ID,';
264 l_inner_group_by_stmt := '
265 GROUP BY fact.customer_id';
266 l_union_group_by_stmt := '
267 GROUP BY customer_id';
268 l_where_stmt := '
269 FII_CUSTOMERS_V cust
270 WHERE a.customer_id = cust.id
271 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
272 ORDER BY rnk';
273
274 ELSE -- l_view_by = 'ITEM+ENI_ITEM_VBH_CAT'
275 l_select_stmt := '
276 SELECT ecat.value VIEWBY,
277 ecat.id VIEWBYID,
278 NULL ISC_ATTRIBUTE_3, -- item description
279 NULL ISC_ATTRIBUTE_4, -- item uom
280 decode(ecat.leaf_node_flag, ''Y'',
281 ''pFunctionName=ISC_DBI_RETURN_VALUE_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_ORG&pParamIds=Y'',
282 ''pFunctionName=ISC_DBI_RETURN_VALUE_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'')
283 ISC_ATTRIBUTE_6, -- drill across url
284 '||l_measures||'
285 FROM
286 (SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, item_category_id)) - 1 rnk,
287 item_category_id,
288 '||l_measures||'
289 FROM
290 (SELECT c.item_category_id, ';
291 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
292 THEN
293 l_inner_select_stmt := '
294 SELECT eni_cat.parent_id ITEM_CATEGORY_ID,';
295 l_inner_group_by_stmt := '
296 GROUP BY eni_cat.parent_id';
297 ELSE
298 l_inner_select_stmt := '
299 SELECT eni_cat.imm_child_id ITEM_CATEGORY_ID,';
300 l_inner_group_by_stmt := '
301 GROUP BY eni_cat.imm_child_id';
302 END IF;
303 l_union_select_stmt := '
304 SELECT item_category_id ITEM_CATEGORY_ID,';
305 l_union_group_by_stmt := '
306 GROUP BY item_category_id';
307 l_where_stmt := '
308 ENI_ITEM_VBH_NODES_V ecat
309 WHERE a.item_category_id = ecat.id
310 AND ecat.parent_id = ecat.child_id
311 AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
312 ORDER BY rnk';
313 END IF;
314
315 IF ((l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' OR l_view_by = 'ORGANIZATION+ORGANIZATION') AND
316 (l_prod IS NULL OR l_prod = '' OR l_prod = 'All') AND
317 (l_cust IS NULL OR l_cust = '' OR l_cust = 'All') AND
318 (l_ret_reason IS NULL OR l_ret_reason = '' OR l_ret_reason = 'All'))
319 THEN
320 IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') THEN
321 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
322 THEN
323 l_inner_select_stmt := '
324 SELECT fact.parent_id ITEM_CATEGORY_ID,';
325 l_inner_group_by_stmt := '
326 GROUP BY fact.parent_id';
327 ELSE
328 l_inner_select_stmt := '
329 SELECT fact.imm_child_id ITEM_CATEGORY_ID,';
330 l_inner_group_by_stmt := '
331 GROUP BY fact.imm_child_id';
332 END IF;
333 END IF;
334
335 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
336 THEN
337 l_prod_cat_from := '';
338 l_prod_cat_where := '
339 AND fact.top_node_flag = ''Y''';
340 ELSE
341 l_prod_cat_from := '';
342 l_prod_cat_where := '
343 AND fact.parent_id = &ITEM+ENI_ITEM_VBH_CAT';
344 END IF;
345
346 l_mv1 := 'ISC_DBI_CFM_011_MV';
347 l_flags_where := '
348 AND fact.inv_org_flag = 0';
349 ELSE
350 l_mv1 := 'ISC_DBI_CFM_002_MV';
351 l_flags_where := '
352 AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
356 IF l_reason_flag = 0 -- use of ISC_DBI_CFM_007_MV (return reason)
353 AND fact.customer_flag = :ISC_CUST_FLAG';
354 END IF;
355
357 THEN l_inner_sql := l_union_select_stmt||'
358 sum(curr_return) CURR_RETURN,
359 sum(prev_return) PREV_RETURN,
360 sum(curr_ship) CURR_SHIP,
361 sum(prev_ship) PREV_SHIP,
362 sum(lines_cnt) LINES_CNT,
363 sum(return_qty) RETURN_QTY
364 FROM ('||l_inner_select_stmt||'
365 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
366 fact.returned_amt_'||l_curr_suffix||', 0)) CURR_RETURN,
367 sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
368 fact.returned_amt_'||l_curr_suffix||', 0)) PREV_RETURN,
369 0 CURR_SHIP,
370 0 PREV_SHIP,
371 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
372 fact.lines_cnt, 0)) LINES_CNT,
373 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
374 fact.returned_qty, 0)) RETURN_QTY
375 FROM ISC_DBI_CFM_007_MV fact,
376 FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
377 WHERE fact.time_id = cal.time_id
378 AND fact.period_type_id = cal.period_type_id
379 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id
380 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
381 AND fact.customer_flag = :ISC_CUST_FLAG
382 AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
383 AND fact.return_reason_flag = :ISC_REASON_FLAG'
384 ||l_org_where
385 ||l_prod_cat_where
386 ||l_prod_where
387 ||l_cust_where
388 ||l_ret_reason_where
389 ||l_inner_group_by_stmt||'
390 UNION ALL
391 '||l_inner_select_stmt||'
392 0 CURR_RETURN,
393 0 PREV_RETURN,
394 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
395 fact.fulfilled_amt2_'||l_curr_suffix||', 0)) CURR_SHIP,
396 sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
397 fact.fulfilled_amt2_'||l_curr_suffix||', 0)) PREV_SHIP,
398 0 LINES_CNT,
399 0 RETURN_QTY
400 FROM ISC_DBI_CFM_002_MV fact,
401 FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
402 WHERE fact.time_id = cal.time_id
403 AND fact.return_flag = 0
404 AND fact.period_type_id = cal.period_type_id
405 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id
406 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
407 AND fact.customer_flag = :ISC_CUST_FLAG
408 AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG'
409 ||l_org_where
410 ||l_prod_cat_where
411 ||l_prod_where
412 ||l_cust_where
413 ||l_inner_group_by_stmt||')'
414 ||l_union_group_by_stmt;
415
416 ELSE -- l_reason_flag = 1 -- use of ISC_DBI_CFM_002_MV (no return reason)
417 l_inner_sql := l_inner_select_stmt||'
418 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
419 decode(fact.return_flag, 1,
420 fact.returned_amt_'||l_curr_suffix||', 0), 0)) CURR_RETURN,
421 sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
422 decode(fact.return_flag, 1,
423 fact.returned_amt_'||l_curr_suffix||', 0), 0)) PREV_RETURN,
424 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
425 decode(fact.return_flag, 0,
426 fact.fulfilled_amt2_'||l_curr_suffix||', 0), 0)) CURR_SHIP,
427 sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
428 decode(fact.return_flag, 0,
429 fact.fulfilled_amt2_'||l_curr_suffix||', 0), 0)) PREV_SHIP,
430 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
431 decode(fact.return_flag, 1,
432 fact.lines_cnt, 0), 0)) LINES_CNT,
433 sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
434 decode(fact.return_flag, 1,
438 WHERE fact.time_id = cal.time_id
435 fact.returned_qty, 0), 0)) RETURN_QTY
436 FROM '||l_mv1||' fact,
437 FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
439 AND fact.period_type_id = cal.period_type_id
440 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id
441 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)'
442 ||l_flags_where
443 ||l_org_where
444 ||l_prod_cat_where
445 ||l_prod_where
446 ||l_cust_where
447 ||l_inner_group_by_stmt;
448 END IF;
449
450 l_stmt := l_select_stmt||'
451 c.return_qty ISC_MEASURE_1, -- return qty
452 c.curr_return ISC_MEASURE_2, -- return value
453 (c.curr_return - c.prev_return)
454 / decode(c.prev_return, 0, NULL,
455 abs(c.prev_return)) * 100 ISC_MEASURE_3, -- return value change
456 c.curr_return
457 / decode(c.curr_ship, 0, NULL,
458 c.curr_ship) * 100 ISC_MEASURE_4, -- return rate
459 c.curr_return
460 / decode(c.curr_ship, 0, NULL,
461 c.curr_ship) * 100 -
462 c.prev_return
463 / decode(c.prev_ship, 0, NULL,
464 c.prev_ship) * 100 ISC_MEASURE_5, -- return rate change
465 c.lines_cnt ISC_MEASURE_6, -- past due lines
466 sum(c.curr_return) over () ISC_MEASURE_7, -- gd total return value
467 (sum(c.curr_return) over () - sum(c.prev_return) over ())
468 / decode(sum(c.prev_return) over (), 0, NULL,
469 abs(sum(c.prev_return) over ())) * 100 ISC_MEASURE_8, -- gd total return change
470 sum(c.curr_return) over ()
471 / decode(sum(c.curr_ship) over (), 0, NULL,
472 sum(c.curr_ship) over ()) * 100 ISC_MEASURE_9, -- gd total return rate
473 sum(c.curr_return) over ()
474 / decode(sum(c.curr_ship) over (), 0, NULL,
475 sum(c.curr_ship) over ()) * 100 -
476 sum(c.prev_return) over()
477 / decode(sum(c.prev_ship) over (), 0, NULL,
478 sum(c.prev_ship) over ()) * 100 ISC_MEASURE_10, -- gd total return rate change
479 c.curr_return ISC_MEASURE_11, -- KPI return value
480 c.prev_return ISC_MEASURE_12, -- KPI return value - prior
481 sum(c.lines_cnt) over () ISC_MEASURE_13, -- gd total past due lines
482 sum(c.curr_return) over () ISC_MEASURE_14, -- gd total KPI return value
483 sum(c.prev_return) over () ISC_MEASURE_15 -- gd total KPI return value - prior
484 FROM ('||l_inner_sql||') c)
485 WHERE ISC_MEASURE_2 <> 0
486 OR ISC_MEASURE_3 IS NOT NULL
487 OR ISC_MEASURE_4 IS NOT NULL
488 OR ISC_MEASURE_5 IS NOT NULL
489 OR ISC_MEASURE_6 <> 0) a,'
490 ||l_where_stmt;
491
492 x_custom_sql := l_stmt;
493
494 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
495 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
496 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
497 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
498 x_custom_output.EXTEND;
499 x_custom_output(1) := l_custom_rec;
500
501 l_custom_rec.attribute_name := ':ISC_CUST_FLAG';
502 l_custom_rec.attribute_value := to_char(l_cust_flag);
503 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
504 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
505 x_custom_output.EXTEND;
506 x_custom_output(2) := l_custom_rec;
507
508 l_custom_rec.attribute_name := ':ISC_REASON_FLAG';
509 l_custom_rec.attribute_value := to_char(l_reason_flag);
510 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
511 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
512 x_custom_output.EXTEND;
513 x_custom_output(3) := l_custom_rec;
514
515 l_custom_rec.attribute_name := ':ISC_LANG';
516 l_custom_rec.attribute_value := l_lang;
517 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
518 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
519 x_custom_output.EXTEND;
520 x_custom_output(4) := l_custom_rec;
521
522 END get_sql;
523
524 END ISC_DBI_RETURN_VALUE_PKG ;
525