MYOB Exo Business CRM

Entity details

Entity: CAMPAIGN

Comment	This table contains details of all marketing campaigns in EXO 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.
TITLE	No	VARCHAR(60)	No	A title/description for the campaign.
REFERENCE	No	VARCHAR(15)	No	A short, user-defined reference code for the campaign.
DESCRIPT	No	VARCHAR(5000)	No	Any notes entered for the campaign.
OWNER	No	INTEGER	No	The ID number of the staff member who is assigned as the campaign's owner.
CREATEDATE	No	DATETIME	No	The date the campaign was created on.
STARTDATE	No	DATETIME	No	The Start Date specified for the campaign.
LAST_UPDATED	No	DATETIME	No	The date that the campaign was last edited on.
ENDDATE	No	DATETIME	No	The End Date specified for the campaign.
CAMPAIGN_STAGE	No	INTEGER	No	The ID number of the Stage specified for the campaign, from the CAMPAIGN_STAGE table.
EST_COST	No	FLOAT(53)	No	The Estimated Cost entered for the campaign.
EST_REVENUE	No	FLOAT(53)	No	The Estimated Revenue amount entered for the campaign.
EST_RESPONSE	No	FLOAT(53)	No	The Estimated Response entered for the campaign.
CAMPAIGN_TYPE	No	INTEGER	Yes	The ID number of the Type specified for the campaign, from the CAMPAIGN_TYPE table.
JOB_LINK	No	INTEGER	No	If campaign costs are being tracked by linking the campaign to a job in the EXO Job Costing system, the linked job's ID number is recorded here.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
CAMPAIGN_CAMPAIGN_CONTACT_LISTS	Non Identifying	CAMPAIGN	CAMPAIGN_CONTACT_LISTS	Zero Or More
CAMPAIGN_CAMPAIGN_HIST	Non Identifying	CAMPAIGN	CAMPAIGN_HIST	Zero Or More
CAMPAIGN_CAMPAIGN_WAVE	Non Identifying	CAMPAIGN	CAMPAIGN_WAVE	Zero Or More
CAMPAIGN_STAGE_CAMPAIGN	Non Identifying	CAMPAIGN_STAGE	CAMPAIGN	Zero Or More
CAMPAIGN_TASKS	Non Identifying	CAMPAIGN	TASKS	Zero Or More
CAMPAIGN_TYPE_CAMPAIGN	Non Identifying	CAMPAIGN_TYPE	CAMPAIGN	Zero Or More
STAFF_CAMPAIGN	Non Identifying	STAFF	CAMPAIGN	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (CAMPAIGN_TYPE)
	Not Null	Column Constraint	NOT NULL (SEQNO)
CAMPAIGN_STAGE_CAMPAIGN	Foreign Key	Table Constraint	FOREIGN KEY (CAMPAIGN_STAGE) REFERENCES CAMPAIGN_STAGE(SEQNO)
CAMPAIGN_TYPE_CAMPAIGN	Foreign Key	Table Constraint	FOREIGN KEY (CAMPAIGN_TYPE) REFERENCES CAMPAIGN_TYPE(SEQNO)
DF__CAMPAIGN__CAMPAI__1CE8C54D	Default	Column Constraint	CAMPAIGN_STAGE DEFAULT 0
DF__CAMPAIGN__CAMPAI__20B95631	Default	Column Constraint	CAMPAIGN_TYPE DEFAULT 1
DF__CAMPAIGN__CREATE__19183469	Default	Column Constraint	CREATEDATE DEFAULT getdate()
DF__CAMPAIGN__ENDDAT__1BF4A114	Default	Column Constraint	ENDDATE DEFAULT getdate()
DF__CAMPAIGN__EST_CO__1DDCE986	Default	Column Constraint	EST_COST DEFAULT 0
DF__CAMPAIGN__EST_RE__1ED10DBF	Default	Column Constraint	EST_REVENUE DEFAULT 0
DF__CAMPAIGN__EST_RE__1FC531F8	Default	Column Constraint	EST_RESPONSE DEFAULT 0
DF__CAMPAIGN__JOB_LI__21AD7A6A	Default	Column Constraint	JOB_LINK DEFAULT -1
DF__CAMPAIGN__LAST_U__1B007CDB	Default	Column Constraint	LAST_UPDATED DEFAULT getdate()
DF__CAMPAIGN__STARTD__1A0C58A2	Default	Column Constraint	STARTDATE DEFAULT getdate()
PK__CAMPAIGN__C8320F98172FEBF7	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)
STAFF_CAMPAIGN	Foreign Key	Table Constraint	FOREIGN KEY (OWNER) REFERENCES STAFF(STAFFNO)

Entity: CAMPAIGN_CONTACT_LISTS

Comment	This table contains records of the contact lists that are assigned to campaigns.
Primary key columns

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	No	INTEGER	Yes	A unique ID number for the record.
CAMPAIGN_SEQNO	No	INTEGER	Yes	The ID number of the campaign that the contact list is assigned to.
CONTACT_LIST_SEQNO	No	INTEGER	Yes	The ID number of the contact list that is assigned to the campaign.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
CAMPAIGN_CAMPAIGN_CONTACT_LISTS	Non Identifying	CAMPAIGN	CAMPAIGN_CONTACT_LISTS	Zero Or More
CONTACT_LIST_CAMPAIGN_CONTACT_LISTS	Non Identifying	CONTACT_LIST	CAMPAIGN_CONTACT_LISTS	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (CONTACT_LIST_SEQNO)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (CAMPAIGN_SEQNO)
CAMPAIGN_CAMPAIGN_CONTACT_LISTS	Foreign Key	Table Constraint	FOREIGN KEY (CAMPAIGN_SEQNO) REFERENCES CAMPAIGN(SEQNO)
CONTACT_LIST_CAMPAIGN_CONTACT_LISTS	Foreign Key	Table Constraint	FOREIGN KEY (CONTACT_LIST_SEQNO) REFERENCES CONTACT_LIST(SEQNO)

Entity: CAMPAIGN_HIST

Comment	This table contains details of History Notes on campaigns.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the History Note.
CAMPAIGN_SEQNO	No	INTEGER	Yes	The ID number of the campaign that the History Note is attached to.
SALESNO	No	INTEGER	No	The ID number of the staff member who entered the History Note.
TRANSDATE	No	DATETIME	No	The date and time that the History Note was created on.
SUBJECT	No	VARCHAR(80)	No	The subject line of the History Note.
NOTE	No	VARCHAR(4096)	No	The body of the History Note.
OUTLOOK_LINK	No	VARCHAR(40)	No	If the History Note was emailed, this field contains a link to the email in MS Outlook.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
CAMPAIGN_CAMPAIGN_HIST	Non Identifying	CAMPAIGN	CAMPAIGN_HIST	Zero Or More
STAFF_CAMPAIGN_HIST	Non Identifying	STAFF	CAMPAIGN_HIST	Zero Or More

Indexes:

Name	Index columns	Index type
CAMPAIGN_HIST_INDEX	CAMPAIGN_SEQNO ASC

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (CAMPAIGN_SEQNO)
	Not Null	Column Constraint	NOT NULL (SEQNO)
CAMPAIGN_CAMPAIGN_HIST	Foreign Key	Table Constraint	FOREIGN KEY (CAMPAIGN_SEQNO) REFERENCES CAMPAIGN(SEQNO)
DF__CAMPAIGN___TRANS__48C7478B	Default	Column Constraint	TRANSDATE DEFAULT getdate()
PK__CAMPAIGN__C8320F9846DEFF19	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)
STAFF_CAMPAIGN_HIST	Foreign Key	Table Constraint	FOREIGN KEY (SALESNO) REFERENCES STAFF(STAFFNO)

Entity: CAMPAIGN_STAGE

Comment	This table records details of the Campaign Stages that are assigned to campaigns in EXO 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 stage.
DESCRIPT	No	VARCHAR(60)	No	The stage's name/description.
STATUSKEY	No	CHAR(1)	No	A one-character code for the stage.
ADMIN_STAT	No	CHAR(1)	Yes	If this field is set to 'Y', the campaign stage does not allow the entry of new transactions.
LOCK_CAMPAIGN	No	CHAR(1)	Yes	If this field is set to 'Y', the campaign stage makes campaigns locked, i.e. read-only.
ISARCHIVED	No	CHAR(1)	Yes	If this field is set to 'Y', campaigns at this stage will be archived.
ISCOMPLETE	No	CHAR(1)	Yes	If this field is set to 'Y', campaigns at this stage are complete.
ISACTIVE	No	CHAR(1)	Yes	If this field is set to 'Y', campaigns at this stage are active.
WORKFLOW_CONSTRAINED	No	CHAR(1)	Yes	If this field is set to 'Y', workflow constraints (defined in the CAMPAIGN_STAGE_CONSTRAINT) apply to the campaign stage.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
CAMPAIGN_STAGE_CAMPAIGN	Non Identifying	CAMPAIGN_STAGE	CAMPAIGN	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (WORKFLOW_CONSTRAINED)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (ADMIN_STAT)
	Not Null	Column Constraint	NOT NULL (ISACTIVE)
	Not Null	Column Constraint	NOT NULL (LOCK_CAMPAIGN)
	Not Null	Column Constraint	NOT NULL (ISCOMPLETE)
	Not Null	Column Constraint	NOT NULL (ISARCHIVED)
DF__CAMPAIGN___ADMIN__26722F87	Default	Column Constraint	ADMIN_STAT DEFAULT N
DF__CAMPAIGN___ISACT__2A42C06B	Default	Column Constraint	ISACTIVE DEFAULT N
DF__CAMPAIGN___ISARC__285A77F9	Default	Column Constraint	ISARCHIVED DEFAULT N
DF__CAMPAIGN___ISCOM__294E9C32	Default	Column Constraint	ISCOMPLETE DEFAULT N
DF__CAMPAIGN___LOCK___276653C0	Default	Column Constraint	LOCK_CAMPAIGN DEFAULT N
DF__CAMPAIGN___WORKF__2B36E4A4	Default	Column Constraint	WORKFLOW_CONSTRAINED DEFAULT N
PK__CAMPAIGN__C8320F982489E715	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)

Entity: CAMPAIGN_STAGE_CONSTRAINT

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

Attributes:

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

Constraints:

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

Entity: CAMPAIGN_TYPE

Comment	This table contains details of the campaign types used by campaigns in EXO CRM.
Primary key columns

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	No	INTEGER	Yes	A unique ID number for the campaign type.
DESCRIPT	No	VARCHAR(60)	No	The campaign type's name/description.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
CAMPAIGN_TYPE_CAMPAIGN	Non Identifying	CAMPAIGN_TYPE	CAMPAIGN	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (SEQNO)

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: CAMPAIGN_WAVE_AUDIT

Comment	This table contains a record of which Contacts each campaign wave was sent to.
Primary key columns

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	No	INTEGER	Yes	A unique ID number for the audit line.
CAMPAIGN_WAVE_SEQNO	No	INTEGER	Yes	The ID number of the campaign wave.
CONTACT_SEQNO	No	INTEGER	Yes	The ID number of the Contact that the wave was sent to.
LOGDATETIME	No	DATETIME	Yes	The date and time that the campaign wave was sent.
COMMUNICATION_METHOD	No	INTEGER	No	The campaign wave's communication method. 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
EXECUTE_TYPE	No	VARCHAR(15)	No	How communication was sent to the contact - "EMAILED" or "PRINTED".

Relationships:

Name	Relationship type	Parent	Child	Cardinality
CAMPAIGN_WAVE_CAMPAIGN_WAVE_AUDIT	Non Identifying	CAMPAIGN_WAVE	CAMPAIGN_WAVE_AUDIT	Zero Or More
CONTACTS_CAMPAIGN_WAVE_AUDIT	Non Identifying	CONTACTS	CAMPAIGN_WAVE_AUDIT	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (LOGDATETIME)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (CAMPAIGN_WAVE_SEQNO)
	Not Null	Column Constraint	NOT NULL (CONTACT_SEQNO)
CAMPAIGN_WAVE_CAMPAIGN_WAVE_AUDIT	Foreign Key	Table Constraint	FOREIGN KEY (CAMPAIGN_WAVE_SEQNO) REFERENCES CAMPAIGN_WAVE(SEQNO)
CONTACTS_CAMPAIGN_WAVE_AUDIT	Foreign Key	Table Constraint	FOREIGN KEY (CONTACT_SEQNO) REFERENCES CONTACTS(SEQNO)
DF__CAMPAIGN___COMMU__5B7007AB	Default	Column Constraint	COMMUNICATION_METHOD DEFAULT 0
DF__CAMPAIGN___LOGDA__5EB688AA	Default	Column Constraint	LOGDATETIME DEFAULT getdate()

Triggers:

Trigger name Code

AFTER_ADD_CAMPAIGN_WAVE_AUDIT CREATE TRIGGER [dbo].[AFTER_ADD_CAMPAIGN_WAVE_AUDIT]
ON [dbo].[CAMPAIGN_WAVE_AUDIT]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO CONTACT_HIST
(CONTACT_SEQNO, POSTTIME, TRANSDATE, NOTE, [SUBJECT], ACTIONDUEDATE, SALESNO)
SELECT
CWA.[CONTACT_SEQNO],
GETDATE(),
CWA.[LOGDATETIME],
C.TITLE+'-'+CW.DESCRIPT+' '+CP.DESCRIPT,
C.TITLE+'-'+CP.DESCRIPT,
GETDATE(),
[DBO].FN_GET_STAFF_NO()
FROM INSERTED CWA /*[dbo].[CAMPAIGN_WAVE_AUDIT]*/
JOIN CAMPAIGN_WAVE CW ON CW.SEQNO = CWA.CAMPAIGN_WAVE_SEQNO AND CW.COMMUNICATION_METHOD <> 1
JOIN CAMPAIGN C ON CW.CAMPAIGN_SEQNO = C.SEQNO
JOIN COMMUNICATION_PROCESSES CP ON CP.SEQNO=CW.COMMUNICATION_METHOD
SET NOCOUNT OFF
END

Entity: CAMPAIGN_WAVE_CONTACT_LISTS

Comment	This table contains records of the contact lists that are used by campaign waves.
Primary key columns

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	No	INTEGER	Yes	A unique ID number for the record.
CAMPAIGN_WAVE_SEQNO	No	INTEGER	Yes	The ID number of the campaign wave that uses the list.
CONTACT_LIST_SEQNO	No	INTEGER	Yes	The ID number of the contact list that is used by the campaign wave.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
CAMPAIGN_WAVE_CAMPAIGN_WAVE_CONTACT_LISTS	Non Identifying	CAMPAIGN_WAVE	CAMPAIGN_WAVE_CONTACT_LISTS	Zero Or More
CONTACT_LIST_CAMPAIGN_WAVE_CONTACT_LISTS	Non Identifying	CONTACT_LIST	CAMPAIGN_WAVE_CONTACT_LISTS	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (CONTACT_LIST_SEQNO)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (CAMPAIGN_WAVE_SEQNO)
CAMPAIGN_WAVE_CAMPAIGN_WAVE_CONTACT_LISTS	Foreign Key	Table Constraint	FOREIGN KEY (CAMPAIGN_WAVE_SEQNO) REFERENCES CAMPAIGN_WAVE(SEQNO)
CONTACT_LIST_CAMPAIGN_WAVE_CONTACT_LISTS	Foreign Key	Table Constraint	FOREIGN KEY (CONTACT_LIST_SEQNO) REFERENCES CONTACT_LIST(SEQNO)

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: CONTACT_LIST

Comment	This table contains details of the contact lists used by EXO 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 contact list.
TITLE	No	VARCHAR(60)	No	The contact list's name.
DESCRIPT	No	VARCHAR(1000)	No	The text entered into the contact list's Notes field.
CONTACT_LIST_TYPE	No	INTEGER	No	The ID number of the contact list's List Type, taken from CONTACT_LIST_TYPE.
IS_PRIVATE	No	CHAR(1)	No	This field contains 'Y' if the contact list is private, i.e. only available to its owner (see the OWNER field).
IS_ACTIVE	No	CHAR(1)	No	This field contains 'Y' if the contact list is active.
CREATED_BY	No	INTEGER	Yes	The ID number of the staff member who created the contact list.
CREATEDATE	No	DATETIME	Yes	The date when the contact list was created.
LAST_MODIFIED	No	DATETIME	Yes	The date when the contact list was last edited.
SETTINGS	No	VARCHAR(max)	No	SQL settings used by the contact list.
OWNER	No	INTEGER	Yes	The ID number of the staff member who is designated as the contact list's owner.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
CONTACT_LIST_CAMPAIGN_CONTACT_LISTS	Non Identifying	CONTACT_LIST	CAMPAIGN_CONTACT_LISTS	Zero Or More
CONTACT_LIST_CAMPAIGN_WAVE_CONTACT_LISTS	Non Identifying	CONTACT_LIST	CAMPAIGN_WAVE_CONTACT_LISTS	Zero Or More
CONTACT_LIST_CONTACT_LIST_ITEM	Non Identifying	CONTACT_LIST	CONTACT_LIST_ITEM	Zero Or More
CONTACT_LIST_TYPE_CONTACT_LIST	Non Identifying	CONTACT_LIST_TYPE	CONTACT_LIST	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

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (OWNER)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (LAST_MODIFIED)
	Not Null	Column Constraint	NOT NULL (CREATEDATE)
	Not Null	Column Constraint	NOT NULL (CREATED_BY)
CONTACT_LIST_TYPE_CONTACT_LIST	Foreign Key	Table Constraint	FOREIGN KEY (CONTACT_LIST_TYPE) REFERENCES CONTACT_LIST_TYPE(SEQNO)
DF__CONTACT_L__CONTA__34C04EDE	Default	Column Constraint	CONTACT_LIST_TYPE DEFAULT 0
DF__CONTACT_L__CREAT__379CBB89	Default	Column Constraint	CREATED_BY DEFAULT 0
DF__CONTACT_L__CREAT__3890DFC2	Default	Column Constraint	CREATEDATE DEFAULT getdate()
DF__CONTACT_L__IS_AC__36A89750	Default	Column Constraint	IS_ACTIVE DEFAULT Y
DF__CONTACT_L__IS_PR__35B47317	Default	Column Constraint	IS_PRIVATE DEFAULT N
DF__CONTACT_L__LAST___398503FB	Default	Column Constraint	LAST_MODIFIED DEFAULT getdate()
DF__CONTACT_L__OWNER__3A792834	Default	Column Constraint	OWNER DEFAULT 0
PK__CONTACT___C8320F9832D8066C	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)
STAFF_CONTACT_LIST	Foreign Key	Table Constraint	FOREIGN KEY (CREATED_BY) REFERENCES STAFF(STAFFNO)
STAFF_CONTACT_LIST1	Foreign Key	Table Constraint	FOREIGN KEY (OWNER) REFERENCES STAFF(STAFFNO)

Entity: CONTACT_LIST_ITEM

Comment	This table contains details of the members of the contact lists used by EXO 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 record.
HDR_SEQNO	No	INTEGER	Yes	The ID number of the contact list (CONTACT_LIST.SEQNO).
ITEM_TYPE	No	INTEGER	Yes	The type of item. 1 = Contact
ITEM_SEQNO	No	INTEGER	No	The item's ID number. For contacts, this is CONTACTS.SEQNO.
QUERY_INSERTED	No	CHAR(1)	No	This field contains 'Y' if the item was added to the list using a query on the List Building Criteria tab of the Contact Lists window; it contains 'N' if the item was added manually.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
CONTACTS_CONTACT_LIST_ITEM	Non Identifying	CONTACTS	CONTACT_LIST_ITEM	Zero Or More
CONTACT_LIST_CONTACT_LIST_ITEM	Non Identifying	CONTACT_LIST	CONTACT_LIST_ITEM	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (ITEM_TYPE)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (HDR_SEQNO)
CONTACTS_CONTACT_LIST_ITEM	Foreign Key	Table Constraint	FOREIGN KEY (ITEM_SEQNO) REFERENCES CONTACTS(SEQNO)
CONTACT_LIST_CONTACT_LIST_ITEM	Foreign Key	Table Constraint	FOREIGN KEY (HDR_SEQNO) REFERENCES CONTACT_LIST(SEQNO)
DF__CONTACT_L__HDR_S__3F3DDD51	Default	Column Constraint	HDR_SEQNO DEFAULT -1
DF__CONTACT_L__ITEM___4032018A	Default	Column Constraint	ITEM_TYPE DEFAULT -1
DF__CONTACT_L__QUERY__412625C3	Default	Column Constraint	QUERY_INSERTED DEFAULT N
PK__CONTACT___C8320F983D5594DF	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)

Triggers:

Trigger name	Code
AFTER_ADD_CONTACTLIST	CREATE TRIGGER [dbo].[AFTER_ADD_CONTACTLIST] ON [dbo].[CONTACT_LIST_ITEM] FOR INSERT AS BEGIN SET NOCOUNT ON INSERT INTO CONTACT_HIST (CONTACT_SEQNO, POSTTIME, TRANSDATE, NOTE, SUBJECT, ACTIONDUEDATE, SALESNO) SELECT I.ITEM_SEQNO, GETDATE(), GETDATE(), 'Contact List : ' + CL.TITLE + char(13)+Char(10) + 'Added on : ' + CONVERT (varchar, GETDATE()) , 'Added to Contact List', GETDATE(), dbo.FN_GET_STAFF_NO() FROM CONTACT_LIST CL JOIN INSERTED I ON CL.SEQNO = I.HDR_SEQNO SET NOCOUNT OFF END
AFTER_DELETE_CONTACTLIST	CREATE TRIGGER [dbo].[AFTER_DELETE_CONTACTLIST] ON [dbo].[CONTACT_LIST_ITEM] FOR DELETE AS BEGIN SET NOCOUNT ON INSERT INTO CONTACT_HIST (CONTACT_SEQNO, POSTTIME, TRANSDATE, NOTE, SUBJECT, ACTIONDUEDATE, SALESNO) SELECT D.ITEM_SEQNO, GETDATE(), GETDATE(), 'Contact List : ' + CL.TITLE + char(13)+Char(10) + 'Deleted on : ' + CONVERT (varchar, GETDATE()) , 'Deleted from Contact List', GETDATE(), dbo.FN_GET_STAFF_NO() FROM CONTACT_LIST CL JOIN DELETED D ON CL.SEQNO = D.HDR_SEQNO SET NOCOUNT OFF END

Entity: CONTACT_LIST_TYPE

Comment	This table contains details of the List Types used by contact lists in EXO CRM.
Primary key columns

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	No	INTEGER	Yes	A unique ID number for the type.
DESCRIPT	No	VARCHAR(60)	No	The name/description of the type.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
CONTACT_LIST_TYPE_CONTACT_LIST	Non Identifying	CONTACT_LIST_TYPE	CONTACT_LIST	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (SEQNO)

Entity: CRM_BUDGET

Comment	This table contains details of Sales Team Budgets in EXO CRM.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	The ID number of the budget record.
NAME	No	VARCHAR(1024)	Yes	The period name.
HEADER_SEQNO	No	INTEGER	Yes	The ID number of the budget this line belongs to (links to CRM_BUDGET_HDR.SEQNO)
MANREP_PERIOD_SEQNO	No	INTEGER	Yes	The ID number of the period as set up in EXO Business Analytics (links to MANREP_PERIOD.PERIOD_SEQNO).
ACTUAL_PERIOD_STATUS_SEQNO	No	INTEGER	No	The ID number of the corresponding period in the current year, as set up in EXO Business Config (links to PERIOD_STATUS.SEQNO).
LYACTUAL_PERIOD_STATUS_SEQNO	No	INTEGER	No	The ID number of the corresponding period last year, as set up in EXO Business Config (links to PERIOD_STATUS.SEQNO).
STARTDATE	No	DATETIME	No	The start date of the period.
ENDDATE	No	DATETIME	No	The end date of the period.
LEVEL_ACCGROUP	No	BIT	Yes	This field contains 1 if the budget applies at the Account Group level; 0 otherwise.
LEVEL_ACCGROUP2	No	BIT	Yes	This field contains 1 if the budget applies at the Alternate Account Group level; 0 otherwise.
LEVEL_ACCNO	No	BIT	Yes	This field contains 1 if the budget applies at the Debtor account level; 0 otherwise.
LEVEL_STOCKGROUP	No	BIT	Yes	This field contains 1 if the budget applies at the Stock Group level; 0 otherwise.
LEVEL_STOCKGROUP2	No	BIT	Yes	This field contains 1 if the budget applies at the Alternate Stock Group level; 0 otherwise.
LEVEL_STOCKCODE	No	BIT	Yes	This field contains 1 if the budget applies at the Stock item level; 0 otherwise.
LEVEL_BRANCH	No	BIT	Yes	This field contains 1 if the budget applies at the Branch level; 0 otherwise.
LEVEL_STAFF	No	BIT	Yes	This field contains 1 if the budget applies at the staff member level; 0 otherwise.
RECALC_LASTRUN	No	DATETIME	No	The date and time when the budget was last calculated.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
FK_CRM_BUDGET_CRM_BUDGET_HDR	Non Identifying	CRM_BUDGET_HDR	CRM_BUDGET	Zero Or More
FK_CRM_BUDGET_EXCLUDED_ACCGROUP2S_CRM_BUDGET	Non Identifying	CRM_BUDGET	CRM_BUDGET_EXCLUDED_ACCGROUP2S	Zero Or More
FK_CRM_BUDGET_EXCLUDED_ACCGROUPS_CRM_BUDGET	Non Identifying	CRM_BUDGET	CRM_BUDGET_EXCLUDED_ACCGROUPS	Zero Or More
FK_CRM_BUDGET_EXCLUDED_BRANCHES_CRM_BUDGET	Non Identifying	CRM_BUDGET	CRM_BUDGET_EXCLUDED_BRANCHES	Zero Or More
FK_CRM_BUDGET_EXCLUDED_DR_ACCS_CRM_BUDGET	Non Identifying	CRM_BUDGET	CRM_BUDGET_EXCLUDED_DR_ACCS	Zero Or More
FK_CRM_BUDGET_EXCLUDED_STAFF_CRM_BUDGET	Non Identifying	CRM_BUDGET	CRM_BUDGET_EXCLUDED_STAFF	Zero Or More
FK_CRM_BUDGET_EXCLUDED_STOCK_GROUP2S_CRM_BUDGET	Non Identifying	CRM_BUDGET	CRM_BUDGET_EXCLUDED_STOCK_GROUP2S	Zero Or More
FK_CRM_BUDGET_EXCLUDED_STOCK_GROUPS_CRM_BUDGET	Non Identifying	CRM_BUDGET	CRM_BUDGET_EXCLUDED_STOCK_GROUPS	Zero Or More
FK_CRM_BUDGET_EXCLUDED_STOCK_ITEMS_CRM_BUDGET	Non Identifying	CRM_BUDGET	CRM_BUDGET_EXCLUDED_STOCK_ITEMS	Zero Or More
FK_CRM_BUDGET_LINE_CRM_BUDGET	Non Identifying	CRM_BUDGET	CRM_BUDGET_LINE	Zero Or More
FK_CRM_BUDGET_MANREP_PERIOD	Non Identifying	MANREP_PERIOD	CRM_BUDGET	Zero Or More
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

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (LEVEL_STAFF)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (NAME)
	Not Null	Column Constraint	NOT NULL (HEADER_SEQNO)
	Not Null	Column Constraint	NOT NULL (MANREP_PERIOD_SEQNO)
	Not Null	Column Constraint	NOT NULL (LEVEL_ACCGROUP)
	Not Null	Column Constraint	NOT NULL (LEVEL_ACCGROUP2)
	Not Null	Column Constraint	NOT NULL (LEVEL_ACCNO)
	Not Null	Column Constraint	NOT NULL (LEVEL_STOCKGROUP)
	Not Null	Column Constraint	NOT NULL (LEVEL_STOCKGROUP2)
	Not Null	Column Constraint	NOT NULL (LEVEL_STOCKCODE)
	Not Null	Column Constraint	NOT NULL (LEVEL_BRANCH)
FK_CRM_BUDGET_CRM_BUDGET_HDR	Foreign Key	Table Constraint	FOREIGN KEY (HEADER_SEQNO) REFERENCES CRM_BUDGET_HDR(SEQNO)
FK_CRM_BUDGET_MANREP_PERIOD	Foreign Key	Table Constraint	FOREIGN KEY (MANREP_PERIOD_SEQNO) REFERENCES MANREP_PERIOD(PERIOD_SEQNO)
FK_CRM_BUDGET_PERIOD_STATUS	Foreign Key	Table Constraint	FOREIGN KEY (ACTUAL_PERIOD_STATUS_SEQNO) REFERENCES PERIOD_STATUS(SEQNO)
FK_CRM_BUDGET_PERIOD_STATUS1	Foreign Key	Table Constraint	FOREIGN KEY (LYACTUAL_PERIOD_STATUS_SEQNO) REFERENCES PERIOD_STATUS(SEQNO)
PK_CRM_BUDGET	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)

Entity: CRM_BUDGET_EXCLUDED_ACCGROUP2S

Comment	This table records any Alternate Account Groups that are excluded from Sales Team Budgets.
Primary key columns

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
BUDGET_SEQNO	No	INTEGER	Yes	The ID number of the budget (Links to CRM_BUDGET.SEQNO).
ACCGROUP	No	INTEGER	Yes	The Alternate Account Group ID to be excluded when generating budgets.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
FK_CRM_BUDGET_EXCLUDED_ACCGROUP2S_CRM_BUDGET	Non Identifying	CRM_BUDGET	CRM_BUDGET_EXCLUDED_ACCGROUP2S	Zero Or More
FK_CRM_BUDGET_EXCLUDED_ACCGROUP2S_DR_ACCGROUP2S	Non Identifying	DR_ACCGROUP2S	CRM_BUDGET_EXCLUDED_ACCGROUP2S	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (ACCGROUP)
	Not Null	Column Constraint	NOT NULL (BUDGET_SEQNO)
FK_CRM_BUDGET_EXCLUDED_ACCGROUP2S_CRM_BUDGET	Foreign Key	Table Constraint	FOREIGN KEY (BUDGET_SEQNO) REFERENCES CRM_BUDGET(SEQNO)
FK_CRM_BUDGET_EXCLUDED_ACCGROUP2S_DR_ACCGROUP2S	Foreign Key	Table Constraint	FOREIGN KEY (ACCGROUP) REFERENCES DR_ACCGROUP2S(ACCGROUP)

Entity: CRM_BUDGET_EXCLUDED_ACCGROUPS

Comment	This table records any Account Groups that are excluded from Sales Team Budgets.
Primary key columns

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
BUDGET_SEQNO	No	INTEGER	Yes	The ID number of the budget (Links to CRM_BUDGET.SEQNO).
ACCGROUP	No	INTEGER	Yes	The Account Group ID to be excluded when generating budgets.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
FK_CRM_BUDGET_EXCLUDED_ACCGROUPS_CRM_BUDGET	Non Identifying	CRM_BUDGET	CRM_BUDGET_EXCLUDED_ACCGROUPS	Zero Or More
FK_CRM_BUDGET_EXCLUDED_ACCGROUPS_DR_ACCGROUPS	Non Identifying	DR_ACCGROUPS	CRM_BUDGET_EXCLUDED_ACCGROUPS	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (ACCGROUP)
	Not Null	Column Constraint	NOT NULL (BUDGET_SEQNO)
FK_CRM_BUDGET_EXCLUDED_ACCGROUPS_CRM_BUDGET	Foreign Key	Table Constraint	FOREIGN KEY (BUDGET_SEQNO) REFERENCES CRM_BUDGET(SEQNO)
FK_CRM_BUDGET_EXCLUDED_ACCGROUPS_DR_ACCGROUPS	Foreign Key	Table Constraint	FOREIGN KEY (ACCGROUP) REFERENCES DR_ACCGROUPS(ACCGROUP)

Entity: CRM_BUDGET_EXCLUDED_BRANCHES

Comment	This table records any branches that are excluded from Sales Team Budgets.
Primary key columns

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
BUDGET_SEQNO	No	INTEGER	Yes	The ID number of the budget (Links to CRM_BUDGET.SEQNO).
BRANCHNO	No	INTEGER	Yes	The branch ID to be excluded when generating budgets.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
FK_CRM_BUDGET_EXCLUDED_BRANCHES_BRANCHES	Non Identifying	BRANCHES	CRM_BUDGET_EXCLUDED_BRANCHES	Zero Or More
FK_CRM_BUDGET_EXCLUDED_BRANCHES_CRM_BUDGET	Non Identifying	CRM_BUDGET	CRM_BUDGET_EXCLUDED_BRANCHES	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (BRANCHNO)
	Not Null	Column Constraint	NOT NULL (BUDGET_SEQNO)
FK_CRM_BUDGET_EXCLUDED_BRANCHES_BRANCHES	Foreign Key	Table Constraint	FOREIGN KEY (BRANCHNO) REFERENCES BRANCHES(BRANCHNO)
FK_CRM_BUDGET_EXCLUDED_BRANCHES_CRM_BUDGET	Foreign Key	Table Constraint	FOREIGN KEY (BUDGET_SEQNO) REFERENCES CRM_BUDGET(SEQNO)

Entity: CRM_BUDGET_EXCLUDED_DR_ACCS

Comment	This table records any Debtor accounts that are excluded from Sales Team Budgets.
Primary key columns

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
BUDGET_SEQNO	No	INTEGER	Yes	The ID number of the budget (Links to CRM_BUDGET.SEQNO).
ACCNO	No	INTEGER	Yes	The Debtor account ID to be excluded when generating budgets.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
FK_CRM_BUDGET_EXCLUDED_DR_ACCS_CRM_BUDGET	Non Identifying	CRM_BUDGET	CRM_BUDGET_EXCLUDED_DR_ACCS	Zero Or More
FK_CRM_BUDGET_EXCLUDED_DR_ACCS_DR_ACCS	Non Identifying	DR_ACCS	CRM_BUDGET_EXCLUDED_DR_ACCS	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (ACCNO)
	Not Null	Column Constraint	NOT NULL (BUDGET_SEQNO)
FK_CRM_BUDGET_EXCLUDED_DR_ACCS_CRM_BUDGET	Foreign Key	Table Constraint	FOREIGN KEY (BUDGET_SEQNO) REFERENCES CRM_BUDGET(SEQNO)
FK_CRM_BUDGET_EXCLUDED_DR_ACCS_DR_ACCS	Foreign Key	Table Constraint	FOREIGN KEY (ACCNO) REFERENCES DR_ACCS(ACCNO)

Entity: CRM_BUDGET_EXCLUDED_STAFF

Comment	This table records any staff members who are excluded from Sales Team Budgets.
Primary key columns

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
BUDGET_SEQNO	No	INTEGER	Yes	The ID number of the budget (Links to CRM_BUDGET.SEQNO).
STAFFNO	No	INTEGER	Yes	The staff member ID to be excluded when generating budgets.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
FK_CRM_BUDGET_EXCLUDED_STAFF_CRM_BUDGET	Non Identifying	CRM_BUDGET	CRM_BUDGET_EXCLUDED_STAFF	Zero Or More
FK_CRM_BUDGET_EXCLUDED_STAFF_STAFF	Non Identifying	STAFF	CRM_BUDGET_EXCLUDED_STAFF	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (STAFFNO)
	Not Null	Column Constraint	NOT NULL (BUDGET_SEQNO)
FK_CRM_BUDGET_EXCLUDED_STAFF_CRM_BUDGET	Foreign Key	Table Constraint	FOREIGN KEY (BUDGET_SEQNO) REFERENCES CRM_BUDGET(SEQNO)
FK_CRM_BUDGET_EXCLUDED_STAFF_STAFF	Foreign Key	Table Constraint	FOREIGN KEY (STAFFNO) REFERENCES STAFF(STAFFNO)

Entity: CRM_BUDGET_EXCLUDED_STOCK_GROUP2S

Comment	This table records any Stock Groups that are excluded from Sales Team Budgets.
Primary key columns

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
BUDGET_SEQNO	No	INTEGER	Yes	The ID number of the budget (Links to CRM_BUDGET.SEQNO).
GROUPNO	No	INTEGER	Yes	The Alternate Stock Group ID to be excluded when generating budgets.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
FK_CRM_BUDGET_EXCLUDED_STOCK_GROUP2S_CRM_BUDGET	Non Identifying	CRM_BUDGET	CRM_BUDGET_EXCLUDED_STOCK_GROUP2S	Zero Or More
FK_CRM_BUDGET_EXCLUDED_STOCK_GROUP2S_STOCK_GROUP2S	Non Identifying	STOCK_GROUP2S	CRM_BUDGET_EXCLUDED_STOCK_GROUP2S	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (GROUPNO)
	Not Null	Column Constraint	NOT NULL (BUDGET_SEQNO)
FK_CRM_BUDGET_EXCLUDED_STOCK_GROUP2S_CRM_BUDGET	Foreign Key	Table Constraint	FOREIGN KEY (BUDGET_SEQNO) REFERENCES CRM_BUDGET(SEQNO)
FK_CRM_BUDGET_EXCLUDED_STOCK_GROUP2S_STOCK_GROUP2S	Foreign Key	Table Constraint	FOREIGN KEY (GROUPNO) REFERENCES STOCK_GROUP2S(GROUPNO)

Entity: CRM_BUDGET_EXCLUDED_STOCK_GROUPS

Comment	This table records any Alternate Stock Groups that are excluded from Sales Team Budgets.
Primary key columns

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
BUDGET_SEQNO	No	INTEGER	Yes	The ID number of the budget (Links to CRM_BUDGET.SEQNO).
GROUPNO	No	INTEGER	Yes	The Stock Group ID to be excluded when generating budgets.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
FK_CRM_BUDGET_EXCLUDED_STOCK_GROUPS_CRM_BUDGET	Non Identifying	CRM_BUDGET	CRM_BUDGET_EXCLUDED_STOCK_GROUPS	Zero Or More
FK_CRM_BUDGET_EXCLUDED_STOCK_GROUPS_STOCK_GROUPS	Non Identifying	STOCK_GROUPS	CRM_BUDGET_EXCLUDED_STOCK_GROUPS	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (GROUPNO)
	Not Null	Column Constraint	NOT NULL (BUDGET_SEQNO)
FK_CRM_BUDGET_EXCLUDED_STOCK_GROUPS_CRM_BUDGET	Foreign Key	Table Constraint	FOREIGN KEY (BUDGET_SEQNO) REFERENCES CRM_BUDGET(SEQNO)
FK_CRM_BUDGET_EXCLUDED_STOCK_GROUPS_STOCK_GROUPS	Foreign Key	Table Constraint	FOREIGN KEY (GROUPNO) REFERENCES STOCK_GROUPS(GROUPNO)

Entity: CRM_BUDGET_EXCLUDED_STOCK_ITEMS

Comment	This table records any Stock items that are excluded from Sales Team Budgets.
Primary key columns

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
BUDGET_SEQNO	No	INTEGER	Yes	The ID number of the budget (Links to CRM_BUDGET.SEQNO).
STOCKCODE	No	VARCHAR(23)	Yes	The Stock item ID to be excluded when generating budgets.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
FK_CRM_BUDGET_EXCLUDED_STOCK_ITEMS_CRM_BUDGET	Non Identifying	CRM_BUDGET	CRM_BUDGET_EXCLUDED_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

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (STOCKCODE)
	Not Null	Column Constraint	NOT NULL (BUDGET_SEQNO)
FK_CRM_BUDGET_EXCLUDED_STOCK_ITEMS_CRM_BUDGET	Foreign Key	Table Constraint	FOREIGN KEY (BUDGET_SEQNO) REFERENCES CRM_BUDGET(SEQNO)
FK_CRM_BUDGET_EXCLUDED_STOCK_ITEMS_STOCK_ITEMS	Foreign Key	Table Constraint	FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE)

Entity: CRM_BUDGET_HDR

Comment	This table contains header information for Sales Team Budgets in EXO 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 budget record.
NAME	No	VARCHAR(1024)	Yes	The budget's name.
MANREP_PERIOD_SEQNO	No	INTEGER	Yes	The ID number of the Sales Year that the budget belongs to (Sales Years are set up using the EXO Business Analytics module).
ISPRIMARY	No	BIT	Yes	This field is set to 1 if the budget is a primary budget; 0 otherwise.
ISACTIVE	No	BIT	Yes	This field is set to 1 if the budget is active or 0 if it is inactive.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
FK_CRM_BUDGET_CRM_BUDGET_HDR	Non Identifying	CRM_BUDGET_HDR	CRM_BUDGET	Zero Or More
FK_CRM_BUDGET_HDR_MANREP_PERIOD	Non Identifying	MANREP_PERIOD	CRM_BUDGET_HDR	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (ISACTIVE)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (NAME)
	Not Null	Column Constraint	NOT NULL (MANREP_PERIOD_SEQNO)
	Not Null	Column Constraint	NOT NULL (ISPRIMARY)
DF_CRM_BUDGET_HDR_ISPRIMARY	Default	Column Constraint	ISPRIMARY DEFAULT 0
DF__CRM_BUDGE__ISACT__0175B502	Default	Column Constraint	ISACTIVE DEFAULT 0
FK_CRM_BUDGET_HDR_MANREP_PERIOD	Foreign Key	Table Constraint	FOREIGN KEY (MANREP_PERIOD_SEQNO) REFERENCES MANREP_PERIOD(PERIOD_SEQNO)
PK_CRM_BUDGET_HDR	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)

Entity: CRM_BUDGET_LINE

Comment	This table contains line information for Sales Team Budgets in EXO 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 budget line record.
BUDGET_SEQNO	No	INTEGER	Yes	The ID number of the budget that the line record belongs to.
VALUE	No	DECIMAL(19, 4)	Yes	The budget value.
LYACTUAL	No	DECIMAL(19, 4)	No	Last year actuals for the budget line.
ACCGROUP	No	INTEGER	No	The ID number of the Account Group that the line applies to (if there is one).
ACCGROUP_EXPANSION	No	BIT	No
ACCGROUP2	No	INTEGER	No	The ID number of the Alternate Account Group that the line applies to (if there is one).
ACCGROUP2_EXPANSION	No	BIT	No
ACCNO	No	INTEGER	No	The ID number of the Debtor account that the line applies to (if there is one).
ACCNO_EXPANSION	No	BIT	No
STOCK_GROUPNO	No	INTEGER	No	The ID number of the Stock Group that the line applies to (if there is one).
STOCK_GROUPNO_EXPANSION	No	BIT	No
STOCK_GROUPNO2	No	INTEGER	No	The ID number of the Alternate Stock Group that the line applies to (if there is one).
STOCK_GROUPNO2_EXPANSION	No	BIT	No
STOCKCODE	No	VARCHAR(23)	No	The ID number of the Stock item that the line applies to (if there is one).
STOCKCODE_EXPANSION	No	BIT	No
BRANCHNO	No	INTEGER	No	The ID number of the branch that the line applies to (if there is one).
BRANCHNO_EXPANSION	No	BIT	No
SALESNO	No	INTEGER	No	The ID number of the staff member that the line applies to (if there is one).
SALESNO_EXPANSION	No	BIT	No
ACTUAL	No	DECIMAL(19, 4)	No	Actuals for the budget line.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
FK_CRM_BUDGET_LINE_BRANCHES	Non Identifying	BRANCHES	CRM_BUDGET_LINE	Zero Or More
FK_CRM_BUDGET_LINE_CRM_BUDGET	Non Identifying	CRM_BUDGET	CRM_BUDGET_LINE	Zero Or More
FK_CRM_BUDGET_LINE_DR_ACCGROUP2S	Non Identifying	DR_ACCGROUP2S	CRM_BUDGET_LINE	Zero Or More
FK_CRM_BUDGET_LINE_DR_ACCGROUPS	Non Identifying	DR_ACCGROUPS	CRM_BUDGET_LINE	Zero Or More
FK_CRM_BUDGET_LINE_DR_ACCS	Non Identifying	DR_ACCS	CRM_BUDGET_LINE	Zero Or More
FK_CRM_BUDGET_LINE_STAFF	Non Identifying	STAFF	CRM_BUDGET_LINE	Zero Or More
FK_CRM_BUDGET_LINE_STOCK_GROUP2S	Non Identifying	STOCK_GROUP2S	CRM_BUDGET_LINE	Zero Or More
FK_CRM_BUDGET_LINE_STOCK_GROUPS	Non Identifying	STOCK_GROUPS	CRM_BUDGET_LINE	Zero Or More
FK_CRM_BUDGET_LINE_STOCK_ITEMS	Non Identifying	STOCK_ITEMS	CRM_BUDGET_LINE	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (VALUE)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (BUDGET_SEQNO)
FK_CRM_BUDGET_LINE_BRANCHES	Foreign Key	Table Constraint	FOREIGN KEY (BRANCHNO) REFERENCES BRANCHES(BRANCHNO)
FK_CRM_BUDGET_LINE_CRM_BUDGET	Foreign Key	Table Constraint	FOREIGN KEY (BUDGET_SEQNO) REFERENCES CRM_BUDGET(SEQNO)
FK_CRM_BUDGET_LINE_DR_ACCGROUP2S	Foreign Key	Table Constraint	FOREIGN KEY (ACCGROUP2) REFERENCES DR_ACCGROUP2S(ACCGROUP)
FK_CRM_BUDGET_LINE_DR_ACCGROUPS	Foreign Key	Table Constraint	FOREIGN KEY (ACCGROUP) REFERENCES DR_ACCGROUPS(ACCGROUP)
FK_CRM_BUDGET_LINE_DR_ACCS	Foreign Key	Table Constraint	FOREIGN KEY (ACCNO) REFERENCES DR_ACCS(ACCNO)
FK_CRM_BUDGET_LINE_STAFF	Foreign Key	Table Constraint	FOREIGN KEY (SALESNO) REFERENCES STAFF(STAFFNO)
FK_CRM_BUDGET_LINE_STOCK_GROUP2S	Foreign Key	Table Constraint	FOREIGN KEY (STOCK_GROUPNO2) REFERENCES STOCK_GROUP2S(GROUPNO)
FK_CRM_BUDGET_LINE_STOCK_GROUPS	Foreign Key	Table Constraint	FOREIGN KEY (STOCK_GROUPNO) REFERENCES STOCK_GROUPS(GROUPNO)
FK_CRM_BUDGET_LINE_STOCK_ITEMS	Foreign Key	Table Constraint	FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE)
PK_CRM_BUDGET_LINE	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)

Entity: MANREP_PERIOD

Comment
Primary key columns	PERIOD_SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
PERIOD_SEQNO	Yes	INTEGER	Yes
STARTDATE	No	DATETIME	No
ENDDATE	No	DATETIME	No
PERIOD_NAME	No	VARCHAR(20)	No
PARENT_PERIOD_SEQNO	No	INTEGER	No
CALENDAR	No	CHAR(1)	No

Relationships:

Name	Relationship type	Parent	Child	Cardinality
FK_CRM_BUDGET_HDR_MANREP_PERIOD	Non Identifying	MANREP_PERIOD	CRM_BUDGET_HDR	Zero Or More
FK_CRM_BUDGET_MANREP_PERIOD	Non Identifying	MANREP_PERIOD	CRM_BUDGET	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (PERIOD_SEQNO)
DF__MANREP_PE__CALEN__61915EA7	Default	Column Constraint	CALENDAR DEFAULT Y
DF__MANREP_PE__ENDDA__5FA91635	Default	Column Constraint	ENDDATE DEFAULT getdate()
DF__MANREP_PE__PAREN__609D3A6E	Default	Column Constraint	PARENT_PERIOD_SEQNO DEFAULT 0
DF__MANREP_PE__START__5EB4F1FC	Default	Column Constraint	STARTDATE DEFAULT getdate()
PK__MANREP_PERIOD__5DC0CDC3	Primary Key	Table Constraint	PRIMARY KEY (PERIOD_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: OPPORTUNITY_HIST

Comment	This table contains details of History Notes on Opportunities.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the History Note.
OPPORTUNITY_SEQNO	No	INTEGER	Yes	The ID number of the Opportunity that the History Note is attached to.
SALESNO	No	INTEGER	No	The ID number of the staff member who entered the History Note.
TRANSDATE	No	DATETIME	No	The date and time of the History Notes.
SUBJECT	No	VARCHAR(80)	No	The subject line of the History Note.
NOTE	No	VARCHAR(4096)	No	The body of the History Note.
OUTLOOK_LINK	No	VARCHAR(40)	No	If the History Note was emailed, this field contains a link to the email in MS Outlook.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
OPPORTUNITY_OPPORTUNITY_HIST	Non Identifying	OPPORTUNITY	OPPORTUNITY_HIST	Zero Or More
STAFF_OPPORTUNITY_HIST	Non Identifying	STAFF	OPPORTUNITY_HIST	Zero Or More

Indexes:

Name	Index columns	Index type
OPPORTUNITY_HIST_INDEX	OPPORTUNITY_SEQNO ASC

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (OPPORTUNITY_SEQNO)
	Not Null	Column Constraint	NOT NULL (SEQNO)
DF__OPPORTUNI__TRANS__6148113B	Default	Column Constraint	TRANSDATE DEFAULT getdate()
OPPORTUNITY_OPPORTUNITY_HIST	Foreign Key	Table Constraint	FOREIGN KEY (OPPORTUNITY_SEQNO) REFERENCES OPPORTUNITY(SEQNO)
PK__OPPORTUNITY_HIST__6053ED02	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)
STAFF_OPPORTUNITY_HIST	Foreign Key	Table Constraint	FOREIGN KEY (SALESNO) REFERENCES STAFF(STAFFNO)

Entity: OPPORTUNITY_LEAD

Comment	This table records all Opportunity Lead Sources.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the Lead Source.
DESCRIPTION	No	VARCHAR(60)	No	The Lead Source's name/description.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
OPPORTUNITY_LEAD_OPPORTUNITY	Non Identifying	OPPORTUNITY_LEAD	OPPORTUNITY	Zero Or More

Constraints:

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

Entity: OPPORTUNITY_LINES

Comment
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes
HDR_SEQNO	No	INTEGER	Yes
STOCKCODE	No	VARCHAR(23)	Yes
DESCRIPTION	No	VARCHAR(40)	Yes
QUANTITY	No	FLOAT(53)	No
UNITPRICE	No	FLOAT(53)	No
DISCOUNT	No	FLOAT(53)	No
TAXRATE_NO	No	INTEGER	No
TAXRATE	No	FLOAT(53)	No
NARRATIVE_SEQNO	No	INTEGER	No

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (DESCRIPTION)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (HDR_SEQNO)
	Not Null	Column Constraint	NOT NULL (STOCKCODE)
PK__OPPORTUNITY_LINE__45D500F0	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)

Entity: OPPORTUNITY_QUOTE

Comment	This table contains details of the quote lines attached to Opportunities (EXO CRM).
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique identifier for the quote line.
QUOTE_QTY	No	FLOAT(53)	No	The quantity on the quote line.
QUOTE_UNITPR	No	FLOAT(53)	No	The sell price for the Stock item on the quote line.
ACTUAL_UNITCOST	No	FLOAT(53)	No	The unit cost for the Stock item on the quote line.
TRANSDATE	No	DATETIME	No	The date and time of the transaction.
EXCHRATE	No	FLOAT(53)	No	The currency exchange rate used for the quote.
DISCOUNT	No	FLOAT(53)	No	The percentage discount on the quote line.
UNITPRICE_INCTAX	No	FLOAT(53)	No	The sell price of the Stock item on the quote line, including tax.
HDR_SEQNO	No	INTEGER	Yes	The ID number of the Opportunity that this line relates to (OPPORTUNITY.SEQNO).
SECTION	No	INTEGER	No
STOCKCODE	No	VARCHAR(23)	No	The ID code of the stock item on this line.
DESCRIPTION	No	VARCHAR(60)	No	The description of the stock item on this line.
SHOW_ON_INVOICE	No	CHAR(1)	No	Set to 'Y' if this line should appear on invoices.
COST_CENTRE	No	INTEGER	No	The Cost Type on the quote line.
LINE_STATUS	No	VARCHAR(30)	No
COST_CENTRE2	No	INTEGER	No	The Cost Group on the quote line.
NARRATIVE	No	VARCHAR(1)	No	Set to 'Y' if there is a narrative on this line.
TAXNO	No	INTEGER	No	The ID number of the tax rate used on this line.
BRANCHNO	No	INTEGER	No	The ID number of the branch used on this line.
SUBCODE	No	INTEGER	No	The GL sub-account on this quote line.
ANALYSIS	No	INTEGER	No
CURRENCYNO	No	INTEGER	No	The ID number of the currency that the Sales Order is in.
ALINENO	No	INTEGER	No	A unique line number.
GLCODE	No	INTEGER	No	The GL account on this quote line.
NARRATIVE_SEQNO	No	INTEGER	No	The ID number of the narrative on this line.
DIM_LENGTH	No	FLOAT(53)	Yes	The Length value specified for the quote line.
DIM_WIDTH	No	FLOAT(53)	Yes	The Width value specified for the quote line.
DIM_DEPTH	No	FLOAT(53)	Yes	The Depth value specified for the quote line.
TOTAL_QUANTITY	No	AS	No	The total quantity on the quote line.
PRICE_OVERRIDDEN	No	CHAR(1)	Yes	Set to 'Y' if the price has been overridden.
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
BOMPRICING	No	CHAR(1)	No	How the Bill of Materials is priced. Will be one of: C = pricing by Component T = Pricing by total
SHOWLINE	No	CHAR(1)	No	Set to 'Y' if the line should be printed on Clarity reports.
LINKEDSTATUS	No	CHAR(1)	No	The linked status of the Stock line. Will be one of: S = Stocked L = Lookup N = Not applicable
LISTPRICE	No	FLOAT(53)	No	The original sell price of the Stock item on this line.
LINETYPE	No	INTEGER	Yes	The type of the line. Will be one of: 0 = Not part of a BOM. 1 = Header line of a BOM. 2 = Line of a BOM.
KITSEQNO	No	INTEGER	Yes	A Bill of Materials SEQNO used to group BOM lines together.
KITCODE	No	VARCHAR(23)	No	The Bill of Materials code, if the line is part of a BOM.
LINKED_STOCKCODE	No	VARCHAR(23)	No	The linked Stock code, if applicable.
LINKED_QTY	No	FLOAT(53)	No	The linked quantity, if applicable.
HIDDEN_COST	No	FLOAT(53)	Yes	This field applies to Bills of Materials. For kits that use pricing by components, this field stores header-level sell costs. For builds that use individual stocklines, this fields stores the lines' real costs.
HIDDEN_SELL	No	FLOAT(53)	Yes	This field applies to Bills of Materials. For kits that use pricing by components, this field stores header-level sell prices. For builds that use individual stocklines, this fields stores the lines' real prices.
SUPPLIERNO	No	INTEGER	No	The ID number of the supplier for the Stock item on this quote line.
FROMLOC	No	INTEGER	No	The ID number of the location that the Stock item on this line is being supplied from.
LINETOTAL	No	AS	No	A computed field showing the line total.
NUNITPR	No	AS	No	A computed field showing the line total including GST.
OPTION_NO	No	INTEGER	Yes	The option number of the Quote Option on this line (OPPORTUNITY_QUOTE_OPTION.OPTION_NO).
SPREADVALUE	No	CHAR(1)	Yes	Set to 'Y' if totals can be spread.
TAXRATE	No	FLOAT(53)	Yes	The percentage tax rate on the quote line.
LINETOTAL_TAX	No	FLOAT(53)	Yes	The total amount of tax on the quote line.
LINE_TAX	No	FLOAT(53)	Yes	The amount of tax on the quote line.
HIDDEN_LINETOTAL	No	FLOAT(53)	Yes
LINETOTAL_INCTAX	No	AS	No	The line total including tax.
OPPLINEID	No	INTEGER	No	A unique ID number for the line.
LINETAX_OVERRIDE	No	FLOAT(53)	Yes	If the tax on the line was manually overridden, this field contains the new value.
LINETAX_OVERRIDDEN	No	CHAR(1)	Yes	Set to 'Y' if the tax on the line was overridden.
OPTION_NO_SEQNO	No	INTEGER	No	The ID number of the Quote Option on this line (OPPORTUNITY_QUOTE_OPTION.SEQNO).

Relationships:

Name	Relationship type	Parent	Child	Cardinality
CURRENCIES_OPPORTUNITY_QUOTE	Non Identifying	CURRENCIES	OPPORTUNITY_QUOTE	Zero Or More
GLACCS_OPPORTUNITY_QUOTE	Non Identifying	GLACCS	OPPORTUNITY_QUOTE	Zero Or More
NARRATIVES_OPPORTUNITY_QUOTE	Non Identifying	NARRATIVES	OPPORTUNITY_QUOTE	Zero Or More
OPPORTUNITY_OPPORTUNITY_QUOTE	Non Identifying	OPPORTUNITY	OPPORTUNITY_QUOTE	Zero Or More
OPPORTUNITY_QUOTE_OPTIONS_OPPORTUNITY_QUOTE	Non Identifying	OPPORTUNITY_QUOTE_OPTIONS	OPPORTUNITY_QUOTE	Zero Or More
OPPORTUNITY_QUOTE_OPTIONS_OPPORTUNITY_QUOTE1	Non Identifying	OPPORTUNITY_QUOTE_OPTIONS	OPPORTUNITY_QUOTE	Zero Or More
STOCK_ITEMS_OPPORTUNITY_QUOTE	Non Identifying	STOCK_ITEMS	OPPORTUNITY_QUOTE	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 (LINETAX_OVERRIDDEN)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (LINETAX_OVERRIDE)
	Not Null	Column Constraint	NOT NULL (HIDDEN_LINETOTAL)
	Not Null	Column Constraint	NOT NULL (LINE_TAX)
	Not Null	Column Constraint	NOT NULL (LINETOTAL_TAX)
	Not Null	Column Constraint	NOT NULL (HDR_SEQNO)
	Not Null	Column Constraint	NOT NULL (TAXRATE)
	Not Null	Column Constraint	NOT NULL (SPREADVALUE)
	Not Null	Column Constraint	NOT NULL (OPTION_NO)
	Not Null	Column Constraint	NOT NULL (HIDDEN_SELL)
	Not Null	Column Constraint	NOT NULL (HIDDEN_COST)
	Not Null	Column Constraint	NOT NULL (KITSEQNO)
	Not Null	Column Constraint	NOT NULL (LINETYPE)
	Not Null	Column Constraint	NOT NULL (PRICE_OVERRIDDEN)
	Not Null	Column Constraint	NOT NULL (DIM_LENGTH)
	Not Null	Column Constraint	NOT NULL (DIM_DEPTH)
	Not Null	Column Constraint	NOT NULL (DIM_WIDTH)
CURRENCIES_OPPORTUNITY_QUOTE	Foreign Key	Table Constraint	FOREIGN KEY (CURRENCYNO) REFERENCES CURRENCIES(CURRENCYNO)
DF_OPPORTUNITY_QUOTE_ALINENO	Default	Column Constraint	ALINENO DEFAULT 0
DF_OPPORTUNITY_QUOTE_ANALYSIS	Default	Column Constraint	ANALYSIS DEFAULT 0
DF_OPPORTUNITY_QUOTE_BOMPRICING	Default	Column Constraint	BOMPRICING DEFAULT N
DF_OPPORTUNITY_QUOTE_BOMTYPE	Default	Column Constraint	BOMTYPE DEFAULT N
DF_OPPORTUNITY_QUOTE_BRANCHNO	Default	Column Constraint	BRANCHNO DEFAULT 0
DF_OPPORTUNITY_QUOTE_CURRENCYNO	Default	Column Constraint	CURRENCYNO DEFAULT 0
DF_OPPORTUNITY_QUOTE_DIM_DEPTH	Default	Column Constraint	DIM_DEPTH DEFAULT 1
DF_OPPORTUNITY_QUOTE_DIM_LENGTH	Default	Column Constraint	DIM_LENGTH DEFAULT 1
DF_OPPORTUNITY_QUOTE_DIM_WIDTH	Default	Column Constraint	DIM_WIDTH DEFAULT 1
DF_OPPORTUNITY_QUOTE_DISCOUNT	Default	Column Constraint	DISCOUNT DEFAULT 0
DF_OPPORTUNITY_QUOTE_EXCHRATE	Default	Column Constraint	EXCHRATE DEFAULT 0
DF_OPPORTUNITY_QUOTE_FROMLOC	Default	Column Constraint	FROMLOC DEFAULT 0
DF_OPPORTUNITY_QUOTE_GLCODE	Default	Column Constraint	GLCODE DEFAULT 0
DF_OPPORTUNITY_QUOTE_HIDDEN_COST	Default	Column Constraint	HIDDEN_COST DEFAULT 0
DF_OPPORTUNITY_QUOTE_HIDDEN_LINETOTAL	Default	Column Constraint	HIDDEN_LINETOTAL DEFAULT 0
DF_OPPORTUNITY_QUOTE_HIDDEN_SELL	Default	Column Constraint	HIDDEN_SELL DEFAULT 0
DF_OPPORTUNITY_QUOTE_KITSEQNO	Default	Column Constraint	KITSEQNO DEFAULT -1
DF_OPPORTUNITY_QUOTE_LINETOTAL_TAX	Default	Column Constraint	LINETOTAL_TAX DEFAULT 0
DF_OPPORTUNITY_QUOTE_LINETYPE	Default	Column Constraint	LINETYPE DEFAULT -1
DF_OPPORTUNITY_QUOTE_LINE_TAX	Default	Column Constraint	LINE_TAX DEFAULT 0
DF_OPPORTUNITY_QUOTE_LINKEDSTATUS	Default	Column Constraint	LINKEDSTATUS DEFAULT N
DF_OPPORTUNITY_QUOTE_LINKED_QTY	Default	Column Constraint	LINKED_QTY DEFAULT 0
DF_OPPORTUNITY_QUOTE_NARRATIVE	Default	Column Constraint	NARRATIVE DEFAULT N
DF_OPPORTUNITY_QUOTE_OPTION_NO	Default	Column Constraint	OPTION_NO DEFAULT 0
DF_OPPORTUNITY_QUOTE_PRICE_OVERRIDDEN	Default	Column Constraint	PRICE_OVERRIDDEN DEFAULT N
DF_OPPORTUNITY_QUOTE_SHOWLINE	Default	Column Constraint	SHOWLINE DEFAULT Y
DF_OPPORTUNITY_QUOTE_SPREADVALUE	Default	Column Constraint	SPREADVALUE DEFAULT Y
DF_OPPORTUNITY_QUOTE_SUBCODE	Default	Column Constraint	SUBCODE DEFAULT 0
DF_OPPORTUNITY_QUOTE_SUPPLIERNO	Default	Column Constraint	SUPPLIERNO DEFAULT 0
DF_OPPORTUNITY_QUOTE_TAXNO	Default	Column Constraint	TAXNO DEFAULT 0
DF_OPPORTUNITY_QUOTE_TAXRATE	Default	Column Constraint	TAXRATE DEFAULT 0
DF_OPPORTUNITY_QUOTE_TRANSDATE	Default	Column Constraint	TRANSDATE DEFAULT getdate()
DF_OPPORTUNITY_QUOTE_UNITPRICE_INCTAX	Default	Column Constraint	UNITPRICE_INCTAX DEFAULT 0
DF__OPPORTUNI__LINET__39BA0E25	Default	Column Constraint	LINETAX_OVERRIDE DEFAULT 0
DF__OPPORTUNI__LINET__3AAE325E	Default	Column Constraint	LINETAX_OVERRIDDEN DEFAULT N
DF__OPPORTUNI__OPTIO__799F8910	Default	Column Constraint	OPTION_NO_SEQNO DEFAULT -1
GLACCS_OPPORTUNITY_QUOTE	Foreign Key	Table Constraint	FOREIGN KEY (GLCODE) REFERENCES GLACCS(ACCNO)
NARRATIVES_OPPORTUNITY_QUOTE	Foreign Key	Table Constraint	FOREIGN KEY (NARRATIVE_SEQNO) REFERENCES NARRATIVES(SEQNO)
OPPORTUNITY_OPPORTUNITY_QUOTE	Foreign Key	Table Constraint	FOREIGN KEY (HDR_SEQNO) REFERENCES OPPORTUNITY(SEQNO)
OPPORTUNITY_QUOTE_OPTIONS_OPPORTUNITY_QUOTE	Foreign Key	Table Constraint	FOREIGN KEY (OPTION_NO_SEQNO) REFERENCES OPPORTUNITY_QUOTE_OPTIONS(SEQNO)
OPPORTUNITY_QUOTE_OPTIONS_OPPORTUNITY_QUOTE1	Foreign Key	Table Constraint	FOREIGN KEY (OPTION_NO) REFERENCES OPPORTUNITY_QUOTE_OPTIONS(OPTION_NO)
PK_OPPORTUNITY_QUOTE	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)
STOCK_ITEMS_OPPORTUNITY_QUOTE	Foreign Key	Table Constraint	FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE)
TAX_RATES_OPPORTUNITY_QUOTE	Foreign Key	Table Constraint	FOREIGN KEY (TAXNO) REFERENCES TAX_RATES(SEQNO)

Entity: OPPORTUNITY_QUOTE_OPTIONS

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

Attributes:

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

Relationships:

Name	Relationship type	Parent	Child	Cardinality
OPPORTUNITY_OPPORTUNITY_QUOTE_OPTIONS	Non Identifying	OPPORTUNITY	OPPORTUNITY_QUOTE_OPTIONS	Zero Or More
OPPORTUNITY_QUOTE_OPTIONS_OPPORTUNITY_QUOTE	Non Identifying	OPPORTUNITY_QUOTE_OPTIONS	OPPORTUNITY_QUOTE	Zero Or More
OPPORTUNITY_QUOTE_OPTIONS_OPPORTUNITY_QUOTE1	Non Identifying	OPPORTUNITY_QUOTE_OPTIONS	OPPORTUNITY_QUOTE	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (OPTION_SELECTED)
	Not Null	Column Constraint	NOT NULL (SEQNO)
DF_OPPORTUNITY_QUOTE_OPTIONS_HDR_SEQNO	Default	Column Constraint	HDR_SEQNO DEFAULT 0
DF_OPPORTUNITY_QUOTE_OPTIONS_OPTION_NO	Default	Column Constraint	OPTION_NO DEFAULT 0
DF_OPPORTUNITY_QUOTE_OPTIONS_OPTION_SELECTED	Default	Column Constraint	OPTION_SELECTED DEFAULT N
OPPORTUNITY_OPPORTUNITY_QUOTE_OPTIONS	Foreign Key	Table Constraint	FOREIGN KEY (HDR_SEQNO) REFERENCES OPPORTUNITY(SEQNO)
PK_OPPORTUNITY_QUOTE_OPTIONS	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)

Entity: OPPORTUNITY_STAGE

Comment	This table records all Opportunity Stages.
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 Stage.
DESCRIPTION	No	VARCHAR(60)	No	A description of the stage.
DEF_PROBABILITY	No	INTEGER	No	The default probability percentage for the stage.
STATUSKEY	No	CHAR(1)	No	A unique, one-character short code for the stage.
ADMIN_STAT	No	CHAR(1)	Yes	If this field contains 'Y', the stage prevents entry of new transactions.
LOCK_JOB	No	CHAR(1)	Yes	If this field contains 'Y', the stage makes the Opportunity read-only.
ISARCHIVED	No	CHAR(1)	Yes	If this field contains 'Y', the stage causes the Opportunity to be archived.
ISCOMPLETE	No	CHAR(1)	Yes	If this field contains 'Y', the stage causes the Opportunity to be marked as Complete.
ISACTIVE	No	CHAR(1)	Yes	If this field contains 'Y', the stage causes the Opportunity to be marked as Active.
ISINVOICEREADY	No	CHAR(1)	Yes	If this field contains 'Y', the stage causes the Opportunity to be marked as Ready for Invoice.
ISLOCKQUOTE	No	CHAR(1)	Yes	If this field contains 'Y', the stage causes quotations on the Opportunity to be read-only.
WORKFLOW_CONSTRAINED	No	CHAR(1)	Yes	If this field contains 'Y', workflow constraints (defined in the OPPORTUNITY_STAGE_CONSTRAINT table) apply to the stage.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
OPPORTUNITY_STAGE_OPPORTUNITY	Non Identifying	OPPORTUNITY_STAGE	OPPORTUNITY	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (WORKFLOW_CONSTRAINED)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (ADMIN_STAT)
	Not Null	Column Constraint	NOT NULL (ISLOCKQUOTE)
	Not Null	Column Constraint	NOT NULL (LOCK_JOB)
	Not Null	Column Constraint	NOT NULL (ISINVOICEREADY)
	Not Null	Column Constraint	NOT NULL (ISARCHIVED)
	Not Null	Column Constraint	NOT NULL (ISACTIVE)
	Not Null	Column Constraint	NOT NULL (ISCOMPLETE)
DF__OPPORTUNI__ADMIN__34A0534D	Default	Column Constraint	ADMIN_STAT DEFAULT N
DF__OPPORTUNI__ISACT__3870E431	Default	Column Constraint	ISACTIVE DEFAULT N
DF__OPPORTUNI__ISARC__36889BBF	Default	Column Constraint	ISARCHIVED DEFAULT N
DF__OPPORTUNI__ISCOM__377CBFF8	Default	Column Constraint	ISCOMPLETE DEFAULT N
DF__OPPORTUNI__ISINV__3965086A	Default	Column Constraint	ISINVOICEREADY DEFAULT N
DF__OPPORTUNI__ISLOC__3A592CA3	Default	Column Constraint	ISLOCKQUOTE DEFAULT N
DF__OPPORTUNI__LOCK___35947786	Default	Column Constraint	LOCK_JOB DEFAULT N
DF__OPPORTUNI__WORKF__3B4D50DC	Default	Column Constraint	WORKFLOW_CONSTRAINED DEFAULT N
PK__OPPORTUNITY_STAG__5C835C1E	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)

Entity: OPPORTUNITY_STAGE_CONSTRAINT

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

Attributes:

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

Constraints:

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

Entity: OPPORTUNITY_TYPE

Comment	This table records all Opportunity Types.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the Opportunity Type.
DESCRIPTION	No	VARCHAR(60)	No	The name/description of the Opportunity Type.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
OPPORTUNITY_TYPE_OPPORTUNITY	Non Identifying	OPPORTUNITY_TYPE	OPPORTUNITY	Zero Or More

Constraints:

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

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: 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: TASKS

Comment	This table contains details of all activities (tasks and appointments) in the EXO Business 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 activity.
PARENTSEQNO	No	INTEGER	No	Identifies the activity that is the ancestor in a chain of recurrences.
EVENTTYPE	No	INTEGER	No	A number indicating the type of event, where 0 is for a normal activity and all other numbers are used for recurring events.
START_DATETIME	No	DATETIME	No	The date and time when the activity starts.
END_DATETIME	No	DATETIME	No	The date and time when the activity ends.
OPTIONS	No	INTEGER	No	2 = All day event 3 = Not an all day event 6 = Not an all day event with reminder 7 = All day event with reminder
SUBJECT	No	VARCHAR(60)	No	The subject of the activity.
COMPANYID	No	VARCHAR(50)	No	The ID number of the company the activity relates to. Will be the ACCNO preceded by a letter: D = Debtor account C = Creditor account P = Prospect (Non Account)
CONTACTSEQNO	No	INTEGER	No	The ID number of the contact the activity is linked to.
OPPORTUNITYSEQNO	No	INTEGER	No	The ID number of the Opportunity the activity is linked to.
RECURRENCEINDEX	No	INTEGER	No	Specifies which one of the activity's recurrences this is.
RECURRENCEINFO	No	IMAGE	No	Contains information about the recurrences of the activity.
ASSIGNED_TO	No	INTEGER	No	The ID number of the staff member who the activity has been assigned to.
ASSIGNED_BY	No	INTEGER	No	The ID number of the staff member who assigned the activity.
REMINDERDATE	No	DATETIME	No	The date and time when a reminder for the activity should be displayed.
REMINDERMINUTES	No	INTEGER	No	When the reminder should be displayed, specified as the number of minutes before the start of the activity.
COMPLETED	No	CHAR(1)	Yes	This field contains "Y" if the activity has been completed.
COMPLETED_DATETIME	No	DATETIME	No	The date and time when the activity was completed.
STATE	No	INTEGER	No	The availability status to be displayed while the activity is active. Will be one of: 0 = Free 1 = Tentative 2 = Busy 3 = Out of office
TYPE	No	INTEGER	No	The ID number of the activity's type (links to TASK_TYPES).
STATUS	No	INTEGER	No	The ID number of the activity's status (links to TASK_STATUSES).
PRIORITY	No	VARCHAR(20)	No	The priority given to the activity. Can be "Low", "Normal" or "High".
LABELCOLOR	No	INTEGER	No	The colour value for the colour used to highlight the activity on calendars.
ACTUALSTART	No	DATETIME	No	The actual datetime stamp when the activity was started. For recurring events it contains the start date of the first occurrence.
ACTUALFINISH	No	DATETIME	No	The actual datetime stamp when the activity was finished.
CREATEDBY	No	INTEGER	No	The ID number of the staff member who created the activity.
CREATEDATE	No	DATETIME	Yes	The date and time when the activity was created.
MODIFIEDBY	No	INTEGER	No	The ID number of the staff member who last modified the activity.
MODIFIEDDATE	No	DATETIME	No	The date and time when the activity was last edited.
OUTLOOKENTRYID	No	VARCHAR(255)	No	If the activity has been synched to MS Outlook, this field contains the ID number of the corresponding task or appointment in Outlook.
RESOURCEALLOC_SEQNO	No	INTEGER	No	The number ID of the resource allocation the activity has been created for (links to JOB_RESOURCE_ALLOCATION.SEQNO)
DETAILS	No	VARCHAR(5500)	No	Details relating to the activity.
ACTIVITY_TYPE	No	INTEGER	Yes	The field contains 0 if the activity is an appointment, or 1 if it is a task.
COMPLETED_PERCENT	No	FLOAT(53)	Yes	The percentage completion amount for the activity (only used for tasks).
DELETED_FLAG	No	CHAR(1)	Yes	This field contains "Y" if the activity has been deleted.
SYNC_ACTIVITY	No	CHAR(1)	Yes	This field contains "Y" if the activity will be synced to MS Outlook.
CAMPAIGN_WAVE_SEQNO	No	INTEGER	No	The ID number of the campaign wave the activity relates to (links to CAMPAIGN_WAVE.SEQNO).
CAMPAIGN_SEQNO	No	INTEGER	No	The ID number of the campaign the activity relates to (links to CAMPAIGN.SEQNO).
JOBNO	No	INTEGER	Yes	The ID number of the job the activity relates to (links to JOBCOST_HDR.JOBNO).
SU_SEQNO	No	INTEGER	Yes	The ID number of the serviceable unit the activity relates to (links to SU_MAIN.SEQNO).
SUBS_HDR_SEQNO	No	INTEGER	Yes	The ID number of the subscription the activity relates to (links to SUBS_HDR.SEQNO)
SOURCEID	No	INTEGER	No	For recurring events, this is the ID number of the master/source event.
SO_SEQNO	No	INTEGER	No	The ID number of the Sales Order the activity relates to (links to SALESORD_HDR.SEQNO).
PO_SEQNO	No	INTEGER	No	The ID number of the Purchase Order the activity relates to (links to PURCHORD_HDR.SEQNO).

Relationships:

Name	Relationship type	Parent	Child	Cardinality
CAMPAIGN_TASKS	Non Identifying	CAMPAIGN	TASKS	Zero Or More
CAMPAIGN_WAVE_TASKS	Non Identifying	CAMPAIGN_WAVE	TASKS	Zero Or More
CONTACTS_TASKS	Non Identifying	CONTACTS	TASKS	Zero Or More
JOBCOST_HDR_TASKS	Non Identifying	JOBCOST_HDR	TASKS	Zero Or More
JOB_RESOURCE_ALLOCATION_TASKS	Non Identifying	JOB_RESOURCE_ALLOCATION	TASKS	Zero Or More
OPPORTUNITY_TASKS	Non Identifying	OPPORTUNITY	TASKS	Zero Or More
PURCHORD_HDR_TASKS	Non Identifying	PURCHORD_HDR	TASKS	Zero Or More
SALESORD_HDR_TASKS	Non Identifying	SALESORD_HDR	TASKS	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
SUBS_HDR_TASKS	Non Identifying	SUBS_HDR	TASKS	Zero Or More
SU_MAIN_TASKS	Non Identifying	SU_MAIN	TASKS	Zero Or More
TASK_STATUSES_TASKS	Non Identifying	TASK_STATUSES	TASKS	Zero Or More
TASK_TYPES_TASKS	Non Identifying	TASK_TYPES	TASKS	Zero Or More

Constraints:

Name	Type	Level	Constraint
	Not Null	Column Constraint	NOT NULL (SUBS_HDR_SEQNO)
	Not Null	Column Constraint	NOT NULL (SEQNO)
	Not Null	Column Constraint	NOT NULL (COMPLETED)
	Not Null	Column Constraint	NOT NULL (SU_SEQNO)
	Not Null	Column Constraint	NOT NULL (CREATEDATE)
	Not Null	Column Constraint	NOT NULL (JOBNO)
	Not Null	Column Constraint	NOT NULL (ACTIVITY_TYPE)
	Not Null	Column Constraint	NOT NULL (SYNC_ACTIVITY)
	Not Null	Column Constraint	NOT NULL (COMPLETED_PERCENT)
	Not Null	Column Constraint	NOT NULL (DELETED_FLAG)
CAMPAIGN_TASKS	Foreign Key	Table Constraint	FOREIGN KEY (CAMPAIGN_SEQNO) REFERENCES CAMPAIGN(SEQNO)
CAMPAIGN_WAVE_TASKS	Foreign Key	Table Constraint	FOREIGN KEY (CAMPAIGN_WAVE_SEQNO) REFERENCES CAMPAIGN_WAVE(SEQNO)
CONTACTS_TASKS	Foreign Key	Table Constraint	FOREIGN KEY (CONTACTSEQNO) REFERENCES CONTACTS(SEQNO)
DF__TASKS__ACTIVITY___61A803EE	Default	Column Constraint	ACTIVITY_TYPE DEFAULT 0
DF__TASKS__CAMPAIGN___5438FA37	Default	Column Constraint	CAMPAIGN_WAVE_SEQNO DEFAULT 0
DF__TASKS__CAMPAIGN___6192F555	Default	Column Constraint	CAMPAIGN_SEQNO DEFAULT 0
DF__TASKS__COMPLETED__629C2827	Default	Column Constraint	COMPLETED_PERCENT DEFAULT 0
DF__TASKS__COMPLETED__64247DE6	Default	Column Constraint	COMPLETED DEFAULT N
DF__TASKS__CREATEDAT__6518A21F	Default	Column Constraint	CREATEDATE DEFAULT getdate()
DF__TASKS__DELETED_F__63904C60	Default	Column Constraint	DELETED_FLAG DEFAULT N
DF__TASKS__JOBNO__04DC3192	Default	Column Constraint	JOBNO DEFAULT -1
DF__TASKS__PO_SEQNO__12611539	Default	Column Constraint	PO_SEQNO DEFAULT -1
DF__TASKS__SOURCEID__6756BBA3	Default	Column Constraint	SOURCEID DEFAULT -1
DF__TASKS__SO_SEQNO__116CF100	Default	Column Constraint	SO_SEQNO DEFAULT -1
DF__TASKS__SUBS_HDR___06C47A04	Default	Column Constraint	SUBS_HDR_SEQNO DEFAULT -1
DF__TASKS__SU_SEQNO__05D055CB	Default	Column Constraint	SU_SEQNO DEFAULT -1
DF__TASKS__SYNC_ACTI__64847099	Default	Column Constraint	SYNC_ACTIVITY DEFAULT Y
JOBCOST_HDR_TASKS	Foreign Key	Table Constraint	FOREIGN KEY (JOBNO) REFERENCES JOBCOST_HDR(JOBNO)
JOB_RESOURCE_ALLOCATION_TASKS	Foreign Key	Table Constraint	FOREIGN KEY (RESOURCEALLOC_SEQNO) REFERENCES JOB_RESOURCE_ALLOCATION(SEQNO)
OPPORTUNITY_TASKS	Foreign Key	Table Constraint	FOREIGN KEY (OPPORTUNITYSEQNO) REFERENCES OPPORTUNITY(SEQNO)
PK__TASKS__633059AD	Primary Key	Table Constraint	PRIMARY KEY (SEQNO)
PURCHORD_HDR_TASKS	Foreign Key	Table Constraint	FOREIGN KEY (PO_SEQNO) REFERENCES PURCHORD_HDR(SEQNO)
SALESORD_HDR_TASKS	Foreign Key	Table Constraint	FOREIGN KEY (SO_SEQNO) REFERENCES SALESORD_HDR(SEQNO)
STAFF_TASKS	Foreign Key	Table Constraint	FOREIGN KEY (CREATEDBY) REFERENCES STAFF(STAFFNO)
STAFF_TASKS1	Foreign Key	Table Constraint	FOREIGN KEY (ASSIGNED_TO) REFERENCES STAFF(STAFFNO)
STAFF_TASKS2	Foreign Key	Table Constraint	FOREIGN KEY (ASSIGNED_BY) REFERENCES STAFF(STAFFNO)
STAFF_TASKS3	Foreign Key	Table Constraint	FOREIGN KEY (MODIFIEDBY) REFERENCES STAFF(STAFFNO)
SUBS_HDR_TASKS	Foreign Key	Table Constraint	FOREIGN KEY (SUBS_HDR_SEQNO) REFERENCES SUBS_HDR(SEQNO)
SU_MAIN_TASKS	Foreign Key	Table Constraint	FOREIGN KEY (SU_SEQNO) REFERENCES SU_MAIN(SEQNO)
TASK_STATUSES_TASKS	Foreign Key	Table Constraint	FOREIGN KEY (STATUS) REFERENCES TASK_STATUSES(SEQNO)
TASK_TYPES_TASKS	Foreign Key	Table Constraint	FOREIGN KEY (TYPE) REFERENCES TASK_TYPES(SEQNO)

Entity: TASK_STATUSES

Comment	This table contains details of the statues that can be assigned to activities.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the status.
DESCRIPTION	No	VARCHAR(60)	No	The name/description of the status.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
TASK_STATUSES_TASKS	Non Identifying	TASK_STATUSES	TASKS	Zero Or More

Constraints:

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

Entity: TASK_TYPES

Comment	This table contains the types that can be assigned to activities.
Primary key columns	SEQNO

Attributes:

Column name	Primary key	Data type	Not NULL	Comment
SEQNO	Yes	INTEGER	Yes	A unique ID number for the type.
DESCRIPTION	No	VARCHAR(60)	No	The type's name/description.

Relationships:

Name	Relationship type	Parent	Child	Cardinality
TASK_TYPES_TASKS	Non Identifying	TASK_TYPES	TASKS	Zero Or More

Constraints:

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