[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 ISC_MEASURE_1,
0 ISC_MEASURE_2,
0 ISC_MEASURE_3
FROM dual
WHERE 1 = 2 /* PLAN_SNAPSHOT dimension has not been populated */';
l_stmt:='SELECT s.period_name VIEWBY,
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
FROM(
SELECT time.start_date PERIOD_ID,
time.name PERIOD_NAME,
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
(SELECT start_date,name
FROM '||l_period_type||'
WHERE start_date between :ISC_CUR_START and :ISC_CUR_END) time
LEFT OUTER JOIN
ISC_DBI_PM_0001_MV f
ON f.start_date = time.start_date
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 time.name,time.start_date,f.snapshot_id) s
GROUP BY s.period_name,s.period_id
ORDER BY s.period_id';