SELECT l.Rank, l.BrokerLevel, t.parentTerritoryId, t.territoryId, t.name, b.broker_id, b.broker_name, coalesce( mbr.firstName, mbr.firstName, '#htmleditformat( request.portalConfiguration.apps.102.unassignedLabel )#') + ' ' + coalesce( mbr.lastName, mbr.lastName, '') managerName, mbr.firstName + ' ' + mbr.lastName mfrManagerName, smbr.firstName + ' ' + smbr.lastName secondaryManagerName, count(distinct cdr.cdr_recordId) distributorCount FROM tbl_TER_Territories t INNER JOIN tbl_TER_TerritoryLevels l ON l.levelId = t.levelid AND l.ownerId = t.ownerId AND l.FSL_TableCode = t.FSL_TableCode AND l.division in () LEFT OUTER JOIN tbl_FSPro_Members mbr ON mbr.ownerId = t.ownerId AND mbr.FSL_TableCode = t.FSL_TableCode AND mbr.FSPro_UserId = t.primaryManager LEFT OUTER JOIN tbl_FSPro_Members smbr ON smbr.ownerId = t.ownerId AND smbr.FSL_TableCode = t.FSL_TableCode AND smbr.FSPro_UserId = t.secondaryManager LEFT OUTER JOIN tbl_CDR_Distributors cdr ON cdr.cdr_territoryId = t.territoryId AND cdr.ownerId = t.ownerId AND cdr.FSL_TableCode = t.FSL_TableCode AND cdr.CRMActive = 'Y' AND cdr.cdr_dstcompanytype <> 'O' and cdr.cdr_dstcompanytype <> 'S' AND ( cdr.cdr_dstparentco in ( ) OR cdr.cdr_recordID IN ( ) OR cdr.cdr_recordId in ( select cdrb.cdr_recordId from tbl_CDR_Distributors cdrb inner join tbl_DST_Distributors dstb on dstb.dstid = cdrb.fsl_dstid inner join tbl_DST_Distributors dstp on dstp.dstid = dstb.dstparentco inner join tbl_CDR_Distributors cdrp on cdrp.fsl_dstid = dstp.dstid and cdrp.ownerid = cdrb.ownerid and cdrp.fsl_tablecode = cdrb.fsl_tablecode and cdrp.cdr_recordid in ( ) where cdrb.ownerid = and cdrb.fsl_tablecode = )) AND (( cdr.cdr_dstparentco = 0 or cdr.cdr_dstparentco is null ) and cdr.cdr_dstcompanytype = 'B' ) LEFT OUTER JOIN tbl_SMA_BrokerOfficeLink sma ON sma.territoryid = t.territoryid AND sma.mfr_id = t.ownerID LEFT OUTER JOIN tblBrokers b ON b.broker_id = sma.brokerid inner join ( select distinct p.partnerid as distrribId, p.ownerId, p.ownerType from tbl_CRM_Participants p inner join tbl_CRM_Interactions i on i.participantId = p.participantId and i.ownerId = p.ownerId and i.ownerType = p.ownerType and i.disposition = and i.disposition = '?' inner join tbl_CRM_InteractionSaleDetails #isdAlias# on #isdAlias#.interactionId = i.interactionId and #isdAlias#.saleSubjectId = and #isdAlias#.disposition in ( 'STD', 'LTO') and #isdAlias#.disposition = and #isdAlias#.nosalereasonId = and i.disposition <> '?' inner join tbl_CRM_MarketingActivities ma on ma.marketingACtivityId = p.marketingActivityId and ma.maCategoryId = where p.ownerId = and p.ownerType = and p.partnerType = 'CDR' and p.marketingActivityId = ) p on p.distrribId = cdr.cdr_recordId and p.ownerid = cdr.ownerId and p.ownerType = cdr.fsl_tableCode WHERE t.OwnerID = AND t.FSL_TableCode = and cdr.classificationid = AND t.territoryId in () AND t.territoryId in ( ) AND t.territoryId in ( ) AND t.territoryId in ( ) and cdr.mfr_bsr_id2 in ( ) and coalesce( cdr.mfr_bsr_id2, 0 ) <> 0 AND cdr.cdr_recordId not in ( select stk.cdr_recordId from tbl_CDR_Stocking stk where stk.ownerId = and stk.ownerType = and stk.volumeQty > 0 and stk.volumetime = 'M' and stk.skuId in ( select sku.skuid from tbl_PRD_Skus sku where sku.ownerId = and sku.FSL_TableCode = and sku.bgtCatID = and sku.skuid = and sku.ProdID = and sku.ProdID in ( select prd.prodId from tbl_PRD_Products prd where prd.ownerId = and prd.FSL_TableCode = and prd.categoryid = ) group by sku.skuid ) group by stk.cdr_recordId having SUM(stk.volumeqty) >= having SUM(stk.volumeqty) <= ) and ( cdr.cdr_priority in ( ) ) and ( cdr.lastInteractionDate is not null and datediff( dd, cdr.lastInteractionDate, getDate() ) <= ) ( cdr.lastInteractionDate is null or datediff( dd, cdr.lastInteractionDate, getDate() ) > ) ( cdr.lastInteractionDate is null or datediff( dd, cdr.lastInteractionDate, getDate() ) > 365 ) and cdr.lastInteractionDate is null and cdr.cdr_recordId in ( select i.partnerId from tbl_CRM_interactions i inner join tbl_CRM_InteractionSaleDetails isd on isd.interactionId = i.interactionId and isd.saleSubjectType = 'SKU' inner join tbl_PRD_Skus s on s.skuId = isd.saleSubjectId and s.ownerId = i.ownerId and s.fsl_tablecode = i.ownerType and s.mfrId = where i.ownerId = and i.ownerType = and i.partnerType = 'CDR' and i.dispositionUserId = ) and cdr.cdr_recordId in ( select i.partnerId from tbl_CRM_interactions i where i.ownerId = and i.ownerType = and i.partnerType = 'CDR' and i.disposition = '?' and i.disposition <> '?' ) AND ( cdr.cdr_dstcode = OR cdr.cdr_dstName like OR cdr.cdr_dstaddress1 like OR cdr.cdr_dstaddress2 like OR cdr.cdr_dstCity like OR cdr.cdr_dstState = OR cdr.cdr_dstzip like OR cdr.foodservicerewardsId = or exists (select * from tbl_CDR_DstCodes cdsc where cdsc.cdr_recordId = cdr.cdr_recordId and cdsc.cdr_dstcode like ) ) and coalesce( cdr.mfr_bsr_id, 0 ) = 0 and cdr.mfr_bsr_id in ( ) and cdr.cdr_recordId in ( ) and ( cdr.mfr_bsr_id = OR cdr.cdr_territoryid IN () ) AND cdr.cdr_recordId in ( select spl.partnerId from tbl_SPL_Opportunities spl inner join tbl_SPL_Stages stg on stg.stageId = spl.stageId inner join tbl_WRK_Tasks tsk on tsk.subjectId = spl.opportunityId and tsk.subjectType = 'SPL' and tsk.ownerId = spl.ownerId and tsk.ownerType = spl.ownerType and tsk.disposition in ( 'A', '?' ) and tsk.dueDate <= { fn now() } and tsk.dueDate <= dateadd( day, #_daysAhead#, getDate() ) and tsk.taskTemplateId = where spl.partnerType = 'CDR' and spl.ownerId = and spl.ownerType = and spl.stageId in ( ) and spl.availableDate between and and spl.salesPersonUserId = and datepart( yy, case when stg.statusCode = 'C' or stg.statusCode = 'I' then spl.availableDate when stg.statusCode = 'A' then getDate() when stg.statusCode = 'R' then spl.availableDate else coalesce( spl.updateDate, spl.createDate ) end ) + 3 > datepart( yy, getdate() ) ) GROUP BY l.rank, l.brokerLevel, t.parentTerritoryId, t.territoryId, t.name, mbr.firstname, mbr.lastname, smbr.firstname, smbr.lastname, b.broker_Name, b.broker_id ORDER BY l.rank ASC, t.name ASC