Clarity

[Apologies if this ends up a double post - I can't seem to find the one I submitted earlier.]  I need to pull cost plan detail data out of multiple projects for analysis.   The only visibility i can find is through the GUI or via XOG.   Looking into the FIN_COST_PLAN_DETAILS table, i discovered that the units and costs are encoded in a BLOB (makes sense, considering the time-varying nature of the data, but still had to check).   Scraping the numbers out the GUI is not an option.   Pulling them out via XOG would require deconstructing the XML, then taking data from the XML and looking up project data in the database. I need to either deliver this data via a portlet that I can export to excel, or with SQL to generate a report that i can output with comma's so excel can load it as a .csv file.  Is there a datamart table that would contain the cracked cost plan detail data?   or am I pretty much stuck with the multi-step option involving XOG?  ThanksBeekerC

Hi,  I would suggest to look at the ODF_SL_Budget_... or the ODF_SL_plan_.. tables or starting with odf_SL because I know the gl allocation details are also stored in an odf_SL table.  kind regards,  Elwin  

Have a look at KB article #8826 (old number)and ODF_SL_ tables  Martti K  Sorry Elwin, you posted while I was writing. Message Edited by another_martink on 16-10-2009 12:21 AM [left]

 I went digging through the ODF_SL and ODF_SSL tables but I didn't see anything that looked like a daily, weekly or montly portion of any of the cost figrues I entered into the cost plan.Also   as search on KB article # 8826 turned up somethig for ErWin data modeller.   do you have a URL link for tha KB article?   or perhaps some other way of searching for that number?   I'll keep digging.Thanks for the info.BeekerC

Hi,      Calculating data for cost plan you can create one view which will output your plan data in months, years, or week basis.  Here is a sample SQL code â€"        For calculating yearly data â€"      SELECT                     fpd.plan_id,f.object_id,SUM(ROUND(slice*(finish_date-start_date),2)) yr3amt  FROM                         ODF_SSL_CST_DTL_COST o,                                                  FIN_COST_PLAN_DETAILS fpd,                                                  FIN_PLANS f  WHERE                     fpd.plan_id = f.ID  AND                             o.prj_object_id =fpd.ID  AND                               f.plan_type_code='FORECAST'  AND                               start_date>=(SELECT start_date FROM BIZ_COM_PERIODS WHERE period_type='ANNUALLY'                                                  AND                             p_year = (SELECT TO_CHAR(SYSDATE,'YYYY')+1 FROM dual))  AND                               finish_date     I hope this will helpful.    ~Sonal

Hi, Cost plan and Budget plan data is stored in the same table. but benefit plan is stored in different table. I havedeveloped the below query which is used to fecthing record for all possible scenarios.  -- Cost plan details (Total Unit, Total Cost, Total Revenue)  SELECT INV.CODE "Investment ID",INV.NAME "Investment Name",INV.ODF_OBJECT_CODE "Investment Type",PLAN.CODE "PlanID",PLAN.NAME "Plan Name",PLAN.PLAN_TYPE_CODE "Type of Plan",PERIOD_TYPE_CODE "Period Type",STRT.START_DATE "Plan StartPeriod",END_DATE.END_DATE-1 "Plan End Period", GRP_BY.NAME "Group By",SUB_GRP_BY.NAME "Sub Group By",IS_PLAN_OF_RECORD "Planof Record",TOTAL_UNITS "Total Units",TOTAL_COST "Total Cost",TOTAL_REVENUE "Total Revenue"
FROM INV_INVESTMENTS INV, FIN_PLANS PLAN, ODF_CA_COSTPLAN ODF, BIZ_COM_PERIODS STRT,BIZ_COM_PERIODS END_DATE,
(SELECT LOOKUP_CODE,NAME FROM CMN_LOOKUPS_V WHERE LOOKUP_TYPE='FIN_PLAN_BY_TYPE' AND LANGUAGE_CODE='en') GRP_BY,
(SELECT LOOKUP_CODE,NAME FROM CMN_LOOKUPS_V WHERE LOOKUP_TYPE='FIN_PLAN_BY_TYPE' AND LANGUAGE_CODE='en') SUB_GRP_BY WHERE INV.ODF_OBJECT_CODE = 'project' AND INV.ID = PLAN.OBJECT_ID AND PLAN.ID = ODF.ID AND PLAN_TYPE_CODE='FORECAST'
AND STRT.ID=START_PERIOD_ID AND END_DATE.ID=END_PERIOD_ID AND GRP_BY.LOOKUP_CODE =PLAN_BY_1_CODE ANDSUB_GRP_BY.LOOKUP_CODE=PLAN_BY_2_CODE AND IS_PLAN_OF_RECORD=1 AND INV.CODE = '09000002'  -- Benefit plan details (Total Benefit, Actual Benefit, Billing Benefit)  SELECT INV.CODE "Investment ID",INV.NAME "Investment Name",INV.ODF_OBJECT_CODE "Investment Type",PLAN.CODE "PlanID",PLAN.NAME "Plan Name",NVL(PLAN.PLAN_TYPE_CODE,'Benefit') "Type of Plan",PERIOD_TYPE_CODE "Period Type",STRT.START_DATE"Plan Start Period",END_DATE.END_DATE-1 "Plan End Period", IS_PLAN_OF_RECORD "Plan of Record",TOTAL_BENEFIT "Benefit",TOTAL_ACTUAL_BENEFIT "Actual Benefit", TOTAL_BILLING_BENEFIT "Billing Benefit"
FROM INV_INVESTMENTS INV, FIN_PLANS PLAN, ODF_CA_BENEFITPLAN ODF, BIZ_COM_PERIODS STRT,BIZ_COM_PERIODS END_DATE WHEREINV.ODF_OBJECT_CODE = 'project' AND INV.ID = PLAN.OBJECT_ID AND PLAN.ID = ODF.ID AND PLAN_TYPE_CODE IS NULL
AND STRT.ID=START_PERIOD_ID AND END_DATE.ID=END_PERIOD_ID AND IS_PLAN_OF_RECORD=1 AND INV.CODE = '09000002'    -- Budget plan details (Total Unit, Total Cost, Total Revenue)SELECT INV.CODE "Investment ID",INV.NAME "Investment Name",INV.ODF_OBJECT_CODE "Investment Type",PLAN.CODE "PlanID",PLAN.NAME "Plan Name",PLAN.PLAN_TYPE_CODE "Type of Plan",PERIOD_TYPE_CODE "Period Type",STRT.START_DATE "Plan StartPeriod",END_DATE.END_DATE-1 "Plan End Period", GRP_BY.NAME "Group By",SUB_GRP_BY.NAME "Sub Group By",IS_PLAN_OF_RECORD "Planof Record",TOTAL_UNITS "Total Units",TOTAL_COST "Total Cost",TOTAL_REVENUE "Total Revenue"
FROM INV_INVESTMENTS INV, FIN_PLANS PLAN, ODF_CA_COSTPLAN ODF, BIZ_COM_PERIODS STRT,BIZ_COM_PERIODS END_DATE,
(SELECT LOOKUP_CODE,NAME FROM CMN_LOOKUPS_V WHERE LOOKUP_TYPE='FIN_PLAN_BY_TYPE' AND LANGUAGE_CODE='en') GRP_BY,
(SELECT LOOKUP_CODE,NAME FROM CMN_LOOKUPS_V WHERE LOOKUP_TYPE='FIN_PLAN_BY_TYPE' AND LANGUAGE_CODE='en') SUB_GRP_BY WHEREINV.ODF_OBJECT_CODE = 'project' AND INV.ID = PLAN.OBJECT_ID AND PLAN.ID = ODF.ID AND PLAN_TYPE_CODE='BUDGET'
AND STRT.ID=START_PERIOD_ID AND END_DATE.ID=END_PERIOD_ID AND GRP_BY.LOOKUP_CODE =PLAN_BY_1_CODE ANDSUB_GRP_BY.LOOKUP_CODE=PLAN_BY_2_CODE AND IS_PLAN_OF_RECORD=1 AND INV.CODE = '09000002'-- Cost plan details (Total Benefit, Actual Benefit, Billing Benefit) based on month (Group by Month)  SELECT COST."Investment ID",COST."Investment Name",COST."Investment Type",COST."Plan ID",COST."Plan Name",COST."Type ofPlan",COST."Period Type",COST."Plan of Record",
GRP_BY.NAME "Group By",SUB_GRP_BY.NAME "Sub Group By",BIZ.PERIOD_NAME,nvl(UNIT.SLICE,0) "Units", nvl(COST.SLICE,0) "Cost",nvl(REVENUE.SLICE,0) "Revenue" FROM
(SELECT INV.CODE "Investment ID",ENTITY_CODE,INV.NAME "Investment Name",INV.ODF_OBJECT_CODE "Investment Type",PLAN.CODE "PlanID",PLAN.NAME "Plan Name",PLAN.PLAN_TYPE_CODE "Type of Plan",PERIOD_TYPE_CODE "Period Type"
,IS_PLAN_OF_RECORD "Plan of Record",PLAN_BY_1_CODE,PLAN_BY_2_CODE,SSL.START_DATE,SSL.FINISH_DATE,ROUND((SSL.FINISH_DATE-SSL.START_DATE)*SUM(SSL.SLICE),2) SLICE FROM ODF_SSL_CST_DTL_COST SSL, INV_INVESTMENTS INV, FIN_PLANS PLAN,FIN_COST_PLAN_DETAILS DETAILS WHERE INV.CODE = '09000002' AND INV.ODF_OBJECT_CODE = 'project' AND INV.ID = PLAN.OBJECT_ID ANDPLAN.ID = DETAILS.PLAN_ID AND DETAILS.ID = SSL.PRJ_OBJECT_ID AND PLAN_TYPE_CODE = 'FORECAST'
AND IS_PLAN_OF_RECORD=1 GROUP BY INV.CODE,INV.NAME,INV.ODF_OBJECT_CODE,PLAN.CODE,PLAN.NAME,PLAN.PLAN_TYPE_CODE,PERIOD_TYPE_CODE,
IS_PLAN_OF_RECORD,ENTITY_CODE,START_DATE,FINISH_DATE,PLAN_BY_2_CODE,(SSL.START_DATE-SSL.FINISH_DATE),PLAN_BY_1_CODE) COST,
(SELECT INV.CODE "Investment ID",ENTITY_CODE,INV.NAME "Investment Name",INV.ODF_OBJECT_CODE "Investment Type",PLAN.CODE "PlanID",PLAN.NAME "Plan Name",PLAN.PLAN_TYPE_CODE "Type of Plan",PERIOD_TYPE_CODE "Period Type"
,IS_PLAN_OF_RECORD "Plan of Record",PLAN_BY_1_CODE,PLAN_BY_2_CODE,SSL.START_DATE,SSL.FINISH_DATE,ROUND((SSL.FINISH_DATE-SSL.START_DATE)*SUM(SSL.SLICE),2) SLICE FROM ODF_SSL_CST_DTL_UNITS SSL, INV_INVESTMENTS INV, FIN_PLANS PLAN,FIN_COST_PLAN_DETAILS DETAILS WHERE INV.CODE = '09000002' AND INV.ODF_OBJECT_CODE = 'project' AND INV.ID = PLAN.OBJECT_ID ANDPLAN.ID = DETAILS.PLAN_ID AND DETAILS.ID = SSL.PRJ_OBJECT_ID AND PLAN_TYPE_CODE = 'FORECAST'
AND IS_PLAN_OF_RECORD=1 GROUP BY ENTITY_CODE,INV.CODE,INV.NAME,INV.ODF_OBJECT_CODE,PLAN.CODE,PLAN.NAME,PLAN.PLAN_TYPE_CODE,PERIOD_TYPE_CODE,
IS_PLAN_OF_RECORD,PLAN_BY_1_CODE,PLAN_BY_2_CODE,START_DATE,FINISH_DATE,(SSL.START_DATE-SSL.FINISH_DATE)) UNIT,
(SELECT ENTITY_CODE,INV.CODE "Investment ID",INV.NAME "Investment Name",INV.ODF_OBJECT_CODE "Investment Type",PLAN.CODE "PlanID",PLAN.NAME "Plan Name",PLAN.PLAN_TYPE_CODE "Type of Plan",PERIOD_TYPE_CODE "Period Type"
,IS_PLAN_OF_RECORD "Plan of Record",PLAN_BY_1_CODE,PLAN_BY_2_CODE,SSL.START_DATE,SSL.FINISH_DATE,ROUND((SSL.FINISH_DATE-SSL.START_DATE)*SUM(SSL.SLICE),2) SLICE FROM ODF_SSL_CST_DTL_REV SSL, INV_INVESTMENTS INV, FIN_PLANS PLAN,FIN_COST_PLAN_DETAILS DETAILS WHERE INV.CODE = '09000002' AND INV.ODF_OBJECT_CODE = 'project' AND INV.ID = PLAN.OBJECT_ID ANDPLAN.ID = DETAILS.PLAN_ID AND DETAILS.ID = SSL.PRJ_OBJECT_ID AND PLAN_TYPE_CODE = 'FORECAST'
AND IS_PLAN_OF_RECORD=1 GROUP BY ENTITY_CODE,INV.CODE,INV.NAME,INV.ODF_OBJECT_CODE,PLAN.CODE,PLAN.NAME,PLAN.PLAN_TYPE_CODE,PERIOD_TYPE_CODE,
IS_PLAN_OF_RECORD,START_DATE,FINISH_DATE,PLAN_BY_1_CODE,PLAN_BY_2_CODE,(SSL.START_DATE-SSL.FINISH_DATE)) REVENUE,
(SELECT LOOKUP_CODE,NAME FROM CMN_LOOKUPS_V WHERE LOOKUP_TYPE='FIN_PLAN_BY_TYPE' AND LANGUAGE_CODE='en') GRP_BY,
(SELECT LOOKUP_CODE,NAME FROM CMN_LOOKUPS_V WHERE LOOKUP_TYPE='FIN_PLAN_BY_TYPE' AND LANGUAGE_CODE='en') SUB_GRP_BY,
ENTITY ENT,BIZ_COM_PERIODS BIZ
WHERE COST."Plan ID"=unit."Plan ID"(+) and cost.start_date=unit.start_date(+)
AND COST."Plan ID"=REVENUE."Plan ID"(+) and cost.start_date=REVENUE.start_date(+)
AND GRP_BY.LOOKUP_CODE =COST.PLAN_BY_1_CODE AND SUB_GRP_BY.LOOKUP_CODE=COST.PLAN_BY_2_CODE AND COST.ENTITY_CODE=ENT.ENTITY
AND BIZ.START_DATE=COST.START_DATE AND BIZ.ENTITY_ID=ENT.ID    -- Benefit plan details (Total Benefit, Actual Benefit, Billing Benefit) based on month (Group by Month)  SELECT INV.CODE "Investment ID",INV.NAME "Investment Name",INV.ODF_OBJECT_CODE "Investment Type",PLAN.CODE "Plan
ID",PLAN.NAME "Plan Name",NVL(PLAN.PLAN_TYPE_CODE,'Benefit') "Type of Plan",PERIOD_TYPE_CODE "Period Type",
PERIOD1.PERIOD_NAME "Period Name",SSL.BENEFIT "Benefit",SSL.ACTUAL_BENEFIT "Actual Benefit", SSL.VARAINCE "Variance"
FROM (SELECT BFT.PRJ_OBJECT_ID,BFT.START_DATE,ROUND((BFT.FINISH_DATE-BFT.START_DATE)*BFT.SLICE,2) BENEFIT,NVL(ROUND((AFBT.FINISH_DATE-AFBT.START_DATE)*AFBT.SLICE,2),0) ACTUAL_BENEFIT,
(ROUND((BFT.FINISH_DATE-BFT.START_DATE)*BFT.SLICE,2)-(NVL(ROUND((AFBT.FINISH_DATE-AFBT.START_DATE)*AFBT.SLICE,2),0)))VARAINCE FROM ODF_SSL_BFT_DTL_BFT BFT,ODF_SSL_BFT_DTL_ABFT AFBT WHERE BFT.START_DATE=AFBT.START_DATE(+)) SSL, INV_INVESTMENTSINV, FIN_PLANS PLAN, FIN_BENEFIT_PLAN_DETAILS DETAILS, ENTITY ENT,
BIZ_COM_PERIODS PERIOD,BIZ_COM_PERIODS PERIOD1 WHERE INV.ODF_OBJECT_CODE = 'project' AND INV.ID = PLAN.OBJECT_ID AND PLAN.ID= DETAILS.PLAN_ID AND
 DETAILS.ID = SSL.PRJ_OBJECT_ID AND PLAN_TYPE_CODE IS NULL AND PERIOD.ID=START_PERIOD_ID
 AND ENTITY_CODE=ENT.ENTITY AND PERIOD1.START_DATE=SSL.START_DATE AND PERIOD1.ENTITY_ID=ENT.ID AND IS_PLAN_OF_RECORD=1 AND    INV.CODE = '09000002'    -- Budget plan details (Total Unit, Total Cost, Total Revenue) based on month (Group by Month)SELECT COST."Investment ID",COST."Investment Name",COST."Investment Type",COST."Plan ID",COST."Plan Name",COST."Type ofPlan",COST."Period Type",COST."Plan of Record",
GRP_BY.NAME "Group By",SUB_GRP_BY.NAME "Sub Group By",BIZ.PERIOD_NAME,nvl(UNIT.SLICE,0) "Units", nvl(COST.SLICE,0) "Cost",nvl(REVENUE.SLICE,0) "Revenue" FROM
(SELECT INV.CODE "Investment ID",ENTITY_CODE,INV.NAME "Investment Name",INV.ODF_OBJECT_CODE "Investment Type",PLAN.CODE "PlanID",PLAN.NAME "Plan Name",PLAN.PLAN_TYPE_CODE "Type of Plan",PERIOD_TYPE_CODE "Period Type"
,IS_PLAN_OF_RECORD "Plan of Record",PLAN_BY_1_CODE,PLAN_BY_2_CODE,SSL.START_DATE,SSL.FINISH_DATE,ROUND((SSL.FINISH_DATE-SSL.START_DATE)*SUM(SSL.SLICE),2) SLICE FROM ODF_SSL_CST_DTL_COST SSL, INV_INVESTMENTS INV, FIN_PLANS PLAN,FIN_COST_PLAN_DETAILS DETAILS WHERE INV.CODE = '09000002' AND INV.ODF_OBJECT_CODE = 'project' AND INV.ID = PLAN.OBJECT_ID ANDPLAN.ID = DETAILS.PLAN_ID AND DETAILS.ID = SSL.PRJ_OBJECT_ID AND PLAN_TYPE_CODE = 'BUDGET'
AND IS_PLAN_OF_RECORD=1 GROUP BY INV.CODE,INV.NAME,INV.ODF_OBJECT_CODE,PLAN.CODE,PLAN.NAME,PLAN.PLAN_TYPE_CODE,PERIOD_TYPE_CODE,
IS_PLAN_OF_RECORD,ENTITY_CODE,START_DATE,FINISH_DATE,PLAN_BY_2_CODE,(SSL.START_DATE-SSL.FINISH_DATE),PLAN_BY_1_CODE) COST,
(SELECT INV.CODE "Investment ID",ENTITY_CODE,INV.NAME "Investment Name",INV.ODF_OBJECT_CODE "Investment Type",PLAN.CODE "PlanID",PLAN.NAME "Plan Name",PLAN.PLAN_TYPE_CODE "Type of Plan",PERIOD_TYPE_CODE "Period Type"
,IS_PLAN_OF_RECORD "Plan of Record",PLAN_BY_1_CODE,PLAN_BY_2_CODE,SSL.START_DATE,SSL.FINISH_DATE,ROUND((SSL.FINISH_DATE-SSL.START_DATE)*SUM(SSL.SLICE),2) SLICE FROM ODF_SSL_CST_DTL_UNITS SSL, INV_INVESTMENTS INV, FIN_PLANS PLAN,FIN_COST_PLAN_DETAILS DETAILS WHERE INV.CODE = '09000002' AND INV.ODF_OBJECT_CODE = 'project' AND INV.ID = PLAN.OBJECT_ID ANDPLAN.ID = DETAILS.PLAN_ID AND DETAILS.ID = SSL.PRJ_OBJECT_ID AND PLAN_TYPE_CODE = 'BUDGET'
AND IS_PLAN_OF_RECORD=1 GROUP BY ENTITY_CODE,INV.CODE,INV.NAME,INV.ODF_OBJECT_CODE,PLAN.CODE,PLAN.NAME,PLAN.PLAN_TYPE_CODE,PERIOD_TYPE_CODE,
IS_PLAN_OF_RECORD,PLAN_BY_1_CODE,PLAN_BY_2_CODE,START_DATE,FINISH_DATE,(SSL.START_DATE-SSL.FINISH_DATE)) UNIT,
(SELECT ENTITY_CODE,INV.CODE "Investment ID",INV.NAME "Investment Name",INV.ODF_OBJECT_CODE "Investment Type",PLAN.CODE "PlanID",PLAN.NAME "Plan Name",PLAN.PLAN_TYPE_CODE "Type of Plan",PERIOD_TYPE_CODE "Period Type"
,IS_PLAN_OF_RECORD "Plan of Record",PLAN_BY_1_CODE,PLAN_BY_2_CODE,SSL.START_DATE,SSL.FINISH_DATE,ROUND((SSL.FINISH_DATE-SSL.START_DATE)*SUM(SSL.SLICE),2) SLICE FROM ODF_SSL_CST_DTL_REV SSL, INV_INVESTMENTS INV, FIN_PLANS PLAN,FIN_COST_PLAN_DETAILS DETAILS WHERE INV.CODE = '09000002' AND INV.ODF_OBJECT_CODE = 'project' AND INV.ID = PLAN.OBJECT_ID ANDPLAN.ID = DETAILS.PLAN_ID AND DETAILS.ID = SSL.PRJ_OBJECT_ID AND PLAN_TYPE_CODE = 'BUDGET'
AND IS_PLAN_OF_RECORD=1 GROUP BY ENTITY_CODE,INV.CODE,INV.NAME,INV.ODF_OBJECT_CODE,PLAN.CODE,PLAN.NAME,PLAN.PLAN_TYPE_CODE,PERIOD_TYPE_CODE,
IS_PLAN_OF_RECORD,START_DATE,FINISH_DATE,PLAN_BY_1_CODE,PLAN_BY_2_CODE,(SSL.START_DATE-SSL.FINISH_DATE)) REVENUE,
(SELECT LOOKUP_CODE,NAME FROM CMN_LOOKUPS_V WHERE LOOKUP_TYPE='FIN_PLAN_BY_TYPE' AND LANGUAGE_CODE='en') GRP_BY,
(SELECT LOOKUP_CODE,NAME FROM CMN_LOOKUPS_V WHERE LOOKUP_TYPE='FIN_PLAN_BY_TYPE' AND LANGUAGE_CODE='en') SUB_GRP_BY,
ENTITY ENT,BIZ_COM_PERIODS BIZ
WHERE COST."Plan ID"=unit."Plan ID"(+) and cost.start_date=unit.start_date(+)
AND COST."Plan ID"=REVENUE."Plan ID"(+) and cost.start_date=REVENUE.start_date(+)
AND GRP_BY.LOOKUP_CODE =COST.PLAN_BY_1_CODE AND SUB_GRP_BY.LOOKUP_CODE=COST.PLAN_BY_2_CODE AND COST.ENTITY_CODE=ENT.ENTITY
AND BIZ.START_DATE=COST.START_DATE AND BIZ.ENTITY_ID=ENT.ID    ThanksSenthil.      

Hi Senthil,

is there a way to get actuals thats in the cost plan and budgets

Thanks,

ray

Hi                     For testing purpose, i have hard coded in Investment code for the following code. if you want to fetch the plan details for all project, just remove inv.code for the following code. the below code might be very useful for you.  -- Cost plan details (Total Benefit, Actual Benefit, Billing Benefit) based on group by and sub group by attribute  SELECT COST."Investment ID",COST."Investment Name",COST."Investment Type",COST."Plan ID",COST."Plan Name",COST."Type ofPlan",COST."Period Type",COST."Plan of Record",
GRP_BY.NAME "Group By",SUB_GRP_BY.NAME "Sub Group By",TRANS.TRANSCLASS,code.PRNAME "Charge Code",BIZ.PERIOD_NAME,nvl(UNIT.SLICE,0) "Units", nvl(COST.SLICE,0) "Cost", nvl(REVENUE.SLICE,0) "Revenue" FROM
(SELECT PRJ_OBJECT_ID,TRANSCLASS_ID,PRCHARGECODE_ID,INV.CODE "Investment ID",ENTITY_CODE,INV.NAME "InvestmentName",INV.ODF_OBJECT_CODE "Investment Type",PLAN.CODE "Plan ID",PLAN.NAME "Plan Name",PLAN.PLAN_TYPE_CODE "Type ofPlan",PERIOD_TYPE_CODE "Period Type"
,IS_PLAN_OF_RECORD "Plan of Record",PLAN_BY_1_CODE,PLAN_BY_2_CODE,SSL.START_DATE,SSL.FINISH_DATE,ROUND((SSL.FINISH_DATE-SSL.START_DATE)*SUM(SSL.SLICE),2) SLICE FROM ODF_SSL_CST_DTL_COST SSL, INV_INVESTMENTS INV, FIN_PLANS PLAN,FIN_COST_PLAN_DETAILS DETAILS WHERE INV.CODE = '09000002' AND INV.ODF_OBJECT_CODE = 'project' AND INV.ID = PLAN.OBJECT_ID ANDPLAN.ID = DETAILS.PLAN_ID AND DETAILS.ID = SSL.PRJ_OBJECT_ID AND PLAN_TYPE_CODE = 'FORECAST'
AND IS_PLAN_OF_RECORD=1 GROUP BY INV.CODE,INV.NAME,INV.ODF_OBJECT_CODE,PLAN.CODE,PLAN.NAME,PLAN.PLAN_TYPE_CODE,PERIOD_TYPE_CODE,
IS_PLAN_OF_RECORD,ENTITY_CODE,START_DATE,FINISH_DATE,PLAN_BY_2_CODE,(SSL.START_DATE-SSL.FINISH_DATE),PLAN_BY_1_CODE,PRJ_OBJECT_ID,TRANSCLASS_ID,PRCHARGECODE_ID)
 COST,
(SELECT PRJ_OBJECT_ID,TRANSCLASS_ID,PRCHARGECODE_ID,INV.CODE "Investment ID",ENTITY_CODE,INV.NAME "InvestmentName",INV.ODF_OBJECT_CODE "Investment Type",PLAN.CODE "Plan ID",PLAN.NAME "Plan Name",PLAN.PLAN_TYPE_CODE "Type ofPlan",PERIOD_TYPE_CODE "Period Type"
,IS_PLAN_OF_RECORD "Plan of Record",PLAN_BY_1_CODE,PLAN_BY_2_CODE,SSL.START_DATE,SSL.FINISH_DATE,ROUND((SSL.FINISH_DATE-SSL.START_DATE)*SUM(SSL.SLICE),2) SLICE FROM ODF_SSL_CST_DTL_UNITS SSL, INV_INVESTMENTS INV, FIN_PLANS PLAN,FIN_COST_PLAN_DETAILS DETAILS WHERE INV.CODE = '09000002' AND INV.ODF_OBJECT_CODE = 'project' AND INV.ID = PLAN.OBJECT_ID ANDPLAN.ID = DETAILS.PLAN_ID AND DETAILS.ID = SSL.PRJ_OBJECT_ID AND PLAN_TYPE_CODE = 'FORECAST'
AND IS_PLAN_OF_RECORD=1 GROUP BY ENTITY_CODE,INV.CODE,INV.NAME,INV.ODF_OBJECT_CODE,PLAN.CODE,PLAN.NAME,PLAN.PLAN_TYPE_CODE,PERIOD_TYPE_CODE,
IS_PLAN_OF_RECORD,PLAN_BY_1_CODE,PLAN_BY_2_CODE,START_DATE,FINISH_DATE,(SSL.START_DATE-SSL.FINISH_DATE),PRJ_OBJECT_ID,TRANSCLASS_ID,PRCHARGECODE_ID) UNIT,
(SELECT PRJ_OBJECT_ID,TRANSCLASS_ID,PRCHARGECODE_ID,ENTITY_CODE,INV.CODE "Investment ID",INV.NAME "InvestmentName",INV.ODF_OBJECT_CODE "Investment Type",PLAN.CODE "Plan ID",PLAN.NAME "Plan Name",PLAN.PLAN_TYPE_CODE "Type ofPlan",PERIOD_TYPE_CODE "Period Type"
,IS_PLAN_OF_RECORD "Plan of Record",PLAN_BY_1_CODE,PLAN_BY_2_CODE,SSL.START_DATE,SSL.FINISH_DATE,ROUND((SSL.FINISH_DATE-SSL.START_DATE)*SUM(SSL.SLICE),2) SLICE FROM ODF_SSL_CST_DTL_REV SSL, INV_INVESTMENTS INV, FIN_PLANS PLAN,FIN_COST_PLAN_DETAILS DETAILS WHERE INV.CODE = '09000002' AND INV.ODF_OBJECT_CODE = 'project' AND INV.ID = PLAN.OBJECT_ID ANDPLAN.ID = DETAILS.PLAN_ID AND DETAILS.ID = SSL.PRJ_OBJECT_ID AND PLAN_TYPE_CODE = 'FORECAST'
AND IS_PLAN_OF_RECORD=1 GROUP BY ENTITY_CODE,INV.CODE,INV.NAME,INV.ODF_OBJECT_CODE,PLAN.CODE,PLAN.NAME,PLAN.PLAN_TYPE_CODE,PERIOD_TYPE_CODE,
IS_PLAN_OF_RECORD,START_DATE,FINISH_DATE,PLAN_BY_1_CODE,PLAN_BY_2_CODE,(SSL.START_DATE-SSL.FINISH_DATE),PRJ_OBJECT_ID,TRANSCLASS_ID,PRCHARGECODE_ID) REVENUE,
(SELECT LOOKUP_CODE,NAME FROM CMN_LOOKUPS_V WHERE LOOKUP_TYPE='FIN_PLAN_BY_TYPE' AND LANGUAGE_CODE='en') GRP_BY,
(SELECT LOOKUP_CODE,NAME FROM CMN_LOOKUPS_V WHERE LOOKUP_TYPE='FIN_PLAN_BY_TYPE' AND LANGUAGE_CODE='en') SUB_GRP_BY,
ENTITY ENT,BIZ_COM_PERIODS BIZ, TRANSCLASS TRANS, PRCHARGECODE CODE
WHERE COST."Plan ID"=unit."Plan ID"(+) and cost.start_date=unit.start_date(+)
AND COST."Plan ID"=REVENUE."Plan ID"(+) and cost.start_date=REVENUE.start_date(+)
AND GRP_BY.LOOKUP_CODE =COST.PLAN_BY_1_CODE AND SUB_GRP_BY.LOOKUP_CODE=COST.PLAN_BY_2_CODE AND COST.ENTITY_CODE=ENT.ENTITY
AND BIZ.START_DATE=COST.START_DATE AND BIZ.ENTITY_ID=ENT.ID AND TRANS.ID=COST.TRANSCLASS_ID ANDCODE.PRID=COST.PRCHARGECODE_ID
ORDER BY PERIOD_NAME  -- Budget plan details (Total Benefit, Actual Benefit, Billing Benefit) based on group by and sub group by attribute  SELECT COST."Investment ID",COST."Investment Name",COST."Investment Type",COST."Plan ID",COST."Plan Name",COST."Type ofPlan",COST."Period Type",COST."Plan of Record",
GRP_BY.NAME "Group By",SUB_GRP_BY.NAME "Sub Group By",TRANS.TRANSCLASS,code.PRNAME "Charge Code",BIZ.PERIOD_NAME,nvl(UNIT.SLICE,0) "Units", nvl(COST.SLICE,0) "Cost", nvl(REVENUE.SLICE,0) "Revenue" FROM
(SELECT PRJ_OBJECT_ID,TRANSCLASS_ID,PRCHARGECODE_ID,INV.CODE "Investment ID",ENTITY_CODE,INV.NAME "InvestmentName",INV.ODF_OBJECT_CODE "Investment Type",PLAN.CODE "Plan ID",PLAN.NAME "Plan Name",PLAN.PLAN_TYPE_CODE "Type ofPlan",PERIOD_TYPE_CODE "Period Type"
,IS_PLAN_OF_RECORD "Plan of Record",PLAN_BY_1_CODE,PLAN_BY_2_CODE,SSL.START_DATE,SSL.FINISH_DATE,ROUND((SSL.FINISH_DATE-SSL.START_DATE)*SUM(SSL.SLICE),2) SLICE FROM ODF_SSL_CST_DTL_COST SSL, INV_INVESTMENTS INV, FIN_PLANS PLAN,FIN_COST_PLAN_DETAILS DETAILS WHERE INV.CODE = '09000002' AND INV.ODF_OBJECT_CODE = 'project' AND INV.ID = PLAN.OBJECT_ID ANDPLAN.ID = DETAILS.PLAN_ID AND DETAILS.ID = SSL.PRJ_OBJECT_ID AND PLAN_TYPE_CODE = 'BUDGET'
AND IS_PLAN_OF_RECORD=1 GROUP BY INV.CODE,INV.NAME,INV.ODF_OBJECT_CODE,PLAN.CODE,PLAN.NAME,PLAN.PLAN_TYPE_CODE,PERIOD_TYPE_CODE,
IS_PLAN_OF_RECORD,ENTITY_CODE,START_DATE,FINISH_DATE,PLAN_BY_2_CODE,(SSL.START_DATE-SSL.FINISH_DATE),PLAN_BY_1_CODE,PRJ_OBJECT_ID,TRANSCLASS_ID,PRCHARGECODE_ID)
 COST,
(SELECT PRJ_OBJECT_ID,TRANSCLASS_ID,PRCHARGECODE_ID,INV.CODE "Investment ID",ENTITY_CODE,INV.NAME "InvestmentName",INV.ODF_OBJECT_CODE "Investment Type",PLAN.CODE "Plan ID",PLAN.NAME "Plan Name",PLAN.PLAN_TYPE_CODE "Type ofPlan",PERIOD_TYPE_CODE "Period Type"
,IS_PLAN_OF_RECORD "Plan of Record",PLAN_BY_1_CODE,PLAN_BY_2_CODE,SSL.START_DATE,SSL.FINISH_DATE,ROUND((SSL.FINISH_DATE-SSL.START_DATE)*SUM(SSL.SLICE),2) SLICE FROM ODF_SSL_CST_DTL_UNITS SSL, INV_INVESTMENTS INV, FIN_PLANS PLAN,FIN_COST_PLAN_DETAILS DETAILS WHERE INV.CODE = '09000002' AND INV.ODF_OBJECT_CODE = 'project' AND INV.ID = PLAN.OBJECT_ID ANDPLAN.ID = DETAILS.PLAN_ID AND DETAILS.ID = SSL.PRJ_OBJECT_ID AND PLAN_TYPE_CODE = 'BUDGET'
AND IS_PLAN_OF_RECORD=1 GROUP BY ENTITY_CODE,INV.CODE,INV.NAME,INV.ODF_OBJECT_CODE,PLAN.CODE,PLAN.NAME,PLAN.PLAN_TYPE_CODE,PERIOD_TYPE_CODE,
IS_PLAN_OF_RECORD,PLAN_BY_1_CODE,PLAN_BY_2_CODE,START_DATE,FINISH_DATE,(SSL.START_DATE-SSL.FINISH_DATE),PRJ_OBJECT_ID,TRANSCLASS_ID,PRCHARGECODE_ID) UNIT,
(SELECT PRJ_OBJECT_ID,TRANSCLASS_ID,PRCHARGECODE_ID,ENTITY_CODE,INV.CODE "Investment ID",INV.NAME "InvestmentName",INV.ODF_OBJECT_CODE "Investment Type",PLAN.CODE "Plan ID",PLAN.NAME "Plan Name",PLAN.PLAN_TYPE_CODE "Type ofPlan",PERIOD_TYPE_CODE "Period Type"
,IS_PLAN_OF_RECORD "Plan of Record",PLAN_BY_1_CODE,PLAN_BY_2_CODE,SSL.START_DATE,SSL.FINISH_DATE,ROUND((SSL.FINISH_DATE-SSL.START_DATE)*SUM(SSL.SLICE),2) SLICE FROM ODF_SSL_CST_DTL_REV SSL, INV_INVESTMENTS INV, FIN_PLANS PLAN,FIN_COST_PLAN_DETAILS DETAILS WHERE INV.CODE = '09000002' AND INV.ODF_OBJECT_CODE = 'project' AND INV.ID = PLAN.OBJECT_ID ANDPLAN.ID = DETAILS.PLAN_ID AND DETAILS.ID = SSL.PRJ_OBJECT_ID AND PLAN_TYPE_CODE = 'BUDGET'
AND IS_PLAN_OF_RECORD=1 GROUP BY ENTITY_CODE,INV.CODE,INV.NAME,INV.ODF_OBJECT_CODE,PLAN.CODE,PLAN.NAME,PLAN.PLAN_TYPE_CODE,PERIOD_TYPE_CODE,
IS_PLAN_OF_RECORD,START_DATE,FINISH_DATE,PLAN_BY_1_CODE,PLAN_BY_2_CODE,(SSL.START_DATE-SSL.FINISH_DATE),PRJ_OBJECT_ID,TRANSCLASS_ID,PRCHARGECODE_ID) REVENUE,
(SELECT LOOKUP_CODE,NAME FROM CMN_LOOKUPS_V WHERE LOOKUP_TYPE='FIN_PLAN_BY_TYPE' AND LANGUAGE_CODE='en') GRP_BY,
(SELECT LOOKUP_CODE,NAME FROM CMN_LOOKUPS_V WHERE LOOKUP_TYPE='FIN_PLAN_BY_TYPE' AND LANGUAGE_CODE='en') SUB_GRP_BY,
ENTITY ENT,BIZ_COM_PERIODS BIZ, TRANSCLASS TRANS, PRCHARGECODE CODE
WHERE COST."Plan ID"=unit."Plan ID"(+) and cost.start_date=unit.start_date(+)
AND COST."Plan ID"=REVENUE."Plan ID"(+) and cost.start_date=REVENUE.start_date(+)
AND GRP_BY.LOOKUP_CODE =COST.PLAN_BY_1_CODE AND SUB_GRP_BY.LOOKUP_CODE=COST.PLAN_BY_2_CODE AND COST.ENTITY_CODE=ENT.ENTITY
AND BIZ.START_DATE=COST.START_DATE AND BIZ.ENTITY_ID=ENT.ID AND TRANS.ID=COST.TRANSCLASS_ID ANDCODE.PRID=COST.PRCHARGECODE_ID
ORDER BY PERIOD_NAME    ThanksSenthil

If you query the slice requests you get among others  197       DAILY_INVESTMENT_COST_PLAN       33       ODF_SL_PLAN_COST_D
198       WEEKLY_INVESTMENT_COST_PLAN       33       ODF_SL_PLAN_COST_W
199       MONTHLY_INVESTMENT_COST_PLAN       33       ODF_SL_PLAN_COST_M
200       financials::plan_benefit::default       34       ODF_SL_PLAN_BENEFIT
201       DAILY_INVESTMENT_BENEFIT_PLAN       34       ODF_SL_PLAN_BENEFIT_D
202       WEEKLY_INVESTMENT_BENEFIT_PLAN       34       ODF_SL_PLAN_BENEFIT_W
203       MONTHLY_INVESTMENT_BENEFIT_PLAN       34       ODF_SL_PLAN_BENEFIT_M  The Clarity ODF SL Slice Tables for Financial Planning KB article #8826.00000 Doc Type [FAQ] Last Reviewed 10/02/2007Subject: 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.0Question:
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 relationshipThe 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 valuesBelow 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
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 OracleSELECT 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
;More Information:
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.   

Hello, MarttiK.
I have a problem that you might be able to help.
Thanks to all on this thread, the following code is working. but we require the primary GROUP BY attribute
(Transaction Class at ITW) to sort the results of this SQL. I’m having trouble finding the table and attribute that
specifies the primary grouping of cost plan details. Please advise if you know where this attribute resides.
SQL:
SELECT p.NAME, fin.TOTAL_COST,
cst.start_date,
(cst.slice*convert(bigint,(cst.finish_date - cst.start_date))) as CostValue
FROM INV_INVESTMENTS p
left join fin_plans fin
left join fin_cost_plan_details dtl
LEFT JOIN niku.ODF_SSL_CST_DTL_COST cst
on cst.prj_object_id = dtl.id
on dtl.PLAN_ID = fin.id
on fin.OBJECT_ID = p.ID
WHERE
fin.plan_type_code = 'BUDGET'
and fin.is_plan_of_record = 1
and p.code = 'PRJ9076'
order by p.name, cst.start_date

------------------------------
Results:
NAME TOTAL_COST start.date CostValue
CAPPM Support
80805.52
1/1/2011 12:00:00 AM
6839.999991
CAPPM Support
80805.52
1/1/2011 12:00:00 AM
49.999993
CAPPM Support
80805.52
1/1/2011 12:00:00 AM
150.00001
CAPPM Support
80805.52
2/1/2011 12:00:00 AM
100.000012
CAPPM Support
80805.52
2/1/2011 12:00:00 AM
49.999992
CAPPM Support
80805.52
2/1/2011 12:00:00 AM
49.999992
CAPPM Support
80805.52
2/1/2011 12:00:00 AM
6384
CAPPM Support
<….>

Hi Steve!

fin_cost_plan_details.PLAN_DETAIL_1_KEY

gives the attribute for group by, along with the 'internal' id of that value

for example:
LOOKUP_CHARGE_CODES:5000001

In your query use a substring function to get the id 5000001, it's the prchargecode.prid; if it's a transaction class it's the transclass.id

then similarly -

fin_cost_plan_details.PLAN_DETAIL_2_KEY gives you the sub group by if any.

finally -
fin_cost_plan_details.plan_id = fin_plans.id

hope this helps :)

Connie :smile

Hello, Connie, and all. Amazing.

I just met this information again. And used it to solve a new problem. 4 years and still producing value.  Maybe it's time we started collecting "residual answer" statistics.

Chris: How about: a new button, "Answered Again", or "Helped Me Too", that we can click when the CGUC Message Board saves another call to Support. 

In any case. Many thanks, Connie.  You're a treasure, again and again.

Thanks for the repsonses - very much appreciated..   Gonna need a few days to digest all that SQL and try them out.Regards,BeekerC

Thanks to all for the responses.couple of things.....- I'm seeing ODF_SL_COST_ and ODF_SSL_COST_, but   my tables are ODF_SL_CST - I'm assuming this is just a version issue, or should I be concerned that my database is out of whack?  - I've configured the Datamart and set up time slicing.   I've run Time Slices, Datamart Extract and Datamart Rollup, i query the ODF_SL_CST_ tables and get nothing.  I do have cost plans in the system, which I can XOG out with no problem.   Am i missing something that's preventing me from having the cost plans sliced?  ThanksBeekerC

Hi - I don't use cost plans so I shall not comment on all those tables...  ....but I do use TIMESLICES a lot; the timeslicing job does not put data in those ODF_SL (etc) tables, it populates the PRJ_BLB_SLICES table (and some other similarily named ones).  You go to the PRJ_BLB_SLICES table with the slice_request_id matching the slice config that you set up (i.e. the id's in Martti's post above) and then the linking "object id" (PRJ_OBJECT_ID) depends on the slice type but links back to the base data (i.e. to the record with the BLOB field in it).  Hope that helps (a bit)      

Hi  We experienced the issue of the ODF_SL* tables not populating with data earlier in the year and CA have confirmed it's a bug in 8.1:  CLRT-36944  Financial  slices  from  custom  slice  requests  in  the
odf_sl_plan_cost  table  not  being  updated

Steps  to  reproduce
===============
1.Go  to  Admin  ->  Data  Administration  ->  Time  Slices  and  create  a  custom  slice
request  based  on  the
project:detailed  planned  cost  or  investment  cost  plan  item.
You  can  use  the  following  details
rollover  interval:Monthly
from  date  :  1/1/09
slice  period:Monthly
number  of  periods  12
2.  Ensure  that  there  are  existing  cost  plan  information  in  the  database  if
not  create  cost  plans  and  populate  the  details  with  cost  information  .
3.Run  the  time  slicing  job
4.  Check  the  odf_sl_plan_cost  table,  this  should  be  populated  with  existing
cost  plan  slices  for  the  new  slice  request
5.  After  a  few  days  create  more  cost  plans  ,  populate  the  cost  plan  details
with  cost  information  ,  run  the  time  slicing  job  and  check  the
odf_sl_plan_cost  table  again

Expected  Results:  The  slice  request  in  the  odf_sl_plan_cost  table  should  be
updated  with  new  values
Actual  Results:  The  odf_sl_plan_cost  table  does  not  get  updated

Workaround:
Go  to  the  UI  and  edit  the  custom  slice  request  and  submit  it  ,  this  should
recreate  the  slice  data  when  the  time  slicing  job  runs
Or
Run  the  following  update  statement  to  recreate  the  slice  request  and  then  run
the  time  slicing  job
update  prj_blb_slicerequests
set  request_completed_date  =  NULL
where  request_name  like  'Insert  the  slice  request  name  here'

This  bug  is  open  with  development  and  should  be  fixed  in  a  future  version====================================================================  From a reporting perspective we get round it by using queries such as the ones earlier in the post and creating dynamic columns if we need  views of cost by period.  Hope this helps.

OK Owen shows what I know (or not) about ODF_SL_ tables, listen to him then not me!   :-)