Deliveries3

Report Deliveries3

Filename: Deliveries3
Title: Deliveries
Sample: Unit217

Synopsis

List of deliveries, alternate format

SQL

SELECT
  'Number of Orders' as ITEM,
  'A-order' as  rank_level,
  COUNT(DISTINCT AVIZNO) as QTY,
  'Orders' as UM
FROM
  Out G
  INNER JOIN OUTDETAILS A ON (G.AVIZ=A.AVIZNO)
  INNER JOIN INVDETAILS D ON (A.INVOICENO=D.ID)  
  LEFT OUTER JOIN ARTICLE B ON (A.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
   (G.Filldate>=:StartDate)
   AND (G.Filldate<=EndDate)
   AND (ISNULL(A.TRANSACTIONTYPE, 'X')='ORD')
--   {customfilter}
UNION
SELECT
  isnull(C.CATEGORY, '')+' '+isnull(B.GROUPE, '') as ITEM,
  'B-category' as  rank_level,
  sum(A.QTY) as MyQTY,
  'PCS' as UM  
FROM
  Out G
  INNER JOIN OUTDETAILS A ON (G.AVIZ=A.AVIZNO)
  INNER JOIN INVDETAILS D ON (A.INVOICENO=D.ID)  
  LEFT OUTER JOIN ARTICLE B ON (A.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
  (G.Filldate>=:StartDate)
  AND (G.Filldate<=EndDate)
  AND (ISNULL(A.TRANSACTIONTYPE, 'X')='ORD')
--  {customfilter}
GROUP BY
  C.CATEGORY, isnull(B.GROUPE, '')
UNION  
SELECT
  A.ARTCODE as ITEM,
  'C-article' as  rank_level,
  sum(A.QTY) as MyQTY,
  'PCS' as UM  
FROM
  Out G
  INNER JOIN OUTDETAILS A ON (G.AVIZ=A.AVIZNO)
  INNER JOIN INVDETAILS D ON (A.INVOICENO=D.ID)  
  LEFT OUTER JOIN ARTICLE B ON (A.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
  (G.Filldate>=:StartDate)
  AND (G.Filldate<=EndDate)
  AND (ISNULL(A.TRANSACTIONTYPE, 'X')='ORD')
--  {customfilter}
GROUP BY
  A.ARTCODE

Parameters

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

Accessible in forms

InvoiceReports