[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_sql_stmt := 'select null VIEWBY,
null ENI_MEASURE1, -- elemental_cost for table
null ENI_MEASURE2, -- change for table
null ENI_MEASURE3, -- pct_of_total for table and graph
null ENI_MEASURE4,
null ENI_MEASURE5,
null ENI_MEASURE6
from sys.dual' ;
l_sql_stmt := 'select cost_element VIEWBY,
decode(cost_element_id, 1, curr_mtl_cost,
2, curr_mtl_ovhd_cost,
3, curr_res_cost,
4, curr_osp_cost,
5, curr_ovhd_cost) ENI_MEASURE1,
decode(cost_element_id, 1,
100*(curr_mtl_cost-prev_mtl_cost)/abs(prev_mtl_cost),
2, 100*(curr_mtl_ovhd_cost-prev_mtl_ovhd_cost)/abs(prev_mtl_ovhd_cost),
3, 100*(curr_res_cost-prev_res_cost)/abs(prev_res_cost),
4, 100*(curr_osp_cost-prev_osp_cost)/abs(prev_osp_cost),
5, 100*(curr_ovhd_cost-prev_ovhd_cost)/abs(prev_ovhd_cost)) ENI_MEASURE2,
decode(cost_element_id, 1, 100*(curr_mtl_cost/curr_item_cost_for_div),
2, 100*(curr_mtl_ovhd_cost/curr_item_cost_for_div),
3, 100*(curr_res_cost/curr_item_cost_for_div),
4, 100*(curr_osp_cost/curr_item_cost_for_div),
5, 100*(curr_ovhd_cost/curr_item_cost_for_div)) ENI_MEASURE3,
curr_item_cost ENI_MEASURE4,
Round(100*(curr_item_cost-prev_item_cost_for_div)/abs(prev_item_cost_for_div),2) ENI_MEASURE5,
decode(curr_item_cost,null,null,0,null,100) ENI_MEASURE6
from (select report_period_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 = ''P''
then decode(material_cost,0,null,
material_cost * ' || l_currency_rate || ')
else null
end) prev_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 = ''P''
then decode(material_overhead_cost,0,null,
material_overhead_cost * ' || l_currency_rate || ')
else null
end) prev_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 = ''P''
then decode(resource_cost,0,null,
resource_cost * ' || l_currency_rate || ')
else null
end) prev_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 = ''P''
then decode(outside_processing_cost,0,null,
outside_processing_cost * ' || l_currency_rate || ')
else null
end) prev_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 = ''P''
then decode(overhead_cost,0,null,
overhead_cost * ' || l_currency_rate || ')
else null
end) prev_ovhd_cost,
sum(case when curr_or_prior_period = ''C''
then decode(item_cost,0,null,
item_cost * ' || l_currency_rate || ')
else null
end) curr_item_cost_for_div,
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 decode(item_cost,0,null,
item_cost * ' || l_currency_rate || ')
else null
end) prev_item_cost_for_div,
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 ''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
and offset = 0
union all
select ''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
and offset = 0) 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) cost
where r=1
group by report_period_start_date),
cst_cost_elements cost_elements '
|| '&' || 'ORDER_BY_CLAUSE';