BOS Enterprise Database
Before you can query a database, it is important that you know and understand the tables as well as the relations between the tables in a database.
The following table outlines how the Transactional Tables in the QuickEasy database are linked.
BOSEnterprise Tables
| TABLE NAME | Primary Key | DESCRIPTION |
| ACCOUNTCATS | ACCOUNTCATID | Stores the Account Categories |
| ACCOUNTS | ACCOUNTID | Stores the Accounts |
| ACCREPORTS | ACCREPORTID | Stores Accounting Reporting as per Entity |
| ACCREPORTITEMS | ACCREPORTITEMID | Stores the Setup as per Accounting Report. List the Accounts used and defines how it affects the Income Statement or Balance sheet |
| AGEING | AGEID | Stores the Values of the Age Analysis for both the Customers as well as Suppliers |
| ALERTS | ALERTID | Stores the Setups for the Transaction Alerts. |
| APPROVALS | APPROVALID | Stores the setups for the Transactions approvals |
| ASSETS | ASSETID | List of the Assets set company |
| AUDITS | AUDITID | Stores Record of changes, Updates and Deletes of Transactions as well as setups. |
| BALSHT | BALSHTID | Information Displayed on the Balance Sheet |
| BANK | BANKID | Stores Transactions from the Bank Import from ofx files. |
| BATCHUPDATES | BATCHUPDATEID | Stores the Setup of the Actions setup on reports. |
| CALENDER_EVENTS | EVENTID | Stores Events added to the Calendar. |
| CASHUP | CASHUPID | Stores the Cashups done in the POS |
| CASHUPITEMS | CASHUPITEMID | Information of the Cashup Money counted. |
| CASHUPLINKUSERS | CASHUPLINKID | Stores the Links between the Users and the Cashups done. |
| CASHUPNOTESCOINS | CASHUPNOTESCOINSID | Record of counted notes and coins on set Cashup. |
| CATEGORIES | CATEGORYID | Job Categories |
| CCENTRE | CCENTREID | Cost Centres in the Database, |
| CCCOSTS | CCCOSTID | Stores Costs Allocated to the Cost Centre |
| CONSOLE | CONSOLEID | Dashboard Indicators |
| COSTITEMS | COSTITEMID | Stores Items that reflect the Costs around a transactions/Transaction item |
| CPYMAIN | CPYMAINID | Information of the Licensed Company |
| CPYSTAFF | STAFFID | Licensed Company Staff |
| CREDITTERMS | CREDITTERMID | Stores the Credit Terms |
| CURRENCIES | CURRENCYID | Different Currencies and a Conversion rate to the Home currency. |
| CUSADDRESS | ADDRESSID | Customer Delivery Addresses |
| CUSCAT | CUSCATID | Customer Categories |
| CUSCONTACTS | CUSCONTACTID | Customer Contacts |
| CUSMAIN | CUSID | Customer Information |
| DSAUDITS | DSAUDITID | Stores Records of all things Exported/Imported Using Datashift |
| DSMAIN | DSMAINID | Data shift export/ Import files |
| DSTABLES | DSTABLEID | Tables used in the Export/ import file |
| DSFIELDS | DSFIELDID | Fields that will be imported/Exported from the selected table |
| EMAILS | EMAILID | Stores Email (sent and in the outbox) |
| EMAILS_ATTACHMENTS | ATTACHMENTID | Stores the Email Attachments |
| EMAIL_LIST | EMAIL_LIST_ID | Stores Email group for Mailshots |
| EMAIL_LIST_ITEMS | LIST_ITEM_ID | Stores the Contacts in the Mail group |
| ENTITIES | ENTITYID | This stores the Different Business Entities in the Database. |
| ENTITYLINKS | ENTITYLINKID | Links to the Entities (include Staff, Transaction Types, Customers/Suppliers) |
| ENTRYTYPES | ENTRYTYPEID | Stores the Transaction Types Setups. |
| FIELDSPECS | FIELDSPECID | Userfields that are setup in the DB. |
| FIELDVALUES | FIELDVALUEID | The information stored in these user fields. |
| HOLIDAYS | HOLIDAYID | Stores Holidays. |
| IMAGES | IMAGEID | Images saved in the DB |
| INCSTAT | INCSTATID | Information that displays on the Income Statement. |
| INFO_TOPICS | TOPICID | Stores the articles in the Knowledge base. |
| LANDINGCOSTS | LANDINGCOSTID | This Table is where the Landing Costs of set item is kept. |
| LEAVELOG | LEAVELOGID | This is where the Staff leave information is kept |
| LEAVERULES | LEAVERULEID | This is where the rules for the Leave Types are kept. |
| LEAVETYPES | LEAVETYPEID | Information of the Type of leave days are kept. eg. Sick Leave, Family responsibilities. |
| LEDGER | TRANSID | This is where the Transactional header information are stored. |
| LEDGERALLOCATONS | LEDGERALLOCID | This is where all the transactional allocations are kept. |
| LOOKUPS | LOOKUPID | This is where the fixed look up list in QE are Kept. |
| MATACC | MATACCID | Stores the Material Accounting information are setup. Location where we set Stock to affect accounting. |
| MATCAT | MATCATID | Material Categories are stored here. |
| MATCOST | MATCOSTID | Where the selling price setup of materials are stored. |
| MATPRICE | MATPRICEID | Where the Cost price setups of materials are stored. |
| MATTREE | MATTREEID | Material Bill of materials are kept here. |
| MATUNIT | MATUNITID | All items in the DB are kept here. |
| MENUGROUPS | MENUGROUPID | The Menu groups are stored here |
| MENUITEMS | MENUITEMID | Menu Items linked to selected Groups are stored here |
| MENULINKS | MENULINKID | Menu Groups assigned to User Roles are stored here. |
| MODULESCOPY | MODULECOPYID | This is where the Copy Rules are stored. |
| MODULESCOSTING | MODULESCOSTINGID | This is where the Costing Rules are kept. |
| MODULESFIN | MODULESFINID | Trans Type links for Financial reports are stored here. |
| MODULESROLES | MODULESROLEID | User Roles are set up here |
| MODULESSETTINGS | MODULESSETTINGID | Trans Type setups linked to User Roles are stored here. |
| MODULESUSERROLES | MODULESUSERROLEID | User Roles assigned to users are kept here. |
| NOTESCOINS | NOTESCOINSID | Denominations of home currency is stored here. |
| OUTQTY | OUTQTYID | Information on outstanding Qty's are kept here. |
| PARAMGROUPS | PARAMGROUPID | Estimate types are stored here. |
| PARAMS | PARAMID | Estimate Type Setups are stored here. |
| POSITIONS | POSITIONID | Staff Positions are stored here. |
| POSPRINT | POSPRINTID | * |
| PRESSCYLINDERS | PRESSCYLINDERID | Flexo Cylinder information are kept here |
| PRESSDIES | PRESSDIEID | Flexo Die information are kept here. |
| PRICEHISTORY | HISTORYID | Price Update information are kept here. |
| PRICELISTS | PRICELISTID | Pricelist are stored |
| PRICELINKS | PRICELINKID | Customers linking to Pricelist setups are kept here. |
| PRICES | PRICEID | Pricelist Prices are stored |
| PRICEUPDATES | PRICEUPDATEID | Price Update Setups are stored here. |
| PROCCAT | PROCCATID | Service Categories are stored here. |
| PROCESS | PROCESSID | Services are stored here. |
| PROCMAT | PROCMATID | Materials linked to Services setups are kept here |
| PRODCODES | PRODCODEID | Production codes are stored. |
| PRODTYPES | PRODTYPEID | Production Types are Stored |
| QUOTEBROKE | QUOTEBROKE | Estimate Outsourced Services are stored here. |
| QUOTECOMPS | QUOTECOMPID | Transaction Component Estimating setups are stored here. |
| QUOTEDESC | QUOTEDESCID | Estimate Descriptions |
| QUOTEFIN | QUOTEFINID | Estimate Services are stored here |
| QUOTEOUTSOURCE | QUOTEOUTID | Estimate Bill of materials are stored here. |
| QUOTEPARAMS | QUOTEPARAMID | Estimate Setups are stored here. |
| QUOTEQTY | QUOTEQTYID | Estimate Qty's and Prices |
| QUOTESUB | QUOTESUBID | Estimate Outsourced Prices are kept here |
| QUOTESUM | QUOTESUMID | Estimate summary are stored here. |
| REMIND | REMINDID | Reminders |
| REPLICATEMAIN | REPLICATEMAINID | Replication selections are kept here |
| REPLICATEITEMS | REPLICATEITEMID | The Replication setups are stored here. |
| REPLICATELOG | REPLICATELOGID | A log of all the Replication actions that had been done are kept here |
| REPORTS | REPORTID | Where All reports and template setups are kept. |
| REPORT_FIELDS | FIELDID | Where the Field setup on SQL Reports are kept. |
| SETTINGS | SETTINGID | All settings are stored |
| SOSTAGES | SOSTAGEID | Items populating the Planning Board are stored here |
| STAGES | STAGEID | Production Stages. |
| STAGESTATUS | STAGESTATUSID | Production Stage Statuses. |
| STARTUP | STARTUPID | Role Startup setup is kept here. |
| STATUS | STATUSID | Status are stored here |
| STOCK | STOCKID | Inventory Items are stored here |
| STOCKCHECK | STOCKCHECKID | Stockchecks are kept here |
| STOCKCHECKCOUNT | STOCKCHECKCOUNTID | Stockcheck counted values are stored here. |
| STOCKLOCATION | LOCATIONID | Inventory Locations are stored here. |
| SUPCAT | SUPCATID | Supplier Categories |
| SUPMAIN | SUPID | Suppliers |
| SUPCONTACTS | SUPCONTACTID | Supplier Contacts |
| TAXTYPES | TAXTYPEID | Tax Type options are stored here |
| TIMELOG | TIMELOGID | Time logged are stored here |
| TRANSFLOWS | TRANSFLOWID | Links to transactions copied from other transactions are stored here. |
| TRAILITEMS | TRAILITEMID | Items displayed on the Trail Balance |
| UNITS | UNITID | Material Units |
| VATITEMS | VATITEMID | Transaction Items are stored here. |
| WORKFLOW | WORKFLOWID | Workflows are Stored here |
| WORKLOWMODULES | MODULEID | Workflow Modules |
| WORKFLOWITEMS | WFITEMID | Items on Workflows |
| WORKFLOWTEMPLATES | WFTEMPID | Workflow setups are kept here |
Table links
Transactions: Ledger
| Transaction Types | join ENTRYTYPES E on E.ENTRYTYPEID = LEDGER.ENTRYTYPEID |
| Statuses | Left join STATUS S on S.STATUSID = LEDGER.STATUSID |
| Currencies | Join CURRENCIES C on C.CURRENCYID = LEDGER.CURRENCYID |
| Job Categories | Left Join CATEGORIES CT on CT.CATEGORYID = :LEDGER.CATEGORYID |
| From Transaction | Left join LEDGER L1 on L1.TRANSID = LEDGER.FROMID |
| Originating Transaction | Join LEDGER L2 on L2.TRANSID = LEDGER.FLOWID |
| Credit Terms | Left Join CREDITTERMS CR on CR.CREDITTERMID = LEDGER.CREDITTERMID |
| Item Entity | Left Join MATUNIT MU on MU.MATUNITID = LEDGER.MATUNITID |
| Business Entity | Left join ENTITIES E on E.ENTITYID = LEDGER.ENTITYID |
| Stock Location From | Left Join STOCKLOCATION S on S.LOCATIONID = LEDGER.LOCATIONIDFROM |
| Stock Location to | Left Join STOCKLOCATION L on L.LOCATIONID = LEDGER.LOCATIONIDTO |
| Bank Recon | Join BANK B on B.BANKID = LEDGER.BANKID |
| Rep | Join CPYSTAFF CP on CP.STAFFID = LEDGER.STAFFIDREP |
| Assign To | Join CPYSTAFF CP on CP.STAFFID = LEDGER.STAFFIDASSIGN |
| Customers Contacts | Join CUSMAIN CM on CM.CUSID = LEDGER.CUSID left join CUSCONTACTS CS on CS.CUSCONTACTID = LEDGER.CONTACTID |
| Suppliers Contact | Join SUPMAIN SM on SM.SUPID = LEDGER.COMPANYID left join SUPCONTACTS SS on SS.SUPCONTACTID = LEDGER.CONTACTID |
Transactions: Items (VATITEMS)
| Transactions | Join LEDGER L on L.TRANSID = VATITEMS.TRANSID |
| Tax Types | Join TAXTYPES TX on TX.TAXTYPEID = VATITEMS.TAXTYPEID |
| Item Status | Left join STATUS S on S.STATUSID = VATITEMS.STATUSID |
| Item Copied From | Join VATITEMS VF on VF.VATITEMID = VATITEMS.FROMID |
| Material Item | Left join MATUNIT MU on MU.MATUNITID = VATITEMS.MATUNITID |
| Material Unit | Left join UNITS U on U.UNITID = VATITEMS.UNITID |
| Estimate Type | Left Join PARAMGROUPS PG on PG.PARAMGROUPID = VATITEMS.ESTIMATEID |
| Inventory | Left join STOCK S on S.STOCKID = VATITEMS.STOCKID |
| Images | Left join IMAGES I on I.IMAGEID = VATITEMS.IMAGEID |
| Material Costs | Left join MATPRICE MP on MP.MATPRICEID = VATITEMS.MATPRICEID |
| Material Selling Prices | Left join MATCOST MC on Mc.MATCOSTID = V.MATCOSTID |
| Debit Accounts | Left join ACCOUNTS S on S.ACCOUNTID = VATITEMS.DRACCOUNTID |
| Credit Accounts | Left join ACCOUNTS S on S.ACCOUNTID = VATITEMS.CRACCOUNTID |
| Originating Trans Item | Left join VATITEMS V on V.VATITEMSID = VATITEMS.FLOWID |
Work flow (WORKFLOW)
| Work Flow Modules | Join WORKFLOWMODULES WF on WF.MODULEID = WORKFLOW.MODULEID |
| Parent Workflow Module | Left join WORKFLOW W on W.WORKFLOWID = WORKFLOW.PARENTID |
| User Created | Join CPYSTAFF CP on CP.STAFFID = WORKFLOW.USERID |
| Rep | Left join CPYSTAFF CP on CP.STAFFID = WORKFLOW.STAFFID |
| Status | Left join STATUS S on S.STATUSID = WORKLOW.STATUSID |
| Customers | Join CUSMAIN CM on SCM.CUSID = WORKFLOW.CUSID |
| Suppliers | Join SUPMAIN SM on SM.SUPID = WORKFLOW.SUPID |
| Item Entity | Left join MATUNIT MU on MU.MATUNITID = WORKFLOW.MATUNITID |
| CONTACTID (customer) | Left join CUSCONTACS CT on CT.CUSCONTACTID = WORKFLOW.CONTACTID |
| CONTACTID (supplier) | Left join SUPCONTACS CP on CP.SUPCONTACTID = WORKFLOW.CONTACTID |