Using Invantive SQL you can easily upload and download information from cloud platforms such as Exact Online. The peculiarities of the APIs are hidden behind an advanced SQL engine. However, when something works differently than you would expect, the cloud platform support channels typically require you to provide API calls instead of Invantive SQL statements to analyze and reproduce an issue.
This note describes how you can collect analytical information for Exact Online. The use case was that upon creation of a sales order from EDI messages from Descartes, the customer’s price list was only applied to the first order line instead on all lines. In this use case, a large number of XML files in INH3 (Inhouse 3) format of Descartes containing INVOIC messages are transformed into Exact Online sales orders.
Materialize Data
The processing of EDI messages is non-trivial and involves many steps and tables. It is convenient to materialize the data used to create the sales orders to simplify reproduction so you don’t have to set up the EDI environment on every test.
The sales orders and order lines are created using an Invantive SQL transaction using two queries joining approximately ten tables. As a first step we materialize the data into XML dump files for easy replay:
select ...
local memorize results clipboard hdr
local save results clipboard hdr to "c:\temp\sample-trace-insert-order-headers.xml" format xml
We materialize both the sales order headers and sales order lines to two separate files. The resulting XML files can later easily be loaded into memory as shown in the next step. The XML files contain both metadata and payload.
Release 21 and later: Exact Online API Native Calls
Using these XML dumps, you can reproduce the Invantive SQL transaction using the script below. The essential part is setting the provider attribute log-native-calls-to-disk to true. More details can be found at Collect Native Platform Call Data.
Release 20 and earlier: Exact Online API Native Calls
Using these XML dumps, you can reproduce the Invantive SQL transaction using the script below. The essential part is setting the provider attribute trace-native-calls to true. It print all native calls to the trace log. The trace log can be inspected using Microsoft Debugview or by activating the trace to file features as described in the manual. Trace-native-calls works for cloud platforms such as OData and XML, but also for traditional native database calls such as on Teradata and SQL Server.
set trace-native-calls true
local load results clipboard lne from "c:\temp\sample-trace-insert-order-headers.xml" format xml
local insert results clipboard lne in table hdr@inmemorystorage create
local load results clipboard lne from "c:\temp\sample-trace-insert-order-lines.xml" format xml
local insert results clipboard lne in table lne@inmemorystorage create
begin transaction
insert into exactonlinerest..salesorders@eol
( description
, orderedby
, deliverto
, deliveryaddress
, invoiceto
, warehouseid
, yourref
, orderdate
, deliverydate
, salesperson
, remarks
)
select description
, orderedby
, deliverto
, deliveryaddress
, invoiceto
, warehouseid
, yourref
, orderdate
, deliverydate
, salesperson
, remarks
from hdr@inmemorystorage
identified
by sales_order_id
insert into exactonlinerest..salesorderlines@eol
( description
, item
, quantity
, unitcode
)
select description
, itm_id
, mle_ordqua
, unitcode
from lne@inmemorystorage
attach
to sales_order_id
commit
Release 20 and earlier Resulting API Call Log
The resulting log looks like this (anonymized) and is ready for delivery to Exact Online support. Please remember to include the associated log on code when you report the call to Exact:
09:52:28.886-25: Native request body on POST to 'https://start.exactonline.nl/api/v1/11111/salesorder/SalesOrders':
09:52:28.887-25: {
"DeliverTo":"eeeeeeee-5006-4bec-a3ce-c6bfeb80474c",
"DeliveryDate":"2018-03-01T00:00:00",
"Description":"EDI order 123123132 van 23-02-2018",
"OrderDate":"2018-02-23T00:00:00",
"OrderedBy":"55555555-c331-40fb-ac9f-0b9d104353df",
"Remarks":"23-02-2018\r\nEnvid: 423423152\r\nAfzender: 8123123123123\r\nReverse: \r\nOntvanger: 8234342423432\r\nBestand: c:\\temp\\in\\Orders_4234234324324.xml",
"SalesOrderLines":[
{
"Description":"Trekpeerdraad",
"Item":"44444444-8e9e-417b-a332-60777e131145",
"Quantity":24.0
},
{
"Description":"Trekperen doos",
"Item":"22222222-9158-4f79-884c-ef4753d92f75",
"Quantity":24.0
},
{
"Description":"Reserve Trekpeer",
"Item":"33333333-c656-4bd3-b345-907bd1c07976",
"Quantity":12.0
}
],
"Salesperson":"4444444-3717-4f75-8df3-c13fddf561de",
"WarehouseID":"1111111-8e9e-416d-9e28-bda6f44382b0",
"YourRef":"123123132"
}
09:52:28.889-25: New result URL: https://start.exactonline.nl/api/v1/11111/salesorder/SalesOrders
09:52:29.580-25: Native response body on POST to 'https://start.exactonline.nl/api/v1/11111/salesorder/SalesOrders':
09:52:29.581-25: {
"d":{
"__metadata":{
"uri":"https://start.exactonline.nl/api/v1/11111/salesorder/SalesOrders(guid'dddddddd-6bd8-43db-8fc1-d8b4f766f218')",
"type":"Exact.Web.Api.Models.SalesOrder"
},
"AmountDC":123.12,
"AmountDiscount":0,
"AmountDiscountExclVat":0,
"AmountFC":123.12,
"AmountFCExclVat":123.12,
"ApprovalStatus":1,
"ApprovalStatusDescription":"Automatically",
"Approved":"\/Date(1522489949107)\/",
"Approver":null,
"ApproverFullName":null,
"Created":"\/Date(1522489949520)\/",
"Creator":"00000000-c43e-4fd1-b8ce-517e55e49173",
"CreatorFullName":"Invantive",
"Currency":"EUR",
"DeliverTo":"eeeeeeee-5006-4bec-a3ce-c6bfeb80474c",
"DeliverToContactPerson":null,
"DeliverToContactPersonFullName":null,
"DeliverToName":"Shop in shop",
"DeliveryDate":"\/Date(1519862400000)\/",
"DeliveryStatus":12,
"DeliveryStatusDescription":"Open",
"DeliveryAddress":"11111111-99c0-47b8-999a-68fc124c284d",
"Description":"EDI order 123123132 van 23-02-2018",
"Discount":0,
"Division":11111,
"Document":null,
"DocumentNumber":null,
"DocumentSubject":null,
"InvoiceStatus":12,
"InvoiceStatusDescription":"Open",
"InvoiceTo":"55555555-c331-40fb-ac9f-0b9d104353df",
"InvoiceToContactPerson":"22222222-a58b-4e7a-92ac-476aae8ee676",
"InvoiceToContactPersonFullName":"Facturen",
"InvoiceToName":"p/a Shops",
"Modified":"\/Date(1522489949520)\/",
"Modifier":"00000000-c43e-4fd1-b8ce-517e55e49173",
"ModifierFullName":"Invantive",
"OrderDate":"\/Date(1519344000000)\/",
"OrderedBy":"55555555-c331-40fb-ac9f-0b9d104353df",
"OrderedByContactPerson":"ffffffffffff-5006-4bec-a3ce-c6bfeb80474c",
"OrderedByContactPersonFullName":"John Doe",
"OrderedByName":"p/a Shops",
"OrderID":"dddddddd-6bd8-43db-8fc1-d8b4f766f218",
"OrderNumber":76,
"PaymentCondition":"36",
"PaymentConditionDescription":"Nu",
"PaymentReference":null,
"Remarks":"23-02-2018\r\nEnvid: 423423152\r\nAfzender: 8123123123123\r\nReverse: \r\nOntvanger: 8234342423432\r\nBestand: c:\\temp\\in\\Orders_4234234324324.xml",
"Salesperson":"4444444-3717-4f75-8df3-c13fddf561de",
"SalespersonFullName":"EDI-bewaking",
"ShippingMethod":"bf944267-5a8c-4ec3-8903-49f7a9fa9c67",
"ShippingMethodDescription":"DHL",
"Status":12,
"StatusDescription":"Open",
"TaxSchedule":null,
"TaxScheduleCode":null,
"TaxScheduleDescription":null,
"WarehouseID":"1111111-8e9e-416d-9e28-bda6f44382b0",
"WarehouseCode":"001",
"WarehouseDescription":"Magazijn Centraal",
"YourRef":"123123132",
"SalesOrderLines":{
"__deferred":{
"uri":"https://start.exactonline.nl/api/v1/11111/salesorder/SalesOrders(guid'dddddddd-6bd8-43db-8fc1-d8b4f766f218')/SalesOrderLines"
}
}
}
}