select r.assignedContractNbr obsoleteAssignedContractNbr, r.ownerId, r.ownerType, r.requestId, r.comments, c.partnerId, c.partnerType, c.contractName, r.startDate, r.endDate, 0 chainSequence, 0 lastApprovedChainSequence, c.contractId obsoleteContractId
from tbl_TPM_Requests r
inner join tbl_TPM_Contracts c
on c.ownerId = r.ownerId
and c.ownerType = r.ownerType
and c.contractId = r.contractId
where r.ownerId = 1
and r.ownerType ='MFR'
and r.comments like 'Blacksmith Promotion: %'
and r.status = 'APPROVED'
and not ( r.comments like '%' + r.assignedContractNbr + '%' )
select r.assignedContractNbr obsoleteAssignedContractNbr, r.ownerId, r.ownerType, r.requestId, r.comments, rp.partnerId, rp.partnerType, r.description contractName, r.startDate, r.endDate, 0 chainSequence, 0 lastApprovedChainSequence, 0 obsoleteContractId
from tbl_TPM_Requests r
left outer join tbl_TPM_Contracts c
on c.ownerId = r.ownerId
and c.ownerType = r.ownerType
and c.contractId = r.contractId
left outer join tbl_TPM_RequestPartners rp
on rp.ownerId = r.ownerId
and rp.ownerType = r.ownerType
and rp.requestId = r.requestId
and rp.primaryPartner = 'Y'
where r.ownerId = 1
and r.ownerType ='MFR'
and r.comments like 'Blacksmith Promotion: %'
and r.status = 'APPROVED'
and c.contractId is null
and not ( r.comments like '%' + r.assignedContractNbr + '%' )
select *
from qmd_blacksmithRequests
where blacksmithPromotionNbr <> obsoleteAssignedContractNbr
order by BlacksmithPromotionNbr, chainSequence
select * from qmd_blacksmithRequests where blacksmithPromotionNbr = '1012472_5'
select contractId
from tbl_TPM_Contracts
where ownerId =
and ownerType =
and partnerId =
and partnerType =
and assignedContractNbr =
insert into tbl_TPM_Contracts ( ownerId, ownerType, partnerId, partnerType, contractName, assignedContractNbr, lastApprovedChainSequence )
values ( , ,
, , ,
,
)
New Contract is #blacksmithPromotionNbr#
Existing Contract is #blacksmithPromotionNbr#
update tbl_TPM_Requests
set assignedContractNbr = ,
sourceRequestId = ,
contractId = ,
chainSequence =
where ownerId =
and ownerType =
and requestId =
update tbl_TPM_Requests
set sourceRequestId = null
where ownerId =
and ownerType =
and sourceRequestId =
update tbl_TPM_Contracts
set lastApprovedChainSequence =
where ownerId =
and ownerType =
and contractId =
update tbl_TPM_Contracts
set ownerId = -1, ownerType = 'RFM'
where ownerId = 1 and ownerType = 'MFR'
and contractId =