with partners as ( select p.ownerId, p.fsltablecode ownerType, p.operatorId partnerId, 'OPR' partnerType, m.fspro_userId accountOwnerUserId, p.territoryId from tbl_OPR_CLientOperators p with (nolock) left join tbl_Fspro_members m with (nolock) on m.fspro_userId = p.mfr_bsr_id and m.fsl_tablecode = p.fsltablecode and m.ownerId = p.ownerId where p.ownerId = and p.fsltablecode = and exists ( select * from tbl_TPM_Contracts spl with (nolock) where spl.ownerId = p.ownerId and spl.ownerType = p.fsltablecode and spl.partnerType = 'OPR' and spl.partnerId = p.operatorId ) union all select p.ownerId, p.fsl_tablecode ownerType, p.cdr_recordId partnerId, 'CDR' partnerType, m.fspro_userId accountOwnerUserId, p.cdr_territoryId territoryId from tbl_CDR_Distributors p with (nolock) left join tbl_Fspro_members m with (nolock) on m.fspro_userId = p.mfr_bsr_id and m.fsl_tablecode = p.fsl_tablecode and m.ownerId = p.ownerId where p.ownerId = and p.fsl_tablecode = and exists ( select * from tbl_TPM_Contracts spl with (nolock) where spl.ownerId = p.ownerId and spl.ownerType = p.fsl_tablecode and spl.partnerType = 'CDR' and spl.partnerId = p.cdr_recordId ) ), partnersWithOwners as ( select p.ownerId, p.ownerType, p.partnerId, p.partnerType, coalesce( nullif( p.accountOwnerUserId, 0 ), coalesce( nullif( t.primaryManager, 0 ), nullif( pt.primaryManager, 0 ) ) ) accountOwnerUserId from partners p inner join tbl_TER_Territories t with (nolock) on t.ownerId = p.ownerId and t.fsl_tablecode = p.ownerType and t.territoryId = p.territoryId left outer join tbl_TER_Territories pt with (nolock) on pt.ownerId = t.ownerId and pt.fsl_tablecode = t.fsl_tablecode and pt.territoryId = t.parentTerritoryId ), accountOwners as ( select p.ownerId, p.ownerType, p.partnerType, p.partnerId, p.accountOwnerUserId, m.firstName accountOwnerFName, m.lastName accountOwnerLName, ltrim( coalesce( m.firstName, '' ) + ' ' + coalesce( m.lastName, '' )) accountOwnerFullName, m.ownerId accountOwnerOwnerId, m.fsl_tablecode accountOwnerOwnerType, rtrim( left( coalesce( m.firstName, '' ) + ' ', 1 )) + '. ' + coalesce( m.lastName, '' ) as accountOwnerShortFullName, case when m.fsl_tablecode = p.ownerType then 'Direct' else 'In-Direct' end accountOwnerType from partnersWithOwners p inner join tbl_Fspro_members m with (nolock) on m.ownerId = p.ownerId and m.fsl_tablecode = p.ownerType and m.fspro_userId = p.accountOwnerUserId where p.ownerId = and p.ownerType = ), businessOwnerChanges as ( select ct.ownerId, ct.ownerType, ct.contractId, ct.businessOwnerUserId, ao.accountOwnerUserId from tbl_TPM_Contracts ct inner join accountOwners ao on ao.ownerId = ct.ownerId and ao.ownerType = ct.ownerType and ao.partnerId = ct.partnerId and ao.partnerType = ct.partnerType where ct.ownerId = and ct.ownerType = and coalesce( ct.businessOwnerUserId, 0 ) <> coalesce( ao.accountOwnerUserId, 0 ) ) update ct set ct.businessOwnerUserId = businessOwnerChanges.accountOwnerUserId from tbl_TPM_Contracts ct inner join businessOwnerChanges on businessOwnerChanges.ownerId = ct.ownerId and businessOwnerChanges.ownerType = ct.ownerType and businessOwnerChanges.contractId = ct.contractId where ct.ownerId = and ct.ownerType = delete from bi..tbl_DW_AgreementAnalyzer_#_clientCode# where ownerId = and ownertype = with claimStats as ( select r.ownerId, r.ownerType, r.contractId, max( c.claimDate ) lastClaim, count( distinct cc.claimContractId ) subClaims from tbl_TPM_ClaimContracts cc with (nolock) inner join tbl_TPM_Claims c with (nolock) on c.claimId = cc.claimId and c.ownerId = cc.ownerId and c.ownerType = cc.ownerType inner join tbl_TPM_Requests r with (nolock) on r.ownerId = cc.ownerId and r.ownerType = cc.ownerType and r.requestId = cc.requestId where cc.reconciled = 'Y' and cc.ownerId = and cc.ownerType = group by r.ownerId, r.ownerType, r.contractId ), partners as ( select opr.ownerId, opr.fsltablecode as ownerType, 'OPR' as partnerType, opr.operatorId as partnerId, opr.companyName as partnerName, ter.territoryId, ter.name as territoryName, ter.internalSalesId as territoryNbr, opr.mfr_bsr_id accountOwnerUserId, ter.primaryManager as territoryManagerUserId, floor( 10191817 * sqrt( opr.operatorId * log10( opr.operatorId ))) as partnerTk, oprcompanyType as partnerCompanyType from tbl_OPR_CLientOperators opr with (nolock) left outer join tbl_TER_Territories ter with (nolock) on ter.ownerId = opr.ownerId and ter.fsl_tablecode = opr.fsltablecode and ter.territoryId = opr.territoryId where opr.ownerId = and opr.fsltablecode = and exists ( select * from tbl_TPM_Contracts ct with (nolock) where ct.ownerId = opr.ownerId and ct.ownerType = opr.fsltablecode and ct.partnerId = opr.operatorId and ct.partnerType = 'OPR' ) union select cdr.ownerId, cdr.fsl_tablecode as ownerType, 'CDR' as partnerType, cdr.cdr_recordId as partnerId, cdr.cdr_dstName as partnerName, ter.territoryId, ter.name as territoryName, ter.internalSalesId as territoryNbr, cdr.mfr_bsr_id accountOwnerUserId, ter.primaryManager as territoryManagerUserId, floor( 10191817 * sqrt( cdr.cdr_recordId * log10( cdr.cdr_recordId ))) as partnerTk, cdr_dstcompanytype as partnerCompanyType from tbl_CDR_Distributors cdr with (nolock) left outer join tbl_TER_Territories ter with (nolock) on ter.ownerId = cdr.ownerId and ter.fsl_tablecode = cdr.fsl_tablecode and ter.territoryId = cdr.cdr_territoryId where cdr.ownerId = and cdr.fsl_tablecode = and exists ( select * from tbl_TPM_Contracts ct with (nolock) where ct.ownerId = cdr.ownerId and ct.ownerType = cdr.fsl_tablecode and ct.partnerId = cdr.cdr_recordId and ct.partnerType = 'CDR' ) ), activeAgreements as ( select ct.ownerId, ct.ownerType, ct.partnerId, ct.partnerType, ct.assignedContractNbr, ct.contractName, ct.contractId, rt.requestType, rt.requestModel, r.termStart, r.termEnd, cs.subClaims, cs.lastClaim, p.partnerName, p.territoryId, p.territoryName, p.territoryNbr, p.accountOwnerUserId, ct.businessOwnerUserId, p.partnerTk, p.partnerCompanyType from tbl_TPM_Contracts ct with (nolock) inner join partners p on p.ownerId = ct.ownerId and p.ownerType = ct.ownerType and p.partnerId = ct.partnerId and p.partnerType = ct.partnerType inner join tbl_TPM_Requests r with (nolock) on r.contractId = ct.contractId and r.ownerId = ct.ownerId and r.ownerType = ct.ownerType and r.status = 'APPROVED' and getDate() between r.startDate and r.endDate inner join tbl_TPM_RequestTypes rt with (nolock) on rt.requestTypeId = r.requestTypeId and rt.ownerId = r.ownerId and rt.ownerType = r.ownerType left outer join claimStats cs on cs.ownerId = ct.ownerId and cs.ownerType = ct.ownerType and cs.contractId = ct.contractId where ct.ownerId = and ct.ownerType = ), expiredAgreements as ( select ct.ownerId, ct.ownerType, ct.partnerId, ct.partnerType, ct.assignedContractNbr, ct.contractName, ct.contractId, rt.requestType, rt.requestModel, r.termStart, r.termEnd, cs.subClaims, cs.lastClaim, p.partnerName, p.territoryId, p.territoryName, p.territoryNbr, p.accountOwnerUserId, ct.businessOwnerUserId, p.partnerTk,p.partnerCompanyType from tbl_TPM_Contracts ct with (nolock) inner join partners p on p.ownerId = ct.ownerId and p.ownerType = ct.ownerType and p.partnerId = ct.partnerId and p.partnerType = ct.partnerType inner join tbl_TPM_Requests r with (nolock) on r.contractId = ct.contractId and r.ownerId = ct.ownerId and r.ownerType = ct.ownerType and r.status = 'APPROVED' and r.chainSequence = ct.lastApprovedChainSequence and r.endDate < getDate() inner join tbl_TPM_RequestTypes rt with (nolock) on rt.requestTypeId = r.requestTypeId and rt.ownerId = r.ownerId and rt.ownerType = r.ownerType left outer join claimStats cs on cs.ownerId = ct.ownerId and cs.ownerType = ct.ownerType and cs.contractId = ct.contractId where ct.ownerId = and ct.ownerType = and ct.contractId not in ( select contractId from activeAgreements ) ) insert into bi..tbl_DW_AgreementAnalyzer_#_clientCode# ( ownerId, ownerType, partnerId, partnerType, partnerTk, assignedContractNbr, contractName, contractId, requestType, requestModel, termStart, termEnd, subClaims, lastClaim, partnerName, territoryId, territoryName, territoryNbr, accountOwnerUserId, businessOwnerUserId, agreementStatus,partnerCompanyType ) select ownerId, ownerType, partnerId, partnerType, partnerTk, assignedContractNbr, contractName, contractId, requestType, requestModel, termStart, termEnd, subClaims, lastClaim, partnerName, territoryId, territoryName, territoryNbr, accountOwnerUserId, businessOwnerUserId, 'active' as agreementStatus, partnerCompanyType from activeAgreements union all select ownerId, ownerType, partnerId, partnerType, partnerTk, assignedContractNbr, contractName, contractId, requestType, requestModel, termStart, termEnd, subClaims, lastClaim, partnerName, territoryId, territoryName, territoryNbr, accountOwnerUserId, businessOwnerUserId, 'expired' as agreementStatus,partnerCompanyType from expiredAgreements with agreementEndDates as ( select ct.ownerId, ct.ownerType, ct.partnerId, ct.partnerType, ct.contractId, ct.renew, r.termEnd agreementEndDate from tbl_TPM_COntracts ct with (nolock) inner join tbl_TPM_Requests r with (nolock) on r.ownerId = ct.ownerId and r.ownerType = ct.ownerType and r.contractId = ct.contractId and r.chainSequence = ct.lastApprovedChainSequence where ct.ownerId = and ct.ownerType = ) update a set a.renew = aed.renew, a.agreementEndDate = aed.agreementEndDate from bi..tbl_DW_AgreementAnalyzer_#_clientCode# a inner join agreementEndDates aed on aed.ownerId = a.ownerId and aed.ownerType = a.ownerType COLLATE Latin1_General_CI_AS and aed.contractId = a.contractId and aed.partnerId = a.partnerId and aed.partnerType = a.partnerType COLLATE Latin1_General_CI_AS where a.ownerId = and a.ownerType = with rolling12Volume as ( select ct.ownerId, ct.ownerType, ct.contractId, ct.partnerId, ct.partnerType, round( sum( pod.itemQty ), 0 ) rolling12ClaimVolume from tbl_TPM_ClaimContracts cc with (nolock) inner join tbl_TPM_Requests r with (nolock) on r.ownerId = cc.ownerId and r.ownerType = cc.ownerType and r.requestId = cc.requestId and r.volumeGenerator = 'Y' inner join tbl_TPM_ProofOfDelivery pod with (nolock) on pod.ownerId = cc.ownerId and pod.ownerType = cc.ownerType and pod.claimId = cc.claimId and pod.contractNbr = cc.contractNbr inner join tbl_TPM_Contracts ct with (nolock) on ct.ownerId = r.ownerId and ct.ownerType = r.ownerType and ct.contractId = r.contractId inner join tbl_TPM_Claims c with (nolock) on c.claimId = cc.claimId and c.ownerId = cc.ownerId and c.ownerType = cc.ownerType where ct.ownerId = and ct.ownerType = and cc.reconciled = 'Y' and pod.status != 'DENIED' and datediff( day, cc.lastDate, getDate() ) <= 365 group by ct.ownerId, ct.ownerType, ct.contractId, ct.partnerId, ct.partnerType ) update a set a.rolling12ClaimVolume = coalesce( r12v.rolling12ClaimVolume, 0 ) from bi..tbl_DW_AgreementAnalyzer_#_clientCode# a left outer join rolling12Volume r12v on r12v.ownerId = a.ownerId and r12v.ownerType = a.ownerType COLLATE Latin1_General_CI_AS and r12v.contractId = a.contractId and r12v.partnerId = a.partnerId and r12v.partnerType = a.partnerType COLLATE Latin1_General_CI_AS where a.ownerId = and a.ownerType = update a set a.accountOwnerFirstName = m.firstName, a.accountOwnerLastName = m.lastName, a.accountOwnerEmail = m.email from bi..tbl_DW_AgreementAnalyzer_#_clientCode# a inner join tbl_Fspro_members m on m.fspro_userId = a.accountOwnerUserId and m.ownerId > 0 where a.ownerId = and a.ownerType =