[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_sql_stmt := 'select null VIEWBY,
NULL VIEWBYID,
null ENI_MEASURE1, -- curr mtl_cost for table
null ENI_MEASURE3, -- curr mtl_ovhd_cost for table
null ENI_MEASURE4, -- curr res_cost for table
null ENI_MEASURE5, -- curr osp_cost for table
null ENI_MEASURE6, -- curr ovhd_cost for table
NULL ENI_MEASURE10, -- curr unit_cost for table
null ENI_MEASURE9 -- pct_change for table
from sys.dual' ;
'select name VIEWBY,
1 VIEWBYID,
curr_mtl_cost ENI_MEASURE1,
curr_mtl_ovhd_cost ENI_MEASURE3,
curr_res_cost ENI_MEASURE4,
curr_osp_cost ENI_MEASURE5,
curr_ovhd_cost ENI_MEASURE6,
curr_item_cost ENI_MEASURE10,
prev_item_cost ENI_MEASURE9
-- removed the calculation of change % for Bug# 3933564
from ( select name, report_period_start_date start_date,
sum(case when curr_or_prior_period = ''C''
then material_cost * ' || l_currency_rate || '
else null
end) curr_mtl_cost,
sum(case when curr_or_prior_period = ''C''
then material_overhead_cost * ' || l_currency_rate || '
else null
end) curr_mtl_ovhd_cost,
sum(case when curr_or_prior_period = ''C''
then resource_cost * ' || l_currency_rate || '
else null
end) curr_res_cost,
sum(case when curr_or_prior_period = ''C''
then outside_processing_cost * ' || l_currency_rate || '
else null
end) curr_osp_cost,
sum(case when curr_or_prior_period = ''C''
then overhead_cost * ' || l_currency_rate || '
else null
end) curr_ovhd_cost,
sum(case when curr_or_prior_period = ''C''
then item_cost * ' || l_currency_rate || '
else null
end) curr_item_cost,
sum(case when curr_or_prior_period = ''P''
then item_cost * ' || l_currency_rate || '
else null
end) prev_item_cost
from
(select t.*, cost.*,
rank() over
(partition by t.curr_or_prior_period, t.report_period_start_date
order by effective_date DESC) r
from
(select TO_CHAR(&' || 'BIS_CURRENT_ASOF_DATE + offset , ''dd-Mon-yyyy'') AS name,
''C'' AS curr_or_prior_period,
&' || 'BIS_CURRENT_ASOF_DATE + offset + start_date_offset
AS report_period_start_date,
&' || 'BIS_CURRENT_ASOF_DATE + offset + start_date_offset
AS period_start_date,
&' || 'BIS_CURRENT_ASOF_DATE + offset AS period_end_date
from fii_time_rolling_offsets
where period_type = :l_period_type
AND comparison_type = :l_comp_type
union all
select TO_CHAR(&' || 'BIS_CURRENT_ASOF_DATE + offset , ''dd-Mon-yyyy'') AS name,
''P'' AS curr_or_prior_period,
&' || 'BIS_CURRENT_ASOF_DATE + offset + start_date_offset
AS report_period_start_date,
&' || 'BIS_PREVIOUS_ASOF_DATE + offset + start_date_offset
AS period_start_date,
&' || 'BIS_PREVIOUS_ASOF_DATE + offset AS period_end_date
from fii_time_rolling_offsets
where period_type = :l_period_type
AND comparison_type = :l_comp_type)t,
eni_dbi_item_cost_f cost
where cost.inventory_item_id (+) = :l_item
and cost.organization_id (+) = :l_org
and cost.effective_date (+) <= period_end_date) t
WHERE r=1
GROUP BY name, report_period_start_date
ORDER BY ' || l_order_by || ' )';