1 files changed, 388 insertions, 0 deletions
diff --git a/sql-ledger/sql/Oracle-tables.sql b/sql-ledger/sql/Oracle-tables.sql
new file mode 100644
index 0000000..d2bf7c7
--- /dev/null
+++ b/sql-ledger/sql/Oracle-tables.sql
@@ -0,0 +1,388 @@
+-- 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
+--
+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 TABLE makemodel (
+  parts_id INTEGER,
+  name VARCHAR2(100)
+);
+--
+CREATE TABLE gl (
+  id INTEGER,
+  reference VARCHAR2(50),
+  description VARCHAR2(100),
+  transdate DATE DEFAULT SYSDATE,
+  employee_id INTEGER,
+  notes VARCHAR2(4000) 
+);
+--
+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)
+);
+INSERT INTO defaults (version) VALUES ('2.0.8');
+--
+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
+);
+--
+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
+);
+--
+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
+);
+--
+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'
+);
+--
+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)
+);
+--
+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)
+);
+--
+-- 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 id.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;;
+--
+