Check if this helps -
Can you provide more details regarding the Financial Planning Slice Tables?
Document ID: TEC440146
Last Modified Date: 2/24/2015
Hide Technical Document Details
- Products
- Releases
- CA PPM:Release:12.1
- CA PPM:Release:13.0
- CA PPM:Release:13.0.1
- CA PPM:Release:13.1
- CA PPM:Release:13.2
- CA PPM:Release:13.3
- CA PPM:Release:14.1
- Components
- CLARITY HOSTED FINANCIALS, TIME ＆ DATA MANAGEMENT
- CLARITY ON DEMAND FINANCIALS, TIME ＆ DATA MANAGEMENT
- CLARITY PPM FINANCIALS, TIME ＆ DATA MANAGEMENT
Description:
Since documentation is lacking regarding the Financial Planning Slice tables, can you please let us know more information about the tables that store the detailed financial planning data for Cost Plans, Benefit Plans and Budget Plans?
Solution:
NOTE: If you are looking for tables or views that provide Cost Plan data sliced into Fiscal Periods, you can look into the CA PPM Solution Pack (CSP) for stock reports or check out the Financial Universe within Business Objects for building your own reports. If you need assistance in building your own reports or portlets, please contact your account manager or CA Global Services team.
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_* 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.
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 this query to get results for other cells and other plan types.
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 = 5001379
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 = 5001379
AND P.OBJECT_CODE ='PROJECT'
AND P.OBJECT_ID = I.ID
ORDER BY P.ID,
P.NAME,
D.DETAIL
;
NJ