Clarity

  • 1.  Project / Program / Portfolio hierarchy query

    Posted Dec 08, 2009 10:57 AM
    Does anyone have, or can give me guidance to write, a SQL script (hopefully without recursion) that will allow me to start with a project, program or portfolio ID and return me a list of the child tree structure, as well as the parent tree structrure (only so far as it's related the selected item being a lowest child, with a direct path to the root?  I'm writing a process that needs to manually roll up figures across a hierarchy, so I'm starting with the script to determine what other projects, programs and portfolios need to be adjusted.  ThanksBeekerC


  • 2.  Re: Project / Program / Portfolio hierarchy query

    Posted Dec 08, 2009 12:05 PM
    While there may be more ellegant ways of getting at that info, here's one of the SQL queries I have for the Program/Project relationship: select   proj1.name as MasterProjName, proj1.code as MasterProjCode,                      proj2.name as SubProjName, proj2.code as SubProjCode
    from       prtask task
                      left join PRSUBPROJECT sub ON task.prid = sub.prtaskid                 left join inv_investments proj1 ON task.prprojectid = proj1.id
                      left join inv_investments proj2 ON sub.prrefprojectid = proj2.id
    where   proj2.code is not null
    order by proj1.code This can easily be tweaked to include additional substructures, or reversed for parent structure.   I have a few others that I use, depending on what I'm trying to do, but this is the first one I pulled.   Hope it's helpful.  Regards,   - Mark


  • 3.  Re: Project / Program / Portfolio hierarchy query

    Posted Dec 08, 2009 01:15 PM
    beekerc,  i'd suggest you use  inv_hierarchies........for the direct relationships,inv_hierarchies_flat..for project/programs parents/subs to all leaf and root nodes.  although i'm unaware of the portfolio records existence in these tables.


  • 4.  Re: Project / Program / Portfolio hierarchy query

    Posted Dec 09, 2009 12:08 AM
    Hi,         Project Hierarchy query. only odf_object_code will be change based on object type.    SELECT PAR.CODE "Project ID", PAR.NAME "Project Name", CHLD.CODE "Child Project ID", CHLD.NAME "Child Project Name" FROM
    INV_HIERARCHIES HIER, ODF_CA_INVESTMENTHIERARCHY ODF, INV_FLAT_HIERARCHIES FLAT,INV_INVESTMENTS PAR, INV_INVESTMENTS CHLD,ODF_CA_INVESTMENTALLOCATION ODF_ALLOC WHERE HIER.PARENT_ID = PAR.ID AND PAR.CODE = '090000001' AND PAR.ODF_OBJECT_CODE = 'project' AND HIER.ID = FLAT.ID AND FLAT.CHILD_ID = CHLD.ID AND HIER.ID = ODF.ID AND HIER.ID = ODF_ALLOC.ID    ThanksSenthil.


  • 5.  Re: Project / Program / Portfolio hierarchy query

    Posted Dec 22, 2009 08:27 AM
    Thanks so far, these are all useful scripts, but I still need some guidance to get to my final objective.Ultimately what I need is to be able to take a single project and determine every project that it's suboridnate to, every program that it's attached to and every portfoio that it belongs to.The portfolio piece is tricky in its own right because, utilizing PMA_PORTFOLIO_CONTENTS, you can see a project that's directly included or attached through the Include and Filter section, but if a program is attached, any projects under them don't appear as discrete projects in this table, nor does anything associated with child portfolios.The purpose of this is that I need to duplicate Clarity's abiltiy to roll-up figures from the lowest level sub-project to the highest level portfolio.   Clarity does this inherently with hours and actual costs.   I need to do this for several custom attributes, but I also also need it to be rolled up exactly like Clarity would do it and as yet, I haven't found a single algorithm that will, from a single project, determine the entire parental tree above it, as each one needs to be rolled-up into.Thanks in advanceB


  • 6.  Re: Project / Program / Portfolio hierarchy query

    Posted Dec 22, 2009 10:46 PM
    Beeker,
     
    we need an DB level relationship to achieve the Hierarchial output.The master and sub project projects should be related and then can be attached to any portfolio or Programs.So the basics is the relation ship between projects must be defined and then they need to be tagged under an Portfolio for analysis.Portfolio and Programmes are different when it comes to Hierarchy. (DB level,app level there may be many differences)  The difference is  Program --> we need to add the projects under "Sub Projects" tab of a program.It is really like an project (@ db level) only an condition difference (is_program=1 in inv_projects).The hierarchy query works similar to an project.  Portfolio --> we can add the investments (projects) to have an view the cost spend at an portfolio level.You need to create an scenario and add investments(projects).But only the projects added will be shown not the sub projects cost will be included.  Note:-   For creating a scenario in a portfolio the projects needs to be added manually under the portfolio in the application screen then only you can view the costs.  If A the master project is added in that portfolio only A cost will be shown.If you want to show all the sub projects cost under A then you need to add both B,C and D  in the scenario. (That's my understanding,may be wrong ).  Example:-

    If   "A" is the Master project   (1st level)   and "B" is the next level sub project (2nd Level - can call as Parent project) and finally "C" and "D" is the last Level (3rd level).
    so in clarity these related by adding in to the sub project tab in the respective projects.

    Now here you can write an Hierarchial Query.(normal ones)
    when you write an query using "Connect By" (oracle)   we can retrive the list of all sub projects under "A" and can retrieve all the projects above "C"   (A and B) and also
    if u want to see the projects under "B" also we can see("C and "D") .Now once an relation is made whether it is cost or units or any custom attribute values you canshow at an Roll up level ("A" ).Once the project id is provided "c" you can find the Master project   "A" too.
    Portfolio:-  Now when these projects is added in a portfolio (Say "ABC" portfolio) then the portfolio tables (PMA_PORTFOLIO_CONTENTS and PMA_PORTFOLIOS) are directly related to prj_projects (ER diagram). so you can retrieve the projects that are added in the portfolio.It will be shown automatically in the application-->portfolio.(Create scenorio and add projects).  You can also create a custom nsql based portlet and show the projects cost "A" at an Roll up level (portfolio level) (can also include the sub projects cost under the added projects cost) and show in the portlet. (here no need to add those sub projects in the portfolio scenorio --investments but the rollup cost will be shown in the portlet,but will that be any use?)  -- Programs--To retrieve the sub projects under an program and also view the cost,units,etc (Modify the query and use Group by )  SELECT pgm.name program,subproject.name subproject,t.prname,odf.*
    FROM
    inv_projects pgm,prtask t,prsubproject s,inv_projects sub ,odf_ca_project odf
    WHERE
    pgm.is_program = 1 and
    t.prprojectid = pgm.prid and
    t.prid = s.prtaskid and
    s.prrefprojectid = sub.prid and
    pgm.prid=odf.idIf there is an Hierarchical relationship between the projects then it can be added to an portfolio or programs and then we can show the consolidated cost   in reports /portlets.  cheers,sundar            


  • 7.  RE: Re: Project / Program / Portfolio hierarchy query

    Posted Apr 19, 2011 07:22 PM
    I am not sure if Beekar got his answer... can some one help in telling how we do the roll up in clarity ... the roll up of costs starting from investments to portfolios.


  • 8.  RE: Project / Program / Portfolio hierarchy query
    Best Answer

    Posted Sep 11, 2012 04:25 PM
    Just came upon this topic, looking to see if anyone else had solved a similar problem that we are facing. However, after reading this thread, a light bulb went on.

    If one wants to a hierarchical query/portlet to rollup costs in the same manner as the Project\Hierarchy tab, the best place to start is with an SQLTrace of this particular page\tab. With SQLTRACE_ACTIVE set to "10," the trace returns the SQL used, the parameter values and the resulting data. As the trace will return many queries, including those used for security/access, having the resulting data helps one find the particular query one is interested in - for instance, the query results will return the project names, so performing a 'find' for a known portion of a project name will skip one down in the trace results to the vicinty of the query we want.

    Doing this resulted in the following SQL. This is from Clarity 12.0.6/Oracle. Its quite large, but hierarchical queries can be large. User and investment IDs appearing in the following SQL have been redacted.

    I have not converted this yet into NSQL. Also, I have not yet analyzed or tested this yet, to see how many levels deep this code will go. In the example that I took this trace from, I had only 2 levels -- one program with 15 subprojects attached. My expectation is that the query that Clarity generates may be dependent on the number of layers found in a given hierarchy - meaning, the query for 2 levels will be different from a query for 3 levels. That will be my next test, to answer the question: Is the resulting query dependent on the number of levels found in a hierarchy? If the answer is yes, I'll probably take a query from an example where I use 6 levels - at most we have a few examples of 5 levels being used in practice, so a query that can return 6 levels should be sufficient for our portlet.

    Might also try this using the Subprojects page, as my user is not interested in the cost/effort rollup.

    Dale

    SELECT inv_hierarchies.id odf_pk , (case when (inv_hierarchies.default_alloc_pct*


    (select PMA_CALC_PV_COST_FOR_INV(inv_hierarchies.child_id) from dual)) > 0 THEN
    ((inv_hierarchies.default_alloc_pct*(select fin_financials.planned_npv
    from fin_financials, odf_object_instance_mapping
    where odf_object_instance_mapping.primary_object_instance_id = inv_hierarchies.child_id
    and odf_object_instance_mapping.secondary_object_instance_id = fin_financials.id


    and odf_object_instance_mapping.primary_object_instance_code =


    (select odf_object_code from inv_investments where inv_investments.id = inv_hierarchies.child_id)


    and odf_object_instance_mapping.secondary_object_instance_code = 'financials' ))


    /(inv_hierarchies.default_alloc_pct*(select PMA_CALC_PV_COST_FOR_INV(inv_hierarchies.child_id) from dual)))


    ELSE 0 end) inv_planned_roi
    , (inv_hierarchies.default_alloc_pct*(select PMA_CALC_PV_COST_FOR_INV(inv_hierarchies.child_id) from dual )) planned_pv_cost
    , inv_investments.CURRENCY_CODE currency_code
    , (SELECT q.currency_name FROM (SELECT c.currency_code currency_code,
    c.currency_code unique_code,
    c.currency_code currency_name
    FROM cmn_currencies c
    WHERE c.is_active = 1
    AND 1=1) q WHERE q.currency_code = inv_investments.CURRENCY_CODE) currency_code_caption
    , inv_hierarchies.DEFAULT_ALLOC_PCT default_alloc_pct
    , inv_investments.schedule_start schedule_start
    , inv_investments.STAGE_CODE stage_code
    , (SELECT name FROM cmn_lookups_v WHERE lookup_type = 'INV_STAGE_TYPE' AND language_code = 'en' AND lookup_code = inv_investments.STAGE_CODE) stage_code_caption
    , fin_financials.BUDGET_CST_FINISH budget_cst_finish
    , inv_investments.code unique_code
    , ( inv_hierarchies.default_alloc_pct
    * (SELECT NVL(SUM (b.totalcost),0)
    FROM ppa_wip a, ppa_wip_values b, inv_investments
    WHERE a.transno = b.transno
    AND b.currency_type = 'HOME'
    AND a.status = 0
    AND a.investment_id = inv_investments.id
    AND inv_investments.id = inv_hierarchies.child_id
    GROUP BY a.investment_id)
    ) aggr_actual_cost
    , inv_investments.PROGRESS progress
    , (SELECT name FROM cmn_lookups_v WHERE lookup_type = 'INVESTMENT_OBJ_PROGRESS' AND language_code = 'en' AND lookup_enum = inv_investments.PROGRESS) progress_caption
    , fin_financials.FORECAST_CST_FINISH forecast_cst_finish
    , (case when (select count(*) from INV_HIERARCHIES where INV_HIERARCHIES.parent_id = INV_INVESTMENTS.id) > 0 then
    INV_INVESTMENTS.id else null end) hg_has_children
    , fin_financials.FORECAST_CST_START forecast_cst_start
    , inv_hierarchies.default_alloc_pct * fin_financials.PLANNED_BEN_TOTAL planned_ben_total
    , inv_investments.schedule_finish schedule_finish
    , (select inv_investments.ODF_OBJECT_CODE from inv_investments where inv_investments.id = inv_hierarchies.child_id) child_inv_type
    , inv_investments.name name
    , inv_investments.STATUS_COMMENT status_comment
    , odf_ca_investmenthierarchy.partition_code partition_code
    , (SELECT q.partition_name FROM (SELECT
    up.model_name model_name,
    up.model_description model_description,
    up.partition_name partition_name,
    up.partition_description partition_description,
    up.partition_code partition_code,
    up.model_code model_code,
    up.is_active is_active,
    up.depth depth
    FROM
    ( SELECT nls2.name model_name, nls2.description model_description, nls1.name partition_name, nls1.description partition_description,
    cpv.partition_code partition_code, cpv.model_code model_code, cpv.is_active is_active, cpv.depth depth
    FROM
    cmn_user_partitions_v cpv,
    cmn_captions_nls nls1,
    cmn_captions_nls nls2,
    cmn_partition_models cpm
    WHERE
    cpv.user_id = xxxxxxx and
    cpv.model_code = cpm.code and
    ( (NULL /* model_code */ IS NOT NULL
    AND cpm.code = NULL /* model_code */)
    OR (NULL /* model_code */ IS NULL
    AND cpm.code= cpm.code)) and
    nls1.table_name = 'CMN_PARTITIONS' and
    nls1.pk_id = cpv.partition_id and
    nls1.language_code = 'en' and
    nls2.table_name = 'CMN_PARTITION_MODELS' and
    nls2.pk_id = cpm.id and
    nls2.language_code = 'en' and
    cpv.is_active = 1
    ) up
    WHERE 1=1) q WHERE q.partition_code = odf_ca_investmenthierarchy.partition_code) partition_code_caption
    , (inv_hierarchies.default_alloc_pct*(select fin_financials.planned_npv
    from fin_financials, odf_object_instance_mapping
    where odf_object_instance_mapping.primary_object_instance_id = inv_hierarchies.child_id
    and odf_object_instance_mapping.secondary_object_instance_id = fin_financials.id


    and odf_object_instance_mapping.primary_object_instance_code =


    (select odf_object_code from inv_investments where inv_investments.id = inv_hierarchies.child_id)


    and odf_object_instance_mapping.secondary_object_instance_code = 'financials' )) inv_planned_npv
    , fin_financials.BUDGET_CST_START budget_cst_start
    , inv_hierarchies.default_alloc_pct * fin_financials.BUDGET_CST_TOTAL budget_cst_total
    , inv_investments.STATUS status
    , (SELECT name FROM cmn_lookups_v WHERE lookup_type = 'INVESTMENT_OBJ_STATUS' AND language_code = 'en' AND lookup_enum = inv_investments.STATUS) status_caption
    , (select inv_investments.ODF_OBJECT_CODE from inv_investments where inv_investments.id = inv_hierarchies.parent_id) parent_inv_type
    , inv_hierarchies.CHILD_ID child_id
    , (SELECT q.name FROM (SELECT p.id id,




    p.code code,




    p.CODE UNIQUE_CODE,




    p.name name,




    p.description description




    FROM inv_investments p




    Where 1=1 and 1=1) q WHERE q.id = inv_hierarchies.CHILD_ID) child_id_caption
    , (select NVL(inv_hierarchies.default_alloc_pct


    * (select fin_financials.planned_cst_total
    from fin_financials, odf_object_instance_mapping
    where odf_object_instance_mapping.primary_object_instance_id = inv_hierarchies.child_id
    and odf_object_instance_mapping.secondary_object_instance_id = fin_financials.id


    and odf_object_instance_mapping.primary_object_instance_code = inv.odf_object_code


    and odf_object_instance_mapping.secondary_object_instance_code = 'financials' )






    ,0)


    -







    (NVL(inv_hierarchies.default_alloc_pct * (SELECT SUM (b.totalcost) FROM ppa_wip a, ppa_wip_values b














    WHERE a.transno = b.transno














    AND b.currency_type = 'HOME'














    AND a.status = 0














    AND a.investment_id = inv.id GROUP BY a.investment_id),0))


    from inv_investments inv


    where inv.id = inv_hierarchies.child_id) cost_variance
    , inv_hierarchies.default_alloc_pct * fin_financials.PLANNED_CST_TOTAL planned_cst_total
    , INV_INVESTMENTS.odf_object_code odf_object_code
    , (SELECT q.investment_type_name FROM (SELECT oe.object_code investment_type_code,


    c.name investment_type_name
    FROM odf_objects o,


    cmn_captions_nls c,


    odf_object_extensions oe
    WHERE c.pk_id = o.ID
    AND 1=1
    AND c.table_name = 'ODF_OBJECTS'
    AND language_code = 'en'
    AND oe.extension_code = 'inv'
    AND oe.object_code = o.code) q WHERE q.investment_type_code = INV_INVESTMENTS.odf_object_code) odf_object_code_caption
    , inv_hierarchies.default_alloc_pct * fin_financials.FORECAST_CST_TOTAL forecast_cst_total
    , inv_investments.IS_ACTIVE is_active
    FROM inv_hierarchies, fin_financials, odf_object_instance_mapping
    , odf_ca_investmenthierarchy ,SRM_RESOURCES CREATED, SRM_RESOURCES UPDATED, ODF_CA_INV, INV_INVESTMENTS WHERE inv_hierarchies.child_id = inv_investments.id
    AND inv_hierarchies.created_by = created.user_id
    AND inv_hierarchies.last_updated_by = updated.user_id
    AND inv_investments.id = odf_ca_inv.ID
    AND inv_hierarchies.id = odf_ca_investmenthierarchy.id
    AND inv_investments.ID = inv_hierarchies.child_id
    AND fin_financials.ID = odf_object_instance_mapping.secondary_object_instance_id
    AND odf_object_instance_mapping.secondary_object_instance_code = 'financials'
    AND inv_investments.ID = odf_object_instance_mapping.primary_object_instance_id
    AND odf_object_instance_mapping.primary_object_instance_code in
    (select object_code from odf_object_extensions where extension_code = 'inv')
    AND inv_hierarchies.parent_id = yyyyyyy
    AND inv_investments.id in (yyyyyyy,yyyyyyy,yyyyyyy,yyyyyyy,yyyyyyy,yyyyyyy,yyyyyyy,yyyyyyy,yyyyyyy,yyyyyyy,yyyyyyy,yyyyyyy,yyyyyyy,yyyyyyy,yyyyyyy,yyyyyyy) ORDER BY name asc