﻿/* Add downlevel work orders that were issued material from flagged work orders */
MERGE INTO TEMP_WO TARGET
USING
(
  select distinct WADOCO, COALESCE(WALOTN, CAST(WADOCO AS CHAR(8))) WALOTN, WAMMCU, WAITM
  from F3111_WOC woc inner join
       F4801_WO wo on (woc.WMDOCO = wo.WADOCO) inner join
       TEMP_WO t_wo on (woc.WMLOTN = t_wo.LOTNUMBER and woc.WMCPIT = t_wo.SHORTITEMNUMBER)
) SOURCE on (TARGET.WORKORDERNUMBER = SOURCE.WADOCO)
WHEN MATCHED THEN
  UPDATE SET PARTSLIST = 'T'
WHEN NOT MATCHED THEN
  INSERT (WORKORDERNUMBER, LOTNUMBER, BRANCHCODE, SHORTITEMNUMBER, PARTSLIST)
  VALUES (SOURCE.WADOCO, SOURCE.WALOTN, SOURCE.WAMMCU, SOURCE.WAITM, 'T');

MERGE INTO TEMP_WO TARGET
USING
(
  select distinct WADOCO, COALESCE(WALOTN, CAST(WADOCO AS CHAR(8))) WALOTN, WAMMCU, WAITM
  from F4111_CARDEX lu inner join
       F4801_WO wo on (lu.ILDOCO = wo.WADOCO) inner join
       TEMP_WO t_wo on (lu.ILLOTN = t_wo.LOTNUMBER and lu.ILITM = t_wo.SHORTITEMNUMBER)
) SOURCE on (TARGET.WORKORDERNUMBER = SOURCE.WADOCO)
WHEN MATCHED THEN
  UPDATE SET CARDEX = 'T'
WHEN NOT MATCHED THEN
  INSERT (WORKORDERNUMBER, LOTNUMBER, BRANCHCODE, SHORTITEMNUMBER, CARDEX)
  VALUES (SOURCE.WADOCO, SOURCE.WALOTN, SOURCE.WAMMCU, SOURCE.WAITM, 'T');