Clarity

  • 1.  Project budget/forecast cost

    Posted Dec 27, 2010 06:30 AM
    Hi All,

    I am using clarity v12.0. I want to get Budget/Forecast cost(monthly) details for each project against transaction classes for that project. Can someone help me out with it and also please tell as to what data(Monthly/Weekly/Daily) is stored in "SLICE" field of ODF_SSL_CST_DTL_COST table.

    Please revert if any doubts regarding my question.

    Thanks & Regards
    Praveen


  • 2.  RE: Project budget/forecast cost

    Posted Dec 28, 2010 02:29 AM
    Hi,

    Look at this messages below hope that will answer your question.

    2280235

    2293051

    2293051

    cheers,
    sundar


  • 3.  RE: Project budget/forecast cost
    Best Answer

    Posted Dec 28, 2010 10:56 PM
    Thanks for the response. I have gone through these posts before posting this thread, never mind I will got through them again and see if I can get something.

    I have a query that gives me total budget/forecast info group by transaction classes for each project. But, I am gonna need a monthly break down of costs against transaction classes for each project.
    SELECT
    I.NAME PROJECT,T.DESCRIPTION,SUM(FD.TOTAL_COST)
    FROM
    INV_INVESTMENTS I,FIN_PLANS FP,FIN_COST_PLAN_DETAILS FD,TRANSCLASS T
    WHERE
    I.ID=FP.OBJECT_ID AND FP.ID=FD.PLAN_ID AND FD.TRANSCLASS_ID=T.ID AND FP.PLAN_TYPE_CODE='BUDGET' AND FP.IS_PLAN_OF_RECORD=1 AND 
    GROUP BY I.NAME,T.DESCRIPTION
    Regards
    Praveen


  • 4.  RE: Project budget/forecast cost

    Posted Jan 03, 2011 01:05 AM
    Try this.

    <Code>

    SELECT sum(ROUND (NVL(cslice.slice,0) * (NVL(cslice.finish_date,SYSDATE) - NVL(cslice.start_date,SYSDATE)),3)) costdata,
    tc.transclass ,i.code proj,to_char(cslice.START_DATE,'Mon-yyyy') mnth
    FROM FIN_COST_PLAN_DETAILS FD inner join FIN_PLANS FP on fp.id = fd.PLAN_ID
    inner join INV_INVESTMENTS I on i.ID = fp.OBJECT_ID
    LEFT OUTER JOIN TRANSCLASS TC ON TC.ID = FD.TRANSCLASS_ID
    LEFT OUTER JOIN odf_ssl_cst_dtl_cost cslice ON cslice.PRJ_OBJECT_ID = FD.ID
    group by transclass,i.code,to_char(cslice.START_DATE,'Mon-yyyy')

    <Code>


  • 5.  RE: Project budget/forecast cost

    Posted Jan 03, 2011 07:46 PM
    Hi Praveen,

    Just want to add a couple of things to the query written by Gauri,

    Please consider joining with BIZ_COM_PERIODS table to get the month name using the PERIOD column.

    Another important use of joining with BIZ_COM_PERIOD is, you should check if your BIZ_COM_PERIOD START_DATE and ODF_SSL_CST_DTL_COST START_DATE are matching, this determines if your Slice data are matching if you are using an external interface to XOG in the Cost Plan details.

    Which database you are dealing with, SQL Server or Oracle

    If you are using SQL Server, then

    To get the cost data , try using isnull(ROUND(cslice.SLICE*(DATEDIFF(DAY, cslice.START_DATE, cslice.FINISH_DATE)),2),0) in the place of
    sum(ROUND (NVL(cslice.slice,0) * (NVL(cslice.finish_date,SYSDATE) - NVL(cslice.start_date,SYSDATE)),3)) costdata


    Please let me know you need any help in writing the query.

    Regards,
    Mathan