The following sample specifies a folder structure for Exact Online consisting of a number of levels as shown in the picture:
*Exact Online FTP Server using Invantive Business Server*
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':
```sql
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:
```sql
--
-- 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;
```