The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_select_stmt VARCHAR2(10000);
l_union_select_stmt VARCHAR2(10000);
l_inner_select_stmt VARCHAR2(10000);
(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 = inv_org)
OR EXISTS
(SELECT 1
FROM mtl_parameters org
WHERE org.organization_id = inv_org
AND NOT EXISTS
(SELECT 1
FROM org_access ora
WHERE org.organization_id = ora.organization_id)))';
THEN l_select_stmt := '
SELECT items.value VIEWBY,
NULL ISC_ATTRIBUTE_3, -- drill across url
items.description ISC_ATTRIBUTE_4, -- item description
'||l_measures||'
FROM
(SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, item_id)) - 1 rnk,
item_id,
'||l_measures||'
FROM
(SELECT c.item_id,';
l_inner_select_stmt := '
SELECT fact.item_id ITEM_ID,';
l_union_select_stmt := '
SELECT item_id ITEM_ID,';
THEN l_select_stmt := '
SELECT org.name VIEWBY,
NULL ISC_ATTRIBUTE_3, -- drill across url
NULL ISC_ATTRIBUTE_4, -- item description
'||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 cust.value VIEWBY,
NULL ISC_ATTRIBUTE_3, -- drill across url
NULL ISC_ATTRIBUTE_4, -- item description
'||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,
decode(ecat.leaf_node_flag, ''Y'',
''pFunctionName=ISC_DBI_FULF_PERF_T&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_ORG&pParamIds=Y'',
''pFunctionName=ISC_DBI_FULF_PERF_T&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'')
ISC_ATTRIBUTE_3, -- drill across url
NULL ISC_ATTRIBUTE_4, -- item description
'||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,';
l_stmt := l_select_stmt || '
c.curr_booked_value ISC_MEASURE_1, -- book
(c.curr_booked_value-c.prev_booked_value)
/ decode(c.prev_booked_value, 0, NULL,
abs(c.prev_booked_value)) * 100 ISC_MEASURE_2, -- book change
c.curr_fulfill_value ISC_MEASURE_3, -- fulf
(c.curr_fulfill_value-c.prev_fulfill_value)
/ decode(c.prev_fulfill_value, 0, NULL,
abs(c.prev_fulfill_value)) * 100 ISC_MEASURE_4, -- fulf change
c.curr_booked_value
/ decode(c.curr_fulfill_value, 0, NULL,
c.curr_fulfill_value) ISC_MEASURE_5, -- book to fulf r
c.curr_booked_value
/ decode(c.curr_fulfill_value, 0, NULL,
c.curr_fulfill_value) -
c.prev_booked_value
/ decode(c.prev_fulfill_value, 0, NULL,
c.prev_fulfill_value) ISC_MEASURE_6, -- book to fulf r change
sum(c.curr_booked_value) over () ISC_MEASURE_7, -- gd total book
(sum(c.curr_booked_value) over () - sum(c.prev_booked_value) over ())
/ decode(sum(c.prev_booked_value) over (), 0, NULL,
abs(sum(c.prev_booked_value) over ()))
* 100 ISC_MEASURE_8, -- gd total book change
sum(c.curr_fulfill_value) over () ISC_MEASURE_9, -- gd total fulf
(sum(c.curr_fulfill_value) over () - sum(c.prev_fulfill_value) over ())
/ decode(sum(c.prev_fulfill_value) over (), 0, NULL,
abs(sum(c.prev_fulfill_value) over ()))
* 100 ISC_MEASURE_10, -- gd total fulf change
sum(c.curr_booked_value) over ()
/ decode(sum(c.curr_fulfill_value) over (), 0, NULL,
sum(c.curr_fulfill_value) over ()) ISC_MEASURE_11, -- gd total book to fulf r
sum(c.curr_booked_value) over ()
/ decode(sum(c.curr_fulfill_value) over (), 0, NULL,
sum(c.curr_fulfill_value) over ()) -
sum(c.prev_booked_value) over ()
/ decode(sum(c.prev_fulfill_value) over (), 0, NULL,
sum(c.prev_fulfill_value) over ()) ISC_MEASURE_12 -- gd total book to fulf r change
FROM ('||l_union_select_stmt||'
sum(curr_booked_value) CURR_BOOKED_VALUE,
sum(prev_booked_value) PREV_BOOKED_VALUE,
sum(curr_fulfill_value) CURR_FULFILL_VALUE,
sum(prev_fulfill_value) PREV_FULFILL_VALUE
FROM ('||l_inner_select_stmt||'
fact.inv_org_id INV_ORG,
decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
fact.booked_amt_'||l_curr_suffix||', 0) CURR_BOOKED_VALUE,
decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
fact.booked_amt_'||l_curr_suffix||', 0) PREV_BOOKED_VALUE,
0 CURR_FULFILL_VALUE,
0 PREV_FULFILL_VALUE
FROM '||l_mv1||' fact,
FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
WHERE fact.time_id = cal.time_id'||l_flags_where||'
AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
AND cal.period_type_id = fact.period_type_id
AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
||l_prod_cat_where
||l_prod_where
||l_cust_where||'
UNION ALL
'||l_inner_select_stmt||'
fact.inv_org_id INV_ORG,
0 CURR_BOOKED_VALUE,
0 PREV_BOOKED_VALUE,
decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
fact.fulfilled_amt_'||l_curr_suffix||', 0) CURR_FULFILL_VALUE,
decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
fact.fulfilled_amt_'||l_curr_suffix||', 0) PREV_FULFILL_VALUE
FROM '||l_mv2||' fact,
FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
WHERE fact.time_id = cal.time_id'||l_flags_where||'
AND fact.return_flag = 0
AND fact.internal_flag = 0
AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
AND cal.period_type_id = fact.period_type_id
AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
||l_prod_cat_where
||l_prod_where
||l_cust_where||')
WHERE '||l_inv_org_where
||l_union_group_by_stmt||') c)) a,'
||l_where_stmt;