--- /dev/null
+-- DB2-tables.sql
+-- Bill Ott modified from Oracle tables, March 02, 2002
+--
+-- Jim Rawlings modified for use with SL 2.0.8 and DB2 v7.2
+-- and higher August 27, 2003
+--
+--
+---------------------------------------------------------
+-- DDL Statements for sequence id
+---------------------------------------------------------
+CREATE SEQUENCE id AS INTEGER START WITH 10000
+INCREMENT BY 1 MAXVALUE 2147483647 MINVALUE 1 CACHE 5
+@
+---------------------------------------------------------
+-- DDL Statements for table makemodel
+---------------------------------------------------------
+CREATE TABLE makemodel (
+ parts_id INTEGER,
+ name VARCHAR(100)
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table gl
+---------------------------------------------------------
+CREATE TABLE gl (
+ id INTEGER,
+ reference VARCHAR(50),
+ description VARCHAR(100),
+ transdate DATE WITH DEFAULT current date,
+ employee_id INTEGER,
+ notes VARCHAR(4000)
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table chart
+---------------------------------------------------------
+CREATE TABLE chart (
+ id INTEGER,
+ accno VARCHAR(20) NOT NULL,
+ description VARCHAR(100),
+ charttype CHAR(1) WITH DEFAULT 'A',
+ category CHAR(1),
+ link VARCHAR(100),
+ gifi_accno VARCHAR(20)
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table gifi
+---------------------------------------------------------
+CREATE TABLE gifi (
+ accno VARCHAR(20),
+ description VARCHAR(100)
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table defaults
+---------------------------------------------------------
+CREATE TABLE defaults (
+ inventory_accno_id INTEGER,
+ income_accno_id INTEGER,
+ expense_accno_id INTEGER,
+ fxgain_accno_id INTEGER,
+ fxloss_accno_id INTEGER,
+ invnumber VARCHAR(30),
+ sonumber VARCHAR(30),
+ yearend VARCHAR(5),
+ weightunit VARCHAR(5),
+ businessnumber VARCHAR(30),
+ version VARCHAR(8),
+ curr VARCHAR(500),
+ closedto DATE,
+ revtrans CHAR(1) WITH DEFAULT '0',
+ ponumber VARCHAR(30)
+) IN LEDGER_TS
+@
+INSERT INTO defaults (version) VALUES ('2.0.10')
+@
+---------------------------------------------------------
+-- DDL Statements for table acc_trans
+---------------------------------------------------------
+CREATE TABLE acc_trans (
+ trans_id INTEGER,
+ chart_id INTEGER,
+ amount FLOAT,
+ transdate DATE WITH DEFAULT current date,
+ source VARCHAR(20),
+ cleared CHAR(1) WITH DEFAULT '0',
+ fx_transaction CHAR(1) WITH DEFAULT '0',
+ project_id INTEGER
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table invoice
+---------------------------------------------------------
+CREATE TABLE invoice (
+ id INTEGER,
+ trans_id INTEGER,
+ parts_id INTEGER,
+ description VARCHAR(4000),
+ qty FLOAT,
+ allocated FLOAT,
+ sellprice FLOAT,
+ fxsellprice FLOAT,
+ discount FLOAT,
+ assemblyitem CHAR(1) WITH DEFAULT '0',
+ unit VARCHAR(5),
+ project_id INTEGER,
+ deliverydate DATE
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table vendor
+---------------------------------------------------------
+CREATE TABLE vendor (
+ id INTEGER,
+ name VARCHAR(35),
+ addr1 VARCHAR(35),
+ addr2 VARCHAR(35),
+ addr3 VARCHAR(35),
+ addr4 VARCHAR(35),
+ contact VARCHAR(35),
+ phone VARCHAR(20),
+ fax VARCHAR(20),
+ email VARCHAR(50),
+ notes VARCHAR(4000),
+ terms INTEGER WITH DEFAULT,
+ taxincluded CHAR(1),
+ vendornumber VARCHAR(40),
+ cc VARCHAR(50),
+ bcc VARCHAR(50)
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table customer
+---------------------------------------------------------
+CREATE TABLE customer (
+ id INTEGER,
+ name VARCHAR(35),
+ addr1 VARCHAR(35),
+ addr2 VARCHAR(35),
+ addr3 VARCHAR(35),
+ addr4 VARCHAR(35),
+ contact VARCHAR(35),
+ phone VARCHAR(20),
+ fax VARCHAR(20),
+ email VARCHAR(50),
+ notes VARCHAR(4000),
+ discount FLOAT,
+ taxincluded CHAR(1),
+ creditlimit FLOAT,
+ terms INTEGER WITH DEFAULT,
+ customernumber VARCHAR(40),
+ cc VARCHAR(50),
+ bcc VARCHAR(50)
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table parts
+---------------------------------------------------------
+CREATE TABLE parts (
+ id INTEGER,
+ partnumber VARCHAR(30),
+ description VARCHAR(4000),
+ unit VARCHAR(5),
+ listprice FLOAT,
+ sellprice FLOAT,
+ lastcost FLOAT,
+ priceupdate DATE WITH DEFAULT current date,
+ weight FLOAT,
+ onhand FLOAT WITH DEFAULT 0,
+ notes VARCHAR(1500),
+ makemodel CHAR(1) WITH DEFAULT '0',
+ assembly CHAR(1) WITH DEFAULT '0',
+ alternate CHAR(1) WITH DEFAULT '0',
+ rop FLOAT,
+ inventory_accno_id INTEGER,
+ income_accno_id INTEGER,
+ expense_accno_id INTEGER,
+ bin VARCHAR(20),
+ obsolete CHAR(1) WITH DEFAULT '0',
+ bom CHAR(1) WITH DEFAULT '0',
+ image VARCHAR(100),
+ drawing VARCHAR(100),
+ microfiche VARCHAR(100),
+ partsgroup_id INTEGER
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table assembly
+---------------------------------------------------------
+CREATE TABLE assembly (
+ id INTEGER,
+ parts_id INTEGER,
+ qty FLOAT,
+ bom CHAR(1)
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table ar
+---------------------------------------------------------
+CREATE TABLE ar (
+ id INTEGER,
+ invnumber VARCHAR(30),
+ transdate DATE WITH DEFAULT current date,
+ customer_id INTEGER,
+ taxincluded CHAR(1),
+ amount FLOAT,
+ netamount FLOAT,
+ paid FLOAT,
+ datepaid DATE,
+ duedate DATE,
+ invoice CHAR(1) WITH DEFAULT '0',
+ shippingpoint VARCHAR(100),
+ terms INTEGER WITH DEFAULT 0,
+ notes VARCHAR(4000),
+ curr CHAR(3),
+ ordnumber VARCHAR(30),
+ employee_id INTEGER
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table ap
+---------------------------------------------------------
+CREATE TABLE ap (
+ id INTEGER,
+ invnumber VARCHAR(30),
+ transdate DATE WITH DEFAULT current date,
+ vendor_id INTEGER,
+ taxincluded CHAR(1) WITH DEFAULT '0',
+ amount FLOAT,
+ netamount FLOAT,
+ paid FLOAT,
+ datepaid DATE,
+ duedate DATE,
+ invoice CHAR(1) WITH DEFAULT '0',
+ ordnumber VARCHAR(30),
+ curr CHAR(3),
+ notes VARCHAR(4000),
+ employee_id INTEGER
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table partstax
+---------------------------------------------------------
+CREATE TABLE partstax (
+ parts_id INTEGER,
+ chart_id INTEGER
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table tax
+---------------------------------------------------------
+CREATE TABLE tax (
+ chart_id INTEGER,
+ rate FLOAT,
+ taxnumber VARCHAR(30)
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table customertax
+---------------------------------------------------------
+CREATE TABLE customertax (
+ customer_id INTEGER,
+ chart_id INTEGER
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table vendortax
+---------------------------------------------------------
+CREATE TABLE vendortax (
+ vendor_id INTEGER,
+ chart_id INTEGER
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table oe
+---------------------------------------------------------
+CREATE TABLE oe (
+ id INTEGER,
+ ordnumber VARCHAR(30),
+ transdate DATE WITH DEFAULT current date,
+ vendor_id INTEGER,
+ customer_id INTEGER,
+ amount FLOAT,
+ netamount FLOAT,
+ reqdate DATE,
+ taxincluded CHAR(1),
+ shippingpoint VARCHAR(100),
+ notes VARCHAR(4000),
+ curr CHAR(3),
+ employee_id INTEGER,
+ closed CHAR(1) WITH DEFAULT '0'
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table orderitems
+---------------------------------------------------------
+CREATE TABLE orderitems (
+ trans_id INTEGER,
+ parts_id INTEGER,
+ description VARCHAR(4000),
+ qty FLOAT,
+ sellprice FLOAT,
+ discount FLOAT,
+ unit VARCHAR(5),
+ project_id INTEGER,
+ reqdate DATE
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table exchangerate
+---------------------------------------------------------
+CREATE TABLE exchangerate (
+ curr CHAR(3),
+ transdate DATE,
+ buy FLOAT,
+ sell FLOAT
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table employee
+---------------------------------------------------------
+CREATE TABLE employee (
+ id INTEGER,
+ login VARCHAR(20),
+ name VARCHAR(35),
+ addr1 VARCHAR(35),
+ addr2 VARCHAR(35),
+ addr3 VARCHAR(35),
+ addr4 VARCHAR(35),
+ workphone VARCHAR(20),
+ homephone VARCHAR(20),
+ startdate DATE WITH DEFAULT current date,
+ enddate DATE,
+ notes VARCHAR(4000)
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table shipto
+---------------------------------------------------------
+CREATE TABLE shipto (
+ trans_id INTEGER,
+ shiptoname VARCHAR(35),
+ shiptoaddr1 VARCHAR(35),
+ shiptoaddr2 VARCHAR(35),
+ shiptoaddr3 VARCHAR(35),
+ shiptoaddr4 VARCHAR(35),
+ shiptocontact VARCHAR(35),
+ shiptophone VARCHAR(20),
+ shiptofax VARCHAR(20),
+ shiptoemail VARCHAR(50)
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table project
+---------------------------------------------------------
+CREATE TABLE project (
+ id INTEGER,
+ projectnumber VARCHAR(50),
+ description VARCHAR(4000)
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+-- DDL Statements for table partsgroup
+---------------------------------------------------------
+CREATE TABLE partsgroup (
+ id INTEGER,
+ partsgroup VARCHAR(100)
+) IN LEDGER_TS
+@
+---------------------------------------------------------
+--!#
+--!# functions N/A
+--!#
+---------------------------------------------------------
+--!#
+--!# triggers
+--!#
+---------------------------------------------------------
+-- DDL Statements for trigger glid
+---------------------------------------------------------
+CREATE TRIGGER glid
+NO CASCADE BEFORE INSERT ON gl
+REFERENCING NEW AS new_id
+FOR EACH ROW MODE DB2SQL
+BEGIN ATOMIC
+set new_id.id = NEXTVAL FOR id;
+END
+@
+---------------------------------------------------------
+-- DDL Statements for trigger chartid
+---------------------------------------------------------
+CREATE TRIGGER chartid
+NO CASCADE BEFORE INSERT ON chart
+REFERENCING NEW AS new_id
+FOR EACH ROW MODE DB2SQL
+BEGIN ATOMIC
+set new_id.id = NEXTVAL FOR id;
+END
+@
+---------------------------------------------------------
+-- DDL Statements for trigger invoiceid
+---------------------------------------------------------
+CREATE TRIGGER invoiceid
+NO CASCADE BEFORE INSERT ON invoice
+REFERENCING NEW AS new_id
+FOR EACH ROW MODE DB2SQL
+BEGIN ATOMIC
+set new_id.id = NEXTVAL FOR id;
+END
+@
+---------------------------------------------------------
+-- DDL Statements for trigger vendorid
+---------------------------------------------------------
+CREATE TRIGGER vendorid
+NO CASCADE BEFORE INSERT ON vendor
+REFERENCING NEW AS new_id
+FOR EACH ROW MODE DB2SQL
+BEGIN ATOMIC
+set new_id.id = NEXTVAL FOR id;
+END
+@
+---------------------------------------------------------
+-- DDL Statements for trigger customerid
+---------------------------------------------------------
+CREATE TRIGGER customerid
+NO CASCADE BEFORE INSERT ON customer
+REFERENCING NEW AS new_id
+FOR EACH ROW MODE DB2SQL
+BEGIN ATOMIC
+set new_id.id = NEXTVAL FOR id;
+END
+@
+---------------------------------------------------------
+-- DDL Statements for trigger partsid
+---------------------------------------------------------
+CREATE TRIGGER partsid
+NO CASCADE BEFORE INSERT ON parts
+REFERENCING NEW AS new_id
+FOR EACH ROW MODE DB2SQL
+BEGIN ATOMIC
+set new_id.id = NEXTVAL FOR id;
+END
+@
+---------------------------------------------------------
+-- DDL Statements for trigger arid
+---------------------------------------------------------
+CREATE TRIGGER arid
+NO CASCADE BEFORE INSERT ON ar
+REFERENCING NEW AS new_id
+FOR EACH ROW MODE DB2SQL
+BEGIN ATOMIC
+set new_id.id = NEXTVAL FOR id;
+END
+@
+---------------------------------------------------------
+-- DDL Statements for trigger apid
+---------------------------------------------------------
+CREATE TRIGGER apid
+NO CASCADE BEFORE INSERT ON ap
+REFERENCING NEW AS new_id
+FOR EACH ROW MODE DB2SQL
+BEGIN ATOMIC
+set new_id.id = NEXTVAL FOR id;
+END
+@
+---------------------------------------------------------
+-- DDL Statements for trigger oeid
+---------------------------------------------------------
+CREATE TRIGGER oeid
+NO CASCADE BEFORE INSERT ON oe
+REFERENCING NEW AS new_id
+FOR EACH ROW MODE DB2SQL
+BEGIN ATOMIC
+set new_id.id = NEXTVAL FOR id;
+END
+@
+---------------------------------------------------------
+-- DDL Statements for trigger employeeid
+---------------------------------------------------------
+CREATE TRIGGER employeeid
+NO CASCADE BEFORE INSERT ON employee
+REFERENCING NEW AS new_id
+FOR EACH ROW MODE DB2SQL
+BEGIN ATOMIC
+set new_id.id = NEXTVAL FOR id;
+END
+@
+---------------------------------------------------------
+-- DDL Statements for trigger projectid
+---------------------------------------------------------
+CREATE TRIGGER projectid
+NO CASCADE BEFORE INSERT ON project
+REFERENCING NEW AS new_id
+FOR EACH ROW MODE DB2SQL
+BEGIN ATOMIC
+set new_id.id = NEXTVAL FOR id;
+END
+@
+---------------------------------------------------------
+-- DDL Statements for trigger partsgroupid
+---------------------------------------------------------
+CREATE TRIGGER partsgroupid
+NO CASCADE BEFORE INSERT ON partsgroup
+REFERENCING NEW AS new_id
+FOR EACH ROW MODE DB2SQL
+BEGIN ATOMIC
+set new_id.id = NEXTVAL FOR id;
+END@
+
+
projects / freeside.git / blobdiff