How do we map the FIN_FINANCIALS table with INV_INVESTMENTS table to get the planned Net Present Value (NPV) data?

Document ID : KB000055322
Last Modified Date : 14/02/2018
Show Technical Document Details

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?

Solution:

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'

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