Read UBL invoices

<< Click to Display Table of Contents >>

Navigation:  Accounting >

Read UBL invoices

This function is started via the menu with Bookkeeping> Financial> Read UBL invoices.

It enables the processing of electronic purchase invoices complying to the UBL format.

 

The screen is composed of three parts.

In the upper left part a list is shown of all available invoice files displaying relevant invoice details found these files.

The upper right part presents details of the creditor being recognized.

The journal proposal at the lower part of the screen gives a representation of the invoice details and the status of each item in the recognition process.

 

The following conditions apply for UBL files to be able to be processed.

The file needs to be a valid UBL file. The file name must end with the .xml extension.

In the bookkeeping the sender of the invoice needs to be present as a creditor.

The creditor may not be blocked.

The currency of the creditor must be equal to the currency of the invoice.

For each invoice line with an amount a general ledger account needs to be available.

The general ledger accounts may not be blocked.

For each VAT category on the invoice a corresponding VAT code needs to be present.
The VAT code in the bookkeeping is determined on the basis of the VAT type and the percentage.

The invoice date must fit within the date boundaries of the bookyear.

The period in which the invoice needs to be booked may not be closed or blocked.

 

Files not in the list (#)

This button will show the UBL files that are not listed due to technical errors.

 

How it works:

The VAT code entered at the creditor determines how the invoice will be booked.

 

Exception: Via Maintenance > Settings > Book > UBL invoices the option be set to have no VAT calculated for the the bookkeeping when reading UBL files.

All invoices will be treated as if the VAT code for the creditor is set to 0% and VAT does not apply. This can be useful when the company of the bookkeeping is exempt from VAt.

This option is not applicable to service companies.

 

No VAT:

When the creditor has a VAT code with 0% and a type No VAT or Default VAT, no VAT will be calculated.

The VAT amount stated in the UBL file in the invoice lines, will be added to the nett amount of that invoice line.

When the VAT is not stated in the invoice line but in the invoice header as a total amount including VAT, the VAT amount will be booked on the GL account Daybook Differences set in the Settings - Book - General.  

 

VAT:

When the creditor has a VAT code with a percentage higher than 0, the VAT mentioned in the UBL file will be booked.

 

VAT shifted inside the Netherlands:

UBL invoices for creditors with a VAT code of this type should not contain VAT amounts. UBL invoices with a VAT amount will be rejected.

When processing the invoice, Mill7 will ask for the percentages of VAT shifted. This will generate two extra journal entries for each VAT percentage.

 

VAT shifted inside and outside the EU:

UBL invoices for creditors with a VAT code of this type normally should not contain VAT amounts, but exceptions are possible.

The VAT amount will be treated as an indication that the VAT amount can be claimed in the country of the creditor.

When processing the invoice, Mill7 will ask for a GL account to book the VAT amount on. This will generate an extra journal entry with the VAT amount.

If there is no VAT included in the UBL file, Mill7 will ask for the percentages of shifted VAT. This will generate two extra journal entries for each VAT percentage.

 

At the end of this help topic is described how this process can started in a batch.

The following topics are described below:

1.Selecting invoice

2.Account recognition

a.Recognition Creditor

b.Recognition General ledger

3.Invoice details

4.Refresh data

5.Book journals

6.Reading UBL invoices in batch

 

Selecting invoice

The list can be sorted in different ways. Click on the column title to change the order. Any subsequent click on the same column title will invert the order.

 

Show invoices for current administration only

With this check box invoices for other administrations can be excluded from the list. Without the check mark all invoices will be displayed.

 

<File location>

With the <File location> button the folder containing the invoice files to be read can be set.

Use the prompt button or the F4 key to select a folder. The prompt function does not support UNC paths, but paths, including UNC paths, can be typed or pasted into the field.

After confirming the location by clicking the <OK> button the file list will be refreshed.

If necessary the file list can be refresh by pressing the F5 key.

 

Attention

Service Companies share the setting for the file location. This can be managed in two ways.

1.Store all ubl files for the service companies together in one folder and distinguish between the addressed companies by the details of the files.

2.Store all ubl files in separate folders and check/change the setting each time when ubl files for a service company are to be read.

 

The selected file can only processed when

- it is a xml file and

- is compiled to the ubl specifications (version 2.1).

 

<Show PDF>

When there is also a pdf file use this button to open the invoice in a pdf reader.

For Mill7 to recognize the pdf the file must have the same name as the selected xml file ans is saved at the same location as the xml file.

When the pdf file is embedded in the xml-file, Mill7 will open the pdf file from the xml file and save it as a pdf file with the same name and at the same location as the xml file.

 

The file list is sorted by creditor name and invoice number.

The list can be browsed to find and select a particular invoice.

Details of the creditor being recognized will be updated when another invoice is selected.

Comparing the details allows to establish whether the creditor linked to the invoice is correct or not.

It will also be clear if all invoice lines can be matched with general ledger accounts.

Invoice lines that cannot be processed because of failed recognition or otherwise are marked with a red error message in the status column.

An explanation of the error will be presented in the status bar below the journal proposal.

The invoice line concerned must be selected to be able to see this explanation.

The error description should explain what is needed to succeed with the recognition.

 

Account recognition

For future invoices recognition rules can be saved by using the <Account recognition> button.

This can be either for recognizing a creditor as for recognizing a ledger account.

Account recognition rules can also be set through menu Maintenance> Financial> Recognition UBL invoice lines

 
Account recognition functions are only available for users with the right 'Maintain account recognition UBL invoices'.
 

The recognition rules are saved in the table set at Settings> Book> Other.

The same table can be used in different bookkeepings.

 

Account recognition can be used for creating new rules and for changing current rules.

When the button is clicked a screen appears in which relevant features out of the invoice file are presented.

The recognition is performed line by line. With the <Previous> and <Next> button it is possible to switch lines.

 

Information, amount, date

All information found in the xml file for the selected invoice line.

 

Text to recognize

The recognition will be executed for this text to be recognized which should be equal to (a part of) the text in the Information field.

 

Account

Select the general ledger or creditor to be recognized.

 

Cost centre

When the ledger account is a cost type account then a cost centre must be provided as well.

The cost centre is pre-filled with the default cost centre of the cost type at the general ledger.

 

Recognition creditor

When a recognition rule selects a creditor the creditor code is used in the invoice details.

When there is no rule available Mill7 searches for

1.a creditor with a search name that exactly matches the creditor name in the xml file.

2.When  no creditor is found Mill7 searches for a creditor with the same

a.VAT id number or

b.CoC number or

c.IBAN number or

d.debtor number provided by the creditor.

 

A creditor found is only recognized when the currency of the creditor is the same as the invoice currency.

 

When a creditor has been recognized relevant details of the creditor will be displayed in the info box 'Creditor recognized'.

 

Recognition general ledger

General ledgers also can be recognized by a rule.

When there is no rule Mill7 will use the default opposite account at the creditor if available.

 

Invoice details

Account

This field present the Creditor code or the general ledger account.

 

Date

The invoice date according to the xml file.

 

Expiry date

The expiry date according to the xml file.

When no date is found Mill7 will calculate the expiry date using the payment conditions of the creditor.

 

Cost centre

When the ledger account is a cost type account the cost centre is obligated.

The cost centre is determined by the following rules:

1.the cost centre according to the recognition rule

2.the creditor cost centre

3.the default cost centre of the general ledger cost type

 

Currency

The currency according to the xml file.

 

Rate

In case the invoice currency is different from the bookkeeping currency Mill7 will determine the rate on the basis of the invoice date and the rate table hearing to the daybook creditor.

 

Debit/Credit

Invoice detail amount according to the xml file.

 

VAT code

Mill7 will determine a VAT code corresponding to the VAT % in the xml file.

 

Vat amount

Vat amount according to the xml file.

 

Invoice number creditor

The invoice number according to the xml file.

 

Status

Shows if the invoice line is ready to be processed or not.

When a line does not comply to all the conditions set a disclosure will be presented in the status bar below.

The items concerned will be shown red in the screen.

 

Refresh data

During the recognition process it may be necessary to create or modify a creditor or general ledger account.

After applying changes in the background, the F5 key can be used to refresh the journal proposal. It is not needed to close and restart the function for an update of the information displayed.

 

Book journals

With the <Process> button the invoice is booked in the creditor daybook.

 

When a UBL file is processed the file will be renamed.

A time stamp will be added to the file name and the file extension will be changed to ".old".

The UBL file disappears from the file list.

 

Special VAT situations:

 

VAT shifted, Delivery/service inside EU and outside EU

This functionality will not be executed when UBL invoices are read in batch mode.

 

For the benefit of the Dutch VAT declaration extra journals can be created automatically.

In case of one of these VAT types the screen VAT calculation will open.

At this screen the invoice amount can be split up in maximum three parts with different VAT %.

The VAT amount for every part will be used to create the VAT journals.

 

With the <OK> button the invoice journals are created including the VAT journals.

With the <Cancel> button, the VAT Calculation is closed without saving any changes. If the booking of the reversed VAT is skipped the invoice is booked without creating the VAT journals.

 

Foreign VAT to be booked

This functionality will not be executed when UBL invoices are read in batch mode.

When the creditor had a VAT code for transactions within the EU or outside the EU and VAT is specified in the UBL file, thje user will be prompted to assign a GL account for the VAT amount. The extra journal made can be used for claiming the VAT in the country of the creditor at a later stage. The expenses will be booked without the VAT.

The GL account number will be stored for future use.

When the <Cancel> button is used the VAT amount will be booked on the standard differences account.

 

VAT to be booked on expense accounts

This functionality will not be executed when UBL invoices are read in batch mode.

When the VAT is supposed to be included in the expenses, for instance because the company is not subject to VAT, and the information is missing to distribute the VAT to the invoice lines correctly, then the user will be asked to provide a GL account to (temporarily) book the VAT on. Later, the user can distribute the VAT manually by changing or adding journal entries.

The GL account number will be stored for future use.

When the <Cancel> button is used the VAT amounts will be booked on the standard differences account.

 

Creditor is not subject to VAT

The Creditor VAT code is a VAT type 'No VAT'.

In that case the VAT amounts of the invoice are booked on the expense accounts.

When not enough information available in the xml file, the VAT amount is directly booked to the general ledger of the creditor's VAT code.

 

Reading UBL invoices in batch

To read UBL invoices in batch Mill7 should be started by using the following line:

 

Millogix.exe /READUBL “<File location>” <VAT percentage shifted (*100)> <GL account for claiming VAT in country creditor> <GL account for VAT to be booked on expense accounts>.

 

<File location>

All UBL (XML) invoice files must be placed in this location (in the form of a file path).

The log file created will also be saved in this path, containing the date and time in the name of the file.

 

Invoices processed will be renamed with the extension ".old".

 

<VAT percentage shifted (*100)>

This percentage will be applied to all invoices of creditors with a VAT code shifted.

If the percentage is set to 0, no additional journal entries will be booked.

The percentage must be multiplied by 100 to prevent errors due to decimal signs.

7.5% will be 750 when used as a parameter.

 

<GL account for claiming VAT in country creditor>

When the creditor uses a VAT code shifted inside or outside the EU and the UBL files contains a VAT amount, this GL will be used to book the amount.

The costs will be booked without VAT.

If the GL account is not specified, these invoices will be rejected in the batch process.

The GL account specified must exist and may not be used as a daybook account or be blocked, otherwise these invoices will be rejected as well.

 

<GL account for VAT to be booked on expense accounts>

 

When the VAT cannot be claimed, for instance because the company is not subject to VAT, then the VAT should be included in the expenses. To do this correctly, the VAT amount or the VAT percentage must be known for each invoice line. This information can be specified in the UBL file per invoice line. When the VAT information is missing on this level and the VAT amounts per invoice line cannot be determined otherwise, the VAT amounts should be distributed manually by the user. In the mean time the VAT totals of the invoice will be booked on a special GL account.

If the GL account is not specified, these invoices will be rejected in the batch process.

The GL account specified must exist and may not be used as a daybook account or be blocked, otherwise these invoices will be rejected as well.

 

Invoices which are not processed remain in the folder and can be imported manually by the user, using the option in menu Bookkeeping>  Financial> Read UBL file.

By doing so and adding the recognition to the system and processing the invoice, the next time similar invoices will be processed automatically.

 

For each UBL file one of the following lines is saved in the log file, stating also the UBL file name.

 

1.Processed in bookkeeping

2.Rejected

a.Error: This is not a valid XML file [parse error]

b.Error: This is not a valid XML file [no root element]

c.Error: This is not a valid or supported UBL file

d.Error: This is not a valid UBL file [invalid value]

e.Error: This is not a valid UBL file [tax exclusive amount and sum of taxable amounts mismatch]

f.Error: This is not a valid UBL file [total taxable amount and sum of invoice line amounts mismatch]

g.Error: Bookkeeping with suitable start and end date not found for client name:
The client name (Bookkeeping name) is detected via the XML element <AccountingCustomerParty - Name> in the UBL file.

3.Problem with processing

a.Warning: VAT percentage missing, reverse-charged not booked.

b.Error: Bookkeeping found, but file cannot be processed.

c.Error: Account for foreign VAT missing.

d.Error: Account for foreign VAT not found.

e.Error: Account for foreign VAT is blocked.

f.Error: Account for VAT to be booked on expense accounts missing.

g.Error: Account for VAT to be booked on expense accounts not found.

h.Error: Account for VAT to be booked on expense accounts is blocked.

 

The account for foreign VAT refers to the GL account used as parameter <GL account for claiming VAT in country creditor> when the batch is executed.

The account for VAT to be booked on expense accounts refers to the GL account used as parameter <GL account for VAT to be booked on expense accounts> when the batch is executed.