[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_period_type_id NUMBER:=32; --only select month buckets
l_mon_num NUMBER; -- number of months in the selected period
(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 a.name VIEWBY,
sum(plan_inv_turns) ISC_MEASURE_1,
sum(comp_inv_turns) ISC_MEASURE_2,
sum(plan_inv_turns)-sum(comp_inv_turns) ISC_MEASURE_3
FROM(
SELECT fii.name,
fii.start_date,
s.period_id,
decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
sum(s.mds)*365/(fii.end_date - fii.start_date +1)/
decode(sign(sum(s.begin_inv+s.end_inv)),0,null,-1,null,
sum(s.begin_inv+s.end_inv)/2/:ISC_MON_NUM),null) plan_inv_turns,
decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
sum(s.mds)*365/(fii.end_date - fii.start_date +1)/
decode(sign(sum(s.begin_inv+s.end_inv)),0,null,-1,null,
sum(s.begin_inv+s.end_inv)/2/:ISC_MON_NUM),null) comp_inv_turns
FROM
(SELECT dates.'||l_period_id||' PERIOD_ID,
f.snapshot_id PLAN_ID,
sum(decode(dates.period_type,''P'',f.inventory_cost,0)) begin_inv,
sum(decode(dates.period_type,''C'',f.inventory_cost,0)) end_inv,
sum(decode(dates.period_type,''C'',f.mds_cost,0)) mds
FROM
(SELECT fii.start_date REPORT_DATE,
fii.start_date START_DATE,
ent_period_id ENT_PERIOD_ID,
ent_qtr_id ENT_QTR_ID,
ent_year_id ENT_YEAR_ID,
''C'' PERIOD_TYPE
FROM FII_TIME_ENT_PERIOD fii
WHERE fii.start_date between :ISC_CUR_START and :ISC_CUR_END
UNION ALL
SELECT pre.start_date REPORT_DATE,
cur.start_date START_DATE,
cur.period_id ENT_PERIOD_ID,
cur.qtr_id ENT_QTR_ID,
cur.year_id ENT_YEAR_ID,
''P'' PERIOD_TYPE
FROM
(SELECT fii.start_date START_DATE,
rownum ID
FROM FII_TIME_ENT_PERIOD fii
WHERE fii.start_date between :ISC_PRE_START and :ISC_PRE_END
ORDER by fii.start_date DESC) pre,
(SELECT fii.start_date START_DATE,
rownum ID,
ent_period_id PERIOD_ID,
ent_qtr_id QTR_ID,
ent_year_id YEAR_ID
FROM FII_TIME_ENT_PERIOD fii
WHERE fii.start_date between :ISC_CUR_START and :ISC_CUR_END
ORDER by fii.start_date DESC) cur
WHERE cur.id = pre.id(+)) dates,
ISC_DBI_PM_0001_MV f
WHERE f.start_date = dates.report_date
AND f.period_type_id = :ISC_PERIOD_TYPE_ID
AND f.union1_flag <> :ISC_UNION_FLAG
AND f.item_cat_flag = :ISC_ITEM_CAT_FLAG
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 dates.'||l_period_id||',f.snapshot_id) s,
'|| l_period_type ||' fii
WHERE fii.'||l_period_id||' = s.period_id(+)
AND fii.start_date BETWEEN :ISC_CUR_START and :ISC_CUR_END
GROUP BY fii.name,fii.start_date,fii.end_date,s.period_id,s.plan_id) a
GROUP BY a.name, a.start_date
ORDER BY a.start_date';