X-Git-Url: http://git.freeside.biz/gitweb/?p=freeside.git;a=blobdiff_plain;f=sql-ledger%2Fsql%2FPg-tables.sql;h=2b7cb92ad6c3ee6bd39e7de22801d4c72110052a;hp=aed9787247b2330c954a14ecb137b0a37991b856;hb=948b8acdd4b9b3864342062d0c397a11f57c5700;hpb=eb9668a6f3181ee02cb335272c5ee4616e61fd09

diff --git a/sql-ledger/sql/Pg-tables.sql b/sql-ledger/sql/Pg-tables.sql
index aed978724..2b7cb92ad 100644
--- a/sql-ledger/sql/Pg-tables.sql
+++ b/sql-ledger/sql/Pg-tables.sql
@@ -1,11 +1,17 @@
-CREATE SEQUENCE id
-  start 10000;
 --
+CREATE SEQUENCE id start 10000;
 SELECT nextval ('id');
 --
+CREATE SEQUENCE invoiceid;
+SELECT nextval ('invoiceid');
+--
+CREATE SEQUENCE orderitemsid MAXVALUE 100000 CYCLE;
+SELECT nextval ('orderitemsid');
+--
 CREATE TABLE makemodel (
   parts_id int,
-  name text
+  make text,
+  model text
 );
 --
 CREATE TABLE gl (
@@ -14,7 +20,8 @@ CREATE TABLE gl (
   description text,
   transdate date DEFAULT current_date,
   employee_id int,
-  notes text
+  notes text,
+  department_id int default 0
 );
 --
 CREATE TABLE chart (
@@ -38,7 +45,7 @@ CREATE TABLE defaults (
   expense_accno_id int,
   fxgain_accno_id int,
   fxloss_accno_id int,
-  invnumber text,
+  sinumber text,
   sonumber text,
   yearend varchar(5),
   weightunit varchar(5),
@@ -47,9 +54,17 @@ CREATE TABLE defaults (
   curr text,
   closedto date,
   revtrans bool DEFAULT 'f',
-  ponumber text
+  ponumber text,
+  sqnumber text,
+  rfqnumber text,
+  audittrail bool default 'f',
+  vinumber text,
+  employeenumber text,
+  partnumber text,
+  customernumber text,
+  vendornumber text
 );
-INSERT INTO defaults (version) VALUES ('2.0.8');
+INSERT INTO defaults (version) VALUES ('2.4.3');
 --
 CREATE TABLE acc_trans (
   trans_id int,
@@ -59,11 +74,12 @@ CREATE TABLE acc_trans (
   source text,
   cleared bool DEFAULT 'f',
   fx_transaction bool DEFAULT 'f',
-  project_id int
+  project_id int,
+  memo text
 );
 --
 CREATE TABLE invoice (
-  id int DEFAULT nextval ( 'id' ),
+  id int DEFAULT nextval ( 'invoiceid' ),
   trans_id int,
   parts_id int,
   description text,
@@ -75,49 +91,45 @@ CREATE TABLE invoice (
   assemblyitem bool DEFAULT 'f',
   unit varchar(5),
   project_id int,
-  deliverydate date
-);
---
-CREATE TABLE vendor (
-  id int DEFAULT nextval ( 'id' ),
-  name varchar(35),
-  addr1 varchar(35),
-  addr2 varchar(35),
-  addr3 varchar(35),
-  addr4 varchar(35),
-  contact varchar(35),
-  phone varchar(20),
-  fax varchar(20),
-  email text,
-  notes text,
-  terms int2 DEFAULT 0,
-  taxincluded bool,
-  vendornumber text,
-  cc text,
-  bcc text
+  deliverydate date,
+  serialnumber text
 );
 --
 CREATE TABLE customer (
-  id int DEFAULT nextval ( 'id' ),
-  name varchar(35),
-  addr1 varchar(35),
-  addr2 varchar(35),
-  addr3 varchar(35),
-  addr4 varchar(35),
-  contact varchar(35),
+  id int default nextval('id'),
+  name varchar(64),
+  address1 varchar(32),
+  address2 varchar(32),
+  city varchar(32),
+  state varchar(32),
+  zipcode varchar(10),
+  country varchar(32),
+  contact varchar(64),
   phone varchar(20),
   fax varchar(20),
   email text,
   notes text,
   discount float4,
-  taxincluded bool,
-  creditlimit float DEFAULT 0,
-  terms int2 DEFAULT 0,
-  customernumber text,
+  taxincluded bool default 'f',
+  creditlimit float default 0,
+  terms int2 default 0,
+  customernumber varchar(32),
   cc text,
-  bcc text
+  bcc text,
+  business_id int,
+  taxnumber varchar(32),
+  sic_code varchar(6),
+  iban varchar(34),
+  bic varchar(11),
+  employee_id int,
+  language_code varchar(6),
+  pricegroup_id int,
+  curr char(3),
+  startdate date,
+  enddate date
 );
 --
+--
 CREATE TABLE parts (
   id int DEFAULT nextval ( 'id' ),
   partnumber text,
@@ -150,7 +162,8 @@ CREATE TABLE assembly (
   id int,
   parts_id int,
   qty float,
-  bom bool
+  bom bool,
+  adj bool
 );
 --
 CREATE TABLE ar (
@@ -170,7 +183,13 @@ CREATE TABLE ar (
   notes text,
   curr char(3),
   ordnumber text,
-  employee_id int
+  employee_id int,
+  till varchar(20),
+  quonumber text,
+  intnotes text,
+  department_id int default 0,
+  shipvia text,
+  language_code varchar(6)
 );
 --
 CREATE TABLE ap (
@@ -188,7 +207,13 @@ CREATE TABLE ap (
   ordnumber text,
   curr char(3),
   notes text,
-  employee_id int
+  employee_id int,
+  till varchar(20),
+  quonumber text,
+  intnotes text,
+  department_id int default 0,
+  shipvia text,
+  language_code varchar(6)
 );
 --
 CREATE TABLE partstax (
@@ -226,7 +251,13 @@ CREATE TABLE oe (
   notes text,
   curr char(3),
   employee_id int,
-  closed bool default 'f'
+  closed bool default 'f',
+  quotation bool default 'f',
+  quonumber text,
+  intnotes text,
+  department_id int default 0,
+  shipvia text,
+  language_code varchar(6)
 );
 --
 CREATE TABLE orderitems (
@@ -238,7 +269,10 @@ CREATE TABLE orderitems (
   discount float4,
   unit varchar(5),
   project_id int,
-  reqdate date
+  reqdate date,
+  ship float4,
+  serialnumber text,
+  id int default nextval('orderitemsid')
 );
 --
 CREATE TABLE exchangerate (
@@ -248,42 +282,190 @@ CREATE TABLE exchangerate (
   sell float8
 );
 --
-CREATE TABLE employee (
-  id int DEFAULT nextval ('id'),
+create table employee (
+  id int default nextval('id'),
   login text,
-  name Varchar(35),
-  addr1 varchar(35),
-  addr2 varchar(35),
-  addr3 varchar(35),
-  addr4 varchar(35),
+  name varchar(64),
+  address1 varchar(32),
+  address2 varchar(32),
+  city varchar(32),
+  state varchar(32),
+  zipcode varchar(10),
+  country varchar(32),
   workphone varchar(20),
   homephone varchar(20),
   startdate date default current_date,
   enddate date,
-  notes text
+  notes text,
+  role varchar(20),
+  sales bool default 'f',
+  email text,
+  sin varchar(20),
+  iban varchar(34),
+  bic varchar(11),
+  managerid int,
+  employeenumber varchar(32)
 );
 --
 create table shipto (
   trans_id int,
-  shiptoname varchar(35),
-  shiptoaddr1 varchar(35),
-  shiptoaddr2 varchar(35),
-  shiptoaddr3 varchar(35),
-  shiptoaddr4 varchar(35),
-  shiptocontact varchar(35),
+  shiptoname varchar(64),
+  shiptoaddress1 varchar(32),
+  shiptoaddress2 varchar(32),
+  shiptocity varchar(32),
+  shiptostate varchar(32),
+  shiptozipcode varchar(10),
+  shiptocountry varchar(32),
+  shiptocontact varchar(64),
   shiptophone varchar(20),
   shiptofax varchar(20),
   shiptoemail text
 );
 --
-create table project (
+CREATE TABLE vendor (
+  id int default nextval('id'),
+  name varchar(64),
+  address1 varchar(32),
+  address2 varchar(32),
+  city varchar(32),
+  state varchar(32),
+  zipcode varchar(10),
+  country varchar(32),
+  contact varchar(64),
+  phone varchar(20),
+  fax varchar(20),
+  email text,
+  notes text,
+  terms int2 default 0,
+  taxincluded bool default 'f',
+  vendornumber varchar(32),
+  cc text,
+  bcc text,
+  gifi_accno varchar(30),
+  business_id int,
+  taxnumber varchar(32),
+  sic_code varchar(6),
+  discount float4,
+  creditlimit float default 0,
+  iban varchar(34),
+  bic varchar(11),
+  employee_id int,
+  language_code varchar(6),
+  pricegroup_id int,
+  curr char(3),
+  startdate date,
+  enddate date
+);
+--
+CREATE TABLE project (
   id int default nextval('id'),
   projectnumber text,
   description text
 );
 --
-create table partsgroup (
+CREATE TABLE partsgroup (
   id int default nextval('id'),
   partsgroup text
 );
 --
+CREATE TABLE status (
+  trans_id int,
+  formname text,
+  printed bool default 'f',
+  emailed bool default 'f',
+  spoolfile text,
+  chart_id int
+);
+--
+CREATE TABLE department (
+  id int default nextval('id'),
+  description text,
+  role char(1) default 'P'
+);
+--
+-- department transaction table
+CREATE TABLE dpt_trans (
+  trans_id int,
+  department_id int
+);
+--
+-- business table
+CREATE TABLE business (
+  id int default nextval('id'),
+  description text,
+  discount float4
+);
+--
+-- SIC
+CREATE TABLE sic (
+  code varchar(6),
+  sictype char(1),
+  description text
+);
+--
+CREATE TABLE warehouse (
+  id int default nextval('id'),
+  description text
+);
+--
+CREATE TABLE inventory (
+  warehouse_id int,
+  parts_id int,
+  oe_id int,
+  orderitems_id int,
+  qty float4,
+  shippingdate date,
+  employee_id int
+);
+--
+CREATE TABLE yearend (
+  trans_id int,
+  transdate date
+);
+--
+CREATE TABLE partsvendor (
+  vendor_id int,
+  parts_id int,
+  partnumber text,
+  leadtime int2,
+  lastcost float,
+  curr char(3)
+);
+--
+CREATE TABLE pricegroup (
+  id int default nextval('id'),
+  pricegroup text
+);
+--
+CREATE TABLE partscustomer (
+  parts_id int,
+  customer_id int,
+  pricegroup_id int,
+  pricebreak float4,
+  sellprice float,
+  validfrom date,
+  validto date,
+  curr char(3)
+);
+--
+CREATE TABLE language (
+  code varchar(6),
+  description text
+);
+--
+CREATE TABLE audittrail (
+  trans_id int,
+  tablename text,
+  reference text,
+  formname text,
+  action text,
+  transdate timestamp default current_timestamp,
+  employee_id int
+);
+--
+CREATE TABLE translation (
+  trans_id int,
+  language_code varchar(6),
+  description text
+);
+--