2 -- Bill Ott modified from Oracle tables, March 02, 2002
4 -- Jim Rawlings modified for use with SL 2.0.8 and DB2 v7.2
5 -- and higher August 27, 2003
8 ---------------------------------------------------------
9 -- DDL Statements for sequence id
10 ---------------------------------------------------------
11 CREATE SEQUENCE id AS INTEGER START WITH 10000
12 INCREMENT BY 1 MAXVALUE 2147483647 MINVALUE 1 CACHE 5
14 ---------------------------------------------------------
15 -- DDL Statements for table makemodel
16 ---------------------------------------------------------
17 CREATE TABLE makemodel (
22 ---------------------------------------------------------
23 -- DDL Statements for table gl
24 ---------------------------------------------------------
27 reference VARCHAR(50),
28 description VARCHAR(100),
29 transdate DATE WITH DEFAULT current date,
34 ---------------------------------------------------------
35 -- DDL Statements for table chart
36 ---------------------------------------------------------
39 accno VARCHAR(20) NOT NULL,
40 description VARCHAR(100),
41 charttype CHAR(1) WITH DEFAULT 'A',
44 gifi_accno VARCHAR(20)
47 ---------------------------------------------------------
48 -- DDL Statements for table gifi
49 ---------------------------------------------------------
52 description VARCHAR(100)
55 ---------------------------------------------------------
56 -- DDL Statements for table defaults
57 ---------------------------------------------------------
58 CREATE TABLE defaults (
59 inventory_accno_id INTEGER,
60 income_accno_id INTEGER,
61 expense_accno_id INTEGER,
62 fxgain_accno_id INTEGER,
63 fxloss_accno_id INTEGER,
64 invnumber VARCHAR(30),
67 weightunit VARCHAR(5),
68 businessnumber VARCHAR(30),
72 revtrans CHAR(1) WITH DEFAULT '0',
76 INSERT INTO defaults (version) VALUES ('2.0.10')
78 ---------------------------------------------------------
79 -- DDL Statements for table acc_trans
80 ---------------------------------------------------------
81 CREATE TABLE acc_trans (
85 transdate DATE WITH DEFAULT current date,
87 cleared CHAR(1) WITH DEFAULT '0',
88 fx_transaction CHAR(1) WITH DEFAULT '0',
92 ---------------------------------------------------------
93 -- DDL Statements for table invoice
94 ---------------------------------------------------------
95 CREATE TABLE invoice (
99 description VARCHAR(4000),
105 assemblyitem CHAR(1) WITH DEFAULT '0',
111 ---------------------------------------------------------
112 -- DDL Statements for table vendor
113 ---------------------------------------------------------
114 CREATE TABLE vendor (
126 terms INTEGER WITH DEFAULT,
128 vendornumber VARCHAR(40),
133 ---------------------------------------------------------
134 -- DDL Statements for table customer
135 ---------------------------------------------------------
136 CREATE TABLE customer (
151 terms INTEGER WITH DEFAULT,
152 customernumber VARCHAR(40),
157 ---------------------------------------------------------
158 -- DDL Statements for table parts
159 ---------------------------------------------------------
162 partnumber VARCHAR(30),
163 description VARCHAR(4000),
168 priceupdate DATE WITH DEFAULT current date,
170 onhand FLOAT WITH DEFAULT 0,
172 makemodel CHAR(1) WITH DEFAULT '0',
173 assembly CHAR(1) WITH DEFAULT '0',
174 alternate CHAR(1) WITH DEFAULT '0',
176 inventory_accno_id INTEGER,
177 income_accno_id INTEGER,
178 expense_accno_id INTEGER,
180 obsolete CHAR(1) WITH DEFAULT '0',
181 bom CHAR(1) WITH DEFAULT '0',
183 drawing VARCHAR(100),
184 microfiche VARCHAR(100),
185 partsgroup_id INTEGER
188 ---------------------------------------------------------
189 -- DDL Statements for table assembly
190 ---------------------------------------------------------
191 CREATE TABLE assembly (
198 ---------------------------------------------------------
199 -- DDL Statements for table ar
200 ---------------------------------------------------------
203 invnumber VARCHAR(30),
204 transdate DATE WITH DEFAULT current date,
212 invoice CHAR(1) WITH DEFAULT '0',
213 shippingpoint VARCHAR(100),
214 terms INTEGER WITH DEFAULT 0,
217 ordnumber VARCHAR(30),
221 ---------------------------------------------------------
222 -- DDL Statements for table ap
223 ---------------------------------------------------------
226 invnumber VARCHAR(30),
227 transdate DATE WITH DEFAULT current date,
229 taxincluded CHAR(1) WITH DEFAULT '0',
235 invoice CHAR(1) WITH DEFAULT '0',
236 ordnumber VARCHAR(30),
242 ---------------------------------------------------------
243 -- DDL Statements for table partstax
244 ---------------------------------------------------------
245 CREATE TABLE partstax (
250 ---------------------------------------------------------
251 -- DDL Statements for table tax
252 ---------------------------------------------------------
256 taxnumber VARCHAR(30)
259 ---------------------------------------------------------
260 -- DDL Statements for table customertax
261 ---------------------------------------------------------
262 CREATE TABLE customertax (
267 ---------------------------------------------------------
268 -- DDL Statements for table vendortax
269 ---------------------------------------------------------
270 CREATE TABLE vendortax (
275 ---------------------------------------------------------
276 -- DDL Statements for table oe
277 ---------------------------------------------------------
280 ordnumber VARCHAR(30),
281 transdate DATE WITH DEFAULT current date,
288 shippingpoint VARCHAR(100),
292 closed CHAR(1) WITH DEFAULT '0'
295 ---------------------------------------------------------
296 -- DDL Statements for table orderitems
297 ---------------------------------------------------------
298 CREATE TABLE orderitems (
301 description VARCHAR(4000),
310 ---------------------------------------------------------
311 -- DDL Statements for table exchangerate
312 ---------------------------------------------------------
313 CREATE TABLE exchangerate (
320 ---------------------------------------------------------
321 -- DDL Statements for table employee
322 ---------------------------------------------------------
323 CREATE TABLE employee (
331 workphone VARCHAR(20),
332 homephone VARCHAR(20),
333 startdate DATE WITH DEFAULT current date,
338 ---------------------------------------------------------
339 -- DDL Statements for table shipto
340 ---------------------------------------------------------
341 CREATE TABLE shipto (
343 shiptoname VARCHAR(35),
344 shiptoaddr1 VARCHAR(35),
345 shiptoaddr2 VARCHAR(35),
346 shiptoaddr3 VARCHAR(35),
347 shiptoaddr4 VARCHAR(35),
348 shiptocontact VARCHAR(35),
349 shiptophone VARCHAR(20),
350 shiptofax VARCHAR(20),
351 shiptoemail VARCHAR(50)
354 ---------------------------------------------------------
355 -- DDL Statements for table project
356 ---------------------------------------------------------
357 CREATE TABLE project (
359 projectnumber VARCHAR(50),
360 description VARCHAR(4000)
363 ---------------------------------------------------------
364 -- DDL Statements for table partsgroup
365 ---------------------------------------------------------
366 CREATE TABLE partsgroup (
368 partsgroup VARCHAR(100)
371 ---------------------------------------------------------
375 ---------------------------------------------------------
379 ---------------------------------------------------------
380 -- DDL Statements for trigger glid
381 ---------------------------------------------------------
383 NO CASCADE BEFORE INSERT ON gl
384 REFERENCING NEW AS new_id
385 FOR EACH ROW MODE DB2SQL
387 set new_id.id = NEXTVAL FOR id;
390 ---------------------------------------------------------
391 -- DDL Statements for trigger chartid
392 ---------------------------------------------------------
393 CREATE TRIGGER chartid
394 NO CASCADE BEFORE INSERT ON chart
395 REFERENCING NEW AS new_id
396 FOR EACH ROW MODE DB2SQL
398 set new_id.id = NEXTVAL FOR id;
401 ---------------------------------------------------------
402 -- DDL Statements for trigger invoiceid
403 ---------------------------------------------------------
404 CREATE TRIGGER invoiceid
405 NO CASCADE BEFORE INSERT ON invoice
406 REFERENCING NEW AS new_id
407 FOR EACH ROW MODE DB2SQL
409 set new_id.id = NEXTVAL FOR id;
412 ---------------------------------------------------------
413 -- DDL Statements for trigger vendorid
414 ---------------------------------------------------------
415 CREATE TRIGGER vendorid
416 NO CASCADE BEFORE INSERT ON vendor
417 REFERENCING NEW AS new_id
418 FOR EACH ROW MODE DB2SQL
420 set new_id.id = NEXTVAL FOR id;
423 ---------------------------------------------------------
424 -- DDL Statements for trigger customerid
425 ---------------------------------------------------------
426 CREATE TRIGGER customerid
427 NO CASCADE BEFORE INSERT ON customer
428 REFERENCING NEW AS new_id
429 FOR EACH ROW MODE DB2SQL
431 set new_id.id = NEXTVAL FOR id;
434 ---------------------------------------------------------
435 -- DDL Statements for trigger partsid
436 ---------------------------------------------------------
437 CREATE TRIGGER partsid
438 NO CASCADE BEFORE INSERT ON parts
439 REFERENCING NEW AS new_id
440 FOR EACH ROW MODE DB2SQL
442 set new_id.id = NEXTVAL FOR id;
445 ---------------------------------------------------------
446 -- DDL Statements for trigger arid
447 ---------------------------------------------------------
449 NO CASCADE BEFORE INSERT ON ar
450 REFERENCING NEW AS new_id
451 FOR EACH ROW MODE DB2SQL
453 set new_id.id = NEXTVAL FOR id;
456 ---------------------------------------------------------
457 -- DDL Statements for trigger apid
458 ---------------------------------------------------------
460 NO CASCADE BEFORE INSERT ON ap
461 REFERENCING NEW AS new_id
462 FOR EACH ROW MODE DB2SQL
464 set new_id.id = NEXTVAL FOR id;
467 ---------------------------------------------------------
468 -- DDL Statements for trigger oeid
469 ---------------------------------------------------------
471 NO CASCADE BEFORE INSERT ON oe
472 REFERENCING NEW AS new_id
473 FOR EACH ROW MODE DB2SQL
475 set new_id.id = NEXTVAL FOR id;
478 ---------------------------------------------------------
479 -- DDL Statements for trigger employeeid
480 ---------------------------------------------------------
481 CREATE TRIGGER employeeid
482 NO CASCADE BEFORE INSERT ON employee
483 REFERENCING NEW AS new_id
484 FOR EACH ROW MODE DB2SQL
486 set new_id.id = NEXTVAL FOR id;
489 ---------------------------------------------------------
490 -- DDL Statements for trigger projectid
491 ---------------------------------------------------------
492 CREATE TRIGGER projectid
493 NO CASCADE BEFORE INSERT ON project
494 REFERENCING NEW AS new_id
495 FOR EACH ROW MODE DB2SQL
497 set new_id.id = NEXTVAL FOR id;
500 ---------------------------------------------------------
501 -- DDL Statements for trigger partsgroupid
502 ---------------------------------------------------------
503 CREATE TRIGGER partsgroupid
504 NO CASCADE BEFORE INSERT ON partsgroup
505 REFERENCING NEW AS new_id
506 FOR EACH ROW MODE DB2SQL
508 set new_id.id = NEXTVAL FOR id;