Expiringgoods
Report ExpiringGoods
- Filename: ExpiringGoods
- Title: Goods which will export in :Days days.
- Sample: Unit161, form 161.
Synopsis
Report to print a list of goods that will expire before a certain date.
SQL
With MyStock as (
SELECT
A.ID, A.SUPPLIER, A.ARTCODE , A.STOCKCODE, A.LOCATION, isnull(A.COLNO, '') as PALNO,
A.EXPIRYDATE, A.FILLDATE, A.LOTNO, A.QTY AS QTYIN,
sum(isnull(B.QTY, 0)) AS QTYOUT,
isnull(A.QTY, 0)-sum(isnull(B.QTY, 0)) AS QTYSTOCK,
FROM
INVDETAILS A
LEFT OUTER JOIN OUTDETAILS B ON
(A.ID=B.INVOICENO)
AND (A.ARTCODE=B.ARTCODE)
AND (B.FILLDATE<:StockDate)
where
(A.RSYSTEMDATE<=:StockDate)
-- {CustomFilter}
GROUP BY
A.ID, A.SUPPLIER, A.ARTCODE , A.STOCKCODE, A.LOCATION, isnull(A.COLNO, ''),
A.EXPIRYDATE, A.FILLDATE, A.LOTNO, A.QTY
HAVING
isnull(A.QTY, 0)-sum(isnull(B.QTY, 0)) > 0
)
SELECT
A.ARTCODE , B.NAME2, A.STOCKCODE, A.LOCATION, A.EXPIRYDATE, A.FILLDATE,
A.LOTNO, A.PALNO,
DATEADD(Month,
-(CASE WHEN ISNULL(ExpiryTime, 0)=0 THEN 100 ELSE EXPIRYTIME END),
(CASE WHEN A.EXPIRYDATE IS NOT NULL THEN EXPIRYDATE ELSE A.FILLDATE END)) AS ProdDate,
sum(QTYIN) as QTYIN,
sum(QTYOUT) as QTYOUT,
sum(isnull(QTYSTOCK, 0)) as QTYSTOCK,
sum(QTYSTOCK*isnull(Weight,0)) as Weight,
sum(QTYSTOCK*isnull(Vol,0)) as Vol
FROM
MYSTOCK A INNER JOIN B ON (ARTCODE=NAME)
WHERE
(isnull(LOTNO,'')<>'')
AND (EXPIRYDATE<=:ExpiryDate)
-- {CustomFilter} -- Still needed ? Filtering already done in MyStock ?
GROUP BY
A.ARTCODE , B.NAME2, A.STOCKCODE, A.LOCATION, A.EXPIRYDATE, A.LOTNO, A.PALNO,
A.Filldate, EXPIRYTIME
ORDER BY
ARTCODE, LOCATION
Parameters
- StockDate Date when articles are placed in stock.
- ExpiryDate Date when articles will expire.
- Days Number of days the expiry date is in the future (displayed in title)
- CustomFilter A macro with a custom SQL WHERE Clause. It must start with AND