declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @targetType varchar(3) = 'OPR'; declare @targetId int = ; declare @salesRepId int = ; declare @oprType varchar(1); declare @classificationId int; declare @distrib1 int; declare @territoryId int; select @classificationId = coalesce( classificationId, 0 ), @distrib1 = coalesce( distrib1, 0 ), @oprType = coalesce( oprType, '' ), @territoryId = territoryId from tbl_OPR_CLientOperators with (nolock) where ownerId = @ownerId and fsltablecode = @ownerType and operatorId = @targetId and 'OPR' = @targetType; with eligibleObjectives as ( select 'ELIGIBLE' as linkStatus, 2 as linkStatusKey, obj.ObjectiveID, obj.ObjName, null totalQuota, osl.quota, osl.QuotaAchieved, ( select COUNT(distinct otl.targetid) from tbl_CRM_InteractionParticipants ip with (nolock) inner join tbl_BCRM_ObjectiveTrgLink otl with (nolock) on otl.LinkID = ip.ParticipantID and otl.ObjectiveID = obj.ObjectiveID inner join tbl_CRM_Interactions int with (nolock) on int.InteractionID = ip.InteractionID and int.DispositionUserId = @salesRepId and int.ownerId = obj.ownerId and int.ownerType = obj.ownerType ) targetCount from tbl_BCRM_Objectives obj with (nolock) inner join tbl_BCRM_ObjectiveStfLink osl with (nolock) on obj.ObjectiveID = osl.ObjectiveID and osl.FSPro_UserID = @salesRepId where obj.ownerid = @ownerId and obj.OwnerType = @ownerType and GETDATE() <= obj.unpublishdate and obj.TargetType = @targetType and obj.Ready = 'y' and obj.allowRepTargeting = 1 and ( obj.territoryid = 0 or obj.territoryid = @territoryId ) and not exists ( select * from tbl_BCRM_ObjectiveTrgLink with (nolock) where ObjectiveID = obj.ObjectiveID and TargetType = @targetType and TargetID = @targetId) and ( obj.oprpriorityrestrict = 0 OR obj.oprpriorityrestrict = @classificationId ) and ( not exists ( select restrictid from tbl_bcrm_objrestrictions with (nolock) where ownerid = obj.ownerid and ownertype = obj.ownertype and objectiveid = obj.objectiveid and restricttype = 'SEG' ) OR exists ( select restrictid from tbl_bcrm_objrestrictions sq_obr with (nolock) inner join tbl_opr_clientoperators sq_opr with (nolock) on sq_opr.operatorid = @targetId and 'OPR' = @targetType and sq_opr.ownerId = @ownerId and sq_opr.fsltablecode = @ownerType inner join tbl_opr_clientsegments sq_seg with (nolock) on sq_seg.clientsegid = sq_opr.oprsegment where sq_obr.ownerid = obj.ownerid and sq_obr.ownertype = obj.ownertype and sq_obr.objectiveid = obj.objectiveid and sq_obr.restricttype = 'SEG' and ( sq_obr.restrictid = sq_seg.clientsegid OR sq_obr.restrictid = sq_seg.ParentClientSegId ) ) ) and ( obj.oprdistributorrestrict = 0 OR obj.oprdistributorrestrict = @distrib1 ) and ( obj.opraffiliationrestrict = 0 OR ( select count(*) from tbl_crm_affiliations with (nolock) where ownerid = obj.ownerid and ownertype = obj.ownertype and orgid = obj.opraffiliationrestrict and orgtype = 'opr' and partnertype = @targetType and partnerid = @targetId ) > 0 ) and ( obj.oprtyperestrict is null OR obj.oprtyperestrict = @oprType ) and ( ( obj.mfroprtyperestrict is null or obj.ObjOwnerType <> 'mfr' ) OR ( obj.mfroprtyperestrict = ( select focusaccount from tbl_BRO_PrincipalOperators po with (nolock) inner join tbl_BRO_PrincipalTerritories pt with (nolock) on pt.principalTerritoryId = po.principalTerritoryId inner join tbl_BRO_Principals p with (nolock) on p.principalid = pt.principalid and p.mfrid = obj.ObjOwnerId where po.ownerid = obj.ownerid and po.ownertype = obj.ownertype and po.operatorid = @targetId and 'OPR' = @targetType ) ) ) ) , data as ( select linkStatusKey, linkStatus, objectiveId, objName, totalQuota, quota, quotaAchieved, targetCount from eligibleObjectives ) #forJSON( 'data', attributes.datastore, 'linkStatusKey,objName' )#