Clarity PPM1

Expand all | Collapse all

Record entry into FIN_FINANCIALS

  • 1.  Record entry into FIN_FINANCIALS

    Posted 11-08-2017 03:39 AM

    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.



  • 2.  Re: Record entry into FIN_FINANCIALS

    Posted 11-08-2017 05:21 AM

    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… 



  • 3.  Re: Record entry into FIN_FINANCIALS

    Posted 11-09-2017 08:22 AM

    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')



  • 4.  Re: Record entry into FIN_FINANCIALS

    Posted 11-08-2017 07:18 PM

    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



  • 5.  Re: Record entry into FIN_FINANCIALS

    Posted 11-09-2017 08:24 AM

    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')