|
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: CR_ACCGROUP2S
| Comment | This table stores the Secondary Groups for Creditors (a grouping on top of the primary grouping - see CR_ACCGROUPS). |
| Primary key columns | ACCGROUP |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| ACCGROUP | Yes | INTEGER | Yes | A unique ID for the Secondary Creditor Group. |
| GROUPNAME | No | VARCHAR(30) | No | The name of the group. |
| REPORTCODE | No | VARCHAR(15) | No | A report code for the group. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CR_ACCGROUP2S_CR_ACCS | Non Identifying | CR_ACCGROUP2S | CR_ACCS | Zero Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (ACCGROUP) |
| PK__CR_ACCGROUP2S__395884C4 | Primary Key | Table Constraint | PRIMARY KEY (ACCGROUP) |
Entity: CR_ACCGROUPS
| Comment | This table stores the Primary Groups for Creditors (the finer level of grouping - compare CR_ACCGROUP2S). |
| Primary key columns | ACCGROUP |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| ACCGROUP | Yes | INTEGER | Yes | A unique ID for the Primary Creditor Group. |
| GROUPNAME | No | VARCHAR(30) | No | The name of the group. |
| REPORTCODE | No | VARCHAR(15) | No | A report code for the group. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CR_ACCGROUPS_CR_ACCS | Non Identifying | CR_ACCGROUPS | CR_ACCS | Zero Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (ACCGROUP) |
| PK__CR_ACCGROUPS__3A6CA48E | Primary Key | Table Constraint | PRIMARY KEY (ACCGROUP) |
Entity: CR_ACCS
| Comment | This table contains information about Creditor accounts. |
| Primary key columns | ACCNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| ACCNO | Yes | INTEGER | Yes | The unique ID number for the Creditor account. |
| NAME | No | VARCHAR(60) | No | The Creditor's name. |
| ADDRESS1 | No | VARCHAR(30) | No | Postal address line 1. |
| ADDRESS2 | No | VARCHAR(30) | No | Postal address line 2. |
| ADDRESS3 | No | VARCHAR(30) | No | Postal address line 3. |
| ADDRESS4 | No | VARCHAR(30) | No | Postal address line 4. |
| ADDRESS5 | No | VARCHAR(30) | No | Postal address line 5. This line is only available on the Creditor Account Details screen if the Additional Postal Address Row profile setting is enabled. |
| PHONE | No | VARCHAR(30) | No | The Creditor's phone number. |
| FAX | No | VARCHAR(30) | No | The Creditor's fax number. |
| EMAIL | No | VARCHAR(60) | No | The Creditor's email address. |
| CREDLIMIT | No | FLOAT(53) | No | The credit limit for the Creditor account. |
| ACCGROUP | No | INTEGER | No | The Creditor's Primary Group. |
| LASTMONTH | No | FLOAT(53) | No | Turnover for the last month. |
| LASTYEAR | No | FLOAT(53) | No | Turnover for the last year |
| AGEDBAL0 | No | FLOAT(53) | No | Aged Balance for the current month. |
| AGEDBAL1 | No | FLOAT(53) | No | Aged Balance for the previous month. |
| AGEDBAL2 | No | FLOAT(53) | No | Aged Balance for two months back. |
| AGEDBAL3 | No | FLOAT(53) | No | Aged Balance for three months back. |
| BALANCE | No | AS | No | The total of all aged balances. |
| CREDITSTATUS | No | INTEGER | No | Credit terms for the Creditor. |
| OPENITEM | No | CHAR(1) | No | This field contains 'Y' if this is not a balance brought forward account. |
| 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. |
| SALESNO | No | INTEGER | No | ID number of the staff member assigned to the Creditor account. |
| DISCOUNTLEVEL | No | INTEGER | No | The Creditor's discount level - this is only populated via EXO APIs. |
| INVOICETYPE | No | INTEGER | No | This field only applies to Australian databases where M-Powered Invoices are enabled. If set to 1, search results on the Transactions tab of the Creditor Account Details window will only include accounts with the M-Powered invoice type. |
| AUTOFREIGHT | No | FLOAT(53) | No | Not used. |
| NOTES | No | VARCHAR(4096) | No | All notes entered for the Creditor on the Notes tab. |
| MONTHVAL | No | FLOAT(53) | No | Turnover for the current month. |
| YEARVAL | No | FLOAT(53) | No | Turnover for the current year. |
| ALPHACODE | No | VARCHAR(15) | No | A non-unique alphanumeric ID code for the Creditor. Can be used for sorting accounts alphabetically, e.g. give "The Warehouse" the code "WARE" to sort under W instead of T. Sometimes used as an alternate account reference. |
| TAXSTATUS | No | INTEGER | No | The Override GST rate for the Creditor. |
| HEAD_ACCNO | No | INTEGER | Yes | The ACCNO of the Creditor's head office account. If no head office, this is set to -1. |
| CURRENCYNO | No | INTEGER | Yes | The ID number of the currency used by the Creditor account. |
| ALERT | No | VARCHAR(60) | No | Pop-up alert text to display when dealing with the Creditor. |
| ISACTIVE | No | CHAR(1) | No | Whether the account is active (Y) or inactive (N). |
| BANK_ACCOUNT | No | VARCHAR(40) | No | The Creditor's bank account number, used for Direct Credit payments. |
| DEFAULT_CODE | No | VARCHAR(15) | No | The default GL code on a Creditors Invoice (when in GL mode). |
| BANK_ACC_NAME | No | VARCHAR(40) | No | The Creditor's bank account name, used for Direct Credit payments. |
| LAST_UPDATED | No | DATETIME | No | Time and date stamp when the Creditor's account details were last updated. |
| LEADTIME | No | INTEGER | No | The Primary Lead Time defined for the Creditor, i.e. the number of days between placing an order and its arrival. |
| TAXREG | No | VARCHAR(30) | No | GST/ABN number of the Creditor. |
| POST_CODE | No | VARCHAR(12) | No | The Creditor's postal code. |
| N_CR_DISC | No | FLOAT(53) | No | The stock line discount for the account on invoice. This is only applicable if manual entry of discounts is allowed on Creditors Invoices (controlled by the profile setting 'Allow manual entry of discounts on creditor invoice and purchase order lines'). |
| GLCONTROLACC | No | INTEGER | No | Not currently used. |
| GLCONTROLSUBACC | No | INTEGER | No | Not currently used. |
| BRANCHNO | No | INTEGER | No | Branch number - only used by specific sites. |
| PRIOR_AGEDBAL0 | No | FLOAT(53) | No | Prior Aged Balance for the current month. |
| PRIOR_AGEDBAL1 | No | FLOAT(53) | No | Prior Aged Balance for the previous month. |
| PRIOR_AGEDBAL2 | No | FLOAT(53) | No | Prior Aged Balance for two months ago. |
| PRIOR_AGEDBAL3 | No | FLOAT(53) | No | Prior Aged Balance for three months ago. |
| PROMPT_PAY_DISC | No | FLOAT(53) | No | The prompt payment discount percentage for the Creditor. |
| BSBNO | No | VARCHAR(40) | No | The Creditor's Bank State Branch number (Australian databases only). |
| AUTO_AUTH_AMT | No | FLOAT(53) | No | The amount that can be automatically authorised by the trigger AUTHORISE_TRANS on the CR_TRANS table. |
| PAY_TYPE | No | INTEGER | No | The default payment method. |
| PRIOR_BALANCE | No | AS | No | The sum of all Prior Aged Balances. |
| ACCGROUP2 | No | INTEGER | No | The Creditor's Secondary Group. |
| LEADTIME2 | No | INTEGER | No | The Secondary Lead Time defined for the Creditor (the alternate lead delivery time in days). |
| N_LAND_COST_PROVN | No | FLOAT(53) | No | This value is used to calculate the Fixed cost for any Inwards Goods for the account. |
| PP_TOPAY | No | CHAR(1) | No | The pay status from the Creditors Payment Processor. |
| STOPCREDIT | No | CHAR(1) | No | If 'Y', the Creditor is on Stop Credit, and no further transactions will take place. |
| DEF_INVMODE | No | INTEGER | Yes | The Default Invoice Mode for the Creditor. Will be one of:
0 = GL
1 = STOCK |
| PRIVATE_ACC | No | CHAR(1) | Yes | If 'Y', the Creditor account is classed as private. It can only be accessed by users who have the profile setting "Allow access to private creditor accounts" (ALLOW_ACCESS_TO_PRIVATE_CR_ACC) enabled. |
| WEBSITE | No | VARCHAR(30) | No | URL of the Creditor's website. |
| AVE_DAYS_TO_PAY | No | INTEGER | Yes | Average Creditor days, calculated by the Stored Procedure DR_CR_ALLOCATION_AVE_DAYS_SP every time you access the Analysis tab. |
| STATEMENT_TEXT | No | VARCHAR(256) | No | This field is used for M-Powered Payments (AU only). Its value is copied to the REF3 field on CR_TRANS for M-Powered transactions. |
| REMITTANCE_METHOD | No | VARCHAR(20) | No | How remittance advice should be sent to the Creditor. Will be one of:
None
E-Mail
Print
Both |
| SEND_PAYMENT_REMITTANCE | No | CHAR(1) | Yes | If this field contains 'Y', remittance advice will be sent to this Creditor whenever payments are made to them via the MYOB M-Powered Payments system. |
| STATEMENT_CONTACT_SEQNO | No | INTEGER | No | The ID number of the Contact who is assigned as the Creditor's Remittance Contact. |
| LINKEDIN | No | VARCHAR(20) | No | The Creditor's LinkedIn account ID. |
| TWITTER | No | VARCHAR(500) | No | The Creditor's Twitter username. |
| FACEBOOK | No | VARCHAR(500) | No | The Creditor's Facebook account ID or username. |
| TRACK_CITP | No | CHAR(1) | Yes | Australian companies only. This field is set to 'Y' if payments to the Creditor should be tracked for reporting to the ATO on the "Taxable payments annual report". |
| LATITUDE | No | FLOAT(53) | No | The Creditor's latitude. |
| LONGITUDE | No | FLOAT(53) | No | The Creditor'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 |
| EXTERNAL_ID | No | VARCHAR(50) | No | An ID used for external systems. |
| IGNOREABNCHECK | No | CHAR(1) | Yes | Indicates whether or not the ABN validation check on the creditor's name should be ignored. |
| TAX_LOCALITY | No | VARCHAR(27) | No | Suburb, Town or Locality used for online tax submissions, e.g. TPAR. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CR_ACCGROUP2S_CR_ACCS | Non Identifying | CR_ACCGROUP2S | CR_ACCS | Zero Or More |
| CR_ACCGROUPS_CR_ACCS | Non Identifying | CR_ACCGROUPS | CR_ACCS | Zero Or More |
| CR_ACCS_CR_CONTACTS | Non Identifying | CR_ACCS | CR_CONTACTS | Zero Or More |
| CR_ACCS_CR_CONT_HIST | Non Identifying | CR_ACCS | CR_CONT_HIST | Zero Or More |
| CR_ACCS_CR_INVLINES | Non Identifying | CR_ACCS | CR_INVLINES | Zero Or More |
| CR_ACCS_CR_LISTS | Non Identifying | CR_ACCS | CR_LISTS | Zero Or More |
| CR_ACCS_CR_TRANS | Non Identifying | CR_ACCS | CR_TRANS | Zero Or More |
| CR_ACCS_INWARDS_GOODS | Non Identifying | CR_ACCS | INWARDS_GOODS | Zero Or More |
| CR_ACCS_INWARDS_GOODS_COSTS | Non Identifying | CR_ACCS | INWARDS_GOODS_COSTS | Zero Or More |
| CR_ACCS_JOBCOST_LINES | Non Identifying | CR_ACCS | JOBCOST_LINES | Zero Or More |
| CR_ACCS_PURCHORD_HDR | Non Identifying | CR_ACCS | PURCHORD_HDR | Zero Or More |
| CR_ACCS_STOCK_ITEMS | Non Identifying | CR_ACCS | STOCK_ITEMS | Zero Or More |
| STAFF_CR_ACCS | Non Identifying | STAFF | CR_ACCS | Zero Or More |
Indexes:
| Name | Index columns | Index type |
| CR_ACCS_NAME | NAME ASC | |
| CR_ACCS_TAXSTATUS | TAXSTATUS | |
| CR_ACCS_TAXSTATUS | TAXSTATUS ASC | |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (IGNOREABNCHECK) |
| Not Null | Column Constraint | NOT NULL (ACCNO) |
| Not Null | Column Constraint | NOT NULL (TRACK_CITP) |
| Not Null | Column Constraint | NOT NULL (SEND_PAYMENT_REMITTANCE) |
| Not Null | Column Constraint | NOT NULL (AVE_DAYS_TO_PAY) |
| Not Null | Column Constraint | NOT NULL (PRIVATE_ACC) |
| Not Null | Column Constraint | NOT NULL (DEF_INVMODE) |
| Not Null | Column Constraint | NOT NULL (CURRENCYNO) |
| Not Null | Column Constraint | NOT NULL (HEAD_ACCNO) |
| CR_ACCGROUP2S_CR_ACCS | Foreign Key | Table Constraint | FOREIGN KEY (ACCGROUP2) REFERENCES CR_ACCGROUP2S(ACCGROUP) |
| CR_ACCGROUPS_CR_ACCS | Foreign Key | Table Constraint | FOREIGN KEY (ACCGROUP) REFERENCES CR_ACCGROUPS(ACCGROUP) |
| DF_CR_ACCS_CURRENCYNO | Default | Column Constraint | CURRENCYNO DEFAULT 0 |
| DF_CR_ACCS_HEAD_ACCNO | Default | Column Constraint | HEAD_ACCNO DEFAULT -1 |
| DF_CR_ACCS_PAY_TYPE | Default | Column Constraint | PAY_TYPE DEFAULT 0 |
| DF__CR_ACCS__ACCGROU__3F3159AB | Default | Column Constraint | ACCGROUP DEFAULT 0 |
| DF__CR_ACCS__ACCGROU__4D9F7493 | Default | Column Constraint | ACCGROUP2 DEFAULT 0 |
| DF__CR_ACCS__AGEDBAL__420DC656 | Default | Column Constraint | AGEDBAL0 DEFAULT 0 |
| DF__CR_ACCS__AGEDBAL__4301EA8F | Default | Column Constraint | AGEDBAL1 DEFAULT 0 |
| DF__CR_ACCS__AGEDBAL__43F60EC8 | Default | Column Constraint | AGEDBAL2 DEFAULT 0 |
| DF__CR_ACCS__AGEDBAL__44EA3301 | Default | Column Constraint | AGEDBAL3 DEFAULT 0 |
| DF__CR_ACCS__AUTOFRE__4B973090 | Default | Column Constraint | AUTOFREIGHT DEFAULT 0 |
| DF__CR_ACCS__AVE_DAY__2C8A3039 | Default | Column Constraint | AVE_DAYS_TO_PAY DEFAULT -1 |
| DF__CR_ACCS__BRANCHN__3B60C8C7 | Default | Column Constraint | BRANCHNO DEFAULT 0 |
| DF__CR_ACCS__CREDITS__46D27B73 | Default | Column Constraint | CREDITSTATUS DEFAULT 0 |
| DF__CR_ACCS__CREDLIM__3E3D3572 | Default | Column Constraint | CREDLIMIT DEFAULT 0 |
| DF__CR_ACCS__DEF_INV__6E4219A6 | Default | Column Constraint | DEF_INVMODE DEFAULT 0 |
| DF__CR_ACCS__DISCOUN__49AEE81E | Default | Column Constraint | DISCOUNTLEVEL DEFAULT 0 |
| DF__CR_ACCS__GEOCODE__4556A95A | Default | Column Constraint | GEOCODE_STATUS DEFAULT NULL |
| DF__CR_ACCS__GLCONTR__39788055 | Default | Column Constraint | GLCONTROLACC DEFAULT 0 |
| DF__CR_ACCS__GLCONTR__3A6CA48E | Default | Column Constraint | GLCONTROLSUBACC DEFAULT 0 |
| DF__CR_ACCS__INVOICE__4AA30C57 | Default | Column Constraint | INVOICETYPE DEFAULT 0 |
| DF__CR_ACCS__ISACTIV__269AB60B | Default | Column Constraint | ISACTIVE DEFAULT Y |
| DF__CR_ACCS__LASTMON__40257DE4 | Default | Column Constraint | LASTMONTH DEFAULT 0 |
| DF__CR_ACCS__LASTYEA__4119A21D | Default | Column Constraint | LASTYEAR DEFAULT 0 |
| DF__CR_ACCS__LEADTIM__0BB1B5A5 | Default | Column Constraint | LEADTIME DEFAULT 0 |
| DF__CR_ACCS__LEADTIM__6B2FD77A | Default | Column Constraint | LEADTIME2 DEFAULT 0 |
| DF__CR_ACCS__MONTHVA__4C8B54C9 | Default | Column Constraint | MONTHVAL DEFAULT 0 |
| DF__CR_ACCS__N_CR_DI__1FCDBCEB | Default | Column Constraint | N_CR_DISC DEFAULT 0 |
| DF__CR_ACCS__N_LAND___6F00685E | Default | Column Constraint | N_LAND_COST_PROVN DEFAULT 0 |
| DF__CR_ACCS__OPENITE__47C69FAC | Default | Column Constraint | OPENITEM DEFAULT Y |
| DF__CR_ACCS__PP_TOPA__2BEA4664 | Default | Column Constraint | PP_TOPAY DEFAULT N |
| DF__CR_ACCS__PRIOR_A__00FF1D08 | Default | Column Constraint | PRIOR_AGEDBAL0 DEFAULT 0 |
| DF__CR_ACCS__PRIOR_A__01F34141 | Default | Column Constraint | PRIOR_AGEDBAL1 DEFAULT 0 |
| DF__CR_ACCS__PRIOR_A__02E7657A | Default | Column Constraint | PRIOR_AGEDBAL2 DEFAULT 0 |
| DF__CR_ACCS__PRIOR_A__03DB89B3 | Default | Column Constraint | PRIOR_AGEDBAL3 DEFAULT 0 |
| DF__CR_ACCS__PRIVATE__109731AA | Default | Column Constraint | PRIVATE_ACC DEFAULT N |
| DF__CR_ACCS__SALESNO__48BAC3E5 | Default | Column Constraint | SALESNO DEFAULT 0 |
| DF__CR_ACCS__SEND_PA__7CA612ED | Default | Column Constraint | SEND_PAYMENT_REMITTANCE DEFAULT N |
| DF__CR_ACCS__STATEME__60E8E9DF | Default | Column Constraint | STATEMENT_CONTACT_SEQNO DEFAULT -1 |
| DF__CR_ACCS__STOPCRE__2D9D84AC | Default | Column Constraint | STOPCREDIT DEFAULT N |
| DF__CR_ACCS__TAXSTAT__1CFC3D38 | Default | Column Constraint | TAXSTATUS DEFAULT 0 |
| DF__CR_ACCS__TRACK_C__6FEC01A4 | Default | Column Constraint | TRACK_CITP DEFAULT N |
| DF__CR_ACCS__YEARVAL__4D7F7902 | Default | Column Constraint | YEARVAL DEFAULT 0 |
| PK__CR_ACCS__3C54ED00 | Primary Key | Table Constraint | PRIMARY KEY (ACCNO) |
| STAFF_CR_ACCS | Foreign Key | Table Constraint | FOREIGN KEY (SALESNO) REFERENCES STAFF(STAFFNO) |
Entity: CR_ALLOCATIONS
| Comment | This table stores details of Creditor allocations. |
| Primary key columns | SEQNO, ALLOCNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique ID number for the allocation. |
| ALLOCNO | Yes | INTEGER | Yes | A unique ID number for the allocation set. |
| TRANS_SEQNO | No | INTEGER | No | This field is a link back to the original transaction line in CR_TRANS table (CR_TRANS.SEQNO). |
| AMOUNT | No | FLOAT(53) | No | The amount allocated. |
| CURRENCY | No | INTEGER | No | The ID number of the currency in which the transaction took place. |
| TAKENUP | No | CHAR(1) | No | This field is a flag that specify whether or not the allocation has been a part of realised gain/losses. |
| ALLOCTIME | No | DATETIME | No | The date and time of the allocation. |
| PERIOD_SEQNO | No | INTEGER | Yes | The period in which the allocation took place. |
| AGE_STAMP | No | INTEGER | No | This is a historical field that has been superseded by the AGE calculated field. |
| AGE | No | AS | No | The allocation's age. This is calculated based on PERIOD_SEQNO. |
| EXCHRATE | No | FLOAT(53) | Yes | The exchange rate used for conversion at the time of the allocation. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CR_TRANS_CR_ALLOCATIONS | Non Identifying | CR_TRANS | CR_ALLOCATIONS | Zero Or More |
| CURRENCIES_CR_ALLOCATIONS | Non Identifying | CURRENCIES | CR_ALLOCATIONS | Zero Or More |
| PERIOD_STATUS_CR_ALLOCATIONS | Non Identifying | PERIOD_STATUS | CR_ALLOCATIONS | Zero Or More |
Indexes:
| Name | Index columns | Index type |
| CR_ALLOCATIONS_PERIOD_SEQNO_INDEX | PERIOD_SEQNO ASC | |
| CR_ALLOCATIONS_TRANS_SEQNO_INDEX | TRANS_SEQNO ASC | |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (EXCHRATE) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| Not Null | Column Constraint | NOT NULL (ALLOCNO) |
| Not Null | Column Constraint | NOT NULL (PERIOD_SEQNO) |
| CR_TRANS_CR_ALLOCATIONS | Foreign Key | Table Constraint | FOREIGN KEY (TRANS_SEQNO) REFERENCES CR_TRANS(SEQNO) |
| CURRENCIES_CR_ALLOCATIONS | Foreign Key | Table Constraint | FOREIGN KEY (CURRENCY) REFERENCES CURRENCIES(CURRENCYNO) |
| DF__CR_ALLOCA__AGE_S__46E920BA | Default | Column Constraint | AGE_STAMP DEFAULT -1 |
| DF__CR_ALLOCA__ALLOC__041093DD | Default | Column Constraint | ALLOCTIME DEFAULT getdate() |
| DF__CR_ALLOCA__AMOUN__61BB7BD9 | Default | Column Constraint | AMOUNT DEFAULT 0 |
| DF__CR_ALLOCA__CURRE__62AFA012 | Default | Column Constraint | CURRENCY DEFAULT 0 |
| DF__CR_ALLOCA__EXCHR__7ED93F79 | Default | Column Constraint | EXCHRATE DEFAULT 1 |
| DF__CR_ALLOCA__PERIO__45F4FC81 | Default | Column Constraint | PERIOD_SEQNO DEFAULT 0 |
| DF__CR_ALLOCA__TAKEN__63A3C44B | Default | Column Constraint | TAKENUP DEFAULT N |
| PERIOD_STATUS_CR_ALLOCATIONS | Foreign Key | Table Constraint | FOREIGN KEY (PERIOD_SEQNO) REFERENCES PERIOD_STATUS(SEQNO) |
| PK__CR_ALLOCATIONS__60C757A0 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO, ALLOCNO) |
Triggers:
| Trigger name | Code |
| update_craccbal_onalloc | CREATE TRIGGER [dbo].[update_craccbal_onalloc] on [dbo].[CR_ALLOCATIONS]
FOR INSERT
AS
declare @accno integer,
@age integer,
@trans_seqno integer,
@amount float,
@exchrate float
begin
SET NOCOUNT ON
select @trans_seqno=trans_seqno, @amount=amount from inserted
select @accno=accno, @age=age, @exchrate=exchrate from cr_trans where seqno=@trans_seqno
IF (@age=0) begin
UPDATE cr_accs
SET agedbal0=agedbal0-@amount
WHERE ACCNO=@ACCNO
end
ELSE IF (@age=1) begin
UPDATE cr_accs
SET agedbal1=agedbal1-@amount
WHERE ACCNO=@ACCNO
end
ELSE IF (@age=2) begin
UPDATE cr_accs
SET agedbal2=agedbal2-@amount
WHERE ACCNO=@ACCNO
end
ELSE BEGIN UPDATE cr_accs
SET agedbal3=agedbal3-@amount
WHERE ACCNO=@ACCNO
END
UPDATE [dbo].[CR_ALLOCATIONS]
SET EXCHRATE=@exchrate
WHERE TRANS_SEQNO=@trans_seqno
SET NOCOUNT OFF
end
|
| UPDATE_CRACCBAL_ONUNALLOC | CREATE TRIGGER [dbo].[UPDATE_CRACCBAL_ONUNALLOC] on [dbo].[CR_ALLOCATIONS]
FOR DELETE
AS
declare @accno integer,
@age integer,
@trans_seqno integer,
@amount float
begin
SET NOCOUNT ON
select @trans_seqno=trans_seqno, @amount=amount from deleted
select @accno=accno, @age=age from CR_trans where seqno=@trans_seqno
IF (@age=0) begin
UPDATE CR_ACCS
SET agedbal0=agedbal0+@amount
WHERE ACCNO=@ACCNO
end
ELSE IF (@age=1) begin
UPDATE CR_ACCS
SET agedbal1=agedbal1+@amount
WHERE ACCNO=@ACCNO
end
ELSE IF (@age=2) begin
UPDATE CR_ACCS
SET agedbal2=agedbal2+@amount
WHERE ACCNO=@ACCNO
end
ELSE BEGIN UPDATE CR_ACCS
SET agedbal3=agedbal3+@amount
WHERE ACCNO=@ACCNO
END
UPDATE CR_TRANS
SET ALLOCATEDBAL=ALLOCATEDBAL-@amount, ALLOCATED=0
WHERE SEQNO=@TRANS_SEQNO
SET NOCOUNT OFF
end
|
| UPDATE_CRACC_PRIORBAL_ONALLOC | CREATE TRIGGER [dbo].[UPDATE_CRACC_PRIORBAL_ONALLOC] ON [dbo].[CR_ALLOCATIONS]
FOR INSERT AS
DECLARE @ACCNO INTEGER,
@TRANS_AGE INTEGER,
@ALLOC_AGE INTEGER,
@TRANS_SEQNO INTEGER,
@TRANS_AMOUNT FLOAT,
@AMOUNT FLOAT
BEGIN
SET NOCOUNT ON
SELECT @TRANS_SEQNO=TRANS_SEQNO, @AMOUNT=AMOUNT, @ALLOC_AGE=AGE FROM INSERTED
SELECT @ACCNO=ACCNO, @TRANS_AMOUNT=AMOUNT, @TRANS_AGE=AGE FROM CR_TRANS WHERE SEQNO=@TRANS_SEQNO
IF (@ALLOC_AGE>0)
BEGIN
IF (@TRANS_AGE=1)
BEGIN
UPDATE CR_ACCS SET PRIOR_AGEDBAL0=PRIOR_AGEDBAL0-@AMOUNT WHERE ACCNO=@ACCNO
END
ELSE
IF (@TRANS_AGE=2)
BEGIN
UPDATE CR_ACCS SET PRIOR_AGEDBAL1=PRIOR_AGEDBAL1-@AMOUNT WHERE ACCNO=@ACCNO
END
ELSE
IF (@TRANS_AGE=3)
BEGIN
UPDATE CR_ACCS SET PRIOR_AGEDBAL2=PRIOR_AGEDBAL2-@AMOUNT WHERE ACCNO=@ACCNO
END
ELSE
BEGIN
UPDATE CR_ACCS SET PRIOR_AGEDBAL3=PRIOR_AGEDBAL3-@AMOUNT WHERE ACCNO=@ACCNO
END
IF (@ALLOC_AGE > 0)
BEGIN
IF @ALLOC_AGE >= 1
BEGIN
IF (@TRANS_AGE>=2)
BEGIN
UPDATE CR_TRANS
SET ALLOCAGE=@ALLOC_AGE, PREV_PERIOD_OPEN=@TRANS_AMOUNT-ISNULL((SELECT SUM(AMOUNT) FROM [dbo].[CR_ALLOCATIONS]
WHERE TRANS_SEQNO=@TRANS_SEQNO AND AGE>=2), 0), PREV_PERIOD_CLOSE=@TRANS_AMOUNT-ISNULL((SELECT SUM(AMOUNT)
FROM [dbo].[CR_ALLOCATIONS] WHERE TRANS_SEQNO=@TRANS_SEQNO AND AGE>=1), 0) WHERE SEQNO=@TRANS_SEQNO
END
IF (@TRANS_AGE=1)
BEGIN
UPDATE CR_TRANS
SET ALLOCAGE=@ALLOC_AGE, PREV_PERIOD_OPEN=0, PREV_PERIOD_CLOSE=@TRANS_AMOUNT-ISNULL((SELECT SUM(AMOUNT)
FROM [dbo].[CR_ALLOCATIONS] WHERE TRANS_SEQNO=@TRANS_SEQNO AND AGE>=1), 0) WHERE SEQNO=@TRANS_SEQNO
END
END
IF (@TRANS_AMOUNT-ISNULL((SELECT SUM(AMOUNT) FROM [dbo].[CR_ALLOCATIONS] WHERE TRANS_SEQNO=@TRANS_SEQNO AND AGE>=1), 0))=0
BEGIN
UPDATE CR_TRANS SET ALLOCATED=2 WHERE SEQNO=@TRANS_SEQNO
END
END
END
SET NOCOUNT OFF
END
|
| UPDATE_CRACC_PRIORBAL_ONUNALLOC | CREATE TRIGGER [dbo].[UPDATE_CRACC_PRIORBAL_ONUNALLOC] ON [dbo].[CR_ALLOCATIONS]
FOR DELETE AS
DECLARE @ACCNO INTEGER,
@TRANS_AGE INTEGER,
@ALLOC_AGE INTEGER,
@TRANS_SEQNO INTEGER,
@TRANS_AMOUNT FLOAT,
@AMOUNT FLOAT
BEGIN
SET NOCOUNT ON
SELECT @TRANS_SEQNO=TRANS_SEQNO, @AMOUNT=AMOUNT, @ALLOC_AGE=AGE FROM DELETED
SELECT @ACCNO=ACCNO, @TRANS_AMOUNT=AMOUNT, @TRANS_AGE=AGE FROM CR_TRANS WHERE SEQNO=@TRANS_SEQNO
IF (@ALLOC_AGE>0)
BEGIN
IF (@TRANS_AGE=1)
BEGIN
UPDATE CR_ACCS SET PRIOR_AGEDBAL0=PRIOR_AGEDBAL0+@AMOUNT WHERE ACCNO=@ACCNO
END
ELSE
IF (@TRANS_AGE=2)
BEGIN
UPDATE CR_ACCS SET PRIOR_AGEDBAL1=PRIOR_AGEDBAL1+@AMOUNT WHERE ACCNO=@ACCNO
END
ELSE
IF (@TRANS_AGE=3)
BEGIN
UPDATE CR_ACCS SET PRIOR_AGEDBAL2=PRIOR_AGEDBAL2+@AMOUNT WHERE ACCNO=@ACCNO
END
ELSE
BEGIN
UPDATE CR_ACCS SET PRIOR_AGEDBAL3=PRIOR_AGEDBAL3+@AMOUNT WHERE ACCNO=@ACCNO
END
IF (@ALLOC_AGE > 0)
BEGIN
IF @ALLOC_AGE >= 1
BEGIN
IF (@TRANS_AGE>=2)
BEGIN
UPDATE CR_TRANS
SET ALLOCAGE=@ALLOC_AGE, PREV_PERIOD_OPEN=@TRANS_AMOUNT+ISNULL((SELECT SUM(AMOUNT) FROM [dbo].[CR_ALLOCATIONS]
WHERE TRANS_SEQNO=@TRANS_SEQNO AND AGE>=2), 0), PREV_PERIOD_CLOSE=@TRANS_AMOUNT+ISNULL((SELECT SUM(AMOUNT)
FROM [dbo].[CR_ALLOCATIONS] WHERE TRANS_SEQNO=@TRANS_SEQNO AND AGE>=1), 0) WHERE SEQNO=@TRANS_SEQNO
END
IF (@TRANS_AGE=1)
BEGIN
UPDATE CR_TRANS
SET ALLOCAGE=@ALLOC_AGE, PREV_PERIOD_OPEN=0, PREV_PERIOD_CLOSE=@TRANS_AMOUNT+ISNULL((SELECT SUM(AMOUNT)
FROM [dbo].[CR_ALLOCATIONS] WHERE TRANS_SEQNO=@TRANS_SEQNO AND AGE>=1), 0) WHERE SEQNO=@TRANS_SEQNO
END
END
UPDATE CR_TRANS SET ALLOCATED=0 WHERE SEQNO=@TRANS_SEQNO
END
END
SET NOCOUNT OFF
END
|
Entity: CR_CONTACTS
| Comment | This table contains details of Contact records that are associated with Creditor accounts. Contact details are taken from the relevant entry in the CONTACTS table. |
| Primary key columns | SEQNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique identifier for the Creditor Contact. The identifier for the Contact record is specified by the CONTACT_SEQNO field. |
| ACCNO | No | INTEGER | No | The account number of the Creditor account that the Contact is associated with. |
| DEFCONTACT | No | CHAR(1) | No | This field is set to 'Y' if the Contact is set as the default Contact for the Creditor account. |
| SALUTATION | No | VARCHAR(4) | No | Taken from the associated record in the CONTACTS table. |
| FIRSTNAME | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| LASTNAME | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| TITLE | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| MOBILE | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| DIRECTPHONE | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| DIRECTFAX | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| EMAIL | No | VARCHAR(60) | No | Taken from the associated record in the CONTACTS table. |
| HOMEPHONE | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| NOTES | No | VARCHAR(4096) | No | Taken from the associated record in the CONTACTS table. |
| ADDRESS1 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| ADDRESS2 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| ADDRESS3 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| DELADDR1 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| DELADDR2 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| DELADDR3 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| DELADDR4 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| ISACTIVE | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB1 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB2 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB3 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB4 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| ADVERTSOURCE | No | INTEGER | No | Taken from the associated record in the CONTACTS table. |
| POST_CODE | No | VARCHAR(12) | No | Taken from the associated record in the CONTACTS table. |
| SUB5 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB6 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB7 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB8 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB9 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB10 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB11 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB12 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB13 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB14 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB15 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB16 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB17 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB18 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB19 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB20 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| ADDRESS4 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| DELADDR5 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| DELADDR6 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
| CONTACT_SEQNO | No | INTEGER | No | The ID number of the Contact record in the CONTACTS table. |
| SUB21 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB22 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB23 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB24 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB25 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| SUB26 | No | CHAR(1) | No | Taken from the associated record in the CONTACTS table. |
| DEFACCOUNT | No | CHAR(1) | Yes | This field is set to 'Y' if the Creditor account is the default company for the Contact, i.e. if it is the company specified in the Company field on the Details tab of the Contacts window. |
| ADDRESS5 | No | VARCHAR(30) | No | Taken from the associated record in the CONTACTS table. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CONTACTS_CR_CONTACTS | Non Identifying | CONTACTS | CR_CONTACTS | Zero Or More |
| CR_ACCS_CR_CONTACTS | Non Identifying | CR_ACCS | CR_CONTACTS | Zero Or More |
Indexes:
| Name | Index columns | Index type |
| CRCONTACT_SEQNO_IDX | CONTACT_SEQNO ASC | |
| CR_DEFCONTACT_INDEX | ACCNO ASC, DEFCONTACT ASC | |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (DEFACCOUNT) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| CONTACTS_CR_CONTACTS | Foreign Key | Table Constraint | FOREIGN KEY (CONTACT_SEQNO) REFERENCES CONTACTS(SEQNO) |
| CR_ACCS_CR_CONTACTS | Foreign Key | Table Constraint | FOREIGN KEY (ACCNO) REFERENCES CR_ACCS(ACCNO) |
| DF__CR_CONTACT__SUB1__08C03A61 | Default | Column Constraint | SUB1 DEFAULT N |
| DF__CR_CONTACT__SUB2__09B45E9A | Default | Column Constraint | SUB2 DEFAULT N |
| DF__CR_CONTACT__SUB3__0AA882D3 | Default | Column Constraint | SUB3 DEFAULT N |
| DF__CR_CONTACT__SUB4__0B9CA70C | Default | Column Constraint | SUB4 DEFAULT N |
| DF__CR_CONTACT__SUB5__226010D3 | Default | Column Constraint | SUB5 DEFAULT N |
| DF__CR_CONTACT__SUB6__2354350C | Default | Column Constraint | SUB6 DEFAULT N |
| DF__CR_CONTACT__SUB7__24485945 | Default | Column Constraint | SUB7 DEFAULT N |
| DF__CR_CONTACT__SUB8__253C7D7E | Default | Column Constraint | SUB8 DEFAULT N |
| DF__CR_CONTACT__SUB9__2630A1B7 | Default | Column Constraint | SUB9 DEFAULT N |
| DF__CR_CONTAC__ADVER__0C90CB45 | Default | Column Constraint | ADVERTSOURCE DEFAULT 0 |
| DF__CR_CONTAC__CONTA__70E97C27 | Default | Column Constraint | CONTACT_SEQNO DEFAULT 0 |
| DF__CR_CONTAC__DEFAC__42F89445 | Default | Column Constraint | DEFACCOUNT DEFAULT N |
| DF__CR_CONTAC__DEFCO__1C0818FF | Default | Column Constraint | DEFCONTACT DEFAULT N |
| DF__CR_CONTAC__ISACT__07CC1628 | Default | Column Constraint | ISACTIVE DEFAULT Y |
| DF__CR_CONTAC__SUB10__2724C5F0 | Default | Column Constraint | SUB10 DEFAULT N |
| DF__CR_CONTAC__SUB11__2818EA29 | Default | Column Constraint | SUB11 DEFAULT N |
| DF__CR_CONTAC__SUB12__290D0E62 | Default | Column Constraint | SUB12 DEFAULT N |
| DF__CR_CONTAC__SUB13__2A01329B | Default | Column Constraint | SUB13 DEFAULT N |
| DF__CR_CONTAC__SUB14__2AF556D4 | Default | Column Constraint | SUB14 DEFAULT N |
| DF__CR_CONTAC__SUB15__2BE97B0D | Default | Column Constraint | SUB15 DEFAULT N |
| DF__CR_CONTAC__SUB16__2CDD9F46 | Default | Column Constraint | SUB16 DEFAULT N |
| DF__CR_CONTAC__SUB17__2DD1C37F | Default | Column Constraint | SUB17 DEFAULT N |
| DF__CR_CONTAC__SUB18__2EC5E7B8 | Default | Column Constraint | SUB18 DEFAULT N |
| DF__CR_CONTAC__SUB19__2FBA0BF1 | Default | Column Constraint | SUB19 DEFAULT N |
| DF__CR_CONTAC__SUB20__30AE302A | Default | Column Constraint | SUB20 DEFAULT N |
| DF__CR_CONTAC__SUB21__5A261260 | Default | Column Constraint | SUB21 DEFAULT Y |
| DF__CR_CONTAC__SUB22__5B1A3699 | Default | Column Constraint | SUB22 DEFAULT Y |
| DF__CR_CONTAC__SUB23__5C0E5AD2 | Default | Column Constraint | SUB23 DEFAULT Y |
| DF__CR_CONTAC__SUB24__5D027F0B | Default | Column Constraint | SUB24 DEFAULT Y |
| DF__CR_CONTAC__SUB25__5DF6A344 | Default | Column Constraint | SUB25 DEFAULT Y |
| DF__CR_CONTAC__SUB26__5EEAC77D | Default | Column Constraint | SUB26 DEFAULT Y |
| PK__CR_CONTAC__SEQNO__1B13F4C6 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
Triggers:
| Trigger name | Code |
| UPDATE_CR_DEFCONTACT | CREATE TRIGGER [dbo].[UPDATE_CR_DEFCONTACT] ON [dbo].[CR_CONTACTS] FOR UPDATE AS
DECLARE
@NEW_DEFCONTACT CHAR,
@SEQNO INTEGER,
@ACCNO INTEGER
BEGIN
SET NOCOUNT ON
SELECT @NEW_DEFCONTACT=DEFCONTACT, @SEQNO=SEQNO, @ACCNO=ACCNO FROM INSERTED
IF (@NEW_DEFCONTACT = 'Y')
EXECUTE UPDATE_DEFAULT_CONTACT 0, @SEQNO, @ACCNO
SET NOCOUNT OFF
END
|
| UPDATE_CR_DEFCONTACT2 | CREATE TRIGGER [dbo].[UPDATE_CR_DEFCONTACT2] ON [dbo].[CR_CONTACTS] FOR INSERT AS
DECLARE
@NEW_DEFCONTACT CHAR,
@SEQNO INTEGER,
@ACCNO INTEGER
BEGIN
SET NOCOUNT ON
SELECT @NEW_DEFCONTACT=DEFCONTACT, @SEQNO=SEQNO, @ACCNO=ACCNO FROM INSERTED
IF (@NEW_DEFCONTACT = 'Y')
EXECUTE UPDATE_DEFAULT_CONTACT 0, @SEQNO, @ACCNO
SET NOCOUNT OFF
END
|
Entity: CR_CONT_HIST
| Comment | This table contains information about the History Notes attached to Creditor accounts. |
| Primary key columns | SEQNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique identifier for the record. |
| CONTACT_SEQNO | No | INTEGER | No | The ID number of the associated Contact record. |
| POSTTIME | No | DATETIME | No | The actual date and time that the record was posted. |
| TRANSDATE | No | DATETIME | No | The transaction date against the History Note. |
| COMTYPE | No | INTEGER | No | This field is used by the Serviceable Units add-on module - it is not set from the Creditor UI. |
| SALESNO | No | INTEGER | No | The ID of the staff member selected on the History Note. |
| NOTE | No | VARCHAR(4096) | No | The main body of the History Note. |
| SUBJECT | No | VARCHAR(80) | No | The subject line of the History Note. |
| ACCNO | No | INTEGER | No | The ID number of the Creditor account that the History Note is attached to. |
| ACTIONSTATUS | No | INTEGER | No | This field is used by the Serviceable Units add-on module - it is not set from the Creditors UI. |
| ACTIONDUEDATE | No | DATETIME | No | This field is used by the Serviceable Units add-on module - it is not set from the Creditors UI. |
| JOBNO | No | INTEGER | Yes | This is a legacy field that is no longer used. |
| EVENT_SEQNO | No | INTEGER | No | If the History Note was created as a result of event logging, this field contains the ID number of the reason event from the REASONS table. |
| OUTLOOK_LINK | No | VARCHAR(40) | No | If the History Note was emailed, this field contains a link to the email in MS Outlook. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CONTACTS_CR_CONT_HIST | Non Identifying | CONTACTS | CR_CONT_HIST | Zero Or More |
| CR_ACCS_CR_CONT_HIST | Non Identifying | CR_ACCS | CR_CONT_HIST | Zero Or More |
| STAFF_CR_CONT_HIST | Non Identifying | STAFF | CR_CONT_HIST | Zero Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (JOBNO) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| CONTACTS_CR_CONT_HIST | Foreign Key | Table Constraint | FOREIGN KEY (CONTACT_SEQNO) REFERENCES CONTACTS(SEQNO) |
| CR_ACCS_CR_CONT_HIST | Foreign Key | Table Constraint | FOREIGN KEY (ACCNO) REFERENCES CR_ACCS(ACCNO) |
| DF__CR_CONT_H__ACCNO__3296789C | Default | Column Constraint | ACCNO DEFAULT -1 |
| DF__CR_CONT_H__ACTIO__5911273F | Default | Column Constraint | ACTIONSTATUS DEFAULT 0 |
| DF__CR_CONT_H__ACTIO__5A054B78 | Default | Column Constraint | ACTIONDUEDATE DEFAULT getdate() |
| DF__CR_CONT_H__COMTY__1249A49B | Default | Column Constraint | COMTYPE DEFAULT 0 |
| DF__CR_CONT_H__EVENT__29D7E0C0 | Default | Column Constraint | EVENT_SEQNO DEFAULT -1 |
| DF__CR_CONT_H__JOBNO__0A5E6A10 | Default | Column Constraint | JOBNO DEFAULT 0 |
| DF__CR_CONT_H__POSTT__10615C29 | Default | Column Constraint | POSTTIME DEFAULT getdate() |
| DF__CR_CONT_H__SALES__133DC8D4 | Default | Column Constraint | SALESNO DEFAULT 0 |
| DF__CR_CONT_H__TRANS__11558062 | Default | Column Constraint | TRANSDATE DEFAULT getdate() |
| PK__CR_CONT_H__SEQNO__0F6D37F0 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
| STAFF_CR_CONT_HIST | Foreign Key | Table Constraint | FOREIGN KEY (SALESNO) REFERENCES STAFF(STAFFNO) |
Entity: CR_INVLINES
| Comment | This table contains line information for all Creditor transactions. |
| Primary key columns | SEQNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique transaction line ID. |
| ACCNO | No | INTEGER | No | The account number of the Creditor that the transaction belongs to. |
| INVNO | No | VARCHAR(20) | No | The invoice number. |
| HDR_SEQNO | No | INTEGER | No | The SEQNO of the record in the CR_TRANS table that this record related to. |
| STOCKCODE | No | VARCHAR(23) | No | Code of the Stock item on this line. |
| DESCRIPTION | No | VARCHAR(40) | No | Description of the Stock item on this line. |
| QUANTITY | No | FLOAT(53) | No | The quantity for this line. |
| UNITPRICE | No | FLOAT(53) | No | The unit price of the Stock item on this line. |
| DISCOUNT | No | FLOAT(53) | No | The percentage discount for the Stock item on this line. |
| DISCOUNTAMT | No | FLOAT(53) | No | The discount amount on a stock line. |
| DISCOUNTPCT | No | FLOAT(53) | No | The discount percentage on a stock line. |
| ANALYSIS | No | INTEGER | No | GL code for the Stock line. Can be overridden. The visibility of this column onscreen is controlled by the profile "Enable display and override of GL code from stock item". |
| LOCATION | No | INTEGER | No | The location that the Stock item will be moved out of. Generally, lines inherit the location from the invoice header. |
| UNITPRICE_INCTAX | No | FLOAT(53) | No | The unit price of the Stock item on this line, inclusive of tax. |
| UPDATE_STOCK | No | CHAR(1) | No | If this field is set to 'Y', stock movements are created via the trigger CR_INVLINES_POST. The field is then set to 'T'. |
| JOBCODE | No | VARCHAR(15) | No | If the line originates from a job, the job's code is recorded in this field. |
| CURRENCYNO | No | INTEGER | No | The ID number of the currency used on the line. |
| EXCHRATE | No | FLOAT(53) | No | The exchange rate for the line at the time of the transaction. |
| TAXRATE | No | FLOAT(53) | No | The percentage tax rate on the line at the time of the transaction. |
| CODETYPE | No | CHAR(1) | No | The type of code on the line. Will be one of:
G - GL code
S - Stock code |
| TAXRATE_NO | No | INTEGER | No | The ID number of the tax rate used by the transaction. |
| LINETOTAL_TAX | No | FLOAT(53) | No | The tax total for the line. |
| LINETAX_OVERRIDDEN | No | CHAR(1) | No | Contains 'Y' if the tax on the line has been manually modified. |
| LINE_SOURCE | No | INTEGER | No | The source SEQNO of the line, if it originated from a Sales Order or job. |
| LINETOTAL | No | AS | No | A computed field containing the total value for the line, excluding tax. |
| LINETOTAL_INCTAX | No | AS | No | A computed field containing the total value for the line, including tax. |
| JOBNO | No | INTEGER | No | If the line originates from a job, the job's ID number is recorded in this field. |
| COST_TYPE | No | INTEGER | No | This field only applies if EXO Job Costing is installed. It identifies the Cost Type that the transaction belongs to. |
| COST_GROUP | No | INTEGER | No | This field only applies if EXO Job Costing is installed. It identifies the Cost Group that the transaction belongs to. |
| BRANCHNO | No | INTEGER | No | The branch number. Lines generally inherit the branch specified on the invoice header. |
| GLACCNO | No | INTEGER | No | GL account number. |
| GLSUBACC | No | INTEGER | No | SL sub-account number. |
| BATCHCODE | No | VARCHAR(20) | No | The batch code on the line. The "Enable stock batch codes" Company-level profile setting must be enabled to be able to use batch codes. |
| CRINVLINEID | No | INTEGER | No | A unique ID for the invoice line, which should be used to link to other tables. Every time an invoice is edited, the lines are essentially removed and reinserted again, which changes the SEQNO. However, the CRINVLINEID field retains its value. Hence, any relationships built on this field are still valid in such a scenario. |
| IGRLINESEQNO | No | INTEGER | No | ID number of the Inwards Goods receipt line (if the source of the line is from Inwards Goods). |
| LINETYPE | No | INTEGER | No | This field indicates the type of line. Will be one of:
0 = Normal stock line
1 = BOM header line
2 = BOM component line
4 = Narrative line
5 = GL code |
| KITSEQNO | No | INTEGER | No | 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 physical stock unit. |
| LINKED_QTY | No | FLOAT(53) | No | The physical stock unit quantity. |
| BOMTYPE | No | CHAR(1) | No | The type of Bill of Materials that the line relates to:
N = Normal stock item
B = Build
K = Kit
L = Lookup |
| SHOWLINE | No | CHAR(1) | No | This field applies to Bills of Materials, and is used by Clarity to determine if the line can be printed. |
| LINKEDSTATUS | No | CHAR(1) | No | Will be one of:
S = Stocked
L = Lookup
N = Not applicable |
| BOMPRICING | No | CHAR(1) | No | How Bills of Materials are priced. Will be one of:
C = Pricing by Component
T = Pricing by total |
| NARRATIVE_SEQNO | No | INTEGER | No | The SEQNO for the narrative on this line. |
| INTERCO_COMPANYNO | No | INTEGER | No | This field only applies if EXO InterCompany is used. It contains the Company ID number. |
| INTERCO_BATCH_DESC | No | VARCHAR(80) | No | This field only applies if EXO InterCompany is used. It contains the batch description. |
| INTERCO_DR_BRANCHNO | No | INTEGER | No | This field only applies if EXO InterCompany is used. It contains the Debtor branch number. |
| INTERCO_DR_ACCNO | No | INTEGER | No | This field only applies if EXO InterCompany is used. It contains the Debtor GL account number. |
| INTERCO_DR_SUBACCNO | No | INTEGER | No | This field only applies if EXO InterCompany is used. It contains the Debtor GL sub-account number. |
| INTERCO_CR_BRANCHNO | No | INTEGER | No | This field only applies if EXO InterCompany is used. It contains the Creditor branch number. |
| INTERCO_CR_ACCNO | No | INTEGER | No | This field only applies if EXO InterCompany is used. It contains the Creditor GL account number. |
| INTERCO_CR_SUBACCNO | No | INTEGER | No | This field only applies if EXO InterCompany is used. It contains the Creditor GL sub-account number. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CR_ACCS_CR_INVLINES | Non Identifying | CR_ACCS | CR_INVLINES | Zero Or More |
| CR_INVLINES_CR_INVLINES_SERIALS | Non Identifying | CR_INVLINES | CR_INVLINES_SERIALS | Zero Or More |
| CR_TRANS_CR_INVLINES | Non Identifying | CR_TRANS | CR_INVLINES | Zero Or More |
| CURRENCIES_CR_INVLINES | Non Identifying | CURRENCIES | CR_INVLINES | Zero Or More |
| NARRATIVES_CR_INVLINES | Non Identifying | NARRATIVES | CR_INVLINES | Zero Or More |
| STOCK_ITEMS_CR_INVLINES | Non Identifying | STOCK_ITEMS | CR_INVLINES | Zero Or More |
| TAX_RATES_CR_INVLINES | Non Identifying | TAX_RATES | CR_INVLINES | Zero Or More |
Indexes:
| Name | Index columns | Index type |
| CR_INVLINES_ANALYSIS | ANALYSIS ASC | |
| CR_INVLINES_CODETYPE | CODETYPE ASC | |
| CR_INVLINES_HDRSEQ | HDR_SEQNO ASC | |
| SK_JC6 | JOBNO ASC | |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| CR_ACCS_CR_INVLINES | Foreign Key | Table Constraint | FOREIGN KEY (ACCNO) REFERENCES CR_ACCS(ACCNO) |
| CR_TRANS_CR_INVLINES | Foreign Key | Table Constraint | FOREIGN KEY (HDR_SEQNO) REFERENCES CR_TRANS(SEQNO) |
| CURRENCIES_CR_INVLINES | Foreign Key | Table Constraint | FOREIGN KEY (CURRENCYNO) REFERENCES CURRENCIES(CURRENCYNO) |
| DF_CR_INVLINES_UNITPRICE_INCTAX | Default | Column Constraint | UNITPRICE_INCTAX DEFAULT 0 |
| DF__CR_INVLIN__ACCNO__505BE5AD | Default | Column Constraint | ACCNO DEFAULT 0 |
| DF__CR_INVLIN__ANALY__57FD0775 | Default | Column Constraint | ANALYSIS DEFAULT 0 |
| DF__CR_INVLIN__BOMPR__5091BB2E | Default | Column Constraint | BOMPRICING DEFAULT N |
| DF__CR_INVLIN__BOMTY__4DB54E83 | Default | Column Constraint | BOMTYPE DEFAULT N |
| DF__CR_INVLIN__BRANC__2A4B4B5E | Default | Column Constraint | BRANCHNO DEFAULT 0 |
| DF__CR_INVLIN__COST___286302EC | Default | Column Constraint | COST_TYPE DEFAULT 0 |
| DF__CR_INVLIN__COST___29572725 | Default | Column Constraint | COST_GROUP DEFAULT 0 |
| DF__CR_INVLIN__CRINV__10D65D75 | Default | Column Constraint | CRINVLINEID DEFAULT 0 |
| DF__CR_INVLIN__CURRE__386F4D83 | Default | Column Constraint | CURRENCYNO DEFAULT 0 |
| DF__CR_INVLIN__DISCO__25869641 | Default | Column Constraint | DISCOUNTAMT DEFAULT 0 |
| DF__CR_INVLIN__DISCO__267ABA7A | Default | Column Constraint | DISCOUNTPCT DEFAULT 0 |
| DF__CR_INVLIN__DISCO__5708E33C | Default | Column Constraint | DISCOUNT DEFAULT 0 |
| DF__CR_INVLIN__EXCHR__396371BC | Default | Column Constraint | EXCHRATE DEFAULT 1 |
| DF__CR_INVLIN__GLACC__2B3F6F97 | Default | Column Constraint | GLACCNO DEFAULT 0 |
| DF__CR_INVLIN__GLSUB__2C3393D0 | Default | Column Constraint | GLSUBACC DEFAULT 0 |
| DF__CR_INVLIN__HDR_S__52442E1F | Default | Column Constraint | HDR_SEQNO DEFAULT 0 |
| DF__CR_INVLIN__IGRLI__2A2C1B24 | Default | Column Constraint | IGRLINESEQNO DEFAULT 0 |
| DF__CR_INVLIN__INTER__32182106 | Default | Column Constraint | INTERCO_COMPANYNO DEFAULT 0 |
| DF__CR_INVLIN__INTER__330C453F | Default | Column Constraint | INTERCO_DR_BRANCHNO DEFAULT 0 |
| DF__CR_INVLIN__INTER__34006978 | Default | Column Constraint | INTERCO_DR_ACCNO DEFAULT 0 |
| DF__CR_INVLIN__INTER__34F48DB1 | Default | Column Constraint | INTERCO_DR_SUBACCNO DEFAULT 0 |
| DF__CR_INVLIN__INTER__35E8B1EA | Default | Column Constraint | INTERCO_CR_BRANCHNO DEFAULT 0 |
| DF__CR_INVLIN__INTER__36DCD623 | Default | Column Constraint | INTERCO_CR_ACCNO DEFAULT 0 |
| DF__CR_INVLIN__INTER__37D0FA5C | Default | Column Constraint | INTERCO_CR_SUBACCNO DEFAULT 0 |
| DF__CR_INVLIN__JOBNO__276EDEB3 | Default | Column Constraint | JOBNO DEFAULT 0 |
| DF__CR_INVLIN__KITSE__0FB81C0A | Default | Column Constraint | KITSEQNO DEFAULT -1 |
| DF__CR_INVLIN__LINET__0EC3F7D1 | Default | Column Constraint | LINETYPE DEFAULT -1 |
| DF__CR_INVLIN__LINET__1CDC41A7 | Default | Column Constraint | LINETOTAL_TAX DEFAULT 0 |
| DF__CR_INVLIN__LINKE__10AC4043 | Default | Column Constraint | LINKED_QTY DEFAULT 0 |
| DF__CR_INVLIN__LINKE__4F9D96F5 | Default | Column Constraint | LINKEDSTATUS DEFAULT N |
| DF__CR_INVLIN__LOCAT__58F12BAE | Default | Column Constraint | LOCATION DEFAULT 1 |
| DF__CR_INVLIN__QUANT__55209ACA | Default | Column Constraint | QUANTITY DEFAULT 0 |
| DF__CR_INVLIN__SHOWL__4EA972BC | Default | Column Constraint | SHOWLINE DEFAULT Y |
| DF__CR_INVLIN__UNITP__5614BF03 | Default | Column Constraint | UNITPRICE DEFAULT 0 |
| DF__CR_INVLIN__UPDAT__027D5126 | Default | Column Constraint | UPDATE_STOCK DEFAULT N |
| NARRATIVES_CR_INVLINES | Foreign Key | Table Constraint | FOREIGN KEY (NARRATIVE_SEQNO) REFERENCES NARRATIVES(SEQNO) |
| PK__CR_INVLINES__4F67C174 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
| STOCK_ITEMS_CR_INVLINES | Foreign Key | Table Constraint | FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE) |
| TAX_RATES_CR_INVLINES | Foreign Key | Table Constraint | FOREIGN KEY (TAXRATE_NO) REFERENCES TAX_RATES(SEQNO) |
Triggers:
| Trigger name | Code |
| CRINVLINESID | CREATE TRIGGER [dbo].[CRINVLINESID] ON [dbo].[CR_INVLINES] FOR INSERT AS
BEGIN
SET NOCOUNT ON
UPDATE [dbo].[CR_INVLINES] SET CRINVLINEID = INSERTED.SEQNO
FROM [dbo].[CR_INVLINES]
INNER JOIN INSERTED ON ([dbo].[CR_INVLINES].SEQNO = INSERTED.SEQNO)
WHERE INSERTED.CRINVLINEID IS NULL OR INSERTED.CRINVLINEID = 0
SET NOCOUNT OFF
END
|
| CR_INVLINES_POST | CREATE TRIGGER [dbo].[CR_INVLINES_POST]
ON [dbo].[CR_INVLINES]
FOR INSERT
AS
BEGIN
/* Exonet created trigger from DBUpdate */
SET NOCOUNT ON
DECLARE @ASTOCKTRANSCODE VARCHAR(23),
@AREF2 VARCHAR(30), @ATRANSTYPE INT, @AGLPOSTED CHAR(1), @APOSTTOGL CHAR(1), @APOSTLOOKUPTOGL CHAR(1),
@ISBOMLINKED CHAR(1), @AQUANTITY FLOAT,
@AUNITPRICE FLOAT, @ASUPPLIERPRICE FLOAT, @AUNITCOST FLOAT,
@UNITPRICE FLOAT, @UNITCOST FLOAT, @HIDDEN_COST FLOAT,
@HIDDEN_SELL FLOAT, @QUANTITY FLOAT, @KITCODE VARCHAR(23), @KITSEQNO INT,
@STOCKCODE VARCHAR(23), @JOBNO INT, @LINKED_STOCKCODE VARCHAR(23),
@SEQNO INT, @HDR_SEQNO INT, @BATCHCODE VARCHAR(20), @INVNO VARCHAR(20), @ACCNO INT, @LOCATION INT,
@LINKEDSTATUS CHAR(1), @DISCOUNT FLOAT, @EXCHRATE FLOAT, @BOMTYPE CHAR(1),
@LINETYPE INT, @UPDATE_STOCK CHAR(1), @SESSION_ID INT
, @AGE INT
, @PERIOD_SEQNO INT
IF ISNULL((SELECT TOP 1 SESSION_ID FROM CR_TRANS C JOIN INSERTED I ON C.SEQNO = I.HDR_SEQNO), -1) = -1
BEGIN
EXECUTE GEN_ID 'CR_TRANS', 'SESSION_ID', 'Y', @ID=@SESSION_ID OUTPUT
UPDATE C
SET SESSION_ID = @SESSION_ID
FROM CR_TRANS C
JOIN INSERTED I ON C.SEQNO = I.HDR_SEQNO
END
ELSE
BEGIN
SELECT @SESSION_ID = (SELECT TOP 1 SESSION_ID FROM CR_TRANS C JOIN INSERTED I ON C.SEQNO = I.HDR_SEQNO)
END
SET @AGE = 0
SELECT @PERIOD_SEQNO = SEQNO FROM PERIOD_STATUS WHERE LEDGER='S' AND AGE = @AGE
DECLARE INSERTED_INVCURSOR CURSOR LOCAL FOR
SELECT UNITPRICE, QUANTITY, KITCODE, KITSEQNO, STOCKCODE,
JOBNO, LINKED_STOCKCODE, SEQNO, HDR_SEQNO, BATCHCODE, INVNO, ACCNO, LOCATION, LINKEDSTATUS,
DISCOUNT, EXCHRATE, BOMTYPE, LINETYPE, UPDATE_STOCK
FROM INSERTED
OPEN INSERTED_INVCURSOR
FETCH NEXT FROM INSERTED_INVCURSOR
INTO @UNITPRICE, @QUANTITY, @KITCODE, @KITSEQNO, @STOCKCODE,
@JOBNO, @LINKED_STOCKCODE, @SEQNO, @HDR_SEQNO, @BATCHCODE, @INVNO, @ACCNO, @LOCATION, @LINKEDSTATUS,
@DISCOUNT, @EXCHRATE, @BOMTYPE, @LINETYPE, @UPDATE_STOCK
WHILE @@FETCH_STATUS = 0
BEGIN
SET @AUNITPRICE = @UNITPRICE * (1 - @DISCOUNT / 100)
SET @ASUPPLIERPRICE = @AUNITPRICE
IF (@STOCKCODE IS NOT NULL) AND (@STOCKCODE<>'')
BEGIN
IF (@UPDATE_STOCK = 'Y')
BEGIN
SET @ISBOMLINKED = 'N'
IF (@BOMTYPE = 'L')
SET @ISBOMLINKED = 'Y'
SET @ATRANSTYPE = 1
SET @AGLPOSTED = 'N'
SET @APOSTTOGL = 'Y'
SET @APOSTLOOKUPTOGL = 'N'
SET @AREF2 = 'CR_INVOICE'
SET @AQUANTITY = @QUANTITY
/* FIND STOCKCODE TO POST TO STOCKTRANS */
SET @ASTOCKTRANSCODE = @LINKED_STOCKCODE
IF (((@ASTOCKTRANSCODE <> '') AND (@LINETYPE = 0)) OR (@ISBOMLINKED = 'Y'))
SET @ASTOCKTRANSCODE = @STOCKCODE
IF (@ASTOCKTRANSCODE = '')
SET @ASTOCKTRANSCODE = @STOCKCODE
/* FIND LOCAL CURRENCY DISCOUNTED UNIT PRICE EXCL TAX TO USE FOR STOCK_TRANS */
IF (@EXCHRATE <> 0)
SET @AUNITPRICE = @AUNITPRICE / @EXCHRATE
INSERT INTO STOCK_TRANS
(TRANSTYPE, STOCKCODE, ACCNO, REF1, REF2, QUANTITY, UNITPRICE, LOCATION, FROM_LEDGER, FROM_HDR, BATCHCODE,
JOBNO, LINE_SEQNO, PLU, KITCODE, KITSEQNO, POST_TO_GL, UNITCOST, GLPOSTED, POST_LOOKUP_TO_GL, SESSION_ID
, PERIOD_SEQNO
) VALUES (@ATRANSTYPE, @ASTOCKTRANSCODE, @ACCNO, @INVNO, @AREF2, @AQUANTITY,
@AUNITPRICE, @LOCATION, 'c', @HDR_SEQNO, @BATCHCODE, @JOBNO, @SEQNO,
@STOCKCODE, @KITCODE, @KITSEQNO, @APOSTTOGL, @AUNITPRICE, @AGLPOSTED, @APOSTLOOKUPTOGL, @SESSION_ID
, @PERIOD_SEQNO
)
/* SET UPDATE_STOCK FIELD ON CR_INVLINE TO MARK LINE AS TRIGGERED */
UPDATE [dbo].[CR_INVLINES]
SET UPDATE_STOCK='T'
WHERE SEQNO = @SEQNO
END
/* UPDATE STOCK TABLE AND SET SUPPLIER COST */
UPDATE STOCK_ITEMS
SET SUPPLIERCOST = @ASUPPLIERPRICE
WHERE (STOCKCODE=@STOCKCODE) AND (SUPPLIERNO=@ACCNO)
/* UPDATE SUPPLIER_STOCK_ITEMS TABLE AND SET LATESTCOST */
UPDATE SUPPLIER_STOCK_ITEMS
SET LATESTCOST = @ASUPPLIERPRICE
WHERE (STOCKCODE=@STOCKCODE)AND (ACCNO=@ACCNO)
END
FETCH NEXT FROM INSERTED_INVCURSOR
INTO @UNITPRICE, @QUANTITY, @KITCODE, @KITSEQNO, @STOCKCODE,
@JOBNO, @LINKED_STOCKCODE, @SEQNO, @HDR_SEQNO, @BATCHCODE, @INVNO, @ACCNO, @LOCATION, @LINKEDSTATUS,
@DISCOUNT, @EXCHRATE, @BOMTYPE, @LINETYPE, @UPDATE_STOCK
END
CLOSE INSERTED_INVCURSOR
DEALLOCATE INSERTED_INVCURSOR
SET NOCOUNT OFF
END
|
Entity: CR_INVLINES_SERIALS
| Comment | This table contains details of serial numbers on Creditor Invoice lines. |
| Primary key columns | SEQNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique ID number for the line. |
| SERIALNO | No | VARCHAR(50) | No | The serial number. |
| INVLINEID | No | INTEGER | No | The associated Creditors Invoice line - links to CR_INVLINES.CRINVLINEID. |
| POSTTIME | No | DATETIME | No | The date and time when the line was saved to the database. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CR_INVLINES_CR_INVLINES_SERIALS | Non Identifying | CR_INVLINES | CR_INVLINES_SERIALS | Zero Or More |
Indexes:
| Name | Index columns | Index type |
| CR_INVLINES_SERIALS_INDEX | SERIALNO ASC, INVLINEID ASC | UNIQUE |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| CR_INVLINES_CR_INVLINES_SERIALS | Foreign Key | Table Constraint | FOREIGN KEY (INVLINEID) REFERENCES CR_INVLINES(CRINVLINEID) |
| DF__CR_INVLIN__POSTT__575EB44E | Default | Column Constraint | POSTTIME DEFAULT getdate() |
| PK__CR_INVLINES_SERI__566A9015 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
Entity: CR_LISTS
| Comment | This table defines the members of the Account Lists used by the Creditors Payment Processor. |
| Primary key columns | LIST_NO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| LIST_NO | Yes | INTEGER | Yes | The ID number of the Account List that this line applies to (CR_LIST_NAMES.LIST_NO). |
| ACCNO | No | INTEGER | Yes | The ID number of the Creditor account. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CR_ACCS_CR_LISTS | Non Identifying | CR_ACCS | CR_LISTS | Zero Or More |
| CR_LISTS_CR_LIST_NAME | Identifying | CR_LISTS | CR_LIST_NAME | Zero Or More |
Indexes:
| Name | Index columns | Index type |
| CR_LISTS_INDEX | LIST_NO ASC, ACCNO ASC | UNIQUE |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (ACCNO) |
| Not Null | Column Constraint | NOT NULL (LIST_NO) |
| CR_ACCS_CR_LISTS | Foreign Key | Table Constraint | FOREIGN KEY (ACCNO) REFERENCES CR_ACCS(ACCNO) |
| PK__CR_LISTS__40058253 | Primary Key | Table Constraint | PRIMARY KEY (LIST_NO) |
Entity: CR_LIST_NAME
| Comment | This table defines the Account Lists used by the Creditors Payment Processor. |
| Primary key columns | LIST_NO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| LIST_NO | Yes | INTEGER | Yes | A unique ID number for the list. |
| LIST_NAME | No | VARCHAR(50) | No | The name of the list. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CR_LISTS_CR_LIST_NAME | Identifying | CR_LISTS | CR_LIST_NAME | Zero Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (LIST_NO) |
| CR_LISTS_CR_LIST_NAME | Foreign Key | Table Constraint | FOREIGN KEY (LIST_NO) REFERENCES CR_LISTS(LIST_NO) |
| PK__CR_LIST_NAME__40F9A68C | Primary Key | Table Constraint | PRIMARY KEY (LIST_NO) |
Entity: CR_PRICES
| Comment | This table contains information on Creditor price rules. |
| Primary key columns | SEQNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique ID number for the price record. |
| ACCNO | No | INTEGER | No | The Creditor account that the price rule is defined for. Has the value -1 if the price rule belongs to a generic price policy. |
| STOCKCODE | No | VARCHAR(23) | No | If the price rule is defined for an individual Stock item, this field stores the item's stockcode. |
| STOCKPRICEGROUP | No | INTEGER | No | If the price rule is defined against a price group rather than an individual product, this field stores the Stock price group. |
| PRICE | No | FLOAT(53) | No | The substitute price to be applied to the stockcode/group. |
| DISCOUNT | No | FLOAT(53) | No | The percentage discount to be applied, as per a price rule. |
| STARTDATE | No | DATETIME | No | Date from which the price rule is active. |
| STOPDATE | No | DATETIME | No | Date to which the price rule remains active. |
| MINQTY | No | FLOAT(53) | No | Minimum quantity for the price rule to apply. |
| ISACTIVE | No | CHAR(1) | No | This field is set to 'Y' if the pricing rule is active. |
| NARRATIVE_SEQNO | No | INTEGER | No | Not used. |
| FIXED | No | CHAR(1) | Yes | This field contains 'Y' if the rule is a fixed price rule. |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (FIXED) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| DF__CR_PRICES__FIXED__64997F32 | Default | Column Constraint | FIXED DEFAULT N |
| DF__CR_PRICES__ISACT__769833DC | Default | Column Constraint | ISACTIVE DEFAULT Y |
| PK__CR_PRICES__75A40FA3 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
Entity: CR_TRANS
| Comment | This table contains header information for all Creditor transactions. |
| Primary key columns | SEQNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique transaction ID. |
| POSTTIME | No | DATETIME | No | The actual date/time the transaction was posted at. |
| TRANSDATE | No | DATETIME | No | The date/time mentioned in the transaction itself. |
| ACCNO | No | INTEGER | No | The ID number of the Creditor account against which the transaction was generated. If the transaction is against a branch account, this field is set to the head account's ACCNO. |
| TRANSTYPE | No | INTEGER | No | 1 = Postive amount is Invoice, Negative amount is Credit; 4 = Negative amount is Payment, Positive amount is Refund |
| INVNO | No | VARCHAR(20) | No | The invoice number. |
| REF1 | No | VARCHAR(20) | No | For payments, this field contains the payment type, e.g. CASH, CHEQUE. For invoices, this field contains whatever was entered into the Order no field. |
| REF2 | No | VARCHAR(20) | No | For payments, this field contains the payment reference/cheque number. For invoices, this field contains the ID number of the job selected for the Job no field. |
| REF3 | No | VARCHAR(30) | No | For payments, this field contains the text 'PAYMENT'. For invoices, this field contains the text 'Invoice'. |
| NAME | No | VARCHAR(70) | No | The ID number and name of the Creditor account against which the transaction was generated. |
| SUBTOTAL | No | FLOAT(53) | No | The tax-exclusive amount, in the currency of the Creditor account. |
| TAXTOTAL | No | FLOAT(53) | No | The tax total in the currency of the Creditor account. |
| TAXINC | No | CHAR(1) | No | Y = tax inclusive, N = tax exclusive. |
| ANALYSIS | No | VARCHAR(12) | No | For payments, this field holds the payment type. |
| ALLOCATEDBAL | No | FLOAT(53) | No | The amount that has been allocated. |
| ALLOCATED | No | CHAR(1) | No | 0 = Unallocated or partially allocated. 1 = Allocated in the current period. 2 = Allocated in any prior period. |
| ALLOCAGE | No | INTEGER | No | The age in which the transaction was allocated. |
| SALESNO | No | INTEGER | No | The ID number of the salesperson assigned to the transaction. |
| GLPOSTED | No | CHAR(1) | No | Y = the transaction has been posted to GL. N = the transaction has not been posted to GL. |
| GLCODE | No | INTEGER | No | For payments, this field contains the GL code of the bank account. |
| DUEDATE | No | DATETIME | No | The payment due date. |
| BRANCH_ACCNO | No | INTEGER | No | Only used in head account/branch account scenarios. This field contains the account number of the branch account. |
| CONTACT_SEQNO | No | INTEGER | No | The SEQNO of the Contact assigned to this transaction. |
| CURRENCYNO | No | INTEGER | No | The ID of the currency being used for the transaction. |
| EXCHRATE | No | FLOAT(53) | No | The exchange rate being used for the transaction, as determined by the currency in use. |
| GLSUBCODE | No | INTEGER | No | For payments, this field contains the GL subcode of the bank account. |
| BRANCHNO | No | INTEGER | No | The branch ID number. |
| PO_SEQNO | No | INTEGER | No | For invoices generated from Purchase Orders, this field holds the SEQNO of the Purchase Order. |
| TAXRATE | No | FLOAT(53) | No | The tax rate used by the transaction, e.g. 12.5 = 12.5%. |
| TAXRATE_NO | No | INTEGER | No | The ID number of the tax rate used by the transaction. |
| PREV_PERIOD_OPEN | No | FLOAT(53) | No | Balance as at the start of the previous period. |
| PAYSTATUS | No | INTEGER | No | The pay status of the transaction. Will be one of: -1 = HELD 0 = NOT RELEASED 1 = RELEASED |
| AUTHORISED | No | CHAR(1) | No | If this field contains 'Y', the transaction was authorised |
| AUTHORISEDBY | No | INTEGER | No | The ID number of the staff member who authorised the transaction. |
| AUTH_DATE | No | DATETIME | No | The time and date when the transaction was authorised. |
| UNREALISED_GAINS_GL_BATCH | No | INTEGER | Yes | The batch number for the unrealised gains journal that the transaction is a part of. Reset to zero when the journal is reversed. |
| TAXRETCODE | No | VARCHAR(15) | No | The tax return code for the GST return that this transaction applies to. |
| N_TOTVENDISC | No | FLOAT(53) | No | The total vendor discount. |
| RELEASEDAMT | No | FLOAT(53) | No | The released amount (applies to the Creditors Payment Processor). |
| PURCH_ACCNO | No | INTEGER | No | If the transaction is against a branch account, this field holds the account number for the branch account and the ACCNO field holds the account number of the head account. In all other cases, this field and the ACCNO field always have same number. |
| RECEIPTNO | No | INTEGER | No | If the transaction is sourced from Inwards Goods, this field contains the Inwards Goods receipt ID. |
| N_TOTVENDISC_EXCLTAX | No | FLOAT(53) | No | The total vendor discount, excluding tax. |
| GLBATCHNO | No | INTEGER | No | The batch number for the journals that have sourced from this transaction. |
| TOAGEDBAL | No | INTEGER | No | Only applicable in case of balance brought forward accounts payments and adjustments. Used in the trigger POST_CR_TRANS to refresh the aged balances on the account. 0 = current 1 = 1 months 2 = 2 months 3 = 3 months 4 = from oldest |
| NARRATIVE_SEQNO | No | INTEGER | No | ID number of the narrative attached to the transaction. |
| IMAGE_URL | No | VARCHAR(80) | No | URL of the image. |
| MANUAL_ROUNDING | No | FLOAT(53) | Yes | This field contains the manual rounding amount (if any). |
| AMOUNT | No | AS | No | The amount of the transaction, including tax, in the currency of the Creditor account. |
| PREV_PERIOD_CLOSE | No | FLOAT(53) | No | Balance as at the end of the previous period. |
| TXID | No | VARBINARY(256) | No | A unique transaction ID number, populated by the trigger TRG_TXID_CR_TRANS. Used to separate native transactions inserted by EXO Business from other transactions. |
| PTNO | No | INTEGER | No | The payment number. |
| SESSION_ID | No | INTEGER | No | The session ID number. |
| PERIOD_SEQNO | No | INTEGER | Yes | ID number of the period that the transaction is in. |
| AGE_STAMP | No | INTEGER | No | This is a historical field that has been superseded by the AGE calculated field. |
| AGE | No | AS | No | The transaction's age. This is calculated based on PERIOD_SEQNO. |
| BANKNO | No | INTEGER | No | The Bank ID number (entered via payment entry). |
| PP_BATCHREF | No | VARCHAR(20) | No | This field contains the Creditors Payment Processor batch reference. |
| CITP | No | CHAR(1) | Yes | Australian companies only. This field is set to 'Y' if the transaction should be tracked for reporting to the ATO on the "Taxable payments annual report". |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CONTACTS_CR_TRANS | Non Identifying | CONTACTS | CR_TRANS | Zero Or More |
| CR_ACCS_CR_TRANS | Non Identifying | CR_ACCS | CR_TRANS | Zero Or More |
| CR_TRANS_CR_ALLOCATIONS | Non Identifying | CR_TRANS | CR_ALLOCATIONS | Zero Or More |
| CR_TRANS_CR_INVLINES | Non Identifying | CR_TRANS | CR_INVLINES | Zero Or More |
| CURRENCIES_CR_TRANS | Non Identifying | CURRENCIES | CR_TRANS | Zero Or More |
| GLACCS_CR_TRANS | Non Identifying | GLACCS | CR_TRANS | Zero Or More |
| NARRATIVES_CR_TRANS | Identifying | NARRATIVES | CR_TRANS | Zero Or More |
| PERIOD_STATUS_CR_TRANS | Non Identifying | PERIOD_STATUS | CR_TRANS | Zero Or More |
| PURCHORD_HDR_CR_TRANS | Non Identifying | PURCHORD_HDR | CR_TRANS | Zero Or More |
| TAX_RATES_CR_TRANS | Non Identifying | TAX_RATES | CR_TRANS | Zero Or More |
Indexes:
| Name | Index columns | Index type |
| CR_TRANS_ACCNO | ACCNO ASC | |
| CR_TRANS_ALLOCATED | ALLOCATED ASC | |
| CR_TRANS_INVNO | INVNO ASC | |
| CR_TRANS_PERIODSEQNO_INDEX | PERIOD_SEQNO ASC | |
| CR_TRANS_PERIOD_SEQNO | PERIOD_SEQNO ASC | |
| CR_TRANS_TRANSDATE | TRANSDATE ASC | |
| CR_TRANS_TRANSTYPE | TRANSTYPE ASC | |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (CITP) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| Not Null | Column Constraint | NOT NULL (PERIOD_SEQNO) |
| Not Null | Column Constraint | NOT NULL (MANUAL_ROUNDING) |
| Not Null | Column Constraint | NOT NULL (UNREALISED_GAINS_GL_BATCH) |
| CONTACTS_CR_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (CONTACT_SEQNO) REFERENCES CONTACTS(SEQNO) |
| CR_ACCS_CR_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (ACCNO) REFERENCES CR_ACCS(ACCNO) |
| CURRENCIES_CR_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (CURRENCYNO) REFERENCES CURRENCIES(CURRENCYNO) |
| DF_CR_TRANS_RELEASEDAMT | Default | Column Constraint | RELEASEDAMT DEFAULT 0 |
| DF__CR_TRANS__ACCNO__60924D76 | Default | Column Constraint | ACCNO DEFAULT 0 |
| DF__CR_TRANS__AGE_ST__4500D848 | Default | Column Constraint | AGE_STAMP DEFAULT -1 |
| DF__CR_TRANS__ALLOCA__6B0FDBE9 | Default | Column Constraint | ALLOCATEDBAL DEFAULT 0 |
| DF__CR_TRANS__ALLOCA__6C040022 | Default | Column Constraint | ALLOCATED DEFAULT 0 |
| DF__CR_TRANS__ALLOCA__6CF8245B | Default | Column Constraint | ALLOCAGE DEFAULT 0 |
| DF__CR_TRANS__AUTHOR__3572E547 | Default | Column Constraint | AUTHORISED DEFAULT N |
| DF__CR_TRANS__BRANCH__33008CF0 | Default | Column Constraint | BRANCHNO DEFAULT 0 |
| DF__CR_TRANS__CITP__70E025DD | Default | Column Constraint | CITP DEFAULT Y |
| DF__CR_TRANS__CURREN__349EBC9F | Default | Column Constraint | CURRENCYNO DEFAULT 0 |
| DF__CR_TRANS__EXCHRA__3592E0D8 | Default | Column Constraint | EXCHRATE DEFAULT 1 |
| DF__CR_TRANS__GLPOST__6FD49106 | Default | Column Constraint | GLPOSTED DEFAULT N |
| DF__CR_TRANS__GLSUBC__320C68B7 | Default | Column Constraint | GLSUBCODE DEFAULT 0 |
| DF__CR_TRANS__MANUAL__4DD54A14 | Default | Column Constraint | MANUAL_ROUNDING DEFAULT 0 |
| DF__CR_TRANS__N_TOTV__2E1BDC42 | Default | Column Constraint | N_TOTVENDISC DEFAULT 0 |
| DF__CR_TRANS__N_TOTV__5540965B | Default | Column Constraint | N_TOTVENDISC_EXCLTAX DEFAULT 0 |
| DF__CR_TRANS__PAYSTA__347EC10E | Default | Column Constraint | PAYSTATUS DEFAULT 0 |
| DF__CR_TRANS__PERIOD__440CB40F | Default | Column Constraint | PERIOD_SEQNO DEFAULT 0 |
| DF__CR_TRANS__POSTTI__5EAA0504 | Default | Column Constraint | POSTTIME DEFAULT getdate() |
| DF__CR_TRANS__PO_SEQ__0E8E2250 | Default | Column Constraint | PO_SEQNO DEFAULT 0 |
| DF__CR_TRANS__PREV_P__2B2A60FE | Default | Column Constraint | PREV_PERIOD_OPEN DEFAULT 0 |
| DF__CR_TRANS__PREV_P__7E78816F | Default | Column Constraint | PREV_PERIOD_CLOSE DEFAULT 0 |
| DF__CR_TRANS__PTNO__3746EA77 | Default | Column Constraint | PTNO DEFAULT -1 |
| DF__CR_TRANS__RECEIP__422DC1E7 | Default | Column Constraint | RECEIPTNO DEFAULT 0 |
| DF__CR_TRANS__SALESN__6EE06CCD | Default | Column Constraint | SALESNO DEFAULT 0 |
| DF__CR_TRANS__SESSIO__32E2280D | Default | Column Constraint | SESSION_ID DEFAULT -1 |
| DF__CR_TRANS__SUBTOT__673F4B05 | Default | Column Constraint | SUBTOTAL DEFAULT 0 |
| DF__CR_TRANS__TAXINC__69279377 | Default | Column Constraint | TAXINC DEFAULT N |
| DF__CR_TRANS__TAXRAT__19FFD4FC | Default | Column Constraint | TAXRATE DEFAULT 0 |
| DF__CR_TRANS__TAXRAT__1AF3F935 | Default | Column Constraint | TAXRATE_NO DEFAULT 0 |
| DF__CR_TRANS__TAXTOT__68336F3E | Default | Column Constraint | TAXTOTAL DEFAULT 0 |
| DF__CR_TRANS__TRANSD__5F9E293D | Default | Column Constraint | TRANSDATE DEFAULT getdate() |
| DF__CR_TRANS__TRANST__618671AF | Default | Column Constraint | TRANSTYPE DEFAULT 0 |
| DF__CR_TRANS__UNREAL__1D66518C | Default | Column Constraint | UNREALISED_GAINS_GL_BATCH DEFAULT 0 |
| GLACCS_CR_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (GLCODE) REFERENCES GLACCS(ACCNO) |
| NARRATIVES_CR_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (SEQNO) REFERENCES NARRATIVES(SEQNO) |
| PERIOD_STATUS_CR_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (PERIOD_SEQNO) REFERENCES PERIOD_STATUS(SEQNO) |
| PK__CR_TRANS__5DB5E0CB | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
| PURCHORD_HDR_CR_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (PO_SEQNO) REFERENCES PURCHORD_HDR(SEQNO) |
| TAX_RATES_CR_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (TAXRATE_NO) REFERENCES TAX_RATES(SEQNO) |
Triggers:
| Trigger name | Code |
| AUTHORISE_TRANS | CREATE TRIGGER [dbo].[AUTHORISE_TRANS]
ON [dbo].[CR_TRANS]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON
UPDATE [dbo].[CR_TRANS]
SET
AUTHORISED = 'Y',
AUTHORISEDBY=0,
AUTH_DATE=GETDATE()
WHERE SEQNO IN
(SELECT I.SEQNO
FROM INSERTED I JOIN CR_ACCS A ON (I.ACCNO = A.ACCNO)
WHERE A.AUTO_AUTH_AMT > I.AMOUNT
AND I.TRANSTYPE = 1)
SET NOCOUNT OFF
END
|
| POST_CR_TRANS | CREATE TRIGGER [dbo].[POST_CR_TRANS]
ON [dbo].[CR_TRANS]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON
DECLARE @ACCNO INTEGER, @BRANCH_ACCNO INTEGER, @SUBTOTAL FLOAT, @TAXTOTAL FLOAT,
@AGE INTEGER, @TRANSTYPE INTEGER, @TOAGEDBAL INTEGER, @PERIODSTHISYEAR INTEGER, @PERIODSINYEAR INTEGER,
@MONTHVAL FLOAT, @LASTMONTH FLOAT, @YEARVAL FLOAT, @LASTYEAR FLOAT,
@AMOUNT FLOAT, @AGEDBAL0 FLOAT, @AGEDBAL1 FLOAT, @AGEDBAL2 FLOAT, @AGEDBAL3 FLOAT,
@PRIOR_AGEDBAL0 FLOAT, @PRIOR_AGEDBAL1 FLOAT, @PRIOR_AGEDBAL2 FLOAT, @PRIOR_AGEDBAL3 FLOAT,
@TURNOVER_ACCNO INTEGER, @OPENITEM CHAR(1)
SELECT @PERIODSTHISYEAR = PERIODSTHISYEAR FROM GL_CONTROL
SELECT @PERIODSINYEAR = COUNT(SEQNO) FROM PERIODS_DEFN WHERE SEQNO > 0
DECLARE INSERTED_CURSOR CURSOR
FOR SELECT ACCNO, BRANCH_ACCNO, SUBTOTAL, TAXTOTAL, AMOUNT, AGE, TRANSTYPE, TOAGEDBAL
FROM INSERTED
OPEN INSERTED_CURSOR
FETCH NEXT FROM INSERTED_CURSOR
INTO @ACCNO, @BRANCH_ACCNO, @SUBTOTAL, @TAXTOTAL, @AMOUNT, @AGE, @TRANSTYPE, @TOAGEDBAL
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @OPENITEM = OPENITEM FROM CR_ACCS WHERE ACCNO = @ACCNO
SET @MONTHVAL = 0
SET @LASTMONTH = 0
SET @YEARVAL = 0
SET @LASTYEAR = 0
SET @AGEDBAL0 = 0
SET @AGEDBAL1 = 0
SET @AGEDBAL2 = 0
SET @AGEDBAL3 = 0
SET @PRIOR_AGEDBAL0 = 0
SET @PRIOR_AGEDBAL1 = 0
SET @PRIOR_AGEDBAL2 = 0
SET @PRIOR_AGEDBAL3 = 0
/* Set Turnover variables */
IF (@TRANSTYPE = 1)
BEGIN
IF (@AGE = 0)
SET @MONTHVAL = @SUBTOTAL
ELSE IF (@AGE = 1)
SET @LASTMONTH = @SUBTOTAL
IF ((@AGE+1) <= @PERIODSTHISYEAR)
SET @YEARVAL = @SUBTOTAL
ELSE IF ((@AGE+1) <= (@PERIODSTHISYEAR+@PERIODSINYEAR))
SET @LASTYEAR = @SUBTOTAL
END
/* Set Aged Balance variables */
IF ((NOT (@OPENITEM = 'Y')) AND (@TOAGEDBAL IS NOT NULL)) /* set according to Age selected */
EXECUTE CR_ACCS_BALFWD_BAL_DELTAS @ACCNO, @TOAGEDBAL, @AMOUNT,
@AGEDBAL0 OUTPUT, @AGEDBAL1 OUTPUT, @AGEDBAL2 OUTPUT, @AGEDBAL3 OUTPUT,
@PRIOR_AGEDBAL0 OUTPUT, @PRIOR_AGEDBAL1 OUTPUT, @PRIOR_AGEDBAL2 OUTPUT
ELSE /* set as usual */
BEGIN
IF (@AGE = 0)
SET @AGEDBAL0 = @AMOUNT
ELSE IF (@AGE = 1)
BEGIN
SET @AGEDBAL1 = @AMOUNT
SET @PRIOR_AGEDBAL0 = @AGEDBAL1
END
ELSE IF (@AGE = 2)
BEGIN
SET @AGEDBAL2 = @AMOUNT
SET @PRIOR_AGEDBAL1 = @AGEDBAL2
END
ELSE
BEGIN
SET @AGEDBAL3 = @AMOUNT
IF (@AGE = 3)
SET @PRIOR_AGEDBAL2 = @AGEDBAL3
ELSE
SET @PRIOR_AGEDBAL3 = @AGEDBAL3
END
END
/* Determine which A/c to set Turnover field values in */
IF (@BRANCH_ACCNO > 0) /* If there is a Branch A/c */
SET @TURNOVER_ACCNO = @BRANCH_ACCNO
ELSE
SET @TURNOVER_ACCNO = @ACCNO
/* Set Turnover field values */
UPDATE CR_ACCS SET
MONTHVAL = MONTHVAL + @MONTHVAL,
LASTMONTH = LASTMONTH + @LASTMONTH,
YEARVAL = YEARVAL + @YEARVAL,
LASTYEAR = LASTYEAR + @LASTYEAR
WHERE
ACCNO = @TURNOVER_ACCNO
/* Set Aged Balance field values */
UPDATE CR_ACCS SET
AGEDBAL0 = AGEDBAL0 + @AGEDBAL0,
AGEDBAL1 = AGEDBAL1 + @AGEDBAL1,
AGEDBAL2 = AGEDBAL2 + @AGEDBAL2,
AGEDBAL3 = AGEDBAL3 + @AGEDBAL3,
PRIOR_AGEDBAL0 = PRIOR_AGEDBAL0 + @PRIOR_AGEDBAL0,
PRIOR_AGEDBAL1 = PRIOR_AGEDBAL1 + @PRIOR_AGEDBAL1,
PRIOR_AGEDBAL2 = PRIOR_AGEDBAL2 + @PRIOR_AGEDBAL2,
PRIOR_AGEDBAL3 = PRIOR_AGEDBAL3 + @PRIOR_AGEDBAL3
WHERE
ACCNO = @ACCNO
FETCH NEXT FROM INSERTED_CURSOR
INTO @ACCNO, @BRANCH_ACCNO, @SUBTOTAL, @TAXTOTAL, @AMOUNT, @AGE, @TRANSTYPE, @TOAGEDBAL
END
CLOSE INSERTED_CURSOR
DEALLOCATE INSERTED_CURSOR
SET NOCOUNT OFF
END
|
| SET_CR_TRANS_PREV_PERIOD_OPEN | CREATE TRIGGER [dbo].[SET_CR_TRANS_PREV_PERIOD_OPEN]
ON [dbo].[CR_TRANS]
FOR INSERT
AS
DECLARE @NEW_SEQNO INTEGER,
@NEW_AGE INTEGER,
@NEW_AMOUNT FLOAT
BEGIN
SET NOCOUNT ON
SELECT @NEW_SEQNO=SEQNO, @NEW_AGE=AGE, @NEW_AMOUNT=AMOUNT FROM INSERTED
IF (@NEW_AGE > 0)
BEGIN
IF (@NEW_AGE>=2)
UPDATE [dbo].[CR_TRANS] SET ALLOCAGE=@NEW_AGE,
PREV_PERIOD_OPEN = @NEW_AMOUNT - ISNULL((SELECT SUM(AMOUNT) FROM CR_ALLOCATIONS WHERE TRANS_SEQNO=@NEW_SEQNO AND AGE>=2), 0),
PREV_PERIOD_CLOSE = @NEW_AMOUNT - ISNULL((SELECT SUM(AMOUNT) FROM CR_ALLOCATIONS WHERE TRANS_SEQNO=@NEW_SEQNO AND AGE>=1), 0)
WHERE SEQNO = @NEW_SEQNO
ELSE
IF (@NEW_AGE=1)
UPDATE [dbo].[CR_TRANS] SET ALLOCAGE=@NEW_AGE,
PREV_PERIOD_OPEN=0,
PREV_PERIOD_CLOSE = @NEW_AMOUNT - ISNULL((SELECT SUM(AMOUNT) FROM CR_ALLOCATIONS WHERE TRANS_SEQNO=@NEW_SEQNO AND AGE>=1), 0)
WHERE SEQNO = @NEW_SEQNO
END
SET NOCOUNT OFF
END
|
Entity: CURRENCIES
| Comment | This table contains details of all currencies used in the EXO Business system. |
| Primary key columns | CURRENCYNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| CURRENCYNO | Yes | INTEGER | Yes | A unique ID code for the currency. |
| CURRCODE | No | VARCHAR(3) | No | The three-letter currency code, e.g. AUD, NZD. |
| CURRNAME | No | VARCHAR(30) | No | The descriptive name for the currency. |
| BUYRATE | No | FLOAT(53) | No | The buy rate (for purchases). |
| SELLRATE | No | FLOAT(53) | No | The sell rate (for sales). |
| CURRSYMBOL | No | VARCHAR(5) | No | The symbol for the currency, e.g. $, £. |
| AVERAGE_RATE | No | FLOAT(53) | No | This field relates to the Intercompany module. |
| CLOSING_RATE | No | FLOAT(53) | No | This field relates to the Intercompany module. |
| RE_MOVEMENTS | No | FLOAT(53) | No | This field relates to the Intercompany module. |
| ALERTPC | No | INTEGER | Yes | The % Change Alert for the currency - when editing currencies, the user will be alerted if the currency is changed by a percentage greater than this. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CURRENCIES_CR_ALLOCATIONS | Non Identifying | CURRENCIES | CR_ALLOCATIONS | Zero Or More |
| CURRENCIES_CR_INVLINES | Non Identifying | CURRENCIES | CR_INVLINES | Zero Or More |
| CURRENCIES_CR_TRANS | Non Identifying | CURRENCIES | CR_TRANS | Zero Or More |
| CURRENCIES_DR_ALLOCATIONS | Non Identifying | CURRENCIES | DR_ALLOCATIONS | Zero Or More |
| CURRENCIES_DR_INVLINES | Non Identifying | CURRENCIES | DR_INVLINES | Zero Or More |
| CURRENCIES_DR_TRANS | Non Identifying | CURRENCIES | DR_TRANS | Zero Or More |
| CURRENCIES_GLACCS | Non Identifying | CURRENCIES | GLACCS | Zero Or More |
| CURRENCIES_JOBCOST_LINES | Non Identifying | CURRENCIES | JOBCOST_LINES | Zero Or More |
| CURRENCIES_JOB_CONTRACT_BILLINGS | Non Identifying | CURRENCIES | JOB_CONTRACT_BILLINGS | Zero Or More |
| CURRENCIES_JOB_TRANSACTIONS | Non Identifying | CURRENCIES | JOB_TRANSACTIONS | Zero Or More |
| CURRENCIES_OPPORTUNITY_QUOTE | Non Identifying | CURRENCIES | OPPORTUNITY_QUOTE | Zero Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (ALERTPC) |
| Not Null | Column Constraint | NOT NULL (CURRENCYNO) |
| DF__CURRENCIE__ALERT__25BE0270 | Default | Column Constraint | ALERTPC DEFAULT 5 |
| DF__CURRENCIE__AVERA__17642ACA | Default | Column Constraint | AVERAGE_RATE DEFAULT 0 |
| DF__CURRENCIE__BUYRA__2CFD9AD7 | Default | Column Constraint | BUYRATE DEFAULT 0 |
| DF__CURRENCIE__CLOSI__18584F03 | Default | Column Constraint | CLOSING_RATE DEFAULT 0 |
| DF__CURRENCIE__RE_MO__2B6B2377 | Default | Column Constraint | RE_MOVEMENTS DEFAULT 0 |
| DF__CURRENCIE__SELLR__2DF1BF10 | Default | Column Constraint | SELLRATE DEFAULT 0 |
| PK__CURRENCIE__CURRE__2C09769E | Primary Key | Table Constraint | PRIMARY KEY (CURRENCYNO) |
Triggers:
| Trigger name | Code |
| CURRENCY_RATE_CHANGE | CREATE TRIGGER [dbo].[CURRENCY_RATE_CHANGE] ON [dbo].[CURRENCIES]
FOR INSERT, UPDATE AS
BEGIN
DECLARE @DR_PERIOD INT
DECLARE @CR_PERIOD INT
DECLARE @STK_PERIOD INT
DECLARE @GL_PERIOD INT
SELECT @DR_PERIOD=SEQNO FROM PERIOD_STATUS WHERE AGE=0 AND LEDGER ='D'
SELECT @CR_PERIOD=SEQNO FROM PERIOD_STATUS WHERE AGE=0 AND LEDGER ='C'
SELECT @STK_PERIOD=SEQNO FROM PERIOD_STATUS WHERE AGE=0 AND LEDGER ='S'
SELECT @GL_PERIOD=SEQNO FROM PERIOD_STATUS WHERE AGE=0 AND LEDGER ='G'
SET NOCOUNT ON
INSERT INTO CURRENCY_RATECHANGES
(CURRENCYNO, CURRCODE, NEWBUYRATE, NEWSELLRATE, DR_PERIOD, CR_PERIOD, STK_PERIOD, GL_PERIOD )
SELECT INSERTED.CURRENCYNO, INSERTED.CURRCODE,
INSERTED.BUYRATE, INSERTED.SELLRATE, @DR_PERIOD, @CR_PERIOD, @STK_PERIOD, @GL_PERIOD
FROM INSERTED
SET NOCOUNT OFF
END
|
Entity: GLACCS
| Comment | This table contains details of all General Ledger accounts. |
| Primary key columns | ACCNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| ACCNO | Yes | INTEGER | Yes | The unique GL account number. |
| NAME | No | VARCHAR(40) | No | The account name. |
| DRCR | No | CHAR(1) | No | This field is set to 'C' for Credit accounts or 'D' for Debit accounts. |
| SECTION | No | INTEGER | No | This field is set to 0 for Profit and loss accounts or 1 for Balance sheet accounts. |
| OPENINGBAL | No | FLOAT(53) | No | The account's opening balance. |
| BALANCE | No | FLOAT(53) | No | The account's balance. |
| USESUBCODES | No | CHAR(1) | No | Not used. |
| REPORTCODE | No | VARCHAR(15) | No | The report code used by the GL account. |
| TAXSTATUS | No | INTEGER | No | The Override GST rate specified for the GL account. A value of -1 corresponds to the AUTO SELECT RATE option. |
| CURRENCYNO | No | INTEGER | No | The ID number of the currency that the GL account is in. |
| ISACTIVE | No | CHAR(1) | No | This field contains 'Y' if the GL account is active. |
| ACCGROUP | No | INTEGER | No | The GL account's Account Group. |
| ALLOWJOURNAL | No | CHAR(1) | No | This field is set to 'Y' if the GL account allows journals. |
| LAST_UPDATED | No | DATETIME | No | The date and time when the GL account was last updated. |
| BALSHEETACCTOT | No | AS | No | The balance sheet total for the account. |
| BALANCE_SHEET_TYPE | No | INTEGER | Yes | For balance sheet accounts, this field indicates the balance sheet type. Will be one of:
-1 = Not a balance sheet account
0 = Asset
1 = Liability
2 = Equity |
| NOTES | No | VARCHAR(4096) | No | Any notes on the GL account. |
| EXCLUDE_FROM_REVAL | No | CHAR(1) | No | If this field is set to 'Y', the account is excluded from revaluation. |
| PRIVATE_ACC | No | CHAR(1) | Yes | This field is set to 'Y' if the GL account is a private account. |
| CREATED_DATE | No | DATETIME | No | The date and time that the GL account was created. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CURRENCIES_GLACCS | Non Identifying | CURRENCIES | GLACCS | Zero Or More |
| GLACCS_CR_TRANS | Non Identifying | GLACCS | CR_TRANS | Zero Or More |
| GLACCS_DR_TRANS | Non Identifying | GLACCS | DR_TRANS | Zero Or More |
| GLACCS_JOBCOST_HDR | Non Identifying | GLACCS | JOBCOST_HDR | Zero Or More |
| GLACCS_JOBCOST_HDR_SALES | Non Identifying | GLACCS | JOBCOST_HDR | Zero Or More |
| GLACCS_JOBCOST_LINES | Non Identifying | GLACCS | JOBCOST_LINES | Zero Or More |
| GLACCS_JOB_CONTRACT_BILLINGS | Non Identifying | GLACCS | JOB_CONTRACT_BILLINGS | Zero Or More |
| GLACCS_JOB_CONTRACT_BILLINGS_SUB | Non Identifying | GLACCS | JOB_CONTRACT_BILLINGS | Zero Or More |
| GLACCS_JOB_TRANSACTIONS | Non Identifying | GLACCS | JOB_TRANSACTIONS | Zero Or More |
| GLACCS_OPPORTUNITY_QUOTE | Non Identifying | GLACCS | OPPORTUNITY_QUOTE | Zero Or More |
| GLACCS_STOCK_GROUPS | Non Identifying | GLACCS | STOCK_GROUPS | Zero Or More |
| GLACCS_STOCK_GROUPS_COS | Non Identifying | GLACCS | STOCK_GROUPS | Zero Or More |
| GLACCS_STOCK_GROUPS_P | Non Identifying | GLACCS | STOCK_GROUPS | Zero Or More |
| GLACCS_STOCK_ITEMS | Non Identifying | GLACCS | STOCK_ITEMS | Zero Or More |
| GLACCS_STOCK_ITEMS_COS | Non Identifying | GLACCS | STOCK_ITEMS | Zero Or More |
| GLACCS_STOCK_TRANS | Non Identifying | GLACCS | STOCK_TRANS | Zero Or More |
| GL_ACCS_STOCKITEMS_PO | Non Identifying | GLACCS | STOCK_ITEMS | Zero Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (PRIVATE_ACC) |
| Not Null | Column Constraint | NOT NULL (ACCNO) |
| Not Null | Column Constraint | NOT NULL (BALANCE_SHEET_TYPE) |
| CURRENCIES_GLACCS | Foreign Key | Table Constraint | FOREIGN KEY (CURRENCYNO) REFERENCES CURRENCIES(CURRENCYNO) |
| DF__GLACCS__ACCGROUP__613C58EC | Default | Column Constraint | ACCGROUP DEFAULT 0 |
| DF__GLACCS__ALLOWJOU__2882FE7D | Default | Column Constraint | ALLOWJOURNAL DEFAULT Y |
| DF__GLACCS__BALANCE__3E082B48 | Default | Column Constraint | BALANCE DEFAULT 0 |
| DF__GLACCS__BALANCE___40113AA2 | Default | Column Constraint | BALANCE_SHEET_TYPE DEFAULT -1 |
| DF__GLACCS__CURRENCY__3A228BCB | Default | Column Constraint | CURRENCYNO DEFAULT 0 |
| DF__GLACCS__DRCR__3B2BBE9D | Default | Column Constraint | DRCR DEFAULT D |
| DF__GLACCS__EXCLUDE___23CA01AF | Default | Column Constraint | EXCLUDE_FROM_REVAL DEFAULT N |
| DF__GLACCS__ISACTIVE__58A712EB | Default | Column Constraint | ISACTIVE DEFAULT Y |
| DF__GLACCS__OPENINGB__3D14070F | Default | Column Constraint | OPENINGBAL DEFAULT 0 |
| DF__GLACCS__PRIVATE___13697CB4 | Default | Column Constraint | PRIVATE_ACC DEFAULT N |
| DF__GLACCS__SECTION__3C1FE2D6 | Default | Column Constraint | SECTION DEFAULT 0 |
| DF__GLACCS__TAXSTATU__51700577 | Default | Column Constraint | TAXSTATUS DEFAULT 0 |
| DF__GLACCS__USESUBCO__3FF073BA | Default | Column Constraint | USESUBCODES DEFAULT N |
| PK__GLACCS__3A379A64 | Primary Key | Table Constraint | PRIMARY KEY (ACCNO) |
Entity: NARRATIVES
| Comment | This table contains the narratives attached to transactions. |
| Primary key columns | SEQNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique ID number for the narrative. |
| NARRATIVE | No | VARCHAR(4096) | No | The text of the narrative. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| NARRATIVES_CR_INVLINES | Non Identifying | NARRATIVES | CR_INVLINES | Zero Or More |
| NARRATIVES_CR_TRANS | Identifying | NARRATIVES | CR_TRANS | Zero Or More |
| NARRATIVES_DR_INVLINES | Non Identifying | NARRATIVES | DR_INVLINES | Zero Or More |
| NARRATIVES_DR_TRANS | Non Identifying | NARRATIVES | DR_TRANS | Zero Or More |
| NARRATIVES_JOBCOST_LINES | Non Identifying | NARRATIVES | JOBCOST_LINES | Zero Or More |
| NARRATIVES_JOB_CONTRACT_BILLINGS | Non Identifying | NARRATIVES | JOB_CONTRACT_BILLINGS | Zero Or More |
| NARRATIVES_JOB_TRANSACTIONS | Non Identifying | NARRATIVES | JOB_TRANSACTIONS | Zero Or More |
| NARRATIVES_OPPORTUNITY_QUOTE | Non Identifying | NARRATIVES | OPPORTUNITY_QUOTE | Zero Or More |
| NARRATIVES_PURCHORD_HDR | Non Identifying | NARRATIVES | PURCHORD_HDR | Zero Or More |
| NARRATIVES_SALESORD_HDR | Non Identifying | NARRATIVES | SALESORD_HDR | Zero Or More |
| NARRATIVES_STOCK_REQUESTLINES | Non Identifying | NARRATIVES | STOCK_REQUESTLINES | Zero Or More |
| NARRATIVES_STOCK_REQUESTS | Non Identifying | NARRATIVES | STOCK_REQUESTS | Zero Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| PK__NARRATIVES__2E31B632 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
Entity: 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: PURCHORD_HDR
| Comment | This table contains header information for all Purchase Orders. |
| Primary key columns | SEQNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique ID number for the Purchase Order. |
| STATUS | No | INTEGER | No | The status of the Purchase Order. Will be one of:
0 = Not Processed
1 = Partly Processed
2 = Fully Processed |
| ACCNO | No | INTEGER | No | The ID number of the Creditor account that the Purchase Order was created for. |
| ORDERDATE | No | DATETIME | No | The date and time when the Purchase Order was created. |
| DUEDATE | No | DATETIME | No | The date and time when the Purchase Order is due. |
| REFERENCE | No | VARCHAR(20) | No | An additional reference code for the Purchase Order. |
| ADDRESS1 | No | VARCHAR(30) | No | Delivery address line 1. |
| ADDRESS2 | No | VARCHAR(30) | No | Delivery address line 2. |
| ADDRESS3 | No | VARCHAR(30) | No | Delivery address line 3. |
| ADDRESS4 | No | VARCHAR(30) | No | Delivery address line 4. |
| INSTRUCTIONS | No | VARCHAR(255) | No | This field is set via the On-Demand Purchase Orders function of the EXO Distribution Advantage module. |
| SUBTOTAL | No | FLOAT(53) | No | The subtotal amount on the Purchase Order. |
| TAXTOTAL | No | FLOAT(53) | No | The total tax amount on the Purchase Order. |
| SALESNO | No | INTEGER | No | The ID number of the staff member assigned to the Purchase Order. |
| CONTACT_SEQNO | No | INTEGER | No | The ID number of the Contact assigned to the Purchase Order. |
| CURRENCYNO | No | INTEGER | No | The ID number of the currency that the Purchase Order is in. |
| EXCHRATE | No | FLOAT(53) | No | The exchange rate for the currency that the Purchase Order is in. |
| TAXINC | No | CHAR(1) | No | Set to 'Y' if the Purchase Order is tax-inclusive. |
| LAST_UPDATED | No | DATETIME | No | The date and time when the Purchase Order was last updated. |
| BRANCHNO | No | INTEGER | No | The Branch number on the Purchase Order. |
| AUTH_STAFFNO | No | INTEGER | No | The ID number of the staff member who authorised the Purchase Order. |
| AUTH_DATE | No | DATETIME | No | The date and time when the Purchase Order was authorised. |
| ORDTOTAL | No | AS | No | The total amount for the Sales Order. |
| ISCONFIRMED | No | CHAR(1) | No | If this field is set to 'Y', this means that all lines in the Purchase Order have been confirmed, i.e. the supplier/creditor has acknowledged the order. |
| LEADTIMEUSED | No | INTEGER | No | Specifies which of the Lead Times defined for the Creditor the Purchase Order uses. Will be one of:
0 = Primary Lead Time
1 = Secondary Lead Time |
| ADDRESS5 | No | VARCHAR(30) | No | Delivery address line 5. |
| ADDRESS6 | No | VARCHAR(30) | No | Delivery address line 6. |
| NARRATIVE_SEQNO | No | INTEGER | No | The ID number of the narrative attached to the Sales Order header. |
| HSTATUS | No | INTEGER | No | For On-Demand Purchase orders, this field contains the line status. Comes from the profile setting 'B2B new purchase line status'. |
| DEFLOCNO | No | INTEGER | No | The default location that stock is being supplied from. |
| PROCESSFINALISATION | No | INTEGER | No | Will be one of:
0 = Normal
1 = Deleted
2 = Forced Fully Processed |
| TXID | No | VARBINARY(256) | No | The Transaction ID assigned to the Purchase Order - this applies when using a payment gateway. |
| SO_SOURCE_REF | No | VARCHAR(20) | No | If the Purchase Order was generated from a Sales Order, the Sales Order's ID number is recorded here. |
| CREATE_DATE | No | DATETIME | No | The date and time when the Purchase Order was created. |
| ACTIVATION_DATE | No | DATETIME | No | The date and time when the Purchase Order was activated. |
| FINALISATION_DATE | No | DATETIME | No | The date and time when the Purchase Order was finalised. |
| LOCALVALUE | No | AS | No | The value of the Purchase Order in the local currency. |
| CAMPAIGN_WAVE_SEQNO | No | INTEGER | No | If the Purchase Order is associated with a campaign wave (EXO CRM), its ID number is recorded here. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CR_ACCS_PURCHORD_HDR | Non Identifying | CR_ACCS | PURCHORD_HDR | Zero Or More |
| NARRATIVES_PURCHORD_HDR | Non Identifying | NARRATIVES | PURCHORD_HDR | Zero Or More |
| PURCHORD_HDR_CR_TRANS | Non Identifying | PURCHORD_HDR | CR_TRANS | Zero Or More |
| PURCHORD_HDR_INWARDS_GOODS_LINES | Non Identifying | PURCHORD_HDR | INWARDS_GOODS_LINES | Zero Or More |
| PURCHORD_HDR_TASKS | Non Identifying | PURCHORD_HDR | TASKS | Zero Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| CR_ACCS_PURCHORD_HDR | Foreign Key | Table Constraint | FOREIGN KEY (ACCNO) REFERENCES CR_ACCS(ACCNO) |
| DF_PURCHORD_HDR_ACCNO | Default | Column Constraint | ACCNO DEFAULT 0 |
| DF_PURCHORD_HDR_DUEDATE | Default | Column Constraint | DUEDATE DEFAULT getdate() |
| DF_PURCHORD_HDR_ORDERDATE | Default | Column Constraint | ORDERDATE DEFAULT getdate() |
| DF_PURCHORD_HDR_STATUS | Default | Column Constraint | STATUS DEFAULT 0 |
| DF_PURCHORD_HDR_SUBTOTAL | Default | Column Constraint | SUBTOTAL DEFAULT 0 |
| DF_PURCHORD_HDR_TAXTOTAL | Default | Column Constraint | TAXTOTAL DEFAULT 0 |
| DF__PURCHORD___BRANC__3C54ED00 | Default | Column Constraint | BRANCHNO DEFAULT 0 |
| DF__PURCHORD___CAMPA__571566E2 | Default | Column Constraint | CAMPAIGN_WAVE_SEQNO DEFAULT -1 |
| DF__PURCHORD___CURRE__30CE2BBB | Default | Column Constraint | CURRENCYNO DEFAULT 0 |
| DF__PURCHORD___DEFLO__3BB699D9 | Default | Column Constraint | DEFLOCNO DEFAULT 0 |
| DF__PURCHORD___EXCHR__31C24FF4 | Default | Column Constraint | EXCHRATE DEFAULT 1 |
| DF__PURCHORD___HSTAT__4FD2A11F | Default | Column Constraint | HSTATUS DEFAULT 0 |
| DF__PURCHORD___ISCON__74B941B4 | Default | Column Constraint | ISCONFIRMED DEFAULT N |
| DF__PURCHORD___LEADT__76A18A26 | Default | Column Constraint | LEADTIMEUSED DEFAULT 0 |
| DF__PURCHORD___NARRA__496FBC53 | Default | Column Constraint | NARRATIVE_SEQNO DEFAULT -1 |
| DF__PURCHORD___PROCE__3D9EE24B | Default | Column Constraint | PROCESSFINALISATION DEFAULT 0 |
| DF__PURCHORD___SALES__71DCD509 | Default | Column Constraint | SALESNO DEFAULT 0 |
| DF__PURCHORD___TAXIN__5649C92D | Default | Column Constraint | TAXINC DEFAULT N |
| NARRATIVES_PURCHORD_HDR | Foreign Key | Table Constraint | FOREIGN KEY (NARRATIVE_SEQNO) REFERENCES NARRATIVES(SEQNO) |
| PK_PURCHORD_HDR | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
Entity: STAFF
| Comment | This table stores details of all EXO Business staff members. These are the people who are authorised to log in to and use the EXO Business system. |
| Primary key columns | STAFFNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| STAFFNO | Yes | INTEGER | Yes | A unique identifier for the staff record. |
| NAME | No | VARCHAR(30) | No | The staff member's full name. |
| JOBTITLE | No | VARCHAR(30) | No | The staff member's job title. |
| EXTENSION | No | VARCHAR(12) | No | The staff member's telephone extension. |
| PHONE | No | VARCHAR(30) | No | The staff member's work telephone number. |
| HOMEPHONE | No | VARCHAR(30) | No | The staff member's home telephone number. |
| ISACTIVE | No | CHAR(1) | No | This field is set to 'Y' if the staff member is currently active, i.e. if their Active flag is ticked in EXO Business Configurator. |
| APP_PASSWORD | No | VARCHAR(30) | No | The staff member's password, used when logging in to the EXO Business system. Passwords are stored in an encrypted format. |
| MENU_NO | No | INTEGER | No | The ID number of the Menu Definition specified for the staff member. Corresponds to the menu's SEQNO field in the MENU_COLLECTION table. |
| AUTH_AMT | No | FLOAT(53) | No | The staff member's Credit Invoice Authorisation Limit. |
| STOCK_AUTH_AMT | No | FLOAT(53) | No | The staff member's Stock Purchase Order Authorisation Limit. |
| NON_STOCK_AUTH_AMT | No | FLOAT(53) | No | The staff member's Non-Stock Purchase Order Authorisation Limit. |
| SECURITYPROFILEID | No | INTEGER | Yes | The ID number of the staff member's Security profile. Corresponds to the profile's ID field in the PROFILE table. |
| USERPROFILEID | No | INTEGER | Yes | The ID number of the staff member's User profile. Corresponds to the profile's ID field in the PROFILE table. |
| LOGINID | No | VARCHAR(30) | Yes | The staff member's login ID. This is the ID used to log in to the EXO Business system. |
| PASSWORD_CHANGED | No | DATETIME | Yes | The date and time when the staff member's password was last changed. |
| LAST_BAD_LOGIN | No | DATETIME | No | The date and time when the staff member last attempted to log in but failed. |
| BAD_LOGIN_COUNT | No | INTEGER | Yes | The number of times the staff member has attempted to log in but failed. |
| LAST_LOGIN | No | DATETIME | No | The date and time when the staff member last successfully logged in. |
| ACCOUNT_STATUS | No | INTEGER | Yes | The staff member's account status. Will be one of:
0 = Account OK
1 = Account Locked
2 = Password Expired |
| EMAIL_ADDRESS | No | VARCHAR(50) | No | The staff member's email address. |
| DISCOUNTRATE | No | FLOAT(53) | Yes | The staff member's Discount Rate Limit. |
| PAYROLL_ID | No | VARCHAR(15) | No | The staff member's Employee ID (Payroll) number. This field is used when EXO Business is integrating with an external payroll system, e.g. MYOB EXO Payroll. It must contain the employee's corresponding ID number in the payroll system. |
| IS_SUPERVISOR | No | CHAR(1) | Yes | This field is set to 'Y' if the staff member is designated as a supervisor, i.e. if their Has supervisory override authority flag is ticked. |
| NICKNAME | No | VARCHAR(15) | No | A short nickname for the employee. |
| ABSENT | No | CHAR(1) | Yes | This field is set to 'Y' if the staff member is currently absent, i.e. if their Absent flag is ticked in EXO Business Configurator. |
| EMPLOYEE_CODE | No | INTEGER | Yes | This field is used by the payroll integration functionality. |
| SMTP_SEQNO | No | INTEGER | No | Where emails are sent via SMTP using the EXO Business Email Sender, this field contains the ID of the SMTP settings that apply to the staff member (SMTP_ACCOUNT.SEQNO). Set up via the SMTP Server Settings window. |
| HAS_BUDGETS | No | CHAR(1) | No | This field is set to 'Y' if the staff member has been assigned a Sales Team Budget, i.e. if their Has Budget flag is ticked in EXO Business Configurator. |
| REPORTS_TO_STAFFNO | No | INTEGER | No | The ID number of the staff member that this staff member reports to. |
| FACEBOOK_ACCESS_TOKEN | No | VARCHAR(max) | No | If the staff member uses their own Facebook account when using the EXO Business social media functions, their encrypted access token is stored here. |
| LINKEDIN_TOKEN_KEY | No | VARCHAR(max) | No | If the staff member uses their own LinkedIn account when using the EXO Business social media functions, their encrypted access token key is stored here. |
| LINKEDIN_TOKEN_SECRET | No | VARCHAR(max) | No | If the staff member uses their own LinkedIn account when using the EXO Business social media functions, their encrypted access token secret is stored here. |
| TWITTER_TOKEN_KEY | No | VARCHAR(max) | No | If the staff member uses their own Twitter account when using the EXO Business social media functions, their encrypted access token key is stored here. |
| TWITTER_TOKEN_SECRET | No | VARCHAR(max) | No | If the staff member uses their own Twitter account when using the EXO Business social media functions, their encrypted access token secret is stored here. |
| API_ACCESS | No | CHAR(1) | No | This field contains 'Y' if the staff member is authorised to use the EXO API. |
| MOBILE_ACCESS | No | CHAR(1) | Yes | This field contains 'Y' if the staff member is authorised to use the EXO OnTheGo mobile app. |
| BANKFEED_REFRESHTOKEN | No | VARCHAR(500) | No | Encrypted OAuth refresh token used for Bank Feeds. |
| SBR_REFRESHTOKEN | No | VARCHAR(1024) | No | The refresh token used for SBR authentication. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| FK_CRM_BUDGET_EXCLUDED_STAFF_STAFF | Non Identifying | STAFF | CRM_BUDGET_EXCLUDED_STAFF | Zero Or More |
| FK_CRM_BUDGET_LINE_STAFF | Non Identifying | STAFF | CRM_BUDGET_LINE | Zero Or More |
| MENU_COLLECTION_STAFF | Non Identifying | MENU_COLLECTION | STAFF | Zero Or More |
| STAFF_BILLOMAT_TEMP | Non Identifying | STAFF | BILLOMAT_TEMP | Zero Or More |
| STAFF_CAMPAIGN | Non Identifying | STAFF | CAMPAIGN | Zero Or More |
| STAFF_CAMPAIGN_HIST | Non Identifying | STAFF | CAMPAIGN_HIST | Zero Or More |
| STAFF_CONTACTS | Non Identifying | STAFF | CONTACTS | Zero Or More |
| STAFF_CONTACT_LIST | Non Identifying | STAFF | CONTACT_LIST | Zero Or More |
| STAFF_CONTACT_LIST1 | Non Identifying | STAFF | CONTACT_LIST | Zero Or More |
| STAFF_CR_ACCS | Non Identifying | STAFF | CR_ACCS | Zero Or More |
| STAFF_CR_CONT_HIST | Non Identifying | STAFF | CR_CONT_HIST | Zero Or More |
| STAFF_DR_ACCS | Non Identifying | STAFF | DR_ACCS | One Or More |
| STAFF_DR_CONT_HIST | Non Identifying | STAFF | DR_CONT_HIST | One Or More |
| STAFF_DR_TRANS | Non Identifying | STAFF | DR_TRANS | Zero Or More |
| STAFF_JOBCOST_HDR | Non Identifying | STAFF | JOBCOST_HDR | Zero Or More |
| STAFF_JOBCOST_HDR_MGR | Non Identifying | STAFF | JOBCOST_HDR | Zero Or More |
| STAFF_JOBCOST_RESOURCE | Non Identifying | STAFF | JOBCOST_RESOURCE | Zero Or More |
| STAFF_JOB_TIMESHEETS | Non Identifying | STAFF | JOB_TIMESHEETS | Zero Or More |
| STAFF_JOB_TIMESHEET_ALLOWANCE | Non Identifying | STAFF | JOB_TIMESHEET_ALLOWANCE | Zero Or More |
| STAFF_JOB_TRANSACTIONS | Non Identifying | STAFF | JOB_TRANSACTIONS | Zero Or More |
| STAFF_OPPORTUNITY_HIST | Non Identifying | STAFF | OPPORTUNITY_HIST | Zero Or More |
| STAFF_STOCK_REQUESTS | Non Identifying | STAFF | STOCK_REQUESTS | Zero Or More |
| STAFF_STOCK_TRANS_HDR | Non Identifying | STAFF | STOCK_TRANS_HDR | Zero Or More |
| STAFF_TASKS | Non Identifying | STAFF | TASKS | Zero Or More |
| STAFF_TASKS1 | Non Identifying | STAFF | TASKS | Zero Or More |
| STAFF_TASKS2 | Non Identifying | STAFF | TASKS | Zero Or More |
| STAFF_TASKS3 | Non Identifying | STAFF | TASKS | Zero Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (MOBILE_ACCESS) |
| Not Null | Column Constraint | NOT NULL (STAFFNO) |
| Not Null | Column Constraint | NOT NULL (SECURITYPROFILEID) |
| Not Null | Column Constraint | NOT NULL (EMPLOYEE_CODE) |
| Not Null | Column Constraint | NOT NULL (USERPROFILEID) |
| Not Null | Column Constraint | NOT NULL (ABSENT) |
| Not Null | Column Constraint | NOT NULL (LOGINID) |
| Not Null | Column Constraint | NOT NULL (PASSWORD_CHANGED) |
| Not Null | Column Constraint | NOT NULL (IS_SUPERVISOR) |
| Not Null | Column Constraint | NOT NULL (BAD_LOGIN_COUNT) |
| Not Null | Column Constraint | NOT NULL (DISCOUNTRATE) |
| Not Null | Column Constraint | NOT NULL (ACCOUNT_STATUS) |
| DF__STAFF__ABSENT__12CA5E36 | Default | Column Constraint | ABSENT DEFAULT N |
| DF__STAFF__ACCOUNT_S__5CACADF9 | Default | Column Constraint | ACCOUNT_STATUS DEFAULT 0 |
| DF__STAFF__BAD_LOGIN__5BB889C0 | Default | Column Constraint | BAD_LOGIN_COUNT DEFAULT 0 |
| DF__STAFF__DISCOUNTR__2C146396 | Default | Column Constraint | DISCOUNTRATE DEFAULT 0 |
| DF__STAFF__EMPLOYEE___3DB4BC3B | Default | Column Constraint | EMPLOYEE_CODE DEFAULT -1 |
| DF__STAFF__HAS_BUDGE__6855017D | Default | Column Constraint | HAS_BUDGETS DEFAULT N |
| DF__STAFF__IS_SUPERV__24F33012 | Default | Column Constraint | IS_SUPERVISOR DEFAULT N |
| DF__STAFF__MOBILE_AC__38F0D275 | Default | Column Constraint | MOBILE_ACCESS DEFAULT N |
| DF__STAFF__PASSWORD___5AC46587 | Default | Column Constraint | PASSWORD_CHANGED DEFAULT getdate() |
| DF__STAFF__REPORTS_T__694925B6 | Default | Column Constraint | REPORTS_TO_STAFFNO DEFAULT -1 |
| DF__STAFF__SECURITYP__58DC1D15 | Default | Column Constraint | SECURITYPROFILEID DEFAULT 0 |
| DF__STAFF__SMTP_SEQN__6B667852 | Default | Column Constraint | SMTP_SEQNO DEFAULT -1 |
| DF__STAFF__USERPROFI__59D0414E | Default | Column Constraint | USERPROFILEID DEFAULT 0 |
| MENU_COLLECTION_STAFF | Foreign Key | Table Constraint | FOREIGN KEY (MENU_NO) REFERENCES MENU_COLLECTION(SEQNO) |
| PK_STAFF | Primary Key | Table Constraint | PRIMARY KEY (STAFFNO) |
Entity: STOCK_ITEMS
| Comment | This table contains details of all stock items. |
| Primary key columns | STOCKCODE |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| STOCKCODE | Yes | VARCHAR(23) | Yes | A unique ID code for the stock item. |
| DESCRIPTION | No | VARCHAR(40) | No | The stock item's description. |
| STOCKGROUP | No | INTEGER | No | The Primary (Fine) Stock Group that the item belongs to. |
| STATUS | No | CHAR(1) | No | The stock item's status. Will be one of:
S = Stock item
L = Lookup item |
| SELLPRICE1 | No | FLOAT(53) | No | Sell price 1. |
| SELLPRICE2 | No | FLOAT(53) | No | Sell price 2. |
| SELLPRICE3 | No | FLOAT(53) | No | Sell price 3. |
| SELLPRICE4 | No | FLOAT(53) | No | Sell price 4. |
| SELLPRICE5 | No | FLOAT(53) | No | Sell price 5. |
| SELLPRICE6 | No | FLOAT(53) | No | Sell price 6. |
| SELLPRICE7 | No | FLOAT(53) | No | Sell price 7. |
| SELLPRICE8 | No | FLOAT(53) | No | Sell price 8. |
| SELLPRICE9 | No | AS | No | Sell price 9. |
| SELLPRICE10 | No | FLOAT(53) | No | Sell price 10. |
| LATESTCOST | No | FLOAT(53) | No | The stock item's Last Cost Price. |
| AVECOST | No | FLOAT(53) | No | The stock item's Average Cost Price. |
| MINSTOCK | No | FLOAT(53) | No | The minimum default stock level for each location. |
| MAXSTOCK | No | FLOAT(53) | No | The maximum default stock level for each location. |
| SUPPLIERNO | No | INTEGER | No | The ID number (CR_ACCS.ACCNO) of the stock item's main supplier. |
| MONTHUNITS | No | FLOAT(53) | No | Sales totals in terms of quantity for the current month. |
| YEARUNITS | No | FLOAT(53) | No | Year to date sales totals in terms of quantity. |
| LASTYEARUNITS | No | FLOAT(53) | No | Units of stock item sold in for the previous year. |
| MONTHVALUE | No | FLOAT(53) | No | Sales value for the current month. |
| YEARVALUE | No | FLOAT(53) | No | Sales value for the current year. |
| LASTYEARVALUE | No | FLOAT(53) | No | Sales value for last year. |
| BINCODE | No | VARCHAR(12) | No | Bincode for the stock item. |
| DISCOUNTLEVEL | No | INTEGER | No | |
| DEFDAYS | No | INTEGER | No | |
| BARCODE1 | No | VARCHAR(30) | No | Barcode/alternate code 1. |
| BARCODE2 | No | VARCHAR(30) | No | Barcode/alternate code 2. |
| BARCODE3 | No | VARCHAR(30) | No | Barcode/alternate code 3. |
| LASTMONTHVALUE | No | FLOAT(53) | No | Sales value for the previous month for the stock item. |
| LASTMONTHUNITS | No | FLOAT(53) | No | Units of stock item sold in the previous month. |
| SALES_GL_CODE | No | INTEGER | No | The default GL code that sales transactions for this stock item are posted to. |
| PURCH_GL_CODE | No | INTEGER | No | The GL code that purchase transactions for this stock are posted to. |
| WEB_SHOW | No | CHAR(1) | No | |
| ISACTIVE | No | CHAR(1) | No | If this field is set to 'Y', the stock item is active. |
| WEIGHT | No | FLOAT(53) | No | Physical weight of the stock item. |
| CUBIC | No | FLOAT(53) | No | Cubic weight of the item. |
| ALERT | No | VARCHAR(60) | No | Text of the pop-up alert message that appears when the stock item is sold or purchased. |
| NOTES | No | VARCHAR(4096) | No | Any notes for the stock item. |
| PQTY | No | FLOAT(53) | No | |
| PACK | No | VARCHAR(10) | No | Describes the unit of sale for pricing, e.g. EACH or PACK. |
| HAS_SN | No | CHAR(1) | No | This field is set to 'Y' if the stock item is serial number tracked. |
| STDCOST | No | FLOAT(53) | No | The stock item's standard cost. |
| SUPPLIERNO2 | No | INTEGER | No | Alternate supplier for the stock item. |
| SUPPLIERNO3 | No | INTEGER | No | Alternate supplier for the stock item. |
| SALES_GLSUBCODE | No | INTEGER | No | The default GL sub-account code that sales transactions for this stock item are posted to. |
| PURCH_GLSUBCODE | No | INTEGER | No | The default GL sub-account code that purchase transactions for this stock item are posted to. |
| BRANCHNO | No | INTEGER | No | Branch number for the stock item. |
| SALESTAXRATE | No | INTEGER | No | Default GST rate for sales transactions. |
| PURCHTAXRATE | No | INTEGER | No | Default GST rate for purchase transactions. |
| LAST_UPDATED | No | DATETIME | No | Date and time when the stock item record was last updated. |
| UPDATEITEM_CODE | No | VARCHAR(23) | No | Only applies to lookup items. The ID number of the stocked item that maintains the actual stock. |
| UPDATEITEM_QTY | No | FLOAT(53) | No | Only applies to lookup items. Quantity of the stocked item. |
| COS_GL_CODE | No | INTEGER | No | The Cost of Sales account updated in the General Ledger when this stock item is sold. |
| COS_GLSUBCODE | No | INTEGER | No | The Cost of Sales sub-account updated in the General Ledger when the stock item is sold. |
| STOCKPRICEGROUP | No | INTEGER | No | The price group that the stock item belongs to. |
| SUPPLIERCOST | No | FLOAT(53) | Yes | The supplier cost price. |
| ECONORDERQTY | No | FLOAT(53) | No | The economic order quantity (EOQ) for the stock item. |
| LINKED_BILLCODE | No | VARCHAR(23) | No | |
| STOCK_CLASSIFICATION | No | INTEGER | Yes | The stock classification that the item belongs to. |
| STOCKGROUP2 | No | INTEGER | No | The stock item's Secondary (Coarse) Stock Group. |
| TOTALSTOCK | No | FLOAT(53) | Yes | Total units of stock for the item. |
| HAS_BN | No | CHAR(1) | No | This field is set to 'Y' if the stock item is batch tracked. |
| HAS_EXPIRY | No | CHAR(1) | Yes | This field is set to 'Y' if the stock item has an expiry date. This flag is only available in the UI if the "Use expiry dates for batched & serial numbers" profile setting is enabled. |
| EXPIRY_DAYS | No | INTEGER | No | Number of days before expiry of the stock item. |
| DUTY | No | FLOAT(53) | Yes | The duty percentage on the stock item. |
| SERIALNO_TYPE | No | INTEGER | Yes | How the stock item is serialised. Will be one of:
0 = Not Serialised
1 = Fully Tracked (In & Internal & Out)
2 = Creditor & Internal (In & Internal)
3 = Debtor Only (Out only)
4 = Creditor & Debtor (In & Out Only)
5 = Creditor Only (In Only)
6 = Internal & Debtor (Internal & Out only)
7 = Internal Only |
| COSTTYPE | No | INTEGER | Yes | |
| COSTGROUP | No | INTEGER | Yes | |
| LABEL_QTY | No | INTEGER | Yes | |
| IS_DISCOUNTABLE | No | CHAR(1) | Yes | This field is set to 'Y' if the stock item is discountable. |
| RESTRICTED_ITEM | No | CHAR(1) | Yes | This field is set to 'Y' if the stock item is a restricted item. |
| NUMDECIMALS | No | INTEGER | Yes | The number of decimal places that can be entered for the stock item's quantity. |
| COGSMETHOD | No | INTEGER | Yes | |
| DEFAULTWARRANTYNO | No | INTEGER | Yes | The default Warranty No for the stock item. |
| DIMENSIONS | No | INTEGER | Yes | How the dimensions of the stock item are specified. Will be one of:
0=None
1=Length
2=Square
3=Cubic |
| X_SIZEID | No | INTEGER | No | Style size ID of the stock item. Used by the Style, Size Colour functionality. |
| X_COLOURID | No | INTEGER | No | Style colour ID of the stock item. Used by the Style, Size Colour functionality. |
| AUTO_NARRATIVE | No | INTEGER | No | The setting of the stock item's Auto Narrative property. Will be one of:
0=No
1=Yes with Blank
2=Yes with Notes |
| VARIABLECOST | No | CHAR(1) | Yes | The field is set to 'Y for Variable Cost stock items. |
| PRICEQTY | No | FLOAT(53) | No | The stock item's sell price per quantity. |
| PRICEPERKG | No | FLOAT(53) | No | The stock item's sell price per kg. |
| LOOKUP_RECOVERABLE | No | CHAR(1) | No | If this field contains 'Y', the stock item is a recoverable expense in EXO Job Costing. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| CR_ACCS_STOCK_ITEMS | Non Identifying | CR_ACCS | STOCK_ITEMS | Zero Or More |
| FK_CRM_BUDGET_EXCLUDED_STOCK_ITEMS_STOCK_ITEMS | Non Identifying | STOCK_ITEMS | CRM_BUDGET_EXCLUDED_STOCK_ITEMS | Zero Or More |
| FK_CRM_BUDGET_LINE_STOCK_ITEMS | Non Identifying | STOCK_ITEMS | CRM_BUDGET_LINE | Zero Or More |
| GLACCS_STOCK_ITEMS | Non Identifying | GLACCS | STOCK_ITEMS | Zero Or More |
| GLACCS_STOCK_ITEMS_COS | Non Identifying | GLACCS | STOCK_ITEMS | Zero Or More |
| GL_ACCS_STOCKITEMS_PO | Non Identifying | GLACCS | STOCK_ITEMS | Zero Or More |
| STOCK_CLASSIFICATIONS_STOCK_ITEMS | Non Identifying | STOCK_CLASSIFICATIONS | STOCK_ITEMS | Zero Or More |
| STOCK_GROUP2S_STOCK_ITEMS | Non Identifying | STOCK_GROUP2S | STOCK_ITEMS | Zero Or More |
| STOCK_GROUPS_STOCK_ITEMS | Non Identifying | STOCK_GROUPS | STOCK_ITEMS | Zero Or More |
| STOCK_ITEMS_BILLOMAT_LINES | Non Identifying | STOCK_ITEMS | BILLOMAT_LINES | Zero Or More |
| STOCK_ITEMS_BILLOMAT_TEMP | Non Identifying | STOCK_ITEMS | BILLOMAT_TEMP | Zero Or More |
| STOCK_ITEMS_CR_INVLINES | Non Identifying | STOCK_ITEMS | CR_INVLINES | Zero Or More |
| STOCK_ITEMS_DR_INVLINES | Non Identifying | STOCK_ITEMS | DR_INVLINES | Zero Or More |
| STOCK_ITEMS_DR_PRICES | Non Identifying | STOCK_ITEMS | DR_PRICES | Zero Or More |
| STOCK_ITEMS_INWARDS_GOODS_LINES | Non Identifying | STOCK_ITEMS | INWARDS_GOODS_LINES | Zero Or More |
| STOCK_ITEMS_JOBCOST_LINES | Non Identifying | STOCK_ITEMS | JOBCOST_LINES | Zero Or More |
| STOCK_ITEMS_JOBCOST_LINES_DESC | Non Identifying | STOCK_ITEMS | JOBCOST_LINES | Zero Or More |
| STOCK_ITEMS_JOB_CONTRACT_BILLINGS | Non Identifying | STOCK_ITEMS | JOB_CONTRACT_BILLINGS | Zero Or More |
| STOCK_ITEMS_JOB_OUTPUT_ITEMS | Non Identifying | STOCK_ITEMS | JOB_OUTPUT_ITEMS | Zero Or More |
| STOCK_ITEMS_JOB_OUTPUT_ITEMS_DESC | Non Identifying | STOCK_ITEMS | JOB_OUTPUT_ITEMS | Zero Or More |
| STOCK_ITEMS_JOB_TIMESHEETS | Non Identifying | STOCK_ITEMS | JOB_TIMESHEETS | Zero Or More |
| STOCK_ITEMS_JOB_TRANSACTIONS | Non Identifying | STOCK_ITEMS | JOB_TRANSACTIONS | Zero Or More |
| STOCK_ITEMS_JOB_TRANSACTIONS_DESC | Non Identifying | STOCK_ITEMS | JOB_TRANSACTIONS | Zero Or More |
| STOCK_ITEMS_OPPORTUNITY_QUOTE | Non Identifying | STOCK_ITEMS | OPPORTUNITY_QUOTE | Zero Or More |
| STOCK_ITEMS_STOCKREQUIREMENT | Non Identifying | STOCK_ITEMS | STOCKREQUIREMENT | Zero Or More |
| STOCK_ITEMS_STOCKTAKE_TOTALS | Non Identifying | STOCK_ITEMS | STOCKTAKE_TOTALS | Zero Or More |
| STOCK_ITEMS_STOCKTAKE_TOTALS_BN | Non Identifying | STOCK_ITEMS | STOCKTAKE_TOTALS | Zero Or More |
| STOCK_ITEMS_STOCKTAKE_TOTALS_EXPDAYS | Non Identifying | STOCK_ITEMS | STOCKTAKE_TOTALS | Zero Or More |
| STOCK_ITEMS_STOCKTAKE_TOTALS_EXPIRY | Non Identifying | STOCK_ITEMS | STOCKTAKE_TOTALS | Zero Or More |
| STOCK_ITEMS_STOCKTAKE_TOTALS_SERIAL | Non Identifying | STOCK_ITEMS | STOCKTAKE_TOTALS | Zero Or More |
| STOCK_ITEMS_STOCK_LOC_INFO | Identifying | STOCK_ITEMS | STOCK_LOC_INFO | Zero Or More |
| STOCK_ITEMS_STOCK_REQUESTLINES | Non Identifying | STOCK_ITEMS | STOCK_REQUESTLINES | Zero Or More |
| STOCK_ITEMS_STOCK_REQUESTLINES_DESC | Non Identifying | STOCK_ITEMS | STOCK_REQUESTLINES | Zero Or More |
| STOCK_ITEMS_STOCK_SERIALNOS | Non Identifying | STOCK_ITEMS | STOCK_SERIALNOS | Zero Or More |
| STOCK_ITEMS_STOCK_TRANS | Non Identifying | STOCK_ITEMS | STOCK_TRANS | Zero Or More |
| STOCK_ITEMS_STOCK_WEB | Non Identifying | STOCK_ITEMS | STOCK_WEB | Zero Or More |
| STOCK_ITEMS_SUPPLIER_STOCK_ITEMS | Non Identifying | STOCK_ITEMS | SUPPLIER_STOCK_ITEMS | Zero Or More |
| STOCK_PRICEGROUPS_STOCK_ITEMS | Non Identifying | STOCK_PRICEGROUPS | STOCK_ITEMS | Zero Or More |
Indexes:
| Name | Index columns | Index type |
| STOCK_ITEMS_DESC | DESCRIPTION ASC | |
Constraints:
| Name | Type | Level | Constraint |
| Default | Column Constraint | LOOKUP_RECOVERABLE DEFAULT Y |
| Not Null | Column Constraint | NOT NULL (STOCKCODE) |
| Not Null | Column Constraint | NOT NULL (VARIABLECOST) |
| Not Null | Column Constraint | NOT NULL (DIMENSIONS) |
| Not Null | Column Constraint | NOT NULL (DEFAULTWARRANTYNO) |
| Not Null | Column Constraint | NOT NULL (COGSMETHOD) |
| Not Null | Column Constraint | NOT NULL (NUMDECIMALS) |
| Not Null | Column Constraint | NOT NULL (RESTRICTED_ITEM) |
| Not Null | Column Constraint | NOT NULL (IS_DISCOUNTABLE) |
| Not Null | Column Constraint | NOT NULL (LABEL_QTY) |
| Not Null | Column Constraint | NOT NULL (COSTGROUP) |
| Not Null | Column Constraint | NOT NULL (COSTTYPE) |
| Not Null | Column Constraint | NOT NULL (SERIALNO_TYPE) |
| Not Null | Column Constraint | NOT NULL (DUTY) |
| Not Null | Column Constraint | NOT NULL (HAS_EXPIRY) |
| Not Null | Column Constraint | NOT NULL (TOTALSTOCK) |
| Not Null | Column Constraint | NOT NULL (STOCK_CLASSIFICATION) |
| Not Null | Column Constraint | NOT NULL (SUPPLIERCOST) |
| CR_ACCS_STOCK_ITEMS | Foreign Key | Table Constraint | FOREIGN KEY (SUPPLIERNO) REFERENCES CR_ACCS(ACCNO) |
| DF__STOCK_ITEM__DUTY__675F4696 | Default | Column Constraint | DUTY DEFAULT 0 |
| DF__STOCK_ITEM__PQTY__7C255952 | Default | Column Constraint | PQTY DEFAULT 1 |
| DF__STOCK_ITE__AUTO___43ECB87E | Default | Column Constraint | AUTO_NARRATIVE DEFAULT 0 |
| DF__STOCK_ITE__AVECO__18A19C6F | Default | Column Constraint | AVECOST DEFAULT 0 |
| DF__STOCK_ITE__BRANC__5FD33367 | Default | Column Constraint | BRANCHNO DEFAULT 0 |
| DF__STOCK_ITE__COGSM__0BD27C8D | Default | Column Constraint | COGSMETHOD DEFAULT 0 |
| DF__STOCK_ITE__COSTG__50BBD860 | Default | Column Constraint | COSTGROUP DEFAULT 0 |
| DF__STOCK_ITE__COSTT__4FC7B427 | Default | Column Constraint | COSTTYPE DEFAULT 0 |
| DF__STOCK_ITE__COS_G__4D7F7902 | Default | Column Constraint | COS_GL_CODE DEFAULT 0 |
| DF__STOCK_ITE__COS_G__4E739D3B | Default | Column Constraint | COS_GLSUBCODE DEFAULT 0 |
| DF__STOCK_ITE__CUBIC__45C948A1 | Default | Column Constraint | CUBIC DEFAULT 0 |
| DF__STOCK_ITE__DEFAU__6F0133B5 | Default | Column Constraint | DEFAULTWARRANTYNO DEFAULT -1 |
| DF__STOCK_ITE__DEFDA__2D9CB955 | Default | Column Constraint | DEFDAYS DEFAULT 0 |
| DF__STOCK_ITE__DIMEN__3CFFC3CD | Default | Column Constraint | DIMENSIONS DEFAULT 0 |
| DF__STOCK_ITE__DISCO__2CA8951C | Default | Column Constraint | DISCOUNTLEVEL DEFAULT 0 |
| DF__STOCK_ITE__EXPIR__666B225D | Default | Column Constraint | EXPIRY_DAYS DEFAULT 1 |
| DF__STOCK_ITE__HAS_B__526429B0 | Default | Column Constraint | HAS_BN DEFAULT N |
| DF__STOCK_ITE__HAS_E__6576FE24 | Default | Column Constraint | HAS_EXPIRY DEFAULT N |
| DF__STOCK_ITE__HAS_S__7FF5EA36 | Default | Column Constraint | HAS_SN DEFAULT N |
| DF__STOCK_ITE__ISACT__74B941B4 | Default | Column Constraint | ISACTIVE DEFAULT Y |
| DF__STOCK_ITE__IS_DI__3044E72D | Default | Column Constraint | IS_DISCOUNTABLE DEFAULT Y |
| DF__STOCK_ITE__LABEL__6C04CB79 | Default | Column Constraint | LABEL_QTY DEFAULT 1 |
| DF__STOCK_ITE__LASTM__2E90DD8E | Default | Column Constraint | LASTMONTHVALUE DEFAULT 0 |
| DF__STOCK_ITE__LASTM__2F8501C7 | Default | Column Constraint | LASTMONTHUNITS DEFAULT 0 |
| DF__STOCK_ITE__LASTY__28D80438 | Default | Column Constraint | LASTYEARUNITS DEFAULT 0 |
| DF__STOCK_ITE__LASTY__2BB470E3 | Default | Column Constraint | LASTYEARVALUE DEFAULT 0 |
| DF__STOCK_ITE__LATES__17AD7836 | Default | Column Constraint | LATESTCOST DEFAULT 0 |
| DF__STOCK_ITE__MAXST__25077354 | Default | Column Constraint | MAXSTOCK DEFAULT 0 |
| DF__STOCK_ITE__MINST__24134F1B | Default | Column Constraint | MINSTOCK DEFAULT 0 |
| DF__STOCK_ITE__MONTH__26EFBBC6 | Default | Column Constraint | MONTHUNITS DEFAULT 0 |
| DF__STOCK_ITE__MONTH__29CC2871 | Default | Column Constraint | MONTHVALUE DEFAULT 0 |
| DF__STOCK_ITE__NUMDE__0801EBA9 | Default | Column Constraint | NUMDECIMALS DEFAULT -1 |
| DF__STOCK_ITE__PURCH__451F3D2B | Default | Column Constraint | PURCH_GLSUBCODE DEFAULT 0 |
| DF__STOCK_ITE__PURCH__4727812E | Default | Column Constraint | PURCH_GL_CODE DEFAULT 0 |
| DF__STOCK_ITE__PURCH__68687968 | Default | Column Constraint | PURCHTAXRATE DEFAULT -1 |
| DF__STOCK_ITE__RESTR__77CB83E0 | Default | Column Constraint | RESTRICTED_ITEM DEFAULT N |
| DF__STOCK_ITE__SALES__442B18F2 | Default | Column Constraint | SALES_GLSUBCODE DEFAULT 0 |
| DF__STOCK_ITE__SALES__46335CF5 | Default | Column Constraint | SALES_GL_CODE DEFAULT 0 |
| DF__STOCK_ITE__SALES__6774552F | Default | Column Constraint | SALESTAXRATE DEFAULT -1 |
| DF__STOCK_ITE__SELLP__13DCE752 | Default | Column Constraint | SELLPRICE1 DEFAULT 0 |
| DF__STOCK_ITE__SELLP__14D10B8B | Default | Column Constraint | SELLPRICE2 DEFAULT 0 |
| DF__STOCK_ITE__SELLP__15C52FC4 | Default | Column Constraint | SELLPRICE3 DEFAULT 0 |
| DF__STOCK_ITE__SELLP__16B953FD | Default | Column Constraint | SELLPRICE4 DEFAULT 0 |
| DF__STOCK_ITE__SELLP__39987BE6 | Default | Column Constraint | SELLPRICE5 DEFAULT 0 |
| DF__STOCK_ITE__SELLP__3A8CA01F | Default | Column Constraint | SELLPRICE6 DEFAULT 0 |
| DF__STOCK_ITE__SELLP__3B80C458 | Default | Column Constraint | SELLPRICE7 DEFAULT 0 |
| DF__STOCK_ITE__SELLP__3C74E891 | Default | Column Constraint | SELLPRICE8 DEFAULT 0 |
| DF__STOCK_ITE__SELLP__3E5D3103 | Default | Column Constraint | SELLPRICE10 DEFAULT 0 |
| DF__STOCK_ITE__SERIA__1C481021 | Default | Column Constraint | SERIALNO_TYPE DEFAULT 0 |
| DF__STOCK_ITE__STATU__12E8C319 | Default | Column Constraint | STATUS DEFAULT L |
| DF__STOCK_ITE__STDCO__392E6792 | Default | Column Constraint | STDCOST DEFAULT 0 |
| DF__STOCK_ITE__STOCK__11F49EE0 | Default | Column Constraint | STOCKGROUP DEFAULT 0 |
| DF__STOCK_ITE__STOCK__4F87BD05 | Default | Column Constraint | STOCKGROUP2 DEFAULT 0 |
| DF__STOCK_ITE__STOCK__6442E2C9 | Default | Column Constraint | STOCK_CLASSIFICATION DEFAULT 0 |
| DF__STOCK_ITE__STOCK__69279377 | Default | Column Constraint | STOCKPRICEGROUP DEFAULT 0 |
| DF__STOCK_ITE__SUPPL__16B953FD | Default | Column Constraint | SUPPLIERCOST DEFAULT 0 |
| DF__STOCK_ITE__SUPPL__25FB978D | Default | Column Constraint | SUPPLIERNO DEFAULT 0 |
| DF__STOCK_ITE__TOTAL__51700577 | Default | Column Constraint | TOTALSTOCK DEFAULT 0 |
| DF__STOCK_ITE__UPDAT__257187A8 | Default | Column Constraint | UPDATEITEM_QTY DEFAULT 0 |
| DF__STOCK_ITE__VARIA__611DF409 | Default | Column Constraint | VARIABLECOST DEFAULT N |
| DF__STOCK_ITE__WEB_S__44160A59 | Default | Column Constraint | WEB_SHOW DEFAULT N |
| DF__STOCK_ITE__WEIGH__44D52468 | Default | Column Constraint | WEIGHT DEFAULT 0 |
| DF__STOCK_ITE__X_COL__02890975 | Default | Column Constraint | X_COLOURID DEFAULT 0 |
| DF__STOCK_ITE__X_SIZ__0194E53C | Default | Column Constraint | X_SIZEID DEFAULT 0 |
| DF__STOCK_ITE__YEARU__27E3DFFF | Default | Column Constraint | YEARUNITS DEFAULT 0 |
| DF__STOCK_ITE__YEARV__2AC04CAA | Default | Column Constraint | YEARVALUE DEFAULT 0 |
| GLACCS_STOCK_ITEMS | Foreign Key | Table Constraint | FOREIGN KEY (SALES_GL_CODE) REFERENCES GLACCS(ACCNO) |
| GLACCS_STOCK_ITEMS_COS | Foreign Key | Table Constraint | FOREIGN KEY (COS_GL_CODE) REFERENCES GLACCS(ACCNO) |
| GL_ACCS_STOCKITEMS_PO | Foreign Key | Table Constraint | FOREIGN KEY (PURCH_GL_CODE) REFERENCES GLACCS(ACCNO) |
| PK__STOCK_ITEMS__11007AA7 | Primary Key | Table Constraint | PRIMARY KEY (STOCKCODE) |
| STOCK_CLASSIFICATIONS_STOCK_ITEMS | Foreign Key | Table Constraint | FOREIGN KEY (STOCK_CLASSIFICATION) REFERENCES STOCK_CLASSIFICATIONS(CLASSNO) |
| STOCK_GROUP2S_STOCK_ITEMS | Foreign Key | Table Constraint | FOREIGN KEY (STOCKGROUP2) REFERENCES STOCK_GROUP2S(GROUPNO) |
| STOCK_GROUPS_STOCK_ITEMS | Foreign Key | Table Constraint | FOREIGN KEY (STOCKGROUP) REFERENCES STOCK_GROUPS(GROUPNO) |
| STOCK_PRICEGROUPS_STOCK_ITEMS | Foreign Key | Table Constraint | FOREIGN KEY (STOCKPRICEGROUP) REFERENCES STOCK_PRICEGROUPS(GROUPNO) |
Triggers:
| Trigger name | Code |
| AFTER_STOCK_ITEMS_DELETE | CREATE TRIGGER [dbo].[AFTER_STOCK_ITEMS_DELETE] ON [dbo].[STOCK_ITEMS]
FOR DELETE
AS
SET NOCOUNT ON
DELETE FROM STOCK_LOC_INFO WHERE STOCKCODE IN (SELECT STOCKCODE FROM DELETED)
SET NOCOUNT OFF
|
| STOCK_ITEMS_LOC_INFO | CREATE TRIGGER [dbo].[STOCK_ITEMS_LOC_INFO] ON [dbo].[STOCK_ITEMS]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO STOCK_LOC_INFO
(STOCKCODE, LOCATION, BINCODE, MINSTOCK, MAXSTOCK, QTY)
SELECT I.STOCKCODE, SL.LOCNO, I.BINCODE, I.MINSTOCK, I.MAXSTOCK, 0
FROM STOCK_LOCATIONS SL, INSERTED I
SET NOCOUNT OFF
END
|
| STOCK_ITEMS_ONEOFF | CREATE TRIGGER [dbo].[STOCK_ITEMS_ONEOFF] ON [dbo].[STOCK_ITEMS]
FOR UPDATE
AS
BEGIN
DECLARE @UPDATEDLINES INT
SET @UPDATEDLINES=@@ROWCOUNT
SET NOCOUNT ON
IF (@UPDATEDLINES > 0) AND NOT UPDATE(ISACTIVE)
BEGIN
UPDATE S
SET ISACTIVE = 'N'
FROM [dbo].[STOCK_ITEMS] S
JOIN INSERTED I ON S.STOCKCODE = I.STOCKCODE
JOIN STOCK_TRANS ST ON I.STOCKCODE = ST.STOCKCODE
WHERE ((S.STOCK_CLASSIFICATION = 100) OR (S.STOCK_CLASSIFICATION = 70))
AND (S.TOTALSTOCK <= 0)
END
SET NOCOUNT OFF
END
|
Entity: TAX_RATES
| Comment | This table contains all tax rates used by the system. |
| Primary key columns | SEQNO |
Attributes:
| Column name | Primary key | Data type | Not NULL | Comment |
| SEQNO | Yes | INTEGER | Yes | A unique ID number for the tax rate. |
| RATE | No | FLOAT(53) | No | The percentage taxation rate. |
| NAME | No | VARCHAR(30) | No | The full name of the taxation rate. |
| SHORTNAME | No | VARCHAR(6) | No | A short name for the taxation rate. |
| BASE | No | FLOAT(53) | No | |
| GLACC | No | INTEGER | No | The GL account used for the tax rate (if it is not using the default control account). |
| GLSUBACC | No | INTEGER | No | The GL subaccount used for the tax rate (if it is not using the default control subaccount). |
| KEY_POINT | No | VARCHAR(5) | No | The tax return key point that applies to the tax rate. |
Relationships:
| Name | Relationship type | Parent | Child | Cardinality |
| TAX_RATES_CR_INVLINES | Non Identifying | TAX_RATES | CR_INVLINES | Zero Or More |
| TAX_RATES_CR_TRANS | Non Identifying | TAX_RATES | CR_TRANS | Zero Or More |
| TAX_RATES_DR_INVLINES | Non Identifying | TAX_RATES | DR_INVLINES | Zero Or More |
| TAX_RATES_DR_TRANS | Non Identifying | TAX_RATES | DR_TRANS | Zero Or More |
| TAX_RATES_JOBCOST_LINES | Non Identifying | TAX_RATES | JOBCOST_LINES | Zero Or More |
| TAX_RATES_JOB_TRANSACTIONS | Non Identifying | TAX_RATES | JOB_TRANSACTIONS | Zero Or More |
| TAX_RATES_OPPORTUNITY_QUOTE | Non Identifying | TAX_RATES | OPPORTUNITY_QUOTE | Zero Or More |
Constraints:
| Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| DF__TAX_RATES__BASE__1E5A75C5 | Default | Column Constraint | BASE DEFAULT 0 |
| DF__TAX_RATES__GLACC__1F4E99FE | Default | Column Constraint | GLACC DEFAULT 0 |
| DF__TAX_RATES__GLSUB__2042BE37 | Default | Column Constraint | GLSUBACC DEFAULT 0 |
| DF__TAX_RATES__RATE__668030F6 | Default | Column Constraint | RATE DEFAULT 0 |
| PK__TAX_RATES__658C0CBD | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
Triggers:
| Trigger name | Code |
| BEFORE_TAX_RATES_INSERT | CREATE TRIGGER [dbo].[BEFORE_TAX_RATES_INSERT] ON [dbo].[TAX_RATES]
FOR INSERT
AS
SET NOCOUNT ON
DECLARE @NEW_SHORTNAME VARCHAR(6)
DECLARE @NEW_SEQNO INTEGER
DECLARE @NEW_RATE FLOAT
SELECT
@NEW_SHORTNAME=SHORTNAME,
@NEW_RATE=RATE,
@NEW_SEQNO=SEQNO
FROM
INSERTED
IF (@NEW_SHORTNAME IS NULL) OR (@NEW_SHORTNAME = '')
UPDATE [dbo].[TAX_RATES] SET SHORTNAME = CAST(@NEW_RATE AS VARCHAR(5))+'%' WHERE SEQNO=@NEW_SEQNO
SET NOCOUNT OFF
RETURN
|
|