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