X-Git-Url: https://delta.frontaccounting.com/gitweb/?a=blobdiff_plain;f=sql%2Fen_US-new.sql;h=cc4ccb196ea4175301faabe248fbbd5af6ce6aac;hb=649c678c71f2827326c9761b0091bb15196f8878;hp=1547fe0778f14ce17d0cccccab08bab67e2c80a9;hpb=adbab1c1f0aee54dc5558639208e16060cfd8d23;p=fa-stable.git diff --git a/sql/en_US-new.sql b/sql/en_US-new.sql index 1547fe07..cc4ccb19 100644 --- a/sql/en_US-new.sql +++ b/sql/en_US-new.sql @@ -186,19 +186,14 @@ CREATE TABLE IF NOT EXISTS `0_bom` ( DROP TABLE IF EXISTS `0_budget_trans`; CREATE TABLE IF NOT EXISTS `0_budget_trans` ( - `counter` int(11) NOT NULL auto_increment, - `type` smallint(6) NOT NULL default '0', - `type_no` bigint(16) NOT NULL default '1', + `id` int(11) NOT NULL auto_increment, `tran_date` date NOT NULL default '0000-00-00', `account` varchar(15) NOT NULL default '', `memo_` tinytext NOT NULL, `amount` double NOT NULL default '0', `dimension_id` int(11) default '0', `dimension2_id` int(11) default '0', - `person_type_id` int(11) default NULL, - `person_id` tinyblob, - PRIMARY KEY (`counter`), - KEY `Type_and_Number` (`type`,`type_no`), + PRIMARY KEY (`id`), KEY `Account` (`account`,`tran_date`,`dimension_id`,`dimension2_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 ; @@ -540,11 +535,11 @@ CREATE TABLE IF NOT EXISTS `0_cust_allocations` ( `trans_type_from` int(11) default NULL, `trans_no_to` int(11) default NULL, `trans_type_to` int(11) default NULL, - PRIMARY KEY (`id`), - UNIQUE KEY(`trans_type_from`,`trans_no_from`,`trans_type_to`,`trans_no_to`), + PRIMARY KEY (`id`), + UNIQUE KEY (`trans_type_from`,`trans_no_from`,`trans_type_to`,`trans_no_to`), KEY `From` (`trans_type_from`,`trans_no_from`), KEY `To` (`trans_type_to`,`trans_no_to`) -) ENGINE=InnoDB AUTO_INCREMENT=1 ; +) ENGINE=InnoDB AUTO_INCREMENT=1 ; -- -- Dumping data for table `0_cust_allocations` @@ -770,6 +765,8 @@ INSERT INTO `0_fiscal_year` VALUES(1, '2008-01-01', '2008-12-31', 0); INSERT INTO `0_fiscal_year` VALUES(2, '2009-01-01', '2009-12-31', 0); INSERT INTO `0_fiscal_year` VALUES(3, '2010-01-01', '2010-12-31', 0); INSERT INTO `0_fiscal_year` VALUES(4, '2011-01-01', '2011-12-31', 0); +INSERT INTO `0_fiscal_year` VALUES(5, '2012-01-01', '2012-12-31', 0); +INSERT INTO `0_fiscal_year` VALUES(6, '2013-01-01', '2013-12-31', 0); -- -- Table structure for table `0_gl_trans` @@ -815,6 +812,7 @@ CREATE TABLE IF NOT EXISTS `0_grn_batch` ( `reference` varchar(60) NOT NULL default '', `delivery_date` date NOT NULL default '0000-00-00', `loc_code` varchar(5) default NULL, + `rate` double NOT NULL default '1', PRIMARY KEY (`id`), KEY `delivery_date` (`delivery_date`), KEY `purch_order_no` (`purch_order_no`) @@ -1558,6 +1556,7 @@ CREATE TABLE IF NOT EXISTS `0_stock_category` ( `dflt_dim2` int(11) default NULL, `inactive` tinyint(1) NOT NULL default '0', `dflt_no_sale` tinyint(1) NOT NULL default '0', + `dflt_no_sale` tinyint(1) NOT NULL default '0', PRIMARY KEY (`category_id`), UNIQUE KEY `description` (`description`) ) ENGINE=MyISAM AUTO_INCREMENT=5 ; @@ -1566,10 +1565,10 @@ CREATE TABLE IF NOT EXISTS `0_stock_category` ( -- Dumping data for table `0_stock_category` -- -INSERT INTO `0_stock_category` VALUES(1, 'Components', 1, 'each', 'B', '4010', '5010', '1510', '5040', '1530', 0, 0, 0, 0); -INSERT INTO `0_stock_category` VALUES(2, 'Charges', 1, 'each', 'D', '4010', '5010', '1510', '5040', '1530', 0, 0, 0, 0); -INSERT INTO `0_stock_category` VALUES(3, 'Systems', 1, 'each', 'M', '4010', '5010', '1510', '5040', '1530', 0, 0, 0, 0); -INSERT INTO `0_stock_category` VALUES(4, 'Services', 1, 'hrs', 'D', '4010', '5010', '1510', '5040', '1530', 0, 0, 0, 0); +INSERT INTO `0_stock_category` VALUES(1, 'Components', 1, 'each', 'B', '4010', '5010', '1510', '5040', '1530', 0, 0, 0, 0, 0); +INSERT INTO `0_stock_category` VALUES(2, 'Charges', 1, 'each', 'D', '4010', '5010', '1510', '5040', '1530', 0, 0, 0, 0, 0); +INSERT INTO `0_stock_category` VALUES(3, 'Systems', 1, 'each', 'M', '4010', '5010', '1510', '5040', '1530', 0, 0, 0, 0, 0); +INSERT INTO `0_stock_category` VALUES(4, 'Services', 1, 'hrs', 'D', '4010', '5010', '1510', '5040', '1530', 0, 0, 0, 0, 0); -- -------------------------------------------------------- @@ -1600,6 +1599,7 @@ CREATE TABLE IF NOT EXISTS `0_stock_master` ( `overhead_cost` double NOT NULL default '0', `inactive` tinyint(1) NOT NULL default '0', `no_sale` tinyint(1) NOT NULL default '0', + `no_purchase` tinyint(1) NOT NULL default '0', `editable` tinyint(1) NOT NULL default '0', PRIMARY KEY (`stock_id`) ) ENGINE=InnoDB; @@ -1695,11 +1695,11 @@ CREATE TABLE IF NOT EXISTS `0_supp_allocations` ( `trans_type_from` int(11) default NULL, `trans_no_to` int(11) default NULL, `trans_type_to` int(11) default NULL, - PRIMARY KEY (`id`), - UNIQUE KEY(`trans_type_from`,`trans_no_from`,`trans_type_to`,`trans_no_to`), + PRIMARY KEY (`id`), + UNIQUE KEY (`trans_type_from`,`trans_no_from`,`trans_type_to`,`trans_no_to`), KEY `From` (`trans_type_from`,`trans_no_from`), KEY `To` (`trans_type_to`,`trans_no_to`) -) ENGINE=InnoDB AUTO_INCREMENT=1 ; +) ENGINE=InnoDB AUTO_INCREMENT=1 ; -- -- Dumping data for table `0_supp_allocations` @@ -1756,7 +1756,7 @@ CREATE TABLE IF NOT EXISTS `0_supp_trans` ( `rate` double NOT NULL default '1', `alloc` double NOT NULL default '0', `tax_included` tinyint(1) NOT NULL default '0', - `tax_algorihm` tinyint(1) NOT NULL default '1', + `tax_algorithm` tinyint(1) NOT NULL default '1', PRIMARY KEY (`type`,`trans_no`), KEY `supplier_id` (`supplier_id`), KEY `SupplierID_2` (`supplier_id`,`supp_reference`), @@ -1781,7 +1781,7 @@ CREATE TABLE IF NOT EXISTS `0_sys_prefs` ( `category` varchar(30) default NULL, `type` varchar(20) NOT NULL default '', `length` smallint(6) default NULL, - `value` text, + `value` text NOT NULL, PRIMARY KEY (`name`), KEY `category` (`category`) ) ENGINE=MyISAM; @@ -1803,7 +1803,7 @@ INSERT INTO `0_sys_prefs` VALUES('coy_logo', 'setup.company', 'varchar', 100, '' INSERT INTO `0_sys_prefs` VALUES('domicile', 'setup.company', 'varchar', 55, ''); INSERT INTO `0_sys_prefs` VALUES('curr_default', 'setup.company', 'char', 3, 'USD'); INSERT INTO `0_sys_prefs` VALUES('use_dimension', 'setup.company', 'tinyint', 1, '1'); -INSERT INTO `0_sys_prefs` VALUES('f_year', 'setup.company', 'int', 11, '3'); +INSERT INTO `0_sys_prefs` VALUES('f_year', 'setup.company', 'int', 11, '6'); INSERT INTO `0_sys_prefs` VALUES('no_item_list', 'setup.company', 'tinyint', 1, '0'); INSERT INTO `0_sys_prefs` VALUES('no_customer_list', 'setup.company', 'tinyint', 1, '0'); INSERT INTO `0_sys_prefs` VALUES('no_supplier_list', 'setup.company', 'tinyint', 1, '0'); @@ -1817,7 +1817,7 @@ INSERT INTO `0_sys_prefs` VALUES('profit_loss_year_act', 'glsetup.general', 'var INSERT INTO `0_sys_prefs` VALUES('retained_earnings_act', 'glsetup.general', 'varchar', 15, '3590'); INSERT INTO `0_sys_prefs` VALUES('bank_charge_act', 'glsetup.general', 'varchar', 15, '5690'); INSERT INTO `0_sys_prefs` VALUES('exchange_diff_act', 'glsetup.general', 'varchar', 15, '4450'); -INSERT INTO `0_sys_prefs` VALUES('tax_algorithm', 'glsetup.general', 'tinyint', 1, '1'); +INSERT INTO `0_sys_prefs` VALUES('tax_algorithm', 'glsetup.customer', 'tinyint', 1, '1'); INSERT INTO `0_sys_prefs` VALUES('default_credit_limit', 'glsetup.customer', 'int', 11, '1000'); INSERT INTO `0_sys_prefs` VALUES('accumulate_shipping', 'glsetup.customer', 'tinyint', 1, '0'); INSERT INTO `0_sys_prefs` VALUES('legal_text', 'glsetup.customer', 'tinytext', 0, ''); @@ -1843,7 +1843,9 @@ INSERT INTO `0_sys_prefs` VALUES('default_workorder_required', 'glsetup.manuf', INSERT INTO `0_sys_prefs` VALUES('version_id', 'system', 'varchar', 11, '2.3rc'); INSERT INTO `0_sys_prefs` VALUES('auto_curr_reval', 'setup.company', 'smallint', 6, '1'); INSERT INTO `0_sys_prefs` VALUES('grn_clearing_act', 'glsetup.purchase', 'varchar', 15, '1550'); +INSERT INTO `0_sys_prefs` VALUES('bcc_email', 'setup.company', 'varchar', 100, ''); INSERT INTO `0_sys_prefs` VALUES('deferred_income_act', 'glsetup.sales', 'varchar', '15', ''); +INSERT INTO `0_sys_prefs` VALUES('gl_closing_date','setup.closing_date', 'date', 8, ''); -- -------------------------------------------------------- @@ -2072,14 +2074,14 @@ CREATE TABLE IF NOT EXISTS `0_users` ( `show_codes` tinyint(1) NOT NULL default '0', `show_hints` tinyint(1) NOT NULL default '0', `last_visit_date` datetime default NULL, - `query_size` tinyint(1) default '10', + `query_size` tinyint(1) unsigned default '10', `graphic_links` tinyint(1) default '1', `pos` smallint(6) default '1', `print_profile` varchar(30) NOT NULL default '1', `rep_popup` tinyint(1) default '1', `sticky_doc_date` tinyint(1) default '0', `startup_tab` varchar(20) NOT NULL default '', - `transaction_days` smallint(6) NOT NULL default '0', + `transaction_days` smallint(6) NOT NULL default '30', `inactive` tinyint(1) NOT NULL default '0', PRIMARY KEY (`id`), UNIQUE KEY `user_id` (`user_id`) @@ -2162,6 +2164,28 @@ CREATE TABLE IF NOT EXISTS `0_workorders` ( -- +-- -------------------------------------------------------- + +-- +-- Table structure for table `0_wo_costing` +-- + +DROP TABLE IF EXISTS `0_wo_costing`; +CREATE TABLE `0_wo_costing` ( + `id` int(11) NOT NULL auto_increment, + `workorder_id` int(11) NOT NULL default '0', + `cost_type` tinyint(1) NOT NULL default '0', + `trans_type` int(11) NOT NULL default '0', + `trans_no` int(11) NOT NULL default '0', + `factor` double NOT NULL default '1', + PRIMARY KEY (`id`) +) ENGINE=InnoDB; + +-- +-- Dumping data for table `0_wo_costing` +-- + + -- -------------------------------------------------------- --