[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 c.period_name VIEWBY,
nvl(c.prod,0) ISC_MEASURE_1, -- Production Cost
nvl(c.comp_prod,0) ISC_MEASURE_2, -- Compare Plan (Production Cost)
nvl((c.prod - c.comp_prod),0) ISC_MEASURE_3, -- Variance (Production Cost)
nvl(c.carry,0) ISC_MEASURE_4, -- Carrying Cost
nvl(c.comp_carry,0) ISC_MEASURE_5, -- Compare Plan (Carrying Cost)
nvl((c.carry - c.comp_carry),0) ISC_MEASURE_6, -- Variance (Carrying Cost)
nvl(c.purch,0) ISC_MEASURE_7, -- Purchasing Cost
nvl(c.comp_purch,0) ISC_MEASURE_8, -- Compare Plan (Purchasing Cost)
nvl((c.purch - c.comp_purch),0) ISC_MEASURE_9, -- Variance (Purchasing Cost)
nvl((c.prod + c.carry + c.purch),0)
ISC_MEASURE_10, -- Combined Cost
nvl((c.comp_prod + c.comp_carry + c.comp_purch),0)
ISC_MEASURE_11, -- Compare Plan (Combined Cost)
nvl(((c.prod + c.carry + c.purch)
- (c.comp_prod + c.comp_carry + c.comp_purch)),0)
ISC_MEASURE_12 -- Variance (Combined Cost)
FROM (SELECT time.start_date PERIOD_ID,
time.name PERIOD_NAME,
sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
f.'||l_col1||', 0)) PROD,
decode(&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2, -1, null,
sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
f.'||l_col1||', 0))) COMP_PROD,
sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
f.'||l_col2||', 0)) CARRY,
decode(&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2, -1, null,
sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
f.'||l_col2||', 0))) COMP_CARRY,
sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
f.'||l_col3||', 0)) PURCH,
decode(&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2, -1, null,
sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
f.'||l_col3||', 0))) COMP_PURCH
FROM (SELECT start_date, name
FROM '||l_period_type||'
WHERE start_date BETWEEN :ISC_TIME_FROM AND :ISC_RPT_END_DATE
) 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.item_cat_flag = :ISC_ITEM_CAT_FLAG
AND f.union1_flag <> 0
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) c
ORDER BY c.period_id';