2 -- Paulo Rodrigues: added functions and triggers, Oct. 31, 2001
4 -- Modified for use with SL 2.0 and Oracle 9i2, Dec 13, 2002
5 -- Updated to 2.3.0, Dec 18, 2003
7 ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
9 CREATE SEQUENCE id START WITH 10000 INCREMENT BY 1 MAXVALUE 2147483647 MINVALUE 1 CACHE 2;
10 SELECT ID.NEXTVAL FROM DUAL;
12 CREATE SEQUENCE invoiceid START WITH 1 INCREMENT BY 1 MAXVALUE 2147483647 MINVALUE 1 CACHE 2;
13 SELECT INVOICEID.NEXTVAL FROM DUAL;
15 CREATE TABLE makemodel (
23 reference VARCHAR2(50),
24 description VARCHAR2(100),
25 transdate DATE DEFAULT SYSDATE,
28 department_id INTEGER DEFAULT 0
33 accno VARCHAR2(20) NOT NULL,
34 description VARCHAR2(100),
35 charttype CHAR(1) DEFAULT 'A',
38 gifi_accno VARCHAR2(20)
43 description VARCHAR2(100)
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),
55 weightunit VARCHAR2(5),
56 businessnumber VARCHAR2(30),
60 revtrans CHAR(1) DEFAULT '0',
61 ponumber VARCHAR2(30),
62 sqnumber VARCHAR2(30),
63 rfqnumber VARCHAR2(30)
65 INSERT INTO defaults (version) VALUES ('2.3.0');
67 CREATE TABLE acc_trans (
71 transdate DATE DEFAULT SYSDATE,
73 cleared CHAR(1) DEFAULT '0',
74 fx_transaction CHAR(1) DEFAULT '0',
78 CREATE TABLE invoice (
82 description VARCHAR2(4000),
88 assemblyitem CHAR(1) DEFAULT '0',
92 serialnumber VARCHAR2(200)
102 contact VARCHAR2(35),
106 notes VARCHAR2(4000),
107 terms INTEGER DEFAULT 0,
109 vendornumber VARCHAR2(40),
114 CREATE TABLE customer (
121 contact VARCHAR2(35),
125 notes VARCHAR2(4000),
129 terms INTEGER DEFAULT 0,
130 customernumber VARCHAR2(40),
137 partnumber VARCHAR2(30),
138 description VARCHAR2(4000),
143 priceupdate DATE DEFAULT SYSDATE,
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',
151 inventory_accno_id INTEGER,
152 income_accno_id INTEGER,
153 expense_accno_id INTEGER,
155 obsolete CHAR(1) DEFAULT '0',
156 bom CHAR(1) DEFAULT '0',
158 drawing VARCHAR2(100),
159 microfiche VARCHAR2(100),
160 partsgroup_id INTEGER
163 CREATE TABLE assembly (
172 invnumber VARCHAR2(30),
173 transdate DATE DEFAULT SYSDATE,
181 invoice CHAR(1) DEFAULT '0',
182 shippingpoint VARCHAR2(100),
183 terms INTEGER DEFAULT 0,
184 notes VARCHAR2(4000),
186 ordnumber VARCHAR2(30),
189 quonumber VARCHAR2(30),
190 intnotes VARCHAR2(4000),
191 department_id INTEGER DEFAULT 0
196 invnumber VARCHAR2(30),
197 transdate DATE DEFAULT SYSDATE,
199 taxincluded CHAR(1) DEFAULT '0',
205 invoice CHAR(1) DEFAULT '0',
206 ordnumber VARCHAR2(30),
208 notes VARCHAR2(4000),
211 quonumber VARCHAR2(30),
212 intnotes VARCHAR2(4000),
213 department_id INTEGER DEFAULT 0
216 CREATE TABLE partstax (
224 taxnumber VARCHAR2(30)
227 CREATE TABLE customertax (
232 CREATE TABLE vendortax (
239 ordnumber VARCHAR2(30),
240 transdate DATE DEFAULT SYSDATE,
247 shippingpoint VARCHAR2(100),
248 notes VARCHAR2(4000),
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
258 CREATE TABLE orderitems (
261 description VARCHAR2(4000),
270 CREATE TABLE exchangerate (
277 CREATE TABLE employee (
285 workphone VARCHAR2(20),
286 homephone VARCHAR2(20),
287 startdate DATE DEFAULT SYSDATE,
289 notes VARCHAR2(4000),
293 CREATE TABLE shipto (
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)
306 CREATE TABLE project (
308 projectnumber VARCHAR2(50),
309 description VARCHAR2(4000)
312 CREATE TABLE partsgroup (
314 partsgroup VARCHAR2(100)
317 CREATE TABLE status (
319 formname VARCHAR2(30),
320 printed CHAR(1) DEFAULT 0,
321 emailed CHAR(1) DEFAULT 0,
322 spoolfile VARCHAR2(20),
326 CREATE TABLE department (
328 description VARCHAR2(100),
329 role CHAR(1) DEFAULT 'P'
334 CREATE OR REPLACE FUNCTION current_date RETURN date AS
341 CREATE OR REPLACE TRIGGER glid BEFORE INSERT ON gl FOR EACH ROW
348 CREATE OR REPLACE TRIGGER chartid BEFORE INSERT ON chart FOR EACH ROW
355 CREATE OR REPLACE TRIGGER invoiceid BEFORE INSERT ON invoice FOR EACH ROW
357 SELECT invoiceid.nextval
362 CREATE OR REPLACE TRIGGER vendorid BEFORE INSERT ON vendor FOR EACH ROW
369 CREATE OR REPLACE TRIGGER customerid BEFORE INSERT ON customer FOR EACH ROW
376 CREATE OR REPLACE TRIGGER partsid BEFORE INSERT ON parts FOR EACH ROW
383 CREATE OR REPLACE TRIGGER arid BEFORE INSERT ON ar FOR EACH ROW
390 CREATE OR REPLACE TRIGGER apid BEFORE INSERT ON ap FOR EACH ROW
397 CREATE OR REPLACE TRIGGER oeid BEFORE INSERT ON oe FOR EACH ROW
404 CREATE OR REPLACE TRIGGER employeeid BEFORE INSERT ON employee FOR EACH ROW
411 CREATE OR REPLACE TRIGGER projectid BEFORE INSERT ON project FOR EACH ROW
418 CREATE OR REPLACE TRIGGER partsgroupid BEFORE INSERT ON partsgroup FOR EACH ROW