Entity: BILLOMAT_HDR
Comment | This table contains header information for Bills of Materials (BOMs). |
Primary key columns | BILLCODE |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
BILLCODE | Yes | VARCHAR(23) | Yes | The BOM's unique ID code. |
DESCRIPTION | No | VARCHAR(40) | No | A descriptive name for the BOM. |
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. |
COSTPRICE | No | FLOAT(53) | No | Cost price for the BOM. |
PRICING_MODE | No | INTEGER | No | How the output item is priced. 0 = Priced by total. 1 = Priced by components. |
OUTPUT_CODE | No | VARCHAR(23) | No | Stock code of the BOM's output item. |
BATCH_QTY | No | FLOAT(53) | No | The BOM's Batch Quantity. |
HIDE_LINES | No | CHAR(1) | No | This field is set to 'Y' if the BOM's "Hide lines on reports" option is ticked. |
WASTAGE | No | FLOAT(53) | No | The BOM's Overhead Allocation percentage. |
NOTES | No | VARCHAR(2048) | No | Any notes recorded for the BOM. |
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 | FLOAT(53) | No | Sell price 9. |
SELLPRICE10 | No | FLOAT(53) | No | Sell price 10. |
ISACTIVE | No | CHAR(1) | No | This field contains 'Y' if the BOM is active; otherwise it is inactive. |
KIT_TYPE | No | INTEGER | Yes | |
BOMTYPE | No | CHAR(1) | Yes | The type of BOM. Will be one of: K = Kit B = Build O = Order Template |
AUTOBUILD | No | CHAR(1) | Yes | Applies to Build type BOMs. This field is set to 'Y' if the BOM's "AutoBuild" option is ticked. This gives the option of building an out-of-stock BOM immediately instead of placing it on backorder. |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
BILLOMAT_HDR_BILLOMAT_LINES | Non Identifying | BILLOMAT_HDR | BILLOMAT_LINES | Zero Or More |
BILLOMAT_HDR_BILLOMAT_TEMP | Non Identifying | BILLOMAT_HDR | BILLOMAT_TEMP | Zero Or More |
BILLOMAT_HDR_JOBCOST_LINES | Non Identifying | BILLOMAT_HDR | JOBCOST_LINES | Zero Or More |
BILLOMAT_HDR_JOB_TRANSACTIONS | Non Identifying | BILLOMAT_HDR | JOB_TRANSACTIONS | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (AUTOBUILD) |
| Not Null | Column Constraint | NOT NULL (BILLCODE) |
| Not Null | Column Constraint | NOT NULL (BOMTYPE) |
| Not Null | Column Constraint | NOT NULL (KIT_TYPE) |
DF__BILLOMAT___AUTOB__43B7AE54 | Default | Column Constraint | AUTOBUILD DEFAULT N |
DF__BILLOMAT___BATCH__0C06BB60 | Default | Column Constraint | BATCH_QTY DEFAULT 1 |
DF__BILLOMAT___BOMTY__48F09966 | Default | Column Constraint | BOMTYPE DEFAULT K |
DF__BILLOMAT___COSTP__0A1E72EE | Default | Column Constraint | COSTPRICE DEFAULT 0 |
DF__BILLOMAT___HIDE___18027DF1 | Default | Column Constraint | HIDE_LINES DEFAULT N |
DF__BILLOMAT___ISACT__2883C9D4 | Default | Column Constraint | ISACTIVE DEFAULT Y |
DF__BILLOMAT___KIT_T__47FC752D | Default | Column Constraint | KIT_TYPE DEFAULT 0 |
DF__BILLOMAT___PRICI__0B129727 | Default | Column Constraint | PRICING_MODE DEFAULT 0 |
DF__BILLOMAT___SELLP__0504B816 | Default | Column Constraint | SELLPRICE5 DEFAULT 0 |
DF__BILLOMAT___SELLP__05F8DC4F | Default | Column Constraint | SELLPRICE6 DEFAULT 0 |
DF__BILLOMAT___SELLP__064DE20A | Default | Column Constraint | SELLPRICE1 DEFAULT 0 |
DF__BILLOMAT___SELLP__06ED0088 | Default | Column Constraint | SELLPRICE7 DEFAULT 0 |
DF__BILLOMAT___SELLP__07420643 | Default | Column Constraint | SELLPRICE2 DEFAULT 0 |
DF__BILLOMAT___SELLP__07E124C1 | Default | Column Constraint | SELLPRICE8 DEFAULT 0 |
DF__BILLOMAT___SELLP__08362A7C | Default | Column Constraint | SELLPRICE3 DEFAULT 0 |
DF__BILLOMAT___SELLP__08D548FA | Default | Column Constraint | SELLPRICE9 DEFAULT 0 |
DF__BILLOMAT___SELLP__092A4EB5 | Default | Column Constraint | SELLPRICE4 DEFAULT 0 |
DF__BILLOMAT___SELLP__09C96D33 | Default | Column Constraint | SELLPRICE10 DEFAULT 0 |
DF__BILLOMAT___WASTA__4E298478 | Default | Column Constraint | WASTAGE DEFAULT 0 |
PK__BILLOMAT___BILLC__0559BDD1 | Primary Key | Table Constraint | PRIMARY KEY (BILLCODE) |
Entity: BILLOMAT_LINES
Comment | This table contains line-level information for Bills of Materials (BOMs). |
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. |
BILLCODE | No | VARCHAR(23) | No | The ID code of the BOM header (BILLOMAT_HDR.BILLCODE). |
STOCKCODE | No | VARCHAR(23) | No | The stock code of the stock item on this line. |
QUANTITY | No | FLOAT(53) | No | The quantity on this line. |
DESCRIPTION | No | VARCHAR(40) | No | The description of the stock item on this line. |
VARIANTLINE | No | CHAR(1) | No | This field contains 'Y' if the line is not included in the BOM by default. |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
BILLOMAT_HDR_BILLOMAT_LINES | Non Identifying | BILLOMAT_HDR | BILLOMAT_LINES | Zero Or More |
STOCK_ITEMS_BILLOMAT_LINES | Non Identifying | STOCK_ITEMS | BILLOMAT_LINES | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
BILLOMAT_HDR_BILLOMAT_LINES | Foreign Key | Table Constraint | FOREIGN KEY (BILLCODE) REFERENCES BILLOMAT_HDR(BILLCODE) |
DF__BILLOMAT___QUANT__2C738AF2 | Default | Column Constraint | QUANTITY DEFAULT 1 |
DF__BILLOMAT___VARIA__2977EE0D | Default | Column Constraint | VARIANTLINE DEFAULT N |
PK__BILLOMAT___SEQNO__2B7F66B9 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
STOCK_ITEMS_BILLOMAT_LINES | Foreign Key | Table Constraint | FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE) |
Entity: BILLOMAT_TEMP
Comment | This table is used in Bill of Materials batch entry. |
Primary key columns | SEQNO |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
SEQNO | Yes | INTEGER | Yes | A unique ID number for the record. |
TRANSDATE | No | DATETIME | No | The date and time when the transaction occurred. |
COMPONENTLOCATION | No | INTEGER | No | The location of the BOM component stock item. |
PRODUCTLOCATION | No | INTEGER | No | The location of the BOM product stock item. |
REFERENCECODE | No | VARCHAR(30) | No | A reference code for the record. |
BATCHQUANTITY | No | FLOAT(53) | No | The batch quantity on the record. |
STAFFNO | No | INTEGER | Yes | The ID number of the staff member on the record. |
BILLCODE | No | VARCHAR(23) | No | The BOM code. |
STOCKCODE | No | VARCHAR(23) | No | The stock code of the BOM component item. |
QUANTITY | No | FLOAT(53) | No | The quantity of the BOM component item. |
UNITPRICE | No | FLOAT(53) | No | The unit price of the BOM component item. |
KITSEQNO | No | INTEGER | No | The ID number of the BOM. |
BOMBATCHSEQNO | No | INTEGER | No | The BOM batch ID number. |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
BILLOMAT_HDR_BILLOMAT_TEMP | Non Identifying | BILLOMAT_HDR | BILLOMAT_TEMP | Zero Or More |
STAFF_BILLOMAT_TEMP | Non Identifying | STAFF | BILLOMAT_TEMP | Zero Or More |
STOCK_ITEMS_BILLOMAT_TEMP | Non Identifying | STOCK_ITEMS | BILLOMAT_TEMP | Zero Or More |
STOCK_LOCATIONS_BILLOMAT_TEMP | Non Identifying | STOCK_LOCATIONS | BILLOMAT_TEMP | Zero Or More |
STOCK_LOCATIONS_BILLOMAT_TEMP_PROD | Non Identifying | STOCK_LOCATIONS | BILLOMAT_TEMP | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (STAFFNO) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
BILLOMAT_HDR_BILLOMAT_TEMP | Foreign Key | Table Constraint | FOREIGN KEY (BILLCODE) REFERENCES BILLOMAT_HDR(BILLCODE) |
DF__BILLOMAT___BOMBA__4B5804C5 | Default | Column Constraint | BOMBATCHSEQNO DEFAULT -1 |
DF__BILLOMAT___KITSE__4A63E08C | Default | Column Constraint | KITSEQNO DEFAULT -1 |
DF__BILLOMAT___STAFF__6458BCB9 | Default | Column Constraint | STAFFNO DEFAULT -1 |
PK__BILLOMAT_TEMP__63649880 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
STAFF_BILLOMAT_TEMP | Foreign Key | Table Constraint | FOREIGN KEY (STAFFNO) REFERENCES STAFF(STAFFNO) |
STOCK_ITEMS_BILLOMAT_TEMP | Foreign Key | Table Constraint | FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE) |
STOCK_LOCATIONS_BILLOMAT_TEMP | Foreign Key | Table Constraint | FOREIGN KEY (COMPONENTLOCATION) REFERENCES STOCK_LOCATIONS(LOCNO) |
STOCK_LOCATIONS_BILLOMAT_TEMP_PROD | Foreign Key | Table Constraint | FOREIGN KEY (PRODUCTLOCATION) REFERENCES STOCK_LOCATIONS(LOCNO) |
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: DR_INVLINES
Comment | This table contains line information for all Debtor transactions. |
Primary key columns | |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
SEQNO | No | INTEGER | Yes | A unique transaction ID. |
ACCNO | No | INTEGER | No | The account number of the Debtor that the transaction belongs to. |
INVNO | No | VARCHAR(20) | No | The invoice number. |
HDR_SEQNO | No | INTEGER | No | The SEQNO of the record in the DR_TRANS table that this record related to. |
STOCKCODE | No | VARCHAR(23) | No | Code of the Stock item on this line. |
DESCRIPTION | No | VARCHAR(40) | No | Description of the Stock item on this line. |
QUANTITY | No | FLOAT(53) | No | The quantity for this line. |
UNITPRICE | No | FLOAT(53) | No | The unit price of the Stock item on this line. |
DISCOUNT | No | FLOAT(53) | No | The percentage discount for the Stock item on this line. |
ANALYSIS | No | INTEGER | No | GL code for the Stock line. Can be overridden. The visibility of this column onscreen is controlled by the profile "Enable display and override of GL code from stock item". |
LOCATION | No | INTEGER | No | The location that the Stock item will be moved out of. Generally, lines inherit the location from the invoice header. |
UNITPRICE_INCTAX | No | FLOAT(53) | No | The unit price of the Stock item on this line, inclusive of tax. |
CUSTOMFIELD | No | VARCHAR(30) | No | |
UPDATE_STOCK | No | CHAR(1) | No | If this field is set to 'Y', stock movements are created via the trigger DR_INVLINES_POST. The field is then set to 'T'. |
UNITCOST | No | FLOAT(53) | No | The unit cost of the Stock item on this line. |
JOBCODE | No | VARCHAR(15) | No | If the line originates from a job, the job's code is recorded in this field. |
CURRENCYNO | No | INTEGER | No | The currency number for the line. |
EXCHRATE | No | FLOAT(53) | No | The exchange rate for the line at the time of the transaction. |
ORDERQTY | No | FLOAT(53) | No | The Order Qty from the Sales Order. |
BKORDQTY | No | FLOAT(53) | No | Back Order Qty from the Sales Order. |
BATCHCODE | No | VARCHAR(20) | No | The batch code on the line. The "Enable stock batch codes" Company-level profile setting must be enabled to be able to use batch codes. |
HIDDEN | No | CHAR(1) | No | |
SUBCODE | No | INTEGER | No | The GL subaccount code on the line. |
BRANCHNO | No | INTEGER | No | The branch number. Lines generally inherit the branch specified on the invoice header. |
TAXRATE | No | FLOAT(53) | No | The percentage tax rate on the line at the time of the transaction. |
TAXRATE_NO | No | INTEGER | No | The ID number of the tax rate used by the transaction. |
LINETOTAL_TAX | No | FLOAT(53) | No | The tax total for the line. |
LINETAX_OVERRIDDEN | No | CHAR(1) | No | Contains 'Y' if the tax on the line has been manually modified. |
LINE_SOURCE | No | INTEGER | No | The source SEQNO of the line, if it originated from a Sales Order or job. |
JOBNO | No | INTEGER | No | If the line originates from a job, the job's ID number is recorded in this field. |
SERIALNO | No | VARCHAR(50) | No | |
TRANSDATE | No | DATETIME | No | The date of the transaction. |
LINETOTAL | No | AS | No | A computed field containing the total value for the line, excluding tax. |
LINETOTAL_INCTAX | No | AS | No | A computed field containing the total value for the line, including tax. |
NUNITPR | No | AS | No | A computed field containing the unit price, taking into account the discount on the line. |
LISTPRICE | No | FLOAT(53) | No | The original unit price for the Stock item on this line. |
FREIGHT_FREE | No | CHAR(1) | Yes | 'Y' or 'N' depending on whether the line is freight free. This comes from the price policy for the line. |
CONTRACT_HDR | No | INTEGER | Yes | |
DRINVLINEID | No | INTEGER | No | A unique ID for the invoice line, which should be used to link to other tables. Every time an invoice is edited, the lines are essentially removed and reinserted again, which changes the SEQNO. However, the DRINVLINEID field retains its value. Hence, any relationships built on this field are still valid in such a scenario. |
NARRATIVE_SEQNO | No | INTEGER | No | The SEQNO for the narrative on this line. |
PRICE_OVERRIDDEN | No | CHAR(1) | Yes | Contains 'Y' if the price on the line has been manually modified. |
KITCODE | No | VARCHAR(23) | No | The Bill of Materials code, if the line is part of a BOM. |
LINETYPE | No | INTEGER | Yes | This field indicates the type of line. Will be one of: 0 = Normal stockline 1 = BOM header line 2 = BOM component line 4 = Narrative line |
KITSEQNO | No | INTEGER | No | A Bill of Materials SEQNO used to group BOM lines together. |
LINKED_STOCKCODE | No | VARCHAR(23) | No | The physical stock unit. |
LINKED_QTY | No | FLOAT(53) | No | The physical stock unit quantity. |
HIDDEN_COST | No | FLOAT(53) | No | This field applies to Bills of Materials. For kits that use pricing by components, this field stores the header-level cost. For builds that use individual stocklines, this fields stores the lines' real costs. |
HIDDEN_SELL | No | FLOAT(53) | No | This field applies to Bills of Materials. For kits that use pricing by components, this field stores header-level sell prices For builds that use individual stocklines, this fields stores the lines' real prices. |
BOMTYPE | No | CHAR(1) | No | The type of Bill of Materials that the line relates to: N = Normal stock item B = Build K = Kit L = Lookup |
SHOWLINE | No | CHAR(1) | No | This field applies to Bills of Materials, and is used by Clarity to determine if the line can be printed. |
LINKEDSTATUS | No | CHAR(1) | No | Will be one of: S = Stocked L = Lookup N = Not applicable |
BOMPRICING | No | CHAR(1) | No | How Bills of Materials are priced. Will be one of: C = Pricing by Component T = Pricing by total |
CREDITQTY | No | INTEGER | Yes | Contains the sum of quantities which have been a part of credit notes against the transaction. |
CODETYPE | No | CHAR(1) | Yes | The type of code on the line. Will be one of: G - GL code S - Stock code |
JOB_CONTRACT_BILLINGS_SEQNO | No | INTEGER | Yes | If the invoice is related to a Progress Billing invoice in EXO Job Costing, the ID number of that invoice is recorded here (from JOB_CONTRACT_BILLINGS). |
COST_LINENO | No | INTEGER | Yes | This field is the link used when allocating cost lines to DR_INVLINES. |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
CURRENCIES_DR_INVLINES | Non Identifying | CURRENCIES | DR_INVLINES | Zero Or More |
DR_INVLINES_DR_INVLINES_SERIALS | Non Identifying | DR_INVLINES | DR_INVLINES_SERIALS | Zero Or More |
DR_INVLINES_JOB_TRANSACTIONS | Identifying | DR_INVLINES | JOB_TRANSACTIONS | Zero Or More |
DR_INVLINES_SALESORD_HDR | Non Identifying | DR_INVLINES | SALESORD_HDR | Zero Or More |
DR_TRANS_DR_INVLINES_ACCNO | Non Identifying | DR_TRANS | DR_INVLINES | Zero Or More |
DR_TRANS_DR_INVLINES_SEQNO | Non Identifying | DR_TRANS | DR_INVLINES | Zero Or More |
NARRATIVES_DR_INVLINES | Non Identifying | NARRATIVES | DR_INVLINES | Zero Or More |
STOCK_ITEMS_DR_INVLINES | Non Identifying | STOCK_ITEMS | DR_INVLINES | Zero Or More |
TAX_RATES_DR_INVLINES | Non Identifying | TAX_RATES | DR_INVLINES | Zero Or More |
Indexes:
Name | Index columns | Index type |
DR_INVLINES_ANALYSIS | ANALYSIS ASC | |
DR_INVLINES_HDRSEQ | HDR_SEQNO ASC | |
DR_INVLINES_LINE_SOURCE | LINE_SOURCE ASC | |
DR_INVLINES_STOCKCODE | STOCKCODE ASC | |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (COST_LINENO) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| Not Null | Column Constraint | NOT NULL (JOB_CONTRACT_BILLINGS_SEQNO) |
| Not Null | Column Constraint | NOT NULL (CODETYPE) |
| Not Null | Column Constraint | NOT NULL (CREDITQTY) |
| Not Null | Column Constraint | NOT NULL (LINETYPE) |
| Not Null | Column Constraint | NOT NULL (PRICE_OVERRIDDEN) |
| Not Null | Column Constraint | NOT NULL (CONTRACT_HDR) |
| Not Null | Column Constraint | NOT NULL (FREIGHT_FREE) |
CURRENCIES_DR_INVLINES | Foreign Key | Table Constraint | FOREIGN KEY (CURRENCYNO) REFERENCES CURRENCIES(CURRENCYNO) |
DF__DR_INVLIN__ACCNO__1229A90A | Default | Column Constraint | ACCNO DEFAULT 0 |
DF__DR_INVLIN__ANALY__1ABEEF0B | Default | Column Constraint | ANALYSIS DEFAULT 0 |
DF__DR_INVLIN__BKORD__6A06A917 | Default | Column Constraint | BKORDQTY DEFAULT 0 |
DF__DR_INVLIN__BOMPR__4CC12A4A | Default | Column Constraint | BOMPRICING DEFAULT N |
DF__DR_INVLIN__BOMTY__49E4BD9F | Default | Column Constraint | BOMTYPE DEFAULT N |
DF__DR_INVLIN__BRANC__34E8D562 | Default | Column Constraint | BRANCHNO DEFAULT 0 |
DF__DR_INVLIN__CODET__7D45FCC2 | Default | Column Constraint | CODETYPE DEFAULT S |
DF__DR_INVLIN__CONTR__485B9C89 | Default | Column Constraint | CONTRACT_HDR DEFAULT 0 |
DF__DR_INVLIN__COST___02FED618 | Default | Column Constraint | COST_LINENO DEFAULT -1 |
DF__DR_INVLIN__CREDI__76D75FA7 | Default | Column Constraint | CREDITQTY DEFAULT 0 |
DF__DR_INVLIN__CURRE__36870511 | Default | Column Constraint | CURRENCYNO DEFAULT 0 |
DF__DR_INVLIN__DISCO__18D6A699 | Default | Column Constraint | DISCOUNT DEFAULT 0 |
DF__DR_INVLIN__DRINV__0FE2393C | Default | Column Constraint | DRINVLINEID DEFAULT 0 |
DF__DR_INVLIN__EXCHR__377B294A | Default | Column Constraint | EXCHRATE DEFAULT 1 |
DF__DR_INVLIN__FREIG__0D84EF7E | Default | Column Constraint | FREIGHT_FREE DEFAULT N |
DF__DR_INVLIN__HDR_S__1411F17C | Default | Column Constraint | HDR_SEQNO DEFAULT 0 |
DF__DR_INVLIN__HIDDE__0CDBAF5F | Default | Column Constraint | HIDDEN_COST DEFAULT 0 |
DF__DR_INVLIN__HIDDE__0DCFD398 | Default | Column Constraint | HIDDEN_SELL DEFAULT 0 |
DF__DR_INVLIN__JOBNO__6A1BB7B0 | Default | Column Constraint | JOBNO DEFAULT 0 |
DF__DR_INVLIN__JOB_C__020AB1DF | Default | Column Constraint | JOB_CONTRACT_BILLINGS_SEQNO DEFAULT -1 |
DF__DR_INVLIN__KITSE__0BE78B26 | Default | Column Constraint | KITSEQNO DEFAULT -1 |
DF__DR_INVLIN__LINET__0E4EF685 | Default | Column Constraint | LINETYPE DEFAULT -1 |
DF__DR_INVLIN__LINET__1BE81D6E | Default | Column Constraint | LINETOTAL_TAX DEFAULT 0 |
DF__DR_INVLIN__LINKE__4BCD0611 | Default | Column Constraint | LINKEDSTATUS DEFAULT N |
DF__DR_INVLIN__LISTP__59063A47 | Default | Column Constraint | LISTPRICE DEFAULT 0 |
DF__DR_INVLIN__LOCAT__1BB31344 | Default | Column Constraint | LOCATION DEFAULT 1 |
DF__DR_INVLIN__ORDER__691284DE | Default | Column Constraint | ORDERQTY DEFAULT 0 |
DF__DR_INVLIN__PRICE__6641052B | Default | Column Constraint | PRICE_OVERRIDDEN DEFAULT N |
DF__DR_INVLIN__QUANT__16EE5E27 | Default | Column Constraint | QUANTITY DEFAULT 0 |
DF__DR_INVLIN__SHOWL__4AD8E1D8 | Default | Column Constraint | SHOWLINE DEFAULT Y |
DF__DR_INVLIN__SUBCO__33F4B129 | Default | Column Constraint | SUBCODE DEFAULT 0 |
DF__DR_INVLIN__TRANS__6B0FDBE9 | Default | Column Constraint | TRANSDATE DEFAULT getdate() |
DF__DR_INVLIN__UNITC__6B2FD77A | Default | Column Constraint | UNITCOST DEFAULT 0 |
DF__DR_INVLIN__UNITP__17E28260 | Default | Column Constraint | UNITPRICE DEFAULT 0 |
DF__DR_INVLIN__UNITP__1CA7377D | Default | Column Constraint | UNITPRICE_INCTAX DEFAULT 0 |
DF__DR_INVLIN__UPDAT__21F5FC7F | Default | Column Constraint | UPDATE_STOCK DEFAULT Y |
DR_TRANS_DR_INVLINES_ACCNO | Foreign Key | Table Constraint | FOREIGN KEY (ACCNO) REFERENCES DR_TRANS(ACCNO) |
DR_TRANS_DR_INVLINES_SEQNO | Foreign Key | Table Constraint | FOREIGN KEY (HDR_SEQNO) REFERENCES DR_TRANS(SEQNO) |
NARRATIVES_DR_INVLINES | Foreign Key | Table Constraint | FOREIGN KEY (SEQNO) REFERENCES NARRATIVES(SEQNO) |
STOCK_ITEMS_DR_INVLINES | Foreign Key | Table Constraint | FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE) |
TAX_RATES_DR_INVLINES | Foreign Key | Table Constraint | FOREIGN KEY (TAXRATE_NO) REFERENCES TAX_RATES(SEQNO) |
Triggers:
Trigger name | Code |
DRINVLINESID | CREATE TRIGGER [dbo].[DRINVLINESID] ON [dbo].[DR_INVLINES] FOR INSERT AS BEGIN SET NOCOUNT ON UPDATE [dbo].[DR_INVLINES] SET DRINVLINEID = INSERTED.SEQNO FROM [dbo].[DR_INVLINES] INNER JOIN INSERTED ON ([dbo].[DR_INVLINES].SEQNO = INSERTED.SEQNO) WHERE INSERTED.DRINVLINEID IS NULL OR INSERTED.DRINVLINEID = 0 SET NOCOUNT OFF END
|
DR_INVLINES_POST | CREATE TRIGGER [dbo].[DR_INVLINES_POST] ON [dbo].[DR_INVLINES] FOR INSERT AS BEGIN /* Exonet created trigger from DBUpdate */ SET NOCOUNT ON DECLARE @ASTOCKTRANSCODE VARCHAR(23), @AREF2 VARCHAR(30), @ATRANSTYPE INT, @AGLPOSTED CHAR(1), @APOSTTOGL CHAR(1), @APOSTLOOKUPTOGL CHAR(1), @ISBOMBUILD CHAR(1), @ISBOMKITHDR CHAR(1), @ISBOMLINKED CHAR(1), @AQUANTITY DOUBLE PRECISION, @AUNITPRICE DOUBLE PRECISION, @AUNITCOST DOUBLE PRECISION, @UNITPRICE DOUBLE PRECISION, @UNITCOST DOUBLE PRECISION, @HIDDEN_COST DOUBLE PRECISION, @HIDDEN_SELL DOUBLE PRECISION, @QUANTITY DOUBLE PRECISION, @KITCODE VARCHAR(23), @KITSEQNO INT, @STOCKCODE VARCHAR(23), @JOBNO INT, @LINKED_STOCKCODE VARCHAR(23), @SEQNO INT, @HDR_SEQNO INT, @BATCHCODE VARCHAR(20), @INVNO VARCHAR(20), @ACCNO INT, @LOCATION INT, @TRANSDATE DATETIME, @LINKEDSTATUS CHAR(1), @DISCOUNT DOUBLE PRECISION, @EXCHRATE DOUBLE PRECISION, @BOMTYPE CHAR(1), @LINETYPE INT, @UPDATE_STOCK CHAR(1), @SESSION_ID INT , @AGE INT , @PERIOD_SEQNO INT IF ISNULL((SELECT TOP 1 SESSION_ID FROM DR_TRANS D JOIN INSERTED I ON D.SEQNO = I.HDR_SEQNO), -1) = -1 BEGIN EXECUTE GEN_ID 'DR_TRANS', 'SESSION_ID', 'Y', @ID=@SESSION_ID OUTPUT UPDATE D SET SESSION_ID = @SESSION_ID FROM DR_TRANS D JOIN INSERTED I ON D.SEQNO = I.HDR_SEQNO END ELSE BEGIN SELECT @SESSION_ID = (SELECT TOP 1 SESSION_ID FROM DR_TRANS D JOIN INSERTED I ON D.SEQNO = I.HDR_SEQNO) END SET @AGE = 0 SELECT @PERIOD_SEQNO = SEQNO FROM PERIOD_STATUS WHERE LEDGER='S' AND AGE = @AGE DECLARE INSERTED_INVCURSOR CURSOR LOCAL FOR SELECT UNITPRICE, UNITCOST, HIDDEN_COST, HIDDEN_SELL, QUANTITY, KITCODE, KITSEQNO, STOCKCODE, JOBNO, LINKED_STOCKCODE, SEQNO, HDR_SEQNO, BATCHCODE, INVNO, ACCNO, LOCATION, TRANSDATE, LINKEDSTATUS, DISCOUNT, EXCHRATE, BOMTYPE, LINETYPE, UPDATE_STOCK FROM INSERTED OPEN INSERTED_INVCURSOR FETCH NEXT FROM INSERTED_INVCURSOR INTO @UNITPRICE, @UNITCOST, @HIDDEN_COST, @HIDDEN_SELL, @QUANTITY, @KITCODE, @KITSEQNO, @STOCKCODE, @JOBNO, @LINKED_STOCKCODE, @SEQNO, @HDR_SEQNO, @BATCHCODE, @INVNO, @ACCNO, @LOCATION, @TRANSDATE, @LINKEDSTATUS, @DISCOUNT, @EXCHRATE, @BOMTYPE, @LINETYPE, @UPDATE_STOCK WHILE @@FETCH_STATUS = 0 BEGIN SET @ISBOMKITHDR = 'N' IF ((@BOMTYPE = 'K') AND (@LINETYPE = 1)) SET @ISBOMKITHDR = 'Y' IF ((@STOCKCODE IS NOT NULL) AND (@STOCKCODE<>'') AND (@UPDATE_STOCK='Y') AND (@ISBOMKITHDR = 'N')) BEGIN /* PREPARE TO INSERT STOCK TRANSACTION FOR NON-KIT HEADER LINES */ SET @ISBOMBUILD = 'N' SET @ISBOMLINKED = 'N' IF (@BOMTYPE = 'B') SET @ISBOMBUILD = 'Y' IF (@BOMTYPE = 'L') SET @ISBOMLINKED = 'Y' SET @ATRANSTYPE = 0 SET @AGLPOSTED = 'N' SET @APOSTTOGL = 'Y' SET @APOSTLOOKUPTOGL = 'N' SET @AUNITPRICE = @UNITPRICE SET @AUNITCOST = @UNITCOST SET @AREF2 = 'INVOICE' SET @AQUANTITY = - @QUANTITY /* FIND STOCKCODE TO POST TO STOCKTRANS */ SET @ASTOCKTRANSCODE = @LINKED_STOCKCODE IF (((@ASTOCKTRANSCODE <> '') AND ((@LINETYPE = 2) OR (@LINETYPE = 0))) OR (@ISBOMLINKED = 'Y')) SET @ASTOCKTRANSCODE = @STOCKCODE IF (@ASTOCKTRANSCODE = '') SET @ASTOCKTRANSCODE = @STOCKCODE /* FIND LOCAL CURRENCY DISCOUNTED UNIT PRICE EXCL TAX TO USE FOR STOCK_TRANS */ SET @AUNITPRICE = @AUNITPRICE * (1 - @DISCOUNT / 100) IF (@EXCHRATE <> 0) SET @AUNITPRICE = @AUNITPRICE / @EXCHRATE IF ((@ISBOMBUILD ='Y') AND (@LINETYPE = 2)) BEGIN /* CONSUME COMPONENTS FOR BOM BUILD COMPONENTS */ SET @AUNITPRICE = @HIDDEN_SELL SET @AUNITCOST = @HIDDEN_COST SET @AREF2 = 'SALES ORD BOM' SET @ATRANSTYPE = 3 SET @AGLPOSTED = 'Y' SET @APOSTTOGL = 'N' SET @AUNITPRICE = @AUNITCOST IF (@LINKEDSTATUS = 'L') SET @APOSTLOOKUPTOGL = 'Y' END ELSE IF ((@ISBOMBUILD='Y') AND (@LINETYPE = 1)) BEGIN /* CREATE NEW BOM BUILD HEADER OUTPUT ITEM */ SET @ATRANSTYPE = 1 SET @AGLPOSTED = 'Y' SET @APOSTTOGL = 'N' SET @AQUANTITY = @QUANTITY SET @AUNITPRICE = @AUNITCOST END INSERT INTO STOCK_TRANS (TRANSTYPE, STOCKCODE, ACCNO, REF1, REF2, QUANTITY, UNITPRICE, LOCATION, FROM_LEDGER, FROM_HDR, BATCHCODE, JOBNO, LINE_SEQNO, TRANSDATE, PLU, KITCODE, KITSEQNO, POST_TO_GL, UNITCOST, GLPOSTED, POST_LOOKUP_TO_GL, SESSION_ID , PERIOD_SEQNO ) VALUES (@ATRANSTYPE, @ASTOCKTRANSCODE, @ACCNO, @INVNO, @AREF2, @AQUANTITY, @AUNITPRICE, @LOCATION, 'd', @HDR_SEQNO, @BATCHCODE, @JOBNO, @SEQNO, @TRANSDATE, @STOCKCODE, @KITCODE, @KITSEQNO, @APOSTTOGL, @AUNITCOST, @AGLPOSTED, @APOSTLOOKUPTOGL, @SESSION_ID , @PERIOD_SEQNO ) /* SET UPDATE_STOCK FIELD ON DR_INVLINE TO MARK LINE AS TRIGGERED */ UPDATE [dbo].[DR_INVLINES] SET UPDATE_STOCK='T' WHERE SEQNO = @SEQNO /* INSERT 2nd TRANSACTION FOR THE SALE OF THE CREATED BOM BUILD HEADER OUTPUT ITEM */ IF ((@ISBOMBUILD='Y') AND (@LINETYPE = 1)) BEGIN SET @ATRANSTYPE = 0 SET @AGLPOSTED = 'N' SET @APOSTTOGL = 'Y' SET @AQUANTITY = -@QUANTITY SET @AUNITPRICE = @UNITPRICE INSERT INTO STOCK_TRANS (TRANSTYPE, STOCKCODE, ACCNO, REF1, REF2, QUANTITY, UNITPRICE, LOCATION, FROM_LEDGER, FROM_HDR, BATCHCODE, JOBNO, LINE_SEQNO, TRANSDATE, PLU, KITCODE, KITSEQNO, POST_TO_GL, UNITCOST, GLPOSTED, POST_LOOKUP_TO_GL, SESSION_ID , PERIOD_SEQNO ) VALUES (@ATRANSTYPE, @ASTOCKTRANSCODE, @ACCNO, @INVNO, @AREF2, @AQUANTITY, @AUNITPRICE, @LOCATION, 'd', @HDR_SEQNO, @BATCHCODE, @JOBNO, @SEQNO, @TRANSDATE, @STOCKCODE, @KITCODE, @KITSEQNO, @APOSTTOGL, @AUNITCOST, @AGLPOSTED, @APOSTLOOKUPTOGL, @SESSION_ID , @PERIOD_SEQNO ) END END ELSE IF ((@STOCKCODE IS NOT NULL) AND (@STOCKCODE<>'') AND (@UPDATE_STOCK='Y') AND (@ISBOMKITHDR = 'Y')) BEGIN /* SET UPDATE_STOCK FIELD FOR KIT HEADER DR_INVLINE TO MARK LINE AS TRIGGERED */ UPDATE [dbo].[DR_INVLINES] SET UPDATE_STOCK='T' WHERE SEQNO = @SEQNO END FETCH NEXT FROM INSERTED_INVCURSOR INTO @UNITPRICE, @UNITCOST, @HIDDEN_COST, @HIDDEN_SELL, @QUANTITY, @KITCODE, @KITSEQNO, @STOCKCODE, @JOBNO, @LINKED_STOCKCODE, @SEQNO, @HDR_SEQNO, @BATCHCODE, @INVNO, @ACCNO, @LOCATION, @TRANSDATE, @LINKEDSTATUS, @DISCOUNT, @EXCHRATE, @BOMTYPE, @LINETYPE, @UPDATE_STOCK END CLOSE INSERTED_INVCURSOR DEALLOCATE INSERTED_INVCURSOR SET NOCOUNT OFF END
|
DR_INVLINES_SET_UNITCOST | CREATE TRIGGER [dbo].[DR_INVLINES_SET_UNITCOST] ON [dbo].[DR_INVLINES] FOR INSERT AS DECLARE @STOCKCOSTMETHOD INTEGER BEGIN SET NOCOUNT ON EXECUTE GET_PROFILE_VALUE_INT 'STOCKCOSTMETHOD', @STOCKCOSTMETHOD OUTPUT UPDATE [dbo].[DR_INVLINES] SET UNITCOST = (SELECT (CASE @STOCKCOSTMETHOD WHEN 1 THEN LATESTCOST WHEN 2 THEN STDCOST ELSE AVECOST END) FROM STOCK_ITEMS WHERE STOCK_ITEMS.STOCKCODE = INSERTED.STOCKCODE) FROM [dbo].[DR_INVLINES] JOIN INSERTED ON ([dbo].[DR_INVLINES].SEQNO = INSERTED.SEQNO) WHERE INSERTED.STOCKCODE IS NOT NULL AND (INSERTED.STOCKCODE <> '') AND (INSERTED.BOMTYPE <> 'K') AND (INSERTED.BOMTYPE <> 'B') SET NOCOUNT OFF END
|
Entity: 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: INWARDS_GOODS
Comment | This table contains header information on Inwards Goods 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 transaction. |
SUPPLIERNAME | No | VARCHAR(70) | No | The name of the supplier on the transaction. |
LOCNO | No | INTEGER | Yes | The ID number of the location on the transaction. |
SUPPLIERREF | No | VARCHAR(50) | No | The shipping reference on the transaction. |
COMPLETE | No | CHAR(1) | Yes | This field contains 'Y' if the receipt has been completed. |
NOTES | No | VARCHAR(500) | No | Any receipt notes on the transaction. |
NUM_CARTONS | No | INTEGER | Yes | The number of cartons. |
INVOICED | No | CHAR(1) | Yes | This field contains 'Y' if the receipt has been invoiced. |
GLBATCHNO | No | INTEGER | Yes | The GL batch number on the transaction. |
SHIPMENTNO | No | INTEGER | Yes | The shipment number on the transaction. |
TRANSDATE | No | DATETIME | No | The transaction date. |
SUPPLIERNO | No | INTEGER | Yes | The ID number of the supplier (Creditor) account. |
PACKSLIP | No | VARCHAR(50) | No | The packing slip reference. |
REVERSALSTATUS | No | INTEGER | No | The reversal status. Will be one of: 0 = Normal 1 = Reversed 2 = Reversal |
RELATED_SEQNO | No | INTEGER | No | For reversals, this field contains the source Inwards Goods ID number. |
COSTED_PERIOD_SEQNO | No | INTEGER | No | The period to which the receipt belongs. |
SESSION_ID | No | INTEGER | No | The session ID for the record. |
LASTINVSEQNO | No | INTEGER | No | The ID number of the last invoice generated against the receipt. |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
CR_ACCS_INWARDS_GOODS | Non Identifying | CR_ACCS | INWARDS_GOODS | Zero Or More |
INWARDS_GOODS_INWARDS_GOODS_COSTS | Non Identifying | INWARDS_GOODS | INWARDS_GOODS_COSTS | Zero Or More |
INWARDS_GOODS_INWARDS_GOODS_LINES | Non Identifying | INWARDS_GOODS | INWARDS_GOODS_LINES | Zero Or More |
STOCK_LOCATIONS_INWARDS_GOODS | Non Identifying | STOCK_LOCATIONS | INWARDS_GOODS | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (SUPPLIERNO) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| Not Null | Column Constraint | NOT NULL (LOCNO) |
| Not Null | Column Constraint | NOT NULL (SHIPMENTNO) |
| Not Null | Column Constraint | NOT NULL (COMPLETE) |
| Not Null | Column Constraint | NOT NULL (GLBATCHNO) |
| Not Null | Column Constraint | NOT NULL (NUM_CARTONS) |
| Not Null | Column Constraint | NOT NULL (INVOICED) |
CR_ACCS_INWARDS_GOODS | Foreign Key | Table Constraint | FOREIGN KEY (SUPPLIERNO) REFERENCES CR_ACCS(ACCNO) |
DF__INWARDS_G__COMPL__151B244E | Default | Column Constraint | COMPLETE DEFAULT N |
DF__INWARDS_G__COSTE__3005BB62 | Default | Column Constraint | COSTED_PERIOD_SEQNO DEFAULT -1 |
DF__INWARDS_G__GLBAT__18EBB532 | Default | Column Constraint | GLBATCHNO DEFAULT 0 |
DF__INWARDS_G__INVOI__17F790F9 | Default | Column Constraint | INVOICED DEFAULT N |
DF__INWARDS_G__LASTI__647983A1 | Default | Column Constraint | LASTINVSEQNO DEFAULT -1 |
DF__INWARDS_G__LOCNO__1332DBDC | Default | Column Constraint | LOCNO DEFAULT 0 |
DF__INWARDS_G__NUM_C__17036CC0 | Default | Column Constraint | NUM_CARTONS DEFAULT 0 |
DF__INWARDS_G__RELAT__25882CEF | Default | Column Constraint | RELATED_SEQNO DEFAULT 0 |
DF__INWARDS_G__REVER__249408B6 | Default | Column Constraint | REVERSALSTATUS DEFAULT 0 |
DF__INWARDS_G__SESSI__34CA707F | Default | Column Constraint | SESSION_ID DEFAULT -1 |
DF__INWARDS_G__SHIPM__19DFD96B | Default | Column Constraint | SHIPMENTNO DEFAULT 0 |
DF__INWARDS_G__SUPPL__4C764630 | Default | Column Constraint | SUPPLIERNO DEFAULT 0 |
DF__INWARDS_G__TRANS__1AD3FDA4 | Default | Column Constraint | TRANSDATE DEFAULT getdate() |
PK__INWARDS_GOODS__662B2B3B | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
STOCK_LOCATIONS_INWARDS_GOODS | Foreign Key | Table Constraint | FOREIGN KEY (LOCNO) REFERENCES STOCK_LOCATIONS(LOCNO) |
Entity: INWARDS_GOODS_COSTS
Comment | This table contains information on Inwards Good Costing. |
Primary key columns | SEQNO |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
SEQNO | Yes | INTEGER | Yes | A unique ID number for the record. |
INWARDS_GOODS_SEQNO | No | INTEGER | Yes | The ID number of the Inwards Goods header record. |
COSTCODE | No | VARCHAR(15) | No | The on cost code for the record. |
DETAILS | No | VARCHAR(40) | No | Details of the on cost. |
SPREAD_TYPE | No | INTEGER | Yes | The spread type for on costs: 0 = Quantity 1 = Value 2 = Weight 3 = Cubic |
FC_COST | No | FLOAT(53) | Yes | The cost in the currency of the supplier account. |
EXCHRATE | No | FLOAT(53) | Yes | The exchange rate. |
COST | No | FLOAT(53) | Yes | The cost in local currency. |
SHIPMENTNO | No | INTEGER | No | The shipment number on the record. |
ACCNO | No | INTEGER | No | The supplier (Creditor account ID). |
INVOICE_NOW | No | FLOAT(53) | No | |
GLBATCH | No | INTEGER | No | GL batch number. |
FX_VAR | No | FLOAT(53) | No | The foreign exchange variance applicable to the invoice raised against this record. |
INV_FC_COST | No | FLOAT(53) | No | The invoiced amount in the currency of the supplier account. |
INV_EXCHRATE | No | FLOAT(53) | No | The exchange rate applicable to the invoice. |
INV_COST | No | FLOAT(53) | No | The invoiced amount in local currency. |
COMPLETE | No | CHAR(1) | Yes | Indicates if the line has been marked as completed, which controls whether or not the line can be edited. |
GLPOSTED | No | CHAR(1) | Yes | This field contains 'Y' if a GL journal exists for the record. |
CAN_SPREAD | No | CHAR(1) | Yes | |
TRANSDATE | No | DATETIME | No | The transaction date of the invoice linked to this record. |
PERIOD_SEQNO | No | INTEGER | Yes | The period ID of the invoice linked to this record. |
AGE_STAMP | No | INTEGER | No | This is a historical field that has been superseded by the AGE calculated field. |
AGE | No | AS | No | The invoice's age. This is calculated based on PERIOD_SEQNO. |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
CR_ACCS_INWARDS_GOODS_COSTS | Non Identifying | CR_ACCS | INWARDS_GOODS_COSTS | Zero Or More |
INWARDS_GOODS_INWARDS_GOODS_COSTS | Non Identifying | INWARDS_GOODS | INWARDS_GOODS_COSTS | Zero Or More |
PERIOD_STATUS_INWARDS_GOODS_COSTS | Non Identifying | PERIOD_STATUS | INWARDS_GOODS_COSTS | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (PERIOD_SEQNO) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| Not Null | Column Constraint | NOT NULL (INWARDS_GOODS_SEQNO) |
| Not Null | Column Constraint | NOT NULL (CAN_SPREAD) |
| Not Null | Column Constraint | NOT NULL (SPREAD_TYPE) |
| Not Null | Column Constraint | NOT NULL (GLPOSTED) |
| Not Null | Column Constraint | NOT NULL (FC_COST) |
| Not Null | Column Constraint | NOT NULL (COMPLETE) |
| Not Null | Column Constraint | NOT NULL (EXCHRATE) |
| Not Null | Column Constraint | NOT NULL (COST) |
CR_ACCS_INWARDS_GOODS_COSTS | Foreign Key | Table Constraint | FOREIGN KEY (ACCNO) REFERENCES CR_ACCS(ACCNO) |
DF__INWARDS_GO__COST__41EDCAC5 | Default | Column Constraint | COST DEFAULT 0 |
DF__INWARDS_G__ACCNO__40457975 | Default | Column Constraint | ACCNO DEFAULT 0 |
DF__INWARDS_G__AGE_S__4AB9B19E | Default | Column Constraint | AGE_STAMP DEFAULT -1 |
DF__INWARDS_G__CAN_S__49CEE3AF | Default | Column Constraint | CAN_SPREAD DEFAULT Y |
DF__INWARDS_G__COMPL__47E69B3D | Default | Column Constraint | COMPLETE DEFAULT N |
DF__INWARDS_G__EXCHR__40F9A68C | Default | Column Constraint | EXCHRATE DEFAULT 0 |
DF__INWARDS_G__FC_CO__40058253 | Default | Column Constraint | FC_COST DEFAULT 0 |
DF__INWARDS_G__FX_VA__44160A59 | Default | Column Constraint | FX_VAR DEFAULT 0 |
DF__INWARDS_G__GLBAT__4321E620 | Default | Column Constraint | GLBATCH DEFAULT 0 |
DF__INWARDS_G__GLPOS__48DABF76 | Default | Column Constraint | GLPOSTED DEFAULT N |
DF__INWARDS_G__INVOI__41399DAE | Default | Column Constraint | INVOICE_NOW DEFAULT 0 |
DF__INWARDS_G__INV_C__46F27704 | Default | Column Constraint | INV_COST DEFAULT 0 |
DF__INWARDS_G__INV_E__45FE52CB | Default | Column Constraint | INV_EXCHRATE DEFAULT 0 |
DF__INWARDS_G__INV_F__450A2E92 | Default | Column Constraint | INV_FC_COST DEFAULT 0 |
DF__INWARDS_G__INWAR__3C34F16F | Default | Column Constraint | INWARDS_GOODS_SEQNO DEFAULT 0 |
DF__INWARDS_G__PERIO__49C58D65 | Default | Column Constraint | PERIOD_SEQNO DEFAULT 0 |
DF__INWARDS_G__SHIPM__3F51553C | Default | Column Constraint | SHIPMENTNO DEFAULT -1 |
DF__INWARDS_G__SPREA__3F115E1A | Default | Column Constraint | SPREAD_TYPE DEFAULT 0 |
DF__INWARDS_G__TRANS__39CE5167 | Default | Column Constraint | TRANSDATE DEFAULT getdate() |
INWARDS_GOODS_INWARDS_GOODS_COSTS | Foreign Key | Table Constraint | FOREIGN KEY (INWARDS_GOODS_SEQNO) REFERENCES INWARDS_GOODS(SEQNO) |
PERIOD_STATUS_INWARDS_GOODS_COSTS | Foreign Key | Table Constraint | FOREIGN KEY (PERIOD_SEQNO) REFERENCES PERIOD_STATUS(SEQNO) |
PK__INWARDS_GOODS_CO__671F4F74 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
Entity: INWARDS_GOODS_LINES
Comment | This table contains line-level information on Inwards Goods 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 record. |
HDR_SEQNO | No | INTEGER | Yes | The ID number of the header Inwards Goods record. |
PO_NUMBER | No | INTEGER | Yes | The ID number of the related Purchase Order header. |
PO_LINE_NUM | No | INTEGER | Yes | The ID number of the related Purchase Order line. |
SHIPMENTNO | No | INTEGER | Yes | The shipment number on the record. |
LOCNO | No | INTEGER | Yes | The ID of the location on the record. |
BATCHNO | No | INTEGER | Yes | The batch number on the record. |
BRANCHNO | No | INTEGER | Yes | The ID number of the branch on the record. |
JOBNO | No | INTEGER | Yes | If the order originates from a job, this field records the ID number of the job. |
COST_TYPE | No | INTEGER | Yes | If the order originates from a job, this field records the ID number of the job's cost type. |
COST_GROUP | No | INTEGER | Yes | If the order originates from a job, this field records the ID number of the job's cost group. |
STOCKCODE | No | VARCHAR(23) | No | The stock code of the stock item on the line. |
DESCRIPTION | No | VARCHAR(40) | No | The description of the stock item on the line. |
QUANTITY | No | FLOAT(53) | No | The receipt quantity on the line. |
UNITPRICE | No | FLOAT(53) | No | The unit price of the stock item on the line. |
DUTYCOST | No | FLOAT(53) | No | The duty cost on the line. |
FIXEDCOST | No | FLOAT(53) | No | The fixed cost on the line. |
LCOST | No | FLOAT(53) | Yes | The landed cost on the line. |
FXCOST | No | FLOAT(53) | Yes | The cost on the line in local currency. |
INV_QUANT | No | FLOAT(53) | Yes | The invoiced quantity on the line. |
BATCHCODE | No | VARCHAR(20) | No | The batch code on the line. |
EXPIRY_DATE | No | DATETIME | No | The expiry date of the stock item, if there is one. |
SUPPLIERCODE | No | VARCHAR(50) | No | The supplier code on the line. |
PURCHPACKQUANT | No | FLOAT(53) | Yes | The purchase pack quantity on the line. |
PURCHPACKPRICE | No | FLOAT(53) | Yes | The purchase pack price on the line. |
EXCHRATE | No | FLOAT(53) | No | The exchange rate on the line. |
LINETYPE | No | INTEGER | No | The line type. Will be one of: 0 = Normal line 1 = BOM header 2 = BOM component 4 = Comment |
KITSEQNO | No | INTEGER | No | The ID number of the Bill of Materials on the line. |
KITCODE | No | VARCHAR(23) | No | The ID code of the Bill of Materials on the line. |
LINKED_STOCKCODE | No | VARCHAR(23) | No | The linked stock item code on the line. |
LINKED_QTY | No | FLOAT(53) | No | The linked quantity on the line. |
BOMTYPE | No | CHAR(1) | No | The Bill of Materials type on the line. Will be one of: N = Normal K = Kit B = Build L = Linked |
SHOWLINE | No | CHAR(1) | No | This field is used to control the visibility of the line on Clarity forms. |
LINKEDSTATUS | No | CHAR(1) | No | S = Stock item, L = Lookup item. |
BOMPRICING | No | CHAR(1) | No | C = BOM priced by Component, T = BOM priced by total, N = Normal |
COMPLETE | No | CHAR(1) | No | This field contains 'Y' if the inwards goods receipt has been completed (and therefore cannot be altered). |
DISCOUNT | No | FLOAT(53) | No | The discount percentage on the line. |
INV_FC_COST | No | FLOAT(53) | No | The cost as on the related invoice. |
INV_EXCHRATE | No | FLOAT(53) | No | The exchange rate applicable to the invoice line. |
INV_COST | No | FLOAT(53) | No | The cost as on the related invoice, in local currency. |
INV_COMPLETE | No | CHAR(1) | No | This field contains 'Y' if the invoice is complete. |
VAR_GLPOSTED | No | CHAR(1) | No | This field contains 'Y' if variance has been posted against this line. |
FX_VAR | No | FLOAT(53) | No | The foreign exchange variance applicable to the line at the time of invoicing. |
INV_TRANSDATE | No | DATETIME | No | The transaction date of the invoice related to this line. |
INWGLID | No | INTEGER | No | An ID number for the inwards goods lines record. |
PERIOD_SEQNO | No | INTEGER | Yes | The ID number of the period on the invoice linked to this record. |
AGE_STAMP | No | INTEGER | No | This is a historical field that has been superseded by the AGE calculated field. |
INV_AGE | No | AS | No | The invoice's age. This is calculated based on PERIOD_SEQNO. |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
BRANCHES_INWARDS_GOODS_LINES | Non Identifying | BRANCHES | INWARDS_GOODS_LINES | Zero Or More |
INWARDS_GOODS_INWARDS_GOODS_LINES | Non Identifying | INWARDS_GOODS | INWARDS_GOODS_LINES | Zero Or More |
JOBCOST_HDR_INWARDS_GOODS_LINES | Non Identifying | JOBCOST_HDR | INWARDS_GOODS_LINES | Zero Or More |
JOB_COSTGROUPS_INWARDS_GOODS_LINES | Non Identifying | JOB_COSTGROUPS | INWARDS_GOODS_LINES | Zero Or More |
JOB_COSTTYPES_INWARDS_GOODS_LINES | Non Identifying | JOB_COSTTYPES | INWARDS_GOODS_LINES | Zero Or More |
PERIOD_STATUS_INWARDS_GOODS_LINES | Non Identifying | PERIOD_STATUS | INWARDS_GOODS_LINES | Zero Or More |
PURCHORD_HDR_INWARDS_GOODS_LINES | Non Identifying | PURCHORD_HDR | INWARDS_GOODS_LINES | Zero Or More |
PURCHORD_LINES_INWARDS_GOODS_LINES | Non Identifying | PURCHORD_LINES | INWARDS_GOODS_LINES | Zero Or More |
STOCK_ITEMS_INWARDS_GOODS_LINES | Non Identifying | STOCK_ITEMS | INWARDS_GOODS_LINES | Zero Or More |
STOCK_LOCATIONS_INWARDS_GOODS_LINES | Non Identifying | STOCK_LOCATIONS | INWARDS_GOODS_LINES | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (PERIOD_SEQNO) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| Not Null | Column Constraint | NOT NULL (HDR_SEQNO) |
| Not Null | Column Constraint | NOT NULL (PURCHPACKPRICE) |
| Not Null | Column Constraint | NOT NULL (PO_NUMBER) |
| Not Null | Column Constraint | NOT NULL (PURCHPACKQUANT) |
| Not Null | Column Constraint | NOT NULL (PO_LINE_NUM) |
| Not Null | Column Constraint | NOT NULL (INV_QUANT) |
| Not Null | Column Constraint | NOT NULL (SHIPMENTNO) |
| Not Null | Column Constraint | NOT NULL (FXCOST) |
| Not Null | Column Constraint | NOT NULL (LOCNO) |
| Not Null | Column Constraint | NOT NULL (LCOST) |
| Not Null | Column Constraint | NOT NULL (BATCHNO) |
| Not Null | Column Constraint | NOT NULL (COST_GROUP) |
| Not Null | Column Constraint | NOT NULL (BRANCHNO) |
| Not Null | Column Constraint | NOT NULL (COST_TYPE) |
| Not Null | Column Constraint | NOT NULL (JOBNO) |
BRANCHES_INWARDS_GOODS_LINES | Foreign Key | Table Constraint | FOREIGN KEY (BRANCHNO) REFERENCES BRANCHES(BRANCHNO) |
DF__INWARDS_G__AGE_S__4CA1FA10 | Default | Column Constraint | AGE_STAMP DEFAULT -1 |
DF__INWARDS_G__BATCH__22751F6C | Default | Column Constraint | BATCHNO DEFAULT 0 |
DF__INWARDS_G__BOMPR__5CF79213 | Default | Column Constraint | BOMPRICING DEFAULT N |
DF__INWARDS_G__BOMTY__5A1B2568 | Default | Column Constraint | BOMTYPE DEFAULT N |
DF__INWARDS_G__BRANC__236943A5 | Default | Column Constraint | BRANCHNO DEFAULT 0 |
DF__INWARDS_G__COMPL__0DEFCF29 | Default | Column Constraint | COMPLETE DEFAULT N |
DF__INWARDS_G__COST___25518C17 | Default | Column Constraint | COST_TYPE DEFAULT 0 |
DF__INWARDS_G__COST___2645B050 | Default | Column Constraint | COST_GROUP DEFAULT 0 |
DF__INWARDS_G__DISCO__332153D8 | Default | Column Constraint | DISCOUNT DEFAULT 0 |
DF__INWARDS_G__DUTYC__2B0A656D | Default | Column Constraint | DUTYCOST DEFAULT 0 |
DF__INWARDS_G__FIXED__2BFE89A6 | Default | Column Constraint | FIXEDCOST DEFAULT 0 |
DF__INWARDS_G__FXCOS__4E5E8EA2 | Default | Column Constraint | FXCOST DEFAULT 0 |
DF__INWARDS_G__FX_VA__697D6489 | Default | Column Constraint | FX_VAR DEFAULT 0 |
DF__INWARDS_G__HDR_S__1DB06A4F | Default | Column Constraint | HDR_SEQNO DEFAULT 0 |
DF__INWARDS_G__INV_C__66A0F7DE | Default | Column Constraint | INV_COST DEFAULT 0 |
DF__INWARDS_G__INV_C__67951C17 | Default | Column Constraint | INV_COMPLETE DEFAULT N |
DF__INWARDS_G__INV_E__65ACD3A5 | Default | Column Constraint | INV_EXCHRATE DEFAULT 0 |
DF__INWARDS_G__INV_F__64B8AF6C | Default | Column Constraint | INV_FC_COST DEFAULT 0 |
DF__INWARDS_G__INV_Q__6FF48C97 | Default | Column Constraint | INV_QUANT DEFAULT 0 |
DF__INWARDS_G__INV_T__6B65ACFB | Default | Column Constraint | INV_TRANSDATE DEFAULT getdate() |
DF__INWARDS_G__INWGL__267C5128 | Default | Column Constraint | INWGLID DEFAULT 0 |
DF__INWARDS_G__JOBNO__245D67DE | Default | Column Constraint | JOBNO DEFAULT 0 |
DF__INWARDS_G__KITSE__1570F560 | Default | Column Constraint | KITSEQNO DEFAULT -1 |
DF__INWARDS_G__LCOST__4D6A6A69 | Default | Column Constraint | LCOST DEFAULT 0 |
DF__INWARDS_G__LINET__147CD127 | Default | Column Constraint | LINETYPE DEFAULT -1 |
DF__INWARDS_G__LINKE__16651999 | Default | Column Constraint | LINKED_QTY DEFAULT 0 |
DF__INWARDS_G__LINKE__5C036DDA | Default | Column Constraint | LINKEDSTATUS DEFAULT N |
DF__INWARDS_G__LOCNO__2180FB33 | Default | Column Constraint | LOCNO DEFAULT 0 |
DF__INWARDS_G__PERIO__4BADD5D7 | Default | Column Constraint | PERIOD_SEQNO DEFAULT 0 |
DF__INWARDS_G__PO_LI__1F98B2C1 | Default | Column Constraint | PO_LINE_NUM DEFAULT 0 |
DF__INWARDS_G__PO_NU__1EA48E88 | Default | Column Constraint | PO_NUMBER DEFAULT 0 |
DF__INWARDS_G__PURCH__04708690 | Default | Column Constraint | PURCHPACKQUANT DEFAULT 1 |
DF__INWARDS_G__PURCH__0564AAC9 | Default | Column Constraint | PURCHPACKPRICE DEFAULT 0 |
DF__INWARDS_G__QUANT__29221CFB | Default | Column Constraint | QUANTITY DEFAULT 0 |
DF__INWARDS_G__SHIPM__208CD6FA | Default | Column Constraint | SHIPMENTNO DEFAULT 0 |
DF__INWARDS_G__SHOWL__5B0F49A1 | Default | Column Constraint | SHOWLINE DEFAULT Y |
DF__INWARDS_G__UNITP__2A164134 | Default | Column Constraint | UNITPRICE DEFAULT 0 |
DF__INWARDS_G__VAR_G__68894050 | Default | Column Constraint | VAR_GLPOSTED DEFAULT N |
INWARDS_GOODS_INWARDS_GOODS_LINES | Foreign Key | Table Constraint | FOREIGN KEY (HDR_SEQNO) REFERENCES INWARDS_GOODS(SEQNO) |
JOBCOST_HDR_INWARDS_GOODS_LINES | Foreign Key | Table Constraint | FOREIGN KEY (JOBNO) REFERENCES JOBCOST_HDR(JOBNO) |
JOB_COSTGROUPS_INWARDS_GOODS_LINES | Foreign Key | Table Constraint | FOREIGN KEY (COST_GROUP) REFERENCES JOB_COSTGROUPS(SEQNO) |
JOB_COSTTYPES_INWARDS_GOODS_LINES | Foreign Key | Table Constraint | FOREIGN KEY (COST_TYPE) REFERENCES JOB_COSTTYPES(SEQNO) |
PERIOD_STATUS_INWARDS_GOODS_LINES | Foreign Key | Table Constraint | FOREIGN KEY (PERIOD_SEQNO) REFERENCES PERIOD_STATUS(SEQNO) |
PK__INWARDS_GOODS_LI__681373AD | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
PURCHORD_HDR_INWARDS_GOODS_LINES | Foreign Key | Table Constraint | FOREIGN KEY (PO_NUMBER) REFERENCES PURCHORD_HDR(SEQNO) |
PURCHORD_LINES_INWARDS_GOODS_LINES | Foreign Key | Table Constraint | FOREIGN KEY (PO_LINE_NUM) REFERENCES PURCHORD_LINES(SEQNO) |
STOCK_ITEMS_INWARDS_GOODS_LINES | Foreign Key | Table Constraint | FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE) |
STOCK_LOCATIONS_INWARDS_GOODS_LINES | Foreign Key | Table Constraint | FOREIGN KEY (LOCNO) REFERENCES STOCK_LOCATIONS(LOCNO) |
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. |
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_MENU_ASSIGNMENTS | Non Identifying | STAFF | MENU_ASSIGNMENTS | Zero Or More |
STAFF_OPPORTUNITY_HIST | Non Identifying | STAFF | OPPORTUNITY_HIST | Zero Or More |
STAFF_PROFILE_FORMS | Non Identifying | STAFF | PROFILE_FORMS | Zero Or More |
STAFF_STOCK_REQUESTS | Non Identifying | STAFF | STOCK_REQUESTS | Zero Or More |
STAFF_STOCK_TRANS_HDR | Non Identifying | STAFF | STOCK_TRANS_HDR | Zero Or More |
STAFF_TASKS | Non Identifying | STAFF | TASKS | Zero Or More |
STAFF_TASKS1 | Non Identifying | STAFF | TASKS | Zero Or More |
STAFF_TASKS2 | Non Identifying | STAFF | TASKS | Zero Or More |
STAFF_TASKS3 | Non Identifying | STAFF | TASKS | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (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: STOCKREQUIREMENT
Comment | This table contains the data used by the Forecast Based Purchasing feature. |
Primary key columns | |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
STOCKCODE | No | VARCHAR(23) | Yes | The stock item's ID code. |
LOCNO | No | INTEGER | No | The ID number of the stock item's location (from STOCK_LOCATIONS). |
DESCRIPTION | No | VARCHAR(40) | No | The stock item's description. |
ACCNO | No | INTEGER | No | The ID number of the stock item's supplier (from CRACCS). |
STOCKGROUP | No | INTEGER | No | The ID number of the stock item's Primary Stock Group. |
MINSTOCK | No | FLOAT(53) | No | The minimum stock level for the stock item. |
MAXSTOCK | No | FLOAT(53) | No | The maximum stock level for the stock item. |
INSTOCKQTY | No | FLOAT(53) | No | Physical stock. |
PURCHORDQTY | No | FLOAT(53) | No | Incoming stock. |
SALESORDQTY | No | FLOAT(53) | No | Committed stock. |
VIRTSTOCK | No | FLOAT(53) | No | Free stock taking into account the incoming and committed stock. |
CALCREORD | No | FLOAT(53) | No | Reorder quantity, calculated taking into account minimum stock, maximum stock and available free stock. |
ACTUALREORD | No | FLOAT(53) | No | Actual reorder quantity for the stock item. |
SALES0 | No | FLOAT(53) | No | Current month sales quantity for the stock item. |
SALES1 | No | FLOAT(53) | No | Sales quantity for the stock item for the month prior to current month. |
SALES2 | No | FLOAT(53) | No | Sales quantity for the stock item two months prior to current month. |
SALES3 | No | FLOAT(53) | No | Sales quantity for the stock item three months prior to current month. |
SALES4 | No | FLOAT(53) | No | Sales quantity for the stock item four months prior to current month. |
SALES5 | No | FLOAT(53) | No | Sales quantity for the stock item five months prior to current month. |
SALES6 | No | FLOAT(53) | No | Sales quantity for the stock item six months prior to current month. |
SALES7 | No | FLOAT(53) | No | Sales quantity for the stock item seven months prior to current month. |
SALES8 | No | FLOAT(53) | No | Sales quantity for the stock item eight months prior to current month. |
SALES9 | No | FLOAT(53) | No | Sales quantity for the stock item nine months prior to current month. |
SALES10 | No | FLOAT(53) | No | Sales quantity for the stock item ten months prior to current month. |
SALES11 | No | FLOAT(53) | No | Sales quantity for the stock item eleven months prior to current month. |
SALES12 | No | FLOAT(53) | No | Sales quantity for the stock item twelve months prior to current month. |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
STOCK_GROUPS_STOCKREQUIREMENT | Non Identifying | STOCK_GROUPS | STOCKREQUIREMENT | Zero Or More |
STOCK_ITEMS_STOCKREQUIREMENT | Non Identifying | STOCK_ITEMS | STOCKREQUIREMENT | Zero Or More |
STOCK_LOCATIONS_STOCKREQUIREMENT | Non Identifying | STOCK_LOCATIONS | STOCKREQUIREMENT | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (STOCKCODE) |
DF__STOCKREQU__ACCNO__19EAC663 | Default | Column Constraint | ACCNO DEFAULT 0 |
DF__STOCKREQU__ACTUA__22800C64 | Default | Column Constraint | ACTUALREORD DEFAULT 0 |
DF__STOCKREQU__CALCR__218BE82B | Default | Column Constraint | CALCREORD DEFAULT 0 |
DF__STOCKREQU__INSTO__1DBB5747 | Default | Column Constraint | INSTOCKQTY DEFAULT 0 |
DF__STOCKREQU__MAXST__1CC7330E | Default | Column Constraint | MAXSTOCK DEFAULT 0 |
DF__STOCKREQU__MINST__1BD30ED5 | Default | Column Constraint | MINSTOCK DEFAULT 0 |
DF__STOCKREQU__PURCH__1EAF7B80 | Default | Column Constraint | PURCHORDQTY DEFAULT 0 |
DF__STOCKREQU__SALES__1FA39FB9 | Default | Column Constraint | SALESORDQTY DEFAULT 0 |
DF__STOCKREQU__SALES__5244F976 | Default | Column Constraint | SALES7 DEFAULT 0 |
DF__STOCKREQU__SALES__53391DAF | Default | Column Constraint | SALES8 DEFAULT 0 |
DF__STOCKREQU__SALES__542D41E8 | Default | Column Constraint | SALES9 DEFAULT 0 |
DF__STOCKREQU__SALES__55216621 | Default | Column Constraint | SALES10 DEFAULT 0 |
DF__STOCKREQU__SALES__56158A5A | Default | Column Constraint | SALES11 DEFAULT 0 |
DF__STOCKREQU__SALES__5709AE93 | Default | Column Constraint | SALES12 DEFAULT 0 |
DF__STOCKREQU__SALES__7795AE5F | Default | Column Constraint | SALES0 DEFAULT 0 |
DF__STOCKREQU__SALES__7889D298 | Default | Column Constraint | SALES1 DEFAULT 0 |
DF__STOCKREQU__SALES__797DF6D1 | Default | Column Constraint | SALES2 DEFAULT 0 |
DF__STOCKREQU__SALES__7A721B0A | Default | Column Constraint | SALES3 DEFAULT 0 |
DF__STOCKREQU__SALES__7B663F43 | Default | Column Constraint | SALES4 DEFAULT 0 |
DF__STOCKREQU__SALES__7C5A637C | Default | Column Constraint | SALES5 DEFAULT 0 |
DF__STOCKREQU__SALES__7D4E87B5 | Default | Column Constraint | SALES6 DEFAULT 0 |
DF__STOCKREQU__STOCK__1ADEEA9C | Default | Column Constraint | STOCKGROUP DEFAULT 0 |
DF__STOCKREQU__VIRTS__2097C3F2 | Default | Column Constraint | VIRTSTOCK DEFAULT 0 |
STOCK_GROUPS_STOCKREQUIREMENT | Foreign Key | Table Constraint | FOREIGN KEY (STOCKGROUP) REFERENCES STOCK_GROUPS(GROUPNO) |
STOCK_ITEMS_STOCKREQUIREMENT | Foreign Key | Table Constraint | FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE) |
STOCK_LOCATIONS_STOCKREQUIREMENT | Foreign Key | Table Constraint | FOREIGN KEY (LOCNO) REFERENCES STOCK_LOCATIONS(LOCNO) |
Entity: STOCKTAKE_CTRL
Comment | This table contains information on the status of stocktake locations. |
Primary key columns | LOCNO |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
LOCNO | Yes | INTEGER | Yes | The ID number of the stock location. |
LOCNAME | No | VARCHAR(30) | No | The name of the stock location. |
ISACTIVE | No | CHAR(1) | No | This field contains 'Y' is the location is currently active. |
TIME_INITIALISED | No | DATETIME | No | The date and time when the location was initialised for stocktake. |
TIME_COUNTSHEETS | No | DATETIME | No | The date and time when count sheets were printed for the location. |
TIME_KEYED | No | DATETIME | No | The date and time when counted quantities were keyed for the location. |
TIME_UPLOADED | No | DATETIME | No | The date and time when stock transactions were uploaded to the Stock ledger. |
CUSTOM_FILTER | No | VARCHAR(100) | No | This field contains the custom SQL filter defined for the location, if there is one. |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
STOCK_LOCATIONS_STOCKTAKE_CTRL | Identifying | STOCK_LOCATIONS | STOCKTAKE_CTRL | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (LOCNO) |
DF__STOCKTAKE__CUSTO__3FBC34E7 | Default | Column Constraint | CUSTOM_FILTER DEFAULT N |
DF__STOCKTAKE__ISACT__6CC31A31 | Default | Column Constraint | ISACTIVE DEFAULT N |
PK__STOCKTAKE_CTRL__6BCEF5F8 | Primary Key | Table Constraint | PRIMARY KEY (LOCNO) |
STOCK_LOCATIONS_STOCKTAKE_CTRL | Foreign Key | Table Constraint | FOREIGN KEY (LOCNO) REFERENCES STOCK_LOCATIONS(LOCNO) |
Entity: STOCKTAKE_TOTALS
Comment | This table contains the stock totals entered as part of a stocktake. |
Primary key columns | SEQNO |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
SEQNO | Yes | INTEGER | Yes | A unique ID number for the stocktake line. |
STOCKCODE | No | VARCHAR(23) | No | The ID code of the stock item being counted. |
DESCRIPTION | No | VARCHAR(40) | No | The description of the stock item being counted. |
LOCATION | No | INTEGER | No | The ID number of the location where the stock was counted. |
BINCODE | No | VARCHAR(12) | No | The Bin Code for the stock item being counted. |
SYSTEMQTY | No | FLOAT(53) | No | The quantity recorded in the EXO Business system for the item at this location. |
COUNTQTY | No | FLOAT(53) | No | The physical count taken for the item at this location. |
VARIANCE | No | FLOAT(53) | No | The difference between the system quantity and the counted quantity. Populated automatically when the Stocktake Variance Report is run for the Stock Take utility. |
SUPPLIERNO | No | INTEGER | No | The ID number of the Creditor who supplies the stock item. |
STOCKGROUP | No | INTEGER | No | The Stock Group that the stock item belongs to. |
BATCHCODE | No | VARCHAR(20) | No | The batchcode for the stock item. |
UNITCOST | No | FLOAT(53) | No | The unit cost for the stock item. Unit costs are only entered during stock take if the "Initialise cost prices" stock take option is enabled. |
STOCKGROUP2 | No | INTEGER | No | The Alternative Stock Group |
HAS_BN | No | CHAR(1) | Yes | This field is set to 'Y' if the stock item is batch tracked. |
SERIALNO_TYPE | No | INTEGER | No | 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 |
HAS_EXPIRY | No | CHAR(1) | Yes | This field is set to 'Y' if the stock item has an expiry date. |
EXPIRY_DAYS | No | INTEGER | No | The number of days until the stock item batch expires. |
EXPIRY_DATE | No | DATETIME | No | The date when the stock item batch expires. |
STOCKGROUP_REPC | No | VARCHAR(15) | No | The report code of the stock item's Primary Stock Group. |
STOCKGROUP2_REPC | No | VARCHAR(15) | No | The report code of the stock item's Secondary Stock Group. |
BARCODE1 | No | VARCHAR(30) | No | Barcode/alternate code 1. |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
STOCK_GROUP2S_STOCKTAKE_TOTALS | Non Identifying | STOCK_GROUP2S | STOCKTAKE_TOTALS | Zero Or More |
STOCK_GROUP2S_STOCKTAKE_TOTALS_REPCODE | Non Identifying | STOCK_GROUP2S | STOCKTAKE_TOTALS | Zero Or More |
STOCK_GROUPS_STOCKTAKE_TOTALS | Non Identifying | STOCK_GROUPS | STOCKTAKE_TOTALS | Zero Or More |
STOCK_GROUPS_STOCKTAKE_TOTALS_REPCODE | Non Identifying | STOCK_GROUPS | STOCKTAKE_TOTALS | 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_LOCATIONS_STOCKTAKE_TOTALS | Non Identifying | STOCK_LOCATIONS | STOCKTAKE_TOTALS | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (HAS_EXPIRY) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| Not Null | Column Constraint | NOT NULL (HAS_BN) |
DF__STOCKTAKE__COUNT__5DD5DC5C | Default | Column Constraint | COUNTQTY DEFAULT 0 |
DF__STOCKTAKE__EXPIR__41A47D59 | Default | Column Constraint | EXPIRY_DAYS DEFAULT 1 |
DF__STOCKTAKE__HAS_B__3DD3EC75 | Default | Column Constraint | HAS_BN DEFAULT N |
DF__STOCKTAKE__HAS_E__40B05920 | Default | Column Constraint | HAS_EXPIRY DEFAULT N |
DF__STOCKTAKE__SERIA__3EC810AE | Default | Column Constraint | SERIALNO_TYPE DEFAULT 0 |
DF__STOCKTAKE__STOCK__3CDFC83C | Default | Column Constraint | STOCKGROUP2 DEFAULT 0 |
DF__STOCKTAKE__STOCK__60B24907 | Default | Column Constraint | STOCKGROUP DEFAULT 0 |
DF__STOCKTAKE__SUPPL__5FBE24CE | Default | Column Constraint | SUPPLIERNO DEFAULT 0 |
DF__STOCKTAKE__SYSTE__5CE1B823 | Default | Column Constraint | SYSTEMQTY DEFAULT 0 |
DF__STOCKTAKE__UNITC__3BEBA403 | Default | Column Constraint | UNITCOST DEFAULT 0 |
DF__STOCKTAKE__VARIA__5ECA0095 | Default | Column Constraint | VARIANCE DEFAULT 0 |
PK_STOCKTAKE_TOTALS | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
STOCK_GROUP2S_STOCKTAKE_TOTALS | Foreign Key | Table Constraint | FOREIGN KEY (STOCKGROUP2) REFERENCES STOCK_GROUP2S(GROUPNO) |
STOCK_GROUP2S_STOCKTAKE_TOTALS_REPCODE | Foreign Key | Table Constraint | FOREIGN KEY (STOCKGROUP2_REPC) REFERENCES STOCK_GROUP2S(REPORTCODE) |
STOCK_GROUPS_STOCKTAKE_TOTALS | Foreign Key | Table Constraint | FOREIGN KEY (STOCKGROUP) REFERENCES STOCK_GROUPS(GROUPNO) |
STOCK_GROUPS_STOCKTAKE_TOTALS_REPCODE | Foreign Key | Table Constraint | FOREIGN KEY (STOCKGROUP_REPC) REFERENCES STOCK_GROUPS(REPORTCODE) |
STOCK_ITEMS_STOCKTAKE_TOTALS | Foreign Key | Table Constraint | FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE) |
STOCK_ITEMS_STOCKTAKE_TOTALS_BN | Foreign Key | Table Constraint | FOREIGN KEY (HAS_BN) REFERENCES STOCK_ITEMS(HAS_BN) |
STOCK_ITEMS_STOCKTAKE_TOTALS_EXPDAYS | Foreign Key | Table Constraint | FOREIGN KEY (EXPIRY_DAYS) REFERENCES STOCK_ITEMS(EXPIRY_DAYS) |
STOCK_ITEMS_STOCKTAKE_TOTALS_EXPIRY | Foreign Key | Table Constraint | FOREIGN KEY (HAS_EXPIRY) REFERENCES STOCK_ITEMS(HAS_EXPIRY) |
STOCK_ITEMS_STOCKTAKE_TOTALS_SERIAL | Foreign Key | Table Constraint | FOREIGN KEY (SERIALNO_TYPE) REFERENCES STOCK_ITEMS(SERIALNO_TYPE) |
STOCK_LOCATIONS_STOCKTAKE_TOTALS | Foreign Key | Table Constraint | FOREIGN KEY (LOCATION) REFERENCES STOCK_LOCATIONS(LOCNO) |
Entity: STOCK_CLASSIFICATIONS
Comment | This table stores the Stock Classifications that are assigned to stock items. |
Primary key columns | CLASSNO |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
CLASSNO | Yes | INTEGER | Yes | A unique ID number for the classification. |
CLASSNAME | No | VARCHAR(100) | Yes | The name of the classification. |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
STOCK_CLASSIFICATIONS_STOCK_ITEMS | Non Identifying | STOCK_CLASSIFICATIONS | STOCK_ITEMS | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (CLASSNAME) |
| Not Null | Column Constraint | NOT NULL (CLASSNO) |
PK__STOCK_CLASSIFICA__0E391C95 | Primary Key | Table Constraint | PRIMARY KEY (CLASSNO) |
Entity: STOCK_COLOUR
Comment | This table contains details of the colours used by the Style, Colour, Size feature. |
Primary key columns | COLOURID |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
COLOURID | Yes | INTEGER | Yes | A unique ID number for the colour. |
COLOURCODE | No | VARCHAR(5) | Yes | A user-defined ID code for the colour. |
COLOURNAME | No | VARCHAR(30) | No | A descriptive name for the colour. |
SWATCHID | No | INTEGER | No | The colour value of the colour record's swatch. |
ISACTIVE | No | CHAR(1) | No | This field contains 'Y' if the colour is currently active. |
SORTORDER | No | INTEGER | No | This field contains the sort order value, which is used to order colours on the Style Picker window. |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (COLOURCODE) |
| Not Null | Column Constraint | NOT NULL (COLOURID) |
DF__STOCK_COL__ISACT__3EC810AE | Default | Column Constraint | ISACTIVE DEFAULT Y |
DF__STOCK_COL__SORTO__05113BBC | Default | Column Constraint | SORTORDER DEFAULT 0 |
DF__STOCK_COL__SWATC__3DD3EC75 | Default | Column Constraint | SWATCHID DEFAULT -1 |
PK__STOCK_COLOUR__06599A59 | Primary Key | Table Constraint | PRIMARY KEY (COLOURID) |
Entity: STOCK_GROUP2S
Comment | This table contains details of Alternate (Coarse) Stock Groups. |
Primary key columns | GROUPNO |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
GROUPNO | Yes | INTEGER | Yes | A unique ID number for the group. |
GROUPNAME | No | VARCHAR(30) | No | The group's name/description. |
BRANCHNO | No | INTEGER | No | The ID number of the group's branch (from BRANCHES). |
STATUS | No | VARCHAR(1) | No | This field contains 'Y' if the 'Display on website option' for the group is ticked. |
FILENAME | No | VARCHAR(50) | No | This field contains the filename of the website picture for the group. |
DATAX_EXCHANGE_FLAG | No | CHAR(1) | Yes | |
DATAX_SOURCE_SITE | No | INTEGER | Yes | |
DATAX_EXCHNO | No | INTEGER | Yes | |
DATAX_SITE_NO2 | No | INTEGER | Yes | |
AUTOCODE | No | VARCHAR(10) | No | The One-off Stock Item autocode prefix for the group. |
AUTOCODENO | No | INTEGER | No | The One-off Stock Item autocode suffix for the group. |
REPORTCODE | No | VARCHAR(15) | No | A report code for the group. |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
BRANCHES_STOCK_GROUP2S | Non Identifying | BRANCHES | STOCK_GROUP2S | Zero Or More |
FK_CRM_BUDGET_EXCLUDED_STOCK_GROUP2S_STOCK_GROUP2S | Non Identifying | STOCK_GROUP2S | CRM_BUDGET_EXCLUDED_STOCK_GROUP2S | Zero Or More |
FK_CRM_BUDGET_LINE_STOCK_GROUP2S | Non Identifying | STOCK_GROUP2S | CRM_BUDGET_LINE | Zero Or More |
STOCK_GROUP2S_STOCKTAKE_TOTALS | Non Identifying | STOCK_GROUP2S | STOCKTAKE_TOTALS | Zero Or More |
STOCK_GROUP2S_STOCKTAKE_TOTALS_REPCODE | Non Identifying | STOCK_GROUP2S | STOCKTAKE_TOTALS | Zero Or More |
STOCK_GROUP2S_STOCK_GROUPS | Non Identifying | STOCK_GROUP2S | STOCK_GROUPS | Zero Or More |
STOCK_GROUP2S_STOCK_ITEMS | Non Identifying | STOCK_GROUP2S | STOCK_ITEMS | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (DATAX_SITE_NO2) |
| Not Null | Column Constraint | NOT NULL (GROUPNO) |
| Not Null | Column Constraint | NOT NULL (DATAX_EXCHNO) |
| Not Null | Column Constraint | NOT NULL (DATAX_SOURCE_SITE) |
| Not Null | Column Constraint | NOT NULL (DATAX_EXCHANGE_FLAG) |
BRANCHES_STOCK_GROUP2S | Foreign Key | Table Constraint | FOREIGN KEY (BRANCHNO) REFERENCES BRANCHES(BRANCHNO) |
DF__STOCK_GRO__BRANC__72D0F942 | Default | Column Constraint | BRANCHNO DEFAULT 0 |
DF__STOCK_GRO__DATAX__74B941B4 | Default | Column Constraint | DATAX_EXCHANGE_FLAG DEFAULT I |
DF__STOCK_GRO__DATAX__75AD65ED | Default | Column Constraint | DATAX_SOURCE_SITE DEFAULT 0 |
DF__STOCK_GRO__DATAX__76A18A26 | Default | Column Constraint | DATAX_EXCHNO DEFAULT 0 |
DF__STOCK_GRO__DATAX__7795AE5F | Default | Column Constraint | DATAX_SITE_NO2 DEFAULT 0 |
DF__STOCK_GRO__STATU__73C51D7B | Default | Column Constraint | STATUS DEFAULT Y |
PK__STOCK_GROUP2S__0F2D40CE | Primary Key | Table Constraint | PRIMARY KEY (GROUPNO) |
Entity: STOCK_GROUPS
Comment | This table contains details of Primary (Fine) Stock Groups. Note: All fields prefixed with X_ relate to the Style, Colour, Size feature. |
Primary key columns | GROUPNO |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
GROUPNO | Yes | INTEGER | Yes | A unique ID code for the group. |
GROUPNAME | No | VARCHAR(30) | No | The group's name/description. |
BRANCHNO | No | INTEGER | No | The ID number of the group's branch (from BRANCHES). |
AUTOCODE | No | VARCHAR(10) | No | The One-off Stock Item autocode prefix for the group. |
AUTOCODENO | No | INTEGER | No | The One-off Stock Item autocode suffix for the group. |
STATUS | No | CHAR(1) | No | This field contains 'Y' if the 'Display on website option' for the group is ticked. |
FILENAME | No | VARCHAR(50) | No | This field contains the filename of the website picture for the group. |
GROUP2_SEQNO | No | INTEGER | No | The ID number of the related Alternate Stock Group. |
REPORTCODE | No | VARCHAR(15) | No | A report code for the group. |
EXPECTEDPROFIT | No | FLOAT(53) | No | The expected profit margin for the group. |
PROFITVARIANCE | No | FLOAT(53) | No | The expected margin variance for the group. |
SALES_GL_CODE | No | INTEGER | No | The code of the GL account for Sales. |
SALES_GLSUBCODE | No | INTEGER | No | The code of the GL subaccount for Sales. |
PURCH_GL_CODE | No | INTEGER | No | The code of the GL account for Purchases Clearing. |
PURCH_GLSUBCODE | No | INTEGER | No | The code of the GL subaccount for Purchases Clearing. |
COS_GL_CODE | No | INTEGER | No | The code of the GL account for Cost of Sales. |
COS_GLSUBCODE | No | INTEGER | No | The code of the GL subaccount for Cost of Sales. |
ISACTIVE | No | CHAR(1) | No | This field contains 'Y' if the group is currently active. |
X_ISSTYLE | No | CHAR(1) | No | This field contains 'Y' if the group is a style. |
X_SIZEIDS | No | VARCHAR(255) | No | This field contains a comma-separated list of size IDs applicable to the style. |
X_COLOURIDS | No | VARCHAR(255) | No | This field contains a comma-separated list of colour IDs applicable to the style. |
X_PRICEGROUP | No | CHAR(1) | No | This field contains 'Y' if the style has a matching Price Group. |
X_STOCKCODE_FORMULA | No | VARCHAR(23) | No | The stock item code formula for the group. |
X_DESCRIPTION_FORMULA | No | VARCHAR(40) | No | The stock item description formula for the group. |
X_SUPPLIERNO | No | INTEGER | No | The ID number of the main supplier of the style. |
X_SUPPLIERCOST | No | FLOAT(53) | No | The supplier cost in the currency of the supplier account. |
X_MINSTOCK | No | FLOAT(53) | No | The minimum stock level for the style. |
X_MAXSTOCK | No | FLOAT(53) | No | The maximum stock level for the style. |
X_BINCODE | No | VARCHAR(12) | No | The bin code for the style. |
X_SELLPRICE1 | No | FLOAT(53) | No | Sell price 1 for the style. |
X_SELLPRICE2 | No | FLOAT(53) | No | Sell price 2 for the style. |
X_SELLPRICE3 | No | FLOAT(53) | No | Sell price 3 for the style. |
X_SELLPRICE4 | No | FLOAT(53) | No | Sell price 4 for the style. |
X_SELLPRICE5 | No | FLOAT(53) | No | Sell price 5 for the style. |
X_SELLPRICE6 | No | FLOAT(53) | No | Sell price 6 for the style. |
X_SELLPRICE7 | No | FLOAT(53) | No | Sell price 7 for the style. |
X_SELLPRICE8 | No | FLOAT(53) | No | Sell price 8 for the style. |
X_SELLPRICE9 | No | FLOAT(53) | No | Sell price 9 for the style. |
X_SELLPRICE10 | No | FLOAT(53) | No | Sell price 10 for the style. |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
BRANCHES_STOCK_GROUPS | Non Identifying | BRANCHES | STOCK_GROUPS | Zero Or More |
FK_CRM_BUDGET_EXCLUDED_STOCK_GROUPS_STOCK_GROUPS | Non Identifying | STOCK_GROUPS | CRM_BUDGET_EXCLUDED_STOCK_GROUPS | Zero Or More |
FK_CRM_BUDGET_LINE_STOCK_GROUPS | Non Identifying | STOCK_GROUPS | CRM_BUDGET_LINE | 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 |
STOCK_GROUP2S_STOCK_GROUPS | Non Identifying | STOCK_GROUP2S | STOCK_GROUPS | Zero Or More |
STOCK_GROUPS_STOCKREQUIREMENT | Non Identifying | STOCK_GROUPS | STOCKREQUIREMENT | Zero Or More |
STOCK_GROUPS_STOCKTAKE_TOTALS | Non Identifying | STOCK_GROUPS | STOCKTAKE_TOTALS | Zero Or More |
STOCK_GROUPS_STOCKTAKE_TOTALS_REPCODE | Non Identifying | STOCK_GROUPS | STOCKTAKE_TOTALS | Zero Or More |
STOCK_GROUPS_STOCK_ITEMS | Non Identifying | STOCK_GROUPS | STOCK_ITEMS | Zero Or More |
STOCK_PRICEGROUPS_STOCK_GROUPS | Non Identifying | STOCK_PRICEGROUPS | STOCK_GROUPS | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (GROUPNO) |
BRANCHES_STOCK_GROUPS | Foreign Key | Table Constraint | FOREIGN KEY (BRANCHNO) REFERENCES BRANCHES(BRANCHNO) |
DF__STOCK_GRO__BRANC__60C757A0 | Default | Column Constraint | BRANCHNO DEFAULT 0 |
DF__STOCK_GRO__COS_G__2BB53C3A | Default | Column Constraint | COS_GL_CODE DEFAULT 0 |
DF__STOCK_GRO__COS_G__2CA96073 | Default | Column Constraint | COS_GLSUBCODE DEFAULT 0 |
DF__STOCK_GRO__EXPEC__1A5FC7AF | Default | Column Constraint | EXPECTEDPROFIT DEFAULT 0 |
DF__STOCK_GRO__GROUP__03FB8544 | Default | Column Constraint | GROUP2_SEQNO DEFAULT 0 |
DF__STOCK_GRO__ISACT__2F85CD1E | Default | Column Constraint | ISACTIVE DEFAULT Y |
DF__STOCK_GRO__PROFI__1B53EBE8 | Default | Column Constraint | PROFITVARIANCE DEFAULT 0 |
DF__STOCK_GRO__PURCH__29CCF3C8 | Default | Column Constraint | PURCH_GL_CODE DEFAULT 0 |
DF__STOCK_GRO__PURCH__2AC11801 | Default | Column Constraint | PURCH_GLSUBCODE DEFAULT 0 |
DF__STOCK_GRO__SALES__27E4AB56 | Default | Column Constraint | SALES_GL_CODE DEFAULT 0 |
DF__STOCK_GRO__SALES__28D8CF8F | Default | Column Constraint | SALES_GLSUBCODE DEFAULT 0 |
DF__STOCK_GRO__STATU__1DD065E0 | Default | Column Constraint | STATUS DEFAULT N |
DF__STOCK_GRO__X_ISS__3FBC34E7 | Default | Column Constraint | X_ISSTYLE DEFAULT N |
DF__STOCK_GRO__X_MAX__7ADCFAB5 | Default | Column Constraint | X_MAXSTOCK DEFAULT 0 |
DF__STOCK_GRO__X_MIN__79E8D67C | Default | Column Constraint | X_MINSTOCK DEFAULT 0 |
DF__STOCK_GRO__X_PRI__4298A192 | Default | Column Constraint | X_PRICEGROUP DEFAULT Y |
DF__STOCK_GRO__X_SEL__0095D40B | Default | Column Constraint | X_SELLPRICE5 DEFAULT 0 |
DF__STOCK_GRO__X_SEL__0189F844 | Default | Column Constraint | X_SELLPRICE6 DEFAULT 0 |
DF__STOCK_GRO__X_SEL__027E1C7D | Default | Column Constraint | X_SELLPRICE7 DEFAULT 0 |
DF__STOCK_GRO__X_SEL__037240B6 | Default | Column Constraint | X_SELLPRICE8 DEFAULT 0 |
DF__STOCK_GRO__X_SEL__046664EF | Default | Column Constraint | X_SELLPRICE9 DEFAULT 0 |
DF__STOCK_GRO__X_SEL__055A8928 | Default | Column Constraint | X_SELLPRICE10 DEFAULT 0 |
DF__STOCK_GRO__X_SEL__7CC54327 | Default | Column Constraint | X_SELLPRICE1 DEFAULT 0 |
DF__STOCK_GRO__X_SEL__7DB96760 | Default | Column Constraint | X_SELLPRICE2 DEFAULT 0 |
DF__STOCK_GRO__X_SEL__7EAD8B99 | Default | Column Constraint | X_SELLPRICE3 DEFAULT 0 |
DF__STOCK_GRO__X_SEL__7FA1AFD2 | Default | Column Constraint | X_SELLPRICE4 DEFAULT 0 |
DF__STOCK_GRO__X_SUP__78008E0A | Default | Column Constraint | X_SUPPLIERNO DEFAULT 0 |
DF__STOCK_GRO__X_SUP__78F4B243 | Default | Column Constraint | X_SUPPLIERCOST DEFAULT 0 |
GLACCS_STOCK_GROUPS | Foreign Key | Table Constraint | FOREIGN KEY (SALES_GL_CODE) REFERENCES GLACCS(ACCNO) |
GLACCS_STOCK_GROUPS_COS | Foreign Key | Table Constraint | FOREIGN KEY (COS_GL_CODE) REFERENCES GLACCS(ACCNO) |
GLACCS_STOCK_GROUPS_P | Foreign Key | Table Constraint | FOREIGN KEY (PURCH_GL_CODE) REFERENCES GLACCS(ACCNO) |
PK__STOCK_GROUPS__0F183235 | Primary Key | Table Constraint | PRIMARY KEY (GROUPNO) |
STOCK_GROUP2S_STOCK_GROUPS | Foreign Key | Table Constraint | FOREIGN KEY (GROUP2_SEQNO) REFERENCES STOCK_GROUP2S(GROUPNO) |
STOCK_PRICEGROUPS_STOCK_GROUPS | Foreign Key | Table Constraint | FOREIGN KEY (X_PRICEGROUP) REFERENCES STOCK_PRICEGROUPS(GROUPNO) |
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: STOCK_LOCATIONS
Comment | This table contains details of all stock locations. |
Primary key columns | LOCNO |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
LOCNO | Yes | INTEGER | Yes | A unique ID number for the location. |
LCODE | No | VARCHAR(8) | No | A short alphanumeric ID code for the location. |
LNAME | No | VARCHAR(30) | No | A descriptive name for the location. |
ISACTIVE | No | CHAR(1) | No | This field contains 'Y' if the location is active. |
EXCLUDE_FROMVALUATION | No | CHAR(1) | No | This field contains 'Y' if the location's "Exclude from Valuation" option is ticked. |
EXCLUDE_FROMFREE_STOCK | No | CHAR(1) | No | This field contains 'Y' if the location's "Exclude from Free Stock" option is ticked. |
EXCLUDE_FROMSALES | No | CHAR(1) | No | This field contains 'Y' if the location's "Exclude from Sales" option is ticked. |
DELADDR1 | No | VARCHAR(30) | No | Line 1 of the location's address. |
DELADDR2 | No | VARCHAR(30) | No | Line 2 of the location's address. |
DELADDR3 | No | VARCHAR(30) | No | Line 3 of the location's address. |
DELADDR4 | No | VARCHAR(30) | No | Line 4 of the location's address. |
DELADDR5 | No | VARCHAR(30) | No | Line 5 of the location's address. |
DELADDR6 | No | VARCHAR(30) | No | Line 6 of the location's address. |
IS_WIPLOCATION | No | CHAR(1) | Yes | This field contains 'Y' if the location represents Work in Progress in EXO Job Costing. |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
STOCK_LOCATIONS_BILLOMAT_TEMP | Non Identifying | STOCK_LOCATIONS | BILLOMAT_TEMP | Zero Or More |
STOCK_LOCATIONS_BILLOMAT_TEMP_PROD | Non Identifying | STOCK_LOCATIONS | BILLOMAT_TEMP | Zero Or More |
STOCK_LOCATIONS_INWARDS_GOODS | Non Identifying | STOCK_LOCATIONS | INWARDS_GOODS | Zero Or More |
STOCK_LOCATIONS_INWARDS_GOODS_LINES | Non Identifying | STOCK_LOCATIONS | INWARDS_GOODS_LINES | Zero Or More |
STOCK_LOCATIONS_JOB_OUTPUT_ITEMS | Non Identifying | STOCK_LOCATIONS | JOB_OUTPUT_ITEMS | Zero Or More |
STOCK_LOCATIONS_STOCKREQUIREMENT | Non Identifying | STOCK_LOCATIONS | STOCKREQUIREMENT | Zero Or More |
STOCK_LOCATIONS_STOCKTAKE_CTRL | Identifying | STOCK_LOCATIONS | STOCKTAKE_CTRL | Zero Or More |
STOCK_LOCATIONS_STOCKTAKE_TOTALS | Non Identifying | STOCK_LOCATIONS | STOCKTAKE_TOTALS | Zero Or More |
STOCK_LOCATIONS_STOCK_LOC_INFO | Non Identifying | STOCK_LOCATIONS | STOCK_LOC_INFO | Zero Or More |
STOCK_LOCATIONS_STOCK_SERIALNOS | Non Identifying | STOCK_LOCATIONS | STOCK_SERIALNOS | Zero Or More |
STOCK_LOCATIONS_STOCK_TRANS | Non Identifying | STOCK_LOCATIONS | STOCK_TRANS | Zero Or More |
STOCK_LOCATIONS_STOCK_TRANS_TO | Non Identifying | STOCK_LOCATIONS | STOCK_TRANS | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (IS_WIPLOCATION) |
| Not Null | Column Constraint | NOT NULL (LOCNO) |
DF__STOCK_LOC__EXCLU__671F4F74 | Default | Column Constraint | EXCLUDE_FROMVALUATION DEFAULT N |
DF__STOCK_LOC__EXCLU__681373AD | Default | Column Constraint | EXCLUDE_FROMFREE_STOCK DEFAULT N |
DF__STOCK_LOC__EXCLU__690797E6 | Default | Column Constraint | EXCLUDE_FROMSALES DEFAULT N |
DF__STOCK_LOC__ISACT__05AEC38C | Default | Column Constraint | ISACTIVE DEFAULT Y |
DF__STOCK_LOC__IS_WI__6909F9EB | Default | Column Constraint | IS_WIPLOCATION DEFAULT N |
PK__STOCK_LOC__LOCNO__04BA9F53 | Primary Key | Table Constraint | PRIMARY KEY (LOCNO) |
Triggers:
Trigger name | Code |
AFTER_STOCK_LOCATIONS_DELETE | CREATE TRIGGER [dbo].[AFTER_STOCK_LOCATIONS_DELETE] ON [dbo].[STOCK_LOCATIONS] FOR DELETE AS SET NOCOUNT ON DELETE FROM STOCK_LOC_INFO WHERE LOCATION IN (SELECT LOCNO FROM DELETED) DELETE FROM STOCKTAKE_CTRL WHERE LOCNO IN (SELECT LOCNO FROM DELETED) SET NOCOUNT OFF
|
STOCK_LOCATIONS_LOC_INFO | /****** Object: Trigger [dbo].[STOCK_LOCATIONS_LOC_INFO] Script Date: 18/12/2001 12:51:36 ******/ CREATE TRIGGER [STOCK_LOCATIONS_LOC_INFO] ON [[dbo].[STOCK_LOCATIONS]] FOR INSERT AS BEGIN SET NOCOUNT ON INSERT INTO STOCK_LOC_INFO (STOCKCODE, LOCATION, MINSTOCK, MAXSTOCK, QTY) SELECT SI.STOCKCODE, I.LOCNO, SI.MINSTOCK, SI.MAXSTOCK, 0 FROM STOCK_ITEMS SI, INSERTED I SET NOCOUNT OFF END
|
STOCK_LOCATIONS_STOCKTAKE | CREATE TRIGGER [dbo].[STOCK_LOCATIONS_STOCKTAKE] ON [dbo].[STOCK_LOCATIONS] FOR INSERT AS BEGIN SET NOCOUNT ON INSERT INTO STOCKTAKE_CTRL (LOCNO, LOCNAME, ISACTIVE) SELECT I.LOCNO, I.LNAME, 'Y' FROM INSERTED I SET NOCOUNT OFF END
|
Entity: STOCK_LOC_INFO
Comment | This table records stock levels at various locations. |
Primary key columns | STOCKCODE |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
STOCKCODE | Yes | VARCHAR(23) | Yes | The unique ID code of the stock item. |
LOCATION | No | INTEGER | Yes | The ID number of the location. |
BINCODE | No | VARCHAR(12) | No | The stock item's bin or shelf code at the location. |
MINSTOCK | No | FLOAT(53) | No | The minimum stock quantity for the stock code at the location. |
MAXSTOCK | No | FLOAT(53) | No | The maximum stock quantity for the stock code at the location. |
QTY | No | FLOAT(53) | Yes | The current stock quantity for the stock code at the location. |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
STOCK_ITEMS_STOCK_LOC_INFO | Identifying | STOCK_ITEMS | STOCK_LOC_INFO | Zero Or More |
STOCK_LOCATIONS_STOCK_LOC_INFO | Non Identifying | STOCK_LOCATIONS | STOCK_LOC_INFO | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (QTY) |
| Not Null | Column Constraint | NOT NULL (STOCKCODE) |
| Not Null | Column Constraint | NOT NULL (LOCATION) |
DF__STOCK_LOC_I__QTY__507BE13E | Default | Column Constraint | QTY DEFAULT 0 |
DF__STOCK_LOC__MAXST__162F4418 | Default | Column Constraint | MAXSTOCK DEFAULT 0 |
DF__STOCK_LOC__MINST__153B1FDF | Default | Column Constraint | MINSTOCK DEFAULT 0 |
PK__STOCK_LOC_INFO__1446FBA6 | Primary Key | Table Constraint | PRIMARY KEY (STOCKCODE) |
STOCK_ITEMS_STOCK_LOC_INFO | Foreign Key | Table Constraint | FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE) |
STOCK_LOCATIONS_STOCK_LOC_INFO | Foreign Key | Table Constraint | FOREIGN KEY (LOCATION) REFERENCES STOCK_LOCATIONS(LOCNO) |
Entity: STOCK_PRICEGROUPS
Comment | This table stores the Price Groups that are assigned to stock items. |
Primary key columns | GROUPNO |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
GROUPNO | Yes | INTEGER | Yes | A unique ID number for the Price Group. |
GROUPNAME | No | VARCHAR(30) | No | The Price Group's name. |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
STOCK_PRICEGROUPS_STOCK_GROUPS | Non Identifying | STOCK_PRICEGROUPS | STOCK_GROUPS | Zero Or More |
STOCK_PRICEGROUPS_STOCK_ITEMS | Non Identifying | STOCK_PRICEGROUPS | STOCK_ITEMS | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (GROUPNO) |
PK__STOCK_PRICEGROUP__13F1F5EB | Primary Key | Table Constraint | PRIMARY KEY (GROUPNO) |
Entity: STOCK_REQUESTLINES
Comment | This table is used by the Stock Transfer Requests feature of the Distribution Advantage module. |
Primary key columns | SEQNO |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
SEQNO | Yes | INTEGER | Yes | A unique ID number for the stock transfer request line. |
HDR_SEQNO | No | INTEGER | Yes | The ID number of the stock transfer request that the line belongs to (from STOCK_REQUESTS). |
STOCKCODE | No | VARCHAR(23) | Yes | The ID code of the stock item on the line. |
DESCRIPTION | No | VARCHAR(40) | Yes | The description of the stock item on the line. |
PACK_SIZE | No | FLOAT(53) | No | The pack size of the stock item on the line. |
REQ_QUANT | No | FLOAT(53) | No | The Requested Qty on the line. |
SUP_QUANT | No | FLOAT(53) | No | The Received Qty on the line. |
COMMENT | No | VARCHAR(30) | No | The comment entered on the line, if any. |
BATCHCODE | No | VARCHAR(20) | No | The batch code on the line. |
LINETYPE | No | INTEGER | No | This field indicates the type of line. Will be one of: 0 = Normal stockline 1 = BOM header line 2 = BOM component line 4 = Narrative line 5 = GL code |
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 ID number for the narrative on this line. |
LOST_QUANT | No | FLOAT(53) | No | |
SENT_QUANT | No | FLOAT(53) | No | The Sent Qty on the line. |
SEND_NOW | No | FLOAT(53) | No | The Send Now quantity on the line. |
SUP_NOW | No | FLOAT(53) | No | The Receive Now quantity on the line. |
INTRANS_QUANT | No | AS | No | The In Transit Qty on the line. |
SOLINEID | No | INTEGER | Yes | |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
NARRATIVES_STOCK_REQUESTLINES | Non Identifying | NARRATIVES | STOCK_REQUESTLINES | 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_REQUESTS_STOCK_REQUESTLINES | Non Identifying | STOCK_REQUESTS | STOCK_REQUESTLINES | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (SOLINEID) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| Not Null | Column Constraint | NOT NULL (HDR_SEQNO) |
| Not Null | Column Constraint | NOT NULL (DESCRIPTION) |
| Not Null | Column Constraint | NOT NULL (STOCKCODE) |
DF__STOCK_REQ__BOMPR__61BC4730 | Default | Column Constraint | BOMPRICING DEFAULT N |
DF__STOCK_REQ__BOMTY__5EDFDA85 | Default | Column Constraint | BOMTYPE DEFAULT N |
DF__STOCK_REQ__HDR_S__7953D99F | Default | Column Constraint | HDR_SEQNO DEFAULT 0 |
DF__STOCK_REQ__LINET__5DEBB64C | Default | Column Constraint | LINETYPE DEFAULT -1 |
DF__STOCK_REQ__LINKE__60C822F7 | Default | Column Constraint | LINKEDSTATUS DEFAULT N |
DF__STOCK_REQ__LOST___6ECC298B | Default | Column Constraint | LOST_QUANT DEFAULT 0 |
DF__STOCK_REQ__PACK___7A47FDD8 | Default | Column Constraint | PACK_SIZE DEFAULT 0 |
DF__STOCK_REQ__REQ_Q__7B3C2211 | Default | Column Constraint | REQ_QUANT DEFAULT 0 |
DF__STOCK_REQ__SEND___70B471FD | Default | Column Constraint | SEND_NOW DEFAULT 0 |
DF__STOCK_REQ__SENT___6FC04DC4 | Default | Column Constraint | SENT_QUANT DEFAULT 0 |
DF__STOCK_REQ__SHOWL__5FD3FEBE | Default | Column Constraint | SHOWLINE DEFAULT Y |
DF__STOCK_REQ__SOLIN__7B3CED68 | Default | Column Constraint | SOLINEID DEFAULT -1 |
DF__STOCK_REQ__SUP_N__71A89636 | Default | Column Constraint | SUP_NOW DEFAULT 0 |
DF__STOCK_REQ__SUP_Q__7D246A83 | Default | Column Constraint | SUP_QUANT DEFAULT 0 |
NARRATIVES_STOCK_REQUESTLINES | Foreign Key | Table Constraint | FOREIGN KEY (NARRATIVE_SEQNO) REFERENCES NARRATIVES(SEQNO) |
PK__STOCK_REQUESTLIN__785FB566 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
STOCK_ITEMS_STOCK_REQUESTLINES | Foreign Key | Table Constraint | FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE) |
STOCK_ITEMS_STOCK_REQUESTLINES_DESC | Foreign Key | Table Constraint | FOREIGN KEY (DESCRIPTION) REFERENCES STOCK_ITEMS(DESCRIPTION) |
STOCK_REQUESTS_STOCK_REQUESTLINES | Foreign Key | Table Constraint | FOREIGN KEY (HDR_SEQNO) REFERENCES STOCK_REQUESTS(SEQNO) |
Entity: STOCK_REQUESTS
Comment | This table is used by the Stock Transfer Requests feature of the Distribution Advantage module. |
Primary key columns | SEQNO |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
SEQNO | Yes | INTEGER | Yes | A unique ID number for the transfer request |
FROM_LOC | No | INTEGER | No | The ID number of the location that stock is to be transferred from. |
TO_LOC | No | INTEGER | No | The ID number of the location that stock is to be transferred to. |
REQUEST_DATE | No | DATETIME | No | The date and time when the request was created. |
REQUIRE_DATE | No | DATETIME | No | The date and time that the request must be completed by. |
STAFFNO | No | INTEGER | No | The ID number of the staff member assigned to the request. |
STATUS | No | INTEGER | No | The status of the stock transfer. Will be one of: 0 = New Request 1 = Picking 2 = In Transit 3 = Complete |
TRANSTYPE | No | INTEGER | No | The ID number of the Stock Request Type (from STOCK_REQUESTTYPES). |
CUSTORDERNO | No | VARCHAR(20) | No | The customer order number, if there is one. |
NARRATIVE_SEQNO | No | INTEGER | No | The ID number for the narrative on this request. |
ORIG_SEQNO | No | INTEGER | No | |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
NARRATIVES_STOCK_REQUESTS | Non Identifying | NARRATIVES | STOCK_REQUESTS | Zero Or More |
STAFF_STOCK_REQUESTS | Non Identifying | STAFF | STOCK_REQUESTS | Zero Or More |
STOCK_REQUESTS_STOCK_REQUESTLINES | Non Identifying | STOCK_REQUESTS | STOCK_REQUESTLINES | Zero Or More |
STOCK_REQUESTTYPES_STOCK_REQUESTS | Non Identifying | STOCK_REQUESTTYPES | STOCK_REQUESTS | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
DF__STOCK_REQ__FROM___70BE939E | Default | Column Constraint | FROM_LOC DEFAULT 0 |
DF__STOCK_REQ__ORIG___5C23696B | Default | Column Constraint | ORIG_SEQNO DEFAULT -1 |
DF__STOCK_REQ__REQUE__72A6DC10 | Default | Column Constraint | REQUEST_DATE DEFAULT getdate() |
DF__STOCK_REQ__REQUI__739B0049 | Default | Column Constraint | REQUIRE_DATE DEFAULT getdate() |
DF__STOCK_REQ__STAFF__748F2482 | Default | Column Constraint | STAFFNO DEFAULT 0 |
DF__STOCK_REQ__STATU__758348BB | Default | Column Constraint | STATUS DEFAULT 0 |
DF__STOCK_REQ__TO_LO__71B2B7D7 | Default | Column Constraint | TO_LOC DEFAULT 0 |
DF__STOCK_REQ__TRANS__76776CF4 | Default | Column Constraint | TRANSTYPE DEFAULT 0 |
NARRATIVES_STOCK_REQUESTS | Foreign Key | Table Constraint | FOREIGN KEY (NARRATIVE_SEQNO) REFERENCES NARRATIVES(SEQNO) |
PK__STOCK_REQUESTS__6FCA6F65 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
STAFF_STOCK_REQUESTS | Foreign Key | Table Constraint | FOREIGN KEY (STAFFNO) REFERENCES STAFF(STAFFNO) |
STOCK_REQUESTTYPES_STOCK_REQUESTS | Foreign Key | Table Constraint | FOREIGN KEY (TRANSTYPE) REFERENCES STOCK_REQUESTTYPES(SEQNO) |
Entity: STOCK_REQUESTTYPES
Comment | This table is used by the Stock Transfer Requests feature of the Distribution Advantage module. It stores the Stock Request Types used by stock transfer requests. |
Primary key columns | SEQNO |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
SEQNO | Yes | INTEGER | Yes | A unique ID number for the request type. |
DISPLAY_NAME | No | VARCHAR(40) | Yes | The name of the request type. |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
STOCK_REQUESTTYPES_STOCK_REQUESTS | Non Identifying | STOCK_REQUESTTYPES | STOCK_REQUESTS | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (DISPLAY_NAME) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
PK__STOCK_REQUESTTYP__7F0CB2F5 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
Entity: STOCK_SERIALNOS
Comment | This table contains details of serialised stock items. |
Primary key columns | SEQNO |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
SEQNO | Yes | INTEGER | Yes | A unique ID number for the record. |
STOCKCODE | No | VARCHAR(23) | Yes | The stock code of the serialised stock item. |
SERIALNO | No | VARCHAR(50) | No | The serial number assigned to the stock item. |
REFERENCE | No | VARCHAR(30) | No | A reference code for the record. |
EXPIRY_DATE | No | DATETIME | Yes | The expiry date on the record. |
LOCNO | No | INTEGER | Yes | The location of the serialised stock item. |
ACTUAL_COST | No | FLOAT(53) | Yes | The actual cost of the item when moved into stock. |
PURCHORDNO | No | INTEGER | Yes | The ID number of the Purchase Order that the item has been receipted in against. |
SALESORDNO | No | INTEGER | Yes | The ID number of the Sales Order ID that the item has been committed against. |
CRINVSEQNO | No | INTEGER | Yes | The ID number of the Creditors Invoice that the item has been receipted in against. |
INVSEQNO | No | INTEGER | Yes | The ID number of the Debtors Invoice ID the item has been sold against. |
JOBNO | No | INTEGER | Yes | The ID number of the job that the serial number belongs to (if there is one). |
STOCKINSEQNO | No | INTEGER | Yes | The ID number of the stock transaction that the item was receipted in against. |
STOCKOUTSEQNO | No | INTEGER | Yes | The ID number of the stock transaction against which the item was moved out of inventory. |
CR_ACCNO | No | INTEGER | Yes | The ID number of the related Creditor account. |
DR_ACCNO | No | INTEGER | Yes | The ID number of the related Debtor account. |
IGRLINESEQNO | No | INTEGER | Yes | The ID number of the Inwards Goods line that the item is linked to. |
JOBLINESEQNO | No | INTEGER | Yes | The ID number of the job line that the item is linked to. |
SALESORDLINESEQNO | No | INTEGER | Yes | The ID number of the Sales Order line that the item is linked to. |
PURCHORDLINESEQNO | No | INTEGER | Yes | The ID number of the Purchase Order line that the item is linked to. |
CRINVLINESEQNO | No | INTEGER | Yes | The ID number of the Creditors Invoice line that the item is linked to. |
DRINVLINESEQNO | No | INTEGER | Yes | The ID number of the Debtors Invoice line that the item is linked to. |
INSTOCK | No | CHAR(1) | Yes | This field contains 'Y' if the serial number is in stock. |
UNITNO | No | INTEGER | Yes | The ID number of the serviceable unit that the item is linked to. |
LASTUPDATED | No | DATETIME | Yes | The date and time when the record was last updated. |
COMPNO | No | INTEGER | Yes | |
KITCODE | No | VARCHAR(50) | No | |
KITID_SERIAL | No | VARCHAR(50) | No | |
IGRSEQNO | No | INTEGER | No | |
ISASSIGNED | No | CHAR(1) | Yes | N = Not assigned o = assigned to a Sales Order i = assigned to inwards goods j = assigned to Job Costing q = assigned to a stock request p = assigned to a POS parked Sales Order P = assigned to a POS parked invoice |
SO_INVLINESEQNO | No | INTEGER | Yes | The invoice line ID linked to the Sales Order for the serial number. |
SO_INVSEQNO | No | INTEGER | Yes | The invoice ID linked to the Sales Order for the serial number. |
PO_INVLINESEQNO | No | INTEGER | Yes | The invoice line ID linked to the Purchase Order for the serial number. |
PO_INVSEQNO | No | INTEGER | Yes | The invoice ID linked to the Purchase Order for the serial number. |
ASSIGNED_SEQNO | No | INTEGER | Yes | The header ID of the record that the serial number is linked to. The details of type of record can be determined from the ISASSIGNED field. |
ASSIGNED_LINESEQNO | No | INTEGER | Yes | The line ID of the record that the serial number is linked to. The details of type of record can be determined from the ISASSIGNED field. |
REQUESTSEQNO | No | INTEGER | No | Stock request ID |
REQUESTLINESEQNO | No | INTEGER | No | Stock request Line ID |
RMASEQNO | No | INTEGER | No | |
RMALINESEQNO | No | INTEGER | No | |
STKMOVSEQNO | No | INTEGER | No | Stock transaction header ID |
STKMOVLINESEQNO | No | INTEGER | No | Stock transaction ID |
WORKSORDSEQNO | No | INTEGER | No | Works order ID |
WORKSORDLINESEQNO | No | INTEGER | No | Works Order Line ID |
SU_SEQNO | No | INTEGER | No | Serviceable Unit ID |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
STOCK_ITEMS_STOCK_SERIALNOS | Non Identifying | STOCK_ITEMS | STOCK_SERIALNOS | Zero Or More |
STOCK_LOCATIONS_STOCK_SERIALNOS | Non Identifying | STOCK_LOCATIONS | STOCK_SERIALNOS | Zero Or More |
Indexes:
Name | Index columns | Index type |
STOCKSERIALINDEX | STOCKCODE ASC, SERIALNO ASC | |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (ASSIGNED_LINESEQNO) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| Not Null | Column Constraint | NOT NULL (STOCKCODE) |
| Not Null | Column Constraint | NOT NULL (EXPIRY_DATE) |
| Not Null | Column Constraint | NOT NULL (ASSIGNED_SEQNO) |
| Not Null | Column Constraint | NOT NULL (LOCNO) |
| Not Null | Column Constraint | NOT NULL (PO_INVSEQNO) |
| Not Null | Column Constraint | NOT NULL (ACTUAL_COST) |
| Not Null | Column Constraint | NOT NULL (PO_INVLINESEQNO) |
| Not Null | Column Constraint | NOT NULL (PURCHORDNO) |
| Not Null | Column Constraint | NOT NULL (SO_INVSEQNO) |
| Not Null | Column Constraint | NOT NULL (SALESORDNO) |
| Not Null | Column Constraint | NOT NULL (SO_INVLINESEQNO) |
| Not Null | Column Constraint | NOT NULL (CRINVSEQNO) |
| Not Null | Column Constraint | NOT NULL (ISASSIGNED) |
| Not Null | Column Constraint | NOT NULL (INVSEQNO) |
| Not Null | Column Constraint | NOT NULL (COMPNO) |
| Not Null | Column Constraint | NOT NULL (JOBNO) |
| Not Null | Column Constraint | NOT NULL (LASTUPDATED) |
| Not Null | Column Constraint | NOT NULL (STOCKINSEQNO) |
| Not Null | Column Constraint | NOT NULL (UNITNO) |
| Not Null | Column Constraint | NOT NULL (STOCKOUTSEQNO) |
| Not Null | Column Constraint | NOT NULL (INSTOCK) |
| Not Null | Column Constraint | NOT NULL (CR_ACCNO) |
| Not Null | Column Constraint | NOT NULL (DRINVLINESEQNO) |
| Not Null | Column Constraint | NOT NULL (DR_ACCNO) |
| Not Null | Column Constraint | NOT NULL (CRINVLINESEQNO) |
| Not Null | Column Constraint | NOT NULL (IGRLINESEQNO) |
| Not Null | Column Constraint | NOT NULL (PURCHORDLINESEQNO) |
| Not Null | Column Constraint | NOT NULL (JOBLINESEQNO) |
| Not Null | Column Constraint | NOT NULL (SALESORDLINESEQNO) |
DF__STOCK_SER__ACTUA__6B3AC472 | Default | Column Constraint | ACTUAL_COST DEFAULT 0 |
DF__STOCK_SER__ASSIG__15F0184D | Default | Column Constraint | ASSIGNED_SEQNO DEFAULT -1 |
DF__STOCK_SER__ASSIG__16E43C86 | Default | Column Constraint | ASSIGNED_LINESEQNO DEFAULT -1 |
DF__STOCK_SER__COMPN__143CDA05 | Default | Column Constraint | COMPNO DEFAULT -1 |
DF__STOCK_SER__CRINV__6E17311D | Default | Column Constraint | CRINVSEQNO DEFAULT -1 |
DF__STOCK_SER__CRINV__7894BF90 | Default | Column Constraint | CRINVLINESEQNO DEFAULT -1 |
DF__STOCK_SER__CR_AC__72DBE63A | Default | Column Constraint | CR_ACCNO DEFAULT -1 |
DF__STOCK_SER__DRINV__7988E3C9 | Default | Column Constraint | DRINVLINESEQNO DEFAULT -1 |
DF__STOCK_SER__DR_AC__73D00A73 | Default | Column Constraint | DR_ACCNO DEFAULT -1 |
DF__STOCK_SER__EXPIR__69527C00 | Default | Column Constraint | EXPIRY_DATE DEFAULT getdate() |
DF__STOCK_SER__IGRLI__74C42EAC | Default | Column Constraint | IGRLINESEQNO DEFAULT -1 |
DF__STOCK_SER__IGRSE__2843D2B2 | Default | Column Constraint | IGRSEQNO DEFAULT 0 |
DF__STOCK_SER__INSTO__7A7D0802 | Default | Column Constraint | INSTOCK DEFAULT Y |
DF__STOCK_SER__INVSE__6F0B5556 | Default | Column Constraint | INVSEQNO DEFAULT -1 |
DF__STOCK_SER__ISASS__617C500E | Default | Column Constraint | ISASSIGNED DEFAULT N |
DF__STOCK_SER__JOBLI__75B852E5 | Default | Column Constraint | JOBLINESEQNO DEFAULT -1 |
DF__STOCK_SER__JOBNO__6FFF798F | Default | Column Constraint | JOBNO DEFAULT -1 |
DF__STOCK_SER__LASTU__7C655074 | Default | Column Constraint | LASTUPDATED DEFAULT getdate() |
DF__STOCK_SER__LOCNO__6A46A039 | Default | Column Constraint | LOCNO DEFAULT -1 |
DF__STOCK_SER__PO_IN__1407CFDB | Default | Column Constraint | PO_INVLINESEQNO DEFAULT -1 |
DF__STOCK_SER__PO_IN__14FBF414 | Default | Column Constraint | PO_INVSEQNO DEFAULT -1 |
DF__STOCK_SER__PURCH__6C2EE8AB | Default | Column Constraint | PURCHORDNO DEFAULT -1 |
DF__STOCK_SER__PURCH__77A09B57 | Default | Column Constraint | PURCHORDLINESEQNO DEFAULT -1 |
DF__STOCK_SER__REQUE__459F2B6F | Default | Column Constraint | REQUESTSEQNO DEFAULT -1 |
DF__STOCK_SER__REQUE__46934FA8 | Default | Column Constraint | REQUESTLINESEQNO DEFAULT -1 |
DF__STOCK_SER__RMALI__487B981A | Default | Column Constraint | RMALINESEQNO DEFAULT -1 |
DF__STOCK_SER__RMASE__478773E1 | Default | Column Constraint | RMASEQNO DEFAULT -1 |
DF__STOCK_SER__SALES__6D230CE4 | Default | Column Constraint | SALESORDNO DEFAULT -1 |
DF__STOCK_SER__SALES__76AC771E | Default | Column Constraint | SALESORDLINESEQNO DEFAULT -1 |
DF__STOCK_SER__SO_IN__121F8769 | Default | Column Constraint | SO_INVLINESEQNO DEFAULT -1 |
DF__STOCK_SER__SO_IN__1313ABA2 | Default | Column Constraint | SO_INVSEQNO DEFAULT -1 |
DF__STOCK_SER__STKMO__7A87F4FA | Default | Column Constraint | STKMOVSEQNO DEFAULT -1 |
DF__STOCK_SER__STKMO__7B7C1933 | Default | Column Constraint | STKMOVLINESEQNO DEFAULT -1 |
DF__STOCK_SER__STOCK__70F39DC8 | Default | Column Constraint | STOCKINSEQNO DEFAULT -1 |
DF__STOCK_SER__STOCK__71E7C201 | Default | Column Constraint | STOCKOUTSEQNO DEFAULT -1 |
DF__STOCK_SER__SU_SE__0E79DF0E | Default | Column Constraint | SU_SEQNO DEFAULT -1 |
DF__STOCK_SER__UNITN__7B712C3B | Default | Column Constraint | UNITNO DEFAULT -1 |
DF__STOCK_SER__WORKS__3F322502 | Default | Column Constraint | WORKSORDSEQNO DEFAULT -1 |
DF__STOCK_SER__WORKS__4026493B | Default | Column Constraint | WORKSORDLINESEQNO DEFAULT -1 |
PK__STOCK_SERIALNOS__685E57C7 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
STOCK_ITEMS_STOCK_SERIALNOS | Foreign Key | Table Constraint | FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE) |
STOCK_LOCATIONS_STOCK_SERIALNOS | Foreign Key | Table Constraint | FOREIGN KEY (LOCNO) REFERENCES STOCK_LOCATIONS(LOCNO) |
Triggers:
Trigger name | Code |
STOCK_SERIALNOS_LASTUPDATED | CREATE TRIGGER [dbo].[STOCK_SERIALNOS_LASTUPDATED] ON [dbo].[STOCK_SERIALNOS] FOR UPDATE AS DECLARE @SEQNO INTEGER BEGIN SET NOCOUNT ON SELECT @SEQNO=SEQNO FROM INSERTED UPDATE STOCK_SERIALNOS SET LASTUPDATED = GETDATE() WHERE SEQNO= @SEQNO SET NOCOUNT OFF END
|
Entity: STOCK_SIZE
Comment | This table contains details of the sizes used by the Style, Colour, Size feature. |
Primary key columns | SIZEID |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
SIZEID | Yes | INTEGER | Yes | A unique ID number for the size. |
SIZECODE | No | VARCHAR(5) | Yes | A user-defined code number for the size. |
SIZENAME | No | VARCHAR(30) | No | A descriptive name for the size. |
ISACTIVE | No | CHAR(1) | No | This field contains 'Y' if the size is currently active. |
SORTORDER | No | INTEGER | No | This field contains the sort order value, which is used to order sizes on the Style Picker window. |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (SIZECODE) |
| Not Null | Column Constraint | NOT NULL (SIZEID) |
DF__STOCK_SIZ__ISACT__3AF77FCA | Default | Column Constraint | ISACTIVE DEFAULT Y |
DF__STOCK_SIZ__SORTO__041D1783 | Default | Column Constraint | SORTORDER DEFAULT 0 |
PK__STOCK_SIZE__0841E2CB | Primary Key | Table Constraint | PRIMARY KEY (SIZEID) |
Entity: STOCK_TRANS
Comment | This table records details of all stock transactions. |
Primary key columns | SEQNO |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
SEQNO | Yes | INTEGER | Yes | A unique ID for the record. |
POSTTIME | No | DATETIME | No | A timestamp of when the record was entered. |
TRANSDATE | No | DATETIME | No | The date when the stock movement took place. |
STOCKCODE | No | VARCHAR(23) | No | The ID code of the stock item. |
TRANSTYPE | No | INTEGER | No | The type of the transaction. Will be one of: 0=Sales 1=Receipt 2=Transfer 3=Adjust Out 4=Adjust In 5=Average Cost Adjustment |
REF1 | No | VARCHAR(30) | No | Source transaction reference. Usually contains the Debtor/Creditor's invoice number, Purchase Order number, Sales Order number, job number, etc. |
REF2 | No | VARCHAR(30) | No | Reference text with details of source of this stock movement. |
QUANTITY | No | FLOAT(53) | No | Quantity of stock. |
UNITPRICE | No | FLOAT(53) | No | Unit price of the stock item. |
LOCATION | No | INTEGER | No | ID number of the Location involved in the stock movement. |
TOLOCATION | No | INTEGER | No | ID of the Destination location (applies in case of stock transfers). |
FROM_LEDGER | No | CHAR(1) | No | d = Debtor c = Creditor s = Stock j = Job Costing i = Inwards Goods Receipt o = Sales Orders p = Purchase Orders |
FROM_HDR | No | INTEGER | No | For manual stock movements this field contains STOCK_TRANS_HDR.SEQNO. For other types of movements, it contains the SEQNO of the header table of source transactions. |
BATCHCODE | No | VARCHAR(20) | No | The batch number. |
ACCNO | No | INTEGER | No | For manual stock movements this field is NULL. For movements originating from other ledgers, this contains the account number against the source transaction. |
LINE_SEQNO | No | INTEGER | No | For manual stock movements this field is NULL. For movements originating from other ledgers, this contains the Line ID of the source line. |
JOBNO | No | INTEGER | No | Job Number if the stock movement sources from a job. |
RECEIPT_NO | No | INTEGER | No | Receipt number when the source of the transaction is an Inwards Goods Receipt. |
GLPOSTED | No | CHAR(1) | No | This field contains 'Y' if the transaction has been posted to the General Ledger. |
GLACC | No | INTEGER | No | The General Ledger Account that is affected when the transaction is posted to GL. |
GLSUBACC | No | INTEGER | No | The General Ledger Sub Account that is affected when the transaction is posted to GL. |
GLBRANCH | No | INTEGER | No | The Branch used for posting to the General Ledger. |
UNITCOST | No | FLOAT(53) | No | The unit cost price of the stock item. |
UPDATEITEM_FLAG | No | CHAR(1) | Yes | This field contains 'Y' for a normal stocked item, 'T' for a linked stock code, or 'U' for a transaction created for the SKU of the linked stock code. |
INC_FIFO_STOCKTAKE | No | CHAR(1) | No | Not supported. |
REVERSED | No | CHAR(1) | No | This field contains 'Y' if the transaction has been reversed. |
ON_COSTS | No | FLOAT(53) | No | On costs (only applicable to lines sourcing from inwards goods receipts). |
POST_LOOKUP_TO_GL | No | CHAR(1) | No | If this field contains 'N' and the stock item is a lookup item, there is no journal against the transaction. |
EXPIRY_DATE | No | DATETIME | No | The Expiry Date for a batch tracked item. |
GLBATCHNO | No | INTEGER | No | ID number of the GL Batch that the posted stock journal belongs to. |
KITSEQNO | No | INTEGER | Yes | ID number of the Kit/Bill of Materials, if in this specific transaction, the stock item belonged to a Kit/BOM. |
KITCODE | No | VARCHAR(23) | No | Code of the Kit/Bill of Materials, if in this specific transaction, the stock item belonged to a Kit/BOM. |
PLU | No | VARCHAR(23) | No | Price Lookup Unit (applies to lookup items). |
POST_TO_GL | No | CHAR(1) | No | If this field is set to 'Y', the stock line is posted to GL; otherwise it is not. |
PREV_QUANTITY | No | FLOAT(53) | No | Quantity of the stock item before the transaction. |
PREV_AVECOST | No | FLOAT(53) | No | Average cost of the stock item before this transaction. |
ALT_QUANTITY | No | FLOAT(53) | No | Alternate quantity. |
ALT_AVECOST | No | FLOAT(53) | No | Alternate weighted average cost. |
ALT_SEQNO | No | INTEGER | No | Alternate SEQNO. |
SESSION_ID | No | INTEGER | No | Session ID |
PERIOD_SEQNO | No | INTEGER | Yes | ID number of the period the transaction belongs to. |
AGE_STAMP | No | INTEGER | No | This is a historical field that has been superseded by the AGE calculated field. |
AGE | No | AS | No | Calculated field that indicates the age of the transaction. |
QTY_TRADED_IN_NEG | No | FLOAT(53) | No | Quantity traded when total stock on hand for the item was in negative at the time of the transaction. |
VALUE_TRADED_IN_NEG | No | FLOAT(53) | No | Value traded when total stock on hand for the item was in negative at the time of the transaction. |
PERIOD_TRADED_IN_SEQ | No | CHAR(1) | No | This field contains 'N' if the transaction was in a prior period. |
NEW_AVECOST | No | FLOAT(53) | No | Average cost of the item after this transaction. |
NEW_QUANTITY | No | FLOAT(53) | No | Stock on hand of the item after this transaction. |
NEW_LOC_QTY | No | FLOAT(53) | No | Quantity of the item in the location the movement took place after this transaction. |
NEW_SEQORDER | No | INTEGER | No | New sequence order of the transaction. |
LOOKUP_RECOVERABLE | No | CHAR(1) | No | This field contains 'Y' if lookup items are recoverable against expenses in EXO Job Costing. |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
GLACCS_STOCK_TRANS | Non Identifying | GLACCS | STOCK_TRANS | Zero Or More |
PERIOD_STATUS_STOCK_TRANS | Non Identifying | PERIOD_STATUS | STOCK_TRANS | Zero Or More |
STOCK_ITEMS_STOCK_TRANS | Non Identifying | STOCK_ITEMS | STOCK_TRANS | Zero Or More |
STOCK_LOCATIONS_STOCK_TRANS | Non Identifying | STOCK_LOCATIONS | STOCK_TRANS | Zero Or More |
STOCK_LOCATIONS_STOCK_TRANS_TO | Non Identifying | STOCK_LOCATIONS | STOCK_TRANS | Zero Or More |
Indexes:
Name | Index columns | Index type |
STOCK_TRANS_CODE | STOCKCODE ASC | |
STOCK_TRANS_PERIODSEQNO_INDEX | PERIOD_SEQNO ASC | |
STOCK_TRANS_RECEIPT_NO | RECEIPT_NO ASC | |
STOCK_TRANS_STOCKCODE_PERIODNO | STOCKCODE ASC, PERIOD_SEQNO ASC | |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (PERIOD_SEQNO) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| Not Null | Column Constraint | NOT NULL (KITSEQNO) |
| Not Null | Column Constraint | NOT NULL (UPDATEITEM_FLAG) |
DF__STOCK_TRA__AGE_S__3D5FB680 | Default | Column Constraint | AGE_STAMP DEFAULT -1 |
DF__STOCK_TRA__ALT_A__7306CEC3 | Default | Column Constraint | ALT_AVECOST DEFAULT 0 |
DF__STOCK_TRA__ALT_Q__7212AA8A | Default | Column Constraint | ALT_QUANTITY DEFAULT 0 |
DF__STOCK_TRA__ALT_S__73FAF2FC | Default | Column Constraint | ALT_SEQNO DEFAULT -1 |
DF__STOCK_TRA__GLACC__420DC656 | Default | Column Constraint | GLACC DEFAULT 0 |
DF__STOCK_TRA__GLBRA__43F60EC8 | Default | Column Constraint | GLBRANCH DEFAULT 0 |
DF__STOCK_TRA__GLPOS__3D491139 | Default | Column Constraint | GLPOSTED DEFAULT N |
DF__STOCK_TRA__GLSUB__4301EA8F | Default | Column Constraint | GLSUBACC DEFAULT 0 |
DF__STOCK_TRA__INC_F__0D64F3ED | Default | Column Constraint | INC_FIFO_STOCKTAKE DEFAULT N |
DF__STOCK_TRA__KITSE__36D1E92B | Default | Column Constraint | KITSEQNO DEFAULT -1 |
DF__STOCK_TRA__LOCAT__3726238F | Default | Column Constraint | LOCATION DEFAULT 1 |
DF__STOCK_TRA__LOOKU__6815D5B2 | Default | Column Constraint | LOOKUP_RECOVERABLE DEFAULT N |
DF__STOCK_TRA__NEW_A__78B651CF | Default | Column Constraint | NEW_AVECOST DEFAULT 0 |
DF__STOCK_TRA__NEW_L__7A9E9A41 | Default | Column Constraint | NEW_LOC_QTY DEFAULT 0 |
DF__STOCK_TRA__NEW_Q__79AA7608 | Default | Column Constraint | NEW_QUANTITY DEFAULT 0 |
DF__STOCK_TRA__NEW_S__7B92BE7A | Default | Column Constraint | NEW_SEQORDER DEFAULT 0 |
DF__STOCK_TRA__ON_CO__6DCC4D03 | Default | Column Constraint | ON_COSTS DEFAULT 0 |
DF__STOCK_TRA__PERIO__3C6B9247 | Default | Column Constraint | PERIOD_SEQNO DEFAULT 0 |
DF__STOCK_TRA__PERIO__77C22D96 | Default | Column Constraint | PERIOD_TRADED_IN_SEQ DEFAULT Y |
DF__STOCK_TRA__POSTT__32616E72 | Default | Column Constraint | POSTTIME DEFAULT getdate() |
DF__STOCK_TRA__POST___5DA0D232 | Default | Column Constraint | POST_LOOKUP_TO_GL DEFAULT N |
DF__STOCK_TRA__PREV___702A6218 | Default | Column Constraint | PREV_QUANTITY DEFAULT 0 |
DF__STOCK_TRA__PREV___711E8651 | Default | Column Constraint | PREV_AVECOST DEFAULT 0 |
DF__STOCK_TRA__QTY_T__75D9E524 | Default | Column Constraint | QTY_TRADED_IN_NEG DEFAULT 0 |
DF__STOCK_TRA__QUANT__353DDB1D | Default | Column Constraint | QUANTITY DEFAULT 0 |
DF__STOCK_TRA__REVER__36670980 | Default | Column Constraint | REVERSED DEFAULT N |
DF__STOCK_TRA__SESSI__145DA0ED | Default | Column Constraint | SESSION_ID DEFAULT -1 |
DF__STOCK_TRA__TOLOC__381A47C8 | Default | Column Constraint | TOLOCATION DEFAULT 0 |
DF__STOCK_TRA__TRANS__335592AB | Default | Column Constraint | TRANSDATE DEFAULT getdate() |
DF__STOCK_TRA__TRANS__3449B6E4 | Default | Column Constraint | TRANSTYPE DEFAULT 0 |
DF__STOCK_TRA__UNITP__3631FF56 | Default | Column Constraint | UNITPRICE DEFAULT 0 |
DF__STOCK_TRA__UPDAT__4B973090 | Default | Column Constraint | UPDATEITEM_FLAG DEFAULT Y |
DF__STOCK_TRA__VALUE__76CE095D | Default | Column Constraint | VALUE_TRADED_IN_NEG DEFAULT 0 |
GLACCS_STOCK_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (GLACC) REFERENCES GLACCS(ACCNO) |
PERIOD_STATUS_STOCK_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (PERIOD_SEQNO) REFERENCES PERIOD_STATUS(SEQNO) |
PK__STOCK_TRANS__316D4A39 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
STOCK_ITEMS_STOCK_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE) |
STOCK_LOCATIONS_STOCK_TRANS | Foreign Key | Table Constraint | FOREIGN KEY (LOCATION) REFERENCES STOCK_LOCATIONS(LOCNO) |
STOCK_LOCATIONS_STOCK_TRANS_TO | Foreign Key | Table Constraint | FOREIGN KEY (TOLOCATION) REFERENCES STOCK_LOCATIONS(LOCNO) |
Triggers:
Trigger name | Code |
STOCK_POST_COSTS | --MODIFIED 13/12/05 TO REMOVE CURSOR AND INSERT CODE TO UPDATE COST PRICES OF LINKED ITEMS BASED ON PACK QUANTITY --MODIFIED 08/08/2011 - SAM - SIMPLIFIED AND ADDED BACK IN LOGIC TO INCLUDE GOODS RETURNED AT ACTUAL COST VIA CREDITS --(SUPPLIER OR CUSTOMER) TO AFFECT AVERAGE COST BASED ON SETTING IN ESSENTIAL - GENERAL SETTINGS --MODIFIED TO EXCLUDE RECEIPTS INTO WIP LOCATIONS FROM UPDATING AVERAGE COST (UNLESS TOTALSTOCK = 0) AND TOTALSTOCK CALCS - WIP STOCK COST WILL INSTEAD BE ACTUAL AND COME FROM JOB_TRANS CREATE TRIGGER [dbo].[STOCK_POST_COSTS] ON [dbo].[STOCK_TRANS] FOR INSERT AS BEGIN SET NOCOUNT ON
DECLARE @INCLUDEZERO VARCHAR(1) DECLARE @INCLUDECREDIT VARCHAR(1) EXECUTE GET_PROFILE_VALUE 'AVECOSTINCLUDESCREDIT', @FIELDVALUE=@INCLUDECREDIT OUTPUT EXECUTE GET_PROFILE_VALUE 'AVECOSTINCLUDESZERO', @FIELDVALUE=@INCLUDEZERO OUTPUT /*CATER FOR CUSTOMER RETURNS AT ORIGINAL COST - IF NOT GENERATED FROM A CREDIT THEN THE AVERAGE COST WILL BE CURRENT AVERAGE*/ DECLARE @TRANSTYPE CHAR(1), @QUANTITY INTEGER, @UNITCOST FLOAT, @STOCKCODE VARCHAR(40), @SEQNO INTEGER, @LOCNO INTEGER, @IS_WIPLOCATION CHAR(1)
SELECT @TRANSTYPE = I.TRANSTYPE, @QUANTITY = I.QUANTITY, @UNITCOST = I.UNITCOST, @STOCKCODE = I.STOCKCODE, @SEQNO = I.SEQNO, @LOCNO = I.LOCATION, @IS_WIPLOCATION=SL.IS_WIPLOCATION FROM INSERTED I JOIN STOCK_LOCATIONS SL ON SL.LOCNO = I.LOCATION
IF @INCLUDECREDIT='Y' AND @TRANSTYPE = 0 AND @QUANTITY > 0 AND (@UNITCOST <> 0 OR @INCLUDEZERO = 'Y') BEGIN /*THIS IS A DEBTORS CREDIT NOTE AND I WANT RETURNED GOODS TO BE BOOKED IN AT TIME OF SALE COST IF KNOWN- AFFECTING MY AVERAGE COST*/ UPDATE SI SET AVECOST = CASE WHEN ABS(SI.TOTALSTOCK) > .00009 AND (SI.TOTALSTOCK-I.QUANTITY) > 0 AND @IS_WIPLOCATION = 'N' THEN (I.UNITCOST*I.QUANTITY+SI.AVECOST*(SI.TOTALSTOCK-I.QUANTITY))/SI.TOTALSTOCK WHEN ABS(SI.TOTALSTOCK) > .00009 AND (SI.TOTALSTOCK-I.QUANTITY) > 0 AND @IS_WIPLOCATION = 'Y' THEN SI.AVECOST /*Leave it alone*/ ELSE I.UNITCOST END FROM STOCK_ITEMS SI JOIN INSERTED I ON SI.STOCKCODE = I.STOCKCODE END ELSE IF (@INCLUDECREDIT='Y' OR @QUANTITY > 0) AND (@INCLUDEZERO = 'Y' OR @UNITCOST <> 0) BEGIN /*IS A STOCK RECEIPT OR A CREDITORS CREDIT NOTE*/ UPDATE SI SET LATESTCOST = I.UNITPRICE, AVECOST = CASE WHEN ABS(SI.TOTALSTOCK) > .00009 AND (SI.TOTALSTOCK-I.QUANTITY) > 0 AND @IS_WIPLOCATION = 'N' THEN (I.UNITPRICE*I.QUANTITY+SI.AVECOST*(SI.TOTALSTOCK-I.QUANTITY))/SI.TOTALSTOCK WHEN ABS(SI.TOTALSTOCK) > .00009 AND (SI.TOTALSTOCK-I.QUANTITY) > 0 AND @IS_WIPLOCATION = 'Y' THEN SI.AVECOST /*Leave it alone*/ ELSE I.UNITPRICE END FROM STOCK_ITEMS SI JOIN INSERTED I ON SI.STOCKCODE = I.STOCKCODE WHERE I.TRANSTYPE = 1 AND I.QUANTITY <> 0 END
-- CODE TO UPDATE LINKED STOCK ITEMS AVECOST AND LATESTCOST TO SKU UPDATE SI SET LATESTCOST = S.LATESTCOST * SI.UPDATEITEM_QTY, AVECOST = S.AVECOST * SI.UPDATEITEM_QTY FROM STOCK_ITEMS SI JOIN STOCK_ITEMS S ON SI.UPDATEITEM_CODE = S.STOCKCODE JOIN INSERTED I ON S.STOCKCODE = I.STOCKCODE WHERE SI.STOCKCODE <> '' AND SI.UPDATEITEM_QTY <> 0 AND S.STATUS = 'S' AND SI.STATUS = 'L'
UPDATE ST SET ST.NEW_AVECOST = SI.AVECOST, ST.LOOKUP_RECOVERABLE = CASE WHEN SI.[STATUS] = 'S' THEN 'Y' WHEN (SI.[STATUS] = 'L' AND (ISNULL(LTRIM(SI.UPDATEITEM_CODE), '') <> '')) OR (SI.STOCKCODE = '@') THEN 'N' ELSE SI.LOOKUP_RECOVERABLE END FROM STOCK_TRANS ST JOIN STOCK_ITEMS SI ON SI.STOCKCODE = ST.STOCKCODE WHERE ST.SEQNO = @SEQNO
SET NOCOUNT OFF END
|
STOCK_TRANS_BATCH_QTY | CREATE TRIGGER [dbo].[STOCK_TRANS_BATCH_QTY] ON [dbo].[STOCK_TRANS] FOR INSERT AS BEGIN
SET NOCOUNT ON DECLARE @LOCATION INT, @TRANSTYPE INT, @QUANTITY DOUBLE PRECISION, @STOCKCODE VARCHAR(23) DECLARE @ROWQTY INT, @BATCHCODE VARCHAR(20) DECLARE @EXPIRY_DATE DATETIME
DECLARE INSERTED_CURSOR CURSOR FOR SELECT LOCATION, TRANSTYPE, QUANTITY, STOCKCODE, EXPIRY_DATE, BATCHCODE FROM INSERTED WHERE BATCHCODE IS NOT NULL
OPEN INSERTED_CURSOR
FETCH NEXT FROM INSERTED_CURSOR INTO @LOCATION, @TRANSTYPE, @QUANTITY, @STOCKCODE, @EXPIRY_DATE, @BATCHCODE
WHILE @@FETCH_STATUS = 0 BEGIN SELECT @ROWQTY=COUNT(*) FROM BATCH_QUANTITIES WHERE (STOCKCODE=@STOCKCODE) AND (BATCHCODE=@BATCHCODE) AND (LOCATION=@LOCATION)
IF ((@ROWQTY>0) AND (@EXPIRY_DATE IS NULL)) BEGIN UPDATE BATCH_QUANTITIES SET QUANTITY=QUANTITY+@QUANTITY WHERE (STOCKCODE=@STOCKCODE) AND (BATCHCODE=@BATCHCODE) AND (LOCATION=@LOCATION) END ELSE IF (@ROWQTY>0) BEGIN UPDATE BATCH_QUANTITIES SET QUANTITY=QUANTITY+@QUANTITY, EXPIRY_DATE=@EXPIRY_DATE WHERE (STOCKCODE=@STOCKCODE) AND (BATCHCODE=@BATCHCODE) AND (LOCATION=@LOCATION) END
IF (@ROWQTY=0) INSERT INTO BATCH_QUANTITIES (STOCKCODE, LOCATION, BATCHCODE, QUANTITY, EXPIRY_DATE) VALUES (@STOCKCODE, @LOCATION, @BATCHCODE, @QUANTITY, @EXPIRY_DATE)
FETCH NEXT FROM INSERTED_CURSOR INTO @LOCATION, @TRANSTYPE, @QUANTITY, @STOCKCODE, @EXPIRY_DATE, @BATCHCODE END
CLOSE INSERTED_CURSOR DEALLOCATE INSERTED_CURSOR SET NOCOUNT OFF END
|
STOCK_TRANS_UPDATEITEM | CREATE TRIGGER [dbo].[STOCK_TRANS_UPDATEITEM] ON [dbo].[STOCK_TRANS] FOR INSERT AS BEGIN /* Exonet created trigger from DBUpdate */ SET NOCOUNT ON DECLARE @UPDATECODE VARCHAR(23), @UPDATEQTY FLOAT, @ITEMSTATUS CHAR, @UNITQTY FLOAT, @UPDATEPRICE FLOAT, @UPDATEITEM_FLAG CHAR, @QUANTITY FLOAT, @STOCKCODE VARCHAR(23), @UNITPRICE FLOAT, @SEQNO INT, @UNITCOST FLOAT, @UPDATEUNITCOST FLOAT, @GLBRANCH INT, @ISUPDATEITEMTRX CHAR(1), @TRANSTYPE INT, @TOLOCATION INT , @PERIOD_SEQNO INT
SELECT @SEQNO=SEQNO, @STOCKCODE=STOCKCODE, @QUANTITY=QUANTITY, @GLBRANCH=GLBRANCH, @UPDATEITEM_FLAG=UPDATEITEM_FLAG, @UNITPRICE=UNITPRICE, @UNITCOST=UNITCOST FROM INSERTED
DECLARE UI_INSERTED_CURSOR CURSOR LOCAL FOR SELECT I.SEQNO, I.STOCKCODE, I.QUANTITY, I.UNITPRICE, S.UPDATEITEM_CODE, S.UPDATEITEM_QTY, S.STATUS, I.UNITCOST, I.GLBRANCH, I.TOLOCATION, I.TRANSTYPE , I.PERIOD_SEQNO FROM INSERTED I JOIN STOCK_ITEMS S ON I.STOCKCODE = S.STOCKCODE WHERE I.UPDATEITEM_FLAG = 'Y'
OPEN UI_INSERTED_CURSOR
FETCH NEXT FROM UI_INSERTED_CURSOR INTO @SEQNO, @STOCKCODE, @QUANTITY, @UNITPRICE, @UPDATECODE, @UPDATEQTY, @ITEMSTATUS, @UNITCOST, @GLBRANCH, @TOLOCATION, @TRANSTYPE , @PERIOD_SEQNO
WHILE @@FETCH_STATUS = 0 BEGIN IF ((@UPDATECODE IS NOT NULL) AND (@UPDATECODE <> '') AND (@STOCKCODE <> @UPDATECODE) AND (@UPDATEQTY IS NOT NULL) AND (@UPDATEQTY <> 0) AND (@QUANTITY<>0)) BEGIN SET @UNITQTY=@QUANTITY*@UPDATEQTY
IF (@ITEMSTATUS='L') BEGIN SET @UPDATEPRICE=@UNITPRICE/@UPDATEQTY SET @UPDATEUNITCOST=@UNITCOST/@UPDATEQTY END ELSE BEGIN SELECT @UPDATEPRICE=0 SELECT @UPDATEUNITCOST=0 END
INSERT INTO [dbo].[STOCK_TRANS] (TRANSDATE, STOCKCODE, TRANSTYPE, REF1, REF2, QUANTITY, UNITPRICE, LOCATION, TOLOCATION, FROM_LEDGER, FROM_HDR, BATCHCODE, ACCNO, LINE_SEQNO, UNITCOST, GLBRANCH, PLU, KITCODE, KITSEQNO, POST_TO_GL, RECEIPT_NO , JOBNO, GLPOSTED, GLACC, GLSUBACC, EXPIRY_DATE, GLBATCHNO, UPDATEITEM_FLAG, SESSION_ID , PERIOD_SEQNO ) SELECT TRANSDATE, @UPDATECODE, TRANSTYPE, REF1, REF2, @UNITQTY, @UPDATEPRICE, LOCATION, TOLOCATION, FROM_LEDGER, FROM_HDR, BATCHCODE, ACCNO, LINE_SEQNO, @UPDATEUNITCOST, @GLBRANCH, STOCKCODE, KITCODE, KITSEQNO, POST_TO_GL, RECEIPT_NO, JOBNO, GLPOSTED, GLACC, GLSUBACC, EXPIRY_DATE, GLBATCHNO, 'U', SESSION_ID , PERIOD_SEQNO FROM INSERTED WHERE SEQNO = @SEQNO
UPDATE [dbo].[STOCK_TRANS] SET UPDATEITEM_FLAG='T' WHERE SEQNO=@SEQNO END FETCH NEXT FROM UI_INSERTED_CURSOR INTO @SEQNO, @STOCKCODE, @QUANTITY, @UNITPRICE, @UPDATECODE, @UPDATEQTY, @ITEMSTATUS, @UNITCOST, @GLBRANCH, @TOLOCATION, @TRANSTYPE , @PERIOD_SEQNO END CLOSE UI_INSERTED_CURSOR DEALLOCATE UI_INSERTED_CURSOR SET NOCOUNT OFF END
|
STOCK_TR_POST | CREATE TRIGGER [dbo].[STOCK_TR_POST] ON [dbo].[STOCK_TRANS] FOR INSERT AS BEGIN SET NOCOUNT ON DECLARE @SEQNO INT, @LOCATION INT, @ITEMSTATUS CHAR(1), @TRANSTYPE INT, @QUANTITY DOUBLE PRECISION, @UNITPRICE DOUBLE PRECISION, @TOLOCATION INT, @STOCKCODE VARCHAR(23), @TRANSDATE DATETIME, @EXPIRY_DATE DATETIME, @REF1 VARCHAR(30), @REF2 VARCHAR(30), @UNITCOST DOUBLE PRECISION, @FROM_LEDGER CHAR(1), @BATCHCODE VARCHAR(20), @GLBRANCH INT, @GLACC INT, @GLSUBACC INT, @SESSION_ID INT, @AGE INT, @PERIOD_SEQNO INT, @FROM_HDR INT, @LINESEQNO INT, @PLU VARCHAR(23), @KITCODE VARCHAR(23), @KITSEQNO INT, @POST_TO_GL CHAR(1), @ACCNO INT, @JOBNO INT, @RECEIPT_NO INT, @GLPOSTED CHAR(1), @UPDATEITEM_FLAG CHAR(1), @GLBATCHNO INT, @ISUPDATEITEMTRX CHAR(1), @MONTHUNITS DOUBLE PRECISION, @LASTMONTHUNITS DOUBLE PRECISION, @YEARUNITS DOUBLE PRECISION, @LASTYEARUNITS DOUBLE PRECISION, @MONTHVALUE DOUBLE PRECISION, @LASTMONTHVALUE DOUBLE PRECISION, @YEARVALUE DOUBLE PRECISION, @LASTYEARVALUE DOUBLE PRECISION, @PERIODSTHISYEAR INTEGER, @PERIODSINYEAR INTEGER, @TOTALSTOCK DOUBLE PRECISION, @NEW_TOTALSTOCK DOUBLE PRECISION, @AVECOST DOUBLE PRECISION, @MAXSEQNO INT, @MINSEQNO INT, @LOC_QTY DOUBLE PRECISION, @TO_WIPLOCATION CHAR(1), @FROM_WIPLOCATION CHAR(1)
SET @TO_WIPLOCATION ='N' SET @FROM_WIPLOCATION ='N'
DECLARE INSERTED_CURSOR CURSOR LOCAL FOR SELECT I.STOCKCODE, I.TRANSTYPE, I.QUANTITY, I.UNITPRICE, I.LOCATION, I.TOLOCATION, S.STATUS, I.TRANSDATE, I.REF1, I.REF2, I.UNITCOST, I.FROM_LEDGER, I.BATCHCODE, I.GLBRANCH, I.GLACC, I.GLSUBACC, I.AGE, I.SESSION_ID, I.PERIOD_SEQNO, I.EXPIRY_DATE, I.FROM_HDR, I.LINE_SEQNO, I.PLU, I.KITCODE, I.KITSEQNO, I.POST_TO_GL, I.ACCNO, I.JOBNO, I.RECEIPT_NO, 'Y', I.UPDATEITEM_FLAG, I.GLBATCHNO, I.SEQNO, S.TOTALSTOCK, S.AVECOST FROM INSERTED I JOIN STOCK_ITEMS S ON I.STOCKCODE = S.STOCKCODE
OPEN INSERTED_CURSOR
FETCH NEXT FROM INSERTED_CURSOR INTO @STOCKCODE, @TRANSTYPE, @QUANTITY, @UNITPRICE, @LOCATION, @TOLOCATION, @ITEMSTATUS, @TRANSDATE, @REF1, @REF2, @UNITCOST, @FROM_LEDGER, @BATCHCODE, @GLBRANCH, @GLACC, @GLSUBACC, @AGE, @SESSION_ID, @PERIOD_SEQNO, @EXPIRY_DATE, @FROM_HDR, @LINESEQNO, @PLU, @KITCODE, @KITSEQNO, @POST_TO_GL, @ACCNO, @JOBNO, @RECEIPT_NO, @GLPOSTED, @UPDATEITEM_FLAG, @GLBATCHNO, @SEQNO, @TOTALSTOCK, @AVECOST
WHILE @@FETCH_STATUS = 0 BEGIN IF @ITEMSTATUS <> 'L' BEGIN SELECT @TO_WIPLOCATION=IS_WIPLOCATION FROM STOCK_LOCATIONS WHERE LOCNO = @TOLOCATION SELECT @FROM_WIPLOCATION=IS_WIPLOCATION FROM STOCK_LOCATIONS WHERE LOCNO = @LOCATION
UPDATE STOCK_LOC_INFO SET QTY = QTY + @QUANTITY WHERE STOCKCODE = @STOCKCODE AND LOCATION = @LOCATION
SELECT @LOC_QTY = QTY FROM STOCK_LOC_INFO WHERE STOCKCODE = @STOCKCODE AND LOCATION = @LOCATION
UPDATE [dbo].[STOCK_TRANS] SET PREV_QUANTITY = @TOTALSTOCK, NEW_LOC_QTY = @LOC_QTY, NEW_QUANTITY = CASE WHEN TRANSTYPE = 2 AND @FROM_WIPLOCATION = 'N' AND @TO_WIPLOCATION = 'N' THEN @TOTALSTOCK -- For pure stock transfers the total stock doesnt change ELSE @TOTALSTOCK + Quantity END, PREV_AVECOST = @AVECOST, QTY_Traded_In_Neg = dbo.StockQtyTradedInNeg(@TOTALSTOCK, Quantity), Value_Traded_In_Neg = case when TRANSTYPE = 1 then dbo.StockQtyTradedInNeg(@TOTALSTOCK, Quantity) * @UNITPRICE when TRANSTYPE = 0 then dbo.StockQtyTradedInNeg(@TOTALSTOCK, Quantity) * @AVECOST else 0 end WHERE SEQNO = @SEQNO
SET @NEW_TOTALSTOCK = @TOTALSTOCK
SELECT @MAXSEQNO = max(SEQNO) FROM [dbo].[STOCK_TRANS] where PERIOD_SEQNO = @PERIOD_SEQNO and STOCKCODE = @STOCKCODE SELECT @MINSEQNO = min(SEQNO) FROM [dbo].[STOCK_TRANS] where PERIOD_SEQNO > @PERIOD_SEQNO and STOCKCODE = @STOCKCODE
IF (@MINSEQNO IS NOT NULL) AND (@MAXSEQNO > @MINSEQNO) BEGIN UPDATE [dbo].[STOCK_TRANS] SET Period_Traded_In_Seq = 'N' WHERE STOCKCODE = @STOCKCODE and SEQNO >= @MINSEQNO AND SEQNO <= @MAXSEQNO
DECLARE @FROMSEQNO INT SELECT @FROMSEQNO = min(SEQNO) FROM [dbo].[STOCK_TRANS] WHERE PERIOD_SEQNO = @PERIOD_SEQNO and STOCKCODE = @STOCKCODE
EXECUTE UPDATE_STOCKTRANS_NEW_SEQORDER @STOCKCODE, @FROMSEQNO END ELSE BEGIN UPDATE [dbo].[STOCK_TRANS] SET Period_Traded_In_Seq = 'Y', NEW_SEQORDER = (SELECT MAX(NEW_SEQORDER)+1 FROM [dbo].[STOCK_TRANS] WHERE STOCKCODE = @STOCKCODE) WHERE SEQNO = @SEQNO END
/* If stock trans is not a transfer (transtype 2) and its not coming out of a WIP location then add qty (negative or positive) The reason for this is that if using WIP location then stock transfer to a WIP location has already reduced TOTALSTOCK. Alternatively if stock trans is a transfer (transtype 2) and its a transfer from a stock location to a WIP location then add qty (negative or positve). This will reduce TOTALSTOCK and put it into WIP*/ IF (@TRANSTYPE <> 2 AND @FROM_WIPLOCATION = 'N') OR (@TRANSTYPE = 2 AND @FROM_WIPLOCATION = 'N' AND @TO_WIPLOCATION = 'Y') BEGIN UPDATE STOCK_ITEMS SET TOTALSTOCK = TOTALSTOCK + @QUANTITY WHERE STOCKCODE = @STOCKCODE
SET @NEW_TOTALSTOCK = @TOTALSTOCK + @QUANTITY END
/* When moving stock out of WIP location to a stock location then we need to increase TOTALSTOCK. */ IF (@TRANSTYPE = 2 AND @FROM_WIPLOCATION = 'Y' AND @TO_WIPLOCATION = 'N') BEGIN UPDATE STOCK_ITEMS SET TOTALSTOCK = TOTALSTOCK - @QUANTITY WHERE STOCKCODE = @STOCKCODE
SET @NEW_TOTALSTOCK = @TOTALSTOCK + @QUANTITY END END
IF @TRANSTYPE = 0 BEGIN SET @MONTHUNITS=0 SET @MONTHVALUE=0 SET @YEARUNITS=0 SET @YEARVALUE=0 SET @LASTMONTHUNITS=0 SET @LASTMONTHVALUE=0 SET @LASTYEARUNITS=0 SET @LASTYEARVALUE=0
SELECT @PERIODSTHISYEAR = PERIODSTHISYEAR FROM GL_CONTROL SELECT @PERIODSINYEAR = COUNT(SEQNO) FROM PERIODS_DEFN WHERE SEQNO > 0
IF @AGE=0 BEGIN SET @MONTHUNITS=@QUANTITY SET @MONTHVALUE=@QUANTITY * @UNITPRICE END ELSE IF @AGE=1 BEGIN SET @LASTMONTHUNITS=@QUANTITY SET @LASTMONTHVALUE=@QUANTITY * @UNITPRICE END
IF ((@AGE+1) <= @PERIODSTHISYEAR) BEGIN SET @YEARUNITS = @QUANTITY SET @YEARVALUE = @QUANTITY * @UNITPRICE END ELSE IF ((@AGE+1) <= (@PERIODSTHISYEAR+@PERIODSINYEAR)) BEGIN SET @LASTYEARUNITS = @QUANTITY SET @LASTYEARVALUE = @QUANTITY * @UNITPRICE END
UPDATE STOCK_ITEMS SET MONTHUNITS = MONTHUNITS - @MONTHUNITS, YEARUNITS = YEARUNITS - @YEARUNITS, MONTHVALUE = MONTHVALUE - @MONTHVALUE, YEARVALUE = YEARVALUE-@YEARVALUE, LASTMONTHUNITS = LASTMONTHUNITS - @LASTMONTHUNITS, LASTYEARUNITS = LASTYEARUNITS - @LASTYEARUNITS, LASTMONTHVALUE = LASTMONTHVALUE - @LASTMONTHVALUE, LASTYEARVALUE = LASTYEARVALUE-@LASTYEARVALUE WHERE STOCKCODE = @STOCKCODE END
IF @TRANSTYPE = 2 AND @TOLOCATION <> 0 AND @UPDATEITEM_FLAG <> 'U' BEGIN SELECT @LOC_QTY = QTY - @QUANTITY FROM STOCK_LOC_INFO WHERE STOCKCODE = @STOCKCODE AND LOCATION = @TOLOCATION
INSERT INTO [dbo].[STOCK_TRANS]( TRANSDATE, STOCKCODE, TRANSTYPE, REF1, REF2, QUANTITY, UNITPRICE, UNITCOST, LOCATION, FROM_LEDGER, BATCHCODE, GLBRANCH, GLACC, GLSUBACC, SESSION_ID, PERIOD_SEQNO, EXPIRY_DATE, FROM_HDR, LINE_SEQNO, PLU, KITCODE, KITSEQNO, POST_TO_GL, ACCNO, JOBNO, RECEIPT_NO, GLPOSTED, UPDATEITEM_FLAG, GLBATCHNO, PREV_QUANTITY, PREV_AVECOST, NEW_LOC_QTY, NEW_QUANTITY) VALUES( @TRANSDATE, @STOCKCODE, @TRANSTYPE, @REF1, @REF2, 0-@QUANTITY, @UNITPRICE, @UNITCOST, @TOLOCATION, @FROM_LEDGER, @BATCHCODE, @GLBRANCH, @GLACC, @GLSUBACC, @SESSION_ID, @PERIOD_SEQNO, @EXPIRY_DATE, @FROM_HDR, @LINESEQNO, @PLU, @KITCODE, @KITSEQNO, @POST_TO_GL, @ACCNO, @JOBNO, @RECEIPT_NO, @GLPOSTED, @UPDATEITEM_FLAG, @GLBATCHNO, @TOTALSTOCK, @AVECOST, @LOC_QTY, @NEW_TOTALSTOCK)
IF @ITEMSTATUS <> 'L' BEGIN UPDATE STOCK_LOC_INFO SET QTY = QTY - @QUANTITY WHERE STOCKCODE = @STOCKCODE AND LOCATION = @TOLOCATION END END
FETCH NEXT FROM INSERTED_CURSOR INTO @STOCKCODE, @TRANSTYPE, @QUANTITY, @UNITPRICE, @LOCATION, @TOLOCATION, @ITEMSTATUS, @TRANSDATE, @REF1, @REF2, @UNITCOST, @FROM_LEDGER, @BATCHCODE, @GLBRANCH, @GLACC, @GLSUBACC, @AGE, @SESSION_ID, @PERIOD_SEQNO, @EXPIRY_DATE, @FROM_HDR, @LINESEQNO, @PLU, @KITCODE, @KITSEQNO, @POST_TO_GL, @ACCNO, @JOBNO, @RECEIPT_NO, @GLPOSTED, @UPDATEITEM_FLAG, @GLBATCHNO, @SEQNO, @TOTALSTOCK, @AVECOST END CLOSE INSERTED_CURSOR DEALLOCATE INSERTED_CURSOR SET NOCOUNT OFF END
|
Entity: STOCK_TRANS_ARCHIVE
Comment | This table is used for archived stock transactions. |
Primary key columns | SEQNO |
Attributes:
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
DF__STOCK_TRA__ALT_A__70AA505C | Default | Column Constraint | ALT_AVECOST DEFAULT 0 |
DF__STOCK_TRA__ALT_Q__6FB62C23 | Default | Column Constraint | ALT_QUANTITY DEFAULT 0 |
DF__STOCK_TRA__ALT_S__719E7495 | Default | Column Constraint | ALT_SEQNO DEFAULT -1 |
DF__STOCK_TRA__PREV___6DCDE3B1 | Default | Column Constraint | PREV_QUANTITY DEFAULT 0 |
DF__STOCK_TRA__PREV___6EC207EA | Default | Column Constraint | PREV_AVECOST DEFAULT 0 |
DF__STOCK_TRA__SESSI__389B0163 | Default | Column Constraint | SESSION_ID DEFAULT -1 |
PK__STOCK_TRANS_ARCH__47284C85 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
Entity: STOCK_TRANS_HDR
Comment | This table contains header information for stock transactions. |
Primary key columns | SEQNO |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
SEQNO | Yes | INTEGER | Yes | A unique ID for the record. |
TRANSDATE | No | DATETIME | Yes | The date and time when the transaction took place. |
TRANSTYPE | No | INTEGER | Yes | The transaction type. Will be one of: 0=Sales 1=Receipt 2=Transfer 3=Adjust Out 4=Adjust In 5=Average Cost Adjustment |
REFERENCE | No | VARCHAR(30) | No | Reference text for the transaction. |
STAFFNO | No | INTEGER | Yes | ID number of the staff member who entered the transaction. |
NARRATIVE_SEQNO | No | INTEGER | No | ID of the narrative on the transaction (entered via manual stock movement). |
PERIOD_SEQNO | No | INTEGER | Yes | ID number of the period that the record belongs to. |
AGE_STAMP | No | INTEGER | No | This is a historical field that has been superseded by the AGE calculated field. |
AGE | No | AS | No | Calculated field that indicates the age the record belongs to. |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
STAFF_STOCK_TRANS_HDR | Non Identifying | STAFF | STOCK_TRANS_HDR | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (PERIOD_SEQNO) |
| Not Null | Column Constraint | NOT NULL (SEQNO) |
| Not Null | Column Constraint | NOT NULL (TRANSDATE) |
| Not Null | Column Constraint | NOT NULL (TRANSTYPE) |
| Not Null | Column Constraint | NOT NULL (STAFFNO) |
DF__STOCK_TRA__AGE_S__41304764 | Default | Column Constraint | AGE_STAMP DEFAULT -1 |
DF__STOCK_TRA__PERIO__403C232B | Default | Column Constraint | PERIOD_SEQNO DEFAULT 0 |
PK__STOCK_TRANS_HDR__42439BD7 | Primary Key | Table Constraint | PRIMARY KEY (SEQNO) |
STAFF_STOCK_TRANS_HDR | Foreign Key | Table Constraint | FOREIGN KEY (STAFFNO) REFERENCES STAFF(STAFFNO) |
Entity: STOCK_WEB
Comment | This table stores information relating to how stock items should appear on a website, as displayed on the Web tab of the Stock Item Details window. |
Primary key columns | |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
STOCKCODE | No | CHAR(23) | Yes | The stock code of the stock item that this record applies to. |
SALES_HTML | No | VARCHAR(4096) | No | The Sales text (HTML) for the specified stock item. |
PICTURE_URL | No | VARCHAR(80) | No | The URL of the picture to be displayed for the stock item. |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
STOCK_ITEMS_STOCK_WEB | Non Identifying | STOCK_ITEMS | STOCK_WEB | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (STOCKCODE) |
STOCK_ITEMS_STOCK_WEB | Foreign Key | Table Constraint | FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE) |
Entity: SUPPLIER_STOCK_ITEMS
Comment | This table contains details of any Alternate Suppliers/Stock Codes set up for stock items. |
Primary key columns | SUPPLIERCODE, ACCNO |
Attributes:
Column name | Primary key | Data type | Not NULL | Comment |
STOCKCODE | No | VARCHAR(23) | Yes | The stock item's ID code in EXO Business. |
SUPPLIERCODE | Yes | VARCHAR(23) | Yes | The supplier's ID code for the stock item. |
DESCRIPTION | No | VARCHAR(40) | No | The supplier's description for the stock item. |
LATESTCOST | No | FLOAT(53) | No | The supplier's latest cost for the stock item. |
ACCNO | Yes | INTEGER | Yes | The ID number of the supplier (Creditor) account. |
ECONORDERQTY | No | FLOAT(53) | No | The Minimum Order Quantity for the stock item. |
PURCHPACKQUANT | No | FLOAT(53) | Yes | The Qty per Pack when purchasing the stock item from the supplier. |
PURCHPACKPRICE | No | FLOAT(53) | Yes | The Pack Cost when purchasing the stock item from the supplier. |
PACKREFERENCE | No | VARCHAR(20) | No | An optional reference code for the pack. |
LAST_UPDATE | No | DATETIME | No | The date when the record was last updated (not set automatically - it must be specified on the Alternate Suppliers / Stock Codes window). |
DISCOUNT | No | FLOAT(53) | No | The percentage discount when purchasing the stock item from the supplier. |
IS_DEFAULT | No | CHAR(1) | Yes | |
Relationships:
Name | Relationship type | Parent | Child | Cardinality |
STOCK_ITEMS_SUPPLIER_STOCK_ITEMS | Non Identifying | STOCK_ITEMS | SUPPLIER_STOCK_ITEMS | Zero Or More |
Constraints:
Name | Type | Level | Constraint |
| Not Null | Column Constraint | NOT NULL (IS_DEFAULT) |
| Not Null | Column Constraint | NOT NULL (STOCKCODE) |
| Not Null | Column Constraint | NOT NULL (SUPPLIERCODE) |
| Not Null | Column Constraint | NOT NULL (PURCHPACKPRICE) |
| Not Null | Column Constraint | NOT NULL (ACCNO) |
| Not Null | Column Constraint | NOT NULL (PURCHPACKQUANT) |
DF__SUPPLIER___DISCO__34157811 | Default | Column Constraint | DISCOUNT DEFAULT 0 |
DF__SUPPLIER___IS_DE__38DA2D2E | Default | Column Constraint | IS_DEFAULT DEFAULT N |
DF__SUPPLIER___LATES__1FB8AE52 | Default | Column Constraint | LATESTCOST DEFAULT 0 |
DF__SUPPLIER___PURCH__68536ACF | Default | Column Constraint | PURCHPACKQUANT DEFAULT 1 |
DF__SUPPLIER___PURCH__69478F08 | Default | Column Constraint | PURCHPACKPRICE DEFAULT 0 |
PK__SUPPLIER_STOCK_I__1EC48A19 | Primary Key | Table Constraint | PRIMARY KEY (SUPPLIERCODE, ACCNO) |
STOCK_ITEMS_SUPPLIER_STOCK_ITEMS | Foreign Key | Table Constraint | FOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE) |
|