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:
...
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.
...
Format of the import file
The import file is to have the following columns:
...
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.
...
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
| Info |
|---|
The Purchase Import Worksheet has three functionalities:
|
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:
...
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.
| Note |
|---|
A record may be cancelled solely if the status is marked as pending |
...
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.
| Note |
|---|
A record may be postponed solely if the status is marked as pending. |
...
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.
| Note |
|---|
A record may be reset solely if the status is not marked as posted or Doc. Created. |
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
| Note |
|---|
Any errors which may crop up will be change the status of the Record as Error and will insert a comment in the Error Message Field. |
In our example the line with Entry No. 7670 and 7671 has blocked dimension value.
...
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.
...
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.
...
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.