Palettelabelscanner

Report palettelabelscanner

• Filename: PaletteLabelScanner
• Title: Palette label
• Sample: Unit48 (scanner)

Synopsis

Print Palette label for article.

SQL

With ArtSel as (
SELECT
   1 AS TypeRecord,
   '' as AVIZNO,
   A.ARTCODE,
   LOCATION,
   A.STOCKCODE,
   SUM(QTYIN - ISNULL(QTYOUT, 0)) AS QTY,
   '' as country,
   A.SUPPLIER,
   A.FDATE,
   A.Lotno,
   A.ExpiryDate
 FROM
   FSTOCTOTAL(:ArticleID) A
   INNER JOIN ARTICLE C ON (A.ARTCODE = C.NAME)
 GROUP BY
   A.STOCKCODE,
   A.ARTCODE,
   A.Location,
   A.Lotno,
   A.ExpiryDate,
   A.SUPPLIER,
   A.FDATE
 HAVING
   SUM(QTYIN - ISNULL(QTYOUT, 0)) > '0'
UNION
 SELECT
   2 AS TypeRecord,
   A.AVIZNO,
   A.ARTCODE,
   B.LOCATION,
   a.stockcode,
   a.qty,
   D.COUNTRY,
   B.SUPPLIER,
   B.FILLDATE AS FDATE,
   B.Lotno,
   B.ExpiryDate
 FROM
   OUTDETAILS a
   INNER JOIN INVDETAILS b ON
     (b.ID = a.INVOICENO)
     AND (b.STOCKCODE = a.STOCKCODE)
     AND (b.ARTCODE = a.ARTCODE)
   INNER JOIN ARTICLE c ON (a.artcode = c.name)
   INNER JOIN OUT D ON (A.AVIZNO = D.AVIZ)
 WHERE
   (a.ARTCODE = :ArticleID)
   AND (pickedby = '')
   AND (A.TransactionType IN ('ORD', 'KIT'))
   AND (A.filldate > :StartDate)
 UNION
 SELECT
   3 AS TypeRecord,
   A.AVIZNO,
   A.ARTCODE,
   A.TransitLocation AS Location,
   a.stockcode,
   a.qty,
   ''   AS Country,
   B.SUPPLIER,
   B.FILLDATE AS FDATE,
   B.Lotno,
   B.ExpiryDate
 FROM
   OUTDETAILS A
   INNER JOIN INVDETAILS B ON
     (b.ID = a.INVOICENO)
     AND (b.STOCKCODE = a.STOCKCODE)
     AND (b.ARTCODE = a.ARTCODE)
 WHERE
   a.ARTCODE = :ArticleID
   AND (pickedby <> '')
   AND (checkedby = '')
   AND (A.TransactionType = 'KIT')
   AND (A.TransitLocation <> '')
   AND A.filldate > :Startdate
)
select
   ArtSel.*, C.NAME2, C.EANCODE3
from
  ArtSel
  Inner JOIN Article C on (ArtSel.ArtCode=C.Name)
ORDER BY
   TypeRecord,AvizNo

Add Calculated fields:

1. ArtBareCore: '*' + B.ARTCODE + '*'
2. STATUS
3. TypeRecord=1: Empty.
4. TypeRecord=2: 'Reserved /' + avizno.
5. TypeRecord=3: 'Production / ' + avizno.
6. STATUS1
7. TypeRecord=1: 'Stock'.
8. TypeRecord=2: 'Reserved'.
9. TypeRecord=3: 'Production'.
10. STATUS2
11. TypeRecord=1: Empty.
12. TypeRecord=2: Country.
13. TypeRecord=3: Empty.
14. LotNo2:
15. If Lotno empty: empty
16. If Lotno not empty: '*'+LotNo+'*'
17. ProductLot:
18. If LotNo<>'' then '*' + ARTCODE + '-' + LOTNO + '*'
19. If LotNo='' then '' + ARTCODE+ ''
20. ProductLot1:
21. If LotNo<>'' then '*' + EANCODE3 + '-' + LOTNO + '*'
22. If LotNo='' then '*' + EANCODE3+ '*'

Parameters

• ArticleID ID of the aricle for which to print a palette label.

Accessible in forms

• Scanner article stock