1 -- function check_department
2 create function check_department() returns opaque as '
9 if new.department_id = 0 then
10 delete from dpt_trans where trans_id = new.id;
14 select into dpt_id trans_id from dpt_trans where trans_id = new.id;
17 update dpt_trans set department_id = new.department_id where trans_id = dpt_id;
19 insert into dpt_trans (trans_id, department_id) values (new.id, new.department_id);
27 -- department transaction table
28 create table dpt_trans (trans_id int, department_id int);
30 -- function del_department
31 create function del_department() returns opaque as '
33 delete from dpt_trans where trans_id = old.id;
41 create trigger check_department after insert or update on ar for each row execute procedure check_department();
43 create trigger check_department after insert or update on ap for each row execute procedure check_department();
45 create trigger check_department after insert or update on gl for each row execute procedure check_department();
47 create trigger check_department after insert or update on oe for each row execute procedure check_department();
51 create trigger del_department after delete on ar for each row execute procedure del_department();
53 create trigger del_department after delete on ap for each row execute procedure del_department();
55 create trigger del_department after delete on gl for each row execute procedure del_department();
57 create trigger del_department after delete on oe for each row execute procedure del_department();
62 create table business (id int default nextval('id'), description text, discount float4);
65 create table sic (code text, sictype char(1), description text);
67 alter table vendor add column gifi_accno text;
68 alter table vendor add column business_id int;
69 alter table vendor add column taxnumber text;
70 alter table vendor add column sic_code text;
72 alter table customer add column business_id int;
73 alter table customer add column taxnumber text;
74 alter table customer add column sic_code text;
76 create function del_customer() returns opaque as '
78 delete from shipto where trans_id = old.id;
79 delete from customertax where customer_id = old.id;
85 create function del_vendor() returns opaque as '
87 delete from shipto where trans_id = old.id;
88 delete from vendortax where vendor_id = old.id;
94 create trigger del_customer after delete on customer for each row execute procedure del_customer();
96 create trigger del_vendor after delete on vendor for each row execute procedure del_vendor();
99 alter table acc_trans add column memo text;
101 alter table employee add column sales bool;
102 alter table employee alter column sales set default 't';
104 alter table vendor add discount float4;
105 alter table vendor add creditlimit float;
107 -- function del_exchangerate
108 create function del_exchangerate() returns opaque as '
118 select into d_curr substr(curr,1,3) from defaults;
120 if TG_RELNAME = ''ar'' then
121 select into t_curr, t_transdate curr, transdate from ar where id = old.id;
123 if TG_RELNAME = ''ap'' then
124 select into t_curr, t_transdate curr, transdate from ap where id = old.id;
126 if TG_RELNAME = ''oe'' then
127 select into t_curr, t_transdate curr, transdate from oe where id = old.id;
130 if d_curr != t_curr then
132 select into t_id a.id from acc_trans ac
133 join ar a on (a.id = ac.trans_id)
134 where a.curr = t_curr
135 and ac.transdate = t_transdate
137 except select a.id from ar a where a.id = old.id
141 select a.id from acc_trans ac
142 join ap a on (a.id = ac.trans_id)
143 where a.curr = t_curr
144 and ac.transdate = t_transdate
146 except select a.id from ap a where a.id = old.id
150 select o.id from oe o
151 where o.curr = t_curr
152 and o.transdate = t_transdate
154 except select o.id from oe o where o.id = old.id;
157 delete from exchangerate where curr = t_curr and transdate = t_transdate;
163 ' language 'plpgsql';
168 create trigger del_exchangerate before delete on ar for each row execute procedure del_exchangerate();
171 create trigger del_exchangerate before delete on ap for each row execute procedure del_exchangerate();
174 create trigger del_exchangerate before delete on oe for each row execute procedure del_exchangerate();
178 alter table orderitems add ship float4;
179 alter table orderitems add serialnumber text;
182 create sequence orderitemsid maxvalue 100000 cycle;
183 alter table orderitems add id int;
184 alter table orderitems alter id set default nextval('orderitemsid');
186 create table warehouse (id int default nextval('id'), description text);
188 create table inventory (warehouse_id int, parts_id int, oe_id int, orderitems_id int, qty float4, shippingdate date);
190 -- update orderitems, fill in id
191 create table temp (id int default nextval('orderitemsid'), tempid oid);
192 insert into temp (tempid) select oid from orderitems;
193 update orderitems set id = temp.id from temp where orderitems.oid = temp.tempid;
196 create index orderitems_id_key on orderitems (id);
198 alter table ar add shipvia text;
199 alter table ap add shipvia text;
200 alter table oe add shipvia text;
203 alter table inventory add employee_id int;
206 create function check_inventory() returns opaque as '
210 row_data inventory%rowtype;
214 if not old.quotation then
215 for row_data in select * from inventory where oe_id = old.id loop
216 select into itemid id from orderitems where trans_id = old.id and id = row_data.orderitems_id;
218 if itemid is null then
219 delete from inventory where oe_id = old.id and orderitems_id = row_data.orderitems_id;
225 ' language 'plpgsql';
228 create trigger check_inventory after update on oe for each row execute procedure check_inventory();
232 create table yearend (
237 -- function del_yearend
238 create function del_yearend() returns opaque as '
240 delete from yearend where trans_id = old.id;
243 ' language 'plpgsql';
248 create trigger del_yearend after delete on gl for each row execute procedure del_yearend();
253 id int default nextval('id'),
266 creditlimit float default 0,
267 terms int2 default 0,
268 customernumber varchar(64),
272 taxnumber varchar(64),
277 insert into temp (id, name, addr1, addr2, addr3, addr4, contact, phone, fax, email, notes, discount, taxincluded, creditlimit, terms, customernumber, cc, bcc, business_id, taxnumber, sic_code) select id, name, addr1, addr2, addr3, addr4, contact, phone, fax, email, notes, discount, taxincluded, creditlimit, terms, customernumber, cc, bcc, business_id, taxnumber, sic_code from customer;
281 alter table temp rename to customer;
283 create index customer_id_key on customer (id);
284 create index customer_customernumber_key on customer (customernumber);
285 create index customer_name_key on customer (name);
286 create index customer_contact_key on customer (contact);
288 create trigger del_customer after delete on customer for each row execute procedure del_customer();
292 id int default nextval('id'),
303 terms int2 default 0,
305 vendornumber varchar(64),
308 gifi_accno varchar(30),
310 taxnumber varchar(64),
313 creditlimit float default 0,
317 insert into temp (id, name, addr1, addr2, addr3, addr4, contact, phone, fax, email, notes, discount, taxincluded, creditlimit, terms, vendornumber, cc, bcc, business_id, taxnumber, sic_code) select id, name, addr1, addr2, addr3, addr4, contact, phone, fax, email, notes, discount, taxincluded, creditlimit, terms, vendornumber, cc, bcc, business_id, taxnumber, sic_code from vendor;
321 alter table temp rename to vendor;
323 create index vendor_id_key on vendor (id);
324 create index vendor_name_key on vendor (name);
325 create index vendor_vendornumber_key on vendor (vendornumber);
326 create index vendor_contact_key on vendor (contact);
328 create trigger del_vendor after delete on vendor for each row execute procedure del_vendor();
336 insert into temp (code, sictype, description) select code, sictype, description from sic;
338 alter table temp rename to sic;
342 shiptoname varchar(64),
343 shiptoaddr1 varchar(64),
344 shiptoaddr2 varchar(64),
345 shiptoaddr3 varchar(64),
346 shiptoaddr4 varchar(64),
347 shiptocontact varchar(64),
348 shiptophone varchar(20),
349 shiptofax varchar(20),
352 insert into temp (trans_id, shiptoname, shiptoaddr1, shiptoaddr2, shiptoaddr3, shiptoaddr4, shiptocontact, shiptophone, shiptofax, shiptoemail) select trans_id, shiptoname, shiptoaddr1, shiptoaddr2, shiptoaddr3, shiptoaddr4, shiptocontact, shiptophone, shiptofax, shiptoemail from shipto;
354 alter table temp rename to shipto;
355 create index shipto_trans_id_key on shipto (trans_id);
359 id int default nextval('id'),
366 workphone varchar(20),
367 homephone varchar(20),
368 startdate date default current_date,
378 insert into temp (id,login,name,addr1,addr2,addr3,addr4,workphone,homephone,startdate,enddate,notes,role,sales) select id,login,name,addr1,addr2,addr3,addr4,workphone,homephone,startdate,enddate,notes,role,sales from employee;
381 alter table temp rename to employee;
383 create index employee_id_key on employee (id);
384 create unique index employee_login_key on employee (login);
385 create index employee_name_key on employee (name);
387 update defaults set version = '2.3.1';