File System Structure Exact Online

The following sample specifies a folder structure for Exact Online consisting of a number of levels as shown in the picture:

The levels are:

•Root folder

•Containing Exact Online companies.

•Each containing a folder 'Accounts'.

•Each containing all document attachment files of that account.

In more detail, the root folder has the following structure:

•The root folder allows the user to get a list of files and folders contained as specified by SupportsListing.

•The root folder contains solely directories as specified by the type 'DIR' in the query.

•One folder exists per Exact Online company listed in the table SystemDivisions using the query in ListSqlStatement:

select sdn.Code code, sdn.Label name, 'DIR' type, sdn.code division from SystemDivisions sdn

Each Exact Online company folder has a query that specifies a folder named 'Accounts':

select :code CODE, 'Accounts' name, 'DIR' type

The Accounts folder contains all customers and suppliers using:

--

-- Switch to Exact Online company of the parent parent folder.

--

use :code;

--

-- Get a list of account folders, irrespective whether they have

-- any associated documents.

--

select to_char(id) code

,      name

,      'DIR' type

,      Division

from   ExactOnlineREST..Accounts

The Accounts folder contains all available documents on an account using the (annotated) query:

--

-- Select the Exact Online company using the division from the parent folder.

--

use :division;

--

-- Get list of the attachments on the account withut

-- retrieving the actual contents.

--

select /*+ http_disk_cache(false) http_memory_cache(false) */ to_char(dae.ID) code

,      dae.FileName name

,      dct.Created date_created

,      dct.Modified date_modified

,      dae.FileSize file_size

,      'FILE' type

,      dct.Division Division

from   ExactOnlineREST.Documents.DocumentAttachmentsBulk dae

join   ExactOnlineREST.Documents.DocumentsBulk dct

on     dct.id = dae.Document

and    dct.Account = :code

When a user downloads a document, the following code is executed:

use :division;

select coalesce(dae.Attachment, dae.AttachmentFromUrl) file_contents

from   ExactOnlineREST.Documents.DocumentAttachmentFiles dae

where  dae.id = :code

When a user uploads a document, the following code is executed to upload the document into Exact Online:

--

-- Switch to the right Exact Online company.

--

use :division;

--

-- Invantive Procedural SQL executed on upload file to FTP server.

-- Uploads the file into Exact Online as a document of type

-- 'Attachment'.

--

declare

 l_dct_subject       varchar2;

 l_dct_type          pls_integer := 183; /* Attachment. */

 l_dct_category_name varchar2    := 'General';

 l_dct_date          date;

 l_account           varchar2    := :code;

begin

 --

 -- Generate unique subject for the document. The file

 -- is added as an attachment to the document.

 --

 l_dct_subject := :file_name || ' ' || to_char(sysdate, 'YYYYMMDDHH24MISS') || '-' || newid();

 l_dct_date    := trunc(sysdate);

 --

 -- Create document.

 --

 insert into exactonlinerest..documents

 ( division

 , account

 , body

 , category

 , contact

 , documentdate

 , financialtransactionentryid

 , opportunity

 , project

 , salesinvoicenumber

 , salesordernumber

 , shopordernumber

 , subject

 , type

 )

 select :division

 ,      l_account

 ,      'Invantive Business Server' body

 ,      dcy.id

 ,      null

 ,      l_dct_date

 ,      null

 ,      null

 ,      null

 ,      null

 ,      null

 ,      null

 ,      l_dct_subject subject

 ,      l_dct_type type

 from   DocumentCategories dcy

 where  dcy.division    = :division

 and    dcy.description = l_dct_category_name

 ;

 --

 -- Attach a file to the document in Exact Online.

 --

 -- Contents of the file will match the value of the bind

 -- parameter :file_contents, whereas :file_name is filled

 -- with the name of the file uploaded to the FTP server.

 --

 -- The document previously created in Exact Online is matched

 -- using the unique subject.

 --

 insert into exactonlinerest..documentattachmentfiles

 ( division

 , document

 , attachment

 , filename

 )

 select :division

 ,      dct.id

 ,      :file_contents

 ,      :file_name

 from   exactonlinerest..documents dct

 where  dct.division     = :division

 and    dct.subject      = l_dct_subject

 and    dct.documentdate = l_dct_date

 ;

end;