ALTER TABLE cf_Stamp ADD weldRodLotNo VARCHAR(50);

ALTER TABLE cf_StampMark ADD xRayAccept VARCHAR(255);
ALTER TABLE cf_StampMark ADD ptAccept VARCHAR(255);
ALTER TABLE cf_StampMark ADD dimensionalAccept VARCHAR(255);

UPDATE sm
SET
  sm.xRayAccept = st.xRayAccept,
  sm.ptAccept = st.ptAccept,
  sm.dimensionalAccept = st.dimensionalAccept
FROM cf_StampMark sm
INNER JOIN cf_Stamp st ON st.id = sm.stampId;


ALTER TABLE cf_Stamp DROP COLUMN xRayAccept;
ALTER TABLE cf_Stamp DROP COLUMN ptAccept;
ALTER TABLE cf_Stamp DROP COLUMN dimensionalAccept;


-- update scrap codes
UPDATE cf_StampMark SET scrapCode = 'Porosity Gas'          WHERE scrapCode = 'POR. GAS';
UPDATE cf_StampMark SET scrapCode = 'Oxides'                WHERE scrapCode = 'F. MAT / DR / OXIDE';
UPDATE cf_StampMark SET scrapCode = 'Shrinkage'             WHERE scrapCode = 'SHRINK';
UPDATE cf_StampMark SET scrapCode = 'Crack'                 WHERE scrapCode = 'CRACK';
UPDATE cf_StampMark SET scrapCode = 'Overcleaned'           WHERE scrapCode = 'OVERCLEAN';
UPDATE cf_StampMark SET scrapCode = 'Handling Damage'       WHERE scrapCode = 'HANDLING DAMAGE';
UPDATE cf_StampMark SET scrapCode = 'Broken Core'           WHERE scrapCode = 'BROKEN CORE';
UPDATE cf_StampMark SET scrapCode = 'Molding'               WHERE scrapCode = 'MOLDING';
UPDATE cf_StampMark SET scrapCode = 'Out of Dimension'      WHERE scrapCode = 'OUT DIM';
UPDATE cf_StampMark SET scrapCode = 'Other'                 WHERE scrapCode = 'OTHER';
UPDATE cf_StampMark SET scrapCode = 'Saw Cut'               WHERE scrapCode = 'SAW CUT';
UPDATE cf_StampMark SET scrapCode = 'Wax Positive Defect'   WHERE scrapCode = 'WAX POSITIVE/DEFECT';
UPDATE cf_StampMark SET scrapCode = 'Bent'                  WHERE scrapCode = 'BENT';
UPDATE cf_StampMark SET scrapCode = 'Blow'                  WHERE scrapCode = 'BLOW';
UPDATE cf_StampMark SET scrapCode = 'Crush'                 WHERE scrapCode = 'CRUSH';
UPDATE cf_StampMark SET scrapCode = 'Set Up Bad'            WHERE scrapCode = 'SETUP BAD';
UPDATE cf_StampMark SET scrapCode = 'Shell Spall'           WHERE scrapCode = 'POSITIVE METAL SHELL SPALL';
UPDATE cf_StampMark SET scrapCode = 'Shell Inclusion'       WHERE scrapCode = 'SHELL INCLUSION';
UPDATE cf_StampMark SET scrapCode = 'Core Float'            WHERE scrapCode = 'CORE FLOAT';
UPDATE cf_StampMark SET scrapCode = 'Targeting'             WHERE scrapCode = 'TARGETING';
UPDATE cf_StampMark SET scrapCode = 'Negative Parting Line' WHERE scrapCode = 'NEGATIVE PARTING LINE';
UPDATE cf_StampMark SET scrapCode = 'Shift'                 WHERE scrapCode = 'SHIFT';
UPDATE cf_StampMark SET scrapCode = 'Core Drop'             WHERE scrapCode = 'CORE DROP';
UPDATE cf_StampMark SET scrapCode = 'Buckle'                WHERE scrapCode = 'BUCKLE';
UPDATE cf_StampMark SET scrapCode = 'Glue'                  WHERE scrapCode = 'GLUE';
UPDATE cf_StampMark SET scrapCode = 'Slag'                  WHERE scrapCode = 'SLAG';


-- append old scrapCode to comments field for marks with wrong scrap code
UPDATE cf_StampMark
SET comments = CAST(comments as nvarchar(max)) + cast(CHAR(13) as nvarchar(max)) + cast('Old Scrap Code: ' as nvarchar(max)) + cast(scrapCode as nvarchar(max))
WHERE scrapCode NOT IN(
  'Porosity Gas', 'Oxides', 'Dross', 'Foreign Material', 'Shrinkage', 'Crack', 'Misrun', 'Coldshut',
  'Overcleaned', 'Handling Damage', 'Broken Core', 'Sand Hole ', 'Sand Drop', 'Molding', 'Out of Dimension',
  'Other', 'Machining', 'Saw Cut', 'Crack Wax', 'Wax Positive Defect', 'Bent', 'Blow', 'Crush', 'Short Pour',
  'Run Out', 'Broken Wax', 'Set Up Bad', 'Dropped Tree', 'Broken Cup', 'Shell Spall', 'Shell Inclusion',
  'Core Float', 'Targeting', 'Negative Parting Line', 'Shift', 'Core Drop', 'Buckle', 'Glue',
  'Mechanical Failure', 'Slag', 'Mold Reaction', 'Core Reaction', 'No Test Bars', 'No Paperwork', 'Bad Weld'
);