Inventorybylocation

Report InventoryByLocation

Filename: InventoryByLocation
Title: Inventory, grouped by location
Sample: Unit71 (Form71)

Synopsis

Print an inventory grouped on location.

SQL

WITH TempData AS (
  SELECT
     artcode, name2, groupe , sum(QTY) as qty, A.LOCATION
  FROM
    INVENTORYSTOCK A
  WHERE
    (A.artcode<>'')
    AND (A.IDNO=:IDNO)
  GROUP BY
    artcode, name2, groupe, A.LOCATION
  HAVING
    sum(QTY)>'0'
  ORDER BY
    artcode
)
SELECT
  a.Artcode, a.Name2, a.Groupe , a.Qty,
  sum(b.qty) as QtyScan, '' as Description,
  A.LOCATION
FROM
  TempData A
  INNER JOIN ARTICLE E on (A.ARTCODE=E.NAME)
  LEFT OUTER JOIN INVENTORYDET B ON
    (a.artcode=b.artcode)
    AND (A.LOCATION=B.LOCATION)
    AND (B.IDNO=:IDNO)
WHERE
  (isnull(a.artcode, '')<>'')
--  {LocationFilter}
GROUP BY
  a.artcode, a.name2, a.groupe , a.qty, A.LOCATION
-- {CustomHaving}
UNION
SELECT
  a.Artcode, Name2, Groupe, '0' as Qty,
  sum(a.qty) as QtyScan, 'Only in warehouse', A.LOCATION
FROM
  INVENTORYDET a
  INNER JOIN ARTICLE E  ON (A.ARTCODE=E.NAME)
WHERE
  (A.IDNO=:IDNO)
  AND (a.artcode+'-'+a.location not in
       (select c.artcode+'-'+c.location from TempData C)
--  {LocationFilter}
--  {SupplierFilter}
GROUP BY
  a.artcode,  name2, groupe, A.LOCATION
ORDER BY
  A.LOCATION, 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'
SupplierFilter macro, Optional filter on supplier:
```
 AND (E.FSupplier=:Supplier)
```
LocationFilter macro, Optional filter on location:

  AND A.LOCATION IN (list of locations);

Accessible in forms

Inventory