I am trying to get Actual Cost (cost_actual) value from the fin_cost_plan_details CA Clarity Database Table. But, the result of this value is a blob.
I would like to know if this value of Actual Cost is being stored in any other CA Clarity Database Table? If not, I am trying to crack this blob in the following way:
utl_raw.cast_to_raw(dbms_lob.substr (cost_actual, dbms_lob.getlength (cost_actual), 1)) Actual_Cost from fin_cost_plan_details;
However, I am getting a very long number returned back, which is not what I am looking for.
Any help with this would be very much appreciated. Thank you.
The cost plan timiscaled data is stored in the slice tables see
Which table stores the Actual Cost?
You need to learn to read the naming covention
cst = cost
dtl = detail (timiscaled)
Title: 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 (Niku KB ID: 8702)
The Clarity ODF SL Slice Tables for Financial Planning
KB article #8826.00000 Doc Type [FAQ] Last Reviewed 10/02/2007
The latest version of the Tecdoc appears to be
Clarity: Can you provide more details regarding the Financial Planning Slice Tables
Document ID: TEC440146
Last Modified Date: 8/5/2014
I did have an idea about the naming conventions. Thank you anyway.
Regarding the Document ID: TEC440146, it refers to the table -> odf_ssl_cst_dtl_acost for getting the Actual Cost for the Cost Plan. When I run this table, it returns nil values. Also, in your previous list, the only table that returns values for Actual Cost is odf_sl_oplact_w, but, this is not the right table as it is giving false values.
Can you check these ?
RE: Total Actuals on Cost Plan
NSQL Query FIN_FINANCIALS.PLANNED_COST - How To?
Thank you every one for your replies.
@Navdeep: Your query is used to get the actuals for the Budget Plan and I need to get the actuals for the Cost Plan.
@Urmas: I've tried using the ppa_wip and ppa_wip_values tables, but, the ppa_wip_values consists of the natural_actualcost, actualcost and emplyactualcost columns. I am not particularly sure what column is the right one to refer to as I seem to be getting incorrect values. Please refer the Actual Cost column in the screenshot shown below that I need to get the values from.
Did you take a look at the "Cost Plan Detail" object ?
I did look into it if you refer my first query. I am getting a blob for the cost_actual. I try to crack this blob in the following way, but, I get a very long number (raw).
Any help to get the blob cracked would be very grateful. Thank you.
Well there may be a catch:
Those table may only containe the user entered values time scaled.
Thre real actuals may come from PPA_WIP and PPA_WIP_VALUES and are summarized for the time periods displayed on the fly.
Most blobs can be cracked using Time Slicing definitions.
As for your specific question regarding the Actual Cost on Task Assignment, you can use the following:
1. the ODF_SSL tables have the data sliced based on the fiscal periods
2. you could also use XOG read on the project using the arguments to read the 'actuals' segments.
3. there are database 'views' that have a naming convention of 'RPT*COST*' (with the caveat that you should use the DAILY view or modify the other period views as noted in TEC601754)
4. there are some CSP Reports that may also fit your needs.
Thank you all for your replies.
I've figured out that the Actual Cost of the Cost Plan comes from the field actualcost related to the ppa_wip_values CA Clarity Database Table. The trick is to use this table and match it to the respective primary keys of the ppa_wip, inv_investments and biz_com_periods CA Clarity Database Tables. The period_type for the biz_com_periods CA Clarity Database Table should be selected as per the requirement i.e. MONTHLY, etc.
I hope this helps as there is no need to crack the Blob for the cost_actual within the fin_cost_plan_details CA Clarity Database Table.