diff options
Diffstat (limited to 'sql-ledger/sql/DB2-tables.sql')
| -rw-r--r-- | sql-ledger/sql/DB2-tables.sql | 511 |
1 files changed, 0 insertions, 511 deletions
diff --git a/sql-ledger/sql/DB2-tables.sql b/sql-ledger/sql/DB2-tables.sql deleted file mode 100644 index 3ac7799..0000000 --- a/sql-ledger/sql/DB2-tables.sql +++ /dev/null @@ -1,511 +0,0 @@ --- 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@ - - |