Clarity

Attempting to build SQL that will bring back the Costs and Actuals Costs data displayed in the Financial Plans tab of Projects. So far we have the SQL below which is bringing back Cost but we are struggling with getting the Actual Costs as well. From searching the forums we should be linking what we have so far with the WIP tables? Can anybody help point us in the right direction?

SELECT COST."Investment ID",
COST."Investment Name",
COST."Investment Type",
COST."Plan ID",
COST."Plan Name",
COST."Type of Plan",
BIZ.Period_Type,
COST."Plan of Record",
TRANS.Description TransClass,
code.PRNAME "Charge Code",
BIZ.PERIOD_NAME,
nvl(COST.SLICE,0) "Cost"
FROM (SELECT PRJ_OBJECT_ID,
TRANSCLASS_ID,
PRCHARGECODE_ID,
INV.CODE "Investment ID",
ENTITY_CODE,
INV.NAME "Investment Name",
INV.ODF_OBJECT_CODE "Investment Type",
PLAN.CODE "Plan ID",
PLAN.NAME "Plan Name",
PLAN.PLAN_TYPE_CODE "Type of Plan",
Period_Type_Code "Period Type" ,
IS_PLAN_OF_RECORD "Plan of Record",
PLAN_BY_1_CODE,
PLAN_BY_2_CODE,
SSL.START_DATE,
SSL.FINISH_DATE,
ROUND((SSL.FINISH_DATE-SSL.START_DATE)*SUM(SSL.SLICE),2) SLICE
FROM ODF_SSL_CST_DTL_COST SSL,
INV_INVESTMENTS INV,
FIN_PLANS PLAN,
FIN_COST_PLAN_DETAILS DETAILS
WHERE INV.ODF_OBJECT_CODE = 'project'
AND INV.ID = PLAN.OBJECT_ID
AND PLAN.ID = DETAILS.PLAN_ID
AND DETAILS.ID = SSL.PRJ_OBJECT_ID
AND PLAN_TYPE_CODE = 'BUDGET'
and inv.code = 'EMXMPSSCPB3GNDE2257'
and IS_PLAN_OF_RECORD = 1
GROUP BY INV.CODE,
INV.NAME,
INV.ODF_OBJECT_CODE,
PLAN.CODE,
PLAN.NAME,
PLAN.PLAN_TYPE_CODE,
Period_Type_code,
IS_PLAN_OF_RECORD,
ENTITY_CODE,
START_DATE,
FINISH_DATE,
PLAN_BY_2_CODE,(SSL.START_DATE-SSL.FINISH_DATE),
PLAN_BY_1_CODE,
PRJ_OBJECT_ID,
TRANSCLASS_ID,PRCHARGECODE_ID) COST,




(SELECT LOOKUP_CODE,NAME FROM CMN_LOOKUPS_V WHERE LOOKUP_TYPE='FIN_PLAN_BY_TYPE' AND LANGUAGE_CODE='en') GRP_BY,
(SELECT LOOKUP_CODE,NAME FROM CMN_LOOKUPS_V WHERE LOOKUP_TYPE='FIN_PLAN_BY_TYPE' AND LANGUAGE_CODE='en') SUB_GRP_BY,
ENTITY ENT,
BIZ_COM_PERIODS BIZ,
TRANSCLASS TRANS,
PRCHARGECODE CODE
WHERE
GRP_BY.LOOKUP_CODE =COST.PLAN_BY_1_CODE
AND SUB_GRP_BY.LOOKUP_CODE=COST.PLAN_BY_2_CODE
AND COST.ENTITY_CODE=ENT.ENTITY
AND BIZ.START_DATE=COST.START_DATE
AND BIZ.ENTITY_ID=ENT.ID
AND TRANS.ID=COST.TRANSCLASS_ID
AND CODE.PRID=COST.PRCHARGECODE_ID
ORDER BY PERIOD_NAME

" From searching the forums we should be linking what we have so far with the WIP tables?" - Correct

From ppa_wip table, you can get hours (quantity), and from ppa_wip_values, you will get the cost. Both these wip tables are joined by transno

NJ

Thanks NJ.

Anyone able to provide the SQL to add to the SQL to show the Actuals?

This will give you something similar to the columns you have in your query. I guess you will want to union it to your query. Note that there are various cost columns in PPA_WIP_VALUES, and the numbers therein can be different if you use multi-currency and/or varying rates by resource . There is a document here that describes how they are calculated. And you always have 5 rows in PPA_WIP_VALUES for each transaction in PPA_WIP - so you need to filter by CURRENCY_TYPE.

Also note that PPA_WIP stores the text values for Transclass and Charge_Code, not the internal 500000 ID values you have in your query, so you may also need to join the code below to the TRANSCLASS and PRCHARGECODE tables to get those.

select 

w.transclass,
w.charge_code,
inv.code as inv_code,
inv.name as inv_name,
w.month_begin,
w.month_end,
w.entity,
sum(w.quantity) as quantity,
sum(wv.amount) as amount,
sum(wv.actualcost) as actual_cost,
sum(wv.totalcost) as total_cost

from 
ppa_wip w 
inner join ppa_wip_values wv on w.transno = wv.transno
inner join inv_investments inv on w.investment_id = inv.id

where w.project_code = '00001'
and wv.currency_type = 'home'

group by
w.transclass,
w.charge_code,
inv.code,
inv.name,
w.month_begin,
w.month_end,
w.entity