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 |