upgrade2.7-2.8.sql

java开源项目源代码

begin;

ALTER TABLE Locations ADD TaxAuthority tinyint(4) NOT NULL default 1;

ALTER TABLE StockMaster ADD TaxLevel tinyint(4) NOT NULL default 1;

CREATE TABLE TaxAuthLevels (
  TaxAuthority tinyint NOT NULL default '1',
  DispatchTaxAuthority tinyint NOT NULL default '1',
  Level tinyint NOT NULL default '0',
  TaxRate double NOT NULL default '0',
  PRIMARY KEY  (TaxAuthority,DispatchTaxAuthority,Level),
  KEY (TaxAuthority),
  KEY (DispatchTaxAuthority)
) TYPE=Innodb;

INSERT INTO TaxAuthLevels VALUES (1, 1, 1, 0.1);
INSERT INTO TaxAuthLevels VALUES (1, 1, 2, 0);

ALTER TABLE TaxAuthorities DROP COLUMN Rate;
ALTER TABLE TaxAuthorities CHANGE TaxID TaxID tinyint(4) NOT NULL default '0';


ALTER TABLE StockMoves ADD COLUMN TaxRate float NOT NULL default 0;
ALTER TABLE DebtorTrans ADD COLUMN EDISent tinyint(4) NOT NULL default 0;
ALTER TABLE DebtorTrans ADD INDEX(`EDISent`);

ALTER TABLE CustBranch ADD CustBranchCode VARCHAR(30) NOT NULL default '';

ALTER TABLE WWW_Users ADD COLUMN Blocked tinyint(4) NOT NULL default 0;

ALTER TABLE DebtorsMaster ADD EDIInvoices tinyint(4) NOT NULL default '0';
ALTER TABLE DebtorsMaster ADD EDIOrders tinyint(4) NOT NULL default '0';
ALTER TABLE DebtorsMaster ADD EDIReference varchar(20) NOT NULL default '';
ALTER TABLE DebtorsMaster ADD EDITransport varchar(5) NOT NULL default 'email';
ALTER TABLE DebtorsMaster ADD EDIAddress varchar(50) NOT NULL default '';
ALTER TABLE DebtorsMaster ADD EDIServerUser varchar(20) NOT NULL default '';
ALTER TABLE DebtorsMaster ADD EDIServerPwd varchar(20) NOT NULL default '';
ALTER TABLE DebtorsMaster ADD INDEX (EDIInvoices);
ALTER TABLE DebtorsMaster ADD INDEX (EDIOrders);

CREATE TABLE EDIItemMapping (
  SuppOrCust varchar(4) NOT NULL default '',
  PartnerCode varchar(10) NOT NULL default '',
  StockID varchar(20) NOT NULL default '',
  PartnerStockID varchar(50) NOT NULL default '',
  PRIMARY KEY  (SuppOrCust,PartnerCode,StockID),
  KEY PartnerCode (PartnerCode),
  KEY StockID (StockID),
  KEY PartnerStockID (PartnerStockID),
  KEY SuppOrCust (SuppOrCust)
) TYPE=Innodb;

CREATE TABLE EDIMessageFormat (
  PartnerCode varchar(10) NOT NULL default '',
  MessageType varchar(6) NOT NULL default '',
  Section varchar(7) NOT NULL default '',
  SequenceNo int(11) NOT NULL default '0',
  LineText varchar(70) NOT NULL default '',
  PRIMARY KEY  (PartnerCode,MessageType,SequenceNo),
  KEY Section (Section)
) TYPE=Innodb;


INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Heading', 10, 'UNH+[EDITransNo]+INVOIC:D:96A:UN:EAN008\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Heading', 20, 'BGM+[InvOrCrd]+[TransNo]+[OrigOrDup]\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Heading', 30, 'DTM+137:[TranDate]:102\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Heading', 60, 'RFF+ON:[OrderNo]\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Heading', 70, 'NAD+BY+[CustBranchCode]::92\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Heading', 80, 'NAD+SU+[CompanyEDIReference]::91\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Heading', 90, 'NAD+UD++[BranchName]+[BranchStreet]+[BranchCity]+[BranchState]\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Heading', 100, 'RFF+AMT:[TaxAuthorityRef]\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Heading', 110, 'PAT+1++5:3:D:30\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Heading', 120, 'DTM+13:[DatePaymentDue]:102\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Heading', 130, 'TAX+7+GST+++:::10\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Heading', 150, 'MOA+124:[TaxTotal]\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Detail', 160, 'LIN+[LineNumber]\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Detail', 170, 'PIA+5+[StockID]:SA+[CustStockID]:IN\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Detail', 180, 'IMD+F++:::[ItemDescription]\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Heading', 85, 'NAD+IV+[CustEDIReference]::9\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Detail', 200, 'QTY+47:[QtyInvoiced]\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Detail', 220, 'MOA+128:[LineTotalExclTax]\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Detail', 230, 'PRI+AAA:[UnitPrice]\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Detail', 240, 'TAX+7+GST+++:::10\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Detail', 250, 'MOA+124:[LineTaxAmount]\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Summary', 260, 'UNS+S\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Summary', 270, 'CNT+2:[NoLines]\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Summary', 280, 'MOA+128:[TotalAmountExclTax]\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Summary', 290, 'TAX+7+GST+++:::10\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Summary', 300, 'MOA+128:[TaxTotal]\'');
INSERT INTO EDIMessageFormat VALUES ('DEFAULT', 'INVOIC', 'Summary', 310, 'UNT+[NoSegments]+[EDITransNo]\'');

ALTER TABLE AccountGroups Type=Innodb;
ALTER TABLE Areas Type=Innodb;
ALTER TABLE BOM Type=Innodb;
ALTER TABLE BankAccounts Type=Innodb;
ALTER TABLE BankTrans Type=Innodb;
ALTER TABLE Buckets Type=Innodb;
ALTER TABLE COGSGLPostings Type=Innodb;
ALTER TABLE ChartMaster Type=Innodb;
ALTER TABLE Companies Type=Innodb;
ALTER TABLE ContractBOM Type=Innodb;
ALTER TABLE ContractReqts Type=Innodb;
ALTER TABLE Contracts Type=Innodb;
ALTER TABLE Currencies Type=Innodb;
ALTER TABLE CustBranch Type=Innodb;
ALTER TABLE DebtorsMaster Type=Innodb;
ALTER TABLE DiscountMatrix Type=Innodb;
ALTER TABLE FreightCosts Type=Innodb;
ALTER TABLE HoldReasons Type=Innodb;
ALTER TABLE LastCostRollUp Type=Innodb;
ALTER TABLE PaymentTerms Type=Innodb;
ALTER TABLE Prices Type=Innodb;
ALTER TABLE PurchData Type=Innodb;
ALTER TABLE ReportColumns Type=Innodb;
ALTER TABLE ReportHeaders Type=Innodb;
ALTER TABLE SalesGLPostings Type=Innodb;
ALTER TABLE SalesTypes Type=Innodb;
ALTER TABLE Salesman Type=Innodb;
ALTER TABLE ShipmentCharges Type=Innodb;
ALTER TABLE Shippers Type=Innodb;
ALTER TABLE StockCategory Type=Innodb;
ALTER TABLE StockCheckFreeze Type=Innodb;
ALTER TABLE StockCounts Type=Innodb;
ALTER TABLE SupplierContacts Type=Innodb;
ALTER TABLE Suppliers Type=Innodb;
ALTER TABLE TaxAuthorities Type=Innodb;
ALTER TABLE WORequirements Type=Innodb;
ALTER TABLE WWW_Users Type=Innodb;
ALTER TABLE WorkCentres Type=Innodb;
ALTER TABLE Locations Type=Innodb;


ALTER TABLE TaxAuthLevels ADD FOREIGN KEY (TaxAuthority) REFERENCES TaxAuthorities (TaxID);
ALTER TABLE TaxAuthLevels ADD FOREIGN KEY (DispatchTaxAuthority) REFERENCES TaxAuthorities (TaxID);

ALTER TABLE BOM ADD FOREIGN KEY (Parent) REFERENCES StockMaster (StockID);
ALTER TABLE BOM ADD FOREIGN KEY (Component) REFERENCES StockMaster (StockID);
ALTER TABLE BOM ADD FOREIGN KEY (WorkCentreAdded) REFERENCES WorkCentres (Code);
ALTER TABLE BOM ADD FOREIGN KEY (LocCode) REFERENCES Locations (LocCode);

ALTER TABLE BankAccounts ADD FOREIGN KEY (AccountCode) REFERENCES ChartMaster (AccountCode);


ALTER TABLE BankTrans ADD FOREIGN KEY (Type) REFERENCES SysTypes (TypeID);
ALTER TABLE BankTrans ADD FOREIGN KEY (BankAct) REFERENCES BankAccounts (AccountCode);


ALTER TABLE Buckets ADD FOREIGN KEY (WorkCentre) REFERENCES WorkCentres (Code);

ALTER TABLE ChartDetails ADD FOREIGN KEY (AccountCode) REFERENCES ChartMaster (AccountCode);
ALTER TABLE ChartDetails ADD FOREIGN KEY (Period) REFERENCES Periods (PeriodNo);


ALTER TABLE ChartMaster ADD FOREIGN KEY (Group_) REFERENCES AccountGroups (GroupName);