Import orders (Transactions) to Pepperi in order to:
- Updated status or other fields in Pepperi orders exported to your ERP
- View invoices or other types of transactions (other than sales orders) from your ERP in Pepperi
- View orders from your ERP system before your company started to use Pepperi (order history)
To view these orders in the Pepperi app or 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 integration or via the Pepperi Studio back office.
(There are 2 sample files attached 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 Delimiter
- File naming convention
- Uploading the files
- Accessing the Error Log
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
If you are loading a new kind of Transaction Type that you don't have listed then you need to create it. Click Add and name the Transaction Type by the name of the data you are loading, for example: Invoice.
If you are loading updated information for an existing Transaction, such as the Sales Order transaction, then you don't need to create anything, continue below:
Select edit (pencil icon) for the Transaction Type you are loading in the order history CSV files.
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
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||
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 you loaded for this Transaction in the Order Header file
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 delimiter in your CSV file otherwise the file will not upload correctly.
To do this:
Settings -> ERP Integration -> Configuration
The default delimiter is a semi-colon. Modify the delimiter 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 file upload in order to keep better track of when you loaded 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