SQL Server Schemas
To use the extended functionality, user must create some new tables in the Sage Database. Below are the schemas required to do this.
SQL Server
CREATE TABLE [scheme].[Codisplinvdm] (
\[batch\] \[char\] (6) COLLATE Latin1\_General\_BIN NOT NULL ,
\[supplier\] \[char\] (8) COLLATE Latin1\_General\_BIN NOT NULL ,
\[item\] \[char\] (10) COLLATE Latin1\_General\_BIN NOT NULL ,
\[line\_no\] \[int\] NOT NULL ,
\[nominal\_code\] \[char\] (16) COLLATE Latin1\_General\_BIN NULL ,
\[nominal\_desc\] \[char\] (30) COLLATE Latin1\_General\_BIN NULL ,
\[vat\_code\] \[char\] (3) COLLATE Latin1\_General\_BIN NOT NULL ,
\[job\_code\] \[char\] (10) COLLATE Latin1\_General\_BIN NOT NULL ,
\[job\_expense\] \[char\] (10) COLLATE Latin1\_General\_BIN NOT NULL ,
\[currency\_value\] \[float\] NULL ,
\[local\_value\] \[float\] NULL ,
\[vat\_amount\] \[float\] NULL ,
\[goods\_vat\_ind\] \[char\] (1) COLLATE Latin1\_General\_BIN NOT NULL ,
\[analysis\_codes1\] \[char\] (10) COLLATE Latin1\_General\_BIN NULL ,
\[analysis\_codes2\] \[char\] (10) COLLATE Latin1\_General\_BIN NULL ,
\[analysis\_codes3\] \[char\] (10) COLLATE Latin1\_General\_BIN NULL ,
\[project\_code\] \[char\] (20) COLLATE SQL\_Latin1\_General\_CP437\_BIN NULL ,
\[exp\_rev\_code\] \[char\] (20) COLLATE SQL\_Latin1\_General\_CP437\_BIN NULL ,
\[date\_1\] \[datetime\] NULL ,
\[date\_2\] \[datetime\] NULL ,
\[date\_3\] \[datetime\] NULL ,
\[analysis\_1\] \[char\] (40) COLLATE Latin1\_General\_BIN NULL ,
\[analysis\_2\] \[char\] (40) COLLATE Latin1\_General\_BIN NULL ,
\[analysis\_3\] \[char\] (40) COLLATE Latin1\_General\_BIN NULL ,
\[analysis\_4\] \[char\] (40) COLLATE Latin1\_General\_BIN NULL ,
\[name\_1\] \[char\] (40) COLLATE Latin1\_General\_BIN NULL ,
\[name\_2\] \[char\] (40) COLLATE Latin1\_General\_BIN NULL ,
\[narrative\_1\] \[char\] (80) COLLATE Latin1\_General\_BIN NULL ,
\[narrative\_2\] \[char\] (80) COLLATE Latin1\_General\_BIN NULL ,
\[narrative\_3\] \[char\] (80) COLLATE Latin1\_General\_BIN NULL ,
\[narrative\_4\] \[char\] (80) COLLATE Latin1\_General\_BIN NULL ,
\[value\_1\] \[float\] NULL ,
\[value\_2\] \[float\] NULL ,
\[value\_3\] \[float\] NULL ,
\[currency\_1\] \[char\] (3) COLLATE Latin1\_General\_BIN NULL ,
\[currency\_2\] \[char\] (3) COLLATE Latin1\_General\_BIN NULL ,
\[currency\_3\] \[char\] (3) COLLATE Latin1\_General\_BIN NULL
) ON [PRIMARY]
GO
ALTER TABLE [scheme].[Codisplinvdm] WITH NOCHECK ADD
CONSTRAINT \[PK\_Codisplinvdm\] PRIMARY KEY CLUSTERED
(
\[batch\],
\[supplier\],
\[item\],
\[line\_no\]
) ON \[PRIMARY\]
GO
CREATE TABLE [scheme].[Codisplinvhm] (
\[batch\] \[char\] (6) COLLATE Latin1\_General\_BIN NOT NULL ,
\[supplier\] \[char\] (8) COLLATE Latin1\_General\_BIN NOT NULL ,
\[item\] \[char\] (10) COLLATE Latin1\_General\_BIN NOT NULL ,
\[refernce\] \[char\] (10) COLLATE Latin1\_General\_BIN NULL ,
\[dated\] \[datetime\] NULL ,
\[due\_date\] \[datetime\] NULL ,
\[name\] \[char\] (32) COLLATE Latin1\_General\_BIN NOT NULL ,
\[alpha\] \[char\] (8) COLLATE Latin1\_General\_BIN NOT NULL ,
\[currency\] \[char\] (3) COLLATE Latin1\_General\_BIN NOT NULL ,
\[exchange\_rate\] \[float\] NULL ,
\[currency\_amount\] \[float\] NULL ,
\[local\_amount\] \[float\] NULL ,
\[vat\_amount\] \[float\] NULL ,
\[settlement\_dis\] \[float\] NULL ,
\[sett\_due\_date\] \[datetime\] NULL ,
\[posting\_ind\] \[char\] (1) COLLATE Latin1\_General\_BIN NOT NULL ,
\[authoriser\] \[char\] (10) COLLATE Latin1\_General\_BIN NOT NULL ,
\[auth\_date\] \[datetime\] NULL ,
\[posting\_sel\] \[char\] (1) COLLATE Latin1\_General\_BIN NOT NULL ,
\[analysis\_codes1\] \[char\] (10) COLLATE Latin1\_General\_BIN NOT NULL ,
\[analysis\_codes2\] \[char\] (10) COLLATE Latin1\_General\_BIN NOT NULL ,
\[analysis\_codes3\] \[char\] (10) COLLATE Latin1\_General\_BIN NOT NULL ,
\[username\] \[char\] (10) COLLATE Latin1\_General\_BIN NOT NULL ,
\[userdate\] \[datetime\] NULL ,
\[usertime\] \[char\] (8) COLLATE Latin1\_General\_BIN NOT NULL ,
\[retention\_amount\] \[float\] NULL ,
\[our\_reference\] \[char\] (10) COLLATE Latin1\_General\_BIN NOT NULL ,
\[fixed\_rate\] \[char\] (1) COLLATE Latin1\_General\_BIN NOT NULL ,
\[currency\_operator\] \[char\] (1) COLLATE Latin1\_General\_BIN NOT NULL ,
\[currency\_type\] \[char\] (2) COLLATE Latin1\_General\_BIN NOT NULL ,
\[reg\_batch\] \[char\] (1) COLLATE Latin1\_General\_BIN NOT NULL ,
\[reg\_status\] \[char\] (1) COLLATE Latin1\_General\_BIN NOT NULL ,
\[short\_name\] \[char\] (3) COLLATE Latin1\_General\_BIN NOT NULL ,
\[effective\_date\] \[datetime\] NULL ,
\[period\] \[char\] (2) COLLATE Latin1\_General\_BIN NOT NULL ,
\[plyear\] \[char\] (4) COLLATE Latin1\_General\_BIN NOT NULL ,
\[sett\_category\] \[char\] (2) COLLATE Latin1\_General\_BIN NOT NULL ,
\[settlement\_dis2\] \[float\] NULL ,
\[settlement\_dis3\] \[float\] NULL ,
\[settlement\_dis4\] \[float\] NULL ,
\[sett\_due\_date2\] \[datetime\] NULL ,
\[sett\_due\_date3\] \[datetime\] NULL ,
\[sett\_due\_date4\] \[datetime\] NULL ,
\[control\] \[char\] (16) COLLATE Latin1\_General\_BIN NOT NULL ,
\[date\_1\] \[datetime\] NULL ,
\[date\_2\] \[datetime\] NULL ,
\[date\_3\] \[datetime\] NULL ,
\[analysis\_1\] \[char\] (40) COLLATE Latin1\_General\_BIN NULL ,
\[analysis\_2\] \[char\] (40) COLLATE Latin1\_General\_BIN NULL ,
\[analysis\_3\] \[char\] (40) COLLATE Latin1\_General\_BIN NULL ,
\[analysis\_4\] \[char\] (40) COLLATE Latin1\_General\_BIN NULL ,
\[name\_1\] \[char\] (40) COLLATE Latin1\_General\_BIN NULL ,
\[name\_2\] \[char\] (40) COLLATE Latin1\_General\_BIN NULL ,
\[narrative\_1\] \[char\] (80) COLLATE Latin1\_General\_BIN NULL ,
\[narrative\_2\] \[char\] (80) COLLATE Latin1\_General\_BIN NULL ,
\[narrative\_3\] \[char\] (80) COLLATE Latin1\_General\_BIN NULL ,
\[narrative\_4\] \[char\] (80) COLLATE Latin1\_General\_BIN NULL ,
\[value\_1\] \[float\] NULL ,
\[value\_2\] \[float\] NULL ,
\[value\_3\] \[float\] NULL ,
\[currency\_1\] \[char\] (3) COLLATE Latin1\_General\_BIN NULL ,
\[currency\_2\] \[char\] (3) COLLATE Latin1\_General\_BIN NULL ,
\[currency\_3\] \[char\] (3) COLLATE Latin1\_General\_BIN NULL
) ON [PRIMARY]
GO
ALTER TABLE [scheme].[Codisplinvhm] WITH NOCHECK ADD
CONSTRAINT \[PK\_Codisplinvhm\] PRIMARY KEY CLUSTERED
(
\[batch\],
\[supplier\],
\[item\]
) ON \[PRIMARY\]
GO