text: ﻿
  CREATE PROCEDURE Integration.GetPurchaseUpdates
  @LastCutoff datetime2(7),
  @NewCutoff datetime2(7)
  WITH EXECUTE AS OWNER
  AS
  BEGIN
      SET NOCOUNT ON;
      SET XACT_ABORT ON;
  
  
      SELECT CAST(po.OrderDate AS date) AS [Date Key],
             po.PurchaseOrderID AS [WWI Purchase Order ID],
             pol.OrderedOuters AS [Ordered Outers],
             pol.OrderedOuters * si.QuantityPerOuter AS [Ordered Quantity],
             pol.ReceivedOuters AS [Received Outers],
             pt.PackageTypeName AS Package,
             pol.IsOrderLineFinalized AS [Is Order Finalized],
             po.SupplierID AS [WWI Supplier ID],
             pol.StockItemID AS [WWI Stock Item ID],
             CASE WHEN pol.LastEditedWhen > po.LastEditedWhen THEN pol.LastEditedWhen ELSE po.LastEditedWhen END AS [Last Modified When]
      FROM Purchasing.PurchaseOrders AS po
      INNER JOIN Purchasing.PurchaseOrderLines AS pol
      ON po.PurchaseOrderID = pol.PurchaseOrderID
      INNER JOIN Warehouse.StockItems AS si
      ON pol.StockItemID = si.StockItemID
      INNER JOIN Warehouse.PackageTypes AS pt
      ON pol.PackageTypeID = pt.PackageTypeID
      WHERE CASE WHEN pol.LastEditedWhen > po.LastEditedWhen THEN pol.LastEditedWhen ELSE po.LastEditedWhen END > @LastCutoff
      AND CASE WHEN pol.LastEditedWhen > po.LastEditedWhen THEN pol.LastEditedWhen ELSE po.LastEditedWhen END <= @NewCutoff
      ORDER BY po.PurchaseOrderID;
  
      RETURN 0;
  END;
  