Error: The MERGE statement attempted to UPDATE or DELETE the same row more than once
Solution: Find the Production Order with a Duplicate PLU
--[Innova].[sfsp_Update_Production_Orders]
DECLARE @RUNDATE DATE
SET @RUNDATE = DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
SET @RUNDATE =
(CASE
WHEN DATEPART(HOUR,GETDATE()) = 8
THEN DATEADD(month, DATEDIFF(MONTH, 0, getdate()), 0)
ELSE DATEADD(DAY,-32,GETDATE())
END )
--SELECT @RUNDATE
SELECT ord.code AS OrderNumber, pm.code AS Plu
FROM innova.dbo.proc_orders ord WITH (NOLOCK)
LEFT JOIN innova.dbo.proc_orderl ol WITH (NOLOCK) ON ord.[order] = ol.[order]
LEFT JOIN innova.dbo.base_companies comp WITH (NOLOCK) ON ord.customer = comp.company
LEFT JOIN innova.dbo.proc_materials pm WITH (NOLOCK) ON ol.material = pm.material
LEFT JOIN Sovfoods_Integration.Innova.Production_Orders po ON ord.code = po.OrderNumber AND pm.code = po.Plu
LEFT JOIN Sovfoods_Integration.SovMS.vw_Product_Groupings pg ON pm.code = pg.plu COLLATE SQL_Latin1_General_CP1_CI_AS
LEFT JOIN
(
select sum(p.nominal) as nominal, p.porderline from innova.dbo.proc_packs p
where p.rtype in (1,6,12)
group by p.porderline
) pman on ol.id = pman.porderline
LEFT JOIN
(
SELECT DISTINCT Date_Nme AS Date,Plu AS Plu,Transfer_Mass AS Reworks
FROM [SOVBI].[dw_Datawarehouse].[dbo].[sfvw_Total_Stock_Transfers_By_Plu_And_Day_FPP]
WHERE Date_Nme >= @RUNDATE
GROUP BY Date_Nme,Plu,Transfer_Mass
) re on re.Plu = pm.code AND re.Date = CONVERT(VARCHAR(10),CONVERT(DATE,ord.created))
WHERE ord.ordertype = 3 -- Production Orders
AND PM.CODE IS NOT NULL
AND CONVERT(DATE,ord.dispatchtime) >= @RUNDATE
GROUP BY ord.code, pm.code
having COUNT(pm.code) > 1 and count(OrderNumber) > 1