[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
(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 = f.organization_id)
OR EXISTS
(SELECT 1
FROM mtl_parameters org
WHERE org.organization_id = f.organization_id
AND NOT EXISTS
(SELECT 1
FROM org_access ora
WHERE org.organization_id = ora.organization_id)))';
SELECT 0 VIEWBY,
0 VIEWBYID,
0 ISC_ATTRIBUTE_1,
0 ISC_MEASURE_7,
0 ISC_MEASURE_8,
0 ISC_MEASURE_1,
0 ISC_MEASURE_2,
0 ISC_MEASURE_3,
0 ISC_MEASURE_4,
0 ISC_MEASURE_5,
0 ISC_MEASURE_6,
0 ISC_MEASURE_9,
0 ISC_MEASURE_10
FROM dual
WHERE 1 = 2 /* PLAN_SNAPSHOT dimension has not been populated */';
FROM(select (rank() over (&ORDER_BY_CLAUSE nulls last,viewby_id))-1 rnk,
viewby_id,
ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,
sum(ISC_MEASURE_7) over ()/decode(sum(ISC_MEASURE_8) over(),0,null,
sum(ISC_MEASURE_8) over())*100 ISC_MEASURE_4,
sum(comp_ontime_total) over ()/decode(sum(comp_total_lines) over(),0,null,
sum(comp_total_lines) over())*100 ISC_MEASURE_5,
ISC_MEASURE_7, ISC_MEASURE_8,
sum(ISC_MEASURE_7) over () ISC_MEASURE_9,
sum(ISC_MEASURE_8) over () ISC_MEASURE_10
FROM(select s.viewby_id,
sum(s.plan_ontime_lines)/decode(sum(s.plan_total_lines),0,null,
sum(s.plan_total_lines))*100 ISC_MEASURE_1,
sum(s.comp_ontime_lines)/decode(sum(s.comp_total_lines),0,null,
sum(s.comp_total_lines))*100 ISC_MEASURE_2,
(sum(s.plan_ontime_lines)/decode(sum(s.plan_total_lines),0,null,
sum(s.plan_total_lines)))*100-
(sum(s.comp_ontime_lines)/decode(sum(s.comp_total_lines),0,null,
sum(s.comp_total_lines)))*100 ISC_MEASURE_3,
sum(s.plan_ontime_lines) ISC_MEASURE_7,
sum(s.plan_total_lines) ISC_MEASURE_8,
sum(s.comp_ontime_lines) comp_ontime_total,
sum(s.comp_total_lines) comp_total_lines
FROM
(SELECT f.'||l_viewby_id||' VIEWBY_ID,
decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
sum(f.total_lines)-sum(f.late_lines),null) plan_ontime_lines,
decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
sum(f.total_lines),null) plan_total_lines,
decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
sum(f.total_lines)-sum(f.late_lines),null) comp_ontime_lines,
decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
sum(f.total_lines),null) comp_total_lines
FROM
ISC_DBI_PM_0001_MV f
WHERE f.start_date = :ISC_CUR_START
AND f.period_type_id = :ISC_PERIOD_TYPE_ID
AND f.union2_flag <> 0
AND f.item_cat_flag = :ISC_ITEM_CAT_FLAG
AND f.snapshot_id in (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)
'||l_org_where||l_inv_cat_where||l_item_where||
'GROUP BY f.'||l_viewby_id||',f.snapshot_id) s
GROUP BY s.viewby_id)
WHERE (ISC_MEASURE_7 <>0 OR ISC_MEASURE_8 <>0)
OR (comp_ontime_total <>0 OR comp_total_lines <>0)) a,';
l_stmt := 'SELECT org.name VIEWBY,
org.organization_id VIEWBYID,
null ISC_ATTRIBUTE_1,
'||l_inner_sql||'
HR_ALL_ORGANIZATION_UNITS_TL org
WHERE org.organization_id = a.viewby_id
AND org.language = :ISC_LANG
AND ((a.rnk between &START_INDEX and &END_INDEX) OR &END_INDEX=-1)
ORDER BY rnk';
l_stmt :='SELECT items.value VIEWBY,
items.id VIEWBYID,
items.description ISC_ATTRIBUTE_1,
'||l_inner_sql||'
ENI_ITEM_ORG_V items
WHERE a.viewby_id = items.id
AND ((a.rnk between &START_INDEX and &END_INDEX) OR &END_INDEX=-1)
ORDER BY rnk';
l_stmt := 'SELECT ecat.value VIEWBY,
ecat.id VIEWBYID,
null ISC_ATTRIBUTE_1,
'||l_inner_sql||'
ENI_ITEM_INV_CAT_V ecat
WHERE a.viewby_id = ecat.id
AND ((a.rnk between &START_INDEX and &END_INDEX) OR &END_INDEX=-1)
ORDER BY rnk';