Packinginvoices

Report Packing Invoices

Filename: PackingInvoices
Title: Packing invoices report :StartDate - :EndDate
Sample: Unit194 (form194)

Synopsis

Packing invoices report.

SQL

WITH Temp1 AS (
  SELECT
    Right('00000000000'+NAME, 15) as NAME,
    Weight
  FROM
    Article
--  {CustomFilter}
),
WITH Temp2 AS (
  SELECT
    A.RSystemDate, A.ExtOrderID, A.InvoiceNumber, C.SalesChannel, A.ID, A.itemQTY,
   Right('00000000000'+LTRIM(RTRIM(skuID)), 15) AS skuID
  FROM
    WARTAINVOICES.DBO.GR_PRINTFILE_INVOICE_ITEMS A
    INNER JOIN WARTAINVOICES.DBO.GR_PRINTFILE_INVOICE C ON
      (C.ID=A.IDNO)
      AND (C.InvoiceNumber=A.InvoiceNumber)
  WHERE
    (A.RsystemDate>=:StartDate)
    AND (A.RsystemDate<:EndDate)
)
SELECT
  CONVERT(CHAR(10),A.RsystemDate,120) AS SystemDate,
  A.ExtOrderID,
  A.InvoiceNumber,
  A.SalesChannel AS SCOUNTRY,
  count(distinct A.ID) AS TRows,
  sum(A.itemQTY) AS TQTY,
  sum(A.itemQTY*isnull(B.Weight, 0)) AS TWeight
FROM
  Temp2  
  INNER JOIN Temp1 B ON (A.skuID=B.NAME)
GROUP BY
  CONVERT(CHAR(10), A.RsystemDate,120),
  A.ExtOrderID,
  A.InvoiceNumber,
  A.SalesChannel
ORDER BY
  SCOUNTRY ASC,
  systemDate ASC,
  A.ExtOrderID ASC

Parameters

StartDate (TDateTime)
EndDate (TDateTime)
CustomFilter (string, macro)

Accessible in forms

InvoiceReports