AnsweredAssumed Answered

Query Project Planned Cost and Actual Cost by Month

Question asked by voliveira on Nov 8, 2013
Latest reply on Nov 8, 2013 by navzjoshi00

Hi,

I am trying to create a QUERY for project Planned Cost and Actual Cost by Month Period. I don't know if I am missing any JOIN/Union or Table.
I want to show this result:

PROJECT ID | PROJECT NAME     | MONTH START          | PLANNED COST        | ACTUAL COST

ABC               | PROJECT ABC        | 01-NOVEMBER-13     | 10.000                         | 7.000

ABC               | PROJECT ABC        | 01-DECEMBER-13     | 10.000                         | 5.000

ABC               | PROJECT ABC        | 01-JANUARY-14         | 10.000                         | 4.000

ABC               | PROJECT ABC        | 01-FEBRUARY-14      | 10.000                         | null

Obs: The actual cost column can have null values

This are the querys that I already done:

Planned Cost (Using FIN_PLANS and BIZ_COM_PERIODS):

select
inv.code investment_code
,inv.name investment_name
,monthly_period.start_date month_start
,round((trunc(fp_cost.finish_date)- trunc(fp_cost.start_date))*fp_cost.slice,2) costs
from
inv_investments inv
inner join BIZ_COM_PERIODS monthly_period on monthly_period.PERIOD_TYPE='MONTHLY'
inner join fin_plans fp on inv.id=fp.object_id
inner join FIN_COST_PLAN_DETAILS fpd on fp.id=fpd.plan_id
inner join ODF_SSL_CST_DTL_COST fp_cost on fpd.id=fp_cost.prj_object_id
and TRUNC(monthly_period.START_DATE) = TRUNC(fp_cost.start_date)
AND TRUNC(monthly_period.END_DATE) = TRUNC(fp_cost.finish_date)
where inv.id=5002107 and fp.is_plan_of_record=1
group by inv.code,inv.name, monthly_period.start_date, round((trunc(fp_cost.finish_date)- trunc(fp_cost.start_date))*fp_cost.slice,2)

Actual Cost QUERY (Using PPA_WIP and BIZ_COM_PERIODS):

SELECT INV.CODE, INV.NAME, PW.INVESTMENT_ID, MONTHLY_PERIOD.START_DATE, PV.ACTUALCOST
FROM INV_INVESTMENTS INV
LEFT JOIN BIZ_COM_PERIODS monthly_period on monthly_period.PERIOD_TYPE='MONTHLY'
INNER JOIN ODF_CA_PROJECT PRJ ON INV.ID = PRJ.ID
INNER JOIN PPA_WIP PW ON INV.ID = PW.INVESTMENT_ID
INNER JOIN FIN_PLANS FP ON PW.INVESTMENT_ID = FP.OBJECT_ID
INNER JOIN FIN_COST_PLAN_DETAILS FPD on FP.ID= FPD.PLAN_ID
AND TRUNC(monthly_period.START_DATE) = TRUNC(PW.MONTH_BEGIN)
AND TRUNC(monthly_period.END_DATE) = TRUNC(PW.MONTH_END)
INNER JOIN PPA_WIP_VALUES PV ON PW.TRANSNO = PV.TRANSNO
WHERE PW.INVESTMENT_ID = 5002107 AND PV.CURRENCY_TYPE = 'HOME'
GROUP BY INV.CODE, INV.NAME, PW.INVESTMENT_ID, MONTHLY_PERIOD.START_DATE, PV.ACTUALCOST

Outcomes