declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @parentObjectiveId int = ; with quotaTypes as ( select objType, quotaType, description, volumeRelated from tbl_bcrm_quotatypes with (nolock) ), data as ( select o.objectiveId,o.objName,o.objDescription, t.territoryId, t.name as territoryName, t.territoryPath, o.parentObjectiveId, o.objLevel, omgr.fspro_userId as objManagerId, omgr.firstName + ' ' + omgr.lastName objManagerFullName, omgr.email as objManagerEmail, omgr2.fspro_userId as objManager2Id, omgr2.firstName + ' ' + omgr2.lastName objManager2FullName, omgr2.email as objManager2Email, omgr3.fspro_userId as objManager3Id, omgr3.firstName + ' ' + omgr3.lastName objManager3FullName, omgr3.email as objManager3Email, o.quotaType, o.totalQuota, qt.description as quotaTypeName from tbl_BCRM_Objectives o with (nolock) left outer join tbl_FSPro_Members omgr with (nolock) on omgr.fspro_userId = o.objManagerId left outer join tbl_FSPro_Members omgr2 with (nolock) on omgr2.fspro_userId = o.objManager2Id left outer join tbl_FSPro_Members omgr3 with (nolock) on omgr3.fspro_userId = o.objManager3Id left outer join tbl_TER_TErritories t with (nolock) on t.ownerId = o.ownerId and t.fsl_tablecode = o.ownerType and t.territoryId = o.territoryId left outer join quotaTypes qt with (nolock) on qt.objType = o.objType and qt.quotaType = o.quotaType where o.ownerId = @ownerId and o.ownerType = @ownerType and o.parentObjectiveId = @parentObjectiveId and ( nullif( o.territoryId, 0 ) is null or o.territoryId in ( )) and o.objectiveId in ( select objectiveId from salesRepVisibility ) and ( exists ( select 1 from tbl_BCRM_ObjectiveStfLink s with (nolock) where s.ownerId = @ownerId and s.ownerType = @ownerType and s.objectiveId = o.objectiveId and s.fspro_userId in ( ) ) or ( o.objManagerId in ( )) ) ) #forJSON( 'data', attributes.datastore, _defaultSortOrder )#