MYOB EXO Business Job Costing

Entity details

Entity: GLACCS

Comment	This table contains details of all General Ledger accounts.
Primary key columns	ACCNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
ACCNO	Yes	INTEGER	Yes	The unique GL account number.
NAME	No	VARCHAR(40)	No	The account name.
DRCR	No	CHAR(1)	No	This field is set to 'C' for Credit accounts or 'D' for Debit accounts.
SECTION	No	INTEGER	No	This field is set to 0 for Profit and loss accounts or 1 for Balance sheet accounts.
OPENINGBAL	No	FLOAT(53)	No	The account's opening balance.
BALANCE	No	FLOAT(53)	No	The account's balance.
USESUBCODES	No	CHAR(1)	No	Not used.
REPORTCODE	No	VARCHAR(15)	No	The report code used by the GL account.
TAXSTATUS	No	INTEGER	No	The Override GST rate specified for the GL account. A value of -1 corresponds to the AUTO SELECT RATE option.
CURRENCYNO	No	INTEGER	No	The ID number of the currency that the GL account is in.
ISACTIVE	No	CHAR(1)	No	This field contains 'Y' if the GL account is active.
ACCGROUP	No	INTEGER	No	The GL account's Account Group.
ALLOWJOURNAL	No	CHAR(1)	No	This field is set to 'Y' if the GL account allows journals.
LAST_UPDATED	No	DATETIME	No	The date and time when the GL account was last updated.
BALSHEETACCTOT	No	AS	No	The balance sheet total for the account.
BALANCE_SHEET_TYPE	No	INTEGER	Yes	For balance sheet accounts, this field indicates the balance sheet type. Will be one of: -1 = Not a balance sheet account 0 = Asset 1 = Liability 2 = Equity
NOTES	No	VARCHAR(4096)	No	Any notes on the GL account.
EXCLUDE_FROM_REVAL	No	CHAR(1)	No	If this field is set to 'Y', the account is excluded from revaluation.
PRIVATE_ACC	No	CHAR(1)	Yes	This field is set to 'Y' if the GL account is a private account.
CREATED_DATE	No	DATETIME	No	The date and time that the GL account was created.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
CURRENCIES_GLACCS	Non Identifying	CURRENCIES	GLACCS	Zero Or More
GLACCS_CR_TRANS	Non Identifying	GLACCS	CR_TRANS	Zero Or More
GLACCS_DR_TRANS	Non Identifying	GLACCS	DR_TRANS	Zero Or More
GLACCS_JOBCOST_HDR	Non Identifying	GLACCS	JOBCOST_HDR	Zero Or More
GLACCS_JOBCOST_HDR_SALES	Non Identifying	GLACCS	JOBCOST_HDR	Zero Or More
GLACCS_JOBCOST_LINES	Non Identifying	GLACCS	JOBCOST_LINES	Zero Or More
GLACCS_JOB_CONTRACT_BILLINGS	Non Identifying	GLACCS	JOB_CONTRACT_BILLINGS	Zero Or More
GLACCS_JOB_CONTRACT_BILLINGS_SUB	Non Identifying	GLACCS	JOB_CONTRACT_BILLINGS	Zero Or More
GLACCS_JOB_TRANSACTIONS	Non Identifying	GLACCS	JOB_TRANSACTIONS	Zero Or More
GLACCS_OPPORTUNITY_QUOTE	Non Identifying	GLACCS	OPPORTUNITY_QUOTE	Zero Or More
GLACCS_STOCK_GROUPS	Non Identifying	GLACCS	STOCK_GROUPS	Zero Or More
GLACCS_STOCK_GROUPS_COS	Non Identifying	GLACCS	STOCK_GROUPS	Zero Or More
GLACCS_STOCK_GROUPS_P	Non Identifying	GLACCS	STOCK_GROUPS	Zero Or More
GLACCS_STOCK_ITEMS	Non Identifying	GLACCS	STOCK_ITEMS	Zero Or More
GLACCS_STOCK_ITEMS_COS	Non Identifying	GLACCS	STOCK_ITEMS	Zero Or More
GLACCS_STOCK_TRANS	Non Identifying	GLACCS	STOCK_TRANS	Zero Or More
GL_ACCS_STOCKITEMS_PO	Non Identifying	GLACCS	STOCK_ITEMS	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (PRIVATE_ACC)
	Not Null	Column Constraint	NOT NULL (ACCNO)
	Not Null	Column Constraint	NOT NULL (BALANCE_SHEET_TYPE)
CURRENCIES_GLACCS	Foreign Key	Table Constraint	FOREIGN KEY (CURRENCYNO) REFERENCES CURRENCIES(CURRENCYNO)
DF__GLACCS__ACCGROUP__613C58EC	Default	Column Constraint	ACCGROUP DEFAULT 0
DF__GLACCS__ALLOWJOU__2882FE7D	Default	Column Constraint	ALLOWJOURNAL DEFAULT Y
DF__GLACCS__BALANCE__3E082B48	Default	Column Constraint	BALANCE DEFAULT 0
DF__GLACCS__BALANCE___40113AA2	Default	Column Constraint	BALANCE_SHEET_TYPE DEFAULT -1
DF__GLACCS__CURRENCY__3A228BCB	Default	Column Constraint	CURRENCYNO DEFAULT 0
DF__GLACCS__DRCR__3B2BBE9D	Default	Column Constraint	DRCR DEFAULT D
DF__GLACCS__EXCLUDE___23CA01AF	Default	Column Constraint	EXCLUDE_FROM_REVAL DEFAULT N
DF__GLACCS__ISACTIVE__58A712EB	Default	Column Constraint	ISACTIVE DEFAULT Y
DF__GLACCS__OPENINGB__3D14070F	Default	Column Constraint	OPENINGBAL DEFAULT 0
DF__GLACCS__PRIVATE___13697CB4	Default	Column Constraint	PRIVATE_ACC DEFAULT N
DF__GLACCS__SECTION__3C1FE2D6	Default	Column Constraint	SECTION DEFAULT 0
DF__GLACCS__TAXSTATU__51700577	Default	Column Constraint	TAXSTATUS DEFAULT 0
DF__GLACCS__USESUBCO__3FF073BA	Default	Column Constraint	USESUBCODES DEFAULT N
PK__GLACCS__3A379A64	Primary Key	Table Constraint	PRIMARY KEY (ACCNO)

Entity: JOBCOST_FLAGS

Comment	This table records the statuses for the various Job Flags on each job.
Primary key columns

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
JOBNO	No	INTEGER	Yes	The job's ID number.
INVOICEREADY	No	CHAR(1)	Yes	This field contains 'Y' if the job's Invoice Ready flag is ticked.
ISACTIVE	No	CHAR(1)	Yes	This field contains 'Y' if the job's Active Job flag is ticked.
ISCOMPLETE	No	CHAR(1)	Yes	This field contains 'Y' if the job's Completed flag is ticked.
ISARCHIVED	No	CHAR(1)	Yes	This field contains 'Y' if the job's Archived Job flag is ticked.
FLAG01	No	CHAR(1)	Yes	This field contains 'Y' if custom flag 1 is ticked for the job.
FLAG02	No	CHAR(1)	Yes	This field contains 'Y' if custom flag 2 is ticked for the job.
FLAG03	No	CHAR(1)	Yes	This field contains 'Y' if custom flag 3 is ticked for the job.
FLAG04	No	CHAR(1)	Yes	This field contains 'Y' if custom flag 4 is ticked for the job.
FLAG05	No	CHAR(1)	Yes	This field contains 'Y' if custom flag 5 is ticked for the job.
FLAG06	No	CHAR(1)	Yes	This field contains 'Y' if custom flag 6 is ticked for the job.
FLAG07	No	CHAR(1)	Yes	This field contains 'Y' if custom flag 7 is ticked for the job.
FLAG08	No	CHAR(1)	Yes	This field contains 'Y' if custom flag 8 is ticked for the job.
FLAG09	No	CHAR(1)	Yes	This field contains 'Y' if custom flag 9 is ticked for the job.
FLAG10	No	CHAR(1)	Yes	This field contains 'Y' if custom flag 10 is ticked for the job.
FLAG11	No	CHAR(1)	Yes	This field contains 'Y' if custom flag 11 is ticked for the job.
FLAG12	No	CHAR(1)	Yes	This field contains 'Y' if custom flag 12 is ticked for the job.
FLAG13	No	CHAR(1)	Yes	This field contains 'Y' if custom flag 13 is ticked for the job.
FLAG14	No	CHAR(1)	Yes	This field contains 'Y' if custom flag 14 is ticked for the job.
FLAG15	No	CHAR(1)	Yes	This field contains 'Y' if custom flag 15 is ticked for the job.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
JOBCOST_HDR_JOBCOST_FLAGS	Non Identifying	JOBCOST_HDR	JOBCOST_FLAGS	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (FLAG15)
	Not Null	Column Constraint	NOT NULL (JOBNO)
	Not Null	Column Constraint	NOT NULL (FLAG14)
	Not Null	Column Constraint	NOT NULL (INVOICEREADY)
	Not Null	Column Constraint	NOT NULL (FLAG13)
	Not Null	Column Constraint	NOT NULL (ISACTIVE)
	Not Null	Column Constraint	NOT NULL (FLAG12)
	Not Null	Column Constraint	NOT NULL (ISCOMPLETE)
	Not Null	Column Constraint	NOT NULL (FLAG11)
	Not Null	Column Constraint	NOT NULL (ISARCHIVED)
	Not Null	Column Constraint	NOT NULL (FLAG10)
	Not Null	Column Constraint	NOT NULL (FLAG01)
	Not Null	Column Constraint	NOT NULL (FLAG09)
	Not Null	Column Constraint	NOT NULL (FLAG02)
	Not Null	Column Constraint	NOT NULL (FLAG08)
	Not Null	Column Constraint	NOT NULL (FLAG03)
	Not Null	Column Constraint	NOT NULL (FLAG07)
	Not Null	Column Constraint	NOT NULL (FLAG04)
	Not Null	Column Constraint	NOT NULL (FLAG06)
	Not Null	Column Constraint	NOT NULL (FLAG05)
DF__JOBCOST_F__FLAG0__0D65BF44	Default	Column Constraint	FLAG01 DEFAULT N
DF__JOBCOST_F__FLAG0__0E59E37D	Default	Column Constraint	FLAG02 DEFAULT N
DF__JOBCOST_F__FLAG0__0F4E07B6	Default	Column Constraint	FLAG03 DEFAULT N
DF__JOBCOST_F__FLAG0__10422BEF	Default	Column Constraint	FLAG04 DEFAULT N
DF__JOBCOST_F__FLAG0__11365028	Default	Column Constraint	FLAG05 DEFAULT N
DF__JOBCOST_F__FLAG0__122A7461	Default	Column Constraint	FLAG06 DEFAULT N
DF__JOBCOST_F__FLAG0__131E989A	Default	Column Constraint	FLAG07 DEFAULT N
DF__JOBCOST_F__FLAG0__1412BCD3	Default	Column Constraint	FLAG08 DEFAULT N
DF__JOBCOST_F__FLAG0__1506E10C	Default	Column Constraint	FLAG09 DEFAULT N
DF__JOBCOST_F__FLAG1__15FB0545	Default	Column Constraint	FLAG10 DEFAULT N
DF__JOBCOST_F__FLAG1__16EF297E	Default	Column Constraint	FLAG11 DEFAULT N
DF__JOBCOST_F__FLAG1__17E34DB7	Default	Column Constraint	FLAG12 DEFAULT N
DF__JOBCOST_F__FLAG1__18D771F0	Default	Column Constraint	FLAG13 DEFAULT N
DF__JOBCOST_F__FLAG1__19CB9629	Default	Column Constraint	FLAG14 DEFAULT N
DF__JOBCOST_F__FLAG1__1ABFBA62	Default	Column Constraint	FLAG15 DEFAULT N
DF__JOBCOST_F__INVOI__09952E60	Default	Column Constraint	INVOICEREADY DEFAULT N
DF__JOBCOST_F__ISACT__0A895299	Default	Column Constraint	ISACTIVE DEFAULT Y
DF__JOBCOST_F__ISARC__0C719B0B	Default	Column Constraint	ISARCHIVED DEFAULT N
DF__JOBCOST_F__ISCOM__0B7D76D2	Default	Column Constraint	ISCOMPLETE DEFAULT N
DF__JOBCOST_F__JOBNO__08A10A27	Default	Column Constraint	JOBNO DEFAULT 0
JOBCOST_HDR_JOBCOST_FLAGS	Foreign Key	Table Constraint	FOREIGN KEY (JOBNO) REFERENCES JOBCOST_HDR(JOBNO)

Entity: JOBCOST_FLAGSDESC

Comment	This table records descriptions for the customisable Job Flags.
Primary key columns	FLAGCODE

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
FLAGCODE	Yes	VARCHAR(8)	Yes	The Job Flag's ID number.
FLAGDESC	No	VARCHAR(60)	No	The Job Flag's description.

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (FLAGCODE)
PK__JOBCOST_FLAGSDES__1CA802D4	Primary Key	Table Constraint	PRIMARY KEY (FLAGCODE)

Entity: JOBCOST_GENERAL_INFO

Comment	This table is no longer used - Job Costing configuration options are now stored as profile settings.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	No longer used.
SOFTWARE_VERSION	No	INTEGER	No	No longer used.
DATAVERSION	No	INTEGER	No	No longer used.
QUOTE_TERMS	No	VARCHAR(1000)	No	No longer used.
STOCKLOC	No	INTEGER	No	No longer used.
JOBCAT	No	INTEGER	No	No longer used.
SUBSADDUP	No	VARCHAR(1)	No	No longer used.
JOBSTAT	No	VARCHAR(1)	No	No longer used.
JOBTYPE	No	INTEGER	No	No longer used.
MOVE_STOCK	No	CHAR(1)	Yes	No longer used.
COSTGLSQL	No	VARCHAR(500)	No	No longer used.
SALESGLSQL	No	VARCHAR(500)	No	No longer used.
DEFDAYS	No	INTEGER	No	No longer used.
DEFHOURS	No	INTEGER	No	No longer used.
OURREF	No	VARCHAR(30)	No	No longer used.
PRINTDESC	No	VARCHAR(1)	No	No longer used.
OPTION_1	No	VARCHAR(1)	No	No longer used.
OPTION_2	No	VARCHAR(1)	No	No longer used.
OPTION_3	No	VARCHAR(1)	No	No longer used.
OPTION_4	No	VARCHAR(1)	No	No longer used.
OPTION_5	No	VARCHAR(1)	No	No longer used.
OPTION_6	No	VARCHAR(1)	No	No longer used.
OPTION_7	No	VARCHAR(1)	No	No longer used.
OPTION_8	No	VARCHAR(1)	No	No longer used.
OPTION_9	No	VARCHAR(1)	No	No longer used.
OPTION_10	No	VARCHAR(1)	No	No longer used.
JOBNO_PROMPT	No	VARCHAR(1)	No	No longer used.
SCHEDULER	No	VARCHAR(1)	No	No longer used.
TIMESELECTSQL	No	VARCHAR(255)	No	No longer used.
COSTSELECTSQL	No	VARCHAR(255)	No	No longer used.
STOCKCOST	No	INTEGER	No	No longer used.
GLACC_TO_USE	No	INTEGER	No	No longer used.
WORKSHEETTYPE	No	INTEGER	No	No longer used.
SHOWMAIN	No	VARCHAR(1)	No	No longer used.
WIPNOTSOH	No	VARCHAR(1)	No	No longer used.
DEFNONSTOCKCODE	No	VARCHAR(23)	No	No longer used.
USE_SALES_ORD_NO	No	CHAR(1)	Yes	No longer used.
QUOTECOST	No	INTEGER	Yes	No longer used.
DEFFOLLOWUPDAYS	No	INTEGER	Yes	No longer used.

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (DEFFOLLOWUPDAYS)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (QUOTECOST)
	Not Null	Column Constraint	NOT NULL (USE_SALES_ORD_NO)
	Not Null	Column Constraint	NOT NULL (MOVE_STOCK)
DF__JOBCOST_G__DATAV__5674B1B6	Default	Column Constraint	DATAVERSION DEFAULT 0
DF__JOBCOST_G__DEFFO__05C49D7C	Default	Column Constraint	DEFFOLLOWUPDAYS DEFAULT 0
DF__JOBCOST_G__GLACC__5D21AF45	Default	Column Constraint	GLACC_TO_USE DEFAULT 0
DF__JOBCOST_G__JOBCA__5768D5EF	Default	Column Constraint	JOBCAT DEFAULT 0
DF__JOBCOST_G__JOBTY__585CFA28	Default	Column Constraint	JOBTYPE DEFAULT 0
DF__JOBCOST_G__MOVE___59511E61	Default	Column Constraint	MOVE_STOCK DEFAULT N
DF__JOBCOST_G__QUOTE__34DEB3C1	Default	Column Constraint	QUOTECOST DEFAULT 1
DF__JOBCOST_G__SHOWM__5F09F7B7	Default	Column Constraint	SHOWMAIN DEFAULT N
DF__JOBCOST_G__SOFTW__55808D7D	Default	Column Constraint	SOFTWARE_VERSION DEFAULT 0
DF__JOBCOST_G__STOCK__5C2D8B0C	Default	Column Constraint	STOCKCOST DEFAULT 0
DF__JOBCOST_G__USE_S__33EA8F88	Default	Column Constraint	USE_SALES_ORD_NO DEFAULT N
DF__JOBCOST_G__WIPNO__5FFE1BF0	Default	Column Constraint	WIPNOTSOH DEFAULT N
DF__JOBCOST_G__WORKS__5E15D37E	Default	Column Constraint	WORKSHEETTYPE DEFAULT 1
PK__JOBCOST_GENERAL___548C6944	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)

Entity: JOBCOST_HDR

Comment	This table contains header information for all jobs.
Primary key columns	JOBNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
JOBNO	Yes	INTEGER	Yes	A unique ID number for the job.
QUOTEDATE	No	DATETIME	No	The quote date on the job.
STARTDATE	No	DATETIME	No	The job's start date.
DUEDATE	No	DATETIME	No	The date when the job is due.
COMPLETED	No	DATETIME	No	The date when the job was completed.
ESTIMATE	No	FLOAT(53)	No	An estimate of the job's value.
INVOICED	No	FLOAT(53)	No	The amount of the job that has been invoiced.
THETIME	No	FLOAT(53)	No	Total amount on timesheets for the job.
MATERIALS	No	FLOAT(53)	No	Total amount on the Costs tab for the job.
DEF_OVERHEAD	No	FLOAT(53)	No	The default overhead on the job.
MATERIALSCOST	No	FLOAT(53)	No	The total costs on the Costs tab.
ESTIMATECOST	No	FLOAT(53)	No	The estimated cost of the job.
THETIMECOST	No	FLOAT(53)	No	The total costs on the Timesheets tab.
INVOICEDCOST	No	FLOAT(53)	No	The total cost of all invoiced lines on the job.
JOBCODE	No	VARCHAR(15)	No	An alphanumeric ID code for the job.
ACCNO	No	INTEGER	No	The number of the Debtor account that the job belongs to.
CUSTORDNO	No	VARCHAR(15)	No	The customer's order number.
STATUS	No	CHAR(1)	No	The ID code of the job's status (from JOB_STATUS).
TITLE	No	VARCHAR(60)	No	A descriptive title for the job.
CATEGORY	No	INTEGER	No	The ID number of the job's category (from JOB_CATEGORIES).
JOBTYPE	No	INTEGER	No	The ID number of the job's type (from JOB_TYPES).
STAFFNO	No	INTEGER	No	The EXO Business staff member responsible for entering the job.
ACTIONBY	No	INTEGER	No	The staff member who is the Job Manager for the job.
MASTER_JOBNO	No	INTEGER	Yes	If the job is a sub-job, this field stores the ID number of the master job.
COSTGL	No	INTEGER	No	The Cost GL account.
SALESGL	No	INTEGER	No	The P&L revenue account for journals when invoicing.
SERIALNO	No	VARCHAR(50)	No	Reference.
CONTACT	No	VARCHAR(50)	No	The contact person associated with the job.
PRIVATE_NOTE	No	CHAR(1000)	No	Any notes on the job.
COSTSUBGL	No	INTEGER	No	The Cost GL sub-account.
SALESSUBGL	No	INTEGER	No	The P&L revenue sub-account for journals when invoicing.
DEL_ADDR	No	INTEGER	No	Delivery address.
CONTACTNO	No	INTEGER	No	The ID number of the contact person on the job.
DELADDR1	No	VARCHAR(30)	No	Delivery address line 1.
DELADDR2	No	VARCHAR(30)	No	Delivery address line 2.
DELADDR3	No	VARCHAR(30)	No	Delivery address line 3.
DELADDR4	No	VARCHAR(30)	No	Delivery address line 4.
DELADDR5	No	VARCHAR(30)	No	Delivery address line 5.
DELADDR6	No	VARCHAR(30)	No	Delivery address line 6.
WRITE_OFF_COST	No	FLOAT(53)	No	The total cost of all written off lines on the job.
TOTAL_HOURS	No	FLOAT(53)	No	The total hours on the job.
EST_HOURS	No	FLOAT(53)	No	Estimated hours on the job.
ASSET_COST	No	FLOAT(53)	No	The cost of the asset(s) created on the job.
ASSET_VALUE	No	FLOAT(53)	No	The value of the asset(s) created on the job.
BRANCHNO	No	INTEGER	No	The job's branch.
ISACTIVE	No	CHAR(1)	Yes	This field contains 'Y' if the job is currently active.
HASUNBILLED	No	CHAR(1)	Yes
INVOICEREADY	No	CHAR(1)	Yes
CALLBACKDATE	No	DATETIME	No	The Follow Up date on the job.
ENTRYDATE	No	DATETIME	No	The date and time when the job was entered.
TOTALVALUE	No	AS	No	The total value of the job (Timesheets and Costs tabs).
TOTALCOST	No	AS	No	The total cost of the job (Timesheets and Costs tabs).
WIPLOC	No	INTEGER	No	The Work in Progress location for the job.
EXCHRATE	No	FLOAT(53)	Yes	The exchange rate used by the job.
RETENTION_RATE	No	FLOAT(53)	Yes	The base retention rate on the job.
RETENTION2_RATE	No	FLOAT(53)	Yes	The Level 2 retention rate on the job.
RETENTION2_MIN	No	FLOAT(53)	Yes	The Minimum Value for the Level 2 Retention on the job.
RETENTION3_RATE	No	FLOAT(53)	Yes	The Level 3 retention rate on the job.
RETENTION3_MIN	No	FLOAT(53)	Yes	The Minimum Value for the Level 3 Retention on the job.
ALLOWANCE	No	FLOAT(53)	Yes
BILLINGMODE	No	INTEGER	No	The billing mode on the job. Will be one of: 0 = Charge-up 1 = Invoice ex-quote 2 = Manufacture - output items 3 = Progress invoices
PROJ_SEQNO	No	INTEGER	No	The ID number of the project that the job belongs to.
DESCRIPTION	No	VARCHAR(5000)	No	The job's description.
OPPORTUNITY_SEQNO	No	INTEGER	No	The ID number of the Opportunity (EXO CRM) that the job is associated with, if there is one.
CAMPAIGN_WAVE_SEQNO	No	INTEGER	No	The ID number of the campaign wave (EXO CRM) that the job is associated with, if there is one.
LINECHARGE_WRITEOFF	No	FLOAT(53)	No	This field stores the sum of value of all JOB_TRANSACTIONS.LINECHARGE_WRITEOFF values on the transaction lines.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
BRANCHES_JOBCOST_HDR	Non Identifying	BRANCHES	JOBCOST_HDR	Zero Or More
CAMPAIGN_WAVE_JOBCOST_HDR	Non Identifying	CAMPAIGN_WAVE	JOBCOST_HDR	Zero Or More
DR_ACCS_JOBCOST_HDR	Non Identifying	DR_ACCS	JOBCOST_HDR	Zero Or More
GLACCS_JOBCOST_HDR	Non Identifying	GLACCS	JOBCOST_HDR	Zero Or More
GLACCS_JOBCOST_HDR_SALES	Non Identifying	GLACCS	JOBCOST_HDR	Zero Or More
JOBCOST_HDR_INWARDS_GOODS_LINES	Non Identifying	JOBCOST_HDR	INWARDS_GOODS_LINES	Zero Or More
JOBCOST_HDR_JOBCOST_FLAGS	Non Identifying	JOBCOST_HDR	JOBCOST_FLAGS	Zero Or More
JOBCOST_HDR_JOBCOST_LINES	Non Identifying	JOBCOST_HDR	JOBCOST_LINES	Zero Or More
JOBCOST_HDR_JOBCOST_LINES_MASTER	Non Identifying	JOBCOST_HDR	JOBCOST_LINES	Zero Or More
JOBCOST_HDR_JOB_CONTRACT_BILLINGS	Non Identifying	JOBCOST_HDR	JOB_CONTRACT_BILLINGS	Zero Or More
JOBCOST_HDR_JOB_CONTRACT_BILLINGS_MASTER	Non Identifying	JOBCOST_HDR	JOB_CONTRACT_BILLINGS	Zero Or More
JOBCOST_HDR_JOB_HIST	Non Identifying	JOBCOST_HDR	JOB_HIST	Zero Or More
JOBCOST_HDR_JOB_OUTPUT_ITEMS	Non Identifying	JOBCOST_HDR	JOB_OUTPUT_ITEMS	Zero Or More
JOBCOST_HDR_JOB_RESOURCE_ALLOCATION	Non Identifying	JOBCOST_HDR	JOB_RESOURCE_ALLOCATION	Zero Or More
JOBCOST_HDR_JOB_TIMESHEETS	Non Identifying	JOBCOST_HDR	JOB_TIMESHEETS	Zero Or More
JOBCOST_HDR_JOB_TIMESHEETS_TITLE	Non Identifying	JOBCOST_HDR	JOB_TIMESHEETS	Zero Or More
JOBCOST_HDR_JOB_TRANSACTIONS	Non Identifying	JOBCOST_HDR	JOB_TRANSACTIONS	Zero Or More
JOBCOST_HDR_JOB_TRANSACTIONS_MASTER	Non Identifying	JOBCOST_HDR	JOB_TRANSACTIONS	Zero Or More
JOBCOST_HDR_TASKS	Non Identifying	JOBCOST_HDR	TASKS	Zero Or More
JOBCOST_PROJ_JOBCOST_HDR	Non Identifying	JOBCOST_PROJ	JOBCOST_HDR	Zero Or More
JOB_CATEGORIES_JOBCOST_HDR	Non Identifying	JOB_CATEGORIES	JOBCOST_HDR	Zero Or More
JOB_STATUS_JOBCOST_HDR	Non Identifying	JOB_STATUS	JOBCOST_HDR	Zero Or More
JOB_TYPES_JOBCOST_HDR	Non Identifying	JOB_TYPES	JOBCOST_HDR	Zero Or More
OPPORTUNITY_JOBCOST_HDR	Non Identifying	OPPORTUNITY	JOBCOST_HDR	Zero Or More
STAFF_JOBCOST_HDR	Non Identifying	STAFF	JOBCOST_HDR	Zero Or More
STAFF_JOBCOST_HDR_MGR	Non Identifying	STAFF	JOBCOST_HDR	Zero Or More

Indexes:

Name	Index columns	Index type
SK1_JOBCOST_HDR	JOBCODE ASC	UNIQUE

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (ALLOWANCE)
	Not Null	Column Constraint	NOT NULL (JOBNO)
	Not Null	Column Constraint	NOT NULL (RETENTION3_MIN)
	Not Null	Column Constraint	NOT NULL (RETENTION3_RATE)
	Not Null	Column Constraint	NOT NULL (RETENTION2_MIN)
	Not Null	Column Constraint	NOT NULL (RETENTION2_RATE)
	Not Null	Column Constraint	NOT NULL (RETENTION_RATE)
	Not Null	Column Constraint	NOT NULL (MASTER_JOBNO)
	Not Null	Column Constraint	NOT NULL (EXCHRATE)
	Not Null	Column Constraint	NOT NULL (INVOICEREADY)
	Not Null	Column Constraint	NOT NULL (HASUNBILLED)
	Not Null	Column Constraint	NOT NULL (ISACTIVE)
BRANCHES_JOBCOST_HDR	Foreign Key	Table Constraint	FOREIGN KEY (BRANCHNO) REFERENCES BRANCHES(BRANCHNO)
CAMPAIGN_WAVE_JOBCOST_HDR	Foreign Key	Table Constraint	FOREIGN KEY (CAMPAIGN_WAVE_SEQNO) REFERENCES CAMPAIGN_WAVE(SEQNO)
DF__JOBCOST_H__ALLOW__5828BB55	Default	Column Constraint	ALLOWANCE DEFAULT 0
DF__JOBCOST_H__ASSET__3E681DFB	Default	Column Constraint	ASSET_COST DEFAULT 0
DF__JOBCOST_H__ASSET__3F5C4234	Default	Column Constraint	ASSET_VALUE DEFAULT 0
DF__JOBCOST_H__BILLI__4C81FE7F	Default	Column Constraint	BILLINGMODE DEFAULT 0
DF__JOBCOST_H__BRANC__46C859D2	Default	Column Constraint	BRANCHNO DEFAULT 0
DF__JOBCOST_H__CALLB__7F179FED	Default	Column Constraint	CALLBACKDATE DEFAULT getdate()
DF__JOBCOST_H__CAMPA__0D717793	Default	Column Constraint	CAMPAIGN_WAVE_SEQNO DEFAULT -1
DF__JOBCOST_H__CONTA__12899BBD	Default	Column Constraint	CONTACTNO DEFAULT -1
DF__JOBCOST_H__COSTS__0FAD2F12	Default	Column Constraint	COSTSUBGL DEFAULT 0
DF__JOBCOST_H__DEF_O__09F455BC	Default	Column Constraint	DEF_OVERHEAD DEFAULT 0
DF__JOBCOST_H__DEL_A__11957784	Default	Column Constraint	DEL_ADDR DEFAULT -1
DF__JOBCOST_H__ENTRY__000BC426	Default	Column Constraint	ENTRYDATE DEFAULT getdate()
DF__JOBCOST_H__ESTIM__0BDC9E2E	Default	Column Constraint	ESTIMATECOST DEFAULT 0
DF__JOBCOST_H__EST_H__3D73F9C2	Default	Column Constraint	EST_HOURS DEFAULT 0
DF__JOBCOST_H__EXCHR__5BB95517	Default	Column Constraint	EXCHRATE DEFAULT 1
DF__JOBCOST_H__HASUN__48B0A244	Default	Column Constraint	HASUNBILLED DEFAULT N
DF__JOBCOST_H__INVOI__0DC4E6A0	Default	Column Constraint	INVOICEDCOST DEFAULT 0
DF__JOBCOST_H__INVOI__49A4C67D	Default	Column Constraint	INVOICEREADY DEFAULT N
DF__JOBCOST_H__ISACT__47BC7E0B	Default	Column Constraint	ISACTIVE DEFAULT Y
DF__JOBCOST_H__LINEC__0A6AEDB6	Default	Column Constraint	LINECHARGE_WRITEOFF DEFAULT 0
DF__JOBCOST_H__MASTE__0EB90AD9	Default	Column Constraint	MASTER_JOBNO DEFAULT 0
DF__JOBCOST_H__MATER__0AE879F5	Default	Column Constraint	MATERIALSCOST DEFAULT 0
DF__JOBCOST_H__OPPOR__74BAD862	Default	Column Constraint	OPPORTUNITY_SEQNO DEFAULT -1
DF__JOBCOST_H__RETEN__104D18E7	Default	Column Constraint	RETENTION2_RATE DEFAULT 0
DF__JOBCOST_H__RETEN__11413D20	Default	Column Constraint	RETENTION2_MIN DEFAULT 0
DF__JOBCOST_H__RETEN__12356159	Default	Column Constraint	RETENTION3_RATE DEFAULT 0
DF__JOBCOST_H__RETEN__13298592	Default	Column Constraint	RETENTION3_MIN DEFAULT 0
DF__JOBCOST_H__RETEN__6542BF51	Default	Column Constraint	RETENTION_RATE DEFAULT 0
DF__JOBCOST_H__SALES__10A1534B	Default	Column Constraint	SALESSUBGL DEFAULT 0
DF__JOBCOST_H__THETI__0CD0C267	Default	Column Constraint	THETIMECOST DEFAULT 0
DF__JOBCOST_H__TOTAL__3C7FD589	Default	Column Constraint	TOTAL_HOURS DEFAULT 0
DF__JOBCOST_H__WRITE__3B8BB150	Default	Column Constraint	WRITE_OFF_COST DEFAULT 0
DR_ACCS_JOBCOST_HDR	Foreign Key	Table Constraint	FOREIGN KEY (ACCNO) REFERENCES DR_ACCS(ACCNO)
GLACCS_JOBCOST_HDR	Foreign Key	Table Constraint	FOREIGN KEY (COSTGL) REFERENCES GLACCS(ACCNO)
GLACCS_JOBCOST_HDR_SALES	Foreign Key	Table Constraint	FOREIGN KEY (SALESGL) REFERENCES GLACCS(ACCNO)
JOBCOST_PROJ_JOBCOST_HDR	Foreign Key	Table Constraint	FOREIGN KEY (PROJ_SEQNO) REFERENCES JOBCOST_PROJ(SEQNO)
JOB_CATEGORIES_JOBCOST_HDR	Foreign Key	Table Constraint	FOREIGN KEY (CATEGORY) REFERENCES JOB_CATEGORIES(CATNO)
JOB_STATUS_JOBCOST_HDR	Foreign Key	Table Constraint	FOREIGN KEY (STATUS) REFERENCES JOB_STATUS(STATUSKEY)
JOB_TYPES_JOBCOST_HDR	Foreign Key	Table Constraint	FOREIGN KEY (JOBTYPE) REFERENCES JOB_TYPES(TYPENO)
OPPORTUNITY_JOBCOST_HDR	Foreign Key	Table Constraint	FOREIGN KEY (OPPORTUNITY_SEQNO) REFERENCES OPPORTUNITY(SEQNO)
PK__JOBCOST_HDR__09003183	Primary Key	Table Constraint	PRIMARY KEY (JOBNO)
STAFF_JOBCOST_HDR	Foreign Key	Table Constraint	FOREIGN KEY (STAFFNO) REFERENCES STAFF(STAFFNO)
STAFF_JOBCOST_HDR_MGR	Foreign Key	Table Constraint	FOREIGN KEY (ACTIONBY) REFERENCES STAFF(STAFFNO)

Triggers:

Trigger name Code

AFTER_JOBCOST_HDR_INSERT CREATE TRIGGER [dbo].[AFTER_JOBCOST_HDR_INSERT] ON [dbo].[JOBCOST_HDR]
FOR INSERT
AS
SET NOCOUNT ON
DECLARE @JOBNO INTEGER
DECLARE @MASTER_JOBNO INTEGER
DECLARE @JOBCODE VARCHAR(15)
SELECT
@JOBNO=JOBNO,
@JOBCODE=JOBCODE,
@MASTER_JOBNO=MASTER_JOBNO
FROM
INSERTED
IF @JOBCODE IS NULL
UPDATE [dbo].[JOBCOST_HDR] SET JOBCODE = CAST(@JOBNO AS VARCHAR(15)) WHERE JOBNO=@JOBNO
IF (@MASTER_JOBNO IS NULL) OR (@MASTER_JOBNO IN (-1, 0))
UPDATE [dbo].[JOBCOST_HDR] SET MASTER_JOBNO = @JOBNO WHERE JOBNO=@JOBNO
SET NOCOUNT OFF
RETURN

Entity: JOBCOST_LINES

Comment	This table contains the details of all job quote lines.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the line.
QUOTE_QTY	No	FLOAT(53)	No	The quantity on the quote.
QUOTE_UNITPR	No	FLOAT(53)	No	The unit price on the line.
ACTUAL_UNITCOST	No	FLOAT(53)	No	The original unit cost on the line.
TRANSDATE	No	DATETIME	No	The transaction date on the line.
EXCHRATE	No	FLOAT(53)	No	The exchange rate applicable to the line.
DISCOUNT	No	FLOAT(53)	No	The percentage discount on the line.
UNITPRICE_INCTAX	No	FLOAT(53)	No	The unit price inclusive of tax.
JOBNO	No	INTEGER	Yes	The ID number of the job that this line is on.
SECTION	No	INTEGER	No
STOCKCODE	No	VARCHAR(23)	No	The stockcode of the stock item on this line.
DESCRIPTION	No	VARCHAR(60)	No	The description of the stock item on this line.
SHOW_ON_INVOICE	No	CHAR(1)	No
COST_CENTRE	No	INTEGER	No	The ID number of the Cost Type on the line (from JOB_COSTTYPES).
LINE_STATUS	No	VARCHAR(30)	No	The status on the job line. Will be one of: Q = quote line J = line has been committed and ready for invoice I = line has been invoiced P = line is a part of a Purchase Order
COST_CENTRE2	No	INTEGER	No	The ID number of the Cost Group on the line (from JOB_COSTGROUPS).
LINE_SOURCE	No	VARCHAR(10)	No
NARRATIVE	No	VARCHAR(1)	No
TAXNO	No	INTEGER	No	The ID number of the tax rate on the line (from TAX_RATES).
BRANCHNO	No	INTEGER	No	The ID number of the branch that the line belongs to (from BRANCHES).
SUBCODE	No	INTEGER	No	The subcode of the P&L revenue account.
ANALYSIS	No	INTEGER	No
CURRENCYNO	No	INTEGER	No	The ID number of the currency on the line (from CURRENCIES).
ALINENO	No	INTEGER	No	A rank ID that determines the order in which lines are displayed.
GLCODE	No	INTEGER	No	The GL account code of the P&L revenue account.
MASTER_JOBNO	No	INTEGER	No	If the line belongs to a sub-job, this field contains the ID of the master job.
COPY_FROM_QUOTE	No	CHAR(1)	Yes	The destination of quote lines. Will be one of: T = Timesheets tab P = Purchases tab C = Costs tab N = none
NARRATIVE_SEQNO	No	INTEGER	No	The ID number of the narrative on the line, if there is one (from NARRATIVES).
DIM_LENGTH	No	FLOAT(53)	Yes	Dimension length.
DIM_WIDTH	No	FLOAT(53)	Yes	Dimension width.
DIM_DEPTH	No	FLOAT(53)	Yes	Dimension depth.
TOTAL_QUANTITY	No	AS	No	The total quantity after considering the dimensions (if applicable).
PRICE_OVERRIDDEN	No	CHAR(1)	Yes	This field contains 'Y' if the unit price or discount has been overridden.
BOMTYPE	No	CHAR(1)	No	Will be one of: N = Normal stock item B = Build K = Kit L = Lookup item
BOMPRICING	No	CHAR(1)	No	Will be one of: N = normal C = pricing by component (BOMs) T= pricing by total (BOMs)
SHOWLINE	No	CHAR(1)	No	Used by reports to display/hide lines.
LINKEDSTATUS	No	CHAR(1)	No	S = Stocked, L = Lookup
LISTPRICE	No	FLOAT(53)	No	The list price of the stock item.
LINETYPE	No	INTEGER	Yes	Will be one of: 0 = normal 1 = BOM header 2 = COM component 4 = comment line
KITSEQNO	No	INTEGER	Yes	The ID number of the Bill of Materials, if the stock line belongs to a one.
KITCODE	No	VARCHAR(23)	No	The ID code of the Bill of Materials, if the stock line belongs to a one (from BILLOMAT_HDR).
LINKED_STOCKCODE	No	VARCHAR(23)	No	Linked stockcode.
LINKED_QTY	No	FLOAT(53)	No	Quantity (if the item is a linked stock item).
HIDDEN_COST	No	FLOAT(53)	Yes	This field applies to Bills of Materials. For kits that use pricing by components, this field stores header-level cost prices. For builds that use individual stock lines, this field stores the real cost.
HIDDEN_SELL	No	FLOAT(53)	Yes	This field applies to Bills of Materials. For kits that use pricing by components, this field stores header-level sell prices. For builds that use individual stock lines, this field stores the sell price.
SUPPLIERNO	No	INTEGER	No	ID of the supplier (Creditor) for the stock item on this line (from CR_ACCS).
FROMLOC	No	INTEGER	No
LINETOTAL	No	AS	No	Total value of the stock line.
NUNITPR	No	AS	No
BATCHCODE	No	VARCHAR(20)	No	The batch code on the line, if there is one.
OPTION_NO	No	INTEGER	Yes	The ID number of the quote option assigned to the line.
X_LABOUR_ALLOWANCE	No	FLOAT(53)	Yes
SPREADVALUE	No	CHAR(1)	Yes
TAXRATE	No	FLOAT(53)	Yes	The tax rate percentage on the line.
LINETOTAL_TAX	No	FLOAT(53)	Yes	The tax value on the line.
PREF_SERIALNO	No	VARCHAR(50)	No
LINE_TAX	No	FLOAT(53)	Yes
HIDDEN_LINETOTAL	No	FLOAT(53)	Yes
SCHEDULE_SEQNO	No	INTEGER	Yes	ID number of the Billing Schedule that applies to the line.
LINETOTAL_INCTAX	No	AS	No	Total value of the line, inclusive of tax.
JOBCOSTLINEID	No	INTEGER	No	ID of the job line.
SNTYPE	No	INTEGER	No	Serial number type (applicable only if the item is serialised).
SNEXPDAYS	No	INTEGER	No	Expiry days (applicable only if the item is serialised).
OPPLINEID	No	INTEGER	No	If the job was created from an Opportunity in EXO CRM, this field contains the ID of the Opportunity line that this line was created from.
COST_LINENO	No	INTEGER	Yes	This field stores the COST_LINENO from DR_INVLINES/DR_INVLINES_PARK.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
BILLOMAT_HDR_JOBCOST_LINES	Non Identifying	BILLOMAT_HDR	JOBCOST_LINES	Zero Or More
BRANCHES_JOBCOST_LINES	Non Identifying	BRANCHES	JOBCOST_LINES	Zero Or More
CR_ACCS_JOBCOST_LINES	Non Identifying	CR_ACCS	JOBCOST_LINES	Zero Or More
CURRENCIES_JOBCOST_LINES	Non Identifying	CURRENCIES	JOBCOST_LINES	Zero Or More
GLACCS_JOBCOST_LINES	Non Identifying	GLACCS	JOBCOST_LINES	Zero Or More
JOBCOST_HDR_JOBCOST_LINES	Non Identifying	JOBCOST_HDR	JOBCOST_LINES	Zero Or More
JOBCOST_HDR_JOBCOST_LINES_MASTER	Non Identifying	JOBCOST_HDR	JOBCOST_LINES	Zero Or More
JOBCOST_LINES_JOB_TRANSACTIONS	Non Identifying	JOBCOST_LINES	JOB_TRANSACTIONS	Zero Or More
JOB_CONTRACT_BILLINGS_JOBCOST_LINES	Non Identifying	JOB_CONTRACT_BILLINGS	JOBCOST_LINES	Zero Or More
JOB_COSTGROUPS_JOBCOST_LINES	Non Identifying	JOB_COSTGROUPS	JOBCOST_LINES	Zero Or More
JOB_COSTTYPES_JOBCOST_LINES	Non Identifying	JOB_COSTTYPES	JOBCOST_LINES	Zero Or More
JOB_QUOTE_OPTIONS_JOBCOST_LINES	Non Identifying	JOB_QUOTE_OPTIONS	JOBCOST_LINES	Zero Or More
NARRATIVES_JOBCOST_LINES	Non Identifying	NARRATIVES	JOBCOST_LINES	Zero Or More
STOCK_ITEMS_JOBCOST_LINES	Non Identifying	STOCK_ITEMS	JOBCOST_LINES	Zero Or More
STOCK_ITEMS_JOBCOST_LINES_DESC	Non Identifying	STOCK_ITEMS	JOBCOST_LINES	Zero Or More
TAX_RATES_JOBCOST_LINES	Non Identifying	TAX_RATES	JOBCOST_LINES	Zero Or More

Indexes:

Name	Index columns	Index type
SK_JC1	JOBNO ASC
SK_JC2	MASTER_JOBNO ASC
SK_JC3	COPY_FROM_QUOTE ASC

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (COST_LINENO)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (SCHEDULE_SEQNO)
	Not Null	Column Constraint	NOT NULL (HIDDEN_LINETOTAL)
	Not Null	Column Constraint	NOT NULL (LINE_TAX)
	Not Null	Column Constraint	NOT NULL (LINETOTAL_TAX)
	Not Null	Column Constraint	NOT NULL (JOBNO)
	Not Null	Column Constraint	NOT NULL (TAXRATE)
	Not Null	Column Constraint	NOT NULL (SPREADVALUE)
	Not Null	Column Constraint	NOT NULL (X_LABOUR_ALLOWANCE)
	Not Null	Column Constraint	NOT NULL (OPTION_NO)
	Not Null	Column Constraint	NOT NULL (HIDDEN_SELL)
	Not Null	Column Constraint	NOT NULL (HIDDEN_COST)
	Not Null	Column Constraint	NOT NULL (KITSEQNO)
	Not Null	Column Constraint	NOT NULL (LINETYPE)
	Not Null	Column Constraint	NOT NULL (PRICE_OVERRIDDEN)
	Not Null	Column Constraint	NOT NULL (COPY_FROM_QUOTE)
	Not Null	Column Constraint	NOT NULL (DIM_DEPTH)
	Not Null	Column Constraint	NOT NULL (DIM_LENGTH)
	Not Null	Column Constraint	NOT NULL (DIM_WIDTH)
BILLOMAT_HDR_JOBCOST_LINES	Foreign Key	Table Constraint	FOREIGN KEY (KITCODE) REFERENCES BILLOMAT_HDR(BILLCODE)
BRANCHES_JOBCOST_LINES	Foreign Key	Table Constraint	FOREIGN KEY (BRANCHNO) REFERENCES BRANCHES(BRANCHNO)
CR_ACCS_JOBCOST_LINES	Foreign Key	Table Constraint	FOREIGN KEY (SUPPLIERNO) REFERENCES CR_ACCS(ACCNO)
CURRENCIES_JOBCOST_LINES	Foreign Key	Table Constraint	FOREIGN KEY (CURRENCYNO) REFERENCES CURRENCIES(CURRENCYNO)
DF__JOBCOST_L__ALINE__1EEF72A2	Default	Column Constraint	ALINENO DEFAULT 0
DF__JOBCOST_L__ANALY__1D072A30	Default	Column Constraint	ANALYSIS DEFAULT 0
DF__JOBCOST_L__BOMPR__686944BF	Default	Column Constraint	BOMPRICING DEFAULT N
DF__JOBCOST_L__BOMTY__67752086	Default	Column Constraint	BOMTYPE DEFAULT N
DF__JOBCOST_L__BRANC__1B1EE1BE	Default	Column Constraint	BRANCHNO DEFAULT 0
DF__JOBCOST_L__COPY___3019FEA4	Default	Column Constraint	COPY_FROM_QUOTE DEFAULT N
DF__JOBCOST_L__COST___790B578A	Default	Column Constraint	COST_LINENO DEFAULT -1
DF__JOBCOST_L__CURRE__1DFB4E69	Default	Column Constraint	CURRENCYNO DEFAULT 0
DF__JOBCOST_L__DIM_D__3D3EEF98	Default	Column Constraint	DIM_DEPTH DEFAULT 1
DF__JOBCOST_L__DIM_L__3B56A726	Default	Column Constraint	DIM_LENGTH DEFAULT 1
DF__JOBCOST_L__DIM_W__3C4ACB5F	Default	Column Constraint	DIM_WIDTH DEFAULT 1
DF__JOBCOST_L__DISCO__174E50DA	Default	Column Constraint	DISCOUNT DEFAULT 0
DF__JOBCOST_L__EXCHR__165A2CA1	Default	Column Constraint	EXCHRATE DEFAULT 0
DF__JOBCOST_L__FROML__7E237BB4	Default	Column Constraint	FROMLOC DEFAULT 0
DF__JOBCOST_L__GLCOD__1FE396DB	Default	Column Constraint	GLCODE DEFAULT 0
DF__JOBCOST_L__HIDDE__635A76DF	Default	Column Constraint	HIDDEN_LINETOTAL DEFAULT 0
DF__JOBCOST_L__HIDDE__7B470F09	Default	Column Constraint	HIDDEN_COST DEFAULT 0
DF__JOBCOST_L__HIDDE__7C3B3342	Default	Column Constraint	HIDDEN_SELL DEFAULT 0
DF__JOBCOST_L__KITSE__795EC697	Default	Column Constraint	KITSEQNO DEFAULT -1
DF__JOBCOST_L__LINET__5F89E5FB	Default	Column Constraint	LINETOTAL_TAX DEFAULT 0
DF__JOBCOST_L__LINET__786AA25E	Default	Column Constraint	LINETYPE DEFAULT -1
DF__JOBCOST_L__LINE___61722E6D	Default	Column Constraint	LINE_TAX DEFAULT 0
DF__JOBCOST_L__LINKE__6A518D31	Default	Column Constraint	LINKEDSTATUS DEFAULT N
DF__JOBCOST_L__LINKE__7A52EAD0	Default	Column Constraint	LINKED_QTY DEFAULT 0
DF__JOBCOST_L__MASTE__20D7BB14	Default	Column Constraint	MASTER_JOBNO DEFAULT 0
DF__JOBCOST_L__NARRA__1936994C	Default	Column Constraint	NARRATIVE DEFAULT N
DF__JOBCOST_L__OPPLI__0E659BCC	Default	Column Constraint	OPPLINEID DEFAULT -1
DF__JOBCOST_L__OPTIO__57E8C433	Default	Column Constraint	OPTION_NO DEFAULT 0
DF__JOBCOST_L__PRICE__1F798287	Default	Column Constraint	PRICE_OVERRIDDEN DEFAULT N
DF__JOBCOST_L__SCHED__40313633	Default	Column Constraint	SCHEDULE_SEQNO DEFAULT 0
DF__JOBCOST_L__SHOWL__695D68F8	Default	Column Constraint	SHOWLINE DEFAULT Y
DF__JOBCOST_L__SNEXP__016AC80A	Default	Column Constraint	SNEXPDAYS DEFAULT -1
DF__JOBCOST_L__SNTYP__7F827F98	Default	Column Constraint	SNTYPE DEFAULT -1
DF__JOBCOST_L__SPREA__59D10CA5	Default	Column Constraint	SPREADVALUE DEFAULT Y
DF__JOBCOST_L__SUBCO__1C1305F7	Default	Column Constraint	SUBCODE DEFAULT 0
DF__JOBCOST_L__SUPPL__7D2F577B	Default	Column Constraint	SUPPLIERNO DEFAULT 0
DF__JOBCOST_L__TAXNO__1A2ABD85	Default	Column Constraint	TAXNO DEFAULT 0
DF__JOBCOST_L__TAXRA__5DA19D89	Default	Column Constraint	TAXRATE DEFAULT 0
DF__JOBCOST_L__TRANS__15660868	Default	Column Constraint	TRANSDATE DEFAULT getdate()
DF__JOBCOST_L__UNITP__18427513	Default	Column Constraint	UNITPRICE_INCTAX DEFAULT 0
DF__JOBCOST_L__X_LAB__58DCE86C	Default	Column Constraint	X_LABOUR_ALLOWANCE DEFAULT 0
GLACCS_JOBCOST_LINES	Foreign Key	Table Constraint	FOREIGN KEY (GLCODE) REFERENCES GLACCS(ACCNO)
JOBCOST_HDR_JOBCOST_LINES	Foreign Key	Table Constraint	FOREIGN KEY (JOBNO) REFERENCES JOBCOST_HDR(JOBNO)
JOBCOST_HDR_JOBCOST_LINES_MASTER	Foreign Key	Table Constraint	FOREIGN KEY (MASTER_JOBNO) REFERENCES JOBCOST_HDR(JOBNO)
JOB_CONTRACT_BILLINGS_JOBCOST_LINES	Foreign Key	Table Constraint	FOREIGN KEY (SCHEDULE_SEQNO) REFERENCES JOB_CONTRACT_BILLINGS(SEQNO)
JOB_COSTGROUPS_JOBCOST_LINES	Foreign Key	Table Constraint	FOREIGN KEY (COST_CENTRE2) REFERENCES JOB_COSTGROUPS(SEQNO)
JOB_COSTTYPES_JOBCOST_LINES	Foreign Key	Table Constraint	FOREIGN KEY (COST_CENTRE) REFERENCES JOB_COSTTYPES(SEQNO)
JOB_QUOTE_OPTIONS_JOBCOST_LINES	Foreign Key	Table Constraint	FOREIGN KEY (OPTION_NO) REFERENCES JOB_QUOTE_OPTIONS(SEQNO)
NARRATIVES_JOBCOST_LINES	Foreign Key	Table Constraint	FOREIGN KEY (NARRATIVE_SEQNO) REFERENCES NARRATIVES(SEQNO)
PK__JOBCOST_LINES__1471E42F	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)
STOCK_ITEMS_JOBCOST_LINES	Foreign Key	Table Constraint	FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE)
STOCK_ITEMS_JOBCOST_LINES_DESC	Foreign Key	Table Constraint	FOREIGN KEY (DESCRIPTION) REFERENCES STOCK_ITEMS(DESCRIPTION)
TAX_RATES_JOBCOST_LINES	Foreign Key	Table Constraint	FOREIGN KEY (TAXNO) REFERENCES TAX_RATES(SEQNO)

Entity: JOBCOST_PROJ

Comment	This table contains details of the projects that jobs can be grouped under.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the project.
PROJ_CODE	No	VARCHAR(15)	Yes	An alphanumerical ID code for the project.
PROJ_TITLE	No	VARCHAR(60)	No	A descriptive title for the project.
ISACTIVE	No	CHAR(1)	Yes	This field contains 'Y' if the project is currently active.
DESCRIPTION	No	TEXT	No	Descriptive text for the project.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
JOBCOST_PROJ_JOBCOST_HDR	Non Identifying	JOBCOST_PROJ	JOBCOST_HDR	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (ISACTIVE)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (PROJ_CODE)
DF__JOBCOST_P__ISACT__053B58EE	Default	Column Constraint	ISACTIVE DEFAULT Y
PK__JOBCOST_PROJ__044734B5	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)

Entity: JOBCOST_RESOURCE

Comment	This table contains details of all job resources.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the resource.
COSTRATE0	No	FLOAT(53)	No
COSTRATE1	No	FLOAT(53)	No
COSTRATE2	No	FLOAT(53)	No
COSTRATE3	No	FLOAT(53)	No
SELLRATE0	No	FLOAT(53)	No
SELLRATE1	No	FLOAT(53)	No
SELLRATE2	No	FLOAT(53)	No
SELLRATE3	No	FLOAT(53)	No
REVIEWDATE	No	DATETIME	No
NORMALHOURS	No	FLOAT(53)	No
STAFFNO	No	INTEGER	No	If the resource relates to an EXO Business staff member, their ID number is recorded here.
RESOURCENAME	No	VARCHAR(30)	Yes	The resource's name.
TITLE	No	VARCHAR(30)	No	The resource's title.
ISACTIVE	No	CHAR(1)	Yes	This field contains 'Y' if the resource is currently active.
DEFAULT_STOCKCODE	No	VARCHAR(23)	No	If the resource has a default stockcode, it is recorded here.
SHORTCODE	No	VARCHAR(3)	No	A short (max. three characters) ID code for the resource.
EMAIL_ADDRESS	No	VARCHAR(40)	No
FILTERSQL	No	VARCHAR(100)	No	If the resource uses a SQL statement to filter stock item searches for times, it is recorded here.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
JOBCOST_RESOURCE_JOB_RESOURCE_ALLOCATION	Non Identifying	JOBCOST_RESOURCE	JOB_RESOURCE_ALLOCATION	Zero Or More
STAFF_JOBCOST_RESOURCE	Non Identifying	STAFF	JOBCOST_RESOURCE	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (ISACTIVE)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (RESOURCENAME)
DF__JOBCOST_R__COSTR__23B427BF	Default	Column Constraint	COSTRATE0 DEFAULT 0
DF__JOBCOST_R__COSTR__24A84BF8	Default	Column Constraint	COSTRATE1 DEFAULT 0
DF__JOBCOST_R__COSTR__259C7031	Default	Column Constraint	COSTRATE2 DEFAULT 0
DF__JOBCOST_R__COSTR__2690946A	Default	Column Constraint	COSTRATE3 DEFAULT 0
DF__JOBCOST_R__ISACT__2C496DC0	Default	Column Constraint	ISACTIVE DEFAULT Y
DF__JOBCOST_R__NORMA__2B554987	Default	Column Constraint	NORMALHOURS DEFAULT 0
DF__JOBCOST_R__SELLR__2784B8A3	Default	Column Constraint	SELLRATE0 DEFAULT 0
DF__JOBCOST_R__SELLR__2878DCDC	Default	Column Constraint	SELLRATE1 DEFAULT 0
DF__JOBCOST_R__SELLR__296D0115	Default	Column Constraint	SELLRATE2 DEFAULT 0
DF__JOBCOST_R__SELLR__2A61254E	Default	Column Constraint	SELLRATE3 DEFAULT 0
PK__JOBCOST_RESOURCE__22C00386	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)
STAFF_JOBCOST_RESOURCE	Foreign Key	Table Constraint	FOREIGN KEY (STAFFNO) REFERENCES STAFF(STAFFNO)

Entity: JOB_CATEGORIES

Comment	This table contains details of all Job Categories.
Primary key columns	CATNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
CATNO	Yes	INTEGER	Yes	A unique ID number for the Job Category.
CATDESC	No	VARCHAR(40)	No	A descriptive name for the category.
DISP_COLOUR	No	VARCHAR(15)	No	Not used.
SHORTCODE	No	VARCHAR(3)	No	A short (max. three characters) ID code for the category.
GANTTBAR_COLOUR	No	INTEGER	No	A colour code that determines how jobs of this category should appear on the diagram on the Job Overview tab.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
JOB_CATEGORIES_JOBCOST_HDR	Non Identifying	JOB_CATEGORIES	JOBCOST_HDR	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (CATNO)
PK__JOB_CATEGORIES__62DA889B	Primary Key	Table Constraint	PRIMARY KEY (CATNO)

Entity: JOB_CONTRACT_BILLINGS

Comment	This table contains details of Progress Billing records on jobs.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the billing record.
JOBNO	No	INTEGER	No	The ID number of the job that the billing record is attached to.
DESCRIPTION	No	VARCHAR(60)	Yes	A descriptive name for the billing record.
DUE_DATE	No	DATETIME	No	The date that this billing entry is due on.
SUBTOTAL	No	FLOAT(53)	No	The subtotal on the billing record.
TAXTOTAL	No	FLOAT(53)	No	The total tax amount on the billing record.
RETENTION	No	FLOAT(53)	No	The base retention percentage for the line.
RETENTION_RATE	No	FLOAT(53)	No	The base retention rate for the line.
INV_SEQNO	No	INTEGER	No	If the line has been invoiced, this field contains the ID number (SEQNO) of the invoice in the DR_TRANS table.
INVNO	No	VARCHAR(20)	No	If the line has been invoiced, this field contains the invoice number for the invoice.
MASTER_JOBNO	No	INTEGER	Yes	If the job is a sub-job, this field contains the ID number of the master job.
CURRENCYNO	No	INTEGER	Yes	This field displays the ID number of the currency that the billing record is in.
EXCHRATE	No	FLOAT(53)	Yes	The exchange rate for the line at the time of the transaction.
RETENTION_DUE_DATE	No	DATETIME	No	The retention due date.
PROFORMA_NO	No	VARCHAR(20)	No
RETENTION_INV_SEQNO	No	INTEGER	Yes
RETENTION_INVNO	No	VARCHAR(20)	No
ALLOW_ALLOCATION	No	CHAR(1)	Yes
RETENTION2	No	FLOAT(53)	Yes	The Retention 2 rate for the line.
RETENTION2_RATE	No	FLOAT(53)	Yes	The Retention 2 percentage for the line.
RETENTION3	No	FLOAT(53)	Yes	The Retention 3 rate for the line.
RETENTION3_RATE	No	FLOAT(53)	Yes	The Retention 3 percentage for the line.
GLCODE	No	INTEGER	Yes	The ID number of the GL account for this line.
GLSUBCODE	No	INTEGER	Yes	The ID number of the GL sub-account for this line.
RETENTION_REALISED	No	FLOAT(53)	Yes	The retention amount realised on the line.
PERCENTAGE_COMPLETE	No	FLOAT(53)	Yes	The Percentage Complete amount for the line.
STOCKCODE	No	VARCHAR(23)	No	The stockcode for the billing record.
NARRATIVE_SEQNO	No	INTEGER	No	The ID number of the narrative on the line, if there is one.
INVOICED	No	CHAR(1)	Yes	This field contains 'Y' if the line has been invoiced.
ALLOCATED_COSTS	No	FLOAT(53)	Yes	The Allocated Costs amount on the line.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
CURRENCIES_JOB_CONTRACT_BILLINGS	Non Identifying	CURRENCIES	JOB_CONTRACT_BILLINGS	Zero Or More
GLACCS_JOB_CONTRACT_BILLINGS	Non Identifying	GLACCS	JOB_CONTRACT_BILLINGS	Zero Or More
GLACCS_JOB_CONTRACT_BILLINGS_SUB	Non Identifying	GLACCS	JOB_CONTRACT_BILLINGS	Zero Or More
JOBCOST_HDR_JOB_CONTRACT_BILLINGS	Non Identifying	JOBCOST_HDR	JOB_CONTRACT_BILLINGS	Zero Or More
JOBCOST_HDR_JOB_CONTRACT_BILLINGS_MASTER	Non Identifying	JOBCOST_HDR	JOB_CONTRACT_BILLINGS	Zero Or More
JOB_CONTRACT_BILLINGS_JOBCOST_LINES	Non Identifying	JOB_CONTRACT_BILLINGS	JOBCOST_LINES	Zero Or More
JOB_CONTRACT_BILLINGS_JOB_TRANSACTIONS	Non Identifying	JOB_CONTRACT_BILLINGS	JOB_TRANSACTIONS	Zero Or More
NARRATIVES_JOB_CONTRACT_BILLINGS	Non Identifying	NARRATIVES	JOB_CONTRACT_BILLINGS	Zero Or More
STOCK_ITEMS_JOB_CONTRACT_BILLINGS	Non Identifying	STOCK_ITEMS	JOB_CONTRACT_BILLINGS	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (ALLOCATED_COSTS)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (INVOICED)
	Not Null	Column Constraint	NOT NULL (DESCRIPTION)
	Not Null	Column Constraint	NOT NULL (MASTER_JOBNO)
	Not Null	Column Constraint	NOT NULL (PERCENTAGE_COMPLETE)
	Not Null	Column Constraint	NOT NULL (CURRENCYNO)
	Not Null	Column Constraint	NOT NULL (RETENTION_REALISED)
	Not Null	Column Constraint	NOT NULL (EXCHRATE)
	Not Null	Column Constraint	NOT NULL (GLSUBCODE)
	Not Null	Column Constraint	NOT NULL (RETENTION_INV_SEQNO)
	Not Null	Column Constraint	NOT NULL (GLCODE)
	Not Null	Column Constraint	NOT NULL (ALLOW_ALLOCATION)
	Not Null	Column Constraint	NOT NULL (RETENTION3_RATE)
	Not Null	Column Constraint	NOT NULL (RETENTION2)
	Not Null	Column Constraint	NOT NULL (RETENTION3)
	Not Null	Column Constraint	NOT NULL (RETENTION2_RATE)
CURRENCIES_JOB_CONTRACT_BILLINGS	Foreign Key	Table Constraint	FOREIGN KEY (CURRENCYNO) REFERENCES CURRENCIES(CURRENCYNO)
DF__JOB_CONTR__ALLOC__069A5CD2	Default	Column Constraint	ALLOCATED_COSTS DEFAULT 0
DF__JOB_CONTR__ALLOW__3F3D11FA	Default	Column Constraint	ALLOW_ALLOCATION DEFAULT Y
DF__JOB_CONTR__CURRE__3C60A54F	Default	Column Constraint	CURRENCYNO DEFAULT 0
DF__JOB_CONTR__EXCHR__3D54C988	Default	Column Constraint	EXCHRATE DEFAULT 1
DF__JOB_CONTR__GLCOD__2E9C95DD	Default	Column Constraint	GLCODE DEFAULT 0
DF__JOB_CONTR__GLSUB__2F90BA16	Default	Column Constraint	GLSUBCODE DEFAULT 0
DF__JOB_CONTR__INVOI__05A63899	Default	Column Constraint	INVOICED DEFAULT N
DF__JOB_CONTR__JOBNO__681F2BFC	Default	Column Constraint	JOBNO DEFAULT 0
DF__JOB_CONTR__MASTE__3B6C8116	Default	Column Constraint	MASTER_JOBNO DEFAULT 0
DF__JOB_CONTR__PERCE__48275DB6	Default	Column Constraint	PERCENTAGE_COMPLETE DEFAULT 0
DF__JOB_CONTR__RETEN__1605F23D	Default	Column Constraint	RETENTION2 DEFAULT 0
DF__JOB_CONTR__RETEN__16FA1676	Default	Column Constraint	RETENTION2_RATE DEFAULT 0
DF__JOB_CONTR__RETEN__17EE3AAF	Default	Column Constraint	RETENTION3 DEFAULT 0
DF__JOB_CONTR__RETEN__18E25EE8	Default	Column Constraint	RETENTION3_RATE DEFAULT 0
DF__JOB_CONTR__RETEN__3084DE4F	Default	Column Constraint	RETENTION_REALISED DEFAULT 0
DF__JOB_CONTR__RETEN__3E48EDC1	Default	Column Constraint	RETENTION_INV_SEQNO DEFAULT 0
GLACCS_JOB_CONTRACT_BILLINGS	Foreign Key	Table Constraint	FOREIGN KEY (GLCODE) REFERENCES GLACCS(ACCNO)
GLACCS_JOB_CONTRACT_BILLINGS_SUB	Foreign Key	Table Constraint	FOREIGN KEY (GLSUBCODE) REFERENCES GLACCS(ACCNO)
JOBCOST_HDR_JOB_CONTRACT_BILLINGS	Foreign Key	Table Constraint	FOREIGN KEY (JOBNO) REFERENCES JOBCOST_HDR(JOBNO)
JOBCOST_HDR_JOB_CONTRACT_BILLINGS_MASTER	Foreign Key	Table Constraint	FOREIGN KEY (MASTER_JOBNO) REFERENCES JOBCOST_HDR(JOBNO)
NARRATIVES_JOB_CONTRACT_BILLINGS	Foreign Key	Table Constraint	FOREIGN KEY (NARRATIVE_SEQNO) REFERENCES NARRATIVES(SEQNO)
PK__JOB_CONTRACT_BIL__672B07C3	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)
STOCK_ITEMS_JOB_CONTRACT_BILLINGS	Foreign Key	Table Constraint	FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE)

Entity: JOB_COSTGROUPS

Comment	This table records details of the Job Cost Groups.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the Cost Group.
DEF_MARKUP	No	FLOAT(53)	No	The default markup on the group.
DEF_OVERHEAD	No	FLOAT(53)	No	The default overhead on the group.
COSTDESC	No	VARCHAR(50)	No	A descriptive name for the group.
SHORTCODE	No	VARCHAR(3)	No	A short (max. three characters) code for the group.
SHOWONQUOTE	No	CHAR(1)	No	This field contains 'T' if the group's "Show on quote" option is ticked; otherwise 'F'.
CONSOLIDATE	No	CHAR(1)	No	This field contains 'T' if the group's "Consolidate" option is ticked; otherwise 'F'.
COPY_FROM_QUOTE	No	CHAR(1)	No	This field indicates what the group's "Copy from quote to:" option is set to. Will be one of: N = None C = Costs T = Timesheet P = Purchase

Relationships:

Name	Relationship type	Parent	Child	Cardinality
JOB_COSTGROUPS_INWARDS_GOODS_LINES	Non Identifying	JOB_COSTGROUPS	INWARDS_GOODS_LINES	Zero Or More
JOB_COSTGROUPS_JOBCOST_LINES	Non Identifying	JOB_COSTGROUPS	JOBCOST_LINES	Zero Or More
JOB_COSTGROUPS_JOB_COSTTYPES	Non Identifying	JOB_COSTGROUPS	JOB_COSTTYPES	Zero Or More
JOB_COSTGROUPS_JOB_TRANSACTIONS	Non Identifying	JOB_COSTGROUPS	JOB_TRANSACTIONS	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (SEQNO)
DF__JOB_COSTG__CONSO__6D58170E	Default	Column Constraint	CONSOLIDATE DEFAULT F
DF__JOB_COSTG__COPY___6E4C3B47	Default	Column Constraint	COPY_FROM_QUOTE DEFAULT F
DF__JOB_COSTG__SHOWO__6C63F2D5	Default	Column Constraint	SHOWONQUOTE DEFAULT F
PK__JOB_COSTGROUPS__6B6FCE9C	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)

Entity: JOB_COSTTYPES

Comment	This table records details of the Job Cost Types
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the Cost Type.
DEF_MARKUP	No	FLOAT(53)	No	The default markup percentage for the type.
DEF_OVERHEAD	No	FLOAT(53)	No	The default overhead percentage for the type.
COSTDESC	No	VARCHAR(50)	No	A descriptive name for the type.
GLCODE	No	INTEGER	No	The General Ledger code for the Sales Account related to this type.
GLSUBCODE	No	INTEGER	No	The General Ledger sub-account code for the Sales Account related to this type.
SHOWONQUOTE	No	CHAR(1)	No	This field contains 'T' if the type's "Show on quote" option is ticked; otherwise 'F'.
SHORTCODE	No	VARCHAR(3)	Yes	A short (max. three characters) code for the type
DEF_COSTGROUP	No	INTEGER	No	The ID number of the Cost Group that this type belongs to.
DEF_PURCH_GLCODE	No	INTEGER	No	The General Ledger code for the Purchasing Account related to this type.
DEF_PURCH_GLSUBCODE	No	INTEGER	No	The General Ledger sub-account code for the Purchasing Account related to this type.
CONSOLIDATE	No	CHAR(1)	No	This field contains 'T' if the type's "Consolidate" option is ticked; otherwise 'F'.
COPY_FROM_QUOTE	No	CHAR(1)	No	This field indicates what the group's "Copy from quote to:" option is set to. Will be one of: N = None C = Costs T = Timesheet P = Purchase

Relationships:

Name	Relationship type	Parent	Child	Cardinality
JOB_COSTGROUPS_JOB_COSTTYPES	Non Identifying	JOB_COSTGROUPS	JOB_COSTTYPES	Zero Or More
JOB_COSTTYPES_INWARDS_GOODS_LINES	Non Identifying	JOB_COSTTYPES	INWARDS_GOODS_LINES	Zero Or More
JOB_COSTTYPES_JOBCOST_LINES	Non Identifying	JOB_COSTTYPES	JOBCOST_LINES	Zero Or More
JOB_COSTTYPES_JOB_TRANSACTIONS	Non Identifying	JOB_COSTTYPES	JOB_TRANSACTIONS	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (SHORTCODE)
	Not Null	Column Constraint	NOT NULL (SEQNO)
DF__JOB_COSTT__CONSO__689361F1	Default	Column Constraint	CONSOLIDATE DEFAULT F
DF__JOB_COSTT__COPY___6987862A	Default	Column Constraint	COPY_FROM_QUOTE DEFAULT F
DF__JOB_COSTT__GLSUB__66AB197F	Default	Column Constraint	GLSUBCODE DEFAULT 0
DF__JOB_COSTT__SHOWO__679F3DB8	Default	Column Constraint	SHOWONQUOTE DEFAULT F
JOB_COSTGROUPS_JOB_COSTTYPES	Foreign Key	Table Constraint	FOREIGN KEY (DEF_COSTGROUP) REFERENCES JOB_COSTGROUPS(SEQNO)
PK__JOB_COSTTYPES__65B6F546	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)

Entity: JOB_HIST

Comment	This table contains details of the History Notes attached to jobs.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the History Note.
JOBNO	No	INTEGER	No	The ID number of the job that the History Note is attached to.
SUBJECT	No	VARCHAR(80)	No	The History Note's subject line.
NOTE	No	VARCHAR(4096)	No	The text of the History Note.
POSTTIME	No	DATETIME	No	The actual date and time that the record was posted.
TRANSDATE	No	DATETIME	No	The transaction date against the History Note.
SALESNO	No	INTEGER	No	The ID number of the EXO Business staff member who added the History Note.
OUTLOOK_LINK	No	VARCHAR(40)	No	If the History Note was emailed, this field contains a link to the email in MS Outlook.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
JOBCOST_HDR_JOB_HIST	Non Identifying	JOBCOST_HDR	JOB_HIST	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (SEQNO)
DF__JOB_HIST__POSTTI__4B03CA61	Default	Column Constraint	POSTTIME DEFAULT getdate()
DF__JOB_HIST__TRANSD__4BF7EE9A	Default	Column Constraint	TRANSDATE DEFAULT getdate()
JOBCOST_HDR_JOB_HIST	Foreign Key	Table Constraint	FOREIGN KEY (JOBNO) REFERENCES JOBCOST_HDR(JOBNO)
PK__JOB_HIST__4A0FA628	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)

Entity: JOB_OTHER_REPORTS

Comment	This is a legacy table that is no longer used.
Primary key columns	REPORTNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
REPORTNO	Yes	INTEGER	Yes	No longer used.
REPORTDESC	No	VARCHAR(40)	No	No longer used.
REPORT_PARAMS	No	VARCHAR(200)	No	No longer used.

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (REPORTNO)
PK__JOB_OTHER_REPORT__703483B9	Primary Key	Table Constraint	PRIMARY KEY (REPORTNO)

Entity: JOB_OUTPUT_ITEMS

Comment	This table records details of any items specified on the Output Items sub-tab of a job.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the item.
JOBNO	No	INTEGER	No	The ID number of the job that the item belongs to.
STOCKCODE	No	VARCHAR(23)	Yes	The stockcode of the output stock item.
DESCRIPTION	No	VARCHAR(60)	Yes	The description of the output stock item.
LOCATION	No	INTEGER	No	The location specified for the output item (from STOCK_LOCATIONS).
QUANTITY	No	FLOAT(53)	No	The quantity of the item that will be produced.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
JOBCOST_HDR_JOB_OUTPUT_ITEMS	Non Identifying	JOBCOST_HDR	JOB_OUTPUT_ITEMS	Zero Or More
STOCK_ITEMS_JOB_OUTPUT_ITEMS	Non Identifying	STOCK_ITEMS	JOB_OUTPUT_ITEMS	Zero Or More
STOCK_ITEMS_JOB_OUTPUT_ITEMS_DESC	Non Identifying	STOCK_ITEMS	JOB_OUTPUT_ITEMS	Zero Or More
STOCK_LOCATIONS_JOB_OUTPUT_ITEMS	Non Identifying	STOCK_LOCATIONS	JOB_OUTPUT_ITEMS	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (DESCRIPTION)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (STOCKCODE)
DF__JOB_OUTPU__JOBNO__032827F3	Default	Column Constraint	JOBNO DEFAULT 0
JOBCOST_HDR_JOB_OUTPUT_ITEMS	Foreign Key	Table Constraint	FOREIGN KEY (JOBNO) REFERENCES JOBCOST_HDR(JOBNO)
PK__JOB_OUTPUT_ITEMS__023403BA	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)
STOCK_ITEMS_JOB_OUTPUT_ITEMS	Foreign Key	Table Constraint	FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE)
STOCK_ITEMS_JOB_OUTPUT_ITEMS_DESC	Foreign Key	Table Constraint	FOREIGN KEY (DESCRIPTION) REFERENCES STOCK_ITEMS(DESCRIPTION)
STOCK_LOCATIONS_JOB_OUTPUT_ITEMS	Foreign Key	Table Constraint	FOREIGN KEY (LOCATION) REFERENCES STOCK_LOCATIONS(LOCNO)

Entity: JOB_QUOTE_OPTIONS

Comment	This table records details of the Quote Options available for job quotes.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the Quote Option.
JOBNO	No	INTEGER	No	The ID number of the job that this option applies to.
OPTION_NO	No	INTEGER	No	A job-specific ID number for the option.
OPTION_NAME	No	VARCHAR(30)	No	The option's name.
OPTION_SELECTED	No	CHAR(1)	Yes	This field contains 'Y' if the option is currently selected for the job.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
JOB_QUOTE_OPTIONS_JOBCOST_LINES	Non Identifying	JOB_QUOTE_OPTIONS	JOBCOST_LINES	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (OPTION_SELECTED)
	Not Null	Column Constraint	NOT NULL (SEQNO)
DF__JOB_QUOTE__JOBNO__550C5788	Default	Column Constraint	JOBNO DEFAULT 0
DF__JOB_QUOTE__OPTIO__56007BC1	Default	Column Constraint	OPTION_NO DEFAULT 0
DF__JOB_QUOTE__OPTIO__56F49FFA	Default	Column Constraint	OPTION_SELECTED DEFAULT N
PK__JOB_QUOTE_OPTION__5418334F	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)

Entity: JOB_RESOURCE_ALLOCATION

Comment	This table records all allocations of resources to jobs.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the allocation.
RESOURCE_SEQNO	No	INTEGER	Yes	The ID number of the resource being allocated.
JOBNO	No	INTEGER	Yes	The ID number of the job that the resource is allocated to.
SUBJECT_NOTES	No	VARCHAR(255)	No	Any notes entered into the allocation's Subject field.
LOCATION	No	INTEGER	No	The Location code entered for the allocation.
START_DATE	No	DATETIME	No	The allocation's start date.
START_TIME	No	DATETIME	No	The allocation's start time.
END_DATE	No	DATETIME	No	The allocation's end date.
END_TIME	No	DATETIME	No	The allocation's end time.
TOTAL_HOURS	No	FLOAT(53)	No	The total number of hours that the resource was allocated for.
APPOINTMENT_SCHEDULED	No	CHAR(1)	Yes	This field contains 'Y' if an appointment has been scheduled for the allocation.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
JOBCOST_HDR_JOB_RESOURCE_ALLOCATION	Non Identifying	JOBCOST_HDR	JOB_RESOURCE_ALLOCATION	Zero Or More
JOBCOST_RESOURCE_JOB_RESOURCE_ALLOCATION	Non Identifying	JOBCOST_RESOURCE	JOB_RESOURCE_ALLOCATION	Zero Or More
JOB_RESOURCE_ALLOCATION_TASKS	Non Identifying	JOB_RESOURCE_ALLOCATION	TASKS	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (APPOINTMENT_SCHEDULED)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (RESOURCE_SEQNO)
	Not Null	Column Constraint	NOT NULL (JOBNO)
DF__JOB_RESOU__APPOI__1F846F7F	Default	Column Constraint	APPOINTMENT_SCHEDULED DEFAULT N
JOBCOST_HDR_JOB_RESOURCE_ALLOCATION	Foreign Key	Table Constraint	FOREIGN KEY (JOBNO) REFERENCES JOBCOST_HDR(JOBNO)
JOBCOST_RESOURCE_JOB_RESOURCE_ALLOCATION	Foreign Key	Table Constraint	FOREIGN KEY (RESOURCE_SEQNO) REFERENCES JOBCOST_RESOURCE(SEQNO)
PK__JOB_RESOURCE_ALL__1E904B46	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)

Entity: JOB_RETENTION_LEVELS

Comment	This table records the default retention levels used by EXO Job Costing.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number of the line.
RTN_2_MIN	No	FLOAT(53)	No	The Minimum Value for the Level 2 Retention.
RTN_2_RATE	No	FLOAT(53)	No	The % Rate for the Level 2 Retention.
RTN_3_MIN	No	FLOAT(53)	No	The Minimum Value for the Level 3 Retention.
RTN_3_RATE	No	FLOAT(53)	No	The % Rate for the Level 3 Retention.

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (SEQNO)
PK__JOB_RETENTION_LE__1ACAA75A	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)

Entity: JOB_STATUS

Comment	This table contains details of Job Statuses.
Primary key columns	STATUSKEY

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
STATUSKEY	Yes	VARCHAR(1)	Yes	A unique one letter ID code for the Job Status.
STATUSDESC	No	VARCHAR(30)	Yes	A descriptive name for the status.
ADMIN_STAT	No	VARCHAR(1)	No	If this field contains 'Y', the status prevents entry of new transactions.
LOCK_JOB	No	VARCHAR(1)	No	If this field contains 'Y', the status makes the job read-only.
ISARCHIVED	No	CHAR(1)	Yes	If this field contains 'Y', the status causes the job to be archived.
ISCOMPLETE	No	CHAR(1)	Yes	If this field contains 'Y', the status causes the job to be marked as Complete.
ISACTIVE	No	CHAR(1)	Yes	If this field contains 'Y', the status causes the job to be marked as Active.
ISINVOICEREADY	No	CHAR(1)	Yes	If this field contains 'Y', the status causes the job to be marked as Ready for Invoice.
ISLOCKQUOTE	No	CHAR(1)	Yes	If this field contains 'Y', the status causes quotations on the job to be read-only.
WORKFLOW_CONSTRAINED	No	CHAR(1)	Yes	If this field contains 'Y', workflow constraints (defined in the JOB_STATUS_CONSTRAINT table) apply to the status.
GANTTBAR_COLOUR	No	INTEGER	No	A colour code that determines how jobs of this status should appear on the diagram on the Job Overview tab.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
JOB_STATUS_JOBCOST_HDR	Non Identifying	JOB_STATUS	JOBCOST_HDR	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (WORKFLOW_CONSTRAINED)
	Not Null	Column Constraint	NOT NULL (STATUSKEY)
	Not Null	Column Constraint	NOT NULL (STATUSDESC)
	Not Null	Column Constraint	NOT NULL (ISLOCKQUOTE)
	Not Null	Column Constraint	NOT NULL (ISARCHIVED)
	Not Null	Column Constraint	NOT NULL (ISINVOICEREADY)
	Not Null	Column Constraint	NOT NULL (ISCOMPLETE)
	Not Null	Column Constraint	NOT NULL (ISACTIVE)
DF__JOB_STATU__ISACT__02E830D1	Default	Column Constraint	ISACTIVE DEFAULT N
DF__JOB_STATU__ISARC__00FFE85F	Default	Column Constraint	ISARCHIVED DEFAULT N
DF__JOB_STATU__ISCOM__01F40C98	Default	Column Constraint	ISCOMPLETE DEFAULT N
DF__JOB_STATU__ISINV__03DC550A	Default	Column Constraint	ISINVOICEREADY DEFAULT N
DF__JOB_STATU__ISLOC__04D07943	Default	Column Constraint	ISLOCKQUOTE DEFAULT N
DF__JOB_STATU__LOCK___052FA09F	Default	Column Constraint	LOCK_JOB DEFAULT N
DF__JOB_STATU__WORKF__3A785CDD	Default	Column Constraint	WORKFLOW_CONSTRAINED DEFAULT N
PK__JOB_STATUS__043B7C66	Primary Key	Table Constraint	PRIMARY KEY (STATUSKEY)

Entity: JOB_STATUS_CONSTRAINT

Comment	This table contains details of job workflow constraints.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the workflow constraint.
ISACTIVE	No	CHAR(1)	Yes	If this field contains 'Y', the constraint applies, so that users are permitted to change a job from the stage specified by the FROM_STATUS to the stage specified by the TO_STATUS.
FROM_STATUS	No	CHAR(1)	No	The source stage for the constraint.
TO_STATUS	No	CHAR(1)	No	The destination stage for the constraint.
DESCRIPTION	No	VARCHAR(50)	Yes	A description of the constraint.
SHORTDESC	No	VARCHAR(5)	Yes	A short code (5 characters max) for the constraint.
TRACKEVENT	No	CHAR(1)	No	If this field contains 'Y', when a job is changed from the FROM_STATUS to the TO_STATUS, the stage change is recorded in the Event Log.

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (SHORTDESC)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (ISACTIVE)
	Not Null	Column Constraint	NOT NULL (DESCRIPTION)
DF__JOB_STATU__ISACT__5DC1991A	Default	Column Constraint	ISACTIVE DEFAULT N
PK__JOB_STATUS_CONST__5CCD74E1	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)

Entity: JOB_TIMESHEETS

Comment	This table records the timesheet lines entered in the EXO Job Costing Timesheets add-on.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the timesheet line.
STAFFNO	No	INTEGER	No	The ID number of the staff member on the timesheet line.
JOBNO	No	INTEGER	No	The ID number of the job that the line relates to.
TITLE	No	VARCHAR(60)	No	The title of the job that the line relates to.
STOCKCODE	No	VARCHAR(23)	Yes	The stockcode of the labour pay item for this line.
DESCRIPTION	No	VARCHAR(60)	No	The description of the labour pay item for this line.
UNITPRICE	No	FLOAT(53)	No
WEEK_START_DATE	No	DATETIME	No	The start date of the week that times are being entered for.
DAY1	No	FLOAT(53)	No	The number of hours entered for day 1.
DAY1_POSTED	No	CHAR(1)	No	This field contain 'Y' day's times have been approved.
DAY1_NARRATIVE	No	INTEGER	No	If a narrative has been entered for the day, its ID number is recorded here.
DAY2	No	FLOAT(53)	No	The number of hours entered for day 2.
DAY2_POSTED	No	CHAR(1)	No	This field contain 'Y' day's times have been approved.
DAY2_NARRATIVE	No	INTEGER	No	If a narrative has been entered for the day, its ID number is recorded here.
DAY3	No	FLOAT(53)	No	The number of hours entered for day 3.
DAY3_POSTED	No	CHAR(1)	No	This field contain 'Y' day's times have been approved.
DAY3_NARRATIVE	No	INTEGER	No	If a narrative has been entered for the day, its ID number is recorded here.
DAY4	No	FLOAT(53)	No	The number of hours entered for day 4.
DAY4_POSTED	No	CHAR(1)	No	This field contain 'Y' day's times have been approved.
DAY4_NARRATIVE	No	INTEGER	No	If a narrative has been entered for the day, its ID number is recorded here.
DAY5	No	FLOAT(53)	No	The number of hours entered for day 5.
DAY5_POSTED	No	CHAR(1)	No	This field contain 'Y' day's times have been approved.
DAY5_NARRATIVE	No	INTEGER	No	If a narrative has been entered for the day, its ID number is recorded here.
DAY6	No	FLOAT(53)	No	The number of hours entered for day 6.
DAY6_POSTED	No	CHAR(1)	No	This field contain 'Y' day's times have been approved.
DAY6_NARRATIVE	No	INTEGER	No	If a narrative has been entered for the day, its ID number is recorded here.
DAY7	No	FLOAT(53)	No	The number of hours entered for day 7.
DAY7_POSTED	No	CHAR(1)	No	This field contain 'Y' day's times have been approved.
DAY7_NARRATIVE	No	INTEGER	No	If a narrative has been entered for the day, its ID number is recorded here.
IS_OVERTIME	No	CHAR(1)	No	This field contains 'Y' if the line is a Special Hours rate.
LINE_ID	No	INTEGER	No
RATE_SEQNO	No	INTEGER	No	The ID number of the Timesheet Rate in the line.
RATE_FACTOR	No	FLOAT(53)	No
COST_GROUP	No	INTEGER	No
COST_TYPE	No	INTEGER	No
LABOUR_ALLOWANCE	No	FLOAT(53)	No
HAS_ALLOWANCE	No	CHAR(1)	No	This field contains 'Y' if allowances/deductions have been added to the line.
SOURCE_REF	No	VARCHAR(20)	No

Relationships:

Name	Relationship type	Parent	Child	Cardinality
JOBCOST_HDR_JOB_TIMESHEETS	Non Identifying	JOBCOST_HDR	JOB_TIMESHEETS	Zero Or More
JOBCOST_HDR_JOB_TIMESHEETS_TITLE	Non Identifying	JOBCOST_HDR	JOB_TIMESHEETS	Zero Or More
JOB_TIMESHEETS_RATES_JOB_TIMESHEETS	Non Identifying	JOB_TIMESHEETS_RATES	JOB_TIMESHEETS	Zero Or More
STAFF_JOB_TIMESHEETS	Non Identifying	STAFF	JOB_TIMESHEETS	Zero Or More
STOCK_ITEMS_JOB_TIMESHEETS	Non Identifying	STOCK_ITEMS	JOB_TIMESHEETS	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (STOCKCODE)
	Not Null	Column Constraint	NOT NULL (SEQNO)
DF__JOB_TIMES__JOBNO__1C68D709	Default	Column Constraint	JOBNO DEFAULT 0
DF__JOB_TIMES__LINE___1D5CFB42	Default	Column Constraint	LINE_ID DEFAULT 0
DF__JOB_TIMES__RATE___1E511F7B	Default	Column Constraint	RATE_SEQNO DEFAULT 0
DF__JOB_TIMES__STAFF__1B74B2D0	Default	Column Constraint	STAFFNO DEFAULT 0
JOBCOST_HDR_JOB_TIMESHEETS	Foreign Key	Table Constraint	FOREIGN KEY (JOBNO) REFERENCES JOBCOST_HDR(JOBNO)
JOBCOST_HDR_JOB_TIMESHEETS_TITLE	Foreign Key	Table Constraint	FOREIGN KEY (TITLE) REFERENCES JOBCOST_HDR(TITLE)
JOB_TIMESHEETS_RATES_JOB_TIMESHEETS	Foreign Key	Table Constraint	FOREIGN KEY (RATE_SEQNO) REFERENCES JOB_TIMESHEETS_RATES(SEQNO)
PK__JOB_TIMESHEETS__1A808E97	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)
STAFF_JOB_TIMESHEETS	Foreign Key	Table Constraint	FOREIGN KEY (STAFFNO) REFERENCES STAFF(STAFFNO)
STOCK_ITEMS_JOB_TIMESHEETS	Foreign Key	Table Constraint	FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE)

Entity: JOB_TIMESHEETS_RATES

Comment	This table records all Timesheet Rates used by the EXO Job Costing Timesheets add-on.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the Timesheet Rate.
SHORTCODE	No	VARCHAR(5)	No	A short ID code for the rate.
RATENAME	No	VARCHAR(30)	No	The rate's name.
COSTRATE	No	FLOAT(53)	No	The factor by which the cost of transactions entered with this rate type will be multiplied when posting to a job.
SELLRATE	No	FLOAT(53)	No	The factor by which the charge-out rate of transactions entered with this rate type will be multiplied when posting to a job.
PAYROLLRATE	No	FLOAT(53)	No	The factor by which the pay rate of transactions exported via a timecard data file to your payroll base rate may be multiplied, e.g. where an employee is to be paid time-and-a-half or double time.
RATECOLOR	No	INTEGER	No	A colour code that determines how timesheet lines of this rate should appear on the EXO Job Costing Timesheets window.
PAYROLL_ALLOWANCE_ID	No	VARCHAR(5)	No	An ID number for mapping Allowances and Deductions with those defined in your payroll via your timecard data file.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
JOB_TIMESHEETS_RATES_JOB_TIMESHEETS	Non Identifying	JOB_TIMESHEETS_RATES	JOB_TIMESHEETS	Zero Or More
JOB_TIMESHEETS_RATES_JOB_TRANSACTIONS	Non Identifying	JOB_TIMESHEETS_RATES	JOB_TRANSACTIONS	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (SEQNO)
PK__JOB_TIMESHEETS_R__203967ED	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)

Entity: JOB_TIMESHEET_ALLOWANCE

Comment	This table contains details of all allowances added to timesheet lines.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the line.
STAFFNO	No	INTEGER	No	The ID number of the staff member that the line relates to.
WEEK_START_DATE	No	DATETIME	No	The start date of the week that the line belongs to.
ALLOWANCE_ID	No	VARCHAR(5)	No	The short ID code of the allowance type on this line.
DESCRIPTION	No	VARCHAR(50)	No	The description of the allowance type on this line.
UNIT	No	FLOAT(53)	No	The unit of the allowance (unit type, e.g. hours, KM, is determined by the allowance type).

Relationships:

Name	Relationship type	Parent	Child	Cardinality
JOB_TIMESHEET_ALLOWANCE_TYPES_JOB_TIMESHEET_ALLOWANCE	Non Identifying	JOB_TIMESHEET_ALLOWANCE_TYPES	JOB_TIMESHEET_ALLOWANCE	Zero Or More
JOB_TIMESHEET_ALLOWANCE_TYPES_JOB_TIMESHEET_ALLOWANCE_DESC	Non Identifying	JOB_TIMESHEET_ALLOWANCE_TYPES	JOB_TIMESHEET_ALLOWANCE	Zero Or More
STAFF_JOB_TIMESHEET_ALLOWANCE	Non Identifying	STAFF	JOB_TIMESHEET_ALLOWANCE	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (SEQNO)
JOB_TIMESHEET_ALLOWANCE_TYPES_JOB_TIMESHEET_ALLOWANCE	Foreign Key	Table Constraint	FOREIGN KEY (ALLOWANCE_ID) REFERENCES JOB_TIMESHEET_ALLOWANCE_TYPES(ALLOWANCE_ID)
JOB_TIMESHEET_ALLOWANCE_TYPES_JOB_TIMESHEET_ALLOWANCE_DESC	Foreign Key	Table Constraint	FOREIGN KEY (DESCRIPTION) REFERENCES JOB_TIMESHEET_ALLOWANCE_TYPES(DESCRIPTION)
PK__JOB_TIMESHEET_AL__2221B05F	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)
STAFF_JOB_TIMESHEET_ALLOWANCE	Foreign Key	Table Constraint	FOREIGN KEY (STAFFNO) REFERENCES STAFF(STAFFNO)

Entity: JOB_TIMESHEET_ALLOWANCE_TYPES

Comment	This table records all Allowance Types used by the EXO Job Costing Timesheets add-on.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the Allowance Type.
ALLOWANCE_ID	No	VARCHAR(5)	No	A short ID code for the type.
ALLOWANCE_FACTOR	No	INTEGER	No
DESCRIPTION	No	VARCHAR(50)	No	Descriptive text for the Allowance Type.
UNIT_OF_MEASURE	No	VARCHAR(50)	No	The unit of measure that the type uses. Will be HOURS, KILOMETERS or CURRENCY.
PAYROLL_ALLOWANCE_ID	No	VARCHAR(5)	No	An ID number for mapping Allowances and Deductions with those defined in your payroll via your timecard data file.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
JOB_TIMESHEET_ALLOWANCE_TYPES_JOB_TIMESHEET_ALLOWANCE	Non Identifying	JOB_TIMESHEET_ALLOWANCE_TYPES	JOB_TIMESHEET_ALLOWANCE	Zero Or More
JOB_TIMESHEET_ALLOWANCE_TYPES_JOB_TIMESHEET_ALLOWANCE_DESC	Non Identifying	JOB_TIMESHEET_ALLOWANCE_TYPES	JOB_TIMESHEET_ALLOWANCE	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (SEQNO)
PK__JOB_TIMESHEET_AL__2409F8D1	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)

Entity: JOB_TRANSACTIONS

Comment	This table contains line-level information on job transactions.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the line.
EXCHRATE	No	FLOAT(53)	No	The exchange rate applicable to the line.
DISCOUNT	No	FLOAT(53)	No	The percentage discount on the line.
UNITPRICE_INCTAX	No	FLOAT(53)	No	The unit price inclusive of tax.
FX_PRICE	No	FLOAT(53)	No
RETENTION_RATE	No	FLOAT(53)	No	The retention rate on the line, if applicable.
RETENTION_AMOUNT	No	FLOAT(53)	No	The retention amount on the line, if applicable.
OVERHEAD	No	FLOAT(53)	No	The overhead amount on the line, if applicable.
QUANTITY	No	FLOAT(53)	No	The quantity on the line.
UNITPRICE	No	FLOAT(53)	No	The unit price of the item on the line.
UNITCOST	No	FLOAT(53)	No	The unit cost of the item on the line.
ENDDATE	No	DATETIME	No	The end date on the line (applies to timesheets).
TRANSDATE	No	DATETIME	No	For timesheets, this field identifies the start date; otherwise it contains the transaction date on the line.
SOURCE_SEQNO	No	INTEGER	Yes	If the line originated from a job quote, this field contains the ID of the original quote line (from JOBCOST_LINES).
JOBNO	No	INTEGER	No	The ID number of the job that this line is on.
MASTER_JOBNO	No	INTEGER	No	If the line belongs to a sub-job, this field contains the ID of the master job.
TRANSTYPE	No	VARCHAR(1)	No	The transaction type. Will be one of: T = Time C = Cost
LINE_STATUS	No	VARCHAR(30)	No	The line's status. Will be one of: R = Ready to invoice W = Writeoff I = Invoiced X = Cancelled A = Asset C = Committed Stock
STOCKCODE	No	VARCHAR(23)	No	The stockcode of the stock item on this line.
DESCRIPTION	No	VARCHAR(60)	No	The description of the stock item on this line.
COST_TYPE	No	INTEGER	No	The ID number of the Cost Type on the line (from JOB_COSTTYPES).
COST_GROUP	No	INTEGER	No	The ID number of the Cost Group on the line (from JOB_COSTGROUPS).
LINE_SOURCE	No	VARCHAR(10)	No	Where the line was sourced from. Will be one of: P = Purchase Q = Quote
SOURCE_ID	No	INTEGER	No	If the line originated from a job quote, this field contains the ID of the original quote line (from JOBCOST_LINES).
NARRATIVE	No	VARCHAR(1)	No
STAFFNO	No	INTEGER	No	ID number of the staff member on the line.
STARTTIME	No	VARCHAR(15)	No	Start time (only applies to timesheets).
ENDTIME	No	VARCHAR(15)	No	End Time (only applies to timesheets).
FROMLOC	No	INTEGER	No	The location origin for stock movements.
LOCATION	No	INTEGER	No	The location that the item moves to.
GLCODE	No	INTEGER	No	The GL account code of the P&L revenue account.
BRANCHNO	No	INTEGER	No	The ID number of the branch that the line belongs to (from BRANCHES).
SUBCODE	No	INTEGER	No	The subcode of the P&L revenue account.
LINESORT	No	INTEGER	No
TAXNO	No	INTEGER	No	The ID number of the tax rate on the line (from TAX_RATES).
ANALYSIS	No	INTEGER	No
CURRENCYNO	No	INTEGER	No	The ID number of the currency on the line (from CURRENCIES).
BILLING_ID	No	INTEGER	No	The Billing ID, if one exists.
BILLING_REF	No	VARCHAR(20)	No	The Billing Reference, if one exists.
INVOICED	No	FLOAT(53)	Yes	The amount invoiced.
INVOICEDQTY	No	FLOAT(53)	Yes	The quantity invoiced.
ALINENO	No	INTEGER	Yes	A rank ID that determines the order in which lines are displayed.
INVOICEDATE	No	DATETIME	No	The invoice date
NARRATIVE_SEQNO	No	INTEGER	No	The ID number of the narrative on the line, if there is one (from NARRATIVES).
DIM_LENGTH	No	FLOAT(53)	Yes	Dimension length
DIM_WIDTH	No	FLOAT(53)	Yes	Dimension width
DIM_DEPTH	No	FLOAT(53)	Yes	Dimension depth
TOTAL_QUANTITY	No	AS	No	The total quantity after considering the dimensions (if applicable).
PRICE_OVERRIDDEN	No	CHAR(1)	Yes	This field contains 'Y' if the unit price or discount has been overridden.
NUNITPR	No	AS	No	The unit price after discount.
LINETYPE	No	INTEGER	Yes	The type of line. Will be one of: 0 = Normal 1 = BOM header 2 = Component 4 = Comment line
KITSEQNO	No	INTEGER	Yes	The ID number of the Bill of Materials, if the stock line belongs to a one.
KITCODE	No	VARCHAR(23)	No	The ID code of the Bill of Materials, if the stock line belongs to a one (from BILLOMAT_HDR).
LINKED_STOCKCODE	No	VARCHAR(23)	No	Code of the linked stock item
LINKED_QTY	No	FLOAT(53)	No	Quantity (if the item is a linked stock item).
JOBLINEID	No	INTEGER	Yes	ID of the job transaction line
BOMTYPE	No	CHAR(1)	No	Will be one of: N = Normal stock item B = Build K = Kit L = Lookup item
BOMPRICING	No	CHAR(1)	No	Will be one of: N = normal C = pricing by component (BOMs) T= pricing by total (BOMs)
SHOWLINE	No	CHAR(1)	No	Flag to indicate if the BOM item line should be printed on Clarity reports or not.
LINKEDSTATUS	No	CHAR(1)	No	S= Stocked, L= Lookup, N= Not Applicable
LISTPRICE	No	FLOAT(53)	No	The original sell price of the item.
HIDDEN_COST	No	FLOAT(53)	Yes	This field applies to Bills of Materials. For kits that use pricing by components, this field stores header-level cost prices. For builds that use individual stock lines, this field stores the real cost.
HIDDEN_SELL	No	FLOAT(53)	Yes	This field applies to Bills of Materials. For kits that use pricing by components, this field stores header-level sell prices. For builds that use individual stock lines, this field stores the sell price.
LINECOST	No	AS	No	Calculated field giving the cost of the transactions line.
LINECHARGE	No	AS	No	Calculated field giving the total price of the transaction line (excluding discount).
LINETOTAL	No	AS	No	Total price of the transaction line including discount.
SOURCE_REF	No	VARCHAR(20)	No	Source reference.
BATCHCODE	No	VARCHAR(20)	No	Batch code.
SPREADVALUE	No	CHAR(1)	Yes	If this field contains 'Y', the line will be taken into account when using the spread function to alter the total.
TAXRATE	No	FLOAT(53)	Yes	The tax rate percentage on the line.
LINETOTAL_TAX	No	FLOAT(53)	Yes	The total tax value for line
LINE_TAX	No	FLOAT(53)	Yes	The tax value on the line.
HIDDEN_LINETOTAL	No	FLOAT(53)	Yes	For Kit type BOMs, this field stores the line total against the header line.
PO_LINESEQNO	No	INTEGER	Yes	The related Purchase Order line ID, if applicable (from PURCHORD_LINES).
INVSEQNO	No	INTEGER	Yes	The related Debtor Invoice ID, if applicable (from DR_TRANS).
INVLINE_SEQNO	No	INTEGER	Yes	The related Debtor Invoice line ID, if applicable (from DR_INVLINES).
SCHEDULE_SEQNO	No	INTEGER	Yes	The related Billing Schedule ID, if the line is allocated to a Billing Schedule (from JOB_CONTRACT_BILLINGS).
WIP_OUT_DATE	No	DATETIME	No	The date and time when the stock moved out of WIP.
ALLOWANCE	No	FLOAT(53)	Yes	Unit labour allowance associated with the stock item.
PROGRESSINVOICE	No	CHAR(1)	Yes	This field contains 'Y' if the line is allocated to a Billing Schedule.
RATE_SEQNO	No	INTEGER	Yes	Timesheet Rate ID from JOB_TIMESHEETS_RATES)
LINETOTAL_INCTAX	No	AS	No	Line total inclusive of discount and tax(Calculated field)
PAYROLL_STATUS	No	CHAR(1)	Yes	Only applicable when timesheets are integrated with MYOB EXO Payroll. Will be one of: Y = the line is ready to invoice N = the line is not ready to post P = the line has been invoiced
PAYROLL_HOURS	No	FLOAT(53)	Yes	Payroll hours (only applicable when timesheets are integrated with MYOB EXO Payroll).
PAYRATE_NO	No	INTEGER	Yes	Pay Rate ID (only applicable when timesheets are integrated with MYOB EXO Payroll).
PAYRATE	No	FLOAT(53)	Yes	Pay Rate percentage (only applicable when timesheets are integrated with MYOB EXO Payroll).
PAYRATE_OVERRIDEN	No	FLOAT(53)	Yes	This field contains 'Y' if the payrate has been modified; otherwise 'N' (only applicable when timesheets are integrated with MYOB EXO Payroll).
WAGE_CODE	No	INTEGER	Yes	Wage code (only applicable when timesheets are integrated with MYOB EXO Payroll).
COST_CENTRE	No	BIGINT	Yes	Cost Centre ID (only applicable when timesheets are integrated with MYOB EXO Payroll).
ISSUPPLIED	No	CHAR(1)	Yes	This field contains 'Y' if the line is supplied; otherwise 'N'.
SNTYPE	No	INTEGER	No	Serial number type.
SNEXPDAYS	No	INTEGER	No	Serial Number expiry days.
SU_SEQNO	No	INTEGER	Yes	The ID number of the Serviceable Unit that the line is associated with, if applicable.
STDCOST_IN	No	FLOAT(53)	No	The Standard cost of the item coming into the WIP location.
STDCOST_OUT	No	FLOAT(53)	No	The Standard cost of the item going out of the WIP location.
AVECOST_IN	No	FLOAT(53)	No	The Average cost of the item coming into the WIP location.
AVECOST_OUT	No	FLOAT(53)	No	The Average cost of the item going out of the WIP location
LATESTCOST_IN	No	FLOAT(53)	No	The Latest cost of the item coming into the WIP location.
LATESTCOST_OUT	No	FLOAT(53)	No	The Latest cost of the item going out of the WIP location.
LOOKUP_RECOVERABLE	No	CHAR(1)	No	This field contains 'Y' if lookups are recoverable against expenses in Job Costing.
COST_LINENO	No	INTEGER	Yes	This field stores the COST_LINENO from DR_INVLINES.
LINECHARGE_WRITEOFF	No	FLOAT(53)	No	This field stores the value of LINECHARGE before the line was written off.
CREDIT_SCHEDULE_SEQNO	No	INTEGER	Yes	This field stores the ID number of the Billing Schedule record that the current record was credited in.
WIP_IN_PERIOD_SEQNO	No	INTEGER	Yes	PERIOD_SEQNO for WIP_IN_DATE
WIP_OUT_PERIOD_SEQNO	No	INTEGER	Yes	PERIOD_SEQNO for WIP_OUT_DATE
STOCK_TRANS_SEQ_IN	No	INTEGER	Yes	This field stores the STOCK_TRANS.SEQNO from the Purchase Order line that inserted the job transaction.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
BILLOMAT_HDR_JOB_TRANSACTIONS	Non Identifying	BILLOMAT_HDR	JOB_TRANSACTIONS	Zero Or More
BRANCHES_JOB_TRANSACTIONS	Non Identifying	BRANCHES	JOB_TRANSACTIONS	Zero Or More
CURRENCIES_JOB_TRANSACTIONS	Non Identifying	CURRENCIES	JOB_TRANSACTIONS	Zero Or More
DR_INVLINES_JOB_TRANSACTIONS	Non Identifying	DR_INVLINES	JOB_TRANSACTIONS	Zero Or More
DR_TRANS_JOB_TRANSACTIONS	Non Identifying	DR_TRANS	JOB_TRANSACTIONS	Zero Or More
GLACCS_JOB_TRANSACTIONS	Non Identifying	GLACCS	JOB_TRANSACTIONS	Zero Or More
JOBCOST_HDR_JOB_TRANSACTIONS	Non Identifying	JOBCOST_HDR	JOB_TRANSACTIONS	Zero Or More
JOBCOST_HDR_JOB_TRANSACTIONS_MASTER	Non Identifying	JOBCOST_HDR	JOB_TRANSACTIONS	Zero Or More
JOBCOST_LINES_JOB_TRANSACTIONS	Non Identifying	JOBCOST_LINES	JOB_TRANSACTIONS	Zero Or More
JOB_CONTRACT_BILLINGS_JOB_TRANSACTIONS	Non Identifying	JOB_CONTRACT_BILLINGS	JOB_TRANSACTIONS	Zero Or More
JOB_COSTGROUPS_JOB_TRANSACTIONS	Non Identifying	JOB_COSTGROUPS	JOB_TRANSACTIONS	Zero Or More
JOB_COSTTYPES_JOB_TRANSACTIONS	Non Identifying	JOB_COSTTYPES	JOB_TRANSACTIONS	Zero Or More
JOB_TIMESHEETS_RATES_JOB_TRANSACTIONS	Non Identifying	JOB_TIMESHEETS_RATES	JOB_TRANSACTIONS	Zero Or More
NARRATIVES_JOB_TRANSACTIONS	Non Identifying	NARRATIVES	JOB_TRANSACTIONS	Zero Or More
PURCHORD_LINES_JOB_TRANSACTIONS	Non Identifying	PURCHORD_LINES	JOB_TRANSACTIONS	Zero Or More
STAFF_JOB_TRANSACTIONS	Non Identifying	STAFF	JOB_TRANSACTIONS	Zero Or More
STOCK_ITEMS_JOB_TRANSACTIONS	Non Identifying	STOCK_ITEMS	JOB_TRANSACTIONS	Zero Or More
STOCK_ITEMS_JOB_TRANSACTIONS_DESC	Non Identifying	STOCK_ITEMS	JOB_TRANSACTIONS	Zero Or More
TAX_RATES_JOB_TRANSACTIONS	Non Identifying	TAX_RATES	JOB_TRANSACTIONS	Zero Or More

Indexes:

Name	Index columns	Index type
SK_JC4	JOBNO ASC
SK_JC5	LINE_STATUS ASC
WIP_IN_PERIOD_SEQNO_INDEX	WIP_IN_PERIOD_SEQNO ASC
WIP_OUT_PERIOD_SEQNO_INDEX	WIP_OUT_PERIOD_SEQNO ASC

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (STOCK_TRANS_SEQ_IN)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (WIP_OUT_PERIOD_SEQNO)
	Not Null	Column Constraint	NOT NULL (WIP_IN_PERIOD_SEQNO)
	Not Null	Column Constraint	NOT NULL (CREDIT_SCHEDULE_SEQNO)
	Not Null	Column Constraint	NOT NULL (COST_LINENO)
	Not Null	Column Constraint	NOT NULL (SU_SEQNO)
	Not Null	Column Constraint	NOT NULL (ISSUPPLIED)
	Not Null	Column Constraint	NOT NULL (COST_CENTRE)
	Not Null	Column Constraint	NOT NULL (WAGE_CODE)
	Not Null	Column Constraint	NOT NULL (SOURCE_SEQNO)
	Not Null	Column Constraint	NOT NULL (PAYRATE_OVERRIDEN)
	Not Null	Column Constraint	NOT NULL (PAYRATE)
	Not Null	Column Constraint	NOT NULL (PAYRATE_NO)
	Not Null	Column Constraint	NOT NULL (PAYROLL_HOURS)
	Not Null	Column Constraint	NOT NULL (PAYROLL_STATUS)
	Not Null	Column Constraint	NOT NULL (RATE_SEQNO)
	Not Null	Column Constraint	NOT NULL (PROGRESSINVOICE)
	Not Null	Column Constraint	NOT NULL (ALLOWANCE)
	Not Null	Column Constraint	NOT NULL (SCHEDULE_SEQNO)
	Not Null	Column Constraint	NOT NULL (INVOICED)
	Not Null	Column Constraint	NOT NULL (INVLINE_SEQNO)
	Not Null	Column Constraint	NOT NULL (INVOICEDQTY)
	Not Null	Column Constraint	NOT NULL (INVSEQNO)
	Not Null	Column Constraint	NOT NULL (ALINENO)
	Not Null	Column Constraint	NOT NULL (PO_LINESEQNO)
	Not Null	Column Constraint	NOT NULL (DIM_LENGTH)
	Not Null	Column Constraint	NOT NULL (HIDDEN_LINETOTAL)
	Not Null	Column Constraint	NOT NULL (DIM_WIDTH)
	Not Null	Column Constraint	NOT NULL (LINE_TAX)
	Not Null	Column Constraint	NOT NULL (DIM_DEPTH)
	Not Null	Column Constraint	NOT NULL (LINETOTAL_TAX)
	Not Null	Column Constraint	NOT NULL (PRICE_OVERRIDDEN)
	Not Null	Column Constraint	NOT NULL (TAXRATE)
	Not Null	Column Constraint	NOT NULL (LINETYPE)
	Not Null	Column Constraint	NOT NULL (SPREADVALUE)
	Not Null	Column Constraint	NOT NULL (KITSEQNO)
	Not Null	Column Constraint	NOT NULL (HIDDEN_SELL)
	Not Null	Column Constraint	NOT NULL (HIDDEN_COST)
	Not Null	Column Constraint	NOT NULL (JOBLINEID)
BILLOMAT_HDR_JOB_TRANSACTIONS	Foreign Key	Table Constraint	FOREIGN KEY (KITCODE) REFERENCES BILLOMAT_HDR(BILLCODE)
BRANCHES_JOB_TRANSACTIONS	Foreign Key	Table Constraint	FOREIGN KEY (BRANCHNO) REFERENCES BRANCHES(BRANCHNO)
CURRENCIES_JOB_TRANSACTIONS	Foreign Key	Table Constraint	FOREIGN KEY (CURRENCYNO) REFERENCES CURRENCIES(CURRENCYNO)
DF__JOB_TRANS__ALINE__32F66B4F	Default	Column Constraint	ALINENO DEFAULT 0
DF__JOB_TRANS__ALLOW__4CEC12D3	Default	Column Constraint	ALLOWANCE DEFAULT 0
DF__JOB_TRANS__ANALY__015F0FBB	Default	Column Constraint	ANALYSIS DEFAULT 0
DF__JOB_TRANS__BOMPR__6498B3DB	Default	Column Constraint	BOMPRICING DEFAULT N
DF__JOB_TRANS__BOMTY__63A48FA2	Default	Column Constraint	BOMTYPE DEFAULT N
DF__JOB_TRANS__BRANC__7D8E7ED7	Default	Column Constraint	BRANCHNO DEFAULT 0
DF__JOB_TRANS__COST___0882A544	Default	Column Constraint	COST_LINENO DEFAULT -1
DF__JOB_TRANS__COST___342B5201	Default	Column Constraint	COST_CENTRE DEFAULT -1
DF__JOB_TRANS__CREDI__01A09D8B	Default	Column Constraint	CREDIT_SCHEDULE_SEQNO DEFAULT -1
DF__JOB_TRANS__CURRE__025333F4	Default	Column Constraint	CURRENCYNO DEFAULT 0
DF__JOB_TRANS__DIM_D__3A6282ED	Default	Column Constraint	DIM_DEPTH DEFAULT 1
DF__JOB_TRANS__DIM_L__387A3A7B	Default	Column Constraint	DIM_LENGTH DEFAULT 1
DF__JOB_TRANS__DIM_W__396E5EB4	Default	Column Constraint	DIM_WIDTH DEFAULT 1
DF__JOB_TRANS__DISCO__7405149D	Default	Column Constraint	DISCOUNT DEFAULT 0
DF__JOB_TRANS__ENDDA__78C9C9BA	Default	Column Constraint	ENDDATE DEFAULT getdate()
DF__JOB_TRANS__EXCHR__7310F064	Default	Column Constraint	EXCHRATE DEFAULT 1
DF__JOB_TRANS__FROML__7AB2122C	Default	Column Constraint	FROMLOC DEFAULT 0
DF__JOB_TRANS__FX_PR__75ED5D0F	Default	Column Constraint	FX_PRICE DEFAULT 0
DF__JOB_TRANS__GLCOD__7C9A5A9E	Default	Column Constraint	GLCODE DEFAULT 0
DF__JOB_TRANS__HIDDE__626652A6	Default	Column Constraint	HIDDEN_LINETOTAL DEFAULT 0
DF__JOB_TRANS__HIDDE__768259EC	Default	Column Constraint	HIDDEN_COST DEFAULT 0
DF__JOB_TRANS__HIDDE__77767E25	Default	Column Constraint	HIDDEN_SELL DEFAULT 0
DF__JOB_TRANS__INVLI__6A07746E	Default	Column Constraint	INVLINE_SEQNO DEFAULT -1
DF__JOB_TRANS__INVOI__310E22DD	Default	Column Constraint	INVOICED DEFAULT 0
DF__JOB_TRANS__INVOI__32024716	Default	Column Constraint	INVOICEDQTY DEFAULT 0
DF__JOB_TRANS__INVSE__69135035	Default	Column Constraint	INVSEQNO DEFAULT -1
DF__JOB_TRANS__ISSUP__3707BEAC	Default	Column Constraint	ISSUPPLIED DEFAULT N
DF__JOB_TRANS__JOBLI__34E9A0B9	Default	Column Constraint	JOBLINEID DEFAULT -1
DF__JOB_TRANS__KITSE__090B1E7B	Default	Column Constraint	KITSEQNO DEFAULT -1
DF__JOB_TRANS__LINEC__0B5F11EF	Default	Column Constraint	LINECHARGE_WRITEOFF DEFAULT 0
DF__JOB_TRANS__LINES__7F76C749	Default	Column Constraint	LINESORT DEFAULT 0
DF__JOB_TRANS__LINET__0816FA42	Default	Column Constraint	LINETYPE DEFAULT -1
DF__JOB_TRANS__LINET__5E95C1C2	Default	Column Constraint	LINETOTAL_TAX DEFAULT 0
DF__JOB_TRANS__LINE___607E0A34	Default	Column Constraint	LINE_TAX DEFAULT 0
DF__JOB_TRANS__LINKE__09FF42B4	Default	Column Constraint	LINKED_QTY DEFAULT 0
DF__JOB_TRANS__LINKE__6680FC4D	Default	Column Constraint	LINKEDSTATUS DEFAULT N
DF__JOB_TRANS__LOCAT__7BA63665	Default	Column Constraint	LOCATION DEFAULT 0
DF__JOB_TRANS__LOOKU__078E810B	Default	Column Constraint	LOOKUP_RECOVERABLE DEFAULT Y
DF__JOB_TRANS__PAYRA__305AC11D	Default	Column Constraint	PAYRATE_NO DEFAULT -1
DF__JOB_TRANS__PAYRA__314EE556	Default	Column Constraint	PAYRATE DEFAULT -1
DF__JOB_TRANS__PAYRA__3243098F	Default	Column Constraint	PAYRATE_OVERRIDEN DEFAULT -1
DF__JOB_TRANS__PAYRO__2E7278AB	Default	Column Constraint	PAYROLL_STATUS DEFAULT N
DF__JOB_TRANS__PAYRO__2F669CE4	Default	Column Constraint	PAYROLL_HOURS DEFAULT 0
DF__JOB_TRANS__PO_LI__644E9B18	Default	Column Constraint	PO_LINESEQNO DEFAULT -1
DF__JOB_TRANS__PRICE__206DA6C0	Default	Column Constraint	PRICE_OVERRIDDEN DEFAULT N
DF__JOB_TRANS__PROGR__4B8DDA46	Default	Column Constraint	PROGRESSINVOICE DEFAULT N
DF__JOB_TRANS__RATE___24FE1D0A	Default	Column Constraint	RATE_SEQNO DEFAULT 0
DF__JOB_TRANS__RETEN__76E18148	Default	Column Constraint	RETENTION_RATE DEFAULT 0
DF__JOB_TRANS__RETEN__77D5A581	Default	Column Constraint	RETENTION_AMOUNT DEFAULT 0
DF__JOB_TRANS__SCHED__41255A6C	Default	Column Constraint	SCHEDULE_SEQNO DEFAULT 0
DF__JOB_TRANS__SHOWL__658CD814	Default	Column Constraint	SHOWLINE DEFAULT Y
DF__JOB_TRANS__SNEXP__025EEC43	Default	Column Constraint	SNEXPDAYS DEFAULT -1
DF__JOB_TRANS__SNTYP__0076A3D1	Default	Column Constraint	SNTYPE DEFAULT -1
DF__JOB_TRANS__SPREA__5AC530DE	Default	Column Constraint	SPREADVALUE DEFAULT Y
DF__JOB_TRANS__STOCK__12B61AF4	Default	Column Constraint	STOCK_TRANS_SEQ_IN DEFAULT -1
DF__JOB_TRANS__SUBCO__7E82A310	Default	Column Constraint	SUBCODE DEFAULT 0
DF__JOB_TRANS__SU_SE__3025B6F3	Default	Column Constraint	SU_SEQNO DEFAULT -1
DF__JOB_TRANS__TAXNO__006AEB82	Default	Column Constraint	TAXNO DEFAULT 0
DF__JOB_TRANS__TAXRA__5CAD7950	Default	Column Constraint	TAXRATE DEFAULT 0
DF__JOB_TRANS__TRANS__79BDEDF3	Default	Column Constraint	TRANSDATE DEFAULT getdate()
DF__JOB_TRANS__UNITP__74F938D6	Default	Column Constraint	UNITPRICE_INCTAX DEFAULT 0
DF__JOB_TRANS__WAGE___33372DC8	Default	Column Constraint	WAGE_CODE DEFAULT -1
DF__JOB_TRANS__WIP_I__066552A8	Default	Column Constraint	WIP_IN_PERIOD_SEQNO DEFAULT -1
DF__JOB_TRANS__WIP_O__075976E1	Default	Column Constraint	WIP_OUT_PERIOD_SEQNO DEFAULT -1
DR_INVLINES_JOB_TRANSACTIONS	Foreign Key	Table Constraint	FOREIGN KEY (INVLINE_SEQNO) REFERENCES DR_INVLINES(SEQNO)
DR_TRANS_JOB_TRANSACTIONS	Foreign Key	Table Constraint	FOREIGN KEY (INVSEQNO) REFERENCES DR_TRANS(SEQNO)
GLACCS_JOB_TRANSACTIONS	Foreign Key	Table Constraint	FOREIGN KEY (GLCODE) REFERENCES GLACCS(ACCNO)
JOBCOST_HDR_JOB_TRANSACTIONS	Foreign Key	Table Constraint	FOREIGN KEY (JOBNO) REFERENCES JOBCOST_HDR(JOBNO)
JOBCOST_HDR_JOB_TRANSACTIONS_MASTER	Foreign Key	Table Constraint	FOREIGN KEY (MASTER_JOBNO) REFERENCES JOBCOST_HDR(JOBNO)
JOBCOST_LINES_JOB_TRANSACTIONS	Foreign Key	Table Constraint	FOREIGN KEY (SOURCE_SEQNO) REFERENCES JOBCOST_LINES(SEQNO)
JOB_CONTRACT_BILLINGS_JOB_TRANSACTIONS	Foreign Key	Table Constraint	FOREIGN KEY (SCHEDULE_SEQNO) REFERENCES JOB_CONTRACT_BILLINGS(SEQNO)
JOB_COSTGROUPS_JOB_TRANSACTIONS	Foreign Key	Table Constraint	FOREIGN KEY (COST_GROUP) REFERENCES JOB_COSTGROUPS(SEQNO)
JOB_COSTTYPES_JOB_TRANSACTIONS	Foreign Key	Table Constraint	FOREIGN KEY (COST_TYPE) REFERENCES JOB_COSTTYPES(SEQNO)
JOB_TIMESHEETS_RATES_JOB_TRANSACTIONS	Foreign Key	Table Constraint	FOREIGN KEY (RATE_SEQNO) REFERENCES JOB_TIMESHEETS_RATES(SEQNO)
NARRATIVES_JOB_TRANSACTIONS	Foreign Key	Table Constraint	FOREIGN KEY (NARRATIVE_SEQNO) REFERENCES NARRATIVES(SEQNO)
PK__JOB_TRANSACTIONS__721CCC2B	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)
PURCHORD_LINES_JOB_TRANSACTIONS	Foreign Key	Table Constraint	FOREIGN KEY (PO_LINESEQNO) REFERENCES PURCHORD_LINES(SEQNO)
STAFF_JOB_TRANSACTIONS	Foreign Key	Table Constraint	FOREIGN KEY (STAFFNO) REFERENCES STAFF(STAFFNO)
STOCK_ITEMS_JOB_TRANSACTIONS	Foreign Key	Table Constraint	FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE)
STOCK_ITEMS_JOB_TRANSACTIONS_DESC	Foreign Key	Table Constraint	FOREIGN KEY (DESCRIPTION) REFERENCES STOCK_ITEMS(DESCRIPTION)
TAX_RATES_JOB_TRANSACTIONS	Foreign Key	Table Constraint	FOREIGN KEY (TAXNO) REFERENCES TAX_RATES(SEQNO)

Triggers:

Trigger name	Code
JOB_STOCK_OUT_TO_WIP	CREATE TRIGGER [dbo].[JOB_STOCK_OUT_TO_WIP] ON [dbo].[JOB_TRANSACTIONS] FOR INSERT AS BEGIN SET NOCOUNT ON DECLARE @WIPLOC INT, @DEFLOC INT, @STOCKCODE VARCHAR(23), @TOTAL_QUANTITY FLOAT, @NUNITPR FLOAT, @LOCATION INT, @JOBNO INT, @SEQNO INT, @TRANSDATE DATETIME EXECUTE GET_PROFILE_VALUE_INT 'JOBDEFWIPLOC', @WIPLOC OUTPUT SELECT @STOCKCODE=STOCKCODE, @TOTAL_QUANTITY=TOTAL_QUANTITY, @NUNITPR=NUNITPR, @LOCATION=LOCATION, @JOBNO=JOBNO, @SEQNO=SEQNO, @TRANSDATE=TRANSDATE FROM INSERTED IF (@STOCKCODE IS NOT NULL) AND (@STOCKCODE <> '') AND (@WIPLOC=0) AND (@LOCATION<>@WIPLOC) BEGIN INSERT INTO STOCK_TRANS (STOCKCODE, QUANTITY, REF1, REF2, UNITPRICE, LOCATION, TOLOCATION, FROM_LEDGER, JOBNO, LINE_SEQNO, TRANSDATE, TRANSTYPE) VALUES (@STOCKCODE, -@TOTAL_QUANTITY, 'JOBCOST', 'T/FER OUT TO WIP', @NUNITPR, @LOCATION, @WIPLOC, 'j', @JOBNO, @SEQNO, @TRANSDATE, 2) END SET NOCOUNT OFF END
JOB_TRANSACTIONS_INSERT	-- ============================================= -- Description: This is a Insert trigger for [dbo].[JOB_TRANSACTIONS]. It intercepts the inserts to -- [dbo].[JOB_TRANSACTIONS] to set the AVECOST, STDCOST and LATESTCOST at the time of inserting -- ============================================= CREATE TRIGGER [dbo].[JOB_TRANSACTIONS_INSERT] ON [dbo].[JOB_TRANSACTIONS] FOR INSERT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @JT_SEQNO INT SELECT @JT_SEQNO = SEQNO FROM INSERTED UPDATE J SET J.AVECOST_IN = S.AVECOST, J.STDCOST_IN = S.STDCOST, J.LATESTCOST_IN = S.LATESTCOST, J.LOOKUP_RECOVERABLE = CASE WHEN S.[STATUS] = 'S' THEN 'Y' WHEN (S.[STATUS] = 'L' AND (ISNULL(LTRIM(S.UPDATEITEM_CODE), '') <> '')) OR (S.STOCKCODE = '@') THEN 'N' ELSE S.LOOKUP_RECOVERABLE END FROM [dbo].[JOB_TRANSACTIONS] J JOIN STOCK_ITEMS S ON S.STOCKCODE=J.STOCKCODE WHERE J.SEQNO = @JT_SEQNO END

Entity: JOB_TYPES

Comment	This table contains details of the available Job Types.
Primary key columns	TYPENO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
TYPENO	Yes	INTEGER	Yes	A unique ID number for the Job Type.
TYPEDESC	No	VARCHAR(40)	No	Descriptive text for the type.
SHORTCODE	No	VARCHAR(3)	No	A short (max. three characters) ID code for the type.
GANTTBAR_COLOUR	No	INTEGER	No	A colour code that determines how jobs of this type should appear on the diagram on the Job Overview tab.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
JOB_TYPES_JOBCOST_HDR	Non Identifying	JOB_TYPES	JOBCOST_HDR	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (TYPENO)
PK__JOB_TYPES__0717E911	Primary Key	Table Constraint	PRIMARY KEY (TYPENO)

Entity: NARRATIVES

Comment	This table contains the narratives attached to transactions.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the narrative.
NARRATIVE	No	VARCHAR(4096)	No	The text of the narrative.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
NARRATIVES_CR_INVLINES	Non Identifying	NARRATIVES	CR_INVLINES	Zero Or More
NARRATIVES_CR_TRANS	Non Identifying	NARRATIVES	CR_TRANS	Zero Or More
NARRATIVES_DR_INVLINES	Non Identifying	NARRATIVES	DR_INVLINES	Zero Or More
NARRATIVES_DR_TRANS	Non Identifying	NARRATIVES	DR_TRANS	Zero Or More
NARRATIVES_JOBCOST_LINES	Non Identifying	NARRATIVES	JOBCOST_LINES	Zero Or More
NARRATIVES_JOB_CONTRACT_BILLINGS	Non Identifying	NARRATIVES	JOB_CONTRACT_BILLINGS	Zero Or More
NARRATIVES_JOB_TRANSACTIONS	Non Identifying	NARRATIVES	JOB_TRANSACTIONS	Zero Or More
NARRATIVES_OPPORTUNITY_QUOTE	Non Identifying	NARRATIVES	OPPORTUNITY_QUOTE	Zero Or More
NARRATIVES_PURCHORD_HDR	Non Identifying	NARRATIVES	PURCHORD_HDR	Zero Or More
NARRATIVES_SALESORD_HDR	Non Identifying	NARRATIVES	SALESORD_HDR	Zero Or More
NARRATIVES_STOCK_REQUESTLINES	Non Identifying	NARRATIVES	STOCK_REQUESTLINES	Zero Or More
NARRATIVES_STOCK_REQUESTS	Non Identifying	NARRATIVES	STOCK_REQUESTS	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (SEQNO)
PK__NARRATIVES__2E31B632	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)

Entity: STAFF

Comment	This table stores details of all EXO Business staff members. These are the people who are authorised to log in to and use the EXO Business system.
Primary key columns	STAFFNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
STAFFNO	Yes	INTEGER	Yes	A unique identifier for the staff record.
NAME	No	VARCHAR(30)	No	The staff member's full name.
JOBTITLE	No	VARCHAR(30)	No	The staff member's job title.
EXTENSION	No	VARCHAR(12)	No	The staff member's telephone extension.
PHONE	No	VARCHAR(30)	No	The staff member's work telephone number.
HOMEPHONE	No	VARCHAR(30)	No	The staff member's home telephone number.
ISACTIVE	No	CHAR(1)	No	This field is set to 'Y' if the staff member is currently active, i.e. if their Active flag is ticked in EXO Business Configurator.
APP_PASSWORD	No	VARCHAR(30)	No	The staff member's password, used when logging in to the EXO Business system. Passwords are stored in an encrypted format.
MENU_NO	No	INTEGER	No	The ID number of the Menu Definition specified for the staff member. Corresponds to the menu's SEQNO field in the MENU_COLLECTION table.
AUTH_AMT	No	FLOAT(53)	No	The staff member's Credit Invoice Authorisation Limit.
STOCK_AUTH_AMT	No	FLOAT(53)	No	The staff member's Stock Purchase Order Authorisation Limit.
NON_STOCK_AUTH_AMT	No	FLOAT(53)	No	The staff member's Non-Stock Purchase Order Authorisation Limit.
SECURITYPROFILEID	No	INTEGER	Yes	The ID number of the staff member's Security profile. Corresponds to the profile's ID field in the PROFILE table.
USERPROFILEID	No	INTEGER	Yes	The ID number of the staff member's User profile. Corresponds to the profile's ID field in the PROFILE table.
LOGINID	No	VARCHAR(30)	Yes	The staff member's login ID. This is the ID used to log in to the EXO Business system.
PASSWORD_CHANGED	No	DATETIME	Yes	The date and time when the staff member's password was last changed.
LAST_BAD_LOGIN	No	DATETIME	No	The date and time when the staff member last attempted to log in but failed.
BAD_LOGIN_COUNT	No	INTEGER	Yes	The number of times the staff member has attempted to log in but failed.
LAST_LOGIN	No	DATETIME	No	The date and time when the staff member last successfully logged in.
ACCOUNT_STATUS	No	INTEGER	Yes	The staff member's account status. Will be one of: 0 = Account OK 1 = Account Locked 2 = Password Expired
EMAIL_ADDRESS	No	VARCHAR(50)	No	The staff member's email address.
DISCOUNTRATE	No	FLOAT(53)	Yes	The staff member's Discount Rate Limit.
PAYROLL_ID	No	VARCHAR(15)	No	The staff member's Employee ID (Payroll) number. This field is used when EXO Business is integrating with an external payroll system, e.g. MYOB EXO Payroll. It must contain the employee's corresponding ID number in the payroll system.
IS_SUPERVISOR	No	CHAR(1)	Yes	This field is set to 'Y' if the staff member is designated as a supervisor, i.e. if their Has supervisory override authority flag is ticked.
NICKNAME	No	VARCHAR(15)	No	A short nickname for the employee.
ABSENT	No	CHAR(1)	Yes	This field is set to 'Y' if the staff member is currently absent, i.e. if their Absent flag is ticked in EXO Business Configurator.
EMPLOYEE_CODE	No	INTEGER	Yes	This field is used by the payroll integration functionality.
SMTP_SEQNO	No	INTEGER	No	Where emails are sent via SMTP using the EXO Business Email Sender, this field contains the ID of the SMTP settings that apply to the staff member (SMTP_ACCOUNT.SEQNO). Set up via the SMTP Server Settings window.
HAS_BUDGETS	No	CHAR(1)	No	This field is set to 'Y' if the staff member has been assigned a Sales Team Budget, i.e. if their Has Budget flag is ticked in EXO Business Configurator.
REPORTS_TO_STAFFNO	No	INTEGER	No	The ID number of the staff member that this staff member reports to.
FACEBOOK_ACCESS_TOKEN	No	VARCHAR(max)	No	If the staff member uses their own Facebook account when using the EXO Business social media functions, their encrypted access token is stored here.
LINKEDIN_TOKEN_KEY	No	VARCHAR(max)	No	If the staff member uses their own LinkedIn account when using the EXO Business social media functions, their encrypted access token key is stored here.
LINKEDIN_TOKEN_SECRET	No	VARCHAR(max)	No	If the staff member uses their own LinkedIn account when using the EXO Business social media functions, their encrypted access token secret is stored here.
TWITTER_TOKEN_KEY	No	VARCHAR(max)	No	If the staff member uses their own Twitter account when using the EXO Business social media functions, their encrypted access token key is stored here.
TWITTER_TOKEN_SECRET	No	VARCHAR(max)	No	If the staff member uses their own Twitter account when using the EXO Business social media functions, their encrypted access token secret is stored here.
API_ACCESS	No	CHAR(1)	No	This field contains 'Y' if the staff member is authorised to use the EXO API.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
FK_CRM_BUDGET_EXCLUDED_STAFF_STAFF	Non Identifying	STAFF	CRM_BUDGET_EXCLUDED_STAFF	Zero Or More
FK_CRM_BUDGET_LINE_STAFF	Non Identifying	STAFF	CRM_BUDGET_LINE	Zero Or More
MENU_COLLECTION_STAFF	Non Identifying	MENU_COLLECTION	STAFF	Zero Or More
STAFF_BILLOMAT_TEMP	Non Identifying	STAFF	BILLOMAT_TEMP	Zero Or More
STAFF_CAMPAIGN	Non Identifying	STAFF	CAMPAIGN	Zero Or More
STAFF_CAMPAIGN_HIST	Non Identifying	STAFF	CAMPAIGN_HIST	Zero Or More
STAFF_CONTACTS	Non Identifying	STAFF	CONTACTS	Zero Or More
STAFF_CONTACT_LIST	Non Identifying	STAFF	CONTACT_LIST	Zero Or More
STAFF_CONTACT_LIST1	Non Identifying	STAFF	CONTACT_LIST	Zero Or More
STAFF_CR_ACCS	Non Identifying	STAFF	CR_ACCS	Zero Or More
STAFF_CR_CONT_HIST	Non Identifying	STAFF	CR_CONT_HIST	Zero Or More
STAFF_DASHBOARDS_STAFF	Non Identifying	STAFF	DASHBOARDS_STAFF	Zero Or More
STAFF_DR_ACCS	Non Identifying	STAFF	DR_ACCS	One Or More
STAFF_DR_CONT_HIST	Non Identifying	STAFF	DR_CONT_HIST	One Or More
STAFF_DR_TRANS	Non Identifying	STAFF	DR_TRANS	Zero Or More
STAFF_JOBCOST_HDR	Non Identifying	STAFF	JOBCOST_HDR	Zero Or More
STAFF_JOBCOST_HDR_MGR	Non Identifying	STAFF	JOBCOST_HDR	Zero Or More
STAFF_JOBCOST_RESOURCE	Non Identifying	STAFF	JOBCOST_RESOURCE	Zero Or More
STAFF_JOB_TIMESHEETS	Non Identifying	STAFF	JOB_TIMESHEETS	Zero Or More
STAFF_JOB_TIMESHEET_ALLOWANCE	Non Identifying	STAFF	JOB_TIMESHEET_ALLOWANCE	Zero Or More
STAFF_JOB_TRANSACTIONS	Non Identifying	STAFF	JOB_TRANSACTIONS	Zero Or More
STAFF_MENU_ASSIGNMENTS	Non Identifying	STAFF	MENU_ASSIGNMENTS	Zero Or More
STAFF_OPPORTUNITY_HIST	Non Identifying	STAFF	OPPORTUNITY_HIST	Zero Or More
STAFF_PROFILE_FORMS	Non Identifying	STAFF	PROFILE_FORMS	Zero Or More
STAFF_STOCK_REQUESTS	Non Identifying	STAFF	STOCK_REQUESTS	Zero Or More
STAFF_STOCK_TRANS_HDR	Non Identifying	STAFF	STOCK_TRANS_HDR	Zero Or More
STAFF_TASKS	Non Identifying	STAFF	TASKS	Zero Or More
STAFF_TASKS1	Non Identifying	STAFF	TASKS	Zero Or More
STAFF_TASKS2	Non Identifying	STAFF	TASKS	Zero Or More
STAFF_TASKS3	Non Identifying	STAFF	TASKS	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (EMPLOYEE_CODE)
	Not Null	Column Constraint	NOT NULL (STAFFNO)
	Not Null	Column Constraint	NOT NULL (SECURITYPROFILEID)
	Not Null	Column Constraint	NOT NULL (ABSENT)
	Not Null	Column Constraint	NOT NULL (USERPROFILEID)
	Not Null	Column Constraint	NOT NULL (IS_SUPERVISOR)
	Not Null	Column Constraint	NOT NULL (LOGINID)
	Not Null	Column Constraint	NOT NULL (PASSWORD_CHANGED)
	Not Null	Column Constraint	NOT NULL (DISCOUNTRATE)
	Not Null	Column Constraint	NOT NULL (BAD_LOGIN_COUNT)
	Not Null	Column Constraint	NOT NULL (ACCOUNT_STATUS)
DF__STAFF__ABSENT__12CA5E36	Default	Column Constraint	ABSENT DEFAULT N
DF__STAFF__ACCOUNT_S__5CACADF9	Default	Column Constraint	ACCOUNT_STATUS DEFAULT 0
DF__STAFF__BAD_LOGIN__5BB889C0	Default	Column Constraint	BAD_LOGIN_COUNT DEFAULT 0
DF__STAFF__DISCOUNTR__2C146396	Default	Column Constraint	DISCOUNTRATE DEFAULT 0
DF__STAFF__EMPLOYEE___3DB4BC3B	Default	Column Constraint	EMPLOYEE_CODE DEFAULT -1
DF__STAFF__HAS_BUDGE__6855017D	Default	Column Constraint	HAS_BUDGETS DEFAULT N
DF__STAFF__IS_SUPERV__24F33012	Default	Column Constraint	IS_SUPERVISOR DEFAULT N
DF__STAFF__PASSWORD___5AC46587	Default	Column Constraint	PASSWORD_CHANGED DEFAULT getdate()
DF__STAFF__REPORTS_T__694925B6	Default	Column Constraint	REPORTS_TO_STAFFNO DEFAULT -1
DF__STAFF__SECURITYP__58DC1D15	Default	Column Constraint	SECURITYPROFILEID DEFAULT 0
DF__STAFF__SMTP_SEQN__6B667852	Default	Column Constraint	SMTP_SEQNO DEFAULT -1
DF__STAFF__USERPROFI__59D0414E	Default	Column Constraint	USERPROFILEID DEFAULT 0
MENU_COLLECTION_STAFF	Foreign Key	Table Constraint	FOREIGN KEY (MENU_NO) REFERENCES MENU_COLLECTION(SEQNO)
PK_STAFF	Primary Key	Table Constraint	PRIMARY KEY (STAFFNO)

Entity: STOCK_ITEMS

Comment	This table contains details of all stock items.
Primary key columns	STOCKCODE

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
STOCKCODE	Yes	VARCHAR(23)	Yes	A unique ID code for the stock item.
DESCRIPTION	No	VARCHAR(40)	No	The stock item's description.
STOCKGROUP	No	INTEGER	No	The Primary (Fine) Stock Group that the item belongs to.
STATUS	No	CHAR(1)	No	The stock item's status. Will be one of: S = Stock item L = Lookup item
SELLPRICE1	No	FLOAT(53)	No	Sell price 1.
SELLPRICE2	No	FLOAT(53)	No	Sell price 2.
SELLPRICE3	No	FLOAT(53)	No	Sell price 3.
SELLPRICE4	No	FLOAT(53)	No	Sell price 4.
SELLPRICE5	No	FLOAT(53)	No	Sell price 5.
SELLPRICE6	No	FLOAT(53)	No	Sell price 6.
SELLPRICE7	No	FLOAT(53)	No	Sell price 7.
SELLPRICE8	No	FLOAT(53)	No	Sell price 8.
SELLPRICE9	No	AS	No	Sell price 9.
SELLPRICE10	No	FLOAT(53)	No	Sell price 10.
LATESTCOST	No	FLOAT(53)	No	The stock item's Last Cost Price.
AVECOST	No	FLOAT(53)	No	The stock item's Average Cost Price.
MINSTOCK	No	FLOAT(53)	No	The minimum default stock level for each location.
MAXSTOCK	No	FLOAT(53)	No	The maximum default stock level for each location.
SUPPLIERNO	No	INTEGER	No	The ID number (CR_ACCS.ACCNO) of the stock item's main supplier.
MONTHUNITS	No	FLOAT(53)	No	Sales totals in terms of quantity for the current month.
YEARUNITS	No	FLOAT(53)	No	Year to date sales totals in terms of quantity.
LASTYEARUNITS	No	FLOAT(53)	No	Units of stock item sold in for the previous year.
MONTHVALUE	No	FLOAT(53)	No	Sales value for the current month.
YEARVALUE	No	FLOAT(53)	No	Sales value for the current year.
LASTYEARVALUE	No	FLOAT(53)	No	Sales value for last year.
BINCODE	No	VARCHAR(12)	No	Bincode for the stock item.
DISCOUNTLEVEL	No	INTEGER	No
DEFDAYS	No	INTEGER	No
BARCODE1	No	VARCHAR(30)	No	Barcode/alternate code 1.
BARCODE2	No	VARCHAR(30)	No	Barcode/alternate code 2.
BARCODE3	No	VARCHAR(30)	No	Barcode/alternate code 3.
LASTMONTHVALUE	No	FLOAT(53)	No	Sales value for the previous month for the stock item.
LASTMONTHUNITS	No	FLOAT(53)	No	Units of stock item sold in the previous month.
SALES_GL_CODE	No	INTEGER	No	The default GL code that sales transactions for this stock item are posted to.
PURCH_GL_CODE	No	INTEGER	No	The GL code that purchase transactions for this stock are posted to.
WEB_SHOW	No	CHAR(1)	No
ISACTIVE	No	CHAR(1)	No	If this field is set to 'Y', the stock item is active.
WEIGHT	No	FLOAT(53)	No	Physical weight of the stock item.
CUBIC	No	FLOAT(53)	No	Cubic weight of the item.
ALERT	No	VARCHAR(60)	No	Text of the pop-up alert message that appears when the stock item is sold or purchased.
NOTES	No	VARCHAR(4096)	No	Any notes for the stock item.
PQTY	No	FLOAT(53)	No
PACK	No	VARCHAR(10)	No	Describes the unit of sale for pricing, e.g. EACH or PACK.
HAS_SN	No	CHAR(1)	No	This field is set to 'Y' if the stock item is serial number tracked.
STDCOST	No	FLOAT(53)	No	The stock item's standard cost.
SUPPLIERNO2	No	INTEGER	No	Alternate supplier for the stock item.
SUPPLIERNO3	No	INTEGER	No	Alternate supplier for the stock item.
SALES_GLSUBCODE	No	INTEGER	No	The default GL sub-account code that sales transactions for this stock item are posted to.
PURCH_GLSUBCODE	No	INTEGER	No	The default GL sub-account code that purchase transactions for this stock item are posted to.
BRANCHNO	No	INTEGER	No	Branch number for the stock item.
SALESTAXRATE	No	INTEGER	No	Default GST rate for sales transactions.
PURCHTAXRATE	No	INTEGER	No	Default GST rate for purchase transactions.
LAST_UPDATED	No	DATETIME	No	Date and time when the stock item record was last updated.
UPDATEITEM_CODE	No	VARCHAR(23)	No	Only applies to lookup items. The ID number of the stocked item that maintains the actual stock.
UPDATEITEM_QTY	No	FLOAT(53)	No	Only applies to lookup items. Quantity of the stocked item.
COS_GL_CODE	No	INTEGER	No	The Cost of Sales account updated in the General Ledger when this stock item is sold.
COS_GLSUBCODE	No	INTEGER	No	The Cost of Sales sub-account updated in the General Ledger when the stock item is sold.
STOCKPRICEGROUP	No	INTEGER	No	The price group that the stock item belongs to.
SUPPLIERCOST	No	FLOAT(53)	Yes	The supplier cost price.
ECONORDERQTY	No	FLOAT(53)	No	The economic order quantity (EOQ) for the stock item.
LINKED_BILLCODE	No	VARCHAR(23)	No
STOCK_CLASSIFICATION	No	INTEGER	Yes	The stock classification that the item belongs to.
STOCKGROUP2	No	INTEGER	No	The stock item's Secondary (Coarse) Stock Group.
TOTALSTOCK	No	FLOAT(53)	Yes	Total units of stock for the item.
HAS_BN	No	CHAR(1)	No	This field is set to 'Y' if the stock item is batch tracked.
HAS_EXPIRY	No	CHAR(1)	Yes	This field is set to 'Y' if the stock item has an expiry date. This flag is only available in the UI if the "Use expiry dates for batched & serial numbers" profile setting is enabled.
EXPIRY_DAYS	No	INTEGER	No	Number of days before expiry of the stock item.
DUTY	No	FLOAT(53)	Yes	The duty percentage on the stock item.
SERIALNO_TYPE	No	INTEGER	Yes	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
COSTTYPE	No	INTEGER	Yes
COSTGROUP	No	INTEGER	Yes
LABEL_QTY	No	INTEGER	Yes
IS_DISCOUNTABLE	No	CHAR(1)	Yes	This field is set to 'Y' if the stock item is discountable.
RESTRICTED_ITEM	No	CHAR(1)	Yes	This field is set to 'Y' if the stock item is a restricted item.
NUMDECIMALS	No	INTEGER	Yes	The number of decimal places that can be entered for the stock item's quantity.
COGSMETHOD	No	INTEGER	Yes
DEFAULTWARRANTYNO	No	INTEGER	Yes	The default Warranty No for the stock item.
DIMENSIONS	No	INTEGER	Yes	How the dimensions of the stock item are specified. Will be one of: 0=None 1=Length 2=Square 3=Cubic
X_SIZEID	No	INTEGER	No	Style size ID of the stock item. Used by the Style, Size Colour functionality.
X_COLOURID	No	INTEGER	No	Style colour ID of the stock item. Used by the Style, Size Colour functionality.
AUTO_NARRATIVE	No	INTEGER	No	The setting of the stock item's Auto Narrative property. Will be one of: 0=No 1=Yes with Blank 2=Yes with Notes
VARIABLECOST	No	CHAR(1)	Yes	The field is set to 'Y for Variable Cost stock items.
PRICEQTY	No	FLOAT(53)	No	The stock item's sell price per quantity.
PRICEPERKG	No	FLOAT(53)	No	The stock item's sell price per kg.
LOOKUP_RECOVERABLE	No	CHAR(1)	No	If this field contains 'Y', the stock item is a recoverable expense in EXO Job Costing.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
CR_ACCS_STOCK_ITEMS	Non Identifying	CR_ACCS	STOCK_ITEMS	Zero Or More
FK_CRM_BUDGET_EXCLUDED_STOCK_ITEMS_STOCK_ITEMS	Non Identifying	STOCK_ITEMS	CRM_BUDGET_EXCLUDED_STOCK_ITEMS	Zero Or More
FK_CRM_BUDGET_LINE_STOCK_ITEMS	Non Identifying	STOCK_ITEMS	CRM_BUDGET_LINE	Zero Or More
GLACCS_STOCK_ITEMS	Non Identifying	GLACCS	STOCK_ITEMS	Zero Or More
GLACCS_STOCK_ITEMS_COS	Non Identifying	GLACCS	STOCK_ITEMS	Zero Or More
GL_ACCS_STOCKITEMS_PO	Non Identifying	GLACCS	STOCK_ITEMS	Zero Or More
STOCK_CLASSIFICATIONS_STOCK_ITEMS	Non Identifying	STOCK_CLASSIFICATIONS	STOCK_ITEMS	Zero Or More
STOCK_GROUP2S_STOCK_ITEMS	Non Identifying	STOCK_GROUP2S	STOCK_ITEMS	Zero Or More
STOCK_GROUPS_STOCK_ITEMS	Non Identifying	STOCK_GROUPS	STOCK_ITEMS	Zero Or More
STOCK_ITEMS_BILLOMAT_LINES	Non Identifying	STOCK_ITEMS	BILLOMAT_LINES	Zero Or More
STOCK_ITEMS_BILLOMAT_TEMP	Non Identifying	STOCK_ITEMS	BILLOMAT_TEMP	Zero Or More
STOCK_ITEMS_CR_INVLINES	Non Identifying	STOCK_ITEMS	CR_INVLINES	Zero Or More
STOCK_ITEMS_DR_INVLINES	Non Identifying	STOCK_ITEMS	DR_INVLINES	One Or More
STOCK_ITEMS_DR_PRICES	Non Identifying	STOCK_ITEMS	DR_PRICES	Zero Or More
STOCK_ITEMS_INWARDS_GOODS_LINES	Non Identifying	STOCK_ITEMS	INWARDS_GOODS_LINES	Zero Or More
STOCK_ITEMS_JOBCOST_LINES	Non Identifying	STOCK_ITEMS	JOBCOST_LINES	Zero Or More
STOCK_ITEMS_JOBCOST_LINES_DESC	Non Identifying	STOCK_ITEMS	JOBCOST_LINES	Zero Or More
STOCK_ITEMS_JOB_CONTRACT_BILLINGS	Non Identifying	STOCK_ITEMS	JOB_CONTRACT_BILLINGS	Zero Or More
STOCK_ITEMS_JOB_OUTPUT_ITEMS	Non Identifying	STOCK_ITEMS	JOB_OUTPUT_ITEMS	Zero Or More
STOCK_ITEMS_JOB_OUTPUT_ITEMS_DESC	Non Identifying	STOCK_ITEMS	JOB_OUTPUT_ITEMS	Zero Or More
STOCK_ITEMS_JOB_TIMESHEETS	Non Identifying	STOCK_ITEMS	JOB_TIMESHEETS	Zero Or More
STOCK_ITEMS_JOB_TRANSACTIONS	Non Identifying	STOCK_ITEMS	JOB_TRANSACTIONS	Zero Or More
STOCK_ITEMS_JOB_TRANSACTIONS_DESC	Non Identifying	STOCK_ITEMS	JOB_TRANSACTIONS	Zero Or More
STOCK_ITEMS_OPPORTUNITY_QUOTE	Non Identifying	STOCK_ITEMS	OPPORTUNITY_QUOTE	Zero Or More
STOCK_ITEMS_STOCKREQUIREMENT	Non Identifying	STOCK_ITEMS	STOCKREQUIREMENT	Zero Or More
STOCK_ITEMS_STOCKTAKE_TOTALS	Non Identifying	STOCK_ITEMS	STOCKTAKE_TOTALS	Zero Or More
STOCK_ITEMS_STOCKTAKE_TOTALS_BN	Non Identifying	STOCK_ITEMS	STOCKTAKE_TOTALS	Zero Or More
STOCK_ITEMS_STOCKTAKE_TOTALS_EXPDAYS	Non Identifying	STOCK_ITEMS	STOCKTAKE_TOTALS	Zero Or More
STOCK_ITEMS_STOCKTAKE_TOTALS_EXPIRY	Non Identifying	STOCK_ITEMS	STOCKTAKE_TOTALS	Zero Or More
STOCK_ITEMS_STOCKTAKE_TOTALS_SERIAL	Non Identifying	STOCK_ITEMS	STOCKTAKE_TOTALS	Zero Or More
STOCK_ITEMS_STOCK_LOC_INFO	Identifying	STOCK_ITEMS	STOCK_LOC_INFO	Zero Or More
STOCK_ITEMS_STOCK_REQUESTLINES	Non Identifying	STOCK_ITEMS	STOCK_REQUESTLINES	Zero Or More
STOCK_ITEMS_STOCK_REQUESTLINES_DESC	Non Identifying	STOCK_ITEMS	STOCK_REQUESTLINES	Zero Or More
STOCK_ITEMS_STOCK_SERIALNOS	Non Identifying	STOCK_ITEMS	STOCK_SERIALNOS	Zero Or More
STOCK_ITEMS_STOCK_TRANS	Non Identifying	STOCK_ITEMS	STOCK_TRANS	Zero Or More
STOCK_ITEMS_STOCK_WEB	Non Identifying	STOCK_ITEMS	STOCK_WEB	Zero Or More
STOCK_ITEMS_SUPPLIER_STOCK_ITEMS	Non Identifying	STOCK_ITEMS	SUPPLIER_STOCK_ITEMS	Zero Or More
STOCK_PRICEGROUPS_STOCK_ITEMS	Non Identifying	STOCK_PRICEGROUPS	STOCK_ITEMS	Zero Or More

Indexes:

Name	Index columns	Index type
STOCK_ITEMS_DESC	DESCRIPTION ASC

Constraints:

Name	Type	Level	Constraint
	Default	Column Constraint	LOOKUP_RECOVERABLE DEFAULT Y
	Not Null	Column Constraint	NOT NULL (STOCKCODE)
	Not Null	Column Constraint	NOT NULL (VARIABLECOST)
	Not Null	Column Constraint	NOT NULL (DIMENSIONS)
	Not Null	Column Constraint	NOT NULL (DEFAULTWARRANTYNO)
	Not Null	Column Constraint	NOT NULL (COGSMETHOD)
	Not Null	Column Constraint	NOT NULL (NUMDECIMALS)
	Not Null	Column Constraint	NOT NULL (RESTRICTED_ITEM)
	Not Null	Column Constraint	NOT NULL (IS_DISCOUNTABLE)
	Not Null	Column Constraint	NOT NULL (LABEL_QTY)
	Not Null	Column Constraint	NOT NULL (COSTGROUP)
	Not Null	Column Constraint	NOT NULL (COSTTYPE)
	Not Null	Column Constraint	NOT NULL (SERIALNO_TYPE)
	Not Null	Column Constraint	NOT NULL (DUTY)
	Not Null	Column Constraint	NOT NULL (HAS_EXPIRY)
	Not Null	Column Constraint	NOT NULL (TOTALSTOCK)
	Not Null	Column Constraint	NOT NULL (STOCK_CLASSIFICATION)
	Not Null	Column Constraint	NOT NULL (SUPPLIERCOST)
CR_ACCS_STOCK_ITEMS	Foreign Key	Table Constraint	FOREIGN KEY (SUPPLIERNO) REFERENCES CR_ACCS(ACCNO)
DF__STOCK_ITEM__DUTY__675F4696	Default	Column Constraint	DUTY DEFAULT 0
DF__STOCK_ITEM__PQTY__7C255952	Default	Column Constraint	PQTY DEFAULT 1
DF__STOCK_ITE__AUTO___43ECB87E	Default	Column Constraint	AUTO_NARRATIVE DEFAULT 0
DF__STOCK_ITE__AVECO__18A19C6F	Default	Column Constraint	AVECOST DEFAULT 0
DF__STOCK_ITE__BRANC__5FD33367	Default	Column Constraint	BRANCHNO DEFAULT 0
DF__STOCK_ITE__COGSM__0BD27C8D	Default	Column Constraint	COGSMETHOD DEFAULT 0
DF__STOCK_ITE__COSTG__50BBD860	Default	Column Constraint	COSTGROUP DEFAULT 0
DF__STOCK_ITE__COSTT__4FC7B427	Default	Column Constraint	COSTTYPE DEFAULT 0
DF__STOCK_ITE__COS_G__4D7F7902	Default	Column Constraint	COS_GL_CODE DEFAULT 0
DF__STOCK_ITE__COS_G__4E739D3B	Default	Column Constraint	COS_GLSUBCODE DEFAULT 0
DF__STOCK_ITE__CUBIC__45C948A1	Default	Column Constraint	CUBIC DEFAULT 0
DF__STOCK_ITE__DEFAU__6F0133B5	Default	Column Constraint	DEFAULTWARRANTYNO DEFAULT -1
DF__STOCK_ITE__DEFDA__2D9CB955	Default	Column Constraint	DEFDAYS DEFAULT 0
DF__STOCK_ITE__DIMEN__3CFFC3CD	Default	Column Constraint	DIMENSIONS DEFAULT 0
DF__STOCK_ITE__DISCO__2CA8951C	Default	Column Constraint	DISCOUNTLEVEL DEFAULT 0
DF__STOCK_ITE__EXPIR__666B225D	Default	Column Constraint	EXPIRY_DAYS DEFAULT 1
DF__STOCK_ITE__HAS_B__526429B0	Default	Column Constraint	HAS_BN DEFAULT N
DF__STOCK_ITE__HAS_E__6576FE24	Default	Column Constraint	HAS_EXPIRY DEFAULT N
DF__STOCK_ITE__HAS_S__7FF5EA36	Default	Column Constraint	HAS_SN DEFAULT N
DF__STOCK_ITE__ISACT__74B941B4	Default	Column Constraint	ISACTIVE DEFAULT Y
DF__STOCK_ITE__IS_DI__3044E72D	Default	Column Constraint	IS_DISCOUNTABLE DEFAULT Y
DF__STOCK_ITE__LABEL__6C04CB79	Default	Column Constraint	LABEL_QTY DEFAULT 1
DF__STOCK_ITE__LASTM__2E90DD8E	Default	Column Constraint	LASTMONTHVALUE DEFAULT 0
DF__STOCK_ITE__LASTM__2F8501C7	Default	Column Constraint	LASTMONTHUNITS DEFAULT 0
DF__STOCK_ITE__LASTY__28D80438	Default	Column Constraint	LASTYEARUNITS DEFAULT 0
DF__STOCK_ITE__LASTY__2BB470E3	Default	Column Constraint	LASTYEARVALUE DEFAULT 0
DF__STOCK_ITE__LATES__17AD7836	Default	Column Constraint	LATESTCOST DEFAULT 0
DF__STOCK_ITE__MAXST__25077354	Default	Column Constraint	MAXSTOCK DEFAULT 0
DF__STOCK_ITE__MINST__24134F1B	Default	Column Constraint	MINSTOCK DEFAULT 0
DF__STOCK_ITE__MONTH__26EFBBC6	Default	Column Constraint	MONTHUNITS DEFAULT 0
DF__STOCK_ITE__MONTH__29CC2871	Default	Column Constraint	MONTHVALUE DEFAULT 0
DF__STOCK_ITE__NUMDE__0801EBA9	Default	Column Constraint	NUMDECIMALS DEFAULT -1
DF__STOCK_ITE__PURCH__451F3D2B	Default	Column Constraint	PURCH_GLSUBCODE DEFAULT 0
DF__STOCK_ITE__PURCH__4727812E	Default	Column Constraint	PURCH_GL_CODE DEFAULT 0
DF__STOCK_ITE__PURCH__68687968	Default	Column Constraint	PURCHTAXRATE DEFAULT -1
DF__STOCK_ITE__RESTR__77CB83E0	Default	Column Constraint	RESTRICTED_ITEM DEFAULT N
DF__STOCK_ITE__SALES__442B18F2	Default	Column Constraint	SALES_GLSUBCODE DEFAULT 0
DF__STOCK_ITE__SALES__46335CF5	Default	Column Constraint	SALES_GL_CODE DEFAULT 0
DF__STOCK_ITE__SALES__6774552F	Default	Column Constraint	SALESTAXRATE DEFAULT -1
DF__STOCK_ITE__SELLP__13DCE752	Default	Column Constraint	SELLPRICE1 DEFAULT 0
DF__STOCK_ITE__SELLP__14D10B8B	Default	Column Constraint	SELLPRICE2 DEFAULT 0
DF__STOCK_ITE__SELLP__15C52FC4	Default	Column Constraint	SELLPRICE3 DEFAULT 0
DF__STOCK_ITE__SELLP__16B953FD	Default	Column Constraint	SELLPRICE4 DEFAULT 0
DF__STOCK_ITE__SELLP__39987BE6	Default	Column Constraint	SELLPRICE5 DEFAULT 0
DF__STOCK_ITE__SELLP__3A8CA01F	Default	Column Constraint	SELLPRICE6 DEFAULT 0
DF__STOCK_ITE__SELLP__3B80C458	Default	Column Constraint	SELLPRICE7 DEFAULT 0
DF__STOCK_ITE__SELLP__3C74E891	Default	Column Constraint	SELLPRICE8 DEFAULT 0
DF__STOCK_ITE__SELLP__3E5D3103	Default	Column Constraint	SELLPRICE10 DEFAULT 0
DF__STOCK_ITE__SERIA__1C481021	Default	Column Constraint	SERIALNO_TYPE DEFAULT 0
DF__STOCK_ITE__STATU__12E8C319	Default	Column Constraint	STATUS DEFAULT L
DF__STOCK_ITE__STDCO__392E6792	Default	Column Constraint	STDCOST DEFAULT 0
DF__STOCK_ITE__STOCK__11F49EE0	Default	Column Constraint	STOCKGROUP DEFAULT 0
DF__STOCK_ITE__STOCK__4F87BD05	Default	Column Constraint	STOCKGROUP2 DEFAULT 0
DF__STOCK_ITE__STOCK__6442E2C9	Default	Column Constraint	STOCK_CLASSIFICATION DEFAULT 0
DF__STOCK_ITE__STOCK__69279377	Default	Column Constraint	STOCKPRICEGROUP DEFAULT 0
DF__STOCK_ITE__SUPPL__16B953FD	Default	Column Constraint	SUPPLIERCOST DEFAULT 0
DF__STOCK_ITE__SUPPL__25FB978D	Default	Column Constraint	SUPPLIERNO DEFAULT 0
DF__STOCK_ITE__TOTAL__51700577	Default	Column Constraint	TOTALSTOCK DEFAULT 0
DF__STOCK_ITE__UPDAT__257187A8	Default	Column Constraint	UPDATEITEM_QTY DEFAULT 0
DF__STOCK_ITE__VARIA__611DF409	Default	Column Constraint	VARIABLECOST DEFAULT N
DF__STOCK_ITE__WEB_S__44160A59	Default	Column Constraint	WEB_SHOW DEFAULT N
DF__STOCK_ITE__WEIGH__44D52468	Default	Column Constraint	WEIGHT DEFAULT 0
DF__STOCK_ITE__X_COL__02890975	Default	Column Constraint	X_COLOURID DEFAULT 0
DF__STOCK_ITE__X_SIZ__0194E53C	Default	Column Constraint	X_SIZEID DEFAULT 0
DF__STOCK_ITE__YEARU__27E3DFFF	Default	Column Constraint	YEARUNITS DEFAULT 0
DF__STOCK_ITE__YEARV__2AC04CAA	Default	Column Constraint	YEARVALUE DEFAULT 0
GLACCS_STOCK_ITEMS	Foreign Key	Table Constraint	FOREIGN KEY (SALES_GL_CODE) REFERENCES GLACCS(ACCNO)
GLACCS_STOCK_ITEMS_COS	Foreign Key	Table Constraint	FOREIGN KEY (COS_GL_CODE) REFERENCES GLACCS(ACCNO)
GL_ACCS_STOCKITEMS_PO	Foreign Key	Table Constraint	FOREIGN KEY (PURCH_GL_CODE) REFERENCES GLACCS(ACCNO)
PK__STOCK_ITEMS__11007AA7	Primary Key	Table Constraint	PRIMARY KEY (STOCKCODE)
STOCK_CLASSIFICATIONS_STOCK_ITEMS	Foreign Key	Table Constraint	FOREIGN KEY (STOCK_CLASSIFICATION) REFERENCES STOCK_CLASSIFICATIONS(CLASSNO)
STOCK_GROUP2S_STOCK_ITEMS	Foreign Key	Table Constraint	FOREIGN KEY (STOCKGROUP2) REFERENCES STOCK_GROUP2S(GROUPNO)
STOCK_GROUPS_STOCK_ITEMS	Foreign Key	Table Constraint	FOREIGN KEY (STOCKGROUP) REFERENCES STOCK_GROUPS(GROUPNO)
STOCK_PRICEGROUPS_STOCK_ITEMS	Foreign Key	Table Constraint	FOREIGN KEY (STOCKPRICEGROUP) REFERENCES STOCK_PRICEGROUPS(GROUPNO)

Triggers:

Trigger name	Code
AFTER_STOCK_ITEMS_DELETE	CREATE TRIGGER [dbo].[AFTER_STOCK_ITEMS_DELETE] ON [dbo].[STOCK_ITEMS] FOR DELETE AS SET NOCOUNT ON DELETE FROM STOCK_LOC_INFO WHERE STOCKCODE IN (SELECT STOCKCODE FROM DELETED) SET NOCOUNT OFF
STOCK_ITEMS_LOC_INFO	CREATE TRIGGER [dbo].[STOCK_ITEMS_LOC_INFO] ON [dbo].[STOCK_ITEMS] FOR INSERT AS BEGIN SET NOCOUNT ON INSERT INTO STOCK_LOC_INFO (STOCKCODE, LOCATION, BINCODE, MINSTOCK, MAXSTOCK, QTY) SELECT I.STOCKCODE, SL.LOCNO, I.BINCODE, I.MINSTOCK, I.MAXSTOCK, 0 FROM STOCK_LOCATIONS SL, INSERTED I SET NOCOUNT OFF END
STOCK_ITEMS_ONEOFF	CREATE TRIGGER [dbo].[STOCK_ITEMS_ONEOFF] ON [dbo].[STOCK_ITEMS] FOR UPDATE AS BEGIN DECLARE @UPDATEDLINES INT SET @UPDATEDLINES=@@ROWCOUNT SET NOCOUNT ON IF (@UPDATEDLINES > 0) AND NOT UPDATE(ISACTIVE) BEGIN UPDATE S SET ISACTIVE = 'N' FROM [dbo].[STOCK_ITEMS] S JOIN INSERTED I ON S.STOCKCODE = I.STOCKCODE JOIN STOCK_TRANS ST ON I.STOCKCODE = ST.STOCKCODE WHERE ((S.STOCK_CLASSIFICATION = 100) OR (S.STOCK_CLASSIFICATION = 70)) AND (S.TOTALSTOCK <= 0) END SET NOCOUNT OFF END