text: ﻿
  CREATE PROCEDURE Integration.MigrateStagedSupplierData
  WITH EXECUTE AS OWNER
  AS
  BEGIN
      SET NOCOUNT ON;
      SET XACT_ABORT ON;
  
      DECLARE @EndOfTime datetime2(7) =  '99991231 23:59:59.9999999';
  
      BEGIN TRAN;
  
      DECLARE @LineageKey int = (SELECT TOP(1) [Lineage Key]
                                 FROM Integration.Lineage
                                 WHERE [Table Name] = N'Supplier'
                                 AND [Data Load Completed] IS NULL
                                 ORDER BY [Lineage Key] DESC);
  
      WITH RowsToCloseOff
      AS
      (
          SELECT s.[WWI Supplier ID], MIN(s.[Valid From]) AS [Valid From]
          FROM Integration.Supplier_Staging AS s
          GROUP BY s.[WWI Supplier ID]
      )
      UPDATE s
          SET s.[Valid To] = rtco.[Valid From]
      FROM Dimension.[Supplier] AS s
      INNER JOIN RowsToCloseOff AS rtco
      ON s.[WWI Supplier ID] = rtco.[WWI Supplier ID]
      WHERE s.[Valid To] = @EndOfTime;
  
      INSERT Dimension.[Supplier]
          ([WWI Supplier ID], Supplier, Category, [Primary Contact], [Supplier Reference],
           [Payment Days], [Postal Code], [Valid From], [Valid To], [Lineage Key])
      SELECT [WWI Supplier ID], Supplier, Category, [Primary Contact], [Supplier Reference],
             [Payment Days], [Postal Code], [Valid From], [Valid To],
             @LineageKey
      FROM Integration.Supplier_Staging;
  
      UPDATE Integration.Lineage
          SET [Data Load Completed] = SYSDATETIME(),
              [Was Successful] = 1
      WHERE [Lineage Key] = @LineageKey;
  
      UPDATE Integration.[ETL Cutoff]
          SET [Cutoff Time] = (SELECT [Source System Cutoff Time]
                               FROM Integration.Lineage
                               WHERE [Lineage Key] = @LineageKey)
      FROM Integration.[ETL Cutoff]
      WHERE [Table Name] = N'Supplier';
  
      COMMIT;
  
      RETURN 0;
  END;