Articleoutmoves

Report ArticleOutMoves

Filename: ArticleOutMoves
Title: Article out moves
Sample: Unit159
Synopsis

No longer used: Display a list of 'M+' (outgoing?) article operations.
SQL

SELECT
  A.INVOICE AS FACT,
  SUPPL+' '+isnull(A.comments, '') AS FIRMA,
  RsystemDate as DATA,
  UPPER(B.ARTCODE) AS ARTICOL,
  sum(QTY) as QTY,
  LOCATION AS LOCATIE,
  COMENTS,
  sum(QTYIN) AS QTYN,
  'Reception' AS OPERATIE ,
  '' as invout,
  sum(QTY) AS QTYX,
  B.STOCKCODE,
  B.PCNAME as pickedby,
  (Select ISNULL(max(MYOPERATOR), '')+'/'+ISNULL(min(MOVEOPERATOR), '')
   from SCANIN X
   where X.INVOICE=A.INVOICE AND X.ARTCODE=B.ARTCODE ) as checkedby,
  'Scanned/Rec.Sc/Rec.Moved' as descriptionfields,
  '+' AS TSIGN,  
   CONVERT(CHAR(10), RsystemDate,120) AS XFILLDATE
FROM
  INV A
  INNER JOIN INVDETAILS B ON (A.INVOICE = B.INVOICE )
WHERE
  (B.ARTCODE=:ArticleID)
group by
  A.INVOICE, SUPPL, isnull(A.comments, ''), RsystemDate,
  B.ARTCODE, LOCATION, COMENTS, B.STOCKCODE,
  B.PCNAME , CONVERT(CHAR(10), RsystemDate,120)


UNION
SELECT
  A.INVOICE AS FACT,
  SUPPL+' '+isnull(A.comments, '') AS FIRMA,
  RsystemDate as DATA,
  UPPER(B.ARTCODE) AS ARTICOL,
  sum(QTY) as QTY,
  LOCATION AS LOCATIE,
  COMENTS,
  sum(QTYIN) AS QTYN,
  'Reception' AS OPERATIE ,
  '' as invout,
  sum(QTY) AS QTYX,
  B.STOCKCODE,
  B.PCNAME as pickedby,
  (Select ISNULL(max(MYOPERATOR), '')+'/'+ISNULL(min(MOVEOPERATOR), '')
   from SCANIN X
   where X.INVOICE=A.INVOICE AND X.ARTCODE=B.ARTCODE ) as checkedby,
  'Scanned/Rec.Sc/Rec.Moved' as descriptionfields,
  '+' AS TSIGN,  
  CONVERT(CHAR(10), RsystemDate,120) AS XFILLDATE
FROM
  INV A
  ISTORIE_INVDETAILS B ON  A.INVOICE = B.INVOICE
WHERE
  (B.ARTCODE=:ArticleID)
GROUP BY
  A.INVOICE, SUPPL, isnull(A.comments, ''), RsystemDate,
  B.ARTCODE, LOCATION, COMENTS, B.STOCKCODE,
  B.PCNAME , CONVERT(CHAR(10), RsystemDate,120)

UNION
SELECT
  STUFF(A.AVIZ+'                       ',20,0,'.') AS FACT,
  CLIENT AS FIRMA,
  B.FILLDATE AS DATA,
  UPPER(B.ARTCODE) AS ARTICOL,
  (-1)*sum(B.QTY) AS QTY,
  C.LOCATION AS LOCATIE ,
  B.COMMENTS AS COMENTS,
  (-1)*sum(QTYCF) AS QTYN,
  'Delivery' AS OPERATIE ,
  'Operated from '+C.INVOICE as invout,
  sum(B.QTY)*(-1) as qtyX,
  C.STOCKCODE,
  pickedby,
  B.PCNAME AS checkedby,
  'Picked/Operator' as descriptionfields,
   '-' AS TSIGN,  
   CONVERT(CHAR(10), B.FILLDATE,120) AS XFILLDATE
FROM
  OUT A
  INNER JOIN OUTDETAILS B ON (A.AVIZ = B.AVIZNO)
  LEFT OUTER JOIN INVDETAILS C ON B.INVOICENO=C.ID
WHERE
  (B.ARTCODE=:ArticleID)
GROUP BY
  A.AVIZ, CLIENT, B.FILLDATE, B.ARTCODE, C.INVOICE,
  C.STOCKCODE, C.LOCATION, B.COMMENTS, pickedby, B.PCNAME

UNION
SELECT
  STUFF(A.AVIZ+'                       ',20,0,'.') AS FACT,
  CLIENT AS FIRMA,
  B.FILLDATE AS DATA,
  UPPER(B.ARTCODE) AS ARTICOL, (-1)*sum(B.QTY) AS QTY,
  C.LOCATION AS LOCATIE ,
  B.COMMENTS AS COMENTS,
  (-1)*sum(QTYCF) AS QTYN,
  'Delivery' AS OPERATIE ,
  'Operated from '+C.INVOICE as invout,
  sum(B.QTY)*(-1) as qtyX,
  C.STOCKCODE,
  pickedby,
  B.PCNAME AS checkedby,
  'Picked/Operator' as descriptionfields,
  '-' AS TSIGN,  
  CONVERT(CHAR(10), B.FILLDATE,120) AS XFILLDATE
FROM
  OUT A
  INNER JOIN ISTORIE_OUTDETAILS B ON (A.AVIZ = B.AVIZNO )
  LEFT OUTER JOIN INVDETAILS C ON B.INVOICENO=C.ID
WHERE
  (B.ARTCODE=:ArticleID)
GROUP BY
  A.AVIZ, CLIENT, B.FILLDATE, B.ARTCODE, C.INVOICE,
  C.STOCKCODE, C.LOCATION, B.COMMENTS, pickedby, B.PCNAME
ORDER BY
  ARTICOL, XFILLDATE ASC, DATA ASC, TSIGN DESC
Parameters

ArticleID id of article.
Accessible in forms

ArticlesGrid