CREATE TABLE `invh_app` (
  `id` int(11) NOT NULL,
  `invno` varchar(25) NOT NULL,
  `app_inv_no` varchar(45) NOT NULL,
  `is_act` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='desktop app and android app invoice mapping';

--
-- Indexes for dumped tables
--
--
--
-- Indexes for table `invh_app`
--
ALTER TABLE `invh_app`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `invno` (`invno`),
  ADD UNIQUE KEY `app_inv_no` (`app_inv_no`);
--
--
-- AUTO_INCREMENT for dumped tables
--
--
--
-- AUTO_INCREMENT for table `invh_app`
--
ALTER TABLE `invh_app`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
--
--
--
--
ALTER TABLE `invh` CHANGE `cname` `cname` VARCHAR(70) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
--
INSERT INTO `access` (`uid`, `menu`, `mitem`, `mdesc`, `midesc`, `Auto`) VALUES ('10', 'DATA', '1', 'Data Entry', 'Order Entry', NULL);
INSERT INTO `access` (`uid`, `menu`, `mitem`, `mdesc`, `midesc`, `Auto`) VALUES ('11', 'DATA', '1', 'Data Entry', 'Order Entry', NULL);
INSERT INTO `access` (`uid`, `menu`, `mitem`, `mdesc`, `midesc`, `Auto`) VALUES ('18', 'DATA', '1', 'Data Entry', 'Order Entry', NULL);
--
INSERT INTO `access` (`uid`, `menu`, `mitem`, `mdesc`, `midesc`, `Auto`) VALUES ('10', 'DATA', '2', 'Data Entry', 'Download and Post Orders', NULL);
INSERT INTO `access` (`uid`, `menu`, `mitem`, `mdesc`, `midesc`, `Auto`) VALUES ('11', 'DATA', '2', 'Data Entry', 'Download and Post Orders', NULL);
INSERT INTO `access` (`uid`, `menu`, `mitem`, `mdesc`, `midesc`, `Auto`) VALUES ('18', 'DATA', '2', 'Data Entry', 'Download and Post Orders', NULL);
--
INSERT INTO `access` (`uid`, `menu`, `mitem`, `mdesc`, `midesc`, `Auto`) VALUES ('10', 'DATA', '3', 'Data Entry', 'Print Invoices', NULL);
INSERT INTO `access` (`uid`, `menu`, `mitem`, `mdesc`, `midesc`, `Auto`) VALUES ('11', 'DATA', '3', 'Data Entry', 'Print Invoices', NULL);
INSERT INTO `access` (`uid`, `menu`, `mitem`, `mdesc`, `midesc`, `Auto`) VALUES ('18', 'DATA', '3', 'Data Entry', 'Print Invoices', NULL);
--
INSERT INTO `access` (`uid`, `menu`, `mitem`, `mdesc`, `midesc`, `Auto`) VALUES ('10', 'DATA', '4', 'Data Entry', 'Post Actual', NULL);
INSERT INTO `access` (`uid`, `menu`, `mitem`, `mdesc`, `midesc`, `Auto`) VALUES ('11', 'DATA', '4', 'Data Entry', 'Post Actual', NULL);
INSERT INTO `access` (`uid`, `menu`, `mitem`, `mdesc`, `midesc`, `Auto`) VALUES ('18', 'DATA', '4', 'Data Entry', 'Post Actual', NULL);


-- newly added

ALTER TABLE `invd` ADD `gr_up` DECIMAL(19,2) NOT NULL DEFAULT '0.00' COMMENT 'good return unit price' AFTER `Auto`;
ALTER TABLE `invh` ADD `app_inv_no` VARCHAR(200) NULL DEFAULT NULL COMMENT 'android app invoice number ' AFTER `auto`;

--
ALTER TABLE `invh_app` ADD `app_inv_date` DATE NOT NULL COMMENT 'app invoice date' AFTER `app_inv_no`;

--
ALTER TABLE `invd` ADD `distributor_stock_id` INT(11) NULL DEFAULT NULL AFTER `gr_up`;
ALTER TABLE `invh` ADD `distributor_stock_id` INT(11) NULL DEFAULT NULL AFTER `app_inv_no`;

ALTER TABLE `inv_ptr` CHANGE `qty` `qty` DECIMAL( 9, 1 ) NOT NULL ;


--
ALTER TABLE `invd` CHANGE `up` `up` DECIMAL(19,2) NOT NULL;
ALTER TABLE `invd` CHANGE `ret_up` `ret_up` DECIMAL(19,2) NOT NULL;
ALTER TABLE `inv_ptr` CHANGE `up` `up` DECIMAL(19,2) NOT NULL;



--
-- Table structure for table `tbl_mst_item_price`  20220628
--

CREATE TABLE `tbl_mst_item_price` (
  `id` bigint(20) NOT NULL,
  `range_id` int(11) NOT NULL,
  `item` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `uom` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `wholesale_price` decimal(19,2) NOT NULL DEFAULT '0.00',
  `min_ret_price` decimal(19,2) NOT NULL DEFAULT '25.00' COMMENT 'minumum return price can enter as a percentage',
  `retail_price` decimal(19,2) NOT NULL DEFAULT '0.00',
  `date_start` date NOT NULL,
  `date_end` date NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='price list table of items';

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_mst_item_price`
--
ALTER TABLE `tbl_mst_item_price`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbl_mst_item_price`
--
ALTER TABLE `tbl_mst_item_price`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;

ALTER TABLE `tbl_mst_item_price` ADD `range_name` VARCHAR(12) NULL AFTER `range_id`;

--
-- 
ALTER TABLE `vno` ADD `distributor_stock_id` INT(11) NOT NULL DEFAULT '0' AFTER `trn_no`;
--
ALTER TABLE `vno` ADD `allowed_op` VARCHAR(10) NOT NULL DEFAULT 'CD' COMMENT 'allowed rep operations C,D,S,T' AFTER `saj_no`;


--
-- Table structure for table `tbl_trans_manual_bill_quota`
--

CREATE TABLE `tbl_trans_manual_bill_quota` (
  `id` bigint(20) NOT NULL,
  `date_created` date NOT NULL,
  `audit_user` varchar(75) COLLATE utf8_unicode_ci NOT NULL,
  `audit_date` datetime NOT NULL,
  `territory_id` int(11) NOT NULL,
  `territory_ref_code` int(7) NOT NULL,
  `range_id` int(11) NOT NULL,
  `range_name` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
  `quota` int(3) NOT NULL COMMENT 'manual bill total allowed',
  `used` int(3) NOT NULL DEFAULT '0' COMMENT 'currently entered bills ',
  `isact` tinyint(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='manual bill allowed to a sales rep using desktop app';

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbl_trans_manual_bill_quota`
--
ALTER TABLE `tbl_trans_manual_bill_quota`
  ADD PRIMARY KEY (`id`);