AnsweredAssumed Answered

Project Assignment Portlet returning incorrect record counts

Question asked by Owen_R on Apr 30, 2014
Latest reply on May 1, 2014 by Owen_R

I'm running the OOTB Project Assignments portlet on 12.1.3 (SQL Server) for a specific resource OBS node and am getting some strange record counts back when I don't pass a value in to the Resource Active parameter.  I have configured the portlet so that Resource Active parameter is visible, and displays as a pull down (default is for it to be a hidden checkbox).

The results are as follows:

1 If Resource Active = Yes then 2597 records returned

2 If Resource Active = No then 3159 records returned

3 if Resource Active = All then >12000 records are returned (where I would expect 2597 + 3159)

I've done a SQL trace for each of the above, and the only difference comes with the check on SRM_RESOURCES.IS_ACTIVE in the Where clause.  In 1 & 2, it checks for IS_ACTIVE = 1 or 0 respectively, as expected.  In 3, the condition is not present (again, as expected as I'm not checking its values), but the returned data set is massive.

I can't understand why the lack of this line increases the data so much; it's not used in a join as far as I can tell.  Can anyone else recreate this?

SQL trace is below:

select * from

(select row_number() over ( order by requirement_name asc , odf_pk) odf_row_num, count(*) over (partition by pmd_analytical_partition_by) odf_num_rows,

q.*

from

( select 'x' pmd_analytical_partition_by, prteam.prid odf_pk , INV_INVESTMENTS.odf_object_code COLLATE Latin1_General_CI_AS_KS investment_type ,

CASE WHEN PRJ_RESOURCES.PRISROLE = 1 THEN ISNULL(PRTEAM.REQUIREMENT_NAME,SUBSTRING(SRM_RESOURCES.FULL_NAME,1,100))ELSE SRM_RESOURCES.FULL_NAME END COLLATE Latin1_General_CI_AS_KS requirement_name ,

prteam.prAvailStart pravailstart , prteam.prProjectID prprojectid,

(select top 1 name from ( SELECT INV.ID ID ,INV.CODE CODE,INV.CODE UNIQUE_CODE,INV.NAME NAME,INV.ODF_OBJECT_CODE OBJECT_CODE FROM INV_INVESTMENTS INV LEFT OUTER JOIN INV_PROJECTS PRJ ON INV.ID = PRJ.PRID

WHERE 1=1 AND ISNULL(PRJ.IS_TEMPLATE,0)=0 AND (INV.PURGE_FLAG=0 OR INV.PURGE_FLAG IS NULL) AND 1=1 and 1=1) q where q.id = prteam.prProjectID) prprojectid_caption ,

PRJ_RESOURCES.PRISROLE isrole , prteam.prResourceID prresourceid,

(select top 1 full_name from ( SELECT SRM_RESOURCES.ID ID,SRM_RESOURCES.LAST_NAME LAST_NAME,SRM_RESOURCES.FIRST_NAME FIRST_NAME,SRM_RESOURCES.FULL_NAME FULL_NAME,SRM_RESOURCES.UNIQUE_NAME UNIQUE_NAME,SRM_RESOURCES.UNIQUE_NAME UNIQUE_CODE FROM SRM_RESOURCES, PRJ_RESOURCES

WHERE 1=1 AND 1=1 and 1=1 AND SRM_RESOURCES.ID=PRJ_RESOURCES.PRID) q where q.id = prteam.prResourceID) prresourceid_caption ,

SRM_RESOURCES.DATE_OF_HIRE prdoh , INV_INVESTMENTS.NAME COLLATE Latin1_General_CI_AS_KS project_name , prteam.prRoleID prroleid,

(select top 1 prname from ( SELECT PRJ_RES.PRID PRID, PRJ_RES.PRUID UNIQUE_CODE, SRM_RES.FULL_NAME PRNAME FROM PRJ_RESOURCES PRJ_RES, SRM_RESOURCES SRM_RES

WHERE PRJ_RES.PRISROLE != 0 AND PRJ_RES.PRID = SRM_RES.ID AND 1=1 AND 1=1 and 1=1) q where q.prid = prteam.prRoleID) prroleid_caption ,

SRM_RESOURCES.DATE_OF_TERMINATION prdot , prteam.RSF_PERSON_TYPE rsf_person_type,

(select top 1 NAME from cmn_lookups_v where language_code = 'en' and lookup_type = 'srm_resource_type' and ID = prteam.RSF_PERSON_TYPE) rsf_person_type_caption ,

prteam.prAvailFinish pravailfinish , prteam.HARD_FINISH hard_finish ,

CASE WHEN SRM_RESOURCES.RESOURCE_TYPE < 2 THEN (niku.prj_sum_assignment_fct(prteam.prid,0) / 3600) / niku.PRJ_HPD_FACTOR_FCT() ELSE niku.prj_sum_assignment_fct(prteam.prid,0) END estsum ,

odf_ca_team.partition_code COLLATE Latin1_General_CI_AS_KS partition_code,

(select b.name from cmn_partitions a,cmn_captions_nls b where a.id = b.pk_id and b.table_name = 'CMN_PARTITIONS' and b.language_code = 'en' and a.code = odf_ca_team.partition_code union select name from cmn_captions_nls where pk_id = -1 and table_name = 'CMN_PARTITIONS' and language_code = 'en' and 'NIKU.ROOT' = odf_ca_team.partition_code) partition_code_caption ,

prteam.prBooking prbooking,

(select top 1 NAME from cmn_lookups_v where language_code = 'en' and lookup_type = 'booking_status_list' and LOOKUP_ENUM = prteam.prBooking) prbooking_caption , CASE WHEN SRM_RESOURCES.RESOURCE_TYPE < 2 THEN PRTEAM.PRALLOCSUM / 3600 / niku.PRJ_HPD_FACTOR_FCT() ELSE PRTEAM.PRALLOCSUM END prallocsum ,

SRM_RESOURCES.EMAIL COLLATE Latin1_General_CI_AS_KS email , INV_INVESTMENTS.SCHEDULE_FINISH projectfinish , INV_INVESTMENTS.SCHEDULE_START projectstart ,

prteam.HARD_START hard_start , SRM_RESOURCES.FULL_NAME COLLATE Latin1_General_CI_AS_KS full_name , SRM_RESOURCES.RESOURCE_TYPE restype,

(select top 1 NAME from cmn_lookups_v where language_code = 'en' and lookup_type = 'srm_resource_type' and LOOKUP_ENUM = SRM_RESOURCES.RESOURCE_TYPE) restype_caption,

ISNULL(prteam.pravailstart, inv_investments.schedule_start) pravailstart_sort, ISNULL(prteam.pravailfinish, inv_investments.schedule_finish) pravailfinish_sort

from

odf_ca_team, PRTeam LEFT OUTER JOIN PRJ_BASELINE_DETAILS BASEREC ON PRTeam.PRID = BASEREC.OBJECT_ID AND BASEREC.OBJECT_TYPE='TEAM' AND 1 = BASEREC.IS_CURRENT

, INV_INVESTMENTS LEFT OUTER JOIN INV_PROJECTS ON INV_INVESTMENTS.ID = INV_PROJECTS.PRID

, SRM_RESOURCES LEFT OUTER JOIN SRM_RESOURCES RES_MANAGER ON SRM_RESOURCES.MANAGER_ID=RES_MANAGER.USER_ID,

PRJ_RESOURCES, SRM_RESOURCES CREATED, SRM_RESOURCES UPDATED , PRJ_OBS_ASSOCIATIONS RES_OBS

where

INV_INVESTMENTS.ID=PRTeam.prProjectID AND SRM_RESOURCES.ID=PRTeam.prResourceID AND PRJ_RESOURCES.PRID = SRM_RESOURCES.ID

and PRTEAM.CREATED_BY = CREATED.USER_ID AND PRTEAM.LAST_UPDATED_BY = UPDATED.USER_ID

/* THIS IS THE RESOURCE OBS NODE - FILTERING FOR UNITS AND DESCENDANTS */

AND RES_OBS.UNIT_ID IN (SELECT UNIT_ID FROM PRJ_OBS_UNITS_FLAT WHERE BRANCH_UNIT_ID=5000188)

AND RES_OBS.TABLE_NAME = 'SRM_RESOURCES' AND RES_OBS.RECORD_ID = SRM_RESOURCES.ID

/* ------- */

and prteam.prid = odf_ca_team.id

and 1=1 and 1=1 and 1=1 and 1=1 and 1=1

and INV_INVESTMENTS.IS_ACTIVE = 1 and PRJ_RESOURCES.PRISROLE = 0 and ISNULL(INV_PROJECTS.IS_TEMPLATE,0) = 0 and SRM_RESOURCES.RESOURCE_TYPE = 0

/* Change the 1 to 0 or comment this line out to replicate the issue */

and SRM_RESOURCES.IS_ACTIVE = 1

/* ---- */

) q) q

 

Outcomes