Expiringlots

Report ExpiringLots

Filename: ExpiringLots
Title: Lots which will expire in :Months months (better stock available)
Sample: Unit179 (SQL) and unit180

Synopsis

List of articles/lots which will expire in the future and for which better stock is available.

SQL

With TempArticle as (
  SELECT DISTINCT
    ARTCODE
  FROM
    STOCTOTAL
WHERE
  (ExpiryDate>GetDate())
  and (ExpiryDate<:MaxExpiryDate)
  and (X.QTYIN-isnull(X.QTYOUT,0)>0)
)
SELECT
  A.ARTCODE,
  LOCATION,
  Lotno,
  ExpiryDate,
  sum(QTYIN-isnull(QTYOUT,0)) AS QTY
FROM
  TempArticle
  LEFT JOIN STOCTOTAL A ON (A.ARTCODE=B.ARTCODE)
WHERE
  (ExpiryDate>GetDate()) and (ExpiryDate<:MaxExpiryDate)
GROUP BY
  A.ARTCODE, LOCATION, Lotno, ExpiryDate
HAVING
  sum(QTYIN-isnull(QTYOUT,0))>0
ORDER BY
  A.ARTCODE, LOCATION, Lotno

Parameters

Months number of Months
MaxExpiryDate Maximum date of expiry (=Today+Months)

Accessible in forms

StockReports