Exchequer Software Plug-ins

Features:

---

Extracting Customer/ Supplier/Stock Discount Matrix Data from Exchequer Enterprise

If tag "CustCode" exists the in spreadsheet, then PostTrans will Import/Extract/Amend Customer/ Supplier Discount Matrix data in Exchequer Enterprise.  If it does not exist then it will import/Extract/Amend Stock Discount Matrix data in Exchequer Enterprise.

Extracting Customer/ Supplier Discount Matrix Data from Exchequer Enterprise

If tag "CustCode" exists the in spreadsheet, to extract Customer/ Supplier Discount Matrix data press button, on the PostTrans Toolbar, this will allow you to add criteria to the lookup:

Here we are going to get all customers with "SAM" in user field 1.  We then press [Clear and Lookup Values] or [Add Lookup Values] button to populate the spreadsheet with data Exchequer Enterprise Customer/Supplier records.

IMPORTANT : The [Add Lookup Values] will not detect potential duplicate records added to the spreadsheet.

Extracting Stock Discount Matrix Data from Exchequer Enterprise

If tag "CustCode" does not exists the in spreadsheet, to extract Stock Discount Matrix data press [Update Lookups], on the PostTrans Toolbar, this will allow you to add criteria to the lookup:

Here we are going to get all stock under the heading "CONTROLPANELS".  We then press [Clear and Lookup Values] or [Add Lookup Values] button to populate the spreadsheet with data Exchequer Enterprise Stock records.   

IMPORTANT : The [Add Lookup Values] will not detect potential duplicate records added to the spreadsheet.

Amending the Exchequer Enterprise Customer/ Supplier/Stock Discount Matrix Data

Having extracted data, the user can now simply edit the Enterprise data, before re-importing it back to Exchequer Enterprise.  

Importing new Exchequer Enterprise Customer/ Supplier/Stock Discount Matrix Data

Simply add new lines to the spreadsheet from row 3 onwards.  Then press [Post Transactions], on the PostTrans Toolbar,  to import Exchequer Enterprise data.  

Deleting Customer/ Supplier/Stock Discount Matrix Data

To delete a single matrix entry, simple enter "X" in "DeleteRecordFlg" column:

 

Customer/ Supplier/Stock Discount Matrix Required fields

To Import/Extract/Amend Exchequer Enterprise Customer/ Supplier/Stock Discount Matrix data, the following Tags/Fields must be specified listed in table below.

Cell A1 must equal DiscPosted

Type tag is used to detect end of spreadsheet.  PostTrans will stop Importing/Amending, from row 3 onwards, when this field is empty.

Restrictions

On the Pervaive version of Exchequer it is advised NOT to use PostTrans to delete Qty breaks.

Account/Qty breakes

Account/Qty breakes have various restrictions due to the functionality of the COM Toolkit:

• Account/Qty breakes cannot be deleted (Toolkit does not provide this functionality). In MS SQL we have by passed this problem.
• Account/Qty breakes cannot be updated (Bug ABSEXCH-14208 and ABSEXCH-14207)
• Effective dates are not supported.

In summary, PostTrans cannot be used to update Account/Qty breakes so as a work around:

1. Export current customer discounts using Post Trans
2. Amend export to be what you really want
3. Use PostTrans to delete entries (MS SQL version only)
4. Use PostTrans to import discounts back onto the customer

Exchequer Customer/ Supplier/Stock Discount Matrix fields available for update:

---

The following Tags are used to identify the corresponding fields in Exchequer Enterprise.  Use the [Design] button (or Ctrl+Shift+D), on the PostTrans Toolbar, to access these tags.   Click here for more Information on Designing templates.

Tag Name	Data Type Length	Description
Required (Prof version):
DiscPosted	Yes/No	Returns 'POSTED' if Trans line has been posted and will be ignored if 'POSTED' (required)
ErrorDesc	Text	Description of error posted back if record unsuccessfully posted
DeleteRecordFlg	DeleteFlag	Delete record if =X
CustCode	Text 6	Customer Code if Customer Matrix  If present then Customer/Supplier discount, else Stock Qty
StockCode	Text 16	Stock Code if Stock Matrix
Type	Text 1	Discount Type  B = Band Price (Req:SalesBand),P = Special Price (Req:SPCurrency and SPrice),M = Margin % (Req:DiscMar),U = Markup % (Req:DiscMar),Q = Quantity Break (Req:QtyFr and QtyTo)
SalesBand	Text 1	Sales Band A-H
Currency	Long	Currency 1-89 (defaults=1)
Price	Double	Price if a Special Price Record
DiscPercent	Double	Discount Percent if Discount, Margin or Markup
DiscAmount	Double	Discount Amount if Discount
DiscMargin	Double	Discount Percent if Margin or Markup
Stock Qty Discount:
QtyFrom	Double	Qty FROM if Stock Qty break, see Special Notes on Importing Customer Qty Breaks
QtyTo	Double	Qty TO if Stock Qty break, see Special Notes on Importing Customer Qty Breaks
Effective dates:
EffectiveDateStart	Date	Start date of price
EffectiveDateEnd	Date	End date of price
Additional Lookups:
StockDescription	Text 35	Stock description from Stock record (Read Only)
AccountName	Text 35	Account Name  (Read Only)

Special Notes on Importing Exchequer Customer Qty Breaks:

---

Customer Qty breaks can easily be imported using PostTrans.  

For example:

EffectiveDateStart, EffectiveDateEnd can also be used, but not shown in above example.

IMPORTANT: 

The lines MUST BE grouped by CustCode and StockCode before importing. Or in other words the sheet should be ordered by CustCode, then StockCode.

The QtyFrom must be more than 0, even though Enterprise will except 0

If you change the QtyTo or QtyFrom breaks figures, then you need to delete the entire Qty break first to remove old entries.  Then re import data.

The effective dates columns should be formatted as TEXT, as with all other date cells when using PostTrans.  This is because on certain conditions Excel will use American date format if the date entered is before the 13th, and English on and after the 13th.  Enter date as dd/mm/yy.