|
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:
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. |
| INVOICE_VIA_MASTER | No | CHAR(1) | No | Applies to sub jobs. This field is set to 'Y' if the job can be invoiced from its master job. |
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__242ABFB9 | Default | Column Constraint | INVOICE_VIA_MASTER DEFAULT Y |
| 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. |
| LINETYPE | No | CHAR(1) | Yes | This field indicates the billing schedule line type. Will be one of:
P = Proforma
Q = Quote
C = Credit Note
I = Job Invoice |
| SOURCE_BILLING_SCHEDULE_SEQNO | No | INTEGER | Yes | This field stores the Billing Schedule SEQNO of the record the current record is linked to. |
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 (SOURCE_BILLING_SCHEDULE_SEQNO) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| Not Null | Column Constraint | NOT NULL (LINETYPE) |
| Not Null | Column Constraint | NOT NULL (DESCRIPTION) |
| Not Null | Column Constraint | NOT NULL (MASTER_JOBNO) |
| Not Null | Column Constraint | NOT NULL (ALLOCATED_COSTS) |
| Not Null | Column Constraint | NOT NULL (CURRENCYNO) |
| Not Null | Column Constraint | NOT NULL (INVOICED) |
| Not Null | Column Constraint | NOT NULL (EXCHRATE) |
| Not Null | Column Constraint | NOT NULL (PERCENTAGE_COMPLETE) |
| Not Null | Column Constraint | NOT NULL (RETENTION_INV_SEQNO) |
| Not Null | Column Constraint | NOT NULL (RETENTION_REALISED) |
| Not Null | Column Constraint | NOT NULL (ALLOW_ALLOCATION) |
| Not Null | Column Constraint | NOT NULL (GLSUBCODE) |
| Not Null | Column Constraint | NOT NULL (RETENTION2) |
| Not Null | Column Constraint | NOT NULL (GLCODE) |
| Not Null | Column Constraint | NOT NULL (RETENTION2_RATE) |
| Not Null | Column Constraint | NOT NULL (RETENTION3_RATE) |
| Not Null | Column Constraint | NOT NULL (RETENTION3) |
| 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__LINET__16D0C49B | Default | Column Constraint | LINETYPE DEFAULT P |
| 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 |
| DF__JOB_CONTR__SOURC__17C4E8D4 | Default | Column Constraint | SOURCE_BILLING_SCHEDULE_SEQNO DEFAULT -1 |
| 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. |
| 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. |
| CREDIT_SCHEDULE_SEQNO | No | INTEGER | Yes | This field stores the ID number of the Billing Schedule record that the current record was credited in. |
| 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. |
| STOCK_TRANS_SEQ_IN | No | INTEGER | Yes | This field stores the STOCK_TRANS.SEQNO from the Purchase Order line that inserted the job transaction. |
| LINECHARGE_WRITEOFF | No | FLOAT(53) | No | This field stores the value of LINECHARGE before the line was written off. |
| 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 |
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 | 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 (WIP_OUT_PERIOD_SEQNO) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| Not Null | Column Constraint | NOT NULL (WIP_IN_PERIOD_SEQNO) |
| Not Null | Column Constraint | NOT NULL (STOCK_TRANS_SEQ_IN) |
| Not Null | Column Constraint | NOT NULL (COST_LINENO) |
| Not Null | Column Constraint | NOT NULL (CREDIT_SCHEDULE_SEQNO) |
| 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_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 | 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. |
| MOBILE_ACCESS | No | CHAR(1) | Yes | This field contains 'Y' if the staff member is authorised to use the EXO OnTheGo mobile app. |
| BANKFEED_REFRESHTOKEN | No | VARCHAR(500) | No | Encrypted OAuth refresh token used for Bank Feeds. |
| SBR_REFRESHTOKEN | No | VARCHAR(1024) | No | The refresh token used for SBR authentication. |
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_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_OPPORTUNITY_HIST | Non Identifying | STAFF | OPPORTUNITY_HIST | 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 (MOBILE_ACCESS) |
| Not Null | Column Constraint | NOT NULL (STAFFNO) |
| Not Null | Column Constraint | NOT NULL (SECURITYPROFILEID) |
| Not Null | Column Constraint | NOT NULL (EMPLOYEE_CODE) |
| Not Null | Column Constraint | NOT NULL (USERPROFILEID) |
| Not Null | Column Constraint | NOT NULL (ABSENT) |
| Not Null | Column Constraint | NOT NULL (LOGINID) |
| Not Null | Column Constraint | NOT NULL (PASSWORD_CHANGED) |
| Not Null | Column Constraint | NOT NULL (IS_SUPERVISOR) |
| Not Null | Column Constraint | NOT NULL (BAD_LOGIN_COUNT) |
| Not Null | Column Constraint | NOT NULL (DISCOUNTRATE) |
| 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__MOBILE_AC__38F0D275 | Default | Column Constraint | MOBILE_ACCESS 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 | Zero 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
|
|