
CREATE DATABASE IF NOT EXISTS ssp;
USE ssp;

-- ---------------------
-- Item master
-- ---------------------

CREATE TABLE  `ssp`.`item` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
-- General --
  `code` varchar(30) NOT NULL,
  `ref_code` varchar(30) DEFAULT NULL, -- To store search / old code (default to same as itemcode)
  `description` varchar(60) DEFAULT NULL,
  `type` char(1) NOT NULL, -- Pre-defined types
  `status` char(1) NOT NULL, -- Pre-defined item status
  `item_group_id` int(10) unsigned NOT NULL, -- User defined category - compulsary
  `item_group_01_id` int(10) unsigned DEFAULT NULL, -- User defined category 1 - optional
  `item_group_02_id` int(10) unsigned DEFAULT NULL, -- User defined category 2 - optional
  `uom_id` int(10) unsigned NOT NULL, -- Default uom
  `purchase_uom_id` int(10) unsigned NOT NULL, -- Default uom for purchase
  `sales_uom_id` int(10) unsigned NOT NULL, -- Default uom for sales
  `location_id` int(10) unsigned NOT NULL, -- Default location
  `shelf_no` varchar(15) DEFAULT NULL, -- Default shelf
  `shelf_life` int(3) unsigned DEFAULT '0.0000',
-- Attributes --
  `is_sales_item` tinyint(1) DEFAULT NULL,
  `is_purchase_item` tinyint(1) DEFAULT NULL,
  `is_service_item` tinyint(1) DEFAULT NULL,
  `is_stock_item` tinyint(1) DEFAULT NULL,
  `is_serialno_item` tinyint(1) DEFAULT NULL,
  `is_batch_item` tinyint(1) DEFAULT NULL,
  `is_part_item` tinyint(1) DEFAULT NULL,
  `is_divisible` tinyint(1) DEFAULT NULL,
  `is_discount` tinyint(1) DEFAULT NULL,
  `is_explode` tinyint(1) DEFAULT NULL,
-- Barcode --
  `barcode` varchar(60) DEFAULT NULL,
  `barcode_type` varchar(30) DEFAULT NULL,
-- Stock settings --
  `stock_currency_id` int(10) unsigned NOT NULL,
  `stock_valuation_type` char(4) DEFAULT NULL,
-- Tax settings --
  `sales_tax_code` varchar(10) DEFAULT NULL, -- Default tax code for sales
  `purchase_tax_code` varchar(10) DEFAULT NULL, -- Default tax code for purchase
-- GL settings --
  `gl_account_sales` varchar(20) DEFAULT NULL,
  `gl_account_salesreturn` varchar(20) DEFAULT NULL,
  `gl_account_cashsales` varchar(20) DEFAULT NULL,
  `gl_account_purchase` varchar(20) DEFAULT NULL,
  `gl_account_cashpurchase` varchar(20) DEFAULT NULL,
  `gl_account_purchasereturn` varchar(20) DEFAULT NULL,
-- Packing / measurements info --
  `gross_weight` decimal(11,4) unsigned DEFAULT NULL,
  `gross_weight_uom_id` int(10) unsigned DEFAULT NULL,
  `net_weight` decimal(11,4) unsigned DEFAULT NULL,
  `net_weight_uom_id` int(10) unsigned DEFAULT NULL,
  `thickness` decimal(11,4) unsigned DEFAULT NULL,
  `thickness_uom_id` varchar(10) DEFAULT NULL,
  `length` decimal(11,4) unsigned DEFAULT NULL,
  `length_uom_id` int(10) unsigned DEFAULT NULL,
  `width` decimal(11,4) unsigned DEFAULT NULL,
  `width_uom_id` int(10) unsigned DEFAULT NULL,
  `height` decimal(11,4) unsigned DEFAULT NULL,
  `height_uom_id` int(10) unsigned DEFAULT NULL,
  `density` decimal(11,4) unsigned DEFAULT NULL,
  `dimension` varchar(60) DEFAULT NULL,
-- Free fields -- each 4 fields
  `userfield_01` varchar(255) DEFAULT NULL,
  `userfield_02` varchar(255) DEFAULT NULL,
  `userfield_03` varchar(255) DEFAULT NULL,
  `userfield_04` varchar(255) DEFAULT NULL,
  `usernumber_01` decimal(11,4) DEFAULT NULL,
  `usernumber_02` decimal(11,4) DEFAULT NULL,
  `usernumber_03` decimal(11,4) DEFAULT NULL,
  `usernumber_04` decimal(11,4) DEFAULT NULL,
  `useryesno_01` tinyint(1) DEFAULT NULL,
  `useryesno_02` tinyint(1) DEFAULT NULL,
  `useryesno_03` tinyint(1) DEFAULT NULL,
  `useryesno_04` tinyint(1) DEFAULT NULL,
-- Others --
  `description1` varchar(60) DEFAULT NULL,
  `description2` varchar(60) DEFAULT NULL,
  `longdescription1` varchar(300) DEFAULT NULL,
  `longdescription2` varchar(300) DEFAULT NULL,
  `percentage1` decimal(11,4) DEFAULT NULL,
  `percentage2` decimal(11,4) DEFAULT NULL,
  `picture` mediumblob,
-- Log --
  `created` int(10) NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` int(10) NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ------------------------------------------------------------------
-- Item uom - to handle multi-uom and at least 1 record for any item
-- ------------------------------------------------------------------

CREATE TABLE  `ssp`.`item_uom` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `item_id` int(10) unsigned NOT NULL,
  `uom_id` int(10) unsigned NOT NULL,
  `uom_rate` decimal(11,4) unsigned NOT NULL DEFAULT '1.0000', -- Base rate always 1.0000
  -- Cost valuation --
  `uptodate_cost` decimal(11,4) DEFAULT '0.0000', -- To store the up-to-date cost by costing method
  `average_cost` decimal(11,4) DEFAULT '0.0000', -- To store the average cost by costing method
  -- Planning --
  `normal_level` decimal(11,4) DEFAULT '0.0000',
  `reorder_Level` decimal(11,4) DEFAULT '0.0000',
  `reorder_qty` decimal(11,4) DEFAULT '0.0000',
  `min_order_qty` decimal(11,4) DEFAULT '0.0000',
-- GL settings - uom level --f
  `gl_account_sales` varchar(20) DEFAULT NULL,
  `gl_account_salesreturn` varchar(20) DEFAULT NULL,
  `gl_account_cashsales` varchar(20) DEFAULT NULL,
  `gl_account_purchase` varchar(20) DEFAULT NULL,
  `gl_account_cashpurchase` varchar(20) DEFAULT NULL,
  `gl_account_purchasereturn` varchar(20) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ------------------------------------------------------------------
-- Maintain item divisions
-- ------------------------------------------------------------------

CREATE TABLE  `ssp`.`item_division` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `division_id` int(10) unsigned NOT NULL, -- division
-- GL settings - division level --
  `gl_account_sales` varchar(20) DEFAULT NULL,
  `gl_account_salesreturn` varchar(20) DEFAULT NULL,
  `gl_account_cashsales` varchar(20) DEFAULT NULL,
  `gl_account_purchase` varchar(20) DEFAULT NULL,
  `gl_account_cashpurchase` varchar(20) DEFAULT NULL,
  `gl_account_purchasereturn` varchar(20) DEFAULT NULL,
-- Division related --
  `userfield_01` varchar(255) DEFAULT NULL,
  `userfield_02` varchar(255) DEFAULT NULL,
  `usernumber_01` decimal(11,4) DEFAULT NULL,
  `usernumber_02` decimal(11,4) DEFAULT NULL,
  `useryesno_01` tinyint(1) DEFAULT NULL,
  `useryesno_02` tinyint(1) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ------------------------------------------------------------------
-- Maintain item groups
-- ------------------------------------------------------------------

CREATE TABLE  `ssp`.`item_group` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `description` varchar(60) DEFAULT NULL,
-- GL settings - item group level --
  `gl_account_sales` varchar(20) DEFAULT NULL,
  `gl_account_salesreturn` varchar(20) DEFAULT NULL,
  `gl_account_cashsales` varchar(20) DEFAULT NULL,
  `gl_account_purchase` varchar(20) DEFAULT NULL,
  `gl_account_cashpurchase` varchar(20) DEFAULT NULL,
  `gl_account_purchasereturn` varchar(20) DEFAULT NULL,
-- Group related --
  `userfield_01` varchar(255) DEFAULT NULL,
  `userfield_02` varchar(255) DEFAULT NULL,
  `usernumber_01` decimal(11,4) DEFAULT NULL,
  `usernumber_02` decimal(11,4) DEFAULT NULL,
  `useryesno_01` tinyint(1) DEFAULT NULL,
  `useryesno_02` tinyint(1) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ------------------------------------------------------------------
-- Maintain item groups (1) optional
-- ------------------------------------------------------------------

CREATE TABLE  `ssp`.`item_group_1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `description` varchar(60) DEFAULT NULL,
-- GL settings - item group level --
  `gl_account_sales` varchar(20) DEFAULT NULL,
  `gl_account_salesreturn` varchar(20) DEFAULT NULL,
  `gl_account_cashsales` varchar(20) DEFAULT NULL,
  `gl_account_purchase` varchar(20) DEFAULT NULL,
  `gl_account_cashpurchase` varchar(20) DEFAULT NULL,
  `gl_account_purchasereturn` varchar(20) DEFAULT NULL,
-- Group related --
  `userfield_01` varchar(255) DEFAULT NULL,
  `userfield_02` varchar(255) DEFAULT NULL,
  `usernumber_01` decimal(11,4) DEFAULT NULL,
  `usernumber_02` decimal(11,4) DEFAULT NULL,
  `useryesno_01` tinyint(1) DEFAULT NULL,
  `useryesno_02` tinyint(1) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ------------------------------------------------------------------
-- Maintain item groups (2) optional
-- ------------------------------------------------------------------

CREATE TABLE  `ssp`.`item_group_2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `description` varchar(60) DEFAULT NULL,
-- GL settings - item group level --
  `gl_account_sales` varchar(20) DEFAULT NULL,
  `gl_account_salesreturn` varchar(20) DEFAULT NULL,
  `gl_account_cashsales` varchar(20) DEFAULT NULL,
  `gl_account_purchase` varchar(20) DEFAULT NULL,
  `gl_account_cashpurchase` varchar(20) DEFAULT NULL,
  `gl_account_purchasereturn` varchar(20) DEFAULT NULL,
-- Group related --
  `userfield_01` varchar(255) DEFAULT NULL,
  `userfield_02` varchar(255) DEFAULT NULL,
  `usernumber_01` decimal(11,4) DEFAULT NULL,
  `usernumber_02` decimal(11,4) DEFAULT NULL,
  `useryesno_01` tinyint(1) DEFAULT NULL,
  `useryesno_02` tinyint(1) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ------------------------------------------------------------------
-- Maintain item cost
-- ------------------------------------------------------------------

CREATE TABLE  `ssp`.`item_cost` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `item_id` int(10) unsigned NOT NULL,
  `vendor_id` int(10) unsigned NOT NULL, -- vendor code mandatory
  `division_id` int(10) unsigned DEFAULT NULL, -- division
  `uom_id` int(10) unsigned NOT NULL,
  `currency_id` int(10) unsigned NOT NULL,
  `unit_price` decimal(11,4) unsigned NOT NULL DEFAULT '0.0000',
  `valid_from` datetime NOT NULL,
  `valid_to` datetime NOT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ------------------------------------------------------------------
-- Maintain item price
-- ------------------------------------------------------------------

CREATE TABLE  `ssp`.`item_price` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `item_id` int(10) unsigned NOT NULL,
  `customer_id` int(10) unsigned NOT NULL,  -- customer code mandatory
  `division_id` int(10) unsigned DEFAULT NULL, -- division
  `uom_id` int(10) unsigned NOT NULL,
  `currency_id` int(10) unsigned NOT NULL,
  `unit_price` decimal(11,4) unsigned NOT NULL DEFAULT '0.0000',
  `valid_from` datetime NOT NULL,
  `valid_to` datetime NOT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ------------------------------------------------------------------
-- Maintain item BOM
-- ------------------------------------------------------------------

CREATE TABLE  `ssp`.`item_bom` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `description` varchar(60) DEFAULT NULL,
  `parent_item_id` int(10) unsigned NOT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE  `ssp`.`item_bom_list` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_bom_id` int(10) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `bom_type` char(1) NOT NULL, -- Pre-defined types
  `usage_qty` decimal(11,4) DEFAULT '0.0000',
  `uom` int(10) unsigned NOT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ------------------------------------------------------------------
-- Inventory
-- ------------------------------------------------------------------

CREATE TABLE  `ssp`.`inventory` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `item_id` int(10) unsigned NOT NULL,
  `item_code` varchar(30) NOT NULL,
  `uom_id` int(10) unsigned NOT NULL,
  `location_id` int(10) unsigned NOT NULL, -- Warehouse of item stock
  `shelf_no` varchar(15) DEFAULT NULL, -- Shelf of item to store
  `serial_no` varchar(30) DEFAULT NULL, -- Item seial no
  `batch_no` varchar(30) DEFAULT NULL, -- Item lot/batch no
  `expiry_date` datetime NOT NULL, -- expiry date / transaction date , to determine lifo/fifo stock outbound
  `ob_qty` decimal(11,4) unsigned NOT NULL DEFAULT '0.0000', -- Opening qty
  `ob_currency` int(10) unsigned NOT NULL, -- Opening cost currency
  `ob_cost` decimal(11,4) unsigned NOT NULL DEFAULT '0.0000', -- Opening cost
  `balance_qty` decimal(11,4) unsigned NOT NULL DEFAULT '0.0000', -- Qty on hand
  `reserved_qty` decimal(11,4) unsigned NOT NULL DEFAULT '0.0000', -- Qty reserved for some purpose
  -- Hence avalaible qty = balanceqty - reservedqty
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ------------------------------------------------------------------
-- Stock transaction posting
-- ------------------------------------------------------------------

CREATE TABLE  `ssp`.`stk_trans` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `post_date` datetime NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `item_code` varchar(30) NOT NULL,
  `uom_id` int(10) unsigned NOT NULL,
  `uom_rate` decimal(11,4) NOT NULL,
  `location_id` int(10) unsigned NOT NULL, -- Posting from location
  `shelf_no` varchar(15) DEFAULT NULL, -- Shelf of item to store
  `serial_no` varchar(30) DEFAULT NULL, -- Item seial no
  `batch_no` varchar(30) DEFAULT NULL, -- Item lot/batch no to post
  `doc_type` char(2) NOT NULL, -- What document type this tranx post from
  `doc_no` varchar(50) NOT NULL, -- Where this tranx post from
  `doc_date` datetime NOT NULL, -- The document date
  `ref_no_01` varchar(50) DEFAULT NULL, -- Any reference no. eg. supplier d.o no.
  `ref_no_02` varchar(50) DEFAULT NULL, -- extra ref no
  `description` varchar(100) DEFAULT NULL, -- To describe this posting
  `sign` int(1) NOT NULL, -- 1 for +stock and -1 for -stock
  `qty` decimal(11,4) NOT NULL,
  `currency_id` int(10) unsigned NOT NULL, -- cost currency
  `cost` decimal(11,4) NOT NULL,
  `total_cost` decimal(11,4) NOT NULL, -- with other cost, eg. delivery cost etc.
  `stock_valuation_type` char(4) DEFAULT NULL, -- to use for calculate and update cost in inventory after posting
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- Warehouse/Location
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`location` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(15) NOT NULL,
  `description` varchar(60) DEFAULT NULL,
  `is_stock_location` tinyint(1) DEFAULT NULL, -- false = not take count in inventory
  `is_transitory` tinyint(1) DEFAULT NULL, -- true = can be used in transit
  `is_active` tinyint(1) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- shelf setup??


-- ----------------------------------------------------------------------
-- Unit of Measurement - Lookup
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`uom` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `description` varchar(60) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------------------------------------------------
-- Area - Lookup
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`area` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `description` varchar(60) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- Terms - Lookup
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`terms` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL,
  `description` varchar(60) DEFAULT NULL,
  `type_id` int(10) unsigned NOT NULL,
  `period` int(2) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `ssp`.`terms_type` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL,
  `description` varchar(60) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `ssp`.`terms_type` (`id`,`code`,`description`) VALUES 
 ('1','MTHEND','Due in the month end'),
 ('2','SPECDATE','Due on specific day of month'),
 ('3','DAYS','Due in number of days'),
 ('4','MONTHS','Due in number of months'),
 ('5','C.O.D','Cash On Delivery');

-- ----------------------------------------------------------------------
-- Currency
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`currency` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `description` varchar(60) DEFAULT NULL,
  `symbol` varchar(6) NOT NULL,
  `gl_account_loss` varchar(20) DEFAULT NULL,
  `gl_account_gain` varchar(20) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE  `ssp`.`currency_rate` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `currency_id` int(10) unsigned NOT NULL,
  `valid_from` datetime NOT NULL,
  `valid_to` datetime NOT NULL,
  `rate` decimal(11,4) NOT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------------------------------------------------
-- Customer master
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`customer` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(15) NOT NULL,
  `ref_code` varchar(30) DEFAULT NULL, -- To store search / old code
  `company_name` varchar(100) NOT NULL,
  `company_name_01` varchar(100) DEFAULT NULL,
  `category_id` int(10) unsigned NOT NULL,
  `area_id` int(10) unsigned NOT NULL,
  `agent_id` int(10) unsigned DEFAULT NULL,
  `term_id` int(10) unsigned NOT NULL,
  `currency_id` int(10) unsigned NOT NULL,
  `registration_no` varchar(50) DEFAULT NULL,
  `business_nature` varchar(200) DEFAULT NULL,
  `address_01` varchar(100) NOT NULL,
  `address_02` varchar(100) DEFAULT NULL,
  `address_03` varchar(100) DEFAULT NULL,
  `address_04` varchar(100) DEFAULT NULL,
  `attention` varchar(80) DEFAULT NULL,
  `postcode` varchar(10) DEFAULT NULL,
  `phone_01` varchar(30) DEFAULT NULL,
  `phone_02` varchar(30) DEFAULT NULL,
  `fax_01` varchar(30) DEFAULT NULL,
  `fax_02` varchar(30) DEFAULT NULL,
  `del_address_01` varchar(100) DEFAULT NULL,
  `del_address_02` varchar(100) DEFAULT NULL,
  `del_address_03` varchar(100) DEFAULT NULL,
  `del_address_04` varchar(100) DEFAULT NULL,
  `del_attention` varchar(80) DEFAULT NULL,
  `del_postcode` varchar(10) DEFAULT NULL,
  `del_phone_01` varchar(30) DEFAULT NULL,
  `del_phone_02` varchar(30) DEFAULT NULL,
  `del_fax_01` varchar(30) DEFAULT NULL,
  `del_fax_02` varchar(30) DEFAULT NULL,
  `email` varchar(60) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT NULL,
  `tax_exempt_no` varchar(50) DEFAULT NULL,
  `tax_exp_date` datetime DEFAULT NULL,
  `website` varchar(50) DEFAULT NULL,
  `allow_exceed_credit_limit` tinyint(1) DEFAULT NULL,
  `overdue_limit` decimal(11,4) unsigned NOT NULL DEFAULT '0.0000',
  `credit_limit` decimal(11,4) unsigned NOT NULL DEFAULT '0.0000',
  `credit_limit_statement` varchar(200) DEFAULT NULL,
  `overdue_limit_statement` varchar(200) DEFAULT NULL,
  `overdue_terms_statement` varchar(200) DEFAULT NULL,
  `overdue_warning_statement` varchar(200) DEFAULT NULL,
  `overdue_critical_statement` varchar(200) DEFAULT NULL,
  `logo` mediumblob,
  `remark` varchar(200) DEFAULT NULL,
-- Free fields -- each 3 fields
  `userfield_01` varchar(255) DEFAULT NULL,
  `userfield_02` varchar(255) DEFAULT NULL,
  `userfield_03` varchar(255) DEFAULT NULL,
  `usernumber_01` decimal(11,4) DEFAULT NULL,
  `usernumber_02` decimal(11,4) DEFAULT NULL,
  `usernumber_03` decimal(11,4) DEFAULT NULL,
  `useryesno_01` tinyint(1) DEFAULT NULL,
  `useryesno_02` tinyint(1) DEFAULT NULL,
  `useryesno_03` tinyint(1) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  `ssp`.`customer_branch` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) unsigned NOT NULL,
  `code` varchar(15) DEFAULT NULL,
  `area_id` int(10) unsigned DEFAULT NULL,
  `address_01` varchar(100) NOT NULL,
  `address_02` varchar(100) DEFAULT NULL,
  `address_03` varchar(100) DEFAULT NULL,
  `address_04` varchar(100) DEFAULT NULL,
  `postcode` varchar(10) DEFAULT NULL,
  `attention` varchar(80) DEFAULT NULL,
  `phone_01` varchar(30) DEFAULT NULL,
  `phone_02` varchar(30) DEFAULT NULL,
  `fax_01` varchar(30) DEFAULT NULL,
  `fax_02` varchar(30) DEFAULT NULL,
  `del_address_01` varchar(100) DEFAULT NULL,
  `del_address_02` varchar(100) DEFAULT NULL,
  `del_address_03` varchar(100) DEFAULT NULL,
  `del_address_04` varchar(100) DEFAULT NULL,
  `del_attention` varchar(80) DEFAULT NULL,
  `del_postcode` varchar(10) DEFAULT NULL,
  `del_phone_01` varchar(30) DEFAULT NULL,
  `del_phone_02` varchar(30) DEFAULT NULL,
  `del_fax_01` varchar(30) DEFAULT NULL,
  `del_fax_02` varchar(30) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  `ssp`.`customer_contact` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `department` varchar(60) DEFAULT NULL,
  `title` varchar(60) DEFAULT NULL,
  `mobile_phone` varchar(30) DEFAULT NULL,
  `direct_phone` varchar(30) DEFAULT NULL,
  `direct_fax` varchar(30) DEFAULT NULL,
  `email` varchar(60) DEFAULT NULL,
  `im` varchar(60) DEFAULT NULL,
  `remark` varchar(200) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  `ssp`.`customer_category` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `description` varchar(60) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ------------------------------------------------------------------
-- Debtor opening balance
-- ------------------------------------------------------------------

CREATE TABLE  `ssp`.`customer_ob` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) unsigned NOT NULL,
  `customer_code` varchar(30) NOT NULL,
  `ob_amount` decimal(11,4) unsigned NOT NULL DEFAULT '0.0000', -- Opening amount
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- Vendor master
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`vendor` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(15) NOT NULL,
  `ref_code` varchar(30) DEFAULT NULL, -- To store search / old code
  `company_name` varchar(100) NOT NULL,
  `company_name_01` varchar(100) DEFAULT NULL,
  `category_id` int(10) unsigned NOT NULL,
  `area_id` int(10) unsigned NOT NULL,
  `agent_id` int(10) unsigned DEFAULT NULL,
  `term_id` int(10) unsigned NOT NULL,
  `currency_id` int(10) unsigned NOT NULL,
  `registration_no` varchar(50) DEFAULT NULL,
  `business_nature` varchar(200) DEFAULT NULL,
  `address_01` varchar(100) NOT NULL,
  `address_02` varchar(100) DEFAULT NULL,
  `address_03` varchar(100) DEFAULT NULL,
  `address_04` varchar(100) DEFAULT NULL,
  `attention` varchar(80) DEFAULT NULL,
  `postcode` varchar(10) DEFAULT NULL,
  `phone_01` varchar(30) DEFAULT NULL,
  `phone_02` varchar(30) DEFAULT NULL,
  `fax_01` varchar(30) DEFAULT NULL,
  `fax_02` varchar(30) DEFAULT NULL,
  `email` varchar(60) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT NULL,
  `tax_exempt_no` varchar(50) DEFAULT NULL,
  `tax_exp_date` datetime DEFAULT NULL,
  `website` varchar(50) DEFAULT NULL,
  `allow_exceed_credit_limit` tinyint(1) DEFAULT NULL,
  `overdue_limit` decimal(11,4) unsigned NOT NULL DEFAULT '0.0000',
  `credit_limit` decimal(11,4) unsigned NOT NULL DEFAULT '0.0000',
  `logo` mediumblob,
  `remark` varchar(200) DEFAULT NULL,
-- Free fields -- each 3 fields
  `userfield_01` varchar(255) DEFAULT NULL,
  `userfield_02` varchar(255) DEFAULT NULL,
  `userfield_03` varchar(255) DEFAULT NULL,
  `usernumber_01` decimal(11,4) DEFAULT NULL,
  `usernumber_02` decimal(11,4) DEFAULT NULL,
  `usernumber_03` decimal(11,4) DEFAULT NULL,
  `useryesno_01` tinyint(1) DEFAULT NULL,
  `useryesno_02` tinyint(1) DEFAULT NULL,
  `useryesno_03` tinyint(1) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE  `ssp`.`vendor_branch` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_id` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `code` varchar(21) DEFAULT NULL,
  `area_id` int(10) unsigned DEFAULT NULL,
  `address_01` varchar(100) NOT NULL,
  `address_02` varchar(100) DEFAULT NULL,
  `address_03` varchar(100) DEFAULT NULL,
  `address_04` varchar(100) DEFAULT NULL,
  `postcode` varchar(10) DEFAULT NULL,
  `attention` varchar(80) DEFAULT NULL,
  `phone_01` varchar(30) DEFAULT NULL,
  `phone_02` varchar(30) DEFAULT NULL,
  `fax_01` varchar(30) DEFAULT NULL,
  `fax_02` varchar(30) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  `ssp`.`vendor_contact` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_id` int(10) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `department` varchar(60) DEFAULT NULL,
  `title` varchar(60) DEFAULT NULL,
  `mobile_phone` varchar(30) DEFAULT NULL,
  `direct_phone` varchar(30) DEFAULT NULL,
  `direct_fax` varchar(30) DEFAULT NULL,
  `email` varchar(60) DEFAULT NULL,
  `im` varchar(60) DEFAULT NULL,
  `remark` varchar(200) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  `ssp`.`vendor_category` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `description` varchar(60) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ------------------------------------------------------------------
-- Creditor opening balance
-- ------------------------------------------------------------------

CREATE TABLE  `ssp`.`vendor_ob` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_id` int(10) unsigned NOT NULL,
  `vendor_code` varchar(30) NOT NULL,
  `ob_amount` decimal(11,4) unsigned NOT NULL DEFAULT '0.0000', -- Opening amount
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ------------------------------------------------------------------
-- Debtor transactions
-- ------------------------------------------------------------------

CREATE TABLE  `ssp`.`debtor_trans` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) unsigned NOT NULL,
  `customer_code` varchar(30) NOT NULL,
  `customer_name` varchar(30) NOT NULL,
  `term_id` int(10) unsigned NOT NULL,
  `term_day` int(10) NOT NULL,
  `term_code` varchar(10) NOT NULL,
  `document_type` char(1) NOT NULL,
  `document_id` int(10) unsigned NOT NULL,
  `document_code` varchar(30) NOT NULL,
  `document_date` datetime NOT NULL,
  `sign` int(1) NOT NULL, -- 1 for +amt and -1 for -amt
  `amount` decimal(11,4) NOT NULL,
  `paid_amount` decimal(11,4) NOT NULL,
  `currency_id` int(10) unsigned NOT NULL,
  `currency_code` varchar(10) NOT NULL,
  `currency_rate` decimal(11,4) NOT NULL,
  `salesman_code` varchar(30) DEFAULT NULL,
  `refno1` varchar(30) DEFAULT NULL,
  `refno2` varchar(30) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ------------------------------------------------------------------
-- Creditor transactions
-- ------------------------------------------------------------------

CREATE TABLE  `ssp`.`creditor_trans` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_id` int(10) unsigned NOT NULL,
  `vendor_code` varchar(30) NOT NULL,
  `vendor_name` varchar(30) NOT NULL,
  `term_id` int(10) unsigned NOT NULL,
  `term_day` int(5) NOT NULL,
  `term_code` varchar(10) NOT NULL,
  `document_type` char(1) NOT NULL,
  `document_id` int(10) unsigned NOT NULL,
  `document_code` varchar(30) NOT NULL,
  `document_date` datetime NOT NULL,
  `sign` int(1) NOT NULL, -- 1 for +amt and -1 for -amt
  `amount` decimal(11,4) NOT NULL,
  `paid_amount` decimal(11,4) NOT NULL,
  `currency_id` int(10) unsigned NOT NULL,
  `currency_code` varchar(10) NOT NULL,
  `currency_rate` decimal(11,4) NOT NULL,
  `purchaser_code` varchar(30) DEFAULT NULL,
  `refno1` varchar(30) DEFAULT NULL,
  `refno2` varchar(30) DEFAULT NULL,
-- Log --
  `ureated` datetime NOT NULL,
  `ureatedby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- Tax
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`tax_group` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `description` varchar(60) DEFAULT NULL,
  `inclusive` tinyint(1) DEFAULT NULL,
  `rate` decimal(11,4) NOT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  `ssp`.`tax` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tax_group_id` int(10) unsigned NOT NULL,
  `code` varchar(10) NOT NULL,
  `description` varchar(60) DEFAULT NULL,
  `gl_account_tax` varchar(20) DEFAULT NULL,
  `non_taxable` tinyint(1) DEFAULT NULL,
  `tax_on_tax` tinyint(1) DEFAULT NULL,
  `rate` decimal(11,4) NOT NULL,
  `net_rate` decimal(11,4) NOT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ----------------------------------------------------------------------
-- Promotion
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`promotion_grp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `description` varchar(60) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  `ssp`.`promotion_line` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `promotion_grp_id` int(10) unsigned NOT NULL,
  `line_no` int(4) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  -- `location_id` int(10) unsigned NOT NULL,
  -- `shelf_no` varchar(15) DEFAULT NULL,
  -- `batch_no` varchar(30) DEFAULT NULL,
  -- `promo_code` varchar(30) DEFAULT NULL,
  -- `expiry_date` datetime DEFAULT NULL,
  -- `del_date` datetime DEFAULT NULL,
  `qty` decimal(14,4) unsigned NOT NULL DEFAULT '0.0000',
  -- `sent_qty` decimal(14,4) unsigned NOT NULL DEFAULT '0.0000',
  `price` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `uom` varchar(20) NOT NULL DEFAULT '',
  `uom_rate` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `disc_percent_01` decimal(5,2) DEFAULT '0.00',
  `disc_percent_02` decimal(5,2) DEFAULT '0.00',
  `disc_percent_03` decimal(5,2) DEFAULT '0.00',
  `disc_percent_04` decimal(5,2) DEFAULT '0.00',
  `disc_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `tax_group_id` int(10) unsigned NOT NULL,
  `tax_percent_01` decimal(5,2) DEFAULT '0.00',
  `tax_percent_02` decimal(5,2) DEFAULT '0.00',
  `tax_percent_03` decimal(5,2) DEFAULT '0.00',
  `tax_percent_04` decimal(5,2) DEFAULT '0.00',
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- Project
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`project` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `description` varchar(60) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- Sales order
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`sales_odr_hdr` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `doc_code` varchar(30) NOT NULL,
  `ref_code` varchar(30) DEFAULT NULL,
  `doc_date` datetime NOT NULL,
  `order_type` char(1) NOT NULL, -- order type : Mobile/Web/??
  `company_id` int(10) unsigned NOT NULL,
  `customer_id` int(10) unsigned NOT NULL DEFAULT '0',
  `salesman_id` int(10) unsigned NOT NULL DEFAULT '0',
  `project_id` int(10) unsigned DEFAULT NULL,
  `division_id` int(10) unsigned NOT NULL DEFAULT '0',
  `term_id` int(10) unsigned NOT NULL,
  `term_code` varchar(10) NOT NULL,
  `term_day` int(5) NOT NULL,
  `branch_id` int(10) unsigned NOT NULL DEFAULT '0',
  `branch_code` varchar(20) DEFAULT '',
  `area_id` int(10) unsigned NOT NULL DEFAULT '0',
  `area_code` varchar(20) DEFAULT '',
  `del_address_01` varchar(100) DEFAULT NULL,
  `del_address_02` varchar(100) DEFAULT NULL,
  `del_address_03` varchar(100) DEFAULT NULL,
  `del_address_04` varchar(100) DEFAULT NULL,
  `del_attention` varchar(80) DEFAULT NULL,
  `del_postcode` varchar(10) DEFAULT NULL,
  `del_phone_01` varchar(30) DEFAULT NULL,
  `del_phone_02` varchar(30) DEFAULT NULL,
  `del_fax_01` varchar(30) DEFAULT NULL,
  `del_fax_02` varchar(30) DEFAULT NULL,
  `currency_rate` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `currency_id` int(10) unsigned NOT NULL,
  `disc_percent_01` decimal(5,2) DEFAULT '0.00',
  `disc_percent_02` decimal(5,2) DEFAULT '0.00',
  `disc_percent_03` decimal(5,2) DEFAULT '0.00',
  `disc_percent_04` decimal(5,2) DEFAULT '0.00',
  `disc_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `tax_percent_01` decimal(5,2) DEFAULT '0.00',
  `tax_percent_02` decimal(5,2) DEFAULT '0.00',
  `tax_percent_03` decimal(5,2) DEFAULT '0.00',
  `tax_percent_04` decimal(5,2) DEFAULT '0.00',
  `tax_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `order_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `net_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `remark_01` varchar(200) DEFAULT NULL,
  `remark_02` varchar(200) DEFAULT NULL,
  `is_printed` tinyint(1) DEFAULT NULL,
  `status` int(2) NOT NULL DEFAULT 0, -- status : 0, draft, 1, processed, 99, completed
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  `ssp`.`sales_odr_dtl` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hdr_id` int(10) unsigned NOT NULL,
  `line_no` int(4) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `location_id` int(10) unsigned NOT NULL,
  `shelf_no` varchar(15) DEFAULT NULL,
  `batch_no` varchar(30) DEFAULT NULL,
  `promo_code` varchar(30) DEFAULT NULL,
  `expiry_date` datetime DEFAULT NULL,
  `del_date` datetime DEFAULT NULL,
  `qty` decimal(14,4) unsigned NOT NULL DEFAULT '0.0000',
  `sent_qty` decimal(14,4) unsigned NOT NULL DEFAULT '0.0000',
  `price` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `uom` varchar(20) NOT NULL DEFAULT '',
  `uom_rate` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `disc_percent_01` decimal(5,2) DEFAULT '0.00',
  `disc_percent_02` decimal(5,2) DEFAULT '0.00',
  `disc_percent_03` decimal(5,2) DEFAULT '0.00',
  `disc_percent_04` decimal(5,2) DEFAULT '0.00',
  `disc_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `tax_group_id` int(10) unsigned NOT NULL,
  `tax_percent_01` decimal(5,2) DEFAULT '0.00',
  `tax_percent_02` decimal(5,2) DEFAULT '0.00',
  `tax_percent_03` decimal(5,2) DEFAULT '0.00',
  `tax_percent_04` decimal(5,2) DEFAULT '0.00',
  `tax_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `net_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `remark` varchar(200) DEFAULT NULL,
  `is_closed` tinyint(1) DEFAULT NULL, -- only true when sent qty = qty
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- ------------------------------------------------------------------
-- Delivery order (+Invoice,Picking,Shipment)
-- ------------------------------------------------------------------

CREATE TABLE  `ssp`.`do_inv` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `do_code` varchar(30) NOT NULL, -- delivery order number
  `inv_code` varchar(30) DEFAULT NULL, -- filled in when invoice generated
  `picking_code` varchar(30) DEFAULT NULL, -- filled in when is picked
  `shipment_code` varchar(30) DEFAULT NULL, -- filled in when is shipped
  `ref_code` varchar(30) DEFAULT NULL,
  `doc_date` datetime NOT NULL,
  `order_type` char(1) NOT NULL, -- order type : Mobile/Web/??
  `company_id` int(10) unsigned NOT NULL,
  `customer_id` int(10) unsigned NOT NULL DEFAULT '0',
  `salesman_id` int(10) unsigned NOT NULL DEFAULT '0',
  `project_id` int(10) unsigned DEFAULT NULL,
  `division_id` int(10) unsigned NOT NULL DEFAULT '0',
  `term_id` int(10) unsigned NOT NULL,
  `term_code` varchar(10) NOT NULL,
  `term_day` int(5) NOT NULL,
  `branch_id` int(10) unsigned NOT NULL DEFAULT '0',
  `branch_code` varchar(20) DEFAULT '',
  `area_id` int(10) unsigned NOT NULL DEFAULT '0',
  `area_code` varchar(20) DEFAULT '',
  `del_address_01` varchar(100) DEFAULT NULL,
  `del_address_02` varchar(100) DEFAULT NULL,
  `del_address_03` varchar(100) DEFAULT NULL,
  `del_address_04` varchar(100) DEFAULT NULL,
  `del_attention` varchar(80) DEFAULT NULL,
  `del_postcode` varchar(10) DEFAULT NULL,
  `del_phone_01` varchar(30) DEFAULT NULL,
  `del_phone_02` varchar(30) DEFAULT NULL,
  `del_fax_01` varchar(30) DEFAULT NULL,
  `del_fax_02` varchar(30) DEFAULT NULL,
  `bill_address_01` varchar(100) DEFAULT NULL,
  `bill_address_02` varchar(100) DEFAULT NULL,
  `bill_address_03` varchar(100) DEFAULT NULL,
  `bill_address_04` varchar(100) DEFAULT NULL,
  `bill_attention` varchar(80) DEFAULT NULL,
  `bill_postcode` varchar(10) DEFAULT NULL,
  `bill_phone_01` varchar(30) DEFAULT NULL,
  `bill_phone_02` varchar(30) DEFAULT NULL,
  `bill_fax_01` varchar(30) DEFAULT NULL,
  `bill_fax_02` varchar(30) DEFAULT NULL,
  `currency_rate` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `currency_id` int(10) unsigned NOT NULL,
  `disc_percent_01` decimal(5,2) DEFAULT '0.00',
  `disc_percent_02` decimal(5,2) DEFAULT '0.00',
  `disc_percent_03` decimal(5,2) DEFAULT '0.00',
  `disc_percent_04` decimal(5,2) DEFAULT '0.00',
  `disc_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `tax_percent_01` decimal(5,2) DEFAULT '0.00',
  `tax_percent_02` decimal(5,2) DEFAULT '0.00',
  `tax_percent_03` decimal(5,2) DEFAULT '0.00',
  `tax_percent_04` decimal(5,2) DEFAULT '0.00',
  `tax_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `order_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `net_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `is_printed` tinyint(1) NOT NULL DEFAULT 0,
  `is_exported` tinyint(1) NOT NULL DEFAULT 0,
  `status` int(2) NOT NULL DEFAULT 0, -- status : to be discussed
  `transport_code` varchar(30) DEFAULT NULL, -- filled in when is shipped
  `driver_name` varchar(30) DEFAULT NULL, -- filled in when is shipped
  `remark_01` varchar(200) DEFAULT NULL,
  `remark_02` varchar(200) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  `ssp`.`do_inv_dtl` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hdr_id` int(10) unsigned NOT NULL,
  `line_no` int(4) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `location_id` int(10) unsigned NOT NULL,
  `shelf_no` varchar(15) DEFAULT NULL,
  `batch_no` varchar(30) DEFAULT NULL,
  `promo_code` varchar(30) DEFAULT NULL,
  `expiry_date` datetime DEFAULT NULL,
  `del_date` datetime DEFAULT NULL,
  `qty` decimal(14,4) NOT NULL DEFAULT '0.0000',
  `price` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `uom` varchar(20) NOT NULL DEFAULT '',
  `uom_rate` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `disc_percent_01` decimal(5,2) DEFAULT '0.00',
  `disc_percent_02` decimal(5,2) DEFAULT '0.00',
  `disc_percent_03` decimal(5,2) DEFAULT '0.00',
  `disc_percent_04` decimal(5,2) DEFAULT '0.00',
  `disc_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `tax_group_id` int(10) unsigned NOT NULL,
  `tax_percent_01` decimal(5,2) DEFAULT '0.00',
  `tax_percent_02` decimal(5,2) DEFAULT '0.00',
  `tax_percent_03` decimal(5,2) DEFAULT '0.00',
  `tax_percent_04` decimal(5,2) DEFAULT '0.00',
  `tax_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `net_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `remark` varchar(200) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- Sales process tracking
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`sales_tracking` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(10) unsigned NOT NULL, -- customer compulsary
  `salesman_id` int(10) unsigned DEFAULT NULL,
  `sales_odr_id` int(10) unsigned DEFAULT NULL,
  `sales_odr_date` datetime DEFAULT NULL,
  `sales_odr_dtl_id` int(10) unsigned DEFAULT NULL,
  `sales_odr_dtl_date` datetime DEFAULT NULL,
  `do_inv_id` int(10) unsigned DEFAULT NULL,
  `do_inv_date` datetime DEFAULT NULL,
  `do_inv_dtl_id` int(10) unsigned DEFAULT NULL,
  `do_inv_dtl_date` datetime DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- Sales return
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`sales_ret` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `doc_code` varchar(30) NOT NULL,
  `recv_code` varchar(30) DEFAULT NULL, -- filled in when received by location
  `ref_code` varchar(30) DEFAULT NULL,
  `doc_date` datetime NOT NULL,
  `company_id` int(10) unsigned NOT NULL,
  `customer_id` int(10) unsigned NOT NULL DEFAULT '0',
  `salesman_id` int(10) unsigned NOT NULL DEFAULT '0',
  `project_id` int(10) unsigned DEFAULT NULL,
  `division_id` int(10) unsigned NOT NULL DEFAULT '0',
  `term_id` int(10) unsigned NOT NULL,
  `term_code` varchar(10) NOT NULL,
  `term_day` int(5) NOT NULL,
  `return_type` int(1) unsigned NOT NULL DEFAULT '0', -- order type : Mobile/Web/??
  `branch_id` int(10) unsigned NOT NULL DEFAULT '0',
  `branch_code` varchar(20) DEFAULT '',
  `area_id` int(10) unsigned NOT NULL DEFAULT '0',
  `area_code` varchar(20) DEFAULT '',
  `del_address_01` varchar(100) DEFAULT NULL,
  `del_address_02` varchar(100) DEFAULT NULL,
  `del_address_03` varchar(100) DEFAULT NULL,
  `del_address_04` varchar(100) DEFAULT NULL,
  `del_attention` varchar(80) DEFAULT NULL,
  `del_postcode` varchar(10) DEFAULT NULL,
  `del_phone_01` varchar(30) DEFAULT NULL,
  `del_phone_02` varchar(30) DEFAULT NULL,
  `del_fax_01` varchar(30) DEFAULT NULL,
  `del_fax_02` varchar(30) DEFAULT NULL,
  `currency_rate` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `currency_id` int(10) unsigned NOT NULL,
  `order_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `net_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `is_printed` tinyint(1) DEFAULT NULL,
  `status` int(2) NOT NULL DEFAULT 0, -- status : 0=draft, 1=processing(+/-stock), 2=completed(CN issued)
  `ar_cn_id` int(10) unsigned DEFAULT NULL, -- AR credit note id (status = 2)
  `remark_01` varchar(200) DEFAULT NULL,
  `remark_02` varchar(200) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  `ssp`.`sales_ret_dtl` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hdr_id` int(10) unsigned NOT NULL,
  `line_no` int(4) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `location_id` int(10) unsigned NOT NULL,
  `shelf_no` varchar(15) DEFAULT NULL,
  `batch_no` varchar(30) DEFAULT NULL,
  `promo_code` varchar(30) DEFAULT NULL,
  `expiry_date` datetime DEFAULT NULL,
  `del_date` datetime DEFAULT NULL,
  `qty` decimal(14,4) NOT NULL DEFAULT '0.0000',
  `price` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `uom` varchar(20) NOT NULL DEFAULT '',
  `uom_rate` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `disc_percent_01` decimal(5,2) DEFAULT '0.00',
  `disc_percent_02` decimal(5,2) DEFAULT '0.00',
  `disc_percent_03` decimal(5,2) DEFAULT '0.00',
  `disc_percent_04` decimal(5,2) DEFAULT '0.00',
  `disc_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `tax_group_id` int(10) unsigned NOT NULL,
  `tax_percent_01` decimal(5,2) DEFAULT '0.00',
  `tax_percent_02` decimal(5,2) DEFAULT '0.00',
  `tax_percent_03` decimal(5,2) DEFAULT '0.00',
  `tax_percent_04` decimal(5,2) DEFAULT '0.00',
  `tax_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `net_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `reason_id` int(10) unsigned NOT NULL, -- compulsary reason for return
  `reason_code` varchar(10) NOT NULL, -- compulsary reason code
  `remark` varchar(200) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- Purchase Requisition
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`prch_req` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `doc_code` varchar(30) NOT NULL,
  `ref_code` varchar(30) DEFAULT NULL,
  `doc_date` datetime NOT NULL,
  `company_id` int(10) unsigned NOT NULL,
  `vendor_id` int(10) unsigned NOT NULL DEFAULT '0',
  `purchaser_id` int(10) unsigned NOT NULL DEFAULT '0',
  `project_id` int(10) unsigned DEFAULT NULL,
  `division_id` int(10) unsigned NOT NULL DEFAULT '0',
  `term_id` int(10) unsigned NOT NULL,
  `term_code` varchar(10) NOT NULL,
  `term_day` int(5) NOT NULL,
  `currency_rate` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `currency_id` int(10) unsigned NOT NULL,
  `disc_percent_01` decimal(5,2) DEFAULT '0.00',
  `disc_percent_02` decimal(5,2) DEFAULT '0.00',
  `disc_percent_03` decimal(5,2) DEFAULT '0.00',
  `disc_percent_04` decimal(5,2) DEFAULT '0.00',
  `disc_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `tax_percent_01` decimal(5,2) DEFAULT '0.00',
  `tax_percent_02` decimal(5,2) DEFAULT '0.00',
  `tax_percent_03` decimal(5,2) DEFAULT '0.00',
  `tax_percent_04` decimal(5,2) DEFAULT '0.00',
  `tax_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `order_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `net_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `remark_01` varchar(200) DEFAULT NULL,
  `remark_02` varchar(200) DEFAULT NULL,
  `is_printed` tinyint(1) DEFAULT NULL,
  `status` int(2) NOT NULL DEFAULT 0, -- status : 0, draft, 1, processed, 99, completed
  `revision` varchar(8) NOT NULL DEFAULT '1.0.0',
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  `ssp`.`prch_req_dtl` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hdr_id` int(10) unsigned NOT NULL,
  `line_no` int(4) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `location_id` int(10) unsigned NOT NULL,
  `del_date` datetime DEFAULT NULL,
  `qty` decimal(14,4) unsigned NOT NULL DEFAULT '0.0000',
  `price` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `uom` varchar(20) NOT NULL DEFAULT '',
  `uom_rate` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `disc_percent_01` decimal(5,2) DEFAULT '0.00',
  `disc_percent_02` decimal(5,2) DEFAULT '0.00',
  `disc_percent_03` decimal(5,2) DEFAULT '0.00',
  `disc_percent_04` decimal(5,2) DEFAULT '0.00',
  `disc_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `tax_group_id` int(10) unsigned NOT NULL,
  `tax_percent_01` decimal(5,2) DEFAULT '0.00',
  `tax_percent_02` decimal(5,2) DEFAULT '0.00',
  `tax_percent_03` decimal(5,2) DEFAULT '0.00',
  `tax_percent_04` decimal(5,2) DEFAULT '0.00',
  `tax_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `net_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `remark` varchar(200) DEFAULT NULL,
  `is_closed` tinyint(1) DEFAULT NULL, -- only true this line is purchased
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- Purchase Order
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`prch_odr` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `doc_code` varchar(30) NOT NULL,
  `ref_code` varchar(30) DEFAULT NULL,
  `doc_date` datetime NOT NULL,
  `order_type` char(1) NOT NULL, -- to discuss
  `company_id` int(10) unsigned NOT NULL,
  `vendor_id` int(10) unsigned NOT NULL DEFAULT '0',
  `purchaser_id` int(10) unsigned NOT NULL DEFAULT '0',
  `project_id` int(10) unsigned DEFAULT NULL,
  `division_id` int(10) unsigned NOT NULL DEFAULT '0',
  `term_id` int(10) unsigned NOT NULL,
  `term_code` varchar(10) NOT NULL,
  `term_day` int(5) NOT NULL,
  `currency_rate` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `currency_id` int(10) unsigned NOT NULL,
  `disc_percent_01` decimal(5,2) DEFAULT '0.00',
  `disc_percent_02` decimal(5,2) DEFAULT '0.00',
  `disc_percent_03` decimal(5,2) DEFAULT '0.00',
  `disc_percent_04` decimal(5,2) DEFAULT '0.00',
  `disc_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `tax_percent_01` decimal(5,2) DEFAULT '0.00',
  `tax_percent_02` decimal(5,2) DEFAULT '0.00',
  `tax_percent_03` decimal(5,2) DEFAULT '0.00',
  `tax_percent_04` decimal(5,2) DEFAULT '0.00',
  `tax_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `order_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `net_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `remark_01` varchar(200) DEFAULT NULL,
  `remark_02` varchar(200) DEFAULT NULL,
  `is_printed` tinyint(1) DEFAULT NULL,
  `status` int(2) NOT NULL DEFAULT 0, -- status : 0, draft, 1, processed, 99, completed
  `revision` varchar(8) NOT NULL DEFAULT '1.0.0',
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  `ssp`.`prch_odr_dtl` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hdr_id` int(10) unsigned NOT NULL,
  `line_no` int(4) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `location_id` int(10) unsigned NOT NULL,
  `del_date` datetime DEFAULT NULL,
  `qty` decimal(14,4) unsigned NOT NULL DEFAULT '0.0000',
  `recv_qty` decimal(14,4) unsigned NOT NULL DEFAULT '0.0000',
  `price` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `uom` varchar(20) NOT NULL DEFAULT '',
  `uom_rate` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `disc_percent_01` decimal(5,2) DEFAULT '0.00',
  `disc_percent_02` decimal(5,2) DEFAULT '0.00',
  `disc_percent_03` decimal(5,2) DEFAULT '0.00',
  `disc_percent_04` decimal(5,2) DEFAULT '0.00',
  `disc_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `tax_group_id` int(10) unsigned NOT NULL,
  `tax_percent_01` decimal(5,2) DEFAULT '0.00',
  `tax_percent_02` decimal(5,2) DEFAULT '0.00',
  `tax_percent_03` decimal(5,2) DEFAULT '0.00',
  `tax_percent_04` decimal(5,2) DEFAULT '0.00',
  `tax_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `net_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `remark` varchar(200) DEFAULT NULL,
  `is_closed` tinyint(1) DEFAULT NULL, -- only true this line is received completely
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- Purchase Received
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`grn_inv` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `grn_code` varchar(30) DEFAULT NULL, -- goods received note number - either one must be filled
  `inv_code` varchar(30) DEFAULT NULL, -- filled in when invoice received
  `ref_code` varchar(30) DEFAULT NULL,
  `doc_date` datetime NOT NULL,
  `order_type` char(1) NOT NULL, -- to discuss
  `company_id` int(10) unsigned NOT NULL,
  `vendor_id` int(10) unsigned NOT NULL DEFAULT '0',
  `purchaser_id` int(10) unsigned NOT NULL DEFAULT '0',
  `project_id` int(10) unsigned DEFAULT NULL,
  `division_id` int(10) unsigned NOT NULL DEFAULT '0',
  `term_id` int(10) unsigned NOT NULL,
  `term_code` varchar(10) NOT NULL,
  `term_day` int(5) NOT NULL,
  `currency_rate` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `currency_id` int(10) unsigned NOT NULL,
  `disc_percent_01` decimal(5,2) DEFAULT '0.00',
  `disc_percent_02` decimal(5,2) DEFAULT '0.00',
  `disc_percent_03` decimal(5,2) DEFAULT '0.00',
  `disc_percent_04` decimal(5,2) DEFAULT '0.00',
  `disc_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `tax_percent_01` decimal(5,2) DEFAULT '0.00',
  `tax_percent_02` decimal(5,2) DEFAULT '0.00',
  `tax_percent_03` decimal(5,2) DEFAULT '0.00',
  `tax_percent_04` decimal(5,2) DEFAULT '0.00',
  `tax_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `order_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `net_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `is_printed` tinyint(1) NOT NULL DEFAULT 0,
  `is_exported` tinyint(1) NOT NULL DEFAULT 0,
  `status` int(2) NOT NULL DEFAULT 0, -- status : 0 - draft, 1 - either grn/inv not filled, 2 - completed
  `remark_01` varchar(200) DEFAULT NULL,
  `remark_02` varchar(200) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  `ssp`.`grn_inv_dtl` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hdr_id` int(10) unsigned NOT NULL,
  `line_no` int(4) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `location_id` int(10) unsigned NOT NULL,
  `shelf_no` varchar(15) DEFAULT NULL,
  `batch_no` varchar(30) DEFAULT NULL,
  `expiry_date` datetime DEFAULT NULL,
  `del_date` datetime DEFAULT NULL,
  `qty` decimal(14,4) unsigned NOT NULL DEFAULT '0.0000',
  `price` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `uom` varchar(20) NOT NULL DEFAULT '',
  `uom_rate` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `conversion_uom` varchar(20) NOT NULL DEFAULT '',
  `conversion_rate` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `disc_percent_01` decimal(5,2) DEFAULT '0.00',
  `disc_percent_02` decimal(5,2) DEFAULT '0.00',
  `disc_percent_03` decimal(5,2) DEFAULT '0.00',
  `disc_percent_04` decimal(5,2) DEFAULT '0.00',
  `disc_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `tax_group_id` int(10) unsigned NOT NULL,
  `tax_percent_01` decimal(5,2) DEFAULT '0.00',
  `tax_percent_02` decimal(5,2) DEFAULT '0.00',
  `tax_percent_03` decimal(5,2) DEFAULT '0.00',
  `tax_percent_04` decimal(5,2) DEFAULT '0.00',
  `tax_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `net_amt` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `remark` varchar(200) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- Purchaching process tracking
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`prch_tracking` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_id` int(10) unsigned NOT NULL, -- vendor compulsary
  `prch_req_id` int(10) unsigned DEFAULT NULL,
  `prch_req_date` datetime DEFAULT NULL,
  `prch_req_dtl_id` int(10) unsigned DEFAULT NULL,
  `prch_req_dtl_date` datetime DEFAULT NULL,
  `prch_odr_id` int(10) unsigned DEFAULT NULL,
  `prch_odr_date` datetime DEFAULT NULL,
  `prch_odr_dtl_id` int(10) unsigned DEFAULT NULL,
  `prch_odr_dtl_date` datetime DEFAULT NULL,
  `grn_inv_id` int(10) unsigned DEFAULT NULL,
  `grn_inv_date` datetime DEFAULT NULL,
  `grn_inv_dtl_id` int(10) unsigned DEFAULT NULL,
  `grn_inv_dtl_date` datetime DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



-- ----------------------------------------------------------------------
-- Stock adjustment
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`stk_adj` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `doc_code` varchar(30) NOT NULL,
  `ref_code` varchar(30) DEFAULT NULL,
  `doc_date` datetime NOT NULL,
  `division_id` int(10) unsigned NOT NULL,
  `reason_id` int(10) unsigned NOT NULL, -- compulsary reason for adjustment
  `reason_code` varchar(10) NOT NULL, -- compulsary reason code
  `is_printed` tinyint(1) DEFAULT NULL,
  `status` int(2) NOT NULL DEFAULT 0, -- status : 0=draft, 1=completed(+/-stock)
  `remark_01` varchar(200) DEFAULT NULL,
  `remark_02` varchar(200) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  `ssp`.`stk_adj_dtl` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hdr_id` int(10) unsigned NOT NULL,
  `line_no` int(4) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `location_id` int(10) unsigned NOT NULL,
  `shelf_no` varchar(15) DEFAULT NULL,
  `batch_no` varchar(30) DEFAULT NULL,
  `expiry_date` datetime DEFAULT NULL,
  `qty` decimal(14,4) NOT NULL DEFAULT '0.0000',
  `uom` varchar(20) NOT NULL DEFAULT '',
  `uom_rate` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `remark` varchar(200) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- Stock transfer
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`stk_transfer` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `doc_code` varchar(30) NOT NULL,
  `ref_code` varchar(30) DEFAULT NULL,
  `doc_date` datetime NOT NULL,
  `division_id` int(10) unsigned NOT NULL,
  `reason_id` int(10) unsigned NOT NULL, -- compulsary reason for transfer * need a reason code lookup table
  `reason_code` varchar(10) NOT NULL, -- compulsary reason code
  `from_location_id` int(10) unsigned NOT NULL, -- from location
  `to_location_id` int(10) unsigned NOT NULL,
  `is_printed` tinyint(1) DEFAULT NULL,
  `status` int(2) NOT NULL DEFAULT 0, -- status : 0=draft, 1=completed(+/-stock)
  `remark_01` varchar(200) DEFAULT NULL,
  `remark_02` varchar(200) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  `ssp`.`stk_transfer_dtl` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hdr_id` int(10) unsigned NOT NULL,
  `line_no` int(4) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `from_location_id` int(10) unsigned NOT NULL,
  `from_shelf_no` varchar(15) DEFAULT NULL,
  `from_batch_no` varchar(30) DEFAULT NULL,
  `to_location_id` int(10) unsigned NOT NULL,
  `to_shelf_no` varchar(15) DEFAULT NULL,
  `to_batch_no` varchar(30) DEFAULT NULL,
  `expiry_date` datetime DEFAULT NULL,
  `qty` decimal(14,4) NOT NULL DEFAULT '0.0000',
  `uom` varchar(20) NOT NULL DEFAULT '',
  `uom_rate` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `remark` varchar(200) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- Stock issue
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`stk_issue` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `doc_code` varchar(30) NOT NULL,
  `ref_code` varchar(30) DEFAULT NULL,
  `doc_date` datetime NOT NULL,
  `division_id` int(10) unsigned NOT NULL,
  `reason_id` int(10) unsigned NOT NULL, -- compulsary reason for issue
  `reason_code` varchar(10) NOT NULL, -- compulsary reason code
  `is_printed` tinyint(1) DEFAULT NULL,
  `status` int(2) NOT NULL DEFAULT 0, -- status : 0=draft, 1=completed(+/-stock)
  `remark_01` varchar(200) DEFAULT NULL,
  `remark_02` varchar(200) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  `ssp`.`stk_issue_dtl` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hdr_id` int(10) unsigned NOT NULL,
  `line_no` int(4) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `location_id` int(10) unsigned NOT NULL,
  `shelf_no` varchar(15) DEFAULT NULL,
  `batch_no` varchar(30) DEFAULT NULL,
  `expiry_date` datetime DEFAULT NULL,
  `qty` decimal(14,4) NOT NULL DEFAULT '0.0000',
  `uom` varchar(20) NOT NULL DEFAULT '',
  `uom_rate` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `remark` varchar(200) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- Stock receive
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`stk_recv` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `doc_code` varchar(30) NOT NULL,
  `ref_code` varchar(30) DEFAULT NULL,
  `doc_date` datetime NOT NULL,
  `division_id` int(10) unsigned NOT NULL,
  `reason_id` int(10) unsigned NOT NULL, -- compulsary reason for receive
  `reason_code` varchar(10) NOT NULL, -- compulsary reason code
  `is_printed` tinyint(1) DEFAULT NULL,
  `status` int(2) NOT NULL DEFAULT 0, -- status : 0=draft, 1=completed(+/-stock)
  `remark_01` varchar(200) DEFAULT NULL,
  `remark_02` varchar(200) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  `ssp`.`stk_recv_dtl` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `hdr_id` int(10) unsigned NOT NULL,
  `line_no` int(4) unsigned NOT NULL,
  `item_id` int(10) unsigned NOT NULL,
  `location_id` int(10) unsigned NOT NULL,
  `shelf_no` varchar(15) DEFAULT NULL,
  `batch_no` varchar(30) DEFAULT NULL,
  `expiry_date` datetime DEFAULT NULL,
  `qty` decimal(14,4) NOT NULL DEFAULT '0.0000',
  `uom` varchar(20) NOT NULL DEFAULT '',
  `uom_rate` decimal(14,6) NOT NULL DEFAULT '0.000000',
  `remark` varchar(200) DEFAULT NULL,
-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------------------------------------------------
-- Stock count
-- ----------------------------------------------------------------------

CREATE TABLE  `ssp`.`stk_count` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `doc_date` datetime NOT NULL,

-- Log --
  `created` datetime NOT NULL,
  `createdby` int UNSIGNED NOT NULL,
  `updated` datetime NOT NULL,
  `updatedby` int UNSIGNED NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;