[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* l_from_clause := ' (select * from eni_dbi_prc_sum2_mv ' ||
' where marker = 1) edps2mv'; */
/* l_from_clause := ' (select * from eni_dbi_prc_sum2_mv edps2mv ' ||
' where edps2mv.marker = 2 ' ||
' AND edps2mv.product_category_id = '|| l_category ||' ) edps2mv'; */
/* l_from_clause := ' (select * from eni_dbi_prc_sum1_mv edps1mv ' ||
' where edps1mv.item_org_id IN (' || l_item_org || '))edps1mv ' ; */
/* l_from_clause := ' (select * from eni_dbi_prc_sum1_mv edps1mv ' ||
' , eni_denorm_hierarchies edh ' ||
' where edps1mv.item_org_id IN (' || l_item_org || ')' ||
' AND edh.parent_id = '|| l_category ||
' AND edh.child_id = edps1mv.product_category_id ) edps1mv'; */
SELECT
date_name AS VIEWBY
, SUM(ENI_MEASURE1) AS ENI_MEASURE1
, SUM(ENI_MEASURE2) AS ENI_MEASURE2
, SUM(ENI_MEASURE7) AS ENI_MEASURE7
, SUM(ENI_MEASURE8) AS ENI_MEASURE8
, SUM(ENI_MEASURE11) AS ENI_MEASURE11
, SUM(ENI_MEASURE17) AS ENI_MEASURE17
, SUM(ENI_MEASURE10) AS ENI_MEASURE10
, SUM(ENI_MEASURE13) AS ENI_MEASURE13
, SUM(ENI_MEASURE14) AS ENI_MEASURE14
, SUM(ENI_MEASURE16) AS ENI_MEASURE16
, SUM(ENI_MEASURE43) AS ENI_MEASURE43
, SUM(ENI_MEASURE47) AS ENI_MEASURE47
FROM
(
SELECT
date_name
, start_date -- start_date
, 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
, NULL AS ENI_MEASURE43 -- drill across url for other expenses
-- Removed the drill for the bug # 3659784
, NVL(ENI_MEASURE7,0)+NVL(ENI_MEASURE13,0)
AS ENI_MEASURE47 -- for Costs(COGS+Expenses) on graph 1
FROM
(
SELECT
t.name AS date_name
,t.start_date as start_date
,SUM(
case when ftrs.report_date = t.c_end_date
then ' ||l_summary|| '.' || l_revenue || '
else NULL
end
) AS ENI_MEASURE1
,SUM(
case when ftrs.report_date = t.p_end_date
then ' ||l_summary|| '.' || l_revenue || '
else 0
end
) AS ENI_MEASURE2
,SUM(
case when ftrs.report_date = t.c_end_date
then ' ||l_summary|| '.' || l_cogs || '
else NULL
end
) AS ENI_MEASURE7
,SUM(
case when ftrs.report_date = t.p_end_date
then ' ||l_summary|| '.' || l_cogs || '
else 0
end
) AS ENI_MEASURE8
'||l_oex_columns||'
,SUM
(
case when ftrs.report_date = t.c_end_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 NULL
end
) AS ENI_MEASURE16
,SUM
(
case when ftrs.report_date = t.p_end_date
then ((' ||l_summary|| '.' || l_revenue || ' - ' ||l_summary|| '.' || l_cogs || '
- ' ||l_summary|| '.' || l_expense || ')/decode(' ||l_summary|| '.' || l_revenue || ', 0, null, ' ||l_summary|| '.' || l_revenue || '))*100
else 0
end
) AS ENI_MEASURE17
, DECODE( SUM( case when ftrs.report_date = t.c_end_date
then ' ||l_summary|| '.' || l_expense || '
else NULL end),
0, null, ''pFunctionName=ENI_DBI_OEX_R'||l_drill_params||''') -- drill across url for other expenses
AS ENI_MEASURE43
FROM
' || l_from_clause || '
, fii_time_rpt_struct ftrs
, (
SELECT
c.name,
c.'||l_id_column||',
c.start_date AS start_date,
(case when '|| '&' || 'BIS_CURRENT_ASOF_DATE < c.end_date
then '|| '&' || 'BIS_CURRENT_ASOF_DATE else c.end_date end ) AS c_end_date,
(case when '|| '&' || 'BIS_PREVIOUS_ASOF_DATE < p.end_date
then '|| '&' || 'BIS_PREVIOUS_ASOF_DATE else p.end_date end ) AS p_end_date
FROM
' || l_period_type ||' c, ' || l_period_type || ' p
WHERE
c.start_date >= ' || '&' || 'BIS_CURRENT_REPORT_START_DATE
AND c.'||l_id_column||' <= :PERIOD_ID ' || '
AND p.start_date >= ' || '&' || 'BIS_PREVIOUS_REPORT_START_DATE
' || l_comp_where || '
) t
WHERE
(
t.c_end_date = ftrs.report_date
OR t.p_end_date = ftrs.report_date
)
AND BITAND(ftrs.record_type_id, &' || 'BIS_NESTED_PATTERN) = ftrs.record_type_id
AND ' || l_where_clause || '
GROUP BY
t.name,t.start_date,t.c_end_date
)
UNION ALL
SELECT
c.name AS date_name
, c.start_date AS start_date
, NULL AS ENI_MEASURE1
, NULL AS ENI_MEASURE2
, NULL AS ENI_MEASURE7
, NULL AS ENI_MEASURE8
, NULL AS ENI_MEASURE11
, NULL AS ENI_MEASURE17
, NULL AS ENI_MEASURE10
, NULL AS ENI_MEASURE13
, NULL AS ENI_MEASURE14
, NULL AS ENI_MEASURE16
, NULL AS ENI_MEASURE43
, NULL AS ENI_MEASURE47
FROM
' || l_period_type ||' c, ' || l_period_type || ' p
WHERE
c.start_date >= ' || '&' || 'BIS_CURRENT_REPORT_START_DATE
AND c.'||l_id_column||' <= :PERIOD_ID' || '
AND p.start_date >= ' || '&' || 'BIS_PREVIOUS_REPORT_START_DATE
' || l_comp_where || '
)
GROUP BY date_name,start_date
ORDER BY
' || l_order_by ;