diff options
author | christopherm <christopherm@4979c152-3d1c-0410-bac9-87ea11338e46> | 2006-09-01 01:16:38 +0000 |
---|---|---|
committer | christopherm <christopherm@4979c152-3d1c-0410-bac9-87ea11338e46> | 2006-09-01 01:16:38 +0000 |
commit | ac5b087ea2d9ba7428d367aaeb288534158fee9a (patch) | |
tree | 2dbe0bdea0b653a215ba9ddfdf627cb57855050d /sql/Oracle-tables.sql |
Initial Import
git-svn-id: https://ledger-smb.svn.sourceforge.net/svnroot/ledger-smb/ledger-smb@1 4979c152-3d1c-0410-bac9-87ea11338e46
Diffstat (limited to 'sql/Oracle-tables.sql')
-rwxr-xr-x | sql/Oracle-tables.sql | 424 |
1 files changed, 424 insertions, 0 deletions
diff --git a/sql/Oracle-tables.sql b/sql/Oracle-tables.sql new file mode 100755 index 00000000..9bd97ce7 --- /dev/null +++ b/sql/Oracle-tables.sql @@ -0,0 +1,424 @@ +-- Oracle-tables.sql +-- Paulo Rodrigues: added functions and triggers, Oct. 31, 2001 +-- +-- Modified for use with SL 2.0 and Oracle 9i2, Dec 13, 2002 +-- Updated to 2.3.0, Dec 18, 2003 +-- +ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; +-- +CREATE SEQUENCE id START WITH 10000 INCREMENT BY 1 MAXVALUE 2147483647 MINVALUE 1 CACHE 2; +SELECT ID.NEXTVAL FROM DUAL; +-- +CREATE SEQUENCE invoiceid START WITH 1 INCREMENT BY 1 MAXVALUE 2147483647 MINVALUE 1 CACHE 2; +SELECT INVOICEID.NEXTVAL FROM DUAL; +-- +CREATE TABLE makemodel ( + parts_id INTEGER, + make VARCHAR2(64), + model VARCHAR2(64) +); +-- +CREATE TABLE gl ( + id INTEGER, + reference VARCHAR2(50), + description VARCHAR2(100), + transdate DATE DEFAULT SYSDATE, + employee_id INTEGER, + notes VARCHAR2(4000), + department_id INTEGER DEFAULT 0 +); +-- +CREATE TABLE chart ( + id INTEGER, + accno VARCHAR2(20) NOT NULL, + description VARCHAR2(100), + charttype CHAR(1) DEFAULT 'A', + category CHAR(1), + link VARCHAR2(100), + gifi_accno VARCHAR2(20) +); +-- +CREATE TABLE gifi ( + accno VARCHAR2(20), + description VARCHAR2(100) +); +-- +CREATE TABLE defaults ( + inventory_accno_id INTEGER, + income_accno_id INTEGER, + expense_accno_id INTEGER, + fxgain_accno_id INTEGER, + fxloss_accno_id INTEGER, + invnumber VARCHAR2(30), + sonumber VARCHAR2(30), + yearend VARCHAR2(5), + weightunit VARCHAR2(5), + businessnumber VARCHAR2(30), + version VARCHAR2(8), + curr VARCHAR2(500), + closedto DATE, + revtrans CHAR(1) DEFAULT '0', + ponumber VARCHAR2(30), + sqnumber VARCHAR2(30), + rfqnumber VARCHAR2(30) +); +INSERT INTO defaults (version) VALUES ('2.3.0'); +-- +CREATE TABLE acc_trans ( + trans_id INTEGER, + chart_id INTEGER, + amount FLOAT, + transdate DATE DEFAULT SYSDATE, + source VARCHAR2(20), + cleared CHAR(1) DEFAULT '0', + fx_transaction CHAR(1) DEFAULT '0', + project_id INTEGER +); +-- +CREATE TABLE invoice ( + id INTEGER, + trans_id INTEGER, + parts_id INTEGER, + description VARCHAR2(4000), + qty FLOAT, + allocated FLOAT, + sellprice FLOAT, + fxsellprice FLOAT, + discount FLOAT, + assemblyitem CHAR(1) DEFAULT '0', + unit VARCHAR2(5), + project_id INTEGER, + deliverydate DATE, + serialnumber VARCHAR2(200) +); +-- +CREATE TABLE vendor ( + id INTEGER, + name VARCHAR2(35), + addr1 VARCHAR2(35), + addr2 VARCHAR2(35), + addr3 VARCHAR2(35), + addr4 VARCHAR2(35), + contact VARCHAR2(35), + phone VARCHAR2(20), + fax VARCHAR2(20), + email VARCHAR2(50), + notes VARCHAR2(4000), + terms INTEGER DEFAULT 0, + taxincluded CHAR(1), + vendornumber VARCHAR2(40), + cc VARCHAR2(50), + bcc VARCHAR2(50) +); +-- +CREATE TABLE customer ( + id INTEGER, + name VARCHAR2(35), + addr1 VARCHAR2(35), + addr2 VARCHAR2(35), + addr3 VARCHAR2(35), + addr4 VARCHAR2(35), + contact VARCHAR2(35), + phone VARCHAR2(20), + fax VARCHAR2(20), + email VARCHAR2(50), + notes VARCHAR2(4000), + discount FLOAT, + taxincluded CHAR(1), + creditlimit FLOAT, + terms INTEGER DEFAULT 0, + customernumber VARCHAR2(40), + cc VARCHAR2(50), + bcc VARCHAR2(50) +); +-- +CREATE TABLE parts ( + id INTEGER, + partnumber VARCHAR2(30), + description VARCHAR2(4000), + unit VARCHAR2(5), + listprice FLOAT, + sellprice FLOAT, + lastcost FLOAT, + priceupdate DATE DEFAULT SYSDATE, + weight FLOAT, + onhand FLOAT DEFAULT 0, + notes VARCHAR2(4000), + makemodel CHAR(1) DEFAULT '0', + assembly CHAR(1) DEFAULT '0', + alternate CHAR(1) DEFAULT '0', + rop FLOAT, + inventory_accno_id INTEGER, + income_accno_id INTEGER, + expense_accno_id INTEGER, + bin VARCHAR2(20), + obsolete CHAR(1) DEFAULT '0', + bom CHAR(1) DEFAULT '0', + image VARCHAR2(100), + drawing VARCHAR2(100), + microfiche VARCHAR2(100), + partsgroup_id INTEGER +); +-- +CREATE TABLE assembly ( + id INTEGER, + parts_id INTEGER, + qty FLOAT, + bom char(1) +); +-- +CREATE TABLE ar ( + id INTEGER, + invnumber VARCHAR2(30), + transdate DATE DEFAULT SYSDATE, + customer_id INTEGER, + taxincluded CHAR(1), + amount FLOAT, + netamount FLOAT, + paid FLOAT, + datepaid DATE, + duedate DATE, + invoice CHAR(1) DEFAULT '0', + shippingpoint VARCHAR2(100), + terms INTEGER DEFAULT 0, + notes VARCHAR2(4000), + curr CHAR(3), + ordnumber VARCHAR2(30), + employee_id INTEGER, + till VARCHAR2(20), + quonumber VARCHAR2(30), + intnotes VARCHAR2(4000), + department_id INTEGER DEFAULT 0 +); +-- +CREATE TABLE ap ( + id INTEGER, + invnumber VARCHAR2(30), + transdate DATE DEFAULT SYSDATE, + vendor_id INTEGER, + taxincluded CHAR(1) DEFAULT '0', + amount FLOAT, + netamount FLOAT, + paid FLOAT, + datepaid DATE, + duedate DATE, + invoice CHAR(1) DEFAULT '0', + ordnumber VARCHAR2(30), + curr CHAR(3), + notes VARCHAR2(4000), + employee_id INTEGER, + till VARCHAR2(20), + quonumber VARCHAR2(30), + intnotes VARCHAR2(4000), + department_id INTEGER DEFAULT 0 +); +-- +CREATE TABLE partstax ( + parts_id INTEGER, + chart_id INTEGER +); +-- +CREATE TABLE tax ( + chart_id INTEGER, + rate FLOAT, + taxnumber VARCHAR2(30) +); +-- +CREATE TABLE customertax ( + customer_id INTEGER, + chart_id INTEGER +); +-- +CREATE TABLE vendortax ( + vendor_id INTEGER, + chart_id INTEGER +); +-- +CREATE TABLE oe ( + id INTEGER, + ordnumber VARCHAR2(30), + transdate DATE DEFAULT SYSDATE, + vendor_id INTEGER, + customer_id INTEGER, + amount FLOAT, + netamount FLOAT, + reqdate DATE, + taxincluded CHAR(1), + shippingpoint VARCHAR2(100), + notes VARCHAR2(4000), + curr CHAR(3), + employee_id INTEGER, + closed CHAR(1) DEFAULT '0', + quotation CHAR(1) DEFAULT '0', + quonumber VARCHAR2(30), + intnotes VARCHAR2(4000), + department_id INTEGER DEFAULT 0 +); +-- +CREATE TABLE orderitems ( + trans_id INTEGER, + parts_id INTEGER, + description VARCHAR2(4000), + qty FLOAT, + sellprice FLOAT, + discount FLOAT, + unit VARCHAR2(5), + project_id INTEGER, + reqdate DATE +); +-- +CREATE TABLE exchangerate ( + curr CHAR(3), + transdate DATE, + buy FLOAT, + sell FLOAT +); +-- +CREATE TABLE employee ( + id INTEGER, + login VARCHAR2(20), + name VARCHAR2(35), + addr1 VARCHAR2(35), + addr2 VARCHAR2(35), + addr3 VARCHAR2(35), + addr4 VARCHAR2(35), + workphone VARCHAR2(20), + homephone VARCHAR2(20), + startdate DATE DEFAULT SYSDATE, + enddate DATE, + notes VARCHAR2(4000), + role VARCHAR2(30) +); +-- +CREATE TABLE shipto ( + trans_id INTEGER, + shiptoname VARCHAR2(35), + shiptoaddr1 VARCHAR2(35), + shiptoaddr2 VARCHAR2(35), + shiptoaddr3 VARCHAR2(35), + shiptoaddr4 VARCHAR2(35), + shiptocontact VARCHAR2(35), + shiptophone VARCHAR2(20), + shiptofax VARCHAR2(20), + shiptoemail VARCHAR2(50) +); +-- +CREATE TABLE project ( + id INTEGER, + projectnumber VARCHAR2(50), + description VARCHAR2(4000) +); +-- +CREATE TABLE partsgroup ( + id INTEGER, + partsgroup VARCHAR2(100) +); +-- +CREATE TABLE status ( + trans_id INTEGER, + formname VARCHAR2(30), + printed CHAR(1) DEFAULT 0, + emailed CHAR(1) DEFAULT 0, + spoolfile VARCHAR2(20), + chart_id INTEGER +); +-- +CREATE TABLE department ( + id INTEGER, + description VARCHAR2(100), + role CHAR(1) DEFAULT 'P' +); +-- +-- functions +-- +CREATE OR REPLACE FUNCTION current_date RETURN date AS +BEGIN + return(sysdate);-- +END;; +-- +-- triggers +-- +CREATE OR REPLACE TRIGGER glid BEFORE INSERT ON gl FOR EACH ROW +BEGIN + SELECT id.nextval + INTO :new.id + FROM DUAL;-- +END;; +-- +CREATE OR REPLACE TRIGGER chartid BEFORE INSERT ON chart FOR EACH ROW +BEGIN + SELECT id.nextval + INTO :new.id + FROM DUAL;-- +END;; +-- +CREATE OR REPLACE TRIGGER invoiceid BEFORE INSERT ON invoice FOR EACH ROW +BEGIN + SELECT invoiceid.nextval + INTO :new.id + FROM DUAL;-- +END;; +-- +CREATE OR REPLACE TRIGGER vendorid BEFORE INSERT ON vendor FOR EACH ROW +BEGIN + SELECT id.nextval + INTO :new.id + FROM DUAL;-- +END;; +-- +CREATE OR REPLACE TRIGGER customerid BEFORE INSERT ON customer FOR EACH ROW +BEGIN + SELECT id.nextval + INTO :new.id + FROM DUAL;-- +END;; +-- +CREATE OR REPLACE TRIGGER partsid BEFORE INSERT ON parts FOR EACH ROW +BEGIN + SELECT id.nextval + INTO :new.id + FROM DUAL;-- +END;; +-- +CREATE OR REPLACE TRIGGER arid BEFORE INSERT ON ar FOR EACH ROW +BEGIN + SELECT id.nextval + INTO :new.id + FROM DUAL;-- +END;; +-- +CREATE OR REPLACE TRIGGER apid BEFORE INSERT ON ap FOR EACH ROW +BEGIN + SELECT id.nextval + INTO :new.id + FROM DUAL;-- +END;; +-- +CREATE OR REPLACE TRIGGER oeid BEFORE INSERT ON oe FOR EACH ROW +BEGIN + SELECT id.nextval + INTO :new.id + FROM DUAL;-- +END;; +-- +CREATE OR REPLACE TRIGGER employeeid BEFORE INSERT ON employee FOR EACH ROW +BEGIN + SELECT id.nextval + INTO :new.id + FROM DUAL;-- +END;; +-- +CREATE OR REPLACE TRIGGER projectid BEFORE INSERT ON project FOR EACH ROW +BEGIN + SELECT id.nextval + INTO :new.id + FROM DUAL;-- +END;; +-- +CREATE OR REPLACE TRIGGER partsgroupid BEFORE INSERT ON partsgroup FOR EACH ROW +BEGIN + SELECT id.nextval + INTO :new.id +FROM DUAL;-- +END;; +-- |