Inventorybyarticle

Report InventoryByArticle

Filename: InventoryByArticle
Title: Inventory, grouped by article
Sample: Unit70 (Form70)

Synopsis

Print an inventory grouped on article.

SQL

WITH TempData AS (
  SELECT
    artcode, name2, groupe , sum(QTY) as qty
  FROM
    INVENTORYSTOCK A
  WHERE
    (A.artcode<>'')
   AND (A.IDNO=:IDNO)
  GROUP BY
    artcode, name2, groupe
  HAVING
    sum(QTY)>'0'
  ORDER BY
    artcode
)
SELECT
  a.Artcode, a.name2, a.groupe ,
  a.Qty, ISNULL(sum(b.qty), 0) as QtyScan,
  '' as description
FROM
  TempData A
  INNER JOIN ARTICLE E on (A.ARTCODE=E.NAME)
  LEFT OUTER JOIN INVENTORYDET B ON
    (a.artcode=b.artcode)
    AND (B.IDNO=:IDNO)
WHERE
  (isnull(a.artcode, '')<>'')
GROUP BY
  a.artcode, a.name2, a.groupe , a.qty
-- {CustomHaving}
UNION
SELECT
  a.Artcode, name2, groupe,
  '0' as Qty, ISNULL(sum(a.qty), 0) as QtyScan,
  'Only in warehouse'  as description
FROM
  INVENTORYDET a
  INNER JOIN ARTICLE E  ON  (A.ARTCODE=E.NAME )
WHERE
  (A.IDNO=:IDNO)
  AND (A.artcode not in (select C.artcode from TempData C))
-- {CustomFilter}
GROUP BY
  a.artcode,  name2, groupe
ORDER BY
  a.artcode

Parameters

IDNO Inventory ID
Supplier: Suplier code, can be empty.
CustomHaving macro, empty or
```
 HAVING A.QTY<>ISNULL(sum(b.qty), '0') ;
```
Depends on 'Only differences'

CustomFilter Optional filter on supplier:

 AND (E.FSupplier=:Supplier)

And/or on location:

  AND A.LOCATION IN (list of locations);

Accessible in forms

Inventory