[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 VIEWBY,
0 VIEWBYID,
0 ISC_ATTRIBUTE_1,
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 ()*365/(:ISC_CUR_END - :ISC_CUR_START+1)/
decode(sign(sum(ISC_MEASURE_8) over()),0,null,-1,null,
sum(ISC_MEASURE_8) over()) ISC_MEASURE_4,
sum(comp_mds) over ()*365/(:ISC_CUR_END - :ISC_CUR_START+1)/
decode(sign(sum(comp_avg_inv) over()),0,null,-1,null,
sum(comp_avg_inv) over()) 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 viewby_id,
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,
sum(plan_mds_total) ISC_MEASURE_7,
sum(plan_avg_inv) ISC_MEASURE_8,
sum(comp_mds_total) comp_mds,
sum(comp_avg_inv) comp_avg_inv
FROM( SELECT s.viewby_id,
decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
sum(s.mds)*365/(:ISC_CUR_END - :ISC_CUR_START+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,
sum(s.begin_inv+s.end_inv)/2/:ISC_MON_NUM,null) plan_avg_inv,
decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
sum(s.mds)*365/(:ISC_CUR_END - :ISC_CUR_START+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,
decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
sum(s.mds),null) plan_mds_total,
decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
sum(s.mds),null) comp_mds_total,
decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
sum(s.begin_inv+s.end_inv)/2/:ISC_MON_NUM,null) comp_avg_inv
FROM
(SELECT f.'||l_viewby_id||' VIEWBY_ID,
dates.start_date PERIOD,
f.snapshot_id PLAN_ID,
sum(decode(dates.period_type,''P'',f.inventory_cost'||l_curr_suffix||',0)) begin_inv,
sum(decode(dates.period_type,''C'',f.inventory_cost'||l_curr_suffix||',0)) end_inv,
sum(decode(dates.period_type,''C'',f.mds_cost'||l_curr_suffix||',0)) mds
FROM
(SELECT fii.start_date START_DATE,
fii.start_date REPORT_DATE,
''C'' PERIOD_TYPE
FROM FII_TIME_ENT_PERIOD fii
WHERE fii.start_date between :ISC_CUR_START and :ISC_CUR_END
UNION ALL
SELECT cur.start_date start_date,
pre.start_date report_date,
''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
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 = 32
AND f.union1_flag <> 0
AND 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 f.'||l_viewby_id||',dates.start_date,f.snapshot_id) s
GROUP BY s.viewby_id,s.plan_id) c
GROUP BY c.viewby_id)
WHERE (ISC_MEASURE_7 <>0 OR ISC_MEASURE_8 <>0)
OR (comp_mds <> 0 OR comp_avg_inv <>0)) a,';
l_stmt := 'SELECT org.name VIEWBY,
org.organization_id VIEWBYID,
null ISC_ATTRIBUTE_1,
'||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 items.value VIEWBY,
items.id VIEWBYID,
items.description ISC_ATTRIBUTE_1,
'||l_inner_sql||'
ENI_ITEM_ORG_V items
WHERE a.viewby_id = items.id
AND((a.rnk between &START_INDEX and &END_INDEX) OR &END_INDEX=-1)
ORDER BY rnk';
l_stmt := 'SELECT ecat.value VIEWBY,
ecat.id VIEWBYID,
null ISC_ATTRIBUTE_1,
'||l_inner_sql||'
ENI_ITEM_INV_CAT_V ecat
WHERE a.viewby_id = ecat.id
AND ((a.rnk between &START_INDEX and &END_INDEX) OR &END_INDEX=-1)
ORDER BY rnk';