Returns

Report Returns

Filename: receptions
Title: Returns in periode :StartPeriode - :EndPeriode
Sample: Unit42

Synopsis

List of returns in a periode.

SQL

SELECT
  COUNT(D.ID) AS NRLINII,
  sum(D.QTY) as MyQTY ,
  D.StockCode,
  C.CATEGORY+' '+isnull(B.GROUPE, '') as CATEGORY,
  SUM(D.QTY*WEIGHT) AS TWEIGHT,
  SUM(D.QTY*(CASE WHEN B.UMV='Cbm' THEN B.VOL*1000 ELSE B.VOL END)) AS TVOL,
  sum(D.QTY*isnull(SubComponentsTQTY, 1)) as SQTY
FROM
  INVDETAILS D
  LEFT OUTER JOIN ARTICLE B ON (D.ARTCODE=B.NAME)
  LEFT OUTER JOIN ARTICLECATEGORY C ON
    (isnull((CASE WHEN B.UMV='Cbm' THEN B.VOL*1000 ELSE B.VOL END), 0)>=C.VOL1)
    AND (isnull((CASE WHEN B.UMV='Cbm' THEN B.VOL*1000 ELSE B.VOL END), 0)<C.VOL2)
    AND (isnull(B.WEIGHT, 0)>=C.WEIGHT1)
    AND (isnull(B.WEIGHT, 0)<C.WEIGHT2)
    AND (C.CategoryID=:Category)
WHERE
  (D.RSYSTEMDATE>=:StartDate)
  AND (D.RSYSTEMDATE<:EndDate)
  AND (LEFT(ISNULL(D.TRANSACTIONTYPE, 'X'), 3)='RET')
  AND (D.LOCATION<>'R.00.00')
--  {CustomFilter}
GROUP BY
  D.StockCode, C.CATEGORY, isnull(B.GROUPE, '')
ORDER BY
  CATEGORY, D.StockCode

Parameters

StartDate (TDateTime)
EndDate (TDateTime)
Category (Integer)
CustomFilter (string, macro)

Accessible in forms

InvoiceReports