with x as ( select r.status, r.contractId, r.ownerId, r.ownerType, r.requestId, r.assignedContractNbr, r.promoNbr, case when rt.primaryPartnerType = rt.payableTo then 'X' else 'Z' end + case when rt.requestModel = 'FIXED' then 'F' when rt.requestModel = 'INCENTIVE' then 'P' else 'A' end requestVariant , ( select count( distinct claimId ) from tbl_TPM_ClaimContracts cc where cc.requestId = r.requestId and cc.ownerId = r.ownerId and cc.ownerType = r.ownerType ) as subClaims from tbl_TPM_Requests r left outer join tbL_TPM_Contracts ct on ct.ownerId = r.ownerId and ct.ownerType = r.ownerType and ct.contractId = r.contractId inner join tbl_TPM_RequestTypes rt on rt.requestTypeId= r.requestTypeId and rt.ownerId = r.ownerId and rt.ownerType = r.ownerType where r.ownerId > 0 and len( r.assignedContractNbr ) = 7 and not r.assignedContractNbr like '%.%' and r.promoNbrSave is null ) select assignedContractNbr + '.' + requestVariant as newAssignedContractNbr, replace( promoNbr, assignedContractNbr, assignedContractNbr + '.' + requestVariant ) as newPromoNbr, * from x order by contractId delete tbl_TPM_Requests where ownerId = and ownerType = and requestId = update tbl_TPM_Requests set assignedContractNbr = , promoNbr = , assignedContractNbrSave = , promoNbrSave = where ownerId = and ownerType = and requestId = update tbl_TPM_Contracts set assignedContractNbr = , assignedContractNbrSave = where ownerId = and ownerType = and contractId = select r.status, r.contractId, r.ownerId, r.ownerType, r.requestId, r.assignedContractNbr, r.promoNbr, case when rt.primaryPartnerType = rt.payableTo then 'X' else 'Z' end + case when rt.requestModel = 'FIXED' then 'F' when rt.requestModel = 'INCENTIVE' then 'P' else 'A' end requestVariant , ( select count( distinct claimId ) from tbl_TPM_ClaimContracts cc where cc.requestId = r.requestId and cc.ownerId = r.ownerId and cc.ownerType = r.ownerType ) as subClaims , ct.assignedContractNbrSave, r.assignedContractNbrSave, r.promoNbrSave from tbl_TPM_Requests r left outer join tbL_TPM_Contracts ct on ct.ownerId = r.ownerId and ct.ownerType = r.ownerType and ct.contractId = r.contractId inner join tbl_TPM_RequestTypes rt on rt.requestTypeId= r.requestTypeId and rt.ownerId = r.ownerId and rt.ownerType = r.ownerType where r.ownerId > 0 and r.assignedContractNbrSave is not null order by ownerId, contractId

Changes committed

Changes rolled back