Norwexdeliverynote

Report NorwexDeliveryNote

• Filename: NorwexDeliveryNote
• Title: Norwex Delivery Note
• Sample: Form197 (unit197)

Synopsis

Afdruk delivery invoice voor norwex.

SQL

Volgende query vereenvoudigen via gebruik van WITH ?

SELECT
  AVIZNO,
  '*'   + AVIZNO + '*' AS BOrderNo,
  REPLACE(A.ARTCODE, 'NW', '') AS ARTCODE,
  sum(A.QTY) AS QTY,
  'N'   AS M,
  max(A.BPOINTS) as BPOINTS,
  (CASE
     WHEN isnull(CountryCode, '') = 'DE' and isnull(C.NAME2DE, '') <> '' THEN
       C.NAME2DE
     WHEN isnull(CountryCode, '') = 'NO' and isnull(C.NAME2NO, '') <> '' THEN
       C.NAME2NO
     WHEN isnull(CountryCode, '') = 'SE' and isnull(C.NAME2SE, '') <> '' THEN
       C.NAME2SE
     ELSE C.NAME2
   END) as ArticleDescription,
  B.CountryCode as BCountry,
  SalesOrderNo,
  max(OrderLineNo) as OrderLineNo,
  ItemCode,
  Description,
  MfgBatchNo,
  SRN,
  StockFeature,
  Owner,
  SerialNumber,
  ShiptoCode,
  Shiptoname,
  Address1,
  Address2,
  Street + ' ' + AddtlAddress as Street,
  ZipCode,
  City,
  CountryCode,
  SalesOrderreference,
  OrderDate,
  DeliveryDate,
  Datemandatory,
  Deliverytype,
  Principal,
  Carrier,
  OutputType,
  ShipmentMethod,
  Pickinstruction,
  Deliveryinstruction,
  Contact,
  Contact1,
  Phone,
  Size,
  Languagecode,
  Customeritemcode,
  CustomerItemdescription,
  Unitprice,
  SpecificField1,
  SpecificField2,
  SpecificField3,
  Cashondelivery,
  Priceforcashondelivery,
  ShipmentNumber,
  RSystemDate,
  BlockNo,
  Processed,
  FileName
FROM
  OUTDETAILS A
  left outer join NRW_OUT B on A.Avizno = B.ShipmentNumber,
  INNER JOIN ARTICLE C ON
    (RTRIM(LEFT (REPLACE(A.ARTCODE, '.', '                                      '), 30)) = C.NAME)
WHERE
  (AVIZNO = : OrderNo)
  AND (len(isnull(CGROUPID, '')) <= 1)
  AND (QTY > 0)
GROUP BY
  AVIZNO,
  CGROUPID,
  A.ARTCODE,
  (CASE
     WHEN isnull(CountryCode, '') = 'DE' and isnull(C.NAME2DE, '') <> '' THEN
       C.NAME2DE
     WHEN isnull(CountryCode, '') = 'NO' and isnull(C.NAME2NO, '') <> '' THEN
       C.NAME2NO
     WHEN isnull(CountryCode, '') = 'SE' and isnull(C.NAME2SE, '') <> '' THEN
       C.NAME2SE
     ELSE C.NAME2
   END),
  SalesOrderNo,
  ItemCode,
  Description,
  MfgBatchNo,
  SRN,
  StockFeature,
  Owner,
  SerialNumber,
  ShiptoCode,
  Shiptoname,
  Address1,
  Address2,
  Street,
  AddtlAddress,
  ZipCode,
  City,
  CountryCode,
  SalesOrderreference,
  OrderDate,
  DeliveryDate,
  Datemandatory,
  Deliverytype,
  Principal,
  Carrier,
  OutputType,
  ShipmentMethod,
  Pickinstruction,
  Deliveryinstruction,
  Contact,
  Contact1,
  Phone,
  Size,
  Languagecode,
  Customeritemcode,
  CustomerItemdescription,
  Unitprice,
  SpecificField1,
  SpecificField2,
  SpecificField3,
  Cashondelivery,
  Priceforcashondelivery,
  ShipmentNumber,
  RSystemDate,
  BlockNo,
  Processed,
  FileName
UNION
SELECT
  DISTINCT AVIZNO,
  '*'   + AVIZNO + '*' AS BOrderNo,
  REPLACE(CGROUPID, 'NW', '') AS ARTCODE,
  MasterQTY AS QTY,
  'Y'   AS M,
  A.BPOINTS,
  (CASE
     WHEN isnull(CountryCode, '') = 'DE' and isnull(C.NAME2DE, '') <> '' THEN
       C.NAME2DE
     WHEN isnull(CountryCode, '') = 'NO' and isnull(C.NAME2NO, '') <> '' THEN
       C.NAME2NO
     WHEN isnull(CountryCode, '') = 'SE' and isnull(C.NAME2SE, '') <> '' THEN
       C.NAME2SE
     ELSE C.NAME2
   END) as ArticleDescription,
  B.CountryCode as BCountry,
  SalesOrderNo,
  '0'   as OrderLineNo,
  ItemCode,
  Description,
  MfgBatchNo,
  SRN,
  StockFeature,
  Owner,
  SerialNumber,
  ShiptoCode,
  Shiptoname,
  Address1,
  Address2,
  Street + ' ' + AddtlAddress as Street,
  ZipCode,
  City,
  CountryCode,
  SalesOrderreference,
  OrderDate,
  DeliveryDate,
  Datemandatory,
  Deliverytype,
  Principal,
  Carrier,
  OutputType,
  ShipmentMethod,
  Pickinstruction,
  Deliveryinstruction,
  Contact,
  Contact1,
  Phone,
  Size,
  Languagecode,
  Customeritemcode,
  CustomerItemdescription,
  Unitprice,
  SpecificField1,
  SpecificField2,
  SpecificField3,
  Cashondelivery,
  Priceforcashondelivery,
  ShipmentNumber,
  RSystemDate,
  BlockNo,
  Processed,
  FileName
FROM
  OUTDETAILS A
  left outer join NRW_OUT B on A.Avizno = B.ShipmentNumber,
  INNER JOIN ARTICLE C ON   (CASE
       WHEN len(isnull(CGROUPID, '')) > 1 then CGROUPID
       else RTRIM(LEFT (REPLACE(A.ARTCODE, '.',
         '                                      '), 30))
     END) = C.NAME
WHERE
  (AVIZNO = :OrderNO)
  AND (QTY > 0)
  AND len(isnull(CGROUPID, '')) > 1
ORDER BY
  ARTCODE ASC

Special processing is needed to print the sender: * Default value is ireland: * Sender: Norwex Ireland Ltd. c/o * Address1: Logistic Center Weert B.V. * Address2: Havenweg 16 6006SM The Netherlands * Tel: +353-1 – 5175740 * Email: IE-INFO@NORWEX.COM * Germany (Country = 'DE') * Sender: NORWEX GERMANY GMBH * Address1: * Address2: Raiffeisenstraße 20 64347 Griesheim * Tel: 06155-822734-0 Fax: 06155-822734-20 * Email: * England: (Country = UK or GB) * Sender: Norwex UK Ltd * Address1: Logistic Center Weert B.V. * Address2: Havenweg 16 6006SM The Netherlands * Tel: 020-36033622 * Email: uk-info@norwex.com * Sweden (Country=SE) * Sender: Norwex Sweden AB * Address1: Limhamnsvägen 110 * Address2: 216 13 Limhamn Skåne län, Malmö, Sweden * Tel: 040 655 80 80 * Email: se-info@norwex.com * Norway (Country=NO) * Sender: Norwex Norge AS * Address1: Nedre Hagaveg 17B * Address2: 2150 Arnes, Norway * Phone: 63 95 66 20 * Email: post@norwex.no

Parameters

• OrderdNo number of the order.

Accessible in forms

1. Pick and Pack