truncate table [tbl_DW_AgreementAnalyzer_#_clientCode#]; 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, ter.territoryPath 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 all 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, ter.territoryPath 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.territoryPath, p.accountOwnerUserId, ct.businessOwnerUserId, p.partnerTk, p.partnerCompanyType from tbl_TPM_Contracts ct with (nolock) inner join partners p with (nolock) 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.territoryPath, p.accountOwnerUserId, ct.businessOwnerUserId, p.partnerTk,p.partnerCompanyType from tbl_TPM_Contracts ct with (nolock) inner join partners p with (nolock) 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 tbl_DW_AgreementAnalyzer_#_clientCode# ( ownerId, ownerType, partnerId, partnerType, partnerTk, assignedContractNbr, contractName, contractId, requestType, requestModel, termStart, termEnd, subClaims, lastClaim, partnerName, territoryId, territoryName, territoryNbr, territoryPath, accountOwnerUserId, businessOwnerUserId, agreementStatus,partnerCompanyType ) select ownerId, ownerType, partnerId, partnerType, partnerTk, assignedContractNbr, contractName, contractId, requestType, requestModel, termStart, termEnd, subClaims, lastClaim, partnerName, territoryId, territoryName, territoryNbr, territoryPath, 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, territoryPath, 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 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 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 = with rolling12Volume as ( select ct.ownerId, ct.ownerType, ct.contractId, ct.partnerId, ct.partnerType, round( sum( tpf.forecast_qty ), 0 ) rolling12AgreementVolume from tbl_TPM_Requests r with (nolock) 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_MFR_TPF_Detail#_tpfSuffix# tpf with (nolock) on tpf.mfr_id = r.ownerId and tpf.rebateContractId = r.requestId and tpf.forecast_status = 'C' and tpf.tPartnerType = ct.partnerType and tpf.tPartnerId = ct.partnerId where ct.ownerId = and ct.ownerType = and r.volumeGenerator = 'Y' and datediff( month, dateFromParts( cast( forecast_year as int ), cast( forecast_month as int ), 1 ), dateFromParts( year(getDate()), month(getDate()), 1 ) ) between 0 and 11 group by ct.ownerId, ct.ownerType, ct.contractId, ct.partnerId, ct.partnerType ) update a set a.rolling12AgreementVolume = coalesce( r12v.rolling12AgreementVolume, 0 ) from 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 tbl_DW_AgreementAnalyzer_#_clientCode# a inner join tbl_Fspro_members m with (nolock) on m.fspro_userId = a.accountOwnerUserId and m.ownerId > 0 where a.ownerId = and a.ownerType = insert into tbl_EAI_inboundBatchMessages( batchId, lineNumber, importId, message, messageLevel ) values ( , null, null, , 'INFO' )

#htmleditformat( _timingMessage )#