Overview

The increasing number of subsidiaries and acquired brands, gives rise to an increased need to import Purchase Invoices or Credit Notes in a faster way, making use of excel import sheets.  

Target benefits

By creating a customised solution, a more user-friendly solution will be made available to the user thus reducing the preparation time whilst spreading the work load to more users as it reduces the direct use of NAV.

Solution setup

Purchase & payables setup

Dimensions for the import have to be set up in the Purchase & Payables setup. Please navigate to Departments / Financial Management / Payables / Setup / Purchases & Payables Setup, open the Purchase Import Process tab and fill in the following values:

Open

Purchase import mapping

The Purchase Mapping Tables creates a link with the import sheet expenses to the G/L Account against which it will be posted. User need to set up the proper link between the Purchase Import Mapping Code (present in the import file) and the actual G/L Account which will be used in the imported document.

Please navigate to Departments/Purchase/Order Processing/Purchase Import Mapping and create all Expense types used in the import files.

Open

Format of the import file

The import file is to have the following columns:

• Posting Date

• Vendor No.

• Vendor Document No.

• Expense Type

• Currency Code

• Description

• Quantity

• Unit Price

• Discount %

• Dimension Value 1 - Product Code

• Dimension Value 2 - Department

• Dimension Value 3 - Segment

• Dimension Value 4 - Location

• Prices Including VAT (accepted values are “yes” and “no”)

• Attachment

• Bank Account No. (if specified, will create Journal line instead of Purchase document)

• Gen. Prod. Posting Group

• VAT Product Posting Group

An example of the import file will be attached to this document. The data should look something like this:

Non-English speaking countries file format

The file has to be in *.csv (COMMA DELIMITED) format. There could appear a problem in non-English speaking countries with regional settings because there is sometimes used a semicolon instead of a comma to delimit values in the CSV files.  So if you are able to choose CSV file via purchase import worksheet, but import doesn’t work, please follow these steps.

1. Click on start.

2. Open control panel.

3. Open Clock and Region.

4. In the region window open additional settings and number tab

5. In the list separator, make sure there is comma (,)

After this setup is done, it’s better to use Libre Calc for open and work with CSV files instead of Excel.

Purchase Import Worsheets

Please navigate to Departments/Purchase/Order Processing/Purchase Import Worksheet.

Action: Import Records

Upon clicking this button, the user will be prompted by a call to upload the worksheet.  The following information will be requested:

• File Path

• Original user sending the excel sheet

Since we are using the test import file mentioned in the “Format of the import file” section, the following records have been imported (Entry No. doesn’t start from 1 because of previous testing).

Action: Cancel Records

By selecting the record(s) and clicking the Cancel Button the user will be setting the status to cancelled.

Action: Postpone Records

The Postpone is aimed at marking the record(s) which for some reason needs further investigation.  By selecting the record(s) and clicking the Postpone Button the user will be setting the status to postponed.  Any postponed records will not be taken into consideration when creating the Purchase Documents.

After confirmation, the status of the record is changed to Postponed.

Action: Reset Records

After cancelling or postponing the records, the user may reset it back to pending. By selecting the record(s) and clicking the Reset Button the user will be setting the status to pending.

By marking all the records and using the Reset Records function, the status will be changed back to Pending (only if the status is not marked as posted or Doc. Created).

Action: Pre-check Records

The validate process will be checking the following information:

• Vendor No. exists

• Vendor Document No. hasn’t been used before

• Expense Type Exists

• Dimension code 1 exists and is not blocked

• Dimension code 2 exists and is not blocked

• Dimension code 3 exists and is not blocked

• Dimension code 4 exists and is not blocked

• Currency Code exists

• Bank Account No. exists

• Gen. Prod. Posting Group exists

• VAT Bus. Post. Group exists

In our example the line with Entry No. 7670 and 7671 has blocked dimension value.

After user uses the Pre-check Records function, the Status is changed to Error and the Error Message provides the user with additional information about the error.

After the information is corrected and user uses the Reset Records function and Pre-check records function again, there are no errors.

Action: Process Invoices

Upon Clicking this button, the system will be reviewing all the records with a positive quantity.  Lines will not be aggregated but the system will identify lines linked to the same invoice through the Posting Date and Vendor Document Number.  In case the Bank Account field is provided, General Jnl. Line will be created, otherwise it creates the Invoice document. Being an invoice, the Vendor Document Number will be inserted in the Vendor Invoice Number.  

Upon creating the Invoice (or Gen. Jnl. Line), the line entry status will be changed to Doc. Created).

In our case the Posting Date, Vendor No. and Vendor Document Number are the same, but the Currency Code is different, so we should end up with the invoices created:

Please verify that the attachment (if there is any in the Purchase Import file) is visible after using the “Links” action in the ribbon menu.

All document created from the Purchase Import Worksheet should not be editable by the user.

After the document is posted by the user, the entries in Purchase Import Worksheet will have their Status changed to Posted.

Action: Process Cr. Memos

Upon Clicking this button, the system will be reviewing all the records with a negative quantity.  Once again, lines will not be aggregated but the system will identify lines linked to the same invoice through the Posting Date and Vendor Document Number.  The Latter will be inserted in the Vendor Credit Memo number field (or Gen. Jnl Line if Bank Account field is provided in the feed file). 

Upon creating the Credit Memo (or gen. Jnl Line), the line entry status will be changed to Doc. Created).

Action: Process All

Upon Clicking this button, the system will trigger the action Process Invoices or Process Credit Memos depending on the quantity for all records.

Enhanced functionality

When there at csv file is specified Bank Account No. (the one before last column), the functionality would create Journal line at Purchases Setup specified Template/Batch. The Purchase Invoice (Cr. Memo) wouldn’t be created in such a case. The Journal line(s) could be processed/Post the standard way.

In case Gen. Prod. Posting Group is provided in feed file, it would update the value at created Document Line (or Gen. Jnl. Line).

Purchase Header Table

A Purchase header table will have a new field, of type Boolean, named Purchase Upload.  This field will not be editable by the user.  Once it’s ticked it stops the user from amending in any way the Purchase Header or Purchase Line linked to the same invoice. However, the user may delete the Document.

Purchase Line Table

PIW Entry No.

The PIW (Purchase Import Worksheet) No. will be providing a direct link to the Purchase Import worksheet.  This will allow the system to update the status accordingly.

This field will be available in the posted documents as well – Posted Purchase Invoice, Posted Purchase Credit Memo.

Process

Import

The user may opt to either import the worksheet or else manually create the lines within the Import Upload worksheet.

Validate

The user may opt to run the validate process.  It’s optional as the same process will be re-run whilst creating the Purchase Invoices or Credit Memos.

Process Invoices

Through this process the user will trigger the Purchase Invoices Creation.

Process Credit Memos

Through this process the user will trigger the Purchase Credit Memos Creation.

Process all records

Through this process the user will trigger the creation of all imported documents.

Review Documents

The user can spot-test some documents in order to ensure that all the data has been correctly uploaded.

Post Documents

Both Purchase Invoices and Purchase Credit Memos are to be posted.

Other test scenarios

Since the information is informational in nature the tests to be performed are functional ones rather than accounting ones.  The following tests are necessary:

Importing the records

User chooses the import file in csv format and all records are imported to the Purchase Import worksheet. The format of the file will be specified with the solution delivery

Deletion Purchase import worksheet lines

System will show an error message when user tries to delete a record in Purchase Import Worksheet line table.

Manual creation of lines

User will be able to manually create new lines in the Purchase Import Worksheet page.

Modification of lines

User will be able to modify lines of Purchase Import Worksheet table which are in Status Pending, Cancelled, Postponed or Error. All other Statuses will cause the system to show an error message.

Cancelling records

User triggers the validation by using the Cancel action. Status of all selected records with Status = Pending is set to Cancelled. Records with Status different than Pending are not modified in any way.

Postponing records

User triggers the validation by using the Postpone action. Status of all selected records with Status = Pending is set to Postpone. Records with Status different than Pending are not modified in any way.

Validation of records

User triggers the validation by using the Validate action, this will trigger all the checks mentioned in Action: Validate section. If any of the checks fail, the record is marked with Error = TRUE and Error message field is filled with the reason for the error.

If the records pass the validation, there is no change done to the record.

Resetting records

Records with Status Cancelled or Postponed can have their status reset to Pending. Records with any other Statuses are not changed in any way.

Processing records

User has an option of processing the records in three ways:

• Only Invoices

• Only Credit Memo’s

• All records

After the respective function is used Lines will not be aggregated but the system will identify lines linked to the same invoice through the Posting Date and Vendor Document Number.

Each line will thus at minimum create a purchase line (or Gen. Jnl. Line) detail and unless pre-existent a new document.

Upon creating the document, the line entry status will be changed to Doc. Created) and each Purchase Line will have PIW Entry No. filled in with the Entry no. of the original Purchase Invoice Worksheet line. The new documents will have Purchase Upload field equal to TRUE.

Modification of the created documents

Documents originating from Purchase Upload will not be accepted, system will display an error message.

Deletion of the created documents

Documents originating from Purchase Upload can be deleted by any user.

Posting Records

After a document originating from the Purchase Upload is posted, the related lines (linked by PIW Entry No.) will have their Status updated to Posted.