﻿--Add any work orders split from flagged work orders
WITH SP_WO AS
(
    SELECT DISTINCT wo.WorkOrderNumber,
           wo.LotNumber,
           wo.BranchCode, 
           wo.ShortItemNumber
    FROM   dbo.WorkOrder AS wo INNER JOIN
           #Temp_WO AS tw_o ON (wo.ParentWorkOrderNumber = CAST(tw_o.WorkOrderNumber AS VARCHAR(8)) AND wo.BranchCode = tw_o.BranchCode)
)
MERGE #Temp_WO AS TARGET
USING SP_WO AS SOURCE
ON    (TARGET.WorkOrderNumber = SOURCE.WorkOrderNumber AND TARGET.BranchCode = SOURCE.BranchCode)
WHEN  MATCHED THEN
	UPDATE SET TARGET.SplitOrder = 1
WHEN NOT MATCHED BY TARGET THEN
	INSERT (WorkOrderNumber, LotNumber, BranchCode, ShortItemNumber, SplitOrder)
	VALUES (SOURCE.WorkOrderNumber, SOURCE.LotNumber, SOURCE.BranchCode, SOURCE.ShortItemNumber, 1);