[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 ISC_ATTRIBUTE_1,
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_7,
0 ISC_MEASURE_8,
0 ISC_MEASURE_9,
0 ISC_MEASURE_10,
0 ISC_MEASURE_11,
0 ISC_MEASURE_12,
0 ISC_MEASURE_13
FROM dual
WHERE 1 = 2 /* PLAN_SNAPSHOT dimension has not been populated */';
SELECT s.period_name VIEWBY,
s.period_id ISC_ATTRIBUTE_1,
sum(s.plan_rev) ISC_MEASURE_1,
sum(s.rev_sf) ISC_MEASURE_2,
sum(s.comp_rev_sf) ISC_MEASURE_3,
sum(s.rev_sf) - sum(s.comp_rev_sf) ISC_MEASURE_4,
sum(s.rev_sf)
/ decode(sum(s.plan_rev), 0, null,
sum(s.plan_rev))
* 100 ISC_MEASURE_5,
sum(s.rev_sf)
/ decode(sum(s.plan_rev), 0, null,
sum(s.plan_rev))
* 100
- sum(s.comp_rev_sf)
/ decode(sum(s.comp_plan_rev), 0, null,
sum(s.comp_plan_rev))
* 100 ISC_MEASURE_6,
sum(s.rev_sf) ISC_MEASURE_7,
sum(s.plan_rev) - sum(s.rev_sf) ISC_MEASURE_8,
sum(s.rev_sf) - sum(s.cost_sf) ISC_MEASURE_9,
sum(s.comp_rev_sf) - sum(s.comp_cost_sf) ISC_MEASURE_10,
sum(s.rev_sf) - sum(s.cost_sf)
- (sum(s.comp_rev_sf) - sum(s.comp_cost_sf)) ISC_MEASURE_11,
(sum(s.rev_sf) - sum(s.cost_sf))
/ decode(sum(s.rev_sf), 0, null,
sum(s.rev_sf))
* 100 ISC_MEASURE_12,
(sum(s.rev_sf) - sum(s.cost_sf))
/ decode(sum(s.rev_sf), 0, null,
sum(s.rev_sf))
* 100 -
(sum(s.comp_rev_sf) - sum(s.comp_cost_sf))
/ decode(sum(s.comp_rev_sf), 0, null,
sum(s.comp_rev_sf))
* 100 ISC_MEASURE_13
FROM (SELECT time.start_date START_DATE,
time.name PERIOD_NAME,
time.'||l_period_id||' PERIOD_ID,
decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
f.mds_price'||l_curr_suffix||', 0) PLAN_REV,
decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
f.mds_price'||l_curr_suffix||', 0) COMP_PLAN_REV,
decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
f.rev_shortfall'||l_curr_suffix||', 0) REV_SF,
decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
f.rev_shortfall'||l_curr_suffix||', 0) COMP_REV_SF,
decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
f.cost_shortfall'||l_curr_suffix||', 0) COST_SF,
decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
f.cost_shortfall'||l_curr_suffix||', 0) COMP_COST_SF
FROM (SELECT start_date,
name,
'||l_period_id||'
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.union1_flag <> 0
AND f.item_cat_flag = 3
AND f.snapshot_id in (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)
'||l_org_where||') s
GROUP BY
s.period_name,
s.period_id,
s.start_date
ORDER BY
s.start_date';