sql-ledger/old/sql-ledger/sql/Oracle-tables.sql

   1 -- Oracle-tables.sql
   2 -- Paulo Rodrigues: added functions and triggers, Oct. 31, 2001
   3 --
   4 -- Modified for use with SL 2.0 and Oracle 9i2, Dec 13, 2002
   5 --
   6 ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
   7 --
   8 CREATE SEQUENCE id START WITH 10000 INCREMENT BY 1 MAXVALUE 2147483647 MINVALUE 1  CACHE 2;
   9 SELECT ID.NEXTVAL FROM DUAL;
  10 --
  11 CREATE TABLE makemodel (
  12   parts_id INTEGER,
  13   name VARCHAR2(100)
  14 );
  15 --
  16 CREATE TABLE gl (
  17   id INTEGER,
  18   reference VARCHAR2(50),
  19   description VARCHAR2(100),
  20   transdate DATE DEFAULT SYSDATE,
  21   employee_id INTEGER,
  22   notes VARCHAR2(4000)
  23 );
  24 --
  25 CREATE TABLE chart (
  26   id INTEGER,
  27   accno VARCHAR2(20) NOT NULL,
  28   description VARCHAR2(100),
  29   charttype CHAR(1) DEFAULT 'A',
  30   category CHAR(1),
  31   link VARCHAR2(100),
  32   gifi_accno VARCHAR2(20)
  33 );
  34 --
  35 CREATE TABLE gifi (
  36   accno VARCHAR2(20),
  37   description VARCHAR2(100)
  38 );
  39 --
  40 CREATE TABLE defaults (
  41   inventory_accno_id INTEGER,
  42   income_accno_id INTEGER,
  43   expense_accno_id INTEGER,
  44   fxgain_accno_id INTEGER,
  45   fxloss_accno_id INTEGER,
  46   invnumber VARCHAR2(30),
  47   sonumber VARCHAR2(30),
  48   yearend VARCHAR2(5),
  49   weightunit VARCHAR2(5),
  50   businessnumber VARCHAR2(30),
  51   version VARCHAR2(8),
  52   curr VARCHAR2(500),
  53   closedto DATE,
  54   revtrans CHAR(1) DEFAULT '0',
  55   ponumber VARCHAR2(30)
  56 );
  57 INSERT INTO defaults (version) VALUES ('2.0.8');
  58 --
  59 CREATE TABLE acc_trans (
  60   trans_id INTEGER,
  61   chart_id INTEGER,
  62   amount FLOAT,
  63   transdate DATE DEFAULT SYSDATE,
  64   source VARCHAR2(20),
  65   cleared CHAR(1) DEFAULT '0',
  66   fx_transaction CHAR(1) DEFAULT '0',
  67   project_id INTEGER
  68 );
  69 --
  70 CREATE TABLE invoice (
  71   id INTEGER,
  72   trans_id INTEGER,
  73   parts_id INTEGER,
  74   description VARCHAR2(4000),
  75   qty FLOAT,
  76   allocated FLOAT,
  77   sellprice FLOAT,
  78   fxsellprice FLOAT,
  79   discount FLOAT,
  80   assemblyitem CHAR(1) DEFAULT '0',
  81   unit VARCHAR2(5),
  82   project_id INTEGER,
  83   deliverydate DATE
  84 );
  85 --
  86 CREATE TABLE vendor (
  87   id INTEGER,
  88   name VARCHAR2(35),
  89   addr1 VARCHAR2(35),
  90   addr2 VARCHAR2(35),
  91   addr3 VARCHAR2(35),
  92   addr4 VARCHAR2(35),
  93   contact VARCHAR2(35),
  94   phone VARCHAR2(20),
  95   fax VARCHAR2(20),
  96   email VARCHAR2(50),
  97   notes VARCHAR2(4000),
  98   terms INTEGER DEFAULT 0,
  99   taxincluded CHAR(1),
 100   vendornumber VARCHAR2(40),
 101   cc VARCHAR2(50),
 102   bcc VARCHAR2(50)
 103 );
 104 --
 105 CREATE TABLE customer (
 106   id INTEGER,
 107   name VARCHAR2(35),
 108   addr1 VARCHAR2(35),
 109   addr2 VARCHAR2(35),
 110   addr3 VARCHAR2(35),
 111   addr4 VARCHAR2(35),
 112   contact VARCHAR2(35),
 113   phone VARCHAR2(20),
 114   fax VARCHAR2(20),
 115   email VARCHAR2(50),
 116   notes VARCHAR2(4000),
 117   discount FLOAT,
 118   taxincluded CHAR(1),
 119   creditlimit FLOAT,
 120   terms INTEGER DEFAULT 0,
 121   customernumber VARCHAR2(40),
 122   cc VARCHAR2(50),
 123   bcc VARCHAR2(50)
 124 );
 125 --
 126 CREATE TABLE parts (
 127   id INTEGER,
 128   partnumber VARCHAR2(30),
 129   description VARCHAR2(4000),
 130   unit VARCHAR2(5),
 131   listprice FLOAT,
 132   sellprice FLOAT,
 133   lastcost FLOAT,
 134   priceupdate DATE DEFAULT SYSDATE,
 135   weight FLOAT,
 136   onhand FLOAT DEFAULT 0,
 137   notes VARCHAR2(4000),
 138   makemodel CHAR(1) DEFAULT '0',
 139   assembly CHAR(1) DEFAULT '0',
 140   alternate CHAR(1) DEFAULT '0',
 141   rop FLOAT,
 142   inventory_accno_id INTEGER,
 143   income_accno_id INTEGER,
 144   expense_accno_id INTEGER,
 145   bin VARCHAR2(20),
 146   obsolete CHAR(1) DEFAULT '0',
 147   bom CHAR(1) DEFAULT '0',
 148   image VARCHAR2(100),
 149   drawing VARCHAR2(100),
 150   microfiche VARCHAR2(100),
 151   partsgroup_id INTEGER
 152 );
 153 --
 154 CREATE TABLE assembly (
 155   id INTEGER,
 156   parts_id INTEGER,
 157   qty FLOAT,
 158   bom CHAR(1)
 159 );
 160 --
 161 CREATE TABLE ar (
 162   id INTEGER,
 163   invnumber VARCHAR2(30),
 164   transdate DATE DEFAULT SYSDATE,
 165   customer_id INTEGER,
 166   taxincluded CHAR(1),
 167   amount FLOAT,
 168   netamount FLOAT,
 169   paid FLOAT,
 170   datepaid DATE,
 171   duedate DATE,
 172   invoice CHAR(1) DEFAULT '0',
 173   shippingpoint VARCHAR2(100),
 174   terms INTEGER DEFAULT 0,
 175   notes VARCHAR2(4000),
 176   curr CHAR(3),
 177   ordnumber VARCHAR2(30),
 178   employee_id INTEGER
 179 );
 180 --
 181 CREATE TABLE ap (
 182   id INTEGER,
 183   invnumber VARCHAR2(30),
 184   transdate DATE DEFAULT SYSDATE,
 185   vendor_id INTEGER,
 186   taxincluded CHAR(1) DEFAULT '0',
 187   amount FLOAT,
 188   netamount FLOAT,
 189   paid FLOAT,
 190   datepaid DATE,
 191   duedate DATE,
 192   invoice CHAR(1) DEFAULT '0',
 193   ordnumber VARCHAR2(30),
 194   curr CHAR(3),
 195   notes VARCHAR2(4000),
 196   employee_id INTEGER
 197 );
 198 --
 199 CREATE TABLE partstax (
 200   parts_id INTEGER,
 201   chart_id INTEGER
 202 );
 203 --
 204 CREATE TABLE tax (
 205   chart_id INTEGER,
 206   rate FLOAT,
 207   taxnumber VARCHAR2(30)
 208 );
 209 --
 210 CREATE TABLE customertax (
 211   customer_id INTEGER,
 212   chart_id INTEGER
 213 );
 214 --
 215 CREATE TABLE vendortax (
 216   vendor_id INTEGER,
 217   chart_id INTEGER
 218 );
 219 --
 220 CREATE TABLE oe (
 221   id INTEGER,
 222   ordnumber VARCHAR2(30),
 223   transdate DATE DEFAULT SYSDATE,
 224   vendor_id INTEGER,
 225   customer_id INTEGER,
 226   amount FLOAT,
 227   netamount FLOAT,
 228   reqdate DATE,
 229   taxincluded CHAR(1),
 230   shippingpoint VARCHAR2(100),
 231   notes VARCHAR2(4000),
 232   curr CHAR(3),
 233   employee_id INTEGER,
 234   closed CHAR(1) DEFAULT '0'
 235 );
 236 --
 237 CREATE TABLE orderitems (
 238   trans_id INTEGER,
 239   parts_id INTEGER,
 240   description VARCHAR2(4000),
 241   qty FLOAT,
 242   sellprice FLOAT,
 243   discount FLOAT,
 244   unit VARCHAR2(5),
 245   project_id INTEGER,
 246   reqdate DATE
 247 );
 248 --
 249 CREATE TABLE exchangerate (
 250   curr CHAR(3),
 251   transdate DATE,
 252   buy FLOAT,
 253   sell FLOAT
 254 );
 255 --
 256 CREATE TABLE employee (
 257   id INTEGER,
 258   login VARCHAR2(20),
 259   name VARCHAR2(35),
 260   addr1 VARCHAR2(35),
 261   addr2 VARCHAR2(35),
 262   addr3 VARCHAR2(35),
 263   addr4 VARCHAR2(35),
 264   workphone VARCHAR2(20),
 265   homephone VARCHAR2(20),
 266   startdate DATE DEFAULT SYSDATE,
 267   enddate DATE,
 268   notes VARCHAR2(4000)
 269 );
 270 --
 271 CREATE TABLE shipto (
 272   trans_id INTEGER,
 273   shiptoname VARCHAR2(35),
 274   shiptoaddr1 VARCHAR2(35),
 275   shiptoaddr2 VARCHAR2(35),
 276   shiptoaddr3 VARCHAR2(35),
 277   shiptoaddr4 VARCHAR2(35),
 278   shiptocontact VARCHAR2(35),
 279   shiptophone VARCHAR2(20),
 280   shiptofax VARCHAR2(20),
 281   shiptoemail VARCHAR2(50)
 282 );
 283 --
 284 CREATE TABLE project (
 285   id INTEGER,
 286   projectnumber VARCHAR2(50),
 287   description VARCHAR2(4000)
 288 );
 289 --
 290 CREATE TABLE partsgroup (
 291   id INTEGER,
 292   partsgroup VARCHAR2(100)
 293 );
 294 --
 295 -- functions
 296 --
 297 CREATE OR REPLACE FUNCTION current_date RETURN date AS
 298 BEGIN
 299   return(sysdate);--
 300 END;;
 301 --
 302 -- triggers
 303 --
 304 CREATE OR REPLACE TRIGGER glid BEFORE INSERT ON gl FOR EACH ROW
 305 BEGIN
 306   SELECT id.nextval
 307   INTO :new.id
 308   FROM DUAL;--
 309 END;;
 310 --
 311 CREATE OR REPLACE TRIGGER chartid BEFORE INSERT ON chart FOR EACH ROW
 312 BEGIN
 313   SELECT id.nextval
 314   INTO :new.id
 315   FROM DUAL;--
 316 END;;
 317 --
 318 CREATE OR REPLACE TRIGGER invoiceid BEFORE INSERT ON invoice FOR EACH ROW
 319 BEGIN
 320   SELECT id.nextval
 321   INTO :new.id
 322   FROM DUAL;--
 323 END;;
 324 --
 325 CREATE OR REPLACE TRIGGER vendorid BEFORE INSERT ON vendor FOR EACH ROW
 326 BEGIN
 327   SELECT id.nextval
 328   INTO :new.id
 329   FROM DUAL;--
 330 END;;
 331 --
 332 CREATE OR REPLACE TRIGGER customerid BEFORE INSERT ON customer FOR EACH ROW
 333 BEGIN
 334   SELECT id.nextval
 335   INTO :new.id
 336   FROM DUAL;--
 337 END;;
 338 --
 339 CREATE OR REPLACE TRIGGER partsid BEFORE INSERT ON parts FOR EACH ROW
 340 BEGIN
 341   SELECT id.nextval
 342   INTO :new.id
 343   FROM DUAL;--
 344 END;;
 345 --
 346 CREATE OR REPLACE TRIGGER arid BEFORE INSERT ON ar FOR EACH ROW
 347 BEGIN
 348   SELECT id.nextval
 349   INTO :new.id
 350   FROM DUAL;--
 351 END;;
 352 --
 353 CREATE OR REPLACE TRIGGER apid BEFORE INSERT ON ap FOR EACH ROW
 354 BEGIN
 355   SELECT id.nextval
 356   INTO :new.id
 357   FROM DUAL;--
 358 END;;
 359 --
 360 CREATE OR REPLACE TRIGGER oeid BEFORE INSERT ON oe FOR EACH ROW
 361 BEGIN
 362   SELECT id.nextval
 363   INTO :new.id
 364   FROM DUAL;--
 365 END;;
 366 --
 367 CREATE OR REPLACE TRIGGER employeeid BEFORE INSERT ON employee FOR EACH ROW
 368 BEGIN
 369   SELECT id.nextval
 370   INTO :new.id
 371   FROM DUAL;--
 372 END;;
 373 --
 374 CREATE OR REPLACE TRIGGER projectid BEFORE INSERT ON project FOR EACH ROW
 375 BEGIN
 376   SELECT id.nextval
 377   INTO :new.id
 378   FROM DUAL;--
 379 END;;
 380 --
 381 CREATE OR REPLACE TRIGGER partsgroupid BEFORE INSERT ON partsgroup FOR EACH ROW
 382 BEGIN
 383   SELECT id.nextval
 384   INTO :new.id
 385   FROM DUAL;--
 386 END;;
 387 --
 388