[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select top_node_flag,leaf_node_flag
into top_flag,leaf_flag
from eni_denorm_hierarchies edh
where edh.parent_id = l_category1
and edh.child_id = edh.parent_id;
SELECT vbh.value AS VIEWBY, vbh.id AS VIEWBYID
, ' || l_drill_to_cat_url || ' AS ENI_ATTRIBUTE4
, ENI_MEASURE1 -- current revenue
, ENI_MEASURE2 -- prior revenue
, ENI_MEASURE7 -- current cogs
, ENI_MEASURE8 -- prior cogs
, ((ENI_MEASURE2 - ENI_MEASURE8)
/decode(ENI_MEASURE2, 0, null, ENI_MEASURE2))*100
AS ENI_MEASURE11 -- prior gross margin
, ((ENI_MEASURE2 - ENI_MEASURE8 - ENI_MEASURE14)
/decode(ENI_MEASURE2, 0, null, ENI_MEASURE2))*100
AS ENI_MEASURE17 -- prior product margin
, ((ENI_MEASURE1 - ENI_MEASURE7)
/decode(ENI_MEASURE1, 0, null, ENI_MEASURE1))*100
AS ENI_MEASURE10 -- current gross margin
, ENI_MEASURE13 -- current other expenses
, ENI_MEASURE14 -- prior other expenses
, ((ENI_MEASURE1 - ENI_MEASURE7 - ENI_MEASURE13)
/decode(ENI_MEASURE1, 0, null, ENI_MEASURE1))*100
AS ENI_MEASURE16 -- current product margin
, ENI_MEASURE21 -- current revenue grand total
, ENI_MEASURE22 -- prior revenue grand total
, ENI_MEASURE27 -- current cogs grand total
, ENI_MEASURE28 -- prior cogs grand total
, ((ENI_MEASURE21 - ENI_MEASURE27)
/decode(ENI_MEASURE21, 0, null, ENI_MEASURE21))*100
AS ENI_MEASURE30 -- gross margin grand total
, (
(ENI_MEASURE21-ENI_MEASURE27)
/decode(ENI_MEASURE21, 0, null, ENI_MEASURE21)
)
-
(
(ENI_MEASURE22-ENI_MEASURE28)
/decode(ENI_MEASURE22, 0, null, ENI_MEASURE22)
)
AS ENI_MEASURE32 -- gross margin change
, ENI_MEASURE33 -- current other expenses grand total
, ENI_MEASURE34 -- prior other expenses grand total
, ((ENI_MEASURE21 - ENI_MEASURE27 - ENI_MEASURE33) /decode(ENI_MEASURE21, 0, null, ENI_MEASURE21))*100 AS ENI_MEASURE36 -- product margin grand total
, (
(
(ENI_MEASURE21-ENI_MEASURE27-ENI_MEASURE33)
/decode(ENI_MEASURE21, 0, null, ENI_MEASURE21)
-
(
(ENI_MEASURE22-ENI_MEASURE28-ENI_MEASURE34)
/decode(ENI_MEASURE22, 0, null, ENI_MEASURE22)
)
)
)
AS ENI_MEASURE38 -- product margin change
, ' || l_drill_to_other_expenses || ' -- drill across url for other expenses
AS ENI_MEASURE43
, NVL(ENI_MEASURE7,0)+NVL(ENI_MEASURE13,0)
AS ENI_MEASURE47 -- for Costs(COGS+Expenses) on graph 1
, ((ENI_MEASURE22 - ENI_MEASURE28 - ENI_MEASURE34) /decode(ENI_MEASURE22, 0, null, ENI_MEASURE22))*100 AS ENI_MEASURE20 -- Prior product margin grand total
FROM
(
SELECT t.*, (rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last, id) - 1) col_rank
FROM
(
SELECT
vbh.id,
SUM
(
case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
then
NVL(' || l_summary || '.' || l_revenue || ',0)
else
0
end
) AS ENI_MEASURE1
, SUM
(
case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
then
' || l_summary || '.' || l_revenue || '
else
0
end
) AS ENI_MEASURE2
, SUM
(
case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
then
NVL(' || l_summary || '.' || l_cogs || ',0)
else
0
end
) AS ENI_MEASURE7
, SUM
(
case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
then
' || l_summary || '.' || l_cogs || '
else
0
end
) AS ENI_MEASURE8
'||l_oex_columns||'
,SUM
(
case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
then
NVL(((' || l_summary || '.rev_amount - ' || l_summary || '.cogs_amount)
/decode(' || l_summary || '.rev_amount, 0, null, ' || l_summary || '.rev_amount))*100,0)
else
0
end
)
AS ENI_MEASURE10
, SUM
(
case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
then
NVL(((' || l_summary || '.' || l_revenue || ' - ' || l_summary || '.' || l_cogs || '
- ' || l_summary || '.' || l_expense || ')
/decode(' || l_summary || '.' || l_revenue || ', 0, null, ' || l_summary || '.' || l_revenue || '))*100,0)
else
0
end
)
AS ENI_MEASURE16
, SUM
(
SUM
(
case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
then
NVL(' || l_summary || '.' || l_revenue || ',0)
else
0
end
)
) OVER()
AS ENI_MEASURE21
, SUM
(
SUM
(
case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
then
' || l_summary || '.' || l_revenue || '
else
0
end
)
) OVER()
AS ENI_MEASURE22
, SUM
(
SUM
(
case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
then
NVL(' || l_summary || '.' || l_cogs || ',0)
else
0
end
)
) OVER()
AS ENI_MEASURE27
, SUM
(
SUM
(
case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
then
NVL(' || l_summary || '.' || l_cogs || ',0)
else
0
end
)
) OVER()
AS ENI_MEASURE28
'||l_oex_total_columns||'
FROM
' || l_from_clause || '
, fii_time_rpt_struct ftrs
' || l_lookup_table ||'
WHERE
' || l_summary || '.time_id = ftrs.time_id
AND
(
ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
OR ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
)
AND BITAND(ftrs.record_type_id, &' || 'BIS_NESTED_PATTERN) = ftrs.record_type_id
' || l_where_clause || '
GROUP BY
' || l_group_by_clause || '
)t
where
NOT( (ENI_MEASURE1 = 0) AND (NVL(ENI_MEASURE2,0) = 0) AND
(ENI_MEASURE7 = 0) AND (NVL(ENI_MEASURE8,0) = 0) AND
(NVL(ENI_MEASURE13,0) = 0) AND (NVL(ENI_MEASURE14,0) = 0))
) a '||l_lookup_table||'
where ((a.col_rank between &'||'START_INDEX and &'||'END_INDEX) OR (&'||'END_INDEX = -1)) '||
l_where_clause_outer || ' and a.id = vbh.id and vbh.parent_id = vbh.child_id order by a.col_rank' ;
SELECT eiv.value as VIEWBY
, eiv.id as VIEWBYID
, ' || l_drill_to_cat_url || ' AS ENI_ATTRIBUTE4
, ENI_MEASURE1
, ENI_MEASURE2
, ENI_MEASURE7
, ENI_MEASURE8
, ((ENI_MEASURE2 - ENI_MEASURE8)
/decode(ENI_MEASURE2, 0, null, ENI_MEASURE2))*100
AS ENI_MEASURE11
, ((ENI_MEASURE2 - ENI_MEASURE8 - ENI_MEASURE14)
/decode(ENI_MEASURE2, 0, null, ENI_MEASURE2))*100
AS ENI_MEASURE17
, ((ENI_MEASURE1 - ENI_MEASURE7)
/decode(ENI_MEASURE1, 0, null, ENI_MEASURE1))*100
AS ENI_MEASURE10
, ENI_MEASURE13
, ENI_MEASURE14
,((ENI_MEASURE1 - ENI_MEASURE7 - ENI_MEASURE13)
/decode(ENI_MEASURE1, 0, null, ENI_MEASURE1))*100
AS ENI_MEASURE16
, ENI_MEASURE21
, ENI_MEASURE22
, ENI_MEASURE27
, ENI_MEASURE28
, ((ENI_MEASURE21 - ENI_MEASURE27)
/decode(ENI_MEASURE21, 0, null, ENI_MEASURE21))*100
AS ENI_MEASURE30
, (
(ENI_MEASURE21-ENI_MEASURE27)
/decode(ENI_MEASURE21, 0, null, ENI_MEASURE21)
)
-
(
(ENI_MEASURE22-ENI_MEASURE28)
/decode(ENI_MEASURE22, 0, null, ENI_MEASURE22)
)
AS ENI_MEASURE32
, ENI_MEASURE33
, ENI_MEASURE34
, ((ENI_MEASURE21 - ENI_MEASURE27 - ENI_MEASURE33) /decode(ENI_MEASURE21, 0, null, ENI_MEASURE21))*100 AS ENI_MEASURE36
, (
(
(ENI_MEASURE21-ENI_MEASURE27-ENI_MEASURE33)
/decode(ENI_MEASURE21, 0, null, ENI_MEASURE21)
-
(
(ENI_MEASURE22-ENI_MEASURE28-ENI_MEASURE34)
/decode(ENI_MEASURE22, 0, null, ENI_MEASURE22)
)
)
)
AS ENI_MEASURE38
, ' || l_drill_to_other_expenses || '
AS ENI_MEASURE43
, NVL(ENI_MEASURE7,0)+NVL(ENI_MEASURE13,0)
AS ENI_MEASURE47
, ((ENI_MEASURE22 - ENI_MEASURE28 - ENI_MEASURE34) /decode(ENI_MEASURE22, 0, null, ENI_MEASURE22))*100 AS ENI_MEASURE20 -- Prior product margin grand total
FROM
(
SELECT t.*, (rank() over( &'||'ORDER_BY_CLAUSE'||' nulls last, item_org_id) - 1) col_rank
FROM
(
SELECT
item_org_id,
SUM
(
case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
then
NVL(' || l_summary || '.' || l_revenue || ',0)
else
0
end
)
AS ENI_MEASURE1
, SUM
(
case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
then
' || l_summary || '.' || l_revenue || '
else
0
end
)
AS ENI_MEASURE2
, SUM
(
case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
then
NVL(' || l_summary || '.' || l_cogs || ',0)
else
0
end
)
AS ENI_MEASURE7
, SUM
(
case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
then
' || l_summary || '.' || l_cogs || '
else
0
end
)
AS ENI_MEASURE8
'||l_oex_columns||'
, SUM
(
case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
then
NVL(((' || l_summary || '.rev_amount - ' || l_summary || '.cogs_amount)
/decode(' || l_summary || '.rev_amount, 0, null, ' || l_summary || '.rev_amount))*100,0)
else
0
end
)
AS ENI_MEASURE10
, SUM
(
case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
then
NVL(((' || l_summary || '.' || l_revenue || ' - ' || l_summary || '.' || l_cogs || '
- ' || l_summary || '.' || l_expense || ')
/decode(' || l_summary || '.' || l_revenue || ', 0, null, ' || l_summary || '.' || l_revenue || '))*100,0)
else
0
end
)
AS ENI_MEASURE16
, SUM
(
SUM
(
case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
then
NVL(' || l_summary || '.' || l_revenue || ',0)
else
0
end
)
) OVER() AS ENI_MEASURE21
, SUM
(
SUM
(
case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
then
' || l_summary || '.' || l_revenue || '
else
0
end
)
) OVER() AS ENI_MEASURE22
, SUM
(
SUM
(
case when ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
then
NVL(' || l_summary || '.' || l_cogs || ',0)
else
0
end
)
) OVER() AS ENI_MEASURE27
, SUM
(
SUM
(
case when ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
then
NVL(' || l_summary || '.' || l_cogs || ',0)
else
0
end
)
) OVER() AS ENI_MEASURE28
'||l_oex_total_columns||'
FROM
' || l_from_clause || '
, fii_time_rpt_struct ftrs
WHERE
' || l_summary || '.time_id = ftrs.time_id
AND
(
ftrs.report_date = '||'&'||'BIS_CURRENT_ASOF_DATE
OR ftrs.report_date = '||'&'||'BIS_PREVIOUS_ASOF_DATE
)
AND BITAND(ftrs.record_type_id, &' || 'BIS_NESTED_PATTERN) = ftrs.record_type_id
' || l_where_clause || '
GROUP BY
' || l_group_by_clause || '
)t
where
NOT( (ENI_MEASURE1 = 0) AND (NVL(ENI_MEASURE2,0) = 0) AND
(ENI_MEASURE7 = 0) AND (NVL(ENI_MEASURE8,0) = 0) AND
(NVL(ENI_MEASURE13,0) = 0) AND (NVL(ENI_MEASURE14,0) = 0))
) a '||l_lookup_table||'
where ((a.col_rank between &'||'START_INDEX and &'||'END_INDEX) OR (&'||'END_INDEX = -1)) '||
l_where_clause_outer || 'order by a.col_rank' ;