AnsweredAssumed Answered

SQL to bring back Costs and Actual Costs

Question asked by robert.muir on Nov 1, 2012
Latest reply on Nov 2, 2012 by Owen_R
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

Outcomes