Clarity

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!

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

Hi Srestha,

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

Thanks!
Chris