|
Lines 1-23
Link Here
|
| 1 |
-- Holds details for vendors supplying goods by EDI |
1 |
-- Holds details for vendors supplying goods by EDI |
| 2 |
CREATE TABLE IF NOT EXISTS vendor_edi_accounts ( |
2 |
CREATE TABLE IF NOT EXISTS vendor_edi_accounts ( |
| 3 |
id int(11) NOT NULL auto_increment, |
3 |
id INT(11) NOT NULL auto_increment, |
| 4 |
description text NOT NULL, |
4 |
description TEXT NOT NULL, |
| 5 |
host varchar(40), |
5 |
host VARCHAR(40), |
| 6 |
username varchar(40), |
6 |
username VARCHAR(40), |
| 7 |
password varchar(40), |
7 |
password VARCHAR(40), |
| 8 |
last_activity date, |
8 |
last_activity DATE, |
| 9 |
vendor_id int(11) references aqbooksellers( id ), |
9 |
vendor_id INT(11) REFERENCES aqbooksellers( id ), |
| 10 |
download_directory text, |
10 |
download_directory TEXT, |
| 11 |
upload_directory text, |
11 |
upload_directory TEXT, |
| 12 |
san varchar(20), |
12 |
san VARCHAR(20), |
| 13 |
id_code_qualifier varchar(3) default '14', |
13 |
id_code_qualifier VARCHAR(3) default '14', |
| 14 |
transport varchar(6) default 'FTP', |
14 |
transport VARCHAR(6) default 'FTP', |
| 15 |
quotes_enabled tinyint(1) not null default 0, |
15 |
quotes_enabled TINYINT(1) not null default 0, |
| 16 |
invoices_enabled tinyint(1) not null default 0, |
16 |
invoices_enabled TINYINT(1) not null default 0, |
| 17 |
orders_enabled tinyint(1) not null default 0, |
17 |
orders_enabled TINYINT(1) not null default 0, |
| 18 |
responses_enabled tinyint(1) not null default 0, |
18 |
responses_enabled TINYINT(1) not null default 0, |
| 19 |
auto_orders tinyint(1) not null default 0, |
19 |
auto_orders TINYINT(1) not null default 0, |
| 20 |
shipment_budget integer(11) references aqbudgets( budget_id ), |
20 |
shipment_budget INTEGER(11) REFERENCES aqbudgets( budget_id ), |
| 21 |
PRIMARY KEY (id), |
21 |
PRIMARY KEY (id), |
| 22 |
KEY vendorid (vendor_id), |
22 |
KEY vendorid (vendor_id), |
| 23 |
KEY shipmentbudget (shipment_budget), |
23 |
KEY shipmentbudget (shipment_budget), |
|
Lines 27-42
CREATE TABLE IF NOT EXISTS vendor_edi_accounts (
Link Here
|
| 27 |
|
27 |
|
| 28 |
-- Hold the actual edifact messages with links to associated baskets |
28 |
-- Hold the actual edifact messages with links to associated baskets |
| 29 |
CREATE TABLE IF NOT EXISTS edifact_messages ( |
29 |
CREATE TABLE IF NOT EXISTS edifact_messages ( |
| 30 |
id int(11) NOT NULL auto_increment, |
30 |
id INT(11) NOT NULL auto_increment, |
| 31 |
message_type varchar(10) NOT NULL, |
31 |
message_type VARCHAR(10) NOT NULL, |
| 32 |
transfer_date date, |
32 |
transfer_date DATE, |
| 33 |
vendor_id int(11) references aqbooksellers( id ), |
33 |
vendor_id INT(11) REFERENCES aqbooksellers( id ), |
| 34 |
edi_acct integer references vendor_edi_accounts( id ), |
34 |
edi_acct INTEGER REFERENCES vendor_edi_accounts( id ), |
| 35 |
status text, |
35 |
status TEXT, |
| 36 |
basketno int(11) REFERENCES aqbasket( basketno), |
36 |
basketno INT(11) REFERENCES aqbasket( basketno), |
| 37 |
raw_msg mediumtext, |
37 |
raw_msg MEDIUMTEXT, |
| 38 |
filename text, |
38 |
filename TEXT, |
| 39 |
deleted boolean not null default 0, |
39 |
deleted BOOLEAN NOT NULL DEFAULT 0, |
| 40 |
PRIMARY KEY (id), |
40 |
PRIMARY KEY (id), |
| 41 |
KEY vendorid ( vendor_id), |
41 |
KEY vendorid ( vendor_id), |
| 42 |
KEY ediacct (edi_acct), |
42 |
KEY ediacct (edi_acct), |
|
Lines 54-72
ALTER TABLE aqinvoices ADD CONSTRAINT edifact_msg_fk FOREIGN KEY ( message_id )
Link Here
|
| 54 |
|
54 |
|
| 55 |
-- Hold the supplier ids from quotes for ordering |
55 |
-- Hold the supplier ids from quotes for ordering |
| 56 |
-- although this is an EAN-13 article number the standard says 35 characters ??? |
56 |
-- although this is an EAN-13 article number the standard says 35 characters ??? |
| 57 |
ALTER TABLE aqorders ADD COLUMN line_item_id varchar(35); |
57 |
ALTER TABLE aqorders ADD COLUMN line_item_id VARCHAR(35); |
| 58 |
|
58 |
|
| 59 |
-- The suppliers unique reference usually a quotation line number ('QLI') |
59 |
-- The suppliers unique reference usually a quotation line number ('QLI') |
| 60 |
-- Otherwise Suppliers unique orderline reference ('SLI') |
60 |
-- Otherwise Suppliers unique orderline reference ('SLI') |
| 61 |
ALTER TABLE aqorders ADD COLUMN suppliers_reference_number varchar(35); |
61 |
ALTER TABLE aqorders ADD COLUMN suppliers_reference_number VARCHAR(35); |
| 62 |
ALTER TABLE aqorders ADD COLUMN suppliers_reference_qualifier varchar(3); |
62 |
ALTER TABLE aqorders ADD COLUMN suppliers_reference_qualifier VARCHAR(3); |
| 63 |
ALTER TABLE aqorders ADD COLUMN suppliers_report text; |
63 |
ALTER TABLE aqorders ADD COLUMN suppliers_report text; |
| 64 |
|
64 |
|
| 65 |
-- hold the EAN/SAN used in ordering |
65 |
-- hold the EAN/SAN used in ordering |
| 66 |
CREATE TABLE IF NOT EXISTS edifact_ean ( |
66 |
CREATE TABLE IF NOT EXISTS edifact_ean ( |
| 67 |
ee_id integer(11) unsigned not null auto_increment primary key, |
67 |
ee_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, |
|
|
68 |
description VARCHAR(128) NULL DEFAULT NULL, |
| 68 |
branchcode VARCHAR(10) NOT NULL REFERENCES branches (branchcode), |
69 |
branchcode VARCHAR(10) NOT NULL REFERENCES branches (branchcode), |
| 69 |
ean varchar(15) NOT NULL, |
70 |
ean VARCHAR(15) NOT NULL, |
| 70 |
id_code_qualifier VARCHAR(3) NOT NULL DEFAULT '14', |
71 |
id_code_qualifier VARCHAR(3) NOT NULL DEFAULT '14', |
| 71 |
CONSTRAINT efk_branchcode FOREIGN KEY ( branchcode ) REFERENCES branches ( branchcode ) |
72 |
CONSTRAINT efk_branchcode FOREIGN KEY ( branchcode ) REFERENCES branches ( branchcode ) |
| 72 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |
73 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; |