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