2 -- Paulo Rodrigues: added functions and triggers, Oct. 31, 2001
4 -- Modified for use with SL 2.0 and Oracle 9i2, Dec 13, 2002
6 ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
8 CREATE SEQUENCE id START WITH 10000 INCREMENT BY 1 MAXVALUE 2147483647 MINVALUE 1 CACHE 2;
9 SELECT ID.NEXTVAL FROM DUAL;
11 CREATE TABLE makemodel (
18 reference VARCHAR2(50),
19 description VARCHAR2(100),
20 transdate DATE DEFAULT SYSDATE,
27 accno VARCHAR2(20) NOT NULL,
28 description VARCHAR2(100),
29 charttype CHAR(1) DEFAULT 'A',
32 gifi_accno VARCHAR2(20)
37 description VARCHAR2(100)
40 CREATE TABLE defaults (
41 inventory_accno_id INTEGER,
42 income_accno_id INTEGER,
43 expense_accno_id INTEGER,
44 fxgain_accno_id INTEGER,
45 fxloss_accno_id INTEGER,
46 invnumber VARCHAR2(30),
47 sonumber VARCHAR2(30),
49 weightunit VARCHAR2(5),
50 businessnumber VARCHAR2(30),
54 revtrans CHAR(1) DEFAULT '0',
57 INSERT INTO defaults (version) VALUES ('2.0.8');
59 CREATE TABLE acc_trans (
63 transdate DATE DEFAULT SYSDATE,
65 cleared CHAR(1) DEFAULT '0',
66 fx_transaction CHAR(1) DEFAULT '0',
70 CREATE TABLE invoice (
74 description VARCHAR2(4000),
80 assemblyitem CHAR(1) DEFAULT '0',
98 terms INTEGER DEFAULT 0,
100 vendornumber VARCHAR2(40),
105 CREATE TABLE customer (
112 contact VARCHAR2(35),
116 notes VARCHAR2(4000),
120 terms INTEGER DEFAULT 0,
121 customernumber VARCHAR2(40),
128 partnumber VARCHAR2(30),
129 description VARCHAR2(4000),
134 priceupdate DATE DEFAULT SYSDATE,
136 onhand FLOAT DEFAULT 0,
137 notes VARCHAR2(4000),
138 makemodel CHAR(1) DEFAULT '0',
139 assembly CHAR(1) DEFAULT '0',
140 alternate CHAR(1) DEFAULT '0',
142 inventory_accno_id INTEGER,
143 income_accno_id INTEGER,
144 expense_accno_id INTEGER,
146 obsolete CHAR(1) DEFAULT '0',
147 bom CHAR(1) DEFAULT '0',
149 drawing VARCHAR2(100),
150 microfiche VARCHAR2(100),
151 partsgroup_id INTEGER
154 CREATE TABLE assembly (
163 invnumber VARCHAR2(30),
164 transdate DATE DEFAULT SYSDATE,
172 invoice CHAR(1) DEFAULT '0',
173 shippingpoint VARCHAR2(100),
174 terms INTEGER DEFAULT 0,
175 notes VARCHAR2(4000),
177 ordnumber VARCHAR2(30),
183 invnumber VARCHAR2(30),
184 transdate DATE DEFAULT SYSDATE,
186 taxincluded CHAR(1) DEFAULT '0',
192 invoice CHAR(1) DEFAULT '0',
193 ordnumber VARCHAR2(30),
195 notes VARCHAR2(4000),
199 CREATE TABLE partstax (
207 taxnumber VARCHAR2(30)
210 CREATE TABLE customertax (
215 CREATE TABLE vendortax (
222 ordnumber VARCHAR2(30),
223 transdate DATE DEFAULT SYSDATE,
230 shippingpoint VARCHAR2(100),
231 notes VARCHAR2(4000),
234 closed CHAR(1) DEFAULT '0'
237 CREATE TABLE orderitems (
240 description VARCHAR2(4000),
249 CREATE TABLE exchangerate (
256 CREATE TABLE employee (
264 workphone VARCHAR2(20),
265 homephone VARCHAR2(20),
266 startdate DATE DEFAULT SYSDATE,
271 CREATE TABLE shipto (
273 shiptoname VARCHAR2(35),
274 shiptoaddr1 VARCHAR2(35),
275 shiptoaddr2 VARCHAR2(35),
276 shiptoaddr3 VARCHAR2(35),
277 shiptoaddr4 VARCHAR2(35),
278 shiptocontact VARCHAR2(35),
279 shiptophone VARCHAR2(20),
280 shiptofax VARCHAR2(20),
281 shiptoemail VARCHAR2(50)
284 CREATE TABLE project (
286 projectnumber VARCHAR2(50),
287 description VARCHAR2(4000)
290 CREATE TABLE partsgroup (
292 partsgroup VARCHAR2(100)
297 CREATE OR REPLACE FUNCTION current_date RETURN date AS
304 CREATE OR REPLACE TRIGGER glid BEFORE INSERT ON gl FOR EACH ROW
311 CREATE OR REPLACE TRIGGER chartid BEFORE INSERT ON chart FOR EACH ROW
318 CREATE OR REPLACE TRIGGER invoiceid BEFORE INSERT ON invoice FOR EACH ROW
325 CREATE OR REPLACE TRIGGER vendorid BEFORE INSERT ON vendor FOR EACH ROW
332 CREATE OR REPLACE TRIGGER customerid BEFORE INSERT ON customer FOR EACH ROW
339 CREATE OR REPLACE TRIGGER partsid BEFORE INSERT ON parts FOR EACH ROW
346 CREATE OR REPLACE TRIGGER arid BEFORE INSERT ON ar FOR EACH ROW
353 CREATE OR REPLACE TRIGGER apid BEFORE INSERT ON ap FOR EACH ROW
360 CREATE OR REPLACE TRIGGER oeid BEFORE INSERT ON oe FOR EACH ROW
367 CREATE OR REPLACE TRIGGER employeeid BEFORE INSERT ON employee FOR EACH ROW
374 CREATE OR REPLACE TRIGGER projectid BEFORE INSERT ON project FOR EACH ROW
381 CREATE OR REPLACE TRIGGER partsgroupid BEFORE INSERT ON partsgroup FOR EACH ROW