AnsweredAssumed Answered

Budget Plan Actual Cost

Question asked by navzjoshi00 Champion on Mar 8, 2013
Latest reply on Mar 11, 2013 by another_martink
Need to display the sum(actual cost) for the current fiscal year. Also, need to keep in mind that the sum(actual would be from the start date of the approved budget till the end date of the approved budget, and that it should be with the current fiscal year)


The query that is used is -

===================================================================================
Select
b.inv_int_id,
b.inv_ext_id,
b.Yr Year,
NVL(Sum(b.Emp_Budget),0) Emp_Budget, -----> this is the cost approved in the budget plan
NVL(Sum(b.Emp_Actual),0) Emp_Actual, -----> this is the actual cost in the budget plan -----------------------------------------> this is wrong for some and correct for some
NVL(Sum(b.OP_Budget),0) OP_Budget, -----> this is the cost approved in the budget plan for expense
NVL(Sum(b.OP_Actual),0) OP_Actual, -----> this is the actual cost in the budget plan for expense -----------------------------------------> this is wrong for some and correct for some
NVL(Sum(b.CAP_Budget),0) CAP_Budget, -----> this is the cost approved in the budget plan for capital
NVL(Sum(b.CAP_Actual),0) CAP_Actual, -----> this is the actual cost in the budget plan for capital -----------------------------------------> this is wrong for some and correct for some
NVL(Sum(b.Emp_Budget),0)- NVL(Sum(b.Emp_Actual),0) Remain1,
NVL(Sum(b.OP_Budget),0)- NVL(Sum(b.OP_Actual),0) Remain2,
NVL(Sum(b.CAP_Budget),0)- NVL(Sum(b.CAP_Actual),0) Remain3,
NVL(Sum(b.Emp_Budget),0)+NVL(Sum(b.OP_Budget),0)+NVL(Sum(b.CAP_Budget),0) Tot_bud,
NVL(Sum(b.Emp_Actual),0)+NVL(Sum(b.OP_Actual),0)+NVL(Sum(b.CAP_Actual),0) Tot_act,
(NVL(Sum(b.Emp_Budget),0)+NVL(Sum(b.OP_Budget),0)+NVL(Sum(b.CAP_Budget),0))-(NVL(Sum(b.Emp_Actual),0)+NVL(Sum(b.OP_Actual),0)+NVL(Sum(b.CAP_Actual),0)) Tot_remain
from
(select
a.inv_int_id,
a.inv_ext_id,
a.Yr,
a.transclass,
a.nam,
Case When a.transclass = 'Employee Labor' Then Sum(a.Budget) end Emp_Budget,
Case When a.transclass = 'Employee Labor' Then Sum(a.actual) end Emp_Actual,

Case When (a.transclass = 'External' and a.nam='Expense') Then Sum(a.Budget) end OP_Budget,
Case When (a.transclass = 'External' and a.nam='Expense') Then Sum(a.actual) end OP_Actual,
Case When (a.transclass = 'External' and a.nam='Capital') Then Sum(a.Budget) end CAP_Budget,
Case When (a.transclass = 'External' and a.nam='Capital') Then Sum(a.actual) end CAP_Actual
from
(select
i7.id inv_int_id,
i7.code inv_ext_id,
c.prname nam,
Case When tc.description='Employee Labor'Then 'Employee Labor' Else 'External' end transclass,
b1.p_year Yr,
NVL((round(oc.slice * (Trunc(oc.finish_date) - trunc(oc.start_date)),2)),0) as Budget,
0 as actual

from niku.inv_investments i7, niku.transclass tc, niku.biz_com_periods b1,
niku.prchargecode c, niku.fin_plans f, niku.fin_cost_plan_details d, niku.odf_ssl_cst_dtl_cost oc
where i7.id = f.object_id
and f.id = d.plan_id
and d.id = oc.prj_object_id
and tc.id = d.transclass_id
and c.prid = substr(d.plan_detail_2_key,21,7)
and b1.period_type = 'MONTHLY'
and Trunc(oc.start_date) = Trunc(b1.start_date)
and Trunc(oc.finish_date) = Trunc(b1.end_date) and b1.entity_id is not null
and f.period_type_code = b1.period_type
and f.is_plan_of_record = 1
and f.plan_type_code = 'BUDGET'


and i7.id = ([color=#fd0000]<internal project id>[color])

UNION All

select i8.id inv_int_id,
i8.code inv_ext_id,
w.CHARGE_CODE nam,
Case When tc.description='Employee Labor'Then 'Employee Labor' Else 'External' end transclass,
b1.p_year Yr,
0 as Budget,
case when sum(v.totalamount) <>0 then sum(v.totalamount) else 0 end [color=#4c00ff]actual[color] ---> This is where the data is correct for some projects, and incorrect for some

from niku.inv_investments i8, niku.transclass tc, niku.biz_com_periods b1, niku.ppa_wip w, niku.ppa_wip_values v,
niku.fin_plans f, niku.fin_cost_plan_details d, niku.odf_ssl_cst_dtl_cost oc
where w.transno=v.transno and v.currency_type='HOME' and w.status=0
and w.project_code=i8.code
and tc.transclass = w.transclass
and b1.period_type = 'MONTHLY'

and Trunc(oc.start_date) = Trunc(b1.start_date)
and Trunc(oc.finish_date) = Trunc(b1.end_date) and b1.entity_id is not null

and Trunc(w.transdate) between Trunc(oc.start_date) and Trunc(b1.end_date-1)

and i8.id = ([color=#fd0000]<internal project id>[color])

and i8.id= f.object_id
and f.id = d.plan_id
and d.id = oc.prj_object_id
and tc.id = d.transclass_id
and f.period_type_code = b1.period_type
and f.is_plan_of_record = 1
and f.plan_type_code = 'BUDGET'

group by i8.id, i8.code,w.CHARGE_CODE, tc.description, b1.p_year

) a
group by a.inv_int_id, a.inv_ext_id, a.Yr, a.transclass, a.nam
order by a.Yr) b
where
b.Yr IN(SELECT
CASE WHEN TO_NUMBER( TO_CHAR( SYSDATE,'MM') ) IN ( 1, 2, 3 ) Then TO_CHAR( TO_NUMBER( TO_CHAR(SYSDATE,'YYYY') ))
Else TO_CHAR( TO_NUMBER( TO_CHAR(SYSDATE,'YYYY') )+1) end ddd from Dual)
Group By
b.inv_int_id,
b.inv_ext_id,
b.Yr;

=================================================================

This query shows correct data for some projects, but does not work for some.

Any clues ?

NJ

Outcomes