Please enable JavaScript to view this site.

Invantive Notes

Navigation: English > LinkedIn

Report Exact Online Support Questions

Scroll Comments Prev Top Next More

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.

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

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"

     }

   }

 }

}