Warehousevolumesweekly

Report WarehouseVolumesWeekly

Filename: WarehouseVolumesWeekly
Title:
Sample: Unit158

Synopsis

Weekly stock report

SQL

With Tmp as (
  SELECT
    D.SUPPLIER,
    :StockDate AS STOCKDATE,
    D.STOCKCODE,  
    D.ARTCODE,
    D.QTY-isnull(SUM(A.QTY), 0) AS QTY,
    D.LOCATION,
    ((D.QTY-isnull(SUM(A.QTY), 0))
      * isnull(B.WEIGHT, 0)) as Weight,
    ((D.QTY-isnull(SUM(A.QTY), 0))
      * isnull((CASE WHEN B.UMV='Cbm' THEN B.VOL*1000 ELSE B.VOL END), 0)) as Vol
FROM
  INVDETAILS D
  LEFT OUTER JOIN OUTDETAILS A ON
    (D.ID=A.INVOICENO)
    AND (CASE WHEN A.DATACF<=:NextStockDate THEN 1
            WHEN isnull(A.TransactionType, '') in ('MOVE', 'COR', 'TRANSFER') then 1
            when left(A.Avizno, 2)='M-' then 1
      else 0 END)=1
  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<:NextStockDate
--  {CustomFilter}
GROUP BY
  D.SUPPLIER, D.STOCKCODE, D.ARTCODE, D.QTY, D.LOCATION, B.VOL, B.Weight, B.UMV  
HAVING
  D.QTY-isnull(SUM(A.QTY), 0)>0
ORDER BY
  D.STOCKCODE
)
SELECT
  DatePart(Week, D.STOCKDATE) as MyWeek,
  D.STOCKDATE,
  D.STOCKCODE,
  D.LOCATION,
  sum(D.QTY) as MyQty,
  C.CATEGORY+' '+isnull(B.GROUPE, '') as CATEGORY,
  SUM(D.QTY*B.WEIGHT) AS TWEIGHT,
  SUM(D.QTY*(CASE WHEN B.UMV='Cbm' THEN B.VOL*1000 ELSE B.VOL END)) AS TVOL,
  (CASE
     WHEN (LEFT(D.LOCATION, 4)>='H.01'
          AND LEFT(D.LOCATION, 4)<='H.21'
          AND SUM(D.QTY*(CASE WHEN B.UMV='Cbm' THEN B.VOL*1000 ELSE B.VOL END))<1500
          AND SUM(D.QTY*(CASE WHEN B.UMV='Cbm' THEN B.VOL*1000 ELSE B.VOL END))>0)
   THEN 1500
   ELSE
     SUM(D.QTY*(CASE WHEN B.UMV='Cbm' THEN B.VOL*1000 ELSE B.VOL END))
   END) as TCRVOL
FROM
  Tmp 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)
GROUP BY
  D.SUPPLIER, D.STOCKDATE, D.STOCKCODE, D.LOCATION, C.CATEGORY, isnull(B.GROUPE, '')
ORDER BY
  MyWeek, CATEGORY, D.location, D.STOCKDATE,  D.StockCode

Parameters

StockDate (TDateTime)
NextStockDate (TDateTime) ( = StockDate+1)
Category (Integer)
CustomFilter (string, macro)
TotalsOnly (disable visibility of detail band if True)

Accessible in forms

InvoiceReports