declare @ownerId int = ; declare @ownerType varchar(3) = ; declare @partnerId int = ; declare @partnerType varchar(3) = ; with rawData as ( select i.interactionId, floor( 10191817 * sqrt( i.interactionId * log10( i.interactionId ))) as interactionIdTk, i.disposition, i.interactionDate, i.sourceType, i.notes, i.closeComment, i.comment, case when i.sourceType = 'EML' then 'Sent' when coalesce( i.disposition, '?' ) = '?' then 'Planned' else 'Completed' end as statusDisplay, bsr.fspro_userId as salesRepId, bsr.firstName as salesRepFirstName, bsr.lastName salesRepLastName, bsr.firstName + ' ' + bsr.lastName as salesRepFullName, bsr.email salesRepEmail, i.partnerId, floor( 10191817 * sqrt( i.partnerId * log10( i.partnerId ))) as partnerIdTk, i.emailId, floor( 10191817 * sqrt( i.emailId * log10( i.emailId ))) as emailIdTk, nullif( nullif( i.interactionMethod, '' ), '?' ) as interactionMethod, cast( case when coalesce( i.sourceType, '' ) = 'EML' then 1 else 0 end as bit ) as directMarketing, i.recapSent, coalesce( i.recapPending, cast( 0 as bit )) as recapPending, 'SalesCallRecap' + format( i.recapSent, 'MM-dd-yyyy' ) recapDocTitle from tbl_CRM_Interactions i with (nolock) left outer join tbl_FSpro_members bsr with (nolock) on bsr.fspro_userId = i.dispositionUserId -- TSE-621 -- bsr.ownerId > 0 where i.ownerId = @ownerId and i.ownerType = @ownerType and i.partnerId = @partnerId and i.partnerType = @partnerType ) , salesCallRecap as ( select r.docTitle recapDocTitle, c.contentId, row_number() over ( partition by r.docTitle order by c.contentId desc ) contentRank, '#_contentRoot#' + c.mainFolder + '/' + c.subFolder + '/' + c.fileName as recapContentURL from tbl_OPR_Repository r with (nolock) inner join tbl_FSC_Content c with (nolock) on c.contentId = r.contentId where r.containerType = @partnerType and r.containerId = @partnerId and r.docTitle like 'SalesCallRecap%' ) , data as ( select rawData.*, scr.recapContentURL, (select count(*) from tbl_crm_interactionparticipants ip with (nolock) inner join tbl_bcrm_objectivetrglink otl with (nolock) on otl.linkid = ip.participantid and otl.inserttype = 'm' where ip.ownerid = @ownerId and ip.ownertype = @ownerType and ip.interactionid = rawData.interactionId ) as hardObjLinks, case when rawData.interactionDate is null then 1 when rawData.statusDisplay = 'Planned' then 2 else 3 end as sortGroup from rawData left outer join salesCallRecap scr with (nolock) on scr.recapDocTitle = rawData.recapDocTitle and scr.contentRank = 1 where 1 = 1 and rawData.directMarketing = 0 and rawData.salesRepId = and ( rawData.interactionDate >= dateadd( month, , cast( getDate() as date )) or rawData.interactionDate is null ) and ( exists ( select* from tbl_CRM_Interactions i with (nolock) inner join tbl_CRM_InteractionSaleDetails isd with (nolock) on isd.interactionId = i.interactionId and isd.ownerType =i.ownerType and isd.ownerId = i.ownerId inner join tbl_PRD_Skus s with (nolock) on s.ownerId = isd.ownerId and s.fsl_tablecode = isd.ownerType and s.skuId = isd.saleSubjectId and 'SKU' = isd.saleSubjectType inner join tblManufacturers mfr with (nolock) on mfr.mfr_id = s.mfrId and mfr.mfr_id = where i.ownerId = @ownerId and i.ownerType = @ownerType and i.partnerId = @partnerId and i.partnerType = @partnerType and i.interactionId = rawData.interactionId ) or exists ( select * from tbl_CRM_Interactions i with (nolock) inner join tbl_CRM_InteractionSaleDetails isd with (nolock) on isd.interactionId = i.interactionId and isd.ownerType =i.ownerType and isd.ownerId = i.ownerId inner join tbl_PRD_Products s with (nolock) on s.ownerId = isd.ownerId and s.fsl_tablecode = isd.ownerType and s.prodId = isd.saleSubjectId and 'PRD' = isd.saleSubjectType inner join tblManufacturers mfr with (nolock) on mfr.mfr_id = s.mfrId and mfr.mfr_id = where i.ownerId = @ownerId and i.ownerType = @ownerType and i.partnerId = @partnerId and i.partnerType = @partnerType and i.interactionId = rawData.interactionId ) ) ) #forJSON( 'data', attributes.datastore, 'sortGroup,interactionDate desc' )#