SMS_Registros duplicados en tablas de surtido

Se tiene identificado un escenario en el cual se duplican registros en las tablas de surtido de una orden eccomerce de tipo CICO, al hacer una consulta a la tabla Mobile.PickingOrder por el campo Ordernumber tendremos 3 registrso de la orden reportada como se muestra a continuacion.

select  * from Mobile.PickingOrder WITH (NOLOCK) where OrderNumber = 12345678 --Numero de orden CICO

Para este caso se tiene la siguiente mitigacion que consiste en depurar los registros duplicados de las tablas de surtido en la BD de tienda que nos reporta el incidente, solo como dato de entrada en el query podremos el numero de orden, posteriormente llamamos a tienda y notificamos al usuario para que valide de nuevo.

El ticket se cierra en el *Servicio Gestión de Tiendas (SMS) CISMS – Surtido

DECLARE	@STORE INT = (select storenumber from Store  where StoreType = 1)
DECLARE	@OrderNumber INT =  35609616

SELECT	COUNT(*) AS 'Conteo',OrderNumber,PO.RetailStore
INTO	#PO	
FROM	Mobile.PickingOrder PO WITH(NOLOCK)
	WHERE	PO.[IF] = '270' AND RetailStore <> @STORE 
			AND PO.CreatedDateTime >= '2022-06-01'
			AND PO.OrderNumber=@OrderNumber
GROUP BY PO.OrderNumber,RetailStore
HAVING	COUNT(OrderNumber) = 3 



SELECT TOP 2 PO.TranNumber,PO.OrderID,PO.TranDate,PO.CreatedDateTime
INTO	#TRAN
FROM	Mobile.PickingOrder po  WITH(NOLOCK)
		INNER JOIN #PO
			ON PO.OrderNumber=#PO.OrderNumber
ORDER BY 4 DESC

SELECT	'ANTES',* 
FROM	Mobile.PickingOrder PO WITH(NOLOCK)
		INNER JOIN Mobile.PickingOrderDetail POD WITH(NOLOCK)
			ON POD.OrderID=PO.OrderID
		LEFT JOIN Mobile.PickingLog PL WITH(NOLOCK)
			ON PL.OrderID=PO.OrderID AND PL.OrderID=POD.OrderID
WHERE	PO.OrderNumber=@OrderNumber  



DELETE mobile.PickingTicket where OrderID IN (select OrderID from #TRAN NOLOCK)
DELETE Mobile.PickingTicketDetail where TicketID IN ( select ticketID from mobile.PickingTicket where OrderID IN (select OrderID from #TRAN NOLOCK))-- where OrderID IN (select OrderID from #TRAN NOLOCK)
delete   Mobile.PickingOrderDetailException  where OrderID IN (select OrderID from #TRAN NOLOCK)
delete   Mobile.PickingOrderDetail  where OrderID IN (select OrderID from #TRAN NOLOCK)

DELETE	POD
FROM	Mobile.PickingOrder PO 
		INNER JOIN #TRAN T
			ON PO.TranNumber=T.TranNumber AND PO.OrderID = T.OrderID
		LEFT JOIN Mobile.PickingOrderDetail POD
			ON POD.OrderID=T.OrderID AND POD.OrderID=PO.OrderID
WHERE	PO.OrderNumber=@OrderNumber


DELETE	PO
FROM	Mobile.PickingOrder PO INNER JOIN #TRAN T
			ON PO.TranNumber=T.TranNumber AND PO.OrderID = T.OrderID
WHERE	PO.OrderNumber=@OrderNumber

DELETE Mobile.PickingLog  where Orderid IN (select OrderID from #TRAN NOLOCK)

SELECT	'DESPUES',* 
FROM	Mobile.PickingOrder PO WITH(NOLOCK)
		INNER JOIN Mobile.PickingOrderDetail POD WITH(NOLOCK)
			ON POD.OrderID=PO.OrderID
		LEFT JOIN Mobile.PickingLog PL WITH(NOLOCK)
			ON PL.OrderID=PO.OrderID AND PL.OrderID=POD.OrderID
WHERE	PO.OrderNumber=@OrderNumber 


DROP TABLE #PO
DROP TABLE #TRAN

Depurar unicamente una orden con registro duplicado de años anteriores de la tabla de surtido, favor de usar el siguiente delete

declare @orden INT = 732477
declare @orderID INT
Select   @orderID = OrderID  from Mobile.PickingOrder  where ordernumber =@orden and OrderStatus = 4 
--Select @orderID
DELETE mobile.PickingTicket where OrderID IN (@orderID)
DELETE Mobile.PickingTicketDetail where TicketID IN ( select ticketID from mobile.PickingTicket where OrderID IN (@orderID))-- where OrderID IN (select OrderID from #TRAN NOLOCK)
DELETE   Mobile.PickingOrderDetailException  where OrderID IN (@orderID)
DELETE   Mobile.PickingOrderDetail  where OrderID IN (@orderID)
DELETE Mobile.PickingOrderDetail where OrderID = @orderID
DELETE	PO
FROM	Mobile.PickingOrder PO
WHERE	PO.OrderNumber=@orden  AND OrderID = @orderID

DELETE Mobile.PickingLog  where Orderid IN (@orderID)