Inlezen bestelling (Exact Globe stock order) in Exact Online als verkooporder met Invantive Cloud

In dit artikel leer je hoe je een Exact Globe XML-bestand kunt importeren in een Exact Online administratie als verkooporder met een Invantive Cloud module.

Voor het uitwisselen van bestellingen met toeleveranciers kan ook een Exact Globe stock order XML-bestand gebruikt als een EDI-“standaard”. Een Exact Globe stock order bestand is in een voorbeeld XML-bestand aan het einde van dit artikel.

Alhoewel Exact Online zelf ook XML-bestanden kan importeren is het formaat van het XML-bestand anders. Daarnaast zijn het natuurlijk twee verschillende kanten: de een stelt een bestelling voor, de ander een verkooporder.

Dat inlezen van een XML bestand in Exact Online is erg snel en sterk aan te bevelen boven gebruik van de REST-tabellen (zie Does Invantive SQL match the speed of Infinite Probability Drive?).

De voorbeeldcode van de module kan eenvoudig toegevoegd worden via Applicaties → Kies → Modules → Toevoegen. Zie bijvoorbeeld Create a mini-site to download your Exact Online articles.

Werking importmodule

De importmodule presenteert een webpagina waar de gebruiker via de browser de locatie van het Exact Globe XML-bestand kan opgeven. Daarna kiest de gebruik de knop “Inlezen in Exact Online”. Na het inlezen wordt een Excel-bestand gedownload in de browser. In dit Excel-bestand staat het verwerkingsverslag en eventuele problemen. Ook bevat het verwerkingsverslag de XML die niet geïmporteerd kon worden; de geslaagde onderdelen zijn hier uit weggelaten zodat het eenvoudig handmatig gecorrigeerd kan worden.

De velden uit de bestelling worden 1-op-1 afgebeeld. Bijzonderheid is dat het GLN-number uit de bestelling (DeliverTo/Address/AddressLine2) gebruikt wordt om de bestellende partij te bepalen voor de verkooporder.

Een aantal controles worden uitgevoerd voor het daadwerkelijke importeren in Exact Online gebeurt zoals dat de artikelen ook echt bestaan en dat het GLN-number bekend is.

De importmodule is geschikt om duizenden orders in een korte tijd te importeren in één of meerdere administraties. Door het gebruik van de XML API is het benodigde aantal API calls beperkt, reken op 1 API call per 100 orderregels.

StockOrder voorbeeld XML-bestand

Alle XML-bestanden van een stock order zien er uit zoals:

<?xml version="1.0" encoding="UTF-8"?>
<eExact xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="eExact-Schema.xsd">
   <Orders>
      <Order type="B" number="123456">
         <OurRef>123456</OurRef>
         <Resource>
            <LastName>Tennis</LastName>
            <FirstName>Club</FirstName>
         </Resource>
         <OrderedAt>
            <Creditor number="258680" code="258680">
               <Name>ACME B.V.</Name>
            </Creditor>
            <Date>2022-03-11</Date>
         </OrderedAt>
         <DeliverTo>
            <Warehouse code="1">
               <Description>Hoofdmagazijn</Description>
            </Warehouse>
            <Address>
               <AddressLine1>Tuinstraat 123</AddressLine1>
               <AddressLine2>8712412345678</AddressLine2>
               <PostalCode>1020 AA</PostalCode>
               <City>Amsterdam</City>
               <Country code="NL" />
               <Phone>020-1699444</Phone>
            </Address>
         </DeliverTo>
         <InvoiceTo>
            <Warehouse code="1">
               <Description>Hoofdmagazijn</Description>
            </Warehouse>
            <Address>
               <AddressLine1>Tuinstraat 123</AddressLine1>
               <AddressLine2>8712412345678</AddressLine2>
               <PostalCode>1020 AA</PostalCode>
               <City>Amsterdam</City>
               <Country code="NL" />
               <Phone>020-1699444</Phone>
            </Address>
         </InvoiceTo>
         <OrderLine lineNo="1">
            <Description>Tennisbal</Description>
            <Item code="TB" />
            <Quantity>2</Quantity>
            <Price type="S">
               <Currency code="EUR" />
               <Value>1.65</Value>
            </Price>
            <Delivery>
               <Date>2022-03-11</Date>
            </Delivery>
            <ItemCode>TB</ItemCode>
         </OrderLine>
      </Order>
   </Orders>
</eExact>

Inleesmodule Invantive Cloud

De volgende module kan gebruikt worden in Invantive Cloud om een Exact Globe stock order in te lezen als sales orders (verkooporders) in Exact Online:

declare
  p_run                   boolean;
  --
  g_division_code         int64 := 123456;
  --
  l_filename               varchar2;
  l_payload                blob;
  l_workbook               excel_workbook;
  l_worksheet              excel_worksheet;
  l_binary                 blob;
  l_cnt                    number;    
  l_cnt_missing_item       number;
  l_cnt_missing_account    number;
  l_list_missing_items     varchar2;
  l_list_missing_accounts  varchar2;
begin
  --
  -- Get parameters.
  --
  p_run               := cast(cloud_http. get_request_form_value('p_run') as boolean);
  --
  if coalesce(p_run, false) = false
  then
    --
    -- Enter parameters.
    --
    cloud_http.set_use_template(true);
    cloud_http.set_template_step_name(translate_resources('{res:itgen_parameters}'));
    cloud_http.append_line_to_response_body_text('<form method="post" enctype="multipart/form-data">');
    cloud_http.append_line_to_response_body_text('<input type="hidden" id="p_run" name="p_run" value="true"/>');
    cloud_http.append_line_to_response_body_text('<ul>');
    cloud_http.append_line_to_response_body_text
    ( '<li><label for="p_file">' 
      || translate_resources('{res:itgen_file}') 
      || '</label></li><li><input type="file" id="p_file" name="p_file" accept=".xml" required/></li>'
    );
    cloud_http.append_line_to_response_body_text('<li><input type="submit" value="Inlezen in Exact Online"/></li>');
    cloud_http.append_line_to_response_body_text('</ul>');
    cloud_http.append_line_to_response_body_text('</form>');
  else
    --
    -- Process form.
    --
    l_filename := cloud_http.get_request_form_file_name('p_file');
    --
    if l_filename is null
    then
      raise_application_error('sample001', 'The file name is empty.');
    end if;
    --
    l_payload := cloud_http.get_request_form_file_contents('p_file');
    --
    if l_payload is null
    then
      raise_application_error('sample002', 'The file payload is empty.');
    end if;
    --
    -- Choose company.
    --
    use select code, 'eol' from systemdivisions@eol where code = g_division_code;
    --
    -- Find available parties with GLN-number.
    --
    create or replace table AccountsAvailable@inmemorystorage
    as
    select act.Division
    ,      act.GlnNumber
    ,      act.Code
    ,      act.Id
    from   AccountsIncremental act
    where  act.GlnNumber is not null
    ;
    --
    -- Make all items persistent.
    --
    create or replace table ItemsAvailable@inmemorystorage
    as
    select itm.Division
    ,      itm.Code
    ,      upper(itm.Code) CodeUC
    ,      itm.Id
    ,      itm.Description
    from   ItemsIncremental@eol itm
    ;
    --
    -- Convert Exact Globe format to a table with order lines.
    --
    create or replace table InboundPurchaseOrders@inmemorystorage
    as
    select odr.*
    ,      upper(odr.ItemCode) ItemCodeUC
    from   xmltable
           ( '/eExact/Orders/Order/OrderLine'
             passing l_payload
             columns OrderNumber            varchar2 path '../@number'  
             ,       LineNumber             int32    path '@lineNo'     
             ,       Quantity               int32    path 'Quantity'
             ,       ItemCode               varchar2 path 'ItemCode'
             ,       PriceCurrency          varchar2 path 'Price/Currency/@code'
             ,       PriceValue             decimal  path 'Price/Value'
             ,       DeliveryDate           date     path 'Delivery/Date'
             ,       Description            varchar2 path 'Description'
             ,       OrderRef               varchar2 path '../OurRef'
             ,       OrderFirstName         varchar2 path '../Resource/FirstName'
             ,       OrderLastName          varchar2 path '../Resource/LastName'
             ,       OrderAtCreditorNumber  varchar2 path '../OrderedAt/Creditor/@number'
             ,       OrderAtCreditorCode    varchar2 path '../OrderedAt/Creditor/@code'
             ,       OrderAtCreditorName    varchar2 path '../OrderedAt/Creditor/Name'
             ,       OrderAtDate            date     path '../OrderedAt/Date'
             ,       DeliverToWarehouesCode varchar2 path '../DeliverTo/Warehouse/@code'
             ,       DeliverToGlnNumber     varchar2 path '../DeliverTo/Address/AddressLine2'
           ) odr
    ;
    --
    -- Check that there is at least one purchase order line.
    --
    select count(*)
    into   l_cnt
    from   InboundPurchaseOrders@inmemorystorage
    ;
    if l_cnt = 0 
    then
      raise_application_error('XXCODE001', 'No purchase orders could be extracted from the file.');
    end if;
    --
    -- Add missing unique IDs from Exact Online.
    --
    create or replace table InboundPurchaseOrdersMatched@inmemorystorage
    as
    select itm.Division
    ,      itm.Id ItemID
    ,      act.Code DeliverToAccountCode
    ,      act.Id DeliverToAccountId
    ,      odr.*
    from   InboundPurchaseOrders@inmemorystorage odr
    left
    outer
    join   AccountsAvailable@inmemorystorage act
    on     act.GlnNumber = odr.DeliverToGlnNumber
    left
    outer
    join   ItemsAvailable@inmemorystorage itm
    on     itm.CodeUC = odr.ItemCodeUC
    ;
    --
    -- Check that all entries in the purchase order lines could be found:
    --
    -- * item exists,
    -- * GLN number is known.
    --
    select count(*)
           cnt
    ,      sum(case when ItemId is null then 1 else 0 end)
           cnt_missing_item
    ,      sum(case when DeliverToAccountId is null then 1 else 0 end)
           cnt_missing_account
    ,      listagg(distinct case when ItemId is null then ItemCodeUC else null end, ', ') 
           list_missing_items
    ,      listagg(distinct case when DeliverToAccountId is null then DeliverToGlnNumber else null end, ', ') 
           list_missing_accounts
    into   l_cnt
    ,      l_cnt_missing_item
    ,      l_cnt_missing_account
    ,      l_list_missing_items
    ,      l_list_missing_accounts
    from   InboundPurchaseOrdersMatched@inmemorystorage
    ;
    if l_cnt = 0 
    then
      raise_application_error('XXCODE002', 'No inbound purchase orders could be matched with available customers and items.');
    end if;
    if l_cnt_missing_item > 0 
    then
      raise_application_error('XXCODE003', 'There are ' || to_char(l_cnt_missing_item) || ' lines with unknown items in the file: ' || l_list_missing_items || '.');
    end if;
    if l_cnt_missing_account > 0 
    then
      raise_application_error('XXCODE004', 'There are ' || to_char(l_cnt_missing_account) || ' lines with unknown accounts in the file with GLN: ' || l_list_missing_accounts || '.');
    end if;
    --
    -- Convert order and order lines to XML format.
    --
    create or replace table sourcelinesxml@inmemorystorage
    as
    select src.Division
    ,      '<SalesOrder>'
           || chr(13)
           || '<OrderedBy ID="{'
           || to_char(src.DeliverToAccountId)
           || '}" code="'
           || src.DeliverToAccountCode
           || '" />'
           || chr(13)
           || '<DeliverTo ID="{'
           || to_char(src.DeliverToAccountId)
           || '}" code="'
           || src.DeliverToAccountCode
           || '" />'
           || chr(13)
           || '<InvoiceTo ID="{'
           || to_char(src.DeliverToAccountId)
           || '}" code="'
           || src.DeliverToAccountCode
           || '" />'
           || xmlelement('YourRef', src.OrderRef, false)
           head
    ,      '<SalesOrderLine line="' 
           || to_char(src.LineNumber) 
           || '">'
           || xmlelement('Description', src.description, false)
           || '<Item code="'
           || src.ItemCode
           || '" ID="'
           || to_char(src.ItemID)
           || '" />'
           || xmlelement('Quantity', src.quantity, false)
           || xmlelement('DeliveryDate', src.DeliveryDate, false)
           || '<UnitPrice>'
           || '<Currency code="' || src.PriceCurrency || '" />'
           || '<Value code="' || to_char(src.PriceValue) || '" />'
           || '</UnitPrice>'
           || '</SalesOrderLine>'
           line
    from   InboundPurchaseOrdersMatched@inmemorystorage src
    ;
    --
    -- Prepare XML upload.
    -- Note the use of XML upload; the REST upload is an order of magnitude slower.
    --
    create or replace table xmlupload@inmemorystorage
    as
    select 'SalesOrders' topic
    ,      division_code
    ,      null orig_system_reference
    ,      cast('origsystemgroup' as varchar2 null) orig_system_group
    ,      xmlformat
           ( '<?xml version="1.0" encoding="utf-8"?>'
             || '<eExact xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="eExact-XML.xsd">'
             || xmlcomment('For company ' || division_code)
             || xmlcomment('Original system group ' || 'origsystemgroup')
             || '<SalesOrders>'
             || xml
             || '</SalesOrders>'
             || '</eExact>'
           )
           filecontents
    from   ( select division_code
             ,      listagg
                    ( head
                      || lines
                      || '</SalesOrder>'
                    , ''
                    )
                    xml
             from   ( select brl.Division division_code
                      ,      brl.head
                      ,      listagg(brl.line, '') lines
                      from   sourcelinesxml@inmemorystorage brl
                      group
                      by     brl.head
                      ,      brl.division
                    )
             group
             by     division_code
           )
    ;
    --
    -- Upload into Exact Online.
    --
    insert into UploadXMLTopics@eol
    ( topic
    , payload
    , division_code
    , orig_system_reference
    , orig_system_group
    , fragment_payload_flag
    , fragment_max_size_characters
    , fail_on_error
    )
    select topic
    ,      filecontents
    ,      to_char(division_code)
    ,      orig_system_reference
    ,      orig_system_group
    --
    -- Try to split into pieces of 25 KB at most.
    --
    ,      true fragment_payload_flag
    ,      25000 fragment_max_size_characters
    --
    -- Returns error through the user interface, not as an HTTP 500 error.
    --
    ,      false fail_on_error
    from   xmlupload@inmemorystorage
    order
    by     division_code
    ,      orig_system_reference
    ,      orig_system_group
    ;
    --
    -- Retrieve results.
    --
    l_workbook := excel.new();
    --
    -- Add a worksheet with a title.
    --
    l_worksheet := excel.add_worksheet
    ( l_workbook
    , 'Parameters'
    );  
    excel.set_cell_contents(l_worksheet, 'A1', 'Exact Online import results using Invantive Cloud');
    excel.set_cell_contents(l_worksheet, 'A2', 'Created ' || to_char(sysdate));
    --
    create or replace table output@inmemorystorage
    as
    select /*+ result_set_name("Results") */
           uxt.transaction_id
    ,      uxt.successful
    ,      uxt.division_code
    ,      uxt.orig_system_reference
    ,      uxt.orig_system_group
    ,      uxt.date_started_utc
    ,      uxt.date_ended_utc
    ,      substr(uxt.payload, 1, 32767)
           payload_32k
           label "{res:itgen_eol_payload}"
    ,      substr(uxt.result, 1, 32767)
           result_32k
           label "{res:itgen_result}"
    ,      substr(uxt.result_errors, 1, 32767)
           result_errors_32k
           label "{res:itgen_eol_result_errors}"
    ,      substr(uxt.payload_retry_failed, 1, 32767)
           payload_retry_failed
           label "{res:itgen_eol_payload_retry_failed}"
    from   UploadXMLTopicFragments@eol uxt
    order
    by     uxt.transaction_id desc
    ;
    --
    -- Generate output.
    --
    l_worksheet := excel.add_worksheet
    ( l_workbook
    , 'Upload Results'
    );  
    excel.fill_using_query(l_worksheet, 'select * from output@inmemorystorage');
    --
    -- Retrieve the resulting XLSX-file.
    --
    l_binary := excel.export_to_xlsx(l_workbook);
    --
    -- Return the XLSX file to the user.
    --
    cloud_http.set_response_body_binary(l_binary);
    cloud_http.set_response_content_type('application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    cloud_http.set_response_header_value('Content-Disposition', 'attachment; filename="upload-results-' || basename(l_filename) || '.xlsx"');
  end if;
end;