[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_select_stmt VARCHAR2(32000);
l_inner_select_stmt VARCHAR2(32000);
l_union_select_stmt VARCHAR2(32000);
l_cust_flag NUMBER; -- 0 for customer and 1 for no customer selected
(SELECT 1
FROM org_access o
WHERE o.responsibility_id = fnd_global.resp_id
AND o.resp_application_id = fnd_global.resp_appl_id
AND o.organization_id = fact.inv_org_id)
OR EXISTS
(SELECT 1
FROM mtl_parameters org
WHERE org.organization_id = fact.inv_org_id
AND NOT EXISTS
(SELECT 1
FROM org_access ora
WHERE org.organization_id = ora.organization_id)))';
THEN l_cust_flag := 0; -- customer selected
l_cust_flag := 0; -- customer selected
THEN l_select_stmt := '
SELECT org.name VIEWBY,
org.organization_id VIEWBYID,
NULL ISC_ATTRIBUTE_3, -- item description
NULL ISC_ATTRIBUTE_4, -- item uom
NULL ISC_ATTRIBUTE_6, -- drill across url
'||l_measures||'
FROM
(SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, inv_org_id)) - 1 rnk,
inv_org_id,
'||l_measures||'
FROM
(SELECT c.inv_org_id, ';
l_inner_select_stmt := '
SELECT fact.inv_org_id INV_ORG_ID,';
l_union_select_stmt := '
SELECT inv_org_id INV_ORG_ID,';
THEN l_select_stmt := '
SELECT items.value VIEWBY,
items.id VIEWBYID,
items.description ISC_ATTRIBUTE_3, -- item description
mtl.unit_of_measure ISC_ATTRIBUTE_4, -- item uom
NULL ISC_ATTRIBUTE_6, -- drill across url
'||l_measures||'
FROM
(SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, item_id)) - 1 rnk,
item_id,
uom,
'||l_measures||'
FROM
(SELECT c.item_id,
c.uom, ';
l_inner_select_stmt := '
SELECT fact.item_id ITEM_ID,
fact.uom UOM,';
l_union_select_stmt := '
SELECT item_id ITEM_ID,
uom UOM,';
THEN l_select_stmt := '
SELECT cust.value VIEWBY,
cust.id VIEWBYID,
NULL ISC_ATTRIBUTE_3, -- item description
NULL ISC_ATTRIBUTE_4, -- item uom
NULL ISC_ATTRIBUTE_6, -- drill across url
'||l_measures||'
FROM
(SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, customer_id)) - 1 rnk,
customer_id,
'||l_measures||'
FROM
(SELECT c.customer_id, ';
l_inner_select_stmt := '
SELECT fact.customer_id CUSTOMER_ID,';
l_union_select_stmt := '
SELECT customer_id CUSTOMER_ID,';
l_select_stmt := '
SELECT ecat.value VIEWBY,
ecat.id VIEWBYID,
NULL ISC_ATTRIBUTE_3, -- item description
NULL ISC_ATTRIBUTE_4, -- item uom
decode(ecat.leaf_node_flag, ''Y'',
''pFunctionName=ISC_DBI_RETURN_VALUE_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_ORG&pParamIds=Y'',
''pFunctionName=ISC_DBI_RETURN_VALUE_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'')
ISC_ATTRIBUTE_6, -- drill across url
'||l_measures||'
FROM
(SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, item_category_id)) - 1 rnk,
item_category_id,
'||l_measures||'
FROM
(SELECT c.item_category_id, ';
l_inner_select_stmt := '
SELECT eni_cat.parent_id ITEM_CATEGORY_ID,';
l_inner_select_stmt := '
SELECT eni_cat.imm_child_id ITEM_CATEGORY_ID,';
l_union_select_stmt := '
SELECT item_category_id ITEM_CATEGORY_ID,';
l_inner_select_stmt := '
SELECT fact.parent_id ITEM_CATEGORY_ID,';
l_inner_select_stmt := '
SELECT fact.imm_child_id ITEM_CATEGORY_ID,';
THEN l_inner_sql := l_union_select_stmt||'
sum(curr_return) CURR_RETURN,
sum(prev_return) PREV_RETURN,
sum(curr_ship) CURR_SHIP,
sum(prev_ship) PREV_SHIP,
sum(lines_cnt) LINES_CNT,
sum(return_qty) RETURN_QTY
FROM ('||l_inner_select_stmt||'
sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
fact.returned_amt_'||l_curr_suffix||', 0)) CURR_RETURN,
sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
fact.returned_amt_'||l_curr_suffix||', 0)) PREV_RETURN,
0 CURR_SHIP,
0 PREV_SHIP,
sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
fact.lines_cnt, 0)) LINES_CNT,
sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
fact.returned_qty, 0)) RETURN_QTY
FROM ISC_DBI_CFM_007_MV fact,
FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
WHERE fact.time_id = cal.time_id
AND fact.period_type_id = cal.period_type_id
AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id
AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
AND fact.customer_flag = :ISC_CUST_FLAG
AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
AND fact.return_reason_flag = :ISC_REASON_FLAG'
||l_org_where
||l_prod_cat_where
||l_prod_where
||l_cust_where
||l_ret_reason_where
||l_inner_group_by_stmt||'
UNION ALL
'||l_inner_select_stmt||'
0 CURR_RETURN,
0 PREV_RETURN,
sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
fact.fulfilled_amt2_'||l_curr_suffix||', 0)) CURR_SHIP,
sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
fact.fulfilled_amt2_'||l_curr_suffix||', 0)) PREV_SHIP,
0 LINES_CNT,
0 RETURN_QTY
FROM ISC_DBI_CFM_002_MV fact,
FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
WHERE fact.time_id = cal.time_id
AND fact.return_flag = 0
AND fact.period_type_id = cal.period_type_id
AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id
AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
AND fact.customer_flag = :ISC_CUST_FLAG
AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG'
||l_org_where
||l_prod_cat_where
||l_prod_where
||l_cust_where
||l_inner_group_by_stmt||')'
||l_union_group_by_stmt;
l_inner_sql := l_inner_select_stmt||'
sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
decode(fact.return_flag, 1,
fact.returned_amt_'||l_curr_suffix||', 0), 0)) CURR_RETURN,
sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
decode(fact.return_flag, 1,
fact.returned_amt_'||l_curr_suffix||', 0), 0)) PREV_RETURN,
sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
decode(fact.return_flag, 0,
fact.fulfilled_amt2_'||l_curr_suffix||', 0), 0)) CURR_SHIP,
sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
decode(fact.return_flag, 0,
fact.fulfilled_amt2_'||l_curr_suffix||', 0), 0)) PREV_SHIP,
sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
decode(fact.return_flag, 1,
fact.lines_cnt, 0), 0)) LINES_CNT,
sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
decode(fact.return_flag, 1,
fact.returned_qty, 0), 0)) RETURN_QTY
FROM '||l_mv1||' fact,
FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
WHERE fact.time_id = cal.time_id
AND fact.period_type_id = cal.period_type_id
AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id
AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)'
||l_flags_where
||l_org_where
||l_prod_cat_where
||l_prod_where
||l_cust_where
||l_inner_group_by_stmt;
l_stmt := l_select_stmt||'
c.return_qty ISC_MEASURE_1, -- return qty
c.curr_return ISC_MEASURE_2, -- return value
(c.curr_return - c.prev_return)
/ decode(c.prev_return, 0, NULL,
abs(c.prev_return)) * 100 ISC_MEASURE_3, -- return value change
c.curr_return
/ decode(c.curr_ship, 0, NULL,
c.curr_ship) * 100 ISC_MEASURE_4, -- return rate
c.curr_return
/ decode(c.curr_ship, 0, NULL,
c.curr_ship) * 100 -
c.prev_return
/ decode(c.prev_ship, 0, NULL,
c.prev_ship) * 100 ISC_MEASURE_5, -- return rate change
c.lines_cnt ISC_MEASURE_6, -- past due lines
sum(c.curr_return) over () ISC_MEASURE_7, -- gd total return value
(sum(c.curr_return) over () - sum(c.prev_return) over ())
/ decode(sum(c.prev_return) over (), 0, NULL,
abs(sum(c.prev_return) over ())) * 100 ISC_MEASURE_8, -- gd total return change
sum(c.curr_return) over ()
/ decode(sum(c.curr_ship) over (), 0, NULL,
sum(c.curr_ship) over ()) * 100 ISC_MEASURE_9, -- gd total return rate
sum(c.curr_return) over ()
/ decode(sum(c.curr_ship) over (), 0, NULL,
sum(c.curr_ship) over ()) * 100 -
sum(c.prev_return) over()
/ decode(sum(c.prev_ship) over (), 0, NULL,
sum(c.prev_ship) over ()) * 100 ISC_MEASURE_10, -- gd total return rate change
c.curr_return ISC_MEASURE_11, -- KPI return value
c.prev_return ISC_MEASURE_12, -- KPI return value - prior
sum(c.lines_cnt) over () ISC_MEASURE_13, -- gd total past due lines
sum(c.curr_return) over () ISC_MEASURE_14, -- gd total KPI return value
sum(c.prev_return) over () ISC_MEASURE_15 -- gd total KPI return value - prior
FROM ('||l_inner_sql||') c)
WHERE ISC_MEASURE_2 <> 0
OR ISC_MEASURE_3 IS NOT NULL
OR ISC_MEASURE_4 IS NOT NULL
OR ISC_MEASURE_5 IS NOT NULL
OR ISC_MEASURE_6 <> 0) a,'
||l_where_stmt;