DECLARE @LatestYear numeric(19,5), @LatestMonth numeric(19,5), @MinDate DATETIME
    SET @MinDate = CONVERT(DATETIME, '2016-07-01 00:00:00', 102)
    SET @LatestYear = (SELECT MAX([YEAR])as MaxYear FROM [ILS].[dbo].[LOCATION_INVENTORY_SNAPSHOT])
    SET @LatestMonth = (SELECT MAX([MONTH])as MaxMonth FROM [ILS].[dbo].[LOCATION_INVENTORY_SNAPSHOT] WHERE [YEAR] = @LatestYear)

    SELECT    dbo.SHIPMENT_HEADER.COMPANY, dbo.SHIPMENT_DETAIL.ITEM, dbo.SHIPMENT_DETAIL.ITEM_DESC, dbo.XREF_OTYPE_TO_BCODE.ORDER_TYPE AS OrderType,
              CASE dbo.XREF_OTYPE_TO_BCODE.ORDER_TYPE WHEN 75 THEN 'NON-REPLACMENT' WHEN 77 THEN 'NON-REPLACMENT' WHEN 79 THEN 'REPLACMENT' WHEN
              89 THEN 'REPLACMENT' ELSE 'UNKNOWN TYPE' END AS OrderTypeDesc, MAX(dbo.SHIPMENT_HEADER.ACTUAL_SHIP_DATE_TIME) AS LastShipDate

    FROM      dbo.SHIPMENT_HEADER INNER JOIN
              dbo.SHIPMENT_DETAIL ON dbo.SHIPMENT_HEADER.INTERNAL_SHIPMENT_NUM = dbo.SHIPMENT_DETAIL.INTERNAL_SHIPMENT_NUM INNER JOIN
              (
                  SELECT
                      [MONTH]
                      ,[YEAR]
                      ,[COMPANY]
                      ,[ITEM]
                      ,[ON_HAND_QTY]
                  FROM [ILS].[dbo].[LOCATION_INVENTORY_SNAPSHOT]
                  WHERE ON_HAND_QTY > 0
                  AND [YEAR] = @LatestYear
                  AND [MONTH] = @LatestMonth
              ) as ItemOnHand ON SHIPMENT_DETAIL.ITEM = ItemOnHand.Item
              LEFT OUTER JOIN
              dbo.XREF_OTYPE_TO_BCODE ON dbo.SHIPMENT_HEADER.ORDER_TYPE = dbo.XREF_OTYPE_TO_BCODE.BUDGET_CODE

    WHERE    (dbo.SHIPMENT_HEADER.ACTUAL_SHIP_DATE_TIME > @MinDate)
    GROUP BY dbo.XREF_OTYPE_TO_BCODE.ORDER_TYPE, dbo.SHIPMENT_DETAIL.ITEM, dbo.SHIPMENT_DETAIL.ITEM_DESC, dbo.SHIPMENT_HEADER.COMPANY