Returndifferencelist

Report ReturnDifferenceList

Filename: ReturnDifferenceList
Title: Return Differences List
Sample: unit151 (Form151)

Synopsis

Print a list of differences between original and returned order.

SQL

With ReturnQty as (
  SELECT
    INVOICE,ARTCODE, sum(QTY) as QTY
  FROM
    RETURCOMPARE
  WHERE
    (INVOICE=:InvoiceID)
  GROUP BY
    INVOICE,ARTCODE  
),
OrderQty as (
SELECT
  ARTCODE, sum(QTY) as QTY
FROM
  INVDETAILS
WHERE
  (INVOICE=:InVoiceID)
GROUP BY
  ARTCODE
)
-- Articles where there is a difference.
SELECT
  A.INVOICE,
  A.ARTCODE,
  B.NAME2,
  OrderQty.QTY as QTY,
  ReturnQty.Qty as QTYImp,
FROM
  INVDETAILS A  
  INNER JOIN ARTICLE B ON (A.ARTCODE=B.NAME)
  LEFT JOIN OrderQty ON (A.ARTCODE=OrderQty.ARTCODE)
  LEFT JOIN ReturnQty ON (A.ARTCODE=ReturnQty.ARTCODE)
WHERE
  (A.INVOICE=:InVoiceID)
  AND (OrderQty.Qty<>ReturnQty.Qty)
UNION
-- Returned Articles not part of the original invoice
SELECT
  A.INVOICE,
  A.ARTCODE,
  B.NAME2,
  0 as QTY,
  A.QTY as QTYImp
FROM
  ReturnQty A,
  LEFT JOIN ARTICLE B ON (A.ARTCODE=B.NAME)
  LEFT JOIN OrderQty ON (A.ArtCode=OrderQty.ArtCode)
WHERE  
  OrderQty.Artcode is null
order by
  ARTCODE ASC

Parameters

InvoiceID ID of the original invoice.

Accessible in forms

Returns