MYOB Exo Business Stock
List of entities
Entity details
Entity: BILLOMAT_HDR
CommentThis table contains header information for Bills of Materials (BOMs).
Primary key columnsBILLCODE

Attributes:
Column namePrimary keyData typeNot NULLComment
BILLCODEYesVARCHAR(23)YesThe BOM's unique ID code.
DESCRIPTIONNoVARCHAR(40)NoA descriptive name for the BOM.
SELLPRICE1NoFLOAT(53)NoSell price 1.
SELLPRICE2NoFLOAT(53)NoSell price 2.
SELLPRICE3NoFLOAT(53)NoSell price 3.
SELLPRICE4NoFLOAT(53)NoSell price 4.
COSTPRICENoFLOAT(53)NoCost price for the BOM.
PRICING_MODENoINTEGERNoHow the output item is priced. 0 = Priced by total. 1 = Priced by components.
OUTPUT_CODENoVARCHAR(23)NoStock code of the BOM's output item.
BATCH_QTYNoFLOAT(53)NoThe BOM's Batch Quantity.
HIDE_LINESNoCHAR(1)NoThis field is set to 'Y' if the BOM's "Hide lines on reports" option is ticked.
WASTAGENoFLOAT(53)NoThe BOM's Overhead Allocation percentage.
NOTESNoVARCHAR(2048)NoAny notes recorded for the BOM.
SELLPRICE5NoFLOAT(53)NoSell price 5.
SELLPRICE6NoFLOAT(53)NoSell price 6.
SELLPRICE7NoFLOAT(53)NoSell price 7.
SELLPRICE8NoFLOAT(53)NoSell price 8.
SELLPRICE9NoFLOAT(53)NoSell price 9.
SELLPRICE10NoFLOAT(53)NoSell price 10.
ISACTIVENoCHAR(1)NoThis field contains 'Y' if the BOM is active; otherwise it is inactive.
KIT_TYPENoINTEGERYes
BOMTYPENoCHAR(1)YesThe type of BOM. Will be one of:
K = Kit
B = Build
O = Order Template
AUTOBUILDNoCHAR(1)YesApplies 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:
NameRelationship typeParentChildCardinality
BILLOMAT_HDR_BILLOMAT_LINESNon IdentifyingBILLOMAT_HDRBILLOMAT_LINESZero Or More
BILLOMAT_HDR_BILLOMAT_TEMPNon IdentifyingBILLOMAT_HDRBILLOMAT_TEMPZero Or More
BILLOMAT_HDR_JOBCOST_LINESNon IdentifyingBILLOMAT_HDRJOBCOST_LINESZero Or More
BILLOMAT_HDR_JOB_TRANSACTIONSNon IdentifyingBILLOMAT_HDRJOB_TRANSACTIONSZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (AUTOBUILD)
Not NullColumn ConstraintNOT NULL (BILLCODE)
Not NullColumn ConstraintNOT NULL (BOMTYPE)
Not NullColumn ConstraintNOT NULL (KIT_TYPE)
DF__BILLOMAT___AUTOB__43B7AE54DefaultColumn ConstraintAUTOBUILD DEFAULT N
DF__BILLOMAT___BATCH__0C06BB60DefaultColumn ConstraintBATCH_QTY DEFAULT 1
DF__BILLOMAT___BOMTY__48F09966DefaultColumn ConstraintBOMTYPE DEFAULT K
DF__BILLOMAT___COSTP__0A1E72EEDefaultColumn ConstraintCOSTPRICE DEFAULT 0
DF__BILLOMAT___HIDE___18027DF1DefaultColumn ConstraintHIDE_LINES DEFAULT N
DF__BILLOMAT___ISACT__2883C9D4DefaultColumn ConstraintISACTIVE DEFAULT Y
DF__BILLOMAT___KIT_T__47FC752DDefaultColumn ConstraintKIT_TYPE DEFAULT 0
DF__BILLOMAT___PRICI__0B129727DefaultColumn ConstraintPRICING_MODE DEFAULT 0
DF__BILLOMAT___SELLP__0504B816DefaultColumn ConstraintSELLPRICE5 DEFAULT 0
DF__BILLOMAT___SELLP__05F8DC4FDefaultColumn ConstraintSELLPRICE6 DEFAULT 0
DF__BILLOMAT___SELLP__064DE20ADefaultColumn ConstraintSELLPRICE1 DEFAULT 0
DF__BILLOMAT___SELLP__06ED0088DefaultColumn ConstraintSELLPRICE7 DEFAULT 0
DF__BILLOMAT___SELLP__07420643DefaultColumn ConstraintSELLPRICE2 DEFAULT 0
DF__BILLOMAT___SELLP__07E124C1DefaultColumn ConstraintSELLPRICE8 DEFAULT 0
DF__BILLOMAT___SELLP__08362A7CDefaultColumn ConstraintSELLPRICE3 DEFAULT 0
DF__BILLOMAT___SELLP__08D548FADefaultColumn ConstraintSELLPRICE9 DEFAULT 0
DF__BILLOMAT___SELLP__092A4EB5DefaultColumn ConstraintSELLPRICE4 DEFAULT 0
DF__BILLOMAT___SELLP__09C96D33DefaultColumn ConstraintSELLPRICE10 DEFAULT 0
DF__BILLOMAT___WASTA__4E298478DefaultColumn ConstraintWASTAGE DEFAULT 0
PK__BILLOMAT___BILLC__0559BDD1Primary KeyTable ConstraintPRIMARY KEY (BILLCODE)



Entity: BILLOMAT_LINES
CommentThis table contains line-level information for Bills of Materials (BOMs).
Primary key columnsSEQNO

Attributes:
Column namePrimary keyData typeNot NULLComment
SEQNOYesINTEGERYesA unique ID number for the line.
BILLCODENoVARCHAR(23)NoThe ID code of the BOM header (BILLOMAT_HDR.BILLCODE).
STOCKCODENoVARCHAR(23)NoThe stock code of the stock item on this line.
QUANTITYNoFLOAT(53)NoThe quantity on this line.
DESCRIPTIONNoVARCHAR(40)NoThe description of the stock item on this line.
VARIANTLINENoCHAR(1)NoThis field contains 'Y' if the line is not included in the BOM by default.

Relationships:
NameRelationship typeParentChildCardinality
BILLOMAT_HDR_BILLOMAT_LINESNon IdentifyingBILLOMAT_HDRBILLOMAT_LINESZero Or More
STOCK_ITEMS_BILLOMAT_LINESNon IdentifyingSTOCK_ITEMSBILLOMAT_LINESZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (SEQNO)
BILLOMAT_HDR_BILLOMAT_LINESForeign KeyTable ConstraintFOREIGN KEY (BILLCODE) REFERENCES BILLOMAT_HDR(BILLCODE)
DF__BILLOMAT___QUANT__2C738AF2DefaultColumn ConstraintQUANTITY DEFAULT 1
DF__BILLOMAT___VARIA__2977EE0DDefaultColumn ConstraintVARIANTLINE DEFAULT N
PK__BILLOMAT___SEQNO__2B7F66B9Primary KeyTable ConstraintPRIMARY KEY (SEQNO)
STOCK_ITEMS_BILLOMAT_LINESForeign KeyTable ConstraintFOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE)



Entity: BILLOMAT_TEMP
CommentThis table is used in Bill of Materials batch entry.
Primary key columnsSEQNO

Attributes:
Column namePrimary keyData typeNot NULLComment
SEQNOYesINTEGERYesA unique ID number for the record.
TRANSDATENoDATETIMENoThe date and time when the transaction occurred.
COMPONENTLOCATIONNoINTEGERNoThe location of the BOM component stock item.
PRODUCTLOCATIONNoINTEGERNoThe location of the BOM product stock item.
REFERENCECODENoVARCHAR(30)NoA reference code for the record.
BATCHQUANTITYNoFLOAT(53)NoThe batch quantity on the record.
STAFFNONoINTEGERYesThe ID number of the staff member on the record.
BILLCODENoVARCHAR(23)NoThe BOM code.
STOCKCODENoVARCHAR(23)NoThe stock code of the BOM component item.
QUANTITYNoFLOAT(53)NoThe quantity of the BOM component item.
UNITPRICENoFLOAT(53)NoThe unit price of the BOM component item.
KITSEQNONoINTEGERNoThe ID number of the BOM.
BOMBATCHSEQNONoINTEGERNoThe BOM batch ID number.

Relationships:
NameRelationship typeParentChildCardinality
BILLOMAT_HDR_BILLOMAT_TEMPNon IdentifyingBILLOMAT_HDRBILLOMAT_TEMPZero Or More
STAFF_BILLOMAT_TEMPNon IdentifyingSTAFFBILLOMAT_TEMPZero Or More
STOCK_ITEMS_BILLOMAT_TEMPNon IdentifyingSTOCK_ITEMSBILLOMAT_TEMPZero Or More
STOCK_LOCATIONS_BILLOMAT_TEMPNon IdentifyingSTOCK_LOCATIONSBILLOMAT_TEMPZero Or More
STOCK_LOCATIONS_BILLOMAT_TEMP_PRODNon IdentifyingSTOCK_LOCATIONSBILLOMAT_TEMPZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (STAFFNO)
Not NullColumn ConstraintNOT NULL (SEQNO)
BILLOMAT_HDR_BILLOMAT_TEMPForeign KeyTable ConstraintFOREIGN KEY (BILLCODE) REFERENCES BILLOMAT_HDR(BILLCODE)
DF__BILLOMAT___BOMBA__4B5804C5DefaultColumn ConstraintBOMBATCHSEQNO DEFAULT -1
DF__BILLOMAT___KITSE__4A63E08CDefaultColumn ConstraintKITSEQNO DEFAULT -1
DF__BILLOMAT___STAFF__6458BCB9DefaultColumn ConstraintSTAFFNO DEFAULT -1
PK__BILLOMAT_TEMP__63649880Primary KeyTable ConstraintPRIMARY KEY (SEQNO)
STAFF_BILLOMAT_TEMPForeign KeyTable ConstraintFOREIGN KEY (STAFFNO) REFERENCES STAFF(STAFFNO)
STOCK_ITEMS_BILLOMAT_TEMPForeign KeyTable ConstraintFOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE)
STOCK_LOCATIONS_BILLOMAT_TEMPForeign KeyTable ConstraintFOREIGN KEY (COMPONENTLOCATION) REFERENCES STOCK_LOCATIONS(LOCNO)
STOCK_LOCATIONS_BILLOMAT_TEMP_PRODForeign KeyTable ConstraintFOREIGN KEY (PRODUCTLOCATION) REFERENCES STOCK_LOCATIONS(LOCNO)



Entity: CR_INVLINES
CommentThis table contains line information for all Creditor transactions.
Primary key columnsSEQNO

Attributes:
Column namePrimary keyData typeNot NULLComment
SEQNOYesINTEGERYesA unique transaction line ID.
ACCNONoINTEGERNoThe account number of the Creditor that the transaction belongs to.
INVNONoVARCHAR(20)NoThe invoice number.
HDR_SEQNONoINTEGERNoThe SEQNO of the record in the CR_TRANS table that this record related to.
STOCKCODENoVARCHAR(23)NoCode of the Stock item on this line.
DESCRIPTIONNoVARCHAR(40)NoDescription of the Stock item on this line.
QUANTITYNoFLOAT(53)NoThe quantity for this line.
UNITPRICENoFLOAT(53)NoThe unit price of the Stock item on this line.
DISCOUNTNoFLOAT(53)NoThe percentage discount for the Stock item on this line.
DISCOUNTAMTNoFLOAT(53)NoThe discount amount on a stock line.
DISCOUNTPCTNoFLOAT(53)NoThe discount percentage on a stock line.
ANALYSISNoINTEGERNoGL 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".
LOCATIONNoINTEGERNoThe location that the Stock item will be moved out of. Generally, lines inherit the location from the invoice header.
UNITPRICE_INCTAXNoFLOAT(53)NoThe unit price of the Stock item on this line, inclusive of tax.
UPDATE_STOCKNoCHAR(1)NoIf this field is set to 'Y', stock movements are created via the trigger CR_INVLINES_POST. The field is then set to 'T'.
JOBCODENoVARCHAR(15)NoIf the line originates from a job, the job's code is recorded in this field.
CURRENCYNONoINTEGERNoThe ID number of the currency used on the line.
EXCHRATENoFLOAT(53)NoThe exchange rate for the line at the time of the transaction.
TAXRATENoFLOAT(53)NoThe percentage tax rate on the line at the time of the transaction.
CODETYPENoCHAR(1)NoThe type of code on the line. Will be one of:
G - GL code
S - Stock code
TAXRATE_NONoINTEGERNoThe ID number of the tax rate used by the transaction.
LINETOTAL_TAXNoFLOAT(53)NoThe tax total for the line.
LINETAX_OVERRIDDENNoCHAR(1)NoContains 'Y' if the tax on the line has been manually modified.
LINE_SOURCENoINTEGERNoThe source SEQNO of the line, if it originated from a Sales Order or job.
LINETOTALNoASNoA computed field containing the total value for the line, excluding tax.
LINETOTAL_INCTAXNoASNoA computed field containing the total value for the line, including tax.
JOBNONoINTEGERNoIf the line originates from a job, the job's ID number is recorded in this field.
COST_TYPENoINTEGERNoThis field only applies if EXO Job Costing is installed. It identifies the Cost Type that the transaction belongs to.
COST_GROUPNoINTEGERNoThis field only applies if EXO Job Costing is installed. It identifies the Cost Group that the transaction belongs to.
BRANCHNONoINTEGERNoThe branch number. Lines generally inherit the branch specified on the invoice header.
GLACCNONoINTEGERNoGL account number.
GLSUBACCNoINTEGERNoSL sub-account number.
BATCHCODENoVARCHAR(20)NoThe batch code on the line. The "Enable stock batch codes" Company-level profile setting must be enabled to be able to use batch codes.
CRINVLINEIDNoINTEGERNoA 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.
IGRLINESEQNONoINTEGERNoID number of the Inwards Goods receipt line (if the source of the line is from Inwards Goods).
LINETYPENoINTEGERNoThis 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
KITSEQNONoINTEGERNoA Bill of Materials SEQNO used to group BOM lines together.
KITCODENoVARCHAR(23)NoThe Bill of Materials code, if the line is part of a BOM.
LINKED_STOCKCODENoVARCHAR(23)NoThe physical stock unit.
LINKED_QTYNoFLOAT(53)NoThe physical stock unit quantity.
BOMTYPENoCHAR(1)NoThe type of Bill of Materials that the line relates to:
N = Normal stock item
B = Build
K = Kit
L = Lookup
SHOWLINENoCHAR(1)NoThis field applies to Bills of Materials, and is used by Clarity to determine if the line can be printed.
LINKEDSTATUSNoCHAR(1)NoWill be one of:
S = Stocked
L = Lookup
N = Not applicable
BOMPRICINGNoCHAR(1)NoHow Bills of Materials are priced. Will be one of:
C = Pricing by Component
T = Pricing by total
NARRATIVE_SEQNONoINTEGERNoThe SEQNO for the narrative on this line.
INTERCO_COMPANYNONoINTEGERNoThis field only applies if EXO InterCompany is used. It contains the Company ID number.
INTERCO_BATCH_DESCNoVARCHAR(80)NoThis field only applies if EXO InterCompany is used. It contains the batch description.
INTERCO_DR_BRANCHNONoINTEGERNoThis field only applies if EXO InterCompany is used. It contains the Debtor branch number.
INTERCO_DR_ACCNONoINTEGERNoThis field only applies if EXO InterCompany is used. It contains the Debtor GL account number.
INTERCO_DR_SUBACCNONoINTEGERNoThis field only applies if EXO InterCompany is used. It contains the Debtor GL sub-account number.
INTERCO_CR_BRANCHNONoINTEGERNoThis field only applies if EXO InterCompany is used. It contains the Creditor branch number.
INTERCO_CR_ACCNONoINTEGERNoThis field only applies if EXO InterCompany is used. It contains the Creditor GL account number.
INTERCO_CR_SUBACCNONoINTEGERNoThis field only applies if EXO InterCompany is used. It contains the Creditor GL sub-account number.

Relationships:
NameRelationship typeParentChildCardinality
CR_ACCS_CR_INVLINESNon IdentifyingCR_ACCSCR_INVLINESZero Or More
CR_INVLINES_CR_INVLINES_SERIALSNon IdentifyingCR_INVLINESCR_INVLINES_SERIALSZero Or More
CR_TRANS_CR_INVLINESNon IdentifyingCR_TRANSCR_INVLINESZero Or More
CURRENCIES_CR_INVLINESNon IdentifyingCURRENCIESCR_INVLINESZero Or More
NARRATIVES_CR_INVLINESNon IdentifyingNARRATIVESCR_INVLINESZero Or More
STOCK_ITEMS_CR_INVLINESNon IdentifyingSTOCK_ITEMSCR_INVLINESZero Or More
TAX_RATES_CR_INVLINESNon IdentifyingTAX_RATESCR_INVLINESZero Or More

Indexes:
NameIndex columnsIndex type
CR_INVLINES_ANALYSISANALYSIS ASC
CR_INVLINES_CODETYPECODETYPE ASC
CR_INVLINES_HDRSEQHDR_SEQNO ASC
SK_JC6JOBNO ASC

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (SEQNO)
CR_ACCS_CR_INVLINESForeign KeyTable ConstraintFOREIGN KEY (ACCNO) REFERENCES CR_ACCS(ACCNO)
CR_TRANS_CR_INVLINESForeign KeyTable ConstraintFOREIGN KEY (HDR_SEQNO) REFERENCES CR_TRANS(SEQNO)
CURRENCIES_CR_INVLINESForeign KeyTable ConstraintFOREIGN KEY (CURRENCYNO) REFERENCES CURRENCIES(CURRENCYNO)
DF_CR_INVLINES_UNITPRICE_INCTAXDefaultColumn ConstraintUNITPRICE_INCTAX DEFAULT 0
DF__CR_INVLIN__ACCNO__505BE5ADDefaultColumn ConstraintACCNO DEFAULT 0
DF__CR_INVLIN__ANALY__57FD0775DefaultColumn ConstraintANALYSIS DEFAULT 0
DF__CR_INVLIN__BOMPR__5091BB2EDefaultColumn ConstraintBOMPRICING DEFAULT N
DF__CR_INVLIN__BOMTY__4DB54E83DefaultColumn ConstraintBOMTYPE DEFAULT N
DF__CR_INVLIN__BRANC__2A4B4B5EDefaultColumn ConstraintBRANCHNO DEFAULT 0
DF__CR_INVLIN__COST___286302ECDefaultColumn ConstraintCOST_TYPE DEFAULT 0
DF__CR_INVLIN__COST___29572725DefaultColumn ConstraintCOST_GROUP DEFAULT 0
DF__CR_INVLIN__CRINV__10D65D75DefaultColumn ConstraintCRINVLINEID DEFAULT 0
DF__CR_INVLIN__CURRE__386F4D83DefaultColumn ConstraintCURRENCYNO DEFAULT 0
DF__CR_INVLIN__DISCO__25869641DefaultColumn ConstraintDISCOUNTAMT DEFAULT 0
DF__CR_INVLIN__DISCO__267ABA7ADefaultColumn ConstraintDISCOUNTPCT DEFAULT 0
DF__CR_INVLIN__DISCO__5708E33CDefaultColumn ConstraintDISCOUNT DEFAULT 0
DF__CR_INVLIN__EXCHR__396371BCDefaultColumn ConstraintEXCHRATE DEFAULT 1
DF__CR_INVLIN__GLACC__2B3F6F97DefaultColumn ConstraintGLACCNO DEFAULT 0
DF__CR_INVLIN__GLSUB__2C3393D0DefaultColumn ConstraintGLSUBACC DEFAULT 0
DF__CR_INVLIN__HDR_S__52442E1FDefaultColumn ConstraintHDR_SEQNO DEFAULT 0
DF__CR_INVLIN__IGRLI__2A2C1B24DefaultColumn ConstraintIGRLINESEQNO DEFAULT 0
DF__CR_INVLIN__INTER__32182106DefaultColumn ConstraintINTERCO_COMPANYNO DEFAULT 0
DF__CR_INVLIN__INTER__330C453FDefaultColumn ConstraintINTERCO_DR_BRANCHNO DEFAULT 0
DF__CR_INVLIN__INTER__34006978DefaultColumn ConstraintINTERCO_DR_ACCNO DEFAULT 0
DF__CR_INVLIN__INTER__34F48DB1DefaultColumn ConstraintINTERCO_DR_SUBACCNO DEFAULT 0
DF__CR_INVLIN__INTER__35E8B1EADefaultColumn ConstraintINTERCO_CR_BRANCHNO DEFAULT 0
DF__CR_INVLIN__INTER__36DCD623DefaultColumn ConstraintINTERCO_CR_ACCNO DEFAULT 0
DF__CR_INVLIN__INTER__37D0FA5CDefaultColumn ConstraintINTERCO_CR_SUBACCNO DEFAULT 0
DF__CR_INVLIN__JOBNO__276EDEB3DefaultColumn ConstraintJOBNO DEFAULT 0
DF__CR_INVLIN__KITSE__0FB81C0ADefaultColumn ConstraintKITSEQNO DEFAULT -1
DF__CR_INVLIN__LINET__0EC3F7D1DefaultColumn ConstraintLINETYPE DEFAULT -1
DF__CR_INVLIN__LINET__1CDC41A7DefaultColumn ConstraintLINETOTAL_TAX DEFAULT 0
DF__CR_INVLIN__LINKE__10AC4043DefaultColumn ConstraintLINKED_QTY DEFAULT 0
DF__CR_INVLIN__LINKE__4F9D96F5DefaultColumn ConstraintLINKEDSTATUS DEFAULT N
DF__CR_INVLIN__LOCAT__58F12BAEDefaultColumn ConstraintLOCATION DEFAULT 1
DF__CR_INVLIN__QUANT__55209ACADefaultColumn ConstraintQUANTITY DEFAULT 0
DF__CR_INVLIN__SHOWL__4EA972BCDefaultColumn ConstraintSHOWLINE DEFAULT Y
DF__CR_INVLIN__UNITP__5614BF03DefaultColumn ConstraintUNITPRICE DEFAULT 0
DF__CR_INVLIN__UPDAT__027D5126DefaultColumn ConstraintUPDATE_STOCK DEFAULT N
NARRATIVES_CR_INVLINESForeign KeyTable ConstraintFOREIGN KEY (NARRATIVE_SEQNO) REFERENCES NARRATIVES(SEQNO)
PK__CR_INVLINES__4F67C174Primary KeyTable ConstraintPRIMARY KEY (SEQNO)
STOCK_ITEMS_CR_INVLINESForeign KeyTable ConstraintFOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE)
TAX_RATES_CR_INVLINESForeign KeyTable ConstraintFOREIGN KEY (TAXRATE_NO) REFERENCES TAX_RATES(SEQNO)

Triggers:
Trigger nameCode
CRINVLINESIDCREATE 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_POSTCREATE 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
CommentThis table contains line information for all Debtor transactions.
Primary key columns

Attributes:
Column namePrimary keyData typeNot NULLComment
SEQNONoINTEGERYesA unique transaction ID.
ACCNONoINTEGERNoThe account number of the Debtor that the transaction belongs to.
INVNONoVARCHAR(20)NoThe invoice number.
HDR_SEQNONoINTEGERNoThe SEQNO of the record in the DR_TRANS table that this record related to.
STOCKCODENoVARCHAR(23)NoCode of the Stock item on this line.
DESCRIPTIONNoVARCHAR(40)NoDescription of the Stock item on this line.
QUANTITYNoFLOAT(53)NoThe quantity for this line.
UNITPRICENoFLOAT(53)NoThe unit price of the Stock item on this line.
DISCOUNTNoFLOAT(53)NoThe percentage discount for the Stock item on this line.
ANALYSISNoINTEGERNoGL 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".
LOCATIONNoINTEGERNoThe location that the Stock item will be moved out of. Generally, lines inherit the location from the invoice header.
UNITPRICE_INCTAXNoFLOAT(53)NoThe unit price of the Stock item on this line, inclusive of tax.
CUSTOMFIELDNoVARCHAR(30)No
UPDATE_STOCKNoCHAR(1)NoIf this field is set to 'Y', stock movements are created via the trigger DR_INVLINES_POST. The field is then set to 'T'.
UNITCOSTNoFLOAT(53)NoThe unit cost of the Stock item on this line.
JOBCODENoVARCHAR(15)NoIf the line originates from a job, the job's code is recorded in this field.
CURRENCYNONoINTEGERNoThe currency number for the line.
EXCHRATENoFLOAT(53)NoThe exchange rate for the line at the time of the transaction.
ORDERQTYNoFLOAT(53)NoThe Order Qty from the Sales Order.
BKORDQTYNoFLOAT(53)NoBack Order Qty from the Sales Order.
BATCHCODENoVARCHAR(20)NoThe batch code on the line. The "Enable stock batch codes" Company-level profile setting must be enabled to be able to use batch codes.
HIDDENNoCHAR(1)No
SUBCODENoINTEGERNoThe GL subaccount code on the line.
BRANCHNONoINTEGERNoThe branch number. Lines generally inherit the branch specified on the invoice header.
TAXRATENoFLOAT(53)NoThe percentage tax rate on the line at the time of the transaction.
TAXRATE_NONoINTEGERNoThe ID number of the tax rate used by the transaction.
LINETOTAL_TAXNoFLOAT(53)NoThe tax total for the line.
LINETAX_OVERRIDDENNoCHAR(1)NoContains 'Y' if the tax on the line has been manually modified.
LINE_SOURCENoINTEGERNoThe source SEQNO of the line, if it originated from a Sales Order or job.
JOBNONoINTEGERNoIf the line originates from a job, the job's ID number is recorded in this field.
SERIALNONoVARCHAR(50)No
TRANSDATENoDATETIMENoThe date of the transaction.
LINETOTALNoASNoA computed field containing the total value for the line, excluding tax.
LINETOTAL_INCTAXNoASNoA computed field containing the total value for the line, including tax.
NUNITPRNoASNoA computed field containing the unit price, taking into account the discount on the line.
LISTPRICENoFLOAT(53)NoThe original unit price for the Stock item on this line.
FREIGHT_FREENoCHAR(1)Yes'Y' or 'N' depending on whether the line is freight free. This comes from the price policy for the line.
CONTRACT_HDRNoINTEGERYes
DRINVLINEIDNoINTEGERNoA 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_SEQNONoINTEGERNoThe SEQNO for the narrative on this line.
PRICE_OVERRIDDENNoCHAR(1)YesContains 'Y' if the price on the line has been manually modified.
KITCODENoVARCHAR(23)NoThe Bill of Materials code, if the line is part of a BOM.
LINETYPENoINTEGERYesThis field indicates the type of line. Will be one of:
0 = Normal stockline
1 = BOM header line
2 = BOM component line
4 = Narrative line
KITSEQNONoINTEGERNoA Bill of Materials SEQNO used to group BOM lines together.
LINKED_STOCKCODENoVARCHAR(23)NoThe physical stock unit.
LINKED_QTYNoFLOAT(53)NoThe physical stock unit quantity.
HIDDEN_COSTNoFLOAT(53)NoThis 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_SELLNoFLOAT(53)NoThis 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.
BOMTYPENoCHAR(1)NoThe type of Bill of Materials that the line relates to: N = Normal stock item B = Build K = Kit L = Lookup
SHOWLINENoCHAR(1)NoThis field applies to Bills of Materials, and is used by Clarity to determine if the line can be printed.
LINKEDSTATUSNoCHAR(1)NoWill be one of: S = Stocked L = Lookup N = Not applicable
BOMPRICINGNoCHAR(1)NoHow Bills of Materials are priced. Will be one of: C = Pricing by Component T = Pricing by total
CREDITQTYNoINTEGERYesContains the sum of quantities which have been a part of credit notes against the transaction.
CODETYPENoCHAR(1)YesThe type of code on the line. Will be one of: G - GL code S - Stock code
JOB_CONTRACT_BILLINGS_SEQNONoINTEGERYesIf 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_LINENONoINTEGERYesThis field is the link used when allocating cost lines to DR_INVLINES.

Relationships:
NameRelationship typeParentChildCardinality
CURRENCIES_DR_INVLINESNon IdentifyingCURRENCIESDR_INVLINESZero Or More
DR_INVLINES_DR_INVLINES_SERIALSNon IdentifyingDR_INVLINESDR_INVLINES_SERIALSZero Or More
DR_INVLINES_JOB_TRANSACTIONSIdentifyingDR_INVLINESJOB_TRANSACTIONSZero Or More
DR_INVLINES_SALESORD_HDRNon IdentifyingDR_INVLINESSALESORD_HDRZero Or More
DR_TRANS_DR_INVLINES_ACCNONon IdentifyingDR_TRANSDR_INVLINESZero Or More
DR_TRANS_DR_INVLINES_SEQNONon IdentifyingDR_TRANSDR_INVLINESZero Or More
NARRATIVES_DR_INVLINESNon IdentifyingNARRATIVESDR_INVLINESZero Or More
STOCK_ITEMS_DR_INVLINESNon IdentifyingSTOCK_ITEMSDR_INVLINESZero Or More
TAX_RATES_DR_INVLINESNon IdentifyingTAX_RATESDR_INVLINESZero Or More

Indexes:
NameIndex columnsIndex type
DR_INVLINES_ANALYSISANALYSIS ASC
DR_INVLINES_HDRSEQHDR_SEQNO ASC
DR_INVLINES_LINE_SOURCELINE_SOURCE ASC
DR_INVLINES_STOCKCODESTOCKCODE ASC

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (COST_LINENO)
Not NullColumn ConstraintNOT NULL (SEQNO)
Not NullColumn ConstraintNOT NULL (JOB_CONTRACT_BILLINGS_SEQNO)
Not NullColumn ConstraintNOT NULL (CODETYPE)
Not NullColumn ConstraintNOT NULL (CREDITQTY)
Not NullColumn ConstraintNOT NULL (LINETYPE)
Not NullColumn ConstraintNOT NULL (PRICE_OVERRIDDEN)
Not NullColumn ConstraintNOT NULL (CONTRACT_HDR)
Not NullColumn ConstraintNOT NULL (FREIGHT_FREE)
CURRENCIES_DR_INVLINESForeign KeyTable ConstraintFOREIGN KEY (CURRENCYNO) REFERENCES CURRENCIES(CURRENCYNO)
DF__DR_INVLIN__ACCNO__1229A90ADefaultColumn ConstraintACCNO DEFAULT 0
DF__DR_INVLIN__ANALY__1ABEEF0BDefaultColumn ConstraintANALYSIS DEFAULT 0
DF__DR_INVLIN__BKORD__6A06A917DefaultColumn ConstraintBKORDQTY DEFAULT 0
DF__DR_INVLIN__BOMPR__4CC12A4ADefaultColumn ConstraintBOMPRICING DEFAULT N
DF__DR_INVLIN__BOMTY__49E4BD9FDefaultColumn ConstraintBOMTYPE DEFAULT N
DF__DR_INVLIN__BRANC__34E8D562DefaultColumn ConstraintBRANCHNO DEFAULT 0
DF__DR_INVLIN__CODET__7D45FCC2DefaultColumn ConstraintCODETYPE DEFAULT S
DF__DR_INVLIN__CONTR__485B9C89DefaultColumn ConstraintCONTRACT_HDR DEFAULT 0
DF__DR_INVLIN__COST___02FED618DefaultColumn ConstraintCOST_LINENO DEFAULT -1
DF__DR_INVLIN__CREDI__76D75FA7DefaultColumn ConstraintCREDITQTY DEFAULT 0
DF__DR_INVLIN__CURRE__36870511DefaultColumn ConstraintCURRENCYNO DEFAULT 0
DF__DR_INVLIN__DISCO__18D6A699DefaultColumn ConstraintDISCOUNT DEFAULT 0
DF__DR_INVLIN__DRINV__0FE2393CDefaultColumn ConstraintDRINVLINEID DEFAULT 0
DF__DR_INVLIN__EXCHR__377B294ADefaultColumn ConstraintEXCHRATE DEFAULT 1
DF__DR_INVLIN__FREIG__0D84EF7EDefaultColumn ConstraintFREIGHT_FREE DEFAULT N
DF__DR_INVLIN__HDR_S__1411F17CDefaultColumn ConstraintHDR_SEQNO DEFAULT 0
DF__DR_INVLIN__HIDDE__0CDBAF5FDefaultColumn ConstraintHIDDEN_COST DEFAULT 0
DF__DR_INVLIN__HIDDE__0DCFD398DefaultColumn ConstraintHIDDEN_SELL DEFAULT 0
DF__DR_INVLIN__JOBNO__6A1BB7B0DefaultColumn ConstraintJOBNO DEFAULT 0
DF__DR_INVLIN__JOB_C__020AB1DFDefaultColumn ConstraintJOB_CONTRACT_BILLINGS_SEQNO DEFAULT -1
DF__DR_INVLIN__KITSE__0BE78B26DefaultColumn ConstraintKITSEQNO DEFAULT -1
DF__DR_INVLIN__LINET__0E4EF685DefaultColumn ConstraintLINETYPE DEFAULT -1
DF__DR_INVLIN__LINET__1BE81D6EDefaultColumn ConstraintLINETOTAL_TAX DEFAULT 0
DF__DR_INVLIN__LINKE__4BCD0611DefaultColumn ConstraintLINKEDSTATUS DEFAULT N
DF__DR_INVLIN__LISTP__59063A47DefaultColumn ConstraintLISTPRICE DEFAULT 0
DF__DR_INVLIN__LOCAT__1BB31344DefaultColumn ConstraintLOCATION DEFAULT 1
DF__DR_INVLIN__ORDER__691284DEDefaultColumn ConstraintORDERQTY DEFAULT 0
DF__DR_INVLIN__PRICE__6641052BDefaultColumn ConstraintPRICE_OVERRIDDEN DEFAULT N
DF__DR_INVLIN__QUANT__16EE5E27DefaultColumn ConstraintQUANTITY DEFAULT 0
DF__DR_INVLIN__SHOWL__4AD8E1D8DefaultColumn ConstraintSHOWLINE DEFAULT Y
DF__DR_INVLIN__SUBCO__33F4B129DefaultColumn ConstraintSUBCODE DEFAULT 0
DF__DR_INVLIN__TRANS__6B0FDBE9DefaultColumn ConstraintTRANSDATE DEFAULT getdate()
DF__DR_INVLIN__UNITC__6B2FD77ADefaultColumn ConstraintUNITCOST DEFAULT 0
DF__DR_INVLIN__UNITP__17E28260DefaultColumn ConstraintUNITPRICE DEFAULT 0
DF__DR_INVLIN__UNITP__1CA7377DDefaultColumn ConstraintUNITPRICE_INCTAX DEFAULT 0
DF__DR_INVLIN__UPDAT__21F5FC7FDefaultColumn ConstraintUPDATE_STOCK DEFAULT Y
DR_TRANS_DR_INVLINES_ACCNOForeign KeyTable ConstraintFOREIGN KEY (ACCNO) REFERENCES DR_TRANS(ACCNO)
DR_TRANS_DR_INVLINES_SEQNOForeign KeyTable ConstraintFOREIGN KEY (HDR_SEQNO) REFERENCES DR_TRANS(SEQNO)
NARRATIVES_DR_INVLINESForeign KeyTable ConstraintFOREIGN KEY (SEQNO) REFERENCES NARRATIVES(SEQNO)
STOCK_ITEMS_DR_INVLINESForeign KeyTable ConstraintFOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE)
TAX_RATES_DR_INVLINESForeign KeyTable ConstraintFOREIGN KEY (TAXRATE_NO) REFERENCES TAX_RATES(SEQNO)

Triggers:
Trigger nameCode
DRINVLINESIDCREATE 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_POSTCREATE 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_UNITCOSTCREATE 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
CommentThis table contains details of all General Ledger accounts.
Primary key columnsACCNO

Attributes:
Column namePrimary keyData typeNot NULLComment
ACCNOYesINTEGERYesThe unique GL account number.
NAMENoVARCHAR(40)NoThe account name.
DRCRNoCHAR(1)NoThis field is set to 'C' for Credit accounts or 'D' for Debit accounts.
SECTIONNoINTEGERNoThis field is set to 0 for Profit and loss accounts or 1 for Balance sheet accounts.
OPENINGBALNoFLOAT(53)NoThe account's opening balance.
BALANCENoFLOAT(53)NoThe account's balance.
USESUBCODESNoCHAR(1)NoNot used.
REPORTCODENoVARCHAR(15)NoThe report code used by the GL account.
TAXSTATUSNoINTEGERNoThe Override GST rate specified for the GL account. A value of -1 corresponds to the AUTO SELECT RATE option.
CURRENCYNONoINTEGERNoThe ID number of the currency that the GL account is in.
ISACTIVENoCHAR(1)NoThis field contains 'Y' if the GL account is active.
ACCGROUPNoINTEGERNoThe GL account's Account Group.
ALLOWJOURNALNoCHAR(1)NoThis field is set to 'Y' if the GL account allows journals.
LAST_UPDATEDNoDATETIMENoThe date and time when the GL account was last updated.
BALSHEETACCTOTNoASNoThe balance sheet total for the account.
BALANCE_SHEET_TYPENoINTEGERYesFor 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
NOTESNoVARCHAR(4096)NoAny notes on the GL account.
EXCLUDE_FROM_REVALNoCHAR(1)NoIf this field is set to 'Y', the account is excluded from revaluation.
PRIVATE_ACCNoCHAR(1)YesThis field is set to 'Y' if the GL account is a private account.
CREATED_DATENoDATETIMENoThe date and time that the GL account was created.

Relationships:
NameRelationship typeParentChildCardinality
CURRENCIES_GLACCSNon IdentifyingCURRENCIESGLACCSZero Or More
GLACCS_CR_TRANSNon IdentifyingGLACCSCR_TRANSZero Or More
GLACCS_DR_TRANSNon IdentifyingGLACCSDR_TRANSZero Or More
GLACCS_JOBCOST_HDRNon IdentifyingGLACCSJOBCOST_HDRZero Or More
GLACCS_JOBCOST_HDR_SALESNon IdentifyingGLACCSJOBCOST_HDRZero Or More
GLACCS_JOBCOST_LINESNon IdentifyingGLACCSJOBCOST_LINESZero Or More
GLACCS_JOB_CONTRACT_BILLINGSNon IdentifyingGLACCSJOB_CONTRACT_BILLINGSZero Or More
GLACCS_JOB_CONTRACT_BILLINGS_SUBNon IdentifyingGLACCSJOB_CONTRACT_BILLINGSZero Or More
GLACCS_JOB_TRANSACTIONSNon IdentifyingGLACCSJOB_TRANSACTIONSZero Or More
GLACCS_OPPORTUNITY_QUOTENon IdentifyingGLACCSOPPORTUNITY_QUOTEZero Or More
GLACCS_STOCK_GROUPSNon IdentifyingGLACCSSTOCK_GROUPSZero Or More
GLACCS_STOCK_GROUPS_COSNon IdentifyingGLACCSSTOCK_GROUPSZero Or More
GLACCS_STOCK_GROUPS_PNon IdentifyingGLACCSSTOCK_GROUPSZero Or More
GLACCS_STOCK_ITEMSNon IdentifyingGLACCSSTOCK_ITEMSZero Or More
GLACCS_STOCK_ITEMS_COSNon IdentifyingGLACCSSTOCK_ITEMSZero Or More
GLACCS_STOCK_TRANSNon IdentifyingGLACCSSTOCK_TRANSZero Or More
GL_ACCS_STOCKITEMS_PONon IdentifyingGLACCSSTOCK_ITEMSZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (PRIVATE_ACC)
Not NullColumn ConstraintNOT NULL (ACCNO)
Not NullColumn ConstraintNOT NULL (BALANCE_SHEET_TYPE)
CURRENCIES_GLACCSForeign KeyTable ConstraintFOREIGN KEY (CURRENCYNO) REFERENCES CURRENCIES(CURRENCYNO)
DF__GLACCS__ACCGROUP__613C58ECDefaultColumn ConstraintACCGROUP DEFAULT 0
DF__GLACCS__ALLOWJOU__2882FE7DDefaultColumn ConstraintALLOWJOURNAL DEFAULT Y
DF__GLACCS__BALANCE__3E082B48DefaultColumn ConstraintBALANCE DEFAULT 0
DF__GLACCS__BALANCE___40113AA2DefaultColumn ConstraintBALANCE_SHEET_TYPE DEFAULT -1
DF__GLACCS__CURRENCY__3A228BCBDefaultColumn ConstraintCURRENCYNO DEFAULT 0
DF__GLACCS__DRCR__3B2BBE9DDefaultColumn ConstraintDRCR DEFAULT D
DF__GLACCS__EXCLUDE___23CA01AFDefaultColumn ConstraintEXCLUDE_FROM_REVAL DEFAULT N
DF__GLACCS__ISACTIVE__58A712EBDefaultColumn ConstraintISACTIVE DEFAULT Y
DF__GLACCS__OPENINGB__3D14070FDefaultColumn ConstraintOPENINGBAL DEFAULT 0
DF__GLACCS__PRIVATE___13697CB4DefaultColumn ConstraintPRIVATE_ACC DEFAULT N
DF__GLACCS__SECTION__3C1FE2D6DefaultColumn ConstraintSECTION DEFAULT 0
DF__GLACCS__TAXSTATU__51700577DefaultColumn ConstraintTAXSTATUS DEFAULT 0
DF__GLACCS__USESUBCO__3FF073BADefaultColumn ConstraintUSESUBCODES DEFAULT N
PK__GLACCS__3A379A64Primary KeyTable ConstraintPRIMARY KEY (ACCNO)



Entity: INWARDS_GOODS
CommentThis table contains header information on Inwards Goods transactions.
Primary key columnsSEQNO

Attributes:
Column namePrimary keyData typeNot NULLComment
SEQNOYesINTEGERYesA unique ID number for the transaction.
SUPPLIERNAMENoVARCHAR(70)NoThe name of the supplier on the transaction.
LOCNONoINTEGERYesThe ID number of the location on the transaction.
SUPPLIERREFNoVARCHAR(50)NoThe shipping reference on the transaction.
COMPLETENoCHAR(1)YesThis field contains 'Y' if the receipt has been completed.
NOTESNoVARCHAR(500)NoAny receipt notes on the transaction.
NUM_CARTONSNoINTEGERYesThe number of cartons.
INVOICEDNoCHAR(1)YesThis field contains 'Y' if the receipt has been invoiced.
GLBATCHNONoINTEGERYesThe GL batch number on the transaction.
SHIPMENTNONoINTEGERYesThe shipment number on the transaction.
TRANSDATENoDATETIMENoThe transaction date.
SUPPLIERNONoINTEGERYesThe ID number of the supplier (Creditor) account.
PACKSLIPNoVARCHAR(50)NoThe packing slip reference.
REVERSALSTATUSNoINTEGERNoThe reversal status. Will be one of:
0 = Normal
1 = Reversed
2 = Reversal
RELATED_SEQNONoINTEGERNoFor reversals, this field contains the source Inwards Goods ID number.
COSTED_PERIOD_SEQNONoINTEGERNoThe period to which the receipt belongs.
SESSION_IDNoINTEGERNoThe session ID for the record.
LASTINVSEQNONoINTEGERNoThe ID number of the last invoice generated against the receipt.

Relationships:
NameRelationship typeParentChildCardinality
CR_ACCS_INWARDS_GOODSNon IdentifyingCR_ACCSINWARDS_GOODSZero Or More
INWARDS_GOODS_INWARDS_GOODS_COSTSNon IdentifyingINWARDS_GOODSINWARDS_GOODS_COSTSZero Or More
INWARDS_GOODS_INWARDS_GOODS_LINESNon IdentifyingINWARDS_GOODSINWARDS_GOODS_LINESZero Or More
STOCK_LOCATIONS_INWARDS_GOODSNon IdentifyingSTOCK_LOCATIONSINWARDS_GOODSZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (SUPPLIERNO)
Not NullColumn ConstraintNOT NULL (SEQNO)
Not NullColumn ConstraintNOT NULL (LOCNO)
Not NullColumn ConstraintNOT NULL (SHIPMENTNO)
Not NullColumn ConstraintNOT NULL (COMPLETE)
Not NullColumn ConstraintNOT NULL (GLBATCHNO)
Not NullColumn ConstraintNOT NULL (NUM_CARTONS)
Not NullColumn ConstraintNOT NULL (INVOICED)
CR_ACCS_INWARDS_GOODSForeign KeyTable ConstraintFOREIGN KEY (SUPPLIERNO) REFERENCES CR_ACCS(ACCNO)
DF__INWARDS_G__COMPL__151B244EDefaultColumn ConstraintCOMPLETE DEFAULT N
DF__INWARDS_G__COSTE__3005BB62DefaultColumn ConstraintCOSTED_PERIOD_SEQNO DEFAULT -1
DF__INWARDS_G__GLBAT__18EBB532DefaultColumn ConstraintGLBATCHNO DEFAULT 0
DF__INWARDS_G__INVOI__17F790F9DefaultColumn ConstraintINVOICED DEFAULT N
DF__INWARDS_G__LASTI__647983A1DefaultColumn ConstraintLASTINVSEQNO DEFAULT -1
DF__INWARDS_G__LOCNO__1332DBDCDefaultColumn ConstraintLOCNO DEFAULT 0
DF__INWARDS_G__NUM_C__17036CC0DefaultColumn ConstraintNUM_CARTONS DEFAULT 0
DF__INWARDS_G__RELAT__25882CEFDefaultColumn ConstraintRELATED_SEQNO DEFAULT 0
DF__INWARDS_G__REVER__249408B6DefaultColumn ConstraintREVERSALSTATUS DEFAULT 0
DF__INWARDS_G__SESSI__34CA707FDefaultColumn ConstraintSESSION_ID DEFAULT -1
DF__INWARDS_G__SHIPM__19DFD96BDefaultColumn ConstraintSHIPMENTNO DEFAULT 0
DF__INWARDS_G__SUPPL__4C764630DefaultColumn ConstraintSUPPLIERNO DEFAULT 0
DF__INWARDS_G__TRANS__1AD3FDA4DefaultColumn ConstraintTRANSDATE DEFAULT getdate()
PK__INWARDS_GOODS__662B2B3BPrimary KeyTable ConstraintPRIMARY KEY (SEQNO)
STOCK_LOCATIONS_INWARDS_GOODSForeign KeyTable ConstraintFOREIGN KEY (LOCNO) REFERENCES STOCK_LOCATIONS(LOCNO)



Entity: INWARDS_GOODS_COSTS
CommentThis table contains information on Inwards Good Costing.
Primary key columnsSEQNO

Attributes:
Column namePrimary keyData typeNot NULLComment
SEQNOYesINTEGERYesA unique ID number for the record.
INWARDS_GOODS_SEQNONoINTEGERYesThe ID number of the Inwards Goods header record.
COSTCODENoVARCHAR(15)NoThe on cost code for the record.
DETAILSNoVARCHAR(40)NoDetails of the on cost.
SPREAD_TYPENoINTEGERYesThe spread type for on costs:
0 = Quantity
1 = Value
2 = Weight
3 = Cubic
FC_COSTNoFLOAT(53)YesThe cost in the currency of the supplier account.
EXCHRATENoFLOAT(53)YesThe exchange rate.
COSTNoFLOAT(53)YesThe cost in local currency.
SHIPMENTNONoINTEGERNoThe shipment number on the record.
ACCNONoINTEGERNoThe supplier (Creditor account ID).
INVOICE_NOWNoFLOAT(53)No
GLBATCHNoINTEGERNoGL batch number.
FX_VARNoFLOAT(53)NoThe foreign exchange variance applicable to the invoice raised against this record.
INV_FC_COSTNoFLOAT(53)NoThe invoiced amount in the currency of the supplier account.
INV_EXCHRATENoFLOAT(53)NoThe exchange rate applicable to the invoice.
INV_COSTNoFLOAT(53)NoThe invoiced amount in local currency.
COMPLETENoCHAR(1)YesIndicates if the line has been marked as completed, which controls whether or not the line can be edited.
GLPOSTEDNoCHAR(1)YesThis field contains 'Y' if a GL journal exists for the record.
CAN_SPREADNoCHAR(1)Yes
TRANSDATENoDATETIMENoThe transaction date of the invoice linked to this record.
PERIOD_SEQNONoINTEGERYesThe period ID of the invoice linked to this record.
AGE_STAMPNoINTEGERNoThis is a historical field that has been superseded by the AGE calculated field.
AGENoASNoThe invoice's age. This is calculated based on PERIOD_SEQNO.

Relationships:
NameRelationship typeParentChildCardinality
CR_ACCS_INWARDS_GOODS_COSTSNon IdentifyingCR_ACCSINWARDS_GOODS_COSTSZero Or More
INWARDS_GOODS_INWARDS_GOODS_COSTSNon IdentifyingINWARDS_GOODSINWARDS_GOODS_COSTSZero Or More
PERIOD_STATUS_INWARDS_GOODS_COSTSNon IdentifyingPERIOD_STATUSINWARDS_GOODS_COSTSZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (PERIOD_SEQNO)
Not NullColumn ConstraintNOT NULL (SEQNO)
Not NullColumn ConstraintNOT NULL (INWARDS_GOODS_SEQNO)
Not NullColumn ConstraintNOT NULL (CAN_SPREAD)
Not NullColumn ConstraintNOT NULL (SPREAD_TYPE)
Not NullColumn ConstraintNOT NULL (GLPOSTED)
Not NullColumn ConstraintNOT NULL (FC_COST)
Not NullColumn ConstraintNOT NULL (COMPLETE)
Not NullColumn ConstraintNOT NULL (EXCHRATE)
Not NullColumn ConstraintNOT NULL (COST)
CR_ACCS_INWARDS_GOODS_COSTSForeign KeyTable ConstraintFOREIGN KEY (ACCNO) REFERENCES CR_ACCS(ACCNO)
DF__INWARDS_GO__COST__41EDCAC5DefaultColumn ConstraintCOST DEFAULT 0
DF__INWARDS_G__ACCNO__40457975DefaultColumn ConstraintACCNO DEFAULT 0
DF__INWARDS_G__AGE_S__4AB9B19EDefaultColumn ConstraintAGE_STAMP DEFAULT -1
DF__INWARDS_G__CAN_S__49CEE3AFDefaultColumn ConstraintCAN_SPREAD DEFAULT Y
DF__INWARDS_G__COMPL__47E69B3DDefaultColumn ConstraintCOMPLETE DEFAULT N
DF__INWARDS_G__EXCHR__40F9A68CDefaultColumn ConstraintEXCHRATE DEFAULT 0
DF__INWARDS_G__FC_CO__40058253DefaultColumn ConstraintFC_COST DEFAULT 0
DF__INWARDS_G__FX_VA__44160A59DefaultColumn ConstraintFX_VAR DEFAULT 0
DF__INWARDS_G__GLBAT__4321E620DefaultColumn ConstraintGLBATCH DEFAULT 0
DF__INWARDS_G__GLPOS__48DABF76DefaultColumn ConstraintGLPOSTED DEFAULT N
DF__INWARDS_G__INVOI__41399DAEDefaultColumn ConstraintINVOICE_NOW DEFAULT 0
DF__INWARDS_G__INV_C__46F27704DefaultColumn ConstraintINV_COST DEFAULT 0
DF__INWARDS_G__INV_E__45FE52CBDefaultColumn ConstraintINV_EXCHRATE DEFAULT 0
DF__INWARDS_G__INV_F__450A2E92DefaultColumn ConstraintINV_FC_COST DEFAULT 0
DF__INWARDS_G__INWAR__3C34F16FDefaultColumn ConstraintINWARDS_GOODS_SEQNO DEFAULT 0
DF__INWARDS_G__PERIO__49C58D65DefaultColumn ConstraintPERIOD_SEQNO DEFAULT 0
DF__INWARDS_G__SHIPM__3F51553CDefaultColumn ConstraintSHIPMENTNO DEFAULT -1
DF__INWARDS_G__SPREA__3F115E1ADefaultColumn ConstraintSPREAD_TYPE DEFAULT 0
DF__INWARDS_G__TRANS__39CE5167DefaultColumn ConstraintTRANSDATE DEFAULT getdate()
INWARDS_GOODS_INWARDS_GOODS_COSTSForeign KeyTable ConstraintFOREIGN KEY (INWARDS_GOODS_SEQNO) REFERENCES INWARDS_GOODS(SEQNO)
PERIOD_STATUS_INWARDS_GOODS_COSTSForeign KeyTable ConstraintFOREIGN KEY (PERIOD_SEQNO) REFERENCES PERIOD_STATUS(SEQNO)
PK__INWARDS_GOODS_CO__671F4F74Primary KeyTable ConstraintPRIMARY KEY (SEQNO)



Entity: INWARDS_GOODS_LINES
CommentThis table contains line-level information on Inwards Goods transactions.
Primary key columnsSEQNO

Attributes:
Column namePrimary keyData typeNot NULLComment
SEQNOYesINTEGERYesA unique ID number for the record.
HDR_SEQNONoINTEGERYesThe ID number of the header Inwards Goods record.
PO_NUMBERNoINTEGERYesThe ID number of the related Purchase Order header.
PO_LINE_NUMNoINTEGERYesThe ID number of the related Purchase Order line.
SHIPMENTNONoINTEGERYesThe shipment number on the record.
LOCNONoINTEGERYesThe ID of the location on the record.
BATCHNONoINTEGERYesThe batch number on the record.
BRANCHNONoINTEGERYesThe ID number of the branch on the record.
JOBNONoINTEGERYesIf the order originates from a job, this field records the ID number of the job.
COST_TYPENoINTEGERYesIf the order originates from a job, this field records the ID number of the job's cost type.
COST_GROUPNoINTEGERYesIf the order originates from a job, this field records the ID number of the job's cost group.
STOCKCODENoVARCHAR(23)NoThe stock code of the stock item on the line.
DESCRIPTIONNoVARCHAR(40)NoThe description of the stock item on the line.
QUANTITYNoFLOAT(53)NoThe receipt quantity on the line.
UNITPRICENoFLOAT(53)NoThe unit price of the stock item on the line.
DUTYCOSTNoFLOAT(53)NoThe duty cost on the line.
FIXEDCOSTNoFLOAT(53)NoThe fixed cost on the line.
LCOSTNoFLOAT(53)YesThe landed cost on the line.
FXCOSTNoFLOAT(53)YesThe cost on the line in local currency.
INV_QUANTNoFLOAT(53)YesThe invoiced quantity on the line.
BATCHCODENoVARCHAR(20)NoThe batch code on the line.
EXPIRY_DATENoDATETIMENoThe expiry date of the stock item, if there is one.
SUPPLIERCODENoVARCHAR(50)NoThe supplier code on the line.
PURCHPACKQUANTNoFLOAT(53)YesThe purchase pack quantity on the line.
PURCHPACKPRICENoFLOAT(53)YesThe purchase pack price on the line.
EXCHRATENoFLOAT(53)NoThe exchange rate on the line.
LINETYPENoINTEGERNoThe line type. Will be one of:
0 = Normal line
1 = BOM header
2 = BOM component
4 = Comment
KITSEQNONoINTEGERNoThe ID number of the Bill of Materials on the line.
KITCODENoVARCHAR(23)NoThe ID code of the Bill of Materials on the line.
LINKED_STOCKCODENoVARCHAR(23)NoThe linked stock item code on the line.
LINKED_QTYNoFLOAT(53)NoThe linked quantity on the line.
BOMTYPENoCHAR(1)NoThe Bill of Materials type on the line. Will be one of:
N = Normal
K = Kit
B = Build
L = Linked
SHOWLINENoCHAR(1)NoThis field is used to control the visibility of the line on Clarity forms.
LINKEDSTATUSNoCHAR(1)NoS = Stock item, L = Lookup item.
BOMPRICINGNoCHAR(1)NoC = BOM priced by Component, T = BOM priced by total, N = Normal
COMPLETENoCHAR(1)NoThis field contains 'Y' if the inwards goods receipt has been completed (and therefore cannot be altered).
DISCOUNTNoFLOAT(53)NoThe discount percentage on the line.
INV_FC_COSTNoFLOAT(53)NoThe cost as on the related invoice.
INV_EXCHRATENoFLOAT(53)NoThe exchange rate applicable to the invoice line.
INV_COSTNoFLOAT(53)NoThe cost as on the related invoice, in local currency.
INV_COMPLETENoCHAR(1)NoThis field contains 'Y' if the invoice is complete.
VAR_GLPOSTEDNoCHAR(1)NoThis field contains 'Y' if variance has been posted against this line.
FX_VARNoFLOAT(53)NoThe foreign exchange variance applicable to the line at the time of invoicing.
INV_TRANSDATENoDATETIMENoThe transaction date of the invoice related to this line.
INWGLIDNoINTEGERNoAn ID number for the inwards goods lines record.
PERIOD_SEQNONoINTEGERYesThe ID number of the period on the invoice linked to this record.
AGE_STAMPNoINTEGERNoThis is a historical field that has been superseded by the AGE calculated field.
INV_AGENoASNoThe invoice's age. This is calculated based on PERIOD_SEQNO.

Relationships:
NameRelationship typeParentChildCardinality
BRANCHES_INWARDS_GOODS_LINESNon IdentifyingBRANCHESINWARDS_GOODS_LINESZero Or More
INWARDS_GOODS_INWARDS_GOODS_LINESNon IdentifyingINWARDS_GOODSINWARDS_GOODS_LINESZero Or More
JOBCOST_HDR_INWARDS_GOODS_LINESNon IdentifyingJOBCOST_HDRINWARDS_GOODS_LINESZero Or More
JOB_COSTGROUPS_INWARDS_GOODS_LINESNon IdentifyingJOB_COSTGROUPSINWARDS_GOODS_LINESZero Or More
JOB_COSTTYPES_INWARDS_GOODS_LINESNon IdentifyingJOB_COSTTYPESINWARDS_GOODS_LINESZero Or More
PERIOD_STATUS_INWARDS_GOODS_LINESNon IdentifyingPERIOD_STATUSINWARDS_GOODS_LINESZero Or More
PURCHORD_HDR_INWARDS_GOODS_LINESNon IdentifyingPURCHORD_HDRINWARDS_GOODS_LINESZero Or More
PURCHORD_LINES_INWARDS_GOODS_LINESNon IdentifyingPURCHORD_LINESINWARDS_GOODS_LINESZero Or More
STOCK_ITEMS_INWARDS_GOODS_LINESNon IdentifyingSTOCK_ITEMSINWARDS_GOODS_LINESZero Or More
STOCK_LOCATIONS_INWARDS_GOODS_LINESNon IdentifyingSTOCK_LOCATIONSINWARDS_GOODS_LINESZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (PERIOD_SEQNO)
Not NullColumn ConstraintNOT NULL (SEQNO)
Not NullColumn ConstraintNOT NULL (HDR_SEQNO)
Not NullColumn ConstraintNOT NULL (PURCHPACKPRICE)
Not NullColumn ConstraintNOT NULL (PO_NUMBER)
Not NullColumn ConstraintNOT NULL (PURCHPACKQUANT)
Not NullColumn ConstraintNOT NULL (PO_LINE_NUM)
Not NullColumn ConstraintNOT NULL (INV_QUANT)
Not NullColumn ConstraintNOT NULL (SHIPMENTNO)
Not NullColumn ConstraintNOT NULL (FXCOST)
Not NullColumn ConstraintNOT NULL (LOCNO)
Not NullColumn ConstraintNOT NULL (LCOST)
Not NullColumn ConstraintNOT NULL (BATCHNO)
Not NullColumn ConstraintNOT NULL (COST_GROUP)
Not NullColumn ConstraintNOT NULL (BRANCHNO)
Not NullColumn ConstraintNOT NULL (COST_TYPE)
Not NullColumn ConstraintNOT NULL (JOBNO)
BRANCHES_INWARDS_GOODS_LINESForeign KeyTable ConstraintFOREIGN KEY (BRANCHNO) REFERENCES BRANCHES(BRANCHNO)
DF__INWARDS_G__AGE_S__4CA1FA10DefaultColumn ConstraintAGE_STAMP DEFAULT -1
DF__INWARDS_G__BATCH__22751F6CDefaultColumn ConstraintBATCHNO DEFAULT 0
DF__INWARDS_G__BOMPR__5CF79213DefaultColumn ConstraintBOMPRICING DEFAULT N
DF__INWARDS_G__BOMTY__5A1B2568DefaultColumn ConstraintBOMTYPE DEFAULT N
DF__INWARDS_G__BRANC__236943A5DefaultColumn ConstraintBRANCHNO DEFAULT 0
DF__INWARDS_G__COMPL__0DEFCF29DefaultColumn ConstraintCOMPLETE DEFAULT N
DF__INWARDS_G__COST___25518C17DefaultColumn ConstraintCOST_TYPE DEFAULT 0
DF__INWARDS_G__COST___2645B050DefaultColumn ConstraintCOST_GROUP DEFAULT 0
DF__INWARDS_G__DISCO__332153D8DefaultColumn ConstraintDISCOUNT DEFAULT 0
DF__INWARDS_G__DUTYC__2B0A656DDefaultColumn ConstraintDUTYCOST DEFAULT 0
DF__INWARDS_G__FIXED__2BFE89A6DefaultColumn ConstraintFIXEDCOST DEFAULT 0
DF__INWARDS_G__FXCOS__4E5E8EA2DefaultColumn ConstraintFXCOST DEFAULT 0
DF__INWARDS_G__FX_VA__697D6489DefaultColumn ConstraintFX_VAR DEFAULT 0
DF__INWARDS_G__HDR_S__1DB06A4FDefaultColumn ConstraintHDR_SEQNO DEFAULT 0
DF__INWARDS_G__INV_C__66A0F7DEDefaultColumn ConstraintINV_COST DEFAULT 0
DF__INWARDS_G__INV_C__67951C17DefaultColumn ConstraintINV_COMPLETE DEFAULT N
DF__INWARDS_G__INV_E__65ACD3A5DefaultColumn ConstraintINV_EXCHRATE DEFAULT 0
DF__INWARDS_G__INV_F__64B8AF6CDefaultColumn ConstraintINV_FC_COST DEFAULT 0
DF__INWARDS_G__INV_Q__6FF48C97DefaultColumn ConstraintINV_QUANT DEFAULT 0
DF__INWARDS_G__INV_T__6B65ACFBDefaultColumn ConstraintINV_TRANSDATE DEFAULT getdate()
DF__INWARDS_G__INWGL__267C5128DefaultColumn ConstraintINWGLID DEFAULT 0
DF__INWARDS_G__JOBNO__245D67DEDefaultColumn ConstraintJOBNO DEFAULT 0
DF__INWARDS_G__KITSE__1570F560DefaultColumn ConstraintKITSEQNO DEFAULT -1
DF__INWARDS_G__LCOST__4D6A6A69DefaultColumn ConstraintLCOST DEFAULT 0
DF__INWARDS_G__LINET__147CD127DefaultColumn ConstraintLINETYPE DEFAULT -1
DF__INWARDS_G__LINKE__16651999DefaultColumn ConstraintLINKED_QTY DEFAULT 0
DF__INWARDS_G__LINKE__5C036DDADefaultColumn ConstraintLINKEDSTATUS DEFAULT N
DF__INWARDS_G__LOCNO__2180FB33DefaultColumn ConstraintLOCNO DEFAULT 0
DF__INWARDS_G__PERIO__4BADD5D7DefaultColumn ConstraintPERIOD_SEQNO DEFAULT 0
DF__INWARDS_G__PO_LI__1F98B2C1DefaultColumn ConstraintPO_LINE_NUM DEFAULT 0
DF__INWARDS_G__PO_NU__1EA48E88DefaultColumn ConstraintPO_NUMBER DEFAULT 0
DF__INWARDS_G__PURCH__04708690DefaultColumn ConstraintPURCHPACKQUANT DEFAULT 1
DF__INWARDS_G__PURCH__0564AAC9DefaultColumn ConstraintPURCHPACKPRICE DEFAULT 0
DF__INWARDS_G__QUANT__29221CFBDefaultColumn ConstraintQUANTITY DEFAULT 0
DF__INWARDS_G__SHIPM__208CD6FADefaultColumn ConstraintSHIPMENTNO DEFAULT 0
DF__INWARDS_G__SHOWL__5B0F49A1DefaultColumn ConstraintSHOWLINE DEFAULT Y
DF__INWARDS_G__UNITP__2A164134DefaultColumn ConstraintUNITPRICE DEFAULT 0
DF__INWARDS_G__VAR_G__68894050DefaultColumn ConstraintVAR_GLPOSTED DEFAULT N
INWARDS_GOODS_INWARDS_GOODS_LINESForeign KeyTable ConstraintFOREIGN KEY (HDR_SEQNO) REFERENCES INWARDS_GOODS(SEQNO)
JOBCOST_HDR_INWARDS_GOODS_LINESForeign KeyTable ConstraintFOREIGN KEY (JOBNO) REFERENCES JOBCOST_HDR(JOBNO)
JOB_COSTGROUPS_INWARDS_GOODS_LINESForeign KeyTable ConstraintFOREIGN KEY (COST_GROUP) REFERENCES JOB_COSTGROUPS(SEQNO)
JOB_COSTTYPES_INWARDS_GOODS_LINESForeign KeyTable ConstraintFOREIGN KEY (COST_TYPE) REFERENCES JOB_COSTTYPES(SEQNO)
PERIOD_STATUS_INWARDS_GOODS_LINESForeign KeyTable ConstraintFOREIGN KEY (PERIOD_SEQNO) REFERENCES PERIOD_STATUS(SEQNO)
PK__INWARDS_GOODS_LI__681373ADPrimary KeyTable ConstraintPRIMARY KEY (SEQNO)
PURCHORD_HDR_INWARDS_GOODS_LINESForeign KeyTable ConstraintFOREIGN KEY (PO_NUMBER) REFERENCES PURCHORD_HDR(SEQNO)
PURCHORD_LINES_INWARDS_GOODS_LINESForeign KeyTable ConstraintFOREIGN KEY (PO_LINE_NUM) REFERENCES PURCHORD_LINES(SEQNO)
STOCK_ITEMS_INWARDS_GOODS_LINESForeign KeyTable ConstraintFOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE)
STOCK_LOCATIONS_INWARDS_GOODS_LINESForeign KeyTable ConstraintFOREIGN KEY (LOCNO) REFERENCES STOCK_LOCATIONS(LOCNO)



Entity: STAFF
CommentThis 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 columnsSTAFFNO

Attributes:
Column namePrimary keyData typeNot NULLComment
STAFFNOYesINTEGERYesA unique identifier for the staff record.
NAMENoVARCHAR(30)NoThe staff member's full name.
JOBTITLENoVARCHAR(30)NoThe staff member's job title.
EXTENSIONNoVARCHAR(12)NoThe staff member's telephone extension.
PHONENoVARCHAR(30)NoThe staff member's work telephone number.
HOMEPHONENoVARCHAR(30)NoThe staff member's home telephone number.
ISACTIVENoCHAR(1)NoThis 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_PASSWORDNoVARCHAR(30)NoThe staff member's password, used when logging in to the EXO Business system. Passwords are stored in an encrypted format.
MENU_NONoINTEGERNoThe ID number of the Menu Definition specified for the staff member. Corresponds to the menu's SEQNO field in the MENU_COLLECTION table.
AUTH_AMTNoFLOAT(53)NoThe staff member's Credit Invoice Authorisation Limit.
STOCK_AUTH_AMTNoFLOAT(53)NoThe staff member's Stock Purchase Order Authorisation Limit.
NON_STOCK_AUTH_AMTNoFLOAT(53)NoThe staff member's Non-Stock Purchase Order Authorisation Limit.
SECURITYPROFILEIDNoINTEGERYesThe ID number of the staff member's Security profile. Corresponds to the profile's ID field in the PROFILE table.
USERPROFILEIDNoINTEGERYesThe ID number of the staff member's User profile. Corresponds to the profile's ID field in the PROFILE table.
LOGINIDNoVARCHAR(30)YesThe staff member's login ID. This is the ID used to log in to the EXO Business system.
PASSWORD_CHANGEDNoDATETIMEYesThe date and time when the staff member's password was last changed.
LAST_BAD_LOGINNoDATETIMENoThe date and time when the staff member last attempted to log in but failed.
BAD_LOGIN_COUNTNoINTEGERYesThe number of times the staff member has attempted to log in but failed.
LAST_LOGINNoDATETIMENoThe date and time when the staff member last successfully logged in.
ACCOUNT_STATUSNoINTEGERYesThe staff member's account status. Will be one of:
0 = Account OK
1 = Account Locked
2 = Password Expired
EMAIL_ADDRESSNoVARCHAR(50)NoThe staff member's email address.
DISCOUNTRATENoFLOAT(53)YesThe staff member's Discount Rate Limit.
PAYROLL_IDNoVARCHAR(15)NoThe 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_SUPERVISORNoCHAR(1)YesThis 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.
NICKNAMENoVARCHAR(15)NoA short nickname for the employee.
ABSENTNoCHAR(1)YesThis 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_CODENoINTEGERYesThis field is used by the payroll integration functionality.
SMTP_SEQNONoINTEGERNoWhere 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_BUDGETSNoCHAR(1)NoThis 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_STAFFNONoINTEGERNoThe ID number of the staff member that this staff member reports to.
FACEBOOK_ACCESS_TOKENNoVARCHAR(max)NoIf 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_KEYNoVARCHAR(max)NoIf 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_SECRETNoVARCHAR(max)NoIf 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_KEYNoVARCHAR(max)NoIf 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_SECRETNoVARCHAR(max)NoIf 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_ACCESSNoCHAR(1)NoThis field contains 'Y' if the staff member is authorised to use the EXO API.
MOBILE_ACCESSNoCHAR(1)YesThis field contains 'Y' if the staff member is authorised to use the EXO OnTheGo mobile app.
BANKFEED_REFRESHTOKENNoVARCHAR(500)NoEncrypted OAuth refresh token used for Bank Feeds.
SBR_REFRESHTOKENNoVARCHAR(1024)NoThe refresh token used for SBR authentication.

Relationships:
NameRelationship typeParentChildCardinality
FK_CRM_BUDGET_EXCLUDED_STAFF_STAFFNon IdentifyingSTAFFCRM_BUDGET_EXCLUDED_STAFFZero Or More
FK_CRM_BUDGET_LINE_STAFFNon IdentifyingSTAFFCRM_BUDGET_LINEZero Or More
MENU_COLLECTION_STAFFNon IdentifyingMENU_COLLECTIONSTAFFZero Or More
STAFF_BILLOMAT_TEMPNon IdentifyingSTAFFBILLOMAT_TEMPZero Or More
STAFF_CAMPAIGNNon IdentifyingSTAFFCAMPAIGNZero Or More
STAFF_CAMPAIGN_HISTNon IdentifyingSTAFFCAMPAIGN_HISTZero Or More
STAFF_CONTACTSNon IdentifyingSTAFFCONTACTSZero Or More
STAFF_CONTACT_LISTNon IdentifyingSTAFFCONTACT_LISTZero Or More
STAFF_CONTACT_LIST1Non IdentifyingSTAFFCONTACT_LISTZero Or More
STAFF_CR_ACCSNon IdentifyingSTAFFCR_ACCSZero Or More
STAFF_CR_CONT_HISTNon IdentifyingSTAFFCR_CONT_HISTZero Or More
STAFF_DR_ACCSNon IdentifyingSTAFFDR_ACCSOne Or More
STAFF_DR_CONT_HISTNon IdentifyingSTAFFDR_CONT_HISTOne Or More
STAFF_DR_TRANSNon IdentifyingSTAFFDR_TRANSZero Or More
STAFF_JOBCOST_HDRNon IdentifyingSTAFFJOBCOST_HDRZero Or More
STAFF_JOBCOST_HDR_MGRNon IdentifyingSTAFFJOBCOST_HDRZero Or More
STAFF_JOBCOST_RESOURCENon IdentifyingSTAFFJOBCOST_RESOURCEZero Or More
STAFF_JOB_TIMESHEETSNon IdentifyingSTAFFJOB_TIMESHEETSZero Or More
STAFF_JOB_TIMESHEET_ALLOWANCENon IdentifyingSTAFFJOB_TIMESHEET_ALLOWANCEZero Or More
STAFF_JOB_TRANSACTIONSNon IdentifyingSTAFFJOB_TRANSACTIONSZero Or More
STAFF_OPPORTUNITY_HISTNon IdentifyingSTAFFOPPORTUNITY_HISTZero Or More
STAFF_STOCK_REQUESTSNon IdentifyingSTAFFSTOCK_REQUESTSZero Or More
STAFF_STOCK_TRANS_HDRNon IdentifyingSTAFFSTOCK_TRANS_HDRZero Or More
STAFF_TASKSNon IdentifyingSTAFFTASKSZero Or More
STAFF_TASKS1Non IdentifyingSTAFFTASKSZero Or More
STAFF_TASKS2Non IdentifyingSTAFFTASKSZero Or More
STAFF_TASKS3Non IdentifyingSTAFFTASKSZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (MOBILE_ACCESS)
Not NullColumn ConstraintNOT NULL (STAFFNO)
Not NullColumn ConstraintNOT NULL (SECURITYPROFILEID)
Not NullColumn ConstraintNOT NULL (EMPLOYEE_CODE)
Not NullColumn ConstraintNOT NULL (USERPROFILEID)
Not NullColumn ConstraintNOT NULL (ABSENT)
Not NullColumn ConstraintNOT NULL (LOGINID)
Not NullColumn ConstraintNOT NULL (PASSWORD_CHANGED)
Not NullColumn ConstraintNOT NULL (IS_SUPERVISOR)
Not NullColumn ConstraintNOT NULL (BAD_LOGIN_COUNT)
Not NullColumn ConstraintNOT NULL (DISCOUNTRATE)
Not NullColumn ConstraintNOT NULL (ACCOUNT_STATUS)
DF__STAFF__ABSENT__12CA5E36DefaultColumn ConstraintABSENT DEFAULT N
DF__STAFF__ACCOUNT_S__5CACADF9DefaultColumn ConstraintACCOUNT_STATUS DEFAULT 0
DF__STAFF__BAD_LOGIN__5BB889C0DefaultColumn ConstraintBAD_LOGIN_COUNT DEFAULT 0
DF__STAFF__DISCOUNTR__2C146396DefaultColumn ConstraintDISCOUNTRATE DEFAULT 0
DF__STAFF__EMPLOYEE___3DB4BC3BDefaultColumn ConstraintEMPLOYEE_CODE DEFAULT -1
DF__STAFF__HAS_BUDGE__6855017DDefaultColumn ConstraintHAS_BUDGETS DEFAULT N
DF__STAFF__IS_SUPERV__24F33012DefaultColumn ConstraintIS_SUPERVISOR DEFAULT N
DF__STAFF__MOBILE_AC__38F0D275DefaultColumn ConstraintMOBILE_ACCESS DEFAULT N
DF__STAFF__PASSWORD___5AC46587DefaultColumn ConstraintPASSWORD_CHANGED DEFAULT getdate()
DF__STAFF__REPORTS_T__694925B6DefaultColumn ConstraintREPORTS_TO_STAFFNO DEFAULT -1
DF__STAFF__SECURITYP__58DC1D15DefaultColumn ConstraintSECURITYPROFILEID DEFAULT 0
DF__STAFF__SMTP_SEQN__6B667852DefaultColumn ConstraintSMTP_SEQNO DEFAULT -1
DF__STAFF__USERPROFI__59D0414EDefaultColumn ConstraintUSERPROFILEID DEFAULT 0
MENU_COLLECTION_STAFFForeign KeyTable ConstraintFOREIGN KEY (MENU_NO) REFERENCES MENU_COLLECTION(SEQNO)
PK_STAFFPrimary KeyTable ConstraintPRIMARY KEY (STAFFNO)



Entity: STOCKREQUIREMENT
CommentThis table contains the data used by the Forecast Based Purchasing feature.
Primary key columns

Attributes:
Column namePrimary keyData typeNot NULLComment
STOCKCODENoVARCHAR(23)YesThe stock item's ID code.
LOCNONoINTEGERNoThe ID number of the stock item's location (from STOCK_LOCATIONS).
DESCRIPTIONNoVARCHAR(40)NoThe stock item's description.
ACCNONoINTEGERNoThe ID number of the stock item's supplier (from CRACCS).
STOCKGROUPNoINTEGERNoThe ID number of the stock item's Primary Stock Group.
MINSTOCKNoFLOAT(53)NoThe minimum stock level for the stock item.
MAXSTOCKNoFLOAT(53)NoThe maximum stock level for the stock item.
INSTOCKQTYNoFLOAT(53)NoPhysical stock.
PURCHORDQTYNoFLOAT(53)NoIncoming stock.
SALESORDQTYNoFLOAT(53)NoCommitted stock.
VIRTSTOCKNoFLOAT(53)NoFree stock taking into account the incoming and committed stock.
CALCREORDNoFLOAT(53)NoReorder quantity, calculated taking into account minimum stock, maximum stock and available free stock.
ACTUALREORDNoFLOAT(53)NoActual reorder quantity for the stock item.
SALES0NoFLOAT(53)NoCurrent month sales quantity for the stock item.
SALES1NoFLOAT(53)NoSales quantity for the stock item for the month prior to current month.
SALES2NoFLOAT(53)NoSales quantity for the stock item two months prior to current month.
SALES3NoFLOAT(53)NoSales quantity for the stock item three months prior to current month.
SALES4NoFLOAT(53)NoSales quantity for the stock item four months prior to current month.
SALES5NoFLOAT(53)NoSales quantity for the stock item five months prior to current month.
SALES6NoFLOAT(53)NoSales quantity for the stock item six months prior to current month.
SALES7NoFLOAT(53)NoSales quantity for the stock item seven months prior to current month.
SALES8NoFLOAT(53)NoSales quantity for the stock item eight months prior to current month.
SALES9NoFLOAT(53)NoSales quantity for the stock item nine months prior to current month.
SALES10NoFLOAT(53)NoSales quantity for the stock item ten months prior to current month.
SALES11NoFLOAT(53)NoSales quantity for the stock item eleven months prior to current month.
SALES12NoFLOAT(53)NoSales quantity for the stock item twelve months prior to current month.

Relationships:
NameRelationship typeParentChildCardinality
STOCK_GROUPS_STOCKREQUIREMENTNon IdentifyingSTOCK_GROUPSSTOCKREQUIREMENTZero Or More
STOCK_ITEMS_STOCKREQUIREMENTNon IdentifyingSTOCK_ITEMSSTOCKREQUIREMENTZero Or More
STOCK_LOCATIONS_STOCKREQUIREMENTNon IdentifyingSTOCK_LOCATIONSSTOCKREQUIREMENTZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (STOCKCODE)
DF__STOCKREQU__ACCNO__19EAC663DefaultColumn ConstraintACCNO DEFAULT 0
DF__STOCKREQU__ACTUA__22800C64DefaultColumn ConstraintACTUALREORD DEFAULT 0
DF__STOCKREQU__CALCR__218BE82BDefaultColumn ConstraintCALCREORD DEFAULT 0
DF__STOCKREQU__INSTO__1DBB5747DefaultColumn ConstraintINSTOCKQTY DEFAULT 0
DF__STOCKREQU__MAXST__1CC7330EDefaultColumn ConstraintMAXSTOCK DEFAULT 0
DF__STOCKREQU__MINST__1BD30ED5DefaultColumn ConstraintMINSTOCK DEFAULT 0
DF__STOCKREQU__PURCH__1EAF7B80DefaultColumn ConstraintPURCHORDQTY DEFAULT 0
DF__STOCKREQU__SALES__1FA39FB9DefaultColumn ConstraintSALESORDQTY DEFAULT 0
DF__STOCKREQU__SALES__5244F976DefaultColumn ConstraintSALES7 DEFAULT 0
DF__STOCKREQU__SALES__53391DAFDefaultColumn ConstraintSALES8 DEFAULT 0
DF__STOCKREQU__SALES__542D41E8DefaultColumn ConstraintSALES9 DEFAULT 0
DF__STOCKREQU__SALES__55216621DefaultColumn ConstraintSALES10 DEFAULT 0
DF__STOCKREQU__SALES__56158A5ADefaultColumn ConstraintSALES11 DEFAULT 0
DF__STOCKREQU__SALES__5709AE93DefaultColumn ConstraintSALES12 DEFAULT 0
DF__STOCKREQU__SALES__7795AE5FDefaultColumn ConstraintSALES0 DEFAULT 0
DF__STOCKREQU__SALES__7889D298DefaultColumn ConstraintSALES1 DEFAULT 0
DF__STOCKREQU__SALES__797DF6D1DefaultColumn ConstraintSALES2 DEFAULT 0
DF__STOCKREQU__SALES__7A721B0ADefaultColumn ConstraintSALES3 DEFAULT 0
DF__STOCKREQU__SALES__7B663F43DefaultColumn ConstraintSALES4 DEFAULT 0
DF__STOCKREQU__SALES__7C5A637CDefaultColumn ConstraintSALES5 DEFAULT 0
DF__STOCKREQU__SALES__7D4E87B5DefaultColumn ConstraintSALES6 DEFAULT 0
DF__STOCKREQU__STOCK__1ADEEA9CDefaultColumn ConstraintSTOCKGROUP DEFAULT 0
DF__STOCKREQU__VIRTS__2097C3F2DefaultColumn ConstraintVIRTSTOCK DEFAULT 0
STOCK_GROUPS_STOCKREQUIREMENTForeign KeyTable ConstraintFOREIGN KEY (STOCKGROUP) REFERENCES STOCK_GROUPS(GROUPNO)
STOCK_ITEMS_STOCKREQUIREMENTForeign KeyTable ConstraintFOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE)
STOCK_LOCATIONS_STOCKREQUIREMENTForeign KeyTable ConstraintFOREIGN KEY (LOCNO) REFERENCES STOCK_LOCATIONS(LOCNO)



Entity: STOCKTAKE_CTRL
CommentThis table contains information on the status of stocktake locations.
Primary key columnsLOCNO

Attributes:
Column namePrimary keyData typeNot NULLComment
LOCNOYesINTEGERYesThe ID number of the stock location.
LOCNAMENoVARCHAR(30)NoThe name of the stock location.
ISACTIVENoCHAR(1)NoThis field contains 'Y' is the location is currently active.
TIME_INITIALISEDNoDATETIMENoThe date and time when the location was initialised for stocktake.
TIME_COUNTSHEETSNoDATETIMENoThe date and time when count sheets were printed for the location.
TIME_KEYEDNoDATETIMENoThe date and time when counted quantities were keyed for the location.
TIME_UPLOADEDNoDATETIMENoThe date and time when stock transactions were uploaded to the Stock ledger.
CUSTOM_FILTERNoVARCHAR(100)NoThis field contains the custom SQL filter defined for the location, if there is one.

Relationships:
NameRelationship typeParentChildCardinality
STOCK_LOCATIONS_STOCKTAKE_CTRLIdentifyingSTOCK_LOCATIONSSTOCKTAKE_CTRLZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (LOCNO)
DF__STOCKTAKE__CUSTO__3FBC34E7DefaultColumn ConstraintCUSTOM_FILTER DEFAULT N
DF__STOCKTAKE__ISACT__6CC31A31DefaultColumn ConstraintISACTIVE DEFAULT N
PK__STOCKTAKE_CTRL__6BCEF5F8Primary KeyTable ConstraintPRIMARY KEY (LOCNO)
STOCK_LOCATIONS_STOCKTAKE_CTRLForeign KeyTable ConstraintFOREIGN KEY (LOCNO) REFERENCES STOCK_LOCATIONS(LOCNO)



Entity: STOCKTAKE_TOTALS
CommentThis table contains the stock totals entered as part of a stocktake.
Primary key columnsSEQNO

Attributes:
Column namePrimary keyData typeNot NULLComment
SEQNOYesINTEGERYesA unique ID number for the stocktake line.
STOCKCODENoVARCHAR(23)NoThe ID code of the stock item being counted.
DESCRIPTIONNoVARCHAR(40)NoThe description of the stock item being counted.
LOCATIONNoINTEGERNoThe ID number of the location where the stock was counted.
BINCODENoVARCHAR(12)NoThe Bin Code for the stock item being counted.
SYSTEMQTYNoFLOAT(53)NoThe quantity recorded in the EXO Business system for the item at this location.
COUNTQTYNoFLOAT(53)NoThe physical count taken for the item at this location.
VARIANCENoFLOAT(53)NoThe difference between the system quantity and the counted quantity. Populated automatically when the Stocktake Variance Report is run for the Stock Take utility.
SUPPLIERNONoINTEGERNoThe ID number of the Creditor who supplies the stock item.
STOCKGROUPNoINTEGERNoThe Stock Group that the stock item belongs to.
BATCHCODENoVARCHAR(20)NoThe batchcode for the stock item.
UNITCOSTNoFLOAT(53)NoThe unit cost for the stock item. Unit costs are only entered during stock take if the "Initialise cost prices" stock take option is enabled.
STOCKGROUP2NoINTEGERNoThe Alternative Stock Group
HAS_BNNoCHAR(1)YesThis field is set to 'Y' if the stock item is batch tracked.
SERIALNO_TYPENoINTEGERNoHow 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_EXPIRYNoCHAR(1)YesThis field is set to 'Y' if the stock item has an expiry date.
EXPIRY_DAYSNoINTEGERNoThe number of days until the stock item batch expires.
EXPIRY_DATENoDATETIMENoThe date when the stock item batch expires.
STOCKGROUP_REPCNoVARCHAR(15)NoThe report code of the stock item's Primary Stock Group.
STOCKGROUP2_REPCNoVARCHAR(15)NoThe report code of the stock item's Secondary Stock Group.
BARCODE1NoVARCHAR(30)NoBarcode/alternate code 1.

Relationships:
NameRelationship typeParentChildCardinality
STOCK_GROUP2S_STOCKTAKE_TOTALSNon IdentifyingSTOCK_GROUP2SSTOCKTAKE_TOTALSZero Or More
STOCK_GROUP2S_STOCKTAKE_TOTALS_REPCODENon IdentifyingSTOCK_GROUP2SSTOCKTAKE_TOTALSZero Or More
STOCK_GROUPS_STOCKTAKE_TOTALSNon IdentifyingSTOCK_GROUPSSTOCKTAKE_TOTALSZero Or More
STOCK_GROUPS_STOCKTAKE_TOTALS_REPCODENon IdentifyingSTOCK_GROUPSSTOCKTAKE_TOTALSZero Or More
STOCK_ITEMS_STOCKTAKE_TOTALSNon IdentifyingSTOCK_ITEMSSTOCKTAKE_TOTALSZero Or More
STOCK_ITEMS_STOCKTAKE_TOTALS_BNNon IdentifyingSTOCK_ITEMSSTOCKTAKE_TOTALSZero Or More
STOCK_ITEMS_STOCKTAKE_TOTALS_EXPDAYSNon IdentifyingSTOCK_ITEMSSTOCKTAKE_TOTALSZero Or More
STOCK_ITEMS_STOCKTAKE_TOTALS_EXPIRYNon IdentifyingSTOCK_ITEMSSTOCKTAKE_TOTALSZero Or More
STOCK_ITEMS_STOCKTAKE_TOTALS_SERIALNon IdentifyingSTOCK_ITEMSSTOCKTAKE_TOTALSZero Or More
STOCK_LOCATIONS_STOCKTAKE_TOTALSNon IdentifyingSTOCK_LOCATIONSSTOCKTAKE_TOTALSZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (HAS_EXPIRY)
Not NullColumn ConstraintNOT NULL (SEQNO)
Not NullColumn ConstraintNOT NULL (HAS_BN)
DF__STOCKTAKE__COUNT__5DD5DC5CDefaultColumn ConstraintCOUNTQTY DEFAULT 0
DF__STOCKTAKE__EXPIR__41A47D59DefaultColumn ConstraintEXPIRY_DAYS DEFAULT 1
DF__STOCKTAKE__HAS_B__3DD3EC75DefaultColumn ConstraintHAS_BN DEFAULT N
DF__STOCKTAKE__HAS_E__40B05920DefaultColumn ConstraintHAS_EXPIRY DEFAULT N
DF__STOCKTAKE__SERIA__3EC810AEDefaultColumn ConstraintSERIALNO_TYPE DEFAULT 0
DF__STOCKTAKE__STOCK__3CDFC83CDefaultColumn ConstraintSTOCKGROUP2 DEFAULT 0
DF__STOCKTAKE__STOCK__60B24907DefaultColumn ConstraintSTOCKGROUP DEFAULT 0
DF__STOCKTAKE__SUPPL__5FBE24CEDefaultColumn ConstraintSUPPLIERNO DEFAULT 0
DF__STOCKTAKE__SYSTE__5CE1B823DefaultColumn ConstraintSYSTEMQTY DEFAULT 0
DF__STOCKTAKE__UNITC__3BEBA403DefaultColumn ConstraintUNITCOST DEFAULT 0
DF__STOCKTAKE__VARIA__5ECA0095DefaultColumn ConstraintVARIANCE DEFAULT 0
PK_STOCKTAKE_TOTALSPrimary KeyTable ConstraintPRIMARY KEY (SEQNO)
STOCK_GROUP2S_STOCKTAKE_TOTALSForeign KeyTable ConstraintFOREIGN KEY (STOCKGROUP2) REFERENCES STOCK_GROUP2S(GROUPNO)
STOCK_GROUP2S_STOCKTAKE_TOTALS_REPCODEForeign KeyTable ConstraintFOREIGN KEY (STOCKGROUP2_REPC) REFERENCES STOCK_GROUP2S(REPORTCODE)
STOCK_GROUPS_STOCKTAKE_TOTALSForeign KeyTable ConstraintFOREIGN KEY (STOCKGROUP) REFERENCES STOCK_GROUPS(GROUPNO)
STOCK_GROUPS_STOCKTAKE_TOTALS_REPCODEForeign KeyTable ConstraintFOREIGN KEY (STOCKGROUP_REPC) REFERENCES STOCK_GROUPS(REPORTCODE)
STOCK_ITEMS_STOCKTAKE_TOTALSForeign KeyTable ConstraintFOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE)
STOCK_ITEMS_STOCKTAKE_TOTALS_BNForeign KeyTable ConstraintFOREIGN KEY (HAS_BN) REFERENCES STOCK_ITEMS(HAS_BN)
STOCK_ITEMS_STOCKTAKE_TOTALS_EXPDAYSForeign KeyTable ConstraintFOREIGN KEY (EXPIRY_DAYS) REFERENCES STOCK_ITEMS(EXPIRY_DAYS)
STOCK_ITEMS_STOCKTAKE_TOTALS_EXPIRYForeign KeyTable ConstraintFOREIGN KEY (HAS_EXPIRY) REFERENCES STOCK_ITEMS(HAS_EXPIRY)
STOCK_ITEMS_STOCKTAKE_TOTALS_SERIALForeign KeyTable ConstraintFOREIGN KEY (SERIALNO_TYPE) REFERENCES STOCK_ITEMS(SERIALNO_TYPE)
STOCK_LOCATIONS_STOCKTAKE_TOTALSForeign KeyTable ConstraintFOREIGN KEY (LOCATION) REFERENCES STOCK_LOCATIONS(LOCNO)



Entity: STOCK_CLASSIFICATIONS
CommentThis table stores the Stock Classifications that are assigned to stock items.
Primary key columnsCLASSNO

Attributes:
Column namePrimary keyData typeNot NULLComment
CLASSNOYesINTEGERYesA unique ID number for the classification.
CLASSNAMENoVARCHAR(100)YesThe name of the classification.

Relationships:
NameRelationship typeParentChildCardinality
STOCK_CLASSIFICATIONS_STOCK_ITEMSNon IdentifyingSTOCK_CLASSIFICATIONSSTOCK_ITEMSZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (CLASSNAME)
Not NullColumn ConstraintNOT NULL (CLASSNO)
PK__STOCK_CLASSIFICA__0E391C95Primary KeyTable ConstraintPRIMARY KEY (CLASSNO)



Entity: STOCK_COLOUR
CommentThis table contains details of the colours used by the Style, Colour, Size feature.
Primary key columnsCOLOURID

Attributes:
Column namePrimary keyData typeNot NULLComment
COLOURIDYesINTEGERYesA unique ID number for the colour.
COLOURCODENoVARCHAR(5)YesA user-defined ID code for the colour.
COLOURNAMENoVARCHAR(30)NoA descriptive name for the colour.
SWATCHIDNoINTEGERNoThe colour value of the colour record's swatch.
ISACTIVENoCHAR(1)NoThis field contains 'Y' if the colour is currently active.
SORTORDERNoINTEGERNoThis field contains the sort order value, which is used to order colours on the Style Picker window.


Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (COLOURCODE)
Not NullColumn ConstraintNOT NULL (COLOURID)
DF__STOCK_COL__ISACT__3EC810AEDefaultColumn ConstraintISACTIVE DEFAULT Y
DF__STOCK_COL__SORTO__05113BBCDefaultColumn ConstraintSORTORDER DEFAULT 0
DF__STOCK_COL__SWATC__3DD3EC75DefaultColumn ConstraintSWATCHID DEFAULT -1
PK__STOCK_COLOUR__06599A59Primary KeyTable ConstraintPRIMARY KEY (COLOURID)



Entity: STOCK_GROUP2S
CommentThis table contains details of Alternate (Coarse) Stock Groups.
Primary key columnsGROUPNO

Attributes:
Column namePrimary keyData typeNot NULLComment
GROUPNOYesINTEGERYesA unique ID number for the group.
GROUPNAMENoVARCHAR(30)NoThe group's name/description.
BRANCHNONoINTEGERNoThe ID number of the group's branch (from BRANCHES).
STATUSNoVARCHAR(1)NoThis field contains 'Y' if the 'Display on website option' for the group is ticked.
FILENAMENoVARCHAR(50)NoThis field contains the filename of the website picture for the group.
DATAX_EXCHANGE_FLAGNoCHAR(1)Yes
DATAX_SOURCE_SITENoINTEGERYes
DATAX_EXCHNONoINTEGERYes
DATAX_SITE_NO2NoINTEGERYes
AUTOCODENoVARCHAR(10)NoThe One-off Stock Item autocode prefix for the group.
AUTOCODENONoINTEGERNoThe One-off Stock Item autocode suffix for the group.
REPORTCODENoVARCHAR(15)NoA report code for the group.

Relationships:
NameRelationship typeParentChildCardinality
BRANCHES_STOCK_GROUP2SNon IdentifyingBRANCHESSTOCK_GROUP2SZero Or More
FK_CRM_BUDGET_EXCLUDED_STOCK_GROUP2S_STOCK_GROUP2SNon IdentifyingSTOCK_GROUP2SCRM_BUDGET_EXCLUDED_STOCK_GROUP2SZero Or More
FK_CRM_BUDGET_LINE_STOCK_GROUP2SNon IdentifyingSTOCK_GROUP2SCRM_BUDGET_LINEZero Or More
STOCK_GROUP2S_STOCKTAKE_TOTALSNon IdentifyingSTOCK_GROUP2SSTOCKTAKE_TOTALSZero Or More
STOCK_GROUP2S_STOCKTAKE_TOTALS_REPCODENon IdentifyingSTOCK_GROUP2SSTOCKTAKE_TOTALSZero Or More
STOCK_GROUP2S_STOCK_GROUPSNon IdentifyingSTOCK_GROUP2SSTOCK_GROUPSZero Or More
STOCK_GROUP2S_STOCK_ITEMSNon IdentifyingSTOCK_GROUP2SSTOCK_ITEMSZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (DATAX_SITE_NO2)
Not NullColumn ConstraintNOT NULL (GROUPNO)
Not NullColumn ConstraintNOT NULL (DATAX_EXCHNO)
Not NullColumn ConstraintNOT NULL (DATAX_SOURCE_SITE)
Not NullColumn ConstraintNOT NULL (DATAX_EXCHANGE_FLAG)
BRANCHES_STOCK_GROUP2SForeign KeyTable ConstraintFOREIGN KEY (BRANCHNO) REFERENCES BRANCHES(BRANCHNO)
DF__STOCK_GRO__BRANC__72D0F942DefaultColumn ConstraintBRANCHNO DEFAULT 0
DF__STOCK_GRO__DATAX__74B941B4DefaultColumn ConstraintDATAX_EXCHANGE_FLAG DEFAULT I
DF__STOCK_GRO__DATAX__75AD65EDDefaultColumn ConstraintDATAX_SOURCE_SITE DEFAULT 0
DF__STOCK_GRO__DATAX__76A18A26DefaultColumn ConstraintDATAX_EXCHNO DEFAULT 0
DF__STOCK_GRO__DATAX__7795AE5FDefaultColumn ConstraintDATAX_SITE_NO2 DEFAULT 0
DF__STOCK_GRO__STATU__73C51D7BDefaultColumn ConstraintSTATUS DEFAULT Y
PK__STOCK_GROUP2S__0F2D40CEPrimary KeyTable ConstraintPRIMARY KEY (GROUPNO)



Entity: STOCK_GROUPS
CommentThis table contains details of Primary (Fine) Stock Groups. Note: All fields prefixed with X_ relate to the Style, Colour, Size feature.
Primary key columnsGROUPNO

Attributes:
Column namePrimary keyData typeNot NULLComment
GROUPNOYesINTEGERYesA unique ID code for the group.
GROUPNAMENoVARCHAR(30)NoThe group's name/description.
BRANCHNONoINTEGERNoThe ID number of the group's branch (from BRANCHES).
AUTOCODENoVARCHAR(10)NoThe One-off Stock Item autocode prefix for the group.
AUTOCODENONoINTEGERNoThe One-off Stock Item autocode suffix for the group.
STATUSNoCHAR(1)NoThis field contains 'Y' if the 'Display on website option' for the group is ticked.
FILENAMENoVARCHAR(50)NoThis field contains the filename of the website picture for the group.
GROUP2_SEQNONoINTEGERNoThe ID number of the related Alternate Stock Group.
REPORTCODENoVARCHAR(15)NoA report code for the group.
EXPECTEDPROFITNoFLOAT(53)NoThe expected profit margin for the group.
PROFITVARIANCENoFLOAT(53)NoThe expected margin variance for the group.
SALES_GL_CODENoINTEGERNoThe code of the GL account for Sales.
SALES_GLSUBCODENoINTEGERNoThe code of the GL subaccount for Sales.
PURCH_GL_CODENoINTEGERNoThe code of the GL account for Purchases Clearing.
PURCH_GLSUBCODENoINTEGERNoThe code of the GL subaccount for Purchases Clearing.
COS_GL_CODENoINTEGERNoThe code of the GL account for Cost of Sales.
COS_GLSUBCODENoINTEGERNoThe code of the GL subaccount for Cost of Sales.
ISACTIVENoCHAR(1)NoThis field contains 'Y' if the group is currently active.
X_ISSTYLENoCHAR(1)NoThis field contains 'Y' if the group is a style.
X_SIZEIDSNoVARCHAR(255)NoThis field contains a comma-separated list of size IDs applicable to the style.
X_COLOURIDSNoVARCHAR(255)NoThis field contains a comma-separated list of colour IDs applicable to the style.
X_PRICEGROUPNoCHAR(1)NoThis field contains 'Y' if the style has a matching Price Group.
X_STOCKCODE_FORMULANoVARCHAR(23)NoThe stock item code formula for the group.
X_DESCRIPTION_FORMULANoVARCHAR(40)NoThe stock item description formula for the group.
X_SUPPLIERNONoINTEGERNoThe ID number of the main supplier of the style.
X_SUPPLIERCOSTNoFLOAT(53)NoThe supplier cost in the currency of the supplier account.
X_MINSTOCKNoFLOAT(53)NoThe minimum stock level for the style.
X_MAXSTOCKNoFLOAT(53)NoThe maximum stock level for the style.
X_BINCODENoVARCHAR(12)NoThe bin code for the style.
X_SELLPRICE1NoFLOAT(53)NoSell price 1 for the style.
X_SELLPRICE2NoFLOAT(53)NoSell price 2 for the style.
X_SELLPRICE3NoFLOAT(53)NoSell price 3 for the style.
X_SELLPRICE4NoFLOAT(53)NoSell price 4 for the style.
X_SELLPRICE5NoFLOAT(53)NoSell price 5 for the style.
X_SELLPRICE6NoFLOAT(53)NoSell price 6 for the style.
X_SELLPRICE7NoFLOAT(53)NoSell price 7 for the style.
X_SELLPRICE8NoFLOAT(53)NoSell price 8 for the style.
X_SELLPRICE9NoFLOAT(53)NoSell price 9 for the style.
X_SELLPRICE10NoFLOAT(53)NoSell price 10 for the style.

Relationships:
NameRelationship typeParentChildCardinality
BRANCHES_STOCK_GROUPSNon IdentifyingBRANCHESSTOCK_GROUPSZero Or More
FK_CRM_BUDGET_EXCLUDED_STOCK_GROUPS_STOCK_GROUPSNon IdentifyingSTOCK_GROUPSCRM_BUDGET_EXCLUDED_STOCK_GROUPSZero Or More
FK_CRM_BUDGET_LINE_STOCK_GROUPSNon IdentifyingSTOCK_GROUPSCRM_BUDGET_LINEZero Or More
GLACCS_STOCK_GROUPSNon IdentifyingGLACCSSTOCK_GROUPSZero Or More
GLACCS_STOCK_GROUPS_COSNon IdentifyingGLACCSSTOCK_GROUPSZero Or More
GLACCS_STOCK_GROUPS_PNon IdentifyingGLACCSSTOCK_GROUPSZero Or More
STOCK_GROUP2S_STOCK_GROUPSNon IdentifyingSTOCK_GROUP2SSTOCK_GROUPSZero Or More
STOCK_GROUPS_STOCKREQUIREMENTNon IdentifyingSTOCK_GROUPSSTOCKREQUIREMENTZero Or More
STOCK_GROUPS_STOCKTAKE_TOTALSNon IdentifyingSTOCK_GROUPSSTOCKTAKE_TOTALSZero Or More
STOCK_GROUPS_STOCKTAKE_TOTALS_REPCODENon IdentifyingSTOCK_GROUPSSTOCKTAKE_TOTALSZero Or More
STOCK_GROUPS_STOCK_ITEMSNon IdentifyingSTOCK_GROUPSSTOCK_ITEMSZero Or More
STOCK_PRICEGROUPS_STOCK_GROUPSNon IdentifyingSTOCK_PRICEGROUPSSTOCK_GROUPSZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (GROUPNO)
BRANCHES_STOCK_GROUPSForeign KeyTable ConstraintFOREIGN KEY (BRANCHNO) REFERENCES BRANCHES(BRANCHNO)
DF__STOCK_GRO__BRANC__60C757A0DefaultColumn ConstraintBRANCHNO DEFAULT 0
DF__STOCK_GRO__COS_G__2BB53C3ADefaultColumn ConstraintCOS_GL_CODE DEFAULT 0
DF__STOCK_GRO__COS_G__2CA96073DefaultColumn ConstraintCOS_GLSUBCODE DEFAULT 0
DF__STOCK_GRO__EXPEC__1A5FC7AFDefaultColumn ConstraintEXPECTEDPROFIT DEFAULT 0
DF__STOCK_GRO__GROUP__03FB8544DefaultColumn ConstraintGROUP2_SEQNO DEFAULT 0
DF__STOCK_GRO__ISACT__2F85CD1EDefaultColumn ConstraintISACTIVE DEFAULT Y
DF__STOCK_GRO__PROFI__1B53EBE8DefaultColumn ConstraintPROFITVARIANCE DEFAULT 0
DF__STOCK_GRO__PURCH__29CCF3C8DefaultColumn ConstraintPURCH_GL_CODE DEFAULT 0
DF__STOCK_GRO__PURCH__2AC11801DefaultColumn ConstraintPURCH_GLSUBCODE DEFAULT 0
DF__STOCK_GRO__SALES__27E4AB56DefaultColumn ConstraintSALES_GL_CODE DEFAULT 0
DF__STOCK_GRO__SALES__28D8CF8FDefaultColumn ConstraintSALES_GLSUBCODE DEFAULT 0
DF__STOCK_GRO__STATU__1DD065E0DefaultColumn ConstraintSTATUS DEFAULT N
DF__STOCK_GRO__X_ISS__3FBC34E7DefaultColumn ConstraintX_ISSTYLE DEFAULT N
DF__STOCK_GRO__X_MAX__7ADCFAB5DefaultColumn ConstraintX_MAXSTOCK DEFAULT 0
DF__STOCK_GRO__X_MIN__79E8D67CDefaultColumn ConstraintX_MINSTOCK DEFAULT 0
DF__STOCK_GRO__X_PRI__4298A192DefaultColumn ConstraintX_PRICEGROUP DEFAULT Y
DF__STOCK_GRO__X_SEL__0095D40BDefaultColumn ConstraintX_SELLPRICE5 DEFAULT 0
DF__STOCK_GRO__X_SEL__0189F844DefaultColumn ConstraintX_SELLPRICE6 DEFAULT 0
DF__STOCK_GRO__X_SEL__027E1C7DDefaultColumn ConstraintX_SELLPRICE7 DEFAULT 0
DF__STOCK_GRO__X_SEL__037240B6DefaultColumn ConstraintX_SELLPRICE8 DEFAULT 0
DF__STOCK_GRO__X_SEL__046664EFDefaultColumn ConstraintX_SELLPRICE9 DEFAULT 0
DF__STOCK_GRO__X_SEL__055A8928DefaultColumn ConstraintX_SELLPRICE10 DEFAULT 0
DF__STOCK_GRO__X_SEL__7CC54327DefaultColumn ConstraintX_SELLPRICE1 DEFAULT 0
DF__STOCK_GRO__X_SEL__7DB96760DefaultColumn ConstraintX_SELLPRICE2 DEFAULT 0
DF__STOCK_GRO__X_SEL__7EAD8B99DefaultColumn ConstraintX_SELLPRICE3 DEFAULT 0
DF__STOCK_GRO__X_SEL__7FA1AFD2DefaultColumn ConstraintX_SELLPRICE4 DEFAULT 0
DF__STOCK_GRO__X_SUP__78008E0ADefaultColumn ConstraintX_SUPPLIERNO DEFAULT 0
DF__STOCK_GRO__X_SUP__78F4B243DefaultColumn ConstraintX_SUPPLIERCOST DEFAULT 0
GLACCS_STOCK_GROUPSForeign KeyTable ConstraintFOREIGN KEY (SALES_GL_CODE) REFERENCES GLACCS(ACCNO)
GLACCS_STOCK_GROUPS_COSForeign KeyTable ConstraintFOREIGN KEY (COS_GL_CODE) REFERENCES GLACCS(ACCNO)
GLACCS_STOCK_GROUPS_PForeign KeyTable ConstraintFOREIGN KEY (PURCH_GL_CODE) REFERENCES GLACCS(ACCNO)
PK__STOCK_GROUPS__0F183235Primary KeyTable ConstraintPRIMARY KEY (GROUPNO)
STOCK_GROUP2S_STOCK_GROUPSForeign KeyTable ConstraintFOREIGN KEY (GROUP2_SEQNO) REFERENCES STOCK_GROUP2S(GROUPNO)
STOCK_PRICEGROUPS_STOCK_GROUPSForeign KeyTable ConstraintFOREIGN KEY (X_PRICEGROUP) REFERENCES STOCK_PRICEGROUPS(GROUPNO)



Entity: STOCK_ITEMS
CommentThis table contains details of all stock items.
Primary key columnsSTOCKCODE

Attributes:
Column namePrimary keyData typeNot NULLComment
STOCKCODEYesVARCHAR(23)YesA unique ID code for the stock item.
DESCRIPTIONNoVARCHAR(40)NoThe stock item's description.
STOCKGROUPNoINTEGERNoThe Primary (Fine) Stock Group that the item belongs to.
STATUSNoCHAR(1)NoThe stock item's status. Will be one of:
S = Stock item
L = Lookup item
SELLPRICE1NoFLOAT(53)NoSell price 1.
SELLPRICE2NoFLOAT(53)NoSell price 2.
SELLPRICE3NoFLOAT(53)NoSell price 3.
SELLPRICE4NoFLOAT(53)NoSell price 4.
SELLPRICE5NoFLOAT(53)NoSell price 5.
SELLPRICE6NoFLOAT(53)NoSell price 6.
SELLPRICE7NoFLOAT(53)NoSell price 7.
SELLPRICE8NoFLOAT(53)NoSell price 8.
SELLPRICE9NoASNoSell price 9.
SELLPRICE10NoFLOAT(53)NoSell price 10.
LATESTCOSTNoFLOAT(53)NoThe stock item's Last Cost Price.
AVECOSTNoFLOAT(53)NoThe stock item's Average Cost Price.
MINSTOCKNoFLOAT(53)NoThe minimum default stock level for each location.
MAXSTOCKNoFLOAT(53)NoThe maximum default stock level for each location.
SUPPLIERNONoINTEGERNoThe ID number (CR_ACCS.ACCNO) of the stock item's main supplier.
MONTHUNITSNoFLOAT(53)NoSales totals in terms of quantity for the current month.
YEARUNITSNoFLOAT(53)NoYear to date sales totals in terms of quantity.
LASTYEARUNITSNoFLOAT(53)NoUnits of stock item sold in for the previous year.
MONTHVALUENoFLOAT(53)NoSales value for the current month.
YEARVALUENoFLOAT(53)NoSales value for the current year.
LASTYEARVALUENoFLOAT(53)NoSales value for last year.
BINCODENoVARCHAR(12)NoBincode for the stock item.
DISCOUNTLEVELNoINTEGERNo
DEFDAYSNoINTEGERNo
BARCODE1NoVARCHAR(30)NoBarcode/alternate code 1.
BARCODE2NoVARCHAR(30)NoBarcode/alternate code 2.
BARCODE3NoVARCHAR(30)NoBarcode/alternate code 3.
LASTMONTHVALUENoFLOAT(53)NoSales value for the previous month for the stock item.
LASTMONTHUNITSNoFLOAT(53)NoUnits of stock item sold in the previous month.
SALES_GL_CODENoINTEGERNoThe default GL code that sales transactions for this stock item are posted to.
PURCH_GL_CODENoINTEGERNoThe GL code that purchase transactions for this stock are posted to.
WEB_SHOWNoCHAR(1)No
ISACTIVENoCHAR(1)NoIf this field is set to 'Y', the stock item is active.
WEIGHTNoFLOAT(53)NoPhysical weight of the stock item.
CUBICNoFLOAT(53)NoCubic weight of the item.
ALERTNoVARCHAR(60)NoText of the pop-up alert message that appears when the stock item is sold or purchased.
NOTESNoVARCHAR(4096)NoAny notes for the stock item.
PQTYNoFLOAT(53)No
PACKNoVARCHAR(10)NoDescribes the unit of sale for pricing, e.g. EACH or PACK.
HAS_SNNoCHAR(1)NoThis field is set to 'Y' if the stock item is serial number tracked.
STDCOSTNoFLOAT(53)NoThe stock item's standard cost.
SUPPLIERNO2NoINTEGERNoAlternate supplier for the stock item.
SUPPLIERNO3NoINTEGERNoAlternate supplier for the stock item.
SALES_GLSUBCODENoINTEGERNoThe default GL sub-account code that sales transactions for this stock item are posted to.
PURCH_GLSUBCODENoINTEGERNoThe default GL sub-account code that purchase transactions for this stock item are posted to.
BRANCHNONoINTEGERNoBranch number for the stock item.
SALESTAXRATENoINTEGERNoDefault GST rate for sales transactions.
PURCHTAXRATENoINTEGERNoDefault GST rate for purchase transactions.
LAST_UPDATEDNoDATETIMENoDate and time when the stock item record was last updated.
UPDATEITEM_CODENoVARCHAR(23)NoOnly applies to lookup items. The ID number of the stocked item that maintains the actual stock.
UPDATEITEM_QTYNoFLOAT(53)NoOnly applies to lookup items. Quantity of the stocked item.
COS_GL_CODENoINTEGERNoThe Cost of Sales account updated in the General Ledger when this stock item is sold.
COS_GLSUBCODENoINTEGERNoThe Cost of Sales sub-account updated in the General Ledger when the stock item is sold.
STOCKPRICEGROUPNoINTEGERNoThe price group that the stock item belongs to.
SUPPLIERCOSTNoFLOAT(53)YesThe supplier cost price.
ECONORDERQTYNoFLOAT(53)NoThe economic order quantity (EOQ) for the stock item.
LINKED_BILLCODENoVARCHAR(23)No
STOCK_CLASSIFICATIONNoINTEGERYesThe stock classification that the item belongs to.
STOCKGROUP2NoINTEGERNoThe stock item's Secondary (Coarse) Stock Group.
TOTALSTOCKNoFLOAT(53)YesTotal units of stock for the item.
HAS_BNNoCHAR(1)NoThis field is set to 'Y' if the stock item is batch tracked.
HAS_EXPIRYNoCHAR(1)YesThis 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_DAYSNoINTEGERNoNumber of days before expiry of the stock item.
DUTYNoFLOAT(53)YesThe duty percentage on the stock item.
SERIALNO_TYPENoINTEGERYesHow 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
COSTTYPENoINTEGERYes
COSTGROUPNoINTEGERYes
LABEL_QTYNoINTEGERYes
IS_DISCOUNTABLENoCHAR(1)YesThis field is set to 'Y' if the stock item is discountable.
RESTRICTED_ITEMNoCHAR(1)YesThis field is set to 'Y' if the stock item is a restricted item.
NUMDECIMALSNoINTEGERYesThe number of decimal places that can be entered for the stock item's quantity.
COGSMETHODNoINTEGERYes
DEFAULTWARRANTYNONoINTEGERYesThe default Warranty No for the stock item.
DIMENSIONSNoINTEGERYesHow the dimensions of the stock item are specified. Will be one of:
0=None
1=Length
2=Square
3=Cubic
X_SIZEIDNoINTEGERNoStyle size ID of the stock item. Used by the Style, Size Colour functionality.
X_COLOURIDNoINTEGERNoStyle colour ID of the stock item. Used by the Style, Size Colour functionality.
AUTO_NARRATIVENoINTEGERNoThe setting of the stock item's Auto Narrative property. Will be one of:
0=No
1=Yes with Blank
2=Yes with Notes
VARIABLECOSTNoCHAR(1)YesThe field is set to 'Y for Variable Cost stock items.
PRICEQTYNoFLOAT(53)NoThe stock item's sell price per quantity.
PRICEPERKGNoFLOAT(53)NoThe stock item's sell price per kg.
LOOKUP_RECOVERABLENoCHAR(1)NoIf this field contains 'Y', the stock item is a recoverable expense in EXO Job Costing.

Relationships:
NameRelationship typeParentChildCardinality
CR_ACCS_STOCK_ITEMSNon IdentifyingCR_ACCSSTOCK_ITEMSZero Or More
FK_CRM_BUDGET_EXCLUDED_STOCK_ITEMS_STOCK_ITEMSNon IdentifyingSTOCK_ITEMSCRM_BUDGET_EXCLUDED_STOCK_ITEMSZero Or More
FK_CRM_BUDGET_LINE_STOCK_ITEMSNon IdentifyingSTOCK_ITEMSCRM_BUDGET_LINEZero Or More
GLACCS_STOCK_ITEMSNon IdentifyingGLACCSSTOCK_ITEMSZero Or More
GLACCS_STOCK_ITEMS_COSNon IdentifyingGLACCSSTOCK_ITEMSZero Or More
GL_ACCS_STOCKITEMS_PONon IdentifyingGLACCSSTOCK_ITEMSZero Or More
STOCK_CLASSIFICATIONS_STOCK_ITEMSNon IdentifyingSTOCK_CLASSIFICATIONSSTOCK_ITEMSZero Or More
STOCK_GROUP2S_STOCK_ITEMSNon IdentifyingSTOCK_GROUP2SSTOCK_ITEMSZero Or More
STOCK_GROUPS_STOCK_ITEMSNon IdentifyingSTOCK_GROUPSSTOCK_ITEMSZero Or More
STOCK_ITEMS_BILLOMAT_LINESNon IdentifyingSTOCK_ITEMSBILLOMAT_LINESZero Or More
STOCK_ITEMS_BILLOMAT_TEMPNon IdentifyingSTOCK_ITEMSBILLOMAT_TEMPZero Or More
STOCK_ITEMS_CR_INVLINESNon IdentifyingSTOCK_ITEMSCR_INVLINESZero Or More
STOCK_ITEMS_DR_INVLINESNon IdentifyingSTOCK_ITEMSDR_INVLINESZero Or More
STOCK_ITEMS_DR_PRICESNon IdentifyingSTOCK_ITEMSDR_PRICESZero Or More
STOCK_ITEMS_INWARDS_GOODS_LINESNon IdentifyingSTOCK_ITEMSINWARDS_GOODS_LINESZero Or More
STOCK_ITEMS_JOBCOST_LINESNon IdentifyingSTOCK_ITEMSJOBCOST_LINESZero Or More
STOCK_ITEMS_JOBCOST_LINES_DESCNon IdentifyingSTOCK_ITEMSJOBCOST_LINESZero Or More
STOCK_ITEMS_JOB_CONTRACT_BILLINGSNon IdentifyingSTOCK_ITEMSJOB_CONTRACT_BILLINGSZero Or More
STOCK_ITEMS_JOB_OUTPUT_ITEMSNon IdentifyingSTOCK_ITEMSJOB_OUTPUT_ITEMSZero Or More
STOCK_ITEMS_JOB_OUTPUT_ITEMS_DESCNon IdentifyingSTOCK_ITEMSJOB_OUTPUT_ITEMSZero Or More
STOCK_ITEMS_JOB_TIMESHEETSNon IdentifyingSTOCK_ITEMSJOB_TIMESHEETSZero Or More
STOCK_ITEMS_JOB_TRANSACTIONSNon IdentifyingSTOCK_ITEMSJOB_TRANSACTIONSZero Or More
STOCK_ITEMS_JOB_TRANSACTIONS_DESCNon IdentifyingSTOCK_ITEMSJOB_TRANSACTIONSZero Or More
STOCK_ITEMS_OPPORTUNITY_QUOTENon IdentifyingSTOCK_ITEMSOPPORTUNITY_QUOTEZero Or More
STOCK_ITEMS_STOCKREQUIREMENTNon IdentifyingSTOCK_ITEMSSTOCKREQUIREMENTZero Or More
STOCK_ITEMS_STOCKTAKE_TOTALSNon IdentifyingSTOCK_ITEMSSTOCKTAKE_TOTALSZero Or More
STOCK_ITEMS_STOCKTAKE_TOTALS_BNNon IdentifyingSTOCK_ITEMSSTOCKTAKE_TOTALSZero Or More
STOCK_ITEMS_STOCKTAKE_TOTALS_EXPDAYSNon IdentifyingSTOCK_ITEMSSTOCKTAKE_TOTALSZero Or More
STOCK_ITEMS_STOCKTAKE_TOTALS_EXPIRYNon IdentifyingSTOCK_ITEMSSTOCKTAKE_TOTALSZero Or More
STOCK_ITEMS_STOCKTAKE_TOTALS_SERIALNon IdentifyingSTOCK_ITEMSSTOCKTAKE_TOTALSZero Or More
STOCK_ITEMS_STOCK_LOC_INFOIdentifyingSTOCK_ITEMSSTOCK_LOC_INFOZero Or More
STOCK_ITEMS_STOCK_REQUESTLINESNon IdentifyingSTOCK_ITEMSSTOCK_REQUESTLINESZero Or More
STOCK_ITEMS_STOCK_REQUESTLINES_DESCNon IdentifyingSTOCK_ITEMSSTOCK_REQUESTLINESZero Or More
STOCK_ITEMS_STOCK_SERIALNOSNon IdentifyingSTOCK_ITEMSSTOCK_SERIALNOSZero Or More
STOCK_ITEMS_STOCK_TRANSNon IdentifyingSTOCK_ITEMSSTOCK_TRANSZero Or More
STOCK_ITEMS_STOCK_WEBNon IdentifyingSTOCK_ITEMSSTOCK_WEBZero Or More
STOCK_ITEMS_SUPPLIER_STOCK_ITEMSNon IdentifyingSTOCK_ITEMSSUPPLIER_STOCK_ITEMSZero Or More
STOCK_PRICEGROUPS_STOCK_ITEMSNon IdentifyingSTOCK_PRICEGROUPSSTOCK_ITEMSZero Or More

Indexes:
NameIndex columnsIndex type
STOCK_ITEMS_DESCDESCRIPTION ASC

Constraints:
NameTypeLevelConstraint
DefaultColumn ConstraintLOOKUP_RECOVERABLE DEFAULT Y
Not NullColumn ConstraintNOT NULL (STOCKCODE)
Not NullColumn ConstraintNOT NULL (VARIABLECOST)
Not NullColumn ConstraintNOT NULL (DIMENSIONS)
Not NullColumn ConstraintNOT NULL (DEFAULTWARRANTYNO)
Not NullColumn ConstraintNOT NULL (COGSMETHOD)
Not NullColumn ConstraintNOT NULL (NUMDECIMALS)
Not NullColumn ConstraintNOT NULL (RESTRICTED_ITEM)
Not NullColumn ConstraintNOT NULL (IS_DISCOUNTABLE)
Not NullColumn ConstraintNOT NULL (LABEL_QTY)
Not NullColumn ConstraintNOT NULL (COSTGROUP)
Not NullColumn ConstraintNOT NULL (COSTTYPE)
Not NullColumn ConstraintNOT NULL (SERIALNO_TYPE)
Not NullColumn ConstraintNOT NULL (DUTY)
Not NullColumn ConstraintNOT NULL (HAS_EXPIRY)
Not NullColumn ConstraintNOT NULL (TOTALSTOCK)
Not NullColumn ConstraintNOT NULL (STOCK_CLASSIFICATION)
Not NullColumn ConstraintNOT NULL (SUPPLIERCOST)
CR_ACCS_STOCK_ITEMSForeign KeyTable ConstraintFOREIGN KEY (SUPPLIERNO) REFERENCES CR_ACCS(ACCNO)
DF__STOCK_ITEM__DUTY__675F4696DefaultColumn ConstraintDUTY DEFAULT 0
DF__STOCK_ITEM__PQTY__7C255952DefaultColumn ConstraintPQTY DEFAULT 1
DF__STOCK_ITE__AUTO___43ECB87EDefaultColumn ConstraintAUTO_NARRATIVE DEFAULT 0
DF__STOCK_ITE__AVECO__18A19C6FDefaultColumn ConstraintAVECOST DEFAULT 0
DF__STOCK_ITE__BRANC__5FD33367DefaultColumn ConstraintBRANCHNO DEFAULT 0
DF__STOCK_ITE__COGSM__0BD27C8DDefaultColumn ConstraintCOGSMETHOD DEFAULT 0
DF__STOCK_ITE__COSTG__50BBD860DefaultColumn ConstraintCOSTGROUP DEFAULT 0
DF__STOCK_ITE__COSTT__4FC7B427DefaultColumn ConstraintCOSTTYPE DEFAULT 0
DF__STOCK_ITE__COS_G__4D7F7902DefaultColumn ConstraintCOS_GL_CODE DEFAULT 0
DF__STOCK_ITE__COS_G__4E739D3BDefaultColumn ConstraintCOS_GLSUBCODE DEFAULT 0
DF__STOCK_ITE__CUBIC__45C948A1DefaultColumn ConstraintCUBIC DEFAULT 0
DF__STOCK_ITE__DEFAU__6F0133B5DefaultColumn ConstraintDEFAULTWARRANTYNO DEFAULT -1
DF__STOCK_ITE__DEFDA__2D9CB955DefaultColumn ConstraintDEFDAYS DEFAULT 0
DF__STOCK_ITE__DIMEN__3CFFC3CDDefaultColumn ConstraintDIMENSIONS DEFAULT 0
DF__STOCK_ITE__DISCO__2CA8951CDefaultColumn ConstraintDISCOUNTLEVEL DEFAULT 0
DF__STOCK_ITE__EXPIR__666B225DDefaultColumn ConstraintEXPIRY_DAYS DEFAULT 1
DF__STOCK_ITE__HAS_B__526429B0DefaultColumn ConstraintHAS_BN DEFAULT N
DF__STOCK_ITE__HAS_E__6576FE24DefaultColumn ConstraintHAS_EXPIRY DEFAULT N
DF__STOCK_ITE__HAS_S__7FF5EA36DefaultColumn ConstraintHAS_SN DEFAULT N
DF__STOCK_ITE__ISACT__74B941B4DefaultColumn ConstraintISACTIVE DEFAULT Y
DF__STOCK_ITE__IS_DI__3044E72DDefaultColumn ConstraintIS_DISCOUNTABLE DEFAULT Y
DF__STOCK_ITE__LABEL__6C04CB79DefaultColumn ConstraintLABEL_QTY DEFAULT 1
DF__STOCK_ITE__LASTM__2E90DD8EDefaultColumn ConstraintLASTMONTHVALUE DEFAULT 0
DF__STOCK_ITE__LASTM__2F8501C7DefaultColumn ConstraintLASTMONTHUNITS DEFAULT 0
DF__STOCK_ITE__LASTY__28D80438DefaultColumn ConstraintLASTYEARUNITS DEFAULT 0
DF__STOCK_ITE__LASTY__2BB470E3DefaultColumn ConstraintLASTYEARVALUE DEFAULT 0
DF__STOCK_ITE__LATES__17AD7836DefaultColumn ConstraintLATESTCOST DEFAULT 0
DF__STOCK_ITE__MAXST__25077354DefaultColumn ConstraintMAXSTOCK DEFAULT 0
DF__STOCK_ITE__MINST__24134F1BDefaultColumn ConstraintMINSTOCK DEFAULT 0
DF__STOCK_ITE__MONTH__26EFBBC6DefaultColumn ConstraintMONTHUNITS DEFAULT 0
DF__STOCK_ITE__MONTH__29CC2871DefaultColumn ConstraintMONTHVALUE DEFAULT 0
DF__STOCK_ITE__NUMDE__0801EBA9DefaultColumn ConstraintNUMDECIMALS DEFAULT -1
DF__STOCK_ITE__PURCH__451F3D2BDefaultColumn ConstraintPURCH_GLSUBCODE DEFAULT 0
DF__STOCK_ITE__PURCH__4727812EDefaultColumn ConstraintPURCH_GL_CODE DEFAULT 0
DF__STOCK_ITE__PURCH__68687968DefaultColumn ConstraintPURCHTAXRATE DEFAULT -1
DF__STOCK_ITE__RESTR__77CB83E0DefaultColumn ConstraintRESTRICTED_ITEM DEFAULT N
DF__STOCK_ITE__SALES__442B18F2DefaultColumn ConstraintSALES_GLSUBCODE DEFAULT 0
DF__STOCK_ITE__SALES__46335CF5DefaultColumn ConstraintSALES_GL_CODE DEFAULT 0
DF__STOCK_ITE__SALES__6774552FDefaultColumn ConstraintSALESTAXRATE DEFAULT -1
DF__STOCK_ITE__SELLP__13DCE752DefaultColumn ConstraintSELLPRICE1 DEFAULT 0
DF__STOCK_ITE__SELLP__14D10B8BDefaultColumn ConstraintSELLPRICE2 DEFAULT 0
DF__STOCK_ITE__SELLP__15C52FC4DefaultColumn ConstraintSELLPRICE3 DEFAULT 0
DF__STOCK_ITE__SELLP__16B953FDDefaultColumn ConstraintSELLPRICE4 DEFAULT 0
DF__STOCK_ITE__SELLP__39987BE6DefaultColumn ConstraintSELLPRICE5 DEFAULT 0
DF__STOCK_ITE__SELLP__3A8CA01FDefaultColumn ConstraintSELLPRICE6 DEFAULT 0
DF__STOCK_ITE__SELLP__3B80C458DefaultColumn ConstraintSELLPRICE7 DEFAULT 0
DF__STOCK_ITE__SELLP__3C74E891DefaultColumn ConstraintSELLPRICE8 DEFAULT 0
DF__STOCK_ITE__SELLP__3E5D3103DefaultColumn ConstraintSELLPRICE10 DEFAULT 0
DF__STOCK_ITE__SERIA__1C481021DefaultColumn ConstraintSERIALNO_TYPE DEFAULT 0
DF__STOCK_ITE__STATU__12E8C319DefaultColumn ConstraintSTATUS DEFAULT L
DF__STOCK_ITE__STDCO__392E6792DefaultColumn ConstraintSTDCOST DEFAULT 0
DF__STOCK_ITE__STOCK__11F49EE0DefaultColumn ConstraintSTOCKGROUP DEFAULT 0
DF__STOCK_ITE__STOCK__4F87BD05DefaultColumn ConstraintSTOCKGROUP2 DEFAULT 0
DF__STOCK_ITE__STOCK__6442E2C9DefaultColumn ConstraintSTOCK_CLASSIFICATION DEFAULT 0
DF__STOCK_ITE__STOCK__69279377DefaultColumn ConstraintSTOCKPRICEGROUP DEFAULT 0
DF__STOCK_ITE__SUPPL__16B953FDDefaultColumn ConstraintSUPPLIERCOST DEFAULT 0
DF__STOCK_ITE__SUPPL__25FB978DDefaultColumn ConstraintSUPPLIERNO DEFAULT 0
DF__STOCK_ITE__TOTAL__51700577DefaultColumn ConstraintTOTALSTOCK DEFAULT 0
DF__STOCK_ITE__UPDAT__257187A8DefaultColumn ConstraintUPDATEITEM_QTY DEFAULT 0
DF__STOCK_ITE__VARIA__611DF409DefaultColumn ConstraintVARIABLECOST DEFAULT N
DF__STOCK_ITE__WEB_S__44160A59DefaultColumn ConstraintWEB_SHOW DEFAULT N
DF__STOCK_ITE__WEIGH__44D52468DefaultColumn ConstraintWEIGHT DEFAULT 0
DF__STOCK_ITE__X_COL__02890975DefaultColumn ConstraintX_COLOURID DEFAULT 0
DF__STOCK_ITE__X_SIZ__0194E53CDefaultColumn ConstraintX_SIZEID DEFAULT 0
DF__STOCK_ITE__YEARU__27E3DFFFDefaultColumn ConstraintYEARUNITS DEFAULT 0
DF__STOCK_ITE__YEARV__2AC04CAADefaultColumn ConstraintYEARVALUE DEFAULT 0
GLACCS_STOCK_ITEMSForeign KeyTable ConstraintFOREIGN KEY (SALES_GL_CODE) REFERENCES GLACCS(ACCNO)
GLACCS_STOCK_ITEMS_COSForeign KeyTable ConstraintFOREIGN KEY (COS_GL_CODE) REFERENCES GLACCS(ACCNO)
GL_ACCS_STOCKITEMS_POForeign KeyTable ConstraintFOREIGN KEY (PURCH_GL_CODE) REFERENCES GLACCS(ACCNO)
PK__STOCK_ITEMS__11007AA7Primary KeyTable ConstraintPRIMARY KEY (STOCKCODE)
STOCK_CLASSIFICATIONS_STOCK_ITEMSForeign KeyTable ConstraintFOREIGN KEY (STOCK_CLASSIFICATION) REFERENCES STOCK_CLASSIFICATIONS(CLASSNO)
STOCK_GROUP2S_STOCK_ITEMSForeign KeyTable ConstraintFOREIGN KEY (STOCKGROUP2) REFERENCES STOCK_GROUP2S(GROUPNO)
STOCK_GROUPS_STOCK_ITEMSForeign KeyTable ConstraintFOREIGN KEY (STOCKGROUP) REFERENCES STOCK_GROUPS(GROUPNO)
STOCK_PRICEGROUPS_STOCK_ITEMSForeign KeyTable ConstraintFOREIGN KEY (STOCKPRICEGROUP) REFERENCES STOCK_PRICEGROUPS(GROUPNO)

Triggers:
Trigger nameCode
AFTER_STOCK_ITEMS_DELETECREATE 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_INFOCREATE 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_ONEOFFCREATE 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
CommentThis table contains details of all stock locations.
Primary key columnsLOCNO

Attributes:
Column namePrimary keyData typeNot NULLComment
LOCNOYesINTEGERYesA unique ID number for the location.
LCODENoVARCHAR(8)NoA short alphanumeric ID code for the location.
LNAMENoVARCHAR(30)NoA descriptive name for the location.
ISACTIVENoCHAR(1)NoThis field contains 'Y' if the location is active.
EXCLUDE_FROMVALUATIONNoCHAR(1)NoThis field contains 'Y' if the location's "Exclude from Valuation" option is ticked.
EXCLUDE_FROMFREE_STOCKNoCHAR(1)NoThis field contains 'Y' if the location's "Exclude from Free Stock" option is ticked.
EXCLUDE_FROMSALESNoCHAR(1)NoThis field contains 'Y' if the location's "Exclude from Sales" option is ticked.
DELADDR1NoVARCHAR(30)NoLine 1 of the location's address.
DELADDR2NoVARCHAR(30)NoLine 2 of the location's address.
DELADDR3NoVARCHAR(30)NoLine 3 of the location's address.
DELADDR4NoVARCHAR(30)NoLine 4 of the location's address.
DELADDR5NoVARCHAR(30)NoLine 5 of the location's address.
DELADDR6NoVARCHAR(30)NoLine 6 of the location's address.
IS_WIPLOCATIONNoCHAR(1)YesThis field contains 'Y' if the location represents Work in Progress in EXO Job Costing.

Relationships:
NameRelationship typeParentChildCardinality
STOCK_LOCATIONS_BILLOMAT_TEMPNon IdentifyingSTOCK_LOCATIONSBILLOMAT_TEMPZero Or More
STOCK_LOCATIONS_BILLOMAT_TEMP_PRODNon IdentifyingSTOCK_LOCATIONSBILLOMAT_TEMPZero Or More
STOCK_LOCATIONS_INWARDS_GOODSNon IdentifyingSTOCK_LOCATIONSINWARDS_GOODSZero Or More
STOCK_LOCATIONS_INWARDS_GOODS_LINESNon IdentifyingSTOCK_LOCATIONSINWARDS_GOODS_LINESZero Or More
STOCK_LOCATIONS_JOB_OUTPUT_ITEMSNon IdentifyingSTOCK_LOCATIONSJOB_OUTPUT_ITEMSZero Or More
STOCK_LOCATIONS_STOCKREQUIREMENTNon IdentifyingSTOCK_LOCATIONSSTOCKREQUIREMENTZero Or More
STOCK_LOCATIONS_STOCKTAKE_CTRLIdentifyingSTOCK_LOCATIONSSTOCKTAKE_CTRLZero Or More
STOCK_LOCATIONS_STOCKTAKE_TOTALSNon IdentifyingSTOCK_LOCATIONSSTOCKTAKE_TOTALSZero Or More
STOCK_LOCATIONS_STOCK_LOC_INFONon IdentifyingSTOCK_LOCATIONSSTOCK_LOC_INFOZero Or More
STOCK_LOCATIONS_STOCK_SERIALNOSNon IdentifyingSTOCK_LOCATIONSSTOCK_SERIALNOSZero Or More
STOCK_LOCATIONS_STOCK_TRANSNon IdentifyingSTOCK_LOCATIONSSTOCK_TRANSZero Or More
STOCK_LOCATIONS_STOCK_TRANS_TONon IdentifyingSTOCK_LOCATIONSSTOCK_TRANSZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (IS_WIPLOCATION)
Not NullColumn ConstraintNOT NULL (LOCNO)
DF__STOCK_LOC__EXCLU__671F4F74DefaultColumn ConstraintEXCLUDE_FROMVALUATION DEFAULT N
DF__STOCK_LOC__EXCLU__681373ADDefaultColumn ConstraintEXCLUDE_FROMFREE_STOCK DEFAULT N
DF__STOCK_LOC__EXCLU__690797E6DefaultColumn ConstraintEXCLUDE_FROMSALES DEFAULT N
DF__STOCK_LOC__ISACT__05AEC38CDefaultColumn ConstraintISACTIVE DEFAULT Y
DF__STOCK_LOC__IS_WI__6909F9EBDefaultColumn ConstraintIS_WIPLOCATION DEFAULT N
PK__STOCK_LOC__LOCNO__04BA9F53Primary KeyTable ConstraintPRIMARY KEY (LOCNO)

Triggers:
Trigger nameCode
AFTER_STOCK_LOCATIONS_DELETECREATE 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_STOCKTAKECREATE 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
CommentThis table records stock levels at various locations.
Primary key columnsSTOCKCODE

Attributes:
Column namePrimary keyData typeNot NULLComment
STOCKCODEYesVARCHAR(23)YesThe unique ID code of the stock item.
LOCATIONNoINTEGERYesThe ID number of the location.
BINCODENoVARCHAR(12)NoThe stock item's bin or shelf code at the location.
MINSTOCKNoFLOAT(53)NoThe minimum stock quantity for the stock code at the location.
MAXSTOCKNoFLOAT(53)NoThe maximum stock quantity for the stock code at the location.
QTYNoFLOAT(53)YesThe current stock quantity for the stock code at the location.

Relationships:
NameRelationship typeParentChildCardinality
STOCK_ITEMS_STOCK_LOC_INFOIdentifyingSTOCK_ITEMSSTOCK_LOC_INFOZero Or More
STOCK_LOCATIONS_STOCK_LOC_INFONon IdentifyingSTOCK_LOCATIONSSTOCK_LOC_INFOZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (QTY)
Not NullColumn ConstraintNOT NULL (STOCKCODE)
Not NullColumn ConstraintNOT NULL (LOCATION)
DF__STOCK_LOC_I__QTY__507BE13EDefaultColumn ConstraintQTY DEFAULT 0
DF__STOCK_LOC__MAXST__162F4418DefaultColumn ConstraintMAXSTOCK DEFAULT 0
DF__STOCK_LOC__MINST__153B1FDFDefaultColumn ConstraintMINSTOCK DEFAULT 0
PK__STOCK_LOC_INFO__1446FBA6Primary KeyTable ConstraintPRIMARY KEY (STOCKCODE)
STOCK_ITEMS_STOCK_LOC_INFOForeign KeyTable ConstraintFOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE)
STOCK_LOCATIONS_STOCK_LOC_INFOForeign KeyTable ConstraintFOREIGN KEY (LOCATION) REFERENCES STOCK_LOCATIONS(LOCNO)



Entity: STOCK_PRICEGROUPS
CommentThis table stores the Price Groups that are assigned to stock items.
Primary key columnsGROUPNO

Attributes:
Column namePrimary keyData typeNot NULLComment
GROUPNOYesINTEGERYesA unique ID number for the Price Group.
GROUPNAMENoVARCHAR(30)NoThe Price Group's name.

Relationships:
NameRelationship typeParentChildCardinality
STOCK_PRICEGROUPS_STOCK_GROUPSNon IdentifyingSTOCK_PRICEGROUPSSTOCK_GROUPSZero Or More
STOCK_PRICEGROUPS_STOCK_ITEMSNon IdentifyingSTOCK_PRICEGROUPSSTOCK_ITEMSZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (GROUPNO)
PK__STOCK_PRICEGROUP__13F1F5EBPrimary KeyTable ConstraintPRIMARY KEY (GROUPNO)



Entity: STOCK_REQUESTLINES
CommentThis table is used by the Stock Transfer Requests feature of the Distribution Advantage module.
Primary key columnsSEQNO

Attributes:
Column namePrimary keyData typeNot NULLComment
SEQNOYesINTEGERYesA unique ID number for the stock transfer request line.
HDR_SEQNONoINTEGERYesThe ID number of the stock transfer request that the line belongs to (from STOCK_REQUESTS).
STOCKCODENoVARCHAR(23)YesThe ID code of the stock item on the line.
DESCRIPTIONNoVARCHAR(40)YesThe description of the stock item on the line.
PACK_SIZENoFLOAT(53)NoThe pack size of the stock item on the line.
REQ_QUANTNoFLOAT(53)NoThe Requested Qty on the line.
SUP_QUANTNoFLOAT(53)NoThe Received Qty on the line.
COMMENTNoVARCHAR(30)NoThe comment entered on the line, if any.
BATCHCODENoVARCHAR(20)NoThe batch code on the line.
LINETYPENoINTEGERNoThis 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_STOCKCODENoVARCHAR(23)NoThe physical stock unit.
LINKED_QTYNoFLOAT(53)NoThe physical stock unit quantity.
BOMTYPENoCHAR(1)NoThe type of Bill of Materials that the line relates to:
N = Normal stock item
B = Build
K = Kit
L = Lookup
SHOWLINENoCHAR(1)NoThis field applies to Bills of Materials, and is used by Clarity to determine if the line can be printed.
LINKEDSTATUSNoCHAR(1)NoWill be one of:
S = Stocked
L = Lookup
N = Not applicable
BOMPRICINGNoCHAR(1)NoHow Bills of Materials are priced. Will be one of:
C = Pricing by Component
T = Pricing by total
NARRATIVE_SEQNONoINTEGERNoThe ID number for the narrative on this line.
LOST_QUANTNoFLOAT(53)No
SENT_QUANTNoFLOAT(53)NoThe Sent Qty on the line.
SEND_NOWNoFLOAT(53)NoThe Send Now quantity on the line.
SUP_NOWNoFLOAT(53)NoThe Receive Now quantity on the line.
INTRANS_QUANTNoASNoThe In Transit Qty on the line.
SOLINEIDNoINTEGERYes

Relationships:
NameRelationship typeParentChildCardinality
NARRATIVES_STOCK_REQUESTLINESNon IdentifyingNARRATIVESSTOCK_REQUESTLINESZero Or More
STOCK_ITEMS_STOCK_REQUESTLINESNon IdentifyingSTOCK_ITEMSSTOCK_REQUESTLINESZero Or More
STOCK_ITEMS_STOCK_REQUESTLINES_DESCNon IdentifyingSTOCK_ITEMSSTOCK_REQUESTLINESZero Or More
STOCK_REQUESTS_STOCK_REQUESTLINESNon IdentifyingSTOCK_REQUESTSSTOCK_REQUESTLINESZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (SOLINEID)
Not NullColumn ConstraintNOT NULL (SEQNO)
Not NullColumn ConstraintNOT NULL (HDR_SEQNO)
Not NullColumn ConstraintNOT NULL (DESCRIPTION)
Not NullColumn ConstraintNOT NULL (STOCKCODE)
DF__STOCK_REQ__BOMPR__61BC4730DefaultColumn ConstraintBOMPRICING DEFAULT N
DF__STOCK_REQ__BOMTY__5EDFDA85DefaultColumn ConstraintBOMTYPE DEFAULT N
DF__STOCK_REQ__HDR_S__7953D99FDefaultColumn ConstraintHDR_SEQNO DEFAULT 0
DF__STOCK_REQ__LINET__5DEBB64CDefaultColumn ConstraintLINETYPE DEFAULT -1
DF__STOCK_REQ__LINKE__60C822F7DefaultColumn ConstraintLINKEDSTATUS DEFAULT N
DF__STOCK_REQ__LOST___6ECC298BDefaultColumn ConstraintLOST_QUANT DEFAULT 0
DF__STOCK_REQ__PACK___7A47FDD8DefaultColumn ConstraintPACK_SIZE DEFAULT 0
DF__STOCK_REQ__REQ_Q__7B3C2211DefaultColumn ConstraintREQ_QUANT DEFAULT 0
DF__STOCK_REQ__SEND___70B471FDDefaultColumn ConstraintSEND_NOW DEFAULT 0
DF__STOCK_REQ__SENT___6FC04DC4DefaultColumn ConstraintSENT_QUANT DEFAULT 0
DF__STOCK_REQ__SHOWL__5FD3FEBEDefaultColumn ConstraintSHOWLINE DEFAULT Y
DF__STOCK_REQ__SOLIN__7B3CED68DefaultColumn ConstraintSOLINEID DEFAULT -1
DF__STOCK_REQ__SUP_N__71A89636DefaultColumn ConstraintSUP_NOW DEFAULT 0
DF__STOCK_REQ__SUP_Q__7D246A83DefaultColumn ConstraintSUP_QUANT DEFAULT 0
NARRATIVES_STOCK_REQUESTLINESForeign KeyTable ConstraintFOREIGN KEY (NARRATIVE_SEQNO) REFERENCES NARRATIVES(SEQNO)
PK__STOCK_REQUESTLIN__785FB566Primary KeyTable ConstraintPRIMARY KEY (SEQNO)
STOCK_ITEMS_STOCK_REQUESTLINESForeign KeyTable ConstraintFOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE)
STOCK_ITEMS_STOCK_REQUESTLINES_DESCForeign KeyTable ConstraintFOREIGN KEY (DESCRIPTION) REFERENCES STOCK_ITEMS(DESCRIPTION)
STOCK_REQUESTS_STOCK_REQUESTLINESForeign KeyTable ConstraintFOREIGN KEY (HDR_SEQNO) REFERENCES STOCK_REQUESTS(SEQNO)



Entity: STOCK_REQUESTS
CommentThis table is used by the Stock Transfer Requests feature of the Distribution Advantage module.
Primary key columnsSEQNO

Attributes:
Column namePrimary keyData typeNot NULLComment
SEQNOYesINTEGERYesA unique ID number for the transfer request
FROM_LOCNoINTEGERNoThe ID number of the location that stock is to be transferred from.
TO_LOCNoINTEGERNoThe ID number of the location that stock is to be transferred to.
REQUEST_DATENoDATETIMENoThe date and time when the request was created.
REQUIRE_DATENoDATETIMENoThe date and time that the request must be completed by.
STAFFNONoINTEGERNoThe ID number of the staff member assigned to the request.
STATUSNoINTEGERNoThe status of the stock transfer. Will be one of:
0 = New Request
1 = Picking
2 = In Transit
3 = Complete
TRANSTYPENoINTEGERNoThe ID number of the Stock Request Type (from STOCK_REQUESTTYPES).
CUSTORDERNONoVARCHAR(20)NoThe customer order number, if there is one.
NARRATIVE_SEQNONoINTEGERNoThe ID number for the narrative on this request.
ORIG_SEQNONoINTEGERNo

Relationships:
NameRelationship typeParentChildCardinality
NARRATIVES_STOCK_REQUESTSNon IdentifyingNARRATIVESSTOCK_REQUESTSZero Or More
STAFF_STOCK_REQUESTSNon IdentifyingSTAFFSTOCK_REQUESTSZero Or More
STOCK_REQUESTS_STOCK_REQUESTLINESNon IdentifyingSTOCK_REQUESTSSTOCK_REQUESTLINESZero Or More
STOCK_REQUESTTYPES_STOCK_REQUESTSNon IdentifyingSTOCK_REQUESTTYPESSTOCK_REQUESTSZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (SEQNO)
DF__STOCK_REQ__FROM___70BE939EDefaultColumn ConstraintFROM_LOC DEFAULT 0
DF__STOCK_REQ__ORIG___5C23696BDefaultColumn ConstraintORIG_SEQNO DEFAULT -1
DF__STOCK_REQ__REQUE__72A6DC10DefaultColumn ConstraintREQUEST_DATE DEFAULT getdate()
DF__STOCK_REQ__REQUI__739B0049DefaultColumn ConstraintREQUIRE_DATE DEFAULT getdate()
DF__STOCK_REQ__STAFF__748F2482DefaultColumn ConstraintSTAFFNO DEFAULT 0
DF__STOCK_REQ__STATU__758348BBDefaultColumn ConstraintSTATUS DEFAULT 0
DF__STOCK_REQ__TO_LO__71B2B7D7DefaultColumn ConstraintTO_LOC DEFAULT 0
DF__STOCK_REQ__TRANS__76776CF4DefaultColumn ConstraintTRANSTYPE DEFAULT 0
NARRATIVES_STOCK_REQUESTSForeign KeyTable ConstraintFOREIGN KEY (NARRATIVE_SEQNO) REFERENCES NARRATIVES(SEQNO)
PK__STOCK_REQUESTS__6FCA6F65Primary KeyTable ConstraintPRIMARY KEY (SEQNO)
STAFF_STOCK_REQUESTSForeign KeyTable ConstraintFOREIGN KEY (STAFFNO) REFERENCES STAFF(STAFFNO)
STOCK_REQUESTTYPES_STOCK_REQUESTSForeign KeyTable ConstraintFOREIGN KEY (TRANSTYPE) REFERENCES STOCK_REQUESTTYPES(SEQNO)



Entity: STOCK_REQUESTTYPES
CommentThis 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 columnsSEQNO

Attributes:
Column namePrimary keyData typeNot NULLComment
SEQNOYesINTEGERYesA unique ID number for the request type.
DISPLAY_NAMENoVARCHAR(40)YesThe name of the request type.

Relationships:
NameRelationship typeParentChildCardinality
STOCK_REQUESTTYPES_STOCK_REQUESTSNon IdentifyingSTOCK_REQUESTTYPESSTOCK_REQUESTSZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (DISPLAY_NAME)
Not NullColumn ConstraintNOT NULL (SEQNO)
PK__STOCK_REQUESTTYP__7F0CB2F5Primary KeyTable ConstraintPRIMARY KEY (SEQNO)



Entity: STOCK_SERIALNOS
CommentThis table contains details of serialised stock items.
Primary key columnsSEQNO

Attributes:
Column namePrimary keyData typeNot NULLComment
SEQNOYesINTEGERYesA unique ID number for the record.
STOCKCODENoVARCHAR(23)YesThe stock code of the serialised stock item.
SERIALNONoVARCHAR(50)NoThe serial number assigned to the stock item.
REFERENCENoVARCHAR(30)NoA reference code for the record.
EXPIRY_DATENoDATETIMEYesThe expiry date on the record.
LOCNONoINTEGERYesThe location of the serialised stock item.
ACTUAL_COSTNoFLOAT(53)YesThe actual cost of the item when moved into stock.
PURCHORDNONoINTEGERYesThe ID number of the Purchase Order that the item has been receipted in against.
SALESORDNONoINTEGERYesThe ID number of the Sales Order ID that the item has been committed against.
CRINVSEQNONoINTEGERYesThe ID number of the Creditors Invoice that the item has been receipted in against.
INVSEQNONoINTEGERYesThe ID number of the Debtors Invoice ID the item has been sold against.
JOBNONoINTEGERYesThe ID number of the job that the serial number belongs to (if there is one).
STOCKINSEQNONoINTEGERYesThe ID number of the stock transaction that the item was receipted in against.
STOCKOUTSEQNONoINTEGERYesThe ID number of the stock transaction against which the item was moved out of inventory.
CR_ACCNONoINTEGERYesThe ID number of the related Creditor account.
DR_ACCNONoINTEGERYesThe ID number of the related Debtor account.
IGRLINESEQNONoINTEGERYesThe ID number of the Inwards Goods line that the item is linked to.
JOBLINESEQNONoINTEGERYesThe ID number of the job line that the item is linked to.
SALESORDLINESEQNONoINTEGERYesThe ID number of the Sales Order line that the item is linked to.
PURCHORDLINESEQNONoINTEGERYesThe ID number of the Purchase Order line that the item is linked to.
CRINVLINESEQNONoINTEGERYesThe ID number of the Creditors Invoice line that the item is linked to.
DRINVLINESEQNONoINTEGERYesThe ID number of the Debtors Invoice line that the item is linked to.
INSTOCKNoCHAR(1)YesThis field contains 'Y' if the serial number is in stock.
UNITNONoINTEGERYesThe ID number of the serviceable unit that the item is linked to.
LASTUPDATEDNoDATETIMEYesThe date and time when the record was last updated.
COMPNONoINTEGERYes
KITCODENoVARCHAR(50)No
KITID_SERIALNoVARCHAR(50)No
IGRSEQNONoINTEGERNo
ISASSIGNEDNoCHAR(1)YesN = 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_INVLINESEQNONoINTEGERYesThe invoice line ID linked to the Sales Order for the serial number.
SO_INVSEQNONoINTEGERYesThe invoice ID linked to the Sales Order for the serial number.
PO_INVLINESEQNONoINTEGERYesThe invoice line ID linked to the Purchase Order for the serial number.
PO_INVSEQNONoINTEGERYesThe invoice ID linked to the Purchase Order for the serial number.
ASSIGNED_SEQNONoINTEGERYesThe 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_LINESEQNONoINTEGERYesThe 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.
REQUESTSEQNONoINTEGERNoStock request ID
REQUESTLINESEQNONoINTEGERNoStock request Line ID
RMASEQNONoINTEGERNo
RMALINESEQNONoINTEGERNo
STKMOVSEQNONoINTEGERNoStock transaction header ID
STKMOVLINESEQNONoINTEGERNoStock transaction ID
WORKSORDSEQNONoINTEGERNoWorks order ID
WORKSORDLINESEQNONoINTEGERNoWorks Order Line ID
SU_SEQNONoINTEGERNoServiceable Unit ID

Relationships:
NameRelationship typeParentChildCardinality
STOCK_ITEMS_STOCK_SERIALNOSNon IdentifyingSTOCK_ITEMSSTOCK_SERIALNOSZero Or More
STOCK_LOCATIONS_STOCK_SERIALNOSNon IdentifyingSTOCK_LOCATIONSSTOCK_SERIALNOSZero Or More

Indexes:
NameIndex columnsIndex type
STOCKSERIALINDEXSTOCKCODE ASC, SERIALNO ASC

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (ASSIGNED_LINESEQNO)
Not NullColumn ConstraintNOT NULL (SEQNO)
Not NullColumn ConstraintNOT NULL (STOCKCODE)
Not NullColumn ConstraintNOT NULL (EXPIRY_DATE)
Not NullColumn ConstraintNOT NULL (ASSIGNED_SEQNO)
Not NullColumn ConstraintNOT NULL (LOCNO)
Not NullColumn ConstraintNOT NULL (PO_INVSEQNO)
Not NullColumn ConstraintNOT NULL (ACTUAL_COST)
Not NullColumn ConstraintNOT NULL (PO_INVLINESEQNO)
Not NullColumn ConstraintNOT NULL (PURCHORDNO)
Not NullColumn ConstraintNOT NULL (SO_INVSEQNO)
Not NullColumn ConstraintNOT NULL (SALESORDNO)
Not NullColumn ConstraintNOT NULL (SO_INVLINESEQNO)
Not NullColumn ConstraintNOT NULL (CRINVSEQNO)
Not NullColumn ConstraintNOT NULL (ISASSIGNED)
Not NullColumn ConstraintNOT NULL (INVSEQNO)
Not NullColumn ConstraintNOT NULL (COMPNO)
Not NullColumn ConstraintNOT NULL (JOBNO)
Not NullColumn ConstraintNOT NULL (LASTUPDATED)
Not NullColumn ConstraintNOT NULL (STOCKINSEQNO)
Not NullColumn ConstraintNOT NULL (UNITNO)
Not NullColumn ConstraintNOT NULL (STOCKOUTSEQNO)
Not NullColumn ConstraintNOT NULL (INSTOCK)
Not NullColumn ConstraintNOT NULL (CR_ACCNO)
Not NullColumn ConstraintNOT NULL (DRINVLINESEQNO)
Not NullColumn ConstraintNOT NULL (DR_ACCNO)
Not NullColumn ConstraintNOT NULL (CRINVLINESEQNO)
Not NullColumn ConstraintNOT NULL (IGRLINESEQNO)
Not NullColumn ConstraintNOT NULL (PURCHORDLINESEQNO)
Not NullColumn ConstraintNOT NULL (JOBLINESEQNO)
Not NullColumn ConstraintNOT NULL (SALESORDLINESEQNO)
DF__STOCK_SER__ACTUA__6B3AC472DefaultColumn ConstraintACTUAL_COST DEFAULT 0
DF__STOCK_SER__ASSIG__15F0184DDefaultColumn ConstraintASSIGNED_SEQNO DEFAULT -1
DF__STOCK_SER__ASSIG__16E43C86DefaultColumn ConstraintASSIGNED_LINESEQNO DEFAULT -1
DF__STOCK_SER__COMPN__143CDA05DefaultColumn ConstraintCOMPNO DEFAULT -1
DF__STOCK_SER__CRINV__6E17311DDefaultColumn ConstraintCRINVSEQNO DEFAULT -1
DF__STOCK_SER__CRINV__7894BF90DefaultColumn ConstraintCRINVLINESEQNO DEFAULT -1
DF__STOCK_SER__CR_AC__72DBE63ADefaultColumn ConstraintCR_ACCNO DEFAULT -1
DF__STOCK_SER__DRINV__7988E3C9DefaultColumn ConstraintDRINVLINESEQNO DEFAULT -1
DF__STOCK_SER__DR_AC__73D00A73DefaultColumn ConstraintDR_ACCNO DEFAULT -1
DF__STOCK_SER__EXPIR__69527C00DefaultColumn ConstraintEXPIRY_DATE DEFAULT getdate()
DF__STOCK_SER__IGRLI__74C42EACDefaultColumn ConstraintIGRLINESEQNO DEFAULT -1
DF__STOCK_SER__IGRSE__2843D2B2DefaultColumn ConstraintIGRSEQNO DEFAULT 0
DF__STOCK_SER__INSTO__7A7D0802DefaultColumn ConstraintINSTOCK DEFAULT Y
DF__STOCK_SER__INVSE__6F0B5556DefaultColumn ConstraintINVSEQNO DEFAULT -1
DF__STOCK_SER__ISASS__617C500EDefaultColumn ConstraintISASSIGNED DEFAULT N
DF__STOCK_SER__JOBLI__75B852E5DefaultColumn ConstraintJOBLINESEQNO DEFAULT -1
DF__STOCK_SER__JOBNO__6FFF798FDefaultColumn ConstraintJOBNO DEFAULT -1
DF__STOCK_SER__LASTU__7C655074DefaultColumn ConstraintLASTUPDATED DEFAULT getdate()
DF__STOCK_SER__LOCNO__6A46A039DefaultColumn ConstraintLOCNO DEFAULT -1
DF__STOCK_SER__PO_IN__1407CFDBDefaultColumn ConstraintPO_INVLINESEQNO DEFAULT -1
DF__STOCK_SER__PO_IN__14FBF414DefaultColumn ConstraintPO_INVSEQNO DEFAULT -1
DF__STOCK_SER__PURCH__6C2EE8ABDefaultColumn ConstraintPURCHORDNO DEFAULT -1
DF__STOCK_SER__PURCH__77A09B57DefaultColumn ConstraintPURCHORDLINESEQNO DEFAULT -1
DF__STOCK_SER__REQUE__459F2B6FDefaultColumn ConstraintREQUESTSEQNO DEFAULT -1
DF__STOCK_SER__REQUE__46934FA8DefaultColumn ConstraintREQUESTLINESEQNO DEFAULT -1
DF__STOCK_SER__RMALI__487B981ADefaultColumn ConstraintRMALINESEQNO DEFAULT -1
DF__STOCK_SER__RMASE__478773E1DefaultColumn ConstraintRMASEQNO DEFAULT -1
DF__STOCK_SER__SALES__6D230CE4DefaultColumn ConstraintSALESORDNO DEFAULT -1
DF__STOCK_SER__SALES__76AC771EDefaultColumn ConstraintSALESORDLINESEQNO DEFAULT -1
DF__STOCK_SER__SO_IN__121F8769DefaultColumn ConstraintSO_INVLINESEQNO DEFAULT -1
DF__STOCK_SER__SO_IN__1313ABA2DefaultColumn ConstraintSO_INVSEQNO DEFAULT -1
DF__STOCK_SER__STKMO__7A87F4FADefaultColumn ConstraintSTKMOVSEQNO DEFAULT -1
DF__STOCK_SER__STKMO__7B7C1933DefaultColumn ConstraintSTKMOVLINESEQNO DEFAULT -1
DF__STOCK_SER__STOCK__70F39DC8DefaultColumn ConstraintSTOCKINSEQNO DEFAULT -1
DF__STOCK_SER__STOCK__71E7C201DefaultColumn ConstraintSTOCKOUTSEQNO DEFAULT -1
DF__STOCK_SER__SU_SE__0E79DF0EDefaultColumn ConstraintSU_SEQNO DEFAULT -1
DF__STOCK_SER__UNITN__7B712C3BDefaultColumn ConstraintUNITNO DEFAULT -1
DF__STOCK_SER__WORKS__3F322502DefaultColumn ConstraintWORKSORDSEQNO DEFAULT -1
DF__STOCK_SER__WORKS__4026493BDefaultColumn ConstraintWORKSORDLINESEQNO DEFAULT -1
PK__STOCK_SERIALNOS__685E57C7Primary KeyTable ConstraintPRIMARY KEY (SEQNO)
STOCK_ITEMS_STOCK_SERIALNOSForeign KeyTable ConstraintFOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE)
STOCK_LOCATIONS_STOCK_SERIALNOSForeign KeyTable ConstraintFOREIGN KEY (LOCNO) REFERENCES STOCK_LOCATIONS(LOCNO)

Triggers:
Trigger nameCode
STOCK_SERIALNOS_LASTUPDATEDCREATE 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
CommentThis table contains details of the sizes used by the Style, Colour, Size feature.
Primary key columnsSIZEID

Attributes:
Column namePrimary keyData typeNot NULLComment
SIZEIDYesINTEGERYesA unique ID number for the size.
SIZECODENoVARCHAR(5)YesA user-defined code number for the size.
SIZENAMENoVARCHAR(30)NoA descriptive name for the size.
ISACTIVENoCHAR(1)NoThis field contains 'Y' if the size is currently active.
SORTORDERNoINTEGERNoThis field contains the sort order value, which is used to order sizes on the Style Picker window.


Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (SIZECODE)
Not NullColumn ConstraintNOT NULL (SIZEID)
DF__STOCK_SIZ__ISACT__3AF77FCADefaultColumn ConstraintISACTIVE DEFAULT Y
DF__STOCK_SIZ__SORTO__041D1783DefaultColumn ConstraintSORTORDER DEFAULT 0
PK__STOCK_SIZE__0841E2CBPrimary KeyTable ConstraintPRIMARY KEY (SIZEID)



Entity: STOCK_TRANS
CommentThis table records details of all stock transactions.
Primary key columnsSEQNO

Attributes:
Column namePrimary keyData typeNot NULLComment
SEQNOYesINTEGERYesA unique ID for the record.
POSTTIMENoDATETIMENoA timestamp of when the record was entered.
TRANSDATENoDATETIMENoThe date when the stock movement took place.
STOCKCODENoVARCHAR(23)NoThe ID code of the stock item.
TRANSTYPENoINTEGERNoThe type of the transaction. Will be one of: 0=Sales 1=Receipt 2=Transfer 3=Adjust Out 4=Adjust In 5=Average Cost Adjustment
REF1NoVARCHAR(30)NoSource transaction reference. Usually contains the Debtor/Creditor's invoice number, Purchase Order number, Sales Order number, job number, etc.
REF2NoVARCHAR(30)NoReference text with details of source of this stock movement.
QUANTITYNoFLOAT(53)NoQuantity of stock.
UNITPRICENoFLOAT(53)NoUnit price of the stock item.
LOCATIONNoINTEGERNoID number of the Location involved in the stock movement.
TOLOCATIONNoINTEGERNoID of the Destination location (applies in case of stock transfers).
FROM_LEDGERNoCHAR(1)Nod = Debtor
c = Creditor
s = Stock
j = Job Costing
i = Inwards Goods Receipt
o = Sales Orders
p = Purchase Orders
FROM_HDRNoINTEGERNoFor 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.
BATCHCODENoVARCHAR(20)NoThe batch number.
ACCNONoINTEGERNoFor manual stock movements this field is NULL. For movements originating from other ledgers, this contains the account number against the source transaction.
LINE_SEQNONoINTEGERNoFor manual stock movements this field is NULL. For movements originating from other ledgers, this contains the Line ID of the source line.
JOBNONoINTEGERNoJob Number if the stock movement sources from a job.
RECEIPT_NONoINTEGERNoReceipt number when the source of the transaction is an Inwards Goods Receipt.
GLPOSTEDNoCHAR(1)NoThis field contains 'Y' if the transaction has been posted to the General Ledger.
GLACCNoINTEGERNoThe General Ledger Account that is affected when the transaction is posted to GL.
GLSUBACCNoINTEGERNoThe General Ledger Sub Account that is affected when the transaction is posted to GL.
GLBRANCHNoINTEGERNoThe Branch used for posting to the General Ledger.
UNITCOSTNoFLOAT(53)NoThe unit cost price of the stock item.
UPDATEITEM_FLAGNoCHAR(1)YesThis 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_STOCKTAKENoCHAR(1)NoNot supported.
REVERSEDNoCHAR(1)NoThis field contains 'Y' if the transaction has been reversed.
ON_COSTSNoFLOAT(53)NoOn costs (only applicable to lines sourcing from inwards goods receipts).
POST_LOOKUP_TO_GLNoCHAR(1)NoIf this field contains 'N' and the stock item is a lookup item, there is no journal against the transaction.
EXPIRY_DATENoDATETIMENoThe Expiry Date for a batch tracked item.
GLBATCHNONoINTEGERNoID number of the GL Batch that the posted stock journal belongs to.
KITSEQNONoINTEGERYesID number of the Kit/Bill of Materials, if in this specific transaction, the stock item belonged to a Kit/BOM.
KITCODENoVARCHAR(23)NoCode of the Kit/Bill of Materials, if in this specific transaction, the stock item belonged to a Kit/BOM.
PLUNoVARCHAR(23)NoPrice Lookup Unit (applies to lookup items).
POST_TO_GLNoCHAR(1)NoIf this field is set to 'Y', the stock line is posted to GL; otherwise it is not.
PREV_QUANTITYNoFLOAT(53)NoQuantity of the stock item before the transaction.
PREV_AVECOSTNoFLOAT(53)NoAverage cost of the stock item before this transaction.
ALT_QUANTITYNoFLOAT(53)NoAlternate quantity.
ALT_AVECOSTNoFLOAT(53)NoAlternate weighted average cost.
ALT_SEQNONoINTEGERNoAlternate SEQNO.
SESSION_IDNoINTEGERNoSession ID
PERIOD_SEQNONoINTEGERYesID number of the period the transaction belongs to.
AGE_STAMPNoINTEGERNoThis is a historical field that has been superseded by the AGE calculated field.
AGENoASNoCalculated field that indicates the age of the transaction.
QTY_TRADED_IN_NEGNoFLOAT(53)NoQuantity traded when total stock on hand for the item was in negative at the time of the transaction.
VALUE_TRADED_IN_NEGNoFLOAT(53)NoValue traded when total stock on hand for the item was in negative at the time of the transaction.
PERIOD_TRADED_IN_SEQNoCHAR(1)NoThis field contains 'N' if the transaction was in a prior period.
NEW_AVECOSTNoFLOAT(53)NoAverage cost of the item after this transaction.
NEW_QUANTITYNoFLOAT(53)NoStock on hand of the item after this transaction.
NEW_LOC_QTYNoFLOAT(53)NoQuantity of the item in the location the movement took place after this transaction.
NEW_SEQORDERNoINTEGERNoNew sequence order of the transaction.
LOOKUP_RECOVERABLENoCHAR(1)NoThis field contains 'Y' if lookup items are recoverable against expenses in EXO Job Costing.

Relationships:
NameRelationship typeParentChildCardinality
GLACCS_STOCK_TRANSNon IdentifyingGLACCSSTOCK_TRANSZero Or More
PERIOD_STATUS_STOCK_TRANSNon IdentifyingPERIOD_STATUSSTOCK_TRANSZero Or More
STOCK_ITEMS_STOCK_TRANSNon IdentifyingSTOCK_ITEMSSTOCK_TRANSZero Or More
STOCK_LOCATIONS_STOCK_TRANSNon IdentifyingSTOCK_LOCATIONSSTOCK_TRANSZero Or More
STOCK_LOCATIONS_STOCK_TRANS_TONon IdentifyingSTOCK_LOCATIONSSTOCK_TRANSZero Or More

Indexes:
NameIndex columnsIndex type
STOCK_TRANS_CODESTOCKCODE ASC
STOCK_TRANS_PERIODSEQNO_INDEXPERIOD_SEQNO ASC
STOCK_TRANS_RECEIPT_NORECEIPT_NO ASC
STOCK_TRANS_STOCKCODE_PERIODNOSTOCKCODE ASC, PERIOD_SEQNO ASC

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (PERIOD_SEQNO)
Not NullColumn ConstraintNOT NULL (SEQNO)
Not NullColumn ConstraintNOT NULL (KITSEQNO)
Not NullColumn ConstraintNOT NULL (UPDATEITEM_FLAG)
DF__STOCK_TRA__AGE_S__3D5FB680DefaultColumn ConstraintAGE_STAMP DEFAULT -1
DF__STOCK_TRA__ALT_A__7306CEC3DefaultColumn ConstraintALT_AVECOST DEFAULT 0
DF__STOCK_TRA__ALT_Q__7212AA8ADefaultColumn ConstraintALT_QUANTITY DEFAULT 0
DF__STOCK_TRA__ALT_S__73FAF2FCDefaultColumn ConstraintALT_SEQNO DEFAULT -1
DF__STOCK_TRA__GLACC__420DC656DefaultColumn ConstraintGLACC DEFAULT 0
DF__STOCK_TRA__GLBRA__43F60EC8DefaultColumn ConstraintGLBRANCH DEFAULT 0
DF__STOCK_TRA__GLPOS__3D491139DefaultColumn ConstraintGLPOSTED DEFAULT N
DF__STOCK_TRA__GLSUB__4301EA8FDefaultColumn ConstraintGLSUBACC DEFAULT 0
DF__STOCK_TRA__INC_F__0D64F3EDDefaultColumn ConstraintINC_FIFO_STOCKTAKE DEFAULT N
DF__STOCK_TRA__KITSE__36D1E92BDefaultColumn ConstraintKITSEQNO DEFAULT -1
DF__STOCK_TRA__LOCAT__3726238FDefaultColumn ConstraintLOCATION DEFAULT 1
DF__STOCK_TRA__LOOKU__6815D5B2DefaultColumn ConstraintLOOKUP_RECOVERABLE DEFAULT N
DF__STOCK_TRA__NEW_A__78B651CFDefaultColumn ConstraintNEW_AVECOST DEFAULT 0
DF__STOCK_TRA__NEW_L__7A9E9A41DefaultColumn ConstraintNEW_LOC_QTY DEFAULT 0
DF__STOCK_TRA__NEW_Q__79AA7608DefaultColumn ConstraintNEW_QUANTITY DEFAULT 0
DF__STOCK_TRA__NEW_S__7B92BE7ADefaultColumn ConstraintNEW_SEQORDER DEFAULT 0
DF__STOCK_TRA__ON_CO__6DCC4D03DefaultColumn ConstraintON_COSTS DEFAULT 0
DF__STOCK_TRA__PERIO__3C6B9247DefaultColumn ConstraintPERIOD_SEQNO DEFAULT 0
DF__STOCK_TRA__PERIO__77C22D96DefaultColumn ConstraintPERIOD_TRADED_IN_SEQ DEFAULT Y
DF__STOCK_TRA__POSTT__32616E72DefaultColumn ConstraintPOSTTIME DEFAULT getdate()
DF__STOCK_TRA__POST___5DA0D232DefaultColumn ConstraintPOST_LOOKUP_TO_GL DEFAULT N
DF__STOCK_TRA__PREV___702A6218DefaultColumn ConstraintPREV_QUANTITY DEFAULT 0
DF__STOCK_TRA__PREV___711E8651DefaultColumn ConstraintPREV_AVECOST DEFAULT 0
DF__STOCK_TRA__QTY_T__75D9E524DefaultColumn ConstraintQTY_TRADED_IN_NEG DEFAULT 0
DF__STOCK_TRA__QUANT__353DDB1DDefaultColumn ConstraintQUANTITY DEFAULT 0
DF__STOCK_TRA__REVER__36670980DefaultColumn ConstraintREVERSED DEFAULT N
DF__STOCK_TRA__SESSI__145DA0EDDefaultColumn ConstraintSESSION_ID DEFAULT -1
DF__STOCK_TRA__TOLOC__381A47C8DefaultColumn ConstraintTOLOCATION DEFAULT 0
DF__STOCK_TRA__TRANS__335592ABDefaultColumn ConstraintTRANSDATE DEFAULT getdate()
DF__STOCK_TRA__TRANS__3449B6E4DefaultColumn ConstraintTRANSTYPE DEFAULT 0
DF__STOCK_TRA__UNITP__3631FF56DefaultColumn ConstraintUNITPRICE DEFAULT 0
DF__STOCK_TRA__UPDAT__4B973090DefaultColumn ConstraintUPDATEITEM_FLAG DEFAULT Y
DF__STOCK_TRA__VALUE__76CE095DDefaultColumn ConstraintVALUE_TRADED_IN_NEG DEFAULT 0
GLACCS_STOCK_TRANSForeign KeyTable ConstraintFOREIGN KEY (GLACC) REFERENCES GLACCS(ACCNO)
PERIOD_STATUS_STOCK_TRANSForeign KeyTable ConstraintFOREIGN KEY (PERIOD_SEQNO) REFERENCES PERIOD_STATUS(SEQNO)
PK__STOCK_TRANS__316D4A39Primary KeyTable ConstraintPRIMARY KEY (SEQNO)
STOCK_ITEMS_STOCK_TRANSForeign KeyTable ConstraintFOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE)
STOCK_LOCATIONS_STOCK_TRANSForeign KeyTable ConstraintFOREIGN KEY (LOCATION) REFERENCES STOCK_LOCATIONS(LOCNO)
STOCK_LOCATIONS_STOCK_TRANS_TOForeign KeyTable ConstraintFOREIGN KEY (TOLOCATION) REFERENCES STOCK_LOCATIONS(LOCNO)

Triggers:
Trigger nameCode
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_QTYCREATE 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_UPDATEITEMCREATE 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_POSTCREATE 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
CommentThis table is used for archived stock transactions.
Primary key columnsSEQNO

Attributes:
Column namePrimary keyData typeNot NULLComment
SEQNOYesINTEGERYesSee STOCK_TRANS.
POSTTIMENoDATETIMENoSee STOCK_TRANS.
TRANSDATENoDATETIMENoSee STOCK_TRANS.
STOCKCODENoVARCHAR(23)NoSee STOCK_TRANS.
TRANSTYPENoINTEGERNoSee STOCK_TRANS.
REF1NoVARCHAR(30)NoSee STOCK_TRANS.
REF2NoVARCHAR(30)NoSee STOCK_TRANS.
QUANTITYNoFLOAT(53)NoSee STOCK_TRANS.
UNITPRICENoFLOAT(53)NoSee STOCK_TRANS.
LOCATIONNoINTEGERNoSee STOCK_TRANS.
TOLOCATIONNoINTEGERNoSee STOCK_TRANS.
AGENoINTEGERNoSee STOCK_TRANS.
FROM_LEDGERNoCHAR(1)NoSee STOCK_TRANS.
FROM_HDRNoINTEGERNoSee STOCK_TRANS.
BATCHCODENoVARCHAR(20)NoSee STOCK_TRANS.
ACCNONoINTEGERNoSee STOCK_TRANS.
LINE_SEQNONoINTEGERNoSee STOCK_TRANS.
JOBNONoINTEGERNoSee STOCK_TRANS.
RECEIPT_NONoINTEGERNoSee STOCK_TRANS.
GLPOSTEDNoCHAR(1)NoSee STOCK_TRANS.
GLACCNoINTEGERNoSee STOCK_TRANS.
GLSUBACCNoINTEGERNoSee STOCK_TRANS.
GLBRANCHNoINTEGERNoSee STOCK_TRANS.
UNITCOSTNoFLOAT(53)NoSee STOCK_TRANS.
UPDATEITEM_FLAGNoCHAR(1)NoSee STOCK_TRANS.
INC_FIFO_STOCKTAKENoCHAR(1)NoSee STOCK_TRANS.
REVERSEDNoCHAR(1)NoSee STOCK_TRANS.
ON_COSTSNoFLOAT(53)NoSee STOCK_TRANS.
POST_LOOKUP_TO_GLNoCHAR(1)NoSee STOCK_TRANS.
EXPIRY_DATENoDATETIMENoSee STOCK_TRANS.
GLBATCHNONoINTEGERNoSee STOCK_TRANS.
KITSEQNONoINTEGERNoSee STOCK_TRANS.
KITCODENoVARCHAR(23)NoSee STOCK_TRANS.
PLUNoVARCHAR(23)NoSee STOCK_TRANS.
POST_TO_GLNoCHAR(1)NoSee STOCK_TRANS.
SESSION_IDNoINTEGERNoSee STOCK_TRANS.
PREV_QUANTITYNoFLOAT(53)NoSee STOCK_TRANS.
PREV_AVECOSTNoFLOAT(53)NoSee STOCK_TRANS.
ALT_QUANTITYNoFLOAT(53)NoSee STOCK_TRANS.
ALT_AVECOSTNoFLOAT(53)NoSee STOCK_TRANS.
ALT_SEQNONoINTEGERNoSee STOCK_TRANS.


Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (SEQNO)
DF__STOCK_TRA__ALT_A__70AA505CDefaultColumn ConstraintALT_AVECOST DEFAULT 0
DF__STOCK_TRA__ALT_Q__6FB62C23DefaultColumn ConstraintALT_QUANTITY DEFAULT 0
DF__STOCK_TRA__ALT_S__719E7495DefaultColumn ConstraintALT_SEQNO DEFAULT -1
DF__STOCK_TRA__PREV___6DCDE3B1DefaultColumn ConstraintPREV_QUANTITY DEFAULT 0
DF__STOCK_TRA__PREV___6EC207EADefaultColumn ConstraintPREV_AVECOST DEFAULT 0
DF__STOCK_TRA__SESSI__389B0163DefaultColumn ConstraintSESSION_ID DEFAULT -1
PK__STOCK_TRANS_ARCH__47284C85Primary KeyTable ConstraintPRIMARY KEY (SEQNO)



Entity: STOCK_TRANS_HDR
CommentThis table contains header information for stock transactions.
Primary key columnsSEQNO

Attributes:
Column namePrimary keyData typeNot NULLComment
SEQNOYesINTEGERYesA unique ID for the record.
TRANSDATENoDATETIMEYesThe date and time when the transaction took place.
TRANSTYPENoINTEGERYesThe transaction type. Will be one of:
0=Sales
1=Receipt
2=Transfer
3=Adjust Out
4=Adjust In
5=Average Cost Adjustment
REFERENCENoVARCHAR(30)NoReference text for the transaction.
STAFFNONoINTEGERYesID number of the staff member who entered the transaction.
NARRATIVE_SEQNONoINTEGERNoID of the narrative on the transaction (entered via manual stock movement).
PERIOD_SEQNONoINTEGERYesID number of the period that the record belongs to.
AGE_STAMPNoINTEGERNoThis is a historical field that has been superseded by the AGE calculated field.
AGENoASNoCalculated field that indicates the age the record belongs to.

Relationships:
NameRelationship typeParentChildCardinality
STAFF_STOCK_TRANS_HDRNon IdentifyingSTAFFSTOCK_TRANS_HDRZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (PERIOD_SEQNO)
Not NullColumn ConstraintNOT NULL (SEQNO)
Not NullColumn ConstraintNOT NULL (TRANSDATE)
Not NullColumn ConstraintNOT NULL (TRANSTYPE)
Not NullColumn ConstraintNOT NULL (STAFFNO)
DF__STOCK_TRA__AGE_S__41304764DefaultColumn ConstraintAGE_STAMP DEFAULT -1
DF__STOCK_TRA__PERIO__403C232BDefaultColumn ConstraintPERIOD_SEQNO DEFAULT 0
PK__STOCK_TRANS_HDR__42439BD7Primary KeyTable ConstraintPRIMARY KEY (SEQNO)
STAFF_STOCK_TRANS_HDRForeign KeyTable ConstraintFOREIGN KEY (STAFFNO) REFERENCES STAFF(STAFFNO)



Entity: STOCK_WEB
CommentThis 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 namePrimary keyData typeNot NULLComment
STOCKCODENoCHAR(23)YesThe stock code of the stock item that this record applies to.
SALES_HTMLNoVARCHAR(4096)NoThe Sales text (HTML) for the specified stock item.
PICTURE_URLNoVARCHAR(80)NoThe URL of the picture to be displayed for the stock item.

Relationships:
NameRelationship typeParentChildCardinality
STOCK_ITEMS_STOCK_WEBNon IdentifyingSTOCK_ITEMSSTOCK_WEBZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (STOCKCODE)
STOCK_ITEMS_STOCK_WEBForeign KeyTable ConstraintFOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE)



Entity: SUPPLIER_STOCK_ITEMS
CommentThis table contains details of any Alternate Suppliers/Stock Codes set up for stock items.
Primary key columnsSUPPLIERCODE, ACCNO

Attributes:
Column namePrimary keyData typeNot NULLComment
STOCKCODENoVARCHAR(23)YesThe stock item's ID code in Exo Business.
SUPPLIERCODEYesVARCHAR(23)YesThe supplier's ID code for the stock item.
DESCRIPTIONNoVARCHAR(40)NoThe supplier's description for the stock item.
LATESTCOSTNoFLOAT(53)NoThe supplier's latest cost for the stock item.
ACCNOYesINTEGERYesThe ID number of the supplier (Creditor) account.
ECONORDERQTYNoFLOAT(53)NoThe Minimum Order Quantity for the stock item.
PURCHPACKQUANTNoFLOAT(53)YesThe Qty per Pack when purchasing the stock item from the supplier.
PURCHPACKPRICENoFLOAT(53)YesThe Pack Cost when purchasing the stock item from the supplier.
PACKREFERENCENoVARCHAR(20)NoAn optional reference code for the pack.
LAST_UPDATENoDATETIMENoThe date when the record was last updated (not set automatically - it must be specified on the Alternate Suppliers / Stock Codes window).
DISCOUNTNoFLOAT(53)NoThe percentage discount when purchasing the stock item from the supplier.
IS_DEFAULTNoCHAR(1)YesWhether or not this supplier is the default.
LEADTIMENoINTEGERNoThe lead time for the supplier, i.e. the number of days between placing an order and its arrival.

Relationships:
NameRelationship typeParentChildCardinality
STOCK_ITEMS_SUPPLIER_STOCK_ITEMSNon IdentifyingSTOCK_ITEMSSUPPLIER_STOCK_ITEMSZero Or More

Constraints:
NameTypeLevelConstraint
Not NullColumn ConstraintNOT NULL (IS_DEFAULT)
Not NullColumn ConstraintNOT NULL (STOCKCODE)
Not NullColumn ConstraintNOT NULL (SUPPLIERCODE)
Not NullColumn ConstraintNOT NULL (PURCHPACKPRICE)
Not NullColumn ConstraintNOT NULL (ACCNO)
Not NullColumn ConstraintNOT NULL (PURCHPACKQUANT)
DF__SUPPLIER___DISCO__0742D19ADefaultColumn ConstraintDISCOUNT DEFAULT 0
DF__SUPPLIER___IS_DE__0C0786B7DefaultColumn ConstraintIS_DEFAULT DEFAULT N
DF__SUPPLIER___LATES__1FB8AE52DefaultColumn ConstraintLATESTCOST DEFAULT 0
DF__SUPPLIER___LEADT__6BD157FDDefaultColumn ConstraintLEADTIME DEFAULT 1
DF__SUPPLIER___PURCH__68536ACFDefaultColumn ConstraintPURCHPACKQUANT DEFAULT 1
DF__SUPPLIER___PURCH__69478F08DefaultColumn ConstraintPURCHPACKPRICE DEFAULT 0
PK__SUPPLIER_STOCK_I__1EC48A19Primary KeyTable ConstraintPRIMARY KEY (SUPPLIERCODE, ACCNO)
STOCK_ITEMS_SUPPLIER_STOCK_ITEMSForeign KeyTable ConstraintFOREIGN KEY (STOCKCODE) REFERENCES STOCK_ITEMS(STOCKCODE)