drop table if exists [#_sendToObjectiveTable#]; declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @objectiveId int = ; with opportunities as ( select skuId, sku, skuDesc, sku + ' - ' + skuDesc as skuFull, cast( json_value( purchasingData, '$.operatorId' ) as int ) operatorId from [#_voidMatrixTable#] with (nolock) where cast( json_value( purchasingData, '$.selected' ) as bit ) = 1 ), targets as ( select t.skuId, t.sku, t.skuDesc, t.skuFull, ph.prodId, ph.mfr_id, ph.categoryId, opr.* from [tbl_DW_CRMOperators#_tableSuffix#] opr with (nolock) inner join opportunities t on t.operatorId = opr.operatorId inner join [tbl_DW_ProductHierarchy#_tableSuffix#] ph on ph.skuId = t.skuId where opr.ownerId = @ownerId and opr.ownerType = @ownerType ), objectives as ( select * from tbl_BCRM_Objectives with (nolock) where targetType = 'OPR' and (( objectiveId = @objectiveId and objLevel = 'S' ) or parentObjectiveId = @objectiveId ) ) select @objectiveId as targetObjectiveId, opr.skuId, opr.sku, opr.skuDesc, opr.skuFull, opr.prodId, opr.mfr_id, opr.categoryId, opr.operatorId, opr.companyName, opr.territoryId, case when opr.salesRepId is not null then opr.salesRepId else opr.primaryManager end as staffUserId, case when opr.salesRepId is not null then opr.salesRepEmail else opr.primaryManagerEmail end as staffEmail, case when opr.salesRepId is not null then opr.salesRepFirstName else opr.primaryManagerFirstName end as staffFirstName, case when opr.salesRepId is not null then opr.salesRepLastName else opr.primaryManagerLastName end as staffLastName, case when opr.salesRepId is not null then opr.salesRepFirstName + ' ' + opr.salesRepLastName else opr.primaryManagerFirstName + ' ' + opr.primaryManagerLastName end as staffFullName, opr.classificationId, opr.segmentPath, opr.distrib1, opr.distrib1_cdr_dstName, opr.gpoId_1, opr.gpoId_2, opr.gpoId_3, opr.gpoId_4, opr.gpoId_5, opr.cmcId_1, opr.cmcId_2, opr.cmcId_3, opr.priority, opr.classification, obj.objectiveId, obj.objName, ( select top 1 otl.linkId from tbl_BCRM_ObjectiveTrgLink otl with (nolock) where otl.objectiveID = obj.objectiveId and otl.targetType = 'OPR' and otl.targetID = opr.operatorId ) as existingTargetLinkId, case when obj.oprpriorityrestrict = 0 then 'pass' when obj.oprpriorityrestrict = opr.classificationId then 'PASS' else 'FAIL' end as classificationRestriction, case when not exists ( select * from tbl_bcrm_objrestrictions r where r.ownerId = @ownerId and r.ownerType = @ownerType and r.objectiveId = obj.objectiveId and restrictType = 'SEG' ) then 'pass' else case when exists ( select * from tbl_bcrm_objrestrictions r inner join tbl_OPR_ClientSegments s on s.ownerId = r.ownerId and s.fsl_tablecode = r.ownerType and s.clientSegId = r.restrictId where r.ownerId = @ownerId and r.ownerType = @ownerType and r.objectiveId = obj.objectiveId and r.restrictType = 'SEG' and opr.segmentPath like s.segmentPath + '%' ) then 'PASS' else 'FAIL' end end as segmentRestriction, case when obj.oprdistributorrestrict = 0 then 'pass' else case when obj.oprdistributorrestrict = opr.distrib1 then 'PASS' else 'FAIL' end end as distributorRestriction, case when obj.opraffiliationrestrict = 0 then 'pass' else case when obj.opraffiliationrestrict in ( opr.gpoId_1, opr.gpoId_2, opr.gpoId_3, opr.gpoId_4, opr.gpoId_5, opr.cmcId_1, opr.cmcId_2, opr.cmcId_3 ) then 'PASS' else 'FAIL' end end as memberGroupRestriction, case when obj.oprtyperestrict is null then 'pass' when obj.oprtyperestrict = opr.priority then 'PASS' else 'FAIL' end as priorityRestriction, 'pass' as mfrPriorityRestriction, case when not exists ( select * from tbl_BCRM_ObjectivePrdLink opl with (nolock) where opl.ownerId = @ownerId and opl.ownerType = @ownerType and opl.objectiveId = obj.objectiveId ) then 'pass' when exists ( select * from tbl_BCRM_ObjectivePrdLink opl with (nolock) where opl.ownerId = @ownerId and opl.ownerType = @ownerType and opl.objectiveId = obj.objectiveId and opl.skuId = opr.skuId ) then 'PASS' when exists ( select * from tbl_BCRM_ObjectivePrdLink opl with (nolock) where opl.ownerId = @ownerId and opl.ownerType = @ownerType and opl.objectiveId = obj.objectiveId and opl.skuId = 0 and opl.categoryId = opr.prodId ) then 'PASS 1' when exists ( select * from tbl_BCRM_ObjectivePrdLink opl with (nolock) where opl.ownerId = @ownerId and opl.ownerType = @ownerType and opl.objectiveId = obj.objectiveId and opl.skuId = 0 and opl.categoryId = -1 and opl.mfrId = opr.mfr_id ) then 'PASS 2' else 'FAIL' end productRestriction, cast( null as varchar(5) ) targetStatus, cast( 'N' as char(1)) as existingObjectiveTarget, cast( null as int ) as existingTargetInteractionId, cast( 'N' as char(1)) as existingSKU into [#_sendToObjectiveTable#] from targets opr with (nolock) left outer join objectives obj with (nolock) on obj.territoryId = opr.territoryId; alter table [#_sendToObjectiveTable#] add voidMatrixTargetId int not null identity (1,1); alter table [#_sendToObjectiveTable#] add constraint [PK_#_sendToObjectiveTable#] primary key ( voidMatrixTargetId ); update sto set sto.existingObjectiveTarget = 'Y', sto.existingTargetInteractionId = ( select top 1 i.interactionId from tbl_BCRM_ObjectiveTrgLink otl with (nolock) inner join tbl_CRM_InteractionParticipants ip with (nolock) on ip.ownerId = otl.ownerId and ip.ownerType = otl.ownerType and ip.participantId = otl.linkId inner join tbl_CRM_Interactions i with (nolock) on i.ownerId = ip.ownerId and i.ownerType = ip.ownerType and i.interactionId = ip.interactionId and i.disposition = '?' where otl.ownerId = and otl.ownerType = and otl.linkId = sto.existingTargetLinkId and otl.objectiveId = sto.objectiveId order by i.interactionDate desc ) from [#_sendToObjectiveTable#] as sto where sto.existingTargetLinkId is not null; update sto set sto.existingSKU = 'Y' from [#_sendToObjectiveTable#] sto where sto.existingTargetLinkId is not null and exists ( select * from tbl_CRM_InteractionSaleDetails isd with (nolock) where isd.ownerId = @ownerId and isd.ownerType = @ownerType and isd.saleSubjectId = sto.skuId and isd.saleSubjectType = 'SKU' and isd.interactionId = sto.existingTargetInteractionId ); update [#_sendToObjectiveTable#] set targetStatus = case when objectiveId is null then 'SKIP' when existingObjectiveTarget = 'Y' and existingTargetInteractionId is null then 'SKIP' when existingObjectiveTarget = 'Y' and existingSKU = 'Y' then 'SKIP' when 'FAIL' in ( priorityRestriction, productRestriction, distributorRestriction, memberGroupRestriction, classificationRestriction, segmentRestriction, mfrPriorityRestriction ) then 'SKIP' else 'OKAY' end; select t.voidMatrixTargetId, t.objectiveId, t.operatorId, t.existingTargetLinkId, t.skuId, t.staffUserId, t.existingTargetInteractionId from [#_sendToObjectiveTable#] t where targetStatus = 'OKAY' order by objectiveId, operatorId declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @objectiveId int = ; select osl.ownerId, osl.ownerType, osl.linkId, osl.objectiveId, osl.fspro_userId, osl.quota, count( distinct otl.targetId ) targets from tbl_BCRM_ObjectiveTrgLink otl with (nolock) inner join tbl_BCRM_Objectives o with (nolock) on o.ownerId= otl.ownerId and o.ownerType = otl.ownerType and o.objectiveId = otl.objectiveId inner join tbl_CRM_InteractionParticipants ip with (nolock) on ip.participantId = otl.linkId and ip.ownerId = otl.ownerId and ip.ownerType = otl.ownerType inner join tbl_CRM_Interactions i with (nolock) on i.ownerId = ip.ownerId and i.ownerType = ip.ownerType and i.interactionId = ip.interactionId inner join tbl_BCRM_ObjectiveStfLink osl with (nolock) on osl.ownerId = otl.ownerId and osl.ownerType = otl.ownerType and osl.objectiveId = otl.objectiveId and osl.fspro_userId= i.dispositionUserId where o.ownerId = @ownerId and o.ownerType = @ownerType and o.quotaType = 'APR' and ( o.objectiveId = @objectiveId or o.parentObjectiveId = @objectiveId ) group by osl.ownerId, osl.ownerType, osl.linkId, osl.objectiveId, osl.fspro_userId, osl.quota update tbl_BCRM_ObjectiveStfLink set quota = where ownerId = and ownerType = and linkId = declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @objectiveId int = ; select o.ownerId, o.ownerType, o.objectiveId, o.parentObjectiveId, o.totalQuota, count( distinct otl.linkId ) as staffQuota from tbl_BCRM_ObjectiveTrgLink otl with (nolock) inner join tbl_BCRM_Objectives o with (nolock) on o.ownerId= otl.ownerId and o.ownerType = otl.ownerType and o.objectiveId = otl.objectiveId inner join tbl_CRM_InteractionParticipants ip with (nolock) on ip.participantId = otl.linkId and ip.ownerId = otl.ownerId and ip.ownerType = otl.ownerType inner join tbl_CRM_Interactions i with (nolock) on i.ownerId = ip.ownerId and i.ownerType = ip.ownerType and i.interactionId = ip.interactionId inner join tbl_BCRM_ObjectiveStfLink osl with (nolock) on osl.ownerId = otl.ownerId and osl.ownerType = otl.ownerType and osl.objectiveId = otl.objectiveId and osl.fspro_userId= i.dispositionUserId where o.ownerId = @ownerId and o.ownerType = @ownerType and o.quotaType = 'APR' and ( o.objectiveId = @objectiveId or o.parentObjectiveId = @objectiveId ) group by o.ownerId, o.ownerType, o.objectiveId, o.parentObjectiveId, o.totalQuota update tbl_BCRM_objectives set totalQuota = where ownerId = and ownerType = and objectiveId = declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @objectiveId int = ; select p.ownerId, p.ownerType, p.objectiveId, p.totalQuota, count( distinct otl.linkId ) as staffQuota from tbl_BCRM_ObjectiveTrgLink otl with (nolock) inner join tbl_BCRM_Objectives o with (nolock) on o.ownerId= otl.ownerId and o.ownerType = otl.ownerType and o.objectiveId = otl.objectiveId inner join tbl_BCRM_Objectives p with (nolock) on p.ownerId = o.ownerId and p.ownerType = o.ownerType and p.objectiveId = o.parentObjectiveId inner join tbl_CRM_InteractionParticipants ip with (nolock) on ip.participantId = otl.linkId and ip.ownerId = otl.ownerId and ip.ownerType = otl.ownerType inner join tbl_CRM_Interactions i with (nolock) on i.ownerId = ip.ownerId and i.ownerType = ip.ownerType and i.interactionId = ip.interactionId inner join tbl_BCRM_ObjectiveStfLink osl with (nolock) on osl.ownerId = otl.ownerId and osl.ownerType = otl.ownerType and osl.objectiveId = otl.objectiveId and osl.fspro_userId= i.dispositionUserId where p.ownerId = @ownerId and p.ownerType = @ownerType and p.quotaType = 'APR' and ( p.objectiveId = @objectiveId ) group by p.ownerId, p.ownerType, p.objectiveId, p.totalQuota update tbl_BCRM_objectives set totalQuota = where ownerId = and ownerType = and objectiveId = select * from [#_sendToObjectiveTable#] with (nolock) order by targetStatus, companyName, skuFull for json auto, include_null_values drop table if exists [#_sendToObjectiveTable#];