You can import orders (Transactions) to Pepperi in order to:
- View Pepperi orders that have been exported to your ERP and updated their status or other fields.
- View orders from your ERP system before your company started to use Pepperi (order history)
To view these orders in the Pepperi app or admin dashboards you will need to load this order history or updated order information in CSV files (manually or automatically via your ERP integration to Pepperi).
You will need to export the information in the format specified below to 2 CSV files - one containing Order Header information and one containing line-item order information. Upload those files using FTP transfer or via the Pepperi Web Admin.
(There are 2 sample files attached all the way at the bottom of this article, you can download them to see an example.)
Make sure to use the file naming conventions explained below when uploading via FTP.
Order history can also be uploaded manually via the Settings -> ERP -> Log -> click Upload.
You will need to make sure that the fields you are loading are active in your mapping. Detailed instructions for both of these steps follow in this article.
After loading your file check the Pepperi Admin log file for any errors.
This article will walk you through all of the required steps:
- Mapping the relevant fields
- Creating the CSV Files
- Checking the Delimeter
- File naming convention
- Uploading the files
- Accessing the Error Log
- Add the status type of the uploaded orders to the Sales Dashboard Settings
Before you build your CSV files you need to provide the mapping name for the relevant fields.
Go to the Transaction Type Fields tab:
Settings -> Sales Activities -> Transaction Types
Select edit (pencil icon) for the Transaction Type you are loading in the order history CSV files (If you are not using multiple transaction types then you will have just one Transaction listed, most probably it is named "Sales Order" unless you changed it).
Go to the Fields tab and check all the categories to make sure the fields you are loading are activated. A field is active if it is black (not greyed-out).
To activate it click the "+" symbol and fill in the "Mapped name" field. This name is the title of the field that will be used by Pepperi when the field is imported. Make sure that the column headings in your CSV file are identical to the mapped names listed here.
Here is an example of a "Transaction Field" that is a required field in the line-item Order Information CSV file, that is NOT active - it's grey. To activate it click on the "+" symbol:
After clicking on the "+" symbol and activating the field, it turns black:
Make sure ALL the fields that you loaded as headings in the 2 CSV files are active. (Many of them may already be active as part of the regular Order data generated by Pepperi, but make sure!).
Look in the Transaction Fields, Catalog Fields, Account Fields sections to locate the fields that you need to make sure are activated.
Your changes are immediately saved so when you are done it is not necessary to click Save.
Creating the CSV Files (see file naming conventions below)
The first CSV file contains Order Header information
The Order Header information file must contain the following mandatory columns. There is a sample file (attached at the bottom of this article) that contains more fields you can load about the order, however they are not required and you can load a CSV file with these headings only:
ExternalID - This is the ID number given by your ERP to this order.
AccountExternalID - This is the Account ID number that the order is associated with
AgentExternalID - The ID of the Sales Rep associated with this Order. (for more on the Sales Rep ID field see: Manage Users)
CatalogExternalID - mandatory if you are using multiple catalogs. This is the name of the catalog the order was created from. (The catalog name that you entered when creating the catalog).
Total - the total amount of the order (legacy users do not need to enter this field, it is auto-calculated. Customers using the new API integration files must enter the order total, it will not be auto-calculated by the system. This allows you to add any charges to the total that were not itemized in the order lines, for example shipping, postage etc..)
Quantity - the total quantity of items ordered (legacy users do not need to enter this field, it is auto-calculated. Customers using the new API integration fiels must enter the total quantity, it will not be auto-calculated by the system.)
The additional fields are:
A number between 1 - 14
*See the list at the end of this article for Order Status meanings.
|Total amount of the order (do not use any currency symbol)||The total quantity of all the items in the order.||Date formatted in the date format of your culture setting||The delivery date of the order, formatted in the date format of your culture setting||
The name or type of transaction - if you are using the Multiple Transaction feature
(otherwise your default transaction will be used)
For more information on Transaction Types:
Mandatory only if you are using multiple catalogs - this is name of the catalog the order was created from. (The catalog name that you entered when creating the catalog).
For more info on the Multiple Catalogs feature: Multiple Catalogs
general remark about the order.
The second CSV file contains the line-item Order information - item codes, prices, etc..
The line-item information CSV file must contain the following mandatory 2 columns. There is a sample file (attached at the bottom of this article) that contains more fields you can load about the order status, however they are not required and you can load a CSV file with these 2 columns only:
TransactionExternalID - The ID number given by your back office to this order
ItemExternalID - The code of the item ordered (as it appears in your catalog)
The additional fields are:
|Quantity of item ordered||The price paid per unit (final after any discount)||Delivery date of the item||A percentage discount that may have been applied to the item|
Before loading the CSV files MAKE SURE TO CHECK THAT THE DELIMITER IS SET to match the delimeter in your CSV file otherwise the file will not upload correctly.
To do this:
Settings -> ERP Integration -> Configuration
The default delimeter is a semi-colon. Modify the delimeter to the one used in your CSV file and click Save.
* IF YOU ARE AUTOMATICALLY LOADING YOUR DATA VIA FTP: make sure that changing the delimiter will not interfere with files that are automatically being loaded. Confirm this with your system administrator.
File naming conventions
Files must be named as follows:
- Order Headers file: API_Transaction_<your transaction name> 2015-01-04_130415_Distributor_xxxxx.csv
- Line-item file : API_TransactionLine_<your transaction name>2016-01-04_130415_Distributor_xxxxx.csv
The default transaction name is "Sales Order", if you changed it, that will have to be reflected in this file name as well.
At the end of the filename add the distributor ID number and the date and time of the transaction in order to keep better track of the files in your log.
For example (5555555 used as an exmaple distributor Id number):
Where 5555555 is the distributor ID number. (How to find your distributor ID number).
Transfer the CSV files to the FTP directory for your integration. (Your Pepperi project manager will be able to provide you with the FTP access).
For testing - you can manually load the 2 Order History CSV files you created above:
Settings -> ERP Integration -> Log
Click the Upload button.
To load the Order header file select "API Transaction Sales Order"
To load the Order line file select "API TransactionLine Sales Order"
Accessing the Pepperi Admin Log file
To check if your File loaded successfully go to the Pepperi log files will indicate if any of the lines in the file/s failed: How to Access the Pepperi Admin log
*Order Status Code meanings:
1 - In Creation
2 - Submitted (setting this status will NOT trigger the sending out of emails that an order has just been submitted. This setting is used for loading historical data)
6 - Revised
9 - Awaiting Approval
12 - ERP
14 - Invoice
16 - In Planning
17 - Published
18 - In payment
19 - Paid
Add the status type of the uploaded orders to the Sales Dashboard Settings
Add the status type of the uploaded orders to the Sales dashboard configuration to ensure these orders appear in your dashboard.
Sales Dashboard Settings allows you to customize the Transaction Types and Statuses that will be included in the statistics displayed in the various Pepperi dashboards. The dashboards provide charts and top selling items based on the transaction types and statuses you select here.
Go to Settings -> Sales Activities -> Sales Dashboard Settings
If you would like the Transactions you loaded to be included in the dashboards select the relevant status and type.