|
Entity: CAMPAIGN_WAVE
| Comment | This table contains details of all campaign waves setup in EXO Business CRM. |
| Primary key columns | SEQNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique ID number for the campaign wave. |
| WAVE_NO | No | INTEGER | No | An ID number that uniquely identifies the campaign wave within the campaign. |
| CAMPAIGN_SEQNO | No | INTEGER | Yes | The ID number of the campaign that the campaign wave is part of. |
| DESCRIPT | No | VARCHAR(50) | No | A short written description of the campaign wave. |
| STARTDATE | No | DATETIME | No | The start date for the period that the campaign wave runs over. |
| ENDDATE | No | DATETIME | No | The end date for the period that the campaign wave runs over. |
| COMMUNICATION_METHOD | No | INTEGER | No | This field specifies the Communication Method selected for the campaign wave. Will be one of:
0 = no Communication Method selected
1 = Mailshot Process
2 = Bulk Activity Creation
3 = Execute SQL
4 = Social Media Post
5 = Export List
6 = Execute External Program |
| TRACKER_KEY | No | VARCHAR(150) | No | For campaign waves with the "Mailshot Process" Communication Method, this field contains any text entered into the Tracker ID field. |
| OPT_IN_URL | No | VARCHAR(150) | No | For campaign waves with the "Mailshot Process" Communication Method, this field contains any text entered into the Opt in URL field. |
| OPT_OUT_URL | No | VARCHAR(150) | No | For campaign waves with the "Mailshot Process" Communication Method, this field contains any text entered into the Opt out URL field. |
| LANDING_SITE | No | VARCHAR(150) | No | For campaign waves with the "Mailshot Process" Communication Method, this field contains any text entered into the Landing site field. |
| PROCESSED | No | CHAR(1) | No | This field is set to 'Y' if the campaign wave's Processed flag is ticked. |
| COMPLETE | No | CHAR(1) | No | This field is set to 'Y' if the campaign wave's Complete flag is ticked. |
| DOC_BATCH_HDR_SEQNO | No | INTEGER | No | For campaign waves with the "Mailshot Process " Communication Method that include email attachments, this field contains the ID number of the documentation batch in the DOC_BATCH_HDR table that relates to the attachments. |
| SOCIAL_MEDIA_TEXT | No | VARCHAR(500) | No | For campaign waves with the "Social Media Post" Communication Method, this field contains the text that is posted to the social media network(s). |
| FACEBOOK_POST_ID | No | VARCHAR(100) | No | For campaign waves with the "Social Media Post" Communication Method, this field contains the ID number of the Facebook post. |
| TWITTER_POST_ID | No | VARCHAR(100) | No | For campaign waves with the "Social Media Post" Communication Method, this field contains the ID number of the Twitter post. |
| LINKEDIN_POST_DATE | No | DATETIME | No | For campaign waves with the "Social Media Post" Communication Method, this field contains the date of the LinkedIn post. |
| SETTINGS | No | VARCHAR(4096) | No | This field is used by campaign waves where the Communication Method is "Execute SQL" or "Execute External Program". |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CAMPAIGN_CAMPAIGN_WAVE | Non Identifying | CAMPAIGN | CAMPAIGN_WAVE | Zero Or More |
| CAMPAIGN_WAVE_CAMPAIGN_WAVE_AUDIT | Non Identifying | CAMPAIGN_WAVE | CAMPAIGN_WAVE_AUDIT | Zero Or More |
| CAMPAIGN_WAVE_CAMPAIGN_WAVE_CONTACT_LISTS | Non Identifying | CAMPAIGN_WAVE | CAMPAIGN_WAVE_CONTACT_LISTS | Zero Or More |
| CAMPAIGN_WAVE_CONTACTS | Non Identifying | CAMPAIGN_WAVE | CONTACTS | Zero Or More |
| CAMPAIGN_WAVE_DR_PRICES | Non Identifying | CAMPAIGN_WAVE | DR_PRICES | Zero Or More |
| CAMPAIGN_WAVE_DR_PRICE_POLICY | Non Identifying | CAMPAIGN_WAVE | DR_PRICE_POLICY | Zero Or More |
| CAMPAIGN_WAVE_DR_TRANS | Non Identifying | CAMPAIGN_WAVE | DR_TRANS | Zero Or More |
| CAMPAIGN_WAVE_JOBCOST_HDR | Non Identifying | CAMPAIGN_WAVE | JOBCOST_HDR | Zero Or More |
| CAMPAIGN_WAVE_OPPORTUNITY | Non Identifying | CAMPAIGN_WAVE | OPPORTUNITY | Zero Or More |
| CAMPAIGN_WAVE_SALESORD_HDR | Non Identifying | CAMPAIGN_WAVE | SALESORD_HDR | Zero Or More |
| CAMPAIGN_WAVE_TASKS | Non Identifying | CAMPAIGN_WAVE | TASKS | Zero Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (CAMPAIGN_SEQNO) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| CAMPAIGN_CAMPAIGN_WAVE | Foreign Key | Table Constraint | FOREIGN KEY (CAMPAIGN_SEQNO) REFERENCES CAMPAIGN(SEQNO) |
| DF__CAMPAIGN___COMMU__4C97D86F | Default | Column Constraint | COMMUNICATION_METHOD DEFAULT 0 |
| DF__CAMPAIGN___COMPL__4E8020E1 | Default | Column Constraint | COMPLETE DEFAULT N |
| DF__CAMPAIGN___DOC_B__4F74451A | Default | Column Constraint | DOC_BATCH_HDR_SEQNO DEFAULT 0 |
| DF__CAMPAIGN___ENDDA__4BA3B436 | Default | Column Constraint | ENDDATE DEFAULT getdate() |
| DF__CAMPAIGN___PROCE__4D8BFCA8 | Default | Column Constraint | PROCESSED DEFAULT N |
| DF__CAMPAIGN___START__4AAF8FFD | Default | Column Constraint | STARTDATE DEFAULT getdate() |
| PK__CAMPAIGN__C8320F98708B2022 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
Triggers:
| Trigger name | Code |
| CAMPAIGN_WAVE_PROCESSED | CREATE TRIGGER [dbo].[CAMPAIGN_WAVE_PROCESSED]
ON [dbo].[CAMPAIGN_WAVE]
FOR UPDATE
AS
BEGIN
DECLARE @FLATLIST AS VARCHAR(500),
@CAMPAIGN_SEQNO INT,
@WAVENO INT,
@CAMPAIGN_WAVE_SEQNO INT,
@SALESNO INT,
@TRANSDATE DATETIME,
@DESCRIPT VARCHAR(50),
@SUBJECT VARCHAR(100),
@NOTE VARCHAR(255),
@COMM_MESSAGE VARCHAR(255),
@PROCESSED CHAR(1),
@COMPLETE CHAR(1),
@CAMPAIGN_WAVE_TYPE VARCHAR(100),
@INTERACTIONS INT,
@WAS_PROCESSED CHAR(1),
@COMMUNICATION_METHOD CHAR(1),
@OLD_SOCIAL_MEDIA_TEXT VARCHAR(500),
@OLD_FACEBOOK_POST_ID VARCHAR(100),
@OLD_LINKEDIN_POST_DATE DATETIME,
@OLD_TWITTER_POST_ID VARCHAR(100),
@FACEBOOK_POST_ID VARCHAR(100),
@TWITTER_POST_ID VARCHAR(100),
@LINKEDIN_POST_DATE DATETIME
SET @FLATLIST ='' /*FLATLIST builds a comma separated list of the CONTACT_LIST.names used in this Campaign Wave*/
SET @WAS_PROCESSED = (SELECT PROCESSED from DELETED)
SET @SALESNO = [dbo].FN_GET_STAFF_NO()
SELECT
@OLD_SOCIAL_MEDIA_TEXT = ISNULL(SOCIAL_MEDIA_TEXT, ''),
@OLD_FACEBOOK_POST_ID = ISNULL(FACEBOOK_POST_ID, ''),
@OLD_LINKEDIN_POST_DATE = ISNULL(LINKEDIN_POST_DATE, 0),
@OLD_TWITTER_POST_ID = ISNULL(TWITTER_POST_ID, ''),
@WAS_PROCESSED = PROCESSED
FROM DELETED
SELECT
@CAMPAIGN_WAVE_SEQNO = SEQNO,
@CAMPAIGN_SEQNO = CAMPAIGN_SEQNO,
@WAVENO = WAVE_NO,
@DESCRIPT = DESCRIPT,
@TRANSDATE = GETDATE(),
@PROCESSED = PROCESSED,
@COMPLETE = COMPLETE,
@COMMUNICATION_METHOD = COMMUNICATION_METHOD,
@OLD_SOCIAL_MEDIA_TEXT = SOCIAL_MEDIA_TEXT,
@FACEBOOK_POST_ID = FACEBOOK_POST_ID,
@LINKEDIN_POST_DATE = LINKEDIN_POST_DATE,
@TWITTER_POST_ID = TWITTER_POST_ID
FROM INSERTED
IF @COMMUNICATION_METHOD <> '4' AND @PROCESSED = 'Y' AND @WAS_PROCESSED <> 'Y'
BEGIN
-- Get interaction count
IF @COMMUNICATION_METHOD = '2' /*BULK TASK INSERT*/
SELECT @INTERACTIONS=ISNULL(COUNT(*), 0)
FROM TASKS WITH (NOLOCK)
WHERE CAMPAIGN_WAVE_SEQNO = @CAMPAIGN_WAVE_SEQNO
ELSE
SELECT @INTERACTIONS=ISNULL(COUNT(*), 0)
FROM CAMPAIGN_WAVE_AUDIT CWA WITH (NOLOCK)
WHERE CWA.CAMPAIGN_WAVE_SEQNO = @CAMPAIGN_WAVE_SEQNO
SELECT @FLATLIST = ISNULL(@FLATLIST, '') + ', ' + TITLE
FROM CONTACT_LIST CL
WHERE CL.SEQNO IN (SELECT CONTACT_LIST_SEQNO
FROM CAMPAIGN_WAVE_CONTACT_LISTS WITH (NOLOCK)
WHERE CAMPAIGN_WAVE_SEQNO = @CAMPAIGN_WAVE_SEQNO)
SELECT @COMM_MESSAGE =
CASE
WHEN @COMMUNICATION_METHOD = '1' THEN 'Mailshot Processed' -- Mailshot
WHEN @COMMUNICATION_METHOD = '2' THEN 'Bulk Activity Processed' -- Bulk Activity Creation
WHEN @COMMUNICATION_METHOD = '3' THEN 'Execute SQL Processed' -- Execute SQL
WHEN @COMMUNICATION_METHOD = '5' THEN 'Export List Processed' -- Export List
WHEN @COMMUNICATION_METHOD = '6' THEN 'Executed External Program' -- Execute External Program
ELSE ''
END
SET @NOTE = 'List(s) Used : ' + SUBSTRING(@FLATLIST, 3, LEN(@FLATLIST))+ ' '+CAST(@INTERACTIONS AS VARCHAR(100))+' Interactions'
SET @SUBJECT='Wave '+ LTRIM(STR(@WAVENO, 2)) + ' ' + @COMM_MESSAGE + '-' + @DESCRIPT
INSERT INTO CAMPAIGN_HIST (CAMPAIGN_SEQNO, SALESNO, TRANSDATE, SUBJECT, NOTE)
VALUES (@CAMPAIGN_SEQNO, @SALESNO, @TRANSDATE, @SUBJECT, @NOTE)
END /*IF PROCESSED*/
ELSE IF @COMMUNICATION_METHOD = '4' -- Social Media Post
BEGIN
SELECT @COMM_MESSAGE =
CASE
WHEN @OLD_FACEBOOK_POST_ID <> @FACEBOOK_POST_ID THEN 'Facebook wall post'
WHEN @OLD_LINKEDIN_POST_DATE <> @LINKEDIN_POST_DATE THEN 'LinkedIn activity'
WHEN @OLD_TWITTER_POST_ID <> @TWITTER_POST_ID THEN 'Twitter status update'
ELSE ''
END
IF @COMM_MESSAGE <> ''
BEGIN
SET @SUBJECT='Wave '+ LTRIM(STR(@WAVENO, 2)) + ' ' + @COMM_MESSAGE --+ '-' + @DESCRIPT
SET @NOTE = @OLD_SOCIAL_MEDIA_TEXT
INSERT INTO CAMPAIGN_HIST (CAMPAIGN_SEQNO, SALESNO, TRANSDATE, SUBJECT, NOTE)
VALUES (@CAMPAIGN_SEQNO, @SALESNO, @TRANSDATE, @SUBJECT, @NOTE)
END
END
END
|
Entity: CONTACTS
| Comment | This table contains information about Contacts. |
| Primary key columns | SEQNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique identifier for the Contact record. |
| SALUTATION | No | VARCHAR(4) | No | The Contact's preferred salutation, e.g. Mr, Mrs. |
| FIRSTNAME | No | VARCHAR(30) | No | The Contact's first name. |
| LASTNAME | No | VARCHAR(30) | No | The Contact's last/family name. |
| TITLE | No | VARCHAR(30) | No | The Contact's job title. |
| MOBILE | No | VARCHAR(30) | No | The Contact's mobile phone number. |
| DIRECTPHONE | No | VARCHAR(30) | No | The Contact's direct telephone number. |
| DIRECTFAX | No | VARCHAR(30) | No | The Contact's fax number. |
| HOMEPHONE | No | VARCHAR(30) | No | The Contact's home phone number. |
| EMAIL | No | VARCHAR(60) | No | The Contact's email address. |
| NOTES | No | VARCHAR(4096) | No | All notes entered for the Contact on the Notes tab. |
| ADDRESS1 | No | VARCHAR(30) | No | Postal address line 1. |
| ADDRESS2 | No | VARCHAR(30) | No | Postal address line 2. |
| ADDRESS3 | No | VARCHAR(30) | No | Postal address line 3. |
| ADDRESS4 | No | VARCHAR(30) | No | Postal address line 4. |
| ADDRESS5 | No | VARCHAR(30) | No | Postal address line 5. This line is only available on the Contacts window if the Additional Postal Address Row profile setting is enabled. |
| POST_CODE | No | VARCHAR(12) | No | The Contact's post code. |
| 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. |
| ISACTIVE | No | CHAR(1) | No | Whether the Contact is active (Y) or inactive (N). |
| ADVERTSOURCE | No | INTEGER | No | The Contact's Advertising Source - corresponds to the SEQNO of the type in the ADVERT_TYPES table. |
| SALESNO | No | INTEGER | No | The STAFFNO of staff member who is set as the Sales person for the Contact. |
| FULLNAME | No | AS | No | The Contact's full name, formed by joining the FIRSTNAME and LASTNAME fields. |
| COMPANY_ACCNO | No | INTEGER | No | If the Contact is associated with a company (Debtor, Creditor or Non Account) the company's ID number is recorded here. |
| COMPANY_ACCTYPE | No | INTEGER | No | If the Contact is associated with a company (Debtor, Creditor or Non Account) the type of company account is recorded here:
1 = Debtor account
2 = Creditor account
3 = Non Account |
| MSN_ID | No | VARCHAR(45) | No | The Contact's MSN ID number. |
| YAHOO_ID | No | VARCHAR(45) | No | The Contact's Yahoo! ID number. |
| SKYPE_ID | No | VARCHAR(45) | No | The Contact's Skype ID number. |
| LAST_UPDATED | No | DATETIME | No | The date and time when the Contact record was last updated. |
| SUB1 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 1 is ticked on the Marketing tab. |
| SUB2 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 2 is ticked on the Marketing tab. |
| SUB3 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 3 is ticked on the Marketing tab. |
| SUB4 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 4 is ticked on the Marketing tab. |
| SUB5 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 5 is ticked on the Marketing tab. |
| SUB6 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 6 is ticked on the Marketing tab. |
| SUB7 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 7 is ticked on the Marketing tab. |
| SUB8 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 8 is ticked on the Marketing tab. |
| SUB9 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 9 is ticked on the Marketing tab. |
| SUB10 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 10 is ticked on the Marketing tab. |
| SUB11 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 11 is ticked on the Marketing tab. |
| SUB12 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 12 is ticked on the Marketing tab. |
| SUB13 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 13 is ticked on the Marketing tab. |
| SUB14 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 14 is ticked on the Marketing tab. |
| SUB15 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 15 is ticked on the Marketing tab. |
| SUB16 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 16 is ticked on the Marketing tab. |
| SUB17 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 17 is ticked on the Marketing tab. |
| SUB18 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 18 is ticked on the Marketing tab. |
| SUB19 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 19 is ticked on the Marketing tab. |
| SUB20 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 20 is ticked on the Marketing tab. |
| SUB21 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 21 is ticked on the Marketing tab. |
| SUB22 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 22 is ticked on the Marketing tab. |
| SUB23 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 23 is ticked on the Marketing tab. |
| SUB24 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 24 is ticked on the Marketing tab. |
| SUB25 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 25 is ticked on the Marketing tab. |
| SUB26 | No | CHAR(1) | No | This field is set to 'Y' if Marketing Class number 26 is ticked on the Marketing tab. |
| SYNC_CONTACTS | No | CHAR(1) | Yes | This field is set to 'Y' if the Contact is set to sync with Microsoft Outlook. |
| LINKEDIN | No | VARCHAR(20) | No | The Contact's LinkedIn account ID. |
| TWITTER | No | VARCHAR(500) | No | The Contact's Twitter username. |
| FACEBOOK | No | VARCHAR(500) | No | The Contact's Facebook username or ID number. |
| OPTOUT_EMARKETING | No | CHAR(1) | No | This field is set to 'Y' if the Contact's Opt-Out eMarketing flag is ticked. Contacts who have opted out of eMarketing will not be included in campaign waves for campaigns whose Campaign Type is set to "Marketing" (EXO Business CRM). |
| CAMPAIGN_WAVE_SEQNO | No | INTEGER | No | If the Contact is included in a campaign wave (EXO Business CRM), the wave's ID number is stored here. |
| LATITUDE | No | FLOAT(53) | No | The Contact's latitude. |
| LONGITUDE | No | FLOAT(53) | No | The Contact's longitude. |
| GEOCODE_STATUS | No | INTEGER | No | Result of the geocode lookup. Will be one of:
Null or -1 - lookup not yet attempted
0 - lookup successful
>0 - lookup failed |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| ADVERT_TYPES_CONTACTS | Non Identifying | ADVERT_TYPES | CONTACTS | Zero Or More |
| CAMPAIGN_WAVE_CONTACTS | Non Identifying | CAMPAIGN_WAVE | CONTACTS | Zero Or More |
| CONTACTS_CAMPAIGN_WAVE_AUDIT | Non Identifying | CONTACTS | CAMPAIGN_WAVE_AUDIT | Zero Or More |
| CONTACTS_CONTACT_LIST_ITEM | Non Identifying | CONTACTS | CONTACT_LIST_ITEM | Zero Or More |
| CONTACTS_CR_CONTACTS | Non Identifying | CONTACTS | CR_CONTACTS | Zero Or More |
| CONTACTS_CR_CONT_HIST | Non Identifying | CONTACTS | CR_CONT_HIST | Zero Or More |
| CONTACTS_CR_TRANS | Non Identifying | CONTACTS | CR_TRANS | Zero Or More |
| CONTACTS_DR_CONTACTS | Non Identifying | CONTACTS | DR_CONTACTS | Zero Or More |
| CONTACTS_TASKS | Non Identifying | CONTACTS | TASKS | Zero Or More |
| STAFF_CONTACTS | Non Identifying | STAFF | CONTACTS | Zero Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (SYNC_CONTACTS) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| ADVERT_TYPES_CONTACTS | Foreign Key | Table Constraint | FOREIGN KEY (ADVERTSOURCE) REFERENCES ADVERT_TYPES(SEQNO) |
| CAMPAIGN_WAVE_CONTACTS | Foreign Key | Table Constraint | FOREIGN KEY (CAMPAIGN_WAVE_SEQNO) REFERENCES CAMPAIGN_WAVE(SEQNO) |
| DF__CONTACTS__ADVERT__08C105B8 | Default | Column Constraint | ADVERTSOURCE DEFAULT 0 |
| DF__CONTACTS__CAMPAI__5AE5F7C6 | Default | Column Constraint | CAMPAIGN_WAVE_SEQNO DEFAULT -1 |
| DF__CONTACTS__ISACTI__74BA0D0B | Default | Column Constraint | ISACTIVE DEFAULT Y |
| DF__CONTACTS__LAST_U__6C3AA0FA | Default | Column Constraint | LAST_UPDATED DEFAULT getdate() |
| DF__CONTACTS__OPTOUT__5250B1C5 | Default | Column Constraint | OPTOUT_EMARKETING DEFAULT N |
| DF__CONTACTS__SALESN__09B529F1 | Default | Column Constraint | SALESNO DEFAULT 0 |
| DF__CONTACTS__SUB10__348057BC | Default | Column Constraint | SUB10 DEFAULT N |
| DF__CONTACTS__SUB11__3668A02E | Default | Column Constraint | SUB11 DEFAULT N |
| DF__CONTACTS__SUB12__3850E8A0 | Default | Column Constraint | SUB12 DEFAULT N |
| DF__CONTACTS__SUB13__3A393112 | Default | Column Constraint | SUB13 DEFAULT N |
| DF__CONTACTS__SUB14__3C217984 | Default | Column Constraint | SUB14 DEFAULT N |
| DF__CONTACTS__SUB15__3E09C1F6 | Default | Column Constraint | SUB15 DEFAULT N |
| DF__CONTACTS__SUB16__3FF20A68 | Default | Column Constraint | SUB16 DEFAULT N |
| DF__CONTACTS__SUB17__41DA52DA | Default | Column Constraint | SUB17 DEFAULT N |
| DF__CONTACTS__SUB18__43C29B4C | Default | Column Constraint | SUB18 DEFAULT N |
| DF__CONTACTS__SUB19__45AAE3BE | Default | Column Constraint | SUB19 DEFAULT N |
| DF__CONTACTS__SUB1__2355CBBA | Default | Column Constraint | SUB1 DEFAULT N |
| DF__CONTACTS__SUB20__47932C30 | Default | Column Constraint | SUB20 DEFAULT N |
| DF__CONTACTS__SUB21__497B74A2 | Default | Column Constraint | SUB21 DEFAULT N |
| DF__CONTACTS__SUB22__4B63BD14 | Default | Column Constraint | SUB22 DEFAULT N |
| DF__CONTACTS__SUB23__4D4C0586 | Default | Column Constraint | SUB23 DEFAULT N |
| DF__CONTACTS__SUB24__4F344DF8 | Default | Column Constraint | SUB24 DEFAULT N |
| DF__CONTACTS__SUB25__511C966A | Default | Column Constraint | SUB25 DEFAULT N |
| DF__CONTACTS__SUB26__5304DEDC | Default | Column Constraint | SUB26 DEFAULT N |
| DF__CONTACTS__SUB2__253E142C | Default | Column Constraint | SUB2 DEFAULT N |
| DF__CONTACTS__SUB3__27265C9E | Default | Column Constraint | SUB3 DEFAULT N |
| DF__CONTACTS__SUB4__290EA510 | Default | Column Constraint | SUB4 DEFAULT N |
| DF__CONTACTS__SUB5__2AF6ED82 | Default | Column Constraint | SUB5 DEFAULT N |
| DF__CONTACTS__SUB6__2CDF35F4 | Default | Column Constraint | SUB6 DEFAULT N |
| DF__CONTACTS__SUB7__2EC77E66 | Default | Column Constraint | SUB7 DEFAULT N |
| DF__CONTACTS__SUB8__30AFC6D8 | Default | Column Constraint | SUB8 DEFAULT N |
| DF__CONTACTS__SUB9__32980F4A | Default | Column Constraint | SUB9 DEFAULT N |
| DF__CONTACTS__SYNC_C__657894D2 | Default | Column Constraint | SYNC_CONTACTS DEFAULT Y |
| PK__CONTACTS__73C5E8D2 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
| STAFF_CONTACTS | Foreign Key | Table Constraint | FOREIGN KEY (SALESNO) REFERENCES STAFF(STAFFNO) |
Entity: CURRENCIES
| Comment | This table contains details of all currencies used in the EXO Business system. |
| Primary key columns | CURRENCYNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| CURRENCYNO | Yes | INTEGER | Yes | A unique ID code for the currency. |
| CURRCODE | No | VARCHAR(3) | No | The three-letter currency code, e.g. AUD, NZD. |
| CURRNAME | No | VARCHAR(30) | No | The descriptive name for the currency. |
| BUYRATE | No | FLOAT(53) | No | The buy rate (for purchases). |
| SELLRATE | No | FLOAT(53) | No | The sell rate (for sales). |
| CURRSYMBOL | No | VARCHAR(5) | No | The symbol for the currency, e.g. $, £. |
| AVERAGE_RATE | No | FLOAT(53) | No | This field relates to the Intercompany module. |
| CLOSING_RATE | No | FLOAT(53) | No | This field relates to the Intercompany module. |
| RE_MOVEMENTS | No | FLOAT(53) | No | This field relates to the Intercompany module. |
| ALERTPC | No | INTEGER | Yes | The % Change Alert for the currency - when editing currencies, the user will be alerted if the currency is changed by a percentage greater than this. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CURRENCIES_CR_ALLOCATIONS | Non Identifying | CURRENCIES | CR_ALLOCATIONS | Zero Or More |
| CURRENCIES_CR_INVLINES | Non Identifying | CURRENCIES | CR_INVLINES | Zero Or More |
| CURRENCIES_CR_TRANS | Non Identifying | CURRENCIES | CR_TRANS | Zero Or More |
| CURRENCIES_DR_ALLOCATIONS | Non Identifying | CURRENCIES | DR_ALLOCATIONS | Zero Or More |
| CURRENCIES_DR_INVLINES | Non Identifying | CURRENCIES | DR_INVLINES | Zero Or More |
| CURRENCIES_DR_TRANS | Non Identifying | CURRENCIES | DR_TRANS | Zero Or More |
| CURRENCIES_GLACCS | Non Identifying | CURRENCIES | GLACCS | Zero Or More |
| CURRENCIES_JOBCOST_LINES | Non Identifying | CURRENCIES | JOBCOST_LINES | Zero Or More |
| CURRENCIES_JOB_CONTRACT_BILLINGS | Non Identifying | CURRENCIES | JOB_CONTRACT_BILLINGS | Zero Or More |
| CURRENCIES_JOB_TRANSACTIONS | Non Identifying | CURRENCIES | JOB_TRANSACTIONS | Zero Or More |
| CURRENCIES_OPPORTUNITY_QUOTE | Non Identifying | CURRENCIES | OPPORTUNITY_QUOTE | Zero Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (ALERTPC) |
| Not Null | Column Constraint | NOT NULL (CURRENCYNO) |
| DF__CURRENCIE__ALERT__25BE0270 | Default | Column Constraint | ALERTPC DEFAULT 5 |
| DF__CURRENCIE__AVERA__17642ACA | Default | Column Constraint | AVERAGE_RATE DEFAULT 0 |
| DF__CURRENCIE__BUYRA__2CFD9AD7 | Default | Column Constraint | BUYRATE DEFAULT 0 |
| DF__CURRENCIE__CLOSI__18584F03 | Default | Column Constraint | CLOSING_RATE DEFAULT 0 |
| DF__CURRENCIE__RE_MO__2B6B2377 | Default | Column Constraint | RE_MOVEMENTS DEFAULT 0 |
| DF__CURRENCIE__SELLR__2DF1BF10 | Default | Column Constraint | SELLRATE DEFAULT 0 |
| PK__CURRENCIE__CURRE__2C09769E | Primary Key | Table Constraint | PRIMARY KEY (CURRENCYNO) |
Triggers:
| Trigger name | Code |
| CURRENCY_RATE_CHANGE | CREATE TRIGGER [dbo].[CURRENCY_RATE_CHANGE] ON [dbo].[CURRENCIES]
FOR INSERT, UPDATE AS
BEGIN
DECLARE @DR_PERIOD INT
DECLARE @CR_PERIOD INT
DECLARE @STK_PERIOD INT
DECLARE @GL_PERIOD INT
SELECT @DR_PERIOD=SEQNO FROM PERIOD_STATUS WHERE AGE=0 AND LEDGER ='D'
SELECT @CR_PERIOD=SEQNO FROM PERIOD_STATUS WHERE AGE=0 AND LEDGER ='C'
SELECT @STK_PERIOD=SEQNO FROM PERIOD_STATUS WHERE AGE=0 AND LEDGER ='S'
SELECT @GL_PERIOD=SEQNO FROM PERIOD_STATUS WHERE AGE=0 AND LEDGER ='G'
SET NOCOUNT ON
INSERT INTO CURRENCY_RATECHANGES
(CURRENCYNO, CURRCODE, NEWBUYRATE, NEWSELLRATE, DR_PERIOD, CR_PERIOD, STK_PERIOD, GL_PERIOD )
SELECT INSERTED.CURRENCYNO, INSERTED.CURRCODE,
INSERTED.BUYRATE, INSERTED.SELLRATE, @DR_PERIOD, @CR_PERIOD, @STK_PERIOD, @GL_PERIOD
FROM INSERTED
SET NOCOUNT OFF
END
|
Entity: DR_ACCGROUP2S
| Comment | This table stores the Secondary Groups for Debtors (a grouping on top of the primary grouping - see DR_ACCGROUPS). |
| Primary key columns | ACCGROUP |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| ACCGROUP | Yes | INTEGER | Yes | A unique ID for the Secondary Debtor Group. |
| GROUPNAME | No | VARCHAR(30) | No | The name of the group. |
| REPORTCODE | No | VARCHAR(15) | No | A report code for the group. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| DR_ACCGROUP2S_DR_ACCS | Non Identifying | DR_ACCGROUP2S | DR_ACCS | Zero Or More |
| FK_CRM_BUDGET_EXCLUDED_ACCGROUP2S_DR_ACCGROUP2S | Non Identifying | DR_ACCGROUP2S | CRM_BUDGET_EXCLUDED_ACCGROUP2S | Zero Or More |
| FK_CRM_BUDGET_LINE_DR_ACCGROUP2S | Non Identifying | DR_ACCGROUP2S | CRM_BUDGET_LINE | Zero Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (ACCGROUP) |
| PK__DR_ACCGROUP2S__46B27FE2 | Primary Key | Table Constraint | PRIMARY KEY (ACCGROUP) |
Entity: DR_ACCGROUPS
| Comment | This table stores the Primary Groups for Debtors (the finer level of grouping - compare DR_ACCGROUP2S). |
| Primary key columns | ACCGROUP |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| ACCGROUP | Yes | INTEGER | Yes | A unique ID for the Primary Debtor Group. |
| GROUPNAME | No | VARCHAR(30) | No | The name of the group. |
| REPORTCODE | No | VARCHAR(15) | No | A report code for the group. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| DR_ACCGROUPS_DR_ACCS | Non Identifying | DR_ACCGROUPS | DR_ACCS | One Or More |
| DR_ACCGROUPS_DR_PRICES | Non Identifying | DR_ACCGROUPS | DR_PRICES | One Or More |
| DR_ACCGROUPS_DR_PRICE_POLICY_ACC | Non Identifying | DR_ACCGROUPS | DR_PRICE_POLICY_ACC | One Or More |
| FK_CRM_BUDGET_EXCLUDED_ACCGROUPS_DR_ACCGROUPS | Non Identifying | DR_ACCGROUPS | CRM_BUDGET_EXCLUDED_ACCGROUPS | Zero Or More |
| FK_CRM_BUDGET_LINE_DR_ACCGROUPS | Non Identifying | DR_ACCGROUPS | CRM_BUDGET_LINE | Zero Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (ACCGROUP) |
| PK__DR_ACCGROUPS__758D6A5C | Primary Key | Table Constraint | PRIMARY KEY (ACCGROUP) |
Entity: DR_ACCS
| Comment | This table contains information about Debtor accounts. |
| Primary key columns | ACCNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| ACCNO | Yes | INTEGER | Yes | The unique ID number for the Debtor account. |
| NAME | No | VARCHAR(60) | No | The Debtor's name. |
| ADDRESS1 | No | VARCHAR(30) | No | Postal address line 1. |
| ADDRESS2 | No | VARCHAR(30) | No | Postal address line 2. |
| ADDRESS3 | No | VARCHAR(30) | No | Postal address line 3. |
| ADDRESS4 | No | VARCHAR(30) | No | Postal address line 4 |
| ADDRESS5 | No | VARCHAR(30) | No | Postal address line 5. This line is only available on the Debtor Account Details screen if the Additional Postal Address Row profile setting is enabled. |
| 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. |
| PHONE | No | VARCHAR(30) | No | The Debtor's phone number. |
| FAX | No | VARCHAR(30) | No | The Debtor's fax number. |
| EMAIL | No | VARCHAR(60) | No | The Debtor's email address. |
| CREDLIMIT | No | FLOAT(53) | No | The credit limit for the Debtor account. |
| ACCGROUP | No | INTEGER | No | The Debtor's primary Debtor Account Group. |
| SALESNO | No | INTEGER | No | ID number of the salesperson assigned to the Debtor account. |
| LASTMONTH | No | FLOAT(53) | No | Turnover for last month. |
| LASTYEAR | No | FLOAT(53) | No | Turnover for last year. |
| AGEDBAL0 | No | FLOAT(53) | No | Aged Balance for the current month. |
| AGEDBAL1 | No | FLOAT(53) | No | Aged Balance for the previous month. |
| AGEDBAL2 | No | FLOAT(53) | No | Aged Balance for two months back. |
| AGEDBAL3 | No | FLOAT(53) | No | Aged Balance for three months back. |
| CREDITSTATUS | No | INTEGER | No | Credit terms for the Debtor. |
| DISCOUNTLEVEL | No | INTEGER | No | The Debtor's discount level - this is only populated via EXO APIs. |
| OPENITEM | No | CHAR(1) | No | Contains "Y" if this is not a balance brought forward account. |
| INVOICETYPE | No | INTEGER | No | This field only applies to Australian databases where M-Powered Invoices are enabled. If set to 1, search results on the Transactions tab of the Debtor Account Details window will only include accounts with the M-Powered invoice type. |
| NOTES | No | VARCHAR(4096) | No | All notes entered for the Debtor on the Notes tab. |
| MONTHVAL | No | FLOAT(53) | No | Turnover for the current month. |
| YEARVAL | No | FLOAT(53) | No | Turnover for the current year. |
| STARTDATE | No | DATETIME | No | The date that the Debtor account was created on. |
| SORTCODE | No | VARCHAR(12) | No | Banks National Clearing Code or Routing Number used by some banks. Expose as an Extra Field to use. |
| BANK | No | VARCHAR(20) | No | The Bank detail from the last update of a payment header. Populated via right-clicking on a payment on the Transactions tab and selecting Edit Ref Field. This option is only available if the profile setting "Permitted level of access to debtor transaction editing" (EDITDRTRANS) is set to allow transaction editing. |
| BANK_ACCOUNT | No | VARCHAR(40) | No | The Debtor's bank account number, used for Direct Debit payments. |
| BANK_ACC_NAME | No | VARCHAR(40) | No | The Debtor's bank account name, used for Direct Debit payments. |
| BSBNO | No | VARCHAR(40) | No | The Debtor's Bank State Branch number (Australian databases only). |
| D_DEBIT_FAX | No | CHAR(1) | No | If set to 'Y', Direct Debit payment advice will be sent to the Debtor by fax. |
| D_DEBIT_PRINT | No | CHAR(1) | No | If set to 'Y', printed Direct Debit payment advice will be sent to the Debtor. |
| D_DEBIT_EMAIL | No | CHAR(1) | No | If set to 'Y', Direct Debit payment advice will be sent to the Debtor by email. |
| PAY_TYPE | No | INTEGER | No | The default payment method. |
| BRANCH | No | VARCHAR(30) | No | The Branch detail from the last update of a payment header. Populated via right-clicking on a payment on the Transactions tab and selecting Edit Ref Field. This option is only available if the profile setting "Permitted level of access to debtor transaction editing" (EDITDRTRANS) is set to allow transaction editing. |
| DRAWER | No | VARCHAR(30) | No | The Drawer detail from the last update of a payment header. Populated via right-clicking on a payment on the Transactions tab and selecting Edit Ref Field. This option is only available if the profile setting "Permitted level of access to debtor transaction editing" (EDITDRTRANS) is set to allow transaction editing. |
| TAXSTATUS | No | INTEGER | No | The Override GST rate for the Debtor. |
| PRICENO | No | INTEGER | No | The Base price for the Debtor. |
| AUTOBILLCODE | No | VARCHAR(23) | No | The Bill of Materials (Order Template) used by the Debtor. |
| ALPHACODE | No | VARCHAR(15) | No | A non-unique alphanumeric ID code for the Debtor. Can be used for sorting accounts alphabetically, e.g. give "The Warehouse" the code "WARE" to sort under W instead of T. Sometimes used as an alternate account reference. |
| HEAD_ACCNO | No | INTEGER | Yes | The ACCNO of the Debtor's head office account. If no head office, this is set to -1. |
| PASS_WORD | No | VARCHAR(30) | No | Used for integrated web shops. |
| CURRENCYNO | No | INTEGER | Yes | The currency of the Debtor account. |
| ALERT | No | VARCHAR(60) | No | Pop-up alert text to display when dealing with the Debtor. |
| STATEMENT | No | CHAR(1) | No | How statements will be sent to the Debtor. P = print, E = email, B = both, N = none. |
| INVFILENO | No | INTEGER | No | The number that is used in the name of the invoice template that will be used for this Debtor, e.g. 1 for Invoice1.clf. |
| PROMPTPAY_PC | No | FLOAT(53) | No | Prompt payment discount percentage. |
| PROMPTPAY_AMT | No | FLOAT(53) | No | Prompt payment discount amount. |
| ISACTIVE | No | CHAR(1) | No | Whether the account is active (Y) or inactive (N). |
| BAD_CHEQUE | No | CHAR(1) | No | If 'Y', cheques are not accepted from the Debtor. Set by the "Do Not Accept Cheque" flag on the Details 2 tab. |
| BRANCHNO | No | INTEGER | No | Branch number - only used by specific sites. |
| LAST_UPDATED | No | DATETIME | No | Time and date stamp when the Debtor's account details were last updated. |
| TAXREG | No | VARCHAR(30) | No | GST/ABN number of the Debtor. |
| STOPCREDIT | No | CHAR(1) | No | If 'Y', the Debtor is on Stop Credit, and no further transactions will take place. |
| POST_CODE | No | VARCHAR(12) | No | The Debtor's postal code. |
| GLCONTROLACC | No | INTEGER | No | Not currently used. |
| GLCONTROLSUBACC | No | INTEGER | No | Not currently used. |
| PRIOR_AGEDBAL0 | No | FLOAT(53) | No | Prior Aged Balance for the current month. |
| PRIOR_AGEDBAL1 | No | FLOAT(53) | No | Prior Aged Balance for the previous month. |
| PRIOR_AGEDBAL2 | No | FLOAT(53) | No | Prior Aged Balance for two months back. |
| PRIOR_AGEDBAL3 | No | FLOAT(53) | No | Prior Aged Balance for three months back. |
| BALANCE | No | AS | No | Current balance. |
| PRIOR_BALANCE | No | AS | No | Balance as at the prior period. |
| ACCGROUP2 | No | INTEGER | No | The Debtor's secondary Debtor Account Group. |
| FREIGHT_FREE | No | CHAR(1) | Yes | Used to suppress the freight prompt on Invoices or Sales Orders for this Debtor account. Can be superseded by the FREIGHT_FREE settings in price rules. |
| COURIER_DEPOT_SEQNO | No | INTEGER | No | Related to the courier add-on module. Not currently supported. |
| KEEPTRANSACTIONS | No | CHAR(1) | Yes | If 'N', all branch transactions are transferred to head office account. |
| NEED_ORDERNO | No | CHAR(1) | Yes | If set to 'Y', order numbers are checked against past order numbers to prevent duplication. Only applies when the profile setting "Validate Debtor Order Numbers" (VALIDATE_DR_ORDERNO) is enabled. |
| PRICEGROUP | No | INTEGER | No | Price Group for the Debtor. New accounts get this from profile setting "Default debtor price group for new debtors" (DEFDRPRICEGROUP). |
| ALLOW_RESTRICTED_STOCK | No | CHAR(1) | Yes | If 'Y', it is possible to sell stock items that have been flagged as restricted goods to this Debtor. If the profile setting "Use customised customer stock restrictions logic" (CUSTOM_STOCK_RESTRICTION) is enabled, a custom function (FN_RESTRICTED_ITEM) is used instead of this field. |
| PRIVATE_ACC | No | CHAR(1) | Yes | If 'Y', the debtor account is classed as private. It can only be accessed by users who have the profile setting "Allow access to private debtor accounts" (ALLOW_ACCESS_TO_PRIVATE_DR_ACC) enabled. |
| ISTEMPLATE | No | CHAR(1) | Yes | If 'Y', this Debtor account can be used as a template for Non Accounts and new POS accounts. |
| WEBSITE | No | VARCHAR(50) | No | URL of the Debtor's website. |
| AVE_DAYS_TO_PAY | No | INTEGER | Yes | Average Debtor days, calculated by the Stored Procedure DR_CR_ALLOCATION_AVE_DAYS_SP every time you access the Analysis tab. |
| INVOICE_TYPE | No | VARCHAR(20) | No | The type of invoice to send to the Debtor: DEFAULT, STANDARD or M-POWERED. Only applies to Australian databases. |
| STATEMENT_CONTACT_SEQNO | No | INTEGER | No | When sending statements via email, the email will be sent to this address. If this field is blank, the email address of the Debtor account's default contact is used. If the account does not have a default Contact, the Debtor's email address (EMAIL) is used. |
| LINKEDIN | No | VARCHAR(20) | No | The Debtor's LinkedIn account ID. |
| TWITTER | No | VARCHAR(500) | No | The Debtor's Twitter username. |
| FACEBOOK | No | VARCHAR(500) | No | The Debtor's Facebook account ID or username. |
| LATITUDE | No | FLOAT(53) | No | The Debtor's latitude. |
| LONGITUDE | No | FLOAT(53) | No | The Debtor's longitude. |
| GEOCODE_STATUS | No | INTEGER | No | Result of the geocode lookup. Will be one of:
Null or -1 - lookup not yet attempted
0 - lookup successful
>0 - lookup failed |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| DR_ACCGROUP2S_DR_ACCS | Non Identifying | DR_ACCGROUP2S | DR_ACCS | Zero Or More |
| DR_ACCGROUPS_DR_ACCS | Non Identifying | DR_ACCGROUPS | DR_ACCS | One Or More |
| DR_ACCS_DR_ADDRESSES | Non Identifying | DR_ACCS | DR_ADDRESSES | One Or More |
| DR_ACCS_DR_CONTACTS | Non Identifying | DR_ACCS | DR_CONTACTS | Zero Or More |
| DR_ACCS_DR_CONT_HIST | Non Identifying | DR_ACCS | DR_CONT_HIST | Zero Or More |
| DR_ACCS_DR_PRICES | Non Identifying | DR_ACCS | DR_PRICES | One Or More |
| DR_ACCS_DR_PRICE_POLICY_ACC | Non Identifying | DR_ACCS | DR_PRICE_POLICY_ACC | One Or More |
| DR_ACCS_DR_TRANS | Non Identifying | DR_ACCS | DR_TRANS | Zero Or More |
| DR_ACCS_JOBCOST_HDR | Non Identifying | DR_ACCS | JOBCOST_HDR | Zero Or More |
| DR_PRICEGROUPS_DR_ACCS | Non Identifying | DR_PRICEGROUPS | DR_ACCS | One Or More |
| FK_CRM_BUDGET_EXCLUDED_DR_ACCS_DR_ACCS | Non Identifying | DR_ACCS | CRM_BUDGET_EXCLUDED_DR_ACCS | Zero Or More |
| FK_CRM_BUDGET_LINE_DR_ACCS | Non Identifying | DR_ACCS | CRM_BUDGET_LINE | Zero Or More |
| STAFF_DR_ACCS | Non Identifying | STAFF | DR_ACCS | One Or More |
Indexes:
| Name | Index columns | Index type |
| DR_ACCS_NAME | NAME ASC | |
| DR_ACCS_PAY_TYPE | PAY_TYPE ASC | |
| DR_ACCS_TAXSTATUS | TAXSTATUS ASC | |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (AVE_DAYS_TO_PAY) |
| Not Null | Column Constraint | NOT NULL (ACCNO) |
| Not Null | Column Constraint | NOT NULL (ISTEMPLATE) |
| Not Null | Column Constraint | NOT NULL (PRIVATE_ACC) |
| Not Null | Column Constraint | NOT NULL (ALLOW_RESTRICTED_STOCK) |
| Not Null | Column Constraint | NOT NULL (NEED_ORDERNO) |
| Not Null | Column Constraint | NOT NULL (KEEPTRANSACTIONS) |
| Not Null | Column Constraint | NOT NULL (FREIGHT_FREE) |
| Not Null | Column Constraint | NOT NULL (CURRENCYNO) |
| Not Null | Column Constraint | NOT NULL (HEAD_ACCNO) |
| DF_DR_ACCS_CURRENCYNO | Default | Column Constraint | CURRENCYNO DEFAULT 0 |
| DF_DR_ACCS_HEAD_ACCNO | Default | Column Constraint | HEAD_ACCNO DEFAULT -1 |
| DF__DR_ACCS__ACCGROU__4E9398CC | Default | Column Constraint | ACCGROUP2 DEFAULT 0 |
| DF__DR_ACCS__ACCGROU__7A521F79 | Default | Column Constraint | ACCGROUP DEFAULT 0 |
| DF__DR_ACCS__AGEDBAL__000AF8CF | Default | Column Constraint | AGEDBAL2 DEFAULT 0 |
| DF__DR_ACCS__AGEDBAL__00FF1D08 | Default | Column Constraint | AGEDBAL3 DEFAULT 0 |
| DF__DR_ACCS__AGEDBAL__7E22B05D | Default | Column Constraint | AGEDBAL0 DEFAULT 0 |
| DF__DR_ACCS__AGEDBAL__7F16D496 | Default | Column Constraint | AGEDBAL1 DEFAULT 0 |
| DF__DR_ACCS__ALLOW_R__78BFA819 | Default | Column Constraint | ALLOW_RESTRICTED_STOCK DEFAULT Y |
| DF__DR_ACCS__AVE_DAY__6BE59B3F | Default | Column Constraint | AVE_DAYS_TO_PAY DEFAULT -1 |
| DF__DR_ACCS__BAD_CHE__48EFCE0F | Default | Column Constraint | BAD_CHEQUE DEFAULT N |
| DF__DR_ACCS__BRANCHN__5EDF0F2E | Default | Column Constraint | BRANCHNO DEFAULT 0 |
| DF__DR_ACCS__CREDITS__02E7657A | Default | Column Constraint | CREDITSTATUS DEFAULT 0 |
| DF__DR_ACCS__CREDLIM__795DFB40 | Default | Column Constraint | CREDLIMIT DEFAULT 0 |
| DF__DR_ACCS__DISCOUN__03DB89B3 | Default | Column Constraint | DISCOUNTLEVEL DEFAULT 0 |
| DF__DR_ACCS__D_DEBIT__4F7CD00D | Default | Column Constraint | D_DEBIT_FAX DEFAULT N |
| DF__DR_ACCS__D_DEBIT__5070F446 | Default | Column Constraint | D_DEBIT_PRINT DEFAULT N |
| DF__DR_ACCS__D_DEBIT__5165187F | Default | Column Constraint | D_DEBIT_EMAIL DEFAULT N |
| DF__DR_ACCS__FREIGHT__0C90CB45 | Default | Column Constraint | FREIGHT_FREE DEFAULT N |
| DF__DR_ACCS__GEOCODE__44628521 | Default | Column Constraint | GEOCODE_STATUS DEFAULT NULL |
| DF__DR_ACCS__GLCONTR__379037E3 | Default | Column Constraint | GLCONTROLACC DEFAULT 0 |
| DF__DR_ACCS__GLCONTR__38845C1C | Default | Column Constraint | GLCONTROLSUBACC DEFAULT 0 |
| DF__DR_ACCS__INVFILE__18C19800 | Default | Column Constraint | INVFILENO DEFAULT 0 |
| DF__DR_ACCS__INVOICE__06B7F65E | Default | Column Constraint | INVOICETYPE DEFAULT 0 |
| DF__DR_ACCS__INVOICE__77E15DD0 | Default | Column Constraint | INVOICE_TYPE DEFAULT DEFAULT |
| DF__DR_ACCS__ISACTIV__25A691D2 | Default | Column Constraint | ISACTIVE DEFAULT Y |
| DF__DR_ACCS__ISTEMPL__5ECACBEC | Default | Column Constraint | ISTEMPLATE DEFAULT N |
| DF__DR_ACCS__KEEPTRA__7EB7AD3A | Default | Column Constraint | KEEPTRANSACTIONS DEFAULT Y |
| DF__DR_ACCS__LASTMON__7C3A67EB | Default | Column Constraint | LASTMONTH DEFAULT 0 |
| DF__DR_ACCS__LASTYEA__7D2E8C24 | Default | Column Constraint | LASTYEAR DEFAULT 0 |
| DF__DR_ACCS__MONTHVA__07AC1A97 | Default | Column Constraint | MONTHVAL DEFAULT 0 |
| DF__DR_ACCS__NEED_OR__4050666D | Default | Column Constraint | NEED_ORDERNO DEFAULT N |
| DF__DR_ACCS__OPENITE__04CFADEC | Default | Column Constraint | OPENITEM DEFAULT Y |
| DF__DR_ACCS__PAY_TYP__52593CB8 | Default | Column Constraint | PAY_TYPE DEFAULT 0 |
| DF__DR_ACCS__PRICEGR__190C7C1A | Default | Column Constraint | PRICEGROUP DEFAULT 0 |
| DF__DR_ACCS__PRICENO__0D64F3ED | Default | Column Constraint | PRICENO DEFAULT 1 |
| DF__DR_ACCS__PRIOR_A__000AF8CF | Default | Column Constraint | PRIOR_AGEDBAL3 DEFAULT 0 |
| DF__DR_ACCS__PRIOR_A__7D2E8C24 | Default | Column Constraint | PRIOR_AGEDBAL0 DEFAULT 0 |
| DF__DR_ACCS__PRIOR_A__7E22B05D | Default | Column Constraint | PRIOR_AGEDBAL1 DEFAULT 0 |
| DF__DR_ACCS__PRIOR_A__7F16D496 | Default | Column Constraint | PRIOR_AGEDBAL2 DEFAULT 0 |
| DF__DR_ACCS__PRIVATE__0FA30D71 | Default | Column Constraint | PRIVATE_ACC DEFAULT N |
| DF__DR_ACCS__PROMPTP__3CFEF876 | Default | Column Constraint | PROMPTPAY_PC DEFAULT 0 |
| DF__DR_ACCS__PROMPTP__3DF31CAF | Default | Column Constraint | PROMPTPAY_AMT DEFAULT 0 |
| DF__DR_ACCS__SALESNO__7B4643B2 | Default | Column Constraint | SALESNO DEFAULT 0 |
| DF__DR_ACCS__STARTDA__09946309 | Default | Column Constraint | STARTDATE DEFAULT getdate() |
| DF__DR_ACCS__STATEME__01DE32A8 | Default | Column Constraint | STATEMENT DEFAULT Y |
| DF__DR_ACCS__STATEME__5FF4C5A6 | Default | Column Constraint | STATEMENT_CONTACT_SEQNO DEFAULT -1 |
| DF__DR_ACCS__STOPCRE__0CA5D9DE | Default | Column Constraint | STOPCREDIT DEFAULT N |
| DF__DR_ACCS__TAXSTAT__0C70CFB4 | Default | Column Constraint | TAXSTATUS DEFAULT 0 |
| DF__DR_ACCS__YEARVAL__08A03ED0 | Default | Column Constraint | YEARVAL DEFAULT 0 |
| DR_ACCGROUP2S_DR_ACCS | Foreign Key | Table Constraint | FOREIGN KEY (ACCGROUP2) REFERENCES DR_ACCGROUP2S(ACCGROUP) |
| DR_ACCGROUPS_DR_ACCS | Foreign Key | Table Constraint | FOREIGN KEY (ACCGROUP) REFERENCES DR_ACCGROUPS(ACCGROUP) |
| DR_PRICEGROUPS_DR_ACCS | Foreign Key | Table Constraint | FOREIGN KEY (PRICEGROUP) REFERENCES DR_PRICEGROUPS(GROUPNO) |
| PK_DR_ACCS | Primary Key | Table Constraint | PRIMARY KEY (ACCNO) |
| STAFF_DR_ACCS | Foreign Key | Table Constraint | FOREIGN KEY (SALESNO) REFERENCES STAFF(STAFFNO) |
Entity: DR_ADDRESSES
| Comment | This table stores the additional delivery addresses for a Debtor account. These are used in places like Sales Orders, where the user can select a delivery address for the order. |
| Primary key columns | SEQNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique ID number for the record. |
| ACCNO | No | INTEGER | No | The account number of the Debtor record that the address applies to. |
| 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. |
| COURIER_DEPOT_SEQNO | No | INTEGER | No | This field is no longer used - it relates to the old couriers module. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| DR_ACCS_DR_ADDRESSES | Non Identifying | DR_ACCS | DR_ADDRESSES | One Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| DR_ACCS_DR_ADDRESSES | Foreign Key | Table Constraint | FOREIGN KEY (ACCNO) REFERENCES DR_ACCS(ACCNO) |
| PK__DR_ADDRESSES__489AC854 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
Entity: DR_ALLOCATIONS
| Comment | This table stores details of Debtor allocations. |
| Primary key columns | SEQNO, ALLOCNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique ID number for the allocation. |
| ALLOCNO | Yes | INTEGER | Yes | A unique ID number for the allocation set. |
| TRANS_SEQNO | No | INTEGER | No | This field is a link back to the original transaction line in DR_TRANS table (DR_TRANS.seqno). |
| AMOUNT | No | FLOAT(53) | No | The amount allocated. |
| CURRENCY | No | INTEGER | No | The ID number of the currency in which the transaction took place. |
| TAKENUP | No | CHAR(1) | No | This field is a flag that specify whether or not the allocation has been a part of realised gain/losses. |
| ALLOCTIME | No | DATETIME | No | The date and time of the allocation. |
| PERIOD_SEQNO | No | INTEGER | Yes | The period in which the allocation took place. |
| AGE_STAMP | No | INTEGER | No | This is a historical field that has been superseded by the AGE calculated field. |
| AGE | No | AS | No | The allocation's age. This is calculated based on PERIOD_SEQNO. |
| EXCHRATE | No | FLOAT(53) | Yes | The exchange rate used for conversion at the time of the allocation. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CURRENCIES_DR_ALLOCATIONS | Non Identifying | CURRENCIES | DR_ALLOCATIONS | Zero Or More |
| DR_TRANS_DR_ALLOCATIONS | Non Identifying | DR_TRANS | DR_ALLOCATIONS | Zero Or More |
| PERIOD_STATUS_DR_ALLOCATIONS | Non Identifying | PERIOD_STATUS | DR_ALLOCATIONS | Zero Or More |
Indexes:
| Name | Index columns | Index type |
| DR_ALLOCATIONS_PERIOD_SEQNO_INDEX | PERIOD_SEQNO ASC | |
| DR_ALLOCATIONS_TRANS_SEQNO_INDEX | TRANS_SEQNO ASC | |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (EXCHRATE) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| Not Null | Column Constraint | NOT NULL (ALLOCNO) |
| Not Null | Column Constraint | NOT NULL (PERIOD_SEQNO) |
| CURRENCIES_DR_ALLOCATIONS | Foreign Key | Table Constraint | FOREIGN KEY (CURRENCY) REFERENCES CURRENCIES(CURRENCYNO) |
| DF__DR_ALLOCA__AGE_S__48D1692C | Default | Column Constraint | AGE_STAMP DEFAULT -1 |
| DF__DR_ALLOCA__ALLOC__02284B6B | Default | Column Constraint | ALLOCTIME DEFAULT getdate() |
| DF__DR_ALLOCA__AMOUN__5CF6C6BC | Default | Column Constraint | AMOUNT DEFAULT 0 |
| DF__DR_ALLOCA__CURRE__5DEAEAF5 | Default | Column Constraint | CURRENCY DEFAULT 0 |
| DF__DR_ALLOCA__EXCHR__7DE51B40 | Default | Column Constraint | EXCHRATE DEFAULT 1 |
| DF__DR_ALLOCA__PERIO__47DD44F3 | Default | Column Constraint | PERIOD_SEQNO DEFAULT 0 |
| DF__DR_ALLOCA__TAKEN__5EDF0F2E | Default | Column Constraint | TAKENUP DEFAULT N |
| DR_TRANS_DR_ALLOCATIONS | Foreign Key | Table Constraint | FOREIGN KEY (TRANS_SEQNO) REFERENCES DR_TRANS(SEQNO) |
| PERIOD_STATUS_DR_ALLOCATIONS | Foreign Key | Table Constraint | FOREIGN KEY (SEQNO) REFERENCES PERIOD_STATUS(PERIOD_SEQNO) |
| PK__DR_ALLOCATIONS__5C02A283 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO, ALLOCNO) |
Triggers:
| Trigger name | Code |
| update_draccbal_onalloc | CREATE TRIGGER [dbo].[update_draccbal_onalloc] on [dbo].[DR_ALLOCATIONS]
FOR INSERT
AS
declare @accno integer,
@age integer,
@trans_seqno integer,
@amount float,
@exchrate float
begin
SET NOCOUNT ON
select @trans_seqno=trans_seqno, @amount=amount from inserted
select @accno=accno, @age=age, @exchrate=exchrate from dr_trans where seqno=@trans_seqno
IF (@age=0) begin
UPDATE dr_accs
SET agedbal0=agedbal0-@amount
WHERE ACCNO=@ACCNO
end
ELSE IF (@age=1) begin
UPDATE dr_accs
SET agedbal1=agedbal1-@amount
WHERE ACCNO=@ACCNO
end
ELSE IF (@age=2) begin
UPDATE dr_accs
SET agedbal2=agedbal2-@amount
WHERE ACCNO=@ACCNO
end
ELSE BEGIN UPDATE dr_accs
SET agedbal3=agedbal3-@amount
WHERE ACCNO=@ACCNO
END
UPDATE [dbo].[DR_ALLOCATIONS]
SET EXCHRATE=@exchrate
WHERE TRANS_SEQNO=@trans_seqno
SET NOCOUNT OFF
end
|
| UPDATE_DRACCBAL_ONUNALLOC | CREATE TRIGGER [dbo].[UPDATE_DRACCBAL_ONUNALLOC] on [dbo].[DR_ALLOCATIONS]
FOR DELETE
AS
declare @accno integer,
@age integer,
@trans_seqno integer,
@amount float
begin
SET NOCOUNT ON
select @trans_seqno=trans_seqno, @amount=amount from deleted
select @accno=accno, @age=age from DR_trans where seqno=@trans_seqno
IF (@age=0) begin
UPDATE DR_ACCS
SET agedbal0=agedbal0+@amount
WHERE ACCNO=@ACCNO
end
ELSE IF (@age=1) begin
UPDATE DR_ACCS
SET agedbal1=agedbal1+@amount
WHERE ACCNO=@ACCNO
end
ELSE IF (@age=2) begin
UPDATE DR_ACCS
SET agedbal2=agedbal2+@amount
WHERE ACCNO=@ACCNO
end
ELSE BEGIN UPDATE DR_ACCS
SET agedbal3=agedbal3+@amount
WHERE ACCNO=@ACCNO
END
UPDATE DR_TRANS
SET ALLOCATEDBAL=ALLOCATEDBAL-@amount, ALLOCATED=0
WHERE SEQNO=@TRANS_SEQNO
SET NOCOUNT OFF
end
|
| UPDATE_DRACC_PRIORBAL_ONALLOC | CREATE TRIGGER [dbo].[UPDATE_DRACC_PRIORBAL_ONALLOC] ON [dbo].[DR_ALLOCATIONS]
FOR INSERT AS
DECLARE @ACCNO INTEGER,
@TRANS_AGE INTEGER,
@ALLOC_AGE INTEGER,
@TRANS_SEQNO INTEGER,
@TRANS_AMOUNT FLOAT,
@AMOUNT FLOAT
BEGIN
SET NOCOUNT ON
SELECT @TRANS_SEQNO=TRANS_SEQNO, @AMOUNT=AMOUNT, @ALLOC_AGE=AGE FROM INSERTED
SELECT @ACCNO=ACCNO, @TRANS_AMOUNT=AMOUNT, @TRANS_AGE=AGE FROM DR_TRANS WHERE SEQNO=@TRANS_SEQNO
IF (@ALLOC_AGE>0)
BEGIN
IF (@TRANS_AGE=1)
BEGIN
UPDATE DR_ACCS SET PRIOR_AGEDBAL0=PRIOR_AGEDBAL0-@AMOUNT WHERE ACCNO=@ACCNO
END
ELSE
IF (@TRANS_AGE=2)
BEGIN
UPDATE DR_ACCS SET PRIOR_AGEDBAL1=PRIOR_AGEDBAL1-@AMOUNT WHERE ACCNO=@ACCNO
END
ELSE
IF (@TRANS_AGE=3)
BEGIN
UPDATE DR_ACCS SET PRIOR_AGEDBAL2=PRIOR_AGEDBAL2-@AMOUNT WHERE ACCNO=@ACCNO
END
ELSE
BEGIN
UPDATE DR_ACCS SET PRIOR_AGEDBAL3=PRIOR_AGEDBAL3-@AMOUNT WHERE ACCNO=@ACCNO
END
IF (@ALLOC_AGE > 0)
BEGIN
IF @ALLOC_AGE >= 1
BEGIN
IF (@TRANS_AGE>=2)
BEGIN
UPDATE DR_TRANS
SET ALLOCAGE=@ALLOC_AGE, PREV_PERIOD_OPEN=@TRANS_AMOUNT-ISNULL((SELECT SUM(AMOUNT) FROM [dbo].[DR_ALLOCATIONS]
WHERE TRANS_SEQNO=@TRANS_SEQNO AND AGE>=2), 0), PREV_PERIOD_CLOSE=@TRANS_AMOUNT-ISNULL((SELECT SUM(AMOUNT)
FROM [dbo].[DR_ALLOCATIONS] WHERE TRANS_SEQNO=@TRANS_SEQNO AND AGE>=1), 0) WHERE SEQNO=@TRANS_SEQNO
END
IF (@TRANS_AGE=1)
BEGIN
UPDATE DR_TRANS
SET ALLOCAGE=@ALLOC_AGE, PREV_PERIOD_OPEN=0, PREV_PERIOD_CLOSE=@TRANS_AMOUNT-ISNULL((SELECT SUM(AMOUNT)
FROM [dbo].[DR_ALLOCATIONS] WHERE TRANS_SEQNO=@TRANS_SEQNO AND AGE>=1), 0) WHERE SEQNO=@TRANS_SEQNO
END
END
IF (@TRANS_AMOUNT-ISNULL((SELECT SUM(AMOUNT) FROM [dbo].[DR_ALLOCATIONS] WHERE TRANS_SEQNO=@TRANS_SEQNO AND AGE>=1), 0))=0
BEGIN
UPDATE DR_TRANS SET ALLOCATED=2 WHERE SEQNO=@TRANS_SEQNO
END
END
END
SET NOCOUNT OFF
END
|
| UPDATE_DRACC_PRIORBAL_ONUNALLOC | CREATE TRIGGER [dbo].[UPDATE_DRACC_PRIORBAL_ONUNALLOC] ON [dbo].[DR_ALLOCATIONS]
FOR DELETE AS
DECLARE @ACCNO INTEGER,
@TRANS_AGE INTEGER,
@ALLOC_AGE INTEGER,
@TRANS_SEQNO INTEGER,
@TRANS_AMOUNT FLOAT,
@AMOUNT FLOAT
BEGIN
SET NOCOUNT ON
SELECT @TRANS_SEQNO=TRANS_SEQNO, @AMOUNT=AMOUNT, @ALLOC_AGE=AGE FROM DELETED
SELECT @ACCNO=ACCNO, @TRANS_AMOUNT=AMOUNT, @TRANS_AGE=AGE FROM DR_TRANS WHERE SEQNO=@TRANS_SEQNO
IF (@ALLOC_AGE>0)
BEGIN
IF (@TRANS_AGE=1)
BEGIN
UPDATE DR_ACCS SET PRIOR_AGEDBAL0=PRIOR_AGEDBAL0+@AMOUNT WHERE ACCNO=@ACCNO
END
ELSE
IF (@TRANS_AGE=2)
BEGIN
UPDATE DR_ACCS SET PRIOR_AGEDBAL1=PRIOR_AGEDBAL1+@AMOUNT WHERE ACCNO=@ACCNO
END
ELSE
IF (@TRANS_AGE=3)
BEGIN
UPDATE DR_ACCS SET PRIOR_AGEDBAL2=PRIOR_AGEDBAL2+@AMOUNT WHERE ACCNO=@ACCNO
END
ELSE
BEGIN
UPDATE DR_ACCS SET PRIOR_AGEDBAL3=PRIOR_AGEDBAL3+@AMOUNT WHERE ACCNO=@ACCNO
END
IF (@ALLOC_AGE > 0)
BEGIN
IF @ALLOC_AGE >= 1
BEGIN
IF (@TRANS_AGE>=2)
BEGIN
UPDATE DR_TRANS
SET ALLOCAGE=@ALLOC_AGE, PREV_PERIOD_OPEN=@TRANS_AMOUNT+ISNULL((SELECT SUM(AMOUNT) FROM [dbo].[DR_ALLOCATIONS]
WHERE TRANS_SEQNO=@TRANS_SEQNO AND AGE>=2), 0), PREV_PERIOD_CLOSE=@TRANS_AMOUNT+ISNULL((SELECT SUM(AMOUNT)
FROM [dbo].[DR_ALLOCATIONS] WHERE TRANS_SEQNO=@TRANS_SEQNO AND AGE>=1), 0) WHERE SEQNO=@TRANS_SEQNO
END
IF (@TRANS_AGE=1)
BEGIN
UPDATE DR_TRANS
SET ALLOCAGE=@ALLOC_AGE, PREV_PERIOD_OPEN=0, PREV_PERIOD_CLOSE=@TRANS_AMOUNT+ISNULL((SELECT SUM(AMOUNT)
FROM [dbo].[DR_ALLOCATIONS] WHERE TRANS_SEQNO=@TRANS_SEQNO AND AGE>=1), 0) WHERE SEQNO=@TRANS_SEQNO
END
END
UPDATE DR_TRANS SET ALLOCATED=0 WHERE SEQNO=@TRANS_SEQNO
END
END
SET NOCOUNT OFF
END
|
Entity: DR_CONTACTS
| Comment | This table contains details of Contact records that are associated with Debtor accounts. Contact details are taken from the relevant entry in the CONTACTS table. |
| Primary key columns | SEQNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique identifier for the Debtor Contact. The identifier for the Contact record is specified by the CONTACT_SEQNO field. |
| ACCNO | No | INTEGER | No | The account number of the Debtor account that the Contact is associated with. |
| DEFCONTACT | No | CHAR(1) | No | This field is set to 'Y' if the Contact is set as the default Contact for the Debtor account. |
| SALUTATION | No | VARCHAR(4) | No | Taken from the associated record in the CONTACTS table. |
| FIRSTNAME | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| LASTNAME | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| TITLE | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| MOBILE | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| DIRECTPHONE | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| DIRECTFAX | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| EMAIL | No | VARCHAR(60) | No | Taken from the associated record in the CONTACTS table. |
| HOMEPHONE | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| NOTES | No | VARCHAR(4096) | No | Taken from the associated record in the CONTACTS table. |
| ADDRESS1 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| ADDRESS2 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| ADDRESS3 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| DELADDR1 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| DELADDR2 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| DELADDR3 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| DELADDR4 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| ISACTIVE | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB1 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB2 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB3 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB4 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| ADVERTSOURCE | No | INTEGER | No | Taken from the associated record in the CONTACTS table. |
| SALESNO | No | INTEGER | No | Taken from the associated record in the CONTACTS table. |
| POST_CODE | No | VARCHAR(12) | No | Taken from the associated record in the CONTACTS table. |
| SUB5 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB6 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB7 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB8 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB9 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB10 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB11 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB12 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB13 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB14 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB15 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB16 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB17 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB18 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB19 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB20 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| ADDRESS4 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| DELADDR5 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| DELADDR6 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| CONTACT_SEQNO | No | INTEGER | No | The ID number of the Contact record in the CONTACTS table. |
| SUB21 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB22 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB23 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB24 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB25 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB26 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| DEFACCOUNT | No | CHAR(1) | Yes | This field is set to 'Y' if the Debtor account is the default company for the Contact, i.e. if it is the company specified in the Company field on the Details tab of the Contacts window. |
| ADDRESS5 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CONTACTS_DR_CONTACTS | Non Identifying | CONTACTS | DR_CONTACTS | Zero Or More |
| DR_ACCS_DR_CONTACTS | Non Identifying | DR_ACCS | DR_CONTACTS | Zero Or More |
| DR_CONTACTS_DR_CONT_HIST | Non Identifying | DR_CONTACTS | DR_CONT_HIST | One Or More |
Indexes:
| Name | Index columns | Index type |
| DRCONTACT_SEQNO_IDX | CONTACT_SEQNO ASC | |
| DR_DEFCONTACT_INDEX | ACCNO ASC, DEFCONTACT ASC | |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (DEFACCOUNT) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| CONTACTS_DR_CONTACTS | Foreign Key | Table Constraint | FOREIGN KEY (CONTACT_SEQNO) REFERENCES CONTACTS(SEQNO) |
| DF__DR_CONTACT__SUB1__7795AE5F | Default | Column Constraint | SUB1 DEFAULT N |
| DF__DR_CONTACT__SUB2__7889D298 | Default | Column Constraint | SUB2 DEFAULT N |
| DF__DR_CONTACT__SUB3__797DF6D1 | Default | Column Constraint | SUB3 DEFAULT N |
| DF__DR_CONTACT__SUB4__7A721B0A | Default | Column Constraint | SUB4 DEFAULT N |
| DF__DR_CONTACT__SUB5__131DCD43 | Default | Column Constraint | SUB5 DEFAULT N |
| DF__DR_CONTACT__SUB6__1411F17C | Default | Column Constraint | SUB6 DEFAULT N |
| DF__DR_CONTACT__SUB7__150615B5 | Default | Column Constraint | SUB7 DEFAULT N |
| DF__DR_CONTACT__SUB8__15FA39EE | Default | Column Constraint | SUB8 DEFAULT N |
| DF__DR_CONTACT__SUB9__16EE5E27 | Default | Column Constraint | SUB9 DEFAULT N |
| DF__DR_CONTAC__ADVER__7B663F43 | Default | Column Constraint | ADVERTSOURCE DEFAULT 0 |
| DF__DR_CONTAC__CONTA__6FF557EE | Default | Column Constraint | CONTACT_SEQNO DEFAULT 0 |
| DF__DR_CONTAC__DEFAC__4204700C | Default | Column Constraint | DEFACCOUNT DEFAULT N |
| DF__DR_CONTAC__DEFCO__4CAB505A | Default | Column Constraint | DEFCONTACT DEFAULT N |
| DF__DR_CONTAC__ISACT__76A18A26 | Default | Column Constraint | ISACTIVE DEFAULT Y |
| DF__DR_CONTAC__SALES__7B313519 | Default | Column Constraint | SALESNO DEFAULT 0 |
| DF__DR_CONTAC__SUB10__17E28260 | Default | Column Constraint | SUB10 DEFAULT N |
| DF__DR_CONTAC__SUB11__18D6A699 | Default | Column Constraint | SUB11 DEFAULT N |
| DF__DR_CONTAC__SUB12__19CACAD2 | Default | Column Constraint | SUB12 DEFAULT N |
| DF__DR_CONTAC__SUB13__1ABEEF0B | Default | Column Constraint | SUB13 DEFAULT N |
| DF__DR_CONTAC__SUB14__1BB31344 | Default | Column Constraint | SUB14 DEFAULT N |
| DF__DR_CONTAC__SUB15__1CA7377D | Default | Column Constraint | SUB15 DEFAULT N |
| DF__DR_CONTAC__SUB16__1D9B5BB6 | Default | Column Constraint | SUB16 DEFAULT N |
| DF__DR_CONTAC__SUB17__1E8F7FEF | Default | Column Constraint | SUB17 DEFAULT N |
| DF__DR_CONTAC__SUB18__1F83A428 | Default | Column Constraint | SUB18 DEFAULT N |
| DF__DR_CONTAC__SUB19__2077C861 | Default | Column Constraint | SUB19 DEFAULT N |
| DF__DR_CONTAC__SUB20__216BEC9A | Default | Column Constraint | SUB20 DEFAULT N |
| DF__DR_CONTAC__SUB21__546D390A | Default | Column Constraint | SUB21 DEFAULT Y |
| DF__DR_CONTAC__SUB22__55615D43 | Default | Column Constraint | SUB22 DEFAULT Y |
| DF__DR_CONTAC__SUB23__5655817C | Default | Column Constraint | SUB23 DEFAULT Y |
| DF__DR_CONTAC__SUB24__5749A5B5 | Default | Column Constraint | SUB24 DEFAULT Y |
| DF__DR_CONTAC__SUB25__583DC9EE | Default | Column Constraint | SUB25 DEFAULT Y |
| DF__DR_CONTAC__SUB26__5931EE27 | Default | Column Constraint | SUB26 DEFAULT Y |
| DR_ACCS_DR_CONTACTS | Foreign Key | Table Constraint | FOREIGN KEY (ACCNO) REFERENCES DR_ACCS(ACCNO) |
| PK__DR_CONTAC__SEQNO__4BB72C21 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
Triggers:
| Trigger name | Code |
| UPDATE_DR_DEFCONTACT | CREATE TRIGGER [dbo].[UPDATE_DR_DEFCONTACT] ON [dbo].[DR_CONTACTS] FOR UPDATE AS
DECLARE
@NEW_DEFCONTACT CHAR,
@SEQNO INTEGER,
@ACCNO INTEGER
BEGIN
SET NOCOUNT ON
SELECT @NEW_DEFCONTACT=DEFCONTACT, @SEQNO=SEQNO, @ACCNO=ACCNO FROM INSERTED
IF (@NEW_DEFCONTACT = 'Y')
EXECUTE UPDATE_DEFAULT_CONTACT 1, @SEQNO, @ACCNO
SET NOCOUNT OFF
END
|
| UPDATE_DR_DEFCONTACT2 | CREATE TRIGGER [dbo].[UPDATE_DR_DEFCONTACT2] ON [dbo].[DR_CONTACTS] FOR INSERT AS
DECLARE
@NEW_DEFCONTACT CHAR,
@SEQNO INTEGER,
@ACCNO INTEGER
BEGIN
SET NOCOUNT ON
SELECT @NEW_DEFCONTACT=DEFCONTACT, @SEQNO=SEQNO, @ACCNO=ACCNO FROM INSERTED
IF (@NEW_DEFCONTACT = 'Y')
EXECUTE UPDATE_DEFAULT_CONTACT 1, @SEQNO, @ACCNO
SET NOCOUNT OFF
END
|
Entity: DR_CONT_HIST
| Comment | This table contains information about the History Notes attached to Debtor accounts. |
| Primary key columns | SEQNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique identifier for the record. |
| CONTACT_SEQNO | No | INTEGER | No | The ID number of the associated Contact record. |
| 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. |
| COMTYPE | No | INTEGER | No | This field is used by the Serviceable Units add-on module - it is not set from the Debtors UI. |
| SALESNO | No | INTEGER | No | The ID of the staff member selected on the History Note. |
| NOTE | No | VARCHAR(4096) | No | The main body of the History Note. |
| SUBJECT | No | VARCHAR(80) | No | The subject line of the History Note. |
| ACCNO | No | INTEGER | No | The ID number of the Debtor account that the History Note is attached to. |
| ACTIONSTATUS | No | INTEGER | No | This field is used by the Serviceable Units add-on module - it is not set from the Debtors UI. |
| ACTIONDUEDATE | No | DATETIME | No | This field is used by the Serviceable Units add-on module - it is not set from the Debtors UI. |
| JOBNO | No | INTEGER | Yes | This is a legacy field that is no longer used. |
| EVENT_SEQNO | No | INTEGER | No | If the History Note was created as a result of event logging, this field contains the ID number of the reason event from the REASONS table. |
| 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 |
| DR_ACCS_DR_CONT_HIST | Non Identifying | DR_ACCS | DR_CONT_HIST | Zero Or More |
| DR_CONTACTS_DR_CONT_HIST | Non Identifying | DR_CONTACTS | DR_CONT_HIST | One Or More |
| REASONS_DR_CONT_HIST | Non Identifying | REASONS | DR_CONT_HIST | Zero Or More |
| STAFF_DR_CONT_HIST | Non Identifying | STAFF | DR_CONT_HIST | One Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (JOBNO) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| DF__DR_CONT_H__ACCNO__31A25463 | Default | Column Constraint | ACCNO DEFAULT -1 |
| DF__DR_CONT_H__ACTIO__5728DECD | Default | Column Constraint | ACTIONSTATUS DEFAULT 0 |
| DF__DR_CONT_H__ACTIO__581D0306 | Default | Column Constraint | ACTIONDUEDATE DEFAULT getdate() |
| DF__DR_CONT_H__COMTY__04EFA97D | Default | Column Constraint | COMTYPE DEFAULT 0 |
| DF__DR_CONT_H__EVENT__28E3BC87 | Default | Column Constraint | EVENT_SEQNO DEFAULT -1 |
| DF__DR_CONT_H__JOBNO__096A45D7 | Default | Column Constraint | JOBNO DEFAULT 0 |
| DF__DR_CONT_H__POSTT__0307610B | Default | Column Constraint | POSTTIME DEFAULT getdate() |
| DF__DR_CONT_H__SALES__05E3CDB6 | Default | Column Constraint | SALESNO DEFAULT 0 |
| DF__DR_CONT_H__TRANS__03FB8544 | Default | Column Constraint | TRANSDATE DEFAULT getdate() |
| DR_ACCS_DR_CONT_HIST | Foreign Key | Table Constraint | FOREIGN KEY (ACCNO) REFERENCES DR_ACCS(ACCNO) |
| DR_CONTACTS_DR_CONT_HIST | Foreign Key | Table Constraint | FOREIGN KEY (CONTACT_SEQNO) REFERENCES DR_CONTACTS(CONTACT_SEQNO) |
| PK__DR_CONT_H__SEQNO__02133CD2 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
| REASONS_DR_CONT_HIST | Foreign Key | Table Constraint | FOREIGN KEY (EVENT_SEQNO) REFERENCES REASONS(SEQNO) |
| STAFF_DR_CONT_HIST | Foreign Key | Table Constraint | FOREIGN KEY (SALESNO) REFERENCES STAFF(STAFFNO) |
Entity: DR_INVLINES
| Comment | This table contains line information for all Debtor transactions. |
| Primary key columns | |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | No | INTEGER | Yes | A unique transaction ID. |
| ACCNO | No | INTEGER | No | The account number of the Debtor that the transaction belongs to. |
| INVNO | No | VARCHAR(20) | No | The invoice number. |
| HDR_SEQNO | No | INTEGER | No | The SEQNO of the record in the DR_TRANS table that this record related to. |
| STOCKCODE | No | VARCHAR(23) | No | Code of the Stock item on this line. |
| DESCRIPTION | No | VARCHAR(40) | No | Description of the Stock item on this line. |
| QUANTITY | No | FLOAT(53) | No | The quantity for this line. |
| UNITPRICE | No | FLOAT(53) | No | The unit price of the Stock item on this line. |
| DISCOUNT | No | FLOAT(53) | No | The percentage discount for the Stock item on this line. |
| ANALYSIS | No | INTEGER | No | GL code for the Stock line. Can be overridden. The visibility of this column onscreen is controlled by the profile "Enable display and override of GL code from stock item". |
| LOCATION | No | INTEGER | No | The location that the Stock item will be moved out of. Generally, lines inherit the location from the invoice header. |
| UNITPRICE_INCTAX | No | FLOAT(53) | No | The unit price of the Stock item on this line, inclusive of tax. |
| CUSTOMFIELD | No | VARCHAR(30) | No | |
| UPDATE_STOCK | No | CHAR(1) | No | If this field is set to 'Y', stock movements are created via the trigger DR_INVLINES_POST. The field is then set to 'T'. |
| UNITCOST | No | FLOAT(53) | No | The unit cost of the Stock item on this line. |
| JOBCODE | No | VARCHAR(15) | No | If the line originates from a job, the job's code is recorded in this field. |
| CURRENCYNO | No | INTEGER | No | The currency number for the line. |
| EXCHRATE | No | FLOAT(53) | No | The exchange rate for the line at the time of the transaction. |
| ORDERQTY | No | FLOAT(53) | No | The Order Qty from the Sales Order. |
| BKORDQTY | No | FLOAT(53) | No | Back Order Qty from the Sales Order. |
| BATCHCODE | No | VARCHAR(20) | No | The batch code on the line. The "Enable stock batch codes" Company-level profile setting must be enabled to be able to use batch codes. |
| HIDDEN | No | CHAR(1) | No | |
| SUBCODE | No | INTEGER | No | The GL subaccount code on the line. |
| BRANCHNO | No | INTEGER | No | The branch number. Lines generally inherit the branch specified on the invoice header. |
| TAXRATE | No | FLOAT(53) | No | The percentage tax rate on the line at the time of the transaction. |
| TAXRATE_NO | No | INTEGER | No | The ID number of the tax rate used by the transaction. |
| LINETOTAL_TAX | No | FLOAT(53) | No | The tax total for the line. |
| LINETAX_OVERRIDDEN | No | CHAR(1) | No | Contains 'Y' if the tax on the line has been manually modified. |
| LINE_SOURCE | No | INTEGER | No | The source SEQNO of the line, if it originated from a Sales Order or job. |
| JOBNO | No | INTEGER | No | If the line originates from a job, the job's ID number is recorded in this field. |
| SERIALNO | No | VARCHAR(50) | No | |
| TRANSDATE | No | DATETIME | No | The date of the transaction. |
| LINETOTAL | No | AS | No | A computed field containing the total value for the line, excluding tax. |
| LINETOTAL_INCTAX | No | AS | No | A computed field containing the total value for the line, including tax. |
| NUNITPR | No | AS | No | A computed field containing the unit price, taking into account the discount on the line. |
| LISTPRICE | No | FLOAT(53) | No | The original unit price for the Stock item on this line. |
| FREIGHT_FREE | No | CHAR(1) | Yes | 'Y' or 'N' depending on whether the line is freight free. This comes from the price policy for the line. |
| CONTRACT_HDR | No | INTEGER | Yes | |
| DRINVLINEID | No | INTEGER | No | A unique ID for the invoice line, which should be used to link to other tables. Every time an invoice is edited, the lines are essentially removed and reinserted again, which changes the SEQNO. However, the DRINVLINEID field retains its value. Hence, any relationships built on this field are still valid in such a scenario. |
| NARRATIVE_SEQNO | No | INTEGER | No | The SEQNO for the narrative on this line. |
| PRICE_OVERRIDDEN | No | CHAR(1) | Yes | Contains 'Y' if the price on the line has been manually modified. |
| KITCODE | No | VARCHAR(23) | No | The Bill of Materials code, if the line is part of a BOM. |
| LINETYPE | No | INTEGER | Yes | This field indicates the type of line. Will be one of:
0 = Normal stockline
1 = BOM header line
2 = BOM component line
4 = Narrative line |
| KITSEQNO | No | INTEGER | No | A Bill of Materials SEQNO used to group BOM lines together. |
| LINKED_STOCKCODE | No | VARCHAR(23) | No | The physical stock unit. |
| LINKED_QTY | No | FLOAT(53) | No | The physical stock unit quantity. |
| HIDDEN_COST | No | FLOAT(53) | No | This field applies to Bills of Materials. For kits that use pricing by components, this field stores the header-level cost. For builds that use individual stocklines, this fields stores the lines' real costs. |
| HIDDEN_SELL | No | FLOAT(53) | No | 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 stocklines, this fields stores the lines' real prices. |
| BOMTYPE | No | CHAR(1) | No | The type of Bill of Materials that the line relates to: N = Normal stock item B = Build K = Kit L = Lookup |
| SHOWLINE | No | CHAR(1) | No | This field applies to Bills of Materials, and is used by Clarity to determine if the line can be printed. |
| LINKEDSTATUS | No | CHAR(1) | No | Will be one of: S = Stocked L = Lookup N = Not applicable |
| BOMPRICING | No | CHAR(1) | No | How Bills of Materials are priced. Will be one of: C = Pricing by Component T = Pricing by total |
| CREDITQTY | No | INTEGER | Yes | Contains the sum of quantities which have been a part of credit notes against the transaction. |
| CODETYPE | No | CHAR(1) | Yes | The type of code on the line. Will be one of: G - GL code S - Stock code |
| JOB_CONTRACT_BILLINGS_SEQNO | No | INTEGER | Yes | If the invoice is related to a Progress Billing invoice in EXO Job Costing, the ID number of that invoice is recorded here (from JOB_CONTRACT_BILLINGS). |
| COST_LINENO | No | INTEGER | Yes | This field is the link used when allocating cost lines to DR_INVLINES. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CURRENCIES_DR_INVLINES | Non Identifying | CURRENCIES | DR_INVLINES | Zero Or More |
| DR_INVLINES_DR_INVLINES_SERIALS | Non Identifying | DR_INVLINES | DR_INVLINES_SERIALS | Zero Or More |
| DR_INVLINES_JOB_TRANSACTIONS | Identifying | DR_INVLINES | JOB_TRANSACTIONS | Zero Or More |
| DR_INVLINES_SALESORD_HDR | Non Identifying | DR_INVLINES | SALESORD_HDR | Zero Or More |
| DR_TRANS_DR_INVLINES_ACCNO | Non Identifying | DR_TRANS | DR_INVLINES | Zero Or More |
| DR_TRANS_DR_INVLINES_SEQNO | Non Identifying | DR_TRANS | DR_INVLINES | Zero Or More |
| NARRATIVES_DR_INVLINES | Non Identifying | NARRATIVES | DR_INVLINES | Zero Or More |
| STOCK_ITEMS_DR_INVLINES | Non Identifying | STOCK_ITEMS | DR_INVLINES | Zero Or More |
| TAX_RATES_DR_INVLINES | Non Identifying | TAX_RATES | DR_INVLINES | Zero Or More |
Indexes:
| Name | Index columns | Index type |
| DR_INVLINES_ANALYSIS | ANALYSIS ASC | |
| DR_INVLINES_HDRSEQ | HDR_SEQNO ASC | |
| DR_INVLINES_LINE_SOURCE | LINE_SOURCE ASC | |
| DR_INVLINES_STOCKCODE | STOCKCODE 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 (JOB_CONTRACT_BILLINGS_SEQNO) |
| Not Null | Column Constraint | NOT NULL (CODETYPE) |
| Not Null | Column Constraint | NOT NULL (CREDITQTY) |
| Not Null | Column Constraint | NOT NULL (LINETYPE) |
| Not Null | Column Constraint | NOT NULL (PRICE_OVERRIDDEN) |
| Not Null | Column Constraint | NOT NULL (CONTRACT_HDR) |
| Not Null | Column Constraint | NOT NULL (FREIGHT_FREE) |
| CURRENCIES_DR_INVLINES | Foreign Key | Table Constraint | FOREIGN KEY (CURRENCYNO) REFERENCES CURRENCIES(CURRENCYNO) |
| DF__DR_INVLIN__ACCNO__1229A90A | Default | Column Constraint | ACCNO DEFAULT 0 |
| DF__DR_INVLIN__ANALY__1ABEEF0B | Default | Column Constraint | ANALYSIS DEFAULT 0 |
| DF__DR_INVLIN__BKORD__6A06A917 | Default | Column Constraint | BKORDQTY DEFAULT 0 |
| DF__DR_INVLIN__BOMPR__4CC12A4A | Default | Column Constraint | BOMPRICING DEFAULT N |
| DF__DR_INVLIN__BOMTY__49E4BD9F | Default | Column Constraint | BOMTYPE DEFAULT N |
| DF__DR_INVLIN__BRANC__34E8D562 | Default | Column Constraint | BRANCHNO DEFAULT 0 |
| DF__DR_INVLIN__CODET__7D45FCC2 | Default | Column Constraint | CODETYPE DEFAULT S |
| DF__DR_INVLIN__CONTR__485B9C89 | Default | Column Constraint | CONTRACT_HDR DEFAULT 0 |
| DF__DR_INVLIN__COST___02FED618 | Default | Column Constraint | COST_LINENO DEFAULT -1 |
| DF__DR_INVLIN__CREDI__76D75FA7 | Default | Column Constraint | CREDITQTY DEFAULT 0 |
| DF__DR_INVLIN__CURRE__36870511 | Default | Column Constraint | CURRENCYNO DEFAULT 0 |
| DF__DR_INVLIN__DISCO__18D6A699 | Default | Column Constraint | DISCOUNT DEFAULT 0 |
| DF__DR_INVLIN__DRINV__0FE2393C | Default | Column Constraint | DRINVLINEID DEFAULT 0 |
| DF__DR_INVLIN__EXCHR__377B294A | Default | Column Constraint | EXCHRATE DEFAULT 1 |
| DF__DR_INVLIN__FREIG__0D84EF7E | Default | Column Constraint | FREIGHT_FREE DEFAULT N |
| DF__DR_INVLIN__HDR_S__1411F17C | Default | Column Constraint | HDR_SEQNO DEFAULT 0 |
| DF__DR_INVLIN__HIDDE__0CDBAF5F | Default | Column Constraint | HIDDEN_COST DEFAULT 0 |
| DF__DR_INVLIN__HIDDE__0DCFD398 | Default | Column Constraint | HIDDEN_SELL DEFAULT 0 |
| DF__DR_INVLIN__JOBNO__6A1BB7B0 | Default | Column Constraint | JOBNO DEFAULT 0 |
| DF__DR_INVLIN__JOB_C__020AB1DF | Default | Column Constraint | JOB_CONTRACT_BILLINGS_SEQNO DEFAULT -1 |
| DF__DR_INVLIN__KITSE__0BE78B26 | Default | Column Constraint | KITSEQNO DEFAULT -1 |
| DF__DR_INVLIN__LINET__0E4EF685 | Default | Column Constraint | LINETYPE DEFAULT -1 |
| DF__DR_INVLIN__LINET__1BE81D6E | Default | Column Constraint | LINETOTAL_TAX DEFAULT 0 |
| DF__DR_INVLIN__LINKE__4BCD0611 | Default | Column Constraint | LINKEDSTATUS DEFAULT N |
| DF__DR_INVLIN__LISTP__59063A47 | Default | Column Constraint | LISTPRICE DEFAULT 0 |
| DF__DR_INVLIN__LOCAT__1BB31344 | Default | Column Constraint | LOCATION DEFAULT 1 |
| DF__DR_INVLIN__ORDER__691284DE | Default | Column Constraint | ORDERQTY DEFAULT 0 |
| DF__DR_INVLIN__PRICE__6641052B | Default | Column Constraint | PRICE_OVERRIDDEN DEFAULT N |
| DF__DR_INVLIN__QUANT__16EE5E27 | Default | Column Constraint | QUANTITY DEFAULT 0 |
| DF__DR_INVLIN__SHOWL__4AD8E1D8 | Default | Column Constraint | SHOWLINE DEFAULT Y |
| DF__DR_INVLIN__SUBCO__33F4B129 | Default | Column Constraint | SUBCODE DEFAULT 0 |
| DF__DR_INVLIN__TRANS__6B0FDBE9 | Default | Column Constraint | TRANSDATE DEFAULT getdate() |
| DF__DR_INVLIN__UNITC__6B2FD77A | Default | Column Constraint | UNITCOST DEFAULT 0 |
| DF__DR_INVLIN__UNITP__17E28260 | Default | Column Constraint | UNITPRICE DEFAULT 0 |
| DF__DR_INVLIN__UNITP__1CA7377D | Default | Column Constraint | UNITPRICE_INCTAX DEFAULT 0 |
| DF__DR_INVLIN__UPDAT__21F5FC7F | Default | Column Constraint | UPDATE_STOCK DEFAULT Y |
| DR_TRANS_DR_INVLINES_ACCNO | Foreign Key | Table Constraint | FOREIGN KEY (ACCNO) REFERENCES DR_TRANS(ACCNO) |
| DR_TRANS_DR_INVLINES_SEQNO | Foreign Key | Table Constraint | FOREIGN KEY (HDR_SEQNO) REFERENCES DR_TRANS(SEQNO) |
| NARRATIVES_DR_INVLINES | Foreign Key | Table Constraint | FOREIGN KEY (SEQNO) REFERENCES NARRATIVES(SEQNO) |
| STOCK_ITEMS_DR_INVLINES | Foreign Key | Table Constraint | FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE) |
| TAX_RATES_DR_INVLINES | Foreign Key | Table Constraint | FOREIGN KEY (TAXRATE_NO) REFERENCES TAX_RATES(SEQNO) |
Triggers:
| Trigger name | Code |
| DRINVLINESID | CREATE TRIGGER [dbo].[DRINVLINESID] ON [dbo].[DR_INVLINES] FOR INSERT AS
BEGIN
SET NOCOUNT ON
UPDATE [dbo].[DR_INVLINES] SET DRINVLINEID = INSERTED.SEQNO
FROM [dbo].[DR_INVLINES]
INNER JOIN INSERTED ON ([dbo].[DR_INVLINES].SEQNO = INSERTED.SEQNO)
WHERE INSERTED.DRINVLINEID IS NULL OR INSERTED.DRINVLINEID = 0
SET NOCOUNT OFF
END
|
| DR_INVLINES_POST | CREATE TRIGGER [dbo].[DR_INVLINES_POST]
ON [dbo].[DR_INVLINES]
FOR INSERT
AS
BEGIN
/* Exonet created trigger from DBUpdate */
SET NOCOUNT ON
DECLARE @ASTOCKTRANSCODE VARCHAR(23),
@AREF2 VARCHAR(30), @ATRANSTYPE INT, @AGLPOSTED CHAR(1), @APOSTTOGL CHAR(1), @APOSTLOOKUPTOGL CHAR(1),
@ISBOMBUILD CHAR(1), @ISBOMKITHDR CHAR(1), @ISBOMLINKED CHAR(1), @AQUANTITY DOUBLE PRECISION,
@AUNITPRICE DOUBLE PRECISION, @AUNITCOST DOUBLE PRECISION,
@UNITPRICE DOUBLE PRECISION, @UNITCOST DOUBLE PRECISION, @HIDDEN_COST DOUBLE PRECISION,
@HIDDEN_SELL DOUBLE PRECISION, @QUANTITY DOUBLE PRECISION, @KITCODE VARCHAR(23), @KITSEQNO INT,
@STOCKCODE VARCHAR(23), @JOBNO INT, @LINKED_STOCKCODE VARCHAR(23),
@SEQNO INT, @HDR_SEQNO INT, @BATCHCODE VARCHAR(20), @INVNO VARCHAR(20), @ACCNO INT, @LOCATION INT,
@TRANSDATE DATETIME, @LINKEDSTATUS CHAR(1), @DISCOUNT DOUBLE PRECISION, @EXCHRATE DOUBLE PRECISION, @BOMTYPE CHAR(1),
@LINETYPE INT, @UPDATE_STOCK CHAR(1), @SESSION_ID INT
, @AGE INT
, @PERIOD_SEQNO INT
IF ISNULL((SELECT TOP 1 SESSION_ID FROM DR_TRANS D JOIN INSERTED I ON D.SEQNO = I.HDR_SEQNO), -1) = -1
BEGIN
EXECUTE GEN_ID 'DR_TRANS', 'SESSION_ID', 'Y', @ID=@SESSION_ID OUTPUT
UPDATE D
SET SESSION_ID = @SESSION_ID
FROM DR_TRANS D
JOIN INSERTED I ON D.SEQNO = I.HDR_SEQNO
END
ELSE
BEGIN
SELECT @SESSION_ID = (SELECT TOP 1 SESSION_ID FROM DR_TRANS D JOIN INSERTED I ON D.SEQNO = I.HDR_SEQNO)
END
SET @AGE = 0
SELECT @PERIOD_SEQNO = SEQNO FROM PERIOD_STATUS WHERE LEDGER='S' AND AGE = @AGE
DECLARE INSERTED_INVCURSOR CURSOR LOCAL FOR
SELECT UNITPRICE, UNITCOST, HIDDEN_COST, HIDDEN_SELL, QUANTITY, KITCODE, KITSEQNO, STOCKCODE,
JOBNO, LINKED_STOCKCODE, SEQNO, HDR_SEQNO, BATCHCODE, INVNO, ACCNO, LOCATION, TRANSDATE, LINKEDSTATUS,
DISCOUNT, EXCHRATE, BOMTYPE, LINETYPE, UPDATE_STOCK
FROM INSERTED
OPEN INSERTED_INVCURSOR
FETCH NEXT FROM INSERTED_INVCURSOR
INTO @UNITPRICE, @UNITCOST, @HIDDEN_COST, @HIDDEN_SELL, @QUANTITY, @KITCODE, @KITSEQNO, @STOCKCODE,
@JOBNO, @LINKED_STOCKCODE, @SEQNO, @HDR_SEQNO, @BATCHCODE, @INVNO, @ACCNO, @LOCATION, @TRANSDATE, @LINKEDSTATUS,
@DISCOUNT, @EXCHRATE, @BOMTYPE, @LINETYPE, @UPDATE_STOCK
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ISBOMKITHDR = 'N'
IF ((@BOMTYPE = 'K') AND (@LINETYPE = 1))
SET @ISBOMKITHDR = 'Y'
IF ((@STOCKCODE IS NOT NULL) AND (@STOCKCODE<>'') AND (@UPDATE_STOCK='Y') AND (@ISBOMKITHDR = 'N'))
BEGIN
/* PREPARE TO INSERT STOCK TRANSACTION FOR NON-KIT HEADER LINES */
SET @ISBOMBUILD = 'N'
SET @ISBOMLINKED = 'N'
IF (@BOMTYPE = 'B')
SET @ISBOMBUILD = 'Y'
IF (@BOMTYPE = 'L')
SET @ISBOMLINKED = 'Y'
SET @ATRANSTYPE = 0
SET @AGLPOSTED = 'N'
SET @APOSTTOGL = 'Y'
SET @APOSTLOOKUPTOGL = 'N'
SET @AUNITPRICE = @UNITPRICE
SET @AUNITCOST = @UNITCOST
SET @AREF2 = 'INVOICE'
SET @AQUANTITY = - @QUANTITY
/* FIND STOCKCODE TO POST TO STOCKTRANS */
SET @ASTOCKTRANSCODE = @LINKED_STOCKCODE
IF (((@ASTOCKTRANSCODE <> '') AND ((@LINETYPE = 2) OR (@LINETYPE = 0))) OR (@ISBOMLINKED = 'Y'))
SET @ASTOCKTRANSCODE = @STOCKCODE
IF (@ASTOCKTRANSCODE = '')
SET @ASTOCKTRANSCODE = @STOCKCODE
/* FIND LOCAL CURRENCY DISCOUNTED UNIT PRICE EXCL TAX TO USE FOR STOCK_TRANS */
SET @AUNITPRICE = @AUNITPRICE * (1 - @DISCOUNT / 100)
IF (@EXCHRATE <> 0)
SET @AUNITPRICE = @AUNITPRICE / @EXCHRATE
IF ((@ISBOMBUILD ='Y') AND (@LINETYPE = 2))
BEGIN
/* CONSUME COMPONENTS FOR BOM BUILD COMPONENTS */
SET @AUNITPRICE = @HIDDEN_SELL
SET @AUNITCOST = @HIDDEN_COST
SET @AREF2 = 'SALES ORD BOM'
SET @ATRANSTYPE = 3
SET @AGLPOSTED = 'Y'
SET @APOSTTOGL = 'N'
SET @AUNITPRICE = @AUNITCOST
IF (@LINKEDSTATUS = 'L')
SET @APOSTLOOKUPTOGL = 'Y'
END
ELSE IF ((@ISBOMBUILD='Y') AND (@LINETYPE = 1))
BEGIN
/* CREATE NEW BOM BUILD HEADER OUTPUT ITEM */
SET @ATRANSTYPE = 1
SET @AGLPOSTED = 'Y'
SET @APOSTTOGL = 'N'
SET @AQUANTITY = @QUANTITY
SET @AUNITPRICE = @AUNITCOST
END
INSERT INTO STOCK_TRANS
(TRANSTYPE, STOCKCODE, ACCNO, REF1, REF2, QUANTITY, UNITPRICE, LOCATION, FROM_LEDGER, FROM_HDR, BATCHCODE,
JOBNO, LINE_SEQNO, TRANSDATE, PLU, KITCODE, KITSEQNO, POST_TO_GL, UNITCOST, GLPOSTED, POST_LOOKUP_TO_GL, SESSION_ID
, PERIOD_SEQNO
) VALUES (@ATRANSTYPE, @ASTOCKTRANSCODE, @ACCNO, @INVNO, @AREF2, @AQUANTITY,
@AUNITPRICE, @LOCATION, 'd', @HDR_SEQNO, @BATCHCODE, @JOBNO, @SEQNO, @TRANSDATE,
@STOCKCODE, @KITCODE, @KITSEQNO, @APOSTTOGL, @AUNITCOST, @AGLPOSTED, @APOSTLOOKUPTOGL, @SESSION_ID
, @PERIOD_SEQNO
)
/* SET UPDATE_STOCK FIELD ON DR_INVLINE TO MARK LINE AS TRIGGERED */
UPDATE [dbo].[DR_INVLINES]
SET UPDATE_STOCK='T'
WHERE SEQNO = @SEQNO
/* INSERT 2nd TRANSACTION FOR THE SALE OF THE CREATED BOM BUILD HEADER OUTPUT ITEM */
IF ((@ISBOMBUILD='Y') AND (@LINETYPE = 1))
BEGIN
SET @ATRANSTYPE = 0
SET @AGLPOSTED = 'N'
SET @APOSTTOGL = 'Y'
SET @AQUANTITY = -@QUANTITY
SET @AUNITPRICE = @UNITPRICE
INSERT INTO STOCK_TRANS
(TRANSTYPE, STOCKCODE, ACCNO, REF1, REF2, QUANTITY, UNITPRICE, LOCATION, FROM_LEDGER, FROM_HDR, BATCHCODE,
JOBNO, LINE_SEQNO, TRANSDATE, PLU, KITCODE, KITSEQNO, POST_TO_GL, UNITCOST, GLPOSTED, POST_LOOKUP_TO_GL, SESSION_ID
, PERIOD_SEQNO
) VALUES (@ATRANSTYPE, @ASTOCKTRANSCODE, @ACCNO, @INVNO, @AREF2, @AQUANTITY,
@AUNITPRICE, @LOCATION, 'd', @HDR_SEQNO, @BATCHCODE, @JOBNO, @SEQNO, @TRANSDATE,
@STOCKCODE, @KITCODE, @KITSEQNO, @APOSTTOGL, @AUNITCOST, @AGLPOSTED, @APOSTLOOKUPTOGL, @SESSION_ID
, @PERIOD_SEQNO
)
END
END
ELSE IF ((@STOCKCODE IS NOT NULL) AND (@STOCKCODE<>'') AND (@UPDATE_STOCK='Y') AND (@ISBOMKITHDR = 'Y'))
BEGIN
/* SET UPDATE_STOCK FIELD FOR KIT HEADER DR_INVLINE TO MARK LINE AS TRIGGERED */
UPDATE [dbo].[DR_INVLINES]
SET UPDATE_STOCK='T'
WHERE SEQNO = @SEQNO
END
FETCH NEXT FROM INSERTED_INVCURSOR
INTO @UNITPRICE, @UNITCOST, @HIDDEN_COST, @HIDDEN_SELL, @QUANTITY, @KITCODE, @KITSEQNO, @STOCKCODE,
@JOBNO, @LINKED_STOCKCODE, @SEQNO, @HDR_SEQNO, @BATCHCODE, @INVNO, @ACCNO, @LOCATION, @TRANSDATE, @LINKEDSTATUS,
@DISCOUNT, @EXCHRATE, @BOMTYPE, @LINETYPE, @UPDATE_STOCK
END
CLOSE INSERTED_INVCURSOR
DEALLOCATE INSERTED_INVCURSOR
SET NOCOUNT OFF
END
|
| DR_INVLINES_SET_UNITCOST | CREATE TRIGGER [dbo].[DR_INVLINES_SET_UNITCOST]
ON [dbo].[DR_INVLINES]
FOR INSERT
AS
DECLARE @STOCKCOSTMETHOD INTEGER
BEGIN
SET NOCOUNT ON
EXECUTE GET_PROFILE_VALUE_INT 'STOCKCOSTMETHOD', @STOCKCOSTMETHOD OUTPUT
UPDATE [dbo].[DR_INVLINES]
SET UNITCOST = (SELECT (CASE @STOCKCOSTMETHOD WHEN 1 THEN LATESTCOST
WHEN 2 THEN STDCOST ELSE AVECOST END)
FROM STOCK_ITEMS
WHERE STOCK_ITEMS.STOCKCODE = INSERTED.STOCKCODE)
FROM [dbo].[DR_INVLINES] JOIN INSERTED ON ([dbo].[DR_INVLINES].SEQNO = INSERTED.SEQNO)
WHERE INSERTED.STOCKCODE IS NOT NULL
AND (INSERTED.STOCKCODE <> '')
AND (INSERTED.BOMTYPE <> 'K') AND (INSERTED.BOMTYPE <> 'B')
SET NOCOUNT OFF
END
|
Entity: DR_INVLINES_SERIALS
| Comment | This table contains details of serial numbers on Debtor Invoice 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. |
| SERIALNO | No | VARCHAR(50) | No | The serial number. |
| INVLINEID | No | INTEGER | No | The associated Debtors Invoice line - links to DR_INVLINES.DRINVLINEID. |
| POSTTIME | No | DATETIME | No | |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| DR_INVLINES_DR_INVLINES_SERIALS | Non Identifying | DR_INVLINES | DR_INVLINES_SERIALS | Zero Or More |
Indexes:
| Name | Index columns | Index type |
| DR_INVLINES_SERIALS_INDEX | SERIALNO ASC, INVLINEID ASC | UNIQUE |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| DF__DR_INVLIN__POSTT__548247A3 | Default | Column Constraint | POSTTIME DEFAULT getdate() |
| DR_INVLINES_DR_INVLINES_SERIALS | Foreign Key | Table Constraint | FOREIGN KEY (INVLINEID) REFERENCES DR_INVLINES(DRINVLINEID) |
| PK__DR_INVLINES_SERI__538E236A | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
Entity: DR_PRICEGROUPS
| Comment | This table stores details of all Debtor Price Policy Groups. These are set up in the EXO Business Configurator at Admin > Debtors > Debtor Price Policy Groups. |
| Primary key columns | GROUPNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| GROUPNO | Yes | INTEGER | Yes | A unique identifier for the Debtor Price Policy Group. |
| GROUPNAME | No | VARCHAR(30) | Yes | The Debtor Price Policy Group's name. |
| REPORTCODE | No | VARCHAR(15) | No | A reporting code for the Debtor Price Policy Group. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| DR_PRICEGROUPS_DR_ACCS | Non Identifying | DR_PRICEGROUPS | DR_ACCS | One Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (GROUPNAME) |
| Not Null | Column Constraint | NOT NULL (GROUPNO) |
| PK__DR_PRICEGROUPS__181857E1 | Primary Key | Table Constraint | PRIMARY KEY (GROUPNO) |
Entity: DR_PRICES
| Comment | This table contains information on Debtor price rules. |
| Primary key columns | SEQNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique ID number for the price record. |
| ACCNO | No | INTEGER | No | The Debtor account that the price rule is defined for. Has the value -1 if the price rule belongs to a generic price policy. |
| STOCKCODE | No | VARCHAR(23) | No | If the price rule is defined for an individual Stock item, this field stores the item's stockcode. |
| PRICE | No | FLOAT(53) | No | The substitute price to be applied to the stockcode/group. |
| STARTDATE | No | DATETIME | No | Date from which the price rule is active. |
| STOPDATE | No | DATETIME | No | Date to which the price rule remains active. |
| MINQTY | No | FLOAT(53) | No | Minimum quantity for the price rule to apply. |
| ACCGROUP | No | INTEGER | No | The Debtors Account Group that the price rule belongs to. |
| STOCKPRICEGROUP | No | INTEGER | No | If the price rule is defined against a price group rather than an individual product, this field stores the Stock price group. |
| DISCOUNT | No | FLOAT(53) | No | The percentage discount to be applied, as per a price rule. |
| FREIGHT_FREE | No | CHAR(1) | Yes | |
| POLICY_HDR | No | INTEGER | No | The Debtor price policy that the price rule belongs to. |
| SELL_PRICE_BANDNO | No | INTEGER | Yes | Switch to this base sell price band (generally set if no discount/substitute price is applied). |
| MASTER_JOBNO | No | INTEGER | No | ID number of the master job that this price rule applies to, if relevant. |
| JOBNO | No | INTEGER | No | ID number of the job that this price rule applies to, if relevant. |
| CAMPAIGN_WAVE_SEQNO | No | INTEGER | No | ID number of the campaign wave that this price rule applies to, if relevant. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CAMPAIGN_WAVE_DR_PRICES | Non Identifying | CAMPAIGN_WAVE | DR_PRICES | Zero Or More |
| DR_ACCGROUPS_DR_PRICES | Non Identifying | DR_ACCGROUPS | DR_PRICES | One Or More |
| DR_ACCS_DR_PRICES | Non Identifying | DR_ACCS | DR_PRICES | One Or More |
| DR_PRICE_POLICY_DR_PRICES | Non Identifying | DR_PRICE_POLICY | DR_PRICES | One Or More |
| STOCK_ITEMS_DR_PRICES | Non Identifying | STOCK_ITEMS | DR_PRICES | Zero Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (SELL_PRICE_BANDNO) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| Not Null | Column Constraint | NOT NULL (FREIGHT_FREE) |
| CAMPAIGN_WAVE_DR_PRICES | Foreign Key | Table Constraint | FOREIGN KEY (CAMPAIGN_WAVE_SEQNO) REFERENCES CAMPAIGN_WAVE(SEQNO) |
| DF__DR_PRICES__ACCGR__0ABD916C | Default | Column Constraint | ACCGROUP DEFAULT -1 |
| DF__DR_PRICES__ACCNO__51FA155C | Default | Column Constraint | ACCNO DEFAULT 0 |
| DF__DR_PRICES__CAMPA__58098B1B | Default | Column Constraint | CAMPAIGN_WAVE_SEQNO DEFAULT -1 |
| DF__DR_PRICES__FREIG__18AC8967 | Default | Column Constraint | FREIGHT_FREE DEFAULT N |
| DF__DR_PRICES__JOBNO__13D39108 | Default | Column Constraint | JOBNO DEFAULT 0 |
| DF__DR_PRICES__MASTE__12DF6CCF | Default | Column Constraint | MASTER_JOBNO DEFAULT 0 |
| DF__DR_PRICES__MINQT__2A6B46EF | Default | Column Constraint | MINQTY DEFAULT 0 |
| DF__DR_PRICES__PRICE__52EE3995 | Default | Column Constraint | PRICE DEFAULT 0 |
| DF__DR_PRICES__SELL___08F60FE2 | Default | Column Constraint | SELL_PRICE_BANDNO DEFAULT -1 |
| DF__DR_PRICES__START__53E25DCE | Default | Column Constraint | STARTDATE DEFAULT getdate() |
| DF__DR_PRICES__STOCK__68336F3E | Default | Column Constraint | STOCKPRICEGROUP DEFAULT -1 |
| DF__DR_PRICES__STOPD__54D68207 | Default | Column Constraint | STOPDATE DEFAULT getdate() |
| DR_ACCGROUPS_DR_PRICES | Foreign Key | Table Constraint | FOREIGN KEY (ACCGROUP) REFERENCES DR_ACCGROUPS(ACCGROUP) |
| DR_ACCS_DR_PRICES | Foreign Key | Table Constraint | FOREIGN KEY (ACCNO) REFERENCES DR_ACCS(ACCNO) |
| DR_PRICE_POLICY_DR_PRICES | Foreign Key | Table Constraint | FOREIGN KEY (POLICY_HDR) REFERENCES DR_PRICE_POLICY(POLICY_HDR) |
| PK__DR_PRICES__SEQNO__5105F123 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
| STOCK_ITEMS_DR_PRICES | Foreign Key | Table Constraint | FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE) |
Entity: DR_PRICE_POLICY
| Comment | This table contains information on Debtor price policies. |
| Primary key columns | POLICY_HDR |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| POLICY_HDR | Yes | INTEGER | Yes | A unique ID number for the policy record. |
| CUSTOMER_REF | No | VARCHAR(30) | No | Reference field for the customer's reference. |
| POLICY_REF | No | VARCHAR(30) | No | Reference field for the policy |
| START_DATE | No | DATETIME | Yes | Date from which the policy becomes active. |
| END_DATE | No | DATETIME | Yes | Date to which the price rule remains active. |
| PRICE_MODE | No | CHAR(1) | Yes | |
| IS_ACTIVE | No | CHAR(1) | Yes | This field contains 'Y' if the policy is active. |
| NOTES | No | VARCHAR(4096) | No | Any notes that have been recorded for the policy. |
| FREIGHT_FREE | No | CHAR(1) | Yes | |
| FIXED | No | CHAR(1) | Yes | This field contains 'Y' if the policy is fixed |
| CAMPAIGN_WAVE_SEQNO | No | INTEGER | No | The ID number of the campaign wave that the policy relates to, if relevant. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CAMPAIGN_WAVE_DR_PRICE_POLICY | Non Identifying | CAMPAIGN_WAVE | DR_PRICE_POLICY | Zero Or More |
| DR_PRICE_POLICY_DR_PRICES | Non Identifying | DR_PRICE_POLICY | DR_PRICES | One Or More |
| DR_PRICE_POLICY_DR_PRICE_POLICY_ACC | Non Identifying | DR_PRICE_POLICY | DR_PRICE_POLICY_ACC | One Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (FIXED) |
| Not Null | Column Constraint | NOT NULL (POLICY_HDR) |
| Not Null | Column Constraint | NOT NULL (START_DATE) |
| Not Null | Column Constraint | NOT NULL (FREIGHT_FREE) |
| Not Null | Column Constraint | NOT NULL (END_DATE) |
| Not Null | Column Constraint | NOT NULL (IS_ACTIVE) |
| Not Null | Column Constraint | NOT NULL (PRICE_MODE) |
| CAMPAIGN_WAVE_DR_PRICE_POLICY | Foreign Key | Table Constraint | FOREIGN KEY (CAMPAIGN_WAVE_SEQNO) REFERENCES CAMPAIGN_WAVE(SEQNO) |
| DF__DR_PRICE___CAMPA__58FDAF54 | Default | Column Constraint | CAMPAIGN_WAVE_SEQNO DEFAULT -1 |
| DF__DR_PRICE___END_D__33C07256 | Default | Column Constraint | END_DATE DEFAULT getdate() |
| DF__DR_PRICE___FIXED__3791033A | Default | Column Constraint | FIXED DEFAULT N |
| DF__DR_PRICE___FREIG__369CDF01 | Default | Column Constraint | FREIGHT_FREE DEFAULT Y |
| DF__DR_PRICE___IS_AC__35A8BAC8 | Default | Column Constraint | IS_ACTIVE DEFAULT Y |
| DF__DR_PRICE___PRICE__34B4968F | Default | Column Constraint | PRICE_MODE DEFAULT Y |
| DF__DR_PRICE___START__32CC4E1D | Default | Column Constraint | START_DATE DEFAULT getdate() |
| PK__DR_PRICE_POLICY__31D829E4 | Primary Key | Table Constraint | PRIMARY KEY (POLICY_HDR) |
Triggers:
| Trigger name | Code |
| AFTER_POLICY_DELETE | CREATE TRIGGER [dbo].[AFTER_POLICY_DELETE] ON [dbo].[DR_PRICE_POLICY]
FOR DELETE
AS
SET NOCOUNT ON
DELETE FROM DR_PRICES WHERE POLICY_HDR IN (SELECT POLICY_HDR FROM DELETED)
DELETE FROM DR_PRICE_POLICY_ACC WHERE POLICY_HDR IN (SELECT POLICY_HDR FROM DELETED)
SET NOCOUNT OFF
|
| AFTER_POLICY_INSERT | CREATE TRIGGER [dbo].[AFTER_POLICY_INSERT] ON [dbo].[DR_PRICE_POLICY]
FOR INSERT
AS
SET NOCOUNT ON
UPDATE [dbo].[DR_PRICE_POLICY] SET POLICY_REF = 'Policy '+CAST(POLICY_HDR AS VARCHAR) WHERE
POLICY_REF IS NULL AND POLICY_HDR IN (SELECT POLICY_HDR FROM INSERTED)
SET NOCOUNT OFF
|
Entity: DR_PRICE_POLICY_ACC
| Comment | This table defines the members of Debtor price policies. |
| Primary key columns | SEQNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique ID number for the record. |
| POLICY_HDR | No | INTEGER | Yes | The Debtor price policy that the account/account group belongs to. |
| ACCNO | No | INTEGER | No | The relevant Debtor account. |
| ACCGROUP | No | INTEGER | No | The relevant Debtor account group. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| DR_ACCGROUPS_DR_PRICE_POLICY_ACC | Non Identifying | DR_ACCGROUPS | DR_PRICE_POLICY_ACC | One Or More |
| DR_ACCS_DR_PRICE_POLICY_ACC | Non Identifying | DR_ACCS | DR_PRICE_POLICY_ACC | One Or More |
| DR_PRICE_POLICY_DR_PRICE_POLICY_ACC | Non Identifying | DR_PRICE_POLICY | DR_PRICE_POLICY_ACC | One Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (POLICY_HDR) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| DR_ACCGROUPS_DR_PRICE_POLICY_ACC | Foreign Key | Table Constraint | FOREIGN KEY (ACCGROUP) REFERENCES DR_ACCGROUPS(ACCGROUP) |
| DR_ACCS_DR_PRICE_POLICY_ACC | Foreign Key | Table Constraint | FOREIGN KEY (ACCNO) REFERENCES DR_ACCS(ACCNO) |
| DR_PRICE_POLICY_DR_PRICE_POLICY_ACC | Foreign Key | Table Constraint | FOREIGN KEY (POLICY_HDR) REFERENCES DR_PRICE_POLICY(POLICY_HDR) |
| PK__DR_PRICE_POLICY___3A6D6FE5 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
Entity: DR_TRANS
| Comment | This table contains header information for all Debtor transactions. |
| Primary key columns | SEQNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique transaction ID. |
| POSTTIME | No | DATETIME | No | The actual date/time the transaction was posted at. |
| TRANSDATE | No | DATETIME | No | The date/time mentioned in the transaction itself. |
| ACCNO | No | INTEGER | No | The ID number of the Debtor account against which the transaction was generated. If the transaction is against a branch account, this field is set to the head account's ACCNO. |
| TRANSTYPE | No | INTEGER | No | 1 = Postive amount is Invoice, Negative amount is Credit; 4 = Negative amount is Payment, Positive amount is Refund |
| INVNO | No | VARCHAR(20) | No | The invoice number. For credit notes, the invoice number is prefixed with CR. |
| REF1 | No | VARCHAR(20) | No | For invoices, this field contains whatever was entered into the Reference field. If the invoice is for an asset created from Job Costing, this field holds the stockcode for the asset. For payments, this field contains the payment type, e.g. CASH, CHEQUE. |
| REF2 | No | VARCHAR(20) | No | For payments, this filed contains the payment reference/cheque number. For invoices, when coming from Sales Order, this field contains the customer orderno Extra Field value. |
| REF3 | No | VARCHAR(30) | No | For payments, this field contains the payment bank and branch. For invoices, this field contains the text 'Invoice'. |
| NAME | No | VARCHAR(70) | No | The name of the Debtor account against which the transaction was generated. |
| SALESNO | No | INTEGER | No | The ID number of the salesperson. |
| SUBTOTAL | No | FLOAT(53) | No | The tax-exclusive amount, in the currency of the Debtor account. |
| TAXTOTAL | No | FLOAT(53) | No | The tax total in the currency of the Debtor account. |
| TAXINC | No | CHAR(1) | No | Y = tax inclusive, N = tax exclusive. |
| ANALYSIS | No | VARCHAR(12) | No | For payments, this field holds the payment type. |
| ALLOCATEDBAL | No | FLOAT(53) | No | The amount that has been allocated. |
| ALLOCATED | No | CHAR(1) | No | 0 = Unallocated or partially allocated. 1 = Allocated in the current period. 2 = Allocated in any prior period. |
| ALLOCAGE | No | INTEGER | No | The age in which the transaction was allocated. |
| GLPOSTED | No | CHAR(1) | No | Y = the transaction has been posted to GL. N = the transaction has not been posted to GL. |
| GLCODE | No | INTEGER | No | For payments, this field contains the GL code of the bank account. |
| DUEDATE | No | DATETIME | No | The payment due date. |
| BRANCH_ACCNO | No | INTEGER | No | Only used in head account/branch account scenarios. This field contains the account number of the branch account. |
| DELIVADDR1 | No | VARCHAR(30) | No | Delivery address line 1. |
| DELIVADDR2 | No | VARCHAR(30) | No | Delivery address line 2. |
| DELIVADDR3 | No | VARCHAR(30) | No | Delivery address line 3. |
| DELIVADDR4 | No | VARCHAR(30) | No | Delivery address line 4. |
| CONTACT_SEQNO | No | INTEGER | No | The SEQNO of the Contact assigned to this transaction. |
| CURRENCYNO | No | INTEGER | No | The ID of the currency being used for the transaction. |
| EXCHRATE | No | FLOAT(53) | No | The exchange rate being used for the transaction, as determined by the currency in use. |
| BATCHNO | No | INTEGER | No | If the transaction was a part of a Debtors receipt batch, the batch number is stored here. |
| SHIFTNO | No | INTEGER | No | The shift number for EXO Business POS. |
| GLSUBCODE | No | INTEGER | No | For payments, this field contains the GL subcode of the bank account. |
| BRANCHNO | No | INTEGER | No | The branch ID number. |
| ORD_REF | No | VARCHAR(20) | No | For invoices generated from Sales Orders, this field holds the order's reference field value. |
| DISPATCH_INFO | No | VARCHAR(70) | No | The dispatch method. The profile setting "Prompt for dispatch information for debtor invoices and sales orders" must be enabled for users to be able to enter a dispatch method. |
| SO_SEQNO | No | INTEGER | No | For invoices generated from Sales Orders, this field holds the SEQNO of the Sales Order. |
| TAXRATE | No | FLOAT(53) | No | The tax rate used by the transaction, e.g. 12.5 = 12.5%. |
| TAXRATE_NO | No | INTEGER | No | The ID number of the tax rate used by the transaction. |
| DELIVADDR5 | No | VARCHAR(30) | No | Delivery address line 5. |
| DELIVADDR6 | No | VARCHAR(30) | No | Delivery address line 6. |
| PREV_PERIOD_OPEN | No | FLOAT(53) | No | Balance as at the start of the previous period. |
| TERMINAL_ID | No | VARCHAR(30) | No | The terminal ID for a shift in EXO Business POS. |
| DEPOSIT_STATUS | No | INTEGER | No | This value is set to 1 if the payment is a deposit. |
| AMOUNT | No | AS | No | The amount of the transaction, including tax, in the currency of the Debtor account. |
| UNREALISED_GAINS_GL_BATCH | No | INTEGER | Yes | The batch number for the unrealised gains journal that the transaction is a part of. Reset to zero when the journal is reversed. |
| TAXRETCODE | No | VARCHAR(15) | No | The tax return code for the GST return that this transaction applies to. |
| WEEK_NO | No | INTEGER | No | The week number. Populated by the SET_DR_TRANS_WEEK_NO trigger - week dates must be set up first. |
| DDNO | No | INTEGER | No | Direct debit number. |
| RELEASEDAMT | No | FLOAT(53) | Yes | This field is populated by the Direct Debits functionality. |
| SALES_ACCNO | No | INTEGER | No | If the transaction is against a branch account, this field holds the account number for the branch account and the ACCNO field holds the account number of the head account. In all other cases, this field and the ACCNO field always have same number. |
| FREIGHT_FREE | No | CHAR(1) | Yes | Set to 'Y' if this transaction is freight free. This is determined by a combination of the FREIGHT_FREE properties on Debtor accounts and Price Policies. |
| CONTRACT_HDR | No | INTEGER | Yes | This field links to price policies to indicate that a price rule was used belonging to this price contract, e.g. it could be a promotion. |
| BANKACCNO | No | VARCHAR(20) | No | |
| BANKACCNAME | No | VARCHAR(20) | No | |
| GLBATCHNO | No | INTEGER | No | The batch number for the journals that have sourced from this transaction. |
| JOBNO | No | INTEGER | Yes | Where Job Costing is in use, this field contains the ID number of the job associated with the transaction. |
| NARRATIVE_SEQNO | No | INTEGER | No | ID number of the narrative attached to the transaction. |
| TOAGEDBAL | No | INTEGER | No | Only applicable in case of balance brought forward accounts payments and adjustments. Used in the trigger POST_DR_TRANS to refresh the aged balances on the account. 0 = current 1 = 1 months 2 = 2 months 3 = 3 months 4 = from oldest |
| EFTCAID | No | VARCHAR(15) | No | Only applies to EFTPOS transactions. The CAID (entered in EXO Business Config) is stamped to every payment transaction in EXO Business when that transaction is processed via EFTPOS. The CAID is used when a settlement is performed to compute settlement totals and produce a settlement report. |
| EFTSTAN | No | INTEGER | No | For EFTPOS payment transactions, the journal number (often know as a STAN) is stamped to each transaction in EXO Business and directly correlates to the journal entry in the EFTPOS software. |
| PAY_STATUS | No | INTEGER | No | This value is set by the Toggle Query Status right-click menu option on the Allocations screen. |
| PHYS_BRANCH | No | INTEGER | No | ID of the Physical branch. |
| PHYS_STAFF | No | INTEGER | No | The staff ID of the person logged in. |
| SESSION_ID | No | INTEGER | No | ID number of the session. |
| PREV_PERIOD_CLOSE | No | FLOAT(53) | No | The balance as at the end of the previous period. |
| EFTAUTH | No | VARCHAR(30) | No | Only applies to EFTPOS transactions. The authorisation code for EFTPOS integration. |
| GATEWAYNO | No | INTEGER | No | Only applies to EFTPOS transactions. The gateway number for EFTPOS integration. |
| SOURCEINV_SEQNO | No | INTEGER | Yes | For credit notes, this field stores the SEQNO of the source invoice transaction. |
| TXID | No | VARBINARY(256) | No | A unique transaction ID number, populated by the trigger TRG_TXID_DR_TRANS. Used to separate native transactions inserted by EXO Business from other transactions. |
| PTNO | No | INTEGER | No | The payment number. |
| CUSTORDERNO | No | VARCHAR(20) | No | For invoices generated from Sales Orders, this field holds the value of the Extra Field customer Order number. |
| PERIOD_SEQNO | No | INTEGER | Yes | ID number of the period that the transaction is in. |
| AGE_STAMP | No | INTEGER | No | This is a historical field that has been superseded by the AGE calculated field. |
| AGE | No | AS | No | The transaction's age. This is calculated based on PERIOD_SEQNO. |
| TAXROUNDING | No | FLOAT(53) | Yes | The tax rounding amount, where the tax on the header doesn't match the sum of the tax on the lines. Where the tax rate is consistent across all lines of an invoice and none of the lines have been overridden, tax is calculated based on the subtotal. If any line on the invoice has a varied tax rate, or any line is recorded as TAX_OVERRIDDEN='Y' then the tax total on the invoice is the sum of the tax on each line. The difference between the sum of the rounded tax on the lines and the application of the tax rate to the subtotal is recorded in this field. |
| OPPORTUNITY_SEQNO | No | INTEGER | No | If the transaction is associated with an Opportunity (EXO Business CRM), the Opportunity's ID number is stored here. |
| CAMPAIGN_WAVE_SEQNO | No | INTEGER | No | If the transaction is associated with a campaign wave (EXO Business CRM), the wave's ID number is stored here. |
| JOB_CONTRACT_BILLINGS_SEQNO | No | INTEGER | Yes | If the invoice is related to a Progress Billing invoice in EXO Job Costing, the ID number of that invoice is recorded here (from JOB_CONTRACT_BILLINGS). |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CAMPAIGN_WAVE_DR_TRANS | Non Identifying | CAMPAIGN_WAVE | DR_TRANS | Zero Or More |
| CURRENCIES_DR_TRANS | Non Identifying | CURRENCIES | DR_TRANS | Zero Or More |
| DR_ACCS_DR_TRANS | Non Identifying | DR_ACCS | DR_TRANS | Zero Or More |
| DR_TRANS_DR_ALLOCATIONS | Non Identifying | DR_TRANS | DR_ALLOCATIONS | Zero Or More |
| DR_TRANS_DR_INVLINES_ACCNO | Non Identifying | DR_TRANS | DR_INVLINES | Zero Or More |
| DR_TRANS_DR_INVLINES_SEQNO | Non Identifying | DR_TRANS | DR_INVLINES | Zero Or More |
| DR_TRANS_JOB_TRANSACTIONS | Non Identifying | DR_TRANS | JOB_TRANSACTIONS | Zero Or More |
| GLACCS_DR_TRANS | Non Identifying | GLACCS | DR_TRANS | Zero Or More |
| NARRATIVES_DR_TRANS | Non Identifying | NARRATIVES | DR_TRANS | Zero Or More |
| OPPORTUNITY_DR_TRANS | Non Identifying | OPPORTUNITY | DR_TRANS | Zero Or More |
| PAYMENT_TYPES_DR_TRANS | Non Identifying | PAYMENT_TYPES | DR_TRANS | Zero Or More |
| PERIOD_STATUS_DR_TRANS | Non Identifying | PERIOD_STATUS | DR_TRANS | Zero Or More |
| STAFF_DR_TRANS | Non Identifying | STAFF | DR_TRANS | Zero Or More |
| TAX_RATES_DR_TRANS | Non Identifying | TAX_RATES | DR_TRANS | Zero Or More |
Indexes:
| Name | Index columns | Index type |
| DR_TRANS_ACCNO | ACCNO ASC | |
| DR_TRANS_ALLOCATED | ALLOCATED ASC | |
| DR_TRANS_GLBATCHNO | GLBATCHNO ASC | |
| DR_TRANS_INVNO | INVNO ASC | |
| DR_TRANS_OPPORTUNITY | OPPORTUNITY_SEQNO ASC | |
| DR_TRANS_PERIODSEQNO_INDEX | PERIOD_SEQNO ASC | |
| DR_TRANS_PERIOD_SEQNO | PERIOD_SEQNO ASC | |
| DR_TRANS_TRANSDATE | TRANSDATE ASC | |
| DR_TRANS_TRANSTYPE | TRANSTYPE ASC | |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (JOB_CONTRACT_BILLINGS_SEQNO) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| Not Null | Column Constraint | NOT NULL (TAXROUNDING) |
| Not Null | Column Constraint | NOT NULL (PERIOD_SEQNO) |
| Not Null | Column Constraint | NOT NULL (SOURCEINV_SEQNO) |
| Not Null | Column Constraint | NOT NULL (JOBNO) |
| Not Null | Column Constraint | NOT NULL (CONTRACT_HDR) |
| Not Null | Column Constraint | NOT NULL (FREIGHT_FREE) |
| Not Null | Column Constraint | NOT NULL (RELEASEDAMT) |
| Not Null | Column Constraint | NOT NULL (UNREALISED_GAINS_GL_BATCH) |
| CAMPAIGN_WAVE_DR_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (CAMPAIGN_WAVE_SEQNO) REFERENCES CAMPAIGN_WAVE(SEQNO) |
| CURRENCIES_DR_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (CURRENCYNO) REFERENCES CURRENCIES(CURRENCYNO) |
| DF__DR_TRANS__ACCNO__2354350C | Default | Column Constraint | ACCNO DEFAULT 0 |
| DF__DR_TRANS__AGE_ST__43188FD6 | Default | Column Constraint | AGE_STAMP DEFAULT -1 |
| DF__DR_TRANS__ALLOCA__2FBA0BF1 | Default | Column Constraint | ALLOCATEDBAL DEFAULT 0 |
| DF__DR_TRANS__ALLOCA__30AE302A | Default | Column Constraint | ALLOCATED DEFAULT 0 |
| DF__DR_TRANS__ALLOCA__31A25463 | Default | Column Constraint | ALLOCAGE DEFAULT 0 |
| DF__DR_TRANS__BRANCH__3118447E | Default | Column Constraint | BRANCHNO DEFAULT 0 |
| DF__DR_TRANS__CAMPAI__562142A9 | Default | Column Constraint | CAMPAIGN_WAVE_SEQNO DEFAULT -1 |
| DF__DR_TRANS__CONTRA__494FC0C2 | Default | Column Constraint | CONTRACT_HDR DEFAULT 0 |
| DF__DR_TRANS__CURREN__32B6742D | Default | Column Constraint | CURRENCYNO DEFAULT 0 |
| DF__DR_TRANS__DDNO__5DCAEF64 | Default | Column Constraint | DDNO DEFAULT -1 |
| DF__DR_TRANS__DEPOSI__4F67C174 | Default | Column Constraint | DEPOSIT_STATUS DEFAULT 0 |
| DF__DR_TRANS__EXCHRA__33AA9866 | Default | Column Constraint | EXCHRATE DEFAULT 1 |
| DF__DR_TRANS__FREIGH__0E7913B7 | Default | Column Constraint | FREIGHT_FREE DEFAULT N |
| DF__DR_TRANS__GATEWA__6C59D134 | Default | Column Constraint | GATEWAYNO DEFAULT -1 |
| DF__DR_TRANS__GLPOST__3296789C | Default | Column Constraint | GLPOSTED DEFAULT N |
| DF__DR_TRANS__GLSUBC__30242045 | Default | Column Constraint | GLSUBCODE DEFAULT 0 |
| DF__DR_TRANS__JOBNO__4ED38FEE | Default | Column Constraint | JOBNO DEFAULT -1 |
| DF__DR_TRANS__JOB_CO__01168DA6 | Default | Column Constraint | JOB_CONTRACT_BILLINGS_SEQNO DEFAULT -1 |
| DF__DR_TRANS__OPPORT__75AEFC9B | Default | Column Constraint | OPPORTUNITY_SEQNO DEFAULT -1 |
| DF__DR_TRANS__PAY_ST__7D6461A5 | Default | Column Constraint | PAY_STATUS DEFAULT 0 |
| DF__DR_TRANS__PERIOD__42246B9D | Default | Column Constraint | PERIOD_SEQNO DEFAULT 0 |
| DF__DR_TRANS__PHYS_B__2E079900 | Default | Column Constraint | PHYS_BRANCH DEFAULT 0 |
| DF__DR_TRANS__PHYS_S__2EFBBD39 | Default | Column Constraint | PHYS_STAFF DEFAULT 0 |
| DF__DR_TRANS__POSTTI__216BEC9A | Default | Column Constraint | POSTTIME DEFAULT getdate() |
| DF__DR_TRANS__PREV_P__2A363CC5 | Default | Column Constraint | PREV_PERIOD_OPEN DEFAULT 0 |
| DF__DR_TRANS__PREV_P__491094F7 | Default | Column Constraint | PREV_PERIOD_CLOSE DEFAULT 0 |
| DF__DR_TRANS__PTNO__3652C63E | Default | Column Constraint | PTNO DEFAULT -1 |
| DF__DR_TRANS__RELEAS__5EBF139D | Default | Column Constraint | RELEASEDAMT DEFAULT 0 |
| DF__DR_TRANS__SALESN__2A01329B | Default | Column Constraint | SALESNO DEFAULT 0 |
| DF__DR_TRANS__SESSIO__2FEFE172 | Default | Column Constraint | SESSION_ID DEFAULT -1 |
| DF__DR_TRANS__SOURCE__75E33B6E | Default | Column Constraint | SOURCEINV_SEQNO DEFAULT -1 |
| DF__DR_TRANS__SO_SEQ__0D99FE17 | Default | Column Constraint | SO_SEQNO DEFAULT 0 |
| DF__DR_TRANS__SUBTOT__2AF556D4 | Default | Column Constraint | SUBTOTAL DEFAULT 0 |
| DF__DR_TRANS__TAXINC__2DD1C37F | Default | Column Constraint | TAXINC DEFAULT N |
| DF__DR_TRANS__TAXRAT__18178C8A | Default | Column Constraint | TAXRATE DEFAULT 0 |
| DF__DR_TRANS__TAXRAT__190BB0C3 | Default | Column Constraint | TAXRATE_NO DEFAULT 0 |
| DF__DR_TRANS__TAXROU__5A70F67A | Default | Column Constraint | TAXROUNDING DEFAULT 0 |
| DF__DR_TRANS__TAXTOT__2BE97B0D | Default | Column Constraint | TAXTOTAL DEFAULT 0 |
| DF__DR_TRANS__TRANSD__226010D3 | Default | Column Constraint | TRANSDATE DEFAULT getdate() |
| DF__DR_TRANS__TRANST__24485945 | Default | Column Constraint | TRANSTYPE DEFAULT 0 |
| DF__DR_TRANS__UNREAL__1C722D53 | Default | Column Constraint | UNREALISED_GAINS_GL_BATCH DEFAULT 0 |
| DF__DR_TRANS__WEEK_N__5CD6CB2B | Default | Column Constraint | WEEK_NO DEFAULT 0 |
| DR_ACCS_DR_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (ACCNO) REFERENCES DR_ACCS(ACCNO) |
| GLACCS_DR_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (GLCODE) REFERENCES GLACCS(ACCNO) |
| NARRATIVES_DR_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (NARRATIVE_SEQNO) REFERENCES NARRATIVES(SEQNO) |
| OPPORTUNITY_DR_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (OPPORTUNITY_SEQNO) REFERENCES OPPORTUNITY(SEQNO) |
| PAYMENT_TYPES_DR_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (PTNO) REFERENCES PAYMENT_TYPES(PTNO) |
| PERIOD_STATUS_DR_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (PERIOD_SEQNO) REFERENCES PERIOD_STATUS(SEQNO) |
| PK__DR_TRANS__2077C861 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
| STAFF_DR_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (SALESNO) REFERENCES STAFF(STAFFNO) |
| TAX_RATES_DR_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (TAXRATE_NO) REFERENCES TAX_RATES(SEQNO) |
Triggers:
| Trigger name | Code |
| POST_DR_TRANS | CREATE TRIGGER [dbo].[POST_DR_TRANS]
ON [dbo].[DR_TRANS]
FOR INSERT
AS
BEGIN
/* Exonet created trigger from DBUpdate version [6.155] */
SET NOCOUNT ON
DECLARE @ACCNO INTEGER, @BRANCH_ACCNO INTEGER, @SUBTOTAL FLOAT, @TAXTOTAL FLOAT,
@AGE INTEGER, @TRANSTYPE INTEGER, @TOAGEDBAL INTEGER, @PERIODSTHISYEAR INTEGER, @PERIODSINYEAR INTEGER,
@MONTHVAL FLOAT, @LASTMONTH FLOAT, @YEARVAL FLOAT, @LASTYEAR FLOAT,
@AMOUNT FLOAT, @AGEDBAL0 FLOAT, @AGEDBAL1 FLOAT, @AGEDBAL2 FLOAT, @AGEDBAL3 FLOAT,
@PRIOR_AGEDBAL0 FLOAT, @PRIOR_AGEDBAL1 FLOAT, @PRIOR_AGEDBAL2 FLOAT, @PRIOR_AGEDBAL3 FLOAT,
@TURNOVER_ACCNO INTEGER, @OPENITEM CHAR(1)
SELECT @PERIODSTHISYEAR = PERIODSTHISYEAR FROM GL_CONTROL
SELECT @PERIODSINYEAR = COUNT(SEQNO) FROM PERIODS_DEFN WHERE SEQNO > 0
DECLARE INSERTED_CURSOR CURSOR LOCAL
FOR SELECT ACCNO, BRANCH_ACCNO, SUBTOTAL, TAXTOTAL, AGE, TRANSTYPE, TOAGEDBAL
FROM INSERTED
OPEN INSERTED_CURSOR
FETCH NEXT FROM INSERTED_CURSOR
INTO @ACCNO, @BRANCH_ACCNO, @SUBTOTAL, @TAXTOTAL, @AGE, @TRANSTYPE, @TOAGEDBAL
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @OPENITEM = OPENITEM FROM DR_ACCS WHERE ACCNO = @ACCNO
SET @MONTHVAL = 0
SET @LASTMONTH = 0
SET @YEARVAL = 0
SET @LASTYEAR = 0
SET @AGEDBAL0 = 0
SET @AGEDBAL1 = 0
SET @AGEDBAL2 = 0
SET @AGEDBAL3 = 0
SET @PRIOR_AGEDBAL0 = 0
SET @PRIOR_AGEDBAL1 = 0
SET @PRIOR_AGEDBAL2 = 0
SET @PRIOR_AGEDBAL3 = 0
/* Set Turnover variables */
IF (@TRANSTYPE = 1)
BEGIN
IF (@AGE = 0)
SET @MONTHVAL = @SUBTOTAL
ELSE IF (@AGE = 1)
SET @LASTMONTH = @SUBTOTAL
IF ((@AGE+1) <= @PERIODSTHISYEAR)
SET @YEARVAL = @SUBTOTAL
ELSE IF ((@AGE+1) <= (@PERIODSTHISYEAR+@PERIODSINYEAR))
SET @LASTYEAR = @SUBTOTAL
END
/* Set Aged Balance variables */
SET @AMOUNT = @SUBTOTAL + @TAXTOTAL
IF ((NOT (@OPENITEM = 'Y')) AND (@TOAGEDBAL IS NOT NULL)) /* set according to Age selected */
EXECUTE DR_ACCS_BALFWD_BAL_DELTAS @ACCNO, @TOAGEDBAL, @AMOUNT,
@AGEDBAL0 OUTPUT, @AGEDBAL1 OUTPUT, @AGEDBAL2 OUTPUT, @AGEDBAL3 OUTPUT,
@PRIOR_AGEDBAL0 OUTPUT, @PRIOR_AGEDBAL1 OUTPUT, @PRIOR_AGEDBAL2 OUTPUT
ELSE /* set as usual */
BEGIN
IF (@AGE = 0)
SET @AGEDBAL0 = @AMOUNT
ELSE IF (@AGE = 1)
BEGIN
SET @AGEDBAL1 = @AMOUNT
SET @PRIOR_AGEDBAL0 = @AGEDBAL1
END
ELSE IF (@AGE = 2)
BEGIN
SET @AGEDBAL2 = @AMOUNT
SET @PRIOR_AGEDBAL1 = @AGEDBAL2
END
ELSE
BEGIN
SET @AGEDBAL3 = @AMOUNT
IF (@AGE = 3)
SET @PRIOR_AGEDBAL2 = @AGEDBAL3
ELSE
SET @PRIOR_AGEDBAL3 = @AGEDBAL3
END
END
/* Determine which A/c to set Turnover field values in */
IF (@BRANCH_ACCNO > 0) /* If there is a Branch A/c */
SET @TURNOVER_ACCNO = @BRANCH_ACCNO
ELSE
SET @TURNOVER_ACCNO = @ACCNO
/* Set Turnover field values */
UPDATE DR_ACCS SET
MONTHVAL = MONTHVAL + @MONTHVAL,
LASTMONTH = LASTMONTH + @LASTMONTH,
YEARVAL = YEARVAL + @YEARVAL,
LASTYEAR = LASTYEAR + @LASTYEAR
WHERE
ACCNO = @TURNOVER_ACCNO
/* Set Aged Balance field values */
UPDATE DR_ACCS SET
AGEDBAL0 = AGEDBAL0 + @AGEDBAL0,
AGEDBAL1 = AGEDBAL1 + @AGEDBAL1,
AGEDBAL2 = AGEDBAL2 + @AGEDBAL2,
AGEDBAL3 = AGEDBAL3 + @AGEDBAL3,
PRIOR_AGEDBAL0 = PRIOR_AGEDBAL0 + @PRIOR_AGEDBAL0,
PRIOR_AGEDBAL1 = PRIOR_AGEDBAL1 + @PRIOR_AGEDBAL1,
PRIOR_AGEDBAL2 = PRIOR_AGEDBAL2 + @PRIOR_AGEDBAL2,
PRIOR_AGEDBAL3 = PRIOR_AGEDBAL3 + @PRIOR_AGEDBAL3
WHERE
ACCNO = @ACCNO
FETCH NEXT FROM INSERTED_CURSOR
INTO @ACCNO, @BRANCH_ACCNO, @SUBTOTAL, @TAXTOTAL, @AGE, @TRANSTYPE, @TOAGEDBAL
END
CLOSE INSERTED_CURSOR
DEALLOCATE INSERTED_CURSOR
SET NOCOUNT OFF
END
|
| SET_DR_TRANS_PREV_PERIOD_OPEN | CREATE TRIGGER [dbo].[SET_DR_TRANS_PREV_PERIOD_OPEN]
ON [dbo].[DR_TRANS]
FOR INSERT
AS
DECLARE @NEW_SEQNO INTEGER,
@NEW_AGE INTEGER,
@NEW_AMOUNT FLOAT
BEGIN
SET NOCOUNT ON
SELECT @NEW_SEQNO=SEQNO, @NEW_AGE=AGE, @NEW_AMOUNT=AMOUNT FROM INSERTED
IF (@NEW_AGE > 0)
BEGIN
IF (@NEW_AGE>=2)
UPDATE [dbo].[DR_TRANS] SET ALLOCAGE=@NEW_AGE,
PREV_PERIOD_OPEN = @NEW_AMOUNT - ISNULL((SELECT SUM(AMOUNT) FROM DR_ALLOCATIONS WHERE TRANS_SEQNO=@NEW_SEQNO AND AGE>=2), 0),
PREV_PERIOD_CLOSE = @NEW_AMOUNT - ISNULL((SELECT SUM(AMOUNT) FROM DR_ALLOCATIONS WHERE TRANS_SEQNO=@NEW_SEQNO AND AGE>=1), 0)
WHERE SEQNO = @NEW_SEQNO
ELSE
IF (@NEW_AGE=1)
UPDATE [dbo].[DR_TRANS] SET ALLOCAGE=@NEW_AGE,
PREV_PERIOD_OPEN=0,
PREV_PERIOD_CLOSE = @NEW_AMOUNT - ISNULL((SELECT SUM(AMOUNT) FROM DR_ALLOCATIONS WHERE TRANS_SEQNO=@NEW_SEQNO AND AGE>=1), 0)
WHERE SEQNO = @NEW_SEQNO
END
SET NOCOUNT OFF
END
|
| SET_DR_TRANS_WEEK_NO | /****** Object: Trigger [dbo].[SET_DR_TRANS_WEEK_NO] Script Date: 18/12/2001 12:51:35 ******/
CREATE TRIGGER SET_DR_TRANS_WEEK_NO
ON dbo.[dbo].[DR_TRANS]
FOR INSERT
AS
DECLARE @NEW_WEEK_NO INTEGER,
@NEW_SEQNO INTEGER,
@TODAYS_DATE DATETIME,
@CURRENT_WEEK_NO INTEGER
BEGIN
SET NOCOUNT ON
SELECT @NEW_WEEK_NO=WEEK_NO, @NEW_SEQNO=SEQNO
FROM INSERTED
IF ((@NEW_WEEK_NO IS NULL) OR (@NEW_WEEK_NO=0))
BEGIN
SELECT @TODAYS_DATE=GETDATE()
SELECT @CURRENT_WEEK_NO=MAX(WEEK_NO) FROM WEEK_DATES
WHERE @TODAYS_DATE>=START_DATE
AND @TODAYS_DATE<(END_DATE+1)
UPDATE [dbo].[DR_TRANS] SET WEEK_NO=@CURRENT_WEEK_NO WHERE SEQNO=@NEW_SEQNO
END
SET NOCOUNT OFF
END
|
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: 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: OPPORTUNITY
| Comment | This table contains details of all Opportunities used by the EXO CRM module. |
| Primary key columns | SEQNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique ID number for the Opportunity. |
| DESCRIPTION | No | VARCHAR(60) | No | Description text for the Opportunity. |
| OPPORTUNITY_TYPE | No | INTEGER | No | The Opportunity's type, taken from OPPORTUNITY_TYPE.SEQNO. |
| OPPORTUNITY_LEAD | No | INTEGER | No | The Opportunity's lead source, taken from OPPORTUNITY_LEAD.SEQNO. |
| OPPORTUNITY_STAGE | No | INTEGER | No | The Opportunity's stage, taken from OPPORTUNITY_STAGE.SEQNO. |
| PROBABILITY | No | INTEGER | No | The Probability value entered for the Opportunity. |
| ASSIGNED_TO | No | INTEGER | No | The ID of the staff member to whom the Opportunity has been assigned. |
| ASSIGNED_BY | No | INTEGER | No | The ID of the staff member who assigned the Opportunity. |
| COMPANYID | No | VARCHAR(50) | No | The ID number of the company (Debtor, Creditor or Non Account) that the Opportunity is assigned to. |
| CONTACTSEQNO | No | INTEGER | No | The ID number of the Contact that the Opportunity is assigned to. |
| AMOUNT | No | FLOAT(53) | No | The quote value for the Opportunity. |
| START_DATE | No | DATETIME | No | The start date of the Opportunity. |
| DETAILS | No | VARCHAR(4096) | No | Any details or notes entered for the Opportunity. |
| CREATEDBY | No | INTEGER | No | The ID number of the staff member who created the Opportunity. |
| CREATEDATE | No | DATETIME | No | The date and time that the Opportunity was created. |
| MODIFIEDBY | No | INTEGER | No | The ID number of the staff member who most recently modified the Opportunity. |
| MODIFIEDDATE | No | DATETIME | No | The date and time that the Opportunity was most recently modified. |
| DUE_DATE | No | DATETIME | No | The due date for the Opportunity. |
| CLOSE_DATE | No | DATETIME | No | The date and time when the Opportunity was changed to Closed status. |
| IS_CLOSE | No | AS | No | Set to 'Y' if the Opportunity has been changed to Closed status. |
| ESTIMATE | No | FLOAT(53) | Yes | The estimated value entered for the Opportunity. |
| LOST_VALUE | No | FLOAT(53) | Yes | The total value of all lost lines on the Opportunity. |
| CLOSED_VALUE | No | FLOAT(53) | Yes | The total value of all closed lines on the Opportunity. |
| TAXTOTAL | No | FLOAT(53) | Yes | The total tax amount on the Opportunity. |
| TAXROUNDING | No | FLOAT(53) | Yes | The difference between the tax on the header and the sum of the tax on the individual lines. |
| CURRENCYNO | No | INTEGER | No | The ID number of the currency that the Sales Order is in. |
| EXCHRATE | No | FLOAT(53) | No | The exchange rate for the currency that the Opportunity is in. |
| WEIGHTED_VALUE | No | AS | No | The Opportunity's weighted value. |
| WEIGHTED_ESTIMATE | No | AS | No | The Opportunity's weighted estimate. |
| CAMPAIGN_WAVE_SEQNO | No | INTEGER | No | If the Opportunity is associated with a campaign wave (EXO Business CRM), the wave's ID number is stored here. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CAMPAIGN_WAVE_OPPORTUNITY | Non Identifying | CAMPAIGN_WAVE | OPPORTUNITY | Zero Or More |
| OPPORTUNITY_DR_TRANS | Non Identifying | OPPORTUNITY | DR_TRANS | Zero Or More |
| OPPORTUNITY_JOBCOST_HDR | Non Identifying | OPPORTUNITY | JOBCOST_HDR | Zero Or More |
| OPPORTUNITY_LEAD_OPPORTUNITY | Non Identifying | OPPORTUNITY_LEAD | OPPORTUNITY | Zero Or More |
| OPPORTUNITY_OPPORTUNITY_HIST | Non Identifying | OPPORTUNITY | OPPORTUNITY_HIST | Zero Or More |
| OPPORTUNITY_OPPORTUNITY_QUOTE | Non Identifying | OPPORTUNITY | OPPORTUNITY_QUOTE | Zero Or More |
| OPPORTUNITY_OPPORTUNITY_QUOTE_OPTIONS | Non Identifying | OPPORTUNITY | OPPORTUNITY_QUOTE_OPTIONS | Zero Or More |
| OPPORTUNITY_SALESORD_HDR | Non Identifying | OPPORTUNITY | SALESORD_HDR | Zero Or More |
| OPPORTUNITY_STAGE_OPPORTUNITY | Non Identifying | OPPORTUNITY_STAGE | OPPORTUNITY | Zero Or More |
| OPPORTUNITY_TASKS | Non Identifying | OPPORTUNITY | TASKS | Zero Or More |
| OPPORTUNITY_TYPE_OPPORTUNITY | Non Identifying | OPPORTUNITY_TYPE | OPPORTUNITY | Zero Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (TAXROUNDING) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| Not Null | Column Constraint | NOT NULL (TAXTOTAL) |
| Not Null | Column Constraint | NOT NULL (ESTIMATE) |
| Not Null | Column Constraint | NOT NULL (CLOSED_VALUE) |
| Not Null | Column Constraint | NOT NULL (LOST_VALUE) |
| CAMPAIGN_WAVE_OPPORTUNITY | Foreign Key | Table Constraint | FOREIGN KEY (CAMPAIGN_WAVE_SEQNO) REFERENCES CAMPAIGN_WAVE(SEQNO) |
| DF_OPPORTUNITY_AMOUNT | Default | Column Constraint | AMOUNT DEFAULT 0 |
| DF_OPPORTUNITY_CLOSED_VALUE | Default | Column Constraint | CLOSED_VALUE DEFAULT 0 |
| DF__OPPORTUNI__CAMPA__3F08D327 | Default | Column Constraint | CAMPAIGN_WAVE_SEQNO DEFAULT -1 |
| DF__OPPORTUNI__CURRE__75CEF82C | Default | Column Constraint | CURRENCYNO DEFAULT 0 |
| DF__OPPORTUNI__ESTIM__63E5521B | Default | Column Constraint | ESTIMATE DEFAULT 0 |
| DF__OPPORTUNI__EXCHR__76C31C65 | Default | Column Constraint | EXCHRATE DEFAULT 1 |
| DF__OPPORTUNI__LOST___64D97654 | Default | Column Constraint | LOST_VALUE DEFAULT 0 |
| DF__OPPORTUNI__TAXRO__3C967AD0 | Default | Column Constraint | TAXROUNDING DEFAULT 0 |
| DF__OPPORTUNI__TAXTO__3BA25697 | Default | Column Constraint | TAXTOTAL DEFAULT 0 |
| OPPORTUNITY_LEAD_OPPORTUNITY | Foreign Key | Table Constraint | FOREIGN KEY (OPPORTUNITY_LEAD) REFERENCES OPPORTUNITY_LEAD(SEQNO) |
| OPPORTUNITY_STAGE_OPPORTUNITY | Foreign Key | Table Constraint | FOREIGN KEY (OPPORTUNITY_STAGE) REFERENCES OPPORTUNITY_STAGE(SEQNO) |
| OPPORTUNITY_TYPE_OPPORTUNITY | Foreign Key | Table Constraint | FOREIGN KEY (OPPORTUNITY_TYPE) REFERENCES OPPORTUNITY_TYPE(SEQNO) |
| PK__OPPORTUNITY__56CA82C8 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
Entity: PAYMENT_TYPES
| Comment | This table contains details of all Payment Types. |
| Primary key columns | PTNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| PTNO | Yes | INTEGER | Yes | A unique ID number for the Payment Type. |
| PTDESC | No | VARCHAR(12) | No | The Payment Type's name. |
| PTKEY | No | CHAR(1) | No | A one-character code for the Payment Type. |
| IMG_FILE | No | VARCHAR(80) | No | The filename of the image displayed for the Payment Type on websites. |
| WEB_SHOW | No | CHAR(1) | No | This field is set to 'Y' if the Payment Type is set to appear on websites, i.e. if its "Show on web site" flag is ticked. |
| LIVE_TRANS | No | CHAR(1) | No | This field is set to 'Y' if the Payment Type is set to be available on bank servers, i.e. if its "On live bank server" flag is ticked. |
| SHORTNAME | No | VARCHAR(6) | No | The short name used for the Payment Type on some displays. |
| PTGROUP | No | INTEGER | No | The ID number of the Payment Group that the Payment Type belongs to. Taken from the PGNO field of the PAYMENT_GROUP table. |
| CURRENCYNO | No | INTEGER | No | |
| LISTSEQ | No | INTEGER | No | The value of this field determines the Payment Type's position in lists. |
| REFUND | No | CHAR(1) | No | This value is set to 'Y' if the Payment Type allows refunds, i.e. if its "Permit use on refund/credit" flag is enabled. |
| OVER_TEND | No | CHAR(1) | No | This value is set to 'Y' if the Payment Type's "Allow to be over tendered" flag is enabled. |
| MIN_TEND | No | FLOAT(53) | No | |
| MAX_TEND | No | FLOAT(53) | No | |
| MAX_PAYOUT | No | FLOAT(53) | No | |
| EFTPOS | No | INTEGER | No | The Payment Type's EFTPOS behaviour. This will be one of:
0 = no EFT interface
1 = validate only (cheque)
2 = process EFT sale
3 = process EFT cash out |
| MEMBER_TYPE | No | INTEGER | No | |
| ACC_MASK | No | VARCHAR(20) | No | |
| ROUND_AMT | No | INTEGER | No | This field specifies the number of cents that the Payment Type should round to. |
| ROUND_UP | No | CHAR(1) | No | This field contains 'Y' if the Payment Type's "Round up when midway" flag is ticked. |
| FEE_AMT | No | FLOAT(53) | No | |
| FEE_MAX | No | FLOAT(53) | No | |
| FEE_STOCKITEM | No | VARCHAR(40) | No | |
| IMAGE_FILE_INDEX | No | INTEGER | No | Specifies the Toolbar image to display on EXO Business interfaces for the Payment Type. |
| ACTIVE_DR | No | CHAR(1) | No | This field is ticked if the Payment Type is active for Debtors, i.e. if its "Active for debtors" flag is ticked. |
| ACTIVE_CR | No | CHAR(1) | No | This field is ticked if the Payment Type is active for Creditors, i.e. if its "Active for creditors" flag is ticked. |
| ACTIVE_POS | No | CHAR(1) | No | This field is ticked if the Payment Type is active for the EXO POS module, i.e. if its "Active for POS" flag is ticked. |
| CHEQUE_TYPE | No | CHAR(1) | No | This value is set to 'Y' if the Payment Type's Payment behaviour is set to "Cheque". |
| DIRECT_DEBIT_TYPE | No | CHAR(1) | No | This value is set to 'Y' if the Payment Type's Payment behaviour is set to "Direct Debit". |
| DIRECT_CREDIT_TYPE | No | CHAR(1) | No | This value is set to 'Y' if the Payment Type's Payment behaviour is set to "Direct Credit". |
| CASHOUTPTNO | No | INTEGER | Yes | This field contains the ID number of the Payment Type that this Payment Type uses as its Linked cash out payment type. |
| BANKFEE | No | CHAR(1) | No | This value is set to 'Y' if the Payment Type's "Apply payment group bank fee" flag is ticked. |
| ZERO_TEND | No | CHAR(1) | No | This value is set to 'Y' if the Payment Type's "Allow zero tender value" flag is ticked. |
| GATEWAY | No | INTEGER | No | This field specifies the Payment Type's Gateway Behaviour. This will be one of:
0 = None
1 = Purchase
2 = Authorise |
| POS_SALE | No | CHAR(1) | No | This value is set to 'Y' if the Payment Type can be used for sales in EXO Business POS, i.e. if its "Sales" flag is ticked in the Available on POS Transactions section. |
| POS_RECEIPT | No | CHAR(1) | No | This value is set to 'Y' if the Payment Type can be used for receipts in EXO Business POS, i.e. if its "Receipts" flag is ticked in the Available on POS Transactions section. |
| POS_CREDIT | No | CHAR(1) | No | This value is set to 'Y' if the Payment Type can be used for credits in EXO Business POS, i.e. if its "Credits" flag is ticked in the Available on POS Transactions section. |
| POS_REFUND | No | CHAR(1) | No | This value is set to 'Y' if the Payment Type can be used for refunds in EXO Business POS, i.e. if its "Refunds" flag is ticked in the Available on POS Transactions section. |
| POS_QUOTE | No | CHAR(1) | No | This value is set to 'Y' if the Payment Type can be used for quote in EXO Business POS, i.e. if its "Quotes" flag is ticked in the Available on POS Transactions section. |
| POS_LAYBY | No | CHAR(1) | No | This value is set to 'Y' if the Payment Type can be used for lay-bys in EXO Business POS, i.e. if its "Lay-bys" flag is ticked in the Available on POS Transactions section. |
| REFERENCELEVEL | No | INTEGER | No | This field specifies the Payment Type's Drawer/Reference level. This will be one of:
0 = Not Editable
1 = Editable
2 = Must Fill |
| INCASHDRAWER | No | CHAR(1) | No | This value is set to 'Y' if the Payment Type's "Open Cash Drawer" flag is ticked. |
| MPOWERED_TYPE | No | CHAR(1) | No | This value is set to 'Y' if the Payment Type's Payment behaviour is set to "M-Powered". |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| PAYMENT_GROUP_PAYMENT_TYPES | Non Identifying | PAYMENT_GROUP | PAYMENT_TYPES | Zero Or More |
| PAYMENT_TYPES_DR_TRANS | Non Identifying | PAYMENT_TYPES | DR_TRANS | Zero Or More |
Indexes:
| Name | Index columns | Index type |
| PAYMENT_TYPES_DESC_INDEX | PTDESC ASC | UNIQUE |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (CASHOUTPTNO) |
| Not Null | Column Constraint | NOT NULL (PTNO) |
| DF__PAYMENT_T__ACTIV__25A75D29 | Default | Column Constraint | ACTIVE_DR DEFAULT N |
| DF__PAYMENT_T__ACTIV__269B8162 | Default | Column Constraint | ACTIVE_CR DEFAULT N |
| DF__PAYMENT_T__ACTIV__278FA59B | Default | Column Constraint | ACTIVE_POS DEFAULT N |
| DF__PAYMENT_T__BANKF__35FDC083 | Default | Column Constraint | BANKFEE DEFAULT N |
| DF__PAYMENT_T__CASHO__68343A95 | Default | Column Constraint | CASHOUTPTNO DEFAULT -1 |
| DF__PAYMENT_T__GATEW__4BED01A2 | Default | Column Constraint | GATEWAY DEFAULT 0 |
| DF__PAYMENT_T__INCAS__6BFAA9D8 | Default | Column Constraint | INCASHDRAWER DEFAULT Y |
| DF__PAYMENT_T__LIVE___20E2A80C | Default | Column Constraint | LIVE_TRANS DEFAULT N |
| DF__PAYMENT_T__MPOWE__7D9A3726 | Default | Column Constraint | MPOWERED_TYPE DEFAULT N |
| DF__PAYMENT_T__OVER___23BF14B7 | Default | Column Constraint | OVER_TEND DEFAULT N |
| DF__PAYMENT_T__POS_C__61A73897 | Default | Column Constraint | POS_CREDIT DEFAULT Y |
| DF__PAYMENT_T__POS_L__6483A542 | Default | Column Constraint | POS_LAYBY DEFAULT Y |
| DF__PAYMENT_T__POS_Q__638F8109 | Default | Column Constraint | POS_QUOTE DEFAULT Y |
| DF__PAYMENT_T__POS_R__60B3145E | Default | Column Constraint | POS_RECEIPT DEFAULT Y |
| DF__PAYMENT_T__POS_R__629B5CD0 | Default | Column Constraint | POS_REFUND DEFAULT Y |
| DF__PAYMENT_T__POS_S__5FBEF025 | Default | Column Constraint | POS_SALE DEFAULT Y |
| DF__PAYMENT_T__PTGRO__21D6CC45 | Default | Column Constraint | PTGROUP DEFAULT 0 |
| DF__PAYMENT_T__REFER__18F76D81 | Default | Column Constraint | REFERENCELEVEL DEFAULT 0 |
| DF__PAYMENT_T__REFUN__22CAF07E | Default | Column Constraint | REFUND DEFAULT N |
| DF__PAYMENT_T__ROUND__24B338F0 | Default | Column Constraint | ROUND_UP DEFAULT N |
| DF__PAYMENT_T__WEB_S__1FEE83D3 | Default | Column Constraint | WEB_SHOW DEFAULT N |
| DF__PAYMENT_T__ZERO___36F1E4BC | Default | Column Constraint | ZERO_TEND DEFAULT N |
| PAYMENT_GROUP_PAYMENT_TYPES | Foreign Key | Table Constraint | FOREIGN KEY (PTGROUP) REFERENCES PAYMENT_GROUP(PGNO) |
| PK__PAYMENT_TYPES__54EB90A0 | Primary Key | Table Constraint | PRIMARY KEY (PTNO) |
Entity: PERIOD_STATUS
| Comment | This table contains details of the age periods set up in the system. |
| Primary key columns | SEQNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique ID number for the period. |
| AGE | No | INTEGER | Yes | The age of the period. The current period has an age of 0. |
| LEDGER | No | CHAR(1) | Yes | The ledger for a specific age. |
| LOCKED | No | CHAR(1) | No | If set to 'Y', the period is locked, implying that this age in a specific ledger is closed and no more transactions should take place |
| PERIOD_SEQNO | No | INTEGER | Yes | The period's sequence number within fiscal year. |
| PERIODNAME | No | VARCHAR(20) | No | The name/description for the period. |
| PERIOD_SHORTNAME | No | VARCHAR(8) | No | A short name for the period. |
| REPORTCODE | No | VARCHAR(8) | No | A code used for reporting purposes. |
| YEARAGE | No | INTEGER | Yes | The ageing of the year that the period falls in. The current year has an age of 0. |
| STARTDATE | No | DATETIME | No | The start date for the period. |
| STOPDATE | No | DATETIME | No | The end date for the period. |
| MINSTOCKSEQNO | No | INTEGER | No | The first transaction for the period found in the Stock ledger. |
| MINGLSEQNO | No | INTEGER | No | The first transaction for the period found in the GLTRANS table. |
| MINTRANSEQNO | No | INTEGER | No | For the Debtors ledger, this specifies the first transaction for the period found in the DR_TRANS table.
For the Creditors ledger, this specifies the first transaction for the period found in the CR_TRANS table. |
| MINTRANLINESEQNO | No | INTEGER | No | For the Debtors ledger, this specifies the first transaction for the period found in the DR_INVLINES table.
For the Creditors ledger, this specifies the first transaction for the period found in the CR_INVLINES table. |
| MINORDSEQNO | No | INTEGER | No | For the Debtors ledger, this specifies the first transaction for the period found in the SALESORD_HDR table.
For the Creditors ledger, this specifies the first transaction for the period found in the PURCHORD_HDR table. |
| MINORDLINESEQNO | No | INTEGER | No | For the Debtors ledger, this specifies the first transaction for the period found in the SALESORD_LINES table.
For the Creditors ledger, this specifies the first transaction for the period found in the PURCHORD_LINES table. |
| FIN_QTR | No | INTEGER | Yes | The financial quarter that the period belongs to. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| FK_CRM_BUDGET_PERIOD_STATUS | Non Identifying | PERIOD_STATUS | CRM_BUDGET | Zero Or More |
| FK_CRM_BUDGET_PERIOD_STATUS1 | Non Identifying | PERIOD_STATUS | CRM_BUDGET | Zero Or More |
| PERIOD_STATUS_CR_ALLOCATIONS | Non Identifying | PERIOD_STATUS | CR_ALLOCATIONS | Zero Or More |
| PERIOD_STATUS_CR_TRANS | Non Identifying | PERIOD_STATUS | CR_TRANS | Zero Or More |
| PERIOD_STATUS_DR_ALLOCATIONS | Non Identifying | PERIOD_STATUS | DR_ALLOCATIONS | Zero Or More |
| PERIOD_STATUS_DR_TRANS | Non Identifying | PERIOD_STATUS | DR_TRANS | Zero Or More |
| PERIOD_STATUS_INWARDS_GOODS_COSTS | Non Identifying | PERIOD_STATUS | INWARDS_GOODS_COSTS | Zero Or More |
| PERIOD_STATUS_INWARDS_GOODS_LINES | Non Identifying | PERIOD_STATUS | INWARDS_GOODS_LINES | Zero Or More |
| PERIOD_STATUS_STOCK_TRANS | Non Identifying | PERIOD_STATUS | STOCK_TRANS | Zero Or More |
Indexes:
| Name | Index columns | Index type |
| PERIOD_STATUS_AGE_INDEX | AGE ASC | |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (FIN_QTR) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| Not Null | Column Constraint | NOT NULL (AGE) |
| Not Null | Column Constraint | NOT NULL (YEARAGE) |
| Not Null | Column Constraint | NOT NULL (LEDGER) |
| Not Null | Column Constraint | NOT NULL (PERIOD_SEQNO) |
| DF__PERIOD_STAT__AGE__0ABE5CC3 | Default | Column Constraint | AGE DEFAULT 0 |
| DF__PERIOD_ST__FIN_Q__1F1104E1 | Default | Column Constraint | FIN_QTR DEFAULT 0 |
| DF__PERIOD_ST__LOCKE__0BB280FC | Default | Column Constraint | LOCKED DEFAULT N |
| DF__PERIOD_ST__MINGL__5B301089 | Default | Column Constraint | MINGLSEQNO DEFAULT -1 |
| DF__PERIOD_ST__MINOR__5E0C7D34 | Default | Column Constraint | MINORDSEQNO DEFAULT -1 |
| DF__PERIOD_ST__MINOR__5F00A16D | Default | Column Constraint | MINORDLINESEQNO DEFAULT -1 |
| DF__PERIOD_ST__MINST__5A3BEC50 | Default | Column Constraint | MINSTOCKSEQNO DEFAULT -1 |
| DF__PERIOD_ST__MINTR__5C2434C2 | Default | Column Constraint | MINTRANSEQNO DEFAULT -1 |
| DF__PERIOD_ST__MINTR__5D1858FB | Default | Column Constraint | MINTRANLINESEQNO DEFAULT -1 |
| DF__PERIOD_ST__PERIO__0CA6A535 | Default | Column Constraint | PERIOD_SEQNO DEFAULT 0 |
| DF__PERIOD_ST__YEARA__0D9AC96E | Default | Column Constraint | YEARAGE DEFAULT 0 |
| PK__PERIOD_STATUS__09CA388A | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
Entity: SALESORD_HDR
| Comment | This table contains header information for all Sales Orders. |
| Primary key columns | SEQNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique ID number for the Sales Order. |
| STATUS | No | INTEGER | No | The status of the Sales Order. Will be one of:
0 = Not Processed
1 = Partly Processed
2 = Fully Processed
3 = Quotation
4 = Standing Order
5 = Layby
6 = Lost Quote |
| ACCNO | No | INTEGER | No | The ID number of the Debtor account that the Sales Order was created for. |
| ORDERDATE | No | DATETIME | No | The date and time when the Sales Order was created. |
| DUEDATE | No | DATETIME | No | The date and time when the Sales Order is due to the customer. |
| CUSTORDERNO | No | VARCHAR(20) | No | The customer's order number. |
| REFERENCE | No | VARCHAR(20) | No | An additional reference number for the Sales Order. |
| ADDRESS1 | No | VARCHAR(30) | No | Delivery address line 1. |
| ADDRESS2 | No | VARCHAR(30) | No | Delivery address line 2. |
| ADDRESS3 | No | VARCHAR(30) | No | Delivery address line 3. |
| ADDRESS4 | No | VARCHAR(30) | No | Delivery address line 4. |
| INSTRUCTIONS | No | VARCHAR(255) | No | Not used. |
| SUBTOTAL | No | FLOAT(53) | No | The subtotal amount on the Sales Order. |
| TAXTOTAL | No | FLOAT(53) | No | The total tax amount on the Sales Order. |
| SALESNO | No | INTEGER | No | The ID number of the salesperson assigned to the Sales Order. |
| CONTACT_SEQNO | No | INTEGER | No | The ID number of the Contact assigned to the Sales Order. |
| CURRENCYNO | No | INTEGER | No | The ID number of the currency that the Sales Order is in. |
| EXCHRATE | No | FLOAT(53) | No | The exchange rate for the currency that the Sales Order is in. |
| CONSIGNTOLOC | No | INTEGER | No | The ID number of the consignment location. |
| BRANCHNO | No | INTEGER | No | The Branch number on the Sales Order. |
| TAXINC | No | CHAR(1) | No | Set to 'Y' if the Sales Order is tax-inclusive. |
| BACKORDER | No | CHAR(1) | No | Set to 'Y' if the Sales Order is on back order. |
| MANIFEST | No | INTEGER | No | |
| DISPATCH_INFO | No | VARCHAR(70) | No | The dispatch method for the Sales Order. |
| HSTATUS | No | INTEGER | No | Not used |
| LAST_UPDATED | No | DATETIME | No | The date and time when the Sales Order was last updated. |
| ADDRESS5 | No | VARCHAR(30) | No | Delivery address line 5. |
| ADDRESS6 | No | VARCHAR(30) | No | Delivery address line 6. |
| PAYMENT_STATUS | No | INTEGER | No | Not used. |
| ORDTOTAL | No | AS | No | The total amount for the Sales Order. |
| DELIVERYCOUNT | No | INTEGER | Yes | A count of the number of times the order has been supplied. |
| INVOICECOUNT | No | INTEGER | Yes | A count of the number of invoices generated for the order. |
| NARRATIVE_SEQNO | No | INTEGER | No | The ID number of the narrative attached to the Sales Order header. |
| HAS_UNRELEASED | No | CHAR(1) | Yes | Set to 'Y' if there are quantities on the Sales Order that have not been released yet. |
| HAS_BACKORDERS | No | CHAR(1) | Yes | Set to 'Y' if there are quantities on the Sales Order that are on back order. |
| HAS_UNSUPPLIED | No | CHAR(1) | Yes | Set to 'Y' if there are quantities on the Sales Order that have not been supplied yet. |
| HAS_UNINVOICED | No | CHAR(1) | Yes | Set to 'Y' if there are quantities on the Sales Order that have not been invoiced yet. |
| HAS_UNPICKED | No | CHAR(1) | Yes | Set to 'Y' if there are quantities on the Sales Order that have not been picked yet. |
| PICKEDCOUNT | No | INTEGER | No | The amount of items on the Sales Order that have been picked. |
| RELEASECOUNT | No | INTEGER | No | The amount of items on the Sales Order that have been released. |
| ORDSTATUS | No | INTEGER | No | Additional status information for the Sales Order. Will be one of:
10 = Quote
20 =
Order
30 =
Acknowledged Order
40 =
Pick In Progress
50 =
Pick Confirmed
70 =
Supplied
80 =
Invoiced
90 =
Complete
100 = Lost Quote
160 = Force Fully Processed |
| DEFLOCNO | No | INTEGER | No | The default location that stock is being supplied from. |
| PROCESSFINALISATION | No | INTEGER | No | Will be one of:
0 = Normal
1 =
Deleted
2 = Forced Fully Processed
3 = Lost Quote |
| MAXCOURIERCHARGE | No | FLOAT(53) | No | Not used. |
| SHIP_COMPLETE | No | CHAR(1) | Yes | |
| TXID | No | VARBINARY(256) | No | The Transaction ID assigned to the Sales Order - this applies when using a payment gateway. |
| ONHOLD | No | CHAR(1) | No | Set to 'Y' if the Sales Order is on hold. |
| TAXROUNDING | No | FLOAT(53) | Yes | The difference between the tax calculated on header and the sum of the tax on all lines. |
| CREATE_DATE | No | DATETIME | No | The date and time when the Sales Order was created. |
| ACTIVATION_DATE | No | DATETIME | No | The date and time when the Sales Order was activated. |
| FINALISATION_DATE | No | DATETIME | No | The date and time when the Sales Order was finalised. |
| WAS_BACKORDERED | No | CHAR(1) | No | Set to 'Y' if the Sales Order has been placed in back order status at any time. |
| OPPORTUNITY_SEQNO | No | INTEGER | No | If the Sales Order is associated with an Opportunity (EXO Business CRM), the Opportunity's ID number is stored here. |
| CAMPAIGN_WAVE_SEQNO | No | INTEGER | No | If the Sales Order is associated with a campaign wave (EXO Business CRM), the wave's ID number is stored here. |
| LOCALVALUE | No | AS | No | The value of the Sales Order in the local currency. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CAMPAIGN_WAVE_SALESORD_HDR | Non Identifying | CAMPAIGN_WAVE | SALESORD_HDR | Zero Or More |
| DR_INVLINES_SALESORD_HDR | Non Identifying | DR_INVLINES | SALESORD_HDR | Zero Or More |
| NARRATIVES_SALESORD_HDR | Non Identifying | NARRATIVES | SALESORD_HDR | Zero Or More |
| OPPORTUNITY_SALESORD_HDR | Non Identifying | OPPORTUNITY | SALESORD_HDR | Zero Or More |
| SALESORD_HDR_TASKS | Non Identifying | SALESORD_HDR | TASKS | Zero Or More |
Indexes:
| Name | Index columns | Index type |
| SALESORD_HDR_IDX | STATUS ASC, DUEDATE ASC | |
| SALESORD_MULTI | ACCNO ASC, SALESNO ASC, OPPORTUNITY_SEQNO ASC | |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (TAXROUNDING) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| Not Null | Column Constraint | NOT NULL (SHIP_COMPLETE) |
| Not Null | Column Constraint | NOT NULL (HAS_UNPICKED) |
| Not Null | Column Constraint | NOT NULL (HAS_UNINVOICED) |
| Not Null | Column Constraint | NOT NULL (HAS_UNSUPPLIED) |
| Not Null | Column Constraint | NOT NULL (HAS_BACKORDERS) |
| Not Null | Column Constraint | NOT NULL (HAS_UNRELEASED) |
| Not Null | Column Constraint | NOT NULL (INVOICECOUNT) |
| Not Null | Column Constraint | NOT NULL (DELIVERYCOUNT) |
| CAMPAIGN_WAVE_SALESORD_HDR | Foreign Key | Table Constraint | FOREIGN KEY (CAMPAIGN_WAVE_SEQNO) REFERENCES CAMPAIGN_WAVE(SEQNO) |
| DF_SALESORD_HDR_ACCNO | Default | Column Constraint | ACCNO DEFAULT 0 |
| DF_SALESORD_HDR_DUEDATE | Default | Column Constraint | DUEDATE DEFAULT getdate() |
| DF_SALESORD_HDR_ORDERDATE | Default | Column Constraint | ORDERDATE DEFAULT getdate() |
| DF_SALESORD_HDR_STATUS | Default | Column Constraint | STATUS DEFAULT 0 |
| DF_SALESORD_HDR_SUBTOTAL | Default | Column Constraint | SUBTOTAL DEFAULT 0 |
| DF_SALESORD_HDR_TAXTOTAL | Default | Column Constraint | TAXTOTAL DEFAULT 0 |
| DF__SALESORD___BACKO__70FDBF69 | Default | Column Constraint | BACKORDER DEFAULT N |
| DF__SALESORD___BRANC__35DCF99B | Default | Column Constraint | BRANCHNO DEFAULT 0 |
| DF__SALESORD___CAMPA__552D1E70 | Default | Column Constraint | CAMPAIGN_WAVE_SEQNO DEFAULT -1 |
| DF__SALESORD___CONSI__2EB0D91F | Default | Column Constraint | CONSIGNTOLOC DEFAULT 0 |
| DF__SALESORD___CURRE__2EE5E349 | Default | Column Constraint | CURRENCYNO DEFAULT 0 |
| DF__SALESORD___DEFLO__3AC275A0 | Default | Column Constraint | DEFLOCNO DEFAULT 0 |
| DF__SALESORD___DELIV__7FABD173 | Default | Column Constraint | DELIVERYCOUNT DEFAULT 0 |
| DF__SALESORD___EXCHR__2FDA0782 | Default | Column Constraint | EXCHRATE DEFAULT 1 |
| DF__SALESORD___HAS_B__68294D9D | Default | Column Constraint | HAS_BACKORDERS DEFAULT N |
| DF__SALESORD___HAS_U__67352964 | Default | Column Constraint | HAS_UNRELEASED DEFAULT N |
| DF__SALESORD___HAS_U__691D71D6 | Default | Column Constraint | HAS_UNSUPPLIED DEFAULT N |
| DF__SALESORD___HAS_U__6A11960F | Default | Column Constraint | HAS_UNINVOICED DEFAULT N |
| DF__SALESORD___HAS_U__6B05BA48 | Default | Column Constraint | HAS_UNPICKED DEFAULT N |
| DF__SALESORD___INVOI__009FF5AC | Default | Column Constraint | INVOICECOUNT DEFAULT 0 |
| DF__SALESORD___MANIF__71F1E3A2 | Default | Column Constraint | MANIFEST DEFAULT 0 |
| DF__SALESORD___MAXCO__4CE125DB | Default | Column Constraint | MAXCOURIERCHARGE DEFAULT 0 |
| DF__SALESORD___ONHOL__44E0DCB7 | Default | Column Constraint | ONHOLD DEFAULT N |
| DF__SALESORD___OPPOR__73C6B429 | Default | Column Constraint | OPPORTUNITY_SEQNO DEFAULT -1 |
| DF__SALESORD___ORDST__1AF4C48C | Default | Column Constraint | ORDSTATUS DEFAULT 0 |
| DF__SALESORD___PAYME__505BE5AD | Default | Column Constraint | PAYMENT_STATUS DEFAULT 0 |
| DF__SALESORD___PICKE__17D860BF | Default | Column Constraint | PICKEDCOUNT DEFAULT 0 |
| DF__SALESORD___PROCE__3CAABE12 | Default | Column Constraint | PROCESSFINALISATION DEFAULT 0 |
| DF__SALESORD___RELEA__153BEB36 | Default | Column Constraint | RELEASECOUNT DEFAULT 0 |
| DF__SALESORD___SALES__70E8B0D0 | Default | Column Constraint | SALESNO DEFAULT 0 |
| DF__SALESORD___SHIP___0EAEE938 | Default | Column Constraint | SHIP_COMPLETE DEFAULT N |
| DF__SALESORD___TAXIN__5555A4F4 | Default | Column Constraint | TAXINC DEFAULT N |
| DF__SALESORD___TAXRO__5B651AB3 | Default | Column Constraint | TAXROUNDING DEFAULT 0 |
| DF__SALESORD___WAS_B__65AD9EFC | Default | Column Constraint | WAS_BACKORDERED DEFAULT N |
| DR_INVLINES_SALESORD_HDR | Foreign Key | Table Constraint | FOREIGN KEY (ACCNO) REFERENCES DR_INVLINES(ACCNO) |
| NARRATIVES_SALESORD_HDR | Foreign Key | Table Constraint | FOREIGN KEY (NARRATIVE_SEQNO) REFERENCES NARRATIVES(SEQNO) |
| OPPORTUNITY_SALESORD_HDR | Foreign Key | Table Constraint | FOREIGN KEY (OPPORTUNITY_SEQNO) REFERENCES OPPORTUNITY(SEQNO) |
| PK_SALESORD_HDR_1__11 | 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
|
Entity: TAX_RATES
| Comment | This table contains all tax rates used by the system. |
| Primary key columns | SEQNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique ID number for the tax rate. |
| RATE | No | FLOAT(53) | No | The percentage taxation rate. |
| NAME | No | VARCHAR(30) | No | The full name of the taxation rate. |
| SHORTNAME | No | VARCHAR(6) | No | A short name for the taxation rate. |
| BASE | No | FLOAT(53) | No | |
| GLACC | No | INTEGER | No | The GL account used for the tax rate (if it is not using the default control account). |
| GLSUBACC | No | INTEGER | No | The GL subaccount used for the tax rate (if it is not using the default control subaccount). |
| KEY_POINT | No | VARCHAR(5) | No | The tax return key point that applies to the tax rate. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| TAX_RATES_CR_INVLINES | Non Identifying | TAX_RATES | CR_INVLINES | Zero Or More |
| TAX_RATES_CR_TRANS | Non Identifying | TAX_RATES | CR_TRANS | Zero Or More |
| TAX_RATES_DR_INVLINES | Non Identifying | TAX_RATES | DR_INVLINES | Zero Or More |
| TAX_RATES_DR_TRANS | Non Identifying | TAX_RATES | DR_TRANS | Zero Or More |
| TAX_RATES_JOBCOST_LINES | Non Identifying | TAX_RATES | JOBCOST_LINES | Zero Or More |
| TAX_RATES_JOB_TRANSACTIONS | Non Identifying | TAX_RATES | JOB_TRANSACTIONS | Zero Or More |
| TAX_RATES_OPPORTUNITY_QUOTE | Non Identifying | TAX_RATES | OPPORTUNITY_QUOTE | Zero Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| DF__TAX_RATES__BASE__1E5A75C5 | Default | Column Constraint | BASE DEFAULT 0 |
| DF__TAX_RATES__GLACC__1F4E99FE | Default | Column Constraint | GLACC DEFAULT 0 |
| DF__TAX_RATES__GLSUB__2042BE37 | Default | Column Constraint | GLSUBACC DEFAULT 0 |
| DF__TAX_RATES__RATE__668030F6 | Default | Column Constraint | RATE DEFAULT 0 |
| PK__TAX_RATES__658C0CBD | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
Triggers:
| Trigger name | Code |
| BEFORE_TAX_RATES_INSERT | CREATE TRIGGER [dbo].[BEFORE_TAX_RATES_INSERT] ON [dbo].[TAX_RATES]
FOR INSERT
AS
SET NOCOUNT ON
DECLARE @NEW_SHORTNAME VARCHAR(6)
DECLARE @NEW_SEQNO INTEGER
DECLARE @NEW_RATE FLOAT
SELECT
@NEW_SHORTNAME=SHORTNAME,
@NEW_RATE=RATE,
@NEW_SEQNO=SEQNO
FROM
INSERTED
IF (@NEW_SHORTNAME IS NULL) OR (@NEW_SHORTNAME = '')
UPDATE [dbo].[TAX_RATES] SET SHORTNAME = CAST(@NEW_RATE AS VARCHAR(5))+'%' WHERE SEQNO=@NEW_SEQNO
SET NOCOUNT OFF
RETURN
|
|