Missing data field "CostPrice" in ExactOnlineXML.. SalesOrderLines

Questions
, ,
1

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?

Sales order XML
Sales order XML803×511 98.3 KB

2

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:complexType>
  <xsd:sequence>
   <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:sequence>
  <xsd:attribute name="line" type="xsd:integer" use="optional"/>
 </xsd:complexType>
</xsd:element>

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.

3

An approximation would be to use the historical purchase orders:

--
-- Historische inkoopprijzen per artikel.
--
create or replace table purchaselines@inmemorystorage
as
select por.orderdate
,      por.orderdate - to_number(to_char(por.orderdate, 'D')) 
       sun_in_week_date
,      to_char(por.orderdate, 'YYYYIW') 
       por_week
       label 'Week'
,      to_char(por.orderdate, 'YYYYMM') 
       por_maand
       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
as
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
group
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
as
select clr.day_date
,      plw.itemcode
,      min(plw.por_week)
       plw_por_week_min
,      max(plw.sun_in_week_date)
       plw_sun_in_week_date_min
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)
group
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
as
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
       end
       netpriceweek
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'))
4

9 berichten zijn gesplitst naar een nieuw topic: Kostprijs in verkoopmarge

5

A bug report has been created at Exact Online with number #03449297 to report that at least both CostPriceFC and Margin are missing in SalesOrderLine.

6

Currently Exact Online considers the missing fields in the XSD a feature for which an idea can be postponed on the Exact Online Community Forums. We will try to motivate that the XSD is used for automatic validation of XML and the current XML is invalid, and therefor a bug.

7

Wat is de divisiecode en land van deze administratie?

8

Divisie is 2913444.

9

This question was automatically closed after 2 weeks of inactivity. The last answer given has been marked as a solution.

Please ask a new question via a separate topic if the problem occurs again. Please include a link to this topic in the new question by pasting its URL into the text.