path: root/sql/DB2-tables.sql

-- 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@