[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,
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
FROM dual
WHERE 1 = 2 /* PLAN_SNAPSHOT dimension has not been populated */';
SELECT fii.name VIEWBY,
nvl(c.rev,0) ISC_MEASURE_1, -- Revenue
nvl(c.comp_rev,0) ISC_MEASURE_2, -- Compare Plan (Revenue)
nvl((c.rev - c.comp_rev),0) ISC_MEASURE_3, -- Variance (Revenue)
nvl(c.cost,0) ISC_MEASURE_4, -- Cost
nvl(c.comp_cost,0) ISC_MEASURE_5, -- Compare Plan (Cost)
nvl((c.cost - c.comp_cost),0) ISC_MEASURE_6, -- Variance (Cost)
nvl((c.rev - c.cost),0) ISC_MEASURE_7, -- Margin
nvl((c.comp_rev - c.comp_cost),0)
ISC_MEASURE_8, -- Compare Plan (Margin)
nvl(((c.rev - c.cost) - (c.comp_rev - c.comp_cost)),0)
ISC_MEASURE_9, -- Variance (Margin)
(c.rev - c.cost) / decode(c.rev,0,NULL,c.rev) * 100
ISC_MEASURE_10, -- Margin Percent
(c.comp_rev - c.comp_cost) / decode(c.comp_rev,0,NULL,c.comp_rev) * 100
ISC_MEASURE_11, -- Compare Plan (Margin Percent)
((c.rev - c.cost) / decode(c.rev,0,NULL,c.rev) * 100)
- ((c.comp_rev - c.comp_cost) / decode(c.comp_rev,0,NULL,c.comp_rev) * 100)
ISC_MEASURE_12 -- Variance (Margin Percent)
FROM (SELECT f.start_date START_DATE,
sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
f.mds_price'||l_curr_suffix||', 0)) REV,
decode(&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2, -1, null,
sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
f.mds_price'||l_curr_suffix||', 0))) COMP_REV,
sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
f.mds_cost'||l_curr_suffix||', 0)) COST,
decode(&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2, -1, null,
sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
f.mds_cost'||l_curr_suffix||', 0))) COMP_COST
FROM '||l_mv||' f'
||l_prod_cat_from||'
WHERE f.start_date BETWEEN :ISC_TIME_FROM AND :ISC_RPT_END_DATE
AND f.period_type_id = :ISC_PERIOD_TYPE_ID
AND f.snapshot_id IN (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)'
||l_flags_where
||l_org_where||l_prod_cat_where||l_prod_where||'
GROUP BY f.start_date) c,
'||l_period_type||' fii
WHERE fii.start_date BETWEEN :ISC_TIME_FROM AND :ISC_RPT_END_DATE
AND fii.start_date = c.start_date(+)
ORDER BY fii.start_date';