--- /dev/null
+-- 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;;
+--
projects / freeside.git / blobdiff