This article illustrates how to create a sales order with lines on Exact Online using the REST-based tables.

Creation of sales order is generally recommended using the Exact Online XML APIs for reasons of an approximately 3 times better throughput and an order of magnitude higher daily quotas. The XML APIs can be accessed using UploadXMLTopics or https://get-my-report.com with an Excel-sheet as input.

Instructions

Exact Online has the peculiarity that it requires each transaction to have at least one line, which holds for sales orders, sales invoices and general ledger transactions. However, a standard insert statement creates just the header. Therefor, the header and the lines must be sent to Exact Online in one transaction.

Invantive SQL provides transaction-logic to accomplish this. The same transaction logic is also used for other connectors such as when creating EDIFACT files.

Execution of the Invantive SQL statements below will create a sales order like:

To create such a sales execute the following steps:

  • Choose the Exact Online company:

use COMPANYNUMBER
CODE
  • Enable native call logging when you want to keep an eye on the actual communication with Exact Online using:

set trace-native-calls true

set log-native-calls-to-disk
CODE
  • The native calls are logged by default in the %USERPROFILE%\invantive\nativelog folder.

  • Start a transaction:

begin transaction
CODE
  • Define the contents of the sales order:

insert into exactonlinerest..salesorders
( orderedby
, description
) 
--
-- Select a random customer and define a transaction 'TRANSACTION1',
-- one per sales order.
-- You can define and upload multiple Exact Online sales orders
-- and send them off in one Invantive SQL transaction.
--
select act.id
,      'buy some items' description
from   exactonlinerest..accounts act
where  act.status = 'C'
limit  1
identified by 'TRANSACTION1'
CODE
  • Add ten order lines to the sales order, each on a different item:

insert into ExactOnlineREST..salesorderlines 
( item
, linenumber
)
select itm.id
,      row_number()
       linenumber
from   exactonlinerest..items itm
where  itm.IsSalesItem = true
limit 10
attach to 'TRANSACTION1'
CODE
  • Hand over the sales order and the associated lines to Exact Online:

commit transaction
CODE
  • After the commit, Exact Online will return validation errors if any.

  • The outgoing native call found in the log folder will resemble (line breaks added for readability):

Outbound https://start.exactonline.nl/api/v1/102673/salesorder/SalesOrders Request using POST started at 2020-06-02 16:43:01 (UTC).
Object: ExactOnlineREST.SalesOrder.SalesOrders
Partition: 102673
Data container ID: https://start.exactonline.nl/8045575
User: john.doe@acme.com

{"Description":"buy some items","OrderedBy":"6d0bfbeb-730f-4290-b886-007392d22a0f"
,"SalesOrderLines":
[ {"Item":"1db1fa03-4b2d-4a54-bee2-005edae3934c","LineNumber":21}
,{"Item":"127fba93-64ce-4cb8-bd9c-00653edf0a13","LineNumber":22}
,{"Item":"46dd7805-15ab-4360-a383-008ce7f46811","LineNumber":23}
,{"Item":"0ed58c10-be4d-4cb9-9ffd-0100d261dc3b","LineNumber":24}
,{"Item":"8194860a-4624-4517-a54f-01194fe2f97d","LineNumber":25}
,{"Item":"cd09577e-e541-4e99-a495-020ab56faa78","LineNumber":26}
,{"Item":"65484fc2-c60c-4b3d-8538-021ad2036ee5","LineNumber":27}
,{"Item":"6e65e4cd-130c-4b3c-8f20-0255283e6368","LineNumber":28}
,{"Item":"0d998f82-fc15-48a8-bc6e-0261a89f0645","LineNumber":29}
,{"Item":"8fa70684-cce7-4916-b4fd-028a7c5af6d4","LineNumber":30}
]
}
JSON
  • The native answer from Exact Online interpreted by Invantive SQL will resemble:

Inbound https://start.exactonline.nl/api/v1/102673/salesorder/SalesOrders POST started at 2020-06-02 16:43:01 (UTC).
Object: ExactOnlineREST.SalesOrder.SalesOrders
Partition: 102673
Data container ID: https://start.exactonline.nl/8045575
User: john.doe@acme.com

{
"d" : {
"__metadata": {
"uri": "https://start.exactonline.nl/api/v1/102673/salesorder/SalesOrders(guid'94feaad3-69cc-46ad-9925-d02d2b7f994a')"
, "type": "Exact.Web.Api.Models.SalesOrder"
}
...
, "Created": "\/Date(1591123382670)\/"
, "Creator": "bd0e191e-6fb4-4537-bf91-9f68d4e62744"
, "CreatorFullName": "John Doe"
, "Currency": "EUR"
, "DeliverTo": "6d0bfbeb-730f-4290-b886-007392d22a0f"
, "DeliverToContactPerson": null
, "DeliverToContactPersonFullName": null
, "DeliverToName": "             61602"
, "DeliveryDate": "\/Date(1591056000000)\/"
, "DeliveryStatus": 12
, "DeliveryStatusDescription": "Open"
, "DeliveryAddress": "bb8da22d-fd4f-4f8e-bd96-b0b1de61bd6a"
, "Description": "buy some items"
, "Division": 102673
...
, "InvoiceStatus": 12
, "InvoiceStatusDescription": "Open"
, "InvoiceTo": "6d0bfbeb-730f-4290-b886-007392d22a0f"
...
, "OrderedBy": "6d0bfbeb-730f-4290-b886-007392d22a0f"
...
, "OrderNumber": 42
...
, "Status": 12
, "StatusDescription": "Open"
...
, "SalesOrderLines": 
{
"__deferred": {
"uri": "https://start.exactonline.nl/api/v1/102673/salesorder/SalesOrders(guid'94feaad3-69cc-46ad-9925-d02d2b7f994a')/SalesOrderLines"
}
}
}
}
JSON