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