sql/alter2.5.sql

   1 # reliable cost change log in stock_moves
   2 ALTER TABLE `0_stock_moves` CHANGE COLUMN `standard_cost` `unit_cost` double NOT NULL DEFAULT '0';
   3 ALTER TABLE `0_debtor_trans_details` CHANGE COLUMN `standard_cost` `unit_cost` double NOT NULL DEFAULT '0';
   4
   5 # naming cleanups
   6 ALTER TABLE `0_purch_orders` CHANGE COLUMN `requisition_no` `supp_reference` tinytext;
   7
   8 # cleanups in work orders
   9 ALTER TABLE  `0_workorders` DROP INDEX `wo_ref`;
  10 ALTER TABLE  `0_workorders` ADD KEY `wo_ref` (`wo_ref`);
  11 ALTER TABLE  `0_workorders` DROP COLUMN `additional_costs`;
  12
  13 # improvements in tax systems support
  14 ALTER TABLE `0_stock_category` ADD COLUMN  `vat_category` tinyint(1) NOT NULL DEFAULT '0' AFTER `dflt_no_purchase`;
  15 ALTER TABLE `0_stock_master` ADD COLUMN  `vat_category` tinyint(1) NOT NULL DEFAULT '0' AFTER `fa_class_id`;
  16 ALTER TABLE `0_trans_tax_details` ADD COLUMN  `vat_category` tinyint(1) NOT NULL DEFAULT '0' AFTER `reg_type`;
  17 ALTER TABLE `0_trans_tax_details` ADD COLUMN `tax_group_id` int(11) DEFAULT NULL AFTER `vat_category`;
  18
  19 UPDATE `0_trans_tax_details` tax
  20         LEFT JOIN `0_supp_trans` purch ON tax.trans_no=purch.trans_no AND tax.trans_type=purch.type
  21         LEFT JOIN `0_suppliers` supp ON purch.supplier_id=supp.supplier_id
  22         LEFT JOIN `0_debtor_trans` sales ON tax.trans_no=sales.trans_no AND tax.trans_type=sales.type
  23         LEFT JOIN `0_cust_branch` cust ON sales.branch_code=cust.branch_code
  24  SET tax.tax_group_id = IFNULL(supp.tax_group_id, cust.tax_group_id);
  25
  26 ALTER TABLE `0_tax_groups` ADD COLUMN `tax_area` tinyint(1) NOT NULL DEFAULT '0' AFTER `name`;
  27
  28 # shipment options
  29 ALTER TABLE `0_stock_master` ADD COLUMN `shipper_id` INT(11) NOT NULL DEFAULT '0' AFTER `vat_category`;
  30
  31 INSERT INTO `0_stock_category` (`description`, `dflt_tax_type`, `dflt_units`, `dflt_mb_flag`, `dflt_sales_act`, `dflt_cogs_act`, `dflt_no_sale`)
  32         VALUES (@shipping_cat_description, @shipping_tax_type, @shipping_units, 'T', @shipping_sales_act, @shipping_cogs_act, '1');
  33
  34 SET @shipment_cat=LAST_INSERT_ID();
  35
  36 INSERT INTO `0_stock_master` (`stock_id`, `tax_type_id`, `description`, `units`, `mb_flag`, `sales_account`, `no_sale`, `no_purchase`, `vat_category`, `category_id`, `shipper_id`, `inactive`)
  37         SELECT shipper.shipper_name, @shipping_tax_type, shipper.shipper_name, @shipping_units, 'T', @shipping_sales_act, 1, 1, 0, @shipment_cat, shipper.shipper_id, shipper.inactive
  38                 FROM `0_shippers` shipper;
  39
  40 ALTER TABLE `0_sales_orders` CHANGE COLUMN `ship_via` `ship_via` varchar(20) NOT NULL DEFAULT '';
  41
  42 UPDATE `0_sales_orders` ord
  43         LEFT JOIN `0_shippers` ship ON  ord.ship_via=ship.shipper_id
  44         LEFT JOIN `0_stock_master` stock ON stock.shipper_id=ship.shipper_id
  45         SET ord.ship_via=stock.stock_id;
  46
  47 ALTER TABLE `0_debtor_trans` CHANGE COLUMN `ship_via` `ship_via` varchar(20) NOT NULL DEFAULT '';
  48
  49 UPDATE `0_debtor_trans` trans
  50         LEFT JOIN `0_shippers` ship ON  trans.ship_via=ship.shipper_id
  51         LEFT JOIN `0_stock_master` stock ON stock.shipper_id=ship.shipper_id
  52         SET trans.ship_via=stock.stock_id;
  53
  54 ALTER TABLE `0_cust_branch` CHANGE COLUMN `default_ship_via` `default_ship_via` varchar(20) NOT NULL DEFAULT '';
  55
  56 UPDATE `0_cust_branch` branch
  57         LEFT JOIN `0_shippers` ship ON  branch.default_ship_via=ship.shipper_id
  58         LEFT JOIN `0_stock_master` stock ON stock.shipper_id=ship.shipper_id
  59         SET branch.default_ship_via=stock.stock_id;
  60
  61 ALTER TABLE `0_tax_group_items` DROP COLUMN `tax_shipping`;
  62
  63 # new debug trail
  64 DROP TABLE `0_sql_trail`;
  65 CREATE TABLE `0_db_trail` (
  66                 `id` int(11) NOT NULL AUTO_INCREMENT,
  67                 `stamp` timestamp DEFAULT CURRENT_TIMESTAMP,
  68                 `user` tinyint(3) unsigned NOT NULL DEFAULT '0',
  69                 `msg`  varchar(255) DEFAULT '',
  70                 `entry`  varchar(255) DEFAULT '',
  71                 `data` text DEFAULT NULL,
  72         PRIMARY KEY (`id`)
  73         ) ENGINE=MyISAM;
  74
  75 # payment terms normalization, early payment support
  76 ALTER TABLE `0_payment_terms` CHANGE COLUMN `terms_indicator` `id` tinyint(1) NOT NULL DEFAULT '0';
  77 ALTER TABLE `0_payment_terms` ADD COLUMN `type` tinyint(1) NOT NULL DEFAULT '0' AFTER `terms`;
  78 UPDATE `0_payment_terms` SET `type`=IF(days_before_due < 0, 1, IF(day_in_following_month>0, 4, IF(days_before_due=0, 2, 3)));
  79 UPDATE `0_payment_terms` SET days_before_due=day_in_following_month WHERE days_before_due<=0;
  80 ALTER TABLE `0_payment_terms` CHANGE COLUMN `days_before_due` `days` int(11) NOT NULL DEFAULT '0';
  81 ALTER TABLE `0_payment_terms` DROP COLUMN `day_in_following_month`;
  82 ALTER TABLE `0_payment_terms` ADD COLUMN `early_discount` double NOT NULL DEFAULT '0' AFTER `days`;
  83 ALTER TABLE `0_payment_terms` ADD COLUMN `early_days` int(11) NOT NULL DEFAULT '0' AFTER `early_discount`;
  84
  85 ALTER TABLE `0_cust_allocations` ADD COLUMN `discount` double unsigned DEFAULT '0' AFTER `amt`;
  86 ALTER TABLE `0_supp_allocations` ADD COLUMN `discount` double unsigned DEFAULT '0' AFTER `amt`;
  87
  88 # please define and set payment terms with discount manually per customer when needed
  89 ALTER TABLE `0_debtors_master` DROP COLUMN `pymt_discount`;
  90
  91 # this update works only for single pay for invoice discounts, so may need additional manual fixes in more complex cases
  92 UPDATE `0_cust_allocations` ca
  93         LEFT JOIN `0_debtor_trans` pay ON pay.`type`=ca.`trans_type_from` AND pay.`trans_no`=ca.`trans_no_from`
  94         LEFT JOIN `0_debtor_trans` trans ON trans.`type`=ca.`trans_type_to` AND trans.`trans_no`=ca.`trans_no_to`
  95 SET ca.discount=pay.ov_discount
  96         WHERE pay.ov_discount != 0 AND pay.ov_amount+pay.ov_discount = trans.ov_amount+trans.ov_gst+trans.ov_freight+trans.ov_freight_tax;
  97
  98 UPDATE `0_supp_allocations` sa
  99         LEFT JOIN `0_supp_trans` pay ON pay.`type`=sa.`trans_type_from` AND pay.`trans_no`=sa.`trans_no_from`
 100         LEFT JOIN `0_supp_trans` trans ON trans.`type`=sa.`trans_type_to` AND trans.`trans_no`=sa.`trans_no_to`
 101 SET sa.discount=pay.ov_discount
 102         WHERE pay.ov_discount != 0 AND pay.ov_amount+pay.ov_discount = trans.ov_amount+trans.ov_gst;
 103
 104 # bank charge stored in bank_trans
 105 ALTER TABLE `0_bank_trans` ADD COLUMN `charge` double DEFAULT 0 AFTER `amount`;
 106
 107 UPDATE `0_bank_trans` bt
 108         LEFT JOIN (SELECT trans.type, trans.trans_no, IF(act.bank_curr_code=home_curr.value, charge.amount,
 109                 IF(act.bank_curr_code=debtor.curr_code, -(trans.amount-ar.ov_amount+ar.ov_discount),
 110                 IFNULL(charge.amount*trans.amount/pmt.amount, 0))) amount
 111                 FROM 0_bank_trans trans
 112                         LEFT JOIN `0_bank_accounts` act ON trans.bank_act=act.id
 113                         LEFT JOIN `0_sys_prefs` charge_act ON charge_act.name='bank_charge_act'
 114                         LEFT JOIN `0_sys_prefs` home_curr ON home_curr.name='curr_default'
 115                         LEFT JOIN `0_gl_trans` charge ON charge.type=trans.`type` AND charge.type_no=trans.trans_no AND charge.account=charge_act.`value`
 116                         LEFT JOIN `0_gl_trans` pmt ON pmt.type=trans.type AND pmt.type_no=trans.trans_no AND pmt.account=act.account_code
 117                         LEFT JOIN `0_debtors_master` debtor ON trans.person_id=debtor.debtor_no AND trans.person_type_id=2
 118                         LEFT JOIN `0_debtor_trans` ar ON trans.type=ar.`type` AND trans.trans_no=ar.trans_no
 119                 WHERE pmt.amount!=0 AND charge.amount!=0 AND trans.amount!=0) charges ON bt.`type`=charges.`type` AND bt.`trans_no`=charges.`trans_no`
 120         SET bt.charge=IFNULL(-charges.amount,0),
 121                 bt.amount=bt.amount+IFNULL(charges.amount,0);