Ga naar inhoud

Csv

Service CSV

Synopsis

The CSV service is not a RPC service, it exists only at the HTTP level, as a CSV export service for the website. 

http://hostname/CSV/exportname?SessionID=XXX[&export_options]
* it accepts a GET request. * exportname is mandatory. * The SessionID must be passed as a parameter to be able to authenticate the request. * Additional parameters for the export service are passed as query arguments. * The return content is a CSV export, with field names in first line. * Return contentType is text/csv * Return using Content-Disposition attachment with CSV export as filename so the browser saves it as a file: 
Content-Disposition: attachment; filename=exportname.csv;
* The fields are separated by , * Double Quotes are used if the Quotes query parameter is 1, True or Y. * For all CSV exports, the field/parameter Company is based on company of the logged-in user. * All CSV exports accept a parameter FromDate, DateTime. default value is 1900-01-01. * All CSV exports accept a parameter ToDate, DateTime. default value is Today.

Available exports

AcceptedOrders

  • Return a list of accepted orders.
  • Parameter CountryID, integer. default value is -1.
  • Parameter CountryCode, string. default value is empty. If it is specified, and CountryID is -1, look up CountryID (cuID) from country code (cuCountryCode).
  • Executes the following query if Country is empty: 
    EXEC WEB_REPORT_ACCEPTED_ORDERS :Company, :FromDate, :ToDate
  • If the CountryID is not -1, the following query is executed instead: 
    EXEC WEB_REPORT_ACCEPTED_ORDERS_Country :Company, :FromDate, :ToDate, :Country
  • The fields are exported with the following headers (in indicated order)
  • OrderNo caption "OrderNo"
  • RecDate caption "RecDate"
  • DClientFullName caption "ClientName"
  • DClientTown caption "Town"
  • DClientTownCode Caption "TownCode"
  • DClientCountry Caption "Country"
  • TWeight Caption "Weight"
  • PickingDate caption "PickingDate"
  • PackingDate caption "PackingDate"
  • ShippingDate caption "ShippingDate"
  • Shipping_Method caption "ShippingMethod"
  • TrackingNumber caption "TrackingNo"

AcceptedOrdersDetail

  • Provides more detail on accepted orders.
  • The following query is executed: 
    EXEC [dbo].[WEB_REPORT_ACCEPTED_ORDERS_DETAIL] :Company, :FromDate, :ToDate
  • The fields are exported with the following headers (in indicated order)
  • OrderNo caption "OrderNo"
  • RecDate caption "RecDate"
  • DClientFullName caption "ClientName"
  • DClientTown caption "Town"
  • DClientCountry caption "Country"
  • ARTCODE caption "Article"
  • ART_DESCR caption "Art_descr"
  • QTY caption "Qty"
  • PickingDate caption "PickingDate"
  • PackingDate caption "PackinDate"
  • ShippingDate caption "ShippingDate"
  • TrackingNumber caption "TrackingNo"

Receptions

  • Returns a list of receptions (incoming articles)
  • Executes the following SQL: 
    EXEC [dbo].[WEB_REPORT_RECEPTION] :Company,:FromDate,:ToDate
  • The fields are exported with the following headers (in indicated order)
  • ArtCode caption "Article_(SKU)"
  • ART_DESCR caption "Article_descr"
  • QTY caption "QTY"
  • STOCKCODE caption "St.C."
  • RecNumber caption "Rec.No."
  • RSystemDate caption "Date"

RejectedOrders

  • Return a list of rejected orders.
  • Executes the following SQL: 
    SELECT
  A.OrderNo,
  A.RSystemDate AS RecDate,
  A.DClientFullName,
  REPLACE(A.DClientTown, ',', ' ') AS DClientTown,
  A.DClientCountry,
  A.RejectionReason AS RReason,
  A.RejectionDescription
FROM
  XML_OUT A
WHERE
  (A.Customer=:Company)
  and (A.Rejected='Y')
  AND (A.RSystemDate Between :FromDate and :ToDate)
ORDER BY
  A.ORDERNO
  • The fields are exported in the following order with the following captions:
  • OrderNo caption "OrderNo"
  • RecDate caption "RecDate"
  • DClientName caption "ClientName"
  • DClientTown caption "ClientTown"
  • DClientCountry caption "ClientCountry"
  • RReason caption "RejectionReason"
  • RejectionDescription caption "RejectionReasonDescription"

Returns

  • Parameter FromDate, DateTime. default value is 1900-01-01.
  • Parameter ToDate, DateTime. default value is Today
  • The following query is executed: 
    SELECT
  Invoice AS RecNumber,
  ARTCODE,
  NAME2 AS Art_Descr,
  D.STOCKCODE,
  C.REASON,
  max(D.RSystemDate) AS RSystemDate,
  sum(QTY) AS QTY,
  OriginalOrderNumber
FROM
  INVDETAILS D
  INNER JOIN ARTICLE B ON D.ARTCODE = B.NAME
  LEFT OUTER JOIN RETUR C ON 'RET' + CAST (C.ID AS VARCHAR (50)) = D.INVOICE
WHERE
  (B.FSUPPLIER = :Company)
  AND (D.RSYSTEMDATE BETWEEN :FromDate and :ToDate)
  AND D.TRANSACTIONTYPE IN ('RET', 'RETD')
GROUP BY
  Invoice,
  ARTCODE,
  NAME2,
  D.STOCKCODE,
  C.REASON,
  OriginalOrderNumber
ORDER BY
  RecNumber,
  ARTCODE
  • StockCode value 75 is replaced by ATS in the output.
  • The fields are exported in the following order with the following captions:
  • RecNumber caption "Rec.No."
  • ARTCODE caption "Article_(SKU)"
  • ART_DESCR caption "Article_descr"
  • STOCKCODE caption "St.C."
  • REASON caption "Reason"
  • RSystemDate caption "Date"
  • QTY caption "QTY"
  • OriginalOrderNumber caption "OriginalOrderNumber"

StockReport

  • Parameter local, boolean.
  • the following query is executed: 
    SELECT
  ARTCODE,
  MAX(ART_DESCR) AS ART_DESCR,
  isnull(sum(CASE WHEN STOCKCODE = 'ATS' THEN QTYIN - isnull(QTYOUT, 0) ELSE 0 END), 0) AS QTY_Available,
  isnull(sum(CASE WHEN LEFT (STOCKCODE, 1) = 'K' THEN QTYIN - isnull(QTYOUT, 0) ELSE 0 END), 0) AS QTY_Damaged,
  isnull(sum(CASE WHEN STOCKCODE = 'QC' THEN QTYIN - isnull(QTYOUT, 0) ELSE 0 END), 0) AS QTY_QC_blocked,
  isnull(sum(CASE WHEN STOCKCODE = 'BO' THEN QTYIN - isnull(QTYOUT, 0) ELSE 0 END), 0) AS QTY_BackOrdered,
  isnull(sum(CASE WHEN STOCKCODE = 'RES' THEN QTYIN - isnull(QTYOUT, 0) ELSE 0 END), 0) AS QTY_Reserved,
  isnull(sum(CASE WHEN STOCKCODE = 'KIT' THEN QTYIN - isnull(QTYOUT, 0) ELSE 0 END), 0) AS QTY_KIT_Res
FROM
  [WEB_FStocTotal_SUPPL](:Company)
GROUP BY
  ARTCODE
ORDER BY
  ARTCODE
  • if company is norwex and parameter local is true, the name of the stored procedure is WEB_FStocTotal_SUPPL_LOCAL_NORWEX
  • In the output, ARTCODE 2E19 is replaced with '2E19
  • In the output, commas in ART_DESC are replaced with spaces.
  • Fields are exported in the following way:
  • ARTCODE caption "Article_(SKU)"
  • ART_DESCR caption "Article_descr"
  • QTY_Available caption "AvailableStock"
  • QTY_QC_blocked caption "QC_BlockedStock"
  • QTY_Reserved caption "ReservedStock"
  • QTY_KIT_Res caption "KIT_Reserved_stock"
  • QTY_BackOrdered caption "Backordered_Stock"
  • QTY_Damaged caption "DamagedStock"

StockReportByLocation

  • the following query is executed: 
    SELECT
  ARTCODE,
  MAX(ART_DESCR),
  isnull(sum(CASE WHEN STOCKCODE = 'ATS' THEN QTYIN - isnull(QTYOUT, 0) ELSE 0 END), 0) AS QTY_Available,
  ExpiryDate
FROM
  [WEB_FStocTotal_SUPPL](?)
GROUP BY
  ARTCODE,
  ExpiryDate
ORDER BY
  ARTCODE
  • In the output, ARTCODE 2E19 is replaced with '2E19
  • In the output, commas in ART_DESC are replaced with spaces.
  • Fields are exported in the following way:
  • ARTCODE caption "Article_(SKU)"
  • ART_DESCR caption "Article_descr"
  • QTY_Available caption "AvailableStock"
  • ExpiryDate caption "ExpiryDate"