Budget/Forecast COST & UNITS breakup

[srkoshti]

Where do we find sliced data for Budget/Forecast UNITS and with GROUP/SUB_GROUP BY? We could find Forecast COST in ODF_SL_PLAN_COST but could not find UNITS anywhere. Does anyone know 1. How to get UNITS breakup by period 2. Budget/Forecast & UNITS breakup by GROUP & SUB-GROUP

[Anon Anon]

Maybe this will help The Clarity ODF SL Slice Tables for Financial Planning KB article #8826.00000 Doc Type [FAQ] Last Reviewed 10/02/2007 Subject: Can you provide more details regarding the Financial Planning Slice Tables in Clarity 8? Keywords: documentation, technical reference guide, financial management, planning slice tables, ODF_SL_DTL, ODF_SL_COST, FIN_PLANS, FIN_PLAN_DETAILS Initial Product: Clarity 8.0 Description: Applies To: Clarity 8.0 Question: Since documentation is lacking regarding the Financial Planning Slice tables, can you please let us know more information about these tables? Answer: The FIN_PLANS table is a table that has all the financial plan-to-investment relationship The tables below link to the FIN_BENEFIT_PLAN_DETAILS (holds properties info) ODF_SL_BFT_DTL_ABFT = Benefit Plan > Benefit Actuals - cell values ODF_SL_BFT_DTL_BBFT = Benefit Plan > Benefit Budgeted - cell values ODF_SL_BFT_DTL_VBFT = Benefit Plan > Benefit Variance - cell values The tables below hold COST and BUDGET data and link to the FIN_PLAN_DETAILS (holds properties info) ODF_SL_COST_DTL_BCOST = Cost or Budget Plan - Budgeted Cost - cell values ODF_SL_COST_DTL_COST = Cost or Budget Plan - Cost - cell values ODF_SL_COST_DTL_BREV = Cost or Budget Plan - Budgeted Revenue- cell values ODF_SL_COST_DTL_REV = Cost or Budget Plan - Revenue - cell values ODF_SL_COST_DTL_UNITS = Cost or Budget Plan - Units - cell values Reference Knowledgebase Article #8702 - 98931 - Documentation : Technical Reference Guide - missing complete documentation for ODF_SL and ODF_SSL tables and missing some columns for the FIN_COST_PLAN_DETAILS table Martti K.

[srkoshti]

Thanks, I will check this up

[Legacy User]

Thanks MartinK! I was stuck at the slicing table name for benefits.. got it from your reply!

-Ashmi

[Anon Anon]

Hi srkoshti,

Use the table ODF_SSL_CST_DTL_UNITS (filled by the system time slice 'costplandetail::units::segment').

Bare in mind that the unit (that is entered for a particular fiscal period within a cost or budget plan for a particular role) is stored as a sliced value.

Formula: slice = units/(number of days of period).

E.g.

period = January 2008 with start date 1/1/2008 and end date 31/1/2008 --> period has 31 days (end_date - start_date)
entered unit (hours) = 10

Thus ODF_SSL_CST_DTL_UNITS.slice = 0.322581 (10/31)

sliced value is valid for:
ODF_SSL_CST_DTL_UNITS.start_date = 2008-01-01+timestamp
ODF_SSL_CST_DTL_UNITS.end_date = 2008-01-31+timestamp

Regards,
Remko Lems
Capgemini Netherlands PPI

PS. The start and end date can be used to map against the BIZ_COM_PERIODS table to retrieve the applicable fiscal period.

Message Edited by rogier on 03-14-2008 12:00 AM

[srkoshti]

By the way any idea which table has information on Group By & Sub-Group By and which Slice do we need to use to get those details. Most of the Slice tables has data on the Slice Object only and does not go further deep. Thanks in advance

[Kathryn Ellis]

Tables that hold the detailed financial planning slice data:[left]  [left] ODF_SSL_BFT_DTL_ABFT = Benefit for Actuals
ODF_SSL_BFT_DTL_BBFT = Benefit for Budget
ODF_SSL_BFT_DTL_BFT = Benefit
ODF_SSL_CST_DTL_COST PCOST     -- Planned Cost
ODF_SSL_CST_DTL_REV PCOST       -- Planned Revenue
ODF_SSL_CST_DTL_UNITS PCOST   -- Planned Units
[left] Example Query:  [left] The following query provides columns for the Transaction Class, Resource Role and Charge Code values (if they exist).   If the plan is not grouped by a particular type of attribute, the value will be null.  [left] The query has commented statements so that the query can be adapted for MSSQL or Oracle[left] The query can be adapted to display Planned Cost, Planned Revenue or Planned Units by switching the table in the FROM statement.[left] The query can generate a narrow set of results if you uncomment the criteria in the where clause to select a specific project id (internal id) or specific plan id (internal id).[left]  [left]  [left] SELECT I.ID PROJECT_ID,
 I.NAME PROJECT_NAME,
 P.ID PLAN_ID ,
 P.NAME PLAN_NAME,
 D.TRANSCLASS_ID,
 D.PRROLE_ID,
 D.PRCHARGECODE_ID,
 PCOST.START_DATE,
-- PCOST.SLICE,               -- raw slice data
[left] -- For MSSQL
-- PCOST.SLICE*(DATEDIFF(DAY, PCOST.START_DATE, PCOST.FINISH_DATE)) PLANNED_COST_AMOUNT
[left] -- For Oracle
 ROUND(PCOST.SLICE*(to_date(PCOST.FINISH_DATE) - to_date(PCOST.START_DATE)),2) PLANNED_COST_AMOUNT[left]  [left] FROM[left]  [left] ODF_SSL_CST_DTL_COST PCOST, -- Planned Cost
[left] -- ODF_SSL_CST_DTL_REV PCOST,   -- Planned Revenue
-- ODF_SSL_CST_DTL_UNITS PCOST, -- Planned Units
[left]  FIN_COST_PLAN_DETAILS D,
 FIN_PLANS P,
 INV_INVESTMENTS I
WHERE PCOST.PRJ_OBJECT_ID = D.ID
 AND D.PLAN_ID = P.ID
 --AND P.OBJECT_ID = ???            -- internal project id
 --AND D.PLAN_ID = ???                     -- internal detailed financial plan id
 AND UPPER(P.OBJECT_CODE) ='PROJECT'
 AND P.OBJECT_ID = I.ID
ORDER BY P.ID,
 P.NAME,
 PCOST.START_DATE,
 D.TRANSCLASS_ID,
 D.PRROLE_ID,
 D.PRCHARGECODE_ID
[left]  [left]