Tables Associated with Financial Cost Plans
Article ID: 54959
Updated On:
Products
Issue/Introduction
Which tables store the detailed financial planning data for Cost Plans, Benefit Plans and Budget Plans?
Environment
Any Supported Clarity release
Cause
Resolution
The FIN_PLANS table stores all the financial plan-to-investment relationship.
There are two types of tables that hold the time-scaled values (TSV) data for each cell within the detailed financial plans. The tables that are named like ODF_SSL_*, e.g. ODF_SSL_BFT_DTL_BFT
contain the slice values for the TSV.
The tables that are named like ODF_SL_* contain internal, system-defined and user-defined sliced values for financial detailed plans. These tables are dependent on a successful Time Slicing job completion.
Sliced Data
The ODF_SL tables are used to display data in the Investment > Hierarchy > Detail View. For ensuring that updated values are reflected on these views, execute the 'Time Slicing' job.
TSV Data
The tables below link to the FIN_BENEFIT_PLAN_DETAILS which contains the Benefit Properties and reflect the time-scaled values that appear on the detailed financial plans. These tables do not depend on the 'Time Slicing' job.
These values are divided by the number of days in the period to get the slice amount. For example, if the value = 1 and there are 31 days in the period, the slice will show .032258, if you then multiply that by 31 you will not get a whole number as there are more decimals. Therefore when using a query, you need to use the ROUND function to get the correct values.
select id, request_name, table_name
from
prj_blb_slicerequests
where request_name like '%benefitplandetail%::segment%
The tables below link to the FIN_PLAN_DETAILS for Cost Plans
select id, request_name, table_name
from
prj_blb_slicerequests
where request_name like '%costplandetail%::segment%'
Below is a sample query that you can use to get the cell details for the 'Actual Benefit' value on a specific Benefit Plan.
Based on the information provided above you can modify the following query to get results for other cells and other plan types.
Note: The annual and quarterly data are rolled up by the unit, e.g. daily, monthly.
For MSSQL - Actual Benefit Plan Data
SELECT I.ID PROJECT_ID,
I.NAME PROJECT_NAME,
P.ID PLAN_ID ,
P.NAME PLAN_NAME,
D.DETAIL DETAIL_NAME,
ABFT.START_DATE,
ABFT.SLICE,
ROUND(ABFT.SLICE*(DATEDIFF(DAY, ABFT.START_DATE, ABFT.FINISH_DATE)),2) CALC_SLICE
FROM ODF_SSL_BFT_DTL_ABFT ABFT,
FIN_BENEFIT_PLAN_DETAILS D,
FIN_PLANS P,
INV_INVESTMENTS I
WHERE ABFT.PRJ_OBJECT_ID = D.ID
AND D.PLAN_ID = P.ID
AND P.OBJECT_ID = xxxxx
AND P.OBJECT_CODE ='PROJECT'
AND P.OBJECT_ID = I.ID
ORDER BY P.ID,
P.NAME,
D.DETAIL
For Oracle - Actual Benefit Plan Data
SELECT I.ID PROJECT_ID,
I.NAME PROJECT_NAME,
P.ID PLAN_ID ,
P.NAME PLAN_NAME,
D.DETAIL DETAIL_NAME,
ABFT.START_DATE,
ABFT.SLICE,
ROUND(ABFT.SLICE*(to_date(ABFT.FINISH_DATE) - to_date(ABFT.START_DATE)),2) CALC_SLICE
FROM ODF_SSL_BFT_DTL_ABFT ABFT,
FIN_BENEFIT_PLAN_DETAILS D,
FIN_PLANS P,
INV_INVESTMENTS I
WHERE ABFT.PRJ_OBJECT_ID = D.ID
AND D.PLAN_ID = P.ID
AND P.OBJECT_ID = xxxxxx
AND P.OBJECT_CODE ='PROJECT'
AND P.OBJECT_ID = I.ID
ORDER BY P.ID,
P.NAME,
D.DETAIL
;
NOTE: Only the 'planned' data that is directly entered through the application will be stored in the cost plan slice tables.
By design, the following tables are not used for storing cost plan data:
ODF_SSL_CST_DTL_VCOST
ODF_SSL_CST_DTL_AREV
ODF_SSL_CST_DTL_VUNITS
ODF_SSL_CST_DTL_ACOST
ODF_SSL_CST_DTL_AUNITS
ODF_SSL_CST_DTL_VREV
The Cost Variance is a computed field that is computed on the fly and not stored in a table.
It is computed as planned cost minus actual cost.
Actual Cost is not stored on the ODF_SSL slice tables either, because it is computed from the PPA_WIP data.
Therefore, the following table/attribute can be queried:
PPA_WIP.Quantity
PPA_WIP_VALUES.TOTALCOST
PPA_WIP_VALUES.ACTUALCOST
Additional Information
For benefit plan cost slices when the billing currency is different than the project currency the slice odf_ssl_bft_dtl_bbft will convert the costs to the billing currency rate.
Instead use slice ODF_SSL_BFT_DTL_BFT - this is the benefit plan cost slice.
For portlets or other reporting, if you want to see the values totaled by Fiscal Periods you can query the data warehouse tables instead.
Most of the values are held in the view DWH_FIN_PLAN_VIRTUAL_V
You will have to join this to other tables such as dwh_cmn_period to get the correct periods as well as other tables/views to get the data you want.
The easiest way to find the tables/views you need is to create an ad-hoc view and then click on the View SQL Query icon to see what tables are being used.
If this icon is not editable, your Jaspersoft admin can set it.
Feedback
