Clarity

Expand all | Collapse all

Cost Plan (Forecast) FTE details

Jump to Best Answer
  • 1.  Cost Plan (Forecast) FTE details

    Posted 06-21-2012 05:48 AM
    Hi,

    We are maintaining only FTE details along with Roles(Group By) in cost plan (forecast) in clarity like the following structure (where PMs can enter FTE numbers directly)

    Group By
    FTEs Jan 12
    Feb 12
    Mar 12
    Apr 12
    May 12
    Jun 12
    Jul 12
    Aug 12
    Sep 12
    Oct 12
    Nov 12
    Dec 12

    Where FTEs coming from a Time-varying attribute.
    Can anyone please let me know whether we have any option to extract plan of record cost plan directly (against all active Investments) from Clarity data model in above format (like export to excel)

    Note: I do not want to XOG out the records (because I have already created an application which can create new/ update existing cost plans where raw data coming from excel).

    Awaiting reply. Thanks in advance!


  • 2.  RE: Cost Plan (Forecast) FTE details
    Best Answer

    Posted 06-21-2012 09:13 AM
    Hi,

    It is possible.
    you can build an query in your DB the same format like the GUI - Cost plan.Then you can export from Toad or whatever tool as excel.

    sample one-->you need to modify the query to bring the cost plan - monthwise FTE - Plan of record only.

    SELECT
    inv.id project_id, inv.code project_code, fpd.id,
    case when NLS_UPPER(PLAN_TYPE_CODE) ='BUDGET' THEN 'APPROVED' ELSE NLS_UPPER(PLAN_TYPE_CODE) END Lookup,
    scb_plan_type,
    prchr.prexternalid chargecode, trans.TRANSTYPE TRANSTYPE, trans.transclass transclass,
    to_date(fcd.start_date,'dd-mon-rr') startdate, to_date(fcd.finish_date,'dd-mon-rr')-1 enddate, fpd.last_updated_date,
    fp.revision revision, 0 units,sum(fcd.slice)slice, NVL(SUM(ROUND((fcd.finish_Date-fcd.start_Date)*fcd.slice,2)),0) cost,
    sum(fpd.total_cost) total_cost
    FROM
    fin_cost_plan_details fpd
    LEFT OUTER JOIN odf_ssl_cst_dtl_cost fcd ON fpd.id = fcd.prj_object_id
    INNER JOIN fin_plans fp ON fpd.plan_id = fp.id
    INNER JOIN odf_ca_costplan odf_cst ON fpd.plan_id=odf_cst.id
    INNER JOIN inv_investments inv ON fp.object_id = inv.id
    INNER JOIN transclass trans ON fpd.transclass_id = trans.id
    INNER JOIN prchargecode prchr ON fpd.prchargecode_id = prchr.prid

    --INNER JOIN cmn_lookups cmn_lookup ON odf_cst.scb_plan_type=cmn_lookup.id
    WHERE
    NLS_UPPER(PLAN_TYPE_CODE) IN ('FORECAST') AND
    fp.is_plan_of_record=1 and inv.code = '11000513'
    GROUP BY
    inv.id,inv.code,fpd.id,scb_plan_type,fp.revision,fpd.last_updated_date,NLS_UPPER(PLAN_TYPE_CODE),transtype,transclass,fcd.start_Date,fcd.finish_Date,
    trans.shortdesc,trans.id,prchr.prexternalid


    cheers,
    sundar


  • 3.  RE: Cost Plan (Forecast) FTE details

    Posted 06-29-2012 03:51 PM
    Hi Srestha,

    Did Sundar's response help answer your question? If so please mark his post as Accepted Solution.

    Thanks!
    Chris