Skip to content

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 NAMEPrimary KeyDESCRIPTION
ACCOUNTCATSACCOUNTCATIDStores the Account Categories
ACCOUNTSACCOUNTIDStores the Accounts
ACCREPORTSACCREPORTIDStores Accounting Reporting as per Entity
ACCREPORTITEMSACCREPORTITEMIDStores the Setup as per Accounting Report.
List the Accounts used and defines how it affects the Income Statement or Balance sheet
AGEINGAGEIDStores the Values of the Age Analysis for both the Customers as well as Suppliers
ALERTSALERTIDStores the Setups for the Transaction Alerts.
APPROVALSAPPROVALIDStores the setups for the Transactions approvals
ASSETSASSETIDList of the Assets set company
AUDITSAUDITIDStores Record of changes, Updates and Deletes of Transactions as well as setups.
BALSHTBALSHTIDInformation Displayed on the Balance Sheet
BANKBANKIDStores Transactions from the Bank Import from ofx files.
BATCHUPDATESBATCHUPDATEIDStores the Setup of the Actions setup on reports.
CALENDER_EVENTSEVENTIDStores Events added to the Calendar.
CASHUPCASHUPIDStores the Cashups done in the POS
CASHUPITEMSCASHUPITEMIDInformation of the Cashup
Money counted.
CASHUPLINKUSERSCASHUPLINKIDStores the Links between the Users and the Cashups done.
CASHUPNOTESCOINSCASHUPNOTESCOINSIDRecord of counted notes and coins on set Cashup.
CATEGORIESCATEGORYIDJob Categories
CCENTRECCENTREIDCost Centres in the Database,
CCCOSTSCCCOSTIDStores Costs Allocated to the Cost Centre
CONSOLECONSOLEIDDashboard Indicators
COSTITEMSCOSTITEMIDStores Items that reflect the Costs around a transactions/Transaction item
CPYMAINCPYMAINIDInformation of the Licensed Company
CPYSTAFFSTAFFIDLicensed Company Staff
CREDITTERMSCREDITTERMIDStores the Credit Terms
CURRENCIESCURRENCYIDDifferent Currencies and a Conversion rate to the Home currency.
CUSADDRESSADDRESSIDCustomer Delivery Addresses
CUSCATCUSCATIDCustomer Categories
CUSCONTACTSCUSCONTACTIDCustomer Contacts
CUSMAINCUSIDCustomer Information
DSAUDITSDSAUDITIDStores Records of all things Exported/Imported Using Datashift
DSMAINDSMAINIDData shift export/ Import files
DSTABLESDSTABLEIDTables used in the Export/ import file
DSFIELDSDSFIELDIDFields that will be imported/Exported from the selected table
EMAILSEMAILIDStores Email (sent and in the outbox)
EMAILS_ATTACHMENTSATTACHMENTIDStores the Email Attachments
EMAIL_LISTEMAIL_LIST_IDStores Email group for Mailshots
EMAIL_LIST_ITEMSLIST_ITEM_IDStores the Contacts in the Mail group
ENTITIESENTITYIDThis stores the Different Business Entities in the Database.
ENTITYLINKSENTITYLINKIDLinks to the Entities (include Staff, Transaction Types, Customers/Suppliers)
ENTRYTYPESENTRYTYPEIDStores the Transaction Types Setups.
FIELDSPECSFIELDSPECIDUserfields that are setup in the DB.
FIELDVALUESFIELDVALUEIDThe information stored in these user fields.
HOLIDAYSHOLIDAYIDStores Holidays.
IMAGESIMAGEIDImages saved in the DB
INCSTATINCSTATIDInformation that displays on the Income Statement.
INFO_TOPICSTOPICIDStores the articles in the Knowledge base.
LANDINGCOSTSLANDINGCOSTIDThis Table is where the Landing Costs of set item is kept.
LEAVELOGLEAVELOGIDThis is where the Staff leave information is kept
LEAVERULESLEAVERULEIDThis is where the rules for the Leave Types are kept.
LEAVETYPESLEAVETYPEIDInformation of the Type of leave days are kept. eg. Sick Leave, Family responsibilities.
LEDGERTRANSIDThis is where the Transactional header information are stored.
LEDGERALLOCATONSLEDGERALLOCIDThis is where all the transactional allocations are kept.
LOOKUPSLOOKUPIDThis is where the fixed look up list in QE are Kept.
MATACCMATACCIDStores the Material Accounting information are setup.
Location where we set Stock to affect accounting.
MATCATMATCATIDMaterial Categories are stored here.
MATCOSTMATCOSTIDWhere the selling price setup of materials are stored.
MATPRICEMATPRICEIDWhere the Cost price setups of materials are stored.
MATTREEMATTREEIDMaterial Bill of materials are kept here.
MATUNITMATUNITIDAll items in the DB are kept here.
MENUGROUPSMENUGROUPIDThe Menu groups are stored here
MENUITEMSMENUITEMIDMenu Items linked to selected Groups are stored here
MENULINKSMENULINKIDMenu Groups assigned to User Roles are stored here.
MODULESCOPYMODULECOPYIDThis is where the Copy Rules are stored.
MODULESCOSTINGMODULESCOSTINGIDThis is where the Costing Rules are kept.
MODULESFINMODULESFINIDTrans Type links for Financial reports are stored here.
MODULESROLESMODULESROLEIDUser Roles are set up here
MODULESSETTINGSMODULESSETTINGIDTrans Type setups linked to User Roles are stored here.
MODULESUSERROLESMODULESUSERROLEIDUser Roles assigned to users are kept here.
NOTESCOINSNOTESCOINSIDDenominations of home currency is stored here.
OUTQTYOUTQTYIDInformation on outstanding Qty's are kept here.
PARAMGROUPSPARAMGROUPIDEstimate types are stored here.
PARAMSPARAMIDEstimate Type Setups are stored here.
POSITIONSPOSITIONIDStaff Positions are stored here.
POSPRINTPOSPRINTID*
PRESSCYLINDERSPRESSCYLINDERIDFlexo Cylinder information are kept here
PRESSDIESPRESSDIEIDFlexo Die information are kept here.
PRICEHISTORYHISTORYIDPrice Update information are kept here.
PRICELISTSPRICELISTIDPricelist are stored
PRICELINKSPRICELINKIDCustomers linking to Pricelist setups are kept here.
PRICESPRICEIDPricelist Prices are stored
PRICEUPDATESPRICEUPDATEIDPrice Update Setups are stored here.
PROCCATPROCCATIDService Categories are stored here.
PROCESSPROCESSIDServices are stored here.
PROCMATPROCMATIDMaterials linked to Services setups are kept here
PRODCODESPRODCODEIDProduction codes are stored.
PRODTYPESPRODTYPEIDProduction Types are Stored
QUOTEBROKEQUOTEBROKEEstimate Outsourced Services are stored here.
QUOTECOMPSQUOTECOMPIDTransaction Component Estimating setups are stored here.
QUOTEDESCQUOTEDESCIDEstimate Descriptions
QUOTEFINQUOTEFINIDEstimate Services are stored here
QUOTEOUTSOURCEQUOTEOUTIDEstimate Bill of materials are stored here.
QUOTEPARAMSQUOTEPARAMIDEstimate Setups are stored here.
QUOTEQTYQUOTEQTYIDEstimate Qty's and Prices
QUOTESUBQUOTESUBIDEstimate Outsourced Prices are kept here
QUOTESUMQUOTESUMIDEstimate summary are stored here.
REMINDREMINDIDReminders
REPLICATEMAINREPLICATEMAINIDReplication selections are kept here
REPLICATEITEMSREPLICATEITEMIDThe Replication setups are stored here.
REPLICATELOGREPLICATELOGIDA log of all the Replication actions that had been done are kept here
REPORTSREPORTIDWhere All reports and template setups are kept.
REPORT_FIELDSFIELDIDWhere the Field setup on SQL Reports are kept.
SETTINGSSETTINGIDAll settings are stored
SOSTAGESSOSTAGEIDItems populating the Planning Board are stored here
STAGESSTAGEIDProduction Stages.
STAGESTATUSSTAGESTATUSIDProduction Stage Statuses.
STARTUPSTARTUPIDRole Startup setup is kept here.
STATUSSTATUSIDStatus are stored here
STOCKSTOCKIDInventory Items are stored here
STOCKCHECKSTOCKCHECKIDStockchecks are kept here
STOCKCHECKCOUNTSTOCKCHECKCOUNTIDStockcheck counted values are stored here.
STOCKLOCATIONLOCATIONIDInventory Locations are stored here.
SUPCATSUPCATIDSupplier Categories
SUPMAINSUPIDSuppliers
SUPCONTACTSSUPCONTACTIDSupplier Contacts
TAXTYPESTAXTYPEIDTax Type options are stored here
TIMELOGTIMELOGIDTime logged are stored here
TRANSFLOWSTRANSFLOWIDLinks to transactions copied from other transactions are stored here.
TRAILITEMSTRAILITEMIDItems displayed on the Trail Balance
UNITSUNITIDMaterial Units
VATITEMSVATITEMIDTransaction Items are stored here.
WORKFLOWWORKFLOWIDWorkflows are Stored here
WORKLOWMODULESMODULEIDWorkflow Modules
WORKFLOWITEMSWFITEMIDItems on Workflows
WORKFLOWTEMPLATESWFTEMPIDWorkflow setups are kept here

Transactions: Ledger

Transaction Typesjoin ENTRYTYPES E on E.ENTRYTYPEID = LEDGER.ENTRYTYPEID
StatusesLeft join STATUS S on S.STATUSID = LEDGER.STATUSID
CurrenciesJoin CURRENCIES C on C.CURRENCYID = LEDGER.CURRENCYID
Job CategoriesLeft Join CATEGORIES CT on CT.CATEGORYID = :LEDGER.CATEGORYID
From TransactionLeft join LEDGER L1 on L1.TRANSID = LEDGER.FROMID
Originating TransactionJoin LEDGER L2 on L2.TRANSID = LEDGER.FLOWID
Credit TermsLeft Join CREDITTERMS CR on CR.CREDITTERMID = LEDGER.CREDITTERMID
Item EntityLeft Join MATUNIT MU on MU.MATUNITID = LEDGER.MATUNITID
Business EntityLeft join ENTITIES E on E.ENTITYID = LEDGER.ENTITYID
Stock Location FromLeft Join STOCKLOCATION S on S.LOCATIONID = LEDGER.LOCATIONIDFROM
Stock Location toLeft Join STOCKLOCATION L on L.LOCATIONID = LEDGER.LOCATIONIDTO
Bank ReconJoin BANK B on B.BANKID = LEDGER.BANKID
RepJoin CPYSTAFF CP on CP.STAFFID = LEDGER.STAFFIDREP
Assign ToJoin 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)

TransactionsJoin LEDGER L on L.TRANSID = VATITEMS.TRANSID
Tax TypesJoin TAXTYPES TX on TX.TAXTYPEID = VATITEMS.TAXTYPEID
Item StatusLeft join STATUS S on S.STATUSID = VATITEMS.STATUSID
Item Copied FromJoin VATITEMS VF on VF.VATITEMID = VATITEMS.FROMID
Material ItemLeft join MATUNIT MU on MU.MATUNITID = VATITEMS.MATUNITID
Material UnitLeft join UNITS U on U.UNITID = VATITEMS.UNITID
Estimate TypeLeft Join PARAMGROUPS PG on PG.PARAMGROUPID = VATITEMS.ESTIMATEID
InventoryLeft join STOCK S on S.STOCKID = VATITEMS.STOCKID
ImagesLeft join IMAGES I on I.IMAGEID = VATITEMS.IMAGEID
Material CostsLeft join MATPRICE MP on MP.MATPRICEID = VATITEMS.MATPRICEID
Material Selling PricesLeft join MATCOST MC on Mc.MATCOSTID = V.MATCOSTID
Debit AccountsLeft join ACCOUNTS S on S.ACCOUNTID = VATITEMS.DRACCOUNTID
Credit AccountsLeft join ACCOUNTS S on S.ACCOUNTID = VATITEMS.CRACCOUNTID
Originating Trans ItemLeft join VATITEMS V on V.VATITEMSID = VATITEMS.FLOWID

Work flow (WORKFLOW)

Work Flow ModulesJoin WORKFLOWMODULES WF on WF.MODULEID = WORKFLOW.MODULEID
Parent Workflow ModuleLeft join WORKFLOW W on W.WORKFLOWID = WORKFLOW.PARENTID
User CreatedJoin CPYSTAFF CP on CP.STAFFID = WORKFLOW.USERID
RepLeft join CPYSTAFF CP on CP.STAFFID = WORKFLOW.STAFFID
StatusLeft join STATUS S on S.STATUSID = WORKLOW.STATUSID
CustomersJoin CUSMAIN CM on SCM.CUSID = WORKFLOW.CUSID
SuppliersJoin SUPMAIN SM on SM.SUPID = WORKFLOW.SUPID
Item EntityLeft 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