Postcode Programming API  PostTrans XL Visual Importer: Import Exchequer Stock Records


This PostTrans spreadsheet allows the user to easily Import/Extract/Amend Stock data in Exchequer Enterprise.   It uses a series of tags, placed in row 1, to identify the data to be Imported/Extracted/Amended.   These tags can be easily added to a spreadsheet using the  button, on the PostTrans Toolbar.  The data is read from row 3 onwards, which allows you to put user descriptions in row 2.  More Information on Design and Tags.

  Demonstration Videos:


             Show me a demonstration video

The "Customers and Stock" Example shows this in action.  more information on PostTrans examples


Import Exchequer Stock Records Features:


Extracting Stock Data from Exchequer Enterprise

To extract Stock data press  button, on the PostTrans Toolbar, this will allow you to add criteria to the lookup:

Exchequer Enterprise get Stock

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.

Exchequer Enterprise Stock Import

Amending the Exchequer Enterprise Stock  Data

Having extracted data, the user can now simply edit the Enterprise data, before re-importing it back to Exchequer Enterprise.   Note only changed records will be updated in Enterprise.

Importing new Exchequer Enterprise Stock  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.  

Stock  Required fields

To Import/Extract/Amend Exchequer Enterprise Stock Data, the following Tags/Fields must be specified:

STKPosted, ErrorDesc, StockCode, ParentStockCode, Desc1, StockType, StockValType, SalesGLCode, CostOfSalesGLCode, WriteOffStockGLCode,StockValueGLCode, WIPGLCode

Cell A1 must equal STKPosted

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

Restrictions

WOPProdTimeDays, WOPProdTimeHours, WOPProdTimeMins do not update at present

StockType cannot be changed, once imported.  It can only be changed to X=Discontinued.


Import Exchequer Stock 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.

Field Name

Data Type Length

Description

Required:

 

 

STKPosted

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  (required)

StockCode

Text 16

Stock identifier code (required) used to identify stock in Exchequer Enterprise

ParentStockCode

Text 16

Code of parent on tree (required) in Exchequer Enterprise

Desc1

Text 35

Stock description (required)

StockType

Text 1

G =Group,P =Product,D =Description,M =Bill of Materials,X =Discontinued (required)

StockType cannot be changed, once imported, apart from setting to Discontinued

StockValType

Text 1

R =Serial/Batch,V=Serial/Batch Avg,C =Last Cost,L =LIFO,F =FIFO,S =Standard,A =Average (required)

Required GL:

 

 

SalesGLCode

Long Integer

Sales Nominal code (required)

CostOfSalesGLCode

Long Integer

Cost of Sales Nominal code (required)

WriteOffStockGLCode

Long Integer

P&L Closing Stock Nominal code (Write off) (required)

StockValueGLCode

Long Integer

Balance Sheet Stock Value Nominal code (required)

FinishedGoodsGLCode

Long Integer

Work In Progress Nominal code (required)

Description:

 

 

AltStockCode

Text 16

Optional code for stock searching

Desc2

Text 35

Stock description 2

Desc3

Text 35

Stock description 3

Desc4

Text 35

Stock description 4

Desc5

Text 35

Stock description 5

Desc6

Text 35

Stock description 6

Price:

 

 

PriceByStockUnit

Integer

Price by Stock Unit 0, Sales Unit 1, Split pack 2

CurrencyBandA

Byte

Currency for Price Band, 0-29,1=Sterling

UnitPriceA

Double

Price for Price Band

CurrencyBandB

Byte

Currency for Price Band, 0-29,1=Sterling

UnitPriceB

Double

Price for Price Band

CurrencyBandC

Byte

Currency for Price Band, 0-29,1=Sterling

UnitPriceC

Double

Price for Price Band

CurrencyBandD

Byte

Currency for Price Band, 0-29,1=Sterling

UnitPriceD

Double

Price for Price Band

CurrencyBandE

Byte

Currency for Price Band, 0-29,1=Sterling

UnitPriceE

Double

Price for Price Band

CurrencyBandF

Byte

Currency for Price Band, 0-29,1=Sterling

UnitPriceF

Double

Price for Price Band

CurrencyBandG

Byte

Currency for Price Band, 0-29,1=Sterling

UnitPriceG

Double

Price for Price Band

CurrencyBandH

Byte

Currency for Price Band, 0-29,1=Sterling

UnitPriceH

Double

Price for Price Band

Cost:

 

 

CostPriceCurrency

Byte

0-29,1=Sterling

LastCostPrice

Double

The last cost price of Stock Item

PORCurrency

Byte

POR Reorder Currency, 0-29,1=Sterling

PORCostPrice

Double

POR Reorder Price

ReOrder Defaults:

 

 

PrefSupplierCode

Text 6

Master supplier Ref.

ReOderCostCentre

Text 3

 

ReOrderDepartment

Text 3

 

Stock Qty:

 

 

UsesMultiBinsFlg

Yes/No

Use multi bins

QtyMin

Double

 

QtyMax

Double

 

ShowAsSalesPacksFlg

Yes/No

Show stock levels as sales units (Packs)

Defaults:

 

 

Department

Text 3

 

CostCentre

Text 3

 

VATCode

Text 1

S,Z,E,1,2,3,4,5,6,7,8,9,A,D,T,X

IS,IZ,... for inclusive VAT

DefaultLineType

Integer

Default Line type 0-5 (Normal,Labaour…)

JobAnalysisCode

Text 10

Default Job Analysis code

Stock Qty Multiplier:

 

 

DescStockingUnitQty

Text 10

Descriptive Stocking Unit Qty

DescSellingQty

Text 10

Descriptive Selling Unit Qty

DescPurchaseQty

Text 10

Descriptive Purchase Unit Qty

SellQtyMul

Double

Number of items in case

PurchaseQtyMul

Double

Number of items in case

IntraStat Settings:

 

 

IntraStatVATCommodityCode

Text 10

Intrastat value fill in if turned on

IntraStatUnitDesc

Text 10

Intrastat value fill in if turned on

IntraStatStockUnit

Double

Intrastat value fill in if turned on

IntraStatSalesWeight

Double

Intrastat value fill in if turned on

IntraStatPurchaseWeight

Double

Intrastat value fill in if turned on

IntraStatCountry

Text 5

Intrastat value fill in if turned on

Works Orders:

 

 

WOPProdTimeDays

Integer

Time taken to produce in days

WOPProdTimeHours

Integer

Time taken to produce in hours

WOPProdTimeMins

Integer

Time taken to produce in mins

WOPROLeadTime 

Integer

Lead time in days

WOPAutoCalcProdTime

Yes/No

Auto Calc Prod Time

WOPMinEcQty 

Integer

Min Economic build qty

WOPIssuedWIPGL

Integer

WIP GL code to use on WOP

User Fields:

 

 

User1

Text 20

User defined field 1

User2

Text 20

User defined field 2

User3-4

Text 30

User defined field 3-4

User5-10

Text 30

User defined field 5-10 (V6.9+)

Web:

 

 

WebLiveCat

Text 20

 

WebPrevCat

Text 20

 

WebInclude

Yes/No

 

ImageFile

Text 30

Image file for web image

Location:

 

 

LocationCode

Text 3

Relates to Location record

BinLocation

Text 8

Location of Bin if multi-Location turned on

BarCode

Text 20

 

BOM:

 

 

ShowBOMKitOnSales

Yes/No

Include BOM items on Sales Transactions

ShowBOMKitOnPurchases

Yes/No

Include BOM items on Purchase Transactions

Read Only:

 

 

FolioNumber

Long Integer

Internal ID (Read only)

LastUsed

Date

Date last used (Read only)

Returns:

   

PurchaseReturnGL 

Long Integer

Return GL

SalesReturnGL 

Long Integer

Return GL

RestockCharge 

Double

Stock Recharge can be a percent

ManufactureWarranty 

Long Integer

Warranty Period

SalesWarranty 

Long Integer

Warranty Period

ManufactureWarrantyPeriod 

Text

Day,Week,Month,Year

SalesWarrantyPeriod 

Text

Day,Week,Month,Year

Notes:

 

 

GNotes

Memo

General Notes (Deletes any existing Notes in General and Dated Notes)

chr$(10) or "<br>" gives new line