Schema Information - Stock_Items for Developers

This article is designed to give a primer on different Stock Item fields & their definitions for software developers (particularly Exo API Developers).

This is a partial reference, for use with the Database Schema from the Education Centre.

Case for Field Names with API

While the Field Names are presented in camel case for ease of reading, the API requires that these are sent with the field names in UPPERCASE.

Field Type	Field Name	Purpose
Primary Key	StockCode	A unique ID code for the stock item.
String	Description	The stock item's description.
Foreign Key	Pseudo-Foreign Keys Not defined at the Database level, Defined in the application layer.	While the SQL Database doesn't hold Foreign Key relationships for the following values, they are nonetheless effectively foreign keys and must have valid values. -1 is the standard value for "Unset", but in some cases Default should be 0. Strongly recommend all these values are set to a valid value from their associated table.
Foreign Key	StockGroup	Primary Stock Group. Foreign key to STOCK_GROUPS
Foreign Key	StockGroup2	Secondary Stock Group. Foreign key to STOCK_GROUP2S
Foreign Key	StockPriceGroup	Stock Price Group (Controls discount pricing). Foreign key to STOCK_PRICEGROUPS
Foreign Key	Stock_Classification	Stock classification. Foreign key to STOCK_CLASSIFICATIONS
Foreign Key	Sales_Gl_Code	Check Revenue account is set. FOREIGN key to GLACCS
Foreign Key	Sales_GL_SubCode	Check Revenue account is set. FOREIGN key to GLSUBACCS
Foreign Key	Purch_GL_Code	Check purchase account is set. FOREIGN key to GLACCS
Foreign Key	Purch_GL_SubCode	Check purchase account is set. FOREIGN key to GLSUBACCS
Foreign Key	COS_GL_Code	Check Cost of Sale account is set. FOREIGN key to GLACCS
Foreign Key	COS_GL_SubCode	Check Cost of Sale account is set. FOREIGN key to GLSUBACCS
Foreign Key	BranchNo	Default Branch for item GL Posting. Foreign key to BRANCHES.
Foreign Key	SalesTaxRate	Default tax rate on Sale of item. If -1 is Debtor default, otherwise is a Foreign Key to the TAX_RATES table,
Foreign Key	PurchTaxRate	Default tax rate on Sale of item. If -1 is Creditor default, otherwise is a Foreign Key to the TAX_RATES table,
Foreign Key	Pack	The Unit/Pack type type. Foreign Key to STOCK_UNITDEFINITION.
OPTIONAL	Job Costing Module - Optional Costing Feature	Optional FOREIGN key fields. these are Optional fields for JOB COSTING, and are strictly Optional unless exposed to the UI as extra fields.
JC Foreign Key	CostType	Foreign key to an optional Job Costing table. May not be present in every database. One of the few we can safely ignore.
JC Foreign Key	CostGroup	Foreign key to an optional Job Costing table. May not be present in every database. One of the few we can safely ignore.
OPTIONAL	Style Color Size Values. - Common	Optional FOREIGN key fields. these are Optional fields for use with the Style Colour Size Matrix & item creation and are strictly Optional unless exposed to the UI as extra fields
Opt Foreign Key	X_SizeID	Foreign key to STOCK_SIZE. Not mandatory and probably optional, but should be set if present. The correct not set value is -1
Opt Foreign Key	X_SizeColourID	Foreign key to STOCK_COLOUR. Not mandatory and probably optional, but should be set if present. The correct not set value is -1
ENUM	SYSTEM Values must be DEFINED	ENUM lists, only accept certain values. Check Schema for full explanation.
ENUM	Status	The valid values are 'S' for Stocked or 'L' for Lookup. A lookup item is a non-stocked item, like Labour, or something that is so fast-moving/low value you don't track individual stock levels.
ENUM	COGSMethod	The method of costing applied for lookup stock items. Required for Lookup items.
ENUM	NumDecimals	The number of decimal places that can be entered for the stock item's quantity. Must be set to a valid Integer.
ENUM	SerialNo_Type	How the stock item is serialised. Will be one of: 0 = Not Serialised 1 = Fully Tracked (In & Internal & Out) 2 = Creditor & Internal (In & Internal) 3 = Debtor Only (Out only) 4 = Creditor & Debtor (In & Out Only) 5 = Creditor Only (In Only) 6 = Internal & Debtor (Internal & Out only) 7 = Internal Only
ENUM	Dimensions	How the dimensions of the stock item are specified. Will be one of: 0=None 1=Length 2=Square 3=Cubic
BOOLEAN	'Y'/'N' values for checkboxes.	Below here BOOLEAN Fields stored as CHAR(1), valid values for API are 'Y' and 'N'. Some might manage to work without being set properly, but it is recommended to explicitly set values.
BOOLEAN	IsActive	Is the stock item active? (If not, may not be able to sell, gets complicated depending on system settings).
BOOLEAN	Has_BN	Is this batch tracked? (Batch tracking is not API supported, but IS a mandatory system field)
BOOLEAN	Has_Expiry	Does this item have an expiry date? (Expiry is part of Batch Tracking & Serial tracking. It is not API supported, but IS a mandatory system field)
BOOLEAN	Is_Discountable	Can you discount this item?
BOOLEAN	Restricted_Item	Is this item on a Restricted Stock list? (Restricted items are available as a custom feature, either they won't be used, or each business is likely to have had custom work done to build out the feature exactly as they require it.)

If you need more help with this topic, contact your MYOB Exo Business partner. If MYOB support you directly, email businesssupport@myob.com.