Multiplelocations

Report MultipleLocations

Filename: MultipleLocations
Title: articles on Multiple locations
Sample: UnitX

Synopsis

List of articles that can be found on multiple locations.

SQL

Main query:

SELECT
  A.ARTCODE, B.NAME2, COUNT(DISTINCT A.LOCATION) as NLOC
FROM
  STOCTOTAL A
  INNER JOIN ARTICLE B ON (A.ARTCODE=B.NAME)
WHERE
--  {CustomFilter}
  AND (LEFT(A.LOCATION, 1) NOT IN ('R', 'P'))
  AND (A.QTYIN-isnull(A.QTYOUT, 0)>0)
GROUP BY
  A.ARTCODE, NAME2
HAVING
  COUNT(DISTINCT A.LOCATION)>1
ORDER BY
  A.ARTCODE ASC

Detail query 1

Per article, the list of locations where the article is, plus the quantity must be displayed

SELECT
  LOCATION,  
  sum(A.QTYIN-isnull(A.QTYOUT, 0)) as QTY
FROM
  STOCTOTAL A
WHERE
  (A.ARTCODE=:ArtCode)
--  {CustomFilter}
  AND (LEFT(A.LOCATION, 1) NOT IN ('R', 'P'))
  AND (A.QTYIN-isnull(A.QTYOUT, 0)>0)
GROUP BY  
  LOCATION  
ORDER BY Location
);

Remark: Check if this can be done with a WITH and grouping in the report.

Detail query 2

Additionally, the first location in the list of locations for the article which will be picked soon must be displayed as well:

SELECT TOP 1
  B.LOCATION, A.DATACF
FROM
  OUTDETAILS A
  INNER JOIN INVDETAILS B ON (b.ID = a.INVOICENO)   
WHERE  
  (A.ARTCODE = :ARTCODE)
  AND (isnull(pickedby, '')<>'')
  AND (isnull(A.TransactionType, 'xx') in ('ORD', 'KIT'))
  AND (B.LOCATION IN (:LocationList) -- Needed ?
ORDER BY
  A.DATACF DESC

Remark: This probably can also be done with a WITH and grouping in the report. If not, investigate whether it must be done in a PrepareReport call.

Parameters

CustomFilter macro with custom filter on supplier and additional location filter.

Accessible in forms

StockReports