X-Git-Url: http://wagnertech.de/gitweb/gitweb.cgi/mfinanz.git/blobdiff_plain/d319704a66e9be64da837ccea10af6774c2b0838..96b6e28fa0928216c64355cfdef53c4496bb3a31:/sql/lx-office.sql?ds=sidebyside

diff --git a/sql/lx-office.sql b/sql/lx-office.sql
index d2c0bca27..e7206536f 100644
--- a/sql/lx-office.sql
+++ b/sql/lx-office.sql
@@ -109,12 +109,23 @@ CREATE TABLE "defaults" (
 	"rfqnumber" text,
         "customernumber" text,
         "vendornumber" text,
+        "audittrail" bool default 'f',
         "articlenumber" text,
         "servicenumber" text,
         "itime" timestamp DEFAULT now(),
         "mtime" timestamp
 );
-INSERT INTO defaults (version) VALUES ('2.1.2');
+INSERT INTO defaults (version,audittrail) VALUES ('2.2.0.1', 't');
+
+CREATE TABLE audittrail (
+        "trans_id" int,
+        "tablename" text,
+        "reference" text,
+        "formname" text,
+        "action" text,
+        "transdate" timestamp default current_timestamp,
+        "employee_id" int
+);
 
 CREATE TABLE "acc_trans" (
 	"trans_id" integer,
@@ -149,7 +160,11 @@ CREATE TABLE "invoice" (
 	"deliverydate" date,
 	"serialnumber" text,
         "itime" timestamp DEFAULT now(),
-        "mtime" timestamp
+        "mtime" timestamp,
+        "pricegroup_id" integer,
+        "ordnumber" text,
+        "transdate" text,
+        "cusordnumber" text
 );
 
 CREATE TABLE "vendor" (
@@ -182,6 +197,8 @@ CREATE TABLE "vendor" (
         "bank_code" varchar(10),
         "bank" text,
         "language" varchar(5),
+        "language_id" integer,
+        "payment_id" integer,
         "datevexport" integer,
         "itime" timestamp DEFAULT now(),
         "mtime" timestamp,
@@ -222,15 +239,18 @@ CREATE TABLE "customer" (
         "bank_code" varchar(10),
         "bank" text,
         "language" varchar(5),
+        "language_id" integer,
+        "payment_id" integer,
         "datevexport" integer,
         "itime" timestamp DEFAULT now(),
         "mtime" timestamp,
         "obsolete" boolean DEFAULT 'f',
         "ustid" varchar(12),
         "username" varchar(50),
-        "user_password" varchar(12),
+        "user_password" text,
         "salesman_id" integer,
-        "c_vendor_id" text
+        "c_vendor_id" text,
+        "klass" integer DEFAULT 0
         
 );
 
@@ -269,6 +289,7 @@ CREATE TABLE "ar" (
 	"paid" numeric (15,5),
 	"datepaid" date,
 	"duedate" date,
+	"deliverydate" date,
 	"invoice" boolean DEFAULT 'f',
 	"shippingpoint" text,
 	"terms" smallint DEFAULT 0,
@@ -279,6 +300,10 @@ CREATE TABLE "ar" (
 	"quonumber" text,
         "cusordnumber" text,
 	"intnotes" text,
+        "language_id" integer,
+        "payment_id" integer,
+        "delivery_customer_id" integer,
+        "delivery_vendor_id" integer,
 	"department_id" integer DEFAULT 0,
 	"shipvia" text,
         "itime" timestamp DEFAULT now(),
@@ -305,6 +330,8 @@ CREATE TABLE "ap" (
 	"employee_id" integer,
 	"quonumber" text,
 	"intnotes" text,
+        "language_id" integer,
+        "payment_id" integer,
 	"department_id" integer DEFAULT 0,
         "itime" timestamp DEFAULT now(), 
         "mtime" timestamp,
@@ -343,6 +370,14 @@ CREATE TABLE "vendortax" (
         "mtime" timestamp
 );
 
+CREATE TABLE "translation" (
+	"parts_id" integer,
+	"language_id" integer,
+        "translation" text,
+        "itime" timestamp DEFAULT now(),
+        "mtime" timestamp
+);
+
 CREATE TABLE "oe" (
 	"id" integer DEFAULT nextval('id'::text) PRIMARY KEY,
 	"ordnumber" text NOT NULL,
@@ -362,6 +397,10 @@ CREATE TABLE "oe" (
 	"quonumber" text,
         "cusordnumber" text,
 	"intnotes" text,
+        "delivery_customer_id" integer,
+        "delivery_vendor_id" integer,
+        "language_id" integer,
+        "payment_id" integer,
 	"department_id" integer DEFAULT 0,
         "itime" timestamp DEFAULT now(),
         "mtime" timestamp,
@@ -370,7 +409,7 @@ CREATE TABLE "oe" (
 );
 
 CREATE TABLE "orderitems" (
-	"trans_id" integer REFERENCES oe (id),
+	"trans_id" integer,
 	"parts_id" integer REFERENCES parts (id),
 	"description" text,
 	"qty" real,
@@ -381,9 +420,13 @@ CREATE TABLE "orderitems" (
 	"reqdate" date,
 	"ship" real,
 	"serialnumber" text,
-	"id" integer DEFAULT nextval('orderitemsid'::text) PRIMARY KEY,
+	"id" integer DEFAULT nextval('orderitemsid'::text),
         "itime" timestamp DEFAULT now(),
-        "mtime" timestamp
+        "mtime" timestamp,
+        "pricegroup_id" integer,
+        "ordnumber" text,
+        "transdate" text,
+        "cusordnumber" text
 );
 
 CREATE TABLE "exchangerate" (
@@ -417,6 +460,8 @@ CREATE TABLE "employee" (
 CREATE TABLE "shipto" (
 	"trans_id" integer,
 	"shiptoname" character varying(75),
+        "shiptodepartment_1" character varying(75),
+        "shiptodepartment_2" character varying(75),
 	"shiptostreet" character varying(75),
 	"shiptozipcode" character varying(75),
 	"shiptocity" character varying(75),
@@ -431,7 +476,7 @@ CREATE TABLE "shipto" (
 
 CREATE TABLE "project" (
 	"id" integer DEFAULT nextval('id'::text) PRIMARY KEY,
-	"projectnumber" text,
+	"projectnumber" text UNIQUE,
 	"description" text,
         "itime" timestamp DEFAULT now(),
         "mtime" timestamp
@@ -511,6 +556,27 @@ CREATE TABLE "business" (
         "mtime" timestamp
 );
 
+
+CREATE TABLE "language" (
+	"id" integer DEFAULT nextval('id'::text) PRIMARY KEY,
+	"description" text,
+	"template_code" text,
+        "article_code" text,
+        "itime" timestamp DEFAULT now(),
+        "mtime" timestamp
+);
+
+CREATE TABLE "payment_terms" (
+	"id" integer DEFAULT nextval('id'::text) PRIMARY KEY,
+	"description" text,
+	"description_long" text,
+	"terms_netto" integer,
+        "terms_skonto" integer,
+        "percent_skonto" real,
+        "itime" timestamp DEFAULT now(),
+        "mtime" timestamp
+);
+
 CREATE TABLE "sic" (
 	"code" text,
 	"sictype" character(1),
@@ -538,6 +604,22 @@ CREATE TABLE "licenseinvoice" (
   license_id integer
 );
 
+-- Preisgruppenverwaltung
+CREATE TABLE "pricegroup" (
+  "id" integer DEFAULT nextval('id'::text),
+  "pricegroup" text not null,
+  PRIMARY KEY (id)
+);
+--Preisverwaltung der Preisgruppen
+CREATE TABLE "prices" (
+  "parts_id" integer REFERENCES parts(id),
+  "pricegroup_id" integer REFERENCES pricegroup(id),
+  "price" numeric(15,5)
+);
+
+
+
+
 -- Table of tax authoritys
 -- for module ustva
 -- Lx office
