Skip to main content
Skip table of contents

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 TypeField NamePurpose
Primary KeyStockCode

A unique ID code for the stock item.

StringDescription

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 KeyStockGroupPrimary Stock Group.  Foreign key to STOCK_GROUPS
Foreign KeyStockGroup2Secondary Stock Group.  Foreign key to STOCK_GROUP2S
Foreign KeyStockPriceGroupStock Price Group (Controls discount pricing).  Foreign key to STOCK_PRICEGROUPS
Foreign KeyStock_ClassificationStock classification.  Foreign key to STOCK_CLASSIFICATIONS
Foreign Key

Sales_Gl_Code

Check Revenue account is set.  FOREIGN key to GLACCS
Foreign KeySales_GL_SubCodeCheck Revenue account is set.  FOREIGN key to GLSUBACCS
Foreign KeyPurch_GL_CodeCheck purchase account is set. FOREIGN key to GLACCS
Foreign KeyPurch_GL_SubCodeCheck purchase account is set. FOREIGN key to GLSUBACCS
Foreign KeyCOS_GL_CodeCheck Cost of Sale account is set. FOREIGN key to GLACCS
Foreign KeyCOS_GL_SubCodeCheck Cost of Sale account is set. FOREIGN key to GLSUBACCS
Foreign KeyBranchNoDefault Branch for item GL Posting.  Foreign key to BRANCHES. 
Foreign KeySalesTaxRateDefault tax rate on Sale of item.  If  -1 is Debtor default, otherwise is a Foreign Key to the TAX_RATES table,
Foreign KeyPurchTaxRateDefault tax rate on Sale of item.  If  -1 is Creditor default, otherwise is a Foreign Key to the TAX_RATES table,
Foreign KeyPackThe Unit/Pack type type.  Foreign Key to STOCK_UNITDEFINITION.
OPTIONALJob 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 KeyCostTypeForeign key to an optional Job Costing table.  May not be present in every database.  One of the few we can safely ignore.
JC Foreign KeyCostGroupForeign key to an optional Job Costing table.  May not be present in every database.  One of the few we can safely ignore.
OPTIONALStyle Color Size Values. - CommonOptional 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 KeyX_SizeIDForeign key to STOCK_SIZE.  Not mandatory and probably optional, but should be set if present.  The correct not set value is -1 
Opt Foreign KeyX_SizeColourIDForeign key to STOCK_COLOUR.  Not mandatory and probably optional, but should be set if present.  The correct not set value is  -1
ENUMSYSTEM Values must be DEFINEDENUM lists, only accept certain values. Check Schema for full explanation.
ENUMStatusThe 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.
ENUMCOGSMethodThe 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.

ENUMSerialNo_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

ENUMDimensions

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.
BOOLEANIsActiveIs the stock item active?  (If not, may not be able to sell, gets complicated depending on system settings).
BOOLEANHas_BNIs this batch tracked? (Batch tracking is not API supported, but IS a mandatory system field)
BOOLEANHas_ExpiryDoes 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)
BOOLEANIs_DiscountableCan you discount this item? 
BOOLEANRestricted_ItemIs 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.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.