Running in Preview Mode

Running in APPLY Mode

with repeated as ( select r.contractId, r.assignedContractNbr, r.promoNbr, count( distinct r.requestId ) agreementCount, max( r.chainSequence ) maxChainSequence, min( r.chainSequence ) minChainSequence from tbl_TPM_Requests r with (nolock) inner join tbl_TPM_Contracts ct with (nolock) on ct.contractId = r.contractId and ct.ownerId = r.ownerId and ct.ownerType = r.ownerType and ct.externalAgreement = 'Y' where r.ownerId = and r.ownerType = and r.status = 'APPROVED' group by r.contractId, r.assignedContractNbr, r.promoNbr having count( distinct r.requestId ) > 1 ), badSequence as ( select r.contractId, r.assignedContractNbr, r.promoNbr, r.assignedContractNbr + '.' + right( '000' + right( r.promoNbr, len( r.promoNbr ) - len( r.assignedContractNbr ) - 1), 3) revisedPromoNbr from tbl_TPM_Contracts ct inner join tbl_TPM_Requests r on r.contractId = ct.contractId and r.ownerId = ct.ownerId and r.ownerType = ct.ownerType where ct.externalAgreement = 'Y' and len( r.promoNbr ) - len( r.assignedContractNbr ) < 4 and left( r.promoNbr, len( r.assignedContractNbr )) = r.assignedContractNbr and ct.ownerId = and ct.ownerType = ) select ct.ownerId, ct.ownerType, ct.contractId, ct.assignedContractNbr, ct.contractName, count( distinct r.requestId ) requests, sum( case when r.status = 'DRAFT' then 1 else 0 end ) drafts, ( select count(*) from badSequence where badSequence.contractId = ct.contractId ) badSequences, ( select count(*) from repeated where repeated.contractId = ct.contractId ) repeats from tbl_TPM_Contracts ct with (nolock) inner join tbl_TPM_Requests r with (nolock) on r.contractId = Ct.contractId and r.ownerId = ct.ownerId and r.ownerType = ct.ownerType where ( ct.contractId in ( select contractId from badSequence ) or ct.contractId in ( select contractId from repeated )) and ct.ownerId = and ct.ownerType = and ct.assignedContractNbr = group by ct.ownerId, ct.ownerType, ct.assignedContractNbr, ct.contractName, ct.contractId

#htmleditformat( _r.sql )#

#htmleditformat( assignedContractnbr )# - #htmleditformat( contractName )#

select r.contractId, r.assignedContractNbr, r.promoNbr, count( distinct r.requestId ) agreementCount, max( r.chainSequence ) maxChainSequence, min( r.chainSequence ) minChainSequence from tbl_TPM_Requests r with (nolock) inner join tbl_TPM_Contracts ct with (nolock) on ct.contractId = r.contractId and ct.ownerId = r.ownerId and ct.ownerType = r.ownerType and ct.externalAgreement = 'Y' where r.ownerId = and r.ownerType = and r.status = 'APPROVED' and r.contractId = group by r.contractId, r.assignedContractNbr, r.promoNbr having count( distinct r.requestId ) > 1 order by agreementCount desc, r.contractId
select ct.assignedContractNbr, ct.contractName, ct.lastApprovedChainSequence, r.requestId, r.promoNbr, r.chainSequence, r.status, r.sourceRequestId, r.startDate, r.endDate, r.termStart, r.termEnd from tbl_TPM_Contracts ct inner join tbl_TPM_Requests r on r.contractId = ct.contractId and r.ownerId = ct.ownerId and r.ownerType = ct.ownerType where ct.ownerId = and ct.ownerType = and ct.contractId = order by r.chainSequence desc with toFix as ( select r.ownerId, r.ownerType, r.contractId, r.requestId, r.assignedContractNbr, r.promoNbr, r.assignedContractNbr + '.' + right( '000' + right( r.promoNbr, len( r.promoNbr ) - len( r.assignedContractNbr ) - 1), 3) revisedPromoNbr from tbl_TPM_Contracts ct inner join tbl_TPM_Requests r on r.contractId = ct.contractId and r.ownerId = ct.ownerId and r.ownerType = ct.ownerType where ct.externalAgreement = 'Y' and len( r.promoNbr ) - len( r.assignedContractNbr ) < 4 and left( r.promoNbr, len( r.assignedContractNbr )) = r.assignedContractNbr and ct.ownerId = and ct.ownerType = and ct.contractId = ) update r set r.promoNbr = tofix.revisedPromoNbr from tbl_TPM_Requests r inner join toFix on tofix.requestId = r.requestId and toFix.ownerId = r.ownerId and toFix.ownerType = r.ownerType

Fixed #_r.recordCount# Sequences

select requestId from tbl_TPM_Requests with (nolock) where contractId = and ownerId = and ownerType = and status ='DRAFT'

Draft RequestId (#listLen( _draftRequestIds )#) #_draftRequestIds#

select a.requestId approvedRequestId 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 and ct.externalAgreement = 'Y' inner join tbl_TPM_Requests a with (nolock) on a.ownerId = r.ownerId and a.ownerType = r.ownerType and a.contractId = r.contractId and a.startDate = r.startDate and a.endDate = r.endDate and a.status = 'APPROVED' where r.status = 'DRAFT' and r.requestId = and r.contractId = and exists ( select * from tbl_MFR_TPF_Detail#_tpfSuffix# tpf with (nolock) where tpf.rebateContractId = r.requestId and tpf.mfr_id = r.ownerId ) and r.ownerId = and r.ownerType = update tbl_MFR_TPF_Detail#_tpfSuffix# set rebateContractId = where mfr_id = and 'MFR' = and rebateContractId = Removing Drafts Updated #_r.recordCount# TPF Records update tbl_TPM_Contracts set flaggedForRecalc = 'Y', flaggedForRecalcTime = where ownerId = and ownerType = and contractId = delete from tbl_TPM_Requests where ownerId = and ownerType = and requestId = and contractId =

Removed #_r.recordCount# draft Agreements

select chainSequence, sourceRequestId, contractId, requestId, volumeGenerator from tbl_TPM_Requests with (nolock) where promoNbr = and ownerId = and ownerType = and status ='APPROVED' order by requestId desc

PromoNbr #arguments.promoNbr#

ContractId #_contractId#

Keep RequestId #_keepRequestId#

Old ChainSequence #_oldChainSequence#

New ChainSequence #_newChainSequence#

New SourceRequestId #_newSourceRequestId#

Old VolumeGenerator #_oldVolumeGenerator#

New VolumeGenerator #_newVolumeGenerator#

Remove RequestId (#listLen( _removeRequestIds )#) #_removeRequestIds#

update tbl_TPM_ClaimContracts set requestId = where ownerId = and ownerType = and requestId in ( ) Updated #_r.recordCount# Sub-Claims update tbl_MFR_TPF_Detail#_tpfSuffix# set rebateContractId = where mfr_id = and 'MFR' = and rebateContractId in ( ) Updated #_r.recordCount# TPF Records update tbl_TPM_Contracts set flaggedForRecalc = 'Y', flaggedForRecalcTime = where ownerId = and ownerType = and contractId =

Flagged Contract (#_contractId#) for Recalc

update tbl_TPM_Requests set chainSequence = , sourceRequestId = where ownerId = and ownerType = and requestId =

Updated Keep

update tbl_TPM_Requests set volumeGenerator = where ownerId = and ownerType = and requestId =

VolumeGenerator changed from #_oldVolumeGenerator# to #_newVolumeGenerator#

update tbl_TPM_Contracts set flaggedForRecalc = 'Y', flaggedForRecalcTime = where ownerId = and ownerType = and contractId =

Flagged Contract (#_contractId#) for Recalc

update tbl_TPM_Contracts set lastApprovedChainSequence = where ownerId = and ownerType = and contractId = and lastApprovedChainSequence =

Contract lastApprovedChainSequence changed from #_oldChainSequence# to #_newChainSequence#

delete from tbl_TPM_Requests where ownerId = and ownerType = and requestId in ( )

Removed #_r.recordCount# repeated Agreements

delete from tbl_TPM_RequestProducts where ownerId = and ownerType = and requestId in ( ); delete from tbl_TPM_RequestExclusions where ownerId = and ownerType = and requestId in ( ); delete from tbl_TPM_RequestPartners where ownerId = and ownerType = and requestId in ( );