EAD (Export Accompanying Document) from Exact Online

Shipments of excise duty goods outside European Union should be accompanied by an EAD (Export Accompanying Document) with Intrastat codes. This article provides the queries on Exact Online of a use case.

Instructions

The sales order lines are used in this sample, selecting all lines of a sales order. The field Additional Description contains per item the box size (if any) in the format “QUANTITY x UNITVOLUME”, such as “12 x 350ml”, “4 x 4L” or “20L” (the last is non-boxed).

The following query extract quantity and unit volume of the items, together with the sales order lines of a specific order and places them in a temporary table for convenience:

create or replace table sle@inmemorystorage
as
select sle.division
,      sle.ordernumber
,      sle.linenumber
,      sle.quantity
,      itm.Description
       itemDescription
,      itm.ExtraDescription
       itemExtraDescription
,      case
       when itm.EXTRADESCRIPTION like '% x %'
       then to_number(regexp_replace(itm.extradescription, '([0-9]+) x (.*)', '$1'))
       else null
       end
       quantity_in_box
       label 'Quantity per Box'
,      case
       --
       -- Formaat: 12 x 350ml
       --
       when itm.EXTRADESCRIPTION like '% x %ml'
       then to_number(regexp_replace(itm.extradescription, '([0-9]+) x ([0-9]+)ml', '$2'))
       --
       -- Formaat: 4 x 4L
       --
       when itm.EXTRADESCRIPTION like '% x %L'
       then to_number(regexp_replace(itm.extradescription, '([0-9]+) x (([0-9]+)(|,([0-9]+)))L', '$3.$5')) * 1000
       --
       -- Formaat: 20L
       --
       when itm.EXTRADESCRIPTION like '%L'
       then to_number(regexp_replace(itm.extradescription, '(([0-9]+)(|,([0-9]+)))L', '$2.$4')) * 1000
       else null
       end
       unit_volume_ml
       label 'Unit Volume (ml)'
,      itm.EXTRADESCRIPTION like '% x %'
       ItemIsBoxed       
       label 'Is Boxed?'
,      itm.GrossWeight
       ItemGrossWeight
,      itm.code
       ItemCode
,      itm.ItemGroupCode
,      itm.ItemGroupDescription
,      itm.NetWeight
       ItemNetWeight
,      iex.STATISTICAL_NUMBER
       ItemStatisticalNumber
,      iex.STATISTICAL_NETWEIGHT
       ItemStatisticalNetWeight
,      iex.TRANSPORTDATA_GROSSWEIGHT
       ItemStatisticalGrossWeight
,      iex.TRANSPORTDATA_NETWEIGHT
       ItemTransportGrossWeight
from   exactonlinerest..salesorderlines sle
join   exactonlinerest..items itm
on     itm.division = sle.division
and    itm.id = sle.item
--
-- Intrastat information is solely available
-- on the XML interface.
--
join   exactonlinexml..itemsex(itm.division, Params_code => itm.code) iex
--
-- Only report one specific type of IntraStat code.
--
on     iex.STATISTICAL_NUMBER = '38119000'
--
-- Use $P{} or $X{} on Invantive Control for Excel 
-- or ${} on Invantive Script to change into a variable.
--
where  sle.ordernumber = 123456

In the following step another list is created with sales order lines, Intrastat codes and derived values (note that all three steps could have been combined into one huge query, but using temporary tables makes it easier to illustrate the steps):

create or replace table rptdetails@inmemorystorage
as
select ItemCode
,      ItemDescription
,      case
       when ItemIsBoxed
       then 'Box' 
       else 'Bottle'
       end
       Package
       label 'Package'
,      quantity_in_box
,      'Pcs'
       UOM
,      unit_volume_ml / 1000
       UnitVolumeLiterEach
       label 'Piece Volume (L)'
,      Quantity
,      ItemStatisticalNumber
,      unit_volume_ml * quantity / 1000
       UnitVolumeLiterTotal
       label 'Total Volume (L)'
,      Quantity * ItemStatisticalGrossWeight
       ItemStatisticalGrossWeightTotal
       label 'Total Statistical Gross Weight'
,      Quantity * ItemStatisticalNetWeight
       ItemStatisticalNetWeightTotal
       label 'Total Statistical Net Weight'
,      Quantity / Quantity_in_box
       BoxesCount
       label '#Boxes'
from   sle@inmemorystorage

In the last step we aggregate the volumes and quantities of bottles and boxes per item on the sales order:

select ItemCode
,      ItemDescription
,      Package
,      quantity_in_box
,      UOM
,      UnitVolumeLiterEach
,      sum(Quantity)
       label 'Totale Quantity'
,      ItemStatisticalNumber
,      sum(UnitVolumeLiterTotal)
       label 'Total Volume (L)'
,      sum(ItemStatisticalGrossWeightTotal)
       label 'Total Statistical Gross Weight'
,      sum(ItemStatisticalNetWeightTotal)
       label 'Total Statistical Net Weight'
,      sum(BoxesCount)
       label '#Boxes'
from   rptdetails@inmemorystorage
group
by     ItemCode
,      ItemDescription
,      Package
,      quantity_in_box
,      UOM
,      UnitVolumeLiterEach
,      ItemStatisticalNumber
order
by     ItemCode

Update October 2021

Please note that the use of the Exact Online XML API for Items is no longer necessary from October 2021 since the statistical values for export purposes are now also available with the ExactOnlineREST variants of Items.