[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_required_hr)/decode(sum(s.plan_available_hr),
0,null,sum(s.plan_available_hr))*100 ISC_MEASURE_1,
sum(s.comp_required_hr)/decode(sum(s.comp_available_hr),
0,null,sum(s.comp_available_hr))*100 ISC_MEASURE_2,
(sum(s.plan_required_hr)/decode(sum(s.plan_available_hr),
0,null,sum(s.plan_available_hr)))*100-
(sum(s.comp_required_hr)/decode(sum(s.comp_available_hr),
0,null,sum(s.comp_available_hr)))*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.required_hours),null) plan_required_hr,
decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
sum(f.available_hours),null) plan_available_hr,
decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
sum(f.required_hours),null) comp_required_hr,
decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
sum(f.available_hours),null) comp_available_hr
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_0002_MV f
ON f.start_date = time.start_date
AND f.period_type_id = :ISC_PERIOD_TYPE_ID
AND f.res_gp_flag = :ISC_RES_GP_FLAG
AND f.snapshot_id in (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)
'||l_org_where||l_res_group_where||l_res_dept_where||l_res_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';