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; |