Prepare stockreport

StockReport

The call to prepare this report call accepts a filter ({customfilter} in the below SQL). It also accepts the following parameters: * IncludeNormalReservedStock (boolean) * IncludeKitReservedStock (boolean) * ExcludeSpareParts (boolean) * StockDate Datum van de stock. * AssumeNorwex Boolean. Condition is altered.

The StockReport1 report preparation inserts the following data from the database in the PrintStock table, into the corresponding fields. The data inserts the result of various queries in the database. The order of the queries is important.

The NextStockDate is StockDate +1.

First query:

SELECT
   A.ID, A.SUPPLIER, A.ARTCODE , A.STOCKCODE, A.LOCATION, isnull(A.COLNO, '''') as PALNO,
  A.FILLDATE, A.EXPIRYDATE, A.LOTNO, A.QTY AS QTYIN,
  sum(isnull(B.QTY, 0)) AS QTYOUT,
  isnull(A.QTY, 0)-sum(isnull(B.QTY, 0)) AS QTYSTOCK,
FROM
  INVDETAILS A
  LEFT OUTER JOIN OUTDETAILS B ON
    (A.ID=B.INVOICENO)
    AND (A.ARTCODE=B.ARTCODE)
    AND (B.FILLDATE<:NextStockDate)
WHERE
  (A.RSYSTEMDATE<=:NextStockdate)
 -- {CustomFilter}
GROUP BY
  A.ID, A.SUPPLIER, A.ARTCODE , A.STOCKCODE, A.LOCATION, isnull(A.COLNO, ''), A.FILLDATE, A.EXPIRYDATE, A.LOTNO, A.QTY
HAVING
  (isnull(A.QTY, 0)-sum(isnull(B.QTY, 0)) > 0)

if the AssumeNorwex parameter is true, then ArtCode is replaced by

RTRIM(LEFT(REPLACE(A.ARTCODE, '.', '                                                    '), 50))

Both in the select fields as in the group by. (50 spaces in the string)

Second query: if the parameter IncludeNormalReservedStock is true, then the following is also included in the table:

SELECT
  A.SUPPLIER, A.ARTCODE , A.STOCKCODE, A.LOCATION, '' AS PALNO , A.FILLDATE,
  A.EXPIRYDATE, A.LOTNO, B.QTY AS QTYIN, 0 AS QTYOUT, B.QTY AS QTYSTOCK, A.ID
FROM
  INVDETAILS A
  INNER JOIN OUTDETAILS B ON
    (A.ID=B.INVOICENO)  
    AND (A.ARTCODE=B.ARTCODE)
    AND (isnull(B.DataCF, '2050-01-01')>:StockDate)
    AND (isnull(B.TransactionType, 'xx')='ORD')
    AND (left(B.AvizNo,2)<>'M-')
-- {CustomFilter}    

Third query: If the parameter IncludeKitReservedStock is true, then the following is also included in the table:

SELECT
  A.ID,A.SUPPLIER, A.ARTCODE , A.STOCKCODE, A.LOCATION, '' AS PALNO , A.FILLDATE,
  A.EXPIRYDATE, A.LOTNO,
  B.QTY AS QTYIN, 0 AS QTYOUT, B.QTY AS QTYSTOCK,
FROM
  INVDETAILS A
  INNER JOIN OUTDETAILS B on
    (A.ID=B.INVOICENO)  
    AND (A.ARTCODE=B.ARTCODE)
    AND (isnull(B.CHECKEDDATE, '2050-01-01')>:StockDate)
    AND (isnull(B.TransactionType, 'xx')='KIT')
    AND (left(B.AvizNo,2)<>'M-')
--    {CustomFilter}

Remark: It may be faster to combine the last 2 queries with an OR in the WHERE condition.

Fourth query: If ExcludeSpareParts is False, the following data is inserted:

SELECT
  'TV SHOP' AS SUPPLIER, B.NAME AS ARTCODE , 'SP' AS STOCKCODE,
  'x' AS LOCATION, '' AS PALNO , NULL AS FILLDATE, NULL AS EXPIRYDATE, '' AS LOTNO,
   0 as QTYIN, 0 as QTYOUT, 0 as QTYSTOCK, '1' AS ID
FROM  
  ARTICLE B
WHERE
  (isnull(Accesories, 'N')='Y')
  AND (B.NAME not in (
        SELECT DISTINCT Z.ARTCODE FROM PRINTSTOCK Z
        WHERE (Z.JOBID=:JOBID)
        )
      )

The fourth query must be done after the previous 2, since it depends on what is already inserted.