Hi,
I think your issue is from the following line :
-- Project Level
SELECT DISTINCT
'L1-' @+@ CMN_TO_CHAR_FCT(i.id) dim_id,
i.name level_name,
i.code level_id,
' ' column_name,
' ' before,
' ' after,
null created_date,
CASE WHEN (
SELECT COUNT(*)
FROM prtask ct
WHERE ct.prprojectid = i.id
) > 0
THEN 'L1-' @+@ CMN_TO_CHAR_FCT(i.id) @+@ CMN_TO_CHAR_FCT(i.id) @+@ CMN_TO_CHAR_FCT(i.id)
WHEN (SELECT COUNT(*)
FROM prsubproject sp
INNER JOIN prtask sptasks ON sptasks.prid = sp.prtaskid
WHERE sptasks.prprojectid = i.id
) > 0
THEN 'L1-' @+@ CMN_TO_CHAR_FCT(i.id) @+@ CMN_TO_CHAR_FCT(i.id) @+@ CMN_TO_CHAR_FCT(i.id)
ELSE NULL END hg_has_children
FROM inv_investments i
In these line you calculate the presence of sub object, to indicate if and expand sign is displayed in the hierarchical portlet. But in the sub query that will run when you click on has a more restricted data set (due to the filter of date) :
(Example only for the second level, and for task of project)
SELECT DISTINCT
'L2-' @+@ CMN_TO_CHAR_FCT(tasks.prprojectid) @+@ @NVL@(@SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@,11,7),'0000000') @+@ @DBUSER@.CMN_TO_CHAR_FCT(tasks.prid) dim_id,
tasks.prname level_name,
tasks.prexternalid level_id,
' ' column_name,
' ' before,
' ' after,
null created_date,
CASE
WHEN
(SELECT COUNT(*)
FROM prtask ct
WHERE
ct.prprojectid = tasks.prprojectid
AND ct.wbs_parseq = tasks.prwbssequence
AND (CMN.COLUMN_NAME in ('prStart','prFinish','prName') or (cmn.column_name='prStatus' and raw_value_after = 2))
and ( (trunc(cmn.created_date,'DDD') >= @where:param:user_def:date:start_date@ or @where:param:user_def:date:start_date@ is null)
and (trunc(cmn.created_date,'DDD')<= @where:param:user_def:date:finish_date@ or @where:param:user_def:date:finish_date@ is null))
and cmn.object_code='task') > 0
THEN 'L2-' @+@ CMN_TO_CHAR_FCT(tasks.prprojectid)
@+@ @NVL@(@SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@,11,7),'0000000')
@+@ @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@,18,7) @+@ @DBUSER@.CMN_TO_CHAR_FCT(tasks.prwbssequence)
WHEN (SELECT COUNT(*)
FROM prtask tasks
WHERE cmn.object_id= tasks.prid
AND(CMN.COLUMN_NAME in ('prStart','prFinish','prName') or (cmn.column_name='prStatus' and raw_value_after = 2))
and ((trunc(cmn.created_date,'DDD') >= @where:param:user_def:date:start_date@ or @where:param:user_def:date:start_date@ is null)
and (trunc(cmn.created_date,'DDD')<= @where:param:user_def:date:finish_date@ or @where:param:user_def:date:finish_date@ is null))
and cmn.object_code='task') > 0
THEN 'L2-' @+@ CMN_TO_CHAR_FCT(tasks.prprojectid) @+@ @NVL@(@SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@,11,7),'0000000')
@+@ @DBUSER@.CMN_TO_CHAR_FCT(tasks.prid)
ELSE NULL
END hg_has_children
FROM prtask tasks
INNER JOIN inv_investments i ON i.id = tasks.prprojectid
LEFT OUTER JOIN cmn_lookups_v ts ON ts.lookup_type = 'prTaskStatus'
AND ts.lookup_enum = tasks.prstatus
AND ts.language_code = @WHERE:PARAM:LANGUAGE@
LEFT OUTER JOIN prsubproject sp ON sp.prtaskid = tasks.prid
LEFT OUTER JOIN CMN_AUDITS CMN ON CMN.OBJECT_ID=tasks.PRID
WHERE
@NVL@(sp.prtaskid,0) = 0
AND ( ('L1-' @+@ CMN_TO_CHAR_FCT(tasks.prprojectid) = @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@,1,10) AND tasks.wbs_parseq = -1)
OR ('L2-' @+@ CMN_TO_CHAR_FCT(tasks.prprojectid) = @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@,1,10) AND @DBUSER@.CMN_TO_CHAR_FCT(tasks.wbs_parseq) = @SUBSTR@(@WHERE:PARAM:USER_DEF:STRING:hg_row_id@,25,7)) )
AND (CMN.COLUMN_NAME in ('prStart','prFinish','prName') or (cmn.column_name='prStatus' and raw_value_after = 2))
and ((trunc(cmn.created_date,'DDD') >= @where:param:user_def:date:start_date@ or @where:param:user_def:date:start_date@ is null)
and (trunc(cmn.created_date,'DDD')<= @where:param:user_def:date:finish_date@ or @where:param:user_def:date:finish_date@ is null))
and cmn.object_code='task' AND CMN.OPERATION_CODE='U'
AND @WHERE:SECURITY:PROJECT:i.id@
So in some case you will have several returns when you select all task associated to a project (without any filter regarding security @WHERE:SECURITY:PROJECT:...@, and this selected task will not have any association with audit entries, or will not be selected in the second part of the query due to the security or date constraint.
You need to align your filter query from the sub query with the query used to test if there existing task (1st part)
Hopes that will help you :-)