upgrade2.8-2.9.sql

java开源项目源代码

INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (10, 10, 0);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (11, 10, 10);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (12, 5, 0);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (13, 99, 0);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (14, 5, 13);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (15, 10, 0);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (19, 99, 0);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (20, 1, 19);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (21, 1, 19);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (22, 2, 19);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (23, 1, 19);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (24, 5, 19);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (28, 200000, 0);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (32, 25, 28);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (33, 9999, 28);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (34, 99, 28);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (36, 5, 34);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (37, 9999, 28);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (38, 10, 28);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (39, 999, 28);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (42, 5, 39);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (43, 99, 28);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (44, 1, 43);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (45, 1, 43);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (46, 2, 43);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (47, 1, 43);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (48, 5, 43);
INSERT INTO `EDI_ORDERS_Seg_Groups` VALUES (49, 10, 28);


CREATE TABLE `LocTransfers` (
  `Reference` int(11) NOT NULL default '0',
  `StockID` varchar(20) NOT NULL default '',
  `ShipQty` int(11) NOT NULL default '0',
  `RecQty` int(11) NOT NULL default '0',
  `ShipDate` date NOT NULL default '0000-00-00',
  `RecDate` date NOT NULL default '0000-00-00',
  `ShipLoc` varchar(7) NOT NULL default '',
  `RecLoc` varchar(7) NOT NULL default '',
  KEY `Reference` (`Reference`,`StockID`),
  KEY `ShipLoc` (`ShipLoc`),
  KEY `RecLoc` (`RecLoc`),
  KEY `StockID` (`StockID`)
) TYPE=InnoDB COMMENT='Stores Transfers To Other Locations';

ALTER TABLE `LocTransfers`
  ADD CONSTRAINT `LocTransfers_ibfk_3` FOREIGN KEY (`StockID`) REFERENCES `StockMaster` (`StockID`),
  ADD CONSTRAINT `LocTransfers_ibfk_1` FOREIGN KEY (`ShipLoc`) REFERENCES `Locations` (`LocCode`),
  ADD CONSTRAINT `LocTransfers_ibfk_2` FOREIGN KEY (`RecLoc`) REFERENCES `Locations` (`LocCode`);

CREATE TABLE StockSerialItems (
  LocCode varchar(5) NOT NULL default '',
  StockID varchar(20) NOT NULL default '',
  SerialNo varchar(30) NOT NULL default '',
  Quantity float NOT NULL default 0,
  PRIMARY KEY  (StockID, SerialNo, LocCode),
  KEY (StockID),
  KEY (LocCode)
) TYPE=InnoDB;

CREATE TABLE StockSerialMoves (
  StkItmMoveNo int(11) NOT NULL auto_increment,
  StockMoveNo int(11) NOT NULL default '0',
  StockID varchar(20) NOT NULL default '',
  SerialNo varchar(30) NOT NULL default '',
  MoveQty float NOT NULL default '0',
  PRIMARY KEY  (StkItmMoveNo),
  KEY StockMoveNo (StockMoveNo),
  KEY StockID_SN (StockID, SerialNo)
) TYPE=InnoDB;

ALTER TABLE StockSerialMoves ADD FOREIGN KEY (StockMoveNo) REFERENCES StockMoves (StkMoveNo);
ALTER TABLE StockSerialItems ADD FOREIGN KEY (StockID) REFERENCES StockMaster (StockID);
ALTER TABLE StockSerialItems ADD FOREIGN KEY (LocCode) REFERENCES Locations (LocCode);
ALTER TABLE StockSerialMoves ADD FOREIGN KEY (StockID, SerialNo) REFERENCES StockSerialItems (StockID, SerialNo);

ALTER TABLE `StockMaster` ADD `Serialised` TINYINT DEFAULT '0' NOT NULL ;
ALTER TABLE `StockMaster` ADD `DecimalPlaces` TINYINT DEFAULT '0' NOT NULL ;
ALTER TABLE `StockMoves` DROP `Bundle`;

CREATE TABLE `Help` (
  ID INT NOT NULL AUTO_INCREMENT,
  PageID SMALLINT NOT NULL,
  Narrative TEXT NOT NULL,
  HelpType CHAR NOT NULL DEFAULT 'U',
  PRIMARY KEY (`ID`),
  INDEX (`PageID`),
  INDEX (`HelpType`)
)TYPE=InnoDB COMMENT='Context Sensitive Help Narrative';

CREATE TABLE Scripts (
  PageID smallint(4) NOT NULL default '0',
  FileName varchar(50) NOT NULL default '',
  PageDescription text NOT NULL,
  PRIMARY KEY  (PageID),
  KEY FileName (FileName)
) TYPE=InnoDB COMMENT='Index of all scripts';


ALTER TABLE Help ADD FOREIGN KEY (PageID) REFERENCES Scripts (PageID);

INSERT INTO Scripts VALUES (1, 'AccountGroups.php', 'Defines the groupings of general ledger accounts');
INSERT INTO Scripts VALUES (2, 'AgedDebtors.php', 'Lists customer account balances in detail or summary in selected currency');
INSERT INTO Scripts VALUES (3, 'AgedSuppliers.php', 'Lists supplier account balances in detail or summary in selected currency');
INSERT INTO Scripts VALUES (4, 'Areas.php', 'Defines the sales areas - all customers must belong to a sales area for the purposes of sales analysis');
INSERT INTO Scripts VALUES (5, 'BOMInquiry.php', 'Displays the bill of material with cost information');
INSERT INTO Scripts VALUES (6, 'BOMListing.php', 'Lists the bills of material for a selected range of items');
INSERT INTO Scripts VALUES (7, 'BOMs.php', 'Administers the bills of material for a selected item');
INSERT INTO Scripts VALUES (8, 'BankAccounts.php', 'Defines the general ledger code for bank accounts and specifies that bank transactions be created for these accounts for the purposes of reconciliation');
INSERT INTO Scripts VALUES (9, 'BankMatching.php', 'Allows payments and receipts to be matched off against bank statements');
INSERT INTO Scripts VALUES (10, 'BankReconciliation.php', 'Displays the bank reconciliation for a selected bank account');
INSERT INTO Scripts VALUES (11, 'COGSGLPostings.php', 'Defines the general ledger account to be used for cost of sales entries');
INSERT INTO Scripts VALUES (12, 'CompanyPreferences.php', 'Defines the settings applicable for the company, including name, address, tax authority reference, whether GL integration used etc.');
INSERT INTO Scripts VALUES (13, 'ConfirmDispatchControlled_Invoice.php', 'Specifies the batch references/serial numbers of items dispatched that are being invoiced');
INSERT INTO Scripts VALUES (14, 'ConfirmDispatch_Invoice.php', 'Creates sales invoices from entered sales orders based on the quantities dispatched that can be modified');
INSERT INTO Scripts VALUES (15, 'CreditItemsControlled.php', 'Specifies the batch references/serial numbers of items being credited back into stock');
INSERT INTO Scripts VALUES (16, 'CreditStatus.php', 'Defines the credit status records. Each customer account is given a credit status from this table. Some credit status records can prohibit invoicing and new orders being entered.');
INSERT INTO Scripts VALUES (17, 'Credit_Invoice.php', 'Creates a credit note based on the details of an existing invoice');
INSERT INTO Scripts VALUES (18, 'Currencies.php', 'Defines the currencies available. Each customer and supplier must be defined as transacting in one of the currencies defined here.');
INSERT INTO Scripts VALUES (19, 'CustEDISetup.php', 'Allows the set up the customer specified EDI parameters for server, email or ftp.');
INSERT INTO Scripts VALUES (20, 'CustWhereAlloc.php', 'Shows to which invoices a receipt was allocated to');
INSERT INTO Scripts VALUES (21, 'CustomerAllocations.php', 'Allows customer receipts and credit notes to be allocated to sales invoices');
INSERT INTO Scripts VALUES (22, 'CustomerBranches.php', 'Defines the details of customer branches such as delivery address and contact details - also sales area, representative etc');
INSERT INTO Scripts VALUES (23, 'CustomerInquiry.php', 'Shows the customers account transactions with balances outstanding, links available to drill down to invoice/credit note or email invoices/credit notes');
INSERT INTO Scripts VALUES (24, 'CustomerReceipt.php', 'Entry of both customer receipts against accounts receivable and also general ledger or nominal receipts');
INSERT INTO Scripts VALUES (25, 'CustomerTransInquiry.php', 'Lists in html the sequence of customer transactions, invoices, credit notes or receipts by a user entered date range');
INSERT INTO Scripts VALUES (26, 'Customers.php', 'Defines the setup of a customer account, including payment terms, billing address, credit status, currency etc');
INSERT INTO Scripts VALUES (27, 'DeliveryDetails.php', 'Used during order entry to allow the entry of delivery addresses other than the defaulted branch delivery address and information about carrier/shipping method etc');
INSERT INTO Scripts VALUES (28, 'DiscountCategories.php', 'Defines the items belonging to a discount category. Discount Categories are used to allow discounts based on quantities across a range of producs');
INSERT INTO Scripts VALUES (29, 'DiscountMatrix.php', 'Defines the rates of discount applicable to discount categories and the customer groupings to which the rates are to apply');
INSERT INTO Scripts VALUES (30, 'EDIMessageFormat.php', 'Specifies the EDI message format used by a customer - administrator use only.');
INSERT INTO Scripts VALUES (31, 'EDIProcessOrders.php', 'Processes incoming EDI orders into sales orders');
INSERT INTO Scripts VALUES (32, 'EDISendInvoices.php', 'Processes invoiced EDI customer invoices into EDI messages and sends using the customers preferred method either ftp or email attachments.');