How do we map the FIN_FINANCIALS table with INV_INVESTMENTS table - Financial Summary Amounts

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Description:

We would like to retrieve the value in the table for the NPV so that we can create our own user-defined portlets within Clarity. How can we get this data from the table?

 

 

Environment

Release: All

Resolution

The mapping for the FIN_FINANCIALS and INV_INVESTMENTS is stored in a table called ODF_OBJECT_INSTANCE_MAPPING. This table holds the id's that maps the records in the investment table and the records in the financial table.

Here is a sample query that gives the investment name and its Net Present Value (NPV) for project investments.

SELECT inv.NAME INVESTMENT_NAME, fin.PLANNED_NPV
FROM FIN_FINANCIALS fin , INV_INVESTMENTS inv, ODF_OBJECT_INSTANCE_MAPPING odf
WHERE odf.PRIMARY_OBJECT_INSTANCE_ID = inv.ID
AND odf.SECONDARY_OBJECT_INSTANCE_ID = fin.ID
AND PRIMARY_OBJECT_INSTANCE_CODE = 'project'
AND SECONDARY_OBJECT_INSTANCE_CODE = 'financials'

 

You can modify the query to add other fields from the FIN_FINANCIALS table other than NPV.

For more information on the Clarity database schema, please refer to the current Clarity Technical Reference Guide.