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: |
| ENUM | Dimensions | How the dimensions of the stock item are specified. Will be one of: |
| 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.