Itgensql001: Unknown identifier 'unitprice_vat'

De volgende query werkt niet meer sinds de overgang van 17.32 naar 20.0.148:

select salesorder_salesordernumber_attr
,      salesorder_status_attr 
,      salesorder_orderdate
,      salesorder_deliverydate
,      salesorder_description
,      salesorder_yourref
,      salesorder_orderedby_code_attr
,      salesorder_orderedby_name
,      salesorder_orderedby_phone
,      salesorder_orderedby_email
,      salesorder_deliverto_code_attr
,      salesorder_deliverto_name
,      salesorder_deliverto_phone
,      salesorder_deliverto_email
,      salesorder_deliveryaddress_addressline1
,      salesorder_deliveryaddress_postalcode
,      salesorder_deliveryaddress_city
,      salesorder_deliveryaddress_country_code_attr
,      salesorder_invoiceto_code_attr
,      salesorder_invoiceto_name
,      salesorder_invoiceto_phone
,      salesorder_invoiceto_email
,      salesorder_foreignamount_currency_code_attr
,      salesorder_foreignamount_value
,      salesorder_foreignamount_rate
,      line_attr
,      description
,      item_code_attr
,      item_description
,      quantity
,      deliverydate
,      unit_code_attr
,      unit_description
,      unitprice_currency_code_attr
,      unitprice_value
,      unitprice_vat -- COMMENTAAR VAN MAKEN
,      unitprice_vatpercentage
,      netprice_currency_code_attr
,      netprice_value
,      netprice_vat -- COMMENTAAR VAN MAKEN
,      netprice_vatpercentage
,      foreignamount_currency_code_attr
,      foreignamount_value
,      foreignamount_rate
,      foreignamount_vatbaseamount
,      foreignamount_vatamount
,      discountpercentage
,      usedropshipment
,      rownum
from   ExactOnlineXML..Salesorderlines

De foutmelding is:

itgensql001: Unknown identifier ‘unitprice_vat’. Consider one of the following: UNITPRICE_VATPERCENTAGE, UNITPRICE_VALUE, NETPRICE_VALUE.

Als ik de gekenmerkte regels omzet in commentaar werkt het wel.

Hoe kan ik de BTW-gegevens van de verkooporderregels weer terugkrijgen in de resultaten?

Hetzelfde probleem treedt op bij:

select item_issalesitem
,      quantity
,      invoice_ordernumber_attr
,      invoice_invoicenumber_attr
,      invoice_warehouse_code_attr
,      item_id_attr
,      item_code_attr
from   ExactOnlineXML..InvoiceLines
where  quantity <0

voor het veld invoice_warehouse_code_attr, maar dat veld blijkt niet verder verwerkt te worden.

Advies is om een query op de XML-tabel SalesInvoiceLines om te zetten naar zoiets als onder, dit voorbeeld te controleren op correctheid en te gebruiken:

select hdr.OrderNumber
,      hdr.status
,      hdr.orderdate
,      hdr.DeliveryDate 
       OrderDeliveryDate
,      hdr.Description
       OrderDescription
,      hdr.YourRef
,      actord.Code
,      actord.Name
,      actord.Phone
,      actord.Email
,      actdlr.Code
,      actdlr.Name
,      actdlr.Phone
,      actdlr.Email
,      adsdlr.AddressLine1
,      adsdlr.PostCode
,      adsdlr.City
,      adsdlr.Country
,      active.Code
,      active.Name
,      active.Phone
,      active.Email
,      sle.Currency
,      hdr.AmountFC
       OrderAmountFC
,      case
       when hdr.AmountDC = 0
       then null
       else hdr.AmountFC / hdr.AmountDC
       end
       OrderExchangeRate
,      sle.LineNumber
,      sle.Description
,      itm.Code
,      itm.Description
,      sle.Quantity
,      sle.DeliveryDate
,      sle.UnitCode
,      uom.Description
       UnitDescription
,      null UnitPriceCurrency
,      sle.UnitPrice
,      null UnitPriceVAT
,      null UnitPriceVATPercentage
,      sle.Currency NetPriceCurrencyCode
,      sle.NetPrice
,      sle.VATCode
,      case
       when sle.Netprice = 0
       then null
       else sle.VATAmount / sle.Netprice * 100
       end
       NetPriceVatPercentage
,      sle.Currency
,      sle.AmountFC
,      case
       when sle.AmountDC = 0
       then null
       else sle.AmountFC / sle.AmountDC
       end
       ExchangeRate
,      sle.AmountFC VATBaseAmount
,      sle.VATAmount VATAmount
,      sle.Discount
,      sle.UseDropShipment
from   SalesOrderLinesIncremental hdr
join   SalesOrderLinesIncremental sle
on     sle.OrderID = hdr.OrderID
and    sle.LineNumber != 0
join   AccountsIncremental actord
on     actord.id = sle.OrderedBy
join   AccountsIncremental actdlr
on     actdlr.id = sle.DeliverTo
join   AccountsIncremental active
on     active.id = sle.InvoiceTo
join   AddressesIncremental adsdlr
on     adsdlr.Id = sle.DeliveryAddress
join   ItemsIncremental itm
on     itm.Id = sle.Item
join   Units uom
on     uom.Division = sle.Division
and    uom.Code     = sle.UnitCode
where  hdr.linenumber = 0
order
by     sle.OrderNumber
,      sle.LineNumber

De SQL-query:

select item_issalesitem
,      quantity
,      invoice_ordernumber_attr
,      invoice_invoicenumber_attr
,      invoice_warehouse_code_attr
,      item_id_attr
,      item_code_attr
from   ExactOnlineXML..InvoiceLines
where  quantity <0

kan efficiënter herschreven worden als een query op Exact Online zoals:

select itm.IsSalesItem
,      sil.Quantity
,      sil.OrderNumber
,      sil.InvoiceNumber
,      whe.Code
       WarehouseCode
,      itm.ID
       ItemID
,      itm.Code
       ItemCode
from   SalesInvoiceLinesIncremental sil
join   ItemsIncremental itm
on     itm.ID = sil.Item
join   ExactOnlineREST..Warehouses whe
on     whe.id = sil.Warehouse
where  Quantity < 0
and    sil.LineNumber != 0