[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_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_required_hr_total) over()/decode(sum(comp_available_hr_total) over(),0,null,
sum(comp_available_hr_total)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_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,
sum(s.plan_required_hr) ISC_MEASURE_7,
sum(s.plan_available_hr) ISC_MEASURE_8,
sum(s.comp_required_hr) comp_required_hr_total,
sum(s.comp_available_hr) comp_available_hr_total
FROM
(SELECT f.'||l_viewby_id||' VIEWBY_ID,
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
ISC_DBI_PM_0002_MV f
WHERE f.start_date = :ISC_CUR_START
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_where||l_res_group_where||l_res_dept_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_required_hr_total <>0 OR comp_available_hr_total <>0)) a,';
l_stmt := 'SELECT org.name VIEWBY,
org.organization_id VIEWBYID,
'||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 res.value VIEWBY,
res.id VIEWBYID,
'||l_inner_sql||'
ENI_RESOURCE_V res
WHERE a.viewby_id = res.id
AND ((a.rnk between &START_INDEX and &END_INDEX) OR &END_INDEX=-1)
ORDER BY rnk';
l_stmt := 'SELECT res.value VIEWBY,
res.id VIEWBYID,
'||l_inner_sql||'
ENI_RESOURCE_DEPARTMENT_V res
WHERE a.viewby_id = res.id
AND ((a.rnk between &START_INDEX and &END_INDEX) OR &END_INDEX=-1)
ORDER BY rnk';
l_stmt := 'SELECT res.value VIEWBY,
res.id VIEWBYID,
'||l_inner_sql||'
ENI_RESOURCE_GROUP_V res
WHERE a.viewby_id = res.id
AND ((a.rnk between &START_INDEX and &END_INDEX) OR &END_INDEX=-1)
ORDER BY rnk';