Missing datafield "CostPrice" in ExactOnlineXML.. SalesOrderLines

In the table ExactOnlineXML..SalesOrderLines the field CostPrice is not available, although it’s available when a SalesOrder is exported via XML in Exact Online. Can this field be added?

The content of the field is skipped because the eExact-XML.xsd does not contain this field (at least on September 16, 2021):

<xsd:element name="SalesOrderLine" maxOccurs="unbounded">
   <xsd:element ref="Description" minOccurs="0"/>
   <xsd:element ref="Item"/>
   <xsd:element ref="Quantity" minOccurs="0"/>
   <xsd:element name="DeliveryDate" type="xsd:date" minOccurs="0"/>
   <xsd:element ref="Unit" minOccurs="0"/>
   <xsd:element name="UnitPrice" type="typePrice" minOccurs="0"/>
   <xsd:element name="NetPrice" type="typePrice" minOccurs="0"/>
   <xsd:element ref="ForeignAmount" minOccurs="0"/>
   <xsd:element name="DiscountPercentage" type="typePercentage" minOccurs="0"/>
   <xsd:element ref="Costcenter" minOccurs="0"/>
   <xsd:element ref="Costunit" minOccurs="0"/>
   <xsd:element ref="Note" minOccurs="0"/>
   <xsd:element name="UseDropShipment" minOccurs="0"/>
   <xsd:element name="ShopOrder" minOccurs="0"/>
   <xsd:element name="VersionNumber" minOccurs="0"/>
   <xsd:element ref="TaxSchedule" minOccurs="0"/>
  <xsd:attribute name="line" type="xsd:integer" use="optional"/>

Advice is to submit a bug report to Exact Online Support and refer to this post. The element CostPriceFC needs to be added in the XSD.

You might also want to check whether CostPriceFC in ExactOnlineREST..SalesOrderLines is still filled.

An approximation would be to use the historical purchase orders:

-- Historische inkoopprijzen per artikel.
create or replace table purchaselines@inmemorystorage
select por.orderdate
,      por.orderdate - to_number(to_char(por.orderdate, 'D')) 
,      to_char(por.orderdate, 'YYYYIW') 
       label 'Week'
,      to_char(por.orderdate, 'YYYYMM') 
       label 'Maand'
,      ple.itemcode
,      ple.netprice
,      ple.quantity
,      por.ordernumber
from   exactonlinerest..PurchaseOrders por
join   exactonlinerest..PurchaseOrderLines ple
on     ple.PurchaseOrderID = por.PurchaseOrderID
-- Maximum prijzen in een week waarin er inkopen geweest zijn
-- voor een artikel.
create or replace table pleweek@inmemorystorage
select itemcode
,      por_week
,      sun_in_week_date
,      avg(quantity * netprice) netpricewavg
,      avg(netprice) netpriceavg
,      max(netprice) netpricemax
,      min(netprice) netpricemin
,      count(*) cnt
from   purchaselines@inmemorystorage
by     itemcode
,      por_week
,      sun_in_week_date
-- Opzoektabel per week wanneer de laatste keer een artikel is ingekocht.
create or replace table plw@inmemorystorage
select clr.day_date
,      plw.itemcode
,      min(plw.por_week)
,      max(plw.sun_in_week_date)
from   CALENDAR@DataDictionary clr
join   pleweek@inmemorystorage plw
on     plw.sun_in_week_date <= clr.day_date
-- Optimalisatie: kijk alleen vanaf de eerste besteldatum.
where  clr.day_date >= ( select min(orderdate) from purchaselines@inmemorystorage )
and    clr.day_in_week = 1
-- Optimalisatie: kijkt niet in de toekomst.
and    clr.day_date <= add_months(trunc(sysdate), 1)
by     clr.day_date
,      plw.itemcode
-- Bepaal per artikel wat de laatste bekende prijs is
-- per week. Als er meerdere prijzen zijn, dan wordt de hoogste
-- inkoopprijs genomen.
create or replace table approx_prices@inmemorystorage
select plw0.day_date
,      plw0.itemcode
,      plw0.plw_sun_in_week_date_min
,      case
       -- Weighted average price since VZKI contained all kinds of 
       -- shipping products.
       when plw0.itemcode = 'VZKI'
       then 29 /* plw1.netpricewavg */
       else plw1.netpricemax
from   plw@inmemorystorage plw0
join   pleweek@inmemorystorage plw1
on     plw1.sun_in_week_date = plw0.plw_sun_in_week_date_min
and    plw1.itemcode = plw0.itemcode

And then search in approx_prices@inmemorystorage:

day_date = sor.sor_orderdate - to_number(to_char(sor.sor_orderdate, 'D'))