AnsweredAssumed Answered

Filter Issue on Portlet

Question asked by navzjoshi00 Champion on Jul 31, 2012
Latest reply on Aug 2, 2012 by navzjoshi00
We have a portlet that displays hadr allocation over a period of time for a resource. Below is the query -

SELECT @SELECT:DIM:USER_DEF:IMPLIED:project:prj_id:projectid@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:project:prj_name:projectname@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:project:proj_manager:proj_manager@,
@SELECT:DIM:USER_DEF:IMPLIED:PERIODE:slice_date:slice_date@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PERIODE:date_display:date_display@,
@SELECT:METRIC:USER_DEF:IMPLIED:SUM (Allocation):Allocation@
FROM (SELECT srmp.ID prj_id, srmp.name prj_name,srmr.full_name res_name, srmr.ID res_id,
(SELECT s_csu.last_name || ', ' || s_csu.first_name
FROM cmn_sec_users s_csu
WHERE s_csu.ID = prjp.manager_id) proj_manager,
pbs.slice_date, INITCAP (TO_CHAR (pbs.slice_date, 'YYWW')) date_display,
SUM (pbs.slice) allocation
FROM srm_projects srmp INNER JOIN prteam ON prteam.prprojectid = srmp.ID and srmp.is_active =1
INNER JOIN prj_projects prjp ON prjp.prid = srmp.ID and prjp.prIsOpen =1
INNER JOIN srm_resources srmr ON srmr.ID = prteam.prresourceid
INNER JOIN prj_blb_slices_w_hardalc pbs ON pbs.prj_object_id = prteam.prid
AND pbs.slice_date BETWEEN TO_DATE (TRUNC (SYSDATE, 'MM')) AND TO_DATE (TRUNC (ADD_MONTHS (SYSDATE, 11), 'MM'))
AND pbs.slice <> 0
WHERE srmr.user_id = @WHERE:param:user_id@



GROUP BY srmp.ID,srmp.name , srmr.full_name, TO_CHAR (pbs.slice_date, 'YYWW'), srmp.NAME, prjp.manager_id, srmr.ID, pbs.slice_date
UNION
SELECT srmp.ID prj_id,srmp.name , srmr.full_name res_name, srmr.ID res_id,
(SELECT s_csu.last_name || ', ' || s_csu.first_name
FROM cmn_sec_users s_csu
WHERE s_csu.ID = prjp.manager_id) proj_manager,
nbical.period_start_date slice_date,
INITCAP (TO_CHAR (nbical.period_start_date, 'YYWW')) date_display, 0 allocation
FROM nbi_dim_calendar_time nbical CROSS JOIN srm_resources srmr
INNER JOIN prteam prt ON prt.prresourceid = srmr.ID
INNER JOIN srm_projects srmp ON srmp.ID = prt.prprojectid and srmp.is_active =1
inner join prj_projects prjp on prjp.prid=srmp.id and prjp.prIsOpen =1
WHERE nbical.period_start_date BETWEEN TO_DATE (TRUNC (SYSDATE, 'MM')) AND TO_DATE (TRUNC (ADD_MONTHS (SYSDATE, 11), 'MM'))
AND nbical.hierarchy_level = 'WEEK'
and prjp.prisopen<>0
AND srmr.user_id = @WHERE:param:user_id@



GROUP BY srmp.ID, srmp.name ,srmr.full_name, srmr.ID, prjp.manager_id,nbical.period_start_date, TO_CHAR (nbical.period_start_date, 'YYWW'))
WHERE @FILTER@
GROUP BY prj_id,prj_id,prj_name,proj_manager,slice_date, date_display
HAVING @HAVING_FILTER@

Filter was supposed to be applied for project name / manager / resource open for time entry (at team level). We changed the query to -

SELECT @SELECT:DIM:USER_DEF:IMPLIED:project:prj_id:projectid@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:project:prj_name:projectname@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:project:proj_manager:proj_manager@,
@SELECT:DIM:USER_DEF:IMPLIED:PERIODE:slice_date:slice_date@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:PERIODE:date_display:date_display@,
@SELECT:METRIC:USER_DEF:IMPLIED:SUM (Allocation):Allocation@
FROM (SELECT srmp.ID prj_id, srmp.name prj_name,srmr.full_name res_name, srmr.ID res_id,
(SELECT s_csu.last_name || ', ' || s_csu.first_name
FROM cmn_sec_users s_csu
WHERE @WHERE:PARAM:USER_DEF:integer:prj_mgr_id@ = s_csu.ID or (@WHERE:PARAM:USER_DEF:integer:prj_mgr_id@ is null and s_csu.ID = prjp.manager_id)) proj_manager,
pbs.slice_date, INITCAP (TO_CHAR (pbs.slice_date, 'YYWW')) date_display,
SUM (pbs.slice) allocation
FROM srm_projects srmp INNER JOIN prteam ON ((@WHERE:PARAM:USER_DEF:integer:prj_db_id@ = srmp.ID and srmp.is_active =1) or (@WHERE:PARAM:USER_DEF:integer:prj_db_id@ is null and prteam.prprojectid = srmp.ID))
INNER JOIN prj_projects prjp ON prjp.prid = srmp.ID and srmp.is_active =1 and prjp.prIsOpen =1
INNER JOIN srm_resources srmr ON srmr.ID = prteam.prresourceid
INNER JOIN prj_blb_slices pbs ON pbs.prj_object_id = prteam.prid
INNER JOIN prj_blb_slicerequests pbsr
ON pbs.slice_request_id = pbsr.ID
AND pbsr.request_name = 'WEEKLYRESOURCEALLOCCURVE'
AND pbs.slice_date BETWEEN TO_DATE (TRUNC (SYSDATE, 'MM')) AND TO_DATE (TRUNC (ADD_MONTHS (SYSDATE, 11), 'MM'))
AND pbs.slice <> 0
WHERE srmr.user_id = @WHERE:param:user_id@
GROUP BY srmp.ID,srmp.name , srmr.full_name, TO_CHAR (pbs.slice_date, 'YYWW'), srmp.NAME, prjp.manager_id, srmr.ID, pbs.slice_date
UNION
SELECT srmp.ID prj_id,srmp.name , srmr.full_name res_name, srmr.ID res_id,
(SELECT s_csu.last_name || ', ' || s_csu.first_name
FROM cmn_sec_users s_csu
WHERE (@WHERE:PARAM:USER_DEF:integer:prj_mgr_id@ = s_csu.ID or (@WHERE:PARAM:USER_DEF:integer:prj_mgr_id@ is null and s_csu.ID = prjp.manager_id))) proj_manager,
nbical.period_start_date slice_date,
INITCAP (TO_CHAR (nbical.period_start_date, 'YYWW')) date_display, 0 allocation
FROM nbi_dim_calendar_time nbical CROSS JOIN srm_resources srmr
INNER JOIN prteam prt ON (prt.prresourceid = srmr.ID and (prt.prisopen = decode(@WHERE:PARAM:USER_DEF:integer:pteamisopen@,4039,1,4040,0) or @WHERE:PARAM:USER_DEF:integer:pteamisopen@ is null))
INNER JOIN srm_projects srmp ON ((@WHERE:PARAM:USER_DEF:integer:prj_db_id@ = srmp.ID and srmp.is_active =1) or (@WHERE:PARAM:USER_DEF:integer:prj_db_id@ is null and srmp.ID = prt.prprojectid and srmp.is_active =1))
inner join prj_projects prjp on prjp.prid=srmp.id and prjp.prIsOpen =1
WHERE nbical.period_start_date BETWEEN TO_DATE (TRUNC (SYSDATE, 'MM')) AND TO_DATE (TRUNC (ADD_MONTHS (SYSDATE, 11), 'MM'))
AND nbical.hierarchy_level = 'WEEK'
and prjp.prisopen<>0
AND srmr.user_id = @WHERE:param:user_id@
GROUP BY srmp.ID, srmp.name ,srmr.full_name, srmr.ID, prjp.manager_id,nbical.period_start_date, TO_CHAR (nbical.period_start_date, 'YYWW'))
WHERE @FILTER@
GROUP BY prj_id,prj_id,prj_name,proj_manager,slice_date, date_display
HAVING @HAVING_FILTER@


Issue -

For a resource, who is hard booked on a project, remove the time entry on the team tab of the project (Do not change the booking status).
If filtered for yes, the above project should not show. However, even if it is yes, the project still shows up.

Any clues on this ?

NJ

Outcomes