Importing Item Sales History

Import sales history into Item History for MAT and customer/item analysis.

Bevica Item Sales History is a separate extension. Please contact your system administrator or your TVision contact if you would like further information or would like to install Bevica Item Sales History.

You can import item-related sales history from your previous system to combine it with Bevica sales data. Imported into the Item History (Sales) page, this can give you a basis for reporting and is used when viewing Item Moving Annual Total Sales Analysis (MAT) and Customer Item Sales Analysis from customer and Item cards.

Prerequisites

Understanding Item Sales History functionality. See Item History (Sales).

Importing Data

This one-off exercise has three steps:

1. Use the B-ITEM HISTORY Configuration Package to prepare and import your historical data. You can find a copy at the end of this article.
2. Run Update Imported Item History to populate additional history fields.
3. Run Populate Customer/Item Sales Analysis to include the new information into this report.

1. 'B-ITEM HISTORY' Configuration Package

1. In the button, search for Configuration Packages.
2. Once opened, select the B-ITEM HISTORY package.
3. Export table 70545 to Excel as your template.

The template, as standard, has these fields.

Field	Mandatory	Note
Entry No.		Enter an entry number on the first line and increment by 1 on each subsequent line. If the item history table is empty then enter Entry No. = 1 in the first line. If you already have data in the item history table then make a note of the last entry number in the table and start the first line with the number after that. Note you can delete existing sales item history data before importing the file. To do this search for Bevica Setup, select Related, Operations, Item History, Delete Item History.
Source No.	Yes	Must be a valid Customer number.
Document Type Invoice	Yes	Must be either 'Sales Invoice' or 'Sales Credit Memo'.
Document No. (Invoice/Credit)		Use this to reference the primary document number for the sales line.
Posting Date	Recommended
Shipment/Receipt Date		If this field is left blank, Bevica will populate it from the Posting Date.
Item No.	Yes	Must be a valid Item No.
Duty Status		Must be either the duty-free or duty-paid code in your system.
Transaction Type		Must be either blank or 'Sale into Reserve'.
Location Code		Must be a valid Location Code.
Quantity	Yes	Enter the quantity sold/credited in the unit of measure in the next field. Should be a positive value.
Unit of Measure Code		Enter a valid Unit of Measure Code for the item. If left blank, Bevica assumes the quantity is the item's base unit.
Unit Price		Set the price per unit. Should be a positive value.
Line Discount Amount		Set the total line discount for the line where applicable.
Line Amount		Set the total amount the customer has paid for the line, it should include any discount. Should be a positive value.
Unit Cost (LCY)		Set the unit code for the line. If populated, Bevica will calculate Margin % and Cash Margin fields. Should be a positive value.
Currency Code		If the prices fields above are in currency, fill the Currency Code. It must be a valid currency code within Bevica.
Drop Shipment		If the line is an ExCellar sales line set this to 'true'.
Currency Exchange Rate		If you don't want to use Currency Exchange Rates in Bevica you can populate the rate here and the update routine will use this instead. If the field is left blank, the standard exchange rates will be used.
Note		Free 100 character free text field for any miscellaneous reference you want to add to the line.

There are additional fields that can be added to the configuration package line if necessary.

Values should be positive and the update report below will update the values based on the document type.

2. Update Imported Item Sales History

1. In the button, search for Bevica Setup.
2. Once opened, select Related, Operations, Item History, Update Imported Item History option from the page ribbon.

This routine updates additional fields on the newly imported Item Sales History records.

• From the customer page, Name is populated if not already filled in.
• From the Item card, Description, Item Category, and Item Group Codes are populated if not already filled in.
• The set of LCY Sales fields (Unit Price (LCY), Line Discount Amount (LCY), Line Amount (LCY)) are all populated by the update routine.
• Shipment Date is copied from Posting Date if Shipment Date is blank.
• Margin % and Cash Margin are calculated.

Note

Imported records do not create any posted Bevica data, i.e. Item Ledger Entries, Posted Sales Invoices, Posted Credit Memos, G/L Entries, or VAT Entries.

3. Update Customer Item Sales Analysis

1. In the button, search for Bevica Setup.
2. Once opened, select Related, Operations, Item History, Populate Customer/Item Sales Analysis option from the page ribbon.

This routine creates a record in the Customer/Item Sales Analysis page for every new combination of Customer & Item.

Deleting Item Sales History records

This routine will delete Item Sales History records, if you need, for example, to start again. To do this, open the routine and filter it to the specific records you want. To delete only imported records, filter the System Created Entry field to 'false'.

To run the routine,

1. In the button, search for Bevica Setup.
2. Once opened, select Related, Operations, Item History, Delete Item History option from the page ribbon.