--Add any work orders issued material from flagged work orders
WITH CWO_D AS(
    SELECT DISTINCT wo.WorkOrderNumber,
           wo.LotNumber,
		   wo.BranchCode,
		   wo.ShortItemNumber
    FROM   dbo.WorkOrderComponent AS woc INNER JOIN
		   dbo.WorkOrder AS wo ON (woc.WorkOrderNumber = wo.WorkOrderNumber) INNER JOIN
           #Temp_WO AS t_wo ON (woc.LotNumber = t_wo.LotNumber AND woc.ShortItemNumber = t_wo.ShortItemNumber)
)
MERGE #Temp_WO AS TARGET
USING CWO_D AS SOURCE
ON    (TARGET.WorkOrderNumber = SOURCE.WorkOrderNumber)
WHEN  MATCHED THEN
	UPDATE SET TARGET.PartsList = 1
WHEN  NOT MATCHED THEN
	INSERT (WorkOrderNumber, LotNumber, BranchCode, ShortItemNumber, PartsList)
	VALUES (SOURCE.WorkOrderNumber, COALESCE(SOURCE.LotNumber, CAST(SOURCE.WorkOrderNumber AS VARCHAR(8))), SOURCE.BranchCode, SOURCE.ShortItemNumber, 1);      

WITH CWO_D AS(
    SELECT DISTINCT wo.WorkOrderNumber,
           wo.LotNumber,
		   wo.BranchCode,
		   wo.ShortItemNumber
    FROM   dbo.LotUsage AS lu INNER JOIN
		   dbo.WorkOrder AS wo ON (lu.WorkOrderNumber = wo.WorkOrderNumber) INNER JOIN
           #Temp_WO AS t_wo ON (lu.LotNumber = t_wo.LotNumber AND lu.ShortItemNumber = t_wo.ShortItemNumber)
)
MERGE #Temp_WO AS TARGET
USING CWO_D AS SOURCE
ON    (TARGET.WorkOrderNumber = SOURCE.WorkOrderNumber)
WHEN  MATCHED THEN
	UPDATE SET TARGET.CARDEX = 1
WHEN  NOT MATCHED THEN
	INSERT (WorkOrderNumber, LotNumber, BranchCode, ShortItemNumber, CARDEX)
	VALUES (SOURCE.WorkOrderNumber, COALESCE(SOURCE.LotNumber, CAST(SOURCE.WorkOrderNumber AS VARCHAR(8))), SOURCE.BranchCode, SOURCE.ShortItemNumber, 1);