Clarity

Hello All,

I am not able to find some projects under FIN_FINANCIALS table. I see both the Projects which are available in it and even not have the Cost Plans created under them and also approved Budgets. 

The Project is also financially active. Is it that there is something missing on the project that is the reason behind the Record not getting created under the Table.

Please suggest.

Thanks and Regards,

Sai.

It would be helpful if you can post the query you are using.

Are you sure that you are referring to the correct row in database table?

You will have to use an intermediate table ODF_OBJECT_INSTANCE_MAPPING to join FIN_FINANCIALS and INV_INVESTMENTS.

You may also refer below link for details.

https://docops.ca.com/ca-ppm/14-3/en/reference/ca-ppm-entity-relationship-diagrams-erd#CAPPMEntityRelationshipDiagrams(E… 

Hello Sridhar, 

Not necessary to use the ODF_OBJECT_INSTANCE_MAPPING table. Below is the query that i have used and is working correct. The query is fetching the correct data for nearly 13K Projects and their Budgets. The issues is with only 29 projects and their budgets have a discrepancy.

SELECT inv.code Projectcode, inv.name, NVL(x.PRJ_PLN_HRS,NVL(OCP.z_planned_hours,0)) PRJ_PLN_HRS
, NVL(x.CST_PLAN_HRS,0) CST_PLAN_HRS
, NVL(x.CST_PLAN_COST,0) CST_PLAN_COST
, NVL(x.CST_PLAN_CAP_COST,0) CST_PLAN_CAP_COST
, NVL(x.CST_PLAN_OP_COST,0) CST_PLAN_OP_COST
FROM inv_investments inv
LEFT OUTER JOIN
(
SELECT inv.id, INV.CODE Projectcode,INV.NAME,NVL(OCP.z_planned_hours,0) PRJ_PLN_HRS
,round(SUM(NVL(FCP.TOTAL_UNITS,0))) CST_PLAN_HRS
,round(SUM(NVL(FCP.TOTAL_COST,0))) CST_PLAN_COST
,round(NVL(FF.planned_cst_capital_total,0)) CST_PLAN_CAP_COST
,round(NVL(FF.planned_cst_operating_total,0)) CST_PLAN_OP_COST
FROM FIN_PLANS FP,
FIN_COST_PLAN_DETAILS FCP,
INV_INVESTMENTS INV,
ODF_CA_PROJECT OCP,
FIN_FINANCIALS FF
WHERE FCP.PLAN_ID=FP.ID
AND INV.ID=FP.OBJECT_ID
AND INV.ID=OCP.ID
AND FF.ID = INV.ID
AND FF.ODF_OBJECT_CODE = 'project'
AND FP.IS_PLAN_OF_RECORD=1
AND FP.PLAN_TYPE_CODE='FORECAST'
GROUP BY inv.id,INV.CODE,INV.NAME,OCP.z_planned_hours,FF.planned_cst_capital_total,FF.planned_cst_operating_total
) x
ON x.id = inv.id
LEFT OUTER JOIN odf_ca_project ocp
ON ocp.id = inv.id
WHERE inv.odf_object_code = 'project'
AND inv.is_active = 1
AND inv.id NOT IN (select prrecordid from prlock where prtablename = 'SRM_PROJECTS')

Hi Sai,

Can you please let me know how did you came to a conclusion that some project are not available under under FIN_FINANCIALS table ?

Can you please run below query replacing the <INV_ID> with the missing project id and see if that fetches any results:

select * from ODF_OBJECT_INSTANCE_MAPPING odf where PRIMARY_OBJECT_INSTANCE_CODE = 'project'  and PRIMARY_OBJECT_INSTANCE_ID=<INV_ID> 

Regards,

Srikanth G

Hello Srikanth 

Yes, this does fetch the Record in it. 

But , Not necessary to use the ODF_OBJECT_INSTANCE_MAPPING table. Below is the query that i have used and is working correct. The query is fetching the correct data for nearly 13K Projects and their Budgets. The issues is with only 29 projects and their budgets have a discrepancy.

SELECT inv.code Projectcode, inv.name, NVL(x.PRJ_PLN_HRS,NVL(OCP.z_planned_hours,0)) PRJ_PLN_HRS
, NVL(x.CST_PLAN_HRS,0) CST_PLAN_HRS
, NVL(x.CST_PLAN_COST,0) CST_PLAN_COST
, NVL(x.CST_PLAN_CAP_COST,0) CST_PLAN_CAP_COST
, NVL(x.CST_PLAN_OP_COST,0) CST_PLAN_OP_COST
FROM inv_investments inv
LEFT OUTER JOIN
(
SELECT inv.id, INV.CODE Projectcode,INV.NAME,NVL(OCP.z_planned_hours,0) PRJ_PLN_HRS 
,round(SUM(NVL(FCP.TOTAL_UNITS,0))) CST_PLAN_HRS
,round(SUM(NVL(FCP.TOTAL_COST,0))) CST_PLAN_COST
,round(NVL(FF.planned_cst_capital_total,0)) CST_PLAN_CAP_COST
,round(NVL(FF.planned_cst_operating_total,0)) CST_PLAN_OP_COST
FROM FIN_PLANS FP, 
FIN_COST_PLAN_DETAILS FCP,
INV_INVESTMENTS INV,
ODF_CA_PROJECT OCP,
FIN_FINANCIALS FF
WHERE FCP.PLAN_ID=FP.ID
AND INV.ID=FP.OBJECT_ID
AND INV.ID=OCP.ID
AND FF.ID = INV.ID
AND FF.ODF_OBJECT_CODE = 'project'
AND FP.IS_PLAN_OF_RECORD=1 
AND FP.PLAN_TYPE_CODE='FORECAST' 
GROUP BY inv.id,INV.CODE,INV.NAME,OCP.z_planned_hours,FF.planned_cst_capital_total,FF.planned_cst_operating_total
) x
ON x.id = inv.id
LEFT OUTER JOIN odf_ca_project ocp
ON ocp.id = inv.id
WHERE inv.odf_object_code = 'project'
AND inv.is_active = 1
AND inv.id NOT IN (select prrecordid from prlock where prtablename = 'SRM_PROJECTS')