ERP Payables: Payable Price Contracts

Agreed Supplier pricing can be loaded as individual pricing contracts or imported in bulk to ensuring that the latest pricing is used for Purchase Orders.

Introduction

Create a single Payable Contract

Workflow Outline to import Payable Contracts 

Check the Stock items in your Lentune system

Export File Options

Export Existing Payable Contracts for a Supplier

Export a blank template file

Edit the spreadsheet to be imported

Import Payable Contracts for a Supplier

Payable Contracts with multiple Price Breaks for a Stock Item


Introduction

Agreed Stock pricing data provided by your Suppliers can be loaded into Lentune as Payable contracts. This allows you to use the agreed pricing when creating Purchase Orders.

A Payable Contract creates a link between a Supplier, a Stock item and the Price for this Stock item. If there are different prices for different quantities of a Stock item, you would have a separate Payable Contract for each Minimum Quantity.

A single Payable Contract can be added manually, or multiple Payable Contracts for a Supplier can be imported from a spreadsheet.

 Return to Top of Page


Create a single Payable Contract

If you wish to create a Payable Contract manually, you can do this in the Edit Payable Contract screen.

  • Go to Payables > Payable Contracts.
  • Click New Payable contract.

In the Edit Payable Contract screen:

In the Contract details section:

  • Select the Supplier
  • Select the Contract type. This is usually Standard Buy.
  • Select the Stock Code.

In the Pricing and agreed override rebate section:

  • Enter the Price (before discounts, excluding tax.)
  • Enter the Discount percent (if applicable).
  • Complete any other fields as applicable. If this Payable Contract applies for any other specific categories, e.g. Branch, Department, Project, you can add these here.
  • Click Confirm.

This will create one Payable contract for that Supplier for that Stock item.

 Return to Top of Page


Workflow outline to import Payable Contracts

  1. Check that the Stock file is up to date, and contains a Stock item for each Payable Contract that you wish to import.
  2. Export either a blank template file, or the existing Payable contracts for the Supplier. This gives you a spreadsheet with the correct format to re-import.
  3. Edit the spreadsheet file to be imported, to add the records from your Supplier in the required columns. Refer below to the spreadsheet to be imported for more explanation about the columns.
    1. You can copy and paste data from the file provided by your Supplier into the spreadsheet you have exported.
    2. Check that all Payable Contracts to import have either:
      1. a Lentune Stock code in Column L, or:
      2. a Supplier Stock Code in Column M,
        and that at least one of these codes exist in your database.
  4. Import the Payable Contract file that you have edited.

   Return to Top of Page



Check the Stock items in your Lentune system

You will need to ascertain that the Payable Contracts to import from the Supplier already have a Stock Code in your database.

To check an individual Stock item:

  • Go to Stock > Stock details.
  • Select the Supplier (payable account) required.
  • Click Search.

Stock items that exist in your system for the selected Supplier will be displayed.

 

To export file of Stock items for a Supplier:

  • Go to Stock > Export.
  • Select the Supplier.
  • Enter a File name for export.
  • Click Export Stock to file.

 

This will create an .xlxs file of all Stock items for the selected Supplier.

 Return to Top of Page


Export File Options

You can export a file of existing Payable Contracts for a specific Supplier, or export a blank template containing the required column headings.

A file of existing Payable Contracts can be a helpful guide when you are populating the correct columns in your spreadsheet with the new data.

Where you want to retain existing Payable Contracts to be used until the new contracts start, you can export the existing Payable Contracts, and edit the spreadsheet to re-import:-

    • to add the Expiry date to existing records,
    • then add new records with an Effective (start) date.

Refer to the Note: Which Payable Contract is used below for more information about this.

If there are no existing Payable Contracts in your database, a blank template file will show you the column headings that are required in the spreadsheet to import Payable Contracts.

  Return to Top of Page


Export Existing Payable Contracts for a Supplier

To export a file of existing Payable Contracts for a specific Supplier:

  • Go to Payables > Payable Contracts.
  • Click Import and/or Export contracts.

 

In the Import / Export Payable Contract Details screen:

  • Go to the Export section in the lower part of the screen.
  • Select the Supplier (payable account).
  • Click Export payable contracts to file.

This will create an .xlxs file containing the existing Payable contracts for this Supplier, and that provides the columns you will need to update and re-import.

 

  Return to Top of Page


Export a blank template file

If there are no existing Payable Contracts in your database, a blank template file will show you the column headings that are required to import Payable Contracts.

To export a blank file containing only column headings:

  • Go to Payables > Payable Contracts.
  • Click Import and/or Export contracts.

 

In the Import / Export Payable Contract Details screen:

  • Go to the Import section in the top section of the screen.
  • Click Get template.

 

This will create a blank .xlxs file that provides the columns headings you will need in your spreadsheet to import the new Payable Contracts.

  Return to Top of Page


Edit the spreadsheet to be imported

The columns that you will need to populate in the spreadsheet to import are:

 

COLUMN Column Name

Data

 
Column A TableReference:  PayableContract  
Column B SupplierCode Your Lentune Supplier Code  
Column C ContractType StandardBuy or Specific  
Column D SupplierContractRef If provided by Supplier Note 1
Column L StockCode Your Lentune Stock Code Note 2
Column M SupplierStockCode As provided by Supplier

Note 2

Column R Price Price as provided by Supplier Note 3
Column S DiscountPercent Discount as provided by Supplier Note 3
Column T MinimumOrderQuantity Can be 0 Note 4
Column U EffectiveDate If provided by Supplier Note 5
Column V ExpiryDate If provided by Supplier Note 5

 

Other columns may be used if required in your system, e.g. Project Code, Branch Code, Department Code, etc.

Column Notes:

Note 1: Column D. SupplierContractRef. If the Supplier provides a Supplier Contract Ref, this is noted at the foot of a Purchase Order so you can see that there may be some special pricing for that item. It could be useful of you have a specific Payable Contract for a specific customer or project.

Note 2: Columns L & M: StockCode & SupplierStockCode.

The import process matches the Lentune Stock code, Column L from the spreadsheet, to the Stock item in the database, and if found will import the record.

If the Lentune Stock Code is not populated in Column L of the spreadsheet, the process will search the database for a Stock Item that has a Supplier's Stock code that matches the Supplier Stock code in Column M of the spreadsheet, and if found, will import the record.

NB: If neither the Lentune Stock Code nor Supplier Stock Code can be matched to a Stock item in the database, the Payable Contract will not be imported.

Note 3: Columns R & S: Price & Discount %: The supplier may provide only a Price. Or they might provide a Price and Discount%. If the latter is the case, you can import these separately, or calculate the net price and import that as the Price.
Alternatively, the Supplier may provide only the Net price which you would place in Column R and leave Column S blank.

Note 4: Column T: Minimum Order Quantity.

Where your Supplier has separate price break levels for different quantities of an item, you would have separate lines creating a Payable Contract for each price break. In that case, the Minimum Order Quantity field would reflect the order quantity for the specific price level. 

TIP: For an alternative method to manage multiple price breaks for a Stock item, refer below.

Note 5: Column U: Effective Date, Column V: Expiry Date

These columns are not mandatory, but should definitely be populated if known. Where your Supplier provides a price file that is due to start or expire on specific future dates, these columns can then be copied to your spreadsheet in these columns.

By populating the fields for Effective Date in your import file and using Expiry Date in your existing Payable Contracts, you are able to import new Payable contracts in advance, and avoid having to import all files at the date that the new contracts will apply.

If the existing Payable contracts have an Expiry date, then they will cease to be used at that date.

NOTE: Which Payable Contract is used.

If there are multiple valid Payable Contracts for the same Stock Code, the lowest price is always used. Therefore, if you have an existing Payable Contract with a price of $10 and no Expiry Date, and a new Payable Contract for the same Stock Code with a price of $12, with or without an Effective date, then the lower priced Payable Contract of $10 will be used. Once the Effective date of the new Payable Contract is reached, that means that this Payable Contract is available, but it will be surpassed by the lower priced Payable Contract that does not have an Expiry Date.

To avoid this situation, you can export existing Payable Contracts and add an Expiry Date to all records, then add the new records with the Effective Date to the spreadsheet to import. After the Effective Date for the new Payable Contracts has arrived, you can then export all Payable Contracts for the Supplier, remove the records where the Expiry Date has passed, and re-import the spreadsheet, selecting Delete Existing Contracts.

Alternatively, where the new Payable Contracts are to begin immediately, you can Delete Existing Contracts when you perform the import process.

Whichever option you use, it is helpful to clear out old Payable Contracts that are no longer in use.

    Return to Top of Page


Import Payable Contracts for a Supplier

Valid import file formats are.xls, .xlxs and .csv.

We strongly advise that best practice is to export existing Payable Contracts first, then edit the spreadsheet file to import, thus ensuring that good pricing is maintained in your database.

 

To import a file of multiple Payable Contracts for a specific Supplier:

  • Go to Payables > Payable Contracts.
  • Click Import and/or Export contracts.

In the Import / Export Payable Contract Details screen:

  • Go to the Import section in the top part of the screen.
  • Select the Supplier (payable account).
  • Drag your file to the box or browse to locate it.
  • Delete existing contracts
    • Select NO if your contracts have an Effective Date in the future or when you have not yet reviewed your existing Payable Contracts. This allows you to retain existing Payable contracts and continue to use them until the future Effective date, or until you are able to review them and remove them. You would also select NO when you have exported existing Payable Contracts, edited them and added the new records from your Supplier to the spreadsheet.

    • Select YES if you have existing Payable Contracts that you wish to overwrite. This could be where you have exported and reviewed existing Payable Contracts for that Supplier, and you want to clear old records. The new Payable Contracts will then be in use immediately. This helps to clear out old files that you no longer require. 

NOTE:

When importing your Payable Contract file, it is important to take care with the import field Delete existing contracts.  

If you have an Effective Date in the future for the Payable Contracts to be imported, DO NOT select Delete existing contracts, as the existing contracts will still need to be used until the Effective date for the new Payable Contracts is reached.

 

By populating the fields for Effective Date and Expiry Date in your import file, you are able to import new Payable contracts in advance, and avoid having to import all files at the date that the new contracts will apply.

If the existing Payable contracts have an Expiry date, then they will cease to be used at that date.

  • Update standard cost price: We recommend this would usually be No.
  • Click Import payable contracts from file.

If any errors are found in the file during the import process, these will be listed in the Import error log details section of the screen.

If you need assistance, please contact your support team at support@lentune.com.

  Return to Top of Page


Payable Contracts with multiple Price Breaks for a Stock Item

When you import Payable Contracts, you may have multiple price breaks for a Stock item, and therefore you will have separate Payable Contracts for each price break.

You can import these by adding the different price breaks to the stock item line in your Payable Contract import spreadsheet.

Refer to ERP Payables: Importing Price Contracts with multiple Price Breaks for details on an alternative method to import multiple price reaks for a Stock Item 


Was this helpful? If you require further assistance, or would like to contact us about this article, please email us at support@lentune.com.

Thank you. Your feedback is appreciated.

Last updated: 20 June 2022