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?
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.