Scripts for Reassigning a Dispatch Follow

The following SQL statements will disassociate the dispatch with any orders associated with it. The original documents will still contain the claim number.

Just replace the green text below with the claim number. Execute only the code that is not in pink below.

If you want to move the dispatch to an existing invoice with the claim number, then put the document number in the first pink text and execute the entire script, including the code in pink at the bottom.

 

declare @dispatchNumber as varchar(50)
declare @documentNumber as varchar(50)
declare @dispatchGUID as uniqueidentifier 
declare @documentClaimGUID as uniqueidentifier  
set @dispatchNumber = '441556698'
set @documentNumber = 'I01008188'


-- find existing dispatch
select top 1 * from eHubDispatch 
where eHubDispatch.DispatchNumber = @dispatchNumber

-- assign dispatchGUID to variable
select top 1 @dispatchGUID = eHubDispatchGUID from eHubDispatch 
where eHubDispatch.DispatchNumber = @dispatchNumber

-- find the target documentclaim
select DocumentClaimData.* from DocumentClaimData 
inner join Document on Document.DocumentClaimDataGUID_FK = DocumentClaimData.DocumentClaimDataGUID
where Document.DocumentNumber = @documentNumber

-- assign documentClaimGUID to variable
select top 1 @documentClaimGUID = DocumentClaimDataGUID from DocumentClaimData
inner join Document on Document.DocumentClaimDataGUID_FK = DocumentClaimData.DocumentClaimDataGUID where Document.DocumentNumber = @documentNumber -- see if that dispatch is attached to a documentclaim select DocumentClaimData.* from DocumentClaimData where DocumentClaimData.eHubDispatchGUID_FK = @dispatchGUID -- if dispatch is already attached, remove the dispatchguid if exists(select DocumentClaimData.* from DocumentClaimData where DocumentClaimData.eHubDispatchGUID_FK = @dispatchGUID) begin update DocumentClaimData set DocumentClaimData.eHubDispatchGUID_FK = NULL where DocumentClaimData.eHubDispatchGUID_FK = @dispatchGUID end -- update target documentclaim with dispatchguid update DocumentClaimData set DocumentClaimData.eHubDispatchGUID_FK = @dispatchGUID where DocumentClaimDataGUID = @documentClaimGUID
Have more questions? Submit a request

Comments

Powered by Zendesk